[Home] [Help]
PACKAGE BODY: APPS.JAI_PO_LLA_TRIGGER_PKG
Source
1 PACKAGE BODY JAI_PO_LLA_TRIGGER_PKG AS
2 /* $Header: jai_po_lla_t.plb 120.13.12020000.2 2012/12/18 11:47:36 anupgupt ship $ */
3
4
5 /********************************************************************************
6
7 1. Ramananada for bug#4703617. File Version 120.1
8 To get the currency code from GL_SETS_OF_BOOKS table,
9 ORG_ORGANIZATION_DEFINITIONS table is referred to get the Set_Of_Books_Id.
10 But using this table has a performance impact. Hence using the table
11 FINANCIALS_SYSTEM_PARAMS_ALL along with GL_SETS_OF_BOOKS talbe to get the
12 currency code.
13 Removed the references of ORG_ORGANIZATION_DEFINITIONS and instead used
14 FINANCIALS_SYSTEM_PARAMS_ALL table
15
16 ********************************************************************************/
17
18 /*
19 Bug 8586635 - Added the procedure to get the quantity from the parent Shipment
20 line to split the taxes in the child lines
21 Used Autonomous transaction as po_line_locations_all needs to be queried
22 */
23 PROCEDURE ja_in_po_get_lineloc_p
24 (
25 p_line_loc_id IN NUMBER,
26 p_prev_quantity OUT NOCOPY NUMBER
27 ) IS
28 PRAGMA AUTONOMOUS_TRANSACTION;
29 BEGIN
30 select quantity into p_prev_quantity
31 from po_line_locations_all
32 where line_location_id = p_line_loc_id;
33 END;
34
35 /*
36 REM +======================================================================+
37 REM NAME ARD_T1
38 REM
39 REM DESCRIPTION Called from trigger JAI_PO_LLA_ARIUD_T1
40 REM
41 REM NOTES Refers to old trigger JAI_PO_LLA_ARD_T1
42 REM
43 REM +======================================================================+
44 */
45 PROCEDURE ARD_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
46 v_po_line_loc_id NUMBER;--File.Sql.35 Cbabu := pr_old.Line_Location_Id;
47
48 ------------- added by Gsr 12-jul-01
49 v_operating_id number;--File.Sql.35 Cbabu :=pr_new.ORG_ID;
50 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
51 v_currency_code gl_sets_of_books.currency_code%type;
52
53 /*
54 || Commented the following two cursors as they are not used in the trigger
55 || Ramananada for bug#4703617
56 */
57 /*
58 CURSOR Fetch_Book_Id_Cur IS SELECT Set_Of_Books_Id
59 FROM Org_Organization_Definitions
60 WHERE Operating_unit = v_operating_id; -- Modified by Ramananda for removal of SQL LITERALs
61 --WHERE NVL(Operating_unit,0) = v_operating_id;
62 CURSOR Sob_Cur is
63 select Currency_code
64 from gl_sets_of_books
65 where set_of_books_id = v_gl_set_of_bks_id; */
66
67 ------ End of addition by Gsri on 12-jul-01
68
69 BEGIN
70 pv_return_code := jai_constants.successful ;
71 /*------------------------------------------------------------------------------------------
72 FILENAME: Ja_In_Po_Tax_Delete_Trg.sql
73
74 CHANGE HISTORY:
75 S.No Date Author and Details
76
77 1 29-Nov-2004 Sanjikum for 4035297. Version 115.1
78 Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
79
80 2 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
81 DB as required for CASE COMPLAINCE. Version 116.1
82
83 3. 13-Jun-2005 File Version: 116.2
84 Ramananda for bug#4428980. Removal of SQL LITERALs is done
85
86 Dependency Due to this Bug:-
87 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
88
89
90 Future Dependencies For the release Of this Object:-
91 (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/
92 A datamodel change )
93 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
94 Current Version Current Bug Dependent Files Version Author Date Remarks
95 Of File On Bug/Patchset Dependent On
96
97 ja_in_po_tax_delete_trg.sql
98 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
99 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
100 ja_in_util_pkg_s.sql 115.0 Sanjikum
101
102 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
103
104 --File.Sql.35 Cbabu
105 v_po_line_loc_id := pr_old.Line_Location_Id;
106 v_operating_id :=pr_new.ORG_ID;
107
108 /*
109 || Commented by Ramananda for bug4703617
110 || Reason:
111 || v_gl_set_of_bks_id is not used in the trigger
112 OPEN Fetch_Book_Id_Cur ;
113 FETCH Fetch_Book_Id_Cur INTO v_gl_set_of_bks_id;
114 CLOSE Fetch_Book_Id_Cur;
115 */
116
117
118 --IF jai_cmn_utils_pkg.check_jai_exists(p_calling_object => 'JA_IN_PO_TAX_DELETE_TRG' ,
119 -- p_set_of_books_id => v_gl_set_of_bks_id) = FALSE THEN
120 -- RETURN;
121 -- END IF;
122
123 DELETE FROM JAI_PO_LINE_LOCATIONS
124 WHERE Line_Location_Id = v_po_line_loc_id;
125
126 DELETE FROM JAI_PO_TAXES
127 WHERE Line_Location_Id = v_po_line_loc_id;
128 END ARD_T1 ;
129
130 /*
131 REM +======================================================================+
132 REM NAME ARI_T1
133 REM
134 REM DESCRIPTION Called from trigger JAI_PO_LLA_ARIUD_T1
135 REM
136 REM NOTES Refers to old trigger JAI_PO_LLA_ARI_T2
137 REM
138 REM +======================================================================+
139 */
140 PROCEDURE ARI_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
141 v_org_id NUMBER;
142 v_type_lookup_code VARCHAR2(10);
143 v_quot_class_code VARCHAR2(25);
144 v_vendor_id NUMBER;
145 v_vendor_site_id NUMBER;
146 v_curr VARCHAR2(15);
147 v_ship_loc_id NUMBER;
148 x NUMBER;
149 y NUMBER;
150 v_shipment_type VARCHAR2(25); --File.Sql.35 Cbabu := pr_new.Shipment_Type;
151 v_shipment_num NUMBER; --File.Sql.35 Cbabu := pr_new.Shipment_Num; -- added on 13th oct subbu
152 v_po_rel_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Po_Release_Id;
153 v_src_ship_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Source_Shipment_Id;
154 v_from_line_loc_id NUMBER; --File.Sql.35 Cbabu := pr_new.From_Line_Location_Id;
155 v_from_line_id NUMBER ; --File.Sql.35 Cbabu := pr_new.From_Line_Id;
156 v_from_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.From_Header_Id;
157 v_quot_line_loc_id NUMBER;
158 v_line_loc_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Line_Location_Id;
159 v_po_line_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Po_Line_Id ;
160 v_po_hdr_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
161 v_q_hdr_id NUMBER;
162 v_q_line_id NUMBER;
163 v_q_line_num NUMBER;
164 v_line_focus_id NUMBER;
165 v_price NUMBER ; --File.Sql.35 Cbabu := pr_new.Price_Override;
166 v_assessable_value NUMBER;
167 ln_vat_assess_value NUMBER; /* rallamse bug#4250072 VAT */
168 v_qty NUMBER ; --File.Sql.35 Cbabu := pr_new.Quantity;
169 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
170 v_cre_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Created_By;
171 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
172 v_last_upd_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
173 v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
174 v_uom_measure VARCHAR2(25); --File.Sql.35 Cbabu := pr_new.Unit_Meas_Lookup_Code;
175 v_uom_code VARCHAR2(25);
176 v_unit_code VARCHAR2(25);
177 v_tax_amt NUMBER;
178 success NUMBER ; --File.Sql.35 Cbabu := 1;
179
180 /* Commented by rallamse bug#4479131 PADDR Elimination
181 rel_found BOOLEAN;
182 --File.Sql.35 Cbabu := FALSE;
183 */
184 -- found BOOLEAN;
185 dummy NUMBER;
186 v_item_id NUMBER;
187 v_temp_uom VARCHAR2(25);
188 result BOOLEAN;
189 req_id NUMBER;
190 v_from_type_lookup_code VARCHAR2(25); -- addition by subbu 11-oct-2000
191 v_line_num NUMBER; -- added on 13th oct
192 v_quot_from_hdr_id NUMBER; -- Added by subbu on 15-OCT-2000
193 v_count NUMBER; -- Added by subbu on 15-OCT-2000
194 v_type VARCHAR2(1) ; -- Added by subbu on 16th-oct-00
195 v_style_id po_headers_all.style_id%TYPE; --Added by Sanjikum for Bug#4483042
196 v_orig_ship_id NUMBER; /*Bug 8586635*/
197
198 v_service_type_code VARCHAR2(30); -- added brathod Bug#5879769
199
200
201 CURSOR Check_Rfq_Quot_Cur IS
202 SELECT From_Type_Lookup_Code, from_header_id,
203 Type_Lookup_Code, Quotation_Class_Code,
204 Vendor_id, Vendor_Site_Id, Currency_Code, Ship_To_Location_Id,
205 style_id --Added by Sanjikum for Bug#4483042
206 FROM Po_Headers_All
207 WHERE Po_Header_Id = v_po_hdr_id;
208
209 CURSOR Fetch_Org_Id_Cur IS
210 SELECT Inventory_Organization_Id
211 FROM Hr_Locations
212 WHERE Location_Id = v_ship_loc_id;
213
214 CURSOR Item_Id_Cur IS
215 SELECT Item_Id, From_Header_Id, From_Line_Id, Line_Num
216 FROM Po_Lines_All
217 WHERE Po_Line_Id = v_po_line_id;
218
219 CURSOR Fetch_Uom_Cur IS
220 SELECT Unit_Meas_Lookup_Code
221 FROM Po_Lines_All
222 WHERE Po_Line_Id = v_po_line_id;
223
224 v_operating_id number; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
225 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
226 v_currency_code gl_sets_of_books.currency_code%type;
227
228
229 /*
230 || Commented the following two cursors as they are not used in the trigger
231 || Ramananada for bug#4703617
232 */
233 /*
234 CURSOR Fetch_Book_Id_Cur IS
235 SELECT Set_Of_Books_Id
236 FROM Org_Organization_Definitions
237 WHERE Operating_unit = v_operating_id; -- Modified by Ramananda for removal of SQL LITERALs
238 --WHERE NVL(Operating_unit,0) = v_operating_id;
239 CURSOR Sob_Cur is
240 select Currency_code
241 from gl_sets_of_books
242 where set_of_books_id = v_gl_set_of_bks_id; */
243
244 CURSOR tax_cur IS
245 SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
246 a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3, a.precedence_4 p_4, a.precedence_5 p_5,
247 a.currency, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.tax_type, a.vendor_id, a.modvat_flag
248 FROM JAI_PO_TAXES a
249 WHERE NVL( a.line_location_id, -999 ) = DECODE( v_quot_line_loc_id, -999, -999, v_quot_line_loc_id )
250 AND Po_Line_Id = v_from_line_id
251 ORDER BY a.tax_line_no;
252
253 CURSOR Fetch_Line_Focus_Id_Cur IS
254 SELECT Line_Focus_Id
255 FROM JAI_PO_LINE_LOCATIONS
256 WHERE Po_Line_Id = v_po_line_id
257 AND Line_Location_Id = v_line_loc_id;
258
259 CURSOR Fetch_UOMCode_Cur( v_temp_uom IN VARCHAR2 ) IS
260 SELECT Uom_Code
261 FROM Mtl_Units_Of_Measure
262 WHERE Unit_Of_Measure = v_temp_uom;
263
264 CURSOR Fetch_Count_Cur IS
265 SELECT COUNT(Line_Location_Id)
266 FROM JAI_PO_LINE_LOCATIONS
267 WHERE Po_Header_Id = v_quot_from_hdr_id;
268
269 -- Vijay Shankar for Bug# 3184418
270 v_tax_modified_flag CHAR(1);
271 CURSOR c_tax_modified_flag(p_line_location_id IN NUMBER) IS
272 SELECT tax_modified_flag
273 FROM JAI_PO_LINE_LOCATIONS
274 WHERE line_location_id = p_line_location_id;
275
276 /*Bug 8586635 - Start*/
277 CURSOR c_get_po_taxes (p_line_location_id IN NUMBER) IS
278 SELECT *
279 FROM JAI_PO_TAXES
280 WHERE line_location_id = p_line_location_id;
281
282 CURSOR c_get_po_line_loc_details (p_line_location_id IN NUMBER) IS
283 SELECT *
284 FROM JAI_PO_LINE_LOCATIONS
285 WHERE line_location_id = p_line_location_id;
286
287 r_get_po_taxes c_get_po_taxes%ROWTYPE;
288 r_get_po_line_loc_details c_get_po_line_loc_details%ROWTYPE;
289 l_prev_quantity NUMBER;
290 l_tax_amount NUMBER;
291 l_ship_line_amount NUMBER;
292 l_line_focus_id NUMBER;
293 /*Bug 8586635 - End*/
294
295 v_hook_value VARCHAR2(10);
296 BEGIN
297 pv_return_code := jai_constants.successful ;
298
299 /*------------------------------------------------------------------------------------------
300 CHANGE HISTORY:
301 S.No Date Author and Details
302 --------------------------------------------------------------------------------------------
303 1 19/09/2002 Vijay Shankar(cbabu) for Bug# 2541354 (Pre- Requisite for future bugs that modify this file)
304 When PO's are auto created from MRP workbench, then taxes are calculated properly
305 according to the quantity passed to the tax defaulting code. But in the present
306 situation quantity of fist distribution line present in shipment line is passed to tax
307 defaulting code from where taxes are getting calculated.
308
309 Solution: A batch program is made which should be run from Reports -> Run of India Local Purchasing responsibility
310 to default the taxes if PO's are auto created as given in the Functional. This
311 solution also solves the performance issue
312 Instead of firing the concurrent 'Concurrent request for defaulting Taxes in PO when linked with Quotation'
313 from this trigger, we are populating NEW temp table created and process them when the same
314 concurrent is fired manually.
315 This bug becomes a prerequisite for future Bugs that modifies this file.
316
317 2 16/12/2003 Vijay Shankar(cbabu) for Bug# 3184418, Fileversion: 618.1 (Obsoleted with Bug# 3570189)
318 When a Quotation is copied from a Source Document, then code is modified to copy the taxes from the SOURCE
319 document to Quotation if defaulted taxes on Source Document are modified by users
320
321 3 19/02/2004 Nagaraj.s for bug 3438863, Fileversion: 618.2 (Obsoleted with Bug# 3570189)
322 Hook Functionality is incorporated by calling the package jai_cmn_hook_pkg.sql
323 Hence this is a certain dependency issue and should be carefully handled
324
325 4 14/04/2004 Vijay Shankar for bugs# 3570189, Version : 619.1
326 PO Hook Functionality is made compatible with 11.5.3 Base Applications by removing last 12 params in call too
327 jai_cmn_hook_pkg.Ja_In_po_line_locations_all procedure. Also Locator related code is removed except for RELEASES
328 as defaultation happens whether the user navigated from localization form or not.
329 Tax defaultation is driven by jai_cmn_hook_pkg. By default taxes are defaulted for all PO's created in
330 Indian OU's (INR as Functional Currency)
331
332 FileVersion: 618.2 is obsoleted with this Version
333 This is a DEPENDANCY for later versions of the file
334
335 5 29-Nov-2004 Sanjikum for 4035297. Version 115.1
336 Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
337
338 Dependency Due to this Bug:-
339 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
340
341 6. 19-Mar-05 rallamse for bug#4227171 Version#115.2
342 Remove automatic GSCC errors
343
344 7. 19-Mar-05 rallamse for bug#4250072 Version#115.3
345 Changes for VAT
346
347 8. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
348 DB as required for CASE COMPLAINCE. Version 116.1
349
350 9 13-Jun-2005 File Version: 116.3
351 Ramananda for bug#4428980. Removal of SQL LITERALs is done
352
353 10 06-Jul-2005 rallamse for bug# PADDR Elimination
354 1. Commented rel_found and call to jai_po_cmn_pkg.query_locator_for_release
355
356 11. 08-Jul-2005 Sanjikum for Bug#4483042
357 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
358 IL functionality should work or not.
359
360 11. 08-Jul-2005 Sanjikum for Bug#4483042, File Version 117.2
361 1) Added a new column style_id in cursor - Check_Rfq_Quot_Cur
362
363 12. 04-Jun-2007 brathod for BUG#5879769, 6109941 File Version # 120.2
364 Added a Call to function get_service_type to get the Service_Type_Code.
365
366 13. 22-Jun-2007 CSahoo for bug#6144740, File Version 120.4
367 Added the parameter pr_new.quantity to the call to jai_po_tax_pkg.Ja_In_Po_Case1 procedure
368
369 14. 08-Jul-2009 Bug 8586635
370 Description: When the Shipment Lines are split in iSupplier, the appropriate Taxes are not updated
371 Fix: Tax Lines are inserted into jai_po_taxes on a prorated basis
372 and the tax amounts are updated in JAI_PO_LINE_LOCATIONS
373
374 15. 14-Aug-2009 Jia for bug#8745089 and bug#8765528.
375 Issue: Create a new quotations or a BPA Release with incorrect price list successfully
376 Fix: Add Item_UOM validation that is advanced pricing enhancement for quotation and Blanket.
377
378 16. 10-Sep-2010 Jia for GST Bug#10043656.
379
380 17. 18-Dec-2012 anupgupt for bug 16013918
381 removed gst changes
382
383 Future Dependencies For the release Of this Object:-
384 (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/
385 A datamodel change )
386
387 ---------------------------------------------------------------------------------------------
388 Current Version Current Bug Dependent Files Version
389 Of File On Bug/Patchset Dependent On
390 ja_in_po_tax_insert_trg.sql
391 ---------------------------------------------------------------------------------------------
392 618.2(OBSOLETE) 3438863 IN60105D2 + 3438863
393 619.1 3570189 IN60105D2 + 3570189
394
395 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0
396 ja_in_util_pkg_s.sql 115.0
397
398 115.3 4250072 IN60106 +
399 4035297 +
400 4245089
401 --------------------------------------------------------------------------------------------*/
402
403 --File.Sql.35 Cbabu
404 v_shipment_type := pr_new.Shipment_Type;
405 v_shipment_num := pr_new.Shipment_Num; -- added on 13th oct subbu
406 v_po_rel_id := pr_new.Po_Release_Id;
407 v_src_ship_id := pr_new.Source_Shipment_Id;
408 v_from_line_loc_id := pr_new.From_Line_Location_Id;
409 v_from_line_id := pr_new.From_Line_Id;
410 v_from_hdr_id := pr_new.From_Header_Id;
411 v_line_loc_id := pr_new.Line_Location_Id;
412 v_po_line_id := pr_new.Po_Line_Id ;
413 v_po_hdr_id := pr_new.Po_Header_Id;
414 v_price := pr_new.Price_Override;
415 v_qty := pr_new.Quantity;
416 v_cre_dt := pr_new.Creation_Date;
417 v_cre_by := pr_new.Created_By;
418 v_last_upd_dt := pr_new.Last_Update_Date ;
419 v_last_upd_by := pr_new.Last_Updated_By;
420 v_last_upd_login := pr_new.Last_Update_Login;
421 v_uom_measure := pr_new.Unit_Meas_Lookup_Code;
422 v_orig_ship_id := pr_new.original_shipment_id; /*Bug 8586635*/
423 success := 1;
424
425 /* Commented rallamse bug#4479131 PADDR Elimination
426 rel_found := false;
427 */
428
429 v_operating_id :=pr_new.ORG_ID;
430
431 -- added modification done by GSri on 12-jul-01
432
433 /* Commented by Ramananda for bug#4703617
434 || Reason: v_gl_set_of_bks_id is not used in the trigger
435 OPEN Fetch_Book_Id_Cur ;
436 FETCH Fetch_Book_Id_Cur INTO v_gl_set_of_bks_id;
437 CLOSE Fetch_Book_Id_Cur;
438 */
439
440 --IF jai_cmn_utils_pkg.check_jai_exists( p_calling_object => 'JA_IN_PO_TAX_INSERT_TRG',
441 -- p_set_of_books_id => v_gl_set_of_bks_id) = FALSE THEN
442 -- RETURN;
443 -- END IF;
444
445 -- Start, Vijay Shankar for Bug# 3570189
446 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_line_locations_all( -- added the hook call for bug 3438863 to customize the code for deciding on India loc Tax defaultation
447 pr_new.LINE_LOCATION_ID ,
448 pr_new.PO_HEADER_ID ,
449 pr_new.PO_LINE_ID ,
450 pr_new.QUANTITY ,
451 pr_new.QUANTITY_RECEIVED ,
452 pr_new.QUANTITY_ACCEPTED ,
453 pr_new.QUANTITY_REJECTED ,
454 pr_new.QUANTITY_BILLED ,
455 pr_new.QUANTITY_CANCELLED ,
456 pr_new.UNIT_MEAS_LOOKUP_CODE ,
457 pr_new.PO_RELEASE_ID ,
458 pr_new.SHIP_TO_LOCATION_ID ,
459 pr_new.SHIP_VIA_LOOKUP_CODE ,
460 pr_new.NEED_BY_DATE ,
461 pr_new.PROMISED_DATE ,
462 pr_new.LAST_ACCEPT_DATE ,
463 pr_new.PRICE_OVERRIDE ,
464 pr_new.ENCUMBERED_FLAG ,
465 pr_new.ENCUMBERED_DATE ,
466 pr_new.UNENCUMBERED_QUANTITY ,
467 pr_new.FOB_LOOKUP_CODE ,
468 pr_new.FREIGHT_TERMS_LOOKUP_CODE ,
469 pr_new.TAXABLE_FLAG ,
470 pr_new.TAX_NAME ,
471 pr_new.ESTIMATED_TAX_AMOUNT ,
472 pr_new.FROM_HEADER_ID ,
473 pr_new.FROM_LINE_ID ,
474 pr_new.FROM_LINE_LOCATION_ID ,
475 pr_new.START_DATE ,
476 pr_new.END_DATE ,
477 pr_new.LEAD_TIME ,
478 pr_new.LEAD_TIME_UNIT ,
479 pr_new.PRICE_DISCOUNT ,
480 pr_new.TERMS_ID ,
481 pr_new.APPROVED_FLAG ,
482 pr_new.APPROVED_DATE ,
483 pr_new.CLOSED_FLAG ,
484 pr_new.CANCEL_FLAG ,
485 pr_new.CANCELLED_BY ,
486 pr_new.CANCEL_DATE ,
487 pr_new.CANCEL_REASON ,
488 pr_new.FIRM_STATUS_LOOKUP_CODE ,
489 pr_new.FIRM_DATE ,
490 pr_new.ATTRIBUTE_CATEGORY ,
491 pr_new.ATTRIBUTE1 ,
492 pr_new.ATTRIBUTE2 ,
493 pr_new.ATTRIBUTE3 ,
494 pr_new.ATTRIBUTE4 ,
495 pr_new.ATTRIBUTE5 ,
496 pr_new.ATTRIBUTE6 ,
497 pr_new.ATTRIBUTE7 ,
498 pr_new.ATTRIBUTE8 ,
499 pr_new.ATTRIBUTE9 ,
500 pr_new.ATTRIBUTE10 ,
501 pr_new.UNIT_OF_MEASURE_CLASS ,
502 pr_new.ENCUMBER_NOW ,
503 pr_new.ATTRIBUTE11 ,
504 pr_new.ATTRIBUTE12 ,
505 pr_new.ATTRIBUTE13 ,
506 pr_new.ATTRIBUTE14 ,
507 pr_new.ATTRIBUTE15 ,
508 pr_new.INSPECTION_REQUIRED_FLAG ,
509 pr_new.RECEIPT_REQUIRED_FLAG ,
510 pr_new.QTY_RCV_TOLERANCE ,
511 pr_new.QTY_RCV_EXCEPTION_CODE ,
512 pr_new.ENFORCE_SHIP_TO_LOCATION_CODE ,
513 pr_new.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
514 pr_new.DAYS_EARLY_RECEIPT_ALLOWED ,
515 pr_new.DAYS_LATE_RECEIPT_ALLOWED ,
516 pr_new.RECEIPT_DAYS_EXCEPTION_CODE ,
517 pr_new.INVOICE_CLOSE_TOLERANCE ,
518 pr_new.RECEIVE_CLOSE_TOLERANCE ,
519 pr_new.SHIP_TO_ORGANIZATION_ID ,
520 pr_new.SHIPMENT_NUM ,
521 pr_new.SOURCE_SHIPMENT_ID ,
522 pr_new.SHIPMENT_TYPE ,
523 pr_new.CLOSED_CODE ,
524 pr_new.REQUEST_ID ,
525 pr_new.PROGRAM_APPLICATION_ID ,
526 pr_new.PROGRAM_ID ,
527 pr_new.PROGRAM_UPDATE_DATE ,
528 pr_new.USSGL_TRANSACTION_CODE ,
529 pr_new.GOVERNMENT_CONTEXT ,
530 pr_new.RECEIVING_ROUTING_ID ,
531 pr_new.ACCRUE_ON_RECEIPT_FLAG ,
532 pr_new.CLOSED_REASON ,
533 pr_new.CLOSED_DATE ,
534 pr_new.CLOSED_BY ,
535 pr_new.ORG_ID ,
536 pr_new.QUANTITY_SHIPPED ,
537 pr_new.COUNTRY_OF_ORIGIN_CODE ,
538 pr_new.TAX_USER_OVERRIDE_FLAG ,
539 pr_new.MATCH_OPTION ,
540 pr_new.TAX_CODE_ID ,
541 pr_new.CALCULATE_TAX_FLAG ,
542 pr_new.CHANGE_PROMISED_DATE_REASON
543 );
544
545 IF v_hook_value = 'FALSE' THEN
546 RETURN;
547 END IF;
548 -- End, 3570189
549
550 -- Commented by Vijay Shankar for bugs# 3570189
551 -- jai_po_cmn_pkg.query_locator_for_release( pr_new.Po_Release_Id, rel_found );
552
553 OPEN Check_Rfq_Quot_Cur;
554 FETCH Check_Rfq_Quot_Cur INTO v_from_type_lookup_code, v_quot_from_hdr_id, v_type_lookup_code, v_Quot_Class_Code,
555 v_vendor_id, v_vendor_site_id, v_curr, v_ship_loc_id,
556 v_style_id; --Added by Sanjikum for Bug#4483042
557 CLOSE Check_Rfq_Quot_Cur;
558
559 --code added by Sanjikum for Bug#4483042
560 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => v_style_id) = FALSE THEN
561 return;
562 END IF;
563
564 open c_get_po_line_loc_details(v_orig_ship_id);
565 fetch c_get_po_line_loc_details into r_get_po_line_loc_details;
566 close c_get_po_line_loc_details;
567
568 /*Bug 8586635 - Start*/
569 /*
570 Bug 8740543
571 Added clause to compare PO Header ID and Line ID so that the below code is called only when split is done
572 */
573 IF v_orig_ship_id IS NOT NULL
574 AND r_get_po_line_loc_details.po_header_id = v_po_hdr_id
575 AND r_get_po_line_loc_details.po_line_id = v_po_line_id THEN
576
577 /*Get the Quantity of the parent Line in an Autonomous Transaction*/
578 ja_in_po_get_lineloc_p (v_orig_ship_id, l_prev_quantity);
579 /*Insert into JAI_PO_LINE_LOCATIONS*/
580 jai_po_cmn_pkg.insert_line
581 ( v_type_lookup_code,
582 v_line_loc_id,
583 r_get_po_line_loc_details.po_header_id,
584 r_get_po_line_loc_details.po_line_id,
585 r_get_po_line_loc_details.creation_date,
586 r_get_po_line_loc_details.created_by,
587 r_get_po_line_loc_details.last_update_date,
588 r_get_po_line_loc_details.last_updated_by,
589 r_get_po_line_loc_details.last_update_login,
590 'I'
591 );
592
593 select line_focus_id into l_line_focus_id
594 from JAI_PO_LINE_LOCATIONS
595 where line_location_id = v_line_loc_id;
596 /*Insert Taxes of the parent line in to the split line after adjusting the Tax Amount and Tax Target Amount*/
597 for r_get_po_taxes in c_get_po_taxes(v_orig_ship_id) loop
598
599 jai_po_tax_pkg.Ja_In_Po_Insert
600 ( v_type_lookup_code, v_quot_class_code,
601 l_line_focus_id, v_line_loc_id,
602 r_get_po_taxes.tax_line_no, r_get_po_taxes.po_line_id, r_get_po_taxes.po_header_id,
603 r_get_po_taxes.precedence_1, r_get_po_taxes.precedence_2, r_get_po_taxes.precedence_3,
604 r_get_po_taxes.precedence_4, r_get_po_taxes.precedence_5,
605 r_get_po_taxes.precedence_6, r_get_po_taxes.precedence_7, r_get_po_taxes.precedence_8,
606 r_get_po_taxes.precedence_9, r_get_po_taxes.precedence_10,
607 r_get_po_taxes.tax_id, NULL, 0, r_get_po_taxes.currency,
608 r_get_po_taxes.tax_rate, r_get_po_taxes.qty_rate, r_get_po_taxes.uom,
609 (r_get_po_taxes.tax_amount * pr_new.QUANTITY/l_prev_quantity), r_get_po_taxes.tax_type, r_get_po_taxes.modvat_flag,
610 r_get_po_taxes.vendor_id, (r_get_po_taxes.tax_target_amount * pr_new.QUANTITY/l_prev_quantity),
611 r_get_po_taxes.creation_date, r_get_po_taxes.created_by, r_get_po_taxes.last_update_date,
612 r_get_po_taxes.last_updated_by, r_get_po_taxes.last_update_login,
613 r_get_po_taxes.tax_category_id
614 );
615
616 END LOOP;
617
618 select sum(tax_amount) into l_tax_amount
619 from jai_po_taxes
620 where line_location_id = v_line_loc_id;
621
622 l_ship_line_amount := v_qty * v_price;
623
624 UPDATE JAI_PO_LINE_LOCATIONS
625 SET tax_modified_flag = 'N',
626 tax_amount = l_tax_amount,
627 total_amount = l_ship_line_amount + l_tax_amount,
628 tax_category_id = r_get_po_taxes.tax_category_id
629 WHERE line_location_id = v_line_loc_id;
630
631 ELSE
632 /*Bug 8586635 - End*/
633 IF v_shipment_type NOT IN ( 'SCHEDULED' , 'BLANKET' ) THEN
634
635 OPEN Fetch_Org_Id_Cur;
636 FETCH Fetch_Org_Id_Cur INTO v_org_id;
637 CLOSE Fetch_Org_Id_Cur;
638
639 OPEN Item_Id_Cur;
640 FETCH Item_Id_Cur INTO v_item_id, v_q_hdr_id, v_q_line_id,v_line_num;
641 CLOSE Item_Id_Cur;
642
643 OPEN Fetch_Uom_Cur;
644 FETCH Fetch_Uom_Cur INTO v_temp_uom;
645 CLOSE Fetch_Uom_Cur;
646
647 /* Bug#5879769. Added by brahtod */
648 v_service_type_code := JAI_AR_RCTLA_TRIGGER_PKG.get_service_type(v_vendor_id, v_vendor_site_id, 'V');
649
650 IF NVL( v_uom_measure, '$' ) = '$' THEN
651 v_uom_measure := v_temp_uom;
652 END IF;
653
654 x := v_line_loc_id;
655 y := v_po_line_id;
656
657 IF v_type_lookup_code = 'QUOTATION' THEN
658 x := v_from_line_loc_id;
659 y := v_from_line_id;
660 END IF;
661
662 /* commented by Vijay Shankar for bugs# 3570189
663 IF v_type_lookup_code = 'QUOTATION' THEN
664 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINRFQQ', found );
665 IF NOT found AND y IS NULL THEN
666 RETURN;
667 END IF;
668 END IF;
669 */
670
671 -- Added code for copy document.
672 -- for quotation to PO and PO to PO on 15-OCT-2000 subbu
673
674 IF v_from_type_lookup_code IN ('QUOTATION','PLANNED','BLANKET','STANDARD')
675 AND v_quot_from_hdr_id IS NOT NULL
676 THEN
677
678 OPEN Fetch_Count_Cur;
679 FETCH Fetch_Count_Cur INTO v_count;
680 CLOSE Fetch_Count_Cur;
681
682 IF v_count > 0 THEN
683
684 v_type := 'S';
685
686 INSERT INTO JAI_PO_COPYDOC_T(
687 TYPE, PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, LINE_NUM,
688 SHIPMENT_NUM, ITEM_ID, FROM_HEADER_ID, FROM_TYPE_LOOKUP_CODE,
689 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
690 ) Values (
691 v_type, v_po_hdr_id, v_po_line_id, v_line_loc_id, v_line_num,
692 v_shipment_num, v_item_id, v_quot_from_hdr_id, v_from_type_lookup_code,
693 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
694 );
695
696 result := Fnd_Request.Set_Mode( TRUE );
697
698 req_id := Fnd_Request.Submit_Request(
699 'JA', 'JAINCPDC', 'Copy Document India Localization.', SYSDATE, FALSE,
700 v_type, v_po_hdr_id, v_po_line_id, v_line_loc_id, v_line_num,
701 v_shipment_num, v_item_id, v_quot_from_hdr_id, v_from_type_lookup_code,
702 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
703 );
704
705 RETURN;
706
707 END IF;
708
709 END IF;
710
711 -- end of modification for copy quotation to PO. on 15th oct 2000. subbu
712
713 -- If the following if is satisfied, it implies that PO has been linked by a quotation.
714 IF v_type_lookup_code IN ( 'STANDARD', 'PLANNED') AND v_q_line_id IS NOT NULL THEN --Uncommented the change for bug #9362704
715
716 OPEN Fetch_UOMCode_Cur( v_temp_uom );
717 FETCH Fetch_UOMCode_Cur INTO v_unit_code;
718 CLOSE Fetch_UOMCode_Cur;
719
720 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value( v_vendor_id, v_vendor_site_id, v_item_id, v_unit_code );
721
722 IF v_assessable_value IS NOT NULL AND v_assessable_value > 0 THEN
723 v_assessable_value := v_assessable_value * v_qty;
724 ELSE
725 v_assessable_value := v_qty * v_price;
726 END IF;
727
728 /* Begin - Bug#4250072 - Added by rallamse for VAT */
729
730 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value (
731 p_party_id => v_vendor_id,
732 p_party_site_id => v_vendor_site_id,
733 p_inventory_item_id => v_item_id,
734 p_uom_code => v_unit_code,
735 p_default_price => v_price,
736 p_ass_value_date => trunc(sysdate) ,
737 p_party_type => 'V'
738 );
739
740 ln_vat_assess_value := ln_vat_assess_value * v_qty ;
741
742 /* End - Bug#4250072 - Added by rallamse for VAT */
743
744 -- Insert Statement added for Bug# 2541354 (When PO is created from Quotation during MRP release then taxes are defaulted correctly)
745 INSERT INTO JAI_PO_QUOT_LINES_T (
746 po_header_id, po_line_id, line_location_id, from_header_id,
747 from_line_id, price_override, uom_code, assessable_value,
748 creation_date, created_by, last_update_date, last_updated_by, last_update_login
749 ) VALUES (
750 v_po_hdr_id, v_po_line_id, v_line_loc_id, v_q_hdr_id,
751 v_q_line_id, v_price, v_unit_code, v_assessable_value,
752 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
753 );
754
755 RETURN; ------ DO NOT ALLOW THE OTHER DEFAULTING TO TAKE PLACE.
756
757 END IF;
758
759 IF v_type_lookup_code = 'QUOTATION' THEN
760
761 -- Added by Jia for bug#8745089 on 2009-08-14, Begin
762 -------------------------------------------------------------------------------------------
763 JAI_AVLIST_VALIDATE_PKG.Check_AvList_Validation( pn_party_id => v_vendor_id
764 , pn_party_site_id => v_vendor_site_id
765 , pn_inventory_item_id => v_item_id
766 , pd_ordered_date => trunc(sysdate)
767 , pv_party_type => 'V'
768 , pn_pricing_list_id => null
769 );
770 -------------------------------------------------------------------------------------------
771 -- Added by Jia for bug#8745089 on 2009-08-14, End
772
773 jai_po_cmn_pkg.insert_line( 'CATALOG', v_line_loc_id, v_po_hdr_id, v_po_line_id, v_cre_dt,
774 v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I' , v_service_type_code -- 5879769, brathod
775 );
776
777 -- added by Vijay Shankar for Bug# 3184418
778 IF x IS NOT NULL THEN
779
780 OPEN c_tax_modified_flag(x);
781 FETCH c_tax_modified_flag INTO v_tax_modified_flag;
782 CLOSE c_tax_modified_flag;
783
784 IF v_tax_modified_flag IS NULL THEN
785 v_tax_modified_flag := 'N';
786 END IF;
787
788 ELSE
789 v_tax_modified_flag := 'N';
790 END IF;
791
792 -- condition added by Vijay Shankar for Bug# 3184418
793 IF v_tax_modified_flag = 'Y' THEN
794 jai_po_tax_pkg.Ja_In_Po_Case1(
795 v_type_lookup_code, v_quot_class_code, v_vendor_id, v_vendor_site_id, v_curr,
796 v_org_id, v_item_id, v_uom_measure, v_line_loc_id,
797 v_po_hdr_id, v_po_line_id, y, x, v_price, v_Qty, v_cre_dt,
798 v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I', success, pr_new.quantity -- pr_new.quantity added by csahoo for bug#6144740
799 );
800 END IF;
801
802 END IF;
803
804 /* commented by Vijay Shankar for bugs# 3570189
805 IF v_type_lookup_code = 'RFQ' THEN
806 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINRFQQ', found );
807 success := 1;
808
809 ELSIF v_type_lookup_code IN ( 'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) THEN
810 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINPO', found );
811 success := 1;
812 END IF;
813
814 IF NOT found AND v_type_lookup_code <> 'RFQ' THEN
815 If v_hook_value = 'FALSE' THEN
816 RETURN;
817 End if; -- bug 3438863
818 END IF;
819 */
820
821 success := 1;
822
823 IF v_type_lookup_code <> 'QUOTATION' THEN
824
825 jai_po_cmn_pkg.insert_line( v_type_lookup_code, v_line_loc_id, v_po_hdr_id, v_po_line_id, v_cre_dt,
826 v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I' ,v_service_type_code -- 5879769, brathod
827 );
828
829 END IF;
830
831 IF x IS NOT NULL AND y IS NOT NULL AND v_type_lookup_code = 'QUOTATION' THEN
832 -- condition added by Vijay Shankar for Bug# 3184418
833 IF v_tax_modified_flag = 'Y' THEN
834 success := 0;
835 ELSE
836 success := 1;
837 END IF;
838
839 END IF;
840
841 IF success <> 0 THEN
842 jai_po_tax_pkg.Ja_In_Po_Case2 (
843 v_type_lookup_code, v_quot_class_code, v_vendor_id, v_vendor_site_id, v_curr,
844 v_org_id, v_item_id, v_line_loc_id, v_po_hdr_id, v_po_line_id,
845 v_price, v_qty, v_cre_dt, v_cre_by, v_last_upd_dt,
846 v_last_upd_by, v_last_upd_login, v_uom_measure, NULL,P_VAT_ASSESS_VALUE => NULL
847 /* for bug 16013918 by anupgupt
848 , pn_gst_assessable_value => NULL -- Added by Jia for GST Bug#10043656 on 2010/09/10
849 */
850 );
851 END IF;
852
853 -- modified by Vijay Shankar for bugs# 3570189
854 -- ELSIF v_shipment_type IN ( 'SCHEDULED', 'BLANKET' ) AND rel_found = TRUE THEN
855 ELSIF v_shipment_type IN ( 'SCHEDULED', 'BLANKET' ) THEN
856
857 -- Added by Jia for bug#8765528 on 2009-08-14, Begin
858 -------------------------------------------------------------------------------------------
859 JAI_AVLIST_VALIDATE_PKG.Check_AvList_Validation( pn_party_id => v_vendor_id
860 , pn_party_site_id => v_vendor_site_id
861 , pn_inventory_item_id => v_item_id
862 , pd_ordered_date => trunc(sysdate)
863 , pv_party_type => 'V'
864 , pn_pricing_list_id => null
865 );
866 -------------------------------------------------------------------------------------------
867 -- Added by Jia for bug#8765528 on 2009-08-14, End
868
869 -- Vijay Shankar for Bug# 3570189
870
871 /* Commented rallamse bug#4479131 PADDR Elimination
872 jai_po_cmn_pkg.query_locator_for_release( pr_new.Po_Release_Id, rel_found );
873 */
874
875 jai_po_cmn_pkg.process_release_shipment (
876 v_shipment_type, v_src_ship_id, v_line_loc_id, v_po_line_id, v_po_hdr_id,
877 v_qty, v_po_rel_id, v_cre_dt, v_cre_by,
878 v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I'
879 );
880
881 END IF;
882
883 END IF; /*IF v_orig_ship_id IS NOT NULL THEN*/
884
885 END ARI_T1 ;
886
887 /*
888 REM +======================================================================+
889 REM NAME ARU_T1
890 REM
891 REM DESCRIPTION Called from trigger JAI_PO_LLA_ARIUD_T1
892 REM
893 REM NOTES Refers to old trigger JAI_PO_LLA_ARU_T3
894 REM
895 REM +======================================================================+
896 */
897 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
898 p_tax_amount NUMBER ;
899 x NUMBER ;
900 y NUMBER ;
901 v_type_lookup_code VARCHAR2(40) ;
902 v_flag VARCHAR2(1) ;
903 flag VARCHAR2(8) ;
904 v_quot_class_code VARCHAR2(25) ;
905 v_vendor_id NUMBER ;
906 v_vendor_site_id NUMBER ;
907 v_curr VARCHAR2(15) ;
908 v_ship_loc_id NUMBER ;
909 v_org_id NUMBER ;
910 v_from_line_loc_id NUMBER; --File.Sql.35 Cbabu := pr_new.from_line_location_id ;
911 v_from_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.from_line_id ;
912 v_line_loc_id NUMBER; --File.Sql.35 Cbabu := pr_new.line_location_id ;
913 v_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.po_line_id ;
914 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.po_header_id ;
915 v_price NUMBER; --File.Sql.35 Cbabu := pr_new.price_override ;
916 v_old_price NUMBER; --File.Sql.35 Cbabu := pr_old.price_override ;
917 v_qty NUMBER; --File.Sql.35 Cbabu := pr_new.quantity ;
918 v_old_qty NUMBER; --File.Sql.35 Cbabu := pr_old.quantity ;
919 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.creation_date ;
920 v_cre_by NUMBER; --File.Sql.35 Cbabu := pr_new.created_by ;
921 v_last_upd_dt DATE ; --File.Sql.35 Cbabu ; := pr_new.last_update_date ;
922 v_last_upd_by NUMBER ; --File.Sql.35 Cbabu := pr_new.last_updated_by ;
923 v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.last_update_login ;
924 v_uom_measure VARCHAR2(50) ;
925 v_uom_code VARCHAR2(25) ;
926 v_shipment_type VARCHAR2(25); --File.Sql.35 Cbabu := pr_new.shipment_type ;
927 v_src_ship_id NUMBER ; --File.Sql.35 Cbabu := pr_new.source_shipment_id ;
928 v_po_rel_id NUMBER ; --File.Sql.35 Cbabu := pr_new.po_release_id ;
929 v_item_id NUMBER ;
930 rel_found BOOLEAN ; --File.Sql.35 Cbabu := FALSE ;
931 found BOOLEAN ;
932 retcode BOOLEAN ;
933 success NUMBER ; --File.Sql.35 Cbabu := 1 ;
934 v_tax_amt NUMBER ; --File.Sql.35 Cbabu := 0 ;
935 v_tot_amt NUMBER ; --File.Sql.35 Cbabu := 0 ;
936 v_assessable_value NUMBER ; --File.Sql.35 Cbabu := 0 ;
937 ln_vat_assess_value NUMBER ; -- added, Harshita for bug #4245062
938 /* for bug 16013918 by anupgupt
939 ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10043656 on 2010/09/10
940 */
941 v_curr_conv_factor NUMBER ;
942 dummy NUMBER ; --File.Sql.35 Cbabu := 1 ;
943 v_exist NUMBER ; --File.Sql.35 Cbabu := 0 ;
944 v_hook_value VARCHAR2(10);/*added by rchandan for paddr elimination*/
945
946 -- Start of bug 3037284
947 v_quantity_received PO_LINE_LOCATIONS_ALL.QUANTITY_RECEIVED%TYPE ; --File.Sql.35 Cbabu := nvl(pr_new.quantity_received,0) ;
948 v_quantity_cancelled PO_LINE_LOCATIONS_ALL.QUANTITY_CANCELLED%TYPE ; --File.Sql.35 Cbabu := pr_new.quantity_cancelled ;
949 v_style_id po_headers_all.style_id%TYPE;
950
951 CURSOR rec_get_tax_amount
952 IS
953 SELECT
954 nvl(a.tax_amount,0) tax_amount,
955 nvl(b.adhoc_flag, 'N') adhoc_flag
956 FROM
957 JAI_PO_TAXES a ,
958 JAI_CMN_TAXES_ALL b
959 WHERE
960 a.tax_id = b.tax_id AND
961 a.po_line_id = v_po_line_id AND
962 a.po_header_id = v_po_hdr_id AND
963 a.line_location_id = v_line_loc_id FOR UPDATE OF a.tax_amount;
964
965 CURSOR rec_calc_total_tax
966 IS
967 SELECT sum(tax_amount )
968 FROM JAI_PO_TAXES
969 WHERE
970 po_line_id = v_po_line_id AND
971 po_header_id = v_po_hdr_id AND
972 line_location_id = v_line_loc_id AND
973 tax_type <> jai_constants.tax_type_tds ; /*'TDS';Ramananda for removal of SQL LITERALs */
974
975 cur_rec_get_total REC_CALC_TOTAL_TAX%ROWTYPE;
976 l_total_tax_amount JAI_PO_LINE_LOCATIONS.TAX_AMOUNT%TYPE;
977
978 -- END of bug 3037284
979
980 CURSOR check_rfq_quot_cur
981 IS
982 SELECT
983 type_lookup_code ,
984 quotation_class_code ,
985 vendor_id ,
986 vendor_site_id ,
987 currency_code ,
988 ship_to_location_id
989 , rate_type, rate_date, rate, -- Vijay Shankar for Bug #3184673
990 style_id
991 FROM
992 po_headers_all
993 WHERE
994 po_header_id = v_po_hdr_id;
995
996 -- Get the Inventory Organization Id
997
998 CURSOR fetch_org_id_cur IS
999 SELECT
1000 Inventory_Organization_Id
1001 FROM
1002 hr_locations
1003 WHERE
1004 location_id = v_ship_loc_id;
1005
1006
1007
1008 CURSOR Item_Id_Cur IS
1009 SELECT
1010 item_id,
1011 unit_meas_lookup_code
1012 FROM
1013 po_lines_all
1014 WHERE
1015 po_line_id = v_po_line_id;
1016
1017
1018 CURSOR tax_modified_cur
1019 IS
1020 SELECT
1021 NVL( Tax_Modified_Flag, 'N' )
1022 FROM
1023 JAI_PO_LINE_LOCATIONS
1024 WHERE
1025 Po_Line_Id = v_po_line_id AND
1026 Line_Location_Id = v_line_loc_id;
1027
1028
1029 CURSOR fetch_sum_cur
1030 IS
1031 SELECT
1032 SUM( NVL( Tax_Amount, 0 ) )
1033 FROM
1034 JAI_PO_TAXES
1035 WHERE
1036 po_line_id = v_po_line_id AND
1037 line_location_id = v_line_loc_id AND
1038 tax_type <> jai_constants.tax_type_tds ; /*'TDS';Ramananda for removal of SQL LITERALs */
1039
1040 CURSOR fetch_sum1_cur
1041 IS
1042 SELECT
1043 sum( nvl( tax_amount, 0 ) )
1044 FROM
1045 JAI_PO_TAXES
1046 WHERE
1047 po_line_id = v_po_line_id AND
1048 line_location_id is null AND
1049 tax_type <> jai_constants.tax_type_tds ; /*'TDS';Ramananda for removal of SQL LITERALs */
1050
1051 -- To Check whether record modified is localized or not
1052 CURSOR localizaed_check_cur
1053 IS
1054 SELECT
1055 nvl(1,0)
1056 FROM
1057 JAI_PO_LINE_LOCATIONS
1058 WHERE
1059 po_line_id = v_po_line_id AND
1060 Line_Location_Id = v_line_loc_id;
1061
1062
1063 CURSOR fetch_uomcode_cur
1064 IS
1065 SELECT
1066 uom_code
1067 FROM
1068 mtl_units_of_measure
1069 WHERE
1070 unit_of_measure = v_uom_measure;
1071
1072 ------------- added by Gsr 12-jul-01
1073
1074 v_operating_id number; --File.Sql.35 Cbabu :=pr_new.org_id ;
1075 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type ;
1076 v_currency_code gl_sets_of_books.currency_code%type ;
1077
1078
1079 /*
1080 || Commented the following two cursors and added the cur_get_curr_code cursor
1081 || Ramananada for bug#4703617
1082 */
1083 /*
1084 CURSOR fetch_book_id_cur
1085 IS
1086 SELECT set_of_books_id
1087 FROM org_organization_definitions
1088 WHERE operating_unit = v_operating_id; -- Modified by Ramananda for removal of SQL LITERALs
1089 --nvl(operating_unit,0) = v_operating_id;
1090 CURSOR sob_cur IS
1091 SELECT currency_code
1092 FROM gl_sets_of_books
1093 WHERE
1094 set_of_books_id = v_gl_set_of_bks_id; */
1095
1096 /*
1097 || Added the following cur_get_curr_code cursor
1098 || Ramananada for bug#4703617
1099 */
1100 CURSOR cur_get_curr_code IS
1101 SELECT
1102 sob.currency_code
1103 FROM
1104 financials_system_params_all FSP, gl_sets_of_books SOB
1105 WHERE
1106 FSP.set_of_books_id = SOB.set_of_books_id
1107 AND FSP.org_id = v_operating_id ;
1108
1109
1110 -- Vijay Shankar for Bug #3184673
1111 v_po_rate_type PO_HEADERS_ALL.rate_type%TYPE;
1112 v_po_rate_date PO_HEADERS_ALL.rate_date%TYPE;
1113 v_po_rate PO_HEADERS_ALL.rate%TYPE;
1114
1115 v_currency_conv_rate NUMBER;
1116 v_trigger_name VARCHAR2(50);
1117 v_temp VARCHAR2(2500);
1118
1119 --Added by Kevin Cheng for Retroactive Price 2008/01/13
1120 --=====================================================
1121 lv_retro_price_flag VARCHAR2(1) := 'N';
1122 lv_process_flag VARCHAR2(10);
1123 lv_process_message VARCHAR2(2000);
1124 --=====================================================
1125
1126 BEGIN
1127 pv_return_code := jai_constants.successful ;
1128 /*------------------------------------------------------------------------------------------
1129 FILENAME: Ja_In_Po_Tax_Update_Trg.sql
1130
1131 S.No dd/mm/yyyy Author and Details
1132 ------------------------------------------------------------------------------------------
1133 1. 14/08/2003 Aiyer , Bug #3037284 ,File version 616.1
1134 Changed the triggering condition to fire when cancel_flag is 'Y'.
1135 In case of full cancellation of a Purchase order line (no receipt done),
1136 the lines from JAI_PO_TAXES and JAI_PO_LINE_LOCATIONS are deleted.
1137 In case of partial cancellation of purchase order line, apportion the tax amount
1138 in the ratio of quantity_received to original line quantity i.e quantity_received/quantity
1139 in table JAI_PO_TAXES.
1140 Calculate the tax_amount in the table JAI_PO_LINE_LOCATIONS as a sum of all records
1141 in the table JAI_PO_TAXES for that line_locations_id excluding 'TDS'
1142 type of taxes.
1143 The total amount in JAI_PO_LINE_LOCATIONS is calculated as
1144 (po_line_locations_all.quantity_received * po_line_locations_all.price_override)
1145 +
1146 sum of all taxes from JAI_PO_TAXES for that line_location_id .
1147
1148 2 15/10/2003 Vijay Shankar for Bug #3184673, File version 618.1
1149 Adhoc taxes are not apportioned when quantity is changed which is handled with this bug.
1150 Code is added to update JAI_PO_TAXES tables based on old and new quantity for adhoc taxes
1151
1152 3 29-Nov-2004 Sanjikum for 4035297. Version 115.1
1153 Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
1154
1155 Dependency Due to this Bug:-
1156 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
1157
1158 4. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.2
1159 The Assessable Value is calculated for the transaction. For this, a call is
1160 made to the function ja_in_vat_assessable_value_f.sql with the parameters
1161 relevant for the transaction. This assessable value is again passed to the
1162 procedure that calucates the taxes.
1163 Base bug - #4245089
1164
1165 5. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
1166 DB as required for CASE COMPLAINCE. Version 116.1
1167
1168 6. 13-Jun-2005 File Version: 116.2
1169 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1170
1171 7. 08-Jul-2005 Sanjikum for Bug#4483042 File Version: 116.3
1172 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
1173 IL functionality should work or not.
1174
1175 8. 8-Jul-2005 File Version: 116.3
1176 rchandan for bug#4479131
1177 The object is modified to eliminate the paddr usage.
1178
1179 9 . 8-Jul-2005 Sanjikum for Bug#4483042, File Version 117.2
1180 1) Added a new column in cursor - check_rfq_quot_cur
1181
1182 10. 15-Jan-2008 Kevin Cheng for Retroactive Price Enhancement
1183 1) Insert change history table;
1184 2) Add parameter to procedure called in ARU_T1;
1185
1186 11. 23-Mar-2009 Bug 8224547 File version 120.6.12010000.2/120.7
1187 Issue - Not able to update PO line quantity and save - getting the PL/SQL
1188 character to number conversion error.
1189 Cause - In procedure ARU_T1, the call to jai_po_tax_pkg.Ja_In_Po_Case2 didn't
1190 have the 4 parameters (they have default value of null) introduced for
1191 bug 5096787. In the previous version, lv_retro_price_flag was passed
1192 as the last parameter, which got assigned to the parameter v_rate as
1193 per the spec. v_rate is a number variable, and this caused the error.
1194 Fix - Inserted 4 null values as parameters (just before lv_retro_price_flag)
1195 in the call to jai_po_tax_pkg.Ja_In_Po_Case2.
1196
1197 12. 10-Sep-2010 Jia for GST Bug#10043656.
1198
1199 Future Dependencies For the release Of this Object:-
1200 (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/
1201 A datamodel change )
1202 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1203 Current Version Current Bug Dependent Files Version Author Date Remarks
1204 Of File On Bug/Patchset Dependent On
1205
1206 Ja_In_Po_Tax_Update_Trg.sql
1207 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1208 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
1209 ja_in_util_pkg_s.sql 115.0 Sanjikum
1210
1211 115.2 4245062 IN60106 + 4245089 hjujjuru 17/03/2005 VAT Implelentation
1212
1213 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
1214
1215
1216 SELECT 'START_JA_IN_PO_TAX_UPDATE_TRG' INTO v_trigger_name FROM DUAL;
1217
1218 --File.Sql.35 Cbabu
1219 v_from_line_loc_id := pr_new.from_line_location_id ;
1220 v_from_line_id := pr_new.from_line_id ;
1221 v_line_loc_id := pr_new.line_location_id ;
1222 v_po_line_id := pr_new.po_line_id ;
1223 v_po_hdr_id := pr_new.po_header_id ;
1224 v_price := pr_new.price_override ;
1225 v_old_price := pr_old.price_override ;
1226 v_qty := pr_new.quantity ;
1227 v_old_qty := pr_old.quantity ;
1228 v_cre_dt := pr_new.creation_date ;
1229 v_cre_by := pr_new.created_by ;
1230 v_last_upd_dt := pr_new.last_update_date ;
1231 v_last_upd_by := pr_new.last_updated_by ;
1232 v_last_upd_login := pr_new.last_update_login ;
1233 v_shipment_type := pr_new.shipment_type ;
1234 v_src_ship_id := pr_new.source_shipment_id ;
1235 v_po_rel_id := pr_new.po_release_id ;
1236 rel_found := FALSE ;
1237 success := 1 ;
1238 v_tax_amt := 0 ;
1239 v_tot_amt := 0 ;
1240 v_assessable_value := 0 ;
1241 dummy := 1 ;
1242 v_exist := 0 ;
1243 v_quantity_received := nvl(pr_new.quantity_received,0) ;
1244 v_quantity_cancelled := pr_new.quantity_cancelled ;
1245 v_operating_id := pr_new.org_id ;
1246
1247
1248 /*
1249 || Commented by Ramananda for bug4703617
1250 OPEN Fetch_Book_Id_Cur ;
1251 FETCH Fetch_Book_Id_Cur INTO v_gl_set_of_bks_id;
1252 CLOSE Fetch_Book_Id_Cur;
1253 */
1254
1255 IF pr_new.org_id IS NOT NULL
1256 THEN
1257
1258 /*
1259 || Added by Ramananda for bug4703617
1260 */
1261 OPEN cur_get_curr_code ;
1262 FETCH cur_get_curr_code INTO v_currency_code ;
1263 CLOSE cur_get_curr_code ;
1264
1265 /*
1266 || Commented by Ramananda for bug4703617
1267 OPEN Sob_cur;
1268 FETCH Sob_cur INTO v_currency_code;
1269 CLOSE Sob_cur; */
1270
1271 /*IF nvl(v_currency_code,'###') <> 'INR'
1272 THEN
1273 RETURN;
1274 END IF;*/
1275 END IF;
1276
1277 --Commented the above and added the below by Sanjikum for Bug#4035297
1278
1279 -- IF jai_cmn_utils_pkg.check_jai_exists( p_calling_object => 'JA_IN_PO_TAX_UPDATE_TRG',
1280 -- p_set_of_books_id => v_gl_set_of_bks_id) = FALSE THEN
1281 -- RETURN;
1282 -- END IF;
1283
1284 -- POC for Releases ONLY
1285
1286 /* jai_po_cmn_pkg.query_locator_for_release( pr_new.Po_Release_Id, rel_found );*//*commented by rchandan for bug#4479131*/
1287
1288 /*added by rchandan for bug#4479131*/
1289
1290 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_line_locations_all( -- added the hook call for bug 3438863 to customize the code for deciding on India loc Tax defaultation
1291 pr_new.LINE_LOCATION_ID ,
1292 pr_new.PO_HEADER_ID ,
1293 pr_new.PO_LINE_ID ,
1294 pr_new.QUANTITY ,
1295 pr_new.QUANTITY_RECEIVED ,
1296 pr_new.QUANTITY_ACCEPTED ,
1297 pr_new.QUANTITY_REJECTED ,
1298 pr_new.QUANTITY_BILLED ,
1299 pr_new.QUANTITY_CANCELLED ,
1300 pr_new.UNIT_MEAS_LOOKUP_CODE ,
1301 pr_new.PO_RELEASE_ID ,
1302 pr_new.SHIP_TO_LOCATION_ID ,
1303 pr_new.SHIP_VIA_LOOKUP_CODE ,
1304 pr_new.NEED_BY_DATE ,
1305 pr_new.PROMISED_DATE ,
1306 pr_new.LAST_ACCEPT_DATE ,
1307 pr_new.PRICE_OVERRIDE ,
1308 pr_new.ENCUMBERED_FLAG ,
1309 pr_new.ENCUMBERED_DATE ,
1310 pr_new.UNENCUMBERED_QUANTITY ,
1311 pr_new.FOB_LOOKUP_CODE ,
1312 pr_new.FREIGHT_TERMS_LOOKUP_CODE ,
1313 pr_new.TAXABLE_FLAG ,
1314 pr_new.TAX_NAME ,
1315 pr_new.ESTIMATED_TAX_AMOUNT ,
1316 pr_new.FROM_HEADER_ID ,
1317 pr_new.FROM_LINE_ID ,
1318 pr_new.FROM_LINE_LOCATION_ID ,
1319 pr_new.START_DATE ,
1320 pr_new.END_DATE ,
1321 pr_new.LEAD_TIME ,
1322 pr_new.LEAD_TIME_UNIT ,
1323 pr_new.PRICE_DISCOUNT ,
1324 pr_new.TERMS_ID ,
1325 pr_new.APPROVED_FLAG ,
1326 pr_new.APPROVED_DATE ,
1327 pr_new.CLOSED_FLAG ,
1328 pr_new.CANCEL_FLAG ,
1329 pr_new.CANCELLED_BY ,
1330 pr_new.CANCEL_DATE ,
1331 pr_new.CANCEL_REASON ,
1332 pr_new.FIRM_STATUS_LOOKUP_CODE ,
1333 pr_new.FIRM_DATE ,
1334 pr_new.ATTRIBUTE_CATEGORY ,
1335 pr_new.ATTRIBUTE1 ,
1336 pr_new.ATTRIBUTE2 ,
1337 pr_new.ATTRIBUTE3 ,
1338 pr_new.ATTRIBUTE4 ,
1339 pr_new.ATTRIBUTE5 ,
1340 pr_new.ATTRIBUTE6 ,
1341 pr_new.ATTRIBUTE7 ,
1342 pr_new.ATTRIBUTE8 ,
1343 pr_new.ATTRIBUTE9 ,
1344 pr_new.ATTRIBUTE10 ,
1345 pr_new.UNIT_OF_MEASURE_CLASS ,
1346 pr_new.ENCUMBER_NOW ,
1347 pr_new.ATTRIBUTE11 ,
1348 pr_new.ATTRIBUTE12 ,
1349 pr_new.ATTRIBUTE13 ,
1350 pr_new.ATTRIBUTE14 ,
1351 pr_new.ATTRIBUTE15 ,
1352 pr_new.INSPECTION_REQUIRED_FLAG ,
1353 pr_new.RECEIPT_REQUIRED_FLAG ,
1354 pr_new.QTY_RCV_TOLERANCE ,
1355 pr_new.QTY_RCV_EXCEPTION_CODE ,
1356 pr_new.ENFORCE_SHIP_TO_LOCATION_CODE ,
1357 pr_new.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
1358 pr_new.DAYS_EARLY_RECEIPT_ALLOWED ,
1359 pr_new.DAYS_LATE_RECEIPT_ALLOWED ,
1360 pr_new.RECEIPT_DAYS_EXCEPTION_CODE ,
1361 pr_new.INVOICE_CLOSE_TOLERANCE ,
1362 pr_new.RECEIVE_CLOSE_TOLERANCE ,
1363 pr_new.SHIP_TO_ORGANIZATION_ID ,
1364 pr_new.SHIPMENT_NUM ,
1365 pr_new.SOURCE_SHIPMENT_ID ,
1366 pr_new.SHIPMENT_TYPE ,
1367 pr_new.CLOSED_CODE ,
1368 pr_new.REQUEST_ID ,
1369 pr_new.PROGRAM_APPLICATION_ID ,
1370 pr_new.PROGRAM_ID ,
1371 pr_new.PROGRAM_UPDATE_DATE ,
1372 pr_new.USSGL_TRANSACTION_CODE ,
1373 pr_new.GOVERNMENT_CONTEXT ,
1374 pr_new.RECEIVING_ROUTING_ID ,
1375 pr_new.ACCRUE_ON_RECEIPT_FLAG ,
1376 pr_new.CLOSED_REASON ,
1377 pr_new.CLOSED_DATE ,
1378 pr_new.CLOSED_BY ,
1379 pr_new.ORG_ID ,
1380 pr_new.QUANTITY_SHIPPED ,
1381 pr_new.COUNTRY_OF_ORIGIN_CODE ,
1382 pr_new.TAX_USER_OVERRIDE_FLAG ,
1383 pr_new.MATCH_OPTION ,
1384 pr_new.TAX_CODE_ID ,
1385 pr_new.CALCULATE_TAX_FLAG ,
1386 pr_new.CHANGE_PROMISED_DATE_REASON
1387 );
1388
1389 IF v_hook_value = 'FALSE' THEN
1390 RETURN;
1391 END IF; /*added by rchandan for bug#4479131*/
1392
1393 -- End of POC
1394
1395 OPEN check_rfq_quot_cur;
1396 FETCH check_rfq_quot_cur INTO
1397 v_type_lookup_code ,
1398 v_quot_class_code ,
1399 v_vendor_id ,
1400 v_vendor_site_id ,
1401 v_curr, v_ship_loc_id
1402 , v_po_rate_type, v_po_rate_date, v_po_rate, -- Vijay Shankar for Bug #3184673
1403 v_style_id; --Added by Sanjikum for Bug#4483042
1404 CLOSE check_rfq_quot_cur;
1405
1406 --code added by Sanjikum for Bug#4483042
1407 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => v_style_id) = FALSE THEN
1408 return;
1409 END IF;
1410
1411 OPEN item_id_cur;
1412 FETCH item_id_cur INTO v_item_id, v_uom_measure;
1413 CLOSE item_id_cur;
1414
1415 -- Get Inventory Organization Id
1416
1417 OPEN fetch_org_id_cur;
1418 FETCH fetch_org_id_cur INTO v_org_id;
1419 CLOSE fetch_org_id_cur;
1420
1421 OPEN fetch_uomcode_cur;
1422 FETCH fetch_uomcode_cur INTO v_uom_code;
1423 CLOSE fetch_uomcode_cur;
1424
1425 OPEN Localizaed_Check_Cur;
1426 FETCH Localizaed_Check_Cur INTO v_exist;
1427 CLOSE Localizaed_Check_Cur;
1428
1429 --Added by Kevin Cheng for Retroactive Price 2008/01/13
1430 --=====================================================
1431 IF pr_new.RETROACTIVE_DATE IS NOT NULL
1432 AND (pr_old.PRICE_OVERRIDE <> pr_new.PRICE_OVERRIDE)
1433 THEN
1434 lv_retro_price_flag := 'Y';
1435 END IF;
1436
1437 IF lv_retro_price_flag = 'Y'
1438 THEN
1439 JAI_RETRO_PRC_PKG.Insert_Price_Changes( pr_old => pr_old
1440 , pr_new => pr_new
1441 , pv_process_flag => lv_process_flag
1442 , pv_process_message => lv_process_message
1443 );
1444
1445 IF lv_process_flag IN ('EE', 'UE')
1446 THEN
1447 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
1448 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_PO_LLA_TRIGGER_PKG.ARU_T1.Err:'||lv_process_message);
1449 app_exception.raise_exception;
1450 END IF;
1451 END IF;
1452 --=====================================================
1453
1454 /*************************************** Part 2 ********************************************************/
1455 /************************** Processing For Po Summary Cancellation *************************************/
1456 /*******************************************************************************************************/
1457 /*
1458 This code has been added by aiyer for the fix of the bug 3037284
1459 This piece of code introduces a new functionality into this trigger.
1460
1461 */
1462 -- Start of code 3037284
1463 IF nvl(pr_old.cancel_flag,'N') <> 'Y' AND nvl(pr_new.cancel_flag,'N') = 'Y' THEN
1464 IF nvl(v_quantity_cancelled,-9999) = nvl(v_qty,0) THEN
1465 /*
1466 Indicating that the entire line has been cancelled and no receipt has been made for ths line,
1467 then delete the line and associated taxes from JAI_PO_LINE_LOCATIONS
1468 and JAI_PO_TAXES and return .
1469 */
1470 DELETE
1471 JAI_PO_TAXES
1472 WHERE
1473 line_location_id = v_line_loc_id AND
1474 po_line_id = v_po_line_id AND
1475 po_header_id = v_po_hdr_id;
1476
1477 DELETE
1478 JAI_PO_LINE_LOCATIONS
1479 WHERE
1480 po_line_id = v_po_line_id AND
1481 po_header_id = v_po_hdr_id AND
1482 line_location_id = v_line_loc_id;
1483
1484 ELSIF nvl(v_quantity_cancelled,-9999) < nvl(v_qty,0) THEN
1485 /*
1486 Indicating that a partial receipt has been made for the line and then the line has been cancelled.
1487 In such a case the cancelled quantity would be lesser than the quantity in table po_line_locations_all.
1488 Now in this scenario update the line and the associated apportioned taxes in the table JAI_PO_LINE_LOCATIONS
1489 and JAI_PO_TAXES
1490 */
1491
1492 /*
1493 Update the JAI_PO_TAXES with the apportioned tax_amount
1494 The tax amounts are apportioned in a ratio of the (quantity_received\quantity)
1495 Only the taxes which have a adhoc flag set to 'N' can be apportioned.
1496 Taxes which have adhoc flag set to 'Y' would remain unaffected .
1497
1498 */
1499 FOR cur_rec_get_tax_amount IN rec_get_tax_amount
1500 LOOP
1501
1502 -- following IF commented by Vijay Shankar for Bug #3184673
1503 -- IF cur_rec_get_tax_amount.adhoc_flag = 'N' THEN
1504
1505 UPDATE
1506 JAI_PO_TAXES
1507 SET
1508 tax_amount = (nvl(v_quantity_received,0) / nvl(v_qty,1)) * nvl(cur_rec_get_tax_amount.tax_amount,0)
1509 WHERE
1510 CURRENT OF rec_get_tax_amount;
1511
1512 -- END IF;
1513 END LOOP;
1514
1515 /*
1516 The record in ja_in_po_line_location has to be updated with the total of all taxes from JAI_PO_TAXES
1517 excluding the TDS type of taxes. The total_amount should be calculated as (quantity_received * price_override) + total of tax amount
1518 */
1519
1520 OPEN rec_calc_total_tax ;
1521 FETCH rec_calc_total_tax INTO l_total_tax_amount;
1522 CLOSE rec_calc_total_tax;
1523
1524 UPDATE
1525 JAI_PO_LINE_LOCATIONS
1526 SET
1527 tax_amount = l_total_tax_amount ,
1528 total_amount = nvl(pr_new.quantity_received * pr_new.price_override, 0) + nvl(l_total_tax_amount,0)
1529 WHERE
1530 po_line_id = v_po_line_id AND
1531 po_header_id = v_po_hdr_id AND
1532 line_location_id = v_line_loc_id ;
1533
1534
1535 END IF; /* End if of nvl(v_quantity_cancelled,-9999) = nvl(v_qty,0 */
1536
1537 -- Exit the trigger
1538 RETURN;
1539 END IF; /* End if of nvl(pr_old.cancel_flag,'N') <> 'Y' AND nvl(pr_new.cancel_flag,'N') = 'Y' */
1540 -- End of code 3037284
1541
1542 /*************************************** Part 3 ********************************************************/
1543 /************************** Processing Based on Shipment type values ***********************************/
1544 /*******************************************************************************************************/
1545
1546
1547 IF v_shipment_type NOT IN ( 'SCHEDULED', 'BLANKET' ) THEN
1548
1549 /*IF v_type_lookup_code IN ( 'RFQ', 'QUOTATION' ) THEN
1550 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINRFQQ', found );
1551 ELSIF v_type_lookup_code IN ( 'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) and v_exist <> 1 THEN
1552 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINPO', found );
1553 END IF;
1554
1555 IF NOT found THEN
1556 RETURN;
1557 END IF;*//*commented by rchandan for bug#4479131*/
1558 IF v_type_lookup_code IN ( 'RFQ', 'QUOTATION', 'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) THEN
1559 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_line_locations_all( -- added the hook call for bug 3438863 to customize the code for deciding on India loc Tax defaultation
1560 pr_new.LINE_LOCATION_ID ,
1561 pr_new.PO_HEADER_ID ,
1562 pr_new.PO_LINE_ID ,
1563 pr_new.QUANTITY ,
1564 pr_new.QUANTITY_RECEIVED ,
1565 pr_new.QUANTITY_ACCEPTED ,
1566 pr_new.QUANTITY_REJECTED ,
1567 pr_new.QUANTITY_BILLED ,
1568 pr_new.QUANTITY_CANCELLED ,
1569 pr_new.UNIT_MEAS_LOOKUP_CODE ,
1570 pr_new.PO_RELEASE_ID ,
1571 pr_new.SHIP_TO_LOCATION_ID ,
1572 pr_new.SHIP_VIA_LOOKUP_CODE ,
1573 pr_new.NEED_BY_DATE ,
1574 pr_new.PROMISED_DATE ,
1575 pr_new.LAST_ACCEPT_DATE ,
1576 pr_new.PRICE_OVERRIDE ,
1577 pr_new.ENCUMBERED_FLAG ,
1578 pr_new.ENCUMBERED_DATE ,
1579 pr_new.UNENCUMBERED_QUANTITY ,
1580 pr_new.FOB_LOOKUP_CODE ,
1581 pr_new.FREIGHT_TERMS_LOOKUP_CODE ,
1582 pr_new.TAXABLE_FLAG ,
1583 pr_new.TAX_NAME ,
1584 pr_new.ESTIMATED_TAX_AMOUNT ,
1585 pr_new.FROM_HEADER_ID ,
1586 pr_new.FROM_LINE_ID ,
1587 pr_new.FROM_LINE_LOCATION_ID ,
1588 pr_new.START_DATE ,
1589 pr_new.END_DATE ,
1590 pr_new.LEAD_TIME ,
1591 pr_new.LEAD_TIME_UNIT ,
1592 pr_new.PRICE_DISCOUNT ,
1593 pr_new.TERMS_ID ,
1594 pr_new.APPROVED_FLAG ,
1595 pr_new.APPROVED_DATE ,
1596 pr_new.CLOSED_FLAG ,
1597 pr_new.CANCEL_FLAG ,
1598 pr_new.CANCELLED_BY ,
1599 pr_new.CANCEL_DATE ,
1600 pr_new.CANCEL_REASON ,
1601 pr_new.FIRM_STATUS_LOOKUP_CODE ,
1602 pr_new.FIRM_DATE ,
1603 pr_new.ATTRIBUTE_CATEGORY ,
1604 pr_new.ATTRIBUTE1 ,
1605 pr_new.ATTRIBUTE2 ,
1606 pr_new.ATTRIBUTE3 ,
1607 pr_new.ATTRIBUTE4 ,
1608 pr_new.ATTRIBUTE5 ,
1609 pr_new.ATTRIBUTE6 ,
1610 pr_new.ATTRIBUTE7 ,
1611 pr_new.ATTRIBUTE8 ,
1612 pr_new.ATTRIBUTE9 ,
1613 pr_new.ATTRIBUTE10 ,
1614 pr_new.UNIT_OF_MEASURE_CLASS ,
1615 pr_new.ENCUMBER_NOW ,
1616 pr_new.ATTRIBUTE11 ,
1617 pr_new.ATTRIBUTE12 ,
1618 pr_new.ATTRIBUTE13 ,
1619 pr_new.ATTRIBUTE14 ,
1620 pr_new.ATTRIBUTE15 ,
1621 pr_new.INSPECTION_REQUIRED_FLAG ,
1622 pr_new.RECEIPT_REQUIRED_FLAG ,
1623 pr_new.QTY_RCV_TOLERANCE ,
1624 pr_new.QTY_RCV_EXCEPTION_CODE ,
1625 pr_new.ENFORCE_SHIP_TO_LOCATION_CODE ,
1626 pr_new.ALLOW_SUBSTITUTE_RECEIPTS_FLAG ,
1627 pr_new.DAYS_EARLY_RECEIPT_ALLOWED ,
1628 pr_new.DAYS_LATE_RECEIPT_ALLOWED ,
1629 pr_new.RECEIPT_DAYS_EXCEPTION_CODE ,
1630 pr_new.INVOICE_CLOSE_TOLERANCE ,
1631 pr_new.RECEIVE_CLOSE_TOLERANCE ,
1632 pr_new.SHIP_TO_ORGANIZATION_ID ,
1633 pr_new.SHIPMENT_NUM ,
1634 pr_new.SOURCE_SHIPMENT_ID ,
1635 pr_new.SHIPMENT_TYPE ,
1636 pr_new.CLOSED_CODE ,
1637 pr_new.REQUEST_ID ,
1638 pr_new.PROGRAM_APPLICATION_ID ,
1639 pr_new.PROGRAM_ID ,
1640 pr_new.PROGRAM_UPDATE_DATE ,
1641 pr_new.USSGL_TRANSACTION_CODE ,
1642 pr_new.GOVERNMENT_CONTEXT ,
1643 pr_new.RECEIVING_ROUTING_ID ,
1644 pr_new.ACCRUE_ON_RECEIPT_FLAG ,
1645 pr_new.CLOSED_REASON ,
1646 pr_new.CLOSED_DATE ,
1647 pr_new.CLOSED_BY ,
1648 pr_new.ORG_ID ,
1649 pr_new.QUANTITY_SHIPPED ,
1650 pr_new.COUNTRY_OF_ORIGIN_CODE ,
1651 pr_new.TAX_USER_OVERRIDE_FLAG ,
1652 pr_new.MATCH_OPTION ,
1653 pr_new.TAX_CODE_ID ,
1654 pr_new.CALCULATE_TAX_FLAG ,
1655 pr_new.CHANGE_PROMISED_DATE_REASON
1656 );
1657
1658 IF v_hook_value = 'FALSE' THEN
1659 RETURN;
1660 END IF; /*added by rchandan for bug#4479131*/
1661 END IF;
1662
1663 ELSIF v_shipment_type IN ( 'SCHEDULED', 'BLANKET' ) AND rel_found <> TRUE THEN
1664
1665 --RETURN; commented for #6137011
1666 null;
1667
1668 ELSE
1669
1670 -- v_org_id := pr_new.Org_Id;
1671 IF v_shipment_type = 'BLANKET' THEN
1672 v_type_lookup_code := v_shipment_type || 'R' ;
1673 ELSE
1674 v_type_lookup_code := v_shipment_type;
1675 END IF;
1676
1677 END IF;
1678
1679
1680
1681 /*************************************** Part 4 ********************************************************/
1682 /************************** Processing When Tax Modifiable flag is 'Y' *********************************/
1683 /*******************************************************************************************************/
1684
1685
1686 OPEN Tax_Modified_Cur;
1687 FETCH Tax_Modified_Cur INTO v_flag;
1688 CLOSE Tax_Modified_Cur;
1689
1690 IF v_flag = 'Y' THEN
1691 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value(
1692 v_vendor_id ,
1693 v_vendor_site_id ,
1694 v_item_id ,
1695 v_uom_code
1696 );
1697 IF NVL( v_assessable_value, 0 ) > 0 THEN
1698 -- The Assessable value is greater than 0
1699 v_assessable_value := v_assessable_value * v_qty;
1700 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, v_assessable_value, v_curr, v_curr_conv_factor );
1701 ELSE
1702 v_assessable_value := v_price*v_qty;
1703 END IF;
1704
1705 --added, Harshita for bug #4245062
1706 ln_vat_assess_value :=
1707 jai_general_pkg.ja_in_vat_assessable_value
1708 ( p_party_id => v_vendor_id,
1709 p_party_site_id => v_vendor_site_id,
1710 p_inventory_item_id => v_item_id,
1711 p_uom_code => v_uom_code,
1712 p_default_price => v_price,
1713 p_ass_value_date => trunc(SYSDATE),
1714 p_party_type => 'V'
1715 ) ;
1716
1717
1718 IF ln_vat_assess_value <> v_price THEN
1719 ln_vat_assess_value := ln_vat_assess_value * v_qty ;
1720 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, ln_vat_assess_value, v_curr, v_curr_conv_factor );
1721 ELSE
1722 ln_vat_assess_value := ln_vat_assess_value * v_qty ;
1723 END IF ;
1724
1725 --ended, Harshita for bug #4245062
1726
1727 -- Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
1728 ---------------------------------------------------------------------------
1729 /* for bug 16013918 by anupgupt
1730 ln_gst_assessable_value :=
1731 jai_gst_general_pkg.get_gst_assessable_value
1732 ( p_party_id => v_vendor_id,
1733 p_party_site_id => v_vendor_site_id,
1734 p_inventory_item_id => v_item_id,
1735 p_uom_code => v_uom_code,
1736 p_default_price => v_price,
1737 p_ass_value_date => trunc(SYSDATE),
1738 p_party_type => 'V'
1739 ) ;
1740
1741
1742 IF ln_gst_assessable_value <> v_price THEN
1743 ln_gst_assessable_value := ln_gst_assessable_value * v_qty ;
1744 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, ln_gst_assessable_value, v_curr, v_curr_conv_factor );
1745 ELSE
1746 ln_gst_assessable_value := ln_gst_assessable_value * v_qty ;
1747 END IF ;
1748 */
1749 ---------------------------------------------------------------------------
1750 -- Added by Jia for GST Bug#10043656 on 2010/09/10, End
1751
1752 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, dummy, v_curr, v_curr_conv_factor );
1753
1754 -- Start, Vijay Shankar for Bug #3184673
1755 IF v_old_qty IS NULL OR v_old_qty = 0 THEN
1756 v_old_qty := 1;
1757 END IF;
1758
1759 UPDATE JAI_PO_TAXES a
1760 SET tax_amount = (tax_amount * pr_new.quantity/ v_old_qty ),
1761 tax_target_amount = (tax_target_amount * pr_new.quantity/ v_old_qty)
1762 WHERE line_location_id = v_line_loc_id
1763 AND EXISTS (select 1 from JAI_CMN_TAXES_ALL b where b.tax_id = a.tax_id and b.adhoc_flag = 'Y');
1764
1765 IF v_curr <> v_currency_code THEN
1766 v_currency_conv_rate := jai_cmn_utils_pkg.currency_conversion(v_gl_set_of_bks_id,
1767 v_curr, v_po_rate_date, v_po_rate_type, v_po_rate);
1768 ELSE
1769 v_currency_conv_rate := 1;
1770 END IF;
1771
1772 jai_po_tax_pkg.calc_tax(
1773 p_type => 'STANDARDPO',
1774 p_header_id => v_po_hdr_id,
1775 P_line_id => v_po_line_id,
1776 p_line_location_id => v_line_loc_id,
1777 p_line_focus_id => null,
1778 p_line_quantity => v_qty,
1779 p_base_value => v_price*v_qty,
1780 p_line_uom_code => v_uom_code,
1781 p_tax_amount => P_TAX_AMOUNT,
1782 p_assessable_value => v_assessable_value,
1783 p_vat_assess_value => ln_vat_assess_value, -- added, Harshita for bug #4245062
1784 p_item_id => v_item_id,
1785 p_conv_rate => v_currency_conv_rate,
1786 p_po_curr => v_curr,
1787 p_func_curr => v_currency_code
1788 , pv_retroprice_changed => lv_retro_price_flag --Added by Kevin Cheng for Retroactive Price 2008/01/13
1789 /* for bug 16013918 by anupgupt
1790 , pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10043656 on 2010/09/10
1791 */
1792 );
1793 -- End, Vijay Shankar for Bug #3184673
1794
1795 /* commented by Vijay Shankar for Bug #3184673
1796 jai_po_tax_pkg.calculate_tax( 'STANDARDPO', v_po_hdr_id, v_po_line_id, v_line_loc_id, v_qty,
1797 v_price*v_qty, v_uom_code, P_TAX_AMOUNT, v_assessable_value,
1798 NULL, v_curr_conv_factor
1799 );
1800 */
1801
1802 -- SELECT 'END_JA_IN_PO_TAX_UPDATE_TRG_1.3' INTO v_trigger_name FROM DUAL;
1803
1804 RETURN;
1805 END IF;
1806
1807
1808 /*************************************** Part 5 ********************************************************/
1809 /************************** Processing When Tax Modifiable flag is 'N' *********************************/
1810 /*******************************************************************************************************/
1811
1812
1813 IF v_flag = 'N' OR retcode = FALSE THEN
1814
1815 DELETE FROM JAI_PO_TAXES
1816 WHERE Po_Line_Id = v_po_line_id
1817 AND NVL( Line_Location_Id, - 999 ) = NVL( v_line_loc_id, -999 );
1818
1819 DELETE FROM JAI_PO_LINE_LOCATIONS
1820 WHERE Po_Line_Id = v_po_line_id
1821 AND NVL( Line_Location_Id, - 999 ) = NVL( v_line_loc_id, -999 );
1822
1823 x := v_line_loc_id;
1824 y := v_po_line_id;
1825
1826 IF v_type_lookup_code = 'QUOTATION' AND v_quot_class_code = 'CATALOG' THEN
1827 x := v_from_line_loc_id;
1828 y := v_from_line_id;
1829 END IF;
1830
1831 IF v_shipment_type IN ( 'SCHEDULED', 'BLANKET' ) THEN
1832
1833 jai_po_cmn_pkg.process_release_shipment ( v_shipment_type,
1834 v_src_ship_id,
1835 v_line_loc_id,
1836 v_po_line_id,
1837 v_po_hdr_id,
1838 v_qty,
1839 v_po_rel_id,
1840 v_cre_dt,
1841 v_cre_by,
1842 v_last_upd_dt,
1843 v_last_upd_by,
1844 v_last_upd_login,
1845 'I'
1846 ,lv_retro_price_flag --Added by Kevin Cheng for Retroactive Price 2008/01/13
1847 );
1848
1849 RETURN;
1850 ELSE
1851
1852 jai_po_cmn_pkg.insert_line( 'CATALOG', v_line_loc_id, v_po_hdr_id, v_po_line_id, v_cre_dt,
1853 v_cre_by,
1854 v_last_upd_dt,
1855 v_last_upd_by,
1856 v_last_upd_login,
1857 'I' );
1858
1859 jai_po_tax_pkg.Ja_In_Po_Case1( v_type_lookup_code,
1860 v_quot_class_code,
1861 v_vendor_id,
1862 v_vendor_site_id,
1863 v_curr,
1864 v_org_id,
1865 v_item_id,
1866 v_uom_measure,
1867 v_line_loc_id,
1868 v_po_hdr_id,
1869 v_po_line_id,
1870 y,
1871 x,
1872 v_price,
1873 v_Qty,
1874 v_cre_dt,
1875 v_cre_by,
1876 v_last_upd_dt,
1877 v_last_upd_by,
1878 v_last_upd_login,
1879 'I',
1880 success );
1881
1882 IF success <> 0 THEN
1883 jai_po_tax_pkg.Ja_In_Po_Case2 ( v_type_lookup_code,
1884 v_quot_class_code,
1885 v_vendor_id,
1886 v_vendor_site_id,
1887 v_curr,
1888 v_org_id,
1889 v_item_id,
1890 v_line_loc_id,
1891 v_po_hdr_id,
1892 v_po_line_id,
1893 v_price,
1894 v_qty,
1895 v_cre_dt,
1896 v_cre_by,
1897 v_last_upd_dt,
1898 v_last_upd_by,
1899 v_last_upd_login,
1900 v_uom_measure,
1901 NULL,
1902 null,
1903 ln_vat_assess_value, -- added, Harshita for bug #4245062
1904 null, /*following four parameters added for bug 8224547*/
1905 null,
1906 null,
1907 null,
1908 null,
1909 lv_retro_price_flag --Added by Kevin Cheng for Retroactive Price 2008/01/13
1910 /* for bug 16013918 by anupgupt
1911 , pn_gst_assessable_value => ln_gst_assessable_value ); -- Added by Jia for GST Bug#10043656 on 2010/09/10
1912 */
1913 );
1914
1915 END IF;
1916 END IF;
1917
1918 ELSE
1919
1920 IF v_line_loc_id IS NOT NULL THEN
1921 OPEN Fetch_Sum_Cur;
1922 FETCH Fetch_Sum_Cur INTO v_tax_amt;
1923 CLOSE Fetch_Sum_Cur;
1924 ELSE
1925 OPEN Fetch_Sum1_Cur;
1926 FETCH Fetch_Sum1_Cur INTO v_tax_amt;
1927 CLOSE Fetch_Sum1_Cur;
1928 END IF;
1929
1930 IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN
1931 v_tax_amt := NULL;
1932 v_tot_amt := NULL;
1933 ELSE
1934 v_tot_amt := v_tax_amt + ( v_qty * v_price );
1935 END IF;
1936
1937 UPDATE JAI_PO_LINE_LOCATIONS
1938 SET Tax_Amount = v_tax_amt,
1939 Total_Amount = v_tot_amt,
1940 Last_Updated_By = v_last_upd_by,
1941 Last_Update_Date = v_last_upd_dt,
1942 Last_Update_Login = v_last_upd_login
1943 WHERE Po_Line_Id = v_po_line_id
1944 AND Line_Location_Id = v_line_loc_id;
1945 END IF;
1946
1947 -- SELECT 'END_JA_IN_PO_TAX_UPDATE_TRG_FINAL' INTO v_trigger_name FROM DUAL;
1948
1949
1950 END ARU_T1 ;
1951
1952 END JAI_PO_LLA_TRIGGER_PKG ;