ifrsdeals - Hari-dta/hari GitHub Wiki
`
CREATE VIEW ifrs9.deals_v AS SELECT concat(nvl(replace(fact_deals.fact_deal_id, ' ', ''), fact_deals.dim_deal_id), '-', fact_deals.dim_product_id, '-', fact_deals.acc_brnch_no, fact_deals.basic_account_no, fact_deals.account_sufix) DEAL_KEY, fact_deals.fact_deal_id, fact_deals.dim_customer_id, fact_deals.dim_account_id, fact_deals.dim_deal_id dim_deal_id, fact_deals.dim_currency_id, fact_deals.dim_product_id, fact_deals.dim_startdate_id, CASE WHEN ((fact_deals.dim_product_id IN ('600010', '2900016', '2900015', '2800018', '3200005', '500021', '8', '11')) AND (((CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'EQ') OR (CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'DP')) OR (CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'OLD-CL'))) THEN CAST(add_months(fact_deals.load_date, 36) AS TIMESTAMP) WHEN ((((CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'EQ') OR (CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'DP')) OR (CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'OLD-CL')) AND (upper(fact_deals.dim_product_id) NOT IN ('600010', '2900016', '2900015', '2800018', '3200005', '500021', '8', '11'))) THEN CAST(add_months(fact_deals.load_date, 12) AS TIMESTAMP) WHEN ((fact_deals.dim_product_id IN ('4000014', '4000012', '4100002', '4100001')) OR ((CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END = 'EQ') AND (fact_deals.dim_product_id IN ('900016', '800021')))) THEN CAST(add_months(fact_deals.load_date, 12) AS TIMESTAMP) ELSE fact_deals.dim_maturitydate_id END dim_maturitydate_id, fact_deals.dim_interest_due_date_id, fact_deals.dim_interest_maturity_date_id, fact_deals.dim_commitment_agreement_date_id, fact_deals.dim_commitment_expiry_date_id, CASE WHEN (fact_deals.dim_product_id = '2400023' AND (CASE WHEN (fact_deals.deal_type IN ('1', '2', '3', '4', '5', '6', '7', '14')) THEN 'EXM' WHEN fact_deals.deal_type = 'DP' THEN 'EQ' WHEN fact_deals.deal_type = 'AC-DBL' THEN 'ECC' ELSE fact_deals.deal_type END IN ('EQ', 'DP'))) THEN CASE WHEN substr(CAST(fact_deals.dim_startdate_id AS STRING), 1, 10) IN ('1799-12-31', '1800-01-01') THEN NULL WHEN (fact_deals.dim_startdate_id > fact_deals.load_date) THEN NULL ELSE fact_deals.dim_startdate_id END ELSE CASE WHEN substr(CAST(fact_deals.dim_duedate_id AS STRING), 1, 10) IN ('1799-12-31', '1800-01-01') THEN NULL WHEN (fact_deals.dim_duedate_id > fact_deals.load_date) THEN NULL ELSE fact_deals.dim_duedate_id END END dim_duedate_id, fact_deals.dim_closuredate_id, fact_deals.dim_coa_id, fact_deals.limit_category, fact_deals.late_pmt_interest_amnt, fact_deals.late_pmt_interest_amnt_lcl, fact_deals.late_pmt_interest_amnt_usd, fact_deals.approved_limit, fact_deals.approved_limit_lcl, fact_deals.approved_limit_usd, fact_deals.installement_amnt, fact_deals.installement_amnt_lcl, fact_deals.installement_amnt_usd, fact_deals.no_of_installement, fact_deals.no_of_paid_install, fact_deals.no_of_due_install, fact_deals.purchase_amnt, fact_deals.purchase_amnt_lcl, fact_deals.purchase_amnt_usd, fact_deals.appriasal_amnt, fact_deals.appriasal_amnt_lcl, fact_deals.appriasal_amnt_usd, fact_deals.balance_amnt, fact_deals.balance_amnt_lcl, fact_deals.balance_amnt_usd, fact_deals.interest_amnt, fact_deals.interest_amnt_lcl, fact_deals.interest_amnt_usd, fact_deals.due_balance_amnt, fact_deals.due_balance_amnt_lcl, fact_deals.due_balance_amnt_usd, fact_deals.due_interest_amnt, fact_deals.due_interest_amnt_lcl, fact_deals.due_interest_amnt_usd, fact_deals.posted_interest_amnt, fact_deals.posted_interest_amnt_lcl, fact_deals.posted_interest_amnt_usd, fact_deals.penalty_interest_amnt, fact_deals.penalty_interest_amnt_lcl, fact_deals.penalty_interest_amnt_usd, fact_deals.settlemnt_paid_amnt, fact_deals.settlemnt_paid_amnt_lcl, fact_deals.settlemnt_paid_amnt_usd, fact_deals.original_loan_amnt, fact_deals.original_loan_amnt_lcl, fact_deals.original_loan_amnt_usd, fact_deals.no_of_tenure, fact_deals.loan_to_value_ratio, fact_deals.commitment_amnt, fact_deals.commitment_amnt_lcl, fact_deals.commitment_amnt_usd, fact_deals.accrued_interest_amnt, fact_deals.accrued_interest_amnt_lcl, fact_deals.accrued_interest_amnt_usd, fact_deals.deal_type, fact_deals.debit_burden_ratio, fact_deals.interest_start_date, fact_deals.loan_purpose, fact_deals.loan_status, fact_deals.migration_date, fact_deals.scheme_description, fact_deals.scheme_id, fact_deals.first_installment_date, fact_deals.interest_rate, fact_deals.interest_type, fact_deals.emi, fact_deals.sanction_date, fact_deals.our_engagment, fact_deals.self_liquidity, fact_deals.available_by, fact_deals.beneficiary_name, fact_deals.beneficiary_address, fact_deals.country_origin, fact_deals.commission_percentage, fact_deals.issuance_commission, fact_deals.amendment_type, fact_deals.old_date, fact_deals.purpose_guarantee, fact_deals.ineterst_frequency, fact_deals.actual_rate, fact_deals.base_rate, fact_deals.differential_rate, fact_deals.tire_rate, fact_deals.commitment_ccy, fact_deals.commitment_reference, fact_deals.negotiation_bank, fact_deals.issuing_bank, fact_deals.advising_bank, fact_deals.load_date, fact_deals.suspended_interest, fact_deals.suspended_interest_lcl, fact_deals.suspended_interest_usd, fact_deals.suspended_commission, fact_deals.suspended_commission_lcl, fact_deals.suspended_commission_usd, fact_deals.cbj_customer_classification, fact_deals.cbj_facility_classification, fact_deals.cbj_provision, fact_deals.cbj_provision_lcl, fact_deals.cbj_provision_usd, fact_deals.tp_rate, fact_deals.payment_frequency, fact_deals.payment_frequency_description, fact_deals.interest_frequency, fact_deals.interest_frequency_description, fact_deals.amortization_type, fact_deals.basic_account_no, fact_deals.account_sufix, fact_deals.acc_brnch_no, fact_deals.partition_load_date, fact_deals.load_country, fact_deals.sql_seq FROM ifrs9.deals fact_deals
output
| deal_key | fact_deal_id | dim_customer_id | dim_account_id | dim_deal_id | dim_currency_id | dim_product_id | dim_startdate_id | dim_maturitydate_id | dim_interest_due_date_id | dim_interest_maturity_date_id | dim_commitment_agreement_date_id | dim_commitment_expiry_date_id | dim_duedate_id | dim_closuredate_id | dim_coa_id | limit_category | late_pmt_interest_amnt | late_pmt_interest_amnt_lcl | late_pmt_interest_amnt_usd | approved_limit | approved_limit_lcl | approved_limit_usd | installement_amnt | installement_amnt_lcl | installement_amnt_usd | no_of_installement | no_of_paid_install | no_of_due_install | purchase_amnt | purchase_amnt_lcl | purchase_amnt_usd | appriasal_amnt | appriasal_amnt_lcl | appriasal_amnt_usd | balance_amnt | balance_amnt_lcl | balance_amnt_usd | interest_amnt | interest_amnt_lcl | interest_amnt_usd | due_balance_amnt | due_balance_amnt_lcl | due_balance_amnt_usd | due_interest_amnt | due_interest_amnt_lcl | due_interest_amnt_usd | posted_interest_amnt | posted_interest_amnt_lcl | posted_interest_amnt_usd | penalty_interest_amnt | penalty_interest_amnt_lcl | penalty_interest_amnt_usd | settlemnt_paid_amnt | settlemnt_paid_amnt_lcl | settlemnt_paid_amnt_usd | original_loan_amnt | original_loan_amnt_lcl | original_loan_amnt_usd | no_of_tenure | loan_to_value_ratio | commitment_amnt | commitment_amnt_lcl | commitment_amnt_usd | accrued_interest_amnt | accrued_interest_amnt_lcl | accrued_interest_amnt_usd | deal_type | debit_burden_ratio | interest_start_date | loan_purpose | loan_status | migration_date | scheme_description | scheme_id | first_installment_date | interest_rate | interest_type | emi | sanction_date | our_engagment | self_liquidity | available_by | beneficiary_name | beneficiary_address | country_origin | commission_percentage | issuance_commission | amendment_type | old_date | purpose_guarantee | ineterst_frequency | actual_rate | base_rate | differential_rate | tire_rate | commitment_ccy | commitment_reference | negotiation_bank | issuing_bank | advising_bank | load_date | suspended_interest | suspended_interest_lcl | suspended_interest_usd | suspended_commission | suspended_commission_lcl | suspended_commission_usd | cbj_customer_classification | cbj_facility_classification | cbj_provision | cbj_provision_lcl | cbj_provision_usd | tp_rate | payment_frequency | payment_frequency_description | interest_frequency | interest_frequency_description | amortization_type | basic_account_no | account_sufix | acc_brnch_no | partition_load_date | load_country | sql_seq |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 5003IBC202000559-D01-A-4000002-5003896540550 | 5003IBC202000559-D01 - A | A70E6D63FED2E | BA02BFDDDAFAC | 5003IBC202000559-D01 | USD | 4000002 | 2020-01-27 00:00:00 | 2020-03-28 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | NULL | 2020-03-28 00:00:00 | 51311 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 181850.60000000 | 9262378.46040000 | 181850.60000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 181850.60000000 | 9262378.46040000 | 181850.60000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 2 | NULL | NULL | NULL | NULL | NULL | ACCEPTANCES INWARD BC's | INBC-A | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 7516.74000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2025-04-27 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 896540 | 550 | 5003 | 2025-04-27 | EG | 9003 | ||
| 5003IBC202302345-D01-A-4000002-5003468146550 | 5003IBC202302345-D01 - A | 0E92C1F3DCF84 | DADDA1C12A97C | 5003IBC202302345-D01 | USD | 4000002 | 2025-04-27 00:00:00 | 2024-05-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | NULL | 2024-05-01 00:00:00 | 51311 | LS001 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 202166.67000000 | 10297157.16978000 | 202166.67000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 233800.00000000 | 11908369.20000000 | 233800.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 1 | NULL | NULL | NULL | NULL | NULL | ACCEPTANCES INWARD BC's | INBC-A | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 620.00000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | RCCSCNBSXXX | NULL | 2025-04-27 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 468146 | 550 | 5003 | 2025-04-27 | EG | 9003 |
| 5003IBC202405449-D01-A-4000002-5003896540550 | 5003IBC202405449-D01 - A | A70E6D63FED2E | BA02BFDDDAFAC | 5003IBC202405449-D01 | USD | 4000002 | 2024-09-17 00:00:00 | 2025-03-03 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | NULL | 2025-03-03 00:00:00 | 51311 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 83285.00000000 | 4242038.19000000 | 83285.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 83285.00000000 | 4242038.19000000 | 83285.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 2 | NULL | NULL | NULL | NULL | NULL | ACCEPTANCES INWARD BC's | INBC-A | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 52430.71000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2025-04-27 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 896540 | 550 | 5003 | 2025-04-27 | EG | 9003 | ||
| 5005IBC201804347-D01-A-4000002-5005207039557 | 5005IBC201804347-D01 - A | 12DF130F42BFF | 8DB99B374515E | 5005IBC201804347-D01 | EUR | 4000002 | 2018-05-09 00:00:00 | 2018-08-11 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | NULL | 2018-08-11 00:00:00 | 51311 | LS001 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 55595.00000000 | 3218179.03171100 | 63183.31628600 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 55595.00000000 | 3218179.03171100 | 63183.31628600 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 2 | NULL | NULL | NULL | NULL | NULL | ACCEPTANCES INWARD BC's | INBC-A | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 9593.04000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2025-04-27 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 207039 | 557 | 5005 | 2025-04-27 | EG | 9003 | |
| 5005IBC201902297-D01-A-4000002-5005245456550 | 5005IBC201902297-D01 - A | 188F9BCDD0D9F | 9469CA91AD9F0 | 5005IBC201902297-D01 | USD | 4000002 | 2019-03-20 00:00:00 | 2019-06-07 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | 1800-01-01 00:00:00 | NULL | 2019-06-07 00:00:00 | 51311 | LS001 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 112944.00000000 | 5752689.69600000 | 112944.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 112944.00000000 | 5752689.69600000 | 112944.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 0.00000000 | 2 | NULL | NULL | NULL | NULL | NULL | ACCEPTANCES INWARD BC's | INBC-A | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 29747.68000000 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2025-04-27 00:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 245456 | 550 | 5005 | 2025-04-27 | EG | 9003 |
`