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