Credit Decisioning

Banking

Banking Details	Bank Account 1	Key Mapping	Remarks
Bank Name	HDFC Bank Ltd.	Lapp.ecs_bank
Bank IFSC Code	HDFC0001563	Lapp.ecs_ifsc_code
MICR Code	654321	Lapp.ecs_micr_code
Account No.	123456789	Lapp.ecs_bank_acc_no
Type of Account	< Savings / Current >	Lapp.ecs_account_type
Account Holder Name	Rajesh K.	Lapp.ecs_cust_name
Address Line	1001, Tower 12, Amrit Society	Lapp.ecs_customer_address	New Key (Button to fetch and store current address details from lapp)
	Kalyaninagar, Pune – 411006
Contact No.	9970665990	Lapp.ecs_customer_contact_no	New key
Statement Start Date	01-10-2019		Runtime: fetch from bank_statement
Statement End Date	31-03-2020		Runtime: fetch from bank_statement
Tenure of Bank Statement (months)	6.00		Runtime: fetch from bank_statement
Is the Address as per Bank Statement, same as Current Residence address	< Yes / No >		Runtime: compare lapp.ecs_customer_address with lapp.current_address_line1 and  line 2
Bank Statement File Type / Source	pdf, jpeg, jpg, etc.		Runtime: fetch from file
Bank Statement password		Lapp.bs_password

Bank Statement Analysis	Bank Account 1
Tenure (months)	3 months
Instances of Cheque Bounce
Top 20 Credits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance`  FROM `bank_statement` where object_id=’APP1660398105608685′ and credit>0   and DATEDIFF (curdate(),CAST(txn_date  as date))<=90 order by CAST(credit AS UNSIGNED) desc limit 20
Top 20 Debits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id=’APP1660239898684120′ and debit>0 and DATEDIFF (curdate(),CAST(txn_date  as date))<=90 order by CAST(debit AS UNSIGNED) desc limit 20
Top 10 Keywords		[mysqli.fetch.rows o.set=template.keyword_data]     SELECT description FROM bank_statement where object_id='[env.@lapp.lapp.lapp_id/]’  and DATEDIFF (curdate(),str_to_date(txn_date,’%d-%m-%Y’))<=90   [/mysqli.fetch.rows]    [php.common_words p1='{template.keyword_data.rows}’ o.set=”template.keywords_arr” c.not_empty=”template.keyword_data.rows”/]   [template.set table.rows=”/]   [loop.@a template.keywords_arr]           [if.less_equal lhs={@a.index} rhs=’11’]                   [template.set_array table.rows.new category=”{@a.key}” total=”{@a.item}”/]     [/if.less_equal]   [/loop.@a]   [template.set keywords]                    [template.set table.column=” /]         [template.set_array table.column.new label=”Keyword” meta_key=”category”/] [template.set_array table.column.new label=”Total” meta_key=”total”/]                 [ui.table dataset=”{template.table}” args=”{template.table}” /]            [/template.set]
EMI outflow		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id='[env.@lapp.lapp.lapp_id/]’ and category=’Bounce’  and DATEDIFF (curdate(),CAST(txn_date  as date))<=90 order by txn_date desc
Last 20 IMPS Credits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id='[env.@lapp.lapp.lapp_id/]’ and description like “%IMPS%” and (debit-credit) > 0 and DATEDIFF (curdate(),CAST(txn_date  as date))<=90 order by txn_date desc limit 20
Last 20 IMPS Debits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id='[env.@lapp.lapp.lapp_id/]’ and description like “%IMPS%” and (credit-debit) > 0 and DATEDIFF (curdate(),CAST(txn_date  as date))<=90 order by txn_date desc limit 20

Bank Statement Analysis	Bank Account 1
Tenure (months)	6 months
Instances of Cheque Bounce
Top 20 Credits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance`  FROM `bank_statement` where object_id=’APP1660398105608685′ and credit>0   and DATEDIFF (curdate(),CAST(txn_date  as date))<=180 order by CAST(credit AS UNSIGNED) desc limit 20
Top 20 Debits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id=’APP1660239898684120′ and debit>0 and DATEDIFF (curdate(),CAST(txn_date  as date))<=180order by CAST(debit AS UNSIGNED) desc limit 20
Top 10 Keywords		[mysqli.fetch.rows o.set=template.keyword_data]     SELECT description FROM bank_statement where object_id='[env.@lapp.lapp.lapp_id/]’  and DATEDIFF (curdate(),str_to_date(txn_date,’%d-%m-%Y’))<=180   [/mysqli.fetch.rows]    [php.common_words p1='{template.keyword_data.rows}’ o.set=”template.keywords_arr” c.not_empty=”template.keyword_data.rows”/]   [template.set table.rows=”/]   [loop.@a template.keywords_arr]           [if.less_equal lhs={@a.index} rhs=’11’]                   [template.set_array table.rows.new category=”{@a.key}” total=”{@a.item}”/]     [/if.less_equal]   [/loop.@a]   [template.set keywords]                    [template.set table.column=” /]         [template.set_array table.column.new label=”Keyword” meta_key=”category”/] [template.set_array table.column.new label=”Total” meta_key=”total”/]                 [ui.table dataset=”{template.table}” args=”{template.table}” /]            [/template.set]
EMI outflow		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id='[env.@lapp.lapp.lapp_id/]’ and category=’Bounce’  and DATEDIFF (curdate(),CAST(txn_date  as date))<=180 order by txn_date desc
Last 20 IMPS Credits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id='[env.@lapp.lapp.lapp_id/]’ and description like “%IMPS%” and (debit-credit) > 0 and DATEDIFF (curdate(),CAST(txn_date  as date))<=180 order by txn_date desc limit 20
Last 20 IMPS Debits		SELECT `txn_date`,`description`,`debit`,`credit`,`balance` FROM `bank_statement` where object_id='[env.@lapp.lapp.lapp_id/]’ and description like “%IMPS%” and (credit-debit) > 0 and DATEDIFF (curdate(),CAST(txn_date  as date))<=180 order by txn_date desc limit 20

Sum of Credits / Inflow (Monthly)	Bank Account 1
Mar-20	65,000
Feb-20	67,000
Jan-20	69,000
Dec-19	89,000
Nov-19	91,000
Oct-19	93,000
Average Monthly Credit / Inflow	79,000

Inflow Query

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    drop temporary table if exists bank_data;

    create temporary table bank_data

    with

    q0 as (select object_id, txn_month from `bank_statement` where object_id=’APP1660045463328628′ group by txn_month),

    q1 as (select bs.txn_month, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, truncate(sum(credit),2) as balance from q0 join bank_statement as bs on q0.object_id=bs.object_id and q0.txn_month=bs.txn_month and bs.credit > 0 group by bs.txn_month order by txn_date)

    select label, balance  from q1;

    (select label, balance from bank_data)

    union

    (select ‘Average Bank Balance’ as label, truncate(avg(balance),2) as balance from bank_data)

Bank Account 1	Dates							Average
Bank Balance Analysis	3rd	5th	7th	10th	15th	20th	25th
Mar-20	2,000	3,000	4,000	5,000	6,000	7,000	8,000	5,000
Feb-20	60,000	63,000	66,000	69,000	72,000	75,000	78,000	69,000
Jan-20	70,000	73,000	76,000	79,000	82,000	85,000	88,000	79,000
Dec-19	85,000	88,000	91,000	94,000	97,000	1,00,000	1,03,000	94,000
Nov-19	65,000	68,000	71,000	74,000	77,000	80,000	83,000	74,000
Oct-19	90,000	93,000	96,000	99,000	1,02,000	1,05,000	1,08,000	99,000
Average Bank Balance	62,000	64,667	67,333	70,000	72,667	75,333	78,000	70,000

Query for Bank Balance Analysis

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    drop temporary table if exists btm;

    create temporary table btm

    with

    q0 as (select max(id) as row_id, bank_id, object_id from `bank_statement` where object_id=’APP1660045463328628′  group by bank_id ),

    q1 as (select min(row_id) as row_id, bank_id, object_id from q0 ),

    q2 as (select bs.object_id, txn_month, bs.bank_id from q1 join bank_statement as bs on q1.object_id=bs.object_id and q1.bank_id=bs.bank_id  group by txn_month )

    select * from q2;

    drop temporary table if exists 3rd_day;

    create temporary table 3rd_day (

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 3rd_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=3 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ‘3rd’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 1 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    drop temporary table if exists 5th_day;

    create temporary table 5th_day(

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 5th_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=5 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ‘5th’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 2 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    drop temporary table if exists 7th_day;

    create temporary table 7th_day(

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 7th_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=7 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ‘7th’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 3 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    drop temporary table if exists 10th_day;

    create temporary table 10th_day(

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 10th_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=10 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ’10th’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 4 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    drop temporary table if exists 15th_day;

    create temporary table 15th_day(

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 15th_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=15 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ’15th’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 5 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    drop temporary table if exists 20th_day;

    create temporary table 20th_day(

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 20th_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=20 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ’20th’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 6 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    drop temporary table if exists 25th_day;

    create temporary table 25th_day(

      `day` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `label` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `balance` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

      `txn_date` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL

    );

    insert ignore into 25th_day(day, label, balance, txn_date)

    with

    q0 as (select max(id) as row_id from bank_statement as bs join btm on bs.object_id=btm.object_id and bs.txn_month=btm.txn_month and bs.bank_id=btm.bank_id and txn_date <> ” and date_format(date(txn_date),’%d’)<=25 group by bs.txn_month),

    q1 as (select truncate(balance, 2) as balance, ’25th’ as day, bs.txn_date, DATE_FORMAT(bs.txn_date, ‘%b-%y’) as label, 7 as order_type from q0 join bank_statement as bs on q0.row_id=bs.id where bs.txn_date <> ”)

    select day, label, balance, txn_date from q1 order by txn_date, order_type;

    select * from 3rd_day

    union

    select * from 5th_day

    union

    select * from 7th_day

    union

    select * from 10th_day

    union

    select * from 15th_day

    union

    select * from 20th_day

    union

    select * from 25th_day;

Bureau

Complete data is extracted from XML of CIBIL  and CRIF

Bureau Report Analysis	CIBIL	CRIF
Customer Name	Capture as per Bureau	Capture as per Bureau
Dual PAN	<Yes / No>	<Yes / No>
–Customer PAN No. as per our system
–Dual PAN No.
Credit Score	712	689
Bureau Start date	01-10-2009	01-10-2013
Bureau Vintage (Years)	11	7
Current Residence Address updated  in Bureau	<Yes / No>	<Yes / No>	To be auto captured, based on comparison of Current Address and Bureau reported addresses in Contactability sheet   Not possible as bureau does not provide the required data
Date on which Address is updated			same as above
No. of enquiries
— last 30 Days	2	1
— last 3 Months	4	3	To be calculated based on the enquiry table at the end of CIBIL report
— last 6 Months	6	5	To be calculated based on the enquiry table at the end of CIBIL report
— last 12 Months	6	5	To be calculated based on the enquiry table at the end of CIBIL report
Total High Credit Sanction	44,72,393	41,55,274
No. of Active accounts	9	12
–Housing Loan / Property Loans	6	10
–Car Loans / Two-Wheeler Loans / Loan against Shares	–	–
–Gold Loans	–	–
–Personal Loans / Consumer Durable Loans	1	1
–Credit Cards	2	1
Total Current outstanding	27,03,353	29,84,624	Balances (Current) to be used
–Housing Loan / Property Loans	23,57,853	26,39,124
–Car Loans / Two-Wheeler Loans / Loan against Shares	–	–
–Gold Loans	–	–
–Personal Loans / Consumer Durable Loans	2,35,000	2,35,000
–CC O/s	1,10,500	1,10,500
Default instances (DPDs in last 24 months)
–Housing Loan / Property Loans
–Car Loans / Two-Wheeler Loans / Loan against Shares
–Gold Loans
–Personal Loans / Consumer Durable Loans
–Credit Cards
Default instances (Any Write-offs observed)
–Housing Loan / Property Loans
–Car Loans / Two-Wheeler Loans / Loan against Shares
–Gold Loans
–Personal Loans / Consumer Durable Loans
–Credit Cards
Current Overdue amount	8,250	8,250
Additional Information
–UAN No.
–Aadhaar Card No.
–Voter ID
–Driving License No.
–Passport No.	U123456	U123456

Details of Borrowing in last 3 / 6 / 9 / 12  months	CIBIL		CRIF
	Active Accounts	Outstanding Amount	Active Accounts	Outstanding Amount	Based on the Drop Down option selected, below details to be populated (Row 45 to 49)
–Housing Loan / Property Loans
–Car Loans / Two-Wheeler Loans / Loan against Shares
–Gold Loans
–Personal Loans / Consumer Durable Loans
–Credit Cards
Total	–	–	–	–

CANNOT ADD SUPPORT FOR NON BUREAU LOAN DETAILS

Final Monthly Obligation to be considered
Obligation as per CIBIL / CRIF		Either G70 or G89, based on whichever is Higher
Additional Obligation not captured		G110
Total monthly obligation	–

Higher Monthly Obligation amongst CIBIL & CRIF	CIBIL / CRIF
Additional Loans taken by Borrower not captured in above Bureau Analysis
Type of Loan	Loan Ending on	Outstanding Amount	Tenure	EMI stated	EMI as per LT FOIR %	Amount to be considered for FOIR (Higher of Column E & F)	Type of Borrower (Ownership)	Whether to be considered in FOIR
Housing Loan / Property Loans									Fully Editable for Input fields
–HL 1							Individual, Joint, Guarantor	Yes / No
–HL 2							Individual, Joint, Guarantor	Yes / No
Car Loans / Two-Wheeler Loans / Loan against Shares
–CL 1							Individual, Joint, Guarantor	Yes / No
–TL 1							Individual, Joint, Guarantor	Yes / No
–LS 1								Yes / No
Gold Loans							Individual, Joint, Guarantor
–GL 1							Individual, Joint, Guarantor	Yes / No
Personal Loans / Consumer Durable Loans
–PL 1							Individual, Joint, Guarantor	Yes / No
–PL 2							Individual, Joint, Guarantor	Yes / No
Credit Cards
–CC 1							Individual, Joint, Guarantor	Yes / No
–CC 2							Individual, Joint, Guarantor	Yes / No
Total Monthly obligation

Contactability

Current Residence
Address Line 1	1001, Tower 12, Amrit Society	Lapp.home_addr_line1
Address Line 2	1001, Tower 12, Amrit Society	Lapp.home_addr_line2
City	Pune	Lapp.home_city
PIN Code	411006	Lapp.home_zipcode
Address Proof Document	Passport	Lapp.address_proof
Current Residence Status	Owned	Lapp.home_ownership_type
No. of Years at Current Residence	( < 1 yr, 1 to 3, 3 to 5, > 5 yrs)	Lapp.years_at_current_residence

Permanent Residence
Address Line 1	1001, Tower 12, Amrit Society	Lapp.permanent_addr_line1
Address Line 2	1001, Tower 12, Amrit Society	Lapp.permanent_addr_line2
City	Pune	Lapp.permanent_city
PIN Code	411006	Lapp.permanent_zipcode
Address Proof Document	Passport	Lapp.permanent_address_proof
Permanent Residence Status	Owned	Lapp.permanent_ownership_type
Name of Contact Person	Mr. X	Lapp.permanent_address_contact_person	New Key
Contact No. of abovementioned person	9765123456	Lapp.permanent_address_contact_number	New Key

Employer Address
Address Line 1		Lapp.office_addr_line1
Address Line 2		Lapp.office_addr_line2
City	Pune	Lapp.office_city
PIN Code	411006	Lapp.office_zipcode

Deputation Address		To be made applicable 3rd party employees / Contractors / Consultants
Address Line 1		lapp.employee_deputation_address_line1
Address Line 2		lapp.employee_deputation_address_line2
City	Mumbai	lapp.employee_deputation_city
PIN Code	400001	Lapp.employee_deputation_zipcode

References
Name	Mobile No.
Lapp.ref.name	Lapp.ref.mobile_number	CPA Input
		CPA Input

Telephone
Current Contact Number	9970665990	Lapp.mobile_number
Truecaller check		Auto Capture, this number’s TrueCaller name
Is it the WhatsApp no.	Yes	CPA Input via Drop Down (Yes / No)	We can do this automatic
Whatsapp Delivery Status		If Whatsapp no. is Yes, then auto trigger a Welcome message and check if it is Deliverable (Success / Failure)	No thoughts have been given in this by doc provider
Alternate Contact no.		CPA Input

E-Mail
Personal E-mail Id	User / CPA input	Lapp.personal_email
Delivery Status of Personal E-mail Id	Success / Failure Output to be displayed	Lapp.personal_email_deliverable_status
Official Email Id	User / CPA input	Lapp.official_email
Delivery Status of Official E-mail Id	Success / Failure Output to be displayed	Company_mca.email_delivery_status

Social Media Contactability
Facebook URL	Manual stored in system in lapp.	Lapp.facebook_url
Count of Facebook Friends	<to be added later>
Facebook Activation date	Not possible to get this
Linkedin URL	Manual stored in system in lapp.	Lapp.linkedin_url
Count of Linkedin Connections	<to be added later>
Linkedin Activation date	Not possible to get this

FETCH BELOW DATA FROM XML OF CRIF AND CIBIL

Bureau Updation Block	CIBIL	CRIF
Bureau details with Current address
Date on which Current address is firstly updated	25-01-2019	07-02-2019	Bureau Report
Count of Loans taken at this Address	3	2	To be calculated based on dates
Amount of Loans	3,00,000	2,00,000	Loans factored, based on above date range calculation; Sanction Amt of those Loans

Address Variations
Count of Address variations	2	2
1002, Tower 8, Kalyan Society, Pune 411006	14-10-2017	04-10-2017	Bureau Report	We need checkboxes to Select Duplicate addresses, and option to delete those line items (i.e., Typo error duplicacy would be avoided)
602, A1 Wing, Rajat Heights, Pune 411018	01-10-2012	04-12-2014	Bureau Report

Telephone / Mobile details
Date of updation in CRIF	04-12-2014	04-12-2014	Bureau Report

Contact No. Variation
Count of Mobile variations	2	2	Bureau Report
9970123456	24-02-2019	24-02-2019	Bureau Report
9980123456	01-10-2018	01-10-2018	Bureau Report

E-mail Id variations
Count of E-mail Id variations	3	4	Bureau Report
abc@gmail.com		24-02-2019	Bureau Report
test@hotmail.com		24-09-2017	Bureau Report
test@loantap.in		01-10-2018	Bureau Report

Employer Details

Employer Details
Employer Name	Tata Consultancy Services Ltd.	Lapp.employer_name	Data will be populated from Company Database based on MCA ID entered by CPA
Employer MCA Id	L22210MH1995PLC084781	Lapp.company_mca_id		MCA ID will be the unique key
Sector of Employer	IT /ITeS		Will get by mca_id	Product team will provide it
Company domain as per database	CS@tcs.com		Extract  form Lapp.official_email	In case of new employer, CPA to update MCA iD and email as per MCA Portal
Employer Category	Cat A	Company_mca.employer_category
Paid Up Capital		Company_mca.paidup_capital

Additional Details wrt Employment
Official E-mail Id	abc@tcs.com	Lapp.official_email	CPA Input
Delivery Status of Official E-mail Id		new key: Lapp.official_email_deliverable_status	Status to be displayed with Conditional formatting
Is Official E-mail Domain same as Employer E-mail Domain?			Output to be displayed based on domain comparison between Row 6 & Row 12, with Conditional formatting	Product team will provide it
Domain Name Creation Date	11-10-1998	Company_mca.domain_creation_date	Auto Capture (ge it from MCA Table)
Designation		New_key employee_designation	CPA Input	Text field CPA input
No. of years with Current employer	2 to 3 years	Lapp.employment_duration	CPA Input	Drop Down as per Annexure I, List 1
No. of years with Current employer as per Linkedin	2 to 3 years		Auto Capture	Will be done later
Overall Work Experience	> 3 years	Lapp.employment_year	CPA Input	Drop Down as per Annexure I, List 1
Salary Account No.		Lapp.salary_account_no
Salary Bank Name		Lapp.salary_bank_name
Count of Linkedin Connections		Lapp.linkedin_connection_count	Auto Capture

	Will be Added Soon   Linkedin Presence of Employer     Is the Company on Linkedin? < Yes / No > Auto Capture Employee Strength / Company Size   Auto Capture Count of Employees active on Linkedin   Auto Capture Headquarters   Auto Capture Type of Company   Auto Capture Commencement Date (Founded)   Auto Capture		Will be Added Soon   Glassdoor Presence of Employer     Is the Company on Glassdoor? < Yes / No > Auto Capture Employee Strength / Company Size   Auto Capture Count of Employees active on Linkedin   Auto Capture Headquarters   Auto Capture Type of Company   Auto Capture Commencement Date (Founded)   Auto Capture

Employer Address
Address Line 1		Lapp.office_addr_line1	CPA Input
Address Line 2		Lapp.office_addr_line2	CPA Input
City	Pune	Lapp.office_city	CPA Input
PIN Code	411006	Lapp.office_zipcode	CPA Input

Deputation Address			To be made applicable for 3rd party employees / Contractors / Consultants
Deputation Employer Name		New key : Lapp.employee_deputation_name	CPA Input  new key	New field as discussed with product team added
Deputation  Email		New key : Lapp.employee_deputation_email	CPA Input  new key	New field as discussed with product team added
Address Line 1		New key : Lapp.employee_deputation_address_line1	CPA Input  new key	Address at which Employee is currently working
Address Line 2		New key : Lapp.employee_deputation_address_line2	CPA Input  new key	Address at which Employee is currently working
City	Mumbai	New key :Lapp.employee_deputation_city	CPA Input new key
PIN Code	400001	New key: Lapp.employee_deputation_zipcode	CPA Input new key

Salary Income Details

Customer Type	Applicant	Label
Employer Name		Lapp.employer_name

Salary details
Particulars	Jan-20	Feb-20	Mar-20	Average
Salary Credit Date	31-01-2020	28-02-2020	31-03-2020		Bank Statement Reader
Net Salary as per Salary Slip	85,000	85,000	92,000	87,333	to be filled by CPA
Amount Credited in Bank Statement	85,000	85,000	92,000	87,333	Bank Statement Reader
Variable Component (NSA, Bonus, OT, Advance, Arrear, Reimbursement)	–	–	7,000	2,333	to be filled by CPA
Fixed Component	85,000	85,000	85,000	85,000	Auto

Query to fetch credit amount and month from the Bank statement Table:

with

        q0 as (SELECT DATE_FORMAT(CAST(txn_date as DATE),”%d-%m-%Y”) as credit_date, txn_date,  DATE_FORMAT(txn_date, ‘%b-%y’) as month, credit FROM bank_statement WHERE object_id=[esc.str @single.lapp.lapp_id/] AND category = ‘Salary’ group by txn_month Limit 0,3)

        select credit_date, month, credit from q0 order by txn_date

Values of fields:

1. Net Salary as per Salary Slip

• Lapp.salary_1
• Lapp.salary_2
• Lapp.salary_3

2. Variable Component

• Lapp.salary_variable_component_1
• Lapp.salary_variable_component_2
• Lapp.salary_variable_component_3

3. Fixed Component

• Lapp.salary_1 – Lapp.salary_variable_component_1
• Lapp.salary_2 – Lapp.salary_variable_component_2
• Lapp.salary_13- Lapp.salary_variable_component_3

Variable Income	Amount	Frequency	Monthly amount
Bonus	50,000	Annually	4,167	Editable	These are Credits from Employer observed in BS or from Salary Slip
Incentive	7,000	Quarterly	2,333	Editable
Additional Variable income considered			6,500

1. Bonus

• Lapp.annual_bonus
• Lapp.annual_bonus_frequency

2. Incentive

• Lapp.incentive
• Lapp.incentive_frequency

Customer Type	Co-Applicant
Employer Name	Lapp.coapp.name

Salary details
Particulars	Jan-20	Feb-20	Mar-20	Average
Salary Credit Date	31-01-2020	28-02-2020	31-03-2020		Bank Statement Reader
Net Salary as per Salary Slip	45,000	45,000	45,000	45,000	to be filled by CPA
Amount Credited in Bank Statement	45,000	45,000	45,000	45,000	Bank Statement Reader
Variable Component (NSA, Bonus, OT, Advance, Arrear, Reimbursement)	–	–	–	–	to be filled by CPA
Fixed Component	45,000	45,000	45,000	45,000	Auto

Note: Red marked fields are not be supposed to be developed, as we are not going to read statement of Co Applicants.

Values of fields:

1. Net Salary as per Salary Slip

• Lapp.coapp.salary_1
• Lapp.coapp.salary_2
• Lapp.coapp.salary_3

2. Variable Component

• Lapp.coapp.salary_variable_component_1
• Lapp.coapp.salary_variable_component_2
• Lapp.coapp.salary_variable_component_3

3. Fixed Component

• Lapp.coapp.salary_1 – Lapp.coapp.salary_variable_component_1
• Lapp.coapp.salary_2 – Lapp.coapp.salary_variable_component_2
• Lapp.coapp.salary_13- Lapp.coapp.salary_variable_component_3

4. Salary Credit Date

• Lapp.coapp.salary_credit_date_1
• Lapp.coapp.salary_credit_date_2
• Lapp.coapp.salary_credit_date_3

5. Amount Credited in Bank Statement

• Lapp.coapp.salary_credit_bank_statement_1
• Lapp.coapp.salary_credit_bank_statement_2
• Lapp.coapp.salary_credit_bank_statement_3

Variable Income	Amount	Frequency	Monthly amount
Bonus	15,000	Annually	1,250	Editable	These are Credits from Employer observed in BS or from Salary Slip
Incentive	–	Quarterly	–	Editable
Additional Variable income considered			1,250

1. Bonus

• Lapp.coapp.annual_bonus
• Lapp.coapp.annual_bonus_frequency

2. Incentive

• Lapp.coapp.incentive
• Lapp.coapp.incentive_frequency

Income to be appraised	Amount	Comments
Average Fixed Salary (Last 3 months)	1,30,000	Sum of field from particular’s table:Fixed Component
Variable Income	7,750	Sum of field from variable income’s table:monthly amount
Other Income (Any other income to be considered)	–
Monthly Income to be appraised	1,37,750	Sum of avg fixed income + variable income

Business Details

Business Details		Key		Description
Channel Name	Metro Cash & Carry	Lapp.channel_code	CPA / Sales / API Input, Drop Down as per Annexure I, List I
Merchant No. with Channel		Lapp.smart_id	CPA / Sales / API Input	We have key lapp.smart_id
Relationship Vintage with Channel		New Key Lapp.business_channel_vintage	CPA / Sales / API Input	Please explain this   Need to add new key for api or partner ask with channel partner if they can provide it Date format editable
Merchant Name		Lapp.business_name	CPA / Sales / API Input	Business name
Nature of Business		Lapp.nature_of_business	CPA / Sales / API Input, drop down to be made available	Nature of Business(List Provided by product team) Kirana / Grocery / Provision Store Mobile Store Medical Store Restaurant / Café Small Manufacturing unit Retailer / Trader (i.e., catering to End consumer) Professional Service Provider (Individual CA, Advocate, Architecture, etc.) Other Service Provider Others
Business Address & Stability
Address Line 1		Lapp.business_addr_line1	CPA / Sales / API Input
Address Line 2		Lapp.business_addr_line2	CPA / Sales / API Input
Landmark		New key Lapp.business_addr_landmark	CPA / Sales / API Input
City		Lapp.business_city	CPA / Sales / API Input, All city drop downs to be made available	CAP Input
Pin Code		Lapp.business_zipcode	CPA / Sales / API Input
Owned / Rented		Lapp.business_ownership_type	CPA / Sales / API Input
Proof of Property Ownership		New key : Lapp.business_property_ownership_proof	CPA / Sales / API Input with Drop Down	Need to provide list of documents Index II   Property Tax Receipt   Copy of Sale Deed
Business Vintage document		New key : Lapp.business_proof	CPA / Sales / API Input, Drop Down as per Annexure I, List II	Business Vintage document(List Provided by product team) Shop Act / Gumasta License ITR Copy GST Registration Certificate Bank Statement Udyog Aadhaar
Business Commencement Date		New Key :Lapp.date_of_formation	CPA / Sales / API Input	Starting date of business
Business Vintage (Years)			Auto Calculated from Business Commencement Date	Calculated by Lapp.business_commencement_date
GST Registration No.		Lapp.business_gst_number	CPA / Sales / API Input
4 Shop Photos	< Yes / No >		Answer to be auto-captured, based on uploading of 4 Shop Photos	Count uploaded photo with name shop_photo.*
Google Location Link of Shop		Lapp.business_google_location_link	Customer to share with Sales / Credit Team, and they will upload	CPA Input

Config Array for Dropdown

[nature_of_business]

    [options new label="Kirana / Grocery /
Provision Store" value="kirana-grocery-provision-store"/]

    [options new label="Mobile Store"
value="mobile-store"/]

    [options new label="Medical
Store" value="office-colleague"/]

    [options new label="Restaurant /
Cafe" value="restaurant-cafe"/]

    [options new label="Small
Manufacturing unit" value="small-manufacturing-unit"/]

    [options new label="Retailer /
Trader  (i.e., catering to End
consumer)" value="retailer-trader"/]

    [options new label="Professional
Service Provider (Individual CA, Advocate, Architecture, etc.)"
value="professional-service-provider"/]

    [options new label="Other Service
Provider" value="other-service-provider"/]

    [options new label="Others"
value="others"/]

[/nature_of_business]

[business_property_ownership_proof]

    [options new label="Index II"
value="index-II"/]

    [options new label="Property Tax
Receipt" value="property-tax-receipt"/]

    [options new label="Copy of Sale
Deed" value="copy-of-sale-deed"/]

[/business_property_ownership_proof]

[business_proof]

    [options new label="Shop Act / Gumasta
License" value="shop-act-gumasta-license"/]

    [options new label="ITR Copy"
value="itr-copy"/]

    [options new label="GST Registration
Certificate" value="gst-registration-certificate"/]

    [options new label="Bank
Statement" value="bank-statement"/]

    [options new label="Udyog
Aadhaar" value="udyog-aadhaar"/]

[/business_proof]

Credit Card Details

No Possible to read credit card

Latest CC Statement details
CC Issuing Bank Name	HDFC Bank Ltd.	Lapp.credit_card_issuing_bank (new key)	CPA Input
Date of Issuance	27-03-2016	Lapp.credit_card_issue_date	CPA Input
Date of Expiry	31-03-2021	Lapp.credit_card_exp_date (new key)	CPA Input
Credit Card Limit	70,000	Lapp.credit_card_limit	CPA Input
Credit Card Statement Date	12-03-2020	Lapp.credit_card_statement_date (new key)	CPA Input
Total Amount due as per CC Statement	62,500	Lapp.credit_card_amount_due (new key)	CPA Input
Total Amount due as per CC Statement (Opening Balance)	42,500	Lapp.credit_card_opening_bal (new key)	CPA Input
Total Amount paid as per CC Statement	42,500	Lapp.credit_card_amount_paid (new key)	CPA Input
Ratio of Amount paid to Amount due	100%	Lapp.credit_card_opening_paid /  Lapp.credit_card_opening_bal	Auto Calculate
Whether Ratio of Amount paid to Amount due satisfies LoanTap norms (i.e., > 70%)	LoanTap norms satisfied	When above ratio is more than, 0.7, then noms satisfied else no	Auto Captured

Complete data is extracted from XML of CIBIL  and CRIF

Additional Credit Card Details	CIBIL	CRIF	Auto Capture from Bureau
CC 1
High Credit	57,800	57,800
Current Balance	34,500	34,500
Credit Limit	60,000	60,000
CC 2
High Credit	35,000	35,000
Current Balance	17,000	17,000
Credit Limit	42,000	42,000
CC 3
High Credit	90,000	90,000
Current Balance	84,000	84,000
Credit Limit	1,00,000	1,00,000
Summary
Count of Active Credit Cards	3	3
High Credit	1,82,800	1,82,800
Current Balance	1,35,500	1,35,500
Credit Limit	2,02,000	2,02,000
Latest CC Statement details