[Home] [Help]
PACKAGE BODY: APPS.JAI_OM_WDD_PROCESSING_PKG
Source
1 PACKAGE BODY JAI_OM_WDD_PROCESSING_PKG AS
2 /* $Header: jai_om_wdd_prc.plb 120.0.12020000.3 2013/03/19 00:31:44 vkaranam noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_OM_WDD_PROCESSING_PKG';
4 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(60) := 'JA.PLSQL.JAI_OM_WDD_PROCESSING_PKG.';
12
13 /*
14 REM +======================================================================+
15 REM NAME DELIVER_DETAILS
16 REM
17 REM DESCRIPTION Called from trigger JAI_OM_WDD_ARIUD_TRG
18 REM
19 REM NOTES Come from JAI_OM_WDD_TRIGGER_PKG.ARU_T2
20 REM
21 REM +======================================================================+
22 */
23 PROCEDURE DELIVER_DETAILS (pr_old t_rec%type ,
24 pr_new t_rec%type ,
25 pv_action varchar2 ,
26 pv_return_code out nocopy varchar2 ,
27 pv_return_message out nocopy varchar2 )
28 IS
29
30 v_api_name CONSTANT VARCHAR2(30) := 'DELIVER_DETAILS';
31 v_inventory_item_id NUMBER;
32 v_organization_id NUMBER ;
33 v_subinventory VARCHAR2(10);
34 v_delivery_detail_id NUMBER;
35 v_source_header_type_id NUMBER ;
36 v_shipped_quantity NUMBER ;
37 v_matched_qty NUMBER ;
38 v_trading_flag VARCHAR2(1) ;
39 v_bonded JAI_INV_SUBINV_DTLS.bonded%TYPE;
40 v_trad_register_code VARCHAR2(30) ;
41 v_item_trading_flag VARCHAR2(1) ;
42 v_location_id NUMBER ;
43 v_exe_flag VARCHAR2(150) ;
44 v_mod_flag VARCHAR2(150) ;
45 v_container_item_flag mtl_system_items.container_item_flag%type ;
46 lv_inventory_item_flag mtl_system_items.inventory_item_flag%type ;
47 le_error EXCEPTION ;
48
49 CURSOR Location_Cursor
50 IS
51 SELECT
52 NVL(Location_id,0),
53 trading,
54 NVL(bonded,'N') bonded
55 FROM
56 JAI_INV_SUBINV_DTLS
57 WHERE
58 Sub_Inventory_Name = v_subinventory AND
59 organization_id = v_organization_id ;
60
61 CURSOR item_trading_cur
62 IS
63 SELECT
64 Item_Trading_Flag
65 FROM
66 JAI_INV_ITM_SETUPS
67 WHERE
68 organization_id = v_organization_id AND
69 inventory_item_id = v_inventory_item_id;
70
71 CURSOR Trading_register_code_cur(p_organization_id NUMBER ,
72 p_location_id NUMBER ,
73 p_order_type_id NUMBER)
74 IS
75 SELECT
76 A.register_code
77 FROM
78 JAI_OM_OE_BOND_REG_HDRS A, JAI_OM_OE_BOND_REG_DTLS b
79 WHERE
80 a.organization_id = p_organization_id AND
81 a.location_id = p_location_id AND
82 a.register_id = b.register_id AND
83 b.order_flag = 'Y' AND
84 b.order_type_id = p_order_type_id ;
85
86 CURSOR matched_receipt_cur1
87 IS
88 SELECT
89 sum(a.quantity_applied) quantity_applied
90 FROM
91 JAI_CMN_MATCH_RECEIPTS a
92 WHERE
93 a.ref_line_id = v_delivery_detail_id
94 AND a.order_invoice = 'O' ;
95
96 CURSOR get_item_attributes
97 IS
98 SELECT
99 excise_flag ,
100 modvat_flag ,
101 nvl(container_item_flag,'N'),
102 nvl(inventory_item_flag,'N')
103 FROM
104 mtl_system_items msi,
105 JAI_INV_ITM_SETUPS jmsi
106 WHERE msi.organization_id = jmsi.organization_id
107 AND msi.inventory_item_id = jmsi.inventory_item_id
108 AND jmsi.inventory_item_id = v_Inventory_Item_Id
109 AND jmsi.organization_id = v_organization_id;
110
111 CURSOR c_check_lc_order
112 IS
113 SELECT
114 lc_flag
115 FROM
116 JAI_OM_OE_SO_LINES
117 WHERE
118 lc_flag = 'Y' AND
119 header_id = pr_new.source_header_id;
120
121 CURSOR c_matched_qty_cur(cp_delivery_detail_id IN NUMBER)
122 IS
123 SELECT
124 sum(qty_matched)
125 FROM
126 JAI_OM_LC_MATCHINGS
127 WHERE
128 order_header_id = pr_new.source_header_id AND
129 delivery_detail_id = cp_delivery_detail_id AND
130 release_flag is null;
131
132 v_check_lc_order VARCHAR2(1);
133 v_lc_qty_matched NUMBER;
134 v_lc_shipped_qty NUMBER;
135
136 cursor c_order_line is
137 select split_from_line_id
138 from oe_order_lines_all
139 where line_id = pr_new.source_line_id;
140
141 cursor c_lc_mtch_dlry_line is
142 select order_line_id
143 from JAI_OM_LC_MATCHINGS
144 where delivery_detail_id = v_delivery_detail_id;
145 r_order_line c_order_line%rowtype;
146 r_lc_mtch_dlry_line c_lc_mtch_dlry_line%rowtype;
147
148 CURSOR c_ja_in_so_lines_tax_amt
149 IS
150 SELECT
151 tax_amount
152 FROM
153 JAI_OM_OE_SO_LINES
154 WHERE
155 line_id = pr_new.source_line_id AND
156 header_id = pr_new.source_header_id;
157
158 -- The following cursor gets the sum of tax amount for the line_id from JAI_OM_OE_SO_TAXES table
159 CURSOR c_ja_in_so_tax_lines_tax_amt
160 IS
161 SELECT
162 nvl(sum(so_tax.tax_amount),0)
163 FROM
164 JAI_OM_OE_SO_TAXES so_tax
165 , jai_cmn_taxes_all tax
166 WHERE
167 line_id = pr_new.source_line_id AND
168 header_id = pr_new.source_header_id
169 AND so_tax.tax_id = tax.tax_id
170 AND NVL(tax.inclusive_tax_flag,'N') = 'N' ;
171
172 v_line_tax_amount Number;
173 v_sum_tax_amount Number;
174
175 CURSOR c_chk_exc_exmpt_rule
176 IS
177 SELECT
178 a.excise_exempt_type ,
179 a.line_number ,
180 a.shipment_line_number ,
181 quantity
182 FROM
183 JAI_OM_OE_SO_LINES a
184 WHERE
185 a.line_id = pr_new.source_line_id AND
186 a.header_id = pr_new.source_header_id ;
187
188 lv_excise_exempt_type JAI_OM_OE_SO_LINES.EXCISE_EXEMPT_TYPE%TYPE ;
189 ln_line_number JAI_OM_OE_SO_LINES.LINE_NUMBER%TYPE ;
190 ln_shipment_line_number JAI_OM_OE_SO_LINES.SHIPMENT_LINE_NUMBER%TYPE ;
191 v_quantity number;
192
193 cursor c_orgn_info is
194 select trading
195 from JAI_CMN_INVENTORY_ORGS
196 where organization_id = pr_new.organization_id
197 and location_id = pr_new.ship_from_location_id ;
198
199 lv_process_flag VARCHAR2 (2);
200 lv_process_message VARCHAR2 (1000);
201
202 CURSOR cur_tot_shipped_quantity (cp_delivery_detail_id IN NUMBER)
203 IS
204 SELECT sum ( shipped_quantity)
205 FROM jai_wsh_del_details_gt
206 WHERE delivery_detail_id = cp_delivery_detail_id
207 OR split_from_delivery_detail_id = cp_delivery_detail_id;
208 v_tot_lc_shipped_qty NUMBER;
209 ln_unprocessed_recs NUMBER;
210
211 BEGIN
212 pv_return_code := jai_constants.successful ;
213
214 v_inventory_item_id :=pr_new.inventory_item_id ;
215 v_organization_id :=pr_new.organization_id ;
216 v_subinventory :=pr_new.subinventory ;
217 v_delivery_detail_id :=pr_new.delivery_detail_id ;
218 v_source_header_type_id :=pr_new.source_header_type_id ;
219 v_shipped_quantity := nvl(pr_new.shipped_quantity,0) ;
220 v_matched_qty := 0;
221 v_lc_shipped_qty := pr_new.Shipped_quantity;
222 v_line_tax_amount :=0;
223 v_sum_tax_amount :=0;
224
225
226 OPEN c_ja_in_so_lines_tax_amt;
227 FETCH c_ja_in_so_lines_tax_amt INTO v_line_tax_amount;
228 CLOSE c_ja_in_so_lines_tax_amt;
229
230 OPEN c_ja_in_so_tax_lines_tax_amt;
231 FETCH c_ja_in_so_tax_lines_tax_amt INTO v_sum_tax_amount;
232 CLOSE c_ja_in_so_tax_lines_tax_amt;
233
234 OPEN c_chk_exc_exmpt_rule;
235 FETCH c_chk_exc_exmpt_rule INTO lv_excise_exempt_type,ln_line_number,ln_shipment_line_number,v_quantity;
236 CLOSE c_chk_exc_exmpt_rule ;
237
238 OPEN location_cursor;
239 FETCH location_cursor INTO v_location_id, v_trading_flag , v_bonded;
240 CLOSE location_cursor;
241
242 OPEN trading_register_code_cur( v_organization_id ,
243 v_location_id ,
244 v_source_header_type_id
245 );
246 FETCH trading_register_code_cur INTO v_trad_register_code;
247 CLOSE trading_register_code_cur;
248
249 OPEN get_item_attributes;
250 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag,v_container_item_flag,lv_inventory_item_flag;
251 CLOSE get_item_attributes;
252
253 open c_orgn_info;
254 fetch c_orgn_info into v_trading_flag;
255 close c_orgn_info;
256
257 OPEN item_trading_cur;
258 FETCH item_trading_cur INTO v_item_trading_flag;
259 CLOSE item_trading_cur;
260
261 IF v_trad_register_code IN(
262 '23D_DOMESTIC_EXCISE' ,
263 '23D_EXPORT_EXCISE'
264 )
265 THEN
266
267 IF nvl(v_trading_flag,'N') = 'Y' AND nvl(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
268
269 OPEN matched_receipt_cur1;
270 FETCH matched_receipt_cur1 INTO v_matched_qty;
271 CLOSE matched_receipt_cur1;
272
273 IF nvl(pr_new.Released_status,'N') = 'C' THEN
274 IF nvl(v_shipped_quantity,0) <> nvl(v_matched_qty,0) THEN
275 pv_return_code := jai_constants.expected_error ;
276 pv_return_message := 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
277 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0));
278 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
279 FND_LOG.STRING(G_LEVEL_STATEMENT,
280 G_MODULE_NAME || v_api_name,
281 'Returned with ' || pv_return_code || pv_return_message);
282 END IF;
283 return ;
284 END IF;
285 END IF;
286
287 if nvl(pr_new.Released_status,'N') = 'B' then
288 if nvl(v_matched_qty,0) > 0 then
289 pv_return_code := jai_constants.expected_error ;
290 pv_return_message := 'Please Unmatch the Delivery prior to backordering ' ;
291 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
292 FND_LOG.STRING(G_LEVEL_STATEMENT,
293 G_MODULE_NAME || v_api_name,
294 'Returned with ' || pv_return_code || pv_return_message);
295 END IF;
296 return ;
297 end if ;
298 end if;
299 END IF;
300 END IF;
301
302 IF nvl(pr_new.Released_status,'N') = 'C' THEN
303
304 OPEN c_check_lc_order;
305 FETCH c_check_lc_order INTO v_check_lc_order;
306 CLOSE c_check_lc_order;
307
308 IF NVL(v_check_lc_order,'N') = 'Y' THEN
309 OPEN c_matched_qty_cur(pr_new.DELIVERY_DETAIL_ID) ;
310 FETCH c_matched_qty_cur INTO v_lc_qty_matched;
311 CLOSE c_matched_qty_cur;
312
313 IF v_lc_qty_matched IS NULL AND pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID IS NOT NULL THEN
314 OPEN c_matched_qty_cur (pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID);
315 FETCH c_matched_qty_cur INTO v_lc_qty_matched;
316 CLOSE c_matched_qty_cur;
317
318 END IF;
319 IF NVL(v_lc_qty_matched,-999) <> NVL(v_lc_shipped_qty,-888) THEN
320 IF pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID IS NULL THEN
321 OPEN cur_tot_shipped_quantity (pr_new.delivery_detail_id);
322 FETCH cur_tot_shipped_quantity INTO v_tot_lc_shipped_qty;
323 CLOSE cur_tot_shipped_quantity;
324 ELSE
325 OPEN cur_tot_shipped_quantity (pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID);
326 FETCH cur_tot_shipped_quantity INTO v_tot_lc_shipped_qty;
327 CLOSE cur_tot_shipped_quantity;
328 END IF;
329
330 IF NVL(v_lc_qty_matched,-999) <> NVL(v_tot_lc_shipped_qty,-888) THEN
331 pv_return_code := jai_constants.expected_error ;
332 pv_return_message := 'LC Matched Quantity -- ' || TO_CHAR(NVL(v_lc_qty_matched,0)) ||
333 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_lc_shipped_qty,0)) || ' for LC enabled Orders';
334 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
335 FND_LOG.STRING(G_LEVEL_STATEMENT,
336 G_MODULE_NAME || v_api_name,
337 'Returned with ' || pv_return_code || pv_return_message);
338 END IF;
339 return ;
340 END IF;
341 END IF;
342
343 Update jai_wsh_del_details_gt
344 set processed_flag = 'Y'
345 where delivery_detail_id = pr_new.delivery_detail_id;
346
347 open c_order_line;
348 fetch c_order_line into r_order_line;
349 close c_order_line;
350
351 if r_order_line.split_from_line_id is not null then
352 open c_lc_mtch_dlry_line;
353 fetch c_lc_mtch_dlry_line into r_lc_mtch_dlry_line;
354 close c_lc_mtch_dlry_line;
355 if pr_new.source_line_id <> r_lc_mtch_dlry_line.order_line_id then
356 update JAI_OM_LC_MATCHINGS
357 set order_line_id = pr_new.source_line_id
358 where delivery_detail_id = v_delivery_detail_id
359 and release_flag is null;
360 end if;
361 end if;
362 END IF;
363 END IF;
364
365 IF NVL(v_check_lc_order,'N') = 'Y' THEN
366 select count(1) into ln_unprocessed_recs from jai_wsh_del_details_gt
367 where processed_flag = 'N'
368 AND (delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id )
369 OR split_from_delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id));
370
371 IF ln_unprocessed_recs = 0 THEN
372 delete jai_wsh_del_details_gt
373 where processed_flag = 'Y'
374 and (delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id )
375 OR split_from_delivery_detail_id = nvl(pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,pr_new.delivery_detail_id));
376
377 END IF;
378 END IF;
379
380 EXCEPTION
381 WHEN le_error THEN
382 IF lv_process_flag = jai_constants.unexpected_error THEN
383 lv_process_message := substr (lv_process_message || ' Object = JAI_OM_WDD_PROCESSING_PKG.DELIVER_DETAILS ', 1,1999) ;
384 END IF;
385
386
387 fnd_message.set_name (application => 'JA',
388 name => 'JAI_GENERIC_MSG'
389 );
390
391 fnd_message.set_token ( token => 'MSG_TEXT',
392 value => lv_process_message
393 );
394
395
396 app_exception.raise_exception;
397
398 WHEN others THEN
399 fnd_message.set_name ( application => 'JA',
400 name => 'JAI_GENERIC_MSG'
401 );
402
403 fnd_message.set_token ( token => 'MSG_TEXT',
404 value => 'Exception Occured in ' || ' Object = JAI_OM_WDD_PROCESSING_PKG.DELIVER_DETAILS'||fnd_global.local_chr(10)||SQLERRM
405 );
406
407 app_exception.raise_exception;
408
409 END DELIVER_DETAILS ;
410
411 /*
412 REM +======================================================================+
413 REM NAME Process Interfaced
414 REM
415 REM DESCRIPTION Called from BE_INTERFACED
416 REM
417 REM NOTES Come from JAI_OM_WDD_TRIGGER_PKG.ARU_T3
418 REM
419 REM +======================================================================+
420 */
421 PROCEDURE PROCESS_INTERFACED (pr_old t_rec%type ,
422 pr_new t_rec%type ,
423 pv_action varchar2 ,
424 pv_return_code out nocopy varchar2 ,
425 pv_return_message out nocopy varchar2 )
426 IS
427 v_api_name CONSTANT VARCHAR2(30) := 'PROCESS_INTERFACED';
428 v_creation_date DATE;
429 v_created_by NUMBER;
430 v_last_update_date DATE ;
431 v_last_updated_by NUMBER;
432 v_last_update_login NUMBER;
433 v_delivery_detail_id NUMBER;
434 v_source_header_id NUMBER;
435 v_source_line_id NUMBER;
436 v_Inventory_Item_Id NUMBER;
437 v_Requested_Quantity_Uom VARCHAR2(3);
438 v_org_id NUMBER;
439 v_customer_id NUMBER;
440 v_source_header_type_id NUMBER ;
441 v_subinventory VARCHAR2(10);
442 v_shipped_quantity NUMBER;
443 v_Organization_Id NUMBER ;
444 v_trading_flag VARCHAR2(1);
445 v_status_code VARCHAR2(2);
446 v_so_lines_count NUMBER;
447 v_selling_price NUMBER;
448 v_tax_category_id NUMBER(15);
449 v_assessable_value NUMBER;
450 v_excise_exempt_type VARCHAR2(60);
451 v_excise_exempt_refno VARCHAR2(30);
452 v_excise_exempt_date DATE;
453 v_quantity NUMBER;
454 v_picking_tax_lines_count NUMBER;
455 v_tax_amount NUMBER;
456 v_base_tax_amount NUMBER;
457 v_func_tax_amount NUMBER;
458 v_basic_excise_duty_amount NUMBER;
459 v_add_excise_duty_amount NUMBER;
460 v_oth_excise_duty_amount NUMBER;
461 v_excise_amount NUMBER;
462 v_tax_amt NUMBER;
463 v_base_tax_amt NUMBER;
464 v_func_tax_amt NUMBER;
465 v_tot_tax_amount NUMBER;
466 v_delivery_line_count NUMBER;
467 v_location_id NUMBER;
468 v_delivery_id NUMBER;
469 v_ship_to_org_id NUMBER;
470 v_bill_to_org_id NUMBER;
471 ln_bill_to_cust_id NUMBER;
472 v_date_confirmed DATE;
473 counter NUMBER;
474 v_bonded_flag VARCHAR2(1);
475 v_register_code VARCHAR2(30);
476 v_fin_year NUMBER;
477 v_old_register JAI_OM_WSH_LINES_ALL.register%TYPE;
478 v_reg_type VARCHAR2(10);
479 v_tot_excise_amt NUMBER;
480 v_tot_basic_ed_amt NUMBER;
481 v_pref_rg23a NUMBER;
482 v_pref_rg23c NUMBER;
483 v_pref_pla NUMBER;
484 v_ssi_unit_flag VARCHAR2(1);
485 v_rg23a_balance NUMBER;
486 v_rg23c_balance NUMBER;
487 v_pla_balance NUMBER;
488 v_order_type_id NUMBER;
489 v_excise_flag VARCHAR2(1);
490 v_item_class JAI_INV_ITM_SETUPS.item_class%TYPE;
491 v_modvat_tax_rate NUMBER;
492 v_rounding_factor NUMBER;
493 v_exempt_bal NUMBER;
494 v_basic_ed_amt NUMBER;
495 v_register_balance NUMBER;
496 v_exe_flag VARCHAR2(150);
497 v_mod_flag VARCHAR2(150);
498 v_export_oriented_unit JAI_CMN_INVENTORY_ORGS.export_oriented_unit%TYPE;
499 v_basic_pla_balance NUMBER;
500 v_additional_pla_balance NUMBER;
501 v_other_pla_balance NUMBER;
502 ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
503 lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
504 lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
505 ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
506 lv_vat_exemption_refno JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_REFNO%TYPE;
507
508 CURSOR Get_Status_Cur IS
509 SELECT A.delivery_id,
510 A.confirm_date,
511 A.status_code
512 FROM Wsh_Delivery_Assignments B,
513 Wsh_New_deliveries A
514 WHERE B.Delivery_Id = A.Delivery_Id
515 AND B.Delivery_Detail_id = v_delivery_detail_id;
516
517 CURSOR get_item_attributes IS
518 SELECT excise_flag, modvat_flag
519 FROM JAI_INV_ITM_SETUPS
520 WHERE inventory_item_id = v_Inventory_Item_Id
521 AND organization_id = v_organization_id;
522
523 CURSOR Get_So_Lines_Count_Cur IS
524 SELECT COUNT(*)
525 FROM JAI_OM_OE_SO_LINES
526 WHERE Line_id = v_source_line_id;
527
528 CURSOR Get_So_Lines_Details_Cur IS
529 SELECT NVL(Selling_Price,0),
530 NVL(Quantity,0),
531 NVL(Tax_Category_Id,0),
532 NVL(Assessable_Value,0),
533 NVL(vat_assessable_value,0),
534 Excise_Exempt_Type,
535 Excise_Exempt_Refno,
536 Excise_Exempt_Date,
537 vat_exemption_flag,
538 vat_exemption_type,
539 vat_exemption_date,
540 vat_exemption_refno
541 FROM JAI_OM_OE_SO_LINES
542 WHERE Line_id = v_source_line_id;
543
544 CURSOR get_conv_detail_cur
545 IS
546 SELECT
547 transactional_curr_code ,
548 conversion_type_code ,
549 conversion_rate ,
550 nvl(b.actual_shipment_date,sysdate) actual_shipment_date
551 FROM
552 oe_order_headers_all a ,
553 oe_order_lines_all b
554 WHERE
555 a.header_id = b.header_id AND
556 b.line_id = v_source_line_id AND
557 a.header_id = v_source_header_id ;
558
559 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
560 v_set_of_books_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE ;
561 v_conv_type_code oe_order_headers_all.conversion_type_code%TYPE;
562 v_conv_rate NUMBER;
563 v_conv_date DATE;
564
565 v_curr_conv_rate NUMBER;
566
567 CURSOR Get_Tax_Lines_Details_Cur IS
568 SELECT b.Tax_Type,
569 NVL(b.Rounding_Factor,2) Rounding_Factor,
570 A.Tax_Line_No,
571 A.Precedence_1,
572 A.Precedence_2,
573 A.Precedence_3,
574 A.Precedence_4,
575 A.Precedence_5,
576 A.Precedence_6,
577 A.Precedence_7,
578 A.Precedence_8,
579 A.Precedence_9,
580 A.Precedence_10,
581 A.Tax_Id,
582 A.Tax_Rate,
583 A.Qty_Rate,
584 A.Uom,
585 A.Tax_Amount,
586 A.Base_Tax_Amount,
587 A.Func_Tax_Amount
588 FROM JAI_OM_OE_SO_TAXES A,
589 JAI_CMN_TAXES_ALL b
590 WHERE Line_id = v_source_line_id
591 AND A.Tax_Id = b.Tax_Id
592 ORDER BY A.Tax_Line_No;
593
594 CURSOR Pick_Tax_Line_Count_Cur(P_Tax_Id NUMBER) IS
595 SELECT COUNT(*)
596 FROM JAI_OM_WSH_LINE_TAXES
597 WHERE Delivery_Detail_Id = v_delivery_detail_id
598 AND Tax_Id = P_Tax_Id;
599
600 CURSOR Get_Tot_Tax_Amount_Cur IS
601 SELECT SUM(A.Tax_Amount)
602 FROM JAI_OM_WSH_LINE_TAXES A,
603 JAI_CMN_TAXES_ALL b
604 WHERE A.Delivery_Detail_Id = v_delivery_detail_id
605 AND b.Tax_Id = A.Tax_Id
606 AND b.Tax_Type <> 'TDS';
607
608 CURSOR Get_Delivery_Line_Count_Cur IS
609 SELECT COUNT(*)
610 FROM JAI_OM_WSH_LINES_ALL
611 WHERE Delivery_Detail_Id = v_delivery_detail_id
612 AND Delivery_id = v_delivery_id;
613
614 CURSOR Location_Cursor IS
615 SELECT NVL(Location_id,0),
616 trading
617 FROM JAI_INV_SUBINV_DTLS
618 WHERE Sub_Inventory_Name = v_subinventory
619 AND organization_id = v_organization_id;
620
621 CURSOR get_ship_to_org_id_cur( p_line_id NUMBER) IS
622 SELECT ship_to_org_id, invoice_to_org_id
623 FROM Oe_order_lines_all
624 WHERE line_id = p_line_id;
625
626 Cursor cur_get_bill_to_cust_id (cp_site_use_id IN NUMBER)
627 IS
628 SELECT customer_id
629 FROM oe_invoice_to_orgs_v
630 WHERE site_use_id = cp_site_use_id;
631
632 CURSOR rg23d_amount_cur(p_tax_id NUMBER) IS
633 SELECT nvl(sum(tax_amount),0) ,
634 nvl(sum(base_tax_amount),0),
635 nvl(sum(func_tax_amount),0)
636 FROM
637 JAI_CMN_MATCH_TAXES
638 WHERE
639 ref_line_id = v_delivery_detail_id AND
640 receipt_id IS NOT NULL AND
641 tax_id = p_tax_id;
642
643 CURSOR ed_cur (p_tax_type VARCHAR2)IS
644 SELECT
645 nvl(sum(a.func_tax_amount),0)
646 FROM
647 JAI_CMN_MATCH_TAXES a,
648 JAI_CMN_TAXES_ALL b
649 WHERE
650 a.tax_id = b.tax_id AND
651 b.tax_type = p_tax_type AND
652 A.ref_line_id = v_delivery_detail_id AND
653 A.receipt_id IS NOT NULL;
654
655 CURSOR item_trading_cur IS
656 SELECT
657 item_trading_flag
658 FROM
659 JAI_INV_ITM_SETUPS
660 WHERE
661 organization_id = v_organization_id AND
662 inventory_item_id = v_inventory_item_id;
663
664 v_item_trading_flag VARCHAR2(1);
665
666 CURSOR uom_code IS
667 SELECT order_quantity_uom
668 FROM oe_order_lines_all
669 WHERE line_id = v_source_line_id;
670 v_order_quantity_uom VARCHAR2(3);
671 v_conversion_rate NUMBER;
672
673 CURSOR Trading_register_code_cur(
674 p_organization_id NUMBER,
675 p_location_id NUMBER,
676 p_order_type_id NUMBER
677 ) IS
678 SELECT
679 a.register_code
680 FROM
681 JAI_OM_OE_BOND_REG_HDRS a,
682 JAI_OM_OE_BOND_REG_DTLS b
683 WHERE
684 A.organization_id = p_organization_id AND
685 A.location_id = p_location_id AND
686 A.register_id = b.register_id AND
687 b.order_flag = 'Y' AND
688 b.order_type_id = p_order_type_id AND
689 A.register_code LIKE '23D%';
690
691 v_trad_register_code VARCHAR2(30);
692
693 CURSOR matched_receipt_cur1 IS
694 SELECT
695
696 sum(a.quantity_applied) quantity_applied
697 FROM
698 JAI_CMN_MATCH_RECEIPTS a
699 WHERE
700 a.ref_line_id = v_delivery_detail_id;
701
702 v_matched_qty NUMBER;
703
704 CURSOR get_item_dtls (p_organization_id NUMBER,p_item_id NUMBER) IS
705 SELECT
706 excise_flag,
707 item_class
708 FROM
709 JAI_INV_ITM_SETUPS
710 WHERE
711 organization_id = p_organization_id AND
712 inventory_item_id = p_item_id;
713
714 CURSOR bonded_cur(p_organization_id NUMBER, p_subinventory VARCHAR2) IS
715 SELECT NVL(A.bonded,'Y') bonded
716 FROM JAI_INV_SUBINV_DTLS A
717 WHERE A.sub_inventory_name = p_subinventory
718 AND A.organization_id = p_organization_id;
719
720 CURSOR fin_year_cur(p_organization_id IN NUMBER) IS
721 SELECT MAX(A.fin_year)
722 FROM JAI_CMN_FIN_YEARS A
723 WHERE organization_id = p_organization_id
724 AND fin_active_flag = 'Y';
725
726 CURSOR pref_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
727 SELECT pref_rg23a,
728 pref_rg23c,
729 pref_pla,
730 NVL(Export_oriented_unit ,'N')
731 FROM JAI_CMN_INVENTORY_ORGS
732 WHERE organization_id = p_organization_id
733 AND location_id = p_location_id ;
734
735 CURSOR rg_bal_cur(p_organization_id NUMBER, p_location_id NUMBER)IS
736 SELECT NVL(rg23a_balance,0) rg23a_balance ,
737 NVL(rg23c_balance,0) rg23c_balance,
738 NVL(pla_balance,0) pla_balance,
739 NVL(basic_pla_balance,0) basic_pla_balance,
740 NVL(additional_pla_balance,0) additional_pla_balance,
741 NVL(other_pla_balance,0) other_pla_balance
742 FROM JAI_CMN_RG_BALANCES
743 WHERE organization_id = p_organization_id
744 AND location_id = p_location_id ;
745
746 CURSOR ssi_unit_flag_cur(p_organization_id NUMBER, p_location_id NUMBER) IS
747 SELECT ssi_unit_flag
748 FROM JAI_CMN_INVENTORY_ORGS
749 WHERE organization_id = p_organization_id
750 AND location_id = p_location_id;
751
752 CURSOR Get_Tax_Lines_Details_Cur1 IS
753 SELECT A.Tax_Rate, NVL(b.Rounding_Factor,0) Rounding_Factor
754 FROM JAI_OM_OE_SO_TAXES A, JAI_CMN_TAXES_ALL b
755 WHERE Line_id = v_source_line_id
756 AND A.Tax_Id = b.Tax_Id
757 AND b.tax_type = 'Modvat Recovery'
758 ORDER BY A.Tax_Line_No;
759
760 CURSOR for_modvat_percentage(p_organization_id NUMBER, p_location_id NUMBER) IS
761 SELECT MODVAT_REVERSE_PERCENT
762 FROM JAI_CMN_INVENTORY_ORGS
763 WHERE organization_id = p_organization_id
764 AND ( location_id = p_location_id OR
765 location_id is NULL AND p_location_id is NULL);
766
767 v_asst_register_id Number;
768 v_reg_exp_date Date;
769 v_lou_flag Varchar2(1);
770
771 CURSOR c_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
772 SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
773 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
774 JAI_CMN_TAXES_ALL jtc
775 WHERE jtc.tax_id = jsptl.tax_id
776 AND delivery_detail_id in
777 (SELECT delivery_detail_id
778 FROM JAI_OM_WSH_LINES_ALL
779 WHERE delivery_id = cp_delivery_id
780 )
781 AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
782
783 ln_sh_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
784
785 CURSOR c_sh_cess_amount (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE) IS
786 SELECT nvl(sum(jsptl.func_tax_amount),0) tax_amount
787 FROM JAI_OM_WSH_LINE_TAXES jsptl ,
788 JAI_CMN_TAXES_ALL jtc
789 WHERE jtc.tax_id = jsptl.tax_id
790 AND delivery_detail_id in
791 (SELECT delivery_detail_id
792 FROM JAI_OM_WSH_LINES_ALL
793 WHERE delivery_id = cp_delivery_id
794 )
795 AND upper(jtc.tax_type) in (upper(jai_constants.tax_type_sh_exc_edu_cess),
796 upper(jai_constants.tax_type_sh_cvd_edu_cess)
797 );
798
799 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
800 lv_process_flag VARCHAR2(5);
801 lv_process_message VARCHAR2(1996);
802
803 ln_vat_cnt NUMBER DEFAULT 0 ;
804 ln_vat_proc_cnt NUMBER DEFAULT 0 ;
805 ln_regime_id JAI_RGM_ORG_REGNS_V.REGIME_ID%TYPE;
806 lv_regns_num JAI_RGM_ORG_REGNS_V.ATTRIBUTE_VALUE%TYPE;
807
808 CURSOR cur_chk_vat_exists (cp_del_det_id JAI_OM_WSH_LINES_ALL.DELIVERY_DETAIL_ID%TYPE)
809 IS
810 SELECT 1
811 FROM JAI_OM_WSH_LINE_TAXES jsptl,
812 JAI_CMN_TAXES_ALL jtc
813 , jai_regime_tax_types_v tax_types
814 WHERE jsptl.delivery_detail_id = cp_del_det_id
815 AND jtc.tax_id = jsptl.tax_id
816 AND jtc.tax_type = tax_types.tax_type
817 AND tax_types.regime_code = jai_constants.vat_regime;
818
819 CURSOR cur_get_regime_info (cp_organization_id JAI_RGM_ORG_REGNS_V.ORGANIZATION_ID%TYPE ,
820 cp_location_id JAI_RGM_ORG_REGNS_V.LOCATION_ID%TYPE
821 )
822 IS
823 SELECT regime_id,
824 attribute_value
825 FROM JAI_RGM_ORG_REGNS_V orrg
826 WHERE orrg.organization_id = cp_organization_id
827 AND orrg.location_id = cp_location_id
828 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
829 AND attribute_code = jai_constants.attr_code_regn_no
830 AND regime_code = jai_constants.vat_regime;
831
832 CURSOR cur_chk_vat_proc_entry (cp_delivery_id JAI_OM_WSH_LINES_ALL.DELIVERY_ID%TYPE)
833 IS
834 SELECT 1
835 FROM JAI_RGM_INVOICE_GEN_T
836 WHERE delivery_id = cp_delivery_id;
837
838 /*
839 || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
840 */
841 CURSOR c_chk_vat_reversal (cp_del_det_id JAI_OM_WSH_LINES_ALL.delivery_detail_id%TYPE,
842 cp_tax_type JAI_CMN_TAXES_ALL.tax_type%TYPE )
843 IS
844 SELECT 1
845 FROM JAI_OM_WSH_LINE_TAXES jsptl,
846 JAI_CMN_TAXES_ALL jtc
847 WHERE jsptl.delivery_detail_id = cp_del_det_id
848 AND jtc.tax_id = jsptl.tax_id
849 AND jtc.tax_type = cp_tax_type ;
850
851 ln_vat_reversal_exists NUMBER ;
852 lv_vat_reversal VARCHAR2(100);
853 lv_vat_invoice_no VARCHAR2(10);
854 lv_vat_inv_gen_status VARCHAR2(10);
855 ln_tcs_exists number;
856 ln_threshold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
857 ln_tcs_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
858 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%type;
859 ln_last_line_no number;
860 ln_base_line_no number;
861
862 CURSOR C_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
863 IS
864 SELECT REGIME_ID
865 FROM JAI_RGM_DEFINITIONS
866 WHERE REGIME_CODE = CP_REGIME_CODE;
867
868 /** Check if taxes with taxType as defined in the regime setup exists for given regime code */
869 CURSOR C_CHK_RGM_TAX_EXISTS ( CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE
870 , CP_DELIVERY_DETAIL_ID JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE)
871 IS
872 SELECT COUNT(1)
873 FROM JAI_REGIME_TAX_TYPES_V JRTTV
874 , JAI_OM_WSH_LINE_TAXES JSPT
875 , JAI_CMN_TAXES_ALL JTC
876 WHERE JTC.TAX_ID = JSPT.TAX_ID
877 AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
878 AND REGIME_CODE = CP_REGIME_CODE
879 AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
880
881 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
882
883
884 BEGIN
885 pv_return_code := jai_constants.successful ;
886
887 v_creation_date := sysdate;
888 v_created_by :=pr_new.Created_By;
889 v_last_update_date :=pr_new.Last_Update_Date;
890 v_last_updated_by :=pr_new.Last_Updated_By;
891 v_last_update_login :=pr_new.Last_Update_Login;
892 v_delivery_detail_id :=pr_new.Delivery_Detail_Id;
893 v_source_header_id :=pr_new.Source_Header_Id;
894 v_source_line_id :=pr_new.Source_Line_Id;
895 v_Inventory_Item_Id :=pr_new.Inventory_Item_Id;
896 v_Requested_Quantity_Uom :=pr_new.Requested_Quantity_Uom;
897 v_org_id :=pr_new.ORG_ID;
898 v_customer_id :=pr_new.CUSTOMER_ID;
899 v_source_header_type_id :=pr_new.SOURCE_HEADER_TYPE_ID;
900 v_subinventory :=pr_new.SUBINVENTORY;
901 v_shipped_quantity := NVL(pr_new.Shipped_Quantity,0);
902 v_Organization_Id :=pr_new.Organization_Id;
903 v_basic_excise_duty_amount := 0;
904 v_add_excise_duty_amount := 0;
905 v_oth_excise_duty_amount := 0;
906 v_excise_amount := 0;
907 v_tax_amt := 0;
908 v_base_tax_amt := 0;
909 v_func_tax_amt := 0;
910 counter :=0;
911 v_conversion_rate := 0;
912 v_matched_qty := 0;
913
914 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
915 FND_LOG.STRING(G_LEVEL_PROCEDURE,
916 G_MODULE_NAME || v_api_name || '.BEGIN',
917 G_PKG_NAME || ': ' || v_api_name || '()+');
918 END IF;
919
920 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
921 FND_LOG.STRING(G_LEVEL_STATEMENT,
922 G_MODULE_NAME || v_api_name,
923 'The TIME Stamp this ENTRY IS Created IS ' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
924 END IF;
925
926 OPEN get_ship_to_org_id_cur( v_source_line_id);
927 FETCH get_ship_to_org_id_cur INTO v_ship_to_org_id , v_bill_to_org_id;
928 CLOSE get_ship_to_org_id_cur;
929
930 OPEN cur_get_bill_to_cust_id(v_bill_to_org_id);
931 FETCH cur_get_bill_to_cust_id INTO ln_bill_to_cust_id;
932 CLOSE cur_get_bill_to_cust_id;
933
934 /*
935 The trigger should be bypassed in case the functional currency code is NON INR.
936 */
937 IF pr_new.org_id IS NOT NULL THEN
938 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
939 (p_org_id => pr_new.org_id);
940 v_currency_code := l_func_curr_det.currency_code;
941 v_set_of_books_id := l_func_curr_det.ledger_id;
942 END IF;
943
944 -- Check for the Delivery Status
945 OPEN Get_Status_Cur;
946 FETCH Get_Status_Cur INTO v_delivery_id, v_date_confirmed, v_status_code;
947 CLOSE Get_Status_Cur;
948 IF NVL(v_status_code,'#') NOT IN ('CO', 'IT','CL') THEN
949 RETURN;
950 END IF;
951
952 IF NVL(pr_new.shipped_quantity,0) = 0 THEN
953 RETURN;
954 END IF;
955 -- Check whether Line Details exists in Localization table.
956 OPEN Get_So_Lines_Count_Cur;
957 FETCH Get_So_Lines_Count_Cur INTO v_so_lines_count;
958 CLOSE Get_So_Lines_Count_Cur ;
959 IF v_so_lines_count = 0 THEN
960 RETURN;
961 END IF;
962 -- Fetch Lines Details from Localization Table
963 OPEN Get_So_Lines_Details_Cur;
964 FETCH Get_So_Lines_Details_Cur INTO
965 v_selling_price,
966 v_quantity,
967 v_tax_category_id,
968 v_assessable_value,
969 ln_vat_assessable_value,
970 v_excise_exempt_type,
971 v_excise_exempt_refno,
972 v_excise_exempt_date,
973 lv_vat_exemption_flag,
974 lv_vat_exemption_type,
975 ld_vat_exemption_date,
976 lv_vat_exemption_refno;
977 CLOSE Get_So_Lines_Details_Cur;
978 --Get The Location Id
979 OPEN Location_Cursor;
980 FETCH Location_Cursor INTO v_location_id, v_trading_flag;
981 CLOSE Location_Cursor;
982
983 IF v_location_id IS NULL THEN
984 v_location_id := pr_new.ship_from_location_id ;
985 END IF;
986
987 OPEN Trading_register_code_cur(v_organization_id, v_location_id, v_source_header_type_id);
988 FETCH Trading_register_code_cur INTO v_trad_register_code;
989 CLOSE Trading_register_code_cur;
990
991 OPEN item_trading_cur;
992 FETCH item_trading_cur INTO v_item_trading_flag;
993 CLOSE item_trading_cur;
994 OPEN get_item_attributes;
995 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag;
996 CLOSE get_item_attributes;
997
998 IF v_trad_register_code IN(
999 '23D_DOMESTIC_EXCISE',
1000 '23D_EXPORT_EXCISE'
1001 )
1002 THEN
1003
1004 IF NVL(v_trading_flag,'N') = 'Y' AND NVL(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
1005 OPEN matched_receipt_cur1;
1006 FETCH matched_receipt_cur1 INTO v_matched_qty;
1007 CLOSE matched_receipt_cur1;
1008 IF NVL(v_shipped_quantity,0) <> NVL(v_matched_qty,0) THEN
1009 pv_return_code := jai_constants.expected_error ;
1010 pv_return_message := 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
1011 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)) ;
1012 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1013 FND_LOG.STRING(G_LEVEL_STATEMENT,
1014 G_MODULE_NAME || v_api_name,
1015 'Returned with ' || pv_return_code || pv_return_message);
1016 END IF;
1017 return ;
1018
1019 END IF;
1020 END IF;
1021 END IF;
1022
1023 OPEN get_conv_detail_cur;
1024 FETCH get_conv_detail_cur INTO v_currency_code, v_conv_type_code,v_conv_rate, v_conv_date;
1025
1026 IF get_conv_detail_cur%FOUND THEN
1027
1028 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion (
1029 v_set_of_books_id ,
1030 v_currency_code ,
1031 v_conv_date ,
1032 v_conv_type_code ,
1033 v_conv_rate
1034 );
1035 IF v_curr_conv_rate IS NULL
1036 THEN
1037 lv_process_message := 'Currency Conversion on shipment date not setup.';
1038 app_exception.raise_exception
1039 (exception_type => 'APP'
1040 ,exception_code => -20275
1041 ,exception_text => lv_process_message
1042 );
1043 END IF;
1044 END IF;
1045
1046 CLOSE get_conv_detail_cur;
1047
1048 FOR Rec IN Get_Tax_Lines_Details_Cur
1049 LOOP
1050 counter:=counter+1;
1051
1052 OPEN uom_code;
1053 FETCH uom_code INTO v_order_quantity_uom;
1054 CLOSE uom_code;
1055
1056 /*As the tax amount, base_tax_amount and func_tax_amount would be present in table JAI_CMN_MATCH_TAXES
1057 only when match receipt functionality has been done .
1058 Now in order to check that the match receipts functionality has been performed the following check has been added in additions
1059 to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
1060 The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
1061 hence applying the check that
1062 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
1063 Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINE_TAXES for Bond register with
1064 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
1065 */
1066 IF NVL(v_trading_flag,'N') = 'Y' AND
1067 NVL(v_item_trading_flag,'N') = 'Y' AND
1068 NVL(v_exe_flag,'N') = 'Y' AND
1069 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
1070 THEN
1071
1072 OPEN rg23d_amount_cur(rec.tax_id);
1073 FETCH rg23d_amount_cur INTO v_tax_amt,v_base_tax_amt,v_func_tax_amt;
1074 CLOSE rg23d_amount_cur;
1075
1076 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1077 FND_LOG.STRING(G_LEVEL_STATEMENT,
1078 G_MODULE_NAME || v_api_name,
1079 'Delivery detail id ' || v_delivery_Detail_id ||
1080 ' Currency Conversion rate --' || v_curr_conv_rate);
1081 FND_LOG.STRING(G_LEVEL_STATEMENT,
1082 G_MODULE_NAME || v_api_name,
1083 'Tax type --'|| rec.tax_type ||
1084 ' tax id '||rec.tax_id ||
1085 ' tax_amount '|| v_tax_amt ||
1086 ' functional tax amount '|| v_func_tax_amt||
1087 ' v_base_tax_amt '||v_base_tax_amt);
1088 END IF;
1089
1090 if upper(rec.tax_type) like '%EXCISE%'
1091 then
1092 /**for excise type of taxes func_tax_amt is in fixed INR and it wont change based on the exchage rate.
1093 Transaction amount will be changed based on the exchange rate**/
1094 v_tax_amt := (v_func_tax_amt * 1/ nvl(v_curr_conv_rate,1)) ;
1095 v_base_tax_amt := v_tax_amt;
1096
1097 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1098 FND_LOG.STRING(G_LEVEL_STATEMENT,
1099 G_MODULE_NAME || v_api_name,
1100 'After conversion excise transaction tax amount '||v_tax_amt);
1101 END IF;
1102 else
1103 --for other type of taxes functional tax amounts will be changing
1104 v_func_tax_amt := (v_tax_amt * nvl(v_curr_conv_rate,1)) ;
1105
1106 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1107 FND_LOG.STRING(G_LEVEL_STATEMENT,
1108 G_MODULE_NAME || v_api_name,
1109 'After conversion other taxes functional tax amount '||v_func_tax_amt);
1110 END IF;
1111 end if;
1112 v_conversion_rate := 1;
1113 ELSE
1114 /*
1115 this control comes here for manufacturing - all scenarios
1116 */
1117 v_tax_amt := (v_shipped_quantity * (rec.tax_amount/v_quantity)) ;
1118 v_base_tax_amt := (v_shipped_quantity * (rec.base_tax_amount/v_quantity)) ;
1119 /*
1120 As the Conversion rate can be different while the sales order was booked and when the sales order would be shipped.
1121 So during shipping the functional tax amount needs to be recalculated from the tax_amount column in JAI_OM_OE_SO_TAXES
1122 , hence setting the v_func_tax_amt = v_tax_amt * nvl((v_curr_conv_rate ,1)
1123 */
1124 v_func_tax_amt := (v_tax_amt * nvl(v_curr_conv_rate,1)) ;
1125
1126 -- Proportionate the Tax Amounts as per the New Shipped Quantity
1127 -- and round it off according to the Rounding Factor Defined.
1128
1129 /*included the UOM conversion logic into the else part Earlier it was outside the end of If condition.*/
1130 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
1131 v_order_quantity_uom,
1132 v_inventory_item_id,
1133 v_conversion_rate);
1134 IF NVL(v_conversion_rate, 0) <= 0 THEN
1135 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
1136 v_order_quantity_uom,
1137 0,
1138 v_conversion_rate);
1139 IF NVL(v_conversion_rate, 0) <= 0 THEN
1140 v_conversion_rate := 0;
1141 END IF;
1142 END IF;
1143 END IF;
1144 -- this was done because - it will ensure that Line splitting \ Backordering is not supported only
1145 -- when the Organization as well as Item are both Trading which is a
1146 -- requirement when match receipt funtionality for RG23D is being used.
1147 v_tax_amount := ROUND((v_tax_amt) * v_conversion_rate,rec.rounding_factor);
1148 v_base_tax_amount := ROUND((v_base_tax_amt) * v_conversion_rate,rec.rounding_factor);
1149
1150 IF NVL(v_trading_flag,'N') = 'Y' AND
1151 NVL(v_item_trading_flag,'N') = 'Y' AND
1152 NVL(v_exe_flag,'N') = 'Y' AND
1153 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') AND
1154 upper(Rec.Tax_type) like '%EXCISE%' THEN
1155 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate, rec.rounding_factor);
1156 ELSE
1157 IF v_curr_conv_rate <> 1 AND upper(rec.tax_type) like '%EXCISE%' THEN
1158 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate,0);
1159 ELSE
1160 v_func_tax_amount := ROUND((v_func_tax_amt)* v_conversion_rate, rec.rounding_factor);
1161 END IF;
1162 END IF;
1163
1164 -- Accumulate the respective types of Excise Duties
1165 -- for inserting into JAI_OM_WSH_LINES_ALL Table.
1166 IF rec.tax_type = 'Excise' THEN
1167 v_basic_excise_duty_amount := NVL(v_basic_excise_duty_amount,0) + v_tax_amount ;
1168 ELSIF rec.tax_type = 'Addl. Excise' THEN
1169 v_add_excise_duty_amount := NVL(v_add_excise_duty_amount,0) + v_tax_amount ;
1170 ELSIF rec.tax_type = 'Other Excise' THEN
1171 v_oth_excise_duty_amount := NVL(v_oth_excise_duty_amount,0) + v_tax_amount ;
1172 END IF;
1173
1174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1175 FND_LOG.STRING(G_LEVEL_STATEMENT,
1176 G_MODULE_NAME || v_api_name,
1177 '1 v_basic_excise_duty_amount -> ' || v_basic_excise_duty_amount ||
1178 ', v_add_excise_duty_amount -> '|| v_add_excise_duty_amount ||
1179 ', v_oth_excise_duty_amount -> '|| v_oth_excise_duty_amount);
1180 END IF;
1181
1182 -- Check for the existence of Tax Lines in JAI_OM_WSH_LINE_TAXES
1183 OPEN Pick_Tax_Line_Count_Cur(rec.tax_id);
1184 FETCH Pick_Tax_Line_Count_Cur INTO v_picking_tax_lines_count;
1185 CLOSE Pick_Tax_Line_Count_Cur;
1186 IF v_picking_tax_lines_count = 0 THEN
1187 INSERT INTO JAI_OM_WSH_LINE_TAXES(Delivery_Detail_Id,
1188 Tax_Line_No,
1189 Precedence_1,
1190 Precedence_2,
1191 Precedence_3,
1192 Precedence_4,
1193 Precedence_5,
1194 Precedence_6,
1195 Precedence_7,
1196 Precedence_8,
1197 Precedence_9,
1198 Precedence_10,
1199 Tax_Id,
1200 Tax_Rate,
1201 Qty_Rate,
1202 Uom,
1203 Tax_Amount,
1204 Base_Tax_Amount,
1205 Func_Tax_Amount,
1206 Creation_Date,
1207 Created_By,
1208 Last_Update_Date,
1209 Last_Updated_By,
1210 Last_Update_Login)
1211 VALUES (
1212 v_delivery_detail_id,
1213 rec.Tax_Line_No,
1214 rec.Precedence_1,
1215 rec.Precedence_2,
1216 rec.Precedence_3,
1217 rec.Precedence_4,
1218 rec.Precedence_5,
1219 rec.Precedence_6,
1220 rec.Precedence_7,
1221 rec.Precedence_8,
1222 rec.Precedence_9,
1223 rec.Precedence_10,
1224 rec.Tax_id,
1225 rec.Tax_rate,
1226 rec.Qty_Rate,
1227 rec.Uom,
1228 v_tax_amount,
1229 v_base_tax_amount,
1230 v_func_tax_amount,
1231 v_creation_date,
1232 v_created_by,
1233 v_last_update_date,
1234 v_last_updated_by,
1235 v_last_update_login
1236 );
1237 ELSE
1238 UPDATE JAI_OM_WSH_LINE_TAXES
1239 SET Tax_Amount = v_tax_amount,
1240 base_tax_amount = v_base_tax_amount,
1241 func_tax_amount = v_func_tax_amount,
1242 Last_Update_Date = v_last_update_date,
1243 Last_Updated_By = v_last_updated_by,
1244 Last_Update_Login = v_last_update_login
1245 WHERE Delivery_Detail_Id = v_delivery_detail_id
1246 AND Tax_Id = rec.Tax_Id;
1247 END IF;
1248 END LOOP;
1249
1250 /**
1251 Aim: Populate TCS Surcharge and Surcharge cess type of taxes if threshold level is high.
1252
1253 Check if TCS type of taxes exists, If yes using the threshold API found out the slab and the tax category id
1254 and delegate the call tax defaultation API
1255 */
1256 ln_tcs_exists := 0;
1257 open c_chk_rgm_tax_exists ( cp_regime_code => jai_constants.tcs_regime
1258 , cp_delivery_detail_id => v_delivery_detail_id);
1259 fetch c_chk_rgm_tax_exists into ln_tcs_exists;
1260 close c_chk_rgm_tax_exists ;
1261
1262 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1263 FND_LOG.STRING(G_LEVEL_STATEMENT,
1264 G_MODULE_NAME || v_api_name,
1265 'tcs regime code :' || jai_constants.tcs_regime ||
1266 ', delivery_detail_id :' || v_delivery_detail_id ||
1267 ', ln_tcs_exists :' || ln_tcs_exists);
1268 END IF;
1269
1270 if nvl(ln_tcs_exists,0) >0 then
1271 /* TCS type of tax is present */
1272 fnd_file.put_line(FND_FILE.LOG,'Localization' );
1273
1274 open c_get_regime_id (cp_regime_code => jai_constants.tcs_regime);
1275 fetch c_get_regime_id into ln_tcs_regime_id;
1276 close c_get_regime_id;
1277
1278 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
1279 (
1280 p_regime_id => ln_tcs_regime_id
1281 , p_organization_id => v_organization_id
1282 , p_party_type => jai_constants.party_type_customer
1283 , p_party_id => v_customer_id
1284 , p_org_id => v_org_id
1285 , p_source_trx_date => v_date_confirmed
1286 , p_threshold_slab_id => ln_threshold_slab_id
1287 , p_process_flag => lv_process_flag
1288 , p_process_message => lv_process_message
1289 );
1290
1291 if lv_process_flag <> jai_constants.successful then
1292 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1293 FND_LOG.STRING(G_LEVEL_STATEMENT,
1294 G_MODULE_NAME || v_api_name,
1295 'Error in jai_rgm_thhold_proc_pkg.get_threshold_slab_id. ' || lv_process_message);
1296 END IF;
1297 app_exception.raise_exception
1298 (exception_type => 'APP'
1299 ,exception_code => -20275
1300 ,exception_text => lv_process_message
1301 );
1302 end if;
1303
1304 if ln_threshold_slab_id is not null then
1305 /* Threshold level is up. Surcharge needs to be defaulted , so find out the tax category based on the threshold slab */
1306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1307 FND_LOG.STRING(G_LEVEL_STATEMENT,
1308 G_MODULE_NAME || v_api_name,
1309 'Before jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id.' ||
1310 'ln_threshold_slab_id :' || ln_threshold_slab_id);
1311 END IF;
1312 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
1313 (
1314 p_threshold_slab_id => ln_threshold_slab_id
1315 , p_org_id => v_org_id
1316 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
1317 , p_process_flag => lv_process_flag
1318 , p_process_message => lv_process_message
1319 );
1320
1321 if lv_process_flag <> jai_constants.successful then
1322 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1323 FND_LOG.STRING(G_LEVEL_STATEMENT,
1324 G_MODULE_NAME || v_api_name,
1325 'Error in jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id. ' || lv_process_message);
1326 END IF;
1327 app_exception.raise_exception
1328 (exception_type => 'APP'
1329 ,exception_code => -20275
1330 ,exception_text => lv_process_message
1331 );
1332 end if;
1333
1334 /* Get line number after which threshold taxes needs to be defaulted */
1335 select max(tax_line_no)
1336 into ln_last_line_no
1337 from JAI_OM_WSH_LINE_TAXES
1338 where delivery_detail_id = v_delivery_detail_id;
1339
1340 /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
1341 select max(tax_line_no)
1342 into ln_base_line_no
1343 from JAI_OM_WSH_LINE_TAXES jsptl
1344 , JAI_CMN_TAXES_ALL jtc
1345 where jsptl.delivery_detail_id = v_delivery_detail_id
1346 and jsptl.tax_id = jtc.tax_id
1347 and jtc.tax_type = jai_constants.tax_type_tcs;
1348
1349 /*
1350 ||Call the helper method to default surcharge taxes on top of the SO taxes using the tax category
1351 || The api jai_rgm_thhold_proc_pkg.default_thhold_taxes inserts lines as per the same specified in the TCS tax category
1352 || into the JAI_OM_WSH_LINE_TAXES table
1353 */
1354
1355 ln_vat_assessable_value := nvl(ln_vat_assessable_value * v_shipped_quantity / v_quantity, 0);
1356
1357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1358 FND_LOG.STRING(G_LEVEL_STATEMENT,
1359 G_MODULE_NAME || v_api_name,
1360 'Before jai_rgm_thhold_proc_pkg.default_thhold_taxes.' ||
1361 'ln_threshold_tax_cat_id :' || ln_threshold_tax_cat_id ||
1362 'ln_vat_assessable_value :' || ln_vat_assessable_value);
1363 END IF;
1364
1365 jai_rgm_thhold_proc_pkg.default_thhold_taxes
1366 (
1367 p_source_trx_id => ''
1368 , p_source_trx_line_id => v_delivery_detail_id
1369 , p_source_event => jai_constants.source_ttype_delivery
1370 , p_action => jai_constants.default_taxes
1371 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
1372 , p_tax_base_line_number => ln_base_line_no
1373 , p_last_line_number => ln_last_line_no
1374 , p_currency_code => v_currency_code
1375 , p_currency_conv_rate => v_conv_rate
1376 , p_quantity => nvl(v_shipped_quantity,0)
1377 , p_base_tax_amt => nvl((v_selling_price * v_conversion_rate) * v_shipped_quantity,0)
1378 , p_assessable_value => nvl(v_assessable_value * v_shipped_quantity, 0)
1379 , p_inventory_item_id => v_inventory_item_id
1380 , p_uom_code => v_order_quantity_uom
1381 , p_vat_assessable_value => ln_vat_assessable_value
1382 , p_process_flag => lv_process_flag
1383 , p_process_message => lv_process_message
1384 );
1385
1386 if lv_process_flag <> jai_constants.successful then
1387 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1388 FND_LOG.STRING(G_LEVEL_STATEMENT,
1389 G_MODULE_NAME || v_api_name,
1390 'Error in jai_rgm_thhold_proc_pkg.default_thhold_taxes. ' || lv_process_message);
1391 END IF;
1392 app_exception.raise_exception
1393 (exception_type => 'APP'
1394 ,exception_code => -20275
1395 ,exception_text => lv_process_message
1396 );
1397 end if;
1398
1399 end if; /* ln_threshold_slab_id is not null then */
1400
1401 end if; /** ln_tcs_exists is not null then */
1402
1403 -- This Code was Added because - in case there are items which do not have tax lines , the
1404 -- v_conversion_rate variable is not getting populated - so the selling price and the
1405 -- assessable value fields are being multiplied by the v_conversion_rate which is 0 initially.
1406 -- hence the addition of the following lines ensures the v_conversion_rate is calculated ,
1407 -- multiplied and correctly done with selling price and assessable value.
1408 OPEN uom_code;
1409 FETCH uom_code INTO v_order_quantity_uom;
1410 CLOSE uom_code;
1411 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
1412 v_order_quantity_uom,
1413 v_inventory_item_id,
1414 v_conversion_rate);
1415 IF NVL(v_conversion_rate, 0) <= 0 THEN
1416 Inv_Convert.inv_um_conversion(v_Requested_Quantity_Uom,
1417 v_order_quantity_uom,
1418 0,
1419 v_conversion_rate);
1420 IF NVL(v_conversion_rate, 0) <= 0 THEN
1421 v_conversion_rate := 0;
1422 END IF;
1423 END IF;
1424
1425 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
1426 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
1427 CLOSE get_item_dtls;
1428
1429 /*
1430 As the tax amount, base_tax_amount and func_tax_amount would be present in table JAI_CMN_MATCH_TAXES
1431 only when match receipt functionality has been done .
1432 Now in order to check that the match receipts functionality has been performed the following check has been added in additions
1433 to the other three checks (of organization being a trading organization, Item being tradable and excisable ):-
1434 The order attached to the bond register is one of 'Trading Domestic With Excise' or 'Export With Excise'
1435 hence applying the check that
1436 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE') .
1437 Before this fix, Null value used to get inserted into taxes in table JAI_OM_WSH_LINES_ALL for Bond register with
1438 'Trading Domestic Without Excise' and 'Export Without Excise' ( problem stated in the bug )
1439 */
1440 IF nvl(v_trading_flag,'N') = 'Y' AND
1441 nvl(v_item_trading_flag,'N') = 'Y' AND
1442 nvl(v_excise_flag,'N') = 'Y' AND
1443 v_trad_register_code IN ( '23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE')
1444 THEN
1445 OPEN ed_cur('Excise');
1446 FETCH ed_cur INTO v_basic_excise_duty_amount;
1447 CLOSE ed_cur ;
1448 OPEN ed_cur('Addl. Excise');
1449 FETCH ed_cur INTO v_add_excise_duty_amount;
1450 CLOSE ed_cur;
1451 OPEN ed_cur('Other Excise');
1452 FETCH ed_cur INTO v_oth_excise_duty_amount;
1453 CLOSE ed_cur;
1454 END IF;
1455 --Get Cumulative Excise amount
1456 --for inserting into JAI_OM_WSH_LINES_ALL Table.
1457 v_excise_amount := (v_basic_excise_duty_amount + v_add_excise_duty_amount +
1458 v_oth_excise_duty_amount);
1459 -- Get Total Tax Amount for the Line
1460 -- for Inserting into JAI_OM_WSH_LINES_ALL Table.
1461 OPEN Get_Tot_Tax_Amount_Cur;
1462 FETCH Get_Tot_Tax_Amount_Cur INTO v_tot_tax_amount;
1463 CLOSE Get_Tot_Tax_Amount_Cur;
1464 -- Check for Delivery lines existence in JAI_OM_WSH_LINES_ALL Table
1465 OPEN Get_Delivery_Line_Count_Cur ;
1466 FETCH Get_Delivery_Line_Count_Cur INTO v_delivery_line_count;
1467 CLOSE Get_Delivery_Line_Count_Cur ;
1468 IF v_delivery_line_count = 0 THEN
1469
1470 INSERT INTO JAI_OM_WSH_LINES_ALL( Delivery_Detail_Id,
1471 Order_Header_Id,
1472 Order_Line_Id,
1473 split_from_delivery_detail_id,
1474 Selling_Price,
1475 Quantity,
1476 Assessable_value,
1477 vat_assessable_value,
1478 Tax_Category_Id,
1479 Tax_Amount,
1480 Inventory_Item_Id,
1481 Organization_Id,
1482 Location_Id,
1483 Unit_Code,
1484 Excise_Amount,
1485 Basic_Excise_Duty_Amount,
1486 Add_Excise_Duty_Amount,
1487 Oth_Excise_Duty_Amount,
1488 Excise_Exempt_Type,
1489 Excise_Exempt_Refno,
1490 Excise_Exempt_Date,
1491 Creation_Date,
1492 Created_By,
1493 Last_Update_Date,
1494 Last_Updated_By,
1495 Last_Update_Login,
1496 ORG_ID,
1497 CUSTOMER_ID,
1498 SHIP_TO_ORG_ID,
1499 ORDER_TYPE_ID,
1500 SUBINVENTORY,
1501 DELIVERY_ID,
1502 VAT_EXEMPTION_FLAG,
1503 VAT_EXEMPTION_TYPE,
1504 VAT_EXEMPTION_DATE,
1505 VAT_EXEMPTION_REFNO
1506 , SHIPPABLE_FLAG)
1507 VALUES (v_delivery_detail_id,
1508 v_source_header_id,
1509 v_source_line_id,
1510 pr_new.split_from_delivery_detail_id,
1511 v_selling_price * v_conversion_rate,
1512 v_shipped_quantity,
1513 v_assessable_value * v_conversion_rate,
1514 ln_vat_assessable_value * v_conversion_rate,
1515 v_tax_category_id,
1516 v_tot_tax_amount,
1517 v_Inventory_Item_Id,
1518 v_Organization_Id,
1519 v_location_id,
1520 v_Requested_Quantity_Uom,
1521 v_excise_amount,
1522 v_basic_excise_duty_amount,
1523 v_add_excise_duty_amount,
1524 v_oth_excise_duty_amount,
1525 v_excise_exempt_type,
1526 v_excise_exempt_refno,
1527 v_excise_exempt_date,
1528 v_creation_date,
1529 v_created_by,
1530 v_last_update_date,
1531 v_last_updated_by,
1532 v_last_update_login,
1533 v_org_Id,
1534 v_customer_id,
1535 v_ship_to_org_id,
1536 v_source_header_type_id,
1537 v_subinventory,
1538 v_DELIVERY_ID,
1539 lv_vat_exemption_flag,
1540 lv_vat_exemption_type,
1541 ld_vat_exemption_date,
1542 lv_vat_exemption_refno
1543 , NULL);
1544
1545 -- Insert the Data Required for RG entries into a Temporary Table
1546 IF NVL(v_excise_flag,'N') = 'Y' THEN
1547 INSERT INTO JAI_OM_OE_GEN_TAXINV_T( date_released,
1548 date_confirmed,
1549 delivery_detail_id,
1550 order_header_id,
1551 creation_date,
1552 created_by,
1553 last_update_date,
1554 last_updated_by,
1555 last_update_login,
1556 delivery_id)
1557 VALUES (SYSDATE,
1558 v_date_confirmed,
1559 v_delivery_detail_id,
1560 v_source_header_id,
1561 v_creation_date,
1562 v_created_by,
1563 v_last_update_date,
1564 v_last_updated_by,
1565 v_last_update_login,
1566 v_delivery_id);
1567 END IF;
1568
1569 /*
1570 || check if VAT type of tax exists
1571 */
1572
1573 OPEN cur_chk_vat_exists (cp_del_det_id => v_delivery_detail_id) ;
1574 FETCH cur_chk_vat_exists INTO ln_vat_cnt;
1575 CLOSE cur_chk_vat_exists ;
1576
1577 OPEN cur_chk_vat_proc_entry (cp_delivery_id => v_delivery_id);
1578 FETCH cur_chk_vat_proc_entry INTO ln_vat_proc_cnt ;
1579 CLOSE cur_chk_vat_proc_entry;
1580
1581
1582 /*
1583 || Check if only 'VAT REVERSAL' tax type is present in JAI_OM_WSH_LINE_TAXES
1584 */
1585 IF nvl(ln_vat_cnt,0) = 0 THEN
1586 lv_vat_reversal := 'VAT REVERSAL' ;
1587 OPEN c_chk_vat_reversal(cp_del_det_id => v_delivery_detail_id,
1588 cp_tax_type => lv_vat_reversal) ;
1589 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
1590 CLOSE c_chk_vat_reversal ;
1591
1592 /*
1593 || VAT invoice number should be punched as 'NA' and accounting should happen
1594 || when 'VAT REVERSAL' type of tax exist and 'VAT' type of tax(es) doesn't exist
1595 */
1596 lv_vat_invoice_no := jai_constants.not_applicable ;
1597 lv_vat_inv_gen_status := 'C' ;
1598 END IF ;
1599
1600 IF (nvl(ln_vat_cnt,0) > 0 OR nvl(ln_vat_reversal_exists,0) = 1 ) AND nvl (ln_vat_proc_cnt,0) = 0 THEN
1601 /* VAT type of tax exists*/
1602 /* Get the regime id for these type of taxes */
1603 OPEN cur_get_regime_info (cp_organization_id => v_organization_id,
1604 cp_location_id => v_location_id
1605 );
1606 FETCH cur_get_regime_info INTO ln_regime_id,
1607 lv_regns_num;
1608 CLOSE cur_get_regime_info;
1609
1610 INSERT INTO JAI_RGM_INVOICE_GEN_T ( regime_id ,
1611 delivery_id ,
1612 delivery_date ,
1613 customer_trx_id ,
1614 organization_id ,
1615 location_id ,
1616 registration_num ,
1617 vat_invoice_no ,
1618 vat_inv_gen_status ,
1619 vat_inv_gen_err_message ,
1620 vat_acct_status ,
1621 vat_acct_err_message ,
1622 request_id ,
1623 program_application_id ,
1624 program_id ,
1625 program_update_date ,
1626 party_id ,
1627 party_site_id ,
1628 party_type ,
1629 creation_date ,
1630 created_by ,
1631 last_update_date ,
1632 last_update_login ,
1633 last_updated_by
1634 )
1635 VALUES (ln_regime_id ,
1636 v_delivery_id ,
1637 v_creation_date ,
1638 null ,
1639 v_organization_id ,
1640 v_location_id ,
1641 lv_regns_num ,
1642 lv_vat_invoice_no ,
1643 nvl(lv_vat_inv_gen_status, 'P'),
1644 null ,
1645 'P' ,
1646 null ,
1647 null ,
1648 null ,
1649 null ,
1650 null ,
1651 ln_bill_to_cust_id ,
1652 v_bill_to_org_id ,
1653 jai_constants.party_type_customer,
1654 v_creation_date ,
1655 v_created_by ,
1656 v_last_update_date ,
1657 v_last_update_login ,
1658 v_last_updated_by
1659 );
1660
1661 END IF;
1662
1663 ELSE
1664 UPDATE JAI_OM_WSH_LINES_ALL
1665 SET quantity = v_shipped_quantity,
1666 tax_amount = v_tot_tax_amount,
1667 order_line_id = v_source_line_id,
1668 excise_amount = v_excise_amount,
1669 basic_excise_duty_amount = v_basic_excise_duty_amount,
1670 add_excise_duty_amount = v_add_excise_duty_amount,
1671 oth_excise_duty_amount = v_oth_excise_duty_amount,
1672 last_update_date = v_last_update_date,
1673 last_updated_by = v_last_updated_by,
1674 last_update_login = v_last_update_login,
1675 VAT_EXEMPTION_FLAG = lv_vat_exemption_flag,
1676 VAT_EXEMPTION_TYPE = lv_vat_exemption_type,
1677 VAT_EXEMPTION_DATE = ld_vat_exemption_date,
1678 VAT_EXEMPTION_REFNO = lv_vat_exemption_refno
1679 WHERE Delivery_id = v_delivery_id
1680 AND Delivery_Detail_id = v_delivery_detail_id;
1681 END IF;
1682
1683 /*
1684 ||Does interim TCS accounting for the TCS type of taxes
1685 */
1686 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1687 FND_LOG.STRING(G_LEVEL_STATEMENT,
1688 G_MODULE_NAME || v_api_name,
1689 'Before in jai_ar_tcs_rep_pkg.wsh_interim_accounting.');
1690 END IF;
1691 jai_ar_tcs_rep_pkg.wsh_interim_accounting ( p_delivery_id => v_delivery_id ,
1692 p_delivery_detail_id => v_delivery_detail_id ,
1693 p_order_header_id => v_source_header_id ,
1694 p_organization_id => v_organization_id ,
1695 p_location_id => v_location_id ,
1696 p_currency_code => v_currency_code ,
1697 p_process_flag => lv_process_flag ,
1698 p_process_message => lv_process_message
1699 );
1700
1701 IF lv_process_flag = jai_constants.expected_error OR
1702 lv_process_flag = jai_constants.unexpected_error
1703 THEN
1704 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1705 FND_LOG.STRING(G_LEVEL_STATEMENT,
1706 G_MODULE_NAME || v_api_name,
1707 'Error in jai_ar_tcs_rep_pkg.wsh_interim_accounting. ' || lv_process_message);
1708 END IF;
1709 /*
1710 || As Returned status is an error/not applicable hence:-
1711 || Set out variables p_process_flag and p_process_message accordingly
1712 */
1713 fnd_message.set_name ( application => 'JA',
1714 name => 'JAI_GENERIC_MSG'
1715 );
1716 fnd_message.set_token ( token => 'MSG_TEXT',
1717 value => lv_process_message
1718 );
1719 app_exception.raise_exception;
1720
1721 END IF;
1722
1723 OPEN bonded_cur(v_organization_id, v_subinventory);
1724 FETCH bonded_cur INTO v_bonded_flag;
1725 CLOSE bonded_cur;
1726
1727 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1728 FND_LOG.STRING(G_LEVEL_STATEMENT,
1729 G_MODULE_NAME || v_api_name,
1730 '2 BEFORE the assignment OF v_order_type_id');
1731 END IF;
1732 v_order_type_id := v_source_header_type_id;
1733
1734 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1735 FND_LOG.STRING(G_LEVEL_STATEMENT,
1736 G_MODULE_NAME || v_api_name,
1737 '3 v_order_type_id IS '|| v_order_type_id);
1738 END IF;
1739
1740 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_organization_id,
1741 v_location_id,
1742 v_order_type_id,
1743 'Y',
1744 v_asst_register_id,
1745 v_register_code
1746 );
1747
1748 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1749 FND_LOG.STRING(G_LEVEL_STATEMENT,
1750 G_MODULE_NAME || v_api_name,
1751 '4 AFTER the Register Code CURSOR');
1752 END IF;
1753
1754 OPEN fin_year_cur(v_organization_id);
1755 FETCH fin_year_cur INTO v_fin_year;
1756 CLOSE fin_year_cur;
1757
1758 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1759 FND_LOG.STRING(G_LEVEL_STATEMENT,
1760 G_MODULE_NAME || v_api_name,
1761 '5 AFTER the Financial Year CURSOR');
1762 END IF;
1763
1764 IF v_delivery_id <> -1 THEN
1765 v_old_register := NULL;
1766 IF v_old_register IS NULL THEN
1767 v_reg_type := NULL;
1768 v_tot_basic_ed_amt := v_basic_excise_duty_amount ;
1769 v_tot_excise_amt := v_excise_amount;
1770 IF NVL(v_bonded_flag,'Y') = 'Y' AND
1771 ( NVL(v_tot_excise_amt,0) > 0 OR v_excise_exempt_type IS NOT NULL ) THEN
1772
1773 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1774 FND_LOG.STRING(G_LEVEL_STATEMENT,
1775 G_MODULE_NAME || v_api_name,
1776 '6 BEFORE the Preference cursors');
1777 END IF;
1778
1779 OPEN pref_cur(v_organization_id, v_location_id);
1780 FETCH pref_cur INTO v_pref_rg23a, v_pref_rg23c, v_pref_pla,v_export_oriented_unit;
1781 CLOSE pref_cur;
1782
1783 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1784 FND_LOG.STRING(G_LEVEL_STATEMENT,
1785 G_MODULE_NAME || v_api_name,
1786 '7 BEFORE the RG Balance CURSOR');
1787 END IF;
1788
1789 OPEN rg_bal_cur(v_organization_id, v_location_id);
1790 FETCH rg_bal_cur INTO v_rg23a_balance,
1791 v_rg23c_balance,
1792 v_pla_balance,
1793 v_basic_pla_balance,
1794 v_additional_pla_balance,
1795 v_other_pla_balance;
1796 CLOSE rg_bal_cur;
1797
1798 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1799 FND_LOG.STRING(G_LEVEL_STATEMENT,
1800 G_MODULE_NAME || v_api_name,
1801 '8 BEFORE the SSI Unit Flag CURSOR');
1802 END IF;
1803
1804 OPEN ssi_unit_flag_cur(v_organization_id, v_location_id);
1805 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag;
1806 CLOSE ssi_unit_flag_cur;
1807
1808 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1809 FND_LOG.STRING(G_LEVEL_STATEMENT,
1810 G_MODULE_NAME || v_api_name,
1811 '9 BEFORE the Register Code CURSOR');
1812 END IF;
1813
1814 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_organization_id,
1815 v_location_id,
1816 v_order_type_id,
1817 'Y',
1818 v_asst_register_id,
1819 v_register_code);
1820
1821 IF NVL(v_register_code,'N') IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1822 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
1823 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
1824 CLOSE get_item_dtls;
1825 IF NVL(v_excise_flag,'N') = 'Y' THEN
1826 IF NVL(v_excise_exempt_type, '@@@') NOT IN ('CT2', 'EXCISE_EXEMPT_CERT',
1827 'CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH','CT3' ) THEN
1828
1829 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1830 FND_LOG.STRING(G_LEVEL_STATEMENT,
1831 G_MODULE_NAME || v_api_name,
1832 '10 BEFORE the jai_om_wsh_processing_pkg.excise_balance_check FUNCTION');
1833 END IF;
1834
1835 open c_cess_amount(v_delivery_id);
1836 fetch c_cess_amount into ln_cess_amount;
1837 close c_cess_amount;
1838
1839 open c_sh_cess_amount(v_delivery_id);
1840 fetch c_sh_cess_amount into ln_sh_cess_amount;
1841 close c_sh_cess_amount;
1842
1843 v_reg_type:= jai_om_wsh_processing_pkg.excise_balance_check
1844 (v_pref_rg23a,
1845 v_pref_rg23c,
1846 v_pref_pla,
1847 NVL(v_ssi_unit_flag,'N'),
1848 v_tot_excise_amt,
1849 v_rg23a_balance,
1850 v_rg23c_balance,
1851 v_pla_balance,
1852 v_basic_pla_balance,
1853 v_additional_pla_balance,
1854 v_other_pla_balance,
1855 v_basic_excise_duty_amount,
1856 v_add_excise_duty_amount ,
1857 v_oth_excise_duty_amount,
1858 v_export_oriented_unit,
1859 v_register_code,
1860 v_delivery_id ,
1861 v_organization_id,
1862 v_location_id ,
1863 ln_cess_amount ,
1864 ln_sh_cess_amount ,
1865 lv_process_flag ,
1866 lv_process_message
1867 );
1868
1869 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.STRING(G_LEVEL_STATEMENT,
1871 G_MODULE_NAME || v_api_name,
1872 '11 AFTER the jai_om_wsh_processing_pkg.excise_balance_check FUNCTION v_reg_type -> ' || v_reg_type);
1873 END IF;
1874 ELSE
1875 OPEN get_item_dtls(v_organization_id,v_inventory_item_id);
1876 FETCH get_item_dtls INTO v_excise_flag,v_item_class;
1877 CLOSE get_item_dtls;
1878 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
1879 IF v_excise_exempt_type IN ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
1880 OPEN Get_Tax_Lines_Details_Cur1;
1881 FETCH Get_Tax_Lines_Details_Cur1 INTO v_modvat_tax_rate,v_rounding_factor;
1882 CLOSE Get_Tax_Lines_Details_Cur1;
1883 ELSE
1884 OPEN for_modvat_percentage(v_organization_id, v_location_id);
1885 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
1886 CLOSE for_modvat_percentage;
1887 END IF;
1888 v_exempt_bal := (NVL(v_exempt_bal, 0) + v_shipped_quantity * v_assessable_value * NVL(v_modvat_tax_rate,0))/100;
1889
1890 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1891 FND_LOG.STRING(G_LEVEL_STATEMENT,
1892 G_MODULE_NAME || v_api_name,
1893 '12 BEFORE the jai_om_wsh_pkg.get_excise_register_with_bal FUNCTION v_exempt_bal -> '|| v_exempt_bal);
1894 END IF;
1895
1896 open c_cess_amount(v_delivery_id);
1897 fetch c_cess_amount into ln_cess_amount;
1898 close c_cess_amount;
1899
1900 open c_sh_cess_amount(v_delivery_id);
1901 fetch c_sh_cess_amount into ln_sh_cess_amount;
1902 close c_sh_cess_amount;
1903
1904 v_reg_type := jai_om_wsh_pkg.get_excise_register_with_bal
1905 (v_pref_rg23a,
1906 v_pref_rg23c,
1907 v_pref_pla,
1908 NVL(v_ssi_unit_flag,'N'),
1909 v_exempt_bal,
1910 v_rg23a_balance,
1911 v_rg23c_balance,
1912 v_pla_balance,
1913 v_basic_pla_balance,
1914 v_additional_pla_balance,
1915 v_other_pla_balance,
1916 v_basic_excise_duty_amount,
1917 v_add_excise_duty_amount ,
1918 v_oth_excise_duty_amount,
1919 v_export_oriented_unit,
1920 v_register_code,
1921 v_delivery_id,
1922 v_organization_id,
1923 v_location_id ,
1924 ln_cess_amount ,
1925 ln_sh_cess_amount ,
1926 lv_process_flag ,
1927 lv_process_message
1928 );
1929
1930 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1931 FND_LOG.STRING(G_LEVEL_STATEMENT,
1932 G_MODULE_NAME || v_api_name,
1933 '13 AFTER the jai_om_wsh_pkg.get_excise_register_with_bal FUNCTION v_reg_type -> '|| v_reg_type);
1934 END IF;
1935 v_basic_ed_amt := v_exempt_bal;
1936 v_tot_basic_ed_amt := NVL(v_tot_basic_ed_amt,0) + v_exempt_bal;
1937
1938 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1939 FND_LOG.STRING(G_LEVEL_STATEMENT,
1940 G_MODULE_NAME || v_api_name,
1941 '14 v_basic_ed_amt -> '|| v_basic_ed_amt||', v_tot_basic_ed_amt -> '|| v_tot_basic_ed_amt);
1942 END IF;
1943 END IF;
1944 END IF;
1945 END IF;
1946 ELSIF NVL(v_register_code,'N') IN ('BOND_REG') THEN
1947 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_asst_register_id,
1948 v_register_balance,
1949 v_reg_exp_date ,
1950 v_lou_flag);
1951
1952 IF nvl(v_reg_exp_date,sysdate) < sysdate then
1953 pv_return_code := jai_constants.expected_error ;
1954 pv_return_message := 'Bonded Register Validity has Expired on ' || v_reg_exp_date ;
1955 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1956 FND_LOG.STRING(G_LEVEL_STATEMENT,
1957 G_MODULE_NAME || v_api_name,
1958 'Returned with ' || pv_return_code || pv_return_message);
1959 END IF;
1960 return ;
1961 end if;
1962 -- logic is : to check if it is a letter of undertaking and only then if balance is a problem , raise an error
1963 v_tot_excise_amt := v_tot_excise_amt * v_curr_conv_rate;
1964 IF NVL(v_register_balance,0) < NVL(v_tot_excise_amt,0) and nvl(v_lou_flag,'N') = 'N' THEN
1965 pv_return_code := jai_constants.expected_error ;
1966 pv_return_message := 'Bonded Register Has Balance -> '||
1967 TO_CHAR(v_register_balance) ||
1968 ' ,which IS less than Excisable Amount -> ' ||
1969 TO_CHAR(v_tot_excise_amt) ;
1970 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1971 FND_LOG.STRING(G_LEVEL_STATEMENT,
1972 G_MODULE_NAME || v_api_name,
1973 'Returned with ' || pv_return_code || pv_return_message);
1974 END IF;
1975 return ;
1976 END IF;
1977 END IF;
1978 END IF;
1979 END IF;
1980 END IF;
1981
1982 Exception
1983 When Others then
1984 pv_return_code := jai_constants.expected_error ;
1985 pv_return_message := substr(sqlerrm,1,200) ;
1986 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1987 FND_LOG.STRING(G_LEVEL_STATEMENT,
1988 G_MODULE_NAME || v_api_name,
1989 'Exception with ' || pv_return_code || pv_return_message);
1990 END IF;
1991 return ;
1992 END PROCESS_INTERFACED ;
1993
1994 /*
1995 REM +========================================================================================+
1996 REM NAME RG23D_REV_ACCOUNTING
1997 REM
1998 REM DESCRIPTION Called from BE_INTERFACED
1999 REM
2000 REM NOTES Come from JAI_OM_WDD_TRIGGER_PKG.RG23D_REV_ACCOUNTING
2001 REM Passing the accounting entries in the sub-ledger table jai_cmn_journal_entries.
2002 REM +========================================================================================+
2003 */
2004 PROCEDURE RG23D_REV_ACCOUNTING(pr_trig_row IN wsh_delivery_details%ROWTYPE,
2005 pv_return_code out nocopy VARCHAR2,
2006 pv_return_message out nocopy VARCHAR2) IS
2007
2008 v_api_name CONSTANT VARCHAR2(30) := 'RG23D_REV_ACCOUNTING';
2009 lv_return_code VARCHAR2(100);
2010 lv_return_message VARCHAR2(2000);
2011
2012 CURSOR c_get_il_setup IS
2013 SELECT trading,
2014 excise_rcvble_account,
2015 excise_paid_account,
2016 excise_23d_account,
2017 excise_in_rg23d,
2018 order_price_excise_inclusive,
2019 cess_paid_payable_account_id,
2020 sh_cess_paid_payable_acct_id
2021 FROM JAI_CMN_INVENTORY_ORGS
2022 WHERE organization_id = pr_trig_row.organization_id
2023 AND location_id = pr_trig_row.ship_from_location_id;
2024
2025 CURSOR c_get_cost_of_goods_ac IS
2026 SELECT cost_of_sales_account
2027 FROM mtl_parameters
2028 WHERE organization_id = pr_trig_row.organization_id;
2029
2030 CURSOR c_matched_receipts IS
2031 SELECT receipt_id, receipt_quantity_applied
2032 FROM JAI_CMN_MATCH_RECEIPTS
2033 WHERE ref_line_id = pr_trig_row.delivery_detail_id;
2034
2035 CURSOR c_rg23d_details(p_register_id NUMBER) IS
2036 SELECT closing_balance_qty - opening_balance_qty receipt_quantity,
2037 Nvl(basic_ed, 0) + Nvl(additional_ed, 0) + Nvl(other_ed, 0) duty_amount,
2038 transaction_uom_code
2039 FROM JAI_CMN_RG_23D_TRXS
2040 WHERE register_id = p_register_id;
2041
2042 CURSOR c_cess_details(p_register_id NUMBER) IS
2043 SELECT credit
2044 FROM JAI_CMN_RG_OTHERS
2045 WHERE source_register = 'RG23D'
2046 AND source_register_id = p_register_id
2047 AND tax_type = 'EXCISE_EDUCATION_CESS';
2048
2049 CURSOR c_sh_cess_details(p_register_id NUMBER) IS
2050 SELECT credit
2051 FROM JAI_CMN_RG_OTHERS
2052 WHERE source_register = 'RG23D'
2053 AND source_register_id = p_register_id
2054 AND tax_type = 'EXCISE_SH_EDU_CESS';
2055
2056 CURSOR c_get_delivery_id IS
2057 SELECT To_Char(delivery_id)
2058 FROM wsh_delivery_assignments
2059 WHERE delivery_detail_id = pr_trig_row.delivery_detail_id;
2060
2061 /*
2062 Get the Currency code using the current org id from the table hr_operating_units and gl_sets_of_books
2063 */
2064
2065 CURSOR Sob_Cur IS
2066 SELECT gsb.currency_code
2067 FROM hr_operating_units hru, gl_sets_of_books gsb
2068 WHERE gsb.set_of_books_id = hru.set_of_books_id
2069 AND hru.organization_id = pr_trig_row.org_id;
2070
2071 CURSOR c_curr_precision(cp_curr_code VARCHAR2) IS
2072 SELECT Nvl(PRECISION, 2)
2073 FROM gl_currencies
2074 WHERE currency_code = cp_curr_code;
2075
2076 CURSOR c_inclusive_flag(CP_DDID IN NUMBER) IS
2077 SELECT count(*)
2078 FROM jai_om_wsh_line_taxes a, jai_cmn_taxes_all b
2079 where a.delivery_Detail_id = cp_ddid
2080 and a.tax_id = b.tax_id
2081 and upper(b.tax_type) like '%EXCISE%'
2082 AND B.INCLUSIVE_TAX_FLAG = 'Y';
2083
2084 lv_inclusive_tax_flag NUMBER;
2085 ln_precision NUMBER;
2086
2087 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE;
2088 v_set_of_books_id NUMBER;
2089 r_il_setup c_get_il_setup%ROWTYPE;
2090 r_rg23d_details c_rg23d_details%ROWTYPE;
2091 ln_cost_of_goods_sold_ac mtl_parameters.cost_of_sales_account%TYPE;
2092 ln_cost_of_goods_ac NUMBER;
2093 ln_excise_amt NUMBER;
2094 ln_temp_amt NUMBER;
2095 ln_cess_amt NUMBER;
2096 ln_sh_cess_amt NUMBER;
2097 ln_qty_ratio NUMBER;
2098 lv_source_name VARCHAR2(100) := 'Register India';
2099 lv_category_name VARCHAR2(100) := 'Register India';
2100 lv_reference_10 varchar2(100) := 'India Localization - RG23D Accounting Reversal on Shipment';
2101 lv_reference_23 varchar2(100) := 'jai_rg23d_shipment_acc_pkg.rg23d_rev_accounting';
2102 lv_reference_24 varchar2(100) := 'wsh_new_deliveries';
2103 lv_reference_26 varchar2(100) := To_Char(pr_trig_row.delivery_detail_id);
2104 lv_reference_25 varchar2(100) := 'delivery_id';
2105 lv_regime_code VARCHAR2(100) := 'EXCISE';
2106 lv_source_table VARCHAR2(100) := 'wsh_delivery_details';
2107
2108 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2109
2110 BEGIN
2111
2112 OPEN c_get_il_setup;
2113 FETCH c_get_il_setup
2114 INTO r_il_setup;
2115 CLOSE c_get_il_setup;
2116
2117 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr(p_org_id => pr_trig_row.organization_id);
2118 v_set_of_books_id := l_func_curr_det.ledger_id;
2119 v_currency_code := l_func_curr_det.currency_code;
2120 IF Nvl(r_il_setup.trading, 'N') = 'Y' AND
2121 Nvl(r_il_setup.excise_in_rg23d, 'N') = 'Y' /*reversing to be done only if this setup is present. not necessary otherwise*/
2122 THEN
2123 ln_excise_amt := 0;
2124 ln_cess_amt := 0;
2125 ln_sh_cess_amt := 0;
2126
2127 OPEN c_get_delivery_id;
2128 FETCH c_get_delivery_id
2129 INTO lv_reference_26;
2130 CLOSE c_get_delivery_id;
2131
2132 lv_reference_10 := lv_reference_10 || ' for delivery_id ' ||
2133 lv_reference_26;
2134 FOR match_rec IN c_matched_receipts LOOP
2135 OPEN c_rg23d_details(match_rec.receipt_id);
2136 FETCH c_rg23d_details
2137 INTO r_rg23d_details;
2138 CLOSE c_rg23d_details;
2139 ln_qty_ratio := Nvl(match_rec.receipt_quantity_applied, 0) /
2140 Nvl(r_rg23d_details.receipt_quantity, 1);
2141 ln_excise_amt := ln_excise_amt +
2142 Nvl(r_rg23d_details.duty_amount * ln_qty_ratio, 0);
2143
2144 OPEN c_cess_details(match_rec.receipt_id);
2145 FETCH c_cess_details
2146 INTO ln_temp_amt;
2147 CLOSE c_cess_details;
2148
2149 ln_cess_amt := ln_cess_amt + Nvl(ln_temp_amt * ln_qty_ratio, 0);
2150 ln_temp_amt := 0;
2151
2152 OPEN c_sh_cess_details(match_rec.receipt_id);
2153 FETCH c_sh_cess_details
2154 INTO ln_temp_amt;
2155 CLOSE c_sh_cess_details;
2156
2157 ln_sh_cess_amt := ln_sh_cess_amt + Nvl(ln_temp_amt * ln_qty_ratio, 0);
2158 ln_temp_amt := 0;
2159
2160 END LOOP;
2161 /* rounding logic*/
2162
2163 OPEN Sob_Cur;
2164 FETCH Sob_Cur
2165 INTO v_currency_code;
2166 CLOSE Sob_Cur;
2167
2168 OPEN c_curr_precision(v_currency_code);
2169 FETCH c_curr_precision
2170 INTO ln_precision;
2171 CLOSE c_curr_precision;
2172
2173 ln_excise_amt := Round(ln_excise_amt, ln_precision);
2174 ln_cess_amt := Round(ln_cess_amt, ln_precision);
2175 ln_sh_cess_amt := Round(ln_sh_cess_amt, ln_precision);
2176
2177 ln_temp_amt := ln_excise_amt + ln_cess_amt + ln_sh_cess_amt;
2178
2179 OPEN c_get_cost_of_goods_ac;
2180 FETCH c_get_cost_of_goods_ac
2181 INTO ln_cost_of_goods_ac;
2182 CLOSE c_get_cost_of_goods_ac;
2183 -- pass a/c entry for 'debit cost of goods sold account'
2184
2185 OPEN c_inclusive_flag(pr_trig_row.delivery_detail_id);
2186 FETCH c_inclusive_flag
2187 INTO lv_inclusive_tax_flag;
2188 CLOSE c_inclusive_flag;
2189
2190 IF lv_inclusive_tax_flag > 0
2191 THEN
2192 jai_cmn_gl_pkg.create_gl_entry(p_organization_id => pr_trig_row.organization_id,
2193 p_currency_code => v_currency_code,
2194 p_credit_amount => NULL,
2195 p_debit_amount => ln_temp_amt,
2196 p_cc_id => ln_cost_of_goods_ac,
2197 p_je_source_name => lv_source_name,
2198 p_je_category_name => lv_category_name,
2199 p_created_by => pr_trig_row.created_by,
2200 p_accounting_date => trunc(sysdate),
2201 p_currency_conversion_date => NULL,
2202 p_currency_conversion_type => NULL,
2203 p_currency_conversion_rate => NULL,
2204 p_reference_10 => lv_reference_10,
2205 p_reference_23 => lv_reference_23,
2206 p_reference_24 => lv_reference_24,
2207 p_reference_25 => lv_reference_25,
2208 p_reference_26 => lv_reference_26);
2209
2210 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
2211 (JOURNAL_ENTRY_ID,
2212 regime_code,
2213 organization_id,
2214 set_of_books_id,
2215 tax_type,
2216 period_name,
2217 code_combination_id,
2218 accounted_dr,
2219 accounted_cr,
2220 transaction_date,
2221 source,
2222 source_table_name,
2223 source_trx_id,
2224 reference_name,
2225 reference_id,
2226 repository_id,
2227 currency_code,
2228 curr_conv_rate,
2229 creation_date,
2230 created_by,
2231 last_update_date,
2232 last_updated_by,
2233 last_update_login)
2234 VALUES
2235 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
2236 lv_regime_code,
2237 pr_trig_row.organization_id,
2238 v_set_of_books_id,
2239 NULL,
2240 NULL,
2241 ln_cost_of_goods_ac,
2242 ln_temp_amt,
2243 NULL,
2244 SYSDATE,
2245 lv_source_name,
2246 lv_source_table,
2247 pr_trig_row.delivery_detail_id,
2248 lv_reference_25,
2249 lv_reference_26,
2250 NULL,
2251 v_currency_code,
2252 NULL,
2253 sysdate,
2254 FND_GLOBAL.user_id,
2255 sysdate,
2256 fnd_global.user_id,
2257 fnd_global.login_id);
2258
2259 -- pass a/c entry for 'credit rg23d account'
2260 jai_cmn_gl_pkg.create_gl_entry(p_organization_id => pr_trig_row.organization_id,
2261 p_currency_code => v_currency_code,
2262 p_credit_amount => ln_temp_amt,
2263 p_debit_amount => NULL,
2264 p_cc_id => r_il_setup.excise_23d_account,
2265 p_je_source_name => lv_source_name,
2266 p_je_category_name => lv_category_name,
2267 p_created_by => pr_trig_row.created_by,
2268 p_accounting_date => trunc(sysdate),
2269 p_currency_conversion_date => NULL,
2270 p_currency_conversion_type => NULL,
2271 p_currency_conversion_rate => NULL,
2272 p_reference_10 => lv_reference_10,
2273 p_reference_23 => lv_reference_23,
2274 p_reference_24 => lv_reference_24,
2275 p_reference_25 => lv_reference_25,
2276 p_reference_26 => lv_reference_26);
2277
2278 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
2279 (JOURNAL_ENTRY_ID,
2280 regime_code,
2281 organization_id,
2282 set_of_books_id,
2283 tax_type,
2284 period_name,
2285 code_combination_id,
2286 accounted_dr,
2287 accounted_cr,
2288 transaction_date,
2289 source,
2290 source_table_name,
2291 source_trx_id,
2292 reference_name,
2293 reference_id,
2294 repository_id,
2295 currency_code,
2296 curr_conv_rate,
2297 creation_date,
2298 created_by,
2299 last_update_date,
2300 last_updated_by,
2301 last_update_login)
2302 VALUES
2303 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
2304 lv_regime_code,
2305 pr_trig_row.organization_id,
2306 v_set_of_books_id,
2307 NULL,
2308 NULL,
2309 r_il_setup.excise_23d_account,
2310 NULL,
2311 ln_temp_amt,
2312 SYSDATE,
2313 lv_source_name,
2314 lv_source_table,
2315 pr_trig_row.delivery_detail_id,
2316 lv_reference_25,
2317 lv_reference_26,
2318 NULL,
2319 v_currency_code,
2320 NULL,
2321 sysdate,
2322 FND_GLOBAL.user_id,
2323 sysdate,
2324 fnd_global.user_id,
2325 fnd_global.login_id);
2326
2327 ELSE
2328 -- pass a/c entry for 'debit excise paid/payable account'
2329 jai_cmn_gl_pkg.create_gl_entry(p_organization_id => pr_trig_row.organization_id,
2330 p_currency_code => v_currency_code,
2331 p_credit_amount => NULL,
2332 p_debit_amount => ln_excise_amt,
2333 p_cc_id => r_il_setup.excise_rcvble_account,
2334 p_je_source_name => lv_source_name,
2335 p_je_category_name => lv_category_name,
2336 p_created_by => pr_trig_row.created_by,
2337 p_accounting_date => trunc(sysdate),
2338 p_currency_conversion_date => NULL,
2339 p_currency_conversion_type => NULL,
2340 p_currency_conversion_rate => NULL,
2341 p_reference_10 => lv_reference_10,
2342 p_reference_23 => lv_reference_23,
2343 p_reference_24 => lv_reference_24,
2344 p_reference_25 => lv_reference_25,
2345 p_reference_26 => lv_reference_26);
2346
2347 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
2348 (JOURNAL_ENTRY_ID,
2349 regime_code,
2350 organization_id,
2351 set_of_books_id,
2352 tax_type,
2353 period_name,
2354 code_combination_id,
2355 accounted_dr,
2356 accounted_cr,
2357 transaction_date,
2358 source,
2359 source_table_name,
2360 source_trx_id,
2361 reference_name,
2362 reference_id,
2363 repository_id,
2364 currency_code,
2365 curr_conv_rate,
2366 creation_date,
2367 created_by,
2368 last_update_date,
2369 last_updated_by,
2370 last_update_login)
2371 VALUES
2372 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
2373 lv_regime_code,
2374 pr_trig_row.organization_id,
2375 v_set_of_books_id,
2376 NULL,
2377 NULL,
2378 r_il_setup.excise_rcvble_account,
2379 ln_excise_amt,
2380 NULL,
2381 SYSDATE,
2382 lv_source_name,
2383 lv_source_table,
2384 pr_trig_row.delivery_detail_id,
2385 lv_reference_25,
2386 lv_reference_26,
2387 NULL,
2388 v_currency_code,
2389 NULL,
2390 sysdate,
2391 FND_GLOBAL.user_id,
2392 sysdate,
2393 fnd_global.user_id,
2394 fnd_global.login_id);
2395
2396 -- pass a/c entry for 'debit cess paid/payable account'
2397 jai_cmn_gl_pkg.create_gl_entry(p_organization_id => pr_trig_row.organization_id,
2398 p_currency_code => v_currency_code,
2399 p_credit_amount => NULL,
2400 p_debit_amount => ln_cess_amt,
2401 p_cc_id => r_il_setup.cess_paid_payable_account_id,
2402 p_je_source_name => lv_source_name,
2403 p_je_category_name => lv_category_name,
2404 p_created_by => pr_trig_row.created_by,
2405 p_accounting_date => trunc(sysdate),
2406 p_currency_conversion_date => NULL,
2407 p_currency_conversion_type => NULL,
2408 p_currency_conversion_rate => NULL,
2409 p_reference_10 => lv_reference_10,
2410 p_reference_23 => lv_reference_23,
2411 p_reference_24 => lv_reference_24,
2412 p_reference_25 => lv_reference_25,
2413 p_reference_26 => lv_reference_26);
2414
2415 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
2416 (JOURNAL_ENTRY_ID,
2417 regime_code,
2418 organization_id,
2419 set_of_books_id,
2420 tax_type,
2421 period_name,
2422 code_combination_id,
2423 accounted_dr,
2424 accounted_cr,
2425 transaction_date,
2426 source,
2427 source_table_name,
2428 source_trx_id,
2429 reference_name,
2430 reference_id,
2431 repository_id,
2432 currency_code,
2433 curr_conv_rate,
2434 creation_date,
2435 created_by,
2436 last_update_date,
2437 last_updated_by,
2438 last_update_login)
2439 VALUES
2440 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
2441 lv_regime_code,
2442 pr_trig_row.organization_id,
2443 v_set_of_books_id,
2444 NULL,
2445 NULL,
2446 r_il_setup.cess_paid_payable_account_id,
2447 ln_cess_amt,
2448 NULL,
2449 SYSDATE,
2450 lv_source_name,
2451 lv_source_table,
2452 pr_trig_row.delivery_detail_id,
2453 lv_reference_25,
2454 lv_reference_26,
2455 NULL,
2456 v_currency_code,
2457 NULL,
2458 sysdate,
2459 FND_GLOBAL.user_id,
2460 sysdate,
2461 fnd_global.user_id,
2462 fnd_global.login_id);
2463
2464 -- pass a/c entry for 'debit sh cess paid/payable account'
2465 jai_cmn_gl_pkg.create_gl_entry(p_organization_id => pr_trig_row.organization_id,
2466 p_currency_code => v_currency_code,
2467 p_credit_amount => NULL,
2468 p_debit_amount => ln_sh_cess_amt,
2469 p_cc_id => r_il_setup.sh_cess_paid_payable_acct_id,
2470 p_je_source_name => lv_source_name,
2471 p_je_category_name => lv_category_name,
2472 p_created_by => pr_trig_row.created_by,
2473 p_accounting_date => trunc(sysdate),
2474 p_currency_conversion_date => NULL,
2475 p_currency_conversion_type => NULL,
2476 p_currency_conversion_rate => NULL,
2477 p_reference_10 => lv_reference_10,
2478 p_reference_23 => lv_reference_23,
2479 p_reference_24 => lv_reference_24,
2480 p_reference_25 => lv_reference_25,
2481 p_reference_26 => lv_reference_26);
2482
2483 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
2484 (JOURNAL_ENTRY_ID,
2485 regime_code,
2486 organization_id,
2487 set_of_books_id,
2488 tax_type,
2489 period_name,
2490 code_combination_id,
2491 accounted_dr,
2492 accounted_cr,
2493 transaction_date,
2494 source,
2495 source_table_name,
2496 source_trx_id,
2497 reference_name,
2498 reference_id,
2499 repository_id,
2500 currency_code,
2501 curr_conv_rate,
2502 creation_date,
2503 created_by,
2504 last_update_date,
2505 last_updated_by,
2506 last_update_login)
2507 VALUES
2508 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
2509 lv_regime_code,
2510 pr_trig_row.organization_id,
2511 v_set_of_books_id,
2512 NULL,
2513 NULL,
2514 r_il_setup.sh_cess_paid_payable_acct_id,
2515 ln_sh_cess_amt,
2516 NULL,
2517 SYSDATE,
2518 lv_source_name,
2519 lv_source_table,
2520 pr_trig_row.delivery_detail_id,
2521 lv_reference_25,
2522 lv_reference_26,
2523 NULL,
2524 v_currency_code,
2525 NULL,
2526 sysdate,
2527 FND_GLOBAL.user_id,
2528 sysdate,
2529 fnd_global.user_id,
2530 fnd_global.login_id);
2531
2532 -- pass a/c entry for 'credit rg23d account'
2533 jai_cmn_gl_pkg.create_gl_entry(p_organization_id => pr_trig_row.organization_id,
2534 p_currency_code => v_currency_code,
2535 p_credit_amount => ln_temp_amt,
2536 p_debit_amount => NULL,
2537 p_cc_id => r_il_setup.excise_23d_account,
2538 p_je_source_name => lv_source_name,
2539 p_je_category_name => lv_category_name,
2540 p_created_by => pr_trig_row.created_by,
2541 p_accounting_date => trunc(sysdate),
2542 p_currency_conversion_date => NULL,
2543 p_currency_conversion_type => NULL,
2544 p_currency_conversion_rate => NULL,
2545 p_reference_10 => lv_reference_10,
2546 p_reference_23 => lv_reference_23,
2547 p_reference_24 => lv_reference_24,
2548 p_reference_25 => lv_reference_25,
2549 p_reference_26 => lv_reference_26);
2550
2551 INSERT INTO JAI_CMN_JOURNAL_ENTRIES
2552 (JOURNAL_ENTRY_ID,
2553 regime_code,
2554 organization_id,
2555 set_of_books_id,
2556 tax_type,
2557 period_name,
2558 code_combination_id,
2559 accounted_dr,
2560 accounted_cr,
2561 transaction_date,
2562 source,
2563 source_table_name,
2564 source_trx_id,
2565 reference_name,
2566 reference_id,
2567 repository_id,
2568 currency_code,
2569 curr_conv_rate,
2570 creation_date,
2571 created_by,
2572 last_update_date,
2573 last_updated_by,
2574 last_update_login)
2575 VALUES
2576 (JAI_CMN_JOURNAL_ENTRIES_S.nextval,
2577 lv_regime_code,
2578 pr_trig_row.organization_id,
2579 v_set_of_books_id,
2580 NULL,
2581 NULL,
2582 r_il_setup.excise_23d_account,
2583 NULL,
2584 ln_temp_amt,
2585 SYSDATE,
2586 lv_source_name,
2587 lv_source_table,
2588 pr_trig_row.delivery_detail_id,
2589 lv_reference_25,
2590 lv_reference_26,
2591 NULL,
2592 v_currency_code,
2593 NULL,
2594 sysdate,
2595 FND_GLOBAL.user_id,
2596 sysdate,
2597 fnd_global.user_id,
2598 fnd_global.login_id);
2599
2600 END IF;
2601 ELSE
2602 RETURN;
2603 END IF;
2604
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 pv_return_code := 'E';
2608 pv_return_message := 'An error occurred when passing RG23D accounting entries. Check the lines in GL Interface';
2609 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2610 FND_LOG.STRING(G_LEVEL_STATEMENT,
2611 G_MODULE_NAME || v_api_name,
2612 'Exception with ' || pv_return_code || pv_return_message);
2613 END IF;
2614 END RG23D_REV_ACCOUNTING;
2615
2616 /*
2617 REM +======================================================================+
2618 REM NAME BE_RELEASED
2619 REM
2620 REM DESCRIPTION Called from BE_RELEASED
2621 REM
2622 REM NOTES Come from JAI_OM_WDD_TRIGGER_PKG.ARU_T4
2623 REM
2624 REM +======================================================================+
2625 */
2626 PROCEDURE PROCESS_RELEASED ( pr_old t_rec%type ,
2627 pr_new t_rec%type ,
2628 pv_action varchar2 ,
2629 pv_return_code out nocopy varchar2 ,
2630 pv_return_message out nocopy varchar2 )
2631 IS
2632 CURSOR c_check_lc_order
2633 IS
2634 SELECT
2635 lc_flag
2636 FROM
2637 JAI_OM_OE_SO_LINES
2638 WHERE
2639 lc_flag = 'Y' AND
2640 header_id = pr_new.source_header_id;
2641
2642 v_check_lc_order VARCHAR2(1);
2643 ln_cnt NUMBER;
2644
2645
2646 BEGIN
2647 pv_return_code := jai_constants.successful ;
2648
2649 OPEN c_check_lc_order;
2650 FETCH c_check_lc_order INTO v_check_lc_order;
2651 CLOSE c_check_lc_order;
2652
2653 IF NVL(v_check_lc_order,'N') = 'Y' THEN
2654
2655 Select count(delivery_detail_id) into ln_cnt
2656 from jai_wsh_del_details_gt
2657 Where delivery_detail_id = pr_new.delivery_detail_id;
2658
2659 IF nvl(ln_cnt,0) > 0 THEN
2660 Update jai_wsh_del_details_gt
2661 set shipped_quantity = pr_new.shipped_quantity
2662 where delivery_detail_id = pr_new.delivery_detail_id;
2663
2664 ELSE
2665 INSERT INTO jai_wsh_del_details_gt( delivery_detail_id,
2666 organization_id ,
2667 inventory_item_id,
2668 source_header_type_id,
2669 shipped_quantity,
2670 source_header_id,
2671 source_line_id,
2672 SPLIT_FROM_DELIVERY_DETAIL_ID,
2673 processed_flag)
2674 Values ( pr_new.delivery_detail_id,
2675 pr_new.organization_id ,
2676 pr_new.inventory_item_id,
2677 pr_new.source_header_type_id,
2678 pr_new.shipped_quantity,
2679 pr_new.source_header_id,
2680 pr_new.source_line_id,
2681 pr_new.SPLIT_FROM_DELIVERY_DETAIL_ID,
2682 'N');
2683 END IF;
2684 END IF;
2685 END PROCESS_RELEASED;
2686
2687 /*
2688 REM +======================================================================+
2689 REM NAME Check Delivery Status
2690 REM
2691 REM DESCRIPTION Called from JAI_OM_WDD_ARIUD_TRG
2692 REM
2693 REM NOTES Extracted from Process Interfaced
2694 REM
2695 REM +======================================================================+
2696 */
2697 FUNCTION CHECK_STATUS (pr_new t_rec%type) RETURN VARCHAR2
2698 IS
2699 v_api_name CONSTANT VARCHAR2(30) := 'CHECK_STATUS';
2700 v_source_line_id NUMBER;
2701 v_delivery_detail_id NUMBER;
2702 v_so_lines_count NUMBER;
2703 v_status_code VARCHAR2(2);
2704
2705 CURSOR Get_Status_Cur IS
2706 SELECT A.status_code
2707 FROM Wsh_Delivery_Assignments B,
2708 Wsh_New_deliveries A
2709 WHERE B.Delivery_Id = A.Delivery_Id
2710 AND B.Delivery_Detail_id = v_delivery_detail_id;
2711
2712 CURSOR Get_So_Lines_Count_Cur IS
2713 SELECT COUNT(*)
2714 FROM JAI_OM_OE_SO_LINES
2715 WHERE Line_id = v_source_line_id;
2716
2717 CURSOR Get_So_Lines_Details_Cur IS
2718 SELECT NVL(Selling_Price,0),
2719 NVL(Quantity,0),
2720 NVL(Tax_Category_Id,0),
2721 NVL(Assessable_Value,0),
2722 NVL(vat_assessable_value,0),
2723 Excise_Exempt_Type,
2724 Excise_Exempt_Refno,
2725 Excise_Exempt_Date,
2726 vat_exemption_flag,
2727 vat_exemption_type,
2728 vat_exemption_date,
2729 vat_exemption_refno
2730 FROM JAI_OM_OE_SO_LINES
2731 WHERE Line_id = v_source_line_id;
2732
2733 BEGIN
2734
2735 v_delivery_detail_id :=pr_new.delivery_detail_id;
2736 v_source_line_id :=pr_new.Source_Line_Id;
2737
2738 OPEN Get_Status_Cur;
2739 FETCH Get_Status_Cur INTO v_status_code;
2740 CLOSE Get_Status_Cur;
2741
2742 IF NVL(v_status_code,'#') NOT IN ('CO', 'IT','CL') THEN
2743 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2744 FND_LOG.STRING(G_LEVEL_STATEMENT,
2745 G_MODULE_NAME || v_api_name,
2746 'Status is not in CO, IT or CL.');
2747 END IF;
2748 RETURN 'N';
2749 END IF;
2750
2751 IF NVL(pr_new.shipped_quantity,0) = 0 THEN
2752 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2753 FND_LOG.STRING(G_LEVEL_STATEMENT,
2754 G_MODULE_NAME || v_api_name,
2755 'Shipped Quantity is 0.');
2756 END IF;
2757 RETURN 'N';
2758 END IF;
2759
2760 -- Check whether Line Details exists in Localization table.
2761 OPEN Get_So_Lines_Count_Cur;
2762 FETCH Get_So_Lines_Count_Cur INTO v_so_lines_count;
2763 CLOSE Get_So_Lines_Count_Cur ;
2764 IF v_so_lines_count = 0 THEN
2765 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2766 FND_LOG.STRING(G_LEVEL_STATEMENT,
2767 G_MODULE_NAME || v_api_name,
2768 'SO Line Count is 0.');
2769 END IF;
2770 RETURN 'N';
2771 END IF;
2772
2773 RETURN 'Y';
2774
2775 END CHECK_STATUS;
2776
2777 /*
2778 REM +======================================================================+
2779 REM NAME Process Validation
2780 REM
2781 REM DESCRIPTION Called from Process Validation
2782 REM
2783 REM NOTES Extracted from Process Interfaced
2784 REM
2785 REM +======================================================================+
2786 */
2787 PROCEDURE VALIDATE_MATCHED_QTY (pr_new t_rec%type ,
2788 pv_return_code out nocopy varchar2 ,
2789 pv_return_message out nocopy varchar2 )
2790 IS
2791 v_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MATCHED_QTY';
2792 v_source_header_type_id NUMBER ;
2793 v_location_id NUMBER;
2794 v_Organization_Id NUMBER ;
2795 v_trad_register_code VARCHAR2(30);
2796 v_shipped_quantity NUMBER;
2797 v_matched_qty NUMBER;
2798 v_trading_flag VARCHAR2(1);
2799 v_item_trading_flag VARCHAR2(1);
2800 v_subinventory VARCHAR2(10);
2801 v_Inventory_Item_Id NUMBER;
2802 v_exe_flag VARCHAR2(150);
2803 v_mod_flag VARCHAR2(150);
2804 v_delivery_detail_id NUMBER;
2805
2806 CURSOR matched_receipt_cur1 IS
2807 SELECT
2808
2809 sum(a.quantity_applied) quantity_applied
2810 FROM
2811 JAI_CMN_MATCH_RECEIPTS a
2812 WHERE
2813 a.ref_line_id = v_delivery_detail_id;
2814
2815 CURSOR Trading_register_code_cur( p_organization_id NUMBER,
2816 p_location_id NUMBER,
2817 p_order_type_id NUMBER ) IS
2818 SELECT
2819 a.register_code
2820 FROM
2821 JAI_OM_OE_BOND_REG_HDRS a,
2822 JAI_OM_OE_BOND_REG_DTLS b
2823 WHERE
2824 A.organization_id = p_organization_id AND
2825 A.location_id = p_location_id AND
2826 A.register_id = b.register_id AND
2827 b.order_flag = 'Y' AND
2828 b.order_type_id = p_order_type_id AND
2829 A.register_code LIKE '23D%';
2830
2831 CURSOR Location_Cursor IS
2832 SELECT NVL(Location_id,0),
2833 trading
2834 FROM JAI_INV_SUBINV_DTLS
2835 WHERE Sub_Inventory_Name = v_subinventory
2836 AND organization_id = v_organization_id;
2837
2838 CURSOR item_trading_cur IS
2839 SELECT
2840 item_trading_flag
2841 FROM
2842 JAI_INV_ITM_SETUPS
2843 WHERE
2844 organization_id = v_organization_id AND
2845 inventory_item_id = v_inventory_item_id;
2846
2847 CURSOR get_item_attributes IS
2848 SELECT excise_flag, modvat_flag
2849 FROM JAI_INV_ITM_SETUPS
2850 WHERE inventory_item_id = v_Inventory_Item_Id
2851 AND organization_id = v_organization_id;
2852
2853 BEGIN
2854 pv_return_code := jai_constants.successful ;
2855
2856 v_Organization_Id := pr_new.Organization_Id;
2857 v_shipped_quantity := NVL(pr_new.Shipped_Quantity,0);
2858 v_subinventory := pr_new.SUBINVENTORY;
2859 v_source_header_type_id := pr_new.SOURCE_HEADER_TYPE_ID;
2860 v_Inventory_Item_Id := pr_new.Inventory_Item_Id;
2861 v_delivery_detail_id := pr_new.Delivery_Detail_Id;
2862
2863 --Get The Location Id
2864 OPEN Location_Cursor;
2865 FETCH Location_Cursor INTO v_location_id, v_trading_flag;
2866 CLOSE Location_Cursor;
2867
2868 OPEN Trading_register_code_cur(v_organization_id, v_location_id, v_source_header_type_id);
2869 FETCH Trading_register_code_cur INTO v_trad_register_code;
2870 CLOSE Trading_register_code_cur;
2871
2872 OPEN item_trading_cur;
2873 FETCH item_trading_cur INTO v_item_trading_flag;
2874 CLOSE item_trading_cur;
2875 OPEN get_item_attributes;
2876 FETCH get_item_attributes INTO v_exe_flag,v_mod_flag;
2877 CLOSE get_item_attributes;
2878
2879 IF v_trad_register_code IN(
2880 '23D_DOMESTIC_EXCISE',
2881 '23D_EXPORT_EXCISE'
2882 )
2883 THEN
2884
2885 IF NVL(v_trading_flag,'N') = 'Y' AND NVL(V_item_trading_flag,'N') = 'Y' AND NVL(v_exe_flag,'N')= 'Y' THEN
2886 OPEN matched_receipt_cur1;
2887 FETCH matched_receipt_cur1 INTO v_matched_qty;
2888 CLOSE matched_receipt_cur1;
2889 IF NVL(v_shipped_quantity,0) <> NVL(v_matched_qty,0) THEN
2890 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2891 FND_LOG.STRING(G_LEVEL_STATEMENT,
2892 G_MODULE_NAME || v_api_name,
2893 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
2894 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)));
2895 END IF;
2896 pv_return_code := jai_constants.expected_error ;
2897 pv_return_message := 'Matched Quantity -- ' || TO_CHAR(NVL(v_matched_qty,0)) ||
2898 ' should be equal to Shipped Quantity -- ' || TO_CHAR(NVL(v_shipped_quantity,0)) ;
2899 END IF;
2900 END IF;
2901 END IF;
2902
2903 Exception
2904 When Others then
2905 pv_return_code := jai_constants.unexpected_error;
2906 pv_return_message := substr(sqlerrm,1,200) ;
2907 END VALIDATE_MATCHED_QTY;
2908
2909 /*
2910 REM +======================================================================+
2911 REM NAME Validate Currency
2912 REM
2913 REM DESCRIPTION Called from Process Validation
2914 REM
2915 REM NOTES Extracted from Process Interfaced
2916 REM
2917 REM +======================================================================+
2918 */
2919 PROCEDURE VALIDATE_CURRENCY (pr_new t_rec%type ,
2920 pv_return_code out nocopy varchar2 ,
2921 pv_return_message out nocopy varchar2 )
2922 IS
2923 v_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CURRENCY';
2924 v_source_line_id NUMBER;
2925 v_source_header_id NUMBER;
2926 lv_process_message VARCHAR2 (1000);
2927
2928 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2929
2930 CURSOR get_conv_detail_cur
2931 IS
2932 SELECT
2933 transactional_curr_code ,
2934 conversion_type_code ,
2935 conversion_rate ,
2936 nvl(b.actual_shipment_date,sysdate) actual_shipment_date
2937 FROM
2938 oe_order_headers_all a ,
2939 oe_order_lines_all b
2940 WHERE
2941 a.header_id = b.header_id AND
2942 b.line_id = v_source_line_id AND
2943 a.header_id = v_source_header_id ;
2944
2945 v_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
2946 v_set_of_books_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE ;
2947 v_conv_type_code oe_order_headers_all.conversion_type_code%TYPE;
2948 v_conv_rate NUMBER;
2949 v_conv_date DATE;
2950 v_curr_conv_rate NUMBER;
2951
2952 BEGIN
2953 pv_return_code := jai_constants.successful;
2954 v_source_header_id :=pr_new.Source_Header_Id;
2955 v_source_line_id :=pr_new.Source_Line_Id;
2956
2957 IF pr_new.org_id IS NOT NULL THEN
2958 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2959 (p_org_id => pr_new.org_id);
2960 v_currency_code := l_func_curr_det.currency_code;
2961 v_set_of_books_id := l_func_curr_det.ledger_id;
2962 END IF;
2963
2964 OPEN get_conv_detail_cur;
2965 FETCH get_conv_detail_cur INTO v_currency_code, v_conv_type_code,v_conv_rate, v_conv_date;
2966
2967 IF get_conv_detail_cur%FOUND THEN
2968
2969 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion (
2970 v_set_of_books_id ,
2971 v_currency_code ,
2972 v_conv_date ,
2973 v_conv_type_code ,
2974 v_conv_rate
2975 );
2976 IF v_curr_conv_rate IS NULL
2977 THEN
2978 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2979 FND_LOG.STRING(G_LEVEL_STATEMENT,
2980 G_MODULE_NAME || v_api_name,
2981 'Currency Conversion on shipment date not setup.');
2982 END IF;
2983 lv_process_message := 'Currency Conversion on shipment date not setup.';
2984 app_exception.raise_exception
2985 (exception_type => 'APP'
2986 ,exception_code => -20275
2987 ,exception_text => lv_process_message
2988 );
2989 END IF;
2990 END IF;
2991
2992 CLOSE get_conv_detail_cur;
2993
2994 Exception
2995 When Others then
2996 pv_return_code := jai_constants.unexpected_error ;
2997 pv_return_message := substr(sqlerrm,1,200) ;
2998 END VALIDATE_CURRENCY;
2999
3000 /*
3001 REM +======================================================================+
3002 REM NAME Validate TCS Taxes
3003 REM
3004 REM DESCRIPTION Called from Process Validation
3005 REM
3006 REM NOTES Extracted from Process Interfaced
3007 REM
3008 REM +======================================================================+
3009 */
3010 PROCEDURE VALIDATE_TCS_TAXES (pr_new t_rec%type ,
3011 pv_return_code out nocopy varchar2 ,
3012 pv_return_message out nocopy varchar2 )
3013 IS
3014 v_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_TCS_TAXES';
3015 v_delivery_detail_id NUMBER;
3016 ln_tcs_exists number;
3017 ln_tcs_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
3018 v_organization_id NUMBER;
3019 v_customer_id NUMBER;
3020 v_org_id NUMBER;
3021 v_date_confirmed DATE;
3022 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%type;
3023 ln_threshold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
3024
3025 lv_process_flag VARCHAR2(5);
3026 lv_process_message VARCHAR2(1996);
3027
3028 /** Check if taxes with taxType as defined in the regime setup exists for given regime code */
3029 CURSOR C_CHK_RGM_TAX_EXISTS ( CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE
3030 , CP_DELIVERY_DETAIL_ID JAI_OM_WSH_LINE_TAXES.DELIVERY_DETAIL_ID%TYPE)
3031 IS
3032 SELECT COUNT(1)
3033 FROM JAI_REGIME_TAX_TYPES_V JRTTV
3034 , JAI_OM_WSH_LINE_TAXES JSPT
3035 , JAI_CMN_TAXES_ALL JTC
3036 WHERE JTC.TAX_ID = JSPT.TAX_ID
3037 AND JTC.TAX_TYPE = JRTTV.TAX_TYPE
3038 AND REGIME_CODE = CP_REGIME_CODE
3039 AND JSPT.DELIVERY_DETAIL_ID = CP_DELIVERY_DETAIL_ID;
3040
3041 CURSOR Get_Status_Cur IS
3042 SELECT A.confirm_date
3043 FROM Wsh_Delivery_Assignments B,
3044 Wsh_New_deliveries A
3045 WHERE B.Delivery_Id = A.Delivery_Id
3046 AND B.Delivery_Detail_id = v_delivery_detail_id;
3047
3048 CURSOR C_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
3049 IS
3050 SELECT REGIME_ID
3051 FROM JAI_RGM_DEFINITIONS
3052 WHERE REGIME_CODE = CP_REGIME_CODE;
3053
3054 BEGIN
3055 pv_return_code := jai_constants.successful;
3056 v_delivery_detail_id := pr_new.delivery_detail_id;
3057 v_organization_id := pr_new.organization_id;
3058 v_customer_id := pr_new.CUSTOMER_ID;
3059 v_org_id := pr_new.ORG_ID;
3060 /**
3061 Check if TCS type of taxes exists, If yes using the threshold API found out the slab and the tax category id
3062 and delegate the call tax defaultation API
3063 */
3064 ln_tcs_exists := 0;
3065 open c_chk_rgm_tax_exists ( cp_regime_code => jai_constants.tcs_regime
3066 , cp_delivery_detail_id => v_delivery_detail_id);
3067 fetch c_chk_rgm_tax_exists into ln_tcs_exists;
3068 close c_chk_rgm_tax_exists ;
3069
3070 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3071 FND_LOG.STRING(G_LEVEL_STATEMENT,
3072 G_MODULE_NAME || v_api_name,
3073 'tcs regime code :' || jai_constants.tcs_regime ||
3074 ', delivery_detail_id :' || v_delivery_detail_id ||
3075 ', ln_tcs_exists :' || ln_tcs_exists);
3076 END IF;
3077
3078 if nvl(ln_tcs_exists,0) >0 then
3079 /* TCS type of tax is present */
3080 fnd_file.put_line(FND_FILE.LOG,'Localization' );
3081
3082 open c_get_regime_id (cp_regime_code => jai_constants.tcs_regime);
3083 fetch c_get_regime_id into ln_tcs_regime_id;
3084 close c_get_regime_id;
3085
3086 -- Check for the Delivery Status
3087 OPEN Get_Status_Cur;
3088 FETCH Get_Status_Cur INTO v_date_confirmed;
3089 CLOSE Get_Status_Cur;
3090
3091 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
3092 (
3093 p_regime_id => ln_tcs_regime_id
3094 , p_organization_id => v_organization_id
3095 , p_party_type => jai_constants.party_type_customer
3096 , p_party_id => v_customer_id
3097 , p_org_id => v_org_id
3098 , p_source_trx_date => v_date_confirmed
3099 , p_threshold_slab_id => ln_threshold_slab_id
3100 , p_process_flag => lv_process_flag
3101 , p_process_message => lv_process_message
3102 );
3103
3104 if lv_process_flag <> jai_constants.successful then
3105 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3106 FND_LOG.STRING(G_LEVEL_STATEMENT,
3107 G_MODULE_NAME || v_api_name,
3108 'Error in jai_rgm_thhold_proc_pkg.get_threshold_slab_id. ' || lv_process_message);
3109 END IF;
3110 app_exception.raise_exception
3111 (exception_type => 'APP'
3112 ,exception_code => -20275
3113 ,exception_text => lv_process_message
3114 );
3115 end if;
3116
3117 if ln_threshold_slab_id is not null then
3118 /* Threshold level is up. Surcharge needs to be defaulted , so find out the tax category based on the threshold slab */
3119 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3120 FND_LOG.STRING(G_LEVEL_STATEMENT,
3121 G_MODULE_NAME || v_api_name,
3122 'Before jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id.' ||
3123 'ln_threshold_slab_id :' || ln_threshold_slab_id);
3124 END IF;
3125 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
3126 (
3127 p_threshold_slab_id => ln_threshold_slab_id
3128 , p_org_id => v_org_id
3129 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
3130 , p_process_flag => lv_process_flag
3131 , p_process_message => lv_process_message
3132 );
3133
3134 if lv_process_flag <> jai_constants.successful then
3135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3136 FND_LOG.STRING(G_LEVEL_STATEMENT,
3137 G_MODULE_NAME || v_api_name,
3138 'Error in jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id. ' || lv_process_message);
3139 END IF;
3140 app_exception.raise_exception
3141 (exception_type => 'APP'
3142 ,exception_code => -20275
3143 ,exception_text => lv_process_message
3144 );
3145 end if;
3146 end if;
3147 end if;
3148 Exception
3149 When Others then
3150 pv_return_code := jai_constants.unexpected_error ;
3151 pv_return_message := substr(sqlerrm,1,200) ;
3152 END VALIDATE_TCS_TAXES;
3153
3154 /*
3155 REM +======================================================================+
3156 REM NAME Validate WSH Interim Accounting
3157 REM
3158 REM DESCRIPTION Called from Process Validation
3159 REM
3160 REM NOTES Extracted from Process Interfaced
3161 REM
3162 REM +======================================================================+
3163 */
3164 PROCEDURE VALIDATE_WSH_INTERIM_ACCT (p_delivery_id number,
3165 pr_new t_rec%type ,
3166 pv_return_code out nocopy varchar2 ,
3167 pv_return_message out nocopy varchar2 )
3168 IS
3169
3170 v_int_liab_acc_ccid NUMBER;
3171 v_liab_acc_ccid NUMBER;
3172 v_delivery_id NUMBER;
3173 v_delivery_detail_id NUMBER;
3174 v_organization_Id NUMBER;
3175 v_location_id NUMBER;
3176 v_subinventory VARCHAR2(10);
3177
3178 CURSOR cur_get_picking_taxes
3179 IS
3180 SELECT
3181 jsptl.* ,
3182 jrttv.regime_id,
3183 jtc.tax_type
3184 FROM
3185 JAI_OM_WSH_LINES_ALL jspl ,
3186 JAI_OM_WSH_LINE_TAXES jsptl,
3187 JAI_CMN_TAXES_ALL jtc ,
3188 jai_regime_tax_types_v jrttv
3189 WHERE
3190 jspl.delivery_detail_id = jsptl.delivery_detail_id
3191 AND jspl.delivery_id = v_delivery_id
3192 AND jspl.delivery_detail_id = v_delivery_detail_id
3193 AND jsptl.tax_id = jtc.tax_id
3194 AND jtc.tax_type = jrttv.tax_type
3195 AND jrttv.regime_code = jai_constants.tcs_regime;
3196
3197 CURSOR c_location_id IS
3198 SELECT NVL(Location_id,0)
3199 FROM JAI_INV_SUBINV_DTLS
3200 WHERE Sub_Inventory_Name = v_subinventory
3201 AND organization_id = v_organization_id;
3202
3203 BEGIN
3204 pv_return_code := jai_constants.successful;
3205
3206 v_delivery_id := p_delivery_id;
3207 v_delivery_detail_id := pr_new.delivery_detail_id;
3208 v_organization_id := pr_new.organization_id;
3209 v_subinventory := pr_new.SUBINVENTORY;
3210
3211 --Get The Location Id
3212 OPEN c_location_id;
3213 FETCH c_location_id INTO v_location_id;
3214 CLOSE c_location_id;
3215
3216 IF v_location_id IS NULL THEN
3217 v_location_id := pr_new.ship_from_location_id ;
3218 END IF;
3219
3220 FOR rec_cur_get_picking_taxes IN cur_get_picking_taxes
3221 LOOP
3222
3223
3224 /*********************************************************************************************************
3225 || Get the code combination id from the Organization/Regime Registration setup
3226 || by calling the function jai_cmn_rgm_recording_pkg.get_account
3227 *********************************************************************************************************/
3228 v_liab_acc_ccid := jai_cmn_rgm_recording_pkg.get_account (
3229 p_regime_id => rec_cur_get_picking_taxes.regime_id ,
3230 p_organization_type => jai_constants.orgn_type_io ,
3231 p_organization_id => v_organization_id ,
3232 p_location_id => v_location_id ,
3233 p_tax_type => rec_cur_get_picking_taxes.tax_type ,
3234 p_account_name => jai_constants.liability
3235 );
3236
3237 v_int_liab_acc_ccid := jai_cmn_rgm_recording_pkg.get_account (
3238 p_regime_id => rec_cur_get_picking_taxes.regime_id ,
3239 p_organization_type => jai_constants.orgn_type_io ,
3240 p_organization_id => v_organization_id ,
3241 p_location_id => v_location_id ,
3242 p_tax_type => rec_cur_get_picking_taxes.tax_type ,
3243 p_account_name => jai_constants.liability_interim
3244 );
3245
3246
3247
3248 IF v_int_liab_acc_ccid IS NULL OR
3249 v_liab_acc_ccid IS NULL
3250 THEN
3251 /**********************************************************************************************************
3252 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
3253 || This is an error condition and the current processing has to be stopped
3254 **********************************************************************************************************/
3255 pv_return_code := jai_constants.expected_error;
3256 pv_return_message := 'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup';
3257 return;
3258 END IF;
3259 END LOOP;
3260
3261 EXCEPTION
3262 WHEN OTHERS THEN
3263 pv_return_code := jai_constants.unexpected_error;
3264 pv_return_message := 'Unexpected error in the jai_om_wdd_processing_pkg.validate_wsh_interim_accounting '||substr(sqlerrm,1,300);
3265
3266 END VALIDATE_WSH_INTERIM_ACCT;
3267
3268
3269 /*
3270 REM +======================================================================+
3271 REM NAME Process Validation
3272 REM
3273 REM DESCRIPTION Called from JAI_OM_WDD_ARIUD_TRG
3274 REM
3275 REM NOTES Extracted from Process Interfaced
3276 REM
3277 REM +======================================================================+
3278 */
3279 PROCEDURE PROCESS_VALIDATION (p_delivery_id number,
3280 p_rec_new t_rec%type ,
3281 px_return_code out nocopy varchar2,
3282 px_exp_msg out nocopy exp_tbl_type)
3283
3284 IS
3285
3286 lt_exp_msg exp_tbl_type := exp_tbl_type();
3287 rec_exp_msg JAI_WSH_EXCEPTIONS_T%ROWTYPE;
3288
3289 v_api_name CONSTANT VARCHAR2(30) := 'PROCESS_VALIDATION';
3290 v_exp_entity CONSTANT VARCHAR2(30) := 'OM_INTERFACE';
3291 lv_delivery_id NUMBER;
3292 lv_curr_conv_rate NUMBER;
3293 lv_return_code VARCHAR2(240);
3294 lv_return_message VARCHAR2(240);
3295
3296 BEGIN
3297
3298 px_return_code := jai_constants.successful;
3299 lv_delivery_id := p_delivery_id;
3300
3301 if check_status (pr_new => p_rec_new) = 'N' then
3302 return;
3303 end if;
3304
3305 validate_matched_qty(pr_new => p_rec_new,
3306 pv_return_code => lv_return_code,
3307 pv_return_message => lv_return_message);
3308
3309 if lv_return_code <> jai_constants.successful then
3310 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3311 FND_LOG.STRING(G_LEVEL_STATEMENT,
3312 G_MODULE_NAME || v_api_name,
3313 'validate_matched_qty returned with ' || lv_return_code || lv_return_message);
3314 END IF;
3315
3316 px_return_code := lv_return_code;
3317
3318 rec_exp_msg.EXCEPTION_TYPE := 'U';
3319 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3320 rec_exp_msg.ERROR_MESSAGE := lv_return_message;
3321
3322 lt_exp_msg.EXTEND;
3323 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3324
3325 end if;
3326
3327 validate_currency(pr_new => p_rec_new,
3328 pv_return_code => lv_return_code,
3329 pv_return_message => lv_return_message);
3330
3331 if lv_return_code <> jai_constants.successful then
3332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3333 FND_LOG.STRING(G_LEVEL_STATEMENT,
3334 G_MODULE_NAME || v_api_name,
3335 'validate_currency returned with ' || lv_return_code || lv_return_message);
3336 END IF;
3337
3338 px_return_code := lv_return_code;
3339
3340 rec_exp_msg.EXCEPTION_TYPE := 'U';
3341 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3342 rec_exp_msg.ERROR_MESSAGE := lv_return_message;
3343
3344 lt_exp_msg.EXTEND;
3345 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3346
3347 end if;
3348
3349 validate_tcs_taxes(pr_new => p_rec_new,
3350 pv_return_code => lv_return_code,
3351 pv_return_message => lv_return_message);
3352
3353 if lv_return_code <> jai_constants.successful then
3354 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3355 FND_LOG.STRING(G_LEVEL_STATEMENT,
3356 G_MODULE_NAME || v_api_name,
3357 'validate_tcs_taxes returned with ' || lv_return_code || lv_return_message);
3358 END IF;
3359
3360 px_return_code := lv_return_code;
3361
3362 rec_exp_msg.EXCEPTION_TYPE := 'U';
3363 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3364 rec_exp_msg.ERROR_MESSAGE := lv_return_message;
3365
3366 lt_exp_msg.EXTEND;
3367 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3368
3369 end if;
3370
3371 validate_wsh_interim_acct(p_delivery_id => lv_delivery_id,
3372 pr_new => p_rec_new,
3373 pv_return_code => lv_return_code,
3374 pv_return_message => lv_return_message);
3375
3376 /*if px_return_code <> jai_constants.successful then*/ -- commented out by zhiwei.xin for bug #16207271 on 5-FEB-2013
3377 if lv_return_code <> jai_constants.successful then -- added by zhiwei.xin for bug #16207271 on 5-FEB-2013
3378 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3379 FND_LOG.STRING(G_LEVEL_STATEMENT,
3380 G_MODULE_NAME || v_api_name,
3381 'validate_wsh_interim_acct returned with ' || lv_return_code || lv_return_message);
3382
3383 END IF;
3384
3385 px_return_code := lv_return_code;
3386
3387 rec_exp_msg.EXCEPTION_TYPE := 'U';
3388 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3389 rec_exp_msg.ERROR_MESSAGE := lv_return_message;
3390
3391 lt_exp_msg.EXTEND;
3392 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3393
3394 end if;
3395
3396 px_exp_msg := lt_exp_msg;
3397
3398 Exception
3399 When Others then
3400 lv_return_code := jai_constants.unexpected_error;
3401 lv_return_message := substr(sqlerrm,1,200) ;
3402 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3403 FND_LOG.STRING(G_LEVEL_STATEMENT,
3404 G_MODULE_NAME || v_api_name,
3405 'Exception with ' || lv_return_code || lv_return_message);
3406 END IF;
3407
3408 px_return_code := lv_return_code;
3409
3410 rec_exp_msg.EXCEPTION_TYPE := 'E';
3411 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3412 rec_exp_msg.ERROR_MESSAGE := lv_return_message;
3413
3414 lt_exp_msg.EXTEND;
3415 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3416
3417 px_exp_msg := lt_exp_msg;
3418
3419 END PROCESS_VALIDATION;
3420
3421 /*
3422 REM +======================================================================+
3423 REM NAME BE_INTERFACED
3424 REM
3425 REM DESCRIPTION Called from business event 'oracle.apps.wsh.delivery.oe.interfaced'
3426 REM
3427 REM NOTES
3428 REM
3429 REM +======================================================================+
3430 */
3431 FUNCTION BE_INTERFACED(p_subscription_guid IN raw,
3432 p_event IN OUT nocopy WF_EVENT_T) RETURN VARCHAR2
3433 IS
3434
3435 v_api_name CONSTANT VARCHAR2(30) := 'BE_INTERFACED';
3436 v_exp_entity CONSTANT VARCHAR2(30) := 'OM_INTERFACE'; -- added for wsh exception handling on 4-JAN-2013
3437 l_eventname VARCHAR2(240);
3438 l_eventkey VARCHAR2(240);
3439 lv_return_code VARCHAR2(240);
3440 lv_return_msg VARCHAR2(240);
3441
3442 le_sub Exception;
3443 le_val Exception;
3444 lt_exp_msg exp_tbl_type := exp_tbl_type();
3445 rec_exp_msg JAI_WSH_EXCEPTIONS_T%ROWTYPE;
3446
3447 cursor c_get_delivery_details(p_delivery_id wsh_delivery_assignments.delivery_id%type)
3448 is
3449 SELECT *
3450 FROM wsh_delivery_details
3451 WHERE delivery_detail_id IN
3452 ( SELECT
3453 delivery_detail_id
3454 FROM
3455 wsh_delivery_assignments
3456 WHERE
3457 delivery_id = p_delivery_id
3458 );
3459 BEGIN
3460 l_eventname := p_event.getEventName;
3461 l_eventkey := p_event.getEventKey;
3462
3463 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3464 FND_LOG.STRING(G_LEVEL_STATEMENT,
3465 G_MODULE_NAME || v_api_name,
3466 'Event Name:' || l_eventname || ',Event Key :' || l_eventkey);
3467 END IF;
3468
3469 IF l_eventname = 'oracle.apps.wsh.delivery.oe.interfaced' THEN
3470
3471 -- added for wsh exception handling on 4-JAN-2013 begin
3472 CLEAR_EXP_MSG(p_delivery_id => to_number(l_eventkey),
3473 p_exp_entity => v_exp_entity);
3474 -- added for wsh exception handling on 4-JAN-2013 end.
3475
3476 FOR rec_wdd IN c_get_delivery_details (to_number(l_eventkey))
3477 LOOP
3478
3479 PROCESS_VALIDATION (p_delivery_id => to_number(l_eventkey),
3480 p_rec_new => rec_wdd,
3481 px_return_code => lv_return_code,
3482 px_exp_msg => lt_exp_msg);
3483
3484 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3485 FND_LOG.STRING(G_LEVEL_STATEMENT,
3486 G_MODULE_NAME || v_api_name,
3487 'Validation processing returned with ' || lv_return_code);
3488 END IF;
3489
3490 IF lv_return_code <> jai_constants.successful then
3491 raise le_val;
3492 END IF;
3493
3494 PROCESS_INTERFACED (pr_old => null,
3495 pr_new => rec_wdd,
3496 pv_action => jai_constants.updating,
3497 pv_return_code => lv_return_code,
3498 pv_return_message => lv_return_msg);
3499
3500 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3501 FND_LOG.STRING(G_LEVEL_STATEMENT,
3502 G_MODULE_NAME || v_api_name,
3503 'Interfaced processing returned with ' || lv_return_code || lv_return_msg);
3504 END IF;
3505
3506 IF lv_return_code <> jai_constants.successful then
3507 raise le_sub;
3508 END IF;
3509
3510 /* accounting entries for RG23D reversal */
3511 RG23D_REV_ACCOUNTING(pr_trig_row => rec_wdd ,
3512 pv_return_code => lv_return_code ,
3513 pv_return_message => lv_return_msg);
3514
3515 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3516 FND_LOG.STRING(G_LEVEL_STATEMENT,
3517 G_MODULE_NAME || v_api_name,
3518 'accounting entries for RG23D reversal returned with ' || lv_return_code || lv_return_msg);
3519 END IF;
3520
3521 IF lv_return_code <> jai_constants.successful then
3522 raise le_sub;
3523 END IF;
3524 END LOOP;
3525
3526 END IF;
3527
3528 RETURN 'SUCCESS';
3529
3530 EXCEPTION
3531 WHEN LE_VAL THEN
3532 -- added for wsh exception handling on 4-JAN-2013 begin
3533 POPULATE_EXP_MSG(p_delivery_id => to_number(l_eventkey),
3534 p_exp_msg => lt_exp_msg);
3535 -- added for wsh exception handling on 4-JAN-2013 end.
3536 RETURN 'ERROR';
3537 WHEN LE_SUB THEN
3538 -- added for wsh exception handling on 4-JAN-2013 begin
3539 rec_exp_msg.EXCEPTION_TYPE := 'U';
3540 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3541 rec_exp_msg.ERROR_MESSAGE := lv_return_msg;
3542
3543 lt_exp_msg.EXTEND;
3544 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3545
3546 POPULATE_EXP_MSG(p_delivery_id => to_number(l_eventkey),
3547 p_exp_msg => lt_exp_msg);
3548 -- added for wsh exception handling on 4-JAN-2013 end.
3549 RETURN 'ERROR';
3550 WHEN OTHERS THEN
3551 -- added for wsh exception handling on 4-JAN-2013 begin
3552 lv_return_code := jai_constants.unexpected_error;
3553 lv_return_msg := substr(sqlerrm,1,200);
3554
3555 rec_exp_msg.EXCEPTION_TYPE := 'E';
3556 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3557 rec_exp_msg.ERROR_MESSAGE := lv_return_msg;
3558
3559 lt_exp_msg.EXTEND;
3560 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3561
3562 POPULATE_EXP_MSG(p_delivery_id => to_number(l_eventkey),
3563 p_exp_msg => lt_exp_msg);
3564 -- added for wsh exception handling on 4-JAN-2013 end.
3565 RETURN 'ERROR';
3566
3567 END BE_INTERFACED;
3568
3569 /*
3570 REM +======================================================================+
3571 REM NAME BE_RELEASED
3572 REM
3573 REM DESCRIPTION Called from business event 'oracle.apps.wsh.line.gen.staged'
3574 REM
3575 REM NOTES
3576 REM
3577 REM +======================================================================+
3578 */
3579 FUNCTION BE_RELEASED(p_subscription_guid IN raw,
3580 p_event IN OUT nocopy WF_EVENT_T) RETURN VARCHAR2
3581 IS
3582
3583 v_api_name CONSTANT VARCHAR2(30) := 'BE_RELEASED';
3584 l_eventname VARCHAR2(240);
3585 l_eventkey VARCHAR2(240);
3586 lv_return_code VARCHAR2(240);
3587 lv_return_msg VARCHAR2(240);
3588
3589 le_sub Exception;
3590
3591 cursor c_get_delivery_details(p_delivery_id wsh_delivery_assignments.delivery_id%type)
3592 is
3593 SELECT *
3594 FROM wsh_delivery_details
3595 WHERE delivery_detail_id IN
3596 ( SELECT
3597 delivery_detail_id
3598 FROM
3599 wsh_delivery_assignments
3600 WHERE
3601 delivery_id = p_delivery_id
3602 );
3603 BEGIN
3604 l_eventname := p_event.getEventName;
3605 l_eventkey := p_event.getEventKey;
3606
3607 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3608 FND_LOG.STRING(G_LEVEL_STATEMENT,
3609 G_MODULE_NAME || v_api_name,
3610 'Event Name:' || l_eventname || ',Event Key :' || l_eventkey);
3611 END IF;
3612
3613 IF l_eventname = 'oracle.apps.wsh.line.gen.staged' THEN
3614 FOR rec_wdd IN c_get_delivery_details (to_number(l_eventkey))
3615 LOOP
3616 PROCESS_RELEASED (pr_old => null,
3617 pr_new => rec_wdd,
3618 pv_action => jai_constants.updating,
3619 pv_return_code => lv_return_code,
3620 pv_return_message => lv_return_msg);
3621
3622 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3623 FND_LOG.STRING(G_LEVEL_STATEMENT,
3624 G_MODULE_NAME || v_api_name,
3625 'Released processing returned with ' || lv_return_code || lv_return_msg);
3626 END IF;
3627
3628 IF lv_return_code <> jai_constants.successful then
3629 raise le_sub;
3630 END IF;
3631 END LOOP;
3632 END IF;
3633
3634 RETURN 'SUCCESS';
3635
3636 EXCEPTION
3637 WHEN LE_SUB THEN
3638 RETURN 'ERROR';
3639 WHEN OTHERS THEN
3640 RETURN 'ERROR';
3641
3642 END BE_RELEASED;
3643
3644 /*
3645 REM +=====================================================================================+
3646 REM NAME INV_INTERFACED
3647 REM
3648 REM DESCRIPTION Called from business event 'oracle.apps.wsh.delivery.gen.interfaced'
3649 REM
3650 REM NOTES
3651 REM Added by zhiwei.xin for VAT Invoice Generation/Accounting on 6-NOV-2012
3652 REM +=====================================================================================+
3653 */
3654 FUNCTION INV_INTERFACED(p_subscription_guid IN raw,
3655 p_event IN OUT nocopy WF_EVENT_T) RETURN VARCHAR2
3656 IS
3657
3658 v_api_name CONSTANT VARCHAR2(30) := 'INV_INTERFACED';
3659 v_exp_entity CONSTANT VARCHAR2(30) := 'INVENTORY_INTERFACE'; -- added for bug#16167577 wsh exception handling on 4-JAN-2013
3660 l_eventname VARCHAR2(240);
3661 l_eventkey VARCHAR2(240);
3662 lv_return_code VARCHAR2(240);
3663 lv_return_msg VARCHAR2(240);
3664 lv_regime_id number;
3665 lv_reg_num varchar2(240);
3666 lv_delivery_date date;
3667 lv_location_id number;
3668 ln_req_id number;
3669
3670 le_sub Exception;
3671 lt_exp_msg exp_tbl_type := exp_tbl_type();
3672 rec_exp_msg JAI_WSH_EXCEPTIONS_T%ROWTYPE;
3673
3674 BEGIN
3675 l_eventname := p_event.getEventName;
3676 l_eventkey := p_event.getEventKey;
3677
3678 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3679 FND_LOG.STRING(G_LEVEL_STATEMENT,
3680 G_MODULE_NAME || v_api_name,
3681 'Event Name:' || l_eventname || ',Event Key :' || l_eventkey);
3682 END IF;
3683
3684 IF l_eventname = 'oracle.apps.wsh.delivery.gen.interfaced' THEN
3685
3686 -- added by zhiwei.xin for bug#16167577 wsh exception handling on 4-JAN-2013
3687 -- clear GT table
3688 CLEAR_EXP_MSG(p_delivery_id => to_number(l_eventKey),
3689 p_exp_entity => v_exp_entity);
3690
3691 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3692 FND_LOG.STRING(G_LEVEL_STATEMENT,
3693 G_MODULE_NAME || v_api_name,
3694 'Process Inventory. Delivery Id : ' || l_eventKey);
3695 END IF;
3696 -- process inventory for VAT/Excise Invoice Generation
3697 process_inventory(pv_return_code => lv_return_code,
3698 pv_return_msg => lv_return_msg,
3699 p_delivery_id => to_number(l_eventKey));
3700
3701 if lv_return_code <> '0' then
3702 raise le_sub;
3703 end if;
3704
3705
3706 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3707 FND_LOG.STRING(G_LEVEL_STATEMENT,
3708 G_MODULE_NAME || v_api_name,
3709 'Submit status check CP. Delivery_id : ' || l_eventKey);
3710 END IF;
3711
3712 -- added by zhiwei.xin for bug#16167577 wsh exception handling on 4-JAN-2013
3713 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
3714 'JAICDOII',
3715 '',
3716 '',
3717 FALSE,
3718 l_eventKey);
3719 END IF;
3720
3721 RETURN 'SUCCESS';
3722
3723 EXCEPTION
3724 WHEN LE_SUB THEN
3725
3726 rec_exp_msg.EXCEPTION_TYPE := 'U';
3727 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3728 rec_exp_msg.ERROR_MESSAGE := lv_return_msg;
3729
3730 lt_exp_msg.EXTEND;
3731 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3732
3733 POPULATE_EXP_MSG(p_delivery_id => to_number(l_eventkey),
3734 p_exp_msg => lt_exp_msg);
3735
3736 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3737 FND_LOG.STRING(G_LEVEL_STATEMENT,
3738 G_MODULE_NAME || v_api_name,
3739 'Submit status check CP. Delivery_id : ' || l_eventKey);
3740 END IF;
3741
3742 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
3743 'JAICDOII',
3744 '',
3745 '',
3746 FALSE,
3747 l_eventKey);
3748
3749 RETURN 'ERROR';
3750
3751 WHEN OTHERS THEN
3752 lv_return_code := jai_constants.unexpected_error;
3753 lv_return_msg := substr(sqlerrm,1,200);
3754
3755 rec_exp_msg.EXCEPTION_TYPE := 'E';
3756 rec_exp_msg.EXCEPTION_ENTITY := v_exp_entity;
3757 rec_exp_msg.ERROR_MESSAGE := lv_return_msg;
3758
3759 lt_exp_msg.EXTEND;
3760 lt_exp_msg(lt_exp_msg.LAST) := rec_exp_msg;
3761
3762 POPULATE_EXP_MSG(p_delivery_id => to_number(l_eventkey),
3763 p_exp_msg => lt_exp_msg);
3764
3765 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3766 FND_LOG.STRING(G_LEVEL_STATEMENT,
3767 G_MODULE_NAME || v_api_name,
3768 'Submit status check CP. Delivery_id : ' || l_eventKey);
3769 END IF;
3770
3771 ln_req_id := FND_REQUEST.SUBMIT_REQUEST('JA',
3772 'JAICDOII',
3773 '',
3774 '',
3775 FALSE,
3776 l_eventKey);
3777 RETURN 'ERROR';
3778
3779 END INV_INTERFACED;
3780
3781 /*
3782 REM +=====================================================================================+
3783 REM NAME PROCESS_INVENTORY
3784 REM
3785 REM DESCRIPTION Called from INV_INTERFACED
3786 REM
3787 REM NOTES
3788 REM Added by zhiwei.xin for bug#16167577 WSH Exception Handling on 4-JAN-2013
3789 REM +=====================================================================================+
3790 */
3791 PROCEDURE PROCESS_INVENTORY(pv_return_code OUT NOCOPY VARCHAR2,
3792 pv_return_msg OUT NOCOPY VARCHAR2,
3793 p_delivery_id NUMBER)
3794 IS
3795
3796 v_api_name CONSTANT VARCHAR2(30) := 'PROCESS_INVENTORY';
3797 v_interface_name CONSTANT VARCHAR2(30) := 'INVENTORY_INTERFACE';
3798
3799 lv_regime_id number;
3800 lv_reg_num varchar2(240);
3801 lv_delivery_date date;
3802 lv_location_id number;
3803 ln_req_id number;
3804 lv_delivery_id number;
3805
3806 cursor c_get_delivery_details(p_delivery_id wsh_delivery_assignments.delivery_id%type)
3807 is
3808 SELECT *
3809 FROM wsh_delivery_details
3810 WHERE delivery_detail_id IN
3811 ( SELECT
3812 delivery_detail_id
3813 FROM
3814 wsh_delivery_assignments
3815 WHERE
3816 delivery_id = p_delivery_id
3817 );
3818
3819 cursor c_get_vat_regime
3820 is
3821 select regime_id
3822 from JAI_RGM_DEFINITIONS
3823 where regime_code = jai_constants.vat_regime;
3824
3825 cursor c_deliver_date(p_delivery_id JAI_RGM_INVOICE_GEN_T.delivery_id%type)
3826 is
3827 select delivery_date
3828 from JAI_RGM_INVOICE_GEN_T
3829 where delivery_id = p_delivery_id;
3830
3831 CURSOR c_get_loc_id (cp_subinventory wsh_delivery_details.SUBINVENTORY%TYPE, cp_organization_id wsh_delivery_details.ORGANIZATION_ID%TYPE)IS
3832 SELECT Location_id
3833 FROM JAI_INV_SUBINV_DTLS
3834 WHERE Sub_Inventory_Name = cp_subinventory
3835 AND organization_id = cp_organization_id;
3836
3837 CURSOR c_reg_num(cp_regime_id number,cp_organization_id wsh_delivery_details.ORGANIZATION_ID%TYPE , cp_location_id wsh_delivery_details.ship_to_location_id%TYPE ) IS
3838 SELECT attribute_Value
3839 FROM JAI_RGM_ORG_REGNS_V
3840 WHERE regime_id = cp_regime_id
3841 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
3842 AND attribute_code = jai_constants.attr_code_regn_no
3843 AND organization_id = cp_organization_id
3844 AND location_id = cp_location_id;
3845
3846 BEGIN
3847 lv_delivery_id := p_delivery_id;
3848
3849 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3850 FND_LOG.STRING(G_LEVEL_STATEMENT,
3851 G_MODULE_NAME || v_api_name,
3852 'Process Inventory Started. p_delivery_id :' || lv_delivery_id);
3853 END IF;
3854
3855 open c_get_vat_regime;
3856 fetch c_get_vat_regime into lv_regime_id ;
3857 close c_get_vat_regime;
3858
3859 open c_deliver_date(lv_delivery_id);
3860 fetch c_deliver_date into lv_delivery_date;
3861 close c_deliver_date;
3862
3863 FOR rec_wdd IN c_get_delivery_details (lv_delivery_id)
3864 LOOP
3865 -- get location id
3866 open c_get_loc_id(rec_wdd.subinventory, rec_wdd.organization_id);
3867 fetch c_get_loc_id into lv_location_id;
3868 close c_get_loc_id;
3869
3870 if nvl(lv_location_id, 0) = 0 then
3871 lv_location_id := rec_wdd.ship_from_location_id;
3872 end if;
3873
3874 open c_reg_num(lv_regime_id, rec_wdd.organization_id, lv_location_id);
3875 fetch c_reg_num into lv_reg_num ;
3876 close c_reg_num;
3877
3878 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3879 FND_LOG.STRING(G_LEVEL_STATEMENT,
3880 G_MODULE_NAME || v_api_name,
3881 'Parameters for Excise Invoice Generation.' ||
3882 'pn_delivery_id : ' || lv_delivery_id);
3883 END IF;
3884
3885 -- Excise Invoice Generation
3886 jai_om_wsh_pkg.process_deliveries (errbuf => pv_return_msg,
3887 retcode => pv_return_code,
3888 pn_delivery_id => lv_delivery_id);
3889
3890 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3891 FND_LOG.STRING(G_LEVEL_STATEMENT,
3892 G_MODULE_NAME || v_api_name,
3893 'Excise Invoice Generation returned with ' || pv_return_code || pv_return_msg);
3894 END IF;
3895
3896 IF pv_return_code <> '0' then
3897 return;
3898 END IF;
3899
3900 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3901 FND_LOG.STRING(G_LEVEL_STATEMENT,
3902 G_MODULE_NAME || v_api_name,
3903 'Parameters for VAT Invoice Generation / Accounting.' ||
3904 'p_regime_id : ' || lv_regime_id ||
3905 ',p_registration_num : ' || lv_reg_num ||
3906 ',p_organization_id : ' || rec_wdd.organization_id ||
3907 ',p_location_id : ' || lv_location_id ||
3908 ',p_order_number_from : ' || 'null' ||
3909 ',p_order_number_to : ' || 'null' ||
3910 ',p_delivery_id_from : ' || lv_delivery_id ||
3911 ',p_delivery_id_to : ' || lv_delivery_id ||
3912 ',pv_delivery_date_from : ' || to_char(lv_delivery_date,'yyyy-MM-dd HH24:MI:SS') ||
3913 ',pv_delivery_date_to : ' || to_char(lv_delivery_date,'yyyy-MM-dd HH24:MI:SS') ||
3914 ',p_process_action : ' || jai_constants.om_action_gen_inv_n_accnt ||
3915 ',p_single_invoice_num : ' || 'N' ||
3916 ',p_override_invoice_date : ' || to_char(sysdate,'yyyy-MM-dd HH24:MI:SS') ||
3917 ',p_debug : ' || jai_constants.no ||
3918 ',p_called_from : ' || 'BE');
3919 END IF;
3920
3921 -- VAT Invoice Generation / Accounting
3922 JAI_CMN_RGM_PROCESSING_PKG.process (retcode => pv_return_code,
3923 errbuf => pv_return_msg,
3924 p_regime_id => lv_regime_id,
3925 p_registration_num => lv_reg_num,
3926 p_organization_id => rec_wdd.organization_id,
3927 p_location_id => lv_location_id,
3928 p_order_number_from => null,
3929 p_order_number_to => null,
3930 p_delivery_id_from => lv_delivery_id,
3931 p_delivery_id_to => lv_delivery_id,
3932 pv_delivery_date_from => to_char(lv_delivery_date,'yyyy-MM-dd HH24:MI:SS'),
3933 pv_delivery_date_to => to_char(lv_delivery_date,'yyyy-MM-dd HH24:MI:SS'),
3934 p_process_action => jai_constants.om_action_gen_inv_n_accnt,
3935 p_single_invoice_num => 'N',
3936 p_override_invoice_date => sysdate,
3937 p_debug => jai_constants.no,
3938 p_called_from => 'BE');
3939
3940 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3941 FND_LOG.STRING(G_LEVEL_STATEMENT,
3942 G_MODULE_NAME || v_api_name,
3943 'VAT Invoice Generation / Accounting returned with ' || pv_return_code || pv_return_msg);
3944 END IF;
3945
3946 IF pv_return_code <> '0' then
3947 return;
3948 END IF;
3949
3950 END LOOP;
3951
3952 EXCEPTION
3953
3954 WHEN OTHERS THEN
3955 raise;
3956
3957 END PROCESS_INVENTORY;
3958
3959 /*
3960 REM +======================================================================+
3961 REM NAME Clear Exception Details
3962 REM
3963 REM DESCRIPTION Called from BE_INTERFACED, INV_INTERFACED
3964 REM
3965 REM NOTES
3966 REM Added by zhiwei.xin for bug#16167577 WSH Exception Handling on 4-JAN-2013
3967 REM +======================================================================+
3968 */
3969 PROCEDURE CLEAR_EXP_MSG (p_delivery_id number,
3970 p_exp_entity varchar2)
3971 IS
3972
3973 PRAGMA AUTONOMOUS_TRANSACTION;
3974
3975 cursor c_error_exist is
3976 select 'Y'
3977 from JAI_WSH_EXCEPTIONS_T
3978 where DELIVERY_ID = p_delivery_id and
3979 EXCEPTION_ENTITY = p_exp_entity;
3980
3981 v_api_name CONSTANT VARCHAR2(30) := 'CLEAR_EXP_MSG';
3982 lv_err_exist varchar2(1);
3983
3984 BEGIN
3985 open c_error_exist;
3986 fetch c_error_exist into lv_err_exist;
3987 close c_error_exist;
3988
3989 if nvl(lv_err_exist, 'N') = 'Y' then
3990
3991 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3992 FND_LOG.STRING(G_LEVEL_STATEMENT,
3993 G_MODULE_NAME || v_api_name,
3994 'Delete table JAI_WSH_EXCEPTIONS_T. Delivery Id : ' || p_delivery_id || 'Exception Entity : ' || p_exp_entity);
3995 END IF;
3996
3997 DELETE FROM JAI_WSH_EXCEPTIONS_T WHERE DELIVERY_ID = p_delivery_id AND EXCEPTION_ENTITY = p_exp_entity ;
3998 COMMIT;
3999
4000 end if;
4001
4002 EXCEPTION
4003 WHEN OTHERS THEN
4004 raise;
4005
4006 END CLEAR_EXP_MSG;
4007
4008 /*
4009 REM +======================================================================+
4010 REM NAME Populate Exception Details
4011 REM
4012 REM DESCRIPTION Called from BE_INTERFACED, INV_INTERFACED
4013 REM
4014 REM NOTES
4015 REM Added by zhiwei.xin for bug#16167577 WSH Exception Handling on 4-JAN-2013
4016 REM +======================================================================+
4017 */
4018 PROCEDURE POPULATE_EXP_MSG (p_delivery_id number,
4019 p_exp_msg exp_tbl_type)
4020 IS
4021
4022 PRAGMA AUTONOMOUS_TRANSACTION;
4023
4024 v_api_name CONSTANT VARCHAR2(30) := 'POPULATE_EXP_MSG';
4025
4026 BEGIN
4027
4028 FOR l_exp_msg_index IN 1 .. NVL(p_exp_msg.LAST,0)
4029 LOOP
4030
4031 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4032 FND_LOG.STRING(G_LEVEL_STATEMENT,
4033 G_MODULE_NAME || v_api_name,
4034 'Populate table JAI_WSH_EXCEPTIONS_T. Delivery Id : ' || p_delivery_id || 'Exception_Entity : ' || p_exp_msg(l_exp_msg_index).EXCEPTION_ENTITY);
4035 END IF;
4036
4037 INSERT INTO JAI_WSH_EXCEPTIONS_T(EXCEPTION_ID,
4038 DELIVERY_ID,
4039 DELIVERY_DETAIL_ID,
4040 EXCEPTION_TYPE,
4041 EXCEPTION_ENTITY,
4042 EXCEPTION_NAME,
4043 ERROR_MESSAGE,
4044 CREATION_DATE,
4045 CREATED_BY,
4046 LAST_UPDATE_DATE,
4047 LAST_UPDATE_LOGIN,
4048 LAST_UPDATED_BY)
4049 VALUES (JAI_WSH_EXCEPTIONS_S.NEXTVAL,
4050 p_delivery_id,
4051 p_exp_msg(l_exp_msg_index).DELIVERY_DETAIL_ID,
4052 p_exp_msg(l_exp_msg_index).EXCEPTION_TYPE,
4053 p_exp_msg(l_exp_msg_index).EXCEPTION_ENTITY,
4054 p_exp_msg(l_exp_msg_index).EXCEPTION_NAME,
4055 p_exp_msg(l_exp_msg_index).ERROR_MESSAGE,
4056 sysdate,
4057 fnd_global.user_id,
4058 sysdate,
4059 fnd_global.login_id,
4060 fnd_global.user_id);
4061 END LOOP;
4062
4063 commit;
4064
4065 EXCEPTION
4066 WHEN OTHERS THEN
4067 rollback;
4068 raise;
4069
4070 END POPULATE_EXP_MSG;
4071
4072 END JAI_OM_WDD_PROCESSING_PKG;