1 package body jai_credit_check_pkg as
2 /* $Header: jai_credit_check.plb 120.5 2011/10/12 08:34:48 qioliu noship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2011 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================+
8 --| FILENAME |
9 --| jai_credit_check_pkg.pck |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to include exclusive tax for credit check |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE process_tax_credit_check |
16 --| |_FUNCTION check_credit_setup |
17 --| |_FUNCTION check_tax_amount_difference |
18 --| |_FUNCTION get_jai_tax_amount |
19 --| |_PROCEDURE process_order_line |
20 --| PROCEDURE process_trading_tax_amount |
21 --| |
22 --| HISTORY |
23 --| 08/24/2011 qiong.liu Created |
24 --| 09/02/2011 zhiwei.xin Modified for code review |
25 --+======================================================================*/
26
27
28
29 /*-------------------------------------------------------------------------------------------------------------------------------+
30 | Created By : qiong.liu |
31 | Creation Date : 08/24/2011 |
32 | Bug Number/ER Name : Credit Check |
33 | SubProgram Name : check_credit_setup |
34 | Type : FUNCTION |
35 | Purpose : check SO Credit Check whether be enabled |
36 | TDD Reference : Section 7.6 |
37 | Assumptions : |
38 | Called From : jai_credit_check_pkg.process_tax_credit_check |
39 |--------------------------------------------------------------------------------------------------------------------------------|
40 | parameters IN/OUT Type Required Description and Purpose |
41 | ------------ -------- ------ ---------- ------------------------- |
42 | pn_cust_account_id IN NUMBER yes Customer account id |
43 | pn_site_use_id IN NUMBER yes Site use id |
44 | credit_check_enabled OUT VARCHAR2 no return by this function to validate |
45 | whether credit check is enabled |
46 | if enabled return 'Y' else return 'N' |
47 ---------------------------------------------------------------------------------------------------------------------------------*/
48 Function check_credit_setup(
49 pn_cust_account_id IN NUMBER,
50 pn_site_use_id IN NUMBER
51 ) RETURN VARCHAR2
52 IS
53
54 --Cusor which is used to validate Credit Check whether be enabled on customer site level
55 CURSOR chk_credit_check_site
56 IS
57 select HZ_CUSTOMER_PROFILES.credit_checking
58 from HZ_CUST_SITE_USES_all,
59 hz_cust_acct_sites_all,
60 HZ_CUSTOMER_PROFILES
61 where hz_cust_acct_sites_all.cust_acct_site_id = HZ_CUST_SITE_USES_all.cust_acct_site_id
62 and hz_cust_acct_sites_all.cust_account_id = pn_cust_account_id
63 and HZ_CUST_SITE_USES_all.site_use_code = 'BILL_TO'
64 and HZ_CUST_SITE_USES_all.site_use_id = HZ_CUSTOMER_PROFILES.site_use_id
65 and HZ_CUST_SITE_USES_all.site_use_id = pn_site_use_id;
66
67 --Cursor is used to validate the Credit Check whether be enabled on customer accounting level
68 CURSOR chk_credit_check_account
69 IS
70 select HZ_CUSTOMER_PROFILES.credit_checking
71 from HZ_CUSTOMER_PROFILES
72 where HZ_CUSTOMER_PROFILES.cust_account_id = pn_cust_account_id
73 and HZ_CUSTOMER_PROFILES.site_use_id is NULL;
74
75 -- variable block
76 lv_credit_check HZ_CUSTOMER_PROFILES.credit_checking%TYPE;
77
78 BEGIN
79 --Firstly, check the available of Credit check on customer site level
80 OPEN chk_credit_check_site;
81 FETCH chk_credit_check_site
82 INTO lv_credit_check;
83 CLOSE chk_credit_check_site;
84
85 IF NVL(lv_credit_check,'#') <> '#'
86 THEN
87 RETURN lv_credit_check;
88 ELSE
89 --Secondly, check the available of Credit check on customer accounting level
90 OPEN chk_credit_check_account;
91 FETCH chk_credit_check_account
92 INTO lv_credit_check;
93 CLOSE chk_credit_check_account;
94
95 RETURN NVL(lv_credit_check,'N');
96 END IF;
97 END check_credit_setup;
98
99
100 /*-------------------------------------------------------------------------------------------------------------------------------+
101 | Created By : Zhiwei Xin |
102 | Creation Date : 08/24/2011 |
103 | Bug Number/ER Name : Credit Check |
104 | SubProgram Name : check_tax_amount_difference |
105 | Type : FUNCTION |
106 | Purpose : check tax amount difference between JAI LINE and OE order Line |
107 | TDD Reference : |
108 | Assumptions : |
109 | Called From : process_tax_credit_Check |
110 |--------------------------------------------------------------------------------------------------------------------------------|
111 | parameters IN/OUT Type Required Description and Purpose |
112 | ------------ -------- ------ ---------- ------------------------- |
113 | pn_so_order_id IN NUMBER yes Identifier of SO order from default NULL|
114 ---------------------------------------------------------------------------------------------------------------------------------*/
115 FUNCTION check_tax_amount_difference (pn_so_order_id IN NUMBER)
116 RETURN VARCHAR2
117 IS
118 -- Cursor used to get JAI line tax amount and OE_ORDER_LINES tax amount by given header id
119 CURSOR get_sum_tax_cur
120 IS
121 SELECT LINES_ALL.LINE_ID
122 ,LINES_ALL.ORDER_LINE_TAX_VALUE
123 ,NVL(SUM(LINES_ALL.JAI_TAX_LINE_AMOUNT),0) JAI_LINE_TAX_AMOUNT
124 FROM
125 (SELECT LINE.LINE_ID LINE_ID
126 ,NVL(LINE.TAX_VALUE,0) ORDER_LINE_TAX_VALUE
127 ,TAX.TAX_AMOUNT JAI_TAX_LINE_AMOUNT
128 FROM OE_ORDER_LINES LINE
129 ,JAI_OM_OE_SO_TAXES TAX
130 ,JAI_CMN_TAXES_ALL JCTA
131 WHERE LINE.HEADER_ID = pn_so_order_id
132 AND TAX.LINE_ID = LINE.LINE_ID
133 AND TAX.TAX_ID = JCTA.TAX_ID
134 AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
135 UNION ALL
136 SELECT LINE.LINE_ID LINE_ID
137 ,NVL(LINE.TAX_VALUE,0) ORDER_LINE_TAX_VALUE
138 ,TAX.TAX_AMOUNT JAI_TAX_LINE_AMOUNT
139 FROM OE_ORDER_LINES LINE
140 ,JAI_OM_OE_RMA_TAXES TAX
141 ,JAI_CMN_TAXES_ALL JCTA
142 WHERE LINE.HEADER_ID = pn_so_order_id
143 AND TAX.RMA_LINE_ID = LINE.LINE_ID
144 AND TAX.TAX_ID = JCTA.TAX_ID
145 AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
146 ) LINES_ALL
147 GROUP BY LINES_ALL.LINE_ID, LINES_ALL.ORDER_LINE_TAX_VALUE;
148
149 BEGIN
150 FOR tax_info_rec in get_sum_tax_cur
151 LOOP
152 IF tax_info_rec.ORDER_LINE_TAX_VALUE <> tax_info_rec.JAI_LINE_TAX_AMOUNT
153 THEN
154 RETURN 'Y';
155 END IF;
156 END LOOP;
157
158 RETURN 'N';
159 EXCEPTION
160 WHEN OTHERS
161 THEN
162 RAISE;
163 END check_tax_amount_difference;
164
165 /*-------------------------------------------------------------------------------------------------------------------------------+
166 | Created By : Zhiwei Xin |
167 | Creation Date : 08/24/2011 |
168 | Bug Number/ER Name : Credit Check |
169 | SubProgram Name : check_tax_amount_difference |
170 | Type : FUNCTION |
171 | Purpose : get jai tax amount |
172 | TDD Reference : |
173 | Assumptions : |
174 | Called From : process_tax_credit_Check |
175 |--------------------------------------------------------------------------------------------------------------------------------|
176 | parameters IN/OUT Type Required Description and Purpose |
177 | ------------ -------- ------ ---------- ------------------------- |
178 | pn_line_id IN NUMBER yes Identifier of jai order line |
179 ---------------------------------------------------------------------------------------------------------------------------------*/
180 FUNCTION get_jai_tax_amount(pn_line_id IN NUMBER)
181 RETURN NUMBER
182 IS
183 -- Cursor used to get JAI line tax amount by given line id
184 CURSOR get_sum_jai_line_tax_cur IS
185 SELECT TAX_AMOUNT_SUM
186 FROM
187 ( SELECT LINE.LINE_ID,
188 NVL(SUM(TAX.TAX_AMOUNT), 0) TAX_AMOUNT_SUM
189 FROM OE_ORDER_LINES LINE,
190 JAI_OM_OE_SO_TAXES TAX,
191 JAI_CMN_TAXES_ALL JCTA
192 WHERE LINE.LINE_ID = pn_line_id
193 AND TAX.LINE_ID = LINE.LINE_ID
194 AND TAX.TAX_ID = JCTA.TAX_ID
195 AND NVL(JCTA.INCLUSIVE_TAX_FLAG, 'N') = 'N'
196 GROUP BY LINE.LINE_ID
197 UNION ALL
198 SELECT LINE.LINE_ID,
199 NVL(SUM(TAX.TAX_AMOUNT), 0) TAX_AMOUNT_SUM
200 FROM OE_ORDER_LINES LINE,
201 JAI_OM_OE_RMA_TAXES TAX,
202 JAI_CMN_TAXES_ALL JCTA
203 WHERE LINE.LINE_ID = pn_line_id
204 AND TAX.RMA_LINE_ID = LINE.LINE_ID
205 AND TAX.TAX_ID = JCTA.TAX_ID
206 AND NVL(JCTA.INCLUSIVE_TAX_FLAG, 'N') = 'N'
207 GROUP BY LINE.LINE_ID
208 );
209
210 ln_jai_tax_amount NUMBER;
211
212 BEGIN
213 OPEN get_sum_jai_line_tax_cur;
214 FETCH get_sum_jai_line_tax_cur
215 INTO ln_jai_tax_amount;
216 CLOSE get_sum_jai_line_tax_cur;
217 RETURN ln_jai_tax_amount;
218 EXCEPTION
219 WHEN OTHERS THEN
220 RAISE;
221 END get_jai_tax_amount;
222
223 /*-------------------------------------------------------------------------------------------------------------------------------+
224 | Created By : qiong.liu |
225 | Creation Date : 08/24/2011 |
226 | Bug Number/ER Name : Credit Check |
227 | SubProgram Name : process_tax_credit_Check |
228 | Type : PROCEDURE |
229 | Purpose : control the main flow of process for credit check |
230 | TDD Reference : Section 7.1 |
231 | Assumptions : |
232 | Called From : concurrent program 'India - Add Tax Value to Sales Order' |
233 |--------------------------------------------------------------------------------------------------------------------------------|
234 | parameters IN/OUT Type Required Description and Purpose |
235 | ------------ -------- ------ ---------- ------------------------- |
236 | errbuf OUT NOCOPY VARCHAR2 no used by CP |
237 | retcode OUT NOCOPY VARCHAR2 no used by CP |
238 | pn_header_id_from IN NUMBER yes Identifier of SO order from default NULL|
239 | pn_header_id_to IN NUMBER yes Identifier of SO order to default NULL |
240 | pn_customer_id IN NUMBER yes Customer id default NULL |
241 | pn_order_type_id IN NUMBER yes Order type id default NULL |
242 ---------------------------------------------------------------------------------------------------------------------------------*/
243
244 PROCEDURE process_tax_credit_check(
245 errbuf OUT NOCOPY VARCHAR2
246 , retcode OUT NOCOPY VARCHAR2
247 , pn_customer_id NUMBER DEFAULT NULL
248 , pn_order_type_id NUMBER DEFAULT NULL
249 , pn_header_id_from NUMBER DEFAULT NULL
250 , pn_header_id_to NUMBER DEFAULT NULL
251 )
252 IS
253
254 ln_org_id number;
255
256 -- Cursor used to get the details of order headers by conditions.
257 -- The status should not be cancelled or closed.
258 CURSOR get_process_order_cur
259 IS
260 SELECT
261 OH.ORDER_TYPE_ID
262 ,OH.SOLD_TO_ORG_ID
263 ,OH.SHIP_TO_ORG_ID
264 ,OH.HEADER_ID
265 ,OH.ORDER_NUMBER
266 FROM OE_ORDER_HEADERS OH
267 WHERE OH.HEADER_ID between NVL(trim(pn_header_id_from),OH.HEADER_ID)
268 and NVL(trim(pn_header_id_to),OH.HEADER_ID)
269 AND OH.SOLD_TO_ORG_ID = NVL(pn_customer_id,OH.SOLD_TO_ORG_ID)
270 AND OH.order_type_id = NVL(pn_order_type_id,OH.order_type_id)
271 AND OH.FLOW_STATUS_CODE not in ('CANCELLED','CLOSED');
272
273 -- Cursor used to get the details of order headers by conditions and add a lock for update.
274 -- The status should not be cancelled or closed.
275 CURSOR get_process_order_lock_cur ( pn_so_header_id IN NUMBER)
276 IS
277 SELECT OH.HEADER_ID
278 FROM OE_ORDER_HEADERS OH
279 WHERE OH.HEADER_ID = pn_so_header_id
280 FOR UPDATE;
281
282 -- Cursor used to get the details of open order lines by header id.
283 CURSOR get_order_lines_cur(pn_header_id OE_ORDER_LINES.HEADER_ID%TYPE)
284 IS
285 SELECT
286 OL.LINE_ID line_id
287 ,OL.LINE_CATEGORY_CODE LINE_CATEGORY_CODE
288 ,JL.LINE_NUMBER LINE_NUMBER
289 ,NVL(OL.TAX_VALUE,0) TAX_VALUE
290 FROM OE_ORDER_LINES OL,
291 JAI_OM_OE_SO_LINES JL
292 WHERE OL.HEADER_ID = pn_header_id
293 AND OL.HEADER_ID = JL.HEADER_ID
294 AND OL.LINE_ID = JL.LINE_ID
295 AND OL.FLOW_STATUS_CODE not in ('CANCELLED','SHIPPED','FULFILLED','CLOSED')
296 AND OL.LINE_CATEGORY_CODE = 'ORDER'
297 AND NVL(OL.TAX_VALUE,0) <> NVL(JL.TAX_AMOUNT,0)
298 UNION ALL
299 SELECT
300 OL.LINE_ID line_id
301 ,OL.LINE_CATEGORY_CODE LINE_CATEGORY_CODE
302 ,JL.RMA_LINE_NUMBER LINE_NUMBER
303 ,NVL(OL.TAX_VALUE,0) TAX_VALUE
304 FROM OE_ORDER_LINES OL,
305 JAI_OM_OE_RMA_LINES JL
306 WHERE OL.HEADER_ID = pn_header_id
307 AND OL.HEADER_ID = JL.RMA_HEADER_ID
308 AND OL.LINE_ID = JL.RMA_LINE_ID
309 AND OL.FLOW_STATUS_CODE not in ('CANCELLED','SHIPPED','FULFILLED','CLOSED')
310 AND OL.LINE_CATEGORY_CODE = 'RETURN'
311 AND NVL(OL.TAX_VALUE,0) <> NVL(JL.TAX_AMOUNT,0);
312
313 -- Cursor used to get the name of order type
314 CURSOR get_type_name( cp_order_type_id NUMBER)
315 IS
316 SELECT NAME
317 FROM oe_order_types_v
318 WHERE order_type_id = cp_order_type_id;
319
320 --Variable Block
321 lv_error_flag VARCHAR2(1);
322 lv_process_message VARCHAR2(2000);
323 le_error EXCEPTION ;
324 lv_return_message VARCHAR2(2000);
325 lv_return_code VARCHAR2(100) ;
326 lv_order_type VARCHAR2(254) ;
327 ln_line_number NUMBER;
328 lb_need_update BOOLEAN;
329 ln_jai_line_tax_amount NUMBER;
330 lv_tax_difference_flag VARCHAR2(1) ;
331 lv_credit_setup_flag VARCHAR2(1) ;
332 lv_header_id OE_ORDER_HEADERS.HEADER_ID%TYPE;
333
334 --Main Block
335 BEGIN
336
337 -- 1st round header loop and check whether update is needed
338 FOR header_check_rec IN get_process_order_cur
339 LOOP
340
341 -- check tax amount difference between JAI LINE and OE order Line
342 lv_tax_difference_flag:= check_tax_amount_difference(header_check_rec.HEADER_ID);
343
344 lv_process_message := 'Check tax amount difference between JAI LINE and OE order Line.'
345 || 'Order Header ID: '||header_check_rec.header_id
346 || ', Check Result: ' ||lv_tax_difference_flag;
347 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
348
349 -- IF any tax amount in JAI LINE is different from the TAX AMOUNT of ORDER LINE
350 -- Then below program will synchronize the jai TAX amount to OE_ORDRER_LINES_ALL.TAX_VALUE for each ORDER line
351 -- ELSE the below programe unit will not process the order
352
353 IF (lv_tax_difference_flag ='Y')
354 THEN
355 -- Lock the processing order
356 OPEN get_process_order_lock_cur(header_check_rec.HEADER_ID);
357 FETCH get_process_order_lock_cur
358 INTO lv_header_id;
359 CLOSE get_process_order_lock_cur;
360
361 lv_process_message := 'Lock OE header for update.'
362 || 'Order Header ID: '|| lv_header_id;
363 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
364
365 -- Get the type name
366 OPEN get_type_name(header_check_rec.order_type_id);
367 FETCH get_type_name
368 INTO lv_order_type;
369 CLOSE get_type_name ;
370
371 SAVEPOINT PROC_MAIN_SVP;
372
373 BEGIN
374
375 lv_error_flag := 'N';
376
377 --Check whether the Credit check be enabled from two level
378 --1. Site level
379 --2. Account level
380 --if enabled return 'Y' else return 'N'
381 lv_credit_setup_flag := check_credit_setup
382 ( pn_cust_account_id => header_check_rec.sold_to_org_id
383 , pn_site_use_id => header_check_rec.ship_to_org_id
384 );
385
386 -- if credit check enabled
387 -- process each order line
388 IF ( lv_credit_setup_flag = 'Y')
389 THEN
390 lv_process_message := 'Order process started.'||'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number;
391 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
392
393 FOR order_line_rec IN get_order_lines_cur(header_check_rec.header_id)
394 LOOP
395 -- get jai tax amount
396 ln_jai_line_tax_amount := get_jai_tax_amount(pn_line_id =>order_line_rec.line_id);
397
398 -- Check the line difference and then handle the order line
399 IF (ln_jai_line_tax_amount <> order_line_rec.tax_value)
400 THEN
401 lv_process_message := 'Order line process started.'||'Order Type '||lv_order_type||
402 ' Order Number '||header_check_rec.order_number||' Order Line '||order_line_rec.LINE_NUMBER;
403 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
404
405 process_order_line
406 ( pn_header_id => header_check_rec.header_id
407 , pn_line_id => order_line_rec.line_id
408 , pv_line_type => order_line_rec.LINE_CATEGORY_CODE
409 , xv_return_code => lv_return_code
410 , xv_return_message => lv_return_message
411 );
412
413 ln_line_number := order_line_rec.LINE_NUMBER;
414 IF lv_return_code <> jai_constants.successful THEN
415 RAISE le_error;
416 ELSE
417 lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||
418 ' Order Line '||order_line_rec.LINE_NUMBER||' updated Tax Successfully.';
419 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
420 END IF;
421 END IF;--(ln_jai_line_tax_amount <> order_line_rec.tax_value)
422 END LOOP;
423 END IF;
424 EXCEPTION
425 WHEN le_error
426 THEN
427 lv_error_flag := 'Y';
428
429 ROLLBACK TO PROC_MAIN_SVP;
430
431 lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||' Order Line '||ln_line_number||' updated Tax failed.';
432 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
433
434 lv_process_message := 'Exception Information: '|| lv_return_message ;
435 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
436
437 GOTO NEXT_ORDER;
438 WHEN OTHERS
439 THEN
440
441 lv_error_flag := 'Y';
442
443 lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||' Order Line '||ln_line_number||' updated Tax failed.';
444 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
445
446 lv_process_message := 'Exception Information: '|| SQLERRM ;
447 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
448
449 ROLLBACK TO PROC_MAIN_SVP;
450
451 GOTO NEXT_ORDER;
452 END;
453
454 if(lv_error_flag = 'N')then
455 lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||' updated Tax Successfully.';
456 fnd_file.put_line(FND_FILE.LOG,lv_process_message);
457 end if;
458
459 <<NEXT_ORDER>>
460 NULL;
461 END IF; --lv_tax_difference_flag ='Y'
462 END LOOP; --header_check_rec
463
464 COMMIT;
465 END process_tax_credit_check;
466
467 /*-------------------------------------------------------------------------------------------------------------------------------+
468 | Created By : qiong.liu |
469 | Creation Date : 08/24/2011 |
470 | Bug Number/ER Name : Credit Check |
471 | SubProgram Name : process_order_line |
472 | Type : PROCEDURE |
473 | Purpose : attach exclusive tax amount to OE for Credit Check |
474 | TDD Reference : Section 7.2 |
475 | Assumptions : |
476 | Called From : jai_credit_check_pkg.process_tax_credit_check |
477 |--------------------------------------------------------------------------------------------------------------------------------|
478 | parameters IN/OUT Type Required Description and Purpose |
479 | ------------ -------- ------ ---------- ------------------------- |
480 | pn_header_id IN NUMBER yes Identifier of SO order |
481 | pn_line_id IN NUMBER yes Identifier of SO line |
482 | pv_line_type IN VARCHAR2 yes Identifier of SO line type |
483 | xv_return_code OUT VARCHAR2 yes Output for process code |
484 | xv_return_message OUT VARCHAR2 yes Output for process message |
485 ---------------------------------------------------------------------------------------------------------------------------------*/
486 PROCEDURE process_order_line(
487 pn_header_id IN NUMBER ,
488 pn_line_id IN NUMBER ,
489 pv_line_type IN VARCHAR2 ,
490 xv_return_code OUT NOCOPY VARCHAR2 ,
491 xv_return_message OUT NOCOPY VARCHAR2
492 )
493 IS
494
495 --Cursor used to get so tax details by line id.
496 CURSOR get_so_tax_cur(cn_line_id IN NUMBER)
497 IS
498 SELECT HDR.ORDER_NUMBER ORDER_NUMBER
499 ,LINE.LINE_NUMBER LINE_NUMBER
500 ,TAX.TAX_LINE_NO TAX_LINE_NO
501 ,HDR.HEADER_ID HEADER_ID
502 ,LINE.LINE_ID LINE_ID
503 ,TAX.TAX_ID TAX_ID
504 ,TAX.TAX_AMOUNT TAX_AMOUNT
505 ,TAX.TAX_RATE TAX_RATE
506 FROM OE_ORDER_HEADERS HDR
507 ,OE_ORDER_LINES LINE
508 ,JAI_OM_OE_SO_TAXES TAX
509 ,JAI_CMN_TAXES_ALL JCTA
510 WHERE HDR.HEADER_ID = LINE.HEADER_ID
511 AND TAX.LINE_ID = LINE.LINE_ID
512 AND LINE.LINE_ID = cn_line_id
513 AND TAX.TAX_ID = JCTA.TAX_ID
514 AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
515 AND NVL(TAX.TAX_AMOUNT,0) <> 0
516 ORDER BY HDR.HEADER_ID
517 ,LINE.LINE_ID
518 ,TAX.TAX_LINE_NO;
519
520
521 -- Cursor used to get rma tax details by line id.
522 CURSOR get_rma_tax_cur(cn_line_id IN NUMBER)
523 IS
524 SELECT HDR.ORDER_NUMBER ORDER_NUMBER
525 ,LINE.LINE_NUMBER LINE_NUMBER
526 ,TAX.TAX_LINE_NO TAX_LINE_NO
527 ,HDR.HEADER_ID HEADER_ID
528 ,LINE.LINE_ID LINE_ID
529 ,TAX.TAX_ID TAX_ID
530 ,TAX.TAX_AMOUNT TAX_AMOUNT
531 ,TAX.TAX_RATE TAX_RATE
532 FROM OE_ORDER_HEADERS HDR
533 ,OE_ORDER_LINES LINE
534 ,JAI_OM_OE_RMA_TAXES TAX
535 ,JAI_CMN_TAXES_ALL JCTA
536 WHERE HDR.HEADER_ID = LINE.HEADER_ID
537 AND TAX.RMA_LINE_ID = LINE.LINE_ID
538 AND LINE.LINE_ID = cn_line_id
539 AND TAX.TAX_ID = JCTA.TAX_ID
540 AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
541 AND NVL(TAX.TAX_AMOUNT,0) <> 0
542 ORDER BY HDR.HEADER_ID
543 ,LINE.LINE_ID
544 ,TAX.TAX_LINE_NO;
545
546 --Variable Block
547 ln_line_exclusive_tax_amt NUMBER := 0;
548 lv_object_name VARCHAR2(200) := 'jai_credit_check.process_order';
549 lv_line_type OE_ORDER_LINES.LINE_CATEGORY_CODE%type;
550
551 BEGIN
552 xv_return_code := jai_constants.successful ;
553 -- delete records
554 delete from OE_PRICE_ADJUSTMENTS
555 where HEADER_ID = pn_header_id
556 and line_id = pn_line_id
557 and LIST_LINE_TYPE_CODE = 'TAX';
558
559 fnd_file.put_line(FND_FILE.LOG,'Deleted records in oe_price_adjustments.'||'Header:'||pn_header_id||',line:'||pn_line_id);
560
561 IF nvl(pv_line_type,'####') = 'ORDER' THEN
562 -- SO order process
563 ln_line_exclusive_tax_amt := 0;
564
565 FOR so_tax_rec IN get_so_tax_cur(pn_line_id)
566 LOOP
567 -- insert records
568 INSERT INTO OE_PRICE_ADJUSTMENTS
569 (PRICE_ADJUSTMENT_ID
570 ,CREATION_DATE
571 ,CREATED_BY
572 ,LAST_UPDATE_DATE
573 ,LAST_UPDATED_BY
574 ,LAST_UPDATE_LOGIN
575 ,HEADER_ID
576 ,LINE_ID
577 ,AUTOMATIC_FLAG
578 ,LIST_LINE_TYPE_CODE
579 ,ARITHMETIC_OPERATOR
580 ,TAX_RATE_ID
581 ,OPERAND
582 ,ADJUSTED_AMOUNT
583 ,LOCK_CONTROL)
584 VALUES
585 (OE_PRICE_ADJUSTMENTS_S.NEXTVAL
586 ,SYSDATE
587 ,FND_GLOBAL.user_id
588 ,SYSDATE
589 ,fnd_global.user_id
590 ,FND_GLOBAL.login_id
591 ,so_tax_rec.HEADER_ID
592 ,so_tax_rec.LINE_ID
593 ,'N'
594 ,'TAX'
595 ,'AMT'
596 ,so_tax_rec.TAX_ID
597 ,so_tax_rec.TAX_RATE
598 ,so_tax_rec.TAX_AMOUNT
599 ,1
600 );
601 -- accumulate amounts
602 ln_line_exclusive_tax_amt := ln_line_exclusive_tax_amt + NVL(so_tax_rec.TAX_AMOUNT,0);
603 END LOOP;
604
605 fnd_file.put_line(FND_FILE.LOG,'Inserted records of SO into oe_price_adjustments.'||'Header:'||pn_header_id||',line:'||pn_line_id||',tax amount:'||ln_line_exclusive_tax_amt);
606
607 ELSIF nvl(pv_line_type,'####') = 'RETURN' THEN
608 --The RMA order process
609 ln_line_exclusive_tax_amt := 0;
610
611 FOR so_tax_rec IN get_rma_tax_cur(pn_line_id)
612 LOOP
613 -- insert records
614 INSERT INTO OE_PRICE_ADJUSTMENTS
615 (PRICE_ADJUSTMENT_ID
616 ,CREATION_DATE
617 ,CREATED_BY
618 ,LAST_UPDATE_DATE
619 ,LAST_UPDATED_BY
620 ,LAST_UPDATE_LOGIN
621 ,HEADER_ID
622 ,LINE_ID
623 ,AUTOMATIC_FLAG
624 ,LIST_LINE_TYPE_CODE
625 ,ARITHMETIC_OPERATOR
626 ,TAX_RATE_ID
627 ,OPERAND
628 ,ADJUSTED_AMOUNT
629 ,LOCK_CONTROL)
630 VALUES
631 (OE_PRICE_ADJUSTMENTS_S.NEXTVAL
632 ,SYSDATE
633 ,FND_GLOBAL.USER_ID
634 ,SYSDATE
635 ,FND_GLOBAL.USER_ID
636 ,FND_GLOBAL.LOGIN_ID
637 ,so_tax_rec.HEADER_ID
638 ,so_tax_rec.LINE_ID
639 ,'N'
640 ,'TAX'
641 ,'AMT'
642 ,so_tax_rec.TAX_ID
643 ,so_tax_rec.TAX_RATE
644 ,(-1) * so_tax_rec.TAX_AMOUNT
645 ,1
646 );
647 -- accumulate amounts
648 ln_line_exclusive_tax_amt := ln_line_exclusive_tax_amt + NVL(so_tax_rec.TAX_AMOUNT,0);
649 END LOOP;
650
651 fnd_file.put_line(FND_FILE.LOG,'Inserted records of RMA into oe_price_adjustments.'||'Header:'||pn_header_id||',line:'||pn_line_id||',tax amount:'||ln_line_exclusive_tax_amt);
652
653 END IF;
654
655 --Update tax value with exclusive tax summary.
656 UPDATE OE_ORDER_LINES
657 SET tax_value = ln_line_exclusive_tax_amt
658 WHERE header_id = pn_header_id
659 AND line_id = pn_line_id;
660 fnd_file.put_line(FND_FILE.LOG,'Updated oe_order_lines.'||'Header:'||pn_header_id||',line:'||pn_line_id||',tax value:'||ln_line_exclusive_tax_amt);
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 xv_return_code := jai_constants.unexpected_error;
665 xv_return_message := 'Encountered an error in JAI_CREDIT_CHECK_PKG.PROCESS_ORDER_LINE ' || substr(sqlerrm,1,1900);
666
667 END process_order_line;
668
669
670 /*-------------------------------------------------------------------------------------------------------------------------------+
671 | Created By : qiong.liu |
672 | Creation Date : 08/24/2011 |
673 | Bug Number/ER Name : Credit Check |
674 | SubProgram Name : process_trading_tax_amount |
675 | Type : PROCEDURE |
676 | Purpose : populate tax amount to OFI table when match receipts in trading cycle |
677 | TDD Reference : Section 7.5 |
678 | Assumptions : |
679 | Called From : jai_cmn_rcv_matching_pkg.om_default_taxes |
680 |--------------------------------------------------------------------------------------------------------------------------------|
681 | parameters IN/OUT Type Required Description and Purpose |
682 | ------------ -------- ------ ---------- ------------------------- |
683 | pn_so_line_id IN NUMBER yes Identifier of SO line |
684 | pn_receipt_id IN NUMBER yes Identifier of receipt |
685 | pn_ref_line_id IN NUMBER yes reference line id |
686 | pv_subinventory IN VARCHAR2 yes sub inventory |
687 ---------------------------------------------------------------------------------------------------------------------------------*/
688 PROCEDURE process_trading_tax_amount(
689 pn_so_line_id IN NUMBER
690 , pn_receipt_id IN NUMBER
691 , pn_ref_line_id IN NUMBER
692 , pv_subinventory IN VARCHAR2
693 )
694 IS
695
696 -- Cursor used to get exclusive tax amount
697 CURSOR c_tax_amount IS
698 SELECT sum(nvl(JOS.tax_amount,0))
699 FROM JAI_OM_OE_SO_TAXES JOS,
700 JAI_CMN_TAXES_ALL JCT
701 WHERE JOS.line_id = pn_so_line_id
702 AND JOS.tax_id = JCT.tax_id
703 AND NVL(JCT.inclusive_tax_flag,'N') = 'N';
704
705 -- variable block
706 ln_exclusive_tax NUMBER;
707 ln_count NUMBER;
708 lv_object_name VARCHAR2(200) := 'jai_credit_check_pkg.process_trading_tax_amount';
709
710 BEGIN
711 SELECT COUNT(wdd.delivery_detail_id)
712 INTO ln_count
713 FROM wsh_delivery_details wdd,
714 OE_ORDER_HEADERS oh,
715 OE_ORDER_LINES ol
716 WHERE wdd.source_header_number = oh.ORDER_NUMBER
717 AND wdd.source_header_id = oh.header_id
718 AND oh.header_id = ol.header_id
719 AND ol.line_id = pn_so_line_id
720 AND wdd.CONTAINER_FLAG = 'N'
721 AND wdd.SOURCE_CODE = 'OE'
722 AND wdd.RELEASED_STATUS IN ('N', 'R', 'S', 'Y', 'B', 'X');
723 IF ln_count = 1 THEN
724 UPDATE JAI_OM_OE_SO_TAXES jos
725 SET (jos.tax_amount,jos.Base_Tax_Amount,jos.Func_Tax_Amount) =
726 ( SELECT jcm.tax_amount,jcm.Base_Tax_Amount,jcm.Func_Tax_Amount
727 FROM JAI_CMN_MATCH_TAXES jcm
728 WHERE jcm.Ref_Line_Id = pn_ref_line_id
729 AND nvl(jcm.Subinventory,'###') = nvl(pv_subinventory,'###')
730 AND jcm.receipt_id = pn_receipt_id
731 AND jcm.Tax_Line_No = jos.Tax_Line_No
732 )
733 WHERE jos.line_id = pn_so_line_id
734 AND exists(
735 SELECT jcm.tax_amount,jcm.Base_Tax_Amount,jcm.Func_Tax_Amount
736 FROM JAI_CMN_MATCH_TAXES jcm
737 WHERE jcm.Ref_Line_Id = pn_ref_line_id
738 AND nvl(jcm.Subinventory,'###') = nvl(pv_subinventory,'###')
739 AND jcm.receipt_id = pn_receipt_id
740 AND jcm.Tax_Line_No = jos.Tax_Line_No
741 );
742
743 -- open cursor
744 OPEN c_tax_amount;
745 FETCH c_tax_amount INTO ln_exclusive_tax;
746 CLOSE c_tax_amount;
747
748 -- update exclusive tax by line id
749 Update JAI_OM_OE_SO_LINES
750 Set tax_amount = ln_exclusive_tax
751 Where line_id = pn_so_line_id;
752
753 ELSIF ln_count > 1 THEN
754 --'SO Line - Delivery Detail id is not 1:1'
755 FND_MESSAGE.SET_NAME('JA','JAI_MATCH_OCCURED');
756 END IF;
757 EXCEPTION
758 WHEN OTHERS THEN
759 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
760 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
761 app_exception.raise_exception;
762
763 END process_trading_tax_amount;
764
765 end jai_credit_check_pkg;
[Home] [Help]