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