[Home] [Help]
PACKAGE BODY: APPS.JAI_OM_TAX_PROCESSING_PKG
Source
1 PACKAGE BODY JAI_OM_TAX_PROCESSING_PKG AS
2 /* $Header: jai_om_tax_p.plb 120.0.12020000.2 2013/03/19 00:30:35 vkaranam noship $ */
3 --+============================================================================================================+
4 --| Fixed History |
5 --| 16/Oct/2012 Zhiwei.Xin Modified for bug 14669425 |
6 --| Fixed Details: |
7 --| Modifed cursor c_get_quantity for fetching shipped line quantity. |
8 --+============================================================================================================*/
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_OM_TAX_PROCESSING_PKG';
11 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
12 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
13 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
14 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
15 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
16 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
17 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
18 G_MODULE_NAME CONSTANT VARCHAR2(60) := 'JA.PLSQL.JAI_OM_TAX_PROCESSING_PKG.';
19
20 /*
21 REM +======================================================================+
22 REM NAME ORDER_LINE_VALIDATION
23 REM
24 REM DESCRIPTION Called from jai_oe_ola_trigger_pkg.ariu_t2 and
25 REM jai_om_tax_processing_pkg.populate_tax
26 REM
27 REM NOTES Come from JAI_OM_WDD_TRIGGER_PKG.ARU_T2
28 REM
29 REM +======================================================================+
30 */
31 PROCEDURE ORDER_LINE_VALIDATION (p_rec_old t_rec%type,
32 p_rec_new t_rec%type,
33 p_action varchar2,
34 px_return_code out nocopy varchar2,
35 px_return_message out nocopy varchar2) IS
36
37 v_api_name CONSTANT VARCHAR2(30) := 'ORDER_LINE_VALIDATION';
38
39 v_inventory_item_id NUMBER;
40 v_organization_id NUMBER;
41 v_subinventory VARCHAR2(10);
42 v_source_header_type_id NUMBER;
43 v_trading_flag VARCHAR2(1);
44 v_bonded JAI_INV_SUBINV_DTLS.bonded%TYPE;
45 lv_allow_shipment_wo_excise VARCHAR2(1);
46 v_trad_register_code VARCHAR2(30);
47 v_location_id NUMBER;
48 v_exe_flag VARCHAR2(150);
49 v_mod_flag VARCHAR2(150);
50 v_container_item_flag mtl_system_items.container_item_flag%type;
51 lv_inventory_item_flag mtl_system_items.inventory_item_flag%type;
52 ln_ship_from_location_id WSH_DELIVERY_DETAILS.ship_from_location_id%type;
53
54 /* variables used in debug package */
55 lv_object_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE ;
56 lv_member_name jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
57 lv_context jai_cmn_debug_contexts.LOG_CONTEXT%TYPE;
58 ln_reg_id NUMBER ;
59 le_error EXCEPTION ;
60
61 CURSOR Location_Cursor
62 IS
63 SELECT
64 NVL(Location_id,0),
65 trading,
66 NVL(bonded,'N') bonded
67 FROM
68 JAI_INV_SUBINV_DTLS
69 WHERE
70 Sub_Inventory_Name = v_subinventory AND
71 organization_id = v_organization_id;
72
73 cursor c_orgn_Null_site_info is
74 select exc_shpt_wo_exc_tax_flag
75 from JAI_CMN_INVENTORY_ORGS
76 where organization_id = p_rec_new.ship_from_org_id
77 and location_id = 0;
78
79 CURSOR Trading_register_code_cur(p_organization_id NUMBER ,
80 p_location_id NUMBER ,
81 p_order_type_id NUMBER)
82 IS
83 SELECT
84 A.register_code
85 FROM
86 JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
87 WHERE
88 a.organization_id = p_organization_id AND
89 a.location_id = p_location_id AND
90 a.register_id = b.register_id AND
91 b.order_flag = 'Y' AND
92 b.order_type_id = p_order_type_id;
93
94 CURSOR get_item_attributes
95 IS
96 SELECT
97 excise_flag ,
98 modvat_flag ,
99 nvl(container_item_flag,'N'),
100 nvl(inventory_item_flag,'N')
101 FROM
102 mtl_system_items msi,
103 JAI_INV_ITM_SETUPS jmsi
104 WHERE msi.organization_id = jmsi.organization_id
105 AND msi.inventory_item_id = jmsi.inventory_item_id
106 AND jmsi.inventory_item_id = v_Inventory_Item_Id
107 AND jmsi.organization_id = v_organization_id;
108
109 -- The following cursor gets the tax amount for the line_id from JAI_OM_OE_SO_LINES table
110 CURSOR c_ja_in_so_lines_tax_amt
111 IS
112 SELECT
113 tax_amount
114 FROM
115 JAI_OM_OE_SO_LINES
116 WHERE
117 line_id = p_rec_new.line_id AND
118 header_id = p_rec_new.header_id;
119
120 -- The following cursor gets the sum of tax amount for the line_id from JAI_OM_OE_SO_TAXES table
121 CURSOR c_ja_in_so_tax_lines_tax_amt
122 IS
123 SELECT
124 nvl(sum(so_tax.tax_amount),0)
125 FROM
126 JAI_OM_OE_SO_TAXES so_tax
127 , jai_cmn_taxes_all tax
128 WHERE
129 line_id = p_rec_new.line_id AND
130 header_id = p_rec_new.header_id
131 AND so_tax.tax_id = tax.tax_id
132 AND NVL(tax.inclusive_tax_flag,'N') = 'N' ;
133
134 v_line_tax_amount Number;
135 v_sum_tax_amount Number;
136
137 CURSOR c_chk_exc_exmpt_rule
138 IS
139 SELECT
140 a.excise_exempt_type ,
141 a.line_number ,
142 a.shipment_line_number ,
143 quantity
144 FROM
145 JAI_OM_OE_SO_LINES a
146 WHERE
147 a.line_id = p_rec_new.line_id AND
148 a.header_id = p_rec_new.header_id ;
149
150 lv_excise_exempt_type JAI_OM_OE_SO_LINES.EXCISE_EXEMPT_TYPE%TYPE ;
151 ln_line_number JAI_OM_OE_SO_LINES.LINE_NUMBER%TYPE ;
152 ln_shipment_line_number JAI_OM_OE_SO_LINES.SHIPMENT_LINE_NUMBER%TYPE ;
153 lv_ret_flag VARCHAR2(10) ;
154 lv_error_msg VARCHAR2(1996) ;
155 v_quantity number;
156 ln_vat_cnt NUMBER DEFAULT 0 ;
157 ln_exc_cnt NUMBER DEFAULT 0 ;
158 ln_cnt_org_loc_setup NUMBER DEFAULT 0 ;
159 lv_applicable JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE;
160 lv_process_flag VARCHAR2 (2);
161 lv_process_message VARCHAR2 (1000);
162
163 CURSOR cur_chk_vat_exists (cp_line_id JAI_OM_OE_SO_TAXES.LINE_ID%TYPE,
164 cp_header_id JAI_OM_OE_SO_TAXES.HEADER_ID%TYPE)
165 IS
166 SELECT 1
167 FROM JAI_OM_OE_SO_TAXES jstl ,
168 JAI_CMN_TAXES_ALL jtc ,
169 jai_regime_tax_types_v tax_types
170 WHERE jstl.line_id = cp_line_id
171 AND jstl.header_id = cp_header_id
172 AND jtc.tax_id = jstl.tax_id
173 AND jtc.tax_type = tax_types.tax_type
174 AND tax_types.regime_code = jai_constants.vat_regime;
175
176 CURSOR cur_chk_excise_exists (cp_line_id JAI_OM_OE_SO_TAXES.LINE_ID%TYPE,
177 cp_header_id JAI_OM_OE_SO_TAXES.HEADER_ID%TYPE
178 )
179 IS
180 SELECT 1
181 FROM JAI_OM_OE_SO_TAXES jstl ,
182 JAI_CMN_TAXES_ALL jtc
183 WHERE jstl.line_id = cp_line_id
184 AND jstl.header_id = cp_header_id
185 AND jtc.tax_id = jstl.tax_id
186 AND jtc.tax_type in ( jai_constants.tax_type_excise,
187 jai_constants.tax_type_exc_additional,
188 jai_constants.tax_type_exc_other,
189 jai_constants.tax_type_exc_edu_cess,
190 jai_constants.tax_type_sh_exc_edu_cess);
191
192 CURSOR cur_chk_org_loc_setup (cp_organization_id JAI_OM_WSH_LINES_ALL.LOCATION_ID%TYPE,
193 cp_location_id JAI_OM_WSH_LINES_ALL.ORGANIZATION_ID%TYPE
194 )
195 IS
196 SELECT 1
197 FROM jai_rgm_parties rgmpt,
198 JAI_RGM_DEFINITIONS rgms
199 WHERE rgmpt.regime_id = rgms.regime_id
200 AND rgmpt.location_id = cp_location_id
201 AND rgmpt.organization_id = cp_organization_id
202 AND rgms.regime_code = jai_constants.vat_regime;
203
204 tab_ooh OE_ORDER_HEADERS_ALL%ROWTYPE ;
205 CURSOR cur_get_org_hdr (cp_header_id OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE)
206 IS
207 SELECT
208 *
209 FROM
210 oe_order_headers_all
211 WHERE
212 header_id = cp_header_id ;
213
214 CURSOR c_ato_line_id IS
215 SELECT
216 oel.ato_line_id
217 FROM
218 oe_order_headers_all oeh,
219 oe_order_lines_all oel
220 WHERE
221 oeh.header_id = p_rec_new.header_id
222 AND oeh.header_id = oel.header_id
223 AND item_type_code = 'CONFIG' ;
224
225 CURSOR c_model_item_id(cp_ato_line_id oe_order_lines_all.line_id%TYPE) IS
226 SELECT
227 oel.inventory_item_id
228 FROM
229 oe_order_lines_all oel
230 WHERE
231 oel.line_id = cp_ato_line_id
232 AND item_type_code = 'MODEL' ;
233
234 ln_ato_line_id NUMBER ;
235 ln_model_item_id NUMBER ;
236
237 BEGIN
238
239 px_return_code := jai_constants.successful ;
240
241 v_inventory_item_id :=p_rec_new.inventory_item_id ;
242 v_organization_id :=p_rec_new.ship_from_org_id ;
243 v_subinventory :=p_rec_new.subinventory ;
244 lv_object_name :='TRIGGER.ARAA.AFTER.JA_IN_WSH_DLRY_AU_REL_STAT_TRG' ;
245 v_line_tax_amount :=0;
246 v_sum_tax_amount :=0;
247
248 --Skip 'RETURN' type, just process 'RETURN' cycle.
249 IF NVL(p_rec_new.line_category_code, 'RETURN') <> 'ORDER' THEN
250 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.STRING(G_LEVEL_STATEMENT,
252 G_MODULE_NAME || v_api_name,
253 ' This procedure just process type is ORDER, skip it for line_id' || p_rec_new.line_id);
254 END IF;
255 px_return_code := jai_constants.successful ;
256 px_return_message := 'This procedure just process type is ORDER, skip it for line_id' || p_rec_new.line_id ;
257 return;
258 END IF;
259
260 OPEN cur_get_org_hdr (cp_header_id => p_rec_new.header_id);
261 FETCH cur_get_org_hdr INTO tab_ooh ;
262 CLOSE cur_get_org_hdr ;
263
264 v_source_header_type_id := tab_ooh.order_type_id;
265 ln_ship_from_location_id := WSH_UTIL_CORE.Org_To_Location(p_rec_new.ship_from_org_id, TRUE);
266
267 OPEN c_ja_in_so_lines_tax_amt;
268 FETCH c_ja_in_so_lines_tax_amt INTO v_line_tax_amount;
269 CLOSE c_ja_in_so_lines_tax_amt;
270
271 OPEN c_ja_in_so_tax_lines_tax_amt;
272 FETCH c_ja_in_so_tax_lines_tax_amt INTO v_sum_tax_amount;
273 CLOSE c_ja_in_so_tax_lines_tax_amt;
274
275 IF NVL(v_line_tax_amount,0) <> NVL(v_sum_tax_amount,0) THEN
276 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
277 FND_LOG.STRING(G_LEVEL_STATEMENT,
278 G_MODULE_NAME || v_api_name,
279 ' Taxes are not matching in JAI_OM_OE_SO_LINES and JA_IN_SO_TAX_LINE FOR LINE_ID ' || p_rec_new.line_id);
280 END IF;
281 px_return_code := jai_constants.expected_error ;
282 px_return_message := 'Taxes are not matching in JAI_OM_OE_SO_LINES and JA_IN_SO_TAX_LINE FOR LINE_ID ' || p_rec_new.line_id ;
283 return ;
284 END IF;
285
286 OPEN c_chk_exc_exmpt_rule;
287 FETCH c_chk_exc_exmpt_rule INTO lv_excise_exempt_type,ln_line_number,ln_shipment_line_number,v_quantity;
288 CLOSE c_chk_exc_exmpt_rule ;
289
290 lv_ret_flag := jai_om_utils_pkg.validate_excise_exemption (
291 p_line_id => p_rec_new.line_id,
292 p_excise_exempt_type => lv_excise_exempt_type,
293 p_line_number => ln_line_number,
294 p_shipment_line_number => ln_shipment_line_number,
295 p_error_msg => lv_error_msg
296 ) ;
297 IF nvl(lv_ret_flag,'S') = 'EE' THEN
298 px_return_code := jai_constants.expected_error ;
299 px_return_message := lv_error_msg;
300 return ;
301 ELSIF nvl(lv_ret_flag,'S') = 'UE' THEN
302 px_return_code := jai_constants.expected_error ;
303 px_return_message := lv_error_msg;
304 return ;
305 END IF ;
306
307 OPEN location_cursor;
308 FETCH location_cursor INTO v_location_id, v_trading_flag , v_bonded;
309 CLOSE location_cursor;
310
311 OPEN trading_register_code_cur( v_organization_id ,
312 v_location_id ,
313 v_source_header_type_id
314 );
315 FETCH trading_register_code_cur INTO v_trad_register_code;
316 CLOSE trading_register_code_cur;
317
318 OPEN get_item_attributes;
319 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag,v_container_item_flag,lv_inventory_item_flag;
320 CLOSE get_item_attributes;
321
322
323 if nvl(p_rec_new.flow_status_code,'ENTERED') = 'ENTERED' then
324 OPEN cur_chk_excise_exists(cp_line_id => p_rec_new.line_id,
325 cp_header_id => p_rec_new.header_id
326 );
327 FETCH cur_chk_excise_exists INTO ln_exc_cnt;
328 CLOSE cur_chk_excise_exists ;
329
330 IF nvl (ln_exc_cnt,0) > 0 AND nvl(v_exe_flag,'N') = 'N' THEN
331 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
332 FND_LOG.STRING(G_LEVEL_STATEMENT,
333 G_MODULE_NAME || v_api_name,
334 'An item which is not Excisable has Excise Taxes attached.' ||
335 'Please correct the item attribute or remove the Excise type of taxes');
336 END IF;
337 px_return_code := jai_constants.expected_error ;
338 px_return_message := 'An item which is not Excisable has Excise Taxes attached.' ||
339 'Please correct the item attribute or remove the Excise type of taxes' ;
340 return ;
341 END IF;
342
343 IF (
344 (
345 NVL(v_bonded,'N') = 'Y' OR
346 NVL(v_trading_flag,'N') = 'Y'
347 ) AND
348 NVL(v_exe_flag,'N') = 'Y' AND
349 v_trad_register_code IN (
350 '23D_DOMESTIC_EXCISE' ,
351 '23D_EXPORT_EXCISE' ,
352 'DOMESTIC_EXCISE' ,
353 'EXPORT_EXCISE' ,
354 'BOND_REG' ,
355 '23D_EXPORT_WITHOUT_EXCISE'
356 ) AND
357 nvl(ln_exc_cnt,0) = 0
358 )
359 THEN
360
361 OPEN c_orgn_null_site_info;
362 FETCH c_orgn_null_site_info INTO lv_allow_shipment_wo_excise;
363 CLOSE c_orgn_null_site_info;
364
365 IF NVL(lv_allow_shipment_wo_excise,'N') = 'Y' THEN
366 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING(G_LEVEL_STATEMENT,
368 G_MODULE_NAME || v_api_name,
369 ' Delivery can not be ship confirmed as Excisable Item in the shipment does not have Excise taxes');
370 END IF;
371 raise_application_error(-20412, 'Delivery can not be ship confirmed as Excisable Item in the shipment does not have Excise taxes' );
372 END IF;
373 END IF;
374
375 IF v_location_id IS NULL and v_container_item_flag ='N' and lv_inventory_item_flag = 'Y' then
376 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
377 FND_LOG.STRING(G_LEVEL_STATEMENT,
378 G_MODULE_NAME || v_api_name,
379 ' Mandatory India Localization Sub-inventory Setup not done for this Location from where shipment is made');
380 END IF;
381 px_return_code := jai_constants.expected_error ;
382 px_return_message := 'Mandatory India Localization Sub-inventory Setup not done for this Location from where shipment is made' ;
383 return ;
384 end if ;
385 end if;
386
387 if v_location_id is null then
388 v_location_id := ln_ship_from_location_id;
389 end if;
390
391 IF nvl(p_rec_new.flow_status_code,'ENTERED') = 'ENTERED' THEN
392 OPEN cur_chk_vat_exists ( cp_line_id => p_rec_new.line_id,
393 cp_header_id => p_rec_new.header_id
394 );
395 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
396 CLOSE cur_chk_vat_exists ;
397
398 IF nvl (ln_vat_cnt,0) > 0 THEN
399
400 OPEN cur_chk_org_loc_setup (cp_organization_id => v_organization_id ,
401 cp_location_id => v_location_id
402 );
403 FETCH cur_chk_org_loc_setup INTO ln_cnt_org_loc_setup;
404 CLOSE cur_chk_org_loc_setup ;
405
406 IF nvl(ln_cnt_org_loc_setup,0) = 0 THEN
407 /*
408 || For vat regime organization-location specific setup does not exist in
409 || jai_rgm_parties (Regime Organization Registration)
410 */
411 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
412 FND_LOG.STRING(G_LEVEL_STATEMENT,
413 G_MODULE_NAME || v_api_name,
414 ' Organization-Location setup does not exist at regime level');
415 END IF;
416 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
417 ,EXCEPTION_CODE => NULL
418 ,EXCEPTION_TEXT => 'Organization-Location setup does not exist at regime level'
419 );
420 END IF;
421 ln_ato_line_id := NULL ;
422 ln_model_item_id := NULL ;
423
424 OPEN c_ato_line_id ;
425 FETCH c_ato_line_id INTO ln_ato_line_id ;
426 CLOSE c_ato_line_id ;
427
428 IF ln_ato_line_id IS NOT NULL THEN
429 OPEN c_model_item_id(ln_ato_line_id) ;
430 FETCH c_model_item_id INTO ln_model_item_id ;
431 CLOSE c_model_item_id ;
432
433 jai_inv_items_pkg.jai_get_attrib ( p_regime_code => jai_constants.vat_regime
434 , p_organization_id => p_rec_new.ship_from_org_id
435 , p_inventory_item_id => ln_model_item_id
436 , p_attribute_code => jai_constants.rgm_attr_item_applicable
437 , p_attribute_value => lv_applicable
438 , p_process_flag => lv_process_flag
439 , p_process_msg => lv_process_message
440 );
441 ELSE
442
443 jai_inv_items_pkg.jai_get_attrib ( p_regime_code => jai_constants.vat_regime
444 , p_organization_id => p_rec_new.ship_from_org_id
445 , p_inventory_item_id => p_rec_new.inventory_item_id
446 , p_attribute_code => jai_constants.rgm_attr_item_applicable
447 , p_attribute_value => lv_applicable
448 , p_process_flag => lv_process_flag
449 , p_process_msg => lv_process_message
450 );
451 END IF;
452 IF lv_process_flag = jai_constants.successful
453 AND nvl(lv_applicable,'N') = 'N' THEN
454 /*
455 ||item is not vatable
456 */
457 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
458 FND_LOG.STRING(G_LEVEL_STATEMENT,
459 G_MODULE_NAME || v_api_name,
460 ' An item which is not Vatable has VAT Taxes attached.Please correct the item attribute or remove the VAT type of taxes');
461 END IF;
462 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
463 ,EXCEPTION_CODE => NULL
464 ,EXCEPTION_TEXT => 'An item which is not Vatable has VAT Taxes attached.Please correct the item attribute or remove the VAT type of taxes'
465 );
466 ELSIF lv_process_flag <> jai_constants.successful THEN
467 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
468 FND_LOG.STRING(G_LEVEL_STATEMENT,
469 G_MODULE_NAME || v_api_name,
470 lv_process_message);
471 END IF;
472 app_exception.raise_exception( EXCEPTION_TYPE => 'APP'
473 ,EXCEPTION_CODE => NULL
474 ,EXCEPTION_TEXT => substr (lv_process_message,1,999)
475 );
476
477
478 END IF;
479 END IF;
480 END IF;
481
482 jai_ar_tcs_rep_pkg.process_transactions ( p_ooh => tab_ooh ,
483 p_event => jai_constants.wsh_ship_confirm ,
484 p_process_flag => lv_process_flag ,
485 p_process_message => lv_process_message
486 );
487
488 IF lv_process_flag = jai_constants.expected_error OR
489 lv_process_flag = jai_constants.unexpected_error
490 THEN
491 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
492 FND_LOG.STRING(G_LEVEL_STATEMENT,
493 G_MODULE_NAME || v_api_name,
494 lv_process_message);
495 END IF;
496 raise le_error;
497 END IF;
498
499 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
500
501
502 EXCEPTION
503 WHEN le_error THEN
504 IF lv_process_flag = jai_constants.unexpected_error THEN
505 lv_process_message := substr (lv_process_message || ' Object = JAI_OM_TAX_PROCESSING_PKG.ORDER_LINE_VALIDATION ', 1,1999) ;
506 END IF;
507
508
509 fnd_message.set_name (application => 'JA',
510 name => 'JAI_GENERIC_MSG'
511 );
512
513 fnd_message.set_token ( token => 'MSG_TEXT',
514 value => lv_process_message
515 );
516
517
518 app_exception.raise_exception;
519
520 WHEN others THEN
521 fnd_message.set_name ( application => 'JA',
522 name => 'JAI_GENERIC_MSG'
523 );
524
525 fnd_message.set_token ( token => 'MSG_TEXT',
526 value => 'Exception Occured in ' || ' Object = JAI_OM_TAX_PROCESSING_PKG.ORDER_LINE_VALIDATION'||fnd_global.local_chr(10)||SQLERRM
527 );
528
529 app_exception.raise_exception;
530
531 END ORDER_LINE_VALIDATION ;
532
533 /*
534 REM +======================================================================+
535 REM NAME COPY_ORDER_TO_ORDER
536 REM
537 REM DESCRIPTION COPY ORDER LINES FROM ORIGINAL ORDER
538 REM
539 REM NOTES Call from procdure COPY_SOURCE_ORDER
540 REM
541 REM +======================================================================+
542 */
543 PROCEDURE COPY_ORDER_TO_ORDER(pr_copy get_copy_order_line%ROWTYPE,
544 pr_order_line t_rec%type,
545 pr_header_info get_header_info%ROWTYPE) IS
546
547 v_api_name CONSTANT VARCHAR2(30) := 'COPY_ORDER_TO_ORDER';
548
549 CURSOR get_so_tax_lines_count_cur(p_header_id NUMBER, p_line_id NUMBER) IS
550 SELECT COUNT(1)
551 FROM JAI_OM_OE_SO_TAXES
552 WHERE header_id = p_header_id
553 AND line_id = p_line_id;
554
555 CURSOR so_tax_lines_cur(p_header_id NUMBER, p_line_id NUMBER) IS
556 SELECT tax_line_no,
557 tax_id,
558 tax_rate,
559 qty_rate,
560 uom,
561 precedence_1,
562 precedence_2,
563 precedence_3,
564 precedence_4,
565 precedence_5,
566 precedence_6,
567 precedence_7,
568 precedence_8,
569 precedence_9,
570 precedence_10,
571 tax_amount,
572 base_tax_amount,
573 func_tax_amount,
574 tax_category_id
575 FROM JAI_OM_OE_SO_TAXES
576 WHERE header_id = p_header_id
577 AND line_id = p_line_id;
578
579 c_source_line_id NUMBER;
580 v_so_tax_lines_count NUMBER;
581 v_tax_line_count NUMBER;
582 r_get_copy_order_line get_copy_order_line%ROWTYPE;
583 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
584 v_set_of_books_id NUMBER;
585 v_converted_rate NUMBER;
586
587 BEGIN
588
589 SELECT COUNT(*)
590 INTO c_source_line_id
591 FROM JAI_OM_OE_SO_LINES
592 WHERE LINE_ID = pr_order_line.line_id;
593 IF c_source_line_id = 0 THEN
594
595 INSERT INTO JAI_OM_OE_SO_LINES
596 (line_number,
597 line_id,
598 header_id,
599 inventory_item_id,
600 unit_code,
601 quantity,
602 tax_category_id,
603 ato_flag,
604 selling_price,
605 line_amount,
606 assessable_value,
607 tax_amount,
608 line_tot_amount,
609 shipment_line_number,
610 excise_exempt_type,
611 excise_exempt_refno,
612 excise_exempt_date,
613 vat_exemption_flag,
614 vat_exemption_type,
615 vat_exemption_date,
616 vat_exemption_refno,
617 vat_assessable_value,
618 vat_reversal_price,
619 creation_date,
620 created_by,
621 last_update_date,
622 last_updated_by,
623 last_update_login,
624 service_type_code,
625 ship_to_org_id)
626 VALUES
627 (pr_order_line.line_number,
628 pr_order_line.line_id,
629 pr_order_line.header_id,
630 pr_order_line.inventory_item_id,
631 pr_copy.unit_code,
632 pr_order_line.ordered_quantity,
633 pr_copy.tax_category_id,
634 'Y',
635 pr_order_line.unit_selling_price,
636 nvl(pr_order_line.unit_selling_price * pr_order_line.ordered_quantity, 0),
637 pr_copy.assessable_value,
638 pr_copy.tax_amount,
639 pr_copy.line_tot_amount,
640 pr_order_line.shipment_number,
641 pr_copy.excise_exempt_type,
642 pr_copy.excise_exempt_refno,
643 pr_copy.excise_exempt_date,
644 pr_copy.vat_exemption_flag,
645 pr_copy.vat_exemption_type,
646 pr_copy.vat_exemption_date,
647 pr_copy.vat_exemption_refno,
648 pr_copy.vat_assessable_value,
649 nvl(pr_copy.vat_reversal_price, 0) * NVL(pr_order_line.ordered_quantity, 0),
650 pr_order_line.creation_date,
651 pr_order_line.created_by,
652 pr_order_line.last_update_date,
653 pr_order_line.last_updated_by,
654 pr_order_line.last_update_login,
655 pr_copy.service_type_code,
656 pr_order_line.ship_to_org_id);
657 END IF;
658
659 /********************* Order TO Order tax lines computation **************************/
660 OPEN Get_So_Tax_Lines_Count_Cur(pr_order_line.SOURCE_DOCUMENT_ID,
661 pr_order_line.SOURCE_DOCUMENT_LINE_ID);
662 FETCH Get_So_Tax_Lines_Count_Cur
663 INTO v_so_tax_lines_count;
664 CLOSE Get_So_Tax_Lines_Count_Cur;
665
666 IF NVL(v_so_tax_lines_count, 0) > 0 THEN
667 FOR Rec IN So_Tax_Lines_Cur(pr_order_line.SOURCE_DOCUMENT_ID,
668 pr_order_line.SOURCE_DOCUMENT_LINE_ID) LOOP
669 SELECT COUNT(1)
670 INTO v_tax_line_count
671 FROM JAI_OM_OE_SO_TAXES
672 WHERE line_id = pr_order_line.line_id
673 AND tax_id = rec.tax_id;
674
675 IF v_tax_line_count = 0 THEN
676
677 /*
678 || call to the ja_in_calc_Taxes_ato would do the trick thru re-calculating the taxes.
679 */
680
681 INSERT INTO JAI_OM_OE_SO_TAXES
682 (header_id,
683 line_id,
684 tax_line_no,
685 tax_id,
686 tax_rate,
687 qty_rate,
688 uom,
689 precedence_1,
690 precedence_2,
691 precedence_3,
692 precedence_4,
693 precedence_5,
694 precedence_6,
695 precedence_7,
696 precedence_8,
697 precedence_9,
698 precedence_10,
699 tax_amount,
700 base_tax_amount,
701 func_tax_amount,
702 creation_date,
703 created_by,
704 last_update_date,
705 last_updated_by,
706 last_update_login,
707 tax_category_id)
708 VALUES
709 (pr_order_line.header_id,
710 pr_order_line.line_id,
711 rec.tax_line_no,
712 rec.tax_id,
713 rec.tax_rate,
714 rec.qty_rate,
715 rec.uom,
716 rec.precedence_1,
717 rec.precedence_2,
718 rec.precedence_3,
719 rec.precedence_4,
720 rec.precedence_5,
721 rec.precedence_6,
722 rec.precedence_7,
723 rec.precedence_8,
724 rec.precedence_9,
725 rec.precedence_10,
726 rec.tax_amount,
727 rec.base_tax_amount,
728 rec.func_tax_amount,
729 pr_order_line.creation_date,
730 pr_order_line.created_by,
731 pr_order_line.last_update_date,
732 pr_order_line.last_updated_by,
733 pr_order_line.last_update_login,
734 rec.tax_category_id);
735
736 END IF;
737 END LOOP;
738
739 -- get newly copied order line
740 OPEN get_copy_order_line(pr_order_line.header_id, pr_order_line.line_id);
741 FETCH get_copy_order_line
742 INTO r_get_copy_order_line;
743 CLOSE get_copy_order_line;
744
745 /*
746 || The variable r_get_copy_order_line has the details of the current line from JAI_OM_OE_SO_LINES table
747 */
748 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => pr_order_line.ORG_ID);
749 v_set_of_books_id := l_func_curr_det.ledger_id;
750 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_set_of_books_id,
751 pr_header_info.currency_code,
752 pr_header_info.conv_date,
753 pr_header_info.conv_type_code,
754 pr_header_info.conv_rate);
755
756 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
757 FND_LOG.STRING(G_LEVEL_STATEMENT,
758 G_MODULE_NAME || v_api_name,
759 ' jai_om_tax_pkg.calculate_ato_taxes :' ||
760 TO_CHAR(pr_order_line.header_id) || ' ~ ' ||
761 TO_CHAR(pr_order_line.line_id));
762 END IF;
763 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE',
764 NULL,
765 pr_order_line.header_id,
766 pr_order_line.line_id,
767 r_get_copy_order_line.assessable_value *
768 (pr_order_line.ordered_quantity),
769 r_get_copy_order_line.line_amount,
770 v_converted_rate,
771 pr_order_line.inventory_item_id,
772 pr_order_line.ordered_quantity,
773 pr_order_line.ordered_quantity,
774 pr_order_line.pricing_quantity_uom,
775 NULL,
776 NULL,
777 NULL,
778 NULL,
779 pr_order_line.last_update_date,
780 pr_order_line.last_updated_by,
781 pr_order_line.last_update_login,
782 r_get_copy_order_line.vat_assessable_value);
783
784 update JAI_OM_OE_SO_LINES
785 set tax_amount = NVL(r_get_copy_order_line.line_amount,
786 0),
787 line_tot_amount = line_amount + NVL(r_get_copy_order_line.line_amount,
788 0),
789 vat_assessable_Value = r_get_copy_order_line.vat_assessable_value
790 where header_id = pr_order_line.header_id
791 and line_id = pr_order_line.line_id;
792
793 END IF;
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 RAISE;
798 END COPY_ORDER_TO_ORDER;
799
800 /*
801 REM +======================================================================+
802 REM NAME COPY_RETURN_TO_ORDER
803 REM
804 REM DESCRIPTION COPY ORDER LINES FROM ORIGINAL RETURN ORDER
805 REM
806 REM NOTES Call from procdure COPY_SOURCE_ORDER
807 REM
808 REM +======================================================================+
809 */
810 PROCEDURE COPY_RETURN_TO_ORDER(pr_order_line t_rec%type,
811 pr_header_info get_header_info%rowtype) IS
812 /*
813 Get the details from the JAI_OM_OE_RMA_LINES table.
814 */
815 CURSOR cur_get_rma_entry_lines(p_header_id JAI_OM_OE_RMA_LINES.RMA_HEADER_ID%TYPE,
816 p_Line_Id JAI_OM_OE_RMA_LINES.RMA_LINE_ID%TYPE) IS
817 SELECT *
818 FROM JAI_OM_OE_RMA_LINES
819 WHERE rma_header_id = p_header_id
820 AND rma_line_id = p_Line_Id;
821
822 CURSOR cur_source_line_id_exists(p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE,
823 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE) IS
824 SELECT 'X'
825 FROM JAI_OM_OE_SO_LINES
826 WHERE line_id = p_line_id
827 AND header_id = p_header_id;
828
829 /*
830 Get the rma trax lines detail from the table JAI_OM_OE_RMA_TAXES
831 */
832 CURSOR cur_get_JAI_OM_OE_RMA_TAXES(p_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE) IS
833 SELECT tax_line_no,
834 tax_id,
835 tax_rate,
836 qty_rate,
837 uom,
838 precedence_1,
839 precedence_2,
840 precedence_3,
841 precedence_4,
842 precedence_5,
843 precedence_6,
844 precedence_7,
845 precedence_8,
846 precedence_9,
847 precedence_10,
848 tax_amount,
849 base_tax_amount,
850 func_tax_amount
851 FROM JAI_OM_OE_RMA_TAXES
852 WHERE rma_line_id = p_line_id;
853
854 /*
855 code to check whether a record exists in the table JAI_OM_OE_SO_TAXES for a given line_id and tax_id.
856 */
857 CURSOR cur_chk_tax_lines_exists(p_line_id1 OE_ORDER_LINES_ALL.LINE_ID%TYPE,
858 p_tax_id JAI_OM_OE_SO_TAXES.TAX_ID%TYPE) IS
859 SELECT 'X'
860 FROM JAI_OM_OE_SO_TAXES
861 WHERE line_id = p_line_id1
862 AND tax_id = p_tax_id;
863
864 rec_get_rma_tax_lines cur_get_JAI_OM_OE_RMA_TAXES%ROWTYPE;
865 l_tax_lines_exist VARCHAR2(10);
866 rec_cur_get_rma_entry_lines cur_get_rma_entry_lines%ROWTYPE;
867 l_exists VARCHAR2(1);
868 l_tax_exists VARCHAR2(2);
869 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
870 v_set_of_books_id NUMBER;
871 v_converted_rate NUMBER;
872 v_assessable_value NUMBER;
873 v_assessable_amount NUMBER;
874 v_line_amount NUMBER;
875 v_line_tax_amount NUMBER;
876 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
877
878 BEGIN
879 v_line_amount := (NVL(pr_order_line.ordered_quantity, 0) * NVL(pr_order_line.UNIT_SELLING_PRICE, 0));
880 /*
881 IF the source_order categoy code is Return and line category code is ORDER then
882 1. Check whether a corresponding record exists in the rma_entry_lines table.
883 IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
884 record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
885 IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
886 */
887
888 OPEN cur_get_rma_entry_lines(pr_order_line.SOURCE_DOCUMENT_ID,
889 pr_order_line.SOURCE_DOCUMENT_LINE_ID);
890 FETCH cur_get_rma_entry_lines
891 INTO rec_cur_get_rma_entry_lines;
892
893 IF cur_get_rma_entry_lines%FOUND THEN
894 OPEN cur_source_line_id_exists(p_line_id => pr_order_line.line_id,
895 p_header_id => pr_order_line.header_id);
896
897 FETCH cur_source_line_id_exists
898 INTO l_exists;
899 IF cur_source_line_id_exists%NOTFOUND THEN
900
901 INSERT INTO JAI_OM_OE_SO_LINES
902 (line_number,
903 line_id,
904 header_id,
905 inventory_item_id,
906 unit_code,
907 quantity,
908 tax_category_id,
909 ato_flag,
910 selling_price,
911 line_amount,
912 assessable_value,
913 tax_amount,
914 line_tot_amount,
915 shipment_line_number,
916 creation_date,
917 created_by,
918 last_update_date,
919 last_updated_by,
920 last_update_login,
921 service_type_code,
922 ship_to_org_id)
923 VALUES
924 (pr_order_line.line_number,
925 pr_order_line.line_id,
926 pr_order_line.header_id,
927 rec_cur_get_rma_entry_lines.inventory_item_id,
928 rec_cur_get_rma_entry_lines.uom,
929 rec_cur_get_rma_entry_lines.quantity,
930 rec_cur_get_rma_entry_lines.tax_category_id,
931 'Y',
932 rec_cur_get_rma_entry_lines.selling_price,
933 v_line_amount,
934 rec_cur_get_rma_entry_lines.assessable_value,
935 rec_cur_get_rma_entry_lines.tax_amount,
936 (v_line_amount + rec_cur_get_rma_entry_lines.tax_amount),
937 pr_order_line.shipment_number,
938 pr_order_line.creation_date,
939 pr_order_line.created_by,
940 pr_order_line.last_update_date,
941 pr_order_line.last_updated_by,
942 pr_order_line.last_update_login,
943 rec_cur_get_rma_entry_lines.service_type_code,
944 pr_order_line.ship_to_org_id);
945
946 END IF;
947 CLOSE cur_source_line_id_exists;
948 END IF;
949 CLOSE cur_get_rma_entry_lines;
950
951 /********************* Return TO Order tax lines computation **************************/
952 l_tax_lines_exist := 'FALSE';
953
954 FOR rec_get_rma_tax_lines in cur_get_JAI_OM_OE_RMA_TAXES(p_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID) loop
955 l_tax_lines_exist := 'TRUE';
956 OPEN cur_chk_tax_lines_exists(p_line_id1 => pr_order_line.line_id,
957 p_tax_id => rec_get_rma_tax_lines.tax_id);
958 FETCH cur_chk_tax_lines_exists
959 INTO l_tax_exists;
960 IF cur_chk_tax_lines_exists%NOTFOUND THEN
961
962 INSERT INTO JAI_OM_OE_SO_TAXES
963 (header_id,
964 line_id,
965 tax_line_no,
966 tax_id,
967 tax_rate,
968 qty_rate,
969 uom,
970 precedence_1,
971 precedence_2,
972 precedence_3,
973 precedence_4,
974 precedence_5,
975 precedence_6,
976 precedence_7,
977 precedence_8,
978 precedence_9,
979 precedence_10,
980 tax_amount,
981 base_tax_amount,
982 func_tax_amount,
983 creation_date,
984 created_by,
985 last_update_date,
986 last_updated_by,
987 last_update_login)
988 VALUES
989 (pr_order_line.header_id,
990 pr_order_line.line_id,
991 rec_get_rma_tax_lines.tax_line_no,
992 rec_get_rma_tax_lines.tax_id,
993 rec_get_rma_tax_lines.tax_rate,
994 rec_get_rma_tax_lines.qty_rate,
995 rec_get_rma_tax_lines.uom,
996 rec_get_rma_tax_lines.precedence_1,
997 rec_get_rma_tax_lines.precedence_2,
998 rec_get_rma_tax_lines.precedence_3,
999 rec_get_rma_tax_lines.precedence_4,
1000 rec_get_rma_tax_lines.precedence_5,
1001 rec_get_rma_tax_lines.precedence_6,
1002 rec_get_rma_tax_lines.precedence_7,
1003 rec_get_rma_tax_lines.precedence_8,
1004 rec_get_rma_tax_lines.precedence_9,
1005 rec_get_rma_tax_lines.precedence_10,
1006 rec_get_rma_tax_lines.tax_amount,
1007 rec_get_rma_tax_lines.base_tax_amount,
1008 rec_get_rma_tax_lines.func_tax_amount,
1009 pr_order_line.creation_date,
1010 pr_order_line.created_by,
1011 pr_order_line.last_update_date,
1012 pr_order_line.last_updated_by,
1013 pr_order_line.last_update_login);
1014
1015 END IF;
1016 CLOSE cur_chk_tax_lines_exists;
1017 END LOOP;
1018
1019 IF l_tax_lines_exist = 'TRUE' THEN
1020
1021 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => pr_order_line.ORG_ID);
1022 v_set_of_books_id := l_func_curr_det.ledger_id;
1023
1024 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_set_of_books_id,
1025 pr_header_info.currency_code,
1026 pr_header_info.conv_date,
1027 pr_header_info.conv_type_code,
1028 pr_header_info.conv_rate);
1029
1030 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value(p_customer_id => pr_header_info.customer_id,
1031 p_ship_to_site_use_id => NVL(pr_order_line.ship_to_ORG_id, 0),
1032 p_inventory_item_id => pr_order_line.inventory_item_id,
1033 p_uom_code => pr_order_line.ORDER_QUANTITY_UOM,
1034 p_default_price => pr_order_line.unit_selling_price,
1035 p_ass_value_date => pr_header_info.date_ordered,
1036 p_sob_id => v_set_of_books_id,
1037 p_curr_conv_code => pr_header_info.conv_type_code,
1038 p_conv_rate => pr_header_info.conv_rate
1039 );
1040
1041 v_assessable_amount := NVL(v_assessable_value, 0) * NVL(pr_order_line.ordered_quantity, 0);
1042 v_line_tax_amount := v_line_amount;
1043 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value(p_party_id => pr_header_info.customer_id,
1044 p_party_site_id => NVL(pr_order_line.ship_to_ORG_id, 0),
1045 p_inventory_item_id => pr_order_line.inventory_item_id,
1046 p_uom_code => pr_order_line.ORDER_QUANTITY_UOM,
1047 p_default_price => pr_order_line.unit_selling_price,
1048 p_ass_value_date => pr_header_info.date_ordered,
1049 p_party_type => 'C');
1050
1051 ln_vat_assessable_value := nvl(ln_vat_assessable_value, 0) * NVL(pr_order_line.ordered_quantity, 0);
1052
1053 IF v_assessable_value <> rec_cur_get_rma_entry_lines.assessable_value THEN
1054 jai_om_tax_pkg.recalculate_oe_taxes(pr_order_line.header_id,
1055 pr_order_line.line_id,
1056 v_assessable_amount,
1057 ln_vat_assessable_value,
1058 v_line_tax_amount,
1059 rec_cur_get_rma_entry_lines.inventory_item_id,
1060 rec_cur_get_rma_entry_lines.quantity,
1061 rec_cur_get_rma_entry_lines.uom,
1062 v_converted_rate,
1063 pr_order_line.last_update_date,
1064 pr_order_line.last_updated_by,
1065 pr_order_line.last_update_login);
1066
1067 UPDATE JAI_OM_OE_SO_LINES
1068 SET assessable_value = v_assessable_value,
1069 tax_amount = NVL(v_line_tax_amount, 0),
1070 line_tot_amount = v_line_amount +
1071 NVL(v_line_tax_amount, 0),
1072 last_update_date = pr_order_line.last_update_date,
1073 last_updated_by = pr_order_line.last_updated_by,
1074 last_update_login = pr_order_line.last_update_login
1075 WHERE header_id = pr_order_line.header_id
1076 AND line_id = pr_order_line.line_id;
1077
1078 END IF;
1079
1080 END IF;
1081
1082 EXCEPTION
1083 WHEN OTHERS THEN
1084 RAISE;
1085 END COPY_RETURN_TO_ORDER;
1086
1087 /*
1088 REM +======================================================================+
1089 REM NAME COPY_ORDER_TO_RETURN
1090 REM
1091 REM DESCRIPTION COPY RETURN TAXES and LINES FROM ORIGINAL ORDER
1092 REM
1093 REM NOTES Call from procdure COPY_SOURCE_ORDER
1094 REM
1095 REM +======================================================================+
1096 */
1097 PROCEDURE COPY_ORDER_TO_RETURN(pr_order_line t_rec%type,
1098 p_order_number NUMBER,
1099 px_return_code out nocopy varchar2,
1100 px_return_message out nocopy varchar2) IS
1101
1102 -- get the details from JAI_OM_WSH_LINES_ALL table
1103 CURSOR cur_get_picking_lines(p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE,
1104 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE) IS
1105 SELECT pl.inventory_item_id inventory_item_id,
1106 pl.unit_code unit_code,
1107 sum(pl.quantity) quantity,
1108 pl.tax_category_id tax_category_id,
1109 pl.selling_price selling_price,
1110 sum(pl.tax_amount) tax_amount,
1111 min(pl.delivery_detail_id) delivery_detail_id
1112 FROM JAI_OM_WSH_LINES_ALL pl
1113 WHERE pl.order_header_id = p_source_document_id
1114 AND pl.order_line_id = p_source_document_line_id
1115 GROUP BY pl.inventory_item_id,
1116 pl.unit_code,
1117 pl.selling_price,
1118 pl.tax_category_id;
1119
1120 cursor c_sales_order_cur is
1121 select quantity, service_type_code
1122 from JAI_OM_OE_SO_LINES
1123 where line_id = pr_order_line.reference_line_id;
1124
1125 CURSOR cur_rma_entry_line_exists(p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE,
1126 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE) IS
1127 SELECT 'X'
1128 FROM JAI_OM_OE_RMA_LINES
1129 WHERE rma_line_id = p_line_id
1130 AND rma_header_id = p_header_id;
1131
1132 l_exists VARCHAR2(1);
1133 rec_cur_get_picking_lines cur_get_picking_lines%ROWTYPE;
1134
1135 CURSOR cur_get_picking_tax_lines(p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE,
1136 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE) IS
1137 SELECT ptl.tax_line_no tax_line_no,
1138 ptl.tax_id tax_id,
1139 ptl.tax_rate tax_rate,
1140 ptl.qty_rate qty_rate,
1141 ptl.uom uom,
1142 ptl.precedence_1 precedence_1,
1143 ptl.precedence_2 precedence_2,
1144 ptl.precedence_3 precedence_3,
1145 ptl.precedence_4 precedence_4,
1146 ptl.precedence_5 precedence_5,
1147 ptl.precedence_6 precedence_6,
1148 ptl.precedence_7 precedence_7,
1149 ptl.precedence_8 precedence_8,
1150 ptl.precedence_9 precedence_9,
1151 ptl.precedence_10 precedence_10,
1152 jtc.tax_type tax_type,
1153 nvl(jtc.rounding_factor, 0) rounding_factor,
1154 SUM(ptl.tax_amount) tax_amount,
1155 SUM(ptl.base_tax_amount) base_tax_amount,
1156 SUM(ptl.func_tax_amount) func_tax_amount,
1157 MIN(ptl.delivery_detail_id) delivery_detail_id
1158 FROM JAI_OM_WSH_LINES_ALL pl,
1159 JAI_OM_WSH_LINE_TAXES ptl,
1160 JAI_CMN_TAXES_ALL jtc
1161 WHERE ptl.delivery_detail_id = pl.delivery_detail_id
1162 AND pl.order_header_id = p_source_document_id
1163 AND pl.order_line_id = p_source_document_line_id
1164 AND jtc.tax_id = ptl.tax_id
1165 GROUP by ptl.tax_line_no,
1166 ptl.tax_id,
1167 ptl.tax_rate,
1168 ptl.qty_rate,
1169 ptl.uom,
1170 precedence_1,
1171 precedence_2,
1172 precedence_3,
1173 precedence_4,
1174 precedence_5,
1175 precedence_6,
1176 precedence_7,
1177 precedence_8,
1178 precedence_9,
1179 precedence_10,
1180 jtc.tax_type,
1181 nvl(jtc.rounding_factor, 0);
1182
1183 CURSOR c_get_quantity(p_source_document_id JAI_OM_WSH_LINES_ALL.order_header_id%type,
1184 p_source_document_line_id JAI_OM_WSH_LINES_ALL.order_line_id%type) IS
1185 --commented out by zhiwei.xin for bug#14669425 on 16-Oct-2012 begin
1186 /*SELECT sum(quantity)
1187 FROM JAI_OM_WSH_LINES_ALL pl, JAI_OM_WSH_LINE_TAXES ptl
1188 WHERE ptl.delivery_detail_id = pl.delivery_detail_id
1189 AND pl.order_header_id = p_source_document_id
1190 AND pl.order_line_id = p_source_document_line_id;*/
1191 --commented out by zhiwei.xin for bug#14669425 on 16-Oct-2012 end.
1192 -- added by zhiwei.xin for bug#14669425 on 16-Oct-2012 begin
1193 SELECT quantity
1194 FROM JAI_OM_WSH_LINES_ALL
1195 WHERE order_header_id = p_source_document_id
1196 AND order_line_id = p_source_document_line_id;
1197 -- added by zhiwei.xin for bug#14669425 on 16-Oct-2012 end.
1198
1199 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER) IS
1200 SELECT requested_quantity_uom
1201 FROM wsh_delivery_details
1202 WHERE delivery_detail_id = p_delivery_detail_id;
1203
1204 CURSOR c_check_vat_type_tax_exists(cp_tax_type VARCHAR2) IS
1205 SELECT 1
1206 FROM jai_regime_tax_types_v
1207 WHERE regime_code = jai_constants.vat_regime
1208 AND tax_type = cp_tax_type;
1209
1210 cursor cur_get_ddetail_id(p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE,
1211 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE) is
1212 select delivery_detail_id
1213 from JAI_OM_OE_RMA_LINES
1214 where rma_header_id = p_source_document_id
1215 AND rma_line_id = p_source_document_line_id;
1216
1217 v_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type;
1218
1219 CURSOR c_get_detail_id(p_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type) IS
1220 SELECT wdd.delivery_detail_id, wnd.confirm_date
1221 FROM wsh_delivery_details wdd,
1222 wsh_delivery_assignments wda,
1223 wsh_new_deliveries wnd
1224 WHERE wdd.delivery_detail_id = p_ddetail_id
1225 AND wda.delivery_detail_id = wdd.delivery_detail_id
1226 AND wnd.delivery_id = wda.delivery_id;
1227
1228 CURSOR c_get_days_flags IS
1229 SELECT excise_return_days,
1230 sales_return_days,
1231 vat_return_days,
1232 nvl(manufacturing, 'N') manufacturing,
1233 nvl(trading, 'N') trading
1234 FROM JAI_CMN_INVENTORY_ORGS
1235 WHERE organization_id = pr_order_line.ship_from_org_id
1236 AND location_id = 0;
1237
1238 CURSOR c_ordered_date IS
1239 SELECT ordered_date
1240 FROM oe_order_headers_all
1241 WHERE header_id = pr_order_line.header_id;
1242
1243 CURSOR c_fulfilled_date IS
1244 SELECT CREATION_DATE
1245 FROM JAI_OM_WSH_LINES_ALL
1246 WHERE ORDER_LINE_ID = pr_order_line.reference_line_id
1247 AND SHIPPABLE_FLAG = 'N';
1248
1249 CURSOR c_get_ship_qty(p_source_document_id NUMBER,
1250 p_source_document_line_id NUMBER) IS
1251 SELECT SUM(wdd.shipped_quantity) qty
1252 FROM wsh_delivery_details wdd
1253 WHERE wdd.delivery_detail_id in
1254 (SELECT delivery_detail_id
1255 FROM JAI_OM_WSH_LINES_ALL
1256 WHERE order_header_id = p_source_document_id
1257 and order_line_id = p_source_document_line_id)
1258 AND wdd.inventory_item_id = pr_order_line.inventory_item_id;
1259
1260 CURSOR cur_get_picking_tax_lines_new(p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE,
1261 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE) IS
1262
1263 SELECT ptl.tax_line_no tax_line_no,
1264 ptl.tax_id tax_id,
1265 ptl.tax_rate tax_rate,
1266 ptl.qty_rate qty_rate,
1267 ptl.uom uom,
1268 ptl.precedence_1 precedence_1,
1269 ptl.precedence_2 precedence_2,
1270 ptl.precedence_3 precedence_3,
1271 ptl.precedence_4 precedence_4,
1272 ptl.precedence_5 precedence_5,
1273 ptl.precedence_6 precedence_6,
1274 ptl.precedence_7 precedence_7,
1275 ptl.precedence_8 precedence_8,
1276 ptl.precedence_9 precedence_9,
1277 ptl.precedence_10 precedence_10,
1278 SUM(ptl.tax_amount) tax_amount,
1279 SUM(ptl.base_tax_amount) base_tax_amount,
1280 SUM(ptl.func_tax_amount) func_tax_amount,
1281 MIN(ptl.delivery_detail_id) delivery_detail_id
1282 FROM JAI_OM_WSH_LINES_ALL pl, JAI_OM_WSH_LINE_TAXES ptl
1283 WHERE ptl.delivery_detail_id = pl.delivery_detail_id
1284 AND pl.order_header_id = p_source_document_id
1285 AND pl.order_line_id = p_source_document_line_id
1286 GROUP by tax_line_no,
1287 tax_id,
1288 tax_rate,
1289 qty_rate,
1290 uom,
1291 precedence_1,
1292 precedence_2,
1293 precedence_3,
1294 precedence_4,
1295 precedence_5,
1296 precedence_6,
1297 precedence_7,
1298 precedence_8,
1299 precedence_9,
1300 precedence_10;
1301
1302 -- Check whether a rma_tax_lines exist for the new line_id and tax_id
1303 CURSOR cur_chk_rma_tax_lines_exists(p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE,
1304 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE) IS
1305 SELECT 'X'
1306 FROM JAI_OM_OE_RMA_TAXES
1307 WHERE rma_line_id = p_line_id
1308 AND tax_id = p_tax_id;
1309
1310 l_tax_exists VARCHAR2(1);
1311
1312 lv_check_vat_type_exists VARCHAR2(1);
1313 v_date_ordered DATE;
1314 v_date_confirmed DATE;
1315 v_delivery_detail_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE;
1316 v_excise_return_days JAI_CMN_INVENTORY_ORGS.excise_return_days % TYPE;
1317 v_sales_return_days JAI_CMN_INVENTORY_ORGS.sales_return_days % TYPE;
1318 v_vat_return_days JAI_CMN_INVENTORY_ORGS.vat_return_days % TYPE;
1319 v_excise_flag VARCHAR2(1);
1320 v_sales_flag VARCHAR2(1);
1321 v_vat_flag VARCHAR2(1);
1322 v_round_tax NUMBER;
1323 v_round_base NUMBER;
1324 v_round_func NUMBER;
1325 v_tax_total NUMBER;
1326 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%type;
1327 v_trading JAI_CMN_INVENTORY_ORGS.trading%type;
1328 v_shipped_quantity wsh_delivery_details.shipped_quantity % TYPE;
1329 v_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
1330 v_requested_quantity_uom VARCHAR2(3);
1331 v_conversion_rate NUMBER := 0;
1332 v_cor_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
1333 v_orig_ord_qty Number;
1334 v_rma_quantity_uom VARCHAR2(3);
1335 v_service_type_code varchar2(30);
1336
1337 BEGIN
1338
1339 px_return_code := jai_constants.successful;
1340 v_rma_quantity_uom := pr_order_line.order_quantity_uom;
1341 OPEN c_sales_order_cur;
1342 FETCH c_sales_order_cur
1343 INTO v_orig_ord_qty, v_service_type_code;
1344 CLOSE c_sales_order_cur;
1345
1346 OPEN cur_get_picking_lines(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
1347 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID);
1348 FETCH cur_get_picking_lines
1349 INTO rec_cur_get_picking_lines;
1350 IF cur_get_picking_lines%FOUND THEN
1351
1352 OPEN cur_rma_entry_line_exists(p_line_id => pr_order_line.line_id,
1353 p_header_id => pr_order_line.header_id);
1354 FETCH cur_rma_entry_line_exists
1355 INTO l_exists;
1356 /*
1357 IF a record does not exists with the newline_id and header_id
1358 only then go ahead with the insert
1359 */
1360 IF cur_rma_entry_line_exists%NOTFOUND THEN
1361 -- Insert a record into JAI_OM_OE_RMA_LINES
1362 INSERT INTO JAI_OM_OE_RMA_LINES
1363 (rma_line_number,
1364 rma_line_id,
1365 rma_header_id,
1366 rma_number,
1367 inventory_item_id,
1368 uom,
1369 quantity,
1370 tax_category_id,
1371 selling_price,
1372 tax_amount,
1373 delivery_detail_id,
1374 creation_date,
1375 created_by,
1376 last_update_date,
1377 last_updated_by,
1378 last_update_login,
1379 service_type_code,
1380 ship_to_org_id)
1381 VALUES
1382 (pr_order_line.line_number,
1383 pr_order_line.line_id,
1384 pr_order_line.header_id,
1385 p_order_number,
1386 rec_cur_get_picking_lines.inventory_item_id,
1387 v_rma_quantity_uom,
1388 pr_order_line.ordered_quantity,
1389 rec_cur_get_picking_lines.tax_category_id,
1390 pr_order_line.unit_selling_price,
1391 rec_cur_get_picking_lines.tax_amount,
1392 rec_cur_get_picking_lines.delivery_detail_id,
1393 pr_order_line.creation_date,
1394 pr_order_line.created_by,
1395 pr_order_line.last_update_date,
1396 pr_order_line.last_updated_by,
1397 pr_order_line.last_update_login,
1398 v_service_type_code,
1399 pr_order_line.ship_to_org_id);
1400 END IF;
1401 CLOSE cur_rma_entry_line_exists;
1402
1403 open cur_get_ddetail_id(p_source_document_id => pr_order_line.header_id,
1404 p_source_document_line_id => pr_order_line.line_id);
1405 fetch cur_get_ddetail_id
1406 into v_ddetail_id;
1407 close cur_get_ddetail_id;
1408
1409 /*
1410 || Following code copied from internal procedure rma_insert of Procedure JA_IN_RMA_MAINTAIN (version 115.5)
1411 */
1412 OPEN c_get_detail_id(v_ddetail_id);
1413 FETCH c_get_detail_id
1414 INTO v_delivery_detail_id, v_date_confirmed;
1415 CLOSE c_get_detail_id;
1416
1417 /* moved code from below IF condition to here to check VAT return days for both
1418 shippable and non-shippalbe lines.*/
1419 OPEN c_get_days_flags;
1420 FETCH c_get_days_flags
1421 INTO v_excise_return_days,
1422 v_sales_return_days,
1423 v_vat_return_days,
1424 v_manufacturing,
1425 v_trading;
1426 CLOSE c_get_days_flags;
1427
1428 OPEN c_ordered_date;
1429 FETCH c_ordered_date
1430 INTO v_date_ordered;
1431 CLOSE c_ordered_date;
1432
1433 IF v_delivery_detail_id IS NOT NULL THEN
1434 IF (v_excise_return_days IS NULL OR
1435 (v_date_ordered - v_date_confirmed) <=
1436 v_excise_return_days) THEN
1437 v_excise_flag := 'Y';
1438 ELSE
1439 v_excise_flag := 'N';
1440 END IF;
1441
1442 IF (v_sales_return_days IS NULL OR
1443 (v_date_ordered - v_date_confirmed) <=
1444 v_sales_return_days) THEN
1445 v_sales_flag := 'Y';
1446 ELSE
1447 v_sales_flag := 'N';
1448 END IF;
1449
1450 IF (v_vat_return_days IS NULL OR
1451 (v_date_ordered - v_date_confirmed) <= v_vat_return_days) THEN
1452
1453 v_vat_flag := 'Y';
1454 ELSE
1455 v_vat_flag := 'N';
1456 END IF;
1457
1458 /*Fetch Shipped Quantity based on Source Order Details instead of Delivery Details
1459 as a single Order Line can have multiple Delivery Details resulting in incorrect Shipped Quantity*/
1460 OPEN c_get_ship_qty(pr_order_line.SOURCE_DOCUMENT_ID,
1461 pr_order_line.SOURCE_DOCUMENT_LINE_ID);
1462 FETCH c_get_ship_qty
1463 INTO v_shipped_quantity;
1464 CLOSE c_get_ship_qty;
1465 OPEN c_get_quantity(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
1466 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID);
1467 FETCH c_get_quantity
1468 INTO v_quantity;
1469 CLOSE c_get_quantity;
1470
1471 IF v_quantity <> 0 THEN
1472 OPEN requested_qty_uom_cur(v_delivery_detail_id);
1473 FETCH requested_qty_uom_cur
1474 INTO v_requested_quantity_uom;
1475 CLOSE requested_qty_uom_cur;
1476
1477 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1478 v_rma_quantity_uom,
1479 pr_order_line.inventory_item_id,
1480 v_conversion_rate);
1481 IF NVL(v_conversion_rate, 0) <= 0 THEN
1482 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
1483 v_rma_quantity_uom,
1484 0,
1485 v_conversion_rate);
1486 IF NVL(v_conversion_rate, 0) <= 0 THEN
1487 v_conversion_rate := 1; --Changed v_conversion_rate from 0 to 1, so that divide by zero error does not occur.
1488 END IF;
1489 END IF;
1490 v_cor_amount := (pr_order_line.ordered_quantity / v_quantity) *
1491 (1 / v_conversion_rate);
1492 END IF;
1493
1494 IF ROUND(v_shipped_quantity, 2) <
1495 ROUND(pr_order_line.ordered_quantity * (1 / v_conversion_rate), 2) THEN
1496 RAISE_APPLICATION_ERROR(-20401,
1497 'RMA quantity can NOT be more than shipped quantity');
1498 END IF;
1499
1500 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
1501 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID) LOOP
1502 OPEN cur_chk_rma_tax_lines_exists(p_line_id => pr_order_line.line_id,
1503 p_tax_id => rec_cur_get_picking_tax_lines.tax_id);
1504 FETCH cur_chk_rma_tax_lines_exists
1505 INTO l_exists;
1506 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
1507
1508 IF rec_cur_get_picking_tax_lines.tax_type IN
1509 ('Excise',
1510 'Addl. Excise',
1511 'Other Excise',
1512 'TDS',
1513 'CVD') THEN
1514 v_round_tax := ROUND((v_cor_amount *
1515 rec_cur_get_picking_tax_lines.tax_amount),
1516 rec_cur_get_picking_tax_lines.rounding_factor);
1517 v_round_base := ROUND((v_cor_amount *
1518 rec_cur_get_picking_tax_lines.base_tax_amount),
1519 rec_cur_get_picking_tax_lines.rounding_factor);
1520 v_round_func := ROUND((v_cor_amount *
1521 rec_cur_get_picking_tax_lines.func_tax_amount),
1522 rec_cur_get_picking_tax_lines.rounding_factor);
1523 ELSE
1524 v_round_tax := ROUND((v_cor_amount *
1525 rec_cur_get_picking_tax_lines.tax_amount),
1526 2);
1527 v_round_base := ROUND((v_cor_amount *
1528 rec_cur_get_picking_tax_lines.base_tax_amount),
1529 2);
1530 v_round_func := ROUND((v_cor_amount *
1531 rec_cur_get_picking_tax_lines.func_tax_amount),
1532 2);
1533 END IF;
1534
1535 lv_check_vat_type_exists := NULL;
1536
1537 OPEN c_check_Vat_type_Tax_exists(rec_cur_get_picking_tax_lines.tax_type);
1538 FETCH c_check_Vat_type_Tax_exists
1539 INTO lv_check_vat_type_exists;
1540 CLOSE c_check_Vat_type_Tax_exists;
1541
1542 IF (rec_cur_get_picking_tax_lines.tax_type IN
1543 ('Excise',
1544 'Addl. Excise',
1545 'Other Excise',
1546 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
1547 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) AND
1548 v_excise_flag = 'N') OR
1549 (rec_cur_get_picking_tax_lines.tax_type IN
1550 ('Sales Tax', 'CST') AND v_sales_flag = 'N') OR
1551 (lv_check_vat_type_exists = 1 AND v_vat_flag = 'N') THEN
1552 v_round_tax := 0;
1553 v_round_base := 0;
1554 v_round_func := 0;
1555 END IF;
1556
1557 INSERT INTO JAI_OM_OE_RMA_TAXES
1558 (rma_line_id,
1559 tax_line_no,
1560 tax_id,
1561 tax_rate,
1562 qty_rate,
1563 uom,
1564 tax_amount,
1565 base_tax_amount,
1566 func_tax_amount,
1567 precedence_1,
1568 precedence_2,
1569 precedence_3,
1570 precedence_4,
1571 precedence_5,
1572 precedence_6,
1573 precedence_7,
1574 precedence_8,
1575 precedence_9,
1576 precedence_10,
1577 delivery_detail_id,
1578 creation_date,
1579 created_by,
1580 last_update_date,
1581 last_updated_by,
1582 last_update_login)
1583 VALUES
1584 (pr_order_line.line_id,
1585 rec_cur_get_picking_tax_lines.tax_line_no,
1586 rec_cur_get_picking_tax_lines.tax_id,
1587 rec_cur_get_picking_tax_lines.tax_rate,
1588 rec_cur_get_picking_tax_lines.qty_rate,
1589 rec_cur_get_picking_tax_lines.uom,
1590 v_round_tax,
1591 v_round_base,
1592 v_round_func,
1593 rec_cur_get_picking_tax_lines.precedence_1,
1594 rec_cur_get_picking_tax_lines.precedence_2,
1595 rec_cur_get_picking_tax_lines.precedence_3,
1596 rec_cur_get_picking_tax_lines.precedence_4,
1597 rec_cur_get_picking_tax_lines.precedence_5,
1598 rec_cur_get_picking_tax_lines.precedence_6,
1599 rec_cur_get_picking_tax_lines.precedence_7,
1600 rec_cur_get_picking_tax_lines.precedence_8,
1601 rec_cur_get_picking_tax_lines.precedence_9,
1602 rec_cur_get_picking_tax_lines.precedence_10,
1603 rec_cur_get_picking_tax_lines.delivery_detail_id,
1604 pr_order_line.creation_date,
1605 pr_order_line.created_by,
1606 pr_order_line.last_update_date,
1607 pr_order_line.last_updated_by,
1608 pr_order_line.last_update_login);
1609
1610 IF rec_cur_get_picking_tax_lines.tax_type <> 'TDS' THEN
1611 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
1612 END IF;
1613 END IF; --IF cur_chk_rma_tax_lines_exists%NOTFOUND
1614 CLOSE cur_chk_rma_tax_lines_exists;
1615 END LOOP;
1616
1617 UPDATE JAI_OM_OE_RMA_LINES
1618 SET tax_amount = v_tax_total
1619 WHERE rma_line_id = pr_order_line.line_id;
1620
1621 -- need to process copying taxes from referenced SO line for non-shippable RMA line whose delivery_detail_id is NULL
1622 ELSIF NVL(pr_order_line.SHIPPABLE_FLAG, 'Y') = 'N' THEN
1623 -- need to check VAT flag for non-shippable, if the validation fails, then vat taxes amounts should be copied as zero.
1624 OPEN c_fulfilled_date;
1625 FETCH c_fulfilled_date
1626 INTO v_date_confirmed;
1627 CLOSE c_fulfilled_date;
1628
1629 IF (v_vat_return_days IS NULL OR
1630 (v_date_ordered - v_date_confirmed) <= v_vat_return_days) THEN
1631
1632 v_vat_flag := 'Y';
1633 ELSE
1634 v_vat_flag := 'N';
1635 END IF;
1636
1637 IF pr_order_line.ORDERED_QUANTITY <> 0 THEN
1638 FOR tax_line_rec IN (SELECT tax_line_no,
1639 precedence_1,
1640 precedence_2,
1641 precedence_3,
1642 precedence_4,
1643 precedence_5,
1644 sptl.tax_id,
1645 sptl.tax_rate,
1646 sptl.qty_rate,
1647 uom,
1648 sptl.tax_amount,
1649 nvl(jtc.rounding_factor, 0) rounding_factor,
1650 base_tax_amount,
1651 func_tax_amount,
1652 jtc.tax_type,
1653 precedence_6,
1654 precedence_7,
1655 precedence_8,
1656 precedence_9,
1657 precedence_10
1658 FROM JAI_OM_WSH_LINE_TAXES sptl,
1659 JAI_CMN_TAXES_ALL jtc
1660 WHERE order_line_id =
1661 pr_order_line.reference_line_id
1662 AND jtc.tax_id = sptl.tax_id) LOOP
1663 lv_check_vat_type_exists := NULL;
1664
1665 OPEN c_check_Vat_type_Tax_exists(tax_line_rec.tax_type);
1666 FETCH c_check_Vat_type_Tax_exists
1667 INTO lv_check_vat_type_exists;
1668 CLOSE c_check_Vat_type_Tax_exists;
1669
1670 IF (lv_check_vat_type_exists = 1 AND v_vat_flag = 'N') THEN
1671 v_round_tax := 0;
1672 v_round_base := 0;
1673 v_round_func := 0;
1674 ELSE
1675 v_round_tax := tax_line_rec.tax_amount;
1676 v_round_base := tax_line_rec.base_tax_amount;
1677 v_round_func := tax_line_rec.func_tax_amount;
1678 END IF; -- lv_check_vat_type_exists = 1 AND v_vat_flag = 'N'
1679
1680 INSERT INTO JAI_OM_OE_RMA_TAXES
1681 (rma_line_id,
1682 delivery_detail_id,
1683 tax_line_no,
1684 precedence_1,
1685 precedence_2,
1686 precedence_3,
1687 precedence_4,
1688 precedence_5,
1689 tax_id,
1690 tax_rate,
1691 qty_rate,
1692 uom,
1693 tax_amount,
1694 base_tax_amount,
1695 func_tax_amount,
1696 creation_date,
1697 created_by,
1698 last_update_date,
1699 last_updated_by,
1700 last_update_login,
1701 precedence_6,
1702 precedence_7,
1703 precedence_8,
1704 precedence_9,
1705 precedence_10)
1706 VALUES
1707 (pr_order_line.line_id,
1708 NULL,
1709 tax_line_rec.tax_line_no,
1710 tax_line_rec.precedence_1,
1711 tax_line_rec.precedence_2,
1712 tax_line_rec.precedence_3,
1713 tax_line_rec.precedence_4,
1714 tax_line_rec.precedence_5,
1715 tax_line_rec.tax_id,
1716 tax_line_rec.tax_rate,
1717 tax_line_rec.qty_rate,
1718 tax_line_rec.uom,
1719 v_round_tax,
1720 v_round_base,
1721 v_round_func,
1722 pr_order_line.creation_date,
1723 pr_order_line.created_by,
1724 pr_order_line.last_update_date,
1725 pr_order_line.last_updated_by,
1726 pr_order_line.last_update_login,
1727 tax_line_rec.precedence_6,
1728 tax_line_rec.precedence_7,
1729 tax_line_rec.precedence_8,
1730 tax_line_rec.precedence_9,
1731 tax_line_rec.precedence_10);
1732 END LOOP; -- tax_line_rec IN (SELECT tax_line_no ......
1733 END IF; --IF pr_order_line.ORDERED_QUANTITY <> 0
1734
1735 END IF; --IF v_delivery_detail_id IS NOT NULL
1736
1737 ELSE
1738 -- Details in picking lines not found . Raise an error message
1739 CLOSE cur_get_picking_lines;
1740 /* RAISE_APPLICATION_ERROR (-20001,'No data found in localisation shipping tables, hence copy cannot be done');
1741 */
1742 px_return_code := jai_constants.expected_error;
1743 px_return_message := 'No data found in localisation shipping tables,hence copy cannot be done';
1744 return;
1745 END IF;
1746 CLOSE cur_get_picking_lines;
1747
1748 /************************************ Order TO Return tax lines computations ********************************************/
1749 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines_new(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
1750 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID) LOOP
1751 OPEN cur_chk_rma_tax_lines_exists(p_line_id => pr_order_line.line_id,
1752 p_tax_id => rec_cur_get_picking_tax_lines.tax_id);
1753 FETCH cur_chk_rma_tax_lines_exists
1754 INTO l_tax_exists;
1755 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
1756 INSERT INTO JAI_OM_OE_RMA_TAXES
1757 (rma_line_id,
1758 tax_line_no,
1759 tax_id,
1760 tax_rate,
1761 qty_rate,
1762 uom,
1763 tax_amount,
1764 base_tax_amount,
1765 func_tax_amount,
1766 precedence_1,
1767 precedence_2,
1768 precedence_3,
1769 precedence_4,
1770 precedence_5,
1771 precedence_6,
1772 precedence_7,
1773 precedence_8,
1774 precedence_9,
1775 precedence_10,
1776 delivery_detail_id,
1777 creation_date,
1778 created_by,
1779 last_update_date,
1780 last_updated_by,
1781 last_update_login)
1782 VALUES
1783 (pr_order_line.line_id,
1784 rec_cur_get_picking_tax_lines.tax_line_no,
1785 rec_cur_get_picking_tax_lines.tax_id,
1786 rec_cur_get_picking_tax_lines.tax_rate,
1787 rec_cur_get_picking_tax_lines.qty_rate,
1788 rec_cur_get_picking_tax_lines.uom,
1789 rec_cur_get_picking_tax_lines.tax_amount,
1790 rec_cur_get_picking_tax_lines.base_tax_amount,
1791 rec_cur_get_picking_tax_lines.func_tax_amount,
1792 rec_cur_get_picking_tax_lines.precedence_1,
1793 rec_cur_get_picking_tax_lines.precedence_2,
1794 rec_cur_get_picking_tax_lines.precedence_3,
1795 rec_cur_get_picking_tax_lines.precedence_4,
1796 rec_cur_get_picking_tax_lines.precedence_5,
1797 rec_cur_get_picking_tax_lines.precedence_6,
1798 rec_cur_get_picking_tax_lines.precedence_7,
1799 rec_cur_get_picking_tax_lines.precedence_8,
1800 rec_cur_get_picking_tax_lines.precedence_9,
1801 rec_cur_get_picking_tax_lines.precedence_10,
1802 rec_cur_get_picking_tax_lines.delivery_detail_id,
1803 pr_order_line.creation_date,
1804 pr_order_line.created_by,
1805 pr_order_line.last_update_date,
1806 pr_order_line.last_updated_by,
1807 pr_order_line.last_update_login);
1808
1809 END IF;
1810 CLOSE cur_chk_rma_tax_lines_exists;
1811 END LOOP;
1812
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 RAISE;
1816 END COPY_ORDER_TO_RETURN;
1817
1818 /*
1819 REM +======================================================================+
1820 REM NAME COPY_MIXED_TO_RETURN
1821 REM
1822 REM DESCRIPTION COPY RETURN TAXES and LINES FROM ORIGINAL MIXED ORDER
1823 REM
1824 REM NOTES Call from procdure COPY_SOURCE_ORDER
1825 REM
1826 REM +======================================================================+
1827 */
1828 PROCEDURE COPY_MIXED_TO_RETURN(pr_order_line t_rec%type,
1829 p_order_number NUMBER,
1830 px_return_code out nocopy varchar2,
1831 px_return_message out nocopy varchar2) IS
1832
1833 /* fetch the details of the original RMA order
1834 */
1835 CURSOR c_rma_details(cp_rma_header_id number,
1836 cp_rma_line_id number) is
1837 SELECT *
1838 FROM JAI_OM_OE_RMA_LINES
1839 WHERE rma_header_id = cp_rma_header_id
1840 AND rma_line_id = cp_rma_line_id;
1841
1842 /* get the new order number from oe_order_headers_all*/
1843 CURSOR c_rma_number(cp_rma_header_id number) is
1844 SELECT order_number
1845 FROM oe_order_headers_all
1846 WHERE header_id = cp_rma_header_id;
1847
1848 cv_rma_details C_RMA_DETAILS%ROWTYPE;
1849 lv_rma_number OE_ORDER_HEADERS_ALL.ORDER_NUMBER%TYPE;
1850
1851 -- get the details from JAI_OM_WSH_LINES_ALL table
1852 CURSOR cur_get_picking_lines(p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE,
1853 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE) IS
1854 SELECT pl.inventory_item_id inventory_item_id,
1855 pl.unit_code unit_code,
1856 sum(pl.quantity) quantity,
1857 pl.tax_category_id tax_category_id,
1858 pl.selling_price selling_price,
1859 sum(pl.tax_amount) tax_amount,
1860 min(pl.delivery_detail_id) delivery_detail_id
1861 FROM JAI_OM_WSH_LINES_ALL pl
1862 WHERE pl.order_header_id = p_source_document_id
1863 AND pl.order_line_id = p_source_document_line_id
1864 GROUP BY pl.inventory_item_id,
1865 pl.unit_code,
1866 pl.selling_price,
1867 pl.tax_category_id;
1868
1869 cursor c_sales_order_cur is
1870 select quantity, service_type_code
1871 from JAI_OM_OE_SO_LINES
1872 where line_id = pr_order_line.reference_line_id;
1873
1874 CURSOR cur_rma_entry_line_exists(p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE,
1875 p_header_id OE_ORDER_LINES_ALL.HEADER_ID%TYPE) IS
1876 SELECT 'X'
1877 FROM JAI_OM_OE_RMA_LINES
1878 WHERE rma_line_id = p_line_id
1879 AND rma_header_id = p_header_id;
1880
1881 l_exists VARCHAR2(1);
1882
1883 rec_cur_get_picking_lines cur_get_picking_lines%ROWTYPE;
1884
1885 CURSOR cur_get_picking_tax_lines(p_source_document_id JAI_OM_WSH_LINES_ALL.ORDER_HEADER_ID%TYPE,
1886 p_source_document_line_id JAI_OM_WSH_LINES_ALL.ORDER_LINE_ID%TYPE) IS
1887 SELECT ptl.tax_line_no tax_line_no,
1888 ptl.tax_id tax_id,
1889 ptl.tax_rate tax_rate,
1890 ptl.qty_rate qty_rate,
1891 ptl.uom uom,
1892 ptl.precedence_1 precedence_1,
1893 ptl.precedence_2 precedence_2,
1894 ptl.precedence_3 precedence_3,
1895 ptl.precedence_4 precedence_4,
1896 ptl.precedence_5 precedence_5,
1897 ptl.precedence_6 precedence_6,
1898 ptl.precedence_7 precedence_7,
1899 ptl.precedence_8 precedence_8,
1900 ptl.precedence_9 precedence_9,
1901 ptl.precedence_10 precedence_10,
1902 jtc.tax_type tax_type,
1903 nvl(jtc.rounding_factor, 0) rounding_factor,
1904 SUM(ptl.tax_amount) tax_amount,
1905 SUM(ptl.base_tax_amount) base_tax_amount,
1906 SUM(ptl.func_tax_amount) func_tax_amount,
1907 MIN(ptl.delivery_detail_id) delivery_detail_id
1908 FROM JAI_OM_WSH_LINES_ALL pl,
1909 JAI_OM_WSH_LINE_TAXES ptl,
1910 JAI_CMN_TAXES_ALL jtc
1911 WHERE ptl.delivery_detail_id = pl.delivery_detail_id
1912 AND pl.order_header_id = p_source_document_id
1913 AND pl.order_line_id = p_source_document_line_id
1914 AND jtc.tax_id = ptl.tax_id
1915 GROUP by ptl.tax_line_no,
1916 ptl.tax_id,
1917 ptl.tax_rate,
1918 ptl.qty_rate,
1919 ptl.uom,
1920 precedence_1,
1921 precedence_2,
1922 precedence_3,
1923 precedence_4,
1924 precedence_5,
1925 precedence_6,
1926 precedence_7,
1927 precedence_8,
1928 precedence_9,
1929 precedence_10,
1930 jtc.tax_type,
1931 nvl(jtc.rounding_factor, 0);
1932
1933 CURSOR cur_chk_rma_tax_lines_exists(p_line_id JAI_OM_OE_RMA_TAXES.RMA_LINE_ID%TYPE,
1934 p_tax_id JAI_OM_OE_RMA_TAXES.TAX_ID%TYPE) IS
1935 SELECT 'X'
1936 FROM JAI_OM_OE_RMA_TAXES
1937 WHERE rma_line_id = p_line_id
1938 AND tax_id = p_tax_id;
1939 CURSOR c_get_quantity(p_source_document_id JAI_OM_WSH_LINES_ALL.order_header_id%type,
1940 p_source_document_line_id JAI_OM_WSH_LINES_ALL.order_line_id%type) IS
1941 -- cmmented out by zhiwei.xin for bug#14669425 on 16-Oct-2012 begin
1942 /*SELECT sum(quantity)
1943 FROM JAI_OM_WSH_LINES_ALL pl, JAI_OM_WSH_LINE_TAXES ptl
1944 WHERE ptl.delivery_detail_id = pl.delivery_detail_id
1945 AND pl.order_header_id = p_source_document_id
1946 AND pl.order_line_id = p_source_document_line_id;*/
1947 -- commented out by zhiwei.xin for bug#14669425 on 16-Oct-2012 end.
1948 -- added by zhiwei.xin for bug#14669425 on 16-Oct-2012 begin
1949 SELECT quantity
1950 FROM JAI_OM_WSH_LINES_ALL
1951 WHERE order_header_id = p_source_document_id
1952 AND order_line_id = p_source_document_line_id;
1953 -- added by zhiwei.xin for bug#14669425 on 16-Oct-2012 end.
1954
1955 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER) IS
1956 SELECT requested_quantity_uom
1957 FROM wsh_delivery_details
1958 WHERE delivery_detail_id = p_delivery_detail_id;
1959
1960 CURSOR c_check_vat_type_tax_exists(cp_tax_type VARCHAR2) IS
1961 SELECT 1
1962 FROM jai_regime_tax_types_v
1963 WHERE regime_code = jai_constants.vat_regime
1964 AND tax_type = cp_tax_type;
1965
1966 cursor cur_get_ddetail_id(p_source_document_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_ID%TYPE,
1967 p_source_document_line_id OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID%TYPE) is
1968 select delivery_detail_id
1969 from JAI_OM_OE_RMA_LINES
1970 where rma_header_id = p_source_document_id
1971 AND rma_line_id = p_source_document_line_id;
1972
1973 v_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type;
1974
1975 CURSOR c_get_detail_id(p_ddetail_id JAI_OM_OE_RMA_LINES.delivery_detail_id%type) IS
1976 SELECT wdd.delivery_detail_id, wnd.confirm_date
1977 FROM wsh_delivery_details wdd,
1978 wsh_delivery_assignments wda,
1979 wsh_new_deliveries wnd
1980 WHERE wdd.delivery_detail_id = p_ddetail_id
1981 AND wda.delivery_detail_id = wdd.delivery_detail_id
1982 AND wnd.delivery_id = wda.delivery_id;
1983
1984 CURSOR c_get_days_flags IS
1985 SELECT excise_return_days,
1986 sales_return_days,
1987 vat_return_days,
1988 nvl(manufacturing, 'N') manufacturing,
1989 nvl(trading, 'N') trading
1990 FROM JAI_CMN_INVENTORY_ORGS
1991 WHERE organization_id = pr_order_line.ship_from_org_id
1992 AND location_id = 0;
1993
1994 CURSOR c_ordered_date IS
1995 SELECT ordered_date
1996 FROM oe_order_headers_all
1997 WHERE header_id = pr_order_line.header_id;
1998
1999 CURSOR c_fulfilled_date IS
2000 SELECT CREATION_DATE
2001 FROM JAI_OM_WSH_LINES_ALL
2002 WHERE ORDER_LINE_ID = pr_order_line.reference_line_id
2003 AND SHIPPABLE_FLAG = 'N';
2004
2005 CURSOR c_get_ship_qty(p_source_document_id NUMBER,
2006 p_source_document_line_id NUMBER) IS
2007 SELECT SUM(wdd.shipped_quantity) qty
2008 FROM wsh_delivery_details wdd
2009 WHERE wdd.delivery_detail_id in
2010 (SELECT delivery_detail_id
2011 FROM JAI_OM_WSH_LINES_ALL
2012 WHERE order_header_id = p_source_document_id
2013 and order_line_id = p_source_document_line_id)
2014 AND wdd.inventory_item_id = pr_order_line.inventory_item_id;
2015
2016 lv_check_vat_type_exists VARCHAR2(1);
2017 v_date_ordered DATE;
2018 v_date_confirmed DATE;
2019 v_delivery_detail_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE;
2020 v_excise_return_days JAI_CMN_INVENTORY_ORGS.excise_return_days % TYPE;
2021 v_sales_return_days JAI_CMN_INVENTORY_ORGS.sales_return_days % TYPE;
2022 v_vat_return_days JAI_CMN_INVENTORY_ORGS.vat_return_days % TYPE;
2023 v_excise_flag VARCHAR2(1);
2024 v_sales_flag VARCHAR2(1);
2025 v_vat_flag VARCHAR2(1);
2026 v_round_tax NUMBER;
2027 v_round_base NUMBER;
2028 v_round_func NUMBER;
2029 v_tax_total NUMBER;
2030 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%type;
2031 v_trading JAI_CMN_INVENTORY_ORGS.trading%type;
2032 v_shipped_quantity wsh_delivery_details.shipped_quantity % TYPE;
2033 v_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
2034 v_requested_quantity_uom VARCHAR2(3);
2035 v_conversion_rate NUMBER := 0;
2036 v_cor_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
2037 v_orig_ord_qty Number;
2038 v_rma_quantity_uom VARCHAR2(3);
2039 v_service_type_code varchar2(30);
2040
2041 BEGIN
2042 -- here need to code the cases where order category code is MIXED and line_category_code is RETURN
2043 -- this is typically the case where a legacy RMA is copied another legacy RMA
2044 -- need to insert into JAI_OM_OE_RMA_LINES from the source.
2045
2046 IF pr_order_line.return_context IS NULL THEN
2047
2048 px_return_code := jai_constants.successful;
2049
2050 open c_rma_details(pr_order_line.source_document_id,
2051 pr_order_line.source_document_line_id);
2052 fetch c_rma_details
2053 into cv_rma_details;
2054 close c_rma_details;
2055
2056 open c_rma_number(pr_order_line.header_id);
2057 fetch c_rma_number
2058 into lv_rma_number;
2059 close c_rma_number;
2060
2061 insert into JAI_OM_OE_RMA_LINES
2062 (rma_line_id,
2063 rma_line_number,
2064 rma_header_id,
2065 rma_number,
2066 picking_line_id,
2067 uom,
2068 selling_price,
2069 quantity,
2070 tax_category_id,
2071 tax_amount,
2072 inventory_item_id,
2073 received_flag,
2074 assessable_value,
2075 creation_date,
2076 created_by,
2077 last_update_date,
2078 last_updated_by,
2079 last_update_login,
2080 excise_duty_rate,
2081 rate_per_unit,
2082 delivery_detail_id,
2083 ship_to_org_id)
2084 values
2085 (pr_order_line.line_id,
2086 pr_order_line.line_number,
2087 pr_order_line.header_id,
2088 lv_rma_number,
2089 null,
2090 pr_order_line.order_quantity_uom,
2091 pr_order_line.unit_selling_price,
2092 pr_order_line.ordered_quantity,
2093 cv_rma_details.tax_category_id,
2094 (cv_rma_details.tax_amount),
2095 pr_order_line.inventory_item_id,
2096 cv_rma_details.received_flag,
2097 cv_rma_details.assessable_value,
2098 sysdate,
2099 pr_order_line.created_by,
2100 sysdate,
2101 pr_order_line.last_updated_by,
2102 pr_order_line.last_update_login,
2103 cv_rma_details.excise_duty_rate,
2104 cv_rma_details.rate_per_unit,
2105 null,
2106 pr_order_line.ship_to_org_id);
2107
2108 FOR cv_rma_taxes in (select *
2109 from JAI_OM_OE_RMA_TAXES
2110 where rma_line_id =
2111 pr_order_line.source_document_line_id) Loop
2112 insert into JAI_OM_OE_RMA_TAXES
2113 (rma_line_id,
2114 tax_line_no,
2115 precedence_1,
2116 precedence_2,
2117 precedence_3,
2118 precedence_4,
2119 precedence_5,
2120 tax_id,
2121 tax_rate,
2122 qty_rate,
2123 uom,
2124 tax_amount,
2125 base_tax_amount,
2126 func_tax_amount,
2127 creation_date,
2128 created_by,
2129 last_update_date,
2130 last_updated_by,
2131 last_update_login,
2132 delivery_detail_id,
2133 precedence_6,
2134 precedence_7,
2135 precedence_8,
2136 precedence_9,
2137 precedence_10)
2138 values
2139 (pr_order_line.line_id,
2140 cv_rma_taxes.tax_line_no,
2141 cv_rma_taxes.precedence_1,
2142 cv_rma_taxes.precedence_2,
2143 cv_rma_taxes.precedence_3,
2144 cv_rma_taxes.precedence_4,
2145 cv_rma_taxes.precedence_5,
2146 cv_rma_taxes.tax_id,
2147 cv_rma_taxes.tax_rate,
2148 cv_rma_taxes.qty_rate,
2149 cv_rma_taxes.uom,
2150 (cv_rma_taxes.tax_amount),
2151 cv_rma_taxes.base_tax_amount,
2152 cv_rma_taxes.func_Tax_amount,
2153 pr_order_line.creation_date,
2154 pr_order_line.created_by,
2155 pr_order_line.last_update_Date,
2156 pr_order_line.last_updated_by,
2157 pr_order_line.last_update_login,
2158 cv_rma_taxes.delivery_detail_id,
2159 cv_rma_taxes.precedence_6,
2160 cv_rma_taxes.precedence_7,
2161 cv_rma_taxes.precedence_8,
2162 cv_rma_taxes.precedence_9,
2163 cv_rma_taxes.precedence_10);
2164 end Loop;
2165
2166 elsif pr_order_line.return_context = 'ORDER' THEN
2167 -- here need to code the cases where order catego
2168 --this script will execute only for the return order context.
2169
2170 px_return_code := jai_constants.successful;
2171 v_rma_quantity_uom := pr_order_line.order_quantity_uom;
2172
2173 OPEN c_sales_order_cur;
2174 FETCH c_sales_order_cur
2175 into v_orig_ord_qty, v_service_type_code;
2176 CLOSE c_sales_order_cur;
2177
2178 OPEN cur_get_picking_lines(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
2179 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID);
2180 FETCH cur_get_picking_lines
2181 INTO rec_cur_get_picking_lines;
2182 IF cur_get_picking_lines%FOUND THEN
2183
2184 OPEN cur_rma_entry_line_exists(p_line_id => pr_order_line.line_id,
2185 p_header_id => pr_order_line.header_id);
2186 FETCH cur_rma_entry_line_exists
2187 INTO l_exists;
2188 /*
2189 IF a record does not exists with the newline_id and header_id
2190 only then go ahead with the insert
2191 */
2192 IF cur_rma_entry_line_exists%NOTFOUND THEN
2193
2194 INSERT INTO JAI_OM_OE_RMA_LINES
2195 (rma_line_number,
2196 rma_line_id,
2197 rma_header_id,
2198 rma_number,
2199 inventory_item_id,
2200 uom,
2201 quantity,
2202 tax_category_id,
2203 selling_price,
2204 tax_amount,
2205 delivery_detail_id,
2206 creation_date,
2207 created_by,
2208 last_update_date,
2209 last_updated_by,
2210 last_update_login,
2211 service_type_code,
2212 ship_to_org_id)
2213 VALUES
2214 (pr_order_line.line_number,
2215 pr_order_line.line_id,
2216 pr_order_line.header_id,
2217 p_order_number,
2218 rec_cur_get_picking_lines.inventory_item_id,
2219 v_rma_quantity_uom,
2220 pr_order_line.ordered_quantity,
2221 rec_cur_get_picking_lines.tax_category_id,
2222 pr_order_line.unit_selling_price,
2223 rec_cur_get_picking_lines.tax_amount,
2224 rec_cur_get_picking_lines.delivery_detail_id,
2225 pr_order_line.creation_date,
2226 pr_order_line.created_by,
2227 pr_order_line.last_update_date,
2228 pr_order_line.last_updated_by,
2229 pr_order_line.last_update_login,
2230 v_service_type_code,
2231 pr_order_line.ship_to_org_id);
2232 END IF;
2233 CLOSE cur_rma_entry_line_exists;
2234
2235 open cur_get_ddetail_id(p_source_document_id => pr_order_line.header_id,
2236 p_source_document_line_id => pr_order_line.line_id);
2237 fetch cur_get_ddetail_id
2238 into v_ddetail_id;
2239 close cur_get_ddetail_id;
2240
2241 OPEN c_get_detail_id(v_ddetail_id);
2242 FETCH c_get_detail_id
2243 INTO v_delivery_detail_id, v_date_confirmed;
2244 CLOSE c_get_detail_id;
2245
2246 /* moved code from below IF condition to here to check VAT return days both for
2247 shippable and non-shippalbe lines. */
2248 OPEN c_get_days_flags;
2249 FETCH c_get_days_flags
2250 INTO v_excise_return_days,
2251 v_sales_return_days,
2252 v_vat_return_days,
2253 v_manufacturing,
2254 v_trading;
2255 CLOSE c_get_days_flags;
2256
2257 OPEN c_ordered_date;
2258 FETCH c_ordered_date
2259 INTO v_date_ordered;
2260 CLOSE c_ordered_date;
2261
2262 IF v_delivery_detail_id IS NOT NULL THEN
2263
2264 IF (v_excise_return_days IS NULL OR
2265 (v_date_ordered - v_date_confirmed) <=
2266 v_excise_return_days) THEN
2267 v_excise_flag := 'Y';
2268 ELSE
2269 v_excise_flag := 'N';
2270 END IF;
2271
2272 IF (v_sales_return_days IS NULL OR
2273 (v_date_ordered - v_date_confirmed) <=
2274 v_sales_return_days) THEN
2275 v_sales_flag := 'Y';
2276 ELSE
2277 v_sales_flag := 'N';
2278 END IF;
2279
2280 IF (v_vat_return_days IS NULL OR
2281 (v_date_ordered - v_date_confirmed) <=
2282 v_vat_return_days) THEN
2283
2284 v_vat_flag := 'Y';
2285 ELSE
2286 v_vat_flag := 'N';
2287 END IF;
2288
2289 /*Fetch Shipped Quantity based on Source Order Details instead of Delivery Details
2290 as a single Order Line can have multiple Delivery Details resulting in incorrect Shipped Quantity*/
2291 OPEN c_get_ship_qty(pr_order_line.SOURCE_DOCUMENT_ID,
2292 pr_order_line.SOURCE_DOCUMENT_LINE_ID);
2293 FETCH c_get_ship_qty
2294 INTO v_shipped_quantity;
2295 CLOSE c_get_ship_qty;
2296
2297 OPEN c_get_quantity(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
2298 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID);
2299 FETCH c_get_quantity
2300 INTO v_quantity;
2301 CLOSE c_get_quantity;
2302
2303 IF v_quantity <> 0 THEN
2304 OPEN requested_qty_uom_cur(v_delivery_detail_id);
2305 FETCH requested_qty_uom_cur
2306 INTO v_requested_quantity_uom;
2307 CLOSE requested_qty_uom_cur;
2308
2309 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
2310 v_rma_quantity_uom,
2311 pr_order_line.inventory_item_id,
2312 v_conversion_rate);
2313 IF NVL(v_conversion_rate, 0) <= 0 THEN
2314 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
2315 v_rma_quantity_uom,
2316 0,
2317 v_conversion_rate);
2318 IF NVL(v_conversion_rate, 0) <= 0 THEN
2319 v_conversion_rate := 1; --Changed v_conversion_rate from 0 to 1, so that divide by zero error does not occur.
2320 END IF;
2321 END IF;
2322 v_cor_amount := (pr_order_line.ordered_quantity / v_quantity) *
2323 (1 / v_conversion_rate);
2324 END IF;
2325
2326 IF round(v_shipped_quantity, 2) <
2327 round(pr_order_line.ordered_quantity * (1 / v_conversion_rate),
2328 2) THEN
2329 RAISE_APPLICATION_ERROR(-20401,
2330 'RMA quantity can NOT be more than shipped quantity');
2331
2332 END IF;
2333
2334 FOR rec_cur_get_picking_tax_lines IN cur_get_picking_tax_lines(p_source_document_id => pr_order_line.SOURCE_DOCUMENT_ID,
2335 p_source_document_line_id => pr_order_line.SOURCE_DOCUMENT_LINE_ID) LOOP
2336 OPEN cur_chk_rma_tax_lines_exists(p_line_id => pr_order_line.line_id,
2337 p_tax_id => rec_cur_get_picking_tax_lines.tax_id);
2338 FETCH cur_chk_rma_tax_lines_exists
2339 INTO l_exists;
2340 IF cur_chk_rma_tax_lines_exists%NOTFOUND THEN
2341
2342 IF rec_cur_get_picking_tax_lines.tax_type IN
2343 ('Excise',
2344 'Addl. Excise',
2345 'Other Excise',
2346 'TDS',
2347 'CVD') THEN
2348 v_round_tax := ROUND((v_cor_amount *
2349 rec_cur_get_picking_tax_lines.tax_amount),
2350 rec_cur_get_picking_tax_lines.rounding_factor);
2351 v_round_base := ROUND((v_cor_amount *
2352 rec_cur_get_picking_tax_lines.base_tax_amount),
2353 rec_cur_get_picking_tax_lines.rounding_factor);
2354 v_round_func := ROUND((v_cor_amount *
2355 rec_cur_get_picking_tax_lines.func_tax_amount),
2356 rec_cur_get_picking_tax_lines.rounding_factor);
2357 ELSE
2358 v_round_tax := ROUND((v_cor_amount *
2359 rec_cur_get_picking_tax_lines.tax_amount),
2360 2);
2361 v_round_base := ROUND((v_cor_amount *
2362 rec_cur_get_picking_tax_lines.base_tax_amount),
2363 2);
2364 v_round_func := ROUND((v_cor_amount *
2365 rec_cur_get_picking_tax_lines.func_tax_amount),
2366 2);
2367 END IF;
2368
2369 lv_check_vat_type_exists := NULL;
2370
2371 OPEN c_check_Vat_type_Tax_exists(rec_cur_get_picking_tax_lines.tax_type);
2372 FETCH c_check_Vat_type_Tax_exists
2373 INTO lv_check_vat_type_exists;
2374 CLOSE c_check_Vat_type_Tax_exists;
2375
2376 IF (rec_cur_get_picking_tax_lines.tax_type IN
2377 ('Excise',
2378 'Addl. Excise',
2379 'Other Excise',
2380 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,
2381 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS) AND
2382 v_excise_flag = 'N') OR
2383 (rec_cur_get_picking_tax_lines.tax_type IN
2384 ('Sales Tax', 'CST') AND v_sales_flag = 'N') OR
2385 (lv_check_vat_type_exists = 1 AND v_vat_flag = 'N') THEN
2386 v_round_tax := 0;
2387 v_round_base := 0;
2388 v_round_func := 0;
2389 END IF;
2390
2391 INSERT INTO JAI_OM_OE_RMA_TAXES
2392 (rma_line_id,
2393 tax_line_no,
2394 tax_id,
2395 tax_rate,
2396 qty_rate,
2397 uom,
2398 tax_amount,
2399 base_tax_amount,
2400 func_tax_amount,
2401 precedence_1,
2402 precedence_2,
2403 precedence_3,
2404 precedence_4,
2405 precedence_5,
2406 precedence_6,
2407 precedence_7,
2408 precedence_8,
2409 precedence_9,
2410 precedence_10,
2411 delivery_detail_id,
2412 creation_date,
2413 created_by,
2414 last_update_date,
2415 last_updated_by,
2416 last_update_login)
2417 VALUES
2418 (pr_order_line.line_id,
2419 rec_cur_get_picking_tax_lines.tax_line_no,
2420 rec_cur_get_picking_tax_lines.tax_id,
2421 rec_cur_get_picking_tax_lines.tax_rate,
2422 rec_cur_get_picking_tax_lines.qty_rate,
2423 rec_cur_get_picking_tax_lines.uom,
2424 v_round_tax,
2425 v_round_base,
2426 v_round_func,
2427 rec_cur_get_picking_tax_lines.precedence_1,
2428 rec_cur_get_picking_tax_lines.precedence_2,
2429 rec_cur_get_picking_tax_lines.precedence_3,
2430 rec_cur_get_picking_tax_lines.precedence_4,
2431 rec_cur_get_picking_tax_lines.precedence_5,
2432 rec_cur_get_picking_tax_lines.precedence_6,
2433 rec_cur_get_picking_tax_lines.precedence_7,
2434 rec_cur_get_picking_tax_lines.precedence_8,
2435 rec_cur_get_picking_tax_lines.precedence_9,
2436 rec_cur_get_picking_tax_lines.precedence_10,
2437 rec_cur_get_picking_tax_lines.delivery_detail_id,
2438 pr_order_line.creation_date,
2439 pr_order_line.created_by,
2440 pr_order_line.last_update_date,
2441 pr_order_line.last_updated_by,
2442 pr_order_line.last_update_login);
2443
2444 IF rec_cur_get_picking_tax_lines.tax_type <> 'TDS' THEN
2445 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
2446 END IF;
2447 END IF; --IF cur_chk_rma_tax_lines_exists%NOTFOUND
2448 CLOSE cur_chk_rma_tax_lines_exists;
2449 END LOOP;
2450
2451 UPDATE JAI_OM_OE_RMA_LINES
2452 SET tax_amount = v_tax_total
2453 WHERE rma_line_id = pr_order_line.line_id;
2454
2455 -- need to process copying taxes from referenced SO line for non-shippable RMA line whose delivery_detail_id is NULL
2456 ELSIF NVL(pr_order_line.SHIPPABLE_FLAG, 'Y') = 'N' THEN
2457 -- should check VAT return date, if the validation fails, then vat amounts should be copied as zero.
2458 OPEN c_fulfilled_date;
2459 FETCH c_fulfilled_date
2460 INTO v_date_confirmed;
2461 CLOSE c_fulfilled_date;
2462
2463 IF (v_vat_return_days IS NULL OR
2464 (v_date_ordered - v_date_confirmed) <=
2465 v_vat_return_days) THEN
2466 v_vat_flag := 'Y';
2467 ELSE
2468 v_vat_flag := 'N';
2469 END IF; -- v_vat_return_days IS NULL OR .....
2470
2471 IF pr_order_line.ORDERED_QUANTITY <> 0 THEN
2472 FOR tax_line_rec IN (SELECT tax_line_no,
2473 precedence_1,
2474 precedence_2,
2475 precedence_3,
2476 precedence_4,
2477 precedence_5,
2478 sptl.tax_id,
2479 sptl.tax_rate,
2480 sptl.qty_rate,
2481 uom,
2482 sptl.tax_amount,
2483 nvl(jtc.rounding_factor, 0) rounding_factor,
2484 base_tax_amount,
2485 func_tax_amount,
2486 jtc.tax_type,
2487 precedence_6,
2488 precedence_7,
2489 precedence_8,
2490 precedence_9,
2491 precedence_10
2492 FROM JAI_OM_WSH_LINE_TAXES sptl,
2493 JAI_CMN_TAXES_ALL jtc
2494 WHERE order_line_id =
2495 pr_order_line.reference_line_id
2496 AND jtc.tax_id = sptl.tax_id) LOOP
2497 lv_check_vat_type_exists := NULL;
2498
2499 OPEN c_check_Vat_type_Tax_exists(tax_line_rec.tax_type);
2500 FETCH c_check_Vat_type_Tax_exists
2501 INTO lv_check_vat_type_exists;
2502 CLOSE c_check_Vat_type_Tax_exists;
2503
2504 IF (lv_check_vat_type_exists = 1 AND v_vat_flag = 'N') THEN
2505 v_round_tax := 0;
2506 v_round_base := 0;
2507 v_round_func := 0;
2508 ELSE
2509 v_round_tax := tax_line_rec.tax_amount;
2510 v_round_base := tax_line_rec.base_tax_amount;
2511 v_round_func := tax_line_rec.func_tax_amount;
2512 END IF; -- lv_check_vat_type_exists = 1 AND v_vat_flag = 'N'
2513
2514 INSERT INTO JAI_OM_OE_RMA_TAXES
2515 (rma_line_id,
2516 delivery_detail_id,
2517 tax_line_no,
2518 precedence_1,
2519 precedence_2,
2520 precedence_3,
2521 precedence_4,
2522 precedence_5,
2523 tax_id,
2524 tax_rate,
2525 qty_rate,
2526 uom,
2527 tax_amount,
2528 base_tax_amount,
2529 func_tax_amount,
2530 creation_date,
2531 created_by,
2532 last_update_date,
2533 last_updated_by,
2534 last_update_login,
2535 precedence_6,
2536 precedence_7,
2537 precedence_8,
2538 precedence_9,
2539 precedence_10)
2540 VALUES
2541 (pr_order_line.line_id,
2542 NULL,
2543 tax_line_rec.tax_line_no,
2544 tax_line_rec.precedence_1,
2545 tax_line_rec.precedence_2,
2546 tax_line_rec.precedence_3,
2547 tax_line_rec.precedence_4,
2548 tax_line_rec.precedence_5,
2549 tax_line_rec.tax_id,
2550 tax_line_rec.tax_rate,
2551 tax_line_rec.qty_rate,
2552 tax_line_rec.uom,
2553 v_round_tax,
2554 v_round_base,
2555 v_round_func,
2556 pr_order_line.creation_date,
2557 pr_order_line.created_by,
2558 pr_order_line.last_update_date,
2559 pr_order_line.last_updated_by,
2560 pr_order_line.last_update_login,
2561 tax_line_rec.precedence_6,
2562 tax_line_rec.precedence_7,
2563 tax_line_rec.precedence_8,
2564 tax_line_rec.precedence_9,
2565 tax_line_rec.precedence_10);
2566 END LOOP; -- tax_line_rec IN (SELECT tax_line_no ......
2567 END IF; --IF pr_order_line.ORDERED_QUANTITY <> 0
2568
2569 END IF; --IF v_delivery_detail_id IS NOT NULL
2570
2571 ELSE
2572 -- Details in picking lines not found . Raise an error message
2573 CLOSE cur_get_picking_lines;
2574
2575 px_return_code := jai_constants.expected_error;
2576 px_return_message := 'No data found in localisation shipping tables,hence copy cannot be done';
2577 return;
2578 END IF;
2579 CLOSE cur_get_picking_lines;
2580
2581 end if; --endif return_context<>'LEGACY'
2582
2583 EXCEPTION
2584 WHEN OTHERS THEN
2585 RAISE;
2586 END COPY_MIXED_TO_RETURN;
2587
2588 /*
2589 REM +======================================================================+
2590 REM NAME COPY_SOURCE_ORDER
2591 REM
2592 REM DESCRIPTION COPY ORDER LINES FROM THE SOURCE ORDER
2593 REM
2594 REM NOTES Call from procdure POPULATE_TAX
2595 REM
2596 REM +======================================================================+
2597 */
2598 PROCEDURE COPY_SOURCE_ORDER (p_header_info get_header_info%rowtype,
2599 pr_new_line t_rec%type,
2600 p_return_code out nocopy varchar2,
2601 p_return_message out nocopy varchar2) IS
2602
2603 v_api_name CONSTANT VARCHAR2(30) := 'COPY_SOURCE_ORDER';
2604
2605 CURSOR source_order_doc_cur(P_Source_Document_Id NUMBER) IS
2606 SELECT order_category_code
2607 FROM oe_order_headers_all
2608 WHERE header_id = p_source_document_id;
2609
2610 copy_rec get_copy_order_line%ROWTYPE;
2611 v_source_order_category_code VARCHAR2(30);
2612 v_line_category_code VARCHAR2(30);
2613
2614 BEGIN
2615 v_line_category_code := pr_new_line.Line_Category_Code;
2616 -- Copy Order
2617 OPEN Get_Copy_Order_Line(pr_new_line.SOURCE_DOCUMENT_ID,
2618 pr_new_line.SOURCE_DOCUMENT_LINE_ID);
2619 FETCH Get_Copy_Order_Line
2620 INTO copy_rec;
2621
2622 IF Get_Copy_Order_Line%NOTFOUND THEN
2623 -- source order line does not exist in JAI_OM_OE_SO_LINES , should not process
2624 -- this could be because of quantity being 0 / cancelled line.
2625
2626 IF v_line_category_code = 'ORDER' THEN
2627 CLOSE get_copy_order_line;
2628 RETURN;
2629 END IF;
2630 END IF;
2631 CLOSE get_copy_order_line;
2632
2633 OPEN source_order_doc_cur(pr_new_line.SOURCE_DOCUMENT_ID);
2634 FETCH source_order_doc_cur
2635 INTO v_source_order_category_code;
2636 CLOSE source_order_doc_cur;
2637
2638 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2639 FND_LOG.STRING(G_LEVEL_STATEMENT,
2640 G_MODULE_NAME || v_api_name,
2641 ' Order header category code ~ Line category code :' ||
2642 v_source_order_category_code || ' ~ ' ||
2643 v_line_category_code);
2644 END IF;
2645
2646 /*
2647 Made the check more explicit as functionality in case of order to order and return to order is different.
2648 */
2649 IF v_source_order_category_code = 'ORDER' AND
2650 v_line_category_code = 'ORDER' THEN
2651 -- copy order lines from original order
2652 copy_order_to_order(pr_copy => copy_rec,
2653 pr_order_line => pr_new_line,
2654 pr_header_info => p_header_info);
2655
2656 ELSIF v_source_order_category_code = 'RETURN' AND
2657 v_line_category_code = 'ORDER' THEN
2658
2659 -- copy order lines from return order
2660 copy_return_to_order(pr_order_line => pr_new_line,
2661 pr_header_info => p_header_info);
2662
2663 ELSIF v_source_order_category_code = 'ORDER' AND
2664 v_line_category_code = 'RETURN' THEN
2665 -- copy return taxes and lines from original order
2666 copy_order_to_return(pr_order_line => pr_new_line,
2667 p_order_number => p_header_info.order_number,
2668 px_return_code => p_return_code,
2669 px_return_message => p_return_message);
2670 if (p_return_code <> jai_constants.successful) then
2671 return;
2672 end if;
2673
2674 ELSIF rtrim(ltrim(v_source_order_category_code)) = 'MIXED' AND
2675 ltrim(rtrim(v_line_category_code)) = 'RETURN' THEN
2676 -- copy return taxes and lines from original mixed order
2677 copy_mixed_to_return(pr_order_line => pr_new_line,
2678 p_order_number => p_header_info.order_number,
2679 px_return_code => p_return_code,
2680 px_return_message => p_return_message);
2681 if (p_return_code <> jai_constants.successful) then
2682 return;
2683 end if;
2684
2685 ELSIF v_source_order_category_code = 'RETURN' AND
2686 v_line_category_code = 'RETURN' THEN
2687 -- Raise an Error
2688 p_return_code := jai_constants.expected_error;
2689 p_return_message := 'Copying of Return Order to Return Order is not currently supported with India Localization Taxes';
2690 return;
2691 END IF;
2692 EXCEPTION
2693 WHEN OTHERS THEN
2694 RAISE;
2695 END COPY_SOURCE_ORDER;
2696
2697 /*
2698 REM +======================================================================+
2699 REM NAME CREATE_NORMAL_ORDER
2700 REM
2701 REM DESCRIPTION CREATE NORMAL ORDER LINES
2702 REM
2703 REM NOTES Call from procdure POPULATE_TAX
2704 REM
2705 REM +======================================================================+
2706 */
2707 PROCEDURE CREATE_NORMAL_ORDER (pr_order_line t_rec%type,
2708 pr_old_line t_rec%type,
2709 pr_header_info get_header_info%rowtype) IS
2710
2711 v_api_name CONSTANT VARCHAR2(30) := 'CREATE_NORMAL_ORDER';
2712
2713 CURSOR get_source_id IS
2714 SELECT order_source_id
2715 FROM oe_order_headers_all
2716 WHERE header_id = pr_order_line.header_id;
2717
2718 CURSOR get_rma_tax_lines_count_cur IS
2719 SELECT COUNT(1)
2720 FROM JAI_OM_OE_RMA_TAXES
2721 WHERE rma_line_id = pr_order_line.line_id;
2722
2723 CURSOR get_so_tax_lines_count_cur(p_header_id NUMBER, p_line_id NUMBER) IS
2724 SELECT COUNT(1)
2725 FROM JAI_OM_OE_SO_TAXES
2726 WHERE header_id = p_header_id
2727 AND line_id = p_line_id;
2728
2729 CURSOR get_rma_lines_count_cur(p_line_id NUMBER) IS
2730 SELECT COUNT(1)
2731 FROM JAI_OM_OE_RMA_LINES
2732 WHERE rma_line_id = pr_order_line.line_id;
2733
2734 CURSOR get_so_lines_count_cur(p_line_id NUMBER) IS
2735 SELECT COUNT(1)
2736 FROM JAI_OM_OE_SO_LINES
2737 WHERE line_id = p_line_id;
2738
2739 CURSOR return_tax_amount_Cur(p_header_id NUMBER, p_line_id NUMBER) IS
2740 SELECT SUM(A.tax_amount)
2741 FROM JAI_OM_OE_RMA_TAXES a, JAI_CMN_TAXES_ALL b
2742 WHERE a.rma_line_id = p_line_id
2743 AND b.tax_id = A.tax_id
2744 AND b.tax_type <> jai_constants.tax_type_tds /* 'TDS'; Ramananda for removal of SQL LITERALs */
2745 AND NVL(b.inclusive_tax_flag, 'N') = 'N';
2746
2747 CURSOR order_tax_amount_Cur(p_header_id NUMBER, p_line_id NUMBER) IS
2748 SELECT SUM(A.tax_amount)
2749 FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
2750 WHERE A.Header_ID = p_header_id
2751 AND A.line_id = p_line_id
2752 AND b.tax_id = A.tax_id
2753 AND b.tax_type <> jai_constants.tax_type_tds
2754 AND NVL(b.inclusive_tax_flag, 'N') = 'N';
2755
2756 v_transaction_name VARCHAR2(30);
2757 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2758 v_set_of_books_id NUMBER;
2759 v_converted_rate NUMBER;
2760 v_assessable_value NUMBER;
2761 v_source_id NUMBER;
2762 v_Line_Category_Code VARCHAR2(30);
2763 v_tax_category_id NUMBER;
2764 v_line_tax_amount NUMBER;
2765 v_line_amount NUMBER;
2766 v_assessable_amount NUMBER;
2767 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
2768 v_so_tax_lines_count NUMBER;
2769 v_shipment_schedule_line_id NUMBER;
2770 v_so_lines_count NUMBER;
2771 c_source_line_id NUMBER;
2772 v_service_type_code varchar2(30);
2773
2774 BEGIN
2775
2776 v_transaction_name := 'SALES_ORDER';
2777 v_Line_Category_Code := pr_order_line.Line_Category_Code;
2778 v_service_type_code := JAI_AR_RCTLA_TRIGGER_PKG.get_service_type(pr_header_info.customer_id,
2779 NVL(pr_order_line.ship_to_ORG_id, 0),
2780 'C');
2781 v_line_amount := (NVL(pr_order_line.ordered_quantity, 0) * NVL(pr_order_line.UNIT_SELLING_PRICE, 0));
2782 v_line_tax_amount := 0;
2783
2784 /*
2785 Issue:-
2786 If an RMA order is created having a return_context as null then the record gets
2787 inserted into the JAI_OM_OE_SO_LINES table.
2788 Even though a rma line is not having the return_context field still the line should be treated as
2789 RMA and not as a sales order line.
2790
2791 Solution:-
2792 Added an NVL clause to the below IF statement .
2793 Now even if the Return_context is null it would be treated as = LEGACY
2794 and the v_transaction_name flag would be set to LEGACY.
2795 Due to this the record would be inserted into the JAI_OM_OE_RMA_LINES table instead of the
2796 JAI_OM_OE_SO_LINES table.
2797 */
2798 IF pr_order_line.LINE_CATEGORY_CODE = 'RETURN' THEN
2799 IF pr_order_line.return_context IS NULL THEN
2800 v_transaction_name := 'RMA_LEGACY_INSERT';
2801 ELSE
2802 RETURN;
2803 END IF;
2804 END IF;
2805
2806 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2807 FND_LOG.STRING(G_LEVEL_STATEMENT,
2808 G_MODULE_NAME || v_api_name,
2809 ' v_transaction_name ' || v_transaction_name);
2810 END IF;
2811 -- Else if the line is a fresh line , Unsplitted
2812 /*
2813 Removed the cursor set_of_books_cur and added the following check
2814 using plsql caching for performance issues reported.
2815 */
2816
2817 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => pr_order_line.ORG_ID);
2818
2819 v_set_of_books_id := l_func_curr_det.ledger_id;
2820
2821 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_set_of_books_id,
2822 pr_header_info.currency_code,
2823 pr_header_info.conv_date,
2824 pr_header_info.conv_type_code,
2825 pr_header_info.conv_rate);
2826 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value(p_customer_id => pr_header_info.customer_id,
2827 p_ship_to_site_use_id => NVL(pr_order_line.ship_to_ORG_id, 0),
2828 p_inventory_item_id => pr_order_line.inventory_item_id,
2829 p_uom_code => pr_order_line.ORDER_QUANTITY_UOM,
2830 p_default_price => pr_order_line.unit_selling_price,
2831 p_ass_value_date => pr_header_info.date_ordered,
2832 p_sob_id => v_set_of_books_id,
2833 p_curr_conv_code => pr_header_info.conv_type_code,
2834 p_conv_rate => v_converted_rate);
2835
2836 v_assessable_amount := NVL(v_assessable_value, 0) * NVL(pr_order_line.ordered_quantity, 0);
2837
2838 ln_vat_assessable_value := jai_general_pkg.JA_IN_VAT_ASSESSABLE_VALUE(P_PARTY_ID => pr_header_info.customer_id,
2839 P_PARTY_SITE_ID => NVL(pr_order_line.ship_to_ORG_id, 0),
2840 P_INVENTORY_ITEM_ID => pr_order_line.inventory_item_id,
2841 P_UOM_CODE => pr_order_line.ORDER_QUANTITY_UOM,
2842 P_DEFAULT_PRICE => pr_order_line.unit_selling_price,
2843 P_ASS_VALUE_DATE => pr_header_info.date_ordered,
2844 P_PARTY_TYPE => 'C');
2845
2846 ln_vat_assessable_value := nvl(ln_vat_assessable_value, 0) * NVL(pr_order_line.ordered_quantity, 0);
2847
2848 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2849 FND_LOG.STRING(G_LEVEL_STATEMENT,
2850 G_MODULE_NAME || v_api_name,
2851 ' v_assessable_value' || v_assessable_value);
2852 END IF;
2853
2854 OPEN get_source_id;
2855 FETCH get_source_id
2856 INTO v_source_id;
2857 CLOSE get_source_id;
2858
2859 IF ((v_line_category_code = 'ORDER') OR
2860 (v_transaction_name = 'RMA_LEGACY_INSERT')) THEN
2861
2862 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2863 FND_LOG.STRING(G_LEVEL_STATEMENT,
2864 G_MODULE_NAME || v_api_name,
2865 ' inside IF OF v_line_category_code IN (ORDER) OR v_transaction_name = RMA_LEGACY_INSERT');
2866 END IF;
2867
2868 IF NVL(pr_order_line.ship_to_ORG_id, 0) <> NVL(pr_old_line.ship_to_ORG_id, 0) THEN
2869 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2870 FND_LOG.STRING(G_LEVEL_STATEMENT,
2871 G_MODULE_NAME || v_api_name,
2872 ' BEFORE DELETING WHEN ship TO org has changed');
2873 END IF;
2874
2875 IF (v_transaction_name = 'RMA_LEGACY_INSERT') THEN
2876
2877 DELETE JAI_OM_OE_RMA_LINES WHERE RMA_LINE_ID = pr_order_line.line_id;
2878 DELETE JAI_OM_OE_RMA_TAXES WHERE RMA_LINE_ID = pr_order_line.line_id;
2879 ELSE
2880 DELETE JAI_OM_OE_SO_LINES WHERE LINE_ID = pr_order_line.line_id;
2881 DELETE JAI_OM_OE_SO_TAXES WHERE Line_ID = pr_order_line.line_id;
2882 END IF;
2883 END IF;
2884
2885 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2886 FND_LOG.STRING(G_LEVEL_STATEMENT,
2887 G_MODULE_NAME || v_api_name,
2888 ' BEFORE calling jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes ');
2889 END IF;
2890
2891 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2892 FND_LOG.STRING(G_LEVEL_STATEMENT,
2893 G_MODULE_NAME || v_api_name,
2894 ' Before calling jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes:' ||
2895 ' pr_order_line.SHIP_FROM_ORG_ID:' || pr_order_line.SHIP_FROM_ORG_ID ||
2896 ' customer_id: ' || pr_header_info.customer_id ||
2897 ' ship_to_site_use_id:' || pr_order_line.ship_to_ORG_id ||
2898 ' pr_order_line.inventory_item_id:' || pr_order_line.inventory_item_id ||
2899 ' pr_order_line.header_id:' || pr_order_line.header_id ||
2900 ' pr_order_line.line_id:' || pr_order_line.line_id);
2901 END IF;
2902
2903 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes(pr_order_line.SHIP_FROM_ORG_ID,
2904 pr_header_info.customer_id,
2905 NVL(pr_order_line.ship_to_ORG_id, 0),
2906 pr_order_line.inventory_item_id,
2907 pr_order_line.header_id,
2908 pr_order_line.line_id,
2909 v_tax_category_id);
2910 IF v_tax_category_id IS NULL THEN
2911
2912 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2913 FND_LOG.STRING(G_LEVEL_STATEMENT,
2914 G_MODULE_NAME || v_api_name,
2915 ' BEFORE calling jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes ');
2916 END IF;
2917
2918 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2919 FND_LOG.STRING(G_LEVEL_STATEMENT,
2920 G_MODULE_NAME || v_api_name,
2921 ' Tax category id is null, then call jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes:' ||
2922 ' pr_order_line.SHIP_FROM_ORG_ID:' || pr_order_line.SHIP_FROM_ORG_ID ||
2923 ' pr_order_line.inventory_item_id: ' ||
2924 pr_order_line.inventory_item_id);
2925 END IF;
2926 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes(pr_order_line.SHIP_FROM_ORG_ID,
2927 pr_order_line.inventory_item_id,
2928 v_tax_category_id);
2929
2930 ELSE
2931 /* elsif of v_tax_category_id IS NULL */
2932 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2933 FND_LOG.STRING(G_LEVEL_STATEMENT,
2934 G_MODULE_NAME || v_api_name,
2935 ' BEFORE setting v_line_tax_amount := p_line_amount ');
2936 END IF;
2937 v_line_tax_amount := v_line_amount;
2938 END IF;
2939
2940 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
2941
2942 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2943 FND_LOG.STRING(G_LEVEL_STATEMENT,
2944 G_MODULE_NAME || v_api_name,
2945 ' inside IF OF RMA_LEGACY_INSERT ');
2946 END IF;
2947
2948 OPEN get_rma_tax_lines_count_cur;
2949 FETCH get_rma_tax_lines_count_cur
2950 INTO v_so_tax_lines_count;
2951 CLOSE get_rma_tax_lines_count_cur;
2952
2953 ELSE
2954 --14
2955 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2956 FND_LOG.STRING(G_LEVEL_STATEMENT,
2957 G_MODULE_NAME || v_api_name,
2958 ' inside ELSE OF RMA_LEGACY_INSERT ');
2959 END IF;
2960
2961 OPEN get_so_tax_lines_count_cur(pr_order_line.header_id, pr_order_line.line_id);
2962 FETCH get_so_tax_lines_count_cur
2963 INTO v_so_tax_lines_count;
2964 CLOSE get_so_tax_lines_count_cur;
2965 END IF;
2966
2967 IF v_so_tax_lines_count = 0 THEN
2968
2969 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2970 FND_LOG.STRING(G_LEVEL_STATEMENT,
2971 G_MODULE_NAME || v_api_name,
2972 ' inside IF OF v_so_tax_lines_count = 0 ');
2973 END IF;
2974
2975 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2976 FND_LOG.STRING(G_LEVEL_STATEMENT,
2977 G_MODULE_NAME || v_api_name,
2978 ' If v_so_tax_lines_count = 0, then call jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes:' ||
2979 ' p_transaction_name:' || v_transaction_name ||
2980 ' p_tax_category_id:' || v_tax_category_id ||
2981 ' p_header_id:' || pr_order_line.header_id ||
2982 ' p_line_id:' || pr_order_line.line_id ||
2983 ' p_assessable_value:' || v_assessable_value ||
2984 ' p_line_tax_amount:' || v_line_tax_amount ||
2985 ' p_inventory_item_id:' || pr_order_line.inventory_item_id ||
2986 ' p_line_quantity:' || pr_order_line.ordered_quantity ||
2987 ' p_uom_code:' || pr_order_line.ORDER_QUANTITY_UOM);
2988 END IF;
2989 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes(transaction_name => v_transaction_name,
2990 p_tax_category_id => v_tax_category_id,
2991 p_header_id => pr_order_line.header_id,
2992 p_line_id => pr_order_line.line_id,
2993 p_assessable_value => v_assessable_amount,
2994 p_tax_amount => v_line_tax_amount,
2995 p_inventory_item_id => pr_order_line.inventory_item_id,
2996 p_line_quantity => NVL(pr_order_line.ordered_quantity, 0),
2997 p_uom_code => pr_order_line.ORDER_QUANTITY_UOM,
2998 p_vendor_id => '',
2999 p_currency => '',
3000 p_currency_conv_factor => v_converted_rate,
3001 p_creation_date => pr_order_line.creation_date,
3002 p_created_by => pr_order_line.created_by,
3003 p_last_update_date => pr_order_line.last_update_date,
3004 p_last_updated_by => pr_order_line.last_updated_by,
3005 p_last_update_login => pr_order_line.last_update_login,
3006 p_operation_flag => NULL,
3007 p_vat_assessable_value => ln_vat_assessable_value
3008 );
3009
3010 END IF; -- v_so_tax_lines_count = 0 THEN
3011 END IF; -- v_line_category_code IN ('ORDER') THEN
3012
3013 IF V_SHIPMENT_SCHEDULE_LINE_ID IS NULL THEN
3014
3015 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3016 FND_LOG.STRING(G_LEVEL_STATEMENT,
3017 G_MODULE_NAME || v_api_name,
3018 ' inside IF OF V_SHIPMENT_SCHEDULE_LINE_ID IS NULL ');
3019 END IF;
3020
3021 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3022 OPEN get_rma_lines_count_cur(pr_order_line.line_id);
3023 FETCH get_rma_lines_count_cur
3024 INTO v_so_lines_count;
3025 CLOSE get_rma_lines_count_cur;
3026 ELSE
3027 OPEN get_so_lines_count_cur(pr_order_line.line_id);
3028 FETCH get_so_lines_count_cur
3029 INTO v_so_lines_count;
3030 CLOSE get_so_lines_count_cur;
3031 END IF;
3032
3033 IF v_so_lines_count = 0 THEN
3034
3035 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3036 FND_LOG.STRING(G_LEVEL_STATEMENT,
3037 G_MODULE_NAME || v_api_name,
3038 ' inside IF OF v_so_lines_count = 0 ');
3039 END IF;
3040 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3041
3042 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3043 FND_LOG.STRING(G_LEVEL_STATEMENT,
3044 G_MODULE_NAME || v_api_name,
3045 'BEFORE opening return_tax_amount_Cur ');
3046 END IF;
3047 OPEN return_tax_amount_Cur(pr_order_line.header_id, pr_order_line.LINE_ID);
3048 FETCH return_tax_amount_Cur
3049 INTO v_line_tax_amount;
3050 CLOSE return_tax_amount_Cur;
3051 ELSE
3052 OPEN order_tax_amount_Cur(pr_order_line.header_id, pr_order_line.LINE_ID);
3053 FETCH order_tax_amount_Cur
3054 INTO v_line_tax_amount;
3055 CLOSE order_tax_amount_Cur;
3056 END IF;
3057
3058 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3059 FND_LOG.STRING(G_LEVEL_STATEMENT,
3060 G_MODULE_NAME || v_api_name,
3061 ' Total tax : ' || v_line_tax_amount);
3062 END IF;
3063
3064 IF v_transaction_name = 'RMA_LEGACY_INSERT' THEN
3065
3066 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3067 FND_LOG.STRING(G_LEVEL_STATEMENT,
3068 G_MODULE_NAME || v_api_name,
3069 ' BEFORE INSERTING RECORD INTO JAI_OM_OE_RMA_LINES ');
3070 END IF;
3071 INSERT INTO JAI_OM_OE_RMA_LINES
3072 (rma_line_number,
3073 rma_line_id,
3074 rma_header_id,
3075 rma_number,
3076 inventory_item_id,
3077 uom,
3078 quantity,
3079 tax_category_id,
3080 selling_price,
3081 tax_amount,
3082 creation_date,
3083 created_by,
3084 last_update_date,
3085 last_updated_by,
3086 last_update_login,
3087 assessable_value,
3088 ship_to_org_id)
3089 VALUES
3090 (pr_order_line.line_number,
3091 pr_order_line.line_id,
3092 pr_order_line.header_id,
3093 pr_header_info.order_number,
3094 pr_order_line.inventory_item_id,
3095 pr_order_line.ORDER_QUANTITY_UOM,
3096 pr_order_line.ordered_quantity,
3097 v_tax_category_id,
3098 pr_order_line.UNIT_SELLING_PRICE,
3099 v_line_tax_amount,
3100 pr_order_line.creation_date,
3101 pr_order_line.created_by,
3102 pr_order_line.last_update_date,
3103 pr_order_line.last_updated_by,
3104 pr_order_line.last_update_login,
3105 v_assessable_value,
3106 pr_order_line.ship_to_org_id);
3107 ELSE
3108 /* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
3109 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3110 FND_LOG.STRING(G_LEVEL_STATEMENT,
3111 G_MODULE_NAME || v_api_name,
3112 ' BEFORE INSERTING RECORD INTO JAI_OM_OE_SO_LINES ');
3113 END IF;
3114
3115 c_source_line_id := 0;
3116 SELECT COUNT(*)
3117 INTO c_source_line_id
3118 FROM JAI_OM_OE_SO_LINES
3119 WHERE LINE_ID = pr_order_line.line_id;
3120 IF c_source_line_id = 0 THEN
3121 INSERT INTO JAI_OM_OE_SO_LINES
3122 (line_number,
3123 line_id,
3124 header_id,
3125 SHIPMENT_LINE_NUMBER,
3126 shipment_schedule_line_id, -- uncommented by sriram - for lmw ato issue
3127 inventory_item_id,
3128 unit_code,
3129 ato_flag,
3130 quantity,
3131 tax_category_id,
3132 selling_price,
3133 assessable_value,
3134 line_amount,
3135 tax_amount,
3136 line_tot_amount,
3137 creation_date,
3138 created_by,
3139 last_update_date,
3140 last_updated_by,
3141 last_update_login,
3142 vat_assessable_value,
3143 service_type_code,
3144 ship_to_org_id)
3145 VALUES
3146 (pr_order_line.line_number,
3147 pr_order_line.line_id,
3148 pr_order_line.header_id,
3149 pr_order_line.SHIPMENT_NUMBER,
3150 pr_order_line.ato_line_id, -- uncommented by sriram - for lmw ato issue
3151 pr_order_line.inventory_item_id,
3152 pr_order_line.ORDER_QUANTITY_UOM,
3153 'Y',
3154 pr_order_line.ordered_quantity,
3155 v_tax_category_id,
3156 pr_order_line.UNIT_SELLING_PRICE,
3157 v_assessable_value,
3158 v_line_amount,
3159 v_line_tax_amount,
3160 (v_line_amount + v_line_tax_amount),
3161 pr_order_line.creation_date,
3162 pr_order_line.created_by,
3163 pr_order_line.last_update_date,
3164 pr_order_line.last_updated_by,
3165 pr_order_line.last_update_login,
3166 ln_vat_assessable_value,
3167 v_service_type_code,
3168 pr_order_line.ship_to_org_id);
3169 END IF;
3170 END IF; --
3171
3172 END IF; -- IF v_so_lines_count = 0
3173
3174 END IF; -- V_SHIPMENT_SCHEDULE_LINE_ID
3175 EXCEPTION
3176 WHEN OTHERS THEN
3177 RAISE;
3178 END CREATE_NORMAL_ORDER;
3179
3180 /*
3181 REM +======================================================================+
3182 REM NAME PROCESS_CONFIG
3183 REM
3184 REM DESCRIPTION PROCESS ATO CONFIG
3185 REM
3186 REM NOTES Call from procdure POPULATE_TAX
3187 REM
3188 REM +======================================================================+
3189 */
3190 PROCEDURE PROCESS_CONFIG (pr_order_line t_rec%type) IS
3191
3192 v_api_name CONSTANT VARCHAR2(30) := 'PROCESS_CONFIG';
3193
3194 cursor c_model_taxes is
3195 select *
3196 from JAI_OM_OE_SO_TAXES
3197 where header_id = pr_order_line.header_id
3198 and line_id = pr_order_line.ato_line_id;
3199
3200 cursor c_model_tax_Categ is
3201 select tax_category_id, inventory_item_id, line_amount
3202 from JAI_OM_OE_SO_LINES
3203 where header_id = pr_order_line.header_id
3204 and line_id = pr_order_line.ato_line_id;
3205
3206 v_output_tax_amount Number;
3207 v_tax_category Number;
3208 v_ato_inventory_item_id Number;
3209 v_ato_line_amount Number;
3210 v_ato_tax_amount Number;
3211 v_ato_selling_price Number;
3212 v_ato_vat_assessable_value NUMBER;
3213 v_ato_assessable_value Number;
3214 v_converted_rate NUMBER;
3215
3216 -- code segment added by sriram - LMW ATO
3217 procedure calc_price_tax_for_config_item is
3218 cursor c_get_line_tax_amt is
3219 select line_amount,
3220 tax_amount,
3221 selling_price,
3222 assessable_value,
3223 quantity,
3224 vat_assessable_value
3225 from JAI_OM_OE_SO_LINES
3226 where header_id = pr_order_line.header_id
3227 and shipment_schedule_line_id = pr_order_line.ato_line_id;
3228
3229 -- the last where clause handles the case where there are multiple config items in the single order
3230
3231 begin
3232
3233 For so_lines_rec in c_get_line_tax_amt Loop
3234 v_ato_line_amount := NVL(v_ato_line_amount, 0) +
3235 NVL(so_lines_rec.line_amount, 0);
3236 v_ato_tax_amount := NVL(v_ato_tax_amount, 0) +
3237 NVL(so_lines_rec.tax_amount, 0);
3238 v_ato_selling_price := NVL(v_ato_selling_price, 0) +
3239 ((NVL(so_lines_rec.selling_price, 0) *
3240 so_lines_rec.quantity) /
3241 pr_order_line.ordered_quantity);
3242 v_ato_assessable_value := NVL(v_ato_assessable_value, 0) +
3243 ((NVL(so_lines_rec.assessable_value,
3244 so_lines_rec.selling_price) *
3245 so_lines_rec.quantity) /
3246 pr_order_line.ordered_quantity);
3247
3248 v_ato_vat_assessable_value := NVL(v_ato_vat_assessable_value, 0) +
3249 ((NVL(so_lines_rec.vat_assessable_value,
3250 so_lines_rec.selling_price) *
3251 so_lines_rec.quantity) /
3252 pr_order_line.ordered_quantity);
3253 end loop;
3254
3255 end;
3256
3257 BEGIN
3258 calc_price_tax_for_config_item;
3259
3260 update JAI_OM_OE_SO_LINES
3261 set line_amount = v_ato_line_amount,
3262 assessable_value = v_ato_assessable_value,
3263 vat_assessable_value = v_ato_vat_assessable_value,
3264 selling_price = v_ato_selling_price
3265 where header_id = pr_order_line.header_id
3266 and line_id = pr_order_line.line_id;
3267
3268 -- copy the taxes of model item into config item
3269 open c_model_tax_Categ;
3270 Fetch c_model_tax_Categ
3271 into v_tax_category,
3272 v_ato_inventory_item_id,
3273 v_output_tax_amount;
3274 close c_model_tax_Categ;
3275
3276 For model_rec in c_model_taxes Loop
3277
3278 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3279 FND_LOG.STRING(G_LEVEL_STATEMENT,
3280 G_MODULE_NAME || v_api_name,
3281 'In the Loop Header ID ~ Line ID :' ||
3282 TO_CHAR(pr_order_line.header_id) || ' ~ ' ||
3283 TO_CHAR(pr_order_line.line_id));
3284 END IF;
3285
3286 Insert into JAI_OM_OE_SO_TAXES
3287 (tax_line_no,
3288 line_id,
3289 header_id,
3290 precedence_1,
3291 precedence_2,
3292 precedence_3,
3293 precedence_4,
3294 precedence_5,
3295 tax_id,
3296 tax_rate,
3297 qty_rate,
3298 uom,
3299 tax_amount,
3300 base_tax_amount,
3301 func_tax_amount,
3302 creation_date,
3303 created_by,
3304 last_update_date,
3305 last_updated_by,
3306 last_update_login,
3307 tax_category_id,
3308 precedence_6,
3309 precedence_7,
3310 precedence_8,
3311 precedence_9,
3312 precedence_10)
3313 Values
3314 (model_rec.tax_line_no,
3315 pr_order_line.line_id,
3316 pr_order_line.header_id,
3317 model_rec.precedence_1,
3318 model_rec.precedence_2,
3319 model_rec.precedence_3,
3320 model_rec.precedence_4,
3321 model_rec.precedence_5,
3322 model_rec.tax_id,
3323 model_rec.tax_rate,
3324 model_rec.qty_rate,
3325 model_rec.uom,
3326 model_rec.tax_amount,
3327 model_rec.base_tax_amount,
3328 model_rec.func_tax_amount,
3329 model_rec.creation_date,
3330 model_rec.created_by,
3331 model_rec.last_update_date,
3332 model_rec.last_updated_by,
3333 model_rec.last_update_login,
3334 model_rec.tax_category_id,
3335 model_rec.precedence_6,
3336 model_rec.precedence_7,
3337 model_rec.precedence_8,
3338 model_rec.precedence_9,
3339 model_rec.precedence_10);
3340
3341 End Loop;
3342 -- to recalculate taxes
3343
3344 v_ato_assessable_value := NVL(v_ato_assessable_value, 0) *
3345 NVL(pr_order_line.ordered_quantity, 0);
3346
3347 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE',
3348 NULL,
3349 pr_order_line.header_id,
3350 pr_order_line.line_id,
3351 v_ato_assessable_value,
3352 v_ato_line_amount,
3353 v_converted_rate,
3354 pr_order_line.inventory_item_id,
3355 pr_order_line.ordered_quantity,
3356 pr_order_line.ordered_quantity,
3357 pr_order_line.pricing_quantity_uom,
3358 NULL,
3359 NULL,
3360 NULL,
3361 NULL,
3362 pr_order_line.last_update_date,
3363 pr_order_line.last_updated_by,
3364 pr_order_line.last_update_login,
3365 v_ato_vat_assessable_value);
3366
3367 -- update the tax amounts after doing tax recalculation .
3368 update JAI_OM_OE_SO_LINES
3369 set tax_amount = NVL(v_ato_line_amount, 0),
3370 line_tot_amount = line_amount + NVL(v_ato_line_amount, 0)
3371 where header_id = pr_order_line.header_id
3372 and line_id = pr_order_line.line_id;
3373
3374
3375 EXCEPTION
3376 WHEN OTHERS THEN
3377 RAISE;
3378 END PROCESS_CONFIG;
3379
3380 /*
3381 REM +======================================================================+
3382 REM NAME COPY_SPLIT_LINE
3383 REM
3384 REM DESCRIPTION COPY FROM ORIGINAL LINES
3385 REM
3386 REM NOTES Call from procdure POPULATE_TAX
3387 REM
3388 REM +======================================================================+
3389 */
3390 PROCEDURE COPY_SPLIT_LINE(pr_order_line t_rec%type) IS
3391
3392 v_api_name CONSTANT VARCHAR2(30) := 'COPY_SPLIT_LINE';
3393
3394 CURSOR rec_get_lc_flag IS
3395 SELECT lc_flag
3396 FROM JAI_OM_OE_SO_LINES
3397 WHERE line_id = pr_order_line.split_from_line_id;
3398
3399 CURSOR get_so_tax_lines_count_cur(p_header_id NUMBER, p_line_id NUMBER) IS
3400 SELECT COUNT(1)
3401 FROM JAI_OM_OE_SO_TAXES
3402 WHERE header_id = p_header_id
3403 AND line_id = p_line_id;
3404
3405 CURSOR so_tax_lines_cur(p_header_id NUMBER, p_line_id NUMBER) IS
3406 SELECT tax_line_no,
3407 tax_id,
3408 tax_rate,
3409 qty_rate,
3410 uom,
3411 precedence_1,
3412 precedence_2,
3413 precedence_3,
3414 precedence_4,
3415 precedence_5,
3416 precedence_6,
3417 precedence_7,
3418 precedence_8,
3419 precedence_9,
3420 precedence_10,
3421 tax_amount,
3422 base_tax_amount,
3423 func_tax_amount,
3424 tax_category_id
3425 FROM JAI_OM_OE_SO_TAXES
3426 WHERE header_id = p_header_id
3427 AND line_id = p_line_id;
3428
3429 cursor c_fetch_rounding_factor(p_tax_id number) is
3430 select nvl(rounding_factor, 0), nvl(adhoc_flag, 'N')
3431 from JAI_CMN_TAXES_ALL
3432 where tax_id = p_tax_id;
3433
3434 CURSOR order_tax_amount_Cur(p_header_id NUMBER, p_line_id NUMBER) IS
3435 SELECT SUM(A.tax_amount)
3436 FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
3437 WHERE A.Header_ID = p_header_id
3438 AND A.line_id = p_line_id
3439 AND b.tax_id = A.tax_id
3440 AND b.tax_type <> jai_constants.tax_type_tds
3441 AND NVL(b.inclusive_tax_flag, 'N') = 'N';
3442
3443 copy_rec get_copy_order_line%ROWTYPE;
3444 c_source_line_id NUMBER;
3445 v_line_new_amount NUMBER;
3446 v_new_vat_assessable_value NUMBER;
3447 l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
3448 v_old_quantity NUMBER;
3449 v_so_tax_lines_count NUMBER;
3450 v_tax_line_count NUMBER;
3451 v_rounding_factor JAI_CMN_TAXES_ALL.rounding_factor%type;
3452 v_adhoc_flag JAI_CMN_TAXES_ALL.adhoc_flag%type;
3453 v_new_tax_amount NUMBER;
3454 v_new_base_tax_amount NUMBER;
3455 v_header_tax_amount number;
3456 v_new_func_tax_amount NUMBER;
3457 v_line_quantity NUMBER;
3458
3459 BEGIN
3460
3461 c_source_line_id := 0;
3462 v_header_tax_amount := 0;
3463 v_line_quantity := NVL(pr_order_line.ordered_quantity, 0);
3464
3465 SELECT COUNT(*)
3466 INTO c_source_line_id
3467 FROM JAI_OM_OE_SO_LINES
3468 WHERE LINE_ID = pr_order_line.line_id;
3469 IF c_source_line_id = 0 THEN
3470 /*
3471 If the original line from which the new line has been split is lc enabled i.e lc _flag has been checked
3472 the new line should also have the same value for lc_flag.
3473 copy the original value of lc_flag value from the orginal line from where the new line has been split.
3474 */
3475
3476 -- When this is a split line
3477 OPEN Get_Copy_Order_Line(pr_order_line.header_id, pr_order_line.SPLIT_FROM_LINE_ID);
3478 FETCH Get_Copy_Order_Line
3479 INTO copy_rec;
3480 CLOSE Get_Copy_Order_Line;
3481 -- Proportionate the corresponding amount according to the new quantity
3482 v_line_new_amount := (copy_rec.line_amount /
3483 copy_rec.QUANTITY) *
3484 (v_line_quantity);
3485 v_new_vat_assessable_value := (copy_rec.vat_assessable_value /
3486 copy_rec.quantity) *
3487 (v_line_quantity);
3488
3489 OPEN rec_get_lc_flag;
3490 FETCH rec_get_lc_flag
3491 INTO l_lc_flag;
3492 CLOSE rec_get_lc_flag;
3493 INSERT INTO JAI_OM_OE_SO_LINES
3494 (line_number,
3495 line_id,
3496 header_id,
3497 SPLIT_FROM_LINE_ID,
3498 SHIPMENT_LINE_NUMBER,
3499 shipment_schedule_line_id,
3500 inventory_item_id,
3501 unit_code,
3502 ato_flag,
3503 quantity,
3504 tax_category_id,
3505 selling_price,
3506 assessable_value,
3507 line_amount,
3508 tax_amount,
3509 line_tot_amount,
3510 creation_date,
3511 created_by,
3512 last_update_date,
3513 last_updated_by,
3514 last_update_login,
3515 EXCISE_EXEMPT_TYPE,
3516 EXCISE_EXEMPT_REFNO,
3517 EXCISE_EXEMPT_DATE,
3518 lc_flag,
3519 VAT_EXEMPTION_FLAG,
3520 VAT_EXEMPTION_TYPE,
3521 VAT_EXEMPTION_DATE,
3522 VAT_EXEMPTION_REFNO,
3523 VAT_ASSESSABLE_VALUE,
3524 VAT_REVERSAL_PRICE,
3525 service_type_code,
3526 ship_to_org_id)
3527 VALUES
3528 (pr_order_line.line_number,
3529 pr_order_line.line_id,
3530 pr_order_line.header_id,
3531 pr_order_line.SPLIT_FROM_LINE_ID,
3532 pr_order_line.SHIPMENT_NUMBER,
3533 pr_order_line.ato_line_id,
3534 pr_order_line.inventory_item_id,
3535 pr_order_line.ORDER_QUANTITY_UOM,
3536 'Y',
3537 pr_order_line.ordered_quantity,
3538 copy_rec.tax_category_id,
3539 pr_order_line.UNIT_SELLING_PRICE,
3540 copy_rec.assessable_value,
3541 v_line_new_amount,
3542 0,
3543 0,
3544 pr_order_line.creation_date,
3545 pr_order_line.created_by,
3546 pr_order_line.last_update_date,
3547 pr_order_line.last_updated_by,
3548 pr_order_line.last_update_login,
3549 Copy_rec.EXCISE_EXEMPT_TYPE,
3550 copy_rec.EXCISE_EXEMPT_REFNO,
3551 copy_rec.EXCISE_EXEMPT_DATE,
3552 l_lc_flag,
3553 Copy_rec.VAT_EXEMPTION_FLAG,
3554 Copy_rec.VAT_EXEMPTION_TYPE,
3555 Copy_rec.VAT_EXEMPTION_DATE,
3556 Copy_rec.VAT_EXEMPTION_REFNO,
3557 v_new_vat_assessable_value,
3558 (copy_rec.vat_reversal_price / copy_rec.quantity) *
3559 (v_line_quantity),
3560 copy_rec.service_type_code,
3561 pr_order_line.ship_to_org_id);
3562
3563 END IF;
3564 -- carry over the old ordered quantity into the below tax line loop for tax amount proportionating
3565
3566 v_old_quantity := copy_rec.QUANTITY;
3567
3568 OPEN Get_So_Tax_Lines_Count_Cur(pr_order_line.header_id,
3569 pr_order_line.SPLIT_FROM_LINE_ID);
3570 FETCH Get_So_Tax_Lines_Count_Cur
3571 INTO v_so_tax_lines_count;
3572 CLOSE Get_So_Tax_Lines_Count_Cur;
3573
3574 IF NVL(v_so_tax_lines_count, 0) > 0 THEN
3575
3576 FOR Rec IN So_Tax_Lines_Cur(pr_order_line.header_id,
3577 pr_order_line.SPLIT_FROM_LINE_ID) LOOP
3578
3579 SELECT COUNT(1)
3580 INTO v_tax_line_count
3581 FROM JAI_OM_OE_SO_TAXES
3582 WHERE line_id = pr_order_line.line_id
3583 AND tax_id = rec.tax_id;
3584
3585 IF v_tax_line_count = 0 THEN
3586
3587 open c_fetch_rounding_factor(rec.tax_id);
3588 fetch c_fetch_rounding_factor
3589 into v_rounding_factor, v_adhoc_flag;
3590 close c_fetch_rounding_factor;
3591
3592 IF v_adhoc_flag = 'N' THEN
3593
3594 --If the tax is a qty rate based tax then pick up the qty rate instead of the tax_rate so added an nvl condition
3595 --to also add qty_rate.
3596
3597 IF rec.tax_rate is not null THEN
3598 v_new_tax_amount := round((rec.base_tax_amount /
3599 v_old_quantity) *
3600 (v_line_quantity) *
3601 (rec.tax_rate) / 100,
3602 v_rounding_factor);
3603 ELSIF rec.qty_rate is not null THEN
3604 v_new_tax_amount := round((rec.base_tax_amount /
3605 v_old_quantity) *
3606 (v_line_quantity),
3607 v_rounding_factor);
3608 END IF;
3609
3610 ELSIF v_adhoc_flag = 'Y' THEN
3611 v_new_tax_amount := round((rec.tax_amount / v_old_quantity) *
3612 v_line_quantity,
3613 v_rounding_factor);
3614 END IF;
3615
3616 v_new_base_tax_amount := round((rec.base_tax_amount /
3617 v_old_quantity) *
3618 (v_line_quantity),
3619 v_rounding_factor);
3620 v_new_func_tax_amount := round((rec.func_tax_amount /
3621 v_old_quantity) *
3622 (v_line_quantity),
3623 v_rounding_factor);
3624 v_header_tax_amount := v_header_tax_amount +
3625 v_new_tax_amount;
3626 INSERT INTO JAI_OM_OE_SO_TAXES
3627 (header_id,
3628 line_id,
3629 tax_line_no,
3630 tax_id,
3631 tax_rate,
3632 qty_rate,
3633 uom,
3634 precedence_1,
3635 precedence_2,
3636 precedence_3,
3637 precedence_4,
3638 precedence_5,
3639 precedence_6,
3640 precedence_7,
3641 precedence_8,
3642 precedence_9,
3643 precedence_10,
3644 tax_amount,
3645 base_tax_amount,
3646 func_tax_amount,
3647 creation_date,
3648 created_by,
3649 last_update_date,
3650 last_updated_by,
3651 last_update_login,
3652 tax_category_id)
3653 VALUES
3654 (pr_order_line.header_id,
3655 pr_order_line.line_id,
3656 rec.tax_line_no,
3657 rec.tax_id,
3658 rec.tax_rate,
3659 rec.qty_rate,
3660 rec.uom,
3661 rec.precedence_1,
3662 rec.precedence_2,
3663 rec.precedence_3,
3664 rec.precedence_4,
3665 rec.precedence_5,
3666 rec.precedence_6,
3667 rec.precedence_7,
3668 rec.precedence_8,
3669 rec.precedence_9,
3670 rec.precedence_10,
3671 v_new_tax_amount,
3672 v_new_base_tax_amount,
3673 v_new_func_tax_amount,
3674 pr_order_line.creation_date,
3675 pr_order_line.created_by,
3676 pr_order_line.last_update_date,
3677 pr_order_line.last_updated_by,
3678 pr_order_line.last_update_login,
3679 rec.tax_category_id);
3680 END IF;
3681 END LOOP; --FOR Rec IN So_Tax_Lines_Cur( pr_order_line.header_id, pr_order_line.SPLIT_FROM_LINE_ID)
3682
3683 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3684 FND_LOG.STRING(G_LEVEL_STATEMENT,
3685 G_MODULE_NAME || v_api_name,
3686 '** START OForder_tax_amount_Cur ' ||
3687 pr_order_line.header_id || ' line id ' || pr_order_line.line_id);
3688 END IF;
3689 OPEN order_tax_amount_Cur(pr_order_line.header_id, pr_order_line.line_id);
3690 FETCH order_tax_amount_Cur
3691 INTO v_header_tax_amount;
3692 CLOSE order_tax_amount_Cur;
3693
3694 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3695 FND_LOG.STRING(G_LEVEL_STATEMENT,
3696 G_MODULE_NAME || v_api_name,
3697 '** v_header_tax_amount ' || v_header_tax_amount);
3698 END IF;
3699 update JAI_OM_OE_SO_LINES
3700 set tax_amount = nvl(v_header_tax_amount, 0),
3701 line_tot_amount = nvl(v_header_tax_amount, 0) +
3702 nvl(line_amount, 0)
3703 where header_id = pr_order_line.header_id
3704 and line_id = pr_order_line.line_id;
3705 END IF; -- NVL(v_so_tax_lines_count,0)>0 THEN
3706 EXCEPTION
3707 WHEN OTHERS THEN
3708 RAISE;
3709 END COPY_SPLIT_LINE;
3710
3711 --==========================================================================
3712 -- FUNCTION NAME:
3713 --
3714 -- Get_Model_line_Detail Private
3715 --
3716 -- DESCRIPTION:
3717 -- Return the ATO model line detail
3718 --
3719 -- PARAMETERS:
3720 -- In: pn_header_id IN NUMBER
3721 -- pn_line_id IN NUMBER
3722 --
3723 -- OUT: xn_organization_id OUT NUMBER
3724 -- xn_inventory_item_id OUT NUMBER
3725 --
3726 -- DESIGN REFERENCES:
3727 --
3728 -- CHANGE HISTORY:
3729 --
3730 -- 04-June-2010 Eric Ma created
3731 --==========================================================================
3732 PROCEDURE Get_Model_line_Detail(pn_header_id IN NUMBER,
3733 pn_line_id IN NUMBER,
3734 xn_organization_id OUT NOCOPY NUMBER,
3735 xn_inventory_item_id OUT NOCOPY NUMBER) IS
3736
3737 CURSOR Get_model_line_dtls_cur IS
3738 SELECT ship_from_org_id, inventory_item_id
3739 FROM OE_ORDER_LINES_ALL
3740 WHERE header_id = pn_header_id
3741 AND line_id = pn_line_id;
3742 BEGIN
3743 OPEN Get_model_line_dtls_cur;
3744 FETCH Get_model_line_dtls_cur
3745 INTO xn_organization_id, xn_inventory_item_id;
3746 CLOSE Get_model_line_dtls_cur;
3747 EXCEPTION
3748 WHEN OTHERS THEN
3749 RAISE;
3750 END Get_Model_line_Detail;
3751
3752 /*
3753 +======================================================================+
3754 REM NAME POPULATE_TAX
3755 REM
3756 REM DESCRIPTION Called from procdure JAI_TAX_PROCESSING_PKG
3757 REM
3758 REM NOTES Come from procdure JAI_OE_OLA_TRIGGER_PKG.ARIU_T1
3759 REM
3760 REM HISTORY
3761 REM 19-Jun-2012 Created from JAI trigger package
3762 REM +======================================================================+
3763 */
3764 PROCEDURE POPULATE_TAX(pr_old t_rec%type,
3765 pr_new t_rec%type,
3766 pv_action varchar2,
3767 pv_return_code out nocopy varchar2,
3768 pv_return_message out nocopy varchar2) IS
3769
3770 v_api_name CONSTANT VARCHAR2(30) := 'POPULATE_TAX';
3771
3772 CURSOR get_order_source_type(p_source_document_type_id NUMBER) IS
3773 SELECT name
3774 FROM oe_order_sources
3775 WHERE order_source_id = p_source_document_type_id;
3776
3777 ---------------------------------
3778 /* Declarations for Copy Order */
3779 ---------------------------------
3780 v_order_source_type VARCHAR2(240);
3781
3782 r_get_header_info get_header_info%ROWTYPE;
3783 v_hook VARCHAR2(6);
3784
3785 BEGIN
3786
3787 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3788 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3789 G_MODULE_NAME || v_api_name || '.BEGIN',
3790 G_PKG_NAME || ': ' || v_api_name || '()+');
3791 END IF;
3792
3793 pv_return_code := jai_constants.successful;
3794
3795 if (pv_action = jai_constants.updating and
3796 ((nvl(pr_old.line_number, -9999) <> nvl(pr_new.line_number, -9998)) and
3797 pr_new.line_number is not null)) OR
3798 (pv_action = jai_constants.updating and
3799 ((nvl(pr_old.shipment_number, -9999) <>
3800 nvl(pr_new.shipment_number, -9998)) and
3801 pr_new.shipment_number is not null)) then
3802
3803 update JAI_OM_OE_SO_LINES
3804 set line_number = pr_new.line_number,
3805 shipment_line_number = pr_new.shipment_number
3806 where line_id = pr_new.line_id;
3807
3808 return;
3809 end if;
3810
3811 v_hook := jai_cmn_hook_pkg.oe_lines_insert(pr_new.line_id,
3812 pr_new.org_id,
3813 pr_new.line_type_id,
3814 pr_new.ship_from_org_id,
3815 pr_new.ship_to_org_id,
3816 pr_new.invoice_to_org_id,
3817 pr_new.sold_to_org_id,
3818 pr_new.sold_from_org_id,
3819 pr_new.inventory_item_id,
3820 pr_new.tax_code,
3821 pr_new.price_list_id,
3822 pr_new.source_document_type_id,
3823 pr_new.source_document_line_id,
3824 pr_new.reference_line_id,
3825 pr_new.reference_header_id,
3826 pr_new.salesrep_id,
3827 pr_new.order_source_id,
3828 pr_new.orig_sys_document_ref,
3829 pr_new.orig_sys_line_ref);
3830
3831 IF v_hook = 'FALSE' THEN
3832 RETURN;
3833 END IF;
3834
3835 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3836 FND_LOG.STRING(G_LEVEL_STATEMENT,
3837 G_MODULE_NAME || v_api_name,
3838 '** START OF POPULATE_TAX ~ ' ||
3839 TO_CHAR(SYSDATE, 'dd/mm/rrrr hh24:mi:ss'));
3840 FND_LOG.STRING(G_LEVEL_STATEMENT,
3841 G_MODULE_NAME || v_api_name,
3842 ' Header ID ~ Line ID :' || TO_CHAR(pr_new.header_id) ||
3843 ' ~ ' || TO_CHAR(pr_new.line_id));
3844 END IF;
3845
3846 IF pv_action = jai_constants.updating AND
3847 pr_new.inventory_item_id <> pr_old.inventory_item_id THEN
3848 DELETE JAI_OM_OE_SO_LINES WHERE line_id = pr_new.line_id;
3849
3850 DELETE JAI_OM_OE_SO_TAXES WHERE line_id = pr_new.line_id;
3851
3852 END IF;
3853
3854 OPEN get_header_info(pr_new.header_id);
3855 FETCH get_header_info
3856 INTO r_get_header_info;
3857 CLOSE get_header_info;
3858
3859 -- check for conversion date
3860 IF r_get_header_info.conv_date IS NULL THEN
3861 r_get_header_info.conv_date := r_get_header_info.date_ordered;
3862 END IF;
3863
3864 IF pr_new.item_type_code = 'STANDARD' AND
3865 ((pr_new.reference_line_id IS NOT NULL OR
3866 pr_new.line_category_code = 'RETURN') AND
3867 pr_new.return_context IS NOT NULL) AND
3868 NVL(pr_new.SOURCE_DOCUMENT_TYPE_ID, 0) <> 2 THEN
3869
3870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3871 FND_LOG.STRING(G_LEVEL_STATEMENT,
3872 G_MODULE_NAME || v_api_name,
3873 ' Returning at STANDARD , RETURN, pr_new.SOURCE_DOCUMENT_TYPE_ID');
3874 FND_LOG.STRING(G_LEVEL_STATEMENT,
3875 G_MODULE_NAME || v_api_name,
3876 '** END OF POPULATE_TAX ~ ' ||
3877 TO_CHAR(SYSDATE, 'dd/mm/rrrr hh24:mi:ss'));
3878 END IF;
3879 RETURN;
3880 END IF;
3881
3882 OPEN Get_Order_Source_Type(pr_new.SOURCE_DOCUMENT_TYPE_ID);
3883 FETCH Get_Order_Source_Type
3884 INTO V_Order_Source_Type;
3885 CLOSE Get_Order_Source_Type;
3886
3887 IF (pr_new.SPLIT_FROM_LINE_ID IS NULL AND
3888 pr_new.SOURCE_DOCUMENT_TYPE_ID IS NOT NULL AND
3889 pr_new.SOURCE_DOCUMENT_LINE_ID IS NOT NULL AND
3890 V_Order_Source_Type = 'Copy') THEN
3891
3892 /************ Scenario 1. Copy Source Order ********************/
3893 copy_source_order(pr_new_line => pr_new,
3894 p_header_info => r_get_header_info,
3895 p_return_code => pv_return_code,
3896 p_return_message => pv_return_message);
3897
3898 if (pv_return_code <> jai_constants.successful) then
3899 return;
3900 end if;
3901
3902 ELSE
3903
3904 IF pr_new.SPLIT_FROM_LINE_ID IS NOT NULL AND
3905 pr_new.LINE_CATEGORY_CODE <> 'RETURN' THEN
3906
3907 /************ Scenario 2. Copy Split Lines ********************/
3908 copy_split_line(pr_order_line => pr_new);
3909
3910 ELSE
3911 /************ Scenario 3. Normal Creation ********************/
3912 create_normal_order(pr_order_line => pr_new,
3913 pr_old_line => pr_old,
3914 pr_header_info => r_get_header_info);
3915 END IF;
3916 END IF;
3917
3918 if upper(pr_new.item_type_code) = 'CONFIG' then
3919 IF pr_new.SPLIT_FROM_LINE_ID IS NULL AND
3920 pr_new.LINE_CATEGORY_CODE <> 'RETURN' THEN
3921
3922 /************ Scenario 4. Item Config ********************/
3923 process_config(pr_order_line => pr_new);
3924
3925 END IF;
3926 end if;
3927
3928 -- commented out by zhiwei.xin for Trigger Replacement bug #15968958 on 20-Dec-2012 begin
3929 /***** order level validation *****/
3930 /*order_line_validation(p_rec_old => pr_old,
3931 p_rec_new => pr_new,
3932 p_action => pv_action,
3933 px_return_code => pv_return_code,
3934 px_return_message => pv_return_message);*/
3935 -- commented out by zhiwei.xin for Trigger Replacement bug #15968958 on 20-Dec-2012 end.
3936
3937 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3938 FND_LOG.STRING(G_LEVEL_STATEMENT,
3939 G_MODULE_NAME || v_api_name,
3940 '** SUCCESSFUL END OF POPULATE_TAX ~ ' ||
3941 TO_CHAR(SYSDATE, 'DD/mm/rrrr hh24:mi:ss'));
3942 FND_LOG.STRING(G_LEVEL_STATEMENT,
3943 G_MODULE_NAME || v_api_name,
3944 'Header ID ~ Line ID :' ||
3945 TO_CHAR(pr_new.header_id) || ' ~ ' ||
3946 TO_CHAR(pr_new.line_id));
3947 END IF;
3948
3949 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3950 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3951 G_MODULE_NAME || v_api_name || '.END',
3952 G_PKG_NAME || ': ' || v_api_name || '()-' ||
3953 ' RETURN_CODE = ' || pv_return_code);
3954 END IF;
3955
3956 EXCEPTION
3957 WHEN OTHERS THEN
3958
3959 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
3960 FND_LOG.STRING(G_LEVEL_ERROR,
3961 G_MODULE_NAME || v_api_name,
3962 'Header ID ~ Line ID :' ||
3963 TO_CHAR(pr_new.header_id) || ' ~ ' ||
3964 TO_CHAR(pr_new.line_id));
3965 FND_LOG.STRING(G_LEVEL_ERROR,
3966 G_MODULE_NAME || v_api_name,
3967 'Error :' || SQLERRM);
3968 FND_LOG.STRING(G_LEVEL_ERROR,
3969 G_MODULE_NAME || v_api_name,
3970 '** Error END OF POPULATE_TAX ~ ' ||
3971 TO_CHAR(SYSDATE, 'DD/mm/rrrr hh24:mi:ss'));
3972 END IF;
3973
3974 Pv_return_code := jai_constants.unexpected_error;
3975 Pv_return_message := 'Encountered an error in JAI_OM_TAX_PROCESSING_PKG.POPULATE_TAX ' ||
3976 substr(sqlerrm, 1, 1900);
3977 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3978 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
3979 G_MODULE_NAME || v_api_name,
3980 SQLERRM);
3981 END IF;
3982
3983 END POPULATE_TAX;
3984
3985 /*
3986 REM +======================================================================+
3987 REM NAME UPDATE_TAX
3988 REM
3989 REM DESCRIPTION Called from procdure JAI_TAX_PROCESSING_PKG
3990 REM
3991 REM NOTES Come from procdure JAI_OE_OLA_TRIGGER_PKG.ARU_T1
3992 REM
3993 REM +======================================================================+
3994 */
3995 PROCEDURE UPDATE_TAX(pr_old t_rec%type,
3996 pr_new t_rec%type,
3997 pv_action varchar2,
3998 pv_return_code out nocopy varchar2,
3999 pv_return_message out nocopy varchar2) IS
4000 v_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TAX';
4001 v_row_id ROWID;
4002 v_line_id NUMBER;
4003 v_header_id NUMBER;
4004 v_warehouse_id NUMBER;
4005 v_quantity NUMBER;
4006 v_last_update_date DATE;
4007 v_last_updated_by NUMBER;
4008 v_last_update_login NUMBER;
4009 v_line_amount NUMBER;
4010 v_inventory_item_id NUMBER;
4011 v_uom_code VARCHAR2(3);
4012 v_ship_to_site_use_id NUMBER;
4013 v_selling_price NUMBER;
4014 v_old_assessable_value NUMBER;
4015 v_line_tax_amount NUMBER;
4016 v_assessable_amount NUMBER;
4017 v_ato_line_amount NUMBER;
4018 v_date_ordered DATE;
4019 v_assessable_value NUMBER;
4020 v_org_id NUMBER;
4021 v_set_of_books_id NUMBER;
4022 v_conv_type_code VARCHAR2(30);
4023 v_conv_rate NUMBER;
4024 v_conv_date DATE;
4025 v_conv_factor NUMBER;
4026 v_old_quantity NUMBER;
4027 v_price_list_id NUMBER;
4028 v_customer_id NUMBER;
4029 v_address_id NUMBER;
4030 ln_inventory_item_id NUMBER;
4031 ln_line_id NUMBER;
4032
4033 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
4034
4035 CURSOR JAI_OM_OE_SO_LINES_cur(p_line_id NUMBER) IS
4036 SELECT quantity,
4037 assessable_value,
4038 excise_exempt_type,
4039 excise_exempt_refno,
4040 vat_reversal_price
4041 FROM JAI_OM_OE_SO_LINES
4042 WHERE line_id = p_line_id;
4043
4044 CURSOR c_ja_in_rma_lines(p_line_id NUMBER) IS
4045 SELECT quantity, assessable_value
4046 FROM JAI_OM_OE_RMA_LINES
4047 WHERE rma_line_id = p_line_id;
4048
4049 CURSOR bind_cur(p_header_id NUMBER) IS
4050 SELECT org_id,
4051 ROWID,
4052 TRANSACTIONAL_CURR_CODE,
4053 conversion_type_code,
4054 conversion_rate,
4055 CONVERSION_RATE_DATE,
4056 SOLD_TO_ORG_ID,
4057 price_list_id,
4058 NVL(ORDERED_DATE, creation_date)
4059 FROM OE_ORDER_HEADERS_ALL
4060 WHERE header_id = p_header_id;
4061
4062 CURSOR address_cur(p_ship_to_org_id IN NUMBER) IS
4063 SELECT NVL(cust_acct_site_id, 0) address_id
4064 FROM HZ_CUST_SITE_USES_ALL A
4065 WHERE A.site_use_id = p_ship_to_org_id;
4066
4067 v_currency_code gl_sets_of_books.currency_code%TYPE;
4068
4069 v_excise_exempt_type varchar2(60);
4070 v_excise_exempt_refno varchar2(30);
4071
4072 Cursor c_get_loc_record is
4073 select selling_price, assessable_value
4074 from JAI_OM_OE_SO_LINES
4075 where header_id = pr_new.header_id
4076 and line_id = pr_new.line_id;
4077
4078 v_loc_selling_price Number;
4079 v_loc_assessable_value Number;
4080
4081 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
4082
4083 ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE;
4084 ln_ordered_qty NUMBER;
4085
4086 PROCEDURE calc_price_tax_for_config_item(p_header_id number,
4087 p_line_id number) is
4088 CURSOR c_get_line_tax_amt is
4089 SELECT line_amount,
4090 tax_amount,
4091 selling_price,
4092 assessable_value,
4093 decode(quantity, 0, 1, quantity) quantity,
4094 vat_assessable_value
4095 FROM JAI_OM_OE_SO_LINES
4096 WHERE header_id = p_header_id
4097 AND shipment_schedule_line_id = pr_new.ato_line_id
4098 AND line_id <> p_line_id;
4099 BEGIN
4100
4101 v_selling_price := 0;
4102 v_assessable_value := 0;
4103 ln_vat_assessable_value := 0;
4104 v_ato_line_amount := 0;
4105
4106 IF nvl(pr_new.ordered_quantity, 0) = 0 THEN
4107 ln_ordered_qty := 1;
4108 ELSE
4109 ln_ordered_qty := pr_new.ordered_quantity;
4110 END IF;
4111
4112 FOR so_lines_rec in c_get_line_tax_amt LOOP
4113 v_ato_line_amount := NVL(v_ato_line_amount, 0) +
4114 NVL(so_lines_rec.line_amount, 0);
4115
4116 v_selling_price := NVL(v_selling_price, 0) +
4117 ((NVL(so_lines_rec.selling_price, 0) *
4118 so_lines_rec.quantity) / ln_ordered_qty);
4119
4120 v_assessable_value := NVL(v_assessable_value, 0) +
4121 ((NVL(so_lines_rec.assessable_value,
4122 so_lines_rec.selling_price) *
4123 so_lines_rec.quantity) / ln_ordered_qty);
4124
4125 ln_vat_assessable_value := NVL(ln_vat_assessable_value, 0) +
4126 ((NVL(so_lines_rec.vat_assessable_value,
4127 so_lines_rec.selling_price) *
4128 so_lines_rec.quantity) / ln_ordered_qty);
4129
4130 END LOOP;
4131 END;
4132
4133 PROCEDURE get_config_item(p_line_id OUT NOCOPY NUMBER,
4134 p_inventory_item_id OUT NOCOPY NUMBER) IS
4135 PRAGMA AUTONOMOUS_TRANSACTION; --added for bug#16341712
4136
4137 CURSOR cur_get_line_id IS
4138 SELECT line_id, inventory_item_id
4139 FROM oe_order_lines_all
4140 WHERE header_id = pr_new.Header_id
4141 and top_model_line_id = pr_new.top_model_line_id
4142 and item_type_code = 'CONFIG';
4143
4144 BEGIN
4145
4146 OPEN cur_get_line_id;
4147 FETCH cur_get_line_id
4148 INTO p_line_id, p_inventory_item_id;
4149 CLOSE cur_get_line_id;
4150
4151 END get_config_item;
4152
4153 BEGIN
4154 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4155 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4156 G_MODULE_NAME || v_api_name || '.BEGIN',
4157 G_PKG_NAME || ': ' || v_api_name || '()+');
4158 END IF;
4159 pv_return_code := jai_constants.successful;
4160
4161 v_line_id := pr_new.line_id;
4162 v_header_id := pr_new.header_id;
4163 v_warehouse_id := pr_new.SHIP_FROM_ORG_ID;
4164 v_quantity := NVL(pr_new.ordered_quantity, 0);
4165 v_last_update_date := pr_new.last_update_date;
4166 v_last_updated_by := pr_new.last_updated_by;
4167 v_last_update_login := pr_new.last_update_login;
4168 v_line_amount := NVL(v_quantity, 0) *
4169 NVL(pr_new.UNIT_selling_price, 0);
4170 v_inventory_item_id := pr_new.inventory_item_id;
4171 v_uom_code := pr_new.ORDER_QUANTITY_UOM;
4172 v_ship_to_site_use_id := NVL(pr_new.SHIP_TO_ORG_ID, 0);
4173 v_selling_price := pr_new.UNIT_SELLING_PRICE;
4174 v_old_assessable_value := 0;
4175 v_ato_line_amount := 0;
4176
4177 -- the following if condition " if pr_new.LINE_CATEGORY_CODE = 'RETURN' then " added by sriram
4178 -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
4179 -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
4180 -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
4181 -- as the JAI_OM_OE_SO_LINES has no records for a return order and instead records are present in the JAI_OM_OE_RMA_LINES
4182 -- table.
4183 -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
4184 -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
4185
4186 if pr_new.LINE_CATEGORY_CODE = 'RETURN' then
4187 OPEN c_ja_in_rma_lines(v_line_id);
4188 FETCH c_ja_in_rma_lines
4189 INTO v_old_quantity, v_old_assessable_value;
4190 CLOSE c_ja_in_rma_lines;
4191 else
4192 OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
4193 FETCH JAI_OM_OE_SO_LINES_cur
4194 INTO v_old_quantity,
4195 v_old_assessable_value,
4196 v_excise_exempt_type,
4197 v_excise_exempt_refno,
4198 ln_vat_reversal_price;
4199 CLOSE JAI_OM_OE_SO_LINES_cur;
4200 end if;
4201
4202 OPEN Bind_Cur(v_header_id);
4203 FETCH Bind_Cur
4204 INTO v_org_id,
4205 v_row_id,
4206 v_currency_code,
4207 v_conv_type_code,
4208 v_conv_rate,
4209 v_conv_date,
4210 v_customer_id,
4211 v_price_list_id,
4212 v_date_ordered;
4213 CLOSE Bind_Cur;
4214
4215 IF v_conv_date IS NULL THEN
4216 v_conv_date := v_date_ordered;
4217 END IF;
4218
4219 /*
4220 Removed the cursor set_of_books_cur and added the following check
4221 using plsql caching for performance issues reported.
4222 */
4223 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => v_warehouse_id);
4224 v_set_of_books_id := l_func_curr_det.ledger_id;
4225
4226 v_conv_factor := jai_cmn_utils_pkg.currency_conversion(v_set_of_books_id,
4227 v_currency_code,
4228 v_conv_date,
4229 v_conv_type_code,
4230 v_conv_rate);
4231
4232 OPEN address_cur(v_ship_to_site_use_id);
4233 FETCH address_cur
4234 INTO v_address_id;
4235 CLOSE address_cur;
4236
4237 --The Logic of Fetching the Assessable Value is written in the Function jai_om_utils_pkg.get_oe_assessable_value.
4238 v_assessable_value := jai_om_utils_pkg.get_oe_assessable_value(p_customer_id => v_customer_id,
4239 p_ship_to_site_use_id => v_ship_to_site_use_id,
4240 p_inventory_item_id => v_inventory_item_id,
4241 p_uom_code => v_uom_code,
4242 p_default_price => pr_new.unit_selling_price,
4243 p_ass_value_date => v_date_ordered,
4244 p_sob_id => v_set_of_books_id,
4245 p_curr_conv_code => v_conv_type_code,
4246 p_conv_rate => v_conv_factor);
4247
4248 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value(p_party_id => v_customer_id,
4249 p_party_site_id => v_ship_to_site_use_id,
4250 p_inventory_item_id => v_inventory_item_id,
4251 p_uom_code => v_uom_code,
4252 p_default_price => pr_new.unit_selling_price,
4253 p_ass_value_date => v_date_ordered,
4254 p_party_type => 'C');
4255
4256 ln_vat_assessable_value := nvl(ln_vat_assessable_value, 0) *
4257 NVL(v_quantity, 0);
4258 ln_vat_reversal_price := nvl(ln_vat_reversal_price, 0) *
4259 NVL(v_quantity, 0);
4260
4261 if NVL(pr_new.item_type_code, '$$$') = 'CONFIG' then
4262
4263 open c_get_loc_record;
4264 fetch c_get_loc_record
4265 into v_loc_selling_price, v_loc_assessable_value;
4266 close c_get_loc_record;
4267 v_assessable_value := v_loc_assessable_value;
4268 else
4269
4270 /*
4271
4272 Functional Description:-
4273 During partial shipment, if the assessable price list setup has been removed after booking an order then
4274 during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
4275 JAI_OM_OE_SO_LINES table and if this too happens to be null then only should it be assigned as nvl(pr_new.unit_selling_price).
4276
4277 Technical Description:-
4278 Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
4279 IF
4280 no then assign this value to the v_assessable_value variable
4281 ELSE
4282 Assign the assign the nvl(pr_new.unit_selling_price) to the v_assessable_value variable.
4283 */
4284
4285 IF NVL(pr_new.Ordered_Quantity, 0) <> NVL(pr_old.Ordered_quantity, 0) AND
4286 pr_new.flow_status_code = 'AWAITING_SHIPPING' THEN
4287 /*
4288 only when there is a partial shipment , do the calculation of assessable value based on
4289 price of the actual price list.
4290 */
4291
4292 DECLARE
4293
4294 CURSOR rec_get_assessable_value IS
4295 SELECT assessable_value
4296 FROM JAI_OM_OE_SO_LINES
4297 WHERE line_id = v_line_id;
4298
4299 cur_rec_get_assessable_value rec_get_assessable_value%ROWTYPE;
4300 BEGIN
4301 pv_return_code := jai_constants.successful;
4302
4303 if v_assessable_value IS NOT NULL THEN
4304
4305 OPEN rec_get_assessable_value;
4306 FETCH rec_get_assessable_value
4307 INTO cur_rec_get_assessable_value;
4308
4309 IF cur_rec_get_assessable_value.assessable_value IS NOT NULL THEN
4310 v_assessable_value := cur_rec_get_assessable_value.assessable_value;
4311 ELSE
4312 v_assessable_value := NVL(pr_new.UNIT_SELLING_PRICE, 0);
4313 END IF;
4314
4315 CLOSE rec_get_assessable_value;
4316 end if;
4317 END;
4318
4319 end if;
4320
4321 end if;
4322
4323 v_assessable_amount := NVL(v_assessable_value, 0) * NVL(v_quantity, 0);
4324
4325 IF v_assessable_amount = 0 THEN
4326 v_assessable_amount := NVL(v_line_Amount, 0);
4327 END IF;
4328
4329 if NVL(v_line_Amount, 0) = 0 then
4330 v_line_amount := v_quantity * v_loc_selling_price;
4331 end if;
4332
4333 v_line_tax_amount := NVL(v_line_amount, 0);
4334
4335 if v_excise_exempt_refno is not null and
4336 v_excise_exempt_type is not null then
4337 v_assessable_amount := 0;
4338 end if;
4339
4340 IF upper(pr_new.item_type_code) = 'CONFIG' AND
4341 pr_new.line_category_code <> 'RETURN' THEN
4342 calc_price_tax_for_config_item(pr_new.header_id, pr_new.line_id);
4343 END IF;
4344
4345 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE',
4346 NULL,
4347 v_header_id,
4348 v_line_id,
4349 v_assessable_amount,
4350 v_line_tax_amount,
4351 v_conv_factor,
4352 v_inventory_item_id,
4353 NVL(v_old_quantity, 0),
4354 v_quantity,
4355 v_uom_code,
4356 NULL,
4357 NULL,
4358 NULL,
4359 NULL,
4360 v_last_update_date,
4361 v_last_updated_by,
4362 v_last_update_login,
4363 ln_vat_assessable_value,
4364 ln_vat_reversal_price);
4365
4366 IF pr_new.line_category_code = 'ORDER' THEN
4367
4368 UPDATE jai_om_oe_so_lines
4369 SET quantity = v_quantity,
4370 unit_code = v_uom_code,
4371 selling_price = v_selling_price,
4372 assessable_value = nvl(v_assessable_value, v_selling_price),
4373 vat_assessable_value = nvl(ln_vat_assessable_value, 0),
4374 tax_amount = NVL(v_line_tax_amount, 0),
4375 line_amount = v_line_amount,
4376 line_tot_amount = v_line_amount +
4377 NVL(v_line_tax_amount, 0),
4378 last_update_date = v_last_update_date,
4379 last_updated_by = v_last_updated_by,
4380 last_update_login = v_last_update_login
4381 WHERE line_id = v_line_id;
4382
4383 IF (upper(pr_new.item_type_code) <> 'CONFIG' AND
4384 pr_new.unit_selling_price <> pr_old.unit_selling_price) THEN
4385
4386 get_config_item(ln_line_id, ln_inventory_item_id);
4387
4388 IF ln_line_id is not null THEN
4389 calc_price_tax_for_config_item(pr_new.header_id, ln_line_id);
4390 v_line_tax_amount := NVL(v_ato_line_amount, 0);
4391 v_assessable_amount := NVL(v_assessable_value, 0) *
4392 NVL(v_quantity, 0);
4393 jai_om_tax_pkg.calculate_ato_taxes('OE_LINES_UPDATE',
4394 NULL,
4395 v_header_id,
4396 ln_line_id,
4397 v_assessable_amount,
4398 v_line_tax_amount,
4399 v_conv_factor,
4400 ln_inventory_item_id,
4401 NVL(v_quantity, 0),
4402 v_quantity,
4403 v_uom_code,
4404 NULL,
4405 NULL,
4406 NULL,
4407 NULL,
4408 v_last_update_date,
4409 v_last_updated_by,
4410 v_last_update_login,
4411 ln_vat_assessable_value,
4412 ln_vat_reversal_price);
4413
4414 UPDATE JAI_OM_OE_SO_LINES
4415 SET quantity = v_quantity,
4416 unit_code = v_uom_code,
4417 selling_price = v_selling_price,
4418 assessable_value = nvl(v_assessable_value,
4419 v_selling_price),
4420 vat_assessable_value = nvl(ln_vat_assessable_value, 0),
4421 tax_amount = NVL(v_line_tax_amount, 0),
4422 line_amount = v_ato_line_amount,
4423 line_tot_amount = v_ato_line_amount +
4424 NVL(v_line_tax_amount, 0),
4425 last_update_date = v_last_update_date,
4426 last_updated_by = v_last_updated_by,
4427 last_update_login = v_last_update_login
4428 WHERE line_id = ln_line_id;
4429
4430 END IF;
4431 END IF;
4432
4433 ELSIF pr_new.line_category_code = 'RETURN' THEN
4434
4435 UPDATE jai_om_oe_rma_lines
4436 SET quantity = v_quantity,
4437 uom = v_uom_code,
4438 selling_price = v_selling_price,
4439 assessable_value = v_assessable_value,
4440 tax_amount = NVL(v_line_tax_amount, 0),
4441 inventory_item_id = v_inventory_item_id,
4442 last_update_date = v_last_update_date,
4443 last_updated_by = v_last_updated_by,
4444 last_update_login = v_last_update_login
4445 WHERE rma_line_id = v_line_id;
4446
4447 END IF;
4448
4449 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4450 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4451 G_MODULE_NAME || v_api_name || '.END',
4452 G_PKG_NAME || ': ' || v_api_name || '()-' ||
4453 ' RETURN_CODE = ' || pv_return_code);
4454 END IF;
4455 EXCEPTION
4456 WHEN OTHERS THEN
4457 Pv_return_code := jai_constants.unexpected_error;
4458 Pv_return_message := 'Encountered an error in JAI_OM_TAX_PROCESSING_PKG.UPDATE_TAX ' ||
4459 substr(sqlerrm, 1, 1900);
4460 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
4461 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
4462 G_MODULE_NAME || v_api_name,
4463 SQLERRM);
4464 END IF;
4465
4466 END UPDATE_TAX;
4467
4468 /*
4469 REM +======================================================================+
4470 REM NAME DEFAULT_TAX
4471 REM
4472 REM DESCRIPTION Called from procdure JAI_TAX_PROCESSING_PKG
4473 REM
4474 REM NOTES Come from procdure JAI_OE_OLA_TRIGGER_PKG.BRIU_T1
4475 REM
4476 REM +======================================================================+
4477 */
4478 PROCEDURE DEFAULT_TAX(pr_old t_rec%type,
4479 pr_new t_rec%type,
4480 pv_action varchar2,
4481 pv_return_code out nocopy varchar2,
4482 pv_return_message out nocopy varchar2) IS
4483 v_exist_ship NUMBER;
4484 v_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_TAX';
4485
4486 CURSOR get_count IS
4487 SELECT COUNT(*)
4488 FROM JAI_OM_WSH_LINES_ALL
4489 WHERE order_line_id = pr_new.reference_line_id;
4490
4491 /*
4492 get the delivery_detail_id from wsh_delivery_details table
4493 */
4494
4495 CURSOR cur_get_delivery_detail_id IS
4496 SELECT delivery_detail_id
4497 FROM wsh_delivery_details
4498 WHERE source_header_id = pr_new.reference_header_id
4499 AND source_line_id = pr_new.reference_line_id
4500 AND inventory_item_id = pr_new.inventory_item_id
4501 AND shipped_quantity IS NOT NULL;
4502
4503 l_new_delivery_detail_id WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID%TYPE;
4504
4505 Cursor c_ordered_date_cur is
4506 select ordered_date
4507 from oe_order_headers_all
4508 where header_id = pr_new.header_id;
4509
4510 -- following cursor is used to get the ship confirm date based on the delivery detail id
4511
4512 cursor c_confirmed_date(p_delivery_Detail_id Number) is
4513 select confirm_date
4514 FROM wsh_delivery_details wdd,
4515 wsh_delivery_assignments wda,
4516 wsh_new_deliveries wnd
4517 WHERE wdd.delivery_detail_id = (p_delivery_Detail_id)
4518 AND wda.delivery_detail_id = wdd.delivery_detail_id
4519 AND wnd.delivery_id = wda.delivery_id;
4520
4521 Cursor c_hr_organizations_cur is
4522 SELECT excise_return_days, sales_return_days
4523 FROM JAI_CMN_INVENTORY_ORGS
4524 WHERE organization_id = pr_new.ship_from_org_id
4525 AND location_id = 0;
4526
4527 Cursor cur_chk_item_dtls(cp_organization_id IN NUMBER,
4528 cp_inventory_item_id IN NUMBER) IS
4529 SELECT 1
4530 FROM JAI_RGM_ITM_REGNS
4531 WHERE ORGANIZATION_ID = cp_organization_id
4532 AND inventory_item_id = cp_inventory_item_id;
4533
4534 ln_organization_id NUMBER;
4535 ln_inventory_item_id NUMBER;
4536 ln_item_exists NUMBER;
4537
4538 v_ordered_Date oe_order_headers_all.ordered_date%type;
4539 v_confirm_date wsh_new_deliveries.confirm_date%type;
4540 v_excise_return_days Number;
4541 v_sales_return_days Number;
4542
4543 CURSOR get_order_source_type(cp_source_document_type_id NUMBER) IS
4544 SELECT name
4545 FROM oe_order_sources
4546 WHERE order_source_id = cp_source_document_type_id;
4547
4548 V_Order_Source_Type OE_ORDER_SOURCES.NAME%TYPE;
4549
4550 cursor c_rma_line_dtls(cp_rma_line_id in number) is
4551 select delivery_detail_id,
4552 nvl(allow_excise_credit_flag, 'N') allow_excise_credit_flag,
4553 nvl(allow_sales_credit_flag, 'N') allow_sales_credit_flag,
4554 rate_per_unit,
4555 excise_duty_rate
4556 from JAI_OM_OE_RMA_LINES
4557 where rma_line_id = cp_rma_line_id;
4558
4559 ln_delivery_detail_id number;
4560 lv_allow_excise_flag varchar2(1);
4561 lv_allow_sales_flag varchar2(1);
4562 ln_excise_duty_per_unit number;
4563 ln_excise_duty_rate number;
4564 BEGIN
4565 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4566 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4567 G_MODULE_NAME || v_api_name || '.BEGIN',
4568 G_PKG_NAME || ': ' || v_api_name || '()+');
4569 END IF;
4570
4571 pv_return_code := jai_constants.successful;
4572
4573 OPEN Get_Count;
4574 FETCH Get_Count
4575 INTO v_exist_ship;
4576 CLOSE Get_Count;
4577 If pr_new.item_type_code = 'CONFIG' and pr_new.ato_line_id is not null AND
4578 pv_action = jai_constants.inserting THEN
4579 OPEN cur_chk_item_dtls(pr_new.SHIP_from_ORG_ID,
4580 pr_new.inventory_item_id);
4581 FETCH cur_chk_item_dtls
4582 INTO ln_item_exists;
4583 CLOSE cur_chk_item_dtls;
4584
4585 IF nvl(ln_item_exists, 0) <> 1 THEN
4586 /*
4587 * The firing table is on OE_ORDER_LINES_ALL. The firing table accessing in the
4588 * trigger in the enven of UPDATING will cause table mutating error. So use an
4589 * AUTONOMOUS transaction to resolve the issue.
4590 */
4591
4592 Get_Model_line_Detail(pn_header_id => pr_new.header_id,
4593 pn_line_id => pr_new.ato_line_id,
4594 xn_organization_id => ln_organization_id,
4595 xn_inventory_item_id => ln_inventory_item_id);
4596
4597 --this is for excise attributes
4598 jai_inv_items_pkg.copy_items(pn_organization_id => pr_new.SHIP_from_ORG_ID,
4599 pn_inventory_item_id => pr_new.inventory_item_id,
4600 pn_source_organization_id => ln_organization_id,
4601 pn_source_inventory_item_id => ln_inventory_item_id);
4602
4603 jai_inv_items_pkg.copy_items(pn_organization_id => pr_new.SHIP_from_ORG_ID,
4604 pn_inventory_item_id => pr_new.inventory_item_id,
4605 pn_source_organization_id => ln_organization_id,
4606 pn_source_inventory_item_id => ln_inventory_item_id,
4607 pn_regime_code => jai_constants.vat_regime);
4608
4609 END IF;
4610 END IF;
4611
4612 IF NVL(v_exist_ship, 0) = 0 THEN
4613 RETURN;
4614 END IF;
4615
4616 /*
4617 Before inserting a record in the oe_order_lines_all table for a return order, check if the delivery detail_id i.e pr_new.attribute2 is null.
4618 IF yes then
4619 1. pick up the delivery_detail_id from the wsh_delivery_details table for records corresponding to the reference_header_id and
4620 reference_line_id in this table and populate the pr_new.attibute2 dff field
4621 2.Set the pr_new.attribute3 = 'Y' and pr_new.attribute4 = 'Y'
4622 3. Populate the context with the following information
4623 if pr_new.return_context pr_new.Context
4624 ---------------------- -------------------
4625 'ORDER' 'Sales Order India'
4626 'INVOICE' 'Invoice India'
4627 'PO' 'Customer PO India'
4628
4629 */
4630
4631 IF pv_action IN (jai_constants.INSERTING, jai_constants.UPDATING) AND
4632 pr_new.reference_header_id IS NOT NULL THEN
4633
4634 OPEN cur_get_delivery_detail_id;
4635 FETCH cur_get_delivery_detail_id
4636 INTO l_new_delivery_detail_id;
4637
4638 if cur_get_delivery_detail_id%FOUND then
4639
4640 open c_ordered_date_cur;
4641 fetch c_ordered_date_cur
4642 into v_ordered_date;
4643 close c_ordered_date_cur;
4644
4645 open c_confirmed_date(l_new_delivery_detail_id);
4646 fetch c_confirmed_date
4647 into v_confirm_date;
4648 close c_confirmed_date;
4649
4650 open c_hr_organizations_cur;
4651 fetch c_hr_organizations_cur
4652 into v_excise_return_days, v_sales_return_days;
4653 close c_hr_organizations_cur;
4654
4655 ln_delivery_detail_id := l_new_delivery_detail_id;
4656
4657 if (v_excise_return_days IS NULL OR
4658 (v_ordered_date - v_confirm_date) <= v_excise_return_days) then
4659 lv_allow_excise_flag := 'Y';
4660 else
4661 lv_allow_excise_flag := 'N';
4662 end if;
4663 if (v_sales_return_days IS NULL OR
4664 (v_ordered_date - v_confirm_date) <= v_sales_return_days) then
4665 lv_allow_sales_flag := 'Y';
4666 else
4667 lv_allow_sales_flag := 'N';
4668 end if;
4669
4670 end if;
4671
4672 CLOSE cur_get_delivery_detail_id;
4673
4674 else
4675
4676 open c_rma_line_dtls(pr_new.line_id);
4677 fetch c_rma_line_dtls
4678 into ln_delivery_detail_id,
4679 lv_allow_excise_flag,
4680 lv_allow_sales_flag,
4681 ln_excise_duty_per_unit,
4682 ln_excise_duty_rate;
4683 close c_rma_line_dtls;
4684
4685 end if;
4686
4687 /* If the v_order_source_type is Copy then this trigger should not insert the taxes */
4688
4689 OPEN Get_Order_Source_Type(pr_new.SOURCE_DOCUMENT_TYPE_ID);
4690 FETCH Get_Order_Source_Type
4691 INTO V_Order_Source_Type;
4692 CLOSE Get_Order_Source_Type;
4693 IF (pr_new.SPLIT_FROM_LINE_ID IS NULL AND
4694 pr_new.SOURCE_DOCUMENT_TYPE_ID IS NOT NULL AND
4695 pr_new.SOURCE_DOCUMENT_LINE_ID IS NOT NULL AND
4696 V_Order_Source_Type = 'Copy') THEN
4697 RETURN;
4698 END IF;
4699
4700 jai_om_rma_pkg.default_taxes_onto_line(pr_new.header_id,
4701 pr_new.line_id,
4702 pr_new.inventory_item_id,
4703 pr_new.ship_from_org_id,
4704 pr_new.reference_line_id,
4705 pr_new.reference_customer_trx_line_id,
4706 pr_new.line_number,
4707 pr_old.return_context,
4708 ln_delivery_detail_id,
4709 lv_allow_excise_flag,
4710 lv_allow_sales_flag,
4711 ln_excise_duty_per_unit,
4712 ln_excise_duty_rate,
4713 pr_old.reference_line_id,
4714 pr_old.reference_customer_trx_line_id,
4715 pr_old.ordered_quantity,
4716 pr_old.cancelled_quantity,
4717 pr_new.return_context,
4718 pr_new.ordered_quantity,
4719 pr_new.cancelled_quantity,
4720 pr_new.order_quantity_uom,
4721 pr_old.unit_selling_price,
4722 pr_new.unit_selling_price,
4723 pr_new.item_type_code,
4724 NULL,
4725 pr_new.creation_date,
4726 pr_new.created_by,
4727 pr_new.last_update_date,
4728 pr_new.last_updated_by,
4729 pr_new.last_update_login,
4730 pr_new.source_document_type_id,
4731 pr_new.line_category_code
4732 );
4733
4734
4735 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4736 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4737 G_MODULE_NAME || v_api_name || '.END',
4738 G_PKG_NAME || ': ' || v_api_name || '()-' ||
4739 ' RETURN_CODE = ' || pv_return_code);
4740 END IF;
4741 EXCEPTION
4742 WHEN OTHERS THEN
4743 Pv_return_code := jai_constants.unexpected_error;
4744 Pv_return_message := 'Encountered an error in JAI_OM_TAX_PROCESSING_PKG.DEFAULT_TAX ' ||
4745 substr(sqlerrm, 1, 1900);
4746 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
4747 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
4748 G_MODULE_NAME || v_api_name,
4749 SQLERRM);
4750 END IF;
4751
4752 END DEFAULT_TAX;
4753
4754 END JAI_OM_TAX_PROCESSING_PKG;