[Home] [Help]
PACKAGE BODY: APPS.AP_QUICK_CREDIT_PKG
Source
1 PACKAGE BODY AP_QUICK_CREDIT_PKG AS
2 /* $Header: apqkcreb.pls 120.12 2006/04/13 07:48:26 sfeng noship $ */
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
309 CURSOR corrected_dist_po(c_invoice_id NUMBER,
310 c_line_number NUMBER) IS
311 SELECT aid.po_distribution_id po_distribution_id,
312 null invoice_distribution_id, --will be populated by the corr API
313 aid.invoice_distribution_id corrected_inv_dist_id,
314 (-1)*aid.quantity_invoiced corrected_quantity,
315 (-1)*aid.amount amount,
316 null base_amount, --will be populated by the corr API
317 null rounding_amt, --will be populated by the corr API
318 aid.unit_price unit_price,
319 null pa_quantity,
320 aid.dist_code_combination_id dist_ccid
321 FROM ap_invoice_distributions aid
322 WHERE aid.invoice_id = c_invoice_id
323 AND aid.invoice_line_number = c_line_number
324 AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TERV',
325 'TRV', 'IPV', 'ERV')
326 AND NVL(reversal_flag, 'N') <> 'Y';
327
328
329 CURSOR corrected_dist_rcv(c_invoice_id NUMBER,
330 c_line_number NUMBER) IS
331 SELECT aid.po_distribution_id po_distribution_id,
332 null invoice_distribution_id, --will be populated by the corr API
333 aid.invoice_distribution_id corrected_inv_dist_id,
334 (-1)*aid.quantity_invoiced corrected_quantity,
335 (-1)*aid.amount amount,
336 null base_amount, --will be populated by the corr API
337 null rounding_amt, --will be populated by the corr API
338 aid.unit_price unit_price,
339 null pa_quantity,
340 aid.dist_code_combination_id dist_ccid
341 FROM ap_invoice_distributions aid
342 WHERE aid.invoice_id = c_invoice_id
343 AND aid.invoice_line_number = c_line_number
344 AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TERV',
345 'TRV', 'IPV', 'ERV')
346 AND NVL(reversal_flag, 'N') <> 'Y';
347
348 BEGIN
349
350 l_curr_calling_sequence := 'AP_QUICK_CREDIT_PKG.Full_Reversal<-' ||
351 P_calling_sequence;
352
353 ----------------------------------------------------------------------------
354 l_debug_info := 'Getting period name for the lines based on the gl_date for '||
355 'the credit/debit memo';
356 ----------------------------------------------------------------------------
357
358 l_period_name := AP_INVOICES_PKG.Get_Period_Name(
359 l_invoice_date => P_Dm_Gl_Date,
360 l_receipt_date => null,
361 l_org_id => P_Dm_Org_Id);
362
363 ----------------------------------------------------------------------------
364 l_debug_info := 'Get wfapproval information ';
365 ----------------------------------------------------------------------------
366 BEGIN
367 SELECT approval_workflow_flag
368 INTO l_wfapproval_flag
369 FROM ap_system_parameters_all
370 WHERE org_id = P_Dm_Org_Id;
371 END;
372
373 if NVL(l_wfapproval_flag,'N') = 'Y' then
374 l_wfapproval_status := 'REQUIRED';
375 else
376 l_wfapproval_status := 'NOT REQUIRED';
377 end if;
378
379
380 ----------------------------------------------------------------------------
381 l_debug_info := 'For every line...';
382 ----------------------------------------------------------------------------
383
384 FOR i in P_Invoice_Lines_Tab.FIRST..P_Invoice_Lines_Tab.LAST LOOP
385
386 ----------------------------------------------------------------------------
387 l_debug_info := 'Verify if it is a PO/RCV matched line';
388 ----------------------------------------------------------------------------
389
390 IF (P_Invoice_Lines_Tab(i).po_line_location_id IS NOT NULL AND
391 P_Invoice_Lines_Tab(i).rcv_transaction_id IS NULL) THEN
392 ------------------------------------------------------------------------
393 l_debug_info := 'The line PO matched populate dist pl/sql table';
394 ------------------------------------------------------------------------
395 BEGIN
396 OPEN corrected_dist_po(P_Invoice_Lines_Tab(i).invoice_id,
397 P_Invoice_Lines_Tab(i).line_number);
398 FETCH corrected_dist_po
399 BULK COLLECT INTO l_Corr_Dist_Tab_Po;
400 CLOSE corrected_dist_po;
401 END;
402
403 /* For Amount Based Matching */
404 ------------------------------------------------------------------------
405 l_debug_info := 'retrieving the matching basis from po';
406 ------------------------------------------------------------------------
407 SELECT plt.matching_basis
408 INTO l_matching_basis
409 FROM po_line_locations_all pll,
410 po_lines_all pl,
411 po_line_types plt
412 WHERE pll.line_location_id = P_Invoice_Lines_Tab(i).po_line_location_id
413 AND pll.po_line_id = pl.po_line_id
414 AND pl.line_type_id = plt.line_type_id;
415
416 IF l_matching_basis = 'AMOUNT' THEN
417
418 ------------------------------------------------------------------------
419 l_debug_info := 'Call Amount correction API for PO match';
420 ------------------------------------------------------------------------
421 AP_PO_AMT_MATCH_PKG.Amount_Correct_Inv_PO(
422 X_Invoice_Id => P_Invoice_id,
423 X_Invoice_Line_Number => NULL,
424 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
425 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
426 X_Match_Mode => 'CR-PD',
427 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
428 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
429 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Po,
430 X_Final_Match_Flag => 'N',
431 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
432 X_Calling_Sequence => l_curr_calling_sequence);
433
434 ELSE
435
436 ------------------------------------------------------------------------
437 l_debug_info := 'Call quantity correction API for PO match';
438 ------------------------------------------------------------------------
439 AP_MATCHING_PKG.Price_Quantity_Correct_Inv_PO(
440 X_Invoice_Id => P_Invoice_id,
441 X_Invoice_Line_Number => NULL,
442 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
443 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
444 X_Correction_Type => 'QTY_CORRECTION',
445 X_Match_Mode => 'CR-PD',
446 X_Correction_Quantity => (-1)*P_Invoice_Lines_Tab(i).quantity_invoiced,
447 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
448 X_Correction_Price => P_Invoice_Lines_Tab(i).unit_price,
449 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
450 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Po,
451 X_Final_Match_Flag => 'N',
452 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
453 X_Calling_Sequence => l_curr_calling_sequence);
454
455 END IF;
456
457 ELSIF (P_Invoice_Lines_Tab(i).po_line_location_id IS NOT NULL AND
458 P_Invoice_Lines_Tab(i).rcv_transaction_id IS NOT NULL) THEN
459 ----------------------------------------------------------------------------
460 l_debug_info := 'The line RCV matched populate dist pl/sql table';
461 ----------------------------------------------------------------------------
462 BEGIN
463 OPEN corrected_dist_rcv(P_Invoice_Lines_Tab(i).invoice_id,
464 P_Invoice_Lines_Tab(i).line_number);
465 FETCH corrected_dist_rcv
466 BULK COLLECT INTO l_Corr_Dist_Tab_Rcv;
467 CLOSE corrected_dist_rcv;
468 END;
469
470 /* For Amount Based Matching */
471 ------------------------------------------------------------------------
472 l_debug_info := 'retrieving the matching basis from po';
473 ------------------------------------------------------------------------
474 SELECT plt.matching_basis
475 INTO l_matching_basis
476 FROM po_line_locations_all pll,
477 po_lines_all pl,
478 po_line_types plt
479 WHERE pll.line_location_id = P_Invoice_Lines_Tab(i).po_line_location_id
480 AND pll.po_line_id = pl.po_line_id
481 AND pl.line_type_id = plt.line_type_id;
482
483 IF l_matching_basis = 'AMOUNT' THEN
484
485 ------------------------------------------------------------------------
486 l_debug_info := 'Call Amount correction API for receipt match';
487 ------------------------------------------------------------------------
488 AP_RCT_AMT_MATCH_PKG.Amount_Correct_Inv_RCV(
489 X_Invoice_Id => P_Invoice_id,
490 X_Invoice_Line_Number => NULL,
491 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
492 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
493 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
494 X_Match_Mode => 'CR-PD',
495 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
496 X_Rcv_Transaction_Id => P_Invoice_Lines_Tab(i).rcv_transaction_id,
497 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Rcv,
498 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
499 X_Calling_Sequence => l_curr_calling_sequence);
500
501 ELSE
502
503 ------------------------------------------------------------------------
504 l_debug_info := 'Call quantity correction API for receipt match';
505 ------------------------------------------------------------------------
506 AP_RECT_MATCH_PKG.Price_Quantity_Correct_Inv_RCV(
507 X_Invoice_Id => P_Invoice_id,
508 X_Invoice_Line_Number => NULL,
509 X_Corrected_Invoice_Id => P_Invoice_Lines_Tab(i).invoice_id,
510 X_Corrected_Line_Number => P_Invoice_Lines_Tab(i).line_number,
511 X_Correction_Type => 'QTY_CORRECTION',
512 X_Match_Mode => 'CR-PD',
513 X_Correction_Quantity => (-1)*P_Invoice_Lines_Tab(i).quantity_invoiced,
514 X_Correction_Amount => (-1)*P_Invoice_Lines_Tab(i).amount,
515 X_Correction_Price => P_Invoice_Lines_Tab(i).unit_price,
516 X_Po_Line_Location_Id => P_Invoice_Lines_Tab(i).po_line_location_id,
517 X_Rcv_Transaction_Id => P_Invoice_Lines_Tab(i).rcv_transaction_id,
518 X_Corr_Dist_Tab => l_Corr_Dist_Tab_Rcv,
519 X_Uom_Lookup_Code => P_Invoice_Lines_Tab(i).unit_meas_lookup_code,
520 X_Calling_Sequence => l_curr_calling_sequence);
521
522 END IF;
523
524 ELSE
525 ----------------------------------------------------------------------------
526 l_debug_info := 'The line is NOT PO or RCV matched - Create line';
527 ----------------------------------------------------------------------------
528 l_line_number := AP_INVOICES_PKG.get_max_line_number(P_invoice_id)+1;
529
530 BEGIN
531 INSERT INTO ap_invoice_lines_all(
532 invoice_id,
533 line_number,
534 line_type_lookup_code,
535 requester_id,
536 description,
537 line_source,
538 org_id,
539 inventory_item_id,
540 item_description,
541 serial_number,
542 manufacturer,
543 model_number,
544 warranty_number,
545 generate_dists,
546 match_type,
547 distribution_set_id,
548 account_segment,
549 balancing_segment,
550 cost_center_segment,
551 overlay_dist_code_concat,
552 default_dist_ccid,
553 prorate_across_all_items,
554 line_group_number,
555 accounting_date,
556 period_name,
557 deferred_acctg_flag,
558 def_acctg_start_date,
559 def_acctg_end_date,
560 def_acctg_number_of_periods,
561 def_acctg_period_type,
562 set_of_books_id,
563 amount,
564 base_amount,
565 rounding_amt,
566 quantity_invoiced,
567 unit_meas_lookup_code,
568 unit_price,
569 wfapproval_status,
570 -- ussgl_transaction_code, - Bug 4277744
571 discarded_flag,
572 original_amount,
573 original_base_amount,
574 original_rounding_amt,
575 cancelled_flag,
576 income_tax_region,
577 type_1099,
578 stat_amount,
579 prepay_invoice_id,
580 prepay_line_number,
581 invoice_includes_prepay_flag,
582 corrected_inv_id,
583 corrected_line_number,
584 po_header_id,
585 po_line_id,
586 po_release_id,
587 po_line_location_id,
588 po_distribution_id,
589 rcv_transaction_id,
590 final_match_flag,
591 assets_tracking_flag,
592 asset_book_type_code,
593 asset_category_id,
594 project_id,
595 task_id,
596 expenditure_type,
597 expenditure_item_date,
598 expenditure_organization_id,
599 pa_quantity,
600 pa_cc_ar_invoice_id,
601 pa_cc_ar_invoice_line_num,
602 pa_cc_processed_code,
603 award_id,
604 awt_group_id,
605 reference_1,
606 reference_2,
607 receipt_verified_flag,
608 receipt_required_flag,
609 receipt_missing_flag,
610 justification,
611 expense_group,
612 start_expense_date,
613 end_expense_date,
614 receipt_currency_code,
615 receipt_conversion_rate,
616 receipt_currency_amount,
617 daily_amount,
618 web_parameter_id,
619 adjustment_reason,
620 merchant_document_number,
621 merchant_name,
622 merchant_reference,
623 merchant_tax_reg_number,
624 merchant_taxpayer_id,
625 country_of_supply,
626 credit_card_trx_id,
627 company_prepaid_invoice_id,
628 cc_reversal_flag,
629 attribute_category,
630 attribute1,
631 attribute2,
632 attribute3,
633 attribute4,
634 attribute5,
635 attribute6,
636 attribute7,
637 attribute8,
638 attribute9,
639 attribute10,
640 attribute11,
641 attribute12,
642 attribute13,
643 attribute14,
644 attribute15,
645 global_attribute_category,
646 global_attribute1,
647 global_attribute2,
648 global_attribute3,
649 global_attribute4,
650 global_attribute5,
651 global_attribute6,
652 global_attribute7,
653 global_attribute8,
654 global_attribute9,
655 global_attribute10,
656 global_attribute11,
657 global_attribute12,
658 global_attribute13,
659 global_attribute14,
660 global_attribute15,
661 global_attribute16,
662 global_attribute17,
663 global_attribute18,
664 global_attribute19,
665 global_attribute20,
666 creation_date,
667 created_by,
668 last_updated_by,
669 last_update_date,
670 last_update_login,
671 program_application_id,
672 program_id,
673 program_update_date,
674 request_id,
675 purchasing_category_id)
676 VALUES (
677 P_Invoice_id, -- invoice_id
678 l_line_number, -- line_number
679 P_Invoice_Lines_Tab(i).line_type_lookup_code, -- line_type_lookup_code
680 P_Invoice_Lines_Tab(i).requester_id, -- requester_id
681 P_Invoice_Lines_Tab(i).description, -- description
682 l_line_source, -- line_source
683 P_Dm_Org_Id, -- org_id
684 P_Invoice_Lines_Tab(i).inventory_item_id, -- inventory_item_id
685 P_Invoice_Lines_Tab(i).Item_description, -- item_description
686 P_Invoice_Lines_Tab(i).serial_number, -- serial_number
687 P_Invoice_Lines_Tab(i).Manufacturer, -- manufacturer
688 P_Invoice_Lines_Tab(i).Model_Number, -- model_number
689 P_Invoice_Lines_Tab(i).warranty_number, -- warranty_number
690 P_Invoice_Lines_Tab(i).generate_dists, -- generate_dists
691 P_Invoice_Lines_Tab(i).match_type, -- match_type
692 P_Invoice_Lines_Tab(i).distribution_set_id, -- distribution_set_id
693 P_Invoice_Lines_Tab(i).account_segment, -- account_segment
694 P_Invoice_Lines_Tab(i).balancing_segment, -- balancing_segment
695 P_Invoice_Lines_Tab(i).cost_center_segment, -- cost_center_segment
696 P_Invoice_Lines_Tab(i).overlay_dist_code_concat, -- overlay_dist_code_concat
697 P_Invoice_Lines_Tab(i).default_dist_ccid, -- default_dist_ccid
698 P_Invoice_Lines_Tab(i).prorate_across_all_items, -- prorate_across_all_items
699 P_Invoice_Lines_Tab(i).line_group_number, -- line_group_number
700 P_Dm_Gl_Date, -- accounting_date
701 l_period_name, -- period_name
702 P_Invoice_Lines_Tab(i).deferred_acctg_flag, -- deferred_acctg_flag
703 P_Invoice_Lines_Tab(i).def_acctg_start_date, -- def_acctg_start_date
704 P_Invoice_Lines_Tab(i).def_acctg_end_date, -- def_acctg_end_date
705 P_Invoice_Lines_Tab(i).def_acctg_number_of_periods, -- def_acctg_number_of_periods
706 P_Invoice_Lines_Tab(i).def_acctg_period_type, -- def_acctg_period_type
707 P_Invoice_Lines_Tab(i).set_of_books_id, -- set_of_books_id
708 (-1)*P_Invoice_Lines_Tab(i).amount, -- amount
709 P_Invoice_Lines_Tab(i).base_amount, -- base_amount
710 P_Invoice_Lines_Tab(i).rounding_amt, -- rounding_amt
711 P_Invoice_Lines_Tab(i).quantity_invoiced, -- quantity_invoiced
712 P_Invoice_Lines_Tab(i).unit_meas_lookup_code, -- unit_meas_lookup_code
713 P_Invoice_Lines_Tab(i).unit_price, -- unit_price
714 l_wfapproval_status, -- wfapproval_status
715 -- Bug 4277744
716 -- P_Invoice_Lines_Tab(i).ussgl_transaction_code, -- ussgl_transaction_code
717 'N', -- discarded_flag
718 P_Invoice_Lines_Tab(i).original_amount, -- original_amount
719 P_Invoice_Lines_Tab(i).original_base_amount, -- original_base_amount
720 P_Invoice_Lines_Tab(i).original_rounding_amt, -- original_rounding_amt
721 P_Invoice_Lines_Tab(i).cancelled_flag, -- cancelled_flag
722 P_Invoice_Lines_Tab(i).income_tax_region, -- income_tax_region
723 P_Invoice_Lines_Tab(i).type_1099, -- type_1099
724 P_Invoice_Lines_Tab(i).stat_amount, -- stat_amount
725 P_Invoice_Lines_Tab(i).prepay_invoice_id, -- prepay_invoice_id
726 P_Invoice_Lines_Tab(i).prepay_line_number, -- prepay_line_number
727 P_Invoice_Lines_Tab(i).invoice_includes_prepay_flag, -- invoice_includes_prepay_flag
728 P_Invoice_Lines_Tab(i).invoice_id, -- corrected_inv_id
729 P_Invoice_Lines_Tab(i).line_number, -- corrected_line_number
730 P_Invoice_Lines_Tab(i).po_header_id, -- po_header_id
731 P_Invoice_Lines_Tab(i).po_line_id, -- po_line_id
732 P_Invoice_Lines_Tab(i).po_release_id, -- po_release_id
733 P_Invoice_Lines_Tab(i).po_line_location_id, -- po_line_location_id
734 P_Invoice_Lines_Tab(i).po_distribution_id, -- po_distribution_id
735 P_Invoice_Lines_Tab(i).rcv_transaction_id, -- rcv_transaction_id
736 P_Invoice_Lines_Tab(i).final_match_flag, -- final_match_flag
737 P_Invoice_Lines_Tab(i).assets_tracking_flag, -- assets_tracking_flag
738 P_Invoice_Lines_Tab(i).asset_book_type_code, -- asset_book_type_code,
739 P_Invoice_Lines_Tab(i).asset_category_id, -- asset_category_id
740 P_Invoice_Lines_Tab(i).project_id, -- project_id
741 P_Invoice_Lines_Tab(i).task_id, -- task_id
742 P_Invoice_Lines_Tab(i).expenditure_type, -- expenditure_type
743 P_Invoice_Lines_Tab(i).expenditure_item_date, -- expenditure_item_date
744 P_Invoice_Lines_Tab(i).expenditure_organization_id,-- expenditure_organization_id
745 P_Invoice_Lines_Tab(i).pa_quantity, -- pa_quantity
746 P_Invoice_Lines_Tab(i).pa_cc_ar_invoice_id, -- pa_cc_ar_invoice_id
747 P_Invoice_Lines_Tab(i).pa_cc_ar_invoice_line_num, -- pa_cc_ar_invoice_line_num
748 P_Invoice_Lines_Tab(i).pa_cc_processed_code, -- pa_cc_processed_code
749 P_Invoice_Lines_Tab(i).award_id, -- award_id
750 P_Invoice_Lines_Tab(i).awt_group_id, -- awt_group_id
751 P_Invoice_Lines_Tab(i).reference_1, -- reference_1
752 P_Invoice_Lines_Tab(i).reference_2, -- reference_2
753 P_Invoice_Lines_Tab(i).receipt_verified_flag, -- receipt_verified_flag
754 P_Invoice_Lines_Tab(i).receipt_required_flag, -- receipt_required_flag
755 P_Invoice_Lines_Tab(i).receipt_missing_flag, -- receipt_missing_flag
756 P_Invoice_Lines_Tab(i).justification, -- justification
757 P_Invoice_Lines_Tab(i).expense_group, -- expense_group
758 P_Invoice_Lines_Tab(i).start_expense_date, -- start_expense_date
759 P_Invoice_Lines_Tab(i).end_expense_date, -- end_expense_date
760 P_Invoice_Lines_Tab(i).receipt_currency_code, -- receipt_currency_code
761 P_Invoice_Lines_Tab(i).receipt_conversion_rate, -- receipt_conversion_rate
762 P_Invoice_Lines_Tab(i).receipt_currency_amount, -- receipt_currency_amount
763 P_Invoice_Lines_Tab(i).daily_amount, -- daily_amount
764 P_Invoice_Lines_Tab(i).web_parameter_id, -- web_parameter_id
765 P_Invoice_Lines_Tab(i).adjustment_reason, -- adjustment_reason
766 P_Invoice_Lines_Tab(i).merchant_document_number, -- merchant_document_number
767 P_Invoice_Lines_Tab(i).merchant_name, -- merchant_name
768 P_Invoice_Lines_Tab(i).merchant_reference, -- merchant_reference
769 P_Invoice_Lines_Tab(i).merchant_tax_reg_number, -- merchant_tax_reg_number
770 P_Invoice_Lines_Tab(i).merchant_taxpayer_id, -- merchant_taxpayer_id
771 P_Invoice_Lines_Tab(i).country_of_supply, -- country_of_supply
772 P_Invoice_Lines_Tab(i).credit_card_trx_id, -- credit_card_trx_id
773 P_Invoice_Lines_Tab(i).company_prepaid_invoice_id, -- company_prepaid_invoice_id
774 P_Invoice_Lines_Tab(i).cc_reversal_flag, -- cc_reversal_flag
775 P_Invoice_Lines_Tab(i).attribute_category, -- attribute_category
776 P_Invoice_Lines_Tab(i).attribute1, -- attribute1
777 P_Invoice_Lines_Tab(i).attribute2, -- attribute2
778 P_Invoice_Lines_Tab(i).attribute3, -- attribute3
779 P_Invoice_Lines_Tab(i).attribute4, -- attribute4
780 P_Invoice_Lines_Tab(i).attribute5, -- attribute5
781 P_Invoice_Lines_Tab(i).attribute6, -- attribute6
782 P_Invoice_Lines_Tab(i).attribute7, -- attribute7
783 P_Invoice_Lines_Tab(i).attribute8, -- attribute8
784 P_Invoice_Lines_Tab(i).attribute9, -- attribute9
785 P_Invoice_Lines_Tab(i).attribute10, -- attribute10
786 P_Invoice_Lines_Tab(i).attribute11, -- attribute11
787 P_Invoice_Lines_Tab(i).attribute12, -- attribute12
788 P_Invoice_Lines_Tab(i).attribute13, -- attribute13
789 P_Invoice_Lines_Tab(i).attribute14, -- attribute14
790 P_Invoice_Lines_Tab(i).attribute15, -- attribute15
791 P_Invoice_Lines_Tab(i).global_attribute_category, -- global_attribute_category
792 P_Invoice_Lines_Tab(i).global_attribute1, -- global_attribute1
793 P_Invoice_Lines_Tab(i).global_attribute2, -- global_attribute2
794 P_Invoice_Lines_Tab(i).global_attribute3, -- global_attribute3
795 P_Invoice_Lines_Tab(i).global_attribute4, -- global_attribute4
796 P_Invoice_Lines_Tab(i).global_attribute5, -- global_attribute5
797 P_Invoice_Lines_Tab(i).global_attribute6, -- global_attribute6
798 P_Invoice_Lines_Tab(i).global_attribute7, -- global_attribute7
799 P_Invoice_Lines_Tab(i).global_attribute8, -- global_attribute8
800 P_Invoice_Lines_Tab(i).global_attribute9, -- global_attribute9
801 P_Invoice_Lines_Tab(i).global_attribute10, -- global_attribute10
802 P_Invoice_Lines_Tab(i).global_attribute11, -- global_attribute11
803 P_Invoice_Lines_Tab(i).global_attribute12, -- global_attribute12
804 P_Invoice_Lines_Tab(i).global_attribute13, -- global_attribute13
805 P_Invoice_Lines_Tab(i).global_attribute14, -- global_attribute14
806 P_Invoice_Lines_Tab(i).global_attribute15, -- global_attribute15
807 P_Invoice_Lines_Tab(i).global_attribute16, -- global_attribute16
808 P_Invoice_Lines_Tab(i).global_attribute17, -- global_attribute17
809 P_Invoice_Lines_Tab(i).global_attribute18, -- global_attribute18
810 P_Invoice_Lines_Tab(i).global_attribute19, -- global_attribute19
811 P_Invoice_Lines_Tab(i).global_attribute20, -- global_attribute20
812 sysdate, -- creation_date
813 FND_GLOBAL.user_id, -- created_by
814 FND_GLOBAL.user_id, -- last_updated_by
815 sysdate, -- last_update_date
816 FND_GLOBAL.login_id, -- last_update_login
817 P_Invoice_Lines_Tab(i).program_application_id, -- program_application_id
818 P_Invoice_Lines_Tab(i).program_id, -- program_id
819 P_Invoice_Lines_Tab(i).program_update_date, -- program_update_date
820 P_Invoice_Lines_Tab(i).request_id, -- request_id
821 P_Invoice_Lines_Tab(i).purchasing_category_id -- purchasing_category_id
822 );
823
824 END;
825
826 ----------------------------------------------------------------------------
827 l_debug_info := 'create distributions';
828 ----------------------------------------------------------------------------
829 INSERT INTO ap_invoice_distributions_all(
830 invoice_id,
831 invoice_line_number,
832 dist_code_combination_id,
833 invoice_distribution_id,
834 last_update_date,
835 last_updated_by,
836 accounting_date,
837 period_name,
838 set_of_books_id,
839 amount,
840 description,
841 type_1099,
842 tax_code_id,
843 posted_flag,
844 batch_id,
845 quantity_invoiced,
846 corrected_quantity,
847 unit_price,
848 match_status_flag,
849 attribute_category,
850 attribute1,
851 attribute2,
852 attribute3,
853 attribute4,
854 attribute5,
855 prepay_amount_remaining,
856 assets_addition_flag,
857 assets_tracking_flag,
858 distribution_line_number,
859 line_type_lookup_code,
860 po_distribution_id,
861 base_amount,
862 pa_addition_flag,
863 encumbered_flag,
864 accrual_posted_flag,
865 cash_posted_flag,
866 last_update_login,
867 creation_date,
868 created_by,
869 stat_amount,
870 attribute11,
871 attribute12,
872 attribute13,
873 attribute14,
874 attribute6,
875 attribute7,
876 attribute8,
877 attribute9,
878 attribute10,
879 attribute15,
880 reversal_flag,
881 parent_invoice_id,
882 income_tax_region,
883 final_match_flag,
884 -- ussgl_transaction_code, - Bug 4277744
885 -- ussgl_trx_code_context, - Bug 4277744
886 expenditure_item_date,
887 expenditure_organization_id,
888 expenditure_type,
889 pa_quantity,
890 project_id,
891 task_id,
892 quantity_variance,
893 base_quantity_variance,
894 awt_flag,
895 awt_group_id,
896 awt_tax_rate_id,
897 awt_gross_amount,
898 reference_1,
899 reference_2,
900 other_invoice_id,
901 awt_invoice_id,
902 awt_origin_group_id,
903 program_application_id,
904 program_id,
905 program_update_date,
906 request_id,
907 award_id,
908 start_expense_date,
909 merchant_document_number,
910 merchant_name,
911 merchant_tax_reg_number,
912 merchant_taxpayer_id,
913 country_of_supply,
914 merchant_reference,
915 parent_reversal_id,
916 rcv_transaction_id,
917 dist_match_type,
918 matched_uom_lookup_code,
919 global_attribute_category,
920 global_attribute1,
921 global_attribute2,
922 global_attribute3,
923 global_attribute4,
924 global_attribute5,
925 global_attribute6,
926 global_attribute7,
927 global_attribute8,
928 global_attribute9,
929 global_attribute10,
930 global_attribute11,
931 global_attribute12,
932 global_attribute13,
933 global_attribute14,
934 global_attribute15,
935 global_attribute16,
936 global_attribute17,
937 global_attribute18,
938 global_attribute19,
939 global_attribute20,
940 receipt_verified_flag,
941 receipt_required_flag,
942 receipt_missing_flag,
943 justification,
944 expense_Group,
945 end_Expense_Date,
946 receipt_Currency_Code,
947 receipt_Conversion_Rate,
948 receipt_Currency_Amount,
949 daily_Amount,
950 web_Parameter_Id,
951 adjustment_Reason,
952 credit_Card_Trx_Id,
953 company_Prepaid_Invoice_Id,
954 org_id,
955 rounding_amt,
956 charge_applicable_to_dist_id,
957 corrected_invoice_dist_id,
958 related_id,
959 asset_book_type_code,
960 asset_category_id,
961 accounting_event_id,
962 cancellation_flag,
963 --Freight and Special Charges
964 rcv_charge_addition_flag)
965 (SELECT
966 P_Invoice_Id, -- invoice_id
967 l_Line_Number, -- invoice_line_number
968 Dist_Code_Combination_Id, -- dist_code_combination_id
969 ap_invoice_distributions_s.NEXTVAL, -- distribution_id
970 sysdate, -- last_update_date
971 FND_GLOBAL.user_id, -- last_updated_by
972 P_Dm_Gl_Date, -- accounting_date
973 l_period_name, -- period_name
974 Set_Of_Books_Id, -- set_of_book_id
975 (-1)*Amount, -- Amount
976 Description, -- description
977 Type_1099, -- type_1099
978 Tax_Code_Id, -- tax_code_id
979 'N', -- Posted_Flag,
980 Batch_Id, -- batch_id
981 quantity_invoiced, -- Quantity_Invoiced
982 corrected_quantity, -- corrected_quanity
983 unit_price, -- Unit_Price,
984 NULL, -- Match_Status_Flag /* bug 4916530 */
985 attribute_category, -- attribute_category
986 attribute1, -- attribute1
987 attribute2, -- attribute2
988 attribute3, -- attribute3
989 attribute4, -- attribute4
990 attribute5, -- attribute5
991 NULL, --prepay_amount_remaining
992 'U', -- Assets_Addition_Flag
993 Assets_Tracking_Flag, -- assets_tracking_flag
994 distribution_line_number, -- dist. line number
995 Line_Type_Lookup_Code, -- line_type_lookup_code
996 Po_Distribution_Id, -- po_distribution_id
997 (-1)*Base_Amount, -- base_amount
998 decode(project_id,NULL,'E', 'N'), -- Pa_addition_flag
999 'N', --Encumbered_Flag,
1000 'N', --Accrual_Posted_Flag,
1001 'N', --Cash_Posted_Flag,
1002 FND_GLOBAL.login_id, -- last_update_login
1003 sysdate, --Creation_Date,
1004 FND_GLOBAL.user_id, --Created_By,
1005 (-1)*Stat_Amount, -- Stat_Amount
1006 attribute11, -- attribute11,
1007 attribute12, -- attribute12,
1008 attribute13, -- attribute13,
1009 attribute14, -- attribute14,
1010 attribute6, -- attribute6,
1011 attribute7, -- attribute7,
1012 attribute8, -- attribute8,
1013 attribute9, -- attribute9,
1014 attribute10, -- attribute10,
1015 attribute15, -- attribute15,
1016 'N', -- Reversal_Flag,
1017 invoice_id, -- parent_invoice_id
1018 Income_Tax_Region, -- income_tax_region
1019 NULL, -- final_match_flag
1020 -- Removed for bug 4277744
1021 -- Ussgl_Transaction_Code, -- ussgl_transaction_code
1022 -- Ussgl_Trx_Code_Context, -- ussgal_trx_code_contextt,
1023 expenditure_item_date, -- expenditure_item_date
1024 Expenditure_Organization_Id, -- expenditure_orgnization_id
1025 Expenditure_Type, -- expenditure_type
1026 (-1)*Pa_Quantity, -- Pa_quantity
1027 Project_Id, -- project_id
1028 Task_Id, -- task_id
1029 (-1)*Quantity_Variance, -- quantity_variance
1030 (-1)*Base_Quantity_Variance, -- base quantity_variance
1031 awt_flag, -- awt_flag
1032 awt_group_id, --awt_group_id,
1033 awt_tax_rate_id, --awt_tax_rate_id
1034 awt_gross_amount, --awt_gross_amount
1035 reference_1, -- reference_1
1036 reference_2, -- reference_2
1037 other_invoice_id, -- other_invoice_id
1038 awt_invoice_id, -- awt_invoice_id
1039 awt_origin_group_id, -- awt_origin_group_id
1040 FND_GLOBAL.prog_appl_id, --program_application_id
1041 FND_GLOBAL.conc_program_id, --program_id
1042 SYSDATE, --program_update_date,
1043 FND_GLOBAL.conc_request_id, --request_id
1044 award_id, -- award_id
1045 start_expense_date, -- start_expense_date
1046 merchant_document_number, -- merchant_document_number
1047 merchant_name, -- merchant_name
1048 merchant_tax_reg_number, -- merchant_tax_reg_number
1049 merchant_taxpayer_id, -- merchant_taxpayer_id
1050 country_of_supply, -- country_of_supply
1051 merchant_reference, -- merchant_reference
1052 invoice_distribution_id, --Parent_Reversal_Id
1053 rcv_transaction_id, -- rcv_transaction_id
1054 dist_match_type, -- dist_match_type
1055 matched_uom_lookup_code, -- matched_uom_lookup_code
1056 global_attribute_category, -- global_attribute_category
1057 global_attribute1, -- global_attribute1
1058 global_attribute2, -- global_attribute2
1059 global_attribute3, -- global_attribute3
1060 global_attribute4, -- global_attribute4
1061 global_attribute5, -- global_attribute5
1062 global_attribute6, -- global_attribute6
1063 global_attribute7, -- global_attribute7
1064 global_attribute8, -- global_attribute8
1065 global_attribute9, -- global_attribute9
1066 global_attribute10, -- global_attribute10
1067 global_attribute11, -- global_attribute11
1068 global_attribute12, -- global_attribute12
1069 global_attribute13, -- global_attribute13
1070 global_attribute14, -- global_attribute14
1071 global_attribute15, -- global_attribute15
1072 global_attribute16, -- global_attribute16
1073 global_attribute17, -- global_attribute17
1074 global_attribute18, -- global_attribute18
1075 global_attribute19, -- global_attribute19
1076 global_attribute20, -- global_attribute20
1077 receipt_verified_flag, --receipt_verified_flag
1078 receipt_required_flag, --receipt_required_flag
1079 receipt_missing_flag, --receipt_missing_flag
1080 justification, --justification
1081 expense_Group, --expense_Group
1082 end_Expense_Date, --end_Expense_Date
1083 receipt_Currency_Code, --receipt_Currency_Code
1084 receipt_Conversion_Rate, --receipt_Conversion_Rate
1085 receipt_Currency_Amount, --receipt_Currency_Amount
1086 daily_Amount, --daily_Amount
1087 web_Parameter_Id, --web_Parameter_Id
1088 adjustment_Reason, --adjustment_Reason
1089 credit_Card_Trx_Id, --credit_Card_Trx_Id
1090 company_Prepaid_Invoice_Id,--company_Prepaid_Invoice_Id
1091 org_id, -- org_id
1092 (-1)*rounding_amt, -- rounding_amt
1093 NULL, -- charge_applicable_to_dist_id
1094 invoice_distribution_id, -- corrected_invoice_dist_id
1095 NULL, -- related_id
1096 asset_book_type_code, -- asset_book_type_code
1097 asset_category_id, -- asset_category_id
1098 NULL, -- accounting_event_id -- bug 5152035
1099 'N', -- cancellation_flag
1100 'N' -- rcv_charge_addition_flag
1101 FROM ap_invoice_distributions_all
1102 WHERE invoice_id = P_Invoice_Lines_Tab(i).invoice_id
1103 AND invoice_line_number = P_Invoice_Lines_Tab(i).line_number
1104 AND line_type_lookup_code NOT IN ('REC_TAX','NONREC_TAX', 'TIPV', 'TERV', 'TRV',
1105 'IPV', 'ERV')
1106 -- This to exclude the tax distributions created in the case of inclusive calculation
1107 -- of taxes since for the exclusive case the TAX lines are not included in the
1108 -- pl/sql table. Also exclude the variances created by AP. Those will
1109 -- be created during validation of the invoice.
1110 AND NVL(reversal_flag, 'N') <> 'Y');
1111
1112
1113 ----------------------------------------------------------------------------
1114 l_debug_info := 'Update charge_applicable_to_dist_id for allocation info ...';
1115 ----------------------------------------------------------------------------
1116 UPDATE ap_invoice_distributions_all aid
1117 SET aid.charge_applicable_to_dist_id =
1118 (SELECT d1.invoice_distribution_id
1119 FROM ap_invoice_distributions_all d,
1120 ap_invoice_distributions_all cor,
1121 ap_invoice_distributions_all d1
1122 WHERE d.invoice_id = aid.invoice_id
1123 AND d.invoice_distribution_id = aid.invoice_distribution_id
1124 AND d.corrected_invoice_dist_id = cor.invoice_distribution_id
1125 AND cor.charge_applicable_to_dist_id IS NOT NULL
1126 AND d1.corrected_invoice_dist_id = cor.charge_applicable_to_dist_id)
1127 WHERE aid.invoice_id = P_Invoice_Id;
1128
1129 ----------------------------------------------------------------------------
1130 l_debug_info := 'MRC Maintenance...';
1131 ----------------------------------------------------------------------------
1132 SELECT aid.invoice_distribution_id
1133 BULK COLLECT INTO l_key_value_list
1134 FROM ap_invoice_distributions aid
1135 WHERE aid.invoice_id = P_Invoice_Id
1136 AND aid.invoice_line_number = l_line_number;
1137
1138 l_key_value_list.DELETE;
1139
1140 END IF;
1141
1142 END LOOP;
1143 RETURN TRUE;
1144
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 IF (SQLCODE <> -20001) THEN
1148 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1149 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1150 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1151 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1152 ' P_Invoice_Id = '||P_Invoice_Id||
1153 ' P_Error_Code = '||P_Error_Code||
1154 ' P_Calling_Sequence = '||P_Calling_Sequence);
1155 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1156 END IF;
1157
1158 APP_EXCEPTION.RAISE_EXCEPTION;
1159
1160 END Full_Reversal;
1161
1162 /*=============================================================================
1163 | FUNCTION - Quick_Credit()
1164 |
1165 | DESCRIPTION
1166 | Public function that will include all the quick credit functionality.
1167 | This API is called from the Invoice Workbench at commit.
1168 | This function returns TRUE if the full reversal for the invoice goes through
1169 | or FALSE and an error code otherwise.
1170 |
1171 | PARAMETERS
1172 | P_Invoice_Id - quick credit invoice id
1173 | P_Vendor_Id_For_Invoice - vendor id for the debit or credit memo
1174 | P_DM_gl_date - gl_date for the credit/debit memo
1175 | P_credited_Invoice_id - Invoice id for the credited invoice
1176 | P_error_code - Error code to be returned when the rules are not followed
1177 | P_calling_sequence - Calling sequence
1178 |
1179 | MODIFICATION HISTORY
1180 | Date Author Description of Change
1181 | 29-JUL-2003 SYIDNER Creation
1182 |
1183 *============================================================================*/
1184
1185 FUNCTION Quick_Credit(
1186 P_Invoice_Id IN NUMBER,
1187 P_Vendor_Id_For_Invoice IN NUMBER,
1188 P_Dm_Gl_Date IN DATE,
1189 P_Dm_Org_Id IN NUMBER,
1190 P_Credited_Invoice_Id IN NUMBER,
1191 P_error_code OUT NOCOPY VARCHAR2,
1192 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
1193
1194 IS
1195
1196 l_debug_info VARCHAR2(240);
1197 l_curr_calling_sequence VARCHAR2(4000);
1198
1199 l_inv_header_rec ap_invoices_all%ROWTYPE;
1200 l_inv_line_list Inv_Line_Tab_Type;
1201
1202 CURSOR Invoice_Header IS
1203 SELECT *
1204 FROM ap_invoices_all
1205 WHERE invoice_id = P_Credited_Invoice_Id;
1206
1207 CURSOR Invoice_Lines IS
1208 SELECT *
1209 FROM ap_invoice_lines_all
1210 WHERE invoice_id = P_Credited_Invoice_Id
1211 AND line_type_lookup_code <> 'TAX'
1212 AND (NVL(discarded_flag, 'N' ) <> 'Y'
1213 OR NVL(cancelled_flag, 'N' ) <> 'Y')
1214 ORDER BY line_number;
1215
1216 l_return_value BOOLEAN := TRUE;
1217
1218 BEGIN
1219
1220 l_curr_calling_sequence := 'AP_QUICK_CREDIT_PKG.Quick_Credit<-' ||
1221 P_calling_sequence;
1222
1223 -----------------------------------------------------------------
1224 l_debug_info := 'Step 1: Populating invoice and lines collections'||
1225 'for credited invoices';
1226 -----------------------------------------------------------------
1227 BEGIN
1228 OPEN Invoice_Header;
1229 FETCH Invoice_Header INTO l_inv_header_rec;
1230 CLOSE Invoice_Header;
1231 END;
1232
1233 BEGIN
1234 OPEN Invoice_Lines;
1235 FETCH Invoice_Lines
1236 BULK COLLECT INTO l_inv_line_list;
1237 CLOSE Invoice_Lines;
1238 END;
1239
1240 -------------------------------------------------------------------
1241 l_debug_info := 'Step 2: Calling Validating Rules';
1242 -------------------------------------------------------------------
1243 IF NOT (AP_QUICK_CREDIT_PKG.Validating_Rules(
1244 P_Invoice_Id => P_Invoice_Id,
1245 P_Vendor_Id_For_Invoice => P_Vendor_Id_For_Invoice,
1246 P_Dm_Gl_Date => P_Dm_Gl_Date,
1247 P_Invoice_Header_Rec => l_inv_header_rec,
1248 P_Invoice_Lines_Tab => l_inv_line_list,
1249 P_error_code => P_error_code,
1250 P_calling_sequence => l_curr_calling_sequence)) THEN
1251
1252 l_return_value := FALSE;
1253 END IF;
1254
1255 --------------------------------------------------------------------
1256 l_debug_info := 'Step 3: Calling Full Reverse';
1257 --------------------------------------------------------------------
1258 IF (l_return_value = TRUE) THEN
1259 IF NOT (AP_QUICK_CREDIT_PKG.Full_Reversal(
1260 P_Invoice_Id => P_Invoice_Id,
1261 P_Dm_Gl_Date => P_Dm_Gl_Date,
1262 P_Dm_Org_Id => P_Dm_Org_Id,
1263 P_Invoice_Header_Rec => l_inv_header_rec,
1264 P_Invoice_Lines_Tab => l_inv_line_list,
1265 P_error_code => P_error_code,
1266 P_calling_sequence => l_curr_calling_sequence)) THEN
1267
1268 l_return_value := FALSE;
1269 END IF;
1270 END IF;
1271
1272 --------------------------------------------------------------------
1273 l_debug_info := 'Step 4: Calling tax ';
1274 --------------------------------------------------------------------
1275 IF (l_return_value = TRUE) THEN
1276 IF NOT (AP_ETAX_PKG.Calling_eTax(
1277 P_Invoice_id => P_Invoice_Id,
1278 P_Calling_Mode => 'REVERSE INVOICE',
1279 P_Override_Status => NULL,
1280 P_Line_Number_To_Delete => NULL,
1281 P_All_Error_Messages => 'N',
1282 P_error_code => P_error_code,
1283 P_calling_sequence => l_curr_calling_sequence)) THEN
1284
1285 l_return_value := FALSE;
1286
1287 END IF;
1288 END IF;
1289
1290 RETURN l_return_value;
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF (SQLCODE <> -20001) THEN
1295 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1296 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1297 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
1298 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1299 ' P_Invoice_Id = '||P_Invoice_Id||
1300 ' P_Vendor_Id_For_Invoice = '||P_Vendor_Id_For_Invoice||
1301 ' P_Credited_Invoice_Id = '||P_Credited_Invoice_Id||
1302 ' P_Error_Code = '||P_Error_Code||
1303 ' P_Calling_Sequence = '||P_Calling_Sequence);
1304 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1305
1306 END IF;
1307
1308 IF ( Invoice_Header%ISOPEN ) THEN
1309 CLOSE Invoice_Header;
1310 END IF;
1311
1312 IF ( Invoice_Lines%ISOPEN ) THEN
1313 CLOSE Invoice_Lines;
1314 END IF;
1315 APP_EXCEPTION.RAISE_EXCEPTION;
1316
1317 END Quick_Credit;
1318 END AP_QUICK_CREDIT_PKG;