[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT chr.currency_code currency_code
,ou.name organization_name
,chr.contract_number contract_number
,a.contract_id contract_id
,ou.organization_id org_id
,a.bkg_transaction_date bkg_transaction_date
,KHR.PDT_ID PDT_ID
FROM
(
SELECT bkg_trx.khr_id contract_id
,min(bkg_trx.transaction_date) bkg_transaction_date
FROM okl_trx_contracts_all bkg_trx
,okl_k_headers khr
,okl_trx_types_b try
,okl_rep_products_gt pdt_gt
WHERE bkg_trx.tcn_type = 'BKG'
AND bkg_trx.try_id = try.id
AND try.trx_type_class = 'BOOKING'
-- Booking Transaction should be related to the Input Ledger
AND bkg_trx.set_of_books_id = p_ledger_id
-- Booking Transaction should belong the Operating Unit inputted
AND bkg_trx.org_id = nvl( p_org_id, bkg_trx.org_id )
-- Booking Transaction should belong the Legal Entity inputted
AND bkg_trx.legal_entity_id = nvl( p_legal_entity_id, bkg_trx.legal_entity_id )
AND bkg_trx.khr_id = khr.id
-- Contracts product in context should be one of the Report Products
AND khr.pdt_id = pdt_gt.product_id
GROUP BY bkg_trx.khr_id
-- Booking Transaction Date should be less than the Report To Date
HAVING MIN(bkg_trx.transaction_date ) < p_end_date
) a
,okc_k_headers_all_b CHR
,okl_k_headers KHR
,hr_operating_units ou
WHERE
a.contract_id = CHR.id
AND KHR.ID = CHR.ID
AND ou.organization_id = chr.authoring_org_id
-- Pick only those Contracts [iff in Expired/Terminated Status]
-- which got expired by a Termination Transaction
-- after the Report Start Date
-- Contract should not have occurbe before the Report Start Date
AND p_start_date <=
nvl( ( SELECT max(transaction_date) last_trm_trx_date
FROM okl_trx_contracts_all term_trx
WHERE term_trx.khr_id = a.contract_id
AND term_trx.tcn_type ='TMT'
AND trn_code = 'EXP'
),
p_start_date + 1
)
; -- End of Cursor: get_contracts_csr
SELECT s.application_column_name segment_col_name
FROM fnd_id_flex_segments s,
fnd_segment_attribute_values sav,
gl_ledgers_public_v glp --,
WHERE s.application_id = 101 -- GL Application ID
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = glp.chart_of_accounts_id --COA ID
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = glp.chart_of_accounts_id
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = 'GL_ACCOUNT'
AND glp.ledger_id = p_ledger_id;
'INSERT INTO okl_code_cmbns_gt ('
||' ccid'
||' ,account_number'
||')'
||' SELECT cc.code_combination_id'
||' ,' || l_segment || ' ' -- segment3
||' FROM gl_code_combinations cc'
||' ,gl_ledgers_public_v gl'
||' WHERE cc.chart_of_accounts_id = gl.chart_of_accounts_id'
||' AND cc.enabled_flag = ' || '''' || 'Y' || ''''
||' AND gl.ledger_id = ' || p_ledger_id
||' AND EXISTS'
||' ('
||' SELECT sg_frm_fvl.flex_value segment_range_from'
||' ,sg_to_fvl.flex_value segment_range_to'
||' FROM fnd_flex_values_vl sg_frm_fvl'
||' ,fnd_flex_values_vl sg_to_fvl'
||' ,okl_report_acc_params acc_params'
||' WHERE sg_frm_fvl.flex_value_id = acc_params.segment_range_from'
||' AND sg_to_fvl.flex_value_id = acc_params.segment_range_to'
||' AND acc_params.report_id = ' || p_report_id || ' '
||' AND cc.' || l_segment || ' >= sg_frm_fvl.flex_value'
||' AND cc.' || l_segment || ' <= sg_to_fvl.flex_value'
||')';
SELECT DISTINCT 'Y' flag
FROM okl_report_parameters params
WHERE params.report_id = p_report_id
AND params.parameter_type_code IN
( 'BOOK_CLASSIFICATION', 'PRODUCT' );
SELECT pdt.NAME product_name
,pdt.id product_id
FROM okl_products pdt;
SELECT pdt.NAME product_name
,pdt.id product_id
FROM okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
WHERE pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND gts.deal_type
IN
(
SELECT params.param_char_value1
FROM okl_report_parameters params
WHERE params.report_id = p_report_id
AND params.parameter_type_code = 'BOOK_CLASSIFICATION'
)
UNION
-- Append list of products from the report too
SELECT pdt.NAME product_name
,pdt.id product_id
FROM okl_report_parameters params
,okl_products pdt
WHERE params.report_id = p_report_id
AND params.parameter_type_code = 'PRODUCT'
AND params.param_num_value1 = pdt.id;
INSERT INTO okl_rep_products_gt VALUES l_report_pdts_tbl(i);
'Before Inserting the Lease Accounting Transactions GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
-- Date Formatted Columns
,value1_date
,value2_date
,value4_date
)
SELECT -- String Formatted Columns
trx_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,trx_legal_entity_name --value15_text
,trx_ledger_name --value16_text
,trx_activity_code --value17_text
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,trx_acc_dr_ccid --value4_num
,trx_acc_cr_ccid --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_legal_entity_id --value8_num
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
,accrual_date --value4_date
FROM
(
-- Query Segment to fetch the UnAccounted Transactions with No Accounting Events
-- Bug 6835659: Modified the Report Extraction to fetch the Transactions
-- of Non-Accounting Transaction Types
SELECT 'TRX_UNACCOUNTED_NO_EVENT' trx_detail_type_code
,trx.trx_number trx_number
,try.name trx_type_name
,NULL trx_event_name
,'Leasing and Finance Management' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,trx.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'D', dist.amount, NULL) trx_dr_amount
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'C', dist.amount, NULL) trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txl.amount
,'SUBTRACT', txl.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'D', dist.code_combination_id
, NULL) trx_acc_dr_ccid
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'C', dist.code_combination_id
, NULL) trx_acc_cr_ccid
,trx.set_of_books_id trx_ledger_id
,trx.org_id trx_operating_unit_id
,trx.legal_entity_id trx_legal_entity_id
,trx.khr_id trx_khr_id
,txl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txl.sty_id trx_sty_id
,540 trx_application_id
-- DATE Format Columns
,trx.transaction_date trx_date
,dist.gl_date gl_date
-- Additional Columns
,trx.id trx_id
,txl.id trx_txl_id
,trx.try_id trx_try_id
,rep.report_id trx_report_id
,dist.id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,trx.transaction_date trx_occ_date
,trx.date_accrual accrual_date
,'Lease' source
FROM okl_trx_contracts_all trx
,okl_txl_cntrct_lns_all txl
,okl_trns_acc_dstrs_all dist
,okl_trx_types_v try
,okl_strm_type_v sty
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE trx.id = txl.tcn_id
AND trx.try_id = try.id
AND try.accounting_event_class_code IS NOT NULL
AND txl.sty_id = sty.id -- May be we dont need outer join here
AND dist.source_id = txl.id
AND dist.source_table = 'OKL_TXL_CNTRCT_LNS'
-- Restrict to only one Distribution Line
-- In ATS Mode restrict the distribution to Debit Only
-- In AMB Mode only one dist. will be created in OKL, hence consider that as Debit
AND nvl(dist.cr_dr_flag, 'D') = 'D'
AND trx.khr_id = chr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- Pick the Distribution which doesnot have the Accounting Event Stamped on It
AND dist.accounting_event_id IS NULL
-- Transaction should have occured in the Start and End date of the Context
AND trx.transaction_date >= p_start_date
AND trx.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txl.sty_id), - 1 ) = nvl(txl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND trx.org_id = NVL( p_org_id, trx.org_id )
AND trx.legal_entity_id = NVL(p_le_id, trx.legal_entity_id )
AND trx.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = trx.org_id
AND le.legal_entity_id = trx.legal_entity_id
AND ledger.ledger_id = trx.set_of_books_id
--End of Query Segment to fetch the UnAccounted Transactions
UNION ALL
-- Query Segment to fetch the UnAccounted Transactions
-- from OLM only. Imp Predicates: XLA_EVENTS.event_status_code in ( 'I', 'U' )
SELECT 'TRX_UNACCOUNTED' trx_detail_type_code
,trx.trx_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Leasing and Finance Management' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,trx.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'D', dist.amount, NULL) trx_dr_amount
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'C', dist.amount, NULL) trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txl.amount
,'SUBTRACT', txl.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'D', dist.code_combination_id
, NULL) trx_acc_dr_ccid
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'C', dist.code_combination_id
, NULL) trx_acc_cr_ccid
,trx.set_of_books_id trx_ledger_id
,trx.org_id trx_operating_unit_id
,trx.legal_entity_id trx_legal_entity_id
,trx.khr_id trx_khr_id
,txl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txl.sty_id trx_sty_id
,540 trx_application_id
-- DATE Format Columns
,trx.transaction_date trx_date
,dist.gl_date gl_date
-- Additional Columns
,trx.id trx_id
,txl.id trx_txl_id
,trx.try_id trx_try_id
,rep.report_id trx_report_id
,dist.id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,trx.transaction_date trx_occ_date
,trx.date_accrual accrual_date
,'Lease' source
FROM okl_trx_contracts_all trx
,okl_txl_cntrct_lns_all txl
,okl_trns_acc_dstrs_all dist
,okl_trx_types_v try
,okl_strm_type_v sty
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
-- XLA Entities
,xla_events xe
,xla_event_types_vl xvl
WHERE trx.id = txl.tcn_id
AND trx.try_id = try.id
AND try.accounting_event_class_code IS NOT NULL
AND txl.sty_id = sty.id -- May be we dont need outer join here
AND dist.source_id = txl.id
AND dist.source_table = 'OKL_TXL_CNTRCT_LNS'
-- Restrict to only one Distribution Line
-- In ATS Mode restrict the distribution to Debit Only
-- In AMB Mode only one dist. will be created in OKL, hence consider that as Debit
AND nvl(dist.cr_dr_flag, 'D') = 'D'
AND trx.khr_id = chr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND xe.event_id = dist.accounting_event_id -- Distribution have the Acc. Event Stamp on it
AND xe.application_id = 540 -- Lease
AND xe.event_status_code IN ( 'U', 'I' ) -- Un Accounted Or Errored out
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
-- Transaction should have occured in the Start and End date of the Context
AND trx.transaction_date >= p_start_date
AND trx.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txl.sty_id), - 1 ) = nvl(txl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND trx.org_id = NVL( p_org_id, trx.org_id )
AND trx.legal_entity_id = NVL(p_le_id, trx.legal_entity_id )
AND trx.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = trx.org_id
AND le.legal_entity_id = trx.legal_entity_id
AND ledger.ledger_id = trx.set_of_books_id
--End of Query Segment to fetch the UnAccounted Transactions
UNION ALL
-- Query Segment to fetch the Unposted Accounting Transactions
-- from OLM only. Imp Predicates: Accounting Event status in P
-- xh.gl_transfer_status_code <> 'Y'
SELECT 'TRX_UNPOSTED' trx_detail_type_code
,trx.trx_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Leasing and Finance Management' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,trx.currency_code currency_code
,glcc.concatenated_segments trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'D', dist.amount, NULL) trx_dr_amount
,DECODE(nvl(dist.cr_dr_flag, 'D')
,'C', dist.amount, NULL) trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txl.amount
,'SUBTRACT', txl.amount * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
)
,trx.set_of_books_id trx_ledger_id
,trx.org_id trx_operating_unit_id
,trx.legal_entity_id trx_legal_entity_id
,trx.khr_id trx_khr_id
,txl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txl.sty_id trx_sty_id
,540 trx_application_id
-- DATE Format Columns
,trx.transaction_date trx_date
,dist.gl_date gl_date
-- Additional Columns
,trx.id trx_id
,txl.id trx_txl_id
,trx.try_id trx_try_id
,rep.report_id trx_report_id
,dist.id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,trx.transaction_date trx_occ_date
,trx.date_accrual accrual_date
,'Lease' source
FROM okl_trx_contracts_all trx
,okl_txl_cntrct_lns_all txl
,okl_trns_acc_dstrs_all dist
,okl_trx_types_v try
,okl_strm_type_v sty
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
-- SLA Entities
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
,xla_events xe
,xla_event_types_vl xvl
-- OLM Reconciliation Report Definitions Table
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,gl_code_combinations_kfv glcc
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE trx.id = txl.tcn_id
AND trx.try_id = try.id
AND try.accounting_event_class_code IS NOT NULL
AND txl.sty_id = sty.id -- May be we dont need outer join here
AND dist.source_id = txl.id
AND dist.source_table = 'OKL_TXL_CNTRCT_LNS'
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
AND dist.posted_yn = 'Y'
AND trx.khr_id = chr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- OKL to SLA Link predicates
AND dist.accounting_event_id = xe.event_id
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = dist.accounting_event_id
AND dist.id = xd.source_distribution_id_num_1
AND xd.application_id = 540
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Important Predicate: XLA Distribution Links should not have the GL Import Link ID
AND xh.gl_transfer_status_code <> 'Y' -- Not Imported to GL Yet
-- Transaction should have occured in the Start and End date of the Context
AND trx.transaction_date >= p_start_date
AND trx.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txl.sty_id), - 1 ) = nvl(txl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND trx.org_id = NVL( p_org_id, trx.org_id )
AND trx.legal_entity_id = NVL(p_le_id, trx.legal_entity_id )
AND trx.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = trx.org_id
AND le.legal_entity_id = trx.legal_entity_id
AND ledger.ledger_id = trx.set_of_books_id
AND glcc.code_combination_id = xl.code_combination_id
-- End of Query Segment to fetch the Unposted Acc. Entries from OLM
UNION ALL
-- Query Segment to find the Journal Entries from GL, either Posted or Unposted
-- If Unposted Detail Type will be TRX_UNPOSTED else if posted it will be TRX_POSTED
SELECT DECODE( gh.status
,'U', 'TRX_UNPOSTED' -- Unposted Transactions
,'P', 'TRX_POSTED' -- Posted Transactions
) trx_detail_type_code
,trx.trx_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Leasing and Finance Management' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,trx.currency_code currency_code
,glcc.concatenated_segments trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,gh.period_name trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txl.amount
,'SUBTRACT', txl.amount * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
) trx_acc_cr_ccid
,trx.set_of_books_id trx_ledger_id
,trx.org_id trx_operating_unit_id
,trx.legal_entity_id trx_legal_entity_id
,trx.khr_id trx_khr_id
,txl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txl.sty_id trx_sty_id
,540 trx_application_id
-- DATE Format Columns
,trx.transaction_date trx_date
,dist.gl_date gl_date
-- Additional Columns
,trx.id trx_id
,txl.id trx_txl_id
,trx.try_id trx_try_id
,rep.report_id trx_report_id
,dist.id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,trx.transaction_date trx_occ_date
,trx.date_accrual accrual_date
,'Lease' source
FROM okl_trx_contracts_all trx
,okl_txl_cntrct_lns_all txl
,okl_trns_acc_dstrs_all dist
,okl_trx_types_v try
,okl_strm_type_v sty
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
-- SLA Entities
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
,xla_events xe
,xla_event_types_vl xvl
-- GL Tables: Import Reference, GL Header and Lines
,gl_import_references gi
,gl_je_headers gh
,gl_je_lines gl
-- OKL Report Definition Tables
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,gl_code_combinations_kfv glcc
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE trx.id = txl.tcn_id
AND trx.try_id = try.id
AND try.accounting_event_class_code IS NOT NULL
AND txl.sty_id = sty.id -- May be we dont need outer join here
AND dist.source_id = txl.id
AND dist.source_table = 'OKL_TXL_CNTRCT_LNS'
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
AND dist.posted_yn = 'Y'
AND trx.khr_id = chr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- OKL to SLA Link predicates
AND dist.posted_yn = 'Y'
AND dist.accounting_event_id = xe.event_id
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = dist.accounting_event_id --XE,XD,XH,XL BEGIN
AND dist.id = xd.source_distribution_id_num_1
AND xd.application_id = 540
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- From SLA to GL Tables Link
AND gi.gl_sl_link_id = xl.gl_sl_link_id --GL TABLES JOIN START
AND xl.ledger_id = gl.ledger_id
AND xl.ledger_id = p_ledger_id
AND gi.gl_sl_link_table = xl.gl_sl_link_table
AND gi.je_header_id = gh.je_header_id
AND gh.je_header_id = gl.je_header_id
AND gi.je_line_num = gl.je_line_num
AND gh.je_source = 'Lease'
-- Important Predicate: gl_je_headers.status can be either Posted
-- There can be even Unposted Entries
AND gh.status IN ( 'U', 'P' ) -- Unposted or Posted Entries
-- Transaction should have occured in the Start and End date of the Context
AND trx.transaction_date >= p_start_date
AND trx.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txl.sty_id), - 1 ) = nvl(txl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND trx.org_id = NVL( p_org_id, trx.org_id )
AND trx.legal_entity_id = NVL(p_le_id, trx.legal_entity_id )
AND trx.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = trx.org_id
AND le.legal_entity_id = trx.legal_entity_id
AND ledger.ledger_id = trx.set_of_books_id
AND glcc.code_combination_id = xl.code_combination_id
-- End of Query Segment to fetch Unposted or Posted Acc. Entries from GL
);
'After Inserting the Lease Transactions Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Receivables Invoice Transactions Data in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
trx_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,trx_legal_entity_name --value15_text
,trx_ledger_name --value16_text
,trx_activity_code --value17_text
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,trx_acc_dr_ccid --value4_num
,trx_acc_cr_ccid --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_legal_entity_id --value8_num
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-------------------------------------------------------------------------------------
-- Query segments to fetch OKL-AR Invoice Transactions from OKL, Not moved to even AR
-------------------------------------------------------------------------------------
-- Query Segment to find the UnAccounted Receivables Invoice Transactions from OKL
SELECT 'TRX_UNACCOUNTED_NO_EVENT' trx_detail_type_code
,tai.trx_number trx_number
,try.name trx_type_name
,NULL trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,NULL trx_dr_amount
,NULL trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txd.amount
,'SUBTRACT', txd.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,NULL trx_acc_dr_ccid
,NULL trx_acc_cr_ccid
,tai.set_of_books_id trx_ledger_id
,tai.org_id trx_operating_unit_id
,tai.legal_entity_id trx_legal_entity_id
,txd.khr_id trx_khr_id
,txd.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txd.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,tai.transaction_date trx_date
,NULL gl_date -- Invoice Date
-- Additional Columns
,tai.id trx_id
,til.id trx_txl_id
,tai.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tai.transaction_date trx_occ_date
,'Receivables' source
FROM okl_txd_ar_ln_dtls_b txd
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE -- OKL to AR Application Predicates
txd.til_id_details = til.id -- Trx. Detail to Trx. Line
AND til.tai_id = tai.id -- Trx. Line to Trx. Header
AND txd.khr_id = chr.id -- Trx. Header to Contract
-- OKL_ARINTF_PVT picks
-- Possible values found: CANCELED, ERROR, SUBMITTED, PROCESSED, WORKING
-- When the Trx. Status is SUBMITTED, then it means that the Trx. is just in OKL
-- When the Trx. Status is PROCESSED, then it means that the Trx. may
-- a. have moved to AR Import Tables but not yet imported successfully.
-- b. Have been moved to AR Import Tables successfully..
AND (
tai.trx_status_code = 'SUBMITTED'
OR (
tai.trx_status_code = 'PROCESSED'
AND NOT EXISTS
(
SELECT 1
FROM ra_customer_trx_lines_all rcl
WHERE txd.id = rcl.interface_line_attribute14
AND chr.contract_number = rcl.interface_line_attribute6
) -- Close: Not Exists
) -- Close: Only for Processed
) -- Close: For both Submitted/Processed
-- Notes: In R12 Found that the OKL_ARINTF_PVT picks only submitted trx.s and moves it to AR
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tai.try_id = try.id
AND txd.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tai.transaction_date >= p_start_date
AND tai.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txd.sty_id), - 1 ) = nvl(txd.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tai.org_id = NVL( p_org_id, tai.org_id )
AND tai.legal_entity_id = NVL(p_le_id, tai.legal_entity_id )
AND tai.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tai.org_id
AND le.legal_entity_id = tai.legal_entity_id
AND ledger.ledger_id = tai.set_of_books_id
UNION ALL
-------------------------------------------------------------------------
-- Query segments to fetch Invoice Transactions from AR related to OLM Contracts
-------------------------------------------------------------------------
-- Query Segment to find the UnAccounted Receivables Invoice Transactions from Receivables
SELECT 'TRX_UNACCOUNTED' trx_detail_type_code
,rct.trx_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,NULL trx_dr_amount
,NULL trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txd.amount
,'SUBTRACT', txd.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,NULL trx_acc_dr_ccid
,NULL trx_acc_cr_ccid
,tai.set_of_books_id trx_ledger_id
,tai.org_id trx_operating_unit_id
,tai.legal_entity_id trx_legal_entity_id
,txd.khr_id trx_khr_id
,txd.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txd.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,tai.transaction_date trx_date
,rct.trx_date gl_date -- Invoice Date
-- Additional Columns
,tai.id trx_id
,til.id trx_txl_id
,tai.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tai.transaction_date trx_occ_date
,'Receivables' source
FROM okl_txd_ar_ln_dtls_b txd
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AR Tables
,ra_customer_trx_lines_all rcl
,ra_customer_trx_all rct
,ra_cust_trx_line_gl_dist_all rad
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE -- OKL to AR Application Predicates
txd.til_id_details = til.id -- Trx. Detail to Trx. Line
AND til.tai_id = tai.id -- Trx. Line to Trx. Header
AND txd.khr_id = chr.id -- Trx. Header to Contract
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tai.try_id = try.id
AND txd.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tai.transaction_date >= p_start_date
AND tai.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txd.sty_id), - 1 ) = nvl(txd.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tai.org_id = NVL( p_org_id, tai.org_id )
AND tai.legal_entity_id = NVL(p_le_id, tai.legal_entity_id )
AND tai.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tai.org_id
AND le.legal_entity_id = tai.legal_entity_id
AND ledger.ledger_id = tai.set_of_books_id
-- OKL to AR Predicates
AND txd.id = rcl.interface_line_attribute14
AND chr.contract_number = rcl.interface_line_attribute6
AND rct.customer_trx_id = rcl.customer_trx_id
AND rct.org_id = nvl(p_org_id, rct.org_id)
AND rcl.customer_trx_line_id = rad.customer_trx_line_id
AND rct.customer_trx_id = rad.customer_trx_id
-- AR to XLA Relations
AND rad.event_id = xe.event_id
AND xe.application_id = 222
AND xe.event_status_code IN ( 'U', 'I' )
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
UNION ALL
-- Query Segment to find the UnPosted Receivables Invoice Transactions from Receivables
-- And hence the Dr/Cr CCId are from XLA only
-- Important Predicate again is xh.gl_transfer_status_code <> 'Y'
SELECT 'TRX_UNPOSTED' trx_detail_type_code
,rct.trx_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txd.amount
,'SUBTRACT', txd.amount * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
) trx_acc_cr_ccid
,tai.set_of_books_id trx_ledger_id
,tai.org_id trx_operating_unit_id
,tai.legal_entity_id trx_legal_entity_id
,txd.khr_id trx_khr_id
,txd.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txd.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,tai.transaction_date trx_date
,rct.trx_date gl_date -- Invoice Date
-- Additional Columns
,tai.id trx_id
,til.id trx_txl_id
,tai.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tai.transaction_date trx_occ_date
,'Receivables' source
FROM okl_txd_ar_ln_dtls_b txd
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AR Tables
,ra_customer_trx_lines_all rcl
,ra_customer_trx_all rct
,ra_cust_trx_line_gl_dist_all rad
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE -- OKL to AR Application Predicates
txd.til_id_details = til.id -- Trx. Detail to Trx. Line
AND til.tai_id = tai.id -- Trx. Line to Trx. Header
AND txd.khr_id = chr.id -- Trx. Header to Contract
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tai.try_id = try.id
AND txd.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tai.transaction_date >= p_start_date
AND tai.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txd.sty_id), - 1 ) = nvl(txd.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tai.org_id = NVL( p_org_id, tai.org_id )
AND tai.legal_entity_id = NVL(p_le_id, tai.legal_entity_id )
AND tai.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tai.org_id
AND le.legal_entity_id = tai.legal_entity_id
AND ledger.ledger_id = tai.set_of_books_id
-- OKL to AR Predicates
AND txd.id = rcl.interface_line_attribute14
AND chr.contract_number = rcl.interface_line_attribute6
AND rct.customer_trx_id = rcl.customer_trx_id
AND rct.org_id = nvl(p_org_id, rct.org_id )
AND rcl.customer_trx_line_id = rad.customer_trx_line_id
AND rct.customer_trx_id = rad.customer_trx_id
-- AR to XLA Relations
AND rad.event_id = xe.event_id
AND xe.application_id = 222
AND xe.event_status_code = 'P'
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = rad.event_id
AND xd.source_distribution_id_num_1 = rad.cust_trx_line_gl_dist_id
AND xd.application_id = 222
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Important Predicate: XLA Distribution Links should not have the GL Import Link ID
AND xh.gl_transfer_status_code <> 'Y' -- Not Imported to GL Yet
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
UNION ALL
-- Query Segment to find the Imported Receivables Invoice Transactions from GL
-- Note that that the Acc. Entry may or may not be posted into GL.
-- Important Predicate again is xla_ae_lines.gl_sl_link_id IS NOT NULL
SELECT DECODE( gh.status
,'U', 'TRX_UNPOSTED' -- Unposted Transactions
,'P', 'TRX_POSTED' -- Posted Transactions
) trx_detail_type_code
,rct.trx_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,glcc.concatenated_segments trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,gh.period_name trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', txd.amount
,'SUBTRACT', txd.amount * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
)
,tai.set_of_books_id trx_ledger_id
,tai.org_id trx_operating_unit_id
,tai.legal_entity_id trx_legal_entity_id
,txd.khr_id trx_khr_id
,txd.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txd.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,tai.transaction_date trx_date
,rct.trx_date gl_date -- Invoice Date
-- Additional Columns
,tai.id trx_id
,til.id trx_txl_id
,tai.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tai.transaction_date trx_occ_date
,'Receivables' source
FROM okl_txd_ar_ln_dtls_b txd
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AR Tables
,ra_customer_trx_lines_all rcl
,ra_customer_trx_all rct
,ra_cust_trx_line_gl_dist_all rad
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
-- GL Tables: Import Reference, GL Header and Lines
,gl_import_references gi
,gl_je_headers gh
,gl_je_lines gl
,gl_code_combinations_kfv glcc
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE -- OKL to AR Application Predicates
txd.til_id_details = til.id -- Trx. Detail to Trx. Line
AND til.tai_id = tai.id -- Trx. Line to Trx. Header
AND txd.khr_id = chr.id -- Trx. Header to Contract
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tai.try_id = try.id
AND txd.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tai.transaction_date >= p_start_date
AND tai.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,txd.sty_id), - 1 ) = nvl(txd.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tai.org_id = NVL( p_org_id, tai.org_id )
AND tai.legal_entity_id = NVL(p_le_id, tai.legal_entity_id )
AND tai.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tai.org_id
AND le.legal_entity_id = tai.legal_entity_id
AND ledger.ledger_id = tai.set_of_books_id
-- OKL to AR Predicates
AND txd.id = rcl.interface_line_attribute14
AND chr.contract_number = rcl.interface_line_attribute6
AND rct.customer_trx_id = rcl.customer_trx_id
AND rct.org_id = nvl(p_org_id, rct.org_id )
AND rcl.customer_trx_line_id = rad.customer_trx_line_id
AND rct.customer_trx_id = rad.customer_trx_id
-- AR to XLA Relations
AND rad.event_id = xe.event_id
AND xe.application_id = 222
AND xe.event_status_code = 'P'
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = rad.event_id
AND xd.source_distribution_id_num_1 = rad.cust_trx_line_gl_dist_id
AND xd.application_id = 222
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND glcc.code_combination_id = xl.code_combination_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
-- From SLA to GL Tables Link
-- Important Predicate: XLA Distribution Links should have the GL Import Link ID
AND gi.gl_sl_link_id = xl.gl_sl_link_id --GL TABLES JOIN START
AND gl.ledger_id = xl.ledger_id
AND gi.gl_sl_link_table = xl.gl_sl_link_table
AND gi.je_header_id = gh.je_header_id
AND gh.je_header_id = gl.je_header_id
AND gi.je_line_num = gl.je_line_num
-- AND gl.code_combination_id = cc.ccid
AND gh.je_source = 'Receivables'
-- Important Predicate: gl_je_headers.status can be either Posted
-- There can be even Unposted Entries
AND gh.status IN ( 'U', 'P' ) -- Unposted or Posted Entries
);
'After Inserting the Receivables Invoice Transactions Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Receivables Adjustments Transactions Data in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
trx_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,trx_legal_entity_name --value15_text
,trx_ledger_name --value16_text
,trx_activity_code --value17_text
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,trx_acc_dr_ccid --value4_num
,trx_acc_cr_ccid --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_legal_entity_id --value8_num
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the OLM-AR Adjustment Transactions
-- which are not yet moved to AR even.
-- Important predicate ajl.receivables_adjustment_id IS NULL
SELECT 'TRX_UNACCOUNTED_NO_EVENT' trx_detail_type_code
,chr.contract_number || adj.adjustment_reason_code
trx_number
,try.name trx_type_name
,NULL trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,NULL trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,NULL trx_dr_amount
,NULL trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', ajl.amount
,'SUBTRACT', ajl.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,NULL trx_acc_dr_ccid
,NULL trx_acc_cr_ccid
,tai.set_of_books_id trx_ledger_id
,adj.org_id trx_operating_unit_id
,NULL trx_legal_entity_id
,ajl.khr_id trx_khr_id
,ajl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,ajl.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,adj.transaction_date trx_date
,adj.gl_date gl_date -- Invoice Date
-- Additional Columns
,adj.id trx_id
,ajl.id trx_txl_id
,adj.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,adj.transaction_date trx_occ_date
,'Receivables - Adjustments' source
FROM -- OKL AR Adjustment Entities
okl_trx_ar_adjsts_all_b adj
,okl_txl_adjsts_lns_all_b ajl
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
-- OKL Entities
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,gl_ledgers_v ledger
WHERE -- OKL - AR Adjustment Application Predicates
adj.id = ajl.adj_id
AND ajl.til_id = til.id
-- Important Predicate
AND ajl.receivables_adjustment_id IS NULL
-- The above predicate ensures that the OLM-AR Adjustment Trx. is not yet moved to AR even.
AND til.tai_id = tai.id
AND ajl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND adj.try_id = try.id
AND ajl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND adj.transaction_date >= p_start_date
AND adj.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,ajl.sty_id), - 1 ) = nvl(ajl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND adj.org_id = NVL( p_org_id, adj.org_id )
AND tai.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = adj.org_id
AND ledger.ledger_id = tai.set_of_books_id
UNION ALL
-- Query Segment to find the UnAccounted Receivables Invoice Transactions from Receivables
SELECT 'TRX_UNACCOUNTED' trx_detail_type_code
,radj.adjustment_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,NULL trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,NULL trx_dr_amount
,NULL trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', ajl.amount
,'SUBTRACT', ajl.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,NULL trx_acc_dr_ccid
,NULL trx_acc_cr_ccid
,radj.set_of_books_id trx_ledger_id
,adj.org_id trx_operating_unit_id
,NULL trx_legal_entity_id
,ajl.khr_id trx_khr_id
,ajl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,ajl.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,adj.transaction_date trx_date
,adj.gl_date gl_date -- Invoice Date
-- Additional Columns
,adj.id trx_id
,ajl.id trx_txl_id
,adj.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,adj.transaction_date trx_occ_date
,'Receivables - Adjustments' source
FROM -- OKL AR Adjustment Entities
okl_trx_ar_adjsts_all_b adj
,okl_txl_adjsts_lns_all_b ajl
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
-- OKL Entities
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AR Tables
,ar_adjustments_all radj
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,gl_ledgers_v ledger
WHERE -- OKL - AR Adjustment Application Predicates
adj.id = ajl.adj_id
AND ajl.til_id = til.id
AND til.tai_id = tai.id
AND ajl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND adj.try_id = try.id
AND ajl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND adj.transaction_date >= p_start_date
AND adj.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,ajl.sty_id), - 1 ) = nvl(ajl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND adj.org_id = NVL( p_org_id, adj.org_id )
AND radj.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = adj.org_id
AND ledger.ledger_id = radj.set_of_books_id
-- OKL to AR Predicates
AND ajl.receivables_adjustment_id = radj.adjustment_id
-- AR to XLA Relations
AND radj.event_id = xe.event_id
AND xe.application_id = 222
AND xe.event_status_code IN ( 'U', 'I' )
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
UNION ALL
-- Query Segment to find the Receivables Adjustment Transactions in SLA
-- Only Adjustment Transactions that are not transferred to GL will be queried up
-- by this segment.
-- And hence the Dr/Cr CCId are from XLA only
-- Important Predicate again is xh.gl_transfer_status_code <> 'Y'
SELECT 'TRX_UNPOSTED' trx_detail_type_code
,radj.adjustment_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,NULL trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', NVL(xl.entered_dr, xl.entered_cr)
,'SUBTRACT', NVL(xl.entered_dr, xl.entered_cr) * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
) trx_acc_cr_ccid
,radj.set_of_books_id trx_ledger_id
,adj.org_id trx_operating_unit_id
,NULL trx_legal_entity_id
,ajl.khr_id trx_khr_id
,ajl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,ajl.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,adj.transaction_date trx_date
,adj.gl_date gl_date -- Invoice Date
-- Additional Columns
,adj.id trx_id
,ajl.id trx_txl_id
,adj.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,adj.transaction_date trx_occ_date
,'Receivables - Adjustments' source
FROM -- OKL AR Adjustment Entities
okl_trx_ar_adjsts_all_b adj
,okl_txl_adjsts_lns_all_b ajl
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
-- OKL Entities
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AR Tables
,ar_adjustments_all radj
,ar_distributions_all rdist
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,gl_ledgers_v ledger
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
WHERE -- OKL - AR Adjustment Application Predicates
adj.id = ajl.adj_id
AND ajl.til_id = til.id
AND til.tai_id = tai.id
AND ajl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND adj.try_id = try.id
AND ajl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND adj.transaction_date >= p_start_date
AND adj.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,ajl.sty_id), - 1 ) = nvl(ajl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND adj.org_id = NVL( p_org_id, adj.org_id )
AND radj.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = adj.org_id
AND ledger.ledger_id = radj.set_of_books_id
-- OKL to AR Predicates
AND ajl.receivables_adjustment_id = radj.adjustment_id
AND radj.adjustment_id = rdist.source_id
AND rdist.source_type = 'ADJ' -- Assumption
AND rdist.source_table = 'ADJ' -- Assumption
-- AR to XLA Relations
AND radj.event_id = xe.event_id
AND xe.application_id = 222
AND xe.event_status_code IN ( 'U', 'I' )
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = radj.event_id
AND xd.application_id = 222
AND xd.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xd.source_distribution_id_num_1 = rdist.line_id
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Important Predicate: XLA Distribution Links should not have the GL Import Link ID
AND xh.gl_transfer_status_code <> 'Y' -- Not Imported to GL Yet
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
UNION ALL
-- Query Segment to find the Receivables Adjustment Transactions in GL#
-- Note that: These Adjustment Transactions may or may not be Posted in GL#,
-- but got imported in GL# though.
SELECT DECODE( gh.status
,'U', 'TRX_UNPOSTED' -- Unposted Transactions
,'P', 'TRX_POSTED' -- Posted Transactions
) trx_detail_type_code
,radj.adjustment_number trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Receivables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tai.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,NULL trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', NVL(xl.entered_dr, xl.entered_cr)
,'SUBTRACT', NVL(xl.entered_dr, xl.entered_cr) * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
) trx_acc_cr_ccid
,radj.set_of_books_id trx_ledger_id
,adj.org_id trx_operating_unit_id
,NULL trx_legal_entity_id
,ajl.khr_id trx_khr_id
,ajl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,ajl.sty_id trx_sty_id
,222 trx_application_id
-- DATE Format Columns
,adj.transaction_date trx_date
,adj.gl_date gl_date -- Invoice Date
-- Additional Columns
,adj.id trx_id
,ajl.id trx_txl_id
,adj.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,adj.transaction_date trx_occ_date
,'Receivables - Adjustments' source
FROM -- OKL AR Adjustment Entities
okl_trx_ar_adjsts_all_b adj
,okl_txl_adjsts_lns_all_b ajl
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
-- OKL Entities
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AR Tables
,ar_adjustments_all radj
,ar_distributions_all rdist
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,gl_ledgers_v ledger
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
-- GL Tables: Import Reference, GL Header and Lines
,gl_import_references gi
,gl_je_headers gh
,gl_je_lines gl
,gl_code_combinations_kfv glcc
WHERE -- OKL - AR Adjustment Application Predicates
adj.id = ajl.adj_id
AND ajl.til_id = til.id
AND til.tai_id = tai.id
AND ajl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND adj.try_id = try.id
AND ajl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND adj.transaction_date >= p_start_date
AND adj.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,ajl.sty_id), - 1 ) = nvl(ajl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND adj.org_id = NVL( p_org_id, adj.org_id )
AND radj.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = adj.org_id
AND ledger.ledger_id = radj.set_of_books_id
-- OKL to AR Predicates
AND ajl.receivables_adjustment_id = radj.adjustment_id
AND radj.adjustment_id = rdist.source_id
AND rdist.source_type = 'ADJ' -- Assumption
AND rdist.source_table = 'ADJ' -- Assumption
-- AR to XLA Relations
AND radj.event_id = xe.event_id
AND xe.application_id = 222
AND xe.event_status_code IN ( 'U', 'I' )
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = radj.event_id
AND xd.application_id = 222
AND xd.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xd.source_distribution_id_num_1 = rdist.line_id
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
-- From SLA to GL Tables Link
-- Important Predicate: XLA Distribution Links should have the GL Import Link ID
AND gi.gl_sl_link_id = xl.gl_sl_link_id --GL TABLES JOIN START
AND gl.ledger_id = xl.ledger_id
AND gi.gl_sl_link_table = xl.gl_sl_link_table
AND gi.je_header_id = gh.je_header_id
AND gh.je_header_id = gl.je_header_id
AND gi.je_line_num = gl.je_line_num
-- AND gl.code_combination_id = cc.ccid
AND gh.je_source = 'Receivables'
-- Important Predicate: gl_je_headers.status can be either Posted
-- There can be even Unposted Entries
AND gh.status IN ( 'U', 'P' ) -- Unposted or Posted Entries
);
'After Inserting the Receivables Adjustment Transactions Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Payables Invoice Transactions Data in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
trx_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,trx_legal_entity_name --value15_text
,trx_ledger_name --value16_text
,trx_activity_code --value17_text
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,trx_acc_dr_ccid --value4_num
,trx_acc_cr_ccid --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_legal_entity_id --value8_num
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
---------------------------------------------------------------------------------------
-- Query Segment to find the OKL-AP Invoice Transactions which are not moved to AP Yet.
---------------------------------------------------------------------------------------
SELECT 'TRX_UNACCOUNTED_NO_EVENT' trx_detail_type_code
,tap.invoice_number trx_number
,try.name trx_type_name
,NULL trx_event_name
,'Payables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tap.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,NULL trx_dr_amount
,NULL trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', tpl.amount
,'SUBTRACT', tpl.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,NULL trx_acc_dr_ccid
,NULL trx_acc_cr_ccid
,tap.set_of_books_id trx_ledger_id
,tap.org_id trx_operating_unit_id
,tap.legal_entity_id trx_legal_entity_id
,tpl.khr_id trx_khr_id
,tpl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,tpl.sty_id trx_sty_id
,200 trx_application_id
-- DATE Format Columns
,tap.transaction_date trx_date
,NULL gl_date -- Invoice Date
-- Additional Columns
,tap.id trx_id
,tpl.id trx_txl_id
,tap.try_id trx_try_id
,rep.report_id trx_report_id
,NULL trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tap.transaction_date trx_occ_date
,'Payables' source
FROM
okl_txl_ap_inv_lns_all_b tpl
,okl_trx_ap_invs_all_b tap
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE
-- OKL to AP Application Predicates
tpl.tap_id = tap.id
-- Restrict to only transactions which are not yet moved to AP
-- Observation: Only Funding requests need to be in APPROVED status
-- For others Entered and Approved can be used for processing to move to AP
AND (
( tap.FUNDING_TYPE_CODE IS NULL AND
NVL(tap.trx_status_code, 'ENTERED') IN ( 'ENTERED', 'APPROVED' ) )
OR
( tap.FUNDING_TYPE_CODE IS NOT NULL AND
NVL(tap.trx_status_code, 'APPROVED') in ( 'APPROVED')
)
)
AND tpl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tap.try_id = try.id
AND tpl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tap.transaction_date >= p_start_date
AND tap.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,tpl.sty_id), - 1 ) = nvl(tpl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tap.org_id = NVL( p_org_id, tap.org_id )
AND tap.legal_entity_id = NVL(p_le_id, tap.legal_entity_id )
AND tap.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tap.org_id
AND le.legal_entity_id = tap.legal_entity_id
AND ledger.ledger_id = tap.set_of_books_id
UNION ALL
-- Query Segment to find the UnAccounted Payables Invoice Transactions from Payables
--
-- Logic: Starting from okl_txl_ap lines go to the Invoice Line
-- in AP and its Invoice Distributions. Invoice Distributions stamps the
-- Accounting Event Id.
-- Assumption: For a Given OKL AP Inv Lines, we assume that there is a one-one mapping
-- to Invoice Line and to its Distribution.
-- The Potential issue may be with the above assumption itself, as we found
-- multiple Distributions for a given Invoice Line Id. Hence, used the
-- LINE_TYPE_LOOKUP_CODE = 'ITEM' predicate. Not sure about this though !!
SELECT 'TRX_UNACCOUNTED' trx_detail_type_code
,inv.invoice_num trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Payables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tap.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,NULL trx_dr_amount
,NULL trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', tpl.amount
,'SUBTRACT', tpl.amount * -1
) trx_net_amount
-- Note that in ATS Mode only distributions can fetch you the CCID
-- In AMB mode, the Un-Accounted Distributions may not have the Code Combinations ID
,NULL trx_acc_dr_ccid
,NULL trx_acc_cr_ccid
,tap.set_of_books_id trx_ledger_id
,tap.org_id trx_operating_unit_id
,tap.legal_entity_id trx_legal_entity_id
,tpl.khr_id trx_khr_id
,tpl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,tpl.sty_id trx_sty_id
,200 trx_application_id
-- DATE Format Columns
,tap.transaction_date trx_date
,inv.invoice_date gl_date -- Invoice Date
-- Additional Columns
,tap.id trx_id
,tpl.id trx_txl_id
,tap.try_id trx_try_id
,rep.report_id trx_report_id
,invdist.invoice_distribution_id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tap.transaction_date trx_occ_date
,'Payables' source
FROM
okl_txl_ap_inv_lns_all_b tpl
,okl_trx_ap_invs_all_b tap
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AP Tables
,ap_invoices_all inv
,ap_invoice_lines_all lin
,ap_invoice_distributions_all invdist
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE
-- OKL to AP Application Predicates
tpl.tap_id = tap.id
AND tpl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tap.try_id = try.id
AND tpl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tap.transaction_date >= p_start_date
AND tap.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,tpl.sty_id), - 1 ) = nvl(tpl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tap.org_id = NVL( p_org_id, tap.org_id )
AND tap.legal_entity_id = NVL(p_le_id, tap.legal_entity_id )
AND tap.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tap.org_id
AND le.legal_entity_id = tap.legal_entity_id
AND ledger.ledger_id = tap.set_of_books_id
-- OKL to AP Invoice Lines Predicates
AND invdist.line_type_lookup_code = 'ITEM' -- Need to verify
AND invdist.invoice_id = lin.invoice_id
AND invdist.invoice_line_number = lin.line_number
AND lin.application_id = 540
AND lin.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND lin.reference_key1 = tpl.id
AND lin.invoice_id = inv.invoice_id
-- AP to XLA Relations
AND invdist.accounting_event_id = xe.event_id
AND xe.application_id = 200 -- Payables
AND xe.event_status_code IN ( 'U', 'I' ) -- Un Accounted Or Errored out
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
UNION ALL
-- Query Segment to find the Unposted Payables Invoice Transactions from Payables
--
-- Logic: Starting from okl_txl_ap lines go to the Invoice Line
-- in AP and its Invoice Distributions. Invoice Distributions stamps the
-- Accounting Event Id.
-- Assumption: For a Given OKL AP Inv Lines, we assume that there is a one-one mapping
-- to Invoice Line and to its Distribution.
-- The Potential issue may be with the above assumption itself, as we found
-- multiple Distributions for a given Invoice Line Id. Hence, used the
-- LINE_TYPE_LOOKUP_CODE = 'ITEM' predicate. Not sure about this though !!
-- Important Predicate: xh.gl_transfer_status_code <> 'Y'
SELECT 'TRX_UNPOSTED' trx_detail_type_code
,inv.invoice_num trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Payables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tap.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,NULL trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', tpl.amount
,'SUBTRACT', tpl.amount * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
) trx_acc_cr_ccid
,tap.set_of_books_id trx_ledger_id
,tap.org_id trx_operating_unit_id
,tap.legal_entity_id trx_legal_entity_id
,tpl.khr_id trx_khr_id
,tpl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,tpl.sty_id trx_sty_id
,200 trx_application_id
-- DATE Format Columns
,tap.transaction_date trx_date
,inv.invoice_date gl_date -- Invoice Date
-- Additional Columns
,tap.id trx_id
,tpl.id trx_txl_id
,tap.try_id trx_try_id
,rep.report_id trx_report_id
,invdist.invoice_distribution_id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tap.transaction_date trx_occ_date
,'Payables' source
FROM
okl_txl_ap_inv_lns_all_b tpl
,okl_trx_ap_invs_all_b tap
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AP Tables
,ap_invoices_all inv
,ap_invoice_lines_all lin
,ap_invoice_distributions_all invdist
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE
-- OKL to AP Application Predicates
tpl.tap_id = tap.id
AND tpl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tap.try_id = try.id
AND tpl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tap.transaction_date >= p_start_date
AND tap.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,tpl.sty_id), - 1 ) = nvl(tpl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tap.org_id = NVL( p_org_id, tap.org_id )
AND tap.legal_entity_id = NVL(p_le_id, tap.legal_entity_id )
AND tap.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tap.org_id
AND le.legal_entity_id = tap.legal_entity_id
AND ledger.ledger_id = tap.set_of_books_id
-- OKL to AP Invoice Lines Predicates
AND invdist.line_type_lookup_code = 'ITEM' -- Need to verify
AND invdist.invoice_id = lin.invoice_id
AND invdist.invoice_line_number = lin.line_number
AND lin.application_id = 540
AND lin.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND lin.reference_key1 = tpl.id
AND lin.invoice_id = inv.invoice_id
-- AP to XLA Relations
AND invdist.accounting_event_id = xe.event_id
AND xe.application_id = 200 -- Payables
AND xe.event_status_code = 'P' -- Pick Only processed XLA Events
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = xe.event_id
AND xd.application_id = 200 -- Payables Application
AND xd.source_distribution_id_num_1 = invdist.invoice_distribution_id
AND xd.source_distribution_type = 'AP_INV_DIST'
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Important Predicate: XLA Distribution Links should not have the GL Import Link ID
AND xh.gl_transfer_status_code <> 'Y' -- Not Imported to GL Yet
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
UNION ALL
-- Query Segment to find the Imported Payables Invoice Transactions from GL
-- Note that Imported means either Posted or not posted.
-- Logic: Starting from okl_txl_ap lines go to the Invoice Line
-- in AP and its Invoice Distributions. Invoice Distributions stamps the
-- Accounting Event Id.
-- Assumption: For a Given OKL AP Inv Lines, we assume that there is a one-one mapping
-- to Invoice Line and to its Distribution.
-- The Potential issue may be with the above assumption itself, as we found
-- multiple Distributions for a given Invoice Line Id. Hence, used the
-- LINE_TYPE_LOOKUP_CODE = 'ITEM' predicate. Not sure about this though !!
-- Important Predicate: xl.gl_sl_link_id = gl.gl_sl_link_id
SELECT DECODE( gh.status
,'U', 'TRX_UNPOSTED' -- Unposted Transactions
,'P', 'TRX_POSTED' -- Posted Transactions
) trx_detail_type_code
,inv.invoice_num trx_number
,try.name trx_type_name
,xvl.name trx_event_name
,'Payables' trx_application_name
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification -- Fetching Code need to change to fetch Meaning
,pdt.NAME product_name
,sty.NAME trx_sty_name
,sty.styb_purpose_meaning trx_sty_purpose
,tap.currency_code currency_code
,NULL trx_account_name
,ou.name trx_operating_unit_name
,le.name trx_legal_entity_name
,ledger.name trx_ledger_name
,rtryp.add_substract_code trx_activity_code
,gh.period_name trx_period_name
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,DECODE(rtryp.add_substract_code
,'ADD', tpl.amount
,'SUBTRACT', tpl.amount * -1
) trx_net_amount
-- Its safe to fetch the CCID from XLA itself instead of the OKL Dist. table
,DECODE(xl.entered_cr
,NULL, xl.code_combination_id -- When Credit Amount is Null, it means Debit Distribution
,NULL
) trx_acc_dr_ccid
,DECODE(xl.entered_dr
,NULL, xl.code_combination_id -- When Debit Amount is Null, it means Credit Distribution
,NULL
) trx_acc_cr_ccid
,tap.set_of_books_id trx_ledger_id
,tap.org_id trx_operating_unit_id
,tap.legal_entity_id trx_legal_entity_id
,tpl.khr_id trx_khr_id
,tpl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,tpl.sty_id trx_sty_id
,200 trx_application_id
-- DATE Format Columns
,tap.transaction_date trx_date
,inv.invoice_date gl_date -- Invoice Date
-- Additional Columns
,tap.id trx_id
,tpl.id trx_txl_id
,tap.try_id trx_try_id
,rep.report_id trx_report_id
,invdist.invoice_distribution_id trx_dist_id
-- Occurance date of the Transaction, not the Transaction Effective Date
,tap.transaction_date trx_occ_date
,'Payables' source
FROM
okl_txl_ap_inv_lns_all_b tpl
,okl_trx_ap_invs_all_b tap
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- AP Tables
,ap_invoices_all inv
,ap_invoice_lines_all lin
,ap_invoice_distributions_all invdist
-- XLA Tables
,xla_events xe
,xla_event_types_vl xvl
,xla_distribution_links xd
,xla_ae_headers xh
,xla_ae_lines xl
-- GL Tables: Import Reference, GL Header and Lines
,gl_import_references gi
,gl_je_headers gh
,gl_je_lines gl
-- OLM Reconciliation Report Entities
,okl_reports_b rep
,okl_report_trx_params rtryp
,okl_rep_products_gt pdt_gt
-- To fetch Names
,hr_operating_units ou
,xle_entity_profiles le
,gl_ledgers_v ledger
WHERE
-- OKL to AP Application Predicates
tpl.tap_id = tap.id
AND tpl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND tap.try_id = try.id
AND tpl.sty_id = sty.id
-- Transaction should have occured in the Start and End date of the Context
AND tap.transaction_date >= p_start_date
AND tap.transaction_date <= p_end_date
-- Add Predicates based on the report
AND rep.report_id = p_report_id
AND rtryp.report_id = rep.report_id
AND try.id = rtryp.try_id
AND nvl(nvl(rtryp.sty_id,tpl.sty_id), - 1 ) = nvl(tpl.sty_id, -1)
-- Products restriction
AND pdt_gt.product_id = pdt.id
-- Org., Ledger and Legal Entity Id restriction
AND tap.org_id = NVL( p_org_id, tap.org_id )
AND tap.legal_entity_id = NVL(p_le_id, tap.legal_entity_id )
AND tap.set_of_books_id = p_ledger_id -- Ledger is Mandatory Input Param
-- Predicates to fetch the Names
AND ou.organization_id = tap.org_id
AND le.legal_entity_id = tap.legal_entity_id
AND ledger.ledger_id = tap.set_of_books_id
-- OKL to AP Invoice Lines Predicates
AND invdist.line_type_lookup_code = 'ITEM' -- Need to verify
AND invdist.invoice_id = lin.invoice_id
AND invdist.invoice_line_number = lin.line_number
AND lin.application_id = 540
AND lin.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND lin.reference_key1 = tpl.id
AND lin.invoice_id = inv.invoice_id
-- AP to XLA Relations
AND invdist.accounting_event_id = xe.event_id
AND xe.application_id = 200 -- Payables
AND xe.event_status_code = 'P' -- Pick Only processed XLA Events
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xd.event_id = xe.event_id
AND xd.application_id = 200 -- Payables Application
AND xd.source_distribution_id_num_1 = invdist.invoice_distribution_id
AND xd.source_distribution_type = 'AP_INV_DIST'
AND xd.ae_header_id = xh.ae_header_id
AND xl.ae_header_id = xh.ae_header_id
AND xl.ledger_id = p_ledger_id
AND xd.ae_line_num = xl.ae_line_num --XD,XH,XL END
-- Restrict to only one Distribution Line based on the Transaction Activity Code Add/Substract
-- If its Add Consider only Debit, else if its Substract consider Credit
AND
(
DECODE(xl.entered_cr, NULL, 'DEBIT_DIST', 'CREDIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
OR
DECODE(xl.entered_dr, NULL, 'CREDIT_DIST', 'DEBIT_DIST' ) =
DECODE(rtryp.add_substract_code,
'ADD', 'DEBIT_DIST', 'SUBTRACT', 'CREDIT_DIST' )
)
-- From SLA to GL Tables Link
-- Important Predicate: XLA Distribution Links should have the GL Import Link ID
AND gi.gl_sl_link_id = xl.gl_sl_link_id
AND gl.ledger_id = xl.ledger_id
AND gi.gl_sl_link_table = xl.gl_sl_link_table
AND gi.je_header_id = gh.je_header_id
AND gh.je_header_id = gl.je_header_id
AND gi.je_line_num = gl.je_line_num
-- AND gl.code_combination_id = cc.ccid
AND gh.je_source = 'Payables'
-- Important Predicate: gl_je_headers.status can be either Posted Or Unposted
AND gh.status IN ( 'U', 'P' ) -- Unposted or Posted Entries
);
'After Inserting the Payables Invoice Transactions Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
UPDATE okl_g_reports_gt
SET value1_text -- trx_detail_type_code
= 'TRX_POST_NON_SPEC_ACCOUNTS'
WHERE value1_text -- trx_detail_type_code
= 'TRX_POSTED'
AND NVL( value4_num -- trx_acc_dr_ccid
,value5_num -- trx_acc_cr_ccid
)
NOT IN
(
SELECT ccid
FROM okl_code_cmbns_gt
);
UPDATE okl_g_reports_gt
SET value1_text -- trx_detail_type_code
= 'TRX_POST_NON_SPEC_PERIOD'
WHERE value1_text -- trx_detail_type_code
= 'TRX_POSTED'
AND ( value2_date -- GL_DATE
< p_start_date
OR value2_date -- GL_DATE
> p_end_date
);
'After Inserting the Trx. Data in GT Table Start Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
SELECT activity_code activity_code
FROM okl_reports_b rep
WHERE rep.report_id = p_report_id;
SELECT stm.khr_id khr_id
,SUM(se.amount) unbilled_amount
FROM okl_streams_rep_v stm
,okl_strm_elements se
,okl_strm_type_v sty
WHERE
stm.khr_id = p_khr_id
AND se.stm_id = stm.id
AND sty.id = stm.sty_id
-- Stream Type should be setup as the Balancing Stream in Report Definition
AND stm.sty_id IN
(
SELECT sty_id
FROM okl_report_stream_params rsp
WHERE rsp.report_id = p_report_id
)
AND stm.say_code <>'WORKING'
-- Only Active Streams are used by down stream processes like Billing/Accrual
AND stm.active_yn = 'Y'
-- No need to Consider WORK Streams as they are never used by Billing/Accrual
AND stm.say_code <> 'WORK'
AND
( -- If Stream is in Current Status, date_current should <= p_date
(
stm.say_code = 'CURR'
AND stm.date_current <= p_date
)
OR -- If Stream is Historized, p_date should be in between
-- date_current and date_history
(
stm.say_code = 'HIST'
AND stm.date_current <= p_date
AND stm.date_history > p_date
)
)
AND
(
-- Case 1: Activity Code is NULL, hence no other predicates
-- should be considered
p_activity IS NULL
-- Case 2: Activity Code is UNBILL, then fetch only those
-- streams which arenot billed untill that date
OR (
p_activity = 'UNBILL'
-- Stream Billed Date should be later than the p_date
AND NVL(se.date_billed, p_date ) >= p_date
) -- End Case 3: Activity Code is UNBILL
-- Case 3:
-- ACTIVITY Code is UNACCRUED.
-- Consider only Un Accrued Streams as on the p_date.
-- Assumption: Stream Element Date has to be considered as the
-- Streams Accrual Date
OR (
-- Case: Activity Code is UNACCRUED
p_activity = 'UNACCRUED'
AND
( -- Case: UNACCURED (AND)
(
-- Stream was Un-Accrued till date, so considered this
NVL( se.accrued_yn, 'N' ) = 'N'
) -- Accrued YN = N
OR
(
se.accrued_yn = 'Y'
AND se.stream_element_date > p_date
)
) -- End Case: UNACCURED (AND)
) -- End Case 3: Activity Code is UNACCRUED
) -- End of AND Clause based on Activity Codes
GROUP BY stm.khr_id;
'Before Call to Bulk Insert ' || TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt VALUES l_strm_bal_tbl(i);
'After Call to Bulk Insert ' || TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
SELECT gl.period_set_name period_set_name
,per.period_type period_type
FROM gl_ledgers gl
,gl_periods per
WHERE gl.ledger_id = p_ledger_id
AND per.period_set_name = gl.period_set_name
AND per.period_name = p_period_from;
'Before Inserting the Accounting Journals From GL Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Manual Posted Journal Entries in GL
SELECT 'GL_ACC_OTHER_APPS' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,gh.je_source trx_event_name
,NULL trx_type_name
,NULL trx_sty_name
,gh.name trx_number
,NULL contract_number
,NULL asset_number
,NULL book_classification
,NULL product_name
,NULL trx_sty_purpose
,NULL trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,gl.code_combination_id code_combination_id
-- Number Format Columns
,gl.entered_dr trx_dr_amount
,gl.entered_cr trx_cr_amount
,( NVL(gl.entered_dr,0) - NVL(gl.entered_cr,0) )
trx_net_amount
,NULL trx_activity_code
,gl.ledger_id trx_ledger_id
,NULL trx_operating_unit_id
,NULL trx_khr_id
,NULL txl_asset_id
,NULL trx_pdt_id
,NULL trx_sty_id
,101 trx_application_id
-- DATE Format Columns
,gh.default_effective_date trx_date
,NULL gl_date
-- Additional Columns
,NULL trx_id
,NULL trx_txl_id
,NULL trx_try_id
,NULL trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
-- Code Combination GT Table
,okl_code_cmbns_gt cc
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.je_source = 'Manual'
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
-- Predicates to fetch Names
AND ledger.ledger_id = gl.ledger_id
AND app.application_id = 101 -- GL Appliation Id
AND gh.default_effective_date >= p_start_date
AND gh.default_effective_date <= p_end_date
-- End of Query Segment to fetch Manual Posted Journal Entries from GL Application
);
'After Inserting the Accounting Journals From GL End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Accounting Journals From Non GL, OLM, AR, AP, FA Applications:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Posted Journal Entries from
-- applications other than GL, OLM, FA, AR and AP
SELECT 'GL_ACC_OTHER_APPS' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,NULL trx_type_name
,NULL trx_sty_name
,xte.transaction_number trx_number
,NULL contract_number
,NULL asset_number
,NULL book_classification
,NULL product_name
,NULL trx_sty_purpose
,NULL trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,( NVL(xl.entered_dr,0) - NVL(xl.entered_cr,0) )
trx_net_amount
,NULL trx_activity_code
,xl.ledger_id trx_ledger_id
,NULL trx_operating_unit_id
,NULL trx_khr_id
,NULL txl_asset_id
,NULL trx_pdt_id
,NULL trx_sty_id
,xl.application_id trx_application_id
-- DATE Format Columns
,xe.transaction_date trx_date
,NULL gl_date
-- Additional Columns
,NULL trx_id
,NULL trx_txl_id
,NULL trx_try_id
,NULL trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_events xe
,xla_event_types_vl xvl
,xla_transaction_entities xte
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xe.event_id = xh.event_id
AND xe.application_id
NOT IN
( 540 -- Leasing and Finance Management
,200 -- Payables
,222 -- Receivables
)
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xte.entity_id = xe.entity_id
AND xte.application_id = xe.application_id
-- Predicates to fetch the Names
AND ledger.ledger_id = xl.ledger_id
AND app.application_id = xe.application_id
-- Restrict the Journal Entries to be in between Start and End Dates
AND gh.default_effective_date >= p_start_date
AND gh.default_effective_date <= p_end_date
-- End of Query Segment to fetch Posted Acc. Entries in GL
-- from applications other than GL, OLM, FA, AR and AP
);
'After Inserting the Accounting Journals From Non GL, OLM, AR, AP, FA Applications End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Accounting Journals in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
,value15_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
,trx_xla_event_id --value15_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Posted Journal Entries from GL from Lease Contracts
SELECT 'GL_ACC_OLM_ENTRIES' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,try.name trx_type_name
,sty.NAME trx_sty_name
,trx.trx_number trx_number
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification
,pdt.NAME product_name
,sty.styb_purpose_meaning trx_sty_purpose
,ou.name trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,( NVL(xl.entered_dr,0) - NVL(xl.entered_cr,0) )
trx_net_amount
,DECODE( xl.entered_dr, NULL, 'SUBTRACT', 'ADD' )
trx_activity_code
,xl.ledger_id trx_ledger_id
,trx.org_id trx_operating_unit_id
,trx.khr_id trx_khr_id
,txl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txl.sty_id trx_sty_id
,xl.application_id trx_application_id
,xe.event_id trx_xla_event_id
-- DATE Format Columns
,trx.transaction_date trx_date
,dist.gl_date gl_date
-- Additional Columns
,trx.id trx_id
,txl.id trx_txl_id
,trx.try_id trx_try_id
,dist.id trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_distribution_links xd
,xla_events xe
,xla_event_types_vl xvl
-- OLM Entities
,okl_trns_acc_dstrs_all dist
,okl_txl_cntrct_lns_all txl
,okl_trx_contracts_all trx
,okl_trx_types_v try
,okl_strm_type_v sty
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
-- To fetch Names
,hr_operating_units ou
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.je_source = 'Lease'
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xd.application_id = 540 -- Restrict to Lease Journals
AND xd.ae_header_id = xh.ae_header_id
AND xd.ae_line_num = xl.ae_line_num
AND xe.event_id = xd.event_id
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
-- XLA to OLM Predicates
AND xd.event_id = dist.accounting_event_id
AND dist.id = xd.source_distribution_id_num_1
AND dist.posted_yn = 'Y'
-- OLM Predicates
AND dist.source_table = 'OKL_TXL_CNTRCT_LNS'
AND dist.source_id = txl.id
AND trx.id = txl.tcn_id
AND trx.try_id = try.id
AND txl.sty_id = sty.id
AND trx.khr_id = chr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- Predicates to fetch the Names
AND ou.organization_id = trx.org_id
AND ledger.ledger_id = trx.set_of_books_id
AND app.application_id = xe.application_id
-- Restrict the Journal Entries to be in between Start and End Dates
AND gh.default_effective_date >= p_start_date
AND gh.default_effective_date <= p_end_date
-- End of Query Segment to fetch Posted Acc. Entries from GL
);
'After Inserting the Lease Accounting Journals Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Receivables Invoice Accounting Journals in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
,value15_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
,trx_xla_event_id --value15_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Posted Journal Entries from GL
-- On behalf of OLM Lease Contract Receivables Invoice
SELECT 'GL_ACC_OLM_ENTRIES' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,try.name trx_type_name
,sty.NAME trx_sty_name
,rct.trx_number trx_number
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification
,pdt.NAME product_name
,sty.styb_purpose_meaning trx_sty_purpose
,ou.name trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,DECODE( xl.entered_cr
,NULL, txd.amount
,0 ) trx_dr_amount
,DECODE( xl.entered_dr
,NULL, txd.amount
,0 ) trx_cr_amount
,DECODE( xl.entered_cr, NULL, txd.amount, txd.amount * -1 )
trx_net_amount
,DECODE( xl.entered_dr, NULL, 'SUBTRACT', 'ADD' )
trx_activity_code
,xl.ledger_id trx_ledger_id
,tai.org_id trx_operating_unit_id
,tai.khr_id trx_khr_id
,txd.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txd.sty_id trx_sty_id
,xl.application_id trx_application_id
,xe.event_id trx_xla_event_id
-- DATE Format Columns
,tai.transaction_date trx_date
,rad.gl_date gl_date
-- Additional Columns
,tai.id trx_id
,txd.id trx_txl_id
,tai.try_id trx_try_id
,NULL trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_distribution_links xd
,xla_events xe
,xla_event_types_vl xvl
-- AR Tables
,ra_cust_trx_line_gl_dist_all rad
,ra_customer_trx_lines_all rcl
,ra_customer_trx_all rct
-- OLM Tables
,okl_txd_ar_ln_dtls_b txd
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- To fetch Names
,hr_operating_units ou
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.je_source = 'Receivables'
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xd.application_id = 222 -- Restrict to Receivables Journals
AND xd.ae_header_id = xh.ae_header_id
AND xd.ae_line_num = xl.ae_line_num
AND xe.event_id = xd.event_id
AND xe.event_status_code = 'P'
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
-- XLA to AR Predicates
AND rad.event_id = xd.event_id
AND rad.cust_trx_line_gl_dist_id = xd.source_distribution_id_num_1
-- AR Predicates
AND rcl.customer_trx_line_id = rad.customer_trx_line_id
AND rct.customer_trx_id = rcl.customer_trx_id
-- OKL to AR Predicates
AND rcl.interface_line_attribute14 = txd.id
AND rcl.interface_line_attribute6 = chr.contract_number
-- OKL Predicates
AND txd.til_id_details = til.id -- Trx. Detail to Trx. Line
AND til.tai_id = tai.id -- Trx. Line to Trx. Header
AND txd.khr_id = chr.id -- Trx. Header to Contract
AND tai.try_id = try.id
AND txd.sty_id = sty.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- Predicates to fetch the Names
AND ou.organization_id = tai.org_id
AND ledger.ledger_id = tai.set_of_books_id
AND app.application_id = xe.application_id
AND gh.default_effective_date >= p_start_date
AND gh.default_effective_date <= p_end_date
-- End of Query Segment to fetch Posted Acc. Entries from GL
-- from Receivables for a Lease Contract Invoice Transactions
);
'After Inserting the Receivables Invoice Accounting Journals Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Receivables Adjustment Accounting Journals in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
,value15_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
,trx_xla_event_id --value15_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Posted Journal Entries from GL
-- On behalf of OLM Lease Contract Receivables Adjustments
SELECT 'GL_ACC_OLM_ENTRIES' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,try.name trx_type_name
,sty.NAME trx_sty_name
,radj.adjustment_number trx_number
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification
,pdt.NAME product_name
,sty.styb_purpose_meaning trx_sty_purpose
,ou.name trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,( NVL(xl.entered_dr,0) - NVL(xl.entered_cr,0) )
trx_net_amount
,DECODE( xl.entered_dr, NULL, 'SUBTRACT', 'ADD' )
trx_activity_code
,xl.ledger_id trx_ledger_id
,adj.org_id trx_operating_unit_id
,ajl.khr_id trx_khr_id
,ajl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,ajl.sty_id trx_sty_id
,xl.application_id trx_application_id
,xe.event_id trx_xla_event_id
-- DATE Format Columns
,adj.transaction_date trx_date
,adj.gl_date gl_date
-- Additional Columns
,adj.id trx_id
,ajl.id trx_txl_id
,adj.try_id trx_try_id
,NULL trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_distribution_links xd
,xla_events xe
,xla_event_types_vl xvl
-- AR Tables
,ar_distributions_all rdist
,ar_adjustments_all radj
-- OLM Tables
,okl_txl_adjsts_lns_all_b ajl
,okl_trx_ar_adjsts_all_b adj
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- To fetch Names
,hr_operating_units ou
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.je_source = 'Receivables'
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xd.application_id = 222 -- Restrict to Receivables Journals
AND xd.ae_header_id = xh.ae_header_id
AND xd.ae_line_num = xl.ae_line_num
AND xe.event_id = xd.event_id
AND xe.event_status_code = 'P'
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
-- XLA to AR Predicates
AND xd.event_id = radj.event_id
AND xd.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND xd.source_distribution_id_num_1 = rdist.line_id
-- AR Predicates
AND rdist.source_type = 'ADJ' -- Assumption
AND radj.adjustment_id = rdist.source_id
-- OKL to AR Predicates
AND ajl.receivables_adjustment_id = radj.adjustment_id
-- OKL Predicates
AND adj.id = ajl.adj_id
AND adj.try_id = try.id
AND ajl.sty_id = sty.id
AND ajl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- Predicates to fetch the Names
AND ou.organization_id = adj.org_id
AND ledger.ledger_id = xl.ledger_id
AND app.application_id = xe.application_id
AND gh.default_effective_date >= p_start_date
AND gh.default_effective_date <= p_end_date
-- End of Query Segment to fetch Posted Acc. Entries from GL
-- from Receivables for a Lease Contract Receivable Adjustment
);
'After Inserting the Receivables Adjustment Accounting Journals Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the Payables Invoice Accounting Journals in GT Table Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
,value15_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
,trx_xla_event_id --value15_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Posted Journal Entries from GL
-- On behalf of OLM Lease Contract Receivables Adjustments
SELECT 'GL_ACC_OLM_ENTRIES' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,try.name trx_type_name
,sty.name trx_sty_name
,inv.invoice_num trx_number
,chr.contract_number contract_number
,NULL asset_number
,gts.deal_type book_classification
,pdt.NAME product_name
,sty.styb_purpose_meaning trx_sty_purpose
,ou.name trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,( NVL(xl.entered_dr,0) - NVL(xl.entered_cr,0) )
trx_net_amount
,DECODE( xl.entered_dr, NULL, 'SUBTRACT', 'ADD' )
trx_activity_code
,xl.ledger_id trx_ledger_id
,tap.org_id trx_operating_unit_id
,tpl.khr_id trx_khr_id
,tpl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,tpl.sty_id trx_sty_id
,xl.application_id trx_application_id
,xe.event_id trx_xla_event_id
-- DATE Format Columns
,inv.invoice_date trx_date
,inv.invoice_date gl_date
-- Additional Columns
,tap.id trx_id
,tpl.id trx_txl_id
,tap.try_id trx_try_id
,invdist.invoice_distribution_id trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_distribution_links xd
,xla_events xe
,xla_event_types_vl xvl
-- AP Tables
,ap_invoice_distributions_all invdist
,ap_invoice_lines_all lin
,ap_invoices_all inv
-- OLM Tables
,okl_txl_ap_inv_lns_all_b tpl
,okl_trx_ap_invs_all_b tap
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_trx_types_v try
,okl_strm_type_v sty
-- To fetch Names
,hr_operating_units ou
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.je_source = 'Payables'
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xd.application_id = 200 -- Restrict to Payables Journals
AND xd.ae_header_id = xh.ae_header_id
AND xd.ae_line_num = xl.ae_line_num
AND xe.event_id = xd.event_id
AND xe.event_status_code = 'P'
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
-- XLA to AP Predicates
AND xd.event_id = invdist.accounting_event_id
AND xd.source_distribution_type = 'AP_INV_DIST'
AND xd.source_distribution_id_num_1 = invdist.invoice_distribution_id
-- AP Predicates
AND invdist.line_type_lookup_code = 'ITEM' -- Need to verify
AND invdist.invoice_line_number = lin.line_number
AND invdist.invoice_id = lin.invoice_id
AND lin.invoice_id = inv.invoice_id
-- OKL to AP Predicates
AND lin.application_id = 540
AND lin.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND lin.reference_key1 = tpl.id
-- OKL Predicates
AND tap.id = tpl.tap_id
AND tap.try_id = try.id
AND tpl.sty_id = sty.id
AND tpl.khr_id = khr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- Predicates to fetch the Names
AND ou.organization_id = tap.org_id
AND ledger.ledger_id = xl.ledger_id
AND app.application_id = xe.application_id
AND gh.default_effective_date >= p_start_date
AND gh.default_effective_date <= p_end_date
-- End of Query Segment to fetch Posted Acc. Entries from GL
-- from Payables for a Lease Contract Payables Invoice Transaction
);
'After Inserting the Payables Invoice Accounting Journals Data in GT Table End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
UPDATE okl_g_reports_gt gt
SET value1_text = 'GL_ACC_NON_SPEC_CRITERIA'
WHERE value1_text = 'GL_ACC_OLM_ENTRIES'
-- Need to Cover Two Cases:
-- Case 1: When Only the Transaction Type is mentioned
-- Case 2: When Combination of Transaction and Stream Type is mentioned
AND
(
-- Step 1: Case (a)
NOT EXISTS
(
SELECT 1
FROM okl_report_trx_params trep
WHERE trep.try_id = gt.value8_num -- trx_try_id
AND NVL( trep.sty_id,
gt.value12_num -- Stream Type Id
) = gt.value12_num -- Stream Type Id
-- Step 1: Case (b)
AND trep.add_substract_code = value17_text -- Debit/Credit or Add/Subtract
AND trep.report_id = p_report_id
)
-- Step 1: Case (C)
OR value11_num -- Product Id
NOT IN
( SELECT product_id
FROM okl_rep_products_gt pdt_gt
)
);
UPDATE okl_g_reports_gt gt
SET value1_text = 'GL_ACC_NON_SPEC_PERIOD'
,value12_text -- trx_period_name
= (
SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = l_period_set_name
AND per.START_DATE <= value1_date
AND per.end_date >= value1_date
AND per.adjustment_period_flag = 'N'
AND period_type = l_period_type
)
WHERE value1_text = 'GL_ACC_OLM_ENTRIES'
AND
(
p_start_date > value1_date -- Transaction Date
OR p_end_date < value1_date
);
'Before Inserting the Accounting Journals AR and AP Applications but not related to OLM:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value2_text
,value3_text
,value4_text
,value5_text
,value6_text
,value7_text
,value8_text
,value9_text
,value10_text
,value11_text
,value12_text
,value13_text
,value14_text
,value15_text
,value16_text
,value17_text
,value18_text
-- Number Formatted Columns
,value1_num
,value2_num
,value3_num
,value4_num
,value5_num
,value6_num
,value7_num
,value8_num
,value9_num
,value10_num
,value11_num
,value12_num
,value13_num
,value15_num
-- Date Formatted Columns
,value1_date
,value2_date
)
SELECT -- String Formatted Columns
gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
,trx_xla_event_id --value15_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
FROM
(
-- Query Segment to find the Posted Journal Entries from
-- AR and AP applications but not related to Lease
SELECT 'GL_ACC_OTHER_APPS' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,NULL trx_type_name
,NULL trx_sty_name
,xte.transaction_number trx_number
,NULL contract_number
,NULL asset_number
,NULL book_classification
,NULL product_name
,NULL trx_sty_purpose
,NULL trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,( NVL(xl.entered_dr,0) - NVL(xl.entered_cr,0) )
trx_net_amount
,NULL trx_activity_code
,xl.ledger_id trx_ledger_id
,NULL trx_operating_unit_id
,NULL trx_khr_id
,NULL txl_asset_id
,NULL trx_pdt_id
,NULL trx_sty_id
,xl.application_id trx_application_id
,xe.event_id trx_xla_event_id
-- DATE Format Columns
,xe.transaction_date trx_date
,NULL gl_date
-- Additional Columns
,NULL trx_id
,NULL trx_txl_id
,NULL trx_try_id
,NULL trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_events xe
,xla_event_types_vl xvl
,xla_transaction_entities xte
,gl_ledgers ledger
,fnd_application_vl app
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xe.event_id = xh.event_id
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
AND xte.entity_id = xe.entity_id
AND xte.application_id = xe.application_id
-- Important: Fetch Only Accounting Events from AR and AP Only
AND xe.application_id
IN
( 200 -- Payables
,222 -- Receivables
)
-- These Accounting Events should not have been fetched
-- as part of Entries related to Lease fetched above
AND xe.event_id
NOT IN
(
SELECT DISTINCT gt.value15_num -- trx_xla_event_id
FROM okl_g_reports_gt gt
WHERE gt.value1_text IN
(
'GL_ACC_OLM_ENTRIES'
,'GL_ACC_NON_SPEC_CRITERIA'
,'GL_ACC_NON_SPEC_PERIOD'
)
)
-- Predicates to fetch the Names
AND ledger.ledger_id = xl.ledger_id
AND app.application_id = xe.application_id
-- Restrict the Journal Entries to be in between Start and End Dates
AND gl.period_name IN
(
SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = l_period_set_name
AND per.START_DATE >= p_start_date
AND per.end_date <= p_end_date
AND per.adjustment_period_flag = 'N'
AND period_type = l_period_type
)
-- End of Query Segment to fetch Posted Acc. Entries in GL
-- from applications other than GL, OLM, FA, AR and AP
);
'After Inserting the Accounting Journals AR and AP Applications but not related to OLM:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the GL Opening Balances Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_OPENING_BALANCE' --value1_text
,currency_code --value11_text
,period_name --value12_text
,account_number --value13_text -- Stores the Segment Number
-- Number Formatted Columns
,SUM(opening_bal_trx_curr) --value1_num
,SUM(opening_bal_led_curr) --value2_num
FROM
(
-- Query Segment to fetch the GL Balances for Non-Ledger Currency
SELECT cc_gt.account_number account_number -- Stores the Segment Number
,cc_gt.ccid ccid
,bal.currency_code currency_code
,bal.period_name period_name
,( begin_balance_dr
- begin_balance_cr ) opening_bal_trx_curr
,( begin_balance_dr_beq
- begin_balance_cr_beq ) opening_bal_led_curr
FROM gl_balances bal
,gl_ledgers led
,okl_code_cmbns_gt cc_gt
WHERE led.ledger_id = p_ledger_id
AND bal.ledger_id = led.ledger_id
-- opening Balance as on Period Start
AND bal.translated_flag IS NULL
AND bal.currency_code <> 'STAT'
AND bal.period_name = p_period_from
AND bal.actual_flag = 'A'
AND bal.currency_code <> led.currency_code
AND bal.currency_code <> 'STAT'
AND bal.code_combination_id = cc_gt.ccid
UNION ALL
-- Query Segment to fetch the GL Balances for Ledger Currency
-- Logic: GL Balances store thes Accumulated Balance of all Currencies
-- in the Ledger Currency. Hence need to deduct the non-ledger
-- currency Total from the GL Balance
SELECT account_number account_number -- Stores the Segment Number
,ccid ccid
,currency_code currency_code
,period_name period_name
,nvl(opening_bal_led_curr,0) opening_bal_trx_curr
,nvl(opening_bal_led_curr,0) opening_bal_led_curr
FROM
(
SELECT cc_gt.account_number account_number -- Segment Number
,cc_gt.ccid ccid
,bal.currency_code currency_code
,bal.period_name period_name
,( begin_balance_dr
- begin_balance_cr ) opening_bal_led_curr
FROM gl_balances bal
,gl_ledgers led
,okl_code_cmbns_gt cc_gt
WHERE led.ledger_id = p_ledger_id
AND bal.ledger_id = led.ledger_id
-- opening Balance as on Period Start
AND bal.translated_flag IS NULL
AND bal.currency_code <> 'STAT'
AND bal.period_name = p_period_from
AND bal.actual_flag = 'A'
AND bal.currency_code = led.currency_code
AND bal.code_combination_id = cc_gt.ccid
)
)
GROUP BY -- String Formatted Columns
'GL_OPENING_BALANCE'
,currency_code
,period_name
,account_number
; -- End of Open Balances Query
UPDATE okl_g_reports_gt lc
SET value1_num =
value1_num -
NVL(
(
SELECT SUM(value2_num) -- Sum of Balance in Ledger Currency
FROM okl_g_reports_gt nlc
WHERE nlc.value1_text = 'GL_OPENING_BALANCE'
AND nlc.value12_text = lc.value12_text -- Period Name
AND nlc.value13_text = lc.value13_text -- Account Number
AND nlc.value11_text <> lc.value11_text
), 0 )
,value2_num =
value2_num -
NVL(
(
SELECT SUM(value2_num) -- Sum of Balance in Ledger Currency
FROM okl_g_reports_gt nlc
WHERE nlc.value1_text = 'GL_OPENING_BALANCE'
AND nlc.value12_text = lc.value12_text -- Period Name
AND nlc.value13_text = lc.value13_text -- Account Number
AND nlc.value11_text <> lc.value11_text
), 0 )
-- Pick only the Ledger Currency GL Opening Balances
WHERE value1_text = 'GL_OPENING_BALANCE' --value1_text
AND value11_text IN
(
SELECT currency_code
FROM gl_ledgers
WHERE ledger_id = p_ledger_id
);
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_OPENING_BALANCE' --value1_text
,(select currency_code
from gl_ledgers
where ledger_id = p_ledger_id) --value11_text
,p_period_from --value12_text
,cgt.account_number --value13_text -- Stores the Segment Number
,0 --value1_num
,0 --value2_num
FROM (select distinct account_number
from okl_code_cmbns_gt) cgt
WHERE cgt.account_number not in (select value13_text
from okl_g_reports_gt
where value1_text = 'GL_OPENING_BALANCE'
);
'After Inserting the GL Opening Balances End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the GL Period Activity Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_PERIOD_ACTIVITY' --value1_text
,currency_code --value11_text
,period_name --value12_text
,account_number --value13_text -- Stores the Segment Number
,SUM(period_activity_trx_curr) --value1_num
,SUM(period_activity_led_curr) --value2_num
FROM
(
-- Query Segment to fetch the GL Closing Balances for Non-Ledger Currency
SELECT cc_gt.account_number account_number -- Stores the Segment Number
,cc_gt.ccid ccid
,bal.currency_code currency_code
,bal.period_name period_name
,period_net_dr -
period_net_cr period_activity_trx_curr
,period_net_dr_beq -
period_net_cr_beq period_activity_led_curr
FROM gl_balances bal
,gl_ledgers led
,okl_code_cmbns_gt cc_gt
,gl_period_statuses gps
WHERE led.ledger_id = p_ledger_id
AND bal.ledger_id = led.ledger_id
AND gps.application_id = 101
and gps.ledger_id = led.ledger_id
and bal.period_name = gps.period_name
and gps.effective_period_num >= ( select pf.effective_period_num
from gl_period_statuses pf
where pf.application_id = 101
and pf.ledger_id = p_ledger_id
and pf.period_name = p_period_from
and rownum < 2
)
and gps.effective_period_num <= ( select pt.effective_period_num
from gl_period_statuses pt
where pt.application_id = 101
and pt.ledger_id = p_ledger_id
and pt.period_name = p_period_to
and rownum < 2
)
AND bal.actual_flag = 'A'
AND bal.translated_flag IS NULL
AND bal.currency_code <> 'STAT'
AND bal.currency_code <> led.currency_code
AND bal.currency_code <> 'STAT'
AND bal.code_combination_id = cc_gt.ccid
UNION ALL
-- Query Segment to fetch the GL Closing Balances for Ledger Currency
-- Logic: GL Balances store thes Accumulated Balance of all Currencies
-- in the Ledger Currency. Hence need to deduct the non-ledger
-- currency Total from the GL Balance
SELECT account_number account_number -- Stores the Segment Number
,ccid ccid
,currency_code currency_code
,period_name period_name
,nvl(period_activity_led_curr,0) period_activity_trx_curr
,nvl(period_activity_led_curr,0) period_activity_led_curr
FROM
(
SELECT cc_gt.account_number account_number -- Segment Number
,cc_gt.ccid ccid
,bal.currency_code currency_code
,bal.period_name period_name
,period_net_dr -
period_net_cr period_activity_led_curr
FROM gl_balances bal
,gl_ledgers led
,okl_code_cmbns_gt cc_gt
,gl_period_statuses gps
WHERE led.ledger_id = p_ledger_id
AND gps.application_id = 101
and gps.ledger_id = led.ledger_id
and bal.period_name = gps.period_name
and gps.effective_period_num >= ( select pf.effective_period_num
from gl_period_statuses pf
where pf.application_id = 101
and pf.ledger_id = p_ledger_id
and pf.period_name = p_period_from
and rownum < 2
)
and gps.effective_period_num <= ( select pt.effective_period_num
from gl_period_statuses pt
where pt.application_id = 101
and pt.ledger_id = p_ledger_id
and pt.period_name = p_period_to
and rownum < 2
)
AND bal.ledger_id = led.ledger_id
AND bal.translated_flag IS NULL
AND bal.currency_code <> 'STAT'
AND bal.actual_flag = 'A'
AND bal.currency_code = led.currency_code
AND bal.code_combination_id = cc_gt.ccid
)
)
GROUP BY -- String Formatted Columns
'GL_PERIOD_ACTIVITY'
,currency_code
,period_name
,account_number
;
UPDATE okl_g_reports_gt lc
SET value1_num =
value1_num -
NVL(
(
SELECT SUM(value2_num) -- Sum of Balance in Ledger Currency
FROM okl_g_reports_gt nlc
WHERE nlc.value1_text = 'GL_PERIOD_ACTIVITY'
AND nlc.value12_text = lc.value12_text -- Period Name
AND nlc.value13_text = lc.value13_text -- Account Number
AND nlc.value11_text <> lc.value11_text
), 0)
,value2_num =
value2_num -
NVL(
(
SELECT SUM(value2_num) -- Sum of Balance in Ledger Currency
FROM okl_g_reports_gt nlc
WHERE nlc.value1_text = 'GL_PERIOD_ACTIVITY'
AND nlc.value12_text = lc.value12_text -- Period Name
AND nlc.value13_text = lc.value13_text -- Account Number
AND nlc.value11_text <> lc.value11_text
), 0 )
-- Pick only the Ledger Currency GL Opening Balances
WHERE value1_text = 'GL_PERIOD_ACTIVITY' --value1_text
AND value11_text IN
(
SELECT currency_code
FROM gl_ledgers
WHERE ledger_id = p_ledger_id
);
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_PERIOD_ACTIVITY' --value1_text
,(select currency_code
from gl_ledgers
where ledger_id = p_ledger_id) --value11_text
,null --value12_text
,cgt.account_number --value13_text -- Stores the Segment Number
-- Number Formatted Columns
,0 --value1_num
,0 --value2_num
FROM (select distinct account_number
from okl_code_cmbns_gt) cgt
WHERE cgt.account_number not in (select value13_text
from okl_g_reports_gt
where value1_text = 'GL_PERIOD_ACTIVITY'
);
'After Inserting the GL Period Activity End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
'Before Inserting the GL Closing Balances Start Time:'
|| TO_CHAR(l_trace_time, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_CLOSING_BALANCE' --value1_text
,currency_code --value11_text
,period_name --value12_text
,account_number --value13_text -- Stores the Segment Number
-- Number Formatted Columns
-- Populating the Closing Balance asa Negative Amount
-- for XML Publisher Layout Publisher
-- In the Layout sum( closing balance + gl accounts ) = 0 hence
-- negated the Closing Balance
,SUM(closing_bal_trx_curr) * -1 --value1_num
,SUM(closing_bal_led_curr) * -1 --value2_num
FROM
(
-- Query Segment to fetch the GL Closing Balances for Non-Ledger Currency
SELECT cc_gt.account_number account_number -- Stores the Segment Number
,cc_gt.ccid ccid
,bal.currency_code currency_code
,bal.period_name period_name
,(
( period_net_dr
+ begin_balance_dr
)
-
( period_net_cr
+ begin_balance_cr
)
) closing_bal_trx_curr
,(
( period_net_dr_beq
+ begin_balance_dr_beq
)
-
( period_net_cr_beq
+ begin_balance_cr_beq
)
) closing_bal_led_curr
FROM gl_balances bal
,gl_ledgers led
,okl_code_cmbns_gt cc_gt
WHERE led.ledger_id = p_ledger_id
AND bal.ledger_id = led.ledger_id
-- Closing Balance as on Period End
AND bal.period_name = p_period_to
AND bal.actual_flag = 'A'
AND bal.translated_flag IS NULL
AND bal.currency_code <> 'STAT'
AND bal.currency_code <> led.currency_code
AND bal.currency_code <> 'STAT'
AND bal.code_combination_id = cc_gt.ccid
UNION ALL
-- Query Segment to fetch the GL Closing Balances for Ledger Currency
-- Logic: GL Balances store thes Accumulated Balance of all Currencies
-- in the Ledger Currency. Hence need to deduct the non-ledger
-- currency Total from the GL Balance
SELECT account_number account_number -- Stores the Segment Number
,ccid ccid
,currency_code currency_code
,period_name period_name
,nvl(closing_bal_led_curr,0) closing_bal_trx_curr
,nvl(closing_bal_led_curr,0) closing_bal_led_curr
FROM
(
SELECT cc_gt.account_number account_number -- Segment Number
,cc_gt.ccid ccid
,bal.currency_code currency_code
,bal.period_name period_name
,(
( period_net_dr
+ begin_balance_dr
)
-
( period_net_cr
+ begin_balance_cr
)
) closing_bal_led_curr
FROM gl_balances bal
,gl_ledgers led
,okl_code_cmbns_gt cc_gt
WHERE led.ledger_id = p_ledger_id
AND bal.ledger_id = led.ledger_id
-- Closing Balance as on Period End
AND bal.period_name = p_period_to
AND bal.translated_flag IS NULL
AND bal.currency_code <> 'STAT'
AND bal.actual_flag = 'A'
AND bal.currency_code = led.currency_code
AND bal.code_combination_id = cc_gt.ccid
)
)
GROUP BY -- String Formatted Columns
'GL_CLOSING_BALANCE'
,currency_code
,period_name
,account_number
;
UPDATE okl_g_reports_gt lc
SET value1_num =
value1_num -
NVL(
(
SELECT SUM(value2_num) -- Sum of Balance in Ledger Currency
FROM okl_g_reports_gt nlc
WHERE nlc.value1_text = 'GL_CLOSING_BALANCE'
AND nlc.value12_text = lc.value12_text -- Period Name
AND nlc.value13_text = lc.value13_text -- Account Number
AND nlc.value11_text <> lc.value11_text
), 0)
,value2_num =
value2_num -
NVL(
(
SELECT SUM(value2_num) -- Sum of Balance in Ledger Currency
FROM okl_g_reports_gt nlc
WHERE nlc.value1_text = 'GL_CLOSING_BALANCE'
AND nlc.value12_text = lc.value12_text -- Period Name
AND nlc.value13_text = lc.value13_text -- Account Number
AND nlc.value11_text <> lc.value11_text
), 0 )
-- Pick only the Ledger Currency GL Opening Balances
WHERE value1_text = 'GL_CLOSING_BALANCE' --value1_text
AND value11_text IN
(
SELECT currency_code
FROM gl_ledgers
WHERE ledger_id = p_ledger_id
);
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_CLOSING_BALANCE' --value1_text
,(select currency_code
from gl_ledgers
where ledger_id = p_ledger_id) --value11_text
,p_period_to --value12_text
,cgt.account_number --value13_text -- Stores the Segment Number
-- Number Formatted Columns
,0 --value1_num
,0 --value2_num
FROM (select distinct account_number
from okl_code_cmbns_gt) cgt
WHERE cgt.account_number not in (select value13_text
from okl_g_reports_gt
where value1_text = 'GL_CLOSING_BALANCE'
);
'After Inserting the GL Closing Balances End Time:'
|| TO_CHAR(SYSDATE, 'DD-MON-YYY HH:MM:SS') );
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_CLOSING_BALANCE' --value1_text
,value11_text --value11_text -- Currency Code
,NULL --value12_text
,NULL --value13_text
,0 --value1_num
,0 --value2_num
FROM okl_g_reports_gt
WHERE value1_text IN ( 'OKL_STR_CLOSE' )
AND value11_text
NOT IN
(
SELECT DISTINCT value11_text
FROM okl_g_reports_gt
WHERE value1_text = 'GL_CLOSING_BALANCE'
)
GROUP BY 'GL_CLOSING_BALANCE'
,value11_text;
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value12_text
,value13_text
-- Number Formatted Columns
,value1_num
,value2_num
)
SELECT -- String Formatted Columns
'GL_OPENING_BALANCE' --value1_text
,value11_text --value11_text -- Currency Code
,NULL --value12_text
,NULL --value13_text
,0 --value1_num
,0 --value2_num
FROM okl_g_reports_gt
WHERE value1_text IN ( 'OKL_STR_OPEN' )
AND value11_text
NOT IN
(
SELECT DISTINCT value11_text
FROM okl_g_reports_gt
WHERE value1_text = 'GL_OPENING_BALANCE'
)
GROUP BY 'GL_OPENING_BALANCE'
,value11_text;
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value5_text -- Contract Number
,value14_text -- Organization Name
-- Number Formatted Columns
,value3_num -- Stream Balances Amount
)
SELECT -- String Formatted Columns
'OKL_STR_CLOSE' --value1_text
,value11_text --value11_text -- Currency Code
,NULL --value5_text
,NULL --value14_text
,0 --value3_num
FROM okl_g_reports_gt
WHERE value1_text IN ( 'GL_CLOSING_BALANCE' )
AND value11_text
NOT IN
(
SELECT DISTINCT value11_text
FROM okl_g_reports_gt
WHERE value1_text = 'OKL_STR_CLOSE'
)
GROUP BY 'OKL_STR_CLOSE'
,value11_text;
INSERT INTO okl_g_reports_gt
(
-- String Formatted Columns
value1_text
,value11_text
,value5_text -- Contract Number
,value14_text -- Organization Name
-- Number Formatted Columns
,value3_num -- Stream Balances Amount
)
SELECT -- String Formatted Columns
'OKL_STR_OPEN' --value1_text
,value11_text --value11_text -- Currency Code
,NULL --value5_text
,NULL --value14_text
,0 --value3_num
FROM okl_g_reports_gt
WHERE value1_text IN ( 'GL_OPENING_BALANCE' )
AND value11_text
NOT IN
(
SELECT DISTINCT value11_text
FROM okl_g_reports_gt
WHERE value1_text = 'OKL_STR_OPEN'
)
GROUP BY 'OKL_STR_OPEN'
,value11_text;
SELECT organization_id org_id
,NAME org_name
FROM hr_operating_units hr
WHERE hr.set_of_books_id = p_ledger_id;
INSERT INTO okl_g_reports_gt VALUES l_invalid_orgs_tbl(i);
SELECT STY_ID
FROM okl_report_stream_params
WHERE report_id = p_report_id;
SELECT styy.id STY_ID
FROM OKL_PROD_STRM_TYPES PSYY,
OKL_STRM_TYPE_V STYY
WHERE psyy.sty_id = styy.id
AND psyy.accrual_yn = 'Y'
AND psyy.pdt_id = p_pdt_id;
SELECT param.STY_ID
FROM okl_report_trx_params param,
okl_trx_types_b trx
WHERE report_id = p_report_id
AND trx.id = param.try_id
AND trx.AEP_CODE = 'ACCRUAL'
MINUS
SELECT STY_ID
FROM okl_report_stream_params
WHERE report_id = p_report_id;
SELECT product_id
FROM okl_rep_products_gt;
SELECT OU.NAME OPERATING_UNIT,
PRO.NAME PROD_NAME,
KHR.CONTRACT_NUMBER,
KLE.NAME ASSET_NUMBER,
STY.NAME STREAM_TYPE_NAME,
STY.STYB_PURPOSE_MEANING STREAM_PURPOSE,
STE.STREAM_ELEMENT_DATE,
STE.AMOUNT,
KHR.CURRENCY_CODE,
STE.SE_LINE_NUMBER,
OU.ORGANIZATION_ID ORG_ID,
PRO.ID PDT_ID,
KHR.ID KHR_ID,
KLE.ID KLE_ID,
STY.ID STY_ID,
(SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = (SELECT gl.period_set_name
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
AND per.START_DATE <= STE.STREAM_ELEMENT_DATE
AND per.end_date >= STE.STREAM_ELEMENT_DATE
AND per.adjustment_period_flag = 'N'
AND period_type = (SELECT gl.accounted_period_type
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
) STREAM_PERIOD
FROM OKL_STRM_TYPE_V STY,
okl_streams_rep_v STM,
OKL_STRM_ELEMENTS STE,
OKC_K_HEADERS_ALL_B KHR,
OKL_K_HEADERS KH,
OKC_K_LINES_V KLE,
OKL_PRODUCTS_V PRO,
HR_OPERATING_UNITS OU
WHERE STY.ID = STM.STY_ID
AND STM.ID = STE.STM_ID
AND STM.KHR_ID = KHR.ID
-- AND STM.ACTIVE_YN = 'Y'
AND KLE.ID = STM.KLE_ID
AND KLE.DNZ_CHR_ID = KHR.ID
AND KH.ID = KHR.ID
AND KH.PDT_ID = PRO.ID
AND KHR.ORG_ID = OU.ORGANIZATION_ID
AND STY.ID IN ( SELECT styy.id
FROM OKL_PROD_STRM_TYPES PSYY,
OKL_STRM_TYPE_V STYY
WHERE psyy.sty_id = sty.id
AND psyy.accrual_yn = 'Y'
and psyy.pdt_id = KH.PDT_ID
UNION
SELECT sty_id
FROM okl_report_stream_params rsp
WHERE rsp.report_id = p_report_id
)
and KH.ID = p_khr_id
AND STM.ID IN ( SELECT MAX(ID)
FROM ( SELECT STM.STY_ID, STM.ID, MAX(DATE_CURRENT)
FROM OKL_STRM_TYPE_V STY,
okl_streams_rep_v STM,
OKL_STRM_ELEMENTS STE,
OKC_K_HEADERS_ALL_B KHR,
OKL_K_HEADERS KH,
OKC_K_LINES_V KLE,
OKL_PRODUCTS_V PRO,
HR_OPERATING_UNITS OU
WHERE STY.ID = STM.STY_ID
AND STM.ID = STE.STM_ID
AND STM.KHR_ID = KHR.ID
AND STM.DATE_CURRENT <= p_end_date
AND NVL(STM.date_history, p_end_date) between p_start_date and p_end_date
AND KHR.ID = p_khr_id
AND KLE.ID = STM.KLE_ID
AND KLE.DNZ_CHR_ID = KHR.ID
AND KH.ID = KHR.ID
AND KH.PDT_ID = PRO.ID
AND KHR.ORG_ID = OU.ORGANIZATION_ID
GROUP BY STM.STY_ID, STM.ID
ORDER BY 3 DESC
)
GROUP BY STY_ID
)
ORDER BY KHR.ID, KLE.ID, STY.ID, STE.SE_LINE_NUMBER;
SELECT OU.NAME OPERATING_UNIT,
PRO.NAME PROD_NAME,
KHR.CONTRACT_NUMBER,
KLE.NAME ASSET_NUMBER,
STY.NAME STREAM_TYPE_NAME,
STY.STYB_PURPOSE_MEANING STREAM_PURPOSE,
STE.STREAM_ELEMENT_DATE,
STE.AMOUNT,
KHR.CURRENCY_CODE,
STE.SE_LINE_NUMBER,
OU.ORGANIZATION_ID ORG_ID,
PRO.ID PDT_ID,
KHR.ID KHR_ID,
KLE.ID KLE_ID,
STY.ID STY_ID,
(SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = (SELECT gl.period_set_name
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
AND per.START_DATE <= STE.STREAM_ELEMENT_DATE
AND per.end_date >= STE.STREAM_ELEMENT_DATE
AND per.adjustment_period_flag = 'N'
AND period_type = (SELECT gl.accounted_period_type
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
) STREAM_PERIOD
FROM OKL_STRM_TYPE_V STY,
okl_streams_rep_v STM,
OKL_STRM_ELEMENTS STE,
OKC_K_HEADERS_ALL_B KHR,
OKL_K_HEADERS KH,
OKC_K_LINES_V KLE,
OKL_PRODUCTS_V PRO,
HR_OPERATING_UNITS OU
WHERE STY.ID = STM.STY_ID
AND STM.ID = STE.STM_ID
AND STM.KHR_ID = KHR.ID
-- AND STM.ACTIVE_YN = 'Y'
AND KLE.ID = STM.KLE_ID
AND KLE.DNZ_CHR_ID = KHR.ID
AND KH.ID = KHR.ID
AND KH.PDT_ID = PRO.ID
AND KHR.ORG_ID = OU.ORGANIZATION_ID
AND STY.ID IN ( SELECT styy.id
FROM OKL_PROD_STRM_TYPES PSYY,
OKL_STRM_TYPE_V STYY
WHERE psyy.sty_id = sty.id
AND psyy.accrual_yn = 'Y'
and psyy.pdt_id = KH.PDT_ID
UNION
SELECT sty_id
FROM okl_report_stream_params rsp
WHERE rsp.report_id = p_report_id
)
and KH.ID = p_khr_id
AND STM.ID IN ( SELECT MAX(ID)
FROM ( SELECT STM.STY_ID, STM.ID, MAX(DATE_CURRENT)
FROM OKL_STRM_TYPE_V STY,
okl_streams_rep_v STM,
OKL_STRM_ELEMENTS STE,
OKC_K_HEADERS_ALL_B KHR,
OKL_K_HEADERS KH,
OKC_K_LINES_V KLE,
OKL_PRODUCTS_V PRO,
HR_OPERATING_UNITS OU
WHERE STY.ID = STM.STY_ID
AND STM.ID = STE.STM_ID
AND STM.KHR_ID = KHR.ID
AND STM.DATE_CURRENT <= p_end_date
AND NVL(STM.date_history, p_end_date) between p_start_date and p_end_date
AND KHR.ID = p_khr_id --BIND CONTRACT IDS WHICH ARE ELIGIBLE BASED ON PRODUCTS
AND KLE.ID = STM.KLE_ID
AND KLE.DNZ_CHR_ID = KHR.ID
AND KH.ID = KHR.ID
AND KH.PDT_ID = PRO.ID
AND KHR.ORG_ID = OU.ORGANIZATION_ID
GROUP BY STM.STY_ID, STM.ID
ORDER BY 3 DESC
)
GROUP BY STY_ID
)
ORDER BY KHR.ID, KLE.ID, STY.ID, STE.SE_LINE_NUMBER;
SELECT * FROM
(
SELECT TCN.ID TCN_ID,
TCL.ID TCL_ID,
TCN.TRX_NUMBER,
TCL.KHR_ID,
TCL.STY_ID,
STY.NAME STREAM_TYPE,
CLE.NAME ASSET_NUMBER,
STY.STYB_PURPOSE_MEANING STREAM_PURPOSE,
TCL.AMOUNT,
TCN.CURRENCY_CODE,
TCN.ORG_ID,
OU.NAME OPERATING_UNIT,
TCN.PRODUCT_NAME,
TCL.KLE_ID,
TCN.TRANSACTION_DATE,
NULL REVERSAL_DATE,
ACCRUAL_ACTIVITY,
TCN.DATE_ACCRUAL,
TCN.DATE_TRANSACTION_OCCURRED,
TCL.LINE_NUMBER,
TTY.ID TRY_ID,
TTY.NAME TRANSACTION_TYPE,
(SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = (SELECT gl.period_set_name
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
AND per.START_DATE <= TCN.TRANSACTION_DATE
AND per.end_date >= TCN.TRANSACTION_DATE
AND per.adjustment_period_flag = 'N'
AND period_type = (SELECT gl.accounted_period_type
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
) TRANSACTION_PERIOD,
NULL REVERSAL_PERIOD
FROM OKL_TRX_CONTRACTS_ALL TCN,
OKL_TXL_CNTRCT_LNS_ALL TCL,
OKL_TRX_TYPES_V TTY,
OKL_STRM_TYPE_V STY,
HR_OPERATING_UNITS OU,
OKC_K_LINES_V CLE
WHERE TCN.ID = TCL.TCN_ID
AND TCN.TRY_ID = TTY.ID
AND TCL.STY_ID = STY.ID
AND TCL.KHR_ID = p_khr_id
AND TCN.KHR_ID = TCL.KHR_ID
AND CLE.ID (+) = TCL.KLE_ID
--
AND TCN.SET_OF_BOOKS_ID = p_ledger_id
AND TTY.ID IN (SELECT TRY_ID FROM OKL_REPORT_TRX_PARAMS where report_id = p_report_id)
AND TCN.ACCRUAL_ACTIVITY <> 'REVERSAL'
AND OU.ORGANIZATION_ID = TCN.ORG_ID
AND NVL(TCN.SOURCE_TRX_ID,-1) NOT IN (SELECT ID
FROM OKL_TRX_CONTRACTS_ALL RBK_TRX
WHERE RBK_TRX.KHR_ID = TCN.KHR_ID
AND RBK_TRX.ID = TCN.SOURCE_TRX_ID
AND TCN_TYPE in('TRBK','ALT'))
UNION ALL
SELECT TCN.ID TCN_ID,
TCL.ID TCL_ID,
TCN.TRX_NUMBER,
TCL.KHR_ID,
TCL.STY_ID,
STY.NAME STREAM_TYPE,
CLE.NAME ASSET_NUMBER,
STY.STYB_PURPOSE_MEANING STREAM_PURPOSE,
TCL.AMOUNT,
TCN.CURRENCY_CODE,
TCN.ORG_ID,
OU.NAME OPERATING_UNIT,
TCN.PRODUCT_NAME,
TCL.KLE_ID,
TCN.TRANSACTION_DATE,
TCN.TRANSACTION_REVERSAL_DATE REVERSAL_DATE,
ACCRUAL_ACTIVITY,
TCN.DATE_ACCRUAL,
TCN.DATE_TRANSACTION_OCCURRED,
TCL.LINE_NUMBER,
TTY.ID TRY_ID,
TTY.NAME TRANSACTION_TYPE,
(SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = (SELECT gl.period_set_name
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
AND per.START_DATE <= TCN.TRANSACTION_DATE
AND per.end_date >= TCN.TRANSACTION_DATE
AND per.adjustment_period_flag = 'N'
AND period_type = (SELECT gl.accounted_period_type
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
) TRANSACTION_PERIOD,
(SELECT per.period_name
FROM gl_periods per
WHERE per.period_set_name = (SELECT gl.period_set_name
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
AND per.START_DATE <= TCN.TRANSACTION_REVERSAL_DATE
AND per.end_date >= TCN.TRANSACTION_REVERSAL_DATE
AND per.adjustment_period_flag = 'N'
AND period_type = (SELECT gl.accounted_period_type
FROM gl_ledgers gl
WHERE ledger_id = p_ledger_id)
) REVERSAL_PERIOD
FROM OKL_TRX_CONTRACTS_ALL TCN,
OKL_TXL_CNTRCT_LNS_ALL TCL,
OKL_TRX_TYPES_V TTY,
OKL_STRM_TYPE_V STY,
HR_OPERATING_UNITS OU,
OKC_K_LINES_V CLE
WHERE TCN.ID = TCL.TCN_ID
AND TCN.TRY_ID = TTY.ID
AND TCL.KHR_ID = p_khr_id
AND TCL.STY_ID = STY.ID
AND TCN.KHR_ID = TCL.KHR_ID
AND CLE.ID (+) = TCL.KLE_ID
--
AND TCN.SET_OF_BOOKS_ID = p_ledger_id
AND TTY.ID IN (SELECT TRY_ID FROM OKL_REPORT_TRX_PARAMS where report_id = p_report_id)
AND TCN.ACCRUAL_ACTIVITY = 'REVERSAL'
AND OU.ORGANIZATION_ID = TCN.ORG_ID
AND NVL(TCN.SOURCE_TRX_ID,-1) NOT IN (SELECT ID
FROM OKL_TRX_CONTRACTS_ALL RBK_TRX
WHERE RBK_TRX.KHR_ID = TCN.KHR_ID
AND RBK_TRX.ID = TCN.SOURCE_TRX_ID
AND TCN_TYPE in ('TRBK','ALT'))
)
ORDER BY TCN_ID, KHR_ID, KLE_ID, STY_ID, DATE_ACCRUAL, TRANSACTION_DATE, LINE_NUMBER;
SELECT ou.name operating_unit,
prod.name Product,
khr.Contract_Number,
kle.name Asset_Number,
st.name stream_type,
st.styb_purpose_meaning Stream_purpose,
tty.NAME TRANSACTION_TYPE,
ce.Stream_Element_Date stream_element_date,
acc_trx.transaction_date,
acc_trx.trx_number,
khr.CURRENCY_CODE,
(ce.Amount - Nvl(pe.Amount,ce.Amount)) Delta_Amt,
DECODE(Sign(ce.Stream_Element_Date - to_date(p_start_date)),
- 1,'N',
DECODE(Sign(to_date(p_end_date) - ce.Stream_Element_Date),
- 1,'N',
'Y')) In_rep_Period,
ou.organization_id org_id,
prod.id product_id,
c.khr_Id khr_Id,
c.kle_Id kle_Id,
c.Sty_Id Sty_Id,
ce.se_Line_Number line_number
FROM okl_streams_rep_v c,
okl_streams_rep_v p,
Okl_strm_Type_v st,
Okl_strm_Elements ce,
Okl_strm_Elements pe,
Okc_k_Headers_All_b khr,
Okc_k_lines_v kle,
hr_operating_units ou,
okl_products prod,
okl_k_headers chr,
okl_trx_contracts_all rbk_trx,
okl_trx_contracts_all acc_trx,
okl_rep_products_gt pdt_gt,
OKL_TRX_TYPES_V TTY
WHERE c.Link_Hist_Stream_Id = p.Id
AND c.Sty_Id = st.Id
AND c.Date_Current >= p.Date_Current
AND c.kle_Id = p.kle_Id
AND ce.stm_Id = c.Id
AND pe.stm_Id = p.Id
AND ce.Stream_Element_Date = pe.Stream_Element_Date
AND ce.se_Line_Number = pe.se_Line_Number
AND c.khr_Id = khr.Id
AND khr.sts_Code IN ('BOOKED','TERMINATED')
AND Nvl(p.Date_History,p_end_date) BETWEEN p_start_date
AND p_end_date
AND Nvl(c.Date_History,p_end_date) BETWEEN p_start_date
AND p_end_date
AND c.Date_Current <= p_end_date
AND Nvl(ce.Accrued_yn,'N') = 'Y'
AND pe.Accrued_yn = 'Y'
AND kle.id = c.kle_id
AND kle.id = p.kle_id
AND khr.org_id = ou.organization_id
AND chr.id = khr.id
AND chr.pdt_id = prod.id
AND rbk_trx.khr_id = khr.id
AND rbk_trx.id = c.trx_id
AND rbk_trx.pdt_id = prod.id
AND acc_trx.source_trx_id = rbk_trx.id
AND acc_trx.khr_id = rbk_trx.khr_id
AND acc_trx.pdt_id = rbk_trx.pdt_id
AND acc_trx.khr_id = khr.id
AND acc_trx.pdt_id = prod.id
AND acc_trx.source_trx_type = 'TCN'
AND prod.id = pdt_gt.product_id
AND acc_trx.SET_OF_BOOKS_ID = p_ledger_id
AND rbk_trx.SET_OF_BOOKS_ID = p_ledger_id
AND TTY.ID IN (SELECT TRY_ID FROM OKL_REPORT_TRX_PARAMS WHERE report_id = p_report_id)
AND TTY.ID = ACC_TRX.TRY_ID
AND prod.id = p_product_id
AND (ce.Amount - Nvl(pe.Amount,ce.Amount)) <> 0
order by org_id,product_id,khr_id,kle_id,sty_id,line_number;
l_pdt_sty_id_tbl.delete;
l_master_strm_tbl.delete;
l_eligible_strm_tbl.delete;
l_non_eligible_strm_tbl.delete;
l_master_trx_tbl.delete;
l_subset_trx_tbl.delete;
l_spec_sty_id_tbl.delete;
l_subset_trx_tbl.delete;
' Inserting records in okl_g_reports_gt table for expected sum of stream elements amount');
INSERT INTO okl_g_reports_gt VALUES l_exp_streams_tbl(i);
' Inserting all records into okl_g_reports_gt');
INSERT INTO okl_g_reports_gt VALUES l_reports_tbl(i);
INSERT INTO okl_g_reports_gt
( value1_text --Bucket name
,value2_text --trx number
,value3_text --trx_type name
,value4_text --application name
,value5_text --contract number
,value6_text --asset number
,value7_text --book classification
,value8_text --product name
,value9_text --trx sty name
,value10_text --trx_sty_purpose
,value11_text --currency code
,value12_text --trx period name
,value13_text --trx account name
,value14_text --trx operating unit
,value15_text --legal entity name
,value16_text --trx ledger name
,value17_text --add subtract activity
,value18_text --trx event name
-- Number Formatted Columns
,value1_num --trx dr amt
,value2_num --trx cr amt
,value3_num --trx net amt
,value4_num --code combination id
,value5_num --code combination id
,value6_num --trx ledger id
,value7_num --Org ID
,value8_num --TRY ID
,value9_num --KHR ID
,value10_num --asset Id
,value11_num --pdt id
,value12_num --sty id
,value13_num --application id
,value15_num --xla event id
-- Date Formatted Columns
,value1_date --trx date
,value2_date --gl date
,value4_date --accrual date
)
SELECT gl_detail_type_code --value1_text
,trx_number --value2_text
,trx_type_name --value3_text
,trx_application_name --value4_text
,contract_number --value5_text
,asset_number --value6_text
,book_classification --value7_text
,product_name --value8_text
,trx_sty_name --value9_text
,trx_sty_purpose --value10_text
,currency_code --value11_text
,trx_period_name --value12_text -- Populated Only for Posted Entries
,trx_account_name --value13_text
,trx_operating_unit_name --value14_text
,NULL --value15_text -- Legal Entity Name
,trx_ledger_name --value16_text
,trx_activity_code --value17_text -- Activity Code Add/Subtract for Dr/Cr
,trx_event_name --value18_text
-- Number Formatted Columns
,trx_dr_amount --value1_num
,trx_cr_amount --value2_num
,trx_net_amount --value3_num
,code_combination_id --value4_num
,code_combination_id --value5_num
,trx_ledger_id --value6_num
,trx_operating_unit_id --value7_num
,trx_try_id --value8_num -- Transaction Type ID
,trx_khr_id --value9_num
,txl_asset_id --value10_num
,trx_pdt_id --value11_num
,trx_sty_id --value12_num
,trx_application_id --value13_num
,trx_xla_event_id --value15_num
-- Date Formatted Columns
,trx_date --value1_date
,gl_date --value2_date
,accrual_date --value4_date
FROM
(
SELECT 'NON_SPEC_PERIOD_TRX_SPEC_PERIOD' gl_detail_type_code
,gh.currency_code currency_code
,app.application_name trx_application_name
,glcc.concatenated_segments trx_account_name
,xvl.name trx_event_name
,try.name trx_type_name
,sty.NAME trx_sty_name
,trx.trx_number trx_number
,chr.contract_number contract_number
,KLE.NAME asset_number
,gts.deal_type book_classification
,pdt.NAME product_name
,sty.styb_purpose_meaning trx_sty_purpose
,ou.name trx_operating_unit_name
,ledger.name trx_ledger_name
,gh.period_name trx_period_name
,xl.code_combination_id code_combination_id
-- Number Format Columns
,xl.entered_dr trx_dr_amount
,xl.entered_cr trx_cr_amount
,( NVL(xl.entered_dr,0) - NVL(xl.entered_cr,0) )
trx_net_amount
,DECODE( xl.entered_dr, NULL, 'SUBTRACT', 'ADD' )
trx_activity_code
,xl.ledger_id trx_ledger_id
,trx.org_id trx_operating_unit_id
,trx.khr_id trx_khr_id
,txl.kle_id txl_asset_id
,khr.pdt_id trx_pdt_id
,txl.sty_id trx_sty_id
,xl.application_id trx_application_id
,xe.event_id trx_xla_event_id
-- DATE Format Columns
,trx.transaction_date trx_date
,dist.gl_date gl_date
,trx.date_accrual accrual_date
-- Additional Columns
,trx.id trx_id
,txl.id trx_txl_id
,trx.try_id trx_try_id
,dist.id trx_dist_id
FROM -- GL Tables: Import Reference, GL Header and Lines
gl_je_lines gl
,gl_je_headers gh
,gl_code_combinations_kfv glcc
,gl_import_references gi
-- Code Combination GT Table
,okl_code_cmbns_gt cc
-- SLA Entities
,xla_ae_lines xl
,xla_ae_headers xh
,xla_distribution_links xd
,xla_events xe
,xla_event_types_vl xvl
-- OLM Entities
,okl_trns_acc_dstrs_all dist
,okl_txl_cntrct_lns_all txl
,okl_trx_contracts_all trx
,okl_trx_types_v try
,okl_strm_type_v sty
,okc_k_headers_all_b chr
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
-- To fetch Names
,hr_operating_units ou
,gl_ledgers ledger
,fnd_application_vl app
,okc_k_lines_v kle
,okl_report_trx_params trep
WHERE
-- Restrict the Code Combinations to the one setup on the Report
gl.code_combination_id = cc.ccid AND
-- GL Tables
gl.ledger_id = p_ledger_id
AND gh.je_header_id = gl.je_header_id
AND gh.ledger_id = gl.ledger_id
AND gh.je_source = 'Lease'
AND gh.status = 'P' -- Pick Only Posted Journals
AND glcc.code_combination_id = gl.code_combination_id
AND gi.je_header_id = gh.je_header_id
AND gi.je_line_num = gl.je_line_num
-- GL to XLA Relations
AND xl.gl_sl_link_id = gi.gl_sl_link_id
AND xl.gl_sl_link_table = gi.gl_sl_link_table
AND xl.ledger_id = gl.ledger_id
-- XLA Predicates
AND xl.ae_header_id = xh.ae_header_id
AND xd.application_id = 540 -- Restrict to Lease Journals
AND xd.ae_header_id = xh.ae_header_id
AND xd.ae_line_num = xl.ae_line_num
AND xe.event_id = xd.event_id
AND xe.application_id = xvl.application_id
AND xvl.event_type_code = xe.event_type_code
-- XLA to OLM Predicates
AND xd.event_id = dist.accounting_event_id
AND dist.id = xd.source_distribution_id_num_1
AND dist.posted_yn = 'Y'
-- OLM Predicates
AND dist.source_table = 'OKL_TXL_CNTRCT_LNS'
AND dist.source_id = txl.id
AND trx.id = txl.tcn_id
AND trx.try_id = try.id
AND txl.sty_id = sty.id
AND trx.khr_id = chr.id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
-- Predicates to fetch the Names
AND ou.organization_id = trx.org_id
AND ledger.ledger_id = trx.set_of_books_id
AND app.application_id = xe.application_id
AND kle.id = txl.kle_id
-- Restrict the Journal Entries to be in between Start and End Dates
AND dist.gl_date between p_start_date AND p_end_date
AND trx.transaction_date not between p_start_date AND p_end_date
AND trep.try_id = trx.try_id -- trx_try_id
AND NVL( trep.sty_id, txl.sty_id) = txl.sty_id -- Stream Type Id
AND DECODE( xl.entered_dr, NULL, 'SUBTRACT', 'ADD' ) = trep.add_substract_code -- Debit/Credit or Add/Subtract
AND trep.report_id = p_report_id
);
SELECT param_num_value1
FROM okl_report_parameters oup
WHERE oup.report_id = p_report_id
AND oup.parameter_type_code = p_parameter_type_code ;
SELECT gl.start_date from_date
FROM gl_period_statuses gl
WHERE gl.application_id = 540
AND gl.set_of_books_id = p_ledger_id
AND gl.period_name = p_gl_period_from;
SELECT gl.end_date to_date
FROM gl_period_statuses gl
WHERE gl.application_id = 540
AND gl.set_of_books_id = p_ledger_id
AND gl.period_name = p_gl_period_to;
SELECT NVL(LEDGER_CATEGORY_CODE,'PRIMARY') LEDGER_CATEGORY_CODE
FROM GL_LEDGERS
WHERE LEDGER_ID = p_ledger_id;
INSERT INTO OKL_G_REPORTS_GT ( VALUE1_TEXT )
VALUES ('OKL_RECON_REP_NO_ACCESS' );