[Home] [Help]
PACKAGE BODY: APPS.AP_AI_TABLE_HANDLER_PKG
Source
1 PACKAGE BODY AP_AI_TABLE_HANDLER_PKG as
2 /* $Header: apainthb.pls 120.25 2010/12/24 04:13:08 pgayen ship $ */
3
4 PROCEDURE CHECK_UNIQUE (
5 p_ROWID VARCHAR2,
6 p_INVOICE_NUM VARCHAR2,
7 p_VENDOR_ID NUMBER,
8 p_ORG_ID NUMBER, -- Bug 5407785
9 P_PARTY_SITE_ID NUMBER, /*Bug9105666*/
10 P_VENDOR_SITE_ID NUMBER, /*Bug9105666*/
11 p_calling_sequence IN VARCHAR2) IS
12
13 dummy_a NUMBER := 0;
14 dummy_b NUMBER := 0;
15 current_calling_sequence VARCHAR2(2000);
16 debug_info VARCHAR2(100);
17
18 BEGIN
19
20 -- Update the calling sequence
21
22 current_calling_sequence :=
23 'AP_AI_TABLE_HANDLER_PKG.CHECK_UNIQUE<-'||p_calling_sequence;
24
25 debug_info := 'Count for same vendor_id,party_site_id and invoice_num'; /*Bug9105666*/
26 SELECT count(1)
27 INTO dummy_a
28 FROM ap_invoices_all
29 WHERE invoice_num = p_INVOICE_NUM
30 AND vendor_id = p_VENDOR_ID
31 AND org_id = p_ORG_ID -- Bug 5407785
32 AND (party_site_id = P_PARTY_SITE_ID /*Bug9105666*/
33 OR (party_site_id is null and P_PARTY_SITE_ID is null)) /*Bug9105666*/
34 AND ((p_ROWID is null) OR
35 (rowid <> p_ROWID));
36
37 IF (dummy_a >= 1) THEN
38 fnd_message.set_name('SQLAP','AP_ALL_DUPLICATE_VALUE');
39 app_exception.raise_exception;
40 END IF;
41
42 debug_info := 'Count for same vendor_id,party_site_id and invoice_num amount purged invoices'; /*Bug9105666*/
43 SELECT count(1)
44 INTO dummy_b
45 FROM ap_history_invoices_all ahi,
46 ap_supplier_sites_all ass /*Bug9105666*/
47 WHERE ahi.vendor_id = ass.vendor_id /*Bug9105666*/
48 AND ahi.org_id = ass.org_id /*Bug9105666*/
49 AND ahi.invoice_num = p_INVOICE_NUM
50 AND ahi.vendor_id = p_VENDOR_ID
51 AND ahi.org_id = p_ORG_ID -- Bug 5407785
52 AND (ass.party_site_id = P_PARTY_SITE_ID /*Bug9105666*/
53 OR (ass.party_site_id is null and P_PARTY_SITE_ID is null)); /*Bug9105666*/
54
55 IF (dummy_b >= 1) THEN
56 fnd_message.set_name('SQLAP','AP_ALL_DUPLICATE_VALUE');
57 app_exception.raise_exception;
58 END IF;
59
60 EXCEPTION
61 WHEN OTHERS THEN
62 IF (SQLCODE <> -20001) THEN
63 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
64 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
65 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
66 current_calling_sequence);
67 FND_MESSAGE.SET_TOKEN('PARAMETERS',
68 'p_Rowid = ' ||p_Rowid
69 ||', p_INVOICE_NUM = '||p_INVOICE_NUM
70 ||', p_VENDOR_ID = ' ||p_VENDOR_ID
71 ||', P_VENDOR_SITE_ID = '||P_VENDOR_SITE_ID /*Bug9105666*/
72 ||', P_PARTY_SITE_ID = '||P_PARTY_SITE_ID /*Bug9105666*/
73 );
74 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
75 END IF;
76 APP_EXCEPTION.RAISE_EXCEPTION;
77
78 END CHECK_UNIQUE;
79
80 ---------------------------------------------------------------------
81 PROCEDURE Insert_Row(
82 p_Rowid IN OUT NOCOPY VARCHAR2,
83 p_Invoice_Id IN OUT NOCOPY NUMBER,
84 p_Last_Update_Date DATE,
85 p_Last_Updated_By NUMBER,
86 p_Vendor_Id NUMBER,
87 p_Invoice_Num VARCHAR2,
88 p_Invoice_Amount NUMBER,
89 p_Vendor_Site_Id NUMBER,
90 p_Amount_Paid NUMBER,
91 p_Discount_Amount_Taken NUMBER,
92 p_Invoice_Date DATE,
93 p_Source VARCHAR2,
94 p_Invoice_Type_Lookup_Code VARCHAR2,
95 p_Description VARCHAR2,
96 p_Batch_Id NUMBER,
97 p_Amt_Applicable_To_Discount NUMBER,
98 p_Terms_Id NUMBER,
99 p_Terms_Date DATE,
100 p_Goods_Received_Date DATE,
101 p_Invoice_Received_Date DATE,
102 p_Voucher_Num VARCHAR2,
103 p_Approved_Amount NUMBER,
104 p_Approval_Status VARCHAR2,
105 p_Approval_Description VARCHAR2,
106 p_Pay_Group_Lookup_Code VARCHAR2,
107 p_Set_Of_Books_Id NUMBER,
108 p_Accts_Pay_CCId NUMBER,
109 p_Recurring_Payment_Id NUMBER,
110 p_Invoice_Currency_Code VARCHAR2,
111 p_Payment_Currency_Code VARCHAR2,
112 p_Exchange_Rate NUMBER,
113 p_Payment_Amount_Total NUMBER,
114 p_Payment_Status_Flag VARCHAR2,
115 p_Posting_Status VARCHAR2,
116 p_Authorized_By VARCHAR2,
117 p_Attribute_Category VARCHAR2,
118 p_Attribute1 VARCHAR2,
119 p_Attribute2 VARCHAR2,
120 p_Attribute3 VARCHAR2,
121 p_Attribute4 VARCHAR2,
122 p_Attribute5 VARCHAR2,
123 p_Creation_Date DATE,
124 p_Created_By NUMBER,
128 p_Exchange_Date DATE,
125 p_Vendor_Prepay_Amount NUMBER,
126 p_Base_Amount NUMBER,
127 p_Exchange_Rate_Type VARCHAR2,
129 p_Payment_Cross_Rate NUMBER,
130 p_Payment_Cross_Rate_Type VARCHAR2,
131 p_Payment_Cross_Rate_Date Date,
132 p_Pay_Curr_Invoice_Amount NUMBER,
133 p_Last_Update_Login NUMBER,
134 p_Original_Prepayment_Amount NUMBER,
135 p_Earliest_Settlement_Date DATE,
136 p_Attribute11 VARCHAR2,
137 p_Attribute12 VARCHAR2,
138 p_Attribute13 VARCHAR2,
139 p_Attribute14 VARCHAR2,
140 p_Attribute6 VARCHAR2,
141 p_Attribute7 VARCHAR2,
142 p_Attribute8 VARCHAR2,
143 p_Attribute9 VARCHAR2,
144 p_Attribute10 VARCHAR2,
145 p_Attribute15 VARCHAR2,
146 p_Cancelled_Date DATE,
147 p_Cancelled_By NUMBER,
148 p_Cancelled_Amount NUMBER,
149 p_Temp_Cancelled_Amount NUMBER,
150 p_Exclusive_Payment_Flag VARCHAR2,
151 p_Po_Header_Id NUMBER,
152 p_Doc_Sequence_Id NUMBER,
153 p_Doc_Sequence_Value NUMBER,
154 p_Doc_Category_Code VARCHAR2,
155 p_Expenditure_Item_Date DATE,
156 p_Expenditure_Organization_Id NUMBER,
157 p_Expenditure_Type VARCHAR2,
158 p_Pa_Default_Dist_Ccid NUMBER,
159 p_Pa_Quantity NUMBER,
160 p_Project_Id NUMBER,
161 p_Task_Id NUMBER,
162 p_Awt_Flag VARCHAR2,
163 p_Awt_Group_Id NUMBER,
164 p_Pay_Awt_Group_Id NUMBER,--bug6639866
165 p_Reference_1 VARCHAR2,
166 p_Reference_2 VARCHAR2,
167 p_Org_Id NUMBER,
168 p_global_attribute_category VARCHAR2 DEFAULT NULL,
169 p_global_attribute1 VARCHAR2 DEFAULT NULL,
170 p_global_attribute2 VARCHAR2 DEFAULT NULL,
171 p_global_attribute3 VARCHAR2 DEFAULT NULL,
172 p_global_attribute4 VARCHAR2 DEFAULT NULL,
173 p_global_attribute5 VARCHAR2 DEFAULT NULL,
174 p_global_attribute6 VARCHAR2 DEFAULT NULL,
175 p_global_attribute7 VARCHAR2 DEFAULT NULL,
176 p_global_attribute8 VARCHAR2 DEFAULT NULL,
177 p_global_attribute9 VARCHAR2 DEFAULT NULL,
178 p_global_attribute10 VARCHAR2 DEFAULT NULL,
179 p_global_attribute11 VARCHAR2 DEFAULT NULL,
180 p_global_attribute12 VARCHAR2 DEFAULT NULL,
181 p_global_attribute13 VARCHAR2 DEFAULT NULL,
182 p_global_attribute14 VARCHAR2 DEFAULT NULL,
183 p_global_attribute15 VARCHAR2 DEFAULT NULL,
184 p_global_attribute16 VARCHAR2 DEFAULT NULL,
185 p_global_attribute17 VARCHAR2 DEFAULT NULL,
186 p_global_attribute18 VARCHAR2 DEFAULT NULL,
187 p_global_attribute19 VARCHAR2 DEFAULT NULL,
188 p_global_attribute20 VARCHAR2 DEFAULT NULL,
189 p_calling_sequence IN VARCHAR2,
190 p_gl_date DATE,
191 p_Award_Id NUMBER,
192 p_approval_iteration NUMBER,
193 p_approval_ready_flag VARCHAR2 DEFAULT 'Y',
194 p_wfapproval_status VARCHAR2 DEFAULT 'NOT REQUIRED',
195 p_paid_on_behalf_employee_id NUMBER DEFAULT NULL,
196 p_amt_due_employee NUMBER DEFAULT NULL,
197 p_amt_due_ccard_company NUMBER DEFAULT NULL,
198 p_requester_id NUMBER DEFAULT NULL,
199 -- Invoice Lines Project Stage 1
200 p_quick_credit VARCHAR2 DEFAULT NULL,
201 p_credited_invoice_id NUMBER DEFAULT NULL,
202 p_distribution_set_id NUMBER DEFAULT NULL,
203 --ETAX: Invwkb
204 p_force_revalidation_flag VARCHAR2 DEFAULT NULL,
205 p_control_amount NUMBER DEFAULT NULL,
206 p_tax_related_invoice_id NUMBER DEFAULT NULL,
207 p_trx_business_category VARCHAR2 DEFAULT NULL,
208 p_user_defined_fisc_class VARCHAR2 DEFAULT NULL,
209 p_taxation_country VARCHAR2 DEFAULT NULL,
210 p_document_sub_type VARCHAR2 DEFAULT NULL,
211 p_supplier_tax_invoice_number VARCHAR2 DEFAULT NULL,
212 p_supplier_tax_invoice_date DATE DEFAULT NULL,
213 p_supplier_tax_exchange_rate NUMBER DEFAULT NULL,
214 p_tax_invoice_recording_date DATE DEFAULT NULL,
215 p_tax_invoice_internal_seq VARCHAR2 DEFAULT NULL, -- bug 8912305: modify
216 p_legal_entity_id NUMBER DEFAULT NULL,
217 p_quick_po_header_id NUMBER DEFAULT NULL,
218 P_PAYMENT_METHOD_CODE varchar2 ,
219 P_PAYMENT_REASON_CODE varchar2 default null,
223 P_BANK_CHARGE_BEARER varchar2 default null,
220 P_PAYMENT_REASON_COMMENTS varchar2 default null,
221 P_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
222 P_URI_CHECK_DIGIT varchar2 default null,
224 P_DELIVERY_CHANNEL_CODE varchar2 default null,
225 P_SETTLEMENT_PRIORITY varchar2 default null,
226 p_net_of_retainage_flag varchar2 default null,
227 p_release_amount_net_of_tax number default null,
228 p_port_of_entry_code varchar2 default null,
229 p_external_bank_account_id number default null,
230 p_party_id number default null,
231 p_party_site_id number default null,
232 /* bug 4931755 */
233 p_disc_is_inv_less_tax_flag varchar2 default null,
234 p_exclude_freight_from_disc varchar2 default null,
235 /* Bug 5087834 */
236 p_remit_msg1 varchar2 default null,
237 p_remit_msg2 varchar2 default null,
238 p_remit_msg3 varchar2 default null,
239 p_cust_registration_number varchar2 default null,
240 /* Third Party Payments*/
241 p_remit_to_supplier_name varchar2 default null,
242 p_remit_to_supplier_id number default null,
243 p_remit_to_supplier_site varchar2 default null,
244 p_remit_to_supplier_site_id number default null,
245 p_relationship_id number default null,
246 /* Bug 7831073 */
247 p_original_invoice_amount number default null,
248 p_dispute_reason varchar2 default null
249
250 )
251 IS
252
253 CURSOR C IS
254 SELECT rowid FROM ap_invoices_all
255 WHERE invoice_id = p_Invoice_Id;
256
257 CURSOR C2 IS
258 SELECT ap_invoices_s.nextval FROM sys.dual;
259
260 current_calling_sequence VARCHAR2(2000);
261 debug_info VARCHAR2(100);
262 BEGIN
263 -- Update the calling sequence
264
265 current_calling_sequence := 'AP_AI_TABLE_HANDLER_PKG.INSERT_ROW<-'||
266 p_calling_sequence;
267
268 -- Check uniqueness first
269 ap_ai_table_handler_pkg.check_unique(
270 p_ROWID,
271 p_INVOICE_NUM,
272 p_VENDOR_ID,
273 p_ORG_ID, -- Bug 5407785
274 P_PARTY_SITE_ID, /*Bug9105666*/
275 P_VENDOR_SITE_ID, /*Bug9105666*/
276 current_calling_sequence);
277
278 IF (p_Invoice_Id is NULL) THEN
279 debug_info := 'Open cursor C2';
280 OPEN C2;
281 debug_info := 'Fetch cursor C2';
282 FETCH C2 INTO
283 p_Invoice_Id;
284 debug_info := 'hello Close cursor C2';
285 CLOSE C2;
286 END IF;
287
288 debug_info := 'Insert into ap_invoices_all';
289
290 INSERT INTO ap_invoices_all(
291 invoice_id,
292 last_update_date,
293 last_updated_by,
294 vendor_id,
295 invoice_num,
296 invoice_amount,
297 vendor_site_id,
298 amount_paid,
299 discount_amount_taken,
300 invoice_date,
301 source,
302 invoice_type_lookup_code,
303 description,
304 batch_id,
305 amount_applicable_to_discount,
306 terms_id,
307 terms_date,
308 goods_received_date,
309 invoice_received_date,
310 voucher_num,
311 approved_amount,
312 approval_status,
313 approval_description,
314 pay_group_lookup_code,
315 set_of_books_id,
316 accts_pay_code_combination_id,
317 recurring_payment_id,
318 invoice_currency_code,
319 payment_currency_code,
320 exchange_rate,
321 payment_amount_total,
322 payment_status_flag,
323 posting_status,
324 authorized_by,
325 attribute_category,
326 attribute1,
327 attribute2,
328 attribute3,
329 attribute4,
330 attribute5,
331 creation_date,
332 created_by,
333 vendor_prepay_amount,
334 base_amount,
335 exchange_rate_type,
336 exchange_date,
337 payment_cross_rate,
338 payment_cross_rate_type,
339 payment_cross_rate_date,
340 pay_curr_invoice_amount,
341 last_update_login,
342 original_prepayment_amount,
343 earliest_settlement_date,
344 attribute11,
345 attribute12,
346 attribute13,
347 attribute14,
348 attribute6,
349 attribute7,
350 attribute8,
351 attribute9,
352 attribute10,
353 attribute15,
354 cancelled_date,
355 cancelled_by,
356 cancelled_amount,
357 temp_cancelled_amount,
358 exclusive_payment_flag,
359 po_header_id,
360 doc_sequence_id,
361 doc_sequence_value,
362 doc_category_code,
363 expenditure_item_date,
364 expenditure_organization_id,
365 expenditure_type,
366 pa_default_dist_ccid,
367 pa_quantity,
368 project_id,
369 task_id,
370 awt_flag,
371 awt_group_id,
372 pay_awt_group_id,--bug6639866
373 reference_1,
377 global_attribute2,
374 reference_2,
375 global_attribute_category,
376 global_attribute1,
378 global_attribute3,
379 global_attribute4,
380 global_attribute5,
381 global_attribute6,
382 global_attribute7,
383 global_attribute8,
384 global_attribute9,
385 global_attribute10,
386 global_attribute11,
387 global_attribute12,
388 global_attribute13,
389 global_attribute14,
390 global_attribute15,
391 global_attribute16,
392 global_attribute17,
393 global_attribute18,
394 global_attribute19,
395 global_attribute20,
396 gl_date,
397 award_id,
398 approval_iteration,
399 approval_ready_flag,
400 wfapproval_status,
401 paid_on_behalf_employee_id,
402 amt_due_employee,
403 amt_due_ccard_company,
404 requester_id,
405 org_id,
406 -- Invoice Lines Project Stage 1
407 quick_credit,
408 credited_invoice_id,
409 distribution_set_id,
410 --ETAX:Invwkb
411 force_revalidation_flag,
412 control_amount,
413 tax_related_invoice_id,
414 trx_business_category,
415 user_defined_fisc_class,
416 taxation_country,
417 document_sub_type,
418 supplier_tax_invoice_number,
419 supplier_tax_invoice_date,
420 supplier_tax_exchange_rate,
421 tax_invoice_recording_date,
422 tax_invoice_internal_seq,
423 legal_entity_id,
424 --Contract Payments
425 quick_po_header_id,
426 PAYMENT_METHOD_CODE,
427 PAYMENT_REASON_CODE,
428 PAYMENT_REASON_COMMENTS,
429 UNIQUE_REMITTANCE_IDENTIFIER,
430 URI_CHECK_DIGIT,
431 BANK_CHARGE_BEARER,
432 DELIVERY_CHANNEL_CODE,
433 SETTLEMENT_PRIORITY,
434 net_of_retainage_flag,
435 release_amount_net_of_tax,
436 port_of_entry_code,
437 external_bank_account_id,
438 party_id,
439 party_site_id,
440 /* bug 4931755 */
441 disc_is_inv_less_tax_flag,
442 exclude_freight_from_discount,
443 REMITTANCE_MESSAGE1,
444 REMITTANCE_MESSAGE2,
445 REMITTANCE_MESSAGE3,
446 CUST_REGISTRATION_NUMBER,
447 /* Third Party Payments*/
448 REMIT_TO_SUPPLIER_NAME,
449 REMIT_TO_SUPPLIER_ID,
450 REMIT_TO_SUPPLIER_SITE,
451 REMIT_TO_SUPPLIER_SITE_ID,
452 RELATIONSHIP_ID,
453 /* Bug 7831073 */
454 original_invoice_amount,
455 dispute_reason
456 )
457 VALUES (
458 p_Invoice_Id,
459 p_Last_Update_Date,
460 p_Last_Updated_By,
461 p_Vendor_Id,
462 p_Invoice_Num,
463 p_Invoice_Amount,
464 p_Vendor_Site_Id,
465 p_Amount_Paid,
466 p_Discount_Amount_Taken,
467 p_Invoice_Date,
468 p_Source,
469 p_Invoice_Type_Lookup_Code,
470 p_Description,
471 p_Batch_Id,
472 p_Amt_Applicable_To_Discount,
473 p_Terms_Id,
474 p_Terms_Date,
475 p_Goods_Received_Date,
476 p_Invoice_Received_Date,
477 p_Voucher_Num,
478 p_Approved_Amount,
479 p_Approval_Status,
480 p_Approval_Description,
481 p_Pay_Group_Lookup_Code,
482 p_Set_Of_Books_Id,
483 p_Accts_Pay_CCId,
484 p_Recurring_Payment_Id,
485 p_Invoice_Currency_Code,
486 p_Payment_Currency_Code,
487 p_Exchange_Rate,
488 p_Payment_Amount_Total,
489 p_Payment_Status_Flag,
490 p_Posting_Status,
491 p_Authorized_By,
492 p_Attribute_Category,
493 p_Attribute1,
494 p_Attribute2,
495 p_Attribute3,
496 p_Attribute4,
497 p_Attribute5,
498 p_Creation_Date,
499 p_Created_By,
500 p_Vendor_Prepay_Amount,
501 p_Base_Amount,
502 p_Exchange_Rate_Type,
503 p_Exchange_Date,
504 p_Payment_Cross_Rate,
505 p_Payment_Cross_Rate_Type,
506 p_Payment_Cross_Rate_Date,
507 p_Pay_Curr_Invoice_Amount,
508 p_Last_Update_Login,
509 p_Original_Prepayment_Amount,
510 p_Earliest_Settlement_Date,
511 p_Attribute11,
512 p_Attribute12,
513 p_Attribute13,
514 p_Attribute14,
515 p_Attribute6,
516 p_Attribute7,
517 p_Attribute8,
518 p_Attribute9,
519 p_Attribute10,
520 p_Attribute15,
521 p_Cancelled_Date,
522 p_Cancelled_By,
523 p_Cancelled_Amount,
524 p_Temp_Cancelled_Amount,
525 p_Exclusive_Payment_Flag,
526 p_Po_Header_Id,
527 p_Doc_Sequence_Id,
528 p_Doc_Sequence_Value,
529 p_Doc_Category_Code,
530 p_Expenditure_Item_Date,
531 p_Expenditure_Organization_Id,
532 p_Expenditure_Type,
533 p_Pa_Default_Dist_Ccid,
534 p_Pa_Quantity,
535 p_Project_Id,
536 p_Task_Id,
537 p_Awt_Flag,
538 p_Awt_Group_Id,
539 p_Pay_Awt_Group_Id,--bug6639866
540 p_Reference_1,
544 p_global_attribute2,
541 p_Reference_2,
542 p_global_attribute_category,
543 p_global_attribute1,
545 p_global_attribute3,
546 p_global_attribute4,
547 p_global_attribute5,
548 p_global_attribute6,
549 p_global_attribute7,
550 p_global_attribute8,
551 p_global_attribute9,
552 p_global_attribute10,
553 p_global_attribute11,
554 p_global_attribute12,
555 p_global_attribute13,
556 p_global_attribute14,
557 p_global_attribute15,
558 p_global_attribute16,
559 p_global_attribute17,
560 p_global_attribute18,
561 p_global_attribute19,
562 p_global_attribute20,
563 p_gl_date,
564 p_Award_Id,
565 p_approval_iteration,
566 p_approval_ready_flag,
567 p_wfapproval_status,
568 p_paid_on_behalf_employee_id,
569 p_amt_due_employee,
570 p_amt_due_ccard_company,
571 p_requester_id,
572 p_org_id,
573 -- Invoice Lines Project Stage 1
574 p_quick_credit,
575 p_credited_invoice_id,
576 p_distribution_set_id,
577 --ETAX: Invwkb
578 p_force_revalidation_flag,
579 p_control_amount,
580 p_tax_related_invoice_id,
581 p_trx_business_category,
582 p_user_defined_fisc_class,
583 p_taxation_country,
584 p_document_sub_type,
585 p_supplier_tax_invoice_number,
586 p_supplier_tax_invoice_date,
587 p_supplier_tax_exchange_rate,
588 p_tax_invoice_recording_date,
589 p_tax_invoice_internal_seq,
590 p_legal_entity_id,
591 p_quick_po_header_id,
592 P_PAYMENT_METHOD_CODE,
593 P_PAYMENT_REASON_CODE,
594 P_PAYMENT_REASON_COMMENTS,
595 P_UNIQUE_REMITTANCE_IDENTIFIER,
596 P_URI_CHECK_DIGIT,
597 P_BANK_CHARGE_BEARER,
598 P_DELIVERY_CHANNEL_CODE,
599 P_SETTLEMENT_PRIORITY,
600 p_net_of_retainage_flag,
601 p_release_amount_net_of_tax,
602 p_port_of_entry_code,
603 p_external_bank_account_id,
604 p_party_id,
605 p_party_site_id,
606 p_disc_is_inv_less_tax_flag,
607 p_exclude_freight_from_disc,
608 p_remit_msg1,
609 p_remit_msg2,
610 p_remit_msg3,
611 p_cust_registration_number,
612 /* Third Party Payments*/
613 p_remit_to_supplier_name,
614 p_remit_to_supplier_id,
615 p_remit_to_supplier_site,
616 p_remit_to_supplier_site_id,
617 p_relationship_id,
618 /* Bug 7831073 */
619 p_original_invoice_amount,
620 p_dispute_reason
621 );
622
623 debug_info := 'Open cursor C';
624 OPEN C;
625 debug_info := 'Fetch cursor C';
626 FETCH C INTO p_Rowid;
627 IF (C%NOTFOUND) THEN
628 debug_info := 'Close cursor C - ROW NOTFOUND';
629 CLOSE C;
630 RAISE NO_DATA_FOUND;
631 END IF;
632 debug_info := 'Close cursor C';
633 CLOSE C;
634
635 --Bug 4539462 DBI logging
636 AP_DBI_PKG.Maintain_DBI_Summary
637 (p_table_name => 'AP_INVOICES',
638 p_operation => 'I',
639 p_key_value1 => p_invoice_id,
640 p_calling_sequence => current_calling_sequence);
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 IF (SQLCODE <> -20001) THEN
645 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
646 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
647 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
648 current_calling_sequence);
649 FND_MESSAGE.SET_TOKEN('PARAMETERS',
650 'p_Rowid = '||p_Rowid
651 ||', p_invoice_id = '||p_invoice_id
652 );
653 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
654 END IF;
655 APP_EXCEPTION.RAISE_EXCEPTION;
656
657 END Insert_Row;
658
659 PROCEDURE Update_Row(
660 p_Rowid VARCHAR2,
661 p_Invoice_Id NUMBER,
662 p_Last_Update_Date DATE,
663 p_Last_Updated_By NUMBER,
664 p_Vendor_Id NUMBER,
665 p_Invoice_Num VARCHAR2,
666 p_Invoice_Amount NUMBER,
667 p_Vendor_Site_Id NUMBER,
668 p_Amount_Paid NUMBER,
669 p_Discount_Amount_Taken NUMBER,
670 p_Invoice_Date DATE,
671 p_Source VARCHAR2,
672 p_Invoice_Type_Lookup_Code VARCHAR2,
673 p_Description VARCHAR2,
674 p_Batch_Id NUMBER,
675 p_Amt_Applicable_To_Discount NUMBER,
676 p_Terms_Id NUMBER,
677 p_Terms_Date DATE,
678 p_Goods_Received_Date DATE,
679 p_Invoice_Received_Date DATE,
680 p_Voucher_Num VARCHAR2,
681 p_Approved_Amount NUMBER,
682 p_Approval_Status VARCHAR2,
683 p_Approval_Description VARCHAR2,
684 p_Pay_Group_Lookup_Code VARCHAR2,
685 p_Set_Of_Books_Id NUMBER,
689 p_Payment_Currency_Code VARCHAR2,
686 p_Accts_Pay_CCId NUMBER,
687 p_Recurring_Payment_Id NUMBER,
688 p_Invoice_Currency_Code VARCHAR2,
690 p_Exchange_Rate NUMBER,
691 p_Payment_Amount_Total NUMBER,
692 p_Payment_Status_Flag VARCHAR2,
693 p_Posting_Status VARCHAR2,
694 p_Authorized_By VARCHAR2,
695 p_Attribute_Category VARCHAR2,
696 p_Attribute1 VARCHAR2,
697 p_Attribute2 VARCHAR2,
698 p_Attribute3 VARCHAR2,
699 p_Attribute4 VARCHAR2,
700 p_Attribute5 VARCHAR2,
701 p_Vendor_Prepay_Amount NUMBER,
702 p_Base_Amount NUMBER,
703 p_Exchange_Rate_Type VARCHAR2,
704 p_Exchange_Date DATE,
705 p_Payment_Cross_Rate NUMBER,
706 p_Payment_Cross_Rate_Type VARCHAR2,
707 p_Payment_Cross_Rate_Date DATE,
708 p_Pay_Curr_Invoice_Amount NUMBER,
709 p_Last_Update_Login NUMBER,
710 p_Original_Prepayment_Amount NUMBER,
711 p_Earliest_Settlement_Date DATE,
712 p_Attribute11 VARCHAR2,
713 p_Attribute12 VARCHAR2,
714 p_Attribute13 VARCHAR2,
715 p_Attribute14 VARCHAR2,
716 p_Attribute6 VARCHAR2,
717 p_Attribute7 VARCHAR2,
718 p_Attribute8 VARCHAR2,
719 p_Attribute9 VARCHAR2,
720 p_Attribute10 VARCHAR2,
721 p_Attribute15 VARCHAR2,
722 p_Cancelled_Date DATE,
723 p_Cancelled_By NUMBER,
724 p_Cancelled_Amount NUMBER,
725 p_Temp_Cancelled_Amount NUMBER,
726 p_Exclusive_Payment_Flag VARCHAR2,
727 p_Po_Header_Id NUMBER,
728 p_Doc_Sequence_Id NUMBER,
729 p_Doc_Sequence_Value NUMBER,
730 p_Doc_Category_Code VARCHAR2,
731 p_Expenditure_Item_Date DATE,
732 p_Expenditure_Organization_Id NUMBER,
733 p_Expenditure_Type VARCHAR2,
734 p_Pa_Default_Dist_Ccid NUMBER,
735 p_Pa_Quantity NUMBER,
736 p_Project_Id NUMBER,
737 p_Task_Id NUMBER,
738 p_Awt_Flag VARCHAR2,
739 p_Awt_Group_Id NUMBER,
740 p_Pay_Awt_Group_Id NUMBER,--bug6639866
741 p_Reference_1 VARCHAR2,
742 p_Reference_2 VARCHAR2,
743 p_Org_Id NUMBER,
744 p_global_attribute_category VARCHAR2 DEFAULT NULL,
745 p_global_attribute1 VARCHAR2 DEFAULT NULL,
746 p_global_attribute2 VARCHAR2 DEFAULT NULL,
747 p_global_attribute3 VARCHAR2 DEFAULT NULL,
748 p_global_attribute4 VARCHAR2 DEFAULT NULL,
749 p_global_attribute5 VARCHAR2 DEFAULT NULL,
750 p_global_attribute6 VARCHAR2 DEFAULT NULL,
751 p_global_attribute7 VARCHAR2 DEFAULT NULL,
752 p_global_attribute8 VARCHAR2 DEFAULT NULL,
753 p_global_attribute9 VARCHAR2 DEFAULT NULL,
754 p_global_attribute10 VARCHAR2 DEFAULT NULL,
755 p_global_attribute11 VARCHAR2 DEFAULT NULL,
756 p_global_attribute12 VARCHAR2 DEFAULT NULL,
757 p_global_attribute13 VARCHAR2 DEFAULT NULL,
758 p_global_attribute14 VARCHAR2 DEFAULT NULL,
759 p_global_attribute15 VARCHAR2 DEFAULT NULL,
760 p_global_attribute16 VARCHAR2 DEFAULT NULL,
761 p_global_attribute17 VARCHAR2 DEFAULT NULL,
762 p_global_attribute18 VARCHAR2 DEFAULT NULL,
763 p_global_attribute19 VARCHAR2 DEFAULT NULL,
764 p_global_attribute20 VARCHAR2 DEFAULT NULL,
765 p_calling_sequence IN VARCHAR2,
766 p_gl_date DATE,
767 p_Award_Id NUMBER,
768 p_approval_iteration NUMBER,
769 p_approval_ready_flag VARCHAR2 DEFAULT 'Y',
770 p_wfapproval_status VARCHAR2 DEFAULT 'NOT REQUIRED',
771 p_requester_id NUMBER DEFAULT NULL,
772 -- Invoice Lines Project Stage 1
773 p_quick_credit VARCHAR2 DEFAULT NULL,
774 p_credited_invoice_id NUMBER DEFAULT NULL,
775 p_distribution_set_id NUMBER DEFAULT NULL,
776 p_FORCE_REVALIDATION_FLAG VARCHAR2 DEFAULT NULL,
777 p_CONTROL_AMOUNT NUMBER DEFAULT NULL,
778 p_TAX_RELATED_INVOICE_ID NUMBER DEFAULT NULL,
779 p_TRX_BUSINESS_CATEGORY VARCHAR2 DEFAULT NULL,
780 p_USER_DEFINED_FISC_CLASS VARCHAR2 DEFAULT NULL,
781 p_TAXATION_COUNTRY VARCHAR2 DEFAULT NULL,
785 p_SUPPLIER_TAX_EXCHANGE_RATE NUMBER DEFAULT NULL,
782 p_DOCUMENT_SUB_TYPE VARCHAR2 DEFAULT NULL,
783 p_SUPPLIER_TAX_INVOICE_NUMBER VARCHAR2 DEFAULT NULL,
784 p_SUPPLIER_TAX_INVOICE_DATE DATE DEFAULT NULL,
786 p_TAX_INVOICE_RECORDING_DATE DATE DEFAULT NULL,
787 p_TAX_INVOICE_INTERNAL_SEQ VARCHAR2 DEFAULT NULL, -- bug 8912305: modify
788 p_QUICK_PO_HEADER_ID NUMBER DEFAULT NULL,
789 P_PAYMENT_METHOD_CODE varchar2 ,
790 P_PAYMENT_REASON_CODE varchar2 default null,
791 P_PAYMENT_REASON_COMMENTS varchar2 default null,
792 P_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
793 P_URI_CHECK_DIGIT varchar2 default null,
794 P_BANK_CHARGE_BEARER varchar2 default null,
795 P_DELIVERY_CHANNEL_CODE varchar2 default null,
796 P_SETTLEMENT_PRIORITY varchar2 default null,
797 p_net_of_retainage_flag varchar2 default null,
798 p_release_amount_net_of_tax number default null,
799 p_port_of_entry_code varchar2 default null,
800 p_external_bank_account_id number default null,
801 p_party_id number default null,
802 p_party_site_id number default null,
803 p_disc_is_inv_less_tax_flag varchar2 default null,
804 p_exclude_freight_from_disc varchar2 default null,
805 -- Bug 5087834
806 p_remit_msg1 varchar2 default null,
807 p_remit_msg2 varchar2 default null,
808 p_remit_msg3 varchar2 default null,
809 /* Third Party Payments*/
810 p_remit_to_supplier_name varchar2 default null,
811 p_remit_to_supplier_id number default null,
812 p_remit_to_supplier_site varchar2 default null,
813 p_remit_to_supplier_site_id number default null,
814 p_relationship_id number default null,
815 /* Bug 7831073 */
816 p_original_invoice_amount number default null,
817 p_dispute_reason varchar2 default null
818 )
819 IS
820 current_calling_sequence VARCHAR2(2000);
821 debug_info VARCHAR2(100);
822 l_invoice_id NUMBER;
823 BEGIN
824
825 -- Update the calling sequence
826
827 current_calling_sequence :=
828 'AP_AI_TABLE_HANDLER_PKG.UPDATE_ROW<-'||p_calling_sequence;
829
830 -- Check uniqueness first
831 ap_ai_table_handler_pkg.check_unique(
832 p_ROWID,
833 p_INVOICE_NUM,
834 p_VENDOR_ID,
835 p_ORG_ID, -- Bug 5407785
836 P_PARTY_SITE_ID, /*Bug9105666*/
837 P_VENDOR_SITE_ID, /*Bug9105666*/
838 current_calling_sequence);
839
840 debug_info := 'Update ap_invoices';
841 UPDATE ap_invoices_all
842 SET
843 invoice_id = p_Invoice_Id,
844 last_update_date = p_Last_Update_Date,
845 last_updated_by = p_Last_Updated_By,
846 vendor_id = p_Vendor_Id,
847 invoice_num = p_Invoice_Num,
848 invoice_amount = p_Invoice_Amount,
849 vendor_site_id = p_Vendor_Site_Id,
850 amount_paid = p_Amount_Paid,
851 discount_amount_taken = p_Discount_Amount_Taken,
852 invoice_date = p_Invoice_Date,
853 source = p_Source,
854 invoice_type_lookup_code = p_Invoice_Type_Lookup_Code,
855 description = p_Description,
856 batch_id = p_Batch_Id,
857 amount_applicable_to_discount = p_Amt_Applicable_To_Discount,
858 terms_id = p_Terms_Id,
859 terms_date = p_Terms_Date,
860 goods_received_date = p_Goods_Received_Date,
861 invoice_received_date = p_Invoice_Received_Date,
862 voucher_num = p_Voucher_Num,
863 approved_amount = p_Approved_Amount,
864 approval_status = p_Approval_Status,
865 approval_description = p_Approval_Description,
866 pay_group_lookup_code = p_Pay_Group_Lookup_Code,
867 set_of_books_id = p_Set_Of_Books_Id,
868 accts_pay_code_combination_id = p_Accts_Pay_CCId,
869 recurring_payment_id = p_Recurring_Payment_Id,
870 invoice_currency_code = p_Invoice_Currency_Code,
871 payment_currency_code = p_Payment_Currency_Code,
872 exchange_rate = p_Exchange_Rate,
873 payment_amount_total = p_Payment_Amount_Total,
874 payment_status_flag = p_Payment_Status_Flag,
875 posting_status = p_Posting_Status,
876 authorized_by = p_Authorized_By,
877 attribute_category = p_Attribute_Category,
878 attribute1 = p_Attribute1,
879 attribute2 = p_Attribute2,
880 attribute3 = p_Attribute3,
881 attribute4 = p_Attribute4,
882 attribute5 = p_Attribute5,
883 vendor_prepay_amount = p_Vendor_Prepay_Amount,
884 base_amount = p_Base_Amount,
885 exchange_rate_type = p_Exchange_Rate_Type,
886 exchange_date = p_Exchange_Date,
887 payment_cross_rate = p_Payment_Cross_Rate,
888 payment_cross_rate_type = p_Payment_Cross_Rate_Type,
889 payment_cross_rate_date = p_Payment_Cross_Rate_Date,
890 pay_curr_invoice_amount = p_Pay_Curr_Invoice_Amount,
894 attribute11 = p_Attribute11,
891 last_update_login = p_Last_Update_Login,
892 original_prepayment_amount = p_Original_Prepayment_Amount,
893 earliest_settlement_date = p_Earliest_Settlement_Date,
895 attribute12 = p_Attribute12,
896 attribute13 = p_Attribute13,
897 attribute14 = p_Attribute14,
898 attribute6 = p_Attribute6,
899 attribute7 = p_Attribute7,
900 attribute8 = p_Attribute8,
901 attribute9 = p_Attribute9,
902 attribute10 = p_Attribute10,
903 attribute15 = p_Attribute15,
904 cancelled_date = p_Cancelled_Date,
905 cancelled_by = p_Cancelled_By,
906 cancelled_amount = p_Cancelled_Amount,
907 temp_cancelled_amount = p_Temp_Cancelled_Amount,
908 exclusive_payment_flag = p_Exclusive_Payment_Flag,
909 po_header_id = p_Po_Header_Id,
910 doc_sequence_id = p_Doc_Sequence_Id,
911 doc_sequence_value = p_Doc_Sequence_Value,
912 doc_category_code = p_Doc_Category_Code,
913 expenditure_item_date = p_Expenditure_Item_Date,
914 expenditure_organization_id = p_Expenditure_Organization_Id,
915 expenditure_type = p_Expenditure_Type,
916 pa_default_dist_ccid = p_Pa_Default_Dist_Ccid,
917 pa_quantity = p_Pa_Quantity,
918 project_id = p_Project_Id,
919 task_id = p_Task_Id,
920 awt_flag = p_Awt_Flag,
921 awt_group_id = p_Awt_Group_Id,
922 pay_awt_group_id = p_Pay_Awt_Group_Id,--bug6639866
923 reference_1 = p_Reference_1,
924 reference_2 = p_Reference_2,
925 global_attribute_category = p_global_attribute_category,
926 global_attribute1 = p_global_attribute1,
927 global_attribute2 = p_global_attribute2,
928 global_attribute3 = p_global_attribute3,
929 global_attribute4 = p_global_attribute4,
930 global_attribute5 = p_global_attribute5,
931 global_attribute6 = p_global_attribute6,
932 global_attribute7 = p_global_attribute7,
933 global_attribute8 = p_global_attribute8,
934 global_attribute9 = p_global_attribute9,
935 global_attribute10 = p_global_attribute10,
936 global_attribute11 = p_global_attribute11,
937 global_attribute12 = p_global_attribute12,
938 global_attribute13 = p_global_attribute13,
939 global_attribute14 = p_global_attribute14,
940 global_attribute15 = p_global_attribute15,
941 global_attribute16 = p_global_attribute16,
942 global_attribute17 = p_global_attribute17,
943 global_attribute18 = p_global_attribute18,
944 global_attribute19 = p_global_attribute19,
945 global_attribute20 = p_global_attribute20,
946 award_id = p_Award_Id,
947 gl_date = p_gl_date,
948 approval_iteration = p_approval_iteration,
949 approval_ready_flag = p_approval_ready_flag,
950 wfapproval_status = p_wfapproval_status,
951 requester_id = p_requester_id,
952 -- Invoice Lines Project Stage 1
953 quick_credit = p_quick_credit,
954 credited_invoice_id = p_credited_invoice_id,
955 distribution_set_id = p_distribution_set_id,
956 --ETAX: Invwkb
957 force_revalidation_flag = p_force_revalidation_flag,
958 control_amount = p_control_amount,
959 tax_related_invoice_id = p_tax_related_invoice_id,
960 trx_business_category = p_trx_business_category,
961 user_defined_fisc_class = p_user_defined_fisc_class,
962 taxation_country = p_taxation_country,
963 document_sub_type = p_document_sub_type,
964 supplier_tax_invoice_number = p_supplier_tax_invoice_number,
965 supplier_tax_invoice_date = p_supplier_tax_invoice_date,
966 supplier_tax_exchange_rate = p_supplier_tax_exchange_rate,
967 tax_invoice_recording_date = p_tax_invoice_recording_date,
968 tax_invoice_internal_seq = p_tax_invoice_internal_seq,
969 quick_po_header_id = p_quick_po_header_id,
970 PAYMENT_METHOD_CODE = p_PAYMENT_METHOD_CODE,
971 PAYMENT_REASON_CODE = p_PAYMENT_REASON_CODE,
972 PAYMENT_REASON_COMMENTS = P_PAYMENT_REASON_COMMENTS,
973 UNIQUE_REMITTANCE_IDENTIFIER = p_UNIQUE_REMITTANCE_IDENTIFIER,
974 URI_CHECK_DIGIT = p_URI_CHECK_DIGIT,
975 BANK_CHARGE_BEARER = p_BANK_CHARGE_BEARER,
976 DELIVERY_CHANNEL_CODE = p_DELIVERY_CHANNEL_CODE,
977 SETTLEMENT_PRIORITY = p_SETTLEMENT_PRIORITY,
978 net_of_retainage_flag = p_net_of_retainage_flag,
979 release_amount_net_of_tax = p_release_amount_net_of_tax,
980 port_of_entry_code = p_port_of_entry_code,
981 external_bank_account_id = p_external_bank_account_id,
982 party_id = p_party_id,
983 party_site_id = p_party_site_id,
984 /* bug 4931755 */
988 REMITTANCE_MESSAGE2 = p_remit_msg2,
985 disc_is_inv_less_tax_flag = p_disc_is_inv_less_tax_flag,
986 exclude_freight_from_discount = p_exclude_freight_from_disc,
987 REMITTANCE_MESSAGE1 = p_remit_msg1,
989 REMITTANCE_MESSAGE3 = p_remit_msg3,
990 /* Third Party Payments*/
991 remit_to_supplier_name = p_remit_to_supplier_name,
992 remit_to_supplier_id = p_remit_to_supplier_id,
993 remit_to_supplier_site = p_remit_to_supplier_site,
994 remit_to_supplier_site_id = p_remit_to_supplier_site_id,
995 relationship_id = p_relationship_id,
996 /* Bug 7831073 */
997 original_invoice_amount = p_original_invoice_amount,
998 dispute_reason = p_dispute_reason
999 WHERE rowid = p_rowid;
1000
1001 IF (SQL%NOTFOUND) THEN
1002 RAISE NO_DATA_FOUND;
1003 END IF;
1004
1005 SELECT invoice_id
1006 INTO l_invoice_id
1007 FROM ap_invoices_all
1008 WHERE rowid = p_rowid;
1009
1010 --Bug 4539462 DBI logging
1011 AP_DBI_PKG.Maintain_DBI_Summary
1012 (p_table_name => 'AP_INVOICES',
1013 p_operation => 'U',
1014 p_key_value1 => P_invoice_id,
1015 p_calling_sequence => current_calling_sequence);
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 IF (SQLCODE <> -20001) THEN
1020 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1021 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1022 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1023 current_calling_sequence);
1024 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1025 'p_Rowid = '||p_Rowid
1026 ||', p_invoice_id = '||p_invoice_id
1027 );
1028 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1029 END IF;
1030 APP_EXCEPTION.RAISE_EXCEPTION;
1031
1032
1033 END Update_Row;
1034
1035
1036 PROCEDURE Delete_Row(
1037 p_Rowid VARCHAR2,
1038 p_calling_sequence IN VARCHAR2)
1039 IS
1040 l_invoice_id NUMBER;
1041 current_calling_sequence VARCHAR2(2000);
1042 debug_info VARCHAR2(100);
1043 l_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1044
1045 /*Start of 7388641*/
1046 l_invoice_type_lookup_code AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
1047 l_return_status_service VARCHAR2(50);
1048 l_msg_count NUMBER;
1049 l_msg_data VARCHAR2(100);
1050 l_transaction_rec ZX_API_PUB.TRANSACTION_REC_TYPE;
1051 l_event_class_code VARCHAR2(25) := NULL;
1052 l_event_type_code VARCHAR2(25) := NULL;
1053 l_error_code VARCHAR2(25);
1054 l_success BOOLEAN;
1055
1056 /*End of 7388641*/
1057
1058 BEGIN
1059 -- Update the calling sequence
1060 --
1061 current_calling_sequence :=
1062 'AP_AI_TABLE_HANDLER_PKG.DELETE_ROW<-'||p_calling_sequence;
1063
1064 -- Get the invoice_id
1065 debug_info := 'Get the invoice_id';
1066
1067 /*7388641 also selecting invoice_type_lookup_code*/
1068 SELECT invoice_id,invoice_type_lookup_code
1069 INTO l_invoice_id,l_invoice_type_lookup_code
1070 FROM ap_invoices
1071 WHERE rowid = p_rowid;
1072
1073 -- Delete attachments
1074 debug_info := 'Delete from fnd_attachments';
1075 fnd_attached_documents2_pkg.delete_attachments(
1076 'AP_INVOICES',
1077 l_invoice_id);
1078
1079 -- Delete from the child entities
1080 -- debug_info := 'Delete from child entity - jg_zz_invoice_info';
1081
1082 -- DELETE FROM jg_zz_invoice_info
1083 -- WHERE invoice_id = l_invoice_id;
1084
1085 debug_info := 'Delete from child entity - ap_invoice_distributions';
1086
1087 DELETE FROM ap_invoice_distributions_all
1088 WHERE invoice_id = l_invoice_id
1089 RETURNING invoice_distribution_id
1090 BULK COLLECT INTO l_key_value_list;
1091
1092 --Bugfix:4670908
1093 debug_info := 'Delete from child entity - ap_invoice_lines';
1094 DELETE FROM ap_invoice_lines_all
1095 WHERE invoice_id = l_invoice_id;
1096
1097 --added back in the following deletes
1098 debug_info := 'Delete from child entity - ap_payment_schedules';
1099 delete from ap_payment_schedules_all
1100 where invoice_id = l_invoice_id;
1101
1102 debug_info := 'Delete from child entity - ap_holds';
1103 delete from ap_holds_all
1104 where invoice_id = l_invoice_id;
1105
1106 --7388641 to de orphan records in tax tables---
1107
1108 debug_info := 'Delete from child entity - ap_self_assessed_tax_dist';
1109 DELETE FROM ap_self_assessed_tax_dist_all
1110 WHERE invoice_id = l_invoice_id;
1111
1112 debug_info := 'Before deleting data in Tax tables taking required data';
1113
1114 l_transaction_rec.application_id := 200;
1115 l_transaction_rec.entity_code := 'AP_INVOICES';
1116 l_transaction_rec.trx_id := l_invoice_id;
1117
1118 l_success := AP_ETAX_UTILITY_PKG.Get_Event_Class_Code(
1119 P_Invoice_Type_Lookup_Code => l_invoice_type_lookup_code,
1120 P_Event_Class_Code => l_event_class_code,
1121 P_error_code => l_error_code,
1122 P_calling_sequence => current_calling_sequence);
1123
1124 IF (l_success) THEN
1125 l_transaction_rec.event_class_code := l_event_class_code;
1126 END IF;
1127
1128 l_success := AP_ETAX_UTILITY_PKG.Get_Event_Type_Code(
1129 P_Event_Class_Code => l_event_class_code,
1133 P_Error_Code => l_error_code,
1130 P_Calling_Mode => 'DELETE INVOICE' ,
1131 P_eTax_Already_called_flag => NULL,
1132 P_Event_Type_Code => l_event_type_code,
1134 P_Calling_Sequence => current_calling_sequence);
1135
1136 IF (l_success) THEN
1137 l_transaction_rec.event_type_code := l_event_type_code;
1138 END IF;
1139
1140 debug_info := 'Calling ZX API to delete entries in Tax tables';
1141
1142 zx_api_pub.global_document_update(
1143 p_api_version => 1.0, --in parameter
1144 p_init_msg_list => FND_API.G_TRUE, --in parameter
1145 p_commit => FND_API.G_FALSE, --in parameter
1146 p_validation_level => FND_API.G_VALID_LEVEL_FULL,--in out parameter
1147 p_transaction_rec => l_transaction_rec,--in parameter
1148 x_return_status => l_return_status_service,--out parameter
1149 x_msg_count => l_msg_count, --out parameter
1150 x_msg_data => l_msg_data); --out parameter
1151
1152 --End of 7388641 --
1153
1154 debug_info := 'Delete from ap_invoices';
1155 DELETE FROM ap_invoices_all
1156 WHERE rowid = p_Rowid;
1157
1158 --Bug 4539462 DBI logging
1159 AP_DBI_PKG.Maintain_DBI_Summary
1160 (p_table_name => 'AP_INVOICES',
1161 p_operation => 'D',
1162 p_key_value1 => l_invoice_id,
1163 p_calling_sequence => current_calling_sequence);
1164
1165 IF (SQL%NOTFOUND) THEN
1166 RAISE NO_DATA_FOUND;
1167 END IF;
1168
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 IF (SQLCODE <> -20001) THEN
1172 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1173 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1174 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1175 current_calling_sequence);
1176 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1177 'p_Rowid = '||p_Rowid
1178 );
1179 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1180 END IF;
1181 APP_EXCEPTION.RAISE_EXCEPTION;
1182
1183
1184 END DELETE_ROW;
1185
1186 END AP_AI_TABLE_HANDLER_PKG;