[Home] [Help]
PACKAGE BODY: APPS.JAI_PO_RLA_TRIGGER_PKG
Source
1 PACKAGE BODY JAI_PO_RLA_TRIGGER_PKG AS
2 /* $Header: jai_po_rla_t.plb 120.19.12020000.3 2013/03/25 01:40:49 vkaranam ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_PO_RLA_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_PO_RLA_ARI_T2
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 --Blanket/Quotation Defaulting Logic
16
17 v_type_lookup_code VARCHAR2(25);
18 FOUND BOOLEAN;
19 --v_rowid ROWID;--File.Sql.35 Cbabu := pr_new.ROWID;
20 v_blanket_hdr NUMBER;--File.Sql.35 Cbabu := pr_new.BLANKET_PO_HEADER_ID;
21 v_blanket_line NUMBER;--File.Sql.35 Cbabu := pr_new.BLANKET_PO_LINE_NUM;
22 v_set_books VARCHAR2(1996);
23 v_gl_set_of_bks_id NUMBER;
24 v_dest_org_id NUMBER;--File.Sql.35 Cbabu := pr_new.Destination_Organization_Id;
25 v_src_org_id NUMBER;--File.Sql.35 Cbabu := pr_new.Source_Organization_Id;
26 v_org_id NUMBER;--File.Sql.35 Cbabu := 0;
27 v_deliver_to_loc_id NUMBER;--Added by Xiao Lv for bug 10043656
28 --lv_enable_gst_flag VARCHAR2(3);--Added by Xiao Lv for bug 10043656
29 v_vendor_id NUMBER;
30 v_site_id NUMBER;
31 v_seg_id VARCHAR2(20);
32 v_uom_code VARCHAR2(3);
33 v_RATE_TYPE VARCHAR2(30);--File.Sql.35 Cbabu := pr_new.Rate_Type;
34 v_RATE_DATE DATE;--File.Sql.35 Cbabu := pr_new.Rate_Date;
35 v_RATE NUMBER;--File.Sql.35 Cbabu := pr_new.Rate;
36 v_hdr_currency VARCHAR2(15);
37 v_currency VARCHAR2(15);--File.Sql.35 Cbabu := pr_new.Currency_Code;
38 v_requisition_header_id NUMBER ;--File.Sql.35 Cbabu := pr_new.Requisition_Header_Id;
39 v_requisition_line_id NUMBER;--File.Sql.35 Cbabu := pr_new.Requisition_Line_Id;
40 v_po_line_id NUMBER;
41 v_line_quantity NUMBER;--File.Sql.35 Cbabu := pr_new.quantity;
42 v_qty NUMBER;
43 v_unit_price NUMBER;--File.Sql.35 Cbabu := pr_new.Unit_Price;
44 v_inventory_item_id NUMBER;--File.Sql.35 Cbabu := pr_new.item_id;
45 v_sugg_vendor_name VARCHAR2(360);--File.Sql.35 Cbabu := pr_new.suggested_vendor_name; --Increased the length 80 to 360 by JMEENA for bug#5394234
46 v_sugg_vendor_loc VARCHAR2(360);--File.Sql.35 Cbabu := pr_new.suggested_vendor_location; --Increased the length 80 to 360 by JMEENA for bug#5394234
47 v_line_amount NUMBER ;--File.Sql.35 Cbabu := NVL( (pr_new.quantity * pr_new.unit_price) ,0);
48 v_tax_category_id NUMBER;
49 v_line_location_id NUMBER;
50 v_tax_amount NUMBER;
51 v_line_total NUMBER;
52 v_total_amount NUMBER;
53 v_creation_date DATE ;--File.Sql.35 Cbabu := pr_new.Creation_Date;
54 v_created_by NUMBER ;--File.Sql.35 Cbabu := pr_new.Created_By;
55 v_last_update_date DATE ;--File.Sql.35 Cbabu := pr_new.Last_Update_Date;
56 v_last_updated_by NUMBER;--File.Sql.35 Cbabu := pr_new.Last_Updated_By;
57 v_last_update_login NUMBER ;--File.Sql.35 Cbabu := pr_new.Last_Update_Login;
58 v_modified_by_agent_flag po_requisition_lines_all.modified_by_agent_flag%type := pr_new.modified_by_agent_flag; /*Added for Bug 8241905*/
59 v_parent_req_line_id po_requisition_lines_all.parent_req_line_id%type := pr_new.parent_req_line_id; /*Added for Bug 8241905*/
60 conv_rate NUMBER;
61 p_tax_amount NUMBER;
62 v_assessable_value NUMBER;
63 ln_vat_assess_value NUMBER; -- Ravi for VAT
64
65 -- ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10091373 on 2010/09/10
66
67 v_tax_category_id_holder JAI_PO_LINE_LOCATIONS.tax_category_id%TYPE; -- cbabu for EnhancementBug# 2427465
68
69 CURSOR Fetch_Org_Id_Cur IS
70 SELECT NVL(Operating_Unit,0)
71 FROM Org_Organization_Definitions
72 WHERE Organization_Id = v_dest_org_id;
73
74 CURSOR org_cur IS
75 SELECT A.Segment1, A.Type_Lookup_Code,apps_source_code--added apps_source_code by rchandan for bug#4627239
76 FROM Po_Requisition_Headers_All A
77 WHERE A.Requisition_Header_Id = v_requisition_header_id;
78
79 /* Bug 5243532. Addd by Lakshmi Gopalsami
80 * Removed the cursor Fetch_Book_Id_Cur
81 * and implemented the same using caching logic.
82 */
83 CURSOR vend_cur(p_sugg_vendor_name IN VARCHAR2) IS
84 SELECT Vendor_Id
85 FROM Po_Vendors
86 WHERE Vendor_Name = p_sugg_vendor_name;
87
88 CURSOR site_cur(p_sugg_vendor_loc IN VARCHAR2) IS
89 SELECT Vendor_Site_Id
90 FROM Po_Vendor_Sites_All A
91 WHERE A.Vendor_Site_Code = p_sugg_vendor_loc
92 AND A.Vendor_Id = v_vendor_id
93 AND (A.Org_Id = v_org_id
94 OR
95 (A.Org_Id is NULL AND v_org_id is NULL)) ; /* Modified by Ramananda for removal of SQL LITERALs */
96 --AND NVL(A.Org_Id,0) = NVL(v_org_id,0);
97
98 --pramasub commented FP start
99 /*CURSOR location_cur(p_blanket_hdr IN NUMBER, p_blanket_line NUMBER) IS
100 SELECT Line_Location_Id, Quantity, Price_Override*/
101 /*4281841 start*/
102 CURSOR cur_bpa_unit_measure(p_blanket_hdr IN NUMBER, p_blanket_line NUMBER) IS
103 SELECT unit_meas_lookup_code
104 FROM Po_Line_Locations_All
105 WHERE Po_Line_Id IN (SELECT Po_Line_Id
106 FROM Po_Lines_All
107 WHERE Po_Header_Id = p_blanket_hdr
108 AND Line_Num = p_blanket_line);
109 lv_unit_meas_lookup po_line_locations_all.unit_meas_lookup_code%TYPE;
110
111 /*4281841 end*/ --pramasub FP end
112
113 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
114 --pramasub commented for FP strt
115 /*CURSOR tax_cur(p_line_location_id IN NUMBER) IS
116 SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
117 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,
118 a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8,a.precedence_9 p_9, a.precedence_10 p_10,
119 a.currency, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.tax_type,
120 a.vendor_id, a.modvat_flag,
121 tax_category_id -- cbabu for EnhancementBug# 2427465
122 FROM JAI_PO_TAXES a
123 WHERE a.line_location_id = p_line_location_id
124 ORDER BY a.tax_line_no;
125
126 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
127 CURSOR tax1_cur IS
128 SELECT a.Po_Line_Id, a.tax_line_no lno, a.tax_id,
129 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,
130 a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8, a.precedence_9 p_9, a.precedence_10 p_10,
131 a.currency, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.tax_type,
132 a.vendor_id, a.modvat_flag,
133 tax_category_id -- cbabu for EnhancementBug# 2427465
134 FROM JAI_PO_TAXES a
135 WHERE a.po_line_id = v_po_line_id
136 AND Line_Location_Id IS NULL
137 ORDER BY a.tax_line_no;*/
138 --pramasub commented for FP end
139 CURSOR cur_bpa_tax_lines(p_po_line_id IN NUMBER,p_line_location_id IN NUMBER) IS
140 SELECT a.Po_Line_Id,
141 a.tax_line_no lno ,
142 a.tax_id ,
143 a.precedence_1 p_1,
144 a.precedence_2 p_2,
145 a.precedence_3 p_3,
146 a.precedence_4 p_4,
147 a.precedence_5 p_5,
148 a.precedence_6 p_6,
149 a.precedence_7 p_7,
150 a.precedence_8 p_8,
151 a.precedence_9 p_9,
152 a.precedence_10 p_10,
153 a.currency ,
154 a.tax_rate ,
155 a.qty_rate ,
156 a.uom ,
157 a.tax_amount ,
158 a.tax_type ,
159 a.vendor_id ,
160 a.modvat_flag ,
161 tax_category_id -- cbabu for EnhancementBug# 2427465
162 FROM JAI_PO_TAXES a --Ja_In_Po_Line_Location_Taxes a
163 WHERE po_line_id = p_po_line_id
164 AND nvl(line_location_id,-999) = p_line_location_id
165 ORDER BY a.tax_line_no; --new cursor added by pramasub for FP
166
167 CURSOR Fetch_Hdr_Curr_Cur IS
168 SELECT NVL( Currency_Code, '$' )
169 FROM Po_Requisition_Headers_V
170 WHERE Requisition_Header_Id = v_requisition_header_id;
171
172 --CURSOR Fetch_Uom_Code_Cur IS pramasub FP start
173 CURSOR Fetch_Uom_Code_Cur(cp_unit_of_meas VARCHAR2) IS /*4281841*/
174 SELECT Uom_Code
175 FROM Mtl_Units_Of_Measure
176 WHERE Unit_Of_Measure = cp_unit_of_meas;
177 --WHERE Unit_Of_Measure = v_uom_code; pramasub start
178
179 -- additions by sriram - starts here Bug # 2977200
180
181 CURSOR c_reqn_line_id(p_reqn_line_id Number) is
182 SELECT 1
183 FROM JAI_PO_REQ_LINE_TAXES
184 WHERE requisition_line_id = p_reqn_line_id;
185
186 v_reqn_ctr Number :=0;
187
188 -- additions by sriram - ends here Bug # 2977200
189 lv_apps_source_code po_requisition_headers_all.apps_source_code%type; -- added by rchandan for bug#462739
190 /*5852041..start */ -- pramasub start
191 ln_tax_amount NUMBER;
192 ln_program_id NUMBER;
193
194 CURSOR c_program_id
195 IS
196 SELECT concurrent_program_id
197 FROM fnd_concurrent_programs_vl
198 WHERE concurrent_program_name ='REQIMPORT'
199 AND application_id = 201;
200
201 /*5852041..start */ -- pramasub end
202 --pramasub 6066485 start
203 cursor c_fetch_sob_from_hrou(cp_org_id in number)
204 is
205 select set_of_books_id
206 from hr_operating_units
207 Where organization_id = cp_org_id;
208 v_new_org_id NUMBER;
209 v_sob_hrou_id NUMBER;
210 --pramasub 6066485 end
211
212
213 v_hook_value VARCHAR2(10) ;
214
215 /* Bug 5243532. Added by Lakshmi Gopalsami
216 * Defined variable for implementing caching logic.
217 */
218 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
219 -- End for bug 5243532
220
221 BEGIN
222 pv_return_code := jai_constants.successful ;
223 /*-----------------------------------------------------------------------------------------------------------------------
224 CHANGE HISTORY: FILENAME: ja_in_reqn_tax_insert_trg.sql
225 S.No Date Author and Details
226 -------------------------------------------------------------------------------------------------------------------------
227 1 06/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
228 tax_category_id column is populated into PO and SO localization tables, which will be used to
229 identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into
230 the tax table will be useful to identify whether the tax is a defaulted or a manual tax.
231
232 2. 26/05/2003 sriram - Bug # 2977200
233
234 A check has been added to ensure that the insert into JAI_PO_REQ_LINE_TAXES
235 should happen only when the requisition line id
236 does not exist in the JAI_PO_REQ_LINE_TAXES table.
237 This check has been added at 2 places , before insert into JAI_PO_REQ_LINE_TAXES
238
239 3 21/10/2003 Vijay Shankar for Bug# 3207886, FileVersion# 616.2
240 Taxes are not getting defaulted from ITEM_CLASS and TAX_CATEGORY setup if Supplier information is not provided
241 requisition line. The issue occured because jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes accepts the Inventory
242 organization id as first argument, but we are passing Operating Unit Id as first parameter. Fixed the issue
243 by passing Destination Organization as parameter.
244
245 4. 30/11/2005 Aparajita for bug#4036241. Version#115.1
246
247 Introduced the call to centralized packaged procedure,
248 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
249
250 5. 17/mar-2005 Rchandan for bug#4245365 Version#115.3
251 Changes made to calculate VAT assessable value . This vat assessable is passed
252 to the procedure that calculates the VAT related taxes
253
254 6. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
255 DB as required for CASE COMPLAINCE. Version 116.1
256
257 7. 13-Jun-2005 File Version: 116.2
258 Ramananda for bug#4428980. Removal of SQL LITERALs is done
259
260 8 06-Jul-2005 rallamse for bug#4479131 PADDR Elimination
261 1. Replaced call to jai_po_cmn_pkg.query_locator_for_line with
262 jai_cmn_hook_pkg.Po_Requisition_Lines_All
263
264
265 9. 03/11/2006 SACSETHI for Bug 5228046, File version 120.4
266 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement, Tax precedence, BOE).
267
268
269 10. 17-APR-2007 Bgowrava for forward porting bug#5989740, 11i Bug#5907436 , File version 120.5
270 Changes added for Handling secondary and Higher Secondary Education Cess
271
272 This bug has datamodel and spec changes.
273
274 11 15-JUN-2007 ssawant for bug 6134111
275 The Taxes are defaulted from the Req to PO but the the Tax Category name
276 it not visible. Hence tax_category_id = v_tax_category_id_holder clause was added
277 in update table query.
278
279 12. 07-Nov-2008 JMEENA for bug#5394234
280 Increased the length of variables v_sugg_vendor_name and v_sugg_vendor_loc from 80 to 360
281
282 13 31-JUL-2009 Bug 8711805
283 Uom_Code was fetched from mtl_units_of_measure using a Query instead of cursor
284 Hence when unit_of_measure is NULL in case of 'Fixed Price Services' Line Type
285 (Set in Profile option 'POR : Amount Based Services Line Type') NO_DATA_FOUND
286 error is thrown.
287
288 14 10-SEP-2010 Jia for GST Bug#10091373.
289
290 Dependency:
291 ----------
292
293 Sl No. Bug Dependent on
294 Bug/Patch set Details
295 -------------------------------------------------------------------------------------------------
296 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
297 4033992.
298 ja_in_util_pkg_s.sql 115.0
299 ja_in_util_pkg_b.sql 115.0
300
301 2. 4245365 4245089 VAT implementation
302
303 --------------------------------------------------------------------------------------------*/
304
305 --File.Sql.35 Cbabu
306 --v_rowid := pr_new.ROWID;
307 v_blanket_hdr := pr_new.BLANKET_PO_HEADER_ID;
308 v_blanket_line := pr_new.BLANKET_PO_LINE_NUM;
309 v_dest_org_id := pr_new.Destination_Organization_Id;
310 v_deliver_to_loc_id := pr_new.DELIVER_TO_LOCATION_ID;--Added by Xiao Lv for bug 10043656
311 v_src_org_id := pr_new.Source_Organization_Id;
312 v_org_id := 0;
313 v_RATE_TYPE := pr_new.Rate_Type;
314 v_RATE_DATE := pr_new.Rate_Date;
315 v_RATE := pr_new.Rate;
316 v_currency := pr_new.Currency_Code;
317 v_requisition_header_id := pr_new.Requisition_Header_Id;
318 v_requisition_line_id := pr_new.Requisition_Line_Id;
319 v_line_quantity := pr_new.quantity;
320 v_unit_price := pr_new.Unit_Price;
321 v_inventory_item_id := pr_new.item_id;
322 v_sugg_vendor_name := pr_new.suggested_vendor_name;
323 v_sugg_vendor_loc := pr_new.suggested_vendor_location;
324 v_line_amount := NVL( (pr_new.quantity * pr_new.unit_price) ,0);
325 v_creation_date := pr_new.Creation_Date;
326 v_created_by := pr_new.Created_By;
327 v_last_update_date := pr_new.Last_Update_Date;
328 v_last_updated_by := pr_new.Last_Updated_By;
329 v_last_update_login := pr_new.Last_Update_Login;
330 v_reqn_ctr :=0;
331 v_new_org_id := pr_new.ORG_ID; --pramasub 6066485
332
333 --if
334 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_REQN_TAX_INSERT_TRG',
335 -- p_org_id => pr_new.org_id)
336
337 -- =
338 -- FALSE
339 -- then
340 /* India Localization funtionality is not required */
341 -- return;
342 -- end if;
343
344 OPEN org_cur; --rchandan for bug#462739 moved the cursor call here as apps_source_code was added in the cursor
345 FETCH Org_Cur INTO v_seg_id, v_type_lookup_code,lv_apps_source_code;
346 CLOSE org_cur;
347
348 /* added by rchandan for bug#4627239 start*/
349
350 /*IF nvl(lv_apps_source_code,'xyz') = 'POR' THEN
351 return;
352 --India Localization does not support iProcurement
353 END IF ;*/
354 /*
355 || ssumaith - eliminated code to stop iprocurement Requisitions from being created.
356 */
357 /* added by rchandan for bug#4627239 end*/
358
359 -- jai_po_cmn_pkg.query_locator_for_line( v_requisition_header_id, 'JAINREQN', FOUND );
360 v_hook_value := 'TRUE';
361 /* If v_hook_value is TRUE, then it means taxes should be defaulted. IF FALSE then return */
362 v_hook_value := jai_cmn_hook_pkg.Po_Requisition_Lines_All
363 (
364 pr_new.REQUISITION_LINE_ID,
365 pr_new.REQUISITION_HEADER_ID,
366 pr_new.LINE_NUM,
367 pr_new.LINE_TYPE_ID,
368 pr_new.CATEGORY_ID,
369 pr_new.ITEM_DESCRIPTION,
370 pr_new.UNIT_MEAS_LOOKUP_CODE,
371 pr_new.UNIT_PRICE,
372 pr_new.QUANTITY,
373 pr_new.DELIVER_TO_LOCATION_ID,
374 pr_new.TO_PERSON_ID,
375 pr_new.LAST_UPDATE_DATE,
376 pr_new.LAST_UPDATED_BY,
377 pr_new.SOURCE_TYPE_CODE,
378 pr_new.LAST_UPDATE_LOGIN,
379 pr_new.CREATION_DATE,
380 pr_new.CREATED_BY,
381 pr_new.ITEM_ID,
382 pr_new.ITEM_REVISION,
383 pr_new.QUANTITY_DELIVERED,
384 pr_new.SUGGESTED_BUYER_ID,
385 pr_new.ENCUMBERED_FLAG,
386 pr_new.RFQ_REQUIRED_FLAG,
387 pr_new.NEED_BY_DATE,
388 pr_new.LINE_LOCATION_ID,
389 pr_new.MODIFIED_BY_AGENT_FLAG,
390 pr_new.PARENT_REQ_LINE_ID,
391 pr_new.JUSTIFICATION,
392 pr_new.NOTE_TO_AGENT,
393 pr_new.NOTE_TO_RECEIVER,
394 pr_new.PURCHASING_AGENT_ID,
395 pr_new.DOCUMENT_TYPE_CODE,
396 pr_new.BLANKET_PO_HEADER_ID,
397 pr_new.BLANKET_PO_LINE_NUM,
398 pr_new.CURRENCY_CODE,
399 pr_new.RATE_TYPE,
400 pr_new.RATE_DATE,
401 pr_new.RATE,
402 pr_new.CURRENCY_UNIT_PRICE,
403 pr_new.SUGGESTED_VENDOR_NAME,
404 pr_new.SUGGESTED_VENDOR_LOCATION,
405 pr_new.SUGGESTED_VENDOR_CONTACT,
406 pr_new.SUGGESTED_VENDOR_PHONE,
407 pr_new.SUGGESTED_VENDOR_PRODUCT_CODE,
408 pr_new.UN_NUMBER_ID,
409 pr_new.HAZARD_CLASS_ID,
410 pr_new.MUST_USE_SUGG_VENDOR_FLAG,
411 pr_new.REFERENCE_NUM,
412 pr_new.ON_RFQ_FLAG,
413 pr_new.URGENT_FLAG,
414 pr_new.CANCEL_FLAG,
415 pr_new.SOURCE_ORGANIZATION_ID,
416 pr_new.SOURCE_SUBINVENTORY,
417 pr_new.DESTINATION_TYPE_CODE,
418 pr_new.DESTINATION_ORGANIZATION_ID,
419 pr_new.DESTINATION_SUBINVENTORY,
420 pr_new.QUANTITY_CANCELLED,
421 pr_new.CANCEL_DATE,
422 pr_new.CANCEL_REASON,
423 pr_new.CLOSED_CODE,
424 pr_new.AGENT_RETURN_NOTE,
425 pr_new.CHANGED_AFTER_RESEARCH_FLAG,
426 pr_new.VENDOR_ID,
427 pr_new.VENDOR_SITE_ID,
428 pr_new.VENDOR_CONTACT_ID,
429 pr_new.RESEARCH_AGENT_ID,
430 pr_new.ON_LINE_FLAG,
431 pr_new.WIP_ENTITY_ID,
432 pr_new.WIP_LINE_ID,
433 pr_new.WIP_REPETITIVE_SCHEDULE_ID,
434 pr_new.WIP_OPERATION_SEQ_NUM,
435 pr_new.WIP_RESOURCE_SEQ_NUM,
436 pr_new.ATTRIBUTE_CATEGORY,
437 pr_new.DESTINATION_CONTEXT,
438 pr_new.INVENTORY_SOURCE_CONTEXT,
439 pr_new.VENDOR_SOURCE_CONTEXT,
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.ATTRIBUTE11,
451 pr_new.ATTRIBUTE12,
452 pr_new.ATTRIBUTE13,
453 pr_new.ATTRIBUTE14,
454 pr_new.ATTRIBUTE15,
455 pr_new.BOM_RESOURCE_ID,
456 pr_new.CLOSED_REASON,
457 pr_new.CLOSED_DATE,
458 pr_new.TRANSACTION_REASON_CODE,
459 pr_new.QUANTITY_RECEIVED,
460 pr_new.SOURCE_REQ_LINE_ID,
461 pr_new.ORG_ID,
462 pr_new.KANBAN_CARD_ID,
463 pr_new.CATALOG_TYPE,
464 pr_new.CATALOG_SOURCE,
465 pr_new.MANUFACTURER_ID,
466 pr_new.MANUFACTURER_NAME,
467 pr_new.MANUFACTURER_PART_NUMBER,
468 pr_new.REQUESTER_EMAIL,
469 pr_new.REQUESTER_FAX,
470 pr_new.REQUESTER_PHONE,
471 pr_new.UNSPSC_CODE,
472 pr_new.OTHER_CATEGORY_CODE,
473 pr_new.SUPPLIER_DUNS,
474 pr_new.TAX_STATUS_INDICATOR,
475 pr_new.PCARD_FLAG,
476 pr_new.NEW_SUPPLIER_FLAG,
477 pr_new.AUTO_RECEIVE_FLAG,
478 pr_new.TAX_USER_OVERRIDE_FLAG,
479 pr_new.TAX_CODE_ID,
480 pr_new.NOTE_TO_VENDOR,
481 pr_new.OKE_CONTRACT_VERSION_ID,
482 pr_new.OKE_CONTRACT_HEADER_ID,
483 pr_new.ITEM_SOURCE_ID,
484 pr_new.SUPPLIER_REF_NUMBER,
485 pr_new.SECONDARY_UNIT_OF_MEASURE,
486 pr_new.SECONDARY_QUANTITY,
487 pr_new.PREFERRED_GRADE,
488 pr_new.SECONDARY_QUANTITY_RECEIVED,
489 pr_new.SECONDARY_QUANTITY_CANCELLED,
490 pr_new.VMI_FLAG,
491 pr_new.AUCTION_HEADER_ID,
492 pr_new.AUCTION_DISPLAY_NUMBER,
493 pr_new.AUCTION_LINE_NUMBER,
494 pr_new.REQS_IN_POOL_FLAG,
495 pr_new.BID_NUMBER,
496 pr_new.BID_LINE_NUMBER,
497 pr_new.NONCAT_TEMPLATE_ID,
498 pr_new.SUGGESTED_VENDOR_CONTACT_FAX,
499 pr_new.SUGGESTED_VENDOR_CONTACT_EMAIL,
500 pr_new.AMOUNT,
501 pr_new.CURRENCY_AMOUNT,
502 pr_new.LABOR_REQ_LINE_ID,
503 pr_new.JOB_ID,
504 pr_new.JOB_LONG_DESCRIPTION,
505 pr_new.CONTRACTOR_STATUS,
506 pr_new.CONTACT_INFORMATION,
507 pr_new.SUGGESTED_SUPPLIER_FLAG,
508 pr_new.CANDIDATE_SCREENING_REQD_FLAG,
509 pr_new.CANDIDATE_FIRST_NAME,
510 pr_new.CANDIDATE_LAST_NAME,
511 pr_new.ASSIGNMENT_END_DATE,
512 pr_new.OVERTIME_ALLOWED_FLAG,
513 pr_new.CONTRACTOR_REQUISITION_FLAG,
514 pr_new.DROP_SHIP_FLAG,
515 pr_new.ASSIGNMENT_START_DATE,
516 pr_new.ORDER_TYPE_LOOKUP_CODE,
517 pr_new.PURCHASE_BASIS,
518 pr_new.MATCHING_BASIS,
519 pr_new.NEGOTIATED_BY_PREPARER_FLAG,
520 pr_new.SHIP_METHOD,
521 pr_new.ESTIMATED_PICKUP_DATE,
522 pr_new.SUPPLIER_NOTIFIED_FOR_CANCEL,
523 pr_new.BASE_UNIT_PRICE,
524 pr_new.AT_SOURCING_FLAG,
525 /* Bug 4535701. Added by Lakshmi Gopalsami
526 * Passing event_id and line_number as null
527 * for build issue */
528 /* Bug4540709. Added by Lakshmig Gopalsami
529 * Reverting the fix for bug 4535701 */
530 /* pr_new.EVENT_ID,
531 pr_new.LINE_NUMBER*/ -- the above two lines commented by ssumaith - bug#4616729
532 NULL, /* the following two nulls are added by ssumaith because these two columns are not present in the table po_requisition_lines_all at this time */
533 NULL
534 ) ;
535
536 --This change is done By Nagaraj.s for Bug#2381124
537 --Change done : The Program id of Requisition Import Program is 32353. If the
538 -- Requisition is created through Requisition Import program then the trigger should
539 -- be executed without checking for the Localization Form Session Id.
540 -- pramasub start
541 OPEN c_program_id;
542 FETCH c_program_id INTO ln_program_id;
543 CLOSE c_program_id;
544 -- pramasub end
545
546 IF pr_new.PROGRAM_ID <> ln_program_id AND nvl(lv_apps_source_code,'$$') <> 'POR' THEN
547 /*rchandan for bug#5852041..replaced 32353 with ln_program_id and OR with AND*/ -- pramasub FP
548 --IF NOT FOUND THEN
549 IF v_hook_value = 'FALSE' THEN
550 RETURN;
551 END IF;
552 END IF;
553
554 OPEN Fetch_Org_Id_Cur;
555 FETCH Fetch_Org_Id_Cur INTO v_org_id;
556 CLOSE Fetch_Org_Id_Cur;
557
558 /*OPEN org_cur; commeneted by pramasub for FP
559 FETCH Org_Cur INTO v_seg_id, v_type_lookup_code;
560 CLOSE org_cur;*/
561
562 /* Bug 5243532. Added by Lakshmi Gopalsami
563 * Removed the cursor Fetch_Book_Id_Cur
564 * and implemented using caching logic.
565 */
566
567 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
568 (p_org_id => v_dest_org_id );
569 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
570
571 -- Start, cbabu for EnhancementBug# 2427465
572 INSERT INTO JAI_PO_REQ_LINES (
573 requisition_line_id, requisition_header_id, tax_modified_flag,
574 tax_amount, total_amount,
575 creation_date, created_by, last_update_date,
576 last_updated_by, last_update_login
577 ) VALUES (
578 v_requisition_line_id, v_requisition_header_id, 'N',
579 NULL, NULL,
580 v_creation_date, v_created_by, v_last_update_date,
581 v_last_updated_by, v_last_update_login
582 );
583 -- End, cbabu for EnhancementBug# 2427465
584
585 OPEN Fetch_Hdr_Curr_Cur;
586 FETCH Fetch_Hdr_Curr_Cur INTO v_hdr_currency;
587 CLOSE Fetch_Hdr_Curr_Cur;
588
589 OPEN vend_cur(v_sugg_vendor_name);
590 FETCH Vend_Cur INTO v_vendor_id;
591 CLOSE vend_cur;
592
593 OPEN site_cur(v_sugg_vendor_loc);
594 FETCH Site_Cur INTO v_site_id;
595 CLOSE site_cur;
596
597 /*Added for Bug 8711805 - Start*/
598 OPEN Fetch_Uom_Code_Cur(pr_new.Unit_Meas_Lookup_Code);
599 FETCH Fetch_Uom_Code_Cur INTO v_uom_code;
600 CLOSE Fetch_Uom_Code_Cur;
601 /*Added for Bug 8711805 - End*/
602 /*
603 SELECT Uom_Code
604 INTO v_uom_code
605 FROM Mtl_Units_of_Measure
606 WHERE Unit_Of_Measure = pr_new.Unit_Meas_Lookup_Code;
607 */
608
609 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value( v_vendor_id, v_site_id,
610 v_inventory_item_id, v_uom_code );
611
612
613
614 -- insert into xc values( ' vendor id ' || to_char( v_vendor_id ) || ' vendor site ' || to_char( v_site_id ) || ' item id ' || to_char( v_inventory_item_id ) || ' uom ' || v_uom_code );
615
616
617 v_currency := NVL( v_currency, v_hdr_currency );
618
619 -- pramasub start 6066485
620 open c_fetch_sob_from_hrou(v_new_org_id);
621 Fetch c_fetch_sob_from_hrou into v_sob_hrou_id;
622 close c_fetch_sob_from_hrou;
623 -- pramasub end 6066485
624
625 --IF v_currency = v_hdr_currency THEN commented by pramasub on FP
626 IF NVL(v_currency,'$$') = NVL(v_hdr_currency,'$$') THEN -- inserted by pramasub on FP
627 conv_rate := 1;
628 ELSE
629 IF v_rate_type = 'User' THEN
630 conv_rate := 1/v_rate;
631 ELSE
632 --conv_rate := 1/jai_cmn_utils_pkg.currency_conversion( v_gl_set_of_bks_id, v_currency, v_rate_date, v_rate_type, v_rate ); pramasub 6066485
633 conv_rate := 1/jai_cmn_utils_pkg.currency_conversion( v_sob_hrou_id, v_currency, v_rate_date, v_rate_type, v_rate );
634 END IF;
635 END IF;
636
637 v_line_amount := v_line_amount * conv_rate;
638
639 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value
640 ( p_party_id => v_vendor_id,
641 p_party_site_id => v_site_id,
642 p_inventory_item_id => v_inventory_item_id,
643 p_uom_code => v_uom_code,
644 p_default_price => 0,
645 p_ass_value_date => SYSDATE,
646 p_party_type => 'V'
647 ) ; -- Ravi for VAT
648
649 IF NVL( v_assessable_value, 0 ) <= 0 THEN
650 v_assessable_value := v_line_amount;
651 ELSE
652 v_assessable_value := v_assessable_value * v_line_quantity * conv_rate;
653 END IF;
654
655 IF ln_vat_assess_value = 0 THEN -- Ravi for VAT
656 ln_vat_assess_value := v_line_amount;
657 ELSE
658 ln_vat_assess_value := ln_vat_assess_value * v_line_quantity * conv_rate;
659 END IF;
660
661 -- Added by Jia for GST Bug#10091373 on 2010/09/10, Begin
662 ---------------------------------------------------------------------------------------
663 /*
664 ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value
665 ( p_party_id => v_vendor_id,
666 p_party_site_id => v_site_id,
667 p_inventory_item_id => v_inventory_item_id,
668 p_uom_code => v_uom_code,
669 p_default_price => 0,
670 p_ass_value_date => SYSDATE,
671 p_party_type => 'V'
672 ) ;
673
674 IF ln_gst_assessable_value = 0
675 THEN
676 ln_gst_assessable_value := v_line_amount;
677 ELSE
678 ln_gst_assessable_value := ln_gst_assessable_value * v_line_quantity * conv_rate;
679 END IF;
680 ---------------------------------------------------------------------------------------
681 -- Added by Jia for GST Bug#10091373 on 2010/09/10, end
682 */
683
684 -- insert into xc values( ' av ' || to_char( v_assessable_value ) );
685 --pramasub start FP
686 -- Following IF condition added by skjayaba for internal QA issue. Tax currency coming as NULL for Requisitions.
687 IF (lv_apps_source_code = 'POR' AND v_currency is NULL) THEN
688 v_currency := 'INR';
689 END IF;
690 --pramasub end FP
691 IF v_blanket_hdr IS NOT NULL AND v_blanket_line IS NOT NULL THEN
692
693 jai_po_cmn_pkg.locate_source_line( v_blanket_hdr, v_blanket_line, v_line_quantity, v_po_line_id, v_line_location_id );
694
695 /*IF v_line_location_id = -999 THEN --pramasub commented FP
696 -- code added by sriram to ensure that we do not have a duplicate requisition line_id
697 -- checking if the requisition line id is already created or not
698 -- Bug # 2977200*/
699
700 open c_reqn_line_id(v_requisition_line_id);
701 fetch c_reqn_line_id into v_reqn_ctr;
702 close c_reqn_line_id;
703
704 if nvl(v_reqn_ctr,0) = 0 then
705
706 -- ends here additions by sriram -- Bug # 2977200
707 --FOR rec IN tax1_cur LOOP
708 FOR rec IN cur_bpa_tax_lines(v_po_line_id, v_line_location_id ) LOOP /*rchandan for bug#5852041*/ --pramasub FP
709 v_po_line_id := Rec.Po_Line_Id;
710
711
712 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
713 INSERT INTO JAI_PO_REQ_LINE_TAXES(
714 requisition_line_id, requisition_header_id, tax_line_no,
715 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
716 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
717 tax_id, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
718 tax_type, modvat_flag, vendor_id, currency,
719 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
720 tax_category_id -- cbabu for EnhancementBug# 2427465
721 ) VALUES (
722 v_requisition_line_id, v_requisition_header_id, rec.lno,
723 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
724 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
725 rec.tax_id, rec.tax_rate, rec.qty_rate, rec.uom, rec.tax_amount, rec.tax_amount + v_line_amount,
726 rec.tax_type, rec.modvat_flag, rec.vendor_id, rec.currency,
727 v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
728 rec.tax_category_id -- cbabu for EnhancementBug# 2427465
729 );
730
731
732 --v_uom_code := rec.uom; /*4281841..commented*/ pramasub FP
733 END LOOP;
734 end if; -- end if added by sriram -- Bug # 2977200
735 /*4281841...start*/
736 OPEN cur_bpa_unit_measure(v_blanket_hdr, v_blanket_line);
737 FETCH cur_bpa_unit_measure INTO lv_unit_meas_lookup;
738 CLOSE cur_bpa_unit_measure;
739
740 OPEN Fetch_Uom_Code_Cur(lv_unit_meas_lookup);
741 FETCH Fetch_Uom_Code_Cur INTO v_uom_code;
742 CLOSE Fetch_Uom_Code_Cur;
743 /*4281841...end*/
744
745 /* jai_po_tax_pkg.calculate_tax( 'REQUISITION', v_requisition_line_id , v_requisition_header_id, NULL,
746 v_line_quantity, v_unit_price*v_line_quantity, NVL( v_currency, v_hdr_currency ) ,
747 v_assessable_value, v_assessable_value, NULL, conv_rate ); */
748
749 /* ELSE -- pramasub FP commented
750
751 -- code added by sriram to ensure that we do not have a duplicate requisition line_id
752 -- checking if the requisition line id is already created or not
753 -- Bug # 2977200
754
755 open c_reqn_line_id(v_requisition_line_id) ;
756 fetch c_reqn_line_id into v_reqn_ctr;
757 close c_reqn_line_id;
758
759 if nvl(v_reqn_ctr,0) = 0 then
760
761 FOR rec IN tax_cur(v_line_location_id) LOOP
762 v_po_line_id := Rec.Po_Line_Id;
763
764
765 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
766 INSERT INTO JAI_PO_REQ_LINE_TAXES(
767 requisition_line_id, requisition_header_id, tax_line_no,
768 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
769 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
770 tax_id, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
771 tax_type, modvat_flag, vendor_id, currency,
772 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
773 tax_category_id -- cbabu for EnhancementBug# 2427465
774 ) VALUES (
775 v_requisition_line_id, v_requisition_header_id, rec.lno,
776 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
777 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
778 rec.tax_id, rec.tax_rate, rec.qty_rate, rec.uom, rec.tax_amount, rec.tax_amount + v_line_amount,
779 rec.tax_type, rec.modvat_flag, rec.vendor_id, rec.currency,
780 v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login,
781 rec.tax_category_id -- cbabu for EnhancementBug# 2427465
782 );
783
784 v_uom_code := rec.uom;
785 END LOOP;
786 end if; -- added by sriram -- Bug # 2977200
787
788 END IF;*/
789 /*jai_po_tax_pkg.calculate_tax( 'REQUISITION_BLANKET', v_requisition_line_id , v_po_line_id, v_line_location_id,
790 v_line_quantity, v_unit_price*v_line_quantity, NVL( v_currency, v_hdr_currency ) ,
791 v_assessable_value, v_assessable_value,ln_vat_assess_value, NULL, conv_rate ); -- Ravi for VAT*/ --pramasub FP commented
792 --ja_in_po_calc_tax is renamed to jai_po_tax_pkg.calc_tax
793 jai_po_tax_pkg.calc_tax(p_type => 'REQUISITION_BLANKET',
794 p_header_id => v_blanket_hdr,
795 p_requisition_line_id => v_requisition_line_id ,
796 P_line_id => v_po_line_id,
797 p_line_location_id => v_line_location_id,
798 p_line_focus_id => NULL,
799 p_line_quantity => v_line_quantity,
800 p_base_value => v_line_amount,
801 p_line_uom_code => v_uom_code,
802 p_tax_amount => ln_tax_amount,
803 p_assessable_value => v_assessable_value,
804 p_vat_assess_value => ln_vat_assess_value,
805 p_item_id => v_inventory_item_id,
806 p_conv_rate => 1/conv_rate,
807 p_po_curr => v_currency,
808 p_func_curr => v_hdr_currency
809 -- , pn_gst_assessable_value => ln_gst_assessable_value
810 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
811
812 ELSIF v_blanket_hdr IS NULL AND v_blanket_line IS NULL THEN
813
814 -- lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;--Added by Xiao Lv for bug 10043656
815 IF v_type_lookup_code = 'PURCHASE' THEN
816
817 -- Following line modified by Vijay Shankar for Bug# 3207886
818 -- Reason: jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes accepts INV_ORGANIZATION_ID as Parameter but we are passing OPT Unit id
819 -- jai_cmn_tax_defaultation_pkg.JA_IN_VENDOR_DEFAULT_TAXES(NVL(v_org_id,0), v_vendor_id,
820 -- IF nvl(lv_enable_gst_flag,'N') = 'N' THEN --Added by Xiao Lv for bug 10043656
821 jai_cmn_tax_defaultation_pkg.JA_IN_VENDOR_DEFAULT_TAXES(v_dest_org_id, v_vendor_id,
822 v_site_id, v_inventory_item_id, v_requisition_header_id,
823 v_requisition_line_id, v_tax_category_id);
824 /*
825 ELSE
826 --Added by Xiao Lv for bug 10043656, begin
827 -----------------------------------------------------------------------
828 jai_gst_tax_defaultation_pkg.jai_gst_vendor_default_taxes(
829 pn_organization_id =>v_dest_org_id,
830 pn_location_id =>v_deliver_to_loc_id,
831 pn_vendor_id =>v_vendor_id,
832 pn_vendor_site_id =>v_site_id,
833 pn_inventory_item_id =>v_inventory_item_id,
834 pd_transaction_date =>v_creation_date,
835 pn_tax_category_id =>v_tax_category_id);
836 -----------------------------------------------------------------------
837 --Added by Xiao Lv for bug 10043656, end
838 END IF;
839 */
840 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES('PO_REQN', v_tax_category_id, v_requisition_header_id,
841 v_requisition_line_id, v_assessable_value, v_line_amount, v_inventory_item_id, v_line_quantity ,
842 v_uom_code, v_vendor_id, NVL( v_currency, v_hdr_currency ), conv_rate,
843 v_creation_date, v_created_by, v_last_update_date,
844 v_last_updated_by, v_last_update_login,p_vat_assessable_value => ln_vat_assess_value, -- Ravi for VAT
845 p_modified_by_agent_flag => v_modified_by_agent_flag, /*Added for Bug 8241905*/
846 p_parent_req_line_id => v_parent_req_line_id /*Added for Bug 8241905*/
847 -- , pn_gst_assessable_value => ln_gst_assessable_value
848 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
849
850 ELSIF v_type_lookup_code = 'INTERNAL' THEN
851
852 --IF nvl(lv_enable_gst_flag,'N') = 'N' THEN --Added by Xiao Lv for bug 10043656
853 jai_cmn_tax_defaultation_pkg.Ja_In_Org_Default_Taxes( v_src_org_id, v_inventory_item_id, v_tax_category_id );
854
855 /*ELSE
856 jai_gst_tax_defaultation_pkg.jai_gst_org_default_taxes(
857 pn_organization_id =>v_src_org_id,
858 pn_location_id =>v_deliver_to_loc_id,
859 pn_party_id =>v_vendor_id,
860 pn_party_site_id =>v_site_id,
861 pv_party_type =>'V',
862 pn_inventory_item_id =>v_inventory_item_id,
863 pd_transaction_date =>v_creation_date,
864 pn_tax_category_id =>v_tax_category_id );
865 END IF;--Added by Xiao Lv for bug 10043656, ends
866 */
867 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES('PO_REQN', v_tax_category_id, v_requisition_header_id,
868 v_requisition_line_id, v_assessable_value, v_line_amount, v_inventory_item_id, v_line_quantity ,
869 v_uom_code, NULL, NVL( v_currency, v_hdr_currency ), conv_rate,
870 v_creation_date, v_created_by, v_last_update_date,
871 v_last_updated_by, v_last_update_login, -1*v_src_org_id,p_vat_assessable_value => ln_vat_assess_value ,
872 p_modified_by_agent_flag => v_modified_by_agent_flag, /*Added for Bug 8241905*/
873 p_parent_req_line_id => v_parent_req_line_id /*Added for Bug 8241905*/
874 --, pn_gst_assessable_value => ln_gst_assessable_value
875 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
876
877 END IF;
878
879 END IF;
880
881 SELECT SUM(TAX_AMOUNT)
882 INTO v_tax_amount
883 FROM JAI_PO_REQ_LINE_TAXES
884 WHERE Requisition_Header_Id = v_requisition_header_id
885 AND Requisition_Line_Id = v_requisition_line_id
886 AND Tax_Type <> jai_constants.tax_type_tds ; /* 'TDS' ;Ramananda for removal of SQL LITERALs */
887 v_total_amount := v_line_amount + v_tax_amount;
888
889 -- cbabu for EnhancementBug# 2427465
890 UPDATE JAI_PO_REQ_LINES
891 SET tax_amount = v_tax_amount,
892 total_amount = v_total_amount
893 WHERE requisition_line_id = v_requisition_line_id;
894 END ARI_T1 ;
895
896 /*
897 REM +======================================================================+
898 REM NAME ARU_T1
899 REM
900 REM DESCRIPTION Called from trigger JAI_PO_RLA_ARIUD_T1
901 REM
902 REM NOTES Refers to old trigger JAI_PO_RLA_ARU_T1
903 REM
904 REM +======================================================================+
905 */
906 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
907 v_requisition_line_id NUMBER; --File.Sql.35 Cbabu := pr_new.Requisition_Line_Id;
908 v_sugg_vendor_name VARCHAR2(360); --File.Sql.35 Cbabu := pr_new.Suggested_Vendor_Name; --Increased the length 80 to 360 by JMEENA for bug#5394234
909 v_dest_org_id NUMBER; --File.Sql.35 Cbabu := pr_new.Destination_Organization_Id; --Increased the length 80 to 360 by JMEENA for bug#5394234
910 v_sugg_vendor_location VARCHAR2(360); --File.Sql.35 Cbabu := pr_new.Suggested_Vendor_Location;
911 v_item_id NUMBER; --File.Sql.35 Cbabu := pr_new.Item_Id;
912 v_shipment_type VARCHAR2(30);
913 v_po_vendor_id NUMBER;
914 v_vendor_id NUMBER;
915 v_sugg_vendor_id NUMBER;
916 v_po_vendor_site_id NUMBER;
917 v_line_loc_id NUMBER; --File.Sql.35 Cbabu := pr_new.Line_Location_Id;
918 v_cre_dt DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
919 v_cre_by NUMBER; --File.Sql.35 Cbabu := pr_new.Created_By;
920 v_last_upd_dt DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date ;
921 v_last_upd_by NUMBER; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
922 v_last_upd_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
923 v_hdr_curr VARCHAR2(30);
924 v_uom_code VARCHAR2(100);
925 v_price NUMBER ; --File.Sql.35 Cbabu := pr_new.Unit_Price;
926 v_qty NUMBER ; --File.Sql.35 Cbabu := pr_new.Quantity;
927 v_curr VARCHAR2(30) ; --File.Sql.35 Cbabu := pr_new.Currency_Code;
928 v_req_conv_rate NUMBER ; --File.Sql.35 Cbabu := pr_new.Rate;
929 v_req_conv_type VARCHAR2(30) ; --File.Sql.35 Cbabu := pr_new.Rate_Type;
930 v_req_conv_date DATE ; --File.Sql.35 Cbabu := pr_new.Rate_Date;
931 v_t_curr VARCHAR2(30);
932 v_po_curr VARCHAR2(30);
933 v_po_conv_rate NUMBER;
934 v_po_conv_type VARCHAR2(30);
935 v_po_conv_date DATE;
936 v_set_of_book_id NUMBER;
937 conv_rate NUMBER;
938 v_curr_conv_factor NUMBER;
939 DUMMY NUMBER;--File.Sql.35 Cbabu := 1;
940 v_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;--added by Gsr and Srihari on 07-03-2001
941
942 -- For Blanket Release
943 v_src_ship_id NUMBER;
944 v_po_rel_id NUMBER;
945
946 -- For Cursor Fetch tax cur
947 v_po_hdr_id NUMBER;
948 v_po_line_id NUMBER;
949 v_line_focus_id NUMBER;
950 Line_tot NUMBER;
951 v_tax_amt NUMBER;
952 v_total_amt NUMBER;
953 v_tax_line_no NUMBER;
954 v_prec1 NUMBER;
955 v_prec2 NUMBER;
956 v_prec3 NUMBER;
957 v_prec4 NUMBER;
958 v_prec5 NUMBER;
959
960 v_prec6 NUMBER;
961 v_prec7 NUMBER;
962 v_prec8 NUMBER;
963 v_prec9 NUMBER;
964 v_prec10 NUMBER;
965
966
967 v_taxid NUMBER;
968 v_tax_rate NUMBER;
969 v_qty_rate NUMBER;
970 v_uom VARCHAR2(15);
971 v_tax_type VARCHAR2(30);
972 v_mod_flag VARCHAR2(1);
973 v_vendor2_id NUMBER;
974 v_mod_cr NUMBER;
975 v_vendor1_id NUMBER;
976 v_tax_target_amt NUMBER;
977 v_tax_amt1 NUMBER;
978 v_assessable_value NUMBER;
979 ln_vat_assess_value NUMBER; -- added rallamse bug#4250072 VAT
980 -- ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10091373 on 2010/09/10
981 v_loc_count NUMBER;--File.Sql.35 Cbabu := 0; --new variable for loc chk on 17-aug-00
982
983 v_tax_category_id JAI_PO_TAXES.tax_category_id%TYPE; -- cbabu for EnhancementBug# 2427465
984 v_tax_category_id_holder JAI_PO_LINE_LOCATIONS.tax_category_id%TYPE; -- cbabu for EnhancementBug# 2427465
985 v_style_id po_headers_all.style_id%TYPE; --Added by Sanjikum for Bug#4483042
986
987 ------------------------------>
988 -- Check the vendor btn the vendor present in tax lines and that of suggested vendor in requisition lines
989 -- If they are same or any one is null then insert po vendor else keep the vendor in tact.
990 -- Check if the document to be created is a Blanket Release / Standard Purchase Order.
991 -- Pick up Line Location Details as well.
992 -- Start of addition by Gsri on 07-MAR-2001
993 /* Bug 5243532. Added by Lakshmi Gopalsami
994 * Removed cursor Sob_cur as currency_code
995 * will be fetched via caching logic.
996 */
997
998
999 -- End of addition by Gsri on 07-MAR-2001
1000
1001 CURSOR Fetch_Hdr_Cur IS
1002 SELECT Po_Header_Id, Po_Line_Id, ( Price_Override * Quantity ) Total,
1003 Shipment_Type, Po_Release_Id, Source_Shipment_Id
1004 , quantity -- cbabu for Bug# 3051278
1005 , ship_to_organization_id, ship_to_location_id, price_override, Unit_Meas_Lookup_Code -- Vijay Shankar for Bug# 3193592
1006 FROM Po_Line_Locations_All
1007 WHERE Line_Location_Id = v_line_loc_id;
1008
1009 -- Start, Vijay Shankar for Bug# 3193592
1010 CURSOR c_inventory_org_id(p_ship_to_location_id IN NUMBER) IS
1011 SELECT Inventory_Organization_Id
1012 FROM Hr_Locations
1013 WHERE Location_Id = p_ship_to_location_id;
1014
1015 v_inventory_org_id HR_LOCATIONS.Inventory_Organization_Id%TYPE;
1016 v_line_loc_cnt NUMBER;
1017 v_ship_to_organization_id NUMBER;
1018 v_ship_to_location_id NUMBER;
1019 v_price_override PO_LINE_LOCATIONS_ALL.price_override%TYPE;
1020 v_unit_meas_lookup_code PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE;
1021 v_line_uom PO_LINES_ALL.unit_meas_lookup_code%TYPE;
1022 v_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
1023 v_quot_class_code PO_HEADERS_ALL.quotation_class_code%TYPE;
1024 -- End, Vijay Shankar for Bug# 3193592
1025
1026 CURSOR Fetch_Unit_Measure_Cur IS
1027 SELECT Unit_Meas_Lookup_Code
1028 FROM Po_Lines_All
1029 WHERE Po_Line_Id = v_po_line_id;
1030
1031 CURSOR Fetch_UomCode_Cur(p_uom IN VARCHAR2) IS
1032 SELECT Uom_Code
1033 FROM Mtl_Units_Of_Measure
1034 WHERE Unit_Of_Measure = p_uom;
1035
1036 -- Pick up vendor id for the corresponding Po_Header_Id
1037 CURSOR Fetch_Po_Vendor_Id_Cur( hdr_id IN NUMBER ) IS
1038 SELECT Vendor_Id, Vendor_SIte_Id, Currency_Code, Rate_Date, Rate_Type, Rate
1039 , type_lookup_code, quotation_class_code, -- Vijay Shankar for Bug# 3193592
1040 style_id --Added by Sanjikum for Bug#4483042
1041 FROM Po_Headers_All
1042 WHERE Po_Header_Id = hdr_id;
1043
1044 /* Bug 4513549. Added by LGOPALSA
1045 Commented the following cursor as it is
1046 not used anywhere
1047 CURSOR Fetch_Focus_Id_Cur( line_id IN NUMBER ) IS
1048 SELECT Line_Focus_Id
1049 FROM JAI_PO_LINE_LOCATIONS
1050 WHERE Po_Line_Id = line_id
1051 -- AND Line_Location_Id IS NULL
1052 AND ( Line_Location_Id IS NULL OR line_location_id = 0 ); -- cbabu for EnhancementBug# 2427465
1053 */
1054
1055 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1056 CURSOR Fetch_Taxes_Cur( rqlineid IN NUMBER ) IS
1057 SELECT Tax_Line_no,
1058 Precedence_1, Precedence_2, Precedence_3, Precedence_4, Precedence_5,
1059 Precedence_6, Precedence_7, Precedence_8, Precedence_9, Precedence_10,
1060 Tax_Id, Currency, Tax_Rate, Qty_Rate, UOM, Tax_Amount, Tax_Type, Modvat_Flag,
1061 Vendor_Id, Tax_Target_Amount,
1062 tax_category_id -- cbabu for EnhancementBug# 2427465
1063 FROM JAI_PO_REQ_LINE_TAXES
1064 WHERE requisition_line_id = rqlineid
1065 ORDER BY Tax_Line_No;
1066
1067 CURSOR Fetch_Mod_Cr_Cur( taxid IN NUMBER ) IS
1068 SELECT Tax_Type, Mod_Cr_Percentage, Vendor_Id
1069 , adhoc_flag -- Vijay Shankar for Bug# 2782356
1070 FROM JAI_CMN_TAXES_ALL
1071 WHERE Tax_Id = taxid;
1072
1073 CURSOR Fetch_Vendor_Id IS
1074 SELECT Vendor_Id
1075 FROM Po_Vendors
1076 WHERE Vendor_Name = v_sugg_vendor_name;
1077
1078 -- Get the header currency code
1079
1080
1081 CURSOR Tot_Amt_Cur IS
1082 SELECT SUM( NVL( Tax_Amount, 0 ) )
1083 FROM JAI_PO_TAXES
1084 WHERE line_location_id = v_line_loc_id
1085 AND Tax_Type <> jai_constants.tax_type_tds ; /* 'TDS'; Ramananda for removal of SQL LITERALs */
1086
1087 -- Start Modification by Gaurav for PO Autocreate issue 17-aug-00
1088 CURSOR Chk_localization_entry IS
1089 SELECT COUNT(REQUISITION_LINE_ID)
1090 FROM JAI_PO_REQ_LINES
1091 WHERE REQUISITION_LINE_ID = pr_new.REQUISITION_LINE_ID;
1092 -- End modification
1093
1094 -- Addition to check tax override flag on 24-apr-01 by RK and GSR
1095 CURSOR tax_override_flag_cur(c_vendor_id NUMBER, c_vendor_site_id NUMBER) IS
1096 SELECT override_flag
1097 FROM JAI_CMN_VENDOR_SITES
1098 WHERE vendor_id = c_vendor_id
1099 AND vendor_site_id = c_vendor_site_id;
1100
1101 v_override_flag VARCHAR2(1);
1102 -- End of addition to check tax override flag on 24-apr-01 by RK and GSR
1103
1104 -- cbabu for EnhancementBug# 2427465
1105 CURSOR c_get_tax_category_id(p_requisition_line_id IN NUMBER) IS
1106 SELECT tax_category_id
1107 FROM JAI_PO_REQ_LINES
1108 WHERE requisition_line_id = p_requisition_line_id;
1109
1110
1111 v_quantity NUMBER; -- cbabu for Bug# 3051278
1112
1113 -- Vijay Shankar for Bug# 2782356
1114 v_tax_amount NUMBER;
1115 v_adhoc_flag CHAR(1);
1116 -- v_tax_currency VARCHAR2(15);
1117 v_curr_conv_rate NUMBER;
1118
1119 v_debug BOOLEAN; --File.Sql.35 Cbabu := false;
1120 v_utl_file_name VARCHAR2(50);--File.Sql.35 Cbabu := 'ja_in_po_dflt_taxes.log';
1121 v_utl_location VARCHAR2(512);
1122 v_myfilehandle UTL_FILE.FILE_TYPE; -- This is for File handling
1123
1124 -- Bug 4513549. Added by LGOPALSA
1125 lv_tax_cnt NUMBER := 0;
1126 lv_tax_from_reqn_flag varchar2(1) := 'N';
1127
1128 /* Bug 5243532. Added by Lakshmi Gopalsami
1129 * Defined variable for implementing caching logic.
1130 */
1131 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1132 v_requisition_header_id NUMBER;
1133 v_reqn_tax NUMBER; --Added by Bgowrava for Bug#8766851
1134
1135 /* Bug 4513549. Fetch the line_focus_id.
1136 This is used when we are inserting taxes from
1137 requistion and ja_in_po_line_locations already exists */
1138
1139 CURSOR Fetch_Focus_Id_Cur_for_req( line_id IN NUMBER , line_loc_id in Number ) IS
1140 SELECT Line_Focus_Id
1141 FROM JAI_PO_LINE_LOCATIONS
1142 WHERE Po_Line_Id = line_id
1143 AND Line_Location_Id = line_loc_id;
1144
1145 Cursor fetch_Tax_cnt( cp_line_loc_id in number ) is
1146 select count(1)
1147 from JAI_PO_TAXES
1148 where line_location_id = cp_line_loc_id;
1149
1150 /* Bug 5243532. Added by Lakshmi Gopalsami
1151 * Removed cursors Fetch_Book_Id and used po_requisition_headers_v
1152 * in cursor Hdr_Curr_Cur instead of gl_sets_of_books
1153 * and implemented using caching logic.
1154 */
1155 CURSOR Fetch_Hdr_Curr_Cur IS
1156 SELECT NVL( Currency_Code, '$' )
1157 FROM Po_Requisition_Headers_V
1158 WHERE Requisition_Header_Id = v_requisition_header_id;
1159
1160 --added, Bgowrava for Bug#6084636
1161 Cursor c_get_tax_modified_flag IS
1162 SELECT tax_modified_flag
1163 FROM JAI_PO_LINE_LOCATIONS
1164 WHERE line_location_id = pr_new.line_location_id ;
1165 lv_tax_modified_flag VARCHAR2(1) ;
1166
1167 BEGIN
1168 pv_return_code := jai_constants.successful ;
1169 /*------------------------------------------------------------------------------------------------------------------
1170 Change History for ja_in_po_dflt_taxes_trg.sql
1171 S.No DD/MM/YYYY Details
1172 ------------------------------------------------------------------------------------------------------------------
1173 1 16/08/2002 SSUMAITH for Bug# 2504283
1174 unCommented the return statement because because it was causing the taxes to be picked up from
1175 requisition instead of blanket agreement.
1176 2 06/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.2
1177 tax_category_id column is populated into PO and SO localization tables, which will be used to
1178 identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into
1179 the tax table will be useful to identify whether the tax is a defaulted or a manual tax.
1180 A cursor is modified to fetch tax_category_id. UPDATE statement of JAI_PO_LINE_LOCATIONS is modified to update
1181 tax_category_id column with defaulting tax category
1182 3 04/02/2003 cbabu for Bug# 2782356, FileVersion# 615.3, Bug logged for IN60104
1183 Adhoc tax amounts are not defaulted from requisition taxes to STANDARD PO autocreated from requisition
1184
1185 4 22/07/2003 cbabu for Bug# 3051278, FileVersion# 616.1
1186 This fix is done to default the taxes onto PO Shipment line which got created through AutoCreate of
1187 Blanket Release from Purchase Requisition. If there is any change in UOM between
1188 requisition line and BPO Line, then PO Shipment quantity has to be given as input to jai_po_cmn_pkg.process_release_shipment
1189 procedure. But requisition quantity is going as input and this is causing the problem.
1190 Code is modified to pick PO Shipment quantity and give it as input to jai_po_cmn_pkg.process_release_shipment procedure
1191
1192 5 18/11/2003 Vijay Shankar for Bug# 3193592, FileVersion# 617.1
1193 when multiple requisitions are merged to form a single PO Shipment during Autocreation process, then this trigger
1194 is erroring out as taxes from multiple requisition lines are getting populated into the same line_location_id.
1195 This is resolved by defaulting taxes from setup with an API call to jai_po_tax_pkg.ja_in_po_case2 procedure
1196 instead of carrying taxes from requisition lines to PO Shipment
1197
1198 6.02/08/2004 ssumaith - bug# 3729015 file version 115.1
1199 commented call to jai_po_cmn_pkg.process_release_shipment because in the ja_in_po_tax_insert_trg
1200 a hook has been implemented which defaults the taxes from a requisition to the release.
1201 and JAINPOCR concurrent is anyway called from ja_in_po_tax_insert_trg , hence the presense
1202 of the call is superfluous.
1203
1204 Also ported the changes done as part of the one-off bug 3599268 into generic code path at two places
1205 in the code.
1206 7.12/Mar/2005 Bug 4210102 - Added by LGOPALSA Version 115.3
1207 (1) Added Customs and CVD education cess
1208 (2) Added checkfile in dbdrv
1209
1210 8. 14/03/2005 bug#4250072 rallamse Version# 115.4
1211 VAT implementation
1212
1213 9. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
1214 DB as required for CASE COMPLAINCE. Version 116.1
1215
1216 10. 13-Jun-2005 File Version: 116.2
1217 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1218
1219 11. 08-Jul-2005 Sanjikum for Bug#4483042
1220 1) Added a call to jai_cmn_utils_pkg.validate_po_type, to check whether for the current PO
1221 IL functionality should work or not.
1222
1223 12. 08-Jul-2005 Sanjikum for Bug#4483042, File version 117.2
1224 1) Added a new column in cursor - Fetch_Po_Vendor_Id_Cur
1225
1226 13. 17-Aug-2005 Ramananda for bug#4513549 during R12 sanity testing. jai_mfg_t.sql File Version 120.2
1227 Re-done the jai_po_rla_t1.sql 120.2 changes.
1228 Problem :
1229 ---------
1230 Existing taxes on PO were getting deleted and then getting
1231 inserted from the setup always whenever merge happens.
1232
1233 Fix:
1234 ----
1235 Checked whether tax already exists for PO for the current line location id
1236 If there exists atleast one line we will not copy the taxes from the PO
1237 Else we will copy the taxes from the current requisition which is getting
1238 merged.
1239
1240 Dependency Due To the current fix:-
1241 None
1242 14. 07-Nov-2008 JMEENA for bug#5394234
1243 Increased the length of variables v_sugg_vendor_name and v_sugg_vendor_locations from 80 to 360
1244
1245 15. 26-Aug-2009 Bgowrava for Bug#8766851 , File Version
1246 Issue: CAN'T AUTOCREATE PO FOR INDIA OPERATING ORG
1247 Fix: Introduced the v_reqn_tax variable to hold the value of number of taxes on the purchase requisition and if this is greater than zero then
1248 the code for calculating tax and updating JaI_Po_Line_Locations is done.
1249
1250 16. 10-Sep-2010 Jia for GST Bug#10091373.
1251
1252 17. 19-Apr-2011 Xiao for bug#12344603
1253 Issue: Exclusive tax amount is incorrect in autocreate po flow,
1254 In procedure ARU_T1, when jai_po_tax_pkg.calculate_tax is invoked, Assessable value
1255 is passed as line amount to calculate taxes.
1256 Fixed: In fact, line amount should be passed to calculate tax as basis.
1257
1258 ===============================================================================
1259 Dependencies
1260
1261 Version Author Dependencies Comments
1262 115.3 LGOPALSA IN60106 + Added CVD and Customs education cess
1263 4146708
1264
1265 115.4 rallamse IN60106 + For VAT implementation
1266 4146708 +
1267 4245089
1268
1269 120.2 RPOKKULA jai_po_da_t1.sql 120.2 (Functional)
1270 ------------------------------------------------------------------------------------------------------------------*/
1271
1272 --File.Sql.35 Cbabu
1273 v_requisition_line_id := pr_new.Requisition_Line_Id;
1274 v_sugg_vendor_name := pr_new.Suggested_Vendor_Name;
1275 v_dest_org_id := pr_new.Destination_Organization_Id;
1276 v_sugg_vendor_location := pr_new.Suggested_Vendor_Location;
1277 v_item_id := pr_new.Item_Id;
1278 v_line_loc_id := pr_new.Line_Location_Id;
1279 v_cre_dt := pr_new.Creation_Date;
1280 v_cre_by := pr_new.Created_By;
1281 v_last_upd_dt := pr_new.Last_Update_Date ;
1282 v_last_upd_by := pr_new.Last_Updated_By;
1283 v_last_upd_login := pr_new.Last_Update_Login;
1284 v_price := pr_new.Unit_Price;
1285 v_qty := pr_new.Quantity;
1286 v_curr := pr_new.Currency_Code;
1287 v_req_conv_rate := pr_new.Rate;
1288 v_req_conv_type := pr_new.Rate_Type;
1289 v_req_conv_date := pr_new.Rate_Date;
1290 DUMMY := 1;
1291 v_loc_count := 0; --new variable for loc chk on 17-aug-00
1292 v_debug := true;
1293 v_utl_file_name := 'ja_in_po_dflt_taxes.log';
1294 -- Bug 5243532. Added by Lakshmi Gopalsami
1295 v_requisition_header_id := pr_new.requisition_header_id;
1296
1297 IF v_debug THEN
1298 BEGIN
1299 pv_return_code := jai_constants.successful ;
1300 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL, Value,SUBSTR (value,1,INSTR(value,',') -1))
1301 INTO v_utl_location
1302 FROM v$parameter
1303 WHERE name = 'utl_file_dir';
1304
1305 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location, v_utl_file_name ,'A');
1306
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 v_debug := false;
1310 END;
1311
1312 END IF;
1313
1314
1315
1316 IF v_debug THEN
1317 UTL_FILE.PUT_LINE(v_myfilehandle,'********* Start Debug, TimeStamp -> '||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS'));
1318 UTL_FILE.PUT_LINE(v_myfilehandle, 'reqn_line_id -> '||pr_new.Requisition_Line_Id
1319 ||', line_loc_id -> '||pr_new.line_location_Id
1320 ||', dest_org_id -> '||pr_new.Destination_Organization_Id
1321 );
1322 END IF;
1323
1324 -- porting of bug 3599268 into generic code path
1325 --Start, Vijay Shankar for Bug# 3599268
1326
1327 /* Bug 5243532. Added by Lakshmi Gopalsami
1328 * Removed cursors Fetch_Book_Id and Sob_cur
1329 * and implemented using caching logic.
1330 */
1331 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1332 (p_org_id => v_dest_org_id );
1333 v_set_of_book_id := l_func_curr_det.ledger_id;
1334 v_currency_code := l_func_curr_det.currency_code;
1335 -- End of bug 5243532
1336
1337 /*IF pr_new.org_id IS NOT NULL THEN
1338 IF v_currency_code <> 'INR' THEN
1339 IF v_debug THEN
1340 utl_file.fclose(v_myfilehandle);
1341 END IF;
1342 RETURN;
1343 END IF;
1344 END IF;*/
1345 --End, Vijay Shankar for Bug# 3599268
1346 -- porting of bug 3599268 into generic code path
1347
1348
1349 -- Start Modification by sriram,gadde and subbu on 26-feb-01
1350 OPEN Fetch_Hdr_Cur;
1351 FETCH Fetch_Hdr_Cur INTO v_po_hdr_id, v_po_line_id, Line_Tot,
1352 v_shipment_type, v_po_rel_id, v_src_ship_id
1353 , v_quantity -- cbabu for Bug# 3051278
1354 , v_ship_to_organization_id, v_ship_to_location_id, v_price_override, v_unit_meas_lookup_code; -- Vijay Shankar for Bug# 3193592
1355 CLOSE Fetch_Hdr_Cur;
1356 -- end Modification by sriram,gadde and subbu on 26-feb-01
1357
1358 OPEN Fetch_Po_Vendor_Id_Cur( v_po_hdr_id );
1359 FETCH Fetch_Po_Vendor_Id_Cur INTO v_po_vendor_id, v_po_vendor_site_id, v_po_curr,
1360 v_po_conv_date, v_po_conv_type, v_po_conv_rate
1361 , v_type_lookup_code, v_quot_class_code, -- Vijay Shankar for Bug# 3193592
1362 v_style_id; --Added by Sanjikum for Bug#4483042
1363 CLOSE Fetch_Po_Vendor_Id_Cur;
1364
1365 --code added by Sanjikum for Bug#4483042
1366 IF jai_cmn_utils_pkg.validate_po_type(p_style_id => v_style_id) = FALSE THEN
1367 return;
1368 END IF;
1369
1370 -- Start Modification by Gaurav for PO Autocreate issue 17-aug-00
1371 OPEN Chk_localization_entry;
1372 FETCH Chk_localization_entry INTO v_loc_count;
1373 CLOSE Chk_localization_entry;
1374
1375 /* Bug 5243532. Added by Lakshmi Gopalsami
1376 * Removed cursor Fetch_Book_Id
1377 * as this is duplication of the earlier values fetched
1378 * Now this is derived using caching logic.
1379 */
1380
1381 OPEN Fetch_Hdr_Curr_Cur;
1382 FETCH Fetch_Hdr_Curr_Cur INTO v_hdr_curr;
1383 CLOSE Fetch_Hdr_Curr_Cur;
1384
1385 v_curr := NVL( v_curr, v_hdr_curr);
1386
1387 OPEN Fetch_Unit_Measure_Cur;
1388 -- FETCH Fetch_Unit_Measure_Cur INTO v_uom_code;
1389 FETCH Fetch_Unit_Measure_Cur INTO v_line_uom;
1390 CLOSE Fetch_Unit_Measure_Cur;
1391
1392 OPEN Fetch_UomCode_Cur(v_line_uom);
1393 FETCH Fetch_UomCode_Cur INTO v_uom_code;
1394 CLOSE Fetch_UomCode_Cur;
1395
1396 --Addition to check tax override flag on 24-apr-01 by RK and GSR
1397 OPEN tax_override_flag_cur(v_po_vendor_id, v_po_vendor_site_id);
1398 FETCH tax_override_flag_cur INTO v_override_flag;
1399 CLOSE tax_override_flag_cur;
1400
1401 IF NVL(v_override_flag,'N') = 'Y' AND v_shipment_type <> 'BLANKET' THEN
1402 IF v_debug THEN
1403 utl_file.fclose(v_myfilehandle);
1404 END IF;
1405 RETURN;
1406 END IF;
1407
1408 --End of addition to check override flag on 24-apr-01 by RK and GSR
1409 --Start of addition by subbu AND gadde on 26-FEB-01
1410
1411 IF v_shipment_type <> 'BLANKET' THEN
1412 IF NVL(v_loc_count,0) = 0 THEN
1413 IF v_debug THEN
1414 utl_file.fclose(v_myfilehandle);
1415 END IF;
1416 RETURN;
1417 END IF;
1418
1419 --End of addition by subbu AND gadde on 26-FEB-01
1420 ELSE
1421
1422 /*
1423 commented by sriram,gadde and subbu on 26-feb-01
1424 OPEN Fetch_Hdr_Cur;
1425 FETCH Fetch_Hdr_Cur INTO v_po_hdr_id, v_po_line_id, Line_Tot, v_shipment_type, v_po_rel_id, v_src_ship_id;
1426 CLOSE Fetch_Hdr_Cur;
1427 */
1428 -- IF v_shipment_type = 'BLANKET' THEN --commented by gadde and subbu
1429 -- Start of addition by Gsri on 07-MAR-2001
1430 /* Porting of Bug# 3599268 into generic code path. This check here is causing to fire for even Other functional currency PO's
1431 so, shifted this code from here to top
1432
1433 OPEN Sob_cur;
1434 FETCH Sob_cur INTO v_currency_code;
1435 CLOSE Sob_cur;
1436
1437 IF pr_new.org_id IS NOT NULL THEN
1438 IF v_currency_code <> 'INR' THEN
1439 IF v_debug THEN
1440 utl_file.fclose(v_myfilehandle);
1441 END IF;
1442 RETURN;
1443 END IF;
1444
1445 END IF;
1446 */
1447 -- End of addition by Gsri on 07-MAR-2001
1448
1449 if v_debug THEN
1450 UTL_FILE.PUT_LINE(v_myfilehandle,'777 jai_po_cmn_pkg.process_release_shipment ('''||'BLANKET'||''''
1451 ||','||v_src_ship_id || v_line_loc_id||',' ||v_po_line_id||',' ||v_po_hdr_id
1452 ||','||v_quantity||',' ||v_po_rel_id
1453 ||',' ||''''||TO_CHAR(v_cre_dt,'DD/MM/RRRR HH24:MI:SS')||''''||',' ||v_cre_by
1454 ||',' ||''''||TO_CHAR(v_last_upd_dt,'DD/MM/RRRR HH24:MI:SS')||''''||',' ||v_last_upd_by
1455 ||',' ||v_last_upd_login||' );'
1456
1457 );
1458 end if;
1459
1460 /*
1461 commented by ssumaith - bug# 3729015 file version 115.2
1462 because in the ja_in_po_tax_insert_trg, because of a hook ,
1463 the taxes JAINPOCR concurrent is anyway called from ja_in_po_tax_insert_trg
1464
1465
1466 jai_po_cmn_pkg.process_release_shipment ( 'BLANKET',
1467 v_src_ship_id,
1468 v_line_loc_id,
1469 v_po_line_id,
1470 v_po_hdr_id,
1471 -- v_qty, -- commented by cbabu for Bug# 3051278
1472 v_quantity, -- cbabu for Bug# 3051278
1473 v_po_rel_id,
1474 v_cre_dt,
1475 v_cre_by,
1476 v_last_upd_dt,
1477 v_last_upd_by,
1478 v_last_upd_login );
1479
1480 */
1481 IF v_debug THEN
1482 utl_file.fclose(v_myfilehandle);
1483 END IF;
1484
1485 --This Return is commented by Nagaraj.s on 09/05/2002
1486 --As this is stopping insertion of Taxes into Ja_IN_Po_line_location_taxes_trg(Bug#2364148)
1487 RETURN; -- uncomented by sriram bug # 2504283 base bug 2335923 16-aug-2002
1488 --Ends here................
1489 END IF;
1490
1491 IF v_sugg_vendor_name IS NULL THEN
1492 v_sugg_vendor_id := -999; -- Means sugg vendor is null
1493 ELSE
1494 OPEN Fetch_Vendor_Id;
1495 FETCH Fetch_Vendor_Id INTO v_sugg_vendor_id;
1496 CLOSE Fetch_Vendor_Id;
1497 END IF;
1498
1499 -- Vijay Shankar for Bug# 3193592
1500 select count(1) into v_line_loc_cnt from JAI_PO_LINE_LOCATIONS
1501 where line_location_id = pr_new.line_location_id;
1502
1503 IF v_debug THEN -- bug 7218695. Added by Lakshmi Gopalsami
1504 UTL_FILE.PUT_LINE(v_myfilehandle, 'pr_new.line_location_id '|| pr_new.line_location_id);
1505 UTL_FILE.PUT_LINE(v_myfilehandle, 'v_line_loc_cnt '|| v_line_loc_cnt);
1506 END IF;
1507
1508 -- this means multiple requisition lines are merged into a single PO Shipment line
1509 -- so the taxes should get defaulted from SET UP's rather than carrying the reqn taxes to PO Shipment
1510
1511 /* Bug 4513549 Added by Lakshmi Gopalsami */
1512
1513 IF v_line_loc_cnt > 0 THEN
1514
1515 IF v_debug THEN -- bug 7218695. Added by Lakshmi Gopalsami
1516 UTL_FILE.PUT_LINE(v_myfilehandle, 'Into the IF Condition');
1517 END IF;
1518
1519 --START, added, Bgowrava for Bug#6084636
1520 OPEN c_get_tax_modified_flag ;
1521 FETCH c_get_tax_modified_flag INTO lv_tax_modified_flag ;
1522 CLOSE c_get_tax_modified_flag;
1523
1524
1525 IF NVL(lv_tax_modified_flag,'N') = 'N' THEN --added, Bgowrava for Bug#6084636
1526
1527 DELETE FROM JAI_PO_TAXES
1528 WHERE line_location_id = pr_new.line_location_id;
1529
1530 IF v_debug THEN -- bug 7218695. Added by Lakshmi Gopalsami
1531
1532 UTL_FILE.PUT_LINE(v_myfilehandle, 'Deleted Taxes');
1533 END IF;
1534
1535 OPEN c_inventory_org_id( v_ship_to_location_id );
1536 FETCH c_inventory_org_id INTO v_inventory_org_id;
1537 CLOSE c_inventory_org_id;
1538
1539
1540 jai_po_tax_pkg.ja_in_po_case2(v_type_lookup_code,
1541 v_quot_class_code,
1542 v_po_vendor_id,
1543 v_po_vendor_site_id,
1544 v_po_curr,
1545 v_inventory_org_id,
1546 v_item_id,
1547 v_line_loc_id,
1548 v_po_hdr_id,
1549 v_po_line_id,
1550 v_price_override,
1551 v_quantity,
1552 v_cre_dt,
1553 v_cre_by,
1554 v_last_upd_dt,
1555 v_last_upd_by,
1556 v_last_upd_login,
1557 nvl(v_unit_meas_lookup_code, v_line_uom),
1558 FLAG => NULL,
1559 P_VAT_ASSESS_VALUE => NULL -- Added by rallamse bug#4250072 VAT
1560 -- , pn_gst_assessable_value => NULL -- Added by Jia for GST Bug#10091373 on 2010/09/10
1561 );
1562
1563
1564 IF v_debug THEN
1565 UTL_FILE.PUT_LINE(v_myfilehandle, 'Reqn Lines Merge returning Successfully' );
1566 utl_file.fclose(v_myfilehandle);
1567 END IF;
1568 END IF ;
1569 return;
1570 END IF;
1571 --END, added, Bgowrava for Bug#6084636
1572
1573
1574 /*
1575 ||Po lines does not exists .
1576 ||Insert the line locations from requisition into po using the procedure below
1577 */
1578
1579 jai_po_cmn_pkg.insert_line( 'STANDARD',
1580 v_line_loc_id, -- Bug 4513549
1581 v_po_hdr_id,
1582 v_po_line_id,
1583 v_cre_dt,
1584 v_cre_by,
1585 v_last_upd_dt,
1586 v_last_upd_by,
1587 v_last_upd_login,
1588 'I' );
1589
1590 IF v_debug THEN
1591 UTL_FILE.PUT_LINE(v_myfilehandle, 'line focus id '|| v_line_focus_id);
1592 END IF;
1593
1594
1595
1596
1597 /* Bug 4513549. Added by LGOPALSA
1598 Fetch the line_focus_id for the current line and current
1599 line location id */
1600
1601 Open Fetch_Focus_Id_Cur_for_req( v_po_line_id,v_line_loc_id);
1602 Fetch Fetch_Focus_Id_Cur_for_req INTO v_line_focus_id;
1603 Close Fetch_Focus_Id_Cur_for_req;
1604
1605 IF v_debug THEN
1606 UTL_FILE.PUT_LINE(v_myfilehandle, 'line focus id '|| v_line_focus_id);
1607 END IF;
1608
1609 IF v_debug THEN
1610 UTL_FILE.PUT_LINE(v_myfilehandle, '2-1 v_line_loc_cnt -> '||v_line_loc_cnt
1611 );
1612 END IF;
1613
1614 v_reqn_tax := 0; --Added by Bgowrava for Bug#8766851
1615 OPEN Fetch_Taxes_Cur( v_requisition_line_id );
1616 LOOP
1617
1618 FETCH Fetch_Taxes_Cur INTO v_tax_line_no, v_prec1, v_prec2, v_prec3, v_prec4, v_prec5,
1619 v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
1620 v_taxid, v_t_curr, v_tax_rate, v_qty_rate, v_uom, v_tax_amt,
1621 v_tax_type, v_mod_flag, v_vendor2_id, v_tax_target_amt,
1622 v_tax_category_id; -- cbabu for EnhancementBug# 2427465
1623
1624 EXIT WHEN Fetch_Taxes_Cur%NOTFOUND;
1625 v_reqn_tax := v_reqn_tax + 1; --Added by Bgowrava for Bug#8766851
1626
1627 OPEN Fetch_Mod_Cr_Cur( v_taxid );
1628 FETCH Fetch_Mod_Cr_Cur INTO v_tax_type, v_mod_cr, v_vendor1_id
1629 , v_adhoc_flag; -- Vijay Shankar for Bug# 2782356
1630 CLOSE Fetch_Mod_Cr_Cur;
1631
1632 IF v_debug THEN
1633 UTL_FILE.PUT_LINE(v_myfilehandle, '2 tax_id -> '||v_taxid
1634 ||', v_t_curr -> '||v_t_curr
1635 ||', v_tax_amt -> '||v_tax_amt
1636 );
1637 END IF;
1638
1639 -- Start, Vijay Shankar for Bug# 2782356
1640 IF nvl(v_adhoc_flag, 'N') = 'Y' THEN
1641 IF v_t_curr <> v_po_curr THEN
1642 -- Vijay Shankar for Bug# 2782356
1643 IF v_debug THEN
1644 UTL_FILE.PUT_LINE(v_myfilehandle, '3 jai_cmn_utils_pkg.currency_conversion('||v_set_of_book_id
1645 ||', '''||v_po_curr
1646 ||''', to_date('||v_po_conv_date
1647 ||','''||'''DD/MM/YYYY''||''), '''||v_po_conv_type
1648 ||''', '||v_po_conv_rate ||');'
1649 );
1650 END IF;
1651
1652 IF v_debug THEN
1653 UTL_FILE.PUT_LINE(v_myfilehandle, '5 v_curr_conv_rate -> '||v_curr_conv_rate );
1654 END IF;
1655
1656 -- as tax_currency is same as functional currency and v_curr_conv_rate contains conversion from v_po_curr to func_curr
1657 IF v_t_curr = v_currency_code THEN
1658 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion( v_set_of_book_id , v_po_curr , v_po_conv_date , v_po_conv_type, v_po_conv_rate );
1659
1660 v_tax_amount := v_tax_amt / v_curr_conv_rate;
1661 IF v_debug THEN
1662 UTL_FILE.PUT_LINE(v_myfilehandle, '5.1 multiply ');
1663 END IF;
1664
1665 ELSE -- now tax_currency is not equal to functional currency, so division is fine
1666 v_curr_conv_rate := jai_cmn_utils_pkg.currency_conversion( v_set_of_book_id , v_t_curr , v_po_conv_date , v_po_conv_type, v_po_conv_rate );
1667 v_tax_amount := v_tax_amt * v_curr_conv_rate;
1668 IF v_debug THEN
1669 UTL_FILE.PUT_LINE(v_myfilehandle, '5.2 divide' );
1670 END IF;
1671
1672 END IF;
1673
1674 v_curr_conv_rate := null;
1675 ELSE
1676 v_tax_amount := v_tax_amt;
1677 END IF;
1678 ELSE
1679 v_tax_amount := 0;
1680 END IF;
1681 -- End, Vijay Shankar for Bug# 2782356
1682
1683 IF v_mod_cr IS NOT NULL AND v_mod_cr > 0 THEN
1684 v_mod_flag := 'Y';
1685 ELSE
1686 v_mod_flag := 'N';
1687 END IF;
1688
1689 /* Added by LGOPALSA. Bug 4210102
1690 * Added Customs and CVD Education Cess */
1691
1692 IF upper(v_tax_type) IN ( 'CUSTOMS', 'CVD',
1693 jai_constants.tax_type_add_cvd , -- Date 03/11/2006 Bug 5228046 added by SACSETHI
1694 jai_constants.tax_type_customs_edu_Cess,JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,
1695 jai_constants.tax_type_cvd_edu_cess,JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS) --Added SH related entries by Bgowrava for forward porting bug#5989740
1696 THEN
1697 v_vendor_id := NULL;
1698 ELSIF v_tax_type = 'TDS' THEN
1699 v_vendor_id := v_vendor1_id;
1700 ELSE
1701 IF NVL( v_vendor2_id, -999 ) = v_sugg_vendor_id THEN
1702 v_vendor_id := v_po_vendor_id;
1703 ELSE
1704 v_vendor_id := v_vendor2_id;
1705 END IF;
1706 END IF;
1707
1708 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1709 INSERT INTO JAI_PO_TAXES(
1710 line_focus_id, line_location_id, tax_line_no, po_line_id, po_header_id,
1711 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
1712 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
1713 tax_id, currency, tax_rate, qty_rate, uom,
1714 tax_amount, tax_type, modvat_flag, vendor_id, tax_target_amount,
1715 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
1716 tax_category_id -- cbabu for EnhancementBug# 2427465
1717 ) VALUES (
1718 v_line_focus_id, v_line_loc_id, v_tax_line_no, v_po_line_id, v_po_hdr_id,
1719 v_prec1, v_prec2, v_prec3, v_prec4, v_prec5,
1720 v_prec6, v_prec7, v_prec8, v_prec9, v_prec10,
1721 v_taxid, NVL( v_po_curr, NVL( v_curr, v_hdr_curr ) ), v_tax_rate, v_qty_rate, v_uom,
1722 -- 0, v_tax_type, v_mod_flag, v_vendor_id, 0,
1723 v_tax_amount, v_tax_type, v_mod_flag, v_vendor_id, 0, -- Vijay Shankar for Bug# 2782356
1724 -- v_taxid, v_tax_currency, v_tax_rate, v_qty_rate, v_uom,
1725 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login,
1726 v_tax_category_id -- cbabu for EnhancementBug# 2427465
1727 );
1728
1729 -- Vijay Shankar for Bug# 2782356
1730 v_tax_amt := 0;
1731 v_tax_amount := 0;
1732 v_adhoc_flag := null;
1733 -- v_tax_currency := null;
1734
1735
1736 END LOOP;
1737
1738 CLOSE Fetch_Taxes_Cur;
1739
1740 if v_reqn_tax > 0 then --Added by Bgowrava for Bug#8766851
1741 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value( v_po_vendor_id, v_po_vendor_site_id, v_item_id, v_uom_code );
1742
1743 IF NVL( v_assessable_value, 0 ) > 0 THEN
1744 -- Bug 4513549. Added by LGOPALSA
1745 v_assessable_value := v_assessable_value * v_quantity;
1746 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, v_assessable_value, v_po_curr, v_curr_conv_factor );
1747 ELSE
1748 v_assessable_value := Line_Tot;
1749 END IF;
1750
1751 /* begin rallamse bug#4250072 for VAT */
1752 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value (
1753 p_party_id => v_po_vendor_id,
1754 p_party_site_id => v_po_vendor_site_id,
1755 p_inventory_item_id => v_item_id,
1756 p_uom_code => v_uom_code,
1757 p_default_price => 0,
1758 p_ass_value_date => trunc(sysdate) ,
1759 p_party_type => 'V'
1760 );
1761
1762 IF NVL( ln_vat_assess_value , 0 ) = 0 THEN
1763 ln_vat_assess_value := Line_tot ;
1764 ELSE
1765 -- Bug 4513549.
1766 -- Added by LGOPALSA. Fix
1767 ln_vat_assess_value := ln_vat_assess_value * v_quantity;
1768 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, ln_vat_assess_value, v_po_curr, v_curr_conv_factor );
1769 END IF ;
1770 /* end rallamse bug#4250072 for VAT */
1771 /*
1772 -- Added by Jia for GST Bug#10091373 on 2010/09/10, Begin
1773 ------------------------------------------------------------------------------------------------
1774 ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value (
1775 p_party_id => v_po_vendor_id,
1776 p_party_site_id => v_po_vendor_site_id,
1777 p_inventory_item_id => v_item_id,
1778 p_uom_code => v_uom_code,
1779 p_default_price => 0,
1780 p_ass_value_date => trunc(sysdate) ,
1781 p_party_type => 'V'
1782 );
1783
1784 IF NVL( ln_gst_assessable_value , 0 ) = 0
1785 THEN
1786 ln_gst_assessable_value := Line_tot ;
1787 ELSE
1788 ln_gst_assessable_value := ln_gst_assessable_value * v_quantity;
1789 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, ln_gst_assessable_value, v_po_curr, v_curr_conv_factor );
1790 END IF ;
1791 */
1792 ------------------------------------------------------------------------------------------------
1793 -- Added by Jia for GST Bug#10091373 on 2010/09/10, End
1794
1795 jai_po_cmn_pkg.Ja_In_Po_Func_Curr( v_po_hdr_id, DUMMY, v_po_curr, v_curr_conv_factor); -- TO get the conversion rate .
1796
1797 /* Added ln_vat_assess_value for bug# for VAT */
1798 jai_po_tax_pkg.calculate_tax( 'STANDARDPO',
1799 v_po_hdr_id ,
1800 v_po_line_id,
1801 v_line_loc_id,
1802 -- Bug 4513549. Added by LGOPALSA
1803 v_quantity,
1804 line_tot,
1805 v_uom_code,
1806 line_tot, --v_assessable_value,--Modified by Xiao for bug#12344603.
1807 v_assessable_value,
1808 ln_vat_assess_value,
1809 NULL,
1810 v_curr_conv_factor
1811 -- , pn_gst_assessable_value => ln_gst_assessable_value
1812 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
1813
1814
1815 /* Bug 4513549. Added by LGOPALSA
1816 Removed the commented call to ja_in_po_calc_tax */
1817
1818 OPEN Tot_Amt_Cur;
1819 FETCH Tot_Amt_Cur INTO v_tax_amt;
1820 CLOSE Tot_Amt_Cur;
1821
1822
1823
1824 -- cbabu for EnhancementBug# 2427465
1825 OPEN c_get_tax_category_id(pr_new.REQUISITION_LINE_ID);
1826 FETCH c_get_tax_category_id INTO v_tax_category_id_holder;
1827 CLOSE c_get_tax_category_id;
1828
1829
1830
1831 UPDATE JaI_Po_Line_Locations
1832 SET Tax_Amount = NVL( v_tax_amt, 0 ),
1833 Total_Amount = NVL( Line_Tot, 0 ) + NVL( v_tax_amt, 0 ),
1834 Last_Updated_By = v_last_upd_by,
1835 Last_Update_Date = v_last_upd_dt,
1836 Last_Update_Login = v_last_upd_login,
1837 tax_category_id = v_tax_category_id_holder /*added by ssawant for bug 6134111*/
1838 WHERE Po_Line_Id = v_po_line_id
1839 AND Line_Location_Id = v_line_Loc_id;
1840
1841 end if; --Added by Bgowrava for Bug#8766851
1842
1843 IF v_debug THEN
1844 UTL_FILE.PUT_LINE(v_myfilehandle, '*******End*******');
1845 utl_file.fclose(v_myfilehandle);
1846 END IF;
1847
1848 exception
1849 when others then
1850 IF v_debug THEN
1851 UTL_FILE.PUT_LINE(v_myfilehandle, '4 Error in procedure, errm -> '||SQLERRM
1852 );
1853 utl_file.fclose(v_myfilehandle);
1854 END IF;
1855 RAISE;
1856 END ARU_T1 ;
1857
1858 /*
1859 REM +======================================================================+
1860 REM NAME ARU_T2
1861 REM
1862 REM DESCRIPTION Called from trigger JAI_PO_RLA_ARIUD_T1
1863 REM
1864 REM NOTES Refers to old trigger JAI_PO_RLA_ARU_T3
1865 REM
1866 REM +======================================================================+
1867 */
1868 PROCEDURE ARU_T2 ( 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
1869 --v_row_id ROWID ; --File.Sql.35 Cbabu := pr_new.ROWID;
1870 v_dest_org_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Destination_Organization_Id;
1871 v_currency VARCHAR2(15); --File.Sql.35 Cbabu := pr_new.Currency_Code;
1872 v_rate_type VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.Rate_Type;
1873 v_rate_date DATE ; --File.Sql.35 Cbabu := pr_new.Rate_Date;
1874 v_rate NUMBER ; --File.Sql.35 Cbabu := pr_new.Rate;
1875 v_header_id NUMBER ; --File.Sql.35 Cbabu := pr_new.requisition_header_id;
1876 v_line_id NUMBER ; --File.Sql.35 Cbabu := pr_new.requisition_line_id;
1877 v_sugg_vendor_name VARCHAR2(360); --File.Sql.35 Cbabu := pr_new.suggested_vendor_name; --Increased the length 80 to 360 by JMEENA for bug#5394234
1878 v_sugg_vendor_loc VARCHAR2(360); --File.Sql.35 Cbabu := pr_new.suggested_vendor_location; --Increased the length 80 to 360 by JMEENA for bug#5394234
1879 v_inventory_item_id NUMBER ; --File.Sql.35 Cbabu := pr_new.item_id;
1880 v_src_org_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Source_Organization_Id;
1881 v_uom VARCHAR2(30) ; --File.Sql.35 Cbabu := pr_new.Unit_Meas_Lookup_Code;
1882 v_quantity NUMBER ; --File.Sql.35 Cbabu := pr_new.Quantity;
1883 v_line_tax_amount NUMBER ; --File.Sql.35 Cbabu := NVL( (pr_new.quantity * pr_new.unit_price) ,0 );
1884 v_line_amount NUMBER ; --File.Sql.35 Cbabu := NVL( ( pr_new.quantity * pr_new.unit_price) ,0 );
1885 v_creation_date DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
1886 v_created_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Created_By;
1887 v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date;
1888 v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
1889 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
1890
1891 v_type_lookup_code Po_Requisition_Headers_All.Type_Lookup_Code % TYPE;
1892 v_tax_category_id NUMBER;
1893 v_org_id NUMBER ; --File.Sql.35 Cbabu := 0;
1894 v_deliver_to_loc_id NUMBER;--Added by Xiao Lv for bug 10043656
1895 -- lv_enable_gst_flag VARCHAR2(3);--Added by Xiao Lv for bug 10043656
1896 found BOOLEAN;
1897 v_vendor_id NUMBER;
1898 v_site_id NUMBER;
1899 v_hdr_curr VARCHAR2(15);
1900 v_tax_flag VARCHAR2(1);
1901 v_seg_id VARCHAR2(20);
1902 v_tax_amount NUMBER;
1903 v_uom_code VARCHAR2(3);
1904 conv_rate NUMBER;
1905 v_assessable_value NUMBER;
1906 ln_vat_assess_value NUMBER; -- Ravi for VAT
1907 -- ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10091373 on 2010/09/10
1908 v_gl_set_of_bks_id NUMBER;
1909 v_hook_value VARCHAR2(10) ; /* rallamse bug#4479131 PADDR Elimination */
1910 ln_tax_amount NUMBER; -- pramasub FP
1911
1912 ------------- added by Gsr 12-jul-01
1913 v_operating_id number; --File.Sql.35 Cbabu :=pr_new.ORG_ID;
1914
1915 /* Bug 5243532. Added by Lakshmi Gopalsami
1916 * Defined variable for implementing caching logic.
1917 */
1918 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1919
1920 CURSOR Fetch_Org_Id_Cur IS SELECT NVL(Operating_Unit,0)
1921 FROM Org_Organization_Definitions
1922 WHERE Organization_Id = v_dest_org_id;
1923
1924 CURSOR bind_cur IS
1925 SELECT Segment1, Type_Lookup_Code, apps_source_code -- pramasub FP
1926 FROM Po_Requisition_Headers_All
1927 WHERE Requisition_Header_Id = v_header_id;
1928
1929 CURSOR vend_cur(p_sugg_vendor_name IN VARCHAR2) IS
1930 SELECT vendor_id
1931 FROM po_vendors
1932 WHERE vendor_name = p_sugg_vendor_name;
1933
1934 CURSOR site_cur(p_sugg_vendor_loc IN VARCHAR2) IS
1935 SELECT Vendor_Site_Id
1936 FROM Po_Vendor_Sites_All A
1937 WHERE A.Vendor_Site_Code = p_sugg_vendor_loc
1938 AND A.Vendor_Id = v_vendor_id
1939 AND (A.Org_Id = v_org_id
1940 OR
1941 (A.Org_Id is NULL AND v_org_id is NULL));
1942 -- AND NVL(A.Org_Id,0) = NVL(v_org_id,0);
1943
1944 CURSOR tax_rate_cur(p_tax_id IN NUMBER) IS
1945 SELECT Tax_Rate, Tax_Amount, Uom_Code, Tax_Type
1946 FROM JAI_CMN_TAXES_ALL
1947 WHERE Tax_Id = p_tax_id;
1948
1949 CURSOR Fetch_Hdr_Curr_Cur IS SELECT NVL( Currency_Code, '$' )
1950 FROM Po_Requisition_Headers_V
1951 WHERE Requisition_Header_Id = v_header_id;
1952
1953 /* Bug 5243532. Addd by Lakshmi Gopalsami
1954 * Removed the cursor Fetch_Book_Id_Cur
1955 * and implemented the same using caching logic.
1956 */
1957
1958 CURSOR Fetch_Uom_Code_Cur IS SELECT Uom_Code
1959 FROM Mtl_Units_Of_Measure
1960 WHERE Unit_Of_Measure = v_uom;
1961
1962 CURSOR Fetch_Mod_Flag_Cur IS Select Tax_modified_Flag
1963 From JAI_PO_REQ_LINES
1964 Where Requisition_Line_Id = v_line_id;
1965
1966 lv_apps_source_code VARCHAR2(20);
1967 /*rchandan for bug#5852041.. start*/ --pramasub FP end
1968 v_blanket_hdr NUMBER;
1969 v_blanket_line NUMBER;
1970 v_po_line_id NUMBER;
1971 v_line_location_id NUMBER;
1972 v_reqn_ctr NUMBER;
1973
1974
1975 CURSOR cur_bpa_tax_lines(p_po_line_id IN NUMBER,p_line_location_id IN NUMBER) IS
1976 SELECT a.Po_Line_Id ,
1977 a.tax_line_no lno ,
1978 a.tax_id ,
1979 a.precedence_1 p_1 ,
1980 a.precedence_2 p_2 ,
1981 a.precedence_3 p_3 ,
1982 a.precedence_4 p_4 ,
1983 a.precedence_5 p_5 ,
1984 a.precedence_6 p_6 ,
1985 a.precedence_7 p_7 ,
1986 a.precedence_8 p_8 ,
1987 a.precedence_9 p_9 ,
1988 a.precedence_10 p_10 ,
1989 a.currency ,
1990 a.tax_rate ,
1991 a.qty_rate ,
1992 a.uom ,
1993 a.tax_amount ,
1994 a.tax_type ,
1995 a.vendor_id ,
1996 a.modvat_flag ,
1997 tax_category_id
1998 --FROM Ja_In_Po_Line_Location_Taxes a
1999 FROM JAI_PO_TAXES a
2000 WHERE po_line_id = p_po_line_id
2001 AND nvl(line_location_id,-999) = p_line_location_id
2002 ORDER BY a.tax_line_no;
2003
2004 CURSOR c_reqn_line_id(p_reqn_line_id Number) is
2005 SELECT 1
2006 --FROM ja_in_reqn_tax_lines
2007 FROM JAI_PO_REQ_LINE_TAXES
2008 WHERE requisition_line_id = p_reqn_line_id;
2009
2010 v_unit_price NUMBER;
2011
2012 /*rchandan for bug#5852041.. end*/ --pramasub FP end
2013
2014 BEGIN
2015 pv_return_code := jai_constants.successful ;
2016 /*------------------------------------------------------------------------------------------
2017 CHANGE HISTORY: FILENAME: Ja_In_Reqn_Tax_Update_Trg.sql
2018 S.No Date Author and Details
2019 ------------------------------------------------------------------------------------------
2020 1 16/08/2002 Nagaraj.s for Bug#2508790
2021 Previously the Coding was
2022 OPEN Fetch_Book_Id_Curr ;
2023 FETCH Fetch_Book_Id_Cur INTO v_gl_set_of_books_id;
2024 CLOSE Fetch_Book_Id_Curr;
2025 The Coding is changed as
2026 OPEN Fetch_Book_Id_Curr ;
2027 FETCH Fetch_Book_Id_Curr INTO v_gl_set_of_books_id;
2028 CLOSE Fetch_Book_Id_Curr;
2029
2030 2 10/10/2003 Vijay Shankar for Bug# 3190872, FileVersion: 616.1
2031 Call to jai_po_tax_pkg.calculate_tax is not invoked properly, which is made proper with this fix
2032
2033 3. 30/11/2005 Aparajita for bug#4036241. Version#115.1
2034
2035 Introduced the call to centralized packaged procedure,
2036 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
2037
2038 5. 17/mar-2005 Rchandan for bug#4245365 Version#115.3
2039 Changes made to calculate VAT assessable value . This vat assessable is passed
2040 to the procedure that calculates the VAT related taxes
2041
2042 6. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
2043 DB as required for CASE COMPLAINCE. Version 116.1
2044
2045 7. 13-Jun-2005 File Version: 116.2
2046 Ramananda for bug#4428980. Removal of SQL LITERALs is done
2047
2048 8 06-Jul-2005 rallamse for bug# PADDR Elimination
2049 1. Replaced call to jai_po_cmn_pkg.query_locator_for_line with
2050 jai_cmn_hook_pkg.Po_Requisition_Lines_All
2051
2052 9 04-Aug-2005 P1 Build Issue bug# 4535701 by Ramananda. File Version 120.2
2053 Commented the columns Event_Id and Line_Number.
2054 These will be uncommented once PO team's po_requisition_lines_all table has these columns
2055 10 17-sep-05 p1 bug 4616729 - ssumaith - file version 120.3
2056 event_id and line_number columns have been commented and null is passes instead in calls to various hook packages
2057
2058 02/11/2006 for Bug 5228046, File version 120.2
2059 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
2060 This bug has datamodel and spec changes.
2061
2062 12. 07-Nov-2008 JMEENA for bug#5394234
2063 Increased the length of variables v_sugg_vendor_name and v_sugg_vendor_loc from 80 to 360
2064
2065 13. 10-Sep-2010 Jia for GST Bug#10091373.
2066
2067 Dependency:
2068 ----------
2069
2070 Sl No. Bug Dependent on
2071 Bug/Patch set Details
2072 -------------------------------------------------------------------------------------------------
2073 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
2074 4033992.
2075 ja_in_util_pkg_s.sql 115.0
2076 ja_in_util_pkg_b.sql 115.0
2077 2. 4245365 4245089 VAT implementation
2078
2079 --------------------------------------------------------------------------------------------*/
2080
2081 --File.Sql.35 Cbabu
2082 --v_row_id := pr_new.ROWID;
2083 v_dest_org_id := pr_new.Destination_Organization_Id;
2084 v_currency := pr_new.Currency_Code;
2085 v_rate_type := pr_new.Rate_Type;
2086 v_rate_date := pr_new.Rate_Date;
2087 v_rate := pr_new.Rate;
2088 v_header_id := pr_new.requisition_header_id;
2089 v_line_id := pr_new.requisition_line_id;
2090 v_sugg_vendor_name := pr_new.suggested_vendor_name;
2091 v_sugg_vendor_loc := pr_new.suggested_vendor_location;
2092 v_inventory_item_id := pr_new.item_id;
2093 v_src_org_id := pr_new.Source_Organization_Id;
2094 v_uom := pr_new.Unit_Meas_Lookup_Code;
2095 v_quantity := pr_new.Quantity;
2096 v_line_tax_amount := NVL( (pr_new.quantity * pr_new.unit_price) ,0 );
2097 v_line_amount := NVL( ( pr_new.quantity * pr_new.unit_price) ,0 );
2098 v_creation_date := pr_new.Creation_Date;
2099 v_created_by := pr_new.Created_By;
2100 v_last_update_date := pr_new.Last_Update_Date;
2101 v_last_updated_by := pr_new.Last_Updated_By;
2102 v_last_update_login := pr_new.Last_Update_Login;
2103 v_org_id := 0;
2104 v_operating_id :=pr_new.ORG_ID;
2105 v_deliver_to_loc_id := pr_new.DELIVER_TO_LOCATION_ID;--Added by Xiao Lv for bug 10043656
2106
2107
2108 --if
2109 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_REQN_TAX_UPDATE_TRG',
2110 -- p_org_id => pr_new.org_id)
2111
2112 -- =
2113 -- FALSE
2114 --then
2115 /* India Localization funtionality is not required */
2116 -- return;
2117 --end if;
2118
2119 --pramasub FP start
2120 OPEN bind_cur;
2121 FETCH Bind_cur INTO v_seg_id, v_type_lookup_code , lv_apps_source_code;
2122 CLOSE bind_cur;
2123 --pramasub FP end
2124
2125 --jai_po_cmn_pkg.query_locator_for_line( v_header_id, 'JAINREQN', found );
2126 v_hook_value := 'TRUE'; /* rallamse bug#4479131 PADDR Elimination */
2127 /* If v_hook_value is TRUE, then it means taxes should be defaulted. IF FALSE then return */
2128 v_hook_value := jai_cmn_hook_pkg.Po_Requisition_Lines_All
2129 (
2130 pr_new.REQUISITION_LINE_ID,
2131 pr_new.REQUISITION_HEADER_ID,
2132 pr_new.LINE_NUM,
2133 pr_new.LINE_TYPE_ID,
2134 pr_new.CATEGORY_ID,
2135 pr_new.ITEM_DESCRIPTION,
2136 pr_new.UNIT_MEAS_LOOKUP_CODE,
2137 pr_new.UNIT_PRICE,
2138 pr_new.QUANTITY,
2139 pr_new.DELIVER_TO_LOCATION_ID,
2140 pr_new.TO_PERSON_ID,
2141 pr_new.LAST_UPDATE_DATE,
2142 pr_new.LAST_UPDATED_BY,
2143 pr_new.SOURCE_TYPE_CODE,
2144 pr_new.LAST_UPDATE_LOGIN,
2145 pr_new.CREATION_DATE,
2146 pr_new.CREATED_BY,
2147 pr_new.ITEM_ID,
2148 pr_new.ITEM_REVISION,
2149 pr_new.QUANTITY_DELIVERED,
2150 pr_new.SUGGESTED_BUYER_ID,
2151 pr_new.ENCUMBERED_FLAG,
2152 pr_new.RFQ_REQUIRED_FLAG,
2153 pr_new.NEED_BY_DATE,
2154 pr_new.LINE_LOCATION_ID,
2155 pr_new.MODIFIED_BY_AGENT_FLAG,
2156 pr_new.PARENT_REQ_LINE_ID,
2157 pr_new.JUSTIFICATION,
2158 pr_new.NOTE_TO_AGENT,
2159 pr_new.NOTE_TO_RECEIVER,
2160 pr_new.PURCHASING_AGENT_ID,
2161 pr_new.DOCUMENT_TYPE_CODE,
2162 pr_new.BLANKET_PO_HEADER_ID,
2163 pr_new.BLANKET_PO_LINE_NUM,
2164 pr_new.CURRENCY_CODE,
2165 pr_new.RATE_TYPE,
2166 pr_new.RATE_DATE,
2167 pr_new.RATE,
2168 pr_new.CURRENCY_UNIT_PRICE,
2169 pr_new.SUGGESTED_VENDOR_NAME,
2170 pr_new.SUGGESTED_VENDOR_LOCATION,
2171 pr_new.SUGGESTED_VENDOR_CONTACT,
2172 pr_new.SUGGESTED_VENDOR_PHONE,
2173 pr_new.SUGGESTED_VENDOR_PRODUCT_CODE,
2174 pr_new.UN_NUMBER_ID,
2175 pr_new.HAZARD_CLASS_ID,
2176 pr_new.MUST_USE_SUGG_VENDOR_FLAG,
2177 pr_new.REFERENCE_NUM,
2178 pr_new.ON_RFQ_FLAG,
2179 pr_new.URGENT_FLAG,
2180 pr_new.CANCEL_FLAG,
2181 pr_new.SOURCE_ORGANIZATION_ID,
2182 pr_new.SOURCE_SUBINVENTORY,
2183 pr_new.DESTINATION_TYPE_CODE,
2184 pr_new.DESTINATION_ORGANIZATION_ID,
2185 pr_new.DESTINATION_SUBINVENTORY,
2186 pr_new.QUANTITY_CANCELLED,
2187 pr_new.CANCEL_DATE,
2188 pr_new.CANCEL_REASON,
2189 pr_new.CLOSED_CODE,
2190 pr_new.AGENT_RETURN_NOTE,
2191 pr_new.CHANGED_AFTER_RESEARCH_FLAG,
2192 pr_new.VENDOR_ID,
2193 pr_new.VENDOR_SITE_ID,
2194 pr_new.VENDOR_CONTACT_ID,
2195 pr_new.RESEARCH_AGENT_ID,
2196 pr_new.ON_LINE_FLAG,
2197 pr_new.WIP_ENTITY_ID,
2198 pr_new.WIP_LINE_ID,
2199 pr_new.WIP_REPETITIVE_SCHEDULE_ID,
2200 pr_new.WIP_OPERATION_SEQ_NUM,
2201 pr_new.WIP_RESOURCE_SEQ_NUM,
2202 pr_new.ATTRIBUTE_CATEGORY,
2203 pr_new.DESTINATION_CONTEXT,
2204 pr_new.INVENTORY_SOURCE_CONTEXT,
2205 pr_new.VENDOR_SOURCE_CONTEXT,
2206 pr_new.ATTRIBUTE1,
2207 pr_new.ATTRIBUTE2,
2208 pr_new.ATTRIBUTE3,
2209 pr_new.ATTRIBUTE4,
2210 pr_new.ATTRIBUTE5,
2211 pr_new.ATTRIBUTE6,
2212 pr_new.ATTRIBUTE7,
2213 pr_new.ATTRIBUTE8,
2214 pr_new.ATTRIBUTE9,
2215 pr_new.ATTRIBUTE10,
2216 pr_new.ATTRIBUTE11,
2217 pr_new.ATTRIBUTE12,
2218 pr_new.ATTRIBUTE13,
2219 pr_new.ATTRIBUTE14,
2220 pr_new.ATTRIBUTE15,
2221 pr_new.BOM_RESOURCE_ID,
2222 pr_new.CLOSED_REASON,
2223 pr_new.CLOSED_DATE,
2224 pr_new.TRANSACTION_REASON_CODE,
2225 pr_new.QUANTITY_RECEIVED,
2226 pr_new.SOURCE_REQ_LINE_ID,
2227 pr_new.ORG_ID,
2228 pr_new.KANBAN_CARD_ID,
2229 pr_new.CATALOG_TYPE,
2230 pr_new.CATALOG_SOURCE,
2231 pr_new.MANUFACTURER_ID,
2232 pr_new.MANUFACTURER_NAME,
2233 pr_new.MANUFACTURER_PART_NUMBER,
2234 pr_new.REQUESTER_EMAIL,
2235 pr_new.REQUESTER_FAX,
2236 pr_new.REQUESTER_PHONE,
2237 pr_new.UNSPSC_CODE,
2238 pr_new.OTHER_CATEGORY_CODE,
2239 pr_new.SUPPLIER_DUNS,
2240 pr_new.TAX_STATUS_INDICATOR,
2241 pr_new.PCARD_FLAG,
2242 pr_new.NEW_SUPPLIER_FLAG,
2243 pr_new.AUTO_RECEIVE_FLAG,
2244 pr_new.TAX_USER_OVERRIDE_FLAG,
2245 pr_new.TAX_CODE_ID,
2246 pr_new.NOTE_TO_VENDOR,
2247 pr_new.OKE_CONTRACT_VERSION_ID,
2248 pr_new.OKE_CONTRACT_HEADER_ID,
2249 pr_new.ITEM_SOURCE_ID,
2250 pr_new.SUPPLIER_REF_NUMBER,
2251 pr_new.SECONDARY_UNIT_OF_MEASURE,
2252 pr_new.SECONDARY_QUANTITY,
2253 pr_new.PREFERRED_GRADE,
2254 pr_new.SECONDARY_QUANTITY_RECEIVED,
2255 pr_new.SECONDARY_QUANTITY_CANCELLED,
2256 pr_new.VMI_FLAG,
2257 pr_new.AUCTION_HEADER_ID,
2258 pr_new.AUCTION_DISPLAY_NUMBER,
2259 pr_new.AUCTION_LINE_NUMBER,
2260 pr_new.REQS_IN_POOL_FLAG,
2261 pr_new.BID_NUMBER,
2262 pr_new.BID_LINE_NUMBER,
2263 pr_new.NONCAT_TEMPLATE_ID,
2264 pr_new.SUGGESTED_VENDOR_CONTACT_FAX,
2265 pr_new.SUGGESTED_VENDOR_CONTACT_EMAIL,
2266 pr_new.AMOUNT,
2267 pr_new.CURRENCY_AMOUNT,
2268 pr_new.LABOR_REQ_LINE_ID,
2269 pr_new.JOB_ID,
2270 pr_new.JOB_LONG_DESCRIPTION,
2271 pr_new.CONTRACTOR_STATUS,
2272 pr_new.CONTACT_INFORMATION,
2273 pr_new.SUGGESTED_SUPPLIER_FLAG,
2274 pr_new.CANDIDATE_SCREENING_REQD_FLAG,
2275 pr_new.CANDIDATE_FIRST_NAME,
2276 pr_new.CANDIDATE_LAST_NAME,
2277 pr_new.ASSIGNMENT_END_DATE,
2278 pr_new.OVERTIME_ALLOWED_FLAG,
2279 pr_new.CONTRACTOR_REQUISITION_FLAG,
2280 pr_new.DROP_SHIP_FLAG,
2281 pr_new.ASSIGNMENT_START_DATE,
2282 pr_new.ORDER_TYPE_LOOKUP_CODE,
2283 pr_new.PURCHASE_BASIS,
2284 pr_new.MATCHING_BASIS,
2285 pr_new.NEGOTIATED_BY_PREPARER_FLAG,
2286 pr_new.SHIP_METHOD,
2287 pr_new.ESTIMATED_PICKUP_DATE,
2288 pr_new.SUPPLIER_NOTIFIED_FOR_CANCEL,
2289 pr_new.BASE_UNIT_PRICE,
2290 pr_new.AT_SOURCING_FLAG,
2291 /*
2292 || Commented the columns for P1 bug# 4535701.
2293 || These will be uncommented once PO teams po_requisition_lines_all table has these columns
2294 */
2295 /* Bug4540709. Added by Lakshmig Gopalsami
2296 * Reverting the fix for bug 4535701 */
2297 /* pr_new.EVENT_ID, /*following 2 cols commented by ssumaith - bug# 4616729 and added the two null towards the end /*
2298 pr_new.LINE_NUMBER*/
2299 NULL,
2300 NULL
2301 ) ;
2302 IF lv_apps_source_code <> 'POR' THEN --pramasub FP
2303 --IF NOT found THEN
2304 IF v_hook_value = 'FALSE' THEN
2305 RETURN;
2306 END IF;
2307 END IF; --pramasub FP
2308 /*5852041 start*/ --pramasub FP start
2309
2310 v_blanket_hdr := pr_new.BLANKET_PO_HEADER_ID;
2311 v_blanket_line := pr_new.BLANKET_PO_LINE_NUM;
2312 v_unit_price := pr_new.unit_price;
2313
2314
2315 --IF DELETING THEN
2316 IF pv_action = jai_constants.deleting THEN
2317 DELETE from JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
2318 WHERE requisition_line_id = pr_old.requisition_line_id ;
2319
2320 DELETE from JAI_PO_REQ_LINES --ja_in_reqn_lines
2321 WHERE requisition_line_id = pr_old.requisition_line_id;
2322
2323 RETURN;
2324
2325 --ELSIF UPDATING AND NVL(pr_new.cancel_flag,'$') = 'Y' AND NVL(pr_old.cancel_flag,'#') <> 'Y' THEN
2326 ELSIF pv_action = jai_constants.updating
2327 AND NVL(pr_new.cancel_flag,'$') = 'Y' AND NVL(pr_old.cancel_flag,'#') <> 'Y' THEN
2328 DELETE from JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
2329 WHERE requisition_line_id = pr_old.requisition_line_id ;
2330
2331 DELETE from JAI_PO_REQ_LINES --ja_in_reqn_lines
2332 WHERE requisition_line_id = pr_old.requisition_line_id;
2333
2334 RETURN;
2335
2336 END IF;
2337
2338 /*5852041 end*/ --pramasub FP end
2339 /*OPEN bind_cur;
2340 FETCH Bind_cur INTO v_seg_id, v_type_lookup_code;
2341 CLOSE bind_cur;*/ --pramasub FP
2342
2343 OPEN Fetch_Org_Id_Cur;
2344 FETCH Fetch_Org_Id_Cur INTO v_org_id;
2345 CLOSE Fetch_Org_Id_Cur;
2346
2347 OPEN Fetch_Mod_Flag_Cur;
2348 FETCH Fetch_Mod_Flag_Cur INTO v_tax_flag;
2349 CLOSE Fetch_Mod_Flag_Cur;
2350
2351 OPEN Fetch_Hdr_Curr_Cur;
2352 FETCH Fetch_Hdr_Curr_Cur INTO v_hdr_curr;
2353 CLOSE Fetch_Hdr_Curr_Cur;
2354
2355 OPEN vend_cur(v_sugg_vendor_name);
2356 FETCH Vend_Cur INTO v_vendor_id;
2357 CLOSE vend_cur;
2358
2359 OPEN site_cur(v_sugg_vendor_loc);
2360 FETCH Site_Cur INTO v_site_id;
2361 CLOSE site_cur;
2362
2363 /* Bug 5243532. Added by Lakshmi Gopalsami
2364 * Removed the cursor Fetch_Book_Id_Cur
2365 * and implemented using caching logic.
2366 */
2367
2368 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2369 (p_org_id => v_dest_org_id );
2370 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
2371
2372
2373 OPEN Fetch_Uom_Code_Cur;
2374 FETCH Fetch_Uom_Code_Cur INTO v_uom_code;
2375 CLOSE Fetch_Uom_Code_Cur;
2376
2377 v_currency := NVL( v_currency, v_hdr_curr );
2378
2379 /*
2380 || The NVL conditions added on both sides of the Equal to sign by ssumaith
2381 || during dev of iprocurement.
2382 */
2383 IF NVL(v_currency,'$') = NVL(v_hdr_curr,'$') THEN
2384 --IF v_currency = v_hdr_curr THEN commented by pramasub FP
2385 conv_rate := 1;
2386 ELSE
2387 IF v_rate_type = 'User' THEN
2388 conv_rate := 1/v_rate;
2389 ELSE
2390 conv_rate := 1/jai_cmn_utils_pkg.currency_conversion( v_gl_set_of_bks_id, v_currency, v_rate_date, v_rate_type, v_rate );
2391 END IF;
2392 END IF;
2393
2394 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value( v_vendor_id, v_site_id,
2395 v_inventory_item_id, v_uom_code );
2396
2397
2398 v_line_amount := v_line_amount * conv_rate ;
2399
2400 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value
2401 ( p_party_id => v_vendor_id,
2402 p_party_site_id => v_site_id,
2403 p_inventory_item_id => v_inventory_item_id,
2404 p_uom_code => v_uom_code,
2405 p_default_price => 0,
2406 p_ass_value_date => trunc(SYSDATE),
2407 p_party_type => 'V'
2408 ) ; -- Ravi for VAT
2409
2410 IF NVL( v_assessable_value, 0 ) <= 0 THEN
2411 v_assessable_value := v_line_amount;
2412 ELSE
2413 v_assessable_value := v_assessable_value * v_quantity * conv_rate ;
2414 END IF;
2415
2416 IF ln_vat_assess_value = 0 THEN -- Ravi for VAT
2417
2418 ln_vat_assess_value := v_line_amount;
2419
2420 ELSE
2421
2422 ln_vat_assess_value := ln_vat_assess_value * v_quantity * conv_rate ;
2423
2424 END IF;
2425
2426 -- Added by Jia for GST Bug#10091373 on 2010/09/10, Begin
2427 ----------------------------------------------------------------------------------
2428 /*
2429 ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value
2430 ( p_party_id => v_vendor_id,
2431 p_party_site_id => v_site_id,
2432 p_inventory_item_id => v_inventory_item_id,
2433 p_uom_code => v_uom_code,
2434 p_default_price => 0,
2435 p_ass_value_date => trunc(SYSDATE),
2436 p_party_type => 'V'
2437 ) ;
2438
2439 IF ln_gst_assessable_value = 0
2440 THEN
2441 ln_gst_assessable_value := v_line_amount;
2442 ELSE
2443 ln_gst_assessable_value := ln_gst_assessable_value * v_quantity * conv_rate ;
2444 END IF;
2445 ----------------------------------------------------------------------------------
2446 -- Added by Jia for GST Bug#10091373 on 2010/09/10, End
2447
2448
2449 lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;--Added by Xiao Lv for bug 10043656
2450 */
2451 --If v_tax_flag = 'N' Then commented by pramasub start FP
2452 /*Entire OR condition in the if clause is added by rchandan for bug#5852041*/
2453 --If v_tax_flag = 'N' OR
2454 -- Following IF condition added by skjayaba for internal QA issue. Tax currency coming as NULL for internal Requisitions. pramasub FP for 115.10
2455 IF (lv_apps_source_code = 'POR' AND v_currency is NULL) THEN
2456 v_currency := 'INR';
2457 END IF;
2458
2459 IF ( (
2460 NVL( pr_old.suggested_vendor_name, 'X' ) <> NVL( pr_new.suggested_vendor_name, 'X' )
2461 )OR
2462 (
2463 NVL( pr_old.suggested_vendor_location, 'X' ) <> NVL( pr_new.suggested_vendor_location, 'X' )
2464 )
2465 ) Then --pramasub end FP
2466
2467 Delete From JAI_PO_REQ_LINE_TAXES
2468 Where Requisition_Line_Id = v_line_id;
2469 -- IF nvl(lv_enable_gst_flag,'N') = 'N' THEN--Added by Xiao Lv for bug 10043656
2470 jai_cmn_tax_defaultation_pkg.JA_IN_VENDOR_DEFAULT_TAXES( v_dest_org_id, v_vendor_id, /*rchandan for bug# replaced NVL(v_org_id,0) with v_dest_org_id*/ --pramasub FP
2471 v_site_id, v_inventory_item_id, v_header_id,
2472 v_line_id, v_tax_category_id );
2473 /*
2474 ELSE
2475 --Added by Xiao Lv for bug 10043656, begin
2476 ----------------------------------------------------------------------------------
2477 jai_gst_tax_defaultation_pkg.jai_gst_vendor_default_taxes(
2478 pn_organization_id =>v_dest_org_id,
2479 pn_location_id =>v_deliver_to_loc_id,
2480 pn_vendor_id =>v_vendor_id,
2481 pn_vendor_site_id =>v_site_id,
2482 pn_inventory_item_id =>v_inventory_item_id,
2483 pd_transaction_date =>v_creation_date,
2484 pn_tax_category_id =>v_tax_category_id );
2485 END IF;
2486 */
2487
2488 ----------------------------------------------------------------------------------
2489 --Added by Xiao Lv for bug 10043656, end
2490
2491 IF v_type_lookup_code = 'INTERNAL' THEN
2492 v_src_org_id := -1*v_src_org_id;
2493 END IF;
2494
2495 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES( 'PO_REQN', v_tax_category_id, v_header_id,
2496 v_line_id, v_assessable_value, v_line_amount, v_inventory_item_id,
2497 v_quantity , v_uom_code, v_vendor_id, NVL( v_currency, v_hdr_curr ),
2498 conv_rate, v_creation_date, v_created_by, v_last_update_date,
2499 v_last_updated_by, v_last_update_login, v_src_org_id,p_vat_assessable_value => ln_vat_assess_value -- Ravi for VAT
2500 --, pn_gst_assessable_value => ln_gst_assessable_value
2501 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
2502
2503 /*UPDATE JAI_PO_REQ_LINES
2504 SET Last_Update_Date = pr_new.last_update_date,
2505 Last_Updated_By = pr_new.last_updated_by,
2506 Last_Update_Login = pr_new.last_update_login
2507 WHERE Requisition_Line_Id = v_line_id
2508 AND Requisition_Header_Id = v_header_id;*/ --pramasub commented for FP
2509 --pramasub FP start 115.10
2510 ELSIF v_tax_flag = 'N' THEN /*5852041*/
2511 Delete From JAI_PO_REQ_LINE_TAXES --Ja_In_Reqn_Tax_Lines pramasub FP
2512 Where Requisition_Line_Id = v_line_id;
2513 IF v_blanket_hdr IS NOT NULL AND v_blanket_line IS NOT NULL THEN
2514 --Ja_In_Locate_Line( v_blanket_hdr, v_blanket_line, v_quantity, v_po_line_id, v_line_location_id );
2515 jai_po_cmn_pkg.locate_source_line( v_blanket_hdr, v_blanket_line, v_quantity, v_po_line_id,
2516 v_line_location_id );
2517 open c_reqn_line_id(v_line_id);
2518 fetch c_reqn_line_id into v_reqn_ctr;
2519 close c_reqn_line_id;
2520
2521 if nvl(v_reqn_ctr,0) = 0 then
2522 FOR rec IN cur_bpa_tax_lines(v_po_line_id, v_line_location_id) LOOP
2523 INSERT INTO JAI_PO_REQ_LINE_TAXES(requisition_line_id, requisition_header_id, tax_line_no,
2524 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
2525 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
2526 tax_id, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
2527 tax_type, modvat_flag, vendor_id, currency,
2528 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
2529 tax_category_id)
2530 VALUES (
2531 v_line_id, v_header_id, rec.lno,rec.p_1, rec.p_2, rec.p_3, rec.p_4,
2532 rec.p_5,rec.p_6 , rec.p_7, rec.p_8 , rec.p_9 , rec.p_10 ,
2533 rec.tax_id, rec.tax_rate, rec.qty_rate, rec.uom, rec.tax_amount,
2534 rec.tax_amount + v_line_amount,rec.tax_type, rec.modvat_flag,
2535 rec.vendor_id, rec.currency,v_creation_date, v_created_by, v_last_update_date,
2536 v_last_updated_by, v_last_update_login,rec.tax_category_id);
2537
2538 --v_uom_code := rec.uom; /*commented out by srjayara for bug 6023447 */ pramasub FP 115.13
2539 END LOOP;
2540 end if;
2541 /*JA_IN_CAL_TAX( 'REQUISITION_BLANKET', v_line_id , v_po_line_id, v_line_location_id,
2542 v_quantity, v_line_amount , NVL( v_currency, v_hdr_curr ) ,
2543 v_assessable_value, v_assessable_value,ln_vat_assess_value, NULL, conv_rate ); -- Ravi for VAT*/
2544
2545 /*5852041 ..commented the above and made call to ja_in_po_calc_tax*/
2546 jai_po_tax_pkg.calc_tax(p_type => 'REQUISITION_BLANKET',
2547 p_header_id => v_blanket_hdr,
2548 p_requisition_line_id => v_line_id ,
2549 P_line_id => v_po_line_id,
2550 p_line_location_id => v_line_location_id,
2551 p_line_focus_id => NULL,
2552 p_line_quantity => v_quantity,
2553 p_base_value => v_line_amount,
2554 p_line_uom_code => v_uom_code,
2555 p_tax_amount => ln_tax_amount,
2556 p_assessable_value => v_assessable_value,
2557 p_vat_assess_value => ln_vat_assess_value,
2558 p_item_id => v_inventory_item_id,
2559 p_conv_rate => 1/conv_rate,
2560 p_po_curr => v_currency
2561 -- , pn_gst_assessable_value => ln_gst_assessable_value
2562 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
2563 ELSIF v_blanket_hdr IS NULL AND v_blanket_line IS NULL THEN
2564 IF v_type_lookup_code = 'PURCHASE' THEN
2565 -- IF nvl(lv_enable_gst_flag,'N') = 'N' THEN--Added by Xiao Lv for bug 10043656
2566 jai_cmn_tax_defaultation_pkg.JA_IN_VENDOR_DEFAULT_TAXES(v_dest_org_id, v_vendor_id,
2567 v_site_id, v_inventory_item_id,
2568 v_header_id,v_line_id, v_tax_category_id);
2569 /*
2570 ELSE
2571 --Added by Xiao Lv for bug 10043656, begin
2572 -----------------------------------------------------------------------------
2573 jai_gst_tax_defaultation_pkg.jai_gst_vendor_default_taxes(
2574 pn_organization_id =>v_dest_org_id,
2575 pn_location_id =>v_deliver_to_loc_id,
2576 pn_vendor_id =>v_vendor_id,
2577 pn_vendor_site_id =>v_site_id,
2578 pn_inventory_item_id =>v_inventory_item_id,
2579 pd_transaction_date =>v_creation_date,
2580 pn_tax_category_id =>v_tax_category_id );
2581 END IF;
2582 */
2583 -----------------------------------------------------------------------------
2584 --Added by Xiao Lv for bug 10043656, end
2585 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES('PO_REQN', v_tax_category_id, v_header_id,
2586 v_line_id, v_assessable_value, v_line_amount, v_inventory_item_id, v_quantity ,
2587 v_uom_code, v_vendor_id, NVL( v_currency, v_hdr_curr ), conv_rate,
2588 v_creation_date, v_created_by, v_last_update_date,
2589 v_last_updated_by, v_last_update_login,p_vat_assessable_value => ln_vat_assess_value
2590 -- , pn_gst_assessable_value => ln_gst_assessable_value
2591 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
2592
2593 ELSIF v_type_lookup_code = 'INTERNAL' THEN
2594 -- IF nvl(lv_enable_gst_flag,'N') = 'N' THEN--Added by Xiao Lv for bug 10043656
2595 jai_cmn_tax_defaultation_pkg.Ja_In_Org_Default_Taxes( v_src_org_id, v_inventory_item_id,
2596 v_tax_category_id );
2597 /*
2598 ELSE
2599 --Added by Xiao Lv for bug 10043656, begin
2600 -----------------------------------------------------------------------------
2601 jai_gst_tax_defaultation_pkg.jai_gst_org_default_taxes(
2602 pn_organization_id => v_src_org_id,
2603 pn_location_id => v_deliver_to_loc_id,
2604 pn_party_id => v_vendor_id,
2605 pn_party_site_id => v_site_id,
2606 pv_party_type => 'V',
2607 pn_inventory_item_id => v_inventory_item_id,
2608 pd_transaction_date => v_creation_date,
2609 pn_tax_category_id => v_tax_category_id );
2610 END IF;*/
2611 -----------------------------------------------------------------------------
2612 --Added by Xiao Lv for bug 10043656, end
2613
2614 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES('PO_REQN', v_tax_category_id, v_header_id,
2615 v_line_id, v_assessable_value, v_line_amount, v_inventory_item_id, v_quantity ,
2616 v_uom_code, NULL, NVL( v_currency, v_hdr_curr ), conv_rate,
2617 v_creation_date, v_created_by, v_last_update_date,
2618 v_last_updated_by, v_last_update_login,
2619 -1*v_src_org_id,p_vat_assessable_value => ln_vat_assess_value
2620 -- , pn_gst_assessable_value => ln_gst_assessable_value
2621 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
2622 END IF;
2623 END IF;
2624 --pramasub FP end
2625 ELSIF v_tax_flag = 'Y' Then
2626 Update JAI_PO_REQ_LINE_TAXES
2627 Set Tax_Amount = 0
2628 WHERE Requisition_Header_Id = v_header_id
2629 AND Requisition_Line_Id = v_line_id
2630 --AND Tax_Rate is Not Null;
2631 AND nvl(Tax_Rate,0) <> 0 and nvl(qty_rate,0) <> 0; /*5852041*/ --pramasub FP 115.10
2632 /*5852041*/ --pramasub FP 115.12
2633 IF ( NVL( pr_old.CURRENCY_CODE, 'INR' ) <> NVL( pr_new.CURRENCY_CODE,'INR' ) ) THEN
2634
2635 UPDATE JAI_PO_REQ_LINE_TAXES --ja_in_reqn_tax_lines
2636 SET currency = nvl(pr_new.CURRENCY_CODE,'INR')
2637 WHERE requisition_header_id = v_header_id
2638 AND requisition_line_id = v_line_id;
2639
2640 UPDATE JAI_PO_REQ_LINE_TAXES jrtl --ja_in_reqn_tax_lines jrtl
2641 SET tax_amount = tax_amount * DECODE(nvl(pr_new.CURRENCY_CODE,'INR'),
2642 'INR',jai_cmn_utils_pkg.currency_conversion( v_gl_set_of_bks_id, pr_old.CURRENCY_CODE,
2643 pr_old.rate_date, pr_old.rate_type, pr_old.rate ),conv_rate)
2644 WHERE requisition_header_id = v_header_id
2645 AND requisition_line_id = v_line_id
2646 AND exists ( SELECT 1
2647 FROM JAI_CMN_TAXES_ALL --ja_in_tax_codes
2648 WHERE tax_id = jrtl.tax_id
2649 AND adhoc_flag = 'Y'
2650 );
2651
2652 END IF; --pramasub FP 115.12
2653
2654 -- following procedure call commented and modified in the next line by Vijay Shankar for Bug# 3190872
2655 -- jai_po_tax_pkg.calculate_tax( 'REQUISITION', v_header_id, v_line_id, -999, -999, -999, ' ', v_line_amount, v_assessable_value, NULL, conv_rate );
2656 /*jai_po_tax_pkg.calculate_tax( 'REQUISITION', v_header_id, v_line_id,
2657 -999, -999, v_line_amount, v_uom_code, v_line_amount,
2658 v_assessable_value,ln_vat_assess_value, v_inventory_item_id, conv_rate ); */-- Ravi for VAT | commented by pramasub FP
2659
2660 /*rchandan for bug#5852041. Commented the above and added the call to ja_in_po_calc_tax*/ --pramasub FP
2661 /*jai_po_tax_pkg.calculate_tax( 'REQUISITION', v_header_id, v_line_id,
2662 -999, v_quantity, v_line_amount, v_uom_code, v_line_amount,
2663 v_assessable_value,ln_vat_assess_value, v_inventory_item_id, conv_rate ); -- Ravi for VAT*/
2664 --ja_in_po_calc_tax is FPed to jai_po_tax_pkg.calc_tax pramasub FP
2665 jai_po_tax_pkg.calc_tax(p_type => 'REQUISITION',
2666 p_header_id => v_header_id,
2667 P_line_id => v_line_id,
2668 p_line_location_id => NULL,
2669 p_line_focus_id => NULL,
2670 p_line_quantity => v_quantity,
2671 p_base_value => v_line_amount,
2672 p_line_uom_code => v_uom_code,
2673 p_tax_amount => ln_tax_amount,
2674 p_assessable_value => v_assessable_value,
2675 p_vat_assess_value => ln_vat_assess_value,
2676 p_item_id => v_inventory_item_id,
2677 p_conv_rate => 1/conv_rate,
2678 p_po_curr => v_currency,
2679 p_func_curr => v_hdr_curr
2680 -- , pn_gst_assessable_value => ln_gst_assessable_value
2681 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
2682
2683 /*UPDATE JAI_PO_REQ_LINES --pramasub FP commented out 115.10
2684 SET Last_Update_Date = pr_new.last_update_date,
2685 Last_Updated_By = pr_new.last_updated_by,
2686 Last_Update_Login = pr_new.last_update_login
2687 WHERE Requisition_Line_Id = v_line_id
2688 AND Requisition_Header_Id = v_header_id;*/
2689
2690 END IF;
2691 END ARU_T2 ;
2692
2693 /*
2694 REM +======================================================================+
2695 REM NAME ARU_T3
2696 REM
2697 REM DESCRIPTION Called from trigger JAI_PO_RLA_ARIUD_T1
2698 REM
2699 REM NOTES Refers to old trigger JAI_PO_RLA_ARU_T4
2700 REM
2701 REM +======================================================================+
2702 */
2703 PROCEDURE ARU_T3 ( 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
2704 -- Trigger used for Currency Updation.
2705
2706 found BOOLEAN;
2707 v_type_lookup_code Po_Requisition_Headers_All.Type_Lookup_Code % TYPE;
2708 --v_row_id Rowid; --File.Sql.35 Cbabu := pr_new.ROWID;
2709 v_vendor_id NUMBER;
2710 v_site_id NUMBER;
2711 v_dest_org_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Destination_Organization_Id;
2712 v_org_id NUMBER ; --File.Sql.35 Cbabu := 0;
2713 v_deliver_to_loc_id NUMBER;--Added by Xiao Lv for bug 10043656
2714 --lv_enable_gst_flag VARCHAR2(3);--Added by Xiao Lv for bug 10043656
2715 v_currency VARCHAR2(15) ; --File.Sql.35 Cbabu := pr_new.Currency_Code;
2716 v_rate_type VARCHAR2(30); --File.Sql.35 Cbabu := pr_new.Rate_Type;
2717 v_rate_date DATE ; --File.Sql.35 Cbabu := pr_new.Rate_Date;
2718 v_rate NUMBER ; --File.Sql.35 Cbabu := pr_new.Rate;
2719 v_hdr_curr VARCHAR2(15);
2720 v_header_id NUMBER ; --File.Sql.35 Cbabu := pr_new.requisition_header_id;
2721 v_line_id NUMBER ; --File.Sql.35 Cbabu := pr_new.requisition_line_id;
2722 v_tax_flag VARCHAR2(1);
2723 v_seg_id VARCHAR2(20);
2724 v_sugg_vendor_name VARCHAR2(360); --File.Sql.35 Cbabu := pr_new.suggested_vendor_name; --Increased the length 80 to 360 by JMEENA for bug#5394234
2725 v_sugg_vendor_loc VARCHAR2(360); --File.Sql.35 Cbabu := pr_new.suggested_vendor_location; --Increased the length 80 to 360 by JMEENA for bug#5394234
2726 v_inventory_item_id NUMBER ; --File.Sql.35 Cbabu := pr_new.item_id;
2727 v_uom VARCHAR2(30) ; --File.Sql.35 Cbabu := pr_new.Unit_Meas_Lookup_Code;
2728 v_tax_category_list VARCHAR2(30);
2729 v_tax_category_id NUMBER;
2730 v_item_class VARCHAR2(30);
2731 v_line_no NUMBER;
2732 v_tax_id NUMBER;
2733 v_tax_rate NUMBER;
2734 v_tax_amount NUMBER;
2735 v_quantity NUMBER ; --File.Sql.35 Cbabu := pr_new.Quantity;
2736 v_line_tax_amount NUMBER ; --File.Sql.35 Cbabu := NVL( (pr_new.quantity * pr_new.unit_price) ,0 );
2737 v_uom_code VARCHAR2(3);
2738 v_line_amount NUMBER ; --File.Sql.35 Cbabu := NVL( ( pr_new.quantity * pr_new.unit_price) ,0 );
2739 conv_rate NUMBER;
2740 v_assessable_value NUMBER ;
2741 ln_vat_assess_value NUMBER ;
2742 -- ln_gst_assessable_value NUMBER ; -- Added by Jia for GST Bug#10091373 on 2010/09/10
2743 v_src_org_id NUMBER ; --File.Sql.35 Cbabu := pr_new.Source_Organization_Id;
2744
2745 v_gl_set_of_bks_id NUMBER;
2746
2747 v_creation_date DATE ; --File.Sql.35 Cbabu := pr_new.Creation_Date;
2748 v_created_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Created_By;
2749 v_last_update_date DATE ; --File.Sql.35 Cbabu := pr_new.Last_Update_Date;
2750 v_last_updated_by NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Updated_By;
2751 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := pr_new.Last_Update_Login;
2752 v_hook_value VARCHAR2(10) ; /* rallamse bug#4479131 PADDR Elimination */
2753
2754 /* Bug 5243532. Added by Lakshmi Gopalsami
2755 * Defined variable for implementing caching logic.
2756 */
2757 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2758
2759 ------------------------------------------------------------------------------------------------
2760 CURSOR Fetch_Org_Id_Cur IS SELECT nvl(Operating_Unit,0)
2761 FROM Org_Organization_Definitions
2762 WHERE Organization_Id = v_dest_org_id;
2763
2764 Cursor bind_cur IS SELECT Segment1, Type_Lookup_Code
2765 FROM Po_Requisition_Headers_All
2766 WHERE Requisition_Header_Id = v_header_id;
2767
2768 Cursor Vend_cur(p_sugg_vendor_name IN VARCHAR2) IS SELECT vendor_id
2769 FROM po_vendors
2770 WHERE vendor_name = p_sugg_vendor_name;
2771
2772 Cursor site_cur(p_sugg_vendor_loc IN VARCHAR2) IS SELECT vendor_Site_Id
2773 FROM Po_vendor_Sites_All A
2774 WHERE A.vendor_Site_Code = p_sugg_vendor_loc
2775 AND A.vendor_Id = v_vendor_id
2776 AND nvl(A.Org_Id,0) = nvl(v_org_id,0);
2777
2778 Cursor tax_rate_cur(p_tax_id IN NUMBER) IS
2779 SELECT Tax_Rate, Tax_Amount, Uom_Code, Tax_Type
2780 FROM JAI_CMN_TAXES_ALL
2781 WHERE Tax_Id = p_tax_id;
2782
2783 CURSOR Fetch_Hdr_Curr_Cur IS SELECT NVL( Currency_Code, '$' )
2784 FROM Po_Requisition_Headers_V
2785 WHERE Requisition_Header_Id = v_header_id;
2786
2787 CURSOR Fetch_Uom_Code_Cur IS SELECT Uom_Code
2788 FROM Mtl_Units_Of_Measure
2789 WHERE Unit_Of_Measure = v_uom;
2790
2791 CURSOR Fetch_Mod_Flag_Cur IS SELECT Tax_modIFied_Flag
2792 From JAI_PO_REQ_LINES
2793 Where Requisition_Line_Id = v_line_id;
2794 BEGIN
2795 pv_return_code := jai_constants.successful ;
2796 /*------------------------------------------------------------------------------------------
2797 FILENAME: Ja_In_Reqn_Curr_Upd_Trg .sql
2798
2799 CHANGE HISTORY:
2800 S.No Date Author and Details
2801
2802 1. 30/11/2005 Aparajita for bug#4036241. Version#115.1
2803
2804 Introduced the call to centralized packaged procedure,
2805 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
2806
2807 2. 15/03/2005 Brathod for bug#4250072 , Version#115.2
2808 Modified the trigger for VAT implementation (vat assessable value)
2809
2810 3. 08-Jun-2005 This Object is Modified to refer to New DB Entry names in place of Old
2811 DB as required for CASE COMPLAINCE. Version 116.1
2812
2813 4. 13-Jun-2005 File Version: 116.2
2814 Ramananda for bug#4428980. Removal of SQL LITERALs is done
2815
2816 5 06-Jul-2005 rallamse for bug# PADDR Elimination
2817 1. Replaced call to jai_po_cmn_pkg.query_locator_for_line with
2818 jai_cmn_hook_pkg.Po_Requisition_Lines_All
2819
2820 6 04-Aug-2005 P1 Build Issue bug# 4535701 by Ramananda. File Version 120.2
2821 Commented the columns Event_Id and Line_Number.
2822 These will be uncommented once PO team's po_requisition_lines_all table has these columns
2823 7. 07-Nov-2008 JMEENA for bug#5394234
2824 Increased the length of variables v_sugg_vendor_name and v_sugg_vendor_loc from 80 to 360
2825
2826 Dependency:
2827 ----------
2828
2829 Sl No. Bug Dependent on
2830 Bug/Patch set Details
2831 -------------------------------------------------------------------------------------------------
2832 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
2833 4033992.
2834 ja_in_util_pkg_s.sql 115.0
2835 ja_in_util_pkg_b.sql 115.0
2836
2837 2 4250072 4245089 All objects for VAT Implementaion
2838 --------------------------------------------------------------------------------------------*/
2839
2840 --File.Sql.35 Cbabu
2841 --v_row_id := pr_new.ROWID;
2842 v_dest_org_id := pr_new.Destination_Organization_Id;
2843 v_org_id := 0;
2844 v_deliver_to_loc_id := pr_new.DELIVER_TO_LOCATION_ID;--Added by Xiao Lv for bug 10043656
2845 v_currency := pr_new.Currency_Code;
2846 v_rate_type := pr_new.Rate_Type;
2847 v_rate_date := pr_new.Rate_Date;
2848 v_rate := pr_new.Rate;
2849 v_header_id := pr_new.requisition_header_id;
2850 v_line_id := pr_new.requisition_line_id;
2851 v_sugg_vendor_name := pr_new.suggested_vendor_name;
2852 v_sugg_vendor_loc := pr_new.suggested_vendor_location;
2853 v_inventory_item_id := pr_new.item_id;
2854 v_uom := pr_new.Unit_Meas_Lookup_Code;
2855 v_quantity := pr_new.Quantity;
2856 v_line_tax_amount := NVL( (pr_new.quantity * pr_new.unit_price) ,0 );
2857 v_line_amount := NVL( ( pr_new.quantity * pr_new.unit_price) ,0 );
2858 v_src_org_id := pr_new.Source_Organization_Id;
2859 v_creation_date := pr_new.Creation_Date;
2860 v_created_by := pr_new.Created_By;
2861 v_last_update_date := pr_new.Last_Update_Date;
2862 v_last_updated_by := pr_new.Last_Updated_By;
2863 v_last_update_login := pr_new.Last_Update_Login;
2864
2865
2866 --if
2867 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_REQN_CURR_UPD_TRG',
2868 -- p_org_id => pr_new.org_id)
2869
2870 -- =
2871 -- FALSE
2872 --then
2873 /* India Localization funtionality is not required */
2874 -- return;
2875 --end if;
2876
2877
2878 --jai_po_cmn_pkg.query_locator_for_line( v_header_id, 'JAINREQN', found );
2879 v_hook_value := 'TRUE'; /* rallamse bug#4479131 PADDR Elimination */
2880 /* If v_hook_value is TRUE, then it means taxes should be defaulted. IF FALSE then return */
2881 v_hook_value := jai_cmn_hook_pkg.Po_Requisition_Lines_All
2882 (
2883 pr_new.REQUISITION_LINE_ID,
2884 pr_new.REQUISITION_HEADER_ID,
2885 pr_new.LINE_NUM,
2886 pr_new.LINE_TYPE_ID,
2887 pr_new.CATEGORY_ID,
2888 pr_new.ITEM_DESCRIPTION,
2889 pr_new.UNIT_MEAS_LOOKUP_CODE,
2890 pr_new.UNIT_PRICE,
2891 pr_new.QUANTITY,
2892 pr_new.DELIVER_TO_LOCATION_ID,
2893 pr_new.TO_PERSON_ID,
2894 pr_new.LAST_UPDATE_DATE,
2895 pr_new.LAST_UPDATED_BY,
2896 pr_new.SOURCE_TYPE_CODE,
2897 pr_new.LAST_UPDATE_LOGIN,
2898 pr_new.CREATION_DATE,
2899 pr_new.CREATED_BY,
2900 pr_new.ITEM_ID,
2901 pr_new.ITEM_REVISION,
2902 pr_new.QUANTITY_DELIVERED,
2903 pr_new.SUGGESTED_BUYER_ID,
2904 pr_new.ENCUMBERED_FLAG,
2905 pr_new.RFQ_REQUIRED_FLAG,
2906 pr_new.NEED_BY_DATE,
2907 pr_new.LINE_LOCATION_ID,
2908 pr_new.MODIFIED_BY_AGENT_FLAG,
2909 pr_new.PARENT_REQ_LINE_ID,
2910 pr_new.JUSTIFICATION,
2911 pr_new.NOTE_TO_AGENT,
2912 pr_new.NOTE_TO_RECEIVER,
2913 pr_new.PURCHASING_AGENT_ID,
2914 pr_new.DOCUMENT_TYPE_CODE,
2915 pr_new.BLANKET_PO_HEADER_ID,
2916 pr_new.BLANKET_PO_LINE_NUM,
2917 pr_new.CURRENCY_CODE,
2918 pr_new.RATE_TYPE,
2919 pr_new.RATE_DATE,
2920 pr_new.RATE,
2921 pr_new.CURRENCY_UNIT_PRICE,
2922 pr_new.SUGGESTED_VENDOR_NAME,
2923 pr_new.SUGGESTED_VENDOR_LOCATION,
2924 pr_new.SUGGESTED_VENDOR_CONTACT,
2925 pr_new.SUGGESTED_VENDOR_PHONE,
2926 pr_new.SUGGESTED_VENDOR_PRODUCT_CODE,
2927 pr_new.UN_NUMBER_ID,
2928 pr_new.HAZARD_CLASS_ID,
2929 pr_new.MUST_USE_SUGG_VENDOR_FLAG,
2930 pr_new.REFERENCE_NUM,
2931 pr_new.ON_RFQ_FLAG,
2932 pr_new.URGENT_FLAG,
2933 pr_new.CANCEL_FLAG,
2934 pr_new.SOURCE_ORGANIZATION_ID,
2935 pr_new.SOURCE_SUBINVENTORY,
2936 pr_new.DESTINATION_TYPE_CODE,
2937 pr_new.DESTINATION_ORGANIZATION_ID,
2938 pr_new.DESTINATION_SUBINVENTORY,
2939 pr_new.QUANTITY_CANCELLED,
2940 pr_new.CANCEL_DATE,
2941 pr_new.CANCEL_REASON,
2942 pr_new.CLOSED_CODE,
2943 pr_new.AGENT_RETURN_NOTE,
2944 pr_new.CHANGED_AFTER_RESEARCH_FLAG,
2945 pr_new.VENDOR_ID,
2946 pr_new.VENDOR_SITE_ID,
2947 pr_new.VENDOR_CONTACT_ID,
2948 pr_new.RESEARCH_AGENT_ID,
2949 pr_new.ON_LINE_FLAG,
2950 pr_new.WIP_ENTITY_ID,
2951 pr_new.WIP_LINE_ID,
2952 pr_new.WIP_REPETITIVE_SCHEDULE_ID,
2953 pr_new.WIP_OPERATION_SEQ_NUM,
2954 pr_new.WIP_RESOURCE_SEQ_NUM,
2955 pr_new.ATTRIBUTE_CATEGORY,
2956 pr_new.DESTINATION_CONTEXT,
2957 pr_new.INVENTORY_SOURCE_CONTEXT,
2958 pr_new.VENDOR_SOURCE_CONTEXT,
2959 pr_new.ATTRIBUTE1,
2960 pr_new.ATTRIBUTE2,
2961 pr_new.ATTRIBUTE3,
2962 pr_new.ATTRIBUTE4,
2963 pr_new.ATTRIBUTE5,
2964 pr_new.ATTRIBUTE6,
2965 pr_new.ATTRIBUTE7,
2966 pr_new.ATTRIBUTE8,
2967 pr_new.ATTRIBUTE9,
2968 pr_new.ATTRIBUTE10,
2969 pr_new.ATTRIBUTE11,
2970 pr_new.ATTRIBUTE12,
2971 pr_new.ATTRIBUTE13,
2972 pr_new.ATTRIBUTE14,
2973 pr_new.ATTRIBUTE15,
2974 pr_new.BOM_RESOURCE_ID,
2975 pr_new.CLOSED_REASON,
2976 pr_new.CLOSED_DATE,
2977 pr_new.TRANSACTION_REASON_CODE,
2978 pr_new.QUANTITY_RECEIVED,
2979 pr_new.SOURCE_REQ_LINE_ID,
2980 pr_new.ORG_ID,
2981 pr_new.KANBAN_CARD_ID,
2982 pr_new.CATALOG_TYPE,
2983 pr_new.CATALOG_SOURCE,
2984 pr_new.MANUFACTURER_ID,
2985 pr_new.MANUFACTURER_NAME,
2986 pr_new.MANUFACTURER_PART_NUMBER,
2987 pr_new.REQUESTER_EMAIL,
2988 pr_new.REQUESTER_FAX,
2989 pr_new.REQUESTER_PHONE,
2990 pr_new.UNSPSC_CODE,
2991 pr_new.OTHER_CATEGORY_CODE,
2992 pr_new.SUPPLIER_DUNS,
2993 pr_new.TAX_STATUS_INDICATOR,
2994 pr_new.PCARD_FLAG,
2995 pr_new.NEW_SUPPLIER_FLAG,
2996 pr_new.AUTO_RECEIVE_FLAG,
2997 pr_new.TAX_USER_OVERRIDE_FLAG,
2998 pr_new.TAX_CODE_ID,
2999 pr_new.NOTE_TO_VENDOR,
3000 pr_new.OKE_CONTRACT_VERSION_ID,
3001 pr_new.OKE_CONTRACT_HEADER_ID,
3002 pr_new.ITEM_SOURCE_ID,
3003 pr_new.SUPPLIER_REF_NUMBER,
3004 pr_new.SECONDARY_UNIT_OF_MEASURE,
3005 pr_new.SECONDARY_QUANTITY,
3006 pr_new.PREFERRED_GRADE,
3007 pr_new.SECONDARY_QUANTITY_RECEIVED,
3008 pr_new.SECONDARY_QUANTITY_CANCELLED,
3009 pr_new.VMI_FLAG,
3010 pr_new.AUCTION_HEADER_ID,
3011 pr_new.AUCTION_DISPLAY_NUMBER,
3012 pr_new.AUCTION_LINE_NUMBER,
3013 pr_new.REQS_IN_POOL_FLAG,
3014 pr_new.BID_NUMBER,
3015 pr_new.BID_LINE_NUMBER,
3016 pr_new.NONCAT_TEMPLATE_ID,
3017 pr_new.SUGGESTED_VENDOR_CONTACT_FAX,
3018 pr_new.SUGGESTED_VENDOR_CONTACT_EMAIL,
3019 pr_new.AMOUNT,
3020 pr_new.CURRENCY_AMOUNT,
3021 pr_new.LABOR_REQ_LINE_ID,
3022 pr_new.JOB_ID,
3023 pr_new.JOB_LONG_DESCRIPTION,
3024 pr_new.CONTRACTOR_STATUS,
3025 pr_new.CONTACT_INFORMATION,
3026 pr_new.SUGGESTED_SUPPLIER_FLAG,
3027 pr_new.CANDIDATE_SCREENING_REQD_FLAG,
3028 pr_new.CANDIDATE_FIRST_NAME,
3029 pr_new.CANDIDATE_LAST_NAME,
3030 pr_new.ASSIGNMENT_END_DATE,
3031 pr_new.OVERTIME_ALLOWED_FLAG,
3032 pr_new.CONTRACTOR_REQUISITION_FLAG,
3033 pr_new.DROP_SHIP_FLAG,
3034 pr_new.ASSIGNMENT_START_DATE,
3035 pr_new.ORDER_TYPE_LOOKUP_CODE,
3036 pr_new.PURCHASE_BASIS,
3037 pr_new.MATCHING_BASIS,
3038 pr_new.NEGOTIATED_BY_PREPARER_FLAG,
3039 pr_new.SHIP_METHOD,
3040 pr_new.ESTIMATED_PICKUP_DATE,
3041 pr_new.SUPPLIER_NOTIFIED_FOR_CANCEL,
3042 pr_new.BASE_UNIT_PRICE,
3043 pr_new.AT_SOURCING_FLAG,
3044 /*
3045 || Commented the columns for P1 bug# 4535701.
3046 || These will be uncommented once PO teams po_requisition_lines_all table has these columns
3047 */
3048 /* Bug4540709. Added by Lakshmig Gopalsami
3049 * Reverting the fix for bug 4535701 */
3050 /* pr_new.EVENT_ID,/*following 2 cols commented by ssumaith - bug# 4616729 and added the two null towards the end /*
3051 pr_new.LINE_NUMBER*/
3052 null,
3053 null
3054 ) ;
3055
3056 /*
3057 END Of POC
3058 */
3059
3060 OPEN Fetch_Hdr_Curr_Cur;
3061 FETCH Fetch_Hdr_Curr_Cur INTO v_hdr_curr;
3062 CLOSE Fetch_Hdr_Curr_Cur;
3063
3064 --IF ( NOT FOUND ) OR ( v_hdr_curr = NVL( v_currency, v_hdr_curr ) ) THEN
3065 IF ( v_hook_value = 'FALSE' ) OR ( v_hdr_curr = NVL( v_currency, v_hdr_curr ) ) THEN
3066 RETURN;
3067 END IF;
3068
3069 OPEN bind_cur;
3070 FETCH Bind_cur INTO v_seg_id, v_type_lookup_code;
3071 CLOSE bind_cur;
3072
3073 OPEN Fetch_Org_Id_Cur;
3074 FETCH Fetch_Org_Id_Cur INTO v_org_id;
3075 CLOSE Fetch_Org_Id_Cur;
3076
3077 OPEN Fetch_Mod_Flag_Cur;
3078 FETCH Fetch_Mod_Flag_Cur INTO v_tax_flag;
3079 CLOSE Fetch_Mod_Flag_Cur;
3080
3081 OPEN Vend_cur(v_sugg_vendor_name);
3082 FETCH Vend_Cur INTO v_vendor_id;
3083 CLOSE Vend_cur;
3084
3085 OPEN site_cur(v_sugg_vendor_loc);
3086 FETCH Site_Cur INTO v_site_id;
3087 CLOSE site_cur;
3088
3089 /* Bug 5243532. Added by Lakshmi Gopalsami
3090 * Removed the cursor Fetch_Book_Id_Cur
3091 * and implemented using caching logic.
3092 */
3093 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3094 (p_org_id => v_dest_org_id );
3095 v_gl_set_of_bks_id := l_func_curr_det.ledger_id;
3096
3097
3098 OPEN Fetch_Uom_Code_Cur;
3099 FETCH Fetch_Uom_Code_Cur INTO v_uom_code;
3100 CLOSE Fetch_Uom_Code_Cur;
3101
3102 IF v_currency = v_hdr_curr THEN
3103 conv_rate := 1;
3104 ELSE
3105 IF v_rate_type = 'User' THEN
3106 conv_rate := 1/v_rate;
3107 ELSE
3108 conv_rate := jai_cmn_utils_pkg.currency_conversion( v_gl_set_of_bks_id, v_currency, v_rate_date, v_rate_type, v_rate );
3109 END IF;
3110 END IF;
3111
3112 v_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value( v_vendor_id, v_site_id,
3113 v_inventory_item_id, v_uom_code );
3114
3115 v_line_amount := v_line_amount * conv_rate;
3116
3117 IF NVL( v_assessable_value, 0 ) <= 0 THEN
3118 v_assessable_value := v_line_amount;
3119 ELSE
3120 v_assessable_value := v_assessable_value * v_quantity * conv_rate;
3121 END IF;
3122
3123
3124 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value
3125 ( p_party_id => v_vendor_id,
3126 p_party_site_id => v_site_id,
3127 p_inventory_item_id => v_inventory_item_id,
3128 p_uom_code => v_uom_code,
3129 p_default_price => 0,
3130 p_ass_value_date => SYSDATE,
3131 p_party_type => 'V'
3132 ) ;
3133 IF ln_vat_assess_value = 0 THEN
3134 ln_vat_assess_value := v_line_amount ;
3135 ELSE
3136 ln_vat_assess_value := ln_vat_assess_value * v_quantity * conv_rate;
3137 END IF;
3138
3139 -- Added by Jia for GST Bug#10091373 on 2010/09/10, Begin
3140 ------------------------------------------------------------------------------
3141 /*
3142 ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value
3143 ( p_party_id => v_vendor_id,
3144 p_party_site_id => v_site_id,
3145 p_inventory_item_id => v_inventory_item_id,
3146 p_uom_code => v_uom_code,
3147 p_default_price => 0,
3148 p_ass_value_date => SYSDATE,
3149 p_party_type => 'V'
3150 ) ;
3151 IF ln_gst_assessable_value = 0
3152 THEN
3153 ln_gst_assessable_value := v_line_amount ;
3154 ELSE
3155 ln_gst_assessable_value := ln_gst_assessable_value * v_quantity * conv_rate;
3156 END IF;
3157 */
3158 ------------------------------------------------------------------------------
3159 -- Added by Jia for GST Bug#10091373 on 2010/09/10, End
3160
3161
3162 IF v_tax_flag = 'N' THEN
3163
3164 Delete From JAI_PO_REQ_LINE_TAXES
3165 Where Requisition_Line_Id = v_line_id;
3166
3167 --lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;--Added by Xiao Lv for bug 10043656
3168 --IF nvl(lv_enable_gst_flag,'N') = 'N' THEN
3169 --Added by Xiao Lv for bug 10043656, begin
3170 ----------------------------------------------------------------------------------------
3171 jai_cmn_tax_defaultation_pkg.Ja_In_vendor_Default_Taxes( nvl(v_org_id,0),
3172 v_vendor_id, v_site_id,
3173 v_inventory_item_id, v_header_id, v_line_id,
3174 v_tax_category_id );
3175 /*
3176 ELSE
3177 jai_gst_tax_defaultation_pkg.jai_gst_vendor_default_taxes(
3178 pn_organization_id =>nvl(v_org_id,0),
3179 pn_location_id =>v_deliver_to_loc_id,
3180 pn_vendor_id =>v_vendor_id,
3181 pn_vendor_site_id =>v_site_id,
3182 pn_inventory_item_id =>v_inventory_item_id,
3183 pd_transaction_date =>v_creation_date,
3184 pn_tax_category_id =>v_tax_category_id );
3185 END IF;*/
3186 ----------------------------------------------------------------------------------------
3187 --Added by Xiao Lv for bug 10043656, end
3188 IF v_type_lookup_code <> 'INTERNAL' THEN
3189 v_src_org_id := 0;
3190 ELSE
3191 v_src_org_id := -1 * v_src_org_id;
3192 END IF;
3193
3194 jai_cmn_tax_defaultation_pkg.Ja_In_Calc_Prec_Taxes( 'PO_REQN', v_tax_category_id, v_header_id, v_line_id,
3195 v_assessable_value, v_line_amount, v_inventory_item_id,
3196 v_quantity , v_uom_code, v_vendor_id, NVL( v_currency, v_hdr_curr ),
3197 conv_rate, v_creation_date, v_created_by, v_last_update_date,
3198 v_last_updated_by, v_last_update_login, v_src_org_id, ln_vat_assess_value
3199 -- , pn_gst_assessable_value => ln_gst_assessable_value
3200 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
3201
3202 UPDATE JAI_PO_REQ_LINES
3203 SET Last_Update_Date = pr_new.last_update_date,
3204 Last_Updated_By = pr_new.last_updated_by,
3205 Last_Update_Login = pr_new.last_update_login
3206 WHERE Requisition_Line_Id = v_line_id
3207 AND Requisition_Header_Id = v_header_id;
3208
3209 ELSIF v_tax_flag = 'Y' THEN
3210 Update JAI_PO_REQ_LINE_TAXES
3211 Set Tax_Amount = 0
3212 WHERE Requisition_Header_Id = v_header_id
3213 AND Requisition_Line_Id = v_line_id
3214 AND Currency = NVL( v_currency, v_hdr_curr )
3215 AND Tax_Rate is Not Null;
3216 jai_po_tax_pkg.calculate_tax( 'REQUISITION', v_header_id, v_line_id, -999, -999, -999, ' ', v_line_tax_amount, v_assessable_value,
3217 ln_vat_assess_value, NULL, conv_rate
3218 -- , pn_gst_assessable_value => ln_gst_assessable_value
3219 ); -- Added by Jia for GST Bug#10091373 on 2010/09/10
3220 UPDATE JAI_PO_REQ_LINES
3221 SET Last_Update_Date = pr_new.last_update_date,
3222 Last_Updated_By = pr_new.last_updated_by,
3223 Last_Update_Login = pr_new.last_update_login
3224 WHERE Requisition_Line_Id = v_line_id
3225 AND Requisition_Header_Id = v_header_id;
3226 END IF;
3227 END ARU_T3 ;
3228
3229 END JAI_PO_RLA_TRIGGER_PKG ;