[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.12020000.2 2012/07/04 07:49:44 sbonala 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,
273 attribute3,
270 attribute_category,
271 attribute1,
272 attribute2,
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,
320 awt_flag,
321 awt_group_id,
322 pay_awt_group_id,--bug6639866
323 awt_tax_rate_id,
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 inventory_transfer_status /*Bug#11067286 */
395 ) VALUES (
396 p_Invoice_Id,
397 -- Invoice Lines Project Stage 1
398 p_Invoice_Line_Number,
399 p_Distribution_Class,
400 p_Dist_Code_Combination_Id,
401 p_Invoice_Distribution_Id,
402 --add for new column 'Invoice_Distribution_Id'
403 p_Last_Update_Date,
404 p_Last_Updated_By,
405 p_Accounting_Date,
406 p_Period_Name,
407 p_Set_Of_Books_Id,
408 p_Amount,
409 p_Description,
410 p_Type_1099,
411 p_Posted_Flag,
412 p_Batch_Id,
413 p_Quantity_Invoiced,
414 p_Unit_Price,
415 p_Match_Status_Flag,
416 p_Attribute_Category,
417 p_Attribute1,
418 p_Attribute2,
419 p_Attribute3,
420 p_Attribute4,
421 p_Attribute5,
425 p_Distribution_Line_Number,
422 p_Prepay_Amount_Remaining,
423 p_Assets_Addition_Flag,
424 p_Assets_Tracking_Flag,
426 p_Line_Type_Lookup_Code,
427 p_Po_Distribution_Id,
428 p_Base_Amount,
429 p_Pa_Addition_Flag,
430 p_Posted_Amount,
431 p_Posted_Base_Amount,
432 p_Encumbered_Flag,
433 p_Accrual_Posted_Flag,
434 p_Cash_Posted_Flag,
435 p_Last_Update_Login,
436 p_Creation_Date,
437 p_Created_By,
438 p_Stat_Amount,
439 p_Attribute11,
440 p_Attribute12,
441 p_Attribute13,
442 p_Attribute14,
443 p_Attribute6,
444 p_Attribute7,
445 p_Attribute8,
446 p_Attribute9,
447 p_Attribute10,
448 p_Attribute15,
449 p_Accts_Pay_Code_Comb_Id,
450 p_Reversal_Flag,
451 p_Parent_Invoice_Id,
452 p_Income_Tax_Region,
453 p_Final_Match_Flag,
454 -- Removed for bug 4277744
455 -- p_Ussgl_Transaction_Code,
456 -- p_Ussgl_Trx_Code_Context,
457 p_Expenditure_Item_Date,
458 p_Expenditure_Organization_Id,
459 p_Expenditure_Type,
460 p_Pa_Quantity,
461 p_Project_Id,
462 p_Task_Id,
463 p_Quantity_Variance,
464 p_Base_Quantity_Variance,
465 p_Packet_Id,
466 p_Awt_Flag,
467 p_Awt_Group_Id,
468 p_Pay_Awt_Group_Id,--bug6639866
469 p_Awt_Tax_Rate_Id,
470 p_Awt_Gross_Amount,
471 p_Reference_1,
472 p_Reference_2,
473 p_Other_Invoice_Id,
474 p_Awt_Invoice_Id,
475 p_Awt_Origin_Group_Id,
476 p_Program_Application_Id,
477 p_Program_Id,
478 p_Program_Update_Date,
479 p_Request_Id,
480 p_Tax_Recoverable_Flag,
481 p_Award_Id,
482 p_Start_Expense_Date,
483 p_Merchant_Document_Number,
484 p_Merchant_Name,
485 p_Merchant_Tax_Reg_Number,
486 p_Merchant_Taxpayer_Id,
487 p_Country_Of_Supply,
488 p_Merchant_Reference,
489 p_Parent_Reversal_Id,
490 p_rcv_transaction_id,
491 l_dist_match_type,
492 p_matched_uom_lookup_code,
493 p_global_attribute_category,
494 p_global_attribute1,
495 p_global_attribute2,
496 p_global_attribute3,
497 p_global_attribute4,
498 p_global_attribute5,
499 p_global_attribute6,
500 p_global_attribute7,
501 p_global_attribute8,
502 p_global_attribute9,
503 p_global_attribute10,
504 p_global_attribute11,
505 p_global_attribute12,
506 p_global_attribute13,
507 p_global_attribute14,
508 p_global_attribute15,
509 p_global_attribute16,
510 p_global_attribute17,
511 p_global_attribute18,
512 p_global_attribute19,
513 p_global_attribute20,
514 p_Receipt_Verified_Flag,
515 p_Receipt_Required_flag,
516 p_Receipt_Missing_flag,
517 p_Justification,
518 p_Expense_Group,
519 p_End_Expense_Date,
520 p_Receipt_Currency_Code,
521 p_Receipt_Conversion_Rate,
522 p_Receipt_Currency_Amount,
523 p_Daily_Amount,
524 p_Web_Parameter_Id,
525 p_Adjustment_Reason,
526 p_Credit_Card_Trx_Id,
527 p_Company_Prepaid_Invoice_Id,
528 p_org_id, --MOAC project
529 -- Invoice Lines Project Stage 1
530 p_rounding_amt,
531 p_charge_applicable_to_dist_id,
532 p_corrected_invoice_dist_id,
533 p_related_id,
534 p_asset_book_type_code,
535 p_asset_category_id,
536 --ETAX: Invwkb
537 p_intended_use,
538 p_rcv_charge_addition_flag,
539 'N' /*Bug#11067286*/
540 );
541
542 --Bug 4539462 DBI logging
543 AP_DBI_PKG.Maintain_DBI_Summary
544 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
545 p_operation => 'I',
546 p_key_value1 => p_invoice_id,
547 p_key_value2 => p_invoice_distribution_id,
548 p_calling_sequence => current_calling_sequence);
549
550 debug_info := 'Open cursor C';
551 OPEN C;
552 debug_info := 'Fetch cursor C';
553 FETCH C INTO p_Rowid;
554 IF (C%NOTFOUND) THEN
555 debug_info := 'Close cursor C - ROW NOTFOUND';
556 CLOSE C;
557 Raise NO_DATA_FOUND;
558 END IF;
559 debug_info := 'Close cursor C';
560 CLOSE C;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 IF (SQLCODE <> -20001) THEN
565 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
566 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
567 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
568 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
569 END IF;
570 APP_EXCEPTION.RAISE_EXCEPTION;
571
575 PROCEDURE Update_Row (
572 END Insert_Row;
573
574
576 p_Rowid VARCHAR2,
577 p_Invoice_Id NUMBER,
578 p_Invoice_Line_Number NUMBER,
579 p_Distribution_Class VARCHAR2,
580 p_Dist_Code_Combination_Id NUMBER,
581 p_Last_Update_Date DATE,
582 p_Last_Updated_By NUMBER,
583 p_Accounting_Date DATE,
584 p_Period_Name VARCHAR2,
585 p_Set_Of_Books_Id NUMBER,
586 p_Amount NUMBER,
587 p_Description VARCHAR2,
588 p_Type_1099 VARCHAR2,
589 p_Posted_Flag VARCHAR2,
590 p_Batch_Id NUMBER,
591 p_Quantity_Invoiced NUMBER,
592 p_Unit_Price NUMBER,
593 p_Match_Status_Flag VARCHAR2,
594 p_Attribute_Category VARCHAR2,
595 p_Attribute1 VARCHAR2,
596 p_Attribute2 VARCHAR2,
597 p_Attribute3 VARCHAR2,
598 p_Attribute4 VARCHAR2,
599 p_Attribute5 VARCHAR2,
600 p_Prepay_Amount_Remaining NUMBER,
601 p_Assets_Addition_Flag VARCHAR2,
602 p_Assets_Tracking_Flag VARCHAR2,
603 p_Distribution_Line_Number NUMBER,
604 p_Line_Type_Lookup_Code VARCHAR2,
605 p_Po_Distribution_Id NUMBER,
606 p_Base_Amount NUMBER,
607 p_Pa_Addition_Flag VARCHAR2,
608 p_Posted_Amount NUMBER,
609 p_Posted_Base_Amount NUMBER,
610 p_Encumbered_Flag VARCHAR2,
611 p_Accrual_Posted_Flag VARCHAR2,
612 p_Cash_Posted_Flag VARCHAR2,
613 p_Last_Update_Login NUMBER,
614 p_Stat_Amount NUMBER,
615 p_Attribute11 VARCHAR2,
616 p_Attribute12 VARCHAR2,
617 p_Attribute13 VARCHAR2,
618 p_Attribute14 VARCHAR2,
619 p_Attribute6 VARCHAR2,
620 p_Attribute7 VARCHAR2,
621 p_Attribute8 VARCHAR2,
622 p_Attribute9 VARCHAR2,
623 p_Attribute10 VARCHAR2,
624 p_Attribute15 VARCHAR2,
625 p_Accts_Pay_Code_Comb_Id NUMBER,
626 p_Reversal_Flag VARCHAR2,
627 p_Parent_Invoice_Id NUMBER,
628 p_Income_Tax_Region VARCHAR2,
629 p_Final_Match_Flag VARCHAR2,
630 -- Removed for bug 4277744
631 -- p_Ussgl_Transaction_Code VARCHAR2,
632 -- p_Ussgl_Trx_Code_Context VARCHAR2,
633 p_Expenditure_Item_Date DATE,
634 p_Expenditure_Organization_Id NUMBER,
635 p_Expenditure_Type VARCHAR2,
636 p_Pa_Quantity NUMBER,
637 p_Project_Id NUMBER,
638 p_Task_Id NUMBER,
639 p_Quantity_Variance NUMBER,
640 p_Base_Quantity_Variance NUMBER,
641 p_Packet_Id NUMBER,
642 p_Awt_Flag VARCHAR2,
643 p_Awt_Group_Id NUMBER,
644 p_Pay_Awt_Group_Id NUMBER,--bug6639866
645 p_Awt_Tax_Rate_Id NUMBER,
646 p_Awt_Gross_Amount NUMBER,
647 p_Reference_1 VARCHAR2,
648 p_Reference_2 VARCHAR2,
649 p_Org_Id NUMBER,
650 p_Other_Invoice_Id NUMBER,
651 p_Awt_Invoice_Id NUMBER,
652 p_Awt_Origin_Group_Id NUMBER,
653 p_Program_Application_Id NUMBER,
654 p_Program_Id NUMBER,
655 p_Program_Update_Date DATE,
656 p_Request_Id NUMBER,
657 p_Tax_Recoverable_Flag VARCHAR2,
658 p_Award_Id NUMBER,
659 p_Start_Expense_Date DATE,
660 p_Merchant_Document_Number VARCHAR2,
661 p_Merchant_Name VARCHAR2,
662 p_Merchant_Tax_Reg_Number VARCHAR2,
663 p_Merchant_Taxpayer_Id VARCHAR2,
664 p_Country_Of_Supply VARCHAR2,
665 p_Merchant_Reference VARCHAR2,
666 p_global_attribute_category VARCHAR2 DEFAULT NULL,
667 p_global_attribute1 VARCHAR2 DEFAULT NULL,
668 p_global_attribute2 VARCHAR2 DEFAULT NULL,
669 p_global_attribute3 VARCHAR2 DEFAULT NULL,
670 p_global_attribute4 VARCHAR2 DEFAULT NULL,
671 p_global_attribute5 VARCHAR2 DEFAULT NULL,
672 p_global_attribute6 VARCHAR2 DEFAULT NULL,
673 p_global_attribute7 VARCHAR2 DEFAULT NULL,
677 p_global_attribute11 VARCHAR2 DEFAULT NULL,
674 p_global_attribute8 VARCHAR2 DEFAULT NULL,
675 p_global_attribute9 VARCHAR2 DEFAULT NULL,
676 p_global_attribute10 VARCHAR2 DEFAULT NULL,
678 p_global_attribute12 VARCHAR2 DEFAULT NULL,
679 p_global_attribute13 VARCHAR2 DEFAULT NULL,
680 p_global_attribute14 VARCHAR2 DEFAULT NULL,
681 p_global_attribute15 VARCHAR2 DEFAULT NULL,
682 p_global_attribute16 VARCHAR2 DEFAULT NULL,
683 p_global_attribute17 VARCHAR2 DEFAULT NULL,
684 p_global_attribute18 VARCHAR2 DEFAULT NULL,
685 p_global_attribute19 VARCHAR2 DEFAULT NULL,
686 p_global_attribute20 VARCHAR2 DEFAULT NULL,
687 p_Calling_Sequence VARCHAR2,
688 -- Invoice Lines Project Stage 1
689 p_Rounding_Amt NUMBER DEFAULT NULL,
690 p_Charge_Applicable_To_Dist_ID NUMBER DEFAULT NULL,
691 p_Corrected_Invoice_Dist_ID NUMBER DEFAULT NULL,
692 p_Related_ID NUMBER DEFAULT NULL,
693 p_Asset_Book_Type_Code VARCHAR2 DEFAULT NULL,
694 p_Asset_Category_ID NUMBER DEFAULT NULL,
695 --ETAX: Invwkb
696 p_Intended_Use VARCHAR2 DEFAULT NULL)
697
698 IS
699 current_calling_sequence VARCHAR2(2000);
700 debug_info VARCHAR2(100);
701 l_invoice_distribution_id NUMBER;
702 BEGIN
703
704 -- Update the calling sequence
705
706 current_calling_sequence := 'AP_AID_TABLE_HANDLER_PKG.Update_Row<-'
707 ||p_Calling_Sequence;
708
709 -- Check for uniqueness of the distribution line number
710
711 AP_AID_TABLE_HANDLER_PKG.CHECK_UNIQUE(
712 p_Rowid,
713 p_Invoice_Id,
714 p_Invoice_Line_Number,
715 p_Distribution_Line_Number,
716 'AP_AID_TABLE_HANDLER_PKG.Update_Row');
717
718 debug_info := 'Update ap_invoice_distributions';
719
720
721 UPDATE AP_INVOICE_DISTRIBUTIONS
722 SET
723 invoice_id = p_Invoice_Id,
724 invoice_line_number = p_Invoice_Line_Number,
725 distribution_class = p_Distribution_Class,
726 dist_code_combination_id = p_Dist_Code_Combination_Id,
727 last_update_date = p_Last_Update_Date,
728 last_updated_by = p_Last_Updated_By,
729 accounting_date = p_Accounting_Date,
730 period_name = p_Period_Name,
731 set_of_books_id = p_Set_Of_Books_Id,
732 amount = p_Amount,
733 description = p_Description,
734 type_1099 = p_Type_1099,
735 posted_flag = p_Posted_Flag,
736 batch_id = p_Batch_Id,
737 quantity_invoiced = p_Quantity_Invoiced,
738 unit_price = p_Unit_Price,
739 match_status_flag = p_Match_Status_Flag,
740 attribute_category = p_Attribute_Category,
741 attribute1 = p_Attribute1,
742 attribute2 = p_Attribute2,
743 attribute3 = p_Attribute3,
744 attribute4 = p_Attribute4,
745 attribute5 = p_Attribute5,
746 prepay_amount_remaining = p_Prepay_Amount_Remaining,
747 assets_addition_flag = p_Assets_Addition_Flag,
748 assets_tracking_flag = p_Assets_Tracking_Flag,
749 distribution_line_number = p_Distribution_Line_Number,
750 line_type_lookup_code = p_Line_Type_Lookup_Code,
751 po_distribution_id = p_Po_Distribution_Id,
752 base_amount = p_Base_Amount,
753 pa_addition_flag = p_Pa_Addition_Flag,
754 posted_amount = p_Posted_Amount,
755 posted_base_amount = p_Posted_Base_Amount,
756 encumbered_flag = p_Encumbered_Flag,
757 accrual_posted_flag = p_Accrual_Posted_Flag,
758 cash_posted_flag = p_Cash_Posted_Flag,
759 last_update_login = p_Last_Update_Login,
760 stat_amount = p_Stat_Amount,
761 attribute11 = p_Attribute11,
762 attribute12 = p_Attribute12,
763 attribute13 = p_Attribute13,
764 attribute14 = p_Attribute14,
765 attribute6 = p_Attribute6,
766 attribute7 = p_Attribute7,
767 attribute8 = p_Attribute8,
768 attribute9 = p_Attribute9,
769 attribute10 = p_Attribute10,
770 attribute15 = p_Attribute15,
771 accts_pay_code_combination_id = p_Accts_Pay_Code_Comb_Id,
772 reversal_flag = p_Reversal_Flag,
773 parent_invoice_id = p_Parent_Invoice_Id,
774 income_tax_region = p_Income_Tax_Region,
775 final_match_flag = p_Final_Match_Flag,
776 -- Removed for bug 4277744
777 -- ussgl_transaction_code = p_Ussgl_Transaction_Code,
778 -- ussgl_trx_code_context = p_Ussgl_Trx_Code_Context,
779 expenditure_item_date = p_Expenditure_Item_Date,
780 expenditure_organization_id = p_Expenditure_Organization_Id,
784 task_id = p_Task_Id,
781 expenditure_type = p_Expenditure_Type,
782 pa_quantity = p_Pa_Quantity,
783 project_id = p_Project_Id,
785 quantity_variance = p_Quantity_Variance,
786 base_quantity_variance = p_Base_Quantity_Variance,
787 packet_id = p_Packet_Id,
788 awt_flag = p_Awt_Flag,
789 awt_group_id = p_Awt_Group_Id,
790 pay_awt_group_id = p_Pay_Awt_Group_Id,--bug6639866
791 awt_tax_rate_id = p_Awt_Tax_Rate_Id,
792 awt_gross_amount = p_Awt_Gross_Amount,
793 reference_1 = p_Reference_1,
794 reference_2 = p_Reference_2,
795 other_invoice_id = p_Other_Invoice_Id,
796 awt_invoice_id = p_Awt_Invoice_Id,
797 awt_origin_group_id = p_Awt_Origin_Group_Id,
798 program_application_id = p_Program_Application_Id,
799 program_id = p_Program_Id,
800 program_update_date = p_Program_Update_Date,
801 request_id = p_Request_Id,
802 tax_recoverable_flag = p_Tax_Recoverable_Flag,
803 award_id = p_Award_Id,
804 start_expense_date = p_Start_Expense_Date,
805 merchant_document_number = p_Merchant_Document_Number,
806 merchant_name = p_Merchant_Name,
807 merchant_tax_reg_number = p_Merchant_Tax_Reg_Number,
808 merchant_taxpayer_id = p_Merchant_Taxpayer_Id,
809 country_of_supply = p_Country_Of_Supply,
810 merchant_reference = p_Merchant_Reference,
811 global_attribute_category = p_global_attribute_category,
812 global_attribute1 = p_global_attribute1,
813 global_attribute2 = p_global_attribute2,
814 global_attribute3 = p_global_attribute3,
815 global_attribute4 = p_global_attribute4,
816 global_attribute5 = p_global_attribute5,
817 global_attribute6 = p_global_attribute6,
818 global_attribute7 = p_global_attribute7,
819 global_attribute8 = p_global_attribute8,
820 global_attribute9 = p_global_attribute9,
821 global_attribute10 = p_global_attribute10,
822 global_attribute11 = p_global_attribute11,
823 global_attribute12 = p_global_attribute12,
824 global_attribute13 = p_global_attribute13,
825 global_attribute14 = p_global_attribute14,
826 global_attribute15 = p_global_attribute15,
827 global_attribute16 = p_global_attribute16,
828 global_attribute17 = p_global_attribute17,
829 global_attribute18 = p_global_attribute18,
830 global_attribute19 = p_global_attribute19,
831 global_attribute20 = p_global_attribute20,
832 Rounding_Amt = p_rounding_amt,
833 Charge_Applicable_To_Dist_ID = p_charge_applicable_to_dist_id,
834 Corrected_Invoice_Dist_ID = p_corrected_invoice_dist_id,
835 Related_ID = p_related_id,
836 Asset_Book_Type_Code = p_asset_book_type_code,
837 Asset_Category_ID = p_asset_category_id ,
838 --ETAX: Invwkb
839 Intended_Use = p_intended_use
840 WHERE rowid = p_Rowid;
841
842 IF (SQL%NOTFOUND) THEN
843 Raise NO_DATA_FOUND;
844 END IF;
845
846 --Bug 4539462 DBI logging
847 AP_DBI_PKG.Maintain_DBI_Summary
848 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
849 p_operation => 'U',
850 p_key_value1 => P_invoice_id,
851 p_key_value2 => l_invoice_distribution_id,
852 p_calling_sequence => current_calling_sequence);
853
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 IF (SQLCODE <> -20001) THEN
858 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
859 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
860 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
861 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
862 END IF;
863 APP_EXCEPTION.RAISE_EXCEPTION;
864
865 END Update_Row;
866
867 PROCEDURE Delete_Row (
868 p_Rowid VARCHAR2,
869 p_Calling_Sequence VARCHAR2)
870 IS
871 current_calling_sequence VARCHAR2(2000);
872 debug_info VARCHAR2(100);
873 l_invoice_distribution_id NUMBER;
874 l_invoice_id NUMBER;
875
876 BEGIN
877
878 -- Update the calling sequence
879
880 current_calling_sequence := 'AP_AID_TABLE_HANDLER_PKG.Delete_Row<-'
881 ||p_Calling_Sequence;
882
883 SELECT invoice_distribution_id, invoice_id
884 INTO l_invoice_distribution_id, l_invoice_id
885 FROM ap_invoice_distributions
886 WHERE rowid = p_Rowid;
887
888 DELETE FROM AP_INVOICE_DISTRIBUTIONS
889 WHERE rowid = p_Rowid;
890
891 IF (SQL%NOTFOUND) THEN
892 Raise NO_DATA_FOUND;
893 END IF;
894
895 --Bug 4539462 DBI logging
896 AP_DBI_PKG.Maintain_DBI_Summary
897 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
898 p_operation => 'D',
899 p_key_value1 => l_invoice_id,
900 p_key_value2 => l_invoice_distribution_id,
901 p_calling_sequence => current_calling_sequence);
902
903 EXCEPTION
904 WHEN OTHERS THEN
905 IF (SQLCODE <> -20001) THEN
906 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
907 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
908 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
909 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', debug_info);
910 END IF;
911 APP_EXCEPTION.RAISE_EXCEPTION;
912
913 END Delete_Row;
914
915 END AP_AID_TABLE_HANDLER_PKG;