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

GL Report - GL,AP,AR Excel

GL Report  - GL,AP,AR  /Excel

Parameter :

1. Ledger Name
2. BU
3. Account
4. Division
5. From Date
6. To Date

SELECT ac.*,
       Nvl(ac.opening_balance, 0)
       + SUM(net_amount) over (PARTITION BY account ORDER BY
       account, ac.row_num)
           RUNNING_CLOSING,
       CASE
         WHEN Nvl(ac.opening_balance, 0)
              + SUM(Nvl(ac.amount_dr, 0) - Nvl(amount_cr, 0)) over (PARTITION BY
              account ORDER
              BY ledger_name, bu_code, division, account) > 0 THEN 'Dr'
         ELSE 'Cr'
       END balanceindicator
FROM   (SELECT bb.*,
               Nvl((SELECT SUM(Nvl (( Nvl(gjl.accounted_dr, 0) -
                                      Nvl(gjl.accounted_dr, 0) ), 0)
                           )
                    FROM   gl_je_headers gjh,
                           gl_je_lines gjl,
                           gl_code_combinations GCC
                    WHERE  1 = 1
                           AND GCC.code_combination_id = gjl.code_combination_id
                           AND gjl.ledger_id = bb.ledger_id
                           AND gcc.segment3 = bb.account
                           AND gjh.status = 'P'
                           AND gjl.je_header_id = gjh.je_header_id
                           AND gjl.effective_date <= :p_from_date), 0)
                      opening_balance,
               Row_number()
                 over (
                   PARTITION BY bb.account
                   ORDER BY bb.account, bb.transaction_date
                 , bb.transaction_number) AS
                      ROW_NUM
        FROM   (SELECT ledger_id,
                       ledger_name,
                       legal_entity,
                       account_description,
                       bu_code,
                       bu_nmae,
                       account,
                       division,
                       je_category_desc,
                       transaction_number,
                       transaction_date,
                       party_number,
                       party_name,
                       bill_no,
                       bill_date,
                       remarks,
                       SUM(amount_dr)  amount_dr,
                       SUM(amount_cr)  amount_cr,
                       SUM(net_amount) net_amount
                FROM   (SELECT 'Q1'
                               query,
                               gjl.ledger_id,
                               gl.name
                               ledger_name,
                               xe.name
                               legal_entity,
                               gl_flexfields_pkg.Get_description_sql (
                               gcc.chart_of_accounts_id,
                               3,
                               gcc.segment3
                               )
                                        account_description,
                               gcc.segment1
                               bu_code,
                               gl_flexfields_pkg.Get_description_sql (
                               gcc.chart_of_accounts_id,
                               1
                               , gcc.segment1
                               )
                               bu_nmae,
                               gcc.segment3
                               account,
                               gcc.segment6
                               division
                               ,
                               gjc.je_category_name
                               je_category_desc
                                       ,
                               gjh.name
                                        transaction_number,
                               To_char(gjh.date_created, 'DD-MON-RR')
                               transaction_date
                                       ,
                               NULL
                                       party_number,
                               NULL
                               party_name,
                               NULL
                               bill_no,
                               To_char(gjh.date_created, 'DD-MON-RR')
                               bill_date,
                               gjl.description
                               remarks,
                               Nvl(gjl.accounted_dr, 0)
                               amount_dr,
                               Nvl(gjl.accounted_cr, 0)
                               amount_cr,
                               ( Nvl(gjl.accounted_dr, 0) -
                                 Nvl(gjl.accounted_cr, 0) )
                               net_amount
                        FROM   gl_code_combinations gcc,
                               gl_ledgers gl,
                               gl_je_lines gjl,
                               gl_je_headers gjh,
                               gl_je_categories gjc,
                               gl_je_sources gjs,
                               xle_entity_profiles xe
                        WHERE  1 = 1
                               AND gjl.code_combination_id =
                                   gcc.code_combination_id
                               AND gl.ledger_id = gjl.ledger_id
                               AND gjl.je_header_id = gjh.je_header_id
                               AND gjh.je_category = gjc.je_category_name
                               AND gjh.je_source = gjs.je_source_name
                               AND gjh.legal_entity_id = xe.legal_entity_id(+)
                               AND gjs.je_source_name IN ( 'Manual' )
                               AND gjh.status = 'P'
                               --AND      gjh.NAME = 'Journal GL 01'
                               ---------------------- PARAMETERS ---------------------
                               AND ( gl.name IN ( :p_led )
                                      OR Coalesce(:p_led, NULL) IS NULL )
                               AND ( gcc.segment1 IN ( :p_bu )
                                      OR Coalesce(:p_bu, NULL) IS NULL )
                               --AND ( xe.name  IN (:P_LEGAL_ENTITY)
                               --      OR       COALESCE(:P_LEGAL_ENTITY,NULL) IS NULL)   
                               AND ( gcc.segment3 IN ( :p_account )
                                      OR Coalesce(:p_account, NULL) IS NULL )
                               AND ( gcc.segment6 IN ( :p_division )
                                      OR Coalesce(:p_division, NULL) IS NULL )
                               AND ( gjl.effective_date >= :p_from_date
                                      OR :p_from_date IS NULL )
                               AND ( gjl.effective_date <= :p_to_date
                                      OR :p_to_date IS NULL )
                        UNION
                        SELECT 'Q2'
                               query,
                               gjl.ledger_id,
                               gl.name
                               ledger_name,
                               xe.name
                               legal_entity
                               ,
                               gl_flexfields_pkg.Get_description_sql (
                               gcc.chart_of_accounts_id, 3,
                               gcc.segment3
                               )
                               account_description,
                               gcc.segment1
                               bu_code,
                               gl_flexfields_pkg.Get_description_sql (
                               gcc.chart_of_accounts_id,
                               1
                               , gcc.segment1
                               )
                               bu_nmae,
                               gcc.segment3
                               account,
                               gcc.segment6
                               division,
                               gjc.je_category_name
                               je_category_desc,
                               rcta.trx_number
                               transaction_number,
                               To_char(rcta.trx_date, 'DD-MON-RR')
                               transaction_date,
                               hp.party_number
                               party_Number
                               ,
                               hp.party_name
                               PartyName,
                               rcta.trx_number
                               bill_no,
                               To_char(rcta.trx_date, 'DD-MON-RR')
                               bill_date,
                               gjl.description
                               remarks,
                               Nvl(gjl.accounted_dr, 0)
                               amount_dr,
                               Nvl(gjl.accounted_cr, 0)
                               amount_cr,
                               Nvl(gjl.accounted_dr, 0) -
                               Nvl(gjl.accounted_cr, 0)
                               net_amount
                        FROM   ra_customer_trx_all rcta,
                               hz_cust_accounts hcaa,
                               hz_cust_acct_sites_all hcasa,
                               hz_cust_site_uses_all hcsua,
                               hz_parties hp,
                               hz_party_sites hps,
                               xla_transaction_entities xte,
                               xla_events xev,
                               xla_ae_headers xah,
                               xla_ae_lines xal,
                               gl_import_references gir,
                               gl_je_headers gjh,
                               gl_je_lines gjl,
                               gl_code_combinations gcc,
                               gl_je_categories gjc,
                               gl_je_sources gjs,
                               gl_ledgers gl,
                               xle_entity_profiles xe,
                               hr_operating_units hou
                        WHERE  1 = 1
                               AND gjl.code_combination_id =
                                   gcc.code_combination_id
                               AND gl.ledger_id = gjl.ledger_id
                               AND rcta.bill_to_customer_id =
                                   hcaa.cust_account_id
                               AND hcaa.party_id = hp.party_id
                               AND hcaa.cust_account_id = hcasa.cust_account_id
                               AND hcasa.cust_acct_site_id =
                                   hcsua.cust_acct_site_id
                               AND hcsua.site_use_id = rcta.bill_to_site_use_id
                               AND hcasa.party_site_id = hps.party_site_id
                               AND rcta.customer_trx_id = xte.source_id_int_1
                               AND xte.application_id = 222
                               AND xev.entity_id = xte.entity_id
                               AND xah.entity_id = xte.entity_id
                               AND xah.event_id = xev.event_id
                               AND xah.ae_header_id = xal.ae_header_id
                               AND xal.gl_sl_link_id = gir.gl_sl_link_id
                               AND gir.gl_sl_link_table = xal.gl_sl_link_table
                               AND gjl.je_header_id = gjh.je_header_id
                               AND gjh.je_header_id = gir.je_header_id
                               AND gjl.je_header_id = gir.je_header_id
                               AND gir.je_line_num = gjl.je_line_num
                               AND gjh.je_category = gjc.je_category_name
                               AND gjh.je_source = gjs.je_source_name
                               AND GJC.je_category_name = 'Sales Invoices'
                               AND gjs.je_source_name = 'Receivables'
                               AND ( gjl.accounted_cr <> 0
                                      OR gjl.accounted_dr <> 0 )
                               AND ( xal.accounted_cr <> 0
                                      OR xal.accounted_dr <> 0 )
                               AND gjh.status = 'P'
                               AND gjh.legal_entity_id = xe.legal_entity_id
                               AND rcta.org_id = hou.organization_id
                               -- AND RCTA.trx_number     = '211000010000090'
                               ---------------------- PARAMETERS ---------------------
                               AND ( gl.name IN ( :p_led )
                                      OR Coalesce(:p_led, NULL) IS NULL )
                               AND ( gcc.segment1 IN ( :p_bu )
                                      OR Coalesce(:p_bu, NULL) IS NULL )
                               AND ( xe.name IN ( :P_LEGAL_ENTITY )
                                      OR Coalesce(:P_LEGAL_ENTITY, NULL) IS NULL
                                   )
                               AND ( gcc.segment3 IN ( :p_account )
                                      OR Coalesce(:p_account, NULL) IS NULL )
                               AND ( gcc.segment6 IN ( :p_division )
                                      OR Coalesce(:p_division, NULL) IS NULL )
                               AND ( gjl.effective_date >= :p_from_date
                                      OR :p_from_date IS NULL )
                               AND ( gjl.effective_date <= :p_to_date
                                      OR :p_to_date IS NULL )) aa
                GROUP  BY ledger_id,
                          ledger_name,
                          legal_entity,
                          account_description,
                          bu_code,
                          bu_nmae,
                          account,
                          division,
                          je_category_desc,
                          transaction_number,
                          transaction_date,
                          party_number,
                          party_name,
                          bill_no,
                          bill_date,
                          remarks) bb) ac