1) API to create Bank
l_extbank_rec.object_version_number := 1.0;
l_extbank_rec.bank_name := rec_cust_bank.bank_name;
l_extbank_rec.bank_number := rec_cust_bank.bank_number;
l_extbank_rec.country_code := rec_cust_bank.country_code;
l_extbank_rec.description := 'Created via API';
IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => l_extbank_rec,
x_bank_id => x_bank_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
2. API to Create Bank Branch
l_ext_bank_branch_rec.bank_party_id := x_bank_id ;
l_ext_bank_branch_rec.bch_object_version_number := 1.0;
l_ext_bank_branch_rec.typ_object_version_number := 1.0;
l_ext_bank_branch_rec.rfc_object_version_number := 1.0;
l_ext_bank_branch_rec.eft_object_version_number := 1.0;
l_ext_bank_branch_rec.branch_name := rec_cust_bank.branch_name;
l_ext_bank_branch_rec.branch_number := rec_cust_bank.branch_number;
l_ext_bank_branch_rec.branch_type := rec_cust_bank.branch_type;
l_ext_bank_branch_rec.bic := rec_cust_bank.bic;
IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_branch_rec => l_ext_bank_branch_rec,
x_branch_id => x_branch_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
3. API to Create Branch Location
p_location_rec.country := rec_cust_bank.country_code;
p_location_rec.address1 := rec_cust_bank.address1;
p_location_rec.city := rec_cust_bank.city;
p_location_rec.postal_code := rec_cust_bank.postal_code;
p_location_rec.state := rec_cust_bank.state;
p_location_rec.province := rec_cust_bank.province;
p_location_rec.created_by_module := 'CE';
hz_location_v2pub.create_location( p_init_msg_list => fnd_api.g_true
, p_location_rec => p_location_rec
, x_location_id => x_location_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data );
4. API to Create Party Site to link Location and Branch id
p_party_site_rec.party_id := x_branch_id;
p_party_site_rec.location_id := x_location_id;
p_party_site_rec.identifying_address_flag := 'N';
p_party_site_rec.created_by_module := 'CE';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_site_v2pub.create_party_site');
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE(
p_init_msg_list => FND_API.G_TRUE,
p_party_site_rec => p_party_site_rec,
x_party_site_id => x_party_site_id,
x_party_site_number => x_party_site_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
5. API to Create Bank Account
l_bank_acct_rec.object_version_number := 1.0;
l_bank_acct_rec.branch_id := x_branch_id ;
l_bank_acct_rec.bank_id := x_bank_id;
l_bank_acct_rec.acct_owner_party_id := rec_cust_bank.acct_owner_party_id;
l_bank_acct_rec.bank_account_name := rec_cust_bank.bank_account_name;
l_bank_acct_rec.bank_account_num := rec_cust_bank.bank_account_num;
l_bank_acct_rec.iban := rec_cust_bank.iban;
l_bank_acct_rec.country_code := rec_cust_bank.country_code;
l_bank_acct_rec.currency := rec_cust_bank.acct_currency;
l_bank_acct_rec.foreign_payment_use_flag := rec_cust_bank.foreign_payment_use_flag;
l_bank_acct_rec.start_date := SYSDATE;
l_bank_acct_rec.status := 'A';
IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT ( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_ext_bank_acct_rec => l_bank_acct_rec,
x_acct_id => l_acct,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response );
6. API to create joint account if more than one party own the Bank Account
IBY_EXT_BANKACCT_PUB.CHECK_BANK_ACCT_OWNER (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_bank_acct_id => NVL(l_acct_id,l_acct),
p_acct_owner_party_id => rec_cust_bank.acct_owner_party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response
);
IF x_return_status = fnd_api.g_ret_sts_error THEN
fnd_file.put_line(fnd_file.LOG,'Current Party is not an owner');
IBY_EXT_BANKACCT_PUB.ADD_JOINT_ACCOUNT_OWNER(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_bank_account_id => NVL(l_acct_id,l_acct),
p_acct_owner_party_id => rec_cust_bank.acct_owner_party_id,
x_joint_acct_owner_id => x_joint_owner_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response);
API to set Payer Instruction
l_instrument.Instrument_Id := NVL(l_acct_id,l_acct);
l_instrument.Instrument_Type := NVL(rec_cust_bank.Instrument_Type,'BANKACCOUNT'); --'BANKACCOUNT';
l_assignment_attribs.priority := 1;
l_assignment_attribs.start_date := TO_DATE('01-JAN-2000');
l_assignment_attribs.Instrument := l_instrument;
fnd_file.put_line(fnd_file.LOG, 'Before calling api Set_Payer_Instr_Assignment');
IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment ( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer,
p_assignment_attribs => l_assignment_attribs,
x_assign_id => l_assign_id,
x_response => x_response);
7. API to Create debit authorization
Debit cards: The authorization process validates the debit card information and debits the third-party payer's bank account immediately. The first party payee may receive the funds at this time. Some payment systems require a separate settlement step to move funds to the first party payee
BEGIN
SELECT hcsua.site_use_id,
hca.account_number
INTO l_site_use_id,
l_cust_acct_number
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
WHERE hca.orig_system_reference = rec_cust_bank.orig_system_customer_ref
AND hcasa.cust_account_id = hca.cust_account_id
AND hcasa.orig_system_reference = rec_cust_bank.orig_system_address_ref
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.orig_system_reference = rec_cust_bank.orig_system_address_ref
AND hcsua.site_use_code = 'BILL_TO';
EXCEPTION
WHEN OTHERS THEN
l_site_use_id := NULL;
l_cust_acct_number :=NULL;
l_err_flag := 'Y';
fnd_file.put_line(fnd_file.log,'Error While getting bill_to site use id in Customer Direct Debit Authorization.');
END;
fnd_file.put_line(fnd_file.log,'l_site_use_id : '||l_site_use_id);
BEGIN
SELECT DISTINCT ood.legal_entity
INTO l_legal_entity_id
FROM org_organization_definitions ood,
hr_operating_units hou
WHERE 1=1
AND ood.operating_unit = hou.organization_id
AND UPPER(hou.name) = UPPER(rec_cust_bank.operating_unit_name) ;
EXCEPTION
WHEN OTHERS THEN
l_legal_entity_id := NULL;
l_err_flag := 'Y';
fnd_file.put_line(fnd_file.log,'Error While getting Legal Entity Id');
END;
IF l_legal_entity_id IS NOT NULL THEN
BEGIN
SELECT entityprofileeo.legal_entity_id,
entityprofileeo.name,
reg.registration_number
INTO l_legal_entity_id,
l_le_name,
l_le_registration_no
FROM xle_entity_profiles entityprofileeo,
hz_geographies hg,
hz_parties hp,
xle_registrations reg,
hr_locations loc,
xle_jurisdictions_b jur,
fnd_territories_vl ft,
xle_lookups lkp
WHERE 1=1
AND hg.geography_id = entityprofileeo.geography_id
AND hp.party_id = entityprofileeo.party_id
AND reg.source_id = entityprofileeo.legal_entity_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.location_id = loc.location_id
AND reg.jurisdiction_id = jur.jurisdiction_id
AND ft.territory_code = loc.country
AND lkp.lookup_type = 'XLE_YES_NO'
AND lkp.lookup_code = entityprofileeo.transacting_entity_flag
AND entityprofileeo.legal_entity_id = l_legal_entity_id;
EXCEPTION
WHEN OTHERS THEN
l_legal_entity_id := NULL;
l_le_name := NULL;
l_le_registration_no := NULL;
l_err_flag := 'Y';
fnd_file.put_line(fnd_file.log,'Error While getting Legal Entity Details');
END;
END IF; --- end if l_legal_entity_id is not null
BEGIN
SELECT COUNT(*)
INTO l_debit_auth_cnt
FROM iby_debit_authorizations ida
WHERE 1=1
AND ida.cust_addr_id = l_site_use_id
AND ida.external_bank_account_use_id = l_assign_id;
EXCEPTION
WHEN OTHERS THEN
l_debit_auth_cnt := NULL;
fnd_file.put_line(fnd_file.log,'Error While Checking exiting Debit Authorization');
END;
BEGIN
SELECT IBY_DEBIT_AUTHORIZATIONS_S.NEXTVAL
INTO l_debit_auth_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error While Getting Max debit Auth Id');
l_debit_auth_id := NULL;
l_err_flag := 'Y';
END;
IF l_err_flag = 'N' AND l_debit_auth_cnt = 0 THEN
BEGIN
iby_fndcpt_setup_pub.Create_Debit_Authorization
( p_debit_auth_id => l_debit_auth_id,
p_bank_use_id => l_assign_id,
p_auth_ref_number => l_cust_acct_number,
p_initial_debit_auth_id => l_debit_auth_id,
p_auth_rev_number => 1,
p_payment_code => 'REC',
p_amend_readon_code => NULL,
p_auth_sign_date => SYSDATE,
p_auth_cancel_date => NULL,
p_debit_auth_method => NULL,
p_pre_notif_flag => 'N',
p_creditor_id => l_legal_entity_id, --- IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LEGAL_ENTITY_ID%TYPE,
p_creditor_name => l_le_name, --IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LE_NAME%TYPE,
p_debit_auth_begin => SYSDATE, --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_BEGIN%TYPE,
p_cust_addr_id => l_site_use_id, --IN IBY_DEBIT_AUTHORIZATIONS.CUST_ADDR_ID%TYPE,
p_debit_auth_flag => 'Y', --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_FLAG%TYPE,
p_debit_auth_ref => NULL, --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_REFERENCE%TYPE,
p_cust_id_code => NULL, --IN IBY_DEBIT_AUTHORIZATIONS.CUST_IDENTIFICATION_CODE%TYPE,
p_creditor_identifer => 'ES75ZZZB84265172', --IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_IDENTIFIER%TYPE,
p_debit_auth_end => NULL, --IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_END%TYPE,
p_mandate_file => NULL, --IN IBY_DEBIT_AUTHORIZATIONS.MANDATE_FILE%TYPE,
x_result => x_msg_count --OUT NOCOPY NUMBER
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
l_err_flag := 'Y';
fnd_file.put_line(fnd_file.log,'Error While Creating Direct Debit Authorization');
END;
END IF;
END IF ; --- END IF MAIN DEBIT AUTHORIZATION
No comments:
Post a Comment