Select *
FROM rcv_transactions_interface rti,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
po_requisition_lines prl,
mtl_system_items msi,
mtl_units_of_measure mum1,
mtl_units_of_measure mum2,
mtl_units_of_measure mum3,
mtl_units_of_measure mum4,
mtl_units_of_measure mum5,
po_lookup_codes plc1,
po_lookup_codes plc2,
po_lookup_codes plc3,
po_lookup_codes plc4,
po_lookup_codes plc5,
po_lookup_codes plc6,
oe_sold_to_orgs_v oec,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_tl oetl,
oe_transaction_types_all oet,
po_headers_trx_v ph,
po_lines_trx_v pl,
po_line_locations_trx_v pll,
po_releases_all pr,
po_vendors pov,
hr_locations_all_tl hrl,
mtl_transaction_reasons mtr,
rcv_routing_headers rrh,
per_all_people_f hre,
org_organization_definitions ood,
fnd_currencies_vl fcv,
per_jobs pj,
po_job_associations pja
WHERE ( rti.transaction_type = plc1.lookup_code
AND plc1.lookup_type = 'RCV TRANSACTION TYPE'
)
AND (rti.shipment_line_id = rsl.shipment_line_id(+))
AND (rti.requisition_line_id = prl.requisition_line_id(+))
AND (rti.shipment_header_id = rsh.shipment_header_id(+))
AND ( msi.inventory_item_id(+) = rti.item_id
AND NVL (msi.organization_id, rti.to_organization_id) =
rti.to_organization_id
AND msi.primary_unit_of_measure = mum1.unit_of_measure(+)
)
AND (rti.unit_of_measure = mum2.unit_of_measure(+))
AND (plc2.lookup_type(+) = 'INSPECTION STATUS'
AND plc2.lookup_code(+) = rti.inspection_status_code)
AND oel.line_id(+) = rti.oe_order_line_id
AND oeh.header_id(+) = rti.oe_order_header_id
AND oec.customer_id(+) = rsh.customer_id
AND oet.transaction_type_id(+) = oeh.order_type_id
AND oet.transaction_type_id = oetl.transaction_type_id(+)
AND oetl.LANGUAGE(+) = USERENV ('LANG')
AND oet.transaction_type_code(+) = 'ORDER'
AND ph.po_header_id(+) = rti.po_header_id
AND (pl.po_line_id(+) = rti.po_line_id)
AND (pll.line_location_id(+) = rti.po_line_location_id)
AND (pr.po_release_id(+) = rti.po_release_id)
AND pov.vendor_id(+) = rsh.vendor_id
AND plc3.lookup_type(+) = 'RCV DESTINATION TYPE'
AND plc3.lookup_code(+) = rti.destination_type_code
AND hrl.location_id(+) =
NVL (rti.deliver_to_location_id, rti.location_id)
AND hrl.LANGUAGE(+) = USERENV ('LANG')
AND rrh.routing_header_id(+) = rti.routing_header_id
AND mum3.unit_of_measure(+) = pl.unit_meas_lookup_code
AND mum4.unit_of_measure(+) = pll.secondary_unit_of_measure
AND mum5.uom_code(+) = oel.ordered_quantity_uom2
AND mtr.reason_id(+) = rti.reason_id
AND hre.person_id(+) = rti.deliver_to_person_id
AND TRUNC (SYSDATE) BETWEEN hre.effective_start_date(+) AND hre.effective_end_date(+)
AND DECODE (hr_security.view_all,
'Y', 'TRUE',
hr_security.show_person (hre.person_id(+), hre.current_applicant_flag(+),
hre.current_employee_flag(+), hre.current_npw_flag(+),
hre.employee_number(+), hre.applicant_number(+),
hre.npw_number(+))
) = 'TRUE'
AND DECODE (hr_general.get_xbg_profile,
'Y', hre.business_group_id(+),
hr_general.get_business_group_id
) = hre.business_group_id(+)
AND ood.organization_id(+) = rti.from_organization_id
AND DECODE (rti.source_document_code,
'PO', 'PO TYPE',
'SHIPMENT SOURCE TYPE'
) = plc4.lookup_type
AND DECODE (rti.transaction_type,
'UNORDERED', 'STANDARD',
DECODE (rti.source_document_code,
'PO', ph.type_lookup_code,
rsh.receipt_source_code
)
) = plc4.lookup_code
AND plc5.lookup_type = 'TRANSACTION STATUS'
AND plc5.lookup_code = rti.transaction_status_code
AND plc6.lookup_type = 'RCV PROCESSING MODE'
AND plc6.lookup_code = rti.processing_mode_code
AND fcv.currency_code(+) = rti.currency_code
AND pj.job_id(+) = rti.job_id
AND pja.job_id(+) = rti.job_id
AND NVL (ph.org_id, -99) = NVL (pll.org_id, -99);
FROM rcv_transactions_interface rti,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
po_requisition_lines prl,
mtl_system_items msi,
mtl_units_of_measure mum1,
mtl_units_of_measure mum2,
mtl_units_of_measure mum3,
mtl_units_of_measure mum4,
mtl_units_of_measure mum5,
po_lookup_codes plc1,
po_lookup_codes plc2,
po_lookup_codes plc3,
po_lookup_codes plc4,
po_lookup_codes plc5,
po_lookup_codes plc6,
oe_sold_to_orgs_v oec,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_tl oetl,
oe_transaction_types_all oet,
po_headers_trx_v ph,
po_lines_trx_v pl,
po_line_locations_trx_v pll,
po_releases_all pr,
po_vendors pov,
hr_locations_all_tl hrl,
mtl_transaction_reasons mtr,
rcv_routing_headers rrh,
per_all_people_f hre,
org_organization_definitions ood,
fnd_currencies_vl fcv,
per_jobs pj,
po_job_associations pja
WHERE ( rti.transaction_type = plc1.lookup_code
AND plc1.lookup_type = 'RCV TRANSACTION TYPE'
)
AND (rti.shipment_line_id = rsl.shipment_line_id(+))
AND (rti.requisition_line_id = prl.requisition_line_id(+))
AND (rti.shipment_header_id = rsh.shipment_header_id(+))
AND ( msi.inventory_item_id(+) = rti.item_id
AND NVL (msi.organization_id, rti.to_organization_id) =
rti.to_organization_id
AND msi.primary_unit_of_measure = mum1.unit_of_measure(+)
)
AND (rti.unit_of_measure = mum2.unit_of_measure(+))
AND (plc2.lookup_type(+) = 'INSPECTION STATUS'
AND plc2.lookup_code(+) = rti.inspection_status_code)
AND oel.line_id(+) = rti.oe_order_line_id
AND oeh.header_id(+) = rti.oe_order_header_id
AND oec.customer_id(+) = rsh.customer_id
AND oet.transaction_type_id(+) = oeh.order_type_id
AND oet.transaction_type_id = oetl.transaction_type_id(+)
AND oetl.LANGUAGE(+) = USERENV ('LANG')
AND oet.transaction_type_code(+) = 'ORDER'
AND ph.po_header_id(+) = rti.po_header_id
AND (pl.po_line_id(+) = rti.po_line_id)
AND (pll.line_location_id(+) = rti.po_line_location_id)
AND (pr.po_release_id(+) = rti.po_release_id)
AND pov.vendor_id(+) = rsh.vendor_id
AND plc3.lookup_type(+) = 'RCV DESTINATION TYPE'
AND plc3.lookup_code(+) = rti.destination_type_code
AND hrl.location_id(+) =
NVL (rti.deliver_to_location_id, rti.location_id)
AND hrl.LANGUAGE(+) = USERENV ('LANG')
AND rrh.routing_header_id(+) = rti.routing_header_id
AND mum3.unit_of_measure(+) = pl.unit_meas_lookup_code
AND mum4.unit_of_measure(+) = pll.secondary_unit_of_measure
AND mum5.uom_code(+) = oel.ordered_quantity_uom2
AND mtr.reason_id(+) = rti.reason_id
AND hre.person_id(+) = rti.deliver_to_person_id
AND TRUNC (SYSDATE) BETWEEN hre.effective_start_date(+) AND hre.effective_end_date(+)
AND DECODE (hr_security.view_all,
'Y', 'TRUE',
hr_security.show_person (hre.person_id(+), hre.current_applicant_flag(+),
hre.current_employee_flag(+), hre.current_npw_flag(+),
hre.employee_number(+), hre.applicant_number(+),
hre.npw_number(+))
) = 'TRUE'
AND DECODE (hr_general.get_xbg_profile,
'Y', hre.business_group_id(+),
hr_general.get_business_group_id
) = hre.business_group_id(+)
AND ood.organization_id(+) = rti.from_organization_id
AND DECODE (rti.source_document_code,
'PO', 'PO TYPE',
'SHIPMENT SOURCE TYPE'
) = plc4.lookup_type
AND DECODE (rti.transaction_type,
'UNORDERED', 'STANDARD',
DECODE (rti.source_document_code,
'PO', ph.type_lookup_code,
rsh.receipt_source_code
)
) = plc4.lookup_code
AND plc5.lookup_type = 'TRANSACTION STATUS'
AND plc5.lookup_code = rti.transaction_status_code
AND plc6.lookup_type = 'RCV PROCESSING MODE'
AND plc6.lookup_code = rti.processing_mode_code
AND fcv.currency_code(+) = rti.currency_code
AND pj.job_id(+) = rti.job_id
AND pja.job_id(+) = rti.job_id
AND NVL (ph.org_id, -99) = NVL (pll.org_id, -99);