[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 |
4441: ,l_array_mpa_acc_ae_header_id
4442: ,l_array_mpa_acc_header_num
4443: ,l_array_mpa_acc_balance_type
4444: FROM xla_ae_lines_gt lgt
4445: ,xla_ae_headers_gt hgt
4446: WHERE l_array_ledger_id(i) = lgt.ledger_id
4447: AND l_array_rev_dist_type(i) = lgt.source_distribution_type
4448: AND nvl(l_array_rev_dist_id_num_1(i),-99) = nvl(lgt.source_distribution_id_num_1,-99)
4449: AND nvl(l_array_rev_dist_id_num_2(i),-99) = nvl(lgt.source_distribution_id_num_2,-99)
4509: AND ae_header_id = l_array_mpa_acc_ae_header_id(j)
4510: AND header_num = l_array_mpa_acc_header_num(j);
4511:
4512: ---------------------------------------------------------------------------------------------------------
4513: -- 4669308 Delete the MPA/Accrual Line Reversal in xla_ae_headers_gt (not in distribution links yet)
4514: ---------------------------------------------------------------------------------------------------------
4515: FORALL j IN 1..l_array_mpa_acc_event_id.count
4516: DELETE xla_ae_headers_gt
4517: WHERE ledger_id = l_array_mpa_acc_ledger_id(j)
4512: ---------------------------------------------------------------------------------------------------------
4513: -- 4669308 Delete the MPA/Accrual Line Reversal in xla_ae_headers_gt (not in distribution links yet)
4514: ---------------------------------------------------------------------------------------------------------
4515: FORALL j IN 1..l_array_mpa_acc_event_id.count
4516: DELETE xla_ae_headers_gt
4517: WHERE ledger_id = l_array_mpa_acc_ledger_id(j)
4518: AND event_id = l_array_mpa_acc_event_id(j)
4519: AND ae_header_id = l_array_mpa_acc_ae_header_id(j)
4520: AND balance_type_code = l_array_mpa_acc_balance_type(j)
5530: , lgt2.anc_id_100
5531: FROM
5532: xla_ae_lines_gt lgt1
5533: ,xla_ae_lines_gt lgt2
5534: ,xla_ae_headers_gt hgt
5535: WHERE lgt1.reversal_code = 'DUMMY_LR'
5536: AND lgt2.ledger_id = lgt1.ledger_id
5537: AND lgt2.source_distribution_type = lgt1.reverse_distribution_type
5538: AND nvl(lgt2.source_distribution_id_num_1,-99) = nvl(lgt1.reverse_dist_id_num_1,-99)
6343: )
6344: AND NVL(lgt.header_num,0) > 0
6345: AND lgt.header_num IN
6346: (SELECT hgt.header_num
6347: FROM xla_ae_headers_gt hgt
6348: WHERE hgt.entity_id = l_array_entity_id(i)
6349: AND hgt.ledger_id = l_array_ledger_id(i)
6350: AND hgt.event_number < l_array_event_number(i)
6351: AND lgt.event_id = hgt.event_id
6354: AND lgt.header_num = hgt.header_num)
6355: ;
6356: --
6357: ---------------------------------------------------------------------------------------------------------
6358: -- 4669308 Delete the MPA/Accrual Transaction Reversal in xla_ae_headers_gt (not in distribution links yet)
6359: ---------------------------------------------------------------------------------------------------------
6360: --
6361: FORALL i IN 1..l_array_entity_id.count
6362: DELETE xla_ae_headers_gt hgt
6358: -- 4669308 Delete the MPA/Accrual Transaction Reversal in xla_ae_headers_gt (not in distribution links yet)
6359: ---------------------------------------------------------------------------------------------------------
6360: --
6361: FORALL i IN 1..l_array_entity_id.count
6362: DELETE xla_ae_headers_gt hgt
6363: WHERE hgt.ledger_id = l_array_ledger_id(i)
6364: AND hgt.entity_id = l_array_entity_id(i)
6365: AND hgt.event_number < l_array_event_number(i)
6366: AND NVL(hgt.header_num,0) > 0
7104: , lgt.anc_id_99
7105: , lgt.anc_id_100
7106:
7107: FROM xla_ae_lines_gt lgt
7108: ,xla_ae_headers_gt hgt
7109: WHERE hgt.entity_id = l_array_entity_id(i)
7110: AND hgt.ledger_id = l_array_ledger_id(i)
7111: AND hgt.event_number < l_array_event_number(i)
7112: AND lgt.event_id = hgt.event_id
7754: -- bug7135700 code change end
7755:
7756:
7757: --
7758: -- select all the reversal lines that do not have a header in xla_ae_headers_gt
7759: --
7760: SELECT event_id
7761: ,ledger_id
7762: ,balance_type_code
7773: FROM xla_ae_lines_gt lgt
7774: WHERE reversal_code = 'REVERSAL'
7775: AND NOT EXISTS
7776: (SELECT 1
7777: FROM xla_ae_headers_gt
7778: WHERE event_id = lgt.event_id
7779: AND ledger_id = lgt.ledger_id
7780: -- AND nvl(header_num, -1) = nvl(lgt.header_num, -1) -- 4262811c missing mpa reversal lines
7781: -- 4669308 NVL(-1) give separate headers for MPA
7801: ,p_module => l_log_module);
7802: END IF;
7803:
7804: --
7805: -- inserting new rows in xla_ae_headers_gt for the reversal lines that did not have a header
7806: --
7807: FORALL I IN 1..l_array_event_id.count
7808: INSERT INTO xla_ae_headers_gt
7809: ( ae_header_id
7804: --
7805: -- inserting new rows in xla_ae_headers_gt for the reversal lines that did not have a header
7806: --
7807: FORALL I IN 1..l_array_event_id.count
7808: INSERT INTO xla_ae_headers_gt
7809: ( ae_header_id
7810: , accounting_entry_status_code
7811: , accounting_entry_type_code
7812: , ledger_id
7861: , gl_transfer_status_code
7862: , decode(l_array_entry_status_code(i),XLA_AE_JOURNAL_ENTRY_PKG.C_VALID,'X','I')
7863: , l_array_header_num(i) -- 4262811c MPA header for line reversal
7864: , accrual_reversal_flag -- 4262811a
7865: FROM xla_ae_headers_gt
7866: WHERE event_id = l_array_event_id(i)
7867: AND ledger_id = l_array_ledger_id(i)
7868: AND balance_type_code = 'X'
7869: AND NVL(header_num,0) = 0); -- 5183946 xla_ae_headers_gt_u1 error
7865: FROM xla_ae_headers_gt
7866: WHERE event_id = l_array_event_id(i)
7867: AND ledger_id = l_array_ledger_id(i)
7868: AND balance_type_code = 'X'
7869: AND NVL(header_num,0) = 0); -- 5183946 xla_ae_headers_gt_u1 error
7870:
7871:
7872: IF (C_LEVEL_EVENT >= g_log_level) THEN
7873: trace
7870:
7871:
7872: IF (C_LEVEL_EVENT >= g_log_level) THEN
7873: trace
7874: (p_msg => 'Headers inserted in xla_ae_headers_gt = '||TO_CHAR(SQL%ROWCOUNT)
7875: ,p_level => C_LEVEL_EVENT
7876: ,p_module => l_log_module);
7877: END IF;
7878: --
10950: END
10951: END
10952: END
10953: FROM gl_daily_rates ra
10954: ,xla_ae_headers_gt xah
10955: ,FND_CURRENCIES fc
10956: ,FND_CURRENCIES fc1
10957: WHERE ra.conversion_date(+)=
10958: nvl(trunc(xal.currency_conversion_date), trunc(xah.accounting_date))
11105: END
11106: END
11107: END
11108: FROM gl_daily_rates ra
11109: ,xla_ae_headers_gt xah
11110: WHERE ra.conversion_date(+)=
11111: nvl(trunc(xal.currency_conversion_date), trunc(xah.accounting_date))
11112: AND ra.conversion_type (+)=
11113: CASE l_ledger_attrs.array_ledger_type(i)
12397: -------------------------------------------------------------------------------------------------------------------------
12398: -- 4655713b Update MPA and Accrual Reversal lines with Prior Entry
12399: -------------------------------------------------------------------------------------------------------------------------
12400: IF xla_accounting_pkg.g_mpa_accrual_exists = 'Y' THEN -- 5666366
12401: SELECT /*+ Leading (xal1,xah1,xal2) index(xah1 XLA_AE_HEADERS_GT_U1) index(xal2 XLA_AE_LINES_GT_N2) no_expand */ -- 5666366
12402: xal2.segment1, xal2.segment2, xal2.segment3, xal2.segment4, xal2.segment5
12403: ,xal2.segment6, xal2.segment7, xal2.segment8, xal2.segment9, xal2.segment10
12404: ,xal2.segment11, xal2.segment12, xal2.segment13, xal2.segment14, xal2.segment15
12405: ,xal2.segment16, xal2.segment17, xal2.segment18, xal2.segment19, xal2.segment20
12469: ,l_array_mpa_ae_header_id
12470: ,l_array_mpa_header_num
12471: ,l_array_acc_rev_flag
12472: FROM xla_ae_lines_gt xal2 -- original downstream line
12473: ,xla_ae_headers_gt xah1 -- original downstream header
12474: ,xla_ae_lines_gt xal1 -- recognition lines
12475: ,xla_ae_lines xal3 -- upstream line
12476: WHERE xal2.source_distribution_type = xal1.source_distribution_type
12477: AND NVL(xal2.source_distribution_id_num_1,C_NUM) = NVL(xal1.source_distribution_id_num_1,C_NUM)
12884: AND ledger_id = l_array_ledger_id(i)
12885: AND 'Y' = l_array_bflow_historic (i);
12886:
12887: --
12888: -- Bug 5339999 Delete the problematic lines from xla_ae_headers_gt.
12889: --
12890:
12891: FORALL i IN 1..l_array_balance_type_code.COUNT
12892: DELETE FROM XLA_AE_HEADERS_GT
12888: -- Bug 5339999 Delete the problematic lines from xla_ae_headers_gt.
12889: --
12890:
12891: FORALL i IN 1..l_array_balance_type_code.COUNT
12892: DELETE FROM XLA_AE_HEADERS_GT
12893: WHERE ae_header_id = l_array_ae_header_id(i)
12894: AND event_id = l_array_event_id(i)
12895: AND ledger_id = l_array_ledger_id(i)
12896: AND 'Y' = l_array_bflow_historic (i);
12904: -------------------------------------------------------------------------
12905:
12906: FORALL i IN 1..l_array_balance_type_code.COUNT
12907:
12908: UPDATE xla_ae_headers_gt
12909: SET accounting_entry_status_code = XLA_AE_JOURNAL_ENTRY_PKG.C_INVALID, -- C_INVALID_STATUS
12910: event_status_code='I' --Bug 13493286
12911: WHERE balance_type_code = l_array_balance_type_code(i)
12912: AND ae_header_id = l_array_ae_header_id(i)
12993: ,'N')
12994: ,'N')
12995: ,'N') -- 5443083
12996: FROM xla_ae_lines_gt l
12997: ,xla_ae_headers_gt h
12998: ,xla_gl_ledgers_v xlr
12999: WHERE (reversal_code = C_DUMMY_SAME
13000: OR reversal_code = C_MPA_SAME_ENTRY) -- 4655713b
13001: AND l.ae_header_id = h.ae_header_id
13429: -------------------------------------------------------------------------------------------------------------------------
13430: -- 4655713b Updates MPA-Accrual (not MPA-Recognition) and Accrual Reversal lines with Same Entry
13431: -------------------------------------------------------------------------------------------------------------------------
13432: IF xla_accounting_pkg.g_mpa_accrual_exists = 'Y' THEN -- 7128871
13433: SELECT /*+ Leading (xal1,xah1,xal2) index(xah1 XLA_AE_HEADERS_GT_U1) index(xal2 XLA_AE_LINES_GT_N2) no_expand */ -- 7128871
13434: xal2.segment1, xal2.segment2, xal2.segment3, xal2.segment4, xal2.segment5
13435: ,xal2.segment6, xal2.segment7, xal2.segment8, xal2.segment9, xal2.segment10
13436: ,xal2.segment11, xal2.segment12, xal2.segment13, xal2.segment14, xal2.segment15
13437: ,xal2.segment16, xal2.segment17, xal2.segment18, xal2.segment19, xal2.segment20
13462: ,l_array_mpa_temp_line_num
13463: ,l_array_mpa_ae_header_id
13464: ,l_array_mpa_header_num
13465: FROM xla_ae_lines_gt xal2 -- Original line
13466: ,xla_ae_headers_gt xah1
13467: ,xla_ae_lines_gt xal1 -- MPA line
13468: WHERE xal2.source_distribution_type = xal1.source_distribution_type
13469: AND NVL(xal2.source_distribution_id_num_1,C_NUM) = NVL(xal1.source_distribution_id_num_1,C_NUM)
13470: AND NVL(xal2.source_distribution_id_num_2,C_NUM) = NVL(xal1.source_distribution_id_num_2,C_NUM)
13772: ,p_level => C_LEVEL_STATEMENT
13773: );
13774: END IF;
13775:
13776: DELETE xla_ae_headers_gt hgt
13777: WHERE ledger_id = l_array_ledger_id(i)
13778: AND ae_header_id = l_array_ae_header_id(i)
13779: AND balance_type_code = l_array_balance_type_code(i)
13780: AND header_num = l_array_header_num(i)
13877:
13878: -- Update JE header status for invalid entries
13879: --
13880: FORALL i IN 1..l_array_balance_type_code.COUNT
13881: UPDATE xla_ae_headers_gt hgt
13882: SET accounting_entry_status_code = XLA_AE_JOURNAL_ENTRY_PKG.C_INVALID, -- C_INVALID_STATUS
13883: event_status_code='I' -- Bug 13493286
13884: WHERE ae_header_id = l_array_ae_header_id(i)
13885: AND event_id = l_array_event_id(i)