[Home] [Help]
PACKAGE BODY: APPS.AP_DBI_PKG
Source
1 PACKAGE BODY AP_DBI_PKG AS
2 /* $Header: apdbigeb.pls 120.1 2005/10/02 03:15:32 sfeng noship $ */
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,
116 PS_Processed_Flag,
117 Created_By,
118 Creation_Date,
119 Last_Updated_By,
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
249 ||', Operation = '||p_operation
250 ||', Key_Value1 = '||to_char(p_key_value1));
251 END IF;
252 APP_EXCEPTION.RAISE_EXCEPTION;
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 *===========================================================================*/
283
284 PROCEDURE Insert_Payment_Confirm_DBI(
285 p_checkrun_name IN VARCHAR2,
286 p_base_currency_code IN VARCHAR2,
287 p_key_table IN VARCHAR2,
288 p_calling_sequence IN VARCHAR2,
289 p_debug_mode IN VARCHAR2 default 'N') IS
290
291 l_curr_calling_sequence VARCHAR2(2000);
292 l_token_value VARCHAR2(200);
293 l_debug_info varchar2(100);
294
295 CURSOR invoice_cur IS
296 SELECT interest.invoice_id
297 FROM ap_selected_invoices interest
298 WHERE interest.original_invoice_id is NOT NULL --Bug3293887
299 AND interest.checkrun_name = p_checkrun_name;
300
301 CURSOR invoice_dist_cur IS
302 SELECT aid.invoice_distribution_id, aid.invoice_id
303 FROM ap_selected_invoices interest,
304 ap_invoice_distributions aid
305 WHERE interest.original_invoice_id is NOT NULL --Bug3293887
306 AND interest.checkrun_name = p_checkrun_name
307 AND aid.invoice_id = interest.invoice_id;
308
309 CURSOR sched_cur IS
310 SELECT aps.invoice_id, aps.payment_num
311 FROM ap_selected_invoices interest,
312 ap_payment_schedules aps
313 WHERE interest.original_invoice_id is NOT NULL --Bug3293887
314 AND interest.checkrun_name = p_checkrun_name
315 AND aps.invoice_id = interest.invoice_id;
316
317 CURSOR invoice_payments_cur IS
318 SELECT aip.invoice_payment_id
319 FROM ap_invoice_payments aip, ap_checks ac,
320 ap_selected_invoice_checks sic
321 WHERE aip.check_id = ac.check_id
322 AND sic.check_id = ac.check_id
323 AND sic.checkrun_name = p_checkrun_name;
324
325 l_invoice_id number;
326 l_invoice_dist_id number;
327 l_check_id number;
328 l_invoice_pay_id number;
329 l_payment_num number;
330
331 BEGIN
332
333
334 l_curr_calling_sequence := 'AP_DBI_PKG.Insert_Payment_Confirm_DBI<-'||
335 P_calling_sequence;
336
337
338 IF (p_key_table = 'AP_INVOICES_ALL') THEN
339
340 OPEN invoice_cur;
341 LOOP
342 FETCH invoice_cur
343 INTO l_invoice_id;
344 EXIT WHEN invoice_cur%NOTFOUND;
345 --l_num_fetches_inv := l_num_fetches_inv + 1;
346
347 AP_DBI_PKG.Maintain_DBI_Summary
348 (p_table_name => 'AP_INVOICES',
349 p_operation => 'I',
350 p_key_value1 => l_invoice_id,
351 p_calling_sequence => l_curr_calling_sequence);
352
353 END LOOP;
354 CLOSE invoice_cur;
355
356 OPEN invoice_dist_cur;
357 LOOP
358 FETCH invoice_dist_cur
359 INTO l_invoice_dist_id,l_invoice_id;
360 EXIT WHEN invoice_dist_cur%NOTFOUND;
361
362 AP_DBI_PKG.Maintain_DBI_Summary
363 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
364 p_operation => 'I',
365 p_key_value1 => l_invoice_id,
366 p_key_value2 => l_invoice_dist_id,
367 p_calling_sequence => l_curr_calling_sequence);
368 END LOOP;
369 CLOSE invoice_dist_cur;
370
371 OPEN sched_cur;
372 LOOP
373 FETCH sched_cur
374 INTO l_invoice_id, l_payment_num;
375 EXIT WHEN sched_cur%NOTFOUND;
376
377 AP_DBI_PKG.Maintain_DBI_Summary
378 (p_table_name => 'AP_PAYMENT_SCHEDULES',
379 p_operation => 'I',
380 p_key_value1 => l_invoice_id,
381 p_key_value2 => l_payment_num,
382 p_calling_sequence => l_curr_calling_sequence);
383 END LOOP;
384 CLOSE sched_cur;
385
386 ELSIF (p_key_table = 'AP_INVOICE_PAYMENTS_ALL') THEN
387
388 OPEN invoice_payments_cur;
389 LOOP
390 FETCH invoice_payments_cur
391 INTO l_invoice_pay_id;
392 EXIT WHEN invoice_payments_cur%NOTFOUND;
393
394 AP_DBI_PKG.Maintain_DBI_Summary
395 (p_table_name => 'AP_INVOICE_PAYMENTS',
396 p_operation => 'I',
397 p_key_value1 => l_invoice_pay_id,
398 p_calling_sequence => l_curr_calling_sequence);
399 END LOOP;
400 CLOSE invoice_payments_cur;
401
402 END IF;
403
404 NULL;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 IF (SQLCODE <> -20001) THEN
409 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
410 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
411 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
412 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
413 END IF;
414 APP_EXCEPTION.RAISE_EXCEPTION;
415
416
417 END Insert_Payment_Confirm_DBI;
418
419 END AP_DBI_PKG;