1 PACKAGE BODY jai_rcv_third_party_pkg AS
2 /* $Header: jai_rcv_3p_prc.plb 120.19.12020000.3 2013/01/30 09:37:03 anupgupt 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 06-AUG-2009 Bug: 8238608 File Version 120.13.12010000.3
121 Issue: Service Accounting does not happen, when Accrue on reciept = N for third party Invoices.
122 Fix: The scenario was not handled earlier. Required code changes are done.
123
124 07-Aug-2009 bug: 8567640 File Version 120.13.12010000.4
125 Issue : Performance issue with 3rd party invoices concurrent
126 Fix: Modified the below cursor queries
127 + c_pending_tp_receipts
128
129 08-Oct-2009 CSahoo for bug#8965721, File Version 120.13.12010000.6
130 Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
131 Fix: Did the Fp of the transaporter scenario correctly again.
132 modified the code in the procedure process_receipt
133
134 07-Jan-2012 anupgupt for bug#13528285
135 Issue: INDIA LOCALISED TAXES NOT CORRECTED IN THE AP INVOICE POST RECEIPT CORRECTIONS
136 Fix: Modified the code to apportion tax amount at line level based on receipt corrected.
137 It's also a debug patch.
138
139 12-Jan-2012 anupgupt for bug#13528285
140 Issue: INDIA LOCALISED TAXES NOT CORRECTED IN THE AP INVOICE POST RECEIPT CORRECTIONS
141 Fix: Modified the code to apportion tax amount at line level based on receipt corrected.
142 It's also a debug patch.
143
144 25-Jan-2012 anupgupt for bug#13528285
145 Issue: INDIA LOCALISED TAXES NOT CORRECTED IN THE AP INVOICE POST RECEIPT CORRECTIONS
146 Fix: Modified the code to calculate third party invoice header amount and line amount based on corrections done on receipt.
147
148 11-Jun-2012 anupgupt for bug#14172169
149 Issue: THIRD PARTY INVOICE IS NOT GENERATING FOR SOME PO RECEIPT LINES
150 Fix: Foward ported changes done in 11i through bug 9902270
151
152 Future Dependencies For the release Of this Object:-
153 ==================================================
154 (Please add a row in the section below only if your bug introduces a dependency due to spec change/
155 A new call to a object/A datamodel change )
156
157 ------------------------------------------------------------------------------------------------------
158 Version Bug Dependencies (including other objects like files if any)
159 -------------------------------------------------------------------------------------------------------
160 115.0 4146708 The new tables have been created through the script attached to bug
161 for service and cess datamodel change.
162
163 ----------------------------------------------------------------------------------------- */
164
165 /****************************** Start process_pending_receipts ****************************/
166
167 procedure process_batch
168 (
169 errbuf out nocopy VARCHAR2,
170 retcode out nocopy VARCHAR2,
171 p_batch_name in VARCHAR2,
172 /* Bug 5096787. Added by LGOPALSA Added parameter p_org_id */
173 p_org_id in NUMBER /* This parameter would no more be used after application of the bug 5490479- Aiyer, */,
174 p_simulation in VARCHAR2 default null,
175 p_debug in NUMBER default 1
176 )
177 is
178
179 /* Added by Ramananda for removal of SQL LITERALs */
180 lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
181 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
182
183 cursor c_pending_tp_receipts(cp_org_id number) is /* modified the cursor query for bug 8567640 */
184 SELECT
185 /*+ no_expand */ jrt.shipment_header_id
186 FROM jai_rcv_transactions jrt,
187 jai_rcv_lines jrl
188 WHERE(jrt.transaction_type = 'RECEIVE' OR(jrt.transaction_type = 'CORRECT'
189 AND jrt.parent_transaction_type = 'RECEIVE'))
190 AND jrt.third_party_flag = 'N'
191 AND jrt.shipment_header_id = jrl.shipment_header_id
192 AND jrt.shipment_line_id = jrl.shipment_line_id
193 AND jrl.tax_modified_flag <> 'Y'
194 AND jrt.organization_id = cp_org_id
195 GROUP BY jrt.shipment_header_id
196 ORDER BY jrt.shipment_header_id;
197
198 cursor c_get_tp_batch_id is
199 select jai_rcv_tp_batches_s.nextval from dual;
200
201 cursor c_no_of_invoice_generated(cp_batch_id number) is
202 select count(batch_invoice_id)
203 from jai_rcv_tp_invoices
204 where batch_id = cp_batch_id;
205
206
207 r_pending_tp_receipts c_pending_tp_receipts%rowtype;
208
209 lv_process_flag VARCHAR2(1);
210 lv_process_message VARCHAR2(256);
211 ln_batch_id NUMBER;
212 ln_no_of_invoice_generated NUMBER;
213
214 ln_req_id NUMBER;
215 ln_uid NUMBER; --File.Sql.35 Cbabu := fnd_global.user_id;
216 lv_temp VARCHAR2(100);
217 ln_org_id NUMBER; /*Added by aiyer for the bug 5490479 */
218
219 begin
220
221 ln_uid := fnd_global.user_id;
222 /*
223 || Start of bug 5490479
224 || Added by aiyer for the bug 5490479
225 || Get the operating unit (org_id)
226 */
227 ln_org_id := mo_global.get_current_org_id;
228 fnd_file.put_line(fnd_file.log, 'Operating unit ln_org_id is -> '||ln_org_id);
229
230 /*End of bug 5490479 */
231 /* This is to identify the path in SQL TRACE file if any problem occured */
232 SELECT 'jai_rcv_third_party_pkg.process_pending_receipts' INTO lv_temp FROM DUAL;
233
234 open c_get_tp_batch_id;
235 fetch c_get_tp_batch_id into ln_batch_id;
236 close c_get_tp_batch_id;
237
238 if p_debug >= 1 then
239 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 1 : Start of procedure jai_rcv_third_party_pkg.process_pending_receipts ****');
240 end if;
241
242 /* Get all receipts where third party needs to be processed. Here only
243 RECEIVE or CORRECT to RECEIVE type of transactions are considered */
244
245 /* Added by Ramananda for removal of SQL LITERALs */
246 lv_ttype_receive := 'RECEIVE' ;
247 lv_ttype_correct := 'CORRECT' ;
248
249 /* Bug 4695630 Added by vumaasha
250 Depending on the value of the ln_org_id(operating unit)
251 we have to process the records
252 Added the following cursor and added cursor parameter for r_pending_tp_receipts
253 */
254
255 /* start of bug 4695630 */
256
257 for c_sel_org in (SELECT organization_id
258 FROM org_organization_definitions
259 WHERE operating_unit = ln_org_id
260 )
261
262 loop
263
264 Fnd_File.put_line(Fnd_File.LOG,
265 'Debug Msg 1 : Inside org definition and processing org '||
266 c_sel_org.organization_id);
267
268
269 for r_pending_tp_receipts in c_pending_tp_receipts(c_sel_org.organization_id) loop
270
271 lv_process_flag := null;
272 lv_process_message := null;
273
274 process_receipt
275 (
276 p_batch_id => ln_batch_id,
277 p_shipment_header_id => r_pending_tp_receipts.shipment_header_id,
278 p_process_flag => lv_process_flag,
279 p_process_message => lv_process_message,
280 p_debug => p_debug,
281 p_simulation => nvl(p_simulation, 'N')
282 );
283
284
285 insert into jai_rcv_tp_batches
286 (
287 batch_id ,
288 shipment_header_id ,
289 process_flag ,
290 process_message ,
291 dummy_flag ,
292 created_by ,
293 creation_date ,
294 last_update_login ,
295 last_update_date ,
296 last_updated_by ,
297 program_application_id,
298 program_id,
299 program_login_id,
300 request_id
301 )
302 values
303 (
304 ln_batch_id ,
305 r_pending_tp_receipts.shipment_header_id,
306 lv_process_flag ,
307 lv_process_message ,
308 nvl(p_simulation, 'N') ,
309 ln_uid ,
310 sysdate ,
311 ln_uid ,
312 sysdate ,
313 null ,
314 fnd_profile.value('PROG_APPL_ID'),
315 fnd_profile.value('CONC_PROGRAM_ID'),
316 fnd_profile.value('CONC_LOGIN_ID'),
317 fnd_profile.value('CONC_REQUEST_ID')
318 );
319
320
321 if nvl(p_simulation, 'N') <> 'Y' then
322 update JAI_RCV_TRANSACTIONS jrt
323 set third_party_flag = lv_process_flag
324 where shipment_header_id = r_pending_tp_receipts.shipment_header_id
325 and ( transaction_type = 'RECEIVE'
326 or
327 (transaction_type = 'CORRECT' and parent_transaction_type = 'RECEIVE')
328 )
329 and third_party_flag = 'N'
330 and exists
331 (
332 select '1'
333 from JAI_RCV_LINES jrl
334 where jrt.shipment_header_id = jrl.shipment_header_id
335 and jrt.shipment_line_id = jrl.shipment_line_id
336 and jrl.tax_modified_flag <> 'Y'
337 );
338 end if;
339
340
341 end loop; /* c_pending_tp_receipts */
342 END loop; /* c_sel_org */
343 /* end of bug 4695630 */
344
345
346 open c_no_of_invoice_generated(ln_batch_id);
347 fetch c_no_of_invoice_generated into ln_no_of_invoice_generated;
348 close c_no_of_invoice_generated;
349
350 if ln_no_of_invoice_generated > 0 and nvl(p_simulation, 'N') <> 'Y' then
351
352 /* Processing has created some invoices, invoking the payable open interface for their import */
353 if p_debug >= 1 then
354 Fnd_File.put_line(Fnd_File.LOG,
355 'Debug Level 1 : Invoking APXIIMPT as no of invoices created is :'
356 || to_char(ln_no_of_invoice_generated) );
357 end if;
358
359 ln_uid := fnd_global.user_id;
360 ln_req_id :=
361 Fnd_Request.submit_request
362 (
363 'SQLAP',
364 'APXIIMPT',
365 'Third party Invoices - Payables open interface Import',
366 '',
367 false,
368 /*Bug 4774647. Added by Lakshmi Gopalsami
369 Passed operating unit also as this parameter has been
370 added by base .*/
371
372 '',
373 'INDIA TAX INVOICE', /*--'RECEIPT', --Ramanand for bug#4388958 */
374 '',
375 p_batch_name,
376 '',
377 '',
378 '',
379 'Y',
380 'N',
381 'Y', /* modified for bug 4695630 */
382 'N',
383 1000,
384 ln_uid,
385 NULL
386 );
387
388 end if; /*ln_total_no_of_invoices > 0 then*/
389
390 << exit_from_procedure >>
391
392 if p_debug >= 1 then
393 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 1 : End of procedure jai_rcv_third_party_pkg.process_pending_receipts ****');
394 end if;
395
396 return;
397
398 exception
399 when others then
400 retcode := 2;
401 errbuf := 'jai_rcv_third_party_pkg.process_pending_receipts:' || sqlerrm;
402 FND_FILE.put_line(FND_FILE.log, 'Error in jai_rcv_third_party_pkg.process_pending_receipts :'||sqlerrm);
403 return;
404 end process_batch;
405 /****************************** End process_pending_receipts ****************************/
406
407
408 /****************************** Start process_receipt ****************************/
409
410 procedure process_receipt
411 (
412 p_batch_id in number,
413 p_shipment_header_id in number,
414 p_process_flag OUT NOCOPY varchar2,
415 p_process_message OUT NOCOPY varchar2,
416 p_debug in number default 1,
417 p_simulation in varchar2
418 )
419 is
420
421 cursor c_rcv_shipment_headers(p_shipment_header_id number) is
422 select receipt_num
423 from rcv_shipment_headers
424 where shipment_header_id = p_shipment_header_id;
425
426 cursor c_rcv_transactions
427 (p_shipment_header_id number)is
428 select
429 vendor_id,
430 vendor_site_id, --added by eric for inclusive tax on 20-dec-2007
431 organization_id,
432 transaction_date,
433 po_header_id,
434 po_line_location_id,
435 po_distribution_id,
436 currency_code,
437 currency_conversion_type,
438 currency_conversion_date,
439 currency_conversion_rate
440 from rcv_transactions
441 where shipment_header_id = p_shipment_header_id
442 and transaction_type = 'RECEIVE';
443
444 /* Added by Ramananda for removal of SQL LITERALs */
445 lv_ttype_receive JAI_RCV_TRANSACTIONS.transaction_type%type;
446 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type;
447
448 /* Bug 4941642. Added by Lakshmi Gopalsami
449 Added aliases for the following cursors and
450 Added alias and shipment_header_id and
451 shipment_line_id condition in inner query
452 (1) c_get_thirdparty_count
453 (2) c_get_thirdparty_null_site_cnt
454 (3) c_get_tparty_invalid_comb_cnt
455
456 */
457 cursor c_get_thirdparty_count /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
458 (
459 p_shipment_header_id number,
460 p_po_vendor_id number
461 )
462 is
463 select count(jrlt.tax_line_no)
464 from JAI_RCV_LINE_TAXES jrlt
465 where jrlt.shipment_header_id = p_shipment_header_id
466 and EXISTS
467 (
468 select 1
469 from JAI_RCV_TRANSACTIONS jrt
470 where jrt.shipment_header_id = jrlt.shipment_header_id
471 AND jrt.shipment_line_id = jrlt.shipment_line_id
472 AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
473 or
474 (jrt.transaction_type = lv_ttype_correct
475 and jrt.parent_transaction_type = lv_ttype_receive
476 )
477 )
478 and jrt.third_party_flag = 'N'
479 )
480 and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
481 and jrlt.vendor_id > 0
482 and jrlt.tax_amount <> 0
483 and jrlt.tax_amount <> 0
484 and jrlt.vendor_id <> p_po_vendor_id;
485
486 /* Bug 4941642. Added by Lakshmi Gopalsami
487 Added alias and shipment_header_id and
488 shipment_line_id condition in inner query
489 */
490 cursor c_get_thirdparty_null_site_cnt /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
491 (
492 p_shipment_header_id number,
493 p_po_vendor_id number
494 )
495 is
496 select count(jrlt.tax_line_no)
497 from JAI_RCV_LINE_TAXES jrlt
498 where jrlt.shipment_header_id = p_shipment_header_id
499 and EXISTS
500 (
501 select 1
502 from JAI_RCV_TRANSACTIONS jrt
503 where jrt.shipment_header_id = jrlt.shipment_header_id
504 AND jrt.shipment_line_id = jrlt.shipment_line_id
505 AND ( jrt.transaction_type = lv_ttype_receive --'RECEIVE'
506 or
507 (jrt.transaction_type = lv_ttype_correct
508 and jrt.parent_transaction_type = lv_ttype_receive
509 )
510 )
511 and jrt.third_party_flag = 'N'
512 )
513 and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
514 and jrlt.vendor_id > 0
515 and jrlt.tax_amount <> 0
516 and jrlt.vendor_id <> p_po_vendor_id
517 and jrlt.vendor_site_id is null;
518
519 /* Bug 4941642. Added by Lakshmi Gopalsami
520 Added alias and shipment_header_id and
521 shipment_line_id condition in inner query
522 */
523
524 cursor c_get_tparty_invalid_comb_cnt /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
525 (
526 p_shipment_header_id number,
527 p_po_vendor_id number
528 )
529 is
530 select count(jrlt.tax_line_no)
531 from JAI_RCV_LINE_TAXES jrlt
532 where jrlt.shipment_header_id = p_shipment_header_id
533 and EXISTS
534 (
535 select 1
536 from JAI_RCV_TRANSACTIONS jrt
537 where jrt.shipment_header_id = jrlt.shipment_header_id
538 AND jrt.shipment_line_id = jrlt.shipment_line_id
539 AND ( jrt.transaction_type = lv_ttype_receive
540 or
541 (jrt.transaction_type = lv_ttype_correct
542 and jrt.parent_transaction_type = lv_ttype_receive
543 )
544 )
545 and jrt.third_party_flag = 'N'
546 )
547 and jrlt.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
548 and jrlt.vendor_id > 0
549 and jrlt.tax_amount <> 0
550 and jrlt.vendor_id <> p_po_vendor_id
551 and jrlt.vendor_site_id is not null
552 and not exists
553 (select '1'
554 from po_vendor_sites_all pvs
555 where pvs.vendor_id = jrlt.vendor_id
556 and pvs.vendor_site_id = jrlt.vendor_site_id
557 );
558
559 cursor c_get_assets_tracking_flag(p_shipment_header_id number) is
560 select decode(count(inventory_item_id), 0, 'N', 'Y')
561 from JAI_INV_ITM_SETUPS
562 where item_class = 'CGIN'
563 and (inventory_item_id, organization_id)
564 in
565 (
566 select item_id, ship_to_location_id
567 from rcv_shipment_lines
568 where shipment_header_id = p_shipment_header_id
569 );
570
571 cursor c_get_po_dist_account (p_po_distribution_id number) is
572 select accrual_account_id
573 from po_distributions_all
574 where po_distribution_id = p_po_distribution_id;
575
576 cursor c_get_latest_po_dist_account (p_line_location_id number) is
577 select accrual_account_id
578 from po_distributions_all
579 where line_location_id = p_line_location_id
580 and creation_date in
581 (
582 select max(creation_date)
583 from po_distributions_all
584 where line_location_id = p_line_location_id
585 );
586
587
588 cursor c_get_vendor_details (p_vendor_id number) is
589 select
590 vendor_name,
591 terms_id,
592 NULL payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
593 /* added the null in the above line by csahoo 5620503 */
594 pay_group_lookup_code,
595 NULL org_id -- added by csahoo for bug#6139899
596 from po_vendors
597 where vendor_id = p_vendor_id;
598
599 /* cbabu for Bug#5613772 */
600 cursor c_get_vendor_site_dtls (p_vendor_site_id number) is
601 SELECT
602 b.vendor_name,
603 a.terms_id,
604 a.payment_method_lookup_code,
605 a.pay_group_lookup_code,
606 a.org_id -- added by csahoo for bug#6139899
607 from po_vendor_sites_all a, po_vendors b
608 where a.vendor_id = b.vendor_id
609 AND a.vendor_site_id = p_vendor_site_id;
610
611
612 cursor c_get_goods_received_date(p_vendor_id number, p_vendor_site_id number) is
613 select
614 decode(terms_date_basis, 'Goods Received', sysdate, null)
615 from po_vendor_sites_all
616 where vendor_id = p_vendor_id
617 and vendor_site_id = p_vendor_site_id;
618
619 cursor c_get_inv_run_no is
620 select jai_rcv_tp_invoices_s1.nextval /* renamed the sequence to point to the correct sequence name - ssumaith - sequence change process */
621 from dual;
622
623 cursor c_check_if_already_processed(p_shipment_header_id number) is
624 select count(transaction_id)
625 from JAI_RCV_TRANSACTIONS
626 where shipment_header_id = p_shipment_header_id
627 and third_party_flag in ('N', 'X'); -- for bug 14172169 by anupgupt
628
629 cursor c_jai_regimes (cpv_regime_code jai_rgm_definitions.regime_code%type) is /* added by vumaasha for bug 8238608 */
630 select regime_id
631 from jai_rgm_definitions
632 where regime_code = cpv_regime_code;
633
634 cursor c_trx_dtls(cp_transaction_id rcv_transactions.transaction_id%TYPE) is /* added by vumaasha for bug 8238608 */
635 SELECT rt.po_distribution_id,
636 rt.po_line_location_id ,
637 rt.po_line_id ,
638 rt.organization_id,
639 pll.ship_to_organization_id,
640 pll.ship_to_location_id
641 FROM
642 rcv_transactions rt,
643 po_line_locations_all pll
644 where rt.po_line_location_id=pll.line_location_id AND
645 rt.transaction_id=cp_transaction_id;
646
647
648 lv_temp varchar2(100);
649 ln_thirdparty_count number;
650 ln_thirdparty_null_site_cnt number;
651 ln_tparty_invalid_comb_cnt number;
652 lv_assets_tracking_flag varchar2(1);
653 ln_accrual_account number;
654 ld_goods_received_date date;
655 lv_receipt_num rcv_shipment_headers.receipt_num%type;
656
657 r_rcv_transactions c_rcv_transactions%rowtype;
658 r_get_vendor_details c_get_vendor_details%rowtype;
659
660 lv_description ap_invoices_interface.description%type;
661 ln_tax_amount number;
662 lv_currency_conversion_type rcv_transactions.currency_conversion_type%type;
663 lv_currency_conversion_rate rcv_transactions.currency_conversion_rate%type;
664 lv_currency_conversion_date date;
665 ln_uid number; --File.Sql.35 Cbabu := fnd_global.user_id;
666 ln_inv_run_no number;
667 lv_invoice_num ap_invoices_all.invoice_num%type;
668 lv_func_currency gl_sets_of_books.currency_code%type;
669 ln_gl_set_of_books_id gl_sets_of_books.set_of_books_id%type;
670 ln_interface_invoice_id number;
671 ln_interface_line_id number;
672 ln_check_if_already_processed number;
673
674
675 ln_vendor_id po_vendors.vendor_id%type;
676 ln_vendor_site po_vendor_sites_all.vendor_site_id%type;
677 lv_currency fnd_currencies.currency_code%type;
678 lv_vendor_has_changed VARCHAR2(10);
679 lb_tp_taxes_processed VARCHAR2(10);
680 ln_batch_invoice_id NUMBER;
681 ln_batch_line_id NUMBER;
682 ln_line_number NUMBER;
683 ln_cm_line_number NUMBER; --added by eric for inclusive tax
684 ln_to_insert_line_number NUMBER; --added by eric for inclusive tax
685 ln_org_id po_vendor_sites_all.org_id%type; -- added by csahoo for bug#6139899
686 ln_lines_to_insert NUMBER default 1; --added by eric for inclusive tax on 20-dec-2007
687 ln_tax_line_amount NUMBER; --added by eric for inclusive tax on 20-dec-2007
688 orig_vndr_details_rec c_get_vendor_details%rowtype; --added by eric for inclusive tax on 20-dec-2007
689 ld_orig_goods_recv_date DATE; --added by eric for inclusive tax on 20-dec-2007
690 lv_orig_currcy_conver_type rcv_transactions.currency_conversion_type%type; --added by eric for inclusive tax on 20-dec-2007
691 lv_orig_currcy_conver_rate rcv_transactions.currency_conversion_rate%type; --added by eric for inclusive tax on 20-dec-2007
692 lv_orig_currcy_conver_date date; --added by eric for inclusive tax on 20-dec-2007
693 r_trx_dtls c_trx_dtls%ROWTYPE; /* added by vumaasha for bug 8238608 */
694 ln_regime_id jai_rgm_definitions.regime_id%TYPE; /* added by vumaasha for bug 8238608 */
695 ln_accrue_on_receipt_flag po_distributions_all.accrue_on_receipt_flag%TYPE; /* added by vumaasha for bug 8238608 */
696
697 --added the below cursor for bug#6988610 by eric on Apr 24,2008 ,begin
698 -----------------------------------------------------------------------
699 ln_totl_incl_tax_amount number;
700
701 CURSOR get_totl_incl_tax_amount
702 ( pn_shipment_header_id IN NUMBER
703 , pn_vendor_id IN NUMBER
704 , pn_vendor_site_id IN NUMBER
705 , pv_currency IN VARCHAR2
706 )
707 IS
708 select
709 sum(nvl(jrtv.tax_amount,0)) totl_incl_tax_amount
710 from
711 JAI_RCV_TAX_V jrtv
712 , jai_cmn_taxes_all jcta --added by eric for inclusive tax
713 where
714 ( jrtv.transaction_id, jrtv.shipment_line_id ) IN
715 ( select transaction_id, shipment_line_id
716 from JAI_RCV_TRANSACTIONS jrt
717 where shipment_header_id = pn_shipment_header_id
718 and ( transaction_type = lv_ttype_receive --'RECEIVE'
719 or
720 (transaction_type = lv_ttype_correct
721 and parent_transaction_type = lv_ttype_receive)
722 )
723 and third_party_flag = 'N'
724
725 )
726 and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
727 and jrtv.vendor_id > 0
728 and nvl(jrtv.tax_amount, 0) is not null
729 and jrtv.shipment_header_id = pn_shipment_header_id
730 and jrtv.tax_id = jcta.tax_id
731 and jcta.inclusive_tax_flag = 'Y'
732 and jrtv.vendor_id = pn_vendor_id
733 and jrtv.vendor_site_id = pn_vendor_site_id
734 and jrtv.currency = pv_currency
735 having sum(nvl(jrtv.tax_amount,0)) > 0 ; /* added to take care of complete CORRECTION */
736 ------------------------------------------------------------------------------------------
737 --added by eric for bug#6988610 on Apr 24,2008 ,end
738
739
740 begin
741
742 ln_uid := fnd_global.user_id;
743 ln_vendor_id := -999;
744 ln_vendor_site := -999;
745 lv_currency := '$$$';
746 lv_vendor_has_changed := 'TRUE';
747 lb_tp_taxes_processed := 'FALSE';
748 ln_line_number := 1; /* modified by vumaasha for bug 8965721 */
749
750 -- This is to identify the path in SQL TRACE file if any problem occured
751 select 'jai_rcv_third_party_pkg.process_receipt : shipment header - ' || to_char(p_shipment_header_id)
752 into lv_temp from dual;
753
754 if p_debug >= 1 then
755 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 1 : ' ||
756 'Start of procedure jai_rcv_third_party_pkg.process_receipt for shipment header :' ||
757 to_char(p_shipment_header_id)
758 );
759 end if;
760
761 /* Validation#0 : Check if third party has already been processed for the receipt */
762 ln_check_if_already_processed := 0;
763 open c_check_if_already_processed(p_shipment_header_id);
764 fetch c_check_if_already_processed into ln_check_if_already_processed;
765 close c_check_if_already_processed;
766
767 if ln_check_if_already_processed = 0 then -- for bug 14172169 by anupgupt
768 p_process_flag := 'G';
769 p_process_message := 'Third party invoices have already got generated for this receipt, cannot process again';
770 goto exit_from_procedure;
771 end if;
772
773
774 /* Validation#1 : Check if PO details exist */
775 open c_rcv_transactions(p_shipment_header_id);
776 fetch c_rcv_transactions into r_rcv_transactions;
777 close c_rcv_transactions;
778
779 if r_rcv_transactions.vendor_id is null then
780
781 if p_debug >= 1 then
782 Fnd_File.put_line(Fnd_File.LOG,
783 ' Debug Level 1 : Details from rcv_transactions are not found for this shipment header, cannot process' );
784 end if;
785
786 p_process_flag := 'E';
787 p_process_message := 'Details from rcv_transactions are not found for this shipment header, cannot process';
788 goto exit_from_procedure;
789
790 end if;
791
792 /* Added by Ramananda for removal of SQL LITERALs */
793 lv_ttype_receive := 'RECEIVE' ;
794 lv_ttype_correct := 'CORRECT' ;
795
796 /* Validation#2 : Check if third party taxes exist */
797 open c_get_thirdparty_count
798 (p_shipment_header_id, r_rcv_transactions.vendor_id);
799 fetch c_get_thirdparty_count into ln_thirdparty_count;
800 close c_get_thirdparty_count;
801
802 if nvl(ln_thirdparty_count, 0) = 0 then
803 /* Not an error condition, but no need to process */
804
805 if p_debug >= 1 then
806 Fnd_File.put_line(Fnd_File.LOG,
807 ' Debug Level 1 : There does not exist any third party tax for this shipment, no need to process' );
808 end if;
809
810 goto exit_from_procedure;
811 end if;
812
813
814 /* Validation#3 : Check if any third party taxes exist with null site*/
815 /* Added by Ramananda for removal of SQL LITERALs */
816 lv_ttype_receive := 'RECEIVE' ;
817 lv_ttype_correct := 'CORRECT' ;
818
819 open c_get_thirdparty_null_site_cnt
820 (p_shipment_header_id, r_rcv_transactions.vendor_id);
821 fetch c_get_thirdparty_null_site_cnt into ln_thirdparty_null_site_cnt;
822 close c_get_thirdparty_null_site_cnt;
823
824 if nvl(ln_thirdparty_null_site_cnt, 0) > 0 then
825
826 if p_debug >= 1 then
827 Fnd_File.put_line(Fnd_File.LOG,
828 ' Debug Level 1 : Error : Third party tax for this shipment exists without site, cannot process ' );
829 end if;
830
831 p_process_flag := 'E';
832 p_process_message := 'Error : Third party tax for this shipment exists without site, cannot process ';
833 goto exit_from_procedure;
834
835 end if;
836
837
838 /* Validation#4 : Check if any third party taxes exist with invalid vendor and site combinations */
839 /* Added by Ramananda for removal of SQL LITERALs */
840 lv_ttype_receive := 'RECEIVE' ;
841 lv_ttype_correct := 'CORRECT' ;
842 open c_get_tparty_invalid_comb_cnt
843 (p_shipment_header_id, r_rcv_transactions.vendor_id);
844 fetch c_get_tparty_invalid_comb_cnt into ln_tparty_invalid_comb_cnt;
845 close c_get_tparty_invalid_comb_cnt;
846
847 if nvl(ln_tparty_invalid_comb_cnt, 0) > 0 then
848
849 if p_debug >= 1 then
850 Fnd_File.put_line(Fnd_File.LOG,
851 ' Debug Level 1 : Error : ' ||
852 'Third party tax for this shipment exists with invalid vendor and site combination, cannot process ' );
853 end if;
854
855 p_process_flag := 'E';
856 p_process_message :=
857 'Error : Third party tax for this shipment exists with invalid vendor and site combination, cannot process ';
858 goto exit_from_procedure;
859
860 end if;
861
862
863 /* All validations are over, control comes here only when the record to be processed is valid */
864
865 /* Get the details required for generating AP invoices */
866 open c_get_assets_tracking_flag(p_shipment_header_id);
867 fetch c_get_assets_tracking_flag into lv_assets_tracking_flag;
868 close c_get_assets_tracking_flag;
869
870 open c_rcv_shipment_headers(p_shipment_header_id);
871 fetch c_rcv_shipment_headers into lv_receipt_num;
872 close c_rcv_shipment_headers;
873
874 if r_rcv_transactions.po_distribution_id is not null then
875
876 open c_get_po_dist_account(r_rcv_transactions.po_distribution_id);
877 fetch c_get_po_dist_account into ln_accrual_account;
878 close c_get_po_dist_account;
879
880 elsif r_rcv_transactions.po_line_location_id is not null then
881
882 open c_get_latest_po_dist_account(r_rcv_transactions.po_line_location_id);
883 fetch c_get_latest_po_dist_account into ln_accrual_account;
884 close c_get_latest_po_dist_account;
885
886 end if;
887
888 if ln_accrual_account is null then
889 p_process_flag := 'E';
890 p_process_message := 'Error : Accrual account not defined, cannot process ';
891 goto exit_from_procedure;
892 end if;
893
894 -- get the functional currency
895 jai_rcv_utils_pkg.get_func_curr
896 (
897 r_rcv_transactions.organization_id,
898 lv_func_currency,
899 ln_gl_set_of_books_id
900 );
901
902 /* Added by Ramananda for removal of SQL LITERALs */
903 lv_ttype_receive := 'RECEIVE' ;
904 lv_ttype_correct := 'CORRECT' ;
905
906 For c_thirdparty_tax_rec IN
907 (
908 /*Bug 4941642. Added by Lakshmi Gopalsami
909 (1) Added shipment header id condition
910 (2) added aliases.
911 (3) Removed two separate conditions on jai_rcv_Transactions
912 and clubbed into a single one.
913 */
914 select
915 jrtv.vendor_id
916 , jrtv.vendor_site_id
917 , jrtv.currency
918 , sum(nvl(jrtv.tax_amount,0)) tax_amount
919 --, nvl(jcta.inclusive_tax_flag,'N') inc_tax_flag --added by eric for inclusive tax
920 , MAX(NVL(jcta.inclusive_tax_flag,'N')) inc_tax_flag --modified by eric for bug#6997730 on Apr-24,2008
921 from
922 JAI_RCV_TAX_V jrtv
923 , jai_cmn_taxes_all jcta --added by eric for inclusive tax
924 where
925 ( jrtv.transaction_id, jrtv.shipment_line_id ) IN
926 ( select transaction_id, shipment_line_id
927 from JAI_RCV_TRANSACTIONS jrt
928 where shipment_header_id = p_shipment_header_id
929 and ( transaction_type = lv_ttype_receive --'RECEIVE'
930 or
931 (transaction_type = lv_ttype_correct
932 and parent_transaction_type = lv_ttype_receive)
933 )
934 and third_party_flag = 'N'
935
936 )
937 and jrtv.tax_type not in (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
938 and jrtv.vendor_id > 0
939 and nvl(jrtv.tax_amount, 0) is not null
940 and jrtv.vendor_id <> r_rcv_transactions.vendor_id /* bug#3957167 */
941 and jrtv.shipment_header_id = p_shipment_header_id
942 and jrtv.tax_id = jcta.tax_id --added by eric for inclusive tax
943 GROUP BY
944 jrtv.vendor_id
945 , jrtv.vendor_site_id
946 , jrtv.currency
947 -- , NVL(jcta.inclusive_tax_flag,'N') --deleted by eric for bug#6997730 on Apr-24,2008
948 having sum(nvl(jrtv.tax_amount,0)) > 0 /* added to take care of complete CORRECTION */
949 )
950 loop
951 Fnd_File.put_line(Fnd_File.LOG, ' ');
952 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 2 : Loop Begin');
953 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 :'
954 || 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 );
955 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 );
956
957 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
958 THEN
959 lv_vendor_has_changed := 'TRUE';
960
961 ln_vendor_id := c_thirdparty_tax_rec.vendor_id;
962 ln_vendor_site := c_thirdparty_tax_rec.vendor_site_id;
963 lv_currency := c_thirdparty_tax_rec.currency;
964 ELSE
965 lv_vendor_has_changed := 'FALSE';
966 END IF;
967
968 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : lv_vendor_has_changed :'|| lv_vendor_has_changed );
969
970 if lv_vendor_has_changed = 'TRUE' THEN
971
972 lv_description := null;
973 ln_tax_amount := null;
974 ld_goods_received_date := null;
975
976 -- get the third party vendor details
977 r_get_vendor_details := null;
978 /* following cursor open added by cbabu for bug#5613772 */
979 OPEN c_get_vendor_site_dtls(c_thirdparty_tax_rec.vendor_site_id);
980 FETCH c_get_vendor_site_dtls INTO r_get_vendor_details;
981 CLOSE c_get_vendor_site_dtls;
982
983 ln_org_id := r_get_vendor_details.org_id; -- added by csahoo for bug#6139899
984 /* following if added by cbabu for bug#5613772 */
985 IF r_get_vendor_details.terms_id IS NULL
986 OR r_get_vendor_details.payment_method_lookup_code IS null
987 OR r_get_vendor_details.pay_group_lookup_code IS NULL
988 then
989 open c_get_vendor_details(c_thirdparty_tax_rec.vendor_id);
990 fetch c_get_vendor_details into r_get_vendor_details;
991 close c_get_vendor_details;
992 END if;
993
994 --commented out by eric for inclusive tax ,begin
995 --lv_description := 'Invoice for vendor '|| r_get_vendor_details.vendor_name ||' against receipt no. '|| lv_receipt_num;
996 --commented out by eric for inclusive tax ,end
997
998
999 --added by eric for inclusive tax on 20-dec,2007, Begin
1000 -----------------------------------------------------------------------
1001 IF (c_thirdparty_tax_rec.inc_tax_flag = 'N') --exclusive tax case
1002 THEN
1003 ln_lines_to_insert :=1 ;
1004 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :Tax is an exclusive tax.Only one AP invoice will be created');
1005 ELSE
1006 ln_lines_to_insert :=2 ;
1007 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :Tax is an inclusive tax. Two AP invoices will be created');
1008 END IF;--(c_thirdparty_tax_rec.inc_tax_flag = 'N')
1009
1010 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :c_thirdparty_tax_rec.inc_tax_flag :'|| c_thirdparty_tax_rec.inc_tax_flag);
1011 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :ln_lines_to_insert: ' || ln_lines_to_insert );
1012 Fnd_File.put_line(Fnd_File.LOG, ' ');
1013
1014 FOR i in 1 .. ln_lines_to_insert
1015 LOOP
1016 -----------------------------------------------------------------------
1017 --added by eric for inclusive tax on 20-dec,2007,END
1018
1019 open c_get_inv_run_no;
1020 fetch c_get_inv_run_no into ln_inv_run_no;
1021 close c_get_inv_run_no;
1022
1023
1024 --commented out by eric for inclusive tax ,begin
1025 --lv_invoice_num := 'RECEIPT/'||lv_receipt_num || '/' || to_char(ln_inv_run_no);
1026 --commented out by eric for inclusive tax ,end
1027
1028
1029
1030 --added by eric for inclusive tax on 20-dec,2007, Begin
1031 -----------------------------------------------------------------------
1032 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : lv_description and lv_invoice_num begin:');
1033 IF (i =1 ) --normal third party invoice
1034 THEN
1035 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =1 Branch :');
1036
1037
1038 lv_description := 'Invoice for vendor '
1039 || r_get_vendor_details.vendor_name
1040 ||' against receipt no. '|| lv_receipt_num;
1041 lv_invoice_num := 'RECEIPT/'||lv_receipt_num || '/'
1042 || to_char(ln_inv_run_no);
1043
1044 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =1 Branch : lv_description :' || lv_description);
1045 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =1 Branch : lv_invoice_num :' || lv_invoice_num);
1046 ELSE --(i =2 ),normal third party invoice--debit memo invoice
1047 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : i =2 Branch :');
1048
1049 lv_description := 'Credit Memo for inclusive 3rd party taxes for'
1050 ||' receipt No. ' || lv_receipt_num;
1051 lv_invoice_num := 'ITP-CM/'|| lv_receipt_num || '/'
1052 ||to_char(ln_inv_run_no);
1053
1054 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =2 Branch : lv_description :' || lv_description);
1055 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2,i =2 Branch : lv_invoice_num :' || lv_invoice_num);
1056 END IF;--(i =1 )
1057
1058 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : lv_description and lv_invoice_num end:');
1059 Fnd_File.put_line(Fnd_File.LOG, ' ');
1060 -----------------------------------------------------------------------
1061 --added by eric for inclusive tax on 20-dec,2007, end
1062
1063
1064 ln_tax_amount := c_thirdparty_tax_rec.tax_amount;
1065
1066 if c_thirdparty_tax_rec.currency <> lv_func_currency then
1067 lv_currency_conversion_type := r_rcv_transactions.currency_conversion_type;
1068 lv_currency_conversion_rate := r_rcv_transactions.currency_conversion_rate;
1069 lv_currency_conversion_date := r_rcv_transactions.currency_conversion_date;
1070 else
1071 lv_currency_conversion_type := null;
1072 lv_currency_conversion_rate := null;
1073 lv_currency_conversion_date := null;
1074 end if;
1075
1076 -- get the details for the vendor site
1077 open c_get_goods_received_date(c_thirdparty_tax_rec.vendor_id , c_thirdparty_tax_rec.vendor_site_id);
1078 fetch c_get_goods_received_date into ld_goods_received_date;
1079 close c_get_goods_received_date;
1080
1081 SELECT jai_rcv_tp_invoices_s.nextval
1082 INTO ln_batch_invoice_id
1083 FROM DUAL;
1084
1085 --Tax table need to be inserted once only
1086 --added by eric for inclusive tax on 20-dec,2007, Begin
1087 -----------------------------------------------------------------------
1088 IF (i =1 )
1089 THEN
1090 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : jai_rcv_tp_invoice stable insert beign:');
1091 -----------------------------------------------------------------------
1092 --added by eric for inclusive tax on 20-dec,2007, end
1093 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,jai_rcv_tp_invoice table insert : i =1 Branch :');
1094 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1. Before insert into jai_rcv_tp_invoices ' );
1095
1096
1097 -- start for bug 13528285 by anupgupt
1098 /* Code changes done to calculate third party invoice header amount as per the correction done on receipt */
1099 DECLARE
1100 /* cursor to fetch tax lines of shipment header id and shipment line id */
1101 CURSOR c_third_party_trans IS
1102 SELECT jrlt.transaction_id, jrlt.shipment_header_id, jrlt.shipment_line_id, jrlt.tax_type, jrlt.tax_amount, jcta.vat_flag, jcta.adhoc_flag, jcta.tax_name
1103 FROM JAI_RCV_TRANSACTIONS jrt, JAI_RCV_LINE_TAXES jrlt, jai_cmn_taxes_all jcta
1104 WHERE jrt.transaction_id = jrlt.transaction_id
1105 AND jrt.shipment_header_id = jrlt.shipment_header_id
1106 AND jrt.shipment_line_id = jrlt.shipment_line_id
1107 AND jrt.shipment_header_id = p_shipment_header_id
1108 AND ( jrt.transaction_type = lv_ttype_receive OR (jrt.transaction_type = lv_ttype_correct AND jrt.parent_transaction_type = lv_ttype_receive ) )
1109 AND jrt.third_party_flag = 'N'
1110 AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery)
1111 AND jrlt.vendor_id > 0
1112 AND NVL(jrlt.tax_amount, 0) IS NOT NULL
1113 AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
1114 AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
1115 AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
1116 AND jrlt.currency = c_thirdparty_tax_rec.currency
1117 AND jrlt.tax_id = jcta.tax_id;
1118
1119 v_transaction_type jai_rcv_transactions.transaction_type%type;
1120 v_transaction_id jai_rcv_transactions.transaction_id%type;
1121 v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1122 v_corrected_quantity NUMBER;
1123 v_original_quantity NUMBER;
1124 BEGIN
1125 ln_tax_amount := 0;
1126 FOR v_third_party_trans IN c_third_party_trans
1127 LOOP
1128 /* fetch transaction type of receive event transaction */
1129 SELECT transaction_type INTO v_transaction_type
1130 FROM jai_rcv_transactions
1131 WHERE transaction_id = v_third_party_trans.transaction_id;
1132
1133 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1.1. before apportion v_third_party_trans.transaction_id > '||v_third_party_trans.transaction_id
1134 ||' > v_third_party_trans.tax_name > '||v_third_party_trans.tax_name||' > v_third_party_trans.tax_type > '||v_third_party_trans.tax_type ||' > v_third_party_trans.tax_amount > '
1135 ||v_third_party_trans.tax_amount||' > v_third_party_trans.adhoc_flag > '||v_third_party_trans.adhoc_flag||' > v_third_party_trans.vat_flag > '||v_third_party_trans.vat_flag
1136 ||' > v_third_party_trans.tax_type > '||v_third_party_trans.tax_type||' > v_transaction_type > '||v_transaction_type);
1137
1138 /* check if apportion of tax amount is required for tax line or not */
1139 IF v_third_party_trans.tax_type NOT IN ('Freight','Insurance','Octrai','Other','PURCHASE TAX','ENTRY TAX') AND (NVL(v_third_party_trans.adhoc_flag,'N') = 'N'
1140 OR (NVL(v_third_party_trans.adhoc_flag,'N') = 'Y' AND NVL(v_third_party_trans.vat_flag,'N') = 'Q')) AND v_transaction_type <> lv_ttype_correct THEN
1141 /* fetch transaction id of receive event */
1142 SELECT transaction_id, quantity INTO v_parent_transaction_id, v_original_quantity
1143 FROM jai_rcv_transactions
1144 WHERE shipment_header_id = v_third_party_trans.shipment_header_id
1145 AND shipment_line_id = v_third_party_trans.shipment_line_id
1146 AND transaction_type = lv_ttype_receive;
1147
1148 /* fetch total of quantity corrections done on receive event */
1149 SELECT NVL(SUM(quantity),0) INTO v_corrected_quantity
1150 FROM JAI_RCV_TRANSACTIONS
1151 WHERE parent_transaction_id = v_parent_transaction_id
1152 AND transaction_type = lv_ttype_correct;
1153
1154 /* apportion tax amount based on before correction quantity and corrected quantity and add it to invoice header amount */
1155 ln_tax_amount := ln_tax_amount + v_third_party_trans.tax_amount * ((v_original_quantity + v_corrected_quantity) / v_original_quantity);
1156
1157 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1.2. apportioning v_corrected_quantity > '||v_corrected_quantity||' > v_original_quantity > '||v_original_quantity
1158 ||' > tax_amount > '||v_third_party_trans.tax_amount * ((v_original_quantity + v_corrected_quantity) / v_original_quantity));
1159 ELSE
1160 /* as apportion is not required, add tax amount to invoice header amount */
1161 ln_tax_amount := ln_tax_amount + v_third_party_trans.tax_amount;
1162 END IF;
1163 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 1.3. after apportion v_third_party_trans.transaction_id > '||v_third_party_trans.transaction_id
1164 ||' > v_third_party_trans.tax_name > '||v_third_party_trans.tax_name||' > v_third_party_trans.tax_type > '||v_third_party_trans.tax_type ||' > ln_tax_amount > '||ln_tax_amount);
1165 END LOOP;
1166 END;
1167 -- end for bug 13528285 by anupgupt
1168
1169 insert into jai_rcv_tp_invoices
1170 (
1171 batch_invoice_id ,
1172 batch_id ,
1173 shipment_header_id ,
1174 vendor_id ,
1175 vendor_site_id ,
1176 invoice_num ,
1177 invoice_currency_code ,
1178 invoice_amount ,
1179 created_by ,
1180 creation_date ,
1181 last_update_login ,
1182 last_update_date ,
1183 last_updated_by,
1184 program_application_id,
1185 program_id,
1186 program_login_id,
1187 request_id
1188 )
1189 values
1190 (
1191 ln_batch_invoice_id ,
1192 p_batch_id,
1193 p_shipment_header_id,
1194 c_thirdparty_tax_rec.vendor_id,
1195 c_thirdparty_tax_rec.vendor_site_id,
1196 lv_invoice_num,
1197 c_thirdparty_tax_rec.currency,
1198 round(ln_tax_amount,2),
1199 ln_uid,
1200 sysdate,
1201 ln_uid,
1202 sysdate,
1203 null,
1204 fnd_profile.value('PROG_APPL_ID'),
1205 fnd_profile.value('CONC_PROGRAM_ID'),
1206 fnd_profile.value('CONC_LOGIN_ID'),
1207 fnd_profile.value('CONC_REQUEST_ID')
1208 );
1209
1210 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 2. After insert into jai_rcv_tp_invoices ' );
1211 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 :jai_rcv_tp_invoice table insert end:');
1212 Fnd_File.put_line(Fnd_File.LOG, ' ');
1213 --added by eric for inclusive tax on 20-dec,2007, Begin
1214 -----------------------------------------------------------------------
1215 END IF;--(i =1 )
1216 -----------------------------------------------------------------------
1217 --added by eric for inclusive tax on 20-dec,2007, end
1218
1219
1220 /* Call the package to insert data into ap interface */
1221 if p_simulation <> 'Y' then
1222
1223 ln_interface_invoice_id := null;
1224 ln_interface_line_id := null;
1225
1226 --Ap invoice interface table need to be inserted twice if necessary
1227 --the first time is for the normal third party inv
1228 --the second time is for the credit memo ,in case of inclusive tax
1229
1230 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : insertface table insert begin:');
1231
1232 --added by eric for inclusive tax on 20-dec,2007, Begin
1233 -----------------------------------------------------------------------
1234 IF (i =1 )
1235 THEN
1236 -----------------------------------------------------------------------
1237 --added by eric for inclusive tax on 20-dec,2007, end
1238 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,insertface table : i =1 Branch :');
1239 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 3. Before insert into insert_ap_inv_interface for Standard Invoice' );
1240
1241 jai_ap_utils_pkg.insert_ap_inv_interface
1242 (
1243 p_jai_source => 'Third Party Invoices',
1244 p_invoice_id => ln_interface_invoice_id,
1245 p_invoice_num => lv_invoice_num,
1246 p_invoice_type_lookup_code => 'STANDARD',
1247 p_invoice_date => r_rcv_transactions.transaction_date, /* bug 9141528 */
1248 p_vendor_id => c_thirdparty_tax_rec.vendor_id,
1249 p_vendor_site_id => c_thirdparty_tax_rec.vendor_site_id,
1250 p_invoice_amount => round(ln_tax_amount,2),
1251 p_invoice_currency_code => c_thirdparty_tax_rec.currency,
1252 p_exchange_rate => lv_currency_conversion_rate,
1253 p_exchange_rate_type => lv_currency_conversion_type,
1254 p_exchange_date => lv_currency_conversion_date,
1255 p_terms_id => r_get_vendor_details.terms_id,
1256 p_description => lv_description,
1257 p_source => 'INDIA TAX INVOICE', /* --'RECEIPT', --Ramanand for bug#4388958 */
1258 p_voucher_num => lv_invoice_num,
1259 --p_payment_method_lookup_code => r_get_vendor_details.payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
1260 p_pay_group_lookup_code => r_get_vendor_details.pay_group_lookup_code,
1261 p_goods_received_date => ld_goods_received_date,
1262 p_created_by => ln_uid,
1263 p_creation_date => sysdate,
1264 p_last_updated_by => ln_uid,
1265 p_last_update_date => sysdate,
1266 p_last_update_login => null,
1267 p_org_id => ln_org_id -- added by csahoo for bug#6139899
1268 );
1269
1270 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 4. After insert Standard third party Invoice :' || lv_invoice_num ||' into insert_ap_inv_interface');
1271 --added by eric for inclusive tax on 20-dec,2007, Begin
1272 -----------------------------------------------------------------------
1273 ELSIF (i =2 )
1274 THEN
1275 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 ,insertface table : i =2 Branch :');
1276 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 5. Before insert into insert_ap_inv_interface for CM ' );
1277
1278 --added by eric for BUG#6988610 on Apr-24,2008, Begin
1279 -----------------------------------------------------------------------
1280 OPEN get_totl_incl_tax_amount
1281 ( pn_shipment_header_id =>p_shipment_header_id
1282 , pn_vendor_id =>c_thirdparty_tax_rec.vendor_id
1283 , pn_vendor_site_id =>c_thirdparty_tax_rec.vendor_site_id
1284 , pv_currency =>c_thirdparty_tax_rec.currency
1285 );
1286 FETCH get_totl_incl_tax_amount
1287 INTO ln_totl_incl_tax_amount;
1288 CLOSE get_totl_incl_tax_amount;
1289 -----------------------------------------------------------------------
1290 --added by eric for BUG#6988610 on Apr-24,2008, End
1291
1292 OPEN c_get_vendor_site_dtls(r_rcv_transactions.vendor_site_id);
1293 FETCH c_get_vendor_site_dtls
1294 INTO orig_vndr_details_rec;
1295 CLOSE c_get_vendor_site_dtls;
1296
1297
1298 IF( orig_vndr_details_rec.terms_id IS NULL
1299 OR orig_vndr_details_rec.payment_method_lookup_code IS NULL
1300 OR orig_vndr_details_rec.pay_group_lookup_code IS NULL
1301 )
1302 THEN
1303 OPEN c_get_vendor_details(r_rcv_transactions.vendor_id);
1304 FETCH c_get_vendor_details
1305 INTO orig_vndr_details_rec;
1306 CLOSE c_get_vendor_details;
1307 END IF; -- IF( orig_vndr_details_rec.terms_id IS NULL)
1308
1309 OPEN c_get_goods_received_date(r_rcv_transactions.vendor_id , r_rcv_transactions.vendor_site_id);
1310 FETCH c_get_goods_received_date
1311 INTO ld_orig_goods_recv_date;
1312 CLOSE c_get_goods_received_date;
1313
1314 IF r_rcv_transactions.currency_code <> lv_func_currency then
1315 lv_orig_currcy_conver_type := r_rcv_transactions.currency_conversion_type;
1316 lv_orig_currcy_conver_rate := r_rcv_transactions.currency_conversion_rate;
1317 lv_orig_currcy_conver_date := r_rcv_transactions.currency_conversion_date;
1318 ELSE
1319 lv_orig_currcy_conver_type := null;
1320 lv_orig_currcy_conver_rate := null;
1321 lv_orig_currcy_conver_date := null;
1322 END IF;
1323
1324
1325 jai_ap_utils_pkg.insert_ap_inv_interface
1326 (
1327 p_jai_source => 'Third Party Invoices', --changed by eric for inclusive tax
1328 p_invoice_id => ln_interface_invoice_id,
1329 p_invoice_num => lv_invoice_num,
1330 p_invoice_type_lookup_code => 'CREDIT', /* CREDIT Memo*/ --changed by eric for inclusive tax
1331 p_invoice_date => SYSDATE,
1332 p_vendor_id => r_rcv_transactions.vendor_id, --changed by eric for inclusive tax
1333 p_vendor_site_id => r_rcv_transactions.vendor_site_id, --changed by eric for inclusive tax
1334 --p_invoice_amount => ROUND(-ln_tax_amount,2), --changed by eric for inclusive tax,deleted by eric for bug#6988610
1335 p_invoice_amount => ROUND(-ln_totl_incl_tax_amount,2), --changed by eric for bug#6988610 on Apr 23,2008
1336 p_invoice_currency_code => r_rcv_transactions.currency_code, --changed by eric for inclusive tax
1337 p_exchange_rate => lv_orig_currcy_conver_type, --changed by eric for inclusive tax
1338 p_exchange_rate_type => lv_orig_currcy_conver_rate, --changed by eric for inclusive tax
1339 p_exchange_date => lv_orig_currcy_conver_date, --changed by eric for inclusive tax
1340 p_terms_id => orig_vndr_details_rec.terms_id, --changed by eric for inclusive tax
1341 p_description => lv_description,
1342 p_source => 'INDIA TAX INVOICE', /* --'RECEIPT', --Ramanand for bug#4388958 */
1343 p_voucher_num => lv_invoice_num,
1344 p_pay_group_lookup_code => orig_vndr_details_rec.pay_group_lookup_code, --changed by eric for inclusive tax
1345 p_goods_received_date => ld_orig_goods_recv_date, --changed by eric for inclusive tax
1346 p_created_by => ln_uid,
1347 p_creation_date => sysdate,
1348 p_last_updated_by => ln_uid,
1349 p_last_update_date => sysdate,
1350 p_last_update_login => null,
1351 p_org_id => ln_org_id -- added by csahoo for bug#6139899
1352 );
1353
1354 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');
1355 --added by eric for inclusive tax on 20-dec,2007, Begin
1356 -----------------------------------------------------------------------
1357 END IF; --(i =1 )
1358 -----------------------------------------------------------------------
1359 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 2 : insertface table insert end:');
1360 Fnd_File.put_line(Fnd_File.LOG, ' ');
1361 --added by eric for inclusive tax on 20-dec,2007, end
1362 end if; /* if p_simulation <> 'Y' then */
1363
1364
1365
1366 --Moved the 'END IF' to the line before the end of the first loop
1367
1368 --Deleted by eric for inclusive tax on Dec 14,2007 Begin
1369 --END IF; /* END IF FOR if lv_vendor_has_changed = 'TRUE' THEN */
1370 --Deleted by eric for inclusive tax on Dec 14,2007 Begin
1371
1372 /*
1373 || The following for loop added by ssumaith - bug# 4284505
1374 */
1375
1376 /* Added by Ramananda for removal of SQL LITERALs */
1377 lv_ttype_receive := 'RECEIVE' ;
1378 lv_ttype_correct := 'CORRECT' ;
1379
1380 /*Bug 4941642. Added by Lakshmi Gopalsami
1381 (1) Added shipment header id condition
1382 (2) added aliases.
1383 (3) Removed two separate conditions on jai_rcv_Transactions
1384 and clubbed into a single one.
1385 */
1386 FOR Tax_rec IN
1387 ( SELECT
1388 jrlt.*
1389 , NVL(jcta.inclusive_tax_flag,'N') inc_tax_flag --added by eric for inclusive tax
1390 , jcta.vat_flag -- for bug 13528285 by anupgupt
1391 , jcta.adhoc_flag -- for bug 13528285 by anupgupt
1392 FROM
1393 JAI_RCV_LINE_TAXES jrlt
1394 , jai_cmn_taxes_all jcta --added by eric for inclusive tax
1395 WHERE jrlt.shipment_header_id = p_shipment_header_id
1396 AND (jrlt.transaction_id, jrlt.shipment_header_id,jrlt.shipment_line_id) in /*modified for bug 8567640 */
1397 ( SELECT jrt.transaction_id,jrt.shipment_header_id,jrt.shipment_line_id
1398 FROM JAI_RCV_TRANSACTIONS jrt
1399 WHERE jrt.shipment_header_id = p_shipment_header_id
1400 AND ( jrt.transaction_type = lv_ttype_receive
1401 or
1402 (jrt.transaction_type = lv_ttype_correct
1403 and jrt.parent_transaction_type = lv_ttype_receive
1404 )
1405 )
1406 AND jrt.third_party_flag = 'N'
1407 )
1408 AND jrlt.tax_type NOT IN (jai_constants.tax_type_tds,jai_constants.tax_type_modvat_recovery) --'TDS', 'Modvat Recovery')
1409 AND jrlt.vendor_id > 0
1410 AND nvl(jrlt.tax_amount, 0) IS NOT NULL
1411 AND jrlt.vendor_id <> r_rcv_transactions.vendor_id
1412 AND jrlt.vendor_id = c_thirdparty_tax_rec.vendor_id
1413 AND jrlt.vendor_site_id = c_thirdparty_tax_rec.vendor_site_id
1414 AND jrlt.currency = c_thirdparty_tax_rec.currency
1415 AND jrlt.tax_id = jcta.tax_id --added by eric for inclusive tax
1416 )
1417 LOOP
1418
1419 Fnd_File.put_line(Fnd_File.LOG, ' ');
1420 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 3 : Loop Begin');
1421 --SELECT jai_rcv_tp_inv_details_s.nextval INTO ln_batch_line_id FROM DUAL;
1422
1423 --tax table need to be populated once only
1424 --added by eric for inclusive tax on 20-dec,2007, Begin
1425 -----------------------------------------------------------------------
1426 IF (i =1 )
1427 THEN
1428 -----------------------------------------------------------------------
1429 --added by eric for inclusive tax on 20-dec,2007, end
1430 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 3 ,jai_rcv_tp_inv_details table : i =1 Branch :');
1431 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7. Before insert into jai_rcv_tp_inv_details' );
1432
1433 -- start for bug 13528285 by anupgupt
1434 /* code added to apportioning of third party invoice lines amounts based on correction of quantity in receipt*/
1435 DECLARE
1436 v_transaction_type jai_rcv_transactions.transaction_type%type;
1437 v_transaction_id jai_rcv_transactions.transaction_id%type;
1438 v_parent_transaction_id jai_rcv_transactions.transaction_id%type;
1439 v_corrected_quantity NUMBER;
1440 v_original_quantity NUMBER;
1441 BEGIN
1442 /* fetch transaction type of transaction*/
1443 SELECT transaction_type INTO v_transaction_type
1444 FROM jai_rcv_transactions
1445 WHERE transaction_id = tax_rec.transaction_id;
1446
1447 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7.1. before apportion tax_rec.transaction_id > '||tax_rec.transaction_id||' > tax_rec.tax_name > '||tax_rec.tax_name||' > tax_rec.tax_type > '
1448 ||tax_rec.tax_type ||' > tax_Rec.tax_amount > '||tax_Rec.tax_amount||' > tax_rec.adhoc_flag > '||tax_rec.adhoc_flag||' > tax_rec.vat_flag > '||tax_rec.vat_flag||' > tax_rec.tax_type > '||tax_rec.tax_type
1449 ||' > v_transaction_type > '||v_transaction_type);
1450
1451 /* check if tax line is applicable for apportioning */
1452 IF tax_rec.tax_type NOT IN ('Freight','Insurance','Octrai','Other','PURCHASE TAX','ENTRY TAX') AND (NVL(tax_rec.adhoc_flag,'N') = 'N' OR (NVL(tax_rec.adhoc_flag,'N') = 'Y'
1453 AND NVL(tax_rec.vat_flag,'N') = 'Q')) AND v_transaction_type <> lv_ttype_correct THEN
1454 /* fetch transaction id of receive event */
1455 SELECT transaction_id, quantity INTO v_parent_transaction_id, v_original_quantity
1456 FROM jai_rcv_transactions
1457 WHERE shipment_header_id = Tax_rec.shipment_header_id
1458 AND shipment_line_id = Tax_rec.shipment_line_id
1459 AND transaction_type = lv_ttype_receive;
1460
1461 /* fetch total correction of quantity done on receive event */
1462 SELECT NVL(SUM(quantity),0) INTO v_corrected_quantity
1463 FROM JAI_RCV_TRANSACTIONS
1464 WHERE parent_transaction_id = v_parent_transaction_id
1465 AND transaction_type = lv_ttype_correct;
1466
1467 /* apportion tax amount based on quantity of receive before correction and quantity correccted */
1468 tax_Rec.tax_amount := tax_Rec.tax_amount * ((v_original_quantity + v_corrected_quantity) / v_original_quantity);
1469 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7.2. apportioning v_corrected_quantity > '||v_corrected_quantity||' > v_original_quantity > '||v_original_quantity||' > tax_Rec.tax_amount > '||tax_Rec.tax_amount);
1470 END IF;
1471 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 7.3. after apportion tax_rec.transaction_id > '||tax_rec.transaction_id||' > tax_rec.tax_name > '||tax_rec.tax_name||' > tax_rec.tax_type > '
1472 ||tax_rec.tax_type ||' > tax_Rec.tax_amount > '||tax_Rec.tax_amount);
1473 END;
1474 -- end for bug 13528285 by anupgupt
1475
1476 INSERT INTO jai_rcv_tp_inv_details
1477 (
1478 BATCH_LINE_ID ,
1479 BATCH_INVOICE_ID ,
1480 RCV_TRANSACTION_ID ,
1481 LINE_NUMBER ,
1482 TAX_ID ,
1483 TAX_AMOUNT ,
1484 TAX_RATE ,
1485 TAX_TYPE ,
1486 CREATED_BY ,
1487 CREATION_DATE ,
1488 LAST_UPDATE_DATE ,
1489 LAST_UPDATED_BY ,
1490 LAST_UPDATE_LOGIN
1491 )
1492 VALUES
1493 (
1494 --ln_batch_line_id ,
1495 jai_rcv_tp_inv_details_s.nextval,
1496 ln_batch_invoice_id ,
1497 tax_rec.transaction_id,
1498 ln_line_number ,
1499 Tax_rec.tax_id ,
1500 tax_Rec.tax_amount ,
1501 tax_rec.tax_rate ,
1502 tax_rec.tax_type ,
1503 fnd_global.user_id ,
1504 sysdate ,
1505 sysdate ,
1506 fnd_global.user_id ,
1507 fnd_global.login_id
1508 ) returning BATCH_LINE_ID into ln_BATCH_LINE_ID;
1509 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 8. After insert into jai_rcv_tp_inv_details' );
1510 --added by eric for inclusive tax on 20-dec,2007, Begin
1511 -----------------------------------------------------------------------
1512 END IF; --(i =1 )
1513 -----------------------------------------------------------------------
1514 --added by eric for inclusive tax on 20-dec,2007, end
1515
1516 lv_description := 'Tax Record for invoice num ' || lv_invoice_num || ' For Tax Type = '|| tax_rec.tax_type ;
1517
1518 --added by eric for inclusive tax on 20-dec,2007, Begin
1519 -----------------------------------------------------------------------
1520 IF (i =1 )
1521 THEN
1522 ln_tax_line_amount:=round(tax_Rec.tax_amount,2);
1523 ELSE--(i =2 )
1524 ln_tax_line_amount:=round(-tax_Rec.tax_amount,2);
1525 END IF; --(i =1 )
1526 -----------------------------------------------------------------------
1527 --added by eric for inclusive tax on 20-dec,2007, end
1528
1529
1530 --added by eric for bug 6971486 on 20-Apr,2008, Begin
1531 -----------------------------------------------------------------------
1532 IF ( (i =1) OR (i =2 AND tax_rec.inc_tax_flag = 'Y'))
1533 THEN
1534 -----------------------------------------------------------------------
1535 --added by eric for bug 6971486 on 20-Apr,2008, End
1536
1537 --added by eric for inclusive tax on 20-dec,2007, Begin
1538 -----------------------------------------------------------------------
1539
1540 /* added by vumaasha for bug 8238608 */
1541 OPEN c_trx_dtls(Tax_rec.transaction_id);
1542 FETCH c_trx_dtls INTO r_trx_dtls;
1543 CLOSE c_trx_dtls;
1544
1545 OPEN c_jai_regimes(jai_constants.service_regime); /* SERVICE */
1546 FETCH c_jai_regimes INTO ln_regime_id;
1547 CLOSE c_jai_regimes;
1548
1549 ln_accrue_on_receipt_flag := jai_rcv_trx_processing_pkg.get_accrue_on_receipt
1550 (
1551 p_po_distribution_id => r_trx_dtls.po_distribution_id,
1552 p_po_line_location_id => r_trx_dtls.po_line_location_id
1553 );
1554
1555 IF ln_accrue_on_receipt_flag = 'N' THEN
1556
1557 ln_accrual_account := jai_cmn_rgm_recording_pkg.get_account
1558 (
1559 p_regime_id => ln_regime_id,
1560 p_organization_type => jai_constants.orgn_type_io,
1561 p_organization_id => r_trx_dtls.ship_to_organization_id,
1562 p_location_id => r_trx_dtls.ship_to_location_id,
1563 p_tax_type => tax_rec.tax_type,
1564 p_account_name => jai_constants.recovery_interim
1565 );
1566
1567 Fnd_File.put_line(Fnd_File.LOG, 'Interim Acct CCID for the Transaction id ' || Tax_rec.transaction_id ||' is: '||ln_accrual_account);
1568 END IF;
1569 /* end of changes for bug 8238608 */
1570
1571 jai_ap_utils_pkg.insert_ap_inv_lines_interface
1572 (
1573 p_jai_source => 'Third Party Invoices',
1574 p_invoice_id => ln_interface_invoice_id,
1575 p_invoice_line_id => ln_interface_line_id,
1576 p_line_number => ln_to_insert_line_number,
1577 --p_line_type_lookup_code => 'MISCELLANEOUS', --deleted by eric FOR BUG bug#6790599
1578 p_line_type_lookup_code => 'ITEM', --added by eric FOR BUG bug#6790599
1579 --Modified by eric for inclusive tax ,begin
1580 p_amount => ln_tax_line_amount,-- round(tax_Rec.tax_amount,2),
1581 --Modified by eric for inclusive tax ,end
1582 p_accounting_date => r_rcv_transactions.transaction_date,
1583 p_description => lv_description,
1584 p_dist_code_combination_id => ln_accrual_account,
1585 p_assets_tracking_flag => lv_assets_tracking_flag,
1586 p_created_by => ln_uid,
1587 p_creation_date => sysdate,
1588 p_last_updated_by => ln_uid,
1589 p_last_update_date => sysdate,
1590 p_last_update_login => null,
1591 p_org_id => ln_org_id -- added by csahoo for bug#139899
1592 );
1593 Fnd_File.put_line(Fnd_File.LOG, ' DEBUG : 10. After insert into insert_ap_inv_lines_interface' );
1594 --added by eric for bug 6971486 on 20-Apr,2008, Begin
1595 -----------------------------------------------------------------------
1596 ln_line_number:=ln_line_number+1; /* added by vumaasha for bug 8965721 */
1597 END IF;
1598 -----------------------------------------------------------------------
1599 --added by eric for bug 6971486 on 20-Apr,2008, End
1600 END LOOP;
1601 ln_line_number := 1; /* modified by vumaasha for bug 8965721 */
1602 --Added by eric for inclusive tax on Dec 14,2007 Begin
1603 ------------------------------------------------------------------------------
1604
1605 Fnd_File.put_line(Fnd_File.LOG, ' ');
1606 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 3 : Loop end');
1607 END LOOP;--( i in 1 .. ln_lines_to_insert)
1608 END IF; /* END IF FOR if lv_vendor_has_changed = 'TRUE' THEN */
1609 ------------------------------------------------------------------------------
1610 --Added by eric for inclusive tax on Dec 14,2007 End
1611 Fnd_File.put_line(Fnd_File.LOG, '**** Debug Level 2 : Loop end');
1612 Fnd_File.put_line(Fnd_File.LOG, ' ');
1613 end loop; --(For c_thirdparty_tax_rec IN)
1614
1615
1616 << exit_from_procedure >>
1617 if p_process_flag is null then
1618 p_process_flag := 'Y';
1619 end if;
1620
1621 if p_debug >= 1 then
1622 Fnd_File.put_line(Fnd_File.LOG, ' ** Debug Level 1 : ' ||
1623 'End of procedure jai_rcv_third_party_pkg.process_receipt for shipment header :' ||
1624 to_char(p_shipment_header_id)
1625 );
1626 end if;
1627
1628 return;
1629
1630 exception
1631 when others then
1632 p_process_flag := 'E';
1633 p_process_message := 'jai_rcv_third_party_pkg.process_receipt:' || sqlerrm;
1634 fnd_file.put_line( fnd_file.log, '******** Error in '||p_process_message);
1635 raise;
1636
1637 end process_receipt;
1638 /****************************** End process_receipt ****************************/
1639
1640 /****************************** Start populate_tp_invoice_id ****************************/
1641 procedure populate_tp_invoice_id
1642 (
1643 p_invoice_id IN ap_invoices_all.invoice_id%TYPE,
1644 p_invoice_num IN ap_invoices_all.invoice_num%TYPE,
1645 p_vendor_id IN ap_invoices_all.vendor_id%TYPE,
1646 p_vendor_site_id IN ap_invoices_all.vendor_site_id%TYPE,
1647 p_process_flag OUT NOCOPY VARCHAR2,
1648 p_process_message OUT NOCOPY VARCHAR2
1649 )
1650 IS
1651
1652 /*
1653 || This procedure is added by ssumaith - bug# 4284505 for updating the invoice_id in the jai_Rcv_tp_invoices table
1654 || This procedure will be called from the trigger - ja_in_ap_aia_before_trg on ap_invoices_all
1655 */
1656 BEGIN
1657
1658 UPDATE jai_rcv_tp_invoices
1659 SET invoice_id = p_invoice_id ,
1660 last_update_date = sysdate ,
1661 last_updated_by = fnd_global.user_id
1662 WHERE invoice_num = p_invoice_num
1663 AND vendor_id = p_vendor_id
1664 AND vendor_site_id = p_vendor_site_id;
1665
1666 p_process_flag := jai_constants.successful;
1667
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 p_process_flag := jai_constants.unexpected_error;
1671 p_process_message := substr(sqlerrm,1,1000);
1672
1673 END;
1674
1675 /****************************** End populate_tp_invoice_id ****************************/
1676
1677
1678 end jai_rcv_third_party_pkg;
[Home] [Help]