Friday 24 January 2014

p2p query from requistion

SELECT prha.segment1 "Req Num",
       pha.segment1 "Po Num",
       pla.line_num "PO Line Num",
       pla.quantity "Line QTY",
       plla.quantity "Shipment QTY",
       ood.organization_code  Org_Code,
       ood.organization_name  Org_Name,
       rsh.receipt_num "Receipt Num",
       aia.invoice_num
FROM
        PO_REQUISITION_HEADERS_ALL PRHA,
        PO_REQUISITION_LINES_ALL PRLA,
        PO_REQ_DISTRIBUTIONS_ALL PRDA,
        PO_DISTRIBUTIONS_ALL PDA,
        PO_HEADERS_ALL PHA,
        PO_LINES_ALL PLA,
        PO_LINE_LOCATIONS_ALL PLLA,
        ORG_ORGANIZATION_DEFINITIONS OOD,
        RCV_SHIPMENT_LINES RSL,
        RCV_SHIPMENT_HEADERS RSH,
        AP_INVOICES_ALL   AIA,
        AP_INVOICE_LINES_ALL AILA
WHERE
     prha.segment1 = '14439'
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 pda.po_line_id = pla.po_line_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = rsl.po_line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND aia.invoice_id(+)= aila.invoice_id
AND plla.line_location_id =aila.po_line_location_id(+)
AND plla.ship_to_organization_id = ood.organization_id


No comments:

Post a Comment