Query To Get Bill To and Ship To details from Install Base (IB) Item Instance Number in Oracle Apps R12

 

--Query To Get Bill To and Ship To details from Install Base (IB) Item Instance Number

SELECT

    CIP.INSTANCE_ID         INSTANCE_NUMBER,

    CIP.SERIAL_NUMBER       SERIAL_NUMBER,

    --Bill To

    HZP1.PARTY_ID            BILL_TO_PARTY_ID,

    HZP1.PARTY_NUMBER        BILL_TO_PARTY_NUMBER,

    HZP1.PARTY_NAME          BILL_TO_PARTY_NAME,

    HPS1.PARTY_SITE_ID       BILL_TO_PARTY_SITE_ID,

    HPS1.PARTY_SITE_NUMBER   BILL_TO_PARTY_SITE_NUMBER,

    HL1.address1||' '||HL1.address2||', '||HL1.city||' '||HL1.state||' '||HL1.country||' '||HL1.postal_code BILL_TO_ADDRESS,

    CIP.BILL_TO_ADDRESS BILL_TO,

    --Ship To

    HZP2.PARTY_ID            SHIP_TO_PARTY_ID,

    HZP2.PARTY_NUMBER        SHIP_TO_PARTY_NUMBER,

    HZP2.PARTY_NAME          SHIP_TO_PARTY_NAME,

    HPS2.PARTY_SITE_ID       SHIP_TO_PARTY_SITE_ID,

    HPS2.PARTY_SITE_NUMBER   SHIP_TO_PARTY_SITE_NUMBER,

    HL2.address1||' '||HL2.address2||', '||HL2.city||' '||HL2.state||' '||HL2.country||' '||HL2.postal_code SHIP_TO_ADDRESS,

    CIP.SHIP_TO_ADDRESS SHIP_TO,

    CIP.LAST_UPDATE_DATE IB_LAST_UPDATE_DATE

FROM

    apps.CSI_INSTANCE_PARTY_V CIP,

    apps.HZ_PARTIES HZP1,

    apps.HZ_PARTY_SITES HPS1,

    apps.HZ_LOCATIONS HL1,

    apps.HZ_CUST_ACCT_SITES_ALL HCASA1,

    apps.HZ_CUST_SITE_USES_ALL HCSUA1,

    apps.HZ_PARTIES HZP2,

    apps.HZ_PARTY_SITES HPS2,

    apps.HZ_LOCATIONS HL2,

    apps.HZ_CUST_ACCT_SITES_ALL HCASA2,

    apps.HZ_CUST_SITE_USES_ALL HCSUA2

WHERE 1=1

--Bill To

AND CIP.BILL_TO_ADDRESS      = HCSUA1.SITE_USE_ID

AND HZP1.PARTY_ID            = HPS1.PARTY_ID

AND HPS1.LOCATION_ID         = HL1.LOCATION_ID

AND HPS1.PARTY_SITE_ID       = HCASA1.PARTY_SITE_ID

AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID

--Ship To

AND CIP.SHIP_TO_ADDRESS      = HCSUA2.SITE_USE_ID

AND HZP2.PARTY_ID            = HPS2.PARTY_ID

AND HPS2.LOCATION_ID         = HL2.LOCATION_ID

AND HPS2.PARTY_SITE_ID       = HCASA2.PARTY_SITE_ID

AND HCSUA2.CUST_ACCT_SITE_ID = HCASA2.CUST_ACCT_SITE_ID

AND CIP.INSTANCE_ID          = '28196679'