[Home] [Help]
PACKAGE BODY: APPS.AP_DBI_PKG
Source
1 PACKAGE BODY AP_DBI_PKG AS
2 /* $Header: apdbigeb.pls 120.1.12010000.2 2009/11/23 03:52:39 ssdeshpa ship $ */
3 -- Public Procedure Specifications
4
5 -- Procedure Definitions
6
7
8 PROCEDURE Maintain_DBI_Summary(p_table_name IN VARCHAR2,
9 p_operation IN VARCHAR2,
10 p_key_value1 IN NUMBER DEFAULT NULL,
11 p_key_value2 IN NUMBER DEFAULT NULL,
12 p_key_value_list IN
13 ap_dbi_pkg.r_dbi_key_value_arr DEFAULT NULL,
14 p_calling_sequence in VARCHAR2) IS
15
16 l_curr_calling_sequence varchar2(2000);
17 l_debug_info varchar2(100);
18
19 l_key_value1 NUMBER DEFAULT NULL;
20 l_use_dbi VARCHAR2(1);
21 l_use_exp_dbi VARCHAR2(1);
22
23
24 BEGIN
25
26 l_curr_calling_sequence := 'Maintain_DBI_Summary ' ||
27 p_calling_sequence;
28
29
30 --Add call to check if DBI is installed
31 FND_PROFILE.GET('FII_AP_DBI_IMP' , l_use_dbi );
32 FND_PROFILE.GET('FII_AP_DBI_EXP_IMP' , l_use_exp_dbi );
33
34 IF nvl(l_use_dbi,'N') = 'Y' OR nvl(l_use_exp_dbi,'N') = 'Y' THEN
35
36
37 l_debug_info := 'Checking list';
38 IF p_key_value_list is not null THEN
39 l_debug_info := 'element exists';
40 --loop through all invoice distribution ids
41 FOR uniq_values IN 1..p_key_value_list.count LOOP
42
43 IF p_table_name = 'AP_INVOICE_DISTRIBUTIONS' THEN
44 --Occasionaly, the invoice_id cannot be determined in
45 --the transaction code.
46 l_debug_info := 'looping through elements';
47 IF p_key_value1 IS NULL then
48 SELECT invoice_id
49 INTO l_key_value1
50 FROM ap_invoice_distributions
51 WHERE invoice_distribution_id =
52 p_key_value_list(uniq_values);
53 END IF;
54
55 l_debug_info := 'inserting record from list';
56 INSERT INTO AP_DBI_LOG(Table_Name,
57 Operation_Flag,
58 Key_Value1,
59 Key_Value2,
60 Exp_Processed_Flag,
61 PS_Processed_Flag,
62 Created_By,
63 Creation_Date,
64 Last_Updated_By,
65 Last_Update_Date,
66 Partition_ID)
67 VALUES
68 (p_table_name,
69 p_operation,
70 nvl(p_key_value1,l_key_value1),
71 p_key_value_list(uniq_values),
72 'N',
73 'N',
74 -1,
75 sysdate,
76 -1,
77 sysdate,
78 mod(to_number(to_char(trunc(sysdate), 'J')), 32)
79 );
80 ELSIF p_table_name = 'AP_INVOICES'
81 OR p_table_name = 'AP_HOLDS' THEN
82 l_debug_info := 'inserting record from list';
83 INSERT INTO AP_DBI_LOG(Table_Name,
84 Operation_Flag,
85 Key_Value1,
86 Key_Value2,
87 Exp_Processed_Flag,
88 PS_Processed_Flag,
89 Created_By,
90 Creation_Date,
91 Last_Updated_By,
92 Last_Update_Date,
93 Partition_ID)
94 VALUES
95 (p_table_name,
96 p_operation,
97 p_key_value_list(uniq_values),
98 p_key_value2,
99 'N',
100 'N',
101 -1,
102 sysdate,
103 -1,
104 sysdate,
105 mod(to_number(to_char(trunc(sysdate), 'J')), 32)
106 );
107 ELSIF p_table_name = 'AP_PAYMENT_SCHEDULES' THEN
108
109 IF p_key_value1 IS NULL THEN
110 /*Have a list of invoices whose payment_nums change*/
111 INSERT INTO AP_DBI_LOG(Table_Name,
112 Operation_Flag,
113 Key_Value1,
114 Key_Value2,
115 Exp_Processed_Flag,
119 Last_Updated_By,
116 PS_Processed_Flag,
117 Created_By,
118 Creation_Date,
120 Last_Update_Date,
121 Partition_ID)
122 SELECT
123 p_table_name,
124 p_operation,
125 p_key_value_list(uniq_values),
126 payment_num,
127 'N',
128 'N',
129 -1,
130 sysdate,
131 -1,
132 sysdate,
133 mod(to_number(to_char(trunc(sysdate), 'J')), 32)
134 FROM AP_PAYMENT_SCHEDULES
135 WHERE invoice_id =p_key_value_list(uniq_values);
136 ELSE /*we have a list of payment_nums for one inv_id*/
137 INSERT INTO AP_DBI_LOG(Table_Name,
138 Operation_Flag,
139 Key_Value1,
140 Key_Value2,
141 Exp_Processed_Flag,
142 PS_Processed_Flag,
143 Created_By,
144 Creation_Date,
145 Last_Updated_By,
146 Last_Update_Date,
147 Partition_ID)
148 SELECT
149 p_table_name,
150 p_operation,
151 p_key_value2,
152 p_key_value_list(uniq_values),
153 'N',
154 'N',
155 -1,
156 sysdate,
157 -1,
158 sysdate,
159 mod(to_number(to_char(trunc(sysdate), 'J')), 32)
160 FROM AP_PAYMENT_SCHEDULES
161 WHERE invoice_id =p_key_value_list(uniq_values);
162 END IF;
163
164 End IF;
165 END LOOP;
166
167 --payment num was not available in the transaction code
168 --but all the payment_nums associated with the invoice were affected*/
169 ELSIF p_table_name = 'AP_PAYMENT_SCHEDULES' and
170 p_key_value2 is null THEN
171
172
173 INSERT INTO AP_DBI_LOG(Table_Name,
174 Operation_Flag,
175 Key_Value1,
176 Key_Value2,
177 Exp_Processed_Flag,
178 PS_Processed_Flag,
179 Created_By,
180 Creation_Date,
181 Last_Updated_By,
182 Last_Update_Date,
183 Partition_ID)
184 SELECT
185 p_table_name,
186 p_operation,
187 p_key_value1,
188 payment_num,
189 'N',
190 'N',
191 -1,
192 sysdate,
193 -1,
194 sysdate,
195 mod(to_number(to_char(trunc(sysdate), 'J')), 32)
196 FROM AP_PAYMENT_SCHEDULES
197 WHERE invoice_id = p_key_value1;
198
199 ELSE -- no values in list for all other situations
200
201 IF p_key_value1 IS NULL then
202 IF p_table_name ='AP_INVOICE_DISTRIBUTIONS' THEN
203 SELECT invoice_id
204 INTO l_key_value1
205 FROM ap_invoice_distributions
206 WHERE invoice_distribution_id =
207 p_key_value2;
208 END IF;
209 END IF;
210
211 l_debug_info := 'inserting normal record';
212 INSERT INTO AP_DBI_LOG(Table_Name,
213 Operation_Flag,
214 Key_Value1,
215 Key_Value2,
216 Exp_Processed_Flag,
217 PS_Processed_Flag,
218 Created_By,
219 Creation_Date,
220 Last_Updated_By,
221 Last_Update_Date,
222 Partition_ID)
223 VALUES
224 (p_table_name,
225 p_operation,
226 nvl(p_key_value1,l_key_value1),
227 p_key_value2,
228 'N',
229 'N',
230 -1,
231 sysdate,
232 -1,
233 sysdate,
234 mod(to_number(to_char(trunc(sysdate), 'J')), 32)
235 );
236
237 END IF;
238 END IF; --using DBI
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 IF (SQLCODE <> -20001) THEN
243 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
244 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
245 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
246 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
247 FND_MESSAGE.SET_TOKEN('PARAMETERS',
248 'Table_Name = '||p_table_name
252 APP_EXCEPTION.RAISE_EXCEPTION;
249 ||', Operation = '||p_operation
250 ||', Key_Value1 = '||to_char(p_key_value1));
251 END IF;
253
254
255 END Maintain_DBI_Summary;
256
257 /*===========================================================================
258 | PUBLIC PROCEDURE Insert_Payment_Confirm_DBI
259 |
260 | DESCRIPTION:
261 | This is a special procedure for inserting invoice related
262 | data during the payment confirmation c program.
263 |
264 | This procedure will call the Maintain_DBI_Summary with the
265 | information required.
266 |
267 | PARAMETERS
268 | p_checkrun_name IN Name of the checkrun for the payment batch
269 | p_base_currency_code IN Base currency code for the payment batch
270 | p_key_table IN Name of the table
271 | p_calling_sequence IN
272 | p_debug_mode IN
273 |
274 | KNOWN ISSUES:
275 |
276 | NOTES:
277 |
278 | MODIFICATION HISTORY
279 | Date Author Description of Change
280 | 04/00/03 Amy McGuire Created -- copied from Biplob Ghose's
281 | Insert_Payment_Confirm_MRC.
282 | 19-Nov-09 ssdeshpa Bug#8964576 DBI information is not getting loaded
283 | for Invoice Payments entity when creating the check
284 | through the PPR
285 *===========================================================================*/
286
287 PROCEDURE Insert_Payment_Confirm_DBI(
288 p_checkrun_name IN VARCHAR2,
289 p_base_currency_code IN VARCHAR2,
290 p_key_table IN VARCHAR2,
291 p_calling_sequence IN VARCHAR2,
292 p_debug_mode IN VARCHAR2 default 'N') IS
293
294 l_curr_calling_sequence VARCHAR2(2000);
295 l_token_value VARCHAR2(200);
296 l_debug_info varchar2(100);
297
298 CURSOR invoice_cur IS
299 SELECT interest.invoice_id
300 FROM ap_selected_invoices interest
301 WHERE interest.original_invoice_id is NOT NULL --Bug3293887
302 AND interest.checkrun_name = p_checkrun_name;
303
304 CURSOR invoice_dist_cur IS
305 SELECT aid.invoice_distribution_id, aid.invoice_id
306 FROM ap_selected_invoices interest,
307 ap_invoice_distributions aid
308 WHERE interest.original_invoice_id is NOT NULL --Bug3293887
309 AND interest.checkrun_name = p_checkrun_name
310 AND aid.invoice_id = interest.invoice_id;
311
312 CURSOR sched_cur IS
313 SELECT aps.invoice_id, aps.payment_num
314 FROM ap_selected_invoices interest,
315 ap_payment_schedules aps
316 WHERE interest.original_invoice_id is NOT NULL --Bug3293887
317 AND interest.checkrun_name = p_checkrun_name
318 AND aps.invoice_id = interest.invoice_id;
319
320 /*Bug#8964576 Fixed the cursor */
321
322 CURSOR invoice_payments_cur IS
323 SELECT aip.invoice_payment_id
324 FROM ap_invoice_payments aip,
325 ap_checks ac
326 WHERE aip.check_id = ac.check_id
327 AND ac.checkrun_name = p_checkrun_name;
328
329 l_invoice_id number;
330 l_invoice_dist_id number;
331 l_check_id number;
332 l_invoice_pay_id number;
333 l_payment_num number;
334
335 BEGIN
336
337
338 l_curr_calling_sequence := 'AP_DBI_PKG.Insert_Payment_Confirm_DBI<-'||
339 P_calling_sequence;
340
341
342 IF (p_key_table = 'AP_INVOICES_ALL') THEN
343
344 OPEN invoice_cur;
345 LOOP
346 FETCH invoice_cur
347 INTO l_invoice_id;
348 EXIT WHEN invoice_cur%NOTFOUND;
349 --l_num_fetches_inv := l_num_fetches_inv + 1;
350
351 AP_DBI_PKG.Maintain_DBI_Summary
352 (p_table_name => 'AP_INVOICES',
353 p_operation => 'I',
354 p_key_value1 => l_invoice_id,
355 p_calling_sequence => l_curr_calling_sequence);
356
357 END LOOP;
358 CLOSE invoice_cur;
359
360 OPEN invoice_dist_cur;
361 LOOP
362 FETCH invoice_dist_cur
363 INTO l_invoice_dist_id,l_invoice_id;
364 EXIT WHEN invoice_dist_cur%NOTFOUND;
365
366 AP_DBI_PKG.Maintain_DBI_Summary
367 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
368 p_operation => 'I',
369 p_key_value1 => l_invoice_id,
370 p_key_value2 => l_invoice_dist_id,
371 p_calling_sequence => l_curr_calling_sequence);
372 END LOOP;
373 CLOSE invoice_dist_cur;
374
375 OPEN sched_cur;
376 LOOP
377 FETCH sched_cur
378 INTO l_invoice_id, l_payment_num;
379 EXIT WHEN sched_cur%NOTFOUND;
380
381 AP_DBI_PKG.Maintain_DBI_Summary
382 (p_table_name => 'AP_PAYMENT_SCHEDULES',
383 p_operation => 'I',
384 p_key_value1 => l_invoice_id,
385 p_key_value2 => l_payment_num,
386 p_calling_sequence => l_curr_calling_sequence);
387 END LOOP;
388 CLOSE sched_cur;
389
390 ELSIF (p_key_table = 'AP_INVOICE_PAYMENTS_ALL') THEN
391
392 OPEN invoice_payments_cur;
393 LOOP
394 FETCH invoice_payments_cur
395 INTO l_invoice_pay_id;
396 EXIT WHEN invoice_payments_cur%NOTFOUND;
397
398 AP_DBI_PKG.Maintain_DBI_Summary
399 (p_table_name => 'AP_INVOICE_PAYMENTS',
400 p_operation => 'I',
401 p_key_value1 => l_invoice_pay_id,
402 p_calling_sequence => l_curr_calling_sequence);
403 END LOOP;
404 CLOSE invoice_payments_cur;
405
406 END IF;
407
408 NULL;
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 IF (SQLCODE <> -20001) THEN
413 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
414 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
415 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
416 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
417 END IF;
418 APP_EXCEPTION.RAISE_EXCEPTION;
419
420
421 END Insert_Payment_Confirm_DBI;
422
423 END AP_DBI_PKG;