[Home] [Help]
PACKAGE BODY: APPS.ZX_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_EXTRACT_PKG AS
2 /* $Header: zxriextractwpkgb.pls 120.68 2011/11/24 11:51:52 srajapar 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 --Bug 9031051
240 P_ESL_DEFAULT_TAX_DATE IN VARCHAR2,
241 P_ESL_OUT_OF_PERIOD_ADJ IN VARCHAR2,
242 P_ESL_EU_TRX_TYPE IN VARCHAR2,
243 P_ESL_EU_GOODS IN VARCHAR2,
244 P_ESL_EU_SERVICES IN VARCHAR2,
245 P_ESL_EU_ADDL_CODE1 IN VARCHAR2,
246 P_ESL_EU_ADDL_CODE2 IN VARCHAR2,
247 P_ESL_SITE_CODE IN VARCHAR2,
248 P_USE_PARALLEL_HINT IN VARCHAR2);
249 -----------------------------------------
250 --Public Methods Declarations
251 -----------------------------------------
252 --
253 -- refer to the documentation in the implemented procedure
254 --
255 PROCEDURE populate_tax_data (
256 P_REPORTING_LEVEL IN VARCHAR2 DEFAULT NULL,
257 P_REPORTING_CONTEXT IN VARCHAR2 DEFAULT NULL,
258 -- apai P_LEGAL_ENTITY_LEVEL IN VARCHAR2 DEFAULT NULL,
259 P_LEGAL_ENTITY_ID IN NUMBER DEFAULT NULL, -- apai COMPANY_NAME
260 P_SUMMARY_LEVEL IN VARCHAR2 DEFAULT NULL,
261 P_LEDGER_ID IN NUMBER DEFAULT NULL,
262 P_REGISTER_TYPE IN VARCHAR2 DEFAULT NULL,
263 P_PRODUCT IN VARCHAR2 DEFAULT NULL,
264 P_MATRIX_REPORT IN VARCHAR2 DEFAULT NULL,
265 P_DETAIL_LEVEL IN VARCHAR2 DEFAULT NULL,
266 P_CURRENCY_CODE_LOW IN VARCHAR2 DEFAULT NULL,
267 P_CURRENCY_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
268 P_INCLUDE_AP_STD_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
269 P_INCLUDE_AP_DM_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
270 P_INCLUDE_AP_CM_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
271 P_INCLUDE_AP_PREP_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
272 P_INCLUDE_AP_MIX_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
273 P_INCLUDE_AP_EXP_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
274 P_INCLUDE_AP_INT_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
275 P_INCLUDE_AR_INV_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
276 P_INCLUDE_AR_APPL_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
277 P_INCLUDE_AR_ADJ_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
278 P_INCLUDE_AR_MISC_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
279 P_INCLUDE_AR_BR_TRX_CLASS IN VARCHAR2 DEFAULT NULL,
280 P_INCLUDE_GL_MANUAL_LINES IN VARCHAR2 DEFAULT NULL,
281 P_THIRD_PARTY_REP_LEVEL IN VARCHAR2 DEFAULT NULL,
282 P_FIRST_PARTY_TAX_REG_NUM IN VARCHAR2 DEFAULT NULL,
283 P_TRX_NUMBER_LOW IN VARCHAR2 DEFAULT NULL,
284 P_TRX_NUMBER_HIGH IN VARCHAR2 DEFAULT NULL,
285 P_EXTRACT_REPORT_LINE_NUMBER IN NUMBER DEFAULT 1,
286 P_AR_TRX_PRINTING_STATUS IN VARCHAR2 DEFAULT NULL,
287 P_AR_EXEMPTION_STATUS IN VARCHAR2 DEFAULT NULL,
288 P_GL_DATE_LOW IN DATE DEFAULT NULL,
289 P_GL_DATE_HIGH IN DATE DEFAULT NULL,
290 P_TRX_DATE_LOW IN DATE DEFAULT NULL,
291 P_TRX_DATE_HIGH IN DATE DEFAULT NULL,
292 P_GL_PERIOD_NAME_LOW IN VARCHAR2 DEFAULT NULL,
293 P_GL_PERIOD_NAME_HIGH IN VARCHAR2 DEFAULT NULL,
294 P_TRX_DATE_PERIOD_NAME_LOW IN VARCHAR2 DEFAULT NULL,
295 P_TRX_DATE_PERIOD_NAME_HIGH IN VARCHAR2 DEFAULT NULL,
296 P_TAX_JURISDICTION_CODE IN VARCHAR DEFAULT NULL,
297 P_TAX_REGIME_CODE IN VARCHAR2 DEFAULT NULL,
298 P_TAX IN VARCHAR2 DEFAULT NULL,
299 P_TAX_STATUS_CODE IN VARCHAR2 DEFAULT NULL,
300 P_TAX_RATE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
301 P_TAX_RATE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
302 P_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
303 P_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
304 P_DOCUMENT_SUB_TYPE IN VARCHAR2 DEFAULT NULL,
305 P_TRX_BUSINESS_CATEGORY IN VARCHAR2 DEFAULT NULL,
306 P_TAX_INVOICE_DATE_LOW IN VARCHAR2 DEFAULT NULL,
307 P_TAX_INVOICE_DATE_HIGH IN VARCHAR2 DEFAULT NULL,
308 P_POSTING_STATUS IN VARCHAR2 DEFAULT NULL,
309 P_EXTRACT_ACCTED_TAX_LINES IN VARCHAR2 DEFAULT NULL,
310 P_INCLUDE_ACCOUNTING_SEGMENTS IN VARCHAR2 DEFAULT NULL,
311 P_BALANCING_SEGMENT_LOW IN VARCHAR2 DEFAULT NULL,
312 P_BALANCING_SEGMENT_HIGH IN VARCHAR2 DEFAULT NULL,
313 P_INCLUDE_DISCOUNTS IN VARCHAR2 DEFAULT NULL,
314 P_EXTRACT_STARTING_LINE_NUM IN NUMBER DEFAULT NULL,
315 P_REQUEST_ID IN NUMBER DEFAULT NULL,
316 P_REPORT_NAME IN VARCHAR2 DEFAULT NULL,
317 P_VAT_TRANSACTION_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
318 P_INCLUDE_FULLY_NR_TAX_FLAG IN VARCHAR2 DEFAULT NULL,
319 P_MUNICIPAL_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
320 P_MUNICIPAL_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
321 P_PROV_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
322 P_PROV_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
323 P_EXCISE_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
324 P_EXCISE_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
325 P_NON_TAXABLE_TAX_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
326 P_PER_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
327 P_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
328 P_FED_PER_TAX_TYPE_CODE_LOW IN VARCHAR2 DEFAULT NULL,
329 P_FED_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2 DEFAULT NULL,
330 P_VAT_TAX_TYPE_CODE IN VARCHAR2 DEFAULT NULL,
331 P_EXCISE_TAX IN VARCHAR2 DEFAULT NULL,
332 P_VAT_ADDITIONAL_TAX IN VARCHAR2 DEFAULT NULL,
333 P_VAT_NON_TAXABLE_TAX IN VARCHAR2 DEFAULT NULL,
334 P_VAT_NOT_TAX IN VARCHAR2 DEFAULT NULL,
335 P_VAT_PERCEPTION_TAX IN VARCHAR2 DEFAULT NULL,
336 P_VAT_TAX IN VARCHAR2 DEFAULT NULL,
337 P_INC_SELF_WD_TAX IN VARCHAR2 DEFAULT NULL,
338 P_EXCLUDING_TRX_LETTER IN VARCHAR2 DEFAULT NULL,
339 P_TRX_LETTER_LOW IN VARCHAR2 DEFAULT NULL,
340 P_TRX_LETTER_HIGH IN VARCHAR2 DEFAULT NULL,
341 P_INCLUDE_REFERENCED_SOURCE IN VARCHAR2 DEFAULT NULL,
342 P_PARTY_NAME IN VARCHAR2 DEFAULT NULL,
343 P_BATCH_NAME IN VARCHAR2 DEFAULT NULL,
344 P_BATCH_DATE_LOW IN DATE DEFAULT NULL,
345 P_BATCH_DATE_HIGH IN DATE DEFAULT NULL,
346 P_BATCH_SOURCE_ID IN VARCHAR2 DEFAULT NULL,
347 P_ADJUSTED_DOC_FROM IN VARCHAR2 DEFAULT NULL,
348 P_ADJUSTED_DOC_TO IN VARCHAR2 DEFAULT NULL,
349 P_STANDARD_VAT_TAX_RATE IN VARCHAR2 DEFAULT NULL,
350 P_MUNICIPAL_TAX IN VARCHAR2 DEFAULT NULL,
351 P_PROVINCIAL_TAX IN VARCHAR2 DEFAULT NULL,
352 P_TAX_ACCOUNT_LOW IN VARCHAR2 DEFAULT NULL,
353 P_TAX_ACCOUNT_HIGH IN VARCHAR2 DEFAULT NULL,
354 P_EXP_CERT_DATE_FROM IN DATE DEFAULT NULL,
355 P_EXP_CERT_DATE_TO IN DATE DEFAULT NULL,
356 P_EXP_METHOD IN VARCHAR2 DEFAULT NULL,
357 P_PRINT_COMPANY_INFO IN VARCHAR2 DEFAULT NULL,
358 P_ORDER_BY IN VARCHAR2 DEFAULT NULL,
359 P_CHART_OF_ACCOUNTS_ID IN NUMBER DEFAULT NULL,
360 P_REPRINT IN VARCHAR2 DEFAULT NULL,
361 P_ERRBUF IN OUT NOCOPY VARCHAR2,
362 P_RETCODE IN OUT NOCOPY VARCHAR2,
363 P_ACCOUNTING_STATUS IN VARCHAR2 DEFAULT NULL,
364 P_REPORTED_STATUS IN VARCHAR2 DEFAULT NULL,
365 P_TAXABLE_ACCOUNT_LOW IN VARCHAR2 DEFAULT NULL,
366 P_TAXABLE_ACCOUNT_HIGH IN VARCHAR2 DEFAULT NULL,
367 P_GL_OR_TRX_DATE_FILTER IN VARCHAR2 DEFAULT 'N', --Bug 5396444
368 --Bug 9031051
369 P_ESL_DEFAULT_TAX_DATE IN VARCHAR2 DEFAULT NULL,
370 P_ESL_OUT_OF_PERIOD_ADJ IN VARCHAR2 DEFAULT NULL,
371 P_ESL_EU_TRX_TYPE IN VARCHAR2 DEFAULT NULL,
372 P_ESL_EU_GOODS IN VARCHAR2 DEFAULT NULL,
373 P_ESL_EU_SERVICES IN VARCHAR2 DEFAULT NULL,
374 P_ESL_EU_ADDL_CODE1 IN VARCHAR2 DEFAULT NULL,
375 P_ESL_EU_ADDL_CODE2 IN VARCHAR2 DEFAULT NULL,
376 P_ESL_SITE_CODE IN VARCHAR2 DEFAULT NULL)
377 IS
378 BEGIN
379 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
380 -- calling the implemented method with the parameters
381 populate_tax_data (
382 p_reporting_level => p_reporting_level,
383 p_reporting_context => p_reporting_context,
384 p_legal_entity_id => p_legal_entity_id,
385 p_summary_level => p_summary_level,
386 p_ledger_id => p_ledger_id,
387 p_register_type => p_register_type,
388 p_product => p_product,
389 p_matrix_report => p_matrix_report,
390 p_detail_level => p_detail_level,
391 p_currency_code_low => p_currency_code_low,
392 p_currency_code_high => p_currency_code_high,
393 p_include_ap_std_trx_class => p_include_ap_std_trx_class,
394 p_include_ap_dm_trx_class => p_include_ap_dm_trx_class,
395 p_include_ap_cm_trx_class => p_include_ap_cm_trx_class,
396 p_include_ap_prep_trx_class => p_include_ap_prep_trx_class,
397 p_include_ap_mix_trx_class => p_include_ap_mix_trx_class,
398 p_include_ap_exp_trx_class => p_include_ap_exp_trx_class,
399 p_include_ap_int_trx_class => p_include_ap_int_trx_class,
400 p_include_ar_inv_trx_class => p_include_ar_inv_trx_class,
401 p_include_ar_appl_trx_class => p_include_ar_appl_trx_class,
402 p_include_ar_adj_trx_class => p_include_ar_adj_trx_class,
403 p_include_ar_misc_trx_class => p_include_ar_misc_trx_class,
404 p_include_ar_br_trx_class => p_include_ar_br_trx_class,
405 p_include_gl_manual_lines => p_include_gl_manual_lines,
406 p_third_party_rep_level => p_third_party_rep_level,
407 p_first_party_tax_reg_num => p_first_party_tax_reg_num,
408 p_trx_number_low => p_trx_number_low,
409 p_trx_number_high => p_trx_number_high,
410 p_extract_report_line_number => p_extract_report_line_number,
411 p_ar_trx_printing_status => p_ar_trx_printing_status,
412 p_ar_exemption_status => p_ar_exemption_status,
413 p_gl_date_low => p_gl_date_low,
414 p_gl_date_high => p_gl_date_high,
415 p_trx_date_low => p_trx_date_low,
416 p_trx_date_high => p_trx_date_high,
417 p_gl_period_name_low => p_gl_period_name_low,
418 p_gl_period_name_high => p_gl_period_name_high,
419 p_trx_date_period_name_low => p_trx_date_period_name_low,
420 p_trx_date_period_name_high => p_trx_date_period_name_high,
421 p_tax_jurisdiction_code => p_tax_jurisdiction_code,
422 p_tax_regime_code => p_tax_regime_code,
423 p_tax => p_tax,
424 p_tax_status_code => p_tax_status_code,
425 p_tax_rate_code_low => p_tax_rate_code_low,
426 p_tax_rate_code_high => p_tax_rate_code_high,
427 p_tax_type_code_low => p_tax_type_code_low,
428 p_tax_type_code_high => p_tax_type_code_high,
429 p_document_sub_type => p_document_sub_type,
430 p_trx_business_category => p_trx_business_category,
431 p_tax_invoice_date_low => p_tax_invoice_date_low,
432 p_tax_invoice_date_high => p_tax_invoice_date_high,
433 p_posting_status => p_posting_status,
434 p_extract_accted_tax_lines => p_extract_accted_tax_lines,
435 p_include_accounting_segments => p_include_accounting_segments,
436 p_balancing_segment_low => p_balancing_segment_low,
437 p_balancing_segment_high => p_balancing_segment_high,
438 p_include_discounts => p_include_discounts,
439 p_extract_starting_line_num => p_extract_starting_line_num,
440 p_request_id => p_request_id,
441 p_report_name => p_report_name,
442 p_vat_transaction_type_code => p_vat_transaction_type_code,
443 p_include_fully_nr_tax_flag => p_include_fully_nr_tax_flag,
444 p_municipal_tax_type_code_low => p_municipal_tax_type_code_low,
445 p_municipal_tax_type_code_high => p_municipal_tax_type_code_high,
446 p_prov_tax_type_code_low => p_prov_tax_type_code_low,
447 p_prov_tax_type_code_high => p_prov_tax_type_code_high,
448 p_excise_tax_type_code_low => p_excise_tax_type_code_low,
449 p_excise_tax_type_code_high => p_excise_tax_type_code_high,
450 p_non_taxable_tax_type_code => p_non_taxable_tax_type_code,
451 p_per_tax_type_code_low => p_per_tax_type_code_low,
452 p_per_tax_type_code_high => p_per_tax_type_code_high,
453 p_fed_per_tax_type_code_low => p_fed_per_tax_type_code_low,
454 p_fed_per_tax_type_code_high => p_fed_per_tax_type_code_high,
455 p_vat_tax_type_code => p_vat_tax_type_code,
456 p_excise_tax => p_excise_tax,
457 p_vat_additional_tax => p_vat_additional_tax,
458 p_vat_non_taxable_tax => p_vat_non_taxable_tax,
459 p_vat_not_tax => p_vat_not_tax,
460 p_vat_perception_tax => p_vat_perception_tax,
461 p_vat_tax => p_vat_tax,
462 p_inc_self_wd_tax => p_inc_self_wd_tax,
463 p_excluding_trx_letter => p_excluding_trx_letter,
464 p_trx_letter_low => p_trx_letter_low,
465 p_trx_letter_high => p_trx_letter_high,
466 p_include_referenced_source => p_include_referenced_source,
467 p_party_name => p_party_name,
468 p_batch_name => p_batch_name,
469 p_batch_date_low => p_batch_date_low,
470 p_batch_date_high => p_batch_date_high,
471 p_batch_source_id => p_batch_source_id,
472 p_adjusted_doc_from => p_adjusted_doc_from,
473 p_adjusted_doc_to => p_adjusted_doc_to,
474 p_standard_vat_tax_rate => p_standard_vat_tax_rate,
475 p_municipal_tax => p_municipal_tax,
476 p_provincial_tax => p_provincial_tax,
477 p_tax_account_low => p_tax_account_low,
478 p_tax_account_high => p_tax_account_high,
479 p_exp_cert_date_from => p_exp_cert_date_from,
480 p_exp_cert_date_to => p_exp_cert_date_to,
481 p_exp_method => p_exp_method,
482 p_print_company_info => p_print_company_info,
483 p_order_by => p_order_by,
484 p_chart_of_accounts_id => p_chart_of_accounts_id,
485 p_reprint => p_reprint,
486 p_errbuf => p_errbuf,
487 p_retcode => p_retcode,
488 p_accounting_status => p_accounting_status,
489 p_reported_status => p_reported_status,
490 p_taxable_account_low => p_taxable_account_low,
491 p_taxable_account_high => p_taxable_account_high,
492 p_gl_or_trx_date_filter => p_gl_or_trx_date_filter,
493 p_esl_default_tax_date => p_esl_default_tax_date,
494 p_esl_out_of_period_adj => p_esl_out_of_period_adj,
495 p_esl_eu_trx_type => p_esl_eu_trx_type,
496 p_esl_eu_goods => p_esl_eu_goods,
497 p_esl_eu_services => p_esl_eu_services,
498 p_esl_eu_addl_code1 => p_esl_eu_addl_code1,
499 p_esl_eu_addl_code2 => p_esl_eu_addl_code2,
500 p_esl_site_code => p_esl_site_code,
501 p_use_parallel_hint => 'Y'
502 );
503 -- exception block is also handled in the implemented procedure
504
505 END populate_tax_data;
506
507 /*===========================================================================+
508 | PROCEDURE |
509 | populate_tax_data() |
510 | |
511 | DESCRIPTION |
512 | This procedure calls AP,AR,and GL extract and populate packages to |
513 | populate Tax extract interface tables. |
514 | |
515 | SCOPE - Public |
516 | |
517 | NOTES |
518 | |
519 | MODIFICATION HISTORY |
520 | 12-FEB-2005 Srinivasa Rao Korrapati Created |
521 | |
522 +===========================================================================*/
523
524 -- Bug 12838309
525 PROCEDURE populate_tax_data (
526 P_REPORTING_LEVEL IN VARCHAR2,
527 P_REPORTING_CONTEXT IN VARCHAR2,
528 P_LEGAL_ENTITY_ID IN NUMBER ,
529 P_SUMMARY_LEVEL IN VARCHAR2,
530 P_LEDGER_ID IN NUMBER ,
531 P_REGISTER_TYPE IN VARCHAR2,
532 P_PRODUCT IN VARCHAR2,
533 P_MATRIX_REPORT IN VARCHAR2,
534 P_DETAIL_LEVEL IN VARCHAR2,
535 P_CURRENCY_CODE_LOW IN VARCHAR2,
536 P_CURRENCY_CODE_HIGH IN VARCHAR2,
537 P_INCLUDE_AP_STD_TRX_CLASS IN VARCHAR2,
538 P_INCLUDE_AP_DM_TRX_CLASS IN VARCHAR2,
539 P_INCLUDE_AP_CM_TRX_CLASS IN VARCHAR2,
540 P_INCLUDE_AP_PREP_TRX_CLASS IN VARCHAR2,
541 P_INCLUDE_AP_MIX_TRX_CLASS IN VARCHAR2,
542 P_INCLUDE_AP_EXP_TRX_CLASS IN VARCHAR2,
543 P_INCLUDE_AP_INT_TRX_CLASS IN VARCHAR2,
544 P_INCLUDE_AR_INV_TRX_CLASS IN VARCHAR2,
545 P_INCLUDE_AR_APPL_TRX_CLASS IN VARCHAR2,
546 P_INCLUDE_AR_ADJ_TRX_CLASS IN VARCHAR2,
547 P_INCLUDE_AR_MISC_TRX_CLASS IN VARCHAR2,
548 P_INCLUDE_AR_BR_TRX_CLASS IN VARCHAR2,
549 P_INCLUDE_GL_MANUAL_LINES IN VARCHAR2,
550 P_THIRD_PARTY_REP_LEVEL IN VARCHAR2,
551 P_FIRST_PARTY_TAX_REG_NUM IN VARCHAR2,
552 P_TRX_NUMBER_LOW IN VARCHAR2,
553 P_TRX_NUMBER_HIGH IN VARCHAR2,
554 P_EXTRACT_REPORT_LINE_NUMBER IN NUMBER,
555 P_AR_TRX_PRINTING_STATUS IN VARCHAR2,
556 P_AR_EXEMPTION_STATUS IN VARCHAR2,
557 P_GL_DATE_LOW IN DATE ,
558 P_GL_DATE_HIGH IN DATE ,
559 P_TRX_DATE_LOW IN DATE ,
560 P_TRX_DATE_HIGH IN DATE ,
561 P_GL_PERIOD_NAME_LOW IN VARCHAR2,
562 P_GL_PERIOD_NAME_HIGH IN VARCHAR2,
563 P_TRX_DATE_PERIOD_NAME_LOW IN VARCHAR2,
564 P_TRX_DATE_PERIOD_NAME_HIGH IN VARCHAR2,
565 P_TAX_JURISDICTION_CODE IN VARCHAR ,
566 P_TAX_REGIME_CODE IN VARCHAR2,
567 P_TAX IN VARCHAR2,
568 P_TAX_STATUS_CODE IN VARCHAR2,
569 P_TAX_RATE_CODE_LOW IN VARCHAR2,
570 P_TAX_RATE_CODE_HIGH IN VARCHAR2,
571 P_TAX_TYPE_CODE_LOW IN VARCHAR2,
572 P_TAX_TYPE_CODE_HIGH IN VARCHAR2,
573 P_DOCUMENT_SUB_TYPE IN VARCHAR2,
574 P_TRX_BUSINESS_CATEGORY IN VARCHAR2,
575 P_TAX_INVOICE_DATE_LOW IN VARCHAR2,
576 P_TAX_INVOICE_DATE_HIGH IN VARCHAR2,
577 P_POSTING_STATUS IN VARCHAR2,
578 P_EXTRACT_ACCTED_TAX_LINES IN VARCHAR2,
579 P_INCLUDE_ACCOUNTING_SEGMENTS IN VARCHAR2,
580 P_BALANCING_SEGMENT_LOW IN VARCHAR2,
581 P_BALANCING_SEGMENT_HIGH IN VARCHAR2,
582 P_INCLUDE_DISCOUNTS IN VARCHAR2,
583 P_EXTRACT_STARTING_LINE_NUM IN NUMBER ,
584 P_REQUEST_ID IN NUMBER ,
585 P_REPORT_NAME IN VARCHAR2,
586 P_VAT_TRANSACTION_TYPE_CODE IN VARCHAR2,
587 P_INCLUDE_FULLY_NR_TAX_FLAG IN VARCHAR2,
588 P_MUNICIPAL_TAX_TYPE_CODE_LOW IN VARCHAR2,
589 P_MUNICIPAL_TAX_TYPE_CODE_HIGH IN VARCHAR2,
590 P_PROV_TAX_TYPE_CODE_LOW IN VARCHAR2,
591 P_PROV_TAX_TYPE_CODE_HIGH IN VARCHAR2,
592 P_EXCISE_TAX_TYPE_CODE_LOW IN VARCHAR2,
593 P_EXCISE_TAX_TYPE_CODE_HIGH IN VARCHAR2,
594 P_NON_TAXABLE_TAX_TYPE_CODE IN VARCHAR2,
595 P_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
596 P_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
597 P_FED_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
598 P_FED_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
599 P_VAT_TAX_TYPE_CODE IN VARCHAR2,
600 P_EXCISE_TAX IN VARCHAR2,
601 P_VAT_ADDITIONAL_TAX IN VARCHAR2,
602 P_VAT_NON_TAXABLE_TAX IN VARCHAR2,
603 P_VAT_NOT_TAX IN VARCHAR2,
604 P_VAT_PERCEPTION_TAX IN VARCHAR2,
605 P_VAT_TAX IN VARCHAR2,
606 P_INC_SELF_WD_TAX IN VARCHAR2,
607 P_EXCLUDING_TRX_LETTER IN VARCHAR2,
608 P_TRX_LETTER_LOW IN VARCHAR2,
609 P_TRX_LETTER_HIGH IN VARCHAR2,
610 P_INCLUDE_REFERENCED_SOURCE IN VARCHAR2,
611 P_PARTY_NAME IN VARCHAR2,
612 P_BATCH_NAME IN VARCHAR2,
613 P_BATCH_DATE_LOW IN DATE ,
614 P_BATCH_DATE_HIGH IN DATE ,
615 P_BATCH_SOURCE_ID IN VARCHAR2,
616 P_ADJUSTED_DOC_FROM IN VARCHAR2,
617 P_ADJUSTED_DOC_TO IN VARCHAR2,
618 P_STANDARD_VAT_TAX_RATE IN VARCHAR2,
619 P_MUNICIPAL_TAX IN VARCHAR2,
620 P_PROVINCIAL_TAX IN VARCHAR2,
621 P_TAX_ACCOUNT_LOW IN VARCHAR2,
622 P_TAX_ACCOUNT_HIGH IN VARCHAR2,
623 P_EXP_CERT_DATE_FROM IN DATE ,
624 P_EXP_CERT_DATE_TO IN DATE ,
625 P_EXP_METHOD IN VARCHAR2,
626 P_PRINT_COMPANY_INFO IN VARCHAR2,
627 P_ORDER_BY IN VARCHAR2,
628 P_CHART_OF_ACCOUNTS_ID IN NUMBER ,
629 P_REPRINT IN VARCHAR2,
630 P_ERRBUF IN OUT NOCOPY VARCHAR2,
631 P_RETCODE IN OUT NOCOPY VARCHAR2,
632 P_ACCOUNTING_STATUS IN VARCHAR2,
633 P_REPORTED_STATUS IN VARCHAR2,
634 P_TAXABLE_ACCOUNT_LOW IN VARCHAR2,
635 P_TAXABLE_ACCOUNT_HIGH IN VARCHAR2,
636 P_GL_OR_TRX_DATE_FILTER IN VARCHAR2,
637 P_ESL_DEFAULT_TAX_DATE IN VARCHAR2,
638 P_ESL_OUT_OF_PERIOD_ADJ IN VARCHAR2,
639 P_ESL_EU_TRX_TYPE IN VARCHAR2,
640 P_ESL_EU_GOODS IN VARCHAR2,
641 P_ESL_EU_SERVICES IN VARCHAR2,
642 P_ESL_EU_ADDL_CODE1 IN VARCHAR2,
643 P_ESL_EU_ADDL_CODE2 IN VARCHAR2,
644 P_ESL_SITE_CODE IN VARCHAR2,
645 P_USE_PARALLEL_HINT IN VARCHAR2)
646 IS
647 l_trl_global_variables_rec TRL_GLOBAL_VARIABLES_REC_TYPE;
648 l_count NUMBER; -- bug 10370545
649 l_start_time DATE; -- bug 10370545
650 l_end_time DATE; -- bug 10370545
651 l_total_time VARCHAR2(10); -- bug 10370545
652 BEGIN
653 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
654
655 -- bug 10370545 Starts
656 BEGIN
657 IF (nvl(P_REQUEST_ID,-1) <> -1) THEN
658 FND_FILE.put_line(which => FND_FILE.LOG
659 ,buff => '+-------------------------------+');
660 FND_FILE.put_line(which => FND_FILE.LOG
661 ,buff => '| Report run statistics for TRL |');
662 FND_FILE.put_line(which => FND_FILE.LOG
663 ,buff => '+-------------------------------+');
664 FND_FILE.put_line(which => FND_FILE.LOG
665 ,buff => ' ');
666 FND_FILE.put_line(which => FND_FILE.LOG
667 ,buff => 'Data in tables (before the report is run)');
668
669 SELECT COUNT(detail_tax_line_id)
670 INTO l_count
671 FROM zx_rep_trx_detail_t;
672
673 FND_FILE.put_line(which => FND_FILE.LOG
674 ,buff => 'zx_rep_trx_detail_t : '||l_count);
675
676 SELECT COUNT(actg_ext_line_id)
677 INTO l_count
678 FROM zx_rep_actg_ext_t;
679
680 FND_FILE.put_line(which => FND_FILE.LOG
681 ,buff => 'zx_rep_actg_ext_t : '||l_count);
682
683 SELECT COUNT(detail_tax_line_ext_id)
684 INTO l_count
685 FROM zx_rep_trx_jx_ext_t;
686
687 FND_FILE.put_line(which => FND_FILE.LOG
688 ,buff => 'zx_rep_trx_jx_ext_t : '||l_count);
689
690 SELECT COUNT(rep_context_id)
691 INTO l_count
692 FROM zx_rep_context_t;
693
694 FND_FILE.put_line(which => FND_FILE.LOG
695 ,buff => 'zx_rep_context_t : '||l_count);
696
697 l_start_time := SYSDATE;
698 END IF;
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 NULL;
703 END;
704
705 -- bug 10370545 Ends
706 IF (g_level_procedure >= g_current_runtime_level ) THEN
707 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data.BEGIN',
708 'ZX_EXTRACT_PKG:populate_tax_data(+): '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
709 END IF;
710
711
712 -- Initialize the parameters:
713
714 initialize(
715 L_TRL_GLOBAL_VARIABLES_REC,
716 P_REPORTING_LEVEL,
717 P_REPORTING_CONTEXT,
718 -- apai P_LEGAL_ENTITY_LEVEL,
719 P_LEGAL_ENTITY_ID,
720 P_SUMMARY_LEVEL,
721 P_LEDGER_ID,
722 P_REGISTER_TYPE,
723 P_PRODUCT,
724 P_MATRIX_REPORT,
725 P_DETAIL_LEVEL,
726 P_CURRENCY_CODE_LOW,
727 P_CURRENCY_CODE_HIGH,
728 P_INCLUDE_AP_STD_TRX_CLASS,
729 P_INCLUDE_AP_DM_TRX_CLASS,
730 P_INCLUDE_AP_CM_TRX_CLASS,
731 P_INCLUDE_AP_PREP_TRX_CLASS,
732 P_INCLUDE_AP_MIX_TRX_CLASS,
733 P_INCLUDE_AP_EXP_TRX_CLASS,
734 P_INCLUDE_AP_INT_TRX_CLASS,
735 P_INCLUDE_AR_INV_TRX_CLASS,
736 P_INCLUDE_AR_APPL_TRX_CLASS,
737 P_INCLUDE_AR_ADJ_TRX_CLASS,
738 P_INCLUDE_AR_MISC_TRX_CLASS,
739 P_INCLUDE_AR_BR_TRX_CLASS,
740 P_INCLUDE_GL_MANUAL_LINES,
741 P_THIRD_PARTY_REP_LEVEL,
742 P_FIRST_PARTY_TAX_REG_NUM,
743 P_TRX_NUMBER_LOW,
744 P_TRX_NUMBER_HIGH,
745 P_EXTRACT_REPORT_LINE_NUMBER,
746 P_AR_TRX_PRINTING_STATUS,
747 P_AR_EXEMPTION_STATUS,
748 P_GL_DATE_LOW,
749 P_GL_DATE_HIGH,
750 P_TRX_DATE_LOW,
751 P_TRX_DATE_HIGH,
752 P_GL_PERIOD_NAME_LOW,
753 P_GL_PERIOD_NAME_HIGH,
754 P_TRX_DATE_PERIOD_NAME_LOW,
755 P_TRX_DATE_PERIOD_NAME_HIGH,
756 P_TAX_JURISDICTION_CODE,
757 P_TAX_REGIME_CODE,
758 P_TAX,
759 P_TAX_STATUS_CODE,
760 P_TAX_RATE_CODE_LOW,
761 P_TAX_RATE_CODE_HIGH,
762 P_TAX_TYPE_CODE_LOW,
763 P_TAX_TYPE_CODE_HIGH,
764 P_DOCUMENT_SUB_TYPE,
765 P_TRX_BUSINESS_CATEGORY,
766 P_TAX_INVOICE_DATE_LOW,
767 P_TAX_INVOICE_DATE_HIGH,
768 P_POSTING_STATUS,
769 P_EXTRACT_ACCTED_TAX_LINES,
770 P_INCLUDE_ACCOUNTING_SEGMENTS,
771 P_BALANCING_SEGMENT_LOW,
772 P_BALANCING_SEGMENT_HIGH,
773 P_INCLUDE_DISCOUNTS,
774 P_EXTRACT_STARTING_LINE_NUM,
775 P_REQUEST_ID,
776 P_REPORT_NAME,
777 P_VAT_TRANSACTION_TYPE_CODE,
778 P_INCLUDE_FULLY_NR_TAX_FLAG,
779 P_MUNICIPAL_TAX_TYPE_CODE_LOW,
780 P_MUNICIPAL_TAX_TYPE_CODE_HIGH,
781 P_PROV_TAX_TYPE_CODE_LOW,
782 P_PROV_TAX_TYPE_CODE_HIGH,
783 P_EXCISE_TAX_TYPE_CODE_LOW,
784 P_EXCISE_TAX_TYPE_CODE_HIGH,
785 P_NON_TAXABLE_TAX_TYPE_CODE,
786 P_PER_TAX_TYPE_CODE_LOW,
787 P_PER_TAX_TYPE_CODE_HIGH,
788 P_FED_PER_TAX_TYPE_CODE_LOW,
789 P_FED_PER_TAX_TYPE_CODE_HIGH,
790 P_VAT_TAX_TYPE_CODE,
791 P_EXCISE_TAX,
792 P_VAT_ADDITIONAL_TAX,
793 P_VAT_NON_TAXABLE_TAX,
794 P_VAT_NOT_TAX,
795 P_VAT_PERCEPTION_TAX,
796 P_VAT_TAX,
797 P_INC_SELF_WD_TAX,
798 P_EXCLUDING_TRX_LETTER,
799 P_TRX_LETTER_LOW,
800 P_TRX_LETTER_HIGH,
801 P_INCLUDE_REFERENCED_SOURCE,
802 P_PARTY_NAME,
803 P_BATCH_NAME,
804 P_BATCH_DATE_LOW,
805 P_BATCH_DATE_HIGH,
806 P_BATCH_SOURCE_ID,
807 P_ADJUSTED_DOC_FROM,
808 P_ADJUSTED_DOC_TO,
809 P_STANDARD_VAT_TAX_RATE,
810 P_MUNICIPAL_TAX,
811 P_PROVINCIAL_TAX,
812 P_TAX_ACCOUNT_LOW,
813 P_TAX_ACCOUNT_HIGH,
814 P_EXP_CERT_DATE_FROM,
815 P_EXP_CERT_DATE_TO,
816 P_EXP_METHOD,
817 P_PRINT_COMPANY_INFO,
818 P_ORDER_BY,
819 P_CHART_OF_ACCOUNTS_ID,
820 P_REPRINT,
821 P_ERRBUF,
822 P_RETCODE,
823 P_ACCOUNTING_STATUS,
824 P_REPORTED_STATUS,
825 P_TAXABLE_ACCOUNT_LOW,
826 P_TAXABLE_ACCOUNT_HIGH,
827 P_GL_OR_TRX_DATE_FILTER, --Bug 5396444
828 --Bug 9031051
829 P_ESL_DEFAULT_TAX_DATE,
830 P_ESL_OUT_OF_PERIOD_ADJ,
831 P_ESL_EU_TRX_TYPE,
832 P_ESL_EU_GOODS,
833 P_ESL_EU_SERVICES,
834 P_ESL_EU_ADDL_CODE1,
835 P_ESL_EU_ADDL_CODE2,
836 P_ESL_SITE_CODE,
837 P_USE_PARALLEL_HINT);
838
839 -- Check whether this is a Multi Org or Non Multi Org installation;
840
841 -- Derive the dependent parameters:
842
843 derive_dependent_parameters(l_trl_global_variables_rec);
844
845 IF (g_level_procedure >= g_current_runtime_level ) THEN
846 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
847 'l_trl_global_variables_rec.gl_date_high : '|| to_char(l_trl_global_variables_rec.gl_date_high,
848 'DD-MM-YYYY HH24:MI:SS'));
849 END IF;
850
851 IF g_ledger_type = 'R' THEN
852 fnd_client_info.set_currency_context(p_ledger_id);
853 END IF;
854
855 IF (g_level_procedure >= g_current_runtime_level ) THEN
856 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
857 'Return Code Check '||to_char(l_trl_global_variables_rec.retcode));
858 END IF;
859
860 IF validate_parameters(l_trl_global_variables_rec) THEN
861 IF l_trl_global_variables_rec.retcode <> 2 THEN
862 extract_rep_context_info(l_trl_global_variables_rec);
863 END IF;
864
865 IF (g_level_procedure >= g_current_runtime_level ) THEN
866 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
867 'ZX.TRL.ZX_EXTRACT_PKG.EXTRACT_TAX_INFO Call');
868 END IF;
869
870 IF l_trl_global_variables_rec.retcode <> 2 THEN
871 extract_tax_info(g_ledger_type, l_trl_global_variables_rec);
872 END IF;
873 IF (g_level_procedure >= g_current_runtime_level ) THEN
874 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
875 'extract_additional_info call : '||to_char(l_trl_global_variables_rec.retcode));
876 END IF;
877 -- IF l_trl_global_variables_rec.retcode <> 2 THEN
878 extract_additional_info(g_ledger_type, l_trl_global_variables_rec) ;
879 cleanup(l_trl_global_variables_rec);
880 --COMMIT; Bug 8262631
881
882 -- END IF;
883
884 IF l_trl_global_variables_rec.retcode <> 2 THEN
885 cleanup(l_trl_global_variables_rec);
886 ELSE
887 delete_all(l_trl_global_variables_rec.request_id);
888 END IF;
889 --COMMIT; Bug 8262631
890
891 l_errbuf := trim(substrb(L_ERRBUF,1,240))||
892 trim(substrb(L_PARAMLIST,1,1500));
893 p_errbuf := substrb(L_ERRBUF,1,l_length_errbuf);
894 -- p_retcode := l_retcode;
895
896
897 IF p_retcode IS NULL THEN
898 p_retcode := 0;
899 END IF;
900 END IF;
901
902 -- bug 10370545 Starts
903
904 BEGIN
905 IF (nvl(P_REQUEST_ID,-1) <> -1) THEN
906 l_end_time := SYSDATE;
907 FND_FILE.put_line(which => FND_FILE.LOG
908 ,buff => ' ');
909 FND_FILE.put_line(which => FND_FILE.LOG
910 ,buff => 'TRL Start Time :'||To_Char(l_start_time,'DD-MM-YYYY HH24:MI:SS'));
911 FND_FILE.put_line(which => FND_FILE.LOG
912 ,buff => 'TRL End Time :'||To_Char(l_end_time,'DD-MM-YYYY HH24:MI:SS'));
913
914
915 SELECT floor((l_end_time-l_start_time)*24)
916 || ':' ||
917 mod(floor((l_end_time-l_start_time)*24*60),60)
918 || ':' ||
919 mod(floor((l_end_time-l_start_time)*24*60*60),60)
920 time_difference
921 INTO l_total_time
922 FROM dual;
923
924
925 FND_FILE.put_line(which => FND_FILE.LOG
926 ,buff => 'Total Time Taken :'||l_total_time);
927 END IF;
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 NULL;
932 END;
933
934 -- bug 10370545 Ends
935
936 IF (g_level_procedure >= g_current_runtime_level ) THEN
937 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.Return Code ',
938 'ZX_EXTRACT_PKG:populate_tax_data' ||to_char(l_trl_global_variables_rec.retcode));
939 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data.END',
940 'ZX_EXTRACT_PKG:populate_tax_data(-)'||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
941 END IF;
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
946 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
947 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
948 FND_MSG_PUB.Add;
949 IF (g_level_unexpected >= g_current_runtime_level ) THEN
950 FND_LOG.STRING(g_level_unexpected,
951 'ZX.TRL.ZX_EXTRACT_PKG.populate_tax_data',
952 g_error_buffer);
953 END IF;
954
955 P_RETCODE := l_trl_global_variables_rec.retcode;
956
957 END populate_tax_data;
958
959 ------------------------------------------------------------------------------
960 -- PRIVATE METHODS
961 ------------------------------------------------------------------------------
962
963
964 /*===========================================================================+
965 | PROCEDURE |
966 | INITIALIZE |
967 | |
968 | DESCRIPTION |
969 | This procedure initializes the parameters for procedure |
970 | ZX_EXTRACT_PKG.populate_tax_data, and writes the values of parameters |
971 | passed in debug file and p_errbuf. |
972 | |
973 | Called from ZX_EXTRACT_PKG.populate_tax_data |
974 | |
975 | SCOPE - Private |
976 | |
977 | NOTES |
978 | |
979 | MODIFICATION HISTORY |
980 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
981 | |
982 +===========================================================================*/
983
984 PROCEDURE initialize(
985 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
986 P_REPORTING_LEVEL IN VARCHAR2,
987 P_REPORTING_CONTEXT IN VARCHAR2,
988 -- apai P_LEGAL_ENTITY_LEVEL IN VARCHAR2,
989 P_LEGAL_ENTITY_ID IN NUMBER , -- apai COMPANY_NAME
990 P_SUMMARY_LEVEL IN VARCHAR2,
991 P_LEDGER_ID IN NUMBER ,
992 P_REGISTER_TYPE IN VARCHAR2,
993 P_PRODUCT IN VARCHAR2,
994 P_MATRIX_REPORT IN VARCHAR2,
995 P_DETAIL_LEVEL IN VARCHAR2,
996 P_CURRENCY_CODE_LOW IN VARCHAR2,
997 P_CURRENCY_CODE_HIGH IN VARCHAR2,
998 P_INCLUDE_AP_STD_TRX_CLASS IN VARCHAR2,
999 P_INCLUDE_AP_DM_TRX_CLASS IN VARCHAR2,
1000 P_INCLUDE_AP_CM_TRX_CLASS IN VARCHAR2,
1001 P_INCLUDE_AP_PREP_TRX_CLASS IN VARCHAR2,
1002 P_INCLUDE_AP_MIX_TRX_CLASS IN VARCHAR2,
1003 P_INCLUDE_AP_EXP_TRX_CLASS IN VARCHAR2,
1004 P_INCLUDE_AP_INT_TRX_CLASS IN VARCHAR2,
1005 P_INCLUDE_AR_INV_TRX_CLASS IN VARCHAR2,
1006 P_INCLUDE_AR_APPL_TRX_CLASS IN VARCHAR2,
1007 P_INCLUDE_AR_ADJ_TRX_CLASS IN VARCHAR2,
1008 P_INCLUDE_AR_MISC_TRX_CLASS IN VARCHAR2,
1009 P_INCLUDE_AR_BR_TRX_CLASS IN VARCHAR2,
1010 P_INCLUDE_GL_MANUAL_LINES IN VARCHAR2,
1011 P_THIRD_PARTY_REP_LEVEL IN VARCHAR2,
1012 P_FIRST_PARTY_TAX_REG_NUM IN VARCHAR2,
1013 P_TRX_NUMBER_LOW IN VARCHAR2,
1014 P_TRX_NUMBER_HIGH IN VARCHAR2,
1015 P_EXTRACT_REPORT_LINE_NUMBER IN NUMBER ,
1016 P_AR_TRX_PRINTING_STATUS IN VARCHAR2,
1017 P_AR_EXEMPTION_STATUS IN VARCHAR2,
1018 P_GL_DATE_LOW IN DATE ,
1019 P_GL_DATE_HIGH IN DATE ,
1020 P_TRX_DATE_LOW IN DATE ,
1021 P_TRX_DATE_HIGH IN DATE ,
1022 P_GL_PERIOD_NAME_LOW IN VARCHAR2,
1023 P_GL_PERIOD_NAME_HIGH IN VARCHAR2,
1024 P_TRX_DATE_PERIOD_NAME_LOW IN VARCHAR2,
1025 P_TRX_DATE_PERIOD_NAME_HIGH IN VARCHAR2,
1026 P_TAX_JURISDICTION_CODE IN VARCHAR ,
1027 P_TAX_REGIME_CODE IN VARCHAR2,
1028 P_TAX IN VARCHAR2,
1029 P_TAX_STATUS_CODE IN VARCHAR2,
1030 P_TAX_RATE_CODE_LOW IN VARCHAR2,
1031 P_TAX_RATE_CODE_HIGH IN VARCHAR2,
1032 P_TAX_TYPE_CODE_LOW IN VARCHAR2,
1033 P_TAX_TYPE_CODE_HIGH IN VARCHAR2,
1034 P_DOCUMENT_SUB_TYPE IN VARCHAR2,
1035 P_TRX_BUSINESS_CATEGORY IN VARCHAR2,
1036 P_TAX_INVOICE_DATE_LOW IN VARCHAR2,
1037 P_TAX_INVOICE_DATE_HIGH IN VARCHAR2,
1038 P_POSTING_STATUS IN VARCHAR2,
1039 P_EXTRACT_ACCTED_TAX_LINES IN VARCHAR2,
1040 P_INCLUDE_ACCOUNTING_SEGMENTS IN VARCHAR2,
1041 P_BALANCING_SEGMENT_LOW IN VARCHAR2,
1042 P_BALANCING_SEGMENT_HIGH IN VARCHAR2,
1043 P_INCLUDE_DISCOUNTS IN VARCHAR2,
1044 P_EXTRACT_STARTING_LINE_NUM IN NUMBER ,
1045 P_REQUEST_ID IN NUMBER ,
1046 P_REPORT_NAME IN VARCHAR2,
1047 P_VAT_TRANSACTION_TYPE_CODE IN VARCHAR2,
1048 P_INCLUDE_FULLY_NR_TAX_FLAG IN VARCHAR2,
1049 P_MUNICIPAL_TAX_TYPE_CODE_LOW IN VARCHAR2,
1050 P_MUNICIPAL_TAX_TYPE_CODE_HIGH IN VARCHAR2,
1051 P_PROV_TAX_TYPE_CODE_LOW IN VARCHAR2,
1052 P_PROV_TAX_TYPE_CODE_HIGH IN VARCHAR2,
1053 P_EXCISE_TAX_TYPE_CODE_LOW IN VARCHAR2,
1054 P_EXCISE_TAX_TYPE_CODE_HIGH IN VARCHAR2,
1055 P_NON_TAXABLE_TAX_TYPE_CODE IN VARCHAR2,
1056 P_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
1057 P_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
1058 P_FED_PER_TAX_TYPE_CODE_LOW IN VARCHAR2,
1059 P_FED_PER_TAX_TYPE_CODE_HIGH IN VARCHAR2,
1060 P_VAT_TAX_TYPE_CODE IN VARCHAR2,
1061 P_EXCISE_TAX IN VARCHAR2,
1062 P_VAT_ADDITIONAL_TAX IN VARCHAR2,
1063 P_VAT_NON_TAXABLE_TAX IN VARCHAR2,
1064 P_VAT_NOT_TAX IN VARCHAR2,
1065 P_VAT_PERCEPTION_TAX IN VARCHAR2,
1066 P_VAT_TAX IN VARCHAR2,
1067 P_INC_SELF_WD_TAX IN VARCHAR2,
1068 P_EXCLUDING_TRX_LETTER IN VARCHAR2,
1069 P_TRX_LETTER_LOW IN VARCHAR2,
1070 P_TRX_LETTER_HIGH IN VARCHAR2,
1071 P_INCLUDE_REFERENCED_SOURCE IN VARCHAR2,
1072 P_PARTY_NAME IN VARCHAR2,
1073 P_BATCH_NAME IN VARCHAR2,
1074 P_BATCH_DATE_LOW IN DATE ,
1075 P_BATCH_DATE_HIGH IN DATE ,
1076 P_BATCH_SOURCE_ID IN VARCHAR2,
1077 P_ADJUSTED_DOC_FROM IN VARCHAR2,
1078 P_ADJUSTED_DOC_TO IN VARCHAR2,
1079 P_STANDARD_VAT_TAX_RATE IN VARCHAR2,
1080 P_MUNICIPAL_TAX IN VARCHAR2,
1081 P_PROVINCIAL_TAX IN VARCHAR2,
1082 P_TAX_ACCOUNT_LOW IN VARCHAR2,
1083 P_TAX_ACCOUNT_HIGH IN VARCHAR2,
1084 P_EXP_CERT_DATE_FROM IN DATE ,
1085 P_EXP_CERT_DATE_TO IN DATE ,
1086 P_EXP_METHOD IN VARCHAR2,
1087 P_PRINT_COMPANY_INFO IN VARCHAR2,
1088 P_ORDER_BY IN VARCHAR2,
1089 P_CHART_OF_ACCOUNTS_ID IN NUMBER ,
1090 P_REPRINT IN VARCHAR2,
1091 P_ERRBUF IN OUT NOCOPY VARCHAR2,
1092 P_RETCODE IN OUT NOCOPY VARCHAR2,
1093 P_ACCOUNTING_STATUS IN VARCHAR2,
1094 P_REPORTED_STATUS IN VARCHAR2,
1095 P_TAXABLE_ACCOUNT_LOW IN VARCHAR2,
1096 P_TAXABLE_ACCOUNT_HIGH IN VARCHAR2,
1097 P_GL_OR_TRX_DATE_FILTER IN VARCHAR2, --Bug 5396444
1098 --Bug 9031051
1099 P_ESL_DEFAULT_TAX_DATE IN VARCHAR2,
1100 P_ESL_OUT_OF_PERIOD_ADJ IN VARCHAR2,
1101 P_ESL_EU_TRX_TYPE IN VARCHAR2,
1102 P_ESL_EU_GOODS IN VARCHAR2,
1103 P_ESL_EU_SERVICES IN VARCHAR2,
1104 P_ESL_EU_ADDL_CODE1 IN VARCHAR2,
1105 P_ESL_EU_ADDL_CODE2 IN VARCHAR2,
1106 P_ESL_SITE_CODE IN VARCHAR2,
1107 P_USE_PARALLEL_HINT IN VARCHAR2)
1108 IS
1109 BEGIN
1110
1111
1112 IF (g_level_procedure >= g_current_runtime_level ) THEN
1113 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE.BEGIN',
1114 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE(+)');
1115
1116 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1117 'P_REPORTING_LEVEL = '||P_REPORTING_LEVEL);
1118
1119
1120 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1121 'P_REPORTING_CONTEXT = '||P_REPORTING_CONTEXT);
1122 /* apai
1123 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1124 'P_LEGAL_ENTITY_LEVEL = '||P_LEGAL_ENTITY_LEVEL);
1125 */
1126 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1127 'P_LEGAL_ENTITY_ID = '||P_LEGAL_ENTITY_ID);
1128 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1129 'P_SUMMARY_LEVEL = '||P_SUMMARY_LEVEL);
1130 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1131 'P_LEDGER_ID = '||P_LEDGER_ID);
1132 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1133 'P_REGISTER_TYPE = '||P_REGISTER_TYPE);
1134 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1135 'P_PRODUCT = '||P_PRODUCT);
1136 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1137 'P_MATRIX_REPORT = '||P_MATRIX_REPORT);
1138 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1139 'P_CURRENCY_CODE_LOW = '||P_CURRENCY_CODE_LOW);
1140 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1141 'P_CURRENCY_CODE_HIGH = '||P_CURRENCY_CODE_HIGH);
1142 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1143 'P_INCLUDE_AP_STD_TRX_CLASS = '||P_INCLUDE_AP_STD_TRX_CLASS);
1144 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1145 'P_INCLUDE_AP_DM_TRX_CLASS = '||P_INCLUDE_AP_DM_TRX_CLASS);
1146 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1147 'P_INCLUDE_AP_CM_TRX_CLASS = '||P_INCLUDE_AP_CM_TRX_CLASS);
1148 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1149 'P_INCLUDE_AP_PREP_TRX_CLASS = '||P_INCLUDE_AP_PREP_TRX_CLASS);
1150 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1151 'P_INCLUDE_AP_MIX_TRX_CLASS = '||P_INCLUDE_AP_MIX_TRX_CLASS);
1152 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1153 'P_INCLUDE_AP_EXP_TRX_CLASS = '||P_INCLUDE_AP_EXP_TRX_CLASS);
1154 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1155 'P_INCLUDE_AP_INT_TRX_CLASS = '||P_INCLUDE_AP_INT_TRX_CLASS);
1156 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1157 'P_INCLUDE_AR_INV_TRX_CLASS = '||P_INCLUDE_AR_INV_TRX_CLASS);
1158 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1159 'P_INCLUDE_AR_APPL_TRX_CLASS = '||P_INCLUDE_AR_APPL_TRX_CLASS);
1160 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1161 'P_INCLUDE_AR_ADJ_TRX_CLASS = '||P_INCLUDE_AR_ADJ_TRX_CLASS);
1162 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1163 'P_INCLUDE_AR_MISC_TRX_CLASS = '||P_INCLUDE_AR_MISC_TRX_CLASS);
1164 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1165 'P_INCLUDE_AR_BR_TRX_CLASS = '||P_INCLUDE_AR_BR_TRX_CLASS);
1166 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1167 'P_INCLUDE_GL_MANUAL_LINES = '||P_INCLUDE_GL_MANUAL_LINES);
1168 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1169 'P_THIRD_PARTY_REP_LEVEL = '||P_THIRD_PARTY_REP_LEVEL);
1170 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1171 'P_FIRST_PARTY_TAX_REG_NUM = '||P_FIRST_PARTY_TAX_REG_NUM);
1172 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1173 'P_TRX_NUMBER_LOW = '||P_TRX_NUMBER_LOW);
1174 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1175 'P_TRX_NUMBER_HIGH = '||P_TRX_NUMBER_HIGH);
1176 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1177 'P_AR_TRX_PRINTING_STATUS = '||P_AR_TRX_PRINTING_STATUS);
1178 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1179 'P_AR_EXEMPTION_STATUS = '||P_AR_EXEMPTION_STATUS);
1180 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1181 'P_GL_DATE_LOW = '||P_GL_DATE_LOW);
1182 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1183 'P_GL_DATE_HIGH = '||P_GL_DATE_HIGH);
1184 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1185 'P_TRX_DATE_LOW = '||P_TRX_DATE_LOW);
1186 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1187 'P_TRX_DATE_HIGH = '||P_TRX_DATE_HIGH);
1188 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1189 'P_GL_PERIOD_NAME_LOW = '||P_GL_PERIOD_NAME_LOW);
1190 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1191 'P_GL_PERIOD_NAME_HIGH = '||P_GL_PERIOD_NAME_HIGH);
1192 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1193 'P_TRX_DATE_PERIOD_NAME_LOW = '||P_TRX_DATE_PERIOD_NAME_LOW);
1194 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1195 'P_TRX_DATE_PERIOD_NAME_HIGH = '||P_TRX_DATE_PERIOD_NAME_HIGH);
1196 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1197 'P_TAX_JURISDICTION_CODE = '||P_TAX_JURISDICTION_CODE);
1198 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1199 'P_TAX_REGIME_CODE = '||P_TAX_REGIME_CODE);
1200 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1201 'P_TAX = '||P_TAX);
1202 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1203 'P_TAX_STATUS_CODE = '||P_TAX_STATUS_CODE);
1204 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1205 'P_TAX_RATE_CODE_LOW = '||P_TAX_RATE_CODE_LOW);
1206 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1207 'P_TAX_RATE_CODE_HIGH = '||P_TAX_RATE_CODE_HIGH);
1208 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1209 'P_TAX_TYPE_CODE_LOW = '||P_TAX_TYPE_CODE_LOW);
1210 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1211 'P_TAX_TYPE_CODE_HIGH = '||P_TAX_TYPE_CODE_HIGH);
1212 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1213 'P_DOCUMENT_SUB_TYPE = '||P_DOCUMENT_SUB_TYPE);
1214 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1215 'P_TRX_BUSINESS_CATEGORY = '||P_TRX_BUSINESS_CATEGORY);
1216 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1217 'P_TAX_INVOICE_DATE_LOW = '||P_TAX_INVOICE_DATE_LOW);
1218 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1219 'P_TAX_INVOICE_DATE_HIGH = '||P_TAX_INVOICE_DATE_HIGH);
1220 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1221 'P_POSTING_STATUS = '||P_POSTING_STATUS);
1222 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1223 'P_ACCOUNTING_STATUS = '||P_ACCOUNTING_STATUS);
1224 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1225 'P_REPORTED_STATUS = '||P_REPORTED_STATUS);
1226 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1227 'P_EXTRACT_ACCTED_TAX_LINES = '||P_EXTRACT_ACCTED_TAX_LINES);
1228 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1229 'P_INCLUDE_ACCOUNTING_SEGMENTS = '||P_INCLUDE_ACCOUNTING_SEGMENTS);
1230 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1231 'P_BALANCING_SEGMENT_LOW = '||P_BALANCING_SEGMENT_LOW);
1232 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1233 'P_BALANCING_SEGMENT_HIGH = '||P_BALANCING_SEGMENT_HIGH);
1234 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1235 'P_INCLUDE_DISCOUNTS = '||P_INCLUDE_DISCOUNTS);
1236 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1237 'P_EXTRACT_STARTING_LINE_NUM = '||P_EXTRACT_STARTING_LINE_NUM);
1238 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1239 'P_REQUEST_ID = '||P_REQUEST_ID);
1240 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1241 'P_REPORT_NAME = '||P_REPORT_NAME);
1242 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1243 'P_VAT_TRANSACTION_TYPE_CODE = '||P_VAT_TRANSACTION_TYPE_CODE);
1244 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1245 'P_INCLUDE_FULLY_NR_TAX_FLAG = '||P_INCLUDE_FULLY_NR_TAX_FLAG);
1246 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1247 'P_MUNICIPAL_TAX_TYPE_CODE_LOW = '||P_MUNICIPAL_TAX_TYPE_CODE_LOW);
1248 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1249 'P_MUNICIPAL_TAX_TYPE_CODE_HIGH = '||P_MUNICIPAL_TAX_TYPE_CODE_HIGH);
1250 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1251 'P_PROVINCIAL_TAX_TYPE_CODE_LOW = '||P_PROV_TAX_TYPE_CODE_LOW);
1252 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1253 'P_PROVINCIAL_TAX_TYPE__CODE_HIGH = '||P_PROV_TAX_TYPE_CODE_HIGH);
1254 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1255 'P_EXCISE_TAX_TYPE_CODE_LOW = '||P_EXCISE_TAX_TYPE_CODE_LOW);
1256 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1257 'P_EXCISE_TAX_TYPE_CODE_HIGH = '||P_EXCISE_TAX_TYPE_CODE_HIGH);
1258 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1259 'P_NON_TAXABLE_TAX_TYPE_CODE = '||P_NON_TAXABLE_TAX_TYPE_CODE);
1260 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1261 'P_PERCEPTION_TAX_TYPE_CODE_LOW = '||P_PER_TAX_TYPE_CODE_LOW);
1262 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1263 'P_PERCEPTION_TAX_TYPE_CODE_HIGH = '||P_PER_TAX_TYPE_CODE_HIGH);
1264 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1265 'P_FED_PER_TAX_TYPE_CODE_LOW = '||P_FED_PER_TAX_TYPE_CODE_LOW);
1266 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1267 'P_FED_PER_TAX_TYPE_CODE_HIGH = '||P_FED_PER_TAX_TYPE_CODE_HIGH);
1268 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1269 'P_VAT_TAX_TYPE_CODE = '||P_VAT_TAX_TYPE_CODE);
1270 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1271 'P_EXCISE_TAX = '||P_EXCISE_TAX);
1272 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1273 'P_VAT_ADDITIONAL_TAX = '||P_VAT_ADDITIONAL_TAX);
1274 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1275 'P_VAT_NON_TAXABLE_TAX = '||P_VAT_NON_TAXABLE_TAX);
1276 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1277 'P_VAT_NOT_TAX = '||P_VAT_NOT_TAX);
1278 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1279 'P_VAT_PERCEPTION_TAX = '||P_VAT_PERCEPTION_TAX);
1280 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1281 'P_VAT_TAX = '||P_VAT_TAX);
1282 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1283 'P_INC_SELF_WD_TAX = '||P_INC_SELF_WD_TAX);
1284 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1285 'P_EXCLUDING_TRX_LETTER = '||P_EXCLUDING_TRX_LETTER);
1286 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1287 'P_TRX_LETTER_LOW = '||P_TRX_LETTER_LOW);
1288 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1289 'P_TRX_LETTER_HIGH = '||P_TRX_LETTER_HIGH);
1290 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1291 'P_INCLUDE_REFERENCED_SOURCE = '||P_INCLUDE_REFERENCED_SOURCE);
1292 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1293 'P_PARTY_NAME = '||P_PARTY_NAME);
1294 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1295 'P_BATCH_NAME = '||P_BATCH_NAME);
1296 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1297 'P_BATCH_SOURCE_ID = '||P_BATCH_SOURCE_ID);
1298 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1299 'P_ADJUSTED_DOC_FROM = '||P_ADJUSTED_DOC_FROM);
1300 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1301 'P_ADJUSTED_DOC_TO = '||P_ADJUSTED_DOC_TO);
1302 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1303 'P_STANDARD_VAT_TAX_RATE = '||P_STANDARD_VAT_TAX_RATE);
1304 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1305 'P_MUNICIPAL_TAX = '||P_MUNICIPAL_TAX);
1306 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1307 'P_PROVINCIAL_TAX = '||P_PROVINCIAL_TAX);
1308 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1309 'P_TAX_ACCOUNT_LOW = '||P_TAX_ACCOUNT_LOW);
1310 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1311 'P_TAX_ACCOUNT_HIGH = '||P_TAX_ACCOUNT_HIGH);
1312 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1313 'P_EXP_CERT_DATE_FROM = '||P_EXP_CERT_DATE_FROM);
1314 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1315 'P_EXP_CERT_DATE_TO = '||P_EXP_CERT_DATE_TO);
1316 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1317 'P_EXP_METHOD = '||P_EXP_METHOD);
1318 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1319 'P_TRX_NUMBER_LOW = '||P_TRX_NUMBER_LOW);
1320 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1321 'P_TRX_NUMBER_HIGH = '||P_TRX_NUMBER_HIGH);
1322 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1323 'P_PRINT_COMPANY_INFO = '||P_PRINT_COMPANY_INFO);
1324 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1325 'P_ORDER_BY = '||P_ORDER_BY);
1326 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1327 'P_ERRBUF = '||P_ERRBUF);
1328 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1329 'P_RETCODE = '||P_RETCODE);
1330 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1331 'P_TAXABLE_ACCOUNT_LOW = '||P_TAXABLE_ACCOUNT_LOW);
1332 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1333 'P_TAXABLE_ACCOUNT_HIGH = '||P_TAXABLE_ACCOUNT_HIGH);
1334 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1335 'P_GL_OR_TRX_DATE_FILTER = '||P_GL_OR_TRX_DATE_FILTER); --Bug 5396444
1336 --Bug 9031051
1337 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1338 'P_ESL_DEFAULT_TAX_DATE = '||P_ESL_DEFAULT_TAX_DATE);
1339 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1340 'P_ESL_OUT_OF_PERIOD_ADJ = '||P_ESL_OUT_OF_PERIOD_ADJ);
1341 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1342 'P_ESL_EU_TRX_TYPE = '||P_ESL_EU_TRX_TYPE);
1343 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1344 'P_ESL_EU_GOODS = '||P_ESL_EU_GOODS);
1345 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1346 'P_ESL_EU_SERVICES = '||P_ESL_EU_SERVICES);
1347 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1348 'P_ESL_EU_ADDL_CODE1 = '||P_ESL_EU_ADDL_CODE1);
1349 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1350 'P_ESL_EU_ADDL_CODE2 = '||P_ESL_EU_ADDL_CODE2);
1351 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1352 'P_ESL_SITE_CODE = '||P_ESL_SITE_CODE);
1353 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1354 'P_USE_PARALLEL_HINT = '||P_USE_PARALLEL_HINT);
1355
1356 END IF;
1357
1358 P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL := P_REPORTING_LEVEL;
1359 P_TRL_GLOBAL_VARIABLES_REC.REPORTING_CONTEXT := P_REPORTING_CONTEXT;
1360 -- apai P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_LEVEL := P_LEGAL_ENTITY_LEVEL;
1361 P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID := P_LEGAL_ENTITY_ID;
1362 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL := P_SUMMARY_LEVEL;
1363 P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID := P_LEDGER_ID;
1364 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE := P_REGISTER_TYPE;
1365 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT := P_PRODUCT;
1366 P_TRL_GLOBAL_VARIABLES_REC.MATRIX_REPORT := P_MATRIX_REPORT;
1367 P_TRL_GLOBAL_VARIABLES_REC.CURRENCY_CODE_LOW := P_CURRENCY_CODE_LOW;
1368 P_TRL_GLOBAL_VARIABLES_REC.CURRENCY_CODE_HIGH := P_CURRENCY_CODE_HIGH;
1369 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_STD_TRX_CLASS := P_INCLUDE_AP_STD_TRX_CLASS;
1370 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_DM_TRX_CLASS := P_INCLUDE_AP_DM_TRX_CLASS;
1371 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_CM_TRX_CLASS := P_INCLUDE_AP_CM_TRX_CLASS;
1372 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_PREP_TRX_CLASS := P_INCLUDE_AP_PREP_TRX_CLASS;
1373 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_MIX_TRX_CLASS := P_INCLUDE_AP_MIX_TRX_CLASS;
1374 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_EXP_TRX_CLASS := P_INCLUDE_AP_EXP_TRX_CLASS;
1375 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AP_INT_TRX_CLASS := P_INCLUDE_AP_INT_TRX_CLASS;
1376 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_INV_TRX_CLASS := P_INCLUDE_AR_INV_TRX_CLASS;
1377 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_APPL_TRX_CLASS := P_INCLUDE_AR_APPL_TRX_CLASS;
1378 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_ADJ_TRX_CLASS := P_INCLUDE_AR_ADJ_TRX_CLASS;
1379 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_MISC_TRX_CLASS := P_INCLUDE_AR_MISC_TRX_CLASS;
1380 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_AR_BR_TRX_CLASS := P_INCLUDE_AR_BR_TRX_CLASS;
1381 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_GL_MANUAL_LINES := P_INCLUDE_GL_MANUAL_LINES;
1382 P_TRL_GLOBAL_VARIABLES_REC.THIRD_PARTY_REP_LEVEL := P_THIRD_PARTY_REP_LEVEL;
1383 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM := P_FIRST_PARTY_TAX_REG_NUM;
1384 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_LOW := P_TRX_NUMBER_LOW;
1385 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_HIGH := P_TRX_NUMBER_HIGH;
1386 P_TRL_GLOBAL_VARIABLES_REC.AR_TRX_PRINTING_STATUS := P_AR_TRX_PRINTING_STATUS;
1387 P_TRL_GLOBAL_VARIABLES_REC.AR_EXEMPTION_STATUS := P_AR_EXEMPTION_STATUS;
1388 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW := P_GL_DATE_LOW;
1389 IF P_GL_DATE_HIGH IS NOT NULL THEN
1390 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH := To_Date(To_Char(P_GL_DATE_HIGH,'DD-MM-YYYY')
1391 ||' 23:59:59','DD-MM-YYYY HH24:MI:SS');
1392 ELSE
1393 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH := P_GL_DATE_HIGH;
1394 END IF;
1395 --P_GL_DATE_HIGH;
1396 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_LOW := P_TRX_DATE_LOW;
1397 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH := P_TRX_DATE_HIGH;
1398 P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_LOW := P_GL_PERIOD_NAME_LOW;
1399 P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_HIGH := P_GL_PERIOD_NAME_HIGH;
1400 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_LOW := P_TRX_DATE_PERIOD_NAME_LOW;
1401 P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_HIGH := P_TRX_DATE_PERIOD_NAME_HIGH;
1402 P_TRL_GLOBAL_VARIABLES_REC.TAX_JURISDICTION_CODE := P_TAX_JURISDICTION_CODE;
1403 P_TRL_GLOBAL_VARIABLES_REC.TAX_REGIME_CODE := P_TAX_REGIME_CODE;
1404 P_TRL_GLOBAL_VARIABLES_REC.TAX := P_TAX;
1405 P_TRL_GLOBAL_VARIABLES_REC.TAX_STATUS_CODE := P_TAX_STATUS_CODE;
1406 -- P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_LOW := P_TAX_RATE_CODE_LOW;
1407 -- P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_HIGH := P_TAX_RATE_CODE_HIGH;
1408 P_TRL_GLOBAL_VARIABLES_REC.TAX_TYPE_CODE_LOW := P_TAX_TYPE_CODE_LOW;
1409 P_TRL_GLOBAL_VARIABLES_REC.TAX_TYPE_CODE_HIGH := P_TAX_TYPE_CODE_HIGH;
1410 P_TRL_GLOBAL_VARIABLES_REC.DOCUMENT_SUB_TYPE := P_DOCUMENT_SUB_TYPE;
1411 P_TRL_GLOBAL_VARIABLES_REC.TRX_BUSINESS_CATEGORY := P_TRX_BUSINESS_CATEGORY;
1412 P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_LOW := P_TAX_INVOICE_DATE_LOW;
1413 P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH := P_TAX_INVOICE_DATE_HIGH;
1414 P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS := P_POSTING_STATUS;
1415 P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_ACCTED_TAX_LINES := P_EXTRACT_ACCTED_TAX_LINES;
1416 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_ACCOUNTING_SEGMENTS := P_INCLUDE_ACCOUNTING_SEGMENTS;
1417 P_TRL_GLOBAL_VARIABLES_REC.BALANCING_SEGMENT_LOW := P_BALANCING_SEGMENT_LOW;
1418 P_TRL_GLOBAL_VARIABLES_REC.BALANCING_SEGMENT_HIGH := P_BALANCING_SEGMENT_HIGH;
1419 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_DISCOUNTS := P_INCLUDE_DISCOUNTS;
1420 P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_STARTING_LINE_NUM := P_EXTRACT_STARTING_LINE_NUM;
1421 P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID := P_REQUEST_ID;
1422 P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME := P_REPORT_NAME;
1423 P_TRL_GLOBAL_VARIABLES_REC.VAT_TRANSACTION_TYPE_CODE := P_VAT_TRANSACTION_TYPE_CODE;
1424 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_FULLY_NR_TAX_FLAG := P_INCLUDE_FULLY_NR_TAX_FLAG;
1425 P_TRL_GLOBAL_VARIABLES_REC.MUNICIPAL_TAX_TYPE_CODE_LOW := P_MUNICIPAL_TAX_TYPE_CODE_LOW;
1426 P_TRL_GLOBAL_VARIABLES_REC.MUNICIPAL_TAX_TYPE_CODE_HIGH := P_MUNICIPAL_TAX_TYPE_CODE_HIGH;
1427 P_TRL_GLOBAL_VARIABLES_REC.PROV_TAX_TYPE_CODE_LOW := P_PROV_TAX_TYPE_CODE_LOW;
1428 P_TRL_GLOBAL_VARIABLES_REC.PROV_TAX_TYPE_CODE_HIGH := P_PROV_TAX_TYPE_CODE_HIGH;
1429 P_TRL_GLOBAL_VARIABLES_REC.EXCISE_TAX_TYPE_CODE_LOW := P_EXCISE_TAX_TYPE_CODE_LOW;
1430 P_TRL_GLOBAL_VARIABLES_REC.EXCISE_TAX_TYPE_CODE_HIGH := P_EXCISE_TAX_TYPE_CODE_HIGH;
1431 P_TRL_GLOBAL_VARIABLES_REC.NON_TAXABLE_TAX_TYPE_CODE := P_NON_TAXABLE_TAX_TYPE_CODE;
1432 P_TRL_GLOBAL_VARIABLES_REC.PER_TAX_TYPE_CODE_LOW := P_PER_TAX_TYPE_CODE_LOW;
1433 P_TRL_GLOBAL_VARIABLES_REC.PER_TAX_TYPE_CODE_HIGH := P_PER_TAX_TYPE_CODE_HIGH;
1434 P_TRL_GLOBAL_VARIABLES_REC.VAT_TAX_TYPE_CODE := P_VAT_TAX_TYPE_CODE;
1435 P_TRL_GLOBAL_VARIABLES_REC.EXCISE_TAX := P_EXCISE_TAX;
1436 P_TRL_GLOBAL_VARIABLES_REC.VAT_ADDITIONAL_TAX := P_VAT_ADDITIONAL_TAX;
1437 P_TRL_GLOBAL_VARIABLES_REC.VAT_NON_TAXABLE_TAX := P_VAT_NON_TAXABLE_TAX;
1438 P_TRL_GLOBAL_VARIABLES_REC.VAT_NOT_TAX := P_VAT_NOT_TAX;
1439 P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX := P_VAT_PERCEPTION_TAX;
1440 P_TRL_GLOBAL_VARIABLES_REC.VAT_TAX := P_VAT_TAX;
1441 P_TRL_GLOBAL_VARIABLES_REC.INC_SELF_WD_TAX := P_INC_SELF_WD_TAX;
1442 P_TRL_GLOBAL_VARIABLES_REC.EXCLUDING_TRX_LETTER := P_EXCLUDING_TRX_LETTER;
1443 P_TRL_GLOBAL_VARIABLES_REC.TRX_LETTER_LOW := P_TRX_LETTER_LOW;
1444 P_TRL_GLOBAL_VARIABLES_REC.TRX_LETTER_HIGH := P_TRX_LETTER_HIGH;
1445 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_REFERENCED_SOURCE := P_INCLUDE_REFERENCED_SOURCE;
1446 P_TRL_GLOBAL_VARIABLES_REC.PARTY_NAME := P_PARTY_NAME;
1447 P_TRL_GLOBAL_VARIABLES_REC.BATCH_NAME := P_BATCH_NAME;
1448 P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID := P_BATCH_SOURCE_ID;
1449 P_TRL_GLOBAL_VARIABLES_REC.ADJUSTED_DOC_FROM := P_ADJUSTED_DOC_FROM;
1450 P_TRL_GLOBAL_VARIABLES_REC.ADJUSTED_DOC_TO := P_ADJUSTED_DOC_TO;
1451 P_TRL_GLOBAL_VARIABLES_REC.STANDARD_VAT_TAX_RATE := P_STANDARD_VAT_TAX_RATE;
1452 P_TRL_GLOBAL_VARIABLES_REC.MUNICIPAL_TAX := P_MUNICIPAL_TAX;
1453 P_TRL_GLOBAL_VARIABLES_REC.PROVINCIAL_TAX := P_PROVINCIAL_TAX;
1454 P_TRL_GLOBAL_VARIABLES_REC.TAX_ACCOUNT_LOW := P_TAX_ACCOUNT_LOW;
1455 P_TRL_GLOBAL_VARIABLES_REC.TAX_ACCOUNT_HIGH := P_TAX_ACCOUNT_HIGH;
1456 P_TRL_GLOBAL_VARIABLES_REC.EXP_CERT_DATE_FROM := P_EXP_CERT_DATE_FROM;
1457 P_TRL_GLOBAL_VARIABLES_REC.EXP_CERT_DATE_TO := P_EXP_CERT_DATE_TO;
1458 P_TRL_GLOBAL_VARIABLES_REC.EXP_METHOD := P_EXP_METHOD;
1459 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_LOW := P_TRX_NUMBER_LOW;
1460 P_TRL_GLOBAL_VARIABLES_REC.TRX_NUMBER_HIGH := P_TRX_NUMBER_HIGH;
1461 P_TRL_GLOBAL_VARIABLES_REC.PRINT_COMPANY_INFO := P_PRINT_COMPANY_INFO;
1462 P_TRL_GLOBAL_VARIABLES_REC.ERRBUF := P_ERRBUF;
1463 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := NVL(P_RETCODE,0);
1464 P_TRL_GLOBAL_VARIABLES_REC.ACCOUNTING_STATUS := P_ACCOUNTING_STATUS;
1465 P_TRL_GLOBAL_VARIABLES_REC.REPORTED_STATUS := P_REPORTED_STATUS;
1466 P_TRL_GLOBAL_VARIABLES_REC.TAXABLE_ACCOUNT_LOW := P_TAXABLE_ACCOUNT_LOW;
1467 P_TRL_GLOBAL_VARIABLES_REC.TAXABLE_ACCOUNT_HIGH := P_TAXABLE_ACCOUNT_HIGH;
1468 P_TRL_GLOBAL_VARIABLES_REC.GL_OR_TRX_DATE_FILTER := P_GL_OR_TRX_DATE_FILTER; --Bug 5396444
1469 --Bug 9031051
1470 P_TRL_GLOBAL_VARIABLES_REC.ESL_DEFAULT_TAX_DATE := P_ESL_DEFAULT_TAX_DATE;
1471 P_TRL_GLOBAL_VARIABLES_REC.ESL_OUT_OF_PERIOD_ADJ := P_ESL_OUT_OF_PERIOD_ADJ;
1472 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_TRX_TYPE := P_ESL_EU_TRX_TYPE;
1473 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_GOODS := P_ESL_EU_GOODS;
1474 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SERVICES := P_ESL_EU_SERVICES;
1475 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE1 := P_ESL_EU_ADDL_CODE1;
1476 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_ADDL_CODE2 := P_ESL_EU_ADDL_CODE2;
1477 P_TRL_GLOBAL_VARIABLES_REC.ESL_EU_SITE_REPORTED := P_ESL_SITE_CODE;
1478 P_TRL_GLOBAL_VARIABLES_REC.USE_PARALLEL_HINT := P_USE_PARALLEL_HINT; -- Bug 12838309
1479
1480 -- Bug 10430098
1481 -- Populate TAX_RATE_CODE_LOW and TAX_RATE_CODE_HIGH Columns
1482
1483 IF ( (P_TAX_RATE_CODE_LOW IS NOT NULL OR P_TAX_RATE_CODE_HIGH IS NOT NULL) AND P_TAX_JURISDICTION_CODE IS NOT NULL ) THEN
1484
1485 SELECT Nvl(MIN(TAX_RATE_CODE),P_TAX_RATE_CODE_LOW), Nvl(MAX(TAX_RATE_CODE),P_TAX_RATE_CODE_HIGH)
1486 INTO P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_LOW, P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_HIGH
1487 FROM ZX_RATES_B
1488 WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE
1489 AND TAX = P_TAX
1490 AND TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE
1491 AND TAX_STATUS_CODE = P_TAX_STATUS_CODE
1492 AND tax_rate_code BETWEEN P_TAX_RATE_CODE_LOW AND P_TAX_RATE_CODE_HIGH
1493 AND (P_TAX_RATE_CODE_LOW IN
1494 (SELECT tax_rate_code
1495 FROM ZX_RATES_B
1496 WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE
1497 AND TAX = P_TAX
1498 AND TAX_STATUS_CODE = P_TAX_STATUS_CODE
1499 AND TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE)
1500 OR P_TAX_RATE_CODE_HIGH IN
1501 (SELECT tax_rate_code
1502 FROM ZX_RATES_B
1503 WHERE TAX_REGIME_CODE = P_TAX_REGIME_CODE
1504 AND TAX = P_TAX
1505 AND TAX_STATUS_CODE = P_TAX_STATUS_CODE
1506 AND TAX_JURISDICTION_CODE = P_TAX_JURISDICTION_CODE)
1507 );
1508 ELSE
1509 P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_LOW:=P_TAX_RATE_CODE_LOW;
1510 P_TRL_GLOBAL_VARIABLES_REC.TAX_RATE_CODE_HIGH:=P_TAX_RATE_CODE_HIGH;
1511 END IF;
1512
1513 -- Populate the WHO columns :
1514
1515 g_created_by := nvl(fnd_profile.value('USER_ID'),1);
1516 g_creation_date := sysdate;
1517 g_last_updated_by := nvl(fnd_profile.value('USER_ID'),1);
1518 g_last_update_date := sysdate;
1519 g_last_update_login := 1;
1520
1521 -- Get the max length of P_ERRBUF, rounded down to 50 characters
1522 -- Since it is not possible to know the max length of the variable
1523 -- which was passed to the TRL as IN OUT parameter P_ERRBUF, we need to
1524 -- do this workaround. Otherwise we get Value error if the size
1525 -- of the variable is not sufficient to hold value in L_ERRBUF.
1526 l_length_errbuf := 0;
1527 BEGIN
1528 FOR i IN 1..40 LOOP
1529 p_errbuf := p_errbuf ||
1530 ' ';
1531 l_length_errbuf := l_length_errbuf + 50;
1532 END LOOP;
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 NULL;
1536 END;
1537 l_length_errbuf := least(l_length_errbuf,2000);
1538 IF (g_level_procedure >= g_current_runtime_level ) THEN
1539 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1540 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE(-)');
1541 END IF;
1542 P_ERRBUF := NULL;
1543
1544
1545 IF (g_level_procedure >= g_current_runtime_level ) THEN
1546 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE.END',
1547 'Length of errbuf : '||to_char(l_length_errbuf));
1548 END IF;
1549
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1553 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1554 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','initialize- '|| g_error_buffer);
1555 FND_MSG_PUB.Add;
1556 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1557 FND_LOG.STRING(g_level_unexpected,
1558 'ZX.TRL.ZX_EXTRACT_PKG.initialize',
1559 'initialize Check : ');
1560 FND_LOG.STRING(g_level_unexpected,
1561 'ZX.TRL.ZX_EXTRACT_PKG.initialize', g_error_buffer);
1562 END IF;
1563 APPEND_ERRBUF(g_error_buffer);
1564 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1565
1566 END initialize;
1567
1568 /*===========================================================================+
1569 | PROCEDURE |
1570 | derive_dependent_parameters |
1571 | |
1572 | DESCRIPTION |
1573 | This procedure derives the dependent parameters |
1574 | for procedure ZX_EXTRACT_PKG.POPULATE |
1575 | |
1576 | Called from ZX_EXTRACT_PKG.POPULATE |
1577 | |
1578 | SCOPE - Private |
1579 | |
1580 +===========================================================================*/
1581
1582 PROCEDURE derive_dependent_parameters (
1583 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1584 IS
1585 l_operating_unit_id NUMBER;
1586 l_ledger_name GL_LEDGERS.Name%TYPE;
1587 l_primary_ledger_id NUMBER(15);
1588 l_legal_entity_id NUMBER;
1589 l_ledger_category gl_ledgers.ledger_category_code%TYPE;
1590
1591
1592 CURSOR le_ledger_cur (c_legal_entity_id NUMBER) IS
1593 SELECT ledger_id
1594 FROM gl_ledger_le_v
1595 WHERE legal_entity_id = c_legal_entity_id
1596 AND ledger_category_code = 'PRIMARY';
1597
1598 CURSOR chart_of_acc_id (c_ledger_id number) IS
1599 SELECT chart_of_accounts_id, name, currency_code
1600 FROM gl_sets_of_books
1601 WHERE set_of_books_id = c_ledger_id;
1602
1603 BEGIN
1604
1605 IF (g_level_procedure >= g_current_runtime_level ) THEN
1606 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters.BEGIN',
1607 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters(+)');
1608 END IF;
1609
1610 IF p_trl_global_variables_rec.reporting_level = '1000' THEN
1611 g_ledger_id := p_trl_global_variables_rec.reporting_context;
1612 l_legal_entity_id := p_trl_global_variables_rec.legal_entity_id;
1613 IF (g_level_procedure >= g_current_runtime_level ) THEN
1614 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 1000',
1615 'g_ledger_id : ' || to_char(g_ledger_id) ||
1616 'l_legal_entity_id : ' || to_char(l_legal_entity_id));
1617 END IF;
1618 ELSIF p_trl_global_variables_rec.reporting_level = '2000' THEN
1619 l_legal_entity_id := p_trl_global_variables_rec.reporting_context;
1620 p_trl_global_variables_rec.legal_entity_id := l_legal_entity_id;
1621 IF (g_level_procedure >= g_current_runtime_level ) THEN
1622 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 2000',
1623 'l_legal_entity_id : ' || to_char(l_legal_entity_id));
1624 END IF;
1625
1626 ELSIF p_trl_global_variables_rec.reporting_level = '3000' THEN
1627 l_operating_unit_id := p_trl_global_variables_rec.reporting_context;
1628 l_legal_entity_id := XLE_UTILITIES_GRP.GET_DefaultLegalContext_OU(l_operating_unit_id);
1629 p_trl_global_variables_rec.legal_entity_id := l_legal_entity_id;
1630
1631 IF (g_level_procedure >= g_current_runtime_level ) THEN
1632 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 3000',
1633 'l_operating_unit_id : ' || to_char(l_operating_unit_id) ||
1634 'l_legal_entity_id : ' || to_char(l_legal_entity_id));
1635 END IF;
1636
1637 IF p_trl_global_variables_rec.ledger_id IS NOT NULL THEN
1638 g_ledger_id := p_trl_global_variables_rec.ledger_id;
1639 ELSE
1640 mo_utils.get_ledger_info(l_operating_unit_id,
1641 g_ledger_id ,
1642 l_ledger_name );
1643 END IF;
1644 IF (g_level_procedure >= g_current_runtime_level ) THEN
1645 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters 3000.2',
1646 'l_operating_unit_id : ' || to_char(l_operating_unit_id) ||
1647 'g_ledger_id : ' || to_char(g_ledger_id) ||
1648 'l_ledger_name : ' || to_char(l_ledger_name));
1649 END IF;
1650
1651 END IF;
1652
1653 IF P_TRL_GLOBAL_VARIABLES_REC.POSTING_STATUS IN ('POSTED','TRANSFERRED') THEN
1654 p_trl_global_variables_rec.accounting_status := 'ACCOUNTED';
1655 IF (g_level_procedure >= g_current_runtime_level ) THEN
1656 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.INITIALIZE',
1657 'p_trl_global_variables_rec.accounting_status = '||p_trl_global_variables_rec.accounting_status);
1658 END IF;
1659 END IF;
1660
1661
1662 IF p_trl_global_variables_rec.reporting_level = '2000' THEN
1663 -- IF p_trl_global_variables_rec.legal_entity_id is not NULL THEN
1664 OPEN le_ledger_cur (l_legal_entity_id);
1665 FETCH le_ledger_cur INTO g_ledger_id;
1666 CLOSE le_ledger_cur;
1667 -- END IF;
1668 END IF;
1669
1670 --Get the Ledger Type for a given Ledger ID.
1671 --Get the primary ledger ID if the ledger type is reprting.
1672
1673 -- Reporting Ledger ----- Secondary Leders --
1674 IF nvl(p_trl_global_variables_rec.ledger_id,g_ledger_id) IS NOT NULL THEN
1675 l_ledger_category := gl_mc_info.get_ledger_category(nvl(p_trl_global_variables_rec.ledger_id,
1676 g_ledger_id));
1677 IF l_ledger_category <> 'PRIMARY' THEN
1678 gl_mc_info.get_sob_type(nvl(p_trl_global_variables_rec.ledger_id,g_ledger_id),
1679 g_ledger_type);
1680 IF NVL(g_ledger_type,'R') IN ('R','N') THEN
1681 p_trl_global_variables_rec.reporting_ledger_id := nvl(p_trl_global_variables_rec.ledger_id,g_ledger_id);
1682 p_trl_global_variables_rec.accounting_status := 'ACCOUNTED';
1683 IF (g_level_procedure >= g_current_runtime_level ) THEN
1684 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1685 'g_ledger_type and Reporting Ledger = '||g_ledger_type||'-'
1686 ||to_char(p_trl_global_variables_rec.reporting_ledger_id));
1687 END IF;
1688 END IF;
1689 END IF;
1690 END IF;
1691
1692 IF (g_level_procedure >= g_current_runtime_level ) THEN
1693 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1694 'Accounting_status = '||p_trl_global_variables_rec.accounting_status);
1695 END IF;
1696
1697
1698 IF nvl(g_ledger_type,'R') IN ('R','N') THEN
1699 -- g_ledger_type := 'P';
1700 -- ELSE
1701 -- p_trl_global_variables_rec.reporting_ledger_id := g_ledger_id;
1702 l_primary_ledger_id := gl_mc_info.get_primary_ledger_id(g_ledger_id);
1703 g_ledger_id := l_primary_ledger_id;
1704 IF p_trl_global_variables_rec.reporting_level = '1000' THEN
1705 p_trl_global_variables_rec.reporting_context := g_ledger_id;
1706 END IF;
1707 END IF;
1708
1709 p_trl_global_variables_rec.ledger_id := g_ledger_id;
1710
1711 IF (g_level_procedure >= g_current_runtime_level ) THEN
1712 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1713 'g_ledger_type = '||g_ledger_type||'-'||to_char(g_ledger_id));
1714 END IF;
1715
1716 IF l_ledger_category <> 'PRIMARY' THEN
1717 -- for Ledger Categories 'ALC'(reporting) ,'SECONDARY', 'NONE'(Ledger Set)
1718 OPEN chart_of_acc_id (p_trl_global_variables_rec.reporting_ledger_id);
1719 FETCH chart_of_acc_id
1720 INTO
1721 P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
1722 P_TRL_GLOBAL_VARIABLES_REC.ledger_name ,
1723 P_TRL_GLOBAL_VARIABLES_REC.func_currency_code;
1724 CLOSE chart_of_acc_id;
1725 ELSE
1726 OPEN chart_of_acc_id (p_trl_global_variables_rec.ledger_id);
1727 FETCH chart_of_acc_id
1728 INTO
1729 P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
1730 P_TRL_GLOBAL_VARIABLES_REC.ledger_name ,
1731 P_TRL_GLOBAL_VARIABLES_REC.func_currency_code;
1732 CLOSE chart_of_acc_id;
1733 END IF;
1734
1735 IF (g_level_procedure >= g_current_runtime_level ) THEN
1736 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1737 'Ledger Category =' ||l_ledger_category);
1738 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1739 'Chart of Accounts ID =' ||to_char(P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id));
1740 END IF;
1741
1742 --derive P_GL_DATE_LOW/HIGH from P_GL_PERIOD_NAME_LOW/HIGH
1743
1744 BEGIN
1745
1746 IF P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_LOW IS NOT NULL AND
1747 P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_LOW IS NULL THEN
1748
1749 SELECT start_date
1750 INTO p_trl_global_variables_rec.gl_date_low
1751 FROM gl_period_statuses
1752 WHERE upper(period_name) = upper(p_trl_global_variables_rec.gl_period_name_low)
1753 AND set_of_books_id = g_ledger_id
1754 AND application_id = 101;
1755
1756 END IF;
1757
1758 IF (g_level_procedure >= g_current_runtime_level ) THEN
1759 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1760 ' p_trl_global_variables_rec.gl_period_name_low =' ||to_char(p_trl_global_variables_rec.gl_date_low));
1761 END IF;
1762
1763
1764 IF P_TRL_GLOBAL_VARIABLES_REC.GL_PERIOD_NAME_HIGH IS NOT NULL
1765 AND P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH IS NULL THEN
1766
1767 SELECT To_Date(To_Char(end_date,'DD-MM-YYYY')||' 23:59:59','DD-MM-YYYY HH24:MI:SS')
1768 INTO p_trl_global_variables_rec.gl_date_high
1769 FROM gl_period_statuses
1770 WHERE upper(period_name) = upper(p_trl_global_variables_rec.gl_period_name_high)
1771 AND set_of_books_id = g_ledger_id
1772 AND application_id = 101;
1773
1774 END IF;
1775
1776 IF (g_level_procedure >= g_current_runtime_level ) THEN
1777 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1778 ' p_trl_global_variables_rec.gl_period_name_high ='
1779 ||to_char(p_trl_global_variables_rec.gl_date_high,'DD-MON-YYYY HH24:MI:SS'));
1780 END IF;
1781
1782 EXCEPTION
1783 WHEN OTHERS THEN
1784 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1785 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1786 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1787 FND_MSG_PUB.Add;
1788 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1789 FND_LOG.STRING(g_level_unexpected,
1790 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1791 'Period Name Low and High parameter has error :');
1792 FND_LOG.STRING(g_level_unexpected,
1793 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1794 g_error_buffer);
1795 END IF;
1796 APPEND_ERRBUF(g_error_buffer);
1797 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1798
1799 END;
1800
1801
1802 -- derive P_TRX_DATE_LOW/HIGH from P_TRX_DATE_PERIOD_NAME_LOW / HIGH
1803
1804 BEGIN
1805 IF P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_LOW IS NOT NULL
1806 AND P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_LOW IS NULL THEN
1807
1808 SELECT start_date
1809 INTO p_trl_global_variables_rec.trx_date_low
1810 FROM gl_period_statuses
1811 WHERE period_name = p_trl_global_variables_rec.trx_date_period_name_low
1812 AND set_of_books_id = g_ledger_id
1813 AND application_id = 101;
1814
1815 END IF;
1816
1817 IF P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_PERIOD_NAME_HIGH IS NOT NULL
1818 AND P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH IS NULL THEN
1819
1820 SELECT end_date
1821 INTO p_trl_global_variables_rec.trx_date_high
1822 FROM gl_period_statuses
1823 WHERE period_name = p_trl_global_variables_rec.trx_date_period_name_high
1824 AND set_of_books_id = g_ledger_id
1825 AND application_id = 101;
1826
1827 END IF;
1828
1829 EXCEPTION
1830 WHEN OTHERS THEN
1831 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1832 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1833 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1834 FND_MSG_PUB.Add;
1835 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1836 FND_LOG.STRING(g_level_unexpected,
1837 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1838 'Transaction Date Low and High parameter has error :');
1839 FND_LOG.STRING(g_level_unexpected,
1840 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1841 g_error_buffer);
1842 END IF;
1843 APPEND_ERRBUF(g_error_buffer);
1844 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1845 END;
1846
1847 IF (g_level_procedure >= g_current_runtime_level ) THEN
1848 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters.END',
1849 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters(-)');
1850 END IF;
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1855 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1856 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','derive_dependent_parameters- '|| g_error_buffer);
1857 FND_MSG_PUB.Add;
1858 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1859 FND_LOG.STRING(g_level_unexpected,
1860 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1861 'Multi Org Utility : mo_utils.get_ledger_info :');
1862 FND_LOG.STRING(g_level_unexpected,
1863 'ZX.TRL.ZX_EXTRACT_PKG.derive_dependent_parameters',
1864 g_error_buffer);
1865 END IF;
1866 APPEND_ERRBUF(g_error_buffer);
1867 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1868 END derive_dependent_parameters;
1869
1870 /*===========================================================================+
1871 | PROCEDURE |
1872 | VALIDATE_PARAMETERS |
1873 | |
1874 | DESCRIPTION |
1875 | This procedure validates the parameters supplied by the user in the |
1876 | ZX_EXTRACT_PKG.POPULATE and gives error message if he parameter |
1877 | values passed are invalid. |
1878 | |
1879 | Called from ZX_EXTRACT_PKG.POPULATE |
1880 | |
1881 | SCOPE - Private |
1882 | |
1883 | NOTES |
1884 | |
1885 | MODIFICATION HISTORY |
1886 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
1887 | |
1888 +===========================================================================*/
1889
1890 FUNCTION VALIDATE_PARAMETERS (
1891 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
1892 RETURN BOOLEAN IS
1893
1894 C_ORDER_BY VARCHAR2(30);
1895 l_count NUMBER;
1896
1897 BEGIN
1898 -- Validation of Parameters:
1899
1900 SELECT count(*) INTO l_count
1901 FROM zx_rep_context_t
1902 WHERE request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
1903
1904 IF l_count > 0 THEN
1905 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1906 FND_LOG.STRING(g_level_unexpected,
1907 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1908 'Duplicate request ID :');
1909 FND_LOG.STRING(g_level_unexpected,
1910 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1911 g_error_buffer);
1912 END IF;
1913
1914 END IF;
1915
1916 RETURN TRUE;
1917
1918 EXCEPTION
1919 WHEN OTHERS THEN
1920 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1921 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1922 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','derive_dependent_parameters- '|| g_error_buffer);
1923 FND_MSG_PUB.Add;
1924 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1925 FND_LOG.STRING(g_level_unexpected,
1926 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1927 'Request ID Duplicate check :');
1928 FND_LOG.STRING(g_level_unexpected,
1929 'ZX.TRL.ZX_EXTRACT_PKG.validate_parameters',
1930 g_error_buffer);
1931 END IF;
1932 APPEND_ERRBUF(g_error_buffer);
1933 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
1934
1935 END VALIDATE_PARAMETERS;
1936
1937
1938 /*===========================================================================+
1939 | PROCEDURE |
1940 | extract_rep_context_info |
1941 | |
1942 | description |
1943 | this PROCEDURE gets the reporting context for the header record |
1944 | called FROM the constructor of zx_extract_pkg |
1945 | |
1946 | scope - private |
1947 | |
1948 | notes |
1949 | |
1950 | |
1951 +===========================================================================*/
1952
1953 PROCEDURE extract_rep_context_info (
1954 p_trl_global_variables_rec IN OUT NOCOPY ZX_EXTRACT_PKG.trl_global_variables_rec_type)
1955 IS
1956 l_operating_unit_id NUMBER;
1957 l_reporting_context_org_id NUMBER(15);
1958 l_reporting_context_tax_reg_no VARCHAR2(60);
1959 l_reporting_context_name VARCHAR2(100);
1960 l_reporting_sob_name VARCHAR2(100);
1961 l_functional_currency_code VARCHAR2(15);
1962 l_ledger_id NUMBER;
1963 l_legal_entity_id NUMBER;
1964
1965 /* apai
1966 CURSOR ledger_ou_cursor (c_ledger_id NUMBER ) IS
1967 SELECT organization_id
1968 FROM hr_operating_units
1969 WHERE mo_global.check_access(organization_id) = 'Y'
1970 AND SET_OF_BOOKS_ID = c_ledger_id ;
1971 */
1972 BEGIN
1973
1974 IF (g_level_procedure >= g_current_runtime_level ) THEN
1975 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.BEGIN',
1976 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info(+)');
1977 END IF;
1978
1979 -- get the structure of address location dff
1980
1981 fnd_dflex.get_flexfield(
1982 'PER',
1983 'Address Location',
1984 pr_flexfield,
1985 pr_flexinfo);
1986
1987 IF (g_level_procedure >= g_current_runtime_level ) THEN
1988 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.BEGIN',
1989 'pr_flexfield :');
1990 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
1991 'p_trl_global_variables_rec.legal_entity_id : p_trl_global_variables_rec.legal_entity_id');
1992
1993 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
1994 'Before call to insert_rep_context_itf');
1995
1996 END IF;
1997
1998 --IF p_trl_global_variables_rec.legal_entity_id IS NOT NULL THEN
1999 insert_rep_context_itf(
2000 p_trl_global_variables_rec,
2001 p_trl_global_variables_rec.legal_entity_id);
2002 --END IF;
2003
2004 /* apai
2005 ELSIF p_trl_global_variables_rec.reporting_level = '1000' THEN
2006
2007 IF (g_level_procedure >= g_current_runtime_level ) THEN
2008 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.BEGIN',
2009 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info : Call to insert_rep_context_itf');
2010 END IF;
2011
2012 BEGIN
2013 -- IF g_ledger_type = 'R' THEN
2014 -- l_ledger_id := gl_mc_info.get_primary_ledger_id(g_ledger_id);
2015 -- ELSE
2016 -- l_ledger_id := p_trl_global_variables_rec.reporting_context;
2017 -- END IF;
2018
2019 -- Above coditions are taken care in the derive dependent parameters API.
2020 -- This API always populates primary ledger ID in g_ledger_id which is a
2021 -- global variable.
2022 OPEN ledger_ou_cursor(g_ledger_id );
2023 BEGIN
2024 LOOP
2025 FETCH ledger_ou_cursor INTO l_operating_unit_id;
2026
2027 IF ledger_ou_cursor%NOTFOUND THEN
2028 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2029 -- FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2030 -- FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2031 -- FND_MSG_PUB.Add;
2032 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2033 FND_LOG.STRING(g_level_unexpected,
2034 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
2035 g_error_buffer);
2036 END IF;
2037
2038 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2039 END IF;
2040 EXIT when ledger_ou_cursor%NOTFOUND ;
2041 insert_rep_context_itf(p_trl_global_variables_rec,l_operating_unit_id);
2042 END LOOP;
2043 END;
2044
2045 IF (ledger_ou_cursor%ISOPEN) THEN
2046 CLOSE ledger_ou_cursor;
2047 END IF;
2048 END;
2049
2050 ELSIF p_trl_global_variables_rec.reporting_level = '3000' THEN
2051 l_operating_unit_id := p_trl_global_variables_rec.reporting_context;
2052 insert_rep_context_itf(
2053 p_trl_global_variables_rec,
2054 l_operating_unit_id);
2055 END IF; -- reporting level
2056 */
2057 IF (g_level_procedure >= g_current_runtime_level ) THEN
2058 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info.END',
2059 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info(-)');
2060 END IF;
2061
2062 EXCEPTION
2063 WHEN OTHERS THEN
2064 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2065 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2066 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2067 FND_MSG_PUB.Add;
2068 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2069 FND_LOG.STRING(g_level_unexpected,
2070 'ZX.TRL.ZX_EXTRACT_PKG.extract_rep_context_info',
2071 g_error_buffer);
2072 END IF;
2073 APPEND_ERRBUF(g_error_buffer);
2074 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2075
2076
2077 END extract_rep_context_info ;
2078
2079 /*===========================================================================+
2080 | PROCEDURE |
2081 | extract_tax_info |
2082 | |
2083 | DESCRIPTION |
2084 | Called from ZX_EXTRACT_PKG.populate_tax_data |
2085 | |
2086 | SCOPE - Private |
2087 | |
2088 | NOTES |
2089 | |
2090 | MODIFICATION HISTORY |
2091 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
2092 +===========================================================================*/
2093
2094 PROCEDURE EXTRACT_TAX_INFO(
2095 p_ledger_type IN VARCHAR2,
2096 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
2097 IS
2098
2099 BEGIN
2100
2101 IF (g_level_procedure >= g_current_runtime_level ) THEN
2102 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.EXTRACT_TAX_INFO.BEGIN',
2103 'ZX_EXTRACT_PKG:EXTRACT_TAX_INFO(+)');
2104 END IF;
2105 -- Need to remove this code since the accounting and non accounting apis are merged
2106 /*
2107 IF P_TRL_GLOBAL_VARIABLES_REC.EXTRACT_ACCTED_TAX_LINES = 'Y' OR
2108 P_ledger_type = 'R' THEN
2109
2110 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AR' OR
2111 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
2112
2113 ZX_AR_ACTG_EXTRACT_PKG.insert_tax_data (
2114 P_MRC_SOB_TYPE => P_ledger_type,
2115 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
2116 );
2117
2118 l_ar_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2119
2120 ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines(
2121 P_TRL_GLOBAL_VARIABLES_REC);
2122 ZX_JL_EXTRACT_PKG.FILTER_JL_AR_TAX_LINES(P_TRL_GLOBAL_VARIABLES_REC);
2123 END IF;
2124
2125 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
2126 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
2127
2128 ZX_AP_ACTG_EXTRACT_PKG.insert_tax_data (
2129 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
2130 );
2131 l_ap_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2132 ZX_JA_EXTRACT_PKG.filter_ja_ap_tax_lines(
2133 P_TRL_GLOBAL_VARIABLES_REC);
2134 END IF;
2135
2136
2137 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
2138 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
2139
2140 ZX_GL_EXTRACT_PKG.insert_tax_data(
2141 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
2142 );
2143 l_gl_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2144 END IF;
2145
2146 ELSE
2147 */ -- Need to remove this code since the accounting and non accounting apis are merged
2148 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AR' OR
2149 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
2150
2151 ZX_AR_EXTRACT_PKG.insert_tax_data (
2152 P_MRC_SOB_TYPE => P_ledger_type,
2153 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
2154 );
2155 l_ar_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2156 IF l_ar_retcode <> 2 THEN
2157 ZX_JA_EXTRACT_PKG.filter_ja_ar_tax_lines(
2158 P_TRL_GLOBAL_VARIABLES_REC);
2159 ZX_JL_EXTRACT_PKG.FILTER_JL_AR_TAX_LINES(P_TRL_GLOBAL_VARIABLES_REC);
2160 END IF;
2161 END IF;
2162
2163 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
2164 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
2165
2166 ZX_AP_EXTRACT_PKG.insert_tax_data (
2167 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
2168 );
2169 l_ap_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2170 IF l_ap_retcode <> 2 THEN
2171 ZX_JA_EXTRACT_PKG.filter_ja_ap_tax_lines(
2172 P_TRL_GLOBAL_VARIABLES_REC);
2173 END IF;
2174 END IF;
2175
2176
2177 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
2178 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL' THEN
2179
2180 ZX_GL_EXTRACT_PKG.insert_tax_data(
2181 P_TRL_GLOBAL_VARIABLES_REC => P_TRL_GLOBAL_VARIABLES_REC
2182 );
2183 l_gl_retcode := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2184 END IF;
2185 -- END IF;
2186
2187
2188
2189 IF (g_level_procedure >= g_current_runtime_level ) THEN
2190 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_tax_info.END',
2191 'ZX_EXTRACT_PKG:extract_tax_info(-)');
2192 END IF;
2193
2194 EXCEPTION
2195 WHEN OTHERS THEN
2196 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2197 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2198 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2199 FND_MSG_PUB.Add;
2200 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2201 FND_LOG.STRING(g_level_unexpected,
2202 'ZX.TRL.ZX_EXTRACT_PKG.extract_tax_info',
2203 g_error_buffer);
2204 END IF;
2205 APPEND_ERRBUF(g_error_buffer);
2206 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2207
2208 END EXTRACT_TAX_INFO;
2209
2210 /*===========================================================================+
2211 | PROCEDURE |
2212 | extract_additional_info |
2213 | |
2214 | DESCRIPTION |
2215 | This procedure populates Additional information in |
2216 | zx_rep_context_t |
2217 | |
2218 | Called from ZX_EXTRACT_PKG.POPULATE |
2219 | |
2220 | MODIFICATION HISTORY |
2221 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
2222 +===========================================================================*/
2223
2224 PROCEDURE extract_additional_info(
2225 p_ledger_type IN VARCHAR2,
2226 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
2227 IS
2228
2229
2230 /*CURSOR rep_context_cursor(
2231 c_request_id IN NUMBER,
2232 c_set_of_books_id IN NUMBER)
2233 IS
2234 SELECT rep_context.rep_context_id , rep_context.rep_entity_id ,
2235 mo.operating_unit_id
2236 FROM zx_rep_context_t rep_context,
2237 fnd_mo_reporting_entities_v mo
2238 WHERE rep_context.rep_entity_id = mo.operating_unit_id
2239 AND mo.ledger_id = c_set_of_books_id
2240 AND rep_context.request_id = c_request_id
2241 AND mo.reporting_level = '3000';
2242 --P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL;
2243 -- '3000' ; */
2244
2245 CURSOR rep_context_cursor(
2246 c_request_id IN NUMBER) IS
2247 SELECT rep_context.rep_context_id,
2248 rep_context.rep_entity_id ,
2249 rep_context.rep_entity_id
2250 FROM zx_rep_context_t rep_context
2251 WHERE rep_context.request_id = c_request_id;
2252
2253 l_rep_context_id_rec zx_extract_pkg.rep_context_id_rectype;
2254 i BINARY_INTEGER;
2255 l_org_id NUMBER;
2256 l_ledger_id NUMBER;
2257
2258 BEGIN
2259
2260 IF (g_level_procedure >= g_current_runtime_level ) THEN
2261 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info.BEGIN',
2262 'ZX_EXTRACT_PKG:extract_additional_info(+)');
2263 END IF;
2264
2265 BEGIN
2266 -- IF p_ledger_type = 'R' THEN
2267 -- l_ledger_id := gl_mc_info.get_primary_ledger_id(
2268 -- g_ledger_id
2269 -- );
2270 -- open rep_context_cursor(
2271 -- P_TRL_GLOBAL_VARIABLES_REC.request_id,
2272 -- l_ledger_id
2273 -- );
2274 -- ELSE
2275 -- open rep_context_cursor(
2276 -- P_TRL_GLOBAL_VARIABLES_REC.request_id,
2277 -- g_ledger_id
2278 -- );
2279 open rep_context_cursor(
2280 P_TRL_GLOBAL_VARIABLES_REC.request_id
2281 );
2282 -- END IF;
2283
2284 i := 1;
2285 rep_context_id_tab.delete;
2286 LOOP
2287 FETCH rep_context_cursor INTO l_rep_context_id_rec;
2288 EXIT WHEN rep_context_cursor%NOTFOUND;
2289 rep_context_id_tab(i) := l_rep_context_id_rec;
2290 IF (g_level_procedure >= g_current_runtime_level ) THEN
2291 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2292 'rep_context_id_tab(i)'||to_char(rep_context_id_tab(i).rep_context_id));
2293 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2294 'l_rep_context_id_rec :'||to_char(l_rep_context_id_rec.rep_context_id));
2295 END IF;
2296 i := i + 1;
2297
2298 END LOOP;
2299
2300 IF rep_context_cursor%isopen THEN
2301 CLOSE rep_context_cursor;
2302 END IF;
2303
2304 EXCEPTION
2305 WHEN OTHERS THEN
2306 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2307 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2308 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','extract_additional_info- '|| g_error_buffer);
2309 FND_MSG_PUB.Add;
2310 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2311 FND_LOG.STRING(g_level_unexpected,
2312 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2313 g_error_buffer);
2314 END IF;
2315
2316 END;
2317
2318 -- Call to AR, AP Populate API calls to populate additional information
2319
2320
2321
2322 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AR' OR
2323 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
2324 THEN
2325 IF L_AR_RETCODE <> 2 THEN
2326 ZX_AR_POPULATE_PKG.update_additional_info(
2327 P_TRL_GLOBAL_VARIABLES_REC);
2328 END IF;
2329 L_AR_RETCODE := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2330 END IF;
2331
2332 IF (P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
2333 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL') AND
2334 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE IN ('TAX','NON-RECOVERABLE','ALL')
2335 THEN
2336 -- IF L_AP_RETCODE <> 2 THEN
2337 ZX_AP_POPULATE_PKG.UPDATE_ADDITIONAL_INFO(
2338 P_TRL_GLOBAL_VARIABLES_REC );
2339 -- END IF;
2340 L_AP_RETCODE := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2341 END IF;
2342
2343 IF (P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
2344 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL')
2345 THEN
2346 -- IF L_AP_RETCODE <> 2 THEN
2347 ZX_GL_EXTRACT_PKG.UPDATE_ADDITIONAL_INFO(
2348 P_TRL_GLOBAL_VARIABLES_REC );
2349 -- END IF;
2350 L_AP_RETCODE := P_TRL_GLOBAL_VARIABLES_REC.RETCODE;
2351 END IF;
2352
2353 -- Call to JX populate Plug-in APIs
2354 IF (g_level_procedure >= g_current_runtime_level ) THEN
2355 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2356 'AR JX product Call: Return Code : '||to_char(L_AR_RETCODE));
2357 END IF;
2358
2359 -- ZX_JL_EXTRACT_PKG.populate_jl_ar(P_TRL_GLOBAL_VARIABLES_REC);
2360 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT IN ( 'AR', 'GL') OR
2361 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
2362 THEN
2363 IF L_AR_RETCODE <> 2 THEN
2364 IF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'RXZXPFTR' THEN
2365 ZX_CORE_REP_EXTRACT_PKG.populate_core_ar(P_TRL_GLOBAL_VARIABLES_REC);
2366 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('SG','TW','ZX') THEN
2367 ZX_JA_EXTRACT_PKG.populate_ja_ar(P_TRL_GLOBAL_VARIABLES_REC);
2368 ELSIF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JGVAT' THEN
2369 ZX_JE_EXTRACT_PKG.populate_je_ar(P_TRL_GLOBAL_VARIABLES_REC);
2370 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('AR','CL','CO','ZZ') THEN
2371 ZX_JL_EXTRACT_PKG.populate_jl_ar(P_TRL_GLOBAL_VARIABLES_REC);
2372 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,1,3) = 'ZXX' THEN
2373 ZX_CORE_REP_EXTRACT_PKG.populate_core_ar(P_TRL_GLOBAL_VARIABLES_REC);
2374 END IF;
2375 END IF;
2376 END IF;
2377
2378 IF (g_level_procedure >= g_current_runtime_level ) THEN
2379 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2380 'AP JX product Call: Return Code : '||to_char(L_AP_RETCODE));
2381 END IF;
2382 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'AP' OR
2383 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
2384 THEN
2385 IF (g_level_procedure >= g_current_runtime_level ) THEN
2386 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2387 'AP JL product Call: Return Code : '||P_TRL_GLOBAL_VARIABLES_REC.PRODUCT);
2388 END IF;
2389
2390 IF L_AP_RETCODE <> 2 THEN
2391 IF (g_level_procedure >= g_current_runtime_level ) THEN
2392 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2393 'AP JL product Call: Return Code : '||to_char(L_AP_RETCODE));
2394 END IF;
2395 IF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'RXZXPFTR' THEN
2396 ZX_CORE_REP_EXTRACT_PKG.populate_core_ap(P_TRL_GLOBAL_VARIABLES_REC);
2397 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('SG','TW','ZX') THEN
2398 ZX_JA_EXTRACT_PKG.populate_ja_ap(P_TRL_GLOBAL_VARIABLES_REC);
2399 ELSIF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JGVAT' THEN
2400 ZX_JE_EXTRACT_PKG.populate_je_ap(P_TRL_GLOBAL_VARIABLES_REC);
2401 -- This is to update recovery rate and IPV amounts --
2402 ZX_CORE_REP_EXTRACT_PKG.populate_core_ap(P_TRL_GLOBAL_VARIABLES_REC);
2403 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,3,2) IN ('AR','CL','CO') THEN
2404 IF (g_level_procedure >= g_current_runtime_level ) THEN
2405 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2406 'AP JL product Call: Return Code : '||'populate_jl_ap');
2407 END IF;
2408
2409 ZX_JL_EXTRACT_PKG.populate_jl_ap(P_TRL_GLOBAL_VARIABLES_REC);
2410 ELSIF SUBSTR(P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,1,3) = 'ZXX' THEN
2411 ZX_CORE_REP_EXTRACT_PKG.populate_core_ap(P_TRL_GLOBAL_VARIABLES_REC);
2412 END IF;
2413 END IF;
2414 END IF;
2415
2416 IF P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'GL' OR
2417 P_TRL_GLOBAL_VARIABLES_REC.PRODUCT = 'ALL'
2418 THEN
2419 IF L_GL_RETCODE <> 2 THEN
2420 IF P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME = 'JGVAT' THEN
2421 ZX_JE_EXTRACT_PKG.populate_je_gl(P_TRL_GLOBAL_VARIABLES_REC);
2422 END IF;
2423 END IF;
2424 END IF;
2425
2426 ZX_JG_EXTRACT_PKG.get_taxable(P_TRL_GLOBAL_VARIABLES_REC);
2427
2428
2429 EXCEPTION
2430 WHEN OTHERS THEN
2431 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2432 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2433 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2434 FND_MSG_PUB.Add;
2435 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2436 FND_LOG.STRING(g_level_unexpected,
2437 'ZX.TRL.ZX_EXTRACT_PKG.extract_additional_info',
2438 g_error_buffer);
2439 END IF;
2440 APPEND_ERRBUF(g_error_buffer);
2441 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
2442
2443 END extract_additional_info;
2444
2445
2446 /*===========================================================================+
2447 | PROCEDURE |
2448 | CLEANUP |
2449 | |
2450 | DESCRIPTION |
2451 | This procedure deletes the records from AR_TAX_EXTRACT_DCL_IF |
2452 | which do not have any child records in AR_TAX_EXTRACT_SUB_ITF |
2453 | for the given request_id |
2454 | |
2455 | Called from ZX_EXTRACT_PKG.POPULATE |
2456 | |
2457 | SCOPE - Private |
2458 | |
2459 | NOTES |
2460 | |
2461 | MODIFICATION HISTORY |
2462 | |
2463 +===========================================================================*/
2464
2465 PROCEDURE CLEANUP (
2466 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
2467 ) IS
2468
2469 j number := 0;
2470 l_declarer_id number;
2471 l_count number;
2472 BEGIN
2473 /*
2474 -- Performance bug#3264164
2475 type extract_line_id_t is table of
2476 ar_tax_extract_sub_itf.extract_line_id%type
2477 index by binary_integer;
2478 type trx_id_t is table of
2479 ar_tax_extract_sub_itf.trx_id%type
2480 index by binary_integer;
2481 type trx_class_code_t is table of
2482 ar_tax_extract_sub_itf.trx_class_code%type
2483 index by binary_integer;
2484 type tax_code_id_t is table of
2485 ar_tax_extract_sub_itf.tax_code_id%type
2486 index by binary_integer;
2487 type tax_code_register_type_code_t is table of
2488 ar_tax_extract_sub_itf.tax_code_register_type_code%type
2489 index by binary_integer;
2490 type extract_source_ledger_t is table of
2491 ar_tax_extract_sub_itf.extract_source_ledger%type
2492 index by binary_integer;
2493 type extract_report_line_number_t is table of
2494 ar_tax_extract_sub_itf.extract_report_line_number%type
2495 index by binary_integer;
2496
2497 l_extract_line_id extract_line_id_t;
2498 l_trx_id trx_id_t;
2499 l_trx_class_code trx_class_code_t;
2500 l_tax_code_id tax_code_id_t;
2501 l_tax_cd_register_type_cd tax_code_register_type_code_t;
2502 l_extract_source_ledger extract_source_ledger_t;
2503 l_extract_report_line_num extract_report_line_number_t;
2504
2505 BEGIN
2506 l_count := 0;
2507
2508 BEGIN
2509 DELETE FROM AR_TAX_EXTRACT_SUB_ITF WHERE EXTRACT_LINE_ID IN
2510 (select extract_line_id
2511 from ar_tax_extract_sub_itf
2512 where request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
2513 and (trx_id is null
2514 or trx_class_code is null
2515 -- or tax_code_id is null
2516 or tax_code_register_type_code is null
2517 or extract_source_ledger is null
2518 or extract_report_line_number is null))
2519 RETURNING
2520 extract_line_id,
2521 trx_id,
2522 trx_class_code,
2523 tax_code_id,
2524 tax_code_register_type_code,
2525 extract_source_ledger,
2526 extract_report_line_number
2527 BULK COLLECT INTO
2528 l_extract_line_id,
2529 l_trx_id,
2530 l_trx_class_code,
2531 l_tax_code_id,
2532 l_tax_cd_register_type_cd,
2533 l_extract_source_ledger,
2534 l_extract_report_line_num;
2535
2536 -- All the mandatory columns for the selected declarer_id
2537
2538 IF PG_DEBUG = 'Y' THEN
2539 FOR j IN 1..l_extract_line_id.COUNT LOOP
2540 arp_util_tax.debug('Mandatory columns missing for Extract Line Id : '||
2541 to_char(l_extract_line_id(j))||
2542 '. Deleting this line from AR_TAX_EXTRACT_SUB_ITF.');
2543 arp_util_tax.debug('trx_id : ' || to_char(l_trx_id(j)) ||
2544 ' trx_class_code : ' || l_trx_class_code(j) ||
2545 ' tax_code_id : ' || to_char(l_tax_code_id(j)));
2546 arp_util_tax.debug('ax_code_register_type_code : ' ||
2547 l_tax_cd_register_type_cd(j) ||
2548 ' extract_source_ledger : ' || l_extract_source_ledger(j) ||
2549 ' extract_report_line_number : ' ||
2550 to_char(l_extract_report_line_num(j)));
2551 END LOOP;
2552
2553 l_count := l_extract_line_id.COUNT;
2554 arp_util_tax.debug(to_char(nvl(l_count,0))||' records deleted because '
2555 ||'mandatory columns are not populated . ');
2556 arp_util_tax.debug(' ');
2557
2558 END IF;
2559
2560 EXCEPTION
2561 WHEN OTHERS THEN
2562 STACK_ERROR('FND','SQL_PLSQL_ERROR','ERRNO',SQLCODE,'REASON',SQLERRM,
2563 'ROUTINE','ZX_EXTRACT_PKG.CLEANUP');
2564 L_MSG := FND_MESSAGE.GET;
2565
2566 END;
2567
2568 declarer_id_lookup_table.delete;
2569 l_count := 0;
2570
2571 -- Performance bug#3264164
2572 DELETE FROM AR_TAX_EXTRACT_SUB_ITF
2573 WHERE TAX_EXTRACT_DECLARER_ID IS NULL
2574 AND REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
2575
2576 l_count := sql%rowcount; -- apai
2577
2578 DELETE FROM zx_rep_trx_detail_t i
2579 WHERE request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
2580 AND rep_context_id is null;
2581 */
2582 NULL;
2583
2584 END CLEANUP;
2585
2586
2587
2588 /*===========================================================================+
2589 | PROCEDURE |
2590 | DELETE_ALL |
2591 | |
2592 | DESCRIPTION |
2593 | This procedure deletes the records from AR_TAX_EXTRACT_DCL_IF |
2594 | and AR_TAX_EXTRACT_SUB_ITF for a given request_id. This procedure |
2595 | is called from the procedure populate if some fatal error condition |
2596 | occurs and error_code is set to 2 |
2597 | |
2598 | Called from ZX_EXTRACT_PKG.POPULATE |
2599 | |
2600 | SCOPE - Private |
2601 | |
2602 | NOTES |
2603 | |
2604 | MODIFICATION HISTORY |
2605 | |
2606 +===========================================================================*/
2607
2608 PROCEDURE DELETE_ALL(P_REQUEST_ID IN NUMBER ) IS
2609 BEGIN
2610 /* IF PG_DEBUG = 'Y' THEN
2611 arp_util_tax.debug('DELETE_ALL(+) ');
2612 END IF;
2613 delete from ar_tax_extract_sub_itf sub_itf
2614 where
2615 sub_itf.request_id = P_REQUEST_ID
2616 and exists
2617 (select dcl_itf.tax_extract_declarer_id
2618 from ar_tax_extract_dcl_itf dcl_itf
2619 where sub_itf.tax_extract_declarer_id=dcl_itf.tax_extract_declarer_id);
2620 commit;
2621
2622 -- check with Srinivas
2623 delete from ar_tax_extract_dcl_itf where request_id = P_REQUEST_ID;
2624 commit; */
2625
2626 NULL;
2627
2628 END;
2629
2630
2631 /*===========================================================================+
2632 | PROCEDURE |
2633 | insert_rep_context_itf |
2634 | |
2635 | DESCRIPTION |
2636 | This PROCEDURE inserts Reporting Context information INTO |
2637 | zx_rep_context_t |
2638 | |
2639 | SCOPE - Private |
2640 | |
2641 | NOTES |
2642 | |
2643 | MODIFICATION HISTORY |
2644 | 08-Feb-2005 Srinivasa Rao Korrapati Created |
2645 +===========================================================================*/
2646
2647 PROCEDURE insert_rep_context_itf(
2648 p_trl_global_variables_rec IN OUT NOCOPY zx_extract_pkg.trl_global_variables_rec_type,
2649 p_rep_entity_id IN NUMBER)
2650 IS
2651
2652 l_style HR_LOCATIONS_ALL.style%TYPE;
2653 l_extract_summary_code VARCHAR2(80);
2654 l_rep_context_id NUMBER;
2655 l_rep_context_org_id NUMBER;
2656 l_request_id NUMBER;
2657 l_rep_entity_id NUMBER;
2658 l_rep_context_tax_reg_no HR_ORGANIZATION_INFORMATION.org_information2%TYPE;
2659 l_rep_context_loc_id HR_LOCATIONS_ALL.location_id%TYPE;
2660 l_rep_context_name HR_ORGANIZATION_UNITS.name%TYPE;
2661 l_rep_context_city HR_LOCATIONS_ALL.town_or_city%TYPE;
2662 l_rep_context_county HR_LOCATIONS_ALL.region_3%TYPE;
2663 l_rep_context_state HR_LOCATIONS_ALL.region_2%TYPE;
2664 l_rep_context_province HR_LOCATIONS_ALL.region_1%TYPE;
2665 l_rep_context_address1 HR_LOCATIONS_ALL.address_line_1%TYPE;
2666 l_rep_context_address2 HR_LOCATIONS_ALL.address_line_2%TYPE;
2667 l_rep_context_address3 HR_LOCATIONS_ALL.address_line_3%TYPE;
2668 l_rep_context_country HR_LOCATIONS_ALL.country%TYPE;
2669 l_rep_context_postal_code HR_LOCATIONS_ALL.postal_code%TYPE;
2670 l_rep_context_phone_number HR_LOCATIONS_ALL.telephone_number_1%TYPE;
2671 l_rep_context_lvl_mng VARCHAR2(80);
2672 l_rep_context_lvl_code VARCHAR2(30);
2673 l_matrix_report_flag VARCHAR2(30);
2674 l_rep_context_entity_region1 VARCHAR2(30);
2675 l_legal_contact_pre_name_adj VARCHAR2(30);
2676 l_legal_contact_party_name xle_legal_contacts_v.contact_name%TYPE;
2677 l_bank_id NUMBER;
2678 l_bank_branch_id NUMBER;
2679 l_bank_account_num NUMBER;
2680 l_taxpayer_id xle_firstparty_information_v.REGISTRATION_NUMBER%TYPE;
2681 l_legal_contact_title xle_legal_contacts_v.title%TYPE;
2682 --l_legal_contact_job_title xle_legal_contacts_v.job_title%TYPE;
2683 l_legal_contact_job_title varchar2(13);--xle_legal_contacts_v.role%TYPE;
2684 l_activity_code xle_firstparty_information_v.activity_code%TYPE;
2685 l_sub_activity_code xle_firstparty_information_v.activity_code%TYPE;
2686 l_inception_date DATE;
2687 l_legal_contact_party_num xle_legal_contacts_v.contact_legal_id%TYPE;
2688 l_legal_auth_address_line2 xle_legalauth_v.address2%TYPE;
2689 l_legal_auth_address_line3 xle_legalauth_v.address3%TYPE;
2690 l_legal_auth_city xle_legalauth_v.city%TYPE;
2691 l_legal_auth_name xle_legalauth_v.authority_name%TYPE;
2692 l_org_information2 VARCHAR2(150);
2693 l_program_application_id NUMBER;
2694 l_program_id NUMBER;
2695 l_program_login_id NUMBER;
2696 l_rowcount NUMBER;
2697 l_hq_party_id NUMBER ;
2698 x_return_status varchar2(200);
2699
2700 /* apai
2701 CURSOR c_loc_rec (c_rep_entity_id NUMBER) IS
2702 SELECT loc.country,
2703 loc.town_or_city,
2704 loc.region_1,
2705 loc.region_2,
2706 loc.region_3,
2707 loc.address_line_1,
2708 loc.address_line_2,
2709 loc.address_line_3,
2710 loc.postal_code,
2711 loc.telephone_number_1,
2712 loc.style,
2713 loc.location_id
2714 FROM hr_locations loc,
2715 hr_organization_units org
2716 WHERE org.location_id = loc.location_id
2717 AND org.organization_id = c_rep_entity_id;
2718 */
2719 CURSOR c_legal_info (c_rep_entity_id number) IS
2720 SELECT
2721 xle_firstpty.name ,
2722 xle_firstpty.activity_code,
2723 xle_firstpty.sub_activity_code,
2724 xle_firstpty.registration_number,
2725 -- xle_firstpty.effective_from
2726 xle_firstpty.location_id,
2727 xle_firstpty.address_line_1,
2728 xle_firstpty.address_line_2,
2729 xle_firstpty.address_line_3,
2730 xle_firstpty.town_or_city,
2731 xle_firstpty.region_1,
2732 xle_firstpty.region_2,
2733 xle_firstpty.region_3,
2734 xle_firstpty.postal_code,
2735 -- xle_firstpty.phone_number,
2736 xle_firstpty.country,
2737 xle_firstpty.address_style
2738 -- xle_cont.contact_name,
2739 -- xle_cont.contact_legal_id,
2740 -- xle_cont.title,
2741 -- xle_cont.job_title
2742 -- xle_cont.role
2743 FROM xle_firstparty_information_v xle_firstpty
2744 -- xle_legal_contacts_v xle_cont
2745 WHERE xle_firstpty.legal_entity_id = c_rep_entity_id;
2746 -- xle_firstpty.legal_entity_id = xle_cont.entity_id(+)
2747
2748 CURSOR c_legal_auth_info (c_rep_entity_id number) IS
2749 SELECT xle_auth.address2,
2750 xle_auth.address3,
2751 xle_auth.city,
2752 xle_auth.authority_name
2753 FROM xle_legalauth_v xle_auth,
2754 xle_registrations xle_reg
2755 WHERE xle_reg.source_id = c_rep_entity_id
2756 AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
2757 AND xle_auth.legalauth_id = xle_reg.issuing_authority_id
2758 AND xle_reg.identifying_flag = 'Y';
2759
2760 CURSOR c_legal_contact_info (c_rep_entity_id number) IS
2761 SELECT per.party_name, -- contact_name,
2762 per.jgzz_fiscal_code, --contact_legal_id,
2763 -- rol.lookup_code, --job Title
2764 XLE_CONTACT_GRP.concat_contact_roles
2765 (rel.subject_id,
2766 rel.object_id),
2767 hzpp.person_pre_name_adjunct -- title
2768 FROM HZ_PARTIES per,
2769 xle_entity_profiles xep,
2770 HZ_RELATIONSHIPS rel,
2771 hz_person_profiles hzpp,
2772 HZ_ORG_CONTACTS con
2773 --XLE_CONTACT_LEGAL_ROLES rol
2774 WHERE rel.relationship_code = 'CONTACT_OF'
2775 AND rel.object_id = xep.party_id
2776 AND per.party_id = hzpp.party_id
2777 AND rel.relationship_type = 'CONTACT'
2778 AND rel.directional_flag = 'F'
2779 AND rel.subject_table_name = 'HZ_PARTIES'
2780 AND rel.subject_type = 'PERSON'
2781 AND rel.subject_id = per.party_id
2782 -- AND rel.subject_id = rol.contact_party_id
2783 -- AND rel.object_id = rol.le_etb_party_id
2784 AND rel.object_table_name = 'HZ_PARTIES'
2785 AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)
2786 AND rel.relationship_id = con.party_relationship_id
2787 AND xep.legal_entity_id = c_rep_entity_id
2788 UNION
2789 SELECT per.party_name, --contact_name,
2790 per.jgzz_fiscal_code, --contact_legal_id,
2791 -- rol.lookup_code, --job Title
2792 XLE_CONTACT_GRP.concat_contact_roles
2793 (rel.subject_id,
2794 rel.object_id),
2795 hzpp.person_pre_name_adjunct --title,
2796 FROM HZ_PARTIES per,
2797 xle_etb_profiles etb,
2798 HZ_RELATIONSHIPS rel,
2799 hz_person_profiles hzpp,
2800 HZ_ORG_CONTACTS con
2801 -- XLE_CONTACT_LEGAL_ROLES rol
2802 WHERE rel.relationship_code = 'CONTACT_OF'
2803 AND rel.object_id = etb.party_id
2804 AND per.party_id = hzpp.party_id
2805 AND rel.relationship_type = 'CONTACT'
2806 AND rel.directional_flag = 'F'
2807 AND rel.subject_table_name = 'HZ_PARTIES'
2808 AND rel.subject_type = 'PERSON'
2809 AND rel.subject_id = per.party_id
2810 --AND rel.subject_id = rol.contact_party_id
2811 --AND rel.object_id = rol.le_etb_party_id
2812 AND rel.object_table_name = 'HZ_PARTIES'
2813 AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE)
2814 AND rel.relationship_id = con.party_relationship_id
2815 AND etb.establishment_id = c_rep_entity_id ;
2816
2817 BEGIN
2818
2819 IF (g_level_procedure >= g_current_runtime_level ) THEN
2820 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf.BEGIN',
2821 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf(+)');
2822 END IF;
2823
2824 IF p_trl_global_variables_rec.summary_level = 'TRANSACTION_DISTRIBUTION' THEN
2825 l_extract_summary_code := 'D';
2826 ELSIF p_trl_global_variables_rec.summary_level = 'TRANSACTION_LINE' THEN
2827 l_extract_summary_code := 'L';
2828 ELSE
2829 l_extract_summary_code := 'H';
2830 END IF;
2831
2832
2833 IF p_trl_global_variables_rec.reporting_level = '1000' THEN
2834 l_rep_context_lvl_mng := 'Ledger';
2835 ELSIF p_trl_global_variables_rec.reporting_level = '2000' THEN
2836 l_rep_context_lvl_mng := 'Legal Entity';
2837 ELSIF p_trl_global_variables_rec.reporting_level = '3000' THEN
2838 l_rep_context_lvl_mng := 'Operating Unit';
2839 END IF;
2840
2841 --Bug 5438409
2842 IF (g_level_statement >= g_current_runtime_level ) THEN
2843 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2844 'l_rep_context_lvl_mng : '||l_rep_context_lvl_mng);
2845 END IF;
2846
2847 /* apai
2848 IF p_trl_global_variables_rec.legal_entity_level IS NOT NULL THEN
2849 */
2850 OPEN c_legal_info (p_trl_global_variables_rec.legal_entity_id);
2851 FETCH c_legal_info INTO
2852 l_rep_context_name,
2853 l_activity_code,
2854 l_sub_activity_code,
2855 l_taxpayer_id,
2856 -- l_effective_from,
2857 l_rep_context_loc_id,
2858 g_rep_context_address1,
2859 g_rep_context_address2,
2860 g_rep_context_address3,
2861 g_rep_context_city,
2862 g_rep_context_region_1,
2863 g_rep_context_region_2,
2864 g_rep_context_region_3,
2865 g_rep_context_postal_code,
2866 g_rep_context_country,
2867 -- g_rep_context_phone_number,
2868 l_style;
2869 -- l_legal_contact_party_name,
2870 -- l_legal_contact_party_num,
2871 -- l_legal_contact_title,
2872 -- l_legal_contact_job_title;
2873 CLOSE c_legal_info;
2874
2875 OPEN c_legal_contact_info(p_trl_global_variables_rec.legal_entity_id);
2876 FETCH c_legal_contact_info INTO
2877 l_legal_contact_party_name,
2878 l_legal_contact_party_num,
2879 l_legal_contact_title,
2880 l_legal_contact_job_title;
2881 CLOSE c_legal_contact_info;
2882
2883 IF (g_level_statement >= g_current_runtime_level ) THEN
2884 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2885 'l_rep_context_name : '|| l_rep_context_name);
2886 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2887 'l_activity_code : '|| l_activity_code);
2888 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2889 'l_sub_activity_code : '|| l_sub_activity_code);
2890 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2891 'l_taxpayer_id : '|| l_taxpayer_id);
2892 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2893 'l_rep_context_loc_id : '|| l_rep_context_loc_id);
2894 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2895 'g_rep_context_address1 : '|| g_rep_context_address1);
2896 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2897 'g_rep_context_city : '|| g_rep_context_city);
2898
2899 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2900 'l_style : '|| l_style);
2901 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2902 'l_legal_contact_party_name : '|| l_legal_contact_party_name);
2903 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2904 'l_legal_contact_title : '|| l_legal_contact_title);
2905 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2906 'l_legal_contact_party_num : '|| l_legal_contact_party_num);
2907
2908 END IF;
2909
2910 OPEN c_legal_auth_info (p_trl_global_variables_rec.legal_entity_id);
2911 FETCH c_legal_auth_info INTO
2912 l_legal_auth_address_line2,
2913 l_legal_auth_address_line3,
2914 l_legal_auth_city,
2915 l_legal_auth_name ;
2916 CLOSE c_legal_auth_info;
2917
2918 IF (g_level_statement >= g_current_runtime_level ) THEN
2919 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2920 'l_legal_auth_address_line2 : '|| l_legal_auth_address_line2);
2921 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2922 'l_legal_auth_address_line3 : '|| l_legal_auth_address_line3);
2923 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2924 'l_legal_auth_city : '|| l_legal_auth_city);
2925 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2926 'l_legal_auth_name : '|| l_legal_auth_name);
2927 END IF;
2928
2929 /* apai
2930 ELSE
2931 OPEN c_loc_rec(p_rep_entity_id);
2932 FETCH c_loc_rec INTO
2933 g_rep_context_country,
2934 g_rep_context_city,
2935 g_rep_context_region_1,
2936 g_rep_context_region_2,
2937 g_rep_context_region_3,
2938 g_rep_context_address1,
2939 g_rep_context_address2,
2940 g_rep_context_address3,
2941 g_rep_context_postal_code,
2942 g_rep_context_phone_number,
2943 l_style,
2944 l_rep_context_loc_id;
2945 CLOSE c_loc_rec;
2946
2947 SELECT org_info.org_information2,
2948 org_unit.organization_id,
2949 org_unit.location_id,
2950 org_unit.name
2951 INTO l_rep_context_tax_reg_no,
2952 l_rep_context_org_id,
2953 l_rep_context_loc_id,
2954 l_rep_context_name
2955 FROM hr_organization_units org_unit,
2956 hr_organization_information org_info
2957 WHERE org_unit.organization_id = org_info.organization_id
2958 AND org_info.org_information1 = 'OPERATING_UNIT'
2959 AND org_unit.organization_id = p_rep_entity_id;
2960
2961 END IF;
2962 */
2963
2964 IF (g_level_procedure >= g_current_runtime_level ) THEN
2965 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2966 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf: After c_loc_rec cursor ');
2967 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2968 'p_rep_entity_id :'||to_char(p_rep_entity_id));
2969 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2970 'g_rep_context_city :'||g_rep_context_city);
2971 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2972 'g_rep_context_address1 :'||g_rep_context_address1);
2973 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
2974 'l_rep_context_name :'||l_rep_context_name);
2975 END IF;
2976
2977 -- populate the legal entity address fields :
2978 -- by calling function get_location_column with l_style
2979
2980 l_rep_context_city := location_value(get_location_column(l_style,'CITY'));
2981 l_rep_context_county := location_value(get_location_column(l_style,'COUNTY'));
2982 l_rep_context_state := location_value(get_location_column(l_style,'STATE'));
2983 l_rep_context_province := location_value(get_location_column(l_style,'PROVINCE'));
2984 l_rep_context_country := location_value(get_location_column(l_style,'COUNTRY'));
2985 l_rep_context_address1 := location_value(get_location_column(l_style,'ADDRESS LINE 1'));
2986 l_rep_context_address2 := location_value(get_location_column(l_style,'ADDRESS LINE 2'));
2987 l_rep_context_address3 := location_value(get_location_column(l_style,'ADDRESS LINE 3'));
2988 l_rep_context_phone_number := location_value(get_location_column(l_style,'TELEPHONE'));
2989 l_rep_context_postal_code := location_value(get_location_column(l_style,'POSTAL CODE'));
2990 l_rep_entity_id := p_rep_entity_id;
2991
2992 --Bug 5439099 : Logic Implemented to populate the org_information2 with Main HQ EST REG NBR
2993
2994 BEGIN
2995 --get ptp id for HQ of the LE
2996 SELECT ptp.party_id
2997 INTO l_hq_party_id
2998 FROM zx_party_tax_profile ptp,
2999 xle_etb_profiles xlep
3000 WHERE ptp.party_id = xlep.party_id
3001 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
3002 AND xlep.legal_entity_id = p_trl_global_variables_rec.legal_entity_id
3003 AND xlep.main_establishment_flag = 'Y';
3004
3005 l_org_information2 := ZX_TCM_EXT_SERVICES_PUB.Get_Default_Tax_Reg(
3006 l_hq_party_id ,
3007 'LEGAL_ESTABLISHMENT',
3008 SYSDATE,
3009 x_return_status);
3010 EXCEPTION
3011 WHEN OTHERS THEN
3012 l_org_information2 := null;
3013 END;
3014
3015
3016 BEGIN
3017 SELECT zx_rep_context_t_s.nextval
3018 INTO l_rep_context_id FROM dual;
3019
3020 IF (g_level_procedure >= g_current_runtime_level ) THEN
3021 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3022 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf: Insert statement begins ');
3023 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3024 'p_rep_entity_id :'||to_char(p_rep_entity_id));
3025 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3026 'rep_context_id :'||to_char(l_rep_context_id));
3027 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3028 'l_rep_context_city :'||l_rep_context_city);
3029 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3030 'l_rep_context_address1 :'||l_rep_context_address1);
3031 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3032 'HQ Tax Registration Nbr : l_org_information2 :'||l_org_information2);
3033 END IF;
3034
3035 Begin
3036 INSERT INTO zx_rep_context_t(
3037 rep_context_id,
3038 request_id,
3039 rep_entity_id,
3040 rep_context_entity_location_id,
3041 rep_context_entity_name,
3042 rep_context_entity_city,
3043 rep_context_entity_county,
3044 rep_context_entity_state,
3045 rep_context_entity_province,
3046 rep_context_entity_address1,
3047 rep_context_entity_address2,
3048 rep_context_entity_address3,
3049 rep_context_entity_country,
3050 rep_context_entity_postal_code,
3051 rep_context_entity_tel_number,
3052 rep_context_lvl_mng,
3053 rep_context_lvl_code,
3054 extract_summary_code,
3055 matrix_report_flag,
3056 legal_contact_pre_name_adjunct,
3057 legal_contact_party_name,
3058 taxpayer_id,
3059 legal_contact_title,
3060 activity_code,
3061 sub_activity_code,
3062 inception_date,
3063 legal_contact_party_num,
3064 legal_auth_address_line2,
3065 legal_auth_address_line3,
3066 legal_auth_city,
3067 legal_authority_name,
3068 org_information2,
3069 program_application_id,
3070 program_id,
3071 program_login_id,
3072 created_by,
3073 creation_date,
3074 last_updated_by,
3075 last_update_date,
3076 last_update_login)
3077 VALUES (
3078 l_rep_context_id,
3079 p_trl_global_variables_rec.request_id,
3080 l_rep_entity_id,
3081 l_rep_context_loc_id,
3082 l_rep_context_name,
3083 l_rep_context_city,
3084 l_rep_context_county,
3085 l_rep_context_state,
3086 l_rep_context_province,
3087 l_rep_context_address1,
3088 l_rep_context_address2,
3089 l_rep_context_address3,
3090 l_rep_context_country,
3091 l_rep_context_postal_code,
3092 l_rep_context_phone_number,
3093 l_rep_context_lvl_mng,
3094 --l_rep_context_lvl_code,
3095 p_trl_global_variables_rec.reporting_level,
3096 l_extract_summary_code,
3097 l_matrix_report_flag,
3098 l_legal_contact_job_title,
3099 l_legal_contact_party_name,
3100 l_taxpayer_id,
3101 l_legal_contact_title,
3102 l_activity_code,
3103 l_sub_activity_code,
3104 l_inception_date,
3105 l_legal_contact_party_num,
3106 l_legal_auth_address_line2,
3107 l_legal_auth_address_line3,
3108 l_legal_auth_city,
3109 l_legal_auth_name,
3110 l_org_information2,
3111 l_program_application_id,
3112 l_program_id,
3113 l_program_login_id,
3114 g_created_by,
3115 g_creation_date,
3116 g_last_updated_by,
3117 g_last_update_date,
3118 g_last_update_login);
3119
3120 l_rowcount := SQL%ROWCOUNT;
3121 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3122 FND_LOG.STRING(g_level_unexpected,
3123 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3124 'Rows Inserted in Rep context table :'||to_char(l_rowcount));
3125 END IF;
3126
3127 IF l_rowcount = 0 THEN
3128 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
3129 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3130 FND_LOG.STRING(g_level_unexpected,
3131 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3132 'P_TRL_GLOBAL_VARIABLES_REC.RETCODE'||to_char(P_TRL_GLOBAL_VARIABLES_REC.RETCODE));
3133 END IF;
3134
3135 END IF;
3136 END;
3137
3138 EXCEPTION
3139 WHEN OTHERS THEN
3140 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3141 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3142 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_rep_context_itf- '|| g_error_buffer);
3143 FND_MSG_PUB.Add;
3144 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3145 FND_LOG.STRING(g_level_unexpected,
3146 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf',
3147 g_error_buffer);
3148 END IF;
3149 APPEND_ERRBUF(g_error_buffer);
3150 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
3151
3152 END;
3153
3154 IF (g_level_procedure >= g_current_runtime_level ) THEN
3155 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf.END',
3156 'ZX.TRL.ZX_EXTRACT_PKG.insert_rep_context_itf(-)');
3157 END IF;
3158
3159 END insert_rep_context_itf ;
3160
3161
3162 /*===========================================================================+
3163 | function |
3164 | location_value |
3165 | |
3166 | description |
3167 | this function RETURNs the value stored in a particular column in |
3168 | the memory structure pr_org_loc_rec |
3169 | |
3170 | scope - private |
3171 +===========================================================================*/
3172
3173 FUNCTION location_value(
3174 p_column in VARCHAR2)
3175 RETURN VARCHAR2 IS
3176
3177 l_column_value VARCHAR2(240);
3178 l_column VARCHAR2(240);
3179
3180 BEGIN
3181
3182 IF (g_level_procedure >= g_current_runtime_level ) THEN
3183 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.location_value.BEGIN',
3184 'ZX.TRL.ZX_EXTRACT_PKG.location_value(+)');
3185 END IF;
3186
3187 l_column := p_column;
3188 IF l_column = 'TOWN_OR_CITY' THEN
3189 l_column_value := g_rep_context_city;
3190 ELSIF l_column = 'COUNTRY' THEN
3191 l_column_value := g_rep_context_country;
3192 ELSIF l_column = 'REGION_1' THEN
3193 l_column_value := g_rep_context_region_1;
3194 ELSIF l_column = 'REGION_2' THEN
3195 l_column_value := g_rep_context_region_2;
3196 ELSIF l_column = 'REGION_3' THEN
3197 l_column_value := g_rep_context_region_3;
3198 ELSIF l_column = 'ADDRESS_LINE_1' THEN
3199 l_column_value := g_rep_context_address1;
3200 ELSIF l_column = 'ADDRESS_LINE_2' THEN
3201 l_column_value := g_rep_context_address2;
3202 ELSIF l_column = 'ADDRESS_LINE_3' THEN
3203 l_column_value := g_rep_context_address3;
3204 ELSIF l_column = 'POSTAL_CODE' THEN
3205 l_column_value := g_rep_context_postal_code;
3206 ELSIF l_column = 'TELEPHONE_NUMBER_1' THEN
3207 l_column_value := g_rep_context_phone_number;
3208 ELSIF l_column = 'TELEPHONE_NUMBER_2' THEN
3209 l_column_value := g_rep_context_phone_number;
3210 END IF;
3211
3212 IF (g_level_procedure >= g_current_runtime_level ) THEN
3213 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.location_value.END',
3214 'ZX.TRL.ZX_EXTRACT_PKG.location_value(-)'||l_column_value);
3215 END IF;
3216
3217 RETURN l_column_value;
3218
3219 EXCEPTION
3220 WHEN OTHERS THEN
3221 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3222 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3223 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','location_value- '|| g_error_buffer);
3224 FND_MSG_PUB.Add;
3225 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3226 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_EXTRACT_PKG.location_value',
3227 g_error_buffer);
3228 END IF;
3229 APPEND_ERRBUF(g_error_buffer);
3230 --P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
3231
3232 END location_value;
3233
3234 /*===========================================================================+
3235 | FUNCTION |
3236 | GET_LOCATION_COLUMN |
3237 | |
3238 | DESCRIPTION |
3239 | This FUNCTION returns the column in table HR_LOCATIONS_V which |
3240 | stores the address information for a particular address style |
3241 | |
3242 | For example, if the location style is US then the column REGION1 |
3243 | is used to store COUNTY information, but if the style is CN the |
3244 | same column may store PROVINCE information. |
3245 | |
3246 | Called from ZX_EXTRACT_PKG.POPULATE_TAX_DATA() |
3247 | |
3248 | SCOPE - Private |
3249 | |
3250 | NOTES |
3251 | |
3252 | MODIFICATION HISTORY |
3253 | |
3254 +===========================================================================*/
3255
3256 FUNCTION get_location_column(p_style IN VARCHAR2,
3257 p_classification IN VARCHAR2)
3258 return VARCHAR2 is
3259
3260 ----------------------------
3261 -- Private Variables
3262 ----------------------------
3263 pr_segments FND_DFLEX.SEGMENTS_DR;
3264 pr_contexts FND_DFLEX.CONTEXTS_DR;
3265 i BINARY_INTEGER;
3266 l_style HR_LOCATIONS_ALL.STYLE%type;
3267 l_context NUMBER;
3268 l_column VARCHAR2(150);
3269
3270 BEGIN
3271
3272 l_style := p_style;
3273
3274 -- Get the context information from 'Address Location' Descriptive Flexfield
3275 -- Select the context value which matches p_org_loc_rec.style
3276 IF (g_level_procedure >= g_current_runtime_level ) THEN
3277 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column.BEGIN',
3278 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column(+)');
3279 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3280 'P_CLASSIFICATION = '||p_classification);
3281 END IF;
3282
3283 fnd_dflex.get_contexts(pr_flexfield, pr_contexts);
3284 l_context := NULL;
3285
3286 FOR i IN 1 .. pr_contexts.ncontexts LOOP
3287 IF(pr_contexts.is_enabled(i)) THEN
3288 IF pr_contexts.context_code(i) = l_style then
3289 IF (g_level_procedure >= g_current_runtime_level ) THEN
3290 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3291 'pr_contexts.context_code= '||pr_contexts.context_code(i)||'-'||
3292 pr_contexts.context_description(i));
3293 END IF;
3294 l_context := i;
3295 END IF;
3296 END IF;
3297 END LOOP;
3298
3299 IF l_context is NULL then
3300 return NULL;
3301 END IF;
3302
3303 -- Select the segments which correspond to the selected context.
3304 fnd_dflex.get_segments(fnd_dflex.make_context(pr_flexfield,
3305 pr_contexts.context_code(l_context)),
3306 pr_segments,
3307 TRUE);
3308
3309 -- Check if the segment name matches with the value of input parameter p_classification,
3310 -- Otherwise write an error message and return null
3311
3312 FOR i IN 1 .. pr_segments.nsegments LOOP
3313 IF upper(pr_segments.segment_name(i)) = upper(p_classification) then
3314 l_column := pr_segments.application_column_name(i);
3315 END IF;
3316 END LOOP;
3317
3318 IF l_column is NULL then
3319 IF (g_level_procedure >= g_current_runtime_level ) THEN
3320 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3321 'No column which matches the value of p_classification: '||p_classification);
3322 END IF;
3323 return NULL;
3324 END IF;
3325
3326 IF (g_level_procedure >= g_current_runtime_level ) THEN
3327 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column.END',
3328 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column(-)');
3329 END IF;
3330
3331 RETURN l_column;
3332
3333 EXCEPTION
3334 WHEN OTHERS THEN
3335 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3336 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3337 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_location_column- '|| g_error_buffer);
3338 FND_MSG_PUB.Add;
3339 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3340 FND_LOG.STRING(g_level_unexpected,
3341 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3342 g_error_buffer);
3343 END IF;
3344 APPEND_ERRBUF(g_error_buffer);
3345 --P_TRL_GLOBAL_VARIABLES_REC.RETCODE := 2;
3346 RETURN NULL;
3347
3348
3349 END get_location_column;
3350
3351
3352 /*===========================================================================+
3353 | PROCEDURE |
3354 | STACK_ERROR |
3355 | |
3356 | DESCRIPTION |
3357 | This procedure takes the token(s), Value(s) and puts on the message |
3358 | stack |
3359 | |
3360 | SCOPE - Private |
3361 | |
3362 | NOTES |
3363 | |
3364 | MODIFICATION HISTORY |
3365 | 13-July-99 Nilesh Patel Created |
3366 | |
3367 +===========================================================================*/
3368
3369 PROCEDURE stack_error (
3370 p_application VARCHAR2,
3371 p_msgname VARCHAR2,
3372 p_token1 VARCHAR2 DEFAULT NULL,
3373 p_value1 VARCHAR2 DEFAULT NULL,
3374 p_token2 VARCHAR2 DEFAULT NULL,
3375 p_value2 VARCHAR2 DEFAULT NULL,
3376 p_token3 VARCHAR2 DEFAULT NULL,
3377 p_value3 VARCHAR2 DEFAULT NULL ) IS
3378 BEGIN
3379
3380 fnd_message.set_name(nvl(p_application,'AR'),nvl(p_msgname,'GENERIC_MESSAGE'));
3381
3382 IF ( p_token1 IS NOT NULL ) THEN
3383 fnd_message.set_token(p_token1, p_value1);
3384 END IF;
3385
3386 IF ( p_token2 IS NOT NULL ) THEN
3387 fnd_message.set_token(p_token2, p_value2);
3388 END IF;
3389
3390
3391 IF ( p_token3 IS NOT NULL ) THEN
3392 fnd_message.set_token(p_token3, p_value3);
3393 END IF;
3394
3395 END stack_error;
3396
3397
3398 /*===========================================================================+
3399 | PROCEDURE |
3400 | SET_RETCODE |
3401 | |
3402 | DESCRIPTION |
3403 | This procedure sets the value of P_RETCODE |
3404 | |
3405 | SCOPE - Public |
3406 | |
3407 | NOTES |
3408 | |
3409 | MODIFICATION HISTORY |
3410 | 7-Oct-1999 Nilesh Patel Created |
3411 | |
3412 +===========================================================================*/
3413
3414 PROCEDURE set_retcode(p_retcode in number) is
3415 BEGIN
3416 If p_retcode = 2 then
3417 L_RETCODE := p_retcode;
3418 elsif p_retcode = 1 then
3419 IF L_RETCODE = 2 then
3420 NULL;
3421 ELSE
3422 L_RETCODE := p_retcode;
3423 END IF;
3424 end if;
3425 END set_retcode;
3426
3427 /*===========================================================================+
3428 | PROCEDURE |
3429 | APPEND_ERRBUF |
3430 | |
3431 | DESCRIPTION |
3432 | This procedure appends the input parameter p_msg to the global |
3433 | variable L_ERRBUF which will be returned to the calling concurrent |
3434 | program. |
3435 | |
3436 | SCOPE - Public |
3437 | |
3438 | NOTES |
3439 | |
3440 | MODIFICATION HISTORY |
3441 | 7-Oct-1999 Nilesh Patel Created |
3442 | |
3443 +===========================================================================*/
3444
3445 PROCEDURE append_errbuf(p_msg in varchar2) is
3446 BEGIN
3447 if nvl(lengthb(L_ERRBUF),0) = 0 THEN
3448 L_ERRBUF := p_msg;
3449 elsif nvl(lengthb(L_ERRBUF),0) < 2000 - nvl(lengthb(p_msg),0) then
3450 L_ERRBUF := L_ERRBUF ||';'||p_msg;
3451 end if;
3452
3453 L_ERRBUF := L_ERRBUF || fnd_global.newline;
3454
3455 END append_errbuf;
3456
3457 PROCEDURE PURGE(p_request_id in number,
3458 p_rows_deleted out NOCOPY number) is
3459 BEGIN
3460
3461 purge(p_request_id);
3462 p_rows_deleted:= purge(p_request_id);
3463
3464
3465 END PURGE;
3466
3467
3468 /*===========================================================================+
3469 | PROCEDURE |
3470 | PURGE |
3471 | |
3472 | DESCRIPTION |
3473 | This procedure deletes the records from AR_TAX_EXTRACT_DCL_ITF |
3474 | and AR_TAX_EXTRACT_SUB_ITF for a given request_id |
3475 | |
3476 | SCOPE - Public |
3477 | |
3478 | NOTES |
3479 | |
3480 | MODIFICATION HISTORY |
3481 | 14-Dec-1999 Nilesh Patel Created |
3482 | |
3483 +===========================================================================*/
3484
3485 PROCEDURE PURGE(p_request_id in number) is
3486 num_rows_deleted number := 0;
3487 BEGIN
3488 IF (g_level_procedure >= g_current_runtime_level ) THEN
3489 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.PURGE.BEGIN',
3490 'ZX.TRL.ZX_EXTRACT_PKG.BEGIN(+)');
3491 END IF;
3492
3493 IF (g_level_procedure >= g_current_runtime_level ) THEN
3494 select count(*) into num_rows_deleted
3495 from zx_rep_trx_detail_t
3496 where request_id = p_request_id;
3497 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3498 'Num of Rows in zx_rep_trx_detail_t :'||num_rows_deleted);
3499 select count(*) into num_rows_deleted
3500 from ZX_REP_ACTG_EXT_T
3501 where request_id = p_request_id;
3502 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3503 'Num of Rows in ZX_REP_ACTG_EXT_T :'||num_rows_deleted);
3504 select count(*) into num_rows_deleted
3505 from ZX_REP_TRX_JX_EXT_T
3506 where request_id = p_request_id;
3507 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3508 'Num of Rows in ZX_REP_TRX_JX_EXT_T :'||num_rows_deleted);
3509 select count(*) into num_rows_deleted
3510 from ZX_REP_CONTEXT_T
3511 where request_id = p_request_id;
3512 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_location_column',
3513 'Num of Rows in ZX_REP_CONTEXT_T :'||num_rows_deleted);
3514 END IF;
3515
3516 IF PG_DEBUG = 'N' THEN
3517 delete from ZX_REP_ACTG_EXT_T where request_id = p_request_id;
3518 delete from ZX_REP_TRX_JX_EXT_T where request_id = p_request_id;
3519 delete from ZX_REP_TRX_DETAIL_T where request_id = p_request_id;
3520 delete from ZX_REP_CONTEXT_T where request_id = p_request_id;
3521 IF (g_level_procedure >= g_current_runtime_level ) THEN
3522 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.PURGE.END',
3523 'In Delete when PG_DEBUG = N ');
3524 END IF;
3525 END IF;
3526
3527 IF (g_level_procedure >= g_current_runtime_level ) THEN
3528 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.PURGE.END',
3529 'ZX.TRL.ZX_EXTRACT_PKG.PURGE(-)');
3530 END IF;
3531
3532 /*
3533 delete from ar_tax_extr_sub_com_ext com_ext where com_ext.extract_line_id
3534 in (select sub_itf.extract_line_id
3535 from ar_tax_Extract_sub_itf sub_itf
3536 where request_id = p_request_id);
3537 delete from ar_tax_extr_sub_ar_ext ar_ext where ar_ext.extract_line_id
3538 in (select sub_itf.extract_line_id
3539 from ar_tax_Extract_sub_itf sub_itf
3540 where request_id = p_request_id);
3541 delete from ar_tax_extr_sub_ap_ext ap_ext where ap_ext.extract_line_id
3542 in (select sub_itf.extract_line_id
3543 from ar_tax_Extract_sub_itf sub_itf
3544 where request_id = p_request_id);
3545 delete from ar_tax_extract_sub_itf where request_id = p_request_id;
3546 delete from ar_tax_Extract_dcl_itf where request_id = p_request_id;
3547 */
3548
3549
3550 END PURGE;
3551
3552
3553 /*===========================================================================+
3554 | FUNCTION |
3555 | PURGE |
3556 | |
3557 | DESCRIPTION |
3558 | This function deletes the records from AR_TAX_EXTRACT_DCL_ITF, |
3559 | AR_TAX_EXTRACT_SUB_ITF for a given request_id |
3560 | |
3561 | SCOPE - Public |
3562 | |
3563 | NOTES |
3564 | |
3565 | MODIFICATION HISTORY |
3566 | 24-May-2000 Nilesh Patel Created |
3567 | |
3568 +===========================================================================*/
3569
3570 FUNCTION PURGE(p_request_id in number) return number is
3571 num_rows_deleted number := 0;
3572 BEGIN
3573
3574 --pg_debug_flag:= nvl(FND_PROFILE.value('TAX_DEBUG_FLAG'),'N');
3575
3576
3577 select count(*) into num_rows_deleted
3578 from zx_rep_trx_detail_t
3579 where request_id = p_request_id;
3580
3581 PURGE(p_request_id);
3582
3583 return(num_rows_deleted);
3584 END;
3585
3586
3587 /*===========================================================================+
3588 | FUNCTION |
3589 | CONVERT_STRING |
3590 | |
3591 | DESCRIPTION |
3592 | This procedure takes the input string, replaces all inverted commas |
3593 | i.e. ' to two inverted commas i.e. '' and returns the converted |
3594 | string |
3595 | |
3596 | SCOPE - Private |
3597 | |
3598 | NOTES |
3599 | |
3600 | MODIFICATION HISTORY |
3601 | 21-July-99 Nilesh Patel Created |
3602 | |
3603 +===========================================================================*/
3604
3605 function convert_string(p_string in varchar2)
3606 return varchar2 is
3607
3608 l_string varchar2(255);
3609 BEGIN
3610 If p_string is not null then
3611 l_string := replace(p_string,'''','''''');
3612 end if;
3613 return l_string;
3614 END;
3615
3616 /*===========================================================================+
3617 | PROCEDURE |
3618 | build_matrx_tbl |
3619 | |
3620 | DESCRIPTION |
3621 | This proceures takes a sql statement (varchar2) as input parameter, |
3622 | executes the sql statement, and builds a PLSQL table of records. |
3623 | |
3624 | SCOPE - Public |
3625 | |
3626 | NOTES |
3627 | |
3628 | MODIFICATION HISTORY |
3629 | 7-Sept-99 Nilesh Patel Created |
3630 | |
3631 +===========================================================================*/
3632
3633 /*
3634 procedure build_matrix_tbl( p_sql in varchar2) is
3635 type csr_type is ref cursor;
3636 csr csr_type;
3637 l_index number := 0;
3638 begin
3639 pg_sql := p_sql;
3640 matrix_tbl.delete;
3641 open csr for pg_sql;
3642 loop
3643 fetch csr into matrix_rec;
3644 exit when csr%notfound;
3645 l_index := l_index + 1;
3646 matrix_tbl(l_index) := matrix_rec;
3647 end loop;
3648 close csr;
3649 end if;
3650 exception
3651 when others then
3652 matrix_tbl.delete;
3653 close csr;
3654 NULL;
3655 end;
3656
3657 */
3658
3659 /*===========================================================================+
3660 | FUNCTION |
3661 | get_rep_context_id |
3662 | |
3663 | DESCRIPTION |
3664 | This function returns the rep_context_id for a given organization_id |
3665 | AR/AP and GL APIs calls this function |
3666 | SCOPE - Public |
3667 | |
3668 | NOTES |
3669 | |
3670 | MODIFICATION HISTORY |
3671 | |
3672 +===========================================================================*/
3673
3674 /* apai this fuction is modified as per legal entity changes
3675 function get_rep_context_id( p_org_id in number,
3676 p_legal in varchar2,
3677 p_legal_id in number,
3678 p_request_id in number)
3679 */
3680 function get_rep_context_id( p_legal_id in number,
3681 p_request_id in number)
3682 return number is
3683 l_rep_context_id number;
3684
3685
3686 CURSOR legal_rep_context_cur (
3687 c_request_id IN NUMBER)
3688 IS
3689 SELECT rep_context.rep_context_id
3690 FROM zx_rep_context_t rep_context
3691 WHERE request_id = c_request_id;
3692 --AND rep_context.rep_entity_id = c_legal_entity_id;
3693
3694 BEGIN
3695
3696 IF (g_level_procedure >= g_current_runtime_level ) THEN
3697 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_rep_context_id(+)',
3698 'Legal Entity ID :'||to_char(p_legal_id));
3699 END IF;
3700
3701 OPEN legal_rep_context_cur (p_request_id);
3702 --p_legal_id);
3703 FETCH legal_rep_context_cur into l_rep_context_id;
3704
3705 IF (g_level_procedure >= g_current_runtime_level ) THEN
3706 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.get_rep_context_id(-)',
3707 'l_rep_context_id :'||to_char(l_rep_context_id));
3708 END IF;
3709
3710 IF legal_rep_context_cur%ISOPEN then
3711 CLOSE legal_rep_context_cur;
3712 END IF;
3713
3714 RETURN l_rep_context_id;
3715
3716 END get_rep_context_id;
3717
3718
3719 /*===========================================================================+
3720 | FUNCTION |
3721 | use_matrix_flag |
3722 | |
3723 | DESCRIPTION |
3724 | This is a view functionwhich is called from concurrent program |
3725 | financial tax register to check whether matrix report flag is |
3726 | required or not |
3727 | SCOPE - Public |
3728 | |
3729 | NOTES |
3730 | |
3731 | MODIFICATION HISTORY |
3732 | 5-Jan-2000 Nilesh Patel Created |
3733 | |
3734 +===========================================================================*/
3735 function use_matrix_flag( p_report_id in varchar2,
3736 p_attribute_set in varchar2,
3737 p_product in varchar2 default NULL)
3738 return varchar2 is
3739 l_use_matrix_rep varchar2(1);
3740 begin
3741 /*cursor use_matrix_flag_csr_ar
3742 (c_report_id in number,
3743 c_attribute_set in varchar2 ) is
3744 select 'Y' from dual where exists
3745 (select '1'
3746 from FA_RX_REPORTS_V RV,
3747 FA_RX_ATTRSETS ATT,
3748 FA_RX_REP_COLUMNS COL
3749 where RV.REPORT_ID = C_REPORT_ID
3750 AND ATT.REPORT_ID = RV.REPORT_ID
3751 AND ATT.ATTRIBUTE_SET = C_ATTRIBUTE_SET
3752 AND ATT.ATTRIBUTE_SET = COL.ATTRIBUTE_SET
3753 AND COL.DISPLAY_STATUS = 'YES'
3754 AND COL.COLUMN_NAME IN (
3755 'TAX1_ACCOUNTED_AMOUNT',
3756 'TAX1_ACCOUNTED_CR',
3757 'TAX1_ACCOUNTED_DR',
3758 'TAX1_CODE',
3759 'TAX1_CODE_DESCRIPTION',
3760 'TAX1_CODE_NAME',
3761 'TAX1_CODE_RATE',
3762 'TAX1_CODE_REG_TYPE_CODE',
3763 'TAX1_CODE_REG_TYPE_MEANING',
3764 'TAX1_CODE_TAX_CLASS_CODE',
3765 'TAX1_CODE_TAX_CLASS_MEANING',
3766 'TAX1_CODE_TYPE_CODE',
3767 'TAX1_CODE_TYPE_MEANING',
3768 'TAX1_CODE_VAT_TRX_TYPE_CODE',
3769 'TAX1_CODE_VAT_TRX_TYPE_DESC',
3770 'TAX1_CODE_VAT_TRX_TYPE_MEANING',
3771 'TAX1_ENTERED_AMOUNT',
3772 'TAX1_ENTERED_CR',
3773 'TAX1_ENTERED_DR',
3774 'TAX1_LINE_EFFECTIVE_TAX_RATE',
3775 'TAX1_LINE_NUMBER',
3776 'TAX1_RECOVERABLE_FLAG',
3777 'TAXABLE1_ACCOUNTED_AMOUNT',
3778 'TAXABLE1_ACCOUNTED_CR',
3779 'TAXABLE1_ACCOUNTED_DR',
3780 'TAXABLE1_ENTERED_AMOUNT',
3781 'TAXABLE1_ENTERED_CR',
3782 'TAXABLE1_ENTERED_DR',
3783 'TAX2_ACCOUNTED_AMOUNT',
3784 'TAX2_ACCOUNTED_CR',
3785 'TAX2_ACCOUNTED_DR',
3786 'TAX2_CODE',
3787 'TAX2_CODE_DESCRIPTION',
3788 'TAX2_CODE_NAME',
3789 'TAX2_CODE_RATE',
3790 'TAX2_CODE_REG_TYPE_CODE',
3791 'TAX2_CODE_REG_TYPE_MEANING',
3792 'TAX2_CODE_TAX_CLASS_CODE',
3793 'TAX2_CODE_TAX_CLASS_MEANING',
3794 'TAX2_CODE_TYPE_CODE',
3795 'TAX2_CODE_TYPE_MEANING',
3796 'TAX2_CODE_VAT_TRX_TYPE_CODE',
3797 'TAX2_CODE_VAT_TRX_TYPE_DESC',
3798 'TAX2_CODE_VAT_TRX_TYPE_MEANING',
3799 'TAX2_ENTERED_AMOUNT',
3800 'TAX2_ENTERED_CR',
3801 'TAX2_ENTERED_DR',
3802 'TAX2_LINE_EFFECTIVE_TAX_RATE',
3803 'TAX2_LINE_NUMBER',
3804 'TAX2_RECOVERABLE_FLAG',
3805 'TAXABLE2_ACCOUNTED_AMOUNT',
3806 'TAXABLE2_ACCOUNTED_CR',
3807 'TAXABLE2_ACCOUNTED_DR',
3808 'TAXABLE2_ENTERED_AMOUNT',
3809 'TAXABLE2_ENTERED_CR',
3810 'TAXABLE2_ENTERED_DR',
3811 'TAX3_ACCOUNTED_AMOUNT',
3812 'TAX3_ACCOUNTED_CR',
3813 'TAX3_ACCOUNTED_DR',
3814 'TAX3_CODE',
3815 'TAX3_CODE_DESCRIPTION',
3816 'TAX3_CODE_NAME',
3817 'TAX3_CODE_RATE',
3818 'TAX3_CODE_REG_TYPE_CODE',
3819 'TAX3_CODE_REG_TYPE_MEANING',
3820 'TAX3_CODE_TAX_CLASS_CODE',
3821 'TAX3_CODE_TAX_CLASS_MEANING',
3822 'TAX3_CODE_TYPE_CODE',
3823 'TAX3_CODE_TYPE_MEANING',
3824 'TAX3_CODE_VAT_TRX_TYPE_CODE',
3825 'TAX3_CODE_VAT_TRX_TYPE_DESC',
3826 'TAX3_CODE_VAT_TRX_TYPE_MEANING',
3827 'TAX3_ENTERED_AMOUNT',
3828 'TAX3_ENTERED_CR',
3829 'TAX3_ENTERED_DR',
3830 'TAX3_LINE_EFFECTIVE_TAX_RATE',
3831 'TAX3_LINE_NUMBER',
3832 'TAX3_RECOVERABLE_FLAG',
3833 'TAXABLE3_ACCOUNTED_AMOUNT',
3834 'TAXABLE3_ACCOUNTED_CR',
3835 'TAXABLE3_ACCOUNTED_DR',
3836 'TAXABLE3_ENTERED_AMOUNT',
3837 'TAXABLE3_ENTERED_CR',
3838 'TAXABLE3_ENTERED_DR',
3839 'TAX4_ACCOUNTED_AMOUNT',
3840 'TAX4_ACCOUNTED_CR',
3841 'TAX4_ACCOUNTED_DR',
3842 'TAX4_CODE',
3843 'TAX4_CODE_DESCRIPTION',
3844 'TAX4_CODE_NAME',
3845 'TAX4_CODE_RATE',
3846 'TAX4_CODE_REG_TYPE_CODE',
3847 'TAX4_CODE_REG_TYPE_MEANING',
3848 'TAX4_CODE_TAX_CLASS_CODE',
3849 'TAX4_CODE_TAX_CLASS_MEANING',
3850 'TAX4_CODE_TYPE_CODE',
3851 'TAX4_CODE_TYPE_MEANING',
3852 'TAX4_CODE_VAT_TRX_TYPE_CODE',
3853 'TAX4_CODE_VAT_TRX_TYPE_DESC',
3854 'TAX4_CODE_VAT_TRX_TYPE_MEANING',
3855 'TAX4_ENTERED_AMOUNT',
3856 'TAX4_ENTERED_CR',
3857 'TAX4_ENTERED_DR',
3858 'TAX4_LINE_EFFECTIVE_TAX_RATE',
3859 'TAX4_LINE_NUMBER',
3860 'TAX4_RECOVERABLE_FLAG',
3861 'TAXABLE4_ACCOUNTED_AMOUNT',
3862 'TAXABLE4_ACCOUNTED_CR',
3863 'TAXABLE4_ACCOUNTED_DR',
3864 'TAXABLE4_ENTERED_AMOUNT',
3865 'TAXABLE4_ENTERED_CR',
3866 'TAXABLE4_ENTERED_DR') );
3867
3868 cursor use_matrix_flag_csr_ap
3869 (c_report_id in number,
3870 c_attribute_set in varchar2 ) is
3871 select 'Y' from dual where exists
3872 (select '1'
3873 from FA_RX_REPORTS_V RV,
3874 FA_RX_ATTRSETS ATT,
3875 FA_RX_REP_COLUMNS COL
3876 where RV.REPORT_ID = C_REPORT_ID
3877 AND ATT.REPORT_ID = RV.REPORT_ID
3878 AND ATT.ATTRIBUTE_SET = C_ATTRIBUTE_SET
3879 AND ATT.ATTRIBUTE_SET = COL.ATTRIBUTE_SET
3880 AND COL.DISPLAY_STATUS = 'YES'
3881 AND COL.COLUMN_NAME IN (
3882 'TAX3_ACCOUNTED_AMOUNT',
3883 'TAX3_ACCOUNTED_CR',
3884 'TAX3_ACCOUNTED_DR',
3885 'TAX3_CODE',
3886 'TAX3_CODE_DESCRIPTION',
3887 'TAX3_CODE_NAME',
3888 'TAX3_CODE_RATE',
3889 'TAX3_CODE_REG_TYPE_CODE',
3890 'TAX3_CODE_REG_TYPE_MEANING',
3891 'TAX3_CODE_TAX_CLASS_CODE',
3892 'TAX3_CODE_TAX_CLASS_MEANING',
3893 'TAX3_CODE_TYPE_CODE',
3894 'TAX3_CODE_TYPE_MEANING',
3895 'TAX3_CODE_VAT_TRX_TYPE_CODE',
3896 'TAX3_CODE_VAT_TRX_TYPE_DESC',
3897 'TAX3_CODE_VAT_TRX_TYPE_MEANING',
3898 'TAX3_ENTERED_AMOUNT',
3899 'TAX3_ENTERED_CR',
3900 'TAX3_ENTERED_DR',
3901 'TAX3_LINE_EFFECTIVE_TAX_RATE',
3902 'TAX3_LINE_NUMBER',
3903 'TAX3_RECOVERABLE_FLAG',
3904 'TAXABLE3_ACCOUNTED_AMOUNT',
3905 'TAXABLE3_ACCOUNTED_CR',
3906 'TAXABLE3_ACCOUNTED_DR',
3907 'TAXABLE3_ENTERED_AMOUNT',
3908 'TAXABLE3_ENTERED_CR',
3909 'TAXABLE3_ENTERED_DR',
3910 'TAX4_ACCOUNTED_AMOUNT',
3911 'TAX4_ACCOUNTED_CR',
3912 'TAX4_ACCOUNTED_DR',
3913 'TAX4_CODE',
3914 'TAX4_CODE_DESCRIPTION',
3915 'TAX4_CODE_NAME',
3916 'TAX4_CODE_RATE',
3917 'TAX4_CODE_REG_TYPE_CODE',
3918 'TAX4_CODE_REG_TYPE_MEANING',
3919 'TAX4_CODE_TAX_CLASS_CODE',
3920 'TAX4_CODE_TAX_CLASS_MEANING',
3921 'TAX4_CODE_TYPE_CODE',
3922 'TAX4_CODE_TYPE_MEANING',
3923 'TAX4_CODE_VAT_TRX_TYPE_CODE',
3924 'TAX4_CODE_VAT_TRX_TYPE_DESC',
3925 'TAX4_CODE_VAT_TRX_TYPE_MEANING',
3926 'TAX4_ENTERED_AMOUNT',
3927 'TAX4_ENTERED_CR',
3928 'TAX4_ENTERED_DR',
3929 'TAX4_LINE_EFFECTIVE_TAX_RATE',
3930 'TAX4_LINE_NUMBER',
3931 'TAX4_RECOVERABLE_FLAG',
3932 'TAXABLE4_ACCOUNTED_AMOUNT',
3933 'TAXABLE4_ACCOUNTED_CR',
3934 'TAXABLE4_ACCOUNTED_DR',
3935 'TAXABLE4_ENTERED_AMOUNT',
3936 'TAXABLE4_ENTERED_CR',
3937 'TAXABLE4_ENTERED_DR') );
3938
3939 Begin
3940 -- arp_util_tax.debug('ZX_EXTRACT_PKG.USE_MATRIX_REPORT: Product = '||
3941 -- P_PRODUCT );
3942
3943 l_use_matrix_rep := 'N';
3944
3945 if P_PRODUCT = 'AP' then
3946 open use_matrix_flag_csr_ap (p_report_id,p_attribute_set);
3947 fetch use_matrix_flag_csr_ap into l_use_matrix_rep;
3948 if use_matrix_flag_csr_ap%isopen then
3949 close use_matrix_flag_csr_ap;
3950 end if;
3951 else
3952 open use_matrix_flag_csr_ar (p_report_id,p_attribute_set);
3953 fetch use_matrix_flag_csr_ar into l_use_matrix_rep;
3954 if use_matrix_flag_csr_ar%isopen then
3955 close use_matrix_flag_csr_ar;
3956 end if;
3957 end if;
3958 return l_use_matrix_rep;
3959 exception
3960 when no_data_found then
3961 IF PG_DEBUG = 'Y' THEN
3962 arp_util_tax.debug('ZX_EXTRACT_PKG.UE_MATRIX_REP : NO_DATA_FOUND ');
3963 END IF;
3964 if use_matrix_flag_csr_ap%isopen then
3965 close use_matrix_flag_csr_ap;
3966 end if;
3967 if use_matrix_flag_csr_ar%isopen then
3968 close use_matrix_flag_csr_ar;
3969 end if;
3970 return ('N');
3971 when others then
3972 IF PG_DEBUG = 'Y' THEN
3973 arp_util_tax.debug('ZX_EXTRACT_PKG.UE_MATRIX_REP: '||SQLCODE
3974 ||' ; '||SQLERRM);
3975 END IF;
3976 if use_matrix_flag_csr_ap%isopen then
3977 close use_matrix_flag_csr_ap;
3978 end if;
3979 if use_matrix_flag_csr_ar%isopen then
3980 close use_matrix_flag_csr_ar;
3981 end if; */
3982 return('N');
3983 end;
3984
3985 /*===========================================================================+
3986 | PROCEDURE |
3987 | zx_upd_legal_reporting_status() |
3988 | |
3989 | DESCRIPTION |
3990 | This procedure is used to update the legal_reporting_status value |
3991 | on the zx_lines with the value passed as input to this procedure |
3992 | |
3993 | SCOPE - Public |
3994 | |
3995 | NOTES |
3996 | |
3997 | MODIFICATION HISTORY |
3998 | 24-Mar-2006 Ashwin Gurram Created |
3999 | |
4000 +===========================================================================*/
4001
4002 PROCEDURE ZX_UPD_LEGAL_REPORTING_STATUS(
4003 p_api_version IN NUMBER,
4004 p_init_msg_list IN VARCHAR2,
4005 p_commit IN VARCHAR2,
4006 p_validation_level IN VARCHAR2,
4007 p_application_id_tbl IN application_id_tbl,
4008 p_entity_code_tbl IN entity_code_tbl,
4009 p_event_class_code_tbl IN event_class_code_tbl,
4010 p_trx_id_tbl IN trx_id_tbl,
4011 p_trx_line_id_tbl IN trx_line_id_tbl,
4012 p_INTERNAL_ORGANIZATION_ID_tbl IN INTERNAL_ORGANIZATION_ID_TBL,
4013 p_TAX_LINE_ID_tbl IN TAX_LINE_ID_TBL,
4014 p_legal_reporting_status_val IN zx_lines.LEGAL_REPORTING_STATUS%type,
4015 x_return_status OUT NOCOPY VARCHAR2,
4016 x_msg_count OUT NOCOPY NUMBER,
4017 x_msg_data OUT NOCOPY VARCHAR2
4018 ) IS
4019
4020 l_count NUMBER := 0;
4021 l_counter_start NUMBER := 1 ;
4022 l_counter_end NUMBER := 0 ;
4023
4024 BEGIN
4025 x_return_status := FND_API.G_RET_STS_SUCCESS;
4026 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4027 IF (g_level_procedure >= g_current_runtime_level ) THEN
4028 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.zx_upd_legal_reporting_status.BEGIN',
4029 'ZX_EXTRACT_PKG:zx_upd_legal_reporting_status(+)');
4030 END IF;
4031
4032 l_count := p_application_id_tbl.COUNT ;
4033
4034 IF ( l_count > C_LINES_PER_INSERT ) THEN
4035 l_counter_end := C_LINES_PER_INSERT;
4036 ELSE
4037 l_counter_end := l_count ;
4038 END IF ;
4039
4040 LOOP
4041 IF ( l_counter_end <= l_count AND l_counter_start <= l_count ) THEN
4042
4043 FORALL i IN l_counter_start .. l_counter_end
4044 UPDATE ZX_LINES
4045 SET LEGAL_REPORTING_STATUS = p_legal_reporting_status_val,
4046 LAST_UPDATED_BY = fnd_global.user_id ,
4047 LAST_UPDATE_DATE = SYSDATE ,
4048 LAST_UPDATE_LOGIN = fnd_global.conc_login_id ,
4049 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
4050 WHERE application_id = p_application_id_tbl(i)
4051 AND entity_code = p_entity_code_tbl(i)
4052 AND event_class_code = p_event_class_code_tbl(i)
4053 AND trx_id = p_trx_id_tbl(i)
4054 AND trx_line_id = p_trx_line_id_tbl(i)
4055 AND INTERNAL_ORGANIZATION_ID = p_INTERNAL_ORGANIZATION_ID_tbl(i)
4056 AND TAX_LINE_ID = p_TAX_LINE_ID_tbl(i) ;
4057
4058 l_counter_start := l_counter_end + 1;
4059 IF ( l_counter_end + C_LINES_PER_INSERT < l_count ) THEN
4060 l_counter_end := l_counter_end + C_LINES_PER_INSERT;
4061 ELSE
4062 l_counter_end := l_count ;
4063 END IF ;
4064 ELSE
4065 EXIT ;
4066 END IF ;
4067
4068 END LOOP ;
4069
4070 IF (g_level_procedure >= g_current_runtime_level ) THEN
4071 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_EXTRACT_PKG.zx_upd_legal_reporting_status.END',
4072 'ZX_EXTRACT_PKG:zx_upd_legal_reporting_status(-)');
4073 END IF;
4074
4075 EXCEPTION
4076 WHEN OTHERS THEN
4077 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4078 FND_LOG.STRING(g_level_unexpected,
4079 'ZX.TRL.ZX_EXTRACT_PKG.zx_upd_legal_reporting_status',
4080 sqlerrm);
4081 END IF;
4082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4083 app_exception.raise_exception;
4084 END ZX_UPD_LEGAL_REPORTING_STATUS;
4085
4086 /*===========================================================================+
4087 | FUNCTION |
4088 | get_legal_message |
4089 | |
4090 | DESCRIPTION |
4091 | This function is used to retrieve the legal justification message |
4092 | for Sales transactions (Invoice CM and DM) |
4093 | |
4094 | SCOPE - Public |
4095 | |
4096 | PARAMETERS |
4097 | p_trx_id IN Transaction ID |
4098 | p_trx_line_id IN Transaction Line ID |
4099 | p_delimiter IN Delimiter for Header level call Default NULL |
4100 | |
4101 | NOTES |
4102 | |
4103 | MODIFICATION HISTORY |
4104 | 17-Nov-2009 Simranjeet Sohal Created |
4105 | 02-Dec-2009 Taniya Sen Modified |
4106 | |
4107 +===========================================================================*/
4108 FUNCTION get_legal_message
4109 (p_trx_id IN zx_lines.trx_id%TYPE,
4110 p_trx_line_id IN zx_lines.trx_line_id%TYPE,
4111 p_delimiter IN VARCHAR2 DEFAULT NULL)
4112 RETURN VARCHAR2 IS
4113
4114 /*----------------------------------------+
4115 | Cursor to retrieve legal message for |
4116 | a transaction at header level |
4117 +----------------------------------------*/
4118 CURSOR get_lgl_msg_header IS
4119 SELECT DISTINCT v1.reporting_code_name
4120 FROM (
4121 SELECT v.reporting_code_name,
4122 ROW_NUMBER() OVER (PARTITION BY v.trx_line_id
4123 ORDER BY v.tax_line_id, v.order_num) AS row_num
4124 FROM (
4125 SELECT DISTINCT rep_codes.reporting_code_name, zxl.trx_line_id, zxl.tax_line_id,1 order_num
4126 FROM zx_reporting_types_b rep_types,
4127 zx_reporting_codes_vl rep_codes,
4128 zx_lines zxl
4129 WHERE zxl.application_id = 222
4130 AND zxl.entity_code = 'TRANSACTIONS'
4131 AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
4132 AND zxl.trx_id = p_trx_id
4133 AND zxl.legal_message_rate = rep_codes.reporting_code_id
4134 AND rep_codes.reporting_type_id = rep_types.reporting_type_id
4135 AND rep_types.legal_message_flag = 'Y'
4136 UNION
4137 SELECT DISTINCT rep_codes.reporting_code_name, zxl.trx_line_id,zxl.tax_line_id, 2 order_num
4138 FROM zx_report_codes_assoc rep_assoc,
4139 zx_reporting_types_b rep_types,
4140 zx_reporting_codes_vl rep_codes,
4141 zx_lines zxl
4142 WHERE zxl.application_id = 222
4143 AND zxl.entity_code = 'TRANSACTIONS'
4144 AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
4145 AND zxl.trx_id = p_trx_id
4146 AND rep_assoc.entity_id = zxl.tax_rate_id
4147 AND rep_assoc.entity_code = 'ZX_RATES'
4148 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
4149 NVL(rep_assoc.effective_to, zxl.trx_date)
4150 AND rep_assoc.reporting_type_id = rep_types.reporting_type_id
4151 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
4152 AND rep_codes.reporting_type_id = rep_types.reporting_type_id
4153 AND rep_types.legal_message_flag = 'Y'
4154 UNION
4155 SELECT DISTINCT rep_codes.reporting_code_name, zxl.trx_line_id,zxl.tax_line_id, 3 order_num
4156 FROM zx_reporting_types_b rep_types,
4157 zx_reporting_codes_vl rep_codes,
4158 zx_lines zxl
4159 WHERE zxl.application_id = 222
4160 AND zxl.entity_code = 'TRANSACTIONS'
4161 AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
4162 AND zxl.trx_id = p_trx_id
4163 AND zxl.legal_message_status = rep_codes.reporting_code_id
4164 AND rep_codes.reporting_type_id = rep_types.reporting_type_id
4165 AND rep_types.legal_message_flag = 'Y'
4166 ) v
4167 ) v1
4168 WHERE v1.row_num = 1;
4169
4170 /*----------------------------------------+
4171 | Cursor to retrieve legal message for |
4172 | a transaction at Line level |
4173 +----------------------------------------*/
4174 CURSOR get_lgl_msg_line IS
4175 SELECT DISTINCT v1.reporting_code_name
4176 FROM (
4177 SELECT v.reporting_code_name,
4178 v.order_num
4179 FROM (
4180 SELECT DISTINCT rep_codes.reporting_code_name, zxl.tax_line_id,1 order_num
4181 FROM zx_reporting_types_b rep_types,
4182 zx_reporting_codes_vl rep_codes,
4183 zx_lines zxl
4184 WHERE zxl.application_id = 222
4185 AND zxl.entity_code = 'TRANSACTIONS'
4186 AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
4187 AND zxl.trx_id = p_trx_id
4188 AND zxl.trx_line_id = p_trx_line_id
4189 AND zxl.legal_message_rate = rep_codes.reporting_code_id
4190 AND rep_codes.reporting_type_id = rep_types.reporting_type_id
4191 AND rep_types.legal_message_flag = 'Y'
4192 UNION
4193 SELECT DISTINCT rep_codes.reporting_code_name, zxl.tax_line_id, 2 order_num
4194 FROM zx_report_codes_assoc rep_assoc,
4195 zx_reporting_types_b rep_types,
4196 zx_reporting_codes_vl rep_codes,
4197 zx_lines zxl
4198 WHERE zxl.application_id = 222
4199 AND zxl.entity_code = 'TRANSACTIONS'
4200 AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
4201 AND zxl.trx_id = p_trx_id
4202 AND zxl.trx_line_id = p_trx_line_id
4203 AND rep_assoc.entity_id = zxl.tax_rate_id
4204 AND rep_assoc.entity_code = 'ZX_RATES'
4205 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
4206 NVL(rep_assoc.effective_to, zxl.trx_date)
4207 AND rep_assoc.reporting_type_id = rep_types.reporting_type_id
4208 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
4209 AND rep_codes.reporting_type_id = rep_types.reporting_type_id
4210 AND rep_types.legal_message_flag = 'Y'
4211 UNION
4212 SELECT DISTINCT rep_codes.reporting_code_name, zxl.tax_line_id, 3 order_num
4213 FROM zx_reporting_types_b rep_types,
4214 zx_reporting_codes_vl rep_codes,
4215 zx_lines zxl
4216 WHERE zxl.application_id = 222
4217 AND zxl.entity_code = 'TRANSACTIONS'
4218 AND zxl.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
4219 AND zxl.trx_id = p_trx_id
4220 AND zxl.trx_line_id = p_trx_line_id
4221 AND zxl.legal_message_status = rep_codes.reporting_code_id
4222 AND rep_codes.reporting_type_id = rep_types.reporting_type_id
4223 AND rep_types.legal_message_flag = 'Y'
4224 ) v
4225 ORDER BY v.tax_line_id,v.order_num,v.reporting_code_name) v1
4226 WHERE rownum = 1;
4227
4228 -- Variables --
4229 l_lgl_msg VARCHAR2(240);
4230 l_legal_msg VARCHAR2(2000);
4231 l_is_first_msg VARCHAR2(1) := 'Y';
4232
4233 BEGIN
4234
4235 l_legal_msg := TO_CHAR(NULL);
4236
4237 -- Check for header level.
4238 IF p_trx_line_id IS NULL THEN
4239
4240 l_lgl_msg := TO_CHAR(NULL);
4241 OPEN get_lgl_msg_header;
4242 LOOP
4243 FETCH get_lgl_msg_header INTO l_lgl_msg;
4244
4245 EXIT WHEN get_lgl_msg_header%NOTFOUND;
4246
4247 IF l_lgl_msg IS NOT NULL THEN
4248 IF l_is_first_msg = 'Y' THEN
4249 l_legal_msg := l_legal_msg || l_lgl_msg;
4250 ELSE
4251 l_legal_msg := l_legal_msg || p_delimiter || l_lgl_msg;
4252 END IF;
4253 END IF;
4254
4255 -- is this the first message being fetched.
4256 IF l_lgl_msg IS NOT NULL AND l_is_first_msg = 'Y' THEN
4257 l_is_first_msg := 'N';
4258 END IF;
4259
4260 END LOOP;
4261 CLOSE get_lgl_msg_header;
4262
4263 ELSE
4264 l_lgl_msg := TO_CHAR(NULL);
4265
4266 OPEN get_lgl_msg_line;
4267 LOOP
4268 FETCH get_lgl_msg_line INTO l_lgl_msg;
4269 EXIT WHEN get_lgl_msg_line%NOTFOUND;
4270 l_legal_msg := l_legal_msg || l_lgl_msg;
4271 END LOOP;
4272
4273 CLOSE get_lgl_msg_line;
4274 END IF;
4275
4276 RETURN l_legal_msg;
4277
4278 EXCEPTION
4279 WHEN OTHERS THEN
4280 RETURN TO_CHAR(NULL);
4281
4282 END get_legal_message;
4283
4284 FUNCTION get_vat_transaction_code_name
4285 (p_tax_line_id IN zx_lines.tax_line_id%TYPE,
4286 p_esl_eu_trx_type IN VARCHAR2,
4287 p_esl_eu_goods IN VARCHAR2,
4288 p_esl_eu_services IN VARCHAR2,
4289 p_esl_eu_addl_code1 IN VARCHAR2,
4290 p_esl_eu_addl_code2 IN VARCHAR2,
4291 p_code_or_name IN VARCHAR2 DEFAULT 'NAME'
4292 )
4293 RETURN VARCHAR2 IS
4294
4295 /*----------------------------------------+
4296 | Cursor to retrieve legal message for |
4297 | a given entity on a given date |
4298 +----------------------------------------*/
4299 CURSOR get_lgl_msg IS
4300 SELECT v1.reporting_code_name, v1.reporting_code_char_value
4301 FROM
4302 (SELECT v.reporting_code_name, v.reporting_code_char_value
4303 FROM (
4304 SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 1 order_num
4305 FROM zx_report_codes_assoc rep_assoc,
4306 zx_reporting_types_b rep_types,
4307 zx_reporting_codes_vl rep_codes,
4308 zx_lines zxl
4309 WHERE zxl.tax_line_id = p_tax_line_id
4310 AND rep_types.reporting_type_id = p_esl_eu_trx_type
4311 AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
4312 AND rep_assoc.entity_id = NVL(zxl.direct_rate_result_id, zxl.rate_result_id)
4313 AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
4314 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
4315 NVL(rep_assoc.effective_to, zxl.trx_date)
4316 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
4317 AND ((p_esl_eu_goods IS NOT NULL
4318 AND rep_codes.reporting_code_id = p_esl_eu_goods) OR
4319 (p_esl_eu_services IS NOT NULL
4320 AND rep_codes.reporting_code_id = p_esl_eu_services) OR
4321 (p_esl_eu_addl_code1 IS NOT NULL
4322 AND rep_codes.reporting_code_id = p_esl_eu_addl_code1) OR
4323 (p_esl_eu_addl_code2 IS NOT NULL
4324 AND rep_codes.reporting_code_id = p_esl_eu_addl_code2))
4325 UNION
4326 SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 2 order_num
4327 FROM zx_report_codes_assoc rep_assoc,
4328 zx_reporting_types_b rep_types,
4329 zx_reporting_codes_vl rep_codes,
4330 zx_lines zxl
4331 WHERE zxl.tax_line_id = p_tax_line_id
4332 AND rep_types.reporting_type_id = p_esl_eu_trx_type
4333 AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
4334 AND rep_assoc.entity_id = zxl.tax_rate_id
4335 AND rep_assoc.entity_code = 'ZX_RATES'
4336 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
4337 NVL(rep_assoc.effective_to, zxl.trx_date)
4338 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
4339 AND ((p_esl_eu_goods IS NOT NULL
4340 AND rep_codes.reporting_code_id = p_esl_eu_goods) OR
4341 (p_esl_eu_services IS NOT NULL
4342 AND rep_codes.reporting_code_id = p_esl_eu_services) OR
4343 (p_esl_eu_addl_code1 IS NOT NULL
4344 AND rep_codes.reporting_code_id = p_esl_eu_addl_code1) OR
4345 (p_esl_eu_addl_code2 IS NOT NULL
4346 AND rep_codes.reporting_code_id = p_esl_eu_addl_code2))
4347 UNION
4348 SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 3 order_num
4349 FROM zx_report_codes_assoc rep_assoc,
4350 zx_reporting_types_b rep_types,
4351 zx_reporting_codes_vl rep_codes,
4352 zx_lines zxl
4353 WHERE zxl.tax_line_id = p_tax_line_id
4354 AND rep_types.reporting_type_id = p_esl_eu_trx_type
4355 AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
4356 AND rep_assoc.entity_id = zxl.status_result_id
4357 AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
4358 AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
4359 NVL(rep_assoc.effective_to, zxl.trx_date)
4360 AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
4361 AND ((p_esl_eu_goods IS NOT NULL
4362 AND rep_codes.reporting_code_id = p_esl_eu_goods) OR
4363 (p_esl_eu_services IS NOT NULL
4364 AND rep_codes.reporting_code_id = p_esl_eu_services) OR
4365 (p_esl_eu_addl_code1 IS NOT NULL
4366 AND rep_codes.reporting_code_id = p_esl_eu_addl_code1) OR
4367 (p_esl_eu_addl_code2 IS NOT NULL
4368 AND rep_codes.reporting_code_id = p_esl_eu_addl_code2))
4369 ) v
4370 WHERE v.reporting_code_name IS NOT NULL
4371 ORDER BY v.order_num,v.reporting_code_name) v1
4372 WHERE ROWNUM = 1;
4373
4374 -- Variables --
4375 l_trx_date DATE;
4376 l_tax_rate_id NUMBER;
4377 l_direct_rate_result_id NUMBER;
4378 l_rate_result_id NUMBER;
4379 l_status_result_id NUMBER;
4380
4381 l_rep_code zx_reporting_codes_b.reporting_code_char_value%type;
4382 l_rep_name zx_reporting_codes_tl.reporting_code_name%type;
4383
4384 BEGIN
4385
4386 l_rep_name := TO_CHAR(NULL);
4387 l_rep_code := TO_CHAR(NULL);
4388
4389 OPEN get_lgl_msg;
4390 FETCH get_lgl_msg INTO l_rep_name, l_rep_code;
4391 CLOSE get_lgl_msg;
4392
4393 IF p_code_or_name = 'CODE' THEN
4394 RETURN l_rep_code;
4395 ELSIF p_code_or_name = 'NAME' THEN
4396 RETURN l_rep_name;
4397 ELSE
4398 RETURN l_rep_name;
4399 END IF;
4400
4401 EXCEPTION
4402 WHEN OTHERS THEN
4403 RETURN TO_CHAR(NULL);
4404
4405 END get_vat_transaction_code_name;
4406 /*=========================================================================+
4407 | PACKAGE Constructor |
4408 | |
4409 | |
4410 | DESCRIPTION |
4411 | The constructor initializes the global variables and displays the |
4412 | version of the package in the debug file |
4413 | |
4414 | MODIFICATION HISTORY |
4415 | 14-July-99 Nilesh Patel Created |
4416 | |
4417 +=========================================================================*/
4418
4419 BEGIN
4420 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4421
4422 IF (g_level_procedure >= g_current_runtime_level ) THEN
4423
4424 l_version_info := NULL;
4425 select substr(text,5,70) into l_version_info from user_source
4426 where name = 'ZX_EXTRACT_PKG'
4427 and text like '%Header:%'
4428 and type = 'PACKAGE BODY'
4429 and line < 10;
4430 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
4431 l_version_info);
4432 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
4433 'g_current_runtime_level :'||to_char(g_current_runtime_level));
4434 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
4435 'g_level_procedure :'||to_char(g_level_procedure));
4436 FND_LOG.STRING(g_level_procedure, 'ZX_EXTRACT_PKG version info :',
4437 'g_level_statement :'||to_char(g_level_statement));
4438 END IF;
4439
4440 IF (g_level_procedure >= g_current_runtime_level ) THEN
4441 l_version_info := NULL;
4442 select substr(text,5,70) into l_version_info from user_source
4443 where name = 'ZX_AR_EXTRACT_PKG'
4444 and text like '%Header:%'
4445 and type = 'PACKAGE BODY'
4446 and line < 10;
4447
4448 FND_LOG.STRING(g_level_procedure, 'ZX_AR_EXTRACT_PKG version info :',
4449 l_version_info);
4450 END IF;
4451
4452 IF (g_level_procedure >= g_current_runtime_level ) THEN
4453 l_version_info := NULL;
4454 select substr(text,5,70) into l_version_info from user_source
4455 where name = 'ZX_AP_EXTRACT_PKG'
4456 and text like '%Header:%'
4457 and type = 'PACKAGE BODY'
4458 and line < 10;
4459 FND_LOG.STRING(g_level_procedure, 'ZX_AP_EXTRACT_PKG version info :',
4460 l_version_info);
4461 END IF;
4462
4463 IF (g_level_procedure >= g_current_runtime_level ) THEN
4464 l_version_info := NULL;
4465 select substr(text,5,70) into l_version_info from user_source
4466 where name = 'ZX_AR_POPULATE_PKG'
4467 and text like '%Header:%'
4468 and type = 'PACKAGE BODY'
4469 and line < 10;
4470 FND_LOG.STRING(g_level_procedure, 'ZX_AR_POPULATE_PKG version info :',
4471 l_version_info);
4472 END IF;
4473
4474 IF (g_level_procedure >= g_current_runtime_level ) THEN
4475 l_version_info := NULL;
4476 select substr(text,5,70) into l_version_info from user_source
4477 where name = 'ZX_AP_POPULATE_PKG'
4478 and text like '%Header:%'
4479 and type = 'PACKAGE BODY'
4480 and line < 10;
4481 FND_LOG.STRING(g_level_procedure, 'ZX_AP_POPULATE_PKG version info :',
4482 l_version_info);
4483 END IF;
4484
4485 IF (g_level_procedure >= g_current_runtime_level ) THEN
4486 l_version_info := NULL;
4487 select substr(text,5,70) into l_version_info from user_source
4488 where name = 'ZX_GL_EXTRACT_PKG'
4489 and text like '%Header:%'
4490 and type = 'PACKAGE BODY'
4491 and line < 10;
4492 FND_LOG.STRING(g_level_procedure, 'ZX_GL_EXTRACT_PKG version info :',
4493 l_version_info);
4494 END IF;
4495
4496 -- END;
4497
4498 END ZX_EXTRACT_PKG;