[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_UTILS_PKG
Source
1 PACKAGE BODY jai_ap_utils_pkg AS
2 /* $Header: jai_ap_utils.plb 120.19.12010000.2 2008/11/25 10:40:19 mbremkum ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Remarks
10 ------------------------------------------------------------------------------------------------------
11 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in
12 place of Old DB Entity Names as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.4
15 Modified the object to remove literals from DML statements and CURSORS.
16
17 23-Jun-2005 Brathod , File Version 112.0 , Bug# 4445989
18 - Signature for procedure get_aportion_factor is modified to use invoice_id and
19 invoice_line_number
20 - Code modified to fetch the details from ap_invoice_lines_all
21 instead of ap_invoice_distributions_all
22
23 02-Sep-2005 Ramananda for Bug#4584221, File Version 120.2
24 Added the new function get_tds_invoice_batch
25 In the form regime registrations (JAIREGIM.fmb) attribute_value field is a free flowing text.
26 In function get_tds_invoice_batch we have considered the values to be 'YES' or 'Y' to get the batch name
27
28 Dependency (Functional)
29 ----------------------
30 jai_ap_utils.pls (120.2)
31 jai_ap_tds_old.plb (120.3)
32 jai_ap_tds_gen.plb (120.8)
33 jai_constants.pls (120.3)
34 jaiorgdffsetup.sql (120.2)
35 jaivmlu.ldt
36
37 3 07/12/2005 Hjujjuru for Bug 4870243, File version 120.5
38 Issue : Invoice Import Program is rejecting the Invoices.
39 Fix : Commented the voucher_num insert into the ap_invoices_interface table
40 4 23/02/2007 bduvarag for Bug#4990941, File version 120.8
41 Forward porting the changes done in 11i bug 4709459
42 5 04/11/2007 bduvarag for Bug#5607160, File version 120.9
43 Forward porting the changes done in 11i bug#5591827
44 6 04/17/2007 vkaranam for Bug#5989740, File version 120.10
45 Forward porting the changes done in 11i bug#5583832
46
47 7 04-Jul-2007 kukumar for bug# 5593895, File version 120.12,120.13 ( brathod changed for 120.11 )
48 Projects changes are not included in this checkin and GSCC error resolved.
49
50 8 04-Jul-2007 Forward porting iSupplier changes
51 Forward porting the changes done in 11i bug#5961325 bug#3637364
52
53 9 17-DEC-2007 Jia Li for Tax inclusive computation
54
55 10 24-Jan-2008 Modifed by Jason Liu for retroactive price
56
57 11 14-APR-2008 Kevin Cheng for bug#6962018
58 change return value from 1 to ratio of AP invoice quantity to PO item quantity for
59 partially recoverable issue.
60 ---------------------------------------------------------------------------------------------------------
61 */
62 GV_MODULE_PREFIX CONSTANT VARCHAR2(30) := 'jai_ap_utils_pkg'; -- -- Added by Jia Li for tax inclusive computation on 2007/12/26
63
64 PROCEDURE create_pla_invoice(P_PLA_ID IN NUMBER,
65 P_SET_OF_BOOK_ID IN NUMBER, P_ORG_ID IN NUMBER) AS
66
67
68 CURSOR counter_cur(inv_id NUMBER) IS
69 SELECT NVL(MAX(line_number),0) + 1 line_num
70 FROM ap_invoice_lines_interface
71 WHERE invoice_id = inv_id;
72
73 CURSOR for_accounting_date(id NUMBER) IS
74 SELECT jibh.tr6_date
75 FROM JAI_CMN_RG_PLA_HDRS jibh,
76 PO_VENDORS pv,
77 PO_VENDOR_SITES_ALL pvs
78 WHERE jibh.PLA_ID = id
79 AND pvs.vendor_site_id (+)= jibh.vendor_site_id
80 AND pv.vendor_id = jibh.vendor_id;
81
82 CURSOR for_invoice_num IS
83 SELECT 'PLA/Invoice/'||TO_CHAR(p_org_id) inv_num
84 FROM DUAL;
85
86 /* Bug 4928860. Added by Lakshmi Gopalsami
87 Removed select and added cursor.
88 */
89 CURSOR multi_org_installed is
90 SELECT decode(multi_org_flag, 'Y', 1,0) multi_org_cnt
91 FROM fnd_product_groups;
92
93 p_rep_head_id NUMBER;
94 p_currency_code VARCHAR2(15);
95 cnt_rec NUMBER;
96 inv_interface_id NUMBER;
97 modvat NUMBER;
98 counter_cur_rec counter_cur%ROWTYPE;
99 inv_line_interface_id NUMBER;
100 for_accounting_date_rec for_accounting_date%ROWTYPE;
101 for_invoice_num_rec for_invoice_num%ROWTYPE;
102 count_orgs NUMBER :=0 ; -- Bug 4928860
103 v_org_id NUMBER;
104 lv_source AP_INVOICES_INTERFACE.source%TYPE ;
105 lv_lookup_type_code ap_invoices_interface.invoice_type_lookup_code%TYPE; --rchandan for bug#4428980
106 lv_description ap_invoices_interface.description%type; --rchandan for bug#4428980
107
108 /* start additions by ssumaith - bug# 4448789 */
109 ln_legal_entity_id NUMBER;
110 lv_legal_entity_name VARCHAR2(240);
111 lv_return_status VARCHAR2(100);
112 ln_msg_count NUMBER;
113 ln_msg_data VARCHAR2(1000);
114 /* ends additions by ssumaith - bug# 4448789*/
115
116 /*-------------------------------------------------------------------------------------------------------------------------
117 FILENAME: ja_in_ins_aplah_aplal_pla_p.sql
118 CHANGE HISTORY:
119
120 S.No Date Author and Details
121 ----------------------------------------------
122 1 24-oct-2002 Aparajita Das for bug # 2639278
123 Populating the siource in ap_invoices_header as "EXTERNAL" instead of "External".
124
125 -------------------------------------------------------------------------------------------------------------------------*/
126
127 BEGIN
128
129 /* Bug 4928860. Added by Lakshmi Gopalsami
130 Removed the count(distinct(org_id) from ap_invoices_all
131 and added the cursor on fnd_product_groups to find out whether
132 multi-org is enabled or not.
133 */
134 OPEN multi_org_installed;
135 FETCH multi_org_installed INTO count_orgs;
136 CLOSE multi_org_installed;
137
138 IF count_orgs = 0 THEN
139 v_org_id := '' ;
140 ELSE
141 v_org_id := p_org_id;
142 END IF;
143
144 Select ap_invoices_interface_s.nextval
145 Into inv_interface_id
146 From dual;
147
148 SELECT ap_invoice_lines_interface_s.NEXTVAL
149 INTO inv_line_interface_id
150 FROM DUAL;
151
152 Select currency_code
153 Into p_currency_code
154 From gl_sets_of_books
155 Where set_of_books_id = P_SET_OF_BOOK_ID;
156
157 OPEN for_invoice_num;
158 FETCH for_invoice_num INTO for_invoice_num_rec;
159 CLOSE for_invoice_num;
160
161 /* start additions by ssumaith - bug# 4448789 */
162 jai_cmn_utils_pkg.GET_LE_INFO(
163 P_API_VERSION => NULL ,
164 P_INIT_MSG_LIST => NULL ,
165 P_COMMIT => NULL ,
166 P_LEDGER_ID => P_SET_OF_BOOK_ID,
167 P_BSV => NULL,
168 P_ORG_ID => v_ORG_ID,
169 X_RETURN_STATUS => lv_return_status ,
170 X_MSG_COUNT => ln_msg_count,
171 X_MSG_DATA => ln_msg_data,
172 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
173 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
174 );
175 /* ends additions by ssumaith - bug# 4448789*/
176
177 /* Bug 5359044. Added by Lakshmi Gopalsami
178 * Changed the 'EXTERNAL' TO 'INDIA - BOE/PLA INVOICES'
179 */
180 lv_source :='INDIA - BOE/PLA INVOICES';
181
182 Insert into AP_INVOICES_INTERFACE
183 (
184 invoice_id ,
185 invoice_num,
186 invoice_date,
187 vendor_id,
188 vendor_site_id,
189 invoice_amount,
190 invoice_currency_code,
191 accts_pay_code_combination_id,
192 source,
193 org_id,
194 legal_entity_id , /*added by ssumaith - bug# 4448789 */
195 created_by,
196 creation_date,
197 last_updated_by,
198 last_update_date
199 )
200 SELECT
201 inv_interface_id , -- REPORT_HEADER_ID,
202 for_invoice_num_rec.inv_num||'/'||jibh.PLA_ID, -- INVOICE_NUM,
203 jibh.TR6_DATE, -- (Invoice Date ) WEEK_END_DATE,
204 jibh.VENDOR_ID, -- VENDOR_ID,
205 jibh.VENDOR_SITE_ID, -- VENDOR_SITE_ID,
206 jibh.PLA_AMOUNT, -- TOTAL,
207 p_currency_code, -- DEFAULT_CURRENCY_CODE,
208 -- Bug 5141305. Added by Lakshmi Gopalsami
209 -- Removed the reference to accts_pay_code_combination_id of po_vendors
210 pvs.ACCTS_PAY_CODE_COMBINATION_ID,
211 lv_source,
212 v_ORG_ID, -- ORG_ID
213 ln_legal_entity_id , -- LEGAL_ENTITY_ID
214 jibh.CREATED_BY, -- CREATED_BY,
215 jibh.CREATION_DATE, -- CREATION_DATE,
216 jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
217 jibh.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
218 FROM JAI_CMN_RG_PLA_HDRS jibh,
219 PO_VENDORS pv,
220 PO_VENDOR_SITES_ALL pvs
221 WHERE jibh.PLA_ID = P_PLA_ID
222 AND pvs.vendor_site_id (+)= jibh.vendor_site_id
223 AND pv.vendor_id = jibh.vendor_id
224 AND NVL(pvs.org_id, 0) = NVL(v_org_id, 0);
225
226 SELECT count(*)
227 into cnt_rec
228 FROM JAI_CMN_RG_PLA_HDRS jibh,
229 JAI_CMN_INVENTORY_ORGS org
230 WHERE jibh.PLA_ID = P_PLA_ID
231 AND org.organization_id = jibh.organization_id
232 AND org.location_id = jibh.location_id;
233
234 OPEN counter_cur(inv_interface_id);
235 FETCH counter_cur INTO counter_cur_rec;
236 CLOSE counter_cur;
237
238 OPEN for_accounting_date(p_pla_id);
239 FETCH for_accounting_date INTO for_accounting_date_rec;
240 CLOSE for_accounting_date;
241
242 if cnt_rec = 0 then
243 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
244 lv_description := 'Line for Invoice no ' || P_PLA_ID; --rchandan for bug#4428980
245 INSERT INTO ap_invoice_lines_interface
246 (
247 invoice_id,
248 invoice_line_id,
249 line_number,
250 line_type_lookup_code,
251 amount,
252 accounting_date,
253 description,
254 dist_code_combination_id,
255 org_id,
256 amount_includes_tax_flag,
257 created_by,
258 creation_date,
259 last_updated_by,
260 last_update_date,
261 last_update_login
262 )
263 SELECT
264 inv_interface_id, -- REPORT_HEADER_ID,
265 inv_line_interface_id,
266 counter_cur_rec.line_num,
267 lv_lookup_type_code, -- LINE_TYPE_LOOKUP_CODE, --rchandan for bug#4428980
268 jibh.PLA_AMOUNT, -- AMOUNT,
269 for_accounting_date_rec.tr6_date,
270 lv_description, -- ITEM_DESCRIPTION, --rchandan for bug#4428980
271 org.MODVAT_PLA_ACCOUNT_ID, -- ACCTS_PAY_CODE_COMBINATION_ID,
275 jibh.CREATION_DATE, -- CREATION_DATE,
272 v_ORG_ID, -- ORG_ID,
273 'N', -- AMOUNT_INCLUDES_TAX_FLAG,
274 jibh.CREATED_BY, -- CREATED_BY,
276 jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
277 jibh.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE,
278 NULL -- LAST_UPDATE_LOGIN
279 FROM JAI_CMN_RG_PLA_HDRS jibh,
280 JAI_CMN_INVENTORY_ORGS org
281 WHERE jibh.PLA_ID = P_PLA_ID
282 AND org.organization_id = jibh.organization_id
283 AND org.location_id = 0 ;
284
285 else
286 lv_lookup_type_code := 'ITEM';--rchandan for bug#4428980
287 lv_description := 'Line for Invoice no ' || P_PLA_ID;--rchandan for bug#4428980
288 INSERT INTO ap_invoice_lines_interface
289 (
290 invoice_id,
291 invoice_line_id,
292 line_number,
293 line_type_lookup_code,
294 amount,
295 accounting_date,
296 description,
297 dist_code_combination_id,
298 org_id,
299 amount_includes_tax_flag,
300 created_by,
301 creation_date,
302 last_updated_by,
303 last_update_date,
304 last_update_login
305 )
306 SELECT
307 inv_interface_id, -- REPORT_HEADER_ID,
308 inv_line_interface_id,
309 counter_cur_rec.line_num,
310 lv_lookup_type_code, -- LINE_TYPE_LOOKUP_CODE, --rchandan for bug#4428980
311 jibh.PLA_AMOUNT, -- AMOUNT,
312 for_accounting_date_rec.tr6_date,
313 lv_description, -- ITEM_DESCRIPTION, --rchandan for bug#4428980
314 org.MODVAT_PLA_ACCOUNT_ID, -- ACCTS_PAY_CODE_COMBINATION_ID,
315 v_ORG_ID, -- ORG_ID,
316 'N', -- AMOUNT_INCLUDES_TAX_FLAG,
317 jibh.CREATED_BY, -- CREATED_BY,
318 jibh.CREATION_DATE, -- CREATION_DATE,
319 jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
320 jibh.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE,
321 NULL -- LAST_UPDATE_LOGIN
322 FROM JAI_CMN_RG_PLA_HDRS jibh,
323 JAI_CMN_INVENTORY_ORGS org
324 WHERE jibh.PLA_ID = P_PLA_ID
325 AND org.organization_id = jibh.organization_id
326 AND org.location_id = jibh.location_id;
327
328 end if;
329
330 END create_pla_invoice;
331
332 PROCEDURE create_boe_invoice
333 (
334 P_BOE_ID IN NUMBER,
335 P_SET_OF_BOOK_ID IN NUMBER,
336 P_ORG_ID IN NUMBER
337 )
338 IS
339
340 CURSOR counter_cur(inv_id NUMBER) IS
341 SELECT NVL(MAX(line_number),0) + 1 line_num
342 FROM ap_invoice_lines_interface
343 WHERE invoice_id = inv_id;
344
345 CURSOR for_invoice_num IS
346 SELECT 'BOE/Invoice/'||TO_CHAR(p_org_id)||'/'||TO_CHAR(P_BOE_ID) inv_num
347 FROM DUAL; --Added on 21-Feb-2002
348
349 /* Bug 4928860. Added by Lakshmi Gopalsami
350 Removed select and added cursor.
351 */
352 CURSOR multi_org_installed is
353 SELECT decode(multi_org_flag, 'Y', 1,0) multi_org_cnt
354 FROM fnd_product_groups;
355
356
357 inv_interface_id NUMBER;
358 inv_line_interface_id NUMBER;
359 p_currency_code VARCHAR(15);
360 cnt_rec NUMBER;
361 counter_cur_rec counter_cur%ROWTYPE;
362 for_invoice_num_rec for_invoice_num%ROWTYPE;
363 count_orgs NUMBER :=0 ; -- Bug 4928860
364 v_org_id NUMBER;
365 lv_description ap_invoices_interface.description%type; -- Ravi for literal removal
366 lv_lookup_type_code ap_invoices_interface.invoice_type_lookup_code%TYPE; --Ravi for literal removal
367 lv_source ap_invoices_interface.source%type; --Ravi for literal removal
368
369 /* start additions by ssumaith - bug# 4448789 */
370 ln_legal_entity_id NUMBER;
371 lv_legal_entity_name VARCHAR2(240);
372 lv_return_status VARCHAR2(100);
373 ln_msg_count NUMBER;
374 ln_msg_data VARCHAR2(1000);
375 /* ends additions by ssumaith - bug# 4448789*/
376
377
378 BEGIN
379
380
381 /*------------------------------------------------------------------------------------------------------------------
382 FILENAME: ja_ins_aerha_aerla_p.sql
383 CHANGE HISTORY:
384
385 S.No Date Author and Details
386 ----------------------------------------------
387 1 21-Feb-2002 RPK:. Version#610.1
388 for the issue of the BOE invoice nums getting stuck up in the interfaces
389 with the reason 'duplicate invoice nums'.
390
391 2 08-MAY-2002 Aparajita for bug 2361769. Version#614.1
392 voucher number field of BOE invoice was not getting populated, populated it with the
393 same value as invoice number.
394
395 3 24-oct-2002 Aparajita Das for bug # 2639278. Version#615.1
396 Populating the source in ap_invoices_header as "EXTERNAL" instead of "External".
397
398 4 22/07/2003 Vijay Shankar for bug#3049198. Version#616.1
399
400 Accounting date for Invoice distributions should be the IMPORT_DATE instead of bol_date.
401 GL_DATE of INVOICE should be populated with IMPORT_DATE which is not happening previously
402 Also INVOICE_DATE of the Invoice is populated with IMPORT_DATE
403 - Removed the definition of cursor for_accounting_date as it was not required.
404 5 10/04/2007 bduvarag for bug#5607160,File version 120.9
405 Forward porting the changes done in 11i bug#5591827
406
407 -------------------------------------------------------------------------------------------------------------------*/
408
409 /* Bug 4928860. Added by Lakshmi Gopalsami
410 Removed the count(distinct(org_id) from ap_invoices_all
411 and added the cursor on fnd_product_groups to find out whether
412 multi-org is enabled or not.
413 */
414
415 OPEN multi_org_installed;
416 FETCH multi_org_installed INTO count_orgs;
417 CLOSE multi_org_installed;
418
419 IF count_orgs = 0 THEN
420 v_org_id := '' ;
421 ELSE
422 v_org_id := p_org_id;
423 END IF;
424
425 SELECT ap_invoices_interface_s.NEXTVAL
426 INTO inv_interface_id
427 FROM dual;
428
429 SELECT ap_invoice_lines_interface_s.NEXTVAL
430 INTO inv_line_interface_id
431 FROM DUAL;
432
433 SELECT currency_code
434 INTO p_currency_code
435 FROM gl_sets_of_books
436 WHERE set_of_books_id = p_set_of_book_id;
437
438 OPEN for_invoice_num;
439 FETCH for_invoice_num INTO for_invoice_num_rec;
440 CLOSE for_invoice_num;
441
442
443
444 /* start additions by ssumaith - bug# 4448789 */
445 jai_cmn_utils_pkg.GET_LE_INFO(
446 P_API_VERSION => NULL ,
447 P_INIT_MSG_LIST => NULL ,
448 P_COMMIT => NULL ,
449 P_LEDGER_ID => P_SET_OF_BOOK_ID,
450 P_BSV => NULL,
451 P_ORG_ID => v_ORG_ID,
452 X_RETURN_STATUS => lv_return_status ,
453 X_MSG_COUNT => ln_msg_count,
454 X_MSG_DATA => ln_msg_data,
455 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
456 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
457 );
458 /* ends additions by ssumaith - bug# 4448789*/
459
460 /* Bug 5359044. Added by Lakshmi Gopalsami
461 * Changed the 'EXTERNAL' TO 'INDIA - BOE/PLA INVOICES'
462 */
463
464 lv_source := 'INDIA - BOE/PLA INVOICES';
465
466 INSERT INTO AP_INVOICES_INTERFACE
467 (
468 invoice_id,
469 invoice_num,
470 -- voucher_num, -- added by Aparajita on 08-may-2002 bug 2361769 Harshita for Bug 4870243
471 invoice_date,
472 vendor_id,
473 vendor_site_id,
474 invoice_amount,
475 invoice_currency_code,
476 accts_pay_code_combination_id,
477 --set_of_books_id,
478 source,
479 gl_date, -- Vijay Shankar for bug#3049198
480 --accounting_date,
481 org_id,
482 legal_entity_id ,
483 created_by,
484 creation_date,
485 last_updated_by,
486 last_update_date
487 )
488 SELECT
489 inv_interface_id, -- invoice_interface_header_id,
490 for_invoice_num_rec.inv_num, -- invoice_num, --added on 21-feb-2002
491 -- for_invoice_num_rec.inv_num, -- added for voucher number, same as invoice number by aparajita Harshita for Bug 4870243
492 -- trunc(jibh.bol_date),
493 trunc(jibh.import_date), -- Vijay Shankar for bug#3049198
494 jibh.vendor_id,
495 jibh.vendor_site_id,
499 -- Removed the reference to accts_pay_code_combination_id of po_vendors
496 round(jibh.boe_amount), -- total,/*Bug 5607160 bduvarag*/
497 p_currency_code, -- default_currency_code,
498 -- Bug 5141305. Added by Lakshmi Gopalsami
500 pvs.ACCTS_PAY_CODE_COMBINATION_ID,
501 lv_source,
502 trunc(jibh.import_date), -- Vijay Shankar for bug#3049198
503 v_org_id , -- org_id,
504 ln_legal_entity_id , -- LEGAL_ENTITY_ID
505 jibh.created_by,
506 trunc(jibh.creation_date),
507 jibh.last_updated_by,
508 trunc(jibh.last_update_date)
509 FROM
510 JAI_CMN_BOE_HDRS jibh,
511 po_vendors pv,
512 po_vendor_sites_all pvs
513 where jibh.boe_id = p_boe_id
514 and pvs.vendor_site_id (+)= jibh.vendor_site_id
515 and pv.vendor_id = jibh.vendor_id
516 and nvl(pvs.org_id, 0) = nvl(v_org_id, 0);
517
518 select count(*)
519 into cnt_rec
520 from JAI_CMN_BOE_HDRS jibh,
521 JAI_CMN_INVENTORY_ORGS org
522 where jibh.boe_id = p_boe_id
523 and org.organization_id = jibh.organization_id
524 and org.location_id = jibh.location_id;
525
526 open counter_cur(inv_interface_id);
527 fetch counter_cur into counter_cur_rec;
528 close counter_cur;
529
530 IF cnt_rec = 0 THEN
531 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
532 lv_description := 'line for invoice no ' || p_boe_id; --rchandan for bug#4428980
533
534 insert into ap_invoice_lines_interface
535 (
536 invoice_id,
537 invoice_line_id,
538 line_number,
539 line_type_lookup_code,
540 amount,
541 accounting_date,
542 description,
543 dist_code_combination_id,
544 org_id,
545 amount_includes_tax_flag,
546 created_by,
547 creation_date,
548 last_updated_by,
549 last_update_date,
550 last_update_login
551 )
552 SELECT
553 inv_interface_id, -- report_header_id,
554 inv_line_interface_id,
555 counter_cur_rec.line_num,
556 lv_lookup_type_code, -- line_type_lookup_code, --rchandan for bug#4428980
557 round(jibh.boe_amount), -- amount,/*Bug 5607160 bduvarag*/
558 jibh.import_date, -- bug#3049198
559 lv_description, -- item_description, --rchandan for bug#4428980
560 org.boe_account_id,
561 v_org_id, -- org_id,
562 'N' , -- amount_includes_tax_flag,
563 jibh.created_by,
564 trunc(jibh.creation_date),
565 jibh.last_updated_by,
566 jibh.last_update_date,
567 null -- last_update_login
568 from JAI_CMN_BOE_HDRS jibh,
569 JAI_CMN_INVENTORY_ORGS org
570 where jibh.boe_id = p_boe_id
571 and org.organization_id = jibh.organization_id
572 AND org.location_id = 0 ;
573
574 ELSE
575 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
576 lv_description := 'Line for Invoice no ' || P_BOE_ID; --rchandan for bug#4428980
577
578 insert into ap_invoice_lines_interface
579 (
580 invoice_id,
581 invoice_line_id,
582 line_number,
583 line_type_lookup_code,
584 amount,
585 accounting_date,
586 description,
587 dist_code_combination_id,
588 org_id,
589 amount_includes_tax_flag,
590 created_by,
591 creation_date,
592 last_updated_by,
593 last_update_date,
594 last_update_login
595 )
596 select
597 inv_interface_id, -- report_header_id,
598 inv_line_interface_id,
599 counter_cur_rec.line_num,
600 lv_lookup_type_code, -- line_type_lookup_code, --rchandan for bug#4428980
601 round(jibh.boe_amount),/*Bug 5607160 bduvarag*/
602 jibh.import_date, -- bug#3049198
603 lv_description, -- item_description, --rchandan for bug#4428980
604 org.boe_account_id,
605 v_org_id, -- org_id,
606 'N', -- amount_includes_tax_flag,
607 jibh.created_by,
608 trunc(jibh.creation_date),
609 jibh.last_updated_by,
610 jibh.last_update_date,
611 null -- last_update_login
612 from
613 JAI_CMN_BOE_HDRS jibh,
614 JAI_CMN_INVENTORY_ORGS org
615 where
616 jibh.boe_id = p_boe_id
617 and org.organization_id = jibh.organization_id
618 and org.location_id = jibh.location_id;
619
620 end if;
621
622 END create_boe_invoice;
623
624 PROCEDURE insert_ap_inv_interface(
625 p_jai_source IN VARCHAR2,
626 p_invoice_id OUT NOCOPY ap_invoices_interface.INVOICE_ID%TYPE,
627 p_invoice_num IN ap_invoices_interface.INVOICE_NUM%TYPE DEFAULT NULL,
628 p_invoice_type_lookup_code IN ap_invoices_interface.INVOICE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
629 p_invoice_date IN ap_invoices_interface.INVOICE_DATE%TYPE DEFAULT NULL,
630 p_po_number IN ap_invoices_interface.PO_NUMBER%TYPE DEFAULT NULL,
631 p_vendor_id IN ap_invoices_interface.VENDOR_ID%TYPE DEFAULT NULL,
635 p_vendor_site_code IN ap_invoices_interface.VENDOR_SITE_CODE%TYPE DEFAULT NULL,
632 p_vendor_num IN ap_invoices_interface.VENDOR_NUM%TYPE DEFAULT NULL,
633 p_vendor_name IN ap_invoices_interface.VENDOR_NAME%TYPE DEFAULT NULL,
634 p_vendor_site_id IN ap_invoices_interface.VENDOR_SITE_ID%TYPE DEFAULT NULL,
636 p_invoice_amount IN ap_invoices_interface.INVOICE_AMOUNT%TYPE DEFAULT NULL,
637 p_invoice_currency_code IN ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE DEFAULT NULL,
638 p_exchange_rate IN ap_invoices_interface.EXCHANGE_RATE%TYPE DEFAULT NULL,
639 p_exchange_rate_type IN ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL,
640 p_exchange_date IN ap_invoices_interface.EXCHANGE_DATE%TYPE DEFAULT NULL,
641 p_terms_id IN ap_invoices_interface.TERMS_ID%TYPE DEFAULT NULL,
642 p_terms_name IN ap_invoices_interface.TERMS_NAME%TYPE DEFAULT NULL,
643 p_description IN ap_invoices_interface.DESCRIPTION%TYPE DEFAULT NULL,
644 p_awt_group_id IN ap_invoices_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
645 p_awt_group_name IN ap_invoices_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
646 p_last_update_date IN ap_invoices_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
647 p_last_updated_by IN ap_invoices_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
648 p_last_update_login IN ap_invoices_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
649 p_creation_date IN ap_invoices_interface.CREATION_DATE%TYPE DEFAULT NULL,
650 p_created_by IN ap_invoices_interface.CREATED_BY%TYPE DEFAULT NULL,
651 --Added below the attribute category and attribute parameters for Bug #3841637
652 p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
653 p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
654 p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
655 p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
656 p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
657 p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
658 p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
659 p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
660 p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
661 p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
662 p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
663 p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
664 p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
665 p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
666 p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
667 p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
668 p_status IN ap_invoices_interface.STATUS%TYPE DEFAULT NULL,
669 p_source IN ap_invoices_interface.SOURCE%TYPE DEFAULT NULL,
670 p_group_id IN ap_invoices_interface.GROUP_ID%TYPE DEFAULT NULL,
671 p_request_id IN ap_invoices_interface.REQUEST_ID%TYPE DEFAULT NULL,
672 p_payment_cross_rate_type IN ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE DEFAULT NULL,
673 p_payment_cross_rate_date IN ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE DEFAULT NULL,
674 p_payment_cross_rate IN ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE DEFAULT NULL,
675 p_payment_currency_code IN ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE DEFAULT NULL,
676 p_workflow_flag IN ap_invoices_interface.WORKFLOW_FLAG%TYPE DEFAULT NULL,
677 p_doc_category_code IN ap_invoices_interface.DOC_CATEGORY_CODE%TYPE DEFAULT NULL,
678 p_voucher_num IN ap_invoices_interface.VOUCHER_NUM%TYPE DEFAULT NULL,
679 p_payment_method_lookup_code IN ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE DEFAULT NULL,
680 p_pay_group_lookup_code IN ap_invoices_interface.PAY_GROUP_LOOKUP_CODE%TYPE DEFAULT NULL,
681 p_goods_received_date IN ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE DEFAULT NULL,
682 p_invoice_received_date IN ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE DEFAULT NULL,
683 p_gl_date IN ap_invoices_interface.GL_DATE%TYPE DEFAULT NULL,
684 p_accts_pay_ccid IN ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
688 p_amount_applicable_to_dis IN ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE DEFAULT NULL,
685 p_ussgl_transaction_code IN ap_invoices_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
686 p_exclusive_payment_flag IN ap_invoices_interface.EXCLUSIVE_PAYMENT_FLAG%TYPE DEFAULT NULL,
687 p_org_id IN ap_invoices_interface.ORG_ID%TYPE DEFAULT NULL,
689 p_prepay_num IN ap_invoices_interface.PREPAY_NUM%TYPE DEFAULT NULL,
690 p_prepay_dist_num IN ap_invoices_interface.PREPAY_DIST_NUM%TYPE DEFAULT NULL,
691 p_prepay_apply_amount IN ap_invoices_interface.PREPAY_APPLY_AMOUNT%TYPE DEFAULT NULL,
692 p_prepay_gl_date IN ap_invoices_interface.PREPAY_GL_DATE%TYPE DEFAULT NULL,
693 -- Bug4240179. Added by LGOPALSA. Changed the data type
694 -- for the following 4 fields.
695 p_invoice_includes_prepay_flag IN VARCHAR2 DEFAULT NULL,
696 p_no_xrate_base_amount IN NUMBER DEFAULT NULL,
697 p_vendor_email_address IN VARCHAR2 DEFAULT NULL,
698 p_terms_date IN DATE DEFAULT NULL,
699 p_requester_id IN NUMBER DEFAULT NULL,
700 p_ship_to_location IN VARCHAR2 DEFAULT NULL,
701 p_external_doc_ref IN VARCHAR2 DEFAULT NULL,
702 -- Bug 7109056. Added by Lakshmi Gopalsami
703 p_payment_method_code IN VARCHAR2 DEFAULT NULL
704 ) IS
705
706 lv_object_name VARCHAR2(61); -- := 'jai_ap_utils_pkg.insert_ap_inv_interface'; /* Added by Ramananda for bug#4407165 */
707
708
709 /* start additions by ssumaith - bug# 4448789 */
710 ln_legal_entity_id NUMBER;
711 lv_legal_entity_name VARCHAR2(240);
712 lv_return_status VARCHAR2(100);
713 ln_msg_count NUMBER;
714 ln_msg_data VARCHAR2(1000);
715 /* ends additions by ssumaith - bug# 4448789*/
716
717
718
719
720
721 BEGIN
722 -- #****************************************************************************************************************************************************************************************
723 -- #
724 -- # Change History -
725 -- # 1. 27-Jan-2005 Sanjikum for Bug #4059774 Version #115.0
726 -- # New Package created for creating AP Invoice Header and lines
727 -- #
728 -- # 2. 17-Feb-2005 Sanjikum for Bug #4183001 Version #115.1
729 -- #
730 -- # Issue -
731 -- # In Base version 11.5.3, 3 columns are not present in tables ap_invoices_interface and insert_ap_inv_lines_interface
732 -- #
733 -- # Fix -
734 -- # a) In the Definition of Procedure insert_ap_inv_interface, changed the type of 3 parameters -
735 -- # p_requester_id, p_ship_to_location, p_external_doc_ref
736 -- # b) In the Insert statement in procedure insert_ap_inv_interface, commented the insert for 3 columns -
737 -- # requester_id, ship_to_location, external_doc_ref
738 -- # c) In the Definition of Procedure insert_ap_inv_lines_interface, changed the type of 3 parameters -
739 -- # p_taxable_flag, p_price_correct_inv_num, p_external_doc_line_ref
740 -- # d) In the Insert statement in procedure insert_ap_inv_lines_interface, commented the insert for 3 columns -
741 -- # taxable_flag, price_correct_inv_num, external_doc_line_ref
742 -- #
743 -- # 3. 25-Mar-2005 Sanjikum for Bug #3841637 Version 115.4
744 -- # Added the Attribute category and 15 attributes columns
745 -- #
746 -- # Future Dependencies For the release Of this Object:-
747 -- # (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
748 -- # A datamodel change )
749 --==============================================================================================================
750 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
751 -- # Current Version Current Bug Dependent Files Version Author Date Remarks
752 -- # Of File On Bug/Patchset Dependent On
753 -- # jai_ap_interface_pkg_b.sql
754 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
755 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
756 -- # ****************************************************************************************************************************************************************************************
757
758 lv_object_name := 'jai_ap_utils_pkg.insert_ap_inv_interface'; /* Added by Ramananda for bug#4407165 */
759
760
761 /* start additions by ssumaith - bug# 4448789 */
762 jai_cmn_utils_pkg.GET_LE_INFO(
763 P_API_VERSION => NULL ,
764 P_INIT_MSG_LIST => NULL ,
765 P_COMMIT => NULL ,
769 X_RETURN_STATUS => lv_return_status ,
766 P_LEDGER_ID => NULL,
767 P_BSV => NULL,
768 P_ORG_ID => p_org_id,
770 X_MSG_COUNT => ln_msg_count,
771 X_MSG_DATA => ln_msg_data,
772 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
773 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
774 );
775 /* ends additions by ssumaith - bug# 4448789*/
776
777
778 INSERT INTO ap_invoices_interface(
779 INVOICE_ID,
780 INVOICE_NUM,
781 INVOICE_TYPE_LOOKUP_CODE,
782 INVOICE_DATE,
783 PO_NUMBER,
784 VENDOR_ID,
785 VENDOR_NUM,
786 VENDOR_NAME,
787 VENDOR_SITE_ID,
788 VENDOR_SITE_CODE,
789 INVOICE_AMOUNT,
790 INVOICE_CURRENCY_CODE,
791 EXCHANGE_RATE,
792 EXCHANGE_RATE_TYPE,
793 EXCHANGE_DATE,
794 TERMS_ID,
795 TERMS_NAME,
796 DESCRIPTION,
797 AWT_GROUP_ID,
798 AWT_GROUP_NAME,
799 LAST_UPDATE_DATE,
800 LAST_UPDATED_BY,
801 LAST_UPDATE_LOGIN,
802 CREATION_DATE,
803 CREATED_BY,
804 --Added below the attribute category and attribute columns for Bug #3841637
805 ATTRIBUTE_CATEGORY,
806 ATTRIBUTE1,
807 ATTRIBUTE2,
808 ATTRIBUTE3,
809 ATTRIBUTE4,
810 ATTRIBUTE5,
811 ATTRIBUTE6,
812 ATTRIBUTE7,
813 ATTRIBUTE8,
814 ATTRIBUTE9,
815 ATTRIBUTE10,
816 ATTRIBUTE11,
817 ATTRIBUTE12,
818 ATTRIBUTE13,
819 ATTRIBUTE14,
820 ATTRIBUTE15,
821 STATUS,
822 SOURCE,
823 GROUP_ID,
824 REQUEST_ID,
825 PAYMENT_CROSS_RATE_TYPE,
826 PAYMENT_CROSS_RATE_DATE,
827 PAYMENT_CROSS_RATE,
828 PAYMENT_CURRENCY_CODE,
829 WORKFLOW_FLAG,
830 DOC_CATEGORY_CODE,
831 -- VOUCHER_NUM, Harshita for Bug 4870243
832 PAYMENT_METHOD_CODE, -- Bug 7109056. added by Lakshmi gopalsami
833 PAY_GROUP_LOOKUP_CODE,
834 GOODS_RECEIVED_DATE,
835 INVOICE_RECEIVED_DATE,
836 GL_DATE,
837 ACCTS_PAY_CODE_COMBINATION_ID,
838 USSGL_TRANSACTION_CODE,
839 EXCLUSIVE_PAYMENT_FLAG,
840 ORG_ID,
841 LEGAL_ENTITY_ID , /* added by ssumaith - bug# 4448789*/
842 AMOUNT_APPLICABLE_TO_DISCOUNT,
843 PREPAY_NUM,
844 PREPAY_DIST_NUM,
845 PREPAY_APPLY_AMOUNT,
846 PREPAY_GL_DATE
847 /* , Bug4240179. Added by LGOPALSA
848 Commented the following 4 fields*/
849 --INVOICE_INCLUDES_PREPAY_FLAG,
850 --NO_XRATE_BASE_AMOUNT,
851 --VENDOR_EMAIL_ADDRESS,
852 --TERMS_DATE
853 /*,
854 REQUESTER_ID,
855 SHIP_TO_LOCATION,
856 EXTERNAL_DOC_REF*/)--commented by Sanjikum for Bug#4183001
857 VALUES(
858 ap_invoices_interface_s.NEXTVAL,
859 p_invoice_num,
860 p_invoice_type_lookup_code,
861 p_invoice_date,
862 p_po_number,
863 p_vendor_id,
864 p_vendor_num,
865 p_vendor_name,
866 p_vendor_site_id,
867 p_vendor_site_code,
868 p_invoice_amount,
869 p_invoice_currency_code,
870 p_exchange_rate,
871 p_exchange_rate_type,
872 p_exchange_date,
873 p_terms_id,
874 p_terms_name,
875 p_description,
876 p_awt_group_id,
877 p_awt_group_name,
878 p_last_update_date,
879 p_last_updated_by,
880 p_last_update_login,
881 p_creation_date,
882 p_created_by,
883 --Added below the attribute category and attribute columns for Bug #3841637
884 p_attribute_category,
885 p_attribute1,
886 p_attribute2,
887 p_attribute3,
888 p_attribute4,
889 p_attribute5,
890 p_attribute6,
891 p_attribute7,
892 p_attribute8,
893 p_attribute9,
894 p_attribute10,
895 p_attribute11,
896 p_attribute12,
897 p_attribute13,
898 p_attribute14,
902 p_group_id,
899 p_attribute15,
900 p_status,
901 p_source,
903 p_request_id,
904 p_payment_cross_rate_type,
905 p_payment_cross_rate_date,
906 p_payment_cross_rate,
907 p_payment_currency_code,
908 p_workflow_flag,
909 p_doc_category_code,
910 -- p_voucher_num, Harshita for Bug 4870243
911 p_payment_method_code, -- Bug 7109056. Added by Lakshmi Gopalsami
912 p_pay_group_lookup_code,
913 p_goods_received_date,
914 p_invoice_received_date,
915 p_gl_date,
916 p_accts_pay_ccid,
917 p_ussgl_transaction_code,
918 p_exclusive_payment_flag,
919 p_org_id,
920 ln_legal_entity_id , /* added by ssumaith - bug# 4448789*/
921 p_amount_applicable_to_dis,
922 p_prepay_num,
923 p_prepay_dist_num,
924 p_prepay_apply_amount,
925 p_prepay_gl_date
926 /* , Bug4240179. Added by LGOPALSA
927 Commented the following 4 fields*/
928 --p_invoice_includes_prepay_flag,
929 --p_no_xrate_base_amount,
930 --p_vendor_email_address,
931 --p_terms_date
932 /*,
933 p_requester_id,
934 p_ship_to_location,
935 p_external_doc_ref*/) --commented by Sanjikum for Bug#4183001
936 RETURNING invoice_id INTO p_invoice_id;
937
938 /* Added by Ramananda for bug#4407165 */
939 EXCEPTION
940 WHEN OTHERS THEN
941 p_invoice_id := null;
942 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
943 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
944 app_exception.raise_exception;
945
946 END insert_ap_inv_interface;
947
948 PROCEDURE insert_ap_inv_lines_interface(
949 p_jai_source IN VARCHAR2,
950 p_invoice_id IN ap_invoice_lines_interface.INVOICE_ID%TYPE,
951 p_invoice_line_id OUT NOCOPY ap_invoice_lines_interface.INVOICE_LINE_ID%TYPE,
952 p_line_number IN ap_invoice_lines_interface.LINE_NUMBER%TYPE DEFAULT NULL,
953 p_line_type_lookup_code IN ap_invoice_lines_interface.LINE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
954 p_line_group_number IN ap_invoice_lines_interface.LINE_GROUP_NUMBER%TYPE DEFAULT NULL,
955 p_amount IN ap_invoice_lines_interface.AMOUNT%TYPE DEFAULT NULL,
956 p_accounting_date IN ap_invoice_lines_interface.ACCOUNTING_DATE%TYPE DEFAULT NULL,
957 p_description IN ap_invoice_lines_interface.DESCRIPTION%TYPE DEFAULT NULL,
958 p_amount_includes_tax_flag IN ap_invoice_lines_interface.AMOUNT_INCLUDES_TAX_FLAG%TYPE DEFAULT NULL,
959 p_prorate_across_flag IN ap_invoice_lines_interface.PRORATE_ACROSS_FLAG%TYPE DEFAULT NULL,
960 p_tax_code IN ap_invoice_lines_interface.TAX_CODE%TYPE DEFAULT NULL,
961 p_final_match_flag IN ap_invoice_lines_interface.FINAL_MATCH_FLAG%TYPE DEFAULT NULL,
962 p_po_header_id IN ap_invoice_lines_interface.PO_HEADER_ID%TYPE DEFAULT NULL,
963 p_po_number IN ap_invoice_lines_interface.PO_NUMBER%TYPE DEFAULT NULL,
964 p_po_line_id IN ap_invoice_lines_interface.PO_LINE_ID%TYPE DEFAULT NULL,
965 p_po_line_number IN ap_invoice_lines_interface.PO_LINE_NUMBER%TYPE DEFAULT NULL,
966 p_po_line_location_id IN ap_invoice_lines_interface.PO_LINE_LOCATION_ID%TYPE DEFAULT NULL,
967 p_po_shipment_num IN ap_invoice_lines_interface.PO_SHIPMENT_NUM%TYPE DEFAULT NULL,
968 p_po_distribution_id IN ap_invoice_lines_interface.PO_DISTRIBUTION_ID%TYPE DEFAULT NULL,
969 p_po_distribution_num IN ap_invoice_lines_interface.PO_DISTRIBUTION_NUM%TYPE DEFAULT NULL,
970 p_po_unit_of_measure IN ap_invoice_lines_interface.PO_UNIT_OF_MEASURE%TYPE DEFAULT NULL,
971 p_inventory_item_id IN ap_invoice_lines_interface.INVENTORY_ITEM_ID%TYPE DEFAULT NULL,
972 p_item_description IN ap_invoice_lines_interface.ITEM_DESCRIPTION%TYPE DEFAULT NULL,
973 p_quantity_invoiced IN ap_invoice_lines_interface.QUANTITY_INVOICED%TYPE DEFAULT NULL,
974 p_ship_to_location_code IN ap_invoice_lines_interface.SHIP_TO_LOCATION_CODE%TYPE DEFAULT NULL,
975 p_unit_price IN ap_invoice_lines_interface.UNIT_PRICE%TYPE DEFAULT NULL,
976 p_distribution_set_id IN ap_invoice_lines_interface.DISTRIBUTION_SET_ID%TYPE DEFAULT NULL,
977 p_distribution_set_name IN ap_invoice_lines_interface.DISTRIBUTION_SET_NAME%TYPE DEFAULT NULL,
978 p_dist_code_concatenated IN ap_invoice_lines_interface.DIST_CODE_CONCATENATED%TYPE DEFAULT NULL,
982 p_last_updated_by IN ap_invoice_lines_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
979 p_dist_code_combination_id IN ap_invoice_lines_interface.DIST_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
980 p_awt_group_id IN ap_invoice_lines_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
981 p_awt_group_name IN ap_invoice_lines_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
983 p_last_update_date IN ap_invoice_lines_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
984 p_last_update_login IN ap_invoice_lines_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
985 p_created_by IN ap_invoice_lines_interface.CREATED_BY%TYPE DEFAULT NULL,
986 p_creation_date IN ap_invoice_lines_interface.CREATION_DATE%TYPE DEFAULT NULL,
987 --Added below the attribute category and attribute parameters for Bug #3841637
988 p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
989 p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
990 p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
991 p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
992 p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
993 p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
994 p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
995 p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
996 p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
997 p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
998 p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
999 p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
1000 p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
1001 p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
1002 p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
1003 p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
1004 p_po_release_id IN ap_invoice_lines_interface.PO_RELEASE_ID%TYPE DEFAULT NULL,
1005 p_release_num IN ap_invoice_lines_interface.RELEASE_NUM%TYPE DEFAULT NULL,
1006 p_account_segment IN ap_invoice_lines_interface.ACCOUNT_SEGMENT%TYPE DEFAULT NULL,
1007 p_balancing_segment IN ap_invoice_lines_interface.BALANCING_SEGMENT%TYPE DEFAULT NULL,
1008 p_cost_center_segment IN ap_invoice_lines_interface.COST_CENTER_SEGMENT%TYPE DEFAULT NULL,
1009 p_project_id IN ap_invoice_lines_interface.PROJECT_ID%TYPE DEFAULT NULL,
1010 p_task_id IN ap_invoice_lines_interface.TASK_ID%TYPE DEFAULT NULL,
1011 p_expenditure_type IN ap_invoice_lines_interface.EXPENDITURE_TYPE%TYPE DEFAULT NULL,
1012 p_expenditure_item_date IN ap_invoice_lines_interface.EXPENDITURE_ITEM_DATE%TYPE DEFAULT NULL,
1013 p_expenditure_organization_id IN ap_invoice_lines_interface.EXPENDITURE_ORGANIZATION_ID%TYPE DEFAULT NULL,
1014 p_project_accounting_context IN ap_invoice_lines_interface.PROJECT_ACCOUNTING_CONTEXT%TYPE DEFAULT NULL,
1015 p_pa_addition_flag IN ap_invoice_lines_interface.PA_ADDITION_FLAG%TYPE DEFAULT NULL,
1016 p_pa_quantity IN ap_invoice_lines_interface.PA_QUANTITY%TYPE DEFAULT NULL,
1017 p_ussgl_transaction_code IN ap_invoice_lines_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
1018 p_stat_amount IN ap_invoice_lines_interface.STAT_AMOUNT%TYPE DEFAULT NULL,
1019 p_type_1099 IN ap_invoice_lines_interface.TYPE_1099%TYPE DEFAULT NULL,
1020 p_income_tax_region IN ap_invoice_lines_interface.INCOME_TAX_REGION%TYPE DEFAULT NULL,
1021 p_assets_tracking_flag IN ap_invoice_lines_interface.ASSETS_TRACKING_FLAG%TYPE DEFAULT NULL,
1022 p_price_correction_flag IN ap_invoice_lines_interface.PRICE_CORRECTION_FLAG%TYPE DEFAULT NULL,
1023 p_org_id IN ap_invoice_lines_interface.ORG_ID%TYPE DEFAULT NULL,
1024 p_receipt_number IN ap_invoice_lines_interface.RECEIPT_NUMBER%TYPE DEFAULT NULL,
1025 p_receipt_line_number IN ap_invoice_lines_interface.RECEIPT_LINE_NUMBER%TYPE DEFAULT NULL,
1026 p_match_option IN ap_invoice_lines_interface.MATCH_OPTION%TYPE DEFAULT NULL,
1027 p_packing_slip IN ap_invoice_lines_interface.PACKING_SLIP%TYPE DEFAULT NULL,
1028 p_rcv_transaction_id IN ap_invoice_lines_interface.RCV_TRANSACTION_ID%TYPE DEFAULT NULL,
1029 p_pa_cc_ar_invoice_id IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_ID%TYPE DEFAULT NULL,
1030 p_pa_cc_ar_invoice_line_num IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_LINE_NUM%TYPE DEFAULT NULL,
1034 p_tax_recovery_rate IN ap_invoice_lines_interface.TAX_RECOVERY_RATE%TYPE DEFAULT NULL,
1031 p_reference_1 IN ap_invoice_lines_interface.REFERENCE_1%TYPE DEFAULT NULL,
1032 p_reference_2 IN ap_invoice_lines_interface.REFERENCE_2%TYPE DEFAULT NULL,
1033 p_pa_cc_processed_code IN ap_invoice_lines_interface.PA_CC_PROCESSED_CODE%TYPE DEFAULT NULL,
1035 p_tax_recovery_override_flag IN ap_invoice_lines_interface.TAX_RECOVERY_OVERRIDE_FLAG%TYPE DEFAULT NULL,
1036 p_tax_recoverable_flag IN ap_invoice_lines_interface.TAX_RECOVERABLE_FLAG%TYPE DEFAULT NULL,
1037 p_tax_code_override_flag IN ap_invoice_lines_interface.TAX_CODE_OVERRIDE_FLAG%TYPE DEFAULT NULL,
1038 p_tax_code_id IN ap_invoice_lines_interface.TAX_CODE_ID%TYPE DEFAULT NULL,
1039 p_credit_card_trx_id IN ap_invoice_lines_interface.CREDIT_CARD_TRX_ID%TYPE DEFAULT NULL,
1040 -- Bug 4240179. Changed data for vendor_item_num and award_id
1041 -- Added by LGOPALSA
1042 p_award_id IN NUMBER DEFAULT NULL,
1043 p_vendor_item_num IN VARCHAR2 DEFAULT NULL,
1044 p_taxable_flag IN VARCHAR2 DEFAULT NULL,
1045 p_price_correct_inv_num IN VARCHAR2 DEFAULT NULL,
1046 p_external_doc_line_ref IN VARCHAR2 DEFAULT NULL)
1047 IS
1048 lv_object_name VARCHAR2(61);
1049 BEGIN
1050
1051 lv_object_name := 'jai_ap_utils_pkg.insert_ap_inv_lines_interface'; /* Added by Ramananda for bug#4407165 */
1052
1053 INSERT INTO ap_invoice_lines_interface(
1054 INVOICE_ID,
1055 INVOICE_LINE_ID,
1056 LINE_NUMBER,
1057 LINE_TYPE_LOOKUP_CODE,
1058 LINE_GROUP_NUMBER,
1059 AMOUNT,
1060 ACCOUNTING_DATE,
1061 DESCRIPTION,
1062 AMOUNT_INCLUDES_TAX_FLAG,
1063 PRORATE_ACROSS_FLAG,
1064 TAX_CODE,
1065 FINAL_MATCH_FLAG,
1066 PO_HEADER_ID,
1067 PO_NUMBER,
1068 PO_LINE_ID,
1069 PO_LINE_NUMBER,
1070 PO_LINE_LOCATION_ID,
1071 PO_SHIPMENT_NUM,
1072 PO_DISTRIBUTION_ID,
1073 PO_DISTRIBUTION_NUM,
1074 PO_UNIT_OF_MEASURE,
1075 INVENTORY_ITEM_ID,
1076 ITEM_DESCRIPTION,
1077 QUANTITY_INVOICED,
1078 SHIP_TO_LOCATION_CODE,
1079 UNIT_PRICE,
1080 DISTRIBUTION_SET_ID,
1081 DISTRIBUTION_SET_NAME,
1082 DIST_CODE_CONCATENATED,
1083 DIST_CODE_COMBINATION_ID,
1084 AWT_GROUP_ID,
1085 AWT_GROUP_NAME,
1086 LAST_UPDATED_BY,
1087 LAST_UPDATE_DATE,
1088 LAST_UPDATE_LOGIN,
1089 CREATED_BY,
1090 CREATION_DATE,
1091 --Added below the attribute category and attribute columns for Bug #3841637
1092 ATTRIBUTE_CATEGORY,
1093 ATTRIBUTE1,
1094 ATTRIBUTE2,
1095 ATTRIBUTE3,
1096 ATTRIBUTE4,
1097 ATTRIBUTE5,
1098 ATTRIBUTE6,
1099 ATTRIBUTE7,
1100 ATTRIBUTE8,
1101 ATTRIBUTE9,
1102 ATTRIBUTE10,
1103 ATTRIBUTE11,
1104 ATTRIBUTE12,
1105 ATTRIBUTE13,
1106 ATTRIBUTE14,
1107 ATTRIBUTE15,
1108 PO_RELEASE_ID,
1109 RELEASE_NUM,
1110 ACCOUNT_SEGMENT,
1111 BALANCING_SEGMENT,
1112 COST_CENTER_SEGMENT,
1113 PROJECT_ID,
1114 TASK_ID,
1115 EXPENDITURE_TYPE,
1116 EXPENDITURE_ITEM_DATE,
1117 EXPENDITURE_ORGANIZATION_ID,
1118 PROJECT_ACCOUNTING_CONTEXT,
1119 PA_ADDITION_FLAG,
1120 PA_QUANTITY,
1121 USSGL_TRANSACTION_CODE,
1122 STAT_AMOUNT,
1123 TYPE_1099,
1124 INCOME_TAX_REGION,
1125 ASSETS_TRACKING_FLAG,
1126 PRICE_CORRECTION_FLAG,
1127 ORG_ID,
1128 RECEIPT_NUMBER,
1129 RECEIPT_LINE_NUMBER,
1130 MATCH_OPTION,
1131 PACKING_SLIP,
1132 RCV_TRANSACTION_ID,
1133 PA_CC_AR_INVOICE_ID,
1134 PA_CC_AR_INVOICE_LINE_NUM,
1135 REFERENCE_1,
1136 REFERENCE_2,
1137 PA_CC_PROCESSED_CODE,
1138 TAX_RECOVERY_RATE,
1139 TAX_RECOVERY_OVERRIDE_FLAG,
1140 TAX_RECOVERABLE_FLAG,
1141 TAX_CODE_OVERRIDE_FLAG,
1142 TAX_CODE_ID,
1143 CREDIT_CARD_TRX_ID
1144 --, Bug 4240179. Commented by LGOPALSA
1145 -- AWARD_ID,
1149 PRICE_CORRECT_INV_NUM,
1146 -- VENDOR_ITEM_NUM
1147 /*,
1148 TAXABLE_FLAG,
1150 EXTERNAL_DOC_LINE_REF*/)--commented by Sanjikum for Bug#4183001
1151 VALUES(
1152 p_invoice_id,
1153 ap_invoice_lines_interface_s.NEXTVAL,
1154 p_line_number,
1155 p_line_type_lookup_code,
1156 p_line_group_number,
1157 p_amount,
1158 p_accounting_date,
1159 p_description,
1160 p_amount_includes_tax_flag,
1161 p_prorate_across_flag,
1162 p_tax_code,
1163 p_final_match_flag,
1164 p_po_header_id,
1165 p_po_number,
1166 p_po_line_id,
1167 p_po_line_number,
1168 p_po_line_location_id,
1169 p_po_shipment_num,
1170 p_po_distribution_id,
1171 p_po_distribution_num,
1172 p_po_unit_of_measure,
1173 p_inventory_item_id,
1174 p_item_description,
1175 p_quantity_invoiced,
1176 p_ship_to_location_code,
1177 p_unit_price,
1178 p_distribution_set_id,
1179 p_distribution_set_name,
1180 p_dist_code_concatenated,
1181 p_dist_code_combination_id,
1182 p_awt_group_id,
1183 p_awt_group_name,
1184 p_last_updated_by,
1185 p_last_update_date,
1186 p_last_update_login,
1187 p_created_by,
1188 p_creation_date,
1189 --Added below the attribute category and attribute columns for Bug #3841637
1190 p_attribute_category,
1191 p_attribute1,
1192 p_attribute2,
1193 p_attribute3,
1194 p_attribute4,
1195 p_attribute5,
1196 p_attribute6,
1197 p_attribute7,
1198 p_attribute8,
1199 p_attribute9,
1200 p_attribute10,
1201 p_attribute11,
1202 p_attribute12,
1203 p_attribute13,
1204 p_attribute14,
1205 p_attribute15,
1206 p_po_release_id,
1207 p_release_num,
1208 p_account_segment,
1209 p_balancing_segment,
1210 p_cost_center_segment,
1211 p_project_id,
1212 p_task_id,
1213 p_expenditure_type,
1214 p_expenditure_item_date,
1215 p_expenditure_organization_id,
1216 p_project_accounting_context,
1217 p_pa_addition_flag,
1218 p_pa_quantity,
1219 p_ussgl_transaction_code,
1220 p_stat_amount,
1221 p_type_1099,
1222 p_income_tax_region,
1223 p_assets_tracking_flag,
1224 p_price_correction_flag,
1225 p_org_id,
1226 p_receipt_number,
1227 p_receipt_line_number,
1228 p_match_option,
1229 p_packing_slip,
1230 p_rcv_transaction_id,
1231 p_pa_cc_ar_invoice_id,
1232 p_pa_cc_ar_invoice_line_num,
1233 p_reference_1,
1234 p_reference_2,
1235 p_pa_cc_processed_code,
1236 p_tax_recovery_rate,
1237 p_tax_recovery_override_flag,
1238 p_tax_recoverable_flag,
1239 p_tax_code_override_flag,
1240 p_tax_code_id,
1241 p_credit_card_trx_id
1242 --, Bug 4240179. Commented by LGOPALSA
1243 --p_award_id,
1244 --p_vendor_item_num
1245 /*,
1246 p_taxable_flag,
1247 p_price_correct_inv_num,
1248 p_external_doc_line_ref*/)--commented by Sanjikum for Bug#4183001
1249 RETURNING invoice_line_id INTO p_invoice_line_id;
1250
1251 /* Added by Ramananda for bug#4407165 */
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 p_invoice_line_id := null;
1255 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1256 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1257 app_exception.raise_exception;
1258
1259 END insert_ap_inv_lines_interface;
1260
1261 /* Brathod, For Bug# 4445989, get_apportion_factor signature is modified to use invoice_id and line_number*/
1262 FUNCTION get_apportion_factor(
1263 -- p_invoice_distribution_id in number
1264 pn_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE
1265 , pn_invoice_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE,
1266 --added the following parameter by vkaranam for bug #5989740
1267 p_factor_type varchar2 default null
1268 ) return number
1269 is
1270 /* Modified cursor to use ap_invoice_lines_all for Bug# 4445989 */
1271 cursor c_get_inv_details is
1275 AND line_number = pn_invoice_line_number;
1272 select quantity_invoiced, unit_price, po_distribution_id, rcv_transaction_id,invoice_id
1273 from ap_invoice_lines_all
1274 where invoice_id = pn_invoice_id
1276
1277 cursor c_get_uoms_po_receipt(p_rcv_transaction_id number) is
1278 select unit_of_measure receipt_uom,
1279 source_doc_unit_of_measure po_uom,
1280 po_unit_price /*Bug 4990941 bduvarag*/
1281 from rcv_transactions
1282 where transaction_id = p_rcv_transaction_id;
1283
1284 cursor c_get_po_qty_price(p_po_distribution_id number) is
1285 select price_override, quantity
1286 from po_line_locations_all
1287 where (po_header_id, po_line_id, line_location_id ) in
1288 (
1289 select po_header_id, po_line_id, line_location_id
1290 from po_distributions_all
1291 where po_distribution_id = p_po_distribution_id
1292 );
1293
1294 cursor c_get_receipt_qty(p_rcv_transaction_id number) is
1295 select qty_received
1296 from JAI_RCV_LINES
1297 where (shipment_header_id, shipment_line_id)
1298 in
1299 (
1300 select shipment_header_id, shipment_line_id
1301 from rcv_transactions
1302 where transaction_id = p_rcv_transaction_id
1303 );
1304
1305 cursor c_get_uom_code(p_unit_of_measure in varchar2) is
1306 select uom_code
1307 from mtl_units_of_measure
1308 where unit_of_measure = p_unit_of_measure;
1309
1310 cursor c_get_item (p_transaction_id number) is
1311 select item_id
1312 from rcv_shipment_lines
1313 where shipment_line_id = (select shipment_line_id
1314 from rcv_transactions
1315 where transaction_id = p_transaction_id);
1316
1317 -- iSupplier porting
1318 CURSOR c_inv(inv_id NUMBER) IS
1319 SELECT source
1320 FROM ap_invoices_all
1321 WHERE invoice_id = inv_id;
1322 -- iSupplier porting
1323
1324
1325 v_invoice_quantity ap_invoice_distributions_all.quantity_invoiced%type;
1326 v_invoice_price ap_invoice_distributions_all.unit_price%type;
1327 v_invoice_id ap_invoice_distributions_all.invoice_id%type; --iSuppleir porting
1328 v_source ap_invoices_all.source%type; --iSuppleir porting
1329
1330 v_po_uom rcv_transactions.source_doc_unit_of_measure%type;
1331 v_receipt_price rcv_transactions.po_unit_price%type ; /*Bug 4990941 bduvarag*/
1332 v_receipt_uom rcv_transactions.unit_of_measure%type;
1333
1334 v_receipt_quantity JAI_RCV_LINES.qty_received%type;
1335
1336 v_po_price po_line_locations_all.price_override%type;
1337 v_po_quantity po_line_locations_all.quantity%type;
1338
1339 v_po_distribution_id ap_invoice_distributions_all.po_distribution_id%type;
1340 v_rcv_transaction_id ap_invoice_distributions_all.rcv_transaction_id%type;
1341
1342 v_uom_conv_factor number;
1343
1344
1345 v_po_uom_code mtl_units_of_measure.uom_code%type;
1346 v_receipt_uom_code mtl_units_of_measure.uom_code%type;
1347
1348 v_item_id rcv_shipment_lines.item_id%type;
1349
1350 v_statement_id number:=0;
1351
1352
1353 begin
1354
1355 /* -----------------------------------------------------------------------------
1356 FILENAME: jai_ap_utils_pkg.get_apportion_factor.sql
1357 CHANGE HISTORY:
1358
1359 S.No Date Author and Details
1360 1 14/06/2004 Created by Aparajita for bug#3633078. Version#115.0
1361
1362 This function computes the factor by which tax on
1363 Receipt or PO should be apportioned to be taken over
1364 to Payable Invoice. This factor considers the changes in
1365 quantity, UOM and Price.
1366
1367 Quantity can be changed at every stage like,
1368 between PO and Receipt, Receipt and Invoice, and PO and
1369 Invoice also.
1370
1371 UOM can be changed between PO and Receipt only.
1372
1373 Price can be changed between PO and Invoice.
1374
1375 Invoice can refer to either a Receipt / PO.
1376
1377 Only apportion not handled here is the currency of tax
1378 and invoice and apportionment if required by exchange rate.
1379 This is so because, that would depend on each tax and current
1380 apportion factor is for all taxes attached to a line.
1381
1382
1383
1384 Future Dependencies For the release Of this Object:-
1385 ==================================================
1386 Please add a row in the section below only if your bug introduces a dependency
1387 like,spec change/ A new call to a object/A datamodel change.
1388
1389 --------------------------------------------------------------------------------
1390 Version Bug Dependencies (including other objects like files if any)
1391 --------------------------------------------------------------------------------
1392
1393
1397 OPEN c_inv(pn_invoice_id);
1394 --------------------------------------------------------------------------------- */
1395 -- Added by Jason Liu for retroactive price on 2008/01/24
1396 ----------------------------------------------------------------------
1398 FETCH c_inv INTO v_source;
1399 CLOSE c_inv;
1400
1401 --Comment out by Kevin Cheng for bug#6962018 Apr 14, 2008
1402 /*IF(v_source = 'PPA')
1403 THEN
1404 RETURN 1;
1405 END IF; --(v_source = 'PPA') */
1406 ----------------------------------------------------------------------
1407
1408 v_statement_id := 1;
1409 open c_get_inv_details;
1410 fetch c_get_inv_details into
1411 v_invoice_quantity, v_invoice_price, v_po_distribution_id, v_rcv_transaction_id, v_invoice_id;
1412 close c_get_inv_details;
1413
1414 v_statement_id := 2;
1415 open c_get_po_qty_price(v_po_distribution_id);
1416 fetch c_get_po_qty_price into v_po_price, v_po_quantity;
1417 close c_get_po_qty_price;
1418
1419 v_statement_id := 3;
1420
1421 if v_rcv_transaction_id is not null then
1422
1423 v_statement_id := 4;
1424 open c_get_uoms_po_receipt(v_rcv_transaction_id);
1425 fetch c_get_uoms_po_receipt into v_receipt_uom, v_po_uom,v_receipt_price;/*bug 4990941 bduvarag*/
1426 close c_get_uoms_po_receipt;
1427
1428 open c_get_receipt_qty(v_rcv_transaction_id);
1429 fetch c_get_receipt_qty into v_receipt_quantity;
1430 close c_get_receipt_qty;
1431
1432 if v_receipt_uom = v_po_uom then
1433 v_statement_id := 5;
1434 v_uom_conv_factor := 1;
1435 else
1436 v_statement_id := 6;
1437 open c_get_uom_code(v_receipt_uom);
1438 fetch c_get_uom_code into v_receipt_uom_code;
1439 close c_get_uom_code;
1440
1441 open c_get_uom_code(v_po_uom);
1442 fetch c_get_uom_code into v_po_uom_code;
1443 close c_get_uom_code;
1444
1445 open c_get_item(v_rcv_transaction_id);
1446 fetch c_get_item into v_item_id;
1447 close c_get_item;
1448
1449 v_statement_id := 7;
1450 Inv_Convert.Inv_Um_Conversion
1451 (
1452 v_receipt_uom_code,
1453 v_po_uom_code,
1454 v_item_id,
1455 v_uom_conv_factor
1456 );
1457
1458 if nvl(v_uom_conv_factor, 0) <= 0 then
1459 v_uom_conv_factor := 1;
1460 end if;
1461
1462
1463 end if;-- v_receipt_uom = v_po_uom t
1464
1465 end if;-- v_rcv_transaction_id is not null
1466
1467
1468
1469 if v_rcv_transaction_id is null then
1470
1471 v_statement_id:=8;
1472
1473 --Add by Kevin Cheng for bug#6962018 Apr 14, 2008
1474 -------------------------------------------------
1475 IF(v_source = 'PPA')
1476 THEN
1477 RETURN v_invoice_quantity/v_po_quantity;
1478 END IF; --(v_source = 'PPA')
1479 -------------------------------------------------
1480
1481 --added the following if condition by vkaranam for bug #5989740
1482 if p_factor_type is null then
1483
1484 -- iSupplier porting
1485 open c_inv(v_invoice_id);
1486 fetch c_inv into v_source;
1487 close c_inv;
1488 -- iSupplier porting
1489
1490 if nvl(v_po_quantity , 0) = 0 or nvl(v_po_price, 0) = 0
1491 or v_source = 'ASBN' then
1492 return 1;
1493 end if;
1494
1495 return ( (v_invoice_quantity / v_po_quantity) * (v_invoice_price / v_po_price) );
1496 else
1497 -- Begin Bug# 5989740
1498 if p_factor_type = 'QUANTITY' then
1499
1500 if nvl(v_po_quantity , 0) = 0 then
1501
1502 return 1 ;
1503
1504 else
1505
1506 return (v_invoice_quantity / v_po_quantity);
1507
1508 end if;
1509
1510 elsif p_factor_type = 'PRICE' then
1511
1512 if nvl(v_po_price , 0) = 0 then
1513
1514 return 1 ;
1515
1516 else
1517
1518 return (v_invoice_price / v_po_price);
1519
1520 end if;
1521
1522 end if; --> p_factor_type = 'QUANTITY'
1523
1524 end if; --> p_factor_type is null
1525 -- End Bug# 5989740
1526
1527
1528 else
1529
1530 v_statement_id:=9;
1531
1532 --Add by Kevin Cheng for bug#6962018 Apr 14, 2008
1533 -------------------------------------------------
1534 IF(v_source = 'PPA')
1535 THEN
1536 RETURN v_invoice_quantity/v_receipt_quantity;
1537 END IF; --(v_source = 'PPA')
1538 -------------------------------------------------
1539
1540 if p_factor_type is null then --bug 5989740
1541 if nvl(v_receipt_quantity, 0) = 0 or nvl(v_po_price, 0) = 0
1542 or nvl(v_uom_conv_factor, 0) = 0 then
1543
1544 return 1;
1545
1546 end if;
1547 /*Bug 4990941 bduvarag*/
1548 return ( (v_invoice_quantity / v_receipt_quantity) *
1549 (v_invoice_price / NVL(v_receipt_price,v_po_price)) *
1550 (1/ v_uom_conv_factor)
1554 if p_factor_type = 'QUANTITY' then
1551 );
1552 else -- Begin Bug# 5989740
1553
1555
1556 if nvl(v_receipt_quantity, 0) = 0 or nvl(v_uom_conv_factor, 0) = 0 then
1557
1558 return 1;
1559 else
1560
1561 return (v_invoice_quantity / v_receipt_quantity) * (1/ v_uom_conv_factor);
1562
1563 end if;
1564
1565 elsif p_factor_type = 'PRICE' then
1566
1567 if nvl(v_po_price, 0) = 0 or v_receipt_price = 0 then
1568
1569 return 1;
1570
1571 else
1572
1573 return (v_invoice_price / NVL(v_receipt_price,v_po_price));
1574
1575 end if;
1576
1577 end if; --> p_factor_type = 'QUANTITY'
1578
1579 end if; --> p_factor_type is null
1580
1581 end if; --> v_rcv_transaction_id
1582 -- End Bug# 5989740
1583
1584
1585
1586
1587 end get_apportion_factor;
1588
1589 PROCEDURE submit_pla_boe_for_approval
1590 (
1591 ERRBUF OUT NOCOPY VARCHAR2,
1592 RETCODE OUT NOCOPY VARCHAR2,
1593 p_boe_id In VARCHAR2,
1594 p_set_of_books_id In Number,
1595 p_prv_req_id In Number,
1596 p_vendor_id In Number
1597 )
1598 is
1599 request_id Number;
1600 result Boolean;
1601 v_invoice_id NUmber;
1602 req_status Boolean := TRUE;
1603 v_phase Varchar2(100);
1604 v_status Varchar2(100);
1605 v_dev_phase Varchar2(100);
1606 v_dev_status Varchar2(100);
1607 v_message Varchar2(100);
1608 v_prv_req_id Number;
1609
1610 /*-------------------------------------------------------------------------------------------------------------------
1611 FILENAME: Ja_In_Auto_Invoice_p.sql
1612
1613 CHANGE HISTORY:
1614 S.No Date Author and Details
1615 ------------------------------------------
1616 1. 29/10/2002 Aparajita for bug # 2645196
1617 When the parent request for importing fails then this request for approval of
1618 PLA/BOE invoices should error out. While polling the status of teh parent request there
1619 should be a delay of 60 seconds between polling.
1620
1621 Also added exception handling to the main procedure and to the sql that fetches
1622 from ap_invoices_all.
1623
1624 Since the procedure was revamped with the new approach, deleted the old code.
1625 --------------------------------------------------------------------------------------------------------------------*/
1626 Begin
1627 v_prv_req_id := p_prv_req_id;
1628
1629 -- start added by Aparajita on 29/10/2002 for bug # 2645196
1630 req_status := Fnd_concurrent.wait_for_request( v_prv_req_id,
1631 60, -- default value - sleep time in secs
1632 0, -- default value - max wait in secs
1633 v_phase,
1634 v_status,
1635 v_dev_phase,
1636 v_dev_status,
1637 v_message );
1638
1639 IF v_dev_phase = 'COMPLETE' THEN
1640
1641 IF v_dev_status <> 'NORMAL' THEN
1642
1643 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
1644 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || v_message);
1645 retcode := 1;
1646 errbuf := 'Exiting with warningr as parent request not completed with normal status';
1647 RETURN;
1648
1649 END IF;
1650
1651 END IF;
1652
1653 -- end added by Aparajita on 29/10/2002 for bug # 2645196
1654
1655 IF v_dev_phase = 'COMPLETE' or v_dev_phase = 'INACTIVE' Then
1656
1657 IF v_dev_status = 'NORMAL' Then
1658
1659 begin
1660
1661 Select invoice_id
1662 into v_invoice_id
1663 from ap_invoices_all
1664 Where invoice_num = p_boe_id
1665 And vendor_id = p_vendor_id;
1666
1667 result := Fnd_request.set_mode(TRUE);
1668 /* Bug 5378544. Added by Lakshmi Gopalsami
1669 * Included org_id and commit size.
1670 */
1671 request_id := FND_REQUEST.SUBMIT_REQUEST
1672 (
1673 'SQLAP',
1674 'APPRVL',
1675 'Payables Approval Localization',
1676 NULL,
1677 FALSE,
1678 '', -- org_id
1679 'All', '','','','','', to_char(v_invoice_id),
1680 '', to_char(p_set_of_books_id), 'N',
1681 '' ); -- commit size
1682 exception
1683 when no_data_found then
1684 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as the PLA/BOE invoice has not got imported ');
1685 Fnd_File.put_line(Fnd_File.LOG, 'PLA/BOE invoice num :' || p_boe_id );
1686 retcode := 1;
1687 errbuf := 'Exiting with warning as the PLA/BOE invoice to approve has not been imported ';
1688 RETURN;
1689 end;
1690
1691 End If;
1692
1693
1694 End If;
1695
1696 Fnd_File.put_line(Fnd_File.LOG, 'PLA/BOE invoice num :' || p_boe_id || ', approval request submitted ');
1697
1701 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
1698 exception
1699 when others then
1700 Fnd_File.put_line(Fnd_File.LOG, 'Exception encountered in procedure jai_ap_utils_pkg.submit_pla_boe_for_approval');
1702 retcode := 2;
1703 errbuf := SQLERRM;
1704 RETURN;
1705 End submit_pla_boe_for_approval;
1706
1707
1708 --As part OF R12 Inititive Inventory conversion the following code IS commented BY Ravi
1709
1710 /*FUNCTION get_opm_assessable_value(p_item_id number,p_qty number,p_exted_price number,P_Cust_Id Number Default 0 ) RETURN NUMBER IS
1711 Cursor C_Item_Dtl IS
1712 Select excise_calc_base -- , assessable_value (Commented as Assessable Value is picked by other conditions now )
1713 From JAI_OPM_ITM_MASTERS
1714 Where item_id = p_item_id;
1715
1716 ---Added For OPM Localization By A.Raina on 22-02-2000
1717 ---Code Added For Fetching the Assessable_value at the customer level
1718
1719 Cursor C_Price_list_id is
1720 Select Pricelist_Id
1721 From JAI_OPM_CUSTOMERS
1722 Where Cust_id = p_cust_id ;
1723
1724 Cursor C_Cust_Ass_Value ( p_Pricelist_Id In Number ) is
1725 Select a.Base_Price
1726 From Op_Prce_Itm a ,op_prce_eff b
1727 Where a.pricelist_id = b.pricelist_id
1728 And a.Pricelist_Id = p_Pricelist_id
1729 And a.Item_Id = p_item_id
1730 And sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate) ;
1731
1732 CURSOR C_item_Ass_Value IS
1733 Select assessable_value
1734 From JAI_OPM_ITM_MASTERS
1735 Where item_id = p_item_id;
1736
1737 v_pricelist_id Number;
1738 v_assessable_flag char(1) ;
1739 --End Addition
1740 l_assessable_val number;
1741 l_excise_cal varchar2(1);
1742 BEGIN
1743
1744 ---Added For OPM Localization By A.Raina on 22-02-2000
1745 ---Code Added For Fetching the Assessable_value at the customer level
1746
1747 OPEN C_Price_list_id ;
1748 FETCH C_Price_list_id into v_pricelist_id;
1749 CLOSE C_Price_list_id ;
1750
1751 l_assessable_val := Null ;
1752 IF v_pricelist_id is Not Null Then
1753 OPEN C_Cust_Ass_Value (v_pricelist_id ) ;
1754 FETCH C_Cust_Ass_Value into l_assessable_val ;
1755 CLOSE C_Cust_Ass_Value ;
1756 End If;
1757 IF l_assessable_val Is Null Then
1758 OPEN C_item_Ass_Value ;
1759 FETCH C_item_Ass_Value into l_assessable_val ;
1760 CLOSE C_item_Ass_Value ;
1761 End If;
1762
1763 ---End Addition
1764
1765 OPEN C_Item_Dtl;
1766 FETCH C_Item_Dtl INTO l_excise_cal ; -- l_assessable_val (Commented as Assessable Value is picked by other conditions now )
1767 CLOSE C_Item_Dtl ;
1768
1769 IF NVL(l_excise_cal,'N') = 'Y' THEN
1770 Return(l_assessable_val*p_qty);
1771 ELSE
1772 Return(p_exted_price);
1773 END IF;
1774 END get_opm_assessable_value;*/
1775
1776
1777 PROCEDURE Print_Log
1778 (
1779 P_debug IN VARCHAR2,
1780 P_string IN VARCHAR2
1781 ) IS
1782
1783 stemp VARCHAR2(1000);
1784 nlength NUMBER := 1;
1785
1786 BEGIN
1787
1788 IF (P_Debug = 'Y') THEN
1789 WHILE(length(P_string) >= nlength)
1790 LOOP
1791
1792 stemp := substrb(P_string, nlength, 80);
1793 fnd_file.put_line(FND_FILE.LOG, stemp);
1794 nlength := (nlength + 80);
1795
1796 END LOOP;
1797 END IF;
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801
1802 IF (SQLCODE <> -20001) THEN
1803 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1804 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1805 END IF;
1806 APP_EXCEPTION.RAISE_EXCEPTION;
1807
1808 END Print_log;
1809
1810
1811 Procedure pan_update ( P_errbuf OUT NOCOPY varchar2,
1812 P_return_code OUT NOCOPY varchar2,
1813 P_vendor_id IN PO_VENDORS.vendor_id%TYPE,
1814 P_old_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
1815 P_new_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
1816 P_debug_flag IN varchar2) is
1817
1818
1819 /* Cursor to lock the jai_ap_tds_thhold_grps */
1820
1821 Cursor C_lock_thhold_grps is
1822 select threshold_grp_id,
1823 vendor_id,
1824 org_tan_num,
1825 vendor_pan_num,
1826 section_type,
1827 section_code,
1828 fin_year,
1829 total_invoice_amount,
1830 total_invoice_cancel_amount,
1831 total_invoice_apply_amount,
1832 total_invoice_unapply_amount,
1833 total_tax_paid,
1834 total_thhold_change_tax_paid,
1835 current_threshold_slab_id,
1836 created_by,
1837 creation_date,
1838 last_updated_by,
1839 last_update_date,
1840 last_update_login
1841 from jai_ap_tds_thhold_grps
1842 where vendor_id = P_vendor_id
1843 and vendor_pan_num = p_old_pan_num
1844 order by vendor_id,threshold_grp_id
1845 for UPDATE of threshold_grp_id NOWAIT;
1846
1847
1848
1849 /* Update the tables in the following order
1850
1851 (1) JAI_AP_TDS_VENDOR_HDRS
1852 (2) jai_ap_tds_thhold_grps
1853 (3) jai_ap_tds_thhold_xceps
1854
1855 */
1856
1860 ln_request_id number;
1857 lv_vendor_site_id_updated varchar2(1000) ;
1858 lv_thhold_grp_id_updated varchar2(1000) ;
1859 lv_thhold_xcep_id_updated varchar2(1000) ;
1861 lv_debug_flag varchar2(30);
1862 lv_debug_msg varchar2(4000) ;
1863
1864
1865 begin
1866
1867 lv_debug_flag := nvl(p_debug_flag, 'N');
1868
1869 lv_vendor_site_id_updated := '';
1870 lv_thhold_grp_id_updated := '';
1871 lv_thhold_xcep_id_updated := '';
1872
1873 fnd_file.put_line(FND_FILE.LOG, 'START OF Procedure ');
1874
1875 ln_request_id := FND_GLOBAL.conc_request_id;
1876
1877 lv_debug_msg := ' A. Report Parameters';
1878
1879 If lv_debug_flag = 'Y' then
1880 Print_log(lv_debug_flag, lv_debug_msg);
1881 End if;
1882
1883 lv_debug_msg := ' B. request id '|| ln_request_id ;
1884
1885 If lv_debug_flag = 'Y' then
1886 Print_log(lv_debug_flag, lv_debug_msg);
1887 End if;
1888
1889 lv_debug_msg := ' C. debug flag ' || lv_debug_flag;
1890
1891 If lv_debug_flag = 'Y' then
1892 Print_log(lv_debug_flag, lv_debug_msg);
1893 End if;
1894
1895 lv_debug_msg := ' D. old pan ' || P_old_pan_num ;
1896
1897 If lv_debug_flag = 'Y' then
1898 Print_log(lv_debug_flag, lv_debug_msg);
1899 End if;
1900
1901 lv_debug_msg := ' E. new pan ' || P_new_pan_num ;
1902
1903 If lv_debug_flag = 'Y' then
1904 Print_log(lv_debug_flag, lv_debug_msg);
1905 End if;
1906
1907 lv_debug_msg :=' F. vendor id '|| P_vendor_id;
1908
1909 If lv_debug_flag = 'Y' then
1910 Print_log(lv_debug_flag, lv_debug_msg);
1911 End if;
1912
1913 -- Update the jai_ap_tds_thhold_grps
1914
1915 lv_debug_msg := ' 1. Update jai_ap_tds_thhold_grps';
1916
1917 If lv_debug_flag = 'Y' then
1918 Print_log(lv_debug_flag, lv_debug_msg);
1919 End if;
1920
1921 for thhold_grps in C_lock_thhold_grps
1922 loop
1923
1924 lv_debug_msg := ' 2. Going to update jai_ap_tds_thhold_grps';
1925
1926 If lv_debug_flag = 'Y' then
1927 Print_log(lv_debug_flag, lv_debug_msg);
1928 End if;
1929
1930 update jai_ap_tds_thhold_grps
1931 set vendor_pan_num = P_new_pan_num
1932 where vendor_id = P_vendor_id
1933 and vendor_pan_num = P_old_pan_num
1934 and threshold_grp_id = thhold_grps.threshold_grp_id;
1935
1936 lv_debug_msg := ' 3. Done with update of '|| thhold_grps.threshold_grp_id;
1937
1938 If lv_debug_flag = 'Y' then
1939 Print_log(lv_debug_flag, lv_debug_msg);
1940 End if;
1941
1942 lv_thhold_grp_id_updated := lv_thhold_grp_id_updated || '-' || thhold_grps.threshold_grp_id;
1943
1944 lv_debug_msg := ' 4. Value of lv_thhold_grp_id_updated '|| lv_thhold_grp_id_updated;
1945
1946 If lv_debug_flag = 'Y' then
1947 Print_log(lv_debug_flag, lv_debug_msg);
1948 End if;
1949
1950
1951 end loop;
1952
1953
1954 -- Update the JAI_AP_TDS_VENDOR_HDRS
1955 lv_debug_msg := ' 5. Update JAI_AP_TDS_VENDOR_HDRS';
1956
1957 If lv_debug_flag = 'Y' then
1958 Print_log(lv_debug_flag, lv_debug_msg);
1959 End if;
1960
1961 for vndr_tds_hdr in (select vthdr.*
1962 from JAI_AP_TDS_VENDOR_HDRS vthdr
1963 where vthdr.vendor_id = P_vendor_id
1964 and vthdr.pan_no = P_old_pan_num)
1965 loop
1966
1967 lv_debug_msg := ' 6. Going to update JAI_AP_TDS_VENDOR_HDRS';
1968
1969 If lv_debug_flag = 'Y' then
1970 Print_log(lv_debug_flag, lv_debug_msg);
1971 End if;
1972
1973 update JAI_AP_TDS_VENDOR_HDRS
1974 set pan_no = P_new_pan_num
1975 where vendor_id = vndr_tds_hdr.vendor_id
1976 and vendor_site_id = vndr_tds_hdr.vendor_site_id
1977 and pan_no = P_old_pan_num;
1978
1979
1980 lv_debug_msg := ' 7. Done with update of vendor '|| vndr_tds_hdr.vendor_id;
1981 lv_debug_msg := lv_debug_msg || ' site '|| vndr_tds_hdr.vendor_site_id ;
1982
1983 If lv_debug_flag = 'Y' then
1984 Print_log(lv_debug_flag, lv_debug_msg);
1985 End if;
1986
1987 If vndr_tds_hdr.vendor_site_id <> 0 Then
1988 lv_vendor_site_id_updated := lv_vendor_site_id_updated || ' - '||vndr_tds_hdr.vendor_site_id;
1989 End if;
1990
1991 lv_debug_msg := ' 8. Value of lv_vendor_site_id_updated '|| lv_vendor_site_id_updated;
1992
1993
1994 If lv_debug_flag = 'Y' then
1995 Print_log(lv_debug_flag, lv_debug_msg);
1996 End if;
1997
1998 end loop;
1999
2000
2001 -- jai_ap_tds_thhold_xceps
2002
2003 lv_debug_msg := ' 9. Update jai_ap_tds_thhold_xceps';
2004
2005 If lv_debug_flag = 'Y' then
2006 Print_log(lv_debug_flag, lv_debug_msg);
2007 End if;
2008
2009 for thhold_xceps in (select tdsxps.*
2010 from jai_ap_tds_thhold_xceps tdsxps
2011 where tdsxps.vendor_id = P_vendor_id
2012 and vendor_pan = P_old_pan_num)
2013 loop
2014
2015 lv_debug_msg := ' 10. Going to update jai_ap_tds_thhold_xceps';
2016
2017 If lv_debug_flag = 'Y' then
2018 Print_log(lv_debug_flag, lv_debug_msg);
2022 set vendor_pan = P_new_pan_num
2019 End if;
2020
2021 Update jai_ap_tds_thhold_xceps
2023 where vendor_id = P_vendor_id
2024 and vendor_pan = P_old_pan_num;
2025
2026 lv_debug_msg := ' 11. Done with update of vendor'||P_vendor_id ;
2027
2028 If lv_debug_flag = 'Y' then
2029 Print_log(lv_debug_flag, lv_debug_msg);
2030 End if;
2031
2032 lv_thhold_xcep_id_updated := lv_thhold_xcep_id_updated || '-' || thhold_xceps.threshold_exception_id;
2033
2034 lv_debug_msg := ' 12. Value of lv_thhold_xcep_id_updated '|| lv_thhold_xcep_id_updated;
2035
2036 If lv_debug_flag = 'Y' then
2037 Print_log(lv_debug_flag, lv_debug_msg);
2038 End if;
2039
2040 end loop;
2041
2042
2043 -- insert a record in jai_ap_tds_pan_changes
2044 -- This help us to keep track of PAN changes for the given vendor
2045
2046
2047 lv_debug_msg := ' 13. Inside insert - ';
2048
2049 If lv_debug_flag = 'Y' then
2050 Print_log(lv_debug_flag, lv_debug_msg);
2051 End if;
2052
2053 Insert into jai_ap_tds_pan_changes
2054 ( pan_change_id,
2055 vendor_id,
2056 old_pan_num,
2057 new_pan_num,
2058 request_id,
2059 request_date,
2060 vendor_site_id_updated,
2061 thhold_grp_id_updated,
2062 thhold_xcep_id_updated,
2063 creation_date,
2064 created_by,
2065 last_update_date,
2066 last_updated_by,
2067 last_update_login
2068 )
2069 values
2070 ( jai_ap_tds_pan_changes_s.nextval,
2071 P_vendor_id,
2072 P_old_pan_num,
2073 P_new_pan_num,
2074 ln_request_id,
2075 sysdate,
2076 lv_vendor_site_id_updated,
2077 lv_thhold_grp_id_updated,
2078 lv_thhold_xcep_id_updated,
2079 sysdate,
2080 fnd_global.user_id,
2081 sysdate,
2082 fnd_global.user_id,
2083 fnd_global.login_id
2084 );
2085
2086
2087 commit;
2088
2089 Exception
2090 When others then
2091
2092 IF (SQLCODE < 0) then
2093
2094 If lv_debug_flag = 'Y' then
2095 Print_log(lv_debug_flag,lv_debug_msg);
2096 Print_log(lv_debug_flag,SQLERRM);
2097 End if;
2098 END IF;
2099
2100 IF (SQLCODE = -54) then
2101 If lv_debug_flag = 'Y' then
2102 Print_log(lv_debug_flag,'(Pan update :Exception) Vendor to be updated by this process are locked');
2103 end if;
2104 END IF;
2105
2106 End pan_update;
2107
2108 /*
2109 || Added function get_tds_invoice_batch by Ramananda for bug#4584221
2110 */
2111 FUNCTION get_tds_invoice_batch(p_invoice_id IN NUMBER)
2112 RETURN VARCHAR2 IS
2113
2114 lv_same_tds_batch VARCHAR2(1);
2115 lv_batch_name ap_batches_all.batch_name%TYPE;
2116 ln_regime_id JAI_RGM_DEFINITIONS.regime_id%type ;
2117 lv_attribute_value JAI_RGM_ORG_REGNS_V.attribute_Value%type ;
2118
2119 CURSOR c_regime_cur IS
2120 SELECT regime_id
2121 FROM JAI_RGM_DEFINITIONS
2122 WHERE regime_code = jai_constants.tds_regime;
2123
2124 CURSOR c_attribute_value_cur(P_regime_id IN NUMBER) is
2125 SELECT attribute_Value
2126 FROM JAI_RGM_ORG_REGNS_V
2127 WHERE regime_id = P_regime_id
2128 AND attribute_type_code = jai_constants.regn_type_others
2129 AND attribute_code = jai_constants.regn_type_tds_batch;
2130
2131 CURSOR c_batch_name(cp_invoice_id NUMBER)
2132 IS
2133 SELECT b.batch_name
2134 FROM ap_invoices_all a,
2135 ap_batches_all b
2136 WHERE a.batch_id = b.batch_id
2137 AND a.invoice_id = cp_invoice_id;
2138
2139 BEGIN
2140
2141 OPEN c_regime_cur ;
2142 FETCH c_regime_cur INTO ln_regime_id ;
2143 CLOSE c_regime_cur ;
2144
2145 OPEN c_attribute_value_cur(ln_regime_id) ;
2146 FETCH c_attribute_value_cur INTO lv_attribute_value ;
2147 CLOSE c_attribute_value_cur ;
2148
2149 IF upper(lv_attribute_value) in ('YES' , 'Y') THEN
2150
2151 OPEN c_batch_name(p_invoice_id);
2152 FETCH c_batch_name INTO lv_batch_name;
2153 CLOSE c_batch_name;
2154
2155 END IF;
2156
2157 IF UPPER(NVL(lv_attribute_value,'N')) in ('NO','N')
2158 OR lv_batch_name IS NULL THEN
2159 lv_batch_name := 'TDS'||TO_CHAR(TRUNC(SYSDATE));
2160 END IF;
2161
2162 RETURN lv_batch_name;
2163 END get_tds_invoice_batch;
2164
2165 /*------------------------------------------------------------------------------------------------------------*/
2166 -- Begin 4579729
2167 /*------------------------------------------------------------------------------------------------------------*/
2168
2169 procedure jai_calc_ipv_erv (P_errmsg OUT NOCOPY VARCHAR2,
2170 P_retcode OUT NOCOPY Number,
2171 P_invoice_id in number,
2172 P_po_dist_id in number,
2173 P_invoice_distribution_id IN NUMBER,
2174 P_amount IN NUMBER,
2175 P_base_amount IN NUMBER,
2176 P_rcv_transaction_id IN NUMBER,
2177 P_invoice_price_variance IN NUMBER,
2178 P_base_invoice_price_variance IN NUMBER,
2179 P_price_var_ccid IN NUMBER,
2180 P_Exchange_rate_variance IN NUMBER,
2181 P_rate_var_ccid IN NUMBER
2182 )
2186
2183 as
2184
2185 /* Cursors */
2187 Cursor check_rec_tax ( ln_tax_id number) is
2188 select tax_name,
2189 tax_account_id,
2190 mod_cr_percentage,
2191 adhoc_flag,
2192 nvl(tax_rate, 0) tax_rate,
2193 tax_type
2194 from JAI_CMN_TAXES_ALL
2195 where tax_id = ln_tax_id;
2196
2197
2198 Cursor get_misc_lines (ln_dist_line_number in number,
2199 ln_invoice_id in number ) is
2200 select *
2201 from ap_invoice_distributions_all
2202 where invoice_id = ln_invoice_id
2203 and distribution_line_number = ln_dist_line_number;
2204
2205
2206 /* precision */
2207 Cursor get_prec (lv_currency_code varchar2) is
2208 select precision
2209 from fnd_currencies
2210 where currency_code = lv_currency_code;
2211
2212
2213 /* Local Variables */
2214 ln_tax_ipv number;
2215 ln_tax_bipv number;
2216 ln_price_var_ccid number;
2217
2218 ln_tax_erv number;
2219
2220 lv_inv_curr_code varchar2(15);
2221 lv_base_curr_code varchar2(15);
2222
2223 ln_inv_pre number;
2224 ln_base_pre number;
2225
2226 r_get_misc_lines get_misc_lines%ROWTYPE;
2227
2228
2229
2230 Begin
2231
2232
2233 fnd_file.put_line(FND_FILE.LOG, ' inside procedure ');
2234
2235 lv_base_curr_code := 'INR';
2236
2237 Begin
2238 Select invoice_currency_code
2239 into lv_inv_curr_code
2240 from ap_invoices_all
2241 where invoice_id = p_invoice_id;
2242
2243 Exception
2244 When others then
2245 null;
2246 End;
2247
2248 If lv_inv_curr_code = 'INR' Then
2249 open get_prec(lv_base_curr_code);
2250 Fetch get_prec into ln_base_pre;
2251 Close get_prec;
2252
2253 ln_inv_pre := ln_base_pre;
2254
2255 Else
2256 open get_prec(lv_inv_curr_code);
2257 Fetch get_prec into ln_inv_pre;
2258 Close get_prec;
2259
2260 open get_prec(lv_base_curr_code);
2261 Fetch get_prec into ln_base_pre;
2262 Close get_prec;
2263
2264 End if;
2265
2266 fnd_file.put_line(FND_FILE.LOG, ' invoice id '|| p_invoice_id);
2267 fnd_file.put_line(FND_FILE.LOG, ' po dist id '|| p_po_dist_id);
2268
2269 for Misc_loop in ( select *
2270 from JAI_AP_MATCH_INV_TAXES
2271 where invoice_id = p_invoice_id
2272 and parent_invoice_distribution_id = p_invoice_distribution_id
2273 )
2274 loop
2275
2276
2277 fnd_file.put_line(FND_FILE.LOG,' inside loop -- 2 ' );
2278
2279 /* For later use if necessary to check the tax type. now education cess will not be
2280 created at invoice level if it is available in PO/Receipt level
2281
2282 for tax_loop in check_rec_tax (select tax_id
2283 from ja_in_ap_tax_distributions
2284 where invoice_id = misc_loop.invoice_id
2285 and distribution_line_number = misc_loop.distribution_line_number)
2286 loop
2287
2288 Service and Education cess are recoverable taxes and
2289 IPV should not be calculated on these lines
2290 If not (tax_loop.tax_type like '%EDUCATION_CESS') Then
2291
2292 */
2293
2294 Open get_misc_lines(misc_loop.distribution_line_number, misc_loop.invoice_id);
2295 Fetch get_misc_lines into r_get_misc_lines;
2296 Close get_misc_lines;
2297
2298 If nvl(p_amount ,0) <> 0 Then
2299
2300 fnd_file.put_line(FND_FILE.LOG,' Inside item amount not zero ' || p_amount);
2301
2302 If nvl(r_get_misc_lines.amount , 0 ) <> 0 Then
2303
2304 fnd_file.put_line(FND_FILE.LOG,' Inside Tax amount not zero ' || r_get_misc_lines.amount);
2305
2306 IF nvl(p_invoice_price_variance,0 ) <> 0 Then
2307
2308 ln_tax_ipv := r_get_misc_lines.amount * (nvl(p_invoice_price_variance,0) /p_amount);
2309
2310 End if;
2311
2312 IF nvl(p_exchange_rate_variance,0 ) <> 0 Then
2313
2314 ln_tax_erv := r_get_misc_lines.amount * (nvl(p_exchange_rate_variance,0)/p_amount);
2315
2316 End if;
2317
2318 fnd_file.put_line(FND_FILE.LOG,' IPV '|| ln_tax_ipv);
2319 fnd_file.put_line(FND_FILE.LOG,' ERV '|| ln_tax_erv);
2320
2321 /* IPV */
2322
2323 If nvl(ln_tax_ipv,0) <> 0 then
2324
2325 fnd_file.put_line(FND_FILE.LOG,' Inside IPV not zero '|| ln_tax_ipv);
2326
2327 ln_tax_bipv := ln_tax_ipv * nvl(r_get_misc_lines.exchange_rate,1);
2328
2329 update ap_invoice_distributions_all
2330 set invoice_price_variance = round(ln_tax_ipv,ln_inv_pre),
2331 base_invoice_price_variance = round(ln_tax_bipv, ln_base_pre),
2332 price_var_code_combination_id = P_price_var_ccid
2333 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
2334 End if;
2335
2336 /* ERV */
2337
2338
2339 If nvl(ln_tax_erv,0) <> 0 then
2340
2341 fnd_file.put_line(FND_FILE.LOG,' Inside ERV not zero '|| ln_tax_erv);
2342 fnd_file.put_line(FND_FILE.LOG,' rate var CCID '|| P_rate_var_ccid);
2343
2344 update ap_invoice_distributions_all
2345 set exchange_rate_variance = round(ln_tax_erv,ln_inv_pre),
2349
2346 rate_var_code_combination_id = P_rate_var_ccid
2347 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
2348 End if;
2350
2351 Else
2352
2353 /* update ipv and bipv to 0. no need to update Var CCID */
2354
2355 update ap_invoice_distributions_all
2356 set invoice_price_variance = 0,
2357 base_invoice_price_variance = 0,
2358 exchange_rate_variance = 0
2359 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
2360 End if;
2361 /* r_get_misc_lines.amount <> 0 */
2362
2363 End if; /* p_amount <> 0 */
2364
2365 -- end loop; -- End tax_loop
2366 end loop; -- End misc_loop
2367
2368 p_errmsg :=NULL;
2369 p_retcode := NULL;
2370
2371
2372 Exception
2373 When others then
2374 P_errmsg := SQLERRM;
2375 P_retcode := 2;
2376 Fnd_File.put_line(Fnd_File.LOG, 'EXCEPTION END PROCEDURE - JAI_CALC_IPV ');
2377 Fnd_File.put_line(Fnd_File.LOG, 'Error : ' || P_errmsg);
2378 End jai_calc_ipv_erv;
2379
2380 -- added, Harshita for Bug 5553150
2381
2382 FUNCTION fetch_tax_target_amt
2383 ( p_invoice_id IN NUMBER ,
2384 p_line_location_id IN NUMBER ,
2385 p_transaction_id IN NUMBER ,
2386 p_parent_dist_id IN NUMBER,
2387 p_tax_id IN NUMBER
2388 )
2389 RETURN NUMBER
2390 IS
2391
2392 TYPE TAX_CUR IS RECORD
2393 (
2394 P_1 JAI_PO_TAXES.precedence_1%type,
2395 P_2 JAI_PO_TAXES.precedence_2%type,
2396 P_3 JAI_PO_TAXES.precedence_3%type,
2397 P_4 JAI_PO_TAXES.precedence_4%type,
2398 P_5 JAI_PO_TAXES.precedence_5%type,
2399 P_6 JAI_PO_TAXES.precedence_6%type,
2400 P_7 JAI_PO_TAXES.precedence_7%type,
2401 P_8 JAI_PO_TAXES.precedence_8%type,
2402 P_9 JAI_PO_TAXES.precedence_9%type,
2403 P_10 JAI_PO_TAXES.precedence_10%type
2404 ) ;
2405
2406 TYPE tax_cur_type IS REF CURSOR RETURN TAX_CUR;
2407 c_tax_cur TAX_CUR_TYPE;
2408 rec c_tax_cur%ROWTYPE;
2409 ln_base_amt number ;
2410
2411
2412 FUNCTION fetch_line_amt(p_precedence_value IN NUMBER)
2413 RETURN NUMBER
2414 IS
2415 cursor c_line_amt
2416 is
2417 select NVL(tax_amount,-1) -- 5763527, Added by kunkumar for Bug#5593895
2418 from JAI_AP_MATCH_INV_TAXES
2419 where invoice_id = p_invoice_id
2420 and line_no = p_precedence_value ;
2421
2422 cursor c_base_inv_amt
2423 is
2424 select amount
2425 from ap_invoice_distributions_all
2426 where invoice_distribution_id = p_parent_dist_id
2427 and invoice_id = p_invoice_id ;
2428
2429 ln_line_amt number ;
2430
2431 BEGIN
2432 if p_precedence_value = -1 then
2433 return 0 ;
2434 elsif p_precedence_value = 0 then
2435 open c_base_inv_amt ;
2436 fetch c_base_inv_amt into ln_line_amt ;
2437 close c_base_inv_amt ;
2438 return nvl(ln_line_amt,0) ;
2439 else
2440 open c_line_amt ;
2441 fetch c_line_amt into ln_line_amt ;
2442 close c_line_amt ;
2443 return nvl(ln_line_amt,0) ;
2444 end if ;
2445
2446 END fetch_line_amt;
2447
2448 BEGIN
2449
2450 IF p_line_location_id is not null then
2451 OPEN c_tax_cur FOR
2452 select Precedence_1 P_1,
2453 Precedence_2 P_2,
2454 Precedence_3 P_3,
2455 Precedence_4 P_4,
2456 Precedence_5 P_5,
2457 Precedence_6 P_6,
2458 Precedence_7 P_7,
2459 Precedence_8 P_8,
2460 Precedence_9 P_9,
2461 Precedence_10 P_10
2462 from JAI_PO_TAXES
2463 where line_location_id = p_line_location_id
2464 and tax_id = p_tax_id ;
2465 ELSE
2466 OPEN c_tax_cur FOR
2467 select Precedence_1 P_1,
2468 Precedence_2 P_2,
2469 Precedence_3 P_3,
2470 Precedence_4 P_4,
2471 Precedence_5 P_5,
2472 Precedence_6 P_6,
2473 Precedence_7 P_7,
2474 Precedence_8 P_8,
2475 Precedence_9 P_9,
2476 Precedence_10 P_10
2477 from JAI_RCV_LINE_TAXES
2478 where shipment_line_id IN
2479 ( select shipment_line_id
2480 from JAI_RCV_LINE_TAXES
2481 where transaction_id = p_transaction_id
2482 )
2483 and tax_id = p_tax_id ;
2484
2485 END IF ;
2486
2487 FETCH c_tax_cur INTO rec;
2488 ln_base_amt := fetch_line_amt(nvl(rec.P_1,-1)) + fetch_line_amt(nvl(rec.P_2,-1)) + fetch_line_amt(nvl(rec.P_3,-1))
2489 + fetch_line_amt(nvl(rec.P_4,-1)) + fetch_line_amt(nvl(rec.P_5,-1)) + fetch_line_amt(nvl(rec.P_6,-1))
2490 + fetch_line_amt(nvl(rec.P_7,-1)) + fetch_line_amt(nvl(rec.P_8,-1)) + fetch_line_amt(nvl(rec.P_9,-1))
2491 + fetch_line_amt(nvl(rec.P_10,-1)) ;
2492 CLOSE c_tax_cur ;
2493 return ln_base_amt ;
2494
2495
2499 -- End 4579729
2496 END fetch_tax_target_amt ;
2497 -- ended, Harshita for Bug 5553150
2498 /*------------------------------------------------------------------------------------------------------------*/
2500 /*------------------------------------------------------------------------------------------------------------*/
2501
2502
2503 -- Added by Jia Li for Tax inclusive computation on 2007/12/17, Begin
2504 --==========================================================================
2505 -- FUNCTION NAME:
2506 --
2507 -- get_tax_account_id Private
2508 --
2509 -- DESCRIPTION:
2510 --
2511 -- This function is get tax account ccid
2512 --
2513 -- PARAMETERS:
2514 -- In: pn_tax_id
2515 -- pn_tax_type
2516 -- pn_org_id
2517 --
2518 -- DESIGN REFERENCES:
2519 -- Inclusive Tax Technical Design V1.4.doc
2520 --
2521 -- CHANGE HISTORY:
2522 --
2523 -- 20-DEC-2007 Jia Li created
2524 --==========================================================================
2525 FUNCTION get_tax_account_id
2526 ( pn_tax_id IN NUMBER
2527 , pv_tax_type IN VARCHAR2
2528 , pn_org_id IN NUMBER
2529 )
2530 RETURN NUMBER
2531 IS
2532 ln_tax_def_acc_id NUMBER;
2533 ln_tax_rgm_acc_id NUMBER;
2534 ln_tax_acc_id NUMBER;
2535 lv_procedure_name VARCHAR2(40):='get_tax_account_id';
2536 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2537 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2538
2539 CURSOR cur_tax_acc IS
2540 SELECT
2541 tax_account_id
2542 FROM
2543 jai_cmn_taxes_all
2544 WHERE tax_id = pn_tax_id;
2545
2546 BEGIN
2547 --logging for debug
2548 IF (ln_proc_level >= ln_dbg_level)
2549 THEN
2550 FND_LOG.STRING( ln_proc_level
2551 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2552 , 'Enter procedure'
2553 );
2554 FND_LOG.STRING( ln_proc_level
2555 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
2556 , 'Org_id = ' || pn_org_id
2557 );
2558 FND_LOG.STRING( ln_proc_level
2559 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
2560 , 'Tax_id = '|| pn_tax_id ||' Tax_type = ' || pv_tax_type
2561 );
2562 END IF; --ln_proc_level>=l_dbg_level
2563
2564 -- Get tax_account_id from tax defination
2565 OPEN cur_tax_acc;
2566 FETCH cur_tax_acc INTO ln_tax_def_acc_id;
2567 CLOSE cur_tax_acc;
2568
2569 -- Get tax_account_id from rgm setup for SERVICE and VAT tax.
2570 BEGIN
2571 SELECT
2572 TO_NUMBER(acc_rgm.attribute_value)
2573 INTO
2574 ln_tax_rgm_acc_id
2575 FROM
2576 jai_rgm_definitions rgm_def
2577 , jai_rgm_registrations tax_rgm
2578 , jai_rgm_registrations acc_rgm
2579 WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
2580 AND tax_rgm.regime_id = rgm_def.regime_id
2581 AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
2582 AND tax_rgm.attribute_code = pv_tax_type
2583 AND tax_rgm.regime_id = acc_rgm.regime_id
2584 AND acc_rgm.registration_type = jai_constants.regn_type_accounts
2585 AND acc_rgm.attribute_code = jai_constants.recovery_interim
2586 AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
2587
2588 ln_tax_acc_id := ln_tax_rgm_acc_id;
2589 EXCEPTION
2590 WHEN no_data_found THEN
2591 ln_tax_acc_id := ln_tax_def_acc_id;
2592 WHEN OTHERS THEN
2593 ln_tax_acc_id := -1;
2594 END;
2595
2596 IF (ln_proc_level >= ln_dbg_level)
2597 THEN
2598 FND_LOG.STRING( ln_proc_level
2599 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.result'
2600 , 'Tax Account ID = ' || ln_tax_acc_id
2601 );
2602 FND_LOG.STRING( ln_proc_level
2603 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
2604 , 'Enter procedure'
2605 );
2606 END IF; -- ln_proc_level >= ln_dbg_level
2607
2608 RETURN ln_tax_acc_id;
2609
2610 END get_tax_account_id;
2611
2612
2613 --==========================================================================
2614 -- PROCEDURE NAME:
2615 --
2616 -- insert_gl_interface Private
2617 --
2618 -- DESCRIPTION:
2619 --
2620 -- This function is insert inclusive data into gl_interface
2621 --
2622 -- PARAMETERS:
2623 -- In: pn_set_of_books_id the set of books id
2624 -- pd_accounting_date GL date of the invoice
2625 -- pv_currency_code currency code
2626 -- pn_enter_cr credit amount
2627 -- pn_enter_dr debit amount
2628 -- pd_transaction_date invoice date
2629 -- pn_code_combination_id code_combination_id
2630 -- pd_currency_conversion_date the column values can be retreived from the invoice
2631 -- pv_currency_conversion_type the column values can be retreived from the invoice
2632 -- pv_currency_conversion_rate the column values can be retreived from the invoice
2636 -- pv_reference26 value of invoice_id
2633 -- pv_reference1 inventory organization code,base on organization_id from PO/Receipt from where it's matched
2634 -- pv_reference10 'India Localization Entry for accounting inclusive taxes for invoice'||lv_invoice_num
2635 -- pv_reference23 procedure name that makes the insert into gl_interface hard code string
2637 -- pv_reference27 organization id of the inventory organization id
2638 --
2639 -- DESIGN REFERENCES:
2640 -- Inclusive Tax Technical Design V1.4.doc
2641 --
2642 -- CHANGE HISTORY:
2643 --
2644 -- 20-DEC-2007 Jia Li created
2645 --==========================================================================
2646 PROCEDURE insert_gl_interface
2647 ( pn_set_of_books_id IN NUMBER
2648 , pd_accounting_date IN DATE
2649 , pv_currency_code IN VARCHAR2
2650 , pn_enter_cr IN NUMBER DEFAULT NULL
2651 , pn_enter_dr IN NUMBER DEFAULT NULL
2652 , pd_transaction_date IN DATE
2653 , pn_code_combination_id IN NUMBER
2654 , pd_currency_conversion_date IN DATE
2655 , pv_currency_conversion_type IN VARCHAR2
2656 , pv_currency_conversion_rate IN VARCHAR2
2657 , pv_reference1 IN VARCHAR2
2658 , pv_reference10 IN VARCHAR2
2659 , pv_reference23 IN VARCHAR2
2660 , pv_reference26 IN VARCHAR2
2661 , pv_reference27 IN VARCHAR2
2662 )
2663 IS
2664 BEGIN
2665 INSERT INTO gl_interface
2666 ( status
2667 , set_of_books_id
2668 , user_je_source_name
2669 , user_je_category_name
2670 , accounting_date
2671 , currency_code
2672 , date_created
2673 , created_by
2674 , actual_flag
2675 , entered_cr
2676 , entered_dr
2677 , transaction_date
2678 , code_combination_id
2679 , currency_conversion_date
2680 , user_currency_conversion_type
2681 , currency_conversion_rate
2682 , reference1
2683 , reference10
2684 , reference22
2685 , reference23
2686 , reference24
2687 , reference25
2688 , reference26
2689 , reference27
2690 )
2691 VALUES
2692 ( 'NEW' -- 'NEW'
2693 , pn_set_of_books_id
2694 , 'Payables India' -- je source name 'Payables India'
2695 , 'Register India' -- je category name 'Register India'
2696 , pd_accounting_date
2697 , pv_currency_code
2698 , sysdate -- standard who column
2699 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
2700 , 'A' -- 'A'
2701 , pn_enter_cr
2702 , pn_enter_cr
2703 , pd_transaction_date
2704 , pn_code_combination_id
2705 , pd_currency_conversion_date
2706 , pv_currency_conversion_type
2707 , pv_currency_conversion_rate
2708 , pv_reference1
2709 , 'India Localization Entry for accounting inclusive taxes for invoice'||pv_reference10
2710 , 'India Localization Entry' -- 'India Localization Entry'
2711 , pv_reference23
2712 , 'AP_INVOICES_ALL' -- 'AP_INVOICES_ALL'
2713 , 'INVOICE_ID' -- 'INVOICE_ID'
2714 , pv_reference26
2715 , pv_reference27
2716 );
2717 END insert_gl_interface;
2718
2719
2720 --==========================================================================
2721 -- PROCEDURE NAME:
2722 --
2723 -- acct_inclu_taxes Public
2724 --
2725 -- DESCRIPTION:
2726 --
2727 -- This procedure is written that would pass GL entries for inclusive taxes in GL interface
2728 --
2729 -- PARAMETERS:
2730 -- In: pn_invoice_id pass the invoice id for which the accounting needs to done
2731 -- pn_invoice_distribution_id pass the invoice distribution id for the item line which the accounting needs to done
2732 -- OUt: xv_process_flag Indicates the process flag, 'SS' for success
2733 -- 'EE' for expected error
2734 -- 'UE' for unexpected error
2735 -- xv_process_message Indicates the process message
2736 --
2737 --
2738 -- DESIGN REFERENCES:
2739 -- Inclusive Tax Technical Design V1.4.doc
2740 --
2741 -- CHANGE HISTORY:
2742 --
2743 -- 17-DEC-2007 Jia Li created
2744 --==========================================================================
2745 PROCEDURE acct_inclu_taxes
2746 ( pn_invoice_id IN NUMBER
2747 , pn_invoice_distribution_id IN NUMBER
2748 , xv_process_flag OUT NOCOPY VARCHAR2
2749 , xv_process_message OUT NOCOPY VARCHAR2
2750 )
2751 IS
2752 ln_org_id ap_invoices_all.org_id%TYPE;
2753 ld_gl_date ap_invoices_all.gl_date%TYPE;
2754 lv_invoice_num ap_invoices_all.invoice_num%TYPE;
2755 ld_invoice_date ap_invoices_all.invoice_date%TYPE;
2756 lv_invoice_currency_code ap_invoices_all.invoice_currency_code%TYPE;
2757 ln_exchange_rate ap_invoices_all.exchange_rate%TYPE;
2761 ln_inventory_item_id ap_invoice_lines_all.inventory_item_id %TYPE;
2758 lv_exchange_rate_type ap_invoices_all.exchange_rate_type%TYPE;
2759 ld_exchange_date ap_invoices_all.exchange_date%TYPE;
2760
2762 ld_accounting_date ap_invoice_lines_all.accounting_date%TYPE;
2763 lv_match_type ap_invoice_lines_all.match_type%TYPE;
2764 ln_set_of_books_id ap_invoice_lines_all.set_of_books_id%TYPE;
2765 ln_po_dist_id ap_invoice_lines_all.po_distribution_id%TYPE;
2766 ln_po_loc_id ap_invoice_lines_all.po_line_location_id%TYPE;
2767 ln_item_line_amt ap_invoice_lines_all.amount%TYPE;
2768
2769 ln_invoice_line_num ap_invoice_distributions_all.invoice_line_number%TYPE;
2770 lv_inclu_tax_flag jai_ap_tds_years.inclusive_tax_flag%TYPE;
2771 ln_inv_org_id mtl_parameters.organization_id%TYPE;
2772 lv_inv_org_code mtl_parameters.organization_code%TYPE;
2773
2774 lv_accrue_on_receipt_flag VARCHAR2(10);
2775 ln_tax_account_id NUMBER;
2776 ln_invoice_post_num NUMBER;
2777 ln_total_inclu_tax_amt NUMBER;
2778 ln_cr_line_amt NUMBER;
2779 ln_total_cr_line_amt NUMBER;
2780
2781 lv_procedure_name VARCHAR2(40):='acct_inclu_taxes';
2782 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2783 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2784
2785 CURSOR match_inclu_tax_cur
2786 ( pn_invoice_line_num NUMBER
2787 )
2788 IS
2789 SELECT
2790 tax_line.tax_id tax_id
2791 , tax.tax_type tax_type
2792 , SUM(tax_line.tax_amount) tax_amount
2793 FROM
2794 jai_ap_match_inv_taxes tax_line
2795 , jai_cmn_taxes_all tax
2796 WHERE NVL(tax.inclusive_tax_flag,'N') = 'Y'
2797 AND tax_line.invoice_id = pn_invoice_id
2798 AND tax_line.parent_invoice_line_number = pn_invoice_line_num
2799 AND tax_line.tax_id = tax.tax_id
2800 GROUP BY
2801 tax_line.tax_id
2802 , tax.tax_type;
2803
2804 CURSOR standalone_inclu_tax_cur
2805 ( pn_invoice_line_num NUMBER
2806 )
2807 IS
2808 SELECT
2809 tax_line.tax_id tax_id
2810 , tax.tax_type tax_type
2811 , SUM(tax_line.tax_amt) tax_amount
2812 FROM
2813 jai_cmn_document_taxes tax_line
2814 , jai_cmn_taxes_all tax
2815 WHERE NVL(tax.inclusive_tax_flag,'N') = 'Y'
2816 AND tax_line.source_doc_type = jai_constants.G_AP_STANDALONE_INVOICE
2817 AND tax_line.source_doc_id = pn_invoice_id
2818 AND tax_line.source_doc_parent_line_no = pn_invoice_line_num
2819 AND tax_line.tax_id = tax.tax_id
2820 GROUP BY
2821 tax_line.tax_id
2822 , tax.tax_type;
2823
2824 CURSOR item_line_dist_cur
2825 ( pn_invoice_line_num NUMBER
2826 )
2827 IS
2828 SELECT
2829 dist_code_combination_id
2830 , amount
2831 FROM
2832 ap_invoice_distributions_all
2833 WHERE invoice_id = pn_invoice_id
2834 AND invoice_line_number = pn_invoice_line_num;
2835
2836 BEGIN
2837 --logging for debug
2838 IF (ln_proc_level >= ln_dbg_level)
2839 THEN
2840 FND_LOG.STRING( ln_proc_level
2841 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2842 , 'Enter procedure'
2843 );
2844 END IF; --ln_proc_level>=l_dbg_level
2845
2846 -- Init variable
2847 ln_total_inclu_tax_amt := 0;
2848 ln_cr_line_amt := 0;
2849 ln_total_cr_line_amt := 0;
2850 ln_invoice_post_num := 0;
2851
2852 -- Get invoice info
2853 SELECT
2854 org_id
2855 , gl_date
2856 , invoice_num
2857 , invoice_date
2858 , invoice_currency_code
2859 , exchange_rate
2860 , exchange_rate_type
2861 , exchange_date
2862 INTO
2863 ln_org_id
2864 , ld_gl_date
2865 , lv_invoice_num
2866 , ld_invoice_date
2867 , lv_invoice_currency_code
2868 , ln_exchange_rate
2869 , lv_exchange_rate_type
2870 , ld_exchange_date
2871 FROM
2872 ap_invoices_all
2873 WHERE invoice_id = pn_invoice_id;
2874
2875 -- Check whether inclusive taxes needs to be accounted separately
2876 BEGIN
2877 SELECT
2878 NVL(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
2879 INTO
2880 lv_inclu_tax_flag
2881 FROM
2882 jai_ap_tds_years ja
2883 WHERE ja.legal_entity_id = ln_org_id
2884 AND sysdate BETWEEN ja.start_date AND ja.end_date;
2885 EXCEPTION
2886 WHEN OTHERS THEN
2887 lv_inclu_tax_flag := 'N';
2888 END;
2889
2890 -- If the user has setup to account inclusive aeparately, inclusive taxes need insert into GL Interface table.
2891 IF lv_inclu_tax_flag = 'Y'
2892 THEN
2893
2894 -- According pn_invoice_distribution_id to get ITEM line num .
2895 BEGIN
2896 SELECT
2897 aila.line_number
2898 INTO
2899 ln_invoice_line_num
2900 FROM
2901 ap_invoice_distributions_all aida
2902 , ap_invoice_lines_all aila
2903 WHERE aida.invoice_distribution_id = pn_invoice_distribution_id
2904 AND aila.line_number = aida.invoice_line_number
2905 AND aila.invoice_id = pn_invoice_id
2906 AND aila.line_type_lookup_code = 'ITEM';
2907 EXCEPTION
2908 WHEN OTHERS THEN
2912 IF ln_invoice_line_num > 0
2909 ln_invoice_line_num := 0;
2910 END;
2911
2913 THEN
2914 -- Get item invoice line info
2915 SELECT
2916 inventory_item_id
2917 , set_of_books_id
2918 , match_type
2919 , po_distribution_id
2920 , po_line_location_id
2921 , amount
2922 INTO
2923 ln_inventory_item_id
2924 , ln_set_of_books_id
2925 , lv_match_type
2926 , ln_po_dist_id
2927 , ln_po_loc_id
2928 , ln_item_line_amt
2929 FROM
2930 ap_invoice_lines_all
2931 WHERE invoice_id = pn_invoice_id
2932 AND line_number = ln_invoice_line_num;
2933
2934 -- Get inv_organization_id and inv_organization_code
2935 IF ln_po_dist_id IS NULL
2936 THEN
2937 lv_match_type := jai_constants.G_AP_STANDALONE_INVOICE;
2938 ln_inv_org_id := NULL;
2939 lv_inv_org_code := '';
2940 ELSE
2941 SELECT
2942 ploc.ship_to_organization_id
2943 , mp.organization_code
2944 INTO
2945 ln_inv_org_id
2946 , lv_inv_org_code
2947 FROM
2948 po_line_locations_all ploc
2949 , mtl_parameters mp
2950 WHERE ploc.line_location_id = ln_po_loc_id
2951 AND ploc.ship_to_organization_id = mp.organization_id;
2952 END IF; -- ln_po_dist_id IS NULL
2953
2954 -- According item invoice line num to get distribution quantity that has been transfer to gl
2955 SELECT
2956 COUNT(invoice_distribution_id)
2957 INTO
2958 ln_invoice_post_num
2959 FROM
2960 ap_invoice_distributions_all aida
2961 WHERE aida.invoice_id = pn_invoice_id
2962 AND aida.invoice_line_number = ln_invoice_line_num
2963 AND aida.posted_flag = 'Y';
2964
2965 -- if only one distribution line has been transfer to GL, then insert inclusive data into GL interface
2966 IF ln_invoice_post_num = 1
2967 THEN
2968 IF lv_match_type = jai_constants.G_AP_STANDALONE_INVOICE
2969 THEN
2970 -- Get inclusive tax info from jai_cmn_document_taxes
2971 -- and insert debit inclusive taxes into GL interface table.
2972 FOR standalone_inclu_tax_csr IN standalone_inclu_tax_cur(ln_invoice_line_num)
2973 LOOP
2974 ln_tax_account_id := get_tax_account_id
2975 ( pn_tax_id => standalone_inclu_tax_csr.tax_id
2976 , pv_tax_type => standalone_inclu_tax_csr.tax_type
2977 , pn_org_id => ln_org_id
2978 );
2979 insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
2980 , pd_accounting_date => ld_gl_date
2981 , pv_currency_code => lv_invoice_currency_code
2982 , pn_enter_dr => standalone_inclu_tax_csr.tax_amount
2983 , pd_transaction_date => ld_invoice_date
2984 , pn_code_combination_id => ln_tax_account_id
2985 , pd_currency_conversion_date => ld_exchange_date
2989 , pv_reference10 => lv_invoice_num
2986 , pv_currency_conversion_type => lv_exchange_rate_type
2987 , pv_currency_conversion_rate => ln_exchange_rate
2988 , pv_reference1 => lv_inv_org_code
2990 , pv_reference23 => lv_procedure_name
2991 , pv_reference26 => pn_invoice_id
2992 , pv_reference27 => ln_inv_org_id
2993 ) ;
2994
2995 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + standalone_inclu_tax_csr.tax_amount;
2996 IF (ln_proc_level >= ln_dbg_level)
2997 THEN
2998 FND_LOG.STRING( ln_proc_level
2999 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3000 , 'Inclusive tax account = '|| ln_tax_account_id
3001 );
3002 FND_LOG.STRING( ln_proc_level
3003 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3004 , 'Inclusive tax amount = '|| standalone_inclu_tax_csr.tax_amount
3005 );
3006 END IF; --ln_proc_level>=l_dbg_level
3007 END LOOP; -- for standalone_inclu_tax_cur cursor
3008 ELSE
3009 -- Get inclusive tax info from jai_ap_match_inv_taxes
3010 -- and insert debit inclusive taxes into GL interface table.
3011 FOR match_inclu_tax_csr IN match_inclu_tax_cur(ln_invoice_line_num)
3012 LOOP
3013 ln_tax_account_id := get_tax_account_id
3014 ( pn_tax_id => match_inclu_tax_csr.tax_id
3015 , pv_tax_type => match_inclu_tax_csr.tax_type
3016 , pn_org_id => ln_org_id
3017 );
3018 insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
3019 , pd_accounting_date => ld_gl_date
3020 , pv_currency_code => lv_invoice_currency_code
3021 , pn_enter_dr => match_inclu_tax_csr.tax_amount
3022 , pd_transaction_date => ld_invoice_date
3023 , pn_code_combination_id => ln_tax_account_id
3024 , pd_currency_conversion_date => ld_exchange_date
3025 , pv_currency_conversion_type => lv_exchange_rate_type
3026 , pv_currency_conversion_rate => ln_exchange_rate
3027 , pv_reference1 => lv_inv_org_code
3028 , pv_reference10 => lv_invoice_num
3029 , pv_reference23 => lv_procedure_name
3030 , pv_reference26 => pn_invoice_id
3031 , pv_reference27 => ln_inv_org_id
3032 ) ;
3033
3034 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + match_inclu_tax_csr.tax_amount;
3035 IF (ln_proc_level >= ln_dbg_level)
3036 THEN
3037 FND_LOG.STRING( ln_proc_level
3038 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3039 , 'Inclusive tax account = '|| ln_tax_account_id
3040 );
3041 FND_LOG.STRING( ln_proc_level
3042 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3043 , 'Inclusive tax amount = '|| match_inclu_tax_csr.tax_amount
3044 );
3045 END IF; --ln_proc_level>=l_dbg_level
3046 END LOOP; -- for match_inclu_tax_cur cursor
3047 END IF; -- lv_match_type = 'STANDALONE_INVOICE'
3048
3049 -- Get item distribution line dist_code_combination_id and amount.
3050 -- and insert credit data into GL interface table.
3051 FOR item_line_dist_csr IN item_line_dist_cur(ln_invoice_line_num)
3052 LOOP
3053 IF ln_item_line_amt <> 0
3054 THEN
3055 ln_cr_line_amt := ( item_line_dist_csr.amount / ln_item_line_amt )
3056 * ln_total_inclu_tax_amt;
3057
3058 insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
3059 , pd_accounting_date => ld_gl_date
3060 , pv_currency_code => lv_invoice_currency_code
3061 , pn_enter_cr => ln_cr_line_amt
3062 , pd_transaction_date => ld_invoice_date
3063 , pn_code_combination_id => item_line_dist_csr.dist_code_combination_id
3064 , pd_currency_conversion_date => ld_exchange_date
3065 , pv_currency_conversion_type => lv_exchange_rate_type
3066 , pv_currency_conversion_rate => ln_exchange_rate
3067 , pv_reference1 => lv_inv_org_code
3068 , pv_reference10 => lv_invoice_num
3069 , pv_reference23 => lv_procedure_name
3070 , pv_reference26 => pn_invoice_id
3071 , pv_reference27 => ln_inv_org_id
3072 ) ;
3073 ln_total_cr_line_amt := ln_total_cr_line_amt + ln_cr_line_amt;
3074 END IF; -- ln_item_line_amt <> 0
3075 END LOOP; -- for item_line_dist_cur cursor
3076
3077 END IF; -- ln_invoice_post_num = 1
3078
3079 END IF; -- ln_invoice_line_num > 0
3080
3081 xv_process_flag := 'SS';
3082 xv_process_message := 'Inclusive taxes have successed into GL Interface';
3083 ELSE
3084 xv_process_flag := 'SS';
3085 xv_process_message := 'Inclusive taxes not be separately';
3086 END IF; -- lv_inclu_tax_flag = 'Y'
3087
3088 --logging for debug
3089 IF (ln_proc_level >= ln_dbg_level)
3090 THEN
3091 FND_LOG.STRING( ln_proc_level
3092 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
3093 , 'Exit procedure'
3094 );
3098 WHEN OTHERS THEN
3095 END IF; -- (ln_proc_level>=ln_dbg_level)
3096
3097 EXCEPTION
3099 xv_process_flag := 'UE';
3100 xv_process_message := 'Exception error in acct_inclu_taxes procedure';
3101
3102 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3103 THEN
3104 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
3105 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
3106 , Sqlcode||Sqlerrm);
3107 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3108
3109 END acct_inclu_taxes;
3110
3111 -- Added by Jia Li on tax inclusive computation on 2007/12/17, End
3112
3113 END jai_ap_utils_pkg ;