CONTENT UPLOAD AND USER PROFILE CONFIGURATION GUIDE (Day 1)

This document includes following topics:

Creating folder for holding files
Creating global database variable
Creating procedure
Verifying db state before procedure execution
Executing procedure
Verifying db state after procedure execution
Delete created directory, variable and procedure
User Profile Configuration
ATM/Branch Locator

Creating folder for holding files

Create a directory (Example - ’/scratch/contents’) on server where db is installed. This directory will hold files which are to be uploaded.
Copy files in the directory created in above step

Note: We are not providing files out of the box. Please provide files for different documents as per your requirement. Makes sure that proper file names use during procedure execution (Refer to section Executing procedure > ‘Proc Execution Statement’ for more detail)

Creating global database variable

Login into database using sys user.
Create db variable (CONTENT_FILES) as mentioned below:
create or replace directory CONTENT_FILES as '/scratch/contents';
Granting access to schema (OBDXSCHEMA – Example- OBDX_DBAUTH181IT) for variable created in step 2 of above.

GRANT READ, WRITE ON DIRECTORY CONTENT_FILES TO &&schema_name;

Creating Procedure

Login into database using schema user (OBDXSCHEMA).
Compile following procedure.

For Origination Disclosure Configuration:

create or replace procedure add_disclosure(

p_file_path in varchar2,

p_file_name in varchar2,

p_mime_type in varchar2,

p_disclosure_code in varchar2,

p_disclosure_name in varchar2,

p_disclosure_desc in varchar2,

p_product_subclass in varchar2)

as

l_blob        blob;

l_file_content             bfile := bfilename(p_file_path, p_file_name);

l_max_val number;

begin

select NVL((max(ID)+1),1) into l_max_val from DIGX_CM_CONTENT;

Insert into DIGX_CM_CONTENT (ID,DOCUMENT_CHECKLIST_ID,TITLE,MIME_TYPE,CONTENT_CATEGORY,CONTENT_TYPE,CONTENT_SIZE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_STATUS,PARTY_ID,SHARED_FLAG) values

(l_max_val,'1',p_disclosure_name,p_mime_type,null,'FILE',6,null,'obdxuser',sysdate,null,null,null,null,null);

insert into DIGX_OR_SUBMISSION_DISCLOSURES (PRODUCT_CLASS, PRODUCT_SUBCLASS, NAME, CONTENT_ID, DESCRIPTION, REFERENCE_ID, DISCLOSURE_CODE)

values ('LOANS', p_product_subclass, p_disclosure_name, l_max_val, p_disclosure_desc, l_max_val, p_disclosure_code);

update DIGX_CM_CONTENT set FILE_CONTENT = empty_blob() WHERE ID = l_max_val;

SELECT FILE_CONTENT into l_blob FROM DIGX_CM_CONTENT WHERE ID = l_max_val;

dbms_lob.open(l_file_content, dbms_lob.lob_readonly);

dbms_lob.open(l_blob, dbms_lob.lob_readwrite);

dbms_lob.loadfromfile(dest_lob      => l_blob,

src_lob       => l_file_content,

amount        => dbms_lob.getlength(l_file_content));

dbms_lob.close(l_file_content);

dbms_lob.close(l_blob);

commit;

end;

For User Print Document Configuration:

create or replace procedure add_content(

p_file_path in varchar2,

p_file_name in varchar2,

p_mime_type in varchar2,

p_document_name in varchar2

as

l_blob        blob;

l_file_content             bfile := bfilename(p_file_path, p_file_name);

l_max_val number;

begin

select NVL((max(ID)+1),1) into l_max_val from DIGX_CM_CONTENT;

Insert into DIGX_CM_CONTENT (ID,DOCUMENT_CHECKLIST_ID,TITLE,MIME_TYPE,CONTENT_CATEGORY,CONTENT_TYPE,CONTENT_SIZE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_STATUS,PARTY_ID,SHARED_FLAG) values

(l_max_val,'1',p_document_name,p_mime_type,null,'FILE',6,null,'obdxuser',sysdate,null,null,null,null,null);

insert into DIGX_UM_USERPRINTDOCS(CODE, NAME)values (l_max_val, p_document_name);

update DIGX_CM_CONTENT set FILE_CONTENT = empty_blob() WHERE ID = l_max_val;

SELECT FILE_CONTENT into l_blob FROM DIGX_CM_CONTENT WHERE ID = l_max_val;

dbms_lob.open(l_file_content, dbms_lob.lob_readonly);

dbms_lob.open(l_blob, dbms_lob.lob_readwrite);

dbms_lob.loadfromfile(dest_lob      => l_blob,

src_lob       => l_file_content,

amount        => dbms_lob.getlength(l_file_content));

dbms_lob.close(l_file_content);

dbms_lob.close(l_blob);

commit;

end;

For Feedback

Pre- requisites before compiling below procedure:

Execute below steps to make Scale(Rating) icons available for Feedback Module.

Note: The images can be found at below location : clip/trunk/core/channel/images/feedback

Insert Query for DIGX_CM_CONTENT Table

Insert into DIGX_CM_CONTENT (ID,PARTY_ID,DOCUMENT_CHECKLIST_ID,TITLE,MIME_TYPE,CONTENT_CATEGORY,CONTENT_TYPE,FILE_CONTENT,CONTENT_SIZE,SHARED_FLAG,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_STATUS) values (‘${contentId1}’,null,null,'heart-fill.svg','image/svg',null,null, null,'','Y',1,'superadmin',sysdate,'superadmin',sysdate,null);

Insert into DIGX_CM_CONTENT (ID,PARTY_ID,DOCUMENT_CHECKLIST_ID,TITLE,MIME_TYPE,CONTENT_CATEGORY,CONTENT_TYPE,FILE_CONTENT,CONTENT_SIZE,SHARED_FLAG,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_STATUS) values (‘${contentId2}’,null,null,'star.png','image/jpeg',null,null, null,'','Y',1,'superadmin',sysdate,'superadmin',sysdate,null);

Insert into DIGX_CM_CONTENT (ID,PARTY_ID,DOCUMENT_CHECKLIST_ID,TITLE,MIME_TYPE,CONTENT_CATEGORY,CONTENT_TYPE,FILE_CONTENT,CONTENT_SIZE,SHARED_FLAG,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_STATUS) values (‘${contentId3}’,null,null,'thumbs-up.svg','image/jpeg',null,null, null,'','Y',1,'superadmin',sysdate,'superadmin',sysdate,null);

Update the contented in digx_fd_scale table.

update  digx_fd_scale set content_id=‘${contentId1}’ where determinant_value in  ('*','OBDX_BU Business Unit') and  id='1';

update  digx_fd_scale set content_id=‘${contentId2}’ where determinant_value in  ('*','OBDX_BU') and  id='2';

update  digx_fd_scale set content_id=‘${contentId3}’ where determinant_value in  ('*','OBDX_BU') and  id='3';

Procedure:

create or replace procedure load_file_to_content( p_file_path in varchar2, p_file_name in varchar2, p_content_id in varchar2 )

l_blob        blob;

l_file_content             bfile := bfilename(p_file_path, p_file_name);

begin

update DIGX_CM_CONTENT set FILE_CONTENT = empty_blob() WHERE ID = p_content_id;

SELECT FILE_CONTENT into l_blob FROM DIGX_CM_CONTENT WHERE ID = p_content_id;

dbms_lob.open(l_file_content, dbms_lob.lob_readonly);

dbms_lob.open(l_blob, dbms_lob.lob_readwrite);

dbms_lob.loadfromfile(dest_lob      => l_blob,

src_lob       => l_file_content,

amount        => dbms_lob.getlength(l_file_content));

dbms_lob.close(l_file_content);

dbms_lob.close(l_blob);

commit;

end;

Verifying DB State Before Procedure Execution

Login into database using schema (OBDXSCHEMA) user.
Confirm data by executing below queries; before executing day1 scripts

For Origination Disclosure Configuration:

select *  from DIGX_CM_CONTENT;

select *  from DIGX_OR_SUBMISSION_DISCLOSURES;

For User Print Document Configuration:

select *  from DIGX_CM_CONTENT;

select  *  from DIGX_UM_USERPRINTDOCS;

For Feedback

select file_content from DIGX_CM_CONTENT where ID IN (' ‘${contentId1} ',' ‘${contentId2} ',' ‘${contentId3} ');

Note: All cells should be null or with empty BLOB

Executing procedure

Login into database using schema user (OBDXSCHEMA).
Template for procedure call is as given below

For Origination Disclosure Configuration:

begin

add_disclosure (

<Variable name for File’s registered path>,

<Files’ Name>,

<CONTENT’S MIME TYPE>,

<DISCLOSURE CODE>,

<DISCLOSURE NAME>,

<DISCLOSURE DESCRIPTION>,

<PRODUCT SUBCLASS>

);

end;

For User Print Document Configuration:

begin

add_content (

<Variable name for File’s registered path>,

<Files’ Name>,

<CONTENT’S MIME TYPE>,

<DOCUMENT NAME>

);

end;

For Feedback

begin

load_file_to_content(

<Variable name for File’s registered path>,

<Files’ Name>,

<Content ID>

);

end;

Execute commands given under column ‘Proc Execution Statement’

For Origination Disclosure Configuration:

Sub-Class	Code	Name	Description	MIME Type	Proc Execution Statement
AUTOLOANFLL	ESIGNDISCLOSURE	E-SIGN Disclosure	E-SIGN_ Disclosure	application/pdf	begin add_disclosure( 'CONTENT_FILES', 'fileName1.pdf', 'application/pdf', 'ESIGNDISCLOSURE', 'E-SIGN Disclosure', 'E-SIGN_Disclosure', 'AUTOLOANFLL' ); end; /
AUTOLOANFLL	PRIVACYPOLICY	Privacy Policy	Privacy_ Policy	application/pdf	begin add_disclosure( 'CONTENT_FILES', 'fileName2.pdf', 'application/pdf', 'PRIVACYPOLICY', 'Privacy Policy', 'Privacy_Policy', 'AUTOLOANFLL' ); end; /
AUTOLOANFLL	LOANACCOUNTAGREEMENT	Loan Account Agreement	Loan_ Account_Agreement	application/pdf	begin add_disclosure( 'CONTENT_FILES', 'fileName3.pdf', 'application/pdf', 'LOANACCOUNTAGREEMENT', 'Loan Account Agreement', 'Loan_Account_Agreement', 'AUTOLOANFLL' ); end; /
PAYDAY	ESIGNDISCLOSURE	E-SIGN Disclosure	E-SIGN_ Disclosure	application/pdf	begin add_disclosure( 'CONTENT_FILES', 'fileName4.pdf', 'application/pdf', 'ESIGNDISCLOSURE', 'E-SIGN Disclosure', 'E-SIGN_Disclosure', 'PAYDAY'); end; /
PAYDAY	PRIVACYPOLICY	Privacy Policy	Privacy_ Policy	application/pdf	begin add_disclosure( 'CONTENT_FILES', 'fileName5.pdf', 'application/pdf', 'PRIVACYPOLICY', 'Privacy Policy', 'Privacy_Policy', 'PAYDAY'); end; /
PAYDAY	LOANACCOUNTAGREEMENT	Loan Account Agreement	Loan_ Account_Agreement	application/pdf	begin add_disclosure( 'CONTENT_FILES', 'fileName6.pdf', 'application/pdf', 'LOANACCOUNTAGREEMENT', 'Loan Account Agreement', 'Loan_Account_Agreement', 'PAYDAY'); end; /

Note: fileName1.pdf - fileName6.pdf must be present at the location mentioned in step 1.1.2.

For User Print Document Configuration:

Name	MIME Type	Proc Execution Statement
Promotional Offer	application/pdf	begin add_content( 'CONTENT_FILES', 'PromotionalOffer.pdf', 'application/pdf', 'Promotional Offer Offers are different variants of a product offered to the customer. Usually, the behavioral characteristics of the product remain the same, while the marketing attributes differ among offers.'); end; /
Terms and Conditions	application/pdf	begin add_content( 'CONTENT_FILES', 'TnC.pdf', 'application/pdf', 'Terms and Conditions'); end; /
Welcome Letter	application/pdf	begin add_content( 'CONTENT_FILES', 'WelcomeLetter.pdf', 'application/pdf', 'Welcome Letter'); end; /

Name

MIME Type

Proc Execution Statement

Promotional Offer

application/pdf

begin

add_content(

'CONTENT_FILES',

'PromotionalOffer.pdf',

'application/pdf',

'Promotional Offer Offers are different variants of a product offered to the customer. Usually, the behavioral characteristics of the product remain the same, while the marketing attributes differ among offers.');

end;

Terms and Conditions

application/pdf

begin

add_content(

'CONTENT_FILES',

'TnC.pdf',

'application/pdf',

'Terms and Conditions');

end;

Welcome Letter

application/pdf

begin

add_content(

'CONTENT_FILES',

'WelcomeLetter.pdf',

'application/pdf',

'Welcome Letter');

end;

Note: PromotionalOffer.pdf - TnC.pdf- WelcomeLetter.pdf must be present at the location mentioned in step 1.1.2.

For Feedback:

Proc Execution Statement :

Name	Proc Execution Statement
Heart-fill.svg	begin load_file_to_content('CONTENT_FILES','heart-fill.svg', ‘${contentId1}’); end; /
Star.png	begin load_file_to_content('CONTENT_FILES','star.png ', ‘${contentId2}’); end; /
Thumbs-up.svg	begin load_file_to_content('CONTENT_FILES','thumbs-up.svg ', ‘${contentId3}’); end; /

Name

Proc Execution Statement

Heart-fill.svg

begin
  load_file_to_content('CONTENT_FILES','heart-fill.svg', ‘${contentId1}’);
end;
/

Star.png

begin
load_file_to_content('CONTENT_FILES','star.png ', ‘${contentId2}’);
end;
/

Thumbs-up.svg

begin
load_file_to_content('CONTENT_FILES','thumbs-up.svg ', ‘${contentId3}’);
end; 
/

Verifying DB State After Procedure Execution

Login into database using schema user (OBDXSCHEMA).
Confirm date if the data is properly inserted into DB by executing below queries

For Origination Disclosure Configuration:

select *  from DIGX_CM_CONTENT;

select *  from DIGX_OR_SUBMISSION_DISCLOSURES;

For User Print Document Configuration:

select *  from DIGX_CM_CONTENT;

select  *  from DIGX_UM_USERPRINTDOCS;

For Feedback

select file_content from DIGX_CM_CONTENT where ID IN (' ‘${contentId1} ',' ‘${contentId2} ',' ‘${contentId3} ');

Note: All cells should contain BLOB

Delete created directory, variable and procedure

Delete the directory variable, created in step 2 of 1.2(with sys user).
drop directory CONTENT_FILES;
Delete the directory, created in step 1 of 1.1.
Drop the procedure, created in step 1.3.

For Origination Disclosure Configuration:

drop procedure add_disclosure;

For User Print Document Configuration:

drop procedure add_content;

For Feedback

drop procedure load_file_to_content;

User Profile Configuration

In order to configure fields available in User Profile (My Profile screen) for User type Retail, for each entity, below scripts have to be executed. Bank can also define if the fields which are being displayed on User Profile screen should be editable or non-editable by Retail user.

Please note that this is only applicable for User Type- Retail

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,

CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values

('faxNo',<Description of fieldname>,<entity_id>,<IS_EDITABLE>,'ofssuser', sysdate, 'ofssuser', sysdate,1);

<entity_id> will be replaced by the ID of entity for which the configuration is to be done.

<IS_EDITABLE> will be replaced either by 'Y' or 'N'.

If Bank wants that field should be available for the user to update in the profile section, replace it with 'Y' else 'N'.

<Description of fieldname> is the description which you want to be displayed on the UI User Interface of Profile.

FIELD_NAME ,for now, can only be: faxNo,adhaarCardNo,panCardNo,address,email,phoneno

EXAMPLE:

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,CREATION_DATE,

LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values ('adhaarCardNo','Aadhaar Card Number','OBDXBU2',

'N','ofssuser', sysdate, 'ofssuser', sysdate,1);

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,CREATION_DATE,

LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values ('panCardNo','Pan Card Number','OBDXBU2','N',

'ofssuser', sysdate, 'ofssuser', sysdate,1);

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,CREATION_DATE

,LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values ('address','Communication Address','OBDXBU2',

'N','ofssuser', sysdate, 'ofssuser', sysdate,1);

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,CREATION_DATE,

LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values ('email','Email ID','OBDXBU2','N','ofssuser',

sysdate, 'ofssuser', sysdate,1);

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,CREATION_DATE,

LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values ('phoneno','Contact Number(Mobile)','OBDXBU2','N',

'ofssuser', sysdate, 'ofssuser', sysdate,1);

Insert into DIGX_UM_PROFILE_CONFIG (FIELD_NAME,DISPLAY_VALUE,DETERMINANT_VALUE,EDITABLE,CREATED_BY,CREATION_DATE,

LAST_UPDATED_BY,LAST_UPDATED_DATE,OBJECT_VERSION_NUMBER) values ('faxNo','Fax Number','OBDXBU2','N','ofssuser',

 sysdate, 'ofssuser', sysdate,1);

ATM/Branch Locator

3.1 Maintaining Default Radius

Default Radius of ATM Automated Teller Machine terminal allows customers having an ATM or debit card to perform several banking transactions such as cash withdrawal, balance inquiry, funds transfer, and so on./Branch Search is 2.5 Kms which can be changed by updating the value of DEFAULT_SEARCH_RADIUS in digx_fw_config_all_b. The updated value will be in effect post server restart.

Home