Thursday 19 January 2017

Find the PO (Purchase Order) to Inovice and Payment Query in Oracle Apps EBS R12

----============ PO TO INVOICE, PAYMENT QUERY ========================---

SELECT 'PHA', PHA.PO_HEADER_ID,PHA.SEGMENT1 PO_NUM, PHA.VENDOR_ID, PHA.VENDOR_SITE_ID,PHA.SHIP_TO_LOCATION_ID,PHA.BILL_TO_LOCATION_ID, PHA.CURRENCY_CODE,PHA.AUTHORIZATION_STATUS,
       'PLA', PLA.PO_LINE_ID, PLA.ITEM_ID, PLA.ITEM_DESCRIPTION, PLA.UNIT_MEAS_LOOKUP_CODE,PLA.UNIT_PRICE, PLA.QUANTITY,PLA.ORG_ID,
       'PDA', PDA.PO_DISTRIBUTION_ID, PDA.SET_OF_BOOKS_ID, PDA.CODE_COMBINATION_ID, PDA.QUANTITY_BILLED, PDA.QUANTITY_CANCELLED, PDA.QUANTITY_DELIVERED, PDA.QUANTITY_ORDERED, PDA.AMOUNT_BILLED, PDA.DESTINATION_ORGANIZATION_ID,
       'PLLA',PLLA.LINE_LOCATION_ID, PLLA.QUANTITY, PLLA.QUANTITY_ACCEPTED, PLLA.QUANTITY_BILLED, PLLA.QUANTITY_CANCELLED, PLLA.QUANTITY_REJECTED, PLLA.QUANTITY_SHIPPED, PLLA.QTY_RCV_TOLERANCE, PLLA.SHIP_TO_LOCATION_ID, PLLA.SHIP_TO_ORGANIZATION_ID,
       'APS', APS.VENDOR_ID, APS.VENDOR_NAME,APS.VENDOR_TYPE_LOOKUP_CODE,APS.PARTY_ID, APS.TCA_SYNC_VENDOR_NAME,
       'APSS',APSS.VENDOR_ID, APSS.VENDOR_SITE_ID, APSS.VENDOR_SITE_CODE, APSS.ADDRESS_LINE1, APSS.ADDRESS_LINE2, APSS.ADDRESS_LINE3, APSS.ADDRESS_LINE4, APSS.CITY, APSS.STATE, APSS.COUNTRY, APSS.LOCATION_ID, APSS.PARTY_SITE_ID,
       'RSH', RSH.SHIPMENT_HEADER_ID, RSH.VENDOR_ID,RSH.RECEIPT_NUM, RSH.SHIP_TO_LOCATION_ID, RSH.SHIP_FROM_LOCATION_ID,RSH.SHIP_TO_ORG_ID,
       'RSL', RSL.SHIPMENT_LINE_ID,RSL.SHIPMENT_HEADER_ID, RSL.QUANTITY_SHIPPED, RSL.QUANTITY_RECEIVED, RSL.ITEM_ID, RSL.ITEM_DESCRIPTION,RSL.SHIP_TO_LOCATION_ID,
       'RT',  RT.TRANSACTION_DATE, RT.TRANSACTION_ID, RT.TRANSACTION_TYPE, RT.QUANTITY, RT.QUANTITY_BILLED, RT.UNIT_OF_MEASURE, RT.PRIMARY_QUANTITY,RT.AMOUNT, RT.AMOUNT_BILLED,
       'API', API.INVOICE_ID, API.INVOICE_NUM, API.INVOICE_AMOUNT, API.AMOUNT_PAID,API.INVOICE_DATE, API.GL_DATE, API.LEGAL_ENTITY_ID, API.PAYMENT_METHOD_CODE,
       'APILA',APILA.INVENTORY_ITEM_ID, APILA.ITEM_DESCRIPTION,APILA.QUANTITY_INVOICED,
       'APIDA', APIDA.ACCOUNTING_DATE, APIDA.ACCRUAL_POSTED_FLAG, APIDA.INVOICE_ID, APIDA.LINE_TYPE_LOOKUP_CODE, APIDA.RCV_TRANSACTION_ID, APIDA.ACCOUNTING_EVENT_ID,
       'APIPA', APIPA.ACCOUNTING_EVENT_ID,APIPA.CHECK_ID,APIPA.INVOICE_PAYMENT_ID , APIPA.BANK_ACCOUNT_NUM,
       'APCHK', APCHK.AMOUNT, APCHK.BANK_ACCOUNT_NAME, APCHK.CHECK_DATE, APCHK.CHECK_ID, APCHK.CHECK_NUMBER, APCHK.VENDOR_NAME, APCHK.PAYMENT_ID,
       'XLATE', XLATE.*
FROM   PO_HEADERS_ALL        PHA,
       PO_LINES_ALL          PLA,
       PO_DISTRIBUTIONS_ALL  PDA,
       PO_LINE_LOCATIONS_ALL PLLA,
       AP_SUPPLIERS          APS,
       AP_SUPPLIER_SITES_ALL APSS,
       RCV_SHIPMENT_HEADERS  RSH,
       RCV_SHIPMENT_LINES    RSL,      
       RCV_TRANSACTIONS      RT,
       AP_INVOICES_ALL       API,
       AP_INVOICE_LINES_ALL  APILA,
       AP_INVOICE_DISTRIBUTIONS_ALL APIDA,
       AP_INVOICE_PAYMENTS_ALL  APIPA,
       AP_CHECKS_ALL  APCHK,
       XLA.XLA_TRANSACTION_ENTITIES XLATE ---xla_transaction_entities
WHERE  PHA.PO_HEADER_ID      = PLA.PO_HEADER_ID
AND    PHA.PO_HEADER_ID      = PDA.PO_HEADER_ID
AND    PLA.PO_LINE_ID        = PDA.PO_LINE_ID
AND    PLLA.PO_HEADER_ID     = PLA.PO_HEADER_ID
AND    PLLA.PO_LINE_ID       = PLA.PO_LINE_ID
AND    APS.VENDOR_ID         = PHA.VENDOR_ID
AND    APS.VENDOR_ID         = APSS.VENDOR_ID
AND    RT.PO_HEADER_ID       = PHA.PO_HEADER_ID
AND    RT.PO_LINE_ID         = PLA.PO_LINE_ID
AND    RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND    RT.SHIPMENT_LINE_ID   = RSL.SHIPMENT_LINE_ID
AND    RSH.SHIPMENT_HEADER_ID= RSL.SHIPMENT_HEADER_ID
AND    APILA.PO_HEADER_ID    = PHA.PO_HEADER_ID
AND    APILA.PO_LINE_ID      = PLA.PO_LINE_ID
AND    APILA.INVOICE_ID      = API.INVOICE_ID
AND    APIDA.INVOICE_ID      = APILA.INVOICE_ID
AND    APIDA.INVOICE_LINE_NUMBER = APILA.LINE_NUMBER
AND    APIPA.INVOICE_ID         = API.INVOICE_ID
AND    APCHK.CHECK_ID          = APIPA.CHECK_ID
AND    XLATE.SECURITY_ID_INT_1 = API.ORG_ID
AND    XLATE.SOURCE_ID_INT_1 = APIPA.CHECK_ID
AND    PHA.SEGMENT1      = 12130009--12130008

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...