Friday 28 October 2016

Purchase order with Budget account query in oracle apps

SELECT   prh.SEGMENT1 "PO_Rrequisition_ #",
         Prl.LINE_NUM "PO_Rrequisition_Line_ #",
         (Prl.UNIT_PRICE * Prl.QUANTITY) "PO_Rrequisition_Amount",
         Prd.GL_ENCUMBERED_DATE "PO_Req_Encumbrance_Date",
         Prd.GL_ENCUMBERED_PERIOD_NAME "PO_Req_Period",
         Prd.ENCUMBERED_FLAG "Req_Encum_Flag",
         Prd.ENCUMBERED_AMOUNT "Req_Encum_Amount",
         glkfv.SEGMENT5 "Account",
         glkfv.CONCATENATED_SEGMENTS "Concat_Seg",
         poh.SEGMENT1 "PO_ #",
         pol.LINE_NUM "PO_Line_ #",
         (pol.UNIT_PRICE * pol.QUANTITY) "PO_Amount",
         Pod.GL_ENCUMBERED_DATE "PO_Encumbrance_Date",
         Pod.GL_ENCUMBERED_PERIOD_NAME "PO_Period",
         pol.CLOSED_DATE,
         pol.CLOSED_FLAG,
         Pod.ENCUMBERED_FLAG "PO_Encum_Flag",
         Pod.ENCUMBERED_AMOUNT "PO_Encum_Amount"
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         PO_REQ_DISTRIBUTIONS_INQ_V PORV,
         po_distributions_all pod,
         po_headers_all poh,
         po_lines_all pol,
         GL_CODE_COMBINATIONS_kfv glkfv
 WHERE       1 = 1
         AND prh.requisition_header_id = prl.requisition_header_id
         AND prl.requisition_line_id = prd.requisition_line_id
         AND prd.DISTRIBUTION_ID = PORV.DISTRIBUTION_ID
         AND prd.distribution_id = pod.req_distribution_id(+)
         AND pol.po_line_id(+) = pod.po_line_id
         AND poh.po_header_id(+) = pol.po_header_id
        AND glkfv.CODE_COMBINATION_ID = PORV.BUDGET_ACCOUNT_ID
         AND glkfv.SEGMENT5 IN ('00110011')

exec MO_GLOBAL.INIT('PO');

No comments:

Post a Comment