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