[Home] [Help]
PACKAGE BODY: APPS.AP_AID_TABLE_HANDLER_PKG
Source
1 PACKAGE BODY AP_AID_TABLE_HANDLER_PKG as
2 /* $Header: apaidthb.pls 120.10 2008/01/23 15:33:06 schamaku ship $ */
3
4 -------------------------------------------------------------------
5 PROCEDURE CHECK_UNIQUE (
6 p_ROWID VARCHAR2,
7 p_INVOICE_ID NUMBER,
8 p_INVOICE_LINE_NUMBER NUMBER,
9 p_DISTRIBUTION_LINE_NUMBER NUMBER,
10 p_Calling_Sequence VARCHAR2) IS
11
12 dummy NUMBER := 0;
13 current_calling_sequence VARCHAR2(2000);
14 debug_info VARCHAR2(100);
15
16 BEGIN
17 -- Update the calling sequence
18
19 current_calling_sequence := 'AP_AID_TABLE_HANDLER_PKG.CHECK_UNIQUE<-'||
20 p_Calling_Sequence;
21
22 debug_info := 'Select from ap_invoice_distributions';
23
24 SELECT COUNT(1)
25 INTO dummy
26 FROM ap_invoice_distributions
27 WHERE (invoice_id = p_INVOICE_ID AND
28 invoice_line_number = p_INVOICE_LINE_NUMBER AND
29 distribution_line_number = p_DISTRIBUTION_LINE_NUMBER)
30 AND ((p_ROWID is null) or (rowid <> p_ROWID));
31
32 IF (dummy >= 1) THEN
33 fnd_message.set_name('SQLAP','AP_ALL_DUPLICATE_VALUE');
34 app_exception.raise_exception;
35 END IF;
36
37 EXCEPTION
38 WHEN OTHERS THEN
39 IF (SQLCODE <> -20001) THEN
40 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
41 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
42 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
43 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Rowid = '||p_ROWID
44 ||', Invoice Id = '||p_INVOICE_ID
45 ||', Distribution line number = '||
46 p_DISTRIBUTION_LINE_NUMBER);
47 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
48 END IF;
49 APP_EXCEPTION.RAISE_EXCEPTION;
50 END CHECK_UNIQUE;
51
52
53 PROCEDURE Insert_Row (
54 p_Rowid IN OUT NOCOPY VARCHAR2,
55 p_Invoice_Id NUMBER,
56 p_Invoice_Line_Number NUMBER,
57 p_Distribution_Class VARCHAR2,
58 p_Invoice_Distribution_Id IN OUT NOCOPY NUMBER,
59 p_Dist_Code_Combination_Id NUMBER,
60 p_Last_Update_Date DATE,
61 p_Last_Updated_By NUMBER,
62 p_Accounting_Date DATE,
63 p_Period_Name VARCHAR2,
64 p_Set_Of_Books_Id NUMBER,
65 p_Amount NUMBER,
66 p_Description VARCHAR2,
67 p_Type_1099 VARCHAR2,
68 p_Posted_Flag VARCHAR2,
69 p_Batch_Id NUMBER,
70 p_Quantity_Invoiced NUMBER,
71 p_Unit_Price NUMBER,
72 p_Match_Status_Flag VARCHAR2,
73 p_Attribute_Category VARCHAR2,
74 p_Attribute1 VARCHAR2,
75 p_Attribute2 VARCHAR2,
76 p_Attribute3 VARCHAR2,
77 p_Attribute4 VARCHAR2,
78 p_Attribute5 VARCHAR2,
79 p_Prepay_Amount_Remaining NUMBER,
80 p_Assets_Addition_Flag VARCHAR2,
81 p_Assets_Tracking_Flag VARCHAR2,
82 p_Distribution_Line_Number NUMBER,
83 p_Line_Type_Lookup_Code VARCHAR2,
84 p_Po_Distribution_Id NUMBER,
85 p_Base_Amount NUMBER,
86 p_Pa_Addition_Flag VARCHAR2,
87 p_Posted_Amount NUMBER,
88 p_Posted_Base_Amount NUMBER,
89 p_Encumbered_Flag VARCHAR2,
90 p_Accrual_Posted_Flag VARCHAR2,
91 p_Cash_Posted_Flag VARCHAR2,
92 p_Last_Update_Login NUMBER,
93 p_Creation_Date DATE,
94 p_Created_By NUMBER,
95 p_Stat_Amount NUMBER,
96 p_Attribute11 VARCHAR2,
97 p_Attribute12 VARCHAR2,
98 p_Attribute13 VARCHAR2,
99 p_Attribute14 VARCHAR2,
100 p_Attribute6 VARCHAR2,
101 p_Attribute7 VARCHAR2,
102 p_Attribute8 VARCHAR2,
103 p_Attribute9 VARCHAR2,
104 p_Attribute10 VARCHAR2,
105 p_Attribute15 VARCHAR2,
106 p_Accts_Pay_Code_Comb_Id NUMBER,
107 p_Reversal_Flag VARCHAR2,
108 p_Parent_Invoice_Id NUMBER,
109 p_Income_Tax_Region VARCHAR2,
110 p_Final_Match_Flag VARCHAR2,
111 -- Removed for bug 4277744
112 -- p_Ussgl_Transaction_Code VARCHAR2,
113 -- p_Ussgl_Trx_Code_Context VARCHAR2,
114 p_Expenditure_Item_Date DATE,
115 p_Expenditure_Organization_Id NUMBER,
116 p_Expenditure_Type VARCHAR2,
117 p_Pa_Quantity NUMBER,
118 p_Project_Id NUMBER,
119 p_Task_Id NUMBER,
120 p_Quantity_Variance NUMBER,
121 p_Base_Quantity_Variance NUMBER,
122 p_Packet_Id NUMBER,
123 p_Awt_Flag VARCHAR2,
124 p_Awt_Group_Id NUMBER,
125 p_Pay_Awt_Group_Id NUMBER,--bug6639866
126 p_Awt_Tax_Rate_Id NUMBER,
127 p_Awt_Gross_Amount NUMBER,
128 p_Reference_1 VARCHAR2,
129 p_Reference_2 VARCHAR2,
130 p_Org_Id NUMBER,
131 p_Other_Invoice_Id NUMBER,
132 p_Awt_Invoice_Id NUMBER,
133 p_Awt_Origin_Group_Id NUMBER,
134 p_Program_Application_Id NUMBER,
135 p_Program_Id NUMBER,
136 p_Program_Update_Date DATE,
137 p_Request_Id NUMBER,
138 p_Tax_Recoverable_Flag VARCHAR2,
139 p_Award_Id NUMBER,
140 p_Start_Expense_Date DATE,
141 p_Merchant_Document_Number VARCHAR2,
142 p_Merchant_Name VARCHAR2,
143 p_Merchant_Tax_Reg_Number VARCHAR2,
144 p_Merchant_Taxpayer_Id VARCHAR2,
145 p_Country_Of_Supply VARCHAR2,
146 p_Merchant_Reference VARCHAR2,
147 p_parent_reversal_id NUMBER,
148 p_rcv_transaction_id NUMBER,
149 p_matched_uom_lookup_code VARCHAR2,
150 p_global_attribute_category VARCHAR2 DEFAULT NULL,
151 p_global_attribute1 VARCHAR2 DEFAULT NULL,
152 p_global_attribute2 VARCHAR2 DEFAULT NULL,
153 p_global_attribute3 VARCHAR2 DEFAULT NULL,
154 p_global_attribute4 VARCHAR2 DEFAULT NULL,
155 p_global_attribute5 VARCHAR2 DEFAULT NULL,
156 p_global_attribute6 VARCHAR2 DEFAULT NULL,
157 p_global_attribute7 VARCHAR2 DEFAULT NULL,
158 p_global_attribute8 VARCHAR2 DEFAULT NULL,
159 p_global_attribute9 VARCHAR2 DEFAULT NULL,
160 p_global_attribute10 VARCHAR2 DEFAULT NULL,
161 p_global_attribute11 VARCHAR2 DEFAULT NULL,
162 p_global_attribute12 VARCHAR2 DEFAULT NULL,
163 p_global_attribute13 VARCHAR2 DEFAULT NULL,
164 p_global_attribute14 VARCHAR2 DEFAULT NULL,
165 p_global_attribute15 VARCHAR2 DEFAULT NULL,
166 p_global_attribute16 VARCHAR2 DEFAULT NULL,
167 p_global_attribute17 VARCHAR2 DEFAULT NULL,
168 p_global_attribute18 VARCHAR2 DEFAULT NULL,
169 p_global_attribute19 VARCHAR2 DEFAULT NULL,
170 p_global_attribute20 VARCHAR2 DEFAULT NULL,
171 p_Calling_Sequence VARCHAR2,
172 p_Receipt_Verified_Flag VARCHAR2 DEFAULT NULL,
173 p_Receipt_Required_flag VARCHAR2 DEFAULT NULL,
174 p_Receipt_Missing_flag VARCHAR2 DEFAULT NULL,
175 p_Justification VARCHAR2 DEFAULT NULL,
176 p_Expense_Group VARCHAR2 DEFAULT NULL,
177 p_End_Expense_Date DATE DEFAULT NULL,
178 p_Receipt_Currency_Code VARCHAR2 DEFAULT NULL,
179 p_Receipt_Conversion_Rate VARCHAR2 DEFAULT NULL,
180 p_Receipt_Currency_Amount NUMBER DEFAULT NULL,
181 p_Daily_Amount NUMBER DEFAULT NULL,
182 p_Web_Parameter_Id NUMBER DEFAULT NULL,
183 p_Adjustment_Reason VARCHAR2 DEFAULT NULL,
184 p_Credit_Card_Trx_Id NUMBER DEFAULT NULL,
185 p_Company_Prepaid_Invoice_Id NUMBER DEFAULT NULL,
186 -- Invoice Lines Project Stage 1
187 p_Rounding_Amt NUMBER DEFAULT NULL,
188 p_Charge_Applicable_To_Dist_ID NUMBER DEFAULT NULL,
189 p_Corrected_Invoice_Dist_ID NUMBER DEFAULT NULL,
190 p_Related_ID NUMBER DEFAULT NULL,
191 p_Asset_Book_Type_Code VARCHAR2 DEFAULT NULL,
192 p_Asset_Category_ID NUMBER DEFAULT NULL,
193 --ETAX: Invwkb
194 p_Intended_Use VARCHAR2 DEFAULT NULL,
195 --Freight and Special Charges
196 p_rcv_charge_addition_flag VARCHAR2 DEFAULT 'N')
197 IS
198
199 current_calling_sequence VARCHAR2(2000);
200 debug_info VARCHAR2(100);
201 l_dist_match_type VARCHAR2(25);
202 CURSOR C IS
203 SELECT rowid
204 FROM AP_INVOICE_DISTRIBUTIONS
205 WHERE invoice_id = p_Invoice_Id
206 AND distribution_line_number = p_Distribution_Line_Number;
207
208 CURSOR C2 IS SELECT ap_invoice_distributions_s.nextval FROM sys.dual;
209
210 BEGIN
211
212 -- Update the calling sequence
213
214 current_calling_sequence := 'AP_AID_TABLE_HANDLER_PKG.Insert_Row<-'
215 ||p_Calling_Sequence;
216
217 -- Check for uniqueness of the distribution line number
218
219 AP_AID_TABLE_HANDLER_PKG.CHECK_UNIQUE(
220 p_Rowid,
221 p_Invoice_Id,
222 p_Invoice_Line_Number,
223 p_Distribution_Line_Number,
224 'AP_AID_TABLE_HANDLER_PKG.Insert_Row');
225
226 debug_info := 'Open cursor C2';
227 OPEN C2;
228 debug_info := 'Fetch cursor C2';
229 FETCH C2 INTO p_Invoice_Distribution_Id;
230 debug_info := 'Close cursor C2';
231 CLOSE C2;
232
233 -- Figure out NOCOPY the dist_match_type (not passed from invoice w/b)
234
235 IF (p_rcv_transaction_id IS NOT NULL AND
236 p_po_distribution_id IS NOT NULL) THEN
237 l_dist_match_type := 'ITEM_TO_RECEIPT';
238 ELSIF (p_rcv_transaction_id IS NOT NULL AND
239 p_po_distribution_id IS NULL) THEN
240 l_dist_match_type := 'OTHER_TO_RECEIPT';
241 ELSIF (p_rcv_transaction_id IS NULL AND
242 p_po_distribution_id IS NOT NULL) THEN
243 l_dist_match_type := 'ITEM_TO_PO';
244 ELSE
245 l_dist_match_type := NULL;
246 END IF;
247
248 debug_info := 'Insert into ap_invoice_distributions';
249
250 INSERT INTO AP_INVOICE_DISTRIBUTIONS(
251 invoice_id,
252 -- Invoice Lines Project Stage 1
253 invoice_line_number,
254 distribution_class,
255 dist_code_combination_id,
256 invoice_distribution_id,
257 last_update_date,
258 last_updated_by,
259 accounting_date,
260 period_name,
261 set_of_books_id,
262 amount,
263 description,
264 type_1099,
265 posted_flag,
266 batch_id,
267 quantity_invoiced,
268 unit_price,
269 match_status_flag,
270 attribute_category,
271 attribute1,
272 attribute2,
273 attribute3,
274 attribute4,
275 attribute5,
276 prepay_amount_remaining,
277 assets_addition_flag,
278 assets_tracking_flag,
279 distribution_line_number,
280 line_type_lookup_code,
281 po_distribution_id,
282 base_amount,
283 pa_addition_flag,
284 posted_amount,
285 posted_base_amount,
286 encumbered_flag,
287 accrual_posted_flag,
288 cash_posted_flag,
289 last_update_login,
290 creation_date,
291 created_by,
292 stat_amount,
293 attribute11,
294 attribute12,
295 attribute13,
296 attribute14,
297 attribute6,
298 attribute7,
299 attribute8,
300 attribute9,
301 attribute10,
302 attribute15,
303 accts_pay_code_combination_id,
304 reversal_flag,
305 parent_invoice_id,
306 income_tax_region,
307 final_match_flag,
308 -- Removed for bug 4277744
309 -- ussgl_transaction_code,
310 -- ussgl_trx_code_context,
311 expenditure_item_date,
312 expenditure_organization_id,
313 expenditure_type,
314 pa_quantity,
315 project_id,
316 task_id,
317 quantity_variance,
318 base_quantity_variance,
319 packet_id,
323 awt_tax_rate_id,
320 awt_flag,
321 awt_group_id,
322 pay_awt_group_id,--bug6639866
324 awt_gross_amount,
325 reference_1,
326 reference_2,
327 other_invoice_id,
328 awt_invoice_id,
329 awt_origin_group_id,
330 program_application_id,
331 program_id,
332 program_update_date,
333 request_id,
334 tax_recoverable_flag,
335 award_id,
336 start_expense_date,
337 merchant_document_number,
338 merchant_name,
339 merchant_tax_reg_number,
340 merchant_taxpayer_id,
341 country_of_supply,
342 merchant_reference,
343 parent_reversal_id,
344 rcv_transaction_id,
345 dist_match_type,
346 matched_uom_lookup_code,
347 global_attribute_category,
348 global_attribute1,
349 global_attribute2,
350 global_attribute3,
351 global_attribute4,
352 global_attribute5,
353 global_attribute6,
354 global_attribute7,
355 global_attribute8,
356 global_attribute9,
357 global_attribute10,
358 global_attribute11,
359 global_attribute12,
360 global_attribute13,
361 global_attribute14,
362 global_attribute15,
363 global_attribute16,
364 global_attribute17,
365 global_attribute18,
366 global_attribute19,
367 global_attribute20,
368 receipt_verified_flag,
369 receipt_required_flag,
370 receipt_missing_flag,
371 justification,
372 expense_Group,
373 end_Expense_Date,
374 receipt_Currency_Code,
375 receipt_Conversion_Rate,
376 receipt_Currency_Amount,
377 daily_Amount,
378 web_Parameter_Id,
379 adjustment_Reason,
380 credit_Card_Trx_Id,
381 company_Prepaid_Invoice_Id,
382 org_id, --MOAC project
383 -- Invoice Lines Project Stage 1
384 rounding_amt,
385 charge_applicable_to_dist_id,
386 corrected_invoice_dist_id,
387 related_id,
388 asset_book_type_code,
389 asset_category_id,
390 --ETAX: Invwkb
391 intended_use,
392 --Freight and Special Charges
393 rcv_charge_addition_flag
394 ) VALUES (
395 p_Invoice_Id,
396 -- Invoice Lines Project Stage 1
397 p_Invoice_Line_Number,
398 p_Distribution_Class,
399 p_Dist_Code_Combination_Id,
400 p_Invoice_Distribution_Id,
401 --add for new column 'Invoice_Distribution_Id'
402 p_Last_Update_Date,
403 p_Last_Updated_By,
404 p_Accounting_Date,
405 p_Period_Name,
406 p_Set_Of_Books_Id,
407 p_Amount,
408 p_Description,
409 p_Type_1099,
410 p_Posted_Flag,
411 p_Batch_Id,
412 p_Quantity_Invoiced,
413 p_Unit_Price,
414 p_Match_Status_Flag,
415 p_Attribute_Category,
416 p_Attribute1,
417 p_Attribute2,
418 p_Attribute3,
419 p_Attribute4,
420 p_Attribute5,
421 p_Prepay_Amount_Remaining,
422 p_Assets_Addition_Flag,
423 p_Assets_Tracking_Flag,
424 p_Distribution_Line_Number,
425 p_Line_Type_Lookup_Code,
426 p_Po_Distribution_Id,
427 p_Base_Amount,
428 p_Pa_Addition_Flag,
429 p_Posted_Amount,
430 p_Posted_Base_Amount,
431 p_Encumbered_Flag,
432 p_Accrual_Posted_Flag,
433 p_Cash_Posted_Flag,
434 p_Last_Update_Login,
435 p_Creation_Date,
436 p_Created_By,
437 p_Stat_Amount,
438 p_Attribute11,
439 p_Attribute12,
440 p_Attribute13,
441 p_Attribute14,
442 p_Attribute6,
443 p_Attribute7,
444 p_Attribute8,
445 p_Attribute9,
446 p_Attribute10,
447 p_Attribute15,
448 p_Accts_Pay_Code_Comb_Id,
449 p_Reversal_Flag,
450 p_Parent_Invoice_Id,
451 p_Income_Tax_Region,
452 p_Final_Match_Flag,
453 -- Removed for bug 4277744
454 -- p_Ussgl_Transaction_Code,
455 -- p_Ussgl_Trx_Code_Context,
456 p_Expenditure_Item_Date,
457 p_Expenditure_Organization_Id,
458 p_Expenditure_Type,
459 p_Pa_Quantity,
463 p_Base_Quantity_Variance,
460 p_Project_Id,
461 p_Task_Id,
462 p_Quantity_Variance,
464 p_Packet_Id,
465 p_Awt_Flag,
466 p_Awt_Group_Id,
467 p_Pay_Awt_Group_Id,--bug6639866
468 p_Awt_Tax_Rate_Id,
469 p_Awt_Gross_Amount,
470 p_Reference_1,
471 p_Reference_2,
472 p_Other_Invoice_Id,
473 p_Awt_Invoice_Id,
474 p_Awt_Origin_Group_Id,
475 p_Program_Application_Id,
476 p_Program_Id,
477 p_Program_Update_Date,
478 p_Request_Id,
479 p_Tax_Recoverable_Flag,
480 p_Award_Id,
481 p_Start_Expense_Date,
482 p_Merchant_Document_Number,
483 p_Merchant_Name,
484 p_Merchant_Tax_Reg_Number,
485 p_Merchant_Taxpayer_Id,
486 p_Country_Of_Supply,
487 p_Merchant_Reference,
488 p_Parent_Reversal_Id,
489 p_rcv_transaction_id,
490 l_dist_match_type,
491 p_matched_uom_lookup_code,
492 p_global_attribute_category,
493 p_global_attribute1,
494 p_global_attribute2,
495 p_global_attribute3,
496 p_global_attribute4,
497 p_global_attribute5,
498 p_global_attribute6,
499 p_global_attribute7,
500 p_global_attribute8,
501 p_global_attribute9,
502 p_global_attribute10,
503 p_global_attribute11,
504 p_global_attribute12,
505 p_global_attribute13,
506 p_global_attribute14,
507 p_global_attribute15,
508 p_global_attribute16,
509 p_global_attribute17,
510 p_global_attribute18,
511 p_global_attribute19,
512 p_global_attribute20,
513 p_Receipt_Verified_Flag,
514 p_Receipt_Required_flag,
515 p_Receipt_Missing_flag,
516 p_Justification,
517 p_Expense_Group,
518 p_End_Expense_Date,
519 p_Receipt_Currency_Code,
520 p_Receipt_Conversion_Rate,
521 p_Receipt_Currency_Amount,
522 p_Daily_Amount,
523 p_Web_Parameter_Id,
524 p_Adjustment_Reason,
525 p_Credit_Card_Trx_Id,
526 p_Company_Prepaid_Invoice_Id,
527 p_org_id, --MOAC project
528 -- Invoice Lines Project Stage 1
529 p_rounding_amt,
530 p_charge_applicable_to_dist_id,
531 p_corrected_invoice_dist_id,
532 p_related_id,
533 p_asset_book_type_code,
534 p_asset_category_id,
535 --ETAX: Invwkb
536 p_intended_use,
537 p_rcv_charge_addition_flag
538 );
539
540 --Bug 4539462 DBI logging
541 AP_DBI_PKG.Maintain_DBI_Summary
542 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
543 p_operation => 'I',
544 p_key_value1 => p_invoice_id,
545 p_key_value2 => p_invoice_distribution_id,
546 p_calling_sequence => current_calling_sequence);
547
548 debug_info := 'Open cursor C';
549 OPEN C;
550 debug_info := 'Fetch cursor C';
551 FETCH C INTO p_Rowid;
552 IF (C%NOTFOUND) THEN
553 debug_info := 'Close cursor C - ROW NOTFOUND';
554 CLOSE C;
555 Raise NO_DATA_FOUND;
556 END IF;
557 debug_info := 'Close cursor C';
558 CLOSE C;
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 IF (SQLCODE <> -20001) THEN
563 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
564 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
565 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
566 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
567 END IF;
568 APP_EXCEPTION.RAISE_EXCEPTION;
569
570 END Insert_Row;
571
572
573 PROCEDURE Update_Row (
574 p_Rowid VARCHAR2,
575 p_Invoice_Id NUMBER,
576 p_Invoice_Line_Number NUMBER,
577 p_Distribution_Class VARCHAR2,
578 p_Dist_Code_Combination_Id NUMBER,
579 p_Last_Update_Date DATE,
580 p_Last_Updated_By NUMBER,
581 p_Accounting_Date DATE,
582 p_Period_Name VARCHAR2,
583 p_Set_Of_Books_Id NUMBER,
584 p_Amount NUMBER,
585 p_Description VARCHAR2,
586 p_Type_1099 VARCHAR2,
587 p_Posted_Flag VARCHAR2,
588 p_Batch_Id NUMBER,
589 p_Quantity_Invoiced NUMBER,
590 p_Unit_Price NUMBER,
591 p_Match_Status_Flag VARCHAR2,
595 p_Attribute3 VARCHAR2,
592 p_Attribute_Category VARCHAR2,
593 p_Attribute1 VARCHAR2,
594 p_Attribute2 VARCHAR2,
596 p_Attribute4 VARCHAR2,
597 p_Attribute5 VARCHAR2,
598 p_Prepay_Amount_Remaining NUMBER,
599 p_Assets_Addition_Flag VARCHAR2,
600 p_Assets_Tracking_Flag VARCHAR2,
601 p_Distribution_Line_Number NUMBER,
602 p_Line_Type_Lookup_Code VARCHAR2,
603 p_Po_Distribution_Id NUMBER,
604 p_Base_Amount NUMBER,
605 p_Pa_Addition_Flag VARCHAR2,
606 p_Posted_Amount NUMBER,
607 p_Posted_Base_Amount NUMBER,
608 p_Encumbered_Flag VARCHAR2,
609 p_Accrual_Posted_Flag VARCHAR2,
610 p_Cash_Posted_Flag VARCHAR2,
611 p_Last_Update_Login NUMBER,
612 p_Stat_Amount NUMBER,
613 p_Attribute11 VARCHAR2,
614 p_Attribute12 VARCHAR2,
615 p_Attribute13 VARCHAR2,
616 p_Attribute14 VARCHAR2,
617 p_Attribute6 VARCHAR2,
618 p_Attribute7 VARCHAR2,
619 p_Attribute8 VARCHAR2,
620 p_Attribute9 VARCHAR2,
621 p_Attribute10 VARCHAR2,
622 p_Attribute15 VARCHAR2,
623 p_Accts_Pay_Code_Comb_Id NUMBER,
624 p_Reversal_Flag VARCHAR2,
625 p_Parent_Invoice_Id NUMBER,
626 p_Income_Tax_Region VARCHAR2,
627 p_Final_Match_Flag VARCHAR2,
628 -- Removed for bug 4277744
629 -- p_Ussgl_Transaction_Code VARCHAR2,
630 -- p_Ussgl_Trx_Code_Context VARCHAR2,
631 p_Expenditure_Item_Date DATE,
632 p_Expenditure_Organization_Id NUMBER,
633 p_Expenditure_Type VARCHAR2,
634 p_Pa_Quantity NUMBER,
635 p_Project_Id NUMBER,
636 p_Task_Id NUMBER,
637 p_Quantity_Variance NUMBER,
638 p_Base_Quantity_Variance NUMBER,
639 p_Packet_Id NUMBER,
640 p_Awt_Flag VARCHAR2,
641 p_Awt_Group_Id NUMBER,
642 p_Pay_Awt_Group_Id NUMBER,--bug6639866
643 p_Awt_Tax_Rate_Id NUMBER,
644 p_Awt_Gross_Amount NUMBER,
645 p_Reference_1 VARCHAR2,
646 p_Reference_2 VARCHAR2,
647 p_Org_Id NUMBER,
648 p_Other_Invoice_Id NUMBER,
649 p_Awt_Invoice_Id NUMBER,
650 p_Awt_Origin_Group_Id NUMBER,
651 p_Program_Application_Id NUMBER,
652 p_Program_Id NUMBER,
653 p_Program_Update_Date DATE,
654 p_Request_Id NUMBER,
655 p_Tax_Recoverable_Flag VARCHAR2,
656 p_Award_Id NUMBER,
657 p_Start_Expense_Date DATE,
658 p_Merchant_Document_Number VARCHAR2,
659 p_Merchant_Name VARCHAR2,
660 p_Merchant_Tax_Reg_Number VARCHAR2,
661 p_Merchant_Taxpayer_Id VARCHAR2,
662 p_Country_Of_Supply VARCHAR2,
663 p_Merchant_Reference VARCHAR2,
664 p_global_attribute_category VARCHAR2 DEFAULT NULL,
665 p_global_attribute1 VARCHAR2 DEFAULT NULL,
666 p_global_attribute2 VARCHAR2 DEFAULT NULL,
667 p_global_attribute3 VARCHAR2 DEFAULT NULL,
668 p_global_attribute4 VARCHAR2 DEFAULT NULL,
669 p_global_attribute5 VARCHAR2 DEFAULT NULL,
670 p_global_attribute6 VARCHAR2 DEFAULT NULL,
671 p_global_attribute7 VARCHAR2 DEFAULT NULL,
672 p_global_attribute8 VARCHAR2 DEFAULT NULL,
673 p_global_attribute9 VARCHAR2 DEFAULT NULL,
674 p_global_attribute10 VARCHAR2 DEFAULT NULL,
675 p_global_attribute11 VARCHAR2 DEFAULT NULL,
676 p_global_attribute12 VARCHAR2 DEFAULT NULL,
677 p_global_attribute13 VARCHAR2 DEFAULT NULL,
678 p_global_attribute14 VARCHAR2 DEFAULT NULL,
679 p_global_attribute15 VARCHAR2 DEFAULT NULL,
680 p_global_attribute16 VARCHAR2 DEFAULT NULL,
684 p_global_attribute20 VARCHAR2 DEFAULT NULL,
681 p_global_attribute17 VARCHAR2 DEFAULT NULL,
682 p_global_attribute18 VARCHAR2 DEFAULT NULL,
683 p_global_attribute19 VARCHAR2 DEFAULT NULL,
685 p_Calling_Sequence VARCHAR2,
686 -- Invoice Lines Project Stage 1
687 p_Rounding_Amt NUMBER DEFAULT NULL,
688 p_Charge_Applicable_To_Dist_ID NUMBER DEFAULT NULL,
689 p_Corrected_Invoice_Dist_ID NUMBER DEFAULT NULL,
690 p_Related_ID NUMBER DEFAULT NULL,
691 p_Asset_Book_Type_Code VARCHAR2 DEFAULT NULL,
692 p_Asset_Category_ID NUMBER DEFAULT NULL,
693 --ETAX: Invwkb
694 p_Intended_Use VARCHAR2 DEFAULT NULL)
695
696 IS
697 current_calling_sequence VARCHAR2(2000);
698 debug_info VARCHAR2(100);
699 l_invoice_distribution_id NUMBER;
700 BEGIN
701
702 -- Update the calling sequence
703
704 current_calling_sequence := 'AP_AID_TABLE_HANDLER_PKG.Update_Row<-'
705 ||p_Calling_Sequence;
706
707 -- Check for uniqueness of the distribution line number
708
709 AP_AID_TABLE_HANDLER_PKG.CHECK_UNIQUE(
710 p_Rowid,
711 p_Invoice_Id,
712 p_Invoice_Line_Number,
713 p_Distribution_Line_Number,
714 'AP_AID_TABLE_HANDLER_PKG.Update_Row');
715
716 debug_info := 'Update ap_invoice_distributions';
717
718
719 UPDATE AP_INVOICE_DISTRIBUTIONS
720 SET
721 invoice_id = p_Invoice_Id,
722 invoice_line_number = p_Invoice_Line_Number,
723 distribution_class = p_Distribution_Class,
724 dist_code_combination_id = p_Dist_Code_Combination_Id,
725 last_update_date = p_Last_Update_Date,
726 last_updated_by = p_Last_Updated_By,
727 accounting_date = p_Accounting_Date,
728 period_name = p_Period_Name,
729 set_of_books_id = p_Set_Of_Books_Id,
730 amount = p_Amount,
731 description = p_Description,
732 type_1099 = p_Type_1099,
733 posted_flag = p_Posted_Flag,
734 batch_id = p_Batch_Id,
735 quantity_invoiced = p_Quantity_Invoiced,
736 unit_price = p_Unit_Price,
737 match_status_flag = p_Match_Status_Flag,
738 attribute_category = p_Attribute_Category,
739 attribute1 = p_Attribute1,
740 attribute2 = p_Attribute2,
741 attribute3 = p_Attribute3,
742 attribute4 = p_Attribute4,
743 attribute5 = p_Attribute5,
744 prepay_amount_remaining = p_Prepay_Amount_Remaining,
745 assets_addition_flag = p_Assets_Addition_Flag,
746 assets_tracking_flag = p_Assets_Tracking_Flag,
747 distribution_line_number = p_Distribution_Line_Number,
748 line_type_lookup_code = p_Line_Type_Lookup_Code,
749 po_distribution_id = p_Po_Distribution_Id,
750 base_amount = p_Base_Amount,
751 pa_addition_flag = p_Pa_Addition_Flag,
752 posted_amount = p_Posted_Amount,
753 posted_base_amount = p_Posted_Base_Amount,
754 encumbered_flag = p_Encumbered_Flag,
755 accrual_posted_flag = p_Accrual_Posted_Flag,
756 cash_posted_flag = p_Cash_Posted_Flag,
757 last_update_login = p_Last_Update_Login,
758 stat_amount = p_Stat_Amount,
759 attribute11 = p_Attribute11,
760 attribute12 = p_Attribute12,
761 attribute13 = p_Attribute13,
762 attribute14 = p_Attribute14,
763 attribute6 = p_Attribute6,
764 attribute7 = p_Attribute7,
765 attribute8 = p_Attribute8,
766 attribute9 = p_Attribute9,
767 attribute10 = p_Attribute10,
768 attribute15 = p_Attribute15,
769 accts_pay_code_combination_id = p_Accts_Pay_Code_Comb_Id,
770 reversal_flag = p_Reversal_Flag,
771 parent_invoice_id = p_Parent_Invoice_Id,
772 income_tax_region = p_Income_Tax_Region,
773 final_match_flag = p_Final_Match_Flag,
774 -- Removed for bug 4277744
775 -- ussgl_transaction_code = p_Ussgl_Transaction_Code,
776 -- ussgl_trx_code_context = p_Ussgl_Trx_Code_Context,
777 expenditure_item_date = p_Expenditure_Item_Date,
778 expenditure_organization_id = p_Expenditure_Organization_Id,
779 expenditure_type = p_Expenditure_Type,
780 pa_quantity = p_Pa_Quantity,
781 project_id = p_Project_Id,
782 task_id = p_Task_Id,
783 quantity_variance = p_Quantity_Variance,
784 base_quantity_variance = p_Base_Quantity_Variance,
788 pay_awt_group_id = p_Pay_Awt_Group_Id,--bug6639866
785 packet_id = p_Packet_Id,
786 awt_flag = p_Awt_Flag,
787 awt_group_id = p_Awt_Group_Id,
789 awt_tax_rate_id = p_Awt_Tax_Rate_Id,
790 awt_gross_amount = p_Awt_Gross_Amount,
791 reference_1 = p_Reference_1,
792 reference_2 = p_Reference_2,
793 other_invoice_id = p_Other_Invoice_Id,
794 awt_invoice_id = p_Awt_Invoice_Id,
795 awt_origin_group_id = p_Awt_Origin_Group_Id,
796 program_application_id = p_Program_Application_Id,
797 program_id = p_Program_Id,
798 program_update_date = p_Program_Update_Date,
799 request_id = p_Request_Id,
800 tax_recoverable_flag = p_Tax_Recoverable_Flag,
801 award_id = p_Award_Id,
802 start_expense_date = p_Start_Expense_Date,
803 merchant_document_number = p_Merchant_Document_Number,
804 merchant_name = p_Merchant_Name,
805 merchant_tax_reg_number = p_Merchant_Tax_Reg_Number,
806 merchant_taxpayer_id = p_Merchant_Taxpayer_Id,
807 country_of_supply = p_Country_Of_Supply,
808 merchant_reference = p_Merchant_Reference,
809 global_attribute_category = p_global_attribute_category,
810 global_attribute1 = p_global_attribute1,
811 global_attribute2 = p_global_attribute2,
812 global_attribute3 = p_global_attribute3,
813 global_attribute4 = p_global_attribute4,
814 global_attribute5 = p_global_attribute5,
815 global_attribute6 = p_global_attribute6,
816 global_attribute7 = p_global_attribute7,
817 global_attribute8 = p_global_attribute8,
818 global_attribute9 = p_global_attribute9,
819 global_attribute10 = p_global_attribute10,
820 global_attribute11 = p_global_attribute11,
821 global_attribute12 = p_global_attribute12,
822 global_attribute13 = p_global_attribute13,
823 global_attribute14 = p_global_attribute14,
824 global_attribute15 = p_global_attribute15,
825 global_attribute16 = p_global_attribute16,
826 global_attribute17 = p_global_attribute17,
827 global_attribute18 = p_global_attribute18,
828 global_attribute19 = p_global_attribute19,
829 global_attribute20 = p_global_attribute20,
830 Rounding_Amt = p_rounding_amt,
831 Charge_Applicable_To_Dist_ID = p_charge_applicable_to_dist_id,
832 Corrected_Invoice_Dist_ID = p_corrected_invoice_dist_id,
833 Related_ID = p_related_id,
834 Asset_Book_Type_Code = p_asset_book_type_code,
835 Asset_Category_ID = p_asset_category_id ,
836 --ETAX: Invwkb
837 Intended_Use = p_intended_use
838 WHERE rowid = p_Rowid;
839
840 IF (SQL%NOTFOUND) THEN
841 Raise NO_DATA_FOUND;
842 END IF;
843
844 --Bug 4539462 DBI logging
845 AP_DBI_PKG.Maintain_DBI_Summary
846 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
847 p_operation => 'U',
848 p_key_value1 => P_invoice_id,
849 p_key_value2 => l_invoice_distribution_id,
850 p_calling_sequence => current_calling_sequence);
851
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 IF (SQLCODE <> -20001) THEN
856 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
857 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
858 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
859 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
860 END IF;
861 APP_EXCEPTION.RAISE_EXCEPTION;
862
863 END Update_Row;
864
865 PROCEDURE Delete_Row (
866 p_Rowid VARCHAR2,
867 p_Calling_Sequence VARCHAR2)
868 IS
869 current_calling_sequence VARCHAR2(2000);
870 debug_info VARCHAR2(100);
871 l_invoice_distribution_id NUMBER;
872 l_invoice_id NUMBER;
873
874 BEGIN
875
876 -- Update the calling sequence
877
878 current_calling_sequence := 'AP_AID_TABLE_HANDLER_PKG.Delete_Row<-'
879 ||p_Calling_Sequence;
880
881 SELECT invoice_distribution_id, invoice_id
882 INTO l_invoice_distribution_id, l_invoice_id
883 FROM ap_invoice_distributions
884 WHERE rowid = p_Rowid;
885
886 DELETE FROM AP_INVOICE_DISTRIBUTIONS
887 WHERE rowid = p_Rowid;
888
889 IF (SQL%NOTFOUND) THEN
890 Raise NO_DATA_FOUND;
891 END IF;
892
893 --Bug 4539462 DBI logging
894 AP_DBI_PKG.Maintain_DBI_Summary
895 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
896 p_operation => 'D',
897 p_key_value1 => l_invoice_id,
898 p_key_value2 => l_invoice_distribution_id,
899 p_calling_sequence => current_calling_sequence);
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 IF (SQLCODE <> -20001) THEN
904 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
905 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
906 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
907 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
908 END IF;
909 APP_EXCEPTION.RAISE_EXCEPTION;
910
911 END Delete_Row;
912
913 END AP_AID_TABLE_HANDLER_PKG;