Link PO Lines and Requisition Lines SQL Statement
581392Aug 9 2007 — edited Aug 10 2007Hi Everyone.
I'm trying to link Requisition lines and Purchase Order lines with an sql statment. So far I've managed to link the PO header to the requisition lines but I'm getting a cartesian product when I try to add the PO lines (po_lines_all) and link it to the PO Header (po_headers_all). The cartisian product consists of all requisitions repeating for each po_line. Can anybody please tell me which join I am missing? This would help me greatly. Thanks to all of you in advance.
Kurt
Here is the SQL that is returning the cartesian product. I need it to return 8 lines but it returns 64.:
SELECT pofg.DOCUMENT_NUMBER Po_No,
prha.segment1 Requisition_No,
prla.line_num req_line_no,
pp.FULL_NAME Requisitio_Name,
pp.PERSON_ID,
pofg.SUPPLIER_NAME Vendor_Name,
pofg.SUPPLIER_ID Vendor_No,
pofg.SUPPLIER_SITE_ID Vendor_Site_ID,
pofg.CREATED_DATE Order_Date,
prla.LINE_NUM PO_line_no,
prla.ITEM_DESCRIPTION Line_Item_Desc
FROM po.po_requisition_headers_all prha,
po.po_requisition_lines_all prla,
po.po_req_distributions_all prda,
po_distributions_all pda,
pofg_purchase_orders pofg,
per_people_f pp,
HR_ALL_ORGANIZATION_UNITS op,
po_lines_all pol
WHERE 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 = pofg.purchase_order_id
AND prha.PREPARER_ID = pp.PERSON_ID
AND op.ORGANIZATION_ID = prha.ORG_ID
and pol.PO_HEADER_ID = pofg.PURCHASE_ORDER_ID
AND prha.segment1 = 16616