SCM: OM: SQL Query: Querying Customer, SHIP_TO and BILL_TO Information on an Order

The following SQL can be run from BI to obtain Customer information.

SCM: OM: SQL Query: Querying Customer, SHIP_TO and BILL_TO Information on an Order 

SELECT  dha.ORDER_NUMBER       ,

        dha.source_order_number,

        dha.SOLD_TO_PARTY_ID   ,

        dha.STATUS_CODE        ,

        hz.PARTY_ID            ,

        hz.PARTY_NUMBER        ,

        hz.PARTY_NAME

FROM    fusion.doo_headers_all dha,

        fusion.HZ_PARTIES HZ

WHERE   dha.SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')

        --        AND status_code <> 'DOO_REFERENCE'

        --        AND Submitted_Flag = 'Y'            -- is this the active/submitted version

         and hz.PARTY_ID =dha.SOLD_TO_PARTY_ID

 

Find SHIP TO information on Order Header

------------------------------------------

 SELECT  SOURCE_ORDER_NUMBER,

        SOLD_TO_CUSTOMER_ID,

        SOLD_TO_PARTY_ID   ,

        HZP.PARTy_name

        ||

        ' '

        ||

        HZP.PARTY_NUMBER "Sold to Customer",

        DOA.ADDRESS_USE_TYPE               ,

        hza.account_number                 ,

        hzp_ship_to.party_name             ,

        hza.account_name                   ,

        doa.PARTY_SITE_ID                  ,

                hzl.ADDRESS1                                    ,

        hzl.ADDRESS2                                    ,

        hzl.ADDRESS3                                    ,

        hzl.ADDRESS4                                    ,

        hzl.CITY                                        ,

        hzl.POSTAL_CODE                                 ,

        hzl.STATE                                       ,

        hzl.COUNTRY

FROM    FUSION.HZ_PARTIES HZP          ,

        FUSION.HZ_PARTIES HZP_SHIP_TO  ,

        FUSION.DOO_HEADERS_aLL DHA     ,

        fusion.DOO_ORDER_ADDRESSES DOA ,

        fusion.HZ_CUST_ACCOUNTS HZA    ,

        fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,

        fusion.HZ_PARTY_SITES hzps          ,

        fusion.hz_locations HZL

WHERE   HZP.PARTY_ID      = DHA.SOLD_TO_PARTY_ID

        AND dha.header_id = doa.header_id (+)

        AND

        (

                doa.ADDRESS_USE_TYPE     = 'SHIP_TO'

                OR doa.ADDRESS_USE_TYPE IS NULL

        )

        AND doa.party_site_id        = hzps.party_site_id (+)

        AND hzcasa.PARTY_SITE_ID (+) = hzps.PARTY_SITE_ID

        AND hzps.party_id            = hzp_ship_to.party_id (+)

        AND HZcasa.CUST_ACCounT_ID   = hza.CUST_ACCOUNT_ID (+)

        AND hzps.location_id         = hzl.location_id (+)

        AND DHA.SOURCE_ORDER_NUMBER  = ('&SOURCE_ORDER_NUMBER')

        AND DHA.status_code          <> 'DOO_REFERENCE'

        AND DHA.Submitted_Flag         = 'Y' -- is this the active/submitted version

Find BILL TO information on Order Header

------------------------------------------

SELECT  SOURCE_ORDER_NUMBER,

        SOLD_TO_CUSTOMER_ID,

        SOLD_TO_PARTY_ID   ,

        HZP.PARTy_name

        ||

        ' '

        ||

        HZP.PARTY_NUMBER "Sold to Customer",

        DOA.ADDRESS_USE_TYPE               ,

        hza.account_number                 ,

        hza.account_name                   ,

        doa.CUST_ACCT_ID                   ,

        doa.CUST_ACCT_SITE_USE_ID          ,

        hzl.ADDRESS1                       ,

        hzl.ADDRESS2                       ,

        hzl.ADDRESS3                       ,

        hzl.ADDRESS4                       ,

        hzl.CITY                           ,

        hzl.POSTAL_CODE                    ,

        hzl.STATE                          ,

        hzl.COUNTRY

FROM    FUSION.HZ_PARTIES HZP               ,

        FUSION.DOO_HEADERS_aLL DHA          ,

        fusion.DOO_ORDER_ADDRESSES DOA      ,

        fusion.HZ_CUST_ACCOUNTS HZA         ,

        fusion.HZ_CUST_SITE_USES_ALL hzcsua ,

        fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,

        fusion.HZ_PARTY_SITES hzps          ,

        fusion.hz_locations HZL

WHERE   HZP.PARTY_ID      = DHA.SOLD_TO_PARTY_ID

        AND dha.header_id = doa.header_id (+)

        AND

        (

                doa.ADDRESS_USE_TYPE     = 'BILL_TO'

                OR doa.ADDRESS_USE_TYPE IS NULL

        )

        AND DOA.CUST_ACCT_ID          = hza.CUST_ACCOUNT_ID (+)

        AND DOA.CUST_ACCT_SITE_USE_ID = hzcsua.SITE_USE_ID(+)

        AND hzcsua.CUST_ACCT_SITE_ID  = hzcasa.CUST_ACCT_SITE_ID (+)

        AND hzcasa.PARTY_SITE_ID      = hzps.PARTY_SITE_ID (+)

        AND hzps.location_id          = hzl.location_id (+)

        AND DHA.SOURCE_ORDER_NUMBER   = ('&SOURCE_ORDER_NUMBER')

--        AND DHA.status_code          <> 'DOO_REFERENCE'

--        AND DHA.Submitted_Flag         = 'Y' -- is this the active/submitted version

Find BILL TO  and SHIP TO information on Order Line

----------------------------------------------------

SELECT  dha.Source_order_number   ,

        dha.order_number          ,

        dha.submitted_Flag        ,

        dfla.SHIP_TO_PARTY_ID     ,

        dfla.SHIP_TO_PARTY_SITE_ID,

        dfla.BILL_TO_CUSTOMER_ID  ,

        dfla.BILL_TO_SITE_USE_ID

FROM    Fusion.DOO_headers_all dha,

        Fusion.DOO_fulfill_lines_all dfla

WHERE   dha.header_id               = dfla.header_id

        AND dha.source_order_number = '&ENTER SOURCE_ORDER_NUMBER'

------------

select * from doo_headers_all where ORDER_NUMBER=:p_order_number

and SUBMITTED_FLAG='Y'

select * from hz_parties where party_id=100000000395803

select * from fun_all_business_units_v where organization_id=300000046987012

select *  FROM   xle_entity_profiles xle  where legal_entity_id=300000046973970

select * from DOO_ORDER_ADDRESSES where header_id=300000128617431