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