# BBK - Consumer Loans Questions
## 1-Seller and Reference Selection List
What is the origin of the referral and seller listings? Do we need to implement CRUD operations for these lists in our scope? If yes, could you share the data model of these lists?
### Answer for the question asked
SELLER LIST
```sql!
select distinct sell from (
select distinct hr_num||'~'||cus_sho_name sell from teller a, tel_role b where a.aut_lvl not in(1,2,3,4) and a.hr_num is not null
and b.role_num in (2,4,6,7,8,9,48,23,138) and a.tell_id=b.tell_id and a.bra_code=b.bra_code and a.bra_code not in (201,999)
union
select distinct hr_num||'~'||cus_sho_name sell from teller a, tel_role b where a.aut_lvl not in(2,3,4) and a.hr_num is not null
and b.role_num in (73,74) and a.tell_id=b.tell_id and a.bra_code=b.bra_code and a.bra_code= 201 and a.bra_code <> 999)
order by 1;
```
### Our comment is as follows.
#### The fields in the given tables can be analyzed as follows:
1. teller table:
• tell_id: Unique identifier for tellers.
• bra_code: Code or number of the branch.
• aut_lvl: Authorization level of tellers.
• hr_num: Human resources number of tellers.
• cus_sho_name: Customer abbreviation and name.
2. tel_role table:
• tel_role_id: Role identifier for tellers.
• tell_id: Unique identifier for tellers.
• bra_code: Code or number of the branch.
• role_num: Role number of tellers.
#### Some possible analysis based on these tables can be:
• tell_id and bra_code fields are used to establish relationships between tellers and roles. Each teller can be assigned one or more roles.
• aut_lvl field is used to determine the authorization level of tellers. This field contains a numerical value representing a specific authorization level.
• cus_sho_name field represents the abbreviation and name of customers.
• bra_code field represents the code or number of branches.
• hr_num field represents the human resources number of tellers.
• role_num field represents the role of tellers.
Based on this analysis, it can be inferred that the "seller" list used in a specific credit application process consists of tellers with specific authorization levels in a specific branch.
#### The following details are needed for further analysis
What data exactly do the Seller Id and Refferid Id lists return? We couldn't understand this part. Can you explain in detail?
Is our analysis correct?
Do the tables here have different fields? The field details of the tables need to be explained. If we're going to CRUD, we need this information.
Shall we pull this list from your service? Can you share service information?
## 2-Employer List and Salary Validation
What is the source of the global employer list? Could you share the service details (query and response details)? Is there any service or method to verify Salary on Certificate?
### Answer for the question asked
Employer List
```sql
select emp_code, decode (emp_type,'3','P','4','O','5','S','1','G','') emp_type,chg_flag,decode (rel_comp_flag,'1','Y','N'),emp_class from emp_list where emp_code =
```
Salary validation
1) Minimum approved salary required is 250 KD
2) Basic salary Could not be greater than Approved Net Salary
### Our comment is as follows.
The SELECT statement specifies the columns to return in the query result.
The emp_code, emp_type, chg_flag, rel_comp_flag and emp_class columns are selected.
The data will be pulled from the "emp_list" table.
The DECODE function is used to convert a value to another value.
The first DECODE statement checks the emp_type column.
If emp_type is '3', the emp_type column is converted to 'P'.
If emp_type is '4', the emp_type column is converted to 'O'.
If emp_type is '5', the emp_type column is converted to 'S'.
If emp_type is '1', the emp_type column is converted to 'G'.
If the emp_type value is not equal to any of the above values, the emp_type column is blank.
The second DECODE statement checks the rel_comp_flag column.
If the rel_comp_flag value is '1', the rel_comp_flag column is converted to 'Y'.
If the rel_comp_flag value is not '1', the rel_comp_flag column is converted to 'N'.
Results are returned as emp_code, emp_type, chg_flag, rel_comp_flag, and emp_class with the columns selected.
This query aims to convert emp_type and rel_comp_flag columns to specific values when pulling data from the "emp_list" table. These conversions are made according to certain conditions. The results are then returned as columns emp_code, emp_type, chg_flag, rel_comp_flag, and emp_class.
#### The following details are needed for further analysis
Is our analysis correct?
Does the table here have different fields? The field details of the tables need to be explained.
In addition, detailed explanations of fields such as P, O, S, G etc. are required according to the condition.
What system is this table on? In Core Banking systems? Or is it in ultimus' own database?
## 3-CiNET Service Call
CiNET shares more information than is visualized in the ultimus screens in powerpoint.
Visualization of all data from Cinet helps to evaluate the application and can also be used in the automated decision table for future improvements.
Can you share the full response of the CiNET query service in the responding format (such as JSON, XML)?
It would be very helpful if the sample responses cover all possible product and account types.
### Answer for the question asked
#### Type of Account
Consumer Loan
Housing Loan
Card
Lease
Rent
#### Relationship to Account
Owner
Guarantor
#### Lender Type
Regional Bank
Non Bank Fin Services
Diversified Fin Company
#### Litigation Status
Open
Close
#### CAIS/CAPS
CAIS
CAPS
In addition, CI-NET request and response example came.
### Our comment is as follows.
#### Cinet Request
```xml!
<REQUEST REQUEST_ID="1"><REQUEST_PARAMETERS><INQUIRY_PURPOSE CODE="2" RELATIONTOACCOUNT="01"/><LOANDETAILS PORTFOLIOTYPE="02" AMOUNTOFFINANCE="0" DURATIONOFAGREEMENT="0" DURATIONUNIT="2" PAYMENTAMOUNT="0" CREDITLIMIT="0" PAYMENTFREQUENCY="01" OCCUPATION="01" OTHERINCOME="0" MONTHLYSALARY="0" CULTURE_INFO="en-US" RESPONSE_TYPE="1"/><REPORT_PARAMETERS CIR_TYPE="14716"/></REQUEST_PARAMETERS><SEARCH_PARAMETERS><IDENTIFIER><CIVILID>273070700663</CIVILID></IDENTIFIER></SEARCH_PARAMETERS></REQUEST>
```
**<INQUIRY_PURPOSE CODE="2" RELATIONTOACCOUNT="01"/>:** This part represents the purpose of the inquiry and the relation to the account in the credit application process. The CODE="2" value likely indicates a credit application, while the RELATIONTOACCOUNT="01" value suggests that the application is related to the account holder.
**<LOANDETAILS PORTFOLIOTYPE="02" AMOUNTOFFINANCE="0" DURATIONOFAGREEMENT="0" DURATIONUNIT="2" PAYMENTAMOUNT="0" CREDITLIMIT="0" PAYMENTFREQUENCY="01" OCCUPATION="01" OTHERINCOME="0" MONTHLYSALARY="0" CULTURE_INFO="en-US" RESPONSE_TYPE="1"/>:** This section represents the loan details and the financial status of the applicant. Here's an explanation of the parameters:
**PORTFOLIOTYPE="02":** It represents the type of loan portfolio. The value 02 may indicate a specific portfolio type.
**AMOUNTOFFINANCE="0":** It indicates the amount of finance for the loan. Here, the value 0 suggests that the loan amount is not specified.
**DURATIONOFAGREEMENT="0" and DURATIONUNIT="2":** These parameters represent the duration of the loan agreement and the unit of duration. The values 0 indicate that the duration is not specified, and when DURATIONUNIT="2", the duration is likely expressed in months.
**PAYMENTAMOUNT="0":** It represents the payment amount. Here, the value 0 suggests that the payment amount is not specified.
**CREDITLIMIT="0":** It represents the credit limit. The value 0 suggests that the credit limit is not specified.
**PAYMENTFREQUENCY="01":** It represents the payment frequency. The value 01 likely indicates monthly payments.
**OCCUPATION="01":** It represents the occupation of the applicant. The value 01 may indicate a specific occupation.
**OTHERINCOME="0":** It represents other income. The value 0 suggests that other income is not specified.
MONTHLYSALARY="0": It represents the monthly salary. The value 0 suggests that the monthly salary is not specified.
**CULTURE_INFO="en-US" RESPONSE_TYPE="1":** These parameters specify the language (en-US) and the response type (1) of the request.
**<REPORT_PARAMETERS CIR_TYPE="14716"/>:** This part represents the report parameters. The value CIR_TYPE="14716" likely indicates the type of credit information report.
**<SEARCH_PARAMETERS><IDENTIFIER><CIVILID>273070700663</CIVILID></IDENTIFIER></SEARCH_PARAMETERS>:** This section represents the search parameters. Here, the civil ID (CIVILID) 273070700663 is specified for the search.
In summary, using the provided request parameters in the credit application process, creditworthiness checks, such as identity verification, financial assessment, and obtaining a credit information report, can be performed.
#### Cinet Response
```xml!
<LiveResponse>
<Warnings/>
<DATAPACKET>
<ReportDetails>
<ReportDetail>
<USERID>BB000xxx</USERID>
<BRANCH>Hawalli Branch</BRANCH>
<CIRNO>W-0015992249/2023</CIRNO>
<REPORTORDERDT>10-05-2023 12:12:10</REPORTORDERDT>
<INSTNAME>Burgan Bank</INSTNAME>
<TRANSACTION_NUMBER>9770835</TRANSACTION_NUMBER>
</ReportDetail>
</ReportDetails>
<Cinet_SearchDetails>
<Cinet_SearchDetails>
<ENGLISHNAME>xxxxxxxxxxxxxxx</ENGLISHNAME>
<ARABIC_NAME>???? ???? ???? ??????</ARABIC_NAME>
<IDENTIFIERTYPE>Civil ID</IDENTIFIERTYPE>
<IDENTIFIERNO>2xxxxxxx3</IDENTIFIERNO>
</Cinet_SearchDetails>
</Cinet_SearchDetails>
<ApplicationDtls />
<ConsumerDetails>
<ConsumerDetails>
<ENGLISHNAME>xxxxxxxxxxxxxx</ENGLISHNAME>
<ARABICNAME>???? ???? ???? ??????</ARABICNAME>
<NATIONALITY>Kuwait</NATIONALITY>
<GENDER>Male</GENDER>
<DOB>07-07-1973</DOB>
<STATUSOFCIVILID>Civil ID is registered for an active person</STATUSOFCIVILID>
<IDENTIFIERNO>273070700663</IDENTIFIERNO>
<HISTORICALSALARY>2192</HISTORICALSALARY>
<REPORTEDDATE_INC>16-05-2022</REPORTEDDATE_INC>
<HISTORICALINCOME>0</HISTORICALINCOME>
<ADDRESS>?????? 1 ???? 6 ???? 16</ADDRESS>
</ConsumerDetails>
</ConsumerDetails>
<Cinet_Warnings />
<Cinet_GovtFundedLoan>
<Cinet_GovtFundedLoan>
<GOVTFUNDLOAN>NO</GOVTFUNDLOAN>
<ISGVTLNINDICATOR>0</ISGVTLNINDICATOR>
<GOVTFUNDLOANAPPLY>NO</GOVTFUNDLOANAPPLY>
<ISGVTLNAPPLIEDINDICATOR>0</ISGVTLNAPPLIEDINDICATOR>
<LEGALINDICATOR>NO</LEGALINDICATOR>
<ISLEGALINDICATOR>0</ISLEGALINDICATOR>
<WRITEOFF>NO</WRITEOFF>
<ISWRITEOFFINDICATOR>0</ISWRITEOFFINDICATOR>
<FAMILYFUNDLOANINDICATOR>YES</FAMILYFUNDLOANINDICATOR>
<ISFAMILYFUNDLOAN>1</ISFAMILYFUNDLOAN>
<FAMILYFUNDLOANAPPLIED>NO</FAMILYFUNDLOANAPPLIED>
<ISFAMILYFUNDAPPLIED>0</ISFAMILYFUNDAPPLIED>
<ISFRAUDLEGALCASE>0</ISFRAUDLEGALCASE>
<DOCUPLOADPENDING>NO</DOCUPLOADPENDING>
<DOCUPLOADINDICATOR>0</DOCUPLOADINDICATOR>
<ACTIVE_DISPUTE>NO</ACTIVE_DISPUTE>
<ISACTIVE_DISPUTE>0</ISACTIVE_DISPUTE>
</Cinet_GovtFundedLoan>
</Cinet_GovtFundedLoan>
<CreditProfileOverview>
<CreditProfileOverview>
<WORSTSTSACTIVAC_VALUE>Up to date current</WORSTSTSACTIVAC_VALUE>
<WORSTSTSACTIVAC_RPTDT>05-2023</WORSTSTSACTIVAC_RPTDT>
<WORSTORYHSTSTS_VALUE>Arrears</WORSTORYHSTSTS_VALUE>
<WORSTORYHSTSTS_RPTDT>01-2019</WORSTORYHSTSTS_RPTDT>
<WORSTHSTNMBRPMNT_VALUE>2</WORSTHSTNMBRPMNT_VALUE>
<WORSTHSTNMBRPMNT_RPTDT>01-2019</WORSTHSTNMBRPMNT_RPTDT>
<MAXNBROVRDUE_VALUE>60</MAXNBROVRDUE_VALUE>
<MAXNBROVRDUE_RPTDT>06-2021</MAXNBROVRDUE_RPTDT>
<MAXNBRPYMNTOVRDUE_VALUE>2</MAXNBRPYMNTOVRDUE_VALUE>
<MAXNBRPYMNTOVRDUE_RPTDT>06-2021</MAXNBRPYMNTOVRDUE_RPTDT>
<TOTLOVRDUEAMNT_VALUE>0</TOTLOVRDUEAMNT_VALUE>
<TOTLOVRDUEAMNT_RPTDT>-</TOTLOVRDUEAMNT_RPTDT>
<TOTWRTAMNT_VALUE>0</TOTWRTAMNT_VALUE>
<TOTWRTAMNT_RPTDT>-</TOTWRTAMNT_RPTDT>
<NMBROFCF_VALUE>8</NMBROFCF_VALUE>
<NMBROFCF_RPTDT>04-2023</NMBROFCF_RPTDT>
<NMBROFCFCLOSED_VALUE>25</NMBROFCFCLOSED_VALUE>
<NMBROFCFCLOSED_RPTDT>05-2023</NMBROFCFCLOSED_RPTDT>
<NMBROFGTOPENCF_VALUE>0</NMBROFGTOPENCF_VALUE>
<NMBROFGTOPENCF_RPTDT>-</NMBROFGTOPENCF_RPTDT>
<NMBROFGTCLSDCF_VALUE>2</NMBROFGTCLSDCF_VALUE>
<NMBROFGTCLSDCF_RPTDT>12-2011</NMBROFGTCLSDCF_RPTDT>
<NMBRINQLST1MNTH_VALUE>0</NMBRINQLST1MNTH_VALUE>
<NMBRINQLST1MNTH_RPTDT>-</NMBRINQLST1MNTH_RPTDT>
<NMBRINQLST3MNTH_VALUE>0</NMBRINQLST3MNTH_VALUE>
<NMBRINQLST3MNTH_RPTDT>-</NMBRINQLST3MNTH_RPTDT>
<NMBRINQLST12MNTH_VALUE>2</NMBRINQLST12MNTH_VALUE>
<NMBRINQLST12MNTH_RPTDT>07-2022</NMBRINQLST12MNTH_RPTDT>
<NMBRCRDAPPPROCESS_VALUE>0</NMBRCRDAPPPROCESS_VALUE>
<NMBRCRDAPPPROCESS_RPTDT>-</NMBRCRDAPPPROCESS_RPTDT>
<MAXCRDUTIL24MNTH_VALUE> 104.00</MAXCRDUTIL24MNTH_VALUE>
<MAXCRDUTIL24MNTH_RPTDT>05-2023</MAXCRDUTIL24MNTH_RPTDT>
<MAXNMBRCFOVRDUE_VALUE>0</MAXNMBRCFOVRDUE_VALUE>
<MAXNMBRCFOVRDUE_RPTDT>-</MAXNMBRCFOVRDUE_RPTDT>
<UTLCRNTDBTRATIO_VALUE> 104.00</UTLCRNTDBTRATIO_VALUE>
<UTLCRNTDBTRATIO_RPTDT>04-2023</UTLCRNTDBTRATIO_RPTDT>
<OPEN_CF_IN_COMM_PORTFOLIO>NO</OPEN_CF_IN_COMM_PORTFOLIO>
<COMM_REPORTED_DT>-</COMM_REPORTED_DT>
</CreditProfileOverview>
</CreditProfileOverview>
<Cinet_LiabilitySummary>
<Cinet_LiabilitySummary>
<TTLLIABILITYAMNT>64552</TTLLIABILITYAMNT>
<AMNTAPPLIEDFOR>0</AMNTAPPLIEDFOR>
<TTLLIABILITYINCPROPOSED>64552</TTLLIABILITYINCPROPOSED>
<TTLLIAAPPROVANDPROPOSED>64552</TTLLIAAPPROVANDPROPOSED>
<TTLINCLUDEALL>64552</TTLINCLUDEALL>
<TTLCONTINGENT>0</TTLCONTINGENT>
<TTL_CURRENT_INSTALLMENT>740</TTL_CURRENT_INSTALLMENT>
<TTL_INST_INC_PRPSD_ACNT>740</TTL_INST_INC_PRPSD_ACNT>
<TTL_CT_INST_INCL_APRVD_CRD>740</TTL_CT_INST_INCL_APRVD_CRD>
<TTL_INST_INC_APRV_CRD_APP>740</TTL_INST_INC_APRV_CRD_APP>
<TTLAPRDCRTAPP>0</TTLAPRDCRTAPP>
</Cinet_LiabilitySummary>
</Cinet_LiabilitySummary>
<CreditProfileSummeryGraph>
<CreditProfileSummeryGraph>
<MONTH>Jun-21</MONTH>
<INSTALLMENT_AMOUNT>261</INSTALLMENT_AMOUNT>
<OVERDUE_AMOUNT>0</OVERDUE_AMOUNT>
</CreditProfileSummeryGraph>
<CreditProfileSummeryGraph>
<MONTH>Jul-21</MONTH>
<INSTALLMENT_AMOUNT>134</INSTALLMENT_AMOUNT>
<OVERDUE_AMOUNT>0</OVERDUE_AMOUNT>
</CreditProfileSummeryGraph>
</CreditProfileSummeryGraph>
<AssetClassificationGraph>
<AssetClassificationGraph>
<PASSLOAN>2</PASSLOAN>
<RESTRUCTUREDLOAN>0</RESTRUCTUREDLOAN>
<SUBSTANDARD>0</SUBSTANDARD>
<DOUBTFULLOAN>0</DOUBTFULLOAN>
<LOSSLOAN>0</LOSSLOAN>
<DISPAYDATE>Jun-21</DISPAYDATE>
</AssetClassificationGraph>
<AssetClassificationGraph>
<PASSLOAN>2</PASSLOAN>
<RESTRUCTUREDLOAN>0</RESTRUCTUREDLOAN>
<SUBSTANDARD>0</SUBSTANDARD>
<DOUBTFULLOAN>0</DOUBTFULLOAN>
<LOSSLOAN>0</LOSSLOAN>
<DISPAYDATE>Jul-21</DISPAYDATE>
</AssetClassificationGraph>
</AssetClassificationGraph>
<Cinet_AccountClassification>
<Cinet_AccountClassification>
<INSTITUTIONTYPE>Local Bank</INSTITUTIONTYPE>
<NOOFACCOUNTS>8</NOOFACCOUNTS>
<SANCTIONEDAMOUNT>104412</SANCTIONEDAMOUNT>
<TOTALOUTSTANDINGBALANCE>64552</TOTALOUTSTANDINGBALANCE>
<RECENTOVERDUE>0</RECENTOVERDUE>
<RECENTOVERDUEDATE>-</RECENTOVERDUEDATE>
<HIGHEST_OVERDUE_AMOUNT>0</HIGHEST_OVERDUE_AMOUNT>
<HIGHEST_OVERDUE_DATE>30-04-2023</HIGHEST_OVERDUE_DATE>
<OVERDUEBALANCE>0</OVERDUEBALANCE>
<UTILIZATION>104</UTILIZATION>
<TOTALOUTSTANDINGBALANCEPERC>0</TOTALOUTSTANDINGBALANCEPERC>
<LEGALACTIONTAKEN>N</LEGALACTIONTAKEN>
</Cinet_AccountClassification>
</Cinet_AccountClassification>
<Cinet_TotalLiabilitySummary>
<Cinet_TotalLiabilitySummary>
<TTLBALOUTSTND>64552</TTLBALOUTSTND>
<TTLNMBRINST>1</TTLNMBRINST>
<TTLNMBRFACI>8</TTLNMBRFACI>
<TTLNMBRBAL>0</TTLNMBRBAL>
<OVRDUE30>0</OVRDUE30>
<OVRDUE60>0</OVRDUE60>
<OVRDUE90>0</OVRDUE90>
<OVRDUE120>0</OVRDUE120>
<OVRDUE150>0</OVRDUE150>
<OVRDUE180>0</OVRDUE180>
<OVRDUE180P>0</OVRDUE180P>
</Cinet_TotalLiabilitySummary>
</Cinet_TotalLiabilitySummary>
<Cinet_OverdueHistoryGraph>
<Cinet_OverdueHistoryGraph>
<OVERDUE_BUCKET_NAME>1-30 Days</OVERDUE_BUCKET_NAME>
<OVERDUEAMOUNT>0</OVERDUEAMOUNT>
<REF>0</REF>
</Cinet_OverdueHistoryGraph>
<Cinet_OverdueHistoryGraph>
<OVERDUE_BUCKET_NAME>31-60 Days</OVERDUE_BUCKET_NAME>
<OVERDUEAMOUNT>0</OVERDUEAMOUNT>
<REF>0</REF>
</Cinet_OverdueHistoryGraph>
</Cinet_OverdueHistoryGraph>
<Cinet_ExposurebyProduct>
<Cinet_ExposurebyProduct>
<PRODUCTTYPE>Charge Card</PRODUCTTYPE>
<COUNT>2</COUNT>
<OVERDUE>0</OVERDUE>
<YETTOBEDUE>312</YETTOBEDUE>
<UNUTILIZEDCREDIT>-12</UNUTILIZEDCREDIT>
</Cinet_ExposurebyProduct>
<Cinet_ExposurebyProduct>
<PRODUCTTYPE>Housing Finance</PRODUCTTYPE>
<COUNT>4</COUNT>
<OVERDUE>0</OVERDUE>
<YETTOBEDUE>37633</YETTOBEDUE>
<UNUTILIZEDCREDIT>NA</UNUTILIZEDCREDIT>
</Cinet_ExposurebyProduct>
</Cinet_ExposurebyProduct>
<Cinet_OverdueGraph>
<Cinet_OverdueGraph>
<PERCENT_LABEL>100%</PERCENT_LABEL>
<PERCENTAGE>100</PERCENTAGE>
</Cinet_OverdueGraph>
<Cinet_OverdueGraph>
<PERCENT_LABEL>0%</PERCENT_LABEL>
<PERCENTAGE>0</PERCENTAGE>
</Cinet_OverdueGraph>
</Cinet_OverdueGraph>
<Cinet_CreditUtilGraph>
<Cinet_CreditUtilGraph>
<PERCENT_LABEL>104%</PERCENT_LABEL>
<UTLIZATION_PERCENTAGE>104</UTLIZATION_PERCENTAGE>
</Cinet_CreditUtilGraph>
<Cinet_CreditUtilGraph>
<PERCENT_LABEL>-4%</PERCENT_LABEL>
<UTLIZATION_PERCENTAGE>-4</UTLIZATION_PERCENTAGE>
</Cinet_CreditUtilGraph>
</Cinet_CreditUtilGraph>
<Cinet_InquirySummary>
<Cinet_InquirySummary>
<INSTITUTIONTYPE>MOC Companies</INSTITUTIONTYPE>
<NMBRLAST12MNTH>2</NMBRLAST12MNTH>
<INQUIRYREASON>New Account Inquiry</INQUIRYREASON>
<NMBRLAST12MNTHRSN>2</NMBRLAST12MNTHRSN>
</Cinet_InquirySummary>
<Cinet_InquirySummary>
<INSTITUTIONTYPE>Local Bank</INSTITUTIONTYPE>
<NMBRLAST12MNTH>3</NMBRLAST12MNTH>
<INQUIRYREASON>Account Review Inquiry</INQUIRYREASON>
<NMBRLAST12MNTHRSN>3</NMBRLAST12MNTHRSN>
</Cinet_InquirySummary>
</Cinet_InquirySummary>
<Cinet_InstTypeGraph>
<Cinet_InstTypeGraph>
<VALUE>60</VALUE>
<INQUIRYTYPE>Local Bank</INQUIRYTYPE>
<LABLE>60%</LABLE>
</Cinet_InstTypeGraph>
<Cinet_InstTypeGraph>
<VALUE>40</VALUE>
<INQUIRYTYPE>MOC Companies</INQUIRYTYPE>
<LABLE>40%</LABLE>
</Cinet_InstTypeGraph>
</Cinet_InstTypeGraph>
<Cinet_InqReasonGraph>
<Cinet_InqReasonGraph>
<LABLE>40%</LABLE>
<VALUE>40</VALUE>
<INQUIRYREASON>New Account Inquiry</INQUIRYREASON>
</Cinet_InqReasonGraph>
<Cinet_InqReasonGraph>
<LABLE>60%</LABLE>
<VALUE>60</VALUE>
<INQUIRYREASON>Account Review Inquiry</INQUIRYREASON>
</Cinet_InqReasonGraph>
</Cinet_InqReasonGraph>
<Cinet_DocStatusProvidedByCustomer />
<Cinet_AccDtls>
<Cinet_AccDtls>
<NUMBERORDER>1</NUMBERORDER>
<RESCHEDULEFLAG>N</RESCHEDULEFLAG>
<PRIMARY_ROOT_ID>6113606</PRIMARY_ROOT_ID>
<ACTIVE_ROOT_ID>6113606</ACTIVE_ROOT_ID>
<INSTITUTION_NAME>Burgan Bank</INSTITUTION_NAME>
<PAYMENT_AMOUNT>316</PAYMENT_AMOUNT>
<INSTITUTIONTYPE>Local Bank</INSTITUTIONTYPE>
<ACCOUNTNO>826537-003</ACCOUNTNO>
<ACCOUNTTYPE>Family Fund Loan</ACCOUNTTYPE>
<ACCOUNTSTATUS>Up to date current</ACCOUNTSTATUS>
<REPORTEDDATE>23-04-2023</REPORTEDDATE>
<ISREPORTDATEINDICATOR>1</ISREPORTDATEINDICATOR>
<ACCOUNTOPENINGDATE>12-06-2013</ACCOUNTOPENINGDATE>
<ASSETCLASSIFICATION>Standard</ASSETCLASSIFICATION>
<DISBURSEMENTDATE>12-06-2013</DISBURSEMENTDATE>
<PLANNEDTERMINATEDDATE>23-12-2028</PLANNEDTERMINATEDDATE>
<REPAYMENTFREQUENCY>Monthly</REPAYMENTFREQUENCY>
<LASTINSTALLMENTDUEDATE>23-04-2023</LASTINSTALLMENTDUEDATE>
<OUTSTANDINGBALANCE>23369</OUTSTANDINGBALANCE>
<WRSTHSTACSTS>Arrears</WRSTHSTACSTS>
<WRSTHSTNUMBEROFPYMENTS>1</WRSTHSTNUMBEROFPYMENTS>
<NOOFPYMNTSOUTSTANDING>0</NOOFPYMNTSOUTSTANDING>
<LEGALACTIONTKN>N</LEGALACTIONTKN>
<ACCGUARANTORPRESENT>N</ACCGUARANTORPRESENT>
<LOANTENURE>186</LOANTENURE>
<AMOUNTOFFINANCE>56842</AMOUNTOFFINANCE>
<LASTINSTALLMENTPAYMENTAMOUNT>316</LASTINSTALLMENTPAYMENTAMOUNT>
<OVERDUEBALANCE>0</OVERDUEBALANCE>
<DAYSPASTDUE>0</DAYSPASTDUE>
<MONTH1>Apr23</MONTH1>
<MONTH2>Mar23</MONTH2>
<MONTH3>Feb23</MONTH3>
<MONTH4>Jan23</MONTH4>
<MONTH5>Dec22</MONTH5>
<MONTH6>Nov22</MONTH6>
<MONTH7>Oct22</MONTH7>
<MONTH8>Sep22</MONTH8>
<MONTH9>Aug22</MONTH9>
<MONTH10>Jul22</MONTH10>
<MONTH11>Jun22</MONTH11>
<MONTH12>May22</MONTH12>
<MONTHVAL1>OK</MONTHVAL1>
<MONTHVAL2>OK</MONTHVAL2>
<MONTHVAL3>OK</MONTHVAL3>
<MONTHVAL4>OK</MONTHVAL4>
<MONTHVAL5>OK</MONTHVAL5>
<MONTHVAL6>OK</MONTHVAL6>
<MONTHVAL7>OK</MONTHVAL7>
<MONTHVAL8>OK</MONTHVAL8>
<MONTHVAL9>OK</MONTHVAL9>
<MONTHVAL10>OK</MONTHVAL10>
<MONTHVAL11>OK</MONTHVAL11>
<MONTHVAL12>OK</MONTHVAL12>
<MONTHINDI1>0</MONTHINDI1>
<MONTHINDI2>0</MONTHINDI2>
<MONTHINDI3>0</MONTHINDI3>
<MONTHINDI4>0</MONTHINDI4>
<MONTHINDI5>0</MONTHINDI5>
<MONTHINDI6>0</MONTHINDI6>
<MONTHINDI7>0</MONTHINDI7>
<MONTHINDI8>0</MONTHINDI8>
<MONTHINDI9>0</MONTHINDI9>
<MONTHINDI10>0</MONTHINDI10>
<MONTHINDI11>0</MONTHINDI11>
<MONTHINDI12>0</MONTHINDI12>
<OVERDUEMONTH1>0</OVERDUEMONTH1>
<OVERDUEMONTH2>0</OVERDUEMONTH2>
<OVERDUEMONTH3>0</OVERDUEMONTH3>
<OVERDUEMONTH4>0</OVERDUEMONTH4>
<OVERDUEMONTH5>0</OVERDUEMONTH5>
<OVERDUEMONTH6>0</OVERDUEMONTH6>
<OVERDUEMONTH7>0</OVERDUEMONTH7>
<OVERDUEMONTH8>0</OVERDUEMONTH8>
<OVERDUEMONTH9>0</OVERDUEMONTH9>
<OVERDUEMONTH10>0</OVERDUEMONTH10>
<OVERDUEMONTH11>0</OVERDUEMONTH11>
<OVERDUEMONTH12>0</OVERDUEMONTH12>
<OVRDUEAMNT30>0</OVRDUEAMNT30>
<OVRDUEAMNT60>0</OVRDUEAMNT60>
<OVRDUEAMNT90>0</OVRDUEAMNT90>
<OVRDUEAMNT120>0</OVRDUEAMNT120>
<OVRDUEAMNT150>0</OVRDUEAMNT150>
<OVRDUEAMNT180>0</OVRDUEAMNT180>
<OVRDUEAMNT180P>0</OVRDUEAMNT180P>
<OVRDUE30>0</OVRDUE30>
<OVRDUE60>0</OVRDUE60>
<OVRDUE90>0</OVRDUE90>
<OVRDUE120>0</OVRDUE120>
<OVRDUE150>0</OVRDUE150>
<OVRDUE180>0</OVRDUE180>
<OVRDUE180P>0</OVRDUE180P>
<DOCUMENTSTATUS>Not Applicable</DOCUMENTSTATUS>
<SUPPORTINGDOCUMENTSTATUS>-</SUPPORTINGDOCUMENTSTATUS>
</Cinet_AccDtls>
</Cinet_AccDtls>
<Cinet_ClsdAcDtls>
<Cinet_ClsdAcDtls>
<ACCOUNTNO>826537-005</ACCOUNTNO>
<ACCOUNTTYPE>Housing Finance</ACCOUNTTYPE>
<ACCOUNTOPNDT>20-08-2015</ACCOUNTOPNDT>
<CRDLIMIT>2900</CRDLIMIT>
<PAYMENTAMOUNT>58</PAYMENTAMOUNT>
<ACCCLSDDT>24-04-2017</ACCCLSDDT>
<REASONCLS>Closed by Customer</REASONCLS>
<ASTCLASSIFICATION>Standard</ASTCLASSIFICATION>
<STSACC>Up to date current</STSACC>
<WRSTHSTACSTATUS>-</WRSTHSTACSTATUS>
<ISWRKSTHSTINDICATOR>0</ISWRKSTHSTINDICATOR>
<NOOFPAYOUTSTND>0</NOOFPAYOUTSTND>
<WRSTHSTNOPAY>-</WRSTHSTNOPAY>
<OVERDUEAMOUNT>0</OVERDUEAMOUNT>
<ISLEGALACTIONTKN>N</ISLEGALACTIONTKN>
<ISLEGALACTIONTKNIND>0</ISLEGALACTIONTKNIND>
<INSTITUTION_TYPE>Local Bank</INSTITUTION_TYPE>
<INSTITUTION_NAME>Burgan Bank</INSTITUTION_NAME>
<DOCUMENT_STATUS>Not Required</DOCUMENT_STATUS>
<SUPPORTINGDOCUMENTSTATUS>-</SUPPORTINGDOCUMENTSTATUS>
</Cinet_ClsdAcDtls>
<Cinet_ClsdAcDtls>
<ACCOUNTNO>-</ACCOUNTNO>
<ACCOUNTTYPE>Consumer Finance</ACCOUNTTYPE>
<ACCOUNTOPNDT>27-08-2017</ACCOUNTOPNDT>
<CRDLIMIT>832</CRDLIMIT>
<PAYMENTAMOUNT>35</PAYMENTAMOUNT>
<ACCCLSDDT>17-06-2019</ACCCLSDDT>
<REASONCLS>Closed by Customer</REASONCLS>
<ASTCLASSIFICATION>Standard</ASTCLASSIFICATION>
<STSACC>Up to date current</STSACC>
<WRSTHSTACSTATUS>Arrears</WRSTHSTACSTATUS>
<ISWRKSTHSTINDICATOR>0</ISWRKSTHSTINDICATOR>
<NOOFPAYOUTSTND>0</NOOFPAYOUTSTND>
<WRSTHSTNOPAY>2</WRSTHSTNOPAY>
<OVERDUEAMOUNT>0</OVERDUEAMOUNT>
<ISLEGALACTIONTKN>N</ISLEGALACTIONTKN>
<ISLEGALACTIONTKNIND>0</ISLEGALACTIONTKNIND>
<INSTITUTION_TYPE>MOC Companies</INSTITUTION_TYPE>
<INSTITUTION_NAME>-</INSTITUTION_NAME>
<DOCUMENT_STATUS>-</DOCUMENT_STATUS>
<SUPPORTINGDOCUMENTSTATUS>-</SUPPORTINGDOCUMENTSTATUS>
</Cinet_ClsdAcDtls>
</Cinet_ClsdAcDtls>
<Cinet_Guaranteed_Open_Loan />
<Cinet_Guaranteed_Clsd_Loan>
<Cinet_Guaranteed_Clsd_Loan>
<ACCOUNTNO>-</ACCOUNTNO>
<ACCOUNTTYPE>Consumer Finance</ACCOUNTTYPE>
<ACCOUNTOPNDT>17-07-2002</ACCOUNTOPNDT>
<CRDLIMIT>7710</CRDLIMIT>
<PAYMENTAMOUNT>110</PAYMENTAMOUNT>
<ACCCLSDDT>03-06-2005</ACCCLSDDT>
<REASONCLS>Closed by Customer</REASONCLS>
<STSACC>Up to date current</STSACC>
<WRSTHSTACSTATUS>Up to date current</WRSTHSTACSTATUS>
<ISWRKSTHSTINDICATOR>0</ISWRKSTHSTINDICATOR>
<NOOFPAYOUTSTND>0</NOOFPAYOUTSTND>
<WRSTHSTNOPAY>0</WRSTHSTNOPAY>
<OVERDUEAMOUNT>0</OVERDUEAMOUNT>
<ISLEGALACTIONTKN>N</ISLEGALACTIONTKN>
<ISLEGALACTIONTKNIND>0</ISLEGALACTIONTKNIND>
<INSTITUTION_TYPE>Investment Co.</INSTITUTION_TYPE>
<WRITE_OFF_AMNT>0</WRITE_OFF_AMNT>
<INSTITUTION_NAME>-</INSTITUTION_NAME>
</Cinet_Guaranteed_Clsd_Loan>
<Cinet_Guaranteed_Clsd_Loan>
<ACCOUNTNO>-</ACCOUNTNO>
<ACCOUNTTYPE>Consumer Finance</ACCOUNTTYPE>
<ACCOUNTOPNDT>08-04-2008</ACCOUNTOPNDT>
<CRDLIMIT>9316</CRDLIMIT>
<PAYMENTAMOUNT>155</PAYMENTAMOUNT>
<ACCCLSDDT>04-12-2011</ACCCLSDDT>
<REASONCLS>Closed by Customer</REASONCLS>
<STSACC>Up to date current</STSACC>
<WRSTHSTACSTATUS>Up to date current</WRSTHSTACSTATUS>
<ISWRKSTHSTINDICATOR>0</ISWRKSTHSTINDICATOR>
<NOOFPAYOUTSTND>0</NOOFPAYOUTSTND>
<WRSTHSTNOPAY>0</WRSTHSTNOPAY>
<OVERDUEAMOUNT>0</OVERDUEAMOUNT>
<ISLEGALACTIONTKN>N</ISLEGALACTIONTKN>
<ISLEGALACTIONTKNIND>0</ISLEGALACTIONTKNIND>
<INSTITUTION_TYPE>Investment Co.</INSTITUTION_TYPE>
<WRITE_OFF_AMNT>0</WRITE_OFF_AMNT>
<INSTITUTION_NAME>-</INSTITUTION_NAME>
</Cinet_Guaranteed_Clsd_Loan>
</Cinet_Guaranteed_Clsd_Loan>
<Cinet_InquiryHistory>
<Cinet_InquiryHistory>
<INQNUMBER>1</INQNUMBER>
<INQUIRYDATE>05-07-2022</INQUIRYDATE>
<INSTITUTIONTYPE>MOC Companies</INSTITUTIONTYPE>
<INQUIRYPURPOSE>New Account Inquiry</INQUIRYPURPOSE>
<FACILITYTYPE>Consumer Loan</FACILITYTYPE>
<AMOUNT>1</AMOUNT>
<RELATIONSHIP_TO_ACCOUNT>Main Applicant</RELATIONSHIP_TO_ACCOUNT>
<PAYMENT_AMOUNT>1</PAYMENT_AMOUNT>
<PAYMENT_FREQUENCY>Monthly</PAYMENT_FREQUENCY>
<DURATION_OF_AGREEMENT>1</DURATION_OF_AGREEMENT>
<INSTITUTION_NAME>-</INSTITUTION_NAME>
<APPROVAL_DATE>-</APPROVAL_DATE>
<CONVERTED_TO_ACCOUNT_ON>-</CONVERTED_TO_ACCOUNT_ON>
<REASONFORINQUIRY>New credit account</REASONFORINQUIRY>
</Cinet_InquiryHistory>
<Cinet_InquiryHistory>
<INQNUMBER>2</INQNUMBER>
<INQUIRYDATE>11-06-2022</INQUIRYDATE>
<INSTITUTIONTYPE>MOC Companies</INSTITUTIONTYPE>
<INQUIRYPURPOSE>New Account Inquiry</INQUIRYPURPOSE>
<FACILITYTYPE>Consumer Loan</FACILITYTYPE>
<AMOUNT>1</AMOUNT>
<RELATIONSHIP_TO_ACCOUNT>Main Applicant</RELATIONSHIP_TO_ACCOUNT>
<PAYMENT_AMOUNT>1</PAYMENT_AMOUNT>
<PAYMENT_FREQUENCY>Monthly</PAYMENT_FREQUENCY>
<DURATION_OF_AGREEMENT>1</DURATION_OF_AGREEMENT>
<INSTITUTION_NAME>-</INSTITUTION_NAME>
<APPROVAL_DATE>-</APPROVAL_DATE>
<CONVERTED_TO_ACCOUNT_ON>-</CONVERTED_TO_ACCOUNT_ON>
<REASONFORINQUIRY>New credit account</REASONFORINQUIRY>
</Cinet_InquiryHistory>
</Cinet_InquiryHistory>
<Cinet_EmploymentHistory>
<Cinet_EmploymentHistory>
<NUMBERORDER>1</NUMBERORDER>
<DATEREPORTED>31-05-2014</DATEREPORTED>
</Cinet_EmploymentHistory>
</Cinet_EmploymentHistory>
<Cinet_ContactHistory>
<Cinet_ContactHistory>
<NUMBERORDER>1</NUMBERORDER>
<DETAILS>625068</DETAILS>
<TYPE>Work Telephone</TYPE>
<DATEREPORTED>04-12-2011</DATEREPORTED>
</Cinet_ContactHistory>
</Cinet_ContactHistory>
<Cinet_GetReportComments />
<Cinet_AddressHistory>
<Cinet_AddressHistory>
<NUMBERORDER>1</NUMBERORDER>
<DETAILS>?????? 1 ???? 6 ???? 16</DETAILS>
<TYPE>Residential</TYPE>
<DATEREPORTED>26-11-2020</DATEREPORTED>
</Cinet_AddressHistory>
</Cinet_AddressHistory>
<Cinet_GovernmentFamilyFundLoans>
<Cinet_GovernmentFamilyFundLoans>
<SNO>1</SNO>
<MANAGER_BANK>Burgan Bank</MANAGER_BANK>
<MESSAGE>?????? ??? ????? ????????? ????? 51 ??? ????? ??? ??????? ???????</MESSAGE>
<APPLICATION_DATE>18-10-2010</APPLICATION_DATE>
</Cinet_GovernmentFamilyFundLoans>
<Cinet_GovernmentFamilyFundLoans>
<SNO>2</SNO>
<MANAGER_BANK>Burgan Bank</MANAGER_BANK>
<MESSAGE>???? ?????? ?????? ???? ????? ????? ????????? ????? 51 ??? ????? - ??????</MESSAGE>
<APPLICATION_DATE>16-10-2011</APPLICATION_DATE>
<APPROVAL_DATE>16-10-2011</APPROVAL_DATE>
</Cinet_GovernmentFamilyFundLoans>
</Cinet_GovernmentFamilyFundLoans>
</DATAPACKET>
</LiveResponse>
```
This data package contains information about the financial profile of a particular user. Here are some key fields and descriptions of the values:
**ReportDetails:** Details of the report, user ID, branch information, report dates etc. contains.
**Cinet_SearchDetails:** Contains information such as the user's search details, name, ID type, and ID number.
**ConsumerDetails:** User's consumer details, name, nationality, gender, date of birth, address, etc. contains information.
**Cinet_GovtFundedLoan:** Contains information about government-backed loans, in which case the user is stated to have received a loan from a family fund.
**CreditProfileOverview:** Provides an overview of the credit profile, e.g. dates of worst-off loan accounts, amounts of outstanding balances, loan applications, etc.
**Cinet_LiabilitySummary:** Contains debt summary information, total debt amount, outstanding amounts, current installments etc.
**CreditProfileSummeryGraph:** Credit profile summary graph includes installment amount and delay amounts according to previous months.
**AssetClassificationGraph:** Asset classification graph includes classification of credit accounts by past months.
**Cinet_AccountClassification:** Contains account classification information, account type, number of accounts, total balance, past delays, etc.
**Cinet_TotalLiabilitySummary:** Contains total debt summary information, total balance, amounts of overdue installments, etc.
**Cinet_OverdueHistoryGraph:** The latency history graph contains latency amounts over different latency ranges.
**Cinet_ExposurebyProduct:** Contains exposure information by product, product type, number of accounts, delayed installments, etc.
**Cinet_OverdueGraph:** Based on these data, it is understood whether there are any overdue payments in the loan payment process.
**Cinet_CreditUtilGraph:** Based on this data, it is seen that the percentage value of loan usage and whether the credit limit has been exceeded.
**Cinet_InquirySummary:** Based on this data, it appears that there have been account review inquiries made in the last 12 months.
**Cinet_InstTypeGraph:** Shows how much investigations have been made from which institutions
**Cinet_InqReasonGraph:** Shows how much the query was made for which reason
**Cinet_AccDtls:** Shows open accounts
**Cinet_ClsdAcDtls:** Shows closed accounts
**Cinet_Guaranteed_Open_Loan:** Shows open loan datas
**Cinet_Guaranteed_Clsd_Loan:** Shows closed loan datas
**Cinet_InquiryHistory:** This tag contains a sub-tag that contains the history of the customer's loan applications. Each subtag contains the details of a loan application.
**Cinet_EmploymentHistory:** This tag contains a subtag containing the customer's employment history.
**Cinet_ContactHistory:** This tag contains the customer's contact history
**Cinet_GetReportComments:** this tag contains report comments
**Cinet_AddressHistory:** This label contains the customer's address history
**Cinet_GovernmentFamilyFundLoans:** This label contains the client's government family fund loans.
This data package provides an overview of the user's financial situation. Using this data, the user's credit history, debt status, asset classification, etc. analyzes can be made. However, it is important to know more about the full meaning and value of each field and to interpret the data accordingly.
#### Sample response analysis
According to the details of the report, the order date of the report is 10 May 2023, your branch is Hawalli Branch and the transaction number is 9770835. The report has been prepared by Burgan Bank. (ReportDetails)
According to the data in the report, the name and identity information of the researched person was hidden. However, the report contains information such as the name, nationality (Kuwait), gender (male) and date of birth (07 July 1973) of the researched person. It also includes information such as the person’s identification number, address, and reported date. (ConsumerDetails)
The report states that he did not receive a government-supported loan or grant, has no legal status, and has not written off any debts. It is also stated that there is a fund loan for the family and the past income of the person is a certain amount. (Cinet_GovtFundedLoan)
The credit profile provides an overview. It is stated that the worst case account is up to date, the worst historical case is delays, and the highest latency is 60 days. In addition, information such as the person’s total delay amount, total written amount and loan utilization rate are included in the report. (CreditProfileOverview, Cinet_LiabilitySummary, CreditProfileSummeryGraph)
The report also includes information such as asset classification, account classification and total liability summary. This information shows the status and liabilities of the person’s accounts. (AssetClassificationGraph, Cinet_TotalLiabilitySummary)
In addition, other graphs such as exposure by product, delay history and credit utilization rate are also included in the report. (Cinet_OverdueHistoryGraph)
#### **The following details are needed for further analysis**
**We tried to match the data you use on Response.**
Cinet Transaction Number:
• Tag: < TRANSACTION_NUMBER >
• Result: " 9770835 "
Total O/S Liability:
• Tag: <TTLLIABILITYAMNT>
• Result: "64552"
Total Installment:
• Tag: <TTL_CURRENT_INSTALLMENT>
• Result: "740"
Open Litigation:
• Tag: <LEGALINDICATOR>
• Result: "NO" (False)
Litigation Date:
• Tag: Couldn't find
• Result: "-"
For Applicant Cinet Liability, we can fill in the following information as a list:
Type of Account:
• Tag: <ACCOUNTTYPE>
• Result: "Family Fund Loan"
Original ATM (Credit amount):
• Tag: <AMOUNTOFFINANCE>
• Result: "56842"
Outstanding (Remaining Amount):
• Tag: <OUTSTANDINGBALANCE>
• Result: "23369"
Installment AMT (Installation amount):
• Tag: <LASTINSTALLMENTPAYMENTAMOUNT>
• Result: "316"
Defaulted AMT:
• Tag: Couldn't find
No of Defaults:
• Tag: Couldn't find
Relationship to Account:
• Tag: Couldn't find
Lender Type:
• Tag: <INSTITUTION_TYPE>
• Result: "Local Bank"
Litigation Status:
• Tag: <ISLEGALACTIONTKN>
• Result: "N"
Closed Litigation Date:
• Tag: Couldn't find
CAIS/CAPS:
•Tag : Couldn't find
Is our analysis correct?
Which fields are used and how are they used according to the request result? A detailed explanation should be made by specifying the parameters.
We think that you have an algorithm that prepares the output you specified according to the response return.
Can you share this algorithm with us?
## 4- End of Service Calculation Algorithm
Could you share End of Service Calculation Algorithm?
Can we use these rules detailed in the link below?
https://kuwaitnewz.com/kuwait-indemnity-calculator/
### Answer for the question asked
End of Service Benefit Calculation
```sql!
If (Emp_period > 0, IF(Nationality="KWT","",IF(EMPLOYER_CLASS="MOD",MIN((basic_sal +social_allowance) * (IF(EMPLOYER_CLASS=MOD,MIN(TRUNC(NO_of_YEARS),5)*1.5, IF(EMPLOYER_CLASS=OTH, MIN (TRUNC (NO_of_YEARS), 5 ) * 0.5 )) + IF(EMPLOYER_CLASS=MOD,MAX (MIN(TRUNC (NO_of_YEARS) -5,5)*2, 0), IF(EMPLOYER_CLASS=OTH ,MAX(MIN(TRUNC(NO_of_YEARS)-5,5)*1,0))) + IF(EMPLOYER_CLASS="MOD", MAX((TRUNC(NO_of_YEARS)-10)*2.5,0), IF(G531="OTH",MAX((TRUNC (NO_of_YEARS) – 10 )*1,0))) ),70000),
IF(EMPLOYER_CLASS="OIL",MIN((basic_sal +social_allowance)*M24,70000),
IF(EMPLOYER_CLASS="OTH", MIN ((basic_sal + social_allowance) * (IF(EMPLOYER_CLASS=MOD,MIN(TRUNC (NO_of_YEARS) , 5 ) * 1.5 , IF(EMPLOYER_CLASS=OTH, MIN (TRUNC(NO_of_YEARS),5)*0.5)) + IF(EMPLOYER_CLASS=MOD, MAX(MIN(TRUNC(NO_of_YEARS) - 5,5) * 2 , 0 ) ,
IF(EMPLOYER_CLASS = OTH , MAX(MIN(TRUNC (NO_of_YEARS)- 5 , 5 )*1,0))) + IF(EMPLOYER_CLASS="MOD",MAX((TRUNC(NO_of_YEARS) -10) * 2.5 ,0 ), IF(EMPLOYER_CLASS="OTH", MAX((TRUNC(NO_of_YEARS) -10) * 1, 0 ))) ),70000))))),"")
```
### Our comment is as follows.
This calculation represents an end-of-service payment calculation. Although the related formula may seem quite complex, I will try to explain it step by step below:
The first condition is "Emp_period > 0". This condition controls that the employee's length of service must be greater than zero. If this condition is not met, the result will be an empty value and the payment will not be calculated.
In the second condition, the citizenship status of the employee is checked. If the employee's citizenship status is "KWT" (Kuwait), the result will be an empty value and the payment will not be calculated. This condition is used to check a special case for Kuwaiti citizens only.
In the third condition, a series of calculations are made if the employer class is "MOD". If the employer class is not "MOD", the fourth condition is passed.
In the fourth condition, another calculation is made if the employer class is "OIL". This condition provides for a special calculation for those working in the oil industry. If the employer class is not "OIL", the fifth and final condition is passed.
In the fifth condition, the employer class is determined as "OTH" (other) and another calculation is made. This condition ensures that a calculation is valid for other employer classes.
Calculations are carried out using the basic salary of the employee and the amount of benefits. The IF and MAX functions are used to calculate coefficients and surcharges based on employer class and length of service.
Service time is calculated by applying different coefficients and additional payments above certain threshold values. This allows the employee to receive a higher pay if they work longer.
The result of the calculation cannot be greater than 70,000. If the calculation result is greater than 70,000, the result is considered 70,000.
This formula makes different calculations based on the employee's length of service, citizenship status and employer class. This calculation is used to automate the end-of-service payment calculation and to ensure appropriate payments in different situations.
#### The following details are needed for further analysis
Is our analysis correct? Could you elaborate on where the inputs required for the calculation come from?
What system is this table on? In Core Banking systems? Or is it in ultimus’ own database?
According to your answer, this calculation is only for non-Kuwaiti citizens. Did we get it right?
## 5- Scoring and Eligible Loan Algorithms
Could you share detailed algoritims of applicant scoring and loan limit calculations.
### Answer for the question asked
Ascore/BScore Input Output Values
```sql!
P_BRA_CODE IN VARCHAR2,
P_CUS_NUM IN VARCHAR2,
P_NAT_NUM IN VARCHAR2, --CVIL ID
P_BIR_DATE IN DATE, -- Age can be calculated
P_NATIONALITY IN VARCHAR2, -- TAB=10
-- P_CUS_DATE_OPEN IN DATE, -- CUSTOMER SINCE, VITAGE CAN BE CALCULATED FROM THIS not ultimus
P_EMP_CODE IN NUMBER, -- emp code as per emp_list -- 547 for others then name will be there
P_EMP_CODE_OTH_name IN VARCHAR2, -- Name of the employer if not available in emp_list
P_EDU_LVL IN NUMBER ,
P_MAR_STATUS IN NUMBER ,
P_RES_OWNERSHIP IN NUMBER, -- TAB 1867
P_CAR_OWNERSHIP IN NUMBER, -- Not applicable
P_EMP_DATE IN DATE, -- LAST EMPLOYMENT DT
P_EMP_TYPE IN NUMBER , --tab_id=1467 employment sector;
P_EMP_CLASS IN NUMBER , --tab_id=1468;
P_PROF_CODE IN NUMBER , -- OCCUPATION -- TAB_ID=810
P_RETIRE_FLAG IN NUMBER , -- 1 FOR RETIRED AND 0 FOR EMPLOYED
-- P_SAL_ACT_DATE_OPEN IN DATE, -- year can be calculated from this
P_SAL_AMT_BANK IN NUMBER,
P_SAL_AMT_CERTI IN NUMBER,
P_SOC_ALLOWANC IN NUMBER,
P_SAL_AMT_APP IN NUMBER,
P_INDEM_AMT IN NUMBER,
P_LOAN_INS_COUNT IN NUMBER,
P_LOAN_CUR_AMT IN NUMBER,
P_INSTALLMENT IN NUMBER,
P_DELQ_DAYS IN NUMBER,
P_ASCORE OUT NUMBER,
P_BSCORE OUT NUMBER,
P_LOAN_AMT OUT NUMBER,
P_CC_AMT OUT NUMBER,
P_CHG_AMT OUT NUMBER,
P_PD OUT NUMBER,
P_TENURE out number,
P_remarks out varchar2);
```
### Our comment is as follows.
#### Input Parameters:
**P_BRA_CODE:** An input parameter of type VARCHAR2. Represents the bank code.
**P_CUS_NUM:** An input parameter of type VARCHAR2. Represents the customer number.
**P_NAT_NUM:** An input parameter of type VARCHAR2. Represents a value called CVIL ID.
**P_BIR_DATE:** An input parameter of type DATE. It represents the date of birth.
**P_NATIONALITY:** An input parameter of type VARCHAR2. It represents national identity.
**P_EMP_CODE:** An input parameter of type NUMBER. Represents employer code. For others, it may be 547 and the name is included in this parameter.
**P_EMP_CODE_OTH_name:** An input parameter of type VARCHAR2. Represents the employer name. This parameter is used if it is not found in emp_list.
**P_EDU_LVL:** An input parameter of type NUMBER. It represents the level of education.
**P_MAR_STATUS:** An input parameter of type NUMBER. It represents marital status.
**P_RES_OWNERSHIP:** An input parameter of type NUMBER. It represents home ownership.
**P_CAR_OWNERSHIP:** An input parameter of type NUMBER. It represents car ownership.
**P_EMP_DATE:** An input parameter of type DATE. It represents the last employment date.
**P_EMP_TYPE:** An input parameter of type NUMBER. It represents the employment sector.
**P_EMP_CLASS:** An input parameter of type NUMBER. It represents the employment class.
**P_PROF_CODE:** An input parameter of type NUMBER. It represents the profession.
**P_RETIRE_FLAG:** An input parameter of type NUMBER. It represents whether he is retired or not.
**P_SAL_AMT_BANK:** An input parameter of type NUMBER. It represents the amount of salary deposited in the bank account.
**P_SAL_AMT_CERTI:** An input parameter of type NUMBER. It represents the amount of salary verified with a certificate.
**P_SOC_ALLOWANC:** An input parameter of type NUMBER. It represents the amount of social assistance.
**P_SAL_AMT_APP:** An input parameter of type NUMBER. It represents the amount of approved salary.
**P_INDEM_AMT:** An input parameter of type NUMBER. It represents the amount of compensation.
**P_LOAN_INS_COUNT:** An input parameter of type NUMBER. It represents the number of credit insurance.
**P_LOAN_CUR_AMT:** An input parameter of type NUMBER. It represents the current loan amount.
**P_INSTALLMENT:** An input parameter of type NUMBER. It represents the loan installment amount.
**P_DELQ_DAYS:** An input parameter of type NUMBER. It represents the number of days of delay.
#### Output Parameters:
**P_ASCORE:** An output parameter of type NUMBER. It represents the A score.
**P_BSCORE:** An output parameter of type NUMBER. It represents the B score.
**P_LOAN_AMT:** An output parameter of type NUMBER. Represents the loan amount.
**P_CC_AMT:** An output parameter of type NUMBER. Represents the credit card amount.
**P_CHG_AMT:** An output parameter of type NUMBER. Represents the amount of change.
**P_PD:** An output parameter of type NUMBER. It represents the PD (insolvency) value.
**P_TENURE:** An output parameter of type NUMBER. It represents the loan term.
**P_remarks:** An output parameter of type VARCHAR2. Represents comments.
This algorithm seems to perform an operation that calculates output values such as P_ASCORE, P_BSCORE, P_LOAN_AMT, P_CC_AMT, P_CHG_AMT, P_PD, P_TENURE, and P_remarks using input parameters. More details are needed to specify the exact workings of the algorithm.
#### The following details are needed for further analysis
Is our analysis correct?
You only shared the spec part of the procedure.
Can you share the body part of this procedure with us?
Also, can you share in detail what the output values here mean and why they are used?
What system is this stored procedure on? In Core Banking systems? Or is it in ultimus’ database?
We have seen in a document that the EIM web service is used for score calculation.
Can you share sample request and response for this service?
Sign in with Wallet
Connect another wallet