[Home] [Help]
PACKAGE BODY: APPS.IGIRSTPT
Source
4 l_debug varchar2(5) := NULL;
1 PACKAGE BODY IGIRSTPT AS
2 -- $Header: igistpdb.pls 120.18 2008/03/05 13:34:07 gkumares ship $
3 l_message varchar2(240) := NULL;
5 l_variable varchar2(80) := NULL;
6 l_value varchar2(2000) := NULL;
7 -- Bug 1058426
8 --l_org_id number := fnd_profile.value('ORG_ID'); --shsaxena for bug 2964361
9 p_receivables_batch_source varchar2(80) := fnd_profile.value('IGI_STP_RECEIVABLES_BATCH');
10
11 p_payables_batch_source varchar2(80) := fnd_profile.value('IGI_STP_PAYABLES_SOURCE');
12
13 p_interface_context varchar2(80) := fnd_profile.value('IGI_STP_INTERFACE_CONTEXT');
14
15 -- End of Bug 1058426
16 --following variables added for bug 3199481: fnd logging changes: sdixit
17 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
18 l_state_level number := FND_LOG.LEVEL_STATEMENT;
19 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
20 l_event_level number := FND_LOG.LEVEL_EVENT;
21 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
22 l_error_level number := FND_LOG.LEVEL_ERROR;
23 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
24
25
26
27
28
29 -- --------------------------------------------------------------------------
30 --
31 -- Create_Ra_Interface
32 --
33 -- --------------------------------------------------------------------------
34
35 PROCEDURE Create_Ra_Interface (p_net_batch_id in number,
36 p_set_of_books_id in number,
37 p_org_id in number,
38 p_user_id in number,
39 p_login_id in number,
40 p_sysdate in date,
41 p_currency_code in varchar2)
42 IS
43
44 -- Only Lines are managed --
45 l_line_type varchar2(20) := 'LINE';
46 l_term_id number := fnd_profile.value('IGI_STP_AR_TERMS');
47
48 l_uom_code varchar2(25) := fnd_profile.value('IGI_STP_UOM');
49
50
51
52 -- Accounts combinations fro receivable and revenue --
53 l_revenue_cc_id number;
54 l_receivable_cc_id number;
55
56 l_standing_charge_count number := 0;
57
58 l_batch_source_name VARCHAR2(50);
59 l_batch_source_id number;
60
61 l_doc_sequence_name varchar2(30);
62 l_doc_sequence_number number := 0;
63
64
65 CURSOR get_ar_packages IS
66 SELECT pck.package_id,
67 net.cust_trx_type_id,
68 pck.rec_or_liab_ccid,
69 pck.technical_ccid,
70 pck.stp_id,
71 pck.site_id,
72 rsu.cust_acct_site_id,
73 pck.amount,
74 pck.description,
75 pck.trx_number,
76 pck.trx_type_class,
77 pck.doc_category_code,
78 pck.related_trx_number,
79 pck.accounting_date,
80 pck.currency_code,
81 c.batch_id,
82 pck.exchange_rate,
83 pck.exchange_rate_type,
84 pck.exchange_date
85 FROM igi_stp_packages_all pck,
86 igi_stp_control c,
87 HZ_CUST_SITE_USES rsu,
88 igi_stp_net_type_alloc_all net
89 WHERE c.control_id = p_net_batch_id
90 AND pck.batch_id = c.batch_id
91 AND pck.application ='AR'
92 AND rsu.site_use_id = pck.site_id
93 AND net.netting_trx_type_id = pck.netting_trx_type_id
94 AND net.trx_type_class = pck.trx_type_class
95 AND net.application = pck.application
96 and pck.org_id = p_org_id
97 and pck.org_id = net.org_id;
98
99 BEGIN
100
101
102
103
104
105 --l_uom_code := 'Ea';
106 -- l_term_id :=4;
107
108 --p_receivables_batch_source := 'BR Automatic Numbering';
109 --p_payables_batch_source := 'INVOICE GATEWAY';
110 --p_interface_context := 'STP NETTING';
111 --
112 -- insert a new Invoice line
113 --
114
115 l_message := 'Insert a new AR document';
116 --fnd_file.put_line(fnd_file.log , l_message);
117
118 -- Batch source id selection --
119 SELECT batch_source_id,
120 name
121 INTO l_batch_source_id,
122 l_batch_source_name
123 FROM ra_batch_sources_all
124 WHERE name = p_receivables_batch_source
125 and org_id = p_org_id;
126
127
128 IF l_debug = 'TRUE'
129 THEN
130
131 l_variable := 'l_batch_source_id';
132 l_value := to_char(l_batch_source_id);
133 l_message := 'Searching Batch source id : '||to_char(l_batch_source_id);
134 fnd_file.put_line(fnd_file.log , l_message);
135 END IF;
136
137
138 -- Term identifier --
139 /* select fpov.profile_option_value
140 into l_term_id
141 from fnd_profile_option_values fpov,
142 fnd_profile_options fpo
143 where fpo.profile_option_id = fpov.profile_option_id
144 and profile_option_name = 'IGI_STP_AR_TERMS'; */
145 --fnd_file.put_line(fnd_file.log , 'l_term_id'||l_term_id);
146 IF l_debug = 'TRUE'
147 THEN
148 l_variable := 'l_term_id';
149 l_value := to_char(l_term_id);
150 l_message := 'Searching Term identifier : '||to_char(l_term_id);
151 fnd_file.put_line(fnd_file.log , l_message);
152 END IF;
153
154
155
156 /* select fpov.profile_option_value
157 into l_uom_code
158 from fnd_profile_option_values fpov,
159 fnd_profile_options fpo
160 where fpo.profile_option_id = fpov.profile_option_id
161 and profile_option_name = 'IGI_STP_UOM'; */
162 IF l_debug = 'TRUE'
163 THEN
164 l_variable := 'l_uom_code';
165 l_value := l_uom_code;
166 l_message := 'Searching Term identifier : '||l_uom_code;
167 fnd_file.put_line(fnd_file.log , l_message);
168 END IF;
169
170
171
172 FOR ar_rec in get_ar_packages LOOP
173
174 -- Line number --
175
176 l_standing_charge_count := l_standing_charge_count + 1;
177 IF l_debug = 'TRUE'
178 THEN
179 l_message := to_char(l_standing_charge_count)||'- Creating AR document : '||ar_rec.trx_number;
180 fnd_file.put_line(fnd_file.log , l_message);
181 l_variable := 'l_standing_charge_count';
182 l_value := to_char(l_standing_charge_count);
183 END IF;
184
185 l_receivable_cc_id := ar_rec.rec_or_liab_ccid;
186 l_revenue_cc_id := ar_rec.technical_ccid;
187
188 IF l_debug = 'TRUE'
189 THEN
190 l_variable := 'l_receivable_cc_id';
191 l_value := to_char(l_receivable_cc_id);
192 l_message := 'Receivable code combination id : '||to_char(l_receivable_cc_id);
193 fnd_file.put_line(fnd_file.log , l_message);
194 l_variable := 'l_revenue_cc_id';
195 l_value := to_char(l_revenue_cc_id);
196 l_message := 'Revenue code combination id : '||to_char(l_revenue_cc_id);
197 fnd_file.put_line(fnd_file.log , l_message);
198 END IF;
199
200
201 INSERT INTO ra_interface_lines_ALL( amount
202 , batch_source_name -- Mandatory
203 , comments
204 , description -- Mandatory
205 , currency_code -- Mandatory
206 , gl_date
207 , conversion_date
208 , conversion_rate
209 , conversion_type -- Mandatory
210 , cust_trx_type_id
211 , interface_line_attribute1
212 , interface_line_attribute2
213 , interface_line_attribute3
214 , interface_line_attribute4
215 , interface_line_attribute5
216 , interface_line_attribute6
217 , interface_line_attribute7
218 , interface_line_context
219 , link_to_line_context
220 , line_number
221 , line_type -- Mandatory
222 , orig_system_bill_customer_id
223 , orig_system_bill_address_id
224 , set_of_books_id -- Mandatory
225 -- , document_number
226 , trx_number
227 , uom_code
228 , created_by
229 , creation_date
230 , last_updated_by
231 , last_update_date
232 , last_update_login
233 , term_id
234 ,ORG_ID)
235 VALUES ( round(ar_rec.amount,2)
236 , l_batch_source_name
237 , l_batch_source_name||' '||ar_rec.trx_number
238 , ar_rec.description
239 , nvl(ar_rec.currency_code,p_currency_code)
240 , ar_rec.accounting_date
241 -- , p_sysdate
242 -- , 1
243 -- , 'User'
244 , nvl(ar_rec.exchange_date,sysdate)
245 , nvl(ar_rec.exchange_rate,1)
246 , nvl(ar_rec.exchange_rate_type,'User')
247 , ar_rec.cust_trx_type_id
248 , ar_rec.stp_id
249 , ar_rec.site_id
250 , to_char(ar_rec.batch_id)
251 , ar_rec.package_id
252 , ar_rec.trx_number
253 , ar_rec.trx_type_class
254 , ar_rec.related_trx_number
255 , p_interface_context
256 , p_interface_context
257 , l_standing_charge_count
258 , l_line_type
259 , ar_rec.stp_id
260 , ar_rec.cust_acct_site_id
261 , p_set_of_books_id
262 -- , l_doc_sequence_number
263 , ar_rec.trx_number
264 , l_uom_code
265 , p_user_id
266 , p_sysdate
267 , p_user_id
268 , p_sysdate
269 , p_login_id
270 , decode(ar_rec.trx_type_class,'CM','',l_term_id)
271 ,P_ORG_ID
272 );
273
274 --
275 -- insert a new distribution line
276 --
277 IF l_receivable_cc_id IS NOT NULL
278 THEN
279
280 l_message := 'Inserting receivable distribution for '||ar_rec.trx_number;
281 --fnd_file.put_line(fnd_file.log , l_message);
282 INSERT INTO ra_interface_distributions_ALL( account_class -- Mandatory
283 , interface_line_context
284 , interface_line_attribute1
285 , interface_line_attribute2
286 , interface_line_attribute3
287 , interface_line_attribute4
288 , interface_line_attribute5
289 , interface_line_attribute6
290 , interface_line_attribute7
291 , percent
292 , code_combination_id
293 , created_by
294 , creation_date
295 , last_updated_by
296 , last_update_date
297 , last_update_login
298 ,ORG_ID
299 )
300 VALUES ( 'REC'
301 , p_interface_context
302 , ar_rec.stp_id
303 , ar_rec.site_id
304 , to_char(ar_rec.batch_id)
305 , ar_rec.package_id
306 , ar_rec.trx_number
307 , ar_rec.trx_type_class
308 , ar_rec.related_trx_number
309 , 100
310 , l_receivable_cc_id
311 , p_user_id
312 , p_sysdate
313 , p_user_id
314 , p_sysdate
315 , p_login_id
316 , p_org_id
317 );
318
319 END IF;
320 IF l_revenue_cc_id IS NOT NULL
321 THEN
322 l_message := 'Inserting revenue distribution for '||ar_rec.trx_number;
323 --fnd_file.put_line(fnd_file.log , l_message);
324
325 INSERT INTO ra_interface_distributions_ALL( account_class -- Mandatory
326 , interface_line_context
327 , interface_line_attribute1
328 , interface_line_attribute2
329 , interface_line_attribute3
330 , interface_line_attribute4
331 , interface_line_attribute5
332 , interface_line_attribute6
333 , interface_line_attribute7
334 , percent
335 , code_combination_id
336 , created_by
337 , creation_date
338 , last_updated_by
339 , last_update_date
340 , last_update_login
341 , org_id
342 )
343 VALUES ( 'REV'
344 , p_interface_context
345 , ar_rec.stp_id
346 , ar_rec.site_id
347 , to_char(ar_rec.batch_id)
348 , ar_rec.package_id
349 , ar_rec.trx_number
350 , ar_rec.trx_type_class
351 , ar_rec.related_trx_number
352 , 100
353 , l_revenue_cc_id
354 , p_user_id
355 , p_sysdate
356 , p_user_id
357 , p_sysdate
358 , p_login_id
359 , p_org_id
360 );
361 END IF;
362 END LOOP;
363 COMMIT;
364
365 EXCEPTION
366 WHEN NO_DATA_FOUND
367 THEN
368 l_message := substr(sqlerrm,1,120)||' : '||'No data found for receivable interface for '||l_value;
369 --fnd_file.put_line(fnd_file.log , l_message);
370 UPDATE igi_stp_batches
371 SET batch_status = 'ARFAILED'
372 WHERE batch_id in
373 (select batch_id
374 from igi_stp_control
375 where control_id = p_net_batch_id);
376 commit;
377 --bug 3199481 fnd logging changes: sdixit
378
379 IF ( l_excep_level >= l_debug_level ) THEN
380 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
381 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
382 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
383 FND_LOG.MESSAGE ( l_excep_level,'igi.pls.IGIRSTPT.Create_Ra_Interface.msg1',TRUE);
384 END IF;
385 raise_application_error(-20000, 'Procedure Create_Ra_Interface failed '||SQLERRM);
386 WHEN OTHERS
387 THEN
388 l_message := substr(sqlerrm,1,120)||' : '||l_message;
389 --fnd_file.put_line(fnd_file.log , l_message);
390
391 UPDATE igi_stp_batches
392 SET batch_status = 'ARFAILED'
393 WHERE batch_id in
394 (select batch_id
395 from igi_stp_control
396 where control_id = p_net_batch_id);
397
398 commit;
399 --bug 3199481 fnd logging changes: sdixit
400 --standard way to handle when-others as per FND logging guidelines
401 --not setting seeded message as hardcoded message is being passed
402
403 IF ( l_unexp_level >= l_debug_level ) THEN
404
405 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
406 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
407 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
408 FND_LOG.MESSAGE ( l_unexp_level,'igi.pls.IGIRSTPT.Create_Ra_Interface.msg2',TRUE);
409 END IF;
410 raise_application_error(-20000, 'Procedure Create_Ra_Interface failed '||SQLERRM);
411
412 END Create_Ra_Interface;
413
414 -- --------------------------------------------------------------------------
415 --
416 -- Create_Ap_Interface
417 --
418 -- --------------------------------------------------------------------------
419
420 PROCEDURE Create_Ap_Interface( p_net_batch_id in number,
421 p_set_of_books_id in number,
422 p_org_id in number,
423 p_user_id in number,
424 p_login_id in number,
425 p_sysdate in date,
426 p_currency_code in varchar2)
427 IS
428
429 l_invoice_id number;
430 l_ap_source varchar2(25);
431 l_term_id number := fnd_profile.value('IGI_STP_AP_TERMS');
432
433 --l_term_id number ;
434 l_pay_group varchar2(25);
435
436 CURSOR get_ap_packages IS
437 SELECT pck.package_id,
438 pck.rec_or_liab_ccid,
439 pck.technical_ccid,
440 pck.stp_id,
441 pck.site_id,
442 pck.amount,
443 pck.description,
444 pck.trx_number,
445 pck.trx_type_class,
446 type.cust_trx_type_id invoice_type_lookup_code,
447 pck.accounting_date,
448 pck.doc_category_code,
449 pck.currency_code,
450 pck.exchange_rate,
451 pck.exchange_rate_type,
452 pck.exchange_date
453 FROM igi_stp_packages_all pck,
454 igi_stp_control c,
455 igi_stp_net_type_alloc_all type
456 WHERE c.control_id = p_net_batch_id
457 AND pck.batch_id = c.batch_id
458 AND pck.application = 'AP'
459 AND type.netting_trx_type_id = pck.netting_trx_type_id
460 AND type.trx_type_class = pck.trx_type_class
461 AND type.application = 'SQLAP'
462 and pck.org_id = p_org_id
463 and pck.org_id = type.org_id;
464
465
466 BEGIN
467
468
469
470 l_pay_group := fnd_profile.value('IGI_STP_PAYGROUP');
471
472 --l_pay_group := 'Standard';
473
474
475 IF l_pay_group is null
476 THEN
477
478 l_pay_group := 'Standard';
479
480 END IF;
481 --p_payables_batch_source := 'INVOICE GATEWAY';
482 --l_uom_code := 'Ea';
483 --l_term_id := 4;
484
485 select lookup_code
486 into l_ap_source
487 -- from IGI_AP_PO_LOOKUP_CODES_V
488 from AP_LOOKUP_CODES
489 where lookup_type = 'SOURCE'
490 and lookup_code = p_payables_batch_source;
491
492
493
494 IF l_debug = 'TRUE'
495 THEN
496 l_variable := 'l_ap_source';
497 l_value := l_ap_source;
498 l_message := 'Checking existance of an AP source : '||l_ap_source;
499 fnd_file.put_line(fnd_file.log , l_message);
500 END IF;
501
502
503 /* select fpov.profile_option_value
504 into l_term_id
505 from fnd_profile_option_values fpov,
506 fnd_profile_options fpo
507 where fpo.profile_option_id = fpov.profile_option_id
508 and profile_option_name = 'IGI_STP_AP_TERMS'; */
509 IF l_debug = 'TRUE'
510 THEN
511 l_variable := 'l_term_id';
512 l_value := to_char(l_term_id);
513 l_message := 'Searching term_id through a profile option : '||l_value;
514 fnd_file.put_line(fnd_file.log , l_message);
515 END IF;
516
517
518 FOR ap_rec in get_ap_packages LOOP
519
520 select ap_invoices_s.nextval
521 into l_invoice_id
522 from dual;
523 IF l_debug = 'TRUE'
524 THEN
525 l_variable := 'l_invoice_id';
526 l_value := to_char(l_invoice_id);
527 l_message := 'AP document number is '||ap_rec.trx_number||' and document id is: '||l_value;
528 fnd_file.put_line(fnd_file.log , l_message);
529 END IF;
530
531
532 l_message := 'Inserting line for '||ap_rec.trx_number;
533 --fnd_file.put_line(fnd_file.log , l_message);
534
535 insert into ap_invoices_interface
536 (ACCTS_PAY_CODE_COMBINATION_ID,
537 CREATED_BY,
538 CREATION_DATE,
539 DESCRIPTION,
540 DOC_CATEGORY_CODE,
541 GL_DATE,
542 INVOICE_AMOUNT,
543 INVOICE_CURRENCY_CODE,
544 INVOICE_DATE,
545 INVOICE_ID,
546 INVOICE_NUM,
547 INVOICE_TYPE_LOOKUP_CODE,
548 LAST_UPDATED_BY,
549 LAST_UPDATE_DATE,
550 LAST_UPDATE_LOGIN,
551 ORG_ID,
552 SOURCE,
553 STATUS,
554 TERMS_ID,
555 VENDOR_ID,
556 VENDOR_SITE_ID,
557 PAY_GROUP_LOOKUP_CODE,
558 EXCHANGE_RATE,
559 EXCHANGE_RATE_TYPE,
560 EXCHANGE_DATE,
561 INVOICE_RECEIVED_DATE) -- bug6847252
562 values
563 (ap_rec.rec_or_liab_ccid, -- ACCTS_PAY_CODE_COMBINATION_ID
564 p_user_id, -- CREATED_BY
565 p_sysdate, -- CREATION_DATE
566 '', -- DESCRIPTION
567 ap_rec.doc_category_code, -- DOC_CATEGORY_CODE
568 p_sysdate, -- GL_DATE
569 round(ap_rec.amount,2), -- INVOICE_AMOUNT
570 nvl(ap_rec.currency_code,
571 p_currency_code), -- INVOICE_CURRENCY_CODE
572 p_sysdate, -- INVOICE_DATE
573 l_invoice_id, -- INVOICE_ID
574 ap_rec.trx_number, -- INVOICE_NUM
575 ap_rec.invoice_type_lookup_code, -- INVOICE_TYPE_LOOKUP_CODE
576 p_user_id, -- LAST_UPDATED_BY
577 p_sysdate, -- LAST_UPDATE_DATE
578 p_user_id, -- LAST_UPDATE_LOGIN
579 p_org_id, -- ORG_ID
580 l_ap_source, -- SOURCE
581 '', -- STATUS
582 l_term_id, -- TERMS_ID
583 ap_rec.stp_id, -- VENDOR_ID
584 ap_rec.site_id, -- VENDOR_SITE_ID
585 l_pay_group,
586 ap_rec.exchange_rate,
587 ap_rec.exchange_rate_type,
588 ap_rec.exchange_date,
589 p_sysdate); -- bug6847252
590
591 l_message := 'Inserting distribution for '||ap_rec.trx_number;
592 --fnd_file.put_line(fnd_file.log , l_message);
593
594 -- Line number --
595 insert into ap_invoice_lines_interface
596 (ACCOUNTING_DATE,
597 AMOUNT,
598 CREATED_BY,
599 CREATION_DATE,
600 DESCRIPTION,
601 DIST_CODE_COMBINATION_ID,
602 INVOICE_ID,
603 INVOICE_LINE_ID,
604 ITEM_DESCRIPTION,
605 LAST_UPDATED_BY,
606 LAST_UPDATE_DATE,
607 LAST_UPDATE_LOGIN,
608 LINE_NUMBER,
609 LINE_TYPE_LOOKUP_CODE,
610 ORG_ID)
611 values
612 (p_sysdate, -- ACCOUNTING_DATE
613 round(ap_rec.amount,2), -- AMOUNT
614 p_user_id, -- CREATED_BY
615 p_sysdate, -- CREATION_DATE
616 '', -- DESCRIPTION
617 ap_rec.technical_ccid, -- DIST_CODE_COMBINATION_ID
618 l_invoice_id, -- INVOICE_ID
619 ap_invoice_lines_interface_s.nextval,
620 -- INVOICE_LINE_ID
621 ap_rec.description, -- ITEM_DESCRIPTION
622 p_user_id, -- LAST_UPDATED_BY
623 p_sysdate, -- LAST_UPDATE_DATE
624 p_login_id, -- LAST_UPDATE_LOGIN
625 1, -- LINE_NUMBER
626 'ITEM', -- LINE_TYPE_LOOKUP_CODE
627 p_org_id -- ORG_ID
628 );
629 END LOOP;
630
631 EXCEPTION
632 WHEN NO_DATA_FOUND
633 THEN l_message := substr(sqlerrm,1,120)||'No data found for AP Interfaces';
634 --fnd_file.put_line(fnd_file.log , l_message);
635 UPDATE igi_stp_batches
636 SET batch_status = 'APFAILED'
637 WHERE batch_id in
638 (select batch_id
639 from igi_stp_control
640 where control_id = p_net_batch_id);
641 commit;
642
643 --bug 3199481 fnd logging changes: sdixit
644 --not setting seeded message as hardcoded message is being passed
645
646 IF ( l_excep_level >= l_debug_level ) THEN
647
648 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
649 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
650 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
651 FND_LOG.MESSAGE ( l_excep_level,'igi.pls.igistpdb.IGIRSTPT.Create_Ap_interface.msg1',TRUE);
652 END IF;
653 raise_application_error(-20000, 'Procedure Create_AP_Interface failed '||SQLERRM);
654 WHEN OTHERS
655 THEN l_message := substr(sqlerrm,1,120)
656 ||l_message;
657 --fnd_file.put_line(fnd_file.log , l_message);
658
659 UPDATE igi_stp_batches
660 SET batch_status = 'APFAILED'
661 WHERE batch_id in
662 (select batch_id
663 from igi_stp_control
664 where control_id = p_net_batch_id);
665
666 commit;
667
668 --bug 3199481 fnd logging changes: sdixit
669 --not setting seeded message as hardcoded message is being passed
670
671 IF ( l_unexp_level >= l_debug_level ) THEN
672
673 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
674 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
675 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
676 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Create_Ap_Interface.msg2',TRUE);
677 END IF;
678 raise_application_error(-20000, 'Procedure Create_AP_Interface failed '||SQLERRM);
679
680 END Create_Ap_Interface;
681
682
683 -- --------------------------------------------------------------------------
684 --
685 -- Populate_Interfaces
686 --
687 -- --------------------------------------------------------------------------
688 PROCEDURE Populate_Interfaces (p_net_batch_id in number,p_org_id in number
689 ) IS
690
691
692 p_sysdate date;
693 --p_org_id NUMBER;
694 p_set_of_books_id NUMBER;
695 p_ledger_name varchar2(50);
696 p_user_id NUMBER;
697 p_login_id NUMBER;
698 p_currency_code VARCHAR2(15);
699
700
701
702
703 l_flag varchar2(1) := 'N';
704
705 BEGIN
706
707
708 p_sysdate := trunc(sysdate);
709 /* p_org_id := fnd_profile.value('ORG_ID');
710 p_set_of_books_id := fnd_profile.value('SET_OF_BOOKS_ID');
711 p_user_id := fnd_profile.value('USER_ID');
712 p_login_id := fnd_profile.value('LOGIN_ID'); */
713
714
715
716
717
718 mo_utils.get_ledger_info(p_org_id,p_set_of_books_id,p_ledger_name);
719 p_user_id := fnd_profile.value('USER_ID');
720 p_login_id := fnd_profile.value('LOGIN_ID');
721
722
723
724 IF l_debug = 'TRUE'
725 THEN
726 l_message := 'The profile options are : org_id '||to_char(p_org_id)||
727 ' , set_of_books_id '||to_char(p_set_of_books_id)||
728 ' , user_id '||to_char(p_user_id)||
729 ' , login_id '||to_char(p_login_id)||
730 ' , sysdate '||to_char(p_sysdate,'DD-MON-YYYY');
731 fnd_file.put_line(fnd_file.log , l_message);
732 l_variable := 'p_org_id';
733 l_value := to_char(p_org_id);
734 END IF;
735
736 l_flag := 'Y';
737 select currency_code
738 into p_currency_code
739 from gl_ledgers_public_v
740 where ledger_id = p_set_of_books_id;
741
742 l_flag := 'N';
743 IF l_debug = 'TRUE'
744 THEN
745 l_message := 'The profile currency_code is : '||p_currency_code;
746 fnd_file.put_line(fnd_file.log , l_message);
747 l_variable := 'p_currency_code';
748 l_value := p_currency_code;
749 END IF;
750
751
752 --------------------
753 -- AP Application --
754 --------------------
755 --fnd_file.put_line(fnd_file.log , '** AP importation **');
756
757 Create_Ap_Interface(p_net_batch_id,
758 p_set_of_books_id,
759 p_org_id,
760 p_user_id,
761 p_login_id,
762 p_sysdate,
763 p_currency_code);
764 --fnd_file.put_line(fnd_file.log , ' ');
765
766 --------------------
767 -- AR Application --
768 --------------------
769
770 --fnd_file.put_line(fnd_file.log , '** AR importation **');
771
772 Create_Ra_Interface(p_net_batch_id,
773 p_set_of_books_id,
774 p_org_id,
775 p_user_id,
776 p_login_id,
777 p_sysdate,
778 p_currency_code);
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 if l_flag = 'Y' then
782 UPDATE igi_stp_batches
783 SET batch_status = 'APFAILED'
784 WHERE batch_id in (select batch_id
785 from igi_stp_control
786 where control_id = p_net_batch_id);
787 commit;
788 --bug 3199481 fnd logging changes: sdixit
789 --standard way to handle when-others as per FND logging guidelines
790 --not setting seeded message as hardcoded message is being passed
791 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
792 --retcode := 2;
793 --errbuf := Fnd_message.get;
794
795 IF ( l_excep_level >= l_debug_level ) THEN
796
797 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
798 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
799 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
800 FND_LOG.MESSAGE ( l_excep_level,'igi.plsql.igistpdb.IGIRSTPT.Populate_Interface.msg1',TRUE);
801 END IF;
802 raise_application_error(-20000, 'Procedure Populate_Interfaces failed '||SQLERRM);
803 else
804 --bug 3199481 fnd logging changes: sdixit
805 --standard way to handle when-others as per FND logging guidelines
806 --not setting seeded message as hardcoded message is being passed
807 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
808 --retcode := 2;
809 --errbuf := Fnd_message.get;
810
811 IF ( l_unexp_level >= l_debug_level ) THEN
812
813 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
814 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
815 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
816 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Populate_Interface.msg2',TRUE);
817 END IF;
818 raise;
819 end if;
820 WHEN OTHERS THEN
821 if l_flag = 'Y' then
822 UPDATE igi_stp_batches
823 SET batch_status = 'APFAILED'
824 WHERE batch_id in (select batch_id
825 from igi_stp_control
826 where control_id = p_net_batch_id);
827 commit;
828 --bug 3199481 fnd logging changes: sdixit
829 --standard way to handle when-others as per FND logging guidelines
830 --not setting seeded message as hardcoded message is being passed
831 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
832 --retcode := 2;
833 --errbuf := Fnd_message.get;
834
835 IF ( l_unexp_level >= l_debug_level ) THEN
836
837 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
838 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
839 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
840 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Populate_Interfaces.msg3',TRUE);
841 END IF;
842 raise_application_error(-20000, 'Procedure Populate_Interfaces failed '||SQLERRM);
843 else
844 raise;
845 --bug 3199481 fnd logging changes: sdixit
846 --standard way to handle when-others as per FND logging guidelines
847
848 IF ( l_unexp_level >= l_debug_level ) THEN
849
850 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
851 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
852 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
853 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Populate_Interfaces.msg4',TRUE);
854 END IF;
855 end if;
856
857 END Populate_Interfaces;
858
859
860 -- --------------------------------------------------------------------------
861 --
862 -- Initiate_Interfaces
863 --
864 -- --------------------------------------------------------------------------
865 PROCEDURE Initiate_Interfaces (p_net_batch_id in number,p_org_id number) IS
866
867
868 --shsaxena for bug 2713715
869 CURSOR Cur_trx_type
870 IS
871 SELECT netting_trx_type_id
872 FROM igi_stp_batches_all
873 WHERE batch_id in
874 (select batch_id
875 from igi_stp_control
876 where control_id =p_net_batch_id)
877 and org_id = p_org_id;
878 l_trx_type_id igi_stp_batches.netting_trx_type_id%type;
879 --shsaxena for bug 2713715
880
881
882 p_set_of_books_id number(15);
883 p_ledger_name varchar2(50);
884 --p_org_id number;
885 p_user_id number;
886 p_login_id number;
887 p_sysdate date;
888 p_currency_code varchar2(15);
889 l_chart_of_accounts_id number(15);
890
891 l_ap_source varchar2(25);
892
893 p_ar_import_request_id number;
894 p_ap_import_request_id number;
895 l_ar_wait_for_request boolean;
896 l_ap_wait_for_request boolean;
897 l_ar_get_request_status boolean;
898 l_ap_get_request_status boolean;
899 l_ap_phase varchar2(30);
900 l_ar_phase varchar2(30);
901 l_ap_status varchar2(30);
902 l_ar_status varchar2(30);
903 l_ap_dev_phase varchar2(30);
904 l_ar_dev_phase varchar2(30);
905 l_ap_dev_status varchar2(30);
906 l_ar_dev_status varchar2(240);
907 l_ap_message varchar2(240);
908 l_ar_message varchar2(240);
909
910 l_batch_source_name VARCHAR2(50);
911 l_interface_context varchar2(80);
912 l_payables_source_name varchar2(80);
913 l_batch_source_id number;
914 l_pay_group varchar2(25);
915 l_flag varchar2(1) := 'P';
916
917 BEGIN
918 l_batch_source_name := fnd_profile.value('IGI_STP_RECEIVABLES_BATCH');
919 --l_batch_source_name := 'BR Automatic Numbering';
920 l_payables_source_name :=fnd_profile.value('IGI_STP_PAYABLES_SOURCE');
921 --l_payables_source_name := 'INVOICE GATEWAY';
922 l_interface_context :=fnd_profile.value('IGI_STP_INTERFACE_CONTEXT');
923 --l_interface_context := 'STP NETTING';
924 l_pay_group := fnd_profile.value('IGI_STP_PAYGROUP');
925 --l_pay_group := 'Standard';
926
927 IF l_pay_group is null
928 THEN
929
930 l_pay_group := 'Standard';
931
932 END IF;
933
934 --p_org_id := fnd_profile.value('ORG_ID');
935 IF l_debug = 'TRUE'
936 THEN
937 l_message := 'ORG_ID : '||to_char(p_org_id);
938 fnd_file.put_line(fnd_file.log , l_message);
939 l_variable := 'p_org_id';
940 l_value := to_char(p_org_id);
941 END IF;
942
943 --p_set_of_books_id := fnd_profile.value('GL_SET_OF_BKS_ID');
944
945 mo_utils.get_ledger_info(p_org_id,p_set_of_books_id,p_ledger_name);
946 IF l_debug = 'TRUE'
947 THEN
948 l_message := 'GL_SET_OF_BKS_ID : '||to_char(p_set_of_books_id);
949 fnd_file.put_line(fnd_file.log , l_message);
950 l_variable := 'p_set_of_books_id';
951 l_value := to_char(p_set_of_books_id);
952 END IF;
953
954 select chart_of_accounts_id
955 into l_chart_of_accounts_id
956 from gl_ledgers_public_v
957 where ledger_id = p_set_of_books_id;
958 IF l_debug = 'TRUE'
959 THEN
960 l_message := 'CHART_OF_ACCOUNTS_ID : '||to_char(l_chart_of_accounts_id);
961 fnd_file.put_line(fnd_file.log , l_message);
962 l_variable := 'l_chart_of_accounts_id';
963 l_value := to_char(l_chart_of_accounts_id);
964 END IF;
965
966 p_user_id := fnd_profile.value('USER_ID');
967 IF l_debug = 'TRUE'
968 THEN
969 l_message := 'USER_ID : '||to_char(p_user_id);
970 fnd_file.put_line(fnd_file.log , l_message);
971 l_variable := 'p_user_id';
972 l_value := to_char(p_user_id);
973 END IF;
974
975 p_login_id := fnd_profile.value('LOGIN_ID');
976 IF l_debug = 'TRUE'
977 THEN
978 l_message := 'LOGIN_ID : '||to_char(p_login_id);
979 fnd_file.put_line(fnd_file.log , l_message);
980 l_variable := 'p_login_id';
981 l_value := to_char(p_login_id);
982 END IF;
983
984 p_sysdate := sysdate;
985
986 select currency_code
987 into p_currency_code
988 from gl_ledgers_public_v
989 where ledger_id = p_set_of_books_id;
990 IF l_debug = 'TRUE'
991 THEN
992 l_message := 'CURRENCY_CODE : '||p_currency_code;
993 fnd_file.put_line(fnd_file.log , l_message);
994 l_variable := 'p_currency_code';
995 l_value := p_currency_code;
996 END IF;
997
998
999 --------------------
1000 -- AP Application --
1001 --------------------
1002
1003 --fnd_file.put_line(fnd_file.log , '** AP importation **');
1004 --fnd_file.put_line(fnd_file.log , l_message);
1005
1006 select lookup_code
1007 into l_ap_source
1008 -- from IGI_AP_PO_LOOKUP_CODES_V
1009 from AP_LOOKUP_CODES
1010 where lookup_type = 'SOURCE'
1011 and lookup_code = p_payables_batch_source;
1012 IF l_debug = 'TRUE'
1013 THEN
1014 l_message := 'AP source : '||l_ap_source;
1015 fnd_file.put_line(fnd_file.log , l_message);
1016 l_variable := 'l_ap_source';
1017 l_value := l_ap_source;
1018 END IF;
1019
1020
1021 update ap_expense_report_headers
1022 set vouchno = 0
1023 where vouchno in
1024 (select batch_id
1025 from igi_stp_control
1026 where control_id
1027 = p_net_batch_id);
1028
1029 --shsaxena for bug 2713715
1030 OPEN Cur_trx_type;
1031 FETCH Cur_trx_type into l_trx_type_id;
1032 CLOSE Cur_trx_type;
1033 IF l_trx_type_id IN (3,4,5,6)
1034 THEN
1035 --shsaxena for bug 2713715
1036
1037 fnd_file.put_line(fnd_file.log , 'AP request submitted');
1038 p_ap_import_request_id := fnd_request.submit_request
1039 ('SQLAP',
1040 'APXIIMPT',
1041 NULL,
1042 NULL,
1043 FALSE,
1044 p_org_id, --ORG_ID
1045 l_ap_source, -- Netting Source --
1046 '', -- Group id : batch name --
1047 '', -- Invoice batch name --
1048 '', -- No hold --
1049 '', -- Hold reason --
1050 to_char(p_sysdate,'YYYY/MM/DD HH24:MI:SS'), -- GL date --
1051 'N', -- No purge --
1052 'N', -- Trace switch --
1053 'N', -- Debug switch --
1054 'N', -- Summary flag --
1055 1000, -- Commit batch size --
1056 p_user_id, -- User id --
1057 p_login_id); -- Login id --
1058
1059 commit;
1060 --fnd_file.put_line(fnd_file.log , 'AP import request id is ...'||p_ap_import_request_id);
1061 IF p_ap_import_request_id = 0
1062 THEN
1063 ROLLBACK;
1064 --fnd_file.put_line(fnd_file.log , 'Submission failed');
1065
1066 UPDATE igi_stp_batches
1067 SET batch_status = 'APFAILED'
1068 WHERE batch_id in
1069 (select batch_id
1070 from igi_stp_control
1071 where control_id
1072 = p_net_batch_id);
1073 COMMIT;
1074
1075 --bug 3199481 fnd logging changes: sdixit
1076 --standard way to handle when-others as per FND logging guidelines
1077 --not setting seeded message as hardcoded message is being passed
1078 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1079 --retcode := 2;
1080 --errbuf := Fnd_message.get;
1081
1082 IF ( l_unexp_level >= l_debug_level ) THEN
1083
1084 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1085 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1086 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1087 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Interface_Batches',TRUE);
1088 END IF;
1089 raise_application_error(-20003, 'Invoice Import failed for the batch :'||l_batch_source_name);
1090
1091 ELSE
1092 l_ap_wait_for_request :=
1093 fnd_concurrent.wait_for_request (p_ap_import_request_id,
1094 60,
1095 0,
1096 l_ap_phase,
1097 l_ap_status,
1098 l_ap_dev_phase,
1099 l_ap_dev_status,
1100 l_ap_message);
1101
1102 IF l_debug = 'TRUE'
1103 THEN
1104 l_message := 'Wait for AP request with phase '||l_ap_phase||', status '||l_ap_status||', dev_phase '||l_ap_dev_phase||', dev_status '||l_ap_dev_status||', message '||l_ap_message;
1105 fnd_file.put_line(fnd_file.log , l_message);
1106 END IF;
1107
1108 l_ap_get_request_status :=
1109 fnd_concurrent.get_request_status (p_ap_import_request_id,
1110 'SQLAP',
1111 -- Bug 1335318
1112 -- 'APXXTR',
1113 'APXIIMPT',
1114 --
1115 l_ap_phase,
1116 l_ap_status,
1117 l_ap_dev_phase,
1118 l_ap_dev_status,
1119 l_ap_message);
1120
1121 IF l_debug = 'TRUE'
1122 THEN
1123 l_message := 'Get_request_status with phase '||l_ap_phase||', status '||l_ap_status||', dev_phase '||l_ap_dev_phase||', dev_status '||l_ap_dev_status||', message '||l_ap_message;
1124 fnd_file.put_line(fnd_file.log , l_message);
1125 END IF;
1126
1127
1128 -- Bug 1335318
1129
1130 /* commented the following code for 2713715 by shsaxena
1131 -- if l_ap_dev_phase = 'COMPLETE'
1132 -- then
1133 -- if l_ap_dev_status = 'NORMAL'
1134 -- then
1135 --
1136 -- UPDATE igi_stp_batches
1137 -- SET batch_status = 'COMPLETE'
1138 -- WHERE batch_id in
1139 -- (select batch_id
1140 -- from igi_stp_control
1141 -- where control_id = p_net_batch_id);
1142 --
1143 -- commit;
1144 -- --fnd_file.put_line(fnd_file.log , 'Submission succeded '||to_char(p_ap_import_request_id));
1145 -- end if;
1146 -- end if;
1147 -- commented the following code for 2713715 by shsaxena */
1148
1149 END IF;
1150 --fnd_file.put_line(fnd_file.log , ' ');
1151
1152 -- Submit AP Approval
1153
1154 --mo_global.set_policy_context('S',p_org_id);
1155
1156 --fnd_request.set_org_id(mo_global.get_current_org_id);
1157
1158 p_ap_import_request_id := fnd_request.submit_request
1159 ('SQLAP',
1160 'APPRVL',
1161 NULL,
1162 NULL,
1163 FALSE,
1164 P_ORG_ID, --ORG_ID
1165 'All', -- MATCH OPTION --
1166 '', -- Group id : batch name --
1167 '', -- START_INVOICE_DATE --
1168 '', -- End invoice date --
1169 '', -- VENDOR_ID --
1170 l_pay_group, -- PAY GROUP --
1171 null, --INVOICE ID--
1172 null, --ENTERED BY USER ID--
1173 p_set_of_books_id, --LEDGER ID--
1174 'N',
1175 1000);
1176
1177 commit;
1178 l_flag := 'R';
1179
1180 END IF; -- shsaxena for bug 2713715
1181
1182
1183 --------------------
1184 -- AR Application --
1185 --------------------
1186
1187 --fnd_file.put_line(fnd_file.log , '** AR importation **');
1188 --fnd_file.put_line(fnd_file.log , l_message);
1189
1190
1191 IF l_trx_type_id IN (4,6) -- shsaxena for bug 2713715
1192 THEN
1193
1194 SELECT batch_source_id
1195 INTO l_batch_source_id
1196 FROM ra_batch_sources_all
1197 WHERE name = l_batch_source_name
1198 and org_id = p_org_id;
1199
1200 IF l_debug = 'TRUE'
1201 THEN
1202 l_message := 'batch source name : '||l_batch_source_name;
1203 fnd_file.put_line(fnd_file.log , l_message);
1204 l_variable := 'l_batch_source_id';
1205 l_value := to_char(l_batch_source_id);
1206 END IF;
1207
1208 p_ar_import_request_id := fnd_request.submit_request
1209 ( 'AR'
1210 , 'RAXMTR' -- AutoInvoice Master Program
1211 , NULL
1212 , NULL
1213 , FALSE
1214 , 1 -- 10 Number of Instances
1215 ,P_ORG_ID --ORG_ID
1216 , l_batch_source_id -- 20 Batch Source Id
1217 , l_batch_source_name -- 30 Batch Source Name
1218 , p_sysdate -- 40 Default Date
1219 , NULL -- 50 Transaction Flexfield
1220 , NULL -- 60 Transaction Type
1221 , NULL -- 70 (Low) Bill To Customer Numbe
1222 , NULL -- 80 (High) Bill To Customer Numb
1223 , NULL -- 90 (Low) Bill To Customer Name
1224 , NULL --100 (High) Bill To Customer Name
1225 , NULL --110 (Low) GL Date
1226 , NULL --120 (High) GL Date
1227 , NULL --130 (Low) Ship Date
1228 , NULL --140 (High) Ship Date
1229 , NULL --150 (Low) Transaction Number
1230 , NULL --160 (High) Transaction Number
1231 , NULL --170 (Low) Sales Order Number
1232 , NULL --180 (High) Sales Order Number
1233 , NULL --190 (Low) Invoice Date
1234 , NULL --200 (High) Invoice Date
1235 , NULL --210 (Low) Ship To Customer Numbe
1236 , NULL --220 (High) Ship To Customer Numb
1237 , NULL --230 (Low) Ship To Customer Name
1238 , NULL --240 (High) Ship To Customer Name
1239 , 'Y' --250 Base Due Date on Trx Date
1240 , NULL --260 Due Date Adjustment Days
1241 -- , l_org_id --270 Ord_id --shsaxena bug 2964361
1242 );
1243
1244 commit;
1245 --fnd_file.put_line(fnd_file.log , 'AR import request id is ...'||p_ar_import_request_id);
1246 IF p_ar_import_request_id = 0
1247 THEN
1248 ROLLBACK;
1249
1250 UPDATE igi_stp_batches
1251 SET batch_status = 'ARFAILED'
1252 WHERE batch_id in
1253 (select batch_id
1254 from igi_stp_control
1255 where control_id = p_net_batch_id);
1256
1257 COMMIT;
1258
1259 --bug 3199481 fnd logging changes: sdixit
1260 --standard way to handle when-others as per FND logging guidelines
1261 --not setting seeded message as hardcoded message is being passed
1262 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1263 --retcode := 2;
1264 --errbuf := Fnd_message.get;
1265
1266 IF ( l_unexp_level >= l_debug_level ) THEN
1267
1268 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1269 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1270 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1271 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Initiate_Interfaces',TRUE);
1272 END IF;
1273 raise_application_error(-20002, ' Auto Invoice not submitted : batch source '||to_char(l_batch_source_id)||' is not found in RA_BATCH_SOURCES');
1274 raise_application_error(-20000, 'Procedure IGIRSTPN.INITIATE_INTERFACES failed '||SQLERRM);
1275
1276 --fnd_file.put_line(fnd_file.log , 'Submission failed');
1277 ELSE
1278 l_ar_wait_for_request :=
1279 fnd_concurrent.wait_for_request(p_ar_import_request_id,
1280 60,
1281 0,
1282 l_ar_phase,
1283 l_ar_status,
1284 l_ar_dev_phase,
1285 l_ar_dev_status,
1286 l_ar_message);
1287
1288
1289 IF l_debug = 'TRUE'
1290 THEN
1291 l_message := 'Wait for AR request with phase '||l_ar_phase||', status '||l_ar_status||', dev_phase '||l_ar_dev_phase||', dev_status '||l_ar_dev_status||', message '||l_ar_message;
1292 fnd_file.put_line(fnd_file.log , l_message);
1293 END IF;
1294 l_ar_get_request_status :=
1295 fnd_concurrent.get_request_status(p_ar_import_request_id,
1296 'SQLAP',
1297 -- Bug 1335318
1298 -- 'APXXTR',
1299 'RAXMTR',
1300 --
1301 l_ar_phase,
1302 l_ar_status,
1303 l_ar_dev_phase,
1304 l_ar_dev_status,
1305 l_ar_message);
1306 IF l_debug = 'TRUE'
1307 THEN
1308 l_message := 'Get_request_status with phase '||l_ar_phase||', status'||l_ar_status||', dev_phase '||l_ar_dev_phase||', dev_status '||l_ar_dev_status||', message '||l_ar_message;
1309 fnd_file.put_line(fnd_file.log , l_message);
1310 END IF;
1311
1312
1313 /* commented by shsaxena for bug 2713715
1314 -- if l_ar_dev_phase = 'COMPLETE'
1315 -- then
1316 -- if l_ar_dev_status = 'NORMAL'
1317 -- then
1318 --
1319 -- UPDATE igi_stp_batches
1320 -- SET batch_status = 'COMPLETE'
1321 -- WHERE batch_id in
1322 -- (select batch_id
1323 -- from igi_stp_control
1324 -- where control_id = p_net_batch_id);
1325 --
1326 -- COMMIT;
1327 -- --fnd_file.put_line(fnd_file.log , 'Submission succeded '||to_char(p_ar_import_request_id));
1328 -- end if;
1329 -- end if;
1330 -- commented by shsaxena for bug 2713715
1331 */
1332
1333 END IF;
1334 END IF; -- shsaxena for bug 2713715
1335
1336 /* Added by shsaxena for Bug 2713715 START */
1337
1338 /* IF l_trx_type_id IN (1,2)
1339 THEN
1340 IF (l_ar_dev_phase = 'COMPLETE') AND (l_ar_dev_status = 'NORMAL')
1341 AND (l_ap_dev_phase = 'COMPLETE') AND (l_ap_dev_status = 'NORMAL')
1342 THEN
1343 UPDATE igi_stp_batches
1344 SET batch_status = 'COMPLETE'
1345 WHERE batch_id in
1346 (select batch_id
1347 from igi_stp_control
1348 where control_id = p_net_batch_id);
1349
1350 commit;
1351 fnd_file.put_line (fnd_file.log , 'Submission succeded for AR --> '||to_char(p_ar_import_request_id));
1352 fnd_file.put_line (fnd_file.log , 'Submission succeded for AP --> '||to_char(p_ap_import_request_id));
1353 END IF; */
1354
1355 IF l_trx_type_id IN (3,5,6)
1356 THEN
1357 IF (l_ap_dev_phase = 'COMPLETE') AND (l_ap_dev_status = 'NORMAL')
1358 THEN
1359 UPDATE igi_stp_batches
1360 SET batch_status = 'COMPLETE'
1361 WHERE batch_id in
1362 (select batch_id
1363 from igi_stp_control
1364 where control_id = p_net_batch_id);
1365
1366 COMMIT;
1367 fnd_file.put_line (fnd_file.log , 'Submission succeded for AP --> '||to_char(p_ap_import_request_id));
1368 END IF;
1369
1370 ELSIF l_trx_type_id IN( 4,6)
1371 THEN
1372 IF ( l_ar_dev_phase = 'COMPLETE') AND (l_ar_dev_status = 'NORMAL')
1373 THEN
1374 UPDATE igi_stp_batches
1375 SET batch_status = 'COMPLETE'
1376 WHERE batch_id in
1377 (select batch_id
1378 from igi_stp_control
1379 where control_id = p_net_batch_id);
1380
1381 COMMIT;
1382 fnd_file.put_line (fnd_file.log , 'Submission succeded for AR --> '||to_char(p_ar_import_request_id));
1383 END IF;
1384
1385 END IF;
1386 /* Added by shsaxena for Bug 2713715 END */
1387
1388 EXCEPTION
1389 WHEN NO_DATA_FOUND THEN
1390 if l_flag = 'P' then
1391 UPDATE igi_stp_batches
1392 SET batch_status = 'APFAILED'
1393 WHERE batch_id in (select batch_id
1394 from igi_stp_control
1395 where control_id = p_net_batch_id);
1396 commit;
1397 --bug 3199481 fnd logging changes: sdixit
1398 --standard way to handle when-others as per FND logging guidelines
1399 --not setting seeded message as hardcoded message is being passed
1400 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1401 --retcode := 2;
1402 --errbuf := Fnd_message.get;
1403
1404 IF ( l_excep_level >= l_debug_level ) THEN
1405
1406 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1407 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1408 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1409 FND_LOG.MESSAGE ( l_excep_level,'igi.plsql.igistpdb.IGIRSTPT.Initiate_Interfaces',TRUE);
1410 END IF;
1411 raise_application_error(-20000, 'Procedure Initiate_Interfaces failed '||SQLERRM);
1412 elsif l_flag = 'R' then
1413 UPDATE igi_stp_batches
1414 SET batch_status = 'ARFAILED'
1415 WHERE batch_id in (select batch_id
1416 from igi_stp_control
1417 where control_id = p_net_batch_id);
1418 commit;
1419 --bug 3199481 fnd logging changes: sdixit
1420 --standard way to handle when-others as per FND logging guidelines
1421 --not setting seeded message as hardcoded message is being passed
1422 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1423 --retcode := 2;
1424 --errbuf := Fnd_message.get;
1425
1426 IF ( l_excep_level >= l_debug_level ) THEN
1427
1428 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1429 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1430 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1431 FND_LOG.MESSAGE ( l_excep_level,'igi.plsql.igistpdb.IGIRSTPT.Initiate_Interfaces',TRUE);
1432 END IF;
1433 raise_application_error(-20000, 'Procedure Initiate_Interfaces failed '||SQLERRM);
1434 else
1435 raise;
1436 end if;
1437 WHEN OTHERS THEN
1438 if l_flag = 'P' then
1439 UPDATE igi_stp_batches
1440 SET batch_status = 'APFAILED'
1441 WHERE batch_id in (select batch_id
1442 from igi_stp_control
1443 where control_id = p_net_batch_id);
1444 commit;
1445 --bug 3199481 fnd logging changes: sdixit
1446 --standard way to handle when-others as per FND logging guidelines
1447 --not setting seeded message as hardcoded message is being passed
1448 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1449 --retcode := 2;
1450 --errbuf := Fnd_message.get;
1451
1452 IF ( l_unexp_level >= l_debug_level ) THEN
1453
1454 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1455 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1456 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1457 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Initiate_Interfaces',TRUE);
1458 END IF;
1459 raise_application_error(-20000, 'Procedure Initiate_Interfaces failed '||SQLERRM);
1460 elsif l_flag = 'R' then
1461 UPDATE igi_stp_batches
1462 SET batch_status = 'ARFAILED'
1463 WHERE batch_id in (select batch_id
1464 from igi_stp_control
1465 where control_id = p_net_batch_id);
1466 commit;
1467 --bug 3199481 fnd logging changes: sdixit
1468 --standard way to handle when-others as per FND logging guidelines
1469 --not setting seeded message as hardcoded message is being passed
1470 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1471 --retcode := 2;
1472 --errbuf := Fnd_message.get;
1473
1474 IF ( l_unexp_level >= l_debug_level ) THEN
1475
1476 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1477 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1478 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1479 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Initiate_Interfaces',TRUE);
1480 END IF;
1481 raise_application_error(-20000, 'Procedure Initiate_Interfaces failed '||SQLERRM);
1482 else
1483 --bug 3199481 fnd logging changes: sdixit
1484 --standard way to handle when-others as per FND logging guidelines
1485 --not setting seeded message as hardcoded message is being passed
1486 --FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_USER_ERROR'); -- Seeded Message
1487 --retcode := 2;
1488 --errbuf := Fnd_message.get;
1489
1490 IF ( l_unexp_level >= l_debug_level ) THEN
1491
1492 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1493 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1494 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1495 FND_LOG.MESSAGE ( l_unexp_level,'igi.plsql.igistpdb.IGIRSTPT.Initiate_Interfaces',TRUE);
1496 END IF;
1497 raise;
1498 end if;
1499
1500 END Initiate_Interfaces;
1501
1502
1503 -- --------------------------------------------------------------------------
1504 --
1505 -- Submit_Batch
1506 --
1507 -- --------------------------------------------------------------------------
1508 PROCEDURE Submit_Batch (errbuf out NOCOPY varchar2,
1509 retcode out NOCOPY varchar2,
1510 p_net_batch_id in number,
1511 p_org_id in number) IS
1512 BEGIN
1513
1514 fnd_profile.get('IGI_DEBUG',l_debug);
1515
1516 l_message := '*** Open Interface for Netting called for batch '||p_net_batch_id||' ***';
1517 --fnd_file.put_line(fnd_file.log , l_message);
1518 --fnd_file.put_line(fnd_file.log , ' ');
1519 --fnd_file.put_line(fnd_file.log , ' ');
1520
1521 l_message := '*** Feed AP/AR interface tables ***';
1522 --fnd_file.put_line(fnd_file.log , l_message);
1523 Populate_Interfaces(p_net_batch_id,p_org_id);
1524 --fnd_file.put_line(fnd_file.log , ' ');
1525 --fnd_file.put_line(fnd_file.log , ' ');
1526
1527 l_message := '*** Execute AP/AR interfaces ***';
1528 --fnd_file.put_line(fnd_file.log , l_message);
1529 Initiate_Interfaces(p_net_batch_id,p_org_id);
1530
1531 END Submit_Batch;
1532
1533
1534 END IGIRSTPT;