DECLARE
x_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
x_bank_id NUMBER;
x_return_status VARCHAR2(10);
x_msg_count number;
x_msg_data varchar2(256);
x_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
x_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
x_branch_id number;
x_acct_id number;
x_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
p_ext_payee_tab IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Tab_Type ;
x_ext_payee_id_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Tab_Type ;
x_ext_payee_status_tab IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_Create_Tab_Type ;
l_ext_payee_rec IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type ;
l_ext_payee_id_rec_type IBY_DISBURSEMENT_SETUP_PUB.Ext_Payee_ID_Rec_Type ;
p_payee IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type ;
p_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type ;
p_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type ;
x_assign_id number;
V_BANK_PARTY_ID NUMBER;
V_BANK_CREATION_FLAG VARCHAR2(40);
p_BANK_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_BRANCH_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_BANK_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
p_BRANCH_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2(2000);
x_location_id NUMBER;
V_COUNT NUMBER;
V_ERROR_FLAG VARCHAR2(40);
V_ERROR_MESSAGE VARCHAR2(4000);
v_TERRITORY_CODE VARCHAR2(40);
CURSOR SUPPLIER_BANK IS
SELECT ROWID, xrs.* FROM XXAP__SUPPLIER_BANK_STG
XRS
WHERE PROCESSED_FLAG = 'N'
AND ACTION_TYPE='INSERT';
begin
dbms_output.put_line('Start of procedure.');
fnd_global.apps_initialize (fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id
);
dbms_output.put_line('Procedure initializad.');
FOR I IN SUPPLIER_BANK LOOP
V_BANK_CREATION_FLAG:='N';
V_ERROR_FLAG:='N';
V_ERROR_MESSAGE:=NULL;
v_TERRITORY_CODE :=NULL;
x_branch_id:=0;
x_bANK_id:=0;
x_LOCATION_id:=0;
V_BANK_PARTY_ID:=0;
begin
select TERRITORY_CODE into v_TERRITORY_CODE from fnd_territories
where UPPER(TERRITORY_CODE)=UPPER(i.BANK_COUNTRY);
exception when others then
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Bank country not exsist';
end;
BEGIN
SELECT BANK_PARTY_ID INTO V_BANK_PARTY_ID FROM CE_BANKS_V
WHERE UPPER(BANK_NAME)=UPPER(I.BANK_NAME)
AND UPPER(HOME_COUNTRY)=UPPER(v_TERRITORY_CODE)
AND ROWNUM=1;
EXCEPTION WHEN NO_DATA_FOUND THEN
x_bank_rec.bank_name := I.BANK_NAME;
x_bank_rec.bank_alt_name:=I.BANK_NAME_ALT;
x_bank_rec.bank_number := I.BANK_NUM;
x_bank_rec.institution_type := 'BANK'; -- hz_code_assignments
.CLASS_CODE
x_bank_rec.country_code :=I.BANK_COUNTRY ;
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
dbms_output.put_line('before External bank creation.');
IBY_EXT_BANKACCT_PUB.create_ext_bank
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_rec => x_bank_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_rec
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('External bank created.');
else
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:='Creation of BANK failed:'||x_msg_data;
END IF;
IF V_ERROR_FLAG='N' THEN
BEGIN
p_bank_location_rec.country := I.BANK_COUNTRY;
p_bank_location_rec.address1 := I.BANK_ADDRESS_1;
p_bank_location_rec.address2 := I.BANK_ADDRESS_2;
p_bank_location_rec.address3 := I.BANK_ADDRESS_3;
p_bank_location_rec.city := I.BANK_CITY;
p_bank_location_rec.postal_code := I.BANK_POSTAL_CODE;
p_bank_location_rec.state := I.BANK_STATE;
p_bank_location_rec.created_by_module := 'BO_API';
DBMS_OUTPUT.PUT_LINE('Calling
the API hz_location_v2pub.create_location');
HZ_LOCATION_V2PUB.CREATE_LOCATION
(
p_init_msg_list => FND_API.G_TRUE,
p_location_rec => p_bank_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);
IF x_return_status = fnd_api.g_ret_sts_success THEN
--COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('x_location_id: '||x_location_id);
DBMS_OUTPUT.PUT_LINE('x_return_status: '||x_return_status);
DBMS_OUTPUT.PUT_LINE('x_msg_count: '||x_msg_count);
DBMS_OUTPUT.PUT_LINE('x_msg_data: '||x_msg_data);
ELSE
DBMS_OUTPUT.put_line ('Creation of Location
failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK
ADDRESS FAIL'||x_msg_data;
V_ERROR_FLAG:='Y';
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
--
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK
ADDRESS FAIL'||x_msg_data;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
IF V_ERROR_FLAG='N' THEN
IF x_bank_id>0 AND x_location_id>0 THEN
BEGIN
--
Setting the Context --
--mo_global.init('AR');
--d_global.set_nls_context('AMERICAN');
--
Initializing the Mandatory API parameters
p_BANK_party_site_rec.party_id := x_bank_id;
p_BANK_party_site_rec.location_id := x_location_id;
p_BANK_party_site_rec.identifying_address_flag := 'Y';
p_BANK_party_site_rec.created_by_module := 'BO_API';
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_BANK_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
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
--
COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Party Site is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('Party Site Id = '||x_party_site_id);
DBMS_OUTPUT.PUT_LINE('Party Site Number = '||x_party_site_number);
ELSE
DBMS_OUTPUT.put_line ('Creation of Party Site
failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK
ADDRESS FAIL'||x_msg_data;
ROLLBACK;
V_ERROR_FLAG:='Y';
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
END IF;
V_BANK_PARTY_ID:=x_bank_id;
IF NVL(x_bank_id,0)>0 THEN
V_BANK_CREATION_FLAG:='Y';
END IF;
END;
IF V_ERROR_FLAG='N' THEN
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM CE_BANK_BRANCHES_V
where UPPER(BANK_BRANCH_NAME)=UPPER(I.BRANCH_NAME)
AND UPPER(BANK_NAME)=UPPER(I.BANK_NAME)
AND UPPER(BANK_HOME_COUNTRY)=UPPER(v_TERRITORY_CODE)
AND ROWNUM=1;
EXCEPTION WHEN OTHERS THEN
V_COUNT:=0;
END;
IF V_COUNT>0 AND V_BANK_CREATION_FLAG='N' THEN
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'BANK AND BRANCH IS
ALREADY EXSIST';
END IF;
IF V_COUNT=0 THEN
x_bank_branch_rec.bank_party_id := V_BANK_PARTY_ID ;
x_bank_branch_rec.branch_name := I.BRANCH_NAME ;
x_bank_branch_rec.alternate_branch_name:=i.BANK_BRANCH_NAME_ALT;
x_bank_branch_rec.branch_number := I.BRANCH_NUM ;
x_bank_branch_rec.bic:=I.BIC;
x_bank_branch_rec.branch_type := 'OTHER' ; --defined in lookup as BANK BRANCH
TYPE
x_msg_count := 0;
x_msg_data := null;
x_return_status := null;
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 => x_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_rec
);
dbms_output.put_line('External bank Branch
created.');
dbms_output.put_line('x_branch_id: '||x_branch_id);
dbms_output.put_line('x_return_status: '||x_return_status);
dbms_output.put_line('x_msg_count. '||x_msg_count);
dbms_output.put_line('x_msg_data. '||x_msg_data);
IF x_return_status = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('External bank BRANCH created.');
else
V_ERROR_FLAG:='Y';
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BANK BRANCH
failed:'||x_msg_data;
END IF;
END IF;
END IF;
IF V_ERROR_FLAG='N' THEN
BEGIN
p_BRANCH_location_rec.country := I.BRANCH_COUNTRY;
p_BRANCH_location_rec.address1 := I.BRANCH_ADDRESS_1;
p_BRANCH_location_rec.address2 := I.BRANCH_ADDRESS_2;
p_BRANCH_location_rec.address3 := I.BRANCH_ADDRESS_3;
p_BRANCH_location_rec.city := I.BRANCH_CITY;
p_BRANCH_location_rec.postal_code := I.BRANCH_POSTAL_CODE;
p_BRANCH_location_rec.state := I.BRANCH_STATE;
p_BRANCH_location_rec.created_by_module := 'BO_API';
DBMS_OUTPUT.PUT_LINE('Calling
the API hz_location_v2pub.create_location');
HZ_LOCATION_V2PUB.CREATE_LOCATION
(
p_init_msg_list => FND_API.G_TRUE,
p_location_rec => p_BRANCH_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);
IF x_return_status = fnd_api.g_ret_sts_success THEN
--COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('x_location_id: '||x_location_id);
DBMS_OUTPUT.PUT_LINE('x_return_status: '||x_return_status);
DBMS_OUTPUT.PUT_LINE('x_msg_count: '||x_msg_count);
DBMS_OUTPUT.PUT_LINE('x_msg_data: '||x_msg_data);
ELSE
DBMS_OUTPUT.put_line ('Creation of Location
failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BRANCH
ADDRESS FAIL'||x_msg_data;
V_ERROR_FLAG:='Y';
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
IF V_ERROR_FLAG='N' THEN
IF x_branch_id>0 AND x_location_id>0 THEN
BEGIN
--
Setting the Context --
--mo_global.init('AR');
--d_global.set_nls_context('AMERICAN');
--
Initializing the Mandatory API parameters
p_BRANCH_party_site_rec.party_id := x_branch_id;
p_BRANCH_party_site_rec.location_id := x_location_id;
p_BRANCH_party_site_rec.identifying_address_flag := 'Y';
p_BRANCH_party_site_rec.created_by_module := 'BO_API';
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_BRANCH_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
);
IF x_return_status = fnd_api.g_ret_sts_success THEN
--
COMMIT;
DBMS_OUTPUT.PUT_LINE('Creation of Party Site is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');
DBMS_OUTPUT.PUT_LINE('Party Site Id = '||x_party_site_id);
DBMS_OUTPUT.PUT_LINE('Party Site Number = '||x_party_site_number);
ELSE
DBMS_OUTPUT.put_line ('Creation of Party Site
failed:'||x_msg_data);
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Creation of BRANCH
ADDRESS FAIL'||x_msg_data;
ROLLBACK;
V_ERROR_FLAG:='Y';
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
END IF;
END IF;
IF V_ERROR_FLAG='Y' THEN
ROLLBACK;
UPDATE XXAP__SUPPLIER_BANK_STG
SET PROCESSED_FLAG = 'N',
ERROR_DESCRIPTION = V_ERROR_MESSAGE
WHERE ROWID = I.ROWID;
COMMIT;
ELSE
UPDATE XXAP_SUPPLIER_BANK_STG
SET PROCESSED_FLAG = 'Y'
WHERE ROWID = I.ROWID;
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
END IF;
No comments:
Post a Comment