Hi ,
I am trying to fetch all the open transfer orders present for a particular supplier in oracle fusion.
I am trying to mainly join : inv_transfer_order_headers , inv_transfer_order_lines , por_requisition_headers_all, por_requisition_lines_all , POZ_SUPPLIERS_V tables.
But still I am not able to get the proper data as required and I feel that there is a problem in the joins.
Below is my code:
SELECT
itol.requested_quantity as Quantity
itoh.header_number AS Oracle_TO,
prla.DELIVER_TO_LOCATION_ID ,
prla.DESTINATION_ORGANIZATION_ID,
prla.DESTINATION_TYPE_CODE,
pozs.vendor_name as supplier_name,
pha.po_header_id,
pla.po_line_id,
esib.item_number AS item,
PRLA.REQUESTER_ID
FROM
inv_transfer_order_headers itoh
,inv_transfer_order_lines itol
,por_requisition_headers_all prha
,por_requisition_lines_all prla
,po_headers_all pha
,POZ_SUPPLIERS_V pozs
,PO_DISTRIBUTIONS_ALL pda
,POR_REQ_DISTRIBUTIONS_ALL prda
,egp_system_items_vl esib
, inv_org_parameters iop
WHERE 1=1
AND itoh.header_id = itol.header_id
AND itol.inventory_item_id = esib.inventory_item_id
AND iop.organization_id = esib.organization_id
AND itol.REQUISITION_ID = prha.REQUISITION_HEADER_ID
AND PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID
AND PDA.PO_HEADER_ID = pha.PO_HEADER_ID
AND pha.PO_HEADER_ID = pla.PO_HEADER_ID
AND pha.vendor_id = pozs.vendor_id
--AND prha.attribute3 = 'USED'
AND itol.status_lookup='OPEN'