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