[Home] [Help]
24: | 19-APR-2003 K.Boussema Included Error messages |
25: | 06-MAI-2003 K.Boussema Modified to fix bug 2936066(Unbalanced JE) |
26: | 13-MAI-2003 K.Boussema Renamed temporary tables xla_je_lines_gt by |
27: | xla_ae_lines_gt, xla_je_headers_gt by |
28: | xla_ae_headers_gt |
29: | Renamed in xla_distribution_links the column |
30: | base_amount by ledger_amount |
31: | 20-MAI-2003 K.Boussema Added a Token to XLA_AP_CANNOT_INSERT_JE |
32: | message |
4343: ,l_array_mpa_acc_ae_header_id
4344: ,l_array_mpa_acc_header_num
4345: ,l_array_mpa_acc_balance_type
4346: FROM xla_ae_lines_gt lgt
4347: ,xla_ae_headers_gt hgt
4348: WHERE l_array_ledger_id(i) = lgt.ledger_id
4349: AND l_array_rev_dist_type(i) = lgt.source_distribution_type
4350: AND nvl(l_array_rev_dist_id_num_1(i),-99) = nvl(lgt.source_distribution_id_num_1,-99)
4351: AND nvl(l_array_rev_dist_id_num_2(i),-99) = nvl(lgt.source_distribution_id_num_2,-99)
4411: AND ae_header_id = l_array_mpa_acc_ae_header_id(j)
4412: AND header_num = l_array_mpa_acc_header_num(j);
4413:
4414: ---------------------------------------------------------------------------------------------------------
4415: -- 4669308 Delete the MPA/Accrual Line Reversal in xla_ae_headers_gt (not in distribution links yet)
4416: ---------------------------------------------------------------------------------------------------------
4417: FORALL j IN 1..l_array_mpa_acc_event_id.count
4418: DELETE xla_ae_headers_gt
4419: WHERE ledger_id = l_array_mpa_acc_ledger_id(j)
4414: ---------------------------------------------------------------------------------------------------------
4415: -- 4669308 Delete the MPA/Accrual Line Reversal in xla_ae_headers_gt (not in distribution links yet)
4416: ---------------------------------------------------------------------------------------------------------
4417: FORALL j IN 1..l_array_mpa_acc_event_id.count
4418: DELETE xla_ae_headers_gt
4419: WHERE ledger_id = l_array_mpa_acc_ledger_id(j)
4420: AND event_id = l_array_mpa_acc_event_id(j)
4421: AND ae_header_id = l_array_mpa_acc_ae_header_id(j)
4422: AND balance_type_code = l_array_mpa_acc_balance_type(j)
5420: , lgt2.anc_id_100
5421: FROM
5422: xla_ae_lines_gt lgt1
5423: ,xla_ae_lines_gt lgt2
5424: ,xla_ae_headers_gt hgt
5425: WHERE lgt1.reversal_code = 'DUMMY_LR'
5426: AND lgt2.ledger_id = lgt1.ledger_id
5427: AND lgt2.source_distribution_type = lgt1.reverse_distribution_type
5428: AND nvl(lgt2.source_distribution_id_num_1,-99) = nvl(lgt1.reverse_dist_id_num_1,-99)
6211: )
6212: AND NVL(lgt.header_num,0) > 0
6213: AND lgt.header_num IN
6214: (SELECT hgt.header_num
6215: FROM xla_ae_headers_gt hgt
6216: WHERE hgt.entity_id = l_array_entity_id(i)
6217: AND hgt.ledger_id = l_array_ledger_id(i)
6218: AND hgt.event_number < l_array_event_number(i)
6219: AND lgt.event_id = hgt.event_id
6222: AND lgt.header_num = hgt.header_num)
6223: ;
6224: --
6225: ---------------------------------------------------------------------------------------------------------
6226: -- 4669308 Delete the MPA/Accrual Transaction Reversal in xla_ae_headers_gt (not in distribution links yet)
6227: ---------------------------------------------------------------------------------------------------------
6228: --
6229: FORALL i IN 1..l_array_entity_id.count
6230: DELETE xla_ae_headers_gt hgt
6226: -- 4669308 Delete the MPA/Accrual Transaction Reversal in xla_ae_headers_gt (not in distribution links yet)
6227: ---------------------------------------------------------------------------------------------------------
6228: --
6229: FORALL i IN 1..l_array_entity_id.count
6230: DELETE xla_ae_headers_gt hgt
6231: WHERE hgt.ledger_id = l_array_ledger_id(i)
6232: AND hgt.entity_id = l_array_entity_id(i)
6233: AND hgt.event_number < l_array_event_number(i)
6234: AND NVL(hgt.header_num,0) > 0
6765: , lgt.alloc_to_dist_id_num_3 alloc_to_dist_id_num_3
6766: , lgt.alloc_to_dist_id_num_4 alloc_to_dist_id_num_4
6767: , lgt.alloc_to_dist_id_num_5 alloc_to_dist_id_num_5
6768: FROM xla_ae_lines_gt lgt
6769: ,xla_ae_headers_gt hgt
6770: WHERE hgt.entity_id = l_array_entity_id(i)
6771: AND hgt.ledger_id = l_array_ledger_id(i)
6772: AND hgt.event_number < l_array_event_number(i)
6773: AND lgt.event_id = hgt.event_id
7365: -- bug7135700 code change end
7366:
7367:
7368: --
7369: -- select all the reversal lines that do not have a header in xla_ae_headers_gt
7370: --
7371: SELECT event_id
7372: ,ledger_id
7373: ,balance_type_code
7382: FROM xla_ae_lines_gt lgt
7383: WHERE reversal_code = 'REVERSAL'
7384: AND NOT EXISTS
7385: (SELECT 1
7386: FROM xla_ae_headers_gt
7387: WHERE event_id = lgt.event_id
7388: AND ledger_id = lgt.ledger_id
7389: -- AND nvl(header_num, -1) = nvl(lgt.header_num, -1) -- 4262811c missing mpa reversal lines
7390: -- 4669308 NVL(-1) give separate headers for MPA
7403: ,p_module => l_log_module);
7404: END IF;
7405:
7406: --
7407: -- inserting new rows in xla_ae_headers_gt for the reversal lines that did not have a header
7408: --
7409: FORALL I IN 1..l_array_event_id.count
7410: INSERT INTO xla_ae_headers_gt
7411: ( ae_header_id
7406: --
7407: -- inserting new rows in xla_ae_headers_gt for the reversal lines that did not have a header
7408: --
7409: FORALL I IN 1..l_array_event_id.count
7410: INSERT INTO xla_ae_headers_gt
7411: ( ae_header_id
7412: , accounting_entry_status_code
7413: , accounting_entry_type_code
7414: , ledger_id
7458: , gl_transfer_status_code
7459: , decode(l_array_entry_status_code(i),XLA_AE_JOURNAL_ENTRY_PKG.C_VALID,'X','I')
7460: , l_array_header_num(i) -- 4262811c MPA header for line reversal
7461: , accrual_reversal_flag -- 4262811a
7462: FROM xla_ae_headers_gt
7463: WHERE event_id = l_array_event_id(i)
7464: AND ledger_id = l_array_ledger_id(i)
7465: AND balance_type_code = 'X'
7466: AND NVL(header_num,0) = 0); -- 5183946 xla_ae_headers_gt_u1 error
7462: FROM xla_ae_headers_gt
7463: WHERE event_id = l_array_event_id(i)
7464: AND ledger_id = l_array_ledger_id(i)
7465: AND balance_type_code = 'X'
7466: AND NVL(header_num,0) = 0); -- 5183946 xla_ae_headers_gt_u1 error
7467:
7468:
7469: IF (C_LEVEL_EVENT >= g_log_level) THEN
7470: trace
7467:
7468:
7469: IF (C_LEVEL_EVENT >= g_log_level) THEN
7470: trace
7471: (p_msg => 'Headers inserted in xla_ae_headers_gt = '||TO_CHAR(SQL%ROWCOUNT)
7472: ,p_level => C_LEVEL_EVENT
7473: ,p_module => l_log_module);
7474: END IF;
7475: --
10512: END
10513: END
10514: END
10515: FROM gl_daily_rates ra
10516: ,xla_ae_headers_gt xah
10517: ,FND_CURRENCIES fc
10518: ,FND_CURRENCIES fc1
10519: WHERE ra.conversion_date(+)=
10520: nvl(trunc(xal.currency_conversion_date), trunc(xah.accounting_date))
10667: END
10668: END
10669: END
10670: FROM gl_daily_rates ra
10671: ,xla_ae_headers_gt xah
10672: WHERE ra.conversion_date(+)=
10673: nvl(trunc(xal.currency_conversion_date), trunc(xah.accounting_date))
10674: AND ra.conversion_type (+)=
10675: CASE l_ledger_attrs.array_ledger_type(i)
11941: -------------------------------------------------------------------------------------------------------------------------
11942: -- 4655713b Update MPA and Accrual Reversal lines with Prior Entry
11943: -------------------------------------------------------------------------------------------------------------------------
11944: IF xla_accounting_pkg.g_mpa_accrual_exists = 'Y' THEN -- 5666366
11945: SELECT /*+ Leading (xal1,xah1,xal2) index(xah1 XLA_AE_HEADERS_GT_U1) index(xal2 XLA_AE_LINES_GT_N2) no_expand */ -- 5666366
11946: xal2.segment1, xal2.segment2, xal2.segment3, xal2.segment4, xal2.segment5
11947: ,xal2.segment6, xal2.segment7, xal2.segment8, xal2.segment9, xal2.segment10
11948: ,xal2.segment11, xal2.segment12, xal2.segment13, xal2.segment14, xal2.segment15
11949: ,xal2.segment16, xal2.segment17, xal2.segment18, xal2.segment19, xal2.segment20
12013: ,l_array_mpa_ae_header_id
12014: ,l_array_mpa_header_num
12015: ,l_array_acc_rev_flag
12016: FROM xla_ae_lines_gt xal2 -- original downstream line
12017: ,xla_ae_headers_gt xah1 -- original downstream header
12018: ,xla_ae_lines_gt xal1 -- recognition lines
12019: ,xla_ae_lines xal3 -- upstream line
12020: WHERE xal2.source_distribution_type = xal1.source_distribution_type
12021: AND NVL(xal2.source_distribution_id_num_1,C_NUM) = NVL(xal1.source_distribution_id_num_1,C_NUM)
12363: AND ledger_id = l_array_ledger_id(i)
12364: AND 'Y' = l_array_bflow_historic (i);
12365:
12366: --
12367: -- Bug 5339999 Delete the problematic lines from xla_ae_headers_gt.
12368: --
12369:
12370: FORALL i IN 1..l_array_balance_type_code.COUNT
12371: DELETE FROM XLA_AE_HEADERS_GT
12367: -- Bug 5339999 Delete the problematic lines from xla_ae_headers_gt.
12368: --
12369:
12370: FORALL i IN 1..l_array_balance_type_code.COUNT
12371: DELETE FROM XLA_AE_HEADERS_GT
12372: WHERE ae_header_id = l_array_ae_header_id(i)
12373: AND event_id = l_array_event_id(i)
12374: AND ledger_id = l_array_ledger_id(i)
12375: AND 'Y' = l_array_bflow_historic (i);
12383: -------------------------------------------------------------------------
12384:
12385: FORALL i IN 1..l_array_balance_type_code.COUNT
12386:
12387: UPDATE xla_ae_headers_gt
12388: SET accounting_entry_status_code = XLA_AE_JOURNAL_ENTRY_PKG.C_INVALID -- C_INVALID_STATUS
12389: WHERE balance_type_code = l_array_balance_type_code(i)
12390: AND ae_header_id = l_array_ae_header_id(i)
12391: AND event_id = l_array_event_id(i)
12471: ,'N')
12472: ,'N')
12473: ,'N') -- 5443083
12474: FROM xla_ae_lines_gt l
12475: ,xla_ae_headers_gt h
12476: ,xla_gl_ledgers_v xlr
12477: WHERE (reversal_code = C_DUMMY_SAME
12478: OR reversal_code = C_MPA_SAME_ENTRY) -- 4655713b
12479: AND l.ae_header_id = h.ae_header_id
12893: -------------------------------------------------------------------------------------------------------------------------
12894: -- 4655713b Updates MPA-Accrual (not MPA-Recognition) and Accrual Reversal lines with Same Entry
12895: -------------------------------------------------------------------------------------------------------------------------
12896: IF xla_accounting_pkg.g_mpa_accrual_exists = 'Y' THEN -- 7128871
12897: SELECT /*+ Leading (xal1,xah1,xal2) index(xah1 XLA_AE_HEADERS_GT_U1) index(xal2 XLA_AE_LINES_GT_N2) no_expand */ -- 7128871
12898: xal2.segment1, xal2.segment2, xal2.segment3, xal2.segment4, xal2.segment5
12899: ,xal2.segment6, xal2.segment7, xal2.segment8, xal2.segment9, xal2.segment10
12900: ,xal2.segment11, xal2.segment12, xal2.segment13, xal2.segment14, xal2.segment15
12901: ,xal2.segment16, xal2.segment17, xal2.segment18, xal2.segment19, xal2.segment20
12926: ,l_array_mpa_temp_line_num
12927: ,l_array_mpa_ae_header_id
12928: ,l_array_mpa_header_num
12929: FROM xla_ae_lines_gt xal2 -- Original line
12930: ,xla_ae_headers_gt xah1
12931: ,xla_ae_lines_gt xal1 -- MPA line
12932: WHERE xal2.source_distribution_type = xal1.source_distribution_type
12933: AND NVL(xal2.source_distribution_id_num_1,C_NUM) = NVL(xal1.source_distribution_id_num_1,C_NUM)
12934: AND NVL(xal2.source_distribution_id_num_2,C_NUM) = NVL(xal1.source_distribution_id_num_2,C_NUM)
13235: ,p_level => C_LEVEL_STATEMENT
13236: );
13237: END IF;
13238:
13239: DELETE xla_ae_headers_gt hgt
13240: WHERE ledger_id = l_array_ledger_id(i)
13241: AND ae_header_id = l_array_ae_header_id(i)
13242: AND balance_type_code = l_array_balance_type_code(i)
13243: AND header_num = l_array_header_num(i)
13317: --
13318: -- Update JE header status for invalid entries
13319: --
13320: FORALL i IN 1..l_array_balance_type_code.COUNT
13321: UPDATE xla_ae_headers_gt hgt
13322: SET accounting_entry_status_code = XLA_AE_JOURNAL_ENTRY_PKG.C_INVALID -- C_INVALID_STATUS
13323: WHERE ae_header_id = l_array_ae_header_id(i)
13324: AND event_id = l_array_event_id(i)
13325: AND ledger_id = l_array_ledger_id(i)