[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.35.12020000.3 2013/03/25 01:55:39 cholei ship $ */
3
4
5
6 /* --------------------------------------------------------------------------------------
7 Filename:
8
9 Change History:
10
11 Date Remarks
12 ------------------------------------------------------------------------------------------------------
13 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in
14 place of Old DB Entity Names as required for CASE COMPLAINCE.
15
16 14-Jun-2005 rchandan for bug#4428980, Version 116.4
17 Modified the object to remove literals from DML statements and CURSORS.
18
19 23-Jun-2005 Brathod , File Version 112.0 , Bug# 4445989
20 - Signature for procedure get_aportion_factor is modified to use invoice_id and
21 invoice_line_number
22 - Code modified to fetch the details from ap_invoice_lines_all
23 instead of ap_invoice_distributions_all
24
25 02-Sep-2005 Ramananda for Bug#4584221, File Version 120.2
26 Added the new function get_tds_invoice_batch
27 In the form regime registrations (JAIREGIM.fmb) attribute_value field is a free flowing text.
28 In function get_tds_invoice_batch we have considered the values to be 'YES' or 'Y' to get the batch name
29
30 Dependency (Functional)
31 ----------------------
32 jai_ap_utils.pls (120.2)
33 jai_ap_tds_old.plb (120.3)
34 jai_ap_tds_gen.plb (120.8)
35 jai_constants.pls (120.3)
36 jaiorgdffsetup.sql (120.2)
37 jaivmlu.ldt
38
39 3 07/12/2005 Hjujjuru for Bug 4870243, File version 120.5
40 Issue : Invoice Import Program is rejecting the Invoices.
41 Fix : Commented the voucher_num insert into the ap_invoices_interface table
42 4 23/02/2007 bduvarag for Bug#4990941, File version 120.8
43 Forward porting the changes done in 11i bug 4709459
44 5 04/11/2007 bduvarag for Bug#5607160, File version 120.9
45 Forward porting the changes done in 11i bug#5591827
46 6 04/17/2007 vkaranam for Bug#5989740, File version 120.10
47 Forward porting the changes done in 11i bug#5583832
48
49 7 04-Jul-2007 kukumar for bug# 5593895, File version 120.12,120.13 ( brathod changed for 120.11 )
50 Projects changes are not included in this checkin and GSCC error resolved.
51
52 8 04-Jul-2007 Forward porting iSupplier changes
53 Forward porting the changes done in 11i bug#5961325 bug#3637364
54
55 9 17-DEC-2007 Jia Li for Tax inclusive computation
56
57 10 24-Jan-2008 Modifed by Jason Liu for retroactive price
58
59 11 14-APR-2008 Kevin Cheng for bug#6962018
60 change return value from 1 to ratio of AP invoice quantity to PO item quantity for
61 partially recoverable issue.
62 12 11-Mar-2011 Wenqiong for bug #1168411,File version 120.11
63 Updated create_boe_invoice, and added new procedure/function ap_void_check,
64 get_boe_applied_status,check_boe_payment.
65
66 13 24-NOV-2011 mmurtuza for bug 13423031
67 Description: AP - HEADER LEVEL LE DOES NOT COME CORRECT AT THE TIME OF TDS GENERATED INVOICES
68 Fix: Added cursor cur_get_base_legal_entity_id in insert_ap_inv_interface
69 procedure to populate legal_entity_id from the base invoice
70
71 14 15-MAR-2013 zhiwei.xin Forward porting for DTC ER bug#13359892
72 ---------------------------------------------------------------------------------------------------------
73 */
74 GV_MODULE_PREFIX CONSTANT VARCHAR2(30) := 'jai_ap_utils_pkg'; -- -- Added by Jia Li for tax inclusive computation on 2007/12/26
75
76 PROCEDURE create_pla_invoice(P_PLA_ID IN NUMBER,
77 P_SET_OF_BOOK_ID IN NUMBER, P_ORG_ID IN NUMBER) AS
78
79
80 CURSOR counter_cur(inv_id NUMBER) IS
81 SELECT NVL(MAX(line_number),0) + 1 line_num
82 FROM ap_invoice_lines_interface
83 WHERE invoice_id = inv_id;
84
85 CURSOR for_accounting_date(id NUMBER) IS
86 SELECT jibh.tr6_date
87 FROM JAI_CMN_RG_PLA_HDRS jibh,
88 PO_VENDORS pv,
89 PO_VENDOR_SITES_ALL pvs
90 WHERE jibh.PLA_ID = id
91 AND pvs.vendor_site_id (+)= jibh.vendor_site_id
92 AND pv.vendor_id = jibh.vendor_id;
93
94 CURSOR for_invoice_num IS
95 SELECT 'PLA/Invoice/'||TO_CHAR(p_org_id) inv_num
96 FROM DUAL;
97
98 /* Bug 4928860. Added by Lakshmi Gopalsami
99 Removed select and added cursor.
100 */
101 CURSOR multi_org_installed is
102 SELECT decode(multi_org_flag, 'Y', 1,0) multi_org_cnt
103 FROM fnd_product_groups;
104
105 p_rep_head_id NUMBER;
106 p_currency_code VARCHAR2(15);
107 cnt_rec NUMBER;
108 inv_interface_id NUMBER;
109 modvat NUMBER;
110 counter_cur_rec counter_cur%ROWTYPE;
111 inv_line_interface_id NUMBER;
112 for_accounting_date_rec for_accounting_date%ROWTYPE;
113 for_invoice_num_rec for_invoice_num%ROWTYPE;
114 count_orgs NUMBER :=0 ; -- Bug 4928860
115 v_org_id NUMBER;
116 lv_source AP_INVOICES_INTERFACE.source%TYPE ;
117 lv_lookup_type_code ap_invoices_interface.invoice_type_lookup_code%TYPE; --rchandan for bug#4428980
118 lv_description ap_invoices_interface.description%type; --rchandan for bug#4428980
119
120 /* start additions by ssumaith - bug# 4448789 */
121 ln_legal_entity_id NUMBER;
122 lv_legal_entity_name VARCHAR2(240);
123 lv_return_status VARCHAR2(100);
124 ln_msg_count NUMBER;
125 ln_msg_data VARCHAR2(1000);
126 /* ends additions by ssumaith - bug# 4448789*/
127
128 /*-------------------------------------------------------------------------------------------------------------------------
129 FILENAME: ja_in_ins_aplah_aplal_pla_p.sql
130 CHANGE HISTORY:
131
132 S.No Date Author and Details
133 ----------------------------------------------
134 1 24-oct-2002 Aparajita Das for bug # 2639278
135 Populating the siource in ap_invoices_header as "EXTERNAL" instead of "External".
136
137 -------------------------------------------------------------------------------------------------------------------------*/
138
139 BEGIN
140
141 /* Bug 4928860. Added by Lakshmi Gopalsami
142 Removed the count(distinct(org_id) from ap_invoices_all
143 and added the cursor on fnd_product_groups to find out whether
144 multi-org is enabled or not.
145 */
146 OPEN multi_org_installed;
147 FETCH multi_org_installed INTO count_orgs;
148 CLOSE multi_org_installed;
149
150 IF count_orgs = 0 THEN
151 v_org_id := '' ;
152 ELSE
153 v_org_id := p_org_id;
154 END IF;
155
156 Select ap_invoices_interface_s.nextval
157 Into inv_interface_id
158 From dual;
159
160 SELECT ap_invoice_lines_interface_s.NEXTVAL
161 INTO inv_line_interface_id
162 FROM DUAL;
163
164 Select currency_code
165 Into p_currency_code
166 From gl_sets_of_books
167 Where set_of_books_id = P_SET_OF_BOOK_ID;
168
169 OPEN for_invoice_num;
170 FETCH for_invoice_num INTO for_invoice_num_rec;
171 CLOSE for_invoice_num;
172
173 /* start additions by ssumaith - bug# 4448789 */
174 jai_cmn_utils_pkg.GET_LE_INFO(
175 P_API_VERSION => NULL ,
176 P_INIT_MSG_LIST => NULL ,
177 P_COMMIT => NULL ,
178 P_LEDGER_ID => P_SET_OF_BOOK_ID,
179 P_BSV => NULL,
180 P_ORG_ID => v_ORG_ID,
181 X_RETURN_STATUS => lv_return_status ,
182 X_MSG_COUNT => ln_msg_count,
183 X_MSG_DATA => ln_msg_data,
184 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
185 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
186 );
187 /* ends additions by ssumaith - bug# 4448789*/
188
189 /* Bug 5359044. Added by Lakshmi Gopalsami
190 * Changed the 'EXTERNAL' TO 'INDIA - BOE/PLA INVOICES'
191 */
192 lv_source :='INDIA - BOE/PLA INVOICES';
193
194 Insert into AP_INVOICES_INTERFACE
195 (
196 invoice_id ,
197 invoice_num,
198 invoice_date,
199 vendor_id,
200 vendor_site_id,
201 invoice_amount,
202 invoice_currency_code,
203 accts_pay_code_combination_id,
204 source,
205 org_id,
206 legal_entity_id , /*added by ssumaith - bug# 4448789 */
207 created_by,
208 creation_date,
209 last_updated_by,
210 last_update_date
211 )
212 SELECT
213 inv_interface_id , -- REPORT_HEADER_ID,
214 for_invoice_num_rec.inv_num||'/'||jibh.PLA_ID, -- INVOICE_NUM,
215 jibh.TR6_DATE, -- (Invoice Date ) WEEK_END_DATE,
216 jibh.VENDOR_ID, -- VENDOR_ID,
217 jibh.VENDOR_SITE_ID, -- VENDOR_SITE_ID,
218 jibh.PLA_AMOUNT, -- TOTAL,
219 p_currency_code, -- DEFAULT_CURRENCY_CODE,
220 -- Bug 5141305. Added by Lakshmi Gopalsami
221 -- Removed the reference to accts_pay_code_combination_id of po_vendors
222 pvs.ACCTS_PAY_CODE_COMBINATION_ID,
223 lv_source,
224 v_ORG_ID, -- ORG_ID
225 ln_legal_entity_id , -- LEGAL_ENTITY_ID
226 jibh.CREATED_BY, -- CREATED_BY,
227 jibh.CREATION_DATE, -- CREATION_DATE,
228 jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
229 jibh.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
230 FROM JAI_CMN_RG_PLA_HDRS jibh,
231 PO_VENDORS pv,
232 PO_VENDOR_SITES_ALL pvs
233 WHERE jibh.PLA_ID = P_PLA_ID
234 AND pvs.vendor_site_id (+)= jibh.vendor_site_id
235 AND pv.vendor_id = jibh.vendor_id
236 AND NVL(pvs.org_id, 0) = NVL(v_org_id, 0);
237
238 SELECT count(*)
239 into cnt_rec
240 FROM JAI_CMN_RG_PLA_HDRS jibh,
241 JAI_CMN_INVENTORY_ORGS org
242 WHERE jibh.PLA_ID = P_PLA_ID
243 AND org.organization_id = jibh.organization_id
244 AND org.location_id = jibh.location_id;
245
246 OPEN counter_cur(inv_interface_id);
247 FETCH counter_cur INTO counter_cur_rec;
248 CLOSE counter_cur;
249
250 OPEN for_accounting_date(p_pla_id);
251 FETCH for_accounting_date INTO for_accounting_date_rec;
252 CLOSE for_accounting_date;
253
254 if cnt_rec = 0 then
255 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
256 lv_description := 'Line for Invoice no ' || P_PLA_ID; --rchandan for bug#4428980
257 INSERT INTO ap_invoice_lines_interface
258 (
259 invoice_id,
260 invoice_line_id,
261 line_number,
262 line_type_lookup_code,
263 amount,
264 accounting_date,
265 description,
266 dist_code_combination_id,
267 org_id,
268 amount_includes_tax_flag,
269 created_by,
270 creation_date,
271 last_updated_by,
272 last_update_date,
273 last_update_login
274 )
275 SELECT
276 inv_interface_id, -- REPORT_HEADER_ID,
277 inv_line_interface_id,
278 counter_cur_rec.line_num,
279 lv_lookup_type_code, -- LINE_TYPE_LOOKUP_CODE, --rchandan for bug#4428980
280 jibh.PLA_AMOUNT, -- AMOUNT,
281 trunc(for_accounting_date_rec.tr6_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
282 lv_description, -- ITEM_DESCRIPTION, --rchandan for bug#4428980
283 org.MODVAT_PLA_ACCOUNT_ID, -- ACCTS_PAY_CODE_COMBINATION_ID,
284 v_ORG_ID, -- ORG_ID,
285 'N', -- AMOUNT_INCLUDES_TAX_FLAG,
286 jibh.CREATED_BY, -- CREATED_BY,
287 jibh.CREATION_DATE, -- CREATION_DATE,
288 jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
289 jibh.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE,
290 NULL -- LAST_UPDATE_LOGIN
291 FROM JAI_CMN_RG_PLA_HDRS jibh,
292 JAI_CMN_INVENTORY_ORGS org
293 WHERE jibh.PLA_ID = P_PLA_ID
294 AND org.organization_id = jibh.organization_id
295 AND org.location_id = 0 ;
296
297 else
298 lv_lookup_type_code := 'ITEM';--rchandan for bug#4428980
299 lv_description := 'Line for Invoice no ' || P_PLA_ID;--rchandan for bug#4428980
300 INSERT INTO ap_invoice_lines_interface
301 (
302 invoice_id,
303 invoice_line_id,
304 line_number,
305 line_type_lookup_code,
306 amount,
307 accounting_date,
308 description,
309 dist_code_combination_id,
310 org_id,
311 amount_includes_tax_flag,
312 created_by,
313 creation_date,
314 last_updated_by,
315 last_update_date,
316 last_update_login
317 )
318 SELECT
319 inv_interface_id, -- REPORT_HEADER_ID,
320 inv_line_interface_id,
321 counter_cur_rec.line_num,
322 lv_lookup_type_code, -- LINE_TYPE_LOOKUP_CODE, --rchandan for bug#4428980
323 jibh.PLA_AMOUNT, -- AMOUNT,
324 trunc(for_accounting_date_rec.tr6_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
325 lv_description, -- ITEM_DESCRIPTION, --rchandan for bug#4428980
326 org.MODVAT_PLA_ACCOUNT_ID, -- ACCTS_PAY_CODE_COMBINATION_ID,
327 v_ORG_ID, -- ORG_ID,
328 'N', -- AMOUNT_INCLUDES_TAX_FLAG,
329 jibh.CREATED_BY, -- CREATED_BY,
330 jibh.CREATION_DATE, -- CREATION_DATE,
331 jibh.LAST_UPDATED_BY, -- LAST_UPDATED_BY,
332 jibh.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE,
333 NULL -- LAST_UPDATE_LOGIN
334 FROM JAI_CMN_RG_PLA_HDRS jibh,
335 JAI_CMN_INVENTORY_ORGS org
336 WHERE jibh.PLA_ID = P_PLA_ID
337 AND org.organization_id = jibh.organization_id
338 AND org.location_id = jibh.location_id;
339
340 end if;
341
342 END create_pla_invoice;
343
344 PROCEDURE create_boe_invoice
345 (
346 P_BOE_ID IN NUMBER,
347 P_SET_OF_BOOK_ID IN NUMBER,
348 P_ORG_ID IN NUMBER
349 )
350 IS
351
352 CURSOR counter_cur(inv_id NUMBER) IS
353 SELECT NVL(MAX(line_number),0) + 1 line_num
354 FROM ap_invoice_lines_interface
355 WHERE invoice_id = inv_id;
356
357 CURSOR for_invoice_num IS
358 SELECT 'BOE/Invoice/'||TO_CHAR(p_org_id)||'/'||TO_CHAR(P_BOE_ID) inv_num
359 FROM DUAL; --Added on 21-Feb-2002
360
361 /* Bug 4928860. Added by Lakshmi Gopalsami
362 Removed select and added cursor.
363 */
364 CURSOR multi_org_installed is
365 SELECT decode(multi_org_flag, 'Y', 1,0) multi_org_cnt
366 FROM fnd_product_groups;
367
368
369 inv_interface_id NUMBER;
370 inv_line_interface_id NUMBER;
371 p_currency_code VARCHAR(15);
372 cnt_rec NUMBER;
373 counter_cur_rec counter_cur%ROWTYPE;
374 for_invoice_num_rec for_invoice_num%ROWTYPE;
375 count_orgs NUMBER :=0 ; -- Bug 4928860
376 v_org_id NUMBER;
377 lv_description ap_invoices_interface.description%type; -- Ravi for literal removal
378 lv_lookup_type_code ap_invoices_interface.invoice_type_lookup_code%TYPE; --Ravi for literal removal
379 lv_source ap_invoices_interface.source%type; --Ravi for literal removal
380 --Added by Wenqiong for Bug #1168411 BOE enhancement
381 lv_tax_account_code ap_invoice_lines_interface.dist_code_combination_id%TYPE;
382
383 /* start additions by ssumaith - bug# 4448789 */
384 ln_legal_entity_id NUMBER;
385 lv_legal_entity_name VARCHAR2(240);
386 lv_return_status VARCHAR2(100);
387 ln_msg_count NUMBER;
388 ln_msg_data VARCHAR2(1000);
389 /* ends additions by ssumaith - bug# 4448789*/
390
391 --Add for Bug #1168411 BOE enhancement by Wenqiong Zhou Begin
392 --------------------------------------------------------
393 CURSOR get_tax_amount_cur(pn_boe_id NUMBER) IS
394 SELECT jct.tax_type,
395 SUM(nvl(jbr.total_amount, jbr.tax_amount)) boe_amount
396 FROM jai_boe_roundings jbr, jai_cmn_taxes_all jct
397 WHERE jbr.boe_id = pn_boe_id
398 AND jbr.tax_id = jct.tax_id
399 GROUP BY jct.tax_type;
400
401 CURSOR get_import_date_cur(pn_boe_id NUMBER) IS
402 SELECT import_date, organization_id, location_id,depb_amount
403 FROM jai_cmn_boe_hdrs
404 WHERE boe_id = pn_boe_id;
405
406 get_tax_amount_rec get_tax_amount_cur%ROWTYPE;
407 ld_import_date DATE;
408 ln_organization_id NUMBER;
409 ln_location_id NUMBER;
410 ln_depb_account_id ap_invoice_lines_interface.dist_code_combination_id%TYPE;
411 ln_depb_amount ap_invoice_lines_interface.amount%TYPE;
412 ----------------------------------------------------
413 --Add for Bug #1168411 BOE enhancement by Wenqiong Zhou End
414
415 BEGIN
416
417
418 /*------------------------------------------------------------------------------------------------------------------
419 FILENAME: ja_ins_aerha_aerla_p.sql
420 CHANGE HISTORY:
421
422 S.No Date Author and Details
423 ----------------------------------------------
424 1 21-Feb-2002 RPK:. Version#610.1
425 for the issue of the BOE invoice nums getting stuck up in the interfaces
426 with the reason 'duplicate invoice nums'.
427
428 2 08-MAY-2002 Aparajita for bug 2361769. Version#614.1
429 voucher number field of BOE invoice was not getting populated, populated it with the
430 same value as invoice number.
431
432 3 24-oct-2002 Aparajita Das for bug # 2639278. Version#615.1
433 Populating the source in ap_invoices_header as "EXTERNAL" instead of "External".
434
435 4 22/07/2003 Vijay Shankar for bug#3049198. Version#616.1
436
437 Accounting date for Invoice distributions should be the IMPORT_DATE instead of bol_date.
438 GL_DATE of INVOICE should be populated with IMPORT_DATE which is not happening previously
439 Also INVOICE_DATE of the Invoice is populated with IMPORT_DATE
440 - Removed the definition of cursor for_accounting_date as it was not required.
441 5 10/04/2007 bduvarag for bug#5607160,File version 120.9
442 Forward porting the changes done in 11i bug#5591827
443
444 6 09-JAN-2009 Bug 6503442 (FP for bug 6282935) - File version 120.22
445 Included logic to populate the invoice_num field in jai_cmn_boe_hdrs.
446 This fix involves the addition of new column in jai_cmn_boe_hdrs, and will
447 be a dependency for all future fixes.
448
449 -------------------------------------------------------------------------------------------------------------------*/
450
451 /* Bug 4928860. Added by Lakshmi Gopalsami
452 Removed the count(distinct(org_id) from ap_invoices_all
453 and added the cursor on fnd_product_groups to find out whether
454 multi-org is enabled or not.
455 */
456
457 OPEN multi_org_installed;
458 FETCH multi_org_installed INTO count_orgs;
459 CLOSE multi_org_installed;
460
461 IF count_orgs = 0 THEN
462 v_org_id := '' ;
463 ELSE
464 v_org_id := p_org_id;
465 END IF;
466
467 SELECT ap_invoices_interface_s.NEXTVAL
468 INTO inv_interface_id
469 FROM dual;
470
471 SELECT ap_invoice_lines_interface_s.NEXTVAL
472 INTO inv_line_interface_id
473 FROM DUAL;
474
475 SELECT currency_code
476 INTO p_currency_code
477 FROM gl_sets_of_books
478 WHERE set_of_books_id = p_set_of_book_id;
479
480 OPEN for_invoice_num;
481 FETCH for_invoice_num INTO for_invoice_num_rec;
482 CLOSE for_invoice_num;
483
484
485
486 /* start additions by ssumaith - bug# 4448789 */
487 jai_cmn_utils_pkg.GET_LE_INFO(
488 P_API_VERSION => NULL ,
489 P_INIT_MSG_LIST => NULL ,
490 P_COMMIT => NULL ,
491 P_LEDGER_ID => P_SET_OF_BOOK_ID,
492 P_BSV => NULL,
493 P_ORG_ID => v_ORG_ID,
494 X_RETURN_STATUS => lv_return_status ,
495 X_MSG_COUNT => ln_msg_count,
496 X_MSG_DATA => ln_msg_data,
497 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
498 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
499 );
500 /* ends additions by ssumaith - bug# 4448789*/
501
502 /* Bug 5359044. Added by Lakshmi Gopalsami
503 * Changed the 'EXTERNAL' TO 'INDIA - BOE/PLA INVOICES'
504 */
505
506 lv_source := 'INDIA - BOE/PLA INVOICES';
507
508 INSERT INTO AP_INVOICES_INTERFACE
509 (
510 invoice_id,
511 invoice_num,
512 -- voucher_num, -- added by Aparajita on 08-may-2002 bug 2361769 Harshita for Bug 4870243
513 invoice_date,
514 invoice_type_lookup_code, -- Added by Bo Li for Bug #1168411 BOE enhancement
515 vendor_id,
516 vendor_site_id,
517 invoice_amount,
518 invoice_currency_code,
519 accts_pay_code_combination_id,
520 --set_of_books_id,
521 source,
522 gl_date, -- Vijay Shankar for bug#3049198
523 --accounting_date,
524 org_id,
525 legal_entity_id ,
526 created_by,
527 creation_date,
528 last_updated_by,
529 last_update_date
530 -- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement Begin
531 ,
532 application_id,
533 product_table,
534 reference_key1,
535 reference_key2,
536 reference_key3,
537 reference_key4,
538 reference_key5
539 -- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
540 )
541 SELECT
542 inv_interface_id, -- invoice_interface_header_id,
543 for_invoice_num_rec.inv_num, -- invoice_num, --added on 21-feb-2002
544 -- for_invoice_num_rec.inv_num, -- added for voucher number, same as invoice number by aparajita Harshita for Bug 4870243
545 -- trunc(jibh.bol_date),
546 trunc(jibh.import_date), -- Vijay Shankar for bug#3049198
547 'STANDARD', -- Added by Bo Li for Bug #1168411 BOE enhancement
548 -- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
549 --------------------------------------------------------------------
550 -- jibh.vendor_id,
551 -- jibh.vendor_site_id,
552 jibh.customs_authority_id,
553 jibh.customs_authority_site_id,
554 --------------------------------------------------------------------
555 -- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
556 jibh.boe_amount - nvl(jibh.depb_amount,0), -- total,/*Bug 5607160 bduvarag*/
557 p_currency_code, -- default_currency_code,
558 -- Bug 5141305. Added by Lakshmi Gopalsami
559 -- Removed the reference to accts_pay_code_combination_id of po_vendors
560 pvs.ACCTS_PAY_CODE_COMBINATION_ID,
561 lv_source,
562 trunc(jibh.import_date), -- Vijay Shankar for bug#3049198
563 v_org_id , -- org_id,
564 ln_legal_entity_id , -- LEGAL_ENTITY_ID
565 jibh.created_by,
566 trunc(jibh.creation_date),
567 jibh.last_updated_by,
568 trunc(jibh.last_update_date)
569 -- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement Begin
570 ,
571 7000,
572 'JAI_CMN_BOE_HDRS',
573 jibh.boe_id,
574 NULL,
575 NULL,
576 NULL,
577 NULL
578 -- Added by Wenqiong Zhou for Bug #1168411 BOE enhancement End
579 FROM
580 JAI_CMN_BOE_HDRS jibh,
581 po_vendors pv,
582 po_vendor_sites_all pvs
583 where jibh.boe_id = p_boe_id
584 and pvs.vendor_site_id (+)= jibh.vendor_site_id
585 and pv.vendor_id = jibh.vendor_id
586 and nvl(pvs.org_id, 0) = nvl(v_org_id, 0);
587
588 /*bug 6503442 - FP of bug 6282935*/
589 /* Comment out by Qiong Liu for Bug#12400670 begin
590 UPDATE jai_cmn_boe_hdrs
591 SET invoice_num = for_invoice_num_rec.inv_num
592 WHERE boe_id = p_boe_id;
593 Comment out by Qiong Liu for Bug#12400670 end */
594 /*end bug 6503442*/
595
596 /* Comment out by Wenqiong Zhou for Bug #1168411 BOE enhancement Begin
597 select count(*)
598 into cnt_rec
599 from JAI_CMN_BOE_HDRS jibh,
600 JAI_CMN_INVENTORY_ORGS org
601 where jibh.boe_id = p_boe_id
602 and org.organization_id = jibh.organization_id
603 and org.location_id = jibh.location_id;
604
605 open counter_cur(inv_interface_id);
606 fetch counter_cur into counter_cur_rec;
607 close counter_cur;
608
609 IF cnt_rec = 0 THEN
610 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
611 lv_description := 'line for invoice no ' || p_boe_id; --rchandan for bug#4428980
612
613 insert into ap_invoice_lines_interface
614 (
615 invoice_id,
616 invoice_line_id,
617 line_number,
618 line_type_lookup_code,
619 amount,
620 accounting_date,
621 description,
622 dist_code_combination_id,
623 org_id,
624 amount_includes_tax_flag,
625 created_by,
626 creation_date,
627 last_updated_by,
628 last_update_date,
629 last_update_login
630 )
631 SELECT
632 inv_interface_id, -- report_header_id,
633 inv_line_interface_id,
634 counter_cur_rec.line_num,
635 lv_lookup_type_code, -- line_type_lookup_code, --rchandan for bug#4428980
636 round(jibh.boe_amount), -- amount,/*Bug 5607160 bduvarag*/
637 /*
638 jibh.import_date, -- bug#3049198
639 lv_description, -- item_description, --rchandan for bug#4428980
640 org.boe_account_id,
641 v_org_id, -- org_id,
642 'N' , -- amount_includes_tax_flag,
643 jibh.created_by,
644 trunc(jibh.creation_date),
645 jibh.last_updated_by,
646 jibh.last_update_date,
647 null -- last_update_login
648 from JAI_CMN_BOE_HDRS jibh,
649 JAI_CMN_INVENTORY_ORGS org
650 where jibh.boe_id = p_boe_id
651 and org.organization_id = jibh.organization_id
652 AND org.location_id = 0 ;
653
654 ELSE
655 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
656 lv_description := 'Line for Invoice no ' || P_BOE_ID; --rchandan for bug#4428980
657
658 insert into ap_invoice_lines_interface
659 (
660 invoice_id,
661 invoice_line_id,
662 line_number,
663 line_type_lookup_code,
664 amount,
665 accounting_date,
666 description,
667 dist_code_combination_id,
668 org_id,
669 amount_includes_tax_flag,
670 created_by,
671 creation_date,
672 last_updated_by,
673 last_update_date,
674 last_update_login
675 )
676 select
677 inv_interface_id, -- report_header_id,
678 inv_line_interface_id,
679 counter_cur_rec.line_num,
680 lv_lookup_type_code, -- line_type_lookup_code, --rchandan for bug#4428980
681 round(jibh.boe_amount),/*Bug 5607160 bduvarag*/
682 /*
683 jibh.import_date, -- bug#3049198
684 lv_description, -- item_description, --rchandan for bug#4428980
685 org.boe_account_id,
686 v_org_id, -- org_id,
687 'N', -- amount_includes_tax_flag,
688 jibh.created_by,
689 trunc(jibh.creation_date),
690 jibh.last_updated_by,
691 jibh.last_update_date,
692 null -- last_update_login
693 from
694 JAI_CMN_BOE_HDRS jibh,
695 JAI_CMN_INVENTORY_ORGS org
696 where
697 jibh.boe_id = p_boe_id
698 and org.organization_id = jibh.organization_id
699 and org.location_id = jibh.location_id;
700
701 end if;
702 */
703 -- Comment out by Wenqiong Zhou for Bug #1168411 BOE enhancement End
704 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
705 lv_description := 'Line for Invoice no ' || p_boe_id; --rchandan for bug#4428980
706
707 /*Added by Wenqiong Zhou for BOE Enhancement Begin*/
708 OPEN get_import_date_cur(p_boe_id);
709 FETCH get_import_date_cur
710 INTO ld_import_date, ln_organization_id, ln_location_id,ln_depb_amount;
711 CLOSE get_import_date_cur;
712
713
714 FOR get_tax_amount_rec IN get_tax_amount_cur(p_boe_id) LOOP
715
716 OPEN counter_cur(inv_interface_id);
717 FETCH counter_cur
718 INTO counter_cur_rec;
719 CLOSE counter_cur;
720
721 SELECT ap_invoice_lines_interface_s.NEXTVAL
722 INTO inv_line_interface_id
723 FROM dual;
724
725 lv_tax_account_code := jai_boe_general_pkg.get_boe_accounting(get_tax_amount_rec.tax_type,
726 'PAID_PAYABLES',
727 ln_organization_id,
728 ln_location_id);
729
730
731 INSERT INTO ap_invoice_lines_interface
732 (invoice_id,
733 invoice_line_id,
734 line_number,
735 line_type_lookup_code,
736 amount,
737 accounting_date,
738 description,
739 dist_code_combination_id,
740 org_id,
741 amount_includes_tax_flag,
742 created_by,
743 creation_date,
744 last_updated_by,
745 last_update_date,
746 last_update_login)
747 VALUES
748 (inv_interface_id,
749 inv_line_interface_id,
750 counter_cur_rec.line_num,
751 lv_lookup_type_code,
752 get_tax_amount_rec.boe_amount,
753 trunc(ld_import_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
754 lv_description,
755 lv_tax_account_code,
756 v_org_id,
757 'N',
758 fnd_global.user_id,
759 SYSDATE,
760 fnd_global.user_id,
761 SYSDATE,
762 fnd_global.login_id);
763
764 END LOOP;
765 IF ln_depb_amount > 0 THEN
766 ln_depb_amount := - ln_depb_amount;
767 ln_depb_account_id := JAI_BOE_GENERAL_PKG.get_depb_account;
768
769
770 open counter_cur(inv_interface_id);
771 fetch counter_cur into counter_cur_rec;
772 close counter_cur;
773
774 SELECT ap_invoice_lines_interface_s.NEXTVAL
775 INTO inv_line_interface_id
776 FROM DUAL;
777
778 insert into ap_invoice_lines_interface
779 (
780 invoice_id,
781 invoice_line_id,
782 line_number,
783 line_type_lookup_code,
784 amount,
785 accounting_date,
786 description,
787 dist_code_combination_id,
788 org_id,
789 amount_includes_tax_flag,
790 created_by,
791 creation_date,
792 last_updated_by,
793 last_update_date,
794 last_update_login
795 )
796 VALUES(
797 inv_interface_id, -- report_header_id,
798 inv_line_interface_id,
799 counter_cur_rec.line_num,
800 lv_lookup_type_code,
801 ln_depb_amount,
802 trunc(ld_import_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
803 lv_description, -- item_description,
804 ln_depb_account_id,
805 v_org_id, -- org_id,
806 'N', -- amount_includes_tax_flag,
807 fnd_global.login_id,
808 SYSDATE,
809 fnd_global.login_id,
810 SYSDATE,
811 fnd_global.login_id
812 );
813 END IF;
814
815
816 /*Added by Wenqiong Zhou for BOE Enhancement End*/
817 END create_boe_invoice;
818
819 PROCEDURE insert_ap_inv_interface(
820 p_jai_source IN VARCHAR2,
821 p_invoice_id OUT NOCOPY ap_invoices_interface.INVOICE_ID%TYPE,
822 p_invoice_num IN ap_invoices_interface.INVOICE_NUM%TYPE DEFAULT NULL,
823 p_invoice_type_lookup_code IN ap_invoices_interface.INVOICE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
824 p_invoice_date IN ap_invoices_interface.INVOICE_DATE%TYPE DEFAULT NULL,
825 p_po_number IN ap_invoices_interface.PO_NUMBER%TYPE DEFAULT NULL,
826 p_vendor_id IN ap_invoices_interface.VENDOR_ID%TYPE DEFAULT NULL,
827 p_vendor_num IN ap_invoices_interface.VENDOR_NUM%TYPE DEFAULT NULL,
828 p_vendor_name IN ap_invoices_interface.VENDOR_NAME%TYPE DEFAULT NULL,
829 p_vendor_site_id IN ap_invoices_interface.VENDOR_SITE_ID%TYPE DEFAULT NULL,
830 p_vendor_site_code IN ap_invoices_interface.VENDOR_SITE_CODE%TYPE DEFAULT NULL,
831 p_invoice_amount IN ap_invoices_interface.INVOICE_AMOUNT%TYPE DEFAULT NULL,
832 p_invoice_currency_code IN ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE DEFAULT NULL,
833 p_exchange_rate IN ap_invoices_interface.EXCHANGE_RATE%TYPE DEFAULT NULL,
834 p_exchange_rate_type IN ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL,
835 p_exchange_date IN ap_invoices_interface.EXCHANGE_DATE%TYPE DEFAULT NULL,
836 p_terms_id IN ap_invoices_interface.TERMS_ID%TYPE DEFAULT NULL,
837 p_terms_name IN ap_invoices_interface.TERMS_NAME%TYPE DEFAULT NULL,
838 p_description IN ap_invoices_interface.DESCRIPTION%TYPE DEFAULT NULL,
839 p_awt_group_id IN ap_invoices_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
840 p_awt_group_name IN ap_invoices_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
841 p_last_update_date IN ap_invoices_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
842 p_last_updated_by IN ap_invoices_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
843 p_last_update_login IN ap_invoices_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
844 p_creation_date IN ap_invoices_interface.CREATION_DATE%TYPE DEFAULT NULL,
845 p_created_by IN ap_invoices_interface.CREATED_BY%TYPE DEFAULT NULL,
846 --Added below the attribute category and attribute parameters for Bug #3841637
847 p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
848 p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
849 p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
850 p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
851 p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
852 p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
853 p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
854 p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
855 p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
856 p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
857 p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
858 p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
859 p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
860 p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
861 p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
862 p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
863 p_status IN ap_invoices_interface.STATUS%TYPE DEFAULT NULL,
864 p_source IN ap_invoices_interface.SOURCE%TYPE DEFAULT NULL,
865 p_group_id IN ap_invoices_interface.GROUP_ID%TYPE DEFAULT NULL,
866 p_request_id IN ap_invoices_interface.REQUEST_ID%TYPE DEFAULT NULL,
867 p_payment_cross_rate_type IN ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE DEFAULT NULL,
868 p_payment_cross_rate_date IN ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE DEFAULT NULL,
869 p_payment_cross_rate IN ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE DEFAULT NULL,
870 p_payment_currency_code IN ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE DEFAULT NULL,
871 p_workflow_flag IN ap_invoices_interface.WORKFLOW_FLAG%TYPE DEFAULT NULL,
872 p_doc_category_code IN ap_invoices_interface.DOC_CATEGORY_CODE%TYPE DEFAULT NULL,
873 p_voucher_num IN ap_invoices_interface.VOUCHER_NUM%TYPE DEFAULT NULL,
874 p_payment_method_lookup_code IN ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE DEFAULT NULL,
875 p_pay_group_lookup_code IN ap_invoices_interface.PAY_GROUP_LOOKUP_CODE%TYPE DEFAULT NULL,
876 p_goods_received_date IN ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE DEFAULT NULL,
877 p_invoice_received_date IN ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE DEFAULT NULL,
878 p_gl_date IN ap_invoices_interface.GL_DATE%TYPE DEFAULT NULL,
879 p_accts_pay_ccid IN ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
880 p_ussgl_transaction_code IN ap_invoices_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
881 p_exclusive_payment_flag IN ap_invoices_interface.EXCLUSIVE_PAYMENT_FLAG%TYPE DEFAULT NULL,
882 p_org_id IN ap_invoices_interface.ORG_ID%TYPE DEFAULT NULL,
883 p_amount_applicable_to_dis IN ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE DEFAULT NULL,
884 p_prepay_num IN ap_invoices_interface.PREPAY_NUM%TYPE DEFAULT NULL,
885 p_prepay_dist_num IN ap_invoices_interface.PREPAY_DIST_NUM%TYPE DEFAULT NULL,
886 p_prepay_apply_amount IN ap_invoices_interface.PREPAY_APPLY_AMOUNT%TYPE DEFAULT NULL,
887 p_prepay_gl_date IN ap_invoices_interface.PREPAY_GL_DATE%TYPE DEFAULT NULL,
888 -- Bug4240179. Added by LGOPALSA. Changed the data type
889 -- for the following 4 fields.
890 p_invoice_includes_prepay_flag IN VARCHAR2 DEFAULT NULL,
891 p_no_xrate_base_amount IN NUMBER DEFAULT NULL,
892 p_vendor_email_address IN VARCHAR2 DEFAULT NULL,
893 p_terms_date IN DATE DEFAULT NULL,
894 p_requester_id IN NUMBER DEFAULT NULL,
895 p_ship_to_location IN VARCHAR2 DEFAULT NULL,
896 p_external_doc_ref IN VARCHAR2 DEFAULT NULL,
897 -- Bug 7109056. Added by Lakshmi Gopalsami
898 p_payment_method_code IN VARCHAR2 DEFAULT NULL,
899 --Added by Qiong for AP Open Interface of ZX intergration --ported by Chong from 12.1.3 20130325
900 p_Calc_Tax_During_Import_Flag IN VARCHAR2 DEFAULT NULL
901 ) IS
902
903 lv_object_name VARCHAR2(61); -- := 'jai_ap_utils_pkg.insert_ap_inv_interface'; /* Added by Ramananda for bug#4407165 */
904
905
906 /* start additions by ssumaith - bug# 4448789 */
907 ln_legal_entity_id NUMBER;
908 lv_legal_entity_name VARCHAR2(240);
909 lv_return_status VARCHAR2(100);
910 ln_msg_count NUMBER;
911 ln_msg_data VARCHAR2(1000);
912 /* ends additions by ssumaith - bug# 4448789*/
913
914 /*Added below cursor by mmurtuza for bug 13423031*/
915 cursor cur_get_base_legal_entity_id is
916 select legal_entity_id from ap_invoices_all
917 WHERE invoice_id = p_attribute1;
918
919
920
921 BEGIN
922 -- #****************************************************************************************************************************************************************************************
923 -- #
924 -- # Change History -
925 -- # 1. 27-Jan-2005 Sanjikum for Bug #4059774 Version #115.0
926 -- # New Package created for creating AP Invoice Header and lines
927 -- #
928 -- # 2. 17-Feb-2005 Sanjikum for Bug #4183001 Version #115.1
929 -- #
930 -- # Issue -
931 -- # In Base version 11.5.3, 3 columns are not present in tables ap_invoices_interface and insert_ap_inv_lines_interface
932 -- #
933 -- # Fix -
934 -- # a) In the Definition of Procedure insert_ap_inv_interface, changed the type of 3 parameters -
935 -- # p_requester_id, p_ship_to_location, p_external_doc_ref
936 -- # b) In the Insert statement in procedure insert_ap_inv_interface, commented the insert for 3 columns -
937 -- # requester_id, ship_to_location, external_doc_ref
938 -- # c) In the Definition of Procedure insert_ap_inv_lines_interface, changed the type of 3 parameters -
939 -- # p_taxable_flag, p_price_correct_inv_num, p_external_doc_line_ref
940 -- # d) In the Insert statement in procedure insert_ap_inv_lines_interface, commented the insert for 3 columns -
941 -- # taxable_flag, price_correct_inv_num, external_doc_line_ref
942 -- #
943 -- # 3. 25-Mar-2005 Sanjikum for Bug #3841637 Version 115.4
944 -- # Added the Attribute category and 15 attributes columns
945 -- #
946 -- # Future Dependencies For the release Of this Object:-
947 -- # (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/
948 -- # A datamodel change )
949 --==============================================================================================================
950 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
951 -- # Current Version Current Bug Dependent Files Version Author Date Remarks
952 -- # Of File On Bug/Patchset Dependent On
953 -- # jai_ap_interface_pkg_b.sql
954 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
955 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
956 -- # ****************************************************************************************************************************************************************************************
957
958 lv_object_name := 'jai_ap_utils_pkg.insert_ap_inv_interface'; /* Added by Ramananda for bug#4407165 */
959
960 /*Start additions by mmurtuza for bug 13423031*/
961 open cur_get_base_legal_entity_id;
962 fetch cur_get_base_legal_entity_id into ln_legal_entity_id;
963 close cur_get_base_legal_entity_id;
964 /*End additions by mmurtuza for bug 13423031*/
965
966
967 if(ln_legal_entity_id is null) then --by mmurtuza for bug 13423031
968 /* start additions by ssumaith - bug# 4448789 */
969 jai_cmn_utils_pkg.GET_LE_INFO(
970 P_API_VERSION => NULL ,
971 P_INIT_MSG_LIST => NULL ,
972 P_COMMIT => NULL ,
973 P_LEDGER_ID => NULL,
974 P_BSV => NULL,
975 P_ORG_ID => p_org_id,
976 X_RETURN_STATUS => lv_return_status ,
977 X_MSG_COUNT => ln_msg_count,
978 X_MSG_DATA => ln_msg_data,
979 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
980 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
981 );
982 /* ends additions by ssumaith - bug# 4448789*/
983 end if; --by mmurtuza for bug 13423031
984
985
986
987 INSERT INTO ap_invoices_interface(
988 INVOICE_ID,
989 INVOICE_NUM,
990 INVOICE_TYPE_LOOKUP_CODE,
991 INVOICE_DATE,
992 PO_NUMBER,
993 VENDOR_ID,
994 VENDOR_NUM,
995 VENDOR_NAME,
996 VENDOR_SITE_ID,
997 VENDOR_SITE_CODE,
998 INVOICE_AMOUNT,
999 INVOICE_CURRENCY_CODE,
1000 EXCHANGE_RATE,
1001 EXCHANGE_RATE_TYPE,
1002 EXCHANGE_DATE,
1003 TERMS_ID,
1004 TERMS_NAME,
1005 DESCRIPTION,
1006 AWT_GROUP_ID,
1007 AWT_GROUP_NAME,
1008 LAST_UPDATE_DATE,
1009 LAST_UPDATED_BY,
1010 LAST_UPDATE_LOGIN,
1011 CREATION_DATE,
1012 CREATED_BY,
1013 --Added below the attribute category and attribute columns for Bug #3841637
1014 ATTRIBUTE_CATEGORY,
1015 ATTRIBUTE1,
1016 ATTRIBUTE2,
1017 ATTRIBUTE3,
1018 ATTRIBUTE4,
1019 ATTRIBUTE5,
1020 ATTRIBUTE6,
1021 ATTRIBUTE7,
1022 ATTRIBUTE8,
1023 ATTRIBUTE9,
1024 ATTRIBUTE10,
1025 ATTRIBUTE11,
1026 ATTRIBUTE12,
1027 ATTRIBUTE13,
1028 ATTRIBUTE14,
1029 ATTRIBUTE15,
1030 STATUS,
1031 SOURCE,
1032 GROUP_ID,
1033 REQUEST_ID,
1034 PAYMENT_CROSS_RATE_TYPE,
1035 PAYMENT_CROSS_RATE_DATE,
1036 PAYMENT_CROSS_RATE,
1037 PAYMENT_CURRENCY_CODE,
1038 WORKFLOW_FLAG,
1039 DOC_CATEGORY_CODE,
1040 -- VOUCHER_NUM, Harshita for Bug 4870243
1041 PAYMENT_METHOD_CODE, -- Bug 7109056. added by Lakshmi gopalsami
1042 PAY_GROUP_LOOKUP_CODE,
1043 GOODS_RECEIVED_DATE,
1044 INVOICE_RECEIVED_DATE,
1045 GL_DATE,
1046 ACCTS_PAY_CODE_COMBINATION_ID,
1047 USSGL_TRANSACTION_CODE,
1048 EXCLUSIVE_PAYMENT_FLAG,
1049 ORG_ID,
1050 LEGAL_ENTITY_ID , /* added by ssumaith - bug# 4448789*/
1051 AMOUNT_APPLICABLE_TO_DISCOUNT,
1052 PREPAY_NUM,
1053 PREPAY_DIST_NUM,
1054 PREPAY_APPLY_AMOUNT,
1055 PREPAY_GL_DATE,
1056 Calc_Tax_During_Import_Flag --Added by Qiong for AP open Interface --ported by Chong from 12.1.3 20130325
1057 /* , Bug4240179. Added by LGOPALSA
1058 Commented the following 4 fields*/
1059 --INVOICE_INCLUDES_PREPAY_FLAG,
1060 --NO_XRATE_BASE_AMOUNT,
1061 --VENDOR_EMAIL_ADDRESS,
1062 --TERMS_DATE
1063 /*,
1064 REQUESTER_ID,
1065 SHIP_TO_LOCATION,
1066 EXTERNAL_DOC_REF*/)--commented by Sanjikum for Bug#4183001
1067 VALUES(
1068 ap_invoices_interface_s.NEXTVAL,
1069 p_invoice_num,
1070 p_invoice_type_lookup_code,
1071 p_invoice_date,
1072 p_po_number,
1073 p_vendor_id,
1074 p_vendor_num,
1075 p_vendor_name,
1076 p_vendor_site_id,
1077 p_vendor_site_code,
1078 p_invoice_amount,
1079 p_invoice_currency_code,
1080 p_exchange_rate,
1081 p_exchange_rate_type,
1082 p_exchange_date,
1083 p_terms_id,
1084 p_terms_name,
1085 p_description,
1086 p_awt_group_id,
1087 p_awt_group_name,
1088 p_last_update_date,
1089 p_last_updated_by,
1090 p_last_update_login,
1091 p_creation_date,
1092 p_created_by,
1093 --Added below the attribute category and attribute columns for Bug #3841637
1094 p_attribute_category,
1095 p_attribute1,
1096 p_attribute2,
1097 p_attribute3,
1098 p_attribute4,
1099 p_attribute5,
1100 p_attribute6,
1101 p_attribute7,
1102 p_attribute8,
1103 p_attribute9,
1104 p_attribute10,
1105 p_attribute11,
1106 p_attribute12,
1107 p_attribute13,
1108 p_attribute14,
1109 p_attribute15,
1110 p_status,
1111 p_source,
1112 p_group_id,
1113 p_request_id,
1114 p_payment_cross_rate_type,
1115 p_payment_cross_rate_date,
1116 p_payment_cross_rate,
1117 p_payment_currency_code,
1118 p_workflow_flag,
1119 p_doc_category_code,
1120 -- p_voucher_num, Harshita for Bug 4870243
1121 p_payment_method_code, -- Bug 7109056. Added by Lakshmi Gopalsami
1122 p_pay_group_lookup_code,
1123 p_goods_received_date,
1124 p_invoice_received_date,
1125 p_gl_date,
1126 p_accts_pay_ccid,
1127 p_ussgl_transaction_code,
1128 p_exclusive_payment_flag,
1129 p_org_id,
1130 ln_legal_entity_id , /* added by ssumaith - bug# 4448789*/
1131 p_amount_applicable_to_dis,
1132 p_prepay_num,
1133 p_prepay_dist_num,
1134 p_prepay_apply_amount,
1135 p_prepay_gl_date
1136 ,p_Calc_Tax_During_Import_Flag --ported by Chong from 12.1.3 20130325
1137 /* , Bug4240179. Added by LGOPALSA
1138 Commented the following 4 fields*/
1139 --p_invoice_includes_prepay_flag,
1140 --p_no_xrate_base_amount,
1141 --p_vendor_email_address,
1142 --p_terms_date
1143 /*,
1144 p_requester_id,
1145 p_ship_to_location,
1146 p_external_doc_ref*/) --commented by Sanjikum for Bug#4183001
1147 RETURNING invoice_id INTO p_invoice_id;
1148
1149 /* Added by Ramananda for bug#4407165 */
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 p_invoice_id := null;
1153 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1154 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1155 app_exception.raise_exception;
1156
1157 END insert_ap_inv_interface;
1158
1159 PROCEDURE insert_ap_inv_lines_interface(
1160 p_jai_source IN VARCHAR2,
1161 p_invoice_id IN ap_invoice_lines_interface.INVOICE_ID%TYPE,
1162 p_invoice_line_id OUT NOCOPY ap_invoice_lines_interface.INVOICE_LINE_ID%TYPE,
1163 p_line_number IN ap_invoice_lines_interface.LINE_NUMBER%TYPE DEFAULT NULL,
1164 p_line_type_lookup_code IN ap_invoice_lines_interface.LINE_TYPE_LOOKUP_CODE%TYPE DEFAULT NULL,
1165 p_line_group_number IN ap_invoice_lines_interface.LINE_GROUP_NUMBER%TYPE DEFAULT NULL,
1166 p_amount IN ap_invoice_lines_interface.AMOUNT%TYPE DEFAULT NULL,
1167 p_accounting_date IN ap_invoice_lines_interface.ACCOUNTING_DATE%TYPE DEFAULT NULL,
1168 p_description IN ap_invoice_lines_interface.DESCRIPTION%TYPE DEFAULT NULL,
1169 p_amount_includes_tax_flag IN ap_invoice_lines_interface.AMOUNT_INCLUDES_TAX_FLAG%TYPE DEFAULT NULL,
1170 p_prorate_across_flag IN ap_invoice_lines_interface.PRORATE_ACROSS_FLAG%TYPE DEFAULT NULL,
1171 p_tax_code IN ap_invoice_lines_interface.TAX_CODE%TYPE DEFAULT NULL,
1172 p_final_match_flag IN ap_invoice_lines_interface.FINAL_MATCH_FLAG%TYPE DEFAULT NULL,
1173 p_po_header_id IN ap_invoice_lines_interface.PO_HEADER_ID%TYPE DEFAULT NULL,
1174 p_po_number IN ap_invoice_lines_interface.PO_NUMBER%TYPE DEFAULT NULL,
1175 p_po_line_id IN ap_invoice_lines_interface.PO_LINE_ID%TYPE DEFAULT NULL,
1176 p_po_line_number IN ap_invoice_lines_interface.PO_LINE_NUMBER%TYPE DEFAULT NULL,
1177 p_po_line_location_id IN ap_invoice_lines_interface.PO_LINE_LOCATION_ID%TYPE DEFAULT NULL,
1178 p_po_shipment_num IN ap_invoice_lines_interface.PO_SHIPMENT_NUM%TYPE DEFAULT NULL,
1179 p_po_distribution_id IN ap_invoice_lines_interface.PO_DISTRIBUTION_ID%TYPE DEFAULT NULL,
1180 p_po_distribution_num IN ap_invoice_lines_interface.PO_DISTRIBUTION_NUM%TYPE DEFAULT NULL,
1181 p_po_unit_of_measure IN ap_invoice_lines_interface.PO_UNIT_OF_MEASURE%TYPE DEFAULT NULL,
1182 p_inventory_item_id IN ap_invoice_lines_interface.INVENTORY_ITEM_ID%TYPE DEFAULT NULL,
1183 p_item_description IN ap_invoice_lines_interface.ITEM_DESCRIPTION%TYPE DEFAULT NULL,
1184 p_quantity_invoiced IN ap_invoice_lines_interface.QUANTITY_INVOICED%TYPE DEFAULT NULL,
1185 p_ship_to_location_code IN ap_invoice_lines_interface.SHIP_TO_LOCATION_CODE%TYPE DEFAULT NULL,
1186 p_unit_price IN ap_invoice_lines_interface.UNIT_PRICE%TYPE DEFAULT NULL,
1187 p_distribution_set_id IN ap_invoice_lines_interface.DISTRIBUTION_SET_ID%TYPE DEFAULT NULL,
1188 p_distribution_set_name IN ap_invoice_lines_interface.DISTRIBUTION_SET_NAME%TYPE DEFAULT NULL,
1189 p_dist_code_concatenated IN ap_invoice_lines_interface.DIST_CODE_CONCATENATED%TYPE DEFAULT NULL,
1190 p_dist_code_combination_id IN ap_invoice_lines_interface.DIST_CODE_COMBINATION_ID%TYPE DEFAULT NULL,
1191 p_awt_group_id IN ap_invoice_lines_interface.AWT_GROUP_ID%TYPE DEFAULT NULL,
1192 p_awt_group_name IN ap_invoice_lines_interface.AWT_GROUP_NAME%TYPE DEFAULT NULL,
1193 p_last_updated_by IN ap_invoice_lines_interface.LAST_UPDATED_BY%TYPE DEFAULT NULL,
1194 p_last_update_date IN ap_invoice_lines_interface.LAST_UPDATE_DATE%TYPE DEFAULT NULL,
1195 p_last_update_login IN ap_invoice_lines_interface.LAST_UPDATE_LOGIN%TYPE DEFAULT NULL,
1196 p_created_by IN ap_invoice_lines_interface.CREATED_BY%TYPE DEFAULT NULL,
1197 p_creation_date IN ap_invoice_lines_interface.CREATION_DATE%TYPE DEFAULT NULL,
1198 --Added below the attribute category and attribute parameters for Bug #3841637
1199 p_attribute_category IN ap_invoices_interface.ATTRIBUTE_CATEGORY%TYPE DEFAULT NULL,
1200 p_attribute1 IN ap_invoices_interface.ATTRIBUTE1%TYPE DEFAULT NULL,
1201 p_attribute2 IN ap_invoices_interface.ATTRIBUTE2%TYPE DEFAULT NULL,
1202 p_attribute3 IN ap_invoices_interface.ATTRIBUTE3%TYPE DEFAULT NULL,
1203 p_attribute4 IN ap_invoices_interface.ATTRIBUTE4%TYPE DEFAULT NULL,
1204 p_attribute5 IN ap_invoices_interface.ATTRIBUTE5%TYPE DEFAULT NULL,
1205 p_attribute6 IN ap_invoices_interface.ATTRIBUTE6%TYPE DEFAULT NULL,
1206 p_attribute7 IN ap_invoices_interface.ATTRIBUTE7%TYPE DEFAULT NULL,
1207 p_attribute8 IN ap_invoices_interface.ATTRIBUTE8%TYPE DEFAULT NULL,
1208 p_attribute9 IN ap_invoices_interface.ATTRIBUTE9%TYPE DEFAULT NULL,
1209 p_attribute10 IN ap_invoices_interface.ATTRIBUTE10%TYPE DEFAULT NULL,
1210 p_attribute11 IN ap_invoices_interface.ATTRIBUTE11%TYPE DEFAULT NULL,
1211 p_attribute12 IN ap_invoices_interface.ATTRIBUTE12%TYPE DEFAULT NULL,
1212 p_attribute13 IN ap_invoices_interface.ATTRIBUTE13%TYPE DEFAULT NULL,
1213 p_attribute14 IN ap_invoices_interface.ATTRIBUTE14%TYPE DEFAULT NULL,
1214 p_attribute15 IN ap_invoices_interface.ATTRIBUTE15%TYPE DEFAULT NULL,
1215 p_po_release_id IN ap_invoice_lines_interface.PO_RELEASE_ID%TYPE DEFAULT NULL,
1216 p_release_num IN ap_invoice_lines_interface.RELEASE_NUM%TYPE DEFAULT NULL,
1217 p_account_segment IN ap_invoice_lines_interface.ACCOUNT_SEGMENT%TYPE DEFAULT NULL,
1218 p_balancing_segment IN ap_invoice_lines_interface.BALANCING_SEGMENT%TYPE DEFAULT NULL,
1219 p_cost_center_segment IN ap_invoice_lines_interface.COST_CENTER_SEGMENT%TYPE DEFAULT NULL,
1220 p_project_id IN ap_invoice_lines_interface.PROJECT_ID%TYPE DEFAULT NULL,
1221 p_task_id IN ap_invoice_lines_interface.TASK_ID%TYPE DEFAULT NULL,
1222 p_expenditure_type IN ap_invoice_lines_interface.EXPENDITURE_TYPE%TYPE DEFAULT NULL,
1223 p_expenditure_item_date IN ap_invoice_lines_interface.EXPENDITURE_ITEM_DATE%TYPE DEFAULT NULL,
1224 p_expenditure_organization_id IN ap_invoice_lines_interface.EXPENDITURE_ORGANIZATION_ID%TYPE DEFAULT NULL,
1225 p_project_accounting_context IN ap_invoice_lines_interface.PROJECT_ACCOUNTING_CONTEXT%TYPE DEFAULT NULL,
1226 p_pa_addition_flag IN ap_invoice_lines_interface.PA_ADDITION_FLAG%TYPE DEFAULT NULL,
1227 p_pa_quantity IN ap_invoice_lines_interface.PA_QUANTITY%TYPE DEFAULT NULL,
1228 p_ussgl_transaction_code IN ap_invoice_lines_interface.USSGL_TRANSACTION_CODE%TYPE DEFAULT NULL,
1229 p_stat_amount IN ap_invoice_lines_interface.STAT_AMOUNT%TYPE DEFAULT NULL,
1230 p_type_1099 IN ap_invoice_lines_interface.TYPE_1099%TYPE DEFAULT NULL,
1231 p_income_tax_region IN ap_invoice_lines_interface.INCOME_TAX_REGION%TYPE DEFAULT NULL,
1232 p_assets_tracking_flag IN ap_invoice_lines_interface.ASSETS_TRACKING_FLAG%TYPE DEFAULT NULL,
1233 p_price_correction_flag IN ap_invoice_lines_interface.PRICE_CORRECTION_FLAG%TYPE DEFAULT NULL,
1234 p_org_id IN ap_invoice_lines_interface.ORG_ID%TYPE DEFAULT NULL,
1235 p_receipt_number IN ap_invoice_lines_interface.RECEIPT_NUMBER%TYPE DEFAULT NULL,
1236 p_receipt_line_number IN ap_invoice_lines_interface.RECEIPT_LINE_NUMBER%TYPE DEFAULT NULL,
1237 p_match_option IN ap_invoice_lines_interface.MATCH_OPTION%TYPE DEFAULT NULL,
1238 p_packing_slip IN ap_invoice_lines_interface.PACKING_SLIP%TYPE DEFAULT NULL,
1239 p_rcv_transaction_id IN ap_invoice_lines_interface.RCV_TRANSACTION_ID%TYPE DEFAULT NULL,
1240 p_pa_cc_ar_invoice_id IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_ID%TYPE DEFAULT NULL,
1241 p_pa_cc_ar_invoice_line_num IN ap_invoice_lines_interface.PA_CC_AR_INVOICE_LINE_NUM%TYPE DEFAULT NULL,
1242 p_reference_1 IN ap_invoice_lines_interface.REFERENCE_1%TYPE DEFAULT NULL,
1243 p_reference_2 IN ap_invoice_lines_interface.REFERENCE_2%TYPE DEFAULT NULL,
1244 p_pa_cc_processed_code IN ap_invoice_lines_interface.PA_CC_PROCESSED_CODE%TYPE DEFAULT NULL,
1245 p_tax_recovery_rate IN ap_invoice_lines_interface.TAX_RECOVERY_RATE%TYPE DEFAULT NULL,
1246 p_tax_recovery_override_flag IN ap_invoice_lines_interface.TAX_RECOVERY_OVERRIDE_FLAG%TYPE DEFAULT NULL,
1247 p_tax_recoverable_flag IN ap_invoice_lines_interface.TAX_RECOVERABLE_FLAG%TYPE DEFAULT NULL,
1248 p_tax_code_override_flag IN ap_invoice_lines_interface.TAX_CODE_OVERRIDE_FLAG%TYPE DEFAULT NULL,
1249 p_tax_code_id IN ap_invoice_lines_interface.TAX_CODE_ID%TYPE DEFAULT NULL,
1250 p_credit_card_trx_id IN ap_invoice_lines_interface.CREDIT_CARD_TRX_ID%TYPE DEFAULT NULL,
1251 -- Bug 4240179. Changed data for vendor_item_num and award_id
1252 -- Added by LGOPALSA
1253 p_award_id IN NUMBER DEFAULT NULL,
1254 p_vendor_item_num IN VARCHAR2 DEFAULT NULL,
1255 p_taxable_flag IN VARCHAR2 DEFAULT NULL,
1256 p_price_correct_inv_num IN VARCHAR2 DEFAULT NULL,
1257 p_external_doc_line_ref IN VARCHAR2 DEFAULT NULL)
1258 IS
1259 lv_object_name VARCHAR2(61);
1260 BEGIN
1261
1262 lv_object_name := 'jai_ap_utils_pkg.insert_ap_inv_lines_interface'; /* Added by Ramananda for bug#4407165 */
1263
1264 INSERT INTO ap_invoice_lines_interface(
1265 INVOICE_ID,
1266 INVOICE_LINE_ID,
1267 LINE_NUMBER,
1268 LINE_TYPE_LOOKUP_CODE,
1269 LINE_GROUP_NUMBER,
1270 AMOUNT,
1271 ACCOUNTING_DATE,
1272 DESCRIPTION,
1273 AMOUNT_INCLUDES_TAX_FLAG,
1274 PRORATE_ACROSS_FLAG,
1275 TAX_CODE,
1276 FINAL_MATCH_FLAG,
1277 PO_HEADER_ID,
1278 PO_NUMBER,
1279 PO_LINE_ID,
1280 PO_LINE_NUMBER,
1281 PO_LINE_LOCATION_ID,
1282 PO_SHIPMENT_NUM,
1283 PO_DISTRIBUTION_ID,
1284 PO_DISTRIBUTION_NUM,
1285 PO_UNIT_OF_MEASURE,
1286 INVENTORY_ITEM_ID,
1287 ITEM_DESCRIPTION,
1288 QUANTITY_INVOICED,
1289 SHIP_TO_LOCATION_CODE,
1290 UNIT_PRICE,
1291 DISTRIBUTION_SET_ID,
1292 DISTRIBUTION_SET_NAME,
1293 DIST_CODE_CONCATENATED,
1294 DIST_CODE_COMBINATION_ID,
1295 AWT_GROUP_ID,
1296 AWT_GROUP_NAME,
1297 LAST_UPDATED_BY,
1298 LAST_UPDATE_DATE,
1299 LAST_UPDATE_LOGIN,
1300 CREATED_BY,
1301 CREATION_DATE,
1302 --Added below the attribute category and attribute columns for Bug #3841637
1303 ATTRIBUTE_CATEGORY,
1304 ATTRIBUTE1,
1305 ATTRIBUTE2,
1306 ATTRIBUTE3,
1307 ATTRIBUTE4,
1308 ATTRIBUTE5,
1309 ATTRIBUTE6,
1310 ATTRIBUTE7,
1311 ATTRIBUTE8,
1312 ATTRIBUTE9,
1313 ATTRIBUTE10,
1314 ATTRIBUTE11,
1315 ATTRIBUTE12,
1316 ATTRIBUTE13,
1317 ATTRIBUTE14,
1318 ATTRIBUTE15,
1319 PO_RELEASE_ID,
1320 RELEASE_NUM,
1321 ACCOUNT_SEGMENT,
1322 BALANCING_SEGMENT,
1323 COST_CENTER_SEGMENT,
1324 PROJECT_ID,
1325 TASK_ID,
1326 EXPENDITURE_TYPE,
1327 EXPENDITURE_ITEM_DATE,
1328 EXPENDITURE_ORGANIZATION_ID,
1329 PROJECT_ACCOUNTING_CONTEXT,
1330 PA_ADDITION_FLAG,
1331 PA_QUANTITY,
1332 USSGL_TRANSACTION_CODE,
1333 STAT_AMOUNT,
1334 TYPE_1099,
1335 INCOME_TAX_REGION,
1336 ASSETS_TRACKING_FLAG,
1337 PRICE_CORRECTION_FLAG,
1338 ORG_ID,
1339 RECEIPT_NUMBER,
1340 RECEIPT_LINE_NUMBER,
1341 MATCH_OPTION,
1342 PACKING_SLIP,
1343 RCV_TRANSACTION_ID,
1344 PA_CC_AR_INVOICE_ID,
1345 PA_CC_AR_INVOICE_LINE_NUM,
1346 REFERENCE_1,
1347 REFERENCE_2,
1348 PA_CC_PROCESSED_CODE,
1349 TAX_RECOVERY_RATE,
1350 TAX_RECOVERY_OVERRIDE_FLAG,
1351 TAX_RECOVERABLE_FLAG,
1352 TAX_CODE_OVERRIDE_FLAG,
1353 TAX_CODE_ID,
1354 CREDIT_CARD_TRX_ID
1355 --, Bug 4240179. Commented by LGOPALSA
1356 -- AWARD_ID,
1357 -- VENDOR_ITEM_NUM
1358 /*,
1359 TAXABLE_FLAG,
1360 PRICE_CORRECT_INV_NUM,
1361 EXTERNAL_DOC_LINE_REF*/)--commented by Sanjikum for Bug#4183001
1362 VALUES(
1363 p_invoice_id,
1364 ap_invoice_lines_interface_s.NEXTVAL,
1365 p_line_number,
1366 p_line_type_lookup_code,
1367 p_line_group_number,
1368 p_amount,
1369 trunc(p_accounting_date, 'DD'), /*Bug 11783447 - Truncate the Accounting Date to Date Precision*/
1370 p_description,
1371 p_amount_includes_tax_flag,
1372 p_prorate_across_flag,
1373 p_tax_code,
1374 p_final_match_flag,
1375 p_po_header_id,
1376 p_po_number,
1377 p_po_line_id,
1378 p_po_line_number,
1379 p_po_line_location_id,
1380 p_po_shipment_num,
1381 p_po_distribution_id,
1382 p_po_distribution_num,
1383 p_po_unit_of_measure,
1384 p_inventory_item_id,
1385 p_item_description,
1386 p_quantity_invoiced,
1387 p_ship_to_location_code,
1388 p_unit_price,
1389 p_distribution_set_id,
1390 p_distribution_set_name,
1391 p_dist_code_concatenated,
1392 p_dist_code_combination_id,
1393 p_awt_group_id,
1394 p_awt_group_name,
1395 p_last_updated_by,
1396 p_last_update_date,
1397 p_last_update_login,
1398 p_created_by,
1399 p_creation_date,
1400 --Added below the attribute category and attribute columns for Bug #3841637
1401 p_attribute_category,
1402 p_attribute1,
1403 p_attribute2,
1404 p_attribute3,
1405 p_attribute4,
1406 p_attribute5,
1407 p_attribute6,
1408 p_attribute7,
1409 p_attribute8,
1410 p_attribute9,
1411 p_attribute10,
1412 p_attribute11,
1413 p_attribute12,
1414 p_attribute13,
1415 p_attribute14,
1416 p_attribute15,
1417 p_po_release_id,
1418 p_release_num,
1419 p_account_segment,
1420 p_balancing_segment,
1421 p_cost_center_segment,
1422 p_project_id,
1423 p_task_id,
1424 p_expenditure_type,
1425 p_expenditure_item_date,
1426 p_expenditure_organization_id,
1427 p_project_accounting_context,
1428 p_pa_addition_flag,
1429 p_pa_quantity,
1430 p_ussgl_transaction_code,
1431 p_stat_amount,
1432 p_type_1099,
1433 p_income_tax_region,
1434 p_assets_tracking_flag,
1435 p_price_correction_flag,
1436 p_org_id,
1437 p_receipt_number,
1438 p_receipt_line_number,
1439 p_match_option,
1440 p_packing_slip,
1441 p_rcv_transaction_id,
1442 p_pa_cc_ar_invoice_id,
1443 p_pa_cc_ar_invoice_line_num,
1444 p_reference_1,
1445 p_reference_2,
1446 p_pa_cc_processed_code,
1447 p_tax_recovery_rate,
1448 p_tax_recovery_override_flag,
1449 p_tax_recoverable_flag,
1450 p_tax_code_override_flag,
1451 p_tax_code_id,
1452 p_credit_card_trx_id
1453 --, Bug 4240179. Commented by LGOPALSA
1454 --p_award_id,
1455 --p_vendor_item_num
1456 /*,
1457 p_taxable_flag,
1458 p_price_correct_inv_num,
1459 p_external_doc_line_ref*/)--commented by Sanjikum for Bug#4183001
1460 RETURNING invoice_line_id INTO p_invoice_line_id;
1461
1462 /* Added by Ramananda for bug#4407165 */
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 p_invoice_line_id := null;
1466 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1467 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1468 app_exception.raise_exception;
1469
1470 END insert_ap_inv_lines_interface;
1471
1472 /* Brathod, For Bug# 4445989, get_apportion_factor signature is modified to use invoice_id and line_number*/
1473 FUNCTION get_apportion_factor(
1474 -- p_invoice_distribution_id in number
1475 pn_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE
1476 , pn_invoice_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE,
1477 --added the following parameter by vkaranam for bug #5989740
1478 p_factor_type varchar2 default null
1479 ) return number
1480 is
1481 /* Modified cursor to use ap_invoice_lines_all for Bug# 4445989 */
1482 cursor c_get_inv_details is
1483 select quantity_invoiced, unit_price, po_distribution_id, rcv_transaction_id,invoice_id
1484 from ap_invoice_lines_all
1485 where invoice_id = pn_invoice_id
1486 AND line_number = pn_invoice_line_number;
1487
1488 cursor c_get_uoms_po_receipt(p_rcv_transaction_id number) is
1489 select unit_of_measure receipt_uom,
1490 source_doc_unit_of_measure po_uom,
1491 po_unit_price /*Bug 4990941 bduvarag*/
1492 from rcv_transactions
1493 where transaction_id = p_rcv_transaction_id;
1494
1495 /* cursor c_get_po_qty_price(p_po_distribution_id number) is
1496 select price_override, quantity
1497 from po_line_locations_all
1498 where (po_header_id, po_line_id, line_location_id ) in
1499 (
1500 select po_header_id, po_line_id, line_location_id
1501 from po_distributions_all
1502 where po_distribution_id = p_po_distribution_id
1503 ); */
1504 /*bug 9346307*/
1505 cursor c_get_po_qty_price is
1506 select price_override, quantity
1507 from po_line_locations_all
1508 where (po_header_id, po_line_id, line_location_id ) in
1509 (
1510 select po_header_id, po_line_id, po_line_location_id
1511 from ap_invoice_lines_all
1512 where invoice_id = pn_invoice_id
1513 AND line_number = pn_invoice_line_number
1514 );
1515
1516 cursor c_get_receipt_qty(p_rcv_transaction_id number) is
1517 select qty_received
1518 from JAI_RCV_LINES
1519 where (shipment_header_id, shipment_line_id)
1520 in
1521 (
1522 select shipment_header_id, shipment_line_id
1523 from rcv_transactions
1524 where transaction_id = p_rcv_transaction_id
1525 );
1526
1527 cursor c_get_uom_code(p_unit_of_measure in varchar2) is
1528 select uom_code
1529 from mtl_units_of_measure
1530 where unit_of_measure = p_unit_of_measure;
1531
1532 cursor c_get_item (p_transaction_id number) is
1533 select item_id
1534 from rcv_shipment_lines
1535 where shipment_line_id = (select shipment_line_id
1536 from rcv_transactions
1537 where transaction_id = p_transaction_id);
1538
1539 -- iSupplier porting
1540 CURSOR c_inv(inv_id NUMBER) IS
1541 SELECT source
1542 FROM ap_invoices_all
1543 WHERE invoice_id = inv_id;
1544 -- iSupplier porting
1545
1546
1547 v_invoice_quantity ap_invoice_distributions_all.quantity_invoiced%type;
1548 v_invoice_price ap_invoice_distributions_all.unit_price%type;
1549 v_invoice_id ap_invoice_distributions_all.invoice_id%type; --iSuppleir porting
1550 v_source ap_invoices_all.source%type; --iSuppleir porting
1551
1552 v_po_uom rcv_transactions.source_doc_unit_of_measure%type;
1553 v_receipt_price rcv_transactions.po_unit_price%type ; /*Bug 4990941 bduvarag*/
1554 v_receipt_uom rcv_transactions.unit_of_measure%type;
1555
1556 v_receipt_quantity JAI_RCV_LINES.qty_received%type;
1557
1558 v_po_price po_line_locations_all.price_override%type;
1559 v_po_quantity po_line_locations_all.quantity%type;
1560
1561 v_po_distribution_id ap_invoice_distributions_all.po_distribution_id%type;
1562 v_rcv_transaction_id ap_invoice_distributions_all.rcv_transaction_id%type;
1563
1564 v_uom_conv_factor number;
1565
1566
1567 v_po_uom_code mtl_units_of_measure.uom_code%type;
1568 v_receipt_uom_code mtl_units_of_measure.uom_code%type;
1569
1570 v_item_id rcv_shipment_lines.item_id%type;
1571
1572 v_statement_id number:=0;
1573
1574
1575 begin
1576
1577 /* -----------------------------------------------------------------------------
1578 FILENAME: jai_ap_utils_pkg.get_apportion_factor.sql
1579 CHANGE HISTORY:
1580
1581 S.No Date Author and Details
1582 1 14/06/2004 Created by Aparajita for bug#3633078. Version#115.0
1583
1584 This function computes the factor by which tax on
1585 Receipt or PO should be apportioned to be taken over
1586 to Payable Invoice. This factor considers the changes in
1587 quantity, UOM and Price.
1588
1589 Quantity can be changed at every stage like,
1590 between PO and Receipt, Receipt and Invoice, and PO and
1591 Invoice also.
1592
1593 UOM can be changed between PO and Receipt only.
1594
1595 Price can be changed between PO and Invoice.
1596
1597 Invoice can refer to either a Receipt / PO.
1598
1599 Only apportion not handled here is the currency of tax
1600 and invoice and apportionment if required by exchange rate.
1601 This is so because, that would depend on each tax and current
1602 apportion factor is for all taxes attached to a line.
1603
1604
1605
1606 Future Dependencies For the release Of this Object:-
1607 ==================================================
1608 Please add a row in the section below only if your bug introduces a dependency
1609 like,spec change/ A new call to a object/A datamodel change.
1610
1611 --------------------------------------------------------------------------------
1612 Version Bug Dependencies (including other objects like files if any)
1613 --------------------------------------------------------------------------------
1614
1615
1616 --------------------------------------------------------------------------------- */
1617 -- Added by Jason Liu for retroactive price on 2008/01/24
1618 ----------------------------------------------------------------------
1619 OPEN c_inv(pn_invoice_id);
1620 FETCH c_inv INTO v_source;
1621 CLOSE c_inv;
1622
1623 --Comment out by Kevin Cheng for bug#6962018 Apr 14, 2008
1624 /*IF(v_source = 'PPA')
1625 THEN
1626 RETURN 1;
1627 END IF; --(v_source = 'PPA') */
1628 ----------------------------------------------------------------------
1629
1630 v_statement_id := 1;
1631 open c_get_inv_details;
1632 fetch c_get_inv_details into
1633 v_invoice_quantity, v_invoice_price, v_po_distribution_id, v_rcv_transaction_id, v_invoice_id;
1634 close c_get_inv_details;
1635
1636 v_statement_id := 2;
1637 open c_get_po_qty_price; /*bug 9346307*/
1638 fetch c_get_po_qty_price into v_po_price, v_po_quantity;
1639 close c_get_po_qty_price;
1640
1641 v_statement_id := 3;
1642
1643 if v_rcv_transaction_id is not null then
1644
1645 v_statement_id := 4;
1646 open c_get_uoms_po_receipt(v_rcv_transaction_id);
1647 fetch c_get_uoms_po_receipt into v_receipt_uom, v_po_uom,v_receipt_price;/*bug 4990941 bduvarag*/
1648 close c_get_uoms_po_receipt;
1649
1650 open c_get_receipt_qty(v_rcv_transaction_id);
1651 fetch c_get_receipt_qty into v_receipt_quantity;
1652 close c_get_receipt_qty;
1653
1654 if v_receipt_uom = v_po_uom then
1655 v_statement_id := 5;
1656 v_uom_conv_factor := 1;
1657 else
1658 v_statement_id := 6;
1659 open c_get_uom_code(v_receipt_uom);
1660 fetch c_get_uom_code into v_receipt_uom_code;
1661 close c_get_uom_code;
1662
1663 open c_get_uom_code(v_po_uom);
1664 fetch c_get_uom_code into v_po_uom_code;
1665 close c_get_uom_code;
1666
1667 open c_get_item(v_rcv_transaction_id);
1668 fetch c_get_item into v_item_id;
1669 close c_get_item;
1670
1671 v_statement_id := 7;
1672 Inv_Convert.Inv_Um_Conversion
1673 (
1674 v_receipt_uom_code,
1675 v_po_uom_code,
1676 v_item_id,
1677 v_uom_conv_factor
1678 );
1679
1680 if nvl(v_uom_conv_factor, 0) <= 0 then
1681 v_uom_conv_factor := 1;
1682 end if;
1683
1684
1685 end if;-- v_receipt_uom = v_po_uom t
1686
1687 end if;-- v_rcv_transaction_id is not null
1688
1689
1690
1691 if v_rcv_transaction_id is null then
1692
1693 v_statement_id:=8;
1694
1695 --Add by Kevin Cheng for bug#6962018 Apr 14, 2008
1696 -------------------------------------------------
1697 IF(v_source = 'PPA')
1698 THEN
1699 RETURN v_invoice_quantity/v_po_quantity;
1700 END IF; --(v_source = 'PPA')
1701 -------------------------------------------------
1702
1703 --added the following if condition by vkaranam for bug #5989740
1704 if p_factor_type is null then
1705
1706 -- iSupplier porting
1707 open c_inv(v_invoice_id);
1708 fetch c_inv into v_source;
1709 close c_inv;
1710 -- iSupplier porting
1711
1712 if nvl(v_po_quantity , 0) = 0 or nvl(v_po_price, 0) = 0
1713 or v_source = 'ASBN' then
1714 return 1;
1715 end if;
1716
1717 return ( (v_invoice_quantity / v_po_quantity) * (v_invoice_price / v_po_price) );
1718 else
1719 -- Begin Bug# 5989740
1720 if p_factor_type = 'QUANTITY' then
1721
1722 if nvl(v_po_quantity , 0) = 0 then
1723
1724 return 1 ;
1725
1726 else
1727
1728 return (v_invoice_quantity / v_po_quantity);
1729
1730 end if;
1731
1732 elsif p_factor_type = 'PRICE' then
1733
1734 if nvl(v_po_price , 0) = 0 then
1735
1736 return 1 ;
1737
1738 else
1739
1740 return (v_invoice_price / v_po_price);
1741
1742 end if;
1743
1744 end if; --> p_factor_type = 'QUANTITY'
1745
1746 end if; --> p_factor_type is null
1747 -- End Bug# 5989740
1748
1749
1750 else
1751
1752 v_statement_id:=9;
1753
1754 --Add by Kevin Cheng for bug#6962018 Apr 14, 2008
1755 -------------------------------------------------
1756 IF(v_source = 'PPA')
1757 THEN
1758 RETURN v_invoice_quantity/v_receipt_quantity;
1759 END IF; --(v_source = 'PPA')
1760 -------------------------------------------------
1761
1762 if p_factor_type is null then --bug 5989740
1763 if nvl(v_receipt_quantity, 0) = 0 or nvl(v_po_price, 0) = 0
1764 or nvl(v_uom_conv_factor, 0) = 0 then
1765
1766 return 1;
1767
1768 end if;
1769 /*Bug 4990941 bduvarag*/
1770 return ( (v_invoice_quantity / v_receipt_quantity) *
1771 (v_invoice_price / NVL(v_receipt_price,v_po_price)) *
1772 (1/ v_uom_conv_factor)
1773 );
1774 else -- Begin Bug# 5989740
1775
1776 if p_factor_type = 'QUANTITY' then
1777
1778 if nvl(v_receipt_quantity, 0) = 0 or nvl(v_uom_conv_factor, 0) = 0 then
1779
1780 return 1;
1781 else
1782
1783 return (v_invoice_quantity / v_receipt_quantity) * (1/ v_uom_conv_factor);
1784
1785 end if;
1786
1787 elsif p_factor_type = 'PRICE' then
1788
1789 if nvl(v_po_price, 0) = 0 or v_receipt_price = 0 then
1790
1791 return 1;
1792
1793 else
1794
1795 return (v_invoice_price / NVL(v_receipt_price,v_po_price));
1796
1797 end if;
1798
1799 end if; --> p_factor_type = 'QUANTITY'
1800
1801 end if; --> p_factor_type is null
1802
1803 end if; --> v_rcv_transaction_id
1804 -- End Bug# 5989740
1805
1806
1807
1808
1809 end get_apportion_factor;
1810
1811 PROCEDURE submit_pla_boe_for_approval
1812 (
1813 ERRBUF OUT NOCOPY VARCHAR2,
1814 RETCODE OUT NOCOPY VARCHAR2,
1815 p_boe_id In VARCHAR2,
1816 p_set_of_books_id In Number,
1817 p_prv_req_id In Number,
1818 p_vendor_id In Number
1819 )
1820 is
1821 request_id Number;
1822 result Boolean;
1823 v_invoice_id NUmber;
1824 req_status Boolean := TRUE;
1825 v_phase Varchar2(100);
1826 v_status Varchar2(100);
1827 v_dev_phase Varchar2(100);
1828 v_dev_status Varchar2(100);
1829 v_message Varchar2(100);
1830 v_prv_req_id Number;
1831
1832 /*-------------------------------------------------------------------------------------------------------------------
1833 FILENAME: Ja_In_Auto_Invoice_p.sql
1834
1835 CHANGE HISTORY:
1836 S.No Date Author and Details
1837 ------------------------------------------
1838 1. 29/10/2002 Aparajita for bug # 2645196
1839 When the parent request for importing fails then this request for approval of
1840 PLA/BOE invoices should error out. While polling the status of teh parent request there
1841 should be a delay of 60 seconds between polling.
1842
1843 Also added exception handling to the main procedure and to the sql that fetches
1844 from ap_invoices_all.
1845
1846 Since the procedure was revamped with the new approach, deleted the old code.
1847 --------------------------------------------------------------------------------------------------------------------*/
1848 Begin
1849 v_prv_req_id := p_prv_req_id;
1850
1851 -- start added by Aparajita on 29/10/2002 for bug # 2645196
1852 req_status := Fnd_concurrent.wait_for_request( v_prv_req_id,
1853 60, -- default value - sleep time in secs
1854 0, -- default value - max wait in secs
1855 v_phase,
1856 v_status,
1857 v_dev_phase,
1858 v_dev_status,
1859 v_message );
1860
1861 IF v_dev_phase = 'COMPLETE' THEN
1862
1863 IF v_dev_status <> 'NORMAL' THEN
1864
1865 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
1866 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || v_message);
1867 retcode := 1;
1868 errbuf := 'Exiting with warningr as parent request not completed with normal status';
1869 RETURN;
1870
1871 END IF;
1872
1873 END IF;
1874
1875 -- end added by Aparajita on 29/10/2002 for bug # 2645196
1876
1877 IF v_dev_phase = 'COMPLETE' or v_dev_phase = 'INACTIVE' Then
1878
1879 IF v_dev_status = 'NORMAL' Then
1880
1881 begin
1882
1883 Select invoice_id
1884 into v_invoice_id
1885 from ap_invoices_all
1886 Where invoice_num = p_boe_id
1887 And vendor_id = p_vendor_id;
1888
1889 result := Fnd_request.set_mode(TRUE);
1890 /* Bug 5378544. Added by Lakshmi Gopalsami
1891 * Included org_id and commit size.
1892 */
1893 request_id := FND_REQUEST.SUBMIT_REQUEST
1894 (
1895 'SQLAP',
1896 'APPRVL',
1897 'Payables Approval Localization',
1898 NULL,
1899 FALSE,
1900 '', -- org_id
1901 'All', '','','','','', to_char(v_invoice_id),
1902 '', to_char(p_set_of_books_id), '', /*Value N was passed to parameter p_commit_size , set it to NULL for bug # 10049591*/
1903 '' ); -- commit size
1904 exception
1905 when no_data_found then
1906 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as the PLA/BOE invoice has not got imported ');
1907 Fnd_File.put_line(Fnd_File.LOG, 'PLA/BOE invoice num :' || p_boe_id );
1908 retcode := 1;
1909 errbuf := 'Exiting with warning as the PLA/BOE invoice to approve has not been imported ';
1910 RETURN;
1911 end;
1912
1913 End If;
1914
1915
1916 End If;
1917
1918 Fnd_File.put_line(Fnd_File.LOG, 'PLA/BOE invoice num :' || p_boe_id || ', approval request submitted ');
1919
1920 exception
1921 when others then
1922 Fnd_File.put_line(Fnd_File.LOG, 'Exception encountered in procedure jai_ap_utils_pkg.submit_pla_boe_for_approval');
1923 Fnd_File.put_line(Fnd_File.LOG, SQLERRM);
1924 retcode := 2;
1925 errbuf := SQLERRM;
1926 RETURN;
1927 End submit_pla_boe_for_approval;
1928
1929
1930 --As part OF R12 Inititive Inventory conversion the following code IS commented BY Ravi
1931
1932 /*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
1933 Cursor C_Item_Dtl IS
1934 Select excise_calc_base -- , assessable_value (Commented as Assessable Value is picked by other conditions now )
1935 From JAI_OPM_ITM_MASTERS
1936 Where item_id = p_item_id;
1937
1938 ---Added For OPM Localization By A.Raina on 22-02-2000
1939 ---Code Added For Fetching the Assessable_value at the customer level
1940
1941 Cursor C_Price_list_id is
1942 Select Pricelist_Id
1943 From JAI_OPM_CUSTOMERS
1944 Where Cust_id = p_cust_id ;
1945
1946 Cursor C_Cust_Ass_Value ( p_Pricelist_Id In Number ) is
1947 Select a.Base_Price
1948 From Op_Prce_Itm a ,op_prce_eff b
1949 Where a.pricelist_id = b.pricelist_id
1950 And a.Pricelist_Id = p_Pricelist_id
1951 And a.Item_Id = p_item_id
1952 And sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate) ;
1953
1954 CURSOR C_item_Ass_Value IS
1955 Select assessable_value
1956 From JAI_OPM_ITM_MASTERS
1957 Where item_id = p_item_id;
1958
1959 v_pricelist_id Number;
1960 v_assessable_flag char(1) ;
1961 --End Addition
1962 l_assessable_val number;
1963 l_excise_cal varchar2(1);
1964 BEGIN
1965
1966 ---Added For OPM Localization By A.Raina on 22-02-2000
1967 ---Code Added For Fetching the Assessable_value at the customer level
1968
1969 OPEN C_Price_list_id ;
1970 FETCH C_Price_list_id into v_pricelist_id;
1971 CLOSE C_Price_list_id ;
1972
1973 l_assessable_val := Null ;
1974 IF v_pricelist_id is Not Null Then
1975 OPEN C_Cust_Ass_Value (v_pricelist_id ) ;
1976 FETCH C_Cust_Ass_Value into l_assessable_val ;
1977 CLOSE C_Cust_Ass_Value ;
1978 End If;
1979 IF l_assessable_val Is Null Then
1980 OPEN C_item_Ass_Value ;
1981 FETCH C_item_Ass_Value into l_assessable_val ;
1982 CLOSE C_item_Ass_Value ;
1983 End If;
1984
1985 ---End Addition
1986
1987 OPEN C_Item_Dtl;
1988 FETCH C_Item_Dtl INTO l_excise_cal ; -- l_assessable_val (Commented as Assessable Value is picked by other conditions now )
1989 CLOSE C_Item_Dtl ;
1990
1991 IF NVL(l_excise_cal,'N') = 'Y' THEN
1992 Return(l_assessable_val*p_qty);
1993 ELSE
1994 Return(p_exted_price);
1995 END IF;
1996 END get_opm_assessable_value;*/
1997
1998
1999 PROCEDURE Print_Log
2000 (
2001 P_debug IN VARCHAR2,
2002 P_string IN VARCHAR2
2003 ) IS
2004
2005 stemp VARCHAR2(1000);
2006 nlength NUMBER := 1;
2007
2008 BEGIN
2009
2010 IF (P_Debug = 'Y') THEN
2011 WHILE(length(P_string) >= nlength)
2012 LOOP
2013
2014 stemp := substrb(P_string, nlength, 80);
2015 fnd_file.put_line(FND_FILE.LOG, stemp);
2016 nlength := (nlength + 80);
2017
2018 END LOOP;
2019 END IF;
2020
2021 EXCEPTION
2022 WHEN OTHERS THEN
2023
2024 IF (SQLCODE <> -20001) THEN
2025 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2026 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2027 END IF;
2028 APP_EXCEPTION.RAISE_EXCEPTION;
2029
2030 END Print_log;
2031
2032
2033 Procedure pan_update ( P_errbuf OUT NOCOPY varchar2,
2034 P_return_code OUT NOCOPY varchar2,
2035 P_vendor_id IN PO_VENDORS.vendor_id%TYPE,
2036 P_old_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
2037 P_new_pan_num IN JAI_AP_TDS_VENDOR_HDRS.pan_no%TYPE,
2038 P_debug_flag IN varchar2) is
2039
2040
2041 /* Cursor to lock the jai_ap_tds_thhold_grps */
2042
2043 Cursor C_lock_thhold_grps is
2044 select threshold_grp_id,
2045 vendor_id,
2046 org_tan_num,
2047 vendor_pan_num,
2048 section_type,
2049 section_code,
2050 fin_year,
2051 total_invoice_amount,
2052 total_invoice_cancel_amount,
2053 total_invoice_apply_amount,
2054 total_invoice_unapply_amount,
2055 total_tax_paid,
2056 total_thhold_change_tax_paid,
2057 current_threshold_slab_id,
2058 created_by,
2059 creation_date,
2060 last_updated_by,
2061 last_update_date,
2062 last_update_login
2063 from jai_ap_tds_thhold_grps
2064 where vendor_id = P_vendor_id
2065 and vendor_pan_num = p_old_pan_num
2066 order by vendor_id,threshold_grp_id
2067 for UPDATE of threshold_grp_id NOWAIT;
2068
2069
2070
2071 /* Update the tables in the following order
2072
2073 (1) JAI_AP_TDS_VENDOR_HDRS
2074 (2) jai_ap_tds_thhold_grps
2075 (3) jai_ap_tds_thhold_xceps
2076
2077 */
2078
2079 lv_vendor_site_id_updated varchar2(1000) ;
2080 lv_thhold_grp_id_updated varchar2(1000) ;
2081 lv_thhold_xcep_id_updated varchar2(1000) ;
2082 ln_request_id number;
2083 lv_debug_flag varchar2(30);
2084 lv_debug_msg varchar2(4000) ;
2085
2086
2087 begin
2088
2089 lv_debug_flag := nvl(p_debug_flag, 'N');
2090
2091 lv_vendor_site_id_updated := '';
2092 lv_thhold_grp_id_updated := '';
2093 lv_thhold_xcep_id_updated := '';
2094
2095 fnd_file.put_line(FND_FILE.LOG, 'START OF Procedure ');
2096
2097 ln_request_id := FND_GLOBAL.conc_request_id;
2098
2099 lv_debug_msg := ' A. Report Parameters';
2100
2101 If lv_debug_flag = 'Y' then
2102 Print_log(lv_debug_flag, lv_debug_msg);
2103 End if;
2104
2105 lv_debug_msg := ' B. request id '|| ln_request_id ;
2106
2107 If lv_debug_flag = 'Y' then
2108 Print_log(lv_debug_flag, lv_debug_msg);
2109 End if;
2110
2111 lv_debug_msg := ' C. debug flag ' || lv_debug_flag;
2112
2113 If lv_debug_flag = 'Y' then
2114 Print_log(lv_debug_flag, lv_debug_msg);
2115 End if;
2116
2117 lv_debug_msg := ' D. old pan ' || P_old_pan_num ;
2118
2119 If lv_debug_flag = 'Y' then
2120 Print_log(lv_debug_flag, lv_debug_msg);
2121 End if;
2122
2123 lv_debug_msg := ' E. new pan ' || P_new_pan_num ;
2124
2125 If lv_debug_flag = 'Y' then
2126 Print_log(lv_debug_flag, lv_debug_msg);
2127 End if;
2128
2129 lv_debug_msg :=' F. vendor id '|| P_vendor_id;
2130
2131 If lv_debug_flag = 'Y' then
2132 Print_log(lv_debug_flag, lv_debug_msg);
2133 End if;
2134
2135 -- Update the jai_ap_tds_thhold_grps
2136
2137 lv_debug_msg := ' 1. Update jai_ap_tds_thhold_grps';
2138
2139 If lv_debug_flag = 'Y' then
2140 Print_log(lv_debug_flag, lv_debug_msg);
2141 End if;
2142
2143 for thhold_grps in C_lock_thhold_grps
2144 loop
2145
2146 lv_debug_msg := ' 2. Going to update jai_ap_tds_thhold_grps';
2147
2148 If lv_debug_flag = 'Y' then
2149 Print_log(lv_debug_flag, lv_debug_msg);
2150 End if;
2151
2152 update jai_ap_tds_thhold_grps
2153 set vendor_pan_num = P_new_pan_num
2154 where vendor_id = P_vendor_id
2155 and vendor_pan_num = P_old_pan_num
2156 and threshold_grp_id = thhold_grps.threshold_grp_id;
2157
2158 lv_debug_msg := ' 3. Done with update of '|| thhold_grps.threshold_grp_id;
2159
2160 If lv_debug_flag = 'Y' then
2161 Print_log(lv_debug_flag, lv_debug_msg);
2162 End if;
2163
2164 lv_thhold_grp_id_updated := lv_thhold_grp_id_updated || '-' || thhold_grps.threshold_grp_id;
2165
2166 lv_debug_msg := ' 4. Value of lv_thhold_grp_id_updated '|| lv_thhold_grp_id_updated;
2167
2168 If lv_debug_flag = 'Y' then
2169 Print_log(lv_debug_flag, lv_debug_msg);
2170 End if;
2171
2172
2173 end loop;
2174
2175
2176 -- Update the JAI_AP_TDS_VENDOR_HDRS
2177 lv_debug_msg := ' 5. Update JAI_AP_TDS_VENDOR_HDRS';
2178
2179 If lv_debug_flag = 'Y' then
2180 Print_log(lv_debug_flag, lv_debug_msg);
2181 End if;
2182
2183 for vndr_tds_hdr in (select vthdr.*
2184 from JAI_AP_TDS_VENDOR_HDRS vthdr
2185 where vthdr.vendor_id = P_vendor_id
2186 and vthdr.pan_no = P_old_pan_num)
2187 loop
2188
2189 lv_debug_msg := ' 6. Going to update JAI_AP_TDS_VENDOR_HDRS';
2190
2191 If lv_debug_flag = 'Y' then
2192 Print_log(lv_debug_flag, lv_debug_msg);
2193 End if;
2194
2195 update JAI_AP_TDS_VENDOR_HDRS
2196 set pan_no = P_new_pan_num
2197 where vendor_id = vndr_tds_hdr.vendor_id
2198 and vendor_site_id = vndr_tds_hdr.vendor_site_id
2199 and pan_no = P_old_pan_num;
2200
2201
2202 lv_debug_msg := ' 7. Done with update of vendor '|| vndr_tds_hdr.vendor_id;
2203 lv_debug_msg := lv_debug_msg || ' site '|| vndr_tds_hdr.vendor_site_id ;
2204
2205 If lv_debug_flag = 'Y' then
2206 Print_log(lv_debug_flag, lv_debug_msg);
2207 End if;
2208
2209 If vndr_tds_hdr.vendor_site_id <> 0 Then
2210 lv_vendor_site_id_updated := lv_vendor_site_id_updated || ' - '||vndr_tds_hdr.vendor_site_id;
2211 End if;
2212
2213 lv_debug_msg := ' 8. Value of lv_vendor_site_id_updated '|| lv_vendor_site_id_updated;
2214
2215
2216 If lv_debug_flag = 'Y' then
2217 Print_log(lv_debug_flag, lv_debug_msg);
2218 End if;
2219
2220 end loop;
2221
2222
2223 -- jai_ap_tds_thhold_xceps
2224
2225 lv_debug_msg := ' 9. Update jai_ap_tds_thhold_xceps';
2226
2227 If lv_debug_flag = 'Y' then
2228 Print_log(lv_debug_flag, lv_debug_msg);
2229 End if;
2230
2231 for thhold_xceps in (select tdsxps.*
2232 from jai_ap_tds_thhold_xceps tdsxps
2233 where tdsxps.vendor_id = P_vendor_id
2234 and vendor_pan = P_old_pan_num)
2235 loop
2236
2237 lv_debug_msg := ' 10. Going to update jai_ap_tds_thhold_xceps';
2238
2239 If lv_debug_flag = 'Y' then
2240 Print_log(lv_debug_flag, lv_debug_msg);
2241 End if;
2242
2243 Update jai_ap_tds_thhold_xceps
2244 set vendor_pan = P_new_pan_num
2245 where vendor_id = P_vendor_id
2246 and vendor_pan = P_old_pan_num;
2247
2248 lv_debug_msg := ' 11. Done with update of vendor'||P_vendor_id ;
2249
2250 If lv_debug_flag = 'Y' then
2251 Print_log(lv_debug_flag, lv_debug_msg);
2252 End if;
2253
2254 lv_thhold_xcep_id_updated := lv_thhold_xcep_id_updated || '-' || thhold_xceps.threshold_exception_id;
2255
2256 lv_debug_msg := ' 12. Value of lv_thhold_xcep_id_updated '|| lv_thhold_xcep_id_updated;
2257
2258 If lv_debug_flag = 'Y' then
2259 Print_log(lv_debug_flag, lv_debug_msg);
2260 End if;
2261
2262 end loop;
2263
2264
2265 -- insert a record in jai_ap_tds_pan_changes
2266 -- This help us to keep track of PAN changes for the given vendor
2267
2268
2269 lv_debug_msg := ' 13. Inside insert - ';
2270
2271 If lv_debug_flag = 'Y' then
2272 Print_log(lv_debug_flag, lv_debug_msg);
2273 End if;
2274
2275 Insert into jai_ap_tds_pan_changes
2276 ( pan_change_id,
2277 vendor_id,
2278 old_pan_num,
2279 new_pan_num,
2280 request_id,
2281 request_date,
2282 vendor_site_id_updated,
2283 thhold_grp_id_updated,
2284 thhold_xcep_id_updated,
2285 creation_date,
2286 created_by,
2287 last_update_date,
2288 last_updated_by,
2289 last_update_login
2290 )
2291 values
2292 ( jai_ap_tds_pan_changes_s.nextval,
2293 P_vendor_id,
2294 P_old_pan_num,
2295 P_new_pan_num,
2296 ln_request_id,
2297 sysdate,
2298 lv_vendor_site_id_updated,
2299 lv_thhold_grp_id_updated,
2300 lv_thhold_xcep_id_updated,
2301 sysdate,
2302 fnd_global.user_id,
2303 sysdate,
2304 fnd_global.user_id,
2305 fnd_global.login_id
2306 );
2307
2308
2309 commit;
2310
2311 Exception
2312 When others then
2313
2314 IF (SQLCODE < 0) then
2315
2316 If lv_debug_flag = 'Y' then
2317 Print_log(lv_debug_flag,lv_debug_msg);
2318 Print_log(lv_debug_flag,SQLERRM);
2319 End if;
2320 END IF;
2321
2322 IF (SQLCODE = -54) then
2323 If lv_debug_flag = 'Y' then
2324 Print_log(lv_debug_flag,'(Pan update :Exception) Vendor to be updated by this process are locked');
2325 end if;
2326 END IF;
2327
2328 End pan_update;
2329
2330 /*
2331 || Added function get_tds_invoice_batch by Ramananda for bug#4584221
2332 */
2333 FUNCTION get_tds_invoice_batch(p_invoice_id IN NUMBER,
2334 p_org_id number default null) --added org_id parameter for bug#9149941
2335 RETURN VARCHAR2 IS
2336
2337 lv_same_tds_batch VARCHAR2(1);
2338 lv_batch_name ap_batches_all.batch_name%TYPE;
2339 ln_regime_id JAI_RGM_DEFINITIONS.regime_id%type ;
2340 lv_attribute_value JAI_RGM_ORG_REGNS_V.attribute_Value%type ;
2341
2342 CURSOR c_regime_cur IS
2343 SELECT regime_id
2344 FROM JAI_RGM_DEFINITIONS
2345 WHERE regime_code = jai_constants.tds_regime;
2346
2347 CURSOR c_attribute_value_cur(P_regime_id IN NUMBER, p_org_id number) is --added org_id parameter for bug#9149941
2348 SELECT attribute_Value
2349 FROM JAI_RGM_ORG_REGNS_V
2350 WHERE regime_id = P_regime_id
2351 AND attribute_type_code = jai_constants.regn_type_others
2352 AND attribute_code = jai_constants.regn_type_tds_batch
2353 and organization_id = nvl(p_org_id, organization_id); --added for Bug#9149941
2354
2355 CURSOR c_batch_name(cp_invoice_id NUMBER)
2356 IS
2357 SELECT b.batch_name
2358 FROM ap_invoices_all a,
2359 ap_batches_all b
2360 WHERE a.batch_id = b.batch_id
2361 AND a.invoice_id = cp_invoice_id;
2362
2363 BEGIN
2364
2365 OPEN c_regime_cur ;
2366 FETCH c_regime_cur INTO ln_regime_id ;
2367 CLOSE c_regime_cur ;
2368
2369 OPEN c_attribute_value_cur(ln_regime_id, p_org_id) ; --added org_id parameter for bug#9149941
2370 FETCH c_attribute_value_cur INTO lv_attribute_value ;
2371 CLOSE c_attribute_value_cur ;
2372
2373 IF upper(lv_attribute_value) in ('YES' , 'Y') THEN
2374
2375 OPEN c_batch_name(p_invoice_id);
2376 FETCH c_batch_name INTO lv_batch_name;
2377 CLOSE c_batch_name;
2378
2379 END IF;
2380
2381 IF UPPER(NVL(lv_attribute_value,'N')) in ('NO','N')
2382 OR lv_batch_name IS NULL THEN
2383 lv_batch_name := 'TDS'||TO_CHAR(TRUNC(SYSDATE));
2384 END IF;
2385
2386 RETURN lv_batch_name;
2387 END get_tds_invoice_batch;
2388
2389 /*------------------------------------------------------------------------------------------------------------*/
2390 -- Begin 4579729
2391 /*------------------------------------------------------------------------------------------------------------*/
2392
2393 procedure jai_calc_ipv_erv (P_errmsg OUT NOCOPY VARCHAR2,
2394 P_retcode OUT NOCOPY Number,
2395 P_invoice_id in number,
2396 P_po_dist_id in number,
2397 P_invoice_distribution_id IN NUMBER,
2398 P_amount IN NUMBER,
2399 P_base_amount IN NUMBER,
2400 P_rcv_transaction_id IN NUMBER,
2401 P_invoice_price_variance IN NUMBER,
2402 P_base_invoice_price_variance IN NUMBER,
2403 P_price_var_ccid IN NUMBER,
2404 P_Exchange_rate_variance IN NUMBER,
2405 P_rate_var_ccid IN NUMBER
2406 )
2407 as
2408
2409 /* Cursors */
2410
2411 Cursor check_rec_tax ( ln_tax_id number) is
2412 select tax_name,
2413 tax_account_id,
2414 mod_cr_percentage,
2415 adhoc_flag,
2416 nvl(tax_rate, 0) tax_rate,
2417 tax_type
2418 from JAI_CMN_TAXES_ALL
2419 where tax_id = ln_tax_id;
2420
2421
2422 Cursor get_misc_lines (ln_dist_line_number in number,
2423 ln_invoice_id in number ) is
2424 select *
2425 from ap_invoice_distributions_all
2426 where invoice_id = ln_invoice_id
2427 and distribution_line_number = ln_dist_line_number;
2428
2429
2430 /* precision */
2431 Cursor get_prec (lv_currency_code varchar2) is
2432 select precision
2433 from fnd_currencies
2434 where currency_code = lv_currency_code;
2435
2436
2437 /* Local Variables */
2438 ln_tax_ipv number;
2439 ln_tax_bipv number;
2440 ln_price_var_ccid number;
2441
2442 ln_tax_erv number;
2443
2444 lv_inv_curr_code varchar2(15);
2445 lv_base_curr_code varchar2(15);
2446
2447 ln_inv_pre number;
2448 ln_base_pre number;
2449
2450 r_get_misc_lines get_misc_lines%ROWTYPE;
2451
2452
2453
2454 Begin
2455
2456
2457 fnd_file.put_line(FND_FILE.LOG, ' inside procedure ');
2458
2459 lv_base_curr_code := 'INR';
2460
2461 Begin
2462 Select invoice_currency_code
2463 into lv_inv_curr_code
2464 from ap_invoices_all
2465 where invoice_id = p_invoice_id;
2466
2467 Exception
2468 When others then
2469 null;
2470 End;
2471
2472 If lv_inv_curr_code = 'INR' Then
2473 open get_prec(lv_base_curr_code);
2474 Fetch get_prec into ln_base_pre;
2475 Close get_prec;
2476
2477 ln_inv_pre := ln_base_pre;
2478
2479 Else
2480 open get_prec(lv_inv_curr_code);
2481 Fetch get_prec into ln_inv_pre;
2482 Close get_prec;
2483
2484 open get_prec(lv_base_curr_code);
2485 Fetch get_prec into ln_base_pre;
2486 Close get_prec;
2487
2488 End if;
2489
2490 fnd_file.put_line(FND_FILE.LOG, ' invoice id '|| p_invoice_id);
2491 fnd_file.put_line(FND_FILE.LOG, ' po dist id '|| p_po_dist_id);
2492
2493 for Misc_loop in ( select *
2494 from JAI_AP_MATCH_INV_TAXES
2495 where invoice_id = p_invoice_id
2496 and parent_invoice_distribution_id = p_invoice_distribution_id
2497 )
2498 loop
2499
2500
2501 fnd_file.put_line(FND_FILE.LOG,' inside loop -- 2 ' );
2502
2503 /* For later use if necessary to check the tax type. now education cess will not be
2504 created at invoice level if it is available in PO/Receipt level
2505
2506 for tax_loop in check_rec_tax (select tax_id
2507 from ja_in_ap_tax_distributions
2508 where invoice_id = misc_loop.invoice_id
2509 and distribution_line_number = misc_loop.distribution_line_number)
2510 loop
2511
2512 Service and Education cess are recoverable taxes and
2513 IPV should not be calculated on these lines
2514 If not (tax_loop.tax_type like '%EDUCATION_CESS') Then
2515
2516 */
2517
2518 Open get_misc_lines(misc_loop.distribution_line_number, misc_loop.invoice_id);
2519 Fetch get_misc_lines into r_get_misc_lines;
2520 Close get_misc_lines;
2521
2522 If nvl(p_amount ,0) <> 0 Then
2523
2524 fnd_file.put_line(FND_FILE.LOG,' Inside item amount not zero ' || p_amount);
2525
2526 If nvl(r_get_misc_lines.amount , 0 ) <> 0 Then
2527
2528 fnd_file.put_line(FND_FILE.LOG,' Inside Tax amount not zero ' || r_get_misc_lines.amount);
2529
2530 IF nvl(p_invoice_price_variance,0 ) <> 0 Then
2531
2532 ln_tax_ipv := r_get_misc_lines.amount * (nvl(p_invoice_price_variance,0) /p_amount);
2533
2534 End if;
2535
2536 IF nvl(p_exchange_rate_variance,0 ) <> 0 Then
2537
2538 ln_tax_erv := r_get_misc_lines.amount * (nvl(p_exchange_rate_variance,0)/p_amount);
2539
2540 End if;
2541
2542 fnd_file.put_line(FND_FILE.LOG,' IPV '|| ln_tax_ipv);
2543 fnd_file.put_line(FND_FILE.LOG,' ERV '|| ln_tax_erv);
2544
2545 /* IPV */
2546
2547 If nvl(ln_tax_ipv,0) <> 0 then
2548
2549 fnd_file.put_line(FND_FILE.LOG,' Inside IPV not zero '|| ln_tax_ipv);
2550
2551 ln_tax_bipv := ln_tax_ipv * nvl(r_get_misc_lines.exchange_rate,1);
2552
2553 update ap_invoice_distributions_all
2554 set invoice_price_variance = round(ln_tax_ipv,ln_inv_pre),
2555 base_invoice_price_variance = round(ln_tax_bipv, ln_base_pre),
2556 price_var_code_combination_id = P_price_var_ccid
2557 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
2558 End if;
2559
2560 /* ERV */
2561
2562
2563 If nvl(ln_tax_erv,0) <> 0 then
2564
2565 fnd_file.put_line(FND_FILE.LOG,' Inside ERV not zero '|| ln_tax_erv);
2566 fnd_file.put_line(FND_FILE.LOG,' rate var CCID '|| P_rate_var_ccid);
2567
2568 update ap_invoice_distributions_all
2569 set exchange_rate_variance = round(ln_tax_erv,ln_inv_pre),
2570 rate_var_code_combination_id = P_rate_var_ccid
2571 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
2572 End if;
2573
2574
2575 Else
2576
2577 /* update ipv and bipv to 0. no need to update Var CCID */
2578
2579 update ap_invoice_distributions_all
2580 set invoice_price_variance = 0,
2581 base_invoice_price_variance = 0,
2582 exchange_rate_variance = 0
2583 where invoice_distribution_id = r_get_misc_lines.invoice_distribution_id;
2584 End if;
2585 /* r_get_misc_lines.amount <> 0 */
2586
2587 End if; /* p_amount <> 0 */
2588
2589 -- end loop; -- End tax_loop
2590 end loop; -- End misc_loop
2591
2592 p_errmsg :=NULL;
2593 p_retcode := NULL;
2594
2595
2596 Exception
2597 When others then
2598 P_errmsg := SQLERRM;
2599 P_retcode := 2;
2600 Fnd_File.put_line(Fnd_File.LOG, 'EXCEPTION END PROCEDURE - JAI_CALC_IPV ');
2601 Fnd_File.put_line(Fnd_File.LOG, 'Error : ' || P_errmsg);
2602 End jai_calc_ipv_erv;
2603
2604 -- added, Harshita for Bug 5553150
2605
2606 FUNCTION fetch_tax_target_amt
2607 ( p_invoice_id IN NUMBER ,
2608 p_line_location_id IN NUMBER ,
2609 p_transaction_id IN NUMBER ,
2610 p_parent_dist_id IN NUMBER,
2611 p_tax_id IN NUMBER
2612 )
2613 RETURN NUMBER
2614 IS
2615
2616 TYPE TAX_CUR IS RECORD
2617 (
2618 P_1 JAI_PO_TAXES.precedence_1%type,
2619 P_2 JAI_PO_TAXES.precedence_2%type,
2620 P_3 JAI_PO_TAXES.precedence_3%type,
2621 P_4 JAI_PO_TAXES.precedence_4%type,
2622 P_5 JAI_PO_TAXES.precedence_5%type,
2623 P_6 JAI_PO_TAXES.precedence_6%type,
2624 P_7 JAI_PO_TAXES.precedence_7%type,
2625 P_8 JAI_PO_TAXES.precedence_8%type,
2626 P_9 JAI_PO_TAXES.precedence_9%type,
2627 P_10 JAI_PO_TAXES.precedence_10%type
2628 ) ;
2629
2630 TYPE tax_cur_type IS REF CURSOR RETURN TAX_CUR;
2631 c_tax_cur TAX_CUR_TYPE;
2632 rec c_tax_cur%ROWTYPE;
2633 ln_base_amt number ;
2634
2635
2636 FUNCTION fetch_line_amt(p_precedence_value IN NUMBER)
2637 RETURN NUMBER
2638 IS
2639 cursor c_line_amt
2640 is
2641 select NVL(tax_amount,-1) -- 5763527, Added by kunkumar for Bug#5593895
2642 from JAI_AP_MATCH_INV_TAXES
2643 where invoice_id = p_invoice_id
2644 AND parent_invoice_distribution_id = p_parent_dist_id /*bug 9346307*/
2645 and line_no = p_precedence_value ;
2646
2647 cursor c_base_inv_amt
2648 is
2649 select amount
2650 from ap_invoice_distributions_all
2651 where invoice_distribution_id = p_parent_dist_id
2652 and invoice_id = p_invoice_id ;
2653
2654 ln_line_amt number ;
2655
2656 BEGIN
2657 if p_precedence_value = -1 then
2658 return 0 ;
2659 elsif p_precedence_value = 0 then
2660 open c_base_inv_amt ;
2661 fetch c_base_inv_amt into ln_line_amt ;
2662 close c_base_inv_amt ;
2663 return nvl(ln_line_amt,0) ;
2664 else
2665 open c_line_amt ;
2666 fetch c_line_amt into ln_line_amt ;
2667 close c_line_amt ;
2668 return nvl(ln_line_amt,0) ;
2669 end if ;
2670
2671 END fetch_line_amt;
2672
2673 BEGIN
2674
2675 IF p_line_location_id is not null then
2676 OPEN c_tax_cur FOR
2677 select Precedence_1 P_1,
2678 Precedence_2 P_2,
2679 Precedence_3 P_3,
2680 Precedence_4 P_4,
2681 Precedence_5 P_5,
2682 Precedence_6 P_6,
2683 Precedence_7 P_7,
2684 Precedence_8 P_8,
2685 Precedence_9 P_9,
2686 Precedence_10 P_10
2687 from JAI_PO_TAXES
2688 where line_location_id = p_line_location_id
2689 and tax_id = p_tax_id ;
2690 ELSE
2691 OPEN c_tax_cur FOR
2692 select Precedence_1 P_1,
2693 Precedence_2 P_2,
2694 Precedence_3 P_3,
2695 Precedence_4 P_4,
2696 Precedence_5 P_5,
2697 Precedence_6 P_6,
2698 Precedence_7 P_7,
2699 Precedence_8 P_8,
2700 Precedence_9 P_9,
2701 Precedence_10 P_10
2702 from JAI_RCV_LINE_TAXES
2703 where shipment_line_id IN
2704 ( select shipment_line_id
2705 from JAI_RCV_LINE_TAXES
2706 where transaction_id = p_transaction_id
2707 )
2708 and tax_id = p_tax_id ;
2709
2710 END IF ;
2711
2712 FETCH c_tax_cur INTO rec;
2713 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))
2714 + fetch_line_amt(nvl(rec.P_4,-1)) + fetch_line_amt(nvl(rec.P_5,-1)) + fetch_line_amt(nvl(rec.P_6,-1))
2715 + fetch_line_amt(nvl(rec.P_7,-1)) + fetch_line_amt(nvl(rec.P_8,-1)) + fetch_line_amt(nvl(rec.P_9,-1))
2716 + fetch_line_amt(nvl(rec.P_10,-1)) ;
2717 CLOSE c_tax_cur ;
2718 return ln_base_amt ;
2719
2720
2721 END fetch_tax_target_amt ;
2722 -- ended, Harshita for Bug 5553150
2723 /*------------------------------------------------------------------------------------------------------------*/
2724 -- End 4579729
2725 /*------------------------------------------------------------------------------------------------------------*/
2726
2727
2728 -- Added by Jia Li for Tax inclusive computation on 2007/12/17, Begin
2729 --==========================================================================
2730 -- FUNCTION NAME:
2731 --
2732 -- get_tax_account_id Private
2733 --
2734 -- DESCRIPTION:
2735 --
2736 -- This function is get tax account ccid
2737 --
2738 -- PARAMETERS:
2739 -- In: pn_tax_id
2740 -- pn_tax_type
2741 -- pn_org_id
2742 --
2743 -- DESIGN REFERENCES:
2744 -- Inclusive Tax Technical Design V1.4.doc
2745 --
2746 -- CHANGE HISTORY:
2747 --
2748 -- 20-DEC-2007 Jia Li created
2749 --==========================================================================
2750 FUNCTION get_tax_account_id
2751 ( pn_tax_id IN NUMBER
2752 , pv_tax_type IN VARCHAR2
2753 , pn_org_id IN NUMBER
2754 )
2755 RETURN NUMBER
2756 IS
2757 ln_tax_def_acc_id NUMBER;
2758 ln_tax_rgm_acc_id NUMBER;
2759 ln_tax_acc_id NUMBER;
2760 lv_procedure_name VARCHAR2(40):='get_tax_account_id';
2761 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2762 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2763
2764 CURSOR cur_tax_acc IS
2765 SELECT
2766 tax_account_id
2767 FROM
2768 jai_cmn_taxes_all
2769 WHERE tax_id = pn_tax_id;
2770
2771 BEGIN
2772 --logging for debug
2773 IF (ln_proc_level >= ln_dbg_level)
2774 THEN
2775 FND_LOG.STRING( ln_proc_level
2776 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2777 , 'Enter procedure'
2778 );
2779 FND_LOG.STRING( ln_proc_level
2780 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
2781 , 'Org_id = ' || pn_org_id
2782 );
2783 FND_LOG.STRING( ln_proc_level
2784 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
2785 , 'Tax_id = '|| pn_tax_id ||' Tax_type = ' || pv_tax_type
2786 );
2787 END IF; --ln_proc_level>=l_dbg_level
2788
2789 -- Get tax_account_id from tax defination
2790 OPEN cur_tax_acc;
2791 FETCH cur_tax_acc INTO ln_tax_def_acc_id;
2792 CLOSE cur_tax_acc;
2793
2794 -- Get tax_account_id from rgm setup for SERVICE and VAT tax.
2795 BEGIN
2796 SELECT
2797 TO_NUMBER(acc_rgm.attribute_value)
2798 INTO
2799 ln_tax_rgm_acc_id
2800 FROM
2801 jai_rgm_definitions rgm_def
2802 , jai_rgm_registrations tax_rgm
2803 , jai_rgm_registrations acc_rgm
2804 WHERE regime_code IN (jai_constants.service_regime,jai_constants.vat_regime)
2805 AND tax_rgm.regime_id = rgm_def.regime_id
2806 AND tax_rgm.registration_type = jai_constants.regn_type_tax_types
2807 AND tax_rgm.attribute_code = pv_tax_type
2808 AND tax_rgm.regime_id = acc_rgm.regime_id
2809 AND acc_rgm.registration_type = jai_constants.regn_type_accounts
2810 AND acc_rgm.attribute_code = jai_constants.recovery_interim
2811 AND acc_rgm.parent_registration_id = tax_rgm.registration_id;
2812
2813 ln_tax_acc_id := ln_tax_rgm_acc_id;
2814 EXCEPTION
2815 WHEN no_data_found THEN
2816 ln_tax_acc_id := ln_tax_def_acc_id;
2817 WHEN OTHERS THEN
2818 ln_tax_acc_id := -1;
2819 END;
2820
2821 IF (ln_proc_level >= ln_dbg_level)
2822 THEN
2823 FND_LOG.STRING( ln_proc_level
2824 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.result'
2825 , 'Tax Account ID = ' || ln_tax_acc_id
2826 );
2827 FND_LOG.STRING( ln_proc_level
2828 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
2829 , 'Enter procedure'
2830 );
2831 END IF; -- ln_proc_level >= ln_dbg_level
2832
2833 RETURN ln_tax_acc_id;
2834
2835 END get_tax_account_id;
2836
2837
2838 --==========================================================================
2839 -- PROCEDURE NAME:
2840 --
2841 -- insert_gl_interface Private
2842 --
2843 -- DESCRIPTION:
2844 --
2845 -- This function is insert inclusive data into gl_interface
2846 --
2847 -- PARAMETERS:
2848 -- In: pn_set_of_books_id the set of books id
2849 -- pd_accounting_date GL date of the invoice
2850 -- pv_currency_code currency code
2851 -- pn_enter_cr credit amount
2852 -- pn_enter_dr debit amount
2853 -- pd_transaction_date invoice date
2854 -- pn_code_combination_id code_combination_id
2855 -- pd_currency_conversion_date the column values can be retreived from the invoice
2856 -- pv_currency_conversion_type the column values can be retreived from the invoice
2857 -- pv_currency_conversion_rate the column values can be retreived from the invoice
2858 -- pv_reference1 inventory organization code,base on organization_id from PO/Receipt from where it's matched
2859 -- pv_reference10 'India Localization Entry for accounting inclusive taxes for invoice'||lv_invoice_num
2860 -- pv_reference23 procedure name that makes the insert into gl_interface hard code string
2861 -- pv_reference26 value of invoice_id
2862 -- pv_reference27 organization id of the inventory organization id
2863 --
2864 -- DESIGN REFERENCES:
2865 -- Inclusive Tax Technical Design V1.4.doc
2866 --
2867 -- CHANGE HISTORY:
2868 --
2869 -- 20-DEC-2007 Jia Li created
2870 --==========================================================================
2871 PROCEDURE insert_gl_interface
2872 ( pn_set_of_books_id IN NUMBER
2873 , pd_accounting_date IN DATE
2874 , pv_currency_code IN VARCHAR2
2875 , pn_enter_cr IN NUMBER DEFAULT NULL
2876 , pn_enter_dr IN NUMBER DEFAULT NULL
2877 , pd_transaction_date IN DATE
2878 , pn_code_combination_id IN NUMBER
2879 , pd_currency_conversion_date IN DATE
2880 , pv_currency_conversion_type IN VARCHAR2
2881 , pv_currency_conversion_rate IN VARCHAR2
2882 , pv_reference1 IN VARCHAR2
2883 , pv_reference10 IN VARCHAR2
2884 , pv_reference23 IN VARCHAR2
2885 , pv_reference26 IN VARCHAR2
2886 , pv_reference27 IN VARCHAR2
2887 )
2888 IS
2889 BEGIN
2890 INSERT INTO gl_interface
2891 ( status
2892 , set_of_books_id
2893 , user_je_source_name
2894 , user_je_category_name
2895 , accounting_date
2896 , currency_code
2897 , date_created
2898 , created_by
2899 , actual_flag
2900 , entered_cr
2901 , entered_dr
2902 , transaction_date
2903 , code_combination_id
2904 , currency_conversion_date
2905 , user_currency_conversion_type
2906 , currency_conversion_rate
2907 , reference1
2908 , reference10
2909 , reference22
2910 , reference23
2911 , reference24
2912 , reference25
2913 , reference26
2914 , reference27
2915 )
2916 VALUES
2917 ( 'NEW' -- 'NEW'
2918 , pn_set_of_books_id
2919 , 'Payables India' -- je source name 'Payables India'
2920 , 'Register India' -- je category name 'Register India'
2921 , pd_accounting_date
2922 , pv_currency_code
2923 , sysdate -- standard who column
2924 , TO_NUMBER(fnd_profile.value('USER_ID')) -- standard who column
2925 , 'A' -- 'A'
2926 , pn_enter_cr
2927 , pn_enter_cr
2928 , pd_transaction_date
2929 , pn_code_combination_id
2930 , pd_currency_conversion_date
2931 , pv_currency_conversion_type
2932 , pv_currency_conversion_rate
2933 , pv_reference1
2934 , 'India Localization Entry for accounting inclusive taxes for invoice'||pv_reference10
2935 , 'India Localization Entry' -- 'India Localization Entry'
2936 , pv_reference23
2937 , 'AP_INVOICES_ALL' -- 'AP_INVOICES_ALL'
2938 , 'INVOICE_ID' -- 'INVOICE_ID'
2939 , pv_reference26
2940 , pv_reference27
2941 );
2942 END insert_gl_interface;
2943
2944
2945 --==========================================================================
2946 -- PROCEDURE NAME:
2947 --
2948 -- acct_inclu_taxes Public
2949 --
2950 -- DESCRIPTION:
2951 --
2952 -- This procedure is written that would pass GL entries for inclusive taxes in GL interface
2953 --
2954 -- PARAMETERS:
2955 -- In: pn_invoice_id pass the invoice id for which the accounting needs to done
2956 -- pn_invoice_distribution_id pass the invoice distribution id for the item line which the accounting needs to done
2957 -- OUt: xv_process_flag Indicates the process flag, 'SS' for success
2958 -- 'EE' for expected error
2959 -- 'UE' for unexpected error
2960 -- xv_process_message Indicates the process message
2961 --
2962 --
2963 -- DESIGN REFERENCES:
2964 -- Inclusive Tax Technical Design V1.4.doc
2965 --
2966 -- CHANGE HISTORY:
2967 --
2968 -- 17-DEC-2007 Jia Li created
2969 --==========================================================================
2970 PROCEDURE acct_inclu_taxes
2971 ( pn_invoice_id IN NUMBER
2972 , pn_invoice_distribution_id IN NUMBER
2973 , xv_process_flag OUT NOCOPY VARCHAR2
2974 , xv_process_message OUT NOCOPY VARCHAR2
2975 )
2976 IS
2977 ln_org_id ap_invoices_all.org_id%TYPE;
2978 ld_gl_date ap_invoices_all.gl_date%TYPE;
2979 lv_invoice_num ap_invoices_all.invoice_num%TYPE;
2980 ld_invoice_date ap_invoices_all.invoice_date%TYPE;
2981 lv_invoice_currency_code ap_invoices_all.invoice_currency_code%TYPE;
2982 ln_exchange_rate ap_invoices_all.exchange_rate%TYPE;
2983 lv_exchange_rate_type ap_invoices_all.exchange_rate_type%TYPE;
2984 ld_exchange_date ap_invoices_all.exchange_date%TYPE;
2985
2986 ln_inventory_item_id ap_invoice_lines_all.inventory_item_id %TYPE;
2987 ld_accounting_date ap_invoice_lines_all.accounting_date%TYPE;
2988 lv_match_type ap_invoice_lines_all.match_type%TYPE;
2989 ln_set_of_books_id ap_invoice_lines_all.set_of_books_id%TYPE;
2990 ln_po_dist_id ap_invoice_lines_all.po_distribution_id%TYPE;
2991 ln_po_loc_id ap_invoice_lines_all.po_line_location_id%TYPE;
2992 ln_item_line_amt ap_invoice_lines_all.amount%TYPE;
2993
2994 ln_invoice_line_num ap_invoice_distributions_all.invoice_line_number%TYPE;
2995 lv_inclu_tax_flag jai_ap_tds_years.inclusive_tax_flag%TYPE;
2996 ln_inv_org_id mtl_parameters.organization_id%TYPE;
2997 lv_inv_org_code mtl_parameters.organization_code%TYPE;
2998
2999 lv_accrue_on_receipt_flag VARCHAR2(10);
3000 ln_tax_account_id NUMBER;
3001 ln_invoice_post_num NUMBER;
3002 ln_total_inclu_tax_amt NUMBER;
3003 ln_cr_line_amt NUMBER;
3004 ln_total_cr_line_amt NUMBER;
3005
3006 lv_procedure_name VARCHAR2(40):='acct_inclu_taxes';
3007 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3008 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
3009
3010 CURSOR match_inclu_tax_cur
3011 ( pn_invoice_line_num NUMBER
3012 )
3013 IS
3014 SELECT
3015 tax_line.tax_id tax_id
3016 , tax.tax_type tax_type
3017 , SUM(tax_line.tax_amount) tax_amount
3018 FROM
3019 jai_ap_match_inv_taxes tax_line
3020 , jai_cmn_taxes_all tax
3021 WHERE NVL(tax.inclusive_tax_flag,'N') = 'Y'
3022 AND tax_line.invoice_id = pn_invoice_id
3023 AND tax_line.parent_invoice_line_number = pn_invoice_line_num
3024 AND tax_line.tax_id = tax.tax_id
3025 GROUP BY
3026 tax_line.tax_id
3027 , tax.tax_type;
3028
3029 CURSOR standalone_inclu_tax_cur
3030 ( pn_invoice_line_num NUMBER
3031 )
3032 IS
3033 SELECT
3034 tax_line.tax_id tax_id
3035 , tax.tax_type tax_type
3036 , SUM(tax_line.tax_amt) tax_amount
3037 FROM
3038 jai_cmn_document_taxes tax_line
3039 , jai_cmn_taxes_all tax
3040 WHERE NVL(tax.inclusive_tax_flag,'N') = 'Y'
3041 AND tax_line.source_doc_type = jai_constants.G_AP_STANDALONE_INVOICE
3042 AND tax_line.source_doc_id = pn_invoice_id
3043 AND tax_line.source_doc_parent_line_no = pn_invoice_line_num
3044 AND tax_line.tax_id = tax.tax_id
3045 GROUP BY
3046 tax_line.tax_id
3047 , tax.tax_type;
3048
3049 CURSOR item_line_dist_cur
3050 ( pn_invoice_line_num NUMBER
3051 )
3052 IS
3053 SELECT
3054 dist_code_combination_id
3055 , amount
3056 FROM
3057 ap_invoice_distributions_all
3058 WHERE invoice_id = pn_invoice_id
3059 AND invoice_line_number = pn_invoice_line_num;
3060
3061 BEGIN
3062 --logging for debug
3063 IF (ln_proc_level >= ln_dbg_level)
3064 THEN
3065 FND_LOG.STRING( ln_proc_level
3066 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
3067 , 'Enter procedure'
3068 );
3069 END IF; --ln_proc_level>=l_dbg_level
3070
3071 -- Init variable
3072 ln_total_inclu_tax_amt := 0;
3073 ln_cr_line_amt := 0;
3074 ln_total_cr_line_amt := 0;
3075 ln_invoice_post_num := 0;
3076
3077 -- Get invoice info
3078 SELECT
3079 org_id
3080 , gl_date
3081 , invoice_num
3082 , invoice_date
3083 , invoice_currency_code
3084 , exchange_rate
3085 , exchange_rate_type
3086 , exchange_date
3087 INTO
3088 ln_org_id
3089 , ld_gl_date
3090 , lv_invoice_num
3091 , ld_invoice_date
3092 , lv_invoice_currency_code
3093 , ln_exchange_rate
3094 , lv_exchange_rate_type
3095 , ld_exchange_date
3096 FROM
3097 ap_invoices_all
3098 WHERE invoice_id = pn_invoice_id;
3099
3100 -- Check whether inclusive taxes needs to be accounted separately
3101 BEGIN
3102 SELECT
3103 NVL(ja.inclusive_tax_flag, 'N') inclusive_tax_flag
3104 INTO
3105 lv_inclu_tax_flag
3106 FROM
3107 jai_ap_tds_years ja
3108 WHERE ja.legal_entity_id = ln_org_id
3109 AND sysdate BETWEEN ja.start_date AND ja.end_date;
3110 EXCEPTION
3111 WHEN OTHERS THEN
3112 lv_inclu_tax_flag := 'N';
3113 END;
3114
3115 -- If the user has setup to account inclusive aeparately, inclusive taxes need insert into GL Interface table.
3116 IF lv_inclu_tax_flag = 'Y'
3117 THEN
3118
3119 -- According pn_invoice_distribution_id to get ITEM line num .
3120 BEGIN
3121 SELECT
3122 aila.line_number
3123 INTO
3124 ln_invoice_line_num
3125 FROM
3126 ap_invoice_distributions_all aida
3127 , ap_invoice_lines_all aila
3128 WHERE aida.invoice_distribution_id = pn_invoice_distribution_id
3129 AND aila.line_number = aida.invoice_line_number
3130 AND aila.invoice_id = pn_invoice_id
3131 AND aila.line_type_lookup_code = 'ITEM';
3132 EXCEPTION
3133 WHEN OTHERS THEN
3134 ln_invoice_line_num := 0;
3135 END;
3136
3137 IF ln_invoice_line_num > 0
3138 THEN
3139 -- Get item invoice line info
3140 SELECT
3141 inventory_item_id
3142 , set_of_books_id
3143 , match_type
3144 , po_distribution_id
3145 , po_line_location_id
3146 , amount
3147 INTO
3148 ln_inventory_item_id
3149 , ln_set_of_books_id
3150 , lv_match_type
3151 , ln_po_dist_id
3152 , ln_po_loc_id
3153 , ln_item_line_amt
3154 FROM
3155 ap_invoice_lines_all
3156 WHERE invoice_id = pn_invoice_id
3157 AND line_number = ln_invoice_line_num;
3158
3159 -- Get inv_organization_id and inv_organization_code
3160 IF ln_po_dist_id IS NULL
3161 THEN
3162 lv_match_type := jai_constants.G_AP_STANDALONE_INVOICE;
3163 ln_inv_org_id := NULL;
3164 lv_inv_org_code := '';
3165 ELSE
3166 SELECT
3167 ploc.ship_to_organization_id
3168 , mp.organization_code
3169 INTO
3170 ln_inv_org_id
3171 , lv_inv_org_code
3172 FROM
3173 po_line_locations_all ploc
3174 , mtl_parameters mp
3175 WHERE ploc.line_location_id = ln_po_loc_id
3176 AND ploc.ship_to_organization_id = mp.organization_id;
3177 END IF; -- ln_po_dist_id IS NULL
3178
3179 -- According item invoice line num to get distribution quantity that has been transfer to gl
3180 SELECT
3181 COUNT(invoice_distribution_id)
3182 INTO
3183 ln_invoice_post_num
3184 FROM
3185 ap_invoice_distributions_all aida
3186 WHERE aida.invoice_id = pn_invoice_id
3187 AND aida.invoice_line_number = ln_invoice_line_num
3188 AND aida.posted_flag = 'Y';
3189
3190 -- if only one distribution line has been transfer to GL, then insert inclusive data into GL interface
3191 IF ln_invoice_post_num = 1
3192 THEN
3193 IF lv_match_type = jai_constants.G_AP_STANDALONE_INVOICE
3194 THEN
3195 -- Get inclusive tax info from jai_cmn_document_taxes
3196 -- and insert debit inclusive taxes into GL interface table.
3197 FOR standalone_inclu_tax_csr IN standalone_inclu_tax_cur(ln_invoice_line_num)
3198 LOOP
3199 ln_tax_account_id := get_tax_account_id
3200 ( pn_tax_id => standalone_inclu_tax_csr.tax_id
3201 , pv_tax_type => standalone_inclu_tax_csr.tax_type
3202 , pn_org_id => ln_org_id
3203 );
3204 insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
3205 , pd_accounting_date => ld_gl_date
3206 , pv_currency_code => lv_invoice_currency_code
3207 , pn_enter_dr => standalone_inclu_tax_csr.tax_amount
3208 , pd_transaction_date => ld_invoice_date
3209 , pn_code_combination_id => ln_tax_account_id
3210 , pd_currency_conversion_date => ld_exchange_date
3211 , pv_currency_conversion_type => lv_exchange_rate_type
3212 , pv_currency_conversion_rate => ln_exchange_rate
3213 , pv_reference1 => lv_inv_org_code
3214 , pv_reference10 => lv_invoice_num
3215 , pv_reference23 => lv_procedure_name
3216 , pv_reference26 => pn_invoice_id
3217 , pv_reference27 => ln_inv_org_id
3218 ) ;
3219
3220 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + standalone_inclu_tax_csr.tax_amount;
3221 IF (ln_proc_level >= ln_dbg_level)
3222 THEN
3223 FND_LOG.STRING( ln_proc_level
3224 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3225 , 'Inclusive tax account = '|| ln_tax_account_id
3226 );
3227 FND_LOG.STRING( ln_proc_level
3228 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3229 , 'Inclusive tax amount = '|| standalone_inclu_tax_csr.tax_amount
3230 );
3231 END IF; --ln_proc_level>=l_dbg_level
3232 END LOOP; -- for standalone_inclu_tax_cur cursor
3233 ELSE
3234 -- Get inclusive tax info from jai_ap_match_inv_taxes
3235 -- and insert debit inclusive taxes into GL interface table.
3236 FOR match_inclu_tax_csr IN match_inclu_tax_cur(ln_invoice_line_num)
3237 LOOP
3238 ln_tax_account_id := get_tax_account_id
3239 ( pn_tax_id => match_inclu_tax_csr.tax_id
3240 , pv_tax_type => match_inclu_tax_csr.tax_type
3241 , pn_org_id => ln_org_id
3242 );
3243 insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
3244 , pd_accounting_date => ld_gl_date
3245 , pv_currency_code => lv_invoice_currency_code
3246 , pn_enter_dr => match_inclu_tax_csr.tax_amount
3247 , pd_transaction_date => ld_invoice_date
3248 , pn_code_combination_id => ln_tax_account_id
3249 , pd_currency_conversion_date => ld_exchange_date
3250 , pv_currency_conversion_type => lv_exchange_rate_type
3251 , pv_currency_conversion_rate => ln_exchange_rate
3252 , pv_reference1 => lv_inv_org_code
3253 , pv_reference10 => lv_invoice_num
3254 , pv_reference23 => lv_procedure_name
3255 , pv_reference26 => pn_invoice_id
3256 , pv_reference27 => ln_inv_org_id
3257 ) ;
3258
3259 ln_total_inclu_tax_amt := ln_total_inclu_tax_amt + match_inclu_tax_csr.tax_amount;
3260 IF (ln_proc_level >= ln_dbg_level)
3261 THEN
3262 FND_LOG.STRING( ln_proc_level
3263 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3264 , 'Inclusive tax account = '|| ln_tax_account_id
3265 );
3266 FND_LOG.STRING( ln_proc_level
3267 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.debug'
3268 , 'Inclusive tax amount = '|| match_inclu_tax_csr.tax_amount
3269 );
3270 END IF; --ln_proc_level>=l_dbg_level
3271 END LOOP; -- for match_inclu_tax_cur cursor
3272 END IF; -- lv_match_type = 'STANDALONE_INVOICE'
3273
3274 -- Get item distribution line dist_code_combination_id and amount.
3275 -- and insert credit data into GL interface table.
3276 FOR item_line_dist_csr IN item_line_dist_cur(ln_invoice_line_num)
3277 LOOP
3278 IF ln_item_line_amt <> 0
3279 THEN
3280 ln_cr_line_amt := ( item_line_dist_csr.amount / ln_item_line_amt )
3281 * ln_total_inclu_tax_amt;
3282
3283 insert_gl_interface( pn_set_of_books_id => ln_set_of_books_id
3284 , pd_accounting_date => ld_gl_date
3285 , pv_currency_code => lv_invoice_currency_code
3286 , pn_enter_cr => ln_cr_line_amt
3287 , pd_transaction_date => ld_invoice_date
3288 , pn_code_combination_id => item_line_dist_csr.dist_code_combination_id
3289 , pd_currency_conversion_date => ld_exchange_date
3290 , pv_currency_conversion_type => lv_exchange_rate_type
3291 , pv_currency_conversion_rate => ln_exchange_rate
3292 , pv_reference1 => lv_inv_org_code
3293 , pv_reference10 => lv_invoice_num
3294 , pv_reference23 => lv_procedure_name
3295 , pv_reference26 => pn_invoice_id
3296 , pv_reference27 => ln_inv_org_id
3297 ) ;
3298 ln_total_cr_line_amt := ln_total_cr_line_amt + ln_cr_line_amt;
3299 END IF; -- ln_item_line_amt <> 0
3300 END LOOP; -- for item_line_dist_cur cursor
3301
3302 END IF; -- ln_invoice_post_num = 1
3303
3304 END IF; -- ln_invoice_line_num > 0
3305
3306 xv_process_flag := 'SS';
3307 xv_process_message := 'Inclusive taxes have successed into GL Interface';
3308 ELSE
3309 xv_process_flag := 'SS';
3310 xv_process_message := 'Inclusive taxes not be separately';
3311 END IF; -- lv_inclu_tax_flag = 'Y'
3312
3313 --logging for debug
3314 IF (ln_proc_level >= ln_dbg_level)
3315 THEN
3316 FND_LOG.STRING( ln_proc_level
3317 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
3318 , 'Exit procedure'
3319 );
3320 END IF; -- (ln_proc_level>=ln_dbg_level)
3321
3322 EXCEPTION
3323 WHEN OTHERS THEN
3324 xv_process_flag := 'UE';
3325 xv_process_message := 'Exception error in acct_inclu_taxes procedure';
3326
3327 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3328 THEN
3329 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
3330 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
3331 , Sqlcode||Sqlerrm);
3332 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3333
3334 END acct_inclu_taxes;
3335
3336 -- Added by Jia Li on tax inclusive computation on 2007/12/17, End
3337
3338 -- Code Ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
3339
3340 /*Added by Wenqiong for DTC begin*/
3341 ------------------------------------------------------------------------------------------------
3342 FUNCTION get_natural_account(pn_sob_id NUMBER, pn_legal_entity_id NUMBER) RETURN VARCHAR2
3343 IS
3344 CURSOR cur_get_coa IS
3345 SELECT CHART_OF_ACCOUNTS_ID
3346 FROM GL_LEDGER_LE_V
3347 WHERE LEDGER_ID = pn_sob_id
3348 AND LEGAL_ENTITY_ID = pn_legal_entity_id;
3349
3350
3351 CURSOR cur_get_account_segment (cn_coa_id NUMBER)IS
3352 SELECT APPLICATION_COLUMN_NAME SEGMENT_NAME
3353 FROM FND_ID_FLEX_SEGMENTS
3354 WHERE ID_FLEX_CODE ='GL#'
3355 AND ID_FLEX_NUM = cn_coa_id
3356 AND FLEX_VALUE_SET_ID = (SELECT FLEX_VALUE_SET_ID
3357 FROM FND_ID_FLEX_SEGMENTS SEG , FND_SEGMENT_ATTRIBUTE_VALUES AT
3358 WHERE SEG.APPLICATION_ID = 101
3359 AND SEG.ID_FLEX_CODE = 'GL#'
3360 AND SEG.ID_FLEX_NUM = cn_coa_id
3361 AND AT.APPLICATION_ID = SEG.APPLICATION_ID
3362 AND AT.ID_FLEX_CODE = SEG.ID_FLEX_CODE
3363 AND AT.ID_FLEX_NUM = SEG.ID_FLEX_NUM
3364 AND AT.APPLICATION_COLUMN_NAME = SEG.APPLICATION_COLUMN_NAME
3365 AND AT.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
3366 AND ATTRIBUTE_VALUE = 'Y');
3367
3368 -----------------------------------------------------------------
3369 --Variables Declare
3370 -----------------------------------------------------------------
3371 ln_coa_id NUMBER;
3372 lv_segment_name FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE;
3373 lv_dynamic_sql VARCHAR2(240);
3374 lv_natual_account_segment JAI_DTC_SCTN_ACCOUNT_MAPPING.NATURAL_ACCOUNT_VALUE%TYPE ;
3375 ln_dist_ccid NUMBER;
3376 ln_invoice_id NUMBER;
3377
3378 BEGIN
3379
3380 OPEN cur_get_coa;
3381 FETCH cur_get_coa INTO ln_coa_id;
3382 CLOSE cur_get_coa;
3383
3384 OPEN cur_get_account_segment(ln_coa_id);
3385 FETCH cur_get_account_segment INTO lv_segment_name;
3386 CLOSE cur_get_account_segment;
3387
3388 RETURN lv_segment_name;
3389 END get_natural_account;
3390
3391 FUNCTION get_section_code(PN_DISTRIBUTION_CCID NUMBER
3392 ,PV_SEGMENT_NAME VARCHAR2
3393 ,Pn_LEGAL_ENTITY_ID NUMBER
3394 ,p_accounting_date Date default NULL --Added by Chong for DTC bug#15939571 20121210
3395 ) RETURN VARCHAR2
3396 IS
3397 -----------------------------------------------------------------
3398 --Cursors Declare
3399 -----------------------------------------------------------------
3400 CURSOR cur_get_section_code(cv_natural_account VARCHAR2
3401 ,cn_legal_entity_id NUMBER
3402 ,cd_accounting_date DATE --Added by Chong for DTC bug#15939571 20121210
3403 ) IS
3404 SELECT DTC_SECTION_CODE
3405 FROM JAI_DTC_SCTN_ACCOUNT_MAPPING
3406 WHERE NATURAL_ACCOUNT_VALUE = cv_natural_account
3407 --Added by Chong for bug#15939571 20121210 Start
3408 -----------------------------------------------------------------------------------------------------
3409 AND from_date <= NVL(cd_accounting_date, sysdate)
3410 AND NVL(to_date, TO_DATE('01019999','DDMMYYYY')) >= NVL(cd_accounting_date, sysdate)
3411 -----------------------------------------------------------------------------------------------------
3412 --Added by Chong for bug#15939571 20121210 End
3413 AND LEGAL_ENTITY_ID = cn_legal_entity_id;
3414
3415 -----------------------------------------------------------------
3416 --Variables Declare
3417 -----------------------------------------------------------------
3418 lv_dynamic_sql VARCHAR2(240);
3419 lv_natual_account_segment JAI_DTC_SCTN_ACCOUNT_MAPPING.NATURAL_ACCOUNT_VALUE%TYPE ;
3420 lv_section_code VARCHAR2(50);
3421 BEGIN
3422
3423 lv_dynamic_sql := 'SELECT ' || PV_SEGMENT_NAME || ' FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = '
3424 || PN_DISTRIBUTION_CCID;
3425 EXECUTE IMMEDIATE lv_dynamic_sql INTO lv_natual_account_segment;
3426
3427 OPEN cur_get_section_code(lv_natual_account_segment
3428 ,Pn_LEGAL_ENTITY_ID
3429 ,p_accounting_date --Added by Chong for DTC bug#15939571 20121210
3430 );
3431 FETCH cur_get_section_code INTO lv_section_code;
3432 CLOSE cur_get_section_code;
3433
3434 jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_STATEMENT, 'JAI.PLSQL.JAI_AP_UTILS_PKG.get_section_code', 'get effective section code:' || lv_section_code);
3435 RETURN lv_section_code;
3436 END get_section_code;
3437
3438 FUNCTION GET_NATURAL_ACCOUNT_VALUE (PN_DISTRIBUTION_CCID NUMBER, PV_SEGMENT_NAME VARCHAR2) RETURN VARCHAR2
3439 IS
3440 lv_dynamic_sql VARCHAR2(240);
3441 lv_natual_account_segment JAI_DTC_SCTN_ACCOUNT_MAPPING.NATURAL_ACCOUNT_VALUE%TYPE ;
3442 BEGIN
3443 lv_dynamic_sql := 'SELECT ' || PV_SEGMENT_NAME || ' FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = '
3444 || PN_DISTRIBUTION_CCID;
3445 EXECUTE IMMEDIATE lv_dynamic_sql INTO lv_natual_account_segment;
3446 RETURN lv_natual_account_segment;
3447 END GET_NATURAL_ACCOUNT_VALUE;
3448 FUNCTION enable_tds_menu(pn_vendor_id NUMBER,pv_invoice_type VARCHAR2, pn_invoice_id NUMBER) RETURN VARCHAR2
3449 IS
3450 lv_enable_menu VARCHAR2(1);
3451 BEGIN
3452 if pv_invoice_type in ('CREDIT','DEBIT') or is_tds_calendar_missing = 'Y'
3453 or is_tds_vendor_setup_missing(pn_vendor_id) = 'Y' OR is_invoice_misc_missing(pn_invoice_id) = 'Y' then
3454 lv_enable_menu := 'N';
3455 else
3456 lv_enable_menu := 'Y';
3457 end if;
3458 RETURN lv_enable_menu;
3459 END enable_tds_menu;
3460
3461 FUNCTION is_tds_calendar_missing RETURN VARCHAR2 IS
3462 cursor c_check_tds_calendar_setup is
3463 SELECT '1'
3464 FROM JAI_RGM_REGISTRATIONS reg,
3465 jai_rgm_definitions def
3466 WHERE
3467 reg.REGISTRATION_TYPE = 'OTHERS' AND
3468 reg.ATTRIBUTE_CODE = 'TDS_CALENDAR' AND
3469 reg.regime_id= def.regime_id AND
3470 def.regime_code = 'TDS';
3471
3472 lv_tds_calendar_setup_flag varchar2(1);
3473 is_tds_calendar_missing VARCHAR2(1);
3474
3475 BEGIN
3476 open c_check_tds_calendar_setup;
3477 fetch c_check_tds_calendar_setup into lv_tds_calendar_setup_flag;
3478 close c_check_tds_calendar_setup;
3479
3480 IF nvl(lv_tds_calendar_setup_flag,'N') <> '1' THEN
3481 is_tds_calendar_missing := 'Y';
3482 ELSE
3483 is_tds_calendar_missing := 'N';
3484 END IF;
3485 RETURN is_tds_calendar_missing;
3486 END is_tds_calendar_missing;
3487 FUNCTION is_tds_vendor_setup_missing(pn_vendor_id NUMBER) RETURN VARCHAR2 IS
3488 cursor c_check_vendor_setup is
3489 select '1' from
3490 JAI_AP_TDS_VENDOR_HDRS
3491 where vendor_id = pn_vendor_id;
3492
3493 lv_vendor_setup_flag varchar2(1);
3494 is_tds_vendor_missing VARCHAR2(1);
3495
3496 BEGIN
3497 open c_check_vendor_setup;
3498 fetch c_check_vendor_setup into lv_vendor_setup_flag;
3499 close c_check_vendor_setup;
3500 IF nvl(lv_vendor_setup_flag,'N') <> '1' THEN
3501 is_tds_vendor_missing := 'Y';
3502 ELSE
3503 is_tds_vendor_missing := 'N';
3504 END IF;
3505 RETURN is_tds_vendor_missing;
3506 END is_tds_vendor_setup_missing;
3507
3508 FUNCTION is_invoice_misc_missing(pn_invoice_id NUMBER) RETURN VARCHAR2 IS
3509 CURSOR c_check_dist_exists IS
3510 SELECT '1' FROM
3511 ap_invoice_distributions_all
3512 WHERE invoice_id = pn_invoice_id;
3513
3514 lv_dist_exists_flag varchar2(1);
3515 is_invoice_misc_missing VARCHAR2(1);
3516
3517 BEGIN
3518 open c_check_dist_exists;
3519 fetch c_check_dist_exists into lv_dist_exists_flag;
3520 close c_check_dist_exists;
3521
3522 IF nvl(lv_dist_exists_flag,'N') <> '1' THEN
3523 is_invoice_misc_missing := 'Y';
3524 ELSE
3525 is_invoice_misc_missing := 'N';
3526 END IF;
3527 RETURN is_invoice_misc_missing;
3528 END is_invoice_misc_missing;
3529 ------------------------------------------------------------------------------------------------
3530 /*Added by Wenqiong for DTC end*/
3531
3532 --Added by Chong.Lei for DTC bug#13359892 on 20111205 begin
3533 ------------------------------------------------------------------------------------------------
3534 PROCEDURE auto_create_category(p_tax_category_name IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_NAME%TYPE
3535 ,p_tax_category_desc IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_DESC%TYPE
3536 ,p_item_class_cd IN JAI_CMN_TAX_CTGS_ALL.ITEM_CLASS_CD%TYPE
3537 ,p_org_id IN JAI_CMN_TAX_CTGS_ALL.ORG_ID%TYPE
3538 ,p_object_version_number IN JAI_CMN_TAX_CTGS_ALL.OBJECT_VERSION_NUMBER%TYPE
3539 ,p_creation_date IN JAI_CMN_TAX_CTGS_ALL.CREATION_DATE%TYPE
3540 ,p_created_by IN JAI_CMN_TAX_CTGS_ALL.CREATED_BY%TYPE
3541 ,p_last_update_date IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATE_DATE%TYPE
3542 ,p_last_updated_by IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATED_BY%TYPE
3543 ,p_last_update_login IN JAI_CMN_TAX_CTGS_ALL.LAST_UPDATE_LOGIN%TYPE
3544 ,p_tax_id IN JAI_CMN_TAX_CTG_LINES.TAX_ID%TYPE
3545 )
3546 IS
3547 CURSOR tax_cat_id IS
3548 SELECT JAI_CMN_TAX_CTGS_ALL_S.nextval val_id
3549 FROM dual;
3550
3551 CURSOR tax_cat is SELECT 'X' X
3552 FROM JAI_CMN_TAX_CTGS_ALL
3553 WHERE tax_category_name = p_tax_category_name
3554 AND org_id = to_number(p_org_id);
3555
3556 ln_val_id NUMBER;
3557 BEGIN
3558
3559 --check tax category ID if already exist
3560 FOR rec IN tax_cat
3561 LOOP
3562 IF rec.x IS NOT NULL THEN
3563 FND_MESSAGE.SET_NAME('JA','JAI_TAX_CTG_EXISTS');
3564 APP_EXCEPTION.RAISE_EXCEPTION;
3565 END IF;
3566 END LOOP;
3567
3568 --get category id from sequence
3569 FOR reco IN tax_cat_id LOOP
3570 ln_val_id := reco.val_id;
3571 END LOOP;
3572
3573 --create tax category header line
3574 INSERT INTO JAI_CMN_TAX_CTGS_ALL(
3575 TAX_CATEGORY_ID
3576 ,TAX_CATEGORY_NAME
3577 ,TAX_CATEGORY_DESC
3578 ,ITEM_CLASS_CD
3579 ,ORG_ID
3580 ,OBJECT_VERSION_NUMBER
3581 ,CREATION_DATE
3582 ,CREATED_BY
3583 ,LAST_UPDATE_DATE
3584 ,LAST_UPDATED_BY
3585 ,LAST_UPDATE_LOGIN
3586 )
3587 VALUES(
3588 ln_val_id
3589 ,p_tax_category_name
3590 ,p_tax_category_desc
3591 ,p_item_class_cd
3592 ,p_org_id
3593 ,p_object_version_number
3594 ,p_creation_date
3595 ,p_created_by
3596 ,p_last_update_date
3597 ,p_last_updated_by
3598 ,p_last_update_login
3599 );
3600
3601 --create tax category detail line
3602 INSERT INTO JAI_CMN_TAX_CTG_LINES(
3603 TAX_CATEGORY_ID
3604 ,TAX_ID
3605 ,LINE_NO
3606 ,PRECEDENCE_1
3607 ,PRECEDENCE_2
3608 ,PRECEDENCE_3
3609 ,PRECEDENCE_4
3610 ,PRECEDENCE_5
3611 ,PRECEDENCE_6
3612 ,PRECEDENCE_7
3613 ,PRECEDENCE_8
3614 ,PRECEDENCE_9
3615 ,PRECEDENCE_10
3616 ,OBJECT_VERSION_NUMBER
3617 ,CREATION_DATE
3618 ,CREATED_BY
3619 ,LAST_UPDATE_DATE
3620 ,LAST_UPDATED_BY
3621 ,LAST_UPDATE_LOGIN
3622 )
3623 VALUES(
3624 ln_val_id
3625 ,p_tax_id
3626 ,1
3627 ,0 --PRECEDENCE_1
3628 ,NULL --PRECEDENCE_2
3629 ,NULL --PRECEDENCE_3
3630 ,NULL --PRECEDENCE_4
3631 ,NULL --PRECEDENCE_5
3632 ,NULL --PRECEDENCE_6
3633 ,NULL --PRECEDENCE_7
3634 ,NULL --PRECEDENCE_8
3635 ,NULL --PRECEDENCE_9
3636 ,NULL --PRECEDENCE_10
3637 ,p_object_version_number
3638 ,p_creation_date
3639 ,p_created_by
3640 ,p_last_update_date
3641 ,p_last_updated_by
3642 ,p_last_update_login
3643 );
3644
3645 END AUTO_CREATE_CATEGORY;
3646 --==========================================================================
3647 -- PROCEDURE NAME:
3648 --
3649 -- auto_create_dtc_category Public
3650 --
3651 -- DESCRIPTION:
3652 --
3653 -- Create a new TDS tax category from legacy TDS tax code
3654 --
3655 -- PARAMETERS:
3656 -- In: p_tax_category_name pass the new TDS tax category name
3657 -- p_tax_category_desc pass the new TDS tax category description
3658 -- p_item_class_cd pass the new TDS tax category item class cd
3659 -- p_org_id pass org_id
3660 -- p_object_version_number pass the new TDS tax category object version number
3661 -- p_tax_id pass the legacy TDS tax code
3662 --
3663 -- DESIGN REFERENCES:
3664 -- For new TDS architecture migration script
3665 --
3666 -- CHANGE HISTORY:
3667 -- 24-OCT-2012 Chong created
3668 --==========================================================================
3669 PROCEDURE auto_create_dtc_category(p_tax_category_name IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_NAME%TYPE
3670 ,p_tax_category_desc IN JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_DESC%TYPE
3671 ,p_item_class_cd IN JAI_CMN_TAX_CTGS_ALL.ITEM_CLASS_CD%TYPE
3672 ,p_org_id IN JAI_CMN_TAX_CTGS_ALL.ORG_ID%TYPE
3673 ,p_object_version_number IN JAI_CMN_TAX_CTGS_ALL.OBJECT_VERSION_NUMBER%TYPE
3674 ,p_tax_id IN JAI_CMN_TAX_CTG_LINES.TAX_ID%TYPE
3675 )
3676 IS
3677
3678 CURSOR tax_cat is
3679 SELECT 'X' X
3680 FROM JAI_CMN_TAX_CTGS_ALL
3681 WHERE tax_category_name = p_tax_category_name
3682 AND org_id = to_number(p_org_id);
3683
3684 CURSOR get_tax_code_info is
3685 SELECT jcta.tax_id
3686 ,jcta.tax_rate
3687 ,jcta.cess_rate
3688 ,jcta.sh_cess_rate
3689 ,jcta.surcharge_rate
3690 ,jcta.tax_name
3691 ,jcta.tax_descr
3692 ,jcta.tax_type
3693 ,jcta.vendor_id
3694 ,jcta.vendor_site_id
3695 ,jcta.modifiable_flag
3696 ,jcta.tax_account_id
3697 ,jcta.orig_tax_percentage
3698 ,jcta.org_id
3699 ,jcta.duty_drawback_flag
3700 ,jcta.vat_flag
3701 ,jcta.section_type
3702 ,jcta.start_date
3703 ,jcta.end_date
3704 FROM JAI_CMN_TAXES_ALL jcta
3705 WHERE jcta.tax_id = p_tax_id
3706 AND jcta.org_id = to_number(p_org_id);
3707
3708 tax_code_info_rec get_tax_code_info%ROWTYPE;
3709 jai_cmn_taxes_all_rec jai_cmn_taxes_all%ROWTYPE;
3710 jai_cmn_tax_ctg_lines_rec jai_cmn_tax_ctg_lines%ROWTYPE;
3711 lv_name VARCHAR2(100);
3712 ln_val_id NUMBER;
3713 ln_base_tax_id NUMBER;
3714 ln_base_tax_rate NUMBER;
3715 ln_cess_id NUMBER;
3716 ln_cess_rate NUMBER;
3717 ln_sh_cess_id NUMBER;
3718 ln_sh_cess_rate NUMBER;
3719 ln_surcharge_id NUMBER;
3720 ln_surcharge_rate NUMBER;
3721 ln_prcd_cnt NUMBER := 0;
3722 BEGIN
3723
3724 --check tax category ID if already exist
3725 FOR rec IN tax_cat
3726 LOOP
3727 IF rec.x IS NOT NULL THEN
3728 FND_MESSAGE.SET_NAME('JA','JAI_TAX_CTG_EXISTS');
3729 APP_EXCEPTION.RAISE_EXCEPTION;
3730 END IF;
3731 END LOOP;
3732
3733 --get category id from sequence
3734 SELECT JAI_CMN_TAX_CTGS_ALL_S.nextval
3735 INTO ln_val_id
3736 FROM dual;
3737
3738 --create tax category header line
3739 INSERT INTO JAI_CMN_TAX_CTGS_ALL(
3740 TAX_CATEGORY_ID
3741 ,TAX_CATEGORY_NAME
3742 ,TAX_CATEGORY_DESC
3743 ,ITEM_CLASS_CD
3744 ,ORG_ID
3745 ,OBJECT_VERSION_NUMBER
3746 ,CREATION_DATE
3747 ,CREATED_BY
3748 ,LAST_UPDATE_DATE
3749 ,LAST_UPDATED_BY
3750 ,LAST_UPDATE_LOGIN
3751 )
3752 VALUES(
3753 ln_val_id
3754 ,p_tax_category_name
3755 ,p_tax_category_desc
3756 ,p_item_class_cd
3757 ,p_org_id
3758 ,p_object_version_number
3759 ,sysdate
3760 ,fnd_global.user_id
3761 ,sysdate
3762 ,fnd_global.user_id
3763 ,fnd_global.login_id
3764 );
3765
3766
3767 --get legacy TDS tax rate from tax code
3768 OPEN get_tax_code_info;
3769 FETCH get_tax_code_info INTO tax_code_info_rec;
3770 CLOSE get_tax_code_info;
3771
3772 --Calcuate all exsiting TDS tax rate, assume that procedence base rate 0, cess rate 1, sh cess rate 1, surcharge 1,2,3
3773 --Calculate base TDS tax rate
3774 ln_base_tax_rate := NVL(tax_code_info_rec.tax_rate,0) - NVL(tax_code_info_rec.cess_rate,0)
3775 - NVL(tax_code_info_rec.sh_cess_rate,0) - NVL(tax_code_info_rec.surcharge_rate,0);
3776 --Cess rate
3777 ln_cess_rate := ROUND(NVL(tax_code_info_rec.cess_rate,0) * 100 / ln_base_tax_rate, 2);
3778 --Sh Cess rate
3779 ln_sh_cess_rate := ROUND(NVL(tax_code_info_rec.sh_cess_rate,0) * 100 / ln_base_tax_rate, 2);
3780 --Surcharge Cess rate
3781 ln_surcharge_rate := ROUND(NVL(tax_code_info_rec.surcharge_rate,0) * 100
3782 / (ln_base_tax_rate + NVL(tax_code_info_rec.cess_rate,0) + NVL(tax_code_info_rec.sh_cess_rate,0)), 2);
3783
3784 --@Create new tax code for base TDS tax
3785 --get tax code ID for base tax
3786 SELECT JAI_CMN_TAXES_ALL_S.nextval
3787 INTO ln_base_tax_id
3788 FROM dual;
3789
3790 ln_prcd_cnt := 1;
3791 lv_name := SUBSTR(ln_base_tax_rate || '% TDS migrated from: ' || tax_code_info_rec.tax_id || '-' || tax_code_info_rec.tax_name,1,100);
3792 jai_cmn_taxes_all_rec := NULL;
3793 jai_cmn_taxes_all_rec.tax_id := ln_base_tax_id;
3794 jai_cmn_taxes_all_rec.tax_rate := ln_base_tax_rate;
3795 jai_cmn_taxes_all_rec.tax_name := SUBSTR(lv_name,1,50);
3796 jai_cmn_taxes_all_rec.tax_descr := lv_name;
3797 jai_cmn_taxes_all_rec.tax_type := 'TDS';
3798 jai_cmn_taxes_all_rec.vendor_id := tax_code_info_rec.vendor_id;
3799 jai_cmn_taxes_all_rec.vendor_site_id := tax_code_info_rec.vendor_site_id;
3800 jai_cmn_taxes_all_rec.modifiable_flag := tax_code_info_rec.modifiable_flag;
3801 jai_cmn_taxes_all_rec.tax_account_id := tax_code_info_rec.tax_account_id;
3802 jai_cmn_taxes_all_rec.orig_tax_percentage := ln_base_tax_rate;
3803 jai_cmn_taxes_all_rec.org_id := tax_code_info_rec.org_id;
3804 jai_cmn_taxes_all_rec.duty_drawback_flag := tax_code_info_rec.duty_drawback_flag;
3805 jai_cmn_taxes_all_rec.vat_flag := tax_code_info_rec.vat_flag;
3806 jai_cmn_taxes_all_rec.section_type := tax_code_info_rec.section_type;
3807 jai_cmn_taxes_all_rec.start_date := tax_code_info_rec.start_date;
3808 jai_cmn_taxes_all_rec.end_date := tax_code_info_rec.end_date;
3809 jai_cmn_taxes_all_rec.CREATION_DATE := sysdate;
3810 jai_cmn_taxes_all_rec.CREATED_BY := fnd_global.user_id;
3811 jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
3812 jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
3813 jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
3814 --insert tax code
3815 insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
3816
3817 --@Create new tax category line for base TDS tax
3818 jai_cmn_tax_ctg_lines_rec := NULL;
3819 jai_cmn_tax_ctg_lines_rec.tax_category_id := ln_val_id;
3820 jai_cmn_tax_ctg_lines_rec.tax_id := ln_base_tax_id;
3821 jai_cmn_tax_ctg_lines_rec.line_no := 1;
3822 jai_cmn_tax_ctg_lines_rec.precedence_1 := 0;
3823 jai_cmn_tax_ctg_lines_rec.object_version_number := p_object_version_number;
3824 jai_cmn_tax_ctg_lines_rec.creation_date := sysdate;
3825 jai_cmn_tax_ctg_lines_rec.created_by := fnd_global.user_id;
3826 jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
3827 jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
3828 jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
3829 insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
3830
3831 --get tax code ID for base tax
3832 SELECT JAI_CMN_TAXES_ALL_S.nextval
3833 INTO ln_cess_id
3834 FROM dual;
3835
3836 IF NVL(ln_cess_rate,0) <> 0 THEN
3837 ln_prcd_cnt := ln_prcd_cnt + 1;
3838 --get tax code ID for TDS CESS tax
3839 SELECT JAI_CMN_TAXES_ALL_S.nextval
3840 INTO ln_cess_id
3841 FROM dual;
3842
3843 lv_name := SUBSTR(ln_cess_rate || '% TDS CESS migrated from: ' || tax_code_info_rec.tax_id || '-' || tax_code_info_rec.tax_name,1,100);
3844 jai_cmn_taxes_all_rec := NULL;
3845 jai_cmn_taxes_all_rec.tax_id := ln_cess_id;
3846 jai_cmn_taxes_all_rec.tax_rate := ln_cess_rate;
3847 jai_cmn_taxes_all_rec.tax_name := SUBSTR(lv_name,1,50);
3848 jai_cmn_taxes_all_rec.tax_descr := lv_name;
3849 jai_cmn_taxes_all_rec.tax_type := 'TDS_EDUCATION_CESS';
3850 jai_cmn_taxes_all_rec.vendor_id := tax_code_info_rec.vendor_id;
3851 jai_cmn_taxes_all_rec.vendor_site_id := tax_code_info_rec.vendor_site_id;
3852 jai_cmn_taxes_all_rec.modifiable_flag := tax_code_info_rec.modifiable_flag;
3853 jai_cmn_taxes_all_rec.tax_account_id := tax_code_info_rec.tax_account_id;
3854 jai_cmn_taxes_all_rec.orig_tax_percentage := ln_cess_rate;
3855 jai_cmn_taxes_all_rec.org_id := tax_code_info_rec.org_id;
3856 jai_cmn_taxes_all_rec.duty_drawback_flag := tax_code_info_rec.duty_drawback_flag;
3857 jai_cmn_taxes_all_rec.vat_flag := tax_code_info_rec.vat_flag;
3858 jai_cmn_taxes_all_rec.section_type := tax_code_info_rec.section_type;
3859 jai_cmn_taxes_all_rec.start_date := tax_code_info_rec.start_date;
3860 jai_cmn_taxes_all_rec.end_date := tax_code_info_rec.end_date;
3861 jai_cmn_taxes_all_rec.CREATION_DATE := sysdate;
3862 jai_cmn_taxes_all_rec.CREATED_BY := fnd_global.user_id;
3863 jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
3864 jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
3865 jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
3866 --insert tax code
3867 insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
3868
3869 --@Create new tax category TDS CESS tax
3870 jai_cmn_tax_ctg_lines_rec := NULL;
3871 jai_cmn_tax_ctg_lines_rec.tax_category_id := ln_val_id;
3872 jai_cmn_tax_ctg_lines_rec.tax_id := ln_cess_id;
3873 jai_cmn_tax_ctg_lines_rec.line_no := 2;
3874 jai_cmn_tax_ctg_lines_rec.precedence_1 := 1;
3875 jai_cmn_tax_ctg_lines_rec.object_version_number := p_object_version_number;
3876 jai_cmn_tax_ctg_lines_rec.creation_date := sysdate;
3877 jai_cmn_tax_ctg_lines_rec.created_by := fnd_global.user_id;
3878 jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
3879 jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
3880 jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
3881 insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
3882 END IF;
3883
3884 IF NVL(ln_sh_cess_rate,0) <> 0 THEN
3885 ln_prcd_cnt := ln_prcd_cnt + 1;
3886 --get tax code ID for TDS CESS tax
3887 SELECT JAI_CMN_TAXES_ALL_S.nextval
3888 INTO ln_sh_cess_id
3889 FROM dual;
3890
3891 lv_name := SUBSTR(ln_sh_cess_rate || '% TDS SH CESS migrated from: ' || tax_code_info_rec.tax_id || '-' || tax_code_info_rec.tax_name,1,100);
3892 jai_cmn_taxes_all_rec := NULL;
3893 jai_cmn_taxes_all_rec.tax_id := ln_sh_cess_id;
3894 jai_cmn_taxes_all_rec.tax_rate := ln_sh_cess_rate;
3895 jai_cmn_taxes_all_rec.tax_name := SUBSTR(lv_name,1,50);
3896 jai_cmn_taxes_all_rec.tax_descr := lv_name;
3897 jai_cmn_taxes_all_rec.tax_type := 'TDS_SH_EDU_CESS';
3898 jai_cmn_taxes_all_rec.vendor_id := tax_code_info_rec.vendor_id;
3899 jai_cmn_taxes_all_rec.vendor_site_id := tax_code_info_rec.vendor_site_id;
3900 jai_cmn_taxes_all_rec.modifiable_flag := tax_code_info_rec.modifiable_flag;
3901 jai_cmn_taxes_all_rec.tax_account_id := tax_code_info_rec.tax_account_id;
3902 jai_cmn_taxes_all_rec.orig_tax_percentage := ln_sh_cess_rate;
3903 jai_cmn_taxes_all_rec.org_id := tax_code_info_rec.org_id;
3904 jai_cmn_taxes_all_rec.duty_drawback_flag := tax_code_info_rec.duty_drawback_flag;
3905 jai_cmn_taxes_all_rec.vat_flag := tax_code_info_rec.vat_flag;
3906 jai_cmn_taxes_all_rec.section_type := tax_code_info_rec.section_type;
3907 jai_cmn_taxes_all_rec.start_date := tax_code_info_rec.start_date;
3908 jai_cmn_taxes_all_rec.end_date := tax_code_info_rec.end_date;
3909 jai_cmn_taxes_all_rec.CREATION_DATE := sysdate;
3910 jai_cmn_taxes_all_rec.CREATED_BY := fnd_global.user_id;
3911 jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
3912 jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
3913 jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
3914 --insert tax code
3915 insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
3916
3917 --@Create new tax category TDS SH CESS tax
3918 jai_cmn_tax_ctg_lines_rec := NULL;
3919 jai_cmn_tax_ctg_lines_rec.tax_category_id := ln_val_id;
3920 jai_cmn_tax_ctg_lines_rec.tax_id := ln_sh_cess_id;
3921 IF ln_prcd_cnt = 3 THEN
3922 jai_cmn_tax_ctg_lines_rec.line_no := 3;
3923 ELSE
3924 jai_cmn_tax_ctg_lines_rec.line_no := 2;
3925 END IF;
3926 jai_cmn_tax_ctg_lines_rec.precedence_1 := 1;
3927 jai_cmn_tax_ctg_lines_rec.object_version_number := p_object_version_number;
3928 jai_cmn_tax_ctg_lines_rec.creation_date := sysdate;
3929 jai_cmn_tax_ctg_lines_rec.created_by := fnd_global.user_id;
3930 jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
3931 jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
3932 jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
3933 insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
3934 END IF;
3935
3936 IF NVL(ln_surcharge_rate,0) <> 0 THEN
3937 ln_prcd_cnt := ln_prcd_cnt + 1;
3938 --get tax code ID for TDS CESS tax
3939 SELECT JAI_CMN_TAXES_ALL_S.nextval
3940 INTO ln_surcharge_id
3941 FROM dual;
3942
3943 lv_name := SUBSTR(ln_surcharge_rate || '% TDS surcharge migrated from: ' || tax_code_info_rec.tax_id || '-' || tax_code_info_rec.tax_name,1,100);
3944 jai_cmn_taxes_all_rec := NULL;
3945 jai_cmn_taxes_all_rec.tax_id := ln_surcharge_id;
3946 jai_cmn_taxes_all_rec.tax_rate := ln_surcharge_rate;
3947 jai_cmn_taxes_all_rec.tax_name := SUBSTR(lv_name,1,50);
3948 jai_cmn_taxes_all_rec.tax_descr := lv_name;
3949 jai_cmn_taxes_all_rec.tax_type := 'TDS_SURCHARGE';
3950 jai_cmn_taxes_all_rec.vendor_id := tax_code_info_rec.vendor_id;
3951 jai_cmn_taxes_all_rec.vendor_site_id := tax_code_info_rec.vendor_site_id;
3952 jai_cmn_taxes_all_rec.modifiable_flag := tax_code_info_rec.modifiable_flag;
3953 jai_cmn_taxes_all_rec.tax_account_id := tax_code_info_rec.tax_account_id;
3954 jai_cmn_taxes_all_rec.orig_tax_percentage := ln_surcharge_rate;
3955 jai_cmn_taxes_all_rec.org_id := tax_code_info_rec.org_id;
3956 jai_cmn_taxes_all_rec.duty_drawback_flag := tax_code_info_rec.duty_drawback_flag;
3957 jai_cmn_taxes_all_rec.vat_flag := tax_code_info_rec.vat_flag;
3958 jai_cmn_taxes_all_rec.section_type := tax_code_info_rec.section_type;
3959 jai_cmn_taxes_all_rec.start_date := tax_code_info_rec.start_date;
3960 jai_cmn_taxes_all_rec.end_date := tax_code_info_rec.end_date;
3961 jai_cmn_taxes_all_rec.CREATION_DATE := sysdate;
3962 jai_cmn_taxes_all_rec.CREATED_BY := fnd_global.user_id;
3963 jai_cmn_taxes_all_rec.LAST_UPDATE_DATE := sysdate;
3964 jai_cmn_taxes_all_rec.LAST_UPDATED_BY := fnd_global.user_id;
3965 jai_cmn_taxes_all_rec.LAST_UPDATE_LOGIN := fnd_global.login_id;
3966 --insert tax code
3967 insert_jai_cmn_taxes_all(jai_cmn_taxes_all_rec);
3968
3969 --@Create new tax category TDS Surcharge tax
3970 jai_cmn_tax_ctg_lines_rec := NULL;
3971 jai_cmn_tax_ctg_lines_rec.tax_category_id := ln_val_id;
3972 jai_cmn_tax_ctg_lines_rec.tax_id := ln_surcharge_id;
3973 IF ln_prcd_cnt = 4 THEN
3974 jai_cmn_tax_ctg_lines_rec.line_no := 4;
3975 jai_cmn_tax_ctg_lines_rec.precedence_1 := 1;
3976 jai_cmn_tax_ctg_lines_rec.precedence_2 := 2;
3977 jai_cmn_tax_ctg_lines_rec.precedence_3 := 3;
3978 ELSIF ln_prcd_cnt = 3 THEN
3979 jai_cmn_tax_ctg_lines_rec.line_no := 3;
3980 jai_cmn_tax_ctg_lines_rec.precedence_1 := 1;
3981 jai_cmn_tax_ctg_lines_rec.precedence_2 := 2;
3982 ELSE
3983 jai_cmn_tax_ctg_lines_rec.line_no := 2;
3984 jai_cmn_tax_ctg_lines_rec.precedence_1 := 1;
3985 END IF;
3986 jai_cmn_tax_ctg_lines_rec.object_version_number := p_object_version_number;
3987 jai_cmn_tax_ctg_lines_rec.creation_date := sysdate;
3988 jai_cmn_tax_ctg_lines_rec.created_by := fnd_global.user_id;
3989 jai_cmn_tax_ctg_lines_rec.last_update_date := sysdate;
3990 jai_cmn_tax_ctg_lines_rec.last_updated_by := fnd_global.user_id;
3991 jai_cmn_tax_ctg_lines_rec.last_update_login := fnd_global.login_id;
3992 insert_jai_cmn_tax_ctg_lines(jai_cmn_tax_ctg_lines_rec);
3993 END IF;
3994
3995 END AUTO_CREATE_DTC_CATEGORY;
3996
3997 --==========================================================================
3998 -- PROCEDURE NAME:
3999 -- insert_jai_cmn_taxes_all Public
4000 --
4001 -- DESCRIPTION:
4002 -- Insert a new line for jai_cmn_taxes_all table
4003 --
4004 -- PARAMETERS:
4005 -- In: p_jai_cmn_taxes_all_rec jai_cmn_taxes_all record
4006 --
4007 -- DESIGN REFERENCES:
4008 -- For new TDS architecture migration script
4009 --
4010 -- CHANGE HISTORY:
4011 -- 24-OCT-2012 Chong created
4012 --==========================================================================
4013 PROCEDURE insert_jai_cmn_taxes_all (
4014 p_jai_cmn_taxes_all_rec jai_cmn_taxes_all%ROWTYPE
4015 ) IS
4016
4017 BEGIN
4018
4019 --insert tax code
4020 INSERT INTO JAI_CMN_TAXES_ALL(
4021 tax_id
4022 ,tax_name
4023 ,tax_descr
4024 ,tax_type
4025 ,tax_rate
4026 ,tax_amount
4027 ,uom_code
4028 ,adhoc_flag
4029 ,vendor_id
4030 ,vendor_flag
4031 ,vendor_site_id
4032 ,modifiable_flag
4033 ,tax_account_id
4034 ,mod_cr_percentage
4035 ,stform_type
4036 ,orig_tax_percentage
4037 ,tds_section
4038 ,currency_flag
4039 ,start_date
4040 ,end_date
4041 ,creation_date
4042 ,created_by
4043 ,last_update_date
4044 ,last_updated_by
4045 ,last_update_login
4046 ,org_id
4047 ,surcharge_rate
4048 ,surcharge_flag
4049 ,rounding_factor
4050 ,duty_drawback_flag
4051 ,duty_drawback_percentage
4052 ,vat_flag
4053 ,section_type
4054 ,section_code
4055 ,cess_rate
4056 ,object_version_number
4057 ,sh_cess_rate
4058 ,inclusive_tax_flag
4059 ,attribute1
4060 ,attribute2
4061 ,attribute3
4062 ,attribute4
4063 ,attribute5
4064 ,attribute6
4065 ,attribute7
4066 ,attribute8
4067 ,attribute9
4068 ,attribute10
4069 ,attribute11
4070 ,attribute12
4071 ,attribute13
4072 ,attribute14
4073 ,attribute15
4074 ,attribute_category
4075 ,tax_type_id
4076 ,auto_create_category
4077 ,reverse_charge_flag
4078 )
4079 VALUES(
4080 p_jai_cmn_taxes_all_rec.tax_id
4081 ,p_jai_cmn_taxes_all_rec.tax_name
4082 ,p_jai_cmn_taxes_all_rec.tax_descr
4083 ,p_jai_cmn_taxes_all_rec.tax_type
4084 ,p_jai_cmn_taxes_all_rec.tax_rate
4085 ,p_jai_cmn_taxes_all_rec.tax_amount
4086 ,p_jai_cmn_taxes_all_rec.uom_code
4087 ,p_jai_cmn_taxes_all_rec.adhoc_flag
4088 ,p_jai_cmn_taxes_all_rec.vendor_id
4089 ,p_jai_cmn_taxes_all_rec.vendor_flag
4090 ,p_jai_cmn_taxes_all_rec.vendor_site_id
4091 ,p_jai_cmn_taxes_all_rec.modifiable_flag
4092 ,p_jai_cmn_taxes_all_rec.tax_account_id
4093 ,p_jai_cmn_taxes_all_rec.mod_cr_percentage
4094 ,p_jai_cmn_taxes_all_rec.stform_type
4095 ,p_jai_cmn_taxes_all_rec.orig_tax_percentage
4096 ,p_jai_cmn_taxes_all_rec.tds_section
4097 ,p_jai_cmn_taxes_all_rec.currency_flag
4098 ,p_jai_cmn_taxes_all_rec.start_date
4099 ,p_jai_cmn_taxes_all_rec.end_date
4100 ,p_jai_cmn_taxes_all_rec.creation_date
4101 ,p_jai_cmn_taxes_all_rec.created_by
4102 ,p_jai_cmn_taxes_all_rec.last_update_date
4103 ,p_jai_cmn_taxes_all_rec.last_updated_by
4104 ,p_jai_cmn_taxes_all_rec.last_update_login
4105 ,p_jai_cmn_taxes_all_rec.org_id
4106 ,p_jai_cmn_taxes_all_rec.surcharge_rate
4107 ,p_jai_cmn_taxes_all_rec.surcharge_flag
4108 ,p_jai_cmn_taxes_all_rec.rounding_factor
4109 ,p_jai_cmn_taxes_all_rec.duty_drawback_flag
4110 ,p_jai_cmn_taxes_all_rec.duty_drawback_percentage
4111 ,p_jai_cmn_taxes_all_rec.vat_flag
4112 ,p_jai_cmn_taxes_all_rec.section_type
4113 ,p_jai_cmn_taxes_all_rec.section_code
4114 ,p_jai_cmn_taxes_all_rec.cess_rate
4115 ,p_jai_cmn_taxes_all_rec.object_version_number
4116 ,p_jai_cmn_taxes_all_rec.sh_cess_rate
4117 ,p_jai_cmn_taxes_all_rec.inclusive_tax_flag
4118 ,p_jai_cmn_taxes_all_rec.attribute1
4119 ,p_jai_cmn_taxes_all_rec.attribute2
4120 ,p_jai_cmn_taxes_all_rec.attribute3
4121 ,p_jai_cmn_taxes_all_rec.attribute4
4122 ,p_jai_cmn_taxes_all_rec.attribute5
4123 ,p_jai_cmn_taxes_all_rec.attribute6
4124 ,p_jai_cmn_taxes_all_rec.attribute7
4125 ,p_jai_cmn_taxes_all_rec.attribute8
4126 ,p_jai_cmn_taxes_all_rec.attribute9
4127 ,p_jai_cmn_taxes_all_rec.attribute10
4128 ,p_jai_cmn_taxes_all_rec.attribute11
4129 ,p_jai_cmn_taxes_all_rec.attribute12
4130 ,p_jai_cmn_taxes_all_rec.attribute13
4131 ,p_jai_cmn_taxes_all_rec.attribute14
4132 ,p_jai_cmn_taxes_all_rec.attribute15
4133 ,p_jai_cmn_taxes_all_rec.attribute_category
4134 ,p_jai_cmn_taxes_all_rec.tax_type_id
4135 ,p_jai_cmn_taxes_all_rec.auto_create_category
4136 ,p_jai_cmn_taxes_all_rec.reverse_charge_flag
4137 );
4138
4139 jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_PROCEDURE, 'JAI.PLSQL.JAI_AP_UTILS_PKG.insert_jai_cmn_taxes_all',
4140 'Created a new tax code ' || p_jai_cmn_taxes_all_rec.tax_id || '-' || p_jai_cmn_taxes_all_rec.tax_name);
4141
4142 END insert_jai_cmn_taxes_all;
4143
4144 --==========================================================================
4145 -- PROCEDURE NAME:
4146 -- insert_jai_cmn_tax_ctg_lines Public
4147 --
4148 -- DESCRIPTION:
4149 -- Insert a new line for jai_cmn_tax_ctg_lines table
4150 --
4151 -- PARAMETERS:
4152 -- In: p_jai_cmn_tax_ctg_lines_rec jai_cmn_tax_ctg_lines record
4153 --
4154 -- DESIGN REFERENCES:
4155 -- For new TDS architecture migration script
4156 --
4157 -- CHANGE HISTORY:
4158 -- 24-OCT-2012 Chong created
4159 --==========================================================================
4160 PROCEDURE insert_jai_cmn_tax_ctg_lines (
4161 p_jai_cmn_tax_ctg_lines_rec jai_cmn_tax_ctg_lines%ROWTYPE
4162 ) IS
4163
4164 BEGIN
4165
4166 --insert tax tagecode
4167 INSERT INTO jai_cmn_tax_ctg_lines(
4168 tax_category_id
4169 ,tax_id
4170 ,line_no
4171 ,precedence_1
4172 ,precedence_2
4173 ,precedence_3
4174 ,precedence_4
4175 ,precedence_5
4176 ,precedence_6
4177 ,precedence_7
4178 ,precedence_8
4179 ,precedence_9
4180 ,precedence_10
4181 ,creation_date
4182 ,created_by
4183 ,last_update_date
4184 ,last_updated_by
4185 ,last_update_login
4186 ,object_version_number
4187 )
4188 VALUES(
4189 p_jai_cmn_tax_ctg_lines_rec.tax_category_id
4190 ,p_jai_cmn_tax_ctg_lines_rec.tax_id
4191 ,p_jai_cmn_tax_ctg_lines_rec.line_no
4192 ,p_jai_cmn_tax_ctg_lines_rec.precedence_1
4193 ,p_jai_cmn_tax_ctg_lines_rec.precedence_2
4194 ,p_jai_cmn_tax_ctg_lines_rec.precedence_3
4195 ,p_jai_cmn_tax_ctg_lines_rec.precedence_4
4196 ,p_jai_cmn_tax_ctg_lines_rec.precedence_5
4197 ,p_jai_cmn_tax_ctg_lines_rec.precedence_6
4198 ,p_jai_cmn_tax_ctg_lines_rec.precedence_7
4199 ,p_jai_cmn_tax_ctg_lines_rec.precedence_8
4200 ,p_jai_cmn_tax_ctg_lines_rec.precedence_9
4201 ,p_jai_cmn_tax_ctg_lines_rec.precedence_10
4202 ,p_jai_cmn_tax_ctg_lines_rec.creation_date
4203 ,p_jai_cmn_tax_ctg_lines_rec.created_by
4204 ,p_jai_cmn_tax_ctg_lines_rec.last_update_date
4205 ,p_jai_cmn_tax_ctg_lines_rec.last_updated_by
4206 ,p_jai_cmn_tax_ctg_lines_rec.last_update_login
4207 ,p_jai_cmn_tax_ctg_lines_rec.object_version_number
4208 );
4209
4210 jai_cmn_utils_pkg.WRITE_FND_LOG(FND_LOG.LEVEL_PROCEDURE, 'JAI.PLSQL.JAI_AP_UTILS_PKG.insert_jai_cmn_tax_ctg_lines',
4211 'Created a tax category line ' || p_jai_cmn_tax_ctg_lines_rec.line_no || '-' || p_jai_cmn_tax_ctg_lines_rec.tax_id);
4212
4213 END insert_jai_cmn_tax_ctg_lines;
4214 ------------------------------------------------------------------------------------------------
4215 --Added by Chong.Lei for DTC bug#13359892 on 20111205 end
4216
4217 --Added by Zhiwei Hou for DTC migration Bug#13359892 on 20120228 begin
4218 ------------------------------------------------------------------------
4219 function get_sec_code_mapping(pv_tds_section_code varchar2) return varchar2
4220 is
4221
4222 cursor get_mapping_section
4223 is
4224 select new_section_code
4225 from jai_tds_section_mapping
4226 where old_section_code = pv_tds_section_code;
4227
4228 lv_mapping_section_code varchar2(50);
4229
4230 begin
4231 lv_mapping_section_code := null;
4232 open get_mapping_section;
4233 fetch get_mapping_section into lv_mapping_section_code;
4234 close get_mapping_section;
4235
4236 lv_mapping_section_code := nvl(lv_mapping_section_code,pv_tds_section_code);
4237
4238 return lv_mapping_section_code;
4239 end get_sec_code_mapping;
4240 ------------------------------------------------------------------------
4241 --Added by Zhiwei Hou for DTC migration Bug#13359892 on 20120228 end
4242
4243 --Added by Zhiwei Hou for DTC migration bug#13359892 on 20120309 begin
4244 -------------------------------------------------------------------------------
4245
4246 /*
4247 To populate mapping between new section and old section which are get from 2 flat files from CP
4248 */
4249 procedure populate_section_mapping(
4250 pv_file_path varchar2,
4251 pv_file_old_name varchar2,
4252 pv_file_new_name varchar2
4253 )
4254 is
4255
4256 l_file_old UTL_FILE.file_type;
4257 l_file_new UTL_FILE.file_type;
4258
4259 l_line_old VARCHAR2 (32767);
4260 l_line_new VARCHAR2 (32767);
4261
4262 l_old_file_dir varchar2(250);
4263 l_new_file_dir varchar2(250);
4264 l_old_file_name varchar2(250);
4265 l_new_file_name varchar2(250);
4266
4267 ln_flag_new number;
4268 ln_flag_old number;
4269 lx_ex exception;
4270
4271 cursor chk_section_available(cv_section_code varchar2)
4272 is
4273 select 1
4274 from ja_lookups
4275 where lookup_code = cv_section_code;
4276
4277 ln_flag number;
4278 ln_sum number;
4279
4280 begin
4281
4282 l_old_file_dir := pv_file_path;--'/usr/tmp';--Path
4283 l_new_file_dir := pv_file_path;--'/usr/tmp';--Path
4284
4285 l_old_file_name := pv_file_old_name;--'old';--Old Section file name
4286 l_new_file_name := pv_file_new_name;--'new';--New Section file name
4287
4288 l_file_old := UTL_FILE.fopen (l_old_file_dir, l_old_file_name, 'R');
4289 l_file_new := UTL_FILE.fopen (l_new_file_dir, l_new_file_name, 'R');
4290
4291
4292 ln_flag := 1;
4293 ln_sum := 0;
4294
4295 delete from jai_tds_section_mapping;
4296
4297 LOOP
4298
4299 UTL_FILE.get_line (l_file_old, l_line_old);
4300 UTL_FILE.get_line (l_file_new, l_line_new);
4301
4302 ln_flag_old := 0;
4303 ln_flag_new := 0;
4304
4305 open chk_section_available(l_line_old);
4306 fetch chk_section_available into ln_flag_old;
4307 close chk_section_available;
4308
4309 if( nvl(ln_flag_old,0) = 0 ) then
4310 Fnd_File.put_line(Fnd_File.LOG,'Please check existence for Old Section code['|| l_line_old ||'].');
4311 ln_flag := 0;
4312 end if;
4313
4314
4315 open chk_section_available(l_line_new);
4316 fetch chk_section_available into ln_flag_new;
4317 close chk_section_available;
4318
4319 if( nvl(ln_flag_new,0) = 0) then
4320 Fnd_File.put_line(Fnd_File.LOG,'Please check existence for New Section code['|| l_line_new ||'].');
4321 ln_flag := 0;
4322 end if;
4323
4324 if( ln_flag = 0) then
4325 goto next_row;
4326 end if;
4327
4328 insert into
4329 jai_tds_section_mapping
4330 (
4331 mapping_id,
4332 old_section_code,
4333 new_section_code
4334 )
4335 values(
4336 JAI_TDS_SECTION_MAPPING_S.nextval,
4337 l_line_old,
4338 l_line_new
4339 );
4340
4341 ln_sum := ln_sum + 1;
4342
4343 << next_row >>
4344 null;
4345
4346 END LOOP;
4347
4348 commit;
4349
4350 EXCEPTION
4351 WHEN NO_DATA_FOUND
4352 THEN
4353 UTL_FILE.fclose (l_file_old);
4354 UTL_FILE.fclose (l_file_new);
4355
4356 if(ln_flag = 0)then
4357 Fnd_File.put_line(Fnd_File.LOG,'0 rows inserted , Please check files to ensure the Section Code is avaiable in System.');
4358 rollback;
4359 else
4360 Fnd_File.put_line(Fnd_File.LOG,ln_sum ||' rows inserted ');
4361 commit;
4362 end if;
4363 WHEN OTHERS THEN
4364 raise lx_ex;
4365 ROLLBACK;
4366
4367 end populate_section_mapping;
4368
4369 /*
4370 To update Section code to new Section Code in Natural Account Mapping Table.
4371 */
4372 procedure update_natural_sec_mapping
4373 is
4374
4375 lx_ex exception;
4376 update_count_out number;
4377 begin
4378
4379 for rec_maps in (
4380 select old_section_code,new_section_code
4381 from jai_tds_section_mapping
4382 )
4383 loop
4384
4385 update JAI_DTC_SCTN_ACCOUNT_MAPPING
4386 set dtc_section_code = rec_maps.new_section_code
4387 where dtc_section_code = rec_maps.old_section_code;
4388
4389 update_count_out := SQL%ROWCOUNT;
4390 Fnd_File.put_line(Fnd_File.output,'In table JAI_DTC_SCTN_ACCOUNT_MAPPING Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
4391 Fnd_File.put_line(Fnd_File.log,'In table JAI_DTC_SCTN_ACCOUNT_MAPPING Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
4392
4393 end loop;
4394
4395 commit;
4396
4397 EXCEPTION
4398 WHEN OTHERS THEN
4399 raise lx_ex;
4400 ROLLBACK;
4401 end update_natural_sec_mapping;
4402
4403 /*
4404 To update Addtional information default section code
4405 */
4406 procedure update_suppl_default_sec
4407 is
4408
4409 lx_ex exception;
4410 update_count_out number;
4411
4412 begin
4413
4414 for rec_maps in (
4415 select old_section_code,new_section_code
4416 from jai_tds_section_mapping
4417 )
4418 loop
4419
4420 update JAI_AP_TDS_VENDOR_HDRS
4421 set section_code = rec_maps.new_section_code
4422 where section_code = rec_maps.old_section_code;
4423
4424 update_count_out := SQL%ROWCOUNT;
4425 Fnd_File.put_line(Fnd_File.output,'In table JAI_AP_TDS_VENDOR_HDRS Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
4426 Fnd_File.put_line(Fnd_File.log,'In table JAI_AP_TDS_VENDOR_HDRS Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
4427
4428 end loop;
4429
4430 commit;
4431
4432 EXCEPTION
4433 WHEN OTHERS THEN
4434 raise lx_ex;
4435 ROLLBACK;
4436 end update_suppl_default_sec;
4437
4438
4439
4440 /*To update actual section code in table JAI_AP_TDS_INV_TAXES*/
4441 procedure update_inv_tax_sec
4442 is
4443
4444 lx_ex exception;
4445 update_count_out number;
4446
4447 begin
4448
4449 for rec_maps in (
4450 select old_section_code,new_section_code
4451 from jai_tds_section_mapping
4452 )
4453 loop
4454
4455 update JAI_AP_TDS_INV_TAXES
4456 set actual_section_code = rec_maps.new_section_code
4457 where actual_section_code = rec_maps.old_section_code;
4458
4459 update_count_out := SQL%ROWCOUNT;
4460 Fnd_File.put_line(Fnd_File.output,'In table JAI_AP_TDS_INV_TAXES Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
4461 Fnd_File.put_line(Fnd_File.log,'In table JAI_AP_TDS_INV_TAXES Updated '||update_count_out||' rows for section code '||rec_maps.old_section_code|| ' to '|| rec_maps.new_section_code);
4462
4463 end loop;
4464
4465 commit;
4466
4467 EXCEPTION
4468 WHEN OTHERS THEN
4469 raise lx_ex;
4470 ROLLBACK;
4471 end update_inv_tax_sec;
4472
4473 /*
4474 Backend procedure for CP JAINDMG 'India - DTC Migration Process'
4475 */
4476 PROCEDURE dtc_batch_migration
4477 (
4478 errbuf OUT NOCOPY VARCHAR2
4479 ,retcode OUT NOCOPY VARCHAR2
4480 ,pv_file_path IN VARCHAR2
4481 ,pv_file_old_name IN VARCHAR2
4482 ,pv_file_new_name in varchar2
4483 ) IS
4484
4485 BEGIN
4486
4487 Fnd_File.put_line(Fnd_File.LOG,'**Entering dtc_migration_batch for file_path :'||pv_file_path||'; file_old_name: '||pv_file_old_name||'; file_new_name: '||pv_file_new_name ||';');
4488
4489 --Process start.
4490 Fnd_File.put_line(Fnd_File.LOG,'----------------------------------------------------------------------------------------');
4491
4492 --Step 1, Populate Mapping between Old and new.
4493 Fnd_File.put_line(Fnd_File.LOG,'Step 1 begin: Populate Section Mapping between Old and New.');
4494 populate_section_mapping(
4495 pv_file_path => pv_file_path,
4496 pv_file_old_name => pv_file_old_name,
4497 pv_file_new_name => pv_file_new_name
4498 );
4499 Fnd_File.put_line(Fnd_File.LOG,'Step 1 end.');
4500
4501 Fnd_File.put_line(Fnd_File.LOG,'----------------------------------------------------------------------------------------');
4502 --Step 2, Update Section code for Natural Account mapping.
4503 Fnd_File.put_line(Fnd_File.LOG,'Step 2 begin: Update Section Code for Natural Account Mapping.');
4504 update_natural_sec_mapping;
4505 Fnd_File.put_line(Fnd_File.LOG,'Step 2 end.');
4506
4507 Fnd_File.put_line(Fnd_File.LOG,'----------------------------------------------------------------------------------------');
4508 --Step 3, Update Defaulted Section Code for Supplier Site.
4509 Fnd_File.put_line(Fnd_File.LOG,'Step 3 begin: Update Defaulted Section Code for Supplier Site.');
4510 update_suppl_default_sec;
4511 Fnd_File.put_line(Fnd_File.LOG,'Step 3 end.');
4512
4513 Fnd_File.put_line(Fnd_File.LOG,'----------------------------------------------------------------------------------------');
4514 --Step 4, Update Actual Section Code for Invoice Tax.
4515 Fnd_File.put_line(Fnd_File.LOG,'Step 4 begin: Update Actual Section Code for Invoice Tax.');
4516 update_inv_tax_sec;
4517 Fnd_File.put_line(Fnd_File.LOG,'Step 4 end.');
4518
4519 Fnd_File.put_line(Fnd_File.LOG,'----------------------------------------------------------------------------------------');
4520 --Process end.
4521 Fnd_File.put_line(Fnd_File.LOG,'**Closing dtc_migration_batch for file_path :'||pv_file_path||'; file_old_name: ' ||pv_file_old_name||'; file_new_name: '||pv_file_new_name|| ';');
4522
4523 END dtc_batch_migration;
4524 -------------------------------------------------------------------------------
4525 --Added by Zhiwei Hou for DTC migration bug#13359892 on 20120309 begin
4526
4527 -- Code Ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
4528
4529
4530 END jai_ap_utils_pkg ;