1 Reply Latest reply on Feb 7, 2017 1:43 PM by raghu_ram

    Need Accrual Amount and Accounts

    Srikanth Mandadi

      Team,

       

      I need develop a query to include PO information, Item and bring in Accrual & Charged Accounts along with Accrual Amount.

      Can you help me link these tables, I did create initial draft of query but I want to double check if I am doing it right.

       

      Below is attached query -

       

      SELECT

        POH.PO_HEADER_ID

      , POH.SEGMENT1 PO_NUMBER

      , POH.ORG_ID

      , POL.PO_LINE_ID

      , PODA.PO_DISTRIBUTION_ID 

      , POL.LINE_NUM PO_LINE

      , POL.LINE_TYPE_ID

      , POLT.LINE_TYPE LINE_TYPE

      , MSIB.SEGMENT1 ITEM_NUMBER

      , POL.ITEM_DESCRIPTION ITEM_DESCRIPTION

      , ael.ACCOUNTING_CLASS_CODE

      , GCC_PO.SEGMENT1||'-'||GCC_PO.SEGMENT2||'-'||GCC_PO.SEGMENT3||'-'||GCC_PO.SEGMENT4||'-'||GCC_PO.SEGMENT5||'-'||GCC_PO.SEGMENT6 CHAR_ACCOUNT_STRING

      , GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6 ACCR_ACCOUNT_STRING

      , SUP.VENDOR_NAME VENDOR_NAME

      , RCVSH.RECEIPT_NUM RECEIPT_NUMBER

      , RCVT.QUANTITY RECEIVED_QUANTITY

      , POL.UNIT_MEAS_LOOKUP_CODE UNIT_OF_MEASURE

      , POL.UNIT_PRICE PURCHASE_ORDER_UNIT_PRICE

      , POH.CURRENCY_CODE PURCHASE_ORDER_CURRENCY

      , NVL(RCVT.QUANTITY_BILLED,0) BILLED_QUANTITY

      , RCVT.AMOUNT_BILLED

      , (RCVT.QUANTITY-NVL(RCVT.QUANTITY_BILLED,0)) ACCRUED_QTY

      , (RCVT.QUANTITY-NVL(RCVT.QUANTITY_BILLED,0))*POL.UNIT_PRICE ACCRUED_AMOUNT

      FROM APPS.PO_HEADERS_ALL POH

      ,APPS.PO_LINES_ALL POL

      ,APPS.PO_LINE_TYPES_TL POLT

      ,APPS.PO_LINE_LOCATIONS_ALL POLL

      ,APPS.MTL_SYSTEM_ITEMS_B MSIB

      ,AP.AP_SUPPLIERS SUP

      ,APPS.PO_DISTRIBUTIONS_ALL PODA

      ,APPS.RCV_SHIPMENT_HEADERS RCVSH

      ,APPS.RCV_TRANSACTIONS RCVT

      ,APPS.GL_CODE_COMBINATIONS GCC

      ,APPS.RCV_RECEIVING_SUB_LEDGER RCVRSL

      ,APPS.xla_ae_lines ael

      ,APPS.xla_distribution_links xdl 

      ,APPS.xla_ae_headers aeh

      ,APPS.GL_CODE_COMBINATIONS GCC_PO

      WHERE

      --PO Joins

      POH.PO_HEADER_ID           = POL.PO_HEADER_ID

      AND POL.LINE_TYPE_ID             = POLT.LINE_TYPE_ID(+)

      AND POLT.LANGUAGE                = 'E'

      AND POL.PO_LINE_ID               = POLL.PO_LINE_ID(+)

      AND POL.ITEM_ID                  = MSIB.INVENTORY_ITEM_ID(+)

      AND POL.ORG_ID                  = MSIB.INVENTORY_ITEM_ID(+)

      --AND POLL.SHIP_TO_ORGANIZATION_ID = MSIB.ORGANIZATION_ID

      AND POH.VENDOR_ID                = SUP.VENDOR_ID

      AND POL.PO_HEADER_ID             = PODA.PO_HEADER_ID

      AND POL.PO_LINE_ID               = PODA.PO_LINE_ID

      --RCV Joins

      AND PODA.PO_DISTRIBUTION_ID    = RCVT.PO_DISTRIBUTION_ID

      AND RCVT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID

      AND RCVT.TRANSACTION_TYPE        = 'RECEIVE'

      AND RCVRSL.RCV_TRANSACTION_ID(+)=RCVT.TRANSACTION_ID

      AND RCVRSL.REFERENCE3=RCVT.PO_DISTRIBUTION_ID

      AND RCVRSL.ACCOUNTING_LINE_TYPE IN ('Accrual','Charge')

      --SLA Joins

      AND aeh.ae_header_id = xdl.ae_header_id

      --AND aeh.EVENT_TYPE_CODE='PERIOD_END_ACCRUAL'

      AND aeh.GL_TRANSFER_STATUS_CODE='Y'

      AND aeh.ae_header_id = ael.ae_header_id

      AND ael.ae_header_id = xdl.ae_header_id

      AND ael.ae_line_num = xdl.ae_line_num

      AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'

      --GCC Joins

      AND ael.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

      AND GCC_PO.CODE_COMBINATION_ID= PODA.CODE_COMBINATION_ID

      --SLA and RCV Joins

      AND xdl.source_distribution_id_num_1 = RCVRSL.rcv_sub_ledger_id

      AND ael.LEDGER_ID = RCVRSL.SET_OF_BOOKS_ID

      --Parameetrs

      --AND aeh.PERIOD_NAME='DEC-16'

      --AND POH.ORG_ID='141'

      ;

       

      Regards,

      Srikanth