eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AP_AIP_TABLE_HANDLER_PKG

Source


1 PACKAGE BODY AP_AIP_TABLE_HANDLER_PKG AS
2 /*$Header: apaipthb.pls 120.11 2005/10/19 22:09:53 bghose noship $*/
3 
4 PROCEDURE insert_row(
5 	P_invoice_id		IN	NUMBER,
6         P_check_id     		IN	NUMBER,
7         P_payment_num	    	IN	NUMBER,
8 	P_invoice_payment_id	IN	NUMBER,
9 	P_old_invoice_payment_id IN 	NUMBER,
10 	P_period_name		IN   	VARCHAR2,
11 	P_accounting_date	IN	DATE,
12 	P_amount		IN	NUMBER,
13 	P_discount_taken	IN	NUMBER,
14 	P_discount_lost		IN	NUMBER,
15 	P_invoice_base_amount	IN	NUMBER,
16 	P_payment_base_amount	IN	NUMBER,
17 	P_accrual_posted_flag	IN	VARCHAR2,
18 	P_cash_posted_flag	IN 	VARCHAR2,
19 	P_posted_flag		IN 	VARCHAR2,
20 	P_set_of_books_id	IN	NUMBER,
21 	P_last_updated_by     	IN 	NUMBER,
22 	P_last_update_login	IN	NUMBER,
23 	P_last_update_date	IN	DATE,
24 	P_currency_code		IN 	VARCHAR2,
25 	P_base_currency_code	IN	VARCHAR2,
26 	P_exchange_rate		IN	NUMBER,
27 	P_exchange_rate_type  	IN 	VARCHAR2,
28 	P_exchange_date		IN 	DATE,
29 	P_bank_account_id	IN	NUMBER,
30 	P_bank_account_num	IN	VARCHAR2,
31 	P_bank_account_type	IN	VARCHAR2,
32 	P_bank_num		IN	VARCHAR2,
33 	P_future_pay_posted_flag  IN   	VARCHAR2,
34 	P_exclusive_payment_flag  IN	VARCHAR2,
35 	P_accts_pay_ccid     	IN	NUMBER,
36 	P_gain_ccid	  	IN	NUMBER,
37 	P_loss_ccid   	  	IN	NUMBER,
38 	P_future_pay_ccid    	IN	NUMBER,
39 	P_asset_ccid	  	IN	NUMBER,
40 	P_payment_dists_flag	IN	VARCHAR2,
41 	P_payment_mode		IN	VARCHAR2,
42 	P_replace_flag		IN	VARCHAR2,
43 	P_attribute1		IN	VARCHAR2,
44 	P_attribute2		IN	VARCHAR2,
45 	P_attribute3		IN	VARCHAR2,
46 	P_attribute4		IN	VARCHAR2,
47 	P_attribute5		IN	VARCHAR2,
48 	P_attribute6		IN	VARCHAR2,
49 	P_attribute7		IN	VARCHAR2,
50 	P_attribute8		IN	VARCHAR2,
51 	P_attribute9		IN	VARCHAR2,
52 	P_attribute10		IN	VARCHAR2,
53 	P_attribute11		IN	VARCHAR2,
54 	P_attribute12		IN	VARCHAR2,
55 	P_attribute13		IN	VARCHAR2,
56 	P_attribute14		IN	VARCHAR2,
57 	P_attribute15		IN	VARCHAR2,
58 	P_attribute_category	IN	VARCHAR2,
59 	P_global_attribute1	IN	VARCHAR2	  Default NULL,
60 	P_global_attribute2	IN	VARCHAR2	  Default NULL,
61 	P_global_attribute3	IN	VARCHAR2	  Default NULL,
62 	P_global_attribute4	IN	VARCHAR2	  Default NULL,
63 	P_global_attribute5	IN	VARCHAR2	  Default NULL,
64 	P_global_attribute6	IN	VARCHAR2	  Default NULL,
65 	P_global_attribute7	IN	VARCHAR2	  Default NULL,
66 	P_global_attribute8	IN	VARCHAR2	  Default NULL,
67 	P_global_attribute9	IN	VARCHAR2	  Default NULL,
68 	P_global_attribute10	IN	VARCHAR2	  Default NULL,
69 	P_global_attribute11	IN	VARCHAR2	  Default NULL,
70 	P_global_attribute12	IN	VARCHAR2	  Default NULL,
71 	P_global_attribute13	IN	VARCHAR2	  Default NULL,
72 	P_global_attribute14	IN	VARCHAR2	  Default NULL,
73 	P_global_attribute15	IN	VARCHAR2	  Default NULL,
74 	P_global_attribute16	IN	VARCHAR2	  Default NULL,
75 	P_global_attribute17	IN	VARCHAR2	  Default NULL,
76 	P_global_attribute18	IN	VARCHAR2	  Default NULL,
77 	P_global_attribute19	IN	VARCHAR2	  Default NULL,
78 	P_global_attribute20	IN	VARCHAR2	  Default NULL,
79 	P_global_attribute_category	  IN	VARCHAR2  Default NULL,
80         P_calling_sequence      IN      VARCHAR2,
81         P_accounting_event_id   IN      NUMBER            Default NULL,
82         P_org_id                IN      NUMBER            Default Null)
83  IS
84  l_iban_number                   IBY_EXT_BANK_ACCOUNTS.IBAN%TYPE; --Bug 4535804
85  current_calling_sequence        VARCHAR2(2000);
86  debug_info                      VARCHAR2(100);
87 
88 BEGIN
89   current_calling_sequence := 'AP_AIP_TABLE_HANDLER_PKG.Insert_Row <-'
90                               ||P_calling_sequence;
91 
92   IF (P_PAYMENT_MODE in ('PAY','REV')) THEN
93 
94     debug_info := 'Get the IBAN_NUMBER';
95     IF  P_Check_Id is not NULL THEN
96       BEGIN
97         SELECT ipb.iban
98           INTO  l_iban_number
99           FROM  ap_checks_all ac, iby_payee_all_bankacct_v ipb  /* External Bank Uptake */
100           WHERE  ac.check_id = P_check_id
101            AND  ipb.ext_bank_account_id = ac.external_bank_account_id
102            AND  ipb.party_id = ac.party_id;
103       EXCEPTION
104         WHEN NO_DATA_FOUND THEN
105           l_iban_number := NULL;
106       END;
107     END IF;
108 
109     debug_info := 'Insert ap_invoice_payments';
110     INSERT INTO AP_INVOICE_PAYMENTS(
111                 invoice_payment_id,
112                 invoice_id,
113                 payment_num,
114                 check_id,
115                 amount,
116                 last_update_date,
117                 last_updated_by,
118                 set_of_books_id,
119                 posted_flag,
120                 accrual_posted_flag,
121                 cash_posted_flag,
122                 accts_pay_code_combination_id,
123                 accounting_date,
124                 period_name,
125                 exchange_rate_type,
126                 exchange_rate,
127                 exchange_date,
128                 discount_lost,
129                 discount_taken,
130                 invoice_base_amount,
131                 payment_base_amount,
132                 asset_code_combination_id,
133                 gain_code_combination_id,
134                 loss_code_combination_id,
135                 bank_account_num,
136                 iban_number,
137                 bank_num,
138                 bank_account_type,
139                 future_pay_code_combination_id,
140                 future_pay_posted_flag,
141                 last_update_login,
142                 creation_date,
143                 created_by,
144                 invoice_payment_type,
145                 other_invoice_id,
146                 reversal_inv_pmt_id,
147                 reversal_flag,
148                 accounting_event_id, -- Events Project - 2
149                 attribute1,
150                 attribute2,
151                 attribute3,
152                 attribute4,
153                 attribute5,
154                 attribute6,
155                 attribute7,
156                 attribute8,
157                 attribute9,
158                 attribute10,
159                 attribute11,
160                 attribute12,
161                 attribute13,
162                 attribute14,
163                 attribute15,
164                 attribute_category,
165                 global_attribute1,
166                 global_attribute2,
167                 global_attribute3,
168                 global_attribute4,
169                 global_attribute5,
170                 global_attribute6,
171                 global_attribute7,
172                 global_attribute8,
173                 global_attribute9,
174                 global_attribute10,
175                 global_attribute11,
176                 global_attribute12,
177                 global_attribute13,
178                 global_attribute14,
179                 global_attribute15,
180                 global_attribute16,
181                 global_attribute17,
182                 global_attribute18,
183                 global_attribute19,
184                 global_attribute20,
185                 global_attribute_category,
186                 org_id,
187                 assets_addition_flag)
188          VALUES(P_invoice_payment_id,
189                 P_invoice_id,
190                 P_payment_num,
191                 P_check_id,
192                 P_amount,
193                 P_last_update_date,
194                 P_last_updated_by,
195                 P_set_of_books_id,
196                 P_posted_flag,
197                 P_accrual_posted_flag,
198                 P_cash_posted_flag,
199                 P_accts_pay_ccid,
200                 P_accounting_date,
201                 P_period_name,
202                 P_exchange_rate_type,
203                 P_exchange_rate,
204                 P_exchange_date,
205                 P_discount_lost,
206                 P_discount_taken,
207                 P_invoice_base_amount,
208                 P_payment_base_amount,
209                 P_asset_ccid,
210                 P_gain_ccid,
211                 P_loss_ccid,
212                 P_bank_account_num,
213                 l_iban_number,
214                 P_bank_num,
215                 P_bank_account_type,
216                 P_future_pay_ccid,
217                 P_future_pay_posted_flag,
218                 P_last_update_login,
219                 SYSDATE,
220                 P_last_updated_by,
221                 '',
222                 '',
223                 P_old_invoice_payment_id,
224                 decode(P_old_invoice_payment_id, '', 'N', 'Y'),
225                 P_accounting_event_id, -- Events Project - 3
226                 P_attribute1,
227                 P_attribute2,
228                 P_attribute3,
229                 P_attribute4,
230                 P_attribute5,
231                 P_attribute6,
232                 P_attribute7,
233                 P_attribute8,
234                 P_attribute9,
235                 P_attribute10,
236                 P_attribute11,
237                 P_attribute12,
238                 P_attribute13,
239                 P_attribute14,
240                 P_attribute15,
241                 P_attribute_category,
242                 P_global_attribute1,
243                 P_global_attribute2,
244                 P_global_attribute3,
245                 P_global_attribute4,
246                 P_global_attribute5,
247                 P_global_attribute6,
248                 P_global_attribute7,
249                 P_global_attribute8,
250                 P_global_attribute9,
251                 P_global_attribute10,
252                 P_global_attribute11,
253                 P_global_attribute12,
254                 P_global_attribute13,
255                 P_global_attribute14,
256                 P_global_attribute15,
257                 P_global_attribute16,
258                 P_global_attribute17,
259                 P_global_attribute18,
260                 P_global_attribute19,
261                 P_global_attribute20,
262                 P_global_attribute_category,
263                 P_org_id,
264                 'U');
265 
266     --Bug 4539462 DBI logging
267     AP_DBI_PKG.Maintain_DBI_Summary
268               (p_table_name => 'AP_INVOICE_PAYMENTS',
269                p_operation => 'I',
270                p_key_value1 => P_invoice_payment_id,
271                 p_calling_sequence => current_calling_sequence);
272 
273     -- Mark old invoice payment as part of a reversal pair
274     if (p_old_invoice_payment_id is not null) then
275       update ap_invoice_payments
276       set    reversal_flag = 'Y',
277              last_update_date = p_last_update_date,
278              last_updated_by = p_last_updated_by
279       where  invoice_payment_id = p_old_invoice_payment_id;
280     end if;
281 
282   end if;
283 
284   -- Update the prepay_amount_remaining if the invoice that is paid is a
285   -- Prepayment Invoice.
286 
287   DECLARE
288     l_invoice_type VARCHAR2(30);
289 
290   BEGIN
291 
292     SELECT invoice_type_lookup_code
293     INTO   l_invoice_type
294     FROM   ap_invoices
295     WHERE  invoice_id = p_invoice_id;
296 
297     IF l_invoice_type = 'PREPAYMENT' THEN
298        --Check for DBI2
299        UPDATE ap_invoice_distributions_all
300        SET    prepay_amount_remaining = total_dist_amount
301        WHERE  invoice_id = p_invoice_id;
302 
303     END IF;
304 
305   EXCEPTION
306     WHEN OTHERS THEN
307       NULL;
308   END;
309 
310 
311 EXCEPTION
312  WHEN OTHERS then
313 
314    if (SQLCODE <> -20001 ) then
315      FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
316      FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
317      FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
318      FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(P_invoice_id)
319                 ||', Payment_num = '||TO_CHAR(P_payment_num)
320                 ||', Check_id = '||TO_CHAR(P_check_id)
321                 ||', Invoice_payment_id = '||TO_CHAR(P_invoice_payment_id)
322               ||', Old Invoice_payment_id = '||TO_CHAR(P_old_invoice_payment_id)
323                 ||', Accounting_date = '||TO_CHAR(P_accounting_date)
324                 ||', Period_name = '||P_period_name
325                 ||', Amount = '||TO_CHAR(P_amount)
326                 ||', accrual_posted_flag = '||P_accrual_posted_flag
327                 ||', cash_posted_flag = '||P_cash_posted_flag
328                 ||', posted_flag = '||P_posted_flag
329                 ||', discount_taken = '||TO_CHAR(P_discount_taken)
330                 ||', discount_lost = '||TO_CHAR(P_discount_lost)
331                 ||', invoice_base_amount = '||TO_CHAR(P_invoice_base_amount)
332                 ||', payment_base_amount = '||TO_CHAR(P_payment_base_amount)
333                 ||', set_of_books_id = '||TO_CHAR(P_set_of_books_id)
334                 ||', currency_code = '||P_currency_code
335                 ||', base_currency_code = '||P_base_currency_code
336                 ||', exchange_rate = '||TO_CHAR(P_exchange_rate)
337                 ||', exchange_rate_type = '||P_exchange_rate_type
338                 ||', exchange_date = '||TO_CHAR(P_exchange_date)
339                 ||', bank_account_id = '||TO_CHAR(P_bank_account_id)
340                 ||', bank_account_num = '||P_bank_account_num
341                 ||', bank_account_type = '||P_bank_account_type
342                 ||', bank_num = '||P_bank_num
343                 ||', future_pay_posted_flag = '||P_future_pay_posted_flag
344                 ||', exclusive_payment_flag = '||P_exclusive_payment_flag
345                 ||', accts_pay_ccid = '||TO_CHAR(P_accts_pay_ccid)
346                 ||', gain_ccid = '||TO_CHAR(P_gain_ccid)
347                 ||', loss_ccid = '||TO_CHAR(P_loss_ccid)
348                 ||', future_pay_ccid= '||TO_CHAR(P_future_pay_ccid)
349                 ||', asset_ccid = '||TO_CHAR(P_asset_ccid)
350                 ||', attribute1 = '||P_attribute1
351                 ||', attribute2 = '||P_attribute2
352                 ||', attribute3 = '||P_attribute3
353                 ||', attribute4 = '||P_attribute4
354                 ||', attribute5 = '||P_attribute5
355                 ||', attribute6 = '||P_attribute6
356                 ||', attribute7 = '||P_attribute7
357                 ||', attribute8 = '||P_attribute8
358                 ||', attribute9 = '||P_attribute9
359                 ||', attribute10 = '||P_attribute10
360                 ||', attribute11 = '||P_attribute11
361                 ||', attribute12 = '||P_attribute12
362                 ||', attribute13 = '||P_attribute13
363                 ||', attribute14 = '||P_attribute14
364                 ||', attribute15 = '||P_attribute15
365                 ||', attribute_category = '||P_attribute_category
366                 ||', Last_update_by = '||TO_CHAR(P_last_updated_by)
367                 ||', Last_update_date = '||TO_CHAR(P_last_update_date)
368                 ||', Last_update_login = '||TO_CHAR(P_last_update_login)
369                 ||', payment_dists_flag = '||P_payment_dists_flag
370                 ||', payment_mode = '||P_payment_mode
371                 ||', replace_flag = '||P_replace_flag);
372 
373      FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
374    end if;
375 
376      APP_EXCEPTION.RAISE_EXCEPTION;
377 
378 end Insert_Row;
379 
380 
381 
382 PROCEDURE Update_Amounts(
383         P_invoice_payment_id    IN      NUMBER,
384         P_amount                IN      NUMBER,
385         P_invoice_base_amount   IN      NUMBER,
386         P_payment_base_amount   IN      NUMBER,
387         P_calling_sequence      IN      VARCHAR2)
388 IS
389 current_calling_sequence  VARCHAR2(2000);
390 debug_info                VARCHAR2(100);
391 
392 
393 Begin
394 
395   -- Update the calling sequence
396   --
397   current_calling_sequence := 'ap_pay_update_inv_pay_amounts<-'||
398                               P_calling_sequence;
399 
400   debug_info := 'update ap_invoice_payments amount';
401 
402   -- Update ap_invoice_payments
403 
404   UPDATE ap_invoice_payments
405     SET amount = nvl(p_amount, amount),
406         invoice_base_amount = nvl(p_invoice_base_amount, invoice_base_amount),
407         payment_base_amount = nvl(p_payment_base_amount, payment_base_amount)
408     WHERE invoice_payment_id = p_invoice_payment_id;
409 
410     --Bug 4539462 DBI logging
411     AP_DBI_PKG.Maintain_DBI_Summary
412               (p_table_name => 'AP_INVOICE_PAYMENTS',
413                p_operation => 'U',
414                p_key_value1 => P_invoice_payment_id,
415                 p_calling_sequence => current_calling_sequence);
416 
417 Exception
418  WHEN OTHERS then
419 
420    if (SQLCODE <> -20001 ) then
421      FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
422      FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
423      FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
424      FND_MESSAGE.SET_TOKEN('PARAMETERS'
425                 ,' Invoice_Payment_Id = '||TO_CHAR(P_invoice_payment_id)
426                 ||', Amount = '||TO_CHAR(P_amount)
427                 ||', Invoice_Base_Amount = '||TO_CHAR(P_Invoice_Base_Amount)
428                 ||', Payment_Base_Amount = '||TO_CHAR(P_Payment_Base_Amount));
432 
429 
430      FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
431    end if;
433    APP_EXCEPTION.RAISE_EXCEPTION;
434 
435 END update_amounts;
436 
437 END AP_AIP_TABLE_HANDLER_PKG;