[Home] [Help]
PACKAGE BODY: APPS.ZX_GL_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_GL_EXTRACT_PKG AS
2 /* $Header: zxrigextractpvtb.pls 120.39.12020000.2 2012/07/23 13:03:57 srajapar noship $ */
3
4 -----------------------------------------
5 --Private Variable Declarations
6 -----------------------------------------
7 --PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
8 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9 L_MSG VARCHAR2(500);
10
11 C_LINES_PER_INSERT CONSTANT NUMBER := 1000;
12
13 TYPE pr_tax_rec_type is RECORD(
14 edr number,
15 ecr number,
16 eam number,
17 adr number,
18 acr number,
19 aam number,
20 tax_group_id number,
21 ETAXABLEAM number
22 );
23
24 /*
25 function prorated_tax(
26 p_trx_id in number,
27 p_ledger_id in number,
28 p_doc_seq_id in number,
29 p_tax_code_id in number,
30 p_code_comb_id in number,
31 p_tax_doc_date in date,
32 p_tax_class in varchar2,
33 p_tax_doc_identifier in varchar2,
34 p_tax_cust_name in varchar2,
35 p_tax_cust_reference in varchar2,
36 p_tax_reg_number in varchar2,
37 p_seq_name in varchar2,
38 p_column_name in varchar2);
39 */
40 pr_tax_rec pr_tax_rec_type := NULL;
41
42 -----------------------------------------
43 -- Public Variable Declarations
44 -----------------------------------------
45 -- New variables Declaration
46 GT_APPLICATION_ID ZX_EXTRACT_PKG.APPLICATION_ID_TBL;
47 GT_TRX_CLASS_MNG ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
48 GT_TRX_CLASS ZX_EXTRACT_PKG.TRX_LINE_CLASS_TBL;
49 gt_detail_tax_line_id ZX_EXTRACT_PKG.detail_tax_line_id_tbl;
50 gt_ledger_id zx_extract_pkg.ledger_id_tbl;
51 gt_trx_id zx_extract_pkg.trx_id_tbl;
52 gt_doc_seq_id zx_extract_pkg.doc_seq_id_tbl;
53 gt_doc_seq_value zx_extract_pkg.doc_seq_value_tbl;
54 gt_doc_seq_name zx_extract_pkg.doc_seq_name_tbl;
55 gt_tax_rate_id zx_extract_pkg.tax_rate_id_tbl;
56 gt_tax_line_id zx_extract_pkg.tax_line_id_tbl;
57 gt_tax_amt zx_extract_pkg.tax_amt_tbl;
58 gt_tax_amt_funcl_curr zx_extract_pkg.tax_amt_funcl_curr_tbl;
59 gt_tax_line_number zx_extract_pkg.tax_line_number_tbl;
60 gt_taxable_amt zx_extract_pkg.taxable_amt_tbl;
61 gt_taxable_amt_funcl_curr zx_extract_pkg.taxable_amt_funcl_curr_tbl;
62 --gt_checkwithkripa zx_extract_pkg.checkwithkripa_tbl;
63 --gt_xla_code_combination_id zx_extract_pkg.xla_code_combination_id_tbl;
64 gt_trx_line_quantity zx_extract_pkg.trx_line_quantity_tbl;
65 --gt_xla_period_name zx_extract_pkg.xla_period_name_tbl;
66 gt_trx_number zx_extract_pkg.trx_number_tbl;
67 gt_trx_description zx_extract_pkg.trx_description_tbl;
68 gt_trx_currency_code zx_extract_pkg.trx_currency_code_tbl;
69 gt_trx_date zx_extract_pkg.trx_date_tbl;
70 gt_trx_communicated_date zx_extract_pkg.trx_communicated_date_tbl;
71 gt_currency_conversion_type zx_extract_pkg.currency_conversion_type_tbl;
72 gt_currency_conversion_date zx_extract_pkg.currency_conversion_date_tbl;
73 gt_currency_conversion_rate zx_extract_pkg.currency_conversion_rate_tbl;
74 gt_tax_line_user_category zx_extract_pkg.tax_line_user_category_tbl;
75 gt_tax_line_user_attribute1 zx_extract_pkg.tax_line_user_attribute1_tbl;
76 gt_tax_line_user_attribute2 zx_extract_pkg.tax_line_user_attribute2_tbl;
77 gt_tax_line_user_attribute3 zx_extract_pkg.tax_line_user_attribute3_tbl;
78 gt_tax_line_user_attribute4 zx_extract_pkg.tax_line_user_attribute4_tbl;
79 gt_tax_line_user_attribute5 zx_extract_pkg.tax_line_user_attribute5_tbl;
80 gt_tax_line_user_attribute6 zx_extract_pkg.tax_line_user_attribute6_tbl;
81 gt_tax_line_user_attribute7 zx_extract_pkg.tax_line_user_attribute7_tbl;
82 gt_tax_line_user_attribute8 zx_extract_pkg.tax_line_user_attribute8_tbl;
83 gt_tax_line_user_attribute9 zx_extract_pkg.tax_line_user_attribute9_tbl;
84 gt_tax_line_user_attribute10 zx_extract_pkg.tax_line_user_attribute10_tbl;
85 gt_tax_line_user_attribute11 zx_extract_pkg.tax_line_user_attribute11_tbl;
86 gt_tax_line_user_attribute12 zx_extract_pkg.tax_line_user_attribute12_tbl;
87 gt_tax_line_user_attribute13 zx_extract_pkg.tax_line_user_attribute13_tbl;
88 gt_tax_line_user_attribute14 zx_extract_pkg.tax_line_user_attribute14_tbl;
89 gt_tax_line_user_attribute15 zx_extract_pkg.tax_line_user_attribute15_tbl;
90 gt_billing_tp_name zx_extract_pkg.billing_tp_name_tbl;
91 gt_billing_tp_number zx_extract_pkg.billing_tp_number_tbl;
92 gt_billing_tp_tax_reg_num zx_extract_pkg.billing_tp_tax_reg_num_tbl;
93 gt_posted_flag zx_extract_pkg.posted_flag_tbl;
94 gt_tax_rate_code zx_extract_pkg.tax_rate_code_tbl;
95 gt_tax_rate_code_description zx_extract_pkg.tax_rate_code_description_tbl;
96 gt_tax_rate zx_extract_pkg.tax_rate_tbl;
97 gt_tax_rate_vat_trx_type_code zx_extract_pkg.tax_rate_vat_trx_type_code_tbl;
98 gt_tax_type_code zx_extract_pkg.tax_type_code_tbl;
99 gt_tax_rate_code_name zx_extract_pkg.tax_rate_code_name_tbl;
100 gt_tax_rate_reg_type_code zx_extract_pkg.tax_rate_reg_type_code_tbl;
101 gt_tax_regime_code zx_extract_pkg.tax_regime_code_tbl;
102 gt_tax zx_extract_pkg.tax_tbl;
103 gt_tax_jurisdiction_code zx_extract_pkg.tax_jurisdiction_code_tbl;
104 gt_tax_status_code zx_extract_pkg.tax_status_code_tbl;
105 gt_tax_currency_code zx_extract_pkg.tax_currency_code_tbl;
106 gt_tax_amt_tax_curr zx_extract_pkg.tax_amt_tax_curr_tbl;
107 gt_taxable_amt_tax_curr zx_extract_pkg.taxable_amt_tax_curr_tbl;
108 gt_orig_taxable_amt zx_extract_pkg.orig_taxable_amt_tbl;
109 gt_orig_taxable_amt_tax_curr zx_extract_pkg.orig_taxable_amt_tax_curr_tbl;
110 gt_orig_tax_amt zx_extract_pkg.orig_tax_amt_tbl;
111 gt_orig_tax_amt_tax_curr zx_extract_pkg.orig_tax_amt_tax_curr_tbl;
112 gt_precision zx_extract_pkg.precision_tbl;
113 gt_minimum_accountable_unit zx_extract_pkg.minimum_accountable_unit_tbl;
114 gt_functional_currency_code zx_extract_pkg.functional_currency_code_tbl;
115 gt_trx_line_id zx_extract_pkg.trx_line_id_tbl;
116 gt_trx_line_number zx_extract_pkg.trx_line_number_tbl;
117 gt_trx_line_description zx_extract_pkg.trx_line_description_tbl;
118 gt_trx_line_type zx_extract_pkg.trx_line_type_tbl;
119 gt_establishment_id zx_extract_pkg.establishment_id_tbl;
120 gt_internal_organization_id zx_extract_pkg.internal_organization_id_tbl;
121 gt_ledger_name zx_extract_pkg.ledger_name_tbl;
122 gt_extract_source_ledger zx_extract_pkg.extract_source_ledger_tbl;
123 gt_doc_event_status zx_extract_pkg.doc_event_status_tbl;
124 gt_sub_ledger_inv_identifier zx_extract_pkg.sub_ledger_inv_identifier_tbl;
125 GT_TAX_RATE_VAT_TRX_TYPE_DESC ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_DESC_TBL;
126 GT_TAX_RATE_VAT_TRX_TYPE_MNG ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_MNG_TBL;
127 GT_TAX_TYPE_MNG ZX_EXTRACT_PKG.TAX_TYPE_MNG_TBL;
128 GT_TAX_REG_NUM ZX_EXTRACT_PKG.HQ_ESTB_REG_NUMBER_TBL;
129 gt_trx_arap_balancing_segment zx_extract_pkg.trx_arap_balancing_seg_tbl;
130 gt_trx_arap_natural_account zx_extract_pkg.trx_arap_natural_account_tbl;
131 gt_trx_taxable_bal_seg zx_extract_pkg.trx_taxable_balancing_seg_tbl;
132 gt_trx_taxable_natural_account zx_extract_pkg.trx_taxable_natural_acct_tbl;
133 gt_trx_tax_balancing_segment zx_extract_pkg.trx_tax_balancing_seg_tbl;
134 gt_trx_tax_natural_account zx_extract_pkg.trx_tax_natural_account_tbl;
135 gt_period_name zx_extract_pkg.period_name_tbl;
136 gt_actg_line_ccid zx_extract_pkg.actg_line_ccid_tbl;
137 gt_account_flexfield zx_extract_pkg.account_flexfield_tbl;
138 gt_account_description zx_extract_pkg.account_description_tbl;
139 gt_actg_ext_line_id zx_extract_pkg.actg_ext_line_id_tbl;
140 gt_accounting_date zx_extract_pkg.accounting_date_tbl;
141
142 TYPE TRX_TAXABLE_ACCOUNT_DESC_tbl IS TABLE OF
143 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_ACCOUNT_DESC%TYPE INDEX BY BINARY_INTEGER;
144
145 TYPE TRX_TAXABLE_BALSEG_DESC_tbl IS TABLE OF
146 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_BALSEG_DESC%TYPE INDEX BY BINARY_INTEGER;
147
148 TYPE TRX_TAXABLE_NATACCT_DESC_tbl IS TABLE OF
149 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_NATACCT_SEG_DESC%TYPE INDEX BY BINARY_INTEGER;
150
151 TYPE TRX_CONTROL_ACCTFLEXFIELD_tbl IS TABLE OF
152 ZX_REP_ACTG_EXT_T.TRX_CONTROL_ACCOUNT_FLEXFIELD%TYPE INDEX BY BINARY_INTEGER;
153
154 GT_TRX_CONTROL_ACCFLEXFIELD TRX_CONTROL_ACCTFLEXFIELD_tbl ;
155 GT_TRX_TAXABLE_ACCOUNT_DESC TRX_TAXABLE_ACCOUNT_DESC_tbl ;
156
157 GT_TRX_TAXABLE_BALSEG_DESC TRX_TAXABLE_BALSEG_DESC_TBL ;
158 GT_TRX_TAXABLE_NATACCT_DESC TRX_TAXABLE_NATACCT_DESC_tbl ;
159
160
161 g_created_by number(15);
162 g_creation_date date;
163 g_last_updated_by number(15);
164 g_last_update_date date;
165 g_last_update_login number(15);
166 g_program_application_id number;
167 g_program_id number;
168 g_program_login_id number;
169
170
171 G_TAX_CLASS VARCHAR2(30);
172 G_SUMMARY_LEVEL VARCHAR2(30);
173 G_GL_DATE_LOW DATE;
174 G_GL_DATE_HIGH DATE;
175 G_TRX_DATE_LOW DATE;
176 G_TRX_DATE_HIGH DATE;
177 G_TAX_CODE_LOW VARCHAR2(30);
178 G_TAX_CODE_HIGH VARCHAR2(30);
179 G_CURRENCY_CODE_LOW VARCHAR2(15);
180 G_CURRENCY_CODE_HIGH VARCHAR2(15);
181 G_POSTING_STATUS VARCHAR2(30); -- 5336803
182 G_TRX_NUMBER VARCHAR2(30);
183 G_TRX_CLASS VARCHAR2(30);
184 G_GBL_TAX_DATE_LOW DATE;
185 G_GBL_TAX_DATE_HIGH DATE;
186 G_TAX_CODE_VAT_TRX_TYPE_LOW VARCHAR2(60);
187 G_TAX_CODE_VAT_TRX_TYPE_HIGH VARCHAR2(60);
188 G_TAX_CODE_TYPE_LOW VARCHAR2(60);
189 G_TAX_CODE_TYPE_HIGH VARCHAR2(60);
190 G_TRADING_PARTNER_TAX_REG_NUM VARCHAR2(60);
191 G_TRADING_PARTNER_TAXPAYER_ID VARCHAR2(30);
192 G_BALANCING_SEGMENT_LOW VARCHAR2(30); --5336803
193 G_BALANCING_SEGMENT_HIGH VARCHAR2(30); --5336803
194 G_REQUEST_ID NUMBER;
195 g_legal_entity_id NUMBER;
196 G_GDF_GL_JE_LINES_CATEGORY VARCHAR2(150);
197 G_GDF_GL_JE_LINES_ATT3 VARCHAR2(150);
198 G_GDF_GL_JE_LINES_ATT3_IS_NULL VARCHAR2(30); --5336803
199 G_INCLUDE_GL_MANUAL_LINES VARCHAR2(30);--5336803
200 G_CHART_OF_ACCOUNTS_ID NUMBER(15);
201 G_REP_CONTEXT_ID NUMBER;
202 G_LEDGER_ID NUMBER;
203 g_ledger_name varchar2(30);
204 g_tax_register_type_mng VARCHAR2(80);
205 g_fun_currency_code varchar2(15);
206 G_EXTRACT_LINE_NUM NUMBER := 1;
207 g_tax_jurisdiction_code VARCHAR2(30);
208 --g_first_party_tax_reg_num VARCHAR2(30);
209 G_TAX_REGIME_CODE VARCHAR2(30);
210 G_TAX VARCHAR2(30);
211 G_TAX_STATUS_CODE VARCHAR2(30);
212 G_TAX_RATE_CODE_LOW VARCHAR2(30);
213 G_TAX_RATE_CODE_HIGH VARCHAR2(30);
214 G_TAX_TYPE_CODE_LOW VARCHAR2(30);
215 G_TAX_TYPE_CODE_HIGH VARCHAR2(30);
216 G_TAX_INVOICE_DATE_LOW VARCHAR2(30);
217 G_TAX_INVOICE_DATE_HIGH VARCHAR2(30);
218 G_VAT_TRANSACTION_TYPE_CODE varchar2(30);
219 G_GL_RETCODE NUMBER :=0;
220 G_TRX_NUMBER_LOW VARCHAR2(30);
221 G_TRX_NUMBER_HIGH VARCHAR2(30);
222 L_COLUMN_LIST_GL VARCHAR2(8000);
223 L_TABLE_LIST_GL VARCHAR2(4000);
224 L_WHERE_CLAUSE_GL VARCHAR2(4000);
225
226 C_LINES_PER_COMMIT CONSTANT NUMBER := 5000;
227
228 g_current_runtime_level NUMBER;
229 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
230 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
231 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
232 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
233 g_error_buffer VARCHAR2(100);
234
235 G_INCLUDE_ACCOUNTING_SEGMENTS VARCHAR2(1);
236 G_GL_OR_TRX_DATE_FILTER VARCHAR2(1);--BugFix5347188
237
238 -----------------------------------------
239 --Private Methods Declarations
240 -----------------------------------------
241 PROCEDURE INSERT_GL_SUB_ITF;
242 PROCEDURE FETCH_GL_TRX_INFO;
243 PROCEDURE ASSIGN_GL_GLOBAL_VARIABLES (
244 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
245 );
246
247 PROCEDURE BUILD_SQL;
248
249 PROCEDURE INIT_GL_GT_TABLES;
250
251 PROCEDURE populate_tax_reg_num(
252 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
253 P_TAX IN zx_rates_vl.tax%TYPE,
254 P_TAX_REGIME_CODE IN zx_rates_vl.tax_regime_code%TYPE,
255 P_TAX_JURISDICTION_CODE IN zx_rates_vl.tax_jurisdiction_code%TYPE,
256 i IN BINARY_INTEGER);
257
258 PROCEDURE GET_ACCOUNTING_SEGMENTS (
259 P_TRX_ID IN NUMBER,
260 P_TRX_LINE_ID IN NUMBER,
261 P_TAX_CODE_ID IN NUMBER,
262 P_BALANCING_SEGMENT IN VARCHAR2,
263 P_ACCOUNTING_SEGMENT IN VARCHAR2,
264 P_CHART_OF_ACCOUNTS_ID IN NUMBER,
265 P_TRX_ARAP_BALANCING_SEGMENT OUT NOCOPY VARCHAR2,
266 P_TRX_ARAP_NATURAL_ACCOUNT OUT NOCOPY VARCHAR2,
267 P_TRX_TAXABLE_BAL_SEG OUT NOCOPY VARCHAR2,
268 P_TRX_TAXABLE_NATURAL_ACCOUNT OUT NOCOPY VARCHAR2,
269 P_TRX_TAX_BALANCING_SEGMENT OUT NOCOPY VARCHAR2,
270 P_TRX_TAX_NATURAL_ACCOUNT OUT NOCOPY VARCHAR2,
271 P_TRX_TAXABLE_BALSEG_DESC OUT NOCOPY VARCHAR2,
272 P_TRX_TAXABLE_NATACCT_DESC OUT NOCOPY varchar2,
273 P_TRX_CONTROL_ACCFLEXFIELD OUT NOCOPY varchar2,
274 P_TRX_TAXABLE_ACCOUNT_DESC OUT NOCOPY varchar2
275 );
276
277 /*===========================================================================+
278 | FUNCTION |
279 | ASSIGN_GLOBAL_VARIABLES_GL |
280 | |
281 | DESCRIPTION |
282 | Assign the global variable to the the output parameters. |
283 | This procedure is used by AR procedures to get the global |
284 | variable values from Main package. |
285 | |
286 | SCOPE - Public |
287 | |
288 | NOTES |
289 | |
290 | MODIFICATION HISTORY |
291 | |
292 +===========================================================================*/
293
294 PROCEDURE ASSIGN_GL_GLOBAL_VARIABLES (
295 P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
296 ) IS
297
298 BEGIN
299
300 IF (g_level_procedure >= g_current_runtime_level ) THEN
301 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.ASSIGN_GL_GLOBAL_VARIABLES.BEGIN',
302 'ZX_GL_EXTRACT_PKG: ASSIGN_GL_GLOBAL_VARIABLES(+)');
303 END IF;
304 g_legal_entity_id := P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID;
305 G_REQUEST_ID := P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
306 -- G_CHART_OF_ACCOUNTS_ID := P_TRL_GLOBAL_VARIABLES_REC.CHART_OF_ACCOUNTS_ID;
307 -- G_REP_CONTEXT_ID := P_TRL_GLOBAL_VARIABLES_REC.REP_CONTEXT_ID;
308 -- G_TAX_CLASS := P_TRL_GLOBAL_VARIABLES_REC.TAX_CLASS;
309 G_SUMMARY_LEVEL := P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL;
310 G_GL_DATE_LOW := P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW;
311 G_GL_DATE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH;
312 G_TRX_DATE_LOW := P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_LOW;
313 G_TRX_DATE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH;
314 G_CURRENCY_CODE_LOW := P_TRL_GLOBAL_VARIABLES_REC.CURRENCY_CODE_LOW;
315 G_CURRENCY_CODE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.CURRENCY_CODE_HIGH;
316 G_POSTING_STATUS := P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS;
317 -- G_TRX_NUMBER := P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER;
318 --G_TRX_CLASS := P_TRL_GLOBAL_VARIABLES_REC.TRX_CLASS;
319 -- G_GBL_TAX_DATE_LOW := P_TRL_GLOBAL_VARIABLES_REC.GBL_TAX_DATE_LOW;
320 -- G_GBL_TAX_DATE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.GBL_TAX_DATE_HIGH;
321 G_VAT_TRANSACTION_TYPE_CODE := P_TRL_GLOBAL_VARIABLES_REC.VAT_TRANSACTION_TYPE_CODE;
322 -- G_TAX_CODE_VAT_TRX_TYPE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.TAX_CODE_VAT_TRX_TYPE_HIGH;
323 -- G_TAX_CODE_TYPE_LOW := P_TRL_GLOBAL_VARIABLES_REC.TAX_CODE_TYPE_LOW;
324 -- G_TAX_CODE_TYPE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.TAX_CODE_TYPE_HIGH;
325 -- G_TRADING_PARTNER_TAX_REG_NUM := P_TRL_GLOBAL_VARIABLES_REC.TRADING_PARTNER_TAX_REG_NUM;
326 -- G_TRADING_PARTNER_TAXPAYER_ID := P_TRL_GLOBAL_VARIABLES_REC.TRADING_PARTNER_TAXPAYER_ID;
327 G_BALANCING_SEGMENT_LOW := P_TRL_GLOBAL_VARIABLES_REC.BALANCING_SEGMENT_LOW;
328 G_BALANCING_SEGMENT_HIGH := P_TRL_GLOBAL_VARIABLES_REC.BALANCING_SEGMENT_HIGH;
329 G_LEDGER_ID := NVL(P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEDGER_ID, P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID);
330 G_INCLUDE_GL_MANUAL_LINES := P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_GL_MANUAL_LINES;
331 G_GDF_GL_JE_LINES_CATEGORY := P_TRL_GLOBAL_VARIABLES_REC.GDF_GL_JE_LINES_CATEGORY;
332 G_GDF_GL_JE_LINES_ATT3 := P_TRL_GLOBAL_VARIABLES_REC.GDF_GL_JE_LINES_ATT3;
333 G_GDF_GL_JE_LINES_ATT3_IS_NULL := P_TRL_GLOBAL_VARIABLES_REC.GDF_GL_JE_LINES_ATT3_IS_NULL;
334 -- G_EXTRACT_LINE_NUM := P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_LINE_NUM;
335 g_tax_jurisdiction_code := p_trl_global_variables_rec.tax_jurisdiction_code;
336 --g_first_party_tax_reg_num := p_trl_global_variables_rec.first_party_tax_reg_num;
337 G_TAX_REGIME_CODE := P_TRL_GLOBAL_VARIABLES_REC.TAX_REGIME_CODE;
338 G_TAX := P_TRL_GLOBAL_VARIABLES_REC.TAX;
339 G_TAX_STATUS_CODE := P_TRL_GLOBAL_VARIABLES_REC.TAX_STATUS_CODE;
340 G_TAX_RATE_CODE_LOW := p_trl_global_variables_rec.tax_rate_code_low;
341 G_TAX_RATE_CODE_HIGH := p_trl_global_variables_rec.tax_rate_code_high;
342 G_TAX_TYPE_CODE_LOW := p_trl_global_variables_rec.tax_type_code_low;
343 G_TAX_TYPE_CODE_HIGH := p_trl_global_variables_rec.tax_type_code_high;
344 --G_TAX_RATE_CODE := P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE;
345 --G_TAX_TYPE_CODE := P_TRL_GLOBAL_VARIABLES_REC.TAX_TYPE_CODE;
346 G_TAX_INVOICE_DATE_LOW := P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_LOW;
347 G_TAX_INVOICE_DATE_HIGH := P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH;
348 G_TRX_NUMBER_LOW := P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_LOW;
349 G_TRX_NUMBER_HIGH := P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_HIGH;
350 G_LEDGER_NAME := P_TRL_GLOBAL_VARIABLES_REC.LEDGER_NAME;
351 G_FUN_CURRENCY_CODE := P_TRL_GLOBAL_VARIABLES_REC.FUNC_CURRENCY_CODE;
352 g_tax_register_type_mng := 'Tax Register';
353 g_include_accounting_segments := p_trl_global_variables_rec.include_accounting_segments;
354 G_GL_OR_TRX_DATE_FILTER := P_TRL_GLOBAL_VARIABLES_REC.GL_OR_TRX_DATE_FILTER;--BugFix:5347188
355
356
357 IF (g_level_procedure >= g_current_runtime_level ) THEN
358 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.ASSIGN_GL_GLOBAL_VARIABLES.END',
359 'ZX_GL_EXTRACT_PKG: ASSIGN_GL_GLOBAL_VARIABLES(-)');
360 END IF;
361
362 END ASSIGN_GL_GLOBAL_VARIABLES;
363
364
365 /*===========================================================================+
366 | PROCEDURE |
367 | INSERT_TAX_DATA |
368 | |
369 | DESCRIPTION |
370 | This procedure takes the input parameters from ZX_EXTRACT_PKG |
371 | and builds dynamic SQL statement clauses based on the parameters, |
372 | supplies them as output parameters. |
373 | |
374 | SCOPE - Public |
375 | |
376 | NOTES |
377 | |
378 | MODIFICATION HISTORY |
379 | 18-Aug-99 Nilesh Patel, created |
380 | |
381 +===========================================================================*/
382
383 PROCEDURE INSERT_TAX_DATA (
384 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
385 ) IS
386 BEGIN
387
388 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
389
390 IF (g_level_procedure >= g_current_runtime_level ) THEN
391 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_TAX_DATA.BEGIN',
392 'ZX_GL_EXTRACT_PKG: INSERT_TAX_DATA(+)');
393 END IF;
394
395 ASSIGN_GL_GLOBAL_VARIABLES(
396 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
397 );
398
399 -- IF G_GL_RETCODE <> 2 THEN
400 BUILD_SQL;
401 -- END IF;
402 -- IF G_GL_RETCODE <> 2 THEN
403 FETCH_GL_TRX_INFO;
404 -- END IF;
405
406 IF (g_level_procedure >= g_current_runtime_level ) THEN
407 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_TAX_DATA.END',
408 'ZX_GL_EXTRACT_PKG: INSERT_TAX_DATA(+)');
409 END IF;
410
411 -- assign the output global variable
412 -- P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_LINE_NUM := G_EXTRACT_LINE_NUM;
413 -- P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
414
415 END INSERT_TAX_DATA;
416
417 /*===========================================================================+
418 | PROCEDURE |
419 | build_sql |
420 | |
421 | DESCRIPTION |
422 | This procedure takes the input parameters from ZX_EXTRACT_PKG |
423 | and builds dynamic SQL statement clauses based on the parameters, |
424 | supplies them as output parameters. |
425 | |
426 | SCOPE - Public |
427 | |
428 | NOTES |
429 | |
430 | MODIFICATION HISTORY |
431 | 18-Aug-99 Nilesh Patel, created |
432 | |
433 +===========================================================================*/
434
435
436 PROCEDURE BUILD_SQL
437 IS
438
439 L_WHERE_GBL_TAX_DATE varchar2(200);
440 L_WHERE_GL_DATE varchar2(200);
441 L_WHERE_TRX_DATE varchar2(200);
442 L_WHERE_GL_OR_TRX_DATE varchar2(250);
443 L_WHERE_TAX_CODE varchar2(200);
444 --L_WHERE_CURRENCY_CODE varchar2(200);
445 L_WHERE_POSTING_STATUS varchar2(200);
446 L_WHERE_TAX_CODE_TYPE varchar2(200);
447 L_WHERE_CHART_OF_ACCOUNTS_ID varchar2(200);
448 L_WHERE_LEDGER_ID VARCHAR2(200);
449 L_CHART_OF_ACCOUNTS_ID number(15);
450 L_WHERE_TRX_CLASS_GL varchar2(200);
451 L_WHERE_TAX_CODE_VAT_TRX_TYPE varchar2(200);
452 L_WHERE_TP_TAX_REG_NUM varchar2(200);
453 L_WHERE_TP_TAXPAYER_ID varchar2(200);
454 --L_WHERE_GL_FLEX varchar2(2000);
455 L_WHERE_TAX_CLASS VARCHAR2(240);
456 L_WHERE_TRX_NUMBER_GL VARCHAR2(240);
457 L_WHERE_GL_LINES_ATT3_IS_NULL VARCHAR2(500);
458 L_WHERE_TAX_JURISDICTION_CODE varchar2(1000);
459 --L_WHERE_FIRST_PTY_TAX_REG_NUM varchar2(1000);
460 L_WHERE_TAX_REGIME_CODE varchar2(500);
461 L_WHERE_TAX varchar2(500);
462 L_WHERE_TAX_STATUS_CODE varchar2(500);
463 L_WHERE_TAX_RATE_CODE varchar2(500);
464 L_WHERE_TAX_TYPE_CODE varchar2(500);
465 L_WHERE_CURRENCY_CODE varchar2(500);
466 L_WHERE_TAX_INVOICE_DATE varchar2(500);
467 --L_WHERE_GL_FLEX_LE VARCHAR2(2000);
468 l_bal_value boolean;
469 l_bsv_flag varchar2(1);
470 l_bsv_list gl_mc_info.le_bsv_tbl_type;
471 l_bsv_in varchar2(300);
472 l_bsv varchar2(100);
473 l_count number;
474 BEGIN
475
476
477 IF (g_level_procedure >= g_current_runtime_level ) THEN
478 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL.BEGIN',
479 'ZX_GL_EXTRACT_PKG: BUILD_SQL(+)');
480 END IF;
481
482 -- New Where Clause Predicates :
483
484
485 --BugFix:5347188
486 IF G_GL_DATE_LOW IS NOT NULL AND G_GL_DATE_HIGH IS NOT NULL THEN
487 L_WHERE_GL_DATE :=
488 ' GJH.DEFAULT_EFFECTIVE_DATE BETWEEN :G_GL_DATE_LOW AND :G_GL_DATE_HIGH ';
489 ELSIF G_GL_DATE_LOW IS NULL AND G_GL_DATE_HIGH IS NULL THEN
490 L_WHERE_GL_DATE := ' DECODE(:G_GL_DATE_LOW,NULL,NULL) IS NULL AND decode(:G_GL_DATE_HIGH,NULL,NULL) IS NULL ';
491 ELSIF G_GL_DATE_LOW IS NULL AND G_GL_DATE_HIGH IS NOT NULL THEN
492 L_WHERE_GL_DATE := ' DECODE(:G_GL_DATE_LOW, NULL,NULL) IS NULL AND GJH.DEFAULT_EFFECTIVE_DATE <= :G_GL_DATE_HIGH ';
493 ELSE
494 L_WHERE_GL_DATE := ' GJH.DEFAULT_EFFECTIVE_DATE >= :G_GL_DATE_LOW AND DECODE(:G_GL_DATE_HIGH, NULL,NULL) IS NULL ';
495 END IF;
496
497
498 --BugFix:5347188
499 IF G_TRX_DATE_LOW IS NOT NULL AND G_TRX_DATE_HIGH IS NOT NULL THEN
500 L_WHERE_TRX_DATE :=
501 ' GJL1.TAX_DOCUMENT_DATE BETWEEN :G_TRX_DATE_LOW AND :G_TRX_DATE_HIGH ';
502
503 ELSIF G_TRX_DATE_LOW IS NULL AND G_TRX_DATE_HIGH IS NULL THEN
504 L_WHERE_TRX_DATE := ' DECODE(:G_TRX_DATE_LOW,NULL,NULL) IS NULL AND DECODE(:G_TRX_DATE_HIGH,NULL,NULL) IS NULL ';
505 ELSIF G_TRX_DATE_LOW IS NULL AND G_TRX_DATE_HIGH IS NOT NULL THEN
506 L_WHERE_TRX_DATE := ' DECODE(:G_TRX_DATE_LOW,NULL,NULL) IS NULL AND GJL1.TAX_DOCUMENT_DATE <= :G_TRX_DATE_HIGH ';
507 ELSE
508 L_WHERE_TRX_DATE := ' GJL1.TAX_DOCUMENT_DATE >= :G_TRX_DATE_LOW AND DECODE(:G_TRX_DATE_HIGH,NULL,NULL) IS NULL ';
509 END IF;
510
511 --BugFix:5347188
512 IF G_GL_OR_TRX_DATE_FILTER = 'Y' THEN
513 L_WHERE_GL_OR_TRX_DATE := ' AND ( ( '||L_WHERE_GL_DATE||' ) OR ('||L_WHERE_TRX_DATE||')) ';
514 L_WHERE_GL_DATE := ' ';
515 L_WHERE_TRX_DATE := ' ';
516 ELSE
517 L_WHERE_GL_DATE := ' AND '||L_WHERE_GL_DATE;
518 L_WHERE_TRX_DATE := ' AND '||L_WHERE_TRX_DATE;
519 L_WHERE_GL_OR_TRX_DATE := ' ';
520 END IF;
521
522
523 -- New Parameters
524 -- IF g_first_party_tax_reg_num IS NOT NULL THEN
525 -- L_WHERE_FIRST_PTY_TAX_REG_NUM := ' AND ptp.rep_registration_number = :g_first_party_tax_reg_num ';
526 -- ELSE
527 -- L_WHERE_FIRST_PTY_TAX_REG_NUM := ' AND DECODE(:g_first_party_tax_reg_num,NULL,NULL) IS NULL ';
528 -- END IF;
529
530 IF g_tax_jurisdiction_code IS NOT NULL THEN
531 L_WHERE_TAX_JURISDICTION_CODE := ' AND ZX_RATE.TAX_JURISDICTION_CODE = :g_tax_jurisdiction_code ';
532 ELSE
533 L_WHERE_TAX_JURISDICTION_CODE := ' AND DECODE(:g_tax_jurisdiction_code,NULL,NULL) IS NULL ';
534 END IF;
535
536 IF G_TAX_REGIME_CODE IS NOT NULL THEN
537 L_WHERE_TAX_REGIME_CODE := ' AND ZX_RATE.TAX_REGIME_CODE = :G_TAX_REGIME_CODE ';
538 ELSE
539 L_WHERE_TAX_REGIME_CODE := ' AND DECODE(:G_TAX_REGIME_CODE,NULL,NULL) IS NULL ';
540 END IF;
541
542 IF G_TAX IS NOT NULL THEN
543 L_WHERE_TAX := ' AND ZX_RATE.TAX = :G_TAX ';
544 ELSE
545 L_WHERE_TAX := ' AND DECODE(:G_TAX,NULL,NULL) IS NULL ';
546 END IF;
547
548 IF G_TAX_STATUS_CODE IS NOT NULL THEN
549 L_WHERE_TAX_STATUS_CODE := ' AND ZX_RATE.TAX_STATUS_CODE = :G_TAX_STATUS_CODE ';
550 ELSE
551 L_WHERE_TAX_STATUS_CODE := ' AND DECODE(:G_TAX_STATUS_CODE,NULL,NULL) IS NULL ';
552 END IF;
553
554 IF g_tax_rate_code_low IS NOT NULL AND g_tax_rate_code_high IS NOT NULL THEN
555 L_WHERE_TAX_RATE_CODE := ' AND ZX_RATE.TAX_RATE_CODE BETWEEN :G_TAX_RATE_CODE_LOW AND :G_TAX_RATE_CODE_HIGH ';
556 ELSE
557 L_WHERE_TAX_RATE_CODE := ' AND DECODE(:G_TAX_RATE_CODE_LOW,NULL,NULL) IS NULL '||
558 ' AND DECODE(:G_TAX_RATE_CODE_HIGH,NULL,NULL) IS NULL ';
559 END IF;
560
561 IF g_tax_type_code_low IS NOT NULL AND g_tax_type_code_high IS NOT NULL THEN
562 --L_WHERE_TAX_TYPE_CODE := ' AND ZX_RATE.TAX_TYPE_CODE BETWEEN :G_TAX_TYPE_CODE_LOW AND :G_TAX_TYPE_CODE_HIGH ';
563 L_WHERE_TAX_TYPE_CODE := ' AND ZX_TAX.TAX_TYPE_CODE BETWEEN :G_TAX_TYPE_CODE_LOW AND :G_TAX_TYPE_CODE_HIGH '; -- bug#7230760
564 ELSE
565 L_WHERE_TAX_TYPE_CODE := ' AND DECODE(:G_TAX_TYPE_CODE_LOW,NULL,NULL) IS NULL '||
566 ' AND DECODE(:G_TAX_TYPE_CODE_HIGH,NULL,NULL) IS NULL ';
567 END IF;
568
569 /* IF G_TAX_RATE_CODE IS NOT NULL THEN
570 L_WHERE_TAX_RATE_CODE := ' AND ZX_RATE.TAX_RATE_CODE = :G_TAX_RATE_CODE ';
571 ELSE
572 L_WHERE_TAX_RATE_CODE := ' AND DECODE(:G_TAX_RATE_CODE,NULL,NULL) IS NULL ';
573 END IF;
574
575 IF G_TAX_TYPE_CODE IS NOT NULL THEN
576 L_WHERE_TAX_TYPE_CODE := ' AND ZX_RATE.TAX_TYPE_CODE = :G_TAX_TYPE_CODE ';
577 ELSE
578 L_WHERE_TAX_TYPE_CODE := ' AND DECODE(:G_TAX_TYPE_CODE,NULL,NULL) IS NULL ';
579 END IF;
580 */
581
582 -- L_WHERE_CURRENCY_CODE
583
584 IF G_CURRENCY_CODE_LOW IS NOT NULL
585 AND G_CURRENCY_CODE_HIGH IS NOT NULL
586 THEN
587 L_WHERE_CURRENCY_CODE := ' AND GJH.CURRENCY_CODE BETWEEN :G_CURRENCY_CODE_LOW AND :G_CURRENCY_CODE_HIGH ';
588 ELSE
589 L_WHERE_CURRENCY_CODE := ' AND DECODE(:G_CURRENCY_CODE_LOW,NULL,NULL) IS NULL AND DECODE(:G_CURRENCY_CODE_HIGH,NULL,NULL) IS NULL ';
590 END IF;
591
592 -- L_WHERE_POSTING_STATUS
593 IF G_POSTING_STATUS = 'POSTED' THEN
594 L_WHERE_POSTING_STATUS := ' AND GJH.POSTED_DATE IS NOT NULL ';
595 ELSIF G_POSTING_STATUS = 'UNPOSTED' THEN
596 L_WHERE_POSTING_STATUS := ' AND GJH.POSTED_DATE IS NULL ';
597 ELSE
598 L_WHERE_POSTING_STATUS := ' AND 1 = 1 ';
599 END IF;
600
601
602 IF G_VAT_TRANSACTION_TYPE_CODE IS NOT NULL
603 THEN
604 L_WHERE_TAX_CODE_VAT_TRX_TYPE :=
605 ' AND ZX_RATE.VAT_TRANSACTION_TYPE_CODE = :G_VAT_TRANSACTION_TYPE_CODE ';
606 ELSE
607 L_WHERE_TAX_CODE_VAT_TRX_TYPE := ' AND DECODE(:G_VAT_TRANSACTION_TYPE_CODE,NULL,NULL) IS NULL ';
608 END IF;
609
610
611 -- New paraneter code
612 IF G_TRX_NUMBER_LOW IS NOT NULL AND G_TRX_NUMBER_HIGH IS NOT NULL THEN
613 L_WHERE_TRX_NUMBER_GL :=
614 'AND GJL1.TAX_DOCUMENT_IDENTIFIER BETWEEN :G_TRX_NUMBER_LOW AND :G_TRX_NUMBER_HIGH ';
615 ELSE
616 L_WHERE_TRX_NUMBER_GL :=
617 ' AND DECODE(:G_TRX_NUMBER_LOW,NULL,NULL) IS NULL AND DECODE(:G_TRX_NUMBER_HIGH,NULL,NULL) IS NULL ';
618 END IF;
619
620 -- L_WHERE_TRADING_PARTNER_TAX_REG_NUM
621
622 IF G_TRADING_PARTNER_TAX_REG_NUM IS NOT NULL
623 THEN
624 L_WHERE_TP_TAX_REG_NUM :=
625 ' AND GJL1.TAX_REGISTRATION_NUMBER = :G_TRADING_PARTNER_TAX_REG_NUM';
626 ELSE
627 L_WHERE_TP_TAX_REG_NUM := ' AND DECODE(:G_TRADING_PARTNER_TAX_REG_NUM,NULL,NULL) IS NULL';
628 END IF;
629 IF PG_DEBUG = 'Y' THEN
630 arp_util_tax.debug('L_WHERE_TP_TAX_REG_NUM = '||
631 L_WHERE_TP_TAX_REG_NUM );
632 END IF;
633
634 -- L_WHERE_TRADING_PARTNER_TAXPAYER_ID
635
636 IF G_TRADING_PARTNER_TAXPAYER_ID IS NOT NULL THEN
637 L_WHERE_TP_TAXPAYER_ID :=
638 ' AND GJL1.TAX_CUSTOMER_REFERENCE = :G_TRADING_PARTNER_TAXPAYER_ID';
639 else
640 L_WHERE_TP_TAXPAYER_ID := ' AND DECODE(:G_TRADING_PARTNER_TAXPAYER_ID,NULL,NULL) IS NULL ';
641 END IF;
642
643 /*Bug Fix 5119565 */
644 IF G_TAX_INVOICE_DATE_LOW IS NOT NULL AND G_TAX_INVOICE_DATE_HIGH IS NOT NULL THEN
645 L_WHERE_TAX_INVOICE_DATE := ' AND gjl1.TAX_DOCUMENT_DATE BETWEEN :G_TAX_INVOICE_DATE_LOW AND :G_TAX_INVOICE_DATE_HIGH ';
646 ELSIF G_TAX_INVOICE_DATE_LOW IS NULL AND G_TAX_INVOICE_DATE_HIGH IS NULL THEN
647 L_WHERE_TAX_INVOICE_DATE := ' AND :G_TAX_INVOICE_DATE_LOW IS NULL AND :G_TAX_INVOICE_DATE_HIGH IS NULL ';
648 ELSIF G_TAX_INVOICE_DATE_LOW IS NOT NULL AND G_TAX_INVOICE_DATE_HIGH IS NULL THEN
649 L_WHERE_TAX_INVOICE_DATE := ' AND gjl1.TAX_DOCUMENT_DATE >= :G_TAX_INVOICE_DATE_LOW AND :G_TAX_INVOICE_DATE_HIGH IS NULL ';
650 ELSE
651 L_WHERE_TAX_INVOICE_DATE := ' AND :G_TAX_INVOICE_DATE_LOW IS NULL AND gjl1.TAX_DOCUMENT_DATE <= :G_TAX_INVOICE_DATE_HIGH ';
652 END IF;
653 IF G_INCLUDE_GL_MANUAL_LINES IS NOT NULL THEN
654 IF G_INCLUDE_GL_MANUAL_LINES = 'N' THEN
655 L_WHERE_TRX_CLASS_GL := ' AND GJH.JE_SOURCE <> ''Manual'' ';
656 ELSE
657 L_WHERE_TRX_CLASS_GL := ' AND 1 = 1 ';
658 END IF;
659 ELSE
660 L_WHERE_TRX_CLASS_GL := 'AND 1 = 1 ';
661 END IF;
662
663 /***
664 -- L_WHERE_GL_FLEX
665 -- Get the SEGMENT_NUMBER of the Balancing Segment of the
666 -- Chart_of_accounts_id associated with the user's set of books.
667 -- Get the chart of accounts id
668
669 -- IF G_BALANCING_SEGMENT_LOW IS NOT NULL AND
670 -- G_BALANCING_SEGMENT_HIGH IS NOT NULL THEN
671 SELECT CHART_OF_ACCOUNTS_ID
672 INTO L_CHART_OF_ACCOUNTS_ID
673 FROM GL_SETS_OF_BOOKS
674 WHERE SET_OF_BOOKS_ID = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
675
676 -- Build the where clause for restricting the data to the
677 -- balancing segments specified.
678 -- L_WHERE_GL_FLEX'
679
680 L_WHERE_GL_FLEX :=
681 FA_RX_FLEX_PKG.FLEX_SQL(
682 P_APPLICATION_ID => 101,
683 P_ID_FLEX_CODE => 'GL#',
684 P_ID_FLEX_NUM => L_CHART_OF_ACCOUNTS_ID,
685 P_TABLE_ALIAS => 'GCC2',
686 P_MODE => 'WHERE',
687 P_QUALIFIER =>'GL_BALANCING',
688 P_FUNCTION => 'BETWEEN',
689 P_OPERAND1 => G_BALANCING_SEGMENT_LOW,
690 P_OPERAND2 => G_BALANCING_SEGMENT_HIGH );
691
692 L_WHERE_GL_FLEX := ' AND '||L_WHERE_GL_FLEX||' ';
693 IF PG_DEBUG = 'Y' THEN
694 arp_util_tax.debug('L_WHERE_GL_FLEX = '||L_WHERE_GL_FLEX);
695 END IF;
696 -- END IF;
697 --AND GCC2.SEGMENT1 BETWEEN '' AND ''
698
699 l_count := 0;
700 IF (g_level_procedure >= g_current_runtime_level ) THEN
701 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
702 'L_WHERE_GL_FLEX = '||L_WHERE_GL_FLEX);
703 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
704 'L_WHERE_GL_FLEX : Call to gl_mc_info.get_bal_seg_values');
705 END IF;
706
707 -- For Legal Entity Level build predicate for blancing segments criteria
708
709 IF g_legal_entity_id IS NOT NULL THEN
710 l_bsv_list := gl_mc_info.le_bsv_tbl_type();
711 l_bal_value :=gl_mc_info.get_bal_seg_values(NULL,g_legal_entity_id,NULL,l_bsv_flag, l_bsv_list);
712 l_count := l_bsv_list.count;
713
714 IF (g_level_procedure >= g_current_runtime_level ) THEN
715 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
716 'L_WHERE_GL_FLEX : After Call to gl_mc_info.get_bal_seg_values'||to_char(l_count)||l_bsv_flag);
717 END IF;
718
719 IF l_count > 0 THEN
720 L_WHERE_GL_FLEX_LE := substr(L_WHERE_GL_FLEX,1,18);
721 L_WHERE_GL_FLEX := L_WHERE_GL_FLEX_LE;
722 L_WHERE_GL_FLEX := L_WHERE_GL_FLEX||' IN '||'('||'''';
723 END If;
724
725 FOR i IN 1..l_count LOOP
726 L_WHERE_GL_FLEX := L_WHERE_GL_FLEX||l_bsv_list(i).bal_seg_value||'''';
727 if i < l_count then
728 L_WHERE_GL_FLEX := L_WHERE_GL_FLEX||',''';
729 end if;
730 -- l_bsv_in := l_bsv_in||'''||l_bsv||''
731 IF (g_level_procedure >= g_current_runtime_level ) THEN
732 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
733 'L_WHERE_GL_FLEX :'||L_WHERE_GL_FLEX);
734 END IF;
735
736 END LOOP;
737
738 L_WHERE_GL_FLEX := L_WHERE_GL_FLEX||' )';
739 IF (g_level_procedure >= g_current_runtime_level ) THEN
740 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
741 'L_WHERE_GL_FLEX : After Call to gl_mc_info.get_bal_seg_values');
742 END IF;
743
744 IF l_bal_value then
745 IF (g_level_procedure >= g_current_runtime_level ) THEN
746 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
747 'L_WHERE_GL_FLEX : bal value ');
748 END IF;
749 END IF;
750 END IF; -- g_legal_entity_id ---
751
752 IF l_count > 0 THEN
753 l_bsv := l_bsv_list(1).bal_seg_value;
754 L_WHERE_GL_FLEX_LE := substr(L_WHERE_GL_FLEX,1,18);
755 L_WHERE_GL_FLEX_LE := L_WHERE_GL_FLEX_LE ||'IN'||l_bsv_in||')';
756 L_WHERE_GL_FLEX := L_WHERE_GL_FLEX_LE ||l_bsv;
757 END If;
758
759 IF (g_level_procedure >= g_current_runtime_level ) THEN
760 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
761 'L_WHERE_GL_FLEX : '||L_WHERE_GL_FLEX);
762 END IF;
763
764 ***/
765 -- Bugfix 3027941
766
767 IF G_GDF_GL_JE_LINES_ATT3_IS_NULL = 'Y' THEN
768 L_WHERE_GL_LINES_ATT3_IS_NULL :=
769 ' AND decode(:G_GDF_GL_JE_LINES_CATEGORY,NULL,NULL) is null and decode (:G_GDF_GL_JE_LINES_ATT3,NULL,NULL) is null and GJL1.GLOBAL_ATTRIBUTE3 IS NULL';
770 ELSIF G_GDF_GL_JE_LINES_ATT3_IS_NULL = 'N' THEN
771 L_WHERE_GL_LINES_ATT3_IS_NULL := ' AND GJL1.GLOBAL_ATTRIBUTE_CATEGORY = :G_GDF_GL_JE_LINES_CATEGORY AND GJL1.GLOBAL_ATTRIBUTE3 = :G_GDF_GL_JE_LINES_ATT3 ';
772
773 ELSE
774 L_WHERE_GL_LINES_ATT3_IS_NULL := ' AND decode(:G_GDF_GL_JE_LINES_CATEGORY,NULL,NULL) is null and decode (:G_GDF_GL_JE_LINES_ATT3,NULL,NULL) is null ';
775 END IF;
776
777 IF PG_DEBUG = 'Y' THEN
778 arp_util_tax.debug('L_WHERE_GL_LINES_ATT3_IS_NULL = ' || L_WHERE_GL_LINES_ATT3_IS_NULL );
779 END IF;
780
781
782
783 IF G_SUMMARY_LEVEL = 'TRANSACTION' THEN
784
785
786 IF (g_level_procedure >= g_current_runtime_level ) THEN
787 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
788 'ZX_GL_EXTRACT_PKG: SQL-1');
789 END IF;
790
791 --New Dyanamic SQL
792 l_column_list_gl :=
793 ' SELECT
794 gjh.ledger_id,
795 gjh.je_header_id,
796 gjh.doc_sequence_id,
797 gjh.doc_sequence_value,
798 seq.name,
799 gjl1.tax_code_id,
800 to_number(NULL), --tax line id
801 decode(gjl1.tax_type_code,''I'',-1,1) *
802 ZX_GL_EXTRACT_PKG.prorated_tax(
803 gjh.je_header_id,
804 gjh.ledger_id,
805 gjh.doc_sequence_id,
806 gjl1.tax_code_id,
807 gjl2.code_combination_id,
808 gjl1.tax_document_date,
809 gjl1.tax_type_code,--zx_rate.tax_class, --Bug 5338150
810 gjl1.tax_document_identifier,
811 gjl1.tax_customer_name,
812 gjl1.tax_customer_reference,
813 gjl1.tax_registration_number,
814 seq.name,
815 ''EAM''),
816 decode(gjl1.tax_type_code,''I'',-1,1) *
817 ZX_GL_EXTRACT_PKG.prorated_tax(
818 gjh.je_header_id,
819 gjh.ledger_id,
820 gjh.doc_sequence_id,
821 gjl1.tax_code_id,
822 gjl2.code_combination_id,
823 gjl1.tax_document_date,
824 gjl1.tax_type_code,--zx_rate.tax_class,--Bug 5338150
825 gjl1.tax_document_identifier,
826 gjl1.tax_customer_name,
827 gjl1.tax_customer_reference,
828 gjl1.tax_registration_number,
829 seq.name,
830 ''AAM''), -- tax accounted amount
831 to_number(NULL), --tax line number
832 (sum(nvl(gjl1.entered_cr,0) - nvl(gjl1.entered_dr,0)))*
833 decode(gjl1.tax_type_code,''I'',-1,1), --taxable entered amount
834 (sum(nvl(gjl1.accounted_cr,0) - nvl(gjl1.accounted_dr,0)))*
835 decode(gjl1.tax_type_code,''I'',-1,1), --taxable accounted amount
836 -- min(gjl1.je_line_num), --acctg_dist_id
837 -- gjl2.code_combination_id,
838 sum(gjl1.stat_amount),'|| --taxable_line_qty
839 -- gjh.period_name,'||
840 'TO_DATE(NULL),'|| --gjl1.tax_document_identifier,
841 'gjh.description,
842 gjh.currency_code,
843 TO_DATE(NULL), --gjl1.tax_document_date,
844 TO_DATE(NULL), --gjl1.tax_document_date,
845 gjh.currency_conversion_type,
846 gjh.currency_conversion_date,
847 gjh.currency_conversion_rate,
848 TO_CHAR(NULL), --gjl1.context,
849 TO_CHAR(NULL), --gjl1.attribute1,
850 TO_CHAR(NULL), --gjl1.attribute2,
851 TO_CHAR(NULL), --gjl1.attribute3,
852 TO_CHAR(NULL), --gjl1.attribute4,
853 TO_CHAR(NULL), --gjl1.attribute5,
854 TO_CHAR(NULL), --gjl1.attribute6,
855 TO_CHAR(NULL), --gjl1.attribute7,
856 TO_CHAR(NULL), --gjl1.attribute8,
857 TO_CHAR(NULL), --gjl1.attribute9,
858 TO_CHAR(NULL), --gjl1.attribute10,
859 TO_CHAR(NULL), --gjl1.attribute11,
860 TO_CHAR(NULL), --gjl1.attribute12,
861 TO_CHAR(NULL), --gjl1.attribute13,
862 TO_CHAR(NULL), --gjl1.attribute14,
863 TO_CHAR(NULL), --gjl1.attribute15,
864 TO_CHAR(NULL), --gjl1.tax_customer_name,
865 TO_CHAR(NULL), --gjl1.tax_customer_reference,
866 TO_CHAR(NULL), --gjl1.tax_registration_number,
867 decode(gjh.posted_date,NULL,''N'',''Y''),
868 zx_rate.tax_rate_code,
869 zx_rate.description,
870 zx_rate.PERCENTAGE_RATE,
871 zx_rate.vat_transaction_type_code,
872 gjl1.tax_type_code, --zx_tax.tax_type_code,
873 zx_rate.tax_rate_name,
874 --zx_rate.tax_rate_register_type_code,
875 zx_rate.tax_regime_code,
876 zx_rate.tax,
877 zx_rate.tax_jurisdiction_code,
878 zx_rate.tax_status_code,
879 TO_CHAR(NULL), --tax_currency_code
880 TO_NUMBER(NULL), --tax_amt_tax_curr
881 TO_NUMBER(NULL), --taxable_amt_tax_curr
882 TO_NUMBER(NULL), --orig_taxable_amt
883 TO_NUMBER(NULL), --orig_taxable_amt_tax_curr
884 TO_NUMBER(NULL), --orig_tax_amt
885 TO_NUMBER(NULL), --orig_tax_amt_tax_curr
886 TO_NUMBER(NULL), --precision
887 TO_NUMBER(NULL), --minimum_accountable_unit
888 -- TO_CHAR(NULL), --functional_currency_code
889 TO_NUMBER(NULL), --gjl1.je_line_num,
890 TO_NUMBER(NULL), --gjl1.je_line_num,
891 TO_CHAR(NULL), --gjl1.description,
892 TO_CHAR(NULL), --gjl1.line_type_code,
893 TO_NUMBER(NULL), --establishment id
894 TO_NUMBER(NULL), --internal organization id
895 --NULL,
896 ''GL'',
897 NULL,
898 TO_CHAR(NULL), -- gjl1.invoice_identifier,
899 gjl2.code_combination_id,
900 gjh.period_name ,
901 TO_DATE(NULL)'; --gjl1.effective_date
902
903 l_table_list_gl :=
904 ' FROM
905 fnd_document_sequences seq,
906 -- gl_tax_options gto,
907 gl_code_combinations gcc2,
908 gl_je_lines gjl2,
909 -- ar_ap_tax_codes_v tax,
910 zx_rates_vl zx_rate,
911 zx_taxes_vl zx_tax,
912 gl_code_combinations gcc1,
913 gl_je_lines gjl1,
914 gl_je_batches gjb,
915 gl_je_headers gjh,
916 gl_period_statuses gps ';
917
918 l_where_clause_gl :=
919 ' WHERE gps.ledger_id = '||to_char(g_ledger_id)||
920 ' AND gps.application_id = 101 '||
921 ' AND gps.closing_status <> ''N'' '||
922 ' AND gjh.period_name = gps.period_name '||
923 ' AND gjh.tax_status_code = ''T'' '||
924 ' AND gjh.ledger_id = '||to_char(g_ledger_id)||
925 ' AND gjh.actual_flag = ''A'' '||
926 ' AND gjb.je_batch_id(+) = gjh.je_batch_id '||
927 ' AND gjb.default_period_name = gjh.period_name '||
928 ' AND gjb.actual_flag = ''A'' '||
929 -- ' AND gjb.ledger_id = '||to_char(g_ledger_id)||
930 ' AND gjl1.je_header_id = gjh.je_header_id '||
931 ' AND gjl1.tax_code_id = DECODE(gjb.SET_OF_BOOKS_ID_11I,NULL,ZX_RATE.TAX_RATE_ID,NVL(ZX_RATE.SOURCE_ID, ZX_RATE.TAX_RATE_ID)) '||
932 ' AND zx_tax.tax = zx_rate.tax
933 and zx_tax.TAX_REGIME_CODE = zx_rate.TAX_REGIME_CODE
934 and zx_tax.CONTENT_OWNER_ID = zx_rate.CONTENT_OWNER_ID '||
935 ' AND gjl1.tax_code_id is not NULL '||
936 ' AND gcc1.code_combination_id = gjl1.code_combination_id '||
937 ' AND gjl2.je_header_id = gjl1.je_header_id '||
938 ' AND gjl2.tax_group_id = gjl1.tax_group_id '||
939 ' AND gjl2.tax_code_id is NULL '||
940 ' AND gcc2.code_combination_id = gjl2.code_combination_id '||
941 -- ' AND gto.ledger_id = '||to_char(g_ledger_id)||
942 -- ' AND gto.org_id = gjb.org_id '||
943 ' AND seq.doc_sequence_id (+) = gjh.doc_sequence_id '
944 || L_WHERE_GL_OR_TRX_DATE
945 || L_WHERE_GL_DATE
946 || L_WHERE_TRX_DATE
947 || L_WHERE_TAX_JURISDICTION_CODE
948 -- || L_WHERE_FIRST_PTY_TAX_REG_NUM
949 || L_WHERE_TAX_REGIME_CODE
950 || L_WHERE_TAX
951 || L_WHERE_TAX_STATUS_CODE
952 || L_WHERE_TAX_RATE_CODE
953 || L_WHERE_TAX_TYPE_CODE
954 || L_WHERE_CURRENCY_CODE
955 || L_WHERE_POSTING_STATUS
956 || L_WHERE_TAX_CODE_VAT_TRX_TYPE
957 || L_WHERE_TRX_NUMBER_GL
958 || L_WHERE_TAX_INVOICE_DATE
959 || L_WHERE_TRX_CLASS_GL
960 -- || L_WHERE_GL_FLEX
961 || L_WHERE_GL_LINES_ATT3_IS_NULL
962 ||' GROUP BY
963 gjh.ledger_id,
964 gjh.je_header_id,
965 gjh.doc_sequence_id,
966 gjh.doc_sequence_value,
967 seq.name,
968 gjl1.tax_code_id,
969 gjl1.tax_type_code,
970 gjl2.code_combination_id,
971 -- gjl1.tax_document_date,
972 -- zx_rate.tax_class,
973 -- gjl1.tax_document_identifier,
974 -- gjl1.tax_customer_name,
975 -- gjl1.tax_customer_reference,
976 -- gjl1.tax_registration_number,
977 -- gjl1.je_line_num, --acctg_dist_id
978 gjh.period_name,
979 gjh.description,
980 gjh.currency_code,
981 gjh.currency_conversion_type,
982 gjh.currency_conversion_date,
983 gjh.currency_conversion_rate,
984 -- gjl1.line_type_code,
985 -- gjl1.description,
986 -- gjl1.context,
987 -- gjl1.attribute1,
988 -- gjl1.attribute2,
989 -- gjl1.attribute3,
990 -- gjl1.attribute4,
991 -- gjl1.attribute5,
992 -- gjl1.attribute6,
993 -- gjl1.attribute7,
994 -- gjl1.attribute8,
995 -- gjl1.attribute9,
996 -- gjl1.attribute10,
997 -- gjl1.attribute11,
998 -- gjl1.attribute12,
999 -- gjl1.attribute13,
1000 -- gjl1.attribute14,
1001 -- gjl1.attribute15,
1002 gjh.posted_date,
1003 zx_rate.tax_rate_code,
1004 zx_rate.description,
1005 zx_rate.PERCENTAGE_RATE,
1006 zx_rate.vat_transaction_type_code,
1007 --zx_tax.tax_type_code,
1008 zx_rate.tax_rate_name,
1009 zx_rate.tax_regime_code,
1010 zx_rate.tax,
1011 zx_rate.tax_status_code,
1012 zx_rate.tax_jurisdiction_code,
1013 gjl1.tax_document_date,
1014 gjl1.tax_type_code,
1015 gjl1.tax_document_identifier,
1016 gjl1.tax_customer_name,
1017 gjl1.tax_customer_reference,
1018 gjl1.tax_registration_number ';
1019 -- gjl1.invoice_identifier,
1020 -- gjl1.effective_date ';
1021
1022
1023
1024 ELSIF g_summary_level IN ('TRANSACTION_LINE',
1025 'TRANSACTION_DISTRIBUTION') THEN
1026
1027 IF (g_level_procedure >= g_current_runtime_level ) THEN
1028 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
1029 'ZX_GL_EXTRACT_PKG: SQL-2');
1030 END IF;
1031
1032 l_column_list_gl :=
1033 ' SELECT
1034 gjh.ledger_id,
1035 gjh.je_header_id,
1036 gjh.doc_sequence_id,
1037 gjh.doc_sequence_value,
1038 seq.name,
1039 gjl1.tax_code_id,
1040 gjl2.je_line_num,
1041 (sum(nvl(gjl2.entered_cr,0) - nvl(gjl2.entered_dr,0)))*
1042 decode(gjl1.tax_type_code,''I'',-1,1),
1043 (sum(nvl(gjl2.accounted_cr,0) - nvl(gjl2.accounted_dr,0)))*
1044 decode(gjl1.tax_type_code,''I'',-1,1),
1045 gjl2.je_line_num,
1046 (sum(nvl(gjl1.entered_cr,0) - nvl(gjl1.entered_dr,0)))*
1047 decode(gjl1.tax_type_code,''I'',-1,1),
1048 (sum(nvl(gjl1.accounted_cr,0) - nvl(gjl1.accounted_dr,0)))*
1049 decode(gjl1.tax_type_code,''I'',-1,1),
1050 -- min(gjl1.je_line_num),
1051 -- gjl2.code_combination_id,
1052 sum(gjl1.stat_amount),'||
1053 -- gjh.period_name, '||
1054 'gjl1.tax_document_identifier,
1055 gjh.description,
1056 gjh.currency_code,
1057 gjl1.tax_document_date,
1058 gjl1.tax_document_date,
1059 gjh.currency_conversion_type,
1060 gjh.currency_conversion_date,
1061 gjh.currency_conversion_rate,
1062 gjl1.context,
1063 gjl1.attribute1,
1064 gjl1.attribute2,
1065 gjl1.attribute3,
1066 gjl1.attribute4,
1067 gjl1.attribute5,
1068 gjl1.attribute6,
1069 gjl1.attribute7,
1070 gjl1.attribute8,
1071 gjl1.attribute9,
1072 gjl1.attribute10,
1073 gjl1.attribute11,
1074 gjl1.attribute12,
1075 gjl1.attribute13,
1076 gjl1.attribute14,
1077 gjl1.attribute15,
1078 gjl1.tax_customer_name,
1079 gjl1.tax_customer_reference,
1080 gjl1.tax_registration_number,
1081 decode(gjh.posted_date,NULL,''N'',''Y''),
1082 zx_rate.tax_rate_code,
1083 zx_rate.description,
1084 zx_rate.PERCENTAGE_RATE,
1085 zx_rate.vat_transaction_type_code,
1086 gjl1.tax_type_code, --zx_tax.tax_type_code,
1087 zx_rate.tax_rate_name,
1088 --zx_rate.tax_rate_register_type_code, --Check this
1089 zx_rate.tax_regime_code,
1090 zx_rate.tax,
1091 zx_rate.tax_jurisdiction_code,
1092 zx_rate.tax_status_code,
1093 NULL, --tax_currency_code
1094 NULL, --tax_amt_tax_curr
1095 NULL, --taxable_amt_tax_curr
1096 NULL, --orig_taxable_amt
1097 NULL, --orig_taxable_amt_tax_curr
1098 NULL, --orig_tax_amt
1099 NULL, --orig_tax_amt_tax_curr
1100 NULL, --precision
1101 NULL, --minimum_accountable_unit
1102 -- NULL, --functional_currency_code
1103 gjl1.je_line_num,
1104 gjl1.je_line_num,
1105 gjl1.description,
1106 gjl1.line_type_code,
1107 NULL, --establishment id
1108 NULL, --internal organization id
1109 -- NULL,
1110 ''GL'',
1111 NULL,
1112 gjl1.invoice_identifier,
1113 gjl2.code_combination_id,
1114 gjh.period_name ,
1115 gjl1.effective_date ';
1116
1117 l_table_list_gl :=
1118 ' from
1119 fnd_document_sequences seq,
1120 -- gl_tax_options gto,
1121 gl_code_combinations gcc2,
1122 gl_je_lines gjl2,
1123 --ar_ap_tax_codes_v tax,
1124 zx_rates_vl zx_rate,
1125 zx_taxes_vl zx_tax,
1126 gl_code_combinations gcc1,
1127 gl_je_lines gjl1,
1128 gl_je_batches gjb,
1129 gl_je_headers gjh,
1130 gl_period_statuses gps ';
1131
1132 l_where_clause_gl :=
1133 ' WHERE gps.ledger_id = '||to_char(g_ledger_id)||
1134 ' AND gps.application_id = 101 '||
1135 ' AND gps.closing_status <> ''N'' '||
1136 ' AND gjh.period_name = gps.period_name '||
1137 ' AND gjh.tax_status_code = ''T'' '||
1138 ' AND gjh.ledger_id = '||to_char(g_ledger_id)||
1139 ' AND gjh.actual_flag = ''A'' '||
1140 ' AND gjb.je_batch_id(+) = gjh.je_batch_id '||
1141 ' AND gjb.default_period_name = gjh.period_name '||
1142 ' AND gjb.actual_flag = ''A'' '||
1143 -- ' AND gjb.ledger_id = '||to_char(g_ledger_id)||
1144 ' AND gjl1.je_header_id = gjh.je_header_id '||
1145 ' AND gjl1.tax_code_id is not NULL '||
1146 ' AND gjl1.tax_code_id = DECODE(gjb.SET_OF_BOOKS_ID_11I,NULL,ZX_RATE.TAX_RATE_ID,NVL(ZX_RATE.SOURCE_ID,ZX_RATE.TAX_RATE_ID)) '||
1147 ' AND zx_tax.tax = zx_rate.tax
1148 and zx_tax.TAX_REGIME_CODE = zx_rate.TAX_REGIME_CODE
1149 and zx_tax.CONTENT_OWNER_ID = zx_rate.CONTENT_OWNER_ID '||
1150 ' AND gcc1.code_combination_id = gjl1.code_combination_id '||
1151 ' AND gjl2.je_header_id = gjl1.je_header_id '||
1152 ' AND gjl2.tax_group_id = gjl1.tax_group_id '||
1153 ' AND gjl2.tax_code_id is NULL '||
1154 ' AND gcc2.code_combination_id = gjl2.code_combination_id '||
1155 -- ' AND gto.ledger_id = '||to_char(g_ledger_id)||
1156 -- ' AND gto.org_id = gjb.org_id '||
1157 ' AND seq.doc_sequence_id (+) = gjh.doc_sequence_id '
1158 || L_WHERE_GL_OR_TRX_DATE
1159 || L_WHERE_GL_DATE
1160 || L_WHERE_TRX_DATE
1161 || L_WHERE_TAX_JURISDICTION_CODE
1162 -- || L_WHERE_FIRST_PTY_TAX_REG_NUM
1163 || L_WHERE_TAX_REGIME_CODE
1164 || L_WHERE_TAX
1165 || L_WHERE_TAX_STATUS_CODE
1166 || L_WHERE_TAX_RATE_CODE
1167 || L_WHERE_TAX_TYPE_CODE
1168 || L_WHERE_CURRENCY_CODE
1169 || L_WHERE_POSTING_STATUS
1170 || L_WHERE_TAX_CODE_VAT_TRX_TYPE
1171 || L_WHERE_TRX_NUMBER_GL
1172 || L_WHERE_TAX_INVOICE_DATE
1173 || L_WHERE_TRX_CLASS_GL
1174 -- || L_WHERE_GL_FLEX
1175 || L_WHERE_GL_LINES_ATT3_IS_NULL
1176 ||' GROUP BY
1177 gjh.ledger_id,
1178 gjh.je_header_id,
1179 gjh.doc_sequence_id,
1180 gjh.doc_sequence_value,
1181 seq.name,
1182 gjl1.tax_code_id,
1183 gjl1.tax_type_code,
1184 gjl2.code_combination_id,
1185 gjl1.tax_document_date,
1186 -- zx_rate.tax_class,
1187 gjl1.tax_document_identifier,
1188 gjl1.tax_customer_name,
1189 gjl1.tax_customer_reference,
1190 gjl1.tax_registration_number,
1191 gjl1.je_line_num, --acctg_dist_id
1192 gjl2.je_line_num, --acctg_dist_id
1193 gjh.period_name,
1194 gjh.description,
1195 gjh.currency_code,
1196 gjh.currency_conversion_type,
1197 gjh.currency_conversion_date,
1198 gjh.currency_conversion_rate,
1199 gjl1.line_type_code,
1200 gjl1.description,
1201 gjl1.context,
1202 gjl1.attribute1,
1203 gjl1.attribute2,
1204 gjl1.attribute3,
1205 gjl1.attribute4,
1206 gjl1.attribute5,
1207 gjl1.attribute6,
1208 gjl1.attribute7,
1209 gjl1.attribute8,
1210 gjl1.attribute9,
1211 gjl1.attribute10,
1212 gjl1.attribute11,
1213 gjl1.attribute12,
1214 gjl1.attribute13,
1215 gjl1.attribute14,
1216 gjl1.attribute15,
1217 gjh.posted_date,
1218 zx_rate.tax_rate_code,
1219 zx_rate.description,
1220 zx_rate.PERCENTAGE_RATE,
1221 zx_rate.vat_transaction_type_code,
1222 --zx_tax.tax_type_code,
1223 zx_rate.tax_rate_name,
1224 zx_rate.tax_regime_code,
1225 zx_rate.tax,
1226 zx_rate.tax_status_code,
1227 zx_rate.tax_jurisdiction_code,
1228 gjl1.invoice_identifier ,
1229 gjl1.effective_date ';
1230
1231
1232
1233 END IF;
1234
1235
1236 IF (g_level_procedure >= g_current_runtime_level ) THEN
1237 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL.END',
1238 'ZX_GL_EXTRACT_PKG: BUILD_SQL(-)');
1239 END IF;
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1244 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1245 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1246 FND_MSG_PUB.Add;
1247 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1248 FND_LOG.STRING(g_level_unexpected,
1249 'ZX.TRL.ZX_GL_EXTRACT_PKG.BUILD_SQL',
1250 g_error_buffer);
1251 END IF;
1252
1253 G_GL_RETCODE := 2;
1254
1255 END BUILD_SQL;
1256
1257 /*===========================================================================+
1258 | FUNCTION |
1259 | prorated_tax |
1260 | DESCRIPTION |
1261 | |
1262 | |
1263 | SCOPE - Private |
1264 | |
1265 | NOTES |
1266 | |
1267 | MODIFICATION HISTORY |
1268 | |
1269 +===========================================================================*/
1270
1271 function prorated_tax(
1272 p_trx_id in number,
1273 p_ledger_id in number,
1274 p_doc_seq_id in number,
1275 p_tax_code_id in number,
1276 p_code_comb_id in number,
1277 p_tax_doc_date in date,
1278 p_tax_class in varchar2,
1279 p_tax_doc_identifier in varchar2,
1280 p_tax_cust_name in varchar2,
1281 p_tax_cust_reference in varchar2,
1282 p_tax_reg_number in varchar2,
1283 p_seq_name in varchar2,
1284 p_column_name in varchar2) return number is
1285
1286 cursor l_denominator_csr(p_tax_group_id in number) is
1287 select sum((nvl(GJL.ENTERED_CR,0)-nvl(GJL.ENTERED_DR,0)))
1288 from GL_JE_LINES GJL
1289 where gjl.je_header_id = p_trx_id and
1290 gjl.tax_group_id = p_tax_group_id and
1291 gjl.tax_code_id is not null;
1292
1293 /* Code added by SSWAYAMP for bug 1742970 */
1294
1295 cursor l_denominator_csr1(p_tax_group_id in number) is
1296 select SUM(nvl(GJL.ENTERED_CR,0))
1297 from GL_JE_LINES GJL
1298 where gjl.je_header_id = p_trx_id and
1299 gjl.tax_group_id = p_tax_group_id and
1300 gjl.tax_code_id is not null;
1301
1302 /* End of Bug 1742970 */
1303
1304 cursor l_tax_csr is
1305 select GJL2.ENTERED_DR EDR,
1306 GJL2.ENTERED_CR ECR,
1307 (nvl(GJL2.ENTERED_CR,0)-nvl(GJL2.ENTERED_DR,0)) EAM,
1308 GJL2.ACCOUNTED_DR ADR,
1309 GJL2.ACCOUNTED_CR ACR,
1310 (nvl(GJL2.ACCOUNTED_CR,0)-nvl(GJL2.ACCOUNTED_DR,0)) AAM,
1311 GJL1.TAX_GROUP_ID TAX_GROUP_ID,
1312 (nvl(GJL1.ENTERED_CR,0)-nvl(GJL1.ENTERED_DR,0)) ETAXABLEAM
1313 from FND_DOCUMENT_SEQUENCES SEQ,
1314 GL_JE_LINES GJL2,
1315 -- AR_AP_TAX_CODES_V TAX,
1316 ZX_RATES_VL ZX_RATE,
1317 GL_JE_LINES GJL1,
1318 GL_JE_HEADERS GJH
1319 where gjh.je_header_id = p_trx_id and
1320 gjh.je_header_id = gjl1.je_header_id and
1321 gjl1.tax_code_id = NVL(ZX_RATE.SOURCE_ID,ZX_RATE.TAX_RATE_ID)
1322 AND ((zx_rate.tax_class in ('OUTPUT','INPUT')) or
1323 (zx_rate.tax_class is null))
1324 AND gjh.je_header_id = gjl2.je_header_id and
1325 gjl2.tax_group_id = gjl1.tax_group_id and
1326 gjl2.tax_code_id is null and
1327 gjh.doc_sequence_id = seq.doc_sequence_id(+) and
1328 ((gjh.ledger_id = p_ledger_id) or
1329 (gjh.ledger_id is null and p_ledger_id is null)) and
1330 ((gjh.doc_sequence_id = p_doc_seq_id) or
1331 (gjh.doc_sequence_id is null and p_doc_seq_id is null)) and
1332 ((seq.name = p_seq_name ) or
1333 (seq.name is null and p_seq_name is null)) and
1334 ((gjl1.tax_code_id = p_tax_code_id) or
1335 (gjl1.tax_code_id is null and p_tax_code_id is null)) and
1336 ((gjl1.tax_type_code = p_tax_class) or
1337 (gjl1.tax_type_code is null and p_tax_class is null)) and
1338 ((gjl1.tax_document_identifier = p_tax_doc_identifier) or
1339 (gjl1.tax_document_identifier is null and p_tax_doc_identifier is null)) and
1340 ((gjl1.tax_document_date = p_tax_doc_date) or
1341 (gjl1.tax_document_date is null and p_tax_doc_date is null)) and
1342 ((gjl1.tax_customer_name = p_tax_cust_name) or
1343 (gjl1.tax_customer_name is null and p_tax_cust_name is null)) and
1344 ((gjl1.tax_customer_reference = p_tax_cust_reference) or
1345 (gjl1.tax_customer_reference is null and p_tax_cust_reference is null)) and
1346 ((gjl1.tax_registration_number = p_tax_reg_number) or
1347 (gjl1.tax_registration_number is null and p_tax_reg_number is null)) and
1348 ((gjl2.code_combination_id = p_code_comb_id) or
1349 (gjl2.code_combination_id is null and p_code_comb_id is null));
1350
1351 l_tax_rec_temp pr_tax_rec_type;
1352 l_amount number;
1353 l_denominator number;
1354 i NUMBER := 0;
1355
1356 begin
1357
1358 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1359
1360 IF ( g_level_procedure>= g_current_runtime_level ) THEN
1361 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG','ZX_GL_EXTRACT_PKG.prorated_tax(+)');
1362 END IF;
1363
1364 --Bug 5338150
1365 pr_tax_rec.edr:= null;
1366 pr_tax_rec.ecr := null;
1367 pr_tax_rec.eam := null;
1368 pr_tax_rec.adr := null;
1369 pr_tax_rec.acr := null;
1370 pr_tax_rec.aam := null;
1371
1372 IF ( g_level_statement>= g_current_runtime_level ) THEN
1373 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','In the Beginning : Displaying the parameters for the function prorated_tax ');
1374 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_trx_id : '||p_trx_id);
1375 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_ledger_id : '||p_ledger_id);
1376 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_doc_seq_id : '||p_doc_seq_id);
1377 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_tax_code_id : '||p_tax_code_id);
1378 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_code_comb_id :' ||p_code_comb_id );
1379 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_tax_class : '||p_tax_class );
1380 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_tax_doc_identifier :'||p_tax_doc_identifier );
1381 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_tax_cust_name : '||p_tax_cust_name );
1382 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_tax_cust_reference : '||p_tax_cust_reference );
1383 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_tax_reg_number : '||p_tax_reg_number );
1384 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_seq_name :' ||p_seq_name );
1385 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','p_column_name : '||p_column_name );
1386 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','Initial Values in pr_tax_rec record');
1387 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.edr : '||pr_tax_rec.edr);
1388 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.ecr : '||pr_tax_rec.ecr);
1389 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.eam : '|| pr_tax_rec.eam);
1390 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.adr : '|| pr_tax_rec.adr);
1391 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.aam : '|| pr_tax_rec.aam);
1392 END IF ;
1393
1394 if (pr_tax_rec.edr is null and
1395 pr_tax_rec.ecr is null and
1396 pr_tax_rec.eam is null and
1397 pr_tax_rec.adr is null and
1398 pr_tax_rec.acr is null and
1399 pr_tax_rec.aam is null) then
1400 --
1401 for l_tax_rec_temp in l_tax_csr LOOP
1402 --
1403 i := i + 1;
1404
1405 IF ( g_level_statement>= g_current_runtime_level ) THEN
1406 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG',' Values for i : '||i);
1407 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG',' l_tax_rec_temp.tax_group_id : '||l_tax_rec_temp.tax_group_id);
1408 END IF ;
1409
1410 open l_denominator_csr(l_tax_rec_temp.tax_group_id);
1411 fetch l_denominator_csr into l_denominator;
1412 close l_denominator_csr;
1413 --
1414 IF ( g_level_statement>= g_current_runtime_level ) THEN
1415 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_denominator1 : '||l_denominator);
1416 END IF ;
1417 /* Code added by SSWAYAMP for bug 1742970 */
1418
1419 if l_denominator = 0 then
1420 open l_denominator_csr1(l_tax_rec_temp.tax_group_id);
1421 fetch l_denominator_csr1 into l_denominator;
1422 if sign(l_tax_rec_temp.etaxableam) = -1 then
1423 l_denominator := l_denominator * (-1);
1424 end if;
1425 close l_denominator_csr1;
1426 end if;
1427
1428 IF ( g_level_statement>= g_current_runtime_level ) THEN
1429 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_denominator 2: '||l_denominator);
1430 END IF ;
1431 /* End of Bug 1742970 */
1432
1433 --bug2242602
1434 if l_denominator =0 and l_tax_rec_temp.etaxableam = 0 then
1435 l_denominator:= 1;
1436 end if;
1437
1438 IF PG_DEBUG = 'Y' THEN
1439 arp_util_tax.debug('etaxableam :'||to_char(l_tax_rec_temp.etaxableam));
1440 arp_util_tax.debug('l_denominator :'||to_char(l_denominator));
1441 END IF;
1442
1443 IF ( g_level_statement>= g_current_runtime_level ) THEN
1444 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_tax_rec_temp.etaxableam : '|| l_tax_rec_temp.etaxableam);
1445 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','Values fetched from l_tax_csr : for pr_tax_rec record');
1446 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_tax_rec_temp.ecr : '|| l_tax_rec_temp.ecr);
1447 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_tax_rec_temp.eam : '|| l_tax_rec_temp.eam);
1448 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_tax_rec_temp.adr : '|| l_tax_rec_temp.adr);
1449 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_tax_rec_temp.acr : '|| l_tax_rec_temp.acr);
1450 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','l_tax_rec_temp.aam : '|| l_tax_rec_temp.aam);
1451 END IF ;
1452 --
1453 pr_tax_rec.edr := nvl(pr_tax_rec.edr,0) +
1454 (l_tax_rec_temp.edr*l_tax_rec_temp.etaxableam/l_denominator);
1455 pr_tax_rec.ecr := nvl(pr_tax_rec.ecr,0) +
1456 (l_tax_rec_temp.ecr*l_tax_rec_temp.etaxableam/l_denominator);
1457 pr_tax_rec.eam := nvl(pr_tax_rec.eam,0) +
1458 (l_tax_rec_temp.eam*l_tax_rec_temp.etaxableam/l_denominator);
1459 pr_tax_rec.adr := nvl(pr_tax_rec.adr,0) +
1460 (l_tax_rec_temp.adr*l_tax_rec_temp.etaxableam/l_denominator);
1461 pr_tax_rec.acr := nvl(pr_tax_rec.acr,0) +
1462 (l_tax_rec_temp.acr*l_tax_rec_temp.etaxableam/l_denominator);
1463 pr_tax_rec.aam := nvl(pr_tax_rec.aam,0) +
1464 (l_tax_rec_temp.aam*l_tax_rec_temp.etaxableam/l_denominator);
1465 --
1466
1467 IF ( g_level_statement>= g_current_runtime_level ) THEN
1468 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.edr : '||pr_tax_rec.edr);
1469 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.ecr : '||pr_tax_rec.ecr);
1470 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.eam : '|| pr_tax_rec.eam);
1471 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.adr : '|| pr_tax_rec.adr);
1472 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.acr :'||to_char(pr_tax_rec.acr));
1473 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG','pr_tax_rec.aam : '|| pr_tax_rec.aam);
1474 END IF ;
1475
1476 end loop;
1477 --
1478 /* IF PG_DEBUG = 'Y' THEN
1479 arp_util_tax.debug('pr_tax_rec.edr :'||to_char(pr_tax_rec.edr));
1480 arp_util_tax.debug('pr_tax_rec.ecr :'||to_char(pr_tax_rec.ecr));
1481 arp_util_tax.debug('pr_tax_rec.eam :'||to_char(pr_tax_rec.eam));
1482 arp_util_tax.debug('pr_tax_rec.adr :'||to_char(pr_tax_rec.adr));
1483 arp_util_tax.debug('pr_tax_rec.acr :'||to_char(pr_tax_rec.acr));
1484 arp_util_tax.debug('pr_tax_rec.aam :'||to_char(pr_tax_rec.aam));
1485 END IF; */
1486
1487 --
1488 end if;
1489 --
1490 if p_column_name = 'EDR' then
1491 l_amount := pr_tax_rec.edr;
1492 pr_tax_rec.edr := NULL;
1493 elsif p_column_name = 'ECR' then
1494 l_amount := pr_tax_rec.ecr;
1495 pr_tax_rec.ecr := NULL;
1496 elsif p_column_name = 'EAM' then
1497 l_amount := pr_tax_rec.eam;
1498 pr_tax_rec.eam := NULL;
1499 elsif p_column_name = 'ADR' then
1500 l_amount := pr_tax_rec.adr;
1501 pr_tax_rec.adr := NULL;
1502 elsif p_column_name = 'ACR' then
1503 l_amount := pr_tax_rec.acr;
1504 pr_tax_rec.acr := NULL;
1505 elsif p_column_name = 'AAM' then
1506 l_amount := pr_tax_rec.aam;
1507 pr_tax_rec.aam := NULL;
1508 else
1509 l_amount := 0;
1510 end if;
1511 --
1512 IF ( g_level_statement>= g_current_runtime_level ) THEN
1513 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG',
1514 ' p_column_name : '||p_column_name||' l_amount : '||l_amount);
1515 END IF ;
1516
1517 IF PG_DEBUG = 'Y' THEN
1518 arp_util_tax.debug('ZX_GL_EXTRACT_PKG.prorated_tax(-)');
1519 END IF;
1520 return l_amount;
1521 end prorated_tax;
1522
1523 /*===========================================================================+
1524 | PROCEDURE |
1525 | INSERT_GL_SUB_ITF |
1526 | DESCRIPTION |
1527 | This procedure inserts GL data into AR_TAX_EXTRACT_SUB_ITF table |
1528 | |
1529 | SCOPE - Private |
1530 | |
1531 | NOTES |
1532 | |
1533 | MODIFICATION HISTORY |
1534 | |
1535 +===========================================================================*/
1536
1537 PROCEDURE INSERT_GL_SUB_ITF
1538 IS
1539 m NUMBER;
1540 BEGIN
1541
1542
1543 IF (g_level_procedure >= g_current_runtime_level ) THEN
1544 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
1545 'INSERT_GL_SUB_ITF(+)');
1546 END IF;
1547 for m in 1..nvl(gt_ledger_id.COUNT,0) loop
1548
1549 IF (g_level_statement >= g_current_runtime_level ) THEN
1550 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
1551 'Taxable Amt '||to_char(GT_TAXABLE_AMT_FUNCL_CURR(m)));
1552 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
1553 'Tax Amt '||to_char(GT_TAX_AMT(m)));
1554 END IF;
1555
1556
1557 end loop;
1558
1559 FORALL i IN 1 .. nvl(gt_ledger_id.COUNT, 0)
1560
1561 -- New insert code
1562
1563 INSERT into zx_rep_trx_detail_t
1564 (application_id,
1565 detail_tax_line_id,
1566 ledger_id,
1567 trx_id,
1568 doc_seq_id,
1569 doc_seq_value,
1570 doc_seq_name,
1571 tax_rate_id,
1572 tax_line_id,
1573 tax_amt,
1574 tax_amt_funcl_curr,
1575 tax_line_number,
1576 taxable_amt,
1577 taxable_amt_funcl_curr,
1578 -- xla_code_combination_id,
1579 trx_line_quantity,
1580 -- xla_period_name,
1581 trx_number,
1582 trx_line_class,
1583 trx_description,
1584 trx_currency_code,
1585 trx_date,
1586 trx_communicated_date,
1587 tax_invoice_date,
1588 currency_conversion_type,
1589 currency_conversion_date,
1590 currency_conversion_rate,
1591 tax_line_user_category,
1592 tax_line_user_attribute1,
1593 tax_line_user_attribute2,
1594 tax_line_user_attribute3,
1595 tax_line_user_attribute4,
1596 tax_line_user_attribute5,
1597 tax_line_user_attribute6,
1598 tax_line_user_attribute7,
1599 tax_line_user_attribute8,
1600 tax_line_user_attribute9,
1601 tax_line_user_attribute10,
1602 tax_line_user_attribute11,
1603 tax_line_user_attribute12,
1604 tax_line_user_attribute13,
1605 tax_line_user_attribute14,
1606 tax_line_user_attribute15,
1607 billing_tp_name,
1608 billing_tp_number,
1609 billing_tp_tax_reg_num,
1610 posted_flag,
1611 tax_rate_code,
1612 tax_rate_code_description,
1613 tax_rate,
1614 tax_rate_vat_trx_type_code,
1615 tax_type_code,
1616 tax_rate_code_name,
1617 tax_rate_register_type_code,
1618 tax_rate_code_reg_type_mng,
1619 tax_regime_code,
1620 tax,
1621 tax_jurisdiction_code,
1622 tax_status_code,
1623 tax_currency_code,
1624 tax_amt_tax_curr,
1625 taxable_amt_tax_curr,
1626 orig_taxable_amt,
1627 orig_taxable_amt_tax_curr,
1628 orig_tax_amt,
1629 orig_tax_amt_tax_curr,
1630 precision,
1631 minimum_accountable_unit,
1632 -- functional_currency_code,
1633 trx_line_id,
1634 trx_line_number,
1635 trx_line_description,
1636 trx_line_type,
1637 establishment_id,
1638 internal_organization_id,
1639 --ledger_name,
1640 extract_source_ledger,
1641 doc_event_status,
1642 sub_ledger_invoice_identifier,
1643 CREATED_BY ,
1644 CREATION_DATE ,
1645 LAST_UPDATED_BY,
1646 LAST_UPDATE_DATE,
1647 LAST_UPDATE_LOGIN,
1648 request_id,
1649 entity_code,
1650 event_class_code,
1651 rep_context_id,
1652 functional_currency_code,
1653 ledger_name)
1654 VALUES (
1655 101,
1656 gt_detail_tax_line_id(i),
1657 gt_ledger_id(i),
1658 gt_trx_id(i),
1659 gt_doc_seq_id(i),
1660 gt_doc_seq_value(i),
1661 gt_doc_seq_name(i),
1662 gt_tax_rate_id(i),
1663 gt_tax_line_id (i),
1664 gt_tax_amt(i),
1665 gt_tax_amt_funcl_curr(i),
1666 gt_tax_line_number(i),
1667 gt_taxable_amt(i),
1668 gt_taxable_amt_funcl_curr(i),
1669 -- gt_xla_code_combination_id(i),
1670 gt_trx_line_quantity(i),
1671 -- gt_xla_period_name(i),
1672 gt_trx_number(i),
1673 'GLMJE',
1674 gt_trx_description(i),
1675 gt_trx_currency_code(i),
1676 gt_trx_date(i),
1677 gt_trx_communicated_date(i),
1678 gt_trx_communicated_date(i),
1679 gt_currency_conversion_type(i),
1680 gt_currency_conversion_date(i),
1681 gt_currency_conversion_rate(i),
1682 gt_tax_line_user_category(i),
1683 gt_tax_line_user_attribute1(i),
1684 gt_tax_line_user_attribute2(i),
1685 gt_tax_line_user_attribute3(i),
1686 gt_tax_line_user_attribute4(i),
1687 gt_tax_line_user_attribute5(i),
1688 gt_tax_line_user_attribute6(i),
1689 gt_tax_line_user_attribute7(i),
1690 gt_tax_line_user_attribute8(i),
1691 gt_tax_line_user_attribute9(i),
1692 gt_tax_line_user_attribute10(i),
1693 gt_tax_line_user_attribute11(i),
1694 gt_tax_line_user_attribute12(i),
1695 gt_tax_line_user_attribute13(i),
1696 gt_tax_line_user_attribute14(i),
1697 gt_tax_line_user_attribute15(i),
1698 gt_billing_tp_name(i),
1699 gt_billing_tp_number(i),
1700 gt_billing_tp_tax_reg_num(i),
1701 gt_posted_flag(i),
1702 gt_tax_rate_code(i),
1703 gt_tax_rate_code_description(i),
1704 gt_tax_rate(i),
1705 gt_tax_rate_vat_trx_type_code(i),
1706 gt_tax_type_code(i),
1707 gt_tax_rate_code_name(i),
1708 -- gt_tax_rate_reg_type_code(i),
1709 'TAX',
1710 g_tax_register_type_mng,
1711 gt_tax_regime_code(i),
1712 gt_tax(i),
1713 gt_tax_jurisdiction_code(i),
1714 gt_tax_status_code(i),
1715 gt_tax_currency_code(i),
1716 gt_tax_amt_tax_curr(i),
1717 gt_taxable_amt_tax_curr(i),
1718 gt_orig_taxable_amt(i),
1719 gt_orig_taxable_amt_tax_curr(i),
1720 gt_orig_tax_amt(i),
1721 gt_orig_tax_amt_tax_curr(i),
1722 gt_precision(i),
1723 gt_minimum_accountable_unit(i),
1724 -- gt_functional_currency_code(i),
1725 gt_trx_line_id(i),
1726 gt_trx_line_number(i),
1727 gt_trx_line_description(i),
1728 gt_trx_line_type(i),
1729 gt_establishment_id(i),
1730 gt_internal_organization_id(i),
1731 --gt_ledger_name(i),
1732 gt_extract_source_ledger(i),
1733 gt_doc_event_status(i),
1734 gt_sub_ledger_inv_identifier(i),
1735 g_created_by ,
1736 g_creation_date ,
1737 g_last_updated_by,
1738 g_last_update_date,
1739 g_last_update_login,
1740 g_request_id,
1741 'GL_JE_LINES',
1742 'MANUAL_JOURNALS',
1743 g_rep_context_id,
1744 g_fun_currency_code,
1745 g_ledger_name );
1746
1747 IF g_include_accounting_segments='Y' THEN
1748 FORALL i IN 1 .. nvl(gt_ledger_id.COUNT, 0)
1749 INSERT INTO ZX_REP_ACTG_EXT_T(
1750 actg_ext_line_id,
1751 detail_tax_line_id,
1752 actg_line_ccid,
1753 period_name,
1754 gl_transfer_flag,
1755 accounting_date,
1756 created_by,
1757 creation_date,
1758 last_updated_by,
1759 last_update_date,
1760 last_update_login,
1761 program_application_id,
1762 program_id,
1763 program_login_id,
1764 request_id)
1765 VALUES (
1766 zx_rep_actg_ext_t_s.nextval,
1767 gt_detail_tax_line_id(i),
1768 gt_actg_line_ccid(i),
1769 gt_period_name(i),
1770 'Y',
1771 gt_accounting_date(i),
1772 g_created_by,
1773 g_creation_date,
1774 g_last_updated_by,
1775 g_last_update_date,
1776 g_last_update_login,
1777 g_program_application_id,
1778 g_program_id,
1779 g_program_login_id,
1780 g_request_id);
1781 END IF;
1782 /*
1783 INSERT INTO AR_TAX_EXTRACT_SUB_ITF
1784 (
1785 EXTRACT_LINE_ID,
1786 CREATED_BY ,
1787 CREATION_DATE ,
1788 LAST_UPDATED_BY,
1789 LAST_UPDATE_DATE,
1790 LAST_UPDATE_LOGIN,
1791 REQUEST_ID,
1792 CHART_OF_ACCOUNTS_ID,
1793 REP_CONTEXT_ID,
1794 EXTRACT_REPORT_LINE_NUMBER,
1795 ledger_id,
1796 EXTRACT_SOURCE_LEDGER,
1797 TRX_ID,
1798 TRX_DOC_SEQUENCE_ID,
1799 TRX_DOC_SEQUENCE_NAME,
1800 TRX_CLASS_CODE,
1801 TAX_CODE_ID,
1802 TAX_CODE_REGISTER_TYPE_CODE,
1803 TAX_LINE_ID,
1804 TAX_ENTERED_DR,
1805 TAX_ENTERED_CR,
1806 TAX_ENTERED_AMOUNT,
1807 TAX_ACCOUNTED_DR,
1808 TAX_ACCOUNTED_CR,
1809 TAX_ACCOUNTED_AMOUNT,
1810 TAX_LINE_NUMBER,
1811 TAXABLE_ENTERED_DR,
1812 TAXABLE_ENTERED_CR,
1813 TAXABLE_AMOUNT,
1814 TAXABLE_ACCOUNTED_DR,
1815 TAXABLE_ACCOUNTED_CR,
1816 TAXABLE_ACCOUNTED_AMOUNT,
1817 ACCTG_DIST_ID,
1818 AL_ACCOUNT_CCID,
1819 TAXABLE_LINE_QUANTITY,
1820 RECONCILIATION_FLAG,
1821 AH_PERIOD_NAME
1822 )
1823 VALUES
1824 (
1825 AR_TAX_EXTRACT_SUB_ITF_S.nextval,
1826 1,
1827 SYSDATE,
1828 1,
1829 SYSDATE,
1830 1,
1831 G_REQUEST_ID,
1832 G_CHART_OF_ACCOUNTS_ID,
1833 G_REP_CONTEXT_ID, --BUG 2610643
1834 PG_EXTRACT_REPORT_LINE_NUM_TAB(i),
1835 PG_ledger_id(i),
1836 'GL',
1837 PG_TRX_ID_TAB(i),
1838 PG_TRX_DOC_SEQUENCE_ID_TAB(i),
1839 PG_TRX_DOC_SEQUENCE_NAME_TAB(i),
1840 'GLMJE',
1841 PG_TAX_CODE_ID_TAB(i),
1842 'TAX',
1843 PG_TAX_LINE_ID_TAB(i),
1844 PG_TAX_ENTERED_DR_TAB(i),
1845 PG_TAX_ENTERED_CR_TAB(i),
1846 PG_TAX_ENTERED_AMOUNT_TAB(i),
1847 PG_TAX_ACCOUNTED_DR_TAB(i),
1848 PG_TAX_ACCOUNTED_CR_TAB(i),
1849 PG_TAX_ACCOUNTED_AMOUNT_TAB(i),
1850 PG_TAX_LINE_NUMBER_TAB(i),
1851 PG_TAXABLE_ENTERED_DR_TAB(i),
1852 PG_TAXABLE_ENTERED_CR_TAB(i),
1853 PG_TAXABLE_AMOUNT_TAB(i),
1854 PG_TAXABLE_ACCOUNTED_DR_TAB(i),
1855 PG_TAXABLE_ACCOUNTED_CR_TAB(i),
1856 PG_TAXABLE_ACCOUNTED_AMT_TAB(i),
1857 PG_ACCTG_DIST_ID_TAB(i),
1858 PG_AL_ACCOUNT_CCID_TAB(i),
1859 PG_TAXABLE_LINE_QUANTITY_TAB(i),
1860 'N',
1861 PG_PERIOD_NAME_TAB(i)
1862 );
1863 */
1864
1865
1866 IF PG_DEBUG = 'Y' THEN
1867 arp_util_tax.debug('Number of GL rows successfully inserted = ' ||
1868 TO_CHAR(nvl(gt_ledger_id.COUNT, 0)));
1869 END IF;
1870
1871 IF (g_level_procedure >= g_current_runtime_level ) THEN
1872 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
1873 'Number of GL rows successfully inserted ='||TO_CHAR(nvl(gt_ledger_id.COUNT, 0)));
1874 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF.END',
1875 'INSERT_GL_SUB_ITF(-)');
1876 END IF;
1877
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1881 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1882 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1883 FND_MSG_PUB.Add;
1884 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1885 FND_LOG.STRING(g_level_unexpected,
1886 'ZX.TRL.ZX_GL_EXTRACT_PKG.INSERT_GL_SUB_ITF',
1887 g_error_buffer);
1888 END IF;
1889
1890 G_GL_RETCODE := 2;
1891
1892 END INSERT_GL_SUB_ITF;
1893
1894 /*===========================================================================+
1895 | PROCEDURE |
1896 | FETCH_GL_TRX_INFO |
1897 | DESCRIPTION |
1898 | This proceure executes GL sql statements from build_sql in GL |
1899 | |
1900 | SCOPE - Private |
1901 | |
1902 | NOTES |
1903 | |
1904 | MODIFICATION HISTORY |
1905 | |
1906 +===========================================================================*/
1907
1908 PROCEDURE FETCH_GL_TRX_INFO
1909 IS
1910 TYPE ZX_REP_DETAIL_CURTYPE IS REF CURSOR;
1911
1912 zx_rep_detail_csr ZX_REP_DETAIL_CURTYPE;
1913 i BINARY_INTEGER;
1914 L_SQL_STATEMENT_GL VARCHAR2(16000);
1915 l_sql1 varchar2(3500);
1916 l_sql2 varchar2(3500);
1917 l_sql3 varchar2(3500);
1918 l_sql4 varchar2(3500);
1919 l_sql5 varchar2(3500);
1920 l_sql6 varchar2(3500);
1921 l_sql7 varchar2(3500);
1922 l_sql8 varchar2(3500);
1923 l_sql9 varchar2(3500);
1924 BEGIN
1925
1926 IF (g_level_procedure >= g_current_runtime_level ) THEN
1927 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO.BEGIN',
1928 'ZX_GL_EXTRACT_PKG: FETCH_GL_TRX_INFO(+)');
1929 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO',
1930 'FETCH_GL_TRX_INFO : Open zx_rep_detail_csr ');
1931 END IF;
1932
1933 L_SQL_STATEMENT_GL := L_COLUMN_LIST_GL ||
1934 L_TABLE_LIST_GL ||
1935 L_WHERE_CLAUSE_GL;
1936
1937
1938 IF (g_level_procedure >= g_current_runtime_level ) THEN
1939 l_sql1 := substr(L_SQL_STATEMENT_GL,1,3000);
1940 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql1);
1941 l_sql2 := substr(L_SQL_STATEMENT_GL,3001,3000);
1942 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql2);
1943 l_sql3 := substr(L_SQL_STATEMENT_GL,6001,3000);
1944 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql3);
1945 l_sql4 := substr(L_SQL_STATEMENT_GL,9001,3000);
1946 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql4);
1947 l_sql5 := substr(L_SQL_STATEMENT_GL,12001,3000);
1948 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql5);
1949 l_sql6 := substr(L_SQL_STATEMENT_GL,15001,3000);
1950 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql6);
1951 l_sql7 := substr(L_SQL_STATEMENT_GL,18001,3000);
1952 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql7);
1953 l_sql8 := substr(L_SQL_STATEMENT_GL,21001,3000);
1954 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO SQL', 'L_SQL_STATEMENT_GL : '||l_sql8);
1955 END IF;
1956
1957 OPEN zx_rep_detail_csr FOR L_SQL_STATEMENT_GL USING
1958 G_GL_DATE_LOW,
1959 G_GL_DATE_HIGH,
1960 G_TRX_DATE_LOW,
1961 G_TRX_DATE_HIGH,
1962 G_TAX_JURISDICTION_CODE,
1963 -- G_FIRST_PARTY_TAX_REG_NUM,
1964 G_TAX_REGIME_CODE,
1965 G_TAX,
1966 G_TAX_STATUS_CODE,
1967 G_TAX_RATE_CODE_LOW,
1968 G_TAX_RATE_CODE_HIGH,
1969 G_TAX_TYPE_CODE_LOW,
1970 G_TAX_TYPE_CODE_HIGH,
1971 G_CURRENCY_CODE_LOW,
1972 G_CURRENCY_CODE_HIGH,
1973 G_VAT_TRANSACTION_TYPE_CODE,
1974 G_TRX_NUMBER_LOW,
1975 G_TRX_NUMBER_HIGH,
1976 G_TAX_INVOICE_DATE_LOW,
1977 G_TAX_INVOICE_DATE_HIGH,
1978 G_GDF_GL_JE_LINES_CATEGORY,
1979 G_GDF_GL_JE_LINES_ATT3;
1980 -- G_DOCUMENT_SUB_TYPE,
1981 -- G_TRX_BUSINESS_CATEGORY,
1982 -- G_AR_EXEMPTION_STATUS,
1983 -- G_VAT_TAX,
1984 -- G_VAT_ADDITIONAL_TAX,
1985 -- G_VAT_NON_TAXABLE_TAX,
1986 -- G_VAT_NOT_TAX,
1987 -- G_VAT_PERCEPTION_TAX,
1988 -- G_EXCISE_TAX;
1989
1990
1991 IF (g_level_procedure >= g_current_runtime_level ) THEN
1992 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO',
1993 'FETCH_GL_TRX_INFO : After Open zx_rep_detail_csr ');
1994 END IF;
1995
1996 --
1997 -- init counter and global GL tables
1998 --
1999 i := 1;
2000 INIT_GL_GT_TABLES;
2001 g_created_by := fnd_global.user_id;
2002 g_creation_date := sysdate;
2003 g_last_updated_by := fnd_global.user_id;
2004 g_last_update_login := fnd_global.login_id;
2005 g_last_update_date := sysdate;
2006
2007
2008 IF PG_DEBUG = 'Y' THEN
2009 arp_util_tax.debug('fetch GL rows ');
2010 END IF;
2011
2012 G_REP_CONTEXT_ID := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(g_legal_entity_id,
2013 g_request_id);
2014
2015 --
2016 -- insert when fetch up to 1000 rows
2017 --
2018 LOOP
2019 FETCH zx_rep_detail_csr INTO
2020 gt_ledger_id(i),
2021 gt_trx_id(i),
2022 gt_doc_seq_id(i),
2023 gt_doc_seq_value(i),
2024 gt_doc_seq_name(i),
2025 gt_tax_rate_id(i),
2026 gt_tax_line_id(i),
2027 gt_tax_amt(i),
2028 gt_tax_amt_funcl_curr(i),
2029 gt_tax_line_number(i),
2030 gt_taxable_amt(i),
2031 gt_taxable_amt_funcl_curr(i),
2032 --gt_xla_code_combination_id(i),
2033 gt_trx_line_quantity(i),
2034 -- gt_xla_period_name(i),
2035 gt_trx_number(i),
2036 gt_trx_description(i),
2037 gt_trx_currency_code(i),
2038 gt_trx_date(i),
2039 gt_trx_communicated_date(i),
2040 gt_currency_conversion_type(i),
2041 gt_currency_conversion_date(i),
2042 gt_currency_conversion_rate(i),
2043 gt_tax_line_user_category(i),
2044 gt_tax_line_user_attribute1(i),
2045 gt_tax_line_user_attribute2(i),
2046 gt_tax_line_user_attribute3(i),
2047 gt_tax_line_user_attribute4(i),
2048 gt_tax_line_user_attribute5(i),
2049 gt_tax_line_user_attribute6(i),
2050 gt_tax_line_user_attribute7(i),
2051 gt_tax_line_user_attribute8(i),
2052 gt_tax_line_user_attribute9(i),
2053 gt_tax_line_user_attribute10(i),
2054 gt_tax_line_user_attribute11(i),
2055 gt_tax_line_user_attribute12(i),
2056 gt_tax_line_user_attribute13(i),
2057 gt_tax_line_user_attribute14(i),
2058 gt_tax_line_user_attribute15(i),
2059 gt_billing_tp_name(i),
2060 gt_billing_tp_number(i),
2061 gt_billing_tp_tax_reg_num(i),
2062 gt_posted_flag(i),
2063 gt_tax_rate_code(i),
2064 gt_tax_rate_code_description(i),
2065 gt_tax_rate(i),
2066 gt_tax_rate_vat_trx_type_code(i),
2067 gt_tax_type_code(i),
2068 gt_tax_rate_code_name(i),
2069 -- gt_tax_rate_reg_type_code(i),
2070 gt_tax_regime_code(i),
2071 gt_tax(i),
2072 gt_tax_jurisdiction_code(i),
2073 gt_tax_status_code(i),
2074 gt_tax_currency_code(i),
2075 gt_tax_amt_tax_curr(i),
2076 gt_taxable_amt_tax_curr(i),
2077 gt_orig_taxable_amt(i),
2078 gt_orig_taxable_amt_tax_curr(i),
2079 gt_orig_tax_amt(i),
2080 gt_orig_tax_amt_tax_curr(i),
2081 gt_precision(i),
2082 gt_minimum_accountable_unit(i),
2083 -- gt_functional_currency_code(i),
2084 gt_trx_line_id(i),
2085 gt_trx_line_number(i),
2086 gt_trx_line_description(i),
2087 gt_trx_line_type(i),
2088 gt_establishment_id(i),
2089 gt_internal_organization_id(i),
2090 -- gt_ledger_name(i),
2091 gt_extract_source_ledger(i),
2092 gt_doc_event_status(i),
2093 gt_sub_ledger_inv_identifier(i),
2094 gt_actg_line_ccid(i),
2095 gt_period_name(i),
2096 gt_accounting_date(i);
2097
2098
2099 IF (g_level_procedure >= g_current_runtime_level ) THEN
2100 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.fetch_tax_info',
2101 'Before insert GL Tax lines :' ||to_char(GT_TRX_ID.count));
2102 END IF;
2103
2104 IF zx_rep_detail_csr%FOUND THEN
2105
2106 SELECT zx_rep_trx_detail_t_s.nextval
2107 INTO gt_detail_tax_line_id(i)
2108 FROM DUAL;
2109
2110 --
2111 -- populate EXTRACT_REPORT_LINE_NUMBER
2112 --
2113 -- PG_EXTRACT_REPORT_LINE_NUM_TAB(i) := G_EXTRACT_LINE_NUM;
2114 -- G_EXTRACT_LINE_NUM := G_EXTRACT_LINE_NUM + 1;
2115
2116 IF (i >= C_LINES_PER_INSERT) THEN
2117 INSERT_GL_SUB_ITF;
2118 --
2119 -- reset counter and init gt tables
2120 --
2121 i := 1;
2122 INIT_GL_GT_TABLES;
2123 ELSE
2124 i := i + 1;
2125 END IF;
2126
2127 ELSE
2128 --
2129 -- total rows fetched less than 1000
2130 -- insert the rest of rows
2131 --
2132 INSERT_GL_SUB_ITF;
2133 CLOSE zx_rep_detail_csr;
2134 EXIT;
2135 END IF;
2136 END LOOP;
2137
2138 IF (g_level_procedure >= g_current_runtime_level ) THEN
2139 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_GL_TRX_INFO.END',
2140 'ZX_GL_EXTRACT_PKG: FETCH_GL_TRX_INFO(-)');
2141 END IF;
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2146 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2147 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2148 FND_MSG_PUB.Add;
2149 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2150 FND_LOG.STRING(g_level_unexpected,
2151 'ZX.TRL.ZX_GL_EXTRACT_PKG.FETCH_TAX_INFO',
2152 g_error_buffer);
2153 END IF;
2154
2155 G_GL_RETCODE := 2;
2156
2157 END FETCH_GL_TRX_INFO;
2158
2159 /*===========================================================================+
2160 | PROCEDURE |
2161 | INIT_GL_GT_TABLES |
2162 | DESCRIPTION |
2163 | This proceure initialize the global table od columns. |
2164 | |
2165 | SCOPE - Private |
2166 | |
2167 | NOTES |
2168 | |
2169 | MODIFICATION HISTORY |
2170 | |
2171 +===========================================================================*/
2172
2173
2174 PROCEDURE INIT_GL_GT_TABLES IS
2175 BEGIN
2176
2177 gt_ledger_id.DELETE;
2178 gt_sub_ledger_inv_identifier.DELETE;
2179 gt_actg_line_ccid.DELETE;
2180 gt_period_name.DELETE;
2181 gt_detail_tax_line_id.DELETE;
2182 /* PG_TRX_ID_TAB.DELETE;
2183 PG_TRX_DOC_SEQUENCE_ID_TAB.DELETE;
2184 PG_TRX_DOC_SEQUENCE_NAME_TAB.DELETE;
2185 PG_TAX_CODE_ID_TAB.DELETE;
2186 PG_TAX_LINE_ID_TAB.DELETE;
2187 PG_TAX_ENTERED_DR_TAB.DELETE;
2188 PG_TAX_ENTERED_CR_TAB.DELETE;
2189 PG_TAX_ENTERED_AMOUNT_TAB.DELETE;
2190 PG_TAX_ACCOUNTED_DR_TAB.DELETE;
2191 PG_TAX_ACCOUNTED_CR_TAB.DELETE;
2192 PG_TAX_ACCOUNTED_AMOUNT_TAB.DELETE;
2193 PG_TAX_LINE_NUMBER_TAB.DELETE;
2194 PG_TAXABLE_ENTERED_DR_TAB.DELETE;
2195 PG_TAXABLE_ENTERED_CR_TAB.DELETE;
2196 PG_TAXABLE_AMOUNT_TAB.DELETE;
2197 PG_TAXABLE_ACCOUNTED_DR_TAB.DELETE;
2198 PG_TAXABLE_ACCOUNTED_CR_TAB.DELETE;
2199 PG_TAXABLE_ACCOUNTED_AMT_TAB.DELETE;
2200 PG_ACCTG_DIST_ID_TAB.DELETE;
2201 PG_AL_ACCOUNT_CCID_TAB.DELETE;
2202 PG_TAXABLE_LINE_QUANTITY_TAB.DELETE;
2203 PG_PERIOD_NAME_TAB.DELETE;
2204 PG_EXTRACT_REPORT_LINE_NUM_TAB.DELETE;
2205 */
2206 END INIT_GL_GT_TABLES;
2207
2208 PROCEDURE UPDATE_ADDITIONAL_INFO(
2209 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
2210 IS
2211
2212
2213 CURSOR detail_t_cur(c_request_id IN NUMBER) IS
2214 SELECT DET.DETAIL_TAX_LINE_ID,
2215 DET.LEDGER_ID,
2216 DET.INTERNAL_ORGANIZATION_ID,
2217 DET.TRX_ID ,
2218 -- TRX_TYPE_ID ,
2219 DET.TRX_LINE_CLASS,
2220 DET.TAX_RATE_VAT_TRX_TYPE_CODE,
2221 -- TAX_RATE_REGISTER_TYPE_CODE,
2222 DET.TAX_LINE_ID ,
2223 DET.TRX_LINE_ID ,
2224 -- RECONCILIATION_FLAG ,
2225 DET.TAX_REGIME_CODE,
2226 DET.TAX,
2227 DET.TAX_JURISDICTION_CODE,
2228 DET.TAX_RATE,
2229 DET.TAX_RATE_ID,
2230 DET.TAX_RATE_CODE,
2231 DET.TAX_TYPE_CODE,
2232 DET.TRX_DATE,
2233 DET.TRX_CURRENCY_CODE,
2234 DET.CURRENCY_CONVERSION_RATE,
2235 DET.APPLICATION_ID,
2236 DET.TAX_AMT,
2237 DET.TAX_AMT_FUNCL_CURR,
2238 det.taxable_amt,
2239 det.taxable_amt_funcl_curr,
2240 ACT.ACTG_LINE_CCID,
2241 ACT.ACTG_EXT_LINE_ID
2242 FROM zx_rep_trx_detail_t det,
2243 zx_rep_actg_ext_t act
2244 WHERE det.extract_source_ledger = 'GL'
2245 AND det.request_id = c_request_id
2246 AND act.detail_tax_line_id(+) = det.detail_tax_line_id;
2247
2248 CURSOR account_type_cur(c_ccid number) IS
2249 SELECT account_type
2250 FROM gl_code_combinations
2251 WHERE code_combination_id = c_ccid;
2252
2253 l_balancing_segment VARCHAR2(25);
2254 l_accounting_segment VARCHAR2(25);
2255 l_count number;
2256 l_meaning VARCHAR2(80);
2257 l_description VARCHAR2(80);
2258 l_tax_amt NUMBER;
2259 l_tax_amt_funcl_curr NUMBER;
2260 l_taxable_amt number;
2261 l_taxable_amt_funcl_curr number;
2262 l_account_type VARCHAR2(1);
2263 BEGIN
2264
2265 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2266 g_request_id := P_TRL_GLOBAL_VARIABLES_REC.request_id;
2267
2268 IF (g_level_procedure >= g_current_runtime_level ) THEN
2269 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO.BEGIN',
2270 'ZX_GL_EXTRACT_PKG:UPDATE_ADDITIONAL_INFO(+)');
2271 END IF;
2272
2273 l_balancing_segment := fa_rx_flex_pkg.flex_sql(
2274 p_application_id =>101,
2275 p_id_flex_code => 'GL#',
2276 p_id_flex_num => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
2277 p_table_alias => '',
2278 p_mode => 'SELECT',
2279 p_qualifier => 'GL_BALANCING');
2280
2281 l_accounting_segment := fa_rx_flex_pkg.flex_sql(
2282 p_application_id =>101,
2283 p_id_flex_code => 'GL#',
2284 p_id_flex_num => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
2285 p_table_alias => '',
2286 p_mode => 'SELECT',
2287 p_qualifier => 'GL_ACCOUNT');
2288
2289 -- The above function will return balancing segment in the form CC.SEGMENT1
2290 -- we need to drop CC. to get the actual balancing segment.
2291
2292 l_balancing_segment := substrb(l_balancing_segment,
2293 instrb(l_balancing_segment,'.')+1);
2294
2295 OPEN detail_t_cur(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID);
2296 LOOP
2297 FETCH detail_t_cur BULK COLLECT INTO
2298 GT_DETAIL_TAX_LINE_ID,
2299 GT_LEDGER_ID,
2300 GT_INTERNAL_ORGANIZATION_ID,
2301 GT_TRX_ID,
2302 --GT_TRX_TYPE_ID,
2303 GT_TRX_CLASS,
2304 GT_TAX_RATE_VAT_TRX_TYPE_CODE,
2305 -- GT_TAX_RATE_REG_TYPE_CODE,
2306 GT_TAX_LINE_ID,
2307 GT_TRX_LINE_ID,
2308 -- GT_RECONCILIATION_FLAG,
2309 GT_TAX_REGIME_CODE,
2310 GT_TAX,
2311 GT_TAX_JURISDICTION_CODE,
2312 GT_TAX_RATE,
2313 GT_TAX_RATE_ID,
2314 GT_TAX_RATE_CODE,
2315 GT_TAX_TYPE_CODE,
2316 GT_TRX_DATE,
2317 GT_TRX_CURRENCY_CODE,
2318 GT_CURRENCY_CONVERSION_RATE,
2319 GT_APPLICATION_ID,
2320 GT_TAX_AMT,
2321 GT_TAX_AMT_FUNCL_CURR,
2322 gt_taxable_amt,
2323 gt_taxable_amt_funcl_curr,
2324 GT_ACTG_LINE_CCID,
2325 GT_ACTG_EXT_LINE_ID
2326 LIMIT C_LINES_PER_COMMIT;
2327
2328 l_count := nvl(GT_DETAIL_TAX_LINE_ID.COUNT,0);
2329
2330 FOR i IN 1..l_count
2331 LOOP
2332
2333 l_tax_amt := GT_TAX_AMT(i);
2334 l_tax_amt_funcl_curr := GT_TAX_AMT_FUNCL_CURR(i);
2335 l_taxable_amt := gt_taxable_amt(i);
2336 l_taxable_amt_funcl_curr := gt_taxable_amt_funcl_curr(i);
2337 --
2338 -- This code is added to populate tax_type_code (I or O) for new tax codes--
2339 --
2340 IF GT_TAX_TYPE_CODE(i) = 'T' THEN
2341 OPEN account_type_cur(GT_ACTG_LINE_CCID(i));
2342 FETCH account_type_cur into l_account_type;
2343 IF l_account_type IN ('A','E') THEN
2344 -- IF sign(GT_TAX_AMT(i)) = -1 THEN
2345 GT_TAX_TYPE_CODE(i) := 'I';
2346 GT_TAX_AMT(i) := l_tax_amt * -1;
2347 GT_TAX_AMT_FUNCL_CURR(i) := l_tax_amt_funcl_curr * -1;
2348 gt_taxable_amt(i) := l_taxable_amt * -1;
2349 gt_taxable_amt_funcl_curr(i) := l_taxable_amt_funcl_curr * -1;
2350 ELSE
2351 GT_TAX_TYPE_CODE(i) := 'O';
2352 END IF;
2353 CLOSE account_type_cur;
2354 END IF;
2355
2356 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := NULL;
2357 GT_TRX_CLASS_MNG(i) := NULL;
2358 GT_TAX_RATE_VAT_TRX_TYPE_MNG(i) := NULL;
2359 GT_TAX_TYPE_MNG(i) := NULL;
2360 GT_TAX_REG_NUM(i) := NULL;
2361
2362 IF GT_TAX_RATE_VAT_TRX_TYPE_CODE(i) IS NOT NULL THEN
2363 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_JEBE_VAT_TRANS_TYPE',
2364 GT_TAX_RATE_VAT_TRX_TYPE_CODE(i),
2365 l_meaning,
2366 l_description);
2367 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := l_description;
2368 GT_TAX_RATE_VAT_TRX_TYPE_MNG(i) := l_meaning;
2369 END IF;
2370 IF GT_TRX_CLASS(i) IS NOT NULL THEN
2371 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TRL_TAXABLE_TRX_TYPE',
2372 GT_TRX_CLASS(i),
2373 l_meaning,
2374 l_description);
2375 GT_TRX_CLASS_MNG(i) := l_meaning;
2376 END IF;
2377 IF GT_TAX_TYPE_CODE(i) IS NOT NULL THEN
2378 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TAX_TYPE_CATEGORY',
2379 GT_TAX_TYPE_CODE(i),
2380 l_meaning,
2381 l_description);
2382 GT_TAX_TYPE_MNG(i) := l_meaning;
2383 END IF;
2384
2385
2386
2387
2388 populate_tax_reg_num(
2389 P_TRL_GLOBAL_VARIABLES_REC,
2390 GT_TAX(i),
2391 GT_TAX_REGIME_CODE(i),
2392 GT_TAX_JURISDICTION_CODE(i),
2393 i );
2394
2395 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2396 FND_LOG.STRING(g_level_unexpected,
2397 'ZX.TRL.ZX_GL_POPULATE_PKG.populate_tax_reg_num.BEGIN',
2398 'tax '||GT_TAX(i)||' tax regime code '||GT_TAX_REGIME_CODE(i)||' Jurisdiction '||GT_TAX_JURISDICTION_CODE(i));
2399 END IF;
2400
2401 IF g_include_accounting_segments='Y' THEN
2402
2403 GET_ACCOUNTING_SEGMENTS (
2404 GT_TRX_ID(i),
2405 GT_TRX_LINE_ID(i),
2406 GT_TAX_RATE_ID(i),
2407 L_BALANCING_SEGMENT,
2408 L_ACCOUNTING_SEGMENT,
2409 P_TRL_GLOBAL_VARIABLES_REC.CHART_OF_ACCOUNTS_ID,
2410 GT_TRX_ARAP_BALANCING_SEGMENT(i),
2411 GT_TRX_ARAP_NATURAL_ACCOUNT(i),
2412 GT_TRX_TAXABLE_BAL_SEG(i),
2413 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),
2414 GT_TRX_TAX_BALANCING_SEGMENT(i),
2415 GT_TRX_TAX_NATURAL_ACCOUNT(i),
2416 GT_TRX_TAXABLE_BALSEG_DESC(i),
2417 GT_TRX_TAXABLE_NATACCT_DESC(i),
2418 GT_TRX_CONTROL_ACCFLEXFIELD(i),
2419 GT_TRX_TAXABLE_ACCOUNT_DESC(i)
2420 );
2421
2422 IF GT_ACTG_LINE_CCID(i) IS NOT NULL THEN
2423
2424 GT_ACCOUNT_FLEXFIELD(i) := FA_RX_FLEX_PKG.GET_VALUE(
2425 P_APPLICATION_ID => 101,
2426 P_ID_FLEX_CODE => 'GL#',
2427 P_ID_FLEX_NUM => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
2428 P_QUALIFIER => 'ALL',
2429 P_CCID => GT_ACTG_LINE_CCID(i));
2430
2431 IF (g_level_procedure >= g_current_runtime_level ) THEN
2432 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO',
2433 'Account Flexfield = '||GT_ACCOUNT_FLEXFIELD(i));
2434 END IF;
2435
2436 GT_ACCOUNT_DESCRIPTION(i) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2437 P_APPLICATION_ID => 101,
2438 P_ID_FLEX_CODE => 'GL#',
2439 P_ID_FLEX_NUM => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
2440 P_QUALIFIER => 'ALL',
2441 P_DATA => GT_ACCOUNT_FLEXFIELD(i));
2442
2443 IF (g_level_procedure >= g_current_runtime_level ) THEN
2444 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO',
2445 'Account Description = '||GT_ACCOUNT_DESCRIPTION(i));
2446 END IF;
2447
2448 END IF;
2449 END IF;
2450 END LOOP;
2451
2452 FORALL i in 1..l_count
2453 UPDATE ZX_REP_TRX_DETAIL_T SET
2454 TAX_RATE_VAT_TRX_TYPE_DESC = GT_TAX_RATE_VAT_TRX_TYPE_DESC(i),
2455 TRX_CLASS_MNG = GT_TRX_CLASS_MNG(i),
2456 TAX_RATE_CODE_VAT_TRX_TYPE_MNG = GT_TAX_RATE_VAT_TRX_TYPE_MNG(i),
2457 TAX_TYPE_MNG = GT_TAX_TYPE_MNG(i),
2458 HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(i),
2459 TAX_TYPE_CODE = GT_TAX_TYPE_CODE(i),
2460 TAX_AMT = GT_TAX_AMT(i),
2461 TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i),
2462 TAXABLE_AMT = GT_TAXABLE_AMT(i),
2463 TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i)
2464 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
2465
2466 IF g_include_accounting_segments='Y' THEN
2467 FORALL i IN 1 .. l_count
2468 UPDATE ZX_REP_ACTG_EXT_T
2469 SET trx_arap_balancing_segment = gt_trx_arap_balancing_segment(i),
2470 trx_arap_natural_account = gt_trx_arap_natural_account(i),
2471 trx_taxable_balancing_segment = gt_trx_taxable_bal_seg(i),
2472 trx_taxable_natural_account = gt_trx_taxable_natural_account(i),
2473 trx_tax_balancing_segment = gt_trx_tax_balancing_segment(i),
2474 trx_tax_natural_account = gt_trx_tax_natural_account(i),
2475 account_flexfield = gt_account_flexfield(i),
2476 account_description = gt_account_description(i),
2477 TRX_CONTROL_ACCOUNT_FLEXFIELD = GT_TRX_CONTROL_ACCFLEXFIELD(i),
2478 TRX_TAXABLE_ACCOUNT_DESC = GT_TRX_TAXABLE_ACCOUNT_DESC(i)
2479 WHERE actg_ext_line_id = gt_actg_ext_line_id(i);
2480 END IF;
2481
2482 EXIT WHEN detail_t_cur%NOTFOUND
2483 OR detail_t_cur%NOTFOUND IS NULL;
2484
2485 END LOOP;
2486
2487 IF (g_level_procedure >= g_current_runtime_level ) THEN
2488 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO.END',
2489 'ZX_GL_EXTRACT_PKG:UPDATE_ADDITIONAL_INFO(-)');
2490 END IF;
2491 END UPDATE_ADDITIONAL_INFO;
2492
2493 /*===========================================================================+
2494 | PROCEDURE |
2495 | get_accounting_segments |
2496 | |
2497 | DESCRIPTION |
2498 | This procedure of gets the Balancing and Natural Account segments |
2499 | For Taxable, Tax and Control Accounts for all manual tax journals |
2500 | |
2501 | |
2502 | SCOPE - Private |
2503 | |
2504 | NOTES |
2505 | |
2506 | MODIFICATION HISTORY |
2507 | 23-May-06 Vinit Doshi Created |
2508 | |
2509 +===========================================================================*/
2510
2511 PROCEDURE GET_ACCOUNTING_SEGMENTS (
2512 P_TRX_ID IN NUMBER,
2513 P_TRX_LINE_ID IN NUMBER,
2514 P_TAX_CODE_ID IN NUMBER,
2515 P_BALANCING_SEGMENT IN VARCHAR2,
2516 P_ACCOUNTING_SEGMENT IN VARCHAR2,
2517 P_CHART_OF_ACCOUNTS_ID IN NUMBER,
2518 P_TRX_ARAP_BALANCING_SEGMENT OUT NOCOPY VARCHAR2,
2519 P_TRX_ARAP_NATURAL_ACCOUNT OUT NOCOPY VARCHAR2,
2520 P_TRX_TAXABLE_BAL_SEG OUT NOCOPY VARCHAR2,
2521 P_TRX_TAXABLE_NATURAL_ACCOUNT OUT NOCOPY VARCHAR2,
2522 P_TRX_TAX_BALANCING_SEGMENT OUT NOCOPY VARCHAR2,
2523 P_TRX_TAX_NATURAL_ACCOUNT OUT NOCOPY VARCHAR2,
2524 P_TRX_TAXABLE_BALSEG_DESC OUT NOCOPY VARCHAR2,
2525 P_TRX_TAXABLE_NATACCT_DESC OUT NOCOPY varchar2,
2526 P_TRX_CONTROL_ACCFLEXFIELD OUT NOCOPY varchar2,
2527 P_TRX_TAXABLE_ACCOUNT_DESC OUT NOCOPY varchar2
2528 ) IS
2529
2530 L_BAL_SEG_VAL VARCHAR2(25);
2531 L_ACCT_SEG_VAL VARCHAR2(25);
2532
2533 L_BAL_SEG_DESC VARCHAR2(240);
2534 L_ACCT_SEG_DESC VARCHAR2(240);
2535
2536 L_SQL_STATEMENT1 VARCHAR2(200);
2537 L_SQL_STATEMENT2 VARCHAR2(200);
2538 L_CCID NUMBER;
2539 L_TAX_GROUP_ID NUMBER;
2540 L_TRX_ARAP_BALANCING_SEGMENT VARCHAR2(2000);
2541 L_TRX_ARAP_NATURAL_ACCOUNT VARCHAR2(2000);
2542 L_TRX_TAXABLE_BAL_SEG VARCHAR2(2000);
2543 L_TRX_TAXABLE_NATURAL_ACCOUNT VARCHAR2(2000);
2544 L_TRX_TAX_BALANCING_SEGMENT VARCHAR2(2000);
2545
2546 L_TRX_TAX_NATURAL_ACCOUNT VARCHAR2(2000);
2547 L_TRX_CONTROL_ACCFLEXFIELD VARCHAR2(2000);
2548
2549 L_TRX_TAXABLE_ACCOUNT_DESC VARCHAR2(2000);
2550 L_TRX_TAXABLE_BALSEG_DESC VARCHAR2(2000);
2551 L_TRX_TAXABLE_NATACCT_DESC VARCHAR2(2000);
2552
2553 CURSOR TAXABLE_ACCT_CURSOR(C_TRX_ID IN NUMBER,
2554 C_TAX_CODE_ID IN NUMBER,
2555 C_TRX_LINE_ID IN NUMBER) IS
2556 SELECT CODE_COMBINATION_ID, TAX_GROUP_ID
2557 FROM GL_JE_LINES
2558 WHERE JE_HEADER_ID = C_TRX_ID
2559 AND TAX_CODE_ID = C_TAX_CODE_ID
2560 AND JE_LINE_NUM = C_TRX_LINE_ID;
2561
2562 CURSOR TAX_ACCT_CURSOR(C_TRX_ID IN NUMBER,
2563 C_TAX_GROUP_ID IN NUMBER,
2564 C_TRX_LINE_ID IN NUMBER) IS
2565 SELECT CODE_COMBINATION_ID
2566 FROM GL_JE_LINES
2567 WHERE JE_HEADER_ID = C_TRX_ID
2568 AND TAX_GROUP_ID = C_TAX_GROUP_ID
2569 AND TAX_CODE_ID IS NULL
2570 AND JE_LINE_NUM = C_TRX_LINE_ID;
2571
2572 CURSOR CONTROL_ACCT_CURSOR(C_TRX_ID IN NUMBER,
2573 C_TRX_LINE_ID IN NUMBER) IS
2574 SELECT CODE_COMBINATION_ID
2575 FROM GL_JE_LINES
2576 WHERE JE_HEADER_ID = C_TRX_ID
2577 AND TAX_GROUP_ID IS NULL
2578 AND JE_LINE_NUM = C_TRX_LINE_ID;
2579
2580 BEGIN
2581
2582 IF (g_level_procedure >= g_current_runtime_level ) THEN
2583 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS.BEGIN',
2584 'ZX_GL_EXTRACT_PKG: GET_ACCOUNTING_SEGMENTS(+)');
2585 END IF;
2586
2587 L_TRX_ARAP_BALANCING_SEGMENT := NULL;
2588 L_TRX_ARAP_NATURAL_ACCOUNT := NULL;
2589 L_TRX_TAXABLE_BAL_SEG := NULL;
2590 L_TRX_TAXABLE_NATURAL_ACCOUNT := NULL;
2591 L_TRX_TAX_BALANCING_SEGMENT := NULL;
2592 L_TRX_TAX_NATURAL_ACCOUNT := NULL;
2593
2594 L_TRX_CONTROL_ACCFLEXFIELD := NULL ;
2595 L_TRX_TAXABLE_ACCOUNT_DESC := NULL;
2596
2597 L_TRX_TAXABLE_BALSEG_DESC := NULL ;
2598 L_TRX_TAXABLE_NATACCT_DESC := NULL ;
2599
2600 L_BAL_SEG_VAL := '';
2601 L_ACCT_SEG_VAL := '';
2602
2603 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
2604 ' FROM GL_CODE_COMBINATIONS '||
2605 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
2606
2607 L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
2608 ' FROM GL_CODE_COMBINATIONS '||
2609 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
2610
2611
2612 OPEN TAXABLE_ACCT_CURSOR(P_TRX_ID, P_TAX_CODE_ID, P_TRX_LINE_ID);
2613 LOOP
2614 FETCH TAXABLE_ACCT_CURSOR INTO L_CCID, L_TAX_GROUP_ID ;
2615 EXIT WHEN TAXABLE_ACCT_CURSOR%NOTFOUND;
2616
2617 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2618 USING L_CCID;
2619
2620 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2621 USING L_CCID;
2622
2623 IF (g_level_procedure >= g_current_runtime_level ) THEN
2624 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2625 'trx_id - tax_code_id - ccid - L_ACCT_SEG_VAL '||to_char(P_TRX_ID)||'-'||to_char(P_TRX_LINE_ID)||to_char(P_TAX_CODE_ID) ||'-'||to_char(L_CCID)||L_ACCT_SEG_VAL);
2626 END IF;
2627
2628
2629 IF l_ccid IS NOT NULL THEN
2630
2631 L_TRX_CONTROL_ACCFLEXFIELD := FA_RX_FLEX_PKG.GET_VALUE(
2632 P_APPLICATION_ID => 101,
2633 P_ID_FLEX_CODE => 'GL#',
2634 P_ID_FLEX_NUM => P_CHART_OF_ACCOUNTS_ID,
2635 P_QUALIFIER => 'ALL',
2636 P_CCID => l_ccid);
2637
2638 IF (g_level_procedure >= g_current_runtime_level ) THEN
2639 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2640 'Taxable Line Account Flexfield = '||L_TRX_CONTROL_ACCFLEXFIELD);
2641 END IF;
2642
2643 L_TRX_TAXABLE_ACCOUNT_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2644 P_APPLICATION_ID => 101,
2645 P_ID_FLEX_CODE => 'GL#',
2646 P_ID_FLEX_NUM => P_CHART_OF_ACCOUNTS_ID,
2647 P_QUALIFIER => 'ALL',
2648 P_DATA => L_TRX_CONTROL_ACCFLEXFIELD);
2649
2650 IF (g_level_procedure >= g_current_runtime_level ) THEN
2651 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2652 'Account Description for Taxable Line CCID = '||L_TRX_TAXABLE_ACCOUNT_DESC);
2653 END IF;
2654
2655 END IF ;
2656
2657 --Bug 5650415
2658 IF L_BAL_SEG_VAL IS NOT NULL THEN
2659 L_BAL_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2660 P_APPLICATION_ID => 101,
2661 P_ID_FLEX_CODE => 'GL#',
2662 P_ID_FLEX_NUM => P_CHART_OF_ACCOUNTS_ID,
2663 P_QUALIFIER => 'GL_BALANCING',
2664 P_DATA => L_BAL_SEG_VAL);
2665 END IF;
2666
2667 IF L_ACCT_SEG_VAL IS NOT NULL THEN
2668 L_ACCT_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2669 P_APPLICATION_ID => 101,
2670 P_ID_FLEX_CODE => 'GL#',
2671 P_ID_FLEX_NUM => P_CHART_OF_ACCOUNTS_ID,
2672 P_QUALIFIER => 'GL_ACCOUNT',
2673 P_DATA => L_ACCT_SEG_VAL);
2674 END IF;
2675
2676
2677 IF L_TRX_ARAP_BALANCING_SEGMENT IS NULL then
2678 L_TRX_ARAP_BALANCING_SEGMENT := L_BAL_SEG_VAL;
2679 L_TRX_TAXABLE_BALSEG_DESC := L_BAL_SEG_DESC;
2680 ELSE
2681 IF INSTRB(L_TRX_ARAP_BALANCING_SEGMENT,L_BAL_SEG_VAL) > 0 THEN
2682 NULL;
2683 ELSE
2684 L_TRX_ARAP_BALANCING_SEGMENT := L_TRX_ARAP_BALANCING_SEGMENT
2685 ||','||L_BAL_SEG_VAL;
2686 L_TRX_TAXABLE_BALSEG_DESC := L_TRX_TAXABLE_BALSEG_DESC||','||L_BAL_SEG_DESC;
2687 END IF;
2688 END IF;
2689
2690 L_TRX_TAXABLE_BAL_SEG := L_TRX_ARAP_BALANCING_SEGMENT;
2691
2692 IF L_TRX_ARAP_NATURAL_ACCOUNT IS NULL then
2693 L_TRX_ARAP_NATURAL_ACCOUNT := L_ACCT_SEG_VAL ;
2694 L_TRX_TAXABLE_NATACCT_DESC := L_ACCT_SEG_DESC;
2695 ELSE
2696 IF INSTRB(L_TRX_ARAP_NATURAL_ACCOUNT,L_ACCT_SEG_VAL) > 0 THEN
2697 NULL;
2698 ELSE
2699 L_TRX_ARAP_NATURAL_ACCOUNT := L_TRX_ARAP_NATURAL_ACCOUNT
2700 ||','||L_ACCT_SEG_VAL;
2701 L_TRX_TAXABLE_NATACCT_DESC := L_TRX_TAXABLE_NATACCT_DESC||','||L_ACCT_SEG_DESC;
2702 END IF;
2703 END IF;
2704 L_TRX_TAXABLE_NATURAL_ACCOUNT := L_TRX_ARAP_NATURAL_ACCOUNT;
2705 END LOOP;
2706
2707 IF (g_level_procedure >= g_current_runtime_level ) THEN
2708 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2709 'Trx Balancing Segment = '||L_TRX_ARAP_BALANCING_SEGMENT);
2710 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2711 'Trx Natural A/c Segment = '||L_TRX_ARAP_NATURAL_ACCOUNT);
2712 END IF;
2713
2714 IF TAXABLE_ACCT_CURSOR%ISOPEN THEN
2715 CLOSE TAXABLE_ACCT_CURSOR;
2716 END IF;
2717
2718 L_BAL_SEG_VAL := '';
2719 L_ACCT_SEG_VAL := '';
2720
2721
2722 OPEN TAX_ACCT_CURSOR(P_TRX_ID, L_TAX_GROUP_ID, P_TRX_LINE_ID);
2723
2724 LOOP
2725 FETCH TAX_ACCT_CURSOR INTO L_CCID;
2726 EXIT WHEN TAX_ACCT_CURSOR%NOTFOUND;
2727
2728 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2729 USING L_CCID;
2730
2731 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2732 USING L_CCID;
2733
2734 IF L_TRX_TAX_BALANCING_SEGMENT IS NULL then
2735 L_TRX_TAX_BALANCING_SEGMENT := L_BAL_SEG_VAL;
2736 ELSE
2737 IF INSTRB(L_TRX_TAX_BALANCING_SEGMENT,L_BAL_SEG_VAL) > 0 THEN
2738 NULL;
2739 ELSE
2740 L_TRX_TAX_BALANCING_SEGMENT := L_TRX_TAX_BALANCING_SEGMENT
2741 ||','||L_BAL_SEG_VAL;
2742 END IF;
2743 END IF;
2744 IF L_TRX_TAX_NATURAL_ACCOUNT IS NULL then
2745 L_TRX_TAX_NATURAL_ACCOUNT := L_ACCT_SEG_VAL ;
2746 ELSE
2747 IF INSTRB(L_TRX_TAX_NATURAL_ACCOUNT,L_ACCT_SEG_VAL) > 0 THEN
2748 NULL;
2749 ELSE
2750 L_TRX_TAX_NATURAL_ACCOUNT :=
2751 L_TRX_TAX_NATURAL_ACCOUNT ||','
2752 ||L_ACCT_SEG_VAL;
2753 END IF;
2754 END IF;
2755
2756 END LOOP;
2757
2758 IF (g_level_procedure >= g_current_runtime_level ) THEN
2759 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2760 'Taxable Balancing Segment = '||L_TRX_TAXABLE_BAL_SEG);
2761 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2762 'Taxable Natural A/c Segment = '||L_TRX_TAXABLE_NATURAL_ACCOUNT);
2763 END IF;
2764
2765 IF TAX_ACCT_CURSOR%ISOPEN THEN
2766 CLOSE TAX_ACCT_CURSOR;
2767 END IF;
2768
2769 L_BAL_SEG_VAL := '';
2770 L_ACCT_SEG_VAL := '';
2771 /*
2772 OPEN CONTROL_ACCT_CURSOR(P_TRX_ID);
2773
2774 LOOP
2775 FETCH CONTROL_ACCT_CURSOR INTO L_CCID;
2776 EXIT WHEN CONTROL_ACCT_CURSOR%NOTFOUND;
2777
2778 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2779 USING L_CCID;
2780
2781 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2782 USING L_CCID;
2783
2784 IF L_TRX_TAX_BALANCING_SEGMENT IS NULL then
2785 L_TRX_TAX_BALANCING_SEGMENT := L_BAL_SEG_VAL;
2786 ELSE
2787 IF INSTRB(L_TRX_TAX_BALANCING_SEGMENT,L_BAL_SEG_VAL) > 0 THEN
2788 NULL;
2789 ELSE
2790 L_TRX_TAX_BALANCING_SEGMENT := L_TRX_TAX_BALANCING_SEGMENT
2791 ||','||L_BAL_SEG_VAL;
2792 END IF;
2793 END IF;
2794
2795 IF L_TRX_TAX_NATURAL_ACCOUNT IS NULL then
2796 L_TRX_TAX_NATURAL_ACCOUNT := L_ACCT_SEG_VAL ;
2797 ELSE
2798 IF INSTRB(L_TRX_TAX_NATURAL_ACCOUNT,L_ACCT_SEG_VAL) > 0 THEN
2799 NULL;
2800 ELSE
2801 L_TRX_TAX_NATURAL_ACCOUNT := L_TRX_TAX_NATURAL_ACCOUNT ||','
2802 ||L_ACCT_SEG_VAL;
2803 END If;
2804 END IF;
2805
2806 END LOOP;
2807 */
2808 IF (g_level_procedure >= g_current_runtime_level ) THEN
2809 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2810 'Tax Balancing Segment = '||L_TRX_TAX_BALANCING_SEGMENT);
2811 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2812 'Tax Natural A/c Segment = '||L_TRX_TAX_NATURAL_ACCOUNT);
2813 END IF;
2814
2815 /* IF CONTROL_ACCT_CURSOR%ISOPEN THEN
2816 CLOSE CONTROL_ACCT_CURSOR;
2817 END IF;
2818 */
2819 P_TRX_ARAP_BALANCING_SEGMENT := L_TRX_ARAP_BALANCING_SEGMENT;
2820 P_TRX_ARAP_NATURAL_ACCOUNT := L_TRX_ARAP_NATURAL_ACCOUNT;
2821 P_TRX_TAXABLE_BAL_SEG := L_TRX_TAXABLE_BAL_SEG;
2822 P_TRX_TAXABLE_NATURAL_ACCOUNT := L_TRX_TAXABLE_NATURAL_ACCOUNT ;
2823 P_TRX_TAX_BALANCING_SEGMENT := L_TRX_TAX_BALANCING_SEGMENT ;
2824 P_TRX_TAX_NATURAL_ACCOUNT := L_TRX_TAX_NATURAL_ACCOUNT ;
2825
2826 P_TRX_CONTROL_ACCFLEXFIELD := L_TRX_CONTROL_ACCFLEXFIELD ;
2827 P_TRX_TAXABLE_ACCOUNT_DESC := L_TRX_TAXABLE_ACCOUNT_DESC;
2828
2829 P_TRX_TAXABLE_BALSEG_DESC := L_TRX_TAXABLE_BALSEG_DESC;
2830 P_TRX_TAXABLE_NATACCT_DESC := L_TRX_TAXABLE_NATACCT_DESC;
2831
2832 IF (g_level_procedure >= g_current_runtime_level ) THEN
2833 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS.END',
2834 'ZX_GL_EXTRACT_PKG: GET_ACCOUNTING_SEGMENTS(-)');
2835 END IF;
2836
2837 EXCEPTION
2838 WHEN OTHERS THEN
2839 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2840 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2841 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_accounting_segments- '|| g_error_buffer);
2842 FND_MSG_PUB.Add;
2843 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2844 FND_LOG.STRING(g_level_unexpected,
2845 'ZX.TRL.ZX_GL_EXTRACT_PKG.GET_ACCOUNTING_SEGMENTS',
2846 g_error_buffer);
2847 END IF;
2848
2849 IF TAXABLE_ACCT_CURSOR%ISOPEN THEN
2850 CLOSE TAXABLE_ACCT_CURSOR;
2851 END IF;
2852
2853 IF TAX_ACCT_CURSOR%ISOPEN THEN
2854 CLOSE TAX_ACCT_CURSOR;
2855 END IF;
2856
2857 IF CONTROL_ACCT_CURSOR%ISOPEN THEN
2858 CLOSE CONTROL_ACCT_CURSOR;
2859 END IF;
2860
2861 G_GL_RETCODE := 2;
2862
2863 END GET_ACCOUNTING_SEGMENTS ;
2864
2865
2866 PROCEDURE populate_tax_reg_num(
2867 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
2868 P_TAX IN zx_rates_vl.tax%TYPE,
2869 P_TAX_REGIME_CODE IN zx_rates_vl.tax_regime_code%TYPE,
2870 P_TAX_JURISDICTION_CODE IN zx_rates_vl.tax_jurisdiction_code%TYPE,
2871 i IN BINARY_INTEGER) IS
2872
2873 l_ptp_id number;
2874 l_return_status varchar2(30);
2875 BEGIN
2876
2877 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2878 FND_LOG.STRING(g_level_unexpected,
2879 'ZX.TRL.ZX_GL_POPULATE_PKG.populate_tax_reg_num.BEGIN',
2880 'populate_tax_reg_num(+) ');
2881 END IF;
2882
2883 IF P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id is NULL THEN
2884
2885 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2886 FND_LOG.STRING(g_level_unexpected,
2887 'ZX.TRL.ZX_GL_POPULATE_PKG.populate_tax_reg_num.BEGIN',
2888 'Null LE ID Found');
2889 FND_LOG.STRING(g_level_unexpected,
2890 'ZX.TRL.ZX_GL_POPULATE_PKG.populate_tax_reg_num.END',
2891 'populate_tax_reg_num(-) ');
2892 END IF;
2893 GT_TAX_REG_NUM(i) := NULL;
2894 return;
2895 END IF;
2896
2897 ZX_TCM_PTP_PKG.GET_PTP_HQ( P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id,
2898 l_ptp_id,
2899 l_return_status);
2900 Begin
2901 SELECT registration_number
2902 INTO GT_TAX_REG_NUM(i)
2903 FROM zx_registrations reg
2904 WHERE reg.party_tax_profile_id = l_ptp_id
2905 AND nvl(reg.tax_regime_code,1) = nvl(p_tax_regime_code,1)
2906 AND nvl(reg.tax,nvl(p_tax,1)) = nvl(p_tax,1)
2907 AND nvl(reg.tax_jurisdiction_code,nvl(p_tax_jurisdiction_code,1)) = nvl(p_tax_jurisdiction_code,1)
2908 AND sysdate >= reg.effective_from
2909 AND (sysdate <= reg.effective_to OR reg.effective_to IS NULL);
2910
2911
2912 EXCEPTION
2913 WHEN TOO_MANY_ROWS THEN
2914 GT_TAX_REG_NUM(i) := NULL;
2915 WHEN NO_DATA_FOUND THEN
2916 GT_TAX_REG_NUM(i) := NULL;
2917 END;
2918 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2919 FND_LOG.STRING(g_level_unexpected,
2920 'ZX.TRL.ZX_GL_POPULATE_PKG.populate_tax_reg_num.END',
2921 'populate_tax_reg_num(-) ');
2922 END IF;
2923 END populate_tax_reg_num;
2924
2925 END ZX_GL_EXTRACT_PKG;