[Home] [Help]
PACKAGE BODY: APPS.ZX_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_EXTRACT_PKG AS
2 /* $Header: zxriextractwpkgb.pls 120.43.12010000.2 2008/11/12 12:58:45 spasala ship $ */
3
4 C_LINES_PER_INSERT CONSTANT NUMBER := 1000;
5
6 -----------------------------------------
7 --Public Variable Declarations
8 -----------------------------------------
9 --
10 -- Define Global Variables;
11
12 --pr_org_loc_rec HR_LOCATIONS_V%ROWTYPE;
13 pr_flexfield FND_DFLEX.DFLEX_R;
14 pr_flexinfo FND_DFLEX.DFLEX_DR;
15 pg_sql varchar2(32000) := null;
16 pg_table_owner VARCHAR2(30);
17 g_ledger_type VARCHAR2(1);
18 g_ledger_id NUMBER(15);
19 g_rep_ledger_id NUMBER(15);
20
21 L_SQL_STATEMENT VARCHAR2(32600);
22 L_VERSION_INFO VARCHAR2(80) := NULL;
23 L_PARAMLIST VARCHAR2(4000);
24 L_ERRBUF VARCHAR2(2000);
25 L_LENGTH_ERRBUF NUMBER;
26 L_RETCODE NUMBER := 0;
27 L_AP_RETCODE NUMBER := 0;
28 L_AR_RETCODE NUMBER := 0;
29 L_GL_RETCODE NUMBER := 0;
30 L_MSG VARCHAR2(500);
31 g_created_by number(15);
32 g_creation_date date;
33 g_last_updated_by number(15);
34 g_last_update_date date;
35 g_last_update_login number(15);
36 g_rep_context_city hr_locations_all.town_or_city%type;
37 g_rep_context_region_1 hr_locations_all.region_3%type;
38 g_rep_context_region_2 hr_locations_all.region_2%type;
39 g_rep_context_region_3 hr_locations_all.region_1%type;
40 g_rep_context_address1 hr_locations_all.address_line_1%type;
41 g_rep_context_address2 hr_locations_all.address_line_2%type;
42 g_rep_context_address3 hr_locations_all.address_line_3%type;
43 g_rep_context_country hr_locations_all.country%type;
44 g_rep_context_postal_code hr_locations_all.postal_code%type;
45 g_rep_context_phone_number hr_locations_all.telephone_number_1%type;
46
47 l_multi_org_flag fnd_product_groups.multi_org_flag%type;
48 l_mrc_reporting_sob_id NUMBER;
49 g_balancing_segment VARCHAR2(25);
50 g_currency_code VARCHAR2(15);
51 l_accounting_segment VARCHAR2(25);
52
53 g_current_runtime_level NUMBER ;
54 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
55 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
56 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
57 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
58 g_error_buffer VARCHAR2(100);
59
60 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
61
62
63 CURSOR multi_org_csr IS
64 SELECT multi_org_flag
65 FROM fnd_product_groups;
66
67
68 TYPE l_sql_statement_tabtype is table of varchar2(32600)
69 index by binary_integer;
70 l_sql_statement_tbl l_sql_statement_tabtype;
71
72
73 -----------------------------------------
74 --Forward Private Methods Declarations
75 -----------------------------------------
76
77
78 PROCEDURE stack_error (
79 p_application VARCHAR2,
80 p_msgname VARCHAR2,
81 p_token1 VARCHAR2 DEFAULT NULL,
82 p_value1 VARCHAR2 DEFAULT NULL,
83 p_token2 VARCHAR2 DEFAULT NULL,
84 p_value2 VARCHAR2 DEFAULT NULL,
85 p_token3 VARCHAR2 DEFAULT NULL,
86 p_value3 VARCHAR2 DEFAULT NULL );
87
88 PROCEDURE EXTRACT_TAX_INFO (
89 p_ledger_type IN VARCHAR2,
90 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE);
91
92 PROCEDURE extract_rep_context_info(
93 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE);
94
95 FUNCTION validate_parameters (
96 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
97 ) return boolean;
98
99 PROCEDURE extract_additional_info (
100 P_ledger_type IN VARCHAR2,
101 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE);
102
103 PROCEDURE derive_dependent_parameters (
104 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE);
105
106 PROCEDURE cleanup (
107 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE);
108
109 PROCEDURE delete_all(p_request_id in number );
110
111 /*PROCEDURE build_gbl_lookup_table; */
112
113 FUNCTION location_value(p_column in varchar2) return varchar2;
114
115 FUNCTION get_location_column(p_style IN VARCHAR2,
116 p_classification IN VARCHAR2)
117 return varchar2;
118
119 FUNCTION convert_string(p_string IN VARCHAR2) return varchar2;
120
121 PROCEDURE insert_rep_context_itf(
122 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
123 p_rep_entity_id IN NUMBER );
124
125 PROCEDURE initialize(
126 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
127 P_REPORTING_LEVEL IN VARCHAR2,
128 P_REPORTING_CONTEXT IN VARCHAR2,
129 -- apai P_LEGAL_ENTITY_LEVEL IN VARCHAR2,
130 P_LEGAL_ENTITY_ID IN NUMBER , -- apai COMPANY_NAME
131 P_SUMMARY_LEVEL IN VARCHAR2,
132 P_LEDGER_ID IN NUMBER ,
133 P_REGISTER_TYPE IN VARCHAR2,
134 P_PRODUCT IN VARCHAR2,
135 P_MATRIX_REPORT IN VARCHAR2,
136 P_DETAIL_LEVEL IN VARCHAR2,
137 P_CURRENCY_CODE_LOW IN VARCHAR2,
138 P_CURRENCY_CODE_HIGH IN VARCHAR2,
139 P_INCLUDE_AP_STD_TRX_CLASS IN VARCHAR2,
140 P_INCLUDE_AP_DM_TRX_CLASS IN VARCHAR2,
141 P_INCLUDE_AP_CM_TRX_CLASS IN VARCHAR2,
142 P_INCLUDE_AP_PREP_TRX_CLASS IN VARCHAR2,
143 P_INCLUDE_AP_MIX_TRX_CLASS IN VARCHAR2,
144 P_INCLUDE_AP_EXP_TRX_CLASS IN VARCHAR2,
145 P_INCLUDE_AP_INT_TRX_CLASS IN VARCHAR2,
146 P_INCLUDE_AR_INV_TRX_CLASS IN VARCHAR2,
147 P_INCLUDE_AR_APPL_TRX_CLASS IN VARCHAR2,
148 P_INCLUDE_AR_ADJ_TRX_CLASS IN VARCHAR2,
149 P_INCLUDE_AR_MISC_TRX_CLASS IN VARCHAR2,
150 P_INCLUDE_AR_BR_TRX_CLASS IN VARCHAR2,
151 P_INCLUDE_GL_MANUAL_LINES IN VARCHAR2,
152 P_THIRD_PARTY_REP_LEVEL IN VARCHAR2,
153 P_FIRST_PARTY_TAX_REG_NUM IN VARCHAR2,
154 P_TRX_NUMBER_LOW IN VARCHAR2,
155 P_TRX_NUMBER_HIGH IN VARCHAR2,
156 P_EXTRACT_REPORT_LINE_NUMBER IN NUMBER ,
157 P_AR_TRX_PRINTING_STATUS IN VARCHAR2,
158 P_AR_EXEMPTION_STATUS IN VARCHAR2,
159 P_GL_DATE_LOW IN DATE ,
160 P_GL_DATE_HIGH IN DATE ,
161 P_TRX_DATE_LOW IN DATE ,
162 P_TRX_DATE_HIGH IN DATE ,
163 P_GL_PERIOD_NAME_LOW IN VARCHAR2,
164 P_GL_PERIOD_NAME_HIGH IN VARCHAR2,
165 P_TRX_DATE_PERIOD_NAME_LOW IN VARCHAR2,
166 P_TRX_DATE_PERIOD_NAME_HIGH IN VARCHAR2,
167 P_TAX_JURISDICTION_CODE IN VARCHAR ,
168 P_TAX_REGIME_CODE IN VARCHAR2,
169 P_TAX IN VARCHAR2,
170 P_TAX_STATUS_CODE IN VARCHAR2,
171 P_TAX_RATE_CODE_LOW IN VARCHAR2,
172 P_TAX_RATE_CODE_HIGH IN VARCHAR2,
173 P_TAX_TYPE_CODE_LOW IN VARCHAR2,
174 P_TAX_TYPE_CODE_HIGH IN VARCHAR2,
175 P_DOCUMENT_SUB_TYPE IN VARCHAR2,
176 P_TRX_BUSINESS_CATEGORY IN VARCHAR2,
177 P_TAX_INVOICE_DATE_LOW IN VARCHAR2,
178 P_TAX_INVOICE_DATE_HIGH IN VARCHAR2,
179 P_POSTING_STATUS IN VARCHAR2,
180 P_EXTRACT_ACCTED_TAX_LINES IN VARCHAR2,
181 P_INCLUDE_ACCOUNTING_SEGMENTS IN VARCHAR2,
182 P_BALANCING_SEGMENT_LOW IN VARCHAR2,
183 P_BALANCING_SEGMENT_HIGH IN VARCHAR2,
184 P_INCLUDE_DISCOUNTS IN VARCHAR2,
185 P_EXTRACT_STARTING_LINE_NUM IN NUMBER ,
186 P_REQUEST_ID IN NUMBER ,
187 P_REPORT_NAME IN VARCHAR2,
188 P_VAT_TRANSACTION_TYPE_CODE IN VARCHAR2,
189 P_INCLUDE_FULLY_NR_TAX_FLAG IN VARCHAR2,
190 P_MUNICIPAL_TAX_TYPE_CODE_LOW IN VARCHAR2,
191 P_MUNICIPAL_TAX_TYPE_CODE_HIGH IN VARCHAR2,
192 P_PROV_TAX_TYPE_CODE_LOW IN VARCHAR2,
193 P_PROV_TAX_TYPE_CODE_HIGH IN VARCHAR2,
194 P_EXCISE_TAX_TYPE_CODE_LOW IN VARCHAR2,
195 P_EXCISE_TAX_TYPE_CODE_HIGH IN VARCHAR2,
196 P_NON_TAXABLE_TAX_TYPE_CODE IN VARCHAR2,
197 P_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
198 P_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
199 P_FED_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
200 P_FED_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
201 P_VAT_TAX_TYPE_CODE IN VARCHAR2,
202 P_EXCISE_TAX IN VARCHAR2,
203 P_VAT_ADDITIONAL_TAX IN VARCHAR2,
204 P_VAT_NON_TAXABLE_TAX IN VARCHAR2,
205 P_VAT_NOT_TAX IN VARCHAR2,
206 P_VAT_PERCEPTION_TAX IN VARCHAR2,
207 P_VAT_TAX IN VARCHAR2,
208 P_INC_SELF_WD_TAX IN VARCHAR2,
209 P_EXCLUDING_TRX_LETTER IN VARCHAR2,
210 P_TRX_LETTER_LOW IN VARCHAR2,
211 P_TRX_LETTER_HIGH IN VARCHAR2,
212 P_INCLUDE_REFERENCED_SOURCE IN VARCHAR2,
213 P_PARTY_NAME IN VARCHAR2,
214 P_BATCH_NAME IN VARCHAR2,
215 P_BATCH_DATE_LOW IN DATE ,
216 P_BATCH_DATE_HIGH IN DATE ,
217 P_BATCH_SOURCE_ID IN VARCHAR2,
218 P_ADJUSTED_DOC_FROM IN VARCHAR2,
219 P_ADJUSTED_DOC_TO IN VARCHAR2,
220 P_STANDARD_VAT_TAX_RATE IN VARCHAR2,
221 P_MUNICIPAL_TAX IN VARCHAR2,
222 P_PROVINCIAL_TAX IN VARCHAR2,
223 P_TAX_ACCOUNT_LOW IN VARCHAR2,
224 P_TAX_ACCOUNT_HIGH IN VARCHAR2,
225 P_EXP_CERT_DATE_FROM IN DATE ,
226 P_EXP_CERT_DATE_TO IN DATE ,
227 P_EXP_METHOD IN VARCHAR2,
228 P_PRINT_COMPANY_INFO IN VARCHAR2,
229 P_ORDER_BY IN VARCHAR2,
230 P_CHART_OF_ACCOUNTS_ID IN NUMBER ,
231 P_REPRINT IN VARCHAR2,
232 P_ERRBUF IN OUT NOCOPY VARCHAR2,
233 P_RETCODE IN OUT NOCOPY VARCHAR2,
234 P_ACCOUNTING_STATUS IN VARCHAR2,
235 P_REPORTED_STATUS IN VARCHAR2,
236 P_TAXABLE_ACCOUNT_LOW IN VARCHAR2,
237 P_TAXABLE_ACCOUNT_HIGH IN VARCHAR2,
238 P_GL_OR_TRX_DATE_FILTER IN VARCHAR2 ); --Bug 5396444
239
240 -----------------------------------------
241 --Public Methods Declarations
242 -----------------------------------------
243 --
244 /*===========================================================================+
245 | PROCEDURE |
246 | populate_tax_data() |
247 | |
248 | DESCRIPTION |
249 | This procedure calls AP,AR,and GL extract and populate packages to |
250 | populate Tax extract interface tables. |
251 | |
252 | SCOPE - Public |
253 | |
254 | NOTES |
255 | |
256 | MODIFICATION HISTORY |
257 | 12-FEB-2005 Srinivasa Rao Korrapati Created |
258 | |
259 +===========================================================================*/
260
261 PROCEDURE populate_tax_data (
262 P_REPORTING_LEVEL IN VARCHAR2 DEFAULT NULL,
263 P_REPORTING_CONTEXT IN VARCHAR2 DEFAULT NULL,
264 -- apai P_LEGAL_ENTITY_LEVEL IN VARCHAR2 DEFAULT NULL,
265 P_LEGAL_ENTITY_ID IN NUMBER DEFAULT NULL, -- apai COMPANY_NAME
266 P_SUMMARY_LEVEL IN VARCHAR2 DEFAULT NULL,
267 P_LEDGER_ID IN NUMBER DEFAULT NULL,
268 P_REGISTER_TYPE IN VARCHAR2 DEFAULT NULL,
269 P_PRODUCT IN VARCHAR2 DEFAULT NULL,
270 P_MATRIX_REPORT IN VARCHAR2 DEFAULT NULL,
271 P_DETAIL_LEVEL IN VARCHAR2 DEFAULT NULL,
272 P_CURRENCY_CODE_LOW IN VARCHAR2 DEFAULT NULL,
273 P_CURRENCY_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
274 P_INCLUDE_AP_STD_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
275 P_INCLUDE_AP_DM_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
276 P_INCLUDE_AP_CM_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
277 P_INCLUDE_AP_PREP_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
278 P_INCLUDE_AP_MIX_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
279 P_INCLUDE_AP_EXP_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
280 P_INCLUDE_AP_INT_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
281 P_INCLUDE_AR_INV_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
282 P_INCLUDE_AR_APPL_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
283 P_INCLUDE_AR_ADJ_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
284 P_INCLUDE_AR_MISC_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
285 P_INCLUDE_AR_BR_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
286 P_INCLUDE_GL_MANUAL_LINES IN VARCHAR2 DEFAULT NULL,
287 P_THIRD_PARTY_REP_LEVEL IN VARCHAR2 DEFAULT NULL,
288 P_FIRST_PARTY_TAX_REG_NUM IN VARCHAR2 DEFAULT NULL,
289 P_TRX_NUMBER_LOW IN VARCHAR2 DEFAULT NULL,
290 P_TRX_NUMBER_HIGH IN VARCHAR2 DEFAULT NULL,
291 P_EXTRACT_REPORT_LINE_NUMBER IN NUMBER DEFAULT 1,
292 P_AR_TRX_PRINTING_STATUS IN VARCHAR2 DEFAULT NULL,
293 P_AR_EXEMPTION_STATUS IN VARCHAR2 DEFAULT NULL,
294 P_GL_DATE_LOW IN DATE DEFAULT NULL,
295 P_GL_DATE_HIGH IN DATE DEFAULT NULL,
296 P_TRX_DATE_LOW IN DATE DEFAULT NULL,
297 P_TRX_DATE_HIGH IN DATE DEFAULT NULL,
298 P_GL_PERIOD_NAME_LOW IN VARCHAR2 DEFAULT NULL,
299 P_GL_PERIOD_NAME_HIGH IN VARCHAR2 DEFAULT NULL,
300 P_TRX_DATE_PERIOD_NAME_LOW IN VARCHAR2 DEFAULT NULL,
301 P_TRX_DATE_PERIOD_NAME_HIGH IN VARCHAR2 DEFAULT NULL,
302 P_TAX_JURISDICTION_CODE IN VARCHAR DEFAULT NULL,
303 P_TAX_REGIME_CODE IN VARCHAR2 DEFAULT NULL,
304 P_TAX IN VARCHAR2 DEFAULT NULL,
305 P_TAX_STATUS_CODE IN VARCHAR2 DEFAULT NULL,
306 P_TAX_RATE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
307 P_TAX_RATE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
308 P_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
309 P_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
310 P_DOCUMENT_SUB_TYPE IN VARCHAR2 DEFAULT NULL,
311 P_TRX_BUSINESS_CATEGORY IN VARCHAR2 DEFAULT NULL,
312 P_TAX_INVOICE_DATE_LOW IN VARCHAR2 DEFAULT NULL,
313 P_TAX_INVOICE_DATE_HIGH IN VARCHAR2 DEFAULT NULL,
314 P_POSTING_STATUS IN VARCHAR2 DEFAULT NULL,
315 P_EXTRACT_ACCTED_TAX_LINES IN VARCHAR2 DEFAULT NULL,
316 P_INCLUDE_ACCOUNTING_SEGMENTS IN VARCHAR2 DEFAULT NULL,
317 P_BALANCING_SEGMENT_LOW IN VARCHAR2 DEFAULT NULL,
318 P_BALANCING_SEGMENT_HIGH IN VARCHAR2 DEFAULT NULL,
319 P_INCLUDE_DISCOUNTS IN VARCHAR2 DEFAULT NULL,
320 P_EXTRACT_STARTING_LINE_NUM IN NUMBER DEFAULT NULL,
321 P_REQUEST_ID IN NUMBER DEFAULT NULL,
322 P_REPORT_NAME IN VARCHAR2 DEFAULT NULL,
323 P_VAT_TRANSACTION_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
324 P_INCLUDE_FULLY_NR_TAX_FLAG IN VARCHAR2 DEFAULT NULL,
325 P_MUNICIPAL_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
326 P_MUNICIPAL_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
327 P_PROV_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
328 P_PROV_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
329 P_EXCISE_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
330 P_EXCISE_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
331 P_NON_TAXABLE_TAX_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
332 P_PER_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
333 P_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
334 P_FED_PER_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
335 P_FED_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
336 P_VAT_TAX_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
337 P_EXCISE_TAX IN VARCHAR2 DEFAULT NULL,
338 P_VAT_ADDITIONAL_TAX IN VARCHAR2 DEFAULT NULL,
339 P_VAT_NON_TAXABLE_TAX IN VARCHAR2 DEFAULT NULL,
340 P_VAT_NOT_TAX IN VARCHAR2 DEFAULT NULL,
341 P_VAT_PERCEPTION_TAX IN VARCHAR2 DEFAULT NULL,
342 P_VAT_TAX IN VARCHAR2 DEFAULT NULL,
343 P_INC_SELF_WD_TAX IN VARCHAR2 DEFAULT NULL,
344 P_EXCLUDING_TRX_LETTER IN VARCHAR2 DEFAULT NULL,
345 P_TRX_LETTER_LOW IN VARCHAR2 DEFAULT NULL,
346 P_TRX_LETTER_HIGH IN VARCHAR2 DEFAULT NULL,
347 P_INCLUDE_REFERENCED_SOURCE IN VARCHAR2 DEFAULT NULL,
348 P_PARTY_NAME IN VARCHAR2 DEFAULT NULL,
349 P_BATCH_NAME IN VARCHAR2 DEFAULT NULL,
350 P_BATCH_DATE_LOW IN DATE DEFAULT NULL,
351 P_BATCH_DATE_HIGH IN DATE DEFAULT NULL,
352 P_BATCH_SOURCE_ID IN VARCHAR2 DEFAULT NULL,
353 P_ADJUSTED_DOC_FROM IN VARCHAR2 DEFAULT NULL,
354 P_ADJUSTED_DOC_TO IN VARCHAR2 DEFAULT NULL,
355 P_STANDARD_VAT_TAX_RATE IN VARCHAR2 DEFAULT NULL,
356 P_MUNICIPAL_TAX IN VARCHAR2 DEFAULT NULL,
357 P_PROVINCIAL_TAX IN VARCHAR2 DEFAULT NULL,
358 P_TAX_ACCOUNT_LOW IN VARCHAR2 DEFAULT NULL,
359 P_TAX_ACCOUNT_HIGH IN VARCHAR2 DEFAULT NULL,
360 P_EXP_CERT_DATE_FROM IN DATE DEFAULT NULL,
361 P_EXP_CERT_DATE_TO IN DATE DEFAULT NULL,
362 P_EXP_METHOD IN VARCHAR2 DEFAULT NULL,
363 P_PRINT_COMPANY_INFO IN VARCHAR2 DEFAULT NULL,
364 P_ORDER_BY IN VARCHAR2 DEFAULT NULL,
365 P_CHART_OF_ACCOUNTS_ID IN NUMBER DEFAULT NULL,
366 P_REPRINT IN VARCHAR2 DEFAULT NULL,
367 P_ERRBUF IN OUT NOCOPY VARCHAR2 ,
368 P_RETCODE IN OUT NOCOPY VARCHAR2 ,
369 P_ACCOUNTING_STATUS IN VARCHAR2 DEFAULT NULL,
370 P_REPORTED_STATUS IN VARCHAR2 DEFAULT NULL,
371 P_TAXABLE_ACCOUNT_LOW IN VARCHAR2 DEFAULT NULL,
372 P_TAXABLE_ACCOUNT_HIGH IN VARCHAR2 DEFAULT NULL,
373 P_GL_OR_TRX_DATE_FILTER IN VARCHAR2 DEFAULT 'N') --Bug 5396444
374 IS
375 l_trl_global_variables_rec TRL_GLOBAL_VARIABLES_REC_TYPE;
376 BEGIN
377 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
378 -- g_current_runtime_level := 1;
379 -- g_level_procedure := 2;
380 IF (g_level_procedure >= g_current_runtime_level ) THEN
381 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data.BEGIN',
382 'ZX_EXTRACT_PKG:populate_tax_data(+)');
383 END IF;
384
385 -- Initialize the parameters:
386
387 initialize(
388 L_TRL_GLOBAL_VARIABLES_REC,
389 P_REPORTING_LEVEL,
390 P_REPORTING_CONTEXT,
391 -- apai P_LEGAL_ENTITY_LEVEL,
392 P_LEGAL_ENTITY_ID,
393 P_SUMMARY_LEVEL,
394 P_LEDGER_ID,
395 P_REGISTER_TYPE,
396 P_PRODUCT,
397 P_MATRIX_REPORT,
398 P_DETAIL_LEVEL,
399 P_CURRENCY_CODE_LOW,
400 P_CURRENCY_CODE_HIGH,
401 P_INCLUDE_AP_STD_TRX_CLASS,
402 P_INCLUDE_AP_DM_TRX_CLASS,
403 P_INCLUDE_AP_CM_TRX_CLASS,
404 P_INCLUDE_AP_PREP_TRX_CLASS,
405 P_INCLUDE_AP_MIX_TRX_CLASS,
406 P_INCLUDE_AP_EXP_TRX_CLASS,
407 P_INCLUDE_AP_INT_TRX_CLASS,
408 P_INCLUDE_AR_INV_TRX_CLASS,
409 P_INCLUDE_AR_APPL_TRX_CLASS,
410 P_INCLUDE_AR_ADJ_TRX_CLASS,
411 P_INCLUDE_AR_MISC_TRX_CLASS,
412 P_INCLUDE_AR_BR_TRX_CLASS,
413 P_INCLUDE_GL_MANUAL_LINES,
414 P_THIRD_PARTY_REP_LEVEL,
415 P_FIRST_PARTY_TAX_REG_NUM,
416 P_TRX_NUMBER_LOW,
417 P_TRX_NUMBER_HIGH,
418 P_EXTRACT_REPORT_LINE_NUMBER,
419 P_AR_TRX_PRINTING_STATUS,
420 P_AR_EXEMPTION_STATUS,
421 P_GL_DATE_LOW,
422 P_GL_DATE_HIGH,
423 P_TRX_DATE_LOW,
424 P_TRX_DATE_HIGH,
425 P_GL_PERIOD_NAME_LOW,
426 P_GL_PERIOD_NAME_HIGH,
427 P_TRX_DATE_PERIOD_NAME_LOW,
428 P_TRX_DATE_PERIOD_NAME_HIGH,
429 P_TAX_JURISDICTION_CODE,
430 P_TAX_REGIME_CODE,
431 P_TAX,
432 P_TAX_STATUS_CODE,
433 P_TAX_RATE_CODE_LOW,
434 P_TAX_RATE_CODE_HIGH,
435 P_TAX_TYPE_CODE_LOW,
436 P_TAX_TYPE_CODE_HIGH,
437 P_DOCUMENT_SUB_TYPE,
438 P_TRX_BUSINESS_CATEGORY,
439 P_TAX_INVOICE_DATE_LOW,
440 P_TAX_INVOICE_DATE_HIGH,
441 P_POSTING_STATUS,
442 P_EXTRACT_ACCTED_TAX_LINES,
443 P_INCLUDE_ACCOUNTING_SEGMENTS,
444 P_BALANCING_SEGMENT_LOW,
445 P_BALANCING_SEGMENT_HIGH,
446 P_INCLUDE_DISCOUNTS,
447 P_EXTRACT_STARTING_LINE_NUM,
448 P_REQUEST_ID,
449 P_REPORT_NAME,
450 P_VAT_TRANSACTION_TYPE_CODE,
451 P_INCLUDE_FULLY_NR_TAX_FLAG,
452 P_MUNICIPAL_TAX_TYPE_CODE_LOW,
453 P_MUNICIPAL_TAX_TYPE_CODE_HIGH,
454 P_PROV_TAX_TYPE_CODE_LOW,
455 P_PROV_TAX_TYPE_CODE_HIGH,
456 P_EXCISE_TAX_TYPE_CODE_LOW,
457 P_EXCISE_TAX_TYPE_CODE_HIGH,
458 P_NON_TAXABLE_TAX_TYPE_CODE,
459 P_PER_TAX_TYPE_CODE_LOW,
460 P_PER_TAX_TYPE_CODE_HIGH,
461 P_FED_PER_TAX_TYPE_CODE_LOW,
462 P_FED_PER_TAX_TYPE_CODE_HIGH,
463 P_VAT_TAX_TYPE_CODE,
464 P_EXCISE_TAX,
465 P_VAT_ADDITIONAL_TAX,
466 P_VAT_NON_TAXABLE_TAX,
467 P_VAT_NOT_TAX,
468 P_VAT_PERCEPTION_TAX,
469 P_VAT_TAX,
470 P_INC_SELF_WD_TAX,
471 P_EXCLUDING_TRX_LETTER,
472 P_TRX_LETTER_LOW,
473 P_TRX_LETTER_HIGH,
474 P_INCLUDE_REFERENCED_SOURCE,
475 P_PARTY_NAME,
476 P_BATCH_NAME,
477 P_BATCH_DATE_LOW,
478 P_BATCH_DATE_HIGH,
479 P_BATCH_SOURCE_ID,
480 P_ADJUSTED_DOC_FROM,
481 P_ADJUSTED_DOC_TO,
482 P_STANDARD_VAT_TAX_RATE,
483 P_MUNICIPAL_TAX,
484 P_PROVINCIAL_TAX,
485 P_TAX_ACCOUNT_LOW,
486 P_TAX_ACCOUNT_HIGH,
487 P_EXP_CERT_DATE_FROM,
488 P_EXP_CERT_DATE_TO,
489 P_EXP_METHOD,
490 P_PRINT_COMPANY_INFO,
491 P_ORDER_BY,
492 P_CHART_OF_ACCOUNTS_ID,
493 P_REPRINT,
494 P_ERRBUF,
495 P_RETCODE,
496 P_ACCOUNTING_STATUS,
497 P_REPORTED_STATUS,
498 P_TAXABLE_ACCOUNT_LOW,
499 P_TAXABLE_ACCOUNT_HIGH,
500 P_GL_OR_TRX_DATE_FILTER); --Bug 5396444
501
502 -- Check whether this is a Multi Org or Non Multi Org installation;
503
504 -- Derive the dependent parameters:
505
506 derive_dependent_parameters(l_trl_global_variables_rec);
507
508 IF g_ledger_type = 'R' THEN
509 fnd_client_info.set_currency_context(p_ledger_id);
510 END IF;
511
512 IF (g_level_procedure >= g_current_runtime_level ) THEN
513 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
514 'Return Code Check '||to_char(l_trl_global_variables_rec.retcode));
515 END IF;
516
517 IF validate_parameters(l_trl_global_variables_rec) THEN
518 IF l_trl_global_variables_rec.retcode <> 2 THEN
519 extract_rep_context_info(l_trl_global_variables_rec);
520 END IF;
521
522 IF (g_level_procedure >= g_current_runtime_level ) THEN
523 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
524 'ZX.TRL.ZX_EXTRACT_PKG.EXTRACT_TAX_INFO Call');
525 END IF;
526
527 IF l_trl_global_variables_rec.retcode <> 2 THEN
528 extract_tax_info(g_ledger_type, l_trl_global_variables_rec);
529 END IF;
530 IF (g_level_procedure >= g_current_runtime_level ) THEN
531 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
532 'extract_additional_info call : '||to_char(l_trl_global_variables_rec.retcode));
533 END IF;
534 -- IF l_trl_global_variables_rec.retcode <> 2 THEN
535 extract_additional_info(g_ledger_type, l_trl_global_variables_rec) ;
536 cleanup(l_trl_global_variables_rec);
537 COMMIT;
538
539 -- END IF;
540
541 IF l_trl_global_variables_rec.retcode <> 2 THEN
542 cleanup(l_trl_global_variables_rec);
543 ELSE
544 delete_all(l_trl_global_variables_rec.request_id);
545 END IF;
546 COMMIT;
547
548 l_errbuf := trim(substrb(L_ERRBUF,1,240))||
549 trim(substrb(L_PARAMLIST,1,1500));
550 p_errbuf := substrb(L_ERRBUF,1,l_length_errbuf);
551 -- p_retcode := l_retcode;
552
553
554 IF p_retcode IS NULL THEN
555 p_retcode := 0;
556 END IF;
557 END IF;
558
559 IF (g_level_procedure >= g_current_runtime_level ) THEN
560 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.Return Code ',
561 'ZX_EXTRACT_PKG:populate_tax_data' ||to_char(l_trl_global_variables_rec.retcode));
562 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data.END',
563 'ZX_EXTRACT_PKG:populate_tax_data(-)');
564 END IF;
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
569 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
570 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
571 FND_MSG_PUB.Add;
572 IF (g_level_unexpected >= g_current_runtime_level ) THEN
573 FND_LOG.STRING(g_level_unexpected,
574 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
575 g_error_buffer);
576 END IF;
577
578 P_RETCODE := l_trl_global_variables_rec.retcode;
579
580 END populate_tax_data;
581
582 ------------------------------------------------------------------------------
583 -- PRIVATE METHODS
584 ------------------------------------------------------------------------------
585
586
587 /*===========================================================================+
588 | PROCEDURE |
589 | INITIALIZE |
590 | |
591 | DESCRIPTION |
592 | This procedure initializes the parameters for procedure |
593 | ZX_EXTRACT_PKG.populate_tax_data, and writes the values of parameters |
594 | passed in debug file and p_errbuf. |
595 | |
596 | Called from ZX_EXTRACT_PKG.populate_tax_data |
597 | |
598 | SCOPE - Private |
599 | |
600 | NOTES |
601 | |
602 | MODIFICATION HISTORY |
603 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
604 | |
605 +===========================================================================*/
606
607 PROCEDURE initialize(
608 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
609 P_REPORTING_LEVEL IN VARCHAR2,
610 P_REPORTING_CONTEXT IN VARCHAR2,
611 -- P_LEGAL_ENTITY_LEVEL IN VARCHAR2,
612 P_LEGAL_ENTITY_ID IN NUMBER , -- apai COMPANY_NAME
613 P_SUMMARY_LEVEL IN VARCHAR2,
614 P_LEDGER_ID IN NUMBER ,
615 P_REGISTER_TYPE IN VARCHAR2,
616 P_PRODUCT IN VARCHAR2,
617 P_MATRIX_REPORT IN VARCHAR2,
618 P_DETAIL_LEVEL IN VARCHAR2,
619 P_CURRENCY_CODE_LOW IN VARCHAR2,
620 P_CURRENCY_CODE_HIGH IN VARCHAR2,
621 P_INCLUDE_AP_STD_TRX_CLASS IN VARCHAR2,
622 P_INCLUDE_AP_DM_TRX_CLASS IN VARCHAR2,
623 P_INCLUDE_AP_CM_TRX_CLASS IN VARCHAR2,
624 P_INCLUDE_AP_PREP_TRX_CLASS IN VARCHAR2,
625 P_INCLUDE_AP_MIX_TRX_CLASS IN VARCHAR2,
626 P_INCLUDE_AP_EXP_TRX_CLASS IN VARCHAR2,
627 P_INCLUDE_AP_INT_TRX_CLASS IN VARCHAR2,
628 P_INCLUDE_AR_INV_TRX_CLASS IN VARCHAR2,
629 P_INCLUDE_AR_APPL_TRX_CLASS IN VARCHAR2,
630 P_INCLUDE_AR_ADJ_TRX_CLASS IN VARCHAR2,
631 P_INCLUDE_AR_MISC_TRX_CLASS IN VARCHAR2,
632 P_INCLUDE_AR_BR_TRX_CLASS IN VARCHAR2,
633 P_INCLUDE_GL_MANUAL_LINES IN VARCHAR2,
634 P_THIRD_PARTY_REP_LEVEL IN VARCHAR2,
635 P_FIRST_PARTY_TAX_REG_NUM IN VARCHAR2,
636 P_TRX_NUMBER_LOW IN VARCHAR2,
637 P_TRX_NUMBER_HIGH IN VARCHAR2,
638 P_EXTRACT_REPORT_LINE_NUMBER IN NUMBER ,
639 P_AR_TRX_PRINTING_STATUS IN VARCHAR2,
640 P_AR_EXEMPTION_STATUS IN VARCHAR2,
641 P_GL_DATE_LOW IN DATE ,
642 P_GL_DATE_HIGH IN DATE ,
643 P_TRX_DATE_LOW IN DATE ,
644 P_TRX_DATE_HIGH IN DATE ,
645 P_GL_PERIOD_NAME_LOW IN VARCHAR2,
646 P_GL_PERIOD_NAME_HIGH IN VARCHAR2,
647 P_TRX_DATE_PERIOD_NAME_LOW IN VARCHAR2,
648 P_TRX_DATE_PERIOD_NAME_HIGH IN VARCHAR2,
649 P_TAX_JURISDICTION_CODE IN VARCHAR ,
650 P_TAX_REGIME_CODE IN VARCHAR2,
651 P_TAX IN VARCHAR2,
652 P_TAX_STATUS_CODE IN VARCHAR2,
653 P_TAX_RATE_CODE_LOW IN VARCHAR2,
654 P_TAX_RATE_CODE_HIGH IN VARCHAR2,
655 P_TAX_TYPE_CODE_LOW IN VARCHAR2,
656 P_TAX_TYPE_CODE_HIGH IN VARCHAR2,
657 P_DOCUMENT_SUB_TYPE IN VARCHAR2,
658 P_TRX_BUSINESS_CATEGORY IN VARCHAR2,
659 P_TAX_INVOICE_DATE_LOW IN VARCHAR2,
660 P_TAX_INVOICE_DATE_HIGH IN VARCHAR2,
661 P_POSTING_STATUS IN VARCHAR2,
662 P_EXTRACT_ACCTED_TAX_LINES IN VARCHAR2,
663 P_INCLUDE_ACCOUNTING_SEGMENTS IN VARCHAR2,
664 P_BALANCING_SEGMENT_LOW IN VARCHAR2,
665 P_BALANCING_SEGMENT_HIGH IN VARCHAR2,
666 P_INCLUDE_DISCOUNTS IN VARCHAR2,
667 P_EXTRACT_STARTING_LINE_NUM IN NUMBER ,
668 P_REQUEST_ID IN NUMBER ,
669 P_REPORT_NAME IN VARCHAR2,
670 P_VAT_TRANSACTION_TYPE_CODE IN VARCHAR2,
671 P_INCLUDE_FULLY_NR_TAX_FLAG IN VARCHAR2,
672 P_MUNICIPAL_TAX_TYPE_CODE_LOW IN VARCHAR2,
673 P_MUNICIPAL_TAX_TYPE_CODE_HIGH IN VARCHAR2,
674 P_PROV_TAX_TYPE_CODE_LOW IN VARCHAR2,
675 P_PROV_TAX_TYPE_CODE_HIGH IN VARCHAR2,
676 P_EXCISE_TAX_TYPE_CODE_LOW IN VARCHAR2,
677 P_EXCISE_TAX_TYPE_CODE_HIGH IN VARCHAR2,
678 P_NON_TAXABLE_TAX_TYPE_CODE IN VARCHAR2,
679 P_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
680 P_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
681 P_FED_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
682 P_FED_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
683 P_VAT_TAX_TYPE_CODE IN VARCHAR2,
684 P_EXCISE_TAX IN VARCHAR2,
685 P_VAT_ADDITIONAL_TAX IN VARCHAR2,
686 P_VAT_NON_TAXABLE_TAX IN VARCHAR2,
687 P_VAT_NOT_TAX IN VARCHAR2,
688 P_VAT_PERCEPTION_TAX IN VARCHAR2,
689 P_VAT_TAX IN VARCHAR2,
690 P_INC_SELF_WD_TAX IN VARCHAR2,
691 P_EXCLUDING_TRX_LETTER IN VARCHAR2,
692 P_TRX_LETTER_LOW IN VARCHAR2,
693 P_TRX_LETTER_HIGH IN VARCHAR2,
694 P_INCLUDE_REFERENCED_SOURCE IN VARCHAR2,
695 P_PARTY_NAME IN VARCHAR2,
696 P_BATCH_NAME IN VARCHAR2,
697 P_BATCH_DATE_LOW IN DATE ,
698 P_BATCH_DATE_HIGH IN DATE ,
699 P_BATCH_SOURCE_ID IN VARCHAR2,
700 P_ADJUSTED_DOC_FROM IN VARCHAR2,
701 P_ADJUSTED_DOC_TO IN VARCHAR2,
702 P_STANDARD_VAT_TAX_RATE IN VARCHAR2,
703 P_MUNICIPAL_TAX IN VARCHAR2,
704 P_PROVINCIAL_TAX IN VARCHAR2,
705 P_TAX_ACCOUNT_LOW IN VARCHAR2,
706 P_TAX_ACCOUNT_HIGH IN VARCHAR2,
707 P_EXP_CERT_DATE_FROM IN DATE ,
708 P_EXP_CERT_DATE_TO IN DATE ,
709 P_EXP_METHOD IN VARCHAR2,
710 P_PRINT_COMPANY_INFO IN VARCHAR2,
711 P_ORDER_BY IN VARCHAR2,
712 P_CHART_OF_ACCOUNTS_ID IN NUMBER ,
713 P_REPRINT IN VARCHAR2,
714 P_ERRBUF IN OUT NOCOPY VARCHAR2,
715 P_RETCODE IN OUT NOCOPY VARCHAR2,
716 P_ACCOUNTING_STATUS IN VARCHAR2,
717 P_REPORTED_STATUS IN VARCHAR2,
718 P_TAXABLE_ACCOUNT_LOW IN VARCHAR2,
719 P_TAXABLE_ACCOUNT_HIGH IN VARCHAR2,
720 P_GL_OR_TRX_DATE_FILTER IN VARCHAR2) --Bug 5396444
721 IS
722 BEGIN
723
724
725 IF (g_level_procedure >= g_current_runtime_level ) THEN
726 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE.BEGIN',
727 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE(+)');
728
729 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
730 'P_REPORTING_LEVEL = '||P_REPORTING_LEVEL);
731
732
733 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
734 'P_REPORTING_CONTEXT = '||P_REPORTING_CONTEXT);
735 /* apai
736 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
737 'P_LEGAL_ENTITY_LEVEL = '||P_LEGAL_ENTITY_LEVEL);
738 */
739 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
740 'P_LEGAL_ENTITY_ID = '||P_LEGAL_ENTITY_ID);
741 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
742 'P_SUMMARY_LEVEL = '||P_SUMMARY_LEVEL);
743 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
744 'P_LEDGER_ID = '||P_LEDGER_ID);
745 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
746 'P_REGISTER_TYPE = '||P_REGISTER_TYPE);
747 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
748 'P_PRODUCT = '||P_PRODUCT);
749 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
750 'P_MATRIX_REPORT = '||P_MATRIX_REPORT);
751 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
752 'P_CURRENCY_CODE_LOW = '||P_CURRENCY_CODE_LOW);
753 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
754 'P_CURRENCY_CODE_HIGH = '||P_CURRENCY_CODE_HIGH);
755 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
756 'P_INCLUDE_AP_STD_TRX_CLASS = '||P_INCLUDE_AP_STD_TRX_CLASS);
757 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
758 'P_INCLUDE_AP_DM_TRX_CLASS = '||P_INCLUDE_AP_DM_TRX_CLASS);
759 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
760 'P_INCLUDE_AP_CM_TRX_CLASS = '||P_INCLUDE_AP_CM_TRX_CLASS);
761 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
762 'P_INCLUDE_AP_PREP_TRX_CLASS = '||P_INCLUDE_AP_PREP_TRX_CLASS);
763 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
764 'P_INCLUDE_AP_MIX_TRX_CLASS = '||P_INCLUDE_AP_MIX_TRX_CLASS);
765 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
766 'P_INCLUDE_AP_EXP_TRX_CLASS = '||P_INCLUDE_AP_EXP_TRX_CLASS);
767 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
768 'P_INCLUDE_AP_INT_TRX_CLASS = '||P_INCLUDE_AP_INT_TRX_CLASS);
769 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
770 'P_INCLUDE_AR_INV_TRX_CLASS = '||P_INCLUDE_AR_INV_TRX_CLASS);
771 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
772 'P_INCLUDE_AR_APPL_TRX_CLASS = '||P_INCLUDE_AR_APPL_TRX_CLASS);
773 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
774 'P_INCLUDE_AR_ADJ_TRX_CLASS = '||P_INCLUDE_AR_ADJ_TRX_CLASS);
775 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
776 'P_INCLUDE_AR_MISC_TRX_CLASS = '||P_INCLUDE_AR_MISC_TRX_CLASS);
777 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
778 'P_INCLUDE_AR_BR_TRX_CLASS = '||P_INCLUDE_AR_BR_TRX_CLASS);
779 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
780 'P_INCLUDE_GL_MANUAL_LINES = '||P_INCLUDE_GL_MANUAL_LINES);
781 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
782 'P_THIRD_PARTY_REP_LEVEL = '||P_THIRD_PARTY_REP_LEVEL);
783 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
784 'P_FIRST_PARTY_TAX_REG_NUM = '||P_FIRST_PARTY_TAX_REG_NUM);
785 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
786 'P_TRX_NUMBER_LOW = '||P_TRX_NUMBER_LOW);
787 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
788 'P_TRX_NUMBER_HIGH = '||P_TRX_NUMBER_HIGH);
789 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
790 'P_AR_TRX_PRINTING_STATUS = '||P_AR_TRX_PRINTING_STATUS);
791 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
792 'P_AR_EXEMPTION_STATUS = '||P_AR_EXEMPTION_STATUS);
793 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
794 'P_GL_DATE_LOW = '||P_GL_DATE_LOW);
795 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
796 'P_GL_DATE_HIGH = '||P_GL_DATE_HIGH);
797 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
798 'P_TRX_DATE_LOW = '||P_TRX_DATE_LOW);
799 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
800 'P_TRX_DATE_HIGH = '||P_TRX_DATE_HIGH);
801 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
802 'P_GL_PERIOD_NAME_LOW = '||P_GL_PERIOD_NAME_LOW);
803 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
804 'P_GL_PERIOD_NAME_HIGH = '||P_GL_PERIOD_NAME_HIGH);
805 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
806 'P_TRX_DATE_PERIOD_NAME_LOW = '||P_TRX_DATE_PERIOD_NAME_LOW);
807 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
808 'P_TRX_DATE_PERIOD_NAME_HIGH = '||P_TRX_DATE_PERIOD_NAME_HIGH);
809 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
810 'P_TAX_JURISDICTION_CODE = '||P_TAX_JURISDICTION_CODE);
811 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
812 'P_TAX_REGIME_CODE = '||P_TAX_REGIME_CODE);
813 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
814 'P_TAX = '||P_TAX);
815 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
816 'P_TAX_STATUS_CODE = '||P_TAX_STATUS_CODE);
817 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
818 'P_TAX_RATE_CODE_LOW = '||P_TAX_RATE_CODE_LOW);
819 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
820 'P_TAX_RATE_CODE_HIGH = '||P_TAX_RATE_CODE_HIGH);
821 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
822 'P_TAX_TYPE_CODE_LOW = '||P_TAX_TYPE_CODE_LOW);
823 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
824 'P_TAX_TYPE_CODE_HIGH = '||P_TAX_TYPE_CODE_HIGH);
825 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
826 'P_DOCUMENT_SUB_TYPE = '||P_DOCUMENT_SUB_TYPE);
827 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
828 'P_TRX_BUSINESS_CATEGORY = '||P_TRX_BUSINESS_CATEGORY);
829 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
830 'P_TAX_INVOICE_DATE_LOW = '||P_TAX_INVOICE_DATE_LOW);
831 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
832 'P_TAX_INVOICE_DATE_HIGH = '||P_TAX_INVOICE_DATE_HIGH);
833 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
834 'P_POSTING_STATUS = '||P_POSTING_STATUS);
835 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
836 'P_ACCOUNTING_STATUS = '||P_ACCOUNTING_STATUS);
837 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
838 'P_REPORTED_STATUS = '||P_REPORTED_STATUS);
839 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
840 'P_EXTRACT_ACCTED_TAX_LINES = '||P_EXTRACT_ACCTED_TAX_LINES);
841 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
842 'P_INCLUDE_ACCOUNTING_SEGMENTS = '||P_INCLUDE_ACCOUNTING_SEGMENTS);
843 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
844 'P_BALANCING_SEGMENT_LOW = '||P_BALANCING_SEGMENT_LOW);
845 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
846 'P_BALANCING_SEGMENT_HIGH = '||P_BALANCING_SEGMENT_HIGH);
847 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
848 'P_INCLUDE_DISCOUNTS = '||P_INCLUDE_DISCOUNTS);
849 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
850 'P_EXTRACT_STARTING_LINE_NUM = '||P_EXTRACT_STARTING_LINE_NUM);
851 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
852 'P_REQUEST_ID = '||P_REQUEST_ID);
853 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
854 'P_REPORT_NAME = '||P_REPORT_NAME);
855 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
856 'P_VAT_TRANSACTION_TYPE_CODE = '||P_VAT_TRANSACTION_TYPE_CODE);
857 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
858 'P_INCLUDE_FULLY_NR_TAX_FLAG = '||P_INCLUDE_FULLY_NR_TAX_FLAG);
859 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
860 'P_MUNICIPAL_TAX_TYPE_CODE_LOW = '||P_MUNICIPAL_TAX_TYPE_CODE_LOW);
861 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
862 'P_MUNICIPAL_TAX_TYPE_CODE_HIGH = '||P_MUNICIPAL_TAX_TYPE_CODE_HIGH);
863 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
864 'P_PROVINCIAL_TAX_TYPE_CODE_LOW = '||P_PROV_TAX_TYPE_CODE_LOW);
865 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
866 'P_PROVINCIAL_TAX_TYPE__CODE_HIGH = '||P_PROV_TAX_TYPE_CODE_HIGH);
867 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
868 'P_EXCISE_TAX_TYPE_CODE_LOW = '||P_EXCISE_TAX_TYPE_CODE_LOW);
869 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
870 'P_EXCISE_TAX_TYPE_CODE_HIGH = '||P_EXCISE_TAX_TYPE_CODE_HIGH);
871 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
872 'P_NON_TAXABLE_TAX_TYPE_CODE = '||P_NON_TAXABLE_TAX_TYPE_CODE);
873 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
874 'P_PERCEPTION_TAX_TYPE_CODE_LOW = '||P_PER_TAX_TYPE_CODE_LOW);
875 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
876 'P_PERCEPTION_TAX_TYPE_CODE_HIGH = '||P_PER_TAX_TYPE_CODE_HIGH);
877 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
878 'P_FED_PER_TAX_TYPE_CODE_LOW = '||P_FED_PER_TAX_TYPE_CODE_LOW);
879 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
880 'P_FED_PER_TAX_TYPE_CODE_HIGH = '||P_FED_PER_TAX_TYPE_CODE_HIGH);
881 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
882 'P_VAT_TAX_TYPE_CODE = '||P_VAT_TAX_TYPE_CODE);
883 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
884 'P_EXCISE_TAX = '||P_EXCISE_TAX);
885 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
886 'P_VAT_ADDITIONAL_TAX = '||P_VAT_ADDITIONAL_TAX);
887 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
888 'P_VAT_NON_TAXABLE_TAX = '||P_VAT_NON_TAXABLE_TAX);
889 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
890 'P_VAT_NOT_TAX = '||P_VAT_NOT_TAX);
891 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
892 'P_VAT_PERCEPTION_TAX = '||P_VAT_PERCEPTION_TAX);
893 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
894 'P_VAT_TAX = '||P_VAT_TAX);
895 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
896 'P_INC_SELF_WD_TAX = '||P_INC_SELF_WD_TAX);
897 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
898 'P_EXCLUDING_TRX_LETTER = '||P_EXCLUDING_TRX_LETTER);
899 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
900 'P_TRX_LETTER_LOW = '||P_TRX_LETTER_LOW);
901 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
902 'P_TRX_LETTER_HIGH = '||P_TRX_LETTER_HIGH);
903 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
904 'P_INCLUDE_REFERENCED_SOURCE = '||P_INCLUDE_REFERENCED_SOURCE);
905 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
906 'P_PARTY_NAME = '||P_PARTY_NAME);
907 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
908 'P_BATCH_NAME = '||P_BATCH_NAME);
909 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
910 'P_BATCH_SOURCE_ID = '||P_BATCH_SOURCE_ID);
911 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
912 'P_ADJUSTED_DOC_FROM = '||P_ADJUSTED_DOC_FROM);
913 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
914 'P_ADJUSTED_DOC_TO = '||P_ADJUSTED_DOC_TO);
915 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
916 'P_STANDARD_VAT_TAX_RATE = '||P_STANDARD_VAT_TAX_RATE);
917 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
918 'P_MUNICIPAL_TAX = '||P_MUNICIPAL_TAX);
919 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
920 'P_PROVINCIAL_TAX = '||P_PROVINCIAL_TAX);
921 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
922 'P_TAX_ACCOUNT_LOW = '||P_TAX_ACCOUNT_LOW);
923 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
924 'P_TAX_ACCOUNT_HIGH = '||P_TAX_ACCOUNT_HIGH);
925 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
926 'P_EXP_CERT_DATE_FROM = '||P_EXP_CERT_DATE_FROM);
927 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
928 'P_EXP_CERT_DATE_TO = '||P_EXP_CERT_DATE_TO);
929 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
930 'P_EXP_METHOD = '||P_EXP_METHOD);
931 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
932 'P_TRX_NUMBER_LOW = '||P_TRX_NUMBER_LOW);
933 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
934 'P_TRX_NUMBER_HIGH = '||P_TRX_NUMBER_HIGH);
935 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
936 'P_PRINT_COMPANY_INFO = '||P_PRINT_COMPANY_INFO);
937 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
938 'P_ORDER_BY = '||P_ORDER_BY);
939 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
940 'P_ERRBUF = '||P_ERRBUF);
941 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
942 'P_RETCODE = '||P_RETCODE);
943 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
944 'P_TAXABLE_ACCOUNT_LOW = '||P_TAXABLE_ACCOUNT_LOW);
945 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
946 'P_TAXABLE_ACCOUNT_HIGH = '||P_TAXABLE_ACCOUNT_HIGH);
947 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
948 'P_GL_OR_TRX_DATE_FILTER = '||P_GL_OR_TRX_DATE_FILTER); --Bug 5396444
949 END IF;
950
951 P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL := P_REPORTING_LEVEL;
952 P_TRL_GLOBAL_VARIABLES_REC.REPORTING_CONTEXT := P_REPORTING_CONTEXT;
953 -- apai P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_LEVEL := P_LEGAL_ENTITY_LEVEL;
954 P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID := P_LEGAL_ENTITY_ID;
955 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL := P_SUMMARY_LEVEL;
956 P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID := P_LEDGER_ID;
957 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE := P_REGISTER_TYPE;
958 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT := P_PRODUCT;
959 P_TRL_GLOBAL_VARIABLES_REC.MATRIX_REPORT := P_MATRIX_REPORT;
960 P_TRL_GLOBAL_VARIABLES_REC.CURRENCY_CODE_LOW := P_CURRENCY_CODE_LOW;
961 P_TRL_GLOBAL_VARIABLES_REC.CURRENCY_CODE_HIGH := P_CURRENCY_CODE_HIGH;
962 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_STD_TRX_CLASS := P_INCLUDE_AP_STD_TRX_CLASS;
963 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_DM_TRX_CLASS := P_INCLUDE_AP_DM_TRX_CLASS;
964 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_CM_TRX_CLASS := P_INCLUDE_AP_CM_TRX_CLASS;
965 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_PREP_TRX_CLASS := P_INCLUDE_AP_PREP_TRX_CLASS;
966 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_MIX_TRX_CLASS := P_INCLUDE_AP_MIX_TRX_CLASS;
967 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_EXP_TRX_CLASS := P_INCLUDE_AP_EXP_TRX_CLASS;
968 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_INT_TRX_CLASS := P_INCLUDE_AP_INT_TRX_CLASS;
969 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_INV_TRX_CLASS := P_INCLUDE_AR_INV_TRX_CLASS;
970 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_APPL_TRX_CLASS := P_INCLUDE_AR_APPL_TRX_CLASS;
971 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_ADJ_TRX_CLASS := P_INCLUDE_AR_ADJ_TRX_CLASS;
972 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_MISC_TRX_CLASS := P_INCLUDE_AR_MISC_TRX_CLASS;
973 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_BR_TRX_CLASS := P_INCLUDE_AR_BR_TRX_CLASS;
974 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_GL_MANUAL_LINES := P_INCLUDE_GL_MANUAL_LINES;
975 P_TRL_GLOBAL_VARIABLES_REC.THIRD_PARTY_REP_LEVEL := P_THIRD_PARTY_REP_LEVEL;
976 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM := P_FIRST_PARTY_TAX_REG_NUM;
977 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_LOW := P_TRX_NUMBER_LOW;
978 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_HIGH := P_TRX_NUMBER_HIGH;
979 P_TRL_GLOBAL_VARIABLES_REC.AR_TRX_PRINTING_STATUS := P_AR_TRX_PRINTING_STATUS;
980 P_TRL_GLOBAL_VARIABLES_REC.AR_EXEMPTION_STATUS := P_AR_EXEMPTION_STATUS;
981 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW := P_GL_DATE_LOW;
982 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH := P_GL_DATE_HIGH;
983 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_LOW := P_TRX_DATE_LOW;
984 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH := P_TRX_DATE_HIGH;
985 P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_LOW := P_GL_PERIOD_NAME_LOW;
986 P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_HIGH := P_GL_PERIOD_NAME_HIGH;
987 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_LOW := P_TRX_DATE_PERIOD_NAME_LOW;
988 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_HIGH := P_TRX_DATE_PERIOD_NAME_HIGH;
989 P_TRL_GLOBAL_VARIABLES_REC.TAX_REGIME_CODE := P_TAX_REGIME_CODE;
990 P_TRL_GLOBAL_VARIABLES_REC.TAX := P_TAX;
991 P_TRL_GLOBAL_VARIABLES_REC.TAX_STATUS_CODE := P_TAX_STATUS_CODE;
992 P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_LOW := P_TAX_RATE_CODE_LOW;
993 P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_HIGH := P_TAX_RATE_CODE_HIGH;
994 P_TRL_GLOBAL_VARIABLES_REC.TAX_TYPE_CODE_LOW := P_TAX_TYPE_CODE_LOW;
995 P_TRL_GLOBAL_VARIABLES_REC.TAX_TYPE_CODE_HIGH := P_TAX_TYPE_CODE_HIGH;
996 P_TRL_GLOBAL_VARIABLES_REC.DOCUMENT_SUB_TYPE := P_DOCUMENT_SUB_TYPE;
997 P_TRL_GLOBAL_VARIABLES_REC.TRX_BUSINESS_CATEGORY := P_TRX_BUSINESS_CATEGORY;
998 P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_LOW := P_TAX_INVOICE_DATE_LOW;
999 P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH := P_TAX_INVOICE_DATE_HIGH;
1000 P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS := P_POSTING_STATUS;
1001 P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_ACCTED_TAX_LINES := P_EXTRACT_ACCTED_TAX_LINES;
1002 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_ACCOUNTING_SEGMENTS := P_INCLUDE_ACCOUNTING_SEGMENTS;
1003 P_TRL_GLOBAL_VARIABLES_REC.BALANCING_SEGMENT_LOW := P_BALANCING_SEGMENT_LOW;
1004 P_TRL_GLOBAL_VARIABLES_REC.BALANCING_SEGMENT_HIGH := P_BALANCING_SEGMENT_HIGH;
1005 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_DISCOUNTS := P_INCLUDE_DISCOUNTS;
1006 P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_STARTING_LINE_NUM := P_EXTRACT_STARTING_LINE_NUM;
1007 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID := P_REQUEST_ID;
1008 P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME := P_REPORT_NAME;
1009 P_TRL_GLOBAL_VARIABLES_REC.VAT_TRANSACTION_TYPE_CODE := P_VAT_TRANSACTION_TYPE_CODE;
1010 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_FULLY_NR_TAX_FLAG := P_INCLUDE_FULLY_NR_TAX_FLAG;
1011 P_TRL_GLOBAL_VARIABLES_REC.MUNICIPAL_TAX_TYPE_CODE_LOW := P_MUNICIPAL_TAX_TYPE_CODE_LOW;
1012 P_TRL_GLOBAL_VARIABLES_REC.MUNICIPAL_TAX_TYPE_CODE_HIGH := P_MUNICIPAL_TAX_TYPE_CODE_HIGH;
1013 P_TRL_GLOBAL_VARIABLES_REC.PROV_TAX_TYPE_CODE_LOW := P_PROV_TAX_TYPE_CODE_LOW;
1014 P_TRL_GLOBAL_VARIABLES_REC.PROV_TAX_TYPE_CODE_HIGH := P_PROV_TAX_TYPE_CODE_HIGH;
1015 P_TRL_GLOBAL_VARIABLES_REC.EXCISE_TAX_TYPE_CODE_LOW := P_EXCISE_TAX_TYPE_CODE_LOW;
1016 P_TRL_GLOBAL_VARIABLES_REC.EXCISE_TAX_TYPE_CODE_HIGH := P_EXCISE_TAX_TYPE_CODE_HIGH;
1017 P_TRL_GLOBAL_VARIABLES_REC.NON_TAXABLE_TAX_TYPE_CODE := P_NON_TAXABLE_TAX_TYPE_CODE;
1018 P_TRL_GLOBAL_VARIABLES_REC.PER_TAX_TYPE_CODE_LOW := P_PER_TAX_TYPE_CODE_LOW;
1019 P_TRL_GLOBAL_VARIABLES_REC.PER_TAX_TYPE_CODE_HIGH := P_PER_TAX_TYPE_CODE_HIGH;
1020 P_TRL_GLOBAL_VARIABLES_REC.VAT_TAX_TYPE_CODE := P_VAT_TAX_TYPE_CODE;
1021 P_TRL_GLOBAL_VARIABLES_REC.EXCISE_TAX := P_EXCISE_TAX;
1022 P_TRL_GLOBAL_VARIABLES_REC.VAT_ADDITIONAL_TAX := P_VAT_ADDITIONAL_TAX;
1023 P_TRL_GLOBAL_VARIABLES_REC.VAT_NON_TAXABLE_TAX := P_VAT_NON_TAXABLE_TAX;
1024 P_TRL_GLOBAL_VARIABLES_REC.VAT_NOT_TAX := P_VAT_NOT_TAX;
1025 P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX := P_VAT_PERCEPTION_TAX;
1026 P_TRL_GLOBAL_VARIABLES_REC.VAT_TAX := P_VAT_TAX;
1027 P_TRL_GLOBAL_VARIABLES_REC.INC_SELF_WD_TAX := P_INC_SELF_WD_TAX;
1028 P_TRL_GLOBAL_VARIABLES_REC.EXCLUDING_TRX_LETTER := P_EXCLUDING_TRX_LETTER;
1029 P_TRL_GLOBAL_VARIABLES_REC.TRX_LETTER_LOW := P_TRX_LETTER_LOW;
1030 P_TRL_GLOBAL_VARIABLES_REC.TRX_LETTER_HIGH := P_TRX_LETTER_HIGH;
1031 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_REFERENCED_SOURCE := P_INCLUDE_REFERENCED_SOURCE;
1032 P_TRL_GLOBAL_VARIABLES_REC.PARTY_NAME := P_PARTY_NAME;
1033 P_TRL_GLOBAL_VARIABLES_REC.BATCH_NAME := P_BATCH_NAME;
1034 P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID := P_BATCH_SOURCE_ID;
1035 P_TRL_GLOBAL_VARIABLES_REC.ADJUSTED_DOC_FROM := P_ADJUSTED_DOC_FROM;
1036 P_TRL_GLOBAL_VARIABLES_REC.ADJUSTED_DOC_TO := P_ADJUSTED_DOC_TO;
1037 P_TRL_GLOBAL_VARIABLES_REC.STANDARD_VAT_TAX_RATE := P_STANDARD_VAT_TAX_RATE;
1038 P_TRL_GLOBAL_VARIABLES_REC.MUNICIPAL_TAX := P_MUNICIPAL_TAX;
1039 P_TRL_GLOBAL_VARIABLES_REC.PROVINCIAL_TAX := P_PROVINCIAL_TAX;
1040 P_TRL_GLOBAL_VARIABLES_REC.TAX_ACCOUNT_LOW := P_TAX_ACCOUNT_LOW;
1041 P_TRL_GLOBAL_VARIABLES_REC.TAX_ACCOUNT_HIGH := P_TAX_ACCOUNT_HIGH;
1042 P_TRL_GLOBAL_VARIABLES_REC.EXP_CERT_DATE_FROM := P_EXP_CERT_DATE_FROM;
1043 P_TRL_GLOBAL_VARIABLES_REC.EXP_CERT_DATE_TO := P_EXP_CERT_DATE_TO;
1044 P_TRL_GLOBAL_VARIABLES_REC.EXP_METHOD := P_EXP_METHOD;
1045 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_LOW := P_TRX_NUMBER_LOW;
1046 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_HIGH := P_TRX_NUMBER_HIGH;
1047 P_TRL_GLOBAL_VARIABLES_REC.PRINT_COMPANY_INFO := P_PRINT_COMPANY_INFO;
1048 P_TRL_GLOBAL_VARIABLES_REC.ERRBUF := P_ERRBUF;
1049 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := NVL(P_RETCODE,0);
1050 P_TRL_GLOBAL_VARIABLES_REC.ACCOUNTING_STATUS := P_ACCOUNTING_STATUS;
1051 P_TRL_GLOBAL_VARIABLES_REC.REPORTED_STATUS := P_REPORTED_STATUS;
1052 P_TRL_GLOBAL_VARIABLES_REC.TAXABLE_ACCOUNT_LOW := P_TAXABLE_ACCOUNT_LOW;
1053 P_TRL_GLOBAL_VARIABLES_REC.TAXABLE_ACCOUNT_HIGH := P_TAXABLE_ACCOUNT_HIGH;
1054 P_TRL_GLOBAL_VARIABLES_REC.GL_OR_TRX_DATE_FILTER := P_GL_OR_TRX_DATE_FILTER; --Bug 5396444
1055
1056
1057
1058 -- Populate the WHO columns :
1059
1060 g_created_by := nvl(fnd_profile.value('USER_ID'),1);
1061 g_creation_date := sysdate;
1062 g_last_updated_by := nvl(fnd_profile.value('USER_ID'),1);
1063 g_last_update_date := sysdate;
1064 g_last_update_login := 1;
1065
1066 -- Get the max length of P_ERRBUF, rounded down to 50 characters
1067 -- Since it is not possible to know the max length of the variable
1068 -- which was passed to the TRL as IN OUT parameter P_ERRBUF, we need to
1069 -- do this workaround. Otherwise we get Value error if the size
1070 -- of the variable is not sufficient to hold value in L_ERRBUF.
1071 l_length_errbuf := 0;
1072 BEGIN
1073 FOR i IN 1..40 LOOP
1074 p_errbuf := p_errbuf ||
1075 ' ';
1076 l_length_errbuf := l_length_errbuf + 50;
1077 END LOOP;
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 NULL;
1081 END;
1082 l_length_errbuf := least(l_length_errbuf,2000);
1083 IF (g_level_procedure >= g_current_runtime_level ) THEN
1084 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1085 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE(-)');
1086 END IF;
1087 P_ERRBUF := NULL;
1088
1089
1090 IF (g_level_procedure >= g_current_runtime_level ) THEN
1091 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE.END',
1092 'Length of errbuf : '||to_char(l_length_errbuf));
1093 END IF;
1094
1095 EXCEPTION
1096 WHEN OTHERS THEN
1097 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1098 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1099 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','initialize- '|| g_error_buffer);
1100 FND_MSG_PUB.Add;
1101 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1102 FND_LOG.STRING(g_level_unexpected,
1103 'ZX.TRL.ZX_EXTRACT_PKG.initialize',
1104 'initialize Check : ');
1105 FND_LOG.STRING(g_level_unexpected,
1106 'ZX.TRL.ZX_EXTRACT_PKG.initialize', g_error_buffer);
1107 END IF;
1108 APPEND_ERRBUF(g_error_buffer);
1109 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1110
1111 END initialize;
1112
1113 /*===========================================================================+
1114 | PROCEDURE |
1115 | derive_dependent_parameters |
1116 | |
1117 | DESCRIPTION |
1118 | This procedure derives the dependent parameters |
1119 | for procedure ZX_EXTRACT_PKG.POPULATE |
1120 | |
1121 | Called from ZX_EXTRACT_PKG.POPULATE |
1122 | |
1123 | SCOPE - Private |
1124 | |
1125 +===========================================================================*/
1126
1127 PROCEDURE derive_dependent_parameters (
1128 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1129 IS
1130 l_operating_unit_id NUMBER;
1131 l_ledger_name GL_LEDGERS.Name%TYPE;
1132 l_primary_ledger_id NUMBER(15);
1133 l_legal_entity_id NUMBER;
1134 l_ledger_category gl_ledgers.ledger_category_code%TYPE;
1135
1136
1137 CURSOR le_ledger_cur (c_legal_entity_id NUMBER) IS
1138 SELECT ledger_id
1139 FROM gl_ledger_le_v
1140 WHERE legal_entity_id = c_legal_entity_id
1141 AND ledger_category_code = 'PRIMARY';
1142
1143 CURSOR chart_of_acc_id (c_ledger_id number) IS
1144 SELECT chart_of_accounts_id, name, currency_code
1145 FROM gl_sets_of_books
1146 WHERE set_of_books_id = c_ledger_id;
1147
1148 BEGIN
1149
1150 IF (g_level_procedure >= g_current_runtime_level ) THEN
1151 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters.BEGIN',
1152 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters(+)');
1153 END IF;
1154
1155
1156
1157 IF p_trl_global_variables_rec.reporting_level = '1000' THEN
1158 g_ledger_id := p_trl_global_variables_rec.reporting_context;
1159 l_legal_entity_id := p_trl_global_variables_rec.legal_entity_id;
1160 IF (g_level_procedure >= g_current_runtime_level ) THEN
1161 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 1000',
1162 'g_ledger_id : ' || to_char(g_ledger_id) ||
1163 'l_legal_entity_id : ' || to_char(l_legal_entity_id));
1164 END IF;
1165 ELSIF p_trl_global_variables_rec.reporting_level = '2000' THEN
1166 l_legal_entity_id := p_trl_global_variables_rec.reporting_context;
1167 p_trl_global_variables_rec.legal_entity_id := l_legal_entity_id;
1168 IF (g_level_procedure >= g_current_runtime_level ) THEN
1169 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 2000',
1170 'l_legal_entity_id : ' || to_char(l_legal_entity_id));
1171 END IF;
1172
1173 ELSIF p_trl_global_variables_rec.reporting_level = '3000' THEN
1174 l_operating_unit_id := p_trl_global_variables_rec.reporting_context;
1175 l_legal_entity_id := XLE_UTILITIES_GRP.GET_DefaultLegalContext_OU(l_operating_unit_id);
1176 p_trl_global_variables_rec.legal_entity_id := l_legal_entity_id;
1177
1178 IF (g_level_procedure >= g_current_runtime_level ) THEN
1179 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 3000',
1180 'l_operating_unit_id : ' || to_char(l_operating_unit_id) ||
1181 'l_legal_entity_id : ' || to_char(l_legal_entity_id));
1182 END IF;
1183
1184 IF p_trl_global_variables_rec.ledger_id IS NOT NULL THEN
1185 g_ledger_id := p_trl_global_variables_rec.ledger_id;
1186 ELSE
1187 mo_utils.get_ledger_info(l_operating_unit_id,
1188 g_ledger_id ,
1189 l_ledger_name );
1190 END IF;
1191 IF (g_level_procedure >= g_current_runtime_level ) THEN
1192 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 3000.2',
1193 'l_operating_unit_id : ' || to_char(l_operating_unit_id) ||
1194 'g_ledger_id : ' || to_char(g_ledger_id) ||
1195 'l_ledger_name : ' || to_char(l_ledger_name));
1196 END IF;
1197
1198 END IF;
1199
1200 IF P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS = 'POSTED' THEN
1201 p_trl_global_variables_rec.accounting_status := 'ACCOUNTED';
1202 END IF;
1203
1204
1205 IF p_trl_global_variables_rec.reporting_level = '2000' THEN
1206 -- IF p_trl_global_variables_rec.legal_entity_id is not NULL THEN
1207 OPEN le_ledger_cur (l_legal_entity_id);
1208 FETCH le_ledger_cur INTO g_ledger_id;
1209 CLOSE le_ledger_cur;
1210 -- END IF;
1211 END IF;
1212
1213 --Get the Ledger Type for a given Ledger ID.
1214 --Get the primary ledger ID if the ledger type is reprting.
1215
1216 -- Reporting Ledger ----- Secondary Leders --
1217 IF nvl(p_trl_global_variables_rec.ledger_id,g_ledger_id) IS NOT NULL THEN
1218 l_ledger_category := gl_mc_info.get_ledger_category(nvl(p_trl_global_variables_rec.ledger_id,
1219 g_ledger_id));
1220 IF l_ledger_category <> 'PRIMARY' THEN
1221 gl_mc_info.get_sob_type(nvl(p_trl_global_variables_rec.ledger_id,g_ledger_id),
1222 g_ledger_type);
1223 IF NVL(g_ledger_type,'R') IN ('R','N') THEN
1224 p_trl_global_variables_rec.reporting_ledger_id := nvl(p_trl_global_variables_rec.ledger_id,g_ledger_id);
1225 p_trl_global_variables_rec.accounting_status := 'ACCOUNTED';
1226 IF (g_level_procedure >= g_current_runtime_level ) THEN
1227 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1228 'g_ledger_type and Reporting Ledger = '||g_ledger_type||'-'
1229 ||to_char(p_trl_global_variables_rec.reporting_ledger_id));
1230 END IF;
1231 END IF;
1232 END IF;
1233 END IF;
1234
1235
1236
1237 IF (g_level_procedure >= g_current_runtime_level ) THEN
1238 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1239 'Accounting_status = '||p_trl_global_variables_rec.accounting_status);
1240 END IF;
1241
1242
1243 IF nvl(g_ledger_type,'R') IN ('R','N') THEN
1244 -- g_ledger_type := 'P';
1245 -- ELSE
1246 -- p_trl_global_variables_rec.reporting_ledger_id := g_ledger_id;
1247 l_primary_ledger_id := gl_mc_info.get_primary_ledger_id(g_ledger_id);
1248 g_ledger_id := l_primary_ledger_id;
1249 IF p_trl_global_variables_rec.reporting_level = '1000' THEN
1250 p_trl_global_variables_rec.reporting_context := g_ledger_id;
1251 END IF;
1252 END IF;
1253
1254 p_trl_global_variables_rec.ledger_id := g_ledger_id;
1255
1256 IF (g_level_procedure >= g_current_runtime_level ) THEN
1257 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1258 'g_ledger_type = '||g_ledger_type||'-'||to_char(g_ledger_id));
1259 END IF;
1260
1261
1262 OPEN chart_of_acc_id (p_trl_global_variables_rec.ledger_id);
1263 FETCH chart_of_acc_id
1264 INTO
1265 P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
1266 P_TRL_GLOBAL_VARIABLES_REC.ledger_name ,
1267 P_TRL_GLOBAL_VARIABLES_REC.func_currency_code;
1268 CLOSE chart_of_acc_id;
1269
1270 IF (g_level_procedure >= g_current_runtime_level ) THEN
1271 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1272 'Chart of Accounts ID =' ||to_char(P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id));
1273 END IF;
1274
1275 --derive P_GL_DATE_LOW/HIGH from P_GL_PERIOD_NAME_LOW/HIGH
1276
1277 BEGIN
1278 IF P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_LOW IS NOT NULL AND
1279 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW IS NULL THEN
1280
1281 SELECT start_date
1282 INTO p_trl_global_variables_rec.gl_date_low
1283 FROM gl_period_statuses
1284 WHERE upper(period_name) = upper(p_trl_global_variables_rec.gl_period_name_low)
1285 AND set_of_books_id = g_ledger_id
1286 AND application_id = 101;
1287
1288 END IF;
1289
1290 IF (g_level_procedure >= g_current_runtime_level ) THEN
1291 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1292 ' p_trl_global_variables_rec.gl_period_name_low =' ||to_char(p_trl_global_variables_rec.gl_date_low));
1293 END IF;
1294
1295
1296 IF P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_HIGH IS NOT NULL
1297 AND P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH IS NULL THEN
1298
1299 SELECT end_date
1300 INTO p_trl_global_variables_rec.gl_date_high
1301 FROM gl_period_statuses
1302 WHERE upper(period_name) = upper(p_trl_global_variables_rec.gl_period_name_high)
1303 AND set_of_books_id = g_ledger_id
1304 AND application_id = 101;
1305
1306 END IF;
1307
1308 IF (g_level_procedure >= g_current_runtime_level ) THEN
1309 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1310 ' p_trl_global_variables_rec.gl_period_name_high =' ||to_char(p_trl_global_variables_rec.gl_date_high));
1311 END IF;
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1316 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1317 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1318 FND_MSG_PUB.Add;
1319 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1320 FND_LOG.STRING(g_level_unexpected,
1321 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1322 'Period Name Low and High parameter has error :');
1323 FND_LOG.STRING(g_level_unexpected,
1324 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1325 g_error_buffer);
1326 END IF;
1327 APPEND_ERRBUF(g_error_buffer);
1328 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1329
1330
1331 END;
1332
1333
1334 -- derive P_TRX_DATE_LOW/HIGH from P_TRX_DATE_PERIOD_NAME_LOW / HIGH
1335
1336 BEGIN
1337 IF P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_LOW IS NOT NULL
1338 AND P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_LOW IS NULL THEN
1339
1340 SELECT start_date
1341 INTO p_trl_global_variables_rec.trx_date_low
1342 FROM gl_period_statuses
1343 WHERE period_name = p_trl_global_variables_rec.trx_date_period_name_low
1344 AND set_of_books_id = g_ledger_id
1345 AND application_id = 101;
1346
1347 END IF;
1348
1349 IF P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_HIGH IS NOT NULL
1350 AND P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH IS NULL THEN
1351
1352 SELECT end_date
1353 INTO p_trl_global_variables_rec.trx_date_high
1354 FROM gl_period_statuses
1355 WHERE period_name = p_trl_global_variables_rec.trx_date_period_name_high
1356 AND set_of_books_id = g_ledger_id
1357 AND application_id = 101;
1358
1359 END IF;
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1364 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1365 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1366 FND_MSG_PUB.Add;
1367 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1368 FND_LOG.STRING(g_level_unexpected,
1369 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1370 'Transaction Date Low and High parameter has error :');
1371 FND_LOG.STRING(g_level_unexpected,
1372 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1373 g_error_buffer);
1374 END IF;
1375 APPEND_ERRBUF(g_error_buffer);
1376 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1377 END;
1378
1379 IF (g_level_procedure >= g_current_runtime_level ) THEN
1380 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters.END',
1381 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters(-)');
1382 END IF;
1383
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1387 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1388 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','derive_dependent_parameters- '|| g_error_buffer);
1389 FND_MSG_PUB.Add;
1390 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1391 FND_LOG.STRING(g_level_unexpected,
1392 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1393 'Multi Org Utility : mo_utils.get_ledger_info :');
1394 FND_LOG.STRING(g_level_unexpected,
1395 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1396 g_error_buffer);
1397 END IF;
1398 APPEND_ERRBUF(g_error_buffer);
1399 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1400 END derive_dependent_parameters;
1401
1402 /*===========================================================================+
1403 | PROCEDURE |
1404 | VALIDATE_PARAMETERS |
1405 | |
1406 | DESCRIPTION |
1407 | This procedure validates the parameters supplied by the user in the |
1408 | ZX_EXTRACT_PKG.POPULATE and gives error message if he parameter |
1409 | values passed are invalid. |
1410 | |
1411 | Called from ZX_EXTRACT_PKG.POPULATE |
1412 | |
1413 | SCOPE - Private |
1414 | |
1415 | NOTES |
1416 | |
1417 | MODIFICATION HISTORY |
1418 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
1419 | |
1420 +===========================================================================*/
1421
1422 FUNCTION VALIDATE_PARAMETERS (
1423 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1424 RETURN BOOLEAN IS
1425
1426 C_ORDER_BY VARCHAR2(30);
1427 l_count NUMBER;
1428
1429 BEGIN
1430 -- Validation of Parameters:
1431
1432 SELECT count(*) INTO l_count
1433 FROM zx_rep_context_t
1434 WHERE request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1435
1436 IF l_count > 0 THEN
1437 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1438 FND_LOG.STRING(g_level_unexpected,
1439 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1440 'Duplicate request ID :');
1441 FND_LOG.STRING(g_level_unexpected,
1442 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1443 g_error_buffer);
1444 END IF;
1445
1446 END IF;
1447
1448
1449 RETURN TRUE;
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1454 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1455 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','derive_dependent_parameters- '|| g_error_buffer);
1456 FND_MSG_PUB.Add;
1457 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1458 FND_LOG.STRING(g_level_unexpected,
1459 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1460 'Request ID Duplicate check :');
1461 FND_LOG.STRING(g_level_unexpected,
1462 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1463 g_error_buffer);
1464 END IF;
1465 APPEND_ERRBUF(g_error_buffer);
1466 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1467
1468 END VALIDATE_PARAMETERS;
1469
1470
1471 /*===========================================================================+
1472 | PROCEDURE |
1473 | extract_rep_context_info |
1474 | |
1475 | description |
1476 | this PROCEDURE gets the reporting context for the header record |
1477 | called FROM the constructor of zx_extract_pkg |
1478 | |
1479 | scope - private |
1480 | |
1481 | notes |
1482 | |
1483 | |
1484 +===========================================================================*/
1485
1486 PROCEDURE extract_rep_context_info (
1487 p_trl_global_variables_rec IN OUT NOCOPY ZX_EXTRACT_PKG.trl_global_variables_rec_type)
1488 IS
1489 l_operating_unit_id NUMBER;
1490 l_reporting_context_org_id NUMBER(15);
1491 l_reporting_context_tax_reg_no VARCHAR2(60);
1492 l_reporting_context_name VARCHAR2(100);
1493 l_reporting_sob_name VARCHAR2(100);
1494 l_functional_currency_code VARCHAR2(15);
1495 l_ledger_id NUMBER;
1496 l_legal_entity_id NUMBER;
1497
1498 /* apai
1499 CURSOR ledger_ou_cursor (c_ledger_id NUMBER ) IS
1500 SELECT organization_id
1501 FROM hr_operating_units
1502 WHERE mo_global.check_access(organization_id) = 'Y'
1503 AND SET_OF_BOOKS_ID = c_ledger_id ;
1504 */
1505 BEGIN
1506
1507 IF (g_level_procedure >= g_current_runtime_level ) THEN
1508 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.BEGIN',
1509 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info(+)');
1510 END IF;
1511
1512 -- get the structure of address location dff
1513
1514 fnd_dflex.get_flexfield(
1515 'PER',
1516 'Address Location',
1517 pr_flexfield,
1518 pr_flexinfo);
1519
1520 IF (g_level_procedure >= g_current_runtime_level ) THEN
1521 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.BEGIN',
1522 'pr_flexfield :');
1523 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
1524 'p_trl_global_variables_rec.legal_entity_id : p_trl_global_variables_rec.legal_entity_id');
1525
1526 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
1527 'Before call to insert_rep_context_itf');
1528
1529 END IF;
1530
1531 --IF p_trl_global_variables_rec.legal_entity_id IS NOT NULL THEN
1532 insert_rep_context_itf(
1533 p_trl_global_variables_rec,
1534 p_trl_global_variables_rec.legal_entity_id);
1535 --END IF;
1536
1537 /* apai
1538 ELSIF p_trl_global_variables_rec.reporting_level = '1000' THEN
1539
1540 IF (g_level_procedure >= g_current_runtime_level ) THEN
1541 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.BEGIN',
1542 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info : Call to insert_rep_context_itf');
1543 END IF;
1544
1545 BEGIN
1546 -- IF g_ledger_type = 'R' THEN
1547 -- l_ledger_id := gl_mc_info.get_primary_ledger_id(g_ledger_id);
1548 -- ELSE
1549 -- l_ledger_id := p_trl_global_variables_rec.reporting_context;
1550 -- END IF;
1551
1552 -- Above coditions are taken care in the derive dependent parameters API.
1553 -- This API always populates primary ledger ID in g_ledger_id which is a
1554 -- global variable.
1555 OPEN ledger_ou_cursor(g_ledger_id );
1556 BEGIN
1557 LOOP
1558 FETCH ledger_ou_cursor INTO l_operating_unit_id;
1559
1560 IF ledger_ou_cursor%NOTFOUND THEN
1561 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1562 -- FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1563 -- FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1564 -- FND_MSG_PUB.Add;
1565 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1566 FND_LOG.STRING(g_level_unexpected,
1567 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
1568 g_error_buffer);
1569 END IF;
1570
1571 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1572 END IF;
1573 EXIT when ledger_ou_cursor%NOTFOUND ;
1574 insert_rep_context_itf(p_trl_global_variables_rec,l_operating_unit_id);
1575 END LOOP;
1576 END;
1577
1578 IF (ledger_ou_cursor%ISOPEN) THEN
1579 CLOSE ledger_ou_cursor;
1580 END IF;
1581 END;
1582
1583 ELSIF p_trl_global_variables_rec.reporting_level = '3000' THEN
1584 l_operating_unit_id := p_trl_global_variables_rec.reporting_context;
1585 insert_rep_context_itf(
1586 p_trl_global_variables_rec,
1587 l_operating_unit_id);
1588 END IF; -- reporting level
1589 */
1590 IF (g_level_procedure >= g_current_runtime_level ) THEN
1591 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.END',
1592 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info(-)');
1593 END IF;
1594
1595 EXCEPTION
1596 WHEN OTHERS THEN
1597 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1598 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1599 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1600 FND_MSG_PUB.Add;
1601 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1602 FND_LOG.STRING(g_level_unexpected,
1603 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
1604 g_error_buffer);
1605 END IF;
1606 APPEND_ERRBUF(g_error_buffer);
1607 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1608
1609
1610 END extract_rep_context_info ;
1611
1612 /*===========================================================================+
1613 | PROCEDURE |
1614 | extract_tax_info |
1615 | |
1616 | DESCRIPTION |
1617 | Called from ZX_EXTRACT_PKG.populate_tax_data |
1618 | |
1619 | SCOPE - Private |
1620 | |
1621 | NOTES |
1622 | |
1623 | MODIFICATION HISTORY |
1624 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
1625 +===========================================================================*/
1626
1627 PROCEDURE EXTRACT_TAX_INFO(
1628 p_ledger_type IN VARCHAR2,
1629 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1630 IS
1631
1632 BEGIN
1633
1634 IF (g_level_procedure >= g_current_runtime_level ) THEN
1635 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.EXTRACT_TAX_INFO.BEGIN',
1636 'ZX_EXTRACT_PKG:EXTRACT_TAX_INFO(+)');
1637 END IF;
1638 -- Need to remove this code since the accounting and non accounting apis are merged
1639 /*
1640 IF P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_ACCTED_TAX_LINES = 'Y' OR
1641 P_ledger_type = 'R' THEN
1642
1643 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AR' OR
1644 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
1645
1646 ZX_AR_ACTG_EXTRACT_PKG.insert_tax_data (
1647 P_MRC_SOB_TYPE => P_ledger_type,
1648 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
1649 );
1650
1651 l_ar_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1652
1653 ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines(
1654 P_TRL_GLOBAL_VARIABLES_REC);
1655 ZX_JL_EXTRACT_PKG.FILTER_JL_AR_TAX_LINES(P_TRL_GLOBAL_VARIABLES_REC);
1656 END IF;
1657
1658 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
1659 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
1660
1661 ZX_AP_ACTG_EXTRACT_PKG.insert_tax_data (
1662 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
1663 );
1664 l_ap_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1665 ZX_JA_EXTRACT_PKG.filter_ja_ap_tax_lines(
1666 P_TRL_GLOBAL_VARIABLES_REC);
1667 END IF;
1668
1669
1670 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
1671 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
1672
1673 ZX_GL_EXTRACT_PKG.insert_tax_data(
1674 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
1675 );
1676 l_gl_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1677 END IF;
1678
1679 ELSE
1680 */ -- Need to remove this code since the accounting and non accounting apis are merged
1681 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AR' OR
1682 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
1683
1684 ZX_AR_EXTRACT_PKG.insert_tax_data (
1685 P_MRC_SOB_TYPE => P_ledger_type,
1686 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
1687 );
1688 l_ar_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1689 IF l_ar_retcode <> 2 THEN
1690 ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines(
1691 P_TRL_GLOBAL_VARIABLES_REC);
1692 ZX_JL_EXTRACT_PKG.FILTER_JL_AR_TAX_LINES(P_TRL_GLOBAL_VARIABLES_REC);
1693 END IF;
1694 END IF;
1695
1696 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
1697 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
1698
1699 ZX_AP_EXTRACT_PKG.insert_tax_data (
1700 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
1701 );
1702 l_ap_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1703 IF l_ap_retcode <> 2 THEN
1704 ZX_JA_EXTRACT_PKG.filter_ja_ap_tax_lines(
1705 P_TRL_GLOBAL_VARIABLES_REC);
1706 END IF;
1707 END IF;
1708
1709
1710 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
1711 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
1712
1713 ZX_GL_EXTRACT_PKG.insert_tax_data(
1714 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
1715 );
1716 l_gl_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1717 END IF;
1718 -- END IF;
1719
1720
1721
1722 IF (g_level_procedure >= g_current_runtime_level ) THEN
1723 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_tax_info.END',
1724 'ZX_EXTRACT_PKG:extract_tax_info(-)');
1725 END IF;
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1730 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1731 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1732 FND_MSG_PUB.Add;
1733 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1734 FND_LOG.STRING(g_level_unexpected,
1735 'ZX.TRL.ZX_EXTRACT_PKG.extract_tax_info',
1736 g_error_buffer);
1737 END IF;
1738 APPEND_ERRBUF(g_error_buffer);
1739 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1740
1741 END EXTRACT_TAX_INFO;
1742
1743 /*===========================================================================+
1744 | PROCEDURE |
1745 | extract_additional_info |
1746 | |
1747 | DESCRIPTION |
1748 | This procedure populates Additional information in |
1749 | zx_rep_context_t |
1750 | |
1751 | Called from ZX_EXTRACT_PKG.POPULATE |
1752 | |
1753 | MODIFICATION HISTORY |
1754 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
1755 +===========================================================================*/
1756
1757 PROCEDURE extract_additional_info(
1758 p_ledger_type IN VARCHAR2,
1759 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1760 IS
1761
1762
1763 /*CURSOR rep_context_cursor(
1764 c_request_id IN NUMBER,
1765 c_set_of_books_id IN NUMBER)
1766 IS
1767 SELECT rep_context.rep_context_id , rep_context.rep_entity_id ,
1768 mo.operating_unit_id
1769 FROM zx_rep_context_t rep_context,
1770 fnd_mo_reporting_entities_v mo
1771 WHERE rep_context.rep_entity_id = mo.operating_unit_id
1772 AND mo.ledger_id = c_set_of_books_id
1773 AND rep_context.request_id = c_request_id
1774 AND mo.reporting_level = '3000';
1775 --P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL;
1776 -- '3000' ; */
1777
1778 CURSOR rep_context_cursor(
1779 c_request_id IN NUMBER) IS
1780 SELECT rep_context.rep_context_id,
1781 rep_context.rep_entity_id ,
1782 rep_context.rep_entity_id
1783 FROM zx_rep_context_t rep_context
1784 WHERE rep_context.request_id = c_request_id;
1785
1786
1787 l_rep_context_id_rec zx_extract_pkg.rep_context_id_rectype;
1788 i BINARY_INTEGER;
1789 l_org_id NUMBER;
1790 l_ledger_id NUMBER;
1791
1792 BEGIN
1793
1794 IF (g_level_procedure >= g_current_runtime_level ) THEN
1795 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info.BEGIN',
1796 'ZX_EXTRACT_PKG:extract_additional_info(+)');
1797 END IF;
1798
1799 BEGIN
1800 -- IF p_ledger_type = 'R' THEN
1801 -- l_ledger_id := gl_mc_info.get_primary_ledger_id(
1802 -- g_ledger_id
1803 -- );
1804 -- open rep_context_cursor(
1805 -- P_TRL_GLOBAL_VARIABLES_REC.request_id,
1806 -- l_ledger_id
1807 -- );
1808 -- ELSE
1809 -- open rep_context_cursor(
1810 -- P_TRL_GLOBAL_VARIABLES_REC.request_id,
1811 -- g_ledger_id
1812 -- );
1813 open rep_context_cursor(
1814 P_TRL_GLOBAL_VARIABLES_REC.request_id
1815 );
1816 -- END IF;
1817
1818 i := 1;
1819 rep_context_id_tab.delete;
1820 LOOP
1821 FETCH rep_context_cursor INTO l_rep_context_id_rec;
1822 EXIT WHEN rep_context_cursor%NOTFOUND;
1823 rep_context_id_tab(i) := l_rep_context_id_rec;
1824 IF (g_level_procedure >= g_current_runtime_level ) THEN
1825 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1826 'rep_context_id_tab(i)'||to_char(rep_context_id_tab(i).rep_context_id));
1827 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1828 'l_rep_context_id_rec :'||to_char(l_rep_context_id_rec.rep_context_id));
1829 END IF;
1830 i := i + 1;
1831
1832 END LOOP;
1833
1834 IF rep_context_cursor%isopen THEN
1835 CLOSE rep_context_cursor;
1836 END IF;
1837
1838 EXCEPTION
1839 WHEN OTHERS THEN
1840 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1841 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1842 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','extract_additional_info- '|| g_error_buffer);
1843 FND_MSG_PUB.Add;
1844 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1845 FND_LOG.STRING(g_level_unexpected,
1846 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1847 g_error_buffer);
1848 END IF;
1849
1850 END;
1851
1852 -- Call to AR, AP Populate API calls to populate additional information
1853
1854
1855
1856 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AR' OR
1857 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
1858 THEN
1859 IF L_AR_RETCODE <> 2 THEN
1860 ZX_AR_POPULATE_PKG.update_additional_info(
1861 P_TRL_GLOBAL_VARIABLES_REC);
1862 END IF;
1863 L_AR_RETCODE := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1864 END IF;
1865
1866 IF (P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
1867 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL') AND
1868 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE IN ('TAX','NON-RECOVERABLE','ALL')
1869 THEN
1870 -- IF L_AP_RETCODE <> 2 THEN
1871 ZX_AP_POPULATE_PKG.UPDATE_ADDITIONAL_INFO(
1872 P_TRL_GLOBAL_VARIABLES_REC );
1873 -- END IF;
1874 L_AP_RETCODE := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1875 END IF;
1876
1877 IF (P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
1878 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL')
1879 THEN
1880 -- IF L_AP_RETCODE <> 2 THEN
1881 ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO(
1882 P_TRL_GLOBAL_VARIABLES_REC );
1883 -- END IF;
1884 L_AP_RETCODE := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
1885 END IF;
1886
1887 -- Call to JX populate Plug-in APIs
1888 IF (g_level_procedure >= g_current_runtime_level ) THEN
1889 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1890 'AR JX product Call: Return Code : '||to_char(L_AR_RETCODE));
1891 END IF;
1892
1893 -- ZX_JL_EXTRACT_PKG.populate_jl_ar(P_TRL_GLOBAL_VARIABLES_REC);
1894 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT IN ( 'AR', 'GL') OR
1895 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
1896 THEN
1897 IF L_AR_RETCODE <> 2 THEN
1898 IF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('SG','TW','ZX')
1899 THEN
1900 ZX_JA_EXTRACT_PKG.populate_ja_ar(P_TRL_GLOBAL_VARIABLES_REC);
1901 ELSIF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JGVAT'
1902 THEN
1903 ZX_JE_EXTRACT_PKG.populate_je_ar(P_TRL_GLOBAL_VARIABLES_REC);
1904 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('AR','CL','CO','ZZ')
1905 THEN
1906 ZX_JL_EXTRACT_PKG.populate_jl_ar(P_TRL_GLOBAL_VARIABLES_REC);
1907 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,1,3) = 'ZXX'
1908 THEN
1909 ZX_CORE_REP_EXTRACT_PKG.populate_core_ar(P_TRL_GLOBAL_VARIABLES_REC);
1910 END IF;
1911 END IF;
1912 END IF;
1913
1914 IF (g_level_procedure >= g_current_runtime_level ) THEN
1915 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1916 'AP JX product Call: Return Code : '||to_char(L_AP_RETCODE));
1917 END IF;
1918 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
1919 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
1920 THEN
1921 IF (g_level_procedure >= g_current_runtime_level ) THEN
1922 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1923 'AP JL product Call: Return Code : '||P_TRL_GLOBAL_VARIABLES_REC.PRODUCT);
1924 END IF;
1925
1926 IF L_AP_RETCODE <> 2 THEN
1927 IF (g_level_procedure >= g_current_runtime_level ) THEN
1928 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1929 'AP JL product Call: Return Code : '||to_char(L_AP_RETCODE));
1930 END IF;
1931
1932 IF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('SG','TW','ZX')
1933 THEN
1934 ZX_JA_EXTRACT_PKG.populate_ja_ap(P_TRL_GLOBAL_VARIABLES_REC);
1935 --ZX_JA_EXTRACT_PKG.populate_ja_ar(P_TRL_GLOBAL_VARIABLES_REC);
1936 ELSIF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JGVAT'
1937 THEN
1938 ZX_JE_EXTRACT_PKG.populate_je_ap(P_TRL_GLOBAL_VARIABLES_REC);
1939 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('AR','CL','CO')
1940 THEN
1941 IF (g_level_procedure >= g_current_runtime_level ) THEN
1942 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1943 'AP JL product Call: Return Code : '||'populate_jl_ap');
1944 END IF;
1945
1946 ZX_JL_EXTRACT_PKG.populate_jl_ap(P_TRL_GLOBAL_VARIABLES_REC);
1947 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,1,3) = 'ZXX'
1948 THEN
1949 ZX_CORE_REP_EXTRACT_PKG.populate_core_ap(P_TRL_GLOBAL_VARIABLES_REC);
1950 END IF;
1951 END IF;
1952 END IF;
1953
1954 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
1955 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
1956 THEN
1957 IF L_GL_RETCODE <> 2 THEN
1958 IF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JGVAT'
1959 THEN
1960 ZX_JE_EXTRACT_PKG.populate_je_gl(P_TRL_GLOBAL_VARIABLES_REC);
1961 END IF;
1962 END IF;
1963 END IF;
1964
1965 ZX_JG_EXTRACT_PKG.get_taxable(P_TRL_GLOBAL_VARIABLES_REC);
1966
1967
1968 EXCEPTION
1969 WHEN OTHERS THEN
1970 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1971 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1972 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1973 FND_MSG_PUB.Add;
1974 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1975 FND_LOG.STRING(g_level_unexpected,
1976 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
1977 g_error_buffer);
1978 END IF;
1979 APPEND_ERRBUF(g_error_buffer);
1980 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1981
1982 END extract_additional_info;
1983
1984
1985 /*===========================================================================+
1986 | PROCEDURE |
1987 | CLEANUP |
1988 | |
1989 | DESCRIPTION |
1990 | This procedure deletes the records from AR_TAX_EXTRACT_DCL_IF |
1991 | which do not have any child records in AR_TAX_EXTRACT_SUB_ITF |
1992 | for the given request_id |
1993 | |
1994 | Called from ZX_EXTRACT_PKG.POPULATE |
1995 | |
1996 | SCOPE - Private |
1997 | |
1998 | NOTES |
1999 | |
2000 | MODIFICATION HISTORY |
2001 | |
2002 +===========================================================================*/
2003
2004 PROCEDURE CLEANUP (
2005 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
2006 ) IS
2007
2008 j number := 0;
2009 l_declarer_id number;
2010 l_count number;
2011 BEGIN
2012 /*
2013 -- Performance bug#3264164
2014 type extract_line_id_t is table of
2015 ar_tax_extract_sub_itf.extract_line_id%type
2016 index by binary_integer;
2017 type trx_id_t is table of
2018 ar_tax_extract_sub_itf.trx_id%type
2019 index by binary_integer;
2020 type trx_class_code_t is table of
2021 ar_tax_extract_sub_itf.trx_class_code%type
2022 index by binary_integer;
2023 type tax_code_id_t is table of
2024 ar_tax_extract_sub_itf.tax_code_id%type
2025 index by binary_integer;
2026 type tax_code_register_type_code_t is table of
2027 ar_tax_extract_sub_itf.tax_code_register_type_code%type
2028 index by binary_integer;
2029 type extract_source_ledger_t is table of
2030 ar_tax_extract_sub_itf.extract_source_ledger%type
2031 index by binary_integer;
2032 type extract_report_line_number_t is table of
2033 ar_tax_extract_sub_itf.extract_report_line_number%type
2034 index by binary_integer;
2035
2036 l_extract_line_id extract_line_id_t;
2037 l_trx_id trx_id_t;
2038 l_trx_class_code trx_class_code_t;
2039 l_tax_code_id tax_code_id_t;
2040 l_tax_cd_register_type_cd tax_code_register_type_code_t;
2041 l_extract_source_ledger extract_source_ledger_t;
2042 l_extract_report_line_num extract_report_line_number_t;
2043
2044 BEGIN
2045 l_count := 0;
2046
2047 BEGIN
2048 DELETE FROM AR_TAX_EXTRACT_SUB_ITF WHERE EXTRACT_LINE_ID IN
2049 (select extract_line_id
2050 from ar_tax_extract_sub_itf
2051 where request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
2052 and (trx_id is null
2053 or trx_class_code is null
2054 -- or tax_code_id is null
2055 or tax_code_register_type_code is null
2056 or extract_source_ledger is null
2057 or extract_report_line_number is null))
2058 RETURNING
2059 extract_line_id,
2060 trx_id,
2061 trx_class_code,
2062 tax_code_id,
2063 tax_code_register_type_code,
2064 extract_source_ledger,
2065 extract_report_line_number
2066 BULK COLLECT INTO
2067 l_extract_line_id,
2068 l_trx_id,
2069 l_trx_class_code,
2070 l_tax_code_id,
2071 l_tax_cd_register_type_cd,
2072 l_extract_source_ledger,
2073 l_extract_report_line_num;
2074
2075 -- All the mandatory columns for the selected declarer_id
2076
2077 IF PG_DEBUG = 'Y' THEN
2078 FOR j IN 1..l_extract_line_id.COUNT LOOP
2079 arp_util_tax.debug('Mandatory columns missing for Extract Line Id : '||
2080 to_char(l_extract_line_id(j))||
2081 '. Deleting this line from AR_TAX_EXTRACT_SUB_ITF.');
2082 arp_util_tax.debug('trx_id : ' || to_char(l_trx_id(j)) ||
2083 ' trx_class_code : ' || l_trx_class_code(j) ||
2084 ' tax_code_id : ' || to_char(l_tax_code_id(j)));
2085 arp_util_tax.debug('ax_code_register_type_code : ' ||
2086 l_tax_cd_register_type_cd(j) ||
2087 ' extract_source_ledger : ' || l_extract_source_ledger(j) ||
2088 ' extract_report_line_number : ' ||
2089 to_char(l_extract_report_line_num(j)));
2090 END LOOP;
2091
2092 l_count := l_extract_line_id.COUNT;
2093 arp_util_tax.debug(to_char(nvl(l_count,0))||' records deleted because '
2094 ||'mandatory columns are not populated . ');
2095 arp_util_tax.debug(' ');
2096
2097 END IF;
2098
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 STACK_ERROR('FND','SQL_PLSQL_ERROR','ERRNO',SQLCODE,'REASON',SQLERRM,
2102 'ROUTINE','ZX_EXTRACT_PKG.CLEANUP');
2103 L_MSG := FND_MESSAGE.GET;
2104
2105 END;
2106
2107 declarer_id_lookup_table.delete;
2108 l_count := 0;
2109
2110 -- Performance bug#3264164
2111 DELETE FROM AR_TAX_EXTRACT_SUB_ITF
2112 WHERE TAX_EXTRACT_DECLARER_ID IS NULL
2113 AND REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
2114
2115 l_count := sql%rowcount; -- apai
2116
2117 DELETE FROM zx_rep_trx_detail_t i
2118 WHERE request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
2119 AND rep_context_id is null;
2120 */
2121 NULL;
2122
2123 END CLEANUP;
2124
2125
2126
2127 /*===========================================================================+
2128 | PROCEDURE |
2129 | DELETE_ALL |
2130 | |
2131 | DESCRIPTION |
2132 | This procedure deletes the records from AR_TAX_EXTRACT_DCL_IF |
2133 | and AR_TAX_EXTRACT_SUB_ITF for a given request_id. This procedure |
2134 | is called from the procedure populate if some fatal error condition |
2135 | occurs and error_code is set to 2 |
2136 | |
2137 | Called from ZX_EXTRACT_PKG.POPULATE |
2138 | |
2139 | SCOPE - Private |
2140 | |
2141 | NOTES |
2142 | |
2143 | MODIFICATION HISTORY |
2144 | |
2145 +===========================================================================*/
2146
2147 PROCEDURE DELETE_ALL(P_REQUEST_ID IN NUMBER ) IS
2148 BEGIN
2149 /* IF PG_DEBUG = 'Y' THEN
2150 arp_util_tax.debug('DELETE_ALL(+) ');
2151 END IF;
2152 delete from ar_tax_extract_sub_itf sub_itf
2153 where
2154 sub_itf.request_id = P_REQUEST_ID
2155 and exists
2156 (select dcl_itf.tax_extract_declarer_id
2157 from ar_tax_extract_dcl_itf dcl_itf
2158 where sub_itf.tax_extract_declarer_id=dcl_itf.tax_extract_declarer_id);
2159 commit;
2160
2161 -- check with Srinivas
2162 delete from ar_tax_extract_dcl_itf where request_id = P_REQUEST_ID;
2163 commit; */
2164
2165 NULL;
2166
2167 END;
2168
2169
2170 /*===========================================================================+
2171 | PROCEDURE |
2172 | insert_rep_context_itf |
2173 | |
2174 | DESCRIPTION |
2175 | This PROCEDURE inserts Reporting Context information INTO |
2176 | zx_rep_context_t |
2177 | |
2178 | SCOPE - Private |
2179 | |
2180 | NOTES |
2181 | |
2182 | MODIFICATION HISTORY |
2183 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
2184 +===========================================================================*/
2185
2186 PROCEDURE insert_rep_context_itf(
2187 p_trl_global_variables_rec IN OUT NOCOPY zx_extract_pkg.trl_global_variables_rec_type,
2188 p_rep_entity_id IN NUMBER)
2189 IS
2190
2191 l_style HR_LOCATIONS_ALL.style%TYPE;
2192 l_extract_summary_code VARCHAR2(80);
2193 l_rep_context_id NUMBER;
2194 l_rep_context_org_id NUMBER;
2195 l_request_id NUMBER;
2196 l_rep_entity_id NUMBER;
2197 l_rep_context_tax_reg_no HR_ORGANIZATION_INFORMATION.org_information2%TYPE;
2198 l_rep_context_loc_id HR_LOCATIONS_ALL.location_id%TYPE;
2199 l_rep_context_name HR_ORGANIZATION_UNITS.name%TYPE;
2200 l_rep_context_city HR_LOCATIONS_ALL.town_or_city%TYPE;
2201 l_rep_context_county HR_LOCATIONS_ALL.region_3%TYPE;
2202 l_rep_context_state HR_LOCATIONS_ALL.region_2%TYPE;
2203 l_rep_context_province HR_LOCATIONS_ALL.region_1%TYPE;
2204 l_rep_context_address1 HR_LOCATIONS_ALL.address_line_1%TYPE;
2205 l_rep_context_address2 HR_LOCATIONS_ALL.address_line_2%TYPE;
2206 l_rep_context_address3 HR_LOCATIONS_ALL.address_line_3%TYPE;
2207 l_rep_context_country HR_LOCATIONS_ALL.country%TYPE;
2208 l_rep_context_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2209 l_rep_context_phone_number HR_LOCATIONS_ALL.telephone_number_1%TYPE;
2210 l_rep_context_lvl_mng VARCHAR2(80);
2211 l_rep_context_lvl_code VARCHAR2(30);
2212 l_matrix_report_flag VARCHAR2(30);
2213 l_rep_context_entity_region1 VARCHAR2(30);
2214 l_legal_contact_pre_name_adj VARCHAR2(30);
2215 l_legal_contact_party_name xle_legal_contacts_v.contact_name%TYPE;
2216 l_bank_id NUMBER;
2217 l_bank_branch_id NUMBER;
2218 l_bank_account_num NUMBER;
2219 l_taxpayer_id xle_firstparty_information_v.REGISTRATION_NUMBER%TYPE;
2220 l_legal_contact_title xle_legal_contacts_v.title%TYPE;
2221 --l_legal_contact_job_title xle_legal_contacts_v.job_title%TYPE;
2222 l_legal_contact_job_title varchar2(13);--xle_legal_contacts_v.role%TYPE;
2223 l_activity_code xle_firstparty_information_v.activity_code%TYPE;
2224 l_sub_activity_code xle_firstparty_information_v.activity_code%TYPE;
2225 l_inception_date DATE;
2226 l_legal_contact_party_num xle_legal_contacts_v.contact_legal_id%TYPE;
2227 l_legal_auth_address_line2 xle_legalauth_v.address2%TYPE;
2228 l_legal_auth_address_line3 xle_legalauth_v.address3%TYPE;
2229 l_legal_auth_city xle_legalauth_v.city%TYPE;
2230 l_legal_auth_name xle_legalauth_v.authority_name%TYPE;
2231 l_org_information2 VARCHAR2(150);
2232 l_program_application_id NUMBER;
2233 l_program_id NUMBER;
2234 l_program_login_id NUMBER;
2235 l_rowcount NUMBER;
2236 l_hq_party_id NUMBER ;
2237 x_return_status varchar2(200);
2238
2239 /* apai
2240 CURSOR c_loc_rec (c_rep_entity_id NUMBER) IS
2241 SELECT loc.country,
2242 loc.town_or_city,
2243 loc.region_1,
2244 loc.region_2,
2245 loc.region_3,
2246 loc.address_line_1,
2247 loc.address_line_2,
2248 loc.address_line_3,
2249 loc.postal_code,
2250 loc.telephone_number_1,
2251 loc.style,
2252 loc.location_id
2253 FROM hr_locations loc,
2254 hr_organization_units org
2255 WHERE org.location_id = loc.location_id
2256 AND org.organization_id = c_rep_entity_id;
2257 */
2258 CURSOR c_legal_info (c_rep_entity_id number) IS
2259 SELECT
2260 xle_firstpty.name ,
2261 xle_firstpty.activity_code,
2262 xle_firstpty.sub_activity_code,
2263 xle_firstpty.registration_number,
2264 -- xle_firstpty.effective_from
2265 xle_firstpty.location_id,
2266 xle_firstpty.address_line_1,
2267 xle_firstpty.address_line_2,
2268 xle_firstpty.address_line_3,
2269 xle_firstpty.town_or_city,
2270 xle_firstpty.region_1,
2271 xle_firstpty.region_2,
2272 xle_firstpty.region_3,
2273 xle_firstpty.postal_code,
2274 -- xle_firstpty.phone_number,
2275 xle_firstpty.country,
2276 xle_firstpty.address_style
2277 -- xle_cont.contact_name,
2278 -- xle_cont.contact_legal_id,
2279 -- xle_cont.title,
2280 -- xle_cont.job_title
2281 -- xle_cont.role
2282 FROM xle_firstparty_information_v xle_firstpty
2283 -- xle_legal_contacts_v xle_cont
2284 WHERE xle_firstpty.legal_entity_id = c_rep_entity_id;
2285 -- xle_firstpty.legal_entity_id = xle_cont.entity_id(+)
2286
2287 CURSOR c_legal_auth_info (c_rep_entity_id number) IS
2288 SELECT xle_auth.address2,
2289 xle_auth.address3,
2290 xle_auth.city,
2291 xle_auth.authority_name
2292 FROM xle_legalauth_v xle_auth,
2293 xle_registrations xle_reg
2294 WHERE xle_reg.source_id = c_rep_entity_id
2295 AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
2296 AND xle_auth.legalauth_id = xle_reg.issuing_authority_id
2297 AND xle_reg.identifying_flag = 'Y';
2298
2299 CURSOR c_legal_contact_info (c_rep_entity_id number) IS
2300 SELECT per.party_name, -- contact_name,
2301 per.jgzz_fiscal_code, --contact_legal_id,
2302 -- rol.lookup_code, --job Title
2303 XLE_CONTACT_GRP.concat_contact_roles
2304 (rel.subject_id,
2305 rel.object_id),
2306 hzpp.person_pre_name_adjunct -- title
2307 FROM HZ_PARTIES per,
2308 xle_entity_profiles xep,
2309 HZ_RELATIONSHIPS rel,
2310 hz_person_profiles hzpp,
2311 HZ_ORG_CONTACTS con
2312 --XLE_CONTACT_LEGAL_ROLES rol
2313 WHERE rel.relationship_code = 'CONTACT_OF'
2314 AND rel.object_id = xep.party_id
2315 AND per.party_id = hzpp.party_id
2316 AND rel.relationship_type = 'CONTACT'
2317 AND rel.directional_flag = 'F'
2318 AND rel.subject_table_name = 'HZ_PARTIES'
2319 AND rel.subject_type = 'PERSON'
2320 AND rel.subject_id = per.party_id
2321 -- AND rel.subject_id = rol.contact_party_id
2322 -- AND rel.object_id = rol.le_etb_party_id
2323 AND rel.object_table_name = 'HZ_PARTIES'
2324 AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)
2325 AND rel.relationship_id = con.party_relationship_id
2326 AND xep.legal_entity_id = c_rep_entity_id
2327 UNION
2328 SELECT per.party_name, --contact_name,
2329 per.jgzz_fiscal_code, --contact_legal_id,
2330 -- rol.lookup_code, --job Title
2331 XLE_CONTACT_GRP.concat_contact_roles
2332 (rel.subject_id,
2333 rel.object_id),
2334 hzpp.person_pre_name_adjunct --title,
2335 FROM HZ_PARTIES per,
2336 xle_etb_profiles etb,
2337 HZ_RELATIONSHIPS rel,
2338 hz_person_profiles hzpp,
2339 HZ_ORG_CONTACTS con
2340 -- XLE_CONTACT_LEGAL_ROLES rol
2341 WHERE rel.relationship_code = 'CONTACT_OF'
2342 AND rel.object_id = etb.party_id
2343 AND per.party_id = hzpp.party_id
2344 AND rel.relationship_type = 'CONTACT'
2345 AND rel.directional_flag = 'F'
2346 AND rel.subject_table_name = 'HZ_PARTIES'
2347 AND rel.subject_type = 'PERSON'
2348 AND rel.subject_id = per.party_id
2349 --AND rel.subject_id = rol.contact_party_id
2350 --AND rel.object_id = rol.le_etb_party_id
2351 AND rel.object_table_name = 'HZ_PARTIES'
2352 AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)
2353 AND rel.relationship_id = con.party_relationship_id
2354 AND etb.establishment_id = c_rep_entity_id ;
2355
2356
2357 BEGIN
2358
2359 IF (g_level_procedure >= g_current_runtime_level ) THEN
2360 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf.BEGIN',
2361 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf(+)');
2362 END IF;
2363
2364 IF p_trl_global_variables_rec.summary_level = 'TRANSACTION_DISTRIBUTION' THEN
2365 l_extract_summary_code := 'D';
2366 ELSIF p_trl_global_variables_rec.summary_level = 'TRANSACTION_LINE' THEN
2367 l_extract_summary_code := 'L';
2368 ELSE
2369 l_extract_summary_code := 'H';
2370 END IF;
2371
2372
2373 IF p_trl_global_variables_rec.reporting_level = '1000' THEN
2374 l_rep_context_lvl_mng := 'Ledger';
2375 ELSIF p_trl_global_variables_rec.reporting_level = '2000' THEN
2376 l_rep_context_lvl_mng := 'Legal Entity';
2377 ELSIF p_trl_global_variables_rec.reporting_level = '3000' THEN
2378 l_rep_context_lvl_mng := 'Operating Unit';
2379 END IF;
2380
2381 --Bug 5438409
2382 IF (g_level_statement >= g_current_runtime_level ) THEN
2383 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2384 'l_rep_context_lvl_mng : '||l_rep_context_lvl_mng);
2385 END IF;
2386
2387 /* apai
2388 IF p_trl_global_variables_rec.legal_entity_level IS NOT NULL THEN
2389 */
2390 OPEN c_legal_info (p_trl_global_variables_rec.legal_entity_id);
2391 FETCH c_legal_info INTO
2392 l_rep_context_name,
2393 l_activity_code,
2394 l_sub_activity_code,
2395 l_taxpayer_id,
2396 -- l_effective_from,
2397 l_rep_context_loc_id,
2398 g_rep_context_address1,
2399 g_rep_context_address2,
2400 g_rep_context_address3,
2401 g_rep_context_city,
2402 g_rep_context_region_1,
2403 g_rep_context_region_2,
2404 g_rep_context_region_3,
2405 g_rep_context_postal_code,
2406 g_rep_context_country,
2407 -- g_rep_context_phone_number,
2408 l_style;
2409 -- l_legal_contact_party_name,
2410 -- l_legal_contact_party_num,
2411 -- l_legal_contact_title,
2412 -- l_legal_contact_job_title;
2413 CLOSE c_legal_info;
2414
2415 OPEN c_legal_contact_info(p_trl_global_variables_rec.legal_entity_id);
2416 FETCH c_legal_contact_info INTO
2417 l_legal_contact_party_name,
2418 l_legal_contact_party_num,
2419 l_legal_contact_title,
2420 l_legal_contact_job_title;
2421 CLOSE c_legal_contact_info;
2422
2423 IF (g_level_statement >= g_current_runtime_level ) THEN
2424 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2425 'l_rep_context_name : '|| l_rep_context_name);
2426 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2427 'l_activity_code : '|| l_activity_code);
2428 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2429 'l_sub_activity_code : '|| l_sub_activity_code);
2430 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2431 'l_taxpayer_id : '|| l_taxpayer_id);
2432 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2433 'l_rep_context_loc_id : '|| l_rep_context_loc_id);
2434 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2435 'g_rep_context_address1 : '|| g_rep_context_address1);
2436 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2437 'g_rep_context_city : '|| g_rep_context_city);
2438
2439 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2440 'l_style : '|| l_style);
2441 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2442 'l_legal_contact_party_name : '|| l_legal_contact_party_name);
2443 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2444 'l_legal_contact_title : '|| l_legal_contact_title);
2445 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2446 'l_legal_contact_party_num : '|| l_legal_contact_party_num);
2447
2448 END IF;
2449
2450 OPEN c_legal_auth_info (p_trl_global_variables_rec.legal_entity_id);
2451 FETCH c_legal_auth_info INTO
2452 l_legal_auth_address_line2,
2453 l_legal_auth_address_line3,
2454 l_legal_auth_city,
2455 l_legal_auth_name ;
2456 CLOSE c_legal_auth_info;
2457
2458 IF (g_level_statement >= g_current_runtime_level ) THEN
2459 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2460 'l_legal_auth_address_line2 : '|| l_legal_auth_address_line2);
2461 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2462 'l_legal_auth_address_line3 : '|| l_legal_auth_address_line3);
2463 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2464 'l_legal_auth_city : '|| l_legal_auth_city);
2465 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2466 'l_legal_auth_name : '|| l_legal_auth_name);
2467
2468 END IF;
2469
2470 /* apai
2471 ELSE
2472 OPEN c_loc_rec(p_rep_entity_id);
2473 FETCH c_loc_rec INTO
2474 g_rep_context_country,
2475 g_rep_context_city,
2476 g_rep_context_region_1,
2477 g_rep_context_region_2,
2478 g_rep_context_region_3,
2479 g_rep_context_address1,
2480 g_rep_context_address2,
2481 g_rep_context_address3,
2482 g_rep_context_postal_code,
2483 g_rep_context_phone_number,
2484 l_style,
2485 l_rep_context_loc_id;
2486 CLOSE c_loc_rec;
2487
2488 SELECT org_info.org_information2,
2489 org_unit.organization_id,
2490 org_unit.location_id,
2491 org_unit.name
2492 INTO l_rep_context_tax_reg_no,
2493 l_rep_context_org_id,
2494 l_rep_context_loc_id,
2495 l_rep_context_name
2496 FROM hr_organization_units org_unit,
2497 hr_organization_information org_info
2498 WHERE org_unit.organization_id = org_info.organization_id
2499 AND org_info.org_information1 = 'OPERATING_UNIT'
2500 AND org_unit.organization_id = p_rep_entity_id;
2501
2502 END IF;
2503 */
2504
2505 IF (g_level_procedure >= g_current_runtime_level ) THEN
2506 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2507 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf: After c_loc_rec cursor ');
2508 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2509 'p_rep_entity_id :'||to_char(p_rep_entity_id));
2510 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2511 'g_rep_context_city :'||g_rep_context_city);
2512 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2513 'g_rep_context_address1 :'||g_rep_context_address1);
2514 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2515 'l_rep_context_name :'||l_rep_context_name);
2516 END IF;
2517
2518 -- populate the legal entity address fields :
2519 -- by calling function get_location_column with l_style
2520
2521 l_rep_context_city := location_value(get_location_column(l_style,'CITY'));
2522 l_rep_context_county := location_value(get_location_column(l_style,'COUNTY'));
2523 l_rep_context_state := location_value(get_location_column(l_style,'STATE'));
2524 l_rep_context_province := location_value(get_location_column(l_style,'PROVINCE'));
2525 l_rep_context_country := location_value(get_location_column(l_style,'COUNTRY'));
2526 l_rep_context_address1 := location_value(get_location_column(l_style,'ADDRESS LINE 1'));
2527 l_rep_context_address2 := location_value(get_location_column(l_style,'ADDRESS LINE 2'));
2528 l_rep_context_address3 := location_value(get_location_column(l_style,'ADDRESS LINE 3'));
2529 l_rep_context_phone_number := location_value(get_location_column(l_style,'TELEPHONE'));
2530 l_rep_context_postal_code := location_value(get_location_column(l_style,'POSTAL CODE'));
2531 l_rep_entity_id := p_rep_entity_id;
2532
2533 --Bug 5439099 : Logic Implemented to populate the org_information2 with Main HQ EST REG NBR
2534
2535 begin
2536 --get ptp id for HQ of the LE
2537 SELECT ptp.party_id
2538 INTO l_hq_party_id
2539 FROM zx_party_tax_profile ptp,
2540 xle_etb_profiles xlep
2541 WHERE ptp.party_id = xlep.party_id
2542 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
2543 AND xlep.legal_entity_id = p_trl_global_variables_rec.legal_entity_id
2544 AND xlep.main_establishment_flag = 'Y';
2545
2546 l_org_information2 := ZX_TCM_EXT_SERVICES_PUB.Get_Default_Tax_Reg(
2547 l_hq_party_id ,
2548 'LEGAL_ESTABLISHMENT',
2549 SYSDATE,
2550 x_return_status);
2551 EXCEPTION WHEN
2552 OTHERS THEN
2553 l_org_information2 := null;
2554 END;
2555
2556
2557 BEGIN
2558 SELECT zx_rep_context_t_s.nextval
2559 INTO l_rep_context_id FROM dual;
2560
2561 IF (g_level_procedure >= g_current_runtime_level ) THEN
2562 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2563 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf: Insert statement begins ');
2564 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2565 'p_rep_entity_id :'||to_char(p_rep_entity_id));
2566 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2567 'rep_context_id :'||to_char(l_rep_context_id));
2568 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2569 'l_rep_context_city :'||l_rep_context_city);
2570 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2571 'l_rep_context_address1 :'||l_rep_context_address1);
2572 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2573 'HQ Tax Registration Nbr : l_org_information2 :'||l_org_information2);
2574 END IF;
2575
2576 Begin
2577 INSERT INTO zx_rep_context_t(
2578 rep_context_id,
2579 request_id,
2580 rep_entity_id,
2581 rep_context_entity_location_id,
2582 rep_context_entity_name,
2583 rep_context_entity_city,
2584 rep_context_entity_county,
2585 rep_context_entity_state,
2586 rep_context_entity_province,
2587 rep_context_entity_address1,
2588 rep_context_entity_address2,
2589 rep_context_entity_address3,
2590 rep_context_entity_country,
2591 rep_context_entity_postal_code,
2592 rep_context_entity_tel_number,
2593 rep_context_lvl_mng,
2594 rep_context_lvl_code,
2595 extract_summary_code,
2596 matrix_report_flag,
2597 legal_contact_pre_name_adjunct,
2598 legal_contact_party_name,
2599 taxpayer_id,
2600 legal_contact_title,
2601 activity_code,
2602 sub_activity_code,
2603 inception_date,
2604 legal_contact_party_num,
2605 legal_auth_address_line2,
2606 legal_auth_address_line3,
2607 legal_auth_city,
2608 legal_authority_name,
2609 org_information2,
2610 program_application_id,
2611 program_id,
2612 program_login_id,
2613 created_by,
2614 creation_date,
2615 last_updated_by,
2616 last_update_date,
2617 last_update_login)
2618 VALUES (
2619 l_rep_context_id,
2620 p_trl_global_variables_rec.request_id,
2621 l_rep_entity_id,
2622 l_rep_context_loc_id,
2623 l_rep_context_name,
2624 l_rep_context_city,
2625 l_rep_context_county,
2626 l_rep_context_state,
2627 l_rep_context_province,
2628 l_rep_context_address1,
2629 l_rep_context_address2,
2630 l_rep_context_address3,
2631 l_rep_context_country,
2632 l_rep_context_postal_code,
2633 l_rep_context_phone_number,
2634 l_rep_context_lvl_mng,
2635 --l_rep_context_lvl_code,
2636 p_trl_global_variables_rec.reporting_level,
2637 l_extract_summary_code,
2638 l_matrix_report_flag,
2639 l_legal_contact_job_title,
2640 l_legal_contact_party_name,
2641 l_taxpayer_id,
2642 l_legal_contact_title,
2643 l_activity_code,
2644 l_sub_activity_code,
2645 l_inception_date,
2646 l_legal_contact_party_num,
2647 l_legal_auth_address_line2,
2648 l_legal_auth_address_line3,
2649 l_legal_auth_city,
2650 l_legal_auth_name,
2651 l_org_information2,
2652 l_program_application_id,
2653 l_program_id,
2654 l_program_login_id,
2655 g_created_by,
2656 g_creation_date,
2657 g_last_updated_by,
2658 g_last_update_date,
2659 g_last_update_login);
2660
2661 l_rowcount := SQL%ROWCOUNT;
2662 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2663 FND_LOG.STRING(g_level_unexpected,
2664 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2665 'Rows Inserted in Rep context table :'||to_char(l_rowcount));
2666 END IF;
2667
2668 IF l_rowcount = 0 THEN
2669 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2670 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2671 FND_LOG.STRING(g_level_unexpected,
2672 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2673 'P_TRL_GLOBAL_VARIABLES_REC.RETCODE'||to_char(P_TRL_GLOBAL_VARIABLES_REC.RETCODE));
2674 END IF;
2675
2676 END IF;
2677 END;
2678
2679 EXCEPTION
2680 WHEN OTHERS THEN
2681 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2682 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2683 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_rep_context_itf- '|| g_error_buffer);
2684 FND_MSG_PUB.Add;
2685 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2686 FND_LOG.STRING(g_level_unexpected,
2687 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2688 g_error_buffer);
2689 END IF;
2690 APPEND_ERRBUF(g_error_buffer);
2691 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2692
2693 END;
2694
2695 IF (g_level_procedure >= g_current_runtime_level ) THEN
2696 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf.END',
2697 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf(-)');
2698 END IF;
2699
2700 END insert_rep_context_itf ;
2701
2702
2703 /*===========================================================================+
2704 | function |
2705 | location_value |
2706 | |
2707 | description |
2708 | this function RETURNs the value stored in a particular column in |
2709 | the memory structure pr_org_loc_rec |
2710 | |
2711 | scope - private |
2712 +===========================================================================*/
2713
2714 FUNCTION location_value(
2715 p_column in VARCHAR2)
2716 RETURN VARCHAR2 IS
2717
2718 l_column_value VARCHAR2(240);
2719 l_column VARCHAR2(240);
2720
2721 BEGIN
2722
2723 IF (g_level_procedure >= g_current_runtime_level ) THEN
2724 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.location_value.BEGIN',
2725 'ZX.TRL.ZX_EXTRACT_PKG.location_value(+)');
2726 END IF;
2727
2728 l_column := p_column;
2729 IF l_column = 'TOWN_OR_CITY' THEN
2730 l_column_value := g_rep_context_city;
2731 ELSIF l_column = 'COUNTRY' THEN
2732 l_column_value := g_rep_context_country;
2733 ELSIF l_column = 'REGION_1' THEN
2734 l_column_value := g_rep_context_region_1;
2735 ELSIF l_column = 'REGION_2' THEN
2736 l_column_value := g_rep_context_region_2;
2737 ELSIF l_column = 'REGION_3' THEN
2738 l_column_value := g_rep_context_region_3;
2739 ELSIF l_column = 'ADDRESS_LINE_1' THEN
2740 l_column_value := g_rep_context_address1;
2741 ELSIF l_column = 'ADDRESS_LINE_2' THEN
2742 l_column_value := g_rep_context_address2;
2743 ELSIF l_column = 'ADDRESS_LINE_3' THEN
2744 l_column_value := g_rep_context_address3;
2745 ELSIF l_column = 'POSTAL_CODE' THEN
2746 l_column_value := g_rep_context_postal_code;
2747 ELSIF l_column = 'TELEPHONE_NUMBER_1' THEN
2748 l_column_value := g_rep_context_phone_number;
2749 ELSIF l_column = 'TELEPHONE_NUMBER_2' THEN
2750 l_column_value := g_rep_context_phone_number;
2751 END IF;
2752
2753 IF (g_level_procedure >= g_current_runtime_level ) THEN
2754 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.location_value.END',
2755 'ZX.TRL.ZX_EXTRACT_PKG.location_value(-)'||l_column_value);
2756 END IF;
2757
2758 RETURN l_column_value;
2759
2760 EXCEPTION
2761 WHEN OTHERS THEN
2762 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2763 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2764 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','location_value- '|| g_error_buffer);
2765 FND_MSG_PUB.Add;
2766 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2767 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_EXTRACT_PKG.location_value',
2768 g_error_buffer);
2769 END IF;
2770 APPEND_ERRBUF(g_error_buffer);
2771 --P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2772
2773 END location_value;
2774
2775 /*===========================================================================+
2776 | FUNCTION |
2777 | GET_LOCATION_COLUMN |
2778 | |
2779 | DESCRIPTION |
2780 | This FUNCTION returns the column in table HR_LOCATIONS_V which |
2781 | stores the address information for a particular address style |
2782 | |
2783 | For example, if the location style is US then the column REGION1 |
2784 | is used to store COUNTY information, but if the style is CN the |
2785 | same column may store PROVINCE information. |
2786 | |
2787 | Called from ZX_EXTRACT_PKG.POPULATE_TAX_DATA() |
2788 | |
2789 | SCOPE - Private |
2790 | |
2791 | NOTES |
2792 | |
2793 | MODIFICATION HISTORY |
2794 | |
2795 +===========================================================================*/
2796
2797 FUNCTION get_location_column(p_style IN VARCHAR2,
2798 p_classification IN VARCHAR2)
2799 return VARCHAR2 is
2800
2801 ----------------------------
2802 -- Private Variables
2803 ----------------------------
2804 pr_segments FND_DFLEX.SEGMENTS_DR;
2805 pr_contexts FND_DFLEX.CONTEXTS_DR;
2806 i BINARY_INTEGER;
2807 l_style HR_LOCATIONS_ALL.STYLE%type;
2808 l_context NUMBER;
2809 l_column VARCHAR2(150);
2810
2811 BEGIN
2812
2813 l_style := p_style;
2814
2815 -- Get the context information from 'Address Location' Descriptive Flexfield
2816 -- Select the context value which matches p_org_loc_rec.style
2817 IF (g_level_procedure >= g_current_runtime_level ) THEN
2818 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column.BEGIN',
2819 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column(+)');
2820 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
2821 'P_CLASSIFICATION = '||p_classification);
2822 END IF;
2823
2824 fnd_dflex.get_contexts(pr_flexfield, pr_contexts);
2825 l_context := NULL;
2826
2827 FOR i IN 1 .. pr_contexts.ncontexts LOOP
2828 IF(pr_contexts.is_enabled(i)) THEN
2829 IF pr_contexts.context_code(i) = l_style then
2830 IF (g_level_procedure >= g_current_runtime_level ) THEN
2831 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
2832 'pr_contexts.context_code= '||pr_contexts.context_code(i)||'-'||
2833 pr_contexts.context_description(i));
2834 END IF;
2835 l_context := i;
2836 END IF;
2837 END IF;
2838 END LOOP;
2839
2840 IF l_context is NULL then
2841 return NULL;
2842 END IF;
2843
2844 -- Select the segments which correspond to the selected context.
2845 fnd_dflex.get_segments(fnd_dflex.make_context(pr_flexfield,
2846 pr_contexts.context_code(l_context)),
2847 pr_segments,
2848 TRUE);
2849
2850 -- Check if the segment name matches with the value of input parameter p_classification,
2851 -- Otherwise write an error message and return null
2852
2853 FOR i IN 1 .. pr_segments.nsegments LOOP
2854 IF upper(pr_segments.segment_name(i)) = upper(p_classification) then
2855 l_column := pr_segments.application_column_name(i);
2856 END IF;
2857 END LOOP;
2858
2859 IF l_column is NULL then
2860 IF (g_level_procedure >= g_current_runtime_level ) THEN
2861 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
2862 'No column which matches the value of p_classification: '||p_classification);
2863 END IF;
2864 return NULL;
2865 END IF;
2866
2867 IF (g_level_procedure >= g_current_runtime_level ) THEN
2868 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column.END',
2869 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column(-)');
2870 END IF;
2871
2872 RETURN l_column;
2873
2874 EXCEPTION
2875 WHEN OTHERS THEN
2876 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2877 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2878 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_location_column- '|| g_error_buffer);
2879 FND_MSG_PUB.Add;
2880 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2881 FND_LOG.STRING(g_level_unexpected,
2882 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
2883 g_error_buffer);
2884 END IF;
2885 APPEND_ERRBUF(g_error_buffer);
2886 --P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2887 RETURN NULL;
2888
2889
2890 END get_location_column;
2891
2892
2893 /*===========================================================================+
2894 | PROCEDURE |
2895 | STACK_ERROR |
2896 | |
2897 | DESCRIPTION |
2898 | This procedure takes the token(s), Value(s) and puts on the message |
2899 | stack |
2900 | |
2901 | SCOPE - Private |
2902 | |
2903 | NOTES |
2904 | |
2905 | MODIFICATION HISTORY |
2906 | 13-July-99 Nilesh Patel Created |
2907 | |
2908 +===========================================================================*/
2909
2910 PROCEDURE stack_error (
2911 p_application VARCHAR2,
2912 p_msgname VARCHAR2,
2913 p_token1 VARCHAR2 DEFAULT NULL,
2914 p_value1 VARCHAR2 DEFAULT NULL,
2915 p_token2 VARCHAR2 DEFAULT NULL,
2916 p_value2 VARCHAR2 DEFAULT NULL,
2917 p_token3 VARCHAR2 DEFAULT NULL,
2918 p_value3 VARCHAR2 DEFAULT NULL ) IS
2919 BEGIN
2920
2921 fnd_message.set_name(nvl(p_application,'AR'),nvl(p_msgname,'GENERIC_MESSAGE'));
2922
2923 IF ( p_token1 IS NOT NULL ) THEN
2924 fnd_message.set_token(p_token1, p_value1);
2925 END IF;
2926
2927 IF ( p_token2 IS NOT NULL ) THEN
2928 fnd_message.set_token(p_token2, p_value2);
2929 END IF;
2930
2931
2932 IF ( p_token3 IS NOT NULL ) THEN
2933 fnd_message.set_token(p_token3, p_value3);
2934 END IF;
2935
2936 END stack_error;
2937
2938
2939 /*===========================================================================+
2940 | PROCEDURE |
2941 | SET_RETCODE |
2942 | |
2943 | DESCRIPTION |
2944 | This procedure sets the value of P_RETCODE |
2945 | |
2946 | SCOPE - Public |
2947 | |
2948 | NOTES |
2949 | |
2950 | MODIFICATION HISTORY |
2951 | 7-Oct-1999 Nilesh Patel Created |
2952 | |
2953 +===========================================================================*/
2954
2955 PROCEDURE set_retcode(p_retcode in number) is
2956 BEGIN
2957 If p_retcode = 2 then
2958 L_RETCODE := p_retcode;
2959 elsif p_retcode = 1 then
2960 IF L_RETCODE = 2 then
2961 NULL;
2962 ELSE
2963 L_RETCODE := p_retcode;
2964 END IF;
2965 end if;
2966 END set_retcode;
2967
2968 /*===========================================================================+
2969 | PROCEDURE |
2970 | APPEND_ERRBUF |
2971 | |
2972 | DESCRIPTION |
2973 | This procedure appends the input parameter p_msg to the global |
2974 | variable L_ERRBUF which will be returned to the calling concurrent |
2975 | program. |
2976 | |
2977 | SCOPE - Public |
2978 | |
2979 | NOTES |
2980 | |
2981 | MODIFICATION HISTORY |
2982 | 7-Oct-1999 Nilesh Patel Created |
2983 | |
2984 +===========================================================================*/
2985
2986 PROCEDURE append_errbuf(p_msg in varchar2) is
2987 BEGIN
2988 if nvl(lengthb(L_ERRBUF),0) = 0 THEN
2989 L_ERRBUF := p_msg;
2990 elsif nvl(lengthb(L_ERRBUF),0) < 2000 - nvl(lengthb(p_msg),0) then
2991 L_ERRBUF := L_ERRBUF ||';'||p_msg;
2992 end if;
2993
2994 L_ERRBUF := L_ERRBUF || fnd_global.newline;
2995
2996 END append_errbuf;
2997
2998 PROCEDURE PURGE(p_request_id in number,
2999 p_rows_deleted out NOCOPY number) is
3000 BEGIN
3001
3002 purge(p_request_id);
3003 p_rows_deleted:= purge(p_request_id);
3004
3005
3006 END PURGE;
3007
3008
3009 /*===========================================================================+
3010 | PROCEDURE |
3011 | PURGE |
3012 | |
3013 | DESCRIPTION |
3014 | This procedure deletes the records from AR_TAX_EXTRACT_DCL_ITF |
3015 | and AR_TAX_EXTRACT_SUB_ITF for a given request_id |
3016 | |
3017 | SCOPE - Public |
3018 | |
3019 | NOTES |
3020 | |
3021 | MODIFICATION HISTORY |
3022 | 14-Dec-1999 Nilesh Patel Created |
3023 | |
3024 +===========================================================================*/
3025
3026 PROCEDURE PURGE(p_request_id in number) is
3027 BEGIN
3028
3029 IF PG_DEBUG = 'N' THEN
3030 delete from ZX_REP_ACTG_EXT_T where request_id = p_request_id;
3031 delete from ZX_REP_TRX_JX_EXT_T where request_id = p_request_id;
3032 delete from ZX_REP_TRX_DETAIL_T where request_id = p_request_id;
3033 delete from ZX_REP_CONTEXT_T where request_id = p_request_id;
3034 END IF;
3035
3036 /*
3037 delete from ar_tax_extr_sub_com_ext com_ext where com_ext.extract_line_id
3038 in (select sub_itf.extract_line_id
3039 from ar_tax_Extract_sub_itf sub_itf
3040 where request_id = p_request_id);
3041 delete from ar_tax_extr_sub_ar_ext ar_ext where ar_ext.extract_line_id
3042 in (select sub_itf.extract_line_id
3043 from ar_tax_Extract_sub_itf sub_itf
3044 where request_id = p_request_id);
3045 delete from ar_tax_extr_sub_ap_ext ap_ext where ap_ext.extract_line_id
3046 in (select sub_itf.extract_line_id
3047 from ar_tax_Extract_sub_itf sub_itf
3048 where request_id = p_request_id);
3049 delete from ar_tax_extract_sub_itf where request_id = p_request_id;
3050 delete from ar_tax_Extract_dcl_itf where request_id = p_request_id;
3051 */
3052
3053
3054 END PURGE;
3055
3056
3057 /*===========================================================================+
3058 | FUNCTION |
3059 | PURGE |
3060 | |
3061 | DESCRIPTION |
3062 | This function deletes the records from AR_TAX_EXTRACT_DCL_ITF, |
3063 | AR_TAX_EXTRACT_SUB_ITF for a given request_id |
3064 | |
3065 | SCOPE - Public |
3066 | |
3067 | NOTES |
3068 | |
3069 | MODIFICATION HISTORY |
3070 | 24-May-2000 Nilesh Patel Created |
3071 | |
3072 +===========================================================================*/
3073
3074 FUNCTION PURGE(p_request_id in number) return number is
3075 num_rows_deleted number := 0;
3076 BEGIN
3077
3078 --pg_debug_flag:= nvl(FND_PROFILE.value('TAX_DEBUG_FLAG'),'N');
3079
3080
3081 select count(*) into num_rows_deleted
3082 from zx_rep_trx_detail_t
3083 where request_id = p_request_id;
3084
3085 PURGE(p_request_id);
3086
3087 return(num_rows_deleted);
3088 END;
3089
3090
3091 /*===========================================================================+
3092 | FUNCTION |
3093 | CONVERT_STRING |
3094 | |
3095 | DESCRIPTION |
3096 | This procedure takes the input string, replaces all inverted commas |
3097 | i.e. ' to two inverted commas i.e. '' and returns the converted |
3098 | string |
3099 | |
3100 | SCOPE - Private |
3101 | |
3102 | NOTES |
3103 | |
3104 | MODIFICATION HISTORY |
3105 | 21-July-99 Nilesh Patel Created |
3106 | |
3107 +===========================================================================*/
3108
3109 function convert_string(p_string in varchar2)
3110 return varchar2 is
3111
3112 l_string varchar2(255);
3113 BEGIN
3114 If p_string is not null then
3115 l_string := replace(p_string,'''','''''');
3116 end if;
3117 return l_string;
3118 END;
3119
3120 /*===========================================================================+
3121 | PROCEDURE |
3122 | build_matrx_tbl |
3123 | |
3124 | DESCRIPTION |
3125 | This proceures takes a sql statement (varchar2) as input parameter, |
3126 | executes the sql statement, and builds a PLSQL table of records. |
3127 | |
3128 | SCOPE - Public |
3129 | |
3130 | NOTES |
3131 | |
3132 | MODIFICATION HISTORY |
3133 | 7-Sept-99 Nilesh Patel Created |
3134 | |
3135 +===========================================================================*/
3136
3137 /*
3138 procedure build_matrix_tbl( p_sql in varchar2) is
3139 type csr_type is ref cursor;
3140 csr csr_type;
3141 l_index number := 0;
3142 begin
3143 pg_sql := p_sql;
3144 matrix_tbl.delete;
3145 open csr for pg_sql;
3146 loop
3147 fetch csr into matrix_rec;
3148 exit when csr%notfound;
3149 l_index := l_index + 1;
3150 matrix_tbl(l_index) := matrix_rec;
3151 end loop;
3152 close csr;
3153 end if;
3154 exception
3155 when others then
3156 matrix_tbl.delete;
3157 close csr;
3158 NULL;
3159 end;
3160
3161 */
3162
3163 /*===========================================================================+
3164 | FUNCTION |
3165 | get_rep_context_id |
3166 | |
3167 | DESCRIPTION |
3168 | This function returns the rep_context_id for a given organization_id |
3169 | AR/AP and GL APIs calls this function |
3170 | SCOPE - Public |
3171 | |
3172 | NOTES |
3173 | |
3174 | MODIFICATION HISTORY |
3175 | |
3176 +===========================================================================*/
3177
3178 /* apai this fuction is modified as per legal entity changes
3179 function get_rep_context_id( p_org_id in number,
3180 p_legal in varchar2,
3181 p_legal_id in number,
3182 p_request_id in number)
3183 */
3184 function get_rep_context_id( p_legal_id in number,
3185 p_request_id in number)
3186 return number is
3187 l_rep_context_id number;
3188
3189
3190 CURSOR legal_rep_context_cur (
3191 c_request_id IN NUMBER)
3192 IS
3193 SELECT rep_context.rep_context_id
3194 FROM zx_rep_context_t rep_context
3195 WHERE request_id = c_request_id;
3196 --AND rep_context.rep_entity_id = c_legal_entity_id;
3197
3198 BEGIN
3199
3200 IF (g_level_procedure >= g_current_runtime_level ) THEN
3201 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_rep_context_id(+)',
3202 'Legal Entity ID :'||to_char(p_legal_id));
3203 END IF;
3204
3205 OPEN legal_rep_context_cur (p_request_id);
3206 --p_legal_id);
3207 FETCH legal_rep_context_cur into l_rep_context_id;
3208
3209 IF (g_level_procedure >= g_current_runtime_level ) THEN
3210 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_rep_context_id(-)',
3211 'l_rep_context_id :'||to_char(l_rep_context_id));
3212 END IF;
3213
3214 IF legal_rep_context_cur%ISOPEN then
3215 CLOSE legal_rep_context_cur;
3216 END IF;
3217
3218 RETURN l_rep_context_id;
3219
3220 END get_rep_context_id;
3221
3222
3223 /*===========================================================================+
3224 | FUNCTION |
3225 | use_matrix_flag |
3226 | |
3227 | DESCRIPTION |
3228 | This is a view functionwhich is called from concurrent program |
3229 | financial tax register to check whether matrix report flag is |
3230 | required or not |
3231 | SCOPE - Public |
3232 | |
3233 | NOTES |
3234 | |
3235 | MODIFICATION HISTORY |
3236 | 5-Jan-2000 Nilesh Patel Created |
3237 | |
3238 +===========================================================================*/
3239 function use_matrix_flag( p_report_id in varchar2,
3240 p_attribute_set in varchar2,
3241 p_product in varchar2 default NULL)
3242 return varchar2 is
3243 l_use_matrix_rep varchar2(1);
3244 begin
3245 /*cursor use_matrix_flag_csr_ar
3246 (c_report_id in number,
3247 c_attribute_set in varchar2 ) is
3248 select 'Y' from dual where exists
3249 (select '1'
3250 from FA_RX_REPORTS_V RV,
3251 FA_RX_ATTRSETS ATT,
3252 FA_RX_REP_COLUMNS COL
3253 where RV.REPORT_ID = C_REPORT_ID
3254 AND ATT.REPORT_ID = RV.REPORT_ID
3255 AND ATT.ATTRIBUTE_SET = C_ATTRIBUTE_SET
3256 AND ATT.ATTRIBUTE_SET = COL.ATTRIBUTE_SET
3257 AND COL.DISPLAY_STATUS = 'YES'
3258 AND COL.COLUMN_NAME IN (
3259 'TAX1_ACCOUNTED_AMOUNT',
3260 'TAX1_ACCOUNTED_CR',
3261 'TAX1_ACCOUNTED_DR',
3262 'TAX1_CODE',
3263 'TAX1_CODE_DESCRIPTION',
3264 'TAX1_CODE_NAME',
3265 'TAX1_CODE_RATE',
3266 'TAX1_CODE_REG_TYPE_CODE',
3267 'TAX1_CODE_REG_TYPE_MEANING',
3268 'TAX1_CODE_TAX_CLASS_CODE',
3269 'TAX1_CODE_TAX_CLASS_MEANING',
3270 'TAX1_CODE_TYPE_CODE',
3271 'TAX1_CODE_TYPE_MEANING',
3272 'TAX1_CODE_VAT_TRX_TYPE_CODE',
3273 'TAX1_CODE_VAT_TRX_TYPE_DESC',
3274 'TAX1_CODE_VAT_TRX_TYPE_MEANING',
3275 'TAX1_ENTERED_AMOUNT',
3276 'TAX1_ENTERED_CR',
3277 'TAX1_ENTERED_DR',
3278 'TAX1_LINE_EFFECTIVE_TAX_RATE',
3279 'TAX1_LINE_NUMBER',
3280 'TAX1_RECOVERABLE_FLAG',
3281 'TAXABLE1_ACCOUNTED_AMOUNT',
3282 'TAXABLE1_ACCOUNTED_CR',
3283 'TAXABLE1_ACCOUNTED_DR',
3284 'TAXABLE1_ENTERED_AMOUNT',
3285 'TAXABLE1_ENTERED_CR',
3286 'TAXABLE1_ENTERED_DR',
3287 'TAX2_ACCOUNTED_AMOUNT',
3288 'TAX2_ACCOUNTED_CR',
3289 'TAX2_ACCOUNTED_DR',
3290 'TAX2_CODE',
3291 'TAX2_CODE_DESCRIPTION',
3292 'TAX2_CODE_NAME',
3293 'TAX2_CODE_RATE',
3294 'TAX2_CODE_REG_TYPE_CODE',
3295 'TAX2_CODE_REG_TYPE_MEANING',
3296 'TAX2_CODE_TAX_CLASS_CODE',
3297 'TAX2_CODE_TAX_CLASS_MEANING',
3298 'TAX2_CODE_TYPE_CODE',
3299 'TAX2_CODE_TYPE_MEANING',
3300 'TAX2_CODE_VAT_TRX_TYPE_CODE',
3301 'TAX2_CODE_VAT_TRX_TYPE_DESC',
3302 'TAX2_CODE_VAT_TRX_TYPE_MEANING',
3303 'TAX2_ENTERED_AMOUNT',
3304 'TAX2_ENTERED_CR',
3305 'TAX2_ENTERED_DR',
3306 'TAX2_LINE_EFFECTIVE_TAX_RATE',
3307 'TAX2_LINE_NUMBER',
3308 'TAX2_RECOVERABLE_FLAG',
3309 'TAXABLE2_ACCOUNTED_AMOUNT',
3310 'TAXABLE2_ACCOUNTED_CR',
3311 'TAXABLE2_ACCOUNTED_DR',
3312 'TAXABLE2_ENTERED_AMOUNT',
3313 'TAXABLE2_ENTERED_CR',
3314 'TAXABLE2_ENTERED_DR',
3315 'TAX3_ACCOUNTED_AMOUNT',
3316 'TAX3_ACCOUNTED_CR',
3317 'TAX3_ACCOUNTED_DR',
3318 'TAX3_CODE',
3319 'TAX3_CODE_DESCRIPTION',
3320 'TAX3_CODE_NAME',
3321 'TAX3_CODE_RATE',
3322 'TAX3_CODE_REG_TYPE_CODE',
3323 'TAX3_CODE_REG_TYPE_MEANING',
3324 'TAX3_CODE_TAX_CLASS_CODE',
3325 'TAX3_CODE_TAX_CLASS_MEANING',
3326 'TAX3_CODE_TYPE_CODE',
3327 'TAX3_CODE_TYPE_MEANING',
3328 'TAX3_CODE_VAT_TRX_TYPE_CODE',
3329 'TAX3_CODE_VAT_TRX_TYPE_DESC',
3330 'TAX3_CODE_VAT_TRX_TYPE_MEANING',
3331 'TAX3_ENTERED_AMOUNT',
3332 'TAX3_ENTERED_CR',
3333 'TAX3_ENTERED_DR',
3334 'TAX3_LINE_EFFECTIVE_TAX_RATE',
3335 'TAX3_LINE_NUMBER',
3336 'TAX3_RECOVERABLE_FLAG',
3337 'TAXABLE3_ACCOUNTED_AMOUNT',
3338 'TAXABLE3_ACCOUNTED_CR',
3339 'TAXABLE3_ACCOUNTED_DR',
3340 'TAXABLE3_ENTERED_AMOUNT',
3341 'TAXABLE3_ENTERED_CR',
3342 'TAXABLE3_ENTERED_DR',
3343 'TAX4_ACCOUNTED_AMOUNT',
3344 'TAX4_ACCOUNTED_CR',
3345 'TAX4_ACCOUNTED_DR',
3346 'TAX4_CODE',
3347 'TAX4_CODE_DESCRIPTION',
3348 'TAX4_CODE_NAME',
3349 'TAX4_CODE_RATE',
3350 'TAX4_CODE_REG_TYPE_CODE',
3351 'TAX4_CODE_REG_TYPE_MEANING',
3352 'TAX4_CODE_TAX_CLASS_CODE',
3353 'TAX4_CODE_TAX_CLASS_MEANING',
3354 'TAX4_CODE_TYPE_CODE',
3355 'TAX4_CODE_TYPE_MEANING',
3356 'TAX4_CODE_VAT_TRX_TYPE_CODE',
3357 'TAX4_CODE_VAT_TRX_TYPE_DESC',
3358 'TAX4_CODE_VAT_TRX_TYPE_MEANING',
3359 'TAX4_ENTERED_AMOUNT',
3360 'TAX4_ENTERED_CR',
3361 'TAX4_ENTERED_DR',
3362 'TAX4_LINE_EFFECTIVE_TAX_RATE',
3363 'TAX4_LINE_NUMBER',
3364 'TAX4_RECOVERABLE_FLAG',
3365 'TAXABLE4_ACCOUNTED_AMOUNT',
3366 'TAXABLE4_ACCOUNTED_CR',
3367 'TAXABLE4_ACCOUNTED_DR',
3368 'TAXABLE4_ENTERED_AMOUNT',
3369 'TAXABLE4_ENTERED_CR',
3370 'TAXABLE4_ENTERED_DR') );
3371
3372 cursor use_matrix_flag_csr_ap
3373 (c_report_id in number,
3374 c_attribute_set in varchar2 ) is
3375 select 'Y' from dual where exists
3376 (select '1'
3377 from FA_RX_REPORTS_V RV,
3378 FA_RX_ATTRSETS ATT,
3379 FA_RX_REP_COLUMNS COL
3380 where RV.REPORT_ID = C_REPORT_ID
3381 AND ATT.REPORT_ID = RV.REPORT_ID
3382 AND ATT.ATTRIBUTE_SET = C_ATTRIBUTE_SET
3383 AND ATT.ATTRIBUTE_SET = COL.ATTRIBUTE_SET
3384 AND COL.DISPLAY_STATUS = 'YES'
3385 AND COL.COLUMN_NAME IN (
3386 'TAX3_ACCOUNTED_AMOUNT',
3387 'TAX3_ACCOUNTED_CR',
3388 'TAX3_ACCOUNTED_DR',
3389 'TAX3_CODE',
3390 'TAX3_CODE_DESCRIPTION',
3391 'TAX3_CODE_NAME',
3392 'TAX3_CODE_RATE',
3393 'TAX3_CODE_REG_TYPE_CODE',
3394 'TAX3_CODE_REG_TYPE_MEANING',
3395 'TAX3_CODE_TAX_CLASS_CODE',
3396 'TAX3_CODE_TAX_CLASS_MEANING',
3397 'TAX3_CODE_TYPE_CODE',
3398 'TAX3_CODE_TYPE_MEANING',
3399 'TAX3_CODE_VAT_TRX_TYPE_CODE',
3400 'TAX3_CODE_VAT_TRX_TYPE_DESC',
3401 'TAX3_CODE_VAT_TRX_TYPE_MEANING',
3402 'TAX3_ENTERED_AMOUNT',
3403 'TAX3_ENTERED_CR',
3404 'TAX3_ENTERED_DR',
3405 'TAX3_LINE_EFFECTIVE_TAX_RATE',
3406 'TAX3_LINE_NUMBER',
3407 'TAX3_RECOVERABLE_FLAG',
3408 'TAXABLE3_ACCOUNTED_AMOUNT',
3409 'TAXABLE3_ACCOUNTED_CR',
3410 'TAXABLE3_ACCOUNTED_DR',
3411 'TAXABLE3_ENTERED_AMOUNT',
3412 'TAXABLE3_ENTERED_CR',
3413 'TAXABLE3_ENTERED_DR',
3414 'TAX4_ACCOUNTED_AMOUNT',
3415 'TAX4_ACCOUNTED_CR',
3416 'TAX4_ACCOUNTED_DR',
3417 'TAX4_CODE',
3418 'TAX4_CODE_DESCRIPTION',
3419 'TAX4_CODE_NAME',
3420 'TAX4_CODE_RATE',
3421 'TAX4_CODE_REG_TYPE_CODE',
3422 'TAX4_CODE_REG_TYPE_MEANING',
3423 'TAX4_CODE_TAX_CLASS_CODE',
3424 'TAX4_CODE_TAX_CLASS_MEANING',
3425 'TAX4_CODE_TYPE_CODE',
3426 'TAX4_CODE_TYPE_MEANING',
3427 'TAX4_CODE_VAT_TRX_TYPE_CODE',
3428 'TAX4_CODE_VAT_TRX_TYPE_DESC',
3429 'TAX4_CODE_VAT_TRX_TYPE_MEANING',
3430 'TAX4_ENTERED_AMOUNT',
3431 'TAX4_ENTERED_CR',
3432 'TAX4_ENTERED_DR',
3433 'TAX4_LINE_EFFECTIVE_TAX_RATE',
3434 'TAX4_LINE_NUMBER',
3435 'TAX4_RECOVERABLE_FLAG',
3436 'TAXABLE4_ACCOUNTED_AMOUNT',
3437 'TAXABLE4_ACCOUNTED_CR',
3438 'TAXABLE4_ACCOUNTED_DR',
3439 'TAXABLE4_ENTERED_AMOUNT',
3440 'TAXABLE4_ENTERED_CR',
3441 'TAXABLE4_ENTERED_DR') );
3442
3443 Begin
3444 -- arp_util_tax.debug('ZX_EXTRACT_PKG.USE_MATRIX_REPORT: Product = '||
3445 -- P_PRODUCT );
3446
3447 l_use_matrix_rep := 'N';
3448
3449 if P_PRODUCT = 'AP' then
3450 open use_matrix_flag_csr_ap (p_report_id,p_attribute_set);
3451 fetch use_matrix_flag_csr_ap into l_use_matrix_rep;
3452 if use_matrix_flag_csr_ap%isopen then
3453 close use_matrix_flag_csr_ap;
3454 end if;
3455 else
3456 open use_matrix_flag_csr_ar (p_report_id,p_attribute_set);
3457 fetch use_matrix_flag_csr_ar into l_use_matrix_rep;
3458 if use_matrix_flag_csr_ar%isopen then
3459 close use_matrix_flag_csr_ar;
3460 end if;
3461 end if;
3462 return l_use_matrix_rep;
3463 exception
3464 when no_data_found then
3465 IF PG_DEBUG = 'Y' THEN
3466 arp_util_tax.debug('ZX_EXTRACT_PKG.UE_MATRIX_REP : NO_DATA_FOUND ');
3467 END IF;
3468 if use_matrix_flag_csr_ap%isopen then
3469 close use_matrix_flag_csr_ap;
3470 end if;
3471 if use_matrix_flag_csr_ar%isopen then
3472 close use_matrix_flag_csr_ar;
3473 end if;
3474 return ('N');
3475 when others then
3476 IF PG_DEBUG = 'Y' THEN
3477 arp_util_tax.debug('ZX_EXTRACT_PKG.UE_MATRIX_REP: '||SQLCODE
3478 ||' ; '||SQLERRM);
3479 END IF;
3480 if use_matrix_flag_csr_ap%isopen then
3481 close use_matrix_flag_csr_ap;
3482 end if;
3483 if use_matrix_flag_csr_ar%isopen then
3484 close use_matrix_flag_csr_ar;
3485 end if; */
3486 return('N');
3487 end;
3488
3489 /*===========================================================================+
3490 | PROCEDURE |
3491 | zx_upd_legal_reporting_status() |
3492 | |
3493 | DESCRIPTION |
3494 | This procedure is used to update the legal_reporting_status value |
3495 | on the zx_lines with the value passed as input to this procedure |
3496 | |
3497 | SCOPE - Public |
3498 | |
3499 | NOTES |
3500 | |
3501 | MODIFICATION HISTORY |
3502 | 24-Mar-2006 Ashwin Gurram Created |
3503 | |
3504 +===========================================================================*/
3505
3506 PROCEDURE ZX_UPD_LEGAL_REPORTING_STATUS(
3507 p_api_version IN NUMBER,
3508 p_init_msg_list IN VARCHAR2,
3509 p_commit IN VARCHAR2,
3510 p_validation_level IN VARCHAR2,
3511 p_application_id_tbl IN application_id_tbl,
3512 p_entity_code_tbl IN entity_code_tbl,
3513 p_event_class_code_tbl IN event_class_code_tbl,
3514 p_trx_id_tbl IN trx_id_tbl,
3515 p_trx_line_id_tbl IN trx_line_id_tbl,
3516 p_INTERNAL_ORGANIZATION_ID_tbl IN INTERNAL_ORGANIZATION_ID_TBL,
3517 p_TAX_LINE_ID_tbl IN TAX_LINE_ID_TBL,
3518 p_legal_reporting_status_val IN zx_lines.LEGAL_REPORTING_STATUS%type,
3519 x_return_status OUT NOCOPY VARCHAR2,
3520 x_msg_count OUT NOCOPY NUMBER,
3521 x_msg_data OUT NOCOPY VARCHAR2
3522 ) IS
3523
3524 l_count NUMBER := 0;
3525 l_counter_start NUMBER := 1 ;
3526 l_counter_end NUMBER := 0 ;
3527
3528 BEGIN
3529 x_return_status := FND_API.G_RET_STS_SUCCESS;
3530 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3531 IF (g_level_procedure >= g_current_runtime_level ) THEN
3532 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.zx_upd_legal_reporting_status.BEGIN',
3533 'ZX_EXTRACT_PKG:zx_upd_legal_reporting_status(+)');
3534 END IF;
3535
3536 l_count := p_application_id_tbl.COUNT ;
3537
3538 IF ( l_count > C_LINES_PER_INSERT ) THEN
3539 l_counter_end := C_LINES_PER_INSERT;
3540 ELSE
3541 l_counter_end := l_count ;
3542 END IF ;
3543
3544 LOOP
3545 IF ( l_counter_end <= l_count AND l_counter_start <= l_count ) THEN
3546
3547 FORALL i IN l_counter_start .. l_counter_end
3548 UPDATE ZX_LINES
3549 SET LEGAL_REPORTING_STATUS = p_legal_reporting_status_val,
3550 LAST_UPDATED_BY = fnd_global.user_id ,
3551 LAST_UPDATE_DATE = SYSDATE ,
3552 LAST_UPDATE_LOGIN = fnd_global.conc_login_id ,
3553 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
3554 WHERE application_id = p_application_id_tbl(i)
3555 AND entity_code = p_entity_code_tbl(i)
3556 AND event_class_code = p_event_class_code_tbl(i)
3557 AND trx_id = p_trx_id_tbl(i)
3558 AND trx_line_id = p_trx_line_id_tbl(i)
3559 AND INTERNAL_ORGANIZATION_ID = p_INTERNAL_ORGANIZATION_ID_tbl(i)
3560 AND TAX_LINE_ID = p_TAX_LINE_ID_tbl(i) ;
3561
3562 l_counter_start := l_counter_end + 1;
3563 IF ( l_counter_end + C_LINES_PER_INSERT < l_count ) THEN
3564 l_counter_end := l_counter_end + C_LINES_PER_INSERT;
3565 ELSE
3566 l_counter_end := l_count ;
3567 END IF ;
3568 ELSE
3569 EXIT ;
3570 END IF ;
3571
3572 END LOOP ;
3573
3574 IF (g_level_procedure >= g_current_runtime_level ) THEN
3575 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.zx_upd_legal_reporting_status.END',
3576 'ZX_EXTRACT_PKG:zx_upd_legal_reporting_status(-)');
3577 END IF;
3578
3579 EXCEPTION
3580 WHEN OTHERS THEN
3581 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3582 FND_LOG.STRING(g_level_unexpected,
3583 'ZX.TRL.ZX_EXTRACT_PKG.zx_upd_legal_reporting_status',
3584 sqlerrm);
3585 END IF;
3586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3587 app_exception.raise_exception;
3588 END ZX_UPD_LEGAL_REPORTING_STATUS;
3589
3590 /*=========================================================================+
3591 | PACKAGE Constructor |
3592 | |
3593 | |
3594 | DESCRIPTION |
3595 | The constructor initializes the global variables and displays the |
3596 | version of the package in the debug file |
3597 | |
3598 | MODIFICATION HISTORY |
3599 | 14-July-99 Nilesh Patel Created |
3600 | |
3601 +=========================================================================*/
3602
3603 BEGIN
3604 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3605
3606 IF (g_level_procedure >= g_current_runtime_level ) THEN
3607
3608 select substr(text,5,70) into l_version_info from user_source
3609 where name = 'ZX_EXTRACT_PKG'
3610 and text like '%Header:%'
3611 and type = 'PACKAGE BODY'
3612 and line < 10;
3613
3614 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
3615 l_version_info);
3616 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
3617 'g_current_runtime_level :'||to_char(g_current_runtime_level));
3618 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
3619 'g_level_procedure :'||to_char(g_level_procedure));
3620 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
3621 'g_level_statement :'||to_char(g_level_statement));
3622
3623 END IF;
3624
3625 l_version_info := NULL;
3626
3627 IF (g_level_procedure >= g_current_runtime_level ) THEN
3628
3629 select substr(text,5,70) into l_version_info from user_source
3630 where name = 'ZX_AR_EXTRACT_PKG'
3631 and text like '%Header:%'
3632 and type = 'PACKAGE BODY'
3633 and line < 10;
3634
3635 FND_LOG.STRING(g_level_procedure, 'ZX_AR_EXTRACT_PKG version info :',
3636 l_version_info);
3637 END IF;
3638
3639 l_version_info := NULL;
3640
3641
3642 IF (g_level_procedure >= g_current_runtime_level ) THEN
3643
3644
3645 select substr(text,5,70) into l_version_info from user_source
3646 where name = 'ZX_AP_EXTRACT_PKG'
3647 and text like '%Header:%'
3648 and type = 'PACKAGE BODY'
3649 and line < 10;
3650
3651 FND_LOG.STRING(g_level_procedure, 'ZX_AP_EXTRACT_PKG version info :',
3652 l_version_info);
3653 END IF;
3654
3655 l_version_info := NULL;
3656
3657 IF (g_level_procedure >= g_current_runtime_level ) THEN
3658
3659 select substr(text,5,70) into l_version_info from user_source
3660 where name = 'ZX_AR_POPULATE_PKG'
3661 and text like '%Header:%'
3662 and type = 'PACKAGE BODY'
3663 and line < 10;
3664
3665 FND_LOG.STRING(g_level_procedure, 'ZX_AR_POPULATE_PKG version info :',
3666 l_version_info);
3667 END IF;
3668
3669 l_version_info := NULL;
3670
3671 IF (g_level_procedure >= g_current_runtime_level ) THEN
3672
3673 select substr(text,5,70) into l_version_info from user_source
3674 where name = 'ZX_AP_POPULATE_PKG'
3675 and text like '%Header:%'
3676 and type = 'PACKAGE BODY'
3677 and line < 10;
3678
3679 FND_LOG.STRING(g_level_procedure, 'ZX_AP_POPULATE_PKG version info :',
3680 l_version_info);
3681 END IF;
3682
3683 l_version_info := NULL;
3684
3685 IF (g_level_procedure >= g_current_runtime_level ) THEN
3686
3687 select substr(text,5,70) into l_version_info from user_source
3688 where name = 'ZX_GL_EXTRACT_PKG'
3689 and text like '%Header:%'
3690 and type = 'PACKAGE BODY'
3691 and line < 10;
3692
3693 FND_LOG.STRING(g_level_procedure, 'ZX_GL_EXTRACT_PKG version info :',
3694 l_version_info);
3695 END IF;
3696
3697 l_version_info := NULL;
3698
3699 -- END;
3700
3701 END ZX_EXTRACT_PKG;