[Home] [Help]
PACKAGE BODY: APPS.JAI_PO_LA_TRIGGER_PKG
Source
1 PACKAGE BODY JAI_PO_LA_TRIGGER_PKG AS
2 /* $Header: jai_po_la_t.plb 120.1.12000000.2 2007/10/25 02:23:12 rallamse ship $ */
3
4 /* REM +======================================================================+
5 REM NAME ARD_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_PO_LA_ARIUD_T1
8 REM
9 REM NOTES Refers to old trigger JAI_PO_LA_ARD_T2
10 REM
11 REM +======================================================================+
12 */
13 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
14 ------------- added by Gsr 12-jul-01
15 v_operating_id number; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
16 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
17 v_currency_code gl_sets_of_books.currency_code%type;
18
19 /* Bug 5243532. Added by Lakshmi Gopalsami
20 * Removed cursor Fetch_Book_Id_Cur
21 * and implemented using caching logic.
22 */
23
24 CURSOR Sob_Cur is
25 select Currency_code
26 from gl_sets_of_books
27 where set_of_books_id = v_gl_set_of_bks_id;
28 ------ End of addition by Gsri on 12-jul-01
29
30
31 v_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_old.Po_Line_Id;
32
33 dummy NUMBER;
34
35
36 CURSOR Check_Llid_Cur IS SELECT COUNT( Line_Location_Id )
37 FROM JAI_PO_TAXES
38 WHERE Po_Line_Id = v_po_line_id;
39
40 /* Bug 5243532. Added by Lakshmi Gopalsami
41 * Defined variable for implementing caching logic.
42 */
43 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
44 BEGIN
45 pv_return_code := jai_constants.successful ;
46 /*------------------------------------------------------------------------------------------
47 FILENAME: Ja_In_Po_Lines_Tax_Delete_Trg.sql
48
49 CHANGE HISTORY:
50 S.No Date Author and Details
51 1 29-Nov-2004 Sanjikum for 4035297. Version 115.1
52 Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
53
54 Dependency Due to this Bug:-
55 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
56
57 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
58 DB Entity as requiredfor CASE COMPLAINCE. Version 116.1
59
60 3. 13-Jun-2005 File Version: 116.2
61 Ramananda for bug#4428980. Removal of SQL LITERALs is done
62
63 Future Dependencies For the release Of this Object:-
64 (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/
65 A datamodel change )
66 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
67 Current Version Current Bug Dependent Files Version Author Date Remarks
68 Of File On Bug/Patchset Dependent On
69
70 Ja_In_Po_Lines_Tax_Delete_Trg.sql
71 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
72 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
73 ja_in_util_pkg_s.sql 115.0 Sanjikum
74
75 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
76
77 --File.Sql.35 Cbabu
78 v_operating_id :=pr_new.ORG_ID;
79 v_po_line_id := pr_old.Po_Line_Id;
80 /* Bug 5243532. Added by Lakshmi Gopalsami
81 REmoved the cursor Fetch_Book_Id_Cur and implemented the same using
82 caching logic.
83 */
84 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
85 (p_org_id => v_operating_id);
86 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
87
88 --IF jai_cmn_utils_pkg.check_jai_exists( p_calling_object => 'JA_IN_PO_LINES_TAX_DELETE_TRG',
89 -- p_set_of_books_id => v_gl_set_of_bks_id ) = FALSE THEN
90 -- RETURN;`
91 --END IF;
92
93
94 DELETE FROM JAI_PO_LINE_LOCATIONS
95 WHERE Po_Line_Id = v_po_line_id;
96
97 OPEN Check_Llid_Cur;
98 FETCH Check_Llid_Cur INTO dummy;
99 CLOSE Check_Llid_Cur;
100
101 IF NVL( dummy, 0 ) > 0 THEN
102 DELETE FROM JAI_PO_TAXES
103 WHERE Po_Line_Id = v_po_line_id;
104 END IF;
105 END ARD_T1 ;
106
107 /*
108 REM +======================================================================+
109 REM NAME ARI_T1
110 REM
111 REM DESCRIPTION Called from trigger JAI_PO_LA_ARIUD_T1
112 REM
113 REM NOTES Refers to old trigger JAI_PO_LA_ARI_T1
114 REM
115 REM +======================================================================+
116 */
117 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
118 v_org_id NUMBER; -- := pr_new.Org_Id;
119 v_type_lookup_code VARCHAR2(10);
120 v_quot_class_code VARCHAR2(25);
121 v_vendor_id NUMBER;
122 v_vendor_site_id NUMBER;
123 v_curr VARCHAR2(15);
124 v_ship_loc_id NUMBER;
125
126 v_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Line_Id ;
127 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
128 v_frm_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.From_Line_Id;
129 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
130 v_cre_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Created_By;
131 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
132 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
133 v_last_upd_login NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
134 v_uom_measure VARCHAR2(25); --File.Sql.35 Cbabu := pr_new.Unit_Meas_Lookup_Code;
135 success NUMBER; --File.Sql.35 Cbabu := 1;
136
137 v_item_id NUMBER ;--File.Sql.35 Cbabu := pr_new.Item_Id;
138 v_tax_ctg_id NUMBER;
139 v_uom_code VARCHAR2(3);
140
141 -- found BOOLEAN;
142 v_currency_code gl_sets_of_books.currency_code%type; --added by Gsr and Sriram on 21-03-2001
143
144 -- Vijay Shankar for Bug# 3466223
145 v_from_header_id NUMBER;
146 v_from_type_lookup_code PO_HEADERS_ALL.from_type_lookup_code%TYPE;
147 result BOOLEAN;
148 req_id NUMBER;
149
150 CURSOR Check_Rfq_Quot_Cur IS
151 SELECT Type_Lookup_Code, Quotation_Class_Code, Vendor_id,
152 Vendor_Site_Id, Currency_Code, Ship_To_Location_Id
153 , from_header_id, from_type_lookup_code -- Vijay Shankar for Bug# 3466223
154 FROM Po_Headers_All
155 WHERE Po_Header_Id = v_po_hdr_id;
156
157 -- Get the Inventory Organization Id
158 CURSOR Fetch_Org_Id_Cur IS
159 SELECT Inventory_Organization_Id
160 FROM Hr_Locations
161 WHERE Location_Id = v_ship_loc_id;
162
163 -- Vijay Shankar for Bug# 3184418
164 CURSOR c_tax_modified_flag(p_po_line_id IN NUMBER) IS
165 SELECT tax_modified_flag
166 FROM JAI_PO_LINE_LOCATIONS
167 WHERE po_line_id = p_po_line_id
168 AND line_location_id IS NULL;
169
170 v_tax_modified_flag CHAR(1);
171 v_inv_org_id NUMBER;
172
173 -- Vijay Shankar for bugs# 3570189
174 v_hook_value VARCHAR2(10); --File.Sql.35 Cbabu := 'TRUE';
175 BEGIN
176 pv_return_code := jai_constants.successful ;
177 /*------------------------------------------------------------------------------------------
178 Change history:
179 S.No Date Author and Details
180 --------------------------------------------------------------------------------------------
181 1 16/12/2003 Vijay Shankar(cbabu) for Bug# 3184418, Fileversion: 618.1
182 code modified to take care of the following issues
183 - If RFQ is autocreated from Requisition, then taxes if present in Requisition should default
184 onto the RFQ without check for Record in JAI_CMN_LOCATORS_T table. (this is to verify whether the user
185 has navigated through Localization form or not). With this fix, taxes will get defaulted onto RFQ if
186 the INR check is passed and required SETUPs are done
187 - For a Quotation, code is modified to copy taxes from source document if taxes are in source document
188 are modified, otherwise it will default the taxes onto quotation line (i.e line_location_id = NULL) via
189 defaulting logic as per setups
190
191 2 20/02/2004 Nagaraj.s for bug 3438863. , Fileversion: 618.2
192 Hook Functionality is incorporated by calling the package
193 jai_cmn_hook_pkg.sql
194 Hence this is a certain dependency issue and should be carefully handled
195
196 3 14/04/2004 Vijay Shankar for bugs# 3570189 and 3553351, Version : 619.1
197 BUG# 3570189: PO Hook Functionality is made compatible with 11.5.3 Base Applications by removing last 11 parameters in call to
198 jai_cmn_hook_pkg.Ja_In_po_lines_all procedure
199 Removed Locator related code and the defaultation happens only base on HOOK Implemenation by Ct. By Default tax
200 defaultation happens for all documents created with INR as functional currency
201
202 BUG# 3553351: Taxes are not getting defaulted for BPA and success is made 0 after returning from
203 jai_po_tax_pkg.Ja_In_Po_Case1 procedure. This is rectified by commenting the line success = 0
204
205 FileVersion: 618.2 is obsoleted with this Version
206 This is a DEPENDANCY for later versions of the file
207
208 4 15/04/2004 Vijay Shankar for Bug# 3466223, FileVersion# 619.2
209 Code is added to Submit Request for Conc. Prog. JAINCPDC to default taxes for BPA lines when created from a
210 Source Document eg. Quotation
211
212 5. 29/Nov/2004 Aiyer for bug#4035566. Version#115.1
213 Issue:-
214 The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
215 where transaction is being done.
216
217 Fix:-
218 Function jai_cmn_utils_pkg.check_jai_exists is being called which returns the TRUE if the currency is INR and FALSE if the currency is
219 NON-INR
220 Also removed the two cursors Fetch_Book_Id_Cur and Sob_cur and variables v_gl_set_of_bks_id and v_currency_code
221
222 Dependency Due to this Bug:-
223 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0. introduced through the bug 4033992
224
225 6. 19-Mar-05 rallamse for bug#4227171 Version#115.2
226 Remove automatic GSCC errors
227
228 7. 19-Mar-05 rallamse for bug#4250072 Version#115.3
229 Added P_VAT_ASSESS_VALUE as argument to jai_po_tax_pkg.Ja_In_Po_Case2
230
231 8. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
232 DB Entity as required for CASE COMPLAINCE. Version 116.1
233
234 9. 13-Jun-2005 File Version: 116.2
235 Ramananda for bug#4428980. Removal of SQL LITERALs is done
236
237 Future Dependencies For the release Of this Object:-
238 (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/
239 A datamodel change )
240 --------------------------------------------------------------------------------------------------------------------------------------
241 Current Version Current Bug Dependent Files Version Author Date Remarks
242 Of File On Bug/Patchset Dependent On
243 ja_in_po_lines_set_locator_trg
244 --------------------------------------------------------------------------------------------------------------------------------------
245 618.2 (Obsolete) 3438863 IN60105D2
246
247 619.1 3570189 IN60105D2 + 3438863
248
249 115.1 4035566 IN60105D2 + ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
250 3438863 + ja_in_util_pkg_b.sql 115.0
251 3570189 +
252 4033992
253
254 115.2 4250072 IN60106 +
255 4035566 + jai_po_tax_pkg.Ja_In_Po_Case2
256 4245089
257
258 --------------------------------------------------------------------------------------------------------------------------------------*/
259
260 --File.Sql.35 Cbabu
261 v_po_line_id := pr_new.Po_Line_Id ;
262 v_po_hdr_id := pr_new.Po_Header_Id;
263 v_frm_po_line_id := pr_new.From_Line_Id;
264 v_cre_dt := pr_new.Creation_Date;
265 v_cre_by := pr_new.Created_By;
266 v_last_upd_dt := pr_new.Last_Update_Date ;
267 v_last_upd_by := pr_new.Last_Updated_By;
268 v_last_upd_login := pr_new.Last_Update_Login;
269 v_uom_measure := pr_new.Unit_Meas_Lookup_Code;
270 success := 1;
271 v_item_id := pr_new.Item_Id;
272 v_hook_value := 'TRUE';
273
274 /*
275 || Code added by aiyer for the bug 4035566
276 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
277 */
278 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_PO_LINES_SET_LOCATOR_TRG' ,
279 -- p_org_id => pr_new.org_id
280 -- ) = FALSE
281 --THEN
282 /*
283 || return as the current set of books is NON-INR based
284 */
285 -- RETURN;
286 -- END IF;
287
288
289 -- Start, Vijay Shankar for bugs# 3570189
290 -- If v_hook_value is TRUE, then it means taxes should be defaulted. IF FALSE then return
291 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_lines_all(
292 pr_new.PO_LINE_ID ,
293 pr_new.PO_HEADER_ID ,
294 pr_new.LINE_TYPE_ID ,
295 pr_new.LINE_NUM ,
296 pr_new.ITEM_ID ,
297 pr_new.ITEM_REVISION ,
298 pr_new.CATEGORY_ID ,
299 pr_new.ITEM_DESCRIPTION ,
300 pr_new.UNIT_MEAS_LOOKUP_CODE ,
301 pr_new.QUANTITY_COMMITTED ,
302 pr_new.COMMITTED_AMOUNT ,
303 pr_new.ALLOW_PRICE_OVERRIDE_FLAG ,
304 pr_new.NOT_TO_EXCEED_PRICE ,
305 pr_new.LIST_PRICE_PER_UNIT ,
306 pr_new.UNIT_PRICE ,
307 pr_new.QUANTITY ,
308 pr_new.UN_NUMBER_ID ,
309 pr_new.HAZARD_CLASS_ID ,
310 pr_new.NOTE_TO_VENDOR ,
311 pr_new.FROM_HEADER_ID ,
312 pr_new.FROM_LINE_ID ,
313 pr_new.MIN_ORDER_QUANTITY ,
314 pr_new.MAX_ORDER_QUANTITY ,
315 pr_new.QTY_RCV_TOLERANCE ,
316 pr_new.OVER_TOLERANCE_ERROR_FLAG ,
317 pr_new.MARKET_PRICE ,
318 pr_new.UNORDERED_FLAG ,
319 pr_new.CLOSED_FLAG ,
320 pr_new.USER_HOLD_FLAG ,
321 pr_new.CANCEL_FLAG ,
322 pr_new.CANCELLED_BY ,
326 pr_new.FIRM_DATE ,
323 pr_new.CANCEL_DATE ,
324 pr_new.CANCEL_REASON ,
325 pr_new.FIRM_STATUS_LOOKUP_CODE ,
327 pr_new.VENDOR_PRODUCT_NUM ,
328 pr_new.CONTRACT_NUM ,
329 pr_new.TAXABLE_FLAG ,
330 pr_new.TAX_NAME ,
331 pr_new.TYPE_1099 ,
332 pr_new.CAPITAL_EXPENSE_FLAG ,
333 pr_new.NEGOTIATED_BY_PREPARER_FLAG ,
334 pr_new.ATTRIBUTE_CATEGORY ,
335 pr_new.ATTRIBUTE1 ,
336 pr_new.ATTRIBUTE2 ,
337 pr_new.ATTRIBUTE3 ,
338 pr_new.ATTRIBUTE4 ,
339 pr_new.ATTRIBUTE5 ,
340 pr_new.ATTRIBUTE6 ,
341 pr_new.ATTRIBUTE7 ,
342 pr_new.ATTRIBUTE8 ,
343 pr_new.ATTRIBUTE9 ,
344 pr_new.ATTRIBUTE10 ,
345 pr_new.REFERENCE_NUM ,
346 pr_new.ATTRIBUTE11 ,
347 pr_new.ATTRIBUTE12 ,
348 pr_new.ATTRIBUTE13 ,
349 pr_new.ATTRIBUTE14 ,
350 pr_new.ATTRIBUTE15 ,
351 pr_new.MIN_RELEASE_AMOUNT ,
352 pr_new.PRICE_TYPE_LOOKUP_CODE ,
353 pr_new.CLOSED_CODE ,
354 pr_new.PRICE_BREAK_LOOKUP_CODE ,
355 pr_new.USSGL_TRANSACTION_CODE ,
356 pr_new.GOVERNMENT_CONTEXT ,
357 pr_new.REQUEST_ID ,
358 pr_new.PROGRAM_APPLICATION_ID ,
359 pr_new.PROGRAM_ID ,
360 pr_new.PROGRAM_UPDATE_DATE ,
361 pr_new.CLOSED_DATE ,
362 pr_new.CLOSED_REASON ,
363 pr_new.CLOSED_BY ,
364 pr_new.TRANSACTION_REASON_CODE ,
365 pr_new.ORG_ID ,
366 pr_new.QC_GRADE ,
367 pr_new.BASE_UOM ,
368 pr_new.BASE_QTY ,
369 pr_new.SECONDARY_UOM ,
370 pr_new.SECONDARY_QTY ,
371 pr_new.LINE_REFERENCE_NUM ,
372 pr_new.PROJECT_ID ,
373 pr_new.TASK_ID ,
374 pr_new.EXPIRATION_DATE ,
375 pr_new.TAX_CODE_ID
376 );
377
378 IF v_hook_value = 'FALSE' THEN
379 RETURN;
380 END IF;
381 -- End, Vijay Shankar for bugs# 3570189
382
383 OPEN Check_Rfq_Quot_Cur;
384 FETCH Check_Rfq_Quot_Cur INTO v_type_lookup_code, v_quot_Class_Code, v_vendor_id,
385 v_vendor_site_id, v_curr, v_ship_loc_id
386 , v_from_header_id, v_from_type_lookup_code; -- Vijay Shankar for Bug# 3466223
387 CLOSE Check_Rfq_Quot_Cur;
388
389 OPEN Fetch_Org_Id_cur;
390 FETCH Fetch_Org_Id_cur INTO v_org_id;
391 CLOSE Fetch_Org_Id_cur;
392
393 IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN
394
395 -- Start, Vijay Shankar for Bug# 3466223
396 IF v_type_lookup_code = 'BLANKET' AND v_from_header_id IS NOT NULL THEN
397
398 Insert into JAI_PO_COPYDOC_T(
399 TYPE, PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, LINE_NUM,
400 SHIPMENT_NUM, ITEM_ID, FROM_HEADER_ID, FROM_TYPE_LOOKUP_CODE,
401 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
402 ) Values (
403 'L', v_po_hdr_id, v_po_line_id, NULL, pr_new.line_num,
404 NULL, v_item_id, v_from_header_id, v_from_type_lookup_code,
405 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
406 );
407
408 result := Fnd_Request.Set_Mode( TRUE );
409
410 req_id := Fnd_Request.Submit_Request('JA', 'JAINCPDC',
411 'Copy Document India Localization.', SYSDATE, FALSE,
412 'L', v_po_hdr_id, v_po_line_id, NULL,
413 pr_new.line_num, NULL, v_item_id,
414 v_from_header_id, v_from_type_lookup_code,
415 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login);
416
417 RETURN;
418
419 END IF;
420 -- End, Vijay Shankar for Bug# 3466223
421
422 -- Start, Vijay Shankar for Bug# 3184418
423 -- this is for Quotation
424 IF v_frm_po_line_id IS NOT NULL AND v_quot_class_code = 'CATALOG' THEN
425 OPEN c_tax_modified_flag(v_frm_po_line_id);
426 FETCH c_tax_modified_flag INTO v_tax_modified_flag;
427 CLOSE c_tax_modified_flag;
428
429 IF v_tax_modified_flag IS NULL THEN
430 v_tax_modified_flag := 'N';
431 END IF;
432 ELSE
433 v_tax_modified_flag := 'N';
434 END IF;
435
436 -- this call is moved here which is previously called after the check (IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN)
437 jai_po_cmn_pkg.insert_line( 'CATALOG', NULL,
438 v_po_hdr_id, v_po_line_id, v_cre_dt,
439 v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I'
440 );
441
442 IF ( v_tax_modified_flag = 'Y' AND v_quot_class_code = 'CATALOG') OR v_type_lookup_code = 'BLANKET' THEN
443 -- End, Vijay Shankar for Bug# 3184418
444
445 jai_po_tax_pkg.Ja_In_Po_Case1(
446 v_type_lookup_code, v_quot_class_code, v_vendor_id, v_vendor_site_id,
447 v_curr, v_org_id, v_item_id, v_uom_measure, NULL,
448 v_po_hdr_id, v_po_line_id, v_frm_po_line_id, NULL, NULL, NULL,
449 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I', success
450 );
451
452 -- Vijay Shankar for bugs# 3570189, 3553351
453 -- success := 0;
454 -- Vijay Shankar for Bug# 3184418
455 ELSE
456 success := 1;
457 END IF;
458
459 IF success <> 0 THEN
460
461 jai_po_tax_pkg.Ja_In_Po_Case2 (
462 v_type_lookup_code, v_quot_class_code, v_vendor_id, v_vendor_site_id,
463 v_curr, v_org_id, v_item_id, NULL,
464 v_po_hdr_id, v_po_line_id, NULL, NULL, v_cre_dt, v_cre_by,
465 v_last_upd_dt, v_last_upd_by, v_last_upd_login, v_uom_measure, FLAG => 'INSLINES',P_VAT_ASSESS_VALUE => NULL /* Added p_vat_assess_value by rallamse bug#4250072 VAT */
466 );
467
468 END IF;
469
470 END IF;
471 END ARI_T1 ;
472
473 /*
474 REM +======================================================================+
475 REM NAME ARU_T1
476 REM
477 REM DESCRIPTION Called from trigger JAI_PO_LA_ARIUD_T1
478 REM
479 REM NOTES Refers to old trigger JAI_PO_LA_ARU_T3
480 REM
481 REM +======================================================================+
482 */
483 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
484 ------------- added by Gsr 12-jul-01
485 v_operating_id number;--File.Sql.35 Cbabu :=pr_new.ORG_ID;
486 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
487 v_currency_code gl_sets_of_books.currency_code%type;
488
489 /* Bug 5243532. Added by Lakshmi Gopalsami
490 * Removed cursor Fetch_Book_Id_Cur
491 * and implemented using caching logic.
492 */
493
494 CURSOR Sob_Cur is
495 select Currency_code
496 from gl_sets_of_books
497 where set_of_books_id = v_gl_set_of_bks_id;
498 ------ End of addition by Gsri on 12-jul-01
499
500 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
501 v_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Line_Id;
502 v_line_loc_id NUMBER;
503 v_line_amt NUMBER;
504 v_org_id NUMBER; -- := pr_new.Org_Id;
505 v_vendor_site_id NUMBER;
506 v_Type_Lookup_Code VARCHAR2(25);
507 v_Quot_Class_Code VARCHAR2(25);
508 v_vendor_id NUMBER;
509 v_reqn_entries NUMBER;
510 v_requisition_line_id NUMBER;
511 v_curr VARCHAR2(3);
512 v_ship_loc_id NUMBER;
513 v_t_flag VARCHAR2(1);
514
515 v_n_price NUMBER; --File.Sql.35 Cbabu := pr_new.Unit_Price;
516 v_price NUMBER;
517 v_qty NUMBER;
518 v_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.Item_Id;
519 v_line_uom VARCHAR2(25); --File.Sql.35 Cbabu := pr_new.Unit_Meas_Lookup_Code;
520 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
521 v_cre_by NUMBER; --File.Sql.35 Cbabu := pr_new.Created_By;
522 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
523 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
524 v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
525 v_uom_measure VARCHAR2(25);
526 v_hook_value VARCHAR2(10);/*added by rchandan for bug#4479131*/
527
528 success NUMBER; --File.Sql.35 Cbabu := 1;
529
530 /*v_rowid JAI_CMN_LOCATORS_T.Row_Id%TYPE;*//*commented by rchandan for bug#4479131*/
531 found BOOLEAN;
532 v_style_id po_headers_all.style_id%TYPE;--Added by Sanjikum for Bug#4483042
533
534 /* Bug 5243532. Added by Lakshmi Gopalsami
535 * Defined variable for implementing caching logic.
536 */
537 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
538
539 ------------------------------------------------------------------------------------
540
541 CURSOR POC_Cur IS SELECT Rowid
542 FROM Po_Headers_All
543 WHERE Po_Header_Id = v_po_hdr_id;
544
545 CURSOR Check_Rfq_Quot_Cur IS SELECT Type_Lookup_Code, Quotation_Class_Code, Vendor_id,
546 Vendor_Site_Id, Currency_Code, Ship_To_Location_Id,
547 style_id --Added by Sanjikum for Bug#4483042
548 FROM Po_Headers_All
549 WHERE Po_Header_Id = v_po_hdr_id;
550
551 -- Get the Inventory Organization Id
552
553 CURSOR Fetch_Org_Id_Cur IS SELECT Inventory_Organization_Id
554 FROM Hr_Locations
555 WHERE Location_Id = v_ship_loc_id;
556
557 ------------------------------------------------------------------------------------
558
559
560 CURSOR Fetch_Lines_Cur IS SELECT Line_Location_Id, Price_Override, Quantity, Unit_Meas_Lookup_Code
561 FROM Po_Line_Locations_All
562 WHERE Po_Line_Id = v_po_line_id;
563
564 CURSOR Fetch_Flag_Cur( llid IN NUMBER ) IS SELECT NVL( Tax_Modified_Flag, 'N' ) Tax_Modified_Flag
565 FROM JAI_PO_LINE_LOCATIONS
566 WHERE Line_Location_Id = llid
567 AND Po_Line_Id = v_po_line_id;
568 BEGIN
569 pv_return_code := jai_constants.successful ;
570 /*------------------------------------------------------------------------------------------
571 FILENAME: Ja_In_Po_Lines_Tax_Update_Trg.sql
572
573 CHANGE HISTORY:
574 S.No Date Author and Details
575 1 29-Nov-2004 Sanjikum for 4035297. Version 115.1
576 Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
577
578 2. 19-Mar-05 rallamse for bug#4227171 Version#115.2
579 Remove automatic GSCC errors
580
581 3. 19-Mar-05 rallamse for bug#4250072 Version#115.3
582 Changes for VAT
583
584 Dependency Due to this Bug:-
585 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
586
587 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
588 as required for CASE COMPLAINCE. Version 116.1
589
590 5. 13-Jun-2005 File Version: 116.2
591 Ramananda for bug#4428980. Removal of SQL LITERALs is done
592
593 6. 08-Jul-2005 Sanjikum for Bug#4483042.File Version: 116.3
594 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
595 IL functionality should work or not.
596
597 7. 8-Jul-2005 File Version: 116.3
598 rchandan for bug#4479131
599 The object is modified to eliminate the paddr usage.
600
601 8. 12-Jul-2005 Sanjikum for Bug#4483042.File Version: 117.2
602 1) Added a new parameter in cursor - Check_Rfq_Quot_Cur
603
604
605
606 Future Dependencies For the release Of this Object:-
607 (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/
608 A datamodel change )
609 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
610 Current Version Current Bug Dependent Files Version Author Date Remarks
611 Of File On Bug/Patchset Dependent On
612
613 Ja_In_Po_Lines_Tax_Update_Trg.sql
614 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
615 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
616 ja_in_util_pkg_s.sql 115.0 Sanjikum
617
618 115.3 4250072 IN60106 +
619 4035297 +
620 4245089
621
622 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
623
624 --File.Sql.35 Cbabu
625 v_operating_id :=pr_new.ORG_ID;
626 v_po_hdr_id := pr_new.Po_Header_Id;
627 v_po_line_id := pr_new.Po_Line_Id;
628 v_n_price := pr_new.Unit_Price;
629 v_item_id := pr_new.Item_Id;
630 v_line_uom := pr_new.Unit_Meas_Lookup_Code;
631 v_cre_dt := pr_new.Creation_Date;
632 v_cre_by := pr_new.Created_By;
633 v_last_upd_dt := pr_new.Last_Update_Date ;
634 v_last_upd_by := pr_new.Last_Updated_By;
635 v_last_upd_login := pr_new.Last_Update_Login;
636 success := 1;
637
638 /* Bug 5243532. Added by Lakshmi Gopalsami
639 * Removed cursor Fetch_Book_Id_Cur
640 * and implemented using caching logic.
641 */
642
643 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
644 (p_org_id => v_operating_id);
645 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
646
647
648 --IF jai_cmn_utils_pkg.check_jai_exists(p_calling_object => 'JA_IN_PO_LINES_TAX_UPDATE_TRG',
649 -- p_set_of_books_id => v_gl_set_of_bks_id) = FALSE THEN
650 -- RETURN;
651 -- END IF;
652
653
654 OPEN Check_Rfq_Quot_Cur;
655 FETCH Check_Rfq_Quot_Cur INTO v_Type_Lookup_Code, v_Quot_Class_Code, v_vendor_id,
656 v_vendor_site_id, v_curr, v_ship_loc_id,
657 v_style_id; --Added by Sanjikum for Bug#4483042
658 CLOSE Check_Rfq_Quot_Cur;
659
660 --code added by Sanjikum for Bug#4483042
661 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => v_style_id) = FALSE THEN
662 return;
663 END IF;
667 /* IF v_type_lookup_code IN ( 'RFQ', 'QUOTATION' ) THEN
664
665 -- POC
666
668 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINRFQQ', found );
669 ELSIF v_type_lookup_code IN ( 'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) THEN
670 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINPO', found );
671 END IF;*//*commented by rchandan for bug#4479131*/
672 IF v_type_lookup_code IN ( 'RFQ', 'QUOTATION' ,'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) THEN
673 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_lines_all (
674 P_PO_LINE_ID =>pr_new.PO_LINE_ID ,
675 P_PO_HEADER_ID =>pr_new.PO_HEADER_ID ,
676 P_LINE_TYPE_ID =>pr_new.LINE_TYPE_ID ,
677 P_LINE_NUM =>pr_new.LINE_NUM ,
678 P_ITEM_ID =>pr_new.ITEM_ID ,
679 P_ITEM_REVISION =>pr_new.ITEM_REVISION ,
680 P_CATEGORY_ID =>pr_new.CATEGORY_ID ,
681 P_ITEM_DESCRIPTION =>pr_new.ITEM_DESCRIPTION ,
682 P_UNIT_MEAS_LOOKUP_CODE =>pr_new.UNIT_MEAS_LOOKUP_CODE ,
683 P_QUANTITY_COMMITTED =>pr_new.QUANTITY_COMMITTED ,
684 P_COMMITTED_AMOUNT =>pr_new.COMMITTED_AMOUNT ,
685 P_ALLOW_PRICE_OVERRIDE_FLAG =>pr_new.ALLOW_PRICE_OVERRIDE_FLAG ,
686 P_NOT_TO_EXCEED_PRICE =>pr_new.NOT_TO_EXCEED_PRICE ,
687 P_LIST_PRICE_PER_UNIT =>pr_new.LIST_PRICE_PER_UNIT ,
688 P_UNIT_PRICE =>pr_new.UNIT_PRICE ,
689 P_QUANTITY =>pr_new.QUANTITY ,
690 P_UN_NUMBER_ID =>pr_new.UN_NUMBER_ID ,
691 P_HAZARD_CLASS_ID =>pr_new.HAZARD_CLASS_ID ,
692 P_NOTE_TO_VENDOR =>pr_new.NOTE_TO_VENDOR ,
693 P_FROM_HEADER_ID =>pr_new.FROM_HEADER_ID ,
694 P_FROM_LINE_ID =>pr_new.FROM_LINE_ID ,
695 P_MIN_ORDER_QUANTITY =>pr_new.MIN_ORDER_QUANTITY ,
696 P_MAX_ORDER_QUANTITY =>pr_new.MAX_ORDER_QUANTITY ,
697 P_QTY_RCV_TOLERANCE =>pr_new.QTY_RCV_TOLERANCE ,
698 P_OVER_TOLERANCE_ERROR_FLAG =>pr_new.OVER_TOLERANCE_ERROR_FLAG ,
699 P_MARKET_PRICE =>pr_new.MARKET_PRICE ,
700 P_UNORDERED_FLAG =>pr_new.UNORDERED_FLAG ,
701 P_CLOSED_FLAG =>pr_new.CLOSED_FLAG ,
702 P_USER_HOLD_FLAG =>pr_new.USER_HOLD_FLAG ,
703 P_CANCEL_FLAG =>pr_new.CANCEL_FLAG ,
704 P_CANCELLED_BY =>pr_new.CANCELLED_BY ,
705 P_CANCEL_DATE =>pr_new.CANCEL_DATE ,
706 P_CANCEL_REASON =>pr_new.CANCEL_REASON ,
707 P_FIRM_STATUS_LOOKUP_CODE =>pr_new.FIRM_STATUS_LOOKUP_CODE ,
708 P_FIRM_DATE =>pr_new.FIRM_DATE ,
709 P_VENDOR_PRODUCT_NUM =>pr_new.VENDOR_PRODUCT_NUM ,
710 P_CONTRACT_NUM =>pr_new.CONTRACT_NUM ,
711 P_TAXABLE_FLAG =>pr_new.TAXABLE_FLAG ,
712 P_TAX_NAME =>pr_new.TAX_NAME ,
713 P_TYPE_1099 =>pr_new.TYPE_1099 ,
714 P_CAPITAL_EXPENSE_FLAG =>pr_new.CAPITAL_EXPENSE_FLAG ,
715 P_NEGOTIATED_BY_PREPARER_FLAG =>pr_new.NEGOTIATED_BY_PREPARER_FLAG ,
716 P_ATTRIBUTE_CATEGORY =>pr_new.ATTRIBUTE_CATEGORY ,
717 P_ATTRIBUTE1 =>pr_new.ATTRIBUTE1 ,
718 P_ATTRIBUTE2 =>pr_new.ATTRIBUTE2 ,
719 P_ATTRIBUTE3 =>pr_new.ATTRIBUTE3 ,
720 P_ATTRIBUTE4 =>pr_new.ATTRIBUTE4 ,
721 P_ATTRIBUTE5 =>pr_new.ATTRIBUTE5 ,
722 P_ATTRIBUTE6 =>pr_new.ATTRIBUTE6 ,
723 P_ATTRIBUTE7 =>pr_new.ATTRIBUTE7 ,
724 P_ATTRIBUTE8 =>pr_new.ATTRIBUTE8 ,
725 P_ATTRIBUTE9 =>pr_new.ATTRIBUTE9 ,
726 P_ATTRIBUTE10 =>pr_new.ATTRIBUTE10 ,
727 P_REFERENCE_NUM =>pr_new.REFERENCE_NUM ,
728 P_ATTRIBUTE11 =>pr_new.ATTRIBUTE11 ,
729 P_ATTRIBUTE12 =>pr_new.ATTRIBUTE12 ,
730 P_ATTRIBUTE13 =>pr_new.ATTRIBUTE13 ,
731 P_ATTRIBUTE14 =>pr_new.ATTRIBUTE14 ,
732 P_ATTRIBUTE15 =>pr_new.ATTRIBUTE15 ,
733 P_MIN_RELEASE_AMOUNT =>pr_new.MIN_RELEASE_AMOUNT ,
734 P_PRICE_TYPE_LOOKUP_CODE =>pr_new.PRICE_TYPE_LOOKUP_CODE ,
735 P_CLOSED_CODE =>pr_new.CLOSED_CODE ,
736 P_PRICE_BREAK_LOOKUP_CODE =>pr_new.PRICE_BREAK_LOOKUP_CODE ,
737 P_USSGL_TRANSACTION_CODE =>pr_new.USSGL_TRANSACTION_CODE ,
741 P_PROGRAM_ID =>pr_new.PROGRAM_ID ,
738 P_GOVERNMENT_CONTEXT =>pr_new.GOVERNMENT_CONTEXT ,
739 P_REQUEST_ID =>pr_new.REQUEST_ID ,
740 P_PROGRAM_APPLICATION_ID =>pr_new.PROGRAM_APPLICATION_ID ,
742 P_PROGRAM_UPDATE_DATE =>pr_new.PROGRAM_UPDATE_DATE ,
743 P_CLOSED_DATE =>pr_new.CLOSED_DATE ,
744 P_CLOSED_REASON =>pr_new.CLOSED_REASON ,
745 P_CLOSED_BY =>pr_new.CLOSED_BY ,
746 P_TRANSACTION_REASON_CODE =>pr_new.TRANSACTION_REASON_CODE ,
747 P_ORG_ID =>pr_new.ORG_ID ,
748 P_QC_GRADE =>pr_new.QC_GRADE ,
749 P_BASE_UOM =>pr_new.BASE_UOM ,
750 P_BASE_QTY =>pr_new.BASE_QTY ,
751 P_SECONDARY_UOM =>pr_new.SECONDARY_UOM ,
752 P_SECONDARY_QTY =>pr_new.SECONDARY_QTY ,
753 P_LINE_REFERENCE_NUM =>pr_new.LINE_REFERENCE_NUM ,
754 P_PROJECT_ID =>pr_new.PROJECT_ID ,
755 P_TASK_ID =>pr_new.TASK_ID ,
756 P_EXPIRATION_DATE =>pr_new.EXPIRATION_DATE ,
757 P_TAX_CODE_ID =>pr_new.TAX_CODE_ID
758 );
759
760 END IF;/*added by rchandan for bug#4479131*/
761
762 -- End Of POC
763
764 IF v_hook_value = 'FALSE' THEN
765 RETURN;
766 END IF;
767
768 -- Get Inventory Organization Id
769
770 OPEN Fetch_Org_Id_Cur;
771 FETCH Fetch_Org_Id_Cur INTO v_org_id;
772 CLOSE Fetch_Org_Id_Cur;
773
774 -- Continue, only if the Item is changed !
775
776 IF pr_old.Item_id <> pr_new.Item_id THEN
777
778 OPEN Fetch_Lines_Cur;
779 LOOP
780 FETCH Fetch_Lines_Cur INTO v_line_loc_id, v_price, v_qty, v_uom_measure;
781 EXIT WHEN Fetch_Lines_Cur%NOTFOUND;
782
783 IF v_type_lookup_code NOT IN ( 'RFQ', 'QUOTATION' ) THEN
784 v_price := v_n_price;
785 END IF;
786
787 OPEN Fetch_Flag_Cur( v_line_loc_id );
788 LOOP
789 FETCH Fetch_Flag_Cur INTO v_t_flag;
790 EXIT WHEN Fetch_Flag_Cur%NOTFOUND;
791 IF UPPER( v_t_flag ) = 'N' THEN
792
793 DELETE FROM JAI_PO_TAXES
794 WHERE Line_Location_Id = v_line_loc_id;
795
796 jai_po_cmn_pkg.insert_line( 'CATALOG',
797 v_line_loc_id,
798 v_po_hdr_id,
799 v_po_line_id,
800 v_cre_dt,
801 v_cre_by,
802 v_last_upd_dt,
803 v_last_upd_by,
804 v_last_upd_login,
805 'U' );
806
807 jai_po_tax_pkg.Ja_In_Po_Case1( v_type_lookup_code,
808 v_quot_class_code,
809 v_vendor_id,
810 v_vendor_site_id,
811 v_curr,
812 v_org_id,
813 v_item_id,
814 v_line_uom,
815 v_line_loc_id,
816 v_po_hdr_id,
817 v_po_line_id,
818 v_po_line_id,
819 v_line_loc_id,
820 v_price,
821 v_Qty,
822 v_cre_dt,
823 v_cre_by,
824 v_last_upd_dt,
825 v_last_upd_by,
826 v_last_upd_login,
827 'U',
828 success );
829 END IF;
830
831 IF ( success <> 0 OR v_t_flag = 'Y' ) THEN
832
833 jai_po_tax_pkg.Ja_In_Po_Case2 ( v_type_lookup_code,
834 v_quot_class_code,
835 v_vendor_id,
836 v_vendor_site_id,
837 v_curr,
838 v_org_id,
839 v_item_id,
840 v_line_loc_id,
841 v_po_hdr_id,
842 v_po_line_id,
843 v_price,
844 v_Qty,
845 v_cre_dt,
846 v_cre_by,
847 v_last_upd_dt,
848 v_last_upd_by,
849 v_last_upd_login,
850 v_uom_measure,
851 NULL,
852 P_VAT_ASSESS_VALUE => NULL /* Added p_vat_assess_value by rallamse bug#4250072 VAT */
853 );
854 END IF;
855 END LOOP;
856 CLOSE Fetch_Flag_Cur;
857 END LOOP;
858 CLOSE Fetch_Lines_Cur;
859 END IF;
860 END ARU_T1 ;
861
862 END JAI_PO_LA_TRIGGER_PKG ;