[Home] [Help]
871: CLOSE inv_conv_rate_zero;
872: -- Bug 8895061 Ends
873:
874:
875: INSERT INTO ar_xla_lines_extract (
876: EVENT_ID
877: ,LINE_NUMBER
878: ,LANGUAGE
879: ,LEDGER_ID
1140: 'EXCH_LOSS','EXCH_GAIN','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX');
1141:
1142:
1143: --BUG#5366837 From document in cash basis
1144: INSERT INTO ar_xla_lines_extract (
1145: EVENT_ID
1146: ,LINE_NUMBER
1147: ,LANGUAGE
1148: ,LEDGER_ID
1347: -- Insert line level data in Line GT with
1348: -- selected_flag = Y
1349: -- level_flag = L
1350: -- From_to_flag = F
1351: INSERT INTO ar_xla_lines_extract (
1352: EVENT_ID
1353: ,LINE_NUMBER
1354: ,LANGUAGE
1355: ,LEDGER_ID
1580:
1581:
1582:
1583: --BUG#5366837
1584: INSERT INTO ar_xla_lines_extract (
1585: EVENT_ID
1586: ,LINE_NUMBER
1587: ,LANGUAGE
1588: ,LEDGER_ID
1793: BEGIN
1794: local_log(procedure_name => 'load_header_data_ctlgd',
1795: p_msg_text => 'arp_xla_extract_main_pkg.load_header_data_ctlgd ()+');
1796: -----------------------
1797: -- Insert header into ar_xla_lines_extract with
1798: -- selected_flg = 'Y'
1799: -- level_flg = 'H'
1800: -- We also need to insert the header data into ar_xla_headers_extract
1801: -- because the view AR_LEDGER_EXT_H is a pure header level view but it is
1801: -- because the view AR_LEDGER_EXT_H is a pure header level view but it is
1802: -- used by the transaction event_types posting with the entity CTLGD
1803: -----------------------
1804: -- Insert into Lines GT
1805: INSERT INTO AR_XLA_LINES_EXTRACT(
1806: EVENT_ID
1807: ,SOURCE_ID
1808: ,SOURCE_TABLE
1809: ,LINE_ID
2009: local_log(procedure_name => 'load_header_data_adj',
2010: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.load_header_data_adj()+');
2011: -- Insert into Lines GT for adjustments because header level
2012: -- shared sources can be used.
2013: INSERT INTO AR_XLA_LINES_EXTRACT(
2014: EVENT_ID
2015: ,SOURCE_ID
2016: ,SOURCE_TABLE
2017: ,LINE_ID
2291: local_log(procedure_name => 'load_header_data_crh',
2292: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.load_header_data_crh()+');
2293: -- Insert header data to line level
2294: -- Pure Header sources and Shared header sources
2295: INSERT INTO AR_XLA_LINES_EXTRACT(
2296: EVENT_ID
2297: ,SOURCE_ID
2298: ,SOURCE_TABLE
2299: ,LINE_ID
2484: IS
2485: BEGIN
2486: local_log(procedure_name => 'Load_header_data_th',
2487: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.Load_header_data_th()+');
2488: INSERT INTO AR_XLA_LINES_EXTRACT(
2489: EVENT_ID
2490: ,SOURCE_ID
2491: ,SOURCE_TABLE
2492: ,LINE_ID
2673: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.load_line_data_ctlgd()+');
2674: -- Insert line level data in Line GT with
2675: -- level_flag = L
2676:
2677: INSERT INTO ar_xla_lines_extract (
2678: EVENT_ID
2679: ,LINE_NUMBER
2680: ,LANGUAGE
2681: ,LEDGER_ID
2899:
2900: -- Insert line level data in Line GT with
2901: -- selected_flag = N
2902: -- level_flag = L
2903: INSERT INTO ar_xla_lines_extract (
2904: EVENT_ID
2905: ,LINE_NUMBER
2906: ,LANGUAGE
2907: ,LEDGER_ID
3122: -- Insert line level data in Line GT with
3123: -- selected_flag = Y
3124: -- level_flag = L
3125: -- from_to_flag = T
3126: INSERT INTO ar_xla_lines_extract (
3127: EVENT_ID
3128: ,LINE_NUMBER
3129: ,LANGUAGE
3130: ,LEDGER_ID
3641:
3642:
3643:
3644: --BUG#5366837 Cash Basis
3645: INSERT INTO ar_xla_lines_extract (
3646: EVENT_ID
3647: ,LINE_NUMBER
3648: ,LANGUAGE
3649: ,LEDGER_ID
3959: -- Insert line level data in Line GT with
3960: -- selected_flag = Y
3961: -- level_flag = L
3962: -- from_to_flag = T
3963: INSERT INTO ar_xla_lines_extract (
3964: EVENT_ID
3965: ,LINE_NUMBER
3966: ,LANGUAGE
3967: ,LEDGER_ID
4166: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.load_line_date_crh ()+');
4167: -- Line data CR in Line GT
4168: -- Selected_flag = N
4169: -- Level_flag = L
4170: INSERT INTO ar_xla_lines_extract (
4171: EVENT_ID
4172: ,LINE_NUMBER
4173: ,LANGUAGE
4174: ,LEDGER_ID
4360: -- 'REVERSED','Y');
4361:
4362: --{Insertion of UNAPP and UNID
4363: --Note all the UNAPP and UNID are inserted here
4364: INSERT INTO ar_xla_lines_extract (
4365: EVENT_ID
4366: ,LINE_NUMBER
4367: ,LANGUAGE
4368: ,LEDGER_ID
4660: IS
4661:
4662: -- Cursor to fetch the foreign currency on-account CM applications with CURR_ROUND
4663: CURSOR cmapp_curr_round_cur IS
4664: SELECT ext1.event_id, ext1.source_id, ext1.amount, ext1.acctd_amount FROM ar_xla_lines_extract ext1
4665: WHERE ext1.source_type='CURR_ROUND'
4666: AND ext1.source_table = 'RA'
4667: AND ext1.mfar_additional_entry ='N'
4668: AND ext1.event_class_code = 'CREDIT_MEMO'
4666: AND ext1.source_table = 'RA'
4667: AND ext1.mfar_additional_entry ='N'
4668: AND ext1.event_class_code = 'CREDIT_MEMO'
4669: AND NOT EXISTS
4670: (SELECT 1 FROM ar_xla_lines_extract ext2
4671: WHERE ext2.event_id = ext1.event_id
4672: AND ext2.source_id = ext1.source_id
4673: AND ext2.source_table = 'RA'
4674: AND ext2.source_type = 'REC'
4679:
4680: -- Cursor to fetch the foreign currency on-account CM applications with EXCHG_GAIN_LOSS
4681: CURSOR cmapp_exch_rate_cur IS
4682: SELECT DISTINCT ext1.event_id, ext1.source_id,0,0
4683: FROM ar_xla_lines_extract ext1
4684: WHERE ext1.exchange_rate IS NOT NULL
4685: AND ext1.source_table = 'RA'
4686: AND ext1.mfar_additional_entry ='N'
4687: AND ext1.event_class_code = 'CREDIT_MEMO'
4685: AND ext1.source_table = 'RA'
4686: AND ext1.mfar_additional_entry ='N'
4687: AND ext1.event_class_code = 'CREDIT_MEMO'
4688: AND NOT EXISTS
4689: (SELECT 1 FROM ar_xla_lines_extract ext2
4690: WHERE ext2.event_id = ext1.event_id
4691: AND ext2.source_id = ext1.source_id
4692: AND ext2.source_table = 'RA'
4693: AND ext2.source_type = 'REC'
4694: AND ext2.mfar_additional_entry ='N'
4695: AND ext2.event_class_code = 'CREDIT_MEMO'
4696: AND ext2.cm_app_to_trx_line_id IS NOT NULL
4697: UNION ALL
4698: SELECT 1 FROM ar_xla_lines_extract ext3
4699: WHERE ext3.event_id = ext1.event_id
4700: AND ext3.source_id = ext1.source_id
4701: AND ext3.source_type = 'CURR_ROUND'
4702: AND ext3.source_table = 'RA'
4787: PROCEDURE mfar_cmapp_trx_update(p_cmapp_curr_round IN cmapp_curr_round, p_call IN VARCHAR2) IS
4788:
4789: CURSOR cmapp_rec_cur (p_source_id NUMBER, p_from_to_flag VARCHAR2) IS
4790: SELECT bal_seg_value, sum(amount), sum(acctd_amount), min(customer_trx_line_id)
4791: FROM ar_xla_lines_extract
4792: WHERE source_id = p_source_id
4793: AND source_table = 'RA'
4794: AND posting_entity = 'APP'
4795: AND event_class_code = 'CREDIT_MEMO'
4870:
4871: -- Update the column cm_app_to_trx_line_id with appropriate value so that the
4872: -- CM accounting happens as for a regular CM from now on.
4873: FOR j in l_cmapp_rec_record_tab_t.FIRST..l_cmapp_rec_record_tab_t.LAST LOOP
4874: UPDATE ar_xla_lines_extract
4875: SET cm_app_to_trx_line_id = l_cmapp_rec_record_tab_t(j).cust_trx_line_id
4876: WHERE source_id = p_cmapp_curr_round.source_id
4877: AND bal_seg_value = l_cmapp_rec_record_tab_t(j).bal_seg_value
4878: AND source_type = 'REC'
4915: local_log(procedure_name => 'mfar_reg_cmapp_curr_round',
4916: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_reg_cmapp_curr_round ()+');
4917:
4918: -- Insert MFAR CURR_ROUND rows for regular Credit Memos
4919: INSERT INTO ar_xla_lines_extract (
4920: EVENT_ID
4921: ,LINE_NUMBER
4922: ,LANGUAGE
4923: ,LEDGER_ID
5132: ,l.THIRD_PARTY_ID
5133: ,l.THIRD_PARTY_SITE_ID
5134: ,l.THIRD_PARTY_TYPE
5135: ,'CURR_ROUND'
5136: FROM AR_XLA_LINES_EXTRACT l,
5137: (SELECT
5138: event_id,
5139: source_id,
5140: CM_APP_TO_TRX_LINE_ID,
5141: sum(-1*ACCTD_AMOUNT) ACCTD_AMT,
5142: sum(-1*FROM_ACCTD_AMOUNT) FROM_ACCTD_AMT,
5143: sum(AMOUNT) AMOUNT,
5144: sum(FROM_AMOUNT) FROM_AMOUNT
5145: FROM ar_xla_lines_extract
5146: WHERE POSTING_ENTITY = 'APP'
5147: AND CUSTOMER_TRX_LINE_ID is not null
5148: AND source_type = 'REC'
5149: AND MFAR_ADDITIONAL_ENTRY = 'N'
5162: AND ard.source_table = 'RA'
5163: AND ard.source_type = 'CURR_ROUND';
5164:
5165: -- Insert MFAR CURR_ROUND rows for on-account Credit Memos
5166: INSERT INTO ar_xla_lines_extract (
5167: EVENT_ID
5168: ,LINE_NUMBER
5169: ,LANGUAGE
5170: ,LEDGER_ID
5379: ,l.THIRD_PARTY_ID
5380: ,l.THIRD_PARTY_SITE_ID
5381: ,l.THIRD_PARTY_TYPE
5382: ,ard.source_type
5383: FROM AR_XLA_LINES_EXTRACT l,
5384: AR_DISTRIBUTIONS_ALL ard,
5385: (select event_id,
5386: source_id,
5387: LINE_ID,
5385: (select event_id,
5386: source_id,
5387: LINE_ID,
5388: sum(-1*ACCTD_AMOUNT) CURR_ROUND_ACCTD_AMT
5389: from ar_xla_lines_extract
5390: where POSTING_ENTITY = 'APP'
5391: AND CUSTOMER_TRX_LINE_ID is not null
5392: AND ((MFAR_ADDITIONAL_ENTRY = 'Y')
5393: OR (MFAR_ADDITIONAL_ENTRY = 'N' AND FROM_TO_FLAG = 'T'))
5475: source_id_int_1,
5476: event_id,
5477: ledger_id) xla
5478: ,gl_sets_of_books sob
5479: ,ar_xla_lines_extract ext -- 10047351
5480: WHERE xla.source_id_int_1 = ra.customer_trx_id
5481: AND xla.event_id = ra.event_id
5482: AND ra.status = 'APP'
5483: AND ra.receivable_application_id = ard.source_id
5545: AND event_type_code IN ('CM_CREATE','CM_UPDATE')
5546: GROUP BY entity_id,
5547: source_id_int_1,
5548: event_id) xla
5549: ,ar_xla_lines_extract ext -- 10047351
5550: WHERE xla.source_id_int_1 = ra.customer_trx_id
5551: AND xla.event_id = ra.event_id
5552: AND ra.status = 'APP'
5553: AND ra.receivable_application_id = ard.source_id
5642: x_sign_value number := 0;
5643:
5644: l_ar_mfar_extract_s number;
5645:
5646: -- pl/sql table for ar_xla_lines_extract
5647: TYPE ar_xla_mfar_extract_gt_tab IS TABLE OF ar_xla_lines_extract%ROWTYPE
5648: INDEX BY BINARY_INTEGER;
5649: l_cm_app_mfar_extract_tab ar_xla_mfar_extract_gt_tab;
5650:
5643:
5644: l_ar_mfar_extract_s number;
5645:
5646: -- pl/sql table for ar_xla_lines_extract
5647: TYPE ar_xla_mfar_extract_gt_tab IS TABLE OF ar_xla_lines_extract%ROWTYPE
5648: INDEX BY BINARY_INTEGER;
5649: l_cm_app_mfar_extract_tab ar_xla_mfar_extract_gt_tab;
5650:
5651: BEGIN
5779:
5780: END LOOP;
5781:
5782: FORALL r IN l_cm_app_mfar_extract_tab.first..l_cm_app_mfar_extract_tab.last
5783: INSERT INTO ar_xla_lines_extract VALUES l_cm_app_mfar_extract_tab(r);
5784:
5785: local_log(procedure_name => 'mfar_cm_app_insert_extract',
5786: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_cm_app_insert_extract()-');
5787:
6484: gl_ledgers lg,
6485: ar_cash_receipt_history_all crhlatest,
6486: (SELECT source_id,
6487: MAX(line_id) max_line_id
6488: FROM ar_xla_lines_extract
6489: WHERE source_table='RA'
6490: AND posting_entity='APP'
6491: AND event_class_code='RECEIPT'
6492: AND customer_trx_line_id IS NOT NULL
6600: ar_cash_receipts_all cr,
6601: gl_ledgers lg,
6602: (SELECT source_id,
6603: MAX(line_id) max_line_id
6604: FROM ar_xla_lines_extract
6605: WHERE source_table='RA'
6606: AND posting_entity='APP'
6607: AND event_class_code='RECEIPT'
6608: AND customer_trx_line_id IS NOT NULL
6714: gl_ledgers lg,
6715: ar_cash_receipt_history_all crhlatest,
6716: (SELECT source_id,
6717: MAX(line_id) max_line_id
6718: FROM ar_xla_lines_extract
6719: WHERE source_table='RA'
6720: AND posting_entity='APP'
6721: AND event_class_code='RECEIPT'
6722: AND customer_trx_line_id IS NOT NULL
6834: gl_ledgers lg,
6835: ar_crh_gt crhgt,
6836: (SELECT source_id,
6837: MAX(line_id) max_line_id
6838: FROM ar_xla_lines_extract
6839: WHERE source_table='RA'
6840: AND posting_entity='APP'
6841: AND event_class_code='RECEIPT'
6842: AND customer_trx_line_id IS NOT NULL
6925: BEGIN
6926: local_log(procedure_name => 'mfar_rctapp_curr_round',
6927: p_msg_text => 'arp_xla_extract_main_pkg.mfar_rctapp_curr_round ()+');
6928:
6929: INSERT INTO ar_xla_lines_extract (
6930: EVENT_ID
6931: ,LINE_NUMBER
6932: ,LANGUAGE
6933: ,LEDGER_ID
7292: BEGIN
7293: local_log(procedure_name => 'mfar_produit_mcd_by_crh',
7294: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_produit_mcd_by_crh ()+');
7295:
7296: INSERT INTO ar_xla_lines_extract
7297: (EVENT_ID
7298: ,LINE_NUMBER
7299: ,MFAR_ADDITIONAL_ENTRY
7300: ,LEDGER_ID
7452: BEGIN
7453: local_log(procedure_name => 'load_line_data_th',
7454: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.load_line_data_th()+');
7455:
7456: INSERT INTO ar_xla_lines_extract (
7457: EVENT_ID
7458: ,LINE_NUMBER
7459: ,LANGUAGE
7460: ,LEDGER_ID
7671: -- Insert line level data in Line GT with
7672: -- selected_flag = N
7673: -- level_flag = L
7674: -- From_to_flag = NULL
7675: INSERT INTO ar_xla_lines_extract (
7676: EVENT_ID
7677: ,LINE_NUMBER
7678: ,LANGUAGE
7679: ,LEDGER_ID
7884: ,p_accounting_mode IN VARCHAR2)
7885: IS
7886: CURSOR c_sob IS
7887: SELECT set_of_books_id
7888: FROM ar_xla_lines_extract gt
7889: WHERE posting_entity in ('CR','CTLGD')
7890: AND select_flag = 'Y'
7891: AND level_flag = 'H'
7892: AND set_of_books_id IS NOT NULL
8614: x_CRH_RECORD_ID number := 0;
8615: x_LINE_ID number := 0;
8616: i number := 0;
8617:
8618: -- pl/sql table for ar_xla_lines_extract
8619: TYPE ar_xla_mfar_extract_gt_tab IS TABLE OF ar_xla_lines_extract%ROWTYPE
8620: INDEX BY BINARY_INTEGER;
8621: l_mfar_extract_tab ar_xla_mfar_extract_gt_tab;
8622:
8615: x_LINE_ID number := 0;
8616: i number := 0;
8617:
8618: -- pl/sql table for ar_xla_lines_extract
8619: TYPE ar_xla_mfar_extract_gt_tab IS TABLE OF ar_xla_lines_extract%ROWTYPE
8620: INDEX BY BINARY_INTEGER;
8621: l_mfar_extract_tab ar_xla_mfar_extract_gt_tab;
8622:
8623:
8892:
8893: END LOOP;
8894:
8895: FORALL r IN l_mfar_extract_tab.first..l_mfar_extract_tab.last
8896: INSERT INTO ar_xla_lines_extract VALUES l_mfar_extract_tab(r);
8897:
8898: local_log(procedure_name => 'mfar_insert_crh_extract',
8899: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.mfar_insert_crh_extract ()-');
8900:
8923: x_APP_RECORD_ID number := 0;
8924:
8925: i number := 0;
8926:
8927: -- pl/sql table for ar_xla_lines_extract
8928: TYPE ar_xla_extract_gt_tab IS TABLE OF ar_xla_lines_extract%ROWTYPE
8929: INDEX BY BINARY_INTEGER;
8930: l_extract_tab ar_xla_extract_gt_tab;
8931:
8924:
8925: i number := 0;
8926:
8927: -- pl/sql table for ar_xla_lines_extract
8928: TYPE ar_xla_extract_gt_tab IS TABLE OF ar_xla_lines_extract%ROWTYPE
8929: INDEX BY BINARY_INTEGER;
8930: l_extract_tab ar_xla_extract_gt_tab;
8931:
8932:
9038:
9039: END LOOP;
9040:
9041: FORALL r IN l_extract_tab.first..l_extract_tab.last
9042: INSERT INTO ar_xla_lines_extract VALUES l_extract_tab(r);
9043:
9044: local_log(procedure_name => 'prorate_extract_acctd_amounts',
9045: p_msg_text => 'ARP_XLA_EXTRACT_MAIN_PKG.prorate_extract_acctd_amounts ()-');
9046:
9214: PROCEDURE diag_data
9215: IS
9216: CURSOR c IS
9217: SELECT *
9218: FROM ar_xla_lines_extract;
9219: l_c c%ROWTYPE;
9220:
9221:
9222: CURSOR c2 IS
9232: --
9233: /*
9234: DELETE FROM ar_xla_event_tmp;
9235: --
9236: DELETE FROM ar_xla_lines_extract_tmp;
9237: --
9238: INSERT INTO ar_xla_event_tmp
9239: (LINE_NUMBER ,
9240: ENTITY_ID ,
9329: BUDGETARY_CONTROL_FLAG
9330: FROM xla_events_gt
9331: WHERE application_id = 222;
9332: --
9333: INSERT INTO ar_xla_lines_extract_tmp
9334: ( EVENT_ID ,
9335: LINE_NUMBER ,
9336: LANGUAGE ,
9337: LEDGER_ID ,
9547: ,third_party_id
9548: ,third_party_site_id
9549: ,third_party_type
9550: ,source_type
9551: FROM ar_xla_lines_extract;
9552: */
9553: local_log('diag_data','+--------BEGIN READING XLA_EVENTS_GT--------------+');
9554: OPEN c2;
9555: LOOP
9606: CLOSE c2;
9607: local_log('diag_data','+--------END READING XLA_EVENTS_GT----------------+');
9608:
9609:
9610: local_log('diag_data','+--------BEGIN READING AR_XLA_LINES_EXTRACT-------+');
9611: cpt := 0;
9612: OPEN c;
9613: LOOP
9614: FETCH c INTO l_c;
9711: local_log('diag_data','
9712: local_log('diag_data','
9713: END LOOP;
9714: CLOSE c;
9715: local_log('diag_data','+--------END READING AR_XLA_LINES_EXTRACT---------+');
9716:
9717:
9718: END;
9719: