The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date DATE,
customer_name VARCHAR2(50),
party_number VARCHAR2(30),
address1 varchar2(240),
address2 varchar2(240),
address3 varchar2(240),
address4 varchar2(240),
city varchar2(60),
county varchar2(60),
state varchar2(60),
country varchar2(60),
postal_plus4_code varchar2(10),
bank_name varchar2(30),
account_name varchar2(80),
account_number varchar2(80),
account_currency varchar2(15),
account_description varchar2(240),
account_exp_date DATE,
payment_method_code varchar2(30),
insert_update_flag varchar2(10),
org_id number(15)
);
select meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and language = userenv('LANG');
Transaction to insert into all interface records relating
to one exchange billing customer record.
*/
PROCEDURE transfer_customer_record (
cust_rec IN OexCustRecTyp,
p_transfer_flag OUT NOCOPY varchar2
) IS
TYPE OexContactRecTyp is RECORD (
person_title varchar2(255),
person_first_name varchar2(255),
person_last_name varchar2(255),
contact_point_id NUMBER(15),
contact_point_type varchar2(255),
phone_line_type varchar2(255),
phone_area_code varchar2(255),
phone_number varchar2(255),
phone_extension varchar2(255),
email_address varchar2(2000)
);
-- 'U'(update), else 'I'(insert)
l_payment_method_name varchar2(240);
INSERT INTO ra_customers_interface_all (
org_id, orig_system_customer_ref, orig_system_address_ref, insert_update_flag,
customer_name, customer_number, address1, address2, address3, address4,
city, county, state, country, postal_code,
customer_prospect_code, customer_status, customer_type,
primary_site_use_flag, site_use_code,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3, :4,
:5, :6, :7, :8, :9,
:10, :11, :12, :13, :14,
:15, :16, :17,
:18, :19,
:20, :21, :22, :23, :24
FROM DUAL ';
INSERT INTO ra_customer_profiles_int_all (
org_id, orig_system_customer_ref, insert_update_flag,
customer_profile_class_name, credit_hold,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5,
:6, :7, :8, :9
FROM DUAL';
INSERT INTO ra_cust_pay_method_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
payment_method_name, start_date, primary_flag,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5, :6,
:7, :8, :9, :10
FROM DUAL';
INSERT INTO ra_customer_banks_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
bank_account_num, bank_account_currency_code, bank_account_inactive_date,
bank_account_name,
bank_name,
bank_branch_name,
start_date, primary_flag,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5, :6,
:7,
decode(:8, :9, :10, :11),
decode(:12, :13, :14, :15),
:16, :17, :18,
:19, :20, :21
FROM DUAL';
INSERT INTO ra_contact_phones_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
orig_system_contact_ref,
contact_first_name, contact_last_name, contact_title,
insert_update_flag, email_address,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4,
:5, :6, :7,
:8, :9,
:10, :11, :12, :13
FROM DUAL';
INSERT INTO ra_contact_phones_int_all (
org_id, orig_system_customer_ref, orig_system_address_ref,
orig_system_contact_ref, orig_system_telephone_ref,
contact_first_name, contact_last_name, contact_title,
insert_update_flag, telephone_type, telephone,
telephone_area_code, telephone_extension, email_address,
created_by, creation_date, last_updated_by, last_update_date
)
SELECT
:1, :2, :3,
:4, :5,
:6, :7, :8,
:9,
decode(:10, :11, :12, :13),
decode(:14, :15, :16, :17),
:18, :19, :20,
:21, :22, :23, :24
FROM DUAL';
select
hpcont.person_pre_name_adjunct person_title,
hpcont.person_first_name person_first_name,
hpcont.person_last_name person_last_name,
hcp1.contact_point_id contact_point_id,
hcp1.contact_point_type contact_point_type,
hcp1.phone_line_type phone_line_type,
hcp1.phone_area_code phone_area_code,
hcp1.phone_number phone_number,
hcp1.phone_extension phone_extension,
hcp1.email_address email_address
from
hz_parties'||g_oexdblink||' hpcont,
hz_contact_points'||g_oexdblink||' hcp1
where hpcont.party_id = :1
and hcp1.owner_table_id = hpcont.party_id
and hcp1.owner_table_name = ''HZ_PARTIES''
';
SELECT rm.name
FROM hz_cust_accounts'||g_oexdblink||' hca,
ar_receipt_methods rm,
ra_cust_receipt_methods rcrm
WHERE hca.orig_system_reference = :1
AND rm.name = :2
AND rcrm.customer_id = hca.cust_account_id
AND rcrm.receipt_method_id = rm.receipt_method_id
AND sysdate <= nvl(rcrm.end_date,sysdate)
';
UPDATE pom_billing_customers'||g_oexdblink||'
SET ar_transfer_flag = null,
insert_update_flag = null,
last_update_date = sysdate
WHERE billing_customer_id = :1
';
UPDATE pom_billing_customers'||g_oexdblink||'
SET ar_transfer_flag = ''E'',
request_id = null,
last_update_date = sysdate
WHERE billing_customer_id = :1
';
if (nvl(cust_rec.insert_update_flag,'~') = 'I') then
l_primary_su_flag := 'Y';
cust_rec.insert_update_flag,
cust_rec.customer_name,
cust_rec.party_number,
nvl(cust_rec.address1,l_a_null),
nvl(cust_rec.address2,l_a_null),
nvl(cust_rec.address3,l_a_null),
nvl(cust_rec.address4,l_a_null),
nvl(cust_rec.city,l_a_null),
nvl(cust_rec.county,l_a_null),
nvl(cust_rec.state,l_a_null),
nvl(cust_rec.country,l_a_null),
nvl(cust_rec.postal_plus4_code,l_a_null),
'CUSTOMER',
'A',
'R',
nvl(l_primary_su_flag,l_a_null),
nvl(l_site_use_code,l_a_null),
-1,
sysdate,
-1,
sysdate ;
print_debug(0,'-inserted customer interface record.');
if (nvl(cust_rec.insert_update_flag,'~') = 'I') then
EXECUTE IMMEDIATE l_sql_profiles_interface
USING
cust_rec.org_id,
cust_rec.orig_sys_cust_ref,
'I',
'DEFAULT',
'N',
-1,
sysdate,
-1,
sysdate;
print_debug(0,'-inserted profile interface record.');
-- no payment method exists for sysdate, insert one
l_pm_ins_upd_flag := 'I';
print_debug(0,'-inserted payment method interface record.');
-- Insert bank account information only for automatic payment methods
-- like credit_card and eft. For bill_me (manual payment), we do not
-- capture account number during registration.
--
IF ( (cust_rec.payment_method_code <> 'BILL_ME') AND
(cust_rec.account_number IS NOT NULL) ) THEN
l_account_number := null;
print_debug(0,'-inserting bank interface record.');
print_debug(0,'-inserted bank account interface record.');
print_debug(0,'-inserting contact interface record.');
cust_rec.insert_update_flag,
l_cont_rec.email_address,
-1,
sysdate,
-1,
sysdate;
cust_rec.insert_update_flag,
l_cont_rec.contact_point_type, '1','2',l_cont_rec.phone_line_type,
l_cont_rec.contact_point_type, '1','2',l_cont_rec.phone_number,
l_cont_rec.phone_area_code,
l_cont_rec.phone_extension,
l_cont_rec.email_address,
-1,
sysdate,
-1,
sysdate;
print_debug(0,'-inserted contact-telephone interface record.');
print_debug(0,'-updated transfer flag .');
print_debug(0,'Insert error: '||sqlerrm);
SELECT bbr.bank_name, bbr.bank_branch_name
FROM ce_bank_branches_v bbr
WHERE bbr.branch_party_id = arp_global.CC_BANK_BRANCH_ID
';
SELECT
pbc.operator_id operator_id,
pbc.billing_customer_id billing_customer_id, '||
''''||g_osr_cust_prefix||''''||' ||to_char(pbc.bill_to_party_id) orig_sys_cust_ref,'||
''''||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,
pbc.bill_to_party_id bill_to_party_id,
pbc.bill_to_site_use_id site_use_id,
pbc.bill_to_contact_party_id contact_party_id,
pbc.creation_date creation_date,
pbc.last_update_date last_update_date,
substr(hp.party_name,1,50) customer_name,
hp.party_number party_number,
hloc.address1 address1,
hloc.address2 address2,
hloc.address3 address3,
hloc.address4 address4,
hloc.city city,
hloc.county county,
hloc.state state,
hloc.country country,
hloc.postal_plus4_code postal_plus4_code,
pbc.bank_name bank_name,
pbc.account_name account_name,
pbc.account_number account_number,
pbc.account_currency account_currency,
pbc.account_description account_description,
pbc.account_inactive_date account_exp_date,
pbc.payment_method_name payment_method_code,
nvl(pbc.insert_update_flag,''I'') insert_update_flag,
pbsp.org_id org_id
FROM
pom_billing_customers'||g_oexdblink||' pbc,
hz_party_site_uses'||g_oexdblink||' hpsu,
hz_party_sites'||g_oexdblink||' hps,
hz_locations'||g_oexdblink||' hloc,
hz_parties'||g_oexdblink||' hp,
pom_billing_seat_parameters'||g_oexdblink||' pbsp
WHERE hp.party_id = pbc.bill_to_party_id
AND hps.party_id = hp.party_id
AND hpsu.party_site_use_id = pbc.bill_to_site_use_id
AND hpsu.site_use_type = ''EXCHANGE_BILLING''
AND hpsu.party_site_id = hps.party_site_id
AND hps.location_id = hloc.location_id
AND pbsp.operator_id = pbc.operator_id
AND pbc.request_id = :1
';
UPDATE pom_billing_customers'||g_oexdblink||'
SET ar_transfer_flag = ''E'',
request_id = null,
last_update_date = sysdate
WHERE ar_transfer_flag = ''N''
AND request_id = :1
';
UPDATE pom_billing_customers'||g_oexdblink||'
SET request_id = :1,
ar_transfer_flag = ''N'',
last_update_date = sysdate
WHERE ( ((nvl(ar_transfer_flag,''~'') = ''N'') AND request_id is null)
OR (nvl(ar_transfer_flag,''~'') = ''E'')
)
';
print_debug(0,'Rows updated: '||to_char(sql%rowcount));
Update records with error flag. Req-id stays so we can report
on failed requests. Additionally we should insert failure
codes/messages into pom_billing_interface_errors.(tablename, pk_of_table,
error_code, error_msg, status) where status can be 'ERROR','CORRECTED'
*/
print_debug(0,'ar_exchange_interface_pkg.customer_interface raised following exception: ');
SELECT
pba.bill_to_party_id bill_to_party_id,
pba.transaction_type trans_type,
rtrim(pbat.billing_activity_type_name) activity_type_name,
pbat.billing_activity_type_id activity_type_id,
pba.transaction_num trans_num,
sum(pba.total_fee) total_fee
FROM
pom_billing_activities'||g_oexdblink||' pba,
pom_billing_activity_types_tl'||g_oexdblink||' pbat
WHERE pba.request_id = :1
AND pbat.billing_activity_type_id = pba.billing_activity_type_id
AND pbat.language_code = ''US''
GROUP BY
pba.bill_to_party_id,
pba.transaction_type,
rtrim(pbat.billing_activity_type_name),
pbat.billing_activity_type_id,
pba.transaction_num
ORDER BY
pba.bill_to_party_id,
pba.transaction_type,
pbat.billing_activity_type_id,
pba.transaction_num
';
SELECT
hp.party_name customer_name,
pbc.billing_customer_id billing_customer_id,
pbc.bill_to_site_use_id bill_to_site_use_id,
pbc.payment_method_name payment_method_code,
pbc.account_number account_number,
pbsp.org_id org_id,
pbsp.set_of_books_id set_of_books_id,
pbsp.orig_system_prefix orig_system_prefix,
pbsp.cust_trxtype_name cust_trxtype_name,
pbsp.payment_term_name payment_term_name,
pbsp.batch_source_name batch_source_name,
pbsp.interface_line_context interface_line_context,'||
''''||g_osr_cust_prefix||''''||'||to_char(pbc.bill_to_party_id) orig_sys_cust_ref,'||
''''||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
FROM
hz_parties'||g_oexdblink||' hp,
pom_billing_customers'||g_oexdblink||' pbc,
pom_billing_seat_parameters'||g_oexdblink||' pbsp
WHERE
hp.party_id = pbc.bill_to_party_id
AND pbsp.operator_id = pbc.operator_id
AND pbc.bill_to_party_id = :1
';
SELECT ba.bank_account_id
FROM ap_bank_account_uses_all bau,
ap_bank_accounts_all ba,
hz_cust_accounts_all hca
WHERE bau.customer_id = hca.cust_account_id
AND hca.orig_system_reference = :1
AND ba.bank_account_num = :2
AND bau.external_bank_account_id = ba.bank_account_id
';
INSERT INTO ra_interface_lines_all
(
org_id, batch_source_name, set_of_books_id, line_type,
currency_code, conversion_rate, conversion_type, description, memo_line_name,
amount, cust_trx_type_name, orig_system_bill_customer_ref, orig_system_bill_address_ref,
term_name, uom_code, trx_date, gl_date,
receipt_method_name, customer_bank_account_id ,
interface_line_context, interface_line_attribute1, interface_line_attribute2,
interface_line_attribute3 , interface_line_attribute4 , interface_line_attribute5 ,
reference_line_context, reference_line_attribute1, reference_line_attribute2,
reference_line_attribute3 , reference_line_attribute4 , reference_line_attribute5 ,
created_by, creation_date, last_updated_by, last_update_date,
quantity
)
SELECT
:1, :2, :3, :4,
:5, :6, :7, :8, :9,
:10, :11, :12, :13,
:14, :15, :16, :17,
:18, :19,
:20, :21, :22,
:23, :24, :25,
:26, :27, :28,
:29, :30, :31,
:32, :33, :34, :35,
:36
FROM DUAL';
SELECT parameter_value
FROM pom_operator_parameters'||g_oexdblink||'
WHERE operator_party_id = '||g_oper_id||'
AND parameter_name = ''oexOperDefaultCurrency''
';
UPDATE pom_billing_activities'||g_oexdblink||'
set request_id = :1
WHERE billing_activity_id in
(select pba.billing_activity_id
from pom_billing_activities'||g_oexdblink||' pba,
pom_billing_customers'||g_oexdblink||' pbc,
pom_billing_activity_types_tl'||g_oexdblink||' pbat
where pbc.bill_to_party_id = pba.bill_to_party_id
AND pbat.billing_activity_type_id = pba.billing_activity_type_id
AND pbat.language_code = ''US''
and pbc.operator_id = '||g_oper_id||'
and pbc.ar_transfer_flag is null
and pba.priced_flag is null
and transaction_date < :2
and ( (nvl(pba.ar_transfer_flag,''~'') = ''N'' AND pba.request_id is null)
OR
(nvl(pba.ar_transfer_flag,''~'') = ''E'')
)
)
';
UPDATE pom_billing_activities'||g_oexdblink ||'
SET ar_transfer_flag = NULL,
last_billed_date = :1,
last_update_date = sysdate
WHERE bill_to_party_id = :2
AND request_id = :3
';
print_debug(0,to_char(sql%rowcount)||' rows updated in pba for INV [req_id = '||to_char(l_request_id)||']');
UPDATE pom_billing_activities'||g_oexdblink||'
SET ar_transfer_flag = NULL,
last_billed_date = :1,
last_update_date = sysdate
WHERE bill_to_party_id = :2
AND request_id = :3
';
print_debug(0,to_char(sql%rowcount)||' rows updated in pba for CM [req_id = '||to_char(l_cm_request_id)||']');
-- Update last_billed_date in pom_billing_customers as the last step.
IF ( (l_invline_index > 0) or (l_oacmline_index > 0) ) THEN
begin
l_temp_sql := '
UPDATE pom_billing_customers'||g_oexdblink||'
set last_billed_date = :1
where bill_to_party_id = :2
';
UPDATE pom_billing_activities'||g_oexdblink||'
SET request_id = :1
WHERE bill_to_party_id = :2
AND transaction_type = :3
AND transaction_num = :4
AND request_id = :5
';
print_debug(0,to_char(sql%rowcount)||' rows updated in pba for credit activities with [req_id = '||to_char(l_cm_request_id)||']');
UPDATE pom_billing_activities'||g_oexdblink||'
SET ar_transfer_flag = ''P'',
last_billed_date = :1,
last_update_date = sysdate
WHERE bill_to_party_id = :2
AND transaction_type = :3
AND transaction_num = :4
AND request_id = :5
';
--print_debug(0,'Total fee = 0, updating trans['||ba_rec.trans_num||'] with status P, rows updated = '||to_char(sql%rowcount));
UPDATE pom_billing_activities'||g_oexdblink||'
SET ar_transfer_flag = NULL,
last_billed_date = :1,
last_update_date = sysdate
WHERE bill_to_party_id = :2
AND request_id = :3
';
print_debug(0,to_char(sql%rowcount)||' rows updated in pba for INV [req_id = '||to_char(l_request_id)||']');
UPDATE pom_billing_activities'||g_oexdblink||'
SET ar_transfer_flag = NULL,
last_billed_date = :1,
last_update_date = sysdate
WHERE bill_to_party_id = :2
AND request_id = :3
';
print_debug(0,to_char(sql%rowcount)||' rows updated in pba for CM [req_id = '||to_char(l_cm_request_id)||']');
-- Update last_billed_date in pom_billing_customers as the last step.
IF ( (l_invline_index > 0) or (l_oacmline_index > 0) ) THEN
begin
l_temp_sql := '
UPDATE pom_billing_customers'||g_oexdblink||'
set last_billed_date = :1
where bill_to_party_id = :2
';
Update records with error flag. Req-id stays so we can report
on failed requests. Additionally we should insert failure
codes/messages into pom_billing_interface_errors.(tablename, pk_of_table,
error_code, error_msg, status) where status can be 'ERROR','CORRECTED'
*/
print_debug(0,'invoice_interface raised following exception: ');
UPDATE pom_billing_activities'||g_oexdblink||'
SET ar_transfer_flag = ''E'',
request_id = null,
last_update_date = sysdate
WHERE request_id = :1
';
l_insert_pom_err varchar2(4000) ;
l_insert_pom_err := '
INSERT INTO pom_billing_interface_errors'||g_oexdblink||'
(
billing_activity_id, billing_customer_id, customer_name,
error_code,
additional_message,
action_required,
invalid_value,
creation_date
)
SELECT
:1, :2, :3, :4,
:5, :6, :7, :8
FROM DUAL
';
EXECUTE IMMEDIATE l_insert_pom_err
USING
p_billing_activity_id,
p_billing_customer_id,
p_customer_name,
p_error_code,
p_additional_message,
p_action_required,
nvl(p_invalid_value,'-'),
sysdate;
EXECUTE IMMEDIATE 'select org_id from ar_system_parameters'
INTO g_org_id;
select operator_id
from pom_billing_seat_parameters'||g_oexdblink||'
where org_id = :1
';
l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_NO_BANKACC_ERR'')
from dual';
l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_NO_BANKACC_ACT'')
from dual';
l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_CUST_INTF_ERR'')
from dual';
l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_CUST_INTF_ACT'')
from dual';
l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_INV_INTF_ERR'')
from dual';
l_sql_stmt := 'select fnd_message.get_string'||g_oexdblink||'(''POM'',''POM_BILL_INV_INTF_ACT'')
from dual';