1 PACKAGE BODY JAI_PO_DA_TRIGGER_PKG AS
2 /* $Header: jai_po_da_t.plb 120.1 2007/06/05 05:18:54 bgowrava ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_PO_DA_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_PO_DA_ARI_T1
11 REM
12 REM +======================================================================+
13 */
14 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
15 /* About this Trigger Creation Date : 20-Apr-2001 Ramakrishna*/
16
17 --This trigger is used to default the taxes
18 --when Standard PO is created from a Standrad Requisition using AutoCreate.
19
20 --When Standard PO is created from a Standard Requisition
21 --Using AutoCreate, AutoCreate inserts details into PO_HEADERS_ALL,PO_LINE_LOCATIONS_ALL,
22 -- PO_DISTRIBUTIONS_ALL
23
24 --In PO_DISTRIBUTIONS_ALL , REQ_DISTRIBUTION_ID will be same as
25 --DISTRIBUTION_ID in PO_REQ_DISTRIBUTIONS_ALL and REQ_DISTRIBUTION_ID is inserted into
26 --PO_DISTRIBUTIONS_ALL only when Standard PO is created from a Standard Requisition using
27 --AutoCreate, other wise it should be null.
28
29 --This trigger will not fire if the user creates Requisition using Requisition Localized screen.
30
31 --This trigger will not fire if Override_flag for the Vendor Vendor_site_id is not checked.
32
33 --This trigger fetches details from PO_HEADERS_ALL using PO_HEADER_ID and
34 --jai_po_tax_pkg.copy_reqn_taxes procedure is called.
35
36 /* End of About this trigger */
37 v_org_id NUMBER;
38 v_type_lookup_code VARCHAR2(10);
39 v_quot_class_code VARCHAR2(25);
40 v_curr VARCHAR2(15);
41 v_ship_loc_id NUMBER;
42 v_po_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Line_Id ;
43 v_po_hdr_id NUMBER; --File.Sql.35 Cbabu := pr_new.Po_Header_Id;
44 v_cre_dt DATE; --File.Sql.35 Cbabu := pr_new.Creation_Date;
45 v_cre_by NUMBER; --File.Sql.35 Cbabu := pr_new.Created_By;
46 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
47 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
48 v_last_upd_login NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
49 v_rate NUMBER; --File.Sql.35 Cbabu := pr_new.rate;
50 v_line_location_id NUMBER; --File.Sql.35 Cbabu := pr_new.line_location_id;
51 v_rate_type VARCHAR2(100);
52 v_rate_date DATE;
53 v_override_flag VARCHAR2(1);
54 v_sup_id NUMBER;
55 v_sup_site_id NUMBER;
56 v_count1 NUMBER;
57 v_count number;
58 v_style_id po_headers_all.style_id%TYPE; --Added by Sanjikum for Bug#4483042
59
60 CURSOR get_po_hdr(c_po_header_id number) IS
61 SELECT type_lookup_code,Quotation_Class_Code,Ship_To_Location_Id,
62 rate, rate_type, rate_date,currency_code,
63 style_id --Added by Sanjikum for Bug#4483042
64 FROM po_headers_all
65 WHERE po_header_id = v_po_hdr_id;
66
67 -- Get the Inventory Organization Id
68
69 CURSOR Fetch_Org_Id_Cur IS
70 SELECT Inventory_Organization_Id
71 FROM Hr_Locations
72 WHERE Location_Id = v_ship_loc_id;
73
74 --Added to check value for Tax Override Flag
75
76 CURSOR tax_override_flag_cur(c_supplier_id number, c_supp_site_id number) IS
77 SELECT override_flag
78 FROM JAI_CMN_VENDOR_SITES
79 WHERE vendor_id = c_supplier_id
80 AND vendor_site_id = c_supp_site_id;
81
82 --added, Bgowrava for Bug#6084636
83 Cursor c_get_tax_modified_flag IS
84 SELECT tax_modified_flag
85 FROM JAI_PO_LINE_LOCATIONS
86 WHERE line_location_id = pr_new.line_location_id ;
87 lv_tax_modified_flag VARCHAR2(1) ;
88
89 CURSOR get_vendor_info IS
90 SELECT nvl(Vendor_id,0), nvl(vendor_Site_Id,0)
91 /* Added by Brathod for bug#4242351 */
92 ,rate
93 ,rate_type
94 ,rate_date
95 ,currency_code
96 /* End of Bug#4242351 */
97 FROM Po_Headers_All
98 WHERE Po_Header_Id = v_po_hdr_id;
99
100 CURSOR Line_Loc_Cur( lineid IN NUMBER ) IS
101 SELECT Line_Location_Id
102 FROM po_line_locations_all
103 WHERE Po_Line_Id = lineid;
104
105 -- End of addition
106 BEGIN
107 pv_return_code := jai_constants.successful ;
108 /*------------------------------------------------------------------------------------------
109 FILENAME: Ja_In_Po_dist_Tax_Insert_Trg.sql
110
111 CHANGE HISTORY:
112 S.No Date Author and Details
113
114 1. 09/11/2004 ssumaith - bug# 3949401 Version#115.1
115
116 commented the code which returns when the new.po_release_id is null
117
118 moved the code which returns the control if taxes are already present in the JAI_PO_TAXES table
119 for the current line_location_id to the else part of the shipment_type <> 'BLANKET'
120
121 Added code to delete taxes from the JAI_PO_LINE_LOCATIONS and JAI_PO_TAXES
122 when the new.req_distribution_id is not null
123
124 Added cursor and code for INR check and returning code if set of books currency is a non india currency.
125
126 2. 29/Nov/2004 Aiyer for bug#4035566. Version#115.2
127 Issue:-
128 The trigger should not get fired when the non-INR based set of books is attached to the current operating unit
129 where transaction is being done.
130
131 Fix:-
132 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
133 NON-INR
134 Removed the cursor c_Sob_Cur and the variable lv_currency_code
135
136 Dependency Due to this Bug:-
137 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0. introduced through the bug 4033992
138
139 3. 31/MAR/2005 BRATHOD For Bug#4242351, Version#115.4
140 Issue :- Procedure jai_po_tax_pkg.copy_reqn_taxes is modified for mutating error and procedure signature
141 has been changed and four new arguments are added. So call to jai_po_tax_pkg.copy_reqn_taxes
142 procedure in this trigger needs to be modified.
143 Resolution:- Call to jai_po_tax_pkg.copy_reqn_taxes is modified by passing required arguments.
144
145 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
146 DB Entity as required for CASE COMPLAINCE. Version 116.1
147
148 5. 13-Jun-2005 File Version: 116.2
149 Ramananda for bug#4428980. Removal of SQL LITERALs is done
150
151 6. 08-Jul-2005 Sanjikum for Bug#4483042
152 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
153 IL functionality should work or not.
154
155 7. 08-Jul-2005 Sanjikum for Bug#4483042, file version 117.2
156 1) Added a new column style_id in the cursor - get_po_hdr
157
158 8. 17-Aug-2005 Ramananda for bug#4513549 during R12 Sanity Testing. jai_mrg_t.sql File Version 120.2
159 Ported the jai_po_da_t1.sql 120.2 changes
160 Commented out the code which is deleting the taxes for the current
161 line location id. This is not required after the current fix
162
163 9. 04-Jun-2007 Bgowrava for Bug#6084636
164 Issue :
165 When the tax_override_flag = 'Y', existing taxes must be deleted and Requisition taxes
166 must get defaulted.
167
168 Fix :
169 Added code to delete taxes if the tax_modified_flag is 'N' and tax_override_flag = 'N'
170
171 Dependency :
172 There is a functional dependency on this Bug for all future Bugs.
173
174
175
176 Future Dependencies For the release Of this Object:-
177 (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/
178 A datamodel change )
179 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
180 Current Version Current Bug Dependent Files Version Author Date Remarks
181 Of File On Bug/Patchset Dependent On
182 ja_in_intrfc_lines_aft_ins_trg
183 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
184 115.2 4035566 IN60105D2 + ja_in_util_pkg_s.sql 115.0 Aiyer 29-Nov-2004 Call to this function.
185 4033992 ja_in_util_pkg_b.sql 115.0
186
187 120.2 jai_po_rla_t1.sql 120.2
188 (Functional)
189
190 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
191
192 --File.Sql.35 Cbabu
193 v_po_line_id := pr_new.Po_Line_Id ;
194 v_po_hdr_id := pr_new.Po_Header_Id;
195 v_cre_dt := pr_new.Creation_Date;
196 v_cre_by := pr_new.Created_By;
197 v_last_upd_dt := pr_new.Last_Update_Date ;
198 v_last_upd_by := pr_new.Last_Updated_By;
199 v_last_upd_login := pr_new.Last_Update_Login;
200 v_rate := pr_new.rate;
201 v_line_location_id := pr_new.line_location_id;
202
203 /*
204 || Code added by aiyer for the bug 4035566
205 || Call the function jai_cmn_utils_pkg.check_jai_exists to check the current set of books in INR/NON-INR based.
206 */
207 --IF jai_cmn_utils_pkg.check_jai_exists ( p_calling_object => 'JA_IN_PO_DIST_TAX_INSERT_TRG' ,
208 -- p_set_of_books_id => pr_new.set_of_books_id
209 -- ) = FALSE
210 --THEN
211 /*
212 || return as the current set of books is NON-INR based
213 */
214 -- RETURN;
215 -- END IF;
216
217 IF pr_new.req_distribution_id is null THEN
218 RETURN;
219 END IF;
220
221 OPEN get_po_hdr(v_po_hdr_id);
222 FETCH get_po_hdr into v_type_lookup_code,v_Quot_Class_Code,v_Ship_Loc_Id,
223 v_rate, v_rate_type, v_rate_date,v_curr,
224 v_style_id; --Added by Sanjikum for Bug#4483042
225 CLOSE get_po_hdr;
226
227 --code added by Sanjikum for Bug#4483042
228 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => v_style_id) = FALSE THEN
229 return;
230 END IF;
231
232 --Added to check value for Tax Override Flag
233
234 OPEN get_vendor_info;
235 FETCH get_vendor_info into v_sup_id
236 , v_sup_site_id
237 /* Added by brathod for bug#4242351 */
238 , v_rate
239 , v_rate_type
240 , v_rate_date
241 , v_curr
242 /* End of Bug# 4242351 */
243 ;
244 CLOSE get_vendor_info;
245
246 OPEN tax_override_flag_cur(v_sup_id, v_sup_site_id);
247 FETCH tax_override_flag_cur into v_override_flag;
248 CLOSE tax_override_flag_cur;
249
250 --added, Bgowrava for Bug#6084636
251 OPEN c_get_tax_modified_flag ;
252 FETCH c_get_tax_modified_flag INTO lv_tax_modified_flag ;
253 CLOSE c_get_tax_modified_flag;
254 --added, Bgowrava for Bug#6084636
255
256 /* Bug 4513549. Commented delete as this is no more required
257 after the new functionality change */
258
259 /*
260
261 if pr_new.po_release_id is null then
262 DELETE from ja_in_po_line_location_taxes
263 WHERE line_location_id = v_line_location_id;
264
265 DELETE from ja_in_po_line_locations
266 WHERE line_location_id = v_line_location_id;
267
268 return;
269
270 the else and the part of code to execute when the else is met is
271 added by ssumaith - bug#3949401
272
273 else */
274
275 --START, added, Bgowrava for Bug#6084636
276 if pr_new.po_release_id is null then
277 -- added, Harshita for Bug 4618717
278 IF nvl(v_override_flag,'N') = 'N' and NVL(lv_tax_modified_flag, 'N') = 'N' THEN
279
280 DELETE from JAI_PO_TAXES
281 WHERE line_location_id = v_line_location_id;
282
283 DELETE from JAI_PO_LINE_LOCATIONS
284 WHERE line_location_id = v_line_location_id;
285
286 END IF ;
287
288 return;
289 /*
290 the else and the part of code to execute when the else is met is added by ssumaith - bug#3949401
291 */
292 else
293 --END, added, Bgowrava for Bug#6084636
294 SELECT count(line_location_id)
295 into v_count1
296 FROM JAI_PO_LINE_LOCATIONS
297 WHERE line_location_id=v_line_location_id;
298
299 IF v_count1 > 0 THEN
300 RETURN;
301 END IF;
302 /* bug# 3949401 */
303 -- end if; -- End for bug4513549
304 end if;
305
306
307
308 IF nvl(v_override_flag,'N') = 'Y' THEN
309 jai_po_tax_pkg.copy_reqn_taxes(v_sup_Id ,
310 v_sup_Site_Id ,
311 v_Po_Hdr_Id ,
312 v_Po_Line_Id ,
313 v_Line_Location_id ,
314 v_Type_Lookup_Code ,
315 v_Quot_Class_Code ,
316 v_Ship_Loc_Id ,
317 v_Org_Id ,
318 v_Cre_Dt ,
319 v_Cre_By ,
320 v_Last_Upd_Dt ,
321 v_Last_Upd_By ,
322 v_Last_Upd_Login
323 /* Added by brathod for bug# 4242351 */
324 , v_rate
325 , v_rate_type
326 , v_rate_date
327 , v_curr
328 /* End of Bug#4242351 */
329 );
330
331 END IF;
332
333
334 END ARI_T1 ;
335
336 END JAI_PO_DA_TRIGGER_PKG ;
[Home] [Help]