1 PACKAGE BODY jai_rcv_third_party_pkg AS
2 /* $Header: jai_rcv_3p_prc.plb 120.13.12010000.2 2008/10/20 14:19:58 vumaasha ship $ */
3 /* --------------------------------------------------------------------------------------
4 Filename: jai_rcv_third_party_pkg_b.sql
5
6 Change History:
7
8 Date Bug Remarks
9 --------- ---------- -------------------------------------------------------------
10 27-jan-05 Bug#3940741 Created by Aparajita. Version # 115.0.
11
12 This is a part of correction ER, done in phases and is concluded
13 with the receipt deplug, which is being shipped along with
14 Service and Education Cess solution.
15
16 This obsoletes the third party code in the old receiving, namely the procedures,
17 1. Ja_In_Con_Ap_Req
18 2. ja_in_receipt_ap_interface.
19
20 Here procedure Process_batch is attached to the concurrent JAINTPRE
21 for generating third party invoices and it calls the procedure
22 process_receipt for each pending shipment_header_id.
23
24 Functionality addressed here is that a receipt is processed for
25 third party invoices only once. All RECEIVE and CORRECT of RECEIVE
26 type of transactions are considered for generating third party invoices.
27
28 If a CORRECT to RECEIVE happens after third party invoice is
29 generated, third_party_flag in JAI_RCV_TRANSACTIONS is set to 'G'
30 to indicate that third party invoice has already been generated.
31
32 This clean up also has introduced two tables which help in tracking
33 third party invoices.
34
35 1. jai_rcv_tp_batches
36 2. jai_rcv_tp_invoices
37
38
39 14-mar-2005 bug#4284505 ssumaith - version 115.1
40
41 Code has been added in the package body to insert third party taxes in the
42 new table created for this bug. The table is jai_rcv_tp_inv_details.
43
44 A new procedure populate_tp_invoice_id has been created which does the actual
45 invoice id update in the jai_Rcv_Tp_inv_Details table.
46
47
48 This table maintains tax level details of third party taxes, and it will be
49 used by the service tax processing concurrent.
50
51
52 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
53 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
54
55 08-Jun-2005 Version 116.3 jai_rcv_3p_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
56 as required for CASE COMPLAINCE.
57
58 13-Jun-2005 File Version: 116.4
59 Ramananda for bug#4428980. Removal of SQL LITERALs is done
60
61 08-Jul-2005 Sanjikum for Bug#4482462
62 1) Removed the column payment_method_lookup_code from cursor - c_get_vendor_details
63 2) In the procedure process_receipt, commented the value of parameter - p_payment_method_lookup_code
64 while calling procedure - jai_ap_utils_pkg.insert_ap_inv_interface
65
66 13-Aug-2005 rchandan for bug#4551623. File version 120.2.
67 Changed the order of parameters of process_batch and added a default NULL to p_simulation.
68 p_simulation is replaced with nvl(p_simulation,'N') in process_batch procedure
69
70 02-Dec-2005 Bug 4774647. Added by Lakshmi Gopalsami Version 120.3
71 Passed operating unit also as this parameter
72 has been added by base .
73
74 23-Jan-2006 Bug4941642. Added by Lakshmi Gopalsami Version 120.4
75 (1) Added conditions in procedure process_receipt
76 in cursor c_thirdparty_tax_rec.
77 (a) Added shipment header id condition
78 (b) added aliases.
79 (c) Removed two separate conditions on jai_rcv_Transactions
80 and clubbed into a single one.
81 (2) Added aliases for the following cursors.
82 (a) c_get_thirdparty_count
83 (b) c_get_thirdparty_null_site_cnt
84 (c) c_get_tparty_invalid_comb_cnt
85 Also added shipment_header_id and shipment_line_id
86 condition in the above cursors. Changed IN clause to
87 exists due to performance issue.
88
89 (3) Added transaction_id in cursor c_pending_tp_receipts
90
91 25-Aug-2006 Bug 5490479, Added by aiyer, File version 120.7
92 Issue:-
93 Org_id parameter in all MOAC related Concurrent programs is getting derived from the profile org_id
94 As this parameter is hidden hence not visible to users and so users cannot choose any other org_id from the security profile.
95
96 Fix:-
97 1. The Multi_org_category field for all MOAC related concurrent programs should be set to 'S' (indicating single org reports).
98 This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
99 security profile.
100 2. Remove the default value for the parameter p_org_id and make it Required false, Display false. This would ensure that null value gets passed
101 to the called procedures/ reports.
102 3. Change the called procedures/reports. Remove the use of p_org_id and instead derive the org_id using the function mo_global.get_current_org_id
103 This change has been made many procedures and reports.
104
105 11-May-2007 Bug5620503, CSahoo, File Version 120.8
106 FORWARD PORTING BUG FOR R11I BUG 5613772
107 Made some changes to the cursor c_get_vendor_details.
108
109 20-Jun-2007 CSahoo for bug#6139899, File Version 120.9
110 modified the code in process_receipt procedure. added the p_org_id paramter in the call to
111 jai_ap_utils_pkg.insert_ap_inv_lines_interface and jai_ap_utils_pkg.insert_ap_inv_interface procedures.
112
113 09-Dec-2007 Code changed for inclusive tax by Eric
114
115 06-Feb-2008 Code changed for bug#6790599 by Eric
116
117 21-Apr-2008 Code changed for bug#6971486 by Eric
118 23-Apr-2008 Code changed for bug#6997730 and bug#6988610
119
120 Future Dependencies For the release Of this Object:-
121 ==================================================
122 (Please add a row in the section below only if your bug introduces a dependency due to spec change/
123 A new call to a object/A datamodel change )
124
125 ------------------------------------------------------------------------------------------------------
126 Version Bug Dependencies (including other objects like files if any)
127 -------------------------------------------------------------------------------------------------------
128 115.0 4146708 The new tables have been created through the script attached to bug
129 for service and cess datamodel change.
130
131 ----------------------------------------------------------------------------------------- */
132
133 /****************************** Start process_pending_receipts ****************************/
134
135 procedure process_batch
136 (
137 errbuf out nocopy VARCHAR2,
138 retcode out nocopy VARCHAR2,
139 p_batch_name in VARCHAR2,
140 /* Bug 5096787. Added by LGOPALSA Added parameter p_org_id */
141 p_org_id in NUMBER /* This parameter would no more be used after application of the bug 5490479- Aiyer, */,
142 p_simulation in VARCHAR2 default null,
143 p_debug in NUMBER default 1
144 )
145 is
146
147 /* Added by Ramananda for removal of SQL LITERALs */
148 lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
149 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
150
151 cursor c_pending_tp_receipts(cp_org_id number) is
152 /* modified cursor - included org_id parameter for bug 4695630 */
153 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
154 select shipment_header_id -- Bug 4941642
155 from JAI_RCV_TRANSACTIONS jrt
156 where ( transaction_type = lv_ttype_receive --'RECEIVE'
157 or
158 (transaction_type = lv_ttype_correct and parent_transaction_type = lv_ttype_receive) --'CORRECT', 'RECEIVE'
159 )
160 and third_party_flag = 'N'
161 and exists
162 (
163 select '1'
164 from JAI_RCV_LINES jrl
165 where jrt.shipment_header_id = jrl.shipment_header_id
166 and jrt.shipment_line_id = jrl.shipment_line_id
167 /*Bug 4941642. Added by Lakshmi Gopalsami
168 Added transaction_id condition
169 */
170 and jrt.transaction_id = jrl.transaction_id
171 and jrl.tax_modified_flag <> 'Y'
172 )
173 AND jrt.organization_id = cp_org_id /* added this condition for bug 4695630 */
174 group by shipment_header_id
175 order by shipment_header_id;
176
177 cursor c_get_tp_batch_id is
178 select jai_rcv_tp_batches_s.nextval from dual;
179
180 cursor c_no_of_invoice_generated(cp_batch_id number) is
181 select count(batch_invoice_id)
182 from jai_rcv_tp_invoices
183 where batch_id = cp_batch_id;
184
185
186 r_pending_tp_receipts c_pending_tp_receipts%rowtype;
187
188 lv_process_flag VARCHAR2(1);
189 lv_process_message VARCHAR2(256);
190 ln_batch_id NUMBER;
191 ln_no_of_invoice_generated NUMBER;
192
193 ln_req_id NUMBER;
194 ln_uid NUMBER; --File.Sql.35 Cbabu := fnd_global.user_id;
195 lv_temp VARCHAR2(100);
196 ln_org_id NUMBER; /*Added by aiyer for the bug 5490479 */
197
198 begin
199
200 ln_uid := fnd_global.user_id;
201 /*
202 || Start of bug 5490479
203 || Added by aiyer for the bug 5490479
204 || Get the operating unit (org_id)
205 */
206 ln_org_id := mo_global.get_current_org_id;
207 fnd_file.put_line(fnd_file.log, 'Operating unit ln_org_id is -> '||ln_org_id);
208
209 /*End of bug 5490479 */
210 /* This is to identify the path in SQL TRACE file if any problem occured */
211 SELECT 'jai_rcv_third_party_pkg.process_pending_receipts' INTO lv_temp FROM DUAL;
212
213 open c_get_tp_batch_id;
214 fetch c_get_tp_batch_id into ln_batch_id;
215 close c_get_tp_batch_id;
216
217 if p_debug >= 1 then
218 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 1 : Start of procedure jai_rcv_third_party_pkg.process_pending_receipts ****');
219 end if;
220
221 /* Get all receipts where third party needs to be processed. Here only
222 RECEIVE or CORRECT to RECEIVE type of transactions are considered */
223
224 /* Added by Ramananda for removal of SQL LITERALs */
225 lv_ttype_receive := 'RECEIVE' ;
226 lv_ttype_correct := 'CORRECT' ;
227
228 /* Bug 4695630 Added by vumaasha
229 Depending on the value of the ln_org_id(operating unit)
230 we have to process the records
231 Added the following cursor and added cursor parameter for r_pending_tp_receipts
232 */
233
234 /* start of bug 4695630 */
235
236 for c_sel_org in (SELECT organization_id
237 FROM org_organization_definitions
238 WHERE operating_unit = ln_org_id
239 )
240
241 loop
242
243 Fnd_File.put_line(Fnd_File.LOG,
244 'Debug Msg 1 : Inside org definition and processing org '||
245 c_sel_org.organization_id);
246
247
248 for r_pending_tp_receipts in c_pending_tp_receipts(c_sel_org.organization_id) loop
249
250 lv_process_flag := null;
251 lv_process_message := null;
252
253 process_receipt
254 (
255 p_batch_id => ln_batch_id,
256 p_shipment_header_id => r_pending_tp_receipts.shipment_header_id,
257 p_process_flag => lv_process_flag,
258 p_process_message => lv_process_message,
259 p_debug => p_debug,
260 p_simulation => nvl(p_simulation, 'N')
261 );
262
263
264 insert into jai_rcv_tp_batches
265 (
266 batch_id ,
267 shipment_header_id ,
268 process_flag ,
269 process_message ,
270 dummy_flag ,
271 created_by ,
272 creation_date ,
273 last_update_login ,
274 last_update_date ,
275 last_updated_by ,
276 program_application_id,
277 program_id,
278 program_login_id,
279 request_id
280 )
281 values
282 (
283 ln_batch_id ,
284 r_pending_tp_receipts.shipment_header_id,
285 lv_process_flag ,
286 lv_process_message ,
287 nvl(p_simulation, 'N') ,
288 ln_uid ,
289 sysdate ,
290 ln_uid ,
291 sysdate ,
292 null ,
293 fnd_profile.value('PROG_APPL_ID'),
294 fnd_profile.value('CONC_PROGRAM_ID'),
295 fnd_profile.value('CONC_LOGIN_ID'),
296 fnd_profile.value('CONC_REQUEST_ID')
297 );
298
299
300 if nvl(p_simulation, 'N') <> 'Y' then
301 update JAI_RCV_TRANSACTIONS jrt
302 set third_party_flag = lv_process_flag
303 where shipment_header_id = r_pending_tp_receipts.shipment_header_id
304 and ( transaction_type = 'RECEIVE'
305 or
306 (transaction_type = 'CORRECT' and parent_transaction_type = 'RECEIVE')
307 )
308 and third_party_flag = 'N'
309 and exists
310 (
311 select '1'
312 from JAI_RCV_LINES jrl
313 where jrt.shipment_header_id = jrl.shipment_header_id
314 and jrt.shipment_line_id = jrl.shipment_line_id
315 and jrl.tax_modified_flag <> 'Y'
316 );
317 end if;
318
319
320 end loop; /* c_pending_tp_receipts */
321 END loop; /* c_sel_org */
322 /* end of bug 4695630 */
323
324
325 open c_no_of_invoice_generated(ln_batch_id);
326 fetch c_no_of_invoice_generated into ln_no_of_invoice_generated;
327 close c_no_of_invoice_generated;
328
329 if ln_no_of_invoice_generated > 0 and nvl(p_simulation, 'N') <> 'Y' then
330
331 /* Processing has created some invoices, invoking the payable open interface for their import */
332 if p_debug >= 1 then
333 Fnd_File.put_line(Fnd_File.LOG,
334 'Debug Level 1 : Invoking APXIIMPT as no of invoices created is :'
335 || to_char(ln_no_of_invoice_generated) );
336 end if;
337
338 ln_uid := fnd_global.user_id;
339 ln_req_id :=
340 Fnd_Request.submit_request
341 (
342 'SQLAP',
343 'APXIIMPT',
344 'Third party Invoices - Payables open interface Import',
345 '',
346 false,
347 /*Bug 4774647. Added by Lakshmi Gopalsami
348 Passed operating unit also as this parameter has been
349 added by base .*/
350
351 '',
352 'INDIA TAX INVOICE', /*--'RECEIPT', --Ramanand for bug#4388958 */
353 '',
354 p_batch_name,
355 '',
356 '',
357 '',
358 'Y',
359 'N',
360 'Y', /* modified for bug 4695630 */
361 'N',
362 1000,
363 ln_uid,
364 NULL
365 );
366
367 end if; /*ln_total_no_of_invoices > 0 then*/
368
369 << exit_from_procedure >>
370
371 if p_debug >= 1 then
372 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 1 : End of procedure jai_rcv_third_party_pkg.process_pending_receipts ****');
373 end if;
374
375 return;
376
377 exception
378 when others then
379 retcode := 2;
380 errbuf := 'jai_rcv_third_party_pkg.process_pending_receipts:' || sqlerrm;
381 FND_FILE.put_line(FND_FILE.log, 'Error in jai_rcv_third_party_pkg.process_pending_receipts :'||sqlerrm);
382 return;
383 end process_batch;
384 /****************************** End process_pending_receipts ****************************/
385
386
387 /****************************** Start process_receipt ****************************/
388
389 procedure process_receipt
390 (
391 p_batch_id in number,
392 p_shipment_header_id in number,
393 p_process_flag OUT NOCOPY varchar2,
394 p_process_message OUT NOCOPY varchar2,
395 p_debug in number default 1,
396 p_simulation in varchar2
397 )
398 is
399
400 cursor c_rcv_shipment_headers(p_shipment_header_id number) is
401 select receipt_num
402 from rcv_shipment_headers
403 where shipment_header_id = p_shipment_header_id;
404
405 cursor c_rcv_transactions
406 (p_shipment_header_id number)is
407 select
408 vendor_id,
409 vendor_site_id, --added by eric for inclusive tax on 20-dec-2007
410 organization_id,
411 transaction_date,
412 po_header_id,
413 po_line_location_id,
414 po_distribution_id,
415 currency_code,
416 currency_conversion_type,
417 currency_conversion_date,
418 currency_conversion_rate
419 from rcv_transactions
420 where shipment_header_id = p_shipment_header_id
421 and transaction_type = 'RECEIVE';
422
423 /* Added by Ramananda for removal of SQL LITERALs */
424 lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
425 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
426
427 /* Bug 4941642. Added by Lakshmi Gopalsami
428 Added aliases for the following cursors and
429 Added alias and shipment_header_id and
430 shipment_line_id condition in inner query
431 (1) c_get_thirdparty_count
432 (2) c_get_thirdparty_null_site_cnt
433 (3) c_get_tparty_invalid_comb_cnt
434
435 */
436 cursor c_get_thirdparty_count /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
437 (
438 p_shipment_header_id number,
439 p_po_vendor_id number
440 )
441 is
442 select count(jrlt.tax_line_no)
443 from JAI_RCV_LINE_TAXES jrlt
444 where jrlt.shipment_header_id = p_shipment_header_id
445 and EXISTS
446 (
447 select 1
448 from JAI_RCV_TRANSACTIONS jrt
449 where jrt.shipment_header_id = jrlt.shipment_header_id
450 AND jrt.shipment_line_id = jrlt.shipment_line_id
451 AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
452 or
453 (jrt.transaction_type = lv_ttype_correct
454 and jrt.parent_transaction_type = lv_ttype_receive
455 )
456 )
457 and jrt.third_party_flag = 'N'
458 )
459 and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
460 and jrlt.vendor_id > 0
461 and jrlt.tax_amount <> 0
462 and jrlt.tax_amount <> 0
463 and jrlt.vendor_id <> p_po_vendor_id;
464
465 /* Bug 4941642. Added by Lakshmi Gopalsami
466 Added alias and shipment_header_id and
467 shipment_line_id condition in inner query
468 */
469 cursor c_get_thirdparty_null_site_cnt /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
470 (
471 p_shipment_header_id number,
472 p_po_vendor_id number
473 )
474 is
475 select count(jrlt.tax_line_no)
476 from JAI_RCV_LINE_TAXES jrlt
477 where jrlt.shipment_header_id = p_shipment_header_id
478 and EXISTS
479 (
480 select 1
481 from JAI_RCV_TRANSACTIONS jrt
482 where jrt.shipment_header_id = jrlt.shipment_header_id
483 AND jrt.shipment_line_id = jrlt.shipment_line_id
484 AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
485 or
486 (jrt.transaction_type = lv_ttype_correct
487 and jrt.parent_transaction_type = lv_ttype_receive
488 )
489 )
490 and jrt.third_party_flag = 'N'
491 )
492 and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
493 and jrlt.vendor_id > 0
494 and jrlt.tax_amount <> 0
495 and jrlt.vendor_id <> p_po_vendor_id
496 and jrlt.vendor_site_id is null;
497
498 /* Bug 4941642. Added by Lakshmi Gopalsami
499 Added alias and shipment_header_id and
500 shipment_line_id condition in inner query
501 */
502
503 cursor c_get_tparty_invalid_comb_cnt /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
504 (
505 p_shipment_header_id number,
506 p_po_vendor_id number
507 )
508 is
509 select count(jrlt.tax_line_no)
510 from JAI_RCV_LINE_TAXES jrlt
511 where jrlt.shipment_header_id = p_shipment_header_id
512 and EXISTS
513 (
514 select 1
515 from JAI_RCV_TRANSACTIONS jrt
516 where jrt.shipment_header_id = jrlt.shipment_header_id
517 AND jrt.shipment_line_id = jrlt.shipment_line_id
518 AND ( jrt.transaction_type = lv_ttype_receive
519 or
520 (jrt.transaction_type = lv_ttype_correct
521 and jrt.parent_transaction_type = lv_ttype_receive
522 )
523 )
524 and jrt.third_party_flag = 'N'
525 )
526 and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
527 and jrlt.vendor_id > 0
528 and jrlt.tax_amount <> 0
529 and jrlt.vendor_id <> p_po_vendor_id
530 and jrlt.vendor_site_id is not null
531 and not exists
532 (select '1'
533 from po_vendor_sites_all pvs
534 where pvs.vendor_id = jrlt.vendor_id
535 and pvs.vendor_site_id = jrlt.vendor_site_id
536 );
537
538 cursor c_get_assets_tracking_flag(p_shipment_header_id number) is
539 select decode(count(inventory_item_id), 0, 'N', 'Y')
540 from JAI_INV_ITM_SETUPS
541 where item_class = 'CGIN'
542 and (inventory_item_id, organization_id)
543 in
544 (
545 select item_id, ship_to_location_id
546 from rcv_shipment_lines
547 where shipment_header_id = p_shipment_header_id
548 );
549
550 cursor c_get_po_dist_account (p_po_distribution_id number) is
551 select accrual_account_id
552 from po_distributions_all
553 where po_distribution_id = p_po_distribution_id;
554
555 cursor c_get_latest_po_dist_account (p_line_location_id number) is
556 select accrual_account_id
557 from po_distributions_all
558 where line_location_id = p_line_location_id
559 and creation_date in
560 (
561 select max(creation_date)
562 from po_distributions_all
563 where line_location_id = p_line_location_id
564 );
565
566
567 cursor c_get_vendor_details (p_vendor_id number) is
568 select
569 vendor_name,
570 terms_id,
571 NULL payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
572 /* added the null in the above line by csahoo 5620503 */
573 pay_group_lookup_code,
574 NULL org_id -- added by csahoo for bug#6139899
575 from po_vendors
576 where vendor_id = p_vendor_id;
577
578 /* cbabu for Bug#5613772 */
579 cursor c_get_vendor_site_dtls (p_vendor_site_id number) is
580 SELECT
581 b.vendor_name,
582 a.terms_id,
583 a.payment_method_lookup_code,
584 a.pay_group_lookup_code,
585 a.org_id -- added by csahoo for bug#6139899
586 from po_vendor_sites_all a, po_vendors b
587 where a.vendor_id = b.vendor_id
588 AND a.vendor_site_id = p_vendor_site_id;
589
590
591 cursor c_get_goods_received_date(p_vendor_id number, p_vendor_site_id number) is
592 select
593 decode(terms_date_basis, 'Goods Received', sysdate, null)
594 from po_vendor_sites_all
595 where vendor_id = p_vendor_id
596 and vendor_site_id = p_vendor_site_id;
597
598 cursor c_get_inv_run_no is
599 select jai_rcv_tp_invoices_s1.nextval /* renamed the sequence to point to the correct sequence name - ssumaith - sequence change process */
600 from dual;
601
602 cursor c_check_if_already_processed(p_shipment_header_id number) is
603 select count(transaction_id)
604 from JAI_RCV_TRANSACTIONS
605 where shipment_header_id = p_shipment_header_id
606 and third_party_flag not in ('N', 'X');
607
608
609 lv_temp varchar2(100);
610 ln_thirdparty_count number;
611 ln_thirdparty_null_site_cnt number;
612 ln_tparty_invalid_comb_cnt number;
613 lv_assets_tracking_flag varchar2(1);
614 ln_accrual_account number;
615 ld_goods_received_date date;
616 lv_receipt_num rcv_shipment_headers.receipt_num%type;
617
618 r_rcv_transactions c_rcv_transactions%rowtype;
619 r_get_vendor_details c_get_vendor_details%rowtype;
620
621 lv_description ap_invoices_interface.description%type;
622 ln_tax_amount number;
623 lv_currency_conversion_type rcv_transactions.currency_conversion_type%type;
624 lv_currency_conversion_rate rcv_transactions.currency_conversion_rate%type;
625 lv_currency_conversion_date date;
626 ln_uid number; --File.Sql.35 Cbabu := fnd_global.user_id;
627 ln_inv_run_no number;
628 lv_invoice_num ap_invoices_all.invoice_num%type;
629 lv_func_currency gl_sets_of_books.currency_code%type;
630 ln_gl_set_of_books_id gl_sets_of_books.set_of_books_id%type;
631 ln_interface_invoice_id number;
632 ln_interface_line_id number;
633 ln_check_if_already_processed number;
634
635
636 ln_vendor_id po_vendors.vendor_id%type;
637 ln_vendor_site po_vendor_sites_all.vendor_site_id%type;
638 lv_currency fnd_currencies.currency_code%type;
639 lv_vendor_has_changed VARCHAR2(10);
640 lb_tp_taxes_processed VARCHAR2(10);
641 ln_batch_invoice_id NUMBER;
642 ln_batch_line_id NUMBER;
643 ln_line_number NUMBER;
644 ln_cm_line_number NUMBER; --added by eric for inclusive tax
645 ln_to_insert_line_number NUMBER; --added by eric for inclusive tax
646 ln_org_id po_vendor_sites_all.org_id%type; -- added by csahoo for bug#6139899
647 ln_lines_to_insert NUMBER default 1; --added by eric for inclusive tax on 20-dec-2007
648 ln_tax_line_amount NUMBER; --added by eric for inclusive tax on 20-dec-2007
649 orig_vndr_details_rec c_get_vendor_details%rowtype; --added by eric for inclusive tax on 20-dec-2007
650 ld_orig_goods_recv_date DATE; --added by eric for inclusive tax on 20-dec-2007
651 lv_orig_currcy_conver_type rcv_transactions.currency_conversion_type%type; --added by eric for inclusive tax on 20-dec-2007
652 lv_orig_currcy_conver_rate rcv_transactions.currency_conversion_rate%type; --added by eric for inclusive tax on 20-dec-2007
653 lv_orig_currcy_conver_date date; --added by eric for inclusive tax on 20-dec-2007
654
655 --added the below cursor for bug#6988610 by eric on Apr 24,2008 ,begin
656 -----------------------------------------------------------------------
657 ln_totl_incl_tax_amount number;
658
659 CURSOR get_totl_incl_tax_amount
660 ( pn_shipment_header_id IN NUMBER
661 , pn_vendor_id IN NUMBER
662 , pn_vendor_site_id IN NUMBER
663 , pv_currency IN VARCHAR2
664 )
665 IS
666 select
667 sum(nvl(jrtv.tax_amount,0)) totl_incl_tax_amount
668 from
669 JAI_RCV_TAX_V jrtv
670 , jai_cmn_taxes_all jcta --added by eric for inclusive tax
671 where
672 ( jrtv.transaction_id, jrtv.shipment_line_id ) IN
673 ( select transaction_id, shipment_line_id
674 from JAI_RCV_TRANSACTIONS jrt
675 where shipment_header_id = pn_shipment_header_id
676 and ( transaction_type = lv_ttype_receive --'RECEIVE'
677 or
678 (transaction_type = lv_ttype_correct
679 and parent_transaction_type = lv_ttype_receive)
680 )
681 and third_party_flag = 'N'
682
683 )
684 and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
685 and jrtv.vendor_id > 0
686 and nvl(jrtv.tax_amount, 0) is not null
687 and jrtv.shipment_header_id = pn_shipment_header_id
688 and jrtv.tax_id = jcta.tax_id
689 and jcta.inclusive_tax_flag = 'Y'
690 and jrtv.vendor_id = pn_vendor_id
691 and jrtv.vendor_site_id = pn_vendor_site_id
692 and jrtv.currency = pv_currency
693 having sum(nvl(jrtv.tax_amount,0)) > 0 ; /* added to take care of complete CORRECTION */
694 ------------------------------------------------------------------------------------------
695 --added by eric for bug#6988610 on Apr 24,2008 ,end
696
697
698 begin
699
700 ln_uid := fnd_global.user_id;
701 ln_vendor_id := -999;
702 ln_vendor_site := -999;
703 lv_currency := '$$$';
704 lv_vendor_has_changed := 'TRUE';
705 lb_tp_taxes_processed := 'FALSE';
706 ln_line_number := 0;
707
708 -- This is to identify the path in SQL TRACE file if any problem occured
709 select 'jai_rcv_third_party_pkg.process_receipt : shipment header - ' || to_char(p_shipment_header_id)
710 into lv_temp from dual;
711
712 if p_debug >= 1 then
713 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 1 : ' ||
714 'Start of procedure jai_rcv_third_party_pkg.process_receipt for shipment header :' ||
715 to_char(p_shipment_header_id)
716 );
717 end if;
718
719 /* Validation#0 : Check if third party has already been processed for the receipt */
720 ln_check_if_already_processed := 0;
721 open c_check_if_already_processed(p_shipment_header_id);
722 fetch c_check_if_already_processed into ln_check_if_already_processed;
723 close c_check_if_already_processed;
724
725 if ln_check_if_already_processed > 0 then
726 p_process_flag := 'G';
727 p_process_message := 'Third party invoices have already got generated for this receipt, cannot process again';
728 goto exit_from_procedure;
729 end if;
730
731
732 /* Validation#1 : Check if PO details exist */
733 open c_rcv_transactions(p_shipment_header_id);
734 fetch c_rcv_transactions into r_rcv_transactions;
735 close c_rcv_transactions;
736
737 if r_rcv_transactions.vendor_id is null then
738
739 if p_debug >= 1 then
740 Fnd_File.put_line(Fnd_File.LOG,
741 ' Debug Level 1 : Details from rcv_transactions are not found for this shipment header, cannot process' );
742 end if;
743
744 p_process_flag := 'E';
745 p_process_message := 'Details from rcv_transactions are not found for this shipment header, cannot process';
746 goto exit_from_procedure;
747
748 end if;
749
750 /* Added by Ramananda for removal of SQL LITERALs */
751 lv_ttype_receive := 'RECEIVE' ;
752 lv_ttype_correct := 'CORRECT' ;
753
754 /* Validation#2 : Check if third party taxes exist */
755 open c_get_thirdparty_count
756 (p_shipment_header_id, r_rcv_transactions.vendor_id);
757 fetch c_get_thirdparty_count into ln_thirdparty_count;
758 close c_get_thirdparty_count;
759
760 if nvl(ln_thirdparty_count, 0) = 0 then
761 /* Not an error condition, but no need to process */
762
763 if p_debug >= 1 then
764 Fnd_File.put_line(Fnd_File.LOG,
765 ' Debug Level 1 : There does not exist any third party tax for this shipment, no need to process' );
766 end if;
767
768 goto exit_from_procedure;
769 end if;
770
771
772 /* Validation#3 : Check if any third party taxes exist with null site*/
773 /* Added by Ramananda for removal of SQL LITERALs */
774 lv_ttype_receive := 'RECEIVE' ;
775 lv_ttype_correct := 'CORRECT' ;
776
777 open c_get_thirdparty_null_site_cnt
778 (p_shipment_header_id, r_rcv_transactions.vendor_id);
779 fetch c_get_thirdparty_null_site_cnt into ln_thirdparty_null_site_cnt;
780 close c_get_thirdparty_null_site_cnt;
781
782 if nvl(ln_thirdparty_null_site_cnt, 0) > 0 then
783
784 if p_debug >= 1 then
785 Fnd_File.put_line(Fnd_File.LOG,
786 ' Debug Level 1 : Error : Third party tax for this shipment exists without site, cannot process ' );
787 end if;
788
789 p_process_flag := 'E';
790 p_process_message := 'Error : Third party tax for this shipment exists without site, cannot process ';
791 goto exit_from_procedure;
792
793 end if;
794
795
796 /* Validation#4 : Check if any third party taxes exist with invalid vendor and site combinations */
797 /* Added by Ramananda for removal of SQL LITERALs */
798 lv_ttype_receive := 'RECEIVE' ;
799 lv_ttype_correct := 'CORRECT' ;
800 open c_get_tparty_invalid_comb_cnt
801 (p_shipment_header_id, r_rcv_transactions.vendor_id);
802 fetch c_get_tparty_invalid_comb_cnt into ln_tparty_invalid_comb_cnt;
803 close c_get_tparty_invalid_comb_cnt;
804
805 if nvl(ln_tparty_invalid_comb_cnt, 0) > 0 then
806
807 if p_debug >= 1 then
808 Fnd_File.put_line(Fnd_File.LOG,
809 ' Debug Level 1 : Error : ' ||
810 'Third party tax for this shipment exists with invalid vendor and site combination, cannot process ' );
811 end if;
812
813 p_process_flag := 'E';
814 p_process_message :=
815 'Error : Third party tax for this shipment exists with invalid vendor and site combination, cannot process ';
816 goto exit_from_procedure;
817
818 end if;
819
820
821 /* All validations are over, control comes here only when the record to be processed is valid */
822
823 /* Get the details required for generating AP invoices */
824 open c_get_assets_tracking_flag(p_shipment_header_id);
825 fetch c_get_assets_tracking_flag into lv_assets_tracking_flag;
826 close c_get_assets_tracking_flag;
827
828 open c_rcv_shipment_headers(p_shipment_header_id);
829 fetch c_rcv_shipment_headers into lv_receipt_num;
830 close c_rcv_shipment_headers;
831
832 if r_rcv_transactions.po_distribution_id is not null then
833
834 open c_get_po_dist_account(r_rcv_transactions.po_distribution_id);
835 fetch c_get_po_dist_account into ln_accrual_account;
836 close c_get_po_dist_account;
837
838 elsif r_rcv_transactions.po_line_location_id is not null then
839
840 open c_get_latest_po_dist_account(r_rcv_transactions.po_line_location_id);
841 fetch c_get_latest_po_dist_account into ln_accrual_account;
842 close c_get_latest_po_dist_account;
843
844 end if;
845
846 if ln_accrual_account is null then
847 p_process_flag := 'E';
848 p_process_message := 'Error : Accrual account not defined, cannot process ';
849 goto exit_from_procedure;
850 end if;
851
852 -- get the functional currency
853 jai_rcv_utils_pkg.get_func_curr
854 (
855 r_rcv_transactions.organization_id,
856 lv_func_currency,
857 ln_gl_set_of_books_id
858 );
859
860 /* Added by Ramananda for removal of SQL LITERALs */
861 lv_ttype_receive := 'RECEIVE' ;
862 lv_ttype_correct := 'CORRECT' ;
863
864 For c_thirdparty_tax_rec IN
865 (
866 /*Bug 4941642. Added by Lakshmi Gopalsami
867 (1) Added shipment header id condition
868 (2) added aliases.
869 (3) Removed two separate conditions on jai_rcv_Transactions
870 and clubbed into a single one.
871 */
872 select
873 jrtv.vendor_id
874 , jrtv.vendor_site_id
875 , jrtv.currency
876 , sum(nvl(jrtv.tax_amount,0)) tax_amount
877 --, nvl(jcta.inclusive_tax_flag,'N') inc_tax_flag --added by eric for inclusive tax
878 , MAX(NVL(jcta.inclusive_tax_flag,'N')) inc_tax_flag --modified by eric for bug#6997730 on Apr-24,2008
879 from
880 JAI_RCV_TAX_V jrtv
881 , jai_cmn_taxes_all jcta --added by eric for inclusive tax
882 where
883 ( jrtv.transaction_id, jrtv.shipment_line_id ) IN
884 ( select transaction_id, shipment_line_id
885 from JAI_RCV_TRANSACTIONS jrt
886 where shipment_header_id = p_shipment_header_id
887 and ( transaction_type = lv_ttype_receive --'RECEIVE'
888 or
889 (transaction_type = lv_ttype_correct
890 and parent_transaction_type = lv_ttype_receive)
891 )
892 and third_party_flag = 'N'
893
894 )
895 and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
896 and jrtv.vendor_id > 0
897 and nvl(jrtv.tax_amount, 0) is not null
898 and jrtv.vendor_id <> r_rcv_transactions.vendor_id /* bug#3957167 */
899 and jrtv.shipment_header_id = p_shipment_header_id
900 and jrtv.tax_id = jcta.tax_id --added by eric for inclusive tax
901 GROUP BY
902 jrtv.vendor_id
903 , jrtv.vendor_site_id
904 , jrtv.currency
905 -- , NVL(jcta.inclusive_tax_flag,'N') --deleted by eric for bug#6997730 on Apr-24,2008
906 having sum(nvl(jrtv.tax_amount,0)) > 0 /* added to take care of complete CORRECTION */
907 )
908 loop
909 Fnd_File.put_line(Fnd_File.LOG, ' ');
910 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 2 : Loop Begin');
911 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : c_thirdparty_tax_rec.vendor_id,c_thirdparty_tax_rec.vendor_site_id,c_thirdparty_tax_rec.currency,c_thirdparty_tax_rec.inc_tax_flag :'
912 || c_thirdparty_tax_rec.vendor_id ||' , '||c_thirdparty_tax_rec.vendor_site_id ||' , '||c_thirdparty_tax_rec.currency ||' , '||c_thirdparty_tax_rec.inc_tax_flag );
913 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : ln_vendor_id,ln_vendor_site,lv_currency :'|| ln_vendor_id||' , '||ln_vendor_site||' , '||lv_currency );
914
915 IF ln_vendor_id <> c_thirdparty_tax_rec.vendor_id OR ln_vendor_site <> c_thirdparty_tax_rec.vendor_site_id OR lv_currency <> c_thirdparty_tax_rec.currency
916 THEN
917 lv_vendor_has_changed := 'TRUE';
918
919 ln_vendor_id := c_thirdparty_tax_rec.vendor_id;
920 ln_vendor_site := c_thirdparty_tax_rec.vendor_site_id;
921 lv_currency := c_thirdparty_tax_rec.currency;
922 ELSE
923 lv_vendor_has_changed := 'FALSE';
924 END IF;
925
926 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : lv_vendor_has_changed :'|| lv_vendor_has_changed );
927
928 if lv_vendor_has_changed = 'TRUE' THEN
929
930 lv_description := null;
931 ln_tax_amount := null;
932 ld_goods_received_date := null;
933
934 -- get the third party vendor details
935 r_get_vendor_details := null;
936 /* following cursor open added by cbabu for bug#5613772 */
937 OPEN c_get_vendor_site_dtls(c_thirdparty_tax_rec.vendor_site_id);
938 FETCH c_get_vendor_site_dtls INTO r_get_vendor_details;
939 CLOSE c_get_vendor_site_dtls;
940
941 ln_org_id := r_get_vendor_details.org_id; -- added by csahoo for bug#6139899
942 /* following if added by cbabu for bug#5613772 */
943 IF r_get_vendor_details.terms_id IS NULL
944 OR r_get_vendor_details.payment_method_lookup_code IS null
945 OR r_get_vendor_details.pay_group_lookup_code IS NULL
946 then
947 open c_get_vendor_details(c_thirdparty_tax_rec.vendor_id);
948 fetch c_get_vendor_details into r_get_vendor_details;
949 close c_get_vendor_details;
950 END if;
951
952 --commented out by eric for inclusive tax ,begin
953 --lv_description := 'Invoice for vendor '|| r_get_vendor_details.vendor_name ||' against receipt no. '|| lv_receipt_num;
954 --commented out by eric for inclusive tax ,end
955
956
957 --added by eric for inclusive tax on 20-dec,2007, Begin
958 -----------------------------------------------------------------------
959 IF (c_thirdparty_tax_rec.inc_tax_flag = 'N') --exclusive tax case
960 THEN
961 ln_lines_to_insert :=1 ;
962 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :Tax is an exclusive tax.Only one AP invoice will be created');
963 ELSE
964 ln_lines_to_insert :=2 ;
965 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :Tax is an inclusive tax. Two AP invoices will be created');
966 END IF;--(c_thirdparty_tax_rec.inc_tax_flag = 'N')
967
968 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :c_thirdparty_tax_rec.inc_tax_flag :'|| c_thirdparty_tax_rec.inc_tax_flag);
969 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :ln_lines_to_insert: ' || ln_lines_to_insert );
970 Fnd_File.put_line(Fnd_File.LOG, ' ');
971
972 FOR i in 1 .. ln_lines_to_insert
973 LOOP
974 -----------------------------------------------------------------------
975 --added by eric for inclusive tax on 20-dec,2007,END
976
977 open c_get_inv_run_no;
978 fetch c_get_inv_run_no into ln_inv_run_no;
979 close c_get_inv_run_no;
980
981
982 --commented out by eric for inclusive tax ,begin
983 --lv_invoice_num := 'RECEIPT/'||lv_receipt_num || '/' || to_char(ln_inv_run_no);
984 --commented out by eric for inclusive tax ,end
985
986
987
988 --added by eric for inclusive tax on 20-dec,2007, Begin
989 -----------------------------------------------------------------------
990 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : lv_description and lv_invoice_num begin:');
991 IF (i =1 ) --normal third party invoice
992 THEN
993 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =1 Branch :');
994
995
996 lv_description := 'Invoice for vendor '
997 || r_get_vendor_details.vendor_name
998 ||' against receipt no. '|| lv_receipt_num;
999 lv_invoice_num := 'RECEIPT/'||lv_receipt_num || '/'
1000 || to_char(ln_inv_run_no);
1001
1002 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =1 Branch : lv_description :' || lv_description);
1003 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =1 Branch : lv_invoice_num :' || lv_invoice_num);
1004 ELSE --(i =2 ),normal third party invoice--debit memo invoice
1005 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : i =2 Branch :');
1006
1007 lv_description := 'Credit Memo for inclusive 3rd party taxes for'
1008 ||' receipt No. ' || lv_receipt_num;
1009 lv_invoice_num := 'ITP-CM/'|| lv_receipt_num || '/'
1010 ||to_char(ln_inv_run_no);
1011
1012 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =2 Branch : lv_description :' || lv_description);
1013 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =2 Branch : lv_invoice_num :' || lv_invoice_num);
1014 END IF;--(i =1 )
1015
1016 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : lv_description and lv_invoice_num end:');
1017 Fnd_File.put_line(Fnd_File.LOG, ' ');
1018 -----------------------------------------------------------------------
1019 --added by eric for inclusive tax on 20-dec,2007, end
1020
1021
1022 ln_tax_amount := c_thirdparty_tax_rec.tax_amount;
1023
1024 if c_thirdparty_tax_rec.currency <> lv_func_currency then
1025 lv_currency_conversion_type := r_rcv_transactions.currency_conversion_type;
1026 lv_currency_conversion_rate := r_rcv_transactions.currency_conversion_rate;
1027 lv_currency_conversion_date := r_rcv_transactions.currency_conversion_date;
1028 else
1029 lv_currency_conversion_type := null;
1030 lv_currency_conversion_rate := null;
1031 lv_currency_conversion_date := null;
1032 end if;
1033
1034 -- get the details for the vendor site
1035 open c_get_goods_received_date(c_thirdparty_tax_rec.vendor_id , c_thirdparty_tax_rec.vendor_site_id);
1036 fetch c_get_goods_received_date into ld_goods_received_date;
1037 close c_get_goods_received_date;
1038
1039 SELECT jai_rcv_tp_invoices_s.nextval
1040 INTO ln_batch_invoice_id
1041 FROM DUAL;
1042
1043 --Tax table need to be inserted once only
1044 --added by eric for inclusive tax on 20-dec,2007, Begin
1045 -----------------------------------------------------------------------
1046 IF (i =1 )
1047 THEN
1048 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : jai_rcv_tp_invoice stable insert beign:');
1049 -----------------------------------------------------------------------
1050 --added by eric for inclusive tax on 20-dec,2007, end
1051 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,jai_rcv_tp_invoice table insert : i =1 Branch :');
1052 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1. Before insert into jai_rcv_tp_invoices ' );
1053
1054
1055 insert into jai_rcv_tp_invoices
1056 (
1057 batch_invoice_id ,
1058 batch_id ,
1059 shipment_header_id ,
1060 vendor_id ,
1061 vendor_site_id ,
1062 invoice_num ,
1063 invoice_currency_code ,
1064 invoice_amount ,
1065 created_by ,
1066 creation_date ,
1067 last_update_login ,
1068 last_update_date ,
1069 last_updated_by,
1070 program_application_id,
1071 program_id,
1072 program_login_id,
1073 request_id
1074 )
1075 values
1076 (
1077 ln_batch_invoice_id ,
1078 p_batch_id,
1079 p_shipment_header_id,
1080 c_thirdparty_tax_rec.vendor_id,
1081 c_thirdparty_tax_rec.vendor_site_id,
1082 lv_invoice_num,
1083 c_thirdparty_tax_rec.currency,
1084 round(ln_tax_amount,2),
1085 ln_uid,
1086 sysdate,
1087 ln_uid,
1088 sysdate,
1089 null,
1090 fnd_profile.value('PROG_APPL_ID'),
1091 fnd_profile.value('CONC_PROGRAM_ID'),
1092 fnd_profile.value('CONC_LOGIN_ID'),
1093 fnd_profile.value('CONC_REQUEST_ID')
1094 );
1095
1096 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 2. After insert into jai_rcv_tp_invoices ' );
1097 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :jai_rcv_tp_invoice table insert end:');
1098 Fnd_File.put_line(Fnd_File.LOG, ' ');
1099 --added by eric for inclusive tax on 20-dec,2007, Begin
1100 -----------------------------------------------------------------------
1101 END IF;--(i =1 )
1102 -----------------------------------------------------------------------
1103 --added by eric for inclusive tax on 20-dec,2007, end
1104
1105
1106 /* Call the package to insert data into ap interface */
1107 if p_simulation <> 'Y' then
1108
1109 ln_interface_invoice_id := null;
1110 ln_interface_line_id := null;
1111
1112 --Ap invoice interface table need to be inserted twice if necessary
1113 --the first time is for the normal third party inv
1114 --the second time is for the credit memo ,in case of inclusive tax
1115
1116 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : insertface table insert begin:');
1117
1118 --added by eric for inclusive tax on 20-dec,2007, Begin
1119 -----------------------------------------------------------------------
1120 IF (i =1 )
1121 THEN
1122 -----------------------------------------------------------------------
1123 --added by eric for inclusive tax on 20-dec,2007, end
1124 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,insertface table : i =1 Branch :');
1125 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 3. Before insert into insert_ap_inv_interface for Standard Invoice' );
1126
1127 jai_ap_utils_pkg.insert_ap_inv_interface
1128 (
1129 p_jai_source => 'Third Party Invoices',
1130 p_invoice_id => ln_interface_invoice_id,
1131 p_invoice_num => lv_invoice_num,
1132 p_invoice_type_lookup_code => 'STANDARD',
1133 p_invoice_date => sysdate,
1134 p_vendor_id => c_thirdparty_tax_rec.vendor_id,
1135 p_vendor_site_id => c_thirdparty_tax_rec.vendor_site_id,
1136 p_invoice_amount => round(ln_tax_amount,2),
1137 p_invoice_currency_code => c_thirdparty_tax_rec.currency,
1138 p_exchange_rate => lv_currency_conversion_rate,
1139 p_exchange_rate_type => lv_currency_conversion_type,
1140 p_exchange_date => lv_currency_conversion_date,
1141 p_terms_id => r_get_vendor_details.terms_id,
1142 p_description => lv_description,
1143 p_source => 'INDIA TAX INVOICE', /* --'RECEIPT', --Ramanand for bug#4388958 */
1144 p_voucher_num => lv_invoice_num,
1145 --p_payment_method_lookup_code => r_get_vendor_details.payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1146 p_pay_group_lookup_code => r_get_vendor_details.pay_group_lookup_code,
1147 p_goods_received_date => ld_goods_received_date,
1148 p_created_by => ln_uid,
1149 p_creation_date => sysdate,
1150 p_last_updated_by => ln_uid,
1151 p_last_update_date => sysdate,
1152 p_last_update_login => null,
1153 p_org_id => ln_org_id -- added by csahoo for bug#6139899
1154 );
1155
1156 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 4. After insert Standard third party Invoice :' || lv_invoice_num ||' into insert_ap_inv_interface');
1157 --added by eric for inclusive tax on 20-dec,2007, Begin
1158 -----------------------------------------------------------------------
1159 ELSIF (i =2 )
1160 THEN
1161 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,insertface table : i =2 Branch :');
1162 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 5. Before insert into insert_ap_inv_interface for CM ' );
1163
1164 --added by eric for BUG#6988610 on Apr-24,2008, Begin
1165 -----------------------------------------------------------------------
1166 OPEN get_totl_incl_tax_amount
1167 ( pn_shipment_header_id =>p_shipment_header_id
1168 , pn_vendor_id =>c_thirdparty_tax_rec.vendor_id
1169 , pn_vendor_site_id =>c_thirdparty_tax_rec.vendor_site_id
1170 , pv_currency =>c_thirdparty_tax_rec.currency
1171 );
1172 FETCH get_totl_incl_tax_amount
1173 INTO ln_totl_incl_tax_amount;
1174 CLOSE get_totl_incl_tax_amount;
1175 -----------------------------------------------------------------------
1176 --added by eric for BUG#6988610 on Apr-24,2008, End
1177
1178 OPEN c_get_vendor_site_dtls(r_rcv_transactions.vendor_site_id);
1179 FETCH c_get_vendor_site_dtls
1180 INTO orig_vndr_details_rec;
1181 CLOSE c_get_vendor_site_dtls;
1182
1183
1184 IF( orig_vndr_details_rec.terms_id IS NULL
1185 OR orig_vndr_details_rec.payment_method_lookup_code IS NULL
1186 OR orig_vndr_details_rec.pay_group_lookup_code IS NULL
1187 )
1188 THEN
1189 OPEN c_get_vendor_details(r_rcv_transactions.vendor_id);
1190 FETCH c_get_vendor_details
1191 INTO orig_vndr_details_rec;
1192 CLOSE c_get_vendor_details;
1193 END IF; -- IF( orig_vndr_details_rec.terms_id IS NULL)
1194
1195 OPEN c_get_goods_received_date(r_rcv_transactions.vendor_id , r_rcv_transactions.vendor_site_id);
1196 FETCH c_get_goods_received_date
1197 INTO ld_orig_goods_recv_date;
1198 CLOSE c_get_goods_received_date;
1199
1200 IF r_rcv_transactions.currency_code <> lv_func_currency then
1201 lv_orig_currcy_conver_type := r_rcv_transactions.currency_conversion_type;
1202 lv_orig_currcy_conver_rate := r_rcv_transactions.currency_conversion_rate;
1203 lv_orig_currcy_conver_date := r_rcv_transactions.currency_conversion_date;
1204 ELSE
1205 lv_orig_currcy_conver_type := null;
1206 lv_orig_currcy_conver_rate := null;
1207 lv_orig_currcy_conver_date := null;
1208 END IF;
1209
1210
1211 jai_ap_utils_pkg.insert_ap_inv_interface
1212 (
1213 p_jai_source => 'Third Party Invoices', --changed by eric for inclusive tax
1214 p_invoice_id => ln_interface_invoice_id,
1215 p_invoice_num => lv_invoice_num,
1216 p_invoice_type_lookup_code => 'CREDIT', /* CREDIT Memo*/ --changed by eric for inclusive tax
1217 p_invoice_date => SYSDATE,
1218 p_vendor_id => r_rcv_transactions.vendor_id, --changed by eric for inclusive tax
1219 p_vendor_site_id => r_rcv_transactions.vendor_site_id, --changed by eric for inclusive tax
1220 --p_invoice_amount => ROUND(-ln_tax_amount,2), --changed by eric for inclusive tax,deleted by eric for bug#6988610
1221 p_invoice_amount => ROUND(-ln_totl_incl_tax_amount,2), --changed by eric for bug#6988610 on Apr 23,2008
1222 p_invoice_currency_code => r_rcv_transactions.currency_code, --changed by eric for inclusive tax
1223 p_exchange_rate => lv_orig_currcy_conver_type, --changed by eric for inclusive tax
1224 p_exchange_rate_type => lv_orig_currcy_conver_rate, --changed by eric for inclusive tax
1225 p_exchange_date => lv_orig_currcy_conver_date, --changed by eric for inclusive tax
1226 p_terms_id => orig_vndr_details_rec.terms_id, --changed by eric for inclusive tax
1227 p_description => lv_description,
1228 p_source => 'INDIA TAX INVOICE', /* --'RECEIPT', --Ramanand for bug#4388958 */
1229 p_voucher_num => lv_invoice_num,
1230 p_pay_group_lookup_code => orig_vndr_details_rec.pay_group_lookup_code, --changed by eric for inclusive tax
1231 p_goods_received_date => ld_orig_goods_recv_date, --changed by eric for inclusive tax
1232 p_created_by => ln_uid,
1233 p_creation_date => sysdate,
1234 p_last_updated_by => ln_uid,
1235 p_last_update_date => sysdate,
1236 p_last_update_login => null,
1237 p_org_id => ln_org_id -- added by csahoo for bug#6139899
1238 );
1239
1240 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 6. After insert Credit Memo of third party Invoice :' || lv_invoice_num ||' into insert_ap_inv_interface');
1241 --added by eric for inclusive tax on 20-dec,2007, Begin
1242 -----------------------------------------------------------------------
1243 END IF; --(i =1 )
1244 -----------------------------------------------------------------------
1245 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : insertface table insert end:');
1246 Fnd_File.put_line(Fnd_File.LOG, ' ');
1247 --added by eric for inclusive tax on 20-dec,2007, end
1248 end if; /* if p_simulation <> 'Y' then */
1249
1250
1251
1252 --Moved the 'END IF' to the line before the end of the first loop
1253
1254 --Deleted by eric for inclusive tax on Dec 14,2007 Begin
1255 --END IF; /* END IF FOR if lv_vendor_has_changed = 'TRUE' THEN */
1256 --Deleted by eric for inclusive tax on Dec 14,2007 Begin
1257
1258 /*
1259 || The following for loop added by ssumaith - bug# 4284505
1260 */
1261
1262 /* Added by Ramananda for removal of SQL LITERALs */
1263 lv_ttype_receive := 'RECEIVE' ;
1264 lv_ttype_correct := 'CORRECT' ;
1265
1266 /*Bug 4941642. Added by Lakshmi Gopalsami
1267 (1) Added shipment header id condition
1268 (2) added aliases.
1269 (3) Removed two separate conditions on jai_rcv_Transactions
1270 and clubbed into a single one.
1271 */
1272 FOR Tax_rec IN
1273 ( SELECT
1274 jrlt.*
1275 , NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --added by eric for inclusive tax
1276 FROM
1277 JAI_RCV_LINE_TAXES jrlt
1278 , jai_cmn_taxes_all jcta --added by eric for inclusive tax
1279 WHERE jrlt.shipment_header_id = p_shipment_header_id
1280 AND (jrlt.transaction_id, jrlt.shipment_line_id) in
1281 ( SELECT jrt.transaction_id,jrt.shipment_line_id
1282 FROM JAI_RCV_TRANSACTIONS jrt
1283 WHERE jrt.shipment_header_id = p_shipment_header_id
1284 AND ( jrt.transaction_type = lv_ttype_receive
1285 or
1286 (jrt.transaction_type = lv_ttype_correct
1287 and jrt.parent_transaction_type = lv_ttype_receive
1288 )
1289 )
1290 AND jrt.third_party_flag = 'N'
1291 )
1292 AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
1293 AND jrlt.vendor_id > 0
1294 AND nvl(jrlt.tax_amount, 0) IS NOT NULL
1295 AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
1296 AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
1297 AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
1298 AND jrlt.currency = c_thirdparty_tax_rec.currency
1299 AND jrlt.tax_id = jcta.tax_id --added by eric for inclusive tax
1300 )
1301 LOOP
1302
1303 Fnd_File.put_line(Fnd_File.LOG, ' ');
1304 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 3 : Loop Begin');
1305 --SELECT jai_rcv_tp_inv_details_s.nextval INTO ln_batch_line_id FROM DUAL;
1306
1307 --tax table need to be populated once only
1308 --added by eric for inclusive tax on 20-dec,2007, Begin
1309 -----------------------------------------------------------------------
1310 IF (i =1 )
1311 THEN
1312 -----------------------------------------------------------------------
1313 --added by eric for inclusive tax on 20-dec,2007, end
1314 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 3 ,jai_rcv_tp_inv_details table : i =1 Branch :');
1315 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7. Before insert into jai_rcv_tp_inv_details' );
1316
1317 INSERT INTO jai_rcv_tp_inv_details
1318 (
1319 BATCH_LINE_ID ,
1320 BATCH_INVOICE_ID ,
1321 RCV_TRANSACTION_ID ,
1322 LINE_NUMBER ,
1323 TAX_ID ,
1324 TAX_AMOUNT ,
1325 TAX_RATE ,
1326 TAX_TYPE ,
1327 CREATED_BY ,
1328 CREATION_DATE ,
1329 LAST_UPDATE_DATE ,
1330 LAST_UPDATED_BY ,
1331 LAST_UPDATE_LOGIN
1332 )
1333 VALUES
1334 (
1335 --ln_batch_line_id ,
1336 jai_rcv_tp_inv_details_s.nextval,
1337 ln_batch_invoice_id ,
1338 tax_rec.transaction_id,
1339 ln_line_number ,
1340 Tax_rec.tax_id ,
1341 tax_Rec.tax_amount ,
1342 tax_rec.tax_rate ,
1343 tax_rec.tax_type ,
1344 fnd_global.user_id ,
1345 sysdate ,
1346 sysdate ,
1347 fnd_global.user_id ,
1348 fnd_global.login_id
1349 ) returning BATCH_LINE_ID into ln_BATCH_LINE_ID;
1350 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 8. After insert into jai_rcv_tp_inv_details' );
1351 --added by eric for inclusive tax on 20-dec,2007, Begin
1352 -----------------------------------------------------------------------
1353 END IF; --(i =1 )
1354 -----------------------------------------------------------------------
1355 --added by eric for inclusive tax on 20-dec,2007, end
1356
1357 lv_description := 'Tax Record for invoice num ' || lv_invoice_num || ' For Tax Type = '|| tax_rec.tax_type ;
1358
1359 --added by eric for inclusive tax on 20-dec,2007, Begin
1360 -----------------------------------------------------------------------
1361 IF (i =1 )
1362 THEN
1363 ln_tax_line_amount:=round(tax_Rec.tax_amount,2);
1364 ELSE--(i =2 )
1365 ln_tax_line_amount:=round(-tax_Rec.tax_amount,2);
1366 END IF; --(i =1 )
1367 -----------------------------------------------------------------------
1368 --added by eric for inclusive tax on 20-dec,2007, end
1369
1370
1371 --added by eric for bug 6971486 on 20-Apr,2008, Begin
1372 -----------------------------------------------------------------------
1373 IF ( (i =1) OR (i =2 AND tax_rec.inc_tax_flag = 'Y'))
1374 THEN
1375 -----------------------------------------------------------------------
1376 --added by eric for bug 6971486 on 20-Apr,2008, End
1377
1378 --added by eric for inclusive tax on 20-dec,2007, Begin
1379 -----------------------------------------------------------------------
1380 IF (i=1)
1381 THEN
1382 ln_line_number := ln_line_number + 1;
1383 ln_to_insert_line_number :=ln_line_number;
1384 ELSE
1385 ln_cm_line_number := ln_cm_line_number + 1;
1386 ln_to_insert_line_number :=ln_cm_line_number;
1387 END IF;
1388 --Fnd_File.put_line(Fnd_File.LOG, ' ln_to_insert_line_number : '||ln_to_insert_line_number);
1389 -----------------------------------------------------------------------
1390 --added by eric for inclusive tax on 20-dec,2007, end
1391 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : i ='||i ||' , '||'ln_to_insert_line_number = '|| ln_to_insert_line_number);
1392 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 9. Before insert into insert_ap_inv_lines_interface' );
1393
1394 jai_ap_utils_pkg.insert_ap_inv_lines_interface
1395 (
1396 p_jai_source => 'Third Party Invoices',
1397 p_invoice_id => ln_interface_invoice_id,
1398 p_invoice_line_id => ln_interface_line_id,
1399 p_line_number => ln_to_insert_line_number,
1400 --p_line_type_lookup_code => 'MISCELLANEOUS', --deleted by eric FOR BUG bug#6790599
1401 p_line_type_lookup_code => 'ITEM', --added by eric FOR BUG bug#6790599
1402 --Modified by eric for inclusive tax ,begin
1403 p_amount => ln_tax_line_amount,-- round(tax_Rec.tax_amount,2),
1404 --Modified by eric for inclusive tax ,end
1405 p_accounting_date => r_rcv_transactions.transaction_date,
1406 p_description => lv_description,
1407 p_dist_code_combination_id => ln_accrual_account,
1408 p_assets_tracking_flag => lv_assets_tracking_flag,
1409 p_created_by => ln_uid,
1410 p_creation_date => sysdate,
1411 p_last_updated_by => ln_uid,
1412 p_last_update_date => sysdate,
1413 p_last_update_login => null,
1414 p_org_id => ln_org_id -- added by csahoo for bug#139899
1415 );
1416 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 10. After insert into insert_ap_inv_lines_interface' );
1417 --added by eric for bug 6971486 on 20-Apr,2008, Begin
1418 -----------------------------------------------------------------------
1419 END IF;
1420 -----------------------------------------------------------------------
1421 --added by eric for bug 6971486 on 20-Apr,2008, End
1422 END LOOP;
1423 ln_line_number := 0;
1424 --Added by eric for inclusive tax on Dec 14,2007 Begin
1425 ------------------------------------------------------------------------------
1426 ln_cm_line_number :=0;
1427 Fnd_File.put_line(Fnd_File.LOG, ' ');
1428 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 3 : Loop end');
1429 END LOOP;--( i in 1 .. ln_lines_to_insert)
1430 END IF; /* END IF FOR if lv_vendor_has_changed = 'TRUE' THEN */
1431 ------------------------------------------------------------------------------
1432 --Added by eric for inclusive tax on Dec 14,2007 End
1433 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 2 : Loop end');
1434 Fnd_File.put_line(Fnd_File.LOG, ' ');
1435 end loop; --(For c_thirdparty_tax_rec IN)
1436
1437
1438 << exit_from_procedure >>
1439 if p_process_flag is null then
1440 p_process_flag := 'Y';
1441 end if;
1442
1443 if p_debug >= 1 then
1444 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 1 : ' ||
1445 'End of procedure jai_rcv_third_party_pkg.process_receipt for shipment header :' ||
1446 to_char(p_shipment_header_id)
1447 );
1448 end if;
1449
1450 return;
1451
1452 exception
1453 when others then
1454 p_process_flag := 'E';
1455 p_process_message := 'jai_rcv_third_party_pkg.process_receipt:' || sqlerrm;
1456 fnd_file.put_line( fnd_file.log, '******** Error in '||p_process_message);
1457 raise;
1458
1459 end process_receipt;
1460 /****************************** End process_receipt ****************************/
1461
1462 /****************************** Start populate_tp_invoice_id ****************************/
1463 procedure populate_tp_invoice_id
1464 (
1465 p_invoice_id IN ap_invoices_all.invoice_id%TYPE,
1466 p_invoice_num IN ap_invoices_all.invoice_num%TYPE,
1467 p_vendor_id IN ap_invoices_all.vendor_id%TYPE,
1468 p_vendor_site_id IN ap_invoices_all.vendor_site_id%TYPE,
1469 p_process_flag OUT NOCOPY VARCHAR2,
1470 p_process_message OUT NOCOPY VARCHAR2
1471 )
1472 IS
1473
1474 /*
1475 || This procedure is added by ssumaith - bug# 4284505 for updating the invoice_id in the jai_Rcv_tp_invoices table
1476 || This procedure will be called from the trigger - ja_in_ap_aia_before_trg on ap_invoices_all
1477 */
1478 BEGIN
1479
1480 UPDATE jai_rcv_tp_invoices
1481 SET invoice_id = p_invoice_id ,
1482 last_update_date = sysdate ,
1483 last_updated_by = fnd_global.user_id
1484 WHERE invoice_num = p_invoice_num
1485 AND vendor_id = p_vendor_id
1486 AND vendor_site_id = p_vendor_site_id;
1487
1488 p_process_flag := jai_constants.successful;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 p_process_flag := jai_constants.unexpected_error;
1493 p_process_message := substr(sqlerrm,1,1000);
1494
1495 END;
1496
1497 /****************************** End populate_tp_invoice_id ****************************/
1498
1499
1500 end jai_rcv_third_party_pkg;
[Home] [Help]