4 /*
1 PACKAGE BODY JAI_AP_ILA_TRIGGER_PKG AS
2 /* $Header: jai_ap_ila_t.plb 120.20.12020000.3 2013/01/22 05:55:31 amandali ship $ */
3
5 REM +======================================================================+
6 REM NAME ARU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_AP_ILA_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_AP_ILA_ARI_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARU_T1 ( pr_old t_rec%type ,
15 pr_new t_rec%type ,
16 pv_action varchar2 ,
17 pv_return_code out nocopy varchar2 ,
18 pv_return_message out nocopy varchar2 ) IS
19
20 /*bug 8425058*/
21 lv_source ap_invoices_all.source%TYPE;
22
23 BEGIN
24 -- Bug 6780154. Added by Lakshmi Gopalsami
25 -- Removed the cursor and added op_distribution_id is null
26 update JAI_AP_MATCH_ERS_T
27 set po_distribution_id = pr_new.po_distribution_id
28 where invoice_id = pr_new.invoice_id
29 and invoice_line_number = pr_new.line_number
30 and po_distribution_id is null;
31
32 jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Inside trigger after update');
33 /*bug 8425058
34 *Issue - "To Insert Tax Distributions" concurrent is not firing when invoice is
35 *matched to PO using the "Quick Match" option in the invoice workbench.
36 *Reason - po_distribution_id is null when row is inserted in ap_invoice_lines_all.
37 *So the procedure ARI_T1 exits without firing the program.
38 *Fix - Call the ARI_T1 procedure from ARU_T1 when the invoice source is not ERS.*/
39
40 jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Value of invoice_id'||pr_new.invoice_id);
41 SELECT source INTO lv_source
42 FROM ap_invoices_all
43 WHERE invoice_id = pr_new.invoice_id;
44
45 jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Value of source is'||lv_source);
46
47 --Added this IF condition to process only Discard of ERS invoices for bug # 12832424
48 IF ( Nvl(lv_source,'XX') = 'ERS' and pr_new.discarded_flag ='Y' and pr_new.discarded_flag <> nvl(pr_old.discarded_flag,'N') and pr_new.line_type_lookup_code ='ITEM') then
49 ari_t1 (pr_old, pr_new, pv_action, pv_return_code, pv_return_message);
50 Elsif Nvl(lv_source,'XX') <> 'ERS' THEN
51 jai_cmn_utils_pkg.write_fnd_log_msg('After update trigger','Calling After insert trigger');
52 ari_t1 (pr_old, pr_new, pv_action, pv_return_code, pv_return_message);
53 END IF;
54
55 /*end bug 8425058*/
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 Pv_return_code := jai_constants.unexpected_error;
60 Pv_return_message := 'Encountered an error in JAI_AP_ILA_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
61 END;
62
63
64 /*
65 REM +======================================================================+
66 REM NAME ARI_T1
67 REM
68 REM DESCRIPTION Called from trigger JAI_AP_ILA_ARIUD_T1
69 REM
70 REM NOTES Refers to old trigger JAI_AP_ILA_ARI_T1
71 REM
72 REM +======================================================================+
73 */
74 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
75 CURSOR curr(c_sob NUMBER) IS
76 SELECT currency_code
77 FROM gl_sets_of_books
78 WHERE set_of_books_id = c_sob;
79
80 CURSOR fetch_rcv_record(v_rcv_trans_id NUMBER) IS
81 SELECT po_header_id,po_line_id
82 FROM rcv_transactions
83 WHERE transaction_id = v_rcv_trans_id;
84
85 CURSOR fetch_tax_distribution(v_po_header_id NUMBER, v_po_line_id NUMBER, v_invoice_id NUMBER) IS
86 SELECT COUNT(*)
87 FROM JAI_AP_MATCH_INV_TAXES
88 WHERE po_header_id = v_po_header_id
89 AND po_line_id = v_po_line_id
90 AND invoice_id = v_invoice_id;
91
92 fetch_rcv_record_rec fetch_rcv_record%ROWTYPE;
93 v_count_tax_dist NUMBER;
94
95 result BOOLEAN;
96 req_id NUMBER;
97
98 v_receipt_num RCV_SHIPMENT_HEADERS.RECEIPT_NUM%TYPE;
99 v_inv_org_id NUMBER;
100 v_source VARCHAR2(25);
101 v_shipment_header_id NUMBER;
102 v_shipment_line_id NUMBER;
103 v_count NUMBER := 0;
104 v_vendor_site_id NUMBER;
105
106 /*Bug 5990061 --vkantamn.Substr is added to retrieve only 30 characters from invoice_num*/
107 CURSOR fetch_recpt_num_cur IS
108 SELECT SUBSTR(SUBSTR(invoice_num, INSTR(invoice_num, '-', 1, 1) + 1 ,
109 (INSTR(invoice_num, '-', 1, 2)-1) - INSTR(invoice_num, '-', 1, 1)
110 ),1,30), Source
111 FROM ap_invoices_all
112 WHERE invoice_id = pr_new.Invoice_Id;
113
114 CURSOR fetch_inv_org_id_cur IS
115 SELECT Inventory_Organization_Id
116 FROM Hr_Locations
117 WHERE Location_Id = ( SELECT Ship_To_Location_Id
118 FROM Po_Headers_All
119 WHERE Po_Header_Id = ( SELECT Po_Header_Id
120 FROM Po_Distributions_All
121 WHERE Po_Distribution_Id = pr_new.Po_Distribution_Id
122 )
123 );
124
125 CURSOR Fetch_Shipment_Line_Id_Cur( invorg IN NUMBER, receiptnum IN VARCHAR2 ) IS
126 SELECT Shipment_Line_Id, Shipment_Header_Id
127 FROM Rcv_Shipment_Lines
128 WHERE Shipment_Header_Id IN ( SELECT Shipment_Header_Id
129 FROM Rcv_Shipment_Headers
130 WHERE Receipt_Num = receiptnum )
131 AND Po_Line_location_Id = ( SELECT Line_Location_Id
132 FROM Po_Distributions_All
133 WHERE Po_Distribution_Id = pr_new.Po_Distribution_Id )
134 AND To_Organization_Id = invorg;
135
136 CURSOR Fetch_Shipment_Count_Cur( invorg IN NUMBER ) IS
137 SELECT COUNT( Shipment_Line_Id )
138 FROM Rcv_Shipment_Lines
139 WHERE Shipment_Header_Id = v_shipment_header_id
140 AND To_Organization_Id = invorg;
141
142 ------------Packing Slip---------------------
143 CURSOR vend_info(inv_id NUMBER) IS
144 SELECT vendor_id, vendor_site_id
145 , invoice_type_lookup_code, cancelled_date -- cbabu for Bug# 2560026
146 FROM ap_invoices_all
147 WHERE invoice_id = inv_id;
148
149 CURSOR set_up_info(ven_id NUMBER, ven_site_id NUMBER, v_org_id NUMBER) IS
150 SELECT pay_on_code, pay_on_receipt_summary_code
151 FROM po_vendor_sites_all
152 WHERE vendor_id = ven_id
153 AND vendor_site_id = ven_site_id
154 AND NVL(org_id, 0) = NVL(v_org_id, 0);
155
156 CURSOR count_receipts(p_pck_slip VARCHAR2, ven_id NUMBER, cpv_transaction_type VARCHAR2 ) IS
157 SELECT DISTINCT rsh.receipt_num, rsh.shipment_header_id
158 FROM rcv_shipment_headers rsh, rcv_transactions rt
159 WHERE rsh.shipment_header_id = rt.shipment_header_id
160 AND rsh.packing_slip = p_pck_slip
161 AND rsh.vendor_id = ven_id
162 AND rt.transaction_type = cpv_transaction_type;
163
164 set_up_info_rec set_up_info%ROWTYPE;
165 vend_info_rec vend_info%ROWTYPE;
166 count_receipts_rec count_receipts%ROWTYPE;
167 v_initial_count NUMBER := 0;
168 v_receipt_code VARCHAR2(25) := 'RECEIPT';
169 v_curr curr%ROWTYPE;
170 ------------Packing Slip---------------------
171 v_rematching VARCHAR2(15) := NULL;
172
173
174 v_log_file_name VARCHAR2(50) := 'jai_ap_ida_t3.log';
175 v_utl_location VARCHAR2(512);
176 v_myfilehandle UTL_FILE.FILE_TYPE;
177 v_debug CHAR(1) := 'N';
178
179 lv_rcpt_tax_exists VARCHAR2(1) := NULL; --Added for Bug #12886913
180 lv_po_tax_exists VARCHAR2(1) := NULL; --Added for Bug #12886913
181
182 BEGIN
183 pv_return_code := jai_constants.successful ;
184
185 /*-------------------------------------------------------------------------------------------------------------------------
186 FILENAME: jai_ap_ida_t3.sql
187 CHANGE HISTORY:
188 S.No Date Author AND Details
189 -------------------------------------------------------------------------------------------------------------------------
190 1. 07-Apr-01 Ajay Sharma to take care of computations related
191 to TDS_TAX_ID at site level.
192
193 2. 20-Apr-01 Modified by Ajay Sharma to correctly compute taxes
194 at the time of RCV_MATCHING
195
196 3. 27-Aug-01 Modification done to fire Pay On Receipt code for
197 running pay on receipt for subsequent receipts against a PO
198
199 4. 30-Aug-01 Ajay Sharma, Code modified on 30-Aug-01 to give complete
200 functionality of pay on receipt and receipt matching
201
202 5. 06-Mar-2002 RPK:
203 Parameter nvl(pr_new.Org_id,0) is added to call the procedure distribution_matching.This has
204 been added for the issue of the unique constraint violation error,when an invoice is matched
205 with more than one receipt at a time. BUG#2255404.
206
207 6. 05-may-2002 Aparajita.
208 Commented the call to concurrent for insert tax lines for pay_on_receipt option and populating
209 a temporary table instead, for avoiding a concurrent request for each item line for
210 performance issue.
211
212 7. 15-nov-2002 cbabu for Bug# 2665306, Version# 615.1
213 Following extra condition is added to stop firing 'India - To Insert Tax distributions'
214 concurrent AND (v_source NOT IN ('ERS', 'ASBN') ) As the data gets inserted into
215 JAI_AP_MATCH_ERS_T table that gets processed through the another concurrent for
216 inserting tax distributions during pay on receipt. And debug code is added to debug whenever
217 there are any issues.
218
219 8. 26-NOV-2002 cbabu for Bug# 2560026, Version# 615.2
220 another ELSIF condition (AP_INVOICES_ALL.invoice_type_lookup_code = 'DEBIT') is added to
221 default taxes when debit memo is auto created or matched with a PURCHASE ORDER or RECEIPT.
222 Based on transaction data following two ways are followed.
223
224 If AP_INVOICES_ALL.source = 'RTS' THEN 'ERS' functionality is invoked Otherwise PO, RECEIPT
225 Match functionality is invoked.
226
227 9. 23-DEC-2002 cbabu for Bug# 2717471, Version# 615.3
228 functionality is added to default the taxes for debit memo distribution line, when it is
229 matched with an invoice.
230
231 10. 08-apr-2003 Aparajita for bug#2851123. Version#615.4
232 The request JAINDIST was submitted wrongly. The seventh parameter in the submit request has to be the po distributions id. It was being passed as transactions id in receipt matching cases.
233
234 11. 11-sep-2003 Vijay Shankar for bug#3138227. Version#616.1
235 The utl file related code is failing which is stopping the transaction to continue. If the utl file
236 opening fails, then v_debug has to be made 'N' so that the transaction goes on without any error
237
238 12 29-Nov-2004 Sanjikum for 4035297. Version 115.1
239 Changed the 'INR' check. Added the call to JA_IN_UTIL.CHECK_JAI_EXISTS
240
241 Dependency Due to this Bug:-
242 The current trigger becomes dependent on the function ja_in_util.check_jai_exists version 115.0.
243
244 13 24-mar-2005 Aparajita. Version#115.2. TDS Clean up Bug#4088186.
245 Removed the TDS related functionality from this trigger. It only caters to pulling
246 purchasing taxes to payables.
247
248 14. 17-AUG-2005 Brathod, Bug# 4557312, File Version 120.1
249 Issue:- fnd_request.submit_request call for submitting concurrent JAINDIST was using
250 SQLAP as application which is JA in R12 as reason concurrent was trying to find
251 a procedure registered with SQLAP application which does not exist in
252 R12 code line
253 Solution:- All the call to submit JAINDIST concurrent are modified to use JA
254 as application instead of SQLAP.
255
256 OBJECT RENAMED TO jai_ap_ida_t3.sql
257 -----------------------------------
258
259 14. 22-Jun-2005 Brathod, File Version 116.0
260 For CASE complaince objects are modified to refer to new db entity names
261 in place of old db entity names.
262
263 15 22-Jun-2005 Brathod, File Version 116.1
264 Object Modified For SQL Literal Changes
265
266 16 23-Jun-2005 Brathod, Filer Version 112.0, Bug# 4445989
267 - Trigger on the table ap_invoice_distributions_all was obsoleted and new trigger (jai_ap_ila_ari_t1)
268 on table ap_invoice_lines_all is created by modifying the trigger code to use ap_invoice_lines_all.
269 Trigger is also reponsible to submit a concurrent JAINDIST. Arguments to the concurrent is modified
270 so the call to concurrent is also modified to use invoice_line_number instead
271 of distribution_line_number
272
273 17 07/12/2005 Hjujjuru for the bug 4866533 File version 120.1
274 added the who columns in the insert into table JAI_AP_MATCH_ERS_T.
275 Dependencies Due to this bug:-
276 None
277
278 18 29-SEP-2011 Bug #12886913
279 Issue :- TO INSERT TAX Distributions (India - To Insert Tax Distributions) concurrent
280 is firing even when there are no taxes existing
281 Solution - Taking the count of po/receipt tax lines based on the match type and after checking
282 if the count is greater than zero then only firing the concurrent program
283
284 19. 22-Jan-2012 Bug 16100273 by amandali
285 Issue:TAXES ARE NOT FLOWING TO RECEIPT MATCHED INVOICE IN CASE OF PO HAVING MULTIPLE DISTRIBUTIONS
286 Fix:For receipt matched invoices having PO with multiple distributions, po_distribution_id would be null in ap_invoice_lines_all
287 Hence added po_header_id condition to process the same.
288
289 Future Dependencies For the release Of this Object:-
290 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
291 A datamodel change )
292 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
293 Current Version Current Bug Dependent Files Version Author Date Remarks
294 Of File On Bug/Patchset Dependent On
295
296 jai_ap_ida_t3.sql
297 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
298 115.1 4035297 IN60105D2+4033992 ja_in_util_pkg_s.sql 115.0 Sanjikum 29-Nov-2004 Call to this function.
299 ja_in_util_pkg_s.sql 115.0 Sanjikum
300
301 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
302
303 18 20-Sep-2007 Bug#5990061. Added by vkantamn ,Version 120.8
304 Modified cursor fetch_recpt_num_cur.
305 Added substr when retrieving receipt number from invoice_num
306
307 19 08-Jan-2008 Modifed by Jason Liu for retroactive price
308
309 20 03-APR-2008 Jason Liu for bug#6918386
310 Added PO Matched invoice case to insert tax lines for PPA invoice
311
312 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
313
314 21 23-Dec-2011 Modified by Qinglei for bug#13405876
315 Added condition pr_new.po_distribution_id IS NOT NULL for ITEM_TO_RECEIPT match type.
316 Because for Invoice Quick Match PO, when insert ap_invoice_lines_all, po_distribution_id is null
317 and should not run CP "TO INSERT TAX Distributions (India - To Insert Tax Distributions)".
318 The CP will just run when updateing ap_invoice_lines_all for Invoice Quick Match PO.
319 09-nov-12 vkaranam for bug#14472181
320 issue:Not able to generate the Debit memo matched to PO having multiple distributions
321 Fix:
322
323 added the pr_new.po_header_id is not null condition for PO matched debit memo
324
325 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
326 --IF JAI_CMN_UTILS_PKG.CHECK_JAI_EXISTS( p_calling_object => 'JAIN_TDSTEMP_AFTERINSERT_TRG',
327 -- p_set_of_books_id => pr_new.set_of_books_id) = FALSE
328 --THEN
329 -- RETURN;
330 --END IF;
331
332 IF v_debug = 'Y' THEN
333 DECLARE
334 lv_name VARCHAR2 (10);
335 BEGIN
336 pv_return_code := jai_constants.successful ;
337 -- Modified by Brathod for SQL Literals
338 lv_name := 'utl_file_dir';
339 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
340 Value,SUBSTR (value,1,INSTR(value,',') -1)) INTO v_utl_location
341 FROM v$parameter
342 WHERE name = lv_name;
343
344 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location, v_log_file_name ,'A');
345 UTL_FILE.PUT_LINE(v_myfilehandle, '********* Start TdsTemp_AfterInsert_Trg ('||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') ||') *********');
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 v_debug := 'N';
350 END;
351
352 END IF;
353
354 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside after insert trigger');
355 OPEN fetch_recpt_num_cur;
356 FETCH fetch_recpt_num_cur INTO v_receipt_num, v_source;
357 CLOSE fetch_recpt_num_cur;
358
359 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of receipt_num is '||v_receipt_num);
360 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of source is '||v_source);
361 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of invoice_id is '||pr_new.invoice_id);
362
363 OPEN vend_info(pr_new.invoice_id);
364 FETCH vend_info INTO vend_info_rec;
365 CLOSE vend_info;
366
367 IF v_source IN ( 'ERS','ASBN') THEN
368
369 OPEN set_up_info(vend_info_rec.vendor_id, vend_info_rec.vendor_site_id, pr_new.org_id );
370 FETCH set_up_info INTO set_up_info_rec;
371 CLOSE set_up_info;
372
373
374 IF set_up_info_rec.pay_on_receipt_summary_code = 'RECEIPT' THEN
375
376 OPEN fetch_inv_org_id_cur;
377 FETCH fetch_inv_org_id_cur INTO v_inv_org_id;
378 CLOSE fetch_inv_org_id_cur;
379
380 OPEN Fetch_Shipment_Line_Id_Cur( v_inv_org_id, v_receipt_num );
381 FETCH Fetch_Shipment_Line_Id_Cur INTO v_shipment_line_id, v_shipment_header_id;
382 CLOSE Fetch_Shipment_Line_Id_Cur;
383
384 OPEN Fetch_Shipment_Count_Cur( v_inv_org_id );
385 FETCH Fetch_Shipment_Count_Cur INTO v_count;
386 CLOSE Fetch_Shipment_Count_Cur;
387
388 v_receipt_code := 'RECEIPT';
389
390 ELSIF set_up_info_rec.pay_on_receipt_summary_code = 'PACKING_SLIP' THEN
391
392 v_initial_count := 0;
393 v_count := 0;
394
395 FOR count_receipts_rec IN count_receipts( v_receipt_num, vend_info_rec.vendor_id , 'RECEIVE') LOOP
396
397 OPEN fetch_inv_org_id_cur;
398 FETCH fetch_inv_org_id_cur INTO v_inv_org_id;
399 CLOSE fetch_inv_org_id_cur;
400
401 OPEN Fetch_Shipment_Line_Id_Cur( v_inv_org_id, count_receipts_rec.receipt_num );
402 FETCH Fetch_Shipment_Line_Id_Cur INTO v_shipment_line_id, v_shipment_header_id;
403 CLOSE Fetch_Shipment_Line_Id_Cur;
404
405 OPEN Fetch_Shipment_Count_Cur( v_inv_org_id );
406 FETCH Fetch_Shipment_Count_Cur INTO v_initial_count;
407 CLOSE Fetch_Shipment_Count_Cur;
408
409 v_count := v_count + v_initial_count;
410
411 END LOOP;
412
413 v_receipt_code := 'PACKING_SLIP';
414
415
416 IF v_count = 0 THEN
417 OPEN fetch_inv_org_id_cur;
418 FETCH fetch_inv_org_id_cur INTO v_inv_org_id;
419 CLOSE fetch_inv_org_id_cur;
420
421 OPEN Fetch_Shipment_Line_Id_Cur( v_inv_org_id, v_receipt_num );
422 FETCH Fetch_Shipment_Line_Id_Cur INTO v_shipment_line_id, v_shipment_header_id;
423 CLOSE Fetch_Shipment_Line_Id_Cur;
424
425 OPEN Fetch_Shipment_Count_Cur( v_inv_org_id );
426 FETCH Fetch_Shipment_Count_Cur INTO v_count;
427 CLOSE Fetch_Shipment_Count_Cur;
428
429 v_receipt_code := 'RECEIPT';
430 END IF;
431
432 END IF; -- packing slip
433
434 END IF; -- IF v_source IN ( 'ERS','ASBN')
435
436 /*
437 IF pr_new.attribute11 = 'Y' THEN
438 UPDATE ap_invoices_all
439 SET invoice_amount = invoice_amount + DECODE(pr_new.amount,-1,1,1,-1)
440 WHERE invoice_id = pr_new.invoice_id;
441 END IF;
442 */
443
444 OPEN fetch_rcv_record(pr_new.rcv_transaction_id);
445 FETCH fetch_rcv_record INTO fetch_rcv_record_rec;
446 CLOSE fetch_rcv_record;
447
448 OPEN fetch_tax_distribution(fetch_rcv_record_rec.po_header_id, fetch_rcv_record_rec.po_line_id, pr_new.invoice_id);
449 FETCH fetch_tax_distribution INTO v_count_tax_dist;
450 CLOSE fetch_tax_distribution;
451
452 lv_rcpt_tax_exists := 'N'; --Added for Bug #12886913
453 lv_po_tax_exists := 'N'; --Added for Bug #12886913
454
455 /* Added for Bug #12886913 *//*Start*/
456 IF pr_new.match_type = 'ITEM_TO_RECEIPT'
457 THEN
458
459 SELECT 'Y'
460 INTO lv_rcpt_tax_exists
461 FROM DUAL
462 WHERE EXISTS (SELECT '1'
463 FROM JAI_RCV_LINE_TAXES
464 WHERE shipment_line_id IN (SELECT shipment_line_id
465 FROM RCV_TRANSACTIONS
466 WHERE po_header_id = pr_new.po_header_id
467 AND po_line_id = pr_new.po_line_id
468 AND source_document_code = 'PO')
469 );
470
471 ELSIF pr_new.match_type = 'ITEM_TO_PO'
472 THEN
473
474 SELECT 'Y'
475 INTO lv_po_tax_exists
476 FROM DUAL
477 WHERE EXISTS (SELECT '1'
478 FROM JAI_PO_TAXES
479 WHERE po_header_id = pr_new.po_header_id
480 AND po_line_id = pr_new.po_line_id);
481
482 END IF;
483
484 /* Added for Bug #12886913 *//*Start*/
485
486 IF v_debug = 'Y' THEN
487 UTL_FILE.PUT_LINE(v_myfilehandle, 'invoice_id -> '|| pr_new.invoice_id
488 ||', po_distribution_id -> ' ||pr_new.po_distribution_id
489 || ', line_type_lookup_code -> ' ||pr_new.line_type_lookup_code
490 || ', amount -> ' ||pr_new.amount
491 || ', line_type_lookup_code -> ' ||pr_new.line_type_lookup_code
492 || ', v_count_tax_dist -> ' ||v_count_tax_dist
493 || ', v_source -> ' ||v_source
494 );
495 END IF;
496 -- Bug 5361931. Added by Lakshmi Gopalsami. Added debug messages
497 fnd_file.put_line(FND_FILE.LOG, 'invoice_id -> '|| pr_new.invoice_id
498 ||', po_distribution_id -> ' || pr_new.po_distribution_id
499 ||', rcv_transaction_id -> ' || pr_new.rcv_transaction_id
500 ||', match_type -> ' || pr_new.match_type
501 ||', line_type_lookup_code -> ' ||pr_new.line_type_lookup_code
502 ||', amount -> ' || pr_new.amount
503 ||', line_type_lookup_code -> ' ||pr_new.line_type_lookup_code
504 ||', v_count_tax_dist -> ' || v_count_tax_dist
505 ||', v_source -> ' || v_source
506 );
507
508 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of invoice_id is '|| pr_new.invoice_id);
509 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of po_distribution_id -> is '||pr_new.po_distribution_id);
510 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of rcv_transacion_id -> is '||pr_new.rcv_transaction_id);
511 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of match_type -> is '||pr_new.match_type);
512 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of line_type_lookup_code -> '||pr_new.line_type_lookup_code);
513 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Value of v_count_tax_dist -> '||v_count_tax_dist);
514
515 /* Bug 5361931. Added by Lakshmi Gopalsami
516 * Instead of blindly checking for po_distribution_id is not null
517 * split the condition depending on match_type.
518 */
519 /* IF ( ( (pr_new.match_type = 'ITEM_TO_RECEIPT' AND
520 pr_new.rcv_transaction_id IS NOT NULL ) OR
521 (pr_new.match_type = 'ITEM_TO_PO' AND
522 pr_new.po_distribution_id IS NOT NULL
523 )
524 ) AND
525 (pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') ) AND
526 (pr_new.amount >= 0)
527 AND v_source IN ('ERS', 'ASBN') OR (v_source = 'SUPPLEMENT')
528 )
529 AND v_count_tax_dist = 0
530 THEN */
531 -- Chenged for iSupplier 6137011
532 /*For ASBN the po_distribution_id is getting updated later. So we are allowing the temp table to be populated
533 even if the po_distribution_id is null and then updating when the update is happening*/
534 IF ( ( pr_new.match_type = 'ITEM_TO_RECEIPT' AND
535 pr_new.rcv_transaction_id IS NOT NULL AND
536 v_source in ('ERS','ASBN','SUPPLEMENT') )
537 OR
538 ( pr_new.match_type = 'ITEM_TO_PO'
539 AND
540 ( ( v_source in ('SUPPLEMENT') AND
541 pr_new.po_distribution_id IS NOT NULL )
542 or
543 v_source='ERS' /* added for bug 8259434 */
544 or
545 v_source ='ASBN' )
546 )
547 ) and
548 pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') AND
549 pr_new.amount >= 0 and
550 v_count_tax_dist = 0
551 THEN
552
553 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 1');
554 v_rematching := 'PAY_ON_RECEIPT';
555
556 INSERT INTO JAI_AP_MATCH_ERS_T
557 (
558 invoice_id,
559 invoice_line_number, -- Using Invoice_line_number instead of distribution_line_number for Bug# 4445989
560 po_distribution_id,
561 quantity_invoiced,
562 shipment_header_id,
563 receipt_num,
564 receipt_code,
565 rematching,
566 rcv_transaction_id,
567 amount,
568 org_id,
569 creation_date,
570 -- added, Harshita for Bug 4866533
571 created_by,
572 last_updated_by,
573 last_update_date
574 )
575 VALUES
576 (
577 pr_new.invoice_id,
578 pr_new.line_number, -- Using pr_new.line_number instead of pr_new.distribution_line_number, Bug# 4445989
579 pr_new.po_distribution_id,
580 pr_new.quantity_invoiced,
581 v_shipment_header_id,
582 v_receipt_num,
583 v_receipt_code,
584 v_rematching,
585 pr_new.rcv_transaction_id,
586 pr_new.amount,
587 NVL(pr_new.org_id,0),
588 SYSDATE,
589 -- added, Harshita for Bug 4866533
590 fnd_global.user_id,
591 fnd_global.user_id,
592 sysdate
593 );
594
595 ELSIF ( (pr_new.po_distribution_id IS NOT NULL) AND
596 (pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') ) AND
597 (pr_new.amount >= 0) AND
598 v_count_tax_dist > 0 AND
599 v_source IN ('ERS', 'ASBN')
600 )
601 OR (v_source = 'SUPPLEMENT')
602
603 THEN
604 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 2');
605 v_rematching := 'RCV_MATCHING';
606 result := Fnd_Request.set_mode(TRUE);
607 req_id := Fnd_Request.submit_request
608 (
609 'JA', -- Removed SQLAP', Bug# 4557312, BRATHOD
610 'JAINDIST',
611 'TO INSERT TAX Distributions',
612 '',
613 FALSE,
614 pr_new.invoice_id,
615 pr_new.line_number, -- Using line_number instead of Distribution_line_number, Bug# 4445989
616 pr_new.po_distribution_id,
617 pr_new.quantity_invoiced,
618 v_shipment_header_id,
619 v_receipt_num,
620 v_receipt_code,
621 v_rematching,
622 pr_new.rcv_transaction_id,
623 pr_new.amount,
624 NVL(pr_new.org_id,0),
625 pr_new.project_id, -- 5876390, 6012570
626 pr_new.task_id, -- 5876390, 6012570
627 pr_new.expenditure_type, -- 5876390, 6012570
628 pr_new.expenditure_organization_id, -- 5876390, 6012570
629 pr_new.expenditure_item_date -- 5876390, 6012570
630 );
631
632 END IF;
633
634
635
636 IF (
637 (pr_new.match_type = 'ITEM_TO_RECEIPT') AND
638 (pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') ) AND
639 (pr_new.amount >= 0) AND
640 (pr_new.po_distribution_id IS NOT NULL OR pr_new.po_header_id IS NOT NULL) AND --Added by Qinglei on 23-Dec-2011 for Bug#13405876
641 /* Added above or condition for bug 16100273 */
642 v_source NOT IN ('ERS', 'ASBN')
643 )
644 THEN
645 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 3');
646 v_rematching := 'RCV_MATCHING';
647 result := Fnd_Request.set_mode(TRUE);
648 req_id := Fnd_Request.submit_request
649 (
650 'JA', --Removed 'SQLAP',Bug# 4557312, brathod
651 'JAINDIST',
652 'TO INSERT TAX Distributions',
653 '',
654 FALSE,
655 pr_new.invoice_id,
656 pr_new.line_number, -- Using Line_Number instead of Distribution_Line_Number, Bug#4445989
657 pr_new.po_distribution_id,-- pr_new.rcv_transaction_id, commented by Aparajita for bug#2851123
658 pr_new.quantity_invoiced,
659 v_shipment_header_id,
660 v_receipt_num,
661 v_receipt_code,
662 v_rematching,
663 pr_new.rcv_transaction_id,
664 pr_new.amount,
665 NVL(pr_new.org_id,0),
666 pr_new.project_id, -- 5876390, 6012570
667 pr_new.task_id, -- 5876390, 6012570
668 pr_new.expenditure_type, -- 5876390, 6012570
669 pr_new.expenditure_organization_id, -- 5876390, 6012570
670 pr_new.expenditure_item_date
671 );
672
673 ELSIF (
674 (pr_new.match_type = 'ITEM_TO_PO') AND
675 (pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') ) AND
676 (pr_new.amount >= 0) AND
677 (pr_new.PO_DISTRIBUTION_ID IS NOT NULL OR pr_new.po_header_id IS NOT NULL) /*bug 9346307*/
678 AND (v_source NOT IN ('ERS', 'ASBN')) -- cbabu for bug # 2665306
679 )
680 THEN
681 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 4');
682 v_rematching := 'PO_MATCHING';
683 result := Fnd_Request.set_mode(TRUE);
684 req_id := Fnd_Request.submit_request
685 (
686 'JA', --Removed 'SQLAP', Bug# 4557312, Brathod
687 'JAINDIST',
688 'TO INSERT TAX Distributions',
689 '',
690 FALSE,
691 pr_new.invoice_id,
692 pr_new.line_number, -- Using Line_Number instead of Distribution_Line_Number,Bug# 4445989
693 pr_new.po_distribution_id,
694 pr_new.quantity_invoiced,
695 v_shipment_header_id,
696 v_receipt_num,
697 v_receipt_code,
698 v_rematching,
699 pr_new.rcv_transaction_id,
700 pr_new.amount,
701 NVL(pr_new.org_id,0),
702 pr_new.project_id, -- 5876390, 6012570
703 pr_new.task_id, -- 5876390, 6012570
704 pr_new.expenditure_type, -- 5876390, 6012570
705 pr_new.expenditure_organization_id, -- 5876390, 6012570
706 pr_new.expenditure_item_date -- 5876390, 6012570
707 );
708
709 -- Start, cbabu for Bug# 2560026 on 20-nov-2002
710 ELSIF (
711 /*added or condition for bug#14472181*/
712 (pr_new.po_distribution_id IS NOT NULL OR pr_new.po_header_id IS NOT NULL ) AND
713 (pr_new.line_type_lookup_code in ('ITEM', 'ACCRUAL') ) AND
714 (pr_new.amount<0) AND
715 (vend_info_rec.invoice_type_lookup_code = 'DEBIT' ) AND
716 (vend_info_rec.cancelled_date IS NULL)
717 )
718 THEN
719 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition 5');
720
721 IF v_source = 'RTS' THEN
722 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition of RTS');
723 v_receipt_code := 'RECEIPT';
724 v_rematching := 'PAY_ON_RECEIPT';
725 INSERT INTO JAI_AP_MATCH_ERS_T (
726 invoice_id, invoice_line_number, -- Bug# 4445989
727 po_distribution_id, quantity_invoiced, shipment_header_id,
728 receipt_num, receipt_code, rematching,
729 rcv_transaction_id, amount, org_id, creation_date,
730 -- added, Harshita for Bug 4866533
731 created_by, last_updated_by, last_update_date
732 ) VALUES (
733 pr_new.invoice_id, pr_new.line_number, -- Bug# 4445989
734 pr_new.po_distribution_id, pr_new.quantity_invoiced, v_shipment_header_id,
735 v_receipt_num, v_receipt_code, v_rematching,
736 pr_new.rcv_transaction_id, pr_new.amount,NVL(pr_new.org_id,0), SYSDATE,
737 -- added, Harshita for Bug 4866533
738 fnd_global.user_id, fnd_global.user_id, sysdate
739 );
740
741 ELSIF v_source = 'Manual Invoice Entry' THEN -- this code will be fired if v_source (i.e AP_INVOICES_ALL.source) is 'Manual Invoice Entry'
742
743 jai_cmn_utils_pkg.write_fnd_log_msg('After insert trigger','Inside condition of Manual entry');
744
745 v_rematching := null;
746 IF (pr_new.match_type = 'ITEM_TO_PO' )
747 /* OR (pr_new.match_type IS NULL AND pr_new.parent_invoice_id IS NOT NULL) -- cbabu for Bug# 2717471 */
748 THEN
749 IF pr_new.rcv_transaction_id IS NOT NULL THEN -- cbabu for Bug# 2717471
750 v_rematching := 'RCV_MATCHING'; -- cbabu for Bug# 2717471
751 ELSE
752 v_rematching := 'PO_MATCHING';
753 END IF;
754 ELSIF pr_new.match_type = 'ITEM_TO_RECEIPT' THEN
755 v_rematching := 'RCV_MATCHING';
756 END IF;
757
758
759 IF v_debug = 'Y' THEN
760 UTL_FILE.PUT_LINE(v_myfilehandle, 'invoice_id -> '|| pr_new.invoice_id
761 ||', po_distribution_id -> ' ||pr_new.po_distribution_id
762 || ', line_type_lookup_code -> ' ||pr_new.line_type_lookup_code
763 || ', amount -> ' ||pr_new.amount
764 || ', line_type_lookup_code -> ' ||pr_new.line_type_lookup_code
765 || ', v_shipment_header_id -> ' ||v_shipment_header_id
766 || ', v_receipt_num -> ' ||v_receipt_num
767 || ', v_receipt_code -> ' ||v_receipt_code
768 || ', v_rematching -> ' ||v_rematching
769 );
770 UTL_FILE.PUT_LINE(v_myfilehandle, ', rcv_transaction_id -> ' ||pr_new.rcv_transaction_id
771 || ', amount -> ' ||pr_new.amount
772 || ', org_id -> ' ||pr_new.org_id
773 || ', v_source -> ' ||v_source
774 );
775 END IF;
776
777 IF v_rematching IS NOT NULL THEN
778 result := Fnd_Request.set_mode(TRUE);
779 req_id := Fnd_Request.submit_request (
780 'JA', --Removed 'SQLAP', Bug# 4557312, Brathod
781 'JAINDIST', 'To INSERT TAX Distributions', '', FALSE,
782 pr_new.invoice_id, pr_new.line_number, -- Bug#4445989
783 pr_new.po_distribution_id, pr_new.quantity_invoiced,
784 v_shipment_header_id, v_receipt_num, v_receipt_code, v_rematching,
785 pr_new.rcv_transaction_id, pr_new.amount,NVL(pr_new.org_id,0),
786 pr_new.project_id, pr_new.task_id,pr_new.expenditure_type -- 5876390, 6012570
787 , pr_new.expenditure_organization_id, pr_new.expenditure_item_date -- 5876390, 6012570
788 );
789 END IF;
790
791 END IF;
792
793 -- End, cbabu for Bug# 2560026
794 -- Added by Jason Liu for retroactive price on 2008/01/07
795 ----------------------------------------------------------------------
796 ELSIF (pr_new.line_type_lookup_code ='RETROITEM' AND
797 v_source = 'PPA' AND pr_new.match_type = 'PO_PRICE_ADJUSTMENT')
798 THEN
799 -- Added by Jason Liu for bug#6918386
800 ----------------------------------------------------------------------
801 IF (pr_new.rcv_transaction_id IS NULL)
802 THEN
803 v_rematching := 'PO_MATCHING';
804 ELSE
805 v_rematching := 'RCV_MATCHING';
806 END IF;
807 ----------------------------------------------------------------------
808 result := Fnd_Request.set_mode(TRUE);
809 req_id := Fnd_Request.submit_request
810 (
811 'JA', --Removed 'SQLAP',Bug# 4557312, brathod
812 'JAINDIST',
813 'TO INSERT TAX Distributions',
814 '',
815 FALSE,
816 pr_new.invoice_id,
817 pr_new.line_number, -- Using Line_Number instead of Distribution_Line_Number, Bug#4445989
818 pr_new.po_distribution_id,-- pr_new.rcv_transaction_id, commented by Aparajita for bug#2851123
819 pr_new.quantity_invoiced,
820 v_shipment_header_id,
821 v_receipt_num,
822 v_receipt_code,
823 v_rematching,
824 pr_new.rcv_transaction_id,
825 pr_new.amount,
826 NVL(pr_new.org_id,0),
827 pr_new.project_id, -- 5876390, 6012570
828 pr_new.task_id, -- 5876390, 6012570
829 pr_new.expenditure_type, -- 5876390, 6012570
830 pr_new.expenditure_organization_id, -- 5876390, 6012570
831 pr_new.expenditure_item_date
832 );
833 END IF; -- ITEM_TO_PO
834 ----------------------------------------------------------------------
835 IF v_debug = 'Y' THEN
836 UTL_FILE.fclose(v_myfilehandle);
837 END IF;
838 /* Added an exception block by Ramananda for bug#4570303 */
839 EXCEPTION
840 /* Added for bug 12886913 */ /* Start */
841 WHEN NO_DATA_FOUND THEN
842 IF ((pr_new.match_type = 'ITEM_TO_RECEIPT' AND lv_rcpt_tax_exists = 'N') OR
843 (pr_new.match_type = 'ITEM_TO_PO' AND lv_po_tax_exists = 'N'))
844 THEN
845 Pv_return_message := 'No IL tax exist for the matched line so skipping the call to the India tax insert concurrent request ' || substr(sqlerrm,1,1900);
846 ELSE
847 Pv_return_message := 'Encountered an error in JAI_AP_ILA_TRIGGER_PKG.ARI_T1 as no data exists ' || substr(sqlerrm,1,1900);
848 END IF;
849 /* Added for bug 12886913 */ /* End */
850 WHEN OTHERS THEN
851 Pv_return_code := jai_constants.unexpected_error;
852 Pv_return_message := 'Encountered an error in JAI_AP_ILA_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
853 END ARI_T1 ;
854
855 END JAI_AP_ILA_TRIGGER_PKG ;
[Home] [Help]