[Home] [Help]
PACKAGE BODY: APPS.AP_INVOICES_PKG
Source
1 PACKAGE BODY AP_INVOICES_PKG AS
2 /* $Header: apiinceb.pls 120.49.12010000.4 2009/02/04 05:57:29 ctetala ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Invoice_Id IN OUT NOCOPY NUMBER,
6 X_Last_Update_Date DATE,
7 X_Last_Updated_By NUMBER,
8 X_Vendor_Id NUMBER,
9 X_Invoice_Num VARCHAR2,
10 X_Invoice_Amount NUMBER,
11 X_Vendor_Site_Id NUMBER,
12 X_Amount_Paid NUMBER,
13 X_Discount_Amount_Taken NUMBER,
14 X_Invoice_Date DATE,
15 X_Source VARCHAR2,
16 X_Invoice_Type_Lookup_Code VARCHAR2,
17 X_Description VARCHAR2,
18 X_Batch_Id NUMBER,
19 X_Amt_Applicable_To_Discount NUMBER,
20 X_Terms_Id NUMBER,
21 X_Terms_Date DATE,
22 X_Goods_Received_Date DATE,
23 X_Invoice_Received_Date DATE,
24 X_Voucher_Num VARCHAR2,
25 X_Approved_Amount NUMBER,
26 X_Approval_Status VARCHAR2,
27 X_Approval_Description VARCHAR2,
28 X_Pay_Group_Lookup_Code VARCHAR2,
29 X_Set_Of_Books_Id NUMBER,
30 X_Accts_Pay_CCId NUMBER,
31 X_Recurring_Payment_Id NUMBER,
32 X_Invoice_Currency_Code VARCHAR2,
33 X_Payment_Currency_Code VARCHAR2,
34 X_Exchange_Rate NUMBER,
35 X_Payment_Amount_Total NUMBER,
36 X_Payment_Status_Flag VARCHAR2,
37 X_Posting_Status VARCHAR2,
38 X_Authorized_By VARCHAR2,
39 X_Attribute_Category VARCHAR2,
40 X_Attribute1 VARCHAR2,
41 X_Attribute2 VARCHAR2,
42 X_Attribute3 VARCHAR2,
43 X_Attribute4 VARCHAR2,
44 X_Attribute5 VARCHAR2,
45 X_Creation_Date DATE,
46 X_Created_By NUMBER,
47 X_Vendor_Prepay_Amount NUMBER,
48 X_Base_Amount NUMBER,
49 X_Exchange_Rate_Type VARCHAR2,
50 X_Exchange_Date DATE,
51 X_Payment_Cross_Rate NUMBER,
52 X_Payment_Cross_Rate_Type VARCHAR2,
53 X_Payment_Cross_Rate_Date Date,
54 X_Pay_Curr_Invoice_Amount NUMBER,
55 X_Last_Update_Login NUMBER,
56 X_Original_Prepayment_Amount NUMBER,
57 X_Earliest_Settlement_Date DATE,
58 X_Attribute11 VARCHAR2,
59 X_Attribute12 VARCHAR2,
60 X_Attribute13 VARCHAR2,
61 X_Attribute14 VARCHAR2,
62 X_Attribute6 VARCHAR2,
63 X_Attribute7 VARCHAR2,
64 X_Attribute8 VARCHAR2,
65 X_Attribute9 VARCHAR2,
66 X_Attribute10 VARCHAR2,
67 X_Attribute15 VARCHAR2,
68 X_Cancelled_Date DATE,
69 X_Cancelled_By NUMBER,
70 X_Cancelled_Amount NUMBER,
71 X_Temp_Cancelled_Amount NUMBER,
72 X_Exclusive_Payment_Flag VARCHAR2,
73 X_Po_Header_Id NUMBER,
74 X_Doc_Sequence_Id NUMBER,
75 X_Doc_Sequence_Value NUMBER,
76 X_Doc_Category_Code VARCHAR2,
77 X_Expenditure_Item_Date DATE,
78 X_Expenditure_Organization_Id NUMBER,
79 X_Expenditure_Type VARCHAR2,
80 X_Pa_Default_Dist_Ccid NUMBER,
81 X_Pa_Quantity NUMBER,
82 X_Project_Id NUMBER,
83 X_Task_Id NUMBER,
84 X_Awt_Flag VARCHAR2,
85 X_Awt_Group_Id NUMBER,
86 X_Pay_Awt_Group_Id NUMBER,--bug6639866
87 X_Reference_1 VARCHAR2,
88 X_Reference_2 VARCHAR2,
89 X_Org_Id NUMBER,
90 X_global_attribute_category VARCHAR2 DEFAULT NULL,
91 X_global_attribute1 VARCHAR2 DEFAULT NULL,
92 X_global_attribute2 VARCHAR2 DEFAULT NULL,
93 X_global_attribute3 VARCHAR2 DEFAULT NULL,
94 X_global_attribute4 VARCHAR2 DEFAULT NULL,
95 X_global_attribute5 VARCHAR2 DEFAULT NULL,
96 X_global_attribute6 VARCHAR2 DEFAULT NULL,
97 X_global_attribute7 VARCHAR2 DEFAULT NULL,
98 X_global_attribute8 VARCHAR2 DEFAULT NULL,
99 X_global_attribute9 VARCHAR2 DEFAULT NULL,
100 X_global_attribute10 VARCHAR2 DEFAULT NULL,
101 X_global_attribute11 VARCHAR2 DEFAULT NULL,
102 X_global_attribute12 VARCHAR2 DEFAULT NULL,
103 X_global_attribute13 VARCHAR2 DEFAULT NULL,
104 X_global_attribute14 VARCHAR2 DEFAULT NULL,
105 X_global_attribute15 VARCHAR2 DEFAULT NULL,
106 X_global_attribute16 VARCHAR2 DEFAULT NULL,
107 X_global_attribute17 VARCHAR2 DEFAULT NULL,
108 X_global_attribute18 VARCHAR2 DEFAULT NULL,
109 X_global_attribute19 VARCHAR2 DEFAULT NULL,
110 X_global_attribute20 VARCHAR2 DEFAULT NULL,
111 X_calling_sequence IN VARCHAR2,
112 X_gl_date DATE,
113 X_Award_Id NUMBER,
114 X_APPROVAL_ITERATION NUMBER DEFAULT NULL,
115 X_APPROVAL_READY_FLAG VARCHAR2 DEFAULT 'Y',
116 X_WFAPPROVAL_STATUS VARCHAR2 DEFAULT 'NOT REQUIRED',
117 X_REQUESTER_ID NUMBER DEFAULT NULL,
118 -- Invoice Lines Project Stage 1
119 X_QUICK_CREDIT VARCHAR2 DEFAULT NULL,
120 X_CREDITED_INVOICE_ID NUMBER DEFAULT NULL,
121 X_DISTRIBUTION_SET_ID NUMBER DEFAULT NULL,
122 --ETAX: Invwkb
123 X_FORCE_REVALIDATION_FLAG VARCHAR2 DEFAULT NULL,
124 X_CONTROL_AMOUNT NUMBER DEFAULT NULL,
125 X_TAX_RELATED_INVOICE_ID NUMBER DEFAULT NULL,
126 X_TRX_BUSINESS_CATEGORY VARCHAR2 DEFAULT NULL,
127 X_USER_DEFINED_FISC_CLASS VARCHAR2 DEFAULT NULL,
128 X_TAXATION_COUNTRY VARCHAR2 DEFAULT NULL,
129 X_DOCUMENT_SUB_TYPE VARCHAR2 DEFAULT NULL,
130 X_SUPPLIER_TAX_INVOICE_NUMBER VARCHAR2 DEFAULT NULL,
131 X_SUPPLIER_TAX_INVOICE_DATE DATE DEFAULT NULL,
132 X_SUPPLIER_TAX_EXCHANGE_RATE NUMBER DEFAULT NULL,
133 X_TAX_INVOICE_RECORDING_DATE DATE DEFAULT NULL,
134 X_TAX_INVOICE_INTERNAL_SEQ NUMBER DEFAULT NULL,
135 X_LEGAL_ENTITY_ID NUMBER DEFAULT NULL,
136 X_QUICK_PO_HEADER_ID NUMBER DEFAULT NULL,
137 x_PAYMENT_METHOD_CODE varchar2 ,
138 x_PAYMENT_REASON_CODE varchar2 default null,
139 X_PAYMENT_REASON_COMMENTS varchar2 default null,
140 x_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
141 x_URI_CHECK_DIGIT varchar2 default null,
142 x_BANK_CHARGE_BEARER varchar2 default null,
143 x_DELIVERY_CHANNEL_CODE varchar2 default null,
144 x_SETTLEMENT_PRIORITY varchar2 default null,
145 x_NET_OF_RETAINAGE_FLAG varchar2 default null,
146 x_RELEASE_AMOUNT_NET_OF_TAX number default null,
147 x_PORT_OF_ENTRY_CODE varchar2 default null,
148 x_external_bank_account_id number default null,
149 x_party_id number default null,
150 x_party_site_id number default null,
151 /* bug 4931755. Exclude Tax and Freight from Discount */
152 x_disc_is_inv_less_tax_flag varchar2 default null,
153 x_exclude_freight_from_disc varchar2 default null,
154 x_remit_msg1 varchar2 default null,
155 x_remit_msg2 varchar2 default null,
156 x_remit_msg3 varchar2 default null,
157 x_cust_registration_number varchar2 default null,
158 /* Third Party Payments*/
159 x_remit_to_supplier_name varchar2 default null,
160 x_remit_to_supplier_id number default null,
161 x_remit_to_supplier_site varchar2 default null,
162 x_remit_to_supplier_site_id number default null,
163 x_relationship_id number default null,
164 /* Bug 7831073 */
165 x_original_invoice_amount number default null,
166 x_dispute_reason varchar2 default null
167 ) IS
168 current_calling_sequence VARCHAR2(2000);
169 debug_info VARCHAR2(100);
170 l_le_id number(15);
171 BEGIN
172 -- Update the calling sequence
173 --
174 current_calling_sequence :=
175 'AP_INVOICES_PKG.INSERT_ROW<-'||X_calling_sequence;
176
177 -- Get LE Information
178 --
179 IF x_legal_entity_id IS NULL THEN
180
181 AP_UTILITIES_PKG.Get_Invoice_LE(
182 X_vendor_site_id,
183 X_accts_pay_ccid,
184 X_org_id,
185 l_le_id);
186
187 END IF;
188
189 AP_AI_TABLE_HANDLER_PKG.Insert_Row
190 (X_Rowid,
191 X_Invoice_Id,
192 X_Last_Update_Date,
193 X_Last_Updated_By,
194 X_Vendor_Id,
195 X_Invoice_Num,
196 X_Invoice_Amount,
197 X_Vendor_Site_Id,
198 X_Amount_Paid,
199 X_Discount_Amount_Taken,
200 X_Invoice_Date,
201 X_Source,
202 X_Invoice_Type_Lookup_Code,
203 X_Description,
204 X_Batch_Id,
205 X_Amt_Applicable_To_Discount,
206 X_Terms_Id,
207 X_Terms_Date,
208 X_Goods_Received_Date,
209 X_Invoice_Received_Date,
210 X_Voucher_Num,
211 X_Approved_Amount,
212 X_Approval_Status,
213 X_Approval_Description,
214 X_Pay_Group_Lookup_Code,
215 X_Set_Of_Books_Id,
216 X_Accts_Pay_CCId,
217 X_Recurring_Payment_Id,
218 X_Invoice_Currency_Code,
219 X_Payment_Currency_Code,
220 X_Exchange_Rate,
221 X_Payment_Amount_Total,
222 X_Payment_Status_Flag,
223 X_Posting_Status,
224 X_Authorized_By,
225 X_Attribute_Category,
226 X_Attribute1,
227 X_Attribute2,
228 X_Attribute3,
229 X_Attribute4,
230 X_Attribute5,
231 X_Creation_Date,
232 X_Created_By,
233 X_Vendor_Prepay_Amount,
234 X_Base_Amount,
235 X_Exchange_Rate_Type,
236 X_Exchange_Date,
237 X_Payment_Cross_Rate,
238 X_Payment_Cross_Rate_Type,
239 X_Payment_Cross_Rate_Date,
240 X_Pay_Curr_Invoice_Amount,
241 X_Last_Update_Login,
242 X_Original_Prepayment_Amount,
243 X_Earliest_Settlement_Date,
244 X_Attribute11,
245 X_Attribute12,
246 X_Attribute13,
247 X_Attribute14,
248 X_Attribute6,
249 X_Attribute7,
250 X_Attribute8,
251 X_Attribute9,
252 X_Attribute10,
253 X_Attribute15,
254 X_Cancelled_Date,
255 X_Cancelled_By,
256 X_Cancelled_Amount,
257 X_Temp_Cancelled_Amount,
258 X_Exclusive_Payment_Flag,
259 X_Po_Header_Id,
260 X_Doc_Sequence_Id,
261 X_Doc_Sequence_Value,
262 X_Doc_Category_Code,
263 X_Expenditure_Item_Date,
264 X_Expenditure_Organization_Id,
265 X_Expenditure_Type,
266 X_Pa_Default_Dist_Ccid,
267 X_Pa_Quantity,
268 X_Project_Id,
269 X_Task_Id,
270 X_Awt_Flag,
271 X_Awt_Group_Id,
272 X_Pay_Awt_Group_Id,--bug6639866
273 X_Reference_1,
274 X_Reference_2,
275 X_Org_id,
276 X_global_attribute_category,
277 X_global_attribute1,
278 X_global_attribute2,
279 X_global_attribute3,
280 X_global_attribute4,
281 X_global_attribute5,
282 X_global_attribute6,
283 X_global_attribute7,
284 X_global_attribute8,
285 X_global_attribute9,
286 X_global_attribute10,
287 X_global_attribute11,
288 X_global_attribute12,
289 X_global_attribute13,
290 X_global_attribute14,
291 X_global_attribute15,
292 X_global_attribute16,
293 X_global_attribute17,
294 X_global_attribute18,
295 X_global_attribute19,
296 X_global_attribute20,
297 current_calling_sequence,
298 X_gl_date,
299 X_Award_Id,
300 X_APPROVAL_ITERATION,
301 X_approval_ready_flag,
302 X_wfapproval_status,
303 NULL,
304 NULL,
305 NULL,
306 X_requester_id, --2289496
307 -- Invoice Lines Project Stage 1
308 X_quick_credit,
309 X_credited_invoice_id,
310 X_distribution_set_id,
311 --Etax: Invwkb
312 X_force_revalidation_flag,
313 X_CONTROL_AMOUNT,
314 X_TAX_RELATED_INVOICE_ID,
315 X_TRX_BUSINESS_CATEGORY,
316 X_USER_DEFINED_FISC_CLASS,
317 X_TAXATION_COUNTRY,
318 X_DOCUMENT_SUB_TYPE,
319 X_SUPPLIER_TAX_INVOICE_NUMBER,
320 X_SUPPLIER_TAX_INVOICE_DATE,
321 X_SUPPLIER_TAX_EXCHANGE_RATE,
322 X_TAX_INVOICE_RECORDING_DATE,
323 X_TAX_INVOICE_INTERNAL_SEQ,
324 NVL(X_LEGAL_ENTITY_ID,l_le_id),
325 X_QUICK_PO_HEADER_ID,
326 x_PAYMENT_METHOD_CODE,
327 x_PAYMENT_REASON_CODE,
328 x_PAYMENT_REASON_COMMENTS,
329 x_UNIQUE_REMITTANCE_IDENTIFIER,
330 x_URI_CHECK_DIGIT,
331 x_BANK_CHARGE_BEARER,
332 x_DELIVERY_CHANNEL_CODE,
333 x_SETTLEMENT_PRIORITY,
334 x_NET_OF_RETAINAGE_FLAG,
335 x_RELEASE_AMOUNT_NET_OF_TAX,
336 x_PORT_OF_ENTRY_CODE,
337 x_external_bank_account_id,
341 x_exclude_freight_from_disc,
338 x_party_id,
339 x_party_site_id,
340 x_disc_is_inv_less_tax_flag,
342 x_remit_msg1,
343 x_remit_msg2,
344 x_remit_msg3,
345 x_cust_registration_number,
346 x_remit_to_supplier_name,
347 x_remit_to_supplier_id,
348 x_remit_to_supplier_site,
349 x_remit_to_supplier_site_id,
350 x_relationship_id,
351 /* Bug 7831073 */
352 x_original_invoice_amount,
353 x_dispute_reason
354 );
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 IF (SQLCODE <> -20001) THEN
359 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
360 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
361 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
362 current_calling_sequence);
363 FND_MESSAGE.SET_TOKEN('PARAMETERS',
364 'X_Rowid = '||X_Rowid
365 ||', X_invoice_id = '||X_invoice_id
366 );
367 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
368 END IF;
369 APP_EXCEPTION.RAISE_EXCEPTION;
370
371
372 END Insert_Row;
373
374 PROCEDURE lock_Row(
375 X_Rowid VARCHAR2,
376 X_Invoice_Id NUMBER,
377 X_Vendor_Id NUMBER,
378 X_Invoice_Num VARCHAR2,
379 X_Invoice_Amount NUMBER,
380 X_Vendor_Site_Id NUMBER,
381 X_Amount_Paid NUMBER,
382 X_Discount_Amount_Taken NUMBER,
383 X_Invoice_Date DATE,
384 X_Source VARCHAR2,
385 X_Invoice_Type_Lookup_Code VARCHAR2,
386 X_Description VARCHAR2,
387 X_Batch_Id NUMBER,
388 X_Amt_Applicable_To_Discount NUMBER,
389 X_Terms_Id NUMBER,
390 X_Terms_Date DATE,
391 X_Goods_Received_Date DATE,
392 X_Invoice_Received_Date DATE,
393 X_Voucher_Num VARCHAR2,
394 X_Approved_Amount NUMBER,
395 X_Approval_Status VARCHAR2,
396 X_Approval_Description VARCHAR2,
397 X_Pay_Group_Lookup_Code VARCHAR2,
398 X_Set_Of_Books_Id NUMBER,
399 X_Accts_Pay_CCId NUMBER,
400 X_Recurring_Payment_Id NUMBER,
401 X_Invoice_Currency_Code VARCHAR2,
402 X_Payment_Currency_Code VARCHAR2,
403 X_Exchange_Rate NUMBER,
404 X_Payment_Amount_Total NUMBER,
405 X_Payment_Status_Flag VARCHAR2,
406 X_Posting_Status VARCHAR2,
407 X_Posting_Flag VARCHAR2,
408 X_Authorized_By VARCHAR2,
409 X_Attribute_Category VARCHAR2,
410 X_Attribute1 VARCHAR2,
411 X_Attribute2 VARCHAR2,
412 X_Attribute3 VARCHAR2,
413 X_Attribute4 VARCHAR2,
414 X_Attribute5 VARCHAR2,
415 X_Vendor_Prepay_Amount NUMBER,
416 X_Base_Amount NUMBER,
417 X_Exchange_Rate_Type VARCHAR2,
418 X_Exchange_Date DATE,
419 X_Payment_Cross_Rate NUMBER,
420 X_Payment_Cross_Rate_Type VARCHAR2,
421 X_Payment_Cross_Rate_Date DATE,
422 X_Pay_Curr_Invoice_Amount NUMBER,
423 X_Original_Prepayment_Amount NUMBER,
424 X_Earliest_Settlement_Date DATE,
425 X_Attribute11 VARCHAR2,
426 X_Attribute12 VARCHAR2,
427 X_Attribute13 VARCHAR2,
428 X_Attribute14 VARCHAR2,
429 X_Attribute6 VARCHAR2,
430 X_Attribute7 VARCHAR2,
431 X_Attribute8 VARCHAR2,
432 X_Attribute9 VARCHAR2,
433 X_Attribute10 VARCHAR2,
434 X_Attribute15 VARCHAR2,
435 X_Cancelled_Date DATE,
436 X_Cancelled_By NUMBER,
437 X_Cancelled_Amount NUMBER,
438 X_Temp_Cancelled_Amount NUMBER,
439 X_Exclusive_Payment_Flag VARCHAR2,
440 X_Po_Header_Id NUMBER,
441 X_Doc_Sequence_Id NUMBER,
442 X_Doc_Sequence_Value NUMBER,
443 X_Doc_Category_Code VARCHAR2,
444 X_Expenditure_Item_Date DATE,
445 X_Expenditure_Organization_Id NUMBER,
446 X_Expenditure_Type VARCHAR2,
447 X_Pa_Default_Dist_Ccid NUMBER,
448 X_Pa_Quantity NUMBER,
452 X_Awt_Group_Id NUMBER,
449 X_Project_Id NUMBER,
450 X_Task_Id NUMBER,
451 X_Awt_Flag VARCHAR2,
453 X_Pay_Awt_Group_Id NUMBER,--bug6639866
454 X_Reference_1 VARCHAR2,
455 X_Reference_2 VARCHAR2,
456 X_Org_Id NUMBER,
457 X_global_attribute_category VARCHAR2 DEFAULT NULL,
458 X_global_attribute1 VARCHAR2 DEFAULT NULL,
459 X_global_attribute2 VARCHAR2 DEFAULT NULL,
460 X_global_attribute3 VARCHAR2 DEFAULT NULL,
461 X_global_attribute4 VARCHAR2 DEFAULT NULL,
462 X_global_attribute5 VARCHAR2 DEFAULT NULL,
463 X_global_attribute6 VARCHAR2 DEFAULT NULL,
464 X_global_attribute7 VARCHAR2 DEFAULT NULL,
465 X_global_attribute8 VARCHAR2 DEFAULT NULL,
466 X_global_attribute9 VARCHAR2 DEFAULT NULL,
467 X_global_attribute10 VARCHAR2 DEFAULT NULL,
468 X_global_attribute11 VARCHAR2 DEFAULT NULL,
469 X_global_attribute12 VARCHAR2 DEFAULT NULL,
470 X_global_attribute13 VARCHAR2 DEFAULT NULL,
471 X_global_attribute14 VARCHAR2 DEFAULT NULL,
472 X_global_attribute15 VARCHAR2 DEFAULT NULL,
473 X_global_attribute16 VARCHAR2 DEFAULT NULL,
474 X_global_attribute17 VARCHAR2 DEFAULT NULL,
475 X_global_attribute18 VARCHAR2 DEFAULT NULL,
476 X_global_attribute19 VARCHAR2 DEFAULT NULL,
477 X_global_attribute20 VARCHAR2 DEFAULT NULL,
478 X_calling_sequence IN VARCHAR2,
479 X_gl_date DATE,
480 X_award_Id NUMBER,
481 X_approval_iteration NUMBER,
482 X_approval_ready_flag VARCHAR2,
483 X_wfapproval_status VARCHAR2,
484 X_requester_id NUMBER DEFAULT NULL,
485 -- Invoice Lines Project Stage 1
486 X_quick_credit VARCHAR2 DEFAULT NULL,
487 X_credited_invoice_id NUMBER DEFAULT NULL,
488 X_distribution_set_iD NUMBER DEFAULT NULL,
489 --ETAX: Invwkb
490 X_FORCE_REVALIDATION_FLAG VARCHAR2 DEFAULT NULL,
491 X_CONTROL_AMOUNT NUMBER DEFAULT NULL,
492 X_TAX_RELATED_INVOICE_ID NUMBER DEFAULT NULL,
493 X_TRX_BUSINESS_CATEGORY VARCHAR2 DEFAULT NULL,
494 X_USER_DEFINED_FISC_CLASS VARCHAR2 DEFAULT NULL,
495 X_TAXATION_COUNTRY VARCHAR2 DEFAULT NULL,
496 X_DOCUMENT_SUB_TYPE VARCHAR2 DEFAULT NULL,
497 X_SUPPLIER_TAX_INVOICE_NUMBER VARCHAR2 DEFAULT NULL,
498 X_SUPPLIER_TAX_INVOICE_DATE DATE DEFAULT NULL,
499 X_SUPPLIER_TAX_EXCHANGE_RATE NUMBER DEFAULT NULL,
500 X_TAX_INVOICE_RECORDING_DATE DATE DEFAULT NULL,
501 X_TAX_INVOICE_INTERNAL_SEQ NUMBER DEFAULT NULL,
502 X_QUICK_PO_HEADER_ID NUMBER DEFAULT NULL,
503 x_PAYMENT_METHOD_CODE varchar2 ,
504 x_PAYMENT_REASON_CODE varchar2 default null,
505 X_PAYMENT_REASON_COMMENTS varchar2 default null,
506 x_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
507 x_URI_CHECK_DIGIT varchar2 default null,
508 x_BANK_CHARGE_BEARER varchar2 default null,
509 x_DELIVERY_CHANNEL_CODE varchar2 default null,
510 x_SETTLEMENT_PRIORITY varchar2 default null,
511 x_NET_OF_RETAINAGE_FLAG varchar2 default null,
512 x_RELEASE_AMOUNT_NET_OF_TAX number default null,
513 x_PORT_OF_ENTRY_CODE varchar2 default null,
514 x_external_bank_account_id number default null,
515 x_party_id number default null,
516 x_party_site_id number default null,
517 /* bug 4931755. Exclude Tax and Freight from Discount */
518 x_disc_is_inv_less_tax_flag varchar2 default null,
519 x_exclude_freight_from_disc varchar2 default null,
520 -- Bug 5087834
521 x_remit_msg1 varchar2 default null,
522 x_remit_msg2 varchar2 default null,
523 x_remit_msg3 varchar2 default null,
524 /* Third Party Payments*/
525 x_remit_to_supplier_name varchar2 default null,
526 x_remit_to_supplier_id number default null,
527 x_remit_to_supplier_site varchar2 default null,
528 x_remit_to_supplier_site_id number default null,
529 x_relationship_id number default null,
530 /* Bug 7831073 */
531 x_original_invoice_amount number default null,
532 x_dispute_reason varchar2 default null
533 ) IS
534 CURSOR C IS
535 SELECT
536 INVOICE_ID,
537 LAST_UPDATE_DATE,
538 LAST_UPDATED_BY,
539 VENDOR_ID,
540 INVOICE_NUM,
541 SET_OF_BOOKS_ID,
542 INVOICE_CURRENCY_CODE,
543 PAYMENT_CURRENCY_CODE,
547 AMOUNT_PAID,
544 PAYMENT_CROSS_RATE,
545 INVOICE_AMOUNT,
546 VENDOR_SITE_ID,
548 DISCOUNT_AMOUNT_TAKEN,
549 INVOICE_DATE,
550 SOURCE,
551 INVOICE_TYPE_LOOKUP_CODE,
552 DESCRIPTION,
553 BATCH_ID,
554 AMOUNT_APPLICABLE_TO_DISCOUNT,
555 TERMS_ID,
556 TERMS_DATE,
557 PAY_GROUP_LOOKUP_CODE,
558 ACCTS_PAY_CODE_COMBINATION_ID,
559 PAYMENT_STATUS_FLAG,
560 CREATION_DATE,
561 CREATED_BY,
562 BASE_AMOUNT,
563 LAST_UPDATE_LOGIN,
564 EXCLUSIVE_PAYMENT_FLAG,
565 PO_HEADER_ID,
566 GOODS_RECEIVED_DATE,
567 INVOICE_RECEIVED_DATE,
568 VOUCHER_NUM,
569 APPROVED_AMOUNT,
570 RECURRING_PAYMENT_ID,
571 EXCHANGE_RATE,
572 EXCHANGE_RATE_TYPE,
573 EXCHANGE_DATE,
574 EARLIEST_SETTLEMENT_DATE,
575 ORIGINAL_PREPAYMENT_AMOUNT,
576 DOC_SEQUENCE_ID,
577 DOC_SEQUENCE_VALUE,
578 DOC_CATEGORY_CODE,
579 ATTRIBUTE1,
580 ATTRIBUTE2,
581 ATTRIBUTE3,
582 ATTRIBUTE4,
583 ATTRIBUTE5,
584 ATTRIBUTE6,
585 ATTRIBUTE7,
586 ATTRIBUTE8,
587 ATTRIBUTE9,
588 ATTRIBUTE10,
589 ATTRIBUTE11,
590 ATTRIBUTE12,
591 ATTRIBUTE13,
592 ATTRIBUTE14,
593 ATTRIBUTE15,
594 ATTRIBUTE_CATEGORY,
595 APPROVAL_STATUS,
596 APPROVAL_DESCRIPTION,
597 POSTING_STATUS,
598 AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID) POSTING_FLAG,
599 AUTHORIZED_BY,
600 CANCELLED_DATE,
601 CANCELLED_BY,
602 CANCELLED_AMOUNT,
603 TEMP_CANCELLED_AMOUNT,
604 PROJECT_ID,
605 TASK_ID,
606 EXPENDITURE_TYPE,
607 EXPENDITURE_ITEM_DATE,
608 PA_QUANTITY,
609 EXPENDITURE_ORGANIZATION_ID,
610 PA_DEFAULT_DIST_CCID,
611 VENDOR_PREPAY_AMOUNT,
612 PAYMENT_AMOUNT_TOTAL,
613 AWT_FLAG,
614 AWT_GROUP_ID,
615 PAY_AWT_GROUP_ID, --bug6639866
616 REFERENCE_1,
617 REFERENCE_2,
618 ORG_ID,
619 GLOBAL_ATTRIBUTE_CATEGORY,
620 GLOBAL_ATTRIBUTE1,
621 GLOBAL_ATTRIBUTE2,
622 GLOBAL_ATTRIBUTE3,
623 GLOBAL_ATTRIBUTE4,
624 GLOBAL_ATTRIBUTE5,
625 GLOBAL_ATTRIBUTE6,
626 GLOBAL_ATTRIBUTE7,
627 GLOBAL_ATTRIBUTE8,
628 GLOBAL_ATTRIBUTE9,
629 GLOBAL_ATTRIBUTE10,
630 GLOBAL_ATTRIBUTE11,
631 GLOBAL_ATTRIBUTE12,
632 GLOBAL_ATTRIBUTE13,
633 GLOBAL_ATTRIBUTE14,
634 GLOBAL_ATTRIBUTE15,
635 GLOBAL_ATTRIBUTE16,
636 GLOBAL_ATTRIBUTE17,
637 GLOBAL_ATTRIBUTE18,
638 GLOBAL_ATTRIBUTE19,
639 GLOBAL_ATTRIBUTE20,
640 PAYMENT_CROSS_RATE_TYPE,
641 PAYMENT_CROSS_RATE_DATE,
642 PAY_CURR_INVOICE_AMOUNT,
643 MRC_BASE_AMOUNT,
644 MRC_EXCHANGE_RATE,
645 MRC_EXCHANGE_RATE_TYPE,
646 MRC_EXCHANGE_DATE,
647 GL_DATE,
648 AWARD_ID,
649 APPROVAL_ITERATION,
650 APPROVAL_READY_FLAG,
651 WFAPPROVAL_STATUS,
652 REQUESTER_ID, --2289496
653 -- Invoice Lines Project Stage 1
654 QUICK_CREDIT,
655 CREDITED_INVOICE_ID,
656 DISTRIBUTION_SET_ID,
657 FORCE_REVALIDATION_FLAG,
658 CONTROL_AMOUNT,
659 TAX_RELATED_INVOICE_ID,
660 TRX_BUSINESS_CATEGORY,
661 USER_DEFINED_FISC_CLASS,
662 TAXATION_COUNTRY,
663 DOCUMENT_SUB_TYPE,
664 SUPPLIER_TAX_INVOICE_NUMBER,
665 SUPPLIER_TAX_INVOICE_DATE,
666 SUPPLIER_TAX_EXCHANGE_RATE,
667 TAX_INVOICE_RECORDING_DATE,
668 TAX_INVOICE_INTERNAL_SEQ,
669 QUICK_PO_HEADER_ID,
670 PAYMENT_METHOD_CODE,
671 PAYMENT_REASON_CODE,
672 PAYMENT_REASON_COMMENTS,
673 UNIQUE_REMITTANCE_IDENTIFIER,
674 URI_CHECK_DIGIT,
675 BANK_CHARGE_BEARER,
676 DELIVERY_CHANNEL_CODE,
677 SETTLEMENT_PRIORITY,
678 NET_OF_RETAINAGE_FLAG,
679 RELEASE_AMOUNT_NET_OF_TAX,
680 PORT_OF_ENTRY_CODE,
681 external_bank_account_id,
682 party_id,
683 party_site_id,
684 disc_is_inv_less_tax_flag,
685 exclude_freight_from_discount,
686 REMITTANCE_MESSAGE1,
687 REMITTANCE_MESSAGE2,
688 REMITTANCE_MESSAGE3,
689 REMIT_TO_SUPPLIER_NAME,
690 REMIT_TO_SUPPLIER_ID,
691 REMIT_TO_SUPPLIER_SITE,
692 REMIT_TO_SUPPLIER_SITE_ID,
693 RELATIONSHIP_ID,
694 /* Bug 7831073 */
695 original_invoice_amount,
696 dispute_reason
697 FROM ap_invoices_all
698 WHERE rowid = X_Rowid
699 FOR UPDATE of Invoice_Id NOWAIT;
700
701 Recinfo C%ROWTYPE;
702 first_conditions BOOLEAN := TRUE;
703 second_conditions BOOLEAN := TRUE;
707 BEGIN
704 current_calling_sequence VARCHAR2(2000);
705 debug_info VARCHAR2(100);
706
708 -- Update the calling sequence
709
710 current_calling_sequence :=
711 'AP_INVOICES_PKG.LOCK_ROW<-'||X_calling_sequence;
712
713 debug_info := 'Open cursor C';
714 OPEN C;
715 debug_info := 'Fetch cursor C';
716 FETCH C INTO Recinfo;
717
718 IF (C%NOTFOUND) THEN
719 debug_info := 'Close cursor C - ROW NOTFOUND';
720 CLOSE C;
721 RAISE NO_DATA_FOUND;
722 END IF;
723 debug_info := 'Close cursor C';
724 CLOSE C;
725
726 first_conditions :=
727 (
728 ( (Recinfo.invoice_id = X_Invoice_Id)
729 OR ( (Recinfo.invoice_id IS NULL)
730 AND (X_Invoice_Id IS NULL)))
731 AND ( (Recinfo.vendor_id = X_Vendor_Id)
732 OR ( (Recinfo.vendor_id IS NULL)
733 AND (X_Vendor_Id IS NULL)))
734 AND ( (Recinfo.invoice_num = X_Invoice_Num)
735 OR ( (Recinfo.invoice_num IS NULL)
736 AND (X_Invoice_Num IS NULL)))
737 AND ( (Recinfo.invoice_amount = X_Invoice_Amount)
738 OR ( (Recinfo.invoice_amount IS NULL)
739 AND (X_Invoice_Amount IS NULL)))
740 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
741 OR ( (Recinfo.vendor_site_id IS NULL)
742 AND (X_Vendor_Site_Id IS NULL)))
743 AND ( (Recinfo.amount_paid = X_Amount_Paid)
744 OR ( (Recinfo.amount_paid IS NULL)
745 AND (X_Amount_Paid IS NULL)))
746 AND ( (Recinfo.discount_amount_taken = X_Discount_Amount_Taken)
747 OR ( (Recinfo.discount_amount_taken IS NULL)
748 AND (X_Discount_Amount_Taken IS NULL)))
749 AND ( (Recinfo.invoice_date = X_Invoice_Date)
750 OR ( (Recinfo.invoice_date IS NULL)
751 AND (X_Invoice_Date IS NULL)))
752 AND ( (Recinfo.source = X_Source)
753 OR ( (Recinfo.source IS NULL)
754 AND (X_Source IS NULL)))
755 AND ( (Recinfo.invoice_type_lookup_code = X_Invoice_Type_Lookup_Code)
756 OR ( (Recinfo.invoice_type_lookup_code IS NULL)
757 AND (X_Invoice_Type_Lookup_Code IS NULL)))
758 AND ( (Recinfo.description = X_Description)
759 OR ( (Recinfo.description IS NULL)
760 AND (X_Description IS NULL)))
761 AND ( (Recinfo.batch_id = X_Batch_Id)
762 OR ( (Recinfo.batch_id IS NULL)
763 AND (X_Batch_Id IS NULL)))
764 AND ( (Recinfo.amount_applicable_to_discount =
765 X_Amt_Applicable_To_Discount)
766 OR ( (Recinfo.amount_applicable_to_discount IS NULL)
767 AND (X_Amt_Applicable_To_Discount IS NULL)))
768 AND ( (Recinfo.terms_id = X_Terms_Id)
769 OR ( (Recinfo.terms_id IS NULL)
770 AND (X_Terms_Id IS NULL)))
771 AND ( (Recinfo.terms_date = X_Terms_Date)
772 OR ( (Recinfo.terms_date IS NULL)
773 AND (X_Terms_Date IS NULL)))
774 AND ( (Recinfo.goods_received_date = X_Goods_Received_Date)
775 OR ( (Recinfo.goods_received_date IS NULL)
776 AND (X_Goods_Received_Date IS NULL)))
777 AND ( (Recinfo.invoice_received_date = X_Invoice_Received_Date)
778 OR ( (Recinfo.invoice_received_date IS NULL)
779 AND (X_Invoice_Received_Date IS NULL)))
780 AND ( (Recinfo.voucher_num = X_Voucher_Num)
781 OR ( (Recinfo.voucher_num IS NULL)
782 AND (X_Voucher_Num IS NULL)))
783 AND ( (Recinfo.approved_amount = X_Approved_Amount)
784 OR ( (Recinfo.approved_amount IS NULL)
785 AND (X_Approved_Amount IS NULL)))
786 AND ( (Recinfo.approval_status = X_Approval_Status)
787 OR ( (Recinfo.approval_status IS NULL)
788 AND (X_Approval_Status IS NULL)))
789 AND ( (Recinfo.approval_description = X_Approval_Description)
790 OR ( (Recinfo.approval_description IS NULL)
791 AND (X_Approval_Description IS NULL)))
792 AND ( (Recinfo.pay_group_lookup_code = X_Pay_Group_Lookup_Code)
793 OR ( (Recinfo.pay_group_lookup_code IS NULL)
794 AND (X_Pay_Group_Lookup_Code IS NULL)))
795 AND ( (Recinfo.set_of_books_id = X_Set_Of_Books_Id)
796 OR ( (Recinfo.set_of_books_id IS NULL)
797 AND (X_Set_Of_Books_Id IS NULL)))
798 AND ( (Recinfo.accts_pay_code_combination_id = X_Accts_Pay_CCId)
799 OR ( (Recinfo.accts_pay_code_combination_id IS NULL)
800 AND (X_Accts_Pay_CCId IS NULL)))
801 AND ( (Recinfo.recurring_payment_id = X_Recurring_Payment_Id)
802 OR ( (Recinfo.recurring_payment_id IS NULL)
803 AND (X_Recurring_Payment_Id IS NULL)))
804 AND ( (Recinfo.invoice_currency_code = X_Invoice_Currency_Code)
805 OR ( (Recinfo.invoice_currency_code IS NULL)
806 AND (X_Invoice_Currency_Code IS NULL)))
807 AND ( (Recinfo.payment_currency_code = X_Payment_Currency_Code)
808 OR ( (Recinfo.payment_currency_code IS NULL)
809 AND (X_Payment_Currency_Code IS NULL)))
810 AND ( (Recinfo.exchange_rate = X_Exchange_Rate)
814 OR ( (Recinfo.payment_amount_total IS NULL)
811 OR ( (Recinfo.exchange_rate IS NULL)
812 AND (X_Exchange_Rate IS NULL)))
813 AND ( (Recinfo.payment_amount_total = X_Payment_Amount_Total)
815 AND (X_Payment_Amount_Total IS NULL)))
816 AND ( (Recinfo.payment_status_flag = X_Payment_Status_Flag)
817 OR ( (Recinfo.payment_status_flag IS NULL)
818 AND (X_Payment_Status_Flag IS NULL)))
819 AND ( (Recinfo.posting_status = X_Posting_Status)
820 OR ( (Recinfo.posting_status IS NULL)
821 AND (X_Posting_Status IS NULL)))
822 AND ( (Recinfo.posting_flag = X_Posting_Flag)
823 OR ( (Recinfo.posting_flag IS NULL)
824 AND (X_Posting_Flag IS NULL)))
825 AND ( (Recinfo.authorized_by = X_Authorized_By)
826 OR ( (Recinfo.authorized_by IS NULL)
827 AND (X_Authorized_By IS NULL)))
828 AND ( (Recinfo.attribute_category = X_Attribute_Category)
829 OR ( (Recinfo.attribute_category IS NULL)
830 AND (X_Attribute_Category IS NULL)))
831 AND ( (Recinfo.attribute1 = X_Attribute1)
832 OR ( (Recinfo.attribute1 IS NULL)
833 AND (X_Attribute1 IS NULL)))
834 AND ( (Recinfo.attribute2 = X_Attribute2)
835 OR ( (Recinfo.attribute2 IS NULL)
836 AND (X_Attribute2 IS NULL)))
837 AND ( (Recinfo.attribute3 = X_Attribute3)
838 OR ( (Recinfo.attribute3 IS NULL)
839 AND (X_Attribute3 IS NULL)))
840 AND ( (Recinfo.attribute4 = X_Attribute4)
841 OR ( (Recinfo.attribute4 IS NULL)
842 AND (X_Attribute4 IS NULL)))
843 AND ( (Recinfo.attribute5 = X_Attribute5)
844 OR ( (Recinfo.attribute5 IS NULL)
845 AND (X_Attribute5 IS NULL)))
846 AND ( (Recinfo.vendor_prepay_amount = X_Vendor_Prepay_Amount)
847 OR ( (Recinfo.vendor_prepay_amount IS NULL)
848 AND (X_Vendor_Prepay_Amount IS NULL)))
849 -- Third Party Payments
850 AND ( (Recinfo.remit_to_supplier_id = X_remit_to_supplier_Id)
851 OR ( (Recinfo.remit_to_supplier_id IS NULL)
852 AND (X_remit_to_supplier_Id IS NULL)))
853 AND ( (Recinfo.remit_to_supplier_site_id = X_remit_to_supplier_site_Id)
854 OR ( (Recinfo.remit_to_supplier_site_id IS NULL)
855 AND (X_remit_to_supplier_site_Id IS NULL)))
856 AND ( (Recinfo.relationship_id = X_relationship_id)
857 OR ( (Recinfo.relationship_id IS NULL)
858 AND (X_relationship_id IS NULL))));
859
860 second_conditions :=
861 (
862 ( (Recinfo.base_amount = X_Base_Amount)
863 OR ( (Recinfo.base_amount IS NULL)
864 AND (X_Base_Amount IS NULL)))
865 AND ( (Recinfo.exchange_rate_type = X_Exchange_Rate_Type)
866 OR ( (Recinfo.exchange_rate_type IS NULL)
867 AND (X_Exchange_Rate_Type IS NULL)))
868 AND ( (Recinfo.exchange_date = X_Exchange_Date)
869 OR ( (Recinfo.exchange_date IS NULL)
870 AND (X_Exchange_Date IS NULL)))
871 AND ( (Recinfo.payment_cross_rate = X_Payment_Cross_Rate)
872 OR ( (Recinfo.payment_cross_rate IS NULL)
873 AND (X_Payment_Cross_Rate IS NULL)))
874 AND ( (Recinfo.payment_cross_rate_type = X_Payment_Cross_Rate_Type)
875 OR ( (Recinfo.payment_cross_rate_type IS NULL)
876 AND (X_Payment_Cross_Rate_Type IS NULL)))
877 AND ( (Recinfo.payment_cross_rate_date = X_Payment_Cross_Rate_Date)
878 OR ( (Recinfo.payment_cross_rate_date IS NULL)
879 AND (X_Payment_Cross_Rate_Date IS NULL)))
880 AND ( (nvl(Recinfo.pay_curr_invoice_amount,Recinfo.invoice_amount) =
881 X_Pay_Curr_Invoice_Amount)
882 OR ( (Recinfo.pay_curr_invoice_amount IS NULL)
883 AND (X_Pay_Curr_Invoice_Amount IS NULL)))
884 AND ( (Recinfo.earliest_settlement_date = X_Earliest_Settlement_Date)
885 OR ( (Recinfo.earliest_settlement_date IS NULL)
886 AND (X_Earliest_Settlement_Date IS NULL)))
887 AND ( (Recinfo.attribute11 = X_Attribute11)
888 OR ( (Recinfo.attribute11 IS NULL)
889 AND (X_Attribute11 IS NULL)))
890 AND ( (Recinfo.attribute12 = X_Attribute12)
891 OR ( (Recinfo.attribute12 IS NULL)
892 AND (X_Attribute12 IS NULL)))
893 AND ( (Recinfo.attribute13 = X_Attribute13)
894 OR ( (Recinfo.attribute13 IS NULL)
895 AND (X_Attribute13 IS NULL)))
896 AND ( (Recinfo.attribute14 = X_Attribute14)
897 OR ( (Recinfo.attribute14 IS NULL)
898 AND (X_Attribute14 IS NULL)))
899 AND ( (Recinfo.attribute6 = X_Attribute6)
900 OR ( (Recinfo.attribute6 IS NULL)
901 AND (X_Attribute6 IS NULL)))
902 AND ( (Recinfo.attribute7 = X_Attribute7)
903 OR ( (Recinfo.attribute7 IS NULL)
904 AND (X_Attribute7 IS NULL)))
905 AND ( (Recinfo.attribute8 = X_Attribute8)
906 OR ( (Recinfo.attribute8 IS NULL)
907 AND (X_Attribute8 IS NULL)))
908 AND ( (Recinfo.attribute9 = X_Attribute9)
912 OR ( (Recinfo.attribute10 IS NULL)
909 OR ( (Recinfo.attribute9 IS NULL)
910 AND (X_Attribute9 IS NULL)))
911 AND ( (Recinfo.attribute10 = X_Attribute10)
913 AND (X_Attribute10 IS NULL)))
914 AND ( (Recinfo.attribute15 = X_Attribute15)
915 OR ( (Recinfo.attribute15 IS NULL)
916 AND (X_Attribute15 IS NULL)))
917 AND ( (Recinfo.cancelled_date = X_Cancelled_Date)
918 OR ( (Recinfo.cancelled_date IS NULL)
919 AND (X_Cancelled_Date IS NULL)))
920 AND ( (Recinfo.cancelled_by = X_Cancelled_By)
921 OR ( (Recinfo.cancelled_by IS NULL)
922 AND (X_Cancelled_By IS NULL)))
923 AND ( (Recinfo.cancelled_amount = X_Cancelled_Amount)
924 OR ( (Recinfo.cancelled_amount IS NULL)
925 AND (X_Cancelled_Amount IS NULL)))
926 AND ( (Recinfo.temp_cancelled_amount = X_Temp_Cancelled_Amount)
927 OR ( (Recinfo.temp_cancelled_amount IS NULL)
928 AND (X_Temp_Cancelled_Amount IS NULL)))
929 AND ( (Recinfo.exclusive_payment_flag = X_Exclusive_Payment_Flag)
930 OR ( (Recinfo.exclusive_payment_flag IS NULL)
931 AND (X_Exclusive_Payment_Flag IS NULL)))
932 AND ( (Recinfo.po_header_id = X_Po_Header_Id)
933 OR ( (Recinfo.po_header_id IS NULL)
934 AND (X_Po_Header_Id IS NULL)))
935 AND ( (Recinfo.doc_sequence_id = X_Doc_Sequence_Id)
936 OR ( (Recinfo.doc_sequence_id IS NULL)
937 AND (X_Doc_Sequence_Id IS NULL)))
938 AND ( (Recinfo.doc_sequence_value = X_Doc_Sequence_Value)
939 OR ( (Recinfo.doc_sequence_value IS NULL)
940 AND (X_Doc_Sequence_Value IS NULL)))
941 AND ( (Recinfo.doc_category_code = X_Doc_Category_Code)
942 OR ( (Recinfo.doc_category_code IS NULL)
943 AND (X_Doc_Category_Code IS NULL)))
944 AND ( (Recinfo.expenditure_item_date = X_Expenditure_Item_Date)
945 OR ( (Recinfo.expenditure_item_date IS NULL)
946 AND (X_Expenditure_Item_Date IS NULL)))
947 AND ( (Recinfo.expenditure_organization_id =
948 X_Expenditure_Organization_Id)
949 OR ( (Recinfo.expenditure_organization_id IS NULL)
950 AND (X_Expenditure_Organization_Id IS NULL)))
951 AND ( (Recinfo.expenditure_type = X_Expenditure_Type)
952 OR ( (Recinfo.expenditure_type IS NULL)
953 AND (X_Expenditure_Type IS NULL)))
954 AND ( (Recinfo.pa_default_dist_ccid = X_Pa_Default_Dist_Ccid)
955 OR ( (Recinfo.pa_default_dist_ccid IS NULL)
956 AND (X_Pa_Default_Dist_Ccid IS NULL)))
957 AND ( (Recinfo.pa_quantity = X_Pa_Quantity)
958 OR ( (Recinfo.pa_quantity IS NULL)
959 AND (X_Pa_Quantity IS NULL)))
960 AND ( (Recinfo.project_id = X_Project_Id)
961 OR ( (Recinfo.project_id IS NULL)
962 AND (X_Project_Id IS NULL))));
963
964 IF (first_conditions
965 AND second_conditions
966 AND ( (Recinfo.task_id = X_Task_Id)
967 OR ( (Recinfo.task_id IS NULL)
968 AND (X_Task_Id IS NULL)))
969 AND ( (Recinfo.awt_flag = X_Awt_Flag)
970 OR ( (Recinfo.awt_flag IS NULL)
971 AND (X_Awt_Flag IS NULL)))
972 AND ( (Recinfo.awt_group_id = X_Awt_Group_Id)
973 OR ( (Recinfo.awt_group_id IS NULL)
974 AND (X_Awt_Group_Id IS NULL)))
975 AND ( (Recinfo.Pay_awt_group_id = X_Pay_Awt_Group_Id)
976 OR ( (Recinfo.Pay_awt_group_id IS NULL)
977 AND (X_Pay_Awt_Group_Id IS NULL))) --bug6639866
978 AND ( (Recinfo.reference_1 = X_Reference_1)
979 OR ( (Recinfo.reference_1 IS NULL)
980 AND (X_Reference_1 IS NULL)))
981 AND ( (Recinfo.reference_2 = X_Reference_2)
982 OR ( (Recinfo.reference_2 IS NULL)
983 AND (X_Reference_2 IS NULL)))
984 AND ( (Recinfo.global_attribute_category =
985 X_global_attribute_category)
986 OR ( (Recinfo.global_attribute_category IS NULL)
987 AND (X_global_attribute_category IS NULL)))
988 AND ( (Recinfo.global_attribute1 = X_global_attribute1)
989 OR ( (Recinfo.global_attribute1 IS NULL)
990 AND (X_global_attribute1 IS NULL)))
991 AND ( (Recinfo.global_attribute2 = X_global_attribute2)
992 OR ( (Recinfo.global_attribute2 IS NULL)
993 AND (X_global_attribute2 IS NULL)))
994 AND ( (Recinfo.global_attribute3 = X_global_attribute3)
995 OR ( (Recinfo.global_attribute3 IS NULL)
996 AND (X_global_attribute3 IS NULL)))
997 AND ( (Recinfo.global_attribute4 = X_global_attribute4)
998 OR ( (Recinfo.global_attribute4 IS NULL)
999 AND (X_global_attribute4 IS NULL)))
1000 AND ( (Recinfo.global_attribute5 = X_global_attribute5)
1001 OR ( (Recinfo.global_attribute5 IS NULL)
1002 AND (X_global_attribute5 IS NULL)))
1003 AND ( (Recinfo.global_attribute6 = X_global_attribute6)
1004 OR ( (Recinfo.global_attribute6 IS NULL)
1005 AND (X_global_attribute6 IS NULL)))
1009 AND ( (Recinfo.global_attribute8 = X_global_attribute8)
1006 AND ( (Recinfo.global_attribute7 = X_global_attribute7)
1007 OR ( (Recinfo.global_attribute7 IS NULL)
1008 AND (X_global_attribute7 IS NULL)))
1010 OR ( (Recinfo.global_attribute8 IS NULL)
1011 AND (X_global_attribute8 IS NULL)))
1012 AND ( (Recinfo.global_attribute9 = X_global_attribute9)
1013 OR ( (Recinfo.global_attribute9 IS NULL)
1014 AND (X_global_attribute9 IS NULL)))
1015 AND ( (Recinfo.global_attribute10 = X_global_attribute10)
1016 OR ( (Recinfo.global_attribute10 IS NULL)
1017 AND (X_global_attribute10 IS NULL)))
1018 AND ( (Recinfo.global_attribute11 = X_global_attribute11)
1019 OR ( (Recinfo.global_attribute11 IS NULL)
1020 AND (X_global_attribute11 IS NULL)))
1021 AND ( (Recinfo.global_attribute12 = X_global_attribute12)
1022 OR ( (Recinfo.global_attribute12 IS NULL)
1023 AND (X_global_attribute12 IS NULL)))
1024 AND ( (Recinfo.global_attribute13 = X_global_attribute13)
1025 OR ( (Recinfo.global_attribute13 IS NULL)
1026 AND (X_global_attribute13 IS NULL)))
1027 AND ( (Recinfo.global_attribute14 = X_global_attribute14)
1028 OR ( (Recinfo.global_attribute14 IS NULL)
1029 AND (X_global_attribute14 IS NULL)))
1030 AND ( (Recinfo.global_attribute15 = X_global_attribute15)
1031 OR ( (Recinfo.global_attribute15 IS NULL)
1032 AND (X_global_attribute15 IS NULL)))
1033 AND ( (Recinfo.global_attribute16 = X_global_attribute16)
1034 OR ( (Recinfo.global_attribute16 IS NULL)
1035 AND (X_global_attribute16 IS NULL)))
1036 AND ( (Recinfo.global_attribute17 = X_global_attribute17)
1037 OR ( (Recinfo.global_attribute17 IS NULL)
1038 AND (X_global_attribute17 IS NULL)))
1039 AND ( (Recinfo.global_attribute18 = X_global_attribute18)
1040 OR ( (Recinfo.global_attribute18 IS NULL)
1041 AND (X_global_attribute18 IS NULL)))
1042 AND ( (Recinfo.global_attribute19 = X_global_attribute19)
1043 OR ( (Recinfo.global_attribute19 IS NULL)
1044 AND (X_global_attribute19 IS NULL)))
1045 AND ( (Recinfo.global_attribute20 = X_global_attribute20)
1046 OR ( (Recinfo.global_attribute20 IS NULL)
1047 AND (X_global_attribute20 IS NULL)))
1048 AND ( (Recinfo.gl_date = X_gl_date)
1049 OR ( (Recinfo.gl_date IS NULL)
1050 AND (X_gl_date IS NULL)))
1051 AND ( (Recinfo.award_id = X_Award_Id)
1052 OR ( (Recinfo.award_id IS NULL)
1053 AND (X_Award_Id IS NULL)))
1054 AND ( (Recinfo.Approval_iteration = X_Approval_Iteration)
1055 OR ( (Recinfo.approval_iteration IS NULL)
1056 AND (X_approval_iteration IS NULL)))
1057 AND ( (Recinfo.approval_ready_flag = X_approval_ready_flag)
1058 OR ( (Recinfo.approval_ready_flag IS NULL)
1059 AND (X_approval_ready_flag IS NULL)))
1060 AND ( (Recinfo.wfapproval_status = X_wfapproval_status)
1061 OR ( (Recinfo.wfapproval_status IS NULL)
1062 AND (X_wfapproval_status IS NULL)))
1063 AND ( (Recinfo.requester_id = X_requester_id)
1064 OR ( (Recinfo.requester_id IS NULL)
1065 AND (X_requester_id IS NULL)))
1066 -- Invoice Lines Project Stage 1
1067 AND ( (Recinfo.quick_credit = X_quick_credit)
1068 OR ( (Recinfo.quick_credit IS NULL)
1069 AND (X_quick_credit IS NULL)))
1070 AND ( (Recinfo.credited_invoice_id = X_credited_invoice_id)
1071 OR ( (Recinfo.credited_invoice_id IS NULL)
1072 AND (X_credited_invoice_id IS NULL)))
1073 AND ( (Recinfo.distribution_set_id = X_distribution_set_id)
1074 OR ( (Recinfo.distribution_set_id IS NULL)
1075 AND (X_distribution_set_id IS NULL)))
1076 -- Moac project
1077 AND ( (Recinfo.org_id = X_org_id)
1078 OR ( (Recinfo.org_id IS NULL)
1079 AND (X_org_id IS NULL)))
1080 AND ( (Recinfo.disc_is_inv_less_tax_flag = X_disc_is_inv_less_tax_flag)
1081 OR ( (Recinfo.disc_is_inv_less_tax_flag IS NULL)
1082 AND (X_disc_is_inv_less_tax_flag IS NULL)))
1083 AND ( (Recinfo.exclude_freight_from_discount = X_exclude_freight_from_disc)
1084 OR ( (Recinfo.exclude_freight_from_discount IS NULL)
1085 AND (X_exclude_freight_from_disc IS NULL)))
1086
1087 --ETAX: Invwkb
1088 AND ( (Recinfo.force_revalidation_flag = X_force_revalidation_flag)
1089 OR ( (Recinfo.force_revalidation_flag IS NULL)
1090 AND (X_force_revalidation_flag IS NULL)))
1091 AND ( (Recinfo.control_amount = X_control_amount)
1092 OR ( (Recinfo.control_amount IS NULL)
1093 AND (X_control_amount IS NULL)))
1094 AND ( (Recinfo.TAX_RELATED_INVOICE_ID = X_TAX_RELATED_INVOICE_ID)
1095 OR ( (Recinfo.TAX_RELATED_INVOICE_ID IS NULL)
1096 AND (X_TAX_RELATED_INVOICE_ID IS NULL)))
1097 AND ( (Recinfo.TRX_BUSINESS_CATEGORY = X_TRX_BUSINESS_CATEGORY)
1098 OR ( (Recinfo.TRX_BUSINESS_CATEGORY IS NULL)
1099 AND (X_TRX_BUSINESS_CATEGORY IS NULL)))
1103 AND ( (Recinfo.TAXATION_COUNTRY = X_TAXATION_COUNTRY)
1100 AND ( (Recinfo.USER_DEFINED_FISC_CLASS = X_USER_DEFINED_FISC_CLASS)
1101 OR ( (Recinfo.USER_DEFINED_FISC_CLASS IS NULL)
1102 AND (X_USER_DEFINED_FISC_CLASS IS NULL)))
1104 OR ( (Recinfo.TAXATION_COUNTRY IS NULL)
1105 AND (X_TAXATION_COUNTRY IS NULL)))
1106 AND ( (Recinfo.DOCUMENT_SUB_TYPE = X_DOCUMENT_SUB_TYPE)
1107 OR ( (Recinfo.DOCUMENT_SUB_TYPE IS NULL)
1108 AND (X_DOCUMENT_SUB_TYPE IS NULL)))
1109 AND ( (Recinfo.SUPPLIER_TAX_INVOICE_NUMBER = X_SUPPLIER_TAX_INVOICE_NUMBER)
1110 OR ( (Recinfo.SUPPLIER_TAX_INVOICE_NUMBER IS NULL)
1111 AND (X_SUPPLIER_TAX_INVOICE_NUMBER IS NULL)))
1112 AND ( (Recinfo.SUPPLIER_TAX_INVOICE_DATE = X_SUPPLIER_TAX_INVOICE_DATE)
1113 OR ( (Recinfo.SUPPLIER_TAX_INVOICE_DATE IS NULL)
1114 AND (X_SUPPLIER_TAX_INVOICE_DATE IS NULL)))
1115 AND ( (Recinfo.SUPPLIER_TAX_EXCHANGE_RATE = X_SUPPLIER_TAX_EXCHANGE_RATE)
1116 OR ( (Recinfo.SUPPLIER_TAX_EXCHANGE_RATE IS NULL)
1117 AND (X_SUPPLIER_TAX_EXCHANGE_RATE IS NULL)))
1118 AND ( (Recinfo.TAX_INVOICE_RECORDING_DATE = X_TAX_INVOICE_RECORDING_DATE)
1119 OR ( (Recinfo.TAX_INVOICE_RECORDING_DATE IS NULL)
1120 AND (X_TAX_INVOICE_RECORDING_DATE IS NULL)))
1121 AND ( (Recinfo.TAX_INVOICE_INTERNAL_SEQ = X_TAX_INVOICE_INTERNAL_SEQ)
1122 OR ( (Recinfo.TAX_INVOICE_INTERNAL_SEQ IS NULL)
1123 AND (X_TAX_INVOICE_INTERNAL_SEQ IS NULL)))
1124 AND ( (Recinfo.QUICK_PO_HEADER_ID = X_QUICK_PO_HEADER_ID)
1125 OR ( (Recinfo.QUICK_PO_HEADER_ID IS NULL)
1126 AND (X_QUICK_PO_HEADER_ID IS NULL)))
1127 AND ( (Recinfo.PAYMENT_METHOD_CODE = X_PAYMENT_METHOD_CODE)
1128 OR ( (Recinfo.PAYMENT_METHOD_CODE IS NULL)
1129 AND (X_PAYMENT_METHOD_CODE IS NULL)))
1130 AND ( (Recinfo.PAYMENT_REASON_CODE = X_PAYMENT_REASON_CODE)
1131 OR ( (Recinfo.PAYMENT_REASON_CODE IS NULL)
1132 AND (X_PAYMENT_REASON_CODE IS NULL)))
1133 AND ( (Recinfo.PAYMENT_REASON_COMMENTS = X_PAYMENT_REASON_COMMENTS)
1134 OR ( (Recinfo.PAYMENT_REASON_COMMENTS IS NULL)
1135 AND (X_PAYMENT_REASON_COMMENTS IS NULL)))
1136 AND ( (Recinfo.UNIQUE_REMITTANCE_IDENTIFIER = X_UNIQUE_REMITTANCE_IDENTIFIER)
1137 OR ( (Recinfo.UNIQUE_REMITTANCE_IDENTIFIER IS NULL)
1138 AND (X_UNIQUE_REMITTANCE_IDENTIFIER IS NULL)))
1139 AND ( (Recinfo.URI_CHECK_DIGIT = X_URI_CHECK_DIGIT)
1140 OR ( (Recinfo.URI_CHECK_DIGIT IS NULL)
1141 AND (X_URI_CHECK_DIGIT IS NULL)))
1142 AND ( (Recinfo.BANK_CHARGE_BEARER = X_BANK_CHARGE_BEARER)
1143 OR ( (Recinfo.BANK_CHARGE_BEARER IS NULL)
1144 AND (X_BANK_CHARGE_BEARER IS NULL)))
1145 AND ( (Recinfo.DELIVERY_CHANNEL_CODE = X_DELIVERY_CHANNEL_CODE)
1146 OR ( (Recinfo.DELIVERY_CHANNEL_CODE IS NULL)
1147 AND (X_DELIVERY_CHANNEL_CODE IS NULL)))
1148 AND ( (Recinfo.SETTLEMENT_PRIORITY = X_SETTLEMENT_PRIORITY)
1149 OR ( (Recinfo.SETTLEMENT_PRIORITY IS NULL)
1150 AND (X_SETTLEMENT_PRIORITY IS NULL)))
1151 AND ( (Recinfo.NET_OF_RETAINAGE_FLAG = X_NET_OF_RETAINAGE_FLAG)
1152 OR ( (Recinfo.NET_OF_RETAINAGE_FLAG IS NULL)
1153 AND (X_NET_OF_RETAINAGE_FLAG IS NULL)))
1154 AND ( (Recinfo.RELEASE_AMOUNT_NET_OF_TAX = X_RELEASE_AMOUNT_NET_OF_TAX)
1155 OR ( (Recinfo.RELEASE_AMOUNT_NET_OF_TAX IS NULL)
1156 AND (X_RELEASE_AMOUNT_NET_OF_TAX IS NULL)))
1157 AND ( (Recinfo.PORT_OF_ENTRY_CODE = X_PORT_OF_ENTRY_CODE)
1158 OR ( (Recinfo.PORT_OF_ENTRY_CODE IS NULL)
1159 AND (X_PORT_OF_ENTRY_CODE IS NULL)))
1160 AND ( (Recinfo.external_bank_account_id = X_external_bank_account_id)
1161 OR ( (Recinfo.external_bank_account_id IS NULL)
1162 AND (x_external_bank_account_id IS NULL)))
1163 AND ( (Recinfo.party_id = X_party_id)
1164 OR ( (Recinfo.party_id IS NULL)
1165 AND (x_party_id IS NULL)))
1166 AND ( (Recinfo.party_site_id = X_party_site_id)
1167 OR ( (Recinfo.party_site_id IS NULL)
1168 AND (x_party_site_id IS NULL)))
1169 -- Bug 5087834
1170 AND ( (Recinfo.REMITTANCE_MESSAGE1 = x_remit_msg1)
1171 OR ( (Recinfo.REMITTANCE_MESSAGE1 IS NULL)
1172 AND (x_remit_msg1 IS NULL)))
1173 AND ( (Recinfo.REMITTANCE_MESSAGE2 = x_remit_msg2)
1174 OR ( (Recinfo.REMITTANCE_MESSAGE2 IS NULL)
1175 AND (x_remit_msg2 IS NULL)))
1176 AND ( (Recinfo.REMITTANCE_MESSAGE3 = x_remit_msg3)
1177 OR ( (Recinfo.REMITTANCE_MESSAGE3 IS NULL)
1178 AND (x_remit_msg3 IS NULL)))
1179 ) then
1180 RETURN;
1181 ELSE
1182 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1183 APP_EXCEPTION.RAISE_EXCEPTION;
1184 END IF;
1185
1186 EXCEPTION
1190 IF (SQLCODE <> -20001) THEN
1187 WHEN NO_DATA_FOUND THEN
1188 RETURN;
1189 WHEN OTHERS THEN
1191 IF (SQLCODE = -54) THEN
1192 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1193 ELSE
1194 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1195 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1196 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1197 current_calling_sequence);
1198 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1199 'X_Rowid = '||X_Rowid
1200 ||', X_invoice_id = '||X_invoice_id
1201 );
1202 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1203 END IF;
1204 END IF;
1205 APP_EXCEPTION.RAISE_EXCEPTION;
1206
1207 END Lock_Row;
1208
1209 PROCEDURE Lock_Row(
1210 X_invoice_id NUMBER,
1211 X_calling_sequence IN VARCHAR2) IS
1212 CURSOR C IS
1213 SELECT
1214 INVOICE_ID,
1215 LAST_UPDATE_DATE,
1216 LAST_UPDATED_BY,
1217 VENDOR_ID,
1218 INVOICE_NUM,
1219 SET_OF_BOOKS_ID,
1220 INVOICE_CURRENCY_CODE,
1221 PAYMENT_CURRENCY_CODE,
1222 PAYMENT_CROSS_RATE,
1223 INVOICE_AMOUNT,
1224 VENDOR_SITE_ID,
1225 AMOUNT_PAID,
1226 DISCOUNT_AMOUNT_TAKEN,
1227 INVOICE_DATE,
1228 SOURCE,
1229 INVOICE_TYPE_LOOKUP_CODE,
1230 DESCRIPTION,
1231 BATCH_ID,
1232 AMOUNT_APPLICABLE_TO_DISCOUNT,
1233 TERMS_ID,
1234 TERMS_DATE,
1235 PAY_GROUP_LOOKUP_CODE,
1236 ACCTS_PAY_CODE_COMBINATION_ID,
1237 PAYMENT_STATUS_FLAG,
1238 CREATION_DATE,
1239 CREATED_BY,
1240 BASE_AMOUNT,
1241 LAST_UPDATE_LOGIN,
1242 EXCLUSIVE_PAYMENT_FLAG,
1243 PO_HEADER_ID,
1244 GOODS_RECEIVED_DATE,
1245 INVOICE_RECEIVED_DATE,
1246 VOUCHER_NUM,
1247 APPROVED_AMOUNT,
1248 RECURRING_PAYMENT_ID,
1249 EXCHANGE_RATE,
1250 EXCHANGE_RATE_TYPE,
1251 EXCHANGE_DATE,
1252 EARLIEST_SETTLEMENT_DATE,
1253 ORIGINAL_PREPAYMENT_AMOUNT,
1254 DOC_SEQUENCE_ID,
1255 DOC_SEQUENCE_VALUE,
1256 DOC_CATEGORY_CODE,
1257 ATTRIBUTE1,
1258 ATTRIBUTE2,
1259 ATTRIBUTE3,
1260 ATTRIBUTE4,
1261 ATTRIBUTE5,
1262 ATTRIBUTE6,
1263 ATTRIBUTE7,
1264 ATTRIBUTE8,
1265 ATTRIBUTE9,
1266 ATTRIBUTE10,
1267 ATTRIBUTE11,
1268 ATTRIBUTE12,
1269 ATTRIBUTE13,
1270 ATTRIBUTE14,
1271 ATTRIBUTE15,
1272 ATTRIBUTE_CATEGORY,
1273 APPROVAL_STATUS,
1274 APPROVAL_DESCRIPTION,
1275 POSTING_STATUS,
1276 AUTHORIZED_BY,
1277 CANCELLED_DATE,
1278 CANCELLED_BY,
1279 CANCELLED_AMOUNT,
1280 TEMP_CANCELLED_AMOUNT,
1281 PROJECT_ID,
1282 TASK_ID,
1283 EXPENDITURE_TYPE,
1284 EXPENDITURE_ITEM_DATE,
1285 PA_QUANTITY,
1286 EXPENDITURE_ORGANIZATION_ID,
1287 PA_DEFAULT_DIST_CCID,
1288 VENDOR_PREPAY_AMOUNT,
1289 PAYMENT_AMOUNT_TOTAL,
1290 AWT_FLAG,
1291 AWT_GROUP_ID,
1292 PAY_AWT_GROUP_ID, -- bug6639866
1293 REFERENCE_1,
1294 REFERENCE_2,
1295 ORG_ID,
1296 GLOBAL_ATTRIBUTE_CATEGORY,
1297 GLOBAL_ATTRIBUTE1,
1298 GLOBAL_ATTRIBUTE2,
1299 GLOBAL_ATTRIBUTE3,
1300 GLOBAL_ATTRIBUTE4,
1301 GLOBAL_ATTRIBUTE5,
1302 GLOBAL_ATTRIBUTE6,
1303 GLOBAL_ATTRIBUTE7,
1304 GLOBAL_ATTRIBUTE8,
1305 GLOBAL_ATTRIBUTE9,
1306 GLOBAL_ATTRIBUTE10,
1307 GLOBAL_ATTRIBUTE11,
1308 GLOBAL_ATTRIBUTE12,
1309 GLOBAL_ATTRIBUTE13,
1310 GLOBAL_ATTRIBUTE14,
1311 GLOBAL_ATTRIBUTE15,
1312 GLOBAL_ATTRIBUTE16,
1313 GLOBAL_ATTRIBUTE17,
1314 GLOBAL_ATTRIBUTE18,
1315 GLOBAL_ATTRIBUTE19,
1316 GLOBAL_ATTRIBUTE20,
1317 PAYMENT_CROSS_RATE_TYPE,
1318 PAYMENT_CROSS_RATE_DATE,
1319 PAY_CURR_INVOICE_AMOUNT,
1320 MRC_BASE_AMOUNT,
1321 MRC_EXCHANGE_RATE,
1322 MRC_EXCHANGE_RATE_TYPE,
1323 MRC_EXCHANGE_DATE,
1324 GL_DATE,
1325 AWARD_ID,
1326 APPROVAL_ITERATION,
1327 APPROVAL_READY_FLAG,
1328 WFAPPROVAL_STATUS,
1329 REQUESTER_ID, --2289496
1330 -- Invoice Lines Project Stage 1
1331 QUICK_CREDIT,
1332 CREDITED_INVOICE_ID,
1333 DISTRIBUTION_SET_ID,
1334 QUICK_PO_HEADER_ID,
1335 PAYMENT_METHOD_CODE,
1336 PAYMENT_REASON_CODE,
1337 PAYMENT_REASON_COMMENTS,
1338 UNIQUE_REMITTANCE_IDENTIFIER,
1339 URI_CHECK_DIGIT,
1340 BANK_CHARGE_BEARER,
1341 DELIVERY_CHANNEL_CODE,
1342 SETTLEMENT_PRIORITY,
1343 NET_OF_RETAINAGE_FLAG,
1344 RELEASE_AMOUNT_NET_OF_TAX,
1345 PORT_OF_ENTRY_CODE,
1349 disc_is_inv_less_tax_flag,
1346 external_bank_account_id,
1347 party_id,
1348 party_site_id,
1350 exclude_freight_from_discount,
1351 REMITTANCE_MESSAGE1,
1352 REMITTANCE_MESSAGE2,
1353 REMITTANCE_MESSAGE3,
1354 REMIT_TO_SUPPLIER_NAME,
1355 REMIT_TO_SUPPLIER_ID,
1356 REMIT_TO_SUPPLIER_SITE,
1357 REMIT_TO_SUPPLIER_SITE_ID,
1358 RELATIONSHIP_ID,
1359 /* Bug 7831073 */
1360 original_invoice_amount,
1361 dispute_reason
1362 FROM ap_invoices_all
1363 WHERE invoice_id = X_Invoice_id
1364 FOR UPDATE of Invoice_Id NOWAIT;
1365
1366 Recinfo C%ROWTYPE;
1367 current_calling_sequence VARCHAR2(2000);
1368 debug_info VARCHAR2(100);
1369
1370 BEGIN
1371 -- Update the calling sequence
1372
1373 current_calling_sequence := 'AP_INVOICES_PKG.LOCK_ROW(Invoice_id)<-'||
1374 X_calling_sequence;
1375
1376 debug_info := 'Open cursor C';
1377 OPEN C;
1378 debug_info := 'Fetch cursor C';
1379 FETCH C INTO Recinfo;
1380 IF (C%NOTFOUND) THEN
1381 debug_info := 'Close cursor C - ROW NOTFOUND';
1382 CLOSE C;
1383 RAISE NO_DATA_FOUND;
1384 END IF;
1385 debug_info := 'Close cursor C';
1386 CLOSE C;
1387
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 IF (SQLCODE <> -20001) THEN
1391 IF (SQLCODE = -54) THEN
1392 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1393 ELSE
1394 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1395 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1396 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1397 current_calling_sequence);
1398 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1399 'X_invoice_id = '||X_invoice_id
1400 );
1401 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1402 END IF;
1403 END IF;
1404 APP_EXCEPTION.RAISE_EXCEPTION;
1405
1406 END Lock_Row;
1407
1408 PROCEDURE Update_Row(
1409 X_Rowid VARCHAR2,
1410 X_Invoice_Id NUMBER,
1411 X_Last_Update_Date DATE,
1412 X_Last_Updated_By NUMBER,
1413 X_Vendor_Id NUMBER,
1414 X_Invoice_Num VARCHAR2,
1415 X_Invoice_Amount NUMBER,
1416 X_Vendor_Site_Id NUMBER,
1417 X_Amount_Paid NUMBER,
1418 X_Discount_Amount_Taken NUMBER,
1419 X_Invoice_Date DATE,
1420 X_Source VARCHAR2,
1421 X_Invoice_Type_Lookup_Code VARCHAR2,
1422 X_Description VARCHAR2,
1423 X_Batch_Id NUMBER,
1424 X_Amt_Applicable_To_Discount NUMBER,
1425 X_Terms_Id NUMBER,
1426 X_Terms_Date DATE,
1427 X_Goods_Received_Date DATE,
1428 X_Invoice_Received_Date DATE,
1429 X_Voucher_Num VARCHAR2,
1430 X_Approved_Amount NUMBER,
1431 X_Approval_Status VARCHAR2,
1432 X_Approval_Description VARCHAR2,
1433 X_Pay_Group_Lookup_Code VARCHAR2,
1434 X_Set_Of_Books_Id NUMBER,
1435 X_Accts_Pay_CCId NUMBER,
1436 X_Recurring_Payment_Id NUMBER,
1437 X_Invoice_Currency_Code VARCHAR2,
1438 X_Payment_Currency_Code VARCHAR2,
1439 X_Exchange_Rate NUMBER,
1440 X_Payment_Amount_Total NUMBER,
1441 X_Payment_Status_Flag VARCHAR2,
1442 X_Posting_Status VARCHAR2,
1443 X_Authorized_By VARCHAR2,
1444 X_Attribute_Category VARCHAR2,
1445 X_Attribute1 VARCHAR2,
1446 X_Attribute2 VARCHAR2,
1447 X_Attribute3 VARCHAR2,
1448 X_Attribute4 VARCHAR2,
1449 X_Attribute5 VARCHAR2,
1450 X_Vendor_Prepay_Amount NUMBER,
1451 X_Base_Amount NUMBER,
1452 X_Exchange_Rate_Type VARCHAR2,
1453 X_Exchange_Date DATE,
1454 X_Payment_Cross_Rate NUMBER,
1455 X_Payment_Cross_Rate_Type VARCHAR2,
1456 X_Payment_Cross_Rate_Date DATE,
1457 X_Pay_Curr_Invoice_Amount NUMBER,
1458 X_Last_Update_Login NUMBER,
1459 X_Original_Prepayment_Amount NUMBER,
1460 X_Earliest_Settlement_Date DATE,
1461 X_Attribute11 VARCHAR2,
1462 X_Attribute12 VARCHAR2,
1463 X_Attribute13 VARCHAR2,
1464 X_Attribute14 VARCHAR2,
1465 X_Attribute6 VARCHAR2,
1466 X_Attribute7 VARCHAR2,
1467 X_Attribute8 VARCHAR2,
1468 X_Attribute9 VARCHAR2,
1472 X_Cancelled_By NUMBER,
1469 X_Attribute10 VARCHAR2,
1470 X_Attribute15 VARCHAR2,
1471 X_Cancelled_Date DATE,
1473 X_Cancelled_Amount NUMBER,
1474 X_Temp_Cancelled_Amount NUMBER,
1475 X_Exclusive_Payment_Flag VARCHAR2,
1476 X_Po_Header_Id NUMBER,
1477 X_Doc_Sequence_Id NUMBER,
1478 X_Doc_Sequence_Value NUMBER,
1479 X_Doc_Category_Code VARCHAR2,
1480 X_Expenditure_Item_Date DATE,
1481 X_Expenditure_Organization_Id NUMBER,
1482 X_Expenditure_Type VARCHAR2,
1483 X_Pa_Default_Dist_Ccid NUMBER,
1484 X_Pa_Quantity NUMBER,
1485 X_Project_Id NUMBER,
1486 X_Task_Id NUMBER,
1487 X_Awt_Flag VARCHAR2,
1488 X_Awt_Group_Id NUMBER,
1489 X_Pay_Awt_Group_Id NUMBER,--bug6639866
1490 X_Reference_1 VARCHAR2,
1491 X_Reference_2 VARCHAR2,
1492 X_Org_Id NUMBER,
1493 X_global_attribute_category VARCHAR2 DEFAULT NULL,
1494 X_global_attribute1 VARCHAR2 DEFAULT NULL,
1495 X_global_attribute2 VARCHAR2 DEFAULT NULL,
1496 X_global_attribute3 VARCHAR2 DEFAULT NULL,
1497 X_global_attribute4 VARCHAR2 DEFAULT NULL,
1498 X_global_attribute5 VARCHAR2 DEFAULT NULL,
1499 X_global_attribute6 VARCHAR2 DEFAULT NULL,
1500 X_global_attribute7 VARCHAR2 DEFAULT NULL,
1501 X_global_attribute8 VARCHAR2 DEFAULT NULL,
1502 X_global_attribute9 VARCHAR2 DEFAULT NULL,
1503 X_global_attribute10 VARCHAR2 DEFAULT NULL,
1504 X_global_attribute11 VARCHAR2 DEFAULT NULL,
1505 X_global_attribute12 VARCHAR2 DEFAULT NULL,
1506 X_global_attribute13 VARCHAR2 DEFAULT NULL,
1507 X_global_attribute14 VARCHAR2 DEFAULT NULL,
1508 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1509 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1510 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1511 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1512 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1513 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1514 X_calling_sequence IN VARCHAR2,
1515 X_gl_date DATE,
1516 X_award_Id NUMBER,
1517 X_approval_iteration NUMBER,
1518 X_approval_ready_flag VARCHAR2,
1519 X_wfapproval_status VARCHAR2,
1520 X_requester_id NUMBER DEFAULT NULL,
1521 -- Invoice Lines Project Stage 1
1522 X_quick_credit VARCHAR2 DEFAULT NULL,
1523 X_credited_invoice_id NUMBER DEFAULT NULL,
1524 X_distribution_set_id NUMBER DEFAULT NULL,
1525 --ETAX: Invwkb
1526 X_FORCE_REVALIDATION_FLAG VARCHAR2 DEFAULT NULL,
1527 X_CONTROL_AMOUNT NUMBER DEFAULT NULL,
1528 X_TAX_RELATED_INVOICE_ID NUMBER DEFAULT NULL,
1529 X_TRX_BUSINESS_CATEGORY VARCHAR2 DEFAULT NULL,
1530 X_USER_DEFINED_FISC_CLASS VARCHAR2 DEFAULT NULL,
1531 X_TAXATION_COUNTRY VARCHAR2 DEFAULT NULL,
1532 X_DOCUMENT_SUB_TYPE VARCHAR2 DEFAULT NULL,
1533 X_SUPPLIER_TAX_INVOICE_NUMBER VARCHAR2 DEFAULT NULL,
1534 X_SUPPLIER_TAX_INVOICE_DATE DATE DEFAULT NULL,
1535 X_SUPPLIER_TAX_EXCHANGE_RATE NUMBER DEFAULT NULL,
1536 X_TAX_INVOICE_RECORDING_DATE DATE DEFAULT NULL,
1537 X_TAX_INVOICE_INTERNAL_SEQ NUMBER DEFAULT NULL,
1538 X_QUICK_PO_HEADER_ID NUMBER DEFAULT NULL,
1539 x_PAYMENT_METHOD_CODE varchar2 ,
1540 x_PAYMENT_REASON_CODE varchar2 default null,
1541 X_PAYMENT_REASON_COMMENTS varchar2 default null,
1542 x_UNIQUE_REMITTANCE_IDENTIFIER varchar2 default null,
1543 x_URI_CHECK_DIGIT varchar2 default null,
1544 x_BANK_CHARGE_BEARER varchar2 default null,
1545 x_DELIVERY_CHANNEL_CODE varchar2 default null,
1546 x_SETTLEMENT_PRIORITY varchar2 default null,
1547 x_NET_OF_RETAINAGE_FLAG varchar2 default null,
1548 x_RELEASE_AMOUNT_NET_OF_TAX number default null,
1549 x_PORT_OF_ENTRY_CODE varchar2 default null,
1550 x_external_bank_account_id number default null,
1551 x_party_id number default null,
1552 x_party_site_id number default null,
1553 /* bug 4931755. Exclude Tax and Freight from Discount */
1554 x_disc_is_inv_less_tax_flag varchar2 default null,
1555 x_exclude_freight_from_disc varchar2 default null,
1559 /* Third Party Payments*/
1556 x_remit_msg1 varchar2 default null,
1557 x_remit_msg2 varchar2 default null,
1558 x_remit_msg3 varchar2 default null,
1560 x_remit_to_supplier_name varchar2 default null,
1561 x_remit_to_supplier_id number default null,
1562 x_remit_to_supplier_site varchar2 default null,
1563 x_remit_to_supplier_site_id number default null,
1564 x_relationship_id number default null,
1565 /* Bug 7831073 */
1566 x_original_invoice_amount number default null,
1567 x_dispute_reason varchar2 default null
1568 ) IS
1569 current_calling_sequence VARCHAR2(2000);
1570 debug_info VARCHAR2(100);
1571 l_invoice_id NUMBER;
1572 BEGIN
1573
1574 -- Update the calling sequence
1575
1576 current_calling_sequence := 'AP_INVOICES_PKG.UPDATE_ROW<-'||
1577 X_calling_sequence;
1578
1579 debug_info := 'Update ap_invoices';
1580
1581 AP_AI_TABLE_HANDLER_PKG.Update_Row
1582 (X_Rowid,
1583 X_Invoice_Id,
1584 X_Last_Update_Date,
1585 X_Last_Updated_By,
1586 X_Vendor_Id,
1587 X_Invoice_Num,
1588 X_Invoice_Amount,
1589 X_Vendor_Site_Id,
1590 X_Amount_Paid,
1591 X_Discount_Amount_Taken,
1592 X_Invoice_Date,
1593 X_Source,
1594 X_Invoice_Type_Lookup_Code,
1595 X_Description,
1596 X_Batch_Id,
1597 X_Amt_Applicable_To_Discount,
1598 X_Terms_Id,
1599 X_Terms_Date,
1600 X_Goods_Received_Date,
1601 X_Invoice_Received_Date,
1602 X_Voucher_Num,
1603 X_Approved_Amount,
1604 X_Approval_Status,
1605 X_Approval_Description,
1606 X_Pay_Group_Lookup_Code,
1607 X_Set_Of_Books_Id,
1608 X_Accts_Pay_CCId,
1609 X_Recurring_Payment_Id,
1610 X_Invoice_Currency_Code,
1611 X_Payment_Currency_Code,
1612 X_Exchange_Rate,
1613 X_Payment_Amount_Total,
1614 X_Payment_Status_Flag,
1615 X_Posting_Status,
1616 X_Authorized_By,
1617 X_Attribute_Category,
1618 X_Attribute1,
1619 X_Attribute2,
1620 X_Attribute3,
1621 X_Attribute4,
1622 X_Attribute5,
1623 X_Vendor_Prepay_Amount,
1624 X_Base_Amount,
1625 X_Exchange_Rate_Type,
1626 X_Exchange_Date,
1627 X_Payment_Cross_Rate,
1628 X_Payment_Cross_Rate_Type,
1629 X_Payment_Cross_Rate_Date,
1630 X_Pay_Curr_Invoice_Amount,
1631 X_Last_Update_Login,
1632 X_Original_Prepayment_Amount,
1633 X_Earliest_Settlement_Date,
1634 X_Attribute11,
1635 X_Attribute12,
1636 X_Attribute13,
1637 X_Attribute14,
1638 X_Attribute6,
1639 X_Attribute7,
1640 X_Attribute8,
1641 X_Attribute9,
1642 X_Attribute10,
1643 X_Attribute15,
1644 X_Cancelled_Date,
1645 X_Cancelled_By,
1646 X_Cancelled_Amount,
1647 X_Temp_Cancelled_Amount,
1648 X_Exclusive_Payment_Flag,
1649 X_Po_Header_Id,
1650 X_Doc_Sequence_Id,
1651 X_Doc_Sequence_Value,
1652 X_Doc_Category_Code,
1653 X_Expenditure_Item_Date,
1654 X_Expenditure_Organization_Id,
1655 X_Expenditure_Type,
1656 X_Pa_Default_Dist_Ccid,
1657 X_Pa_Quantity,
1658 X_Project_Id,
1659 X_Task_Id,
1660 X_Awt_Flag,
1661 X_Awt_Group_Id,
1662 X_Pay_Awt_Group_Id,--bug6639866
1663 X_Reference_1,
1664 X_Reference_2,
1665 X_Org_id,
1666 X_global_attribute_category,
1667 X_global_attribute1,
1668 X_global_attribute2,
1669 X_global_attribute3,
1670 X_global_attribute4,
1671 X_global_attribute5,
1672 X_global_attribute6,
1673 X_global_attribute7,
1674 X_global_attribute8,
1675 X_global_attribute9,
1676 X_global_attribute10,
1677 X_global_attribute11,
1678 X_global_attribute12,
1679 X_global_attribute13,
1680 X_global_attribute14,
1681 X_global_attribute15,
1682 X_global_attribute16,
1683 X_global_attribute17,
1684 X_global_attribute18,
1685 X_global_attribute19,
1686 X_global_attribute20,
1687 current_calling_sequence,
1688 X_gl_date,
1689 X_Award_Id,
1690 X_approval_iteration,
1691 X_approval_ready_flag,
1692 X_wfapproval_status,
1693 X_requester_id , --2289496
1694 -- Invoice Lines Project Stage 1
1695 X_quick_credit,
1696 X_credited_invoice_id,
1697 X_distribution_set_id,
1698 X_FORCE_REVALIDATION_FLAG,
1699 X_CONTROL_AMOUNT,
1700 X_TAX_RELATED_INVOICE_ID,
1701 X_TRX_BUSINESS_CATEGORY,
1705 X_SUPPLIER_TAX_INVOICE_NUMBER,
1702 X_USER_DEFINED_FISC_CLASS,
1703 X_TAXATION_COUNTRY,
1704 X_DOCUMENT_SUB_TYPE,
1706 X_SUPPLIER_TAX_INVOICE_DATE,
1707 X_SUPPLIER_TAX_EXCHANGE_RATE,
1708 X_TAX_INVOICE_RECORDING_DATE,
1709 X_TAX_INVOICE_INTERNAL_SEQ,
1710 X_QUICK_PO_HEADER_ID,
1711 x_PAYMENT_METHOD_CODE ,
1712 x_PAYMENT_REASON_CODE,
1713 x_PAYMENT_REASON_COMMENTS,
1714 x_UNIQUE_REMITTANCE_IDENTIFIER,
1715 x_URI_CHECK_DIGIT,
1716 x_BANK_CHARGE_BEARER,
1717 x_DELIVERY_CHANNEL_CODE ,
1718 x_SETTLEMENT_PRIORITY,
1719 x_NET_OF_RETAINAGE_FLAG,
1720 x_RELEASE_AMOUNT_NET_OF_TAX,
1721 x_PORT_OF_ENTRY_CODE,
1722 x_external_bank_account_id,
1723 x_party_id,
1724 x_party_site_id,
1725 x_disc_is_inv_less_tax_flag,
1726 x_exclude_freight_from_disc,
1727 x_remit_msg1,
1728 x_remit_msg2,
1729 x_remit_msg3,
1730 x_remit_to_supplier_name,
1731 x_remit_to_supplier_id,
1732 x_remit_to_supplier_site,
1733 x_remit_to_supplier_site_id,
1734 x_relationship_id,
1735 /* Bug 7831073 */
1736 x_original_invoice_amount,
1737 x_dispute_reason
1738 );
1739
1740
1741 EXCEPTION
1742 WHEN OTHERS THEN
1743 IF (SQLCODE <> -20001) THEN
1744 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1745 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1746 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1747 current_calling_sequence);
1748 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1749 'X_Rowid = '||X_Rowid
1750 ||', X_invoice_id = '||X_invoice_id
1751 );
1752 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1753 END IF;
1754 APP_EXCEPTION.RAISE_EXCEPTION;
1755
1756 END Update_Row;
1757
1758 PROCEDURE Delete_Row(
1759 X_Rowid VARCHAR2,
1760 X_calling_sequence IN VARCHAR2)
1761 IS
1762
1763 l_prepayments_applied_flag VARCHAR2(1);
1764 l_encumbered_flag VARCHAR2(1);
1765 l_payments_exist_flag VARCHAR2(1);
1766 l_selected_for_payment_flag VARCHAR2(1);
1767 l_posting_flag VARCHAR2(1);
1768 l_po_number VARCHAR2(30);
1769 l_prepay_amount_applied NUMBER;
1770 l_approval_status VARCHAR2(30); -- Bug 5497262
1771 l_invoice_type VARCHAR2(30);
1772 l_message_name VARCHAR2(30) := '';
1773 l_invoice_id NUMBER;
1774 l_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1775 current_calling_sequence VARCHAR2(2000);
1776 debug_info VARCHAR2(100);
1777
1778 BEGIN
1779
1780 -- Update the calling sequence
1781 --
1782 current_calling_sequence := 'AP_INVOICES_PKG.DELETE_ROW<-'||
1783 X_calling_sequence;
1784
1785 -- Get the invoice_id
1786 debug_info := 'Get the invoice_id';
1787
1788 SELECT invoice_id
1789 INTO l_invoice_id
1790 FROM ap_invoices
1791 WHERE rowid = X_rowid;
1792
1793 -- Verify that the record being deleted meets the requirements
1794 -- for deletion
1795 debug_info := 'Get parameter values to check requirements for deletion';
1796 SELECT
1797 ap_invoices_pkg.get_prepayments_applied_flag(invoice_id),
1798 ap_invoices_pkg.get_encumbered_flag(invoice_id),
1799 ap_invoices_pkg.get_payments_exist_flag(invoice_id),
1800 ap_invoices_pkg.selected_for_payment_flag(invoice_id),
1801 ap_invoices_pkg.get_posting_status(invoice_id),
1802 ap_invoices_pkg.get_po_number(invoice_id),
1803 ap_invoices_pkg.get_prepay_amount_applied(invoice_id),
1804 ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount,
1805 payment_status_flag, invoice_type_lookup_code), -- Bug 5497262
1806 invoice_type_lookup_code
1807 INTO
1808 l_prepayments_applied_flag,
1809 l_encumbered_flag,
1810 l_payments_exist_flag,
1811 l_selected_for_payment_flag,
1812 l_posting_flag,
1813 l_po_number,
1814 l_prepay_amount_applied,
1815 l_approval_status,
1816 l_invoice_type
1817 FROM ap_invoices
1818 WHERE rowid = X_Rowid;
1819
1820 IF (l_prepayments_applied_flag = 'Y') THEN
1821 l_message_name := 'AP_INV_DEL_INV_PREPAYS';
1822 ELSIF (l_encumbered_flag <> 'N') THEN
1823 l_message_name := 'AP_INV_NO_UPDATE_APPROVED_INV';
1824 ELSIF (l_payments_exist_flag = 'Y') THEN
1825 l_message_name := 'AP_INV_NO_UPDATE_PAID_INV';
1826 ELSIF (l_selected_for_payment_flag = 'Y') THEN
1827 l_message_name := 'AP_INV_SELECTED_INVOICE';
1828 ELSIF (l_posting_flag <> 'N' or
1829 l_po_number <> 'UNMATCHED') THEN
1830 l_message_name := 'AP_INV_MATCHED_POSTED_INVOICE';
1831 ELSIF (l_prepay_amount_applied <> 0) THEN
1832 l_message_name := 'AP_INV_DEL_APPLIED_PREPAY';
1833 ELSIF (l_approval_status IN ('APPROVED', 'UNPAID')) THEN
1834 l_message_name := 'AP_INV_DEL_PAY_REQUEST'; -- Bug 5497262
1835 ELSIF (l_invoice_type = 'RETAINAGE RELEASE') THEN
1836 l_message_name := 'AP_INV_DEL_RET_RELEASE';
1837 END IF;
1838
1839 IF (l_message_name is not null) THEN
1840 FND_MESSAGE.Set_Name('SQLAP', l_message_name);
1841 APP_EXCEPTION.RAISE_EXCEPTION;
1842 END IF;
1843
1844 AP_AI_TABLE_HANDLER_PKG.Delete_Row(
1848 EXCEPTION
1845 X_Rowid,
1846 current_calling_sequence);
1847
1849 WHEN OTHERS THEN
1850 IF (SQLCODE <> -20001) THEN
1851 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1852 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1853 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1854 current_calling_sequence);
1855 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1856 'X_Rowid = '||X_Rowid
1857 );
1858 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1859 END IF;
1860 APP_EXCEPTION.RAISE_EXCEPTION;
1861
1862 END Delete_Row;
1863
1864
1865 ----------------------------------------------------------------------
1866 -- FUNCTION get_max_line_number RETURNs the max line NUMBER given
1867 -- an invoice id. It RETURNs 0 IF no lines found.
1868 ----------------------------------------------------------------------
1869
1870 FUNCTION Get_Max_Line_Number (X_invoice_id IN NUMBER) RETURN NUMBER
1871 IS
1872 l_max_line_number NUMBER := 0;
1873 BEGIN
1874
1875 SELECT nvl(max(line_number),0)
1876 INTO l_max_line_number
1877 FROM ap_invoice_lines
1878 WHERE invoice_id = X_invoice_id;
1879
1880 RETURN(l_max_line_number);
1881 END get_max_line_number;
1882
1883 -----------------------------------------------------------------------
1884 -- FUNCTION get_expenditure_item_date RETURNs the expenditure item date
1885 -- to be used given PA's profile option: 'PA: Default Expenditure Item
1886 -- Date for AP Invoices'
1887 -----------------------------------------------------------------------
1888
1889 FUNCTION Get_Expenditure_Item_Date(
1890 X_invoice_id IN NUMBER,
1891 X_invoice_date IN DATE,
1892 X_GL_date IN DATE,
1893 X_po_dist_id IN NUMBER DEFAULT NULL,
1894 X_rcv_trx_id IN NUMBER DEFAULT NULL,
1895 X_error_found OUT NOCOPY VARCHAR2) RETURN DATE
1896 IS
1897
1898 l_expenditure_item_date DATE := NULL;
1899 l_expenditure_item_Date_prfl VARCHAR2(240);
1900 l_po_date DATE := NULL;
1901 l_rcv_date DATE := NULL;
1902 BEGIN
1903
1904
1905 /* IF (X_po_dist_id is not null) THEN
1906 BEGIN
1907 SELECT decode(destination_type_code, 'EXPENSE',
1908 expenditure_item_date,
1909 NULL)
1910 INTO l_po_date
1911 FROM po_distributions
1912 WHERE po_distribution_id = X_po_dist_id;
1913
1914 EXCEPTION
1915 WHEN NO_DATA_FOUND THEN
1916 X_error_found := 'Y';
1917 RETURN(NULL);
1918 END;
1919
1920 ELSIF (X_rcv_trx_id is not null) THEN
1921 BEGIN
1922 SELECT transaction_date
1923 INTO l_rcv_date
1924 FROM rcv_transactions
1925 WHERE transaction_id = X_rcv_trx_id;
1926
1927 EXCEPTION
1928 WHEN NO_DATA_FOUND THEN
1929 X_error_found := 'Y';
1930 RETURN(NULL);
1931 END;
1932
1933 END IF;
1934
1935
1936 l_expenditure_item_Date_prfl :=
1937 NVL(FND_PROFILE.VALUE('PA_AP_EI_DATE_DEFAULT'), 'Transaction Date');
1938
1939 --bugfix:4505249
1940 IF l_expenditure_item_Date_prfl = 'PO Expenditure Item Date/Transaction Date'
1941 THEN
1942 IF (l_po_date is not null) THEN
1943 l_expenditure_item_date := l_po_date;
1944 ELSE
1945 l_expenditure_item_Date := X_invoice_date;
1946 END IF;
1947 ELSIF l_expenditure_item_Date_prfl ='PO Expenditure Item Date/Transaction GL Date'
1948 THEN
1949 IF (l_po_date is not null) THEN
1950 l_expenditure_item_date := l_po_date;
1951 ELSE
1952 l_expenditure_item_date := X_GL_Date;
1953 END IF;
1954 ELSIF l_expenditure_item_Date_prfl = 'PO Expenditure Item Date/Transaction System Date'
1955 THEN
1956 IF (l_po_date is not null) THEN
1957 l_expenditure_item_date := l_po_date;
1958 ELSE
1959 l_expenditure_item_date := TRUNC(sysdate);
1960 END IF;
1961 ELSIF l_expenditure_item_Date_prfl = 'Receipt Date/Transaction Date' THEN
1962 IF (l_rcv_date is not null) THEN
1963 l_expenditure_item_date := l_rcv_date;
1964 ELSE
1965 l_expenditure_item_date := X_invoice_date;
1966 END IF;
1967 ELSIF l_expenditure_item_Date_prfl = 'Receipt Date/Transaction GL Date' THEN
1968 IF (l_rcv_date is not null) THEN
1969 l_expenditure_item_date := l_rcv_date;
1970 ELSE
1971 l_expenditure_item_date := X_GL_Date;
1972 END IF;
1973 ELSIF l_expenditure_item_Date_prfl = 'Receipt Date/Transaction System Date' THEN
1974 IF (l_rcv_date is not null) THEN
1975 l_expenditure_item_date := l_rcv_date;
1976 ELSE
1977 l_expenditure_item_date := TRUNC(SYSDATE);
1978 END IF;
1979 ELSIF l_expenditure_item_Date_prfl = 'Transaction Date' THEN
1980 l_expenditure_item_date := X_invoice_date;
1981 ELSIF l_expenditure_item_Date_prfl = 'Transaction GL Date' THEN
1982 l_expenditure_item_date := X_GL_date;
1983 ELSIF l_expenditure_item_Date_prfl = 'Transaction System Date' THEN
1984 l_expenditure_item_date := TRUNC(SYSDATE);
1985 ELSE
1986 l_expenditure_item_date := null;
1987 END IF;
1988 */
1989
1993 p_transaction_date => x_invoice_date,
1990 -- Bug 5294998. Expenditure_Item_Dtae will retreived based on PA API
1991 l_expenditure_item_date :=
1992 PA_AP_INTEGRATION.Get_Si_Cost_Exp_Item_Date (
1994 p_gl_date => x_gl_date,
1995 p_creation_date => sysdate,
1996 p_po_exp_item_date => NULL,
1997 p_po_distribution_id => x_po_dist_id,
1998 p_calling_program => 'PO-MATCH');
1999
2000
2001 X_error_found := 'N';
2002 RETURN(l_expenditure_item_date);
2003
2004 END get_expenditure_item_date;
2005
2006 --=========================================================================
2007 -- The following functions have been mapped to AP_INVOICES_UTILITY_PKG
2008 -- (apinvuts.pls apinvutb.pls)
2009 --
2010 --=========================================================================
2011 FUNCTION get_distribution_total(l_invoice_id IN NUMBER)
2012 RETURN NUMBER
2013 IS
2014 l_distribution_total NUMBER := 0;
2015
2016 BEGIN
2017 l_distribution_total := AP_INVOICES_UTILITY_PKG.get_distribution_total(
2018 l_invoice_id);
2019
2020 RETURN(l_distribution_total);
2021
2022 END get_distribution_total;
2023
2024
2025
2026 FUNCTION get_posting_status(l_invoice_id IN NUMBER)
2027 RETURN VARCHAR2
2028 IS
2029 l_invoice_posting_flag VARCHAR2(1);
2030
2031 BEGIN
2032 l_invoice_posting_flag := AP_INVOICES_UTILITY_PKG.get_posting_status(
2033 l_invoice_id);
2034
2035 RETURN(l_invoice_posting_flag);
2036
2037 END get_posting_status;
2038
2039 PROCEDURE CHECK_UNIQUE (
2040 X_ROWID VARCHAR2,
2041 X_INVOICE_NUM VARCHAR2,
2042 X_VENDOR_ID NUMBER,
2043 X_ORG_ID NUMBER, /* Bug 5407785 */
2044 X_calling_sequence IN VARCHAR2) IS
2045 BEGIN
2046
2047 AP_INVOICES_UTILITY_PKG.CHECK_UNIQUE (
2048 X_ROWID,
2049 X_INVOICE_NUM,
2050 X_VENDOR_ID,
2051 X_ORG_ID, /* Bug 5407785 */
2052 X_calling_sequence);
2053
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 APP_EXCEPTION.RAISE_EXCEPTION;
2057
2058 END CHECK_UNIQUE;
2059
2060
2061 PROCEDURE CHECK_UNIQUE_VOUCHER_NUM (
2062 X_ROWID VARCHAR2,
2063 X_VOUCHER_NUM VARCHAR2,
2064 X_calling_sequence IN VARCHAR2) IS
2065 BEGIN
2066
2067 AP_INVOICES_UTILITY_PKG.CHECK_UNIQUE_VOUCHER_NUM (
2068 X_ROWID,
2069 X_VOUCHER_NUM,
2070 X_calling_sequence);
2071
2072 EXCEPTION
2073 WHEN OTHERS THEN
2074 APP_EXCEPTION.RAISE_EXCEPTION;
2075
2076 END CHECK_UNIQUE_VOUCHER_NUM;
2077
2078 FUNCTION get_approval_status(
2079 l_invoice_id IN NUMBER,
2080 l_invoice_amount IN NUMBER,
2081 l_payment_status_flag IN VARCHAR2,
2082 l_invoice_type_lookup_code IN VARCHAR2)
2083 RETURN VARCHAR2
2084 IS
2085 l_invoice_approval_status VARCHAR2(25);
2086 BEGIN
2087
2088 l_invoice_approval_status := AP_INVOICES_UTILITY_PKG.get_approval_status(
2089 l_invoice_id,
2090 l_invoice_amount,
2091 l_payment_status_flag,
2092 l_invoice_type_lookup_code);
2093
2094 RETURN(l_invoice_approval_status);
2095
2096 END get_approval_status;
2097
2098 FUNCTION get_po_number(l_invoice_id IN NUMBER)
2099 RETURN VARCHAR2
2100 IS
2101 l_po_number VARCHAR2(25);
2102 BEGIN
2103 l_po_number := AP_INVOICES_UTILITY_PKG.get_po_number(l_invoice_id);
2104
2105 RETURN(l_po_number);
2106
2107 END get_po_number;
2108
2109 FUNCTION get_release_number(l_invoice_id IN NUMBER)
2110 RETURN VARCHAR2
2111 IS
2112 l_release_number VARCHAR2(25);
2113 BEGIN
2114 l_release_number := AP_INVOICES_UTILITY_PKG.get_release_number(l_invoice_id);
2115
2116 RETURN(l_release_number);
2117
2118 END get_release_number;
2119
2120
2121 FUNCTION get_receipt_number(l_invoice_id IN NUMBER)
2122 RETURN VARCHAR2
2123 IS
2124 l_receipt_number VARCHAR2(25);
2125 BEGIN
2126 l_receipt_number := AP_INVOICES_UTILITY_PKG.get_receipt_number(l_invoice_id);
2127
2128 RETURN(l_receipt_number);
2129
2130 END get_receipt_number;
2131
2132 FUNCTION get_po_number_list(l_invoice_id IN NUMBER)
2133 RETURN VARCHAR2
2134 IS
2135 l_po_number_list VARCHAR2(2000) := NULL;
2136 BEGIN
2137
2138 l_po_number_list := AP_INVOICES_UTILITY_PKG.get_po_number_list(l_invoice_id);
2139
2140 RETURN(l_po_number_list);
2141
2142 END get_po_number_list;
2143
2144 FUNCTION get_amount_withheld(l_invoice_id IN NUMBER)
2145 RETURN NUMBER
2146 IS
2147 l_amount_withheld NUMBER := 0;
2148 BEGIN
2149 l_amount_withheld := AP_INVOICES_UTILITY_PKG.get_amount_withheld(
2150 l_invoice_id);
2151
2152 RETURN(l_amount_withheld);
2153
2154 END get_amount_withheld;
2155
2156 FUNCTION get_prepaid_amount(l_invoice_id IN NUMBER)
2157 RETURN NUMBER
2158 IS
2159 l_prepaid_amount NUMBER := 0;
2160 BEGIN
2164
2161 l_prepaid_amount := AP_INVOICES_UTILITY_PKG.get_prepaid_amount(l_invoice_id);
2162
2163 RETURN(l_prepaid_amount);
2165 END get_prepaid_amount;
2166
2167 FUNCTION get_notes_count(l_invoice_id IN NUMBER)
2168 RETURN NUMBER
2169 IS
2170 l_notes_count NUMBER := 0;
2171 BEGIN
2172 l_notes_count := AP_INVOICES_UTILITY_PKG.get_notes_count(l_invoice_id);
2173 RETURN(l_notes_count);
2174
2175 END get_notes_count;
2176
2177 FUNCTION get_holds_count(l_invoice_id IN NUMBER)
2178 RETURN NUMBER
2179 IS
2180 l_holds_count NUMBER := 0;
2181 BEGIN
2182 l_holds_count := AP_INVOICES_UTILITY_PKG.get_holds_count(l_invoice_id);
2183
2184 RETURN(l_holds_count);
2185
2186 END get_holds_count;
2187
2188 --bug 5334577
2189 FUNCTION get_sched_holds_count(l_invoice_id IN NUMBER)
2190 RETURN NUMBER
2191 IS
2192 l_holds_count NUMBER := 0;
2193 BEGIN
2194 l_holds_count := AP_INVOICES_UTILITY_PKG.get_sched_holds_count(l_invoice_id);
2195
2196 RETURN(l_holds_count);
2197
2198 END get_sched_holds_count;
2199
2200
2201 FUNCTION get_total_prepays(
2202 l_vendor_id IN NUMBER,
2203 l_org_id IN NUMBER)
2204 RETURN NUMBER
2205 IS
2206 l_prepay_count NUMBER := 0;
2207 BEGIN
2208 -- MOAC. Added org_id parameter to FUNCTION and to call
2209 l_prepay_count := AP_INVOICES_UTILITY_PKG.get_total_prepays(
2210 l_vendor_id,
2211 l_org_id);
2212 RETURN(l_prepay_count);
2213
2214 END get_total_prepays;
2215
2216 FUNCTION get_available_prepays(
2217 l_vendor_id IN NUMBER,
2218 l_org_id IN NUMBER)
2219 RETURN NUMBER
2220 IS
2221 l_prepay_count NUMBER := 0;
2222 BEGIN
2223 -- MOAC. Added org_id parameter to FUNCTION and to call
2224 l_prepay_count := AP_INVOICES_UTILITY_PKG.get_available_prepays(
2225 l_vendor_id,
2226 l_org_id);
2227
2228 RETURN(l_prepay_count);
2229
2230 END get_available_prepays;
2231
2232 FUNCTION get_encumbered_flag(l_invoice_id IN NUMBER) RETURN VARCHAR2
2233 IS
2234 l_encumbered_flag VARCHAR2(1);
2235
2236 BEGIN
2237 l_encumbered_flag := AP_INVOICES_UTILITY_PKG.get_encumbered_flag(
2238 l_invoice_id);
2239
2240 RETURN(l_encumbered_flag);
2241
2242 END get_encumbered_flag;
2243
2244 FUNCTION get_amount_hold_flag(l_invoice_id IN NUMBER)
2245 RETURN VARCHAR2
2246 IS
2247 l_amount_hold_flag VARCHAR2(1) := 'N';
2248 BEGIN
2249 l_amount_hold_flag := AP_INVOICES_UTILITY_PKG.get_amount_hold_flag(
2250 l_invoice_id);
2251
2252 RETURN(L_amount_hold_flag);
2253
2254 END get_amount_hold_flag;
2255
2256 FUNCTION get_vendor_hold_flag(l_invoice_id IN NUMBER)
2257 RETURN VARCHAR2
2258 IS
2259 l_vendor_hold_flag VARCHAR2(1) := 'N';
2260 BEGIN
2261 l_vendor_hold_flag := AP_INVOICES_UTILITY_PKG.get_vendor_hold_flag(
2262 l_invoice_id);
2263
2264 RETURN(l_vendor_hold_flag);
2265
2266 END get_vendor_hold_flag;
2267
2268 -- --------------------------------------------------------------------------
2269 -- Procedure get_gl_date_and_period() can be used to determine the
2270 -- open period given a date. This PROCEDURE will also allow you to
2271 -- compare with a parent GL date, such as that of ap_batches for
2272 -- ap_invoices. You needn't pass a parent date, however, to determine
2273 -- the next open period. The GL date and period name are written to
2274 -- IN OUT NOCOPY parameters, P_GL_Date and P_Period_Name, passed to the
2275 -- procedure. If there is no open period, the PROCEDURE RETURNs
2276 -- null in the IN OUT NOCOPY parameters.
2277 -- Instead of Calling the AP_INVOICES_UTILITY_PKG.get_gl_date_and_period,
2278 -- the code has been SHIFTED into this PROCEDURE as a part of clean-up and
2279 -- PROCEDURE get_gl_date_and_period is REMOVED from AP_INVOICE_UTILITY_PKG.
2280 -- -------------------------------------------------------------------------
2281
2282 PROCEDURE Get_gl_date_and_period (
2283 P_Date IN DATE,
2284 P_Receipt_Date IN DATE DEFAULT NULL,
2285 P_Period_Name OUT NOCOPY VARCHAR2,
2286 P_GL_Date OUT NOCOPY DATE,
2287 P_Batch_GL_Date IN DATE DEFAULT NULL,
2288 P_Org_Id IN NUMBER DEFAULT
2289 MO_GLOBAL.GET_CURRENT_ORG_ID)
2290 IS
2291 l_period_name gl_period_statuses.period_name%TYPE := '';
2292 l_current_date date := '';
2293 l_gl_date date := '';
2294 y_date date := '';
2295 n_date date := '';
2296
2297 BEGIN
2298
2299 -- Determine which date we should be using
2300
2301 -- First set up temporary variables y_date and n_date
2302
2303 IF (P_Batch_GL_Date is null) THEN
2304 IF (P_Receipt_Date is null) THEN
2305 y_date := TRUNC(SYSDATE);
2306 n_date := TRUNC(P_date);
2307 ELSE
2308 y_date := TRUNC(P_Receipt_Date);
2309 n_date := TRUNC(P_Receipt_Date);
2310 END IF;
2311 END IF;
2312
2313 -- MOAC. Added org_id parameter and predicate to select statement
2314 SELECT NVL(P_Batch_GL_Date,
2315 DECODE(SP.gl_date_from_receipt_flag,
2319 TRUNC(P_Date)))
2316 'S',TRUNC(SYSDATE),
2317 'Y',y_date,
2318 'N',n_date,
2320 INTO l_current_date
2321 FROM ap_system_parameters_all SP --5126689
2322 WHERE sp.org_id = p_org_id;
2323
2324 -- Initialize the IN OUT NOCOPY variables
2325 P_GL_Date := '';
2326 P_Period_Name := '';
2327
2328 -- See IF the period corresponding to P_Date is open
2329 -- Added org_id parameter to this call MOAC
2330 l_period_name := ap_utilities_pkg.get_current_gl_date(
2331 l_current_date, P_Org_Id);
2332
2333 IF (l_period_name is null) THEN
2334
2335 -- The date is in a closed period, roll forward until we find one
2336 -- MOAC. Added org_id parameter to call
2337 ap_utilities_pkg.get_open_gl_date
2338 (l_current_date,
2339 l_period_name,
2340 l_gl_date,
2341 P_Org_Id);
2342 ELSE
2343 -- No need to call the function. The GL date will be the
2344 -- date passed to the function
2345 l_gl_date := l_current_date;
2346 END IF;
2347
2348 P_Period_Name := l_Period_Name;
2349 P_GL_Date := l_GL_Date;
2350
2351 END Get_gl_date_and_period;
2352
2353 -- =====================================================================
2354 --
2355 -- Bug 803299 Added an extra parameter l_receipt_date in get_gl_date and
2356 -- get_period_name function. Please refer to bug 991787 for the fix of
2357 -- this bug.
2358 -- =====================================================================
2359
2360 FUNCTION get_gl_date(
2361 l_invoice_date IN DATE,
2362 l_receipt_date IN DATE DEFAULT NULL)
2363 RETURN DATE
2364 IS
2365 l_GL_Date date := '';
2366 l_period_name gl_period_statuses.period_name%TYPE := ''; -- Not used
2367 BEGIN
2368
2369 -- Call get_gl_date_and_period from ap_invoice_pkg instead of
2370 -- ap_invoices_utiliy_pkg; Done as a part of clean-up act
2371
2372 ap_invoices_pkg.get_gl_date_and_period( -- get_gl_date
2373 l_invoice_date,
2374 l_receipt_date,
2375 l_period_name,
2376 l_GL_Date);
2377
2378 RETURN(l_GL_Date);
2379 END get_gl_date;
2380
2381 FUNCTION get_period_name(
2382 l_invoice_date IN DATE,
2383 l_receipt_date IN DATE DEFAULT NULL,
2384 l_org_id IN NUMBER DEFAULT
2385 MO_GLOBAL.GET_CURRENT_ORG_ID)
2386 RETURN VARCHAR2
2387 IS
2388 l_GL_Date date := '';
2389 l_period_name gl_period_statuses.period_name%TYPE := '';
2390 BEGIN
2391
2392 -- Call get_gl_date_and_period from ap_invoice_pkg instead of
2393 -- ap_invoices_utiliy_pkg; Done as a part of clean-up act
2394 -- MOAC. Added org_id parameter to FUNCTION and call
2395
2396 ap_invoices_pkg.get_gl_date_and_period(
2397 P_date => l_invoice_date,
2398 P_Receipt_Date => l_receipt_date,
2399 P_Period_Name => l_period_name,
2400 P_GL_Date => l_GL_Date,
2401 P_Org_Id => l_org_id);
2402
2403 RETURN(l_period_name);
2404 END get_period_name;
2405
2406 FUNCTION get_similar_drcr_memo(
2407 P_vendor_id IN NUMBER,
2408 P_vendor_site_id IN NUMBER,
2409 P_invoice_amount IN NUMBER,
2410 P_invoice_type_lookup_code IN VARCHAR2,
2411 P_invoice_currency_code IN VARCHAR2,
2412 P_calling_sequence IN VARCHAR2) RETURN VARCHAR2
2413 IS
2414 l_invoice_num ap_invoices.invoice_num%TYPE;
2415
2416 BEGIN
2417 l_invoice_num := AP_INVOICES_UTILITY_PKG.get_similar_drcr_memo(
2418 P_vendor_id,
2419 P_vendor_site_id,
2420 P_invoice_amount,
2421 P_invoice_type_lookup_code,
2422 P_invoice_currency_code,
2423 P_calling_sequence);
2424 RETURN(l_invoice_num);
2425
2426 END get_similar_drcr_memo;
2427
2428 FUNCTION eft_bank_details_exist (
2429 P_vendor_site_id IN NUMBER,
2430 P_calling_sequence IN VARCHAR2) RETURN BOOLEAN
2431 IS
2432 l_flag boolean;
2433 BEGIN
2434 l_flag := AP_INVOICES_UTILITY_PKG.eft_bank_details_exist (
2435 P_vendor_site_id,
2436 P_calling_sequence);
2437
2438 RETURN(l_flag);
2439
2440 END eft_bank_details_exist;
2441
2442 FUNCTION eft_bank_curr_details_exist (P_vendor_site_id IN NUMBER,
2443 P_currency_code IN VARCHAR2,
2444 P_calling_sequence IN VARCHAR2) RETURN boolean
2445 IS
2446 l_flag boolean;
2447 BEGIN
2448 l_flag := AP_INVOICES_UTILITY_PKG.eft_bank_curr_details_exist (
2449 P_vendor_site_id,
2450 P_currency_code,
2451 P_calling_sequence);
2452
2453 RETURN(l_flag);
2454
2455 END eft_bank_curr_details_exist;
2456
2457 FUNCTION selected_for_payment_flag (
2458 P_invoice_id IN NUMBER) RETURN VARCHAR2
2459 IS
2460 l_flag VARCHAR2(1) := 'N';
2461 BEGIN
2462 l_flag := AP_INVOICES_UTILITY_PKG.selected_for_payment_flag (P_invoice_id);
2463
2464 RETURN(l_flag);
2465
2466 END selected_for_payment_flag;
2467
2471 l_flag VARCHAR2(1) := 'N';
2468 FUNCTION get_discount_pay_dists_flag (P_invoice_id IN NUMBER)
2469 RETURN VARCHAR2
2470 IS
2472 BEGIN
2473 l_flag := AP_INVOICES_UTILITY_PKG.get_discount_pay_dists_flag (P_invoice_id);
2474
2475 RETURN(l_flag);
2476
2477 END get_discount_pay_dists_flag;
2478
2479
2480 FUNCTION get_unposted_void_payment (
2481 P_invoice_id IN NUMBER)
2482 RETURN VARCHAR2
2483 IS
2484 l_flag VARCHAR2(1) := 'N';
2485 BEGIN
2486 l_flag := AP_INVOICES_UTILITY_PKG.get_unposted_void_payment (P_invoice_id);
2487
2488 RETURN(l_flag);
2489
2490 END get_unposted_void_payment;
2491
2492 FUNCTION get_prepayments_applied_flag (
2493 P_invoice_id IN NUMBER)
2494 RETURN VARCHAR2
2495 IS
2496 l_flag VARCHAR2(1) := 'N';
2497 BEGIN
2498 l_flag := AP_INVOICES_UTILITY_PKG.get_prepayments_applied_flag (P_invoice_id);
2499 RETURN(l_flag);
2500
2501 END get_prepayments_applied_flag;
2502
2503 FUNCTION get_payments_exist_flag (
2504 P_invoice_id IN NUMBER)
2505 RETURN VARCHAR2
2506 IS
2507 l_flag VARCHAR2(1) := 'N';
2508 BEGIN
2509 l_flag := AP_INVOICES_UTILITY_PKG.get_payments_exist_flag (P_invoice_id);
2510
2511 RETURN(l_flag);
2512
2513 END get_payments_exist_flag;
2514
2515 FUNCTION get_prepay_amount_applied (P_invoice_id IN NUMBER)
2516 RETURN NUMBER
2517 IS
2518 l_prepay_amount NUMBER := 0;
2519
2520 BEGIN
2521 l_prepay_amount := AP_INVOICES_UTILITY_PKG.get_prepay_amount_applied (
2522 P_invoice_id);
2523
2524 RETURN(l_prepay_amount);
2525
2526 END get_prepay_amount_applied;
2527
2528 FUNCTION get_packet_id (P_invoice_id IN NUMBER)
2529 RETURN NUMBER
2530 IS
2531 l_packet_id NUMBER := '';
2532 BEGIN
2533 l_packet_id := AP_INVOICES_UTILITY_PKG.get_packet_id (P_invoice_id);
2534
2535 RETURN(l_packet_id);
2536
2537 END get_packet_id;
2538
2539 --=========================================================================
2540 -- The functions above have been mapped to AP_INVOICES_UTILITY_PKG
2541 -- (apinvuts.pls apinvutb.pls)
2542 --=========================================================================
2543
2544 --=========================================================================
2545 -- The Following functions have been mapped to AP_INVOICES_POST_PROCESS_PKG
2546 -- (apinvpps.pls apinvppb.pls)
2547 --
2548 --=========================================================================
2549
2550 -----------------------------------------------------------------------
2551 -- Procedure insert_children
2552 -- Inserts child records into AP_HOLDS, AP_PAYMENT_SCHEDULES
2553 -- and AP_INVOICE_LINES
2554 -- PRECONDITION: Called from POST_INSERT
2555 -----------------------------------------------------------------------
2556
2557 PROCEDURE insert_children (
2558 X_invoice_id IN NUMBER,
2559 X_Payment_Priority IN NUMBER,
2560 X_Hold_count IN OUT NOCOPY NUMBER,
2561 X_Line_count IN OUT NOCOPY NUMBER,
2562 X_Line_Total IN OUT NOCOPY NUMBER,
2563 X_calling_sequence IN VARCHAR2,
2564 X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
2565
2566 IS
2567 BEGIN
2568 AP_INVOICES_POST_PROCESS_PKG.insert_children (
2569 X_invoice_id,
2570 X_Payment_Priority,
2571 X_Hold_count,
2572 X_Line_count,
2573 X_Line_Total,
2574 X_calling_sequence,
2575 X_Sched_Hold_count); --bug 5334577
2576
2577 EXCEPTION
2578 WHEN OTHERS THEN
2579 APP_EXCEPTION.RAISE_EXCEPTION;
2580
2581 END insert_children;
2582
2583 -----------------------------------------------------------------------
2584 -- Procedure create_holds
2585 -- Creates invoice limit and vendor holds
2586 -- Called for an invoice at POST_UPDATE and POST_INSERT
2587 -----------------------------------------------------------------------
2588 PROCEDURE create_holds (
2589 X_invoice_id IN NUMBER,
2590 X_event IN VARCHAR2 DEFAULT 'UPDATE',
2591 X_update_base IN VARCHAR2 DEFAULT 'N',
2592 X_vendor_changed_flag IN VARCHAR2 DEFAULT 'N',
2593 X_calling_sequence IN VARCHAR2)
2594 IS
2595 BEGIN
2596 AP_INVOICES_POST_PROCESS_PKG.create_holds (
2597 X_invoice_id,
2598 X_event,
2599 X_update_base,
2600 X_vendor_changed_flag,
2601 X_calling_sequence);
2602
2603 EXCEPTION
2604 WHEN OTHERS THEN
2605 APP_EXCEPTION.RAISE_EXCEPTION;
2606
2607 END create_holds;
2608
2609 -----------------------------------------------------------------------
2610 -- Procedure invoice_pre_update
2611 -- Checks to see IF payment schedules should be recalculated.
2612 -- Performs a liability adjustment on paid or partially paid invoices.
2613 -- Determines whether match_status_flag's should be reset on all
2614 -- distributions after the commit has occurred.
2615 -- PRECONDITION: Called during PRE-UPDATE
2616 -----------------------------------------------------------------------
2617
2621 X_payment_status_flag IN OUT NOCOPY VARCHAR2,
2618 PROCEDURE invoice_pre_update (
2619 X_invoice_id IN NUMBER,
2620 X_invoice_amount IN NUMBER,
2622 X_invoice_type_lookup_code IN VARCHAR2,
2623 X_last_updated_by IN NUMBER,
2624 X_accts_pay_ccid IN NUMBER,
2625 X_terms_id IN NUMBER,
2626 X_terms_date IN DATE,
2627 X_discount_amount IN NUMBER,
2628 X_exchange_rate_type IN VARCHAR2,
2629 X_exchange_date IN DATE,
2630 X_exchange_rate IN NUMBER,
2631 X_vendor_id IN NUMBER,
2632 X_payment_method_code IN VARCHAR2,
2633 X_message1 IN OUT NOCOPY VARCHAR2,
2634 X_message2 IN OUT NOCOPY VARCHAR2,
2635 X_reset_match_status IN OUT NOCOPY VARCHAR2,
2636 X_vendor_changed_flag IN OUT NOCOPY VARCHAR2,
2637 X_recalc_pay_sched IN OUT NOCOPY VARCHAR2,
2638 X_liability_adjusted_flag IN OUT NOCOPY VARCHAR2,
2639 X_external_bank_account_id IN NUMBER, --bug 7714053
2640 X_calling_sequence IN VARCHAR2,
2641 X_revalidate_ps IN OUT NOCOPY VARCHAR2)
2642 IS
2643 BEGIN
2644 AP_INVOICES_POST_PROCESS_PKG.invoice_pre_update (
2645 X_invoice_id,
2646 X_invoice_amount,
2647 X_payment_status_flag,
2648 X_invoice_type_lookup_code,
2649 X_last_updated_by,
2650 X_accts_pay_ccid,
2651 X_terms_id,
2652 X_terms_date,
2653 X_discount_amount,
2654 X_exchange_rate_type,
2655 X_exchange_date,
2656 X_exchange_rate,
2657 X_vendor_id,
2658 X_payment_method_code,
2659 X_message1,
2660 X_message2,
2661 X_reset_match_status,
2662 X_vendor_changed_flag,
2663 X_recalc_pay_sched,
2664 X_liability_adjusted_flag,
2665 X_external_bank_account_id, --bug 7714053
2666 X_calling_sequence,
2667 X_revalidate_ps);
2668
2669 EXCEPTION
2670 WHEN OTHERS THEN
2671 APP_EXCEPTION.RAISE_EXCEPTION;
2672
2673 END invoice_pre_update;
2674
2675 -----------------------------------------------------------------------
2676 -- Procedure invoice_post_update
2677 -- o Applies/releases invoice limit and vendor holds
2678 -- o Recalculates payment schedules IF necessary
2679 -- PRECONDITION: Called during POST-UPDATE
2680 -----------------------------------------------------------------------
2681
2682 PROCEDURE invoice_post_update (
2683 X_invoice_id IN NUMBER,
2684 X_payment_priority IN NUMBER,
2685 X_recalc_pay_sched IN OUT NOCOPY VARCHAR2,
2686 X_Hold_count IN OUT NOCOPY NUMBER,
2687 X_update_base IN VARCHAR2,
2688 X_vendor_changed_flag IN VARCHAR2,
2689 X_calling_sequence IN VARCHAR2,
2690 X_Sched_Hold_count IN OUT NOCOPY NUMBER) -- bug 5334577
2691 IS
2692 BEGIN
2693 AP_INVOICES_POST_PROCESS_PKG.invoice_post_update (
2694 X_invoice_id,
2695 X_payment_priority,
2696 X_recalc_pay_sched,
2697 X_Hold_count,
2698 X_update_base,
2699 X_vendor_changed_flag,
2700 X_calling_sequence,
2701 X_Sched_Hold_count); --bug 5334577
2702
2703 EXCEPTION
2704 WHEN OTHERS THEN
2705 APP_EXCEPTION.RAISE_EXCEPTION;
2706
2707 END invoice_post_update;
2708
2709 -----------------------------------------------------------------------
2710 -- Procedure post_forms_commit
2711 -- o Calls distribution PROCEDURE which resets match status,
2712 -- recalculates base, 1099 info, etc.
2713 -- o Determines new invoice-level statuses
2714 -- PRECONDITION: Called during POST-FORMS-COMMIT
2715 -----------------------------------------------------------------------
2716
2717 --Invoice Lines: Distributions.
2718
2719 --Modified the signature of the procedure to get
2720 --highest line number and line total for a invoice as oppose to
2721 --highest distribution line number and distribution total.
2722
2723 PROCEDURE post_forms_commit (
2724 X_invoice_id IN NUMBER,
2725 X_type_1099 IN VARCHAR2,
2726 X_income_tax_region IN VARCHAR2,
2727 X_vendor_changed_flag IN OUT NOCOPY VARCHAR2,
2728 X_update_base IN OUT NOCOPY VARCHAR2,
2729 X_reset_match_status IN OUT NOCOPY VARCHAR2,
2730 X_update_occurred IN OUT NOCOPY VARCHAR2,
2731 X_approval_status_lookup_code IN OUT NOCOPY VARCHAR2,
2732 X_holds_count IN OUT NOCOPY NUMBER,
2733 X_posting_flag IN OUT NOCOPY VARCHAR2,
2734 X_amount_paid IN OUT NOCOPY NUMBER,
2735 X_highest_line_num IN OUT NOCOPY NUMBER,
2736 X_line_total IN OUT NOCOPY NUMBER,
2737 X_actual_invoice_count IN OUT NOCOPY NUMBER,
2738 X_actual_invoice_total IN OUT NOCOPY NUMBER,
2739 X_calling_sequence IN VARCHAR2,
2743
2740 X_sched_holds_count IN OUT NOCOPY NUMBER) IS --bug 5334577
2741
2742 BEGIN
2744 AP_INVOICES_POST_PROCESS_PKG.post_forms_commit (
2745 X_invoice_id,
2746 NULL,
2747 X_type_1099,
2748 X_income_tax_region,
2749 X_vendor_changed_flag,
2750 X_update_base,
2751 X_reset_match_status,
2752 x_update_occurred,
2753 X_approval_status_lookup_code,
2754 X_holds_count,
2755 X_posting_flag,
2756 X_amount_paid,
2757 X_highest_line_num,
2758 X_line_total,
2759 X_actual_invoice_count,
2760 X_actual_invoice_total,
2761 X_calling_sequence,
2762 X_sched_holds_count); --bug 5334577
2763
2764
2765 EXCEPTION
2766 WHEN OTHERS THEN
2767 APP_EXCEPTION.RAISE_EXCEPTION;
2768
2769 END post_forms_commit;
2770
2771 -----------------------------------------------------------------------
2772 -- Procedure Select_Summary calculates the initial value for the
2773 -- batch (actual) total
2774 --
2775 -----------------------------------------------------------------------
2776
2777 PROCEDURE Select_Summary(
2778 X_Batch_ID IN NUMBER,
2779 X_Total IN OUT NOCOPY NUMBER,
2780 X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
2781 X_Calling_Sequence IN VARCHAR2)
2782 IS
2783 BEGIN
2784 AP_INVOICES_POST_PROCESS_PKG.Select_Summary(
2785 X_Batch_ID,
2786 X_Total,
2787 X_Total_Rtot_DB,
2788 X_Calling_Sequence);
2789
2790 EXCEPTION
2791 WHEN OTHERS THEN
2792 APP_EXCEPTION.RAISE_EXCEPTION;
2793 END Select_Summary;
2794
2795 --=========================================================================
2796 -- The Functions above have been mapped to AP_INVOICES_POST_PROCESS_PKG
2797 -- (apinvpps.pls apinvppb.pls)
2798 --=========================================================================
2799
2800 --bug4299234
2801 FUNCTION Get_WFapproval_Status(
2802 P_invoice_id IN NUMBER,
2803 P_org_id IN NUMBER) RETURN VARCHAR2 is
2804 header_approval_status varchar2(50);
2805 l_rejected number;
2806 l_reapprove number;
2807 l_approved number;
2808 l_not_required number;
2809 l_initiated number; -- Bug 5624375
2810
2811 BEGIN
2812
2813 /*Bug5090887 commented the Group by */
2814
2815 select SUM(decode(wfapproval_status,'NOT REQUIRED',1,0)) ,
2816 SUM(decode(wfapproval_status,'APPROVED',1,0)) ,
2817 SUM(decode(wfapproval_status,'REAPPROVE',1,0)) ,
2818 SUM(decode(wfapproval_status,'REJECTED',1,0)),
2819 SUM(decode(wfapproval_status,'INITIATED',1,0))
2820 into l_not_required,l_approved,l_reapprove,l_rejected, l_initiated
2821 from ap_invoice_lines_all
2822 where invoice_id=p_invoice_id
2823 and org_id=p_org_id;
2824 --group by wfapproval_status;
2825
2826 --Bug4926114 chenged the return codes from init caps to caps
2827 If l_initiated>0 then return('INITIATED'); end if; -- Bug 5624375
2828 If l_rejected>0 then return('REJECTED'); end if;
2829 If l_reapprove>0 then return('REAPPROVE'); end if;
2830
2831 select wfapproval_status
2832 into header_approval_status
2833 from ap_invoices_all
2834 where invoice_id=p_invoice_id
2835 and org_id=p_org_id;
2836
2837 IF (l_approved > 0 and header_approval_status = 'NOT REQUIRED') THEN
2838 return('APPROVED');
2839 END IF;
2840 return(header_approval_status);
2841 exception
2842 when no_data_found then
2843 return(header_approval_status); --bug4546162
2844
2845 end Get_WFapproval_Status;
2846
2847
2848
2849 procedure get_payment_attributes (p_le_id IN NUMBER,
2850 p_org_id in number,
2851 p_payee_party_id in number,
2852 p_payee_party_site_id in number,
2853 p_supplier_site_id in number,
2854 p_payment_currency in varchar2,
2855 p_payment_amount in number,
2856 p_payment_function in varchar2,
2857 p_pay_proc_trxn_type_code in varchar2,
2858 p_PAYMENT_METHOD_CODE out nocopy varchar2,
2859 p_IBY_PAYMENT_METHOD out nocopy varchar2,
2860 p_PAYMENT_REASON_CODE out nocopy varchar2,
2861 p_PAYMENT_REASON out nocopy varchar2,
2862 p_BANK_CHARGE_BEARER out nocopy varchar2,
2863 p_BANK_CHARGE_BEARER_DSP out nocopy varchar2,
2864 p_DELIVERY_CHANNEL_CODE out nocopy varchar2,
2865 p_DELIVERY_CHANNEL out nocopy varchar2,
2866 p_SETTLEMENT_PRIORITY out nocopy varchar2,
2867 p_SETTLEMENT_PRIORITY_DSP out nocopy varchar2,
2868 p_PAY_ALONE out nocopy varchar2,
2872 p_bank_branch_name out nocopy varchar2,
2869 p_external_bank_account_id out nocopy number,
2870 p_bank_account_num out nocopy varchar2,
2871 p_bank_account_name out nocopy varchar2,
2873 p_bank_branch_num out nocopy varchar2,
2874 p_bank_name out nocopy varchar2,
2875 p_bank_number out nocopy varchar2,
2876 p_payment_reason_comments out nocopy varchar2, --4874927
2877 p_application_id in number default 200 -- 5115632
2878 ) IS
2879
2880
2881 l_trx_attributes iby_disbursement_comp_pub.Trxn_Attributes_Rec_Type;
2882 l_result_pmt_attributes iby_disbursement_comp_pub.Default_Pmt_Attrs_Rec_Type;
2883 l_return_status varchar2(30);
2884 l_msg_count number;
2885 l_msg_data varchar2(2000);
2886
2887
2888
2889 BEGIN
2890 l_trx_attributes.application_id := nvl(p_application_id, 200);
2891 l_trx_attributes.payer_legal_entity_id := p_le_id;
2892 l_trx_attributes.payer_org_type := 'OPERATING_UNIT';
2893 l_trx_attributes.payer_org_id := p_org_id;
2894 l_trx_attributes.payee_party_id := p_payee_party_id;
2895 l_trx_attributes.payee_party_site_id := p_payee_party_site_id;
2896
2897 if p_supplier_site_id > 0 then
2898 l_trx_attributes.supplier_site_id := p_supplier_site_id;
2899 else
2900 l_trx_attributes.supplier_site_id := null;
2901 end if;
2902
2903
2904 l_trx_attributes.payment_currency := p_payment_currency;
2905 l_trx_attributes.payment_amount := p_payment_amount;
2906 l_trx_attributes.payment_function := p_payment_function;
2907 l_trx_attributes.pay_proc_trxn_type_code := p_pay_proc_trxn_type_code;
2908
2909
2910 iby_disbursement_comp_pub.get_default_payment_attributes(
2911 p_api_version => 1.0,
2912 p_trxn_attributes_rec => l_trx_attributes,
2913 p_ignore_payee_pref => 'N',
2914 x_return_status => l_return_status,
2915 x_msg_count => l_msg_count,
2916 x_msg_data => l_msg_data,
2917 x_default_pmt_attrs_rec => l_result_pmt_attributes);
2918
2919 if l_return_status = FND_API.G_RET_STS_SUCCESS then
2920
2921 p_PAYMENT_METHOD_CODE := l_result_pmt_attributes.payment_method.Payment_Method_Code;
2922 p_IBY_PAYMENT_METHOD := l_result_pmt_attributes.payment_method.Payment_Method_Name;
2923 p_PAYMENT_REASON_CODE := l_result_pmt_attributes.payment_reason.code;
2924 p_PAYMENT_REASON := l_result_pmt_attributes.payment_reason.meaning;
2925 p_BANK_CHARGE_BEARER := l_result_pmt_attributes.Bank_Charge_Bearer.code;
2926 p_BANK_CHARGE_BEARER_DSP := l_result_pmt_attributes.Bank_Charge_Bearer.meaning;
2927 p_DELIVERY_CHANNEL_CODE := l_result_pmt_attributes.delivery_channel.code;
2928 p_DELIVERY_CHANNEL := l_result_pmt_attributes.delivery_channel.meaning;
2929 p_SETTLEMENT_PRIORITY := l_result_pmt_attributes.settlement_priority.code;
2930 p_SETTLEMENT_PRIORITY_DSP := l_result_pmt_attributes.settlement_priority.meaning;
2931 p_PAY_ALONE := l_result_pmt_attributes.pay_alone;
2932 p_bank_account_name := l_result_pmt_attributes.Payee_BankAccount.Payee_BankAccount_Name;
2933 p_bank_account_num := l_result_pmt_attributes.Payee_BankAccount.Payee_BankAccount_Num;
2934 p_external_bank_account_id := l_result_pmt_attributes.Payee_BankAccount.Payee_BankAccount_Id;
2935 p_bank_branch_name := l_result_pmt_attributes.Payee_BankAccount.Payee_BranchName;
2936 p_bank_branch_num := l_result_pmt_attributes.Payee_BankAccount.Payee_BranchNumber;
2937 p_bank_name := l_result_pmt_attributes.Payee_BankAccount.Payee_BankName;
2938 p_bank_number := l_result_pmt_attributes.Payee_BankAccount.Payee_BankNumber;
2939 p_payment_reason_comments := l_result_pmt_attributes.payment_reason_comments; --4874927
2940
2941
2942 end if;
2943
2944 end get_payment_attributes;
2945
2946
2947 procedure validate_docs_payable(p_invoice_id in number,
2948 p_payment_num in number default null,
2949 p_hold_flag out nocopy varchar2) is
2950
2951 cursor docs_to_be_inserted is
2952 select
2953 IBY_DOCS_PAYABLE_GT_S.nextval,
2954 200,
2955 ai.invoice_id,
2956 aps.payment_num,
2957 -- ai.invoice_num,
2958 nvl(ai.pay_proc_trxn_type_code, decode(ai.invoice_type_lookup_code,'EXPENSE REPORT',
2959 'EMPLOYEE_EXP','PAYABLES_DOC')) ,
2960 APS.PAYMENT_METHOD_CODE, --4705834
2961 aps.gross_amount,
2962 nvl(ai.EXCLUSIVE_PAYMENT_FLAG,'N'),
2963 -- As per the discussion with Omar/Jayanta, we will only
2964 -- have payables payment function and no more employee expenses
2965 -- payment function.
2966 nvl(ai.PAYMENT_FUNCTION,'PAYABLES_DISB'),
2967 ai.invoice_date,
2968 ai.invoice_type_lookup_code,
2969 ai.description,
2970 aps.gross_amount ,
2971 aps.EXTERNAL_BANK_ACCOUNT_ID, --4705834
2972 nvl(ai.PARTY_ID,pv.party_id),
2973 nvl(ai.PARTY_SITE_ID, pvs.party_site_id),
2974 decode(sign(ai.vendor_site_id),1,ai.vendor_site_id,null),
2975 ai.LEGAL_ENTITY_ID,
2976 ai.ORG_ID ,
2980 ai.BANK_CHARGE_BEARER ,
2977 'OPERATING_UNIT',
2978 ai.invoice_currency_code,
2979 ai.PAYMENT_CURRENCY_CODE,
2981 ai.PAYMENT_REASON_CODE ,
2982 ai.PAYMENT_REASON_COMMENTS,
2983 ai.SETTLEMENT_PRIORITY ,
2984 aps.REMITTANCE_MESSAGE1 ,
2985 aps.REMITTANCE_MESSAGE2 ,
2986 aps.REMITTANCE_MESSAGE3 ,
2987 ai.UNIQUE_REMITTANCE_IDENTIFIER ,
2988 ai.URI_CHECK_DIGIT ,
2989 ai.DELIVERY_CHANNEL_CODE ,
2990 aps.DISCOUNT_DATE,
2991 aps.CREATED_BY ,
2992 sysdate ,
2993 aps.LAST_UPDATED_BY ,
2994 sysdate,
2995 1,
2996 aps.iby_hold_reason,
2997 aps.hold_flag
2998 from ap_invoices_all ai,
2999 ap_payment_schedules_all aps,
3000 ap_suppliers pv,
3001 ap_supplier_sites_all pvs
3002 where ai.invoice_id = p_invoice_id
3003 and ai.invoice_id = aps.invoice_id
3004 and nvl(p_payment_num, aps.payment_num) = aps.payment_num
3005 and aps.payment_status_flag in ('N','P')
3006 and aps.checkrun_id is null
3007 /* Bug 5612834. Added outer-join for Payment request */
3008 and ai.party_id = pv.party_id (+)
3009 and ai.vendor_site_id = pvs.vendor_site_id(+);
3010
3011
3012 l_DOCUMENT_PAYABLE_ID number;
3013 l_CALLING_APP_ID number;
3014 l_CALLING_APP_DOC_UNIQUE_REF1 number;
3015 l_CALLING_APP_DOC_UNIQUE_REF2 number;
3016 -- l_CALLING_APP_DOC_REF_NUMBER ap_invoices_all.invoice_num%type;
3017 l_PAY_PROC_TRXN_TYPE_CODE ap_invoices_all.pay_proc_trxn_type_code%type;
3018 l_PAYMENT_METHOD_CODE ap_invoices_all.payment_method_code%type;
3019 l_PAYMENT_AMOUNT number;
3020 l_EXCLUSIVE_PAYMENT_FLAG ap_invoices_all.exclusive_payment_flag%type;
3021 l_PAYMENT_FUNCTION ap_invoices_all.payment_function%type;
3022 l_DOCUMENT_DATE date;
3023 l_DOCUMENT_TYPE ap_invoices_all.invoice_type_lookup_code%type;
3024 l_DOCUMENT_DESCRIPTION ap_invoices_all.description%type;
3025 l_DOCUMENT_AMOUNT number;
3026 l_EXTERNAL_BANK_ACCOUNT_ID number;
3027 l_PAYEE_PARTY_ID number;
3028 l_PAYEE_PARTY_SITE_ID number;
3029 l_SUPPLIER_SITE_ID number;
3030 l_LEGAL_ENTITY_ID number;
3031 l_ORG_ID number;
3032 l_ORG_TYPE varchar2(30);
3033 l_DOCUMENT_CURRENCY_CODE ap_invoices_all.invoice_currency_code%type;
3034 l_PAYMENT_CURRENCY_CODE ap_invoices_all.payment_currency_code%type;
3035 l_BANK_CHARGE_BEARER ap_invoices_all.bank_charge_bearer%type;
3036 l_PAYMENT_REASON_CODE ap_invoices_all.payment_reason_code%type;
3037 l_PAYMENT_REASON_COMMENTS ap_invoices_all.payment_reason_comments%type;
3038 l_SETTLEMENT_PRIORITY ap_invoices_all.settlement_priority%type;
3039 l_REMITTANCE_MESSAGE1 ap_payment_schedules_all.remittance_message1%type;
3040 l_REMITTANCE_MESSAGE2 ap_payment_schedules_all.remittance_message2%type;
3041 l_REMITTANCE_MESSAGE3 ap_payment_schedules_all.remittance_message3%type;
3042 l_UNIQUE_REMITTANCE_IDENTIFIER ap_invoices_all.unique_remittance_identifier%type;
3043 l_URI_CHECK_DIGIT ap_invoices_all.uri_check_digit%type;
3044 l_DELIVERY_CHANNEL_CODE ap_invoices_all.delivery_channel_code%type;
3045 l_DISCOUNT_DATE date;
3046 l_CREATED_BY number;
3047 l_CREATION_DATE date;
3048 l_LAST_UPDATED_BY number;
3049 l_LAST_UPDATE_DATE date;
3050 l_OBJECT_VERSION_NUMBER number;
3051 l_iby_hold_reason ap_payment_schedules_all.iby_hold_reason%type;
3052 l_hold_flag ap_payment_schedules_all.hold_flag%type;
3053
3054 l_return_status VARCHAR2(10);
3055 l_msg_count NUMBER;
3056 l_msg_data varchar2(2000);
3057 l_error_message varchar2(255);
3058
3059 -- Bug 5652886
3060 l_iby_error_msg_list iby_error_tab_type;
3061 l_iby_error_msg_str VARCHAR2(2000);
3062
3063 CURSOR iby_error_msg_cursor (p_document_payable_id IN NUMBER) IS
3064 select error_message,
3065 transaction_id
3066 from IBY_TRANSACTION_ERRORS_GT
3067 where transaction_id = p_document_payable_id;
3068
3069 begin
3070
3071 p_hold_flag := 'N';
3072
3073 open docs_to_be_inserted;
3074 loop
3075 fetch docs_to_be_inserted into
3076 l_DOCUMENT_PAYABLE_ID,
3077 l_CALLING_APP_ID,
3078 l_CALLING_APP_DOC_UNIQUE_REF1,
3079 l_CALLING_APP_DOC_UNIQUE_REF2,
3080 -- l_CALLING_APP_DOC_REF_NUMBER,
3081 l_PAY_PROC_TRXN_TYPE_CODE,
3082 l_PAYMENT_METHOD_CODE,
3083 l_PAYMENT_AMOUNT,
3084 l_EXCLUSIVE_PAYMENT_FLAG,
3085 l_PAYMENT_FUNCTION,
3086 l_DOCUMENT_DATE,
3087 l_DOCUMENT_TYPE,
3088 l_DOCUMENT_DESCRIPTION,
3089 l_DOCUMENT_AMOUNT ,
3090 l_EXTERNAL_BANK_ACCOUNT_ID,
3091 l_PAYEE_PARTY_ID,
3092 l_PAYEE_PARTY_SITE_ID,
3093 l_SUPPLIER_SITE_ID,
3094 l_LEGAL_ENTITY_ID,
3095 l_ORG_ID ,
3096 l_ORG_TYPE,
3097 l_DOCUMENT_CURRENCY_CODE,
3098 l_PAYMENT_CURRENCY_CODE,
3099 l_BANK_CHARGE_BEARER ,
3100 l_PAYMENT_REASON_CODE ,
3101 l_PAYMENT_REASON_COMMENTS,
3105 l_REMITTANCE_MESSAGE3 ,
3102 l_SETTLEMENT_PRIORITY ,
3103 l_REMITTANCE_MESSAGE1 ,
3104 l_REMITTANCE_MESSAGE2 ,
3106 l_UNIQUE_REMITTANCE_IDENTIFIER ,
3107 l_URI_CHECK_DIGIT ,
3108 l_DELIVERY_CHANNEL_CODE ,
3109 l_DISCOUNT_DATE,
3110 l_CREATED_BY ,
3111 l_CREATION_DATE ,
3112 l_LAST_UPDATED_BY ,
3113 l_LAST_UPDATE_DATE,
3114 l_OBJECT_VERSION_NUMBER,
3115 l_iby_hold_reason,
3116 l_hold_flag;
3117
3118 exit when docs_to_be_inserted%notfound;
3119
3120
3121
3122
3123 insert into IBY_DOCS_PAYABLE_GT(
3124 DOCUMENT_PAYABLE_ID,
3125 CALLING_APP_ID,
3126 CALLING_APP_DOC_UNIQUE_REF1,
3127 CALLING_APP_DOC_UNIQUE_REF2,
3128 -- CALLING_APP_DOC_REF_NUMBER,
3129 PAY_PROC_TRXN_TYPE_CODE,
3130 PAYMENT_METHOD_CODE,
3131 PAYMENT_AMOUNT,
3132 EXCLUSIVE_PAYMENT_FLAG,
3133 PAYMENT_FUNCTION,
3134 DOCUMENT_DATE,
3135 DOCUMENT_TYPE,
3136 DOCUMENT_DESCRIPTION,
3137 DOCUMENT_AMOUNT ,
3138 EXTERNAL_BANK_ACCOUNT_ID,
3139 PAYEE_PARTY_ID,
3140 PAYEE_PARTY_SITE_ID,
3141 SUPPLIER_SITE_ID,
3142 LEGAL_ENTITY_ID,
3143 ORG_ID ,
3144 ORG_TYPE,
3145 DOCUMENT_CURRENCY_CODE,
3146 PAYMENT_CURRENCY_CODE,
3147 BANK_CHARGE_BEARER ,
3148 PAYMENT_REASON_CODE ,
3149 PAYMENT_REASON_COMMENTS,
3150 SETTLEMENT_PRIORITY ,
3151 REMITTANCE_MESSAGE1 ,
3152 REMITTANCE_MESSAGE2 ,
3153 REMITTANCE_MESSAGE3 ,
3154 UNIQUE_REMITTANCE_IDENTIFIER ,
3155 URI_CHECK_DIGIT ,
3156 DELIVERY_CHANNEL_CODE ,
3157 DISCOUNT_DATE,
3158 CREATED_BY ,
3159 CREATION_DATE ,
3160 LAST_UPDATED_BY ,
3161 LAST_UPDATE_DATE,
3162 OBJECT_VERSION_NUMBER,
3163 ALLOW_REMOVING_DOCUMENT_FLAG)
3164 values (
3165 l_DOCUMENT_PAYABLE_ID,
3166 l_CALLING_APP_ID,
3167 l_CALLING_APP_DOC_UNIQUE_REF1,
3168 l_CALLING_APP_DOC_UNIQUE_REF2,
3169 -- l_CALLING_APP_DOC_REF_NUMBER,
3170 l_PAY_PROC_TRXN_TYPE_CODE,
3171 l_PAYMENT_METHOD_CODE,
3172 l_PAYMENT_AMOUNT,
3173 l_EXCLUSIVE_PAYMENT_FLAG,
3174 l_PAYMENT_FUNCTION,
3175 l_DOCUMENT_DATE,
3176 l_DOCUMENT_TYPE,
3177 l_DOCUMENT_DESCRIPTION,
3178 l_DOCUMENT_AMOUNT ,
3179 l_EXTERNAL_BANK_ACCOUNT_ID,
3180 l_PAYEE_PARTY_ID,
3181 l_PAYEE_PARTY_SITE_ID,
3182 l_SUPPLIER_SITE_ID,
3183 l_LEGAL_ENTITY_ID,
3184 l_ORG_ID ,
3185 l_ORG_TYPE,
3186 l_DOCUMENT_CURRENCY_CODE,
3187 l_PAYMENT_CURRENCY_CODE,
3188 l_BANK_CHARGE_BEARER ,
3189 l_PAYMENT_REASON_CODE ,
3190 l_PAYMENT_REASON_COMMENTS,
3191 l_SETTLEMENT_PRIORITY ,
3192 l_REMITTANCE_MESSAGE1 ,
3193 l_REMITTANCE_MESSAGE2 ,
3194 l_REMITTANCE_MESSAGE3 ,
3195 l_UNIQUE_REMITTANCE_IDENTIFIER ,
3196 l_URI_CHECK_DIGIT ,
3197 l_DELIVERY_CHANNEL_CODE ,
3198 l_DISCOUNT_DATE,
3199 l_CREATED_BY ,
3200 l_CREATION_DATE ,
3201 l_LAST_UPDATED_BY ,
3202 l_LAST_UPDATE_DATE,
3203 l_OBJECT_VERSION_NUMBER,
3204 'N');
3205
3206 --call the api;
3207 IBY_DISBURSEMENT_COMP_PUB.Validate_Documents(
3208 p_api_version => 1,
3209 p_document_id => l_DOCUMENT_PAYABLE_ID,
3210 x_return_status => l_return_status,
3211 x_msg_count => l_msg_count,
3212 x_msg_data => l_msg_data);
3213
3214
3215 /* Bug 5652886. Rewriting the logic for handling multiple holds */
3216 --check the errors table
3217 OPEN iby_error_msg_cursor (l_document_payable_id);
3218 FETCH iby_error_msg_cursor
3219 BULK COLLECT INTO l_iby_error_msg_list;
3220 CLOSE iby_error_msg_cursor;
3221
3222 IF l_iby_error_msg_list.COUNT > 0 THEN
3223
3224 FOR i IN 1..l_iby_error_msg_list.COUNT
3225 LOOP
3226 IF Nvl(length(l_iby_error_msg_str),0) < 1745 THEN
3227 -- iby_hold_reason legth is 2000 and legth of Iby
3228 -- error message is 2000
3229 IF i = 1 THEN
3230 l_iby_error_msg_str := l_iby_error_msg_list(i).error_message;
3231 ELSE
3232 l_iby_error_msg_str := l_iby_error_msg_str||'* *'
3233 ||l_iby_error_msg_list(i).error_message;
3234 END IF;
3235 END IF;
3236 END LOOP;
3237
3238 Update Ap_Payment_Schedules_all
3239 Set hold_flag = 'Y',
3240 iby_hold_reason = l_iby_error_msg_str
3241 Where invoice_id = l_calling_app_doc_unique_ref1
3242 And payment_num = l_calling_app_doc_unique_ref2;
3243
3244 p_hold_flag := 'Y';
3245
3246 ELSE
3247
3248 --if no row exists remove the hold flag and reason on the payment schedule if it
3249 --was previously on hold and there was a hold reason.
3250 IF l_hold_flag = 'Y' and l_iby_hold_reason IS NOT NULL THEN
3251 Update Ap_Payment_Schedules_All
3252 Set hold_flag = 'N',
3253 iby_hold_reason = Null
3254 Where invoice_id = l_calling_app_doc_unique_ref1
3255 And payment_num = l_calling_app_doc_unique_ref2;
3256 END IF;
3257
3258 END IF;
3259
3260 end loop;
3261
3262 close docs_to_be_inserted;
3263
3264 end;
3265
3266
3267 END AP_INVOICES_PKG;