CONTENT UPLOAD 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
• 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 step 1 of 1.1.

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 1.5.3 column ‘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 1.2.

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;

/

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;

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;

/

• Execute commands given under column ‘Proc Execution Statement’

For Origination Disclosure Configuration:

begin

add_disclosure(

'CONTENT_FILES',

'fileName1.pdf',

'application/pdf',

'ESIGNDISCLOSURE',

'E-SIGN Disclosure',

'E-SIGN_Disclosure',

'AUTOLOANFLL' );

end;

/

begin

add_disclosure(

'CONTENT_FILES',

'fileName2.pdf',

'application/pdf',

'PRIVACYPOLICY',

'Privacy Policy', 'Privacy_Policy',

'AUTOLOANFLL' );

end;

/

begin

add_disclosure(

'CONTENT_FILES',

'fileName3.pdf',

'application/pdf', 'LOANACCOUNTAGREEMENT',

'Loan Account Agreement', 'Loan_Account_Agreement',

'AUTOLOANFLL' );

end;

/

begin

add_disclosure(

'CONTENT_FILES',

'fileName4.pdf',

'application/pdf',

'ESIGNDISCLOSURE',

'E-SIGN Disclosure',

'E-SIGN_Disclosure',

'PAYDAY');

end;

/

begin

add_disclosure(

'CONTENT_FILES',

'fileName5.pdf',

'application/pdf', 'PRIVACYPOLICY',

'Privacy Policy',

'Privacy_Policy',

'PAYDAY');

end;

/

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:

begin

add_content(

'CONTENT_FILES',

'PromotionalOffer.pdf',

'application/pdf',

'Promotional OfferOffers 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;

/

begin

add_content(

'CONTENT_FILES',

'TnC.pdf',

'application/pdf',

'Terms and Conditions');

end;

/

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.

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;

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;

ATM/Branch Locator

3.1 Maintaining Default Radius

Default Radius of ATMAutomated 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