SELECT distinct
operating_unit ,
requisition_number,
requisition_type,
requisition_date ,
requisition_status ,
requisition_user_name,
requisition_line_number,
requisition_item_name,
requisition_item_description,
note_to_agent ,
cost_center,
project_number,
task_number,
requisition_quantity,
requisition_line_amount,
po_date,
po_number ,
po_created_by,
po_approval_status ,
po_line_status,
po_quantity ,
po_amount ,
currency_code,
destination_type,
inspection_required,
vendor_name ,
vendor_site,
&grn_details
invoice_date ,
invoice_number ,
invoice_amount,
invoice_due_date
FROM
(
SELECT
haouv.NAME operating_unit,
prha.segment1 requisition_number,
xxen_util.meaning(prha.type_lookup_code,'REQUISITION TYPE',201) requisition_type,
nvl(xxen_util.meaning(prha.authorization_status,'AUTHORIZATION STATUS',201) ,prha.authorization_status) requisition_status,
ppx.full_name requisition_user_name,
xxen_util.meaning(prha.closed_code,'DOCUMENT STATE',201) requisition_closed_status,
gcck.segment2 cost_center,
TRUNC(prha.creation_date) requisition_date,
prla.line_num requisition_line_number,
(SELECT DISTINCT msiv.segment1 FROM mtl_system_items_vl msiv
WHERE msiv.inventory_item_id = prla.item_id
) requisition_item_name,
prla.item_description requisition_item_description,
prla.note_to_agent ,
(select distinct ppa.segment1 from pa_projects_all ppa where ppa.project_id = prda.project_id) project_number,
(select distinct pt.task_number from pa_tasks pt where pt.task_id = prda.task_id) task_number,
prla.quantity requisition_quantity,
( nvl(prla.quantity, 0) * nvl(prla.unit_price, 0) ) requisition_line_amount,
xxen_util.client_time(pha.creation_date) po_date,
pha.segment1 po_number,
xxen_util.user_name(pha.created_by) po_created_by,
xxen_util.meaning(pha.authorization_status , 'DOCUMENT STATE', 201) po_approval_status,
coalesce(
xxen_util.meaning(plla.closed_code,'DOCUMENT STATE',201),
xxen_util.meaning(pla.closed_code,'DOCUMENT STATE',201),
xxen_util.meaning(pha.closed_code,'DOCUMENT STATE',201)
) po_line_status,
plla.quantity po_quantity,
pla.line_num po_line_number,
( nvl(plla.quantity, 0) * nvl(pla.unit_price, 0) ) po_amount,
pha.currency_code currency_code,
xxen_util.meaning(decode(plla.inspection_required_flag,'Y','Y'),'YES_NO',0) inspection_required,
xxen_util.meaning(pda.destination_type_code,'DESTINATION TYPE',201) destination_type,
pv.vendor_name vendor_name,
pvs.vendor_site_code vendor_site,
xxen_util.client_time( rsh.creation_date) receipt_creation_date,
rsh.receipt_num receipt_number,
rsl.quantity_received receipt_quantity,
xxen_util.client_time(aia.invoice_date) invoice_date,
aia.invoice_num invoice_number,
aia.invoice_amount,
xxen_util.client_time(apsa.due_date) invoice_due_date
FROM
po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_req_distributions_all prda,
hr_all_organization_units_vl haouv,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rct,
ap_invoice_distributions_all aid,
ap_invoices_all aia,
ap_payment_schedules_all apsa,
per_people_x ppx,
gl_code_combinations_kfv gcck,
po_vendors pv,
po_vendor_sites pvs
WHERE
pha.po_header_id = pla.po_header_id (+)
AND pla.po_line_id = plla.po_line_id (+)
AND plla.line_location_id = pda.line_location_id (+)
AND pda.req_distribution_id = prda.distribution_id (+)
AND prla.requisition_line_id = prda.requisition_line_id (+)
AND prha.requisition_header_id = prla.requisition_header_id (+)
AND prha.org_id=haouv.organization_id
AND pla.po_line_id = rct.po_line_id (+)
AND plla.line_location_id = rct.po_line_location_id (+)
AND pda.po_distribution_id = rct.po_distribution_id (+)
AND rsh.shipment_header_id = rct.shipment_header_id [Mrugesh: wont this need an outer join]
AND rsl.shipment_line_id = rct.shipment_line_id [Mrugesh: wont this need an outer join]
AND rct.transaction_type (+) IN ('RECEIVE') --- [Mrugesh: what is this]
AND pda.po_distribution_id = aid.po_distribution_id (+)
AND aid.invoice_id = aia.invoice_id (+)
AND aia.invoice_id = apsa.invoice_id (+)
AND prha.preparer_id = ppx.person_id
AND prda.code_combination_id = gcck.code_combination_id
AND pha.vendor_id = pv.vendor_id
AND pha.vendor_site_id = pvs.vendor_site_id
UNION ALL -- [Mrugesh: Why do we need outer join]
SELECT
haouv.NAME operating_unit,
prha.segment1 requisition_number,
xxen_util.meaning(prha.type_lookup_code,'REQUISITION TYPE',201) requisition_type,
prha.authorization_status requisition_status,
ppx.full_name requisition_user_name,
xxen_util.meaning(prha.closed_code,'DOCUMENT STATE',201) requisition_closed_status,
gcck.segment2 cost_center,
prha.creation_date requisition_date,
prla.line_num requisition_line_number,
(SELECT distinct msib.segment1 FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = prla.item_id
) requisition_item_name,
prla.item_description requisition_item_description,
prla.note_to_agent ,
(select distinct ppa.segment1 from pa_projects_all ppa where ppa.project_id = prda.project_id) project_number,
(select distinct pt.task_number from pa_tasks pt where pt.task_id = prda.task_id) task_number,
prla.quantity requisition_quantity,
( nvl(prla.quantity, 0) * nvl(prla.unit_price, 0) ) requisition_line_amount,
NULL po_date,
NULL po_number,
NULL po_created_by,
NULL po_approval_status,
NULL po_line_status,
NULL po_quantity,
NULL po_line_number,
NULL po_amount,
prla.currency_code currency_code,
NULL destination_type,
NULL inspection_required,
(select pv.vendor_name from po_vendors pv where pv.vendor_id=prla.vendor_id) vendor_name,
(select pvs.vendor_site_code from po_vendor_sites pvs where pvs.vendor_site_id=prla.vendor_site_id) vendor_site,
NULL receipt_creation_date,
NULL receipt_number,
NULL receipt_quantity,
NULL invoice_date,
NULL invoice_number,
NULL invoice_amount,
NULL invoice_due_date
FROM
po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
per_people_x ppx,
hr_all_organization_units_vl haouv,
gl_code_combinations_kfv gcck
WHERE
prha.requisition_header_id = prla.requisition_header_id (+)
AND prla.requisition_line_id = prda.requisition_line_id (+)
AND prha.preparer_id = ppx.person_id
AND prha.org_id=haouv.organization_id
AND prda.code_combination_id = gcck.code_combination_id
AND NOT EXISTS ( SELECT 1 FROM po_distributions_all pda
WHERE prda.distribution_id = pda.req_distribution_id)
)
WHERE 1 = 1 |
