[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 20-MAI-2003 K.Boussema Added a Token to XLA_AP_CANNOT_INSERT_JE |
| message |
| 27-MAI-2003 K.Boussema Renamed the Document seq. Accounting sources |
| 27-MAI-2003 K.Boussema Renamed code_combination_status by |
| code_combination_status_flag |
| Renamed base_amount by ledger_amount |
| 30-MAI-2003 K.Boussema Renamed BUSGET_VERSION_ID by BUDGET_VERSION_ID|
| bug 2981358 |
| 11-JUN-2003 K.Boussema Renamed Sequence columns, bug 3000007 |
| 23-JUN-2003 K.Boussema Updated the call to get_period_name bug3005754|
| 17-JUL-2003 K.Boussema Reviewd the code |
| 17-SEP-2003 K.Boussema Updated to Get je_category from cache:3109690|
| 19-SEP-2003 K.Boussema Code changed to include reversed_ae_header_id|
| and reversed_line_num, see bug 3143095 |
| 03-OCT-2003 K.Boussema Changed description width to 1996 |
| 16-OCT-2003 K.Boussema Fixed the issue when the entered and |
| accounted amounts are reversed. |
| 22-OCT-2003 K.Boussema Changed to capture the Merge Matching Lines |
| preference for Accounting Reversal from JLT |
| 26-NOV-2003 K.Boussema Removed the insert of the sl_coa_mapping name|
| for change third party lines, bug 3278955 |
| 12-DEC-2003 K. Boussema Renamed target_coa_id in xla_ae_lines_gt |
| by ccid_coa_id |
| Reviewed the InsertHeaders, bug 3042840 |
| 18-DEC-2003 K.Boussema Changed to fix bug 3042840,3307761,3268940 |
| 3310291 and 3320689 |
| 07-JAN-2004 K.Boussema Changed to populate switch_side_flag column |
| 19-JAN-2004 K.Boussema Removed the validation of doc sequence |
| 05-FEB-2004 S.Singhania Changes based on bug 3419803. |
| - correct column names are used |
| TAX_LINE_REF_ID, TAX_SUMMARY_LINE_REF_ID,|
| TAX_REC_NREC_DIST_REF_ID |
| - reference to the column is removed. |
| TAX_REC_NREC_SUMMARY_DIST_REF |
| 17-FEB-2004 K.Boussema Made changes for the FND_LOG. |
| passed ae_header_id to error messages |
| 12-MAR-2004 K.Boussema Removed the validation of the party type |
| 22-MAR-2004 K.Boussema Added a parameter p_module to the TRACE calls|
| and the procedure. |
| 29-MAR-2204 K.Boussema Changed based on bug 3528667 |
| - added get_period_name function to retrieve|
| the period_name from gl_period_statuses |
| - added the cache of period_name |
| - changed the insert into xla_ae_headers_gt |
| 11-MAY-2004 K.Boussema Removed the call to XLA trace routine from |
| trace() procedure |
| 17-MAY-2004 W.Shen change for attribute enhancement project |
| 1. add TransactionReversal procedure from |
| lines package |
| 2. SetHdrAccountingSource, add gl_transfer_flag|
| and trx_acct_reversal_option |
| 3. SetHdrAccountingSource, add gl_date |
| 4. change to InsertHeaders procedure |
| 5. rename reversed_ae_header_id to |
| ref_ae_header_id, reversed_ae_line_num to |
| ref_ae_line_num |
| 26-MAY-2004 W.Shen change error message |
| 02-JUL-2004 W.Shen if gl_transfer_flag is not mapped at all, set |
| the default value to 'N' instead of 'NT' |
| bug 3741223 |
| 27-JUL-2004 W.Shen In SetHeaderId, if all the 3 flag is 'N', |
| (which is the case when transaction reversal)|
| 3 header ids are generated and assigned |
| bug 3786980 |
| 23-Sep-2004 S.Singhania Made changes for the bulk peroformance. It has |
| changed the code at number of places. |
| 25-May-2005 W. Shen Remove column ledger_amount,entered amount |
| from xla_distribution_links, change the |
| function change_third_party to remove the |
| reference to the columns. |
| 11-Jul-2005 A. Wan Changed for MPA. 4262811 |
| 01-Aug-2005 W. Chan 4458381 - Public Sector Enhancement |
| 18-Oct-2005 V. Kumar Removed code for Analytical Criteria |
| 23-Nov-2005 V. Kumar Bug 4752774 - Added Hints for performance |
| 3-Jan-2006 W. Chan Bug 4924492 - Populate budget version id for |
| accounting reversal |
| 20-JAN-2006 A.Wan 4884853 add GetAccrualRevDate |
| 07-FEB-2006 A.Wan 4897417 error if MPA's GL periods not defined. |
| 13-Feb-2006 A.Wan 4955764 - set MPA's g_rec_lines.array_gl_date |
| 16-Apr-2006 A.Wan 5132302 - applied to amt for Gain/Loss |
| 09-May-2006 A.Wan 5161760 - chk header attributes is valid. |
| 21-Jun-2006 A.Wan 5100860 Performance fix, see bug for detail |
+===========================================================================*/
--
--+==========================================================================+
--| |
--| |
--| CONSTANTS |
--| |
--| |
--+==========================================================================+
--
--
C_VALID CONSTANT NUMBER := 0;
SELECT period_name
,CASE p_gl_date_option
WHEN 'XLA_FIRST_DAY_NEXT_GL_PERIOD' THEN start_date
WHEN 'XLA_LAST_DAY_NEXT_GL_PERIOD' THEN end_date
ELSE p_gl_date + 1
END GL_DATE
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.adjustment_period_flag = 'N'
AND p_gl_date < gps.start_date
ORDER BY gps.start_date ASC;
SELECT period_name
,CASE p_gl_date_option
WHEN 'FIRST_DAY_GL_PERIOD' THEN start_date
WHEN 'LAST_DAY_GL_PERIOD' THEN end_date
ELSE ADD_MONTHS(p_start_date, ROUND(MONTHS_BETWEEN(start_date, p_start_date))) -- originating_day
END GL_DATE
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.ledger_id = p_ledger_id
AND gps.adjustment_period_flag = 'N'
AND p_end_date BETWEEN gps.start_date AND gps.end_date
ORDER BY gps.start_date DESC;
SELECT period_name,
CASE p_gl_date_option
WHEN 'FIRST_DAY_GL_PERIOD' THEN start_date
WHEN 'LAST_DAY_GL_PERIOD' THEN end_date
ELSE ADD_MONTHS(p_start_date, ROUND(MONTHS_BETWEEN(start_date, p_start_date))) -- originating_day
END GL_DATE,
-- closing_status, -- Not needed. Updated in PostAccountingEngine (?)
CASE p_proration_code
WHEN '360_DAYS' THEN 30
ELSE end_date-start_date+1
END DAYS_IN_PERIOD,
CASE p_proration_code
WHEN '360_DAYS' THEN least(end_date-p_start_date+1,30)
ELSE end_date-p_start_date+1
END DAYS_IN_FIRST_PERIOD, -- only valid for first period
CASE p_proration_code
WHEN '360_DAYS' THEN least(p_end_date-start_date+1,30)
ELSE p_end_date-start_date+1
END DAYS_IN_LAST_PERIOD -- only valid for last period
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = p_ledger_id
AND adjustment_period_flag = 'N'
AND end_date > p_start_date
AND start_date < p_end_date
ORDER BY start_date;
x_period_names.DELETE(x_num_entries);
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => l_hdr_idx
,p_balance_type_code => 'A');
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => l_hdr_idx
,p_balance_type_code => 'A');
XLA_AE_JOURNAL_ENTRY_PKG.UpdateJournalEntryStatus (p_hdr_idx => l_hdr_idx
,p_balance_type_code => 'A');
| InsertHeaders |
| |
+======================================================================*/
FUNCTION InsertHeaders
RETURN BOOLEAN
IS
--
l_period_name VARCHAR2(25);
l_log_module := C_DEFAULT_MODULE||'.InsertHeaders';
(p_msg => 'BEGIN of InsertHeaders'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'SQL - Insert into xla_ae_headers_gt'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_ae_headers_gt
( ae_header_id
, accounting_entry_status_code
, accounting_entry_type_code
, ledger_id
, entity_id
, event_id
, event_type_code
, accounting_date
, product_rule_type_code
, product_rule_code
, product_rule_version
, je_category_name
--, period_name
, doc_sequence_id
, doc_sequence_value
, description
, budget_version_id
--, encumbrance_type_id
, balance_type_code
, amb_context_code
, doc_category_code
, gl_transfer_status_code
, event_status_code
, anc_id_1
, anc_id_2
, anc_id_3
, anc_id_4
, anc_id_5
, anc_id_6
, anc_id_7
, anc_id_8
, anc_id_9
, anc_id_10
, anc_id_11
, anc_id_12
, anc_id_13
, anc_id_14
, anc_id_15
, anc_id_16
, anc_id_17
, anc_id_18
, anc_id_19
, anc_id_20
, anc_id_21
, anc_id_22
, anc_id_23
, anc_id_24
, anc_id_25
, anc_id_26
, anc_id_27
, anc_id_28
, anc_id_29
, anc_id_30
, anc_id_31
, anc_id_32
, anc_id_33
, anc_id_34
, anc_id_35
, anc_id_36
, anc_id_37
, anc_id_38
, anc_id_39
, anc_id_40
, anc_id_41
, anc_id_42
, anc_id_43
, anc_id_44
, anc_id_45
, anc_id_46
, anc_id_47
, anc_id_48
, anc_id_49
, anc_id_50
, anc_id_51
, anc_id_52
, anc_id_53
, anc_id_54
, anc_id_55
, anc_id_56
, anc_id_57
, anc_id_58
, anc_id_59
, anc_id_60
, anc_id_61
, anc_id_62
, anc_id_63
, anc_id_64
, anc_id_65
, anc_id_66
, anc_id_67
, anc_id_68
, anc_id_69
, anc_id_70
, anc_id_71
, anc_id_72
, anc_id_73
, anc_id_74
, anc_id_75
, anc_id_76
, anc_id_77
, anc_id_78
, anc_id_79
, anc_id_80
, anc_id_81
, anc_id_82
, anc_id_83
, anc_id_84
, anc_id_85
, anc_id_86
, anc_id_87
, anc_id_88
, anc_id_89
, anc_id_90
, anc_id_91
, anc_id_92
, anc_id_93
, anc_id_94
, anc_id_95
, anc_id_96
, anc_id_97
, anc_id_98
, anc_id_99
, anc_id_100
--
, event_number
, header_num -- 4262811
, accrual_reversal_flag -- 4262811
, acc_rev_gl_date_option -- 4262811
, parent_header_id -- 4262811
, parent_ae_line_num) -- 4262811
(SELECT /*+ INDEX (AEL, XLA_AE_LINES_GT_N2) */ -- 4752774
g_rec_header_new.array_event_id(Idx)
, CASE ael.balance_type_code
when C_BUDGET THEN
g_rec_header_new.array_budget_status(Idx)
when C_ENCUMBRANCE THEN
g_rec_header_new.array_encumbrance_status(Idx)
when C_ACTUAL THEN
g_rec_header_new.array_actual_status(Idx)
ELSE
XLA_AE_JOURNAL_ENTRY_PKG.C_NOT_CREATED
END CASE
, C_STANDARD
, g_rec_header_new.array_target_ledger_id(Idx)
, g_rec_header_new.array_entity_id(Idx)
, g_rec_header_new.array_event_id(Idx)
, g_rec_header_new.array_event_type_code(Idx)
, g_rec_header_new.array_gl_date(Idx)
, XLA_AE_JOURNAL_ENTRY_PKG.g_cache_pad.product_rule_type_code
, XLA_AE_JOURNAL_ENTRY_PKG.g_cache_pad.product_rule_code
, XLA_AE_JOURNAL_ENTRY_PKG.g_cache_pad.product_rule_version
, g_rec_header_new.array_je_category_name(Idx)
--, 'XYZ' --l_period_name
, g_rec_header_new.array_doc_sequence_id(Idx)
, g_rec_header_new.array_doc_sequence_value(Idx)
, g_rec_header_new.array_description(Idx)
, DECODE(ael.balance_type_code -- 4924492
,C_BUDGET, g_rec_header_new.array_budget_version_id(Idx)
,'X', g_rec_header_new.array_budget_version_id(Idx)
,NULL)
--, DECODE(ael.balance_type_code, C_ENCUMBRANCE, g_rec_header_new.array_encumbrance_type_id(Idx)
-- , NULL) -- 4458381 Public Sector Enh
, ael.balance_type_code
, XLA_AE_JOURNAL_ENTRY_PKG.g_cache_pad.amb_context_code
, g_rec_header_new.array_doc_category_code(Idx)
, DECODE(g_rec_header_new.array_gl_transfer_flag(Idx), 'N', 'NT', 'Y', 'N', 'N')
, g_rec_header_new.array_event_status(Idx)
, g_rec_header_new.array_anc_id_1(Idx)
, g_rec_header_new.array_anc_id_2(Idx)
, g_rec_header_new.array_anc_id_3(Idx)
, g_rec_header_new.array_anc_id_4(Idx)
, g_rec_header_new.array_anc_id_5(Idx)
, g_rec_header_new.array_anc_id_6(Idx)
, g_rec_header_new.array_anc_id_7(Idx)
, g_rec_header_new.array_anc_id_8(Idx)
, g_rec_header_new.array_anc_id_9(Idx)
, g_rec_header_new.array_anc_id_10(Idx)
, g_rec_header_new.array_anc_id_11(Idx)
, g_rec_header_new.array_anc_id_12(Idx)
, g_rec_header_new.array_anc_id_13(Idx)
, g_rec_header_new.array_anc_id_14(Idx)
, g_rec_header_new.array_anc_id_15(Idx)
, g_rec_header_new.array_anc_id_16(Idx)
, g_rec_header_new.array_anc_id_17(Idx)
, g_rec_header_new.array_anc_id_18(Idx)
, g_rec_header_new.array_anc_id_19(Idx)
, g_rec_header_new.array_anc_id_20(Idx)
, g_rec_header_new.array_anc_id_21(Idx)
, g_rec_header_new.array_anc_id_22(Idx)
, g_rec_header_new.array_anc_id_23(Idx)
, g_rec_header_new.array_anc_id_24(Idx)
, g_rec_header_new.array_anc_id_25(Idx)
, g_rec_header_new.array_anc_id_26(Idx)
, g_rec_header_new.array_anc_id_27(Idx)
, g_rec_header_new.array_anc_id_28(Idx)
, g_rec_header_new.array_anc_id_29(Idx)
, g_rec_header_new.array_anc_id_30(Idx)
, g_rec_header_new.array_anc_id_31(Idx)
, g_rec_header_new.array_anc_id_32(Idx)
, g_rec_header_new.array_anc_id_33(Idx)
, g_rec_header_new.array_anc_id_34(Idx)
, g_rec_header_new.array_anc_id_35(Idx)
, g_rec_header_new.array_anc_id_36(Idx)
, g_rec_header_new.array_anc_id_37(Idx)
, g_rec_header_new.array_anc_id_38(Idx)
, g_rec_header_new.array_anc_id_39(Idx)
, g_rec_header_new.array_anc_id_40(Idx)
, g_rec_header_new.array_anc_id_41(Idx)
, g_rec_header_new.array_anc_id_42(Idx)
, g_rec_header_new.array_anc_id_43(Idx)
, g_rec_header_new.array_anc_id_44(Idx)
, g_rec_header_new.array_anc_id_45(Idx)
, g_rec_header_new.array_anc_id_46(Idx)
, g_rec_header_new.array_anc_id_47(Idx)
, g_rec_header_new.array_anc_id_48(Idx)
, g_rec_header_new.array_anc_id_49(Idx)
, g_rec_header_new.array_anc_id_50(Idx)
, g_rec_header_new.array_anc_id_51(Idx)
, g_rec_header_new.array_anc_id_52(Idx)
, g_rec_header_new.array_anc_id_53(Idx)
, g_rec_header_new.array_anc_id_54(Idx)
, g_rec_header_new.array_anc_id_55(Idx)
, g_rec_header_new.array_anc_id_56(Idx)
, g_rec_header_new.array_anc_id_57(Idx)
, g_rec_header_new.array_anc_id_58(Idx)
, g_rec_header_new.array_anc_id_59(Idx)
, g_rec_header_new.array_anc_id_60(Idx)
, g_rec_header_new.array_anc_id_61(Idx)
, g_rec_header_new.array_anc_id_62(Idx)
, g_rec_header_new.array_anc_id_63(Idx)
, g_rec_header_new.array_anc_id_64(Idx)
, g_rec_header_new.array_anc_id_65(Idx)
, g_rec_header_new.array_anc_id_66(Idx)
, g_rec_header_new.array_anc_id_67(Idx)
, g_rec_header_new.array_anc_id_68(Idx)
, g_rec_header_new.array_anc_id_69(Idx)
, g_rec_header_new.array_anc_id_70(Idx)
, g_rec_header_new.array_anc_id_71(Idx)
, g_rec_header_new.array_anc_id_72(Idx)
, g_rec_header_new.array_anc_id_73(Idx)
, g_rec_header_new.array_anc_id_74(Idx)
, g_rec_header_new.array_anc_id_75(Idx)
, g_rec_header_new.array_anc_id_76(Idx)
, g_rec_header_new.array_anc_id_77(Idx)
, g_rec_header_new.array_anc_id_78(Idx)
, g_rec_header_new.array_anc_id_79(Idx)
, g_rec_header_new.array_anc_id_80(Idx)
, g_rec_header_new.array_anc_id_81(Idx)
, g_rec_header_new.array_anc_id_82(Idx)
, g_rec_header_new.array_anc_id_83(Idx)
, g_rec_header_new.array_anc_id_84(Idx)
, g_rec_header_new.array_anc_id_85(Idx)
, g_rec_header_new.array_anc_id_86(Idx)
, g_rec_header_new.array_anc_id_87(Idx)
, g_rec_header_new.array_anc_id_88(Idx)
, g_rec_header_new.array_anc_id_89(Idx)
, g_rec_header_new.array_anc_id_90(Idx)
, g_rec_header_new.array_anc_id_91(Idx)
, g_rec_header_new.array_anc_id_92(Idx)
, g_rec_header_new.array_anc_id_93(Idx)
, g_rec_header_new.array_anc_id_94(Idx)
, g_rec_header_new.array_anc_id_95(Idx)
, g_rec_header_new.array_anc_id_96(Idx)
, g_rec_header_new.array_anc_id_97(Idx)
, g_rec_header_new.array_anc_id_98(Idx)
, g_rec_header_new.array_anc_id_99(Idx)
, g_rec_header_new.array_anc_id_100(Idx)
--
, g_rec_header_new.array_event_number(Idx)
, g_rec_header_new.array_header_num(Idx) -- 4262811
, g_rec_header_new.array_accrual_reversal_flag(Idx) -- 4262811
, g_rec_header_new.array_acc_rev_gl_date_option(Idx) -- 4262811
, g_rec_header_new.array_parent_header_id(Idx) -- 4262811
, g_rec_header_new.array_parent_line_num(Idx) -- 4262811
FROM xla_ae_lines_gt ael
WHERE ael.ae_header_id = g_rec_header_new.array_event_id(Idx)
AND ael.ledger_id = g_rec_header_new.array_target_ledger_id(Idx)
AND (nvl(ael.gain_or_loss_flag, 'N') <> 'Y' or nvl(ael.calculate_g_l_amts_flag, 'N') <> 'Y')
-- AND nvl(ael.entered_amount,0) > 0
-- AND ael.temp_line_num <> 0
group by ael.balance_type_code
);
(p_msg => '# temporary headers inserted into GT xla_ae_headers_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
(p_msg => 'END of InsertHeaders'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'XLA_AE_HEADER_PKG.InsertHeaders');
(p_location => 'XLA_AE_HEADER_PKG.InsertHeaders');
END InsertHeaders;
(p_msg => 'SQL - Insert into xla_ae_lines_gt '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_ae_lines_gt
(
ae_header_id
, temp_line_num
, event_id
--
, ref_ae_header_id
, ref_ae_line_num
, ref_temp_line_num
--
, accounting_class_code
, event_class_code
, event_type_code
, line_definition_owner_code
, line_definition_code
, accounting_line_type_code
, accounting_line_code
--
, code_combination_status_code
, code_combination_id
, sl_coa_mapping_name
, dynamic_insert_flag
, source_coa_id
, ccid_coa_id
--
, description
, gl_transfer_mode_code
, merge_duplicate_code
, switch_side_flag
--
--, entered_amount
--, ledger_amount
, unrounded_entered_cr
, unrounded_entered_dr
, unrounded_accounted_cr
, unrounded_accounted_dr
, entered_cr
, entered_dr
, accounted_cr
, accounted_dr
, currency_code
, currency_conversion_date
, currency_conversion_rate
, currency_conversion_type
, statistical_amount
--
, party_id
, party_site_id
, party_type_code
--
, ussgl_transaction_code
, jgzz_recon_ref
--
, source_distribution_id_char_1
, source_distribution_id_char_2
, source_distribution_id_char_3
, source_distribution_id_char_4
, source_distribution_id_char_5
, source_distribution_id_num_1
, source_distribution_id_num_2
, source_distribution_id_num_3
, source_distribution_id_num_4
, source_distribution_id_num_5
, source_distribution_type
--
, tax_line_ref_id
, tax_summary_line_ref_id
, tax_rec_nrec_dist_ref_id
, anc_id_1
, anc_id_2
, anc_id_3
, anc_id_4
, anc_id_5
, anc_id_6
, anc_id_7
, anc_id_8
, anc_id_9
, anc_id_10
, anc_id_11
, anc_id_12
, anc_id_13
, anc_id_14
, anc_id_15
, anc_id_16
, anc_id_17
, anc_id_18
, anc_id_19
, anc_id_20
, anc_id_21
, anc_id_22
, anc_id_23
, anc_id_24
, anc_id_25
, anc_id_26
, anc_id_27
, anc_id_28
, anc_id_29
, anc_id_30
, anc_id_31
, anc_id_32
, anc_id_33
, anc_id_34
, anc_id_35
, anc_id_36
, anc_id_37
, anc_id_38
, anc_id_39
, anc_id_40
, anc_id_41
, anc_id_42
, anc_id_43
, anc_id_44
, anc_id_45
, anc_id_46
, anc_id_47
, anc_id_48
, anc_id_49
, anc_id_50
, anc_id_51
, anc_id_52
, anc_id_53
, anc_id_54
, anc_id_55
, anc_id_56
, anc_id_57
, anc_id_58
, anc_id_59
, anc_id_60
, anc_id_61
, anc_id_62
, anc_id_63
, anc_id_64
, anc_id_65
, anc_id_66
, anc_id_67
, anc_id_68
, anc_id_69
, anc_id_70
, anc_id_71
, anc_id_72
, anc_id_73
, anc_id_74
, anc_id_75
, anc_id_76
, anc_id_77
, anc_id_78
, anc_id_79
, anc_id_80
, anc_id_81
, anc_id_82
, anc_id_83
, anc_id_84
, anc_id_85
, anc_id_86
, anc_id_87
, anc_id_88
, anc_id_89
, anc_id_90
, anc_id_91
, anc_id_92
, anc_id_93
, anc_id_94
, anc_id_95
, anc_id_96
, anc_id_97
, anc_id_98
, anc_id_99
, anc_id_100
, inherit_desc_flag -- 4219869
, mpa_accrual_entry_flag -- 4262811
, encumbrance_type_id -- 4458381 Public Sector Enh
, header_num -- 5100860 assign value to avoid using function index
)
SELECT
CASE xah.balance_type_code
WHEN C_ACTUAL THEN g_rec_header_new.array_actual_header_id(g_header_idx)
WHEN C_BUDGET THEN g_rec_header_new.array_budget_header_id(g_header_idx)
WHEN C_ENCUMBRANCE THEN g_rec_header_new.array_encumb_header_id(g_header_idx)
END
, XLA_AE_LINES_PKG.SetLineNum(xah.balance_type_code)
, XLA_AE_JOURNAL_ENTRY_PKG.g_cache_event.event_id
--
, xal.ae_header_id
, xal.ae_line_num
, xdl.temp_line_num
--
, xal.accounting_class_code
, xdl.event_class_code
, xdl.event_type_code
, xdl.line_definition_owner_code
, xdl.line_definition_code
, xdl.accounting_line_type_code
, xdl.accounting_line_code
--
, C_CCID
, xal.code_combination_id
, NULL
, NULL
, NULL
, NULL
, xal.description
, xal.gl_transfer_mode_code
, xdl.merge_duplicate_code
, DECODE(XLA_AE_JOURNAL_ENTRY_PKG.g_cache_ledgers_info.ledger_reversal_option,
'SIDE', C_SWITCH,
C_NO_SWITCH
)
/* , xdl.entered_amount
, xdl.ledger_amount */
--
, xal.unrounded_entered_dr
, xal.unrounded_entered_cr
, xal.unrounded_accounted_dr
, xal.unrounded_accounted_cr
, xal.entered_dr
, xal.entered_cr
, xal.accounted_dr
, xal.accounted_cr
--
, xal.currency_code
, xal.currency_conversion_date
, xal.currency_conversion_rate
, xal.currency_conversion_type
, xal.statistical_amount
--
, xal.party_id
, xal.party_site_id
, xal.party_type_code
--
, xal.ussgl_transaction_code
, xal.jgzz_recon_ref
--
, xdl.source_distribution_id_char_1
, xdl.source_distribution_id_char_2
, xdl.source_distribution_id_char_3
, xdl.source_distribution_id_char_4
, xdl.source_distribution_id_char_5
, xdl.source_distribution_id_num_1
, xdl.source_distribution_id_num_2
, xdl.source_distribution_id_num_3
, xdl.source_distribution_id_num_4
, xdl.source_distribution_id_num_5
, xdl.source_distribution_type
--
, xdl.tax_line_ref_id
, xdl.tax_summary_line_ref_id
, xdl.tax_rec_nrec_dist_ref_id
, anc_id_1
, anc_id_2
, anc_id_3
, anc_id_4
, anc_id_5
, anc_id_6
, anc_id_7
, anc_id_8
, anc_id_9
, anc_id_10
, anc_id_11
, anc_id_12
, anc_id_13
, anc_id_14
, anc_id_15
, anc_id_16
, anc_id_17
, anc_id_18
, anc_id_19
, anc_id_20
, anc_id_21
, anc_id_22
, anc_id_23
, anc_id_24
, anc_id_25
, anc_id_26
, anc_id_27
, anc_id_28
, anc_id_29
, anc_id_30
, anc_id_31
, anc_id_32
, anc_id_33
, anc_id_34
, anc_id_35
, anc_id_36
, anc_id_37
, anc_id_38
, anc_id_39
, anc_id_40
, anc_id_41
, anc_id_42
, anc_id_43
, anc_id_44
, anc_id_45
, anc_id_46
, anc_id_47
, anc_id_48
, anc_id_49
, anc_id_50
, anc_id_51
, anc_id_52
, anc_id_53
, anc_id_54
, anc_id_55
, anc_id_56
, anc_id_57
, anc_id_58
, anc_id_59
, anc_id_60
, anc_id_61
, anc_id_62
, anc_id_63
, anc_id_64
, anc_id_65
, anc_id_66
, anc_id_67
, anc_id_68
, anc_id_69
, anc_id_70
, anc_id_71
, anc_id_72
, anc_id_73
, anc_id_74
, anc_id_75
, anc_id_76
, anc_id_77
, anc_id_78
, anc_id_79
, anc_id_80
, anc_id_81
, anc_id_82
, anc_id_83
, anc_id_84
, anc_id_85
, anc_id_86
, anc_id_87
, anc_id_88
, anc_id_89
, anc_id_90
, anc_id_91
, anc_id_92
, anc_id_93
, anc_id_94
, anc_id_95
, anc_id_96
, anc_id_97
, anc_id_98
, anc_id_99
, anc_id_100
, 'N' -- 4219869 inherit_desc_flag
, 'N' -- 4262811 mpa_accrual_entry_flag
, xal.encumbrance_type_id -- 4458381 Public Sector Enh
, 0 -- 5100860 assign value to avoid using function index
--
FROM xla_ae_lines xal,
xla_ae_headers xah,
xla_distribution_links xdl,
xla_events xe,
(SELECT ae_header_id
,ae_line_num
,MAX(DECODE(rank,1,anc_id)) anc_id_1
,MAX(DECODE(rank,2,anc_id)) anc_id_2
,MAX(DECODE(rank,3,anc_id)) anc_id_3
,MAX(DECODE(rank,4,anc_id)) anc_id_4
,MAX(DECODE(rank,5,anc_id)) anc_id_5
,MAX(DECODE(rank,6,anc_id)) anc_id_6
,MAX(DECODE(rank,7,anc_id)) anc_id_7
,MAX(DECODE(rank,8,anc_id)) anc_id_8
,MAX(DECODE(rank,9,anc_id)) anc_id_9
,MAX(DECODE(rank,10,anc_id)) anc_id_10
,MAX(DECODE(rank,11,anc_id)) anc_id_11
,MAX(DECODE(rank,12,anc_id)) anc_id_12
,MAX(DECODE(rank,13,anc_id)) anc_id_13
,MAX(DECODE(rank,14,anc_id)) anc_id_14
,MAX(DECODE(rank,15,anc_id)) anc_id_15
,MAX(DECODE(rank,16,anc_id)) anc_id_16
,MAX(DECODE(rank,17,anc_id)) anc_id_17
,MAX(DECODE(rank,18,anc_id)) anc_id_18
,MAX(DECODE(rank,19,anc_id)) anc_id_19
,MAX(DECODE(rank,20,anc_id)) anc_id_20
,MAX(DECODE(rank,21,anc_id)) anc_id_21
,MAX(DECODE(rank,22,anc_id)) anc_id_22
,MAX(DECODE(rank,23,anc_id)) anc_id_23
,MAX(DECODE(rank,24,anc_id)) anc_id_24
,MAX(DECODE(rank,25,anc_id)) anc_id_25
,MAX(DECODE(rank,26,anc_id)) anc_id_26
,MAX(DECODE(rank,27,anc_id)) anc_id_27
,MAX(DECODE(rank,28,anc_id)) anc_id_28
,MAX(DECODE(rank,29,anc_id)) anc_id_29
,MAX(DECODE(rank,30,anc_id)) anc_id_30
,MAX(DECODE(rank,31,anc_id)) anc_id_31
,MAX(DECODE(rank,32,anc_id)) anc_id_32
,MAX(DECODE(rank,33,anc_id)) anc_id_33
,MAX(DECODE(rank,34,anc_id)) anc_id_34
,MAX(DECODE(rank,35,anc_id)) anc_id_35
,MAX(DECODE(rank,36,anc_id)) anc_id_36
,MAX(DECODE(rank,37,anc_id)) anc_id_37
,MAX(DECODE(rank,38,anc_id)) anc_id_38
,MAX(DECODE(rank,39,anc_id)) anc_id_39
,MAX(DECODE(rank,40,anc_id)) anc_id_40
,MAX(DECODE(rank,41,anc_id)) anc_id_41
,MAX(DECODE(rank,42,anc_id)) anc_id_42
,MAX(DECODE(rank,43,anc_id)) anc_id_43
,MAX(DECODE(rank,44,anc_id)) anc_id_44
,MAX(DECODE(rank,45,anc_id)) anc_id_45
,MAX(DECODE(rank,46,anc_id)) anc_id_46
,MAX(DECODE(rank,47,anc_id)) anc_id_47
,MAX(DECODE(rank,48,anc_id)) anc_id_48
,MAX(DECODE(rank,49,anc_id)) anc_id_49
,MAX(DECODE(rank,50,anc_id)) anc_id_50
,MAX(DECODE(rank,51,anc_id)) anc_id_51
,MAX(DECODE(rank,52,anc_id)) anc_id_52
,MAX(DECODE(rank,53,anc_id)) anc_id_53
,MAX(DECODE(rank,54,anc_id)) anc_id_54
,MAX(DECODE(rank,55,anc_id)) anc_id_55
,MAX(DECODE(rank,56,anc_id)) anc_id_56
,MAX(DECODE(rank,57,anc_id)) anc_id_57
,MAX(DECODE(rank,58,anc_id)) anc_id_58
,MAX(DECODE(rank,59,anc_id)) anc_id_59
,MAX(DECODE(rank,60,anc_id)) anc_id_60
,MAX(DECODE(rank,61,anc_id)) anc_id_61
,MAX(DECODE(rank,62,anc_id)) anc_id_62
,MAX(DECODE(rank,63,anc_id)) anc_id_63
,MAX(DECODE(rank,64,anc_id)) anc_id_64
,MAX(DECODE(rank,65,anc_id)) anc_id_65
,MAX(DECODE(rank,66,anc_id)) anc_id_66
,MAX(DECODE(rank,67,anc_id)) anc_id_67
,MAX(DECODE(rank,68,anc_id)) anc_id_68
,MAX(DECODE(rank,69,anc_id)) anc_id_69
,MAX(DECODE(rank,70,anc_id)) anc_id_70
,MAX(DECODE(rank,71,anc_id)) anc_id_71
,MAX(DECODE(rank,72,anc_id)) anc_id_72
,MAX(DECODE(rank,73,anc_id)) anc_id_73
,MAX(DECODE(rank,74,anc_id)) anc_id_74
,MAX(DECODE(rank,75,anc_id)) anc_id_75
,MAX(DECODE(rank,76,anc_id)) anc_id_76
,MAX(DECODE(rank,77,anc_id)) anc_id_77
,MAX(DECODE(rank,78,anc_id)) anc_id_78
,MAX(DECODE(rank,79,anc_id)) anc_id_79
,MAX(DECODE(rank,80,anc_id)) anc_id_80
,MAX(DECODE(rank,81,anc_id)) anc_id_81
,MAX(DECODE(rank,82,anc_id)) anc_id_82
,MAX(DECODE(rank,83,anc_id)) anc_id_83
,MAX(DECODE(rank,84,anc_id)) anc_id_84
,MAX(DECODE(rank,85,anc_id)) anc_id_85
,MAX(DECODE(rank,86,anc_id)) anc_id_86
,MAX(DECODE(rank,87,anc_id)) anc_id_87
,MAX(DECODE(rank,88,anc_id)) anc_id_88
,MAX(DECODE(rank,89,anc_id)) anc_id_89
,MAX(DECODE(rank,90,anc_id)) anc_id_90
,MAX(DECODE(rank,91,anc_id)) anc_id_91
,MAX(DECODE(rank,92,anc_id)) anc_id_92
,MAX(DECODE(rank,93,anc_id)) anc_id_93
,MAX(DECODE(rank,94,anc_id)) anc_id_94
,MAX(DECODE(rank,95,anc_id)) anc_id_95
,MAX(DECODE(rank,96,anc_id)) anc_id_96
,MAX(DECODE(rank,97,anc_id)) anc_id_97
,MAX(DECODE(rank,98,anc_id)) anc_id_98
,MAX(DECODE(rank,99,anc_id)) anc_id_99
,MAX(DECODE(rank,100,anc_id)) anc_id_100
FROM
(SELECT xald.ae_header_id
, xald.ae_line_num
, xald.analytical_criterion_code || '(]' ||
xald.analytical_criterion_type_code || '(]' ||
xald.amb_context_code || '(]' ||
xald.ac1 || '(]' ||
xald.ac2 || '(]' ||
xald.ac3 || '(]' ||
xald.ac4 || '(]' ||
xald.ac5 anc_id
, RANK() OVER (
PARTITION BY ae_header_id, ae_line_num
ORDER BY analytical_criterion_code
,analytical_criterion_type_code
,amb_context_code
,ac1
,ac2
,ac3
,ac4
,ac5) rank
FROM xla_ae_line_acs xald)
GROUP BY ae_header_id, ae_line_num) anc
WHERE xe.event_id = xdl.event_id
AND xe.event_id = xah.event_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xe.entity_id = XLA_AE_JOURNAL_ENTRY_PKG.g_cache_event.entity_id
AND anc.ae_header_id = xdl.ae_header_id
AND anc.ae_line_num = xdl.ae_line_num
AND xah.ledger_id = XLA_AE_JOURNAL_ENTRY_PKG.g_cache_event.target_ledger_id
AND (g_rec_header_new.array_previous_party_id(g_header_idx) IS NULL
OR
xal.party_id = g_rec_header_new.array_previous_party_id(g_header_idx))
AND (g_rec_header_new.array_previous_party_site_id(g_header_idx) IS NULL
OR
xal.party_site_id = g_rec_header_new.array_previous_party_site_id(g_header_idx))
AND (g_rec_header_new.array_party_change_type(g_header_idx) IS NULL
OR
xal.party_type_code = g_rec_header_new.array_party_change_type(g_header_idx))
;
(p_msg => '# temporary journal lines inserted into GT xla_ae_lines_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
(p_msg => 'SQL - Insert into xla_ae_lines_gt '
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
INSERT INTO xla_ae_lines_gt
(
ae_header_id
, temp_line_num
, event_id
--
, ref_ae_header_id
, ref_ae_line_num
, ref_temp_line_num
--
, accounting_class_code
, event_class_code
, event_type_code
, line_definition_owner_code
, line_definition_code
, accounting_line_type_code
, accounting_line_code
--
, code_combination_status_code
, code_combination_id
, sl_coa_mapping_name
, dynamic_insert_flag
, source_coa_id
, ccid_coa_id
--
, description
, gl_transfer_mode_code
, merge_duplicate_code
, switch_side_flag
--
--, entered_amount
--, ledger_amount
, unrounded_entered_dr
, unrounded_entered_cr
, unrounded_accounted_dr
, unrounded_accounted_cr
, entered_dr
, entered_cr
, accounted_dr
, accounted_cr
, currency_code
, currency_conversion_date
, currency_conversion_rate
, currency_conversion_type
, statistical_amount
--
, party_id
, party_site_id
, party_type_code
--
, ussgl_transaction_code
, jgzz_recon_ref
--
, source_distribution_id_char_1
, source_distribution_id_char_2
, source_distribution_id_char_3
, source_distribution_id_char_4
, source_distribution_id_char_5
, source_distribution_id_num_1
, source_distribution_id_num_2
, source_distribution_id_num_3
, source_distribution_id_num_4
, source_distribution_id_num_5
, source_distribution_type
--
, tax_line_ref_id
, tax_summary_line_ref_id
, tax_rec_nrec_dist_ref_id
, inherit_desc_flag -- 4219869
, mpa_accrual_entry_flag -- 4262811
, encumbrance_type_id -- 4458381 Public Sector Enh
, header_num -- 5100860 assign value to avoid using function index
)
SELECT
CASE xah.balance_type_code
WHEN C_ACTUAL THEN g_rec_header_new.array_actual_header_id(g_header_idx)
WHEN C_BUDGET THEN g_rec_header_new.array_budget_header_id(g_header_idx)
WHEN C_ENCUMBRANCE THEN g_rec_header_new.array_encumb_header_id(g_header_idx)
END
, XLA_AE_LINES_PKG.SetLineNum(xah.balance_type_code)
, XLA_AE_JOURNAL_ENTRY_PKG.g_cache_event.event_id
--
, xal.ae_header_id
, xal.ae_line_num
, xdl.temp_line_num
--
, xal.accounting_class_code
, xdl.event_class_code
, xdl.event_type_code
, xdl.line_definition_owner_code
, xdl.line_definition_code
, xdl.accounting_line_type_code
, xdl.accounting_line_code
--
, C_CCID
, xal.code_combination_id
, NULL
, NULL
, NULL
, NULL
, xal.description
, xal.gl_transfer_mode_code
, xdl.merge_duplicate_code
, DECODE(XLA_AE_JOURNAL_ENTRY_PKG.g_cache_ledgers_info.ledger_reversal_option,
'SIDE', C_SWITCH,
C_NO_SWITCH
)
-- , xdl.entered_amount
-- , xdl.ledger_amount
--
, xal.unrounded_entered_dr
, xal.unrounded_entered_cr
, xal.unrounded_accounted_dr
, xal.unrounded_accounted_cr
, xal.entered_dr
, xal.entered_cr
, xal.accounted_dr
, xal.accounted_cr
--
, xal.currency_code
, xal.currency_conversion_date
, xal.currency_conversion_rate
, xal.currency_conversion_type
, xal.statistical_amount
--
, g_rec_header_new.array_new_party_id(g_header_idx)
, g_rec_header_new.array_new_party_site_id(g_header_idx)
, g_rec_header_new.array_party_change_type(g_header_idx)
--
, xal.ussgl_transaction_code
, xal.jgzz_recon_ref
--
, xdl.source_distribution_id_char_1
, xdl.source_distribution_id_char_2
, xdl.source_distribution_id_char_3
, xdl.source_distribution_id_char_4
, xdl.source_distribution_id_char_5
, xdl.source_distribution_id_num_1
, xdl.source_distribution_id_num_2
, xdl.source_distribution_id_num_3
, xdl.source_distribution_id_num_4
, xdl.source_distribution_id_num_5
, xdl.source_distribution_type
--
, xdl.tax_line_ref_id
, xdl.tax_summary_line_ref_id
, xdl.tax_rec_nrec_dist_ref_id
, 'N' -- 4219869 inherit_desc_flag
, 'N' -- 4262811 mpa_accrual_entry_flag
, xal.encumbrance_type_id -- 4458381 Public Sector Enh
, 0 -- 5100860 assign value to avoid using function index
--
FROM xla_ae_lines xal,
xla_ae_headers xah,
xla_distribution_links xdl,
xla_events xe
WHERE xe.event_id = xdl.event_id
AND xe.event_id = xah.event_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xe.entity_id = XLA_AE_JOURNAL_ENTRY_PKG.g_cache_event.entity_id
AND xah.ledger_id = XLA_AE_JOURNAL_ENTRY_PKG.g_cache_event.target_ledger_id
AND ( g_rec_header_new.array_previous_party_id(g_header_idx) IS NULL
OR
xal.party_id = g_rec_header_new.array_previous_party_id(g_header_idx))
AND ( g_rec_header_new.array_previous_party_site_id(g_header_idx) IS NULL
OR
xal.party_site_id = g_rec_header_new.array_previous_party_site_id(g_header_idx))
AND ( g_rec_header_new.array_party_change_type(g_header_idx) IS NULL
OR
xal.party_type_code = g_rec_header_new.array_party_change_type(g_header_idx))
;
(p_msg => '# temporary journal lines inserted into GT xla_ae_lines_gt = '||SQL%ROWCOUNT
,p_level => C_LEVEL_EVENT
,p_module => l_log_module);
(p_msg => 'SQL - update xla_ae_headers_gt'
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
UPDATE xla_ae_headers_gt xah
SET accounting_date =
DECODE(xah.acc_rev_gl_date_option,
'XLA_NEXT_DAY',
(SELECT min(transaction_date)
FROM gl_transaction_dates d
WHERE d.business_day_flag = 'Y'
AND d.transaction_date >= xah.accounting_date
AND d.transaction_calendar_id = l_transaction_calendar_id),
-- XLA_FIRST_DAY_NEXT_GL_PERIOD, XLA_LAST_DAY_NEXT_GL_PERIOD, NONE
(SELECT transaction_date
FROM (SELECT xah2.ae_header_id
, d.transaction_date
FROM xla_ae_headers_gt xah2
, gl_transaction_dates d
WHERE d.transaction_calendar_id = l_transaction_calendar_id
AND d.business_day_flag = 'Y'
AND d.transaction_date >= xah2.period_start_date
ORDER by CASE WHEN d.transaction_date = xah2.accounting_date
THEN 0
WHEN d.transaction_date < xah2.accounting_date
THEN xah2.accounting_date - d.transaction_date
WHEN d.transaction_date > xah2.accounting_date
THEN 1000 + d.transaction_date - xah2.accounting_date
END ) tmp
WHERE ROWNUM = 1
AND xah.ae_header_id = tmp.ae_header_id));
(p_msg => '# rows updated in xla_ae_headers_gt(1.2) ='||SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);