AR Data Fusion to EBS Report

 AR Data Fusion to EBS Report

WITH 

   Bill_To_Cust AS 

   (SELECT  /*+ materialize */

        hzp.party_name, 

        hzp.party_number                 ,

        hzp.party_id                     ,

        hzps.PARTY_SITE_NUMBER           ,

        HZA.account_number               ,

        HZA.Account_name                 ,

        hza.CUST_ACCOUNT_ID              ,

        hzps.party_site_id               ,

        hcsu.site_use_id                 ,

hcsu.STATUS ,

        hcas.start_Date                  ,

        hcas.end_Date                    ,

        hcsu.SITE_USE_CODE               ,

        hcsu.LOCATION                    ,

        hzl.LOCATION_ID

FROM    HZ_PARTIES HZP               ,

HZ_PARTY_SITES hzps          ,

HZ_CUST_ACCOUNTS HZA         ,

HZ_CUST_ACCT_SITES_ALL hcas  ,

HZ_CUST_SITE_USES_ALL hcsu   ,

hz_locations HZL

WHERE   hzP.party_id                 = HZA.party_id

AND hza.CUST_ACCOUNT_ID      = hcas.CUST_ACCOUNT_ID

AND hcas.party_site_id     = hzps.party_site_id

AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

AND hzps.location_id         = hzl.location_id

AND hcsu.site_use_code     = 'BILL_TO' 

),

Ship_To_Cust_SU AS 

   ( SELECT /*+ materialize */

            hcsu.site_use_id, hcsu.Location, hpsu.PARTY_SITE_USE_ID,

hcsu.STATUS    ,

hcsu.attribute2,    -- Sub Corp Code

        hcsu.attribute4,    -- DSA Flag

            hcsu.attribute6,    -- Bill Source Code

        hcsu.attribute5,    -- Ship Credit Flag

hcsu.attribute8, -- Customer Class Code 

hcsu.attribute12,    -- region_code

hcsu.attribute13, -- sales_country_cd

        hcsu.attribute21 -- handling_code

        FROM HZ_CUST_SITE_USES_ALL hcsu,

     HZ_CUST_ACCT_SITES_ALL hcas,

HZ_PARTY_SITE_USES hpsu

          WHERE 1 = 1

    AND hcsu.site_use_code  = 'SHIP_TO'

AND hpsu.SITE_USE_TYPE = hcsu.site_use_code

AND hcas.PARTY_SITE_ID = hpsu.PARTY_SITE_ID

AND hcas.CUST_ACCT_SITE_ID = hcsu.CUST_ACCT_SITE_ID

),

ESI_ItemData AS 

   ( SELECT /*+ materialize */

        esi.Item_Number          item_number, 

esi.inventory_item_status_code Item_Status,

esi.INVENTORY_ITEM_ID,  

substr(eie.ATTRIBUTE_CHAR3, 1, 4) Item_pti4 -- pti4

     FROM EGP_SYSTEM_ITEMS_B esi,

EGO_ITEM_EFF_B eie,

             inv_org_parameters iop

        WHERE 1 = 1

   AND esi.organization_id    = iop.organization_id-- 300000338928296

           AND iop.organization_code = 'GLO' -- master organization code

   AND esi.INVENTORY_ITEM_ID = eie.INVENTORY_ITEM_ID 

   AND esi.organization_id = eie.organization_id 

   AND eie.CONTEXT_CODE IN ('XXON_FIN_ATT_AG')   -- Modified on 5Jun 'XXON_SITE_ATTRIBUTES''ORAMAP_DFF_ATTRIBUTE', 

),

SO_Details as

    ( SELECT dha.order_number, dla.line_number || '~' || dfl.FULFILL_LINE_NUMBER sales_order_line_line, 

dla.line_number, dfl.FULFILL_LINE_NUMBER,

         dha.CUSTOMER_PO_NUMBER, dha.ORDER_TYPE_CODE, dle.ATTRIBUTE_CHAR14 Quote_Number, 

dfle.attribute_char7 IPN,

dha.Header_id, dla.Line_id, to_char(dfl.FULFILL_LINE_ID) FULFILL_LINE_ID

     FROM DOO_HEADERS_ALL dha, 

      DOO_LINES_ALL dla,

      DOO_FULFILL_LINES_ALL dfl,

  DOO_LINES_EFF_B dle, 

  doo_fulfill_lines_eff_b dfle

WHERE dla.Header_id = dha.Header_id

  AND dfl.Line_Id = dla.Line_id 

  AND dla.LINE_ID = dle.LINE_ID (+)

  AND dle.CONTEXT_CODE (+) = 'Additional Line Information'

  AND dfle.fulfill_line_id (+) = dfl.fulfill_line_id

  AND dfle.CONTEXT_CODE (+) = 'Additional Line Information'

),

Curr_Precision as

    ( select /*+ materialize */

    precision from FND_CURRENCIES_B 

where 1 = 1

  AND enabled_flag = 'Y' 

  AND currency_flag = 'Y' 

  AND currency_code = 'USD'

),

RevAcct_DistDetails as

( SELECT DISTINCT rgdrev.ACCTD_AMOUNT, rgdrev.GL_DATE, rgdrev.LAST_UPDATE_DATE,

gccrev.Segment6, gccrev.Segment3, rgdrev.GL_POSTED_DATE,

rgdrev.CUSTOMER_TRX_LINE_ID,

rgdrev.CUSTOMER_TRX_ID

FROM   RA_CUST_TRX_LINE_GL_DIST_ALL rgdrev,

gl_code_combinations    gccrev,

xla_distribution_links xdl,

xla_ae_lines xal,

gl_import_references gir,

gl_je_lines gjl

where 1 = 1

AND rgdrev.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1 -- Link RA to XLA_DISTRIBUTION_LINKS

AND rgdrev.EVENT_ID = xdl.EVENT_ID

AND xdl.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

AND xdl.LINE_DEFINITION_CODE

IN ('XXON_AR_INV_DEFAULT_ACCRUAL', 'XXON_AR_DM_DEFAULT_ACCRUAL', 'XXON_AR_CM_DEFAULT_ACCRUAL')

AND rgdrev.ACCOUNT_CLASS = 'REV'

AND xdl.ae_header_id = xal.ae_header_id

AND xdl.ae_line_num = xal.ae_line_num

AND xal.gl_sl_link_id = gir.gl_sl_link_id 

AND gir.je_header_id = gjl.je_header_id

AND gir.je_line_num = gjl.je_line_num

AND rgdrev.SET_OF_BOOKS_ID = gjl.LEDGER_ID

AND gccrev.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID

--and rgdrev.CUST_TRX_LINE_GL_DIST_ID = 539090

)

SELECT 

       '1' as Key, 

   TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')) PROCESS_BATCH_ID,

   RCT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,

   RCTL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID,

       CASE

          WHEN rtt.type = 'INV' then 'Invoice'

          WHEN rtt.type = 'DM' then 'Debit Memo'

          WHEN rtt.type = 'CM' then 'Credit Memo'

        END activity, 

   stsu.attribute6  bill_source_cd, 

   stsu.attribute8 business_class_cd,

   btc.account_number account_number,

   rtt.Attribute1 credit_reason_cd,

   rtt.Description trx_type_descr,

   rtt.Name trx_type,

   rctl.unit_selling_price trx_unit_price,

   rctl.Attribute13 price_source, -- 21July2025 Changed to attribute13

   rct.trx_date transaction_date,

   stsu.attribute13 sales_country_cd,

   stsu.location ship_to_cust_cd,

   btc.location bill_to_cust_cd,

   stsu.attribute2 sub_corp_cd,

   CASE

          when nvl(stsu.attribute8, 'BLANK') in ('INT-SPS', 'OMD', 'BLANK') 

  THEN 'N'

          ELSE NVL(rtt.attribute5,'N')

       END true_bill_flg,

       ROUND(DECODE (rct.Invoice_Currency_Code, 'USD', rctl.UNIT_SELLING_PRICE, 

      rctl.UNIT_SELLING_PRICE * NVL((SELECT conversion_rate

     FROM gl_daily_rates gdr

     WHERE 1 = 1

       AND gdr.conversion_type = 'Corporate'

       AND gdr.conversion_date = TRUNC(rct.trx_date)

       AND gdr.FROM_CURRENCY = rct.Invoice_Currency_Code

       AND gdr.TO_CURRENCY = 'USD'

       AND ROWNUM = 1),1)

), 4) trx_unit_price_usd,

   stsu.attribute13 bill_to_country,

   NULL ship_from_area,

   --rctl.interface_line_attribute4 cust_part_num,

   DECODE (rtt.type, 'INV', rctl.interface_line_attribute10, 

rctl.ATTRIBUTE9) cust_part_num, -- 21July2025 Changed has per GM-10523

   NVL(sod.CUSTOMER_PO_NUMBER, rct.PURCHASE_ORDER) purchase_order, 

   ROUND( rad.ACCTD_AMOUNT, 

                (SELECT PRECISION FROM Curr_Precision)) trx_amt_usd,

   nvl(rctl.quantity_invoiced, rctl.quantity_credited) trx_line_qty,

   rctl.Extended_Amount trx_line_amt,

   --rctl.sales_order_line sales_order_line, -- Changed back on 5Jun

   DECODE (rtt.type, 'INV', rctl.sales_order_line, 

rctl.ATTRIBUTE11) sales_order_line, -- 21July2025 Changed for DM/CM GM-10523

   --sod.sales_order_line_line sales_order_line_line, -- Changed back on 5Jun

   DECODE (rtt.type, 'INV', sod.sales_order_line_line, 

rctl.ATTRIBUTE12) sales_order_line_line, -- 21July2025 Changed for DM/CM GM-10523

   --sod.line_number sales_order_line, -- Changed on 21May 

   --sod.FULFILL_LINE_NUMBER sales_order_line_line, -- Changed on 21May 

   sod.ORDER_TYPE_CODE     order_type, 

   eid.Item_Number          item_number,

   sod.Quote_Number quote_id,

   --rctl.sales_order sales_order,

   DECODE (rtt.type, 'INV', rctl.sales_order,

rctl.ATTRIBUTE10) sales_order, -- 21July2025 Changed for DM/CM GM-10523

   rctl.line_number trx_line_num,

   hou.short_code operating_unit,

   btc.location sold_to_cust_cd,

   stsu.attribute5 ship_credit_flag,

   stsu.attribute4 dsa_flag,

   rct.INVOICE_CURRENCY_CODE transaction_currency,

   stsu.attribute12 region_code,

   stsu.attribute21 handling_code,

   rctl.description part_description,

   rad.Segment6          pti3, -- COA segment 6

   Item_pti4 pti4,

   eid.Item_Status Item_Status,

   CASE 

          WHEN stsu.attribute12 = 'EO' 

    THEN   nvl(to_char(rct.doc_sequence_value), rct.trx_number)

            ELSE rct.trx_number

       END billing_number,

   NVL(rtt.Attribute2, 'N') reversal_required_flag,

   rtt.Attribute11 autoreversal_credit_account,

   rtt.Attribute3 autoreversal_debit_account,

   rad.Segment3 gl_rev_acct, 

   gccrec.Segment3 gl_rec_acct,

   NVL(rad.GL_DATE, rct.TRX_DATE) gl_posted_date,

   NULL related_invoice_number,

   rtt.attribute12 autoreversal_je_description,

   nvl(rtt.attribute13, 'N') obr_ruleset_dcd_accrual_flag,

   nvl(rtt.attribute14, 'N') obr_ruleset_dsc_accrual_flag,

   nvl(rtt.attribute15, 'N') obr_ruleset_dsa_accrual_flag,

   nvl(rtt.attribute10, 'N') obr_ruleset_reb_accrual_flag,

   nvl(rtt.attribute8, 'N') quantity_override_flag,

   (SELECT gp.PERIOD_NAME FROM GL_PERIODS gp

          WHERE 1 = 1

            AND gp.PERIOD_SET_NAME = '4-4-5'

AND rad.GL_DATE BETWEEN gp.START_DATE AND gp.END_DATE

            AND ROWNUM = 1) period_name,

   hou.set_of_books_id set_of_books_id,

   hou.short_code BU,

   stsu.attribute8 attribute8,  -- Customer class code at customer Ship to

   stsu.status status,

   rct.TRX_NUMBER TRX_NUMBER, 

   rct.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID, 

   rbs.NAME Batch_Name,

   'NEW' PROCESSING_STATUS,

   NVL(rct.ship_to_site_use_id, stsu.site_use_id) SHIP_TO_SITE_USE_ID, 

   stsu.PARTY_SITE_USE_ID SHIP_TO_PARTY_SITE_USE_ID,

   sod.IPN INTERNAL_PART_NUMBER -- Added on 5Jun

  FROM RA_CUSTOMER_TRX_ALL rct,

       RA_CUSTOMER_TRX_LINES_ALL rctl,

       hr_operating_units hou, 

   ra_cust_trx_types_all      rtt,

   Ship_To_Cust_SU stsu,

   Bill_To_Cust btc,

   --RA_CUST_TRX_LINE_GL_DIST_ALL rgdrev,

   --gl_code_combinations    gccrev,

   RevAcct_DistDetails rad,

   RA_CUST_TRX_LINE_GL_DIST_ALL rgdrec,

   gl_code_combinations    gccrec,

   ESI_Itemdata     eid,

   RA_BATCH_SOURCES_ALL rbs,

   SO_Details sod

 WHERE 1 = 1

    AND rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID

AND rct.ORG_ID = hou.Organization_Id

    AND btc.CUST_ACCOUNT_ID = rct.BILL_TO_CUSTOMER_ID

AND rct.CUST_TRX_TYPE_SEQ_ID    = rtt.CUST_TRX_TYPE_SEQ_ID

    AND btc.site_use_id          = rct.bill_to_site_use_id

    AND stsu.PARTY_SITE_USE_ID      = rct.ship_to_party_site_use_id

AND rctl.CUSTOMER_TRX_LINE_ID = rad.CUSTOMER_TRX_LINE_ID 

AND rct.CUSTOMER_TRX_ID = rad.CUSTOMER_TRX_ID

--AND rgdrev.ACCOUNT_CLASS = 'REV'

--AND rgdrev.CODE_COMBINATION_ID  = gccrev.CODE_COMBINATION_ID

AND rct.CUSTOMER_TRX_ID = rgdrec.CUSTOMER_TRX_ID

AND rgdrec.ACCOUNT_CLASS = 'REC'

AND rgdrec.CODE_COMBINATION_ID  = gccrec.CODE_COMBINATION_ID

AND eid.INVENTORY_ITEM_ID = rctl.INVENTORY_ITEM_ID

AND rct.BATCH_SOURCE_SEQ_ID = rbs.BATCH_SOURCE_SEQ_ID

AND rctl.interface_line_attribute5 = sod.FULFILL_LINE_ID (+)

AND rctl.LINE_TYPE = 'LINE'

AND rct.COMPLETE_FLAG = 'Y'

AND rbs.NAME NOT IN ('XXON_Conversions', 'Manual') 

AND (rad.GL_POSTED_DATE IS NOT NULL AND rgdrec.GL_POSTED_DATE IS NOT NULL) -- Modified on 20June. Check only posted transactions to be extracted

AND (rct.LAST_UPDATE_DATE  > SYSDATE - :p_offset_days 

OR rctl.LAST_UPDATE_DATE  > SYSDATE - :p_offset_days

OR rad.LAST_UPDATE_DATE > SYSDATE - :p_offset_days 

OR rgdrec.LAST_UPDATE_DATE > SYSDATE - :p_offset_days )

AND rct.TRX_NUMBER = NVL(:p_Trx_Number, rct.TRX_NUMBER)

ORDER BY RCT.CUSTOMER_TRX_ID, RCTL.CUSTOMER_TRX_LINE_ID

--- End of SQL