[Home] [Help]
PACKAGE BODY: APPS.IGIPMSDA
Source
4 g_user_id NUMBER := fnd_global.user_id;
1 PACKAGE BODY IGIPMSDA AS
2 -- $Header: igipmsdb.pls 115.13 2003/12/01 14:59:08 sdixit ship $
3
5 g_date DATE := sysdate;
6 g_login_id NUMBER := fnd_global.login_id;
7 --bug 3199481: following variables added for fnd logging changes:sdixit :start
8 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 l_state_level number := FND_LOG.LEVEL_STATEMENT;
10 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
11 l_event_level number := FND_LOG.LEVEL_EVENT;
12 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
13 l_error_level number := FND_LOG.LEVEL_ERROR;
14 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
15
16 PROCEDURE WriteToLog ( pp_mesg in varchar2) IS
17 BEGIN
18 FND_FILE.put_line( FND_FILE.log, pp_mesg );
19 END WriteToLog;
20
21 PROCEDURE Synchronize_Invoice ( p_invoice_id in number
22 , p_accounting_rule_id in number
23 ) IS
24 l_default_acc_rule_id NUMBER(15);
25 l_parent_distribution_id number(15);
26 l_default_flag VARCHAR2(1) := 'N';
27 l_duration number;
28 l_continue BOOLEAN;
29 /*
30 -- Check if the invoice has been approved or cancelled
31 -- This excludes prepayments automatically.
32 */
33 CURSOR c_proper_inv (cp_invoice_id in number) IS
34 SELECT inv.invoice_id, inv.approval_status_lookup_code
35 from ap_invoices_v inv
36 WHERE inv.invoice_id = cp_invoice_id
37 and inv.approval_status_lookup_code
38 in ('APPROVED', 'CANCELLED')
39 ;
40
41 CURSOR c_proper_dist ( cp_invoice_id in number) IS
42 SELECT inv_dist.invoice_id, inv_dist.distribution_line_number
43 , inv_dist.invoice_distribution_id, inv_dist.dist_code_combination_id
44 , inv_dist.accounting_date gl_date
45 FROM ap_invoice_distributions inv_dist
46 WHERE inv_dist.invoice_id = cp_invoice_id
47 AND ( inv_dist.line_type_lookup_code = 'ITEM' OR
48 ( inv_dist.line_type_lookup_code = 'TAX' AND
49 inv_dist.tax_recoverable_flag = 'N' AND
50 ( not ( inv_dist.tax_recovery_override_flag is not null ) OR
51 inv_dist.tax_recovery_override_flag = 'N'
52 )
53 )
54 )
55 AND match_status_flag = 'A'
56 ;
57
58 FUNCTION DefaultRuleId ( cp_code_combination_id in number
59 )
60 RETURN NUMBER
61 IS
62 CURSOR c_rule IS
63 SELECT default_accounting_rule_id, 1 priority
64 FROM igi_mpp_expense_rules
65 WHERE enabled_flag = 'Y'
66 and expense_ccid = cp_code_combination_id
67 UNION
68 SELECT p_accounting_rule_id, 2 priority
69 FROM SYS.DUAL
70 WHERE p_accounting_rule_id is not null
71 UNION
72 SELECT default_accounting_rule_id, 3 priority
73 FROM igi_mpp_setup
74 ORDER BY 2
75 ;
76 BEGIN
77 FOR l_rule in C_rule LOOP
78 return l_rule.default_accounting_rule_id;
79 END LOOP;
80 return -1;
81 END DefaultRuleID
82 ;
83 FUNCTION InvoiceDistExists ( cp_invoice_id in number
84 , cp_distribution_line_number in number
85 ) RETURN BOOLEAN IS
86 CURSOR c_exists IS
87 SELECT 'x'
88 FROM igi_mpp_ap_invoice_dists
89 WHERE invoice_id = cp_invoice_id
90 AND distribution_line_number = cp_distribution_line_number
91 ;
92
93 BEGIN
94 FOR l_exists IN C_exists LOOP
95 return TRUE;
96 END LOOP;
97 return FALSE;
98 END InvoiceDistExists
99 ;
100 FUNCTION InvoiceExists ( cp_invoice_id in number )
101 RETURN BOOLEAN IS
102 CURSOR c_exists IS
103 SELECT 'x'
104 FROM igi_mpp_ap_invoices
105 WHERE invoice_id = cp_invoice_id
106 ;
107 BEGIN
108 FOR l_exists IN C_exists LOOP
109 return TRUE;
110 END LOOP;
111 return FALSE;
112 END InvoiceExists;
113
114 FUNCTION IsReversal ( fp_invoice_id in number
115 , fp_distribution_line_number in number
116 )
117 RETURN BOOLEAN IS
118 CURSOR C_reversal IS
122 and distribution_line_number = fp_distribution_line_number
119 select parent_reversal_id
120 from ap_invoice_distributions
121 where invoice_id = fp_invoice_id
123 and reversal_flag = 'Y'
124 and parent_reversal_id is not null
125 ;
126
127 BEGIN
128
129 for l_reversal in c_reversal loop
130 return TRUE;
131 end loop;
132 return FALSE;
133
134 END IsReversal;
135
136 FUNCTION ParentDistributionID ( fp_invoice_distribution_id in number )
137 RETURN NUMBER IS
138 CURSOR c_rev IS
139 SELECT parent_reversal_id
140 FROM ap_invoice_distributions
141 WHERE invoice_distribution_id = fp_invoice_distribution_id;
142 BEGIN
143 FOR l_rev in c_rev LOOP
144 return l_rev.parent_reversal_id;
145 END LOOP;
146 return -1;
147 EXCEPTION WHEN OTHERS THEN
148 --bug 3199481 fnd logging changes:sdixit :start
149 --standard way to handle when-others as per FND logging guidelines
150
151 IF ( l_unexp_level >= l_debug_level ) THEN
152
153 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
154 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
155 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
156 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.l.igipmsdb.ParentDistributionID',TRUE);
157 END IF;
158 --bug 3199481 fnd logging changes: sdixit: end block
159 return -1;
160
161 END ParentDistributionID;
162 PROCEDURE GetMPPDefaults ( fp_invoice_distribution_id in number
163 , ofp_accounting_rule_id in out NOCOPY number
164 , ofp_ignore_mpp_flag in out NOCOPY varchar2
165 )
166 IS
167 CURSOR c_defaults IS
168 SELECT mpp_dist.accounting_rule_id, mpp_dist.ignore_mpp_flag
169 FROM igi_mpp_ap_invoice_dists mpp_dist
170 , ap_invoice_distributions dist
171 WHERE dist.invoice_id = mpp_dist.invoice_id
172 and dist.distribution_line_number
173 = mpp_dist.distribution_line_number
174 and dist.reversal_flag = 'Y'
175 and dist.invoice_distribution_id
176 = fp_invoice_distribution_id
177 ;
178 BEGIN
179 FOR l_defaults in c_defaults LOOP
180 ofp_accounting_rule_id := l_defaults.accounting_rule_id ;
181 ofp_ignore_mpp_flag := l_defaults.ignore_mpp_flag;
182 END LOOP;
183
184 END GetMPPDefaults;
185
186
187 BEGIN
188 --bug 3199481: fnd logging changes:sdixit :start
189 IF (l_state_level >= l_debug_level ) THEN
190 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
191 'BEGIN MPP Expense collection');
192 END IF;
193 IF (l_state_level >= l_debug_level ) THEN
194 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMGLT.InsertInterfaceRec',
195 '>> Invoice Id '||p_invoice_id||' >>');
196 END IF;
197 --bug 3199481 fnd logging changes: sdixit: end block
198
199 FOR l_inv in c_proper_inv ( p_invoice_id ) LOOP
200
201 l_continue := TRUE;
202 --bug 3199481: fnd logging changes:sdixit :start
203 IF (l_state_level >= l_debug_level ) THEN
204 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
205 '>> Invoice has been '||l_inv.approval_status_lookup_code );
206 END IF;
207 --bug 3199481 fnd logging changes: sdixit: end block
208
209 IF NOT invoiceExists ( l_inv.invoice_id ) THEN
210 IF l_inv.approval_status_lookup_code = 'CANCELLED' THEN
211 l_continue := FALSE;
212 ELSE
213 -- Drop record into MPP extended invoice table
214 --bug 3199481: fnd logging changes:sdixit :start
215 IF (l_state_level >= l_debug_level ) THEN
216 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
217 '>> Invoice Does not exist in MPP extended table. Inserting...');
218 END IF;
219 --bug 3199481 fnd logging changes: sdixit: end block
220 INSERT INTO igi_mpp_ap_invoices
221 ( invoice_id
222 , accounting_rule_id
223 , ignore_mpp_flag
224 , created_by
225 , creation_date
226 , last_updated_by
227 , last_update_date
228 , last_update_login
229 ) VALUES (
230 l_inv.invoice_id
231 , p_accounting_rule_id
232 , 'N'
233 , g_user_id
234 , g_date
235 , g_user_id
236 , g_date
237 , g_login_id
238 );
239 END IF;
240 END IF;
241
242 IF l_continue THEN
243
244 --bug 3199481: fnd logging changes:sdixit :start
245 IF (l_state_level >= l_debug_level ) THEN
246 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
247 '>> Continuing... ');
248 END IF;
249 IF InvoiceExists ( l_inv.invoice_id ) THEN
250
254 END IF;
251 IF (l_state_level >= l_debug_level ) THEN
252 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
253 '>> Invoice Does exists. Check the extended distribution records...');
255
256 FOR l_dist in C_proper_dist ( l_inv.invoice_id ) LOOP
257
258 IF (l_state_level >= l_debug_level ) THEN
259 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
260 '>> Distribution Exists. Insert Extended Dist if not there...');
261 END IF;
262 --bug 3199481 fnd logging changes: sdixit: end block
263
264 IF Not InvoiceDistExists ( l_dist.invoice_id,
265 l_dist.distribution_line_number )
266 THEN
267 --bug 3199481: fnd logging changes:sdixit :start
268 IF (l_state_level >= l_debug_level ) THEN
269 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
270 '>> Insert Extended Dist.');
271 END IF;
272 --bug 3199481 fnd logging changes: sdixit: end block
273
274
275 l_default_acc_rule_id := DefaultRuleID
276 ( l_dist.dist_code_combination_id );
277 l_default_flag := 'N';
278
279 IF IsReversal ( l_dist.invoice_id,
280 l_dist.distribution_line_number )
281 THEN
282 l_parent_distribution_id :=
283 ParentDistributionID
284 ( l_dist.invoice_distribution_id );
285 if l_parent_distribution_id = -1 then
286 l_parent_distribution_id :=
287 l_dist.invoice_distribution_id;
288 end if;
289
290 GetMPPDefaults
291 ( l_parent_distribution_id
292 , l_default_acc_rule_id
293 , l_default_flag );
294 END IF;
295
296 select occurrences
297 into l_duration
298 from ra_rules
299 where rule_id = l_default_acc_rule_id;
300
301
302 INSERT into igi_mpp_ap_invoice_dists
303 (
304 distribution_line_number
305 ,invoice_id
306 ,accounting_rule_id
307 ,ignore_mpp_flag
308 ,start_date
309 ,duration
313 ,last_update_date
310 ,created_by
311 ,creation_date
312 ,last_updated_by
314 ,last_update_login
315 ) VALUES ( l_dist.distribution_line_number
316 , l_dist.invoice_id
317 , l_default_acc_rule_id
318 , l_default_flag
319 , l_dist.gl_date
320 , l_duration
321 , g_user_id
322 , g_date
323 , g_user_id
327
324 , g_date
325 , g_login_id
326 );
328
329
330 END IF;
331
332 END LOOP;
333 END IF;
334 END IF; -- If can continue
335 END LOOP;
336 --bug 3199481: fnd logging changes:sdixit :start
337 IF (l_state_level >= l_debug_level ) THEN
338 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',
339 'END MPP Expense Collection.');
340 END IF;
341 --bug 3199481 fnd logging changes: sdixit: end block
342
343 EXCEPTION
344 WHEN others THEN
345 --standard way to handle when-others as per FND logging guidelines
346
347 IF ( l_unexp_level >= l_debug_level ) THEN
348
349 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
350 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
351 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
352 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeInvoice',TRUE);
353 END IF;
354 --bug 3199481 fnd logging changes: sdixit: end block
355 raise_application_error ( -20000, SQLERRM);
356 END;
357
358 PROCEDURE Synchronize_transfer ( errbuf out NOCOPY varchar2
359 , retcode out NOCOPY number
360 , p_transfer_id in number ) IS
361 CURSOR c_inv IS
362 SELECT apinv.invoice_id, imit.accounting_rule_id, imit.rowid
363 imit_rowid
364 from ap_invoices_v apinv
365 , igi_mpp_invoice_transfer imit
366 WHERE apinv.approval_status_lookup_code = 'APPROVED'
367 AND imit.invoice_id = apinv.invoice_id
368 AND imit.transfer_id = p_transfer_id
369 ;
370
371 FUNCTION IsTransferOK ( fp_invoice_id in number )
372 RETURN BOOLEAN IS
373 CURSOR c_trx IS
374 SELECT 'x'
375 FROM igi_mpp_ap_invoices
376 WHERE invoice_id = fp_invoice_id
377 ;
378 BEGIN
379 FOR l_trx in c_trx LOOP
380 return TRUE;
381 END LOOP;
382 return FALSE;
383 END IsTransferOK;
384
385 BEGIN
386 --bug 3199481: fnd logging changes:sdixit :start
387 IF (l_state_level >= l_debug_level ) THEN
388 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeTransfer',
389 ' Transfer ID : '||p_transfer_id );
390 END IF;
391 --bug 3199481 fnd logging changes: sdixit: end block
392 FOR l_inv in C_inv LOOP
393 --bug 3199481: fnd logging changes:sdixit :start
394 IF (l_state_level >= l_debug_level ) THEN
395 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeTransfer',
396 ' Transfer : '||l_inv.invoice_id );
397 END IF;
398 --bug 3199481 fnd logging changes: sdixit: end block
399 Synchronize_invoice ( l_inv.invoice_id, l_inv.accounting_rule_id );
400 IF IsTransferOK ( l_inv.invoice_id) THEN
401 delete from igi_mpp_invoice_transfer
402 where rowid = l_inv.imit_rowid
403 ;
404 END IF;
405 END LOOP;
406 commit;
407 errbuf := 'Normal Completion';
408 retcode := 0;
409 EXCEPTION WHEN OTHERS THEN
410 rollback;
411 --bug 3199481 fnd logging changes: sdixit: start block
412 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
413 retcode := 2;
414 errbuf := Fnd_message.get;
415
416 IF ( l_unexp_level >= l_debug_level ) THEN
417
418 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
419 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
420 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
421 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igipmsdb.IGIPMSDA.SynchronizeTransfer',TRUE);
422 END IF;
423 --bug 3199481 fnd logging changes: sdixit: end block
424 return;
425 END ;
426
427 PROCEDURE Synchronize ( errbuf out NOCOPY varchar2
428 , retcode out NOCOPY number
429 , p_mode in varchar2
430 , p_invoice_num in varchar2
431 , p_vendor_name in varchar2
432 , p_batch_name in varchar2
433 ) IS
434 CURSOR c_inv IS
435 SELECT invoice_id
436 from ap_invoices_v apinv
437 where vendor_name = nvl(p_vendor_name, vendor_name)
438 and ( ( p_batch_name is null )
439 OR
440 ( ( p_batch_name is not null ) AND
441 batch_name = p_batch_name
442 )
443 )
444 and invoice_num = nvl(p_invoice_num, invoice_num)
445 and approval_status_lookup_code in ( 'APPROVED', 'CANCELLED')
446 and set_of_books_id = ( select set_of_books_id from ap_system_parameters )
447 ;
448
449 FUNCTION ModeCheck ( fp_mode in varchar2
450 , fp_invoice_id in number
451 )
452 RETURN BOOLEAN IS
453 CURSOR c_igi_inv IS
454 SELECT 'x'
455 FROM igi_mpp_ap_invoices
456 WHERE invoice_id = fp_invoice_id
457 ;
458 b_rec_found BOOLEAN ;
462
459 BEGIN
460
461 b_rec_found := FALSE;
463 FOR l_igi IN c_igi_inv LOOP
464 b_rec_found := TRUE;
465 END LOOP;
466
470 ELSE
467 IF p_mode = 'EXISTING' THEN
468 IF b_rec_found THEN
469 return TRUE;
471 return FALSE;
472 END IF;
473 ELSIF p_mode = 'NEW' THEN
474 IF b_rec_found THEN
475 return FALSE;
476 ELSE
477 return TRUE;
478 END IF;
479 ELSIF p_mode = 'ALL' THEN
480 return TRUE;
481 ELSE
482 return FALSE;
483 END IF;
484 EXCEPTION WHEN OTHERS THEN
485 --bug 3199481 fnd logging changes:sdixit :start
486
487 IF ( l_unexp_level >= l_debug_level ) THEN
488
489 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
490 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
491 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
492 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.l.igipmsdb.IGIPMSDA.ModeCheck',TRUE);
493 END IF;
494 return FALSE;
495 END ModeCheck;
496 BEGIN
497
498 --bug 3199481: fnd logging changes:sdixit :start
499 IF (l_state_level >= l_debug_level ) THEN
500 FND_LOG.STRING (l_state_level ,'igi.plsql.igipmsdb.IGIPMSDA.Synchronize',
501 'Expense Collection mode : '||p_mode );
502 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.Synchronize',
503 'Invoice number : '||p_invoice_num );
504 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.Synchronize',
505 ' Vendor name : '||p_vendor_name );
506 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.Synchronize',
507 ' Batch name : '||p_batch_name );
508 END IF;
509 --bug 3199481 fnd logging changes: sdixit: end block
510
511 FOR l_inv in C_inv LOOP
512
513 IF ModeCheck ( p_mode, l_inv.invoice_id ) THEN
514 --bug 3199481: fnd logging changes:sdixit :start
515 IF (l_state_level >= l_debug_level ) THEN
516 FND_LOG.STRING (l_state_level , 'igi.plsql.igipmsdb.IGIPMSDA.Synchronize',
517 ' Process : '||l_inv.invoice_id );
518 END IF;
519 --bug 3199481 fnd logging changes: sdixit: end block
520 Synchronize_Invoice ( l_inv.invoice_id );
521 END IF;
522
523 END LOOP;
524 COMMIT;
525 errbuf := 'Normal Completion';
526 retcode := 0;
527
528
529 EXCEPTION WHEN OTHERS THEN
530 rollback;
531 --bug 3199481 fnd logging changes: sdixit: start block
532 --errbuf := SQLERRM;
533 --retcode := 2;
534 --standard way to handle when-others as per FND logging guidelines
535 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
536 retcode := 2;
537 errbuf := Fnd_message.get;
538
539 IF ( l_unexp_level >= l_debug_level ) THEN
540
541 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
542 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
543 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
544 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igipmsdb.IGIPMSDA.Synchronize',TRUE);
545 END IF;
546 --bug 3199481 fnd logging changes: sdixit: end block
547 return;
548 END Synchronize;
549
550 END IGIPMSDA ;