Разработка хранилища данных для микрофинансовой организации в Индии
Клиенты и продукты микрофинансовой организации, каналы связи и продаж. Алгоритм получения кредита и требования к хранилищу данных. Общая характеристика компании, сбор функциональных и бизнес-требований. Согласование модели данных. Разработка фреймворка.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | дипломная работа |
| Язык | русский |
| Дата добавления | 28.11.2019 |
| Размер файла | 1,4 M |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
· Данные на источнике изменились
· Требуемые записи были явно отфильтрованы некоторым условием
· Требуемые записи не попали в одно из пересечений множеств при работе с операторами left join и inner join.
·
Рисунок 36. Проверка гипотезы
Проверив проблемные договоры на гипотезу, что для них на источнике указан нерелевантный skp_client, и уведомив об этом банк, прототип посчитали согласованным.
По остальным прототипам подтверждение от банка получено не было.
3.6 Разработка фреймворка
Прототипы нужны для того, чтобы понять, что бизнес требования поняты корректно. Далее - разработка Фреймворка, позволяющего ежедневно запускать процесс по загрузке витрин [34]. Для этого требуется получить ответы на следующие вопросы:
· Нужно ли проводить инкрементальную загрузку или можно грузить полным срезом.
· Что будет триггером к старту загрузки.
На эти вопросы ответ пока получен не был. Несмотря на это, основная часть по разработке фреймворка выполнена. Как только будет подтверждение от бизнес-заказчика, потребуется поставить ETL процесс на регламент, и фреймворк можно считать разработанным.
3.7 Проведение UAT
Пока что проект не подошел к тестированию, так как от бизнес-заказчика не получены нужные сведения, по разработке фреймворка. Однако, тест кейсы были согласованы. Тестирование ведется следующим образом: за эталон принимается прототип. Данные сверяются с тем, что было загружено по результату ETL процесса, разработанного в рамках Фреймворка.
Следующие тесты необходимо провести, для принятия работ бизнес-заказчиком:
· Соответствие количества строк.
· Соответствие названий атрибутов
· Соответствие типов атрибутов
· Соответствие порядка атрибутов
· Соответствие качественного содержания атрибутов.
По результатам тестирования работа будет считаться принятой или отправлена на доработку.
Таким образом, после проведения приемо-сдаточных испытаний, проект можно считать завершенным.
Заключение
микрофинансовый бизнес фреймворк
Цель проекта, разработать витрины данных, достигнута. Базовые задачи по разработке выполнены, несмотря на то, что по части из задач требуется подтверждение со стороны бизнес-заказчика, поэтому проект нельзя считать завершенным. Однако, все задачи, в том числе по разработке фреймворка и тестированию, выполнены настолько, насколько это возможно в силу отсутствия подтверждения от бизнес-заказчика.
По завершению проекта будет разработана витрин данных, загрузка которых будет ежедневной. Структура данных будет соответствовать требованиям банка и приложений для запуска маркетинговых кампаний. Следующие сущности должны быть разработаны: client, application, contract, product, client's communication details.
Сбор бизнес-требований и функциональных требований осуществляется посредством GAP анализа, позволяющего соотнести желания бизнес-заказчика с возможностями проекта.
После загрузки витрины, ожидается, что данные будут передаваться в приложения, которые будут запускать кампании через каналы коммуникации: телефонные звонки, смс, мессенджеры, электронная почта, что повысит эффективность коммуникации банка с клиентами, в результате чего банк будет способен находить большее число платежеспособных клиентов.
Исходя из рассказанного следует, что проект имеет большую ценность для банка. Теоретическая часть подтверждает, что разработка хранилища для микрофинансовой организации в Индии имеет множество специфических требований, поэтому процесс разработки ведется при тесном контакте с заказчиком.
Список источников
1. Baland J.M., Somanathan R., Vandewalle L. Socially Disadvantaged Groups and Microfinance in India //Economic Development and Cultural Change. - 2019. - Т. 67. - №. 3. - С. 000-000.
2. Bonifati A. et al. Designing data marts for data warehouses //ACM transactions on software engineering and methodology. - 2001. - Т. 10. - №. 4. - С. 452-483.
3. Bosomworth D. Mobile marketing statistics 2015 //Leeds: Smart Insights (Marketing Intelligence) Ltd. - 2015.
4. Boyd J.H., De Nicolo G. The theory of bank risk taking and competition revisited //The Journal of finance. - 2005. - Т. 60. - №. 3. - С. 1329-1343.
5. Callen M. et al. Microcredit: Neither miracle nor mirage.
6. Chakravarty S., Jha A.N. Viability of «Credit Scoring in Microfinance» for Developing Countries //International Review of Social Sciences and Humanities. - 2012. - Т. 3. - №. 1. - С. 104-107.
7. Churchill C.F., Frankiewicz C. Making microfinance work: Managing for improved performance. - International Labour Organization, 2006.
8. Coltman T. Can superior CRM capabilities improve performance in banking //Journal of Financial Services Marketing. - 2007. - Т. 12. - №. 2. - С. 102-114.
9. Crookshanks E. Practical enterprise software development techniques: Tools and techniques for large scale solutions. - Apress, 2015.
10. Devlin B., Cote L.D. Data warehouse: from architecture to implementation. - Addison-Wesley Longman Publishing Co., Inc., 1996.
11. Doo N. Cost of Living 2013. - 2013.
12. Elmasri R., Navathe S. Fundamentals of database systems. - Addison-Wesley Publishing Company, 2010.
13. Ganski R.A., Wong H.K.T. Optimization of nested SQL queries revisited //ACM SIGMOD Record. - ACM, 1987. - Т. 16. - №. 3. - С. 23-33.
14. Google Our Mobile Planet, 2013
15. Han-Yuh L. Development of a framework for customer relationship management (CRM) in the banking industry //International Journal of Management. - 2007. - Т. 24. - №. 1. - С. 15.
16. Harrison G. Oracle SQL high-performance tuning. - Prentice Hall Professional Technical Reference, 2000.
17. Jennings M.D. Gap analysis: concepts, methods, and recent results //Landscape ecology. - 2000. - Т. 15. - №. 1. - С. 5-20.
18. Kimball R., Caserta J. The data warehouse ETL toolkit: practical techniques for extracting, cleaning, conforming, and delivering data. - John Wiley & Sons, 2011.
19. Kumar K., McKay C., Rotman S. Microfinance and mobile banking: The story so far //Focus note. - 2010. - Т. 62. - С. 1-16.
20. Meyer R.L. et al. The demand for flexible microfinance products: Lessons from Bangladesh //Journal of International development. - 2002. - Т. 14. - №. 3. - С. 351-368.
21. Mukherjee D., D'Souza D. Think phased implementation for successful data warehousing //Information Systems Management. - 2003. - Т. 20. - №. 2. - С. 82-90.
22. Newzoo Global Mobile Market Report 2018
23. Newzoo Global Mobile Market Report, April 2017
24. Numbeo. Cost of Living, 2016.
25. Oracle docs Gathering Optimizer Statistics
26. Poushter J. et al. Smartphone ownership and internet usage continues to climb in emerging economies //Pew Research Center. - 2016. - Т. 22. - С. 1-44.
27. Rajan R., Dhal S.C. Non-performing loans and terms of credit of public sector banks in India: An empirical assessment //Reserve Bank of India Occasional Papers. - 2003. - Т. 24. - №. 3. - С. 81-121.
28. Schreiner M. Credit scoring for microfinance: Can it work? //Journal of Microfinance/ESR Review. - 2000. - Т. 2. - №. 2. - С. 6.
29. Shukla S., Sinha A. Employee Turnover in banking sector: Empirical evidence //IOSR Journal of Humanities and Social Science. - 2013. - Т. 11. - №. 5. - С. 57-61.
30. Sumrell M. From waterfall to agile-how does a QA team transition //Proceedings of the Agile Conference (AGILE). - 2007. - С. 291-295.
31. Sureshchandra K., Shrinivasavadhani J. Moving from waterfall to agile //Agile 2008 conference. - IEEE, 2008. - С. 97-101.
32. Statista Share of mobile phone users that use a smartphone in India from 2014 to 2019
33. Telecom Commercial Communications Customer Preference Portal
34. Vassiliadis P., Simitsis A., Skiadopoulos S. Conceptual modeling for ETL processes //Proceedings of the 5th ACM international workshop on Data Warehousing and OLAP. - ACM, 2002. - С. 14-21.
35. Ильяхов М., Сарычева Л. Пиши, сокращай: Как создавать сильный текст. - Alpina, 2018.
36. Вешкин Ю.Г., Авагян Г.Л. Банковские системы зарубежных стран // М.: Экономистъ. - 2004.
37. Никитин А.А., Пеникас Г.И., Семенова М.В. Анализ предложений по корректировке капитала на изменение собственного кредитного риска // Банковское дело. - 2012. - №. 4. - С. 50-54.
38. Loan Against Property Interest Rates Comparison, Mortgage Loan Interest Rates in All Banks, 2018
Приложения
Приложение 1
Прототип application
create table application_vv2504 as
select
SKP_APPLICATION
ID_SOURCE
CODE_SOURCE_SYSTEM
SKP_PROC_INSERTED
SKP_PROC_UPDATED
FLAG_DELETED
DATE_EFFECTIVE
ID_CONTRACT
SKP_CLIENT
ID_CUID
SKP_CREDIT_CASE
DTIME_APPL_CREATION
DTIME_APPL_PROPOSAL
DATE_APPL_FIRST_BOD
DATE_APPL_SECOND_BOD
DTIME_APPL_DECISION
DTIME_OFFER_CALCULATED
DTIME_IR_PRESENTED_CALCULATION
DTIME_APPL_APPROVE
DATE_APPL_CANCEL
DATE_APPL_REJECTED
CODE_PRODUCT_TYPE
NAME_PRODUCT_TYPE
NAME_CREDIT_STATUS
SKP_SALESROOM
SKP_PRODUCT
CODE_PRODUCT
CODE_PRODUCT_VARIANT
CODE_GROUP_SEGMENT
NAME_CREDIT_REG_STATUS
CODE_PRICELIST
CODE_EMPLOYEE_SIGNED
CODE_TYPE_CANCELLATION
FLAG_FB_REJ
FLAG_FB_PREAPPCAN
FLAG_SAME_DAY_SIGNING
TEXT_CONTRACT_NUMBER
TEXT_DEAL_NUMBER
CODE_PRODUCT_PROFILE
FLAG_BID_PRODUCT
NAME_COMMODITY_TYPE
NAME_COMMODITY_CATEGORY
CODE_COMMODITY_UNIT
CODE_PRODUCER
NAME_PRODUCER
NAME_GOODS_LOAN
CODE_ECONOMICAL_STATUS
NAME_EMPLOYMENT_TYPE
NUM_CIBIL_SCORE
FLAG_APPL_FILLED_OFFLINE
FLAG_APPL_SIGNED_OFFLINE
NAME_SLSRM
AMT_CREDIT
AMT_CREDIT_TOTAL
AMT_COMMODITY_PRICE
AMT_DOWN_PAYMENT
AMT_ANNUITY
AMT_ANNUITY_WO_FEE
AMT_ANNUITY_FEE
AMT_ORIG_FEE
AMT_PAYMENT_CASH
AMT_INCOME_MAIN
CNT_TERM
CNT_PAYMENT_ADVANCED
RATE_INTEREST
RATE_INTEREST_PRESENTED_1
RATE_INTEREST_PRESENTED_2
RATE_INTEREST_PRESENTED_3
RATE_FLAT
CODE_REJECT_REASON
CODE_SCORING_RESULT
CODE_SCORING_TYPE
CODE_SUITE_TYPE
RANK_APPLICATION
case when fill_date is not null then row_number() over (partition by skp_client order by FILL_DATE asc) else 0 end as RANK_RELOGIN_VERSION
RANK_APPLICATION_EXCL
DATE_1
DATE_11
FILL_DATE
from
(
with ccase as
(
select skp_credit_case from owner_dwh. Ft_CREDIT_STATUS_TT
group by skp_credit_case having max (dtime_valid_from)>=date'2018-01-01'
)
fcst as
(
select /*+ materialize */ /*+ PARALLEL(4) */ * from owner_dwh. Ft_CREDIT_STATUS_TT t1 join
(
select
distinct FIRST_VALUE (t.skf_credit_status) over (partition by t.skp_credit_case, t.skp_credit_status order by t.DTIME_VALID_FROM desc nulls last) as skf_credit_status
from owner_dwh. Ft_CREDIT_STATUS_TT t
join owner_dwh.ft_application_base_tt t2 on t.skp_credit_case = t2.skp_credit_case
join ccase t3 on t.skp_credit_case = t3.skp_credit_case
where
- t.dtime_valid_from>=date'2018-01-01'
t2.skp_client!=-1
- and skp_credit_case ='171105291'
) t2
on t1.skf_credit_status = t2.skf_credit_status
)
app_date as (
select /*+ materialize */ /*+ PARALLEL(4) */
t.skp_credit_case
max (case when skp_credit_status in (1,11) then dtime_valid_from else null end) as appl_date
min (dtime_valid_from) as filled_date
max (case when skp_credit_status = 1 then dtime_valid_from else null end) as date_1 - approved
max (case when skp_credit_status = 2 then dtime_valid_from else null end) as date_2 - active
max (case when skp_credit_status = 3 then dtime_valid_from else null end) as date_3 - canceleld
max (case when skp_credit_status = 6 then dtime_valid_from else null end) as date_6 - in preprocess
max (case when skp_credit_status = 7 then dtime_valid_from else null end) as date_7 - process
max (case when skp_credit_status = 9 then dtime_valid_from else null end) as date_9 - signed
max (case when skp_credit_status = 11 then dtime_valid_from else null end) as date_11-rejected
max (case when skp_credit_status = 9 then skp_employee else null end) as skp_employee_signed
max (dtime_valid_from) as max_date
from fcst t
group by
skp_credit_case
)
app_dates as
(
select
t1.*
lead (t2.skp_credit_case) over (partition by t2.skp_client order by t1.appl_date desc) as skp_credit_case_last
case when t2.SKP_CREDIT_TYPE ='1' then lead (case when t2.skp_credit_type = '1' then t2.skp_credit_case else null end ignore nulls) over (partition by t2.skp_client order by t1.appl_date desc) end as skp_credit_case_last_t1
from app_date t1
join owner_dwh.ft_application_base_tt t2 on t1.skp_credit_case = t2.skp_credit_case and t1.appl_date is not null
)
select -+ parallel (8)
fappbt.skp_application as skp_application
'null' as id_source
'null' as code_source_system
'null' as skp_proc_inserted
'null' as skp_proc_updated
'null' as flag_deleted
'null' as date_effective
dapp.id_contract as id_contract
dc.skp_client as skp_client
dc.id_cuid as id_cuid
fappbt.skp_credit_case as skp_credit_case
faea.DTIME_APPL_CREATION as DTIME_APPL_CREATION
dapp.dtime_proposal as DTIME_APPL_PROPOSAL
ad.date_6 as DATE_APPL_FIRST_BOD
ad.date_7 as DATE_APPL_SECOND_BOD
fappbt.date_decision as DTIME_APPL_DECISION
fappbt.DTIME_OFFER_CALCULATED as DTIME_OFFER_CALCULATED
fappbt.DTIME_IR_PRESENTED_CALCULATION as DTIME_IR_PRESENTED_CALCULATION
ad.date_1 as DTIME_APPL_APPROVE
ad.date_3 as DATE_APPL_CANCEL
ad.date_11 as DATE_APPL_REJECTED
cct.CODE_PRODUCT_TYPE as CODE_PRODUCT_TYPE
fappbt.SKP_CREDIT_TYPE as NAME_PRODUCT_TYPE
ccs.NAME_CREDIT_STATUS as NAME_CREDIT_STATUS
fappbt.SKP_SALESROOM as SKP_SALESROOM
fappbt.SKP_PRODUCT as SKP_PRODUCT
dp.CODE_PRODUCT as CODE_PRODUCT
dpv.CODE_PRODUCT_VARIANT as CODE_PRODUCT_VARIANT
dcs.CODE_GROUP_SEGMENT as CODE_GROUP_SEGMENT
-, ccr.CODE_CLIENT_ROLE as CODE_CLIENT_ROLE
crs.NAME_REGISTRATION_STATUS as NAME_CREDIT_REG_STATUS
dpr.CODE_PRICELIST as CODE_PRICELIST
-, de.NAME_COMMON as CODE_EMPLOYEE_SIGNED - check
emp_signed.NAME_COMMON as CODE_EMPLOYEE_SIGNED
/*, case when ad.date_3 < ad.date_1 then 'PreAC'
when ad.date_3 < ad.date_9 then 'PostAC'
when ad.date_3 < ad.date_2 then 'PostSC'
when ad.date_3 > ad.date_2 then 'PostActC'
when ad.date_3 is not null then 'Cancelled'
else 'Not_Cancelled' end as CODE_TYPE_CANCELLATION */
case when ad.date_3 is not null
then (
case when ad.date_3=max_date then (
case when ad.date_3 >ad.date_2 then 'PostActC'
when ad.date_3 >ad.date_9 and ad.date_3>ad.date_1 then 'PostSC'
when ad.date_3 > ad.date_1 then 'PostAC'
else 'PreAC' end)
else 'Cancelled'
end
)
else 'Not_Cancelled'
end as CODE_TYPE_CANCELLATION
case when trunc (ad.date_11)=trunc (ad.date_6) then 'Y'
when trunc (ad.date_11)!=trunc (ad.date_6) then 'N'
else 'X' end as FLAG_FB_REJ
case when ad.date_11 is null and ad.date_7 is null and ad.date_6 is not null and ad.date_3 is not null then 'Y' else 'XNA' end as FLAG_FB_PREAPPCAN - redo
case when trunc (ad.date_1)=trunc (ad.date_9) then 'Y'
when trunc (ad.date_1)!=trunc (ad.date_9) then 'N'
else'X' end as FLAG_SAME_DAY_SIGNING
dccb.TEXT_CONTRACT_NUMBER as TEXT_CONTRACT_NUMBER
dccb.TEXT_DEAL_NUMBER as TEXT_DEAL_NUMBER
favca.CODE_PRODUCT_PROFILE as CODE_PRODUCT_PROFILE
favca.FLAG_BID_PRODUCT as FLAG_BID_PRODUCT
fappbt.NAME_GOODS_TYPE as NAME_COMMODITY_TYPE
fappbt.NAME_GOODS_CATEGORY as NAME_COMMODITY_CATEGORY
decode (lower(fappbt.NAME_GOODS_CATEGORY), 'two-wheelers', 'TW'
'xna', 'CL'
'CD') as CODE_COMMODITY_UNIT
fappbt.CODE_PRODUCER as CODE_PRODUCER
fappbt.NAME_PRODUCER as NAME_PRODUCER
-, fappbt.skp_credit_type
-, coalesce (fcst1.DTIME_VALID_FROM, fcst11.DTIME_VALID_FROM) as kek
-, greatest (coalesce(fcst1.DTIME_VALID_FROM, fcst11.DTIME_VALID_FROM), coalesce (fcst11.DTIME_VALID_FROM, fcst1.DTIME_VALID_FROM)) as kek2
-, fcst9.DTIME_VALID_FROM
-, lag (CASE WHEN fappbt.skp_credit_type = '1' THEN fappbt.NAME_GOODS_TYPE END IGNORE NULLS) over (partition by fappbt.skp_client order by fcst.skp_credit_status_9 desc) as NAME_GOODS_LOAN
case when fapll1.skp_credit_case is not null then concat (concat(fapll1.NAME_GOODS_TYPE, ' '), fapll1.NAME_PRODUCER) else null end as NAME_GOODS_LOAN - return
-, lag (case when fcst9.DTIME_VALID_FROM is not null then concat (concat(fappbt.NAME_GOODS_TYPE, ' '), fappbt.NAME_PRODUCER)) over (partition by fappbt.skp_client, fappbt.skp_credit_type order by fcst9.DTIME_VALID_FROM desc) as NAME_GOODS_LOAN2
favia.CODE_INCOME_TYPE as CODE_ECONOMICAL_STATUS
case when favia.CODE_INCOME_TYPE in ('SELF-EMP_PUBLIC_LTD', 'SELF-EMP_PVT_LTD', 'SELFEMPLOYED', 'SELF-EMP_PARTNER', 'SELF-EMP_PROPR') then 'Self Employed'
when favia.CODE_INCOME_TYPE in ('STUDENT', 'RETIRED_NO_PENSION', 'UNEMPLOYED', 'PERSON_IN_HOUSEHOLD') then 'Unemployed'
when favia.code_income_type in ('SALARIED_PUBLIC_LTD', 'SALARIED_NGO_TRUST_COOP_PS', 'RETIRED_PENSIONER', 'SALARIED_GOVT', 'SALARIED_PROPRIETORSHIP', 'SALARIED_PVT_LTD', 'SALARIED_OTHER') then 'Salaried'
else 'NA' end as NAME_EMPLOYMENT_TYPE
favca.NUM_CIBIL_SCORE as NUM_CIBIL_SCORE
faea.FLAG_APPL_FILLED_OFFLINE as FLAG_APPL_FILLED_OFFLINE
fappbt.FLAG_APPL_SIGNED_OFFLINE as FLAG_APPL_SIGNED_OFFLINE
dsll.name_salesroom as NAME_SLSRM - return
fappbt.amt_credit as AMT_CREDIT
fappbt.amt_credit_total as AMT_CREDIT_TOTAL
fappbt.amt_goods_price as AMT_COMMODITY_PRICE
fappbt.amt_down_payment as AMT_DOWN_PAYMENT
fappbt.amt_annuity as AMT_ANNUITY
fappbt.amt_annuity_wo_fee as AMT_ANNUITY_WO_FEE
fappbt.amt_annuity_fee as AMT_ANNUITY_FEE
fappbt.amt_fee_origination as AMT_ORIG_FEE
fappbt.amt_payment_cash as AMT_PAYMENT_CASH
fact.AMT_INCOME_MAIN as AMT_INCOME_MAIN
fappbt.CNT_INSTALMENT as CNT_TERM
fappbt.CNT_PAYMENT_ADVANCED as CNT_PAYMENT_ADVANCED
fappbt.RATE_INTEREST as RATE_INTEREST
fappbt.RATE_INTEREST_PRESENTED_1 as RATE_INTEREST_PRESENTED_1
fappbt.RATE_INTEREST_PRESENTED_2 as RATE_INTEREST_PRESENTED_2
fappbt.RATE_INTEREST_PRESENTED_3 as RATE_INTEREST_PRESENTED_3
fappbt.RATE_INTEREST_PRESENTED_1*100 as RATE_FLAT
fappbt.CODE_REJECT_REASON as CODE_REJECT_REASON
fappbt.CODE_SCORING_RESULT as CODE_SCORING_RESULT
favca.CODE_SCORING_TYPE as CODE_SCORING_TYPE
favca.CODE_SUITE_TYPE as CODE_SUITE_TYPE
row_number() over (partition by fappbt.skp_client order by ad.appl_date desc) as RANK_APPLICATION
-, row_number() over (partition by fappbt.skp_client order by ad.filled_date asc) as RANK_RELOGIN_VERSION
case when ds.CODE_SALESROOM!='01126079I'
then
row_number() over (partition by fappbt.skp_client, ds.CODE_SALESROOM order by ad.appl_date asc)
else 0 end as RANK_APPLICATION_EXCL
ad.date_1 as date_1
ad.date_11 as date_11
ad.filled_date as fill_date
from owner_dwh. Ft_APPLICATION_BASE_TT fappbt
join app_dates ad on ad.skp_credit_case = fappbt.skp_credit_case
left join owner_dwh.dct_employee emp_signed on emp_signed.skp_employee = ad.skp_employee_signed
left join owner_dwh. Ft_APPLICATION_BASE_TT fapll on fapll.skp_credit_case = ad.skp_credit_case_last - описание предыдущего заявления
left join owner_dwh. Ft_APPLICATION_BASE_TT fapll1 on fapll1.skp_credit_case = ad.skp_credit_case_last_t1
left join owner_dwh.dct_salesroom dsll on dsll.SKP_SALESROOM = fapll.SKP_SALESROOM - пред апп
left join owner_dwh.dct_application dapp
on fappbt.skp_application = dapp.skp_application
left join owner_dwh.DCt_CLIENT dc
on fappbt.skp_client = dc.skp_client
left join owner_dwh.F_APPLICATION_EVENT_AT faea
on faea.skp_application=fappbt.skp_application
left join owner_dwh.CLt_CREDIT_TYPE cct
on fappbt.skp_credit_type=cct.skp_credit_type
left join owner_dwh.CLt_CREDIT_STATUS ccs -
on ccs.skp_credit_status=fappbt.skp_credit_status
left join owner_dwh.DCt_PRODUCT dp
on dp.SKP_PRODUCT = fappbt.SKP_PRODUCT
left join owner_dwh.DCt_PRODUCT_VARIANT dpv
on dpv.skp_product_variant=fappbt.skp_product_variant
left join owner_dwh.DCt_CREDIT_SEGMENTATION dcs
on fappbt.SKP_CREDIT_SEGMENTATION =dcs.SKP_CREDIT_SEGMENTATION
left join owner_dwh.DCT_CREDIT_CASE dccb
on dccb.skp_application = fappbt.skp_application
left join owner_dwh.clt_registration_status crs
on crs.SKP_REGISTRATION_STATUS = dccb.SKP_REGISTRATION_STATUS
left join owner_dwh.DCt_PRICELIST dpr
on dpr.skp_pricelist = fappbt.skp_pricelist
- добавить
left join owner_dwh. Ft_APPLICATION_VCT_CREDIT_AT favca
on favca.skp_application=fappbt.skp_application
left join owner_dwh. Ft_APPLICATION_VCT_INFO_AT favia
on favia.SKP_APPLICATION = fappbt.SKP_APPLICATION
left join owner_dwh. Ft_APPLICATION_CLIENT_TT fact
on fact.skp_client_role='11' and fact.skp_application=fappbt.skp_application
left join owner_dwh.dct_salesroom ds
on ds.SKP_SALESROOM = fappbt.SKP_SALESROOM
where fappbt.skp_client!=-1
union all
select -+ parallel (8)
fappbt.skp_application as skp_application
'null' as id_source
'null' as code_source_system
'null' as skp_proc_inserted
'null' as skp_proc_updated
'null' as flag_deleted
'null' as date_effective
dapp.id_contract as id_contract
dc.skp_client as skp_client
dc.id_cuid as id_cuid
fappbt.skp_credit_case as skp_credit_case
faea.DTIME_APPL_CREATION as DTIME_APPL_CREATION
dapp.dtime_proposal as DTIME_APPL_PROPOSAL
ad.date_6 as DATE_APPL_FIRST_BOD
ad.date_7 as DATE_APPL_SECOND_BOD
fappbt.date_decision as DTIME_APPL_DECISION
fappbt.DTIME_OFFER_CALCULATED as DTIME_OFFER_CALCULATED
fappbt.DTIME_IR_PRESENTED_CALCULATION as DTIME_IR_PRESENTED_CALCULATION
null as DTIME_APPL_APPROVE
ad.date_3 as DATE_APPL_CANCEL
null as DATE_APPL_REJECTED
cct.CODE_PRODUCT_TYPE as CODE_PRODUCT_TYPE
fappbt.SKP_CREDIT_TYPE as NAME_PRODUCT_TYPE
ccs.NAME_CREDIT_STATUS as NAME_CREDIT_STATUS
fappbt.SKP_SALESROOM as SKP_SALESROOM
fappbt.SKP_PRODUCT as SKP_PRODUCT
dp.CODE_PRODUCT as CODE_PRODUCT
dpv.CODE_PRODUCT_VARIANT as CODE_PRODUCT_VARIANT
dcs.CODE_GROUP_SEGMENT as CODE_GROUP_SEGMENT
-, ccr.CODE_CLIENT_ROLE as CODE_CLIENT_ROLE
crs.NAME_REGISTRATION_STATUS as NAME_CREDIT_REG_STATUS
dpr.CODE_PRICELIST as CODE_PRICELIST
-, de.NAME_COMMON as CODE_EMPLOYEE_SIGNED - check
emp_signed.NAME_COMMON as CODE_EMPLOYEE_SIGNED
case when ad.date_3 is not null
then (
case when ad.date_3=max_date then (
case when ad.date_3 >ad.date_2 then 'PostActC'
when ad.date_3 >ad.date_9 and ad.date_3>ad.date_1 then 'PostSC'
when ad.date_3 > ad.date_1 then 'PostAC'
else 'PreAC' end)
else 'Cancelled'
end
)
else 'Not_Cancelled'
end as CODE_TYPE_CANCELLATION
'X' as FLAG_FB_REJ
case when ad.date_7 is null and ad.date_6 is not null and ad.date_3 is not null then 'Y' else 'XNA' end as FLAG_FB_PREAPPCAN - redo
'X' as FLAG_SAME_DAY_SIGNING
dccb.TEXT_CONTRACT_NUMBER as TEXT_CONTRACT_NUMBER
dccb.TEXT_DEAL_NUMBER as TEXT_DEAL_NUMBER
favca.CODE_PRODUCT_PROFILE as CODE_PRODUCT_PROFILE
favca.FLAG_BID_PRODUCT as FLAG_BID_PRODUCT
fappbt.NAME_GOODS_TYPE as NAME_COMMODITY_TYPE
fappbt.NAME_GOODS_CATEGORY as NAME_COMMODITY_CATEGORY
decode (lower(fappbt.NAME_GOODS_CATEGORY), 'two-wheelers', 'TW'
'xna', 'CL'
'CD') as CODE_COMMODITY_UNIT
fappbt.CODE_PRODUCER as CODE_PRODUCER
fappbt.NAME_PRODUCER as NAME_PRODUCER
-, fappbt.skp_credit_type
-, coalesce (fcst1.DTIME_VALID_FROM, fcst11.DTIME_VALID_FROM) as kek
-, greatest (coalesce(fcst1.DTIME_VALID_FROM, fcst11.DTIME_VALID_FROM), coalesce (fcst11.DTIME_VALID_FROM, fcst1.DTIME_VALID_FROM)) as kek2
-, fcst9.DTIME_VALID_FROM
-, lag (CASE WHEN fappbt.skp_credit_type = '1' THEN fappbt.NAME_GOODS_TYPE END IGNORE NULLS) over (partition by fappbt.skp_client order by fcst.skp_credit_status_9 desc) as NAME_GOODS_LOAN
null as NAME_GOODS_LOAN - lead (case when ad.appl_date is not null then concat (concat(fappbt.NAME_GOODS_TYPE, ' '), fappbt.NAME_PRODUCER) else null end)
- over (partition by fappbt.skp_client, fappbt.skp_credit_type order by ad.appl_date desc nulls last) as NAME_GOODS_LOAN - return
-, lag (case when fcst9.DTIME_VALID_FROM is not null then concat (concat(fappbt.NAME_GOODS_TYPE, ' '), fappbt.NAME_PRODUCER)) over (partition by fappbt.skp_client, fappbt.skp_credit_type order by fcst9.DTIME_VALID_FROM desc) as NAME_GOODS_LOAN2
favia.CODE_INCOME_TYPE as CODE_ECONOMICAL_STATUS
case when favia.CODE_INCOME_TYPE in ('SELF-EMP_PUBLIC_LTD', 'SELF-EMP_PVT_LTD', 'SELFEMPLOYED', 'SELF-EMP_PARTNER', 'SELF-EMP_PROPR') then 'Self Employed'
when favia.CODE_INCOME_TYPE in ('STUDENT', 'RETIRED_NO_PENSION', 'UNEMPLOYED', 'PERSON_IN_HOUSEHOLD') then 'Unemployed'
when favia.code_income_type in ('SALARIED_PUBLIC_LTD', 'SALARIED_NGO_TRUST_COOP_PS', 'RETIRED_PENSIONER', 'SALARIED_GOVT', 'SALARIED_PROPRIETORSHIP', 'SALARIED_PVT_LTD', 'SALARIED_OTHER') then 'Salaried'
else 'NA' end as NAME_EMPLOYMENT_TYPE
favca.NUM_CIBIL_SCORE as NUM_CIBIL_SCORE
faea.FLAG_APPL_FILLED_OFFLINE as FLAG_APPL_FILLED_OFFLINE
fappbt.FLAG_APPL_SIGNED_OFFLINE as FLAG_APPL_SIGNED_OFFLINE
null as NAME_SLSRM-lead (case when ad.appl_date is not null then ds.name_salesroom else null end)
- over (partition by fappbt.skp_client order by ad.appl_date desc nulls last) as NAME_SLSRM - return
fappbt.amt_credit as AMT_CREDIT
fappbt.amt_credit_total as AMT_CREDIT_TOTAL
fappbt.amt_goods_price as AMT_COMMODITY_PRICE
fappbt.amt_down_payment as AMT_DOWN_PAYMENT
fappbt.amt_annuity as AMT_ANNUITY
fappbt.amt_annuity_wo_fee as AMT_ANNUITY_WO_FEE
fappbt.amt_annuity_fee as AMT_ANNUITY_FEE
fappbt.amt_fee_origination as AMT_ORIG_FEE
fappbt.amt_payment_cash as AMT_PAYMENT_CASH
fact.AMT_INCOME_MAIN as AMT_INCOME_MAIN
fappbt.CNT_INSTALMENT as CNT_TERM
fappbt.CNT_PAYMENT_ADVANCED as CNT_PAYMENT_ADVANCED
fappbt.RATE_INTEREST as RATE_INTEREST
fappbt.RATE_INTEREST_PRESENTED_1 as RATE_INTEREST_PRESENTED_1
fappbt.RATE_INTEREST_PRESENTED_2 as RATE_INTEREST_PRESENTED_2
fappbt.RATE_INTEREST_PRESENTED_3 as RATE_INTEREST_PRESENTED_3
fappbt.RATE_INTEREST_PRESENTED_1*100 as RATE_FLAT
fappbt.CODE_REJECT_REASON as CODE_REJECT_REASON
fappbt.CODE_SCORING_RESULT as CODE_SCORING_RESULT
favca.CODE_SCORING_TYPE as CODE_SCORING_TYPE
favca.CODE_SUITE_TYPE as CODE_SUITE_TYPE
0 as RANK_APPLICATION - case when ad.appl_date is not null then row_number() over (partition by fappbt.skp_client order by ad.appl_date desc nulls last)
- else 0 end as RANK_APPLICATION
-, row_number() over (partition by fappbt.skp_client order by ad.filled_date asc nulls last) as RANK_RELOGIN_VERSION
0 as RANK_APPLICATION_EXCL - case when ds.CODE_SALESROOM ='01126079I' and ad.appl_date is not null
null as date_1
null as date_11
ad.filled_date as fill_date
from owner_dwh. Ft_APPLICATION_BASE_TT fappbt
join app_date ad on ad.skp_credit_case = fappbt.skp_credit_case
left join owner_dwh.dct_employee emp_signed on emp_signed.skp_employee=ad.skp_employee_signed
- left join owner_dwh.F_APPLICATION_BASE_TT fapll on fapll.skp_credit_case = ad.skp_credit_case_last - описание предыдущего заявления
- left join owner_dwh.dc_salesroom dsll on dsll.SKP_SALESROOM = fapll.SKP_SALESROOM - пред апп
left join owner_dwh.dct_application dapp
on fappbt.skp_application = dapp.skp_application
left join owner_dwh.DCt_CLIENT dc
on fappbt.skp_client = dc.skp_client
left join owner_dwh.F_APPLICATION_EVENT_AT faea
on faea.skp_application=fappbt.skp_application
left join owner_dwh.CLt_CREDIT_TYPE cct
on fappbt.skp_credit_type=cct.skp_credit_type
left join owner_dwh.CLt_CREDIT_STATUS ccs -
on ccs.skp_credit_status=fappbt.skp_credit_status
left join owner_dwh.DCt_PRODUCT dp
on dp.SKP_PRODUCT = fappbt.SKP_PRODUCT
left join owner_dwh.DCt_PRODUCT_VARIANT dpv
on dpv.skp_product_variant=fappbt.skp_product_variant
left join owner_dwh.DCt_CREDIT_SEGMENTATION dcs
on fappbt.SKP_CREDIT_SEGMENTATION =dcs.SKP_CREDIT_SEGMENTATION
left join owner_dwh.DCT_CREDIT_CASE dccb
on dccb.skp_application = fappbt.skp_application
left join owner_dwh.clt_registration_status crs
on crs.SKP_REGISTRATION_STATUS = dccb.SKP_REGISTRATION_STATUS
left join owner_dwh.DCt_PRICELIST dpr
on dpr.skp_pricelist = fappbt.skp_pricelist
- добавить
left join owner_dwh. Ft_APPLICATION_VCT_CREDIT_AT favca
on favca.skp_application=fappbt.skp_application
left join owner_dwh. Ft_APPLICATION_VCT_INFO_AT favia
on favia.SKP_APPLICATION = fappbt.SKP_APPLICATION
left join owner_dwh. Ft_APPLICATION_CLIENT_TT fact
on fact.skp_client_role='11' and fact.skp_application=fappbt.skp_application
left join owner_dwh.dct_salesroom ds
on ds.SKP_SALESROOM = fappbt.SKP_SALESROOM
where ad.appl_date is null and fappbt.skp_client!=-1
);
Приложение 2
Прототип contract
CREATE TABLE CONTRACT_vv3004 as select * from (
with ccase as
(
select t1.skp_credit_case
from owner_dwh. Ft_CREDIT_STATUS_TT t1
group by t1.skp_credit_case having max (t1.dtime_valid_from)>=date'2018-01-01'
)
fcst as
(
select /*+ materialize */ /*+ PARALLEL(4) */ t1.* from owner_dwh. Ft_CREDIT_STATUS_TT t1 join
(
select distinct FIRST_VALUE (t1.skf_credit_status) over (partition by t1.skp_credit_case, t1.skp_credit_status order by t1.DTIME_VALID_FROM desc nulls last) as skf_credit_status
from owner_dwh. Ft_CREDIT_STATUS_TT t1 join ccase t2 on t1.skp_credit_case = t2.skp_credit_case
- where dtime_valid_from>=date'2018-01-01'
- skp_credit_case in ('131314100', '193668533')
) t2
on t1.skf_credit_status = t2.skf_credit_status
)
app_date as
(
select /*+ materialize */ /*+ PARALLEL(4) */
t2.skp_client
t.skp_credit_case
max (case when t.skp_credit_status in (1,11) then dtime_valid_from else null end) as appl_date
min (dtime_valid_from) as filled_date
max (case when t.skp_credit_status = 1 then dtime_valid_from else null end) as date_1 - approved
max (case when t.skp_credit_status = 2 then dtime_valid_from else null end) as date_2 - active
max (case when t.skp_credit_status = 3 then dtime_valid_from else null end) as date_3 - canceleld
max (case when t.skp_credit_status = 5 then dtime_valid_from else null end) as date_5 - returned
max (case when t.skp_credit_status = 6 then dtime_valid_from else null end) as date_6 - preprocess
max (case when t.skp_credit_status = 7 then dtime_valid_from else null end) as date_7-process
max (case when t.skp_credit_status = 8 then dtime_valid_from else null end) as date_8-Paid-Off
max (case when t.skp_credit_status = 9 then dtime_valid_from else null end) as date_9 - signed
max (case when t.skp_credit_status = 10 then dtime_valid_from else null end) as date_10 - written-off
max (case when t.skp_credit_status = 11 then dtime_valid_from else null end) as date_11 - rejected
max (case when t.skp_credit_status = 26131 then dtime_valid_from else null end) as date_26131 - sold
max (case when t.skp_credit_status = 1 then skf_credit_status else null end) as skf_1
max (case when t.skp_credit_status = 2 then skf_credit_status else null end) as skf_2
max (case when t.skp_credit_status = 3 then skf_credit_status else null end) as skf_3
max (case when t.skp_credit_status = 6 then skf_credit_status else null end) as skf_6
max (case when t.skp_credit_status = 7 then skf_credit_status else null end) as skf_7
max (case when t.skp_credit_status = 8 then skf_credit_status else null end) as skf_8
max (case when t.skp_credit_status = 9 then skf_credit_status else null end) as skf_9
max (case when t.skp_credit_status = 11 then skf_credit_status else null end) as skf_11
max (dtime_valid_from) as dtime_valid_from
from fcst t join owner_dwh.f_application_base_tt t2 on t.skp_credit_case = t2.skp_credit_case
group by
t.skp_credit_case
t2.skp_client
)
ll_prepre1 as
(
select
distinct
t1.skp_credit_case
-, t2.skp_credit_case as b
-, fabt.skp_credit_case as c
-, first_value (t2.skp_credit_case) over (partition by t1.skp_client, t1.skp_credit_case order by t2.appl_date desc) as last_app - очень сложный алгоритм подсчета предыдущего заявления для текущего кредита
-, lead (t1.skp_credit_case) over (partition by t1.skp_client order by t1.date_9 desc nulls last) as last_loan
-, lead (t1.skp_credit_case) over (partition by t1.skp_client order by t1.date_2 desc nulls last) as last_loan_t2 - type 2 last activated
-, first_value (t1.skp_credit_case) over (partition by t1.skp_client order by t1.date_9 asc nulls last) as first_loan
case when t3.skp_credit_type = 1 then first_value (case when t4.skp_credit_type =1 and t4.skp_credit_status= 2 then t2.skp_credit_case else null end ignore nulls) over (partition by t1.skp_client, t1.skp_credit_case order by t2.appl_date desc) - очень сложный алгоритм подсчета предыдущего заявления для текущего кредита
end as last_loan_t1 - last active loan application
-, row_number() over (partition by t1.skp_client order by t1.date_9 desc) as RANK_LOAN
from app_date t1
join app_date t2 on t1.skp_client = t2.skp_client and t1.date_9>t2.appl_date
left join owner_dwh.ft_application_base_tt t3 on t3.skp_credit_case = t1.skp_credit_case
left join owner_dwh.ft_application_base_tt t4 on t2.skp_credit_case = t4.skp_credit_case
- left join owner_dwh.FT_APPLICATION_BASE_TT fabt
- on t1.skp_credit_case = fabt.skp_credit_case and fabt.skp_credit_type =1
)
ll_prepre as
(
select
distinct
t1.skp_credit_case
-, t1.last_app
t2.last_loan_t1 - last loan appliction
lead (t1.skp_credit_case) over (partition by t1.skp_client order by t1.date_9 desc nulls last) as last_loan
lead (t1.skp_credit_case) over (partition by t1.skp_client order by t1.date_2 desc nulls last) as last_loan_t2 - type 2 last activated contract
first_value (t1.skp_credit_case) over (partition by t1.skp_client order by t1.date_9 asc nulls last) as first_loan
-, lead (case when t1.skp_credit_case is not null then t1.skp_credit_case else null end IGNORE NULLS) over (partition by t1.skp_client order by t1.date_9 desc nulls last) as last_loan_t1 - with skp_credut_type=1
row_number() over (partition by t1.skp_client order by t1.date_9 desc) as RANK_LOAN
from
app_date t1
- left join owner_dwh.FT_APPLICATION_BASE_TT fabt
- on t1.skp_credit_case = fabt.skp_credit_case and fabt.skp_credit_type =1
left join ll_prepre1 t2
on t1.skp_credit_case = t2.skp_credit_case
)
select
dc.SKP_CONTRACT as SKP_CONTRACT -+
dc.SKP_CREDIT_CASE as SKP_CREDIT_CASE-+
dc.SKP_CLIENT as SKP_CLIENT -+
dsh.NAME_SALES_DISTRICT as NAME_SALES_DISTRICT -+
dc.text_contract_number as TEXT_CONTRACT_NUMBER-+
dc.TEXT_LOAN_PURPOSE as TEXT_LOAN_PURPOSE-+
dc.CODE_CREDIT_STATUS as CODE_CREDIT_STATUS-+
ccs.NAME_CREDIT_STATUS as NAME_CREDIT_STATUS-+
ccsr.NAME_CREDIT_STATUS_REASON as NAME_STATUS_REASON -+
,
Case
when dc.skp_credit_status = 3 and ad.skf_9 is not null and fopt.DTIME_DISBURSEMENT is not null then 'Cancelled_post_Disbursement'
when dc.skp_credit_status = 3 and ad.skf_9 is not null and fopt.DTIME_DISBURSEMENT is null then 'Cancelled_post_Signing'
when dc.skp_credit_status = 3 and ad.skf_9 is null and ad.skf_1 is not null then 'Cancelled_Post_Approval'
when dc.skp_credit_status = 3 and ad.skf_1 is null then 'Cancelled_pre_Approval'
when dc.skp_credit_status = 11 then 'Reject'
when dc.skp_credit_status = 8 Then 'Paid-Off'
else decode (dc.skp_credit_status
1,'Approved'
2,'Active'
3,'Cancelled'
4,'Finished'
5,'Returned'
6,'In Preprocess'
7,'In Process'
8,'Paid off'
9,'Signed'
10,'Written off'
11,'Rejected'
26131,'Sold'
-2,'XAP'
-1,'XNA'
null) end as TEXT_STATUS_INFO -+
ccsr3. Name_Credit_Status_reason as NAME_CANCELLATION_REASON -+
crs.name_registration_status as NAME_REGISTRATION_STATUS -+
dp.name_product as NAME_PRODUCT-+
cpct.NAME_PAYMENT_CHANNEL_TYPE as NAME_PAYMENT_CHANNEL_TYPE -+
ds.code_salesroom as CODE_SALESROOM-+
'null' as TEXT_DOCS_LOAN-+
-, fcht.CODE_PRODUCT_TYPE as CODE_PRODUCT_TYPE
cct.CODE_PRODUCT_TYPE as CODE_PRODUCT_TYPE -+
dp_f.CODE_PRODUCT as CODE_PRODUCT_GROUP -+
'null' as FLAG_COVER-+
'null' as FLAG_PARTNER_SIGNED -+
fabt.NAME_GOODS_TYPE as NAME_GOODS_TYPE-+
fabt.NAME_GOODS_CATEGORY as NAME_GOODS_CATEGORY-+
case when mxw.code_product like ' % UPXL % L % ' or mxw.code_product like ' % CONS % L % ' or mxw.code_product like ' % CLX % L % ' then 'Y' else 'N' end as FLAG_SAFEPAY-+
dc.SKP_PRODUCT as SKP_PRODUCT-+
'null' as FLAG_JOINT_LENDING-+
fcba.AMT_CREDIT as AMT_CREDIT-+
fabt.AMT_CREDIT_TOTAL as AMT_CREDIT_TOTAL-+
fabt.AMT_CREDIT as AMT_PAYMENT-+
-, fcba_last.AMT_OUTSTANDING_TOTAL as AMT_DEBT
fcba_ll.AMT_CREDIT_START as AMT_LOAN-+
fcaba.AMT_OUTSTANDING_TOTAL as AMT_DEBT-+
-, fiha_ll.amt_instalment as AMT_INIT_PAYMENT-tbd
-, fcba_ll.amt_instalment as AMT_CUR_PAYMENT-tbd
ad.dtime_valid_from as DATE_CHNG_STATUS_LAST-+
fopt.DTIME_PAYMENT_INSERTED as DTIME_PAYMENT_INSERTED_LAST-+
fopt.DTIME_DISBURSEMENT as DTIME_DISBURSEMENT-+
ad.date_9 as DATE_CONTRACT_SIGN-+
ad.date_2 as DATE_CONTRACT_ACTIVE-+
ad.date_5 as DATE_CONTRACT_RETURN-+
ad.date_10 as DATE_CONTRACT_WRITEOFF-+
ad.date_8 as DATE_CONTRACT_PAYOFF-+
fiha.DATE_INSTALMENT as DATE_DUE_NEXT-+
fiha_la.DATE_INSTALMENT as DATE_CONTRACT_END
-, greatest (ad_ll.date_8, ad_ll.date_10, ad_ll.date_26131) as DATE_CONTRACT_END_FACT
fcba_ll.DTIME_CLOSE as DATE_CONTRACT_END_FACT
cnt.cnt as CNT_PAYMENTS
hol.hol_count as CNT_PAYM_HOLIDAY
fcba.RATE_EIR as RATE_EIR
lp.RANK_LOAN as NUM_CONTRACT_RANK_DESC
from owner_dwh.DC_CONTRACT dc
join ll_prepre lp
on lp.skp_credit_case = dc.skp_credit_case
left join owner_dwh.FT_CONTRACT_BASE_AD fcba_last
on fcba_last.skp_credit_case = lp.last_loan
left join owner_dwh.ft_application_base_tt fabt_last
on lp.last_loan_t1 = fabt_last.skp_credit_case
left join OWNER_DWH. Ft_CONTRACT_BASE_AD fcba_ll_t1
on fcba_ll_t1.skp_credit_case = lp.last_loan_t1
left join owner_dwh.ft_application_base_tt fabt
on dc.skp_credit_case = fabt.skp_credit_case
left join app_date ad
on ad.skp_credit_case = dc.skp_credit_case
left join owner_dwh.dct_salesroom ds_ll
on ds_ll.skp_salesroom = fabt_last.skp_salesroom
left join owner_dwh.DCT_SALES_HIERARCHY dsh
on dsh.skp_sales_HIERARCHY =ds_ll.skp_sales_HIERARCHY
left join owner_dwh.clt_credit_status ccs
on ccs.skp_credit_status = dc.skp_credit_status
left join fcst
on fcst.skp_credit_status= dc.skp_credit_status and fcst.skp_credit_case = dc.skp_credit_case
left join owner_dwh.CLT_CREDIT_STATUS_REASON ccsr
on ccsr.SKP_CREDIT_STATUS_REASON = fcst.SKP_CREDIT_STATUS_REASON
left join owner_dwh.ft_credit_status_tt fcst3
on fcst3.skf_credit_status = ad.skf_3
left join owner_dwh.CLt_Credit_Status_Reason ccsr3
on ccsr3.skp_credit_status_reason=fcst3.skp_credit_status_reason
left join AP_CRM.MSDWH_XSELL_WR mxw
on mxw.skp_credit_case = dc.skp_credit_case
left join (
select skp_credit_case, case when DTIME_DISBURSEMENT =date'3000-01-01' then null else DTIME_DISBURSEMENT end as DTIME_DISBURSEMENT, DTIME_PAYMENT_INSERTED
from (
select skp_credit_case, max (DTIME_PAYMENT_INSERTED) as DTIME_PAYMENT_INSERTED, max (DTIME_PAYMENT) as DTIME_DISBURSEMENT
from owner_dwh.FT_Outgoing_Payment_TT fopt
where skp_outgoing_payment_type=5
group by skp_credit_case)
fopt) fopt
on fopt.skp_credit_case = dc.skp_credit_case
left join owner_dwh.CLT_REGISTRATION_STATUS crs
on crs.skP_registration_status =dc.skp_registration_status
left join owner_Dwh.dct_product dp
on dp.skp_product=dc.skp_product
join owner_dwh. Ft_CONTRACT_BASE_AD fcba
on fcba.skp_contract =dc.skp_contract
left join owner_dwh.CLT_PAYMENT_CHANNEL_TYPE cpct
on cpct.SKP_PAYMENT_CHANNEL_TYPE = fcba.SKP_PAYMENT_CHANNEL_TYPE
left join owner_dwh.dct_salesroom ds
on ds.skp_salesroom = dc.skp_salesroom
- left join owner_dwh.FT_CONTRACT_HISTORY_TT fcht
- on fcht.skp_credit_case = dc.SKP_CREDIT_CASE and fcht.flag_current='Y'
left join owner_Dwh.CLT_CREDIT_TYPE cct
on dc.skp_credit_type=cct.skp_credit_type
left join owner_dwh.dct_contract dc_f
on dc_f.skp_credit_case = lp.first_loan
left join owner_dwh.dct_product dp_f
on dp_f.skp_product = dc_f.skp_product
/*left join
(
select skp_credit_case, amt_instalment
from owner_dwh.FT_INSTALMENT_HEAD_AD
where num_version =1 and num_instalment_number=1
) fiha_ll
on lp.last_loan = fiha_ll.skp_credit_case*/
/*left join
(
select
distinct
skp_credit_case
first_value (amt_instalment) over (partition by skp_credit_case order by num_version desc, num_instalment_number desc) as amt_instalment
from owner_dwh.FT_INSTALMENT_HEAD_AD
- group by skp_credit_case, first_value (amt_instalment) over (partition by skp_credit_case order by num_version desc, num_instalment_number desc)
) fiha_ll2
on lp.last_loan = fiha_ll2.skp_credit_case*/
left join
(
select
distinct
skp_credit_case
first_value (DATE_INSTALMENT) over (partition by skp_credit_case order by num_version desc, num_instalment_number asc) as DATE_INSTALMENT
from owner_dwh.FT_INSTALMENT_HEAD_AD
where DATE_INSTALMENT>=trunc(sysdate)
- group by skp_credit_case, first_value (amt_instalment) over (partition by skp_credit_case order by num_version desc, num_instalment_number desc)
) fiha
on dc.skp_credit_case = fiha.skp_credit_case
left join
(
select
distinct
skp_credit_case
first_value (DATE_INSTALMENT) over (partition by skp_credit_case order by num_version desc, num_instalment_number desc) as DATE_INSTALMENT
from owner_dwh.FT_INSTALMENT_HEAD_AD
- group by skp_credit_case, first_value (amt_instalment) over (partition by skp_credit_case order by num_version desc, num_instalment_number desc)
) fiha_la
on lp.last_loan_t2 = fiha_la.skp_credit_case
left join app_date ad_ll
on ad_ll.skp_credit_case = lp.last_loan_t2
Left join owner_dwh.FT_CONTRACT_BASE_AD fcba_ll
on fcba_ll.skp_credit_case = lp.last_loan_t2
left join OWNER_DWH.F_CONTRACT_AGGR_BALANCE_AD fcaba
on fcaba.skp_credit_case =lp.last_loan_t2
left join
(
SELECT A.SKP_CREDIT_CASE, count(*) as hol_count
FROM OWNER_DWH.F_INSTALMENT_HEAD_AD A
WHERE A.SKP_INSTALMENT_REGULARITY = 6 AND A.CODE_INSTALMENT_HEAD_STATUS = 'a'
AND A.AMT_INSTALMENT = 0
group by skp_credit_case
) hol
on hol.skp_credit_case=dc.skp_credit_case
left join (
SELECT SKP_CREDIT_CASE, MAX (NUM_INSTALMENT_NUMBER) cnt
FROM OWNER_DWH.F_INSTALMENT_HEAD_AD
WHERE CODE_INSTALMENT_HEAD_STATUS = 'a'
GROUP BY SKP_CREDIT_CASE) cnt
on cnt.skp_credit_case=lp.last_loan_t2
/*left join
(select skp_credit_case, count(*) as cnt_holidays from
owner_dwh.FT_INSTALMENT_HEAD_AD
where skp_ins_head_generating_type = 306 and DATE_INSTALMENT>=add_months (sysdate, - 12)
group by skp_credit_case
where) holid
on dc.skp_credit_cae = holid.skp_credit_case*/
)
;
Приложение 3
Прототип product
select
dp.skp_product as SKP_PRODUCT
dp.code_product as CODE_PRODUCT
dp.name_product as NAME_PRODUCT
dp.code_maturity_type as CODE_MATURITY_TYPE
dp.CODE_PRODUCT_TYPE as CODE_PRODUCT_TYPE
dp.DTIME_PRODUCT_VALID_FROM as DTIME_PRODUCT_VALID_FROM
dp.DTIME_PRODUCT_VALID_TO as DTIME_PRODUCT_VALID_TO
dp.CODE_PRODUCT_PURPOSE as CODE_PRODUCT_PURPOSE
dp.NUM_VERSION as NUM_VERSION
dpp.CODE_PRODUCT_PROFILE as CODE_PRODUCT_PROFILE
cc.CODE_CURRENCY
dp.DTIME_INSERTED as DTIME_INSERTED
dp.DTIME_UPDATED as DTIME_UPDATED
cpvs.CODE_PRODUCT_VERSION_STATUS as CODE_PRODUCT_VERSION_STATUS
dp.DTIME_ACTIVATION as DTIME_ACTIVATION
dp.DTIME_PLANNED_ACTIVATION as DTIME_PLANNED_ACTIVATION
dp.CODE_PRICELIST as CODE_PRICELIST
dp.FLAG_ALTERNATIVE as FLAG_ALTERNATIVE
dp.CODE_TERMS_AND_CONDITIONS as CODE_TERMS_AND_CONDITIONS
dp.DTIME_TERMINATION as DTIME_TERMINATION
from owner_dwh.DC_PRODUCT dp
left join owner_dwh.DC_PRODUCT_PROFILE dpp
on dp.SKP_PRODUCT_PROFILE=dpp.SKP_PRODUCT_PROFILE
left join owner_dwh.CL_CURRENCY cc
on cc.SKP_CURRENCY =dp.SKP_CURRENCY
left join owner_dwh.CL_PRODUCT_VERSION_STATUS cpvs
on cpvs.SKP_PRODUCT_VERSION_STATUS = dp.SKP_PRODUCT_VERSION_STATUS
where skp_product = 1
;
Приложение 4
Прототип client
select
dc.id_cuid as ID_CUID
dc.skp_client as SKP_CLIENT
dc.code_gender as CODE_GENDER
dc.name_first as NAME_FIRST
dc.name_last as NAME_LAST
dc.name_full as NAME_FULL
dc.name_middle as NAME_MIDDLE
dc.date_birth as DATE_BIRTH
dc.code_education_type as CODE_EDUCATION_TYPE
'tbd' as AMT_INCOME_MAIN
trunc (months_between (sysdate, dc.date_birth)/12)
'tbd' as NAME_LANGUAGE1
'tbd' as NAME_LANGUAGE2
decode (name_gender, 'Male', concat ('Mr. ', NAME_LAST), concat ('Ms. ', NAME_LAST)) as NAME_CLIENT_FOR_SMS
'tbd' as FLAG_CONSENT_TO_DP
nvl (dddm.TEXT_VALID_ACCOUNT_NUMBERS, 'N/A') as TEXT_VALID_ACCOUNT_NUMBERS
'tbd' as FLAG_MOBILE_APP
'tbd' as SKP_CONTRACT_FIRST
'tbd' as SKP_CONTRACT_LAST
decode (ccc.skp_client, null, 'N', 'Y') as FLAG_ALREADY_FILLED_APPL
'tbd' as CODE_BUSINESS_REGION
'tbd' as SKP_APPL_FIRST
'tbd' as SKP_APPL_LAST
'tbd' as FLAG_CANCELLED_LAST_60_DAYS
'tbd' as DTIME_CONTRACT_CLOSE
'tbd' as CNT_LAST_CANCELLED_IN
from
owner_dwh.DC_CLIENT dc
left join (select dddm.skp_client, LISTAGG (dba.TEXT_ACCOUNT_NUMBER, '; ') WITHIN GROUP (order by null) as TEXT_VALID_ACCOUNT_NUMBERS
from owner_dwh.DC_DIRECT_DEBIT_MANDATE dddm
left join owner_dwh.DC_BANK_ACCOUNt dba on dba.skp_bank_account = dddm.skp_bank_account
where upper (dba.TEXT_ACCOUNT_NUMBER) not like ' % X % '
group by dddm.skp_client
) dddm
on dddm.skp_client = dc.skp_client
left join (select da.skp_client as skp_client
from owner_dwh.DC_APPLICATION da
left join owner_dwh.f_credit_status_tt fcst
on da.skp_credit_case = fcst.skp_credit_case
where fcst.skp_credit_status in (7, 6)
group by da.skp_client) ccc
on ccc.skp_client = dc.skp_client
where dc.skp_client =15673;
Размещено на Allbest.ru
...Подобные документы
Характеристика основных этапов разработок и проектирования базы данных, определение целей ее создания и функциональных особенностей, предметной области и необходимой информации. Требования к инфологической модели. Методы физической организации данных.
курсовая работа [1,7 M], добавлен 22.02.2011Определение базы данных и банков данных. Компоненты банка данных. Основные требования к технологии интегрированного хранения и обработки данных. Система управления и модели организации доступа к базам данных. Разработка приложений и администрирование.
презентация [17,1 K], добавлен 19.08.2013Определение многомерной модели данных для удовлетворения основных информационных потребностей предприятия. Экстракция, загрузка и перенос данных из различных источников данных. Разработка собственных ETL–систем. Оптимизация работы хранилища данных.
презентация [9,1 M], добавлен 25.09.2013Формы представляемой информации. Основные типы используемой модели данных. Уровни информационных процессов. Поиск информации и поиск данных. Сетевое хранилище данных. Проблемы разработки и сопровождения хранилищ данных. Технологии обработки данных.
лекция [15,5 K], добавлен 19.08.2013Основные понятия базы данных. Разработка сложной формы для обработки данных. Модели организации данных. Архитектура Microsoft Access. Реляционные связи между таблицами баз данных. Проектирование базы данных. Модификация данных с помощью запросов действий.
лабораторная работа [345,5 K], добавлен 20.12.2011Разработка информационно-аналитической системы агентства недвижимости. Обоснование выбора архитектуры базы данных и СУБД. Моделирование потоков данных (DFD диаграмм). Проектирование инфологической модели данных с использованием модели "сущность-связь".
дипломная работа [5,4 M], добавлен 06.06.2013Развитая автоматизированная информационная система как условие обеспечения эффективного функционирования организации. Проектирование и построение информационной логической модели базы данных. Краткая характеристика Access. Разработка структуры таблиц.
курсовая работа [39,6 K], добавлен 27.02.2009Описание предметной области, определение функциональных требований к системе и построение диаграммы потока данных. Построение модели "сущность-связь", описание сущностей и атрибутов модели. Построение реляционной базы данных и описание ее таблицы.
курсовая работа [624,5 K], добавлен 30.05.2019Определение функциональных зависимостей. Разработка структуры базы данных. Организация запросов к базе данных. Использование триггеров для поддержки данных в актуальном состоянии. Разработка хранимых процедур и функций. Ограничения ведения базы данных.
курсовая работа [113,2 K], добавлен 17.06.2014Построение информационно-логической модели базы данных. Корректировка данных средствами запросов. Проектирование алгоритмов обработки данных. Реализация пользовательского интерфейса средствами форм. Разработка запросов для корректировки и выборки данных.
курсовая работа [680,9 K], добавлен 19.10.2010Описание торговой сети, сбор данных, которые должны содержаться в базе данных. Определение сущностей и атрибутов и построение концептуальной модели. Переход к физической модели. Определение таблиц, полей и типов данных. Определение связей между таблицами.
курсовая работа [1,5 M], добавлен 31.03.2015Принципы построения и основные компоненты хранилищ данных, общая характеристика основных требований к ним по Р. Кинболлу. Понятие и виды баз данных. Методика проектирования комплекса задач автоматизации учета по счету 02 "Амортизация основных средств".
контрольная работа [27,8 K], добавлен 12.11.2010Особенности создания учетных записей на файловом сервере. Разработка функциональной модели базы данных. Отчет по дугам модели. Сущность, атрибуты и связи информационной модели. Разработка базы данных в системе управления базами данных MS Access.
контрольная работа [2,3 M], добавлен 23.01.2014Сущность и характеристика типов моделей данных: иерархическая, сетевая и реляционная. Базовые понятия реляционной модели данных. Атрибуты, схема отношения базы данных. Условия целостности данных. Связи между таблицами. Общие представления о модели данных.
курсовая работа [36,1 K], добавлен 29.01.2011Сущность разработки и построения хранилища данных в цепочке локальных сетей. Его типичная структура. Особенности организации хранения информации. Алгоритм действия системы ROLAP и его сравнение с алгоритмом многомерных систем управления базами данных.
курсовая работа [743,1 K], добавлен 23.01.2015Определение общих требований к организации автоматизированного рабочего места. Создание модели автоматизированного рабочего места менеджера фирмы "Информстиль". Разработка базы данных и описание алгоритма программы по учету продаж вычислительной техники.
дипломная работа [2,9 M], добавлен 03.07.2015Разработка базы данных, позволяющей определять месторасположение на полке и код товаров в магазинных складах, количество и качество товаров. Концепция баз данных. Модели данных, описание данных проектирования. Разработка программного приложения.
курсовая работа [1,1 M], добавлен 13.06.2014Иерархическая модель данных. Основные элементы сетевой модели данных. Требования заказчика. Разработка автоматизированной системы управления "Преподаватели". Описание этапов разработки. Установка связей между таблицами. Резервирование базы данных в SQL.
курсовая работа [1,3 M], добавлен 10.02.2014Способы мониторинга качества данных. Формирование функциональных требований к системе мониторинга консистентности данных. Документирование требований к системе мониторинга консистентности данных. Написание скриптов проверок для системы мониторинга.
дипломная работа [387,3 K], добавлен 26.08.2017Этапы создания и разработки базы данных. Построение модели предметной области. Разработка даталогической и физической моделей данных, способы обработки данных о сотрудниках организации. Проектирование приложений пользователя. Создание кнопочной формы.
курсовая работа [2,1 M], добавлен 14.02.2011
