[Home] [Help]
PACKAGE BODY: APPS.AR_EXCHANGE_INTERFACE_PKG
Source
1 PACKAGE BODY ar_exchange_interface_pkg as
2 /*$Header: AREXINPB.pls 120.7 2005/07/19 12:58:10 naneja noship $ */
3
4 custcount NUMBER := 0;
5 g_commit_level NUMBER := 100;
6 g_org_id NUMBER;
7 g_oper_id NUMBER;
8 l_sql_stmt varchar2(2000);
9 g_oexdblink varchar2(100);
10 init_error EXCEPTION;
11 g_bank_name varchar2(60);
12 g_branch_name varchar2(60);
13 g_inv_trxtype_name varchar2(20);
14 g_cred_trxtype_name varchar2(20);
15 g_payment_method_billme varchar2(240) ;
16 g_payment_method_credit varchar2(240) ;
17 g_payment_method_eft varchar2(240) ;
18 g_error_msg_nobankerr varchar2(255);
19 g_error_msg_nobankact varchar2(255);
20 g_error_msg_custintferr varchar2(255);
21 g_error_msg_custintfact varchar2(255);
22 g_error_msg_invintferr varchar2(255);
23 g_error_msg_invintfact varchar2(255);
24 g_is_debug_enabled varchar2(10) := 'N';
25
26
27
28 TYPE OexCustRecTyp IS RECORD (
29 operator_id NUMBER,
30 billing_customer_id NUMBER(15),
31 orig_sys_cust_ref VARCHAR2(240),
32 orig_sys_addr_ref varchar2(240),
33 bill_to_party_id number(15),
34 site_use_id number(15),
35 contact_party_id number(15),
36 creation_date DATE,
37 last_update_date DATE,
38 customer_name VARCHAR2(50),
39 party_number VARCHAR2(30),
40 address1 varchar2(240),
41 address2 varchar2(240),
42 address3 varchar2(240),
43 address4 varchar2(240),
44 city varchar2(60),
45 county varchar2(60),
46 state varchar2(60),
47 country varchar2(60),
48 postal_plus4_code varchar2(10),
49 bank_name varchar2(30),
50 account_name varchar2(80),
51 account_number varchar2(80),
52 account_currency varchar2(15),
53 account_description varchar2(240),
54 account_exp_date DATE,
55 payment_method_code varchar2(30),
56 insert_update_flag varchar2(10),
57 org_id number(15)
58 );
59
60 g_osr_cust_prefix varchar2(100) ;
61 g_osr_addr_prefix varchar2(100) ;
62
63 l_invalid_value varchar2(255);
64 l_action_reqd_msg varchar2(255);
65
66 LEVEL0 CONSTANT NUMBER := 0;
67 LEVEL2 CONSTANT NUMBER := 2;
68 LEVEL4 CONSTANT NUMBER := 4;
69 LEVEL_MIDDLE CONSTANT NUMBER := 16;
70 OUTPUT_LINE_WIDTH CONSTANT NUMBER := 40;
71
72 FUNCTION billing_cycle_end_date(
73 p_cutoff_date IN date,
74 p_last_billed_date IN date ) return date IS
75 l_cycle_end_date date;
76 BEGIN
77 l_cycle_end_date := p_last_billed_date;
78
79 while (add_months(l_cycle_end_date,1) <= p_cutoff_date) loop
80 l_cycle_end_date := add_months(l_cycle_end_date,1);
81 end loop;
82
83 return l_cycle_end_date;
84
85 END billing_cycle_end_date;
86
87 FUNCTION get_fnd_lookup(
88 p_lookup_type IN VARCHAR2,
89 p_lookup_code IN VARCHAR2 ) RETURN VARCHAR2 IS
90 CURSOR c_lkp IS
91 select meaning
92 from fnd_lookup_values
93 where lookup_type = p_lookup_type
94 and lookup_code = p_lookup_code
95 and language = userenv('LANG');
96 l_meaning varchar2(240);
97 BEGIN
98 for crec in c_lkp
99 loop
100 l_meaning := crec.meaning;
101 exit;
102 end loop;
103 return l_meaning;
104 EXCEPTION
105 WHEN OTHERS THEN
106 raise;
107 END get_fnd_lookup;
108
109 PROCEDURE print_debug(
110 p_indent IN NUMBER,
111 p_text IN VARCHAR2 ) IS
112 BEGIN
113 fnd_file.put_line( FND_FILE.LOG, RPAD(' ', (1+p_indent)*2)||p_text );
114 EXCEPTION
115 WHEN OTHERS THEN
116 null;
117 END print_debug;
118
119 /* Get new request id from sequence. For 6.1, just create on-account credit memos */
120 function get_inv_request_id RETURN NUMBER IS
121 BEGIN
122 null;
123 EXCEPTION
124 WHEN OTHERS THEN
125 raise;
126 END get_inv_request_id;
127
128 /* Get related invoice number for a credit memo to be created against */
129 procedure get_related_invoice (
130 p_party_id IN number,
131 p_trans_num IN varchar2,
132 p_cm_amount IN number,
133 p_reg_cm_flag OUT NOCOPY varchar2,
134 p_related_inv_reqid OUT NOCOPY number
135 ) IS
136 BEGIN
137 /*
138 Logic:
139 * get orig transaction's request id
140 * get orig invoice number/trx_id from the request id
141 * if bal > cm_amount, invoice is still open. this becomes a regular cm.
142 * else this is on-acc cm.
143 */
144 null;
145 EXCEPTION
146 WHEN OTHERS THEN
147 raise;
148 END get_related_invoice;
149
150 /*
151 Transaction to insert into all interface records relating
152 to one exchange billing customer record.
153 */
154 PROCEDURE transfer_customer_record (
155 cust_rec IN OexCustRecTyp,
156 p_transfer_flag OUT NOCOPY varchar2
157 ) IS
158
159 TYPE OexContactRecTyp is RECORD (
160 person_title varchar2(255),
161 person_first_name varchar2(255),
162 person_last_name varchar2(255),
163 contact_point_id NUMBER(15),
164 contact_point_type varchar2(255),
165 phone_line_type varchar2(255),
166 phone_area_code varchar2(255),
167 phone_number varchar2(255),
168 phone_extension varchar2(255),
169 email_address varchar2(2000)
170 );
171
172 l_sql_is_dup_pay_meth varchar2(4000);
173 l_set_oex_cust_status varchar2(4000);
174 l_set_oex_cust_errstatus varchar2(4000);
175 l_oex_contacts varchar2(4000) ;
176
177
178 TYPE OexContactCurTyp IS REF CURSOR;
179 contrec_cv OexContactCurTyp; -- cursor variable
180 l_cont_rec OexContactRecTyp; -- store fetched cv record into local record
181 -- to pass as parameter to other procedures.
182
183 l_cust_upd_ins_flag varchar2(10) := 'I';
184 -- if customer/address(reference) exists in AR,
185 -- 'U'(update), else 'I'(insert)
186 l_payment_method_name varchar2(240);
187 l_error_code varchar2(30);
188 l_error_msg varchar2(255);
189 l_contact_ref varchar2(255);
190 l_telephone_ref varchar2(255);
191 l_telephone_type varchar2(30);
192 l_account_number varchar2(30);
193 l_a_null char(1); -- represents NULL in the dynamic statement
194 l_site_use_code varchar2(100); -- site use code
195 l_primary_su_flag varchar2(100); -- primary site use flag
196
197 l_sql_customers_interface varchar2(4000) := '
198 INSERT INTO ra_customers_interface_all (
199 org_id, orig_system_customer_ref, orig_system_address_ref, insert_update_flag,
200 customer_name, customer_number, address1, address2, address3, address4,
201 city, county, state, country, postal_code,
202 customer_prospect_code, customer_status, customer_type,
203 primary_site_use_flag, site_use_code,
204 created_by, creation_date, last_updated_by, last_update_date
205 )
206 SELECT
207 :1, :2, :3, :4,
208 :5, :6, :7, :8, :9,
209 :10, :11, :12, :13, :14,
210 :15, :16, :17,
211 :18, :19,
212 :20, :21, :22, :23, :24
213 FROM DUAL ';
214
215 l_sql_profiles_interface varchar2(4000) := '
216 INSERT INTO ra_customer_profiles_int_all (
217 org_id, orig_system_customer_ref, insert_update_flag,
218 customer_profile_class_name, credit_hold,
219 created_by, creation_date, last_updated_by, last_update_date
220 )
221 SELECT
222 :1, :2, :3,
223 :4, :5,
224 :6, :7, :8, :9
225 FROM DUAL';
226
227 l_sql_pay_methods_interface varchar2(4000) := '
228 INSERT INTO ra_cust_pay_method_int_all (
229 org_id, orig_system_customer_ref, orig_system_address_ref,
230 payment_method_name, start_date, primary_flag,
231 created_by, creation_date, last_updated_by, last_update_date
232 )
233 SELECT
234 :1, :2, :3,
235 :4, :5, :6,
236 :7, :8, :9, :10
237 FROM DUAL';
238
239 l_sql_banks_interface varchar2(4000) := '
240 INSERT INTO ra_customer_banks_int_all (
241 org_id, orig_system_customer_ref, orig_system_address_ref,
242 bank_account_num, bank_account_currency_code, bank_account_inactive_date,
243 bank_account_name,
244 bank_name,
245 bank_branch_name,
246 start_date, primary_flag,
247 created_by, creation_date, last_updated_by, last_update_date
248 )
249 SELECT
250 :1, :2, :3,
251 :4, :5, :6,
252 :7,
253 decode(:8, :9, :10, :11),
254 decode(:12, :13, :14, :15),
255 :16, :17, :18,
256 :19, :20, :21
257 FROM DUAL';
258
259 l_sql_eml_contacts_interface varchar2(4000) := '
260 INSERT INTO ra_contact_phones_int_all (
261 org_id, orig_system_customer_ref, orig_system_address_ref,
262 orig_system_contact_ref,
263 contact_first_name, contact_last_name, contact_title,
264 insert_update_flag, email_address,
265 created_by, creation_date, last_updated_by, last_update_date
266 )
267 SELECT
268 :1, :2, :3,
269 :4,
270 :5, :6, :7,
271 :8, :9,
272 :10, :11, :12, :13
273 FROM DUAL';
274
275 l_sql_contacts_interface varchar2(4000) := '
276 INSERT INTO ra_contact_phones_int_all (
277 org_id, orig_system_customer_ref, orig_system_address_ref,
278 orig_system_contact_ref, orig_system_telephone_ref,
279 contact_first_name, contact_last_name, contact_title,
280 insert_update_flag, telephone_type, telephone,
281 telephone_area_code, telephone_extension, email_address,
282 created_by, creation_date, last_updated_by, last_update_date
283 )
284 SELECT
285 :1, :2, :3,
286 :4, :5,
287 :6, :7, :8,
288 :9,
289 decode(:10, :11, :12, :13),
290 decode(:14, :15, :16, :17),
291 :18, :19, :20,
292 :21, :22, :23, :24
293 FROM DUAL';
294
295 TYPE PayMethodTyp IS REF CURSOR ;
296 pay_meth_cv PayMethodTyp;
297 l_existing_pm_name varchar2(30);
298 l_pm_ins_upd_flag varchar2(10) := 'N';
299
300
301 BEGIN
302
303 /* Initialize dyn.sql.stmt strings */
304 l_oex_contacts := '
305 select
306 hpcont.person_pre_name_adjunct person_title,
307 hpcont.person_first_name person_first_name,
308 hpcont.person_last_name person_last_name,
309 hcp1.contact_point_id contact_point_id,
310 hcp1.contact_point_type contact_point_type,
311 hcp1.phone_line_type phone_line_type,
312 hcp1.phone_area_code phone_area_code,
313 hcp1.phone_number phone_number,
314 hcp1.phone_extension phone_extension,
315 hcp1.email_address email_address
316 from
317 hz_parties'||g_oexdblink||' hpcont,
318 hz_contact_points'||g_oexdblink||' hcp1
319 where hpcont.party_id = :1
320 and hcp1.owner_table_id = hpcont.party_id
321 and hcp1.owner_table_name = ''HZ_PARTIES''
322 ';
323
324 l_sql_is_dup_pay_meth := '
325 SELECT rm.name
326 FROM hz_cust_accounts'||g_oexdblink||' hca,
327 ar_receipt_methods rm,
328 ra_cust_receipt_methods rcrm
329 WHERE hca.orig_system_reference = :1
330 AND rm.name = :2
331 AND rcrm.customer_id = hca.cust_account_id
332 AND rcrm.receipt_method_id = rm.receipt_method_id
333 AND sysdate <= nvl(rcrm.end_date,sysdate)
334 ';
335
336 l_set_oex_cust_status := '
337 UPDATE pom_billing_customers'||g_oexdblink||'
338 SET ar_transfer_flag = null,
339 insert_update_flag = null,
340 last_update_date = sysdate
341 WHERE billing_customer_id = :1
342 ';
343
344 l_set_oex_cust_errstatus := '
345 UPDATE pom_billing_customers'||g_oexdblink||'
346 SET ar_transfer_flag = ''E'',
347 request_id = null,
348 last_update_date = sysdate
349 WHERE billing_customer_id = :1
350 ';
351
352 custcount := custcount + 1;
353 print_debug(0,'----------------------------------------- # '||to_char(custcount)||' ----');
354 print_debug(0,'transfer_customer_interface + ');
355 print_debug(0,'org id is :'||to_char(cust_rec.org_id));
356 print_debug(0,'customer_ref is :'||cust_rec.orig_sys_cust_ref);
357 print_debug(0,'customer_name : '||cust_rec.customer_name);
358
359 /* Interface tables:
360 - customer interface
361 - profile interface
362 - bank interface
363 - payment method interface
364 - contact interface
365 */
366 BEGIN
367
368 if (nvl(cust_rec.insert_update_flag,'~') = 'I') then
369 l_primary_su_flag := 'Y';
370 l_site_use_code := 'BILL_TO';
371 else
372 l_primary_su_flag := null;
373 l_site_use_code := null;
374 end if;
375
376 print_debug(0,'Begin data transfer');
377 EXECUTE IMMEDIATE l_sql_customers_interface
378 USING
379 cust_rec.org_id,
380 cust_rec.orig_sys_cust_ref,
381 nvl(cust_rec.orig_sys_addr_ref,l_a_null),
382 cust_rec.insert_update_flag,
383 cust_rec.customer_name,
384 cust_rec.party_number,
385 nvl(cust_rec.address1,l_a_null),
386 nvl(cust_rec.address2,l_a_null),
387 nvl(cust_rec.address3,l_a_null),
388 nvl(cust_rec.address4,l_a_null),
389 nvl(cust_rec.city,l_a_null),
390 nvl(cust_rec.county,l_a_null),
391 nvl(cust_rec.state,l_a_null),
392 nvl(cust_rec.country,l_a_null),
393 nvl(cust_rec.postal_plus4_code,l_a_null),
394 'CUSTOMER',
395 'A',
396 'R',
397 nvl(l_primary_su_flag,l_a_null),
398 nvl(l_site_use_code,l_a_null),
399 -1,
400 sysdate,
401 -1,
402 sysdate ;
403 print_debug(0,'-inserted customer interface record.');
404
405
406 if (nvl(cust_rec.insert_update_flag,'~') = 'I') then
407 EXECUTE IMMEDIATE l_sql_profiles_interface
408 USING
409 cust_rec.org_id,
410 cust_rec.orig_sys_cust_ref,
411 'I',
412 'DEFAULT',
413 'N',
414 -1,
415 sysdate,
416 -1,
417 sysdate;
418 end if;
419 print_debug(0,'-inserted profile interface record.');
420
421 IF (cust_rec.payment_method_code IS NOT NULL) THEN
422
423 IF (cust_rec.payment_method_code = 'BILL_ME') THEN
424 l_payment_method_name := g_payment_method_billme;
425 ELSIF (cust_rec.payment_method_code = 'EFT') THEN
426 l_payment_method_name := g_payment_method_eft;
427 ELSE
428 l_payment_method_name := g_payment_method_credit;
429 END IF;
430
431 OPEN pay_meth_cv FOR l_sql_is_dup_pay_meth
432 USING cust_rec.orig_sys_cust_ref, l_payment_method_name;
433 FETCH pay_meth_cv INTO l_existing_pm_name;
434 IF (pay_meth_cv%NOTFOUND) THEN
435
436 -- no payment method exists for sysdate, insert one
437 l_pm_ins_upd_flag := 'I';
438 END IF;
439
440 IF (pay_meth_cv%ISOPEN) THEN
441 CLOSE pay_meth_cv;
442 END IF;
443
444 IF ( l_pm_ins_upd_flag = 'I') THEN
445 EXECUTE IMMEDIATE l_sql_pay_methods_interface
446 USING
447 cust_rec.org_id,
448 cust_rec.orig_sys_cust_ref,
449 cust_rec.orig_sys_addr_ref,
450 l_payment_method_name,
451 sysdate,
452 'N',
453 -1,
454 sysdate,
455 -1,
456 sysdate;
457 END IF;
458 END IF; -- if cust_rec.payment_method_code
459 print_debug(0,'-inserted payment method interface record.');
460
461 --
462 -- Insert bank account information only for automatic payment methods
463 -- like credit_card and eft. For bill_me (manual payment), we do not
464 -- capture account number during registration.
465 --
466 IF ( (cust_rec.payment_method_code <> 'BILL_ME') AND
467 (cust_rec.account_number IS NOT NULL) ) THEN
468
469 l_account_number := null;
470 print_debug(0,'-inserting bank interface record.');
471 EXECUTE IMMEDIATE '
472 BEGIN
473 pom_billing_util_pkg.get_util_info_w'||g_oexdblink||'(
474 i_old_info => :a,
475 i_info => :b );
476 END;
477 '
481
478 USING IN cust_rec.account_number, IN OUT l_account_number;
479
480
482 print_debug(0,'l_account_number = ['||l_account_number||']');
483 EXECUTE IMMEDIATE l_sql_banks_interface
484 USING
485 cust_rec.org_id,
486 cust_rec.orig_sys_cust_ref,
487 nvl(cust_rec.orig_sys_addr_ref,l_a_null),
488 l_account_number,
489 nvl(cust_rec.account_currency,l_a_null),
490 nvl(cust_rec.account_exp_date, l_a_null),
491 nvl(cust_rec.account_name,cust_rec.customer_name),
492 cust_rec.payment_method_code,'CREDIT_CARD',g_bank_name,cust_rec.bank_name,
493 cust_rec.payment_method_code,'CREDIT_CARD',g_branch_name,cust_rec.bank_name,
494 sysdate,
495 'N',
496 -1,
497 sysdate,
498 -1,
499 sysdate;
500 print_debug(0,'-inserted bank account interface record.');
501 END IF; -- if cust_rec.payment_method_code
502
503 print_debug(0,'-inserting contact interface record.');
504 IF (cust_rec.contact_party_id IS NOT NULL) THEN
505
506 l_contact_ref := cust_rec.orig_sys_addr_ref||'_CONT'||cust_rec.contact_party_id;
507
508 OPEN contrec_cv FOR l_oex_contacts
509 USING
510 cust_rec.contact_party_id;
511
512 LOOP
513 FETCH contrec_cv INTO l_cont_rec;
514 EXIT WHEN contrec_cv%NOTFOUND;
515
516 if (l_cont_rec.contact_point_type = 'EMAIL') then
517 print_debug(0,'- EMAIL contact .');
518
519 -- for email contact type, phone_ref, phone_number and phone_type are null.
520 EXECUTE IMMEDIATE l_sql_eml_contacts_interface
521 USING
522 cust_rec.org_id,
523 cust_rec.orig_sys_cust_ref,
524 cust_rec.orig_sys_addr_ref,
525 l_contact_ref,
526 l_cont_rec.person_first_name,
527 l_cont_rec.person_last_name,
528 l_cont_rec.person_title,
529 cust_rec.insert_update_flag,
530 l_cont_rec.email_address,
531 -1,
532 sysdate,
533 -1,
534 sysdate;
535 else
536 print_debug(0,'- PHONE/FAX contact .');
537 -- for contact types other than email.
538 -- Deliberately making decode of contact_point_type result in phone_line_type
539 -- and phone_number by using '1' and '2'.
540 -- Valid contact point types are 'FAX','PHONE'.
541 --
542 l_telephone_ref := cust_rec.orig_sys_addr_ref||'_PHONE'||l_cont_rec.contact_point_id;
543 EXECUTE IMMEDIATE l_sql_contacts_interface
544 USING
545 cust_rec.org_id,
546 cust_rec.orig_sys_cust_ref,
547 cust_rec.orig_sys_addr_ref,
548 l_contact_ref,
549 l_telephone_ref,
550 l_cont_rec.person_first_name,
551 l_cont_rec.person_last_name,
552 l_cont_rec.person_title,
553 cust_rec.insert_update_flag,
554 l_cont_rec.contact_point_type, '1','2',l_cont_rec.phone_line_type,
555 l_cont_rec.contact_point_type, '1','2',l_cont_rec.phone_number,
556 l_cont_rec.phone_area_code,
557 l_cont_rec.phone_extension,
558 l_cont_rec.email_address,
559 -1,
560 sysdate,
561 -1,
562 sysdate;
563 end if;
564 print_debug(0,'-inserted contact-telephone interface record.');
565 END LOOP;
566
567 IF (contrec_cv%ISOPEN) THEN
568 CLOSE contrec_cv;
569 END IF;
570 END IF;
571
572 -- success
573 EXECUTE IMMEDIATE l_set_oex_cust_status
574 USING
575 cust_rec.billing_customer_id;
576 p_transfer_flag := 'T';
577
578 print_debug(0,'-updated transfer flag .');
579
580 print_debug(0,'transfer_customer_interface - ');
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 -- failure
585 p_transfer_flag := 'E';
586
587 EXECUTE IMMEDIATE l_set_oex_cust_errstatus
588 USING
589 cust_rec.billing_customer_id;
590
591 print_debug(0,'Insert error: '||sqlerrm);
592
593 raise;
594 END;
595 END transfer_customer_record;
596
597
598 /*
599 Create customer record in AR for billing party in Exchange.
600 */
601
602 procedure customer_interface (
603 p_bill_to_party_id IN NUMBER default null,
604 p_bill_to_site_use_id IN NUMBER default null,
605 p_conc_request_id IN NUMBER default null,
606 x_error_code OUT NOCOPY varchar2,
607 x_error_msg OUT NOCOPY varchar2
608 ) IS
609
610 l_oex_cust varchar2(8000);
611 l_error_code varchar2(30);
612 l_error_msg varchar2(255);
613 l_transfer_flag varchar2(10); -- Indicates success/failure of customer record transfer
614 l_request_id NUMBER(15);
615
616
617 l_set_oex_cust_err varchar2(4000);
618 l_set_pom_cust_upd varchar2(4000);
619 TYPE OexCustCurTyp IS REF CURSOR;
620 oex_cust_rec_cv OexCustCurTyp; -- cursor variable
621 l_oex_cust_rec OexCustRecTyp; -- store fetched cv record into local record
622
623 l_sql_get_bank_info varchar2(4000) := '
624 SELECT bbr.bank_name, bbr.bank_branch_name
625 FROM ce_bank_branches_v bbr
626 WHERE bbr.branch_party_id = arp_global.CC_BANK_BRANCH_ID
627 ';
628
629 BEGIN
630 print_debug(0,'customer_interface +');
631
632 /* Initialize dyn.sql.stmt strings */
633 l_oex_cust := '
634 SELECT
635 pbc.operator_id operator_id,
639 pbc.bill_to_party_id bill_to_party_id,
636 pbc.billing_customer_id billing_customer_id, '||
637 ''''||g_osr_cust_prefix||''''||' ||to_char(pbc.bill_to_party_id) orig_sys_cust_ref,'||
638 ''''||g_osr_cust_prefix||''''||' ||to_char(pbc.bill_to_party_id)|| '||''''||g_osr_addr_prefix||''''||' ||to_char(pbc.bill_to_site_use_id) orig_sys_addr_ref,
640 pbc.bill_to_site_use_id site_use_id,
641 pbc.bill_to_contact_party_id contact_party_id,
642 pbc.creation_date creation_date,
643 pbc.last_update_date last_update_date,
644 substr(hp.party_name,1,50) customer_name,
645 hp.party_number party_number,
646 hloc.address1 address1,
647 hloc.address2 address2,
648 hloc.address3 address3,
649 hloc.address4 address4,
650 hloc.city city,
651 hloc.county county,
652 hloc.state state,
653 hloc.country country,
654 hloc.postal_plus4_code postal_plus4_code,
655 pbc.bank_name bank_name,
656 pbc.account_name account_name,
657 pbc.account_number account_number,
658 pbc.account_currency account_currency,
659 pbc.account_description account_description,
660 pbc.account_inactive_date account_exp_date,
661 pbc.payment_method_name payment_method_code,
662 nvl(pbc.insert_update_flag,''I'') insert_update_flag,
663 pbsp.org_id org_id
664 FROM
665 pom_billing_customers'||g_oexdblink||' pbc,
666 hz_party_site_uses'||g_oexdblink||' hpsu,
667 hz_party_sites'||g_oexdblink||' hps,
668 hz_locations'||g_oexdblink||' hloc,
669 hz_parties'||g_oexdblink||' hp,
670 pom_billing_seat_parameters'||g_oexdblink||' pbsp
671 WHERE hp.party_id = pbc.bill_to_party_id
672 AND hps.party_id = hp.party_id
673 AND hpsu.party_site_use_id = pbc.bill_to_site_use_id
674 AND hpsu.site_use_type = ''EXCHANGE_BILLING''
675 AND hpsu.party_site_id = hps.party_site_id
676 AND hps.location_id = hloc.location_id
677 AND pbsp.operator_id = pbc.operator_id
678 AND pbc.request_id = :1
679 ';
680
681 l_set_oex_cust_err := '
682 UPDATE pom_billing_customers'||g_oexdblink||'
683 SET ar_transfer_flag = ''E'',
684 request_id = null,
685 last_update_date = sysdate
686 WHERE ar_transfer_flag = ''N''
687 AND request_id = :1
688 ';
689
690 l_set_pom_cust_upd := '
691 UPDATE pom_billing_customers'||g_oexdblink||'
692 SET request_id = :1,
693 ar_transfer_flag = ''N'',
694 last_update_date = sysdate
695 WHERE ( ((nvl(ar_transfer_flag,''~'') = ''N'') AND request_id is null)
696 OR (nvl(ar_transfer_flag,''~'') = ''E'')
697 )
698 ';
699
700 custcount := 0; -- running count of # of customer records imported
701
702 l_request_id := p_conc_request_id;
703
704 print_debug(0,'Updating pbc with request_id : '||to_char(l_request_id));
705 begin
706
707
708 EXECUTE IMMEDIATE l_set_pom_cust_upd
709 USING
710 l_request_id;
711
712 print_debug(0,'Rows updated: '||to_char(sql%rowcount));
713 exception
714 when others then
715 raise;
716 end;
717
718 BEGIN
719 /* get bank branch information */
720 EXECUTE IMMEDIATE l_sql_get_bank_info
721 INTO g_bank_name, g_branch_name;
722 print_debug(LEVEL0, 'CC Bank Name: '||g_bank_name);
723 print_debug(LEVEL0, 'CC Bank Branch Name: '||g_branch_name);
724
725 IF ( g_bank_name is null ) THEN
726 l_error_code := 'AR_OEX_CC_BANK_FETCH_ERR';
727 print_debug(0,' Unable to fetch bank_name,bank_branch_name for automatic payment method.');
728 END IF;
729 EXCEPTION
730 WHEN OTHERS THEN
731 print_debug(LEVEL0, 'Error from l_sql_get_bank_info'||sqlerrm);
732 END;
733
734 print_debug(0,'-----------------------------');
735 print_debug(0,'SQL for oex_cust_rec_cv is : ');
736 print_debug(0,'-----------------------------');
737
738 print_debug(0, l_oex_cust );
739
740 print_debug(0,'-----------------------------');
741 print_debug(0,'Opening cursor variable oex_cust_rec_cv ...');
742 OPEN oex_cust_rec_cv FOR l_oex_cust USING l_request_id;
743
744 print_debug(0,'Fetching data...');
745 LOOP
746
747 FETCH oex_cust_rec_cv INTO l_oex_cust_rec;
748 EXIT WHEN oex_cust_rec_cv%NOTFOUND;
749
750 BEGIN
751 l_error_code := null;
752 l_error_msg := null;
753
754 -- Transaction to transfer all related interface records for one customer.
755
756 if (mod(custcount,g_commit_level) = 0) then
757 -- set savepoint once for each batch of g_commit_level records
758 savepoint A;
759 end if;
760
761 transfer_customer_record (
762 l_oex_cust_rec ,
763 l_transfer_flag
764 );
765
766 if (mod(custcount,g_commit_level) = 0) then
767 -- commit for each batch of g_commit_level records imported
768 commit;
769 end if;
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 l_error_msg := g_error_msg_custintferr;
774 l_action_reqd_msg := g_error_msg_custintfact;
775
776 rollback to A;
777
778 ar_exchange_interface_pkg.record_error(
779 p_billing_activity_id => null,
780 p_billing_customer_id => null,
781 p_customer_name => null,
782 p_error_code => 'POM_BILL_CUST_INTF_ERR',
786 );
783 p_additional_message => l_error_msg||' '||sqlerrm,
784 p_action_required => l_action_reqd_msg,
785 p_invalid_value => l_invalid_value
787 END;
788
789 END LOOP;
790
791 IF (oex_cust_rec_cv%ISOPEN) THEN
792 print_debug(0,'Closing cursor.');
793 CLOSE oex_cust_rec_cv;
794 END IF;
795
796 commit; -- commit last batch of records
797 x_error_code := 'S';
798
799 print_debug(0,'customer_interface -');
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 /*
804 Update records with error flag. Req-id stays so we can report
805 on failed requests. Additionally we should insert failure
806 codes/messages into pom_billing_interface_errors.(tablename, pk_of_table,
807 error_code, error_msg, status) where status can be 'ERROR','CORRECTED'
808 */
809 print_debug(0,'ar_exchange_interface_pkg.customer_interface raised following exception: ');
810 l_error_code := 'AR_INTERFACE_PROGRAM_ERROR';
811 l_action_reqd_msg := g_error_msg_custintfact;
812
813 print_debug(0,'SQLERRM: '||sqlerrm);
814 print_debug(0,'PROGRAM ERROR CODE: '||l_error_code);
815 print_debug(0,'Check Exceptions page for more information and action required.');
816
817
818 --EXECUTE IMMEDIATE l_set_oex_cust_err USING l_request_id;
819
820 ar_exchange_interface_pkg.record_error(
821 p_billing_activity_id => null,
822 p_billing_customer_id => null,
823 p_customer_name => null,
824 p_error_code => l_error_code,
825 p_additional_message => sqlerrm,
826 p_action_required => l_action_reqd_msg,
827 p_invalid_value => l_invalid_value
828 );
829 x_error_code := l_error_code;
830
831 IF (oex_cust_rec_cv%ISOPEN) THEN
832 CLOSE oex_cust_rec_cv;
833 END IF;
834
835 END customer_interface;
836
837 /* Invoice interface */
838 procedure invoice_interface (
839 p_cutoff_date IN date default null ,
840 p_customer_name IN VARCHAR2 default null ,
841 p_conc_request_id IN NUMBER default null,
842 x_error_code OUT NOCOPY varchar2,
843 x_error_msg OUT NOCOPY varchar2
844 ) IS
845
846
847 TYPE BillActivityCurTyp IS REF CURSOR;
848 bill_act_cv BillActivityCurTyp; -- cursor variable
849
850 l_sql_billing_activities varchar2(4000) := '
851 SELECT
852 pba.bill_to_party_id bill_to_party_id,
853 pba.transaction_type trans_type,
854 rtrim(pbat.billing_activity_type_name) activity_type_name,
855 pbat.billing_activity_type_id activity_type_id,
856 pba.transaction_num trans_num,
857 sum(pba.total_fee) total_fee
858 FROM
859 pom_billing_activities'||g_oexdblink||' pba,
860 pom_billing_activity_types_tl'||g_oexdblink||' pbat
861 WHERE pba.request_id = :1
862 AND pbat.billing_activity_type_id = pba.billing_activity_type_id
863 AND pbat.language_code = ''US''
864 GROUP BY
865 pba.bill_to_party_id,
866 pba.transaction_type,
867 rtrim(pbat.billing_activity_type_name),
868 pbat.billing_activity_type_id,
869 pba.transaction_num
870 ORDER BY
871 pba.bill_to_party_id,
872 pba.transaction_type,
873 pbat.billing_activity_type_id,
874 pba.transaction_num
875 ';
876 TYPE BillActivityRec IS RECORD (
877 bill_to_party_id number(15),
878 trans_type varchar2(80),
879 activity_type_name varchar2(255),
880 activity_type_id number(15),
881 trans_num varchar2(80),
882 total_fee NUMBER
883 );
884 ba_rec BillActivityRec;
885
886
887 TYPE CustDetailsCurTyp IS REF CURSOR;
888 cust_details_cv CustDetailsCurTyp; -- cursor variable
889 l_sql_cust_details varchar2(4000);
890
891 TYPE CustDetailsRec IS RECORD (
892 customer_name varchar2(50),
893 billing_customer_id NUMBER(15),
894 bill_to_site_use_id NUMBER(15),
895 payment_method_code varchar2(30),
896 account_number varchar2(80),
897 org_id NUMBER(15),
898 set_of_books_id NUMBER(15),
899 orig_system_prefix varchar2(100),
900 cust_trxtype_name varchar2(20),
901 payment_term_name varchar2(100),
902 batch_source_name varchar2(100),
903 interface_line_context varchar2(240),
904 orig_sys_cust_ref varchar2(240),
905 orig_sys_addr_ref varchar2(240)
906 );
907 custrec CustDetailsRec;
908
909 TYPE BankAccountTyp IS REF CURSOR ;
910 bank_acc_cv BankAccountTyp;
911 TYPE ra_intf_line_rectype is RECORD (
912 bill_to_party_id number(15),
913 activity_type_name varchar2(255) := null,
914 activity_type_id number(15),
915 trans_type varchar2(80) := null,
916 trans_num varchar2(80),
917 total_fee number,
918 related_inv_reqid number(15),
919 reqid number(15),
920 quantity number(15)
921 );
922
923 TYPE invtabtype IS TABLE OF ra_intf_line_rectype index by binary_integer;
924 TYPE cmtabtype IS TABLE OF ra_intf_line_rectype index by binary_integer;
925 TYPE oacmtabtype IS TABLE OF ra_intf_line_rectype index by binary_integer;
926
927 invtab invtabtype;
928 cmtab cmtabtype;
929 oacmtab oacmtabtype;
930
931 l_invline_index number := 0;
932 l_cm_index number := 0;
933 l_oacmline_index number := 0;
934 l_reg_cm_flag varchar2(10) := 'N';
935 l_related_inv_reqid number;
936
940 l_tmp_request_id number(15);
937 l_inv_prev_acttypeid number(15) := -1;
938 l_cm_prev_acttypeid number(15) := -1;
939 l_oacm_prev_acttypeid number(15) := -1;
941 l_cm_request_id number;
942
943 -- Dynamic sql statement holders
944 l_sql_bank_acc varchar2(4000) ;
945 l_sql_invoice_interface varchar2(4000) ;
946 l_sql_get_pom_param varchar2(4000) ;
947 l_temp_sql varchar2(4000);
948
949 -- Local variables
950 l_request_id number(15);
951 l_error_code varchar2(30);
952 l_error_msg varchar2(255);
953 l_cutoff_date date ;
954 l_uom_code varchar2(240) := 'Ea';
955 l_gl_date DATE := sysdate;
956 l_cycle_start_date DATE ;
957 l_cycle_end_date DATE ;
958 l_bank_account_id NUMBER(15); -- from pom_billing_customers
959 l_billing_period varchar2(30);
960 l_party_id NUMBER(15) := null;
961 l_prev_party_id NUMBER(15) := -1;
962 l_skip_party varchar2(5) := 'N'; -- If error occurs, skip all records for this party
963 l_a_null char(1); -- represents NULL in the dynamic statement
964 l_exit_prog varchar2(1) := 'N';
965 l_default_currency varchar2(10);
966 l_rounded_amount number;
967 l_ba_row_num number(15) := 1;
968 l_payment_method_name varchar2(240);
969 l_cc_number varchar2(30); /* Bank account num */
970
971 -- User-defined exceptions
972 no_default_currency EXCEPTION;
973 interface_program_error EXCEPTION;
974
975 -- Cust details (for new party row in cursor)
976 l_bill_to_party_id NUMBER(15) := -99;
977 l_customer_name varchar2(255);
978 l_billing_customer_id NUMBER(15) := -1;
979 l_bill_to_site_use_id NUMBER(15) := -1;
980 l_payment_method_code varchar2(240);
981 l_account_number varchar2(80); /* Bank account num */
982 l_org_id NUMBER(15) := -1;
983 l_set_of_books_id NUMBER(15) := -1;
984 l_orig_system_prefix varchar2(240);
985 l_payment_term_name varchar2(50);
986 l_batch_source_name varchar2(50);
987 l_interface_line_context varchar2(50);
988 l_orig_sys_cust_ref varchar2(240);
989 l_orig_sys_addr_ref varchar2(240);
990
991 -- Cust details (for previous party row in cursor)
992 l_prev_bill_to_party_id NUMBER(15) := -99;
993 l_prev_customer_name varchar2(255);
994 l_prev_billing_customer_id NUMBER(15) := -1;
995 l_prev_bill_to_site_use_id NUMBER(15) := -1;
996 l_prev_payment_method_code varchar2(240);
997 l_prev_account_number varchar2(80); /* Bank account num */
998 l_prev_org_id NUMBER(15) := -1;
999 l_prev_set_of_books_id NUMBER(15) := -1;
1000 l_prev_orig_system_prefix varchar2(240);
1001 l_prev_payment_term_name varchar2(50);
1002 l_prev_batch_source_name varchar2(50);
1003 l_prev_interface_line_context varchar2(50);
1004 l_prev_orig_sys_cust_ref varchar2(240);
1005 l_prev_orig_sys_addr_ref varchar2(240);
1006
1007 BEGIN
1008
1009 print_debug(LEVEL0,'invoice_interface +');
1010 -- Initialize dynamic sql statements.
1011 l_sql_cust_details := '
1012 SELECT
1013 hp.party_name customer_name,
1014 pbc.billing_customer_id billing_customer_id,
1015 pbc.bill_to_site_use_id bill_to_site_use_id,
1016 pbc.payment_method_name payment_method_code,
1017 pbc.account_number account_number,
1018 pbsp.org_id org_id,
1019 pbsp.set_of_books_id set_of_books_id,
1020 pbsp.orig_system_prefix orig_system_prefix,
1021 pbsp.cust_trxtype_name cust_trxtype_name,
1022 pbsp.payment_term_name payment_term_name,
1023 pbsp.batch_source_name batch_source_name,
1024 pbsp.interface_line_context interface_line_context,'||
1025 ''''||g_osr_cust_prefix||''''||'||to_char(pbc.bill_to_party_id) orig_sys_cust_ref,'||
1026 ''''||g_osr_cust_prefix||''''||'||to_char(pbc.bill_to_party_id)||'||''''||g_osr_addr_prefix||''''||' ||to_char(pbc.bill_to_site_use_id) orig_sys_addr_ref
1027 FROM
1028 hz_parties'||g_oexdblink||' hp,
1029 pom_billing_customers'||g_oexdblink||' pbc,
1030 pom_billing_seat_parameters'||g_oexdblink||' pbsp
1031 WHERE
1032 hp.party_id = pbc.bill_to_party_id
1033 AND pbsp.operator_id = pbc.operator_id
1034 AND pbc.bill_to_party_id = :1
1035 ';
1036
1037 l_sql_bank_acc := '
1038 SELECT ba.bank_account_id
1039 FROM ap_bank_account_uses_all bau,
1040 ap_bank_accounts_all ba,
1041 hz_cust_accounts_all hca
1042 WHERE bau.customer_id = hca.cust_account_id
1043 AND hca.orig_system_reference = :1
1044 AND ba.bank_account_num = :2
1045 AND bau.external_bank_account_id = ba.bank_account_id
1046 ';
1047
1048 l_sql_invoice_interface := '
1049 INSERT INTO ra_interface_lines_all
1050 (
1051 org_id, batch_source_name, set_of_books_id, line_type,
1052 currency_code, conversion_rate, conversion_type, description, memo_line_name,
1053 amount, cust_trx_type_name, orig_system_bill_customer_ref, orig_system_bill_address_ref,
1054 term_name, uom_code, trx_date, gl_date,
1055 receipt_method_name, customer_bank_account_id ,
1056 interface_line_context, interface_line_attribute1, interface_line_attribute2,
1057 interface_line_attribute3 , interface_line_attribute4 , interface_line_attribute5 ,
1058 reference_line_context, reference_line_attribute1, reference_line_attribute2,
1059 reference_line_attribute3 , reference_line_attribute4 , reference_line_attribute5 ,
1060 created_by, creation_date, last_updated_by, last_update_date,
1061 quantity
1062 )
1063 SELECT
1064 :1, :2, :3, :4,
1065 :5, :6, :7, :8, :9,
1066 :10, :11, :12, :13,
1070 :23, :24, :25,
1067 :14, :15, :16, :17,
1068 :18, :19,
1069 :20, :21, :22,
1071 :26, :27, :28,
1072 :29, :30, :31,
1073 :32, :33, :34, :35,
1074 :36
1075 FROM DUAL';
1076
1077 l_sql_get_pom_param := '
1078 SELECT parameter_value
1079 FROM pom_operator_parameters'||g_oexdblink||'
1080 WHERE operator_party_id = '||g_oper_id||'
1081 AND parameter_name = ''oexOperDefaultCurrency''
1082 ';
1083
1084 BEGIN
1085 /* get default operator currency */
1086 EXECUTE IMMEDIATE l_sql_get_pom_param
1087 INTO l_default_currency;
1088 print_debug(LEVEL0, 'Operator default currency is :'||l_default_currency);
1089
1090 IF ( l_default_currency is null ) THEN
1091 l_error_code := 'POM_NO_DEFAULT_CURRENCY';
1092 print_debug(0,' no_default_currency exception raised. Terminating program.');
1093 RAISE no_default_currency;
1094 END IF;
1095 END;
1096
1097
1098 IF ( (p_cutoff_date IS NULL) or (p_cutoff_date > sysdate) ) THEN
1099 l_cutoff_date := sysdate;
1100 ELSE
1101 l_cutoff_date := p_cutoff_date;
1102 END IF;
1103
1104 l_request_id := p_conc_request_id;
1105
1106 l_cycle_end_date := to_date('01'||'-'||to_char(sysdate,'MM')||'-'||to_char(sysdate,'YYYY'),'DD-MM-YYYY');
1107 l_cycle_start_date := add_months(l_cycle_end_date,-1);
1108 l_billing_period := to_char(l_cycle_start_date) ||' - '||to_char(l_cycle_end_date-1);
1109 print_debug(LEVEL0,'-----------------------------------------');
1110 print_debug(LEVEL0, 'Cut off date: '||to_char(l_cutoff_date)||', Request ID: '||to_char(l_request_id));
1111 print_debug(LEVEL0,'Billing period: '||l_billing_period);
1112 print_debug(LEVEL0,'-----------------------------------------');
1113
1114 l_temp_sql := '
1115 UPDATE pom_billing_activities'||g_oexdblink||'
1116 set request_id = :1
1117 WHERE billing_activity_id in
1118 (select pba.billing_activity_id
1119 from pom_billing_activities'||g_oexdblink||' pba,
1120 pom_billing_customers'||g_oexdblink||' pbc,
1121 pom_billing_activity_types_tl'||g_oexdblink||' pbat
1122 where pbc.bill_to_party_id = pba.bill_to_party_id
1123 AND pbat.billing_activity_type_id = pba.billing_activity_type_id
1124 AND pbat.language_code = ''US''
1125 and pbc.operator_id = '||g_oper_id||'
1126 and pbc.ar_transfer_flag is null
1127 and pba.priced_flag is null
1128 and transaction_date < :2
1129 and ( (nvl(pba.ar_transfer_flag,''~'') = ''N'' AND pba.request_id is null)
1130 OR
1131 (nvl(pba.ar_transfer_flag,''~'') = ''E'')
1132 )
1133 )
1134 ';
1135
1136 EXECUTE IMMEDIATE l_temp_sql
1137 USING l_request_id,l_cycle_end_date;
1138
1139 IF (sql%rowcount = 0) THEN
1140 print_debug(LEVEL0, 'No data to process ');
1141 l_exit_prog := 'Y';
1142 ELSE
1143 print_debug(LEVEL0, 'Rows to process : '||to_char(sql%rowcount));
1144 END IF;
1145
1146
1147 IF (l_exit_prog = 'N') THEN --{
1148
1149 -- Get billing activities
1150 print_debug(LEVEL0,'Opening cursor variable bill_act_cv...');
1151 OPEN bill_act_cv FOR l_sql_billing_activities
1152 USING l_request_id;
1153
1154 print_debug(LEVEL0,'Fetching data...');
1155 LOOP --{
1156 FETCH bill_act_cv INTO ba_rec;
1157 EXIT WHEN bill_act_cv%NOTFOUND;
1158
1159 l_error_code := null;
1160 l_error_msg := null;
1161
1162 IF (ba_rec.bill_to_party_id <> l_prev_party_id) THEN --{
1163 print_debug(0,'New party id record from bill_act_cv');
1164
1165 -- Billing records for new party.
1166 l_skip_party := 'N';
1167 l_prev_party_id := ba_rec.bill_to_party_id;
1168
1169 -- Invoice table specific prev value checks. Initialize so the table gets
1170 -- a new row when the party_id in the ba cursor changes.
1171 l_inv_prev_acttypeid := -1;
1172 l_oacm_prev_acttypeid := -1;
1173 l_tmp_request_id := null;
1174
1175 IF (l_ba_row_num = 1) THEN --{
1176 -- Initialize party specific local variables.
1177 l_bank_account_id := null;
1178 l_payment_term_name := null;
1179 l_customer_name := null;
1180 l_billing_customer_id := -99;
1181 l_bill_to_site_use_id := -99;
1182 l_payment_method_code := null;
1183 l_account_number := null;
1184 l_cc_number := null;
1185 l_org_id := -99;
1186 l_set_of_books_id := -99;
1187 l_orig_system_prefix := null;
1188 l_payment_term_name := null;
1189 l_batch_source_name := null;
1190 l_interface_line_context := null;
1191 l_orig_sys_cust_ref := null;
1192 l_orig_sys_addr_ref := null;
1193
1194 print_debug(LEVEL0,'l_sql_cust_details: '||l_sql_cust_details);
1195 print_debug(LEVEL0,'ba_rec.bill_to_party_id: '||to_char(ba_rec.bill_to_party_id));
1196 print_debug(LEVEL0,'g_osr_cust_prefix: '||g_osr_cust_prefix);
1197 print_debug(LEVEL0,'g_osr_addr_prefix: '||g_osr_addr_prefix);
1198 print_debug(LEVEL0,'First rec: Opening cursor variable cust_details_cv...');
1199 OPEN cust_details_cv FOR l_sql_cust_details
1200 USING ba_rec.bill_to_party_id;
1201
1202 print_debug(LEVEL0,'First rec: Fetching data...');
1203 LOOP
1204 FETCH cust_details_cv INTO custrec;
1205 EXIT WHEN cust_details_cv%NOTFOUND;
1206
1207 l_bill_to_party_id := ba_rec.bill_to_party_id;
1208 l_customer_name := custrec.customer_name;
1212 l_account_number := custrec.account_number;
1209 l_billing_customer_id := custrec.billing_customer_id;
1210 l_bill_to_site_use_id := custrec.bill_to_site_use_id;
1211 l_payment_method_code := custrec.payment_method_code;
1213 l_org_id := custrec.org_id;
1214 l_set_of_books_id := custrec.set_of_books_id;
1215 l_orig_system_prefix := custrec.orig_system_prefix;
1216 l_payment_term_name := custrec.payment_term_name;
1217 l_batch_source_name := custrec.batch_source_name;
1218 l_interface_line_context := custrec.interface_line_context;
1219 l_orig_sys_cust_ref := custrec.orig_sys_cust_ref;
1220 l_orig_sys_addr_ref := custrec.orig_sys_addr_ref;
1221 EXIT;
1222 END LOOP;
1223
1224 IF (cust_details_cv%ISOPEN) THEN
1225 CLOSE cust_details_cv;
1226 END IF;
1227
1228 -- Row 1 of cursor: store cust details for first party in cursor
1229 l_prev_bill_to_party_id := l_bill_to_party_id;
1230 l_prev_customer_name := l_customer_name;
1231 l_prev_billing_customer_id := l_billing_customer_id;
1232 l_prev_bill_to_site_use_id := l_bill_to_site_use_id;
1233 l_prev_payment_method_code := l_payment_method_code;
1234 l_prev_account_number := l_account_number;
1235 l_prev_org_id := l_org_id;
1236 l_prev_set_of_books_id := l_set_of_books_id;
1237 l_prev_orig_system_prefix := l_orig_system_prefix;
1238 l_prev_payment_term_name := l_payment_term_name;
1239 l_prev_batch_source_name := l_batch_source_name;
1240 l_prev_interface_line_context := l_interface_line_context;
1241 l_prev_orig_sys_cust_ref := l_orig_sys_cust_ref;
1242 l_prev_orig_sys_addr_ref := l_orig_sys_addr_ref;
1243 ELSE
1244 -- First, store cust details for previous party in cursor
1245 l_prev_bill_to_party_id := l_bill_to_party_id;
1246 l_prev_customer_name := l_customer_name;
1247 l_prev_billing_customer_id := l_billing_customer_id;
1248 l_prev_bill_to_site_use_id := l_bill_to_site_use_id;
1249 l_prev_payment_method_code := l_payment_method_code;
1250 l_prev_account_number := l_account_number;
1251 l_prev_org_id := l_org_id;
1252 l_prev_set_of_books_id := l_set_of_books_id;
1253 l_prev_orig_system_prefix := l_orig_system_prefix;
1254 l_prev_payment_term_name := l_payment_term_name;
1255 l_prev_batch_source_name := l_batch_source_name;
1256 l_prev_interface_line_context := l_interface_line_context;
1257 l_prev_orig_sys_cust_ref := l_orig_sys_cust_ref;
1258 l_prev_orig_sys_addr_ref := l_orig_sys_addr_ref;
1259
1260 -- Initialize party specific local variables.
1261 l_bank_account_id := null;
1262 l_payment_term_name := null;
1263 l_customer_name := null;
1264 l_billing_customer_id := -99;
1265 l_bill_to_site_use_id := -99;
1266 l_payment_method_code := null;
1267 l_account_number := null;
1268 l_cc_number := null;
1269 l_org_id := -99;
1270 l_set_of_books_id := -99;
1271 l_orig_system_prefix := null;
1272 l_payment_term_name := null;
1273 l_batch_source_name := null;
1274 l_interface_line_context := null;
1275 l_orig_sys_cust_ref := null;
1276 l_orig_sys_addr_ref := null;
1277
1278 -- Get cust details for current party in cursor
1279 OPEN cust_details_cv FOR l_sql_cust_details
1280 USING ba_rec.bill_to_party_id;
1281
1282 LOOP
1283 FETCH cust_details_cv INTO custrec;
1284 EXIT WHEN cust_details_cv%NOTFOUND;
1285
1286 l_bill_to_party_id := ba_rec.bill_to_party_id;
1287 l_customer_name := custrec.customer_name;
1288 l_billing_customer_id := custrec.billing_customer_id;
1289 l_bill_to_site_use_id := custrec.bill_to_site_use_id;
1290 l_payment_method_code := custrec.payment_method_code;
1291 l_account_number := custrec.account_number;
1292 l_org_id := custrec.org_id;
1293 l_set_of_books_id := custrec.set_of_books_id;
1294 l_orig_system_prefix := custrec.orig_system_prefix;
1295 l_payment_term_name := custrec.payment_term_name;
1296 l_batch_source_name := custrec.batch_source_name;
1297 l_interface_line_context := custrec.interface_line_context;
1298 l_orig_sys_cust_ref := custrec.orig_sys_cust_ref;
1299 l_orig_sys_addr_ref := custrec.orig_sys_addr_ref;
1300 EXIT;
1301 END LOOP;
1302 IF (cust_details_cv%ISOPEN) THEN
1303 CLOSE cust_details_cv;
1304 END IF;
1305 END IF; --}
1306
1307
1308 l_ba_row_num := l_ba_row_num + 1;
1309
1310 IF (l_prev_billing_customer_id = '-1') THEN
1311 -- skip party
1312 l_skip_party := 'Y';
1313 print_debug(0,'Cursor c_cust_details returned no rows. Skipping party [party_id='||to_char(ba_rec.bill_to_party_id)||']');
1314 END IF;
1315
1316 -- Get cust related values
1317 IF (l_prev_payment_method_code = 'BILL_ME') THEN --{
1318 l_payment_method_name := g_payment_method_billme;
1319
1320 ELSE -- CREDIT_CARD or EFT
1321 IF (l_prev_payment_method_code = 'EFT') THEN
1322 l_payment_method_name := g_payment_method_eft;
1323 ELSE
1324 l_payment_method_name := g_payment_method_credit;
1325 END IF;
1326
1327 /* For payment types CREDIT_CARD and EFT, due date is immediate */
1328 l_payment_term_name := 'IMMEDIATE';
1329
1333 EXECUTE IMMEDIATE '
1330 /* IF payment_method is Automatic, bank_account is must be passed in
1331 to the invoice interface table */
1332 l_cc_number := null;
1334 BEGIN
1335 pom_billing_util_pkg.get_util_info_w'||g_oexdblink||'(
1336 i_old_info => :a,
1337 i_info => :b );
1338 END;
1339 '
1340 USING IN l_prev_account_number, IN OUT l_cc_number;
1341
1342 OPEN bank_acc_cv FOR l_sql_bank_acc
1343 USING l_prev_orig_sys_cust_ref , l_cc_number;
1344 FETCH bank_acc_cv INTO l_bank_account_id;
1345 IF (bank_acc_cv%NOTFOUND) THEN
1346 l_error_msg := g_error_msg_nobankerr;
1347 l_action_reqd_msg := g_error_msg_nobankact;
1348 l_skip_party := 'Y';
1349 ELSIF (l_bank_account_id IS NULL) THEN
1350 l_error_msg := g_error_msg_nobankerr;
1351 l_action_reqd_msg := g_error_msg_nobankact;
1352 l_skip_party := 'Y';
1353 END IF;
1354
1355 IF (bank_acc_cv%ISOPEN) THEN
1356 CLOSE bank_acc_cv;
1357 END IF;
1358
1359 END IF; --} if payment_method = 'BILL_ME'
1360
1361
1362 -- Populate AR Invoice Interface table for the previous party
1363 if (l_invline_index > 0) then --{
1364 print_debug(0,'Bill-to Party = '||l_prev_customer_name||' ('||to_char(l_prev_bill_to_party_id)||')') ;
1365
1366 FOR ix in 1..l_invline_index LOOP
1367
1368 /* list of trx lines in an invoice. transfer the lines*/
1369 l_rounded_amount := null;
1370 l_rounded_amount := gl_mc_currency_pkg.currround(invtab(ix).total_fee, l_default_currency);
1371
1372 --print_debug(0,'INV: amount=['||to_char(invtab(ix).total_fee)||'],rounded amount = ['||to_char(l_rounded_amount)||']');
1373
1374 print_debug(LEVEL2,'INV: Transfering: '||
1375 invtab(ix).activity_type_name ||','||
1376 to_char(l_rounded_amount) );
1377
1378 l_error_msg := g_error_msg_invintferr;
1379 l_action_reqd_msg := g_error_msg_invintfact;
1380
1381 EXECUTE IMMEDIATE l_sql_invoice_interface
1382 USING
1383 g_org_id,
1384 l_prev_batch_source_name,
1385 l_prev_set_of_books_id,
1386 'LINE',
1387 l_default_currency,
1388 1,
1389 'User',
1390 invtab(ix).activity_type_name,
1391 invtab(ix).activity_type_name,
1392 l_rounded_amount,
1393 g_inv_trxtype_name,
1394 l_prev_orig_sys_cust_ref,
1395 nvl(l_prev_orig_sys_addr_ref,l_a_null),
1396 nvl(l_prev_payment_term_name,l_a_null),
1397 nvl(l_uom_code,l_a_null),
1398 sysdate,
1399 sysdate,
1400 l_payment_method_name,
1401 nvl(l_bank_account_id,l_a_null),
1402 l_prev_interface_line_context,
1403 invtab(ix).bill_to_party_id,
1404 l_prev_bill_to_site_use_id,
1405 l_request_id,
1406 l_billing_period,
1407 invtab(ix).activity_type_id,
1408 l_a_null,
1409 l_a_null,
1410 l_a_null,
1411 l_a_null,
1412 l_a_null,
1413 l_a_null,
1414 -1,
1415 sysdate,
1416 -1,
1417 sysdate,
1418 invtab(ix).quantity;
1419
1420 l_error_msg := null;
1421 l_action_reqd_msg := null;
1422
1423 END LOOP; -- for each invoice line
1424
1425 -- successfully transfered all invoice components to AR.
1426 l_temp_sql := '
1427 UPDATE pom_billing_activities'||g_oexdblink ||'
1428 SET ar_transfer_flag = NULL,
1429 last_billed_date = :1,
1430 last_update_date = sysdate
1431 WHERE bill_to_party_id = :2
1432 AND request_id = :3
1433 ';
1434 EXECUTE IMMEDIATE l_temp_sql
1435 USING l_cycle_end_date, l_prev_bill_to_party_id, l_request_id;
1436
1437 print_debug(0,to_char(sql%rowcount)||' rows updated in pba for INV [req_id = '||to_char(l_request_id)||']');
1438
1439 end if; --}
1440
1441 IF (l_oacmline_index > 0) THEN --{
1442
1443 FOR ix in 1..l_oacmline_index LOOP
1444 /* list of trx lines in an on-account credit memo. transfer the lines*/
1445
1446 l_rounded_amount := null;
1447 l_rounded_amount := gl_mc_currency_pkg.currround(oacmtab(ix).total_fee, l_default_currency);
1448
1449 --print_debug(0,'CM: amount=['||to_char(oacmtab(ix).total_fee)||'],rounded amount = ['||to_char(l_rounded_amount)||']');
1450
1451 print_debug(LEVEL2,'CM: Transfering: '|| oacmtab(ix).activity_type_name ||','|| to_char(l_rounded_amount) );
1452
1453 l_error_msg := g_error_msg_invintferr;
1454 l_action_reqd_msg := g_error_msg_invintfact;
1455
1456 EXECUTE IMMEDIATE l_sql_invoice_interface
1457 USING
1458 g_org_id,
1459 l_prev_batch_source_name,
1460 l_prev_set_of_books_id,
1461 'LINE',
1462 l_default_currency,
1463 1,
1464 'User',
1465 oacmtab(ix).activity_type_name,
1466 oacmtab(ix).activity_type_name,
1467 l_rounded_amount,
1468 g_cred_trxtype_name,
1469 l_prev_orig_sys_cust_ref,
1470 nvl(l_prev_orig_sys_addr_ref,l_a_null),
1471 l_a_null,
1472 nvl(l_uom_code,l_a_null),
1473 sysdate,
1474 sysdate,
1475 l_payment_method_name,
1476 nvl(l_bank_account_id,l_a_null),
1477 l_prev_interface_line_context,
1481 l_billing_period,
1478 oacmtab(ix).bill_to_party_id,
1479 l_prev_bill_to_site_use_id,
1480 l_cm_request_id,
1482 oacmtab(ix).activity_type_id,
1483 l_a_null,
1484 l_a_null,
1485 l_a_null,
1486 l_a_null,
1487 l_a_null,
1488 l_a_null,
1489 -1,
1490 sysdate,
1491 -1,
1492 sysdate,
1493 oacmtab(ix).quantity;
1494
1495 l_error_msg := null;
1496 l_action_reqd_msg := null;
1497
1498 END LOOP; -- for each on-account credit memo line
1499
1500 -- successfully transfered all CM records to AR.
1501 l_temp_sql := '
1502 UPDATE pom_billing_activities'||g_oexdblink||'
1503 SET ar_transfer_flag = NULL,
1504 last_billed_date = :1,
1505 last_update_date = sysdate
1506 WHERE bill_to_party_id = :2
1507 AND request_id = :3
1508 ';
1509 EXECUTE IMMEDIATE l_temp_sql
1510 USING l_cycle_end_date, l_prev_bill_to_party_id, l_cm_request_id;
1511
1512 print_debug(0,to_char(sql%rowcount)||' rows updated in pba for CM [req_id = '||to_char(l_cm_request_id)||']');
1513 END IF; --}
1514
1515 -- Update last_billed_date in pom_billing_customers as the last step.
1516 IF ( (l_invline_index > 0) or (l_oacmline_index > 0) ) THEN
1517 begin
1518 l_temp_sql := '
1519 UPDATE pom_billing_customers'||g_oexdblink||'
1520 set last_billed_date = :1
1521 where bill_to_party_id = :2
1522 ';
1523 EXECUTE IMMEDIATE l_temp_sql
1524 USING l_cycle_end_date, l_prev_bill_to_party_id;
1525
1526
1527 print_debug(0,'Committing transfer.');
1528
1529 -- reset plsql table indices
1530 l_invline_index := 0;
1531 l_cm_index := 0;
1532 l_oacmline_index := 0;
1533
1534 COMMIT;
1535
1536 exception
1537 when others then
1538 raise;
1539 end;
1540 END IF;
1541
1542 END IF; --} if party <> prev_party
1543
1544 IF (l_skip_party = 'N') then --{
1545
1546 /*
1547 * Logic to split activities into Invoice and On-Account Credit Memo.
1548 *
1549 * In each cursor iteration here, we get a transaction type and several transaction
1550 * numbers within it. Since billing activities in the cursor are grouped by
1551 * transaction numbers, it is possible that the original transaction with +ve
1552 * total_fee amt and the same transaction with an equal -ve total-fee amt (a reject),
1553 * appear in the same cycle and the sum(total_fee) for the cursor row equals zero.
1554 *
1555 * If total_fee for a cursor row line = 0, it is not transfered.
1556 * If total_fee for a cursor row > 0, it goes into an invoice.
1557 * If total_fee for a cursor row < 0, it goes into an on-account credit memo.
1558 *
1559 * Since we know how many transaction numbers go into an invoice line, we can tell
1560 * the quantity of the invoice line. The unit price however cannot be determined as
1561 * we don't have a way of storing the pricing for each transaction in the invoice
1562 * interface table.
1563 *
1564 * Thus from an invoice, we can tell the number of spot purchases or the number of
1565 * transaction deliveries that made up that invoice, the sum of charges for that
1566 * invoice line type (activity type), but not the unit price.
1567 *
1568 print_debug(0,'Trans type, num :'||ba_rec.trans_type||','||
1569 ba_rec.trans_num||',activityrecfee='||
1570 to_char(ba_rec.total_fee));
1571 */
1572
1573 --{
1574 if (ba_rec.total_fee > 0) then --{
1575 /*
1576 * Invoice: store in invtab table.
1577 */
1578 if (ba_rec.activity_type_id <> l_inv_prev_acttypeid) then --{
1579 /*
1580 * A different transaction type. Start accumulating total_fee
1581 * for all transaction_num under this type to create an invoice
1582 * invoice line.
1583 */
1584 l_invline_index := l_invline_index + 1;
1585 l_inv_prev_acttypeid := ba_rec.activity_type_id;
1586
1587 invtab(l_invline_index).bill_to_party_id := ba_rec.bill_to_party_id;
1588 invtab(l_invline_index).activity_type_name := ba_rec.activity_type_name;
1589 invtab(l_invline_index).activity_type_id := ba_rec.activity_type_id;
1590 invtab(l_invline_index).trans_type := ba_rec.trans_type;
1591
1592 invtab(l_invline_index).quantity := 1;
1593 invtab(l_invline_index).total_fee := ba_rec.total_fee;
1594 else
1595 invtab(l_invline_index).quantity := invtab(l_invline_index).quantity + 1;
1596 invtab(l_invline_index).total_fee := invtab(l_invline_index).total_fee
1597 + ba_rec.total_fee;
1598 end if; --}
1599
1600 IF (g_is_debug_enabled = 'Y') THEN
1601 print_debug(LEVEL0,'INV TABLE: '||
1602 'Party_ID: '||to_char(ba_rec.bill_to_party_id)||
1603 ', ('||to_char(l_invline_index)||') '||
1604 ', ('||to_char(invtab(l_invline_index).quantity)||') '||
1605 ', Activity: '||invtab(l_invline_index).activity_type_name||
1606 ', Total-fee: '||to_char(invtab(l_invline_index).total_fee) );
1607 END IF;
1608 elsif (ba_rec.total_fee < 0) then --}{
1609 /*
1610 * Credit Memo or On-Account Credit Memo
1611 */
1612 l_reg_cm_flag := 'N';
1616 ba_rec.bill_to_party_id,
1613 l_related_inv_reqid := null;
1614
1615 get_related_invoice (
1617 ba_rec.trans_num,
1618 ba_rec.total_fee,
1619 l_reg_cm_flag,
1620 l_related_inv_reqid
1621 );
1622 if (l_reg_cm_flag = 'Y') then --{
1623 /*
1624 * Regular Credit Memo
1625 */
1626 --print_debug(0,'Regular cm');
1627 l_cm_index := l_cm_index + 1;
1628 cmtab(l_cm_index).bill_to_party_id := ba_rec.bill_to_party_id;
1629 cmtab(l_cm_index).activity_type_name := ba_rec.activity_type_name;
1630 cmtab(l_cm_index).activity_type_id := ba_rec.activity_type_id;
1631 cmtab(l_cm_index).trans_type := ba_rec.trans_type;
1632 cmtab(l_cm_index).trans_num := ba_rec.trans_num;
1633 cmtab(l_cm_index).total_fee := ba_rec.total_fee;
1634 cmtab(l_cm_index).related_inv_reqid := l_related_inv_reqid;
1635 /*
1636 Get the next request id from sequence. Interface line contexts info must be different
1637 for different transaction groups, otherwise autoinvoice will reject the lines.
1638 */
1639 cmtab(l_cm_index).reqid := to_number(to_char(l_request_id)||'.1');
1640
1641 else --} On-account {
1642
1643 /*
1644 * On-Account Credit Memo
1645 */
1646 if (ba_rec.activity_type_id <> l_oacm_prev_acttypeid) then
1647 l_oacmline_index := l_oacmline_index + 1;
1648 l_oacm_prev_acttypeid := ba_rec.activity_type_id;
1649 oacmtab(l_oacmline_index).bill_to_party_id := ba_rec.bill_to_party_id;
1650 oacmtab(l_oacmline_index).activity_type_name := ba_rec.activity_type_name;
1651 oacmtab(l_oacmline_index).activity_type_id := ba_rec.activity_type_id;
1652 oacmtab(l_oacmline_index).trans_num := ba_rec.trans_num;
1653 oacmtab(l_oacmline_index).trans_type := ba_rec.trans_type;
1654 oacmtab(l_oacmline_index).quantity := 1;
1655 oacmtab(l_oacmline_index).total_fee := ba_rec.total_fee;
1656 -- no trans_num here, since we are accumulating the charges for all rejects that have had their invoice paid.
1657 print_debug(0,'oacm: trans_num: '||oacmtab(l_oacmline_index).trans_num);
1658 else
1659 oacmtab(l_oacmline_index).quantity := oacmtab(l_oacmline_index).quantity + 1;
1660 oacmtab(l_oacmline_index).total_fee := oacmtab(l_oacmline_index).total_fee + ba_rec.total_fee;
1661 end if;
1662
1663 IF (g_is_debug_enabled = 'Y') THEN
1664 print_debug(LEVEL0,'OACM TABLE: '||
1665 'Party_ID: '||to_char(ba_rec.bill_to_party_id)||
1666 ', ('||to_char(l_oacmline_index)||') '||
1667 ', Activity: '||oacmtab(l_oacmline_index).activity_type_name||
1668 ', Total-fee: '||to_char(oacmtab(l_oacmline_index).total_fee) );
1669 END IF;
1670
1671 /*
1672 Get the next request id from sequence. Interface line contexts info must be different
1673 for different transaction groups, otherwise autoinvoice will reject the lines.
1674 All OnAccountCMs are grouped into one CM.This is given a new request id different from
1675 the invoice request IDs.
1676 */
1677 if (l_cm_request_id is null) then
1678 /* Assign next sequence to cm request id, for reference in exchange . This new request however does not correspond to a valid conc.request in AR. Here we need a unique reference */
1679 l_cm_request_id := l_request_id + 1;
1680 end if;
1681 l_temp_sql := '
1682 UPDATE pom_billing_activities'||g_oexdblink||'
1683 SET request_id = :1
1684 WHERE bill_to_party_id = :2
1685 AND transaction_type = :3
1686 AND transaction_num = :4
1687 AND request_id = :5
1688 ';
1689 EXECUTE IMMEDIATE l_temp_sql
1690 USING l_cm_request_id,
1691 ba_rec.bill_to_party_id,
1692 ba_rec.trans_type,
1693 oacmtab(l_oacmline_index).trans_num,
1694 l_request_id;
1695
1696 print_debug(0,to_char(sql%rowcount)||' rows updated in pba for credit activities with [req_id = '||to_char(l_cm_request_id)||']');
1697
1698 end if; --}on-account
1699
1700 else --}total_fee = 0 {
1701
1702 --total_fee = 0, nothing to interface for now, just say billed.
1703 l_temp_sql := '
1704 UPDATE pom_billing_activities'||g_oexdblink||'
1705 SET ar_transfer_flag = ''P'',
1706 last_billed_date = :1,
1707 last_update_date = sysdate
1708 WHERE bill_to_party_id = :2
1709 AND transaction_type = :3
1710 AND transaction_num = :4
1711 AND request_id = :5
1712 ';
1713
1714 EXECUTE IMMEDIATE l_temp_sql
1715 USING l_cycle_end_date,
1716 ba_rec.bill_to_party_id,
1717 ba_rec.trans_type,
1718 ba_rec.trans_num,
1719 l_request_id;
1720 --print_debug(0,'Total fee = 0, updating trans['||ba_rec.trans_num||'] with status P, rows updated = '||to_char(sql%rowcount));
1721
1722 end if; --} if (total_fee = 0)
1723 --}
1724
1725 else
1726 print_debug(0,'Skipped party. Error = '||l_error_msg);
1727
1728 end if; --} if l_skip_party
1729
1730 END LOOP; --} cursor c_billing_activities
1731
1732 IF (bill_act_cv%ISOPEN) THEN
1733 CLOSE bill_act_cv;
1734 END IF;
1735
1739
1736 -- Populate AR Invoice Interface table for the last party in the cursor
1737 print_debug(0, ' Last party in cursor ');
1738 if (l_invline_index > 0) then --{
1740 print_debug(0,'Bill-to Party = '||l_customer_name||' ('||to_char(l_bill_to_party_id)||')') ;
1741 FOR ix in 1..l_invline_index LOOP
1742
1743 /* list of trx lines in an invoice. transfer the lines*/
1744 l_rounded_amount := null;
1745 l_rounded_amount := gl_mc_currency_pkg.currround(invtab(ix).total_fee, l_default_currency);
1746
1747 --print_debug(0,'INV: amount=['||to_char(invtab(ix).total_fee)||'],rounded amount = ['||to_char(l_rounded_amount)||']');
1748
1749 print_debug(LEVEL2,'INV: Transfering: '|| invtab(ix).activity_type_name ||','|| to_char(l_rounded_amount) );
1750
1751 l_error_msg := g_error_msg_invintferr;
1752 l_action_reqd_msg := g_error_msg_invintfact;
1753
1754 EXECUTE IMMEDIATE l_sql_invoice_interface
1755 USING
1756 g_org_id,
1757 l_batch_source_name,
1758 l_set_of_books_id,
1759 'LINE',
1760 l_default_currency,
1761 1,
1762 'User',
1763 invtab(ix).activity_type_name,
1764 invtab(ix).activity_type_name,
1765 l_rounded_amount,
1766 g_inv_trxtype_name,
1767 l_orig_sys_cust_ref,
1768 nvl(l_orig_sys_addr_ref,l_a_null),
1769 nvl(l_payment_term_name,l_a_null),
1770 nvl(l_uom_code,l_a_null),
1771 sysdate,
1772 sysdate,
1773 l_payment_method_name,
1774 nvl(l_bank_account_id,l_a_null),
1775 l_interface_line_context,
1776 invtab(ix).bill_to_party_id,
1777 l_bill_to_site_use_id,
1778 l_request_id,
1779 l_billing_period,
1780 invtab(ix).activity_type_id,
1781 l_a_null,
1782 l_a_null,
1783 l_a_null,
1784 l_a_null,
1785 l_a_null,
1786 l_a_null,
1787 -1,
1788 sysdate,
1789 -1,
1790 sysdate,
1791 invtab(ix).quantity;
1792
1793 l_error_msg := null;
1794 l_action_reqd_msg := null;
1795
1796 END LOOP; -- for each invoice line
1797
1798 -- successfully transfered all invoice components to AR.
1799 l_temp_sql := '
1800 UPDATE pom_billing_activities'||g_oexdblink||'
1801 SET ar_transfer_flag = NULL,
1802 last_billed_date = :1,
1803 last_update_date = sysdate
1804 WHERE bill_to_party_id = :2
1805 AND request_id = :3
1806 ';
1807
1808 EXECUTE IMMEDIATE l_temp_sql
1809 USING l_cycle_end_date,
1810 l_bill_to_party_id,
1811 l_request_id;
1812
1813 print_debug(0,to_char(sql%rowcount)||' rows updated in pba for INV [req_id = '||to_char(l_request_id)||']');
1814 end if; --}
1815
1816 IF (l_oacmline_index > 0) THEN --{
1817
1818 FOR ix in 1..l_oacmline_index LOOP
1819 /* list of trx lines in an on-account credit memo. transfer the lines*/
1820
1821 l_rounded_amount := null;
1822 l_rounded_amount := gl_mc_currency_pkg.currround(oacmtab(ix).total_fee, l_default_currency);
1823
1824 --print_debug(0,'CM: amount=['||to_char(oacmtab(ix).total_fee)||'],rounded amount = ['||to_char(l_rounded_amount)||']');
1825
1826 print_debug(LEVEL2,'CM: Transfering: '|| oacmtab(ix).activity_type_name ||','|| to_char(l_rounded_amount) );
1827
1828 l_error_msg := g_error_msg_invintferr;
1829 l_action_reqd_msg := g_error_msg_invintfact;
1830
1831 EXECUTE IMMEDIATE l_sql_invoice_interface
1832 USING
1833 g_org_id,
1834 l_batch_source_name,
1835 l_set_of_books_id,
1836 'LINE',
1837 l_default_currency,
1838 1,
1839 'User',
1840 oacmtab(ix).activity_type_name,
1841 oacmtab(ix).activity_type_name,
1842 l_rounded_amount,
1843 g_cred_trxtype_name,
1844 l_orig_sys_cust_ref,
1845 nvl(l_orig_sys_addr_ref,l_a_null),
1846 l_a_null,
1847 nvl(l_uom_code,l_a_null),
1848 sysdate,
1849 sysdate,
1850 l_payment_method_name,
1851 nvl(l_bank_account_id,l_a_null),
1852 l_interface_line_context,
1853 oacmtab(ix).bill_to_party_id,
1854 l_bill_to_site_use_id,
1855 l_cm_request_id,
1856 l_billing_period,
1857 oacmtab(ix).activity_type_id,
1858 l_a_null,
1859 l_a_null,
1860 l_a_null,
1861 l_a_null,
1862 l_a_null,
1863 l_a_null,
1864 -1,
1865 sysdate,
1866 -1,
1867 sysdate,
1868 oacmtab(ix).quantity;
1869
1870 l_error_msg := null;
1871 l_action_reqd_msg := null;
1872
1873 END LOOP; -- for each on-account credit memo line
1874
1875 -- successfully transfered all CM records to AR.
1876 l_temp_sql := '
1877 UPDATE pom_billing_activities'||g_oexdblink||'
1878 SET ar_transfer_flag = NULL,
1879 last_billed_date = :1,
1880 last_update_date = sysdate
1881 WHERE bill_to_party_id = :2
1882 AND request_id = :3
1883 ';
1884 EXECUTE IMMEDIATE l_temp_sql
1885 USING l_cycle_end_date, l_bill_to_party_id, l_cm_request_id;
1886
1887 print_debug(0,to_char(sql%rowcount)||' rows updated in pba for CM [req_id = '||to_char(l_cm_request_id)||']');
1888 END IF; --}
1889
1893 l_temp_sql := '
1890 -- Update last_billed_date in pom_billing_customers as the last step.
1891 IF ( (l_invline_index > 0) or (l_oacmline_index > 0) ) THEN
1892 begin
1894 UPDATE pom_billing_customers'||g_oexdblink||'
1895 set last_billed_date = :1
1896 where bill_to_party_id = :2
1897 ';
1898 EXECUTE IMMEDIATE l_temp_sql
1899 USING l_cycle_end_date, l_bill_to_party_id;
1900
1901 print_debug(0,'Committing transfer.');
1902
1903 -- reset plsql table indices
1904 l_invline_index := 0;
1905 l_cm_index := 0;
1906 l_oacmline_index := 0;
1907
1908 COMMIT;
1909 exception
1910 when others then
1911 raise;
1912 end;
1913 END IF;
1914
1915 END IF; --} if l_exit_prog = 'N'
1916
1917
1918 x_error_code := l_error_code;
1919 x_error_msg := l_error_msg||sqlerrm;
1920
1921 COMMIT;
1922
1923 print_debug(LEVEL0,'invoice_interface -');
1924
1925 EXCEPTION
1926 WHEN interface_program_error THEN
1927 RAISE;
1928
1929 WHEN OTHERS THEN
1930 /*
1931 Update records with error flag. Req-id stays so we can report
1932 on failed requests. Additionally we should insert failure
1933 codes/messages into pom_billing_interface_errors.(tablename, pk_of_table,
1934 error_code, error_msg, status) where status can be 'ERROR','CORRECTED'
1935 */
1936 print_debug(0,'invoice_interface raised following exception: ');
1937 print_debug(0,'SQLERRM: '||sqlerrm);
1938 print_debug(0,'PROGRAM ERROR CODE: '||l_error_code);
1939 print_debug(0,'Check Exceptions Page for more information and action required.');
1940
1941
1942 print_debug(0,'Rolling back.');
1943 ROLLBACK ;
1944
1945 l_temp_sql := '
1946 UPDATE pom_billing_activities'||g_oexdblink||'
1947 SET ar_transfer_flag = ''E'',
1948 request_id = null,
1949 last_update_date = sysdate
1950 WHERE request_id = :1
1951 ';
1952 EXECUTE IMMEDIATE l_temp_sql
1953 USING l_request_id;
1954
1955 ar_exchange_interface_pkg.record_error(
1956 p_billing_activity_id => null,
1957 p_billing_customer_id => null,
1958 p_customer_name => nvl(l_customer_name, p_customer_name),
1959 p_error_code => l_error_code,
1960 p_additional_message => l_error_msg,
1961 p_action_required => l_action_reqd_msg,
1962 p_invalid_value => l_invalid_value
1963 );
1964
1965 COMMIT;
1966
1967 x_error_code := l_error_code;
1968 x_error_msg := l_error_msg||sqlerrm;
1969
1970 print_debug(LEVEL0,'invoice_interface: '||l_error_msg);
1971 print_debug(LEVEL0,'invoice_interface: '||sqlerrm);
1972
1973 print_debug(LEVEL0,'invoice_interface -');
1974
1975 END invoice_interface;
1976
1977 procedure record_error (
1978 p_billing_activity_id IN number default null,
1979 p_billing_customer_id IN number default null,
1980 p_customer_name IN varchar2 default null,
1981 p_error_code IN varchar2,
1982 p_additional_message IN varchar2 default null,
1983 p_action_required IN varchar2 default null,
1984 p_invalid_value IN varchar2
1985 ) IS
1986
1987 l_insert_pom_err varchar2(4000) ;
1988
1989 BEGIN
1990
1991 l_insert_pom_err := '
1992 INSERT INTO pom_billing_interface_errors'||g_oexdblink||'
1993 (
1994 billing_activity_id, billing_customer_id, customer_name,
1995 error_code,
1996 additional_message,
1997 action_required,
1998 invalid_value,
1999 creation_date
2000 )
2001 SELECT
2002 :1, :2, :3, :4,
2003 :5, :6, :7, :8
2004 FROM DUAL
2005 ';
2006
2007 print_debug(LEVEL0,'-------- ERROR -------------------------- ');
2008 print_debug(LEVEL0,'ar_exchange_interface_pkg.record_error + : ');
2009 print_debug(LEVEL4,'p_billing_activity_id : '||to_char(p_billing_activity_id));
2010 print_debug(LEVEL4,'p_billing_customer_id : '||to_char(p_billing_customer_id));
2011 print_debug(LEVEL4,'p_customer_name : '||p_customer_name);
2012 print_debug(LEVEL4,'p_error_code : '||p_error_code);
2013 print_debug(LEVEL4,'p_invalid_value : '||p_invalid_value);
2014 print_debug(LEVEL4,'p_additional_message : '||p_additional_message);
2015 print_debug(LEVEL4,'p_action_required : '||p_action_required);
2016 print_debug(LEVEL0,'------------------------------------------ ');
2017 -- operation can be 'INVOICE' or 'CUSTOMER'
2018
2019 EXECUTE IMMEDIATE l_insert_pom_err
2020 USING
2021 p_billing_activity_id,
2022 p_billing_customer_id,
2023 p_customer_name,
2024 p_error_code,
2025 p_additional_message,
2026 p_action_required,
2027 nvl(p_invalid_value,'-'),
2028 sysdate;
2029
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 raise;
2033 END record_error;
2034
2035 BEGIN
2036 print_debug(0,'ar_exchange_interface_pkg +');
2037 print_debug(0,'Package initialization section +');
2038
2039 /* Pkg initialization section */
2040 g_osr_cust_prefix := 'EXCHANGE_CUST';
2041 g_osr_addr_prefix := '_ADDR';
2042
2043 g_inv_trxtype_name := arp_standard.ar_lookup('ORACLE_EXCHANGE_TRX_TYPES','INVOICE');
2047
2044 g_cred_trxtype_name := arp_standard.ar_lookup('ORACLE_EXCHANGE_TRX_TYPES','CREDIT');
2045 print_debug(0,'INV Trxtype in AR = ['||g_inv_trxtype_name||']');
2046 print_debug(0,'CM Trxtype in AR = ['||g_cred_trxtype_name||']');
2048 g_payment_method_billme := arp_standard.ar_lookup('ORACLE_EXCHANGE_PAY_METHODS','BILL_ME');
2049 g_payment_method_credit := arp_standard.ar_lookup('ORACLE_EXCHANGE_PAY_METHODS','CREDIT_CARD');
2050 g_payment_method_eft := arp_standard.ar_lookup('ORACLE_EXCHANGE_PAY_METHODS','EFT');
2051 print_debug(0,'BILL_ME Pay method = ['||g_payment_method_billme||']');
2052 print_debug(0,'CREDIT_CARD Pay method = ['||g_payment_method_credit||']');
2053 print_debug(0,'EFT Pay method = ['||g_payment_method_eft||']');
2054
2055 g_oexdblink := fnd_profile.value('ORACLE_EXCHANGE_DATABASE_LINK');
2056 IF (g_oexdblink is null) THEN
2057 print_debug(0,'Error: Could not get value for profile ORACLE_EXCHANGE_DATABASE_LINK. Terminating program...');
2058 RAISE init_error;
2059 ELSE
2060 g_oexdblink := '@'||g_oexdblink;
2061 print_debug(0, 'DBlink name (g_oexdblink) is '||g_oexdblink);
2062 END IF;
2063
2064 EXECUTE IMMEDIATE 'select org_id from ar_system_parameters'
2065 INTO g_org_id;
2066
2067 IF (g_org_id is null) THEN
2068 RAISE init_error;
2069 END IF;
2070
2071 l_sql_stmt := '
2072 select operator_id
2073 from pom_billing_seat_parameters'||g_oexdblink||'
2074 where org_id = :1
2075 ';
2076
2077 BEGIN
2078 EXECUTE IMMEDIATE l_sql_stmt
2079 INTO g_oper_id
2080 USING g_org_id;
2081 EXCEPTION
2082 WHEN TOO_MANY_ROWS THEN
2083 print_debug(0,'More than one seat parameter row in exchange is configured with org id: '||to_char(g_org_id));
2084 print_debug(0,'Resolve and re-run this program. Terminating...');
2085 RAISE;
2086 END;
2087
2088 IF (g_oper_id is null) THEN
2089 RAISE init_error;
2090 END IF;
2091
2092 print_debug(0,'Org_id: '||to_char(g_org_id)||', Operator ID: '||to_char(g_oper_id));
2093
2094
2095 -- Get message from POM product in exchange and cache it locally
2096 l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_NO_BANKACC_ERR'')
2097 from dual';
2098 execute immediate l_sql_stmt into g_error_msg_nobankerr;
2099
2100 l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_NO_BANKACC_ACT'')
2101 from dual';
2102 execute immediate l_sql_stmt into g_error_msg_nobankact;
2103
2104 l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_CUST_INTF_ERR'')
2105 from dual';
2106 execute immediate l_sql_stmt into g_error_msg_custintferr;
2107
2108 l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_CUST_INTF_ACT'')
2109 from dual';
2110 execute immediate l_sql_stmt into g_error_msg_custintfact;
2111
2112 l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_INV_INTF_ERR'')
2113 from dual';
2114 execute immediate l_sql_stmt into g_error_msg_invintferr;
2115
2116 l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_INV_INTF_ACT'')
2117 from dual';
2118 execute immediate l_sql_stmt into g_error_msg_invintfact;
2119
2120 l_sql_stmt := null;
2121
2122 IF (fnd_profile.value_specific('AFLOG_ENABLED',fnd_global.user_id) = 'Y') THEN
2123 g_is_debug_enabled := 'Y';
2124 END IF;
2125
2126 print_debug(0,'Pkg initialization section -');
2127
2128 EXCEPTION
2129 WHEN OTHERS THEN
2130 RAISE;
2131 END ar_exchange_interface_pkg;