1 PACKAGE BODY JAI_PO_HA_TRIGGER_PKG AS
2 /* $Header: jai_po_ha_t.plb 120.6.12020000.2 2013/01/30 10:16:26 amandali ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_PO_HA_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_PO_HA_ARU_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15 CURSOR c_check_osp_po_distrib is
16 select 1
17 from po_lines_all pol
18 where po_header_id = pr_new.po_header_id
19 and exists
20 (select 1
21 from po_line_types_b
22 where line_type_id = pol.line_type_id
23 and outside_operation_flag = 'Y'
24 );
25
26 cursor c_set_of_books is
27 select set_of_books_id
28 from po_distributions_all
29 where po_header_id = pr_new.po_header_id;
30
31 CURSOR c_Sob_Cur(cp_set_of_books_id number) is
32 select Currency_code
33 from gl_sets_of_books
34 where set_of_books_id = cp_set_of_books_id;
35
36 ln_sob_id number;
37 lv_sob_cur gl_sets_of_books.Currency_code%type;
38 ln_osp_flag number;
39 v_sqlerrm varchar2(210);
40
41
42 BEGIN
43 pv_return_code := jai_constants.successful ;
44 /*--------------------------------------------------------------------------------------------------------------------------
45 FILENAME: ja_in_create_57F4.sql
46
47 CHANGE HISTORY:
48 S.No Date Author and Details
49 1 23/05/2003 Nagaraj.s for Bug2728485 Version : 616.1
50
51 This Issue corresponds to the Code Merge
52 The Average Cost of the Item is not previously picked up.
53 The Costs are picked up in the Following Order, means if the First One is not
54 found then the Program tries to Pick up the cost from the second combination.
55
56 1. List_Price from so_price_list_lines for the combination of
57 Item,Vendor,Vendor Site and UOM Code.
58
59 2. List_Price from so_price_list_lines for the combination of
60 Item,Vendor,Vendor Site =0 and UOM Code.
61
62 3. list_price_per_unit from mtl_system_items for Item,Organization combination
63
64 4. Item_Cost from CST_Item_Costs Table for Organization,Item
65
66
67
68 2. 11/08/2003 Sriram - Bug # 3021456 File Version 616.2
69
70 When a Phantom item exists as a part of the Bill of materials ,
71 the phantom item needs to be exploded until its atomic element.
72
73 This is achived by doing the following.
74
75 1) Added a new loop which explodes each of the phantom items
76 This is done by making an API call to the BOM routine.
77 2) Added another loop which inserts the atomic elements in that
78 belong to a phantom item instead of the phantom item itself.
79
80 3) Changed the Quantity to correctly reflect the po Qty * component quantity instead of just the po_qty.
81
82 3. 01/11/2004 ssumaith - bug#3179320 - file version 115.1
83
84 Removed the code to populate the 57F4 details from the trigger and instead calling the procedure
85 jai_po_osp_pkg.ja_in_57F4_process_header. This creates a dependency for future bugs.
86
87
88 4. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.3
89 Check whether india localization is being used was done using a INR check in every trigger.
90 This check has now been moved into a new package and calls made to this package from this trigger
91 If the function jai_cmn_utils_pkg.check_jai_exists returns true it means INR is the set of books currency ,
92 Hence if this function returns FALSE , control should return.
93
94 5. 08-dec-2004 ssumaith - bug# 4037690 - File version 115.4
95
96 comparison in the cursor c_set_of_books was incorrect. This cursor was refering to po_release_id
97 instead of po_header_id. This has been corrected now.
98
99 6. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
100 DB Entity as required for CASE COMPLAINCE. Version 116.1
101
102 7. 13-Jun-2005 File Version: 116.2
103 Ramananda for bug#4428980. Removal of SQL LITERALs is done
104
105 8 07/12/2005 Hjujjuru for the bug 4866533 File version 120.1
106 added the who columns in the insert of JAI_CMN_ERRORS_T
107 Dependencies Due to this bug:-
108 None
109
110 9. 18/12/2012 for bug 16013918 by anupgupt
111 Removed GST changes
112
113 Future Dependencies For the release Of this Object:-
114 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
115 A datamodel change )
116 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
117 Current Version Current Bug Dependent Files Version Author Date Remarks
118 Of File On Bug/Patchset Dependent On
119
120 ja_in_create_57f4_trg.sql
121 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
122 115.2 4037690 IN60105D2 ja_in_util_pkg_s.sql 115.0 ssumaith 29-Nov-2004 Call to this function.
123 ja_in_util_pkg_s.sql 115.0 ssumaith
124
125 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
126
127 open c_set_of_books;
128 fetch c_set_of_books into ln_sob_id;
129 close c_set_of_books;
130
131 --If jai_cmn_utils_pkg.check_jai_exists(P_CALLING_OBJECT => 'JA_IN_CREATE_57F4', P_SET_OF_BOOKS_ID => ln_sob_id) = false then
132 -- return;
133 --end if;
134
135 /*The following code has been commented and added the above code instead - ssumaith - bug# 4037690*/
136
137 /*
138 open c_Sob_Cur(ln_sob_id);
139 fetch c_Sob_Cur into lv_sob_cur;
140 close c_Sob_Cur;
141
142 if lv_sob_cur <> 'INR' then
143 return;
144 end if;
145 */
146 open c_check_osp_po_distrib;
147 fetch c_check_osp_po_distrib into ln_osp_flag;
148 close c_check_osp_po_distrib;
149
150 if nvl(ln_osp_flag, -1) = 1 then
151
152 jai_po_osp_pkg.ja_in_57F4_process_header
153 (
154 pr_new.po_header_id ,
155 NULL, /* release id */
156 pr_new.vendor_id,
157 pr_new.vendor_site_id,
158 'PO'
159 );
160 end if;
161
162 exception
163 when others then
164 v_sqlerrm := substr(sqlerrm,1,200);
165 insert into JAI_CMN_ERRORS_T
166 ( APPLICATION_SOURCE ,
167 ERROR_MESSAGE ,
168 ADDITIONAL_ERROR_MESG ,
169 CREATION_DATE ,
170 CREATED_BY ,
171 -- added, Harshita for Bug 4866533
172 LAST_UPDATED_BY,
173 LAST_UPDATE_DATE
174 )
175 values
176 ( 'ja_in_create_57F4',
177 'error occured' ,
178 v_sqlerrm ,
179 sysdate ,
180 fnd_global.user_id,
181 -- added, Harshita for Bug 4866533
182 fnd_global.user_id,
183 sysdate
184 );
185 END ARU_T1 ;
186
187 /*
188 REM +======================================================================+
189 REM NAME ARU_T2
190 REM
191 REM DESCRIPTION Called from trigger JAI_PO_HA_ARIUD_T1
192 REM
193 REM NOTES Refers to old trigger JAI_PO_HA_ARU_T3
194 REM
195 REM +======================================================================+
196 */
197 PROCEDURE ARU_T2 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
198 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
199 v_curr VARCHAR2(3); --File.Sql.35 Cbabu := pr_new.Currency_Code;
200 v_old_curr VARCHAR2(3); --File.Sql.35 Cbabu := pr_old.Currency_Code;
201 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
202 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
203 v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
204
205 BEGIN
206 pv_return_code := jai_constants.successful ;
207 /*------------------------------------------------------------------------------------------
208 FILENAME: Ja_In_Po_Hdr_Curr_Upd_Trg.sql
209
210 CHANGE HISTORY:
211 S.No Date Author and Details
212 1. 29/Nov/2004 Aiyer for bug#4035566. Version#115.1
213 Issue:-
214 The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
215 where transaction is being done.
216
217 Fix:-
218 Function jai_cmn_utils_pkg.check_jai_exists is being called which returns the TRUE if the currency is INR and FALSE if the currency is
219 NON-INR
220 Removed the cursors Fetch_Book_Id_Cur and Sob_Cur and the variables v_operating_id and v_gl_set_of_bks_id
221
222 Dependency Due to this Bug:-
223 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0 introduced through the bug 4033992
224
225 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
226 DB Entity as required for CASE COMPLAINCE. Version 116.1
227
228 3. 13-Jun-2005 File Version: 116.2
229 Ramananda for bug#4428980. Removal of SQL LITERALs is done
230
231 Future Dependencies For the release Of this Object:-
232 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
233 A datamodel change )
234 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
235 Current Version Current Bug Dependent Files Version Author Date Remarks
236 Of File On Bug/Patchset Dependent On
237 ja_in_po_hdr_curr_upd_trg
238 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
239 115.1 4035566 IN60105D2 + 4033992 ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
240 ja_in_util_pkg_b.sql 115.0
241
242 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
243
244 v_po_hdr_id := pr_new.Po_Header_Id;
245 v_curr := pr_new.Currency_Code;
246 v_old_curr := pr_old.Currency_Code;
247 v_last_upd_dt := pr_new.Last_Update_Date ;
248 v_last_upd_by := pr_new.Last_Updated_By;
249 v_last_upd_login := pr_new.Last_Update_Login;
250 /*
251 || Code added by aiyer for the bug 4035566
252 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
253 */
254
255 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_PO_HDR_CURR_UPD_TRG' ,
256 -- p_org_id => pr_new.org_id
257 -- ) = FALSE
258 --THEN
259 /*
260 || return as the current set of books is NON-INR based
261 */
262 -- RETURN;
263 -- END IF;
264
265
266
267 UPDATE JAI_PO_TAXES
268 SET Currency = v_curr,
269 Last_Update_Date = v_last_upd_dt,
270 Last_Updated_By = v_last_upd_by,
271 Last_Update_Login = v_last_upd_login
272 WHERE Po_Header_Id = v_po_hdr_id
273 AND Currency = v_old_curr;
274
275 END ARU_T2 ;
276
277 /*
278 REM +======================================================================+
279 REM NAME ARU_T3
280 REM
281 REM DESCRIPTION Called from trigger JAI_PO_HA_ARIUD_T1
282 REM
283 REM NOTES Refers to old trigger JAI_PO_HA_ARU_T4
284 REM
285 REM +======================================================================+
286 */
287 PROCEDURE ARU_T3 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
288 v_vendor_id NUMBER; --File.Sql.35 Cbabu := NVL( pr_new.Vendor_Id, 0 );
289 v_vendor_site_id NUMBER; --File.Sql.35 Cbabu := NVL( pr_new.Vendor_Site_Id, 0 );
290 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
291 v_type_lookup_code VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.Type_Lookup_Code;
292 v_quot_class_code VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.Quotation_Class_Code;
293 v_ship_loc_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Ship_To_Location_Id;
294 v_org_id NUMBER;
295 v_po_org_id NUMBER ; --File.Sql.35 Cbabu := NVL( pr_new.Org_Id, -999 );
296 v_rate NUMBER; --File.Sql.35 Cbabu := pr_new.Rate;
297 v_rate_type VARCHAR2(100); --File.Sql.35 Cbabu := pr_new.Rate_Type;
298 v_rate_date DATE ; --File.Sql.35 Cbabu := pr_new.Rate_Date;
299 v_ship_to_loc_id NUMBER; --File.Sql.35 Cbabu := pr_new.Ship_To_Location_Id;
300
301
302 v_next_val NUMBER;
303 line_loc_flag BOOLEAN;
304
305 v_assessable_value NUMBER;
306 ln_vat_assess_value NUMBER; -- added, Harshita for bug #4245062
307 /* for bug 16013918 by anupgupt
308 ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10043656 on 2010/09/10
309 */
310 v_func_curr VARCHAR2(15);
311 v_curr VARCHAR2(100); --File.Sql.35 Cbabu := pr_new.Currency_Code;
312 v_conv_rate NUMBER;
313
314 flag VARCHAR2(10);
315
316 v_line_cnt NUMBER;
317 v_tax_flag VARCHAR2(1);
318 v_old_vendor_id NUMBER;
319 v_item_id NUMBER;
320 v_qty NUMBER;
321 v_price NUMBER;
322 v_uom VARCHAR2(25);
323 v_line_uom VARCHAR2(25);
324
325 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
326 v_cre_by NUMBER; --File.Sql.35 Cbabu := pr_new.Created_By;
327 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
328 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
329 v_last_upd_login NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
330
331 v_service_Type_code VARCHAR2(30); /* ssumaith - bug# 6109941 */
332
333 CURSOR Fetch_Org_Id_Cur IS
334 SELECT Inventory_Organization_id
335 FROM Hr_Locations
336 WHERE Location_Id = v_ship_loc_id;
337
338 -- Get the Line Focus Id from the Sequence
339
340 CURSOR Fetch_Focus_Id IS
341 SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
342 FROM Dual;
343
344 CURSOR Chk_Line_Count IS
345 SELECT NVL( COUNT( Po_Line_Id ), 0 )
346 FROM JAI_PO_LINE_LOCATIONS
347 WHERE Po_Header_Id = v_po_hdr_id;
348
349
350 CURSOR Lines_Cur IS
351 SELECT DISTINCT Po_Line_Id
352 FROM JAI_PO_LINE_LOCATIONS
353 WHERE Po_Header_Id = v_po_hdr_id;
354
355 CURSOR Fetch_Item_Cur( Lineid IN NUMBER ) IS
356 SELECT Item_Id
357 FROM Po_Lines_All
358 WHERE Po_Line_Id = Lineid;
359
360 CURSOR Line_Loc_Cur( lineid IN NUMBER ) IS
361 SELECT Line_Location_Id
362 FROM JAI_PO_LINE_LOCATIONS
363 WHERE Po_Line_Id = lineid;
364
365 CURSOR Fetch_Dtls_Cur( lineid IN NUMBER ) IS
366 SELECT Quantity, Unit_Price, Unit_Meas_Lookup_Code
367 FROM Po_Lines_All
368 WHERE Po_Line_Id = lineid;
369
370 CURSOR Fetch_Dtls1_Cur( lineid IN NUMBER, linelocid IN NUMBER ) IS
371 SELECT Quantity, Price_Override, Unit_Meas_Lookup_Code
372 FROM Po_Line_Locations_All
373 WHERE Po_Line_Id = lineid
374 AND Line_Location_Id = linelocid;
375
376
377 CURSOR Fetch_UOMCode_Cur IS
378 SELECT Uom_Code
379 FROM Mtl_Units_Of_Measure
380 WHERE Unit_Of_Measure = v_uom;
381
382 CURSOR Tax_Flag1_Cur( lineid IN NUMBER ) IS
383 SELECT NVL( Tax_Modified_Flag, 'N' )
384 FROM JAI_PO_LINE_LOCATIONS
385 WHERE Po_Line_Id = lineid
386 AND Line_Location_Id IS NULL;
387
388
389 CURSOR Tax_Flag_Cur( lineid IN NUMBER, linelocid IN NUMBER ) IS
390 SELECT NVL( Tax_Modified_Flag, 'N' )
391 FROM JAI_PO_LINE_LOCATIONS
392 WHERE Po_Line_Id = lineid
393 AND Line_Location_Id = linelocid;
394
395
396 CURSOR Chk_Vendor( lineid IN NUMBER, linelocid IN NUMBER ) IS
397 SELECT Vendor_Id
398 FROM JAI_PO_TAXES
399 WHERE Po_Line_Id = lineid
400 AND Line_Location_Id = linelocid;
401
402 CURSOR Chk_Vendor1( lineid IN NUMBER ) IS
403 SELECT Vendor_Id
404 FROM JAI_PO_TAXES
405 WHERE Po_Line_Id = lineid
406 AND Line_Location_Id IS NULL;
407
408 --This code is added to check value of Tax Override Flag for the Supplier,Supplier_site_id
409 --by RK and GSR on 19-Apr-2001
410
411 CURSOR tax_override_flag_cur(c_supplier_id number, c_supp_site_id number) IS
412 SELECT override_flag
413 FROM JAI_CMN_VENDOR_SITES
414 WHERE vendor_id = c_supplier_id
415 AND vendor_site_id = c_supp_site_id;
416
417 v_override_flag varchar2(1);
418
419 BEGIN
420 pv_return_code := jai_constants.successful ;
421 /*--------------------------------------------------------------------------------------------------------------
422 FILENAME: Ja_In_Po_Hdr_Vendor_Upd_Trg.sql
423
424 CHANGE HISTORY:
425 SL.No Date Author and Details
426 1 19-Apr-2001 RK and GSR. Version#614.1
427 Code has been added to Check for override_flag for vendor and Vendor_site_id.
428
429 2. 08-mar-2004 Aparajita. Bug#3030483. Version#619.1
430 Vendor id gets updated for a PO for supplier merge scenario. Data in receipt tax table
431 JAI_RCV_LINE_TAXES still used to hold the old vendor. Added code on top to update
432 this vendor id as there are multiple return statements in the code.
433
434 There is no need to update the vendor site as it is only for third party tax.
435
436 3. 29/Nov/2004 Aiyer for bug#4035566. Version#115.1
437 Issue:-
438 The trigger should not get fires when the non-INR based set of books is attached to the current operating unit
439 where transaction is being done.
440
441 Fix:-
442 Function jai_cmn_utils_pkg.check_jai_exists is being called which returns the TRUE if the currency is INR and FALSE if the currency is
443 NON-INR
444
445 Dependency Due to this Bug:-
446 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0. introduced through the bug 4033992
447
448 4. 23/Jan/2005 brathod for bug#4030192 Version#115.2
449 Issue:- Trigger is not updating the vendor_id field in JAI_PO_TAXES
450 when a PO having no vendor is updated with the new vendor.
451
452 Fix :- Modified condition that checks wether pr_old.vendor_id is equal to vendor_id in
453 JAI_PO_TAXES. The condition was evaluating to false because
454 comparision like "Null = Null".
455
456 5. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.3
457 The Assessable Value is calculated for the transaction. For this, a call is
458 made to the function ja_in_vat_assessable_value_f.sql with the parameters
459 relevant for the transaction. This assessable value is again passed to the
460 procedure that calucates the taxes.
461
462 Base bug - #4245089
463
464 Dependency Due to this Bug:-
465 None
466 6. 31-Mar-2005 Brathod, Bug#4242351, File Version 115.5
467 Issue :- Procedure jai_po_tax_pkg.copy_reqn_taxes is modified for mutating error and new
468 arguments are added in the procedure signature that must be passed from
469 current trigger. Call to jai_po_tax_pkg.copy_reqn_taxes procedure in the current
470 trigger needs to be modified.
471
472 Fix:- call to jai_po_tax_pkg.copy_reqn_taxes is modified by passing the required
473 new arguments.
474
475 7. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
476 DB Entity as required for CASE COMPLAINCE. Version 116.1
477
478 8. 13-Jun-2005 File Version: 116.2
479 Ramananda for bug#4428980. Removal of SQL LITERALs is done
480
481 9. 08-Jul-2005 Sanjikum for Bug#4483042
482 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
483 IL functionality should work or not.
484
485 10. 12-Jul-2005 Sanjikum for Bug#4483042
486 1) Changed the parameter being passed to jai_cmn_utils_pkg.validate_po_type
487
488 11. 10-Sep-2010 Jia for GST Bug#10091373
489
490 12. 06-06-2012 amandali for bug 14085523
491 Description:AFTER SUPPLIER MERGE OLD SUPPLIER IS DISPLAYED IN 'RECEIPT LOCALIZATION' WINDOW
492 Fix: In ARU_T3, added an update statement to jai_rcv_headers to update the vendor to the new vendor
493
494 Future Dependencies For the release Of this Object:-
495 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
496 A datamodel change )
497 ----------------------------------------------------------------------------------------------------------------------------------------------------------
498 Current Version Current Bug Dependent Files Version Author Date Remarks
499 Of File On Bug/Patchset Dependent On
500 ja_in_po_hdr_vendor_upd_trg
501 ----------------------------------------------------------------------------------------------------------------------------------------------------
502 115.1 4035566 IN60105D2 +
503 4033992 ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
504 ja_in_util_pkg_b.sql 115.0
505
506 115.2 4245062 IN60106 + 4245089 hjujjuru 17/03/2005 VAT Implelentation
507 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
508
509
510 --File.Sql.35 Cbabu
511 v_vendor_id := NVL( pr_new.Vendor_Id, 0 );
512 v_vendor_site_id := NVL( pr_new.Vendor_Site_Id, 0 );
513 v_po_hdr_id := pr_new.Po_Header_Id;
514 v_type_lookup_code := pr_new.Type_Lookup_Code;
515 v_quot_class_code := pr_new.Quotation_Class_Code;
516 v_ship_loc_id := pr_new.Ship_To_Location_Id;
517 v_po_org_id := NVL( pr_new.Org_Id, -999 );
518 v_rate := pr_new.Rate;
519 v_rate_type := pr_new.Rate_Type;
520 v_rate_date := pr_new.Rate_Date;
521 v_ship_to_loc_id := pr_new.Ship_To_Location_Id;
522 v_curr := pr_new.Currency_Code;
523 v_cre_dt := pr_new.Creation_Date;
524 v_cre_by := pr_new.Created_By;
525 v_last_upd_dt := pr_new.Last_Update_Date ;
526 v_last_upd_by := pr_new.Last_Updated_By;
527 v_last_upd_login := pr_new.Last_Update_Login;
528
529
530 /*
531 || Code added by aiyer for the bug 4035566
532 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
533 */
534 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_PO_HDR_VENDOR_UPD_TRG' ,
535 -- p_org_id => pr_new.org_id
536 -- ) = FALSE
537 --THEN
538 /*
539 || return as the current set of books is NON-INR based
540 */
541 -- RETURN;
542 -- END IF;
543
544 --code added by Sanjikum for Bug#4483042
545 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => pr_new.style_id) = FALSE THEN
546 return;
547 END IF;
548
549 -- Start added for bug#3030483
550 IF pr_new.vendor_id <> pr_old.vendor_id then
551 for c_receipt_rec in
552 (
553 select distinct shipment_header_id, shipment_line_id
554 from rcv_transactions
555 where po_header_id = pr_new.po_header_id
556 )
557 loop
558 update JAI_RCV_LINE_TAXES
559 set vendor_id = pr_new.vendor_id
560 where shipment_header_id = c_receipt_rec.shipment_header_id
561 and shipment_line_id = c_receipt_rec.shipment_line_id
562 and vendor_id = pr_old.vendor_id;
563 /* start -14085523 */
564 update jai_rcv_headers
565 set vendor_id = pr_new.vendor_id,
566 vendor_site_id = decode(vendor_site_id,pr_old.vendor_site_id, pr_new.vendor_site_id)
567 where shipment_header_id = c_receipt_rec.shipment_header_id
568 and vendor_id = pr_old.vendor_id;
569 /* end -14085523 */
570 end loop;
571 END IF;
572 -- End added for bug#3030483
573
574 --This code is added to check value of Tax Override Flag for the Supplier,Supplier_site_id
575 --by RK and GSR on 19-Apr-2001
576 OPEN tax_override_flag_cur(v_vendor_id, v_vendor_site_id);
577 FETCH tax_override_flag_cur into v_override_flag;
578 CLOSE tax_override_flag_cur;
579
580 OPEN Chk_Line_Count;
581 FETCH Chk_Line_Count INTO v_line_cnt;
582 CLOSE Chk_Line_Count;
583
584 -- Get the Inventory Organization Id
585
586 OPEN Fetch_Org_Id_Cur;
587 FETCH Fetch_Org_Id_Cur INTO v_org_id;
588 CLOSE fetch_Org_Id_Cur;
589
590 FOR Rec IN Lines_Cur LOOP
591
592 FOR Rec1 IN Line_Loc_Cur( Rec.Po_Line_Id ) LOOP
593
594 IF NVL( Rec1.Line_Location_Id, -999 ) = -999 THEN
595
596 /** Added by Sriram **/
597
598 IF nvl(v_override_flag,'N') = 'Y' THEN
599
600 DELETE FROM JAI_PO_TAXES
601 WHERE po_header_id = v_po_hdr_id;
602
603 jai_po_tax_pkg.copy_reqn_taxes
604 (
605 v_Vendor_Id ,
606 v_Vendor_Site_Id,
607 v_Po_Hdr_Id ,
608 Rec.Po_Line_Id, --added by Sriram on 22-Nov-2001
609 Rec1.Line_Location_Id, --added by Sriram on 22-Nov-2001
610 v_Type_Lookup_Code ,
611 v_Quot_Class_Code ,
612 v_Ship_To_Loc_Id ,
613 v_Org_Id ,
614 v_Cre_Dt ,
615 v_Cre_By ,
616 v_Last_Upd_Dt ,
617 v_Last_Upd_By ,
618 v_Last_Upd_Login
619 /* Added by brathod, For Bug#4242351 */
620 ,v_rate
621 ,v_rate_type
622 ,v_rate_date
623 ,v_curr
624 /* End of Bug#4242351 */
625 );
626 RETURN;
627
628 END IF;
629
630 /** End Addition **/
631
632
633 OPEN Tax_Flag1_Cur( Rec.Po_Line_Id );
634 FETCH Tax_Flag1_Cur INTO v_tax_flag;
635 CLOSE Tax_Flag1_Cur;
636
637 OPEN Fetch_Dtls_Cur( Rec.Po_Line_Id );
638 FETCH Fetch_Dtls_Cur INTO v_qty, v_price, v_uom;
639 CLOSE Fetch_Dtls_Cur;
640
641 v_line_uom := v_uom;
642 line_loc_flag := FALSE;
643
644 ELSE
645
646 OPEN Tax_Flag_Cur( Rec.Po_Line_Id, Rec1.Line_Location_Id );
647 FETCH Tax_Flag_Cur INTO v_tax_flag;
648 CLOSE Tax_Flag_Cur;
649
650 OPEN Fetch_Dtls1_Cur( Rec.Po_Line_Id, Rec1.Line_Location_Id );
651 FETCH Fetch_Dtls1_Cur INTO v_qty, v_price, v_uom;
652 CLOSE Fetch_Dtls1_Cur;
653
654 IF v_uom IS NULL THEN
655 FOR uom_rec IN Fetch_Dtls_Cur( Rec.Po_Line_Id ) LOOP
656 v_uom := uom_rec.unit_meas_lookup_code;
657 END LOOP;
658 END IF;
659
660 line_loc_flag := TRUE;
661
662 END IF;
663
664 --v_uom := NVL( v_uom, v_line_uom );
665 OPEN Fetch_UOMCode_Cur;
666 FETCH Fetch_UOMCode_Cur INTO v_uom;
667 CLOSE Fetch_UOMCode_Cur;
668
669 OPEN Fetch_Item_Cur( Rec.Po_Line_Id );
670 FETCH Fetch_Item_Cur INTO v_item_id;
671 CLOSE Fetch_Item_Cur;
672
673 v_assessable_value :=
674 jai_cmn_setup_pkg.get_po_assessable_value
675 (
676 v_vendor_id, v_vendor_site_id,
677 v_item_id, v_uom
678 );
679
680 v_conv_rate := v_rate;
681
682 jai_po_cmn_pkg.get_functional_curr
683 (
684 v_ship_to_loc_id,
685 v_po_org_id, v_org_id,
686 v_curr, v_assessable_value,
687 v_conv_rate,
688 v_rate_type,
689 v_rate_date,
690 v_func_curr
691 );
692
693
694 IF NVL( v_assessable_value, 0 ) <= 0 THEN
695 v_assessable_value := v_price * v_qty;
696 ELSE
697 v_assessable_value := v_assessable_value * v_qty;
698 END IF;
699
700 -- added, Harshita for bug #4245062
701
702 ln_vat_assess_value :=
703 jai_general_pkg.ja_in_vat_assessable_value
704 ( p_party_id => v_vendor_id,
705 p_party_site_id => v_vendor_site_id,
706 p_inventory_item_id => v_item_id,
707 p_uom_code => v_uom,
708 p_default_price => v_price,
709 p_ass_value_date => trunc(SYSDATE),
710 p_party_type => 'V'
711 ) ;
712 v_conv_rate := v_rate;
713
714 jai_po_cmn_pkg.get_functional_curr
715 (
716 v_ship_to_loc_id,
717 v_po_org_id, v_org_id,
718 v_curr, ln_vat_assess_value,
719 v_conv_rate,
720 v_rate_type,
721 v_rate_date,
722 v_func_curr
723 );
724
725 ln_vat_assess_value := ln_vat_assess_value * v_qty;
726 --ended, Harshita for bug #4245062
727
728 -- Added by Jia for GST Bug#10091373 on 2010/09/10, Begin
729 ---------------------------------------------------------------
730 /* for bug 16013918 by anupgupt
731 ln_gst_assessable_value :=
732 jai_gst_general_pkg.get_gst_assessable_value
733 ( p_party_id => v_vendor_id,
734 p_party_site_id => v_vendor_site_id,
735 p_inventory_item_id => v_item_id,
736 p_uom_code => v_uom,
737 p_default_price => v_price,
738 p_ass_value_date => trunc(SYSDATE),
739 p_party_type => 'V'
740 ) ;
741 v_conv_rate := v_rate;
742
743 jai_po_cmn_pkg.get_functional_curr
744 (
745 v_ship_to_loc_id,
746 v_po_org_id, v_org_id,
747 v_curr, ln_gst_assessable_value,
748 v_conv_rate,
749 v_rate_type,
750 v_rate_date,
751 v_func_curr
752 );
753
754 ln_gst_assessable_value := ln_gst_assessable_value * v_qty;
755 */
756 ---------------------------------------------------------------
757 -- Added by Jia for GST Bug#10091373 on 2010/09/10, end
758
759 IF v_tax_flag = 'N' THEN
760
761 DELETE FROM JAI_PO_TAXES
762 WHERE Po_Line_Id = Rec.Po_Line_Id
763 AND NVL( Line_Location_Id, -999 ) = NVL( Rec1.Line_Location_Id, -999 );
764
765
766 DELETE FROM JAI_PO_LINE_LOCATIONS
767 WHERE Po_Line_Id = Rec.Po_Line_Id
768 AND NVL( Line_Location_Id, -999 ) = NVL( Rec1.Line_Location_Id, -999 );
769
770 OPEN Fetch_Focus_Id;
771 FETCH Fetch_Focus_Id INTO v_next_val;
772 CLOSE Fetch_Focus_Id;
773
774 v_service_type_code := jai_ar_rctla_trigger_pkg.get_service_Type(v_vendor_id,v_vendor_site_id , 'V');
775 /*above code added by ssumaith - bug# 6109941 */
776
777 INSERT INTO JAI_PO_LINE_LOCATIONS
778 (
779 Line_Focus_Id,
780 Line_Location_Id,
781 Po_Line_Id,
782 Po_Header_Id,
783 Tax_Modified_Flag,
784 Tax_Amount,
785 Total_Amount,
786 Creation_Date,
787 Created_By,
788 Last_Update_Date,
789 Last_Updated_By,
790 Last_Update_Login,
791 service_type_code /* ssumaith - bug# 6109941*/
792 )
793 VALUES
794 (
795 v_next_val,
796 Rec1.Line_Location_Id,
797 Rec.po_line_id,
798 v_po_hdr_id,
799 'N',
800 0,
801 0,
802 v_cre_dt,
803 v_cre_by,
804 v_last_upd_dt,
805 v_last_upd_by,
806 v_last_upd_login,
807 v_service_type_code /* added by ssumaith - bug# 6109941 */
808 );
809
810 IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN
811 --Addition by Ramakrishna on 15/12/2000 to check taxes defaulting
812 if Rec1.line_location_id is null then
813 flag := 'INSLINES';
814 else
815 flag := 'I';
816 end if;
817
818 --end of addition by Ramakrishna
819 ELSE
820 flag := 'I';
821 END IF;
822
823 jai_po_tax_pkg.Ja_In_Po_Case2
824 (
825 v_Type_Lookup_Code,
826 v_Quot_Class_Code,
827 pr_new.Vendor_Id,
828 pr_new.Vendor_Site_Id,
829 pr_new.Currency_Code,
830 v_org_id,
831 v_Item_Id,
832 Rec1.Line_Location_Id,
833 v_po_hdr_id,
834 Rec.Po_Line_Id,
835 v_price,
836 v_qty,
837 v_cre_dt,
838 v_cre_by,
839 v_last_upd_dt,
840 v_last_upd_by,
841 v_last_upd_login,
842 v_uom,
843 flag,
844 NVL( v_assessable_value, -9999 ),
845 ln_vat_assess_value, -- added, Harshita for bug #4245062
846 NVL( v_conv_rate, 1 ),
847 /* Bug 5096787. Added by Lakshmi Gopalsami */
848 v_rate,
849 v_rate_date,
850 v_rate_type -- Bug 8319569. Changed the order of date and type
851 /* for bug 16013918 by anupgupt
852 , pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10091373 on 2010/09/10
853 */
854 );
855
856 ELSE
857
858 IF line_loc_flag THEN
859 OPEN Chk_Vendor( Rec.Po_Line_Id, Rec1.Line_Location_Id );
860 FETCH Chk_Vendor INTO v_old_vendor_id;
861 CLOSE Chk_Vendor;
862
863 jai_po_tax_pkg.calculate_tax
864 (
865 'STANDARDPO',
866 v_po_hdr_id ,
867 Rec.Po_Line_Id,
868 Rec1.line_location_id,
869 v_qty, v_price*v_qty, v_uom, v_assessable_value,
870 NVL( v_assessable_value, v_price*v_qty ),
871 ln_vat_assess_value, -- added, Harshita for bug #4245062
872 NULL,
873 v_conv_rate
874 /* for bug 16013918 by anupgupt
875 , pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10091373 on 2010/09/10
876 */
877 );
878
879 ELSE
880
881 OPEN Chk_Vendor1( Rec.Po_Line_Id );
882 FETCH Chk_Vendor1 INTO v_old_vendor_id;
883 CLOSE Chk_Vendor1;
884
885 END IF;
886
887
888 -- Before Modification for BUG#4030192
889 -- IF pr_old.Vendor_Id = NVL( v_old_vendor_id, pr_old.Vendor_Id ) THEN
890
891 -- Modified by Bhavik for BUG#4030192
892 IF NVL(pr_old.Vendor_Id,0) = NVL( v_old_vendor_id, NVL(pr_old.Vendor_Id,0) ) THEN
893
894 UPDATE JAI_PO_TAXES
895 SET Vendor_Id = v_vendor_id
896 WHERE Vendor_id = nvl(pr_old.vendor_id,0) /*Added by nprashar for bug 8349329*/
897 AND Po_Line_Id = Rec.Po_Line_Id
898 AND nvl(Line_Location_Id,-999) = nvl(Rec1.Line_Location_Id,-999); /*Added by nprashar for bug 8349329*/
899
900 END IF;
901
902 END IF;
903
904 END LOOP;
905
906 END LOOP;
907 END ARU_T3 ;
908
909 END JAI_PO_HA_TRIGGER_PKG ;
[Home] [Help]