[Home] [Help]
PACKAGE BODY: APPS.ZX_TDS_PROCESS_CEC_PVT
Source
1 PACKAGE BODY ZX_TDS_PROCESS_CEC_PVT as
2 /* $Header: zxdilcecevalpvb.pls 120.17.12010000.2 2009/04/02 06:58:12 ssanka ship $ */
3
4 ----------------------------
5 -- Constants
6 ----------------------------
7 IDX_SHIP_FROM CONSTANT NUMBER := 1;
8 IDX_SHIP_TO CONSTANT NUMBER := 2;
9 IDX_POO CONSTANT NUMBER := 3;
10 IDX_POA CONSTANT NUMBER := 4;
11 IDX_BILL_TO CONSTANT NUMBER := 5;
12 IDX_TRX CONSTANT NUMBER := 6;
13 IDX_ITEM CONSTANT NUMBER := 7;
14 IDX_TAX_CODE CONSTANT NUMBER := 8;
15 STATS_INIT CONSTANT NUMBER := 0;
16 STATS_TRUE CONSTANT NUMBER := 1;
17 STATS_FALSE CONSTANT NUMBER := -1;
18
19 EXP_ERROR_MESSAGE EXCEPTION;
20
21 pr_flexfield FND_DFLEX.DFLEX_R;
22 pr_flexinfo FND_DFLEX.DFLEX_DR;
23 pr_segments FND_DFLEX.SEGMENTS_DR;
24 pr_contexts FND_DFLEX.CONTEXTS_DR;
25
26 pr_tax_rate NUMBER;
27 pr_do_not_use_this_tax_flag BOOLEAN;
28 pr_do_not_use_this_group_flag BOOLEAN;
29 pr_message_token VARCHAR2(2500);
33 -------------------------------------------------
30
31 pr_action_rec_tbl action_rec_tbl_type;
32
34 -- Creating Table structure for Statistics info
35 ------------------------------------------------
36 TYPE pr_site_use_rec_type is RECORD(
37 tax_classification hz_cust_site_uses_all.tax_classification%type,
38 cust_acct_site_id hz_cust_site_uses_all.cust_acct_site_id%type,
39 site_use_id hz_cust_site_uses_all.site_use_id%type);
40
41 pr_site_use_rec pr_site_use_rec_type;
42
43 TYPE pr_site_loc_rec_type is RECORD(
44 COUNTRY HZ_LOCATIONS.COUNTRY%TYPE,
45 STATE HZ_LOCATIONS.STATE%TYPE,
46 COUNTY HZ_LOCATIONS.COUNTY%TYPE,
47 PROVINCE HZ_LOCATIONS.PROVINCE%TYPE,
48 CITY HZ_LOCATIONS.CITY%TYPE,
49 CUST_ACCT_SITE_ID HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID%TYPE);
50
51 pr_site_loc_rec pr_site_loc_rec_type;
52
53 TYPE pr_org_loc_rec_type is RECORD(
54 COUNTRY HR_LOCATIONS_ALL.COUNTRY%TYPE,
55 TOWN_OR_CITY HR_LOCATIONS_ALL.TOWN_OR_CITY%TYPE,
56 REGION_1 HR_LOCATIONS_ALL.REGION_1%TYPE,
57 REGION_2 HR_LOCATIONS_ALL.REGION_2%TYPE,
58 REGION_3 HR_LOCATIONS_ALL.REGION_3%TYPE,
59 ADDRESS_LINE_1 HR_LOCATIONS_ALL.ADDRESS_LINE_1%TYPE,
60 ADDRESS_LINE_2 HR_LOCATIONS_ALL.ADDRESS_LINE_2%TYPE,
61 ADDRESS_LINE_3 HR_LOCATIONS_ALL.ADDRESS_LINE_3%TYPE,
62 POSTAL_CODE HR_LOCATIONS_ALL.POSTAL_CODE%TYPE,
63 TELEPHONE_NUMBER_1 HR_LOCATIONS_ALL.TELEPHONE_NUMBER_1%TYPE,
64 TELEPHONE_NUMBER_2 HR_LOCATIONS_ALL.TELEPHONE_NUMBER_2%TYPE,
65 TELEPHONE_NUMBER_3 HR_LOCATIONS_ALL.TELEPHONE_NUMBER_3%TYPE,
66 STYLE HR_LOCATIONS_ALL.STYLE%TYPE,
67 LOCATION_ID HR_LOCATIONS_ALL.LOCATION_ID%TYPE -- bug fix 4417523
68 -- ORGANIZATION_ID NUMBER
69 );
70
71 pr_org_loc_rec pr_org_loc_rec_type;
72
73 TYPE pr_stats_rec_type is RECORD(
74 country NUMBER,
75 state NUMBER,
76 county NUMBER,
77 city NUMBER,
78 province NUMBER,
79 fob NUMBER,
80 tax_classification NUMBER,
81 type NUMBER,
82 user_item_type NUMBER,
83 vat_reg_num NUMBER,
84 warehouse NUMBER);
85
86 TYPE pr_stats_rec_tbl_type is TABLE of pr_stats_rec_type
87 index by binary_integer;
88
89 pr_stats_rec_tbl pr_stats_rec_tbl_type;
90 pr_stats_default_rec pr_stats_rec_type;
91
92 g_current_runtime_level NUMBER;
93
94 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
95 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
96 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
97
98 -- Parameters required for evaluating Constraint, Condition Set and Exception Set.
99 g_cec_ship_to_party_site_id NUMBER;
100 g_cec_bill_to_party_site_id NUMBER;
101 g_cec_ship_to_party_id NUMBER;
102 g_cec_bill_to_party_id NUMBER;
103 g_cec_poo_location_id NUMBER;
104 g_cec_poa_location_id NUMBER;
105 g_cec_trx_id NUMBER;
106 g_cec_trx_line_id NUMBER;
107 g_cec_ledger_id NUMBER;
108 g_cec_internal_organization_id NUMBER;
109 g_cec_so_organization_id NUMBER;
110 g_cec_product_org_id NUMBER;
111 g_cec_product_id NUMBER;
112 g_cec_trx_type_id NUMBER;
113 g_cec_trx_line_date DATE;
114 g_cec_fob_point VARCHAR2(30);
115 g_cec_ship_to_site_use_id NUMBER;
116 g_cec_bill_to_site_use_id NUMBER;
117
118 -- bug fix 4417523
119 -- global cache variables for HR Address location
120 pg_column VARCHAR2(150);
121 pg_classification VARCHAR2(80);
122
123 /*===========================================================================+
124 | PROCEDURE |
125 | init_stats_rec_tbl |
126 | |
127 | DESCRIPTION |
128 | The purpose of this procedure is to initialize the pr_stats_rec_tbl |
129 | nested table |
130 | SCOPE - Private |
131 | |
132 +===========================================================================*/
133
134 procedure init_stats_rec_tbl is
135 begin
136
137 pr_stats_default_rec.country := 0;
138 pr_stats_default_rec.state := 0;
139 pr_stats_default_rec.county := 0;
140 pr_stats_default_rec.city := 0;
141 pr_stats_default_rec.province := 0;
142 pr_stats_default_rec.fob := 0;
143 pr_stats_default_rec.tax_classification := 0;
144 pr_stats_default_rec.type := 0;
145 pr_stats_default_rec.user_item_type := 0;
146 pr_stats_default_rec.vat_reg_num := 0;
147 pr_stats_default_rec.warehouse := 0;
148
149
150 pr_stats_rec_tbl(IDX_SHIP_FROM) := pr_stats_default_rec;
151 pr_stats_rec_tbl(IDX_SHIP_TO) := pr_stats_default_rec;
152 pr_stats_rec_tbl(IDX_POO) := pr_stats_default_rec;
153 pr_stats_rec_tbl(IDX_POA) := pr_stats_default_rec;
154 pr_stats_rec_tbl(IDX_BILL_TO) := pr_stats_default_rec;
158
155 pr_stats_rec_tbl(IDX_TRX) := pr_stats_default_rec;
156 pr_stats_rec_tbl(IDX_ITEM) := pr_stats_default_rec;
157 pr_stats_rec_tbl(IDX_TAX_CODE) := pr_stats_default_rec;
159
160 end;
161
162
163 /*===========================================================================+
164 | PROCEDURE |
165 | evaluate_cec_lines |
166 | |
167 | DESCRIPTION |
168 | Evaluate Compiled Condition of Lines. If condition is evaluates to TRUE,|
169 | return TRUE, else FALSE. |
170 | |
171 | SCOPE - Private |
172 | |
173 +===========================================================================*/
174 function evaluate_cec_lines (p_compiled_condition IN VARCHAR2) return BOOLEAN is
175
176 l_cursor INTEGER;
177 l_ignore INTEGER;
178
179 l_temp NUMBER;
180 l_result BOOLEAN;
181 l_exception_result BOOLEAN;
182
183 l_and_condition VARCHAR2(2500);
184
185 begin
186
187 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
188
189
190 IF (g_level_statement >= g_current_runtime_level ) THEN
191 FND_LOG.STRING(g_level_statement,
192 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines.BEGIN',
193 'ZX_TDS_PROCESS_CEC_PVT: evaluate_cec_lines(+)');
194
195 END IF;
196
197 l_temp :=0;
198 l_result := FALSE;
199 l_exception_result := FALSE;
200
201 if (p_compiled_condition is null) then
202 l_result := TRUE;
203 else
204 BEGIN
205
206 /* comment out for bug 4417523 begin: no need to execute the condition twice
207
208 -- Replace 'OR' with 'AND' sot that all the functions in
209 -- the conditions get called.
210 l_and_condition := replace(upper(p_compiled_condition), ' OR ', ' AND ');
211
212 -- First Execute with all "AND" only. Each function in the condition
213 -- sets the global flag accordingly.
214 IF (g_level_statement >= g_current_runtime_level ) THEN
215 FND_LOG.STRING(g_level_statement,
216 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines',
217 'Compiled Condition: ' || l_and_condition);
218 END IF;
219
220 EXECUTE IMMEDIATE 'BEGIN '|| l_and_condition ||'
221 THEN :temp := 1; else :temp := 0; END IF; END;' USING OUT l_temp ;
222
223 IF (g_level_statement >= g_current_runtime_level ) THEN
224 IF l_temp = 1 THEN
225 FND_LOG.STRING(g_level_statement,
226 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines', 'Condition is satisfied ');
227 ELSE
228 FND_LOG.STRING(g_level_statement,
229 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines', 'Condition is NOT satisfied ');
230 END IF;
231 END IF;
232 end comment out for bug 4417523 */
233 --
234 -- Now execute with original conditions to evaluate.
235 --
236 IF (g_level_statement >= g_current_runtime_level ) THEN
237 FND_LOG.STRING(g_level_statement,
238 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines',
239 'Compiled Condition: ' || p_compiled_condition);
240 END IF;
241
242 EXECUTE IMMEDIATE 'BEGIN '|| p_compiled_condition||
243 'THEN :temp := 1; ELSE :temp := 0;END IF; END;' USING IN OUT l_temp ;
244
245 IF (g_level_statement >= g_current_runtime_level ) THEN
246 IF l_temp = 1 THEN
247 FND_LOG.STRING(g_level_statement,
248 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines', 'Condition is satisfied ');
249 ELSE
250 FND_LOG.STRING(g_level_statement,
251 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines', 'Condition is NOT satisfied ');
252 END IF;
253 END IF;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF (g_level_unexpected >= g_current_runtime_level ) THEN
258 FND_LOG.STRING(g_level_unexpected,
259 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines', 'Error Message: ' || sqlerrm);
260 END IF;
261 app_exception.raise_exception;
262 END;
263 if l_temp = 1 then
264 l_result := TRUE;
265 else
266 l_result := FALSE;
267 end if;
268 end if;
269 IF (g_level_statement >= g_current_runtime_level ) THEN
270
271 FND_LOG.STRING(g_level_statement,
272 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_lines.END',
273 'ZX_TDS_PROCESS_CEC_PVT: evaluate_cec_lines(-)');
274 END IF;
275
276 return l_result;
277 end evaluate_cec_lines;
278
279 /*===========================================================================+
280 | PROCEDURE |
281 | evaluate_cec_action |
282 | |
283 | DESCRIPTION |
284 | Execute Compiled condition of Action. |
288 +===========================================================================*/
285 | |
286 | SCOPE - Private |
287 | |
289 procedure evaluate_cec_action(p_compiled_action IN VARCHAR2,
290 p_type IN VARCHAR2) is
291
292 l_cursor INTEGER;
293 l_ignore INTEGER;
294 l_msg VARCHAR2(60);
295
296 begin
297
298 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
299 IF (g_level_statement >= g_current_runtime_level ) THEN
300 FND_LOG.STRING(g_level_statement,
301 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action.BEGIN',
302 'ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action (+)');
303 END IF;
304
305 pr_do_not_use_this_tax_flag := FALSE;
306 pr_do_not_use_this_group_flag := FALSE;
307
308 if (p_compiled_action is not null) then
309 BEGIN
310
311 IF (g_level_statement >= g_current_runtime_level ) THEN
312 FND_LOG.STRING(g_level_statement,
313 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action',
314 'Compiled Action: ' || p_compiled_action);
315 END IF;
316
317 IF (instr(p_compiled_action, 'ZX_TDS_PROCESS_CEC_PVT.USE_THIS_TAX_CODE(') >0) THEN
318 IF (g_level_statement >= g_current_runtime_level ) THEN
319 FND_LOG.STRING(g_level_statement,
320 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action',
321 'Use this Tax');
322 END IF;
323 ELSIF (instr(p_compiled_action, 'ZX_TDS_PROCESS_CEC_PVT.DO_NOT_USE_THIS_TAX_CODE(') >0) THEN
324 IF (g_level_statement >= g_current_runtime_level ) THEN
325 FND_LOG.STRING(g_level_statement,
326 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action',
327 'DO NOT use this Tax');
328 END IF;
329 pr_do_not_use_this_tax_flag := TRUE;
330
331 ELSIF (instr(p_compiled_action, 'ZX_TDS_PROCESS_CEC_PVT.USE_THIS_TAX_GROUP(') >0) THEN
332 IF (g_level_statement >= g_current_runtime_level ) THEN
333 FND_LOG.STRING(g_level_statement,
334 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action',
335 'Use this Tax Condition Group');
336 END IF;
337 ELSIF (instr(p_compiled_action, 'ZX_TDS_PROCESS_CEC_PVT.DO_NOT_USE_THIS_TAX_GROUP(') >0) THEN
338 IF (g_level_statement >= g_current_runtime_level ) THEN
339 FND_LOG.STRING(g_level_statement,
340 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action',
341 'DO NOT use this Tax Condition Group');
342 END IF;
343 pr_do_not_use_this_group_flag := TRUE;
344
345 ELSE
346 EXECUTE IMMEDIATE 'BEGIN '||p_compiled_action||' END;' ;
347 END IF;
348
349 if(pr_message_token is not null) then
350 app_exception.raise_exception;
351 end if;
352 EXCEPTION
353 WHEN OTHERS THEN
354 IF (g_level_statement >= g_current_runtime_level ) THEN
355 FND_LOG.STRING(g_level_statement,
356 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action',
357 'Error Message: ' || sqlerrm);
358 END IF;
359 app_exception.raise_exception;
360 END;
361 end if;
362 IF (g_level_statement >= g_current_runtime_level ) THEN
363
364 FND_LOG.STRING(g_level_statement,
365 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action.END',
366 'ZX_TDS_PROCESS_CEC_PVT.evaluate_cec_action (-)');
367 END IF;
368 end evaluate_cec_action;
369
370 /*===========================================================================+
371 | FUNCTION |
372 | create_compiled_lines |
373 | |
374 | DESCRIPTION |
375 | Compile the Condition for Lines. |
376 | |
377 | SCOPE - Private |
378 | |
379 +===========================================================================*/
380
381 function create_compiled_lines(p_cec_id IN NUMBER) return VARCHAR2 is
382
383 l_compiled_line VARCHAR2(2500);
384 l_end VARCHAR2(1);
385
386 CURSOR cec_csr (c_cec_id NUMBER) is
387 select upper(tax_condition_clause) tax_condition_clause,
388 upper(tax_condition_entity) tax_condition_entity,
389 upper(tax_condition_field) tax_condition_field,
390 upper(tax_condition_operator) tax_condition_operator,
391 upper(tax_condition_value) tax_condition_value
392 from ar_tax_condition_lines_all
393 where tax_condition_id = c_cec_id
394 order by display_order;
395
396 begin
397
398 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
399 IF (g_level_statement >= g_current_runtime_level ) THEN
400 FND_LOG.STRING(g_level_statement,
401 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.create_compiled_lines.BEGIN',
402 'ZX_TDS_PROCESS_CEC_PVT.create_compiled_lines (+)');
403
404 END IF;
405
406 for cec_rec in cec_csr(p_cec_id) LOOP
407 --
408 -- IF, OR, AND
409 --
410 l_compiled_line := l_compiled_line || cec_rec.tax_condition_clause ||' ';
411 --
412 -- SHIP_FROM, SHIP_TO, POO, POA, ITEM, TRX, BILL_TO ...
413 --
414 if (cec_rec.tax_condition_entity is not null) then
415 l_compiled_line := l_compiled_line ||
416 'ZX_TDS_PROCESS_CEC_PVT.'|| cec_rec.tax_condition_entity||'(';
417 l_end := ')';
418 end if;
419 --
420 -- COUNTRY, STATE, PROVINCE, CITY, TAX_CLASSIFICATION ...
421 --
422 if (cec_rec.tax_condition_field is not null) then
423 l_compiled_line := l_compiled_line || ''''||cec_rec.tax_condition_field||''',';
424 else
425 l_compiled_line := l_compiled_line ||'NULL,';
426 end if;
427 --
428 -- =, <, >, <=, >= ...
429 --
430 if (cec_rec.tax_condition_operator is not null) then
431 l_compiled_line := l_compiled_line || ''''||cec_rec.tax_condition_operator||''',';
432 else
433 l_compiled_line := l_compiled_line ||'NULL,';
434 end if;
435 --
436 -- ONTARIO, OTTAWA, JAPAN, ENGLAND, CA ...
437 --
438 if (cec_rec.tax_condition_value is not null) then
439 l_compiled_line := l_compiled_line || ''''||cec_rec.tax_condition_value||'''';
440 else
441 l_compiled_line := l_compiled_line ||'NULL';
442 end if;
443 --
444 l_compiled_line := l_compiled_line || l_end || ' ';
445 --
446 -- By Now l_compiled_line should look like
447 -- IF SHIP_FROM('PROVINCE','=','ONTARIO')
448 --
449 end LOOP;
450
451 IF (g_level_statement >= g_current_runtime_level ) THEN
452
453 FND_LOG.STRING(g_level_statement,
454 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.create_compiled_lines.END',
455 'ZX_TDS_PROCESS_CEC_PVT.create_compiled_lines (-)');
456 END IF;
457 return l_compiled_line;
458 end create_compiled_lines;
459
460 /*===========================================================================+
461 | FUNCTION |
462 | create_compiled_action |
463 | |
464 | DESCRIPTION |
465 | Compile the condition for Actions. |
466 | |
467 | SCOPE - Private |
468 | |
469 +===========================================================================*/
470
471 function create_compiled_action(p_cec_id IN NUMBER,
472 p_action_type IN VARCHAR2) return VARCHAR2 is
473
474 l_compiled_action VARCHAR2(4000);
475 l_end VARCHAR2(2);
476
477 CURSOR action_csr (c_cec_id NUMBER,
478 c_action_type VARCHAR2) is
479 select upper(tax_condition_action_code) tax_condition_action_code,
480 decode(upper(tax_condition_action_code),
481 'USER_MESSAGE',tax_condition_action_value,
482 'DEFAULT_TAX_CODE', tax_condition_action_value,
483 upper(tax_condition_action_value)) tax_condition_action_value
484 from ar_tax_condition_actions_all
485 where tax_condition_id = c_cec_id
486 and tax_condition_action_type = c_action_type
487 order by display_order;
488 l_counter NUMBER;
489
490 begin
491
492 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
493 IF (g_level_statement >= g_current_runtime_level ) THEN
494 FND_LOG.STRING(g_level_statement,
495 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.create_compiled_action.BEGIN',
496 'ZX_TDS_PROCESS_CEC_PVT.create_compiled_action (+)');
497
498 END IF;
499 l_counter := 1;
500 pr_action_rec_tbl.delete;
501
502 for action_rec in action_csr(p_cec_id, p_action_type)
503 LOOP
504 -- ERROR_MESSAGE, SYSTEM_ERROR, USE_TAX_CODE ...
505 if (action_rec.tax_condition_action_code is not null) then
506 l_compiled_action := l_compiled_action || 'ZX_TDS_PROCESS_CEC_PVT.'||
507 action_rec.tax_condition_action_code ||'(';
508 l_end := ');';
509 end if;
510
511 pr_action_rec_tbl(l_counter).tax_condition_id := p_cec_id;
512 pr_action_rec_tbl(l_counter).action_type := p_action_type;
516
513 pr_action_rec_tbl(l_counter).action_code := action_rec.tax_condition_action_code;
514 pr_action_rec_tbl(l_counter).action_value := action_rec.tax_condition_action_value;
515 l_counter := l_counter + 1;
517 if (action_rec.tax_condition_action_value is not null) then
518 l_compiled_action := l_compiled_action ||
519 ''''||action_rec.tax_condition_action_value||'''';
520 else
521 l_compiled_action := l_compiled_action ||'NULL';
522 end if;
523
524 l_compiled_action := l_compiled_action || l_end;
525
526 end LOOP;
527
528 IF (g_level_statement >= g_current_runtime_level ) THEN
529
530 FND_LOG.STRING(g_level_statement,
531 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.create_compiled_action.END',
532 'ZX_TDS_PROCESS_CEC_PVT.create_compiled_action (-)');
533 END IF;
534 return l_compiled_action;
535 end create_compiled_action;
536
537 /*----------------------------------------------------------------------------*
538 | FUNCTION |
539 | get_hr_location |
540 | |
541 | DESCRIPTION |
542 | This function gets the location information from HR_LOCATION_V |
543 | Based on the input parameter p_classification, and the structure |
544 | of 'Location Address' Flexfield,the function maps appropriate |
545 | application_column_name to either county, state, county, province |
546 | or city. |
547 | |
548 | SCOPE - Private |
549 | |
550 *----------------------------------------------------------------------------*/
551
552 FUNCTION get_hr_location (
553 p_organization_id IN NUMBER,
554 p_location_id IN NUMBER,
555 p_classification IN VARCHAR2)
556 return VARCHAR2 is
557
558 l_classification varchar2(30);
559 l_column VARCHAR2(30);
560 l_column_value VARCHAR2(150);
561 l_location_id NUMBER;
562
563 -- performance bug fix 4417523
564 cursor c_loc_rec (c_location_id NUMBER)is
565 select
566 loc.COUNTRY,
567 loc.TOWN_OR_CITY,
568 loc.REGION_1,
569 loc.REGION_2,
570 loc.REGION_3,
571 loc.ADDRESS_LINE_1,
572 loc.ADDRESS_LINE_2,
573 loc.ADDRESS_LINE_3,
574 loc.POSTAL_CODE,
575 loc.TELEPHONE_NUMBER_1,
576 loc.TELEPHONE_NUMBER_2,
577 loc.TELEPHONE_NUMBER_3,
578 loc.STYLE,
579 loc.LOCATION_ID
580 from hr_locations_all loc,
581 fnd_descr_flex_contexts fnd
582 where loc.location_id = c_location_id
583 AND loc.style = fnd.descriptive_flex_context_code
584 AND fnd.application_id = 800 -- Application_short_name 'PER'
585 AND fnd.descriptive_flexfield_name = 'Address Location'
586 AND fnd.enabled_flag IN ('Y', 'y')
587 AND NVL (loc.business_group_id, NVL (hr_general.get_business_group_id, -99)) =
588 NVL (hr_general.get_business_group_id, NVL (loc.business_group_id, -99));
589
590 cursor c_get_location_id(c_organization_id NUMBER) is
591 select location_id
592 from hr_organization_units
593 where organization_id = c_organization_id;
594
595 BEGIN
596 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
597
598 IF (g_level_statement >= g_current_runtime_level ) THEN
599 FND_LOG.STRING(g_level_statement,
600 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_hr_location.BEGIN',
601 'ZX_TDS_PROCESS_CEC_PVT.get_hr_location (+)');
602
603 END IF;
604
605 IF p_organization_id is not NULL then
606 open c_get_location_id(p_organization_id);
607 fetch c_get_location_id into l_location_id;
608 close c_get_location_id;
609 ELSE
610 l_location_id := p_location_id; -- when organization_id is NULL, we expect location_id to be passed.
611 -- For POO, POA, the location_id is directly passed, so no need to use
612 -- the cursor c_location_id.
613 END IF;
614
615 -- begin bug fix 4417523
616 IF NVL(pr_org_loc_rec.location_id, -1) <> NVL(l_location_id,-2) THEN
617
618 open c_loc_rec(l_location_id);
619 fetch c_loc_rec into pr_org_loc_rec;
620
621 IF c_loc_rec%FOUND THEN
622 -- Based on the address style, the columns in hr_locations_v are used to store
623 -- different components of a locations's address. Hence, you have to call the
624 -- descriptive flexfield API's to find out how the DFF 'Address Location' has
625 -- been setup and pick up the correct column,which corresponds to the input
626 -- parameter, p_classification. The function get_clocation_column
627 -- performs this function.
628 -- l_style := pr_org_loc_rec.style;
629
630 l_column := upper(get_location_column(
631 p_style => pr_org_loc_rec.style,
632 p_classification => p_classification));
633
634 END IF;
635
636 pg_column := l_column;
637 pg_classification := p_classification;
638
639 CLOSE c_loc_rec;
640
641 ELSE -- organization / location is found in cache
642
643 IF pg_classification IS NULL OR pg_classification <> p_classification THEN
644
645 IF (g_level_statement >= g_current_runtime_level ) THEN
646 FND_LOG.STRING(g_level_statement,
647 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_hr_location',
648 'Same organization but different classification');
649
650 END IF;
651
652 l_column := UPPER(get_location_column(
653 p_style => pr_org_loc_rec.style,
654 p_classification => p_classification));
655
656 pg_column := l_column;
657 pg_classification := p_classification;
658 ELSE --Same organization and same classificationas cached
659
660 IF (g_level_statement >= g_current_runtime_level ) THEN
661 FND_LOG.STRING(g_level_statement,
662 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_hr_location',
663 'Same organization and same classificationas cached: ');
664
665 END IF;
666
667 l_column := pg_column;
668 END IF; -- IF pg_classification IS NULL OR pg_classification <> p_classification THEN
669
670 END IF; --IF NVL(pr_org_loc_rec.location_id, -1) <> NVL(l_location_id,-2) THEN
671
672 IF (g_level_statement >= g_current_runtime_level ) THEN
673 FND_LOG.STRING(g_level_statement,
674 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_hr_location',
675 'l_column: '|| l_column);
676 END IF;
677
678 --end bug fix 4417523
679
680 if l_column = 'TOWN_OR_CITY' then
681 l_column_value := pr_org_loc_rec.TOWN_OR_CITY;
682 elsif l_column = 'COUNTRY' then
683 l_column_value := pr_org_loc_rec.COUNTRY;
684 elsif l_column = 'REGION_1' then
685 l_column_value := pr_org_loc_rec.REGION_1;
686 elsif l_column = 'REGION_2' then
687 l_column_value := pr_org_loc_rec.REGION_2;
688 elsif l_column = 'REGION_3' then
689 l_column_value := pr_org_loc_rec.REGION_3;
690 elsif l_column = 'ADDRESS_LINE_1' then
691 l_column_value := pr_org_loc_rec.ADDRESS_LINE_1;
692 elsif l_column = 'ADDRESS_LINE_2' then
693 l_column_value := pr_org_loc_rec.ADDRESS_LINE_2;
694 elsif l_column = 'ADDRESS_LINE_3' then
695 l_column_value := pr_org_loc_rec.ADDRESS_LINE_3;
696 elsif l_column = 'POSTAL_CODE' then
697 l_column_value := pr_org_loc_rec.POSTAL_CODE;
698 elsif l_column = 'TELEPHONE_NUMBER_1' then
699 l_column_value := pr_org_loc_rec.TELEPHONE_NUMBER_1;
700 elsif l_column = 'TELEPHONE_NUMBER_2' then
701 l_column_value := pr_org_loc_rec.TELEPHONE_NUMBER_2;
702 elsif l_column = 'TELEPHONE_NUMBER_3' then
703 l_column_value := pr_org_loc_rec.TELEPHONE_NUMBER_3;
704 else
705 IF (g_level_unexpected >= g_current_runtime_level ) THEN
706 FND_LOG.STRING(g_level_unexpected,
707 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_hr_location',
708 'Invalid Column');
709 END IF;
710 end if;
711
712 IF (g_level_statement >= g_current_runtime_level ) THEN
713
714 FND_LOG.STRING(g_level_statement,
715 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_hr_location.END',
716 'ZX_TDS_PROCESS_CEC_PVT.get_hr_location (-)');
717 END IF;
718
719 return upper(l_column_value);
720
721 END get_hr_location;
722
723 /*----------------------------------------------------------------------------*
724 | FUNCTION |
725 | get_site_location |
726 | |
727 | DESCRIPTION |
728 | This function gets the location information from RA_ADDRESSES |
729 | and RA_SITE_USES based on input parameters p_site_use_id and |
730 | p_classification |
731 | |
732 | SCOPE - Private |
733 | |
734 *----------------------------------------------------------------------------*/
735
736 FUNCTION get_site_location
737 (p_site_use_id IN NUMBER,
738 p_classification IN VARCHAR2)
739
740 return VARCHAR2 is
741
742 l_location VARCHAR2(150);
743
744 cursor c_site_use_rec(c_site_use_id NUMBER) is
745 select site.TAX_CLASSIFICATION,
746 site.cust_acct_site_id,
747 site.site_use_id
748 from HZ_CUST_SITE_USES_ALL site
749 where site.site_use_id = c_site_use_id;
750
751 cursor c_site_loc_rec(c_address_id number) is
752 select loc.COUNTRY,
753 loc.STATE,
754 loc.COUNTY,
755 loc.PROVINCE,
756 loc.CITY,
757 acct_site.CUST_ACCT_SITE_ID
758 from HZ_PARTY_SITES PARTY_SITE,
759 -- HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
760 HZ_LOCATIONS LOC,
761 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
762 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND
763 LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND
764 -- LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND
765 -- NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) AND
766 ACCT_SITE.CUST_ACCT_SITE_ID = c_address_id;
767
768 BEGIN
769
770 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
771
772 IF (g_level_statement >= g_current_runtime_level ) THEN
773 FND_LOG.STRING(g_level_statement,
774 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_site_location.BEGIN',
775 'ZX_TDS_PROCESS_CEC_PVT.get_site_location (+)'||
776 'p_site_use_id= ' || p_site_use_id ||
777 'p_classification= ' || p_classification ||
778 'pr_site_use_rec.site_used_id= '||pr_site_use_rec.site_use_id) ;
779 END IF;
780 pr_site_use_rec := NULL; --Bug 5865500
781
782 IF nvl(pr_site_use_rec.site_use_id,-1) <> nvl(p_site_use_id,-2) then
783
784 pr_site_use_rec.TAX_CLASSIFICATION := NULL;
785 pr_site_use_rec.cust_acct_site_id := NULL;
786
787 open c_site_use_rec(p_site_use_id);
788 fetch c_site_use_rec into pr_site_use_rec;
789 close c_site_use_rec;
790
791 END IF;
792
793 IF (g_level_statement >= g_current_runtime_level ) THEN
794
795 FND_LOG.STRING(g_level_statement,
796 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_site_location',
797 'pr_site_use_rec.cust_acct_site_id: '||pr_site_use_rec.cust_acct_site_id);
798 END IF;
799
800 IF nvl(pr_site_loc_rec.cust_acct_site_id, -1) <> nvl(pr_site_use_rec.cust_acct_site_id,-2) then
801
802 pr_site_loc_rec.COUNTRY := NULL;
803 pr_site_loc_rec.STATE := NULL;
804 pr_site_loc_rec.COUNTY := NULL;
805 pr_site_loc_rec.PROVINCE := NULL;
806 pr_site_loc_rec.CITY := NULL;
807 pr_site_loc_rec.cust_acct_site_id := NULL;
808
809 open c_site_loc_rec(pr_site_use_rec.cust_acct_site_id);
810 fetch c_site_loc_rec into pr_site_loc_rec;
811 close c_site_loc_rec;
812
813 IF (g_level_statement >= g_current_runtime_level ) THEN
814 FND_LOG.STRING(g_level_statement,
815 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_site_location',
816 'pr_site_loc_rec.COUNTRY'||pr_site_loc_rec.COUNTRY||' STATE '||pr_site_loc_rec.STATE||
817 ' PROVINCE '||pr_site_loc_rec.PROVINCE||
818 ' CITY '||pr_site_loc_rec.CITY||
819 ' COUNTY '||pr_site_loc_rec.COUNTY||
820 ' TAX_CLASSIFICATION '||
821 pr_site_use_rec.TAX_CLASSIFICATION||
822 ' cust_acct_site_id'||
823 pr_site_loc_rec.cust_acct_site_id);
824 END IF;
825 END IF;
826
827 IF upper(p_classification) = 'COUNTRY' then
828 l_location := pr_site_loc_rec.COUNTRY;
829 ELSIF upper(p_classification) = 'STATE' then
830 l_location := pr_site_loc_rec.STATE;
831 ELSIF upper(p_classification) = 'COUNTY' then
832 l_location := pr_site_loc_rec.COUNTY;
833 ELSIF upper(p_classification) = 'PROVINCE' then
834 l_location := pr_site_loc_rec.PROVINCE;
835 ELSIF upper(p_classification) = 'CITY' then
836 l_location := pr_site_loc_rec.CITY;
837 ELSIF upper(p_classification) = 'TAX_CLASSIFICATION' then
838 l_location := pr_site_use_rec.TAX_CLASSIFICATION;
839 ELSE
840 IF (g_level_statement >= g_current_runtime_level ) THEN
841 FND_LOG.STRING(g_level_statement,
842 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_site_location',
843 'Invalid value for p_classification');
844 END IF;
845 END IF;
846
847 IF (g_level_statement >= g_current_runtime_level ) THEN
848
849 FND_LOG.STRING(g_level_statement,
850 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_site_location.END',
851 'ZX_TDS_PROCESS_CEC_PVT.get_site_location (-)'||
852 ' location: '||l_location);
853 END IF;
854 RETURN upper(l_location);
855
856 EXCEPTION
857 when others then
858 IF (g_level_statement >= g_current_runtime_level ) THEN
859 FND_LOG.STRING(g_level_statement,
860 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_site_location',
861 'Exception in Get Site Location: ' || SQLCODE ||' ; '||SQLERRM);
862 END IF;
863 system_message('Exception');
864 END get_site_location;
865
866
867 /*----------------------------------------------------------------------------*
868 | FUNCTION |
869 | compare_condition |
870 | |
871 | DESCRIPTION |
872 | This function compares the condition based on calling function and |
873 | and the condition value entered by user. |
874 | |
875 | SCOPE - Private |
876 | |
877 *----------------------------------------------------------------------------*/
878
879 FUNCTION compare_condition
880 ( p_id IN NUMBER,
881 P_calling_function IN VARCHAR2,
882 p_classification IN VARCHAR2,
883 p_operator IN VARCHAR2,
884 p_value IN VARCHAR2)
885 return BOOLEAN is
886
887 l_temp varchar2(150);
888 l_value varchar2(150);
889 l_index NUMBER;
890
891 l_return_val NUMBER;
892
893 l_warehouse VARCHAR2(60);
894 l_party_tax_profile_id NUMBER;
895 l_reg_number VARCHAR2(50);
896 l_count NUMBER;
897 l_pty_id NUMBER;
898
899 -- If p_classification is WAREHOUSE, get the Warehouse Name
900 -- which is the Organization Name,using the Warehouse id to evaluate the
901 -- condition
902
903 cursor warehouse_csr (
904 l_set_of_books_id IN ar_system_parameters.set_of_books_id%TYPE,
905 l_org_id IN ar_system_parameters.org_id%TYPE ) is
906 select organization_name
907 from org_organization_definitions org
908 where l_set_of_books_id = org.set_of_books_id
909 and org.operating_unit = l_org_id
910 and org.organization_id = g_cec_product_org_id;
911
912 cursor get_vat_reg_num_site (c_site_use_id number) is
913 SELECT ZP.PARTY_TAX_PROFILE_ID,Upper(SU.TAX_REFERENCE)
914 FROM HZ_CUST_SITE_USES_ALL SU,
915 HZ_CUST_ACCT_SITES_ALL CAS,
916 HZ_PARTY_SITES PS,
917 ZX_PARTY_TAX_PROFILE ZP
918 WHERE SU.SITE_USE_ID = c_site_use_id AND
919 SU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID AND
920 CAS.PARTY_SITE_ID = PS.PARTY_SITE_ID AND
921 ZP.PARTY_ID = PS.PARTY_SITE_ID AND
922 ZP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE';
923
924 cursor get_vat_reg_num_cust (c_cust_id number) is
925 SELECT ZP.PARTY_TAX_PROFILE_ID,Upper(PARTY.TAX_REFERENCE)
926 FROM HZ_CUST_ACCOUNTS CA,
927 HZ_PARTIES PARTY,
928 ZX_PARTY_TAX_PROFILE ZP
929 WHERE CA.CUST_ACCOUNT_ID = c_cust_id AND
930 CA.PARTY_ID = PARTY.PARTY_ID AND
931 PARTY.PARTY_ID = ZP.PARTY_ID AND
932 ZP.PARTY_TYPE_CODE = 'THIRD_PARTY';
933
934 BEGIN
935
936 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
937
938 IF (g_level_statement >= g_current_runtime_level ) THEN
939 FND_LOG.STRING(g_level_statement,
940 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition.BEGIN',
941 'ZX_TDS_PROCESS_CEC_PVT.compare_condition (+)'||
942 ' p_id = '||to_char(p_id)||
943 ' P_calling_function: '|| P_calling_function||
944 ' p_classification: '|| p_classification ||
945 ' p_operator: '||p_operator||
946 ' p_value: '||p_value);
947 End If;
948
949 l_return_val := 0;
950
951 if (upper(p_operator) = 'NOT_FOUND') then
952 --
953 -- Find where it is called
954 if (upper(p_calling_function) = 'BILL_TO') then
955 l_index := IDX_BILL_TO;
956 elsif (upper(p_calling_function) = 'SHIP_TO') then
957 l_index := IDX_SHIP_TO;
958 elsif (upper(p_calling_function) = 'SHIP_FROM') then
959 l_index := IDX_SHIP_FROM;
960 elsif (upper(p_calling_function) = 'POO') then
961 l_index := IDX_POO;
962 elsif (upper(p_calling_function) = 'POA') then
963 l_index := IDX_POA;
964 elsif (upper(p_calling_function) = 'TRX') then
965 l_index := IDX_TRX;
966 elsif (upper(p_calling_function) = 'ITEM') then
967 l_index := IDX_ITEM;
968 elsif (upper(p_calling_function) = 'TAX_CODE') then
969 l_index := 0;
970 l_return_val := 0;
971 else
972 l_index := 0;
973 end if;
974
975 if (l_index > 0) then
976 if (upper(p_classification) = 'COUNTRY') then
977 l_return_val := pr_stats_rec_tbl(l_index).country;
978 elsif (upper(p_classification) = 'STATE') then
979 l_return_val := pr_stats_rec_tbl(l_index).state;
980 elsif (upper(p_classification) = 'COUNTY') then
981 l_return_val := pr_stats_rec_tbl(l_index).county;
982 elsif (upper(p_classification) = 'CITY') then
983 l_return_val := pr_stats_rec_tbl(l_index).city;
984 elsif (upper(p_classification) = 'PROVINCE') then
985 l_return_val := pr_stats_rec_tbl(l_index).province;
986 elsif (upper(p_classification) = 'FOB') then
987 l_return_val := pr_stats_rec_tbl(l_index).fob;
988 elsif (upper(p_classification) = 'TAX_CLASSIFICATION') then
989 l_return_val := pr_stats_rec_tbl(l_index).tax_classification;
990 elsif (upper(p_classification) = 'TYPE') then
991 l_return_val := pr_stats_rec_tbl(l_index).type;
992 elsif (upper(p_classification) = 'USER_ITEM_TYPE') then
993 l_return_val := pr_stats_rec_tbl(l_index).user_item_type;
994 elsif (upper(p_classification) = 'VAT_NUM') then
995 l_return_val := pr_stats_rec_tbl(l_index).vat_reg_num;
996 elsif (upper(p_classification) = 'WAREHOUSE') then
997 l_return_val := pr_stats_rec_tbl(l_index).warehouse;
998 else
999 l_return_val := 0;
1000 end if;
1001 end if;
1002 --
1003 -- l_return_val STATS_INIT, STATS_TRUE means each function called in
1004 -- the condition evaluated to TRUE, STATS_FLASE means each function
1005 -- called in the condition evaluated to FALSE. Since this is for NOT_FOUND
1006 -- return TRUE when the function evaluated to FALSE,
1007 -- and FALSE when the function evaluated to TRUE.
1008 --
1009 if (l_return_val in (STATS_INIT, STATS_TRUE)) then
1010 return FALSE;
1011 else
1012 return TRUE;
1013 end if;
1014 end if;
1015
1016 IF upper(p_calling_function) in ('BILL_TO','SHIP_TO') then
1017
1018 IF (upper(p_classification) <> 'VAT_NUM') THEN
1019 l_temp := get_site_location( p_site_use_id => p_id,
1020 p_classification => p_classification);
1021 l_value := upper(p_value);
1022
1023 IF (g_level_statement >= g_current_runtime_level) THEN
1024 FND_LOG.STRING(g_level_statement,
1025 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition',
1026 ' l_temp = '|| l_temp ||
1027 ' l_value = ' || l_value);
1028 END IF;
1029
1030 IF l_temp is null and l_value is NULL THEN
1031 return TRUE;
1032 ELSIF p_operator = '=' THEN
1033 return l_temp = l_value;
1034 ELSIF p_operator = '>' THEN
1035 return l_temp > l_value;
1036 ELSIF p_operator = '>=' THEN
1037 return l_temp >= l_value;
1038 ELSIF p_operator = '<' THEN
1039 return l_temp < l_value;
1040 ELSIF p_operator = '<=' THEN
1041 return l_temp <= l_value;
1045 return TRUE;
1042 ELSIF p_operator = '<>' AND l_temp IS NOT NULL THEN
1043 return l_temp <> l_value;
1044 ELSIF p_operator = '<>' AND l_temp IS NULL THEN
1046 ELSE
1047 return FALSE;
1048 END IF;
1049 ELSE
1050 IF (g_level_statement >= g_current_runtime_level ) THEN
1051 FND_LOG.STRING(g_level_statement,'Calling Function: ',p_calling_function);
1052 FND_LOG.STRING(g_level_statement,'g_cec_bill_to_party_id: ',nvl(g_cec_bill_to_party_id,0));
1053 FND_LOG.STRING(g_level_statement,'g_cec_ship_to_party_id: ',nvl(g_cec_ship_to_party_id,0));
1054 END IF;
1055
1056 BEGIN
1057 OPEN get_vat_reg_num_site(p_id);
1058 FETCH get_vat_reg_num_site INTO l_party_tax_profile_id,l_reg_number;
1059 CLOSE get_vat_reg_num_site;
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 CLOSE get_vat_reg_num_site;
1063 END;
1064 IF l_reg_number IS NULL THEN
1065 IF l_party_tax_profile_id IS NOT NULL THEN
1066
1067 SELECT COUNT(*) INTO l_count FROM ZX_REGISTRATIONS
1068 WHERE PARTY_TAX_PROFILE_ID = l_party_tax_profile_id;
1069
1070 IF l_count = 1 THEN
1071 SELECT REGISTRATION_NUMBER INTO l_reg_number
1072 FROM ZX_REGISTRATIONS
1073 WHERE PARTY_TAX_PROFILE_ID = l_party_tax_profile_id;
1074 ELSIF l_count > 1 THEN
1075 BEGIN
1076 SELECT REGISTRATION_NUMBER INTO l_reg_number
1077 FROM ZX_REGISTRATIONS
1078 WHERE PARTY_TAX_PROFILE_ID = l_party_tax_profile_id
1079 AND DEFAULT_REGISTRATION_FLAG = 'Y';
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 NULL;
1083 END;
1084 END IF;
1085 END IF;
1086
1087 IF (g_level_statement >= g_current_runtime_level ) THEN
1088 FND_LOG.STRING(g_level_statement,'Value: ',nvl(l_reg_number,0));
1089 END IF;
1090
1091 IF l_reg_number IS NULL THEN
1092 IF upper(p_calling_function) = 'BILL_TO' THEN
1093 l_pty_id := g_cec_bill_to_party_id;
1094 ELSE
1095 l_pty_id := g_cec_SHIP_to_party_id;
1096 END IF;
1097 IF (g_level_statement >= g_current_runtime_level ) THEN
1098 FND_LOG.STRING(g_level_statement,'Value: ',nvl(l_pty_id,0));
1099 END IF;
1100 BEGIN
1101 OPEN get_vat_reg_num_cust(l_pty_id);
1102 FETCH get_vat_reg_num_cust INTO l_party_tax_profile_id,l_reg_number;
1103 CLOSE get_vat_reg_num_cust;
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 CLOSE get_vat_reg_num_cust;
1107 END;
1108
1109 IF (g_level_statement >= g_current_runtime_level ) THEN
1110 FND_LOG.STRING(g_level_statement,'Value: ',nvl(l_reg_number,0));
1111 END IF;
1112
1113 IF l_reg_number IS NULL THEN
1114 IF l_party_tax_profile_id IS NOT NULL THEN
1115
1116 SELECT COUNT(*) INTO l_count FROM ZX_REGISTRATIONS
1117 WHERE PARTY_TAX_PROFILE_ID = l_party_tax_profile_id;
1118
1119 IF l_count = 1 THEN
1120 SELECT REGISTRATION_NUMBER INTO l_reg_number
1121 FROM ZX_REGISTRATIONS
1122 WHERE PARTY_TAX_PROFILE_ID = l_party_tax_profile_id;
1123 ELSIF l_count > 1 THEN
1124 BEGIN
1125 SELECT REGISTRATION_NUMBER INTO l_reg_number
1126 FROM ZX_REGISTRATIONS
1127 WHERE PARTY_TAX_PROFILE_ID = l_party_tax_profile_id
1128 AND DEFAULT_REGISTRATION_FLAG = 'Y';
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 NULL;
1132 END;
1133 END IF;
1134 END IF;
1135 END IF;
1136 END IF;
1137 END IF;
1138
1139 IF (g_level_statement >= g_current_runtime_level ) THEN
1140 FND_LOG.STRING(g_level_statement,'Value: ',nvl(l_reg_number,0));
1141 END IF;
1142
1143 IF p_operator = 'IS' THEN
1144 return l_reg_number IS NOT NULL;
1145 ELSIF p_operator = 'IS_NOT' THEN
1146 return l_reg_number IS NULL;
1147 ELSE
1148 return FALSE;
1149 END IF;
1150
1151 /*IF upper(p_calling_function) = 'BILL_TO' THEN
1152
1153 --+ nipatel commenting this call for the time being as ar_validate_vat is obsolete
1154 -- and we are internally discussing whether it can be replaced with ZX_TRN_VALIDATION_PKG
1155 -- AR_VALIDATE_VAT.AR_COORDINATE_VALIDATION(p_id,
1156 -- g_cec_bill_to_party_id,l_temp);
1157 NULL;
1158
1159 ELSE
1160
1161 --+ nipatel commenting this call for the time being as ar_validate_vat is obsolete
1162 -- and we are internally discussing whether it can be replaced with ZX_TRN_VALIDATION_PKG
1163 -- AR_VALIDATE_VAT.AR_COORDINATE_VALIDATION(p_id,
1164 -- g_cec_ship_to_party_id,l_temp);
1165 NULL;
1166
1167 END IF;
1168
1169
1170 IF p_operator = 'IS' THEN
1171 return l_temp = 'P';
1172 ELSIF p_operator = 'IS_NOT' THEN
1173 return l_temp <> 'P';
1174 ELSE
1175 return FALSE;
1176 END IF;*/
1177 END IF;
1178
1179 ELSIF upper(p_calling_function) in ('SHIP_FROM','POO','POA') THEN
1180
1181 IF upper(p_calling_function) = 'SHIP_FROM' THEN
1182 l_temp := get_hr_location ( p_organization_id => p_id,
1183 p_location_id => NULL,
1184 p_classification => p_classification);
1185 ELSE -- upper(p_calling_function)in ('POO','POA')
1186 l_temp := get_hr_location ( p_organization_id => NULL,
1187 p_location_id => p_id,
1188 p_classification => p_classification);
1189 END IF;
1190
1191 l_value := upper(p_value);
1192
1193 IF (g_level_statement >= g_current_runtime_level) THEN
1194 FND_LOG.STRING(g_level_statement,
1195 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition',
1196 ' l_temp = '|| l_temp ||
1197 ' l_value = ' || l_value);
1198 END IF;
1199
1200 IF upper(p_classification) = 'WAREHOUSE' then
1201 IF (g_level_statement >= g_current_runtime_level) THEN
1202 FND_LOG.STRING(g_level_statement,
1203 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition',
1204 ' Calling Function is Ship From and p_classification is WAREHOUSE');
1205 END IF;
1206
1207 open warehouse_csr(g_cec_ledger_id, g_cec_internal_organization_id);
1208 fetch warehouse_csr into l_warehouse;
1209 close warehouse_csr;
1210
1211 l_temp := upper(l_warehouse);
1212 end if;
1213
1214 IF l_temp is null and l_value is null then
1215 return TRUE;
1219 return l_temp > l_value;
1216 ELSIF p_operator = '=' THEN
1217 return l_temp = l_value;
1218 ELSIF p_operator = '>' THEN
1220 ELSIF p_operator = '>=' THEN
1221 return l_temp >= l_value;
1222 ELSIF p_operator = '<' THEN
1223 return l_temp < l_value;
1224 ELSIF p_operator = '<=' THEN
1225 return l_temp <= l_value;
1226 ELSIF p_operator = '<>' AND l_temp IS NOT NULL THEN
1227 return l_temp <> l_value;
1228 ELSIF p_operator = '<>' AND l_temp IS NULL THEN
1229 return TRUE;
1230 ELSE
1231 return FALSE;
1232 END IF;
1233
1234 ELSIF upper(p_calling_function) = 'TRX' THEN
1235 if (p_classification = 'FOB') then
1236 l_temp := upper(g_cec_fob_point);
1237 l_value := upper(p_value);
1238
1239 elsif (p_classification = 'TYPE') THEN
1240 l_value := p_value;
1241 l_temp := to_char(g_cec_trx_type_id);
1242
1243 else
1244 l_temp := NULL;
1245 l_value := NULL;
1246 end if;
1247
1248 IF (g_level_statement >= g_current_runtime_level) THEN
1249 FND_LOG.STRING(g_level_statement,
1250 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition',
1251 ' l_temp = '|| l_temp ||
1252 ' l_value = ' || l_value);
1253 END IF;
1254
1255 IF l_temp is null and l_value is null then
1256 return TRUE;
1257 ELSIF p_operator = '=' THEN
1258 return l_temp = l_value;
1259 ELSIF p_operator = '>' THEN
1260 return l_temp > l_value;
1261 ELSIF p_operator = '>=' THEN
1262 return l_temp >= l_value;
1263 ELSIF p_operator = '<' THEN
1264 return l_temp < l_value;
1265 ELSIF p_operator = '<=' THEN
1266 return l_temp <= l_value;
1267 ELSIF p_operator = '<>' AND l_temp IS NOT NULL THEN
1268 return l_temp <> l_value;
1269 ELSIF p_operator = '<>' AND l_temp IS NULL THEN
1270 return TRUE;
1271 ELSE
1272 return FALSE;
1273 END IF;
1274 ELSIF upper(p_calling_function) = 'ITEM' THEN
1275 if (p_classification = 'USER_ITEM_TYPE') then
1276 BEGIN
1277 l_value := upper(p_value);
1278 SELECT item_type
1279 INTO l_temp
1280 FROM MTL_SYSTEM_ITEMS
1281 WHERE inventory_item_id = g_cec_product_id
1282 AND organization_id =
1283 nvl(g_cec_product_org_id, g_cec_so_organization_id);
1284 -- Open: SO_ORGANIZATION_ID doesn not have a mapping column in eBTax yet
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND then
1287 l_temp := NULL;
1288 WHEN TOO_MANY_ROWS then
1289 l_temp := NULL;
1290
1291 WHEN OTHERS then
1292 RAISE;
1293 END;
1294 else
1295 l_temp := NULL;
1296 l_value := NULL;
1297 end if;
1298
1299 IF (g_level_statement >= g_current_runtime_level) THEN
1300 FND_LOG.STRING(g_level_statement,
1301 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition',
1302 ' l_temp = '|| l_temp ||
1303 ' l_value = ' || l_value);
1304 END IF;
1305
1306 IF l_temp is null and l_value is null then
1307 return TRUE;
1308 ELSIF p_operator = '=' THEN
1309 return l_temp = l_value;
1310 ELSIF p_operator = '>' THEN
1311 return l_temp > l_value;
1312 ELSIF p_operator = '>=' THEN
1313 return l_temp >= l_value;
1314 ELSIF p_operator = '<' THEN
1315 return l_temp < l_value;
1316 ELSIF p_operator = '<=' THEN
1317 return l_temp <= l_value;
1318 ELSIF p_operator = '<>' AND l_temp IS NOT NULL THEN
1319 return l_temp <> l_value;
1320 ELSIF p_operator = '<>' AND l_temp IS NULL THEN
1321 return TRUE;
1322 ELSE
1323 return FALSE;
1324 END IF;
1325 ELSE
1326 return FALSE;
1327 END IF;
1328 IF (g_level_statement >= g_current_runtime_level ) THEN
1329
1330 FND_LOG.STRING(g_level_statement,
1331 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.compare_condition.END',
1332 'ZX_TDS_PROCESS_CEC_PVT.compare_condition (-)');
1333 END IF;
1334 END compare_condition;
1335
1336
1337 /*----------------------------------------------------------------------------*
1338 | FUNCTION |
1339 | get_location_column |
1340 | |
1341 | DESCRIPTION |
1342 | This function gets the application column name based on |
1343 | the input parameter p_classification, and the address style. |
1344 | Based on the context value of the 'Location Address' Flexfield, |
1345 | the function maps appropriate application_column_name to either |
1346 | county, state, county, province or city. |
1347 | |
1348 | SCOPE - Private |
1349 | |
1350 *----------------------------------------------------------------------------*/
1351
1352 FUNCTION get_location_column(p_style IN VARCHAR2,
1353 p_classification IN VARCHAR2)
1354 return VARCHAR2 is
1355
1356 i BINARY_INTEGER;
1357 -- l_style hr_locations_v.style%type; -- bug fix 4417523
1358 l_context NUMBER;
1359 l_column VARCHAR2(150);
1360
1361 BEGIN
1362
1363 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1364
1365 IF (g_level_statement >= g_current_runtime_level ) THEN
1366 FND_LOG.STRING(g_level_statement,
1367 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column.BEGIN',
1368 'ZX_TDS_PROCESS_CEC_PVT.get_location_column (+)');
1369
1370 END IF;
1371 fnd_dflex.get_flexfield(
1372 'PER',
1373 'Address Location',
1374 pr_flexfield,
1375 pr_flexinfo);
1376
1377 /*
1378 --commented out for bug 4417523 begin, the following function is now
1379 --carried out in get_hr_location()
1380 l_style := p_style;
1381
1382 -- Get the context information from 'Address Location' Descriptive Flexfield
1383 -- Select the context value which matches p_org_loc_rec.style
1384
1385 fnd_dflex.get_contexts(pr_flexfield, pr_contexts);
1386 l_context := NULL;
1387
1388 FOR i IN 1 .. pr_contexts.ncontexts LOOP
1389 IF(pr_contexts.is_enabled(i)) THEN
1390 if pr_contexts.context_code(i) = l_style then
1391
1392 l_context := i;
1393 end if;
1394 END IF;
1395 END LOOP;
1396
1397 IF l_context is NULL then
1398 IF (g_level_statement >= g_current_runtime_level ) THEN
1399 FND_LOG.STRING(g_level_statement,
1400 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column',
1401 'No context which matches the style');
1402
1403 FND_LOG.STRING(g_level_statement,
1404 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column.END',
1405 'ZX_TDS_PROCESS_CEC_PVT.get_location_column (-)');
1406 END IF;
1407 return NULL;
1408 END IF;
1409 --commented out for bug 4417523 end*/
1410
1411 -- Select the segments which correspond to the selected context.
1412 -- bug fix 4417523 replace pr_contexts.context_code(l_context) with p_style
1413 -- fnd_dflex.get_segments(fnd_dflex.make_context(pr_flexfield,
1414 -- pr_contexts.context_code(l_context)),
1415 -- pr_segments,
1416 -- TRUE);
1417 fnd_dflex.get_segments(fnd_dflex.make_context(pr_flexfield,
1418 p_style),
1419 pr_segments,
1420 TRUE);
1421
1422 -- Check if the segment name matches with the value of input parameter p_classification,
1423 -- Otherwise write an error message and return null
1424
1425 FOR i IN 1 .. pr_segments.nsegments LOOP
1426
1427 IF upper(pr_segments.segment_name(i)) = upper(p_classification) then
1428
1429 l_column := pr_segments.application_column_name(i);
1430 IF (g_level_statement >= g_current_runtime_level ) THEN
1431
1432 FND_LOG.STRING(g_level_statement,
1433 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column',
1434 ' Segment name= '||pr_segments.segment_name(i)||
1435 ' Column Name= '|| pr_segments.application_column_name(i)||
1436 ' Description= '||pr_segments.description(i));
1437 END IF;
1438 END IF;
1439
1440 END LOOP;
1441
1442 IF l_column is NULL then
1443 IF (g_level_statement >= g_current_runtime_level ) THEN
1444 FND_LOG.STRING(g_level_statement,
1445 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column',
1446 'No column which matches the value of '||
1447 'input parameter p_classification');
1448 FND_LOG.STRING(g_level_statement,
1449 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column',
1450 'Get Location Column (-)');
1451 END IF;
1452 END IF;
1453
1454 IF (g_level_statement >= g_current_runtime_level ) THEN
1455
1456 FND_LOG.STRING(g_level_statement,
1457 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.get_location_column.END',
1458 'ZX_TDS_PROCESS_CEC_PVT.get_location_column (-)');
1459 END IF;
1460 RETURN l_column;
1461
1462 END get_location_column;
1463
1464 /*----------------------------------------------------------------------------*
1465 | PROCEDURE |
1466 | update_pr_stats_rec_tbl |
1467 | |
1468 | DESCRIPTION |
1469 | This function updates stats_rec_tbl with status of each condition to |
1470 | be compared. |
1471 | |
1472 | SCOPE - Private |
1473 | |
1474 *----------------------------------------------------------------------------*/
1475
1476 procedure update_pr_stats_rec_tbl(p_classification IN VARCHAR2,
1477 p_index IN NUMBER,
1478 p_flag IN BOOLEAN) is
1479
1480 begin
1481
1482 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1483 IF (g_level_statement >= g_current_runtime_level ) THEN
1484 FND_LOG.STRING(g_level_statement,
1485 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.update_pr_stats_rec_tbl.BEGIN',
1486 'ZX_TDS_PROCESS_CEC_PVT.update_pr_stats_rec_tbl (+)');
1487 END IF;
1488
1489 if (p_flag) then
1490 if (upper(p_classification) = 'COUNTRY' and
1491 pr_stats_rec_tbl(p_index).country in (STATS_INIT, STATS_FALSE)) then
1492 pr_stats_rec_tbl(p_index).country := STATS_TRUE;
1493
1494 elsif(upper(p_classification) = 'STATE' and
1495 pr_stats_rec_tbl(p_index).state in (STATS_INIT, STATS_FALSE)) then
1496 pr_stats_rec_tbl(p_index).state := STATS_TRUE;
1497
1498 elsif(upper(p_classification) = 'COUNTY' and
1499 pr_stats_rec_tbl(p_index).county in (STATS_INIT, STATS_FALSE)) then
1500 pr_stats_rec_tbl(p_index).county := STATS_TRUE;
1501
1502 elsif(upper(p_classification) = 'CITY' and
1503 pr_stats_rec_tbl(p_index).county in (STATS_INIT, STATS_FALSE)) then
1504 pr_stats_rec_tbl(p_index).city := STATS_TRUE;
1505
1506 elsif(upper(p_classification) = 'PROVINCE' and
1507 pr_stats_rec_tbl(p_index).province in (STATS_INIT, STATS_FALSE)) then
1508 pr_stats_rec_tbl(p_index).province := STATS_TRUE;
1509
1510 elsif(upper(p_classification) = 'FOB' and
1511 pr_stats_rec_tbl(p_index).fob in (STATS_INIT, STATS_FALSE)) then
1512 pr_stats_rec_tbl(p_index).fob := STATS_TRUE;
1513
1514 elsif(upper(p_classification) = 'TAX_CLASSIFICATION' and
1515 pr_stats_rec_tbl(p_index).tax_classification in (STATS_INIT, STATS_FALSE)) then
1516 pr_stats_rec_tbl(p_index).tax_classification := STATS_TRUE;
1517
1518 elsif(upper(p_classification) = 'TYPE' and
1519 pr_stats_rec_tbl(p_index).type in (STATS_INIT, STATS_FALSE)) then
1520 pr_stats_rec_tbl(p_index).type := STATS_TRUE;
1521
1522 elsif(upper(p_classification) = 'USER_ITEM_TYPE' and
1523 pr_stats_rec_tbl(p_index).user_item_type in (STATS_INIT, STATS_FALSE)) then
1524 pr_stats_rec_tbl(p_index).user_item_type := STATS_TRUE;
1525
1526 elsif(upper(p_classification) = 'VAT_NUM' and
1527 pr_stats_rec_tbl(p_index).vat_reg_num in (STATS_INIT, STATS_FALSE)) then
1528 pr_stats_rec_tbl(p_index).vat_reg_num := STATS_TRUE;
1529
1530 elsif(upper(p_classification) = 'WAREHOUSE' and
1531 pr_stats_rec_tbl(p_index).warehouse in (STATS_INIT, STATS_FALSE)) then
1532 pr_stats_rec_tbl(p_index).warehouse := STATS_TRUE;
1533 end if;
1534 else
1535 if (upper(p_classification) = 'COUNTRY' and
1536 pr_stats_rec_tbl(p_index).country = STATS_INIT) then
1537 pr_stats_rec_tbl(p_index).country := STATS_FALSE;
1538
1539 elsif(upper(p_classification) = 'STATE' and
1540 pr_stats_rec_tbl(p_index).state = STATS_INIT) then
1541 pr_stats_rec_tbl(p_index).state := STATS_FALSE;
1542 elsif(upper(p_classification) = 'COUNTY' and
1543 pr_stats_rec_tbl(p_index).county = STATS_INIT) then
1544 pr_stats_rec_tbl(p_index).county := STATS_FALSE;
1545
1546 elsif(upper(p_classification) = 'CITY' and
1547 pr_stats_rec_tbl(p_index).city = STATS_INIT) then
1548 pr_stats_rec_tbl(p_index).city := STATS_FALSE;
1549
1550 elsif(upper(p_classification) = 'PROVINCE' and
1551 pr_stats_rec_tbl(p_index).province = STATS_INIT) then
1552 pr_stats_rec_tbl(p_index).province := STATS_FALSE;
1553
1554 elsif(upper(p_classification) = 'FOB' and
1555 pr_stats_rec_tbl(p_index).fob = STATS_INIT) then
1556 pr_stats_rec_tbl(p_index).fob := STATS_FALSE;
1557
1558 elsif(upper(p_classification) = 'TAX_CLASSIFICATION' and
1559 pr_stats_rec_tbl(p_index).tax_classification = STATS_INIT) then
1560 pr_stats_rec_tbl(p_index).tax_classification := STATS_FALSE;
1561
1562 elsif(upper(p_classification) = 'TYPE' and
1563 pr_stats_rec_tbl(p_index).type = STATS_INIT) then
1564 pr_stats_rec_tbl(p_index).type := STATS_FALSE;
1565
1566 elsif(upper(p_classification) = 'USER_ITEM_TYPE' and
1567 pr_stats_rec_tbl(p_index).user_item_type = STATS_INIT) then
1568 pr_stats_rec_tbl(p_index).user_item_type := STATS_FALSE;
1569
1570 elsif(upper(p_classification) = 'VAT_NUM' and
1571 pr_stats_rec_tbl(p_index).vat_reg_num = STATS_INIT) then
1572 pr_stats_rec_tbl(p_index).vat_reg_num := STATS_FALSE;
1573
1574 elsif(upper(p_classification) = 'WAREHOUSE' and
1575 pr_stats_rec_tbl(p_index).warehouse = STATS_INIT) then
1576 pr_stats_rec_tbl(p_index).warehouse := STATS_FALSE;
1577 end if;
1578 end if;
1579
1580 IF (g_level_statement >= g_current_runtime_level ) THEN
1581 FND_LOG.STRING(g_level_statement,
1582 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.update_pr_stats_rec_tbl.END',
1583 'ZX_TDS_PROCESS_CEC_PVT.update_pr_stats_rec_tbl (-)');
1584 END IF;
1585 end update_pr_stats_rec_tbl;
1586
1587 /*===========================================================================+
1588 | PROCEDURE |
1589 | dump_stats_rec |
1590 | |
1591 | DESCRIPTION |
1592 | dumps the image of pr_stats_rec_tbl(x) |
1593 | |
1594 | SCOPE - Private |
1595 | |
1596 +===========================================================================*/
1597 procedure dump_stats_rec(p_x in number) is
1598
1599 l_x VARCHAR2(20);
1600
1601 begin
1602
1603 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1604 IF (g_level_statement >= g_current_runtime_level) THEN
1605 FND_LOG.STRING(g_level_statement,
1606 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.dump_stats_rec.BEGIN',
1607 ' ZX_TDS_PROCESS_CEC_PVT.dump_stats_rec (+)');
1608
1609 END IF;
1610
1611 if (p_x = IDX_SHIP_FROM) then
1612 l_x := 'SHIP FROM';
1613 elsif (p_x = IDX_SHIP_TO) then
1614 l_x := 'SHIP TO';
1615 elsif (p_x = IDX_POO) then
1616 l_x := 'POO';
1617 elsif (p_x = IDX_POA) then
1618 l_x := 'POA';
1619 elsif (p_x = IDX_BILL_TO) then
1620 l_x := 'BILL TO';
1621 elsif (p_x = IDX_TRX) then
1622 l_x := 'TRX';
1623 elsif (p_x = IDX_ITEM) then
1624 l_x := 'ITEM';
1625 elsif (p_x = IDX_TAX_CODE) then
1626 l_x := 'TAX_CODE';
1627 end if;
1628
1629 IF (g_level_statement >= g_current_runtime_level) THEN
1630
1631 FND_LOG.STRING(g_level_statement,
1632 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.dump_stats_rec.END',
1633 ' ZX_TDS_PROCESS_CEC_PVT.dump_stats_rec (-)');
1634 END IF;
1635 end dump_stats_rec;
1636
1637 /*===========================================================================+
1638 | PROCEDURE |
1639 | dump_pr_stats_rec_tbl |
1640 | |
1641 | DESCRIPTION |
1642 | dumps the image of pr_stats_rec_tbl |
1643 | |
1644 | SCOPE - Private |
1645 | |
1646 +===========================================================================*/
1647 procedure dump_pr_stats_rec_tbl is
1648
1649 begin
1650
1651 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1652 IF (g_level_statement >= g_current_runtime_level) THEN
1653 FND_LOG.STRING(g_level_statement,
1654 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.dump_pr_stats_rec_tbl.BEGIN',
1655 ' ZX_TDS_PROCESS_CEC_PVT.dump_pr_stats_rec_tbl (+)');
1656 END IF;
1657
1658 for i in IDX_SHIP_FROM .. IDX_TAX_CODE LOOP
1659 dump_stats_rec(i);
1660 end LOOP;
1661
1662 IF (g_level_statement >= g_current_runtime_level) THEN
1663 FND_LOG.STRING(g_level_statement,
1664 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.dump_pr_stats_rec_tbl.END',
1665 ' ZX_TDS_PROCESS_CEC_PVT.dump_pr_stats_rec_tbl (-)');
1666 END IF;
1667 end dump_pr_stats_rec_tbl;
1668
1669 -------------------
1670 -- Public Methods -
1671 -------------------
1672
1673 /*----------------------------------------------------------------------------*
1674 | The following functions |
1675 | ship_to, |
1676 | ship_from, |
1677 | poo, |
1678 | poa, |
1679 | trx, |
1680 | item |
1681 | tax_code |
1682 | call private function compare_condition which compares the site |
1683 | location (obtained by calling get_site_location function with the |
1684 | parameter p_classification) with p_value using the operator p_operator |
1685 | and returns true or false based on the result. |
1686 *----------------------------------------------------------------------------*/
1687
1688 -- This function checks whether the condition (built using input parameters)
1689 -- evaluates to TRUE or FALSE for a Ship To site (identified by tax_info_rec.
1690 -- ship_to_site_use_id)
1691 FUNCTION tax_code
1692 (p_classification IN VARCHAR2 Default NULL,
1693 p_operator IN VARCHAR2 Default NULL,
1694 p_value IN VARCHAR2 DEFAULT NULL)
1695 return BOOLEAN is
1696
1697 l_return_val BOOLEAN;
1698
1699 BEGIN
1700
1701 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1702 l_return_val := FALSE;
1703
1704 IF (g_level_statement >= g_current_runtime_level) THEN
1705 FND_LOG.STRING(g_level_statement,
1706 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.tax_code.BEGIN',
1707 'ZX_TDS_PROCESS_CEC_PVT.tax_code (+)');
1708 END IF;
1709
1710 l_return_val := compare_condition ( g_cec_ship_to_party_site_id,
1711 'TAX_CODE',
1712 p_classification,
1713 p_operator,
1714 p_value);
1715 if l_return_val = TRUE then
1716 IF (g_level_statement >= g_current_runtime_level ) THEN
1717 FND_LOG.STRING(g_level_statement,
1718 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.tax_code',
1719 'The condition Tax code '||p_classification||
1720 p_operator||p_value||' evaluates to TRUE');
1721 END IF;
1722 else
1723 IF (g_level_statement >= g_current_runtime_level ) THEN
1724 FND_LOG.STRING(g_level_statement,
1725 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.tax_code',
1726 'The condition Tax code '||p_classification||
1727 p_operator||p_value||' evaluates to FALSE');
1728 END IF;
1729 end if;
1730 -- Only when p_oprator is not NOT_FOUND, update pr_stats_rec_tbl.
1731 if (p_operator <> 'NOT_FOUND') then
1732 update_pr_stats_rec_tbl(p_classification => p_classification,
1733 p_index => IDX_TAX_CODE,
1734 p_flag => l_return_val);
1735 dump_stats_rec(IDX_TAX_CODE); --for bug1833141
1736 end if;
1737 IF (g_level_statement >= g_current_runtime_level) THEN
1738 FND_LOG.STRING(g_level_statement,
1739 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.tax_code.END',
1740 'ZX_TDS_PROCESS_CEC_PVT.tax_code (-)');
1741 END IF;
1742 return l_return_val;
1743 END tax_code;
1744
1745 FUNCTION ship_to
1746 (p_classification IN VARCHAR2 Default NULL,
1747 p_operator IN VARCHAR2 Default NULL,
1748 p_value IN VARCHAR2 DEFAULT NULL)
1749 return BOOLEAN is
1750
1751 l_return_val BOOLEAN;
1752
1753 BEGIN
1754
1755 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1756 l_return_val := FALSE;
1757
1758 IF (g_level_statement >= g_current_runtime_level ) THEN
1759 FND_LOG.STRING(g_level_statement,
1760 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_to.BEGIN',
1761 'ZX_TDS_PROCESS_CEC_PVT.Ship_To (+)');
1762 END IF;
1763
1764 l_return_val := compare_condition ( g_cec_ship_to_site_use_id,
1765 'SHIP_TO',
1766 p_classification,
1767 p_operator,
1768 p_value);
1769 if l_return_val = TRUE then
1770 IF (g_level_statement >= g_current_runtime_level ) THEN
1771 FND_LOG.STRING(g_level_statement,
1772 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_to',
1773 'The condition Ship to '||p_classification||
1774 p_operator||p_value||' evaluates to TRUE');
1775 END IF;
1776 else
1777 IF (g_level_statement >= g_current_runtime_level ) THEN
1778 FND_LOG.STRING(g_level_statement,
1779 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_to',
1780 'The condition Ship to '||p_classification||
1781 p_operator||p_value||' evaluates to FALSE');
1782 END IF;
1783 end if;
1784 -- Only when p_oprator is not NOT_FOUND, update pr_stats_rec_tbl.
1785 if (p_operator <> 'NOT_FOUND') then
1786 update_pr_stats_rec_tbl(p_classification => p_classification,
1787 p_index => IDX_SHIP_TO,
1788 p_flag => l_return_val);
1789 dump_stats_rec(IDX_SHIP_TO); --for bug1833141
1790 end if;
1791 IF (g_level_statement >= g_current_runtime_level ) THEN
1792 FND_LOG.STRING(g_level_statement,
1793 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_to.END',
1794 'ZX_TDS_PROCESS_CEC_PVT.Ship_To (-)');
1795 END IF;
1796 return l_return_val;
1797 END ship_to;
1798
1799 ----------------------------------------------------------------------------
1800 -- This function checks whether the condition (built using input parameters)
1801 -- evaluates to TRUE or FALSE for a point of Order Acceptance (identified
1802 -- through tax_info_rec.ship_from_warehouse_id)
1803
1804 FUNCTION ship_from
1805 (p_classification IN VARCHAR2 Default NULL,
1806 p_operator IN VARCHAR2 Default NULL,
1807 p_value IN VARCHAR2 DEFAULT NULL)
1808 return BOOLEAN is
1809
1810 l_return_val BOOLEAN;
1811
1812 BEGIN
1813
1814 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1815 l_return_val := FALSE;
1816
1817 IF (g_level_statement >= g_current_runtime_level ) THEN
1818 FND_LOG.STRING(g_level_statement,
1819 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_from.BEGIN',
1820 'ZX_TDS_PROCESS_CEC_PVT.Ship_From(+)');
1821 END IF;
1822
1823 l_return_val := compare_condition ( g_cec_product_org_id,
1824 'SHIP_FROM',
1825 p_classification,
1826 p_operator,
1827 p_value);
1828 if l_return_val = TRUE then
1829 IF (g_level_statement >= g_current_runtime_level ) THEN
1830 FND_LOG.STRING(g_level_statement,
1831 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_from',
1832 'The condition Ship From '||p_classification||
1833 p_operator||p_value||' evaluates to TRUE');
1834 END IF;
1835 else
1836 IF (g_level_statement >= g_current_runtime_level ) THEN
1837 FND_LOG.STRING(g_level_statement,
1838 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_from',
1839 'The condition Ship From '||p_classification||
1840 p_operator||p_value||' evaluates to FALSE');
1841 END IF;
1842 end if;
1843 -- Only when p_oprator is not NOT_FOUND, update pr_stats_rec_tbl.
1844 if (p_operator <> 'NOT_FOUND') then
1845 update_pr_stats_rec_tbl(p_classification => p_classification,
1846 p_index => IDX_SHIP_FROM,
1847 p_flag => l_return_val);
1848 dump_stats_rec(IDX_SHIP_FROM); --for bug1833141
1849 end if;
1850 IF (g_level_statement >= g_current_runtime_level ) THEN
1851 FND_LOG.STRING(g_level_statement,
1852 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.ship_from.END',
1853 'ZX_TDS_PROCESS_CEC_PVT.Ship_From (-)');
1854 END IF;
1855 return l_return_val;
1856 END ship_from;
1857
1858
1859 -----------------------------------------------------------------------------
1860 -- This function checks whether the condition (built using input parameters)
1861 -- evaluates to TRUE or FALSE for a Bill To Site (identified through
1862 -- tax_info_rec.bill_to_site_use_id)
1863
1864 FUNCTION bill_to
1865 (p_classification IN VARCHAR2 Default NULL,
1866 p_operator IN VARCHAR2 Default NULL,
1867 p_value IN VARCHAR2 DEFAULT NULL)
1868 return BOOLEAN is
1869
1870 l_return_val BOOLEAN;
1871
1872 BEGIN
1873
1874 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1875 l_return_val := FALSE;
1876
1877 IF (g_level_statement >= g_current_runtime_level ) THEN
1878 FND_LOG.STRING(g_level_statement,
1879 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.bill_to.BEGIN',
1880 'ZX_TDS_PROCESS_CEC_PVT.bill_to (+)');
1881 END IF;
1882
1883 l_return_val := compare_condition ( g_cec_bill_to_site_use_id,
1884 'BILL_TO',
1885 p_classification,
1886 p_operator,
1887 p_value);
1888 if l_return_val = TRUE then
1889 IF (g_level_statement >= g_current_runtime_level ) THEN
1890 FND_LOG.STRING(g_level_statement,
1891 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.bill_to',
1892 'The condition Bill to '||p_classification||
1893 p_operator||p_value||' evaluates to TRUE');
1894 END IF;
1895 else
1896 IF (g_level_statement >= g_current_runtime_level ) THEN
1897 FND_LOG.STRING(g_level_statement,
1898 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.bill_to',
1899 'The condition Bill to '||p_classification||
1900 p_operator||p_value||' evaluates to FALSE');
1901 END IF;
1902 end if;
1903 -- Only when p_operator is not NOT_FOUND, update pr_stats_rec_tbl.
1904 if (p_operator <> 'NOT_FOUND') then
1905 update_pr_stats_rec_tbl(p_classification => p_classification,
1906 p_index => IDX_BILL_TO,
1907 p_flag => l_return_val);
1908 dump_stats_rec(IDX_BILL_TO);
1909 end if;
1910 IF (g_level_statement >= g_current_runtime_level ) THEN
1911 FND_LOG.STRING(g_level_statement,
1912 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.bill_to.END',
1913 'ZX_TDS_PROCESS_CEC_PVT.bill_to (-)');
1914 END IF;
1915 return l_return_val;
1916 END bill_to;
1917
1918 --------------------------------------------------------------------------
1919 -- This function checks whether the condition (built using input parameters)
1920 -- evaluates to TRUE or FALSE for a point of Order Origin (identified
1921 -- through tax_info_rec.poo_id)
1922
1923 FUNCTION poo
1924 (p_classification IN VARCHAR2 Default NULL,
1925 p_operator IN VARCHAR2 Default NULL,
1926 p_value IN VARCHAR2 DEFAULT NULL)
1927 return BOOLEAN is
1928
1929 l_return_val BOOLEAN;
1930
1931 BEGIN
1932
1933 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1934 l_return_val := FALSE;
1935
1936 IF (g_level_statement >= g_current_runtime_level ) THEN
1937 FND_LOG.STRING(g_level_statement,
1938 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poo.BEGIN',
1939 'ZX_TDS_PROCESS_CEC_PVT.poo (+)');
1940
1941 END IF;
1942
1943 l_return_val := compare_condition ( g_cec_poo_location_id,
1944 'POO',
1945 p_classification,
1946 p_operator,
1947 p_value);
1948 if l_return_val = TRUE then
1949 IF (g_level_statement >= g_current_runtime_level ) THEN
1950 FND_LOG.STRING(g_level_statement,
1951 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poo',
1952 'The condition POO '||p_classification||
1953 p_operator||p_value||' evaluates to TRUE');
1954 END IF;
1955 else
1956 IF (g_level_statement >= g_current_runtime_level ) THEN
1957 FND_LOG.STRING(g_level_statement,
1958 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poo',
1959 'The condition POO '||p_classification||
1960 p_operator||p_value||' evaluates to FALSE');
1961 END IF;
1962 end if;
1963 -- Only when p_oprator is not NOT_FOUND, update pr_stats_rec_tbl.
1964 if (p_operator <> 'NOT_FOUND') then
1965 update_pr_stats_rec_tbl(p_classification => p_classification,
1966 p_index => IDX_POO,
1967 p_flag => l_return_val);
1968 dump_stats_rec(IDX_POO); --for bug1833141
1969 end if;
1970 IF (g_level_statement >= g_current_runtime_level ) THEN
1971 FND_LOG.STRING(g_level_statement,
1972 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poo',
1976 'ZX_TDS_PROCESS_CEC_PVT.poo (-)');
1973 'Point of Origin (-)');
1974 FND_LOG.STRING(g_level_statement,
1975 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poo.END',
1977 END IF;
1978 return l_return_val;
1979 END poo;
1980
1981 ---------------------------------------------------------------------------
1982 -- This function checks whether the condition (built using input parameters)
1983 -- evaluates to TRUE or FALSE for a point of Order Acceptance (identified
1984 -- through tax_info_rec.poa_id)
1985
1986 FUNCTION poa
1987 (p_classification IN VARCHAR2 Default NULL,
1988 p_operator IN VARCHAR2 Default NULL,
1989 p_value IN VARCHAR2 DEFAULT NULL)
1990 return BOOLEAN is
1991
1992 l_return_val BOOLEAN;
1993
1994 BEGIN
1995
1996 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1997 l_return_val := FALSE;
1998
1999 IF (g_level_statement >= g_current_runtime_level ) THEN
2000 FND_LOG.STRING(g_level_statement,
2001 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poa.BEGIN',
2002 'ZX_TDS_PROCESS_CEC_PVT.poa (+)');
2003
2004 END IF;
2005
2006 l_return_val := compare_condition ( g_cec_poa_location_id,
2007 'POA',
2008 p_classification,
2009 p_operator,
2010 p_value);
2011 if l_return_val = TRUE then
2012 IF (g_level_statement >= g_current_runtime_level ) THEN
2013 FND_LOG.STRING(g_level_statement,
2014 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poa',
2015 'The condition POA '||p_classification||
2016 p_operator||p_value||' evaluates to TRUE');
2017 END IF;
2018 else
2019 IF (g_level_statement >= g_current_runtime_level ) THEN
2020 FND_LOG.STRING(g_level_statement,
2021 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poa',
2022 'The condition POA '||p_classification||
2023 p_operator||p_value||' evaluates to FALSE');
2024 END IF;
2025 end if;
2026
2027 if (p_operator <> 'NOT_FOUND') then
2028 update_pr_stats_rec_tbl(p_classification => p_classification,
2029 p_index => IDX_POA,
2030 p_flag => l_return_val);
2031 dump_stats_rec(IDX_POA);
2032 end if;
2033 IF (g_level_statement >= g_current_runtime_level ) THEN
2034
2035 FND_LOG.STRING(g_level_statement,
2036 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.poa.END',
2037 'ZX_TDS_PROCESS_CEC_PVT.poa (-)');
2038 END IF;
2039 return l_return_val;
2040 END poa;
2041
2042 -------------------------------------------------------------------------
2043 -- This function checks whether the condition (built using input parameters)
2044 -- evaluates to TRUE or FALSE for an transaction (Identified by tax_info_rec.
2045 -- customer_trx_id)
2046
2047 FUNCTION trx
2048 (p_classification IN VARCHAR2 Default NULL,
2049 p_operator IN VARCHAR2 Default NULL,
2050 p_value IN VARCHAR2 DEFAULT NULL)
2051 return BOOLEAN is
2052
2053 l_return_val BOOLEAN;
2054
2055 BEGIN
2056
2057 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2058 l_return_val := FALSE;
2059
2060 IF (g_level_statement >= g_current_runtime_level ) THEN
2061 FND_LOG.STRING(g_level_statement,
2062 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.trx.BEGIN',
2063 'ZX_TDS_PROCESS_CEC_PVT.trx (+)');
2064
2065 END IF;
2066
2067 l_return_val := compare_condition (
2068 g_cec_trx_id,
2069 'TRX',
2070 p_classification,
2071 p_operator,
2072 p_value);
2073 if l_return_val = TRUE then
2074 IF (g_level_statement >= g_current_runtime_level ) THEN
2075 FND_LOG.STRING(g_level_statement,
2076 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.trx',
2077 'The Trx condition evaluates to TRUE');
2078 END IF;
2079 else
2080 IF (g_level_statement >= g_current_runtime_level ) THEN
2081 FND_LOG.STRING(g_level_statement,
2082 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.trx',
2083 'The Trx condition evaluates to FALSE');
2084 END IF;
2085 end if;
2086 -- Only when p_oprator is not NOT_FOUND, update pr_stats_rec_tbl.
2087 if (p_operator <> 'NOT_FOUND') then
2088 update_pr_stats_rec_tbl(p_classification => p_classification,
2089 p_index => IDX_TRX,
2090 p_flag => l_return_val);
2091 dump_stats_rec(IDX_TRX); --for bug1833141
2092 end if;
2093 IF (g_level_statement >= g_current_runtime_level ) THEN
2094
2095 FND_LOG.STRING(g_level_statement,
2096 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.trx.END',
2097 'ZX_TDS_PROCESS_CEC_PVT.trx (-)');
2098 END IF;
2099
2100 return l_return_val;
2101 END trx;
2102
2103 -------------------------------------------------------------------------
2104 -- This function checks whether the condition (built using input parameters)
2105 -- evaluates to TRUE or FALSE for an item (identified through a transaction
2106 -- line)
2107
2108 FUNCTION item (p_classification IN VARCHAR2 Default NULL,
2109 p_operator IN VARCHAR2 Default NULL,
2110 p_value IN VARCHAR2 DEFAULT NULL) return BOOLEAN is
2111
2112 l_return_val BOOLEAN ;
2113
2117 l_return_val := FALSE;
2114 BEGIN
2115
2116 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2118
2119 IF (g_level_statement >= g_current_runtime_level ) THEN
2120 FND_LOG.STRING(g_level_statement,
2121 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.item.BEGIN',
2122 'ZX_TDS_PROCESS_CEC_PVT.item (+)');
2123 END IF;
2124
2125
2126 l_return_val := compare_condition ( g_cec_trx_line_id,
2127 'ITEM',
2128 p_classification,
2129 p_operator,
2130 p_value);
2131 if l_return_val = TRUE then
2132 IF (g_level_statement >= g_current_runtime_level ) THEN
2133 FND_LOG.STRING(g_level_statement,
2134 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.item',
2135 'Item Condition Evaluates to TRUE');
2136 END IF;
2137 else
2138 IF (g_level_statement >= g_current_runtime_level ) THEN
2139 FND_LOG.STRING(g_level_statement,
2140 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.item',
2141 'Item Condition Evaluates to FALSE');
2142 END IF;
2143 end if;
2144 --
2145 -- Only when p_oprator is not NOT_FOUND, update pr_stats_rec_tbl.
2146 --
2147 if (p_operator <> 'NOT_FOUND') then
2148 --
2149 update_pr_stats_rec_tbl(p_classification => p_classification,
2150 p_index => IDX_ITEM,
2151 p_flag => l_return_val);
2152 --
2153 dump_stats_rec(IDX_ITEM); --for bug1833141
2154 end if;
2155
2156 IF (g_level_statement >= g_current_runtime_level ) THEN
2157 FND_LOG.STRING(g_level_statement,
2158 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.item.END',
2159 'ZX_TDS_PROCESS_CEC_PVT.item (-)');
2160 END IF;
2161 return l_return_val;
2162 END item;
2163
2164 -------------------------------------------------------------------------
2165 -- The procedure user_message puts the user supplied message on the message
2166 -- stack
2167
2168 PROCEDURE user_message (p_msg IN VARCHAR2 default NULL) is
2169 BEGIN
2170
2171 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2172
2173 IF (g_level_statement >= g_current_runtime_level ) THEN
2174 FND_LOG.STRING(g_level_statement,
2175 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.user_message.BEGIN',
2176 'ZX_TDS_PROCESS_CEC_PVT.user_message (+)');
2177 END IF;
2178 if (p_msg is not null) then
2179 pr_message_token := p_msg;
2180 end if;
2181 IF (g_level_statement >= g_current_runtime_level ) THEN
2182 FND_LOG.STRING(g_level_statement,
2183 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.user_message.END',
2184 'ZX_TDS_PROCESS_CEC_PVT.user_message (-)');
2185 END IF;
2186
2187 END user_message;
2188
2189 ------------------------------------------------------------------------
2190 -- The procedure system_message puts the user message, as well as the
2191 -- Oracle error message on the message stack
2192
2193 PROCEDURE system_message (p_msg IN VARCHAR2 default NULL) is
2194
2195 BEGIN
2196
2197 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2198
2199 IF (g_level_statement >= g_current_runtime_level ) THEN
2200 FND_LOG.STRING(g_level_statement,
2201 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.system_message.BEGIN',
2202 'ZX_TDS_PROCESS_CEC_PVT.system_message (+)');
2203 END IF;
2204
2205 if (p_msg is not null) then
2206 pr_message_token := fnd_message.get_string('AR',p_msg);
2207 if(pr_message_token is null) then
2208 pr_message_token := 'System Error';
2209 end if;
2210 end if;
2211
2212 IF (g_level_statement >= g_current_runtime_level ) THEN
2213 FND_LOG.STRING(g_level_statement,
2214 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.system_message.END',
2215 'ZX_TDS_PROCESS_CEC_PVT.system_message (-)');
2216 END IF;
2217
2218 END system_message;
2219
2220 PROCEDURE apply_exception (p_exception IN VARCHAR2 default NULL) is
2221 BEGIN
2222
2223 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2224
2225 IF (g_level_statement >= g_current_runtime_level ) THEN
2226 FND_LOG.STRING(g_level_statement,
2227 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.apply_exception.BEGIN',
2228 'ZX_TDS_PROCESS_CEC_PVT.Apply_Exception (+)');
2229 END IF;
2230 -- set pr_tax_rate with p_exception.
2231 if (p_exception is not null) then
2232 pr_tax_rate := to_number(p_exception);
2233 end if;
2234
2235 IF (g_level_statement >= g_current_runtime_level ) THEN
2236 FND_LOG.STRING(g_level_statement,
2237 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.apply_exception.END',
2238 'ZX_TDS_PROCESS_CEC_PVT.Apply_Exception (-)');
2239 END IF;
2240
2241 END apply_exception;
2242
2243 PROCEDURE do_not_apply_exception (p_exception IN VARCHAR2 default NULL) is
2244 BEGIN
2245
2246 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2247
2248 IF (g_level_statement >= g_current_runtime_level ) THEN
2249 FND_LOG.STRING(g_level_statement,
2250 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.do_not_apply_exception.BEGIN',
2251 'ZX_TDS_PROCESS_CEC_PVT.Do_Not_Apply_Exception (+)');
2252 END IF;
2253 NULL;
2254 IF (g_level_statement >= g_current_runtime_level ) THEN
2255 FND_LOG.STRING(g_level_statement,
2256 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.do_not_apply_exception.END',
2257 'ZX_TDS_PROCESS_CEC_PVT.Do_Not_Apply_Exception (-)');
2258 END IF;
2259
2260 END do_not_apply_exception;
2261
2262 PROCEDURE use_tax_code (p_tax_code IN VARCHAR2 default NULL) is
2263 BEGIN
2264
2265 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2266
2267 IF (g_level_statement >= g_current_runtime_level ) THEN
2268 FND_LOG.STRING(g_level_statement,
2269 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.use_tax_code.BEGIN',
2270 'ZX_TDS_PROCESS_CEC_PVT.use_tax_code (+)');
2271 FND_LOG.STRING(g_level_statement,
2272 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.use_tax_code.END',
2273 'ZX_TDS_PROCESS_CEC_PVT.use_tax_code (-)');
2274 END IF;
2275
2276 END use_tax_code;
2277
2278 PROCEDURE use_this_tax_code (p_tax_code IN VARCHAR2 default NULL) is
2279 BEGIN
2280
2281 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2282
2283 IF (g_level_statement >= g_current_runtime_level ) THEN
2284 FND_LOG.STRING(g_level_statement,
2285 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.use_this_tax_code.BEGIN',
2286 'ZX_TDS_PROCESS_CEC_PVT.use_this_tax_code (+)');
2287 FND_LOG.STRING(g_level_statement,
2288 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.use_this_tax_code.END',
2289 'ZX_TDS_PROCESS_CEC_PVT.use_this_tax_code (-)');
2290 END IF;
2291
2292 END use_this_tax_code;
2293
2294 PROCEDURE default_tax_code (p_tax_code IN VARCHAR2 default NULL) is
2295 BEGIN
2296
2297 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2298
2299 IF (g_level_statement >= g_current_runtime_level ) THEN
2300 FND_LOG.STRING(g_level_statement,
2301 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.default_tax_code.BEGIN',
2302 'ZX_TDS_PROCESS_CEC_PVT.default_tax_code (+)');
2303 FND_LOG.STRING(g_level_statement,
2304 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.default_tax_code.END',
2305 'ZX_TDS_PROCESS_CEC_PVT.default_tax_code (-)');
2306 END IF;
2307
2308 END default_tax_code;
2309
2310 PROCEDURE use_this_tax_group (p_tax_group_code IN VARCHAR2 default NULL) is
2311 BEGIN
2312
2313 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2314
2315 IF (g_level_statement >= g_current_runtime_level ) THEN
2316 FND_LOG.STRING(g_level_statement,
2317 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.use_this_tax_group.BEGIN',
2318 'ZX_TDS_PROCESS_CEC_PVT.use_this_tax_group (+)');
2319
2320 FND_LOG.STRING(g_level_statement,
2321 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.use_this_tax_group.END',
2322 'ZX_TDS_PROCESS_CEC_PVT.use_this_tax_group (-)');
2323 END IF;
2324
2325 END use_this_tax_group;
2326
2327 PROCEDURE do_not_use_this_tax_code(p_param IN VARCHAR2 DEFAULT NULL) is
2328 BEGIN
2329
2330 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2331
2332 IF (g_level_statement >= g_current_runtime_level ) THEN
2333 FND_LOG.STRING(g_level_statement,
2334 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_code.BEGIN',
2335 'ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_code (+)');
2336 END IF;
2337 pr_do_not_use_this_tax_flag := TRUE;
2338 IF (g_level_statement >= g_current_runtime_level ) THEN
2339 FND_LOG.STRING(g_level_statement,
2340 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_code.END',
2341 'ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_code (-)');
2342 END IF;
2343
2344 END do_not_use_this_tax_code;
2345
2346 PROCEDURE do_not_use_this_tax_group(p_param IN VARCHAR2 DEFAULT NULL) is
2347 BEGIN
2348
2349 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2350
2351 IF (g_level_statement >= g_current_runtime_level ) THEN
2352 FND_LOG.STRING(g_level_statement,
2353 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_group.BEGIN',
2354 'ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_group (+)');
2355
2356 END IF;
2357 pr_do_not_use_this_group_flag := TRUE;
2358 IF (g_level_statement >= g_current_runtime_level ) THEN
2359
2360 FND_LOG.STRING(g_level_statement,
2361 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_group.END',
2362 'ZX_TDS_PROCESS_CEC_PVT.do_not_use_this_tax_group (-)');
2363 END IF;
2364
2365 END do_not_use_this_tax_group;
2366
2367 /*===========================================================================+
2368 | FUNCTION |
2369 | evaluate_cec |
2370 | |
2371 | DESCRIPTION |
2372 | This procedure evaluates Condition Set, Exception Set and Constraint |
2373 | defined in old GTE model for a given Tax Group based on the passed |
2374 | values like Condition Set Id, Exception Set Id and Constraint Id. |
2375 | The old Tax Groups model along with its associated Condition Set Id, |
2376 | Exception Set Id and Constraint Id is migrated into Tax Rules Model of |
2377 | E-Business Tax during Defaulting Hierarchy Migration into Associated |
2378 | Condition Groups of Rules for Condition Set Id and Exception Set Id |
2379 | and into Condition Groups for Constraint Id. |
2380 | |
2381 | SCOPE |
2382 | PUBLIC |
2383 | |
2384 +===========================================================================*/
2385
2386 PROCEDURE evaluate_cec (p_constraint_id IN NUMBER DEFAULT NULL,
2387 p_condition_set_id IN NUMBER DEFAULT NULL,
2388 p_exception_set_id IN NUMBER DEFAULT NULL,
2389 p_cec_ship_to_party_site_id IN NUMBER,
2390 p_cec_bill_to_party_site_id IN NUMBER,
2391 p_cec_ship_to_party_id IN NUMBER,
2392 p_cec_bill_to_party_id IN NUMBER,
2393 p_cec_poo_location_id IN NUMBER,
2394 p_cec_poa_location_id IN NUMBER,
2395 p_cec_trx_id IN NUMBER,
2396 p_cec_trx_line_id IN NUMBER,
2397 p_cec_ledger_id IN NUMBER,
2398 p_cec_internal_organization_id IN NUMBER,
2399 p_cec_so_organization_id IN NUMBER,
2400 p_cec_product_org_id IN NUMBER,
2401 p_cec_product_id IN NUMBER,
2402 p_cec_trx_type_id IN NUMBER,
2403 p_cec_trx_line_date IN DATE,
2404 p_cec_fob_point IN VARCHAR2,
2405 p_cec_ship_to_site_use_id IN VARCHAR2,
2406 p_cec_bill_to_site_use_id IN VARCHAR2,
2407 p_cec_result OUT NOCOPY BOOLEAN,
2408 p_action_rec_tbl OUT NOCOPY action_rec_tbl_type,
2409 p_return_status OUT NOCOPY VARCHAR2,
2410 p_error_buffer OUT NOCOPY VARCHAR2) is
2411
2412 cursor loc_qualifier_csr(c_trx_date DATE) is
2413 select lookup_code
2414 from ar_lookups
2415 where LOOKUP_TYPE = 'ARTAXVDR_LOC_QUALIFIER'
2416 and enabled_flag = 'Y'
2417 and trunc(c_trx_date) between
2418 trunc(start_date_active) and
2419 trunc(nvl(end_date_active, c_trx_date));
2420
2421 l_amt_incl_tax_override VARCHAR2(1);
2422
2423 l_index NUMBER;
2424 l_grp_index NUMBER;
2425
2426 l_vat_tax_id NUMBER;
2427 l_tax_type VARCHAR2(30);
2428 l_constraint_result BOOLEAN;
2429 l_condition_result BOOLEAN;
2430 l_exception_result BOOLEAN;
2431
2432 l_total_lines NUMBER;
2433
2434 TYPE qualifier_tbl_type is TABLE of ar_lookups.lookup_code%TYPE index by binary_integer;
2435 l_qualifier_tbl qualifier_tbl_type;
2436 l_code_tax_type VARCHAR2(30);
2437 l_lookup_code ar_lookups.lookup_code%TYPE;
2438 l_tax_compiled_constraint VARCHAR2(2500);
2439 l_tax_compiled_condition VARCHAR2(2500);
2440 l_tax_compiled_exception VARCHAR2(2500);
2441 l_true_compiled_action VARCHAR2(4000);
2442 l_false_compiled_action VARCHAR2(4000);
2443
2444
2445
2446 BEGIN
2447
2448 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2449
2450 IF (g_level_statement >= g_current_runtime_level ) THEN
2451 FND_LOG.STRING(g_level_statement,
2452 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec.BEGIN',
2453 'ZX_TDS_PROCESS_CEC_PVT: evaluate_cec(+)');
2454 END IF;
2455
2456 p_return_status := FND_API.G_RET_STS_SUCCESS;
2457
2458
2459 l_index := 0;
2460 l_grp_index := 0;
2461 l_total_lines := 1;
2462
2463 -- Initialize parameters required for processing constraint/condition set/exception set
2464 g_cec_ship_to_party_site_id := p_cec_ship_to_party_site_id;
2465 g_cec_bill_to_party_site_id := p_cec_bill_to_party_site_id;
2466 g_cec_ship_to_party_id := p_cec_ship_to_party_id;
2467 g_cec_bill_to_party_id := p_cec_bill_to_party_id;
2468 g_cec_poo_location_id := p_cec_poo_location_id;
2469 g_cec_poa_location_id := p_cec_poa_location_id;
2470 g_cec_trx_id := p_cec_trx_id;
2471 g_cec_trx_line_id := p_cec_trx_line_id;
2472 g_cec_ledger_id := p_cec_ledger_id;
2473 g_cec_internal_organization_id := p_cec_internal_organization_id;
2474 g_cec_so_organization_id := p_cec_so_organization_id;
2475 g_cec_product_org_id := p_cec_product_org_id;
2476 g_cec_product_id := p_cec_product_id;
2477 g_cec_trx_line_date := p_cec_trx_line_date;
2478 g_cec_trx_type_id := p_cec_trx_type_id;
2479 g_cec_fob_point := p_cec_fob_point;
2480 g_cec_ship_to_site_use_id :=p_cec_ship_to_site_use_id;
2481 g_cec_bill_to_site_use_id :=p_cec_bill_to_site_use_id;
2482
2483 -- Evaluate Condition, Constraint, Exception
2484 -- In each call, we will have only one of condition_set_id, constraint_id or exception_set_id
2485 -- populated but these values will not be populated simultaneously.
2486
2487 p_action_rec_tbl.delete;
2488
2489 If p_constraint_id IS NOT NULL then
2490
2491 l_tax_compiled_constraint := create_compiled_lines(p_constraint_id);
2492 -- l_true_compiled_action := create_compiled_action(p_constraint_id, 'TRUE'); --Move this below to ensure one action
2493 -- l_false_compiled_action := create_compiled_action(p_constraint_id, 'FALSE');--Move this below to ensure one action
2494
2495 l_constraint_result := evaluate_cec_lines(l_tax_compiled_constraint);
2496 if (l_constraint_result) then
2497 l_true_compiled_action := create_compiled_action(p_constraint_id, 'TRUE'); --Bug 5691957
2498 evaluate_cec_action(l_true_compiled_action, 'CONSTRAINT');
2499 else
2500 l_false_compiled_action := create_compiled_action(p_constraint_id, 'FALSE');--Bug 5691957
2501 evaluate_cec_action(l_false_compiled_action, 'CONSTRAINT');
2502 end if;
2503
2504 End If;
2505
2506
2507 If NVL(pr_do_not_use_this_group_flag, FALSE) then
2508 IF (g_level_statement >= g_current_runtime_level ) THEN
2509 FND_LOG.STRING(g_level_statement,
2510 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec',
2511 'Constraint Evaluated to False - Do not use the Condition Group');
2512
2513 END IF;
2514 p_cec_result := false;
2515 End If;
2516
2517 If not NVL(pr_do_not_use_this_group_flag, FALSE) then
2518 If p_condition_set_id is NULL and p_exception_set_id is null then
2519 p_cec_result := true;
2520 else
2521 -- Evaluate Condition Set
2522 l_condition_result := true; -- bugfix 5572117
2523 if p_condition_set_id IS NOT NULL then
2524 l_tax_compiled_condition := create_compiled_lines(p_condition_set_id);
2525 -- l_true_compiled_action := create_compiled_action(p_condition_set_id, 'TRUE');--Move this below to ensure one action
2526 -- l_false_compiled_action := create_compiled_action(p_condition_set_id, 'FALSE');--Move this below to ensure one action
2527
2528 l_condition_result := evaluate_cec_lines(l_tax_compiled_condition);
2529 if (l_condition_result) then
2530 l_true_compiled_action := create_compiled_action(p_condition_set_id, 'TRUE'); --Bug 5691957
2531 evaluate_cec_action(l_true_compiled_action, 'CONDITION');
2532 p_cec_result := true;
2533 else
2534 l_false_compiled_action := create_compiled_action(p_condition_set_id, 'FALSE'); --Bug 5691957
2535 evaluate_cec_action(l_false_compiled_action, 'CONDITION');
2536 p_cec_result := false;
2537 end if;
2538
2539 end if;
2540
2541 -- When Condition evaluates to TRUE, Evaluate Exception Set if it exists
2542 if p_exception_set_id IS NOT NULL and l_condition_result then
2543 l_tax_compiled_exception := create_compiled_lines(p_exception_set_id);
2544 -- l_true_compiled_action := create_compiled_action(p_exception_set_id, 'TRUE');--Move this below to ensure one action
2545 -- l_false_compiled_action := create_compiled_action(p_exception_set_id, 'FALSE');--Move this below to ensure one action
2546
2547 if (evaluate_cec_lines(l_tax_compiled_exception)) then
2548 l_true_compiled_action := create_compiled_action(p_exception_set_id, 'TRUE');--Bug 5691957
2549 evaluate_cec_action( l_true_compiled_action, 'EXCEPTION');
2550 p_cec_result := true;
2551 else
2552 l_false_compiled_action := create_compiled_action(p_exception_set_id, 'FALSE');--Bug 5691957
2553 evaluate_cec_action( l_false_compiled_action, 'EXCEPTION');
2554 p_cec_result := false;
2555 end if;
2556
2557 end if;
2558 End If;
2559 End If;
2560
2561 p_action_rec_tbl := pr_action_rec_tbl;
2562
2563 IF (g_level_statement >= g_current_runtime_level ) THEN
2564 FND_LOG.STRING(g_level_statement,
2565 'ZX.PLSQL.ZX_TDS_PROCESS_CEC_PVT.evaluate_cec.END',
2566 'ZX_TDS_PROCESS_CEC_PVT: evaluate_cec(-)');
2567 END IF;
2568
2569 exception
2570 when no_data_found then
2571 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2572
2573 raise;
2574
2575 when others then
2576 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2577
2578 app_exception.raise_exception;
2579
2580 end evaluate_cec;
2581
2582 BEGIN
2583 init_stats_rec_tbl;
2584
2585 END ZX_TDS_PROCESS_CEC_PVT;