CREATE OR REPLACE PROCEDURE APPS.XX_GL_MV_DRILL_REPORT (errbuf VARCHAR2, retcode NUMBER)
AS
--START_DATE DATE;
CURSOR LEG_ID IS (SELECT '2027' LEDGER_ID FROM DUAL
UNION
SELECT '2062' LEDGER_ID FROM DUAL);
/*SELECT GSB.LEDGER_ID
FROM GL_LEDGERS GSB
WHERE EXISTS(SELECT 1 FROM GL_BALANCES GB WHERE GB.LEDGER_ID=GSB.LEDGER_ID));
*/
BEGIN
-----------------*********DELETE******--------------
EXECUTE IMMEDIATE 'TRUNCATE TABLE XX_GL_MV_REPORT';
COMMIT;
-----------------*********FLAG 1******--------------
FOR GL_LED_ID IN LEG_ID
LOOP
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '1' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date, acr.doc_sequence_value, amcd.gl_date,
acr.receipt_number trx_number, acr.cash_receipt_id,
rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, acr.receipt_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
NULL project_no, NULL trx_batch_name, jeh.status,
NVL (acr.comments, acr.reversal_comments) narration,
rc.customer_id, rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, acr.status check_status,
acr.reversal_date check_void_date, cc.account_type,
NULL reference1, jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_misc_cash_distributions_all amcd,
ar_cash_receipts_all acr,
hr_operating_units hou,
fnd_user fu,
hz_cust_site_uses_all hcsu,
ar_customers rc,
DUAL
WHERE 1 = 1
AND xlajed.source_distribution_id_num_1 = ada.line_id
AND ada.source_table = 'MCD'
AND ada.source_id = amcd.misc_cash_distribution_id
AND amcd.cash_receipt_id = acr.cash_receipt_id
AND acr.org_id = hou.organization_id
AND acr.last_updated_by = fu.user_id(+)
AND acr.pay_from_customer = rc.customer_id(+)
AND acr.customer_site_use_id = hcsu.site_use_id(+)
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
AND jeh.je_category = 'Misc Receipts'
AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
--AND JEL.EFFECTIVE_DATE BETWEEN AND SYSDATE
);
fnd_file.put_line (fnd_file.LOG,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'FLAG 1');
-----------------*********FLAG 2******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '2' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, NULL org_name,
NULL user_name, jeh.doc_sequence_value, jeh.posted_date,
adj.doc_sequence_value, adj.gl_date,
rct.trx_number trx_number, rct.customer_trx_id,
rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, jel.effective_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
NULL project_no, NULL trx_batch_name, jeh.status,
adj.comments narration, rc.customer_id, rc.customer_number,
hcsu.site_use_id, hcsu.site_use_code, NULL check_status,
NULL check_void_date, cc.account_type, NULL reference1,
jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null FBT
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_adjustments_all adj,
ra_customer_trx_all rct,
hz_cust_site_uses_all hcsu,
hr_operating_units hou,
ar_customers rc,
fnd_user fu,
DUAL
WHERE 1 = 1
AND xlajed.source_distribution_id_num_1 = ada.line_id
AND ada.source_table = 'ADJ'
AND ada.source_id = adj.adjustment_id
AND adj.org_id = hou.organization_id
AND adj.last_updated_by = fu.user_id(+)
AND rct.customer_trx_id = adj.customer_trx_id
AND rct.bill_to_customer_id = rc.customer_id(+)
AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
AND jeh.je_category = 'Adjustment'
AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
);
--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID'||GL_LED_ID.LEDGER_ID||'---'||'FLAG 2');
-----------------*********FLAG 4******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT flag, code_combination_id, SOURCE, set_of_books_id, sob, CATEGORY,
period, batch_name, header_name, description, currency,
sob_currency, entered_debit, entered_credit, accounted_debit,
accounted_credit, org_name, user_name, gl_voucher_number,
posted_date, sl_voucher_number, gl_date, trx_number,
cash_receipt_id, cust_supp_name, effective_date, receipt_date,
gl_seg1, gl_seg2, gl_seg3, gl_seg4, gl_seg5, gl_seg6, gl_seg7,
gl_seg8, gl_seg9, gl_seg10, gl_seg11, gl_seg12, gl_name_seg1,
gl_name_seg2, gl_name_seg3, gl_name_seg4, gl_name_seg5,
gl_name_seg6, gl_name_seg7, gl_name_seg8, gl_name_seg9,
gl_name_seg10, gl_name_seg11, gl_name_seg12, exp_emp_no, project_no,
trx_batch_name, status, narration, customer_id, customer_number,
site_use_id, site_use_code, check_status, check_void_date,
account_type, reference1, creation_date, je_header_id,
accrual_rev_je_header_id, accrual_rev_period_name, dummy,
payment_status, invoice_type_code, je_line_num, gl_fbt, ap_fbt
FROM (SELECT '4' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id set_of_books_id, gsb.NAME sob,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code currency, gsb.currency_code sob_currency,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credit, hou.NAME org_name,
INITCAP (fu.description) user_name,
jeh.doc_sequence_value gl_voucher_number, jeh.posted_date,
acr.doc_sequence_value sl_voucher_number,
jeh.posted_date gl_date,
--acrh.gl_date,
acr.receipt_number trx_number,
acr.cash_receipt_id, rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, acr.receipt_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
NULL trx_batch_name, jeh.status,
NVL (acr.comments, acr.reversal_comments) narration,
rc.customer_id, rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, acr.status check_status,
acr.reversal_date check_void_date, cc.account_type,
NULL reference1, jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL dummy, NULL payment_status, NULL invoice_type_code,
jel.je_line_num, jel.attribute3 gl_fbt, NULL ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_cash_receipt_history_all acrh,
ar_cash_receipts_all acr,
hr_operating_units hou,
fnd_user fu,
hz_cust_site_uses_all hcsu,
ar_customers rc,
DUAL
WHERE 1 = 1
--AND jeh.je_header_id = 1825109
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajed.source_distribution_id_num_1 = ada.line_id
AND ada.source_table = 'CRH'
--AND ada.source_type = 'CASH'
AND ada.source_type IN ('CASH', 'BANK_CHARGES')
AND ada.source_id = acrh.cash_receipt_history_id
AND acrh.cash_receipt_id = acr.cash_receipt_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
--AND jeh.je_category IN
-- ('Receipts', 'Credit Memos', 'Misc Receipts')
AND jeh.je_category IN
('Receipts',
'Credit Memos',
'Misc Receipts',
'Debit Memos'
)
AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND acrh.org_id = hou.organization_id
--AND acrh.last_updated_by = fu.user_id(+)
AND jeh.last_updated_by = fu.user_id(+)
AND acr.pay_from_customer = rc.customer_id(+)
AND acr.customer_site_use_id = hcsu.site_use_id(+)
UNION
SELECT '4' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date, acr.doc_sequence_value,
jeh.posted_date gl_date,
--ara.gl_date,
acr.receipt_number trx_number,
acr.cash_receipt_id, rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, acr.receipt_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
NULL trx_batch_name, jeh.status,
NVL (acr.comments, acr.reversal_comments) narration,
rc.customer_id, rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, acr.status check_status,
acr.reversal_date check_void_date, cc.account_type,
NULL reference1, jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
NULL ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
hr_operating_units hou,
fnd_user fu,
hz_cust_site_uses_all hcsu,
ar_customers rc,
DUAL
WHERE 1 = 1
--AND jeh.je_header_id = 1825109
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajed.source_distribution_id_num_1 = ada.line_id
AND ada.source_table = 'RA'
--AND ada.source_type NOT IN ('UNID')
AND ada.source_id = ara.receivable_application_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
--AND jeh.je_category IN ('Receipts', 'Credit Memos')
AND jeh.je_category IN
('Receipts', 'Credit Memos', 'Debit Memos')
AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ara.org_id = hou.organization_id
--AND ara.last_updated_by = fu.user_id(+)
AND jeh.last_updated_by = fu.user_id(+)
AND acr.pay_from_customer = rc.customer_id(+)
AND acr.customer_site_use_id = hcsu.site_use_id(+)
--AND cc.gl_seg2 != '417001'
UNION
SELECT DISTINCT '4' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name,
'**********'
) CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code,
gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits,
hou.NAME org_name,
INITCAP (fu.description) user_name,
jeh.doc_sequence_value, jeh.posted_date,
rct.doc_sequence_value, jeh.posted_date gl_date,
rct.trx_number trx_number, rct.customer_trx_id,
rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date,
rct.trx_date, cc.gl_seg1, cc.gl_seg2, cc.gl_seg3,
cc.gl_seg4, cc.gl_seg5, cc.gl_seg6, cc.gl_seg7,
cc.gl_seg8, cc.gl_seg9, cc.gl_seg10, cc.gl_seg11,
cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12,
NULL exp_emp_no, NULL project_no,
NULL trx_batch_name, jeh.status,
rct.comments narration, rc.customer_id,
rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, rct.status_trx check_status,
NULL check_void_date, cc.account_type,
NULL reference1, jel.creation_date,
jeh.je_header_id, jeh.accrual_rev_je_header_id,
jeh.accrual_rev_period_name, NULL, NULL, NULL,
jel.je_line_num, jel.attribute3 gl_fbt,
NULL ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_receivable_applications_all ara,
ra_customer_trx_all rct,
hr_operating_units hou,
fnd_user fu,
hz_cust_site_uses_all hcsu,
ar_customers rc,
DUAL
WHERE 1 = 1
--AND jeh.je_header_id = 1825109
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajed.source_distribution_id_num_1 = ada.line_id
AND ada.source_table = 'RA'
AND ada.source_id = ara.receivable_application_id
AND ara.customer_trx_id = rct.customer_trx_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND ( xlajel.accounted_dr != 0
OR xlajel.accounted_cr != 0
)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
AND jeh.je_category IN
('Receipts', 'Credit Memos', 'Debit Memos')
AND xlajed.source_distribution_type =
'AR_DISTRIBUTIONS_ALL'
AND ara.org_id = hou.organization_id
AND jeh.last_updated_by = fu.user_id(+)
AND rct.bill_to_customer_id = rc.customer_id(+)
AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
UNION ALL
SELECT DISTINCT '4' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id set_of_books_id, gsb.NAME sob,
NVL (cat.user_je_category_name,
'**********'
) CATEGORY,
jel.period_name period, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code currency,
gsb.currency_code sob_currency,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credit,
NULL org_name, INITCAP (fu.description) user_name,
jeh.doc_sequence_value gl_voucher_number,
jeh.posted_date, NULL sl_voucher_number,
jeh.posted_date gl_date, NULL trx_number,
NULL cash_receipt_id, NULL cust_supp_name,
xlajel.accounting_date effective_date,
NULL receipt_date, cc.gl_seg1, cc.gl_seg2,
cc.gl_seg3, cc.gl_seg4, cc.gl_seg5, cc.gl_seg6,
cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12,
NULL exp_emp_no, NULL project_no,
NULL trx_batch_name, jeh.status, NULL narration,
NULL customer_id, NULL customer_number,
NULL site_use_id, NULL site_use_code,
NULL check_status, NULL check_void_date,
cc.account_type, NULL reference1,
jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id,
jeh.accrual_rev_period_name, NULL dummy,
NULL payment_status, NULL invoice_type_code,
jel.je_line_num, jel.attribute3 gl_fbt,
NULL ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
fnd_user fu,
DUAL
WHERE 1 = 1
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND ( xlajel.accounted_dr != 0
OR xlajel.accounted_cr != 0
)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
AND jeh.je_category IN
('Receipts',
'Credit Memos',
'Misc Receipts',
'Debit Memos'
)
AND xlajed.source_distribution_type = 'XLA_MANUAL'
AND jeh.last_updated_by = fu.user_id(+))
WHERE set_of_books_id=GL_LED_ID.LEDGER_ID-------------
);
--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 4');
-----------------*********FLAG 6******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '6' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, NULL org_name,
NULL user_name, jeh.doc_sequence_value, jeh.posted_date,
NULL, rctlgd.gl_date, rct.trx_number trx_number,
rct.customer_trx_id, rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, jel.effective_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
NULL project_no, NULL trx_batch_name, jeh.status,
rel_acctg_pkg.get_ar_invoice_detail
(jel.ledger_id,
rct.customer_trx_id
) narration,
rc.customer_id, rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, NULL check_status,
NULL check_void_date, cc.account_type, NULL reference1,
jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num,jel.attribute3,null AP_FBT
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ra_cust_trx_line_gl_dist_all rctlgd,
ra_customer_trx_all rct,
hz_cust_site_uses_all hcsu,
ar_customers rc,
fnd_user fu,
DUAL
WHERE 1 = 1
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajed.source_distribution_id_num_1 =
rctlgd.cust_trx_line_gl_dist_id
AND rctlgd.customer_trx_id = rct.customer_trx_id
AND rct.bill_to_customer_id = rc.customer_id(+)
AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
AND rctlgd.last_updated_by = fu.user_id(+)
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
--AND jeh.je_category IN
-- ('Sales Invoices', 'Chargebacks', 'Credit Memos')
AND jeh.je_category IN
('Sales Invoices', 'Chargebacks', 'Credit Memos', 'Debit Memos')
AND xlajed.source_distribution_type =
'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
);
--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 6');
-----------------*********FLAG 10******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '10' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date, ai.doc_sequence_value, ai.gl_date,
ai.invoice_num trx_number, ai.invoice_id,
pov.vendor_name cust_supp_name,
xlajel.accounting_date effective_date, ai.invoice_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
NULL project_no, NULL trx_batch_name, jeh.status,
ai.description narration, pov.vendor_id,
pov.segment1 vendor_number, povs.vendor_site_id,
povs.vendor_site_code, NULL check_status,
NULL check_void_date, cc.account_type,
DECODE (ai.attribute_category,
'RL Invoice Header', ai.attribute10,
NULL
) reference1,
jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL,
ap_invoices_pkg.get_approval_status
(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code
) payment_status,
ai.invoice_type_lookup_code, jel.je_line_num,jel.attribute3 GL_FBT
--,aid.attribute6 AP_FBT
, null ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_ae_headers xlajeh,
xla_distribution_links xlajed,
ap_invoice_distributions_all aid,
ap_invoices_all ai,
po_vendors pov,
po_vendor_sites_all povs,
hr_operating_units hou,
fnd_user fu,
DUAL
WHERE 1 = 1
AND aid.invoice_id = ai.invoice_id
AND xlajed.source_distribution_id_num_1 =
aid.invoice_distribution_id
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajel.ae_header_id = xlajeh.ae_header_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Payables'
AND jeh.je_category = 'Purchase Invoices'
AND xlajed.source_distribution_type = 'AP_INV_DIST'
AND ai.org_id = hou.organization_id
AND ai.last_updated_by = fu.user_id(+)
AND ai.vendor_id = pov.vendor_id(+)
AND ai.vendor_site_id = povs.vendor_site_id(+)
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
);
--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 10');
-----------------*********FLAG 11******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '11' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date, ai.doc_sequence_value, ai.gl_date,
ai.invoice_num trx_number, ai.invoice_id,
pov.vendor_name cust_supp_name,
xlajel.accounting_date effective_date, ai.invoice_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
NULL project_no, NULL trx_batch_name, jeh.status,
ai.description narration, pov.vendor_id,
pov.segment1 vendor_number, povs.vendor_site_id,
povs.vendor_site_code, NULL check_status,
NULL check_void_date, cc.account_type,
DECODE (ai.attribute_category,
'RL Invoice Header', ai.attribute10,
NULL
) reference1,
jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL,
ap_invoices_pkg.get_approval_status
(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code
) payment_status,
ai.invoice_type_lookup_code, jel.je_line_num,jel.attribute3 GL_FBT
--,aid.attribute6 AP_FBT
, null ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_ae_headers xlajeh,
xla_distribution_links xlajed,
ap_prepay_app_dists apad,
ap_invoice_distributions_all aid,
ap_invoices_all ai,
po_vendors pov,
po_vendor_sites_all povs,
hr_operating_units hou,
fnd_user fu,
DUAL
WHERE 1 = 1
AND aid.invoice_id = ai.invoice_id
AND apad.prepay_app_distribution_id =
aid.invoice_distribution_id
AND xlajed.source_distribution_id_num_1 =
apad.prepay_app_dist_id
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajel.ae_header_id = xlajeh.ae_header_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Payables'
AND jeh.je_category = 'Purchase Invoices'
AND xlajed.source_distribution_type = 'AP_PREPAY'
AND ai.org_id = hou.organization_id
AND ai.last_updated_by = fu.user_id(+)
AND ai.vendor_id = pov.vendor_id(+)
AND ai.vendor_site_id = povs.vendor_site_id(+)
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
);
--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 11');
-----------------*********FLAG 12******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '12' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date, ac.doc_sequence_value,
appha.accounting_date gl_date,
TO_CHAR (ac.check_number) trx_number, ac.check_id,
ac.vendor_name cust_supp_name,
xlajel.accounting_date effective_date, ac.check_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
NULL project_no, NULL trx_batch_name, jeh.status,
rel_acctg_pkg.get_payment_detail (jel.ledger_id,
ac.check_id,
'CHECK_ID',
'DESCR'
) narration,
ac.vendor_id, pov.segment1 vendor_number,
povs.vendor_site_id, povs.vendor_site_code,
ac.status_lookup_code check_status,
void_date check_void_date, cc.account_type,
NULL reference1, jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_ae_headers xlajeh,
xla_distribution_links xlajed,
ap_payment_hist_dists apphd,
ap_payment_history_all appha,
ap_checks_all ac,
po_vendors pov,
po_vendor_sites_all povs,
hr_operating_units hou,
fnd_user fu
WHERE 1 = 1
AND apphd.payment_history_id = appha.payment_history_id
AND xlajed.source_distribution_type = 'AP_PMT_DIST'
AND APPHA.TRANSACTION_TYPE=
DECODE((SELECT 1
FROM ap_payment_history_all AX ,
ap_payment_history_all BS
WHERE AX.CHECK_ID=APPHA.CHECK_ID
AND AX.REV_PMT_HIST_ID=BS.PAYMENT_HISTORY_ID
AND AX.TRANSACTION_TYPE='PAYMENT CANCELLED'),'1','PAYMENT CREATED',APPHA.TRANSACTION_TYPE)
AND xlajed.source_distribution_id_num_1 =apphd.payment_hist_dist_id
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajel.ae_header_id = xlajeh.ae_header_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Payables'
AND jeh.je_category IN ('Payments', 'Reconciled Payments')
AND appha.check_id = ac.check_id
AND ac.org_id = hou.organization_id
AND ac.last_updated_by = fu.user_id(+)
AND ac.vendor_id = pov.vendor_id(+)
AND ac.vendor_site_id = povs.vendor_site_id(+)
--AND CC.GL_SEG1=421
--AND CC.GL_SEG2='515001'
--AND JEH.JE_HEADER_ID=2213108
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
);
--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 12');
-----------------*********FLAG 13******--------------
INSERT INTO XX_GL_MV_REPORT
(SELECT '13' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE, gsb.ledger_id,
gsb.NAME, NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
entered_dr entered_debit, entered_cr entered_credit,
accounted_dr accounted_debit, accounted_cr accounted_credits,
NULL org_name, INITCAP (fu.description) user_name,
jeh.doc_sequence_value, jeh.posted_date,
CASE
WHEN jeh.je_source = 'Payables'
AND jeh.je_category = 'Payments'
AND SUBSTR (jel.description, 1, 1) BETWEEN '0' AND '9'
THEN TO_NUMBER (jel.description)
ELSE NULL
END subledger_voucher_number,
NULL subledger_gl_date, NULL trx_number, NULL trx_id,
NULL cust_supp_name, jel.effective_date effective_date,
jel.effective_date, cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4,
cc.gl_seg5, cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8, cc.gl_name_seg9,
cc.gl_name_seg10, cc.gl_name_seg11, cc.gl_name_seg12,
NULL exp_emp_no, NULL project_no, NULL trx_batch_name, jeh.status,
jel.description narration, NULL vendor_id, NULL vendor_number,
NULL vendor_site_id, NULL vendor_site_code, NULL check_status,
NULL check_void_date, cc.account_type, NULL reference1,
jel.creation_date, jeh.je_header_id, jeh.accrual_rev_je_header_id,
NULL, NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
fnd_user fu
WHERE 1 = 1
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (accounted_dr != 0 OR accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND ( jeh.je_source NOT IN ('Receivables', 'Payables')
OR ( jeh.je_source IN ('Receivables', 'Payables')
AND NOT EXISTS (
SELECT 'X'
FROM gl_import_references gir
WHERE 1 = 1
AND gir.je_header_id = jeh.je_header_id)
OR ( jeh.je_source IN ('Receivables', 'Payables')
AND EXISTS (
SELECT 'X'
FROM gl_import_references gir
WHERE 1 = 1
AND gir.je_header_id = jeh.je_header_id
AND NOT EXISTS (
SELECT 'X'
FROM xla_ae_lines xlajel
WHERE 1 = 1
AND xlajel.gl_sl_link_id =
gir.gl_sl_link_id))
)
)
)
AND jeh.last_updated_by = fu.user_id(+)
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
);
----------------------UNAPP EXCEPTION CASE-----------------
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '19' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits,
hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date,
acr.doc_sequence_value,
jeh.posted_date gl_date,
acr.receipt_number trx_number,
acr.cash_receipt_id, rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, acr.receipt_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
NULL trx_batch_name, jeh.status,
NVL (acr.comments, acr.reversal_comments) narration,
rc.customer_id, rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, acr.status check_status,
acr.reversal_date check_void_date, cc.account_type,
NULL reference1, jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
NULL ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
hr_operating_units hou,
fnd_user fu,
hz_cust_site_uses_all hcsu,
ar_customers rc,
DUAL
WHERE 1 = 1
--AND xlajel.Ae_Header_Id=1283555
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajel.ae_line_num = xlajed.ae_line_num
AND xlajed.source_distribution_id_num_1 IN (50710,50711)
AND xlajed.source_distribution_id_num_1=ADA.LINE_ID
AND ada.source_table = 'RA'
--AND ada.source_type NOT IN ('UNID')
AND ara.receivable_application_id IN (3862)
AND ARA.STATUS='UNAPP'
AND ara.cash_receipt_id = acr.cash_receipt_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
--AND jeh.je_category IN ('Receipts', 'Credit Memos')
AND jeh.je_category IN
('Receipts', 'Credit Memos', 'Debit Memos')
AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ara.org_id = hou.organization_id
--AND ara.last_updated_by = fu.user_id(+)
AND jeh.last_updated_by = fu.user_id(+)
AND acr.pay_from_customer = rc.customer_id(+)
AND acr.customer_site_use_id = hcsu.site_use_id(+)
AND CC.GL_SEG1='421'-------------
AND CC.GL_SEG2='416001'
AND JEH.je_header_id='1692150'
AND GSB.ledger_id=GL_LED_ID.LEDGER_ID);
----------------------DATA FIX CASE---
INSERT INTO XX_GL_MV_REPORT
(SELECT
'16' FLAG,
GCC.CODE_COMBINATION_ID,
GJH.JE_SOURCE SOURCE,
GJH.LEDGER_ID,
(SELECT GSB.NAME FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) NAME,
GJH.JE_CATEGORY CATEGORY,
GJH.PERIOD_NAME,
GJB.NAME batch_name,
GJH.NAME HEADER_name,
GJL.DESCRIPTION DESCRIPTION,
GJH.CURRENCY_CODE,
(SELECT GSB.CURRENCY_CODE FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) sob_currency_code,
XAL.ENTERED_DR entered_debit,
XAL.ENTERED_CR entered_credit,
XAL.ACCOUNTED_DR accounted_debit,
XAL.ACCOUNTED_CR accounted_credit,
(SELECT OU.name FROM HR_OPERATING_UNITS OU WHERE OU.set_of_books_id=GJH.LEDGER_ID) org_name,
NULL user_name,
GJH.DOC_SEQUENCE_VALUE,
GJH.posted_date,
NULL doc_sequence_value,
GJL.EFFECTIVE_DATE gl_date,
NULL trx_number,
NULL INVOICE_ID,
NULL VENDOR_NAME,
XAL.ACCOUNTING_DATE effective_date,
NULL NVOICE_DATE,
cc.gl_seg1,
cc.gl_seg2,
cc.gl_seg3,
cc.gl_seg4,
cc.gl_seg5,
cc.gl_seg6,
cc.gl_seg7,
cc.gl_seg8,
cc.gl_seg9,
cc.gl_seg10,
cc.gl_seg11,
cc.gl_seg12,
cc.gl_name_seg1,
cc.gl_name_seg2,
cc.gl_name_seg3,
cc.gl_name_seg4,
cc.gl_name_seg5,
cc.gl_name_seg6,
cc.gl_name_seg7,
cc.gl_name_seg8,
cc.gl_name_seg9,
cc.gl_name_seg10,
cc.gl_name_seg11,
cc.gl_name_seg12,
NULL exp_emp_no,
NULL project_no,
NULL trx_batch_name,
GJH.STATUS,
GJL.DESCRIPTION narration,
NULL VENDOR_ID,
NULL vendor_number,
NULL VENDOR_SITE_ID,
NULL VENDOR_SITE_CODE,
NULL check_status,
NULL check_void_date,
cc.account_type,
NULL reference1,
GJL.CREATION_DATE,
GJH.JE_HEADER_ID,
GJH.accrual_rev_je_header_id,
GJH.accrual_rev_period_name,
NULL,
NULL payment_status,
NULL invoice_type_lookup_code,
GJL.je_line_num,
GJL.attribute3 GL_FBT,
null ap_fbt
FROM
(SELECT DISTINCT
XDL_IN.APPLICATION_ID,
XDL_IN.EVENT_ID,
XDL_IN.AE_HEADER_ID,
XDL_IN.AE_LINE_NUM,
XDL_IN.SOURCE_DISTRIBUTION_TYPE,
XDL_IN.ALLOC_TO_SOURCE_ID_NUM_1
FROM XLA_DISTRIBUTION_LINKS XDL_IN) AX,
XLA_AE_LINES XAL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
GL_CODE_COMBINATIONS GCC,
disc_ccid_dsc_mv cc
WHERE AX.AE_HEADER_ID IN (2123151,2123149,2123150,2123149,1917207,1917206,1917209,1917205,1917208,1917210,1917211)
AND AX.SOURCE_DISTRIBUTION_TYPE='XLA_MANUAL'
AND AX.AE_HEADER_ID=XAL.AE_HEADER_ID
AND AX.AE_LINE_NUM=XAL.AE_LINE_NUM
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.JE_LINE_NUM=AX.AE_LINE_NUM
AND GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND GJH.LEDGER_ID=GL_LED_ID.LEDGER_ID);
--------------------------EXCEPTION FOR JOURNAL IS A PART OF MANUAL ENTERY---------------
INSERT INTO XX_GL_MV_REPORT
(
SELECT
'17' FLAG,
GCC.CODE_COMBINATION_ID,
GJH.JE_SOURCE SOURCE,
GJH.LEDGER_ID,
(SELECT GSB.NAME FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) NAME,
GJH.JE_CATEGORY CATEGORY,
GJH.PERIOD_NAME,
GJB.NAME batch_name,
GJH.NAME batch_name,
GJL.DESCRIPTION DESCRIPTION,
GJH.CURRENCY_CODE,
(SELECT GSB.CURRENCY_CODE FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) sob_currency_code,
XAL.ENTERED_DR entered_debit,
XAL.ENTERED_CR entered_credit,
XAL.ACCOUNTED_DR accounted_debit,
XAL.ACCOUNTED_CR entered_credit,
(SELECT OU.name FROM HR_OPERATING_UNITS OU WHERE OU.set_of_books_id=GJH.LEDGER_ID) org_name,
INITCAP(fu.description) user_name,
GJH.DOC_SEQUENCE_VALUE,
GJH.posted_date,
NULL doc_sequence_value,
GJL.EFFECTIVE_DATE gl_date,
APA.INVOICE_NUM trx_number,
APA.INVOICE_ID,
ASUP.VENDOR_NAME,
XAL.ACCOUNTING_DATE effective_date,
APA.INVOICE_DATE,
cc.gl_seg1,
cc.gl_seg2,
cc.gl_seg3,
cc.gl_seg4,
cc.gl_seg5,
cc.gl_seg6,
cc.gl_seg7,
cc.gl_seg8,
cc.gl_seg9,
cc.gl_seg10,
cc.gl_seg11,
cc.gl_seg12,
cc.gl_name_seg1,
cc.gl_name_seg2,
cc.gl_name_seg3,
cc.gl_name_seg4,
cc.gl_name_seg5,
cc.gl_name_seg6,
cc.gl_name_seg7,
cc.gl_name_seg8,
cc.gl_name_seg9,
cc.gl_name_seg10,
cc.gl_name_seg11,
cc.gl_name_seg12,
NULL exp_emp_no,
NULL project_no,
NULL trx_batch_name,
GJH.STATUS,
APA.DESCRIPTION narration,
APA.VENDOR_ID,
ASUP.SEGMENT1 vendor_number,
ASA.VENDOR_SITE_ID,
ASA.VENDOR_SITE_CODE,
NULL check_status,
NULL check_void_date,
cc.account_type,
DECODE (APA.attribute_category,
'RL Invoice Header', APA.attribute10,
NULL
) reference1,
GJL.CREATION_DATE,
GJH.JE_HEADER_ID,
GJH.accrual_rev_je_header_id,
GJH.accrual_rev_period_name,
NULL,
ap_invoices_pkg.get_approval_status
(APA.invoice_id,
APA.invoice_amount,
APA.payment_status_flag,
APA.invoice_type_lookup_code
) payment_status,
APA.invoice_type_lookup_code,
GJL.je_line_num,
GJL.attribute3 GL_FBT,
null ap_fbt
FROM
(SELECT DISTINCT XDL_IN.APPLICATION_ID,----------------
XDL_IN.EVENT_ID,
XDL_IN.AE_HEADER_ID,
XDL_IN.AE_LINE_NUM,
XDL_IN.SOURCE_DISTRIBUTION_TYPE,
XDL_IN.ALLOC_TO_SOURCE_ID_NUM_1
FROM XLA_DISTRIBUTION_LINKS XDL_IN )AX, -----------------
XLA_AE_LINES XAL,
AP_INVOICES_ALL APA ,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
fnd_user fu,
AP_SUPPLIERS ASUP,
disc_ccid_dsc_mv cc,
AP_SUPPLIER_SITES_ALL ASA,
DUAL
WHERE AX.AE_HEADER_ID IN (1478392,1478991,1724120,1726874,1726873,1728823,1728878)
AND AX.AE_HEADER_ID=XAL.AE_HEADER_ID
AND AX.AE_LINE_NUM=XAL.AE_LINE_NUM
AND (XAL.accounted_dr != 0 OR XAL.accounted_cr != 0)
AND AX.ALLOC_TO_SOURCE_ID_NUM_1=APA.INVOICE_ID
AND XAL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.JE_LINE_NUM=AX.AE_LINE_NUM
AND GJH.JE_BATCH_ID=GJB.JE_BATCH_ID
AND APA.LAST_UPDATED_BY=fu.user_id(+)
AND ASUP.VENDOR_ID=APA.VENDOR_ID
AND CC.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND ASA.VENDOR_SITE_ID=APA.VENDOR_SITE_ID
AND GJH.LEDGER_ID=GL_LED_ID.LEDGER_ID
);
--------------------FOR ACCOUNT 754001
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '15' flag, jel.code_combination_id,
NVL (src.user_je_source_name, '**********') SOURCE,
gsb.ledger_id, gsb.NAME,
NVL (cat.user_je_category_name, '**********') CATEGORY,
jel.period_name period_name, jeb.NAME batch_name,
jeh.NAME header_name, jel.description description,
jeh.currency_code, gsb.currency_code sob_currency_code,
xlajel.entered_dr entered_debit,
xlajel.entered_cr entered_credit,
xlajel.accounted_dr accounted_debit,
xlajel.accounted_cr accounted_credits, hou.NAME org_name,
INITCAP (fu.description) user_name, jeh.doc_sequence_value,
jeh.posted_date, acr.doc_sequence_value,
jeh.posted_date gl_date,
--ara.gl_date,
acr.receipt_number trx_number,
acr.cash_receipt_id, rc.customer_name cust_supp_name,
xlajel.accounting_date effective_date, acr.receipt_date,
cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
NULL trx_batch_name, jeh.status,
NVL (acr.comments, acr.reversal_comments) narration,
rc.customer_id, rc.customer_number, hcsu.site_use_id,
hcsu.site_use_code, acr.status check_status,
acr.reversal_date check_void_date, cc.account_type,
NULL reference1, jel.creation_date, jeh.je_header_id,
jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
NULL ap_fbt
FROM disc_ccid_dsc_mv cc,
gl_ledger_names_v gsb,
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references gir,
gl_je_batches jeb,
gl_je_categories cat,
gl_je_sources src,
xla_ae_lines xlajel,
xla_distribution_links xlajed,
ar_distributions_all ada,
ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
hr_operating_units hou,
fnd_user fu,
hz_cust_site_uses_all hcsu,
ar_customers rc,
DUAL
WHERE 1 = 1
AND xlajel.ae_header_id = xlajed.ae_header_id
AND xlajed.source_distribution_id_num_1 = ada.line_id
AND ada.source_table = 'RA'
AND ada.source_id = ara.receivable_application_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND gir.je_header_id = jeh.je_header_id
AND gir.je_line_num = jel.je_line_num
AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
AND jel.code_combination_id = cc.code_combination_id
AND jel.status || '' = 'P'
AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeh.ledger_id = gsb.ledger_id
AND jeb.average_journal_flag = 'N'
AND src.je_source_name = jeh.je_source
AND cat.je_category_name = jeh.je_category
AND jeh.je_source = 'Receivables'
AND jeh.je_category IN
('Receipts', 'Credit Memos', 'Debit Memos')
AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ara.org_id = hou.organization_id
AND jeh.last_updated_by = fu.user_id(+)
AND acr.pay_from_customer = rc.customer_id(+)
AND acr.customer_site_use_id = hcsu.site_use_id(+)
AND CC.GL_SEG2='754001'
AND CC.GL_SEG1=(SELECT DECODE(GL_LED_ID.LEDGER_ID,'2027','421','431') FROM DUAL)
);
COMMIT;
fnd_file.put_line (fnd_file.LOG,'LOAD SUCCSSFULLY FOR-'||GL_LED_ID.LEDGER_ID);
END LOOP;
fnd_file.put_line (fnd_file.LOG,'DATA LOAD SUCCESFULLY');
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'ERROR WHILE LOADING DATA');
END;
/
No comments:
Post a Comment