Thursday, September 19, 2019
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));
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment