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