[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_MATCH_TAX_PROC_PKG
Source
4 --| Copyright (c) 2007 Oracle Corporation Redwood Shores, California, USA |
1 PACKAGE BODY jai_ap_match_tax_proc_pkg AS
2 --$Header: jai_ap_match_tax_proc.plb 120.0.12020000.3 2013/03/21 09:26:31 wenzhou noship $
3 --|+======================================================================+
5 --| All rights reserved. |
6 --+=======================================================================+
7 --| FILENAME |
8 --| jai_ap_match_tax_proc.plb |
9 --| |
10 --| DESCRIPTION |
11 --| This package offer funcitons to calculate tax amount and creat |
12 --| tax lines. Also it provide the tax modification and delete |
13 --| functionalities |
14 --| |
15 --| |
16 --| |
17 --| PROCEDURE LIST |
18 --| HISTORY |
22 --==========================================================================
19 --| 2012/08/31 Wenqiong Zhou Created |
20 --+======================================================================*/
21
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'jai_ap_match_tax_proc_pkg';
25 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
26 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
27 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
28 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
29 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
30 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
31 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
32 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'JAI.PLSQL.jai_ap_match_tax_proc_pkg.';
33 G_JAI_APPLICATION_ID CONSTANT NUMBER := 7000;
34 G_PRODUCT_TABLE CONSTANT VARCHAR2(30) := 'JAI_CMN_DOCUMENT_TAXES';
35 G_MATCH_PRODUCT_TABLE CONSTANT VARCHAR2(30) := 'JAI_AP_MATCH_INV_TAXES';
36
37
38 /*----------------------------------------------------------------------------*
39 | PRIVATE FUNCTIONS/PROCEDURES |
40 *----------------------------------------------------------------------------*/
41 /*
42 REM +======================================================================+
43 REM Created By : Wenqiong Zhou
44 REM Creation Date : Sep 1, 2012
45 REM Bug Number/ER Name : 14040855/JAI Triggers Elimination
46 REM SubProgram Name : get_service_account
47 REM Type : utils API
48 REM Purpose : Get ccid based on parameters
49 REM TDD Reference :
50 REM
51 REM Assumptions :
52 REM
53 REM Parameter IN/OUT Type Required Description and Purpose
54 REM ----------------------- ------ ------------ ---------- ------------------------------------
55 REM p_accrue_on_receipt_flag IN VARCHAR2 Y Indiate the accrue on receipt flag on PO.
56 REM p_rematch IN VARCHAR2 Y Match type of the invoice line
57 REM p_tax_type IN VARCHAR2 Y Taxe type which need to be .
58 REM p_organization_id IN NUMBER Y Organization id of po/receipt
59 REM p_location_id IN NUMBER Y location id of po/receipt
60 REM
61 REM
62 REM CALLED BY prepare_copy_tax
63 REM
64 REM +================================================================================================+
65 */
66 FUNCTION get_service_account
67 (
68 p_accrue_on_receipt_flag VARCHAR2
69 ,p_rematch VARCHAR2
70 ,p_tax_type VARCHAR2
71 ,p_organization_id NUMBER
72 ,p_location_id NUMBER
73 ) RETURN NUMBER IS
74
75 lb_charge_service_interim BOOLEAN;
76 lb_service_reverse_charge BOOLEAN := FALSE;
77 lb_service_reverse_liablility BOOLEAN := FALSE;
78 ln_service_interim_account NUMBER;
79 ln_service_reverse_account NUMBER;
80 ln_service_regime_id NUMBER;
81
82 cursor c_jai_regimes
83 IS
84 select regime_id
85 from JAI_RGM_DEFINITIONS
86 where regime_code = jai_constants.service_regime;
87
88 l_api_name CONSTANT VARCHAR2(30) := 'get_service_account';
89 BEGIN
90
91
92 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
93 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
94 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'parameters: p_rematch,'||p_rematch||'()+');
95 END IF;
96
97 IF p_rematch = 'PO_MATCHING' THEN
98 lb_charge_service_interim := TRUE;
99 ELSIF p_rematch = 'REVERSE_CHARGE' THEN
100 lb_charge_service_interim := FALSE;
101 lb_service_reverse_charge := TRUE;
102 ELSIF p_rematch = 'REVERSE_LIABILITY' THEN
103 lb_charge_service_interim := FALSE;
104 lb_service_reverse_liablility := TRUE;
105 ELSIF p_rematch = 'INCLUSIVE' THEN
106 IF p_accrue_on_receipt_flag = 'N' THEN
107 lb_charge_service_interim := FALSE;
108 ELSE
109 lb_charge_service_interim := TRUE;
110 END IF;
111 ELSE
112 IF p_accrue_on_receipt_flag = 'N' THEN
113 lb_charge_service_interim := TRUE;
114 ELSE
115 lb_charge_service_interim := FALSE;
116
117 END IF;
118 END IF;
119
120 OPEN c_jai_regimes;
121 FETCH c_jai_regimes INTO ln_service_regime_id;
122 CLOSE c_jai_regimes;
123
124 IF lb_charge_service_interim THEN
125 ln_service_interim_account := jai_cmn_rgm_recording_pkg.get_account(p_regime_id => ln_service_regime_id
126 ,p_organization_type => jai_constants.orgn_type_io
127 ,p_organization_id => p_organization_id
128 ,p_location_id => p_location_id
129 ,p_tax_type => p_tax_type
130 ,p_account_name => jai_constants.recovery_interim);
131 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
132 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'recovery interim acct: '||ln_service_interim_account);
133 END IF;
134
138 ,p_organization_type => jai_constants.orgn_type_io
135 RETURN ln_service_interim_account;
136 ELSIF lb_service_reverse_charge THEN
137 ln_service_reverse_account := jai_cmn_rgm_recording_pkg.get_account(p_regime_id => ln_service_regime_id
139 ,p_organization_id => p_organization_id
140 ,p_location_id => p_location_id
141 ,p_tax_type => p_tax_type
142 ,p_account_name => 'REVERSE_CHARGE');
143 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
144 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'REVERSE_CHARGE acct: '||ln_service_reverse_account);
145 END IF;
146 RETURN ln_service_reverse_account;
147 ELSIF lb_service_reverse_liablility THEN
148 ln_service_interim_account := jai_cmn_rgm_recording_pkg.get_account(p_regime_id => ln_service_regime_id
149 ,p_organization_type => jai_constants.orgn_type_io
150 ,p_organization_id => p_organization_id
151 ,p_location_id => p_location_id
152 ,p_tax_type => p_tax_type
153 ,p_account_name => jai_constants.liability_interim);
154 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
155 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'ln_service_interim_liablity: '||ln_service_interim_account);
156 END IF;
157 RETURN ln_service_interim_account;
158 ELSE
159 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
160 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'return null. ');
161 END IF;
162
163 RETURN NULL;
164 END IF;
165 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
166 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
167 END IF;
168 EXCEPTION
169 WHEN OTHERS THEN
170 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
172 END IF;
173 app_exception.raise_exception;
174 END get_service_account;
175
176 FUNCTION update_payment_schedule (p_total_tax NUMBER, p_invoice_id NUMBER, p_precision NUMBER) RETURN boolean IS
177
178 v_total_tax_in_payment number;
179 v_tax_installment number;
180 v_payment_num ap_payment_schedules_all.payment_num%type;
181 v_total_payment_amt number;
182 v_diff_tax_amount number;
183
184 cursor c_total_payment_amt is
185 select sum(gross_amount)
186 from ap_payment_schedules_all
187 where invoice_id = p_invoice_id;
188
189 l_api_name CONSTANT VARCHAR2(30) := 'update_payment_schedule';
190 BEGIN
191
192
193 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
194 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
195 END IF;
196
197
198 Fnd_File.put_line(Fnd_File.LOG, 'Start of function update_payment_schedule');
199
200 open c_total_payment_amt;
201 fetch c_total_payment_amt into v_total_payment_amt;
202 close c_total_payment_amt;
203
204 if nvl(v_total_payment_amt, -1) = -1 then
205 Fnd_File.put_line(Fnd_File.LOG, 'Cannot update payment schedule, total payment amount :'
206 || to_char(v_total_payment_amt));
207 return false;
208 Elsif v_total_payment_amt = 0 Then
209 v_total_payment_amt := 1;
210
211 end if;
212
213
214 v_total_tax_in_payment := 0;
215
216 for c_installments in
217 (
218 select gross_amount,
219 payment_num
220 from ap_payment_schedules_all
221 where invoice_id = p_invoice_id
222 order by payment_num
223 )
224 loop
225
226 v_tax_installment := 0;
227 v_payment_num := c_installments.payment_num;
228
229 v_tax_installment := p_total_tax * (c_installments.gross_amount / nvl(v_total_payment_amt,1));
230
231 v_tax_installment := round(v_tax_installment, p_precision);
232
233 update ap_payment_schedules_all
234 set gross_amount = gross_amount + v_tax_installment,
235 amount_remaining = amount_remaining + v_tax_installment,
236 inv_curr_gross_amount = inv_curr_gross_amount + v_tax_installment,
237 payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
238
239 where invoice_id = p_invoice_id
240 and payment_num = v_payment_num;
241
242
243 v_total_tax_in_payment := v_total_tax_in_payment + v_tax_installment;
244
245 end loop;
246
247
248 -- any difference in tax because of rounding has to be added to the last installment.
249 if v_total_tax_in_payment <> p_total_tax then
250
251 v_diff_tax_amount := round( p_total_tax - v_total_tax_in_payment,p_precision);
252
253 update ap_payment_schedules_all
254 set gross_amount = gross_amount + v_diff_tax_amount,
255 amount_remaining = amount_remaining + v_diff_tax_amount,
256 inv_curr_gross_amount = inv_curr_gross_amount + v_diff_tax_amount
257 where invoice_id = p_invoice_id
261
258 and payment_num = v_payment_num;
259
260 end if;
262
263 return true;
264 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
265 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
266 END IF;
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
270 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
271 END IF;
272 app_exception.raise_exception;
273
274
275 end update_payment_schedule;
276 /*-----------------------------------------------------------------------*
277 | PUBLIC FUNCTIONS/PROCEDURES |
278 *-----------------------------------------------------------------------*/
279
280 /*
281 REM +======================================================================+
282 REM Created By : Wenqiong Zhou
283 REM Creation Date : Sep 1, 2012
284 REM Bug Number/ER Name : 14040855/JAI Triggers Elimination
285 REM SubProgram Name : copy_tax_from_po_rcv
286 REM Type : processing API
287 REM Purpose : Copy taxes to AP invoice from PO/RCV.
288 REM TDD Reference :
289 REM
290 REM Assumptions :
291 REM
292 REM Parameter IN/OUT Type Required Description and Purpose
293 REM -------------------- ------ ------------ ---------- ------------------------------------
294 REM Pn_invoice_id IN NUMBER Y Indiate which invoice is processed.
295 REM pn_invoice_line_num IN NUMBER Y Indiate which invoice line is processed.
296 REM p_tax_line_tbl IN l_tax_line_tbl_type Y The taxes to be copied to invoice.
297 REM pv_rematch IN VARCHAR2 Y Match type of the invoice line
298 REM pn_shipment_hdr_id IN NUMBER Y shipment hdr id of the PO.
299 REM pn_shipment_line_id IN NUMBER Y shipment line id of the PO.
300 REM
301 REM CALLED BY the form JAINCPTX.fmb
302 REM
303 REM +================================================================================================+
304 */
305 procedure copy_tax_from_po_rcv
306 (
307 Pn_invoice_id NUMBER
308 ,pn_invoice_line_num NUMBER
309 ,p_tax_line_tbl l_tax_line_tbl_type
310 ,pv_rematch VARCHAR2
311 ,pn_shipment_hdr_id NUMBER
312 ,pn_shipment_line_id NUMBER
313 )IS
314 r_ap_inv_line_item ap_invoice_lines_all%ROWTYPE;
315 r_ap_inv_dist_item ap_invoice_distributions_all%ROWTYPE;
316 r_ap_inv_dist_tax ap_invoice_distributions_all%ROWTYPE;
317 lv_tax_type VARCHAR2(10);
318 lv_is_item_an_expense VARCHAR2(1);
319 lv_accrue_on_receipt_flag VARCHAR2(1);
320 ln_lines_to_insert NUMBER;
321 ln_rec_tax_amt NUMBER;
322 ln_nrec_tax_amt NUMBER;
323 ln_inclurec_tax_amt NUMBER;
324 lv_source ap_invoices_all.source%TYPE;
325 ln_currency_precision NUMBER;
326 l_tax_rec l_tax_line_rec_type;
327 ln_rvs_liablity_acct NUMBER;
328 ln_incls_acct_id NUMBER;
329 ln_cum_tax_amt NUMBER;
330 CURSOR c_inv_line IS
331 SELECT * FROM
332 ap_invoice_lines_all
333 WHERE invoice_id = Pn_invoice_id
334 AND LINE_NUMBER = pn_invoice_line_num;
335
336 CURSOR c_inv_dist IS
337 SELECT * FROM
338 ap_invoice_distributions_all
339 WHERE invoice_id = pn_invoice_id
340 AND invoice_line_number = pn_invoice_line_num
341 AND distribution_line_number = ( SELECT min(distribution_line_number)
342 FROM ap_invoice_distributions_all apid
343 WHERE apid.invoice_id = Pn_invoice_id
344 AND apid.invoice_line_number = pn_invoice_line_num);
345 CURSOR c_inv_source IS
346 SELECT SOURCE FROM
347 ap_invoices_all
348 WHERE invoice_id = pn_invoice_id;
349 l_api_name CONSTANT VARCHAR2(30) := 'copy_tax_from_po_rcv';
350 BEGIN
351
352 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
353 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
354 END IF;
355
356
357 OPEN c_inv_line;
358 FETCH c_inv_line INTO r_ap_inv_line_item;
359 CLOSE c_inv_line;
360
361 OPEN c_inv_dist;
362 FETCH c_inv_dist INTO r_ap_inv_dist_item;
363 CLOSE c_inv_dist;
364
365 OPEN c_inv_source;
366 FETCH c_inv_source INTO lv_source;
367 CLOSE c_inv_source;
368
369 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
370 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'parameters:Pn_invoice_id,'||Pn_invoice_id||',pn_invoice_line_num'||pn_invoice_line_num
371 ||',pv_rematch:'||pv_rematch||',lv_source:'||lv_source||'invoice_dist_id:'||r_ap_inv_dist_item.invoice_distribution_id
372 ||',pn_shipment_hdr_id:'||pn_shipment_hdr_id||',pn_shipment_line_id:'||pn_shipment_line_id
373 );
374 END IF;
375 FOR indx IN p_tax_line_tbl.FIRST .. p_tax_line_tbl.LAST
376 LOOP
377 l_tax_rec := p_tax_line_tbl(indx);
378 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
379 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'parameters in p_tax_line_tbl,tax_id:'||
380 l_tax_rec.tax_id||',tax_line_no:'||l_tax_rec.tax_line_no||',tax_type:'||
381 l_tax_rec.tax_type||',tax_name'||l_tax_rec.tax_name||',tax_amount:'||
382 l_tax_rec.tax_amount||',rounding_factor'||l_tax_rec.rounding_factor||',modvat_flag:'||
383 l_tax_rec.modvat_flag||',inc_tax_flag'||l_tax_rec.inc_tax_flag||',reverse_charge_flag:'||
387 ,pr_ap_inv_dist_item => r_ap_inv_dist_item
384 l_tax_rec.reverse_charge_flag||',mod_cr_percentage'||l_tax_rec.mod_cr_percentage);
385 END IF;
386 prepare_copy_tax(pr_ap_inv_line_item => r_ap_inv_line_item
388 ,pr_tax_line_rec => l_tax_rec
389 ,pv_rematch => pv_rematch
390 ,pn_shipment_hdr_id => pn_shipment_hdr_id
391 ,pn_shipment_line_id => pn_shipment_line_id
392 ,pn_lines_to_insert => ln_lines_to_insert
393 ,pv_accrue_on_receipt_flag => lv_accrue_on_receipt_flag
394 ,pv_is_item_an_expense => lv_is_item_an_expense
395 ,pv_tax_type => lv_tax_type
396 ,pn_rec_tax_amt => ln_rec_tax_amt
397 ,pn_nrec_tax_amt => ln_nrec_tax_amt
398 ,pn_inclurec_tax_amt => ln_inclurec_tax_amt
399 ,pn_currency_precision => ln_currency_precision
400 ,pn_rvs_charge_liability_acct=>ln_rvs_liablity_acct
401 ,pn_incls_acct_id => ln_incls_acct_id
402 ,pr_ap_inv_dist_tax => r_ap_inv_dist_tax
403 );
404 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
405 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'out parameters after prepare_copy_tax,ln_lines_to_insert:'||
406 ln_lines_to_insert||',lv_accrue_on_receipt_flag:'||lv_accrue_on_receipt_flag||',lv_is_item_an_expense:'||
407 lv_is_item_an_expense||',lv_tax_type'||lv_tax_type||',ln_rec_tax_amt:'||
408 ln_rec_tax_amt||',ln_nrec_tax_amt'||ln_nrec_tax_amt||',ln_inclurec_tax_amt:'||
409 ln_inclurec_tax_amt||',ln_currency_precision'||ln_currency_precision||',ln_rvs_liablity_acct:'||
410 ln_rvs_liablity_acct||',ln_incls_acct_id'||ln_incls_acct_id);
411 END IF;
412
413 populate_tax_to_ap
414 (
415 pr_ap_inv_line_item => r_ap_inv_line_item
416 ,pr_ap_inv_dist_item => r_ap_inv_dist_item
417 ,pr_ap_inv_dist_tax => r_ap_inv_dist_tax
418 ,p_tax_line_rec => l_tax_rec
419 ,pv_rematch => pv_rematch
420 ,pv_source => lv_source
421 ,pn_lines_to_insert => ln_lines_to_insert
422 ,pv_is_item_an_expense => lv_is_item_an_expense
423 ,pv_accrue_on_receipt_flag => lv_accrue_on_receipt_flag
424 ,pv_tax_type => lv_tax_type
425 ,pn_rec_tax_amt => ln_rec_tax_amt
426 ,pn_nrec_tax_amt => ln_nrec_tax_amt
427 ,pn_inclurec_tax_amt => ln_inclurec_tax_amt
428 ,pn_rvs_charge_liability_acct => ln_rvs_liablity_acct
429 ,pn_incls_acct_id => ln_incls_acct_id
430 ,pn_currency_precision => ln_currency_precision
431 );
432
433 END LOOP;
434
435
436 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
437 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
438 END IF;
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
442 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
443 END IF;
444 app_exception.raise_exception;
445
446 END copy_tax_from_po_rcv;
447 /*
448 REM +======================================================================+
449 REM Created By : Wenqiong Zhou
450 REM Creation Date : Sep 1, 2012
451 REM Bug Number/ER Name : 14040855/JAI Triggers Elimination
452 REM SubProgram Name : populate_tax_to_ap
453 REM Type : processing API
454 REM Purpose : Populate taxes to AP invoice based on the data prepared in prepare_copy_tax.
455 REM TDD Reference :
456 REM
457 REM Assumptions :
458 REM
459 REM Parameter IN/OUT Type Required Description and Purpose
460 REM -------------------- ------ ------------ ---------- ------------------------------------
461 REM Pn_invoice_id IN NUMBER Y Indiate which invoice is processed.
462 REM pn_invoice_line_num IN NUMBER Y Indiate which invoice line is processed.
463 REM p_tax_line_tbl IN l_tax_line_tbl_type Y The taxes to be copied to invoice.
464 REM pv_rematch IN VARCHAR2 Y Match type of the invoice line
465 REM pn_shipment_hdr_id IN NUMBER Y shipment hdr id of the PO.
466 REM pn_shipment_line_id IN NUMBER Y shipment line id of the PO.
467 REM
468 REM CALLED BY
469 REM
470 REM +================================================================================================+
471 */
472 PROCEDURE populate_tax_to_ap
473 (
474 pr_ap_inv_line_item IN ap_invoice_lines%ROWTYPE
475 ,pr_ap_inv_dist_item IN ap_invoice_distributions_all%ROWTYPE
476 ,pr_ap_inv_dist_tax IN ap_invoice_distributions_all%ROWTYPE
477 ,p_tax_line_rec IN l_tax_line_rec_type
478 ,pv_rematch IN VARCHAR2
479 ,pv_source IN VARCHAR2
480 ,pn_lines_to_insert IN NUMBER
481 ,pv_is_item_an_expense IN VARCHAR2
482 ,pv_accrue_on_receipt_flag VARCHAR2
483 ,pv_tax_type IN VARCHAR2
484 ,pn_rec_tax_amt IN NUMBER
485 ,pn_nrec_tax_amt NUMBER
486 ,pn_inclurec_tax_amt NUMBER
487 ,pn_rvs_charge_liability_acct NUMBER DEFAULT NULL
488 ,pn_incls_acct_id IN NUMBER
492 v_tax_amount NUMBER;
489 ,pn_currency_precision IN NUMBER
490 ) IS
491
493 v_tax_amt_dist NUMBER;
494 lv_tax_line_amount NUMBER;
495 lv_modvat_flag VARCHAR2(1);
496 lv_ap_line_to_inst_flag VARCHAR2(1);
497 lv_tax_line_to_inst_flag VARCHAR2(1);
498 v_assets_tracking_flag VARCHAR2(1) := 'N';--Updated by Wenqiong on Mar-21-2013 for bug16526089
499 ln_invoice_id NUMBER := pr_ap_inv_line_item.invoice_id ;
500 ln_inv_line_num NUMBER;
501 ln_user_id NUMBER := fnd_global.user_id;
502 ln_login_id NUMBER := fnd_global.login_id;
503 lv_misc ap_invoice_distributions_all.line_type_lookup_code%TYPE := 'MISCELLANEOUS';
504 lv_dist_class ap_invoice_distributions_all.distribution_class%TYPE := 'PERMANENT';
505 lr_ap_inv_dist_tax ap_invoice_distributions_all%ROWTYPE;
506 lr_ap_inv_dist_incl_tax ap_invoice_distributions_all%ROWTYPE;
507 lr_ap_inv_dist_liability ap_invoice_distributions_all%ROWTYPE;
508 lr_ap_inv_line_tax ap_invoice_lines%ROWTYPE;
509 lr_ap_inv_line_incl_tax ap_invoice_lines%ROWTYPE;
510 lr_ap_inv_line_liability ap_invoice_lines%ROWTYPE;
511 lr_jai_ap_match_taxes JAI_AP_MATCH_INV_TAXES%ROWTYPE;
512 ln_project_id ap_invoice_lines.project_id%TYPE;
513 ln_task_id ap_invoice_lines.task_id%TYPE;
514 lv_exp_type ap_invoice_lines.expenditure_type%TYPE;
515 ld_exp_item_date ap_invoice_lines.expenditure_item_date%TYPE;
516 ln_exp_organization_id ap_invoice_lines.expenditure_organization_id%TYPE;
517 lv_project_accounting_context ap_invoice_distributions_all.project_accounting_context%TYPE;
518 lv_pa_addition_flag ap_invoice_distributions_all.pa_addition_flag%TYPE;
519 v_distribution_no NUMBER;
520 lv_match_type VARCHAR2(15) := 'NOT_MATCHED';
521 ln_dist_code_combination_id NUMBER;
522 v_tax_variance_inv_cur NUMBER;
523 v_tax_variance_fun_cur NUMBER;
524 v_price_var_accnt NUMBER;
525 ln_base_amount NUMBER;
526 v_invoice_distribution_id NUMBER;
527 ln_tax_precision NUMBER := p_tax_line_rec.rounding_factor;
528 cum_tax_amt NUMBER := -1;
529 caid NUMBER;
530 apccid NUMBER;
531 ln_legal_entity_id NUMBER;
532 v_liability_inv_dist_id NUMBER;
533 ln_inclu_serv_amt NUMBER;
534 lv_functional_currency VARCHAR2(3);
535 v_update_payment_schedule BOOLEAN;
536
537 CURSOR c_ap_dist IS
538 SELECT a.accounting_date,
539 a.accrual_posted_flag,
540 a.assets_addition_flag,
541 a.assets_tracking_flag,
542 a.cash_posted_flag,
543 a.dist_code_combination_id,
544 a.last_updated_by,
545 a.last_update_date,
546 a.line_type_lookup_code,
547 a.period_name,
548 a.set_of_books_id,
549 a.amount,
550 a.base_amount,
551 a.batch_id,
552 a.created_by,
553 a.creation_date,
554 a.description,
555 a.accts_pay_code_combination_id,
556 a.exchange_rate_variance,
557 a.last_update_login,
558 a.match_status_flag,
559 a.posted_flag,
560 a.rate_var_code_combination_id,
561 a.reversal_flag,
562 a.vat_code,
563 a.exchange_date,
564 a.exchange_rate,
565 a.exchange_rate_type,
566 a.price_adjustment_flag,
567 a.program_application_id,
568 a.program_id,
569 a.program_update_date,
570 a.global_attribute1,
571 a.global_attribute2,
572 a.global_attribute3,
573 a.po_distribution_id,
574 a.project_id,
575 a.task_id,
576 a.expenditure_type,
577 a.expenditure_item_date,
578 a.expenditure_organization_id,
579 a.quantity_invoiced,
580 Nvl(a.quantity_invoiced,1)/Nvl(b.quantity_invoiced,1) qty_apportion_factor,
581 a.unit_price,
582 price_var_code_combination_id,
583 invoice_distribution_id,
584 matched_uom_lookup_code,
585 invoice_price_variance,
586 a.distribution_line_number,
587 a.org_id
588 ,project_accounting_context
589 ,pa_addition_flag
590 ,a.dist_match_type
591 FROM ap_invoice_distributions_all a,
592 ap_invoice_lines_all b
593 WHERE a.invoice_id = ln_invoice_id
594 AND a.invoice_line_number = pr_ap_inv_line_item.line_number
595 AND b.invoice_id = ln_invoice_id
596 AND b.line_number = pr_ap_inv_line_item.line_number
597 AND a.invoice_id = b.invoice_id
598 AND a.invoice_line_number = b.line_number
599 ORDER BY a.distribution_line_number;
600
601 CURSOR c_caid_curr(c_sob_id NUMBER) IS
602 SELECT chart_of_accounts_id,currency_code
603 FROM gl_sets_of_books
604 WHERE set_of_books_id = c_sob_id;
605
606 CURSOR c_invoice_info IS
607 SELECT accts_pay_code_combination_id apccid,legal_entity_id, NVL(exchange_rate, 1) exchange_rate,invoice_currency_code
608 FROM ap_invoices_all
609 WHERE invoice_id = ln_invoice_id;
610
611
612 CURSOR c_po_item_info IS
613 SELECT pod.po_header_id,
614 pod.po_line_id,
615 pod.line_location_id,
616 pod.set_of_books_id,
617 pod.org_id,
618 poh.rate,
619 poh.rate_type,
620 pod.rate_date,
621 poh.currency_code,
622 api.last_update_login,
623 apd.dist_code_combination_id,
624 api.creation_date,
625 api.created_by,
626 api.last_update_date,
627 api.last_updated_by,
628 api.invoice_date
632 po_headers_all poh
629 FROM ap_invoices_all api,
630 ap_invoice_distributions_all apd,
631 po_distributions_all pod,
633 WHERE apd.invoice_id = api.invoice_id
634 AND pod.po_header_id = poh.po_header_id
635 AND apd.po_distribution_id = pod.po_distribution_id
636 AND apd.invoice_line_number = pr_ap_inv_line_item.line_number
637 AND api.invoice_id = ln_invoice_id
638 AND apd.distribution_line_number = pr_ap_inv_dist_item.distribution_line_number;
639
640 r_ap_dist c_ap_dist%ROWTYPE;
641 r_po_item_info c_po_item_info%ROWTYPE;
642 r_invoice_info c_invoice_info%ROWTYPE;
643 l_api_name CONSTANT VARCHAR2(30) := 'populate_tax_to_ap';
644 BEGIN
645
646
647 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
648 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+'||'invoice_id:'
649 ||pr_ap_inv_line_item.invoice_id||'ln_invoice_id:'||ln_invoice_id );
650 END IF;
651 -- Cursor to select maximum line number from invoice lines for particular invoice
652 SELECT max(line_number)
653 INTO ln_inv_line_num
654 FROM ap_invoice_lines_all
655 WHERE invoice_id = ln_invoice_id;
656
657 if ln_tax_precision is null then
658 ln_tax_precision := -1;
659 end if;
660
661 OPEN c_po_item_info;
662 FETCH c_po_item_info INTO r_po_item_info;
663 CLOSE c_po_item_info;
664
665
666
667 IF pv_source <> 'SUPPLEMENT' THEN
668 OPEN c_caid_curr(r_po_item_info.set_of_books_id);
669 FETCH c_caid_curr INTO caid,lv_functional_currency;
670 CLOSE c_caid_curr;
671 END IF;
672
673 OPEN c_invoice_info;
674 FETCH c_invoice_info INTO r_invoice_info;--apccid,ln_legal_entity_id;
675 CLOSE c_invoice_info;
676
677 for line in 1..pn_lines_to_insert
678 loop
679 /*
680 -------------------------------------------------------------------------------------
681 -- v_tax_amount is for storing the tax amount and saving into the those 2 ap tables.
682 -- lv_tax_line_amount is for storing the tax amount and saving into the tax table.
683
684 -----------Exclusive Tax :
685
686 -- *In FR/NR tax case, ln_lines_to_insert =1
687 -- v_tax_amount :=v_tax_amount; ( ln_rec_tax_amt IS Null)
688 -- lv_tax_line_amount:= v_tax_amount
689 -- this line need to be inserted into 2 ap tables and 1 jai tax table
690
691 -- *In PR tax case, ln_lines_to_insert =2
692 -- line 1:
693 -- v_tax_amount :=ln_rec_tax_amt;
694 -- lv_tax_line_amount:= v_tax_amount
695 -- this line need to be inserted into 2 ap tables and 1 jai tax table
696
697 -- line2:
698 -- v_tax_amount :=ln_nrec_tax_amt;
699 -- lv_tax_line_amount:= v_tax_amount
700 -- this line need to be inserted into 2 ap tables and 1 jai tax table
701
702
703 -----------Inclusive Tax :
704
705 -- *In FR/NR tax without project case, ln_lines_to_insert =1
706 -- v_tax_amount :=v_tax_amount;
707 -- lv_tax_line_amount:= v_tax_amount
708 -- this line need to be inserted into the jai tax table
709
710
711 -- *In FR/NR tax with project case, ln_lines_to_insert is =2
712 -- line 1:
713 -- v_tax_amount := v_tax_amount;
714 -- lv_tax_line_amount := v_tax_amount;
715 -- no project information
716 -- this line need to be inserted into the jai tax table
717
718 -- line2:
719 -- v_tax_amount := -v_tax_amount;
720 -- lv_tax_line_amount := v_tax_amount;
721 -- project information is same as parent line
722 -- this line need to be inserted into 2 ap tables
723
724
725 --* In PR tax without project case, ln_lines_to_insert =2
726 -- line 1:
727 -- v_tax_amount := ln_rec_tax_amt;
728 -- lv_tax_line_amount := ln_rec_tax_amt;
729 -- no project information
730 -- this line need to be inserted the jai tax table
731
732 -- line2:
733 -- v_tax_amount := ln_nrec_tax_amt;
734 -- lv_tax_line_amount := ln_nrec_tax_amt
735 -- no project information
736 -- this line need to be inserted the jai tax table
737
738
739 --* In PR tax with project case, ln_lines_to_insert =4
740 -- line 1:
741 -- v_tax_amount := ln_rec_tax_amt;
742 -- lv_tax_line_amount := ln_rec_tax_amt;
743 -- no project information
744 -- this line need to be inserted into the jai tax table only
745
746 -- line 2:
747 -- v_tax_amount := ln_nrec_tax_amt;
748 -- lv_tax_line_amount:= ln_nrec_tax_amt;
749 -- project information is same as parent line
750 -- this line need to be inserted into the jai tax table only
751
752 -- line 3:
753 -- v_tax_amount := ln_rec_tax_amt;
754 -- lv_tax_line_amount := ln_rec_tax_amt;
755 -- no project information
756 -- this line need to be inserted into 2 ap tables only
757
758 -- line 4:
759 -- v_tax_amount := -ln_rec_tax_amt;
760 -- lv_tax_line_amount:= ln_rec_tax_amt;
761 -- project information is same as parent line
762 -- this line need to be inserted into 2 ap tables only
763
764
765
766
767 -- v_tax_amount is for storing the tax amount saving into the ap tax table.
768 */
769
770
771 IF (NVL(p_tax_line_rec.inc_tax_flag,'N') = 'N')THEN--exclusive case
772
776 lv_tax_line_amount:= v_tax_amount ;
773 IF line = 1 then
774
775 v_tax_amount := nvl(pn_rec_tax_amt,p_tax_line_rec.tax_amount);
777 lv_modvat_flag := p_tax_line_rec.modvat_flag ;
778
779 lv_ap_line_to_inst_flag := 'Y';
780 lv_tax_line_to_inst_flag := 'Y';
781 --Added by Wenqiong on 21-Feb-2013 for bug16360225 begin
782 --Copy below logic from line=2 for non recoverable taxes.
783 IF pv_tax_type ='NR' THEN
784 IF pr_ap_inv_dist_item.assets_tracking_flag = jai_constants.YES THEN
785 v_assets_tracking_flag := jai_constants.YES;
786 END IF;
787
788 lv_modvat_flag := jai_constants.NO ;
789
790 --
791 -- This is a non recoverable line hence the tax amounts should be added into the project costs by populating
792 -- projects related columns so that PROJECTS can consider this line for Project Costing
793 --
794 IF nvl(pv_accrue_on_receipt_flag,'#') <> 'Y' THEN
795 ln_project_id := pr_ap_inv_line_item.project_id;
796 ln_task_id := pr_ap_inv_line_item.task_id;
797 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
798 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
799 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
800
801 lv_project_accounting_context := pr_ap_inv_dist_item.project_accounting_context;
802 lv_pa_addition_flag := pr_ap_inv_dist_item.pa_addition_flag;
803 END if;
804
805 -- For non recoverable line charge account should be same as of the parent line
806 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id;
807 END IF;
808 --Added by Wenqiong on 21-Feb-2013 for bug16360225 End
809 ELSIF line = 2 then
810
811 v_tax_amount := pn_nrec_tax_amt;
812 lv_tax_line_amount := v_tax_amount ;
813 lv_ap_line_to_inst_flag := 'Y';
814 lv_tax_line_to_inst_flag := 'Y';
815
816
817 IF pr_ap_inv_dist_item.assets_tracking_flag = jai_constants.YES THEN
818 v_assets_tracking_flag := jai_constants.YES;
819 END IF;
820
821 lv_modvat_flag := jai_constants.NO ;
822
823 --
824 -- This is a non recoverable line hence the tax amounts should be added into the project costs by populating
825 -- projects related columns so that PROJECTS can consider this line for Project Costing
826 --
827 IF nvl(pv_accrue_on_receipt_flag,'#') <> 'Y' THEN
828 ln_project_id := pr_ap_inv_line_item.project_id;
829 ln_task_id := pr_ap_inv_line_item.task_id;
830 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
831 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
832 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
833
834 lv_project_accounting_context := pr_ap_inv_dist_item.project_accounting_context;
835 lv_pa_addition_flag := pr_ap_inv_dist_item.pa_addition_flag;
836 END if;
837
838 -- For non recoverable line charge account should be same as of the parent line
839 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id;
840
841 END IF; --line = 1
842 ELSIF (NVL(p_tax_line_rec.inc_tax_flag,'N') = 'Y') --inclusive case
843 THEN
844 IF( pv_tax_type ='PR')
845 THEN
846 IF ( line = 1 )
847 THEN
848 --recoverable part
849 v_tax_amount := pn_rec_tax_amt ;
850 lv_tax_line_amount := v_tax_amount ;
851 lv_modvat_flag := p_tax_line_rec.modvat_flag ;
852 lv_ap_line_to_inst_flag := 'N';
853 lv_tax_line_to_inst_flag := 'Y';
854
855 --non recoverable part
856 ELSIF ( line = 2 )
857 THEN
858 v_tax_amount := pn_nrec_tax_amt ;
859 lv_tax_line_amount := v_tax_amount ;
860 lv_modvat_flag := jai_constants.NO ;
861 lv_ap_line_to_inst_flag := 'N';
862 lv_tax_line_to_inst_flag := 'Y';
863
864 --recoverable part without project infor
865 ELSIF ( line = 3 )
866 THEN
867 v_tax_amount := pn_rec_tax_amt;
868 lv_tax_line_amount := NULL;
869
870 lv_ap_line_to_inst_flag := 'Y';
871 lv_tax_line_to_inst_flag := 'N';
872
873 --Project information
874 ln_project_id := NULL;
875 ln_task_id := NULL;
876 lv_exp_type := NULL;
877 ld_exp_item_date := NULL;
878 ln_exp_organization_id := NULL;
879 lv_project_accounting_context := NULL;
880 lv_pa_addition_flag := NULL;
881
882 -- For inclusive recoverable line charge account should be same as of the parent line
883 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id ;
884
885 --recoverable part in negative amount with project infor
886 ELSIF ( line = 4 )
887 THEN
891 lv_tax_line_to_inst_flag := 'N';
888 v_tax_amount := NVL(pn_rec_tax_amt, v_tax_amount)* -1;
889 lv_tax_line_amount := NULL;
890 lv_ap_line_to_inst_flag := 'Y';
892
893 --Project information
894 ln_project_id := pr_ap_inv_line_item.project_id;
895 ln_task_id := pr_ap_inv_line_item.task_id;
896 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
897 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
898 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
899 lv_project_accounting_context := pr_ap_inv_dist_item.project_accounting_context;
900 lv_pa_addition_flag := pr_ap_inv_dist_item.pa_addition_flag;
901 -- For inclusive recoverable line charge account should be same as of the parent line
902 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id ;
903 END IF; --line = 1
904 ELSIF ( pv_tax_type = 'RE' AND pr_ap_inv_line_item.project_id IS NOT NULL ) THEN
905 --recoverable tax without project infor
906 IF ( line = 1 )
907 THEN
908 v_tax_amount := p_tax_line_rec.tax_amount ;
909 lv_tax_line_amount := v_tax_amount ;
910 lv_modvat_flag := p_tax_line_rec.modvat_flag ;
911 lv_ap_line_to_inst_flag := 'Y'; --added by eric for inclusive tax
912 lv_tax_line_to_inst_flag := 'Y'; --added by eric for inclusive tax
913
914 ln_project_id := NULL;
915 ln_task_id := NULL;
916 lv_exp_type := NULL;
917 ld_exp_item_date := NULL;
918 ln_exp_organization_id := NULL;
919 lv_project_accounting_context := NULL;
920 lv_pa_addition_flag := NULL;
921 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id ;
922 --recoverable tax in negative amount with project infor
923 ELSIF ( line = 2 )
924 THEN
925 v_tax_amount := p_tax_line_rec.tax_amount * -1;
926 lv_tax_line_amount := NULL ;
927 lv_modvat_flag := p_tax_line_rec.modvat_flag ;
928 lv_ap_line_to_inst_flag := 'Y'; --added by eric for inclusive tax
929 lv_tax_line_to_inst_flag := 'N';
930
931 ln_project_id := pr_ap_inv_line_item.project_id;
932 ln_task_id := pr_ap_inv_line_item.task_id;
933 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
934 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
935 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
936 lv_project_accounting_context := pr_ap_inv_dist_item.project_accounting_context;
937 lv_pa_addition_flag := pr_ap_inv_dist_item.pa_addition_flag;
938
939 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id ;
940 END IF;
941 -- ELSIF ( lv_tax_type <> 'PR' AND p_project_id IS NULL )
942 -- THEN
943 ELSE
944 Fnd_File.put_line(Fnd_File.LOG, 'NOT Inclusive PR Tax,NOT Inclusive RE for project ');
945 --The case process the inclusive NR tax and inclusive RE tax not for project
946
947 lv_tax_line_amount := p_tax_line_rec.tax_amount ; --added by eric for inclusive tax
948 lv_modvat_flag := p_tax_line_rec.modvat_flag ;
949 lv_ap_line_to_inst_flag := 'N'; --added by eric for inclusive tax
950 lv_tax_line_to_inst_flag := 'Y'; --added by eric for inclusive tax
951
952 END IF;--( tax_type ='PR' and (ln_lines_to_insert =4 OR )
953 END IF; --(NVL(r_tax_lines_r ec.inc_tax_flag,'N') = 'N')
954 ln_dist_code_combination_id := nvl(ln_dist_code_combination_id,pr_ap_inv_dist_tax.dist_code_combination_id);
955
956
957 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
958 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'inclusive case?'||NVL(p_tax_line_rec.inc_tax_flag,'N')||'line:'
959 ||line||',v_tax_amount,'||v_tax_amount||',lv_tax_line_amount,'||lv_tax_line_amount||',lv_modvat_flag'||lv_modvat_flag
960 ||',lv_ap_line_to_inst_flag:'||lv_ap_line_to_inst_flag||',lv_tax_line_to_inst_flag:'||lv_tax_line_to_inst_flag
961 ||',ln_dist_code_combination_id:'||ln_dist_code_combination_id ||'v_assets_tracking_flag:'||v_assets_tracking_flag
962 ||',ln_tax_precision'|| ln_tax_precision||',pn_currency_precision '||pn_currency_precision||',ln_project_id:'||ln_project_id ||'ln_task_id:'||ln_task_id );
963 END IF;
964 --insert exclusive tax to the ap tables
965 --or insert recoverable inclusive tax with project information the ap tables
966
967
968 --1.Only exlusive taxes or inclusive taxes with project information
969 -- are inserted into Ap Lines and Dist Lines
970
971 --2.All taxes need to be inserted into jai tax tables. Futher the
972 -- partially recoverable tax need to be splitted into 2 lines
973
974
975 IF ( NVL(lv_ap_line_to_inst_flag,'N')='Y' OR NVL(lv_tax_line_to_inst_flag,'N')='Y')
976 THEN
977 ln_inv_line_num := ln_inv_line_num + 1;
978 IF NVL(lv_ap_line_to_inst_flag,'N')='Y'
979 THEN
980
981
982 lr_ap_inv_line_tax := NULL;
983 lr_ap_inv_line_tax.INVOICE_ID := ln_invoice_id;
987 lr_ap_inv_line_tax.ORG_ID := pr_ap_inv_line_item.org_id;
984 lr_ap_inv_line_tax.LINE_NUMBER := ln_inv_line_num;
985 lr_ap_inv_line_tax.LINE_TYPE_LOOKUP_CODE := lv_misc;
986 lr_ap_inv_line_tax.DESCRIPTION := pr_ap_inv_dist_tax.description;
988 lr_ap_inv_line_tax.MATCH_TYPE := lv_match_type;
989 lr_ap_inv_line_tax.DEFAULT_DIST_CCID := ln_dist_code_combination_id;
990 lr_ap_inv_line_tax.ACCOUNTING_DATE := pr_ap_inv_line_item.accounting_date;
991 lr_ap_inv_line_tax.PERIOD_NAME := pr_ap_inv_line_item.period_name;
992 lr_ap_inv_line_tax.DEFERRED_ACCTG_FLAG := pr_ap_inv_line_item.deferred_acctg_flag;
993 lr_ap_inv_line_tax.DEF_ACCTG_START_DATE := pr_ap_inv_line_item.def_acctg_start_date;
994 lr_ap_inv_line_tax.DEF_ACCTG_END_DATE := pr_ap_inv_line_item.def_acctg_end_date;
995 lr_ap_inv_line_tax.DEF_ACCTG_NUMBER_OF_PERIODS := pr_ap_inv_line_item.def_acctg_number_of_periods;
996 lr_ap_inv_line_tax.DEF_ACCTG_PERIOD_TYPE := pr_ap_inv_line_item.def_acctg_period_type;
997 lr_ap_inv_line_tax.SET_OF_BOOKS_ID := pr_ap_inv_line_item.set_of_books_id;
998 lr_ap_inv_line_tax.AMOUNT := ROUND(ROUND(v_tax_amount,ln_tax_precision),pn_currency_precision);
999 lr_ap_inv_line_tax.WFAPPROVAL_STATUS := pr_ap_inv_line_item.wfapproval_status;
1000 lr_ap_inv_line_tax.CREATION_DATE := sysdate;
1001 lr_ap_inv_line_tax.CREATED_BY := ln_user_id;
1002 lr_ap_inv_line_tax.LAST_UPDATED_BY := ln_user_id;
1003 lr_ap_inv_line_tax.LAST_UPDATE_DATE := sysdate;
1004 lr_ap_inv_line_tax.LAST_UPDATE_LOGIN := ln_login_id;
1005 lr_ap_inv_line_tax.project_id := ln_project_id;
1006 lr_ap_inv_line_tax.task_id := ln_task_id;
1007 lr_ap_inv_line_tax.expenditure_type := lv_exp_type;
1008 lr_ap_inv_line_tax.expenditure_item_date := ld_exp_item_date;
1009 lr_ap_inv_line_tax.expenditure_organization_id := ln_exp_organization_id;
1010 lr_ap_inv_line_tax.po_distribution_id := pr_ap_inv_line_item.po_distribution_id;
1011
1012 lr_ap_inv_line_tax.application_id := NULL;
1013 lr_ap_inv_line_tax.product_table := NULL;
1014 lr_ap_inv_line_tax.reference_key1 := NULL;
1015 lr_ap_inv_line_tax.reference_key2 := NULL;
1016
1017 IF p_tax_line_rec.inc_tax_flag = 'Y' OR p_tax_line_rec.reverse_charge_flag = 'Y' THEN
1018 lr_ap_inv_line_tax.application_id := G_JAI_APPLICATION_ID;
1019 lr_ap_inv_line_tax.product_table := G_MATCH_PRODUCT_TABLE;
1020 lr_ap_inv_line_tax.reference_key1 := p_tax_line_rec.tax_id;
1021 lr_ap_inv_line_tax.reference_key2 := pr_ap_inv_line_item.line_number;
1022 END IF;
1023 jai_ap_match_tax_proc_pkg.insert_ap_inv_line( p_ap_invoice_lines_all_rec => lr_ap_inv_line_tax);
1024
1025
1026 END IF;
1027
1028 IF pv_rematch = 'PO_MATCHING' THEN
1029
1030 /*new LOOP added to insert multiple distributions for same line, if the matched PO shipment has multiple distributions*/
1031 v_distribution_no := 1;
1032
1033 FOR r_ap_dist IN c_ap_dist
1034 LOOP
1035 v_tax_amt_dist := v_tax_amount * r_ap_dist.qty_apportion_factor;
1036
1037 IF nvl(r_ap_dist.invoice_price_variance, -1) <> -1 AND
1038 nvl(r_ap_dist.amount, -1) <> -1
1039 THEN
1040 v_price_var_accnt := r_ap_dist.price_var_code_combination_id;
1041 v_tax_variance_inv_cur := v_tax_amt_dist * (r_ap_dist.invoice_price_variance / r_ap_dist.amount);
1042 v_tax_variance_fun_cur := v_tax_variance_inv_cur * nvl(r_invoice_info.exchange_rate, 1);
1043 END IF;
1044
1045 IF p_tax_line_rec.modvat_flag = jai_constants.no OR line = 2 OR line = 4
1046 THEN
1047 v_assets_tracking_flag := r_ap_dist.assets_tracking_flag;
1048 lv_project_accounting_context := r_ap_dist.project_accounting_context;
1049 lv_pa_addition_flag := r_ap_dist.pa_addition_flag;
1050 END IF;
1051
1052 ln_dist_code_combination_id := Nvl(ln_dist_code_combination_id,r_ap_dist.dist_code_combination_id);
1053
1054
1055 ln_base_amount := null ;
1056 if p_tax_line_rec.tax_type IN ( jai_constants.tax_type_value_added, jai_constants.tax_type_cst ) then
1057 ln_base_amount := jai_ap_utils_pkg.fetch_tax_target_amt(
1058 p_invoice_id => ln_invoice_id ,
1059 p_line_location_id => pr_ap_inv_line_item.po_line_location_id ,
1060 p_transaction_id => null ,
1061 p_parent_dist_id => r_ap_dist.invoice_distribution_id ,
1062 p_tax_id => p_tax_line_rec.tax_id
1063 ) ;
1064 end if ;
1065
1066 IF NVL(lv_ap_line_to_inst_flag,'N')='Y'
1067 THEN
1068
1069
1070 IF p_tax_line_rec.modvat_flag = 'Y' THEN -- if tax is recoverable
1071 DECLARE
1072 CURSOR c_tax_account_id -- tax_account_id of tax_id and tax type Other
1073 IS
1074 SELECT tax_account_id
1075 FROM jai_cmn_taxes_all
1076 WHERE tax_id = p_tax_line_rec.tax_id
1077 AND tax_type = 'Other';
1078
1079 v_tax_account_id jai_cmn_taxes_all.tax_account_id%type := NULL;
1080 BEGIN
1081 OPEN c_tax_account_id;
1082 FETCH c_tax_account_id INTO v_tax_account_id;
1086 ln_dist_code_combination_id := v_tax_account_id;
1083 CLOSE c_tax_account_id;
1084
1085 IF v_tax_account_id IS NOT NULL THEN -- overwrite tax_account_id if tax is recoverable and of type Other
1087 END IF;
1088 END;
1089 END IF;
1090
1091 lr_ap_inv_dist_tax := pr_ap_inv_dist_tax;
1092 lr_ap_inv_dist_tax.assets_tracking_flag := nvl(v_assets_tracking_flag,pr_ap_inv_dist_tax.assets_tracking_flag);
1093 lr_ap_inv_dist_tax.assets_addition_flag := r_ap_dist.assets_addition_flag;
1094 lr_ap_inv_dist_tax.distribution_line_number := v_distribution_no;
1095 lr_ap_inv_dist_tax.dist_code_combination_id := ln_dist_code_combination_id;
1096 lr_ap_inv_dist_tax.amount := ROUND(ROUND(v_tax_amt_dist, ln_tax_precision),pn_currency_precision);
1097 lr_ap_inv_dist_tax.base_amount := ROUND(ROUND(v_tax_amt_dist * pr_ap_inv_dist_item.exchange_rate, ln_tax_precision),pn_currency_precision);
1098
1099 IF (v_assets_tracking_flag <> 'Y')
1100 THEN
1101 lr_ap_inv_dist_tax.quantity_invoiced := 0;
1102 END IF;
1103 lr_ap_inv_dist_tax.po_distribution_id := r_ap_dist.po_distribution_id ;
1104 lr_ap_inv_dist_tax.rcv_transaction_id := pr_ap_inv_dist_item.rcv_transaction_id;
1105 lr_ap_inv_dist_tax.price_var_code_combination_id := v_price_var_accnt;
1106 lr_ap_inv_dist_tax.invoice_price_variance := v_tax_variance_inv_cur;
1107 lr_ap_inv_dist_tax.base_invoice_price_variance := v_tax_variance_fun_cur;
1108 lr_ap_inv_dist_tax.matched_uom_lookup_code := r_ap_dist.matched_uom_lookup_code;
1109 lr_ap_inv_dist_tax.INVOICE_LINE_NUMBER := ln_inv_line_num;
1110
1111 IF (v_assets_tracking_flag <> 'N')
1112 THEN
1113 lr_ap_inv_dist_tax.charge_applicable_to_dist_id := pr_ap_inv_dist_item.invoice_distribution_id;
1114 END IF;
1115 lr_ap_inv_dist_tax.project_id := ln_project_id;
1116 lr_ap_inv_dist_tax.task_id := ln_task_id;
1117 lr_ap_inv_dist_tax.expenditure_type := lv_exp_type;
1118 lr_ap_inv_dist_tax.expenditure_item_date := ld_exp_item_date;
1119 lr_ap_inv_dist_tax.expenditure_organization_id := ln_exp_organization_id;
1120 lr_ap_inv_dist_tax.project_accounting_context := lv_project_accounting_context;
1121 lr_ap_inv_dist_tax.pa_addition_flag := lv_pa_addition_flag;
1122
1123 v_invoice_distribution_id := jai_ap_match_tax_proc_pkg.insert_ap_inv_dist(lr_ap_inv_dist_tax);
1124
1125
1126
1127 IF p_tax_line_rec.reverse_charge_flag = 'Y' THEN
1128 lr_ap_inv_line_liability := lr_ap_inv_line_tax;
1129 lr_ap_inv_line_tax.AMOUNT := -lr_ap_inv_line_tax.AMOUNT;
1130 lr_ap_inv_line_liability.DEFAULT_DIST_CCID := pn_rvs_charge_liability_acct;
1131
1132
1133 lr_ap_inv_dist_liability := lr_ap_inv_dist_tax;
1134 lr_ap_inv_dist_liability.amount := -lr_ap_inv_dist_tax.amount;
1135 lr_ap_inv_dist_liability.dist_code_combination_id := pn_rvs_charge_liability_acct;
1136
1137 END IF;
1138
1139 END IF;--( NVL(lv_ap_line_to_inst_flag,'N')='Y')
1140
1141
1142 IF (NVL(lv_tax_line_to_inst_flag,'N') = 'Y')
1143 THEN
1144
1145
1146 lr_jai_ap_match_taxes := NULL;
1147 lr_jai_ap_match_taxes.assets_tracking_flag := nvl(v_assets_tracking_flag,pr_ap_inv_dist_tax.assets_tracking_flag);
1148 lr_jai_ap_match_taxes.invoice_id := ln_invoice_id;
1149 lr_jai_ap_match_taxes.po_header_id := pr_ap_inv_line_item.po_header_id;
1150 lr_jai_ap_match_taxes.po_line_id := pr_ap_inv_line_item.po_line_id;
1151 lr_jai_ap_match_taxes.line_location_id := pr_ap_inv_line_item.PO_LINE_LOCATION_ID;
1152 lr_jai_ap_match_taxes.set_of_books_id := pr_ap_inv_line_item.set_of_books_id;
1153 lr_jai_ap_match_taxes.exchange_rate := r_po_item_info.rate;
1154 lr_jai_ap_match_taxes.exchange_rate_type := r_po_item_info.rate_type;
1155 lr_jai_ap_match_taxes.exchange_date := r_po_item_info.rate_date;
1156 lr_jai_ap_match_taxes.currency_code := r_po_item_info.currency_code;
1157 lr_jai_ap_match_taxes.code_combination_id := ln_dist_code_combination_id;
1158 lr_jai_ap_match_taxes.last_update_login := pr_ap_inv_line_item.last_update_login;
1159 lr_jai_ap_match_taxes.creation_date := pr_ap_inv_line_item.creation_date;
1160 lr_jai_ap_match_taxes.created_by := pr_ap_inv_line_item.created_by;
1161 lr_jai_ap_match_taxes.last_update_date := pr_ap_inv_line_item.last_update_date;
1162 lr_jai_ap_match_taxes.last_updated_by := pr_ap_inv_line_item.last_updated_by;
1163 lr_jai_ap_match_taxes.acct_pay_code_combination_id := r_invoice_info.apccid;
1164 lr_jai_ap_match_taxes.accounting_date := r_po_item_info.invoice_date;
1165 lr_jai_ap_match_taxes.tax_id := p_tax_line_rec.tax_id;
1169 lr_jai_ap_match_taxes.chart_of_accounts_id := caid;
1166 lr_jai_ap_match_taxes.tax_amount := ROUND(ROUND(lv_tax_line_amount * r_ap_dist.qty_apportion_factor, ln_tax_precision),pn_currency_precision) ;
1167 lr_jai_ap_match_taxes.base_amount := nvl(ln_base_amount,
1168 ROUND(ROUND(p_tax_line_rec.tax_amount*r_ap_dist.qty_apportion_factor, ln_tax_precision), pn_currency_precision));
1170 lr_jai_ap_match_taxes.distribution_line_number := v_distribution_no;
1171 lr_jai_ap_match_taxes.po_distribution_id := r_ap_dist.po_distribution_id;
1172 lr_jai_ap_match_taxes.parent_invoice_distribution_id := r_ap_dist.invoice_distribution_id;
1173 lr_jai_ap_match_taxes.legal_entity_id := r_invoice_info.legal_entity_id;
1174 IF NVL(p_tax_line_rec.inc_tax_flag,'N') = 'Y' THEN
1175 lr_jai_ap_match_taxes.INVOICE_LINE_NUMBER := pr_ap_inv_line_item.line_number;
1176 ELSE
1177 lr_jai_ap_match_taxes.INVOICE_LINE_NUMBER := ln_inv_line_num;
1178 END IF;
1179
1180 lr_jai_ap_match_taxes.INVOICE_DISTRIBUTION_ID := NVL(v_invoice_distribution_id,pr_ap_inv_dist_item.invoice_distribution_id) ;
1181 lr_jai_ap_match_taxes.PARENT_INVOICE_LINE_NUMBER := pr_ap_inv_line_item.line_number ;
1182 lr_jai_ap_match_taxes.RCV_TRANSACTION_ID := pr_ap_inv_line_item.RCV_TRANSACTION_ID;
1183 lr_jai_ap_match_taxes.LINE_TYPE_LOOKUP_CODE := lv_misc;
1184 lr_jai_ap_match_taxes.recoverable_flag := lv_modvat_flag;
1185 lr_jai_ap_match_taxes.line_no := p_tax_line_rec.tax_line_no ;
1186 jai_ap_match_tax_proc_pkg.insert_jai_ap_match(lr_jai_ap_match_taxes);
1187
1188 END IF;--(NVL(lv_tax_line_to_inst_flag,'N') = 'Y')
1189
1190 v_distribution_no := v_distribution_no +1;
1191
1192 END LOOP;
1193
1194 ELSE--For non-PO MATCHING, no distribution split required.
1195
1196 v_distribution_no := 1;
1197 IF nvl(lv_ap_line_to_inst_flag,'N') = 'Y' THEN
1198 lr_ap_inv_dist_tax := pr_ap_inv_dist_tax;
1199 lr_ap_inv_dist_tax.assets_tracking_flag := nvl(v_assets_tracking_flag,pr_ap_inv_dist_tax.assets_tracking_flag);
1200 lr_ap_inv_dist_tax.assets_addition_flag := pr_ap_inv_dist_item.assets_addition_flag;
1201 lr_ap_inv_dist_tax.distribution_line_number := v_distribution_no;
1202 lr_ap_inv_dist_tax.dist_code_combination_id := nvl(ln_dist_code_combination_id,pr_ap_inv_dist_tax.dist_code_combination_id);
1203 lr_ap_inv_dist_tax.amount := ROUND(ROUND(v_tax_amount,ln_tax_precision),pn_currency_precision);
1204 lr_ap_inv_dist_tax.base_amount := ROUND(ROUND(v_tax_amount * pr_ap_inv_dist_item.exchange_rate,ln_tax_precision),pn_currency_precision);
1205
1206
1207 IF (lr_ap_inv_dist_tax.assets_tracking_flag <> 'Y')
1208 THEN
1209 lr_ap_inv_dist_tax.quantity_invoiced := 0;
1210 END IF;
1211 lr_ap_inv_dist_tax.po_distribution_id := pr_ap_inv_dist_item.po_distribution_id;
1212 lr_ap_inv_dist_tax.rcv_transaction_id := pr_ap_inv_dist_item.rcv_transaction_id;
1213 lr_ap_inv_dist_tax.price_var_code_combination_id := nvl(v_price_var_accnt,pr_ap_inv_dist_tax.price_var_code_combination_id);
1214 lr_ap_inv_dist_tax.invoice_price_variance := nvl(v_tax_variance_inv_cur,pr_ap_inv_dist_tax.invoice_price_variance);
1215 lr_ap_inv_dist_tax.base_invoice_price_variance := nvl(v_tax_variance_fun_cur,pr_ap_inv_dist_tax.base_invoice_price_variance);
1216 lr_ap_inv_dist_tax.matched_uom_lookup_code := pr_ap_inv_dist_item.matched_uom_lookup_code;
1217 lr_ap_inv_dist_tax.INVOICE_LINE_NUMBER := ln_inv_line_num;
1218
1219 IF (lr_ap_inv_dist_tax.assets_tracking_flag <> 'N')
1220 THEN
1221 lr_ap_inv_dist_tax.charge_applicable_to_dist_id := pr_ap_inv_dist_item.invoice_distribution_id;
1222 END IF;
1223 lr_ap_inv_dist_tax.project_id := ln_project_id;
1224 lr_ap_inv_dist_tax.task_id := ln_task_id;
1225 lr_ap_inv_dist_tax.expenditure_type := lv_exp_type;
1226 lr_ap_inv_dist_tax.expenditure_item_date := ld_exp_item_date;
1227 lr_ap_inv_dist_tax.expenditure_organization_id := ln_exp_organization_id;
1228 lr_ap_inv_dist_tax.project_accounting_context := lv_project_accounting_context;
1229 lr_ap_inv_dist_tax.pa_addition_flag := lv_pa_addition_flag;
1230
1231 v_invoice_distribution_id := jai_ap_match_tax_proc_pkg.insert_ap_inv_dist(lr_ap_inv_dist_tax);
1232
1233
1234
1235 IF p_tax_line_rec.reverse_charge_flag = 'Y' THEN
1236 lr_ap_inv_line_liability := lr_ap_inv_line_tax;
1237 lr_ap_inv_line_tax.AMOUNT := -lr_ap_inv_line_tax.AMOUNT;
1238 lr_ap_inv_line_liability.DEFAULT_DIST_CCID := pn_rvs_charge_liability_acct;
1239
1240
1241 lr_ap_inv_dist_liability := lr_ap_inv_dist_tax;
1242 lr_ap_inv_dist_liability.amount := -lr_ap_inv_dist_tax.amount;
1243 lr_ap_inv_dist_liability.dist_code_combination_id := pn_rvs_charge_liability_acct;
1244
1245 END IF;
1246 END IF;
1247
1248
1249 IF (NVL(lv_tax_line_to_inst_flag,'N') = 'Y') THEN
1250
1251
1255 lr_jai_ap_match_taxes.po_header_id := pr_ap_inv_line_item.po_header_id;
1252 lr_jai_ap_match_taxes := NULL;
1253 lr_jai_ap_match_taxes.assets_tracking_flag := nvl(v_assets_tracking_flag,pr_ap_inv_dist_tax.assets_tracking_flag);
1254 lr_jai_ap_match_taxes.invoice_id := ln_invoice_id;
1256 lr_jai_ap_match_taxes.po_line_id := pr_ap_inv_line_item.po_line_id;
1257 lr_jai_ap_match_taxes.line_location_id := pr_ap_inv_line_item.PO_LINE_LOCATION_ID;
1258 lr_jai_ap_match_taxes.set_of_books_id := pr_ap_inv_line_item.set_of_books_id;
1259 lr_jai_ap_match_taxes.exchange_rate := r_po_item_info.rate;
1260 lr_jai_ap_match_taxes.exchange_rate_type := r_po_item_info.rate_type;
1261 lr_jai_ap_match_taxes.exchange_date := r_po_item_info.rate_date;
1262 lr_jai_ap_match_taxes.currency_code := r_po_item_info.currency_code;
1263 lr_jai_ap_match_taxes.code_combination_id := nvl(ln_dist_code_combination_id,pr_ap_inv_dist_tax.dist_code_combination_id);
1264 lr_jai_ap_match_taxes.last_update_login := pr_ap_inv_line_item.last_update_login;
1265 lr_jai_ap_match_taxes.creation_date := pr_ap_inv_line_item.creation_date;
1266 lr_jai_ap_match_taxes.created_by := pr_ap_inv_line_item.created_by;
1267 lr_jai_ap_match_taxes.last_update_date := pr_ap_inv_line_item.last_update_date;
1268 lr_jai_ap_match_taxes.last_updated_by := pr_ap_inv_line_item.last_updated_by;
1269 lr_jai_ap_match_taxes.acct_pay_code_combination_id := r_invoice_info.apccid;
1270 lr_jai_ap_match_taxes.accounting_date := r_po_item_info.invoice_date;
1271 lr_jai_ap_match_taxes.tax_id := p_tax_line_rec.tax_id;
1272 lr_jai_ap_match_taxes.tax_amount := ROUND(ROUND(lv_tax_line_amount,ln_tax_precision),pn_currency_precision);
1273 lr_jai_ap_match_taxes.base_amount := ROUND(ROUND(p_tax_line_rec.tax_amount,ln_tax_precision),pn_currency_precision);
1274 lr_jai_ap_match_taxes.chart_of_accounts_id := caid;
1275 lr_jai_ap_match_taxes.distribution_line_number := v_distribution_no;
1276 lr_jai_ap_match_taxes.po_distribution_id := pr_ap_inv_line_item.po_distribution_id;
1277 lr_jai_ap_match_taxes.parent_invoice_distribution_id := pr_ap_inv_dist_item.invoice_distribution_id;
1278 lr_jai_ap_match_taxes.legal_entity_id := r_invoice_info.legal_entity_id;
1279 IF NVL(p_tax_line_rec.inc_tax_flag,'N') = 'Y' THEN
1280 lr_jai_ap_match_taxes.INVOICE_LINE_NUMBER := pr_ap_inv_line_item.line_number;
1281 ELSE
1282 lr_jai_ap_match_taxes.INVOICE_LINE_NUMBER := ln_inv_line_num;
1283 END IF;
1284
1285 lr_jai_ap_match_taxes.INVOICE_DISTRIBUTION_ID := NVL(v_invoice_distribution_id,pr_ap_inv_dist_item.invoice_distribution_id) ;
1286 lr_jai_ap_match_taxes.PARENT_INVOICE_LINE_NUMBER := pr_ap_inv_line_item.line_number ;
1287 lr_jai_ap_match_taxes.RCV_TRANSACTION_ID := pr_ap_inv_line_item.RCV_TRANSACTION_ID;
1288 lr_jai_ap_match_taxes.LINE_TYPE_LOOKUP_CODE := lv_misc;
1289 lr_jai_ap_match_taxes.recoverable_flag := lv_modvat_flag;
1290 lr_jai_ap_match_taxes.line_no := p_tax_line_rec.tax_line_no ;
1291
1292 jai_ap_match_tax_proc_pkg.insert_jai_ap_match(lr_jai_ap_match_taxes);
1293 END IF;--(NVL(lv_tax_line_to_inst_flag,'N') = 'Y')
1294
1295 END IF;
1296
1297 END IF;
1298
1299 IF NVL(p_tax_line_rec.inc_tax_flag,'N') ='N' THEN
1300 cum_tax_amt := cum_tax_amt + ROUND(v_tax_amount, pn_currency_precision);
1301 END IF;
1302
1303
1304
1305 end loop ;--> for line in 1 to ln_lines_to_insert
1306 If nvl(pv_accrue_on_receipt_flag ,'N')='Y' then
1307 fnd_file.put_line(fnd_file.log ,'Start the Insertion of Inclusive Service tax lines for POT recoverable service amount is '||pn_inclurec_tax_amt);
1308 lv_tax_line_amount := null;
1309 lv_ap_line_to_inst_flag := null;
1310 ln_inclu_serv_amt := null;
1311 ln_dist_code_combination_id := null;
1312
1313 for inclu_Serv_line in 1..2
1314 loop
1315 ln_inclu_serv_amt := pn_inclurec_tax_amt;
1316
1317 if ln_inclu_serv_amt <>0 and NVL(p_tax_line_rec.inc_tax_flag,'N') = 'Y'
1318 and p_tax_line_rec.tax_type IN ( 'Service','SERVICE_EDUCATION_CESS','SERVICE_SH_EDU_CESS') then
1319 if inclu_Serv_line =1 THEN
1320 lv_tax_line_amount:= ln_inclu_serv_amt ;
1321 lv_ap_line_to_inst_flag := 'Y';
1322
1323 ln_dist_code_combination_id := pn_incls_acct_id;
1324 fnd_file.put_line(fnd_file.log ,'Inclusive Service tax lines for positve recovery line POT interim recovery account is '||ln_dist_code_combination_id);
1325
1326 elsif inclu_Serv_line =2 then
1327 lv_tax_line_amount := ln_inclu_serv_amt *-1 ;
1328 lv_ap_line_to_inst_flag := 'Y';
1329 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id;
1330 fnd_file.put_line(fnd_file.log ,'Inclusive Service tax POT lines for negative recovery line ap accrual account is '||ln_dist_code_combination_id);
1331 end if;
1332 end if;-- pot ln_inclu_serv_amt <>0
1333 IF ( NVL(lv_ap_line_to_inst_flag,'N')='Y')
1334 THEN
1335
1339 --For inclusive tax recoverable part, the positive invoice line should use invoice_line_number
1336 IF inclu_Serv_line =2 THEN
1337 ln_inv_line_num := ln_inv_line_num + 1;
1338 END IF;
1340 --when insert jai_ap_match_inv_taxes used; for the negative line, will use ln_inv_line_num + 1 as
1341 --line number.
1342
1343
1344 Fnd_File.put_line(Fnd_File.LOG,'Before inserting Inclusive Service tax lines for POT into ap_invoice_lines_all for line no :'
1345 || ln_inv_line_num ||' tax amount = '||lv_tax_line_amount);
1346 lr_ap_inv_line_incl_tax := lr_ap_inv_line_tax;
1347
1348 lr_ap_inv_line_incl_tax.invoice_id := ln_invoice_id;
1349 lr_ap_inv_line_incl_tax.line_number := ln_inv_line_num;
1350 lr_ap_inv_line_incl_tax.DEFAULT_DIST_CCID := ln_dist_code_combination_id;
1351 lr_ap_inv_line_incl_tax.AMOUNT := ROUND(ROUND(lv_tax_line_amount,ln_tax_precision),pn_currency_precision);
1352 lr_ap_inv_line_incl_tax.project_id := ln_project_id;
1353 lr_ap_inv_line_incl_tax.task_id := ln_task_id;
1354 lr_ap_inv_line_incl_tax.expenditure_type := lv_exp_type;
1355 lr_ap_inv_line_incl_tax.expenditure_item_date := ld_exp_item_date;
1356 lr_ap_inv_line_incl_tax.expenditure_organization_id := ln_exp_organization_id;
1357 lr_ap_inv_line_incl_tax.LINE_TYPE_LOOKUP_CODE := lv_misc;
1358 lr_ap_inv_line_incl_tax.DESCRIPTION := pr_ap_inv_dist_tax.description;
1359 lr_ap_inv_line_incl_tax.ORG_ID := pr_ap_inv_line_item.org_id;
1360 lr_ap_inv_line_incl_tax.MATCH_TYPE := lv_match_type;
1361
1362 lr_ap_inv_line_incl_tax.ACCOUNTING_DATE := pr_ap_inv_line_item.accounting_date;
1363 lr_ap_inv_line_incl_tax.PERIOD_NAME := pr_ap_inv_line_item.period_name;
1364 lr_ap_inv_line_incl_tax.DEFERRED_ACCTG_FLAG := pr_ap_inv_line_item.deferred_acctg_flag;
1365 lr_ap_inv_line_incl_tax.DEF_ACCTG_START_DATE := pr_ap_inv_line_item.def_acctg_start_date;
1366 lr_ap_inv_line_incl_tax.DEF_ACCTG_END_DATE := pr_ap_inv_line_item.def_acctg_end_date;
1367 lr_ap_inv_line_incl_tax.DEF_ACCTG_NUMBER_OF_PERIODS := pr_ap_inv_line_item.def_acctg_number_of_periods;
1368 lr_ap_inv_line_incl_tax.DEF_ACCTG_PERIOD_TYPE := pr_ap_inv_line_item.def_acctg_period_type;
1369 lr_ap_inv_line_incl_tax.SET_OF_BOOKS_ID := pr_ap_inv_line_item.set_of_books_id;
1370
1371 lr_ap_inv_line_incl_tax.WFAPPROVAL_STATUS := pr_ap_inv_line_item.wfapproval_status;
1372 lr_ap_inv_line_incl_tax.CREATION_DATE := sysdate;
1373 lr_ap_inv_line_incl_tax.CREATED_BY := ln_user_id;
1374 lr_ap_inv_line_incl_tax.LAST_UPDATED_BY := ln_user_id;
1375 lr_ap_inv_line_incl_tax.LAST_UPDATE_DATE := sysdate;
1376 lr_ap_inv_line_incl_tax.LAST_UPDATE_LOGIN := ln_login_id;
1377 lr_ap_inv_line_incl_tax.po_distribution_id := pr_ap_inv_line_item.po_distribution_id;
1378
1379
1380 IF p_tax_line_rec.inc_tax_flag = 'Y' THEN
1381 lr_ap_inv_line_incl_tax.application_id := G_JAI_APPLICATION_ID;
1382 lr_ap_inv_line_incl_tax.product_table := G_MATCH_PRODUCT_TABLE;
1383 lr_ap_inv_line_incl_tax.reference_key1 := p_tax_line_rec.tax_id;
1384 lr_ap_inv_line_incl_tax.reference_key2 := pr_ap_inv_line_item.line_number;
1385 END IF;
1386
1387 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1388 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Before inserting
1389 Inclusive Service tax lines for POT into ap_line ');
1390 END IF;
1391
1395 lr_ap_inv_dist_incl_tax.assets_tracking_flag := nvl(v_assets_tracking_flag,pr_ap_inv_dist_tax.assets_tracking_flag);
1392 jai_ap_match_tax_proc_pkg.insert_ap_inv_line( p_ap_invoice_lines_all_rec => lr_ap_inv_line_incl_tax);
1393
1394 lr_ap_inv_dist_incl_tax := pr_ap_inv_dist_tax;
1396 lr_ap_inv_dist_incl_tax.invoice_id := ln_invoice_id;
1397 lr_ap_inv_dist_incl_tax.amount := ROUND(ROUND(lv_tax_line_amount,ln_tax_precision),pn_currency_precision);
1398 lr_ap_inv_dist_incl_tax.base_amount := ROUND(ROUND(lv_tax_line_amount * pr_ap_inv_dist_item.exchange_rate,ln_tax_precision), pn_currency_precision);
1402 lr_ap_inv_dist_incl_tax.dist_code_combination_id := ln_dist_code_combination_id;
1399 lr_ap_inv_dist_incl_tax.quantity_invoiced := 0;
1400 lr_ap_inv_dist_incl_tax.INVOICE_LINE_NUMBER := ln_inv_line_num;
1401 lr_ap_inv_dist_incl_tax.distribution_line_number := 1;
1403 lr_ap_inv_dist_incl_tax.project_id := ln_project_id;
1404 lr_ap_inv_dist_incl_tax.task_id := ln_task_id;
1405 lr_ap_inv_dist_incl_tax.expenditure_type := lv_exp_type;
1406 lr_ap_inv_dist_incl_tax.expenditure_item_date := ld_exp_item_date;
1407 lr_ap_inv_dist_incl_tax.expenditure_organization_id := ln_exp_organization_id;
1408 lr_ap_inv_dist_incl_tax.project_accounting_context := lv_project_accounting_context;
1409 lr_ap_inv_dist_incl_tax.pa_addition_flag := lv_pa_addition_flag;
1410 IF (lr_ap_inv_dist_incl_tax.assets_tracking_flag <> 'N')
1411 THEN
1412 lr_ap_inv_dist_incl_tax.charge_applicable_to_dist_id := pr_ap_inv_dist_item.invoice_distribution_id;
1413 END IF;
1414
1415 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1416 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'Before inserting
1417 Inclusive Service tax lines for POT into ap_dist ');
1418 END IF;
1419 v_invoice_distribution_id := jai_ap_match_tax_proc_pkg.insert_ap_inv_dist( p_ap_invoice_dist_all_rec => lr_ap_inv_dist_incl_tax);
1420
1421 UPDATE JAI_AP_MATCH_INV_TAXES jat
1422 SET jat.invoice_distribution_id = v_invoice_distribution_id
1423 , jat.INVOICE_LINE_NUMBER = ln_inv_line_num
1424 WHERE jat.invoice_id = ln_invoice_id
1425 AND jat.po_header_id = pr_ap_inv_line_item.po_header_id
1426 AND jat.po_line_id = pr_ap_inv_line_item.po_line_id
1427 AND jat.line_location_id = pr_ap_inv_line_item.po_line_location_id
1428 AND jat.set_of_books_id = pr_ap_inv_line_item.set_of_books_id
1429 AND jat.line_no = p_tax_line_rec.tax_line_no
1430 AND jat.tax_id = p_tax_line_rec.tax_id
1431 AND jat.recoverable_flag = decode(inclu_Serv_line,1,'Y',2,'N','N');
1432
1433
1434 END IF; --POT ( NVL(lv_ap_line_to_inst_flag,'N')='Y')
1435
1436 end loop; --end positive negative service line loop
1437
1438 end if; --end accrue one receipt flag condition
1439
1440 IF p_tax_line_rec.reverse_charge_flag = 'Y' THEN
1441 ln_inv_line_num := ln_inv_line_num + 1;
1442 lr_ap_inv_line_liability.amount := -abs(lr_ap_inv_line_liability.amount);
1443 lr_ap_inv_line_liability.LINE_NUMBER := ln_inv_line_num;
1444 jai_ap_match_tax_proc_pkg.insert_ap_inv_line( p_ap_invoice_lines_all_rec => lr_ap_inv_line_liability);
1445
1446 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1447 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'insert_ap_inv_line liability line for reverse charge with amt:'||lr_ap_inv_line_liability.amount);
1448 END IF;
1449 lr_ap_inv_dist_liability.INVOICE_LINE_NUMBER := ln_inv_line_num;
1450 v_liability_inv_dist_id := jai_ap_match_tax_proc_pkg.insert_ap_inv_dist(lr_ap_inv_dist_liability);
1451 END IF;
1452
1453
1454 if (pv_source = 'ISP') then
1455
1456 /*
1457 cum_tax_amt is initialized with -1 for Bug 5593895. Bug updates are not clear as to why it was done
1458 This causes Tax Amount to be less by 1 when PO Matched Invoice is created from iSupplier
1459 Hence incrementing Tax Amount by 1 for this specific scenario as modifying initialization to 0 may cause regression
1460 */
1461 cum_tax_amt := cum_tax_amt + 1;
1462
1463 v_update_payment_schedule:=update_payment_schedule(cum_tax_amt,ln_invoice_id,pn_currency_precision);
1464
1465 UPDATE ap_invoices_all
1466 SET invoice_amount = invoice_amount + cum_tax_amt,
1467 approved_amount = approved_amount + cum_tax_amt,
1468 pay_curr_invoice_amount = pay_curr_invoice_amount + cum_tax_amt,
1469 amount_applicable_to_discount = amount_applicable_to_discount + cum_tax_amt,
1470 payment_status_flag = decode(payment_status_flag, 'Y', 'P', payment_status_flag)
1471 WHERE invoice_id = ln_invoice_id;
1472
1473 if r_invoice_info.invoice_currency_code <> lv_functional_currency then
1474 -- invoice currency is not the functional currency.
1475 update ap_invoices_all
1476 set base_amount = invoice_amount * exchange_rate
1477 where invoice_id = ln_invoice_id;
1478 end if;
1479
1480 end if;
1481 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
1483 END IF;
1484 EXCEPTION
1485 WHEN OTHERS THEN
1486 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1487 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1488 END IF;
1489 app_exception.raise_exception;
1490
1491 END populate_tax_to_ap;
1492 /*
1493 REM +===============================================================================================================================+
1494 REM Created By : Wenqiong Zhou
1495 REM Creation Date : Sep 1, 2012
1496 REM Bug Number/ER Name : 14040855/JAI Triggers Elimination
1500 REM TDD Reference :
1497 REM SubProgram Name : prepare_copy_tax
1498 REM Type : processing API
1499 REM Purpose : Prepare data before copying taxes.
1501 REM
1502 REM Assumptions :
1503 REM
1504 REM Parameter IN/OUT Type Required Description and Purpose
1505 REM ------------------------------ ------ ---------------------------------------- ---------- ------------------------------------
1506 REM pr_ap_inv_line_item IN ap_invoice_lines%ROWTYPE Y Indiate which invoice is processed.
1507 REM pr_ap_inv_dist_item IN ap_invoice_distributions_all%ROWTYPE Y Indiate which invoice line is processed.
1508 REM pr_tax_line_rec IN/OUT l_tax_line_tbl_type Y The taxes to be copied to invoice.
1509 REM pv_rematch IN VARCHAR2 Y Match type of the invoice line
1510 REM pn_shipment_hdr_id IN NUMBER Y shipment hdr id of the PO.
1511 REM pn_shipment_line_id IN NUMBER Y shipment line id of the PO.
1512 REM pn_lines_to_insert OUT NUMBER Y Numbers of lines will be inserted in AP lines.
1513 REM pv_accrue_on_receipt_flag OUT VARCHAR2 Y Accrue on receipt flag.
1514 REM pv_is_item_an_expense OUT VARCHAR2 Y Expense item flag.
1515 REM pv_tax_type OUT VARCHAR2 Y 'RE'--Recoverable tax part.
1516 REM 'NR'--Non recoverable tax part.
1517 REM 'PR'--Partially recoverable tax part.
1518 REM pn_rec_tax_amt OUT NUMBER Y recoverale tax amount
1519 REM pn_nrec_tax_amt OUT NUMBER Y non-recoverable tax amount
1520 REM pn_inclurec_tax_amt OUT NUMBER Y inclusive recoverable tax amount
1521 REM pn_currency_precision OUT NUMBER Y currency precision.
1522 REM pn_rvs_charge_liability_acct OUT NUMBER Y reverse charge liablity account id.
1523 REM pn_incls_acct_id OUT NUMBER Y inclusive tax account id.
1524 REM pr_ap_inv_dist_tax OUT ap_invoice_distributions_all%ROWTYPE Y The AP dist line with prepared data.
1525 REM
1526 REM CALLED BY copy_tax_from_po_rcv
1527 REM -------------------------------------------------------------------------------------------------------------------------------------
1528 REM | | = 1 | Exclusive tax or inlcusive tax without project info
1529 REM |pn_lines_to_insert | = 2 | Inclusive recoverable tax with project info
1530 REM | | = 2 * pn_lines_to_insert | Partially recoverable tax will be split two part
1531 REM -------------------------------------------------------------------------------------------------------------------------------------
1532 REM |pv_accrue_on_receipt_flag | jai_rcv_trx_processing_pkg.get_accrue_on_receipt
1533 REM -------------------------------------------------------------------------------------------------------------------------------------
1534 REM |pv_is_item_an_expense | jai_general_pkg.is_item_an_expense
1535 REM |pv_tax_type | = 'RE' |Full Recoverable
1536 REM | | = 'NR' |Non Recoverable
1537 REM | | = 'PR' |Partially Recoverable
1538 REM |pn_rec_tax_amt | = recoverable tax amount
1539 REM |pn_nrec_tax_amt | = non recoverable tax amount
1540 REM |pn_inclurec_tax_amt | = Inclusive recoverable tax amount
1541 REM |pn_currency_precision | = Precision setup for this currency
1542 REM |pn_rvs_charge_liability_acct | = Service tax liability account if it's reverse charge tax.
1543 REM |pn_incls_acct_id | = Recovery account for inclusive Service Tax .
1544 REM |pn_currency_precision | = Precision setup for this currency
1545 REM +===========================================================================================================================================+
1546 */
1547 PROCEDURE prepare_copy_tax(pr_ap_inv_line_item IN ap_invoice_lines%ROWTYPE
1548 ,pr_ap_inv_dist_item IN ap_invoice_distributions_all%ROWTYPE
1549 ,pr_tax_line_rec IN OUT NOCOPY l_tax_line_rec_type
1550 ,pv_rematch IN VARCHAR2
1551 ,pn_shipment_hdr_id IN NUMBER
1552 ,pn_shipment_line_id IN NUMBER
1553 ,pn_lines_to_insert OUT NOCOPY NUMBER
1554 ,pv_accrue_on_receipt_flag OUT NOCOPY VARCHAR2
1555 ,pv_is_item_an_expense OUT NOCOPY VARCHAR2
1556 ,pv_tax_type OUT NOCOPY VARCHAR2
1557 ,pn_rec_tax_amt OUT NOCOPY NUMBER
1558 ,pn_nrec_tax_amt OUT NOCOPY NUMBER
1559 ,pn_inclurec_tax_amt OUT NOCOPY NUMBER
1560 ,pn_currency_precision OUT NOCOPY NUMBER
1561 ,pn_rvs_charge_liability_acct OUT NOCOPY NUMBER
1562 ,pn_incls_acct_id OUT NOCOPY NUMBER
1566
1563 ,pr_ap_inv_dist_tax OUT NOCOPY ap_invoice_distributions_all%ROWTYPE
1564 ) IS
1565
1567
1568 CURSOR c_get_item_id IS
1569 SELECT item_id
1570 FROM po_lines_all pla,po_distributions_all pda
1571 WHERE pla.po_line_id = pda.po_line_id
1572 AND pda.po_distribution_id = pr_ap_inv_line_item.po_distribution_id;
1573
1574 CURSOR get_account_type IS
1575 SELECT account_type
1576 FROM gl_code_combinations
1577 WHERE code_combination_id = pr_ap_inv_dist_item.dist_code_combination_id;
1578
1579 CURSOR Fetch_Shipment_Line_Id_Cur( invorg IN NUMBER, receiptnum IN VARCHAR2 ) IS
1580 SELECT Shipment_Line_Id, Shipment_Header_Id
1581 FROM Rcv_Shipment_Lines
1582 WHERE Shipment_Header_Id IN ( SELECT Shipment_Header_Id
1583 FROM Rcv_Shipment_Headers
1584 WHERE Receipt_Num = receiptnum )
1585 AND Po_Line_location_Id = ( SELECT Line_Location_Id
1586 FROM Po_Distributions_All
1587 WHERE Po_Distribution_Id = pr_ap_inv_line_item.po_line_location_id)
1588 AND To_Organization_Id = invorg;
1589
1590 cursor c_regime_tax_type(cp_regime_code varchar2, cp_tax_type varchar2) is
1591 select jrr.regime_id,attribute_code tax_type
1592 from JAI_RGM_REGISTRATIONS jrr,JAI_RGM_DEFINITIONS jrd
1593 where jrr.regime_id = jrd.regime_id
1594 and jrr.registration_type = jai_constants.regn_type_tax_types /* TAX_TYPES */
1595 AND jrd.regime_code = cp_regime_code
1596 and jrr.attribute_code = cp_tax_type;
1597
1598 CURSOR c_tax(t_id NUMBER) IS
1599 SELECT tax_name,
1600 tax_account_id,
1601 mod_cr_percentage,
1602 adhoc_flag,
1603 nvl(tax_rate,-1) tax_rate,
1604 tax_type,
1605 NVL(rounding_factor,0) rounding_factor
1606 FROM JAI_CMN_TAXES_ALL
1607 WHERE tax_id = t_id;
1608
1609 CURSOR for_org_id IS
1610 SELECT org_id, vendor_id, NVL(exchange_rate, 1) exchange_rate, invoice_currency_code,batch_id
1611 FROM ap_invoices_all
1612 WHERE invoice_id = pr_ap_inv_line_item.invoice_id;
1613
1614 cursor c_get_excise_costing_flag (cp_rcv_transaction_id rcv_transactions.transaction_id%type,
1615 cp_organization_id JAI_RCV_TRANSACTIONS.organization_id%type,
1616 cp_shipment_header_id JAI_RCV_TRANSACTIONS.shipment_header_id%type,
1617 cp_txn_type JAI_RCV_TRANSACTIONS.transaction_type%type,
1618 cp_attribute1 VARCHAR2)
1619 is
1620 select attribute2 excise_costing_flag
1621 from jai_rcv_transactions jrcvt
1622 where jrcvt.parent_transaction_id = cp_rcv_transaction_id
1623 and jrcvt.organization_id = cp_organization_id
1624 and jrcvt.shipment_header_id = cp_shipment_header_id
1625 and jrcvt.transaction_type = cp_txn_type ;
1626 cursor c_fnd_curr_precision(cp_currency_code fnd_currencies.currency_code%type) is
1627 select precision
1628 from fnd_currencies
1629 where currency_code = cp_currency_code;
1630
1631
1632 ln_po_dist_id NUMBER;
1633 ln_location_id NUMBER;
1634 ln_organization_id NUMBER;
1635 ln_item_id NUMBER;
1636 lv_excise_costing_flag VARCHAR2(1);
1637 r_vat_regime_tax_type c_regime_tax_type%ROWTYPE;
1638 r_service_regime_tax_type c_regime_tax_type%ROWTYPE;
1639 ln_tax_precision NUMBER;
1640 v_assets_tracking_flag VARCHAR2(1) := 'N';--Updated by Wenqiong on Mar-21-2013 for bug16526089
1641 ln_dist_code_combination_id NUMBER;
1642 c_tax_rec c_tax%ROWTYPE;
1643 v_tax_variance_inv_cur NUMBER;
1644 v_tax_variance_fun_cur NUMBER;
1645 v_price_var_accnt NUMBER;
1646 for_org_id_rec for_org_id%ROWTYPE;
1647 lv_account_type gl_code_combinations.account_type%TYPE;
1648 ln_project_id ap_invoice_lines.project_id%TYPE;
1649 ln_task_id ap_invoice_lines.task_id%TYPE;
1650 lv_exp_type ap_invoice_lines.expenditure_type%TYPE;
1651 ld_exp_item_date ap_invoice_lines.expenditure_item_date%TYPE;
1652 ln_exp_organization_id ap_invoice_lines.expenditure_organization_id%TYPE;
1653 lv_project_accounting_context ap_invoice_distributions_all.project_accounting_context%TYPE;
1654 lv_pa_addition_flag ap_invoice_distributions_all.pa_addition_flag%TYPE;
1655
1656
1657 ln_user_id NUMBER := fnd_global.user_id;
1658 ln_login_id NUMBER := fnd_global.login_id;
1659 lv_misc ap_invoice_distributions_all.line_type_lookup_code%TYPE := 'MISCELLANEOUS';
1660 lv_dist_class ap_invoice_distributions_all.distribution_class%TYPE := 'PERMANENT';
1661
1662
1663
1664 l_api_name CONSTANT VARCHAR2(30) := 'prepare_copy_tax';
1665 BEGIN
1666 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1668 END IF;
1669 OPEN for_org_id;
1670 FETCH for_org_id INTO for_org_id_rec;
1671 CLOSE for_org_id;
1672
1673 open c_fnd_curr_precision(for_org_id_rec.invoice_currency_code);
1674 fetch c_fnd_curr_precision into pn_currency_precision;
1675 close c_fnd_curr_precision;
1676
1677 IF pn_currency_precision IS NULL THEN
1678 pn_currency_precision := -1;
1679 END IF;
1680
1681 IF pr_ap_inv_line_item.po_distribution_id IS NULL THEN
1682
1683 SELECT Min(po_distribution_id)
1684 INTO ln_po_dist_id
1685 FROM po_distributions_all
1686 WHERE po_line_id = pr_ap_inv_line_item.po_line_id
1687 AND line_location_id = pr_ap_inv_line_item.po_line_location_id;
1688
1689 END IF;
1693 ,ln_po_dist_id
1690 ln_po_dist_id := Nvl(pr_ap_inv_line_item.po_distribution_id,ln_po_dist_id);
1691
1692 get_org_loc_id(pr_ap_inv_line_item.rcv_transaction_id
1694 ,pr_ap_inv_line_item.match_type
1695 ,ln_organization_id
1696 ,ln_location_id);
1697
1698 open c_get_item_id;
1699 fetch c_get_item_id into ln_item_id;
1700 close c_get_item_id;
1701
1702 pv_accrue_on_receipt_flag := jai_rcv_trx_processing_pkg.get_accrue_on_receipt (
1703 p_po_distribution_id => ln_po_dist_id);
1704
1705 pv_is_item_an_expense := jai_general_pkg.is_item_an_expense (
1706 p_organization_id => ln_organization_id,
1707 p_item_id => ln_item_id
1708 );
1709
1710 /*
1711 | Get the account type for the distribution.
1712 | This is required to find out whether charge_applicable_to_dist
1713 */
1714 OPEN get_account_type;
1715 FETCH get_account_type INTO lv_account_type;
1716 CLOSE get_account_type;
1717
1718 open c_regime_tax_type(jai_constants.service_regime, pr_tax_line_rec.tax_type);
1719 fetch c_regime_tax_type into r_service_regime_tax_type;
1720 close c_regime_tax_type;
1721
1722
1723 open c_regime_tax_type(jai_constants.vat_regime, pr_tax_line_rec.tax_type);
1724 fetch c_regime_tax_type into r_vat_regime_tax_type;
1725 close c_regime_tax_type;
1726
1727 OPEN c_tax(pr_tax_line_rec.tax_id);
1728 FETCH c_tax INTO c_tax_rec;
1729 CLOSE c_tax;
1730 pr_tax_line_rec.tax_name := c_tax_rec.tax_name;
1731 pr_tax_line_rec.rounding_factor := c_tax_rec.rounding_factor;
1732 IF pv_rematch <> 'PO_MATCHING' THEN
1733 open c_get_excise_costing_flag (cp_rcv_transaction_id => pr_ap_inv_line_item.rcv_transaction_id
1734 ,cp_organization_id => ln_organization_id
1735 ,cp_shipment_header_id => pn_shipment_hdr_id
1736 ,cp_txn_type => 'DELIVER'
1737 ,cp_attribute1 => 'CENVAT_COSTED_FLAG');
1738 fetch c_get_excise_costing_flag into lv_excise_costing_flag;
1739 close c_get_excise_costing_flag ;
1740 END IF;
1741
1742 if pr_tax_line_rec.modvat_flag = jai_constants.YES and nvl(pr_tax_line_rec.mod_cr_percentage, -1) > 0 then
1743
1744 --recoverable tax
1745 pv_tax_type := 'RE';
1746
1747 --recoverable tax
1748 v_assets_tracking_flag := 'N';
1749
1750
1751 if r_service_regime_tax_type.tax_type is not null THEN
1752
1753 /* Service type of tax */
1754 IF nvl(pr_tax_line_rec.reverse_charge_flag,'N') = 'N' THEN
1755 ln_dist_code_combination_id := get_service_account
1756 (
1757 pv_accrue_on_receipt_flag,
1758 pv_rematch,
1759 pr_tax_line_rec.tax_type,
1760 ln_organization_id,
1761 ln_location_id
1762 );
1763
1764 pn_incls_acct_id := get_service_account
1765 (
1766 pv_accrue_on_receipt_flag,
1767 'INCLUSIVE',
1768 pr_tax_line_rec.tax_type,
1769 ln_organization_id,
1770 ln_location_id
1771 );
1772 ln_dist_code_combination_id := nvl(ln_dist_code_combination_id,pr_ap_inv_dist_item.dist_code_combination_id);
1773 ELSE
1774 ln_dist_code_combination_id := get_service_account
1775 (
1776 pv_accrue_on_receipt_flag,
1777 'REVERSE_CHARGE',
1778 pr_tax_line_rec.tax_type,
1779 ln_organization_id,
1780 ln_location_id
1781 );
1782
1783
1784 pn_rvs_charge_liability_acct := get_service_account
1785 (
1786 pv_accrue_on_receipt_flag,
1787 'REVERSE_LIABILITY',
1788 pr_tax_line_rec.tax_type,
1789 ln_organization_id,
1790 ln_location_id
1791 );
1792 END IF;
1793
1794 ELSIF r_vat_regime_tax_type.tax_type IS NOT NULL THEN
1795
1796 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id;
1797 ELSE
1798
1799 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id;
1800
1801 END IF;
1802 ELSE -- NR tax ,project infor is same as the parent line
1803 IF pv_rematch = 'PO_MATCHING' THEN
1804 ln_project_id := pr_ap_inv_line_item.project_id;
1805 ln_task_id := pr_ap_inv_line_item.task_id;
1806 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
1807 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
1808 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
1809
1813 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
1810 ELSIF pv_rematch <> 'PO_MATCHING' AND nvl(pv_accrue_on_receipt_flag , '#') <> 'Y' THEN
1811 ln_project_id := pr_ap_inv_line_item.project_id;
1812 ln_task_id := pr_ap_inv_line_item.task_id;
1814 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
1815 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
1816
1817 lv_project_accounting_context := pr_ap_inv_dist_item.project_accounting_context;
1818 lv_pa_addition_flag := pr_ap_inv_dist_item.pa_addition_flag;
1819 END if;
1820 --non recoverable
1821 pv_tax_type := 'NR';
1822 END IF;
1823
1824 if pv_rematch <> 'PO_MATCHING' AND UPPER(pr_tax_line_rec.tax_type) like '%EXCISE%' then
1825
1826 if lv_excise_costing_flag = 'Y' THEN
1827 IF nvl(pv_accrue_on_receipt_flag , '#') <> 'Y' then
1828 ln_project_id := pr_ap_inv_line_item.project_id;
1829 ln_task_id := pr_ap_inv_line_item.task_id;
1830 lv_exp_type := pr_ap_inv_line_item.expenditure_type;
1831 ld_exp_item_date := pr_ap_inv_line_item.expenditure_item_date;
1832 ln_exp_organization_id := pr_ap_inv_line_item.expenditure_organization_id;
1833 lv_project_accounting_context := pr_ap_inv_dist_item.project_accounting_context;
1834 lv_pa_addition_flag := pr_ap_inv_dist_item.pa_addition_flag;
1835 END if;
1836 ln_dist_code_combination_id := pr_ap_inv_dist_item.dist_code_combination_id;
1837 end if;
1838 end if;
1839 /* IF pv_rematch = 'PO_MATCHING' THEN
1840
1841 jai_aptax_pkg_client_extn.JAI_CALC_APMATCH_TAX (P_MATCH_TYPE => 'PO_MATCHING',
1842 P_INVOICE_ID =>inv_id,
1843 P_INVOICE_line_no =>pn_invoice_line_number,
1844 P_TAX_ID => i.tax_id ,
1845 P_line_location_id => ln_po_line_location_id,
1846 P_tax_amount => v_tax_amount
1847 );
1848 ELSIF pv_rematch = 'RCV_MATCHING' THEN
1849 JAI_APTAX_PKG_CLIENT_EXTN.JAI_CALC_APMATCH_TAX (P_MATCH_TYPE => 'RCV_MATCHING',
1850 P_INVOICE_ID =>inv_id,
1851 P_INVOICE_line_no =>pn_invoice_line_number,
1852 P_TAX_ID => r_tax_lines_rec.tax_id,
1853 p_rcv_transaction_id => rcv_tran_id,
1854 P_tax_amount => v_tax_amount
1855 );
1856 END IF;
1857 fnd_file.put_line(FND_FILE.LOG, 'the tax amount after the JAI_CALC_APMATCH_TAX call is '||v_tax_amount);*/
1858
1859 pn_rec_tax_amt := null;
1860 pn_nrec_tax_amt := null;
1861 pn_lines_to_insert := 1;
1862
1863 --exclusive tax or inlcusive tax without project info, pn_lines_to_insert := 1;
1867 IF ( NVL(pr_tax_line_rec.inc_tax_flag,'N') = 'N'
1864 --inclusive recoverable tax with project info, pn_lines_to_insert := 2;
1865 --PR(partially recoverable) tax is processed in another logic
1866
1868 OR ( NVL(pr_tax_line_rec.inc_tax_flag,'N') = 'Y'
1869 AND pr_ap_inv_line_item.project_id IS NULL
1870 )
1871 )--exclusive tax or inlcusive tax without project info, pn_lines_to_insert := 1;
1872 THEN
1873 pn_lines_to_insert := 1;
1874 ELSIF ( NVL(pr_tax_line_rec.inc_tax_flag,'N') = 'Y'
1875 AND pr_ap_inv_line_item.project_id IS NOT NULL
1876 AND pv_tax_type = 'RE'
1877 )
1878 THEN --inclusive recoverable tax with project info, pn_lines_to_insert := 2;
1879 pn_lines_to_insert := 2;
1880 END IF;--( NVL(pr_tax_line_rec.inc_tax_flag,'N') = 'N' OR ( NVL(pr_tax_line_rec.inc_tax_flag,'N'))
1881
1882
1883 Fnd_File.put_line(Fnd_File.LOG, 'pr_tax_line_rec.modvat_flag ='||pr_tax_line_rec.modvat_flag ||',c_tax_rec.mod_cr_percentage='||c_tax_rec.mod_cr_percentage);
1884
1885 if pr_tax_line_rec.modvat_flag = jai_constants.YES
1886 and nvl(pr_tax_line_rec.mod_cr_percentage, -1) > 0
1887 and nvl(pr_tax_line_rec.mod_cr_percentage, -1) < 100
1888 then
1889 --
1890 -- Tax line is for partial Recoverable tax. Hence split amount into two parts, Recoverable and Non-Recoverable
1891 -- and instead of one line, two lines needs to be inserted.
1892 -- For ordinary lines (with modvat_flag = 'N' or with mod_cr_percentage = 100) there will be only one line inserted
1893 --
1894 pn_lines_to_insert := 2 * pn_lines_to_insert;
1898
1895 pn_rec_tax_amt := round ( nvl(pr_tax_line_rec.tax_amount,0) * (pr_tax_line_rec.mod_cr_percentage/100) , c_tax_rec.rounding_factor) ;
1896 pn_nrec_tax_amt := round ( nvl(pr_tax_line_rec.tax_amount,0) - nvl(pn_rec_tax_amt,0) , c_tax_rec.rounding_factor) ;
1897 pv_tax_type := 'PR';
1899 end if;
1900
1901 if pr_tax_line_rec.modvat_flag = jai_constants.YES then
1902 pn_inclurec_tax_amt := round ( nvl(pr_tax_line_rec.tax_amount,0) * (pr_tax_line_rec.mod_cr_percentage/100) , c_tax_rec.rounding_factor) ;
1903 end if;
1904 if nvl(pr_ap_inv_dist_item.invoice_price_variance, -1) <>-1
1905 and
1906 nvl(pr_ap_inv_dist_item.amount, -1) <> -1 THEN
1907
1908 v_tax_variance_inv_cur :=
1909 pr_tax_line_rec.tax_amount *
1910 (pr_ap_inv_dist_item.invoice_price_variance / pr_ap_inv_dist_item.amount);
1911
1912 if nvl(v_tax_variance_inv_cur, -1) <> -1 THEN
1913 v_tax_variance_fun_cur := v_tax_variance_inv_cur * nvl(for_org_id_rec.exchange_rate, 1);
1914 end if;
1915
1916 v_price_var_accnt := pr_ap_inv_dist_item.price_var_code_combination_id;
1917
1918 end if;
1919
1920 pr_ap_inv_dist_tax := NULL;
1921 pr_ap_inv_dist_tax.accounting_date := pr_ap_inv_dist_item.accounting_date;
1922 pr_ap_inv_dist_tax.accrual_posted_flag := 'N';
1923 pr_ap_inv_dist_tax.assets_addition_flag := pr_ap_inv_dist_item.assets_addition_flag;
1924 pr_ap_inv_dist_tax.assets_tracking_flag := v_assets_tracking_flag;
1925 pr_ap_inv_dist_tax.cash_posted_flag := 'N';
1926 pr_ap_inv_dist_tax.distribution_line_number := 1;
1927 pr_ap_inv_dist_tax.dist_code_combination_id := ln_dist_code_combination_id;
1928 pr_ap_inv_dist_tax.invoice_id := pr_ap_inv_dist_item.invoice_id;
1929 pr_ap_inv_dist_tax.last_updated_by := pr_ap_inv_dist_item.last_updated_by;
1930 pr_ap_inv_dist_tax.last_update_date := pr_ap_inv_dist_item.last_update_date;
1931 pr_ap_inv_dist_tax.line_type_lookup_code := lv_misc;
1932 pr_ap_inv_dist_tax.period_name := pr_ap_inv_dist_item.period_name;
1933 pr_ap_inv_dist_tax.set_of_books_id := pr_ap_inv_dist_item.set_of_books_id;
1934 pr_ap_inv_dist_tax.batch_id := for_org_id_rec.batch_id;
1935 pr_ap_inv_dist_tax.created_by := pr_ap_inv_dist_item.created_by;
1936 pr_ap_inv_dist_tax.creation_date := pr_ap_inv_dist_item.creation_date;
1937 pr_ap_inv_dist_tax.description := c_tax_rec.tax_name;
1938 pr_ap_inv_dist_tax.exchange_rate_variance := null;
1939 pr_ap_inv_dist_tax.last_update_login := pr_ap_inv_dist_item.last_update_login;
1940 pr_ap_inv_dist_tax.match_status_flag := pr_ap_inv_dist_item.match_status_flag;
1941 pr_ap_inv_dist_tax.posted_flag := 'N';
1942 pr_ap_inv_dist_tax.rate_var_code_combination_id := NULL;
1943 pr_ap_inv_dist_tax.reversal_flag := pr_ap_inv_dist_item.reversal_flag;
1944 pr_ap_inv_dist_tax.program_application_id := pr_ap_inv_dist_item.program_application_id;
1945 pr_ap_inv_dist_tax.program_id := pr_ap_inv_dist_item.program_id;
1946 pr_ap_inv_dist_tax.program_update_date := pr_ap_inv_dist_item.program_update_date;
1947 pr_ap_inv_dist_tax.accts_pay_code_combination_id := pr_ap_inv_dist_item.accts_pay_code_combination_id;
1948
1949 IF (v_assets_tracking_flag <> 'Y')
1950 THEN
1951 pr_ap_inv_dist_tax.quantity_invoiced := 0;
1952 END IF;
1953 pr_ap_inv_dist_tax.po_distribution_id := pr_ap_inv_dist_item.po_distribution_id ;
1954 pr_ap_inv_dist_tax.rcv_transaction_id := pr_ap_inv_dist_item.rcv_transaction_id;
1955 pr_ap_inv_dist_tax.price_var_code_combination_id := v_price_var_accnt;
1956 pr_ap_inv_dist_tax.invoice_price_variance := v_tax_variance_inv_cur;
1957 pr_ap_inv_dist_tax.base_invoice_price_variance := v_tax_variance_fun_cur;
1958 pr_ap_inv_dist_tax.matched_uom_lookup_code := pr_ap_inv_dist_item.matched_uom_lookup_code;
1959
1960 pr_ap_inv_dist_tax.org_id := pr_ap_inv_dist_item.org_id;
1961 IF (v_assets_tracking_flag <> 'N')
1962 THEN
1963 pr_ap_inv_dist_tax.charge_applicable_to_dist_id := pr_ap_inv_dist_item.invoice_distribution_id;
1964 END IF;
1965 pr_ap_inv_dist_tax.project_id := ln_project_id;
1966 pr_ap_inv_dist_tax.task_id := ln_task_id;
1967 pr_ap_inv_dist_tax.expenditure_type := lv_exp_type;
1968 pr_ap_inv_dist_tax.expenditure_item_date := ld_exp_item_date;
1969 pr_ap_inv_dist_tax.expenditure_organization_id := ln_exp_organization_id;
1970 pr_ap_inv_dist_tax.project_accounting_context := lv_project_accounting_context;
1971 pr_ap_inv_dist_tax.pa_addition_flag := lv_pa_addition_flag;
1972 pr_ap_inv_dist_tax.distribution_class := lv_dist_class;
1973 pr_ap_inv_dist_tax.dist_match_type := pr_ap_inv_dist_item.dist_match_type;
1974
1975 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1976 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
1977 END IF;
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1981 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
1982 END IF;
1983 app_exception.raise_exception;
1984
1985
1986 END prepare_copy_tax;
1987
1991 l_api_name CONSTANT VARCHAR2(50) := 'insert_jai_ap_match()';
1988 PROCEDURE insert_jai_ap_match(
1989 p_jai_ap_match_taxes_rec IN JAI_AP_MATCH_INV_TAXES%ROWTYPE
1990 )IS
1992 BEGIN
1993 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1994 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1995 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'invoice line num: '||p_jai_ap_match_taxes_rec.INVOICE_LINE_NUMBER);
1996
1997 END IF;
1998
1999 INSERT INTO JAI_AP_MATCH_INV_TAXES
2000 (
2001 tax_distribution_id,
2002 assets_tracking_flag,
2003 invoice_id,
2004 po_header_id,
2005 po_line_id,
2006 line_location_id,
2007 set_of_books_id,
2008 exchange_rate,
2009 exchange_rate_type,
2010 exchange_date,
2011 currency_code,
2012 code_combination_id,
2013 last_update_login,
2014 creation_date,
2015 created_by,
2016 last_update_date,
2017 last_updated_by,
2018 acct_pay_code_combination_id,
2019 accounting_date,
2020 tax_id,
2021 tax_amount,
2022 base_amount,
2023 chart_of_accounts_id,
2024 distribution_line_number,
2025 po_distribution_id,
2026 parent_invoice_distribution_id,
2027 legal_entity_id
2028 ,INVOICE_LINE_NUMBER
2029 ,INVOICE_DISTRIBUTION_ID
2030 ,PARENT_INVOICE_LINE_NUMBER
2031 ,RCV_TRANSACTION_ID
2032 ,LINE_TYPE_LOOKUP_CODE
2033 , recoverable_flag
2034 , line_no
2035 )
2036 VALUES
2037 (
2038 JAI_AP_MATCH_INV_TAXES_S.NEXTVAL,
2039 p_jai_ap_match_taxes_rec.assets_tracking_flag,
2040 p_jai_ap_match_taxes_rec.invoice_id,
2041 p_jai_ap_match_taxes_rec.po_header_id,
2042 p_jai_ap_match_taxes_rec.po_line_id,
2043 p_jai_ap_match_taxes_rec.line_location_id,
2044 p_jai_ap_match_taxes_rec.set_of_books_id,
2045 p_jai_ap_match_taxes_rec.exchange_rate,
2046 p_jai_ap_match_taxes_rec.exchange_rate_type,
2047 p_jai_ap_match_taxes_rec.exchange_date,
2048 p_jai_ap_match_taxes_rec.currency_code,
2049 p_jai_ap_match_taxes_rec.code_combination_id,
2050 p_jai_ap_match_taxes_rec.last_update_login,
2051 p_jai_ap_match_taxes_rec.creation_date,
2052 p_jai_ap_match_taxes_rec.created_by,
2053 p_jai_ap_match_taxes_rec.last_update_date,
2054 p_jai_ap_match_taxes_rec.last_updated_by,
2055 p_jai_ap_match_taxes_rec.acct_pay_code_combination_id,
2056 p_jai_ap_match_taxes_rec.accounting_date,
2057 p_jai_ap_match_taxes_rec.tax_id,
2058 p_jai_ap_match_taxes_rec.tax_amount,
2059 p_jai_ap_match_taxes_rec.base_amount,
2060 p_jai_ap_match_taxes_rec.chart_of_accounts_id,
2061 p_jai_ap_match_taxes_rec.distribution_line_number,
2062 p_jai_ap_match_taxes_rec.po_distribution_id,
2063 p_jai_ap_match_taxes_rec.parent_invoice_distribution_id,
2064 p_jai_ap_match_taxes_rec.legal_entity_id ,
2065 p_jai_ap_match_taxes_rec.INVOICE_LINE_NUMBER,
2066 p_jai_ap_match_taxes_rec.INVOICE_DISTRIBUTION_ID ,
2067 p_jai_ap_match_taxes_rec.PARENT_INVOICE_LINE_NUMBER ,
2068 p_jai_ap_match_taxes_rec.RCV_TRANSACTION_ID ,
2069 p_jai_ap_match_taxes_rec.LINE_TYPE_LOOKUP_CODE ,
2070 p_jai_ap_match_taxes_rec.recoverable_flag ,
2071 p_jai_ap_match_taxes_rec.line_no
2072 );
2073
2074
2075
2076 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
2078 END IF;
2079 EXCEPTION
2080 WHEN OTHERS THEN
2081 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
2082 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
2083 END IF;
2084 app_exception.raise_exception;
2085
2086 END insert_jai_ap_match;
2087
2088 PROCEDURE insert_ap_inv_line(
2089 p_ap_invoice_lines_all_rec IN ap_invoice_lines_all%ROWTYPE
2090 )IS
2091 l_api_name CONSTANT VARCHAR2(50) := 'insert_ap_inv_line()';
2092 BEGIN
2093 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2094 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
2095 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'invoice line num: '||p_ap_invoice_lines_all_rec.LINE_NUMBER);
2096 END IF;
2097
2098 INSERT INTO ap_invoice_lines_all
2099 (INVOICE_ID
2100 ,LINE_NUMBER
2101 ,LINE_TYPE_LOOKUP_CODE
2102 ,REQUESTER_ID
2103 ,DESCRIPTION
2104 ,LINE_SOURCE
2105 ,ORG_ID
2106 ,LINE_GROUP_NUMBER
2107 ,INVENTORY_ITEM_ID
2108 ,ITEM_DESCRIPTION
2109 ,SERIAL_NUMBER
2110 ,MANUFACTURER
2111 ,MODEL_NUMBER
2112 ,WARRANTY_NUMBER
2113 ,GENERATE_DISTS
2114 ,MATCH_TYPE
2115 ,DISTRIBUTION_SET_ID
2116 ,ACCOUNT_SEGMENT
2117 ,BALANCING_SEGMENT
2118 ,COST_CENTER_SEGMENT
2119 ,OVERLAY_DIST_CODE_CONCAT
2120 ,DEFAULT_DIST_CCID
2121 ,PRORATE_ACROSS_ALL_ITEMS
2122 ,ACCOUNTING_DATE
2123 ,PERIOD_NAME
2124 ,DEFERRED_ACCTG_FLAG
2125 ,DEF_ACCTG_START_DATE
2126 ,DEF_ACCTG_END_DATE
2127 ,DEF_ACCTG_NUMBER_OF_PERIODS
2128 ,DEF_ACCTG_PERIOD_TYPE
2129 ,SET_OF_BOOKS_ID
2130 ,AMOUNT
2131 ,BASE_AMOUNT
2132 ,ROUNDING_AMT
2133 ,QUANTITY_INVOICED
2134 ,UNIT_MEAS_LOOKUP_CODE
2138 ,DISCARDED_FLAG
2135 ,UNIT_PRICE
2136 ,WFAPPROVAL_STATUS
2137 ,USSGL_TRANSACTION_CODE
2139 ,ORIGINAL_AMOUNT
2140 ,ORIGINAL_BASE_AMOUNT
2141 ,ORIGINAL_ROUNDING_AMT
2142 ,CANCELLED_FLAG
2143 ,INCOME_TAX_REGION
2147 ,PREPAY_LINE_NUMBER
2144 ,TYPE_1099
2145 ,STAT_AMOUNT
2146 ,PREPAY_INVOICE_ID
2148 ,INVOICE_INCLUDES_PREPAY_FLAG
2149 ,CORRECTED_INV_ID
2150 ,CORRECTED_LINE_NUMBER
2151 ,PO_HEADER_ID
2152 ,PO_LINE_ID
2153 ,PO_RELEASE_ID
2154 ,PO_LINE_LOCATION_ID
2155 ,PO_DISTRIBUTION_ID
2156 ,RCV_TRANSACTION_ID
2157 ,FINAL_MATCH_FLAG
2158 ,ASSETS_TRACKING_FLAG
2159 ,ASSET_BOOK_TYPE_CODE
2160 ,ASSET_CATEGORY_ID
2161 ,PROJECT_ID
2162 ,TASK_ID
2163 ,EXPENDITURE_TYPE
2164 ,EXPENDITURE_ITEM_DATE
2165 ,EXPENDITURE_ORGANIZATION_ID
2166 ,PA_QUANTITY
2167 ,PA_CC_AR_INVOICE_ID
2168 ,PA_CC_AR_INVOICE_LINE_NUM
2169 ,PA_CC_PROCESSED_CODE
2170 ,AWARD_ID
2171 ,AWT_GROUP_ID
2172 ,REFERENCE_1
2173 ,REFERENCE_2
2174 ,RECEIPT_VERIFIED_FLAG
2175 ,RECEIPT_REQUIRED_FLAG
2176 ,RECEIPT_MISSING_FLAG
2177 ,JUSTIFICATION
2178 ,EXPENSE_GROUP
2179 ,START_EXPENSE_DATE
2180 ,END_EXPENSE_DATE
2181 ,RECEIPT_CURRENCY_CODE
2182 ,RECEIPT_CONVERSION_RATE
2183 ,RECEIPT_CURRENCY_AMOUNT
2184 ,DAILY_AMOUNT
2185 ,WEB_PARAMETER_ID
2186 ,ADJUSTMENT_REASON
2187 ,MERCHANT_DOCUMENT_NUMBER
2188 ,MERCHANT_NAME
2189 ,MERCHANT_REFERENCE
2190 ,MERCHANT_TAX_REG_NUMBER
2191 ,MERCHANT_TAXPAYER_ID
2192 ,COUNTRY_OF_SUPPLY
2193 ,CREDIT_CARD_TRX_ID
2194 ,COMPANY_PREPAID_INVOICE_ID
2195 ,CC_REVERSAL_FLAG
2196 ,CREATION_DATE
2197 ,CREATED_BY
2198 ,LAST_UPDATED_BY
2199 ,LAST_UPDATE_DATE
2200 ,LAST_UPDATE_LOGIN
2201 ,PROGRAM_APPLICATION_ID
2202 ,PROGRAM_ID
2203 ,PROGRAM_UPDATE_DATE
2204 ,REQUEST_ID
2205 ,ATTRIBUTE_CATEGORY
2206 ,ATTRIBUTE1
2207 ,ATTRIBUTE2
2208 ,ATTRIBUTE3
2209 ,ATTRIBUTE4
2210 ,ATTRIBUTE5
2211 ,ATTRIBUTE6
2212 ,ATTRIBUTE7
2213 ,ATTRIBUTE8
2214 ,ATTRIBUTE9
2215 ,ATTRIBUTE10
2216 ,ATTRIBUTE11
2217 ,ATTRIBUTE12
2218 ,ATTRIBUTE13
2219 ,ATTRIBUTE14
2220 ,ATTRIBUTE15
2221 ,GLOBAL_ATTRIBUTE_CATEGORY
2222 ,GLOBAL_ATTRIBUTE1
2223 ,GLOBAL_ATTRIBUTE2
2224 ,GLOBAL_ATTRIBUTE3
2225 ,GLOBAL_ATTRIBUTE4
2226 ,GLOBAL_ATTRIBUTE5
2227 ,GLOBAL_ATTRIBUTE6
2228 ,GLOBAL_ATTRIBUTE7
2229 ,GLOBAL_ATTRIBUTE8
2230 ,GLOBAL_ATTRIBUTE9
2231 ,GLOBAL_ATTRIBUTE10
2232 ,GLOBAL_ATTRIBUTE11
2233 ,GLOBAL_ATTRIBUTE12
2234 ,GLOBAL_ATTRIBUTE13
2235 ,GLOBAL_ATTRIBUTE14
2236 ,GLOBAL_ATTRIBUTE15
2237 ,GLOBAL_ATTRIBUTE16
2238 ,GLOBAL_ATTRIBUTE17
2239 ,GLOBAL_ATTRIBUTE18
2240 ,GLOBAL_ATTRIBUTE19
2241 ,GLOBAL_ATTRIBUTE20
2242 ,LINE_SELECTED_FOR_APPL_FLAG
2243 ,PREPAY_APPL_REQUEST_ID
2244 ,APPLICATION_ID
2245 ,PRODUCT_TABLE
2246 ,REFERENCE_KEY1
2247 ,REFERENCE_KEY2
2248 ,REFERENCE_KEY3
2249 ,REFERENCE_KEY4
2250 ,REFERENCE_KEY5
2251 ,PURCHASING_CATEGORY_ID
2252 ,COST_FACTOR_ID
2253 ,CONTROL_AMOUNT
2254 ,ASSESSABLE_VALUE
2255 ,TOTAL_REC_TAX_AMOUNT
2256 ,TOTAL_NREC_TAX_AMOUNT
2260 ,PRIMARY_INTENDED_USE
2257 ,TOTAL_REC_TAX_AMT_FUNCL_CURR
2258 ,TOTAL_NREC_TAX_AMT_FUNCL_CURR
2259 ,INCLUDED_TAX_AMOUNT
2261 ,TAX_ALREADY_CALCULATED_FLAG
2262 ,SHIP_TO_LOCATION_ID
2263 ,PRODUCT_TYPE
2264 ,PRODUCT_CATEGORY
2265 ,PRODUCT_FISC_CLASSIFICATION
2266 ,USER_DEFINED_FISC_CLASS
2267 ,TRX_BUSINESS_CATEGORY
2268 ,SUMMARY_TAX_LINE_ID
2269 ,TAX_REGIME_CODE
2270 ,TAX
2271 ,TAX_JURISDICTION_CODE
2272 ,TAX_STATUS_CODE
2273 ,TAX_RATE_ID
2274 ,TAX_RATE_CODE
2275 ,TAX_RATE
2276 ,TAX_CODE_ID
2277 ,HISTORICAL_FLAG
2278 ,TAX_CLASSIFICATION_CODE
2279 ,SOURCE_APPLICATION_ID
2280 ,SOURCE_EVENT_CLASS_CODE
2281 ,SOURCE_ENTITY_CODE
2282 ,SOURCE_TRX_ID
2283 ,SOURCE_LINE_ID
2284 ,SOURCE_TRX_LEVEL_TYPE
2285 ,RETAINED_AMOUNT
2286 ,RETAINED_AMOUNT_REMAINING
2287 ,RETAINED_INVOICE_ID
2288 ,RETAINED_LINE_NUMBER
2289 ,LINE_SELECTED_FOR_RELEASE_FLAG
2290 ,LINE_OWNER_ROLE
2291 ,DISPUTABLE_FLAG
2292 ,RCV_SHIPMENT_LINE_ID
2293 ,AIL_INVOICE_ID
2294 ,AIL_DISTRIBUTION_LINE_NUMBER
2295 ,AIL_INVOICE_ID2
2296 ,AIL_DISTRIBUTION_LINE_NUMBER2
2297 ,AIL_INVOICE_ID3
2298 ,AIL_DISTRIBUTION_LINE_NUMBER3
2299 ,AIL_INVOICE_ID4
2300 ,PAY_AWT_GROUP_ID
2301 )
2302 VALUES
2303 (p_ap_invoice_lines_all_rec.INVOICE_ID
2304 ,p_ap_invoice_lines_all_rec.LINE_NUMBER
2305 ,p_ap_invoice_lines_all_rec.LINE_TYPE_LOOKUP_CODE
2306 ,p_ap_invoice_lines_all_rec.REQUESTER_ID
2307 ,p_ap_invoice_lines_all_rec.DESCRIPTION
2308 ,p_ap_invoice_lines_all_rec.LINE_SOURCE
2309 ,p_ap_invoice_lines_all_rec.ORG_ID
2310 ,p_ap_invoice_lines_all_rec.LINE_GROUP_NUMBER
2311 ,p_ap_invoice_lines_all_rec.INVENTORY_ITEM_ID
2312 ,p_ap_invoice_lines_all_rec.ITEM_DESCRIPTION
2313 ,p_ap_invoice_lines_all_rec.SERIAL_NUMBER
2314 ,p_ap_invoice_lines_all_rec.MANUFACTURER
2315 ,p_ap_invoice_lines_all_rec.MODEL_NUMBER
2316 ,p_ap_invoice_lines_all_rec.WARRANTY_NUMBER
2317 ,p_ap_invoice_lines_all_rec.GENERATE_DISTS
2318 ,p_ap_invoice_lines_all_rec.MATCH_TYPE
2319 ,p_ap_invoice_lines_all_rec.DISTRIBUTION_SET_ID
2320 ,p_ap_invoice_lines_all_rec.ACCOUNT_SEGMENT
2321 ,p_ap_invoice_lines_all_rec.BALANCING_SEGMENT
2322 ,p_ap_invoice_lines_all_rec.COST_CENTER_SEGMENT
2323 ,p_ap_invoice_lines_all_rec.OVERLAY_DIST_CODE_CONCAT
2324 ,p_ap_invoice_lines_all_rec.DEFAULT_DIST_CCID
2325 ,p_ap_invoice_lines_all_rec.PRORATE_ACROSS_ALL_ITEMS
2326 ,p_ap_invoice_lines_all_rec.ACCOUNTING_DATE
2327 ,p_ap_invoice_lines_all_rec.PERIOD_NAME
2328 ,p_ap_invoice_lines_all_rec.DEFERRED_ACCTG_FLAG
2329 ,p_ap_invoice_lines_all_rec.DEF_ACCTG_START_DATE
2330 ,p_ap_invoice_lines_all_rec.DEF_ACCTG_END_DATE
2331 ,p_ap_invoice_lines_all_rec.DEF_ACCTG_NUMBER_OF_PERIODS
2332 ,p_ap_invoice_lines_all_rec.DEF_ACCTG_PERIOD_TYPE
2333 ,p_ap_invoice_lines_all_rec.SET_OF_BOOKS_ID
2334 ,p_ap_invoice_lines_all_rec.AMOUNT
2335 ,p_ap_invoice_lines_all_rec.BASE_AMOUNT
2336 ,p_ap_invoice_lines_all_rec.ROUNDING_AMT
2337 ,p_ap_invoice_lines_all_rec.QUANTITY_INVOICED
2338 ,p_ap_invoice_lines_all_rec.UNIT_MEAS_LOOKUP_CODE
2339 ,p_ap_invoice_lines_all_rec.UNIT_PRICE
2340 ,p_ap_invoice_lines_all_rec.WFAPPROVAL_STATUS
2341 ,p_ap_invoice_lines_all_rec.USSGL_TRANSACTION_CODE
2342 ,p_ap_invoice_lines_all_rec.DISCARDED_FLAG
2343 ,p_ap_invoice_lines_all_rec.ORIGINAL_AMOUNT
2344 ,p_ap_invoice_lines_all_rec.ORIGINAL_BASE_AMOUNT
2345 ,p_ap_invoice_lines_all_rec.ORIGINAL_ROUNDING_AMT
2346 ,p_ap_invoice_lines_all_rec.CANCELLED_FLAG
2347 ,p_ap_invoice_lines_all_rec.INCOME_TAX_REGION
2348 ,p_ap_invoice_lines_all_rec.TYPE_1099
2349 ,p_ap_invoice_lines_all_rec.STAT_AMOUNT
2350 ,p_ap_invoice_lines_all_rec.PREPAY_INVOICE_ID
2351 ,p_ap_invoice_lines_all_rec.PREPAY_LINE_NUMBER
2352 ,p_ap_invoice_lines_all_rec.INVOICE_INCLUDES_PREPAY_FLAG
2353 ,p_ap_invoice_lines_all_rec.CORRECTED_INV_ID
2354 ,p_ap_invoice_lines_all_rec.CORRECTED_LINE_NUMBER
2355 ,p_ap_invoice_lines_all_rec.PO_HEADER_ID
2356 ,p_ap_invoice_lines_all_rec.PO_LINE_ID
2357 ,p_ap_invoice_lines_all_rec.PO_RELEASE_ID
2358 ,p_ap_invoice_lines_all_rec.PO_LINE_LOCATION_ID
2359 ,p_ap_invoice_lines_all_rec.PO_DISTRIBUTION_ID
2360 ,p_ap_invoice_lines_all_rec.RCV_TRANSACTION_ID
2361 ,p_ap_invoice_lines_all_rec.FINAL_MATCH_FLAG
2362 ,p_ap_invoice_lines_all_rec.ASSETS_TRACKING_FLAG
2363 ,p_ap_invoice_lines_all_rec.ASSET_BOOK_TYPE_CODE
2364 ,p_ap_invoice_lines_all_rec.ASSET_CATEGORY_ID
2365 ,p_ap_invoice_lines_all_rec.PROJECT_ID
2366 ,p_ap_invoice_lines_all_rec.TASK_ID
2367 ,p_ap_invoice_lines_all_rec.EXPENDITURE_TYPE
2368 ,p_ap_invoice_lines_all_rec.EXPENDITURE_ITEM_DATE
2369 ,p_ap_invoice_lines_all_rec.EXPENDITURE_ORGANIZATION_ID
2370 ,p_ap_invoice_lines_all_rec.PA_QUANTITY
2371 ,p_ap_invoice_lines_all_rec.PA_CC_AR_INVOICE_ID
2372 ,p_ap_invoice_lines_all_rec.PA_CC_AR_INVOICE_LINE_NUM
2373 ,p_ap_invoice_lines_all_rec.PA_CC_PROCESSED_CODE
2374 ,p_ap_invoice_lines_all_rec.AWARD_ID
2375 ,p_ap_invoice_lines_all_rec.AWT_GROUP_ID
2376 ,p_ap_invoice_lines_all_rec.REFERENCE_1
2377 ,p_ap_invoice_lines_all_rec.REFERENCE_2
2378 ,p_ap_invoice_lines_all_rec.RECEIPT_VERIFIED_FLAG
2379 ,p_ap_invoice_lines_all_rec.RECEIPT_REQUIRED_FLAG
2380 ,p_ap_invoice_lines_all_rec.RECEIPT_MISSING_FLAG
2381 ,p_ap_invoice_lines_all_rec.JUSTIFICATION
2382 ,p_ap_invoice_lines_all_rec.EXPENSE_GROUP
2383 ,p_ap_invoice_lines_all_rec.START_EXPENSE_DATE
2384 ,p_ap_invoice_lines_all_rec.END_EXPENSE_DATE
2385 ,p_ap_invoice_lines_all_rec.RECEIPT_CURRENCY_CODE
2386 ,p_ap_invoice_lines_all_rec.RECEIPT_CONVERSION_RATE
2387 ,p_ap_invoice_lines_all_rec.RECEIPT_CURRENCY_AMOUNT
2388 ,p_ap_invoice_lines_all_rec.DAILY_AMOUNT
2389 ,p_ap_invoice_lines_all_rec.WEB_PARAMETER_ID
2390 ,p_ap_invoice_lines_all_rec.ADJUSTMENT_REASON
2391 ,p_ap_invoice_lines_all_rec.MERCHANT_DOCUMENT_NUMBER
2392 ,p_ap_invoice_lines_all_rec.MERCHANT_NAME
2393 ,p_ap_invoice_lines_all_rec.MERCHANT_REFERENCE
2394 ,p_ap_invoice_lines_all_rec.MERCHANT_TAX_REG_NUMBER
2395 ,p_ap_invoice_lines_all_rec.MERCHANT_TAXPAYER_ID
2396 ,p_ap_invoice_lines_all_rec.COUNTRY_OF_SUPPLY
2397 ,p_ap_invoice_lines_all_rec.CREDIT_CARD_TRX_ID
2398 ,p_ap_invoice_lines_all_rec.COMPANY_PREPAID_INVOICE_ID
2399 ,p_ap_invoice_lines_all_rec.CC_REVERSAL_FLAG
2400 ,p_ap_invoice_lines_all_rec.CREATION_DATE
2401 ,p_ap_invoice_lines_all_rec.CREATED_BY
2402 ,p_ap_invoice_lines_all_rec.LAST_UPDATED_BY
2403 ,p_ap_invoice_lines_all_rec.LAST_UPDATE_DATE
2404 ,p_ap_invoice_lines_all_rec.LAST_UPDATE_LOGIN
2405 ,p_ap_invoice_lines_all_rec.PROGRAM_APPLICATION_ID
2406 ,p_ap_invoice_lines_all_rec.PROGRAM_ID
2407 ,p_ap_invoice_lines_all_rec.PROGRAM_UPDATE_DATE
2408 ,p_ap_invoice_lines_all_rec.REQUEST_ID
2409 ,p_ap_invoice_lines_all_rec.ATTRIBUTE_CATEGORY
2410 ,p_ap_invoice_lines_all_rec.ATTRIBUTE1
2411 ,p_ap_invoice_lines_all_rec.ATTRIBUTE2
2412 ,p_ap_invoice_lines_all_rec.ATTRIBUTE3
2413 ,p_ap_invoice_lines_all_rec.ATTRIBUTE4
2414 ,p_ap_invoice_lines_all_rec.ATTRIBUTE5
2415 ,p_ap_invoice_lines_all_rec.ATTRIBUTE6
2416 ,p_ap_invoice_lines_all_rec.ATTRIBUTE7
2417 ,p_ap_invoice_lines_all_rec.ATTRIBUTE8
2418 ,p_ap_invoice_lines_all_rec.ATTRIBUTE9
2419 ,p_ap_invoice_lines_all_rec.ATTRIBUTE10
2420 ,p_ap_invoice_lines_all_rec.ATTRIBUTE11
2421 ,p_ap_invoice_lines_all_rec.ATTRIBUTE12
2422 ,p_ap_invoice_lines_all_rec.ATTRIBUTE13
2423 ,p_ap_invoice_lines_all_rec.ATTRIBUTE14
2424 ,p_ap_invoice_lines_all_rec.ATTRIBUTE15
2425 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE_CATEGORY
2426 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE1
2427 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE2
2428 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE3
2429 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE4
2430 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE5
2431 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE6
2432 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE7
2433 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE8
2434 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE9
2435 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE10
2436 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE11
2437 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE12
2438 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE13
2439 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE14
2440 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE15
2441 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE16
2442 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE17
2443 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE18
2444 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE19
2445 ,p_ap_invoice_lines_all_rec.GLOBAL_ATTRIBUTE20
2446 ,p_ap_invoice_lines_all_rec.LINE_SELECTED_FOR_APPL_FLAG
2447 ,p_ap_invoice_lines_all_rec.PREPAY_APPL_REQUEST_ID
2448 ,p_ap_invoice_lines_all_rec.APPLICATION_ID
2449 ,p_ap_invoice_lines_all_rec.PRODUCT_TABLE
2450 ,p_ap_invoice_lines_all_rec.REFERENCE_KEY1
2451 ,p_ap_invoice_lines_all_rec.REFERENCE_KEY2
2452 ,p_ap_invoice_lines_all_rec.REFERENCE_KEY3
2453 ,p_ap_invoice_lines_all_rec.REFERENCE_KEY4
2454 ,p_ap_invoice_lines_all_rec.REFERENCE_KEY5
2455 ,p_ap_invoice_lines_all_rec.PURCHASING_CATEGORY_ID
2456 ,p_ap_invoice_lines_all_rec.COST_FACTOR_ID
2457 ,p_ap_invoice_lines_all_rec.CONTROL_AMOUNT
2458 ,p_ap_invoice_lines_all_rec.ASSESSABLE_VALUE
2459 ,p_ap_invoice_lines_all_rec.TOTAL_REC_TAX_AMOUNT
2460 ,p_ap_invoice_lines_all_rec.TOTAL_NREC_TAX_AMOUNT
2461 ,p_ap_invoice_lines_all_rec.TOTAL_REC_TAX_AMT_FUNCL_CURR
2462 ,p_ap_invoice_lines_all_rec.TOTAL_NREC_TAX_AMT_FUNCL_CURR
2463 ,p_ap_invoice_lines_all_rec.INCLUDED_TAX_AMOUNT
2464 ,p_ap_invoice_lines_all_rec.PRIMARY_INTENDED_USE
2465 ,p_ap_invoice_lines_all_rec.TAX_ALREADY_CALCULATED_FLAG
2466 ,p_ap_invoice_lines_all_rec.SHIP_TO_LOCATION_ID
2467 ,p_ap_invoice_lines_all_rec.PRODUCT_TYPE
2468 ,p_ap_invoice_lines_all_rec.PRODUCT_CATEGORY
2469 ,p_ap_invoice_lines_all_rec.PRODUCT_FISC_CLASSIFICATION
2470 ,p_ap_invoice_lines_all_rec.USER_DEFINED_FISC_CLASS
2471 ,p_ap_invoice_lines_all_rec.TRX_BUSINESS_CATEGORY
2472 ,p_ap_invoice_lines_all_rec.SUMMARY_TAX_LINE_ID
2473 ,p_ap_invoice_lines_all_rec.TAX_REGIME_CODE
2474 ,p_ap_invoice_lines_all_rec.TAX
2475 ,p_ap_invoice_lines_all_rec.TAX_JURISDICTION_CODE
2476 ,p_ap_invoice_lines_all_rec.TAX_STATUS_CODE
2480 ,p_ap_invoice_lines_all_rec.TAX_CODE_ID
2477 ,p_ap_invoice_lines_all_rec.TAX_RATE_ID
2478 ,p_ap_invoice_lines_all_rec.TAX_RATE_CODE
2479 ,p_ap_invoice_lines_all_rec.TAX_RATE
2481 ,p_ap_invoice_lines_all_rec.HISTORICAL_FLAG
2482 ,p_ap_invoice_lines_all_rec.TAX_CLASSIFICATION_CODE
2483 ,p_ap_invoice_lines_all_rec.SOURCE_APPLICATION_ID
2487 ,p_ap_invoice_lines_all_rec.SOURCE_LINE_ID
2484 ,p_ap_invoice_lines_all_rec.SOURCE_EVENT_CLASS_CODE
2485 ,p_ap_invoice_lines_all_rec.SOURCE_ENTITY_CODE
2486 ,p_ap_invoice_lines_all_rec.SOURCE_TRX_ID
2488 ,p_ap_invoice_lines_all_rec.SOURCE_TRX_LEVEL_TYPE
2489 ,p_ap_invoice_lines_all_rec.RETAINED_AMOUNT
2490 ,p_ap_invoice_lines_all_rec.RETAINED_AMOUNT_REMAINING
2491 ,p_ap_invoice_lines_all_rec.RETAINED_INVOICE_ID
2492 ,p_ap_invoice_lines_all_rec.RETAINED_LINE_NUMBER
2493 ,p_ap_invoice_lines_all_rec.LINE_SELECTED_FOR_RELEASE_FLAG
2494 ,p_ap_invoice_lines_all_rec.LINE_OWNER_ROLE
2495 ,p_ap_invoice_lines_all_rec.DISPUTABLE_FLAG
2496 ,p_ap_invoice_lines_all_rec.RCV_SHIPMENT_LINE_ID
2497 ,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID
2498 ,p_ap_invoice_lines_all_rec.AIL_DISTRIBUTION_LINE_NUMBER
2499 ,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID2
2500 ,p_ap_invoice_lines_all_rec.AIL_DISTRIBUTION_LINE_NUMBER2
2501 ,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID3
2502 ,p_ap_invoice_lines_all_rec.AIL_DISTRIBUTION_LINE_NUMBER3
2503 ,p_ap_invoice_lines_all_rec.AIL_INVOICE_ID4
2504 ,p_ap_invoice_lines_all_rec.PAY_AWT_GROUP_ID
2505 );
2506
2507
2508 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2509 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
2510 END IF;
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
2514 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
2515 END IF;
2516 app_exception.raise_exception;
2517 END insert_ap_inv_line;
2518
2519 FUNCTION insert_ap_inv_dist(
2520 p_ap_invoice_dist_all_rec IN ap_invoice_distributions_all%ROWTYPE
2521 )RETURN NUMBER IS
2522 CURSOR c_get_inv_dist_id IS
2523 select ap_invoice_distributions_s.nextval
2524 from dual;
2525
2526 ln_dist_id NUMBER;
2527 l_api_name CONSTANT VARCHAR2(50) := 'insert_ap_inv_dist()';
2528 BEGIN
2529 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2530 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
2531 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'invoice line num: '||p_ap_invoice_dist_all_rec.INVOICE_LINE_NUMBER);
2532 END IF;
2533 OPEN c_get_inv_dist_id;
2534 FETCH c_get_inv_dist_id INTO ln_dist_id;
2535 CLOSE c_get_inv_dist_id;
2536 INSERT INTO ap_invoice_distributions_all
2537 (ACCOUNTING_DATE
2538 ,ACCRUAL_POSTED_FLAG
2539 ,ASSETS_ADDITION_FLAG
2540 ,ASSETS_TRACKING_FLAG
2541 ,CASH_POSTED_FLAG
2542 ,DISTRIBUTION_LINE_NUMBER
2543 ,DIST_CODE_COMBINATION_ID
2544 ,INVOICE_ID
2545 ,LAST_UPDATED_BY
2546 ,LAST_UPDATE_DATE
2547 ,LINE_TYPE_LOOKUP_CODE
2548 ,PERIOD_NAME
2549 ,SET_OF_BOOKS_ID
2550 ,ACCTS_PAY_CODE_COMBINATION_ID
2551 ,AMOUNT
2552 ,BASE_AMOUNT
2553 ,BASE_INVOICE_PRICE_VARIANCE
2554 ,BATCH_ID
2555 ,CREATED_BY
2556 ,CREATION_DATE
2557 ,DESCRIPTION
2558 ,EXCHANGE_RATE_VARIANCE
2559 ,FINAL_MATCH_FLAG
2560 ,INCOME_TAX_REGION
2561 ,INVOICE_PRICE_VARIANCE
2562 ,LAST_UPDATE_LOGIN
2563 ,MATCH_STATUS_FLAG
2564 ,POSTED_FLAG
2565 ,PO_DISTRIBUTION_ID
2566 ,PROGRAM_APPLICATION_ID
2567 ,PROGRAM_ID
2568 ,PROGRAM_UPDATE_DATE
2569 ,QUANTITY_INVOICED
2570 ,RATE_VAR_CODE_COMBINATION_ID
2571 ,REQUEST_ID
2572 ,REVERSAL_FLAG
2573 ,TYPE_1099
2574 ,UNIT_PRICE
2575 ,AMOUNT_ENCUMBERED
2576 ,BASE_AMOUNT_ENCUMBERED
2577 ,ENCUMBERED_FLAG
2578 ,EXCHANGE_DATE
2579 ,EXCHANGE_RATE
2580 ,EXCHANGE_RATE_TYPE
2581 ,PRICE_ADJUSTMENT_FLAG
2582 ,PRICE_VAR_CODE_COMBINATION_ID
2583 ,QUANTITY_UNENCUMBERED
2584 ,STAT_AMOUNT
2585 ,AMOUNT_TO_POST
2586 ,ATTRIBUTE1
2587 ,ATTRIBUTE10
2588 ,ATTRIBUTE11
2589 ,ATTRIBUTE12
2590 ,ATTRIBUTE13
2591 ,ATTRIBUTE14
2592 ,ATTRIBUTE15
2593 ,ATTRIBUTE2
2594 ,ATTRIBUTE3
2595 ,ATTRIBUTE4
2596 ,ATTRIBUTE5
2597 ,ATTRIBUTE6
2598 ,ATTRIBUTE7
2599 ,ATTRIBUTE8
2600 ,ATTRIBUTE9
2601 ,ATTRIBUTE_CATEGORY
2602 ,BASE_AMOUNT_TO_POST
2603 ,CASH_JE_BATCH_ID
2604 ,EXPENDITURE_ITEM_DATE
2605 ,EXPENDITURE_ORGANIZATION_ID
2606 ,EXPENDITURE_TYPE
2607 ,JE_BATCH_ID
2608 ,PARENT_INVOICE_ID
2609 ,PA_ADDITION_FLAG
2610 ,PA_QUANTITY
2611 ,POSTED_AMOUNT
2612 ,POSTED_BASE_AMOUNT
2613 ,PREPAY_AMOUNT_REMAINING
2614 ,PROJECT_ACCOUNTING_CONTEXT
2615 ,PROJECT_ID
2616 ,TASK_ID
2617 ,USSGL_TRANSACTION_CODE
2621 ,QUANTITY_VARIANCE
2618 ,USSGL_TRX_CODE_CONTEXT
2619 ,EARLIEST_SETTLEMENT_DATE
2620 ,REQ_DISTRIBUTION_ID
2622 ,BASE_QUANTITY_VARIANCE
2623 ,PACKET_ID
2624 ,AWT_FLAG
2625 ,AWT_GROUP_ID
2626 ,AWT_TAX_RATE_ID
2627 ,AWT_GROSS_AMOUNT
2631 ,REFERENCE_2
2628 ,AWT_INVOICE_ID
2629 ,AWT_ORIGIN_GROUP_ID
2630 ,REFERENCE_1
2632 ,ORG_ID
2633 ,OTHER_INVOICE_ID
2634 ,AWT_INVOICE_PAYMENT_ID
2635 ,GLOBAL_ATTRIBUTE_CATEGORY
2636 ,GLOBAL_ATTRIBUTE1
2637 ,GLOBAL_ATTRIBUTE2
2638 ,GLOBAL_ATTRIBUTE3
2639 ,GLOBAL_ATTRIBUTE4
2640 ,GLOBAL_ATTRIBUTE5
2641 ,GLOBAL_ATTRIBUTE6
2642 ,GLOBAL_ATTRIBUTE7
2643 ,GLOBAL_ATTRIBUTE8
2644 ,GLOBAL_ATTRIBUTE9
2645 ,GLOBAL_ATTRIBUTE10
2646 ,GLOBAL_ATTRIBUTE11
2647 ,GLOBAL_ATTRIBUTE12
2648 ,GLOBAL_ATTRIBUTE13
2649 ,GLOBAL_ATTRIBUTE14
2650 ,GLOBAL_ATTRIBUTE15
2651 ,GLOBAL_ATTRIBUTE16
2652 ,GLOBAL_ATTRIBUTE17
2653 ,GLOBAL_ATTRIBUTE18
2654 ,GLOBAL_ATTRIBUTE19
2655 ,GLOBAL_ATTRIBUTE20
2656 ,LINE_GROUP_NUMBER
2657 ,RECEIPT_VERIFIED_FLAG
2658 ,RECEIPT_REQUIRED_FLAG
2659 ,RECEIPT_MISSING_FLAG
2660 ,JUSTIFICATION
2661 ,EXPENSE_GROUP
2662 ,START_EXPENSE_DATE
2663 ,END_EXPENSE_DATE
2664 ,RECEIPT_CURRENCY_CODE
2665 ,RECEIPT_CONVERSION_RATE
2666 ,RECEIPT_CURRENCY_AMOUNT
2667 ,DAILY_AMOUNT
2668 ,WEB_PARAMETER_ID
2669 ,ADJUSTMENT_REASON
2670 ,AWARD_ID
2671 ,MRC_ACCRUAL_POSTED_FLAG
2672 ,MRC_CASH_POSTED_FLAG
2673 ,MRC_DIST_CODE_COMBINATION_ID
2674 ,MRC_AMOUNT
2675 ,MRC_BASE_AMOUNT
2676 ,MRC_BASE_INV_PRICE_VARIANCE
2677 ,MRC_EXCHANGE_RATE_VARIANCE
2678 ,MRC_POSTED_FLAG
2679 ,MRC_PROGRAM_APPLICATION_ID
2680 ,MRC_PROGRAM_ID
2681 ,MRC_PROGRAM_UPDATE_DATE
2682 ,MRC_RATE_VAR_CCID
2683 ,MRC_REQUEST_ID
2684 ,MRC_EXCHANGE_DATE
2685 ,MRC_EXCHANGE_RATE
2686 ,MRC_EXCHANGE_RATE_TYPE
2687 ,MRC_AMOUNT_TO_POST
2688 ,MRC_BASE_AMOUNT_TO_POST
2689 ,MRC_CASH_JE_BATCH_ID
2693 ,MRC_RECEIPT_CONVERSION_RATE
2690 ,MRC_JE_BATCH_ID
2691 ,MRC_POSTED_AMOUNT
2692 ,MRC_POSTED_BASE_AMOUNT
2694 ,CREDIT_CARD_TRX_ID
2695 ,DIST_MATCH_TYPE
2696 ,RCV_TRANSACTION_ID
2697 ,INVOICE_DISTRIBUTION_ID
2698 ,PARENT_REVERSAL_ID
2699 ,TAX_RECOVERABLE_FLAG
2700 ,PA_CC_AR_INVOICE_ID
2701 ,PA_CC_AR_INVOICE_LINE_NUM
2702 ,PA_CC_PROCESSED_CODE
2703 ,MERCHANT_DOCUMENT_NUMBER
2704 ,MERCHANT_NAME
2705 ,MERCHANT_REFERENCE
2706 ,MERCHANT_TAX_REG_NUMBER
2707 ,MERCHANT_TAXPAYER_ID
2708 ,COUNTRY_OF_SUPPLY
2709 ,MATCHED_UOM_LOOKUP_CODE
2710 ,GMS_BURDENABLE_RAW_COST
2711 ,ACCOUNTING_EVENT_ID
2712 ,PREPAY_DISTRIBUTION_ID
2713 ,UPGRADE_POSTED_AMT
2714 ,UPGRADE_BASE_POSTED_AMT
2715 ,INVENTORY_TRANSFER_STATUS
2716 ,COMPANY_PREPAID_INVOICE_ID
2717 ,CC_REVERSAL_FLAG
2718 ,AWT_WITHHELD_AMT
2719 ,INVOICE_INCLUDES_PREPAY_FLAG
2720 ,PRICE_CORRECT_INV_ID
2721 ,PRICE_CORRECT_QTY
2722 ,PA_CMT_XFACE_FLAG
2723 ,CANCELLATION_FLAG
2724 ,INVOICE_LINE_NUMBER
2725 ,CORRECTED_INVOICE_DIST_ID
2726 ,ROUNDING_AMT
2727 ,CHARGE_APPLICABLE_TO_DIST_ID
2728 ,CORRECTED_QUANTITY
2729 ,RELATED_ID
2730 ,ASSET_BOOK_TYPE_CODE
2731 ,ASSET_CATEGORY_ID
2732 ,DISTRIBUTION_CLASS
2733 ,FINAL_PAYMENT_ROUNDING
2734 ,FINAL_APPLICATION_ROUNDING
2735 ,AMOUNT_AT_PREPAY_XRATE
2736 ,CASH_BASIS_FINAL_APP_ROUNDING
2737 ,AMOUNT_AT_PREPAY_PAY_XRATE
2738 ,INTENDED_USE
2739 ,DETAIL_TAX_DIST_ID
2740 ,REC_NREC_RATE
2741 ,RECOVERY_RATE_ID
2742 ,RECOVERY_RATE_NAME
2743 ,RECOVERY_TYPE_CODE
2744 ,RECOVERY_RATE_CODE
2745 ,WITHHOLDING_TAX_CODE_ID
2746 ,TAX_ALREADY_DISTRIBUTED_FLAG
2747 ,SUMMARY_TAX_LINE_ID
2748 ,TAXABLE_AMOUNT
2749 ,TAXABLE_BASE_AMOUNT
2750 ,EXTRA_PO_ERV
2751 ,PREPAY_TAX_DIFF_AMOUNT
2752 ,TAX_CODE_ID
2753 ,VAT_CODE
2757 ,TAX_RECOVERY_OVERRIDE_FLAG
2754 ,AMOUNT_INCLUDES_TAX_FLAG
2755 ,TAX_CALCULATED_FLAG
2756 ,TAX_RECOVERY_RATE
2758 ,TAX_CODE_OVERRIDE_FLAG
2759 ,TOTAL_DIST_AMOUNT
2760 ,TOTAL_DIST_BASE_AMOUNT
2761 ,PREPAY_TAX_PARENT_ID
2762 ,CANCELLED_FLAG
2763 ,OLD_DISTRIBUTION_ID
2764 ,OLD_DIST_LINE_NUMBER
2765 ,AMOUNT_VARIANCE
2766 ,BASE_AMOUNT_VARIANCE
2767 ,HISTORICAL_FLAG
2768 ,RCV_CHARGE_ADDITION_FLAG
2769 ,AWT_RELATED_ID
2770 ,RELATED_RETAINAGE_DIST_ID
2771 ,RETAINED_AMOUNT_REMAINING
2772 ,BC_EVENT_ID
2773 ,RETAINED_INVOICE_DIST_ID
2774 ,FINAL_RELEASE_ROUNDING
2775 ,FULLY_PAID_ACCTD_FLAG
2776 ,ROOT_DISTRIBUTION_ID
2777 ,XINV_PARENT_REVERSAL_ID
2778 ,RECURRING_PAYMENT_ID
2779 ,RELEASE_INV_DIST_DERIVED_FROM
2780 ,PAY_AWT_GROUP_ID
2781 )
2782 VALUES
2783 (p_ap_invoice_dist_all_rec.ACCOUNTING_DATE
2784 ,p_ap_invoice_dist_all_rec.ACCRUAL_POSTED_FLAG
2785 ,p_ap_invoice_dist_all_rec.ASSETS_ADDITION_FLAG
2786 ,p_ap_invoice_dist_all_rec.ASSETS_TRACKING_FLAG
2787 ,p_ap_invoice_dist_all_rec.CASH_POSTED_FLAG
2788 ,p_ap_invoice_dist_all_rec.DISTRIBUTION_LINE_NUMBER
2789 ,p_ap_invoice_dist_all_rec.DIST_CODE_COMBINATION_ID
2790 ,p_ap_invoice_dist_all_rec.INVOICE_ID
2791 ,p_ap_invoice_dist_all_rec.LAST_UPDATED_BY
2792 ,p_ap_invoice_dist_all_rec.LAST_UPDATE_DATE
2793 ,p_ap_invoice_dist_all_rec.LINE_TYPE_LOOKUP_CODE
2794 ,p_ap_invoice_dist_all_rec.PERIOD_NAME
2795 ,p_ap_invoice_dist_all_rec.SET_OF_BOOKS_ID
2796 ,p_ap_invoice_dist_all_rec.ACCTS_PAY_CODE_COMBINATION_ID
2797 ,p_ap_invoice_dist_all_rec.AMOUNT
2798 ,p_ap_invoice_dist_all_rec.BASE_AMOUNT
2799 ,p_ap_invoice_dist_all_rec.BASE_INVOICE_PRICE_VARIANCE
2800 ,p_ap_invoice_dist_all_rec.BATCH_ID
2801 ,p_ap_invoice_dist_all_rec.CREATED_BY
2802 ,p_ap_invoice_dist_all_rec.CREATION_DATE
2803 ,p_ap_invoice_dist_all_rec.DESCRIPTION
2804 ,p_ap_invoice_dist_all_rec.EXCHANGE_RATE_VARIANCE
2805 ,p_ap_invoice_dist_all_rec.FINAL_MATCH_FLAG
2806 ,p_ap_invoice_dist_all_rec.INCOME_TAX_REGION
2807 ,p_ap_invoice_dist_all_rec.INVOICE_PRICE_VARIANCE
2808 ,p_ap_invoice_dist_all_rec.LAST_UPDATE_LOGIN
2809 ,p_ap_invoice_dist_all_rec.MATCH_STATUS_FLAG
2810 ,p_ap_invoice_dist_all_rec.POSTED_FLAG
2811 ,p_ap_invoice_dist_all_rec.PO_DISTRIBUTION_ID
2812 ,p_ap_invoice_dist_all_rec.PROGRAM_APPLICATION_ID
2813 ,p_ap_invoice_dist_all_rec.PROGRAM_ID
2814 ,p_ap_invoice_dist_all_rec.PROGRAM_UPDATE_DATE
2815 ,p_ap_invoice_dist_all_rec.QUANTITY_INVOICED
2816 ,p_ap_invoice_dist_all_rec.RATE_VAR_CODE_COMBINATION_ID
2817 ,p_ap_invoice_dist_all_rec.REQUEST_ID
2818 ,p_ap_invoice_dist_all_rec.REVERSAL_FLAG
2819 ,p_ap_invoice_dist_all_rec.TYPE_1099
2820 ,p_ap_invoice_dist_all_rec.UNIT_PRICE
2821 ,p_ap_invoice_dist_all_rec.AMOUNT_ENCUMBERED
2822 ,p_ap_invoice_dist_all_rec.BASE_AMOUNT_ENCUMBERED
2823 ,p_ap_invoice_dist_all_rec.ENCUMBERED_FLAG
2824 ,p_ap_invoice_dist_all_rec.EXCHANGE_DATE
2825 ,p_ap_invoice_dist_all_rec.EXCHANGE_RATE
2826 ,p_ap_invoice_dist_all_rec.EXCHANGE_RATE_TYPE
2827 ,p_ap_invoice_dist_all_rec.PRICE_ADJUSTMENT_FLAG
2828 ,p_ap_invoice_dist_all_rec.PRICE_VAR_CODE_COMBINATION_ID
2829 ,p_ap_invoice_dist_all_rec.QUANTITY_UNENCUMBERED
2830 ,p_ap_invoice_dist_all_rec.STAT_AMOUNT
2831 ,p_ap_invoice_dist_all_rec.AMOUNT_TO_POST
2832 ,p_ap_invoice_dist_all_rec.ATTRIBUTE1
2833 ,p_ap_invoice_dist_all_rec.ATTRIBUTE10
2834 ,p_ap_invoice_dist_all_rec.ATTRIBUTE11
2835 ,p_ap_invoice_dist_all_rec.ATTRIBUTE12
2836 ,p_ap_invoice_dist_all_rec.ATTRIBUTE13
2837 ,p_ap_invoice_dist_all_rec.ATTRIBUTE14
2838 ,p_ap_invoice_dist_all_rec.ATTRIBUTE15
2839 ,p_ap_invoice_dist_all_rec.ATTRIBUTE2
2840 ,p_ap_invoice_dist_all_rec.ATTRIBUTE3
2841 ,p_ap_invoice_dist_all_rec.ATTRIBUTE4
2842 ,p_ap_invoice_dist_all_rec.ATTRIBUTE5
2843 ,p_ap_invoice_dist_all_rec.ATTRIBUTE6
2844 ,p_ap_invoice_dist_all_rec.ATTRIBUTE7
2845 ,p_ap_invoice_dist_all_rec.ATTRIBUTE8
2846 ,p_ap_invoice_dist_all_rec.ATTRIBUTE9
2847 ,p_ap_invoice_dist_all_rec.ATTRIBUTE_CATEGORY
2848 ,p_ap_invoice_dist_all_rec.BASE_AMOUNT_TO_POST
2849 ,p_ap_invoice_dist_all_rec.CASH_JE_BATCH_ID
2850 ,p_ap_invoice_dist_all_rec.EXPENDITURE_ITEM_DATE
2851 ,p_ap_invoice_dist_all_rec.EXPENDITURE_ORGANIZATION_ID
2852 ,p_ap_invoice_dist_all_rec.EXPENDITURE_TYPE
2853 ,p_ap_invoice_dist_all_rec.JE_BATCH_ID
2854 ,p_ap_invoice_dist_all_rec.PARENT_INVOICE_ID
2855 ,p_ap_invoice_dist_all_rec.PA_ADDITION_FLAG
2856 ,p_ap_invoice_dist_all_rec.PA_QUANTITY
2857 ,p_ap_invoice_dist_all_rec.POSTED_AMOUNT
2858 ,p_ap_invoice_dist_all_rec.POSTED_BASE_AMOUNT
2859 ,p_ap_invoice_dist_all_rec.PREPAY_AMOUNT_REMAINING
2860 ,p_ap_invoice_dist_all_rec.PROJECT_ACCOUNTING_CONTEXT
2861 ,p_ap_invoice_dist_all_rec.PROJECT_ID
2862 ,p_ap_invoice_dist_all_rec.TASK_ID
2863 ,p_ap_invoice_dist_all_rec.USSGL_TRANSACTION_CODE
2864 ,p_ap_invoice_dist_all_rec.USSGL_TRX_CODE_CONTEXT
2865 ,p_ap_invoice_dist_all_rec.EARLIEST_SETTLEMENT_DATE
2866 ,p_ap_invoice_dist_all_rec.REQ_DISTRIBUTION_ID
2867 ,p_ap_invoice_dist_all_rec.QUANTITY_VARIANCE
2868 ,p_ap_invoice_dist_all_rec.BASE_QUANTITY_VARIANCE
2869 ,p_ap_invoice_dist_all_rec.PACKET_ID
2870 ,p_ap_invoice_dist_all_rec.AWT_FLAG
2871 ,p_ap_invoice_dist_all_rec.AWT_GROUP_ID
2872 ,p_ap_invoice_dist_all_rec.AWT_TAX_RATE_ID
2873 ,p_ap_invoice_dist_all_rec.AWT_GROSS_AMOUNT
2874 ,p_ap_invoice_dist_all_rec.AWT_INVOICE_ID
2875 ,p_ap_invoice_dist_all_rec.AWT_ORIGIN_GROUP_ID
2876 ,p_ap_invoice_dist_all_rec.REFERENCE_1
2877 ,p_ap_invoice_dist_all_rec.REFERENCE_2
2878 ,p_ap_invoice_dist_all_rec.ORG_ID
2879 ,p_ap_invoice_dist_all_rec.OTHER_INVOICE_ID
2880 ,p_ap_invoice_dist_all_rec.AWT_INVOICE_PAYMENT_ID
2881 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE_CATEGORY
2882 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE1
2883 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE2
2887 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE6
2884 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE3
2885 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE4
2886 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE5
2888 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE7
2889 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE8
2890 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE9
2891 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE10
2892 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE11
2893 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE12
2894 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE13
2895 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE14
2896 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE15
2897 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE16
2898 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE17
2899 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE18
2900 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE19
2901 ,p_ap_invoice_dist_all_rec.GLOBAL_ATTRIBUTE20
2902 ,p_ap_invoice_dist_all_rec.LINE_GROUP_NUMBER
2903 ,p_ap_invoice_dist_all_rec.RECEIPT_VERIFIED_FLAG
2904 ,p_ap_invoice_dist_all_rec.RECEIPT_REQUIRED_FLAG
2905 ,p_ap_invoice_dist_all_rec.RECEIPT_MISSING_FLAG
2906 ,p_ap_invoice_dist_all_rec.JUSTIFICATION
2907 ,p_ap_invoice_dist_all_rec.EXPENSE_GROUP
2908 ,p_ap_invoice_dist_all_rec.START_EXPENSE_DATE
2909 ,p_ap_invoice_dist_all_rec.END_EXPENSE_DATE
2910 ,p_ap_invoice_dist_all_rec.RECEIPT_CURRENCY_CODE
2911 ,p_ap_invoice_dist_all_rec.RECEIPT_CONVERSION_RATE
2912 ,p_ap_invoice_dist_all_rec.RECEIPT_CURRENCY_AMOUNT
2913 ,p_ap_invoice_dist_all_rec.DAILY_AMOUNT
2914 ,p_ap_invoice_dist_all_rec.WEB_PARAMETER_ID
2915 ,p_ap_invoice_dist_all_rec.ADJUSTMENT_REASON
2916 ,p_ap_invoice_dist_all_rec.AWARD_ID
2917 ,p_ap_invoice_dist_all_rec.MRC_ACCRUAL_POSTED_FLAG
2918 ,p_ap_invoice_dist_all_rec.MRC_CASH_POSTED_FLAG
2919 ,p_ap_invoice_dist_all_rec.MRC_DIST_CODE_COMBINATION_ID
2920 ,p_ap_invoice_dist_all_rec.MRC_AMOUNT
2921 ,p_ap_invoice_dist_all_rec.MRC_BASE_AMOUNT
2922 ,p_ap_invoice_dist_all_rec.MRC_BASE_INV_PRICE_VARIANCE
2923 ,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_RATE_VARIANCE
2924 ,p_ap_invoice_dist_all_rec.MRC_POSTED_FLAG
2925 ,p_ap_invoice_dist_all_rec.MRC_PROGRAM_APPLICATION_ID
2926 ,p_ap_invoice_dist_all_rec.MRC_PROGRAM_ID
2927 ,p_ap_invoice_dist_all_rec.MRC_PROGRAM_UPDATE_DATE
2928 ,p_ap_invoice_dist_all_rec.MRC_RATE_VAR_CCID
2929 ,p_ap_invoice_dist_all_rec.MRC_REQUEST_ID
2930 ,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_DATE
2931 ,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_RATE
2932 ,p_ap_invoice_dist_all_rec.MRC_EXCHANGE_RATE_TYPE
2933 ,p_ap_invoice_dist_all_rec.MRC_AMOUNT_TO_POST
2934 ,p_ap_invoice_dist_all_rec.MRC_BASE_AMOUNT_TO_POST
2935 ,p_ap_invoice_dist_all_rec.MRC_CASH_JE_BATCH_ID
2936 ,p_ap_invoice_dist_all_rec.MRC_JE_BATCH_ID
2937 ,p_ap_invoice_dist_all_rec.MRC_POSTED_AMOUNT
2938 ,p_ap_invoice_dist_all_rec.MRC_POSTED_BASE_AMOUNT
2939 ,p_ap_invoice_dist_all_rec.MRC_RECEIPT_CONVERSION_RATE
2940 ,p_ap_invoice_dist_all_rec.CREDIT_CARD_TRX_ID
2941 ,p_ap_invoice_dist_all_rec.DIST_MATCH_TYPE
2942 ,p_ap_invoice_dist_all_rec.RCV_TRANSACTION_ID
2943 ,ln_dist_id--p_ap_invoice_dist_all_rec.INVOICE_DISTRIBUTION_ID
2944 ,p_ap_invoice_dist_all_rec.PARENT_REVERSAL_ID
2945 ,p_ap_invoice_dist_all_rec.TAX_RECOVERABLE_FLAG
2946 ,p_ap_invoice_dist_all_rec.PA_CC_AR_INVOICE_ID
2947 ,p_ap_invoice_dist_all_rec.PA_CC_AR_INVOICE_LINE_NUM
2948 ,p_ap_invoice_dist_all_rec.PA_CC_PROCESSED_CODE
2949 ,p_ap_invoice_dist_all_rec.MERCHANT_DOCUMENT_NUMBER
2950 ,p_ap_invoice_dist_all_rec.MERCHANT_NAME
2951 ,p_ap_invoice_dist_all_rec.MERCHANT_REFERENCE
2952 ,p_ap_invoice_dist_all_rec.MERCHANT_TAX_REG_NUMBER
2953 ,p_ap_invoice_dist_all_rec.MERCHANT_TAXPAYER_ID
2954 ,p_ap_invoice_dist_all_rec.COUNTRY_OF_SUPPLY
2955 ,p_ap_invoice_dist_all_rec.MATCHED_UOM_LOOKUP_CODE
2956 ,p_ap_invoice_dist_all_rec.GMS_BURDENABLE_RAW_COST
2957 ,p_ap_invoice_dist_all_rec.ACCOUNTING_EVENT_ID
2958 ,p_ap_invoice_dist_all_rec.PREPAY_DISTRIBUTION_ID
2959 ,p_ap_invoice_dist_all_rec.UPGRADE_POSTED_AMT
2960 ,p_ap_invoice_dist_all_rec.UPGRADE_BASE_POSTED_AMT
2961 ,p_ap_invoice_dist_all_rec.INVENTORY_TRANSFER_STATUS
2962 ,p_ap_invoice_dist_all_rec.COMPANY_PREPAID_INVOICE_ID
2963 ,p_ap_invoice_dist_all_rec.CC_REVERSAL_FLAG
2964 ,p_ap_invoice_dist_all_rec.AWT_WITHHELD_AMT
2965 ,p_ap_invoice_dist_all_rec.INVOICE_INCLUDES_PREPAY_FLAG
2966 ,p_ap_invoice_dist_all_rec.PRICE_CORRECT_INV_ID
2967 ,p_ap_invoice_dist_all_rec.PRICE_CORRECT_QTY
2968 ,p_ap_invoice_dist_all_rec.PA_CMT_XFACE_FLAG
2969 ,p_ap_invoice_dist_all_rec.CANCELLATION_FLAG
2970 ,p_ap_invoice_dist_all_rec.INVOICE_LINE_NUMBER
2971 ,p_ap_invoice_dist_all_rec.CORRECTED_INVOICE_DIST_ID
2972 ,p_ap_invoice_dist_all_rec.ROUNDING_AMT
2973 ,p_ap_invoice_dist_all_rec.CHARGE_APPLICABLE_TO_DIST_ID
2974 ,p_ap_invoice_dist_all_rec.CORRECTED_QUANTITY
2975 ,p_ap_invoice_dist_all_rec.RELATED_ID
2976 ,p_ap_invoice_dist_all_rec.ASSET_BOOK_TYPE_CODE
2977 ,p_ap_invoice_dist_all_rec.ASSET_CATEGORY_ID
2978 ,p_ap_invoice_dist_all_rec.DISTRIBUTION_CLASS
2979 ,p_ap_invoice_dist_all_rec.FINAL_PAYMENT_ROUNDING
2980 ,p_ap_invoice_dist_all_rec.FINAL_APPLICATION_ROUNDING
2981 ,p_ap_invoice_dist_all_rec.AMOUNT_AT_PREPAY_XRATE
2982 ,p_ap_invoice_dist_all_rec.CASH_BASIS_FINAL_APP_ROUNDING
2983 ,p_ap_invoice_dist_all_rec.AMOUNT_AT_PREPAY_PAY_XRATE
2984 ,p_ap_invoice_dist_all_rec.INTENDED_USE
2985 ,p_ap_invoice_dist_all_rec.DETAIL_TAX_DIST_ID
2986 ,p_ap_invoice_dist_all_rec.REC_NREC_RATE
2987 ,p_ap_invoice_dist_all_rec.RECOVERY_RATE_ID
2988 ,p_ap_invoice_dist_all_rec.RECOVERY_RATE_NAME
2989 ,p_ap_invoice_dist_all_rec.RECOVERY_TYPE_CODE
2990 ,p_ap_invoice_dist_all_rec.RECOVERY_RATE_CODE
2991 ,p_ap_invoice_dist_all_rec.WITHHOLDING_TAX_CODE_ID
2992 ,p_ap_invoice_dist_all_rec.TAX_ALREADY_DISTRIBUTED_FLAG
2993 ,p_ap_invoice_dist_all_rec.SUMMARY_TAX_LINE_ID
2994 ,p_ap_invoice_dist_all_rec.TAXABLE_AMOUNT
2995 ,p_ap_invoice_dist_all_rec.TAXABLE_BASE_AMOUNT
2996 ,p_ap_invoice_dist_all_rec.EXTRA_PO_ERV
3002 ,p_ap_invoice_dist_all_rec.TAX_RECOVERY_RATE
2997 ,p_ap_invoice_dist_all_rec.PREPAY_TAX_DIFF_AMOUNT
2998 ,p_ap_invoice_dist_all_rec.TAX_CODE_ID
2999 ,p_ap_invoice_dist_all_rec.VAT_CODE
3000 ,p_ap_invoice_dist_all_rec.AMOUNT_INCLUDES_TAX_FLAG
3001 ,p_ap_invoice_dist_all_rec.TAX_CALCULATED_FLAG
3003 ,p_ap_invoice_dist_all_rec.TAX_RECOVERY_OVERRIDE_FLAG
3004 ,p_ap_invoice_dist_all_rec.TAX_CODE_OVERRIDE_FLAG
3005 ,p_ap_invoice_dist_all_rec.TOTAL_DIST_AMOUNT
3006 ,p_ap_invoice_dist_all_rec.TOTAL_DIST_BASE_AMOUNT
3007 ,p_ap_invoice_dist_all_rec.PREPAY_TAX_PARENT_ID
3008 ,p_ap_invoice_dist_all_rec.CANCELLED_FLAG
3009 ,p_ap_invoice_dist_all_rec.OLD_DISTRIBUTION_ID
3010 ,p_ap_invoice_dist_all_rec.OLD_DIST_LINE_NUMBER
3011 ,p_ap_invoice_dist_all_rec.AMOUNT_VARIANCE
3012 ,p_ap_invoice_dist_all_rec.BASE_AMOUNT_VARIANCE
3013 ,p_ap_invoice_dist_all_rec.HISTORICAL_FLAG
3014 ,p_ap_invoice_dist_all_rec.RCV_CHARGE_ADDITION_FLAG
3015 ,p_ap_invoice_dist_all_rec.AWT_RELATED_ID
3016 ,p_ap_invoice_dist_all_rec.RELATED_RETAINAGE_DIST_ID
3017 ,p_ap_invoice_dist_all_rec.RETAINED_AMOUNT_REMAINING
3018 ,p_ap_invoice_dist_all_rec.BC_EVENT_ID
3019 ,p_ap_invoice_dist_all_rec.RETAINED_INVOICE_DIST_ID
3020 ,p_ap_invoice_dist_all_rec.FINAL_RELEASE_ROUNDING
3021 ,p_ap_invoice_dist_all_rec.FULLY_PAID_ACCTD_FLAG
3022 ,p_ap_invoice_dist_all_rec.ROOT_DISTRIBUTION_ID
3023 ,p_ap_invoice_dist_all_rec.XINV_PARENT_REVERSAL_ID
3024 ,p_ap_invoice_dist_all_rec.RECURRING_PAYMENT_ID
3025 ,p_ap_invoice_dist_all_rec.RELEASE_INV_DIST_DERIVED_FROM
3026 ,p_ap_invoice_dist_all_rec.PAY_AWT_GROUP_ID
3027 );
3028
3029 RETURN ln_dist_id;
3030 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3031 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
3032 END IF;
3033 EXCEPTION
3034 WHEN OTHERS THEN
3035 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3036 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
3037 END IF;
3038 app_exception.raise_exception;
3039 END insert_ap_inv_dist;
3040
3041
3042 PROCEDURE get_match_type
3043 (
3044 pn_invoice_id IN NUMBER
3045 ,pn_invoice_line_num IN NUMBER
3046 ,pv_match_type OUT NOCOPY VARCHAR2
3047 ,pv_receipt_code OUT NOCOPY VARCHAR2
3048 ,pn_shipment_line_id OUT NOCOPY NUMBER
3049 ,pn_shipment_header_id OUT NOCOPY NUMBER
3050 ) IS
3051
3052
3053 CURSOR c_invoice_line IS
3054 SELECT *
3055 FROM ap_invoice_lines_all
3056 WHERE invoice_id = pn_invoice_id AND line_number = pn_invoice_line_num;
3057
3058 CURSOR c_invoice_source IS
3059 SELECT SOURCE FROM ap_invoices_all WHERE invoice_id = pn_invoice_id;
3060
3061 CURSOR vend_info IS
3062 SELECT vendor_id, vendor_site_id, invoice_type_lookup_code, cancelled_date
3063 FROM ap_invoices_all
3064 WHERE invoice_id = pn_invoice_id;
3065
3066 CURSOR fetch_rcv_record(v_rcv_trans_id NUMBER) IS
3067 SELECT po_header_id,po_line_id
3068 FROM rcv_transactions
3069 WHERE transaction_id = v_rcv_trans_id;
3070
3071 CURSOR fetch_tax_distribution(v_po_header_id NUMBER, v_po_line_id NUMBER, v_invoice_id NUMBER) IS
3072 SELECT COUNT(*)
3073 FROM JAI_AP_MATCH_INV_TAXES
3074 WHERE po_header_id = v_po_header_id
3075 AND po_line_id = v_po_line_id
3076 AND invoice_id = v_invoice_id;
3077
3078 CURSOR fetch_recpt_num_cur IS
3079 SELECT SUBSTR(SUBSTR(invoice_num, INSTR(invoice_num, '-', 1, 1) + 1 ,
3080 (INSTR(invoice_num, '-', 1, 2)-1) - INSTR(invoice_num, '-', 1, 1)
3081 ),1,30), Source
3082 FROM ap_invoices_all
3083 WHERE invoice_id = pn_invoice_id;
3084
3085 CURSOR count_receipts(p_pck_slip VARCHAR2, ven_id NUMBER, cpv_transaction_type VARCHAR2 ) IS
3086 SELECT DISTINCT rsh.receipt_num, rsh.shipment_header_id
3087 FROM rcv_shipment_headers rsh, rcv_transactions rt
3088 WHERE rsh.shipment_header_id = rt.shipment_header_id
3089 AND rsh.packing_slip = p_pck_slip
3090 AND rsh.vendor_id = ven_id
3091 AND rt.transaction_type = cpv_transaction_type;
3092
3093 CURSOR set_up_info(ven_id NUMBER, ven_site_id NUMBER, v_org_id NUMBER) IS
3094 SELECT pay_on_code, pay_on_receipt_summary_code
3095 FROM po_vendor_sites_all
3096 WHERE vendor_id = ven_id
3097 AND vendor_site_id = ven_site_id
3098 AND NVL(org_id, 0) = NVL(v_org_id, 0);
3099
3100 CURSOR fetch_inv_org_id_cur(c_po_dist_id NUMBER) IS
3101 SELECT Inventory_Organization_Id
3102 FROM Hr_Locations
3103 WHERE Location_Id = ( SELECT Ship_To_Location_Id
3104 FROM Po_Headers_All
3105 WHERE Po_Header_Id = ( SELECT Po_Header_Id
3106 FROM Po_Distributions_All
3107 WHERE Po_Distribution_Id = c_po_dist_id
3108 )
3109 );
3113 WHERE Shipment_Header_Id IN ( SELECT Shipment_Header_Id
3110 CURSOR Fetch_Shipment_Line_Id_Cur( invorg IN NUMBER, receiptnum IN VARCHAR2 ,c_po_dist_id NUMBER) IS
3111 SELECT Shipment_Line_Id, Shipment_Header_Id
3112 FROM Rcv_Shipment_Lines
3114 FROM Rcv_Shipment_Headers
3115 WHERE Receipt_Num = receiptnum )
3116 AND Po_Line_location_Id = ( SELECT Line_Location_Id
3117 FROM Po_Distributions_All
3118 WHERE Po_Distribution_Id = c_po_dist_id)
3119 AND To_Organization_Id = invorg;
3120
3121 CURSOR Fetch_Shipment_Count_Cur( invorg IN NUMBER,v_shipment_header_id NUMBER ) IS
3122 SELECT COUNT( Shipment_Line_Id )
3123 FROM Rcv_Shipment_Lines
3124 WHERE Shipment_Header_Id = v_shipment_header_id
3125 AND To_Organization_Id = invorg;
3126
3127 r_ap_inv_line_item ap_invoice_lines_all%ROWTYPE;
3128 count_receipts_rec count_receipts%ROWTYPE;
3129 vend_info_rec vend_info%ROWTYPE;
3130 fetch_rcv_record_rec fetch_rcv_record%ROWTYPE;
3131 set_up_info_rec set_up_info%ROWTYPE;
3132 v_receipt_num RCV_SHIPMENT_HEADERS.RECEIPT_NUM%TYPE;
3133 v_source ap_invoices_all.SOURCE%TYPE;
3134
3135 v_inv_org_id NUMBER;
3136 v_count_tax_dist NUMBER;
3137
3138 v_initial_count NUMBER := 0;
3139 v_count NUMBER := 0;
3140 l_api_name CONSTANT VARCHAR2(50) := 'get_match_type()';
3141 BEGIN
3142 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3143 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
3144 END IF;
3145
3146 OPEN c_invoice_line;
3147 FETCH c_invoice_line INTO r_ap_inv_line_item;
3148 CLOSE c_invoice_line;
3149
3150
3151 OPEN vend_info;
3152 FETCH vend_info INTO vend_info_rec;
3153 CLOSE vend_info;
3154
3155 OPEN fetch_rcv_record(r_ap_inv_line_item.rcv_transaction_id);
3156 FETCH fetch_rcv_record INTO fetch_rcv_record_rec;
3157 CLOSE fetch_rcv_record;
3158
3159 OPEN fetch_recpt_num_cur;
3160 FETCH fetch_recpt_num_cur INTO v_receipt_num, v_source;
3161 CLOSE fetch_recpt_num_cur;
3162
3163 OPEN fetch_tax_distribution(fetch_rcv_record_rec.po_header_id
3164 ,fetch_rcv_record_rec.po_line_id
3165 ,pn_invoice_id);
3166 FETCH fetch_tax_distribution INTO v_count_tax_dist;
3167 CLOSE fetch_tax_distribution;
3168
3169
3170 IF v_source IN ('ERS', 'ASBN') THEN
3171 OPEN set_up_info(vend_info_rec.vendor_id
3172 ,vend_info_rec.vendor_site_id
3173 ,r_ap_inv_line_item.org_id);
3174 FETCH set_up_info INTO set_up_info_rec;
3175 CLOSE set_up_info;
3176
3177 IF set_up_info_rec.pay_on_receipt_summary_code = 'RECEIPT' THEN
3178
3179 OPEN fetch_inv_org_id_cur(r_ap_inv_line_item.Po_Distribution_Id);
3180 FETCH fetch_inv_org_id_cur INTO v_inv_org_id;
3181 CLOSE fetch_inv_org_id_cur;
3182
3183 OPEN fetch_shipment_line_id_cur(v_inv_org_id
3184 ,v_receipt_num,r_ap_inv_line_item.Po_Distribution_Id);
3185 FETCH fetch_shipment_line_id_cur INTO pn_shipment_line_id, pn_shipment_header_id;
3186 CLOSE fetch_shipment_line_id_cur;
3187
3188 OPEN fetch_shipment_count_cur(v_inv_org_id,pn_shipment_header_id);
3189 FETCH fetch_shipment_count_cur INTO v_count;
3190 CLOSE fetch_shipment_count_cur;
3191
3192 pv_receipt_code := 'RECEIPT';
3193
3194 ELSIF set_up_info_rec.pay_on_receipt_summary_code = 'PACKING_SLIP' THEN
3195
3196
3197 FOR count_receipts_rec IN count_receipts(v_receipt_num,vend_info_rec.vendor_id,'RECEIVE') LOOP
3198
3199 OPEN fetch_inv_org_id_cur(r_ap_inv_line_item.Po_Distribution_Id);
3200 FETCH fetch_inv_org_id_cur INTO v_inv_org_id;
3201 CLOSE fetch_inv_org_id_cur;
3202
3203 OPEN fetch_shipment_line_id_cur(v_inv_org_id,count_receipts_rec.receipt_num,r_ap_inv_line_item.Po_Distribution_Id);
3204 FETCH fetch_shipment_line_id_cur INTO pn_shipment_line_id, pn_shipment_header_id;
3205 CLOSE fetch_shipment_line_id_cur;
3206
3207 OPEN fetch_shipment_count_cur(v_inv_org_id,pn_shipment_header_id);
3208 FETCH fetch_shipment_count_cur INTO v_initial_count;
3209 CLOSE fetch_shipment_count_cur;
3210
3211 v_count := v_count + v_initial_count;
3212
3213 END LOOP;
3214
3215 pv_receipt_code := 'PACKING_SLIP';
3216
3217 IF v_count = 0 THEN
3218 OPEN fetch_inv_org_id_cur(r_ap_inv_line_item.Po_Distribution_Id);
3219 FETCH fetch_inv_org_id_cur INTO v_inv_org_id;
3220 CLOSE fetch_inv_org_id_cur;
3221
3222 OPEN fetch_shipment_line_id_cur(v_inv_org_id ,v_receipt_num,r_ap_inv_line_item.Po_Distribution_Id);
3223 FETCH fetch_shipment_line_id_cur INTO pn_shipment_line_id, pn_shipment_header_id;
3224 CLOSE fetch_shipment_line_id_cur;
3225
3226 OPEN fetch_shipment_count_cur(v_inv_org_id,pn_shipment_header_id);
3227 FETCH fetch_shipment_count_cur INTO v_count;
3228 CLOSE fetch_shipment_count_cur;
3229
3230 pv_receipt_code := 'RECEIPT';
3231 END IF;
3232
3233 END IF; -- packing slip
3234
3235 END IF;
3236
3237 IF ( ( r_ap_inv_line_item.match_type = 'ITEM_TO_RECEIPT' AND
3238 r_ap_inv_line_item.rcv_transaction_id IS NOT NULL AND
3239 v_source in ('ERS','ASBN','SUPPLEMENT') )
3240 OR
3241 ( r_ap_inv_line_item.match_type = 'ITEM_TO_PO'
3242 AND
3243 ( ( v_source in ('SUPPLEMENT') AND
3244 r_ap_inv_line_item.po_distribution_id IS NOT NULL )
3245 or
3249 )
3246 v_source='ERS'
3247 or
3248 v_source ='ASBN' )
3250 ) and
3251 r_ap_inv_line_item.line_type_lookup_code in ('ITEM', 'ACCRUAL') AND
3252 r_ap_inv_line_item.amount >= 0 and
3253 v_count_tax_dist = 0 THEN
3254 pv_match_type := 'PAY_ON_RECEIPT';
3255
3256 ELSIF ((r_ap_inv_line_item.po_distribution_id IS NOT NULL) AND
3257 (r_ap_inv_line_item.line_type_lookup_code IN ('ITEM', 'ACCRUAL')) AND
3258 (r_ap_inv_line_item.amount >= 0) AND v_count_tax_dist > 0 AND
3259 v_source IN ('ERS', 'ASBN')) OR (v_source = 'SUPPLEMENT')
3260
3261 THEN
3262 pv_match_type := 'RCV_MATCHING';
3263
3264 END IF;
3265
3266 IF ((r_ap_inv_line_item.match_type = 'ITEM_TO_RECEIPT') AND
3267 (r_ap_inv_line_item.line_type_lookup_code IN ('ITEM', 'ACCRUAL')) AND
3268 (r_ap_inv_line_item.amount >= 0) AND
3269 (r_ap_inv_line_item.po_distribution_id IS NOT NULL) AND
3270 v_source NOT IN ('ERS', 'ASBN')) THEN
3271
3272 pv_match_type := 'RCV_MATCHING';
3273
3274 ELSIF ((r_ap_inv_line_item.match_type = 'ITEM_TO_PO') AND
3275 (r_ap_inv_line_item.line_type_lookup_code IN ('ITEM', 'ACCRUAL')) AND
3276 (r_ap_inv_line_item.amount >= 0) AND
3277 (r_ap_inv_line_item.po_distribution_id IS NOT NULL OR
3278 r_ap_inv_line_item.po_header_id IS NOT NULL) AND
3279 (v_source NOT IN ('ERS', 'ASBN'))) THEN
3280 pv_match_type := 'PO_MATCHING';
3281
3282 ELSIF ((r_ap_inv_line_item.po_distribution_id IS NOT NULL) AND
3283 (r_ap_inv_line_item.line_type_lookup_code IN ('ITEM', 'ACCRUAL')) AND
3284 (r_ap_inv_line_item.amount < 0) AND
3285 (vend_info_rec.invoice_type_lookup_code = 'DEBIT') AND
3286 (vend_info_rec.cancelled_date IS NULL)) THEN
3287 IF v_source = 'RTS' THEN
3288 pv_receipt_code := 'RECEIPT';
3289 pv_match_type := 'PAY_ON_RECEIPT';
3290
3291 ELSIF v_source = 'Manual Invoice Entry' THEN
3292 pv_match_type := NULL;
3293 IF (r_ap_inv_line_item.match_type = 'ITEM_TO_PO') THEN
3294 IF r_ap_inv_line_item.rcv_transaction_id IS NOT NULL THEN
3295 pv_match_type := 'RCV_MATCHING';
3296 ELSE
3297 pv_match_type := 'PO_MATCHING';
3298 END IF;
3299 ELSIF r_ap_inv_line_item.match_type = 'ITEM_TO_RECEIPT' THEN
3300 pv_match_type := 'RCV_MATCHING';
3301 END IF;
3302
3303 END IF;
3304 ELSIF (r_ap_inv_line_item.line_type_lookup_code = 'RETROITEM' AND
3305 v_source = 'PPA' AND
3306 r_ap_inv_line_item.match_type = 'PO_PRICE_ADJUSTMENT') THEN
3307 IF (r_ap_inv_line_item.rcv_transaction_id IS NULL) THEN
3308 pv_match_type := 'PO_MATCHING';
3309 ELSE
3310 pv_match_type := 'RCV_MATCHING';
3311 END IF;
3312
3313 END IF; -- ITEM_TO_PO
3317 END IF;
3314
3315 IF pv_match_type = 'PAY_ON_RECEIPT' AND r_ap_inv_line_item.rcv_transaction_id IS NOT NULL THEN
3316 pv_match_type := 'RCV_MATCHING';
3318 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3319 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
3320 END IF;
3321 EXCEPTION
3322 WHEN OTHERS THEN
3323 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3324 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
3325 END IF;
3326 app_exception.raise_exception;
3327 END get_match_type;
3328 FUNCTION apportion_tax_4_price_cor_inv
3329 ( p_inv_id NUMBER
3330 ,p_invoice_line_num NUMBER
3331 ,p_tax_amount NUMBER
3332 ,p_tax_id NUMBER
3333 ) RETURN NUMBER IS
3334
3335 -- get the corresponding base line amount in original invoice
3336 CURSOR c_check_orig_item_line(p_price_correct_inv_id NUMBER, p_po_distribution_id NUMBER) IS
3337 SELECT amount, invoice_distribution_id
3338 FROM ap_invoice_distributions_all
3339 WHERE invoice_id = p_price_correct_inv_id AND
3340 po_distribution_id = p_po_distribution_id AND
3341 line_type_lookup_code = 'ITEM';
3342
3343 -- get the corresponding tax lin amount in the original invoice
3344 CURSOR c_get_orig_tax_line(p_orig_invoice_id NUMBER, p_orig_invoice_dist_id NUMBER, p_tax_id NUMBER) IS
3345
3346 SELECT SUM(jam.tax_amount) tax_amount
3347 FROM jai_ap_match_inv_taxes jam, jai_cmn_taxes_all jct
3348 WHERE p_orig_invoice_id = p_orig_invoice_id AND
3349 parent_invoice_distribution_id = p_orig_invoice_dist_id AND
3350 jam.tax_id = p_tax_id
3351 AND jam.tax_id = jct.tax_id AND
3352 (nvl(jct.adhoc_flag,'N') = 'Y' OR
3356 c_check_orig_item_line_rec c_check_orig_item_line%ROWTYPE;
3353 (jct.adhoc_flag = 'Y' AND
3354 jct.tax_type NOT IN ('Freight', 'Insurance', 'Octrai', 'Other','PURCHASE TAX', 'ENTRY TAX')));
3355
3357 c_get_orig_tax_line_rec c_get_orig_tax_line%ROWTYPE;
3358 v_amount NUMBER;
3359 cur_price_correct_inv_id NUMBER(15);
3360 cur_po_distribution_id NUMBER(15);
3361 cur_amount NUMBER;
3362 cur_quantity_invoiced NUMBER;
3363 v_count NUMBER;
3364 BEGIN
3365
3366 fnd_file.put_line(fnd_file.log
3367 ,'Start of function apportion_tax_4_price_cor_inv');
3368
3369 BEGIN
3370 EXECUTE IMMEDIATE 'select /* price_correct_inv_id */, po_distribution_id, amount, quantity_invoiced
3371 from ap_invoice_lines_all
3372 where invoice_id = :inv_id
3373 and line_number = :inv_line_num'
3374 INTO cur_price_correct_inv_id, cur_po_distribution_id, cur_amount, cur_quantity_invoiced
3375 USING p_inv_id, p_invoice_line_num;
3376
3377 EXCEPTION
3378 WHEN OTHERS THEN
3379 RETURN p_tax_amount;
3380 END;
3381
3382 -- control comes here when it is a case of price correction
3383 OPEN c_check_orig_item_line(cur_price_correct_inv_id
3384 ,cur_po_distribution_id);
3385 FETCH c_check_orig_item_line
3386 INTO c_check_orig_item_line_rec;
3387 CLOSE c_check_orig_item_line;
3388
3389 OPEN c_get_orig_tax_line(cur_price_correct_inv_id
3390 ,c_check_orig_item_line_rec.invoice_distribution_id
3391 ,p_tax_id);
3392 FETCH c_get_orig_tax_line
3393 INTO c_get_orig_tax_line_rec;
3394 CLOSE c_get_orig_tax_line;
3395
3396 IF c_check_orig_item_line_rec.amount = -1 THEN
3397 --Modified by kunkumar for 5593895
3398 fnd_file.put_line(fnd_file.log
3399 ,'Original item line has -1 amount, cannot apportion tax'); --Modified by kunkumar for 5593895
3400 RETURN p_tax_amount;
3401 END IF;
3402
3403 v_amount := (c_get_orig_tax_line_rec.tax_amount /
3404 c_check_orig_item_line_rec.amount) * cur_amount;
3405
3406 fnd_file.put_line(fnd_file.log
3407 ,' original amount / tax :' ||
3408 c_check_orig_item_line_rec.amount || '/' ||
3409 c_get_orig_tax_line_rec.tax_amount);
3410
3411 RETURN round(v_amount
3412 ,2);
3413
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 fnd_file.put_line(fnd_file.log
3417 ,'Exception function apportion_tax_4_price_cor_inv :' ||
3418 SQLERRM);
3419 RETURN p_tax_amount;
3420 END apportion_tax_4_price_cor_inv;
3421
3422 FUNCTION tax_apportion_convert
3423 (
3424 pn_invoice_id NUMBER
3425 ,pn_invoice_line_num NUMBER
3426 ,pv_adhoc_flag VARCHAR2
3427 ,pv_vat_flag VARCHAR2
3428 ,pv_tax_type VARCHAR2
3429 ,pn_tax_id NUMBER
3430 ,pn_tax_amount NUMBER
3431 ) RETURN NUMBER IS
3432 ln_apportn_factor_for_adhoc NUMBER;
3433 ln_apported_tax_amount NUMBER;
3434 l_api_name CONSTANT VARCHAR2(30) := 'tax_apportion_convert';
3435 BEGIN
3436 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3437 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
3438 END IF;
3439
3440 IF nvl(pv_adhoc_flag,'N') = 'Y' THEN
3441
3442 IF pv_tax_type IN ('Freight', 'Insurance', 'Octrai', 'Other', 'PURCHASE TAX', 'ENTRY TAX') AND
3443 pv_vat_flag = 'Q' THEN
3444 ln_apportn_factor_for_adhoc := jai_ap_utils_pkg.get_apportion_factor(pn_invoice_id
3445 ,pn_invoice_line_num
3446 ,'QUANTITY');
3447 ln_apported_tax_amount := pn_tax_amount * ln_apportn_factor_for_adhoc;
3448
3449 ELSIF pv_tax_type NOT IN ('Freight', 'Insurance', 'Octrai', 'Other','PURCHASE TAX', 'ENTRY TAX') THEN
3450 ln_apported_tax_amount := pn_tax_amount * jai_ap_utils_pkg.get_apportion_factor(pn_invoice_id
3451 ,pn_invoice_line_num);
3452 ELSE
3453 ln_apported_tax_amount := pn_tax_amount;
3454 END IF;
3455 ELSE
3456 ln_apported_tax_amount := pn_tax_amount * jai_ap_utils_pkg.get_apportion_factor(pn_invoice_id
3457 ,pn_invoice_line_num);
3458 END IF;
3459
3460
3461 /* IF pr_tax_line_rec.currency <> for_org_id_rec.invoice_currency_code THEN
3465 ln_apported_tax_amount := apportion_tax_4_price_cor_inv(pn_invoice_id,pn_invoice_line_num,ln_apported_tax_amount,pn_tax_id);
3462 v_tax_amount := v_tax_amount / for_org_id_rec.exchange_rate;
3463 END IF;*/
3464 IF nvl(ln_apported_tax_amount,-1) = -1 THEN
3466 END IF;
3467 ln_apported_tax_amount := nvl(ln_apported_tax_amount,-1);
3468
3469 RETURN ln_apported_tax_amount;
3470 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3471 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
3472 END IF;
3473 EXCEPTION
3474 WHEN OTHERS THEN
3475 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3476 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
3477 END IF;
3478 app_exception.raise_exception;
3479
3480 END tax_apportion_convert;
3481
3482
3483 PROCEDURE get_org_loc_id
3484 (
3485 pn_rcv_trx_id IN NUMBER
3486 ,pn_po_dist_id IN NUMBER
3487 ,pv_match_type IN VARCHAR2
3488 ,pn_organization_id OUT NOCOPY NUMBER
3489 ,pn_location_id OUT NOCOPY NUMBER
3490 ) IS
3491 CURSOR c_rcv_organization_id IS
3492 SELECT organization_id, location_id
3493 FROM rcv_transactions
3494 WHERE transaction_id = pn_rcv_trx_id;
3495 CURSOR c_po_organization_id IS
3496 SELECT ship_to_organization_id, ship_to_location_id
3497 FROM po_line_locations_all plla, po_distributions_all pod
3498 WHERE plla.line_location_id = pod.line_location_id AND
3499 pod.po_distribution_id = pn_po_dist_id;
3500 l_api_name CONSTANT VARCHAR2(30) := 'get_org_loc_id';
3501 BEGIN
3502 IF pv_match_type = 'ITEM_TO_PO' THEN
3503 OPEN c_po_organization_id;
3504 FETCH c_po_organization_id
3505 INTO pn_organization_id, pn_location_id;
3506 CLOSE c_po_organization_id;
3507 ELSIF pv_match_type = 'ITEM_TO_RECEIPT' THEN
3508 OPEN c_rcv_organization_id;
3509 FETCH c_rcv_organization_id
3510 INTO pn_organization_id, pn_location_id;
3511 CLOSE c_rcv_organization_id;
3512 END IF;
3513 IF ( G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3514 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name);
3515 END IF;
3516 EXCEPTION
3517 WHEN OTHERS THEN
3518 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
3519 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,SQLERRM);
3520 END IF;
3521 app_exception.raise_exception;
3522
3523
3524 END get_org_loc_id;
3525
3526 procedure update_ap_inv_line(p_invoice_id number,
3527 p_invoice_line_number number) is
3528 begin
3529 Update ap_invoice_lines_all
3530 set discarded_flag = 'Y',
3531 amount = 0,
3532 last_update_date = sysdate,
3533 last_updated_by = -1
3534 Where invoice_id = p_invoice_id
3535 and line_number = p_invoice_line_number
3536 and line_type_lookup_code = 'MISCELLANEOUS';
3537 end update_ap_inv_line;
3538
3539
3540 PROCEDURE process_discard(pr_ap_invoice_line_rec AP_INVOICE_LINES_ALL%rowtype) is
3541
3542 Cursor c_match_tax_lines(p_invoice_id number,
3543 p_invoice_line_number number,
3544 p_po_dist_id number) is
3545 select la.invoice_id,
3546 la.po_distribution_id,
3547 da.invoice_distribution_id,
3548 la.rcv_transaction_id,
3549 la.line_number invoice_line_number
3550 from ap_invoice_lines_all la, ap_invoice_distributions_all da
3551 where la.invoice_id = p_invoice_id
3552 and la.invoice_id = da.invoice_id
3553 and la.line_number = da.invoice_line_number
3554 and la.line_type_lookup_code = 'MISCELLANEOUS'
3555 and la.amount < 0
3556 and la.reference_key1 is not null
3557 and la.reference_key2 = p_invoice_line_number
3558 union all
3559 select invoice_id,
3560 po_distribution_id,
3561 invoice_distribution_id,
3562 rcv_transaction_id,
3563 invoice_line_number
3564 from jai_ap_match_inv_taxes
3565 where line_type_lookup_code = 'MISCELLANEOUS'
3566 and invoice_id = p_invoice_id
3567 and parent_invoice_line_number = p_invoice_line_number
3568 and invoice_line_number <> p_invoice_line_number
3569 AND tax_amount <> 0
3570 and po_distribution_id = p_po_dist_id;
3571
3572 Cursor c_inv_dist_tax_lines(ln_invoice_id number,
3573 ln_invoice_distribution_id number) is
3574 select accounting_date,
3575 accrual_posted_flag,
3576 assets_addition_flag,
3577 assets_tracking_flag,
3578 cash_posted_flag,
3579 distribution_line_number,
3580 dist_code_combination_id,
3581 invoice_id,
3582 line_type_lookup_code,
3583 period_name,
3584 set_of_books_id,
3585 amount,
3586 base_amount,
3587 batch_id,
3588 description,
3589 exchange_rate_variance,
3590 last_update_login,
3591 match_status_flag,
3592 posted_flag,
3593 rate_var_code_combination_id,
3594 reversal_flag,
3595 program_application_id,
3596 program_id,
3597 program_update_date,
3598 accts_pay_code_combination_id,
3599 invoice_distribution_id,
3600 quantity_invoiced,
3601 po_distribution_id,
3602 rcv_transaction_id,
3606 matched_uom_lookup_code,
3603 price_var_code_combination_id,
3604 invoice_price_variance,
3605 base_invoice_price_variance,
3607 invoice_line_number,
3608 org_id,
3609 charge_applicable_to_dist_id,
3610 project_id,
3611 task_id,
3612 expenditure_type,
3613 expenditure_item_date,
3614 expenditure_organization_id,
3615 project_accounting_context,
3616 pa_addition_flag,
3617 distribution_class,
3618 dist_match_type
3619 from ap_invoice_distributions_all
3620 where invoice_id = ln_invoice_id
3621 and invoice_distribution_id = ln_invoice_distribution_id;
3622
3623 Cursor c_inv_dist_tax_lines_ers(ln_invoice_id number,
3624 ln_po_distribution_id number) is
3625 select accounting_date,
3629 cash_posted_flag,
3626 accrual_posted_flag,
3627 assets_addition_flag,
3628 assets_tracking_flag,
3630 distribution_line_number,
3631 dist_code_combination_id,
3632 invoice_id,
3633 line_type_lookup_code,
3634 period_name,
3635 set_of_books_id,
3636 amount,
3637 base_amount,
3638 batch_id,
3639 description,
3640 exchange_rate_variance,
3641 last_update_login,
3642 match_status_flag,
3643 posted_flag,
3644 rate_var_code_combination_id,
3645 reversal_flag,
3646 program_application_id,
3647 program_id,
3648 program_update_date,
3649 accts_pay_code_combination_id,
3650 invoice_distribution_id,
3651 quantity_invoiced,
3652 po_distribution_id,
3653 rcv_transaction_id,
3654 price_var_code_combination_id,
3655 invoice_price_variance,
3656 base_invoice_price_variance,
3657 matched_uom_lookup_code,
3658 invoice_line_number,
3659 org_id,
3660 charge_applicable_to_dist_id,
3661 project_id,
3662 task_id,
3663 expenditure_type,
3664 expenditure_item_date,
3665 expenditure_organization_id,
3666 project_accounting_context,
3667 pa_addition_flag,
3668 distribution_class,
3669 dist_match_type
3670 from ap_invoice_distributions_all
3671 where invoice_id = ln_invoice_id
3672 and po_distribution_id = ln_po_distribution_id
3673 and line_type_lookup_code = 'MISCELLANEOUS';
3674
3675 cursor c_get_source(cp_invoice_id ap_invoices_all.invoice_id%type) is
3676 select source from ap_invoices_all where invoice_id = cp_invoice_id;
3677
3678 ln_invoice_id ap_invoice_lines_all.invoice_id%type;
3679 ln_po_dist_id ap_invoice_lines_all.po_distribution_id%type;
3680 lv_discard_flag ap_invoice_lines_all.discarded_flag%type;
3681 ln_line_number ap_invoice_lines_all.line_number%type;
3682 lv_source ap_invoices_all.source%type;
3683 lc_inv_dist ap_invoice_distributions_all%rowtype;
3684 ln_inv_dist_id ap_invoice_distributions_all.invoice_distribution_id%type;
3685
3686 BEGIN
3687 ln_invoice_id := pr_ap_invoice_line_rec.invoice_id;
3688 ln_po_dist_id := pr_ap_invoice_line_rec.po_distribution_id;
3689 lv_discard_flag := pr_ap_invoice_line_rec.discarded_flag;
3690 ln_line_number := pr_ap_invoice_line_rec.line_number;
3691
3692 open c_get_source(ln_invoice_id);
3693 fetch c_get_source
3694 into lv_source;
3695 close c_get_source;
3696
3697 If lv_discard_flag = 'Y' and lv_source <> 'ERS' then
3698 For ident_rec in c_match_tax_lines(ln_invoice_id,
3699 ln_line_number,
3700 ln_po_dist_id) Loop
3701
3702 For ins_reverse_dist_lines in c_inv_dist_tax_lines(ident_rec.invoice_id,
3703 ident_rec.invoice_distribution_id) Loop
3704 --INSERT INTO ap_invoice_distributions_all
3705 lc_inv_dist := null;
3706 lc_inv_dist.accounting_date := ins_reverse_dist_lines.accounting_date;
3707 lc_inv_dist.accrual_posted_flag := ins_reverse_dist_lines.accrual_posted_flag;
3708 lc_inv_dist.assets_addition_flag := ins_reverse_dist_lines.assets_addition_flag;
3709 lc_inv_dist.assets_tracking_flag := ins_reverse_dist_lines.assets_tracking_flag;
3710 lc_inv_dist.cash_posted_flag := ins_reverse_dist_lines.cash_posted_flag;
3711 lc_inv_dist.distribution_line_number := ins_reverse_dist_lines.distribution_line_number + 1;
3712 lc_inv_dist.dist_code_combination_id := ins_reverse_dist_lines.dist_code_combination_id;
3716 lc_inv_dist.set_of_books_id := ins_reverse_dist_lines.set_of_books_id;
3713 lc_inv_dist.invoice_id := ins_reverse_dist_lines.invoice_id;
3714 lc_inv_dist.line_type_lookup_code := ins_reverse_dist_lines.line_type_lookup_code;
3715 lc_inv_dist.period_name := ins_reverse_dist_lines.period_name;
3717 lc_inv_dist.amount := -ins_reverse_dist_lines.amount;
3718 lc_inv_dist.base_amount := ins_reverse_dist_lines.base_amount;
3719 lc_inv_dist.batch_id := ins_reverse_dist_lines.batch_id;
3720 lc_inv_dist.description := ins_reverse_dist_lines.description;
3721 lc_inv_dist.exchange_rate_variance := ins_reverse_dist_lines.exchange_rate_variance;
3722 lc_inv_dist.last_update_login := ins_reverse_dist_lines.last_update_login;
3723 lc_inv_dist.match_status_flag := ins_reverse_dist_lines.match_status_flag;
3724 lc_inv_dist.posted_flag := ins_reverse_dist_lines.posted_flag;
3725 lc_inv_dist.rate_var_code_combination_id := ins_reverse_dist_lines.rate_var_code_combination_id;
3726 lc_inv_dist.reversal_flag := ins_reverse_dist_lines.reversal_flag;
3727 lc_inv_dist.program_application_id := ins_reverse_dist_lines.program_application_id;
3728 lc_inv_dist.program_id := ins_reverse_dist_lines.program_id;
3729 lc_inv_dist.program_update_date := ins_reverse_dist_lines.program_update_date;
3730 lc_inv_dist.accts_pay_code_combination_id := ins_reverse_dist_lines.accts_pay_code_combination_id;
3731 lc_inv_dist.invoice_distribution_id := ins_reverse_dist_lines.invoice_distribution_id;
3732 lc_inv_dist.quantity_invoiced := ins_reverse_dist_lines.quantity_invoiced;
3733 lc_inv_dist.po_distribution_id := ins_reverse_dist_lines.po_distribution_id;
3734 lc_inv_dist.rcv_transaction_id := ins_reverse_dist_lines.rcv_transaction_id;
3735 lc_inv_dist.price_var_code_combination_id := ins_reverse_dist_lines.price_var_code_combination_id;
3736 lc_inv_dist.invoice_price_variance := ins_reverse_dist_lines.invoice_price_variance;
3737 lc_inv_dist.base_invoice_price_variance := ins_reverse_dist_lines.base_invoice_price_variance;
3738 lc_inv_dist.matched_uom_lookup_code := ins_reverse_dist_lines.matched_uom_lookup_code;
3739 lc_inv_dist.invoice_line_number := ins_reverse_dist_lines.invoice_line_number;
3740 lc_inv_dist.org_id := ins_reverse_dist_lines.org_id;
3741 lc_inv_dist.charge_applicable_to_dist_id := ins_reverse_dist_lines.charge_applicable_to_dist_id;
3742 lc_inv_dist.project_id := ins_reverse_dist_lines.project_id;
3743 lc_inv_dist.task_id := ins_reverse_dist_lines.task_id;
3744 lc_inv_dist.expenditure_type := ins_reverse_dist_lines.expenditure_type;
3745 lc_inv_dist.expenditure_item_date := ins_reverse_dist_lines.expenditure_item_date;
3746 lc_inv_dist.expenditure_organization_id := ins_reverse_dist_lines.expenditure_organization_id;
3747 lc_inv_dist.project_accounting_context := ins_reverse_dist_lines.project_accounting_context;
3748 lc_inv_dist.pa_addition_flag := ins_reverse_dist_lines.pa_addition_flag;
3749 lc_inv_dist.distribution_class := ins_reverse_dist_lines.distribution_class;
3750 lc_inv_dist.dist_match_type := ins_reverse_dist_lines.dist_match_type;
3751 lc_inv_dist.Last_Updated_By := -1;
3752 lc_inv_dist.Last_Update_date := sysdate;
3753 lc_inv_dist.match_status_flag := 'N';
3754 lc_inv_dist.reversal_flag := 'Y';
3755 lc_inv_dist.program_application_id := -1;
3756 lc_inv_dist.program_id := -1;
3757 lc_inv_dist.program_update_date := sysdate;
3758 lc_inv_dist.quantity_invoiced := null;
3759
3760 ln_inv_dist_id := jai_ap_match_tax_proc_pkg.insert_ap_inv_dist(lc_inv_dist);
3761 End Loop;
3762
3763 --Update ap_invoice_lines_all
3764 jai_ap_match_tax_proc_pkg.update_ap_inv_line(ident_rec.invoice_id,
3765 ident_rec.invoice_line_number);
3766 Commit;
3767 End Loop;
3768 Elsif lv_discard_flag = 'Y' and lv_source = 'ERS' Then
3769 For ins_reverse_dist_lines_ers in c_inv_dist_tax_lines_ers(ln_invoice_id,
3770 ln_po_dist_id) Loop
3771 --INSERT INTO ap_invoice_distributions_all
3772 lc_inv_dist := null;
3773 lc_inv_dist.accounting_date := ins_reverse_dist_lines_ers.accounting_date;
3774 lc_inv_dist.accrual_posted_flag := ins_reverse_dist_lines_ers.accrual_posted_flag;
3775 lc_inv_dist.assets_addition_flag := ins_reverse_dist_lines_ers.assets_addition_flag;
3776 lc_inv_dist.assets_tracking_flag := ins_reverse_dist_lines_ers.assets_tracking_flag;
3777 lc_inv_dist.cash_posted_flag := ins_reverse_dist_lines_ers.cash_posted_flag;
3778 lc_inv_dist.distribution_line_number := ins_reverse_dist_lines_ers.distribution_line_number + 1;
3779 lc_inv_dist.dist_code_combination_id := ins_reverse_dist_lines_ers.dist_code_combination_id;
3780 lc_inv_dist.invoice_id := ins_reverse_dist_lines_ers.invoice_id;
3781 lc_inv_dist.line_type_lookup_code := ins_reverse_dist_lines_ers.line_type_lookup_code;
3782 lc_inv_dist.period_name := ins_reverse_dist_lines_ers.period_name;
3786 lc_inv_dist.batch_id := ins_reverse_dist_lines_ers.batch_id;
3783 lc_inv_dist.set_of_books_id := ins_reverse_dist_lines_ers.set_of_books_id;
3784 lc_inv_dist.amount := -ins_reverse_dist_lines_ers.amount;
3785 lc_inv_dist.base_amount := ins_reverse_dist_lines_ers.base_amount;
3787 lc_inv_dist.description := ins_reverse_dist_lines_ers.description;
3788 lc_inv_dist.exchange_rate_variance := ins_reverse_dist_lines_ers.exchange_rate_variance;
3789 lc_inv_dist.last_update_login := ins_reverse_dist_lines_ers.last_update_login;
3790 lc_inv_dist.match_status_flag := ins_reverse_dist_lines_ers.match_status_flag;
3791 lc_inv_dist.posted_flag := ins_reverse_dist_lines_ers.posted_flag;
3792 lc_inv_dist.rate_var_code_combination_id := ins_reverse_dist_lines_ers.rate_var_code_combination_id;
3793 lc_inv_dist.reversal_flag := ins_reverse_dist_lines_ers.reversal_flag;
3794 lc_inv_dist.program_application_id := ins_reverse_dist_lines_ers.program_application_id;
3795 lc_inv_dist.program_id := ins_reverse_dist_lines_ers.program_id;
3796 lc_inv_dist.program_update_date := ins_reverse_dist_lines_ers.program_update_date;
3797 lc_inv_dist.accts_pay_code_combination_id := ins_reverse_dist_lines_ers.accts_pay_code_combination_id;
3798 lc_inv_dist.invoice_distribution_id := ins_reverse_dist_lines_ers.invoice_distribution_id;
3799 lc_inv_dist.quantity_invoiced := ins_reverse_dist_lines_ers.quantity_invoiced;
3800 lc_inv_dist.po_distribution_id := ins_reverse_dist_lines_ers.po_distribution_id;
3801 lc_inv_dist.rcv_transaction_id := ins_reverse_dist_lines_ers.rcv_transaction_id;
3802 lc_inv_dist.price_var_code_combination_id := ins_reverse_dist_lines_ers.price_var_code_combination_id;
3803 lc_inv_dist.invoice_price_variance := ins_reverse_dist_lines_ers.invoice_price_variance;
3804 lc_inv_dist.base_invoice_price_variance := ins_reverse_dist_lines_ers.base_invoice_price_variance;
3805 lc_inv_dist.matched_uom_lookup_code := ins_reverse_dist_lines_ers.matched_uom_lookup_code;
3806 lc_inv_dist.invoice_line_number := ins_reverse_dist_lines_ers.invoice_line_number;
3807 lc_inv_dist.org_id := ins_reverse_dist_lines_ers.org_id;
3808 lc_inv_dist.charge_applicable_to_dist_id := ins_reverse_dist_lines_ers.charge_applicable_to_dist_id;
3809 lc_inv_dist.project_id := ins_reverse_dist_lines_ers.project_id;
3810 lc_inv_dist.task_id := ins_reverse_dist_lines_ers.task_id;
3811 lc_inv_dist.expenditure_type := ins_reverse_dist_lines_ers.expenditure_type;
3812 lc_inv_dist.expenditure_item_date := ins_reverse_dist_lines_ers.expenditure_item_date;
3813 lc_inv_dist.expenditure_organization_id := ins_reverse_dist_lines_ers.expenditure_organization_id;
3814 lc_inv_dist.project_accounting_context := ins_reverse_dist_lines_ers.project_accounting_context;
3815 lc_inv_dist.pa_addition_flag := ins_reverse_dist_lines_ers.pa_addition_flag;
3816 lc_inv_dist.distribution_class := ins_reverse_dist_lines_ers.distribution_class;
3817 lc_inv_dist.dist_match_type := ins_reverse_dist_lines_ers.dist_match_type;
3818 lc_inv_dist.Last_Updated_By := -1;
3819 lc_inv_dist.Last_Update_date := sysdate;
3820 lc_inv_dist.match_status_flag := 'N';
3821 lc_inv_dist.reversal_flag := 'Y';
3822 lc_inv_dist.program_application_id := -1;
3823 lc_inv_dist.program_id := -1;
3824 lc_inv_dist.program_update_date := sysdate;
3825 lc_inv_dist.quantity_invoiced := null;
3826
3827 ln_inv_dist_id := jai_ap_match_tax_proc_pkg.insert_ap_inv_dist(lc_inv_dist);
3828 --Update ap_invoice_lines_all
3829 jai_ap_match_tax_proc_pkg.update_ap_inv_line(ins_reverse_dist_lines_ers.invoice_id,
3830 ins_reverse_dist_lines_ers.invoice_line_number);
3831 Commit;
3832 End Loop;
3833 END IF;
3834
3835 end process_discard;
3836
3837
3838 procedure tax_check_at_inv_validate(p_invoice_id in number,
3839 p_invoice_line_number in number default null,
3840 p_process_flag out nocopy varchar2,
3841 p_process_message out nocopy varchar2,
3842 p_codepath in out nocopy varchar2) is
3843 cursor c_po_tax_num(cp_invoice_id number) is
3844 select sum(nm)
3845 from (select count(1) nm
3846 from ap_invoice_lines_all la, JAI_PO_TAXES pt
3847 where la.match_type in ('ITEM_TO_PO')
3848 and la.invoice_id = cp_invoice_id
3849 and la.po_line_location_id = pt.line_location_id
3850 and la.po_header_id = pt.po_header_id
3851 and la.po_line_id = pt.po_line_id
3852 and la.line_number = p_invoice_line_number
3853 union all
3854 select count(1) nm
3855 from ap_invoice_lines_all la, JAI_RCV_LINE_TAXES lt
3856 where la.match_type in ('ITEM_TO_RECEIPT')
3857 and la.invoice_id = cp_invoice_id
3858 and la.line_number = p_invoice_line_number
3859 and la.rcv_transaction_id = lt.transaction_id);
3860
3861 cursor c_inv_tax_num(cp_invoice_id number) is
3862 select count(1)
3863 from JAI_AP_MATCH_INV_TAXES
3864 where invoice_id = cp_invoice_id
3865 and PARENT_INVOICE_LINE_NUMBER = p_invoice_line_number;
3866 ln_po_tax_count number;
3867 ln_inv_tax_count number;
3868 begin
3869 p_codepath := jai_general_pkg.plot_codepath(1,
3870 p_codepath,
3871 'jai_ap_match_tax_proc_pkg.tax_check_at_inv_validate',
3872 'START');
3873 open c_po_tax_num(p_invoice_id);
3874 fetch c_po_tax_num
3875 into ln_po_tax_count;
3876 close c_po_tax_num;
3877 if ln_po_tax_count > 0 then
3878 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath);
3879 open c_inv_tax_num(p_invoice_id);
3880 fetch c_inv_tax_num
3881 into ln_inv_tax_count;
3882 close c_inv_tax_num;
3883 if ln_inv_tax_count > 0 then
3884 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath);
3885 p_process_flag := 'S';
3886 P_process_message := 'Taxes from PO/Recepit have been copied!';
3887 else
3888 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath);
3889 p_process_flag := 'E';
3890 P_process_message := 'Taxes from PO/Recepit have not been copied yet!';
3891 end if;
3892 end if;
3893 exception
3894 when others then
3895 p_process_flag := 'E';
3896 P_process_message := 'jai_ap_match_tax_proc_pkg.tax_check_at_inv_validate :' ||
3897 sqlerrm;
3898
3899 end tax_check_at_inv_validate;
3900
3901 function get_match_type(pn_invoice_id IN NUMBER,
3902 pn_invoice_line_num IN NUMBER) return varchar2 is
3903 lv_match_type varchar2(50);
3904 lv_receipt_code VARCHAR2(20);
3905 ln_shipment_line_id NUMBER;
3906 ln_shipment_header_id NUMBER;
3907 begin
3908 jai_ap_match_tax_proc_pkg.get_match_type(pn_invoice_id,
3909 pn_invoice_line_num,
3910 lv_match_type,
3911 lv_receipt_code,
3912 ln_shipment_line_id,
3913 ln_shipment_header_id);
3914 return lv_match_type;
3915 end get_match_type;
3916
3917
3918 FUNCTION enable_match_details_menu(pn_invoice_id NUMBER) RETURN VARCHAR2 is
3919 cursor c_inv_lines_count(cn_invoice_id number) is
3920 select count(1)
3921 from ap_invoice_lines_all
3922 where invoice_id = cn_invoice_id;
3923 cursor c_invoice_type(cn_invoice_id number) is
3924 select invoice_type_lookup_code
3925 from ap_invoices_all
3926 where invoice_id = cn_invoice_id;
3927 cursor c_invoice_lines(cn_invoice_id number) is
3928 select * from ap_invoice_lines_all where invoice_id = cn_invoice_id;
3929
3930 lv_enable_menu varchar2(1);
3931 lv_count number;
3932 lv_invoice_type ap_invoices_all.invoice_type_lookup_code%type;
3933 lv_match_type varchar2(50);
3934 lv_receipt_code varchar2(50);
3935 ln_shipment_line_id NUMBER;
3936 ln_shipment_header_id NUMBER;
3937 begin
3938 open c_inv_lines_count(pn_invoice_id);
3939 fetch c_inv_lines_count
3940 into lv_count;
3941 close c_inv_lines_count;
3942
3943 if lv_count > 0 then
3944 lv_enable_menu := 'Y';
3945 else
3946 lv_enable_menu := 'N';
3947 return lv_enable_menu;
3948 end if;
3949
3950 open c_invoice_type(pn_invoice_id);
3951 fetch c_invoice_type
3952 into lv_invoice_type;
3953 close c_invoice_type;
3954
3955 /* Commented out by Wenqiong on 26-DEC-2012 for bug16040264 to make menu enable for DM from RTV
3956 if lv_invoice_type in ('CREDIT', 'DEBIT') then
3957 lv_enable_menu := 'N';
3958 return lv_enable_menu;
3959 else
3960 lv_enable_menu := 'Y';
3961 end if;*/
3962
3963 for ret in c_invoice_lines(pn_invoice_id) loop
3964 if ret.match_type in
3965 ('ITEM_TO_PO', 'ITEM_TO_RECEIPT', 'PO_PRICE_ADJUSTMENT') then
3966 lv_enable_menu := 'Y';
3967 exit;
3968 else
3969 lv_enable_menu := 'N';
3970 end if;
3971 end loop;
3972 return lv_enable_menu;
3973 end enable_match_details_menu;
3974
3975 END jai_ap_match_tax_proc_pkg;