SELECT
fnd_flex_ext.get_segs('GL','GL#',glc2.chart_of_accounts_id,glc2.code_combination_id) accrual_account ,
fun.bu_name operating_unit ,
poh.segment1 po_number ,
pol.line_num po_line_number ,
poh.currency_code po_currency_code ,
pol.matching_basis ,
rcvtr.quantity_received ,
fnd_flex_ext.get_segs('GL','GL#',glc1.chart_of_accounts_id,glc1.CODE_COMBINATION_ID) Expense_Account ,
TRUNC(SYSDATE)-TRUNC(poh.creation_date) AS aged_in_days ,
ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',pol.amount,pol.unit_price*pol.quantity),0),2) Entered_po_amount ,
NVL(invn.amount_billed ,0) Entered_ap_amount ,
ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received, pol.unit_price*rcvtr.quantity_received),0),2) Entered_GRN_amount ,
NVL(invn.amount_billed ,0) - NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received,pol.unit_price*rcvtr.quantity_received),0) Entered_accrued_balance,
ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',pol.amount*NVL(poh.rate,1),pol.unit_price*pol.quantity*NVL(poh.rate,1)),0),2) Functional_po_amount ,
NVL(amount_billed_b,0) Functional_ap_amount,
ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received*NVL(poh.rate,1),pol.unit_price*rcvtr.quantity_received*NVL(poh.rate,1)),0),2) Functional_GRN_amount,
NVL(amount_billed_b,0) - ROUND(NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received*NVL(poh.rate,1),pol.unit_price*rcvtr.quantity_received*NVL(poh.rate,1)),0),2) Functional_accrued_balance,
psv.vendor_name supplier_name ,
psv.segment1 supplier_number ,
pssa.vendor_site_code supplier_site_name ,
(
SELECT
per.full_name
FROM
por_requisition_lines_all req,
per_person_names_f per
WHERE
pol.po_line_id = req.po_line_id
AND req.requester_id = per.person_id
AND rownum <= 1 ) AS requisitioner,
(
SELECT
ppf.full_name
FROM
per_person_names_f ppf
WHERE
ppf.person_id = poh.agent_id
AND ppf.name_type ='GLOBAL'
AND TRUNC(SYSDATE) BETWEEN TRUNC(ppf.effective_start_date)AND TRUNC(ppf.effective_end_date)) buyer
FROM
po_headers_all poh
,poz_suppliers_v psv
,poz_supplier_sites_v pssa
,poz_suppliers_pii supps_pii
,fun_all_business_units_v fun
,po_lines_all pol
,po_line_locations_all poll
,hr_organization_units_f_tl hro
,(
SELECT DISTINCT
po_line_id ,
line_location_id ,
pjc_project_id ,
pjc_task_id ,
pjc_organization_id ,
pjc_expenditure_type_id,
code_combination_id ,
accrual_account_id
FROM
po_distributions_all) pod
,pjf_projects_all_vl prj
,hr_locations_all dloc
,pjf_tasks_v task
,pjf_exp_types_vl exp
,pjf_organizations_expend_v exp_org
,(
SELECT
rsl.po_line_id ,
rsl.po_line_location_id ,
SUM( DECODE( rt.transaction_type, 'RETURN TO VENDOR', NVL(rt.quantity, 0) * -1, NVL(rt.quantity, 0) ) ) AS quantity_received,
SUM( DECODE( rt.transaction_type, 'RETURN TO VENDOR', NVL(rt.amount, 0) * -1, NVL(rt.amount, 0) ) ) AS amount_received
FROM
rcv_transactions rt ,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE
rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rt.destination_type_code = 'RECEIVING'
GROUP BY
rsl.PO_LINE_ID,
rsl.PO_LINE_LOCATION_ID ) rcvtr
,GL_CODE_COMBINATIONS glc1
,GL_CODE_COMBINATIONS glc2
,(
SELECT
l.po_line_id ,
po_line_location_id ,
COUNT(DISTINCT l.invoice_id) AS num_invoices ,
SUM(l.amount) AS amount_billed,
SUM(NVL(l.base_amount, l.amount)) AS amount_billed_b
FROM
ap_invoice_lines_all l,
ap_invoices_all h
WHERE
h.invoice_id = l.invoice_id
GROUP BY
po_line_id,
po_line_location_id ) invn
,xle_entity_profiles xle
,egp_categories_vl cat
WHERE 1=1
AND poh.vendor_id = psv.vendor_id
AND poh.vendor_site_id = pssa.vendor_site_id
AND supps_pii.vendor_id = psv.vendor_id
AND poh.billto_bu_id = fun.bu_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.ship_to_organization_id = hro.organization_id
AND hro.language = userenv('LANG')
AND pol.po_line_id = pod.po_line_id
AND poll.line_location_id = pod.line_location_id
AND prj.project_id = pod.pjc_project_id(+)
AND dloc.location_id=poll.ship_to_location_id(+)
AND task.task_id = pod.pjc_task_id(+)
AND exp.expenditure_type_id = pod.pjc_expenditure_type_id(+)
AND exp_org.organization_id = pod.pjc_organization_id(+)
AND exp_org.org_id =fun.bu_id(+)
AND pol.PO_LINE_ID = rcvtr.PO_LINE_ID
AND poll.LINE_LOCATION_ID = rcvtr.PO_LINE_LOCATION_ID
AND pod.CODE_COMBINATION_ID = glc1.CODE_COMBINATION_ID(+)
AND pod.ACCRUAL_ACCOUNT_ID = glc2.CODE_COMBINATION_ID(+)
AND poll.po_line_id = invn.po_line_id(+)
AND poll.line_location_id = invn.po_line_location_id(+)
AND poh.soldto_le_id = xle.legal_entity_id
AND cat.category_id = pol.category_id(+)
AND NVL(invn.amount_billed ,0) <> NVL(DECODE(pol.matching_basis,'AMOUNT',rcvtr.amount_received, pol.unit_price*rcvtr.quantity_received),0)
AND (fun.bu_id IN (:p_bu_id) OR COALESCE(:p_bu_id, NULL) IS NULL)
AND (psv.vendor_id IN (:p_vendor_id) OR COALESCE(:p_vendor_id, NULL) IS NULL)
ORDER BY
fun.bu_name,
poh.segment1,
pol.line_num
No comments:
Post a Comment