Sunday 16 March 2014

Request set WIzard

1.Dvelop The Control files and package   and then Cretate The Concurent Programs
And The Collect The all the concurrent programs
2.Goto to the System Administator Responsibility àConcurrentàSet    and then enter
3.
4.and then goto ‘Request Set Wizard’


5.Click Next [here Sequentially is one by one executing][In parallel is All at a time Execute]
6. Give The Set name and  Application name(payables) and description
     [here Abort Processing is any one Program is error  Remaining Programs not executing it’s automatically stoped]
    [here Continue Processing is any one Program is error  Remaining Programs are executing it’s
Automatically Processing]
7. and then Click Next
8. and then Click Next  and Then Give the ‘Concurrent program Header ’
 And also Give the ‘Concurrent program Lines’
And  also Give the ‘Concurrent program Package ‘
9.Click the Finish and It Auto Maticaly Save then form  and see the below
10.And then Goto
11.And Close above window
12.And Clock The LinkStages
13.And Then click the DONE option AND COPY Set Name::à AP XX Invoice Interface Requestprogram’
14.And  ADD the Above set name to REQUEST
SecurityàResponsibilityàrequest
15.ADD the Specipic Responsibility
16.Here Select The TYPE name:-SET
And  Paste SET name::­à AP XX Invoice Interface Requestprogram….
17.Save and Close the form and
18.Goto Specific Responsibility
19.Here we select ‘REQUEST SET’      and click Ok
-=  = = = --



20.
Here we Give the Request Set name::- AP XX Invoice Interface Requestprogram
And Then Submit..
21.And See The output  here ..
. ‘Concurrent program Header ’
 ‘Concurrent program Lines’
 ‘Concurrent program Package’ Programs are Running..
22.Check The data in Interface Tables …If you ADD ‘Payables Open Interface Import’
DATA   AutoMaticaly store Base Tables..


CONTRACT PURCHASING


SELECT  PHA.SEGMENT1 PONUM,
        PHA.TYPE_LOOKUP_CODE,
        PHA1.SEGMENT1  STDPOUM,
        PHA1.TYPE_LOOKUP_CODE,
        MSIB.SEGMENT1 ITEM,
        APS.VENDOR_NAME,
        APSSA.VENDOR_SITE_CODE,
        PHA.AMOUNT_LIMIT,
        HL1.LOCATION_CODE,
        HL2.LOCATION_CODE,
        HOU.NAME ORGNAME,
        PLA.QUANTITY,
        PLA.UNIT_PRICE,
        PLLA.NEED_BY_DATE,
        PDA.CODE_COMBINATION_ID,
        GCCK.CONCATENATED_SEGMENTS,
        RSH.RECEIPT_NUM,
        RSL.DESTINATION_TYPE_CODE,
        RT.DESTINATION_TYPE_CODE,
        AIA.INVOICE_NUM,
        AIA.INVOICE_AMOUNT,
        AILA.LINE_TYPE_LOOKUP_CODE,
        AILA.LINE_NUMBER,
        AIDA.DISTRIBUTION_LINE_NUMBER,
        AIDA.AMOUNT,
        AIPA.INVOICE_PAYMENT_ID,
        AIPA.AMOUNT,
        AIPA.CHECK_ID,
        ACA.BANK_ACCOUNT_NAME,
        ACA.BANK_ACCOUNT_NUM
FROM    PO_HEADERS_ALL                 PHA,
        PO_HEADERS_ALL                 PHA1,
        PO_LINES_ALL                   PLA,
        PO_LINE_LOCATIONS_ALL          PLLA,
        PO_DISTRIBUTIONS_ALL           PDA,
        GL_CODE_COMBINATIONS_KFV       GCCK,
        MTL_SYSTEM_ITEMS_B             MSIB,
        AP_SUPPLIERS                   APS,
        AP_SUPPLIER_SITES_ALL          APSSA,
        HR_OPERATING_UNITS             HOU,
        RCV_SHIPMENT_HEADERS           RSH,
        RCV_SHIPMENT_LINES             RSL,
        RCV_TRANSACTIONS               RT,
        AP_INVOICES_ALL                AIA,
        AP_INVOICE_LINES_ALL           AILA,
        AP_INVOICE_DISTRIBUTIONS_ALL   AIDA,
        AP_INVOICE_PAYMENTS_ALL        AIPA,
        AP_CHECKS_ALL                  ACA,
        HR_LOCATIONS                   HL1,
        HR_LOCATIONS                   HL2
WHERE   PHA.SEGMENT1='6050'
AND     RT.DESTINATION_TYPE_CODE='INVENTORY'
AND     PHA1.PO_HEADER_ID=PLA.PO_HEADER_ID
AND     PLA.CONTRACT_ID=PHA.PO_HEADER_ID
AND     PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND     PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND     PDA.CODE_COMBINATION_ID=GCCK.CODE_COMBINATION_ID
AND     MSIB.INVENTORY_ITEM_ID=PLA.ITEM_ID
AND     MSIB.ORGANIZATION_ID=PHA.ORG_ID
AND     PHA.ORG_ID=HOU.ORGANIZATION_ID
AND     RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND     RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND     RSL.PO_LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID
AND     AIA.INVOICE_ID=AILA.INVOICE_ID
AND     AILA.LINE_NUMBER=AIDA.INVOICE_LINE_NUMBER
AND     AIDA.INVOICE_ID=AIA.INVOICE_ID
AND     AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND     AIA.INVOICE_ID=AIPA.INVOICE_ID
AND     AIPA.CHECK_ID=ACA.CHECK_ID
AND     HL1.LOCATION_ID=PHA.SHIP_TO_LOCATION_ID
AND     HL2.LOCATION_ID=PHA.BILL_TO_LOCATION_ID
AND     APS.VENDOR_ID=PHA.VENDOR_ID
AND     APSSA.VENDOR_SITE_ID=PHA.VENDOR_SITE_ID



SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6050'

SELECT * FROM PO_HEADERS_ALL  WHERE SEGMENT1='6051'

SELECT * FROM PO_LINES_ALL WHERE CONTRACT_ID=110410


SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6050'

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='6051'

SELECT  PLA.CONTRACT_ID
FROM    PO_LINES_ALL    PLA,
        PO_HEADERS_ALL  PHA
WHERE   CONTRACT_ID=110410
AND     PHA.PO_HEADER_ID=PLA.CONTRACT_ID

BLANKET



 SELECT PHA.SEGMENT1,
        PHA.BLANKET_TOTAL_AMOUNT,
        PHA.AMOUNT_LIMIT,
        PLA.ITEM_DESCRIPTION,
        PDA.CODE_COMBINATION_ID,
        PLLA.NEED_BY_DATE,
        PLLA.PRICE_OVERRIDE,
        PLLA.QUANTITY,
        PRA.RELEASE_NUM,
        RSH.RECEIPT_NUM,
        AIA.INVOICE_NUM,
        AIA.INVOICE_AMOUNT,
        AILA.LINE_TYPE_LOOKUP_CODE,
        AILA.LINE_NUMBER,
        AIDA.DISTRIBUTION_LINE_NUMBER,
        AIDA.AMOUNT,
        AIPA.AMOUNT,
        AIPA.CHECK_ID,
        APC.BANK_ACCOUNT_NAME,
        APC.BANK_ACCOUNT_NUM
 FROM   PO_HEADERS_ALL  PHA,
        PO_LINES_ALL    PLA,
        PO_LINE_LOCATIONS_ALL PLLA,
        PO_DISTRIBUTIONS_ALL   PDA,
        PO_RELEASES_ALL        PRA,
        RCV_SHIPMENT_HEADERS   RSH,
        RCV_SHIPMENT_LINES     RSL,
        AP_INVOICES_ALL         AIA,
        AP_INVOICE_LINES_ALL   AILA,
        AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
        AP_INVOICE_PAYMENTS_ALL  AIPA,
        AP_CHECKS_ALL            APC
 WHERE  PHA.SEGMENT1='6044'
 AND    PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
 AND    PLA.PO_LINE_ID=PLLA.PO_LINE_ID
 AND    PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
 AND    PLLA.PO_RELEASE_ID=PRA.PO_RELEASE_ID
 AND    RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
 AND    PLLA.LINE_LOCATION_ID=RSL.PO_LINE_LOCATION_ID
 AND    AIA.INVOICE_ID=AILA.INVOICE_ID
 AND    AILA.LINE_NUMBER=AIDA.INVOICE_LINE_NUMBER
 AND    AIA.INVOICE_ID=AIDA.INVOICE_ID
 AND    AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
 AND    AIPA.INVOICE_ID=AIA.INVOICE_ID
 AND    AIPA.CHECK_ID=APC.CHECK_ID




SELECT * FROM PO_RELEASES_ALL


SELECT *  FROM PO_LINE_LOCATIONS_ALL

p2p o2c

Pencial   66459  14313  6066  23732 10037665  1,060.00

SELECT  OOHA.ORDER_NUMBER,
        OOHA.FLOW_STATUS_CODE,
        OOHA.ORDER_CATEGORY_CODE,
        OOLA.ORDERED_ITEM,
        ODSS.LINE_ID,
        ODSS.DROP_SHIP_SOURCE_ID,
        QLH.NAME,
        PRHA.SEGMENT1 REQNUM,
        PHA.SEGMENT1 PONUM,
        RSH.RECEIPT_NUM ,
        AIA.INVOICE_ID,
        AIPA.AMOUNT ,
        ACA.CHECK_ID,
        ACA.BANK_ACCOUNT_NAME
FROM    OE_ORDER_HEADERS_ALL        OOHA,
        OE_ORDER_LINES_ALL          OOLA,
        OE_DROP_SHIP_SOURCES        ODSS,
        QP_LIST_HEADERS             QLH,
        PO_REQUISITION_HEADERS_ALL  PRHA,
        PO_REQUISITION_LINES_ALL    PRLA,
        PO_REQ_DISTRIBUTIONS_ALL    PRDA,
        PO_HEADERS_ALL              PHA,
        PO_LINES_ALL                PLA,
        PO_LINE_LOCATIONS_ALL       PLLA,
        PO_DISTRIBUTIONS_ALL        PDA,
        RCV_SHIPMENT_HEADERS        RSH,
        RCV_SHIPMENT_LINES          RSL,
        HZ_PARTIES                  HP,
        HZ_CUST_ACCOUNTS            HCA,
        HZ_CUST_SITE_USES_ALL       HCSUA,
        HZ_LOCATIONS                HL,
        HZ_PARTY_SITES              HPS,
        HZ_CUST_ACCT_SITES_ALL      HCASA,
        RA_CUSTOMER_TRX_ALL         RCTA ,
        AR_PAYMENT_SCHEDULES_ALL    APSA,
        AP_INVOICES_ALL             AIA,
        AP_INVOICE_LINES_ALL        AILA,
        AP_INVOICE_DISTRIBUTIONS_ALL  AIDA,
        AP_INVOICE_PAYMENTS_ALL     AIPA,
        AP_CHECKS_ALL               ACA
WHERE   OOHA.ORDER_NUMBER=66459
AND     OOHA.HEADER_ID=OOLA.HEADER_ID
AND     OOLA.HEADER_ID=ODSS.HEADER_ID
AND     OOHA.PRICE_LIST_ID=QLH.LIST_HEADER_ID
AND     PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND     PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND     PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND     PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND     PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND     PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND     ODSS.PO_HEADER_ID=PHA.PO_HEADER_ID
AND     RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND     PLLA.LINE_LOCATION_ID=RSL.PO_LINE_LOCATION_ID
AND     HP.PARTY_ID=HCA.PARTY_ID
AND     HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
AND     HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID
AND     HL.LOCATION_ID=HPS.LOCATION_ID
AND     HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID
AND     HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
AND     TO_CHAR(OOHA.ORDER_NUMBER)=RCTA.INTERFACE_HEADER_ATTRIBUTE1
AND     RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND     AIA.INVOICE_ID=AILA.INVOICE_ID
AND     AILA.LINE_NUMBER=AIDA.INVOICE_LINE_NUMBER
AND     AIA.INVOICE_ID=AIDA.INVOICE_ID
AND     AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND     AIA.INVOICE_ID=AIPA.INVOICE_ID
AND     AIPA.CHECK_ID=ACA.CHECK_ID


SELECT * FROM OE_DROP_SHIP_SOURCES

BACK 2 BACK QUERY

backtoback item  6074-po   oeorder=66473 rqui-14322  INV--10037683   AMT --31.81

 SELECT  OOHA.ORDER_NUMBER
        ,OOLA.LINE_NUMBER
        ,OOLA.ORDERED_ITEM
        ,OOLA.FLOW_STATUS_CODE
        ,QLH.NAME
        ,HP.PARTY_NAME
        ,PRHA.SEGMENT1 REQNUM
        ,PRLA.LINE_NUM
        ,PHA.SEGMENT1 PONUM
       -- ,RSH.RECEIPT_NUM
        ,WDD.DELIVERY_DETAIL_ID
       -- ,RCTA.TRX_NUMBER
       -- ,RCTA.CUSTOMER_TRX_ID
        --,RCTLA.INTERFACE_LINE_ATTRIBUTE1
        --,RSA.SALESREP_NUMBER
      --  ,AIA.INVOICE_NUM
       -- ,AILA.AMOUNT
        --,AIPA.CHECK_ID
        --,ACA.BANK_ACCOUNT_NAME
        --,ACA.BANK_ACCOUNT_NUM
FROM     OE_ORDER_HEADERS_ALL        OOHA
        ,OE_ORDER_LINES_ALL          OOLA
        ,QP_LIST_HEADERS             QLH      
        ,HZ_PARTIES                  HP
        ,HZ_CUST_ACCOUNTS            HCA
        ,HZ_CUST_SITE_USES_ALL       HCSUA
        ,PO_REQUISITION_HEADERS_ALL  PRHA
        ,PO_REQUISITION_LINES_ALL    PRLA
        ,PO_REQ_DISTRIBUTIONS_ALL    PRDA
        ,PO_HEADERS_ALL              PHA
        ,PO_LINES_ALL                PLA
        ,PO_LINE_LOCATIONS_ALL       PLLA
        ,PO_DISTRIBUTIONS_ALL        PDA
        ,PO_RELEASES_ALL             PRL
        ,WSH_DELIVERY_DETAILS        WDD
        ,WSH_DELIVERY_ASSIGNMENTS    WDA
        ,RCV_SHIPMENT_HEADERS        RSH
        ,RCV_SHIPMENT_LINES          RSL
        --,RA_CUSTOMER_TRX_ALL         RCTA
        --,RA_CUSTOMER_TRX_LINES_ALL   RCTLA
        --,RA_SALESREPS_ALL            RSA
        --,AR_CASH_RECEIPTS_ALL         ACRA
        --,AR_RECEIVABLE_APPLICATIONS_ALL ARAA
       -- ,AP_INVOICES_ALL                 AIA
        --,AP_INVOICE_LINES_ALL            AILA
        --,AP_INVOICE_DISTRIBUTIONS_ALL    AIDA
        --,AP_INVOICE_PAYMENTS_ALL         AIPA
        --,AP_CHECKS_ALL                   ACA
 WHERE   OOHA.ORDER_NUMBER=66476
 AND     OOHA.HEADER_ID=OOLA.HEADER_ID
 AND     QLH.LIST_HEADER_ID=OOHA.PRICE_LIST_ID
 AND     HP.PARTY_ID=HCA.PARTY_ID
 AND     HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
 AND     HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID
 AND     PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
 AND     PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
 AND     OOLA.LINE_ID=PRHA.INTERFACE_SOURCE_LINE_ID
 AND     PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
 AND     PLA.PO_LINE_ID=PLLA.PO_LINE_ID
 AND     PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
 AND     PDA.REQ_DISTRIBUTION_ID=PRDA.DISTRIBUTION_ID
 AND     PRL.PO_RELEASE_ID=PLLA.PO_RELEASE_ID
 AND     WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
 AND     WDD.SOURCE_HEADER_ID=OOLA.HEADER_ID
 AND     RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
 AND     RSL.PO_LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID
-- AND     RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
 --AND     TO_CHAR(OOHA.ORDER_NUMBER)=RCTA.INTERFACE_HEADER_ATTRIBUTE1
 --AND     RSA.SALESREP_ID=OOHA.SALESREP_ID
 --AND     ACRA.CASH_RECEIPT_ID=ARAA.CASH_RECEIPT_ID
 --AND     ARAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID  
 --AND     AIA.INVOICE_ID=AILA.INVOICE_ID
-- AND     AILA.LINE_NUMBER=AIDA.INVOICE_LINE_NUMBER
-- AND     AIA.INVOICE_ID=AIDA.INVOICE_ID
--AND     AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
-- AND     AIPA.INVOICE_ID=AIA.INVOICE_ID
-- AND     AIPA.CHECK_ID=ACA.CHECK_ID



SELECT *FROM AR_CASH_RECEIPTS_ALL ORDER BY CREATION_DATE DESC      

SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL ORDER BY CREATION_DATE DESC

 select * from po_headers_all d where d.SEGMENT1='6074'

DROP SHIP

Pencial   66459  14313  6066  23732 10037665  1,060.00

SELECT  OOHA.ORDER_NUMBER,
        OOHA.FLOW_STATUS_CODE,
        OOHA.ORDER_CATEGORY_CODE,
        OOLA.ORDERED_ITEM,
        ODSS.LINE_ID,
        ODSS.DROP_SHIP_SOURCE_ID,
        QLH.NAME,
        PRHA.SEGMENT1 REQNUM,
        PHA.SEGMENT1 PONUM,
        RSH.RECEIPT_NUM ,
        AIA.INVOICE_ID,
        AIPA.AMOUNT ,
        ACA.CHECK_ID,
        ACA.BANK_ACCOUNT_NAME
FROM    OE_ORDER_HEADERS_ALL        OOHA,
        OE_ORDER_LINES_ALL          OOLA,
        OE_DROP_SHIP_SOURCES        ODSS,
        QP_LIST_HEADERS             QLH,
        PO_REQUISITION_HEADERS_ALL  PRHA,
        PO_REQUISITION_LINES_ALL    PRLA,
        PO_REQ_DISTRIBUTIONS_ALL    PRDA,
        PO_HEADERS_ALL              PHA,
        PO_LINES_ALL                PLA,
        PO_LINE_LOCATIONS_ALL       PLLA,
        PO_DISTRIBUTIONS_ALL        PDA,
        RCV_SHIPMENT_HEADERS        RSH,
        RCV_SHIPMENT_LINES          RSL,
        HZ_PARTIES                  HP,
        HZ_CUST_ACCOUNTS            HCA,
        HZ_CUST_SITE_USES_ALL       HCSUA,
        HZ_LOCATIONS                HL,
        HZ_PARTY_SITES              HPS,
        HZ_CUST_ACCT_SITES_ALL      HCASA,
        RA_CUSTOMER_TRX_ALL         RCTA ,
        AR_PAYMENT_SCHEDULES_ALL    APSA,
        AP_INVOICES_ALL             AIA,
        AP_INVOICE_LINES_ALL        AILA,
        AP_INVOICE_DISTRIBUTIONS_ALL  AIDA,
        AP_INVOICE_PAYMENTS_ALL     AIPA,
        AP_CHECKS_ALL               ACA
WHERE   OOHA.ORDER_NUMBER=66459
AND     OOHA.HEADER_ID=OOLA.HEADER_ID
AND     OOLA.HEADER_ID=ODSS.HEADER_ID
AND     OOHA.PRICE_LIST_ID=QLH.LIST_HEADER_ID
AND     PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND     PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND     PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND     PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND     PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND     PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND     ODSS.PO_HEADER_ID=PHA.PO_HEADER_ID
AND     RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND     PLLA.LINE_LOCATION_ID=RSL.PO_LINE_LOCATION_ID
AND     HP.PARTY_ID=HCA.PARTY_ID
AND     HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID
AND     HCSUA.SITE_USE_ID=OOHA.SHIP_TO_ORG_ID
AND     HL.LOCATION_ID=HPS.LOCATION_ID
AND     HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID
AND     HCSUA.CUST_ACCT_SITE_ID=HCASA.CUST_ACCT_SITE_ID
AND     TO_CHAR(OOHA.ORDER_NUMBER)=RCTA.INTERFACE_HEADER_ATTRIBUTE1
AND     RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND     AIA.INVOICE_ID=AILA.INVOICE_ID
AND     AILA.LINE_NUMBER=AIDA.INVOICE_LINE_NUMBER
AND     AIA.INVOICE_ID=AIDA.INVOICE_ID
AND     AIDA.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND     AIA.INVOICE_ID=AIPA.INVOICE_ID
AND     AIPA.CHECK_ID=ACA.CHECK_ID


SELECT * FROM OE_DROP_SHIP_SOURCES

P2P QUERY

SELECT  PRHA.SEGMENT1 REQNUM
       ,PHA.SEGMENT1  PONUM
       ,HL.LOCATION_CODE SHIPTO
       ,HL1.LOCATION_CODE BILTO
       ,APS.VENDOR_NAME
       ,APSSA.VENDOR_SITE_CODE
       ,APSC.FIRST_NAME
       ,APSC.LAST_NAME
       ,RT.TRANSACTION_TYPE
       ,RSH.RECEIPT_NUM PORECEIPTNUM
       ,AIA.INVOICE_NUM
      ---,XTE.ENTITY_CODE
FROM    PO_REQUISITION_HEADERS_ALL  PRHA
       ,PO_REQUISITION_LINES_ALL    PRLA
       ,PO_REQ_DISTRIBUTIONS_ALL    PRDA
       ,PO_DISTRIBUTIONS_ALL        PDA
       ,PO_LINE_LOCATIONS_ALL       PLLA
       ,PO_LINES_ALL                PLA
       ,PO_HEADERS_ALL              PHA
       ,HR_LOCATIONS                HL
       ,HR_LOCATIONS                HL1
       ,MTL_SYSTEM_ITEMS_B          MSIB
       ,AP_SUPPLIERS                APS
       ,AP_SUPPLIER_SITES_ALL       APSSA
       ,AP_SUPPLIER_CONTACTS        APSC
       ,RCV_TRANSACTIONS            RT
       ,RCV_SHIPMENT_LINES          RSL
       ,RCV_SHIPMENT_HEADERS        RSH
       ,AP_INVOICE_DISTRIBUTIONS_ALL AIDA
       ,AP_INVOICE_LINES_ALL         AILA
       ,AP_INVOICES_ALL              AIA
       ,AP_INVOICE_PAYMENTS_ALL      AIPA
       ,AP_CHECKS_ALL                ACA
       ,XLA_AE_HEADERS               XAH
       ,XLA_AE_LINES                 XAL
       ,XLA_EVENTS                   XE
       ,XLA_TRANSACTION_ENTITIES     XTE
       ,XLA_DISTRIBUTION_LINKS       XDL
       ,GL_IMPORT_REFERENCES         GIR
       ,GL_JE_BATCHES                GJB
       ,GL_JE_HEADERS                GJH
       ,GL_JE_LINES                  GJL    
WHERE   PRHA.SEGMENT1='14200'
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.LINE_LOCATION_ID=PLLA.LINE_LOCATION_ID
AND     PLLA.PO_LINE_ID=PLA.PO_LINE_ID
AND     PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND     HL.LOCATION_ID=PHA.SHIP_TO_LOCATION_ID
AND     HL1.LOCATION_ID=PHA.BILL_TO_LOCATION_ID
AND     MSIB.INVENTORY_ITEM_ID=PLA.ITEM_ID
AND     PHA.ORG_ID=MSIB.ORGANIZATION_ID
AND     PHA.VENDOR_ID=APS.VENDOR_ID
AND     PHA.VENDOR_SITE_ID=APSSA.VENDOR_SITE_ID
AND     APS.VENDOR_ID=APSSA.VENDOR_ID
AND     PHA.VENDOR_CONTACT_ID=APSC.VENDOR_CONTACT_ID
AND     PHA.PO_HEADER_ID=RT.PO_HEADER_ID
--AND     PLLA.PO_LINE_ID=RSL.PO_LINE_ID
--AND     PHA.PO_HEADER_ID=RSL.PO_HEADER_ID
AND     RT.TRANSACTION_TYPE='DELIVER'
AND     RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND     RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND     PDA.PO_DISTRIBUTION_ID=AIDA.PO_DISTRIBUTION_ID
AND     AIDA.INVOICE_LINE_NUMBER=AILA.LINE_NUMBER
AND     AIDA.INVOICE_ID=AILA.INVOICE_ID
AND     AILA.INVOICE_ID=AIA.INVOICE_ID
AND     AIA.INVOICE_ID=AIPA.INVOICE_ID
AND     AIPA.CHECK_ID=ACA.CHECK_ID
AND     ACA.CHECK_NUMBER=XTE.TRANSACTION_NUMBER
AND     AIPA.CHECK_ID=XTE.SOURCE_ID_INT_1
AND     AIA.ORG_ID=XTE.SECURITY_ID_INT_1
AND    XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND    XAH.EVENT_ID=XE.EVENT_ID
AND    XE.ENTITY_ID=XTE.ENTITY_ID
AND    XAL.AE_LINE_NUM=AIDA.INVOICE_LINE_NUMBER
AND    XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND    XDL.APPLIED_TO_DIST_ID_NUM_1=AIDA.INVOICE_DISTRIBUTION_ID
AND    GIR.REFERENCE_5=XTE.ENTITY_ID
AND    XTE.ENTITY_CODE='MANUAL'
AND    GIR.REFERENCE_6=TO_CHAR(XE.EVENT_ID)
AND    GIR.REFERENCE_7=TO_CHAR(XAH.AE_HEADER_ID)
AND    GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND    GJB.JE_BATCH_ID=GIR.JE_BATCH_ID
AND    GJH.JE_BATCH_ID=GJB.JE_BATCH_ID
AND    GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND    GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND    GJL.JE_LINE_NUM=GIR.JE_LINE_NUM