Reconciliation of CM/Payables to GL

Reconciliation of CM/Payables to GL


SELECT  gh.je_source journal_source
,(select name from gl_je_batches where je_batch_id = gh.je_batch_id)  batch_name 
,gh.name journal_name 
,gcc.concatenated_segments, gcc.CODE_COMBINATION_ID,
gjl.period_name,
null invoice_number,
null invoice_date,
(select segment1 from po_headers_all poh
,rcv_shipment_lines rsl
where poh.po_header_id = rsl.po_header_id
and rsl.shipment_line_id = jtdf.TRX_LINE_ID) po_number,
(select pol.line_num from po_headers_all poh,po_lines_all pol
,rcv_shipment_lines rsl
where poh.po_header_id = rsl.po_header_id
and poh.po_header_id = pol.po_header_id
and rsl.po_line_id = pol.po_line_id
and rsl.shipment_line_id = jtdf.TRX_LINE_ID) line_number,
(select pll.shipment_num from po_headers_all poh,po_lines_all pol,
po_line_locations_all pll
,rcv_shipment_lines rsl
where poh.po_header_id = rsl.po_header_id
and poh.po_header_id = pol.po_header_id
and rsl.po_line_id = pol.po_line_id
and pll.po_header_id = poh.po_header_id
and pll.po_line_id = pol.po_line_id
and rsl.shipment_line_id = jtdf.TRX_LINE_ID) shipment_number,
--jtdf.TRX_LOC_LINE_ID,
jtdf.trX_NUMBER RECEIPT_NUMBER,
jje.accounted_dr xla_accounted_dr,
jje.accounted_cr xla_accounted_cr                   
                    , gjl.accounted_dr ledger_dr
                    , gjl.accounted_cr ledger_cr                                 
                    ,gh.je_category journal_category
                    ,jtdf.TRX_TYPE rcv_transaction_type
                     ,TO_CHAR (gh.default_effective_date, 'DD-MON-YYYY') gl_date
                    ,TO_CHAR (gh.posted_date, 'DD-MON-YYYY') posted_date
                    ,(select segment1 from ap_suppliers  where vendor_id =  jtdf.party_id) party_code
                    ,(select vendor_name from ap_suppliers  where vendor_id =  jtdf.party_id) party_name
                    ,(select vendor_site_code from ap_supplier_sites_all  where vendor_site_id =  jtdf.party_site_id) party_site
                    ,NULL item_code                                     
FROM apps.jai_tax_journal_entries jje ,
apps.jai_tax_det_factors jtdf ,
apps.jai_tax_lines jtl,
apps.gl_je_headers gh,
apps.gl_je_lines gjl,
gl_code_combinations_kfv gcc
WHERE user_je_source_name ='Financials India'
--AND user_je_category_name IN ('PO Receiving','Delivery','Return Receive')
and jje.code_combination_id IN (43256,1935900, 1607449)
AND jtdf.det_factor_id =jje.tax_det_factor_id
AND jtl.tax_line_id =jje.tax_line_id
AND jtl.tax_rate_code NOT LIKE '%FREIGHT%'
--and jtl.rounded_tax_amt_fun_curr =8100
and gh.je_header_id =gjl.je_header_id
AND gh.JE_SOURCE ='Financials India'
--AND gh.je_category IN ('PO Receiving','Delivery','Return Receive')
AND gjl.reference_3 =TO_CHAR(jje.tax_det_factor_id)
--AND gh.name ='Delivery INR 2'
and gcc.code_combination_id = gjl.code_combination_id
and gjl.code_combination_id IN (43256,1935900, 1607449)
AND (( gjl.entered_dr =gjl.accounted_dr
AND NVL(gjl.entered_dr,0) <>0
AND NVL(gjl.accounted_dr,0)<>0)
OR ( gjl.entered_Cr =gjl.accounted_Cr
AND NVL(gjl.entered_Cr,0) <>0
AND NVL(gjl.accounted_Cr,0)<>0) )
UNION
(SELECT       GJH.je_source journal_source
,GJB.name batch_name 
,GJH.name journal_name                     
,GLCC.concatenated_segments segments
,glcc.code_combination_id
                    ,GJL.period_name
                    , NULL INVOICE_NUMBER 
                    , NULL INVOICE_DATE
                    ,(SELECT        pha.segment1 "po number"             
                        FROM        po_headers_all PHA,
                                    rcv_shipment_lines  RSL1,
                                    rcv_shipment_headers RSH1
                       WHERE     1=1
                         AND     PHA.po_header_id       = RSL1.po_header_id
                         AND     RSL1.shipment_header_id= RSH1.shipment_header_id
                         AND     RSH1.shipment_header_id= RSH.shipment_header_id
                         AND     rownum=1) po_number   
                         ,pla.line_num line_number         
                         ,pll.shipment_num shipment_number                               
                    ,RSH.receipt_num receipt_number                   
                    ,XDL.unrounded_accounted_dr xla_accounted_dr
                    ,xdl.unrounded_accounted_cr xla_accounted_cr                   
                    , gjl.accounted_dr ledger_dr
                    , gjl.accounted_cr ledger_cr                                 
                    ,GJH.je_category journal_category
                    /*,GLCC.segment1 entity_segment
                    ,GLCC.segment2 project_segment
                    ,FFV.attribute1 project_vertical_dff
                    ,GLCC.segment3
                    ,GLCC.segment4
                    ,GLCC.segment5
                    ,GLCC.segment6
                    ,GLCC.segment7*/
                    ,rt.TRANSACTION_TYPE rcv_transaction_type
                    /*,TO_CHAR (GJH.doc_sequence_value) gl_doc_no*/
                    ,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date
                    ,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date
                    --,XAH.event_type_code sla_event_type
                    --,TO_CHAR (RSH.creation_date, 'DD-MON-YYYY') shipment_creation_date
                   -- ,APS.vendor_name party_name                                     
                   -- ,APSA.vendor_site_code party_site   
                       , (SELECT aps.segment1   
                           FROM apps.ap_suppliers aps
                          WHERE aps.vendor_id = rsh.vendor_id) party_code
                          ,(SELECT REPLACE(REPLACE(aps.vendor_name, CHR(10)), CHR(13))
                           FROM apps.ap_suppliers aps
                          WHERE aps.vendor_id = rsh.vendor_id) party_name
                          ,(SELECT apsa.vendor_site_code
                           FROM apps.ap_supplier_sites_all apsa
                          WHERE apsa.vendor_id = rsh.vendor_id
                            AND apsa.vendor_site_id = rsh.vendor_site_id)
                                                                   party_site
                   /* ,GLCC.segment2 project_code                                       
                    ,NULL task_code                                             
                    ,NULL expenditure_type */           
                    ,NULL item_code                                     
                   -- ,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) transaction_cur_amount
                    /*,PHA.currency_code entered_currency_code
                    ,PHA.rate_type exchange_rate_type                             
                    ,PHA.rate exchange_rate  */                               
                   -- ,NVL((XDL.unrounded_accounted_cr) * -1, XDL.unrounded_accounted_dr) functional_currency_amount                   
      FROM    gl_je_batches GJB,
                     gl_je_headers GJH,
                     gl_je_lines GJL,
                     gl_import_references GIR,
                     gl_code_combinations_kfv GLCC,
                     xla_ae_headers XAH,
                     xla_ae_lines XAL,
                     xla_distribution_links XDL,
                     rcv_receiving_sub_ledger RSL,
                     rcv_transactions RT,
                     rcv_shipment_headers RSH,
                    -- rcv_shipment_lines rsl1,
                     po_line_locations_all PLL,
                     po_headers_all PHA,
                     po_lines_all PLA,
                     fnd_flex_value_sets FVS,
                     fnd_flex_values FFV
       WHERE          1 = 1
        AND          GJH.je_batch_id                           = GJB.je_batch_id
        AND          GLCC.code_combination_id         = GJL.code_combination_id
        AND          GJH.je_header_id                         = GJL.je_header_id
        AND          GJH.je_batch_id                           = GIR.je_batch_id
        AND          GJL.je_header_id                         = GIR.je_header_id
        AND          GJL.je_line_num                          = GIR.je_line_num
        AND          GIR.gl_sl_link_table                     = XAL.gl_sl_link_table
        AND          GIR.gl_sl_link_id                          = XAL.gl_sl_link_id
        AND          XAH.ae_header_id                      = XAL.ae_header_id
        AND          XAH.application_id                      = XAL.application_id
        AND          XDL.ae_header_id                       = XAL.ae_header_id
        AND          XDL.ae_line_num                        = XAL.ae_line_num
        AND          XDL.source_distribution_type     = 'RCV_RECEIVING_SUB_LEDGER'
        AND          RSL.rcv_sub_ledger_id               = XDL.source_distribution_id_num_1
        AND          RSL.rcv_transaction_id               = RT.transaction_id
        AND          RT.shipment_header_id              = RSH.shipment_header_id
        AND          RT.po_line_location_id                = PLL.line_location_id
        AND          PLL.PO_HEADER_ID                 = PHA.PO_HEADER_ID
        AND          PHA.PO_HEADER_ID                = PLA.PO_HEADER_ID
        AND          PLA.PO_LINE_ID                        = PLL.PO_LINE_ID
        AND          FFV.flex_value_set_id                 = FVS.flex_value_set_id(+)
        AND          GLCC.segment2                          = FFV.flex_value(+)
        AND   TRUNC (gjh.posted_date) BETWEEN NVL (:lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (:lc_gl_posted_to,TRUNC (GJH.posted_date))
        AND          GJH.je_source                   = NVL (:p_gl_source, GJH.je_source)
        AND          GJH.je_category                 = NVL (:p_gl_category, GJH.je_category)
        and glcc.code_combination_id IN (43256,1935900, 1607449)
        AND GJL.period_name = 'SEP-19'
        and GJH.ledger_id = 2177)
        UNION       
(SELECT        /*+
           OPT_PARAM('_optimizer_mjc_enabled','false')
           OPT_PARAM('hash_join_enabled','false')
           OPT_PARAM('optimizer_index_cost_adj',20)
           OPT_PARAM('optimizer_index_caching',70)
       */
      ir.je_source      journal_source,
      ir.je_batch_name batch_name,
      ir.je_header_name journal_name,
       cc.concatenated_segments,--gll.ledger_id,
       cc.code_combination_id, 
       ir.period_name,
              ai.invoice_num invoice_number,
       ai.invoice_date invoice_date,
           (select segment1 from po_headers_all where po_header_id IN 
       (select po_header_id from ap_invoice_lines_all where invoice_id = ai.invoice_id and org_id = ai.org_id)) PO_NUMBER,     
      NULL LINE_NUMBER,
      NULL SHIPMENT_NUMBER,
      NULL RECEIPT_NUMBER, 
       xal.accounted_dr xla_accounted_dr, xal.accounted_cr xla_accounted_cr
              , ir.accounted_dr ledger_dr
                    , ir.accounted_cr ledger_cr                               
                    ,ir.je_category journal_category
                    ,NULL rcv_transaction_type
                     ,TO_CHAR (ir.default_effective_date, 'DD-MON-YYYY') gl_date
                    ,TO_CHAR (ir.posted_date, 'DD-MON-YYYY') posted_date
                      , aps.segment1 party_code
                          , aps.vendor_name party_name
                          , apss.vendor_site_code  party_site                       
                    ,NULL item_code         
  FROM xla.xla_transaction_entities PARTITION (AP) xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       ap.ap_invoices_all ai,
       ap.ap_suppliers aps,
       ap.ap_supplier_sites_all apss,
       apps.fnd_application_vl fat,
       gl.gl_ledgers gll,
       hr.hr_all_organization_units hou,
       gl_code_combinations_kfv cc,
       apps.xla_lookups l1,
      (SELECT ir.gl_sl_link_id, ir.gl_sl_link_table, jb.je_batch_id,gll.ACCOUNTED_DR, gll.ACCOUNTED_CR,
              jb.name je_batch_name, jh.je_header_id, jh.name je_header_name, ir.je_line_num,jh.default_effective_date,JH.POSTED_DATE,
              jh.je_source, jh.je_category, jh.ledger_id,jh.name,jh.description,jh.period_name
         FROM gl.gl_import_references ir, gl.gl_je_batches jb, gl.gl_je_headers jh, gl_je_lines gll
        WHERE ir.je_batch_id = jb.je_batch_id
          AND jh.je_header_id = gll.je_header_id
          AND gll.JE_LINE_NUM = ir.JE_LINE_NUM
          AND ir.je_header_id = jh.je_header_id
          and jh.period_name = 'SEP-19'
          ) ir
WHERE xte.entity_id = xah.entity_id
   AND xah.ae_header_id = xal.ae_header_id
   AND xal.application_id = 200
   AND xah.application_id = 200
   AND xte.application_id = 200
   AND xte.application_id = xah.application_id
   AND xah.application_id = xal.application_id
   AND xte.application_id = fat.application_id
--   AND ir.name = '19-JUL-2019 Purchase Invoices INR'
--   --AND fat.language = USERENV('LANG')
-- Modified just to provide alias by Ashish M. on 18-Apr-2014,Ref:CREE_ITALY_LANG_PACK
   AND ai.org_id = hou.organization_id
   AND xte.entity_code IN ('AP_INVOICES')
   AND xah.balance_type_code = 'A'
   AND xah.gl_transfer_status_code = 'Y'
   AND xal.accounting_class_code = l1.lookup_code
   AND l1.lookup_type = 'XLA_ACCOUNTING_CLASS'
   AND NVL(xte.source_id_int_1, -99) = ai.invoice_id(+)
   AND ai.vendor_id = aps.vendor_id(+)
   AND ai.vendor_site_id = apss.vendor_site_id(+)
--   AND NVL (xte.source_id_int_1, -99) = &invoice_id
--   AND xah.accounting_date BETWEEN :gl_date_from AND :gl_date_to
--   AND xah.period_name = :period_name
--   AND ir.ledger_id = :reporting_entity_id
   AND (NVL (xal.entered_dr, 0) <> 0
     OR  NVL (xal.entered_cr, 0) <> 0
     OR  NVL (xal.accounted_dr, 0) <> 0
     OR  NVL (xal.accounted_cr, 0) <> 0)
   AND EXISTS
          (SELECT 1
             FROM gl.gl_import_references ir
            WHERE ir.gl_sl_link_id = xal.gl_sl_link_id
              AND ir.gl_sl_link_table = xal.gl_sl_link_table)
   AND xal.gl_sl_link_id = ir.gl_sl_link_id(+)
   AND xal.gl_sl_link_table = ir.gl_sl_link_table(+)
--   AND xal.ledger_id = gll.ledger_id(+)
   AND ir.ledger_id = gll.ledger_id(+)
   AND xal.code_combination_id = cc.code_combination_id
   AND gll.name = 'India PL'
   --and ir.je_batch_name = 'Payables A 2830894 76784080'
   --and ir.name = '06-SEP-2019 Purchase Invoices INR'
   and xal.code_combination_id IN(43256,1935900, 1607449));