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