[Home] [Help]
PACKAGE BODY: APPS.AP_QUICK_CREDIT_PKG
Source
1 PACKAGE BODY AP_QUICK_CREDIT_PKG AS
2 /* $Header: apqkcreb.pls 120.14.12020000.2 2013/02/08 11:27:27 sbonala ship $ */
3
4 TYPE Inv_Line_Tab_Type IS TABLE OF ap_invoice_lines_all%ROWTYPE;
5
6 /*=============================================================================
7 | FUNCTION - Validating_Rules()
8 |
9 | DESCRIPTION
10 | Private function that will validate the rules for the creation of a
11 | credit or debit memo with quick credit functionality.
12 | This function returns TRUE if the reversal for the invoice can go through
13 | or FALSE and an error code if any of the quick credit rules is not
14 | followed.
15 |
16 | The following rules are validated in this function:
17 | 1. Check if supplier is the same for CR/DB memo and credited invoice
18 | 2. Check if credited invoice is not a CR/DB memo or prepayment
19 | 3. Check if credited invoice is cancelled
20 | 4. Check if CI contains price or quantity corrections
21 | 5. Check if CI contains prepayment applications
22 | 6. Check if CI contains withholding tax
23 | For each line
24 | 7. Is line fully distributed?
25 | 8. Is quantity or amount billed below 0 after reversal?
26 | 9. Are ccids in the distributions of the line invalid?
27 |
28 | PARAMETERS
29 | P_Invoice_Id - invoice id
30 | P_Vendor_Id_For_Invoice - vendor id for the debit or credit memo
31 | P_Invoice_Header_Rec - header record for the credited invoice
32 | P_Invoice_Lines_Tab - line list for the credited invoice
33 | P_Error_Code - Error code to be returned to the user
34 | P_Calling_Sequence - debug info
35 |
36 | MODIFICATION HISTORY
37 | Date Author Description of Change
38 | 29-JUL-2003 SYIDNER Creation
39 |
40 *============================================================================*/
41
42 FUNCTION Validating_Rules(
43 P_Invoice_Id IN NUMBER,
44 P_Vendor_Id_For_Invoice IN NUMBER,
45 P_Dm_Gl_Date IN DATE,
46 P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE,
47 P_Invoice_Lines_Tab IN Inv_Line_Tab_Type,
48 P_Error_Code OUT NOCOPY VARCHAR2,
49 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
50
51 IS
52 l_debug_info VARCHAR2(240);
53 l_curr_calling_sequence VARCHAR2(4000);
54
55 i BINARY_INTEGER := 0;
56 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
57
58
59 TYPE dist_ccid_list_tab
60 IS TABLE OF ap_invoice_distributions_all.dist_code_combination_id%TYPE;
61 l_dist_ccid_list dist_ccid_list_tab;
62 l_return_var BOOLEAN := TRUE;
63
64 BEGIN
65 l_curr_calling_sequence := 'AP_QUICK_CREDIT_PKG.Validating_Rules<-' ||
66 P_calling_sequence;
67
68 ------------------------------------------------------------------
69 l_debug_info := 'Step 0: Get Chart of Accounts Id for CCID '||
70 'validation';
71 ------------------------------------------------------------------
72
73 BEGIN
74 SELECT chart_of_accounts_id
75 INTO l_chart_of_accounts_id
76 FROM gl_sets_of_books
77 WHERE set_of_books_id = P_Invoice_Header_Rec.set_of_books_id;
78 END;
79
80 ------------------------------------------------------------------
81 l_debug_info := 'Step 1: Check if supplier is the same for CR/DB
82 memo and credited invoice';
83 ------------------------------------------------------------------
84 IF ( P_Vendor_Id_For_Invoice <> P_Invoice_Header_Rec.vendor_id ) THEN
85 p_error_code := 'AP_QC_VENDOR_IS_DIFFERENT';
86
87 l_return_var := FALSE;
88 END IF;
89
90 ------------------------------------------------------------------
91 l_debug_info := 'Step 2: Check if credited invoice is not a CR/DB
92 memo or prepayment';
93 ------------------------------------------------------------------
94 IF (l_return_var = TRUE) THEN
95 IF ( P_Invoice_Header_Rec.invoice_type_lookup_code IN
96 ('CREDIT', 'DEBIT', 'PREPAYMENT')) THEN
97
98 p_error_code := 'AP_QC_INV_RESTRICTED_TYPE';
99 l_return_var := FALSE;
100 END IF;
101 END IF;
102
103 ------------------------------------------------------------------
104 l_debug_info := 'Step 3: Check if credited invoice is cancelled';
105 ------------------------------------------------------------------
106 IF (l_return_var = TRUE) THEN
107 IF (P_Invoice_Header_Rec.cancelled_date IS NOT NULL
108 AND P_Invoice_Header_Rec.cancelled_by IS NOT NULL) THEN
109
110 p_error_code := 'AP_QC_INV_ALREADY_CANCELLED';
111 l_return_var := FALSE;
112 END IF;
113 END IF;
114
115 ------------------------------------------------------------------
116 l_debug_info := 'Step 4: Check if CI contains price or quantity
117 corrections';
118 ------------------------------------------------------------------
119 IF (l_return_var = TRUE) THEN
120 IF (AP_INVOICES_UTILITY_PKG.Inv_With_PQ_Corrections(
121 P_Invoice_Id => P_Invoice_Header_Rec.invoice_id,
122 P_Calling_sequence => l_curr_calling_sequence)) THEN
123
124 p_error_code := 'AP_QC_INV_WITH_PQ_CORRECTION';
125 l_return_var := FALSE;
126 END IF;
127 END IF;
128
129 ------------------------------------------------------------------
130 l_debug_info := 'Step 5: Check if CI contains prepayment
131 applications';
132 ------------------------------------------------------------------
133 IF (l_return_var = TRUE) THEN
134 IF (AP_INVOICES_UTILITY_PKG.Inv_With_Prepayments(
135 P_Invoice_Id => P_Invoice_Header_Rec.invoice_id,
136 P_Calling_sequence => l_curr_calling_sequence)) THEN
137
138 p_error_code := 'AP_QC_INV_WITH_PREPAYMENTS';
139 l_return_var := FALSE;
140 END IF;
141 END IF;
142
143 ------------------------------------------------------------------
144 l_debug_info := 'Step 6: Check if CI contains withholding tax';
145 ------------------------------------------------------------------
146 IF (l_return_var = TRUE) THEN
147 IF (AP_INVOICES_UTILITY_PKG.Invoice_Includes_Awt(
148 P_Invoice_Id => P_Invoice_Header_Rec.invoice_id,
149 P_Calling_sequence => l_curr_calling_sequence)) THEN
150
151 p_error_code := 'AP_QC_INV_CONTAINS_AWT';
152 l_return_var := FALSE;
153 END IF;
154 END IF;
155
156 ------------------------------------------------------------------
157 l_debug_info := 'Step 7: Check if CI is matched to PO finally closed';
158 ------------------------------------------------------------------
159 IF (l_return_var = TRUE) THEN
160 IF (AP_INVOICES_UTILITY_PKG.Inv_Matched_Finally_Closed_Po(
161 P_Invoice_Id => P_Invoice_Header_Rec.invoice_id,
162 P_Calling_sequence => l_curr_calling_sequence)) THEN
163
164 p_error_code := 'AP_QC_INV_PO_FINALLY_CLOSED';
165 l_return_var := FALSE;
166 END IF;
167 END IF;
168
169 ------------------------------------------------------------------
170 l_debug_info := 'Step 8: Validation for lines';
171 ------------------------------------------------------------------
172 IF (l_return_var = TRUE) THEN
173 FOR i IN P_Invoice_Lines_Tab.FIRST..P_Invoice_Lines_Tab.LAST LOOP
174
175 ------------------------------------------------------------------
176 l_debug_info := 'Step 8: Is line fully distributed?';
177 ------------------------------------------------------------------
178 IF NOT (AP_INVOICE_LINES_UTILITY_PKG.Is_Line_Fully_Distributed(
179 P_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
180 P_Line_Number => P_Invoice_Lines_Tab(i).line_number,
181 P_Calling_sequence => l_curr_calling_sequence)) THEN
182
183
184 p_error_code := 'AP_QC_INV_NOT_FULLY_DIST';
185 l_return_var := FALSE;
186 END IF;
187
188 ------------------------------------------------------------------
189 l_debug_info := 'Step 9: Is quantity or amount billed below 0
190 after reversal? ';
191 ------------------------------------------------------------------
192 IF (l_return_var = TRUE) THEN
193 IF (AP_INVOICE_LINES_UTILITY_PKG.Is_PO_RCV_Amount_Exceeded(
194 P_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
195 P_Line_Number => P_Invoice_Lines_Tab(i).line_number,
196 P_Calling_sequence => l_curr_calling_sequence)) THEN
197
198 p_error_code := 'AP_QC_BILLED_AMOUNT_BELOW_ZERO';
199 l_return_var := FALSE;
200 END IF;
201 END IF;
202
203 ------------------------------------------------------------------
204 l_debug_info := 'Step 10: Are ccids in the distributions of the
205 line invalid?';
206 ------------------------------------------------------------------
207 IF (l_return_var = TRUE) THEN
208 BEGIN
209 SELECT DISTINCT aid.dist_code_combination_id
210 BULK COLLECT INTO l_dist_ccid_list
211 FROM ap_invoice_distributions_all aid
212 WHERE aid.invoice_id = P_Invoice_Lines_Tab(i).invoice_id
213 AND aid.invoice_line_number = P_Invoice_Lines_Tab(i).line_number
214 AND NVL(aid.reversal_flag, 'N') <> 'Y';
215
216 IF l_dist_ccid_list.COUNT > 0 THEN
217 FOR j IN l_dist_ccid_list.FIRST..l_dist_ccid_list.LAST LOOP
218
219
220 IF (l_return_var = TRUE) THEN
221 IF NOT(AP_UTILITIES_PKG.Is_Ccid_Valid(
222 P_CCID => l_dist_ccid_list(j),
223 P_Chart_Of_Accounts_Id => l_chart_of_accounts_id,
224 P_Date => P_Dm_Gl_Date,
225 P_Calling_Sequence => l_curr_calling_sequence)) THEN
226
227 p_error_code := 'AP_QC_DIST_CCIDS_NOT_VALID';
228 l_return_var := FALSE;
229 END IF;
230 END IF;
231
232 END LOOP; -- ccid validation loop
233 END IF;
234 END;
235 END IF; -- l_return_var for ccids validation
236
237 END LOOP; -- lines loop
238 END IF; -- l_return_var for lines loop
239
240 RETURN l_return_var;
241 EXCEPTION
242 WHEN OTHERS THEN
243 IF (SQLCODE <> -20001) THEN
244 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
245 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
246 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
247 FND_MESSAGE.SET_TOKEN('PARAMETERS',
248 ' P_Invoice_Id = '||P_Invoice_Id||
249 ' P_Vendor_Id_For_Invoice = '||P_Vendor_Id_For_Invoice||
250 ' P_Error_Code = '||P_Error_Code||
251 ' P_Calling_Sequence = '||P_Calling_Sequence);
252 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
253 END IF;
254
255 APP_EXCEPTION.RAISE_EXCEPTION;
256
257 END Validating_Rules;
258
259 /*=============================================================================
260 | FUNCTION - Full_Reversal()
261 |
262 | DESCRIPTION
263 | Private function that will create the reversed lines and distributions
264 | for the credit or debit memo.
265 | This function returns TRUE if the lines and distributions are created
266 | or FALSE and an error code otherwise.
267 |
268 | PARAMETERS
269 | P_Invoice_Id - invoice id
270 | P_Invoice_Header_Rec - header record for the credited invoice
271 | P_Invoice_Lines_Tab - line list for the credited invoice
272 | P_Error_Code - Error code to be returned to the user
273 | P_Calling_Sequence - debug info
274 |
275 | MODIFICATION HISTORY
276 | Date Author Description of Change
277 | 29-JUL-2003 SYIDNER Creation
278 |
279 *============================================================================*/
280
281 FUNCTION Full_Reversal(
282 P_Invoice_Id IN NUMBER,
283 P_Dm_Gl_Date IN DATE,
284 P_Dm_Org_Id IN NUMBER,
285 P_Invoice_Header_Rec IN ap_invoices_all%ROWTYPE,
286 P_Invoice_Lines_Tab IN Inv_Line_Tab_Type,
287 P_error_code OUT NOCOPY VARCHAR2,
288 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
289
290 IS
291 l_debug_info VARCHAR2(240);
292 l_curr_calling_sequence VARCHAR2(4000);
293 i BINARY_INTEGER := 0;
294 l_line_number ap_invoice_lines_all.line_number%TYPE;
295 l_period_name gl_period_statuses.period_name%TYPE := '';
296 l_line_source
297 ap_invoice_lines_all.line_source%TYPE := 'QUICK CREDIT';
298
299 l_wfapproval_flag
300 ap_system_parameters_all.approval_workflow_flag%TYPE;
301 l_wfapproval_status ap_invoice_lines_all.wfapproval_status%TYPE;
302 l_key_value_list GL_CA_UTILITY_PKG.R_KEY_VALUE_ARR;
303
304 l_Corr_Dist_Tab_Po AP_MATCHING_PKG.corr_dist_tab_type;
305 l_Corr_Dist_Tab_Rcv AP_MATCHING_PKG.corr_dist_tab_type;
306
307 l_matching_basis PO_LINE_TYPES.matching_basis%TYPE; /* ABM */
308 l_copy_gdf VARCHAR2(1); --Bug#13464635
309
310 CURSOR corrected_dist_po(c_invoice_id NUMBER,
311 c_line_number NUMBER) IS
312 SELECT aid.po_distribution_id po_distribution_id,
313 null invoice_distribution_id, --will be populated by the corr API
314 aid.invoice_distribution_id corrected_inv_dist_id,
315 (-1)*AID.QUANTITY_INVOICED CORRECTED_QUANTITY,
316 (-1)* (aid.amount+nvl(AID_IPV.AMOUNT,0)) amount, --bug13431447
317 null base_amount, --will be populated by the corr API
318 null rounding_amt, --will be populated by the corr API
319 aid.unit_price unit_price,
320 null pa_quantity,
321 AID.DIST_CODE_COMBINATION_ID DIST_CCID
322 FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,
323 AP_INVOICE_DISTRIBUTIONS_ALL AID_IPV --added for bug13431447
324 WHERE AID.INVOICE_ID = C_INVOICE_ID
325 AND AID.INVOICE_LINE_NUMBER = C_LINE_NUMBER
326 --bug13431447 starts
327 AND AID.INVOICE_DISTRIBUTION_ID=AID_IPV.RELATED_ID(+)
328 AND AID_IPV.LINE_TYPE_LOOKUP_CODE(+) = 'IPV'
329 AND NVL(AID_IPV.REVERSAL_FLAG(+), 'N') <> 'Y'
330 --bug13431447 ends
331 AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TERV',
332 'TRV', 'IPV', 'ERV')
333 AND NVL(AID.REVERSAL_FLAG, 'N') <> 'Y'
334 ;
335
336
337 CURSOR corrected_dist_rcv(c_invoice_id NUMBER,
338 c_line_number NUMBER) IS
339 SELECT aid.po_distribution_id po_distribution_id,
340 null invoice_distribution_id, --will be populated by the corr API
341 aid.invoice_distribution_id corrected_inv_dist_id,
342 (-1)*AID.QUANTITY_INVOICED CORRECTED_QUANTITY,
343 (-1)* (aid.amount+nvl(AID_IPV.AMOUNT,0)) amount, --bug13431447
344 null base_amount, --will be populated by the corr API
345 null rounding_amt, --will be populated by the corr API
346 aid.unit_price unit_price,
347 null pa_quantity,
348 aid.dist_code_combination_id dist_ccid
349 FROM AP_INVOICE_DISTRIBUTIONS AID,
350 AP_INVOICE_DISTRIBUTIONS_ALL AID_IPV --added for bug13431447
351 WHERE aid.invoice_id = c_invoice_id
352 AND AID.INVOICE_LINE_NUMBER = C_LINE_NUMBER
353 --bug13431447 starts
354 AND AID.INVOICE_DISTRIBUTION_ID=AID_IPV.RELATED_ID(+)
355 AND AID_IPV.LINE_TYPE_LOOKUP_CODE(+) = 'IPV'
356 AND NVL(AID_IPV.REVERSAL_FLAG(+), 'N') <> 'Y'
357 --bug13431447 ends
358 AND aid.line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TERV',
359 'TRV', 'IPV', 'ERV')
360 AND NVL(AID.REVERSAL_FLAG, 'N') <> 'Y';
361
362 BEGIN
363
364 l_curr_calling_sequence := 'AP_QUICK_CREDIT_PKG.Full_Reversal<-' ||
365 P_calling_sequence;
366
367 ----------------------------------------------------------------------------
368 l_debug_info := 'Getting period name for the lines based on the gl_date for '||
369 'the credit/debit memo';
370 ----------------------------------------------------------------------------
371
372 l_period_name := AP_INVOICES_PKG.Get_Period_Name(
373 l_invoice_date => P_Dm_Gl_Date,
374 l_receipt_date => null,
375 l_org_id => P_Dm_Org_Id);
376
377 ----------------------------------------------------------------------------
378 l_debug_info := 'Get wfapproval information ';
379 ----------------------------------------------------------------------------
380 BEGIN
381 SELECT approval_workflow_flag
382 INTO l_wfapproval_flag
383 FROM ap_system_parameters_all
384 WHERE org_id = P_Dm_Org_Id;
385 END;
386
387 if NVL(l_wfapproval_flag,'N') = 'Y' then
388 l_wfapproval_status := 'REQUIRED';
389 else
390 l_wfapproval_status := 'NOT REQUIRED';
391 end if;
392
393
394 ----------------------------------------------------------------------------
395 l_debug_info := 'For every line...';
396 ----------------------------------------------------------------------------
397
398 FOR i in P_Invoice_Lines_Tab.FIRST..P_Invoice_Lines_Tab.LAST LOOP
399
400 ----------------------------------------------------------------------------
401 l_debug_info := 'Verify if it is a PO/RCV matched line';
402 ----------------------------------------------------------------------------
403
404 IF (P_Invoice_Lines_Tab(i).po_line_location_id IS NOT NULL AND
405 P_Invoice_Lines_Tab(i).rcv_transaction_id IS NULL) THEN
406 ------------------------------------------------------------------------
407 l_debug_info := 'The line PO matched populate dist pl/sql table';
408 ------------------------------------------------------------------------
409 BEGIN
410 OPEN corrected_dist_po(P_Invoice_Lines_Tab(i).invoice_id,
411 P_Invoice_Lines_Tab(i).line_number);
412 FETCH corrected_dist_po
413 BULK COLLECT INTO l_Corr_Dist_Tab_Po;
414 CLOSE corrected_dist_po;
415 END;
416
417 /* For Amount Based Matching */
418 ------------------------------------------------------------------------
419 l_debug_info := 'retrieving the matching basis from po';
420 ------------------------------------------------------------------------
421 SELECT plt.matching_basis
422 INTO l_matching_basis
423 FROM po_line_locations_all pll,
424 po_lines_all pl,
425 po_line_types plt
426 WHERE pll.line_location_id = P_Invoice_Lines_Tab(i).po_line_location_id
427 AND pll.po_line_id = pl.po_line_id
428 AND pl.line_type_id = plt.line_type_id;
429
430 IF l_matching_basis = 'AMOUNT' THEN
431
432 ------------------------------------------------------------------------
433 l_debug_info := 'Call Amount correction API for PO match';
434 ------------------------------------------------------------------------
435 AP_PO_AMT_MATCH_PKG.Amount_Correct_Inv_PO(
436 X_Invoice_Id => P_Invoice_id,
437 X_Invoice_Line_Number => NULL,
438 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
439 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
440 X_Match_Mode => 'CR-PD',
441 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
442 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
443 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Po,
444 X_Final_Match_Flag => 'N',
445 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
446 X_Calling_Sequence => l_curr_calling_sequence);
447
448 ELSE
449
450 ------------------------------------------------------------------------
451 l_debug_info := 'Call quantity correction API for PO match';
452 ------------------------------------------------------------------------
453 AP_MATCHING_PKG.Price_Quantity_Correct_Inv_PO(
454 X_Invoice_Id => P_Invoice_id,
455 X_Invoice_Line_Number => NULL,
456 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
457 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
458 X_Correction_Type => 'QTY_CORRECTION',
459 X_Match_Mode => 'CR-PD',
460 X_Correction_Quantity => (-1)*P_Invoice_Lines_Tab(i).quantity_invoiced,
461 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
462 X_Correction_Price => P_Invoice_Lines_Tab(i).unit_price,
463 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
464 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Po,
465 X_Final_Match_Flag => 'N',
466 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
467 X_Calling_Sequence => l_curr_calling_sequence);
468
469 END IF;
470
471 ELSIF (P_Invoice_Lines_Tab(i).po_line_location_id IS NOT NULL AND
472 P_Invoice_Lines_Tab(i).rcv_transaction_id IS NOT NULL) THEN
473 ----------------------------------------------------------------------------
474 l_debug_info := 'The line RCV matched populate dist pl/sql table';
475 ----------------------------------------------------------------------------
476 BEGIN
477 OPEN corrected_dist_rcv(P_Invoice_Lines_Tab(i).invoice_id,
478 P_Invoice_Lines_Tab(i).line_number);
479 FETCH corrected_dist_rcv
480 BULK COLLECT INTO l_Corr_Dist_Tab_Rcv;
481 CLOSE corrected_dist_rcv;
482 END;
483
484 /* For Amount Based Matching */
485 ------------------------------------------------------------------------
486 l_debug_info := 'retrieving the matching basis from po';
487 ------------------------------------------------------------------------
488 SELECT plt.matching_basis
489 INTO l_matching_basis
490 FROM po_line_locations_all pll,
491 po_lines_all pl,
492 po_line_types plt
493 WHERE pll.line_location_id = P_Invoice_Lines_Tab(i).po_line_location_id
494 AND pll.po_line_id = pl.po_line_id
495 AND pl.line_type_id = plt.line_type_id;
496
497 IF l_matching_basis = 'AMOUNT' THEN
498
499 ------------------------------------------------------------------------
500 l_debug_info := 'Call Amount correction API for receipt match';
501 ------------------------------------------------------------------------
502 AP_RCT_AMT_MATCH_PKG.Amount_Correct_Inv_RCV(
503 X_Invoice_Id => P_Invoice_id,
504 X_Invoice_Line_Number => NULL,
505 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
506 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
507 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
508 X_Match_Mode => 'CR-PD',
509 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
510 X_Rcv_Transaction_Id => P_Invoice_Lines_Tab(i).rcv_transaction_id,
511 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Rcv,
512 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
513 X_Calling_Sequence => l_curr_calling_sequence);
514
515 ELSE
516
517 ------------------------------------------------------------------------
518 l_debug_info := 'Call quantity correction API for receipt match';
519 ------------------------------------------------------------------------
520 AP_RECT_MATCH_PKG.Price_Quantity_Correct_Inv_RCV(
521 X_Invoice_Id => P_Invoice_id,
522 X_Invoice_Line_Number => NULL,
523 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
524 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
525 X_Correction_Type => 'QTY_CORRECTION',
526 X_Match_Mode => 'CR-PD',
527 X_Correction_Quantity => (-1)*P_Invoice_Lines_Tab(i).quantity_invoiced,
528 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
529 X_Correction_Price => P_Invoice_Lines_Tab(i).unit_price,
530 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
531 X_Rcv_Transaction_Id => P_Invoice_Lines_Tab(i).rcv_transaction_id,
532 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Rcv,
533 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
534 X_Calling_Sequence => l_curr_calling_sequence);
535
536 END IF;
537
538 ELSE
539 ----------------------------------------------------------------------------
540 l_debug_info := 'The line is NOT PO or RCV matched - Create line';
541 ----------------------------------------------------------------------------
542 l_line_number := AP_INVOICES_PKG.get_max_line_number(P_invoice_id)+1;
543
544 BEGIN
545 INSERT INTO ap_invoice_lines_all(
546 invoice_id,
547 line_number,
548 line_type_lookup_code,
549 requester_id,
550 description,
551 line_source,
552 org_id,
553 inventory_item_id,
554 item_description,
555 serial_number,
556 manufacturer,
557 model_number,
558 warranty_number,
559 generate_dists,
560 match_type,
561 distribution_set_id,
562 account_segment,
563 balancing_segment,
564 cost_center_segment,
565 overlay_dist_code_concat,
566 default_dist_ccid,
567 prorate_across_all_items,
568 line_group_number,
569 accounting_date,
570 period_name,
571 deferred_acctg_flag,
572 def_acctg_start_date,
573 def_acctg_end_date,
574 def_acctg_number_of_periods,
575 def_acctg_period_type,
576 set_of_books_id,
577 amount,
578 base_amount,
579 rounding_amt,
580 quantity_invoiced,
581 unit_meas_lookup_code,
582 unit_price,
583 wfapproval_status,
584 -- ussgl_transaction_code, - Bug 4277744
585 discarded_flag,
586 original_amount,
587 original_base_amount,
588 original_rounding_amt,
589 cancelled_flag,
590 income_tax_region,
591 type_1099,
592 stat_amount,
593 prepay_invoice_id,
594 prepay_line_number,
595 invoice_includes_prepay_flag,
596 corrected_inv_id,
597 corrected_line_number,
598 po_header_id,
599 po_line_id,
600 po_release_id,
601 po_line_location_id,
602 po_distribution_id,
603 rcv_transaction_id,
604 final_match_flag,
605 assets_tracking_flag,
606 asset_book_type_code,
607 asset_category_id,
608 project_id,
609 task_id,
610 expenditure_type,
611 expenditure_item_date,
612 expenditure_organization_id,
613 pa_quantity,
614 pa_cc_ar_invoice_id,
615 pa_cc_ar_invoice_line_num,
616 pa_cc_processed_code,
617 award_id,
618 awt_group_id,
619 reference_1,
620 reference_2,
621 receipt_verified_flag,
622 receipt_required_flag,
623 receipt_missing_flag,
624 justification,
625 expense_group,
626 start_expense_date,
627 end_expense_date,
628 receipt_currency_code,
629 receipt_conversion_rate,
630 receipt_currency_amount,
631 daily_amount,
632 web_parameter_id,
633 adjustment_reason,
634 merchant_document_number,
635 merchant_name,
636 merchant_reference,
637 merchant_tax_reg_number,
638 merchant_taxpayer_id,
639 country_of_supply,
640 credit_card_trx_id,
641 company_prepaid_invoice_id,
642 cc_reversal_flag,
643 attribute_category,
644 attribute1,
645 attribute2,
646 attribute3,
647 attribute4,
648 attribute5,
649 attribute6,
650 attribute7,
651 attribute8,
652 attribute9,
653 attribute10,
654 attribute11,
655 attribute12,
656 attribute13,
657 attribute14,
658 attribute15,
659 global_attribute_category,
660 global_attribute1,
661 global_attribute2,
662 global_attribute3,
663 global_attribute4,
664 global_attribute5,
665 global_attribute6,
666 global_attribute7,
667 global_attribute8,
668 global_attribute9,
669 global_attribute10,
670 global_attribute11,
671 global_attribute12,
672 global_attribute13,
673 global_attribute14,
674 global_attribute15,
675 global_attribute16,
676 global_attribute17,
677 global_attribute18,
678 global_attribute19,
679 global_attribute20,
680 creation_date,
681 created_by,
682 last_updated_by,
683 last_update_date,
684 last_update_login,
685 program_application_id,
686 program_id,
687 program_update_date,
688 request_id,
689 purchasing_category_id)
690 VALUES (
691 P_Invoice_id, -- invoice_id
692 l_line_number, -- line_number
693 P_Invoice_Lines_Tab(i).line_type_lookup_code, -- line_type_lookup_code
694 P_Invoice_Lines_Tab(i).requester_id, -- requester_id
695 P_Invoice_Lines_Tab(i).description, -- description
696 l_line_source, -- line_source
697 P_Dm_Org_Id, -- org_id
698 P_Invoice_Lines_Tab(i).inventory_item_id, -- inventory_item_id
699 P_Invoice_Lines_Tab(i).Item_description, -- item_description
700 P_Invoice_Lines_Tab(i).serial_number, -- serial_number
701 P_Invoice_Lines_Tab(i).Manufacturer, -- manufacturer
702 P_Invoice_Lines_Tab(i).Model_Number, -- model_number
703 P_Invoice_Lines_Tab(i).warranty_number, -- warranty_number
704 P_Invoice_Lines_Tab(i).generate_dists, -- generate_dists
705 P_Invoice_Lines_Tab(i).match_type, -- match_type
706 P_Invoice_Lines_Tab(i).distribution_set_id, -- distribution_set_id
707 P_Invoice_Lines_Tab(i).account_segment, -- account_segment
708 P_Invoice_Lines_Tab(i).balancing_segment, -- balancing_segment
709 P_Invoice_Lines_Tab(i).cost_center_segment, -- cost_center_segment
710 P_Invoice_Lines_Tab(i).overlay_dist_code_concat, -- overlay_dist_code_concat
711 P_Invoice_Lines_Tab(i).default_dist_ccid, -- default_dist_ccid
712 P_Invoice_Lines_Tab(i).prorate_across_all_items, -- prorate_across_all_items
713 P_Invoice_Lines_Tab(i).line_group_number, -- line_group_number
714 P_Dm_Gl_Date, -- accounting_date
715 l_period_name, -- period_name
716 P_Invoice_Lines_Tab(i).deferred_acctg_flag, -- deferred_acctg_flag
717 P_Invoice_Lines_Tab(i).def_acctg_start_date, -- def_acctg_start_date
718 P_Invoice_Lines_Tab(i).def_acctg_end_date, -- def_acctg_end_date
719 P_Invoice_Lines_Tab(i).def_acctg_number_of_periods, -- def_acctg_number_of_periods
720 P_Invoice_Lines_Tab(i).def_acctg_period_type, -- def_acctg_period_type
721 P_Invoice_Lines_Tab(i).set_of_books_id, -- set_of_books_id
722 (-1)*P_Invoice_Lines_Tab(i).amount, -- amount
723 P_Invoice_Lines_Tab(i).base_amount, -- base_amount
724 P_Invoice_Lines_Tab(i).rounding_amt, -- rounding_amt
725 P_Invoice_Lines_Tab(i).quantity_invoiced, -- quantity_invoiced
726 P_Invoice_Lines_Tab(i).unit_meas_lookup_code, -- unit_meas_lookup_code
727 P_Invoice_Lines_Tab(i).unit_price, -- unit_price
728 l_wfapproval_status, -- wfapproval_status
729 -- Bug 4277744
730 -- P_Invoice_Lines_Tab(i).ussgl_transaction_code, -- ussgl_transaction_code
731 'N', -- discarded_flag
732 P_Invoice_Lines_Tab(i).original_amount, -- original_amount
733 P_Invoice_Lines_Tab(i).original_base_amount, -- original_base_amount
734 P_Invoice_Lines_Tab(i).original_rounding_amt, -- original_rounding_amt
735 P_Invoice_Lines_Tab(i).cancelled_flag, -- cancelled_flag
736 P_Invoice_Lines_Tab(i).income_tax_region, -- income_tax_region
737 P_Invoice_Lines_Tab(i).type_1099, -- type_1099
738 P_Invoice_Lines_Tab(i).stat_amount, -- stat_amount
739 P_Invoice_Lines_Tab(i).prepay_invoice_id, -- prepay_invoice_id
740 P_Invoice_Lines_Tab(i).prepay_line_number, -- prepay_line_number
741 P_Invoice_Lines_Tab(i).invoice_includes_prepay_flag, -- invoice_includes_prepay_flag
742 P_Invoice_Lines_Tab(i).invoice_id, -- corrected_inv_id
743 P_Invoice_Lines_Tab(i).line_number, -- corrected_line_number
744 P_Invoice_Lines_Tab(i).po_header_id, -- po_header_id
745 P_Invoice_Lines_Tab(i).po_line_id, -- po_line_id
746 P_Invoice_Lines_Tab(i).po_release_id, -- po_release_id
747 P_Invoice_Lines_Tab(i).po_line_location_id, -- po_line_location_id
748 P_Invoice_Lines_Tab(i).po_distribution_id, -- po_distribution_id
749 P_Invoice_Lines_Tab(i).rcv_transaction_id, -- rcv_transaction_id
750 P_Invoice_Lines_Tab(i).final_match_flag, -- final_match_flag
751 P_Invoice_Lines_Tab(i).assets_tracking_flag, -- assets_tracking_flag
752 P_Invoice_Lines_Tab(i).asset_book_type_code, -- asset_book_type_code,
753 P_Invoice_Lines_Tab(i).asset_category_id, -- asset_category_id
754 P_Invoice_Lines_Tab(i).project_id, -- project_id
755 P_Invoice_Lines_Tab(i).task_id, -- task_id
756 P_Invoice_Lines_Tab(i).expenditure_type, -- expenditure_type
757 P_Invoice_Lines_Tab(i).expenditure_item_date, -- expenditure_item_date
758 P_Invoice_Lines_Tab(i).expenditure_organization_id,-- expenditure_organization_id
759 P_Invoice_Lines_Tab(i).pa_quantity, -- pa_quantity
760 P_Invoice_Lines_Tab(i).pa_cc_ar_invoice_id, -- pa_cc_ar_invoice_id
761 P_Invoice_Lines_Tab(i).pa_cc_ar_invoice_line_num, -- pa_cc_ar_invoice_line_num
762 P_Invoice_Lines_Tab(i).pa_cc_processed_code, -- pa_cc_processed_code
763 P_Invoice_Lines_Tab(i).award_id, -- award_id
764 P_Invoice_Lines_Tab(i).awt_group_id, -- awt_group_id
765 P_Invoice_Lines_Tab(i).reference_1, -- reference_1
766 P_Invoice_Lines_Tab(i).reference_2, -- reference_2
767 P_Invoice_Lines_Tab(i).receipt_verified_flag, -- receipt_verified_flag
768 P_Invoice_Lines_Tab(i).receipt_required_flag, -- receipt_required_flag
769 P_Invoice_Lines_Tab(i).receipt_missing_flag, -- receipt_missing_flag
770 P_Invoice_Lines_Tab(i).justification, -- justification
771 P_Invoice_Lines_Tab(i).expense_group, -- expense_group
772 P_Invoice_Lines_Tab(i).start_expense_date, -- start_expense_date
773 P_Invoice_Lines_Tab(i).end_expense_date, -- end_expense_date
774 P_Invoice_Lines_Tab(i).receipt_currency_code, -- receipt_currency_code
775 P_Invoice_Lines_Tab(i).receipt_conversion_rate, -- receipt_conversion_rate
776 P_Invoice_Lines_Tab(i).receipt_currency_amount, -- receipt_currency_amount
777 P_Invoice_Lines_Tab(i).daily_amount, -- daily_amount
778 P_Invoice_Lines_Tab(i).web_parameter_id, -- web_parameter_id
779 P_Invoice_Lines_Tab(i).adjustment_reason, -- adjustment_reason
780 P_Invoice_Lines_Tab(i).merchant_document_number, -- merchant_document_number
781 P_Invoice_Lines_Tab(i).merchant_name, -- merchant_name
782 P_Invoice_Lines_Tab(i).merchant_reference, -- merchant_reference
783 P_Invoice_Lines_Tab(i).merchant_tax_reg_number, -- merchant_tax_reg_number
784 P_Invoice_Lines_Tab(i).merchant_taxpayer_id, -- merchant_taxpayer_id
785 P_Invoice_Lines_Tab(i).country_of_supply, -- country_of_supply
786 P_Invoice_Lines_Tab(i).credit_card_trx_id, -- credit_card_trx_id
787 P_Invoice_Lines_Tab(i).company_prepaid_invoice_id, -- company_prepaid_invoice_id
788 P_Invoice_Lines_Tab(i).cc_reversal_flag, -- cc_reversal_flag
789 P_Invoice_Lines_Tab(i).attribute_category, -- attribute_category
790 P_Invoice_Lines_Tab(i).attribute1, -- attribute1
791 P_Invoice_Lines_Tab(i).attribute2, -- attribute2
792 P_Invoice_Lines_Tab(i).attribute3, -- attribute3
793 P_Invoice_Lines_Tab(i).attribute4, -- attribute4
794 P_Invoice_Lines_Tab(i).attribute5, -- attribute5
795 P_Invoice_Lines_Tab(i).attribute6, -- attribute6
796 P_Invoice_Lines_Tab(i).attribute7, -- attribute7
797 P_Invoice_Lines_Tab(i).attribute8, -- attribute8
798 P_Invoice_Lines_Tab(i).attribute9, -- attribute9
799 P_Invoice_Lines_Tab(i).attribute10, -- attribute10
800 P_Invoice_Lines_Tab(i).attribute11, -- attribute11
801 P_Invoice_Lines_Tab(i).attribute12, -- attribute12
802 P_Invoice_Lines_Tab(i).attribute13, -- attribute13
803 P_Invoice_Lines_Tab(i).attribute14, -- attribute14
804 P_Invoice_Lines_Tab(i).attribute15, -- attribute15
805 P_Invoice_Lines_Tab(i).global_attribute_category, -- global_attribute_category
806 P_Invoice_Lines_Tab(i).global_attribute1, -- global_attribute1
807 P_Invoice_Lines_Tab(i).global_attribute2, -- global_attribute2
808 P_Invoice_Lines_Tab(i).global_attribute3, -- global_attribute3
809 P_Invoice_Lines_Tab(i).global_attribute4, -- global_attribute4
810 P_Invoice_Lines_Tab(i).global_attribute5, -- global_attribute5
811 P_Invoice_Lines_Tab(i).global_attribute6, -- global_attribute6
812 P_Invoice_Lines_Tab(i).global_attribute7, -- global_attribute7
813 P_Invoice_Lines_Tab(i).global_attribute8, -- global_attribute8
814 P_Invoice_Lines_Tab(i).global_attribute9, -- global_attribute9
815 P_Invoice_Lines_Tab(i).global_attribute10, -- global_attribute10
816 P_Invoice_Lines_Tab(i).global_attribute11, -- global_attribute11
817 P_Invoice_Lines_Tab(i).global_attribute12, -- global_attribute12
818 P_Invoice_Lines_Tab(i).global_attribute13, -- global_attribute13
819 P_Invoice_Lines_Tab(i).global_attribute14, -- global_attribute14
820 P_Invoice_Lines_Tab(i).global_attribute15, -- global_attribute15
821 P_Invoice_Lines_Tab(i).global_attribute16, -- global_attribute16
822 P_Invoice_Lines_Tab(i).global_attribute17, -- global_attribute17
823 P_Invoice_Lines_Tab(i).global_attribute18, -- global_attribute18
824 P_Invoice_Lines_Tab(i).global_attribute19, -- global_attribute19
825 P_Invoice_Lines_Tab(i).global_attribute20, -- global_attribute20
826 sysdate, -- creation_date
827 FND_GLOBAL.user_id, -- created_by
828 FND_GLOBAL.user_id, -- last_updated_by
829 sysdate, -- last_update_date
830 FND_GLOBAL.login_id, -- last_update_login
831 P_Invoice_Lines_Tab(i).program_application_id, -- program_application_id
832 P_Invoice_Lines_Tab(i).program_id, -- program_id
833 P_Invoice_Lines_Tab(i).program_update_date, -- program_update_date
834 P_Invoice_Lines_Tab(i).request_id, -- request_id
835 P_Invoice_Lines_Tab(i).purchasing_category_id -- purchasing_category_id
836 );
837
838 END;
839
840 --GTAS Project Bug#13464635 begin
841 IF AP_INVOICES_UTILITY_PKG.FV_ENABLED THEN
842 l_copy_gdf := 'N';
843 ELSE
844 l_copy_gdf := 'Y';
845 END IF;
846 --End Bug#13464635
847
848 ----------------------------------------------------------------------------
849 l_debug_info := 'create distributions';
850 ----------------------------------------------------------------------------
851 INSERT INTO ap_invoice_distributions_all(
852 invoice_id,
853 invoice_line_number,
854 dist_code_combination_id,
855 invoice_distribution_id,
856 last_update_date,
857 last_updated_by,
858 accounting_date,
859 period_name,
860 set_of_books_id,
861 amount,
862 description,
863 type_1099,
864 tax_code_id,
865 posted_flag,
866 batch_id,
867 quantity_invoiced,
868 corrected_quantity,
869 unit_price,
870 match_status_flag,
871 attribute_category,
872 attribute1,
873 attribute2,
874 attribute3,
875 attribute4,
876 attribute5,
877 prepay_amount_remaining,
878 assets_addition_flag,
879 assets_tracking_flag,
880 distribution_line_number,
881 line_type_lookup_code,
882 po_distribution_id,
883 base_amount,
884 pa_addition_flag,
885 encumbered_flag,
886 accrual_posted_flag,
887 cash_posted_flag,
888 last_update_login,
889 creation_date,
890 created_by,
891 stat_amount,
892 attribute11,
893 attribute12,
894 attribute13,
895 attribute14,
896 attribute6,
897 attribute7,
898 attribute8,
899 attribute9,
900 attribute10,
901 attribute15,
902 reversal_flag,
903 parent_invoice_id,
904 income_tax_region,
905 final_match_flag,
906 -- ussgl_transaction_code, - Bug 4277744
907 -- ussgl_trx_code_context, - Bug 4277744
908 expenditure_item_date,
909 expenditure_organization_id,
910 expenditure_type,
911 pa_quantity,
912 project_id,
913 task_id,
914 quantity_variance,
915 base_quantity_variance,
916 awt_flag,
917 awt_group_id,
918 awt_tax_rate_id,
919 awt_gross_amount,
920 reference_1,
921 reference_2,
922 other_invoice_id,
923 awt_invoice_id,
924 awt_origin_group_id,
925 program_application_id,
926 program_id,
927 program_update_date,
928 request_id,
929 award_id,
930 start_expense_date,
931 merchant_document_number,
932 merchant_name,
933 merchant_tax_reg_number,
934 merchant_taxpayer_id,
935 country_of_supply,
936 merchant_reference,
937 parent_reversal_id,
938 rcv_transaction_id,
939 dist_match_type,
940 matched_uom_lookup_code,
941 global_attribute_category,
942 global_attribute1,
943 global_attribute2,
944 global_attribute3,
945 global_attribute4,
946 global_attribute5,
947 global_attribute6,
948 global_attribute7,
949 global_attribute8,
950 global_attribute9,
951 global_attribute10,
952 global_attribute11,
953 global_attribute12,
954 global_attribute13,
955 global_attribute14,
956 global_attribute15,
957 global_attribute16,
958 global_attribute17,
959 global_attribute18,
960 global_attribute19,
961 global_attribute20,
962 receipt_verified_flag,
963 receipt_required_flag,
964 receipt_missing_flag,
965 justification,
966 expense_Group,
967 end_Expense_Date,
968 receipt_Currency_Code,
969 receipt_Conversion_Rate,
970 receipt_Currency_Amount,
971 daily_Amount,
972 web_Parameter_Id,
973 adjustment_Reason,
974 credit_Card_Trx_Id,
975 company_Prepaid_Invoice_Id,
976 org_id,
977 rounding_amt,
978 charge_applicable_to_dist_id,
979 corrected_invoice_dist_id,
980 related_id,
981 asset_book_type_code,
982 asset_category_id,
983 accounting_event_id,
984 cancellation_flag,
985 --Freight and Special Charges
986 rcv_charge_addition_flag)
987 (SELECT
988 P_Invoice_Id, -- invoice_id
989 l_Line_Number, -- invoice_line_number
990 Dist_Code_Combination_Id, -- dist_code_combination_id
991 ap_invoice_distributions_s.NEXTVAL, -- distribution_id
992 sysdate, -- last_update_date
993 FND_GLOBAL.user_id, -- last_updated_by
994 P_Dm_Gl_Date, -- accounting_date
995 l_period_name, -- period_name
996 Set_Of_Books_Id, -- set_of_book_id
997 (-1)*Amount, -- Amount
998 Description, -- description
999 Type_1099, -- type_1099
1000 Tax_Code_Id, -- tax_code_id
1001 'N', -- Posted_Flag,
1002 Batch_Id, -- batch_id
1003 quantity_invoiced, -- Quantity_Invoiced
1004 corrected_quantity, -- corrected_quanity
1005 unit_price, -- Unit_Price,
1006 NULL, -- Match_Status_Flag /* bug 4916530 */
1007 attribute_category, -- attribute_category
1008 attribute1, -- attribute1
1009 attribute2, -- attribute2
1010 attribute3, -- attribute3
1011 attribute4, -- attribute4
1012 attribute5, -- attribute5
1013 NULL, --prepay_amount_remaining
1014 'U', -- Assets_Addition_Flag
1015 Assets_Tracking_Flag, -- assets_tracking_flag
1016 distribution_line_number, -- dist. line number
1017 Line_Type_Lookup_Code, -- line_type_lookup_code
1018 Po_Distribution_Id, -- po_distribution_id
1019 (-1)*Base_Amount, -- base_amount
1020 decode(project_id,NULL,'E', 'N'), -- Pa_addition_flag
1021 'N', --Encumbered_Flag,
1022 'N', --Accrual_Posted_Flag,
1023 'N', --Cash_Posted_Flag,
1024 FND_GLOBAL.login_id, -- last_update_login
1025 sysdate, --Creation_Date,
1026 FND_GLOBAL.user_id, --Created_By,
1027 (-1)*Stat_Amount, -- Stat_Amount
1028 attribute11, -- attribute11,
1029 attribute12, -- attribute12,
1030 attribute13, -- attribute13,
1031 attribute14, -- attribute14,
1032 attribute6, -- attribute6,
1033 attribute7, -- attribute7,
1034 attribute8, -- attribute8,
1035 attribute9, -- attribute9,
1036 attribute10, -- attribute10,
1037 attribute15, -- attribute15,
1038 'N', -- Reversal_Flag,
1039 invoice_id, -- parent_invoice_id
1040 Income_Tax_Region, -- income_tax_region
1041 NULL, -- final_match_flag
1042 -- Removed for bug 4277744
1043 -- Ussgl_Transaction_Code, -- ussgl_transaction_code
1044 -- Ussgl_Trx_Code_Context, -- ussgal_trx_code_contextt,
1045 expenditure_item_date, -- expenditure_item_date
1046 Expenditure_Organization_Id, -- expenditure_orgnization_id
1047 Expenditure_Type, -- expenditure_type
1048 (-1)*Pa_Quantity, -- Pa_quantity
1049 Project_Id, -- project_id
1050 Task_Id, -- task_id
1051 (-1)*Quantity_Variance, -- quantity_variance
1052 (-1)*Base_Quantity_Variance, -- base quantity_variance
1053 awt_flag, -- awt_flag
1054 awt_group_id, --awt_group_id,
1055 awt_tax_rate_id, --awt_tax_rate_id
1056 awt_gross_amount, --awt_gross_amount
1057 reference_1, -- reference_1
1058 reference_2, -- reference_2
1059 other_invoice_id, -- other_invoice_id
1060 awt_invoice_id, -- awt_invoice_id
1061 awt_origin_group_id, -- awt_origin_group_id
1062 FND_GLOBAL.prog_appl_id, --program_application_id
1063 FND_GLOBAL.conc_program_id, --program_id
1064 SYSDATE, --program_update_date,
1065 FND_GLOBAL.conc_request_id, --request_id
1066 award_id, -- award_id
1067 start_expense_date, -- start_expense_date
1068 merchant_document_number, -- merchant_document_number
1069 merchant_name, -- merchant_name
1070 merchant_tax_reg_number, -- merchant_tax_reg_number
1071 merchant_taxpayer_id, -- merchant_taxpayer_id
1072 country_of_supply, -- country_of_supply
1073 merchant_reference, -- merchant_reference
1074 invoice_distribution_id, --Parent_Reversal_Id
1075 rcv_transaction_id, -- rcv_transaction_id
1076 dist_match_type, -- dist_match_type
1077 matched_uom_lookup_code, -- matched_uom_lookup_code
1078 /* Bug#15977829
1079 global_attribute_category, -- global_attribute_category */
1080 DECODE(l_copy_gdf,'N',NULL,'Y',Global_Attribute_Category),
1081 --Bug#13464635
1082 /* global_attribute1, -- global_attribute1
1083 global_attribute2, -- global_attribute2
1084 global_attribute3, -- global_attribute3 */
1085 DECODE(l_copy_gdf,'N',NULL,'Y',Global_Attribute1),
1086 DECODE(l_copy_gdf,'N',NULL,'Y',Global_Attribute2),
1087 DECODE(l_copy_gdf,'N',NULL,'Y',Global_Attribute3),
1088 --End Bug#13464635
1089 global_attribute4, -- global_attribute4
1090 global_attribute5, -- global_attribute5
1091 global_attribute6, -- global_attribute6
1092 global_attribute7, -- global_attribute7
1093 global_attribute8, -- global_attribute8
1094 global_attribute9, -- global_attribute9
1095 global_attribute10, -- global_attribute10
1096 global_attribute11, -- global_attribute11
1097 global_attribute12, -- global_attribute12
1098 global_attribute13, -- global_attribute13
1099 global_attribute14, -- global_attribute14
1100 global_attribute15, -- global_attribute15
1101 global_attribute16, -- global_attribute16
1102 global_attribute17, -- global_attribute17
1103 global_attribute18, -- global_attribute18
1104 global_attribute19, -- global_attribute19
1105 global_attribute20, -- global_attribute20
1106 receipt_verified_flag, --receipt_verified_flag
1107 receipt_required_flag, --receipt_required_flag
1108 receipt_missing_flag, --receipt_missing_flag
1109 justification, --justification
1110 expense_Group, --expense_Group
1111 end_Expense_Date, --end_Expense_Date
1112 receipt_Currency_Code, --receipt_Currency_Code
1113 receipt_Conversion_Rate, --receipt_Conversion_Rate
1114 receipt_Currency_Amount, --receipt_Currency_Amount
1115 daily_Amount, --daily_Amount
1116 web_Parameter_Id, --web_Parameter_Id
1117 adjustment_Reason, --adjustment_Reason
1118 credit_Card_Trx_Id, --credit_Card_Trx_Id
1119 company_Prepaid_Invoice_Id,--company_Prepaid_Invoice_Id
1120 org_id, -- org_id
1121 (-1)*rounding_amt, -- rounding_amt
1122 NULL, -- charge_applicable_to_dist_id
1123 invoice_distribution_id, -- corrected_invoice_dist_id
1124 NULL, -- related_id
1125 asset_book_type_code, -- asset_book_type_code
1126 asset_category_id, -- asset_category_id
1127 NULL, -- accounting_event_id -- bug 5152035
1128 'N', -- cancellation_flag
1129 'N' -- rcv_charge_addition_flag
1130 FROM ap_invoice_distributions_all
1131 WHERE invoice_id = P_Invoice_Lines_Tab(i).invoice_id
1132 AND invoice_line_number = P_Invoice_Lines_Tab(i).line_number
1133 AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TERV', 'TRV',
1134 'IPV', 'ERV')
1135 -- This to exclude the tax distributions created in the case of inclusive calculation
1136 -- of taxes since for the exclusive case the TAX lines are not included in the
1137 -- pl/sql table. Also exclude the variances created by AP. Those will
1138 -- be created during validation of the invoice.
1139 AND NVL(reversal_flag, 'N') <> 'Y');
1140
1141
1142 ----------------------------------------------------------------------------
1143 l_debug_info := 'Update charge_applicable_to_dist_id for allocation info ...';
1144 ----------------------------------------------------------------------------
1145 UPDATE ap_invoice_distributions_all aid
1146 SET aid.charge_applicable_to_dist_id =
1147 (SELECT d1.invoice_distribution_id
1148 FROM ap_invoice_distributions_all d,
1149 ap_invoice_distributions_all cor,
1150 ap_invoice_distributions_all d1
1151 WHERE d.invoice_id = aid.invoice_id
1152 AND d.invoice_distribution_id = aid.invoice_distribution_id
1153 AND d.corrected_invoice_dist_id = cor.invoice_distribution_id
1154 AND cor.charge_applicable_to_dist_id IS NOT NULL
1155 AND d1.corrected_invoice_dist_id = cor.charge_applicable_to_dist_id)
1156 WHERE aid.invoice_id = P_Invoice_Id;
1157
1158 ----------------------------------------------------------------------------
1159 l_debug_info := 'MRC Maintenance...';
1160 ----------------------------------------------------------------------------
1161 SELECT aid.invoice_distribution_id
1162 BULK COLLECT INTO l_key_value_list
1163 FROM ap_invoice_distributions aid
1164 WHERE aid.invoice_id = P_Invoice_Id
1165 AND aid.invoice_line_number = l_line_number;
1166
1167 l_key_value_list.DELETE;
1168
1169 END IF;
1170
1171 END LOOP;
1172 RETURN TRUE;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 IF (SQLCODE <> -20001) THEN
1177 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1178 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1179 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1180 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1181 ' P_Invoice_Id = '||P_Invoice_Id||
1182 ' P_Error_Code = '||P_Error_Code||
1183 ' P_Calling_Sequence = '||P_Calling_Sequence);
1184 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1185 END IF;
1186
1187 APP_EXCEPTION.RAISE_EXCEPTION;
1188
1189 END Full_Reversal;
1190
1191 /*=============================================================================
1192 | FUNCTION - Quick_Credit()
1193 |
1194 | DESCRIPTION
1195 | Public function that will include all the quick credit functionality.
1196 | This API is called from the Invoice Workbench at commit.
1197 | This function returns TRUE if the full reversal for the invoice goes through
1198 | or FALSE and an error code otherwise.
1199 |
1200 | PARAMETERS
1201 | P_Invoice_Id - quick credit invoice id
1202 | P_Vendor_Id_For_Invoice - vendor id for the debit or credit memo
1203 | P_DM_gl_date - gl_date for the credit/debit memo
1204 | P_credited_Invoice_id - Invoice id for the credited invoice
1205 | P_error_code - Error code to be returned when the rules are not followed
1206 | P_calling_sequence - Calling sequence
1207 |
1208 | MODIFICATION HISTORY
1209 | Date Author Description of Change
1210 | 29-JUL-2003 SYIDNER Creation
1211 |
1212 *============================================================================*/
1213
1214 FUNCTION Quick_Credit(
1215 P_Invoice_Id IN NUMBER,
1216 P_Vendor_Id_For_Invoice IN NUMBER,
1217 P_Dm_Gl_Date IN DATE,
1218 P_Dm_Org_Id IN NUMBER,
1219 P_Credited_Invoice_Id IN NUMBER,
1220 P_error_code OUT NOCOPY VARCHAR2,
1221 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
1222
1223 IS
1224
1225 l_debug_info VARCHAR2(240);
1226 l_curr_calling_sequence VARCHAR2(4000);
1227
1228 l_inv_header_rec ap_invoices_all%ROWTYPE;
1229 l_inv_line_list Inv_Line_Tab_Type;
1230
1231 CURSOR Invoice_Header IS
1232 SELECT *
1233 FROM ap_invoices_all
1234 WHERE invoice_id = P_Credited_Invoice_Id;
1235
1236 CURSOR Invoice_Lines IS
1237 SELECT *
1238 FROM ap_invoice_lines_all
1239 WHERE invoice_id = P_Credited_Invoice_Id
1240 AND line_type_lookup_code <> 'TAX'
1241 AND (NVL(discarded_flag, 'N' ) <> 'Y'
1242 OR NVL(cancelled_flag, 'N' ) <> 'Y')
1243 ORDER BY line_number;
1244
1245 l_return_value BOOLEAN := TRUE;
1246
1247 BEGIN
1248
1249 l_curr_calling_sequence := 'AP_QUICK_CREDIT_PKG.Quick_Credit<-' ||
1250 P_calling_sequence;
1251
1252 -----------------------------------------------------------------
1253 l_debug_info := 'Step 1: Populating invoice and lines collections'||
1254 'for credited invoices';
1255 -----------------------------------------------------------------
1256 BEGIN
1257 OPEN Invoice_Header;
1258 FETCH Invoice_Header INTO l_inv_header_rec;
1259 CLOSE Invoice_Header;
1260 END;
1261
1262 BEGIN
1263 OPEN Invoice_Lines;
1264 FETCH Invoice_Lines
1265 BULK COLLECT INTO l_inv_line_list;
1266 CLOSE Invoice_Lines;
1267 END;
1268
1269 -------------------------------------------------------------------
1270 l_debug_info := 'Step 2: Calling Validating Rules';
1271 -------------------------------------------------------------------
1272 IF NOT (AP_QUICK_CREDIT_PKG.Validating_Rules(
1273 P_Invoice_Id => P_Invoice_Id,
1274 P_Vendor_Id_For_Invoice => P_Vendor_Id_For_Invoice,
1275 P_Dm_Gl_Date => P_Dm_Gl_Date,
1276 P_Invoice_Header_Rec => l_inv_header_rec,
1277 P_Invoice_Lines_Tab => l_inv_line_list,
1278 P_error_code => P_error_code,
1279 P_calling_sequence => l_curr_calling_sequence)) THEN
1280
1281 l_return_value := FALSE;
1282 END IF;
1283
1284 --------------------------------------------------------------------
1285 l_debug_info := 'Step 3: Calling Full Reverse';
1286 --------------------------------------------------------------------
1287 IF (l_return_value = TRUE) THEN
1288 IF NOT (AP_QUICK_CREDIT_PKG.Full_Reversal(
1289 P_Invoice_Id => P_Invoice_Id,
1290 P_Dm_Gl_Date => P_Dm_Gl_Date,
1291 P_Dm_Org_Id => P_Dm_Org_Id,
1292 P_Invoice_Header_Rec => l_inv_header_rec,
1293 P_Invoice_Lines_Tab => l_inv_line_list,
1294 P_error_code => P_error_code,
1295 P_calling_sequence => l_curr_calling_sequence)) THEN
1296
1297 l_return_value := FALSE;
1298 END IF;
1299 END IF;
1300
1301 --------------------------------------------------------------------
1302 l_debug_info := 'Step 4: Calling tax ';
1303 --------------------------------------------------------------------
1304 IF (l_return_value = TRUE) THEN
1305 IF NOT (AP_ETAX_PKG.Calling_eTax(
1306 P_Invoice_id => P_Invoice_Id,
1307 P_Calling_Mode => 'REVERSE INVOICE',
1308 P_Override_Status => NULL,
1309 P_Line_Number_To_Delete => NULL,
1310 P_All_Error_Messages => 'N',
1311 P_error_code => P_error_code,
1312 P_calling_sequence => l_curr_calling_sequence)) THEN
1313
1314 l_return_value := FALSE;
1315
1316 END IF;
1317 END IF;
1318
1319 RETURN l_return_value;
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 IF (SQLCODE <> -20001) THEN
1324 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1325 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1326 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1327 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1328 ' P_Invoice_Id = '||P_Invoice_Id||
1329 ' P_Vendor_Id_For_Invoice = '||P_Vendor_Id_For_Invoice||
1330 ' P_Credited_Invoice_Id = '||P_Credited_Invoice_Id||
1331 ' P_Error_Code = '||P_Error_Code||
1332 ' P_Calling_Sequence = '||P_Calling_Sequence);
1333 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1334
1335 END IF;
1336
1337 IF ( Invoice_Header%ISOPEN ) THEN
1338 CLOSE Invoice_Header;
1339 END IF;
1340
1341 IF ( Invoice_Lines%ISOPEN ) THEN
1342 CLOSE Invoice_Lines;
1343 END IF;
1344 APP_EXCEPTION.RAISE_EXCEPTION;
1345
1346 END Quick_Credit;
1347 END AP_QUICK_CREDIT_PKG;