[Home] [Help]
PACKAGE BODY: APPS.IGI_STP_CREATE_PCK_PKG
Source
1 PACKAGE BODY IGI_STP_CREATE_PCK_PKG AS
2 -- $Header: igistpcb.pls 120.9 2008/03/05 13:35:55 gkumares ship $
3 -- Processing Variables
4 --following variables added for bug 3199481: fnd logging changes: sdixit
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number := FND_LOG.LEVEL_EVENT;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number := FND_LOG.LEVEL_ERROR;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
12
13
14
15
16 p_batch_id number;
17 p_package_id number;
18 p_netting_trx_type_id number;
19 p_trx_type_class varchar2(3);
20 p_contra_party_id number;
21 p_contra_amount number;
22 l_message varchar2(240);
23 l_variable varchar2(80);
24 l_value varchar2(2000);
25 p_org_id number;
26
27 -- get_candidate_packages : to allow processing of
28 -- all netting transactions
29 --org_id added as part of MOAC uptake
30 CURSOR get_candidate_packages IS
31 SELECT distinct package_id,
32 netting_trx_type_id
33 FROM igi_stp_candidates_all
34 WHERE batch_id =p_batch_id
35 AND org_id = p_org_id;
36
37 -- get_ap_amount : to calculate the sum of the invoice
38 -- amounts selected for a package and netting transaction
39 CURSOR get_ap_amount IS
40 SELECT sum(amount) ap_amount
41 FROM igi_stp_candidates
42 WHERE batch_id = p_batch_id
43 AND application = 'AP'
44 AND netting_trx_type_id = p_netting_trx_type_id
45 AND package_id = p_package_id;
46
47 -- get_ar_amount : to calculate the sum of the invoice
48 -- amounts selected for a package and netting transaction
49 CURSOR get_ar_amount IS
50 SELECT sum(amount) ar_amount
51 FROM igi_stp_candidates_ALL
52 WHERE batch_id = p_batch_id
53 AND application = 'AR'
54 AND ORG_ID = P_ORG_ID
55 AND netting_trx_type_id = p_netting_trx_type_id
56 AND package_id = p_package_id;
57
58 -- Bug 2938450 (Tpradhan)
59 -- Commented the following cursor since it is not in use
60 -- get_ap_only_amount : to calculate the sum of the invoice
61 -- amounts selected for a package and netting transaction
62 /*
63 CURSOR get_ap_only_amount IS
64 SELECT sum(amount) ap_amount
65 FROM igi_stp_candidates cand,
66 igi_stp_net_type_alloc net
67 WHERE cand.batch_id = p_batch_id
68 AND cand.application = 'AP'
69 AND cand.netting_trx_type_id = p_netting_trx_type_id
70 AND cand.package_id = p_package_id
71 AND net.netting_trx_type_id = cand.netting_trx_type_id
72 AND net.application = decode(cand.application,'AP','SQLAP',cand.application)
73 AND net.trx_type_class = p_trx_type_class;
74 */
75
76 -- Bug 2938450 (Tpradhan)
77 -- In the Cursors get_ar_candidate_details, get_ap_candidate_details and get_ap_only_candidate_details
78 -- the Amount column selected has been modified to now select the difference between the amount and
79 -- the current netting amount. This change was made because if only the amount is selected then the
80 -- system checks whether the amount being netted is less than the amount fetched through the cursor.
81 -- If yes then it adds the amount being netted to the existing netting amount. Thus in a scenario
82 -- where the Amount is 5000 and Current Netting Amount is 3000, if the user tries to net a new amount of
83 -- 4000 the system accepts it and adds 4000 to the current netting of 3000 thereby leading to a
84 -- transaction amount of 5000 and netting amount of 3000 + 4000 = 7000. With the changes now it will
85 -- net only 4000 - 3000 which is 1000 to the existing amount of 3000 thus making the total netting
86 -- amount to 4000 thereby ensuring that it does not exceed the transaction amount
87
88 -- get_ar_candidate_details : to retrieve the details
89 -- of the candidate transactions in order to create package
90 -- records
91 CURSOR get_ar_candidate_details IS
92 SELECT package_num,
93 application,
94 trx_id,
95 trx_number,
96 stp_id,
97 site_id,
98 reference,
99 (amount - netting_amount) Amount, -- Bug 2938450 (Details of changes on top)
100 currency_code,
101 exchange_rate,
102 exchange_rate_type,
103 exchange_date
104 FROM igi_stp_candidates
105 WHERE batch_id = p_batch_id
106 AND package_id = p_package_id
107 AND netting_trx_type_id = p_netting_trx_type_id
108 AND process_flag = 'S'
109 AND application = 'AR';
110
111 -- get_ap_candidate_details : to retrieve the details
112 -- of the candidate transactions in order to create package
113 -- records
114 CURSOR get_ap_candidate_details IS
115 SELECT package_num,
116 application,
117 trx_id,
118 trx_number,
119 stp_id,
120 site_id,
121 reference,
122 (amount - netting_amount) Amount, -- Bug 2938450 (Details of changes on top)
123 currency_code,
124 exchange_rate,
125 exchange_rate_type,
126 exchange_date
127 FROM igi_stp_candidates
128 WHERE batch_id = p_batch_id
129 AND package_id = p_package_id
130 AND netting_trx_type_id = p_netting_trx_type_id
131 AND process_flag = 'S'
132 AND application = 'AP';
133
134 -- get_ap_only_candidate_details : to retrieve the details
135 -- of the candidate transactions for a transaction class
136 -- in order to create package records
137 CURSOR get_ap_only_candidate_details IS
138 SELECT cand.package_num,
139 cand.application,
140 cand.trx_id,
141 cand.trx_number,
142 cand.stp_id,
143 cand.site_id,
144 cand.reference,
145 (cand.amount - cand.netting_amount) Amount, -- Bug 2938450 (Details of changes on top)
146 net.trx_type_class,
147 currency_code,
148 exchange_rate,
149 exchange_rate_type,
150 exchange_date
151 FROM igi_stp_candidates_all cand,
152 igi_stp_net_type_alloc_all net
153 WHERE cand.batch_id = p_batch_id
154 AND cand.package_id = p_package_id
155 AND cand.netting_trx_type_id = p_netting_trx_type_id
156 AND cand.process_flag = 'S'
157 AND cand.application = 'AP'
158 AND net.netting_trx_type_id = cand.netting_trx_type_id
159 AND net.application = decode(cand.application,'AP','SQLAP',cand.application)
160 AND net.trx_type_class = 'INV'
161 and cand.org_id = p_org_id
162 and cand.org_id = net.org_id;
163
164 CURSOR get_vendor (p_customer_id in number) is
165 select vendor_id from igi_po_vendors
166 where customer_id = p_customer_id;
167
168 CURSOR get_vendor_sites(p_vendor_id in number) is
169 select vendor_site_id , accts_pay_code_combination_id
170 from ap_supplier_sites_all
171 where vendor_id = p_vendor_id
172 and pay_site_flag = 'Y'
173 and org_id = p_org_id
174 and rownum = 1;
175
176 CURSOR get_customer (p_vendor_id in number) is
177 select customer_id from igi_po_vendors
178 where vendor_id = p_vendor_id;
179
180 CURSOR get_customer_sites(p_customer_id in number ) is
181 select CSU.site_use_id, gl_id_rec
182 from HZ_PARTY_SITES PS,
183 HZ_LOCATIONS LOC,
184 HZ_CUST_ACCT_SITES_ALL CAS,
185 HZ_CUST_SITE_USES CSU
186 where CAS.party_site_id = PS.party_site_id
187 AND LOC.location_id = PS.location_id
188 and CAS.CUST_ACCOUNT_ID = p_customer_id
189 and CSU.cust_acct_site_id = CAS.cust_acct_site_id
190 and CSU.site_use_code ='BILL_TO'
191 AND CSU.PRIMARY_FLAG ='Y';
192
193
194
195 PROCEDURE feed_packages (p_batch_id in number,
196 l_package_id in number,
197 l_package_num in number,
198 l_org_id in number,
199 l_stp_id in number,
200 l_site_id in number,
201 l_amount in number,
202 l_trx_number in varchar2,
203 l_reference in varchar2,
204 l_netting_trx_type_id in number,
205 l_ccid in number,
206 l_application in varchar2,
207 l_trx_type_class in varchar2,
208 l_currency_code in varchar2,
209 l_exchange_rate in number,
210 l_exchange_rate_type in varchar2,
211 l_exchange_date in date)
212 IS
213 l_prefix varchar2(240);
214 local_site_id number;
215 -- l_currency_code varchar2(15);
216
217 cursor get_ar_site is
218 select CSU.site_use_id
219 from HZ_PARTY_SITES PS,
220 HZ_LOCATIONS LOC,
221 HZ_CUST_ACCT_SITES_ALL CAS,
222 HZ_CUST_SITE_USES CSU
223 where CAS.party_site_id = PS.party_site_id
224 AND LOC.location_id = PS.location_id
225 and CAS.CUST_ACCOUNT_ID = l_stp_id
226 and CSU.cust_acct_site_id = CAS.cust_acct_site_id
227 and CSU.site_use_code ='BILL_TO'
228 AND CSU.PRIMARY_FLAG ='Y';
229
230 --Fwd port bug6743918
231 cursor get_ap_site is
232 select vendor_site_id
233 from ap_supplier_sites_all
234 where vendor_id = l_stp_id
235 and pay_site_flag ='Y'
236 and vendor_site_id = l_site_id
237 and org_id = p_org_id;
238
239
240 BEGIN
241
242 -- Get currency code
243 /*
244 if l_application = 'AP'
245 then
246 select invoice_currency_code
247 into l_currency_code
248 from ap_invoices
249 where invoice_num = l_trx_number;
250
251 else
252 select invoice_currency_code
253 into l_currency_code
254 from ra_customer_trx
255 where trx_number = l_trx_number;
256
257 end if; */
258
259 -- Get Document Number prefix --
260 l_message := 'Get Document Number prefix';
261 fnd_profile.get('IGI_STP_NETTING_PREFIX',l_prefix);
262 -- Insert into the packages table
263 l_message := 'Insert into the packages table';
264 if l_netting_trx_type_id in (1,2,4,6)
265 then if l_application = 'AP'
266 then open get_ap_site;
267 fetch get_ap_site into local_site_id;
268 close get_ap_site;
269
270 else
271 open get_ar_site;
272 fetch get_ar_site into local_site_id;
273 close get_ar_site;
274
275 end if;
276 else local_site_id :=null;
277 end if;
278
279 INSERT INTO igi_stp_packages_all
280 ( BATCH_ID ,
281 PACKAGE_ID ,
282 PACKAGE_NUM ,
283 ORG_ID ,
284 STP_ID ,
285 SITE_ID ,
286 APPLICATION ,
287 AMOUNT ,
288 ACCOUNTING_DATE ,
289 TRX_NUMBER ,
290 RELATED_TRX_NUMBER ,
291 REFERENCE ,
292 NETTING_TRX_TYPE_ID ,
293 TECHNICAL_CCID ,
294 REC_OR_LIAB_CCID ,
295 TRX_TYPE_CLASS ,
296 DOC_CATEGORY_CODE ,
297 DESCRIPTION ,
298 CURRENCY_CODE ,
299 EXCHANGE_RATE ,
300 EXCHANGE_RATE_TYPE ,
301 EXCHANGE_DATE ,
302 CREATED_BY ,
303 CREATION_DATE ,
304 LAST_UPDATED_BY ,
305 LAST_UPDATE_DATE )
306 SELECT
307 p_batch_id ,
308 l_package_id ,
309 l_package_num ,
310 P_ORG_ID ,
311 l_stp_id ,
312 nvl(local_site_Id,l_site_id) ,
313 l_application ,
314 l_amount ,
315 sysdate ,
316 l_prefix||to_char(igi_stp_trx_s.nextval) ,
317 l_trx_number ,
318 l_reference ,
319 l_netting_trx_type_id ,
320 net.netting_expense_ccid ,
321 l_ccid ,
322 l_trx_type_class ,
323 net.doc_category_code ,
324 net.netting_trx_type_id ,
325 l_currency_code ,
326 l_exchange_rate ,
327 l_exchange_rate_type ,
328 l_exchange_date ,
329 -1 ,
330 sysdate ,
331 -1 ,
332 sysdate
333 FROM igi_stp_net_type_alloc_ALL net
334 WHERE net.netting_trx_type_id = l_netting_trx_type_id
335 AND net.application = decode(l_application,'AP', 'SQLAP',l_application)
336 AND net.trx_type_class = l_trx_type_class
337 AND ORG_ID = P_ORG_ID;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 -- fnd_message.set_name('IGI', 'MRC_SYSTEM_OPTIONS_NOT_FOUND');
342 -- fnd_message.set_token('MODULE','STEP 1');
343 -- RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
344
345 --bug 3199481 fnd logging changes: sdixit
346 IF ( l_unexp_level >= l_debug_level ) THEN
347
348 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
349 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
350 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
351 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.feed_packages',TRUE);
352 END IF;
353 RAISE_APPLICATION_ERROR(-20010, 'Error in feed_packages procedure');
354 END feed_packages;
355
356
357 PROCEDURE Delete_Candidates (p_user_id in number)
358 IS
359 BEGIN
360 -- Delete Candidates --
361 --ssemwal for Bug 2437020 included where condition
362 DELETE FROM igi_stp_candidates
363 WHERE user_id = p_user_id ;
364 --shsaxena for bug 2713715
365 -- and process_flag = 'R';
366 --shsaxena for bug 2713715
367 EXCEPTION
368 WHEN OTHERS THEN
369 Rollback;
370 --bug 3199481 fnd logging changes: sdixit
371 IF ( l_unexp_level >= l_debug_level ) THEN
372
373 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
374 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
375 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
376 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Delete_Candidates',TRUE);
377 END IF;
378 -- fnd_message.set_name('SQLGL', 'MRC_SYSTEM_OPTIONS_NOT_FOUND');
379 -- fnd_message.set_token('MODULE','STEP 2');
380 -- RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
381 RAISE_APPLICATION_ERROR(-20010, 'Error in delete_candidates procedure');
382 END Delete_Candidates;
383
384
385 PROCEDURE Netting (p_batch_id in number,
386 p_package_id in number,
387 p_netting_trx_type_id in number)
388 IS
389 l_ar_netting_amount number;
390 l_ap_netting_amount number;
391 l_get_ar_amount number;
392 l_get_ap_amount number;
393 l_liability_ccid number;
394 l_rec_ccid number;
395 l_flag varchar2(1);
396
397 BEGIN
398
399
400
401 --------------------------------------
402 -- Process AP or AR Balance package --
403 --------------------------------------
404
405
406 -- Calculate the AP netting amount
407 l_message := 'Getting the initial AP netting amount';
408 OPEN get_ap_amount;
409 FETCH get_ap_amount INTO l_get_ap_amount;
410 CLOSE get_ap_amount;
411
412 l_variable := 'l_get_ap_amount';
413 l_value := to_char(l_get_ap_amount);
414 --bug 3199481 fnd logging changes: sdixit: start block
415 IF l_state_level >= l_debug_level THEN
416 fnd_log.string(l_state_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',l_message||' : '||l_get_ap_amount);
417 END IF;
418 --bug 3199481 fnd logging changes: sdixit: end block
419
420 -- Calculate the AR netting amount
421 l_message := 'Getting the initial AR netting amount';
422 OPEN get_ar_amount;
423 FETCH get_ar_amount INTO l_get_ar_amount;
424 CLOSE get_ar_amount;
425
426 l_variable := 'l_get_ar_amount';
427 l_value := to_char(l_get_ar_amount);
428 --bug 3199481 fnd logging changes: sdixit: start block
429 IF (l_state_level >= l_debug_level ) THEN
430 FND_LOG.STRING (l_state_level ,
431 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
432 l_message||' :'||l_get_ar_amount);
433 END IF;
434 --bug 3199481 fnd logging changes: sdixit: end block
435
436 -- set the netting_amount to the lower of the AP netting amount
437 -- and AR netting amount
438 l_message := 'Getting the netting amount';
439
440 -- Bug 2938450 (Tpradhan), Replaced the from dual select statement with direct assignment
441 l_ar_netting_amount := least(nvl(l_get_ap_amount,0),nvl(l_get_ar_amount,0));
442 l_ap_netting_amount := l_ar_netting_amount;
443 --bug 3199481 fnd logging changes: sdixit: start block
444 IF (l_state_level >= l_debug_level ) THEN
445 FND_LOG.STRING (l_state_level ,
446 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
447 l_message||' :'||l_ar_netting_amount);
448 END IF;
449 --bug 3199481 fnd logging changes: sdixit: end block
450
451
452
453 IF (l_ap_netting_amount <> 0)
454 OR (l_ar_netting_amount <> 0)
455
456 THEN
457 l_flag := '1';
458 -- Process the AR elements of the package --
459 --------------------------------------------
460 FOR ar_rec IN get_ar_candidate_details LOOP
461
462 -- Get the receivables account --
463 l_message := 'Getting the receivable account';
464 IF l_rec_ccid is null
465 THEN
466 SELECT dist.code_combination_id
467 INTO l_rec_ccid
468 FROM ra_customer_trx trx,
469 ra_cust_trx_line_gl_dist dist,
470 igi_stp_candidates candidates
471 WHERE dist.customer_trx_id = trx.customer_trx_id
472 AND dist.account_class = 'REC'
473 AND trx.trx_number = candidates.trx_number
474 AND candidates.package_id = p_package_id
475 AND candidates.netting_trx_type_id = p_netting_trx_type_id
476 AND candidates.batch_id = p_batch_id
477 AND rownum = 1;
478 END IF;
479 l_variable := 'l_rec_ccid';
480 l_value := to_char(l_rec_ccid);
481 --bug 3199481 fnd logging changes: sdixit: start block
482 IF (l_state_level >= l_debug_level ) THEN
483 FND_LOG.STRING (l_state_level ,
484 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
485 l_message||' : '||l_rec_ccid);
486 END IF;
487 --bug 3199481 fnd logging changes: sdixit: end block
488
489
490 feed_packages (p_batch_id,
491 p_package_id,
492 ar_rec.package_num,
493 p_org_id,
494 ar_rec.stp_id,
495 ar_rec.site_id,
496 (-1)*least(ar_rec.amount,l_ar_netting_amount),
497 ar_rec.trx_number,
498 ar_rec.reference,
499 p_netting_trx_type_id,
500 l_rec_ccid,
501 -- Bug: 1079477 changed AP to AR
502 'AR',
503 'CM',
504 ar_rec.currency_code,
505 ar_rec.exchange_rate,
506 ar_rec.exchange_rate_type,
507 ar_rec.exchange_date);
508
509 --bug 3199481 fnd logging changes: sdixit: start block
510 IF (l_state_level >= l_debug_level ) THEN
511 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
512 'Creating a payables document');
513 END IF;
514 --bug 3199481 fnd logging changes: sdixit: end block
515 l_ar_netting_amount := l_ar_netting_amount - ar_rec.amount;
516 l_message := 'AR Netting amount diminish';
517
518 l_variable := 'l_ar_netting_amount';
519 l_value := to_char(l_ar_netting_amount);
520 --bug 3199481 fnd logging changes: sdixit: start block
521 IF (l_state_level >= l_debug_level ) THEN
522 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
523 l_message||' : '||l_ar_netting_amount);
524 END IF;
525 --bug 3199481 fnd logging changes: sdixit: end block
526
527 IF l_ar_netting_amount <= 0
528 THEN exit;
529 END IF;
530
531 END LOOP;
532
533
534
535 -- Process the AP elements of the package -
536 ------------------------------------------
537
538 l_flag := '2';
539 FOR ap_rec IN get_ap_candidate_details LOOP
540
541 -- Get the liability account
542 l_message := 'Getting the liability account';
543 IF l_liability_ccid is null THEN
544 SELECT api.accts_pay_code_combination_id
545 INTO l_liability_ccid
546 FROM ap_invoices api,
547 igi_stp_candidates candidates
548 WHERE api.invoice_id = candidates.trx_id
549 AND candidates.package_id = p_package_id
550 AND candidates.netting_trx_type_id = p_netting_trx_type_id
551 AND candidates.batch_id = p_batch_id
552 AND rownum = 1;
553 END IF;
554
555 l_variable := 'l_liability_ccid';
556 l_value := to_char(l_liability_ccid);
557 --bug 3199481 fnd logging changes: sdixit: start block
558 IF (l_state_level >= l_debug_level ) THEN
559 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
560 l_message||' : '||l_liability_ccid);
561 END IF;
562 --bug 3199481 fnd logging changes: sdixit: end block
563
564 feed_packages (p_batch_id,
565 p_package_id,
566 ap_rec.package_num,
567 p_org_id,
568 ap_rec.stp_id,
569 ap_rec.site_id,
570 (-1)*least(ap_rec.amount,l_ap_netting_amount),
571 ap_rec.trx_number,
572 ap_rec.reference,
573 p_netting_trx_type_id,
574 l_liability_ccid,
575 -- Bug: 1079477 changed AR to AP
576 'AP',
577 'CM',
578 ap_rec.currency_code,
579 ap_rec.exchange_rate,
580 ap_rec.exchange_rate_type,
581 ap_rec.exchange_date);
582
583 l_message := 'Creating a receivable document';
584 --bug 3199481 fnd logging changes: sdixit: start block
585 IF (l_state_level >= l_debug_level ) THEN
586 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',l_message);
587 END IF;
588
589 l_ap_netting_amount := l_ap_netting_amount - ap_rec.amount;
590 l_message := 'AP Netting amount diminish';
591 l_variable := 'l_ap_netting_amount';
592 l_value := to_char(l_ap_netting_amount);
593 --bug 3199481 fnd logging changes: sdixit: start block
594 IF (l_state_level >= l_debug_level ) THEN
595 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting',
596 l_message||' : '||l_ap_netting_amount);
597 END IF;
598
599 IF l_ap_netting_amount <= 0 THEN
600 exit;
601 END IF;
602 END LOOP;
603 END IF;
604 EXCEPTION
605 WHEN OTHERS THEN
606 -- fnd_message.set_name('SQLGL', 'MRC_SYSTEM_OPTIONS_NOT_FOUND');
607 -- fnd_message.set_token('MODULE','STEP 3');
608 -- RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
609 if l_flag = '1' then
610
611 --bug 3199481 fnd logging changes: sdixit
612 IF ( l_unexp_level >= l_debug_level ) THEN
613
614 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
615 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
616 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
617 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting.msg1',TRUE);
618 END IF;
619
620 RAISE_APPLICATION_ERROR(-20010, 'Error in netting procedure Receivable AC ');
621 elsif l_flag = '2' then
622 --bug 3199481 fnd logging changes: sdixit
623 IF ( l_unexp_level >= l_debug_level ) THEN
624
625 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
626 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
627 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
628 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting.msg2',TRUE);
629 END IF;
630 RAISE_APPLICATION_ERROR(-20010, 'Error in netting procedure Liability AC');
631 else
632 --bug 3199481 fnd logging changes: sdixit
633 IF ( l_unexp_level >= l_debug_level ) THEN
634
635 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
636 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
637 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
638 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Netting.msg3',TRUE);
639 END IF;
640 RAISE_APPLICATION_ERROR(-20010, 'Error in netting procedure');
641 end if;
642
643 END Netting;
644
645 procedure trx_sites
646 is
647 begin
648 null;
649 end trx_sites;
650
651 --p_org_id is new input parameter added as part of MOAC uptake
652
653 PROCEDURE AP_only_Netting (p_batch_id in number,
654 p_package_id in number,
655 p_netting_trx_type_id in number,
656 p_contra_party_id in number,
657 p_contra_amount in number,
658 p_org_id in number)
659 IS
660 l_ap_only_netting_amount number;
661 l_liability_ccid number;
662 l_contra_party_site_id number;
663 l_contra_trx_type_id varchar2(30);
664 l_flag varchar2(1);
665 BEGIN
666
667 -- Initialise ap_amount to the sum for all transaction types--
668 l_message := 'Getting the initial AP netting amount for objections to payment and assignments';
669 l_ap_only_netting_amount := nvl(p_contra_amount,0);
670 l_variable := 'l_ap_only_netting_amount';
671 l_value := to_char(l_ap_only_netting_amount);
672 --bug 3199481 fnd logging changes: sdixit: start block
673 -- fnd_file.put_line(fnd_file.log ,l_message||' : '||l_ap_only_netting_amount);
674 IF (l_state_level >= l_debug_level ) THEN
675 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',
676 l_message||' : '||l_ap_only_netting_amount);
677 END IF;
678 --bug 3199481 fnd logging changes: sdixit: end block
679
680 -- Get the contra_party_site_id
681 l_message := 'Get the contra_party_site_id';
682 l_flag := '1';
683 --po_vendor_sites replaced with ap_supplier_sites_all and org_id added
684 SELECT vendor_site_id
685 INTO l_contra_party_site_id
686 FROM ap_supplier_sites_all
687 WHERE vendor_id = p_contra_party_id
688 AND org_id = p_org_id
689 AND rownum = 1;
690 l_variable := 'l_contra_party_site_id';
691 l_value := to_char(l_contra_party_site_id);
692 --bug 3199481 fnd logging changes: sdixit: start block
693 --fnd_file.put_line(fnd_file.log ,l_message||' : '||l_contra_party_site_id);
694 IF (l_state_level >= l_debug_level ) THEN
695 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',
696 l_message||' : '||l_contra_party_site_id);
697 END IF;
698 --bug 3199481 fnd logging changes: sdixit: end block
699
700 l_flag := '2';
701 FOR ap_rec IN get_ap_only_candidate_details LOOP
702
703
704 -- Get the liability account
705 l_message := 'Getting the liability account';
706 IF l_liability_ccid is null
707 THEN
708 SELECT api.accts_pay_code_combination_id
709 INTO l_liability_ccid
710 FROM ap_invoices_all api,
711 igi_stp_candidates_all candidates
712 WHERE api.invoice_id = candidates.trx_id
713 AND candidates.package_id = p_package_id
714 AND candidates.netting_trx_type_id = p_netting_trx_type_id
715 AND candidates.batch_id = p_batch_id
716 and candidates.org_id = p_org_id
717 and api.org_id = candidates.org_id
718 AND rownum = 1;
719 END IF;
720
721 l_variable := 'l_liability_ccid';
722 l_value := to_char(l_liability_ccid);
723 --bug 3199481 fnd logging changes: sdixit: start block
724 -- fnd_file.put_line(fnd_file.log ,l_message||' : '||l_liability_ccid);
725 IF (l_state_level >= l_debug_level ) THEN
726 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.ap_only_Netting',
727 l_message||' : '||l_liability_ccid);
728 END IF;
729 --bug 3199481 fnd logging changes: sdixit: end block
730
731
732 IF p_netting_trx_type_id = 3
733
734 THEN
735 -- IF (nvl(l_ap_only_netting_amount,0) <> 0)
736
737 -- THEN
738 -- Insert the AP document for the stp
739 feed_packages (p_batch_id,
740 p_package_id,
741 ap_rec.package_num,
742 P_ORG_ID,
743 p_contra_party_id,
744 l_contra_party_site_id,
745 least(round(ap_rec.amount,2),l_ap_only_netting_amount),
746 ap_rec.trx_number,
747 ap_rec.reference,
748 p_netting_trx_type_id,
749 l_liability_ccid,
750 'AP',
751 'INV',
752 ap_rec.currency_code,
753 ap_rec.exchange_rate,
754 ap_rec.exchange_rate_type,
755 ap_rec.exchange_date);
756 l_message := 'Create AP objection to payment document for the third party';
757 --bug 3199481 fnd logging changes: sdixit: start block
758 --d_file.put_line(fnd_file.log ,l_message);
759 IF (l_state_level >= l_debug_level ) THEN
760 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',l_message);
761 END IF;
762 --bug 3199481 fnd logging changes: sdixit: end block
763
764 -- Insert the AP document for the Contra party id
765 feed_packages (p_batch_id,
766 p_package_id,
767 ap_rec.package_num,
768 P_ORG_ID,
769 ap_rec.stp_id,
770 ap_rec.site_id,
771 round((-1)*least(ap_rec.amount,l_ap_only_netting_amount),2),
772 ap_rec.trx_number,
773 ap_rec.reference,
774 p_netting_trx_type_id,
775 l_liability_ccid,
776 'AP',
777 'CM',
778 ap_rec.currency_code,
779 ap_rec.exchange_rate,
780 ap_rec.exchange_rate_type,
781 ap_rec.exchange_date);
782 l_message := 'Create AP objection to payment document for the Contra party';
783 --bug 3199481 fnd logging changes: sdixit: start block
784 IF (l_state_level >= l_debug_level ) THEN
785 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',l_message);
786 END IF;
787 --bug 3199481 fnd logging changes: sdixit: end block
788
789 ELSIF p_netting_trx_type_id = 5 THEN
790 -- Netting Trx Type = 5 therefore process Assignments
791 -- Insert the AP document for the stp
792
793 feed_packages (p_batch_id,
794 p_package_id,
795 ap_rec.package_num,
796 P_ORG_ID,
797 p_contra_party_id,
798 l_contra_party_site_id,
799 least(round(ap_rec.amount,2),l_ap_only_netting_amount),
800 -- round(ap_rec.amount,2),
801 ap_rec.trx_number,
802 ap_rec.reference,
803 p_netting_trx_type_id,
804 l_liability_ccid,
805 'AP',
806 'INV',
807 ap_rec.currency_code,
808 ap_rec.exchange_rate,
809 ap_rec.exchange_rate_type,
810 ap_rec.exchange_date);
811
812 l_message := 'Create AP assignment document for the STP';
813 --bug 3199481 fnd logging changes: sdixit: start block
814 IF (l_state_level >= l_debug_level ) THEN
815 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',l_message);
816 END IF;
817 --bug 3199481 fnd logging changes: sdixit: end block
818
819 -- Insert the AP document for the Contra party id
820 feed_packages (p_batch_id,
821 p_package_id,
822 ap_rec.package_num,
823 P_ORG_ID,
824 ap_rec.stp_id,
825 ap_rec.site_id,
826 round((-1)*least(ap_rec.amount,l_ap_only_netting_amount),2),
827 -- round(-1*ap_rec.amount,2),
828 ap_rec.trx_number,
829 ap_rec.reference,
830 p_netting_trx_type_id,
831 l_liability_ccid,
832 'AP',
833 'CM',
834 ap_rec.currency_code,
835 ap_rec.exchange_rate,
836 ap_rec.exchange_rate_type,
837 ap_rec.exchange_date);
838 l_message := 'Create AP assignment doc. for the Contra party';
839 --bug 3199481 fnd logging changes: sdixit: start block
840 IF (l_state_level >= l_debug_level ) THEN
841 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',l_message);
842 END IF;
843 --bug 3199481 fnd logging changes: sdixit: end block
844
845 END IF;
846
847 l_ap_only_netting_amount := l_ap_only_netting_amount - ap_rec.amount;
848 l_message := 'AP Netting amount diminish';
849 l_variable := 'l_ap_only_netting_amount';
850 l_value := to_char(l_ap_only_netting_amount);
851 --bug 3199481 fnd logging changes: sdixit: start block
852 IF (l_state_level >= l_debug_level ) THEN
853 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Ap_only_Netting',l_message);
854 END IF;
855 --bug 3199481 fnd logging changes: sdixit: end block
856
857 IF l_ap_only_netting_amount <= 0
858 THEN exit;
859 END IF;
860 END LOOP;
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 -- fnd_message.set_name('SQLGL', 'MRC_SYSTEM_OPTIONS_NOT_FOUND');
865 -- fnd_message.set_token('MODULE',l_message);
866 -- RAISE_APPLICATION_ERROR(-20010, fnd_message.get);
867 if l_flag = '1' then
868 --bug 3199481 fnd logging changes: sdixit
869 IF ( l_unexp_level >= l_debug_level ) THEN
870
871 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
872 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
873 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
874 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.AP_Only_Netting.msg1',TRUE);
875 END IF;
876 RAISE_APPLICATION_ERROR(-20010, 'Error in ap_only_netting procedure vendor site id');
877 elsif l_flag = '2' then
878 --bug 3199481 fnd logging changes: sdixit
879 IF ( l_unexp_level >= l_debug_level ) THEN
880
881 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
882 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
883 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
884 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.AP_Only_Netting.msg2',TRUE);
885 END IF;
886 RAISE_APPLICATION_ERROR(-20010, 'Error in ap_only_netting procedure liability account');
887 else
888 --bug 3199481 fnd logging changes: sdixit
889 IF ( l_unexp_level >= l_debug_level ) THEN
890
891 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
892 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
893 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
894 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.AP_Only_Netting.msg3',TRUE);
895 END IF;
896 RAISE_APPLICATION_ERROR(-20010, 'Error in ap_only_netting procedure' );
897 end if;
898 END AP_only_Netting;
899
900 --l_org_id is new input parameter added as part of MOAC uptake
901 PROCEDURE Submit_Netting (l_batch_id in number
902 ,l_contra_party_id in number
903 ,l_contra_amount in number
904 ,l_org_id in number)
905 IS
906 l_user_id number;
907
908 BEGIN
909
910
911
912 p_batch_id := l_batch_id;
913 l_variable := 'l_batch_id';
914 l_value := to_char(l_batch_id);
915 p_contra_party_id := l_contra_party_id;
916 l_variable := 'l_contra_party_id';
917 l_value := to_char(l_contra_party_id);
918 p_contra_amount := l_contra_amount;
919 l_variable := 'l_contra_amount';
920 l_value := to_char(l_contra_amount);
921 p_org_id := l_org_id;
922
923
924
925
926
927 FOR candidate_rec IN get_candidate_packages LOOP
928
929 p_package_id := candidate_rec.package_id;
930 p_netting_trx_type_id := candidate_rec.netting_trx_type_id;
931
932
933 l_variable := 'p_package_id';
934 l_value := to_char(p_package_id);
935 l_message := 'package_id : '||to_char(p_package_id);
936 --bug 3199481 fnd logging changes: sdixit: start block
937 IF (l_state_level >= l_debug_level ) THEN
938 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Submit_Netting',l_message);
939 END IF;
940 --bug 3199481 fnd logging changes: sdixit: end block
941
942 l_variable := 'p_netting_trx_type_id';
943 l_value := to_char(p_netting_trx_type_id);
944 l_message := 'netting_trx_type_id : '||to_char(p_netting_trx_type_id);
945 --bug 3199481 fnd logging changes: sdixit: start block
946 IF (l_state_level >= l_debug_level ) THEN
947 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Submit_Netting',l_message);
948 END IF;
949 --bug 3199481 fnd logging changes: sdixit: end block
950
951 --Transaction types 1 and 2 are commented because this partcular feature is not part of R12
952 --p_org_id is new input parameter passed to AP_only_Netting,pay_excess_netting and sup_reimb_netting procedures
953
954 /* IF candidate_rec.netting_trx_type_id = 1 THEN
955 l_message := 'Process AP Balance';
956 Netting(p_batch_id,p_package_id,1);
957 ELSIF candidate_rec.netting_trx_type_id = 2 THEN
958 l_message := 'Process AP Balance';
959 Netting(p_batch_id,p_package_id,2);*/
960 IF candidate_rec.netting_trx_type_id = 3 THEN
961 l_message := 'Process Objections to Payment';
962 AP_only_Netting(p_batch_id,p_package_id,3, p_contra_party_id, p_contra_amount,p_org_id);
963 ELSIF candidate_rec.netting_trx_type_id = 4 THEN
964 l_message := 'Process Payment excesses';
965 pay_excess_netting(p_batch_id,p_package_id,4,p_org_id);
966 ELSIF candidate_rec.netting_trx_type_id = 5 THEN
967
968 l_message := 'Process Assignments';
969 AP_only_Netting(p_batch_id,p_package_id,5, p_contra_party_id, p_contra_amount,p_org_id);
970 ELSIF candidate_rec.netting_trx_type_id = 6 THEN
971 l_message := 'Process Supplier Reimbursements';
972 sup_reimb_netting(p_batch_id,p_package_id,6,p_org_id);
973 END IF;
974 END LOOP;
975
976 l_user_id := fnd_profile.value('USER_ID');
977 l_variable := 'l_user_id';
978 l_value := to_char(l_user_id);
979 l_message := 'Deleting candidates';
980 Delete_Candidates (l_user_id);
981
982
983 END Submit_Netting;
984
985 PROCEDURE pay_excess_netting (p_batch_id in number,
986 package_id in number,
987 p_netting_trx_type_id in number,
988 p_ORG_ID IN NUMBER) IS
989 l_ar_netting_amount number;
990 l_get_ar_amount number;
991 l_liability_ccid number;
992 l_rec_ccid number;
993 l_flag varchar2(1);
994 l_vendor_id number;
995 l_vendor_site_id number;
996 BEGIN
997 --------------------------------------
998 -- Process AP or AR Balance package --
999 --------------------------------------
1000 -- Calculate the AR netting amount
1001 l_message := 'Getting the initial AR netting amount';
1002 OPEN get_ar_amount;
1003 FETCH get_ar_amount INTO l_get_ar_amount;
1004 CLOSE get_ar_amount;
1005
1006 l_variable := 'l_get_ar_amount';
1007 l_value := to_char(l_get_ar_amount);
1008 --bug 3199481 fnd logging changes: sdixit: start block
1009 IF (l_state_level >= l_debug_level ) THEN
1010 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.Pay_excess_Netting',l_message);
1011 END IF;
1012 --bug 3199481 fnd logging changes: sdixit: end block
1013
1014 -- set the netting_amount to the lower of the AP netting amount
1015 -- and AR netting amount
1016 l_message := 'Getting the netting amount';
1017
1018 -- Bug 2938450 (Tpradhan), Replaced the from dual select statement with direct assignment
1019 l_ar_netting_amount := nvl(l_get_ar_amount,0);
1020 l_variable := 'l_ar_netting_amount';
1021 l_value := to_char(l_ar_netting_amount);
1022 --bug 3199481 fnd logging changes: sdixit: start block
1023 IF (l_state_level >= l_debug_level ) THEN
1024 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.pay_excess_Netting',l_message);
1025 END IF;
1026 --bug 3199481 fnd logging changes: sdixit: end block
1027 IF (l_ar_netting_amount <> 0) THEN
1028 l_flag := '1';
1029 -- Process the AR elements of the package --
1030 --------------------------------------------
1031 FOR ar_rec IN get_ar_candidate_details LOOP
1032 -- Get the receivables account --
1033 l_message := 'Getting the receivable account';
1034 IF l_rec_ccid is null THEN
1035 SELECT dist.code_combination_id
1036 INTO l_rec_ccid
1037 FROM ra_customer_trx trx,
1038 ra_cust_trx_line_gl_dist dist,
1039 igi_stp_candidates candidates
1040 WHERE dist.customer_trx_id = trx.customer_trx_id
1041 AND dist.account_class = 'REC'
1042 AND trx.trx_number = candidates.trx_number
1043 AND candidates.package_id = p_package_id
1044 AND candidates.netting_trx_type_id = p_netting_trx_type_id
1045 AND candidates.batch_id = p_batch_id
1046 AND rownum = 1;
1047 END IF;
1048 l_variable := 'l_rec_ccid';
1049 l_value := to_char(l_rec_ccid);
1050 --bug 3199481 fnd logging changes: sdixit: start block
1051 IF (l_state_level >= l_debug_level ) THEN
1052 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.pay_excess_Netting',l_message);
1053 END IF;
1054 --bug 3199481 fnd logging changes: sdixit: end block
1055 l_message := 'Creating a Receivable DM';
1056 feed_packages (p_batch_id,
1057 p_package_id,
1058 ar_rec.package_num,
1059 P_ORG_ID,
1060 ar_rec.stp_id,
1061 ar_rec.site_id,
1062 (-1)*least(ar_rec.amount,l_ar_netting_amount),
1063 ar_rec.trx_number,
1064 ar_rec.reference,
1065 p_netting_trx_type_id,
1066 l_rec_ccid,
1067 'AR',
1068 'DM',
1069 ar_rec.currency_code,
1070 ar_rec.exchange_rate,
1071 ar_rec.exchange_rate_type,
1072 ar_rec.exchange_date);
1073 l_message := 'Creating a payable invoice';
1074 --bug 3199481 fnd logging changes: sdixit: start block
1075 IF (l_state_level >= l_debug_level ) THEN
1076 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.pay_excess_Netting',l_message);
1077 END IF;
1078 --bug 3199481 fnd logging changes: sdixit: end block
1079
1080 open get_vendor(ar_rec.stp_id);
1081 fetch get_vendor into l_vendor_id;
1082 close get_vendor;
1083 open get_vendor_sites(l_vendor_id);
1084 fetch get_vendor_sites into l_vendor_site_id, l_liability_ccid;
1085 close get_vendor_sites;
1086
1087 feed_packages (p_batch_id,
1088 p_package_id,
1089 ar_rec.package_num,
1090 p_org_id,
1091 l_vendor_id,
1092 l_vendor_site_id,
1093 (-1)*least(ar_rec.amount,l_ar_netting_amount),
1094 ar_rec.trx_number,
1095 ar_rec.reference,
1096 p_netting_trx_type_id,
1097 l_liability_ccid,
1098 'AP',
1099 'INV',
1100 ar_rec.currency_code,
1101 ar_rec.exchange_rate,
1102 ar_rec.exchange_rate_type,
1103 ar_rec.exchange_date);
1104 l_ar_netting_amount := l_ar_netting_amount - ar_rec.amount;
1105
1106 l_message := 'Payment Excess Netting amount diminish';
1107 l_variable := 'l_ar_netting_amount';
1108 l_value := to_char(l_ar_netting_amount);
1109 --bug 3199481 fnd logging changes: sdixit: start block
1110 IF (l_state_level >= l_debug_level ) THEN
1111 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.pay_excess_Netting',l_message);
1112 END IF;
1113 --bug 3199481 fnd logging changes: sdixit: end block
1114
1115 IF l_ar_netting_amount <= 0 THEN
1116 exit;
1117 END IF;
1118
1119 END LOOP;
1120 END IF;
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 --bug 3199481 fnd logging changes: sdixit
1124 IF ( l_unexp_level >= l_debug_level ) THEN
1125
1126 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1127 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1128 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1129 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.pay_excess_netting',TRUE);
1130 END IF;
1131
1132 if l_flag = '1' then
1133 RAISE_APPLICATION_ERROR(-20010, 'Error in pay excess netting procedure Receivable AC ');
1134 else
1135 RAISE_APPLICATION_ERROR(-20010, 'Error in pay excess netting procedure');
1136 end if;
1137 END pay_excess_netting;
1138
1139
1140 PROCEDURE sup_reimb_netting (p_batch_id in number,
1141 package_id in number,
1142 p_netting_trx_type_id in number,
1143 p_org_id in number) IS
1144 l_ap_netting_amount number;
1145 l_get_ap_amount number;
1146 l_liability_ccid number;
1147 l_rec_ccid number;
1148 l_customer_id number;
1149 l_customer_site_id number;
1150 l_flag varchar2(1);
1151
1152 BEGIN
1153 --------------------------------------
1154 -- Process AP or AR Balance package --
1155 --------------------------------------
1156
1157
1158 -- Calculate the AP netting amount
1159 l_message := 'Getting the initial AP netting amount';
1160 OPEN get_ap_amount;
1161 FETCH get_ap_amount INTO l_get_ap_amount;
1162 CLOSE get_ap_amount;
1163
1164 l_variable := 'l_get_ap_amount';
1165 l_value := to_char(l_get_ap_amount);
1166 --bug 3199481 fnd logging changes: sdixit: start block
1167 IF (l_state_level >= l_debug_level ) THEN
1168 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.sup_reimb_Netting',l_message);
1169 END IF;
1170 --bug 3199481 fnd logging changes: sdixit: end block
1171
1172 l_message := 'Getting the netting amount';
1173
1174 -- Bug 2938450 (Tpradhan), Replaced the from dual select statement with direct assignment
1175 l_ap_netting_amount := nvl(l_get_ap_amount,0);
1176 IF (l_ap_netting_amount <> 0) THEN
1177 l_flag := '1';
1178 FOR ap_rec IN get_ap_candidate_details LOOP
1179 -- Get the liability account
1180 l_message := 'Getting the liability account';
1181 IF l_liability_ccid is null THEN
1182 SELECT api.accts_pay_code_combination_id
1183 INTO l_liability_ccid
1184 FROM ap_invoices api,
1185 igi_stp_candidates candidates
1186 WHERE api.invoice_id = candidates.trx_id
1187 AND candidates.package_id = p_package_id
1188 AND candidates.netting_trx_type_id = p_netting_trx_type_id
1189 AND candidates.batch_id = p_batch_id
1190 AND rownum = 1;
1191 END IF;
1192 l_variable := 'l_liability_ccid';
1193 l_value := to_char(l_liability_ccid);
1194 --bug 3199481 fnd logging changes: sdixit: start block
1195 --fnd_file.put_line(fnd_file.log ,l_message);
1196 IF (l_state_level >= l_debug_level ) THEN
1197 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.sup_reimb_Netting',l_message);
1198 END IF;
1199 --bug 3199481 fnd logging changes: sdixit: end block
1200
1201 feed_packages (p_batch_id,
1202 p_package_id,
1203 ap_rec.package_num,
1204 p_org_id,
1205 ap_rec.stp_id,
1206 ap_rec.site_id,
1207 (-1)*least(ap_rec.amount,l_ap_netting_amount),
1208 ap_rec.trx_number,
1209 ap_rec.reference,
1210 p_netting_trx_type_id,
1211 l_liability_ccid,
1212 'AP',
1213 'INV',
1214 ap_rec.currency_code,
1215 ap_rec.exchange_rate,
1216 ap_rec.exchange_rate_type,
1217 ap_rec.exchange_date);
1218 l_message := 'Creating a receivable document';
1219 --bug 3199481 fnd logging changes: sdixit: start block
1220 IF (l_state_level >= l_debug_level ) THEN
1221 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.sup_reimb_Netting',l_message);
1222 END IF;
1223 --bug 3199481 fnd logging changes: sdixit: end block
1224
1225 open get_customer(ap_rec.stp_id);
1226 fetch get_customer into l_customer_id;
1227 close get_customer;
1228 open get_customer_sites(l_customer_id);
1229 fetch get_customer_sites into l_customer_site_id,l_rec_ccid;
1230 close get_customer_sites;
1231
1232 feed_packages (p_batch_id,
1233 p_package_id,
1234 ap_rec.package_num,
1235 p_org_id,
1236 l_customer_id,
1237 l_customer_site_id,
1238 (-1)*least(ap_rec.amount,l_ap_netting_amount),
1239 ap_rec.trx_number,
1240 ap_rec.reference,
1241 p_netting_trx_type_id,
1242 l_rec_ccid,
1243 'AR',
1244 'DM',
1245 ap_rec.currency_code,
1246 ap_rec.exchange_rate,
1247 ap_rec.exchange_rate_type,
1248 ap_rec.exchange_date);
1249 l_ap_netting_amount := l_ap_netting_amount - ap_rec.amount;
1250 l_message := 'AP Netting amount diminish';
1251 l_variable := 'l_ap_netting_amount';
1252 l_value := to_char(l_ap_netting_amount);
1253 --bug 3199481 fnd logging changes: sdixit: start block
1254 IF (l_state_level >= l_debug_level ) THEN
1255 FND_LOG.STRING (l_state_level , 'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.sup_reimb_Netting',l_message);
1256 END IF;
1257 --bug 3199481 fnd logging changes: sdixit: end block
1258
1259 IF l_ap_netting_amount <= 0 THEN
1260 exit;
1261 END IF;
1262 END LOOP;
1263 END IF;
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 --bug 3199481 fnd logging changes: sdixit
1267 IF ( l_unexp_level >= l_debug_level ) THEN
1268
1269 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1270 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1271 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1272 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpcb.IGI_STP_CREATE_PCK_PKG.sup_reimb_netting',TRUE);
1273 END IF;
1274 if l_flag = '1' then
1275 RAISE_APPLICATION_ERROR(-20010, 'Error in supplier reimb netting procedure Liability AC');
1276 else
1277 RAISE_APPLICATION_ERROR(-20010, 'Error in supplier reimb netting procedure');
1278 end if;
1279
1280 END sup_reimb_netting;
1281
1282
1283 END IGI_STP_CREATE_PCK_PKG;