[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.9.12020000.2 2012/12/18 10:20:41 anupgupt 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 10. 10-Sep-2010 Jia for GST Bug#10091373.
238
239 11. 18-Dec-2012 for bug 16013918 by anupgupt
240 Removed GST changes
241
242 Future Dependencies For the release Of this Object:-
243 (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/
244 A datamodel change )
245 --------------------------------------------------------------------------------------------------------------------------------------
246 Current Version Current Bug Dependent Files Version Author Date Remarks
247 Of File On Bug/Patchset Dependent On
248 ja_in_po_lines_set_locator_trg
249 --------------------------------------------------------------------------------------------------------------------------------------
250 618.2 (Obsolete) 3438863 IN60105D2
251
252 619.1 3570189 IN60105D2 + 3438863
253
254 115.1 4035566 IN60105D2 + ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
255 3438863 + ja_in_util_pkg_b.sql 115.0
256 3570189 +
257 4033992
258
259 115.2 4250072 IN60106 +
260 4035566 + jai_po_tax_pkg.Ja_In_Po_Case2
261 4245089
262
263 --------------------------------------------------------------------------------------------------------------------------------------*/
264
265 --File.Sql.35 Cbabu
266 v_po_line_id := pr_new.Po_Line_Id ;
267 v_po_hdr_id := pr_new.Po_Header_Id;
268 v_frm_po_line_id := pr_new.From_Line_Id;
269 v_cre_dt := pr_new.Creation_Date;
270 v_cre_by := pr_new.Created_By;
271 v_last_upd_dt := pr_new.Last_Update_Date ;
272 v_last_upd_by := pr_new.Last_Updated_By;
273 v_last_upd_login := pr_new.Last_Update_Login;
274 v_uom_measure := pr_new.Unit_Meas_Lookup_Code;
275 success := 1;
276 v_item_id := pr_new.Item_Id;
277 v_hook_value := 'TRUE';
278
279 /*
280 || Code added by aiyer for the bug 4035566
281 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
282 */
283 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_PO_LINES_SET_LOCATOR_TRG' ,
284 -- p_org_id => pr_new.org_id
285 -- ) = FALSE
286 --THEN
287 /*
288 || return as the current set of books is NON-INR based
289 */
290 -- RETURN;
291 -- END IF;
292
293
294 -- Start, Vijay Shankar for bugs# 3570189
295 -- If v_hook_value is TRUE, then it means taxes should be defaulted. IF FALSE then return
296 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_lines_all(
297 pr_new.PO_LINE_ID ,
298 pr_new.PO_HEADER_ID ,
299 pr_new.LINE_TYPE_ID ,
300 pr_new.LINE_NUM ,
301 pr_new.ITEM_ID ,
302 pr_new.ITEM_REVISION ,
303 pr_new.CATEGORY_ID ,
304 pr_new.ITEM_DESCRIPTION ,
305 pr_new.UNIT_MEAS_LOOKUP_CODE ,
306 pr_new.QUANTITY_COMMITTED ,
307 pr_new.COMMITTED_AMOUNT ,
308 pr_new.ALLOW_PRICE_OVERRIDE_FLAG ,
309 pr_new.NOT_TO_EXCEED_PRICE ,
310 pr_new.LIST_PRICE_PER_UNIT ,
311 pr_new.UNIT_PRICE ,
312 pr_new.QUANTITY ,
313 pr_new.UN_NUMBER_ID ,
314 pr_new.HAZARD_CLASS_ID ,
315 pr_new.NOTE_TO_VENDOR ,
316 pr_new.FROM_HEADER_ID ,
317 pr_new.FROM_LINE_ID ,
318 pr_new.MIN_ORDER_QUANTITY ,
319 pr_new.MAX_ORDER_QUANTITY ,
320 pr_new.QTY_RCV_TOLERANCE ,
321 pr_new.OVER_TOLERANCE_ERROR_FLAG ,
322 pr_new.MARKET_PRICE ,
323 pr_new.UNORDERED_FLAG ,
324 pr_new.CLOSED_FLAG ,
325 pr_new.USER_HOLD_FLAG ,
326 pr_new.CANCEL_FLAG ,
327 pr_new.CANCELLED_BY ,
328 pr_new.CANCEL_DATE ,
329 pr_new.CANCEL_REASON ,
330 pr_new.FIRM_STATUS_LOOKUP_CODE ,
331 pr_new.FIRM_DATE ,
332 pr_new.VENDOR_PRODUCT_NUM ,
333 pr_new.CONTRACT_NUM ,
334 pr_new.TAXABLE_FLAG ,
335 pr_new.TAX_NAME ,
336 pr_new.TYPE_1099 ,
337 pr_new.CAPITAL_EXPENSE_FLAG ,
338 pr_new.NEGOTIATED_BY_PREPARER_FLAG ,
339 pr_new.ATTRIBUTE_CATEGORY ,
340 pr_new.ATTRIBUTE1 ,
341 pr_new.ATTRIBUTE2 ,
342 pr_new.ATTRIBUTE3 ,
343 pr_new.ATTRIBUTE4 ,
344 pr_new.ATTRIBUTE5 ,
345 pr_new.ATTRIBUTE6 ,
346 pr_new.ATTRIBUTE7 ,
347 pr_new.ATTRIBUTE8 ,
348 pr_new.ATTRIBUTE9 ,
349 pr_new.ATTRIBUTE10 ,
350 pr_new.REFERENCE_NUM ,
351 pr_new.ATTRIBUTE11 ,
352 pr_new.ATTRIBUTE12 ,
353 pr_new.ATTRIBUTE13 ,
354 pr_new.ATTRIBUTE14 ,
355 pr_new.ATTRIBUTE15 ,
356 pr_new.MIN_RELEASE_AMOUNT ,
357 pr_new.PRICE_TYPE_LOOKUP_CODE ,
358 pr_new.CLOSED_CODE ,
359 pr_new.PRICE_BREAK_LOOKUP_CODE ,
360 pr_new.USSGL_TRANSACTION_CODE ,
361 pr_new.GOVERNMENT_CONTEXT ,
362 pr_new.REQUEST_ID ,
363 pr_new.PROGRAM_APPLICATION_ID ,
364 pr_new.PROGRAM_ID ,
365 pr_new.PROGRAM_UPDATE_DATE ,
366 pr_new.CLOSED_DATE ,
367 pr_new.CLOSED_REASON ,
368 pr_new.CLOSED_BY ,
369 pr_new.TRANSACTION_REASON_CODE ,
370 pr_new.ORG_ID ,
371 pr_new.QC_GRADE ,
372 pr_new.BASE_UOM ,
373 pr_new.BASE_QTY ,
374 pr_new.SECONDARY_UOM ,
375 pr_new.SECONDARY_QTY ,
376 pr_new.LINE_REFERENCE_NUM ,
377 pr_new.PROJECT_ID ,
378 pr_new.TASK_ID ,
379 pr_new.EXPIRATION_DATE ,
380 pr_new.TAX_CODE_ID
381 );
382
383 IF v_hook_value = 'FALSE' THEN
384 RETURN;
385 END IF;
386 -- End, Vijay Shankar for bugs# 3570189
387
388 OPEN Check_Rfq_Quot_Cur;
389 FETCH Check_Rfq_Quot_Cur INTO v_type_lookup_code, v_quot_Class_Code, v_vendor_id,
390 v_vendor_site_id, v_curr, v_ship_loc_id
391 , v_from_header_id, v_from_type_lookup_code; -- Vijay Shankar for Bug# 3466223
392 CLOSE Check_Rfq_Quot_Cur;
393
394 OPEN Fetch_Org_Id_cur;
395 FETCH Fetch_Org_Id_cur INTO v_org_id;
396 CLOSE Fetch_Org_Id_cur;
397
398 IF v_type_lookup_code = 'BLANKET' OR v_quot_class_code = 'CATALOG' THEN
399
400 -- Start, Vijay Shankar for Bug# 3466223
401 IF v_type_lookup_code = 'BLANKET' AND v_from_header_id IS NOT NULL THEN
402
403 Insert into JAI_PO_COPYDOC_T(
404 TYPE, PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, LINE_NUM,
405 SHIPMENT_NUM, ITEM_ID, FROM_HEADER_ID, FROM_TYPE_LOOKUP_CODE,
406 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
407 ) Values (
408 'L', v_po_hdr_id, v_po_line_id, NULL, pr_new.line_num,
409 NULL, v_item_id, v_from_header_id, v_from_type_lookup_code,
410 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
411 );
412
413 result := Fnd_Request.Set_Mode( TRUE );
414
415 req_id := Fnd_Request.Submit_Request('JA', 'JAINCPDC',
416 'Copy Document India Localization.', SYSDATE, FALSE,
417 'L', v_po_hdr_id, v_po_line_id, NULL,
418 pr_new.line_num, NULL, v_item_id,
419 v_from_header_id, v_from_type_lookup_code,
420 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login);
421
422 RETURN;
423
424 END IF;
425 -- End, Vijay Shankar for Bug# 3466223
426
427 -- Start, Vijay Shankar for Bug# 3184418
428 -- this is for Quotation
429 IF v_frm_po_line_id IS NOT NULL AND v_quot_class_code = 'CATALOG' THEN
430 OPEN c_tax_modified_flag(v_frm_po_line_id);
431 FETCH c_tax_modified_flag INTO v_tax_modified_flag;
432 CLOSE c_tax_modified_flag;
433
434 IF v_tax_modified_flag IS NULL THEN
435 v_tax_modified_flag := 'N';
436 END IF;
437 ELSE
438 v_tax_modified_flag := 'N';
439 END IF;
440
441 -- 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)
442 jai_po_cmn_pkg.insert_line( 'CATALOG', NULL,
443 v_po_hdr_id, v_po_line_id, v_cre_dt,
444 v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I'
445 );
446
447 IF ( v_tax_modified_flag = 'Y' AND v_quot_class_code = 'CATALOG') OR v_type_lookup_code = 'BLANKET' THEN
448 -- End, Vijay Shankar for Bug# 3184418
449
450 jai_po_tax_pkg.Ja_In_Po_Case1(
451 v_type_lookup_code, v_quot_class_code, v_vendor_id, v_vendor_site_id,
452 v_curr, v_org_id, v_item_id, v_uom_measure, NULL,
453 v_po_hdr_id, v_po_line_id, v_frm_po_line_id, NULL, NULL, NULL,
454 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login, 'I', success
455 );
456
457 -- Vijay Shankar for bugs# 3570189, 3553351
458 -- success := 0;
459 -- Vijay Shankar for Bug# 3184418
460 ELSE
461 success := 1;
462 END IF;
463
464 IF success <> 0 THEN
465
466 jai_po_tax_pkg.Ja_In_Po_Case2 (
467 v_type_lookup_code, v_quot_class_code, v_vendor_id, v_vendor_site_id,
468 v_curr, v_org_id, v_item_id, NULL,
469 v_po_hdr_id, v_po_line_id, NULL, NULL, v_cre_dt, v_cre_by,
470 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 */
471 /* for bug 16013918 by anupgupt
472 , pn_gst_assessable_value => NULL -- Added by Jia for GST Bug#10091373 on 2010/09/10
473 */
474 );
475
476 END IF;
477
478 END IF;
479 END ARI_T1 ;
480
481 /*
482 REM +======================================================================+
483 REM NAME ARU_T1
484 REM
485 REM DESCRIPTION Called from trigger JAI_PO_LA_ARIUD_T1
486 REM
487 REM NOTES Refers to old trigger JAI_PO_LA_ARU_T3
488 REM
489 REM +======================================================================+
490 */
491 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
492 ------------- added by Gsr 12-jul-01
493 v_operating_id number;--File.Sql.35 Cbabu :=pr_new.ORG_ID;
494 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
495 v_currency_code gl_sets_of_books.currency_code%type;
496
497 /* Bug 5243532. Added by Lakshmi Gopalsami
498 * Removed cursor Fetch_Book_Id_Cur
499 * and implemented using caching logic.
500 */
501
502 CURSOR Sob_Cur is
503 select Currency_code
504 from gl_sets_of_books
505 where set_of_books_id = v_gl_set_of_bks_id;
506 ------ End of addition by Gsri on 12-jul-01
507
508 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
509 v_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Line_Id;
510 v_line_loc_id NUMBER;
511 v_line_amt NUMBER;
512 v_org_id NUMBER; -- := pr_new.Org_Id;
513 v_vendor_site_id NUMBER;
514 v_Type_Lookup_Code VARCHAR2(25);
515 v_Quot_Class_Code VARCHAR2(25);
516 v_vendor_id NUMBER;
517 v_reqn_entries NUMBER;
518 v_requisition_line_id NUMBER;
519 v_curr VARCHAR2(3);
520 v_ship_loc_id NUMBER;
521 v_t_flag VARCHAR2(1);
522
523 v_n_price NUMBER; --File.Sql.35 Cbabu := pr_new.Unit_Price;
524 v_price NUMBER;
525 v_qty NUMBER;
526 v_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.Item_Id;
527 v_line_uom VARCHAR2(25); --File.Sql.35 Cbabu := pr_new.Unit_Meas_Lookup_Code;
528 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
529 v_cre_by NUMBER; --File.Sql.35 Cbabu := pr_new.Created_By;
530 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
531 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
532 v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
533 v_uom_measure VARCHAR2(25);
534 v_hook_value VARCHAR2(10);/*added by rchandan for bug#4479131*/
535 v_from_header_id NUMBER; /*Added by nprashar for bug # 9362704*/
536 v_from_line_id NUMBER;/*Added by nprashar for bug # 9362704*/
537 v_unit_code VARCHAR2(25); /*Added by nprashar for bug # 9362704*/
538 v_assessable_value NUMBER; /*Added by nprashar for bug # 9362704*/
539
540 success NUMBER; --File.Sql.35 Cbabu := 1;
541
542 /*v_rowid JAI_CMN_LOCATORS_T.Row_Id%TYPE;*//*commented by rchandan for bug#4479131*/
543 found BOOLEAN;
544 v_style_id po_headers_all.style_id%TYPE;--Added by Sanjikum for Bug#4483042
545
546 /* Bug 5243532. Added by Lakshmi Gopalsami
547 * Defined variable for implementing caching logic.
548 */
549 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
550
551 ------------------------------------------------------------------------------------
552
553 CURSOR POC_Cur IS SELECT Rowid
554 FROM Po_Headers_All
555 WHERE Po_Header_Id = v_po_hdr_id;
556
557 CURSOR Check_Rfq_Quot_Cur IS SELECT Type_Lookup_Code, Quotation_Class_Code, Vendor_id,
558 Vendor_Site_Id, Currency_Code, Ship_To_Location_Id,
559 style_id --Added by Sanjikum for Bug#4483042
560 FROM Po_Headers_All
561 WHERE Po_Header_Id = v_po_hdr_id;
562
563 -- Get the Inventory Organization Id
564
565 CURSOR Fetch_Org_Id_Cur IS SELECT Inventory_Organization_Id
566 FROM Hr_Locations
567 WHERE Location_Id = v_ship_loc_id;
568
569 --Removed cursor CURSOR C_Check_Quot_lines /*Added by nprashar for bug # 9362704*/
570 CURSOR Fetch_UOMCode_Cur( v_temp_uom IN VARCHAR2 ) IS
571 SELECT Uom_Code
572 FROM Mtl_Units_Of_Measure
573 WHERE Unit_Of_Measure = v_temp_uom;
574
575
576 ------------------------------------------------------------------------------------
577
578
579 CURSOR Fetch_Lines_Cur IS SELECT Line_Location_Id, Price_Override, Quantity, Unit_Meas_Lookup_Code
580 FROM Po_Line_Locations_All
581 WHERE Po_Line_Id = v_po_line_id;
582
583 CURSOR Fetch_Flag_Cur( llid IN NUMBER ) IS SELECT NVL( Tax_Modified_Flag, 'N' ) Tax_Modified_Flag
584 FROM JAI_PO_LINE_LOCATIONS
585 WHERE Line_Location_Id = llid
586 AND Po_Line_Id = v_po_line_id;
587 BEGIN
588 pv_return_code := jai_constants.successful ;
589 /*------------------------------------------------------------------------------------------
590 FILENAME: Ja_In_Po_Lines_Tax_Update_Trg.sql
591
592 CHANGE HISTORY:
593 S.No Date Author and Details
594 1 29-Nov-2004 Sanjikum for 4035297. Version 115.1
595 Changed the 'INR' check. Added the call to jai_cmn_utils_pkg.check_jai_exists
596
597 2. 19-Mar-05 rallamse for bug#4227171 Version#115.2
598 Remove automatic GSCC errors
599
600 3. 19-Mar-05 rallamse for bug#4250072 Version#115.3
601 Changes for VAT
602
603 Dependency Due to this Bug:-
604 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
605
606 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
607 as required for CASE COMPLAINCE. Version 116.1
608
609 5. 13-Jun-2005 File Version: 116.2
610 Ramananda for bug#4428980. Removal of SQL LITERALs is done
611
612 6. 08-Jul-2005 Sanjikum for Bug#4483042.File Version: 116.3
613 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
614 IL functionality should work or not.
615
616 7. 8-Jul-2005 File Version: 116.3
617 rchandan for bug#4479131
618 The object is modified to eliminate the paddr usage.
619
620 8. 12-Jul-2005 Sanjikum for Bug#4483042.File Version: 117.2
621 1) Added a new parameter in cursor - Check_Rfq_Quot_Cur
622
623 9. 10-Sep-2010 Jia for GST Bug#10091373.
624
625 Future Dependencies For the release Of this Object:-
626 (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/
627 A datamodel change )
628 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
629 Current Version Current Bug Dependent Files Version Author Date Remarks
630 Of File On Bug/Patchset Dependent On
631
632 Ja_In_Po_Lines_Tax_Update_Trg.sql
633 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
634 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
635 ja_in_util_pkg_s.sql 115.0 Sanjikum
636
637 115.3 4250072 IN60106 +
638 4035297 +
639 4245089
640
641 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
642
643 --File.Sql.35 Cbabu
644 v_operating_id :=pr_new.ORG_ID;
645 v_po_hdr_id := pr_new.Po_Header_Id;
646 v_po_line_id := pr_new.Po_Line_Id;
647 v_n_price := pr_new.Unit_Price;
648 v_item_id := pr_new.Item_Id;
649 v_line_uom := pr_new.Unit_Meas_Lookup_Code;
650 v_cre_dt := pr_new.Creation_Date;
651 v_cre_by := pr_new.Created_By;
652 v_last_upd_dt := pr_new.Last_Update_Date ;
653 v_last_upd_by := pr_new.Last_Updated_By;
654 v_last_upd_login := pr_new.Last_Update_Login;
655 v_from_header_id := pr_new.from_header_id;
656 v_from_line_id := pr_new.from_line_id;
657 success := 1;
658
659 /* Bug 5243532. Added by Lakshmi Gopalsami
660 * Removed cursor Fetch_Book_Id_Cur
661 * and implemented using caching logic.
662 */
663
664 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
665 (p_org_id => v_operating_id);
666 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
667
668
669 --IF jai_cmn_utils_pkg.check_jai_exists(p_calling_object => 'JA_IN_PO_LINES_TAX_UPDATE_TRG',
670 -- p_set_of_books_id => v_gl_set_of_bks_id) = FALSE THEN
671 -- RETURN;
672 -- END IF;
673
674
675 OPEN Check_Rfq_Quot_Cur;
676 FETCH Check_Rfq_Quot_Cur INTO v_Type_Lookup_Code, v_Quot_Class_Code, v_vendor_id,
677 v_vendor_site_id, v_curr, v_ship_loc_id,
678 v_style_id; --Added by Sanjikum for Bug#4483042
679 CLOSE Check_Rfq_Quot_Cur;
680
681 --code added by Sanjikum for Bug#4483042
682 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => v_style_id) = FALSE THEN
683 return;
684 END IF;
685
686 -- POC
687
688 /* IF v_type_lookup_code IN ( 'RFQ', 'QUOTATION' ) THEN
689 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINRFQQ', found );
690 ELSIF v_type_lookup_code IN ( 'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) THEN
691 jai_po_cmn_pkg.query_locator_for_line( v_po_hdr_id, 'JAINPO', found );
692 END IF;*//*commented by rchandan for bug#4479131*/
693
694 IF nvl(pr_new.from_header_id,0) <> nvl(pr_old.from_header_id,0) and nvl(pr_new.from_line_id,0) <> nvl(pr_old.from_line_id,0) Then /*Added by nprashar for bug # 9362704*/
695 IF v_type_lookup_code IN ('STANDARD', 'PLANNED') Then /*Added by nprashar for bug # 9362704*/
696 If v_from_header_id is NOT NULL and v_from_line_id is NOT NULL
697 Then
698
699 OPEN Fetch_UOMCode_Cur( v_line_uom);
700 FETCH Fetch_UOMCode_Cur INTO v_unit_code;
701 CLOSE Fetch_UOMCode_Cur;
702
703
704 Open Fetch_Lines_Cur;
705 Fetch Fetch_Lines_Cur into v_line_loc_id, v_price, v_qty, v_uom_measure;
706 Close Fetch_Lines_Cur;
707
708 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value( v_vendor_id, v_vendor_site_id, v_item_id, v_unit_code );
709
710
711 IF v_assessable_value IS NOT NULL AND v_assessable_value > 0 THEN
712 v_assessable_value := v_assessable_value * v_qty;
713 ELSE
714 v_assessable_value := v_qty * v_price;
715 END IF;
716
717
718 INSERT INTO JAI_PO_QUOT_LINES_T (
719 po_header_id, po_line_id, line_location_id, from_header_id,
720 from_line_id, price_override, uom_code, assessable_value,
721 creation_date, created_by, last_update_date, last_updated_by, last_update_login)
722 VALUES (v_po_hdr_id,v_po_line_id,v_line_loc_id,v_from_header_id,v_from_line_id,v_price,v_unit_code,
723 v_assessable_value,v_cre_dt,v_cre_by,v_last_upd_dt,v_last_upd_by,v_last_upd_login);
724
725 End IF;
726 End IF; /*Added by nprashar for bug # 9362704 Ends here*/
727 End IF;
728
729 IF v_type_lookup_code IN ( 'RFQ', 'QUOTATION' ,'STANDARD', 'PLANNED', 'CONTRACT', 'BLANKET' ) THEN
730 v_hook_value := jai_cmn_hook_pkg.Ja_In_po_lines_all (
731 P_PO_LINE_ID =>pr_new.PO_LINE_ID ,
732 P_PO_HEADER_ID =>pr_new.PO_HEADER_ID ,
733 P_LINE_TYPE_ID =>pr_new.LINE_TYPE_ID ,
734 P_LINE_NUM =>pr_new.LINE_NUM ,
735 P_ITEM_ID =>pr_new.ITEM_ID ,
736 P_ITEM_REVISION =>pr_new.ITEM_REVISION ,
737 P_CATEGORY_ID =>pr_new.CATEGORY_ID ,
738 P_ITEM_DESCRIPTION =>pr_new.ITEM_DESCRIPTION ,
739 P_UNIT_MEAS_LOOKUP_CODE =>pr_new.UNIT_MEAS_LOOKUP_CODE ,
740 P_QUANTITY_COMMITTED =>pr_new.QUANTITY_COMMITTED ,
741 P_COMMITTED_AMOUNT =>pr_new.COMMITTED_AMOUNT ,
742 P_ALLOW_PRICE_OVERRIDE_FLAG =>pr_new.ALLOW_PRICE_OVERRIDE_FLAG ,
743 P_NOT_TO_EXCEED_PRICE =>pr_new.NOT_TO_EXCEED_PRICE ,
744 P_LIST_PRICE_PER_UNIT =>pr_new.LIST_PRICE_PER_UNIT ,
745 P_UNIT_PRICE =>pr_new.UNIT_PRICE ,
746 P_QUANTITY =>pr_new.QUANTITY ,
747 P_UN_NUMBER_ID =>pr_new.UN_NUMBER_ID ,
748 P_HAZARD_CLASS_ID =>pr_new.HAZARD_CLASS_ID ,
749 P_NOTE_TO_VENDOR =>pr_new.NOTE_TO_VENDOR ,
750 P_FROM_HEADER_ID =>pr_new.FROM_HEADER_ID ,
751 P_FROM_LINE_ID =>pr_new.FROM_LINE_ID ,
752 P_MIN_ORDER_QUANTITY =>pr_new.MIN_ORDER_QUANTITY ,
753 P_MAX_ORDER_QUANTITY =>pr_new.MAX_ORDER_QUANTITY ,
754 P_QTY_RCV_TOLERANCE =>pr_new.QTY_RCV_TOLERANCE ,
755 P_OVER_TOLERANCE_ERROR_FLAG =>pr_new.OVER_TOLERANCE_ERROR_FLAG ,
756 P_MARKET_PRICE =>pr_new.MARKET_PRICE ,
757 P_UNORDERED_FLAG =>pr_new.UNORDERED_FLAG ,
758 P_CLOSED_FLAG =>pr_new.CLOSED_FLAG ,
759 P_USER_HOLD_FLAG =>pr_new.USER_HOLD_FLAG ,
760 P_CANCEL_FLAG =>pr_new.CANCEL_FLAG ,
761 P_CANCELLED_BY =>pr_new.CANCELLED_BY ,
762 P_CANCEL_DATE =>pr_new.CANCEL_DATE ,
763 P_CANCEL_REASON =>pr_new.CANCEL_REASON ,
764 P_FIRM_STATUS_LOOKUP_CODE =>pr_new.FIRM_STATUS_LOOKUP_CODE ,
765 P_FIRM_DATE =>pr_new.FIRM_DATE ,
766 P_VENDOR_PRODUCT_NUM =>pr_new.VENDOR_PRODUCT_NUM ,
767 P_CONTRACT_NUM =>pr_new.CONTRACT_NUM ,
768 P_TAXABLE_FLAG =>pr_new.TAXABLE_FLAG ,
769 P_TAX_NAME =>pr_new.TAX_NAME ,
770 P_TYPE_1099 =>pr_new.TYPE_1099 ,
771 P_CAPITAL_EXPENSE_FLAG =>pr_new.CAPITAL_EXPENSE_FLAG ,
772 P_NEGOTIATED_BY_PREPARER_FLAG =>pr_new.NEGOTIATED_BY_PREPARER_FLAG ,
773 P_ATTRIBUTE_CATEGORY =>pr_new.ATTRIBUTE_CATEGORY ,
774 P_ATTRIBUTE1 =>pr_new.ATTRIBUTE1 ,
775 P_ATTRIBUTE2 =>pr_new.ATTRIBUTE2 ,
776 P_ATTRIBUTE3 =>pr_new.ATTRIBUTE3 ,
777 P_ATTRIBUTE4 =>pr_new.ATTRIBUTE4 ,
778 P_ATTRIBUTE5 =>pr_new.ATTRIBUTE5 ,
779 P_ATTRIBUTE6 =>pr_new.ATTRIBUTE6 ,
780 P_ATTRIBUTE7 =>pr_new.ATTRIBUTE7 ,
781 P_ATTRIBUTE8 =>pr_new.ATTRIBUTE8 ,
782 P_ATTRIBUTE9 =>pr_new.ATTRIBUTE9 ,
783 P_ATTRIBUTE10 =>pr_new.ATTRIBUTE10 ,
784 P_REFERENCE_NUM =>pr_new.REFERENCE_NUM ,
785 P_ATTRIBUTE11 =>pr_new.ATTRIBUTE11 ,
786 P_ATTRIBUTE12 =>pr_new.ATTRIBUTE12 ,
787 P_ATTRIBUTE13 =>pr_new.ATTRIBUTE13 ,
788 P_ATTRIBUTE14 =>pr_new.ATTRIBUTE14 ,
789 P_ATTRIBUTE15 =>pr_new.ATTRIBUTE15 ,
790 P_MIN_RELEASE_AMOUNT =>pr_new.MIN_RELEASE_AMOUNT ,
791 P_PRICE_TYPE_LOOKUP_CODE =>pr_new.PRICE_TYPE_LOOKUP_CODE ,
792 P_CLOSED_CODE =>pr_new.CLOSED_CODE ,
793 P_PRICE_BREAK_LOOKUP_CODE =>pr_new.PRICE_BREAK_LOOKUP_CODE ,
794 P_USSGL_TRANSACTION_CODE =>pr_new.USSGL_TRANSACTION_CODE ,
795 P_GOVERNMENT_CONTEXT =>pr_new.GOVERNMENT_CONTEXT ,
796 P_REQUEST_ID =>pr_new.REQUEST_ID ,
797 P_PROGRAM_APPLICATION_ID =>pr_new.PROGRAM_APPLICATION_ID ,
798 P_PROGRAM_ID =>pr_new.PROGRAM_ID ,
799 P_PROGRAM_UPDATE_DATE =>pr_new.PROGRAM_UPDATE_DATE ,
800 P_CLOSED_DATE =>pr_new.CLOSED_DATE ,
801 P_CLOSED_REASON =>pr_new.CLOSED_REASON ,
802 P_CLOSED_BY =>pr_new.CLOSED_BY ,
803 P_TRANSACTION_REASON_CODE =>pr_new.TRANSACTION_REASON_CODE ,
804 P_ORG_ID =>pr_new.ORG_ID ,
805 P_QC_GRADE =>pr_new.QC_GRADE ,
806 P_BASE_UOM =>pr_new.BASE_UOM ,
807 P_BASE_QTY =>pr_new.BASE_QTY ,
808 P_SECONDARY_UOM =>pr_new.SECONDARY_UOM ,
809 P_SECONDARY_QTY =>pr_new.SECONDARY_QTY ,
810 P_LINE_REFERENCE_NUM =>pr_new.LINE_REFERENCE_NUM ,
811 P_PROJECT_ID =>pr_new.PROJECT_ID ,
812 P_TASK_ID =>pr_new.TASK_ID ,
813 P_EXPIRATION_DATE =>pr_new.EXPIRATION_DATE ,
814 P_TAX_CODE_ID =>pr_new.TAX_CODE_ID
815 );
816
817 END IF;/*added by rchandan for bug#4479131*/
818
819 -- End Of POC
820
821 IF v_hook_value = 'FALSE' THEN
822 RETURN;
823 END IF;
824
825 -- Get Inventory Organization Id
826
827 OPEN Fetch_Org_Id_Cur;
828 FETCH Fetch_Org_Id_Cur INTO v_org_id;
829 CLOSE Fetch_Org_Id_Cur;
830
831 -- Continue, only if the Item is changed !
832
833 IF pr_old.Item_id <> pr_new.Item_id THEN
834
835 OPEN Fetch_Lines_Cur;
836 LOOP
837 FETCH Fetch_Lines_Cur INTO v_line_loc_id, v_price, v_qty, v_uom_measure;
838 EXIT WHEN Fetch_Lines_Cur%NOTFOUND;
839
840 IF v_type_lookup_code NOT IN ( 'RFQ', 'QUOTATION' ) THEN
841 v_price := v_n_price;
842 END IF;
843
844 OPEN Fetch_Flag_Cur( v_line_loc_id );
845 LOOP
846 FETCH Fetch_Flag_Cur INTO v_t_flag;
847 EXIT WHEN Fetch_Flag_Cur%NOTFOUND;
848 IF UPPER( v_t_flag ) = 'N' THEN
849
850 DELETE FROM JAI_PO_TAXES
851 WHERE Line_Location_Id = v_line_loc_id;
852
853 jai_po_cmn_pkg.insert_line( 'CATALOG',
854 v_line_loc_id,
855 v_po_hdr_id,
856 v_po_line_id,
857 v_cre_dt,
858 v_cre_by,
859 v_last_upd_dt,
860 v_last_upd_by,
861 v_last_upd_login,
862 'U' );
863
864 jai_po_tax_pkg.Ja_In_Po_Case1( v_type_lookup_code,
865 v_quot_class_code,
866 v_vendor_id,
867 v_vendor_site_id,
868 v_curr,
869 v_org_id,
870 v_item_id,
871 v_line_uom,
872 v_line_loc_id,
873 v_po_hdr_id,
874 v_po_line_id,
875 v_po_line_id,
876 v_line_loc_id,
877 v_price,
878 v_Qty,
879 v_cre_dt,
880 v_cre_by,
881 v_last_upd_dt,
882 v_last_upd_by,
883 v_last_upd_login,
884 'U',
885 success );
886 END IF;
887
888 IF ( success <> 0 OR v_t_flag = 'Y' ) THEN
889
890 jai_po_tax_pkg.Ja_In_Po_Case2 ( v_type_lookup_code,
891 v_quot_class_code,
892 v_vendor_id,
893 v_vendor_site_id,
894 v_curr,
895 v_org_id,
896 v_item_id,
897 v_line_loc_id,
898 v_po_hdr_id,
899 v_po_line_id,
900 v_price,
901 v_Qty,
902 v_cre_dt,
903 v_cre_by,
904 v_last_upd_dt,
905 v_last_upd_by,
906 v_last_upd_login,
907 v_uom_measure,
908 NULL,
909 P_VAT_ASSESS_VALUE => NULL /* Added p_vat_assess_value by rallamse bug#4250072 VAT */
910 /* for bug 16013918 by anupgupt
911 , pn_gst_assessable_value => NULL -- Added by Jia for GST Bug#10091373 on 2010/09/10
912 */
913 );
914 END IF;
915 END LOOP;
916 CLOSE Fetch_Flag_Cur;
917 END LOOP;
918 CLOSE Fetch_Lines_Cur;
919 END IF;
920 END ARU_T1 ;
921
922 END JAI_PO_LA_TRIGGER_PKG ;