La mpiran 1 Hasil Wawancara

Lampiran 1 Hasil Wawancara L1

No 1

2

Pertanyaan Bagaimana cara mendapatkan data untuk dijadikan analytical report ?

Apa itu eMAS ?

Jawaban Cara mendapatkan data untuk dijadikan analytical report adalah : 1.

Ketika seseorang ingin mengajukan permohonan kredit ke PT.Bank X, ia harus menjalani operational procedure yaitu mengisi form pengajuan kredit. Selama permohonan kredit belum di-approve, data tersebut ada di dalam server LOS (Loan Origination System). LOS adalah sebuah database server yang menyimpan informasi calon peminjam PT.Bank X.

2.

Suatu aplikasi pengajuan kredit akan mengalami proses scoring, yakni proses penilaian kelayakan kredit mengenai apakah orang yang mengajukan kredit tersebut akan mampu melunasi kredit tepat waktu dan tanpa masalah. Jika suatu aplikasi lolos dari proses scoring, maka datanya akan masuk ke dalam server eMAS (Enhancement Mandiri Advanced System). Server eMAS adalah Core Banking System dari PT .Bank X. Database server inilah yang setiap harinya menyimpan data transaksi setiap nasabah PT .Bank X.

3.

Data transaksi harian dari database server eMAS dimasukkan ke data warehouse dengan metode Backup dan Restore. Kemudian dilakukan proses ETL dari LOS server dan data warehouse server ke data mart server dengan proses ET L (Extract, Transform, Load).

4.

Proses yang terjadi di dalam data mart server adalah pembentukan beberapa fact table dari data mart yang kemudian dengan bantuan Analytical Reporting Tools, yaitu Business Object 6.0, dihasilkanlah analytical report yang digunakan pihak managerial untuk monitoring kinerja Consumer Loan dan mendukung proses pengambilan keputusan.

eMAS (Enhancement Mandiri Advance System) adalah core banking PT .Bank X. Database server inilah yang setiap harinya menyimpan data transaksi setiap nasabah PT.Bank X.

1

L2

3

Apa itu LOS ?

LOS (Loan Origination System) adalah sebuah database server yang menyimpan informasi calon peminjam PT.Bank X (bisa nasabah, bisa juga non-nasabah PT .Bank X). Setelah melalui beberapa proses seleksi dan penilaian kelayakan penyediaan pinjaman, maka calon peminjam yang diterima pinjamannya akan masuk ke dalam eMAS dan terhitung sebagai salah satu nasabah PT.Bank X. Data-data peminjam yang ada di LOS lebih lengkap daripada di eMAS. Oleh karena, untuk menghasilkan analytical report yang lengkap dibutuhkan data-data dari eMAS dan LOS.

4

Apa kendala yang terjadi dengan sistem yang sudah berjalan sekarang dan dampaknya?

Kendala yang ada sekarang adalah : 1. Untuk membentuk analytical report dengan jumlah data sekitar 300.000 record membutuhkan waktu 5-7 jam sehingga analytical report yang dihasilkan sudah tidak up to date karena sudah lewat 1 hari kerja. 2. Karena permasalahan efisiensi waktu yang terjadi, maka pihak Consumer Loan Groups lebih memilih untuk melakukan query secara langsung ke data mart yang ada sehingga Business Object yang sudah ada tidak digunakan dengan optimal. 3. Dibutuhkan tambahan kemampuan yang harus dimiliki oleh staff Consumer Loan Group, yaitu SQL query untuk memenuhi permintaan Ad Hoc Report dari pihak managerial

5

Design data warehouse yang ada saat ini hanya menyimpan data harian Apa yang sehingga ketika pembentukan data mart menjadi fact table dibuatlah menyebabkan suatu tabel (P_ACCT _DTL_ENH) yang menyimpan history data untuk 1 proses tahun. Karena adanya proses AUDIT_TRAIL yang melakukan pembentukan fact table membutuhkan pengecekan setiap kali data yang baru diambil dan menyimpan hasil pengecekan tersebut dalam satu tabel dengan tujuan untuk process waktu 5 hingga 7 monitoring. jam ?

6

Mengapa desain data warehouse dibuat hanya menyimpan data harian?

Pada saat pengembangan data warehouse, tujuannya adalah untuk menghasilkan regulatory report (report harian yang hanya menyimpan posisi terakhir) untuk dilaporkan ke Bank Indonesia.

7

Mengapa data warehouse yang ada sekarang belum dijadikan histori ?

Berkaitan dengan biaya, resources yang tersedia dan perencanaan proyek. Untuk perubahan arsitektur data warehouse sendiri, pihak kami (tim IT PT.Bank X) sudah mencanangkan perencanaan proyek tersebut untuk pada tahun 2008 ini dan akan segera diimplementasikan pada tahun 2009.

2

L3

8

Bagaimana struktur organisasi dan pembagian tanggung ja wab dan wewenang di IT-BSA Group?

IT – BSA merupakan group yang merupakan bagian dari direktorat IT di PT.Bank X yang dipimpin oleh seorang Group Head. IT – BSA menangani sisi development dari aplikasi dan sistem-sistem yang ada. IT – BSA terdiri dari 7 department yaitu : 1. Department IT – IMS IT -MIS Merupakan singkatan dari IT - Information Management System Department. Departemen ini terdiri dari 3 bagian : DWH & BI Mengupayakan solusi yang berkaitan dengan permasalahan dibidang data warehouse dan Bussiness Inteligence System. PMS, MIS, EIS Mengupayakan solusi yang berkaitan dengan permasalahan dibidang Perfomance, Management, Expert Information System. Regularly Reporting Bertugas untuk menghasilkan report ke BI (Bank Indonesia), report untuk keperluan auditing, report untuk vendor, dll. 2. Department IT – RMS Department IT – RMS merupakan singkatan dari IT - Risk Management System Department. Department ini berkaitan dengan solusi-solusi yang berhubungan dengan resiko. Misalnya : ERM (Enterprise Risk Management), Credit Scoring (Menilai apakah seorang pemohon kredit dapat membayar kredit tepat waktu dan tanpa masalah). 3. Department IT – ACR Department IT – ACR merupakan singkatan dari IT - Asset & Customer Relationship Solution Department. Department ini mengelola aset-aset PT.Bank X yang dapat berupa pinjaman nasabah (consumer loan). Selain itu, IT -ACR juga bertugas untuk mengatur customer relationship management (tata cara berhubungan/ melayani nasabah).

3

L4 4. Department IT – WOB Department IT – WOB merupakan singkatan dari IT – WholeSale & Overseas Branches Solutions Department. Department ini berkaitan dengan transaksi besar (wholesale), misalnya OPICS, Surat-surat berharga,dll. Selain itu IT-WOB juga terkait dengan cabang-cabang PT.Bank X di luar negeri. Misalnya : di Singapore, Dili (India), dll. Khusus untuk pembukaan cabang baru misalnya di Burma, IT -WOB yang mempersiapkan sistem-sistem yang nantinya akan di jalankan di cabang baru tersebut agar dapat tetap terintegrasi dengan PT .Bank X pusat. 5. Department IT – ECS Department IT – ECS merupakan singkatan dari IT – Electronic Channel & Credit Card Solution Department. Department ini berkaitan dengan ATM, e-Banking, m-Banking, dan Credit Card. Department inilah yang membuat sistem untuk online transaction yang akan memudahkan nasabah untuk melakukan kegiatan perbankan dimana saja dan kapan saja. 6. Department IT – LT S Department IT – LTS merupakan singkatan dari IT – Liability & Transaction Solution Department. Department ini bertugas dengan segala se suatu yang menjadi tanggung ja wa b PT .Bank X (Liability of PT .Bank X), misalnya : T abungan, deposito, giro dan CMS (Corporate Management System). CMS merupakan suatu sistem yang memungkinkan suatu perusahaan yang menjadi merupakan nasabah PT .Bank X untuk mengakses dan mengatur data-data perbankan perusahaan itu sendiri secara online tanpa harus datang ke PT .Bank X. 7. Department IT – CCS Department IT – CCS merupakan singkatan dari IT – Corporate Center Solution Department. Department ini bertugas untuk mengatasi permasalahan dan mengatur segala aktivitas yang berkaitan dengan Corporate. Misalnya : pada bagian HRD.

9

Apa yang dimaksud dengan PPAP ?

PPAP adalah singkatan dari Penyisihan Penghapusan Aktiva Produktif. Setiap ada kredit tidak lancar, Bank harus menyisihkan 10% dari nilai kredit tersebut kepada Bank Indonesia. Hal ini untuk menjaga jika banyak kredit macet, Bank masih mempunyai dana untuk membayar bunga deposito kepada nasabah. Sehingga walaupun Bank tidak mendapat uang dari bunga kredit yang seharusnya disetor oleh nasabah, Bank harus tetap membayar kewajibannya untuk membayar bunga deposito.

4

L5

10

Bagaimana struktur organisasi dan pembagian tanggung ja wab dan wewenang di Consumer Loan Group?

Consumer Loan merupakan group bagian dari direktorat Consumer Finance di PT .Bank X yang dipimpin oleh seorang Group Head. Consumer Loan Group menangani pinjaman-pinjaman dari para nasabah PT .Bank X. Consumer Loan Group terdiri dari 8 department yaitu : 1. Personal Loan Department Personal Loan menangani hal-hal yang berkaitan dengan :

Kredit Tanpa Agunan /Jaminan Kredit Tanpa Agunan adalah kredit yang akan diberikan kepada nasabah PT .Bank X tanpa harus meyerahkan agunan/ jaminan kepada PT .Bank X. Kredit tanpa agunan tersebut dapat digunakan untuk kepentingan pendidikan, payroll (gaji karyawan), regular (kepentingan lainnya).

Kredit Mitrakarya Kredit Mitrakarya adalah layanan kredit yang ditujukan bagi perusahaan-perusahaan yang telah bekerja sama dengan PT .Bank X. Kredit tersebut dapat digunakan untuk kepentingan KPR (Kredit Pemilikan Rumah), KPM (Kredit Pemilikan Mobil), dan KTA (Kredit tanpa agunan). Kredit Mitrakarya ini akan memberikan perhitungan suku bunga khusus, berbe da dengan suku bunga personal. PT .Bank X akan memberikan limit budget kredit yang untuk satu perusahaan.

Sehingga job desk dari Personal Loan Department adalah : ¾ Mencari cara agar masyarakat yang membutuhkan dana secara cepat, akan meminjam dana melalui PT .Bank X dengan menggunakan Kredit Tanpa Agunan. ¾ Mencari cara agar perusahaan-perusahaan menjalin kerja sama dengan PT.Bank X dan meminjam dana melalui PT .Bank X dengan menggunakan Kredit Mitrakarya. ¾ Mencari tahu aplikasi/produk kredit apa yang dibutuhkan perseorangan/perusahaan pada umumnya, dan merancang suatu produk yang sesuai dengan kebutuhan pasar, agar PT .Bank X menjadi Bank terpercaya pilihan masyarakat.

5

L6

2. Automotif Department Automotif Department menangani hal-hal yang berkaitan dengan Kredit Pemilikan Mobil (KPM) dengan jaminan. Sehingga job desk dari Personal Loan Department adalah : ¾

Mencari cara agar masyarakat yang ingin memiliki mobil, membeli mobil tersebut melalui PT.Bank X melalui Kredit Pemilikan Mobil (KPM) PT.Bank X. ¾ Selain memikirkan strategi untuk bersaing dengan Kredit Pemilikan Mobil dari bank lain, Automotif Department juga memiliki tugas untuk membuat ketentuan-ketentuan yang berlaku untuk aplikasi KPM PT .Bank X. ¾ Menentukan suku bunga yang diterapkan untuk Kredit Pemilikan Mobil (KPM) PT .Bank X. ¾ Menentukan jenis agunan yang harus diserahkan pada PT .Bank X sebagai jaminan kredit. 3. Mortgage Department Department ini menangani hal-hal yang berkaitan dengan :

Kredit Pemilikan Rumah (KPR) dengan agunan Kredit Pemilikan Rumah (KPR) dengan agunan adalah kredit yang diperuntukkan bagi nasabah yang ingin membeli rumah baru dari developer maupun rumah second (rumah yang pernah di huni sebelumnya). Untuk dapat menikmati Kredit Pemilikan Rumah (KPR) ini, nasabah harus memberikan agunan atau kepada PT.Bank X sebagai jaminan. Agunan dapat berupa akte tanah, surat kepemilikan mobil, dan lain-lain.

Kredit Multiguna Kredit Multiguna adalah jenis kredit yang dapat digunakan untuk keperluan konsumtif, misalnya : untuk pendidikan, dan lain-lain.

Sehingga job desk dari Mortgage Department adalah : ¾ Mencari strategi bisnis agar masyarakat yang ingin memiliki rumah, membeli rumah secara kredit melalui PT .Bank X. ¾ Mencari strategi bisnis yang dapat menyebabkan masyarakat memilih PT .Bank X sebagai bank terpercaya untuk kredit keperluan konsumtif mereka. ¾ Pada Mortgage Department ini, terdapat divisi promotion and marketing yang bertugas untuk melakukan promosi mengenai produk-produk Consumer Loan Group, yakni : Kredit Pemilikan Mobil (KPM), Kredit Pemilikan Rumah (KPR), Kredit Tanpa Agunan (KT A), Kredit Mitrakarya dan Kredit Multiguna. Divisi tersebut dapat melakukan promosi melalui berbagai media cetak (koran, majalah, tabloid), elektronik (T V, radio, internet), maupun dengan mengadakan bazaar atau pameran.

6

L7

4. Developer / Broker Relationship Department Developer / Broker Relationship Department bertugas untuk : ¾ Menjalin kerja sama dengan developer-developer real estate/ perumahan dan broker-broker (lembaga perantara penjual-belian rumah, misalnya : Ray White, Century 21, Era, dan lain-lain). ¾ Menilai apakah suatu developer/broker yang akan menjalin hubungan kerja sama dengan PT .Bank X merupakan perusahaan atau organisasi yang berkualitas dan bertanggung jawa b. ¾ Kerja sama yang dijalin antara PT.Bank X dengan pihak developer adalah mengenai pembelian rumah baru langsung dari developer. Dengan adanya kerja sama, diharapkan dapat memudahkan transaksi yang terjadi antara nasabah PT.Bank X dengan pihak developer. ¾ Kerja sama yang dijalin antara Bank Madiri dengan pihak broker adalah mengenai pengurusan atau proses penjualbelian rumah baik yang baru atau pun second. Dengan adanya kerja sama dengan pihak broker, diharapkan dapat memudahkan transaksi yang terjadi antara nasabah PT .Bank X dengan pihak brokernya sendiri. 5. Sales Management Department Sales Management Department bertugas untuk melakukan penjualan aplikasi-aplikasi kredit Consumer Loan Group, yakni : Kredit Pemilikan Mobil (KPM), Kredit Pemilikan Rumah (KPR), Kredit T anpa Agunan (KT A), Kredit Mitrakarya dan Kredit Multiguna. Petugas yang menjalankan tugas tersebut biasanya merupakan outsourcing yang dihire oleh PT.Bank X untuk mengajak masyarakat menjadi nasabah PT.Bank X. 6. Jakarta Operation Department Jakarta Operation Department merupakan department yang menangani processing pengajuan kredit untuk wilayah Jakarta saja. Deparment ini bertugas untuk melakukan : ¾ Processing pengajuan aplikasi kredit, yang dimulai dari permberian informasi dan keterangan mengenai jenis kredit yang dibutuhkan nasabah, ketentuan-ketentuan yang berlaku, cara pembayaran, interview untuk memperoleh data pribadi nasabah (misalnya : alamat rumah, pekerjaan, gaji) ¾ Setelah nasabah mendapat keterangan lebih lanjut mengenai kredit PT.Bank X, nasabah akan menerima SPPK (Surat Penawaran Pemberian Kredit), yang berisi limit kredit yang disetujui PT .Bank X untuk nasabah tersebut. Jika nasabah bersedia menerima limit kredit yang diberikan, maka selanjutnya semua data mengenai pengajuan kredit oleh nasabah tersebut diserahkan kepada Jakarta Disbursement Department.

7

L8 7. Jakarta Disbursement Department Setelah nasabah menyetujui limit kredit yang dicantumkan oleh PT .Bank X pada SPPK, maka Jakarta Disbursement Department bertugas untuk mencairkan dana tersebut. Dana tersebut dapat disalurkan langsung kepada pihak-pihak yang terkait, misalnya untuk KPR, dana disalurkan kepada developer. 8. Regional Operation Department Regional Operation Department merupakan department yang menangani processing pengajuan kredit hingga pencairan dana untuk wilayah di luar Jakarta. Rincian tugas Deparment ini adalah untuk melakukan : ¾ Processing pengajuan aplikasi kredit, yang dimulai dari permberian informasi dan keterangan mengenai jenis kredit yang dibutuhkan nasabah, ketentuan-ketentuan yang berlaku, cara pembayaran, interview nasabah untuk memperoleh datadata pribadi nasabah (misalnya : alamat rumah, pekerjaan, gaji) ¾ Setelah nasabah mendapat keterangan lebih lanjut mengenai kredit PT.Bank X, nasabah akan menerima SPPK (Surat Penawaran Pemberian Kredit), yang berisi limit kredit yang disetujui PT .Bank X untuk nasabah tersebut. ¾ Perbedaan antara Regional Operation Department dengan Jakarta Operation Department selain wilayah yang ditanganinya, terletak pada hal berikut : jika nasabah bersedia menerima limit kredit yang diberikan, maka selanjutnya Regional Operation Department dapat langsung mencairkan dana tersebut tanpa harus menyerahkan data-data pengajuan aplikasi kredit nasabah tersebut ke department lain yang khusus untuk mencairkan dana. Hal ini dikarenakan untuk wilayah luar Jakarta masih dapat di handle oleh satu department saja, karena jumlah aplikasi yang diproses tidak sebanyak wilayah Jakarta.

11

Apa yang dimaksud dengan Document Collection ?

Doccument collection adalah proses pengumpulan data-data yang menjadi persyaratan bagi nasabah yang ingin mengajukan kredit, datadata ynag dimaksud dapat berupa KTP (Kartu T anda Penduduk), Slip Gaji (jika nasabah tersebut bekerja sebagai karyawan suatu perusahaan), dan data-data pribadi lainnya.

12

Apa yang dimaksud dengan DT BO ?

DT BO adalah singkatan dari Data To Be Obtained, yaitu saat dimana data-data persyaratan nasabah telah dilengkapi semua. Sehingga datadata tersebut dapat dianalisis lebih lanjut untuk proses scoring, dll.

8

L9

13

Apa yang dimaksud dengan proses scoring?

Proses scoring adalah proses pengecekan kelayakan seorang nasabah untuk diberikan pinjaman kredit oleh PT.Bank X, yang dilakukan pada saat proses scoring ini adalah pengecekan jaminan yang diberikan oleh nasabah, apakah nilai jaminan tersebut sebanding dengan jumlah dana yang akan dikeluarkan oleh PT.Bank X untuk nasabah tersebut. Hal lain yang dapat dilakukan adalah mempertimbangkan apakah dengan gaji yang diperoleh oleh nasabah setiap bulannya, nasabah tersebut dapat melunasi kewajiban membayar cicilan kredit setiap bulannya.

14

Apa yang dimaksud dengan proses Appraisal, Investigation, Verification, IDIBI, dan disbursement ?

a. Appraisal adalah : scoring jaminan, apakah nilai jaminan tersebut sebanding dengan jumlah dana yang akan dikeluarkan oleh PT .Bank X untuk nasabah tersebut. b. Investigation adalah : pengecekan kebenaran data nasabah, misalnya apakah benar nasabah tersebut tinggal di daerah tersebut, apa benar nasabah tersebut bekerja di perusahaan tersebut, dll. c. Verification adalah : Pengecekan mengenai gaji nasabah, apakah benar gaji-nya sekian rupiah? (biasanya dicek ke bagiam HRD) d. IDIBI adalah : Pengecekan status nasabah ke Bank Indonesia, apakah nasabah tersebut pernah bermasalah dengan kredit lain e. Disbursement adalah : Pencairan dana kredit yang telah disetujui

15

Apa yang dimaksud dengan SPPK?

SPPK adalah singkatan dari Surat Penawaran Pemberian Kredit, surat ini berisi jumlah limit kredit yang disetujui oleh PT .Bank X. Misalnya : Seseorang mengajukan permintaan kredit sebesar 100 juta, dengan memberi jaminan akte rumah, setelah melalui proses scoring, PT .Bank X merasa limit kredit yang sebanding dengan jaminannya hanya sebesar 80 juta. Maka melalui SPPK ini, PT .Bank X akan memberitahukannya pada nasabah. Jika nasabah menyetujui nya, maka nasabah tersebut harus menandatangani PK (Perjanjian Kredit).

16

Apa yang dimaksud dengan Mitrakarya ?

Mitrakarya adalah jenis kredit PT.Bank X dimana nasabahnya bukan perseorangan melainkan satu perusahaan. Dimana nantinya PT .Bank X akan memberikan limit maksimum yang dapat digunakan perusahaan tersebut untuk kredit pada PT.Bank X. Misalnya : untuk perusahaan A, PT.Bank X memberikan limit 1 Milyar, perusahaan tersebut dapat mengajukan kredit untuk pegawai-pegawainya senilai 1 Milyar. Untuk dapat mengambil kredit mitrakarya tentu harus melalui proses scoring yang lebih ketat lagi.

9

L10

Lampiran 2 Analytical

Report Consumer Loan

1. Detailed Average Turn Aroun Time Report

Gambar 1. Detailed Average Turn Aroun Time Report

10

L11

Penjelasan Detailed Average Turn Aroun Time Report: 1. AIP - Doc Collection : Waktu yang dibutuhkan dari proses AIP (Approval In Principal) / Scoring awal hingga pengumpulan persyaratan (KTP, KK, slip gaji, dan lain-lain). Dari report di atas dapat dilihat bahwa proses AIP – Doc Collection rata-rata membutuhkan waktu hingga 4 hari. 2. Doc Collection – DTBO : Waktu yang dibutuhkan dari proses pengumpulan datadata persyaratan hingga DTBO (Data To Be Obtained) / persyaratan lengkap dikumpulkan. Dari report di atas terlihat bahwa proses AIP – Doc Collection membutuhkan waktu 0 hari. Artinya proses tersebut hanya memerlukan waktu sesaat, hingga tidak sampai 1 hari proses tersebut telah selesai dilaksanakan. 3. Total – Sales : Jumlah waktu yang dibutuhkan dari AIP-DTBO (AIP - Doc Collection)+(Doc Collection – DTBO). Report menunjukkan total sales = 4. 4. DTBO – Appraisal : Waktu yang dibutuhkan dari proses DTBO (Data To Be Obtained) / persyaratan lengkap dikumpulkan hingga scoring jaminan apakah sepadan dengan dana kredit yang dikeluarkan. Dari report di atas dapat dilihat bahwa proses DTBO – Appraisal rata-rata membutuhkan waktu hingga 3 hari. 5. DTBO – Investigation : Waktu yang dibutuhkan dari proses DTBO hingga proses investigasi selesai dilakukan. Dimana Investigasi yang dimaksud adalah : pengecekan kebenaran data-data nasabah, misalnya : apakah benar alamat rumah dan alamat kantor sesuai dengan yang dicantumkan nasabah. Dari report di atas

11

L12 dapat dilihat bahwa proses DTBO – Investigation rata-rata membutuhkan waktu hingga 4 hari. 6. DTBO – Verification : Waktu yang dibutuhkan dari proses DTBO hingga verifikasi. Verifikasi adalah proses pengecekan benarkah gaji nya per-bulan sekian rupiah (cek ke bagian HRD perusahaan tempat ia bekerja). Dari report di atas terlihat bahwa proses DTBO – Verification rata-rata membutuhkan waktu 4 hari. 7. DTBO – IDIBI

: Waktu yang diperlukan dari proses DTBO hingga IDIBI

(Pengecekan status nasabah ke Bank Indonesia, apakah nasabah tersebut masuk dalam daftar black list karena pernah tidak membayar tunggakan kredit lain). Dari report di atas dapat di lihat bahwa proses DTBO – Verification rata-rata membutuhkan waktu hingga 6 hari. 8. Sub Total – Verification : Jumlah waktu maksimum untuk melakukan verifikasi [ M aks(DTBO – Investigation, DTBO – Verification, DTBO – Verification, DTBO – IDIBI)]. Report di atas menunjukkan Sub Total – Verification adalah 6 hari. 9. Final score and limit setting : Waktu yang dibutuhkan dari Final Scoring hingga menentukan besarnya dana yang akan diberikan kepada nasabah untuk kredit. Dari report di atas terlihat bahwa proses Final score and limit setting membutuhkan waktu 0 hari. Artinya proses tersebut hanya memerlukan waktu sesaat, hingga tidak sampai 1 hari proses tersebut telah selesai dilaksanakan. 10. Final Score – Approval :. Waktu

yang dibutuhkan dari proses final scoring

(pengecekan mengenai kelayakan seorang nasabah yang akan diberikan dana kredit) hingga proses approval (aplikasi pengajuan kredit nasabah tersebut 12

L13 diterima). Dari report di atas terlihat bahwa proses Final Score – Approval ratarata membutuhkan waktu 1 hari. 11. SPPK/Send to loan disbursement : Waktu yang dibutuhkan dari proses keluarnya SPPK hingga pengiriman permohonan pencairan dana ke bagian pencairan dana. Dari report di atas terlihat bahwa proses SPPK/Send to loan disbursement ratarata hanya membutuhkan waktu 0 hari. Artinya proses tersebut hanya memerlukan waktu sesaat, hingga tidak sampai 1 hari proses tersebut telah selesai dilaksanakan. 12. Total – Processing : Jumlah aplikasi pengajuan kredit yang diproses. 13. Original DTBO - Loan Disbursement Others : Waktu yang dibutuhkan dari proses DTBO hingga pencairan dana kredit selain KPR, misalnya KPM , KTA. Dari report di atas terlihat bahwa proses Original DTBO - Loan Disbursement Others rata-rata membutuhkan waktu 76 hari. 14. Original DTBO - Loan Disbursement New Houses : Waktu yang dibutuhkan dari proses DTBO hingga pencairan dana untuk kredit KPR dari developer. Dari report di atas terlihat bahwa proses Original DTBO - Loan Disbursement NewHouse ratarata membutuhkan waktu 6 hari. 15. SPPK – Cancel : Waktu antara dikeluarkannya SPPK hingga dicancel oleh nasabah. Dari report di atas terlihat bahwa proses SPPK – Cancel rata-rata membutuhkan waktu 10 hari. 16. SPPK – Disbursement others : Waktu dari SPPK hingga pencairan dana untuk kredit selain KPR. Dari report di atas terlihat bahwa proses SPPK – Disbursement others rata-rata membutuhkan waktu 74 hari. 13

L14 17. Total – Disbursement : Jumlah aplikasi yang telah dicairkan. 18. SPPK - Disbursement

New House : Waktu dari SPPK hingga pencairan dana

untuk kredit KPR. Dari report di atas terlihat bahwa proses SPPK – Disbursement others rata-rata hanya membutuhkan waktu 0 hari. Artinya proses tersebut hanya memerlukan waktu sesaat, hingga tidak sampai 1 hari proses tersebut telah selesai dilaksanakan.

Keterangan : •

Daily Turnaround Time : Jumlah waktu rata-rata (dalam satuan days) yang diperlukan untuk pembuatan report tersebut setiap harinya.

•

MTD Turnaround Time : Jumlah waktu rata-rata (dalam satuan days) yang diperlukan untuk pembuatan report tersebut dari tanggal 1 bulan tersebut hingga tanggal pembuatan report. M isalnya : report dibuat pada tanggal 23 M aret 2008, maka M TD : 1 - 23 M aret 2008

•

YTD Turnaround Time : Jumlah waktu rata-rata (dalam satuan days) yang diperlukan untuk pembuatan report tersebut dari tanggal 1 Januari tahun tersebut hingga tanggal pembuatan report. M isalnya : report dibuat pada tanggal 23 M aret 2008, maka YTD : 1 Januari - 23 M aret 2008

14

L15

2. P & L Report

Gambar 2. P & L (Profit and Loss) Report

15

L16 Penjelasan Profit and Loss Report (P &L Report) •

Interest Income = Pendapatan dari bunga kredit, sehingga dari data di atas dapat dilihat bahwa pada bulan July 2007 pendapatan PT.Bank X dari bunga kredit adalah sebesar Rp.112.489.078.619

•

Interest Expenses : Pengeluaran untuk bunga deposito, sehingga dari data di atas dapat dilihat bahwa pada bulan July 2007 pegeluaran PT.Bank X untuk bunga deposito adalah sebesar Rp.17.528.867.675

•

Net Interest Income : Pendapatan bersih dari bunga kredit, yaitu pendapatan bersih setelah melakukan kalkulasi : (Interest Income - Interest Expenses) : (Rp.112.489.078.619 - Rp.17.528.867.675) : Rp.94.960.210.945

•

Insurance Fees : Pendapatan dari biaya asuransi yang dibayar oleh nasabah, dari data di atas dapat dilihat bahwa pada bulan July 2007 pendapatan PT.Bank X dari bunga kredit adalah sebesar Rp.112.489.078.619

•

Provission & Commision : Pendapatan dari provisi dan komisi, dari data di atas

dapat dilihat bahwa pada bulan July 2007 pendapatan PT.Bank X dari provisi dan komisi adalah sebesar Rp.4..757.603.370 •

Admin Fees : Pendapatan dari biaya administrasi yang dibayar nasabah setiap bulannya, dari data di atas dapat dilihat bahwa pada bulan July 2007 pendapatan PT.Bank X dari biaya administrasi adalah sebesar Rp.1.042.075.000

•

Other Fees : Pendapatan dari biaya lain-lain (selain dari bunga kredit, biaya asuransi, provisi dan komisi dan biaya administrasi), dari data di atas dapat dilihat

16

L17 bahwa pada bulan July 2007 pendapatan PT.Bank X dari biaya lain-lain adalah sebesar Rp.882.853.111 •

Net Revenue : Pendapatan secara keseluruhan (Net Interest Income + Insurance Fees + Provission & Commision + Admin Fees + Other Fees ), dari data di atas dapat dilihat bahwa pada bulan July 2007 net revenue yang diperoleh PT.Bank X adalah sebesar Rp.102.251.703.394

•

Expenses : Pengeluaran-pengeluran PT.Bank X, diantaranya : -

Marketing : Biaya Maketing (promosi pasang billboard, iklan di TV, Koran, majalah, dll), dari data di atas dapat dilihat bahwa pada bulan July 2007 pengeluaran

PT.Bank

X

untuk

biaya

marketing

adalah

sebesar

Rp.1.631.788.697 -

Premises : Biaya Premise, dari data di atas dapat dilihat bahwa pada bulan July 2007 pengeluaran PT.Bank X untuk biaya marketing adalah sebesar Rp.641.005.437

-

Salaries : Gaji karyawan, dari data di atas dapat dilihat bahwa pada bulan July

2007 pengeluaran PT.Bank X untuk membayar seluruh gaji

karyawannya adalah sebesar Rp. 6.323.641.402 -

Other Expen ses : Pengeluaran lainnya, misalnya air, listrik, telepon, dll. Dari data di atas dapat dilihat bahwa pada bulan July 2007 pengeluaran PT.Bank X untuk membayar seluruh gaji karyawannya adalah sebesar Rp.4.382.266.637

-

Allocated Cost : Dana yang dialokasikan untuk keperluan tertentu, misalnya untuk proyek pengembangan datawarehouse atau upgrade hardisk. Dari 17

L18 data di atas dapat dilihat bahwa pada bulan July 2007 pengeluaran PT.Bank X

untuk

membayar

seluruh

gaji

karyawannya

adalah

sebesar

pengeluaran

dengan

Rp.1.897.767.166

-

Total

Operating

Expenses

:

Total

seluruh

menjumlahkan seluruh peneluaran untuk marketing, premises, salaries, other expenses, dan allocated cost. Dari data di atas dapat dilihat bahwa pada bulan July 2007 pengeluaran PT.Bank X untuk membayar seluruh kegitan operational tersebut adalah sebesar Rp.14.876.469.393 •

PPAP : Penyisihan Penghapusan Aktiva Produktif. Untuk setiap kredit yang tidak lancar, PT.Bank X harus menyisihkan dana 10% dari dana kredit tersebut kepada Bank Indonesia. Dari data di atas dapat dilihat bahwa pada bulan July 2007 pengeluaran PT.Bank X untuk membayar seluruh gaji karyawannya adalah sebesar Rp.7.487.296.991

•

Profit / Loss : Untuk menandakan status keuangan pada bulan tersebut, apakah profit (untung) atau loss (rugi), dan berapa besar keuntungan atau kerugian pada bulan tersebut. Dari data di atas dapat dilihat bahwa pada bulan July 2007 PT.Bank X mendapat keuntungan sebesar Rp.79.887.937.065

18

L19

19

L20

3. KPI Report

Gambar 3. KPI Report

L19

20

L21

21

L22 Penjelasan Key Perfomance of Index (KPI Report) •

Avg TA time (AIP-SPPK) : Waktu rata-rata yang dibutuhkan dari saat AIP (Aproval

In Principal) / saat aplikasi di-approve pertama kali sebelum di-scoring hingga dikeluarkannya SPPK (Surat Penawaran Pemberian Kredit). Dari report di atas dapat dilihat bahwa pada tanggal 18 April 2005, waktu yang diperlukan dari proses AIP-SPPK adalah 14 hari. •

Avg TA time (DTBO-SPPK) : Waktu rata-rata yang dibutuhkan dari tahap DTBO

(Data To Be Obatain)/semua data telah terkumpul lengkap hingga SPPK (Surat Penawaran Pemberian Kredit) dikeluarkan. Dari report di atas dapat dilihat bahwa pada tanggal 18 April 2005, waktu yang diperlukan dari proses DTBO-SPPK adalah 10 hari. •

Net Outstanding : Sisa saldo terakhir yang masih harus dibayar oleh nasabah kepada

PT.Bank X diluar suku bunga. Dari report di atas dapat dilihat bahwa pada tanggal 18 April 2005, jumlah net outstandingnya adalah Rp.7.404.373.000.000 (karena dalam satuan mio=juta) . •

Initial Approval Rate : Perbandingan jumlah aplikasi pengajuan kredit yang

diapprove (diterima oleh PT.Bank X) awal dari total aplikasi yang masuk. Dari report di atas dapat dilihat bahwa pada tanggal 18 April 2005, initial approval ratenya adalah 89.69 %.

22

L23 •

Final Approval Rate : Perbandingan jumlah aplikasi yang diapprove setelah

melewati proses scoring lebih lanjutdari total aplikasi yang masuk. Dari report di atas dapat dilihat bahwa pada tanggal 18 April 2005, final approval ratenya adalah 53.33 %. •

Number of SPPK : Jumlah SPPK (Surat Penawaran Pangjuan Kredit) yang berhasil

dihasilkan hingga saat itu. Dari report di atas dapat dilihat bahwa pada tanggal 18 April 2005, jumlah SPPK yang berhasil dihasilkan adalah 459. •

SPPK Amount : Jumlah dana yang dicantumkan dalam SPPK (Surat Penawaran

Pangajuan Kredit) yang berhasil dihasilkan hingga saat itu. Dari report di atas dapat dilihat bahwa jumlah dana yang yang tercantum dalam SPPK hingga tanggal 18 April 2005 adalah Rp.37.635.000.000 Keterangan : -

Indicator : Tolak ukur yang dijadikan patokan dari tingkat kinerja.

-

Current Date : Hari pada saat pembuatan report tersebut.

-

Previous Day : Hari sebelum pembuatan report tersebut.

-

MTD : Month To Date (dari tanggal diawal bulan hingga tanggal pada saat report tersebut dibuat, misalnya hari pembuatan report adalah tanggal 15 M aret 2008, maka M TD nya adalah dari tanggal 1 M aret 2008 hingga 15 M aret 2008).

-

Same date, prior month : Tanggal yang sama pada bulan sebelumnya.

-

MTD as of last day prior month : Dari tanggal diawal bulan hingga tanggal pada saat report tersebut dibuat satu bulan sebelumnya (misalnya : report dibuat tanggal 15 M aret 2008, maka MTD as of last day prior month adalah tanggal 1 Febuari 2008 – 15 Febuari 2008).

-

YTD : Year To Date (dari tanggal 1 januari hingga tanggal pada saat report tersebut dibuat pada tahun tersebut, misalnya report dibuat tanggal 15 M aret 2008, maka YTD adalah dari tanggal 1 januari 2008 hingga 15 M aret 2008). 23

L24

24

L25

4. Loan Line Analysis Report

L22

Gambar 4. Loan Line Analysis Report 25

L26 Penjelasan Loan Line Analysis Report : 1. Product : Jenis pinjaman, misalnya : Housing Loan 2. Company : Nama developer yang melakukan pinjaman housing loan 3.

Total Guarantor Line- Opening Balance : Jumlah limit dana yang bisa dipinjam developer tersebut. Jumlah limit ini tergantung dari proses scoring terhadap developer tersebut, semakin besar dan telah punya nama baik maka semakin besar pula dana yang dapat diberikan pada developer tersebut.

4.

Realized : Jumlah dana yang disetujui Bank mandiri untuk dicairkan

5.

Total Amount Disbursed/Released : Jumlah dana yang telah dicairkan

6.

No of Applications Disbursed : Jumlah aplikasi (SPPK) yang telah dicairkan

7. Remaining Line : Sisa limit dana yang masih bisa dipinjam oleh developer tersebut (Guarantor Line – Realized) 8. Actual Remaining Line : Sisa limit dana yang masih bisa dipinjam oleh developer tersebut (guarantor line – total amount disburse, namun jika developer tersebut telah membayar sebagian dana yang telah dipinjamnya, maka actual remainingnya akan bertambah).

M isalnya : Developer A mendapat guarantor line sebesar Rp.1.000.000.000 dan telah dicairkan sebesar

Rp.700.000.000, maka pada mulanya actual remaining

linenya Rp.300.000.000 kemudian developer tersebut membayar ciclilan sebesar Rp.100.000.000, maka actual remaining linenya bernilai Rp.400.000.000 (actual remaining line awal + cicilan). 26

L27

27

L28 5.

M itrakrya

Loan

Line

Analysis

Amount in Mio Selection Criteria : M AY-08 Main Company Name XXX

XXX

Sub Compan y Name

Total Applications Approved

Remaining Line

Total Amount Disbursed

No of Applications Disbursed

Actual Remaining Line

Kolektibilit as

361,000,000

10

639,000,000

131,000,000

5

1,000,000,000

Total:

1,000,000,000

361,000,000

10

639,000,000

131,000,000

5

1,000,000,000

PT.XYZ1

666,666,666

176,000,000

10

490,666,666

150,000,000

7

666,666,666

Sub T otal

666,666,666

176,000,000

10

490,666,666

150,000,000

7

666,666,666

PT.XYZ 2

333,333,334

126,000,000

5

207,333,334

76,000,000

2

333,333,334

333,333,334

126,000,000

5

207,333,334

76,000,000

2

333,333,334

1,000,000,000

302,000,000

15

226,000,000

9

1,000,000,000

PT.XYZ

150,000,000

115,000,000

5

35,000,000

97,000,000

3

35,000,0000

Sub Total:

150,000,000

115,000,000

5

35,000,000

97,000,000

3

35,000,0000

0.00

Total:

150,000,000

115,000,000

5

35,000,000

97,000,000

3

35,000,0000

0.00

2,125,000,000

103

2,300,000,000 2,300,000,000

2,125,000,000

Current

Baki Debit

1,000,000,000

Total:

XXX

Total Limit Approved

PT.XYZ

Sub Total:

XXX

Total Guarantor line

0.00 0.00

Current

0.00 0.00

Current

0.00 0.00

Current

PT. XXXX

2,300,000,000

2,125,000,000

103

Sub Total:

2,300,000,000

2,125,000,000

103

2,125,000,000

2,125,000,000

103

163,000,000

8

163,000,000

163,000,000

8

200,000,000

163,000,000

8

163,000,000

163,000,000

8

200,000,000

0.00

2,500,000,000

2,288,000,000

111

2,288,000,000

2,288,000,000

111

2,500,000,000

0.00

CV. XXX Sub Total: Total:

200,000,000

200,000,000

1-29

0.00

0.00 0.00

30-59

0.00

Gambar 5. M itrakarya Loan Line Analysis Report L24

L29

L30 Penjelasan Mitrakarya Loan Line Analysis Report: 1. Main Company Name : Nama perusahaan induk 2. Sub Company Name : Nama anak perusahaan 3.

Total Guarantor Line – Open Balanced : Jumlah limit dana yang bisa dipinjam (plafon) perusahaan tersebut. Jumlah limit ini tergantung dari proses scoring terhadap perusahaan tersebut, semakin besar dan telah punya nama baik maka semakin besar pula dana yang dapat diberikan pada perusahaan tersebut.

4. Total Limit Approved : Jumlah dana yang disetujui untuk dicairkan 5. Total Application Approved : Jumlah aplikasi yang telah disetujui 6. Remaining Line : Sisa limit kredit yang masih dapat digunakan 7. Total Amount Disbursed : Jumalah dana yang telah dicairkan 8. No of Application Disbursed : Jumalah aplikasi yang telah dicairkan 9. Actual Remaining Line : Sisa limit dana yang masih bisa dipinjam oleh perusahaan tersebut (guarantor line – total amount disburse), namun jika perusahaan tersebut telah membayar sebagian dana yang telah dipinjamnya, maka actual remainingnya akan bertambah). M isalnya : perusahaan A Rp.1.000.000.000 dan telah dicairkan sebesar

mendapat guarantor line sebesar Rp.700.000.000, maka actual

remaining linenya Rp.300.000.000 kemudian perusahaan menyicil Rp.100.000.000, actual remaining linenya bernilai Rp.400.000.000 (actual remaining line + cicilan). 9. Kolektibilitas : Tingkat kelancaran pembayaran cicilan kredit oleh perusahaan. M isalnya :Current berarti nasabah telah membayar tagihan untuk bulan depan, 1-29 hari termasuk kredit lancar. 30-59 hari berarti tergolong kredit tidak lancar. 10. Baki debit : Sisa tagihan yang masih harus dibayar oleh perusahaan tersebut kepada PT.Bank X.

L31 Lampiran 3 Lampiran Coding ¾

TABLE P_ACCT_DTL_ENH CREATE TABLE P_ACCT_DTL_ENH( ACCT_NO NUMBER(19) NOT CUST_NO NUMBER(19) NOT LOS_APPLN_NO VARCHAR2(20 BYTE) NOT FACILITY_CD VARCHAR2(4 BYTE), FACILITY_SEQ NUMBER(10), SALES_EXEC_ID VARCHAR2(12 BYTE), APPLIED_LOAN_AMT NUMBER(17,2), SPPK_LOAN_AMT NUMBER(17,2), HOST_APPLN_NO VARCHAR2(20 BYTE) NOT ACCT_OPEN_DATE DATE, VINTAGE NUMBER(6), PRG_CODE VARCHAR2(10 BYTE), EMPLOYMENT_TYPE VARCHAR2(6 BYTE), OCC_CD VARCHAR2(5 BYTE), REGION_CD VARCHAR2(6 BYTE), BRANCH_CD VARCHAR2(5 BYTE), HUB_CD VARCHAR2(10 BYTE), DEVELOPER_CD VARCHAR2(10 BYTE), CHANNEL_CD VARCHAR2(6 BYTE), POSTAL_CD VARCHAR2(9 BYTE), DOB DATE, TOTAL_INCOME NUMBER(20,2), FINAL_APPLN_SCORE NUMBER(8,2), GENDER_CD VARCHAR2(1 BYTE), LOAN_TYPE VARCHAR2(10 BYTE), ORIG_BAL NUMBER(17,2), LOAN_TENURE NUMBER(4), ORIG_LOAN_DATE DATE, PAYMENT_FREQUENCY NUMBER(3), ACCT_STATUS1 NUMBER(2), ACCT_SUB_STATUS1 NUMBER(2), LTD_RELEASE_AMT1 NUMBER(17,2), CURR_BAL1 NUMBER(17,2), YTD_INT_AMT1 NUMBER(22,2), YTD_PRIN_AMT1 NUMBER(22,2), INST_AMT1 NUMBER(17,2), CHARGEOFF_DATE1 DATE, PAYMENT_AMT1 NUMBER(17,2), INT_RATE1 NUMBER(6,2), AMT_DPD1 NUMBER(5), INT_DPD1 NUMBER(5), CREATED_USER VARCHAR2(12 BYTE), CREATED_DTM DATE, PROCESS_SEQ_NO NUMBER(12), PREV_DAY_RELEASE_AMT NUMBER(22,2), COMPANY_CD VARCHAR2(10 BYTE), MONTH_IDX VARCHAR(2) NOT NULL, COMBRANCH_CODE VARCHAR2(10 BYTE), DISBMT_END_DATE DATE ) PARTITION BY RANGE (MONTH_IDX) SUBPARTITION BY HASH SUBPARTITIONS 4 ( PARTITION P_JAN VALUES LESS THAN ('02'), PARTITION P_FEB VALUES LESS THAN ('03'), PARTITION P_MAR VALUES LESS THAN ('04'), PARTITION P_APR VALUES LESS THAN ('05'), PARTITION P_MAY VALUES LESS THAN ('06'), PARTITION P_JUN VALUES LESS THAN ('07'), PARTITION P_JUL VALUES LESS THAN ('08'), PARTITION P_AUG VALUES LESS THAN ('09'), PARTITION P_SEP VALUES LESS THAN ('10'), PARTITION P_OCT VALUES LESS THAN ('11'), PARTITION P_NOV VALUES LESS THAN ('12'), PARTITION P_DEC VALUES LESS THAN ('13'));

NULL, NULL, NULL,

NULL,

(ACCT_NO)

CREATE INDEX BMCLN2.IDX_LOS_APPLN_NO_P_ACCT_DTL ON BMCLN2.P_ACCT_DTL_ENH (LOS_APPLN_NO); CREATE UNIQUE INDEX PK_P_ACCT_DTL_ENH ON P_ACCT_DTL_ENH (ACCT_NO,MONTH_IDX); ALTER TABLE P_ACCT_DTL_ENH ADD CONSTRAINT PK_P_ACCT_DTL_ENH PRIMARY KEY(ACCT_NO,MONTH_IDX); CREATE INDEX BMCLN2.IDX_PRG_CODE_P_ACCT_DTL ON BMCLN2.P_ACCT_DTL_ENH(PRG_CODE); CREATE INDEX BMCLN2.IDX_ACCT_SUB_STATUS_P_ACCT_DTL ON BMCLN2.P_ACCT_DTL_ENH (ACCT_SUB_STATUS1); CREATE INDEX BMCLN2.IDX_MNTH_IDX ON BMCLN2.P_ACCT_DTL_ENH (MONTH_IDX); CREATE INDEX IDX_REGION_CD ON P_ACCT_DTL_ENH(REGION_CD); CREATE INDEX BMCLN2.IDX_ACCT_STATUS1_P_ACCT_DTL ON BMCLN2.P_ACCT_DTL_ENH(ACCT_STATUS1); CREATE PUBLIC SYNONYM P_ACCT_DTL_ENH FOR P_ACCT_DTL_ENH;

L32 ¾

TABLE D_CUST_PROMPT_DTL CREATE TABLE D_CUST_PROMPT_DTL ( COLD_CUST_NO VARCHAR2(18 BYTE) CUST_NAME VARCHAR2(95 BYTE), HOME_ADDRESS VARCHAR2(95 BYTE), AGE_OF_PENSION NUMBER(10), SALES_EXEC_ID VARCHAR2(20 BYTE), REGION_CD VARCHAR2(10 BYTE), AREA_ID VARCHAR2(10 BYTE), BRANCH_NO VARCHAR2(11 BYTE), BUSSINES_TYPE VARCHAR2(5 BYTE), CAPIGN VARCHAR2(5 BYTE), CHANNEL_CODE VARCHAR2(5 BYTE), COMP_NAME VARCHAR2(30 BYTE), COMP_CODE VARCHAR2(10 BYTE), COMP_TYPE_CODE VARCHAR2(10 BYTE), DOB DATE, EDU_CD VARCHAR2(10 BYTE), HOME_CITY VARCHAR2(30 BYTE), LONG_OF_STAY NUMBER(10), HOME_OWNSHIP_CD VARCHAR2(10 BYTE), HOME_PHONE_NO VARCHAR2(20 BYTE), POSTAL_CD VARCHAR2(7 BYTE), INCOME NUMBER(38,2), PRE_APPR_STATUS_CHG_DATE DATE, JOB_TYPE VARCHAR2(10 BYTE), JOIN_INCOME_FLAG VARCHAR2(1 BYTE), KTP_CITY_CD VARCHAR2(30 BYTE), KTP_EXP_DATE DATE, KTP_POSTAL_CODE VARCHAR2(7 BYTE), ASSOSIATE_MANDIRI NUMBER(10), LIFE_OF_SERVICES NUMBER(10), MARITAL_CODE VARCHAR2(10 BYTE), MOBILEPHONE_NO VARCHAR2(12 BYTE), NO_OF_CHILD NUMBER(10), CUST_OF_PHONENO VARCHAR2(20 BYTE), OFFICER_ID VARCHAR2(10 BYTE), OFFICER_NAME VARCHAR2(50 BYTE), TOTAL_INCOME NUMBER(38,2), YEAR_OF_SERVICE NUMBER(10), OTHER_INCOME NUMBER(38,2), POB VARCHAR2(30 BYTE), REJ_REASON_CD VARCHAR2(1 BYTE), SEX VARCHAR2(1 BYTE), SP_INCOME NUMBER(38,2), SP_JOB_CD VARCHAR2(30 BYTE), SP_JOB_TYPE VARCHAR2(5 BYTE), SP_OF_NAME VARCHAR2(40 BYTE), CU_SP_OF_PHONENO VARCHAR2(20 BYTE), SPOUSE_OTHER_INCOME NUMBER(38,2), USER_ID VARCHAR2(20 BYTE), CU_INPUTDATE DATE, WARM_CUST_ID VARCHAR2(20 BYTE), RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12) );

NOT NULL,

DEFAULT DEFAULT DEFAULT DEFAULT

UPPER(USER) SYSDATE UPPER(USER) SYSDATE

NOT NOT NOT NOT

NULL, NULL, NULL, NULL,

CREATE UNIQUE INDEX PK_D_CUST_PROMPT_DTL ON D_CUST_PROMPT_DTL(WARM_CUST_ID); CREATE PUBLIC SYNONYM D_CUST_PROMPT_DTL FOR D_CUST_PROMPT_DTL; ALTER TABLE D_CUST_PROMPT_DTL ADD ( CONSTRAINT PK_D_CUST_PROMPT_DTL PRIMARY KEY(WARM_CUST_ID));

L33 ¾ TABLE D_APPLN_HOST_DTL CREATE TABLE D_APPLN_HOST_DTL( HOST_APPLN_NO VARCHAR2(20 BYTE) LOS_APPLN_NO VARCHAR2(20 BYTE), FACILITY_CD VARCHAR2(4 BYTE) FACILITY_SEQ NUMBER(10) APPLIED_AMT NUMBER(17,2), APPROVED_AMT NUMBER(22,2), ENTRY_DATE DATE, APPLN_DATE DATE, FINAL_PMT_AMT NUMBER(17,2), TERM NUMBER(5), TERM_CD VARCHAR2(1 BYTE), LOAN_PURPOSE VARCHAR2(5 BYTE), OFFER_DATE DATE, APPROVED_DATE DATE, BU_APPROVAL_BY VARCHAR2(10 BYTE), CRM_APPROVAL_BY VARCHAR2(10 BYTE), OFFICER_CD VARCHAR2(10 BYTE), LIMIT_STATUS VARCHAR2(2 BYTE), FACILITY_STATUS VARCHAR2(1 BYTE), MOD_INT VARCHAR2(1 BYTE), INT_RATE NUMBER(11,9), PRIM_RATE NUMBER(5), PRIM_RATE_FL NUMBER(11,9), PRIM_RATE_CL NUMBER(11,9), CURR_TYPE VARCHAR2(20 BYTE), FACILITY_CIF_NO NUMBER(19), DEC_SENT_DATE DATE, LAST_CHG_DT DATE, BU_APPROVAL_DATE DATE, CRM_APPROVAL_DATE DATE, OFFER_ACCEPT_DATE DATE, BRANCH_NO NUMBER(5), PMT_AMT NUMBER(17,2), APPROVAL_DATE DATE, APPROVED_BY VARCHAR2(10 BYTE), ORG_AMT NUMBER(17,2), OS_BAL NUMBER(17,2), PROD_TYPE VARCHAR2(10 BYTE), SP3K_NO VARCHAR2(19 BYTE), PROV_BANK NUMBER(17,2), NOTARY_FEE NUMBER(17,2), APPRAISAL_FEE NUMBER(17,2), SKMHT_FEE NUMBER(17,2), LIFE_INS_FEE NUMBER(17,2), FIRST_MTH_PMT NUMBER(17,2), PRA_REALISASI_FLAG VARCHAR2(1 BYTE), INSURANCE_CD VARCHAR2(10 BYTE), FIRE_INSURANCE_CD VARCHAR2(10 BYTE), ADMINISTRATION_FEE NUMBER(17,2), AUTO_INS_FEE NUMBER(17,2), STAMP_FEE NUMBER(17,2), CUST_GIRO NUMBER(19), BLOCK_ACC_BAL NUMBER(17,2), DOWN_PMT NUMBER(17,2), AUTO_INS_CD NUMBER(10), HOME_INS_FEE NUMBER(17,2), OLD_CIF_OTH NUMBER(19), OLD_CIF_ACC_NO NUMBER(19), ADMIN_FEE_DEBT NUMBER(17,2), SP2D_PRNT_DEBT NUMBER(6), POSTING_DATE DATE, TRANS_AMT NUMBER(17,2), CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12))

NOT NULL, NOT NULL, NOT NULL,

RECORD_TO_DATE DEFAULT UPPER(USER) DEFAULT SYSDATE DEFAULT UPPER(USER) DEFAULT SYSDATE

DATE, NOT NOT NOT NOT


PARTITION BY HASH(HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ) PARTITIONS 8; CREATE INDEX IDX_RECORD_TO_DATE_APPLN_HOST ON D_APPLN_HOST_DTL(RECORD_TO_DATE); CREATE INDEX BMCLN2.IDX_LOS_APPLN_NO_APPLN_HOST ON BMCLN2.D_APPLN_HOST_DTL (LOS_APPLN_NO); CREATE UNIQUE INDEX PK_D_LOAN_APPLN_FACILITY_ENH ON D_APPLN_HOST_DTL(HOST_APPLN_NO,FACILITY_CD,FACILITY_SEQ); CREATE PUBLIC SYNONYM D_APPLN_HOST_DTL FOR D_APPLN_HOST_DTL; ALTER TABLE D_APPLN_HOST_DTL ADD ( CONSTRAINT PK_D_LOAN_APPLN_FACILITY_ENH PRIMARY KEY (HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ));

L34 TABLE D_APPLN_LOS_DTL CREATE TABLE D_APPLN_LOS_DTL( SALES_EXEC_ID VARCHAR2(10 BYTE), APPRAISAL_END_DATE DATE, APPRAISAL_START_DATE DATE, BI_CHECK_DATE DATE, DISBMT_END_DATE DATE, DOC_COLL_SPPK_END_DATE DATE, APPR_END_DATE DATE, FINAL_APPLN_STATUS VARCHAR2(20 BYTE), DTBO_START_DATE DATE, INVST_END_DATE DATE, INVST_START_DATE DATE, APPR_START_DATE DATE, SEND_LD_END_DATE DATE, APPLN_RCVD_DATE DATE, REGIONAL_ID VARCHAR2(10 BYTE), APPLN_NO VARCHAR2(20 BYTE) NOT NULL, FS_LS_END_DATE DATE, SEND_LD_START_DATE DATE, DOC_COLL_SPPK_START_DATE DATE, SPPK_END_DATE DATE, DTBO_END_DATE DATE, VER_ASG_DATE DATE, VERFN_END_DATE DATE, VERFN_START_DATE DATE, BRANCH_CD VARCHAR2(5 BYTE), CHANNEL_CODE VARCHAR2(3 BYTE), COMPANY_CD VARCHAR2(10 BYTE), CUST_NO VARCHAR2(20 BYTE), REJ_REASON_CD VARCHAR2(10 BYTE), BRANCH_NO VARCHAR2(10 BYTE), AGENCY_CD VARCHAR2(10 BYTE), AO_CD VARCHAR2(20 BYTE), APPLN_ENTRY_DATE DATE, APPRAISAL_FLAG VARCHAR2(1 BYTE), APPLN_REMARK VARCHAR2(4000 BYTE), APPROVAL_BY VARCHAR2(20 BYTE), APPROVAL_DATE DATE, APROVAL_DEV_FLAG VARCHAR2(1 BYTE), APROVAL_DEV_BY VARCHAR2(20 BYTE), BI_CHECKING VARCHAR2(5 BYTE), BI_STA VARCHAR2(10 BYTE), BOOKING_BY VARCHAR2(20 BYTE), BOOKING_STATUS VARCHAR2(1 BYTE), CONFIRM_BOOKING VARCHAR2(1 BYTE), DATA_ENTRY_BY VARCHAR2(20 BYTE), INV_ENTRY_DATE DATE, LAST_TRACK VARCHAR2(20 BYTE), LAST_TRACK_BY VARCHAR2(20 BYTE), LAST_TRACK_DATE DATE, NAIK_ATAS VARCHAR2(1 BYTE), NAK_BY VARCHAR2(20 BYTE), NOTARY_RCVD_DATE DATE, NOTARY_SEND_DATE DATE, OVERRIDE_REASON_CD VARCHAR2(10 BYTE), ORG_MARKT_NAME VARCHAR2(50 BYTE), ORG_OFR_NAME VARCHAR2(80 BYTE), OVERRIDE_STATUS VARCHAR2(1 BYTE), OVERRIDE_BY VARCHAR2(20 BYTE), OVERRIDE_DATE DATE, PROSPECT_ID VARCHAR2(20 BYTE), RCVD_BY_LD_BY VARCHAR2(20 BYTE), SCORING_BY VARCHAR2(20 BYTE), SPPK_BY VARCHAR2(20 BYTE), SPPK_CONFIRM_BY VARCHAR2(20 BYTE), TBO_BY VARCHAR2(20 BYTE), TBO_COPY_BY VARCHAR2(20 BYTE), TBO_COPY_DATE DATE, VER_ENTRY_DATE DATE, VER_FLAG VARCHAR2(1 BYTE), AREA_ID VARCHAR2(10 BYTE), MEDIA_ID VARCHAR2(4 BYTE), PRG_CODE VARCHAR2(20 BYTE), SOURCE_PROSPECT VARCHAR2(1 BYTE), BLACKLIST_STATUS VARCHAR2(1 BYTE), VVIP_FLAG VARCHAR2(1 BYTE), REL_BANK_MANDIRI NUMBER(10), DATE_ENTRY_DATE DATE, INV_DATE DATE,

L35 NOTARY_DATE REJECT_BY REJECT_DATE REJECT_LAST_TRACK_BY REJECT_LAST_TRACK_DATE SCORING_FLAG SCORING_BY1 SCORING_DATE SIGN_DATE BANDING_STATUS TBO_ORG_DATE UNDO_LEGAL_DATE VER_DATE VER_OFFICER RECORD_TO_DATE CREATED_USER CREATED_DTM UPDATED_USER UPDATED_DTM PROCESS_SEQ_NO COMBRANCH_CODE AGENT_OFR_NAME AGENCY_AGENT_NAME

DATE, VARCHAR2(20 BYTE), DATE, VARCHAR2(20 BYTE), DATE, VARCHAR2(1 BYTE), VARCHAR2(25 BYTE), DATE, DATE, VARCHAR2(1 BYTE), DATE, DATE, DATE, VARCHAR2(20 BYTE), DATE, VARCHAR2(12 BYTE) DATE VARCHAR2(12 BYTE) DATE NUMBER(12), VARCHAR2(10 BYTE), VARCHAR2(50 BYTE), VARCHAR2(50 BYTE))



NOT NOT NOT NOT


PARTITION BY HASH(APPLN_NO)PARTITIONS 8; CREATE UNIQUE INDEX PK_D_APPLN_LOS_DTL ON D_APPLN_LOS_DTL(APPLN_NO); CREATE INDEX IDX_PROSPECT_ID_APPLN_LOS ON D_APPLN_LOS_DTL(PROSPECT_ID); CREATE INDEX BMCLN2.IDX_PRG_CODE ON BMCLN2.D_APPLN_LOS_DTL(PRG_CODE); CREATE INDEX BMCLN2.IDX_SPPK_END_DATE_APPLN_LOS ON BMCLN2.D_APPLN_LOS_DTL(SPPK_END_DATE); CREATE INDEX BMCLN2.IDX_APPROVAL_DATE_APPLN_LOS ON BMCLN2.D_APPLN_LOS_DTL(APPROVAL_DATE); CREATE INDEX BMCLN2.IDX_REGIONAL_ID ON BMCLN2.D_APPLN_LOS_DTL(REGIONAL_ID); CREATE INDEX BMCLN2.IDX_DISBMT_END_DATE_APPLN_LOS ON BMCLN2.D_APPLN_LOS_DTL(DISBMT_END_DATE); CREATE INDEX BMCLN2.IDX_BRANCH_CD ON BMCLN2.D_APPLN_LOS_DTL(BRANCH_CD); CREATE INDEX IDX_DOC_SPPK_START_APPLN_LOS ON D_APPLN_LOS_DTL(DOC_COLL_SPPK_START_DATE); CREATE PUBLIC SYNONYM D_APPLN_LOS_DTL FOR D_APPLN_LOS_DTL; ALTER TABLE D_APPLN_LOS_DTL ADD ( CONSTRAINT PK_D_APPLN_LOS_DTL PRIMARY KEY(APPLN_NO));

¾ TABLE D_FACILITY_LOS_DTL CREATE TABLE D_FACILITY_LOS_DTL( APPLN_NO VARCHAR2(20 BYTE) INT_RATE NUMBER(22,2), SPPK_LOAN_AMT NUMBER(22,2), FINAL_LOAN_TERM NUMBER(10), DISBMT_START_DATE DATE, APPLIED_LOAN_AMT NUMBER(22,2), APPLIED_LOAN_TERM NUMBER(10), PROD_PRG_CD VARCHAR2(8 BYTE), APPROVE_FLAG VARCHAR2(1 BYTE), INSTALLMENT_AMT NUMBER(22,2), INT_TYPE VARCHAR2(2 BYTE), INSR_CASH_AMT NUMBER(17,2), INSR_CASH_PERCENT NUMBER(17,2), INSR_TYPE NUMBER(10), APPLIED_INT NUMBER(22,2), APPLIED_INT_TYPE VARCHAR2(2 BYTE), PK_DATE DATE, PK_NO VARCHAR2(50 BYTE), SPPK_DATE DATE, LOAN_PURPOSE VARCHAR2(10 BYTE), SPPK_NO VARCHAR2(40 BYTE), DSR NUMBER(17,2), LTV NUMBER(17,2), RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12), TOP_UP_FLAG VARCHAR2(2 BYTE), ACCT_NO VARCHAR2(100 BYTE), CURR_BAL NUMBER(17,2), APPL_ADD_LIMIT NUMBER(17,2), DECS_ADD_LIMIT NUMBER(17,2), CARD_BUILDING_FLAG VARCHAR2(2 BYTE) )

NOT NULL,



NOT NOT NOT NOT


PARTITION BY HASH(APPLN_NO)PARTITIONS 8; CREATE INDEX BMCLN2.IDX_DISBMT_START_DATE ON BMCLN2.D_FACILITY_LOS_DTL(DISBMT_START_DATE); CREATE UNIQUE INDEX D_FACILITY_LOS_DTL ON D_FACILITY_LOS_DTL(APPLN_NO); CREATE PUBLIC SYNONYM D_FACILITY_LOS_DTL FOR D_FACILITY_LOS_DTL; ALTER TABLE D_FACILITY_LOS_DTL ADD (CONSTRAINT D_FACILITY_LOS_DTL PRIMARY KEY(APPLN_NO));

L36 ¾ TABLE D_CUST_SUMMARY CREATE TABLE D_CUST_SUMMARY( APPLN_NO SCORE INCOME SUMMARY_LIMIT_CREDIT SUMMARY_LIMIT_INSTALLMENT SUMMARY_LIMIT_TENOR SUMMARY_LOAN_AMOUNT PROD_PRG_CD COLLTRL_VALUE SUMMARY_MAX_INSTALLMENT FINAL_APPLN_SCORE SCORING_PROCESS_DATE SCORING_LIMIT_CREDIT SCORING_LIMIT_TENOR SCORING_FINISH_STATUS RECORD_TO_DATE CREATED_USER CREATED_DTM UPDATED_USER UPDATED_DTM PROCESS_SEQ_NO );

VARCHAR2(20 BYTE) VARCHAR2(30 BYTE), NUMBER, NUMBER, NUMBER, VARCHAR2(10 BYTE), NUMBER, VARCHAR2(8 BYTE), NUMBER, NUMBER, NUMBER, DATE, NUMBER, VARCHAR2(5 BYTE), VARCHAR2(1 BYTE), DATE, VARCHAR2(12 BYTE) DATE VARCHAR2(12 BYTE) DATE NUMBER

NOT NULL,



NOT NOT NOT NOT


CREATE UNIQUE INDEX PK_D_CUST_SUMMARY ON D_CUST_SUMMARY(APPLN_NO); CREATE PUBLIC SYNONYM D_CUST_SUMMARY FOR D_CUST_SUMMARY; ALTER TABLE D_CUST_SUMMARY ADD(CONSTRAINT PK_D_CUST_SUMMARY PRIMARY KEY(APPLN_NO));

¾ TABLE D_COLLATERAL_DTL_ENH CREATE TABLE D_COLLATERAL_DTL_ENH( COLLTRL_ID NUMBER(10) COLLTRL_DESC VARCHAR2(50 BYTE), COLLTRL_SUB_TYPE VARCHAR2(1 BYTE), CAR_MODEL VARCHAR2(60 BYTE), CAR_MAKER VARCHAR2(10 BYTE), LOS_APPLN_NO VARCHAR2(20 BYTE), VEHICLE_CATEGORY VARCHAR2(20 BYTE), CUST_REF_NO VARCHAR2(20 BYTE) DEVELOPER_CDG VARCHAR2(10 BYTE), AREA_ID VARCHAR2(10 BYTE), ATAS_NAMA VARCHAR2(30 BYTE), BPKB_DATE DATE, BPKBNUM VARCHAR2(30 BYTE), BUILDINGAREA NUMBER(17,2), CERTIFICATE_DATE DATE, CERTIFICATE_DUEDATE DATE, CERTIFICATE_STATUS VARCHAR2(20 BYTE), CERTIFICATE_TYPE VARCHAR2(20 BYTE), IMB_NO VARCHAR2(50 BYTE), AFT_SAFETY_MARGIN_VAL NUMBER(17,2), MADE_DATE VARCHAR2(4 BYTE), DEALER_ID VARCHAR2(10 BYTE), DEV_CODE VARCHAR2(10 BYTE), ENGINE_NO VARCHAR2(20 BYTE), LAND_AREA NUMBER(17,2), LOKASI_AGUNAN VARCHAR2(10 BYTE), MERK VARCHAR2(20 BYTE), NOMINAL_VALUE NUMBER(22,2), OFFERED_VALUE NUMBER(17,2), CLTRL_ADDRESS VARCHAR2(240 BYTE), PK_KECAMATAN VARCHAR2(50 BYTE), PK_KELURAHAN VARCHAR2(50 BYTE), PROP_CODE VARCHAR2(50 BYTE), PROP_TYPE VARCHAR2(10 BYTE), APPR_VALUE NUMBER(17,2), RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12) );

NOT NULL,

NOT NULL,



NOT NOT NOT NOT


CREATE UNIQUE INDEX PK_D_COLLTRL_DTL_ENH ON D_COLLATERAL_DTL_ENH (COLLTRL_ID, CUST_REF_NO); CREATE PUBLIC SYNONYM D_COLLATERAL_DTL_ENH FOR D_COLLATERAL_DTL_ENH; ALTER TABLE D_COLLATERAL_DTL_ENH ADD(CONSTRAINT PK_D_COLLTRL_DTL_ENH PRIMARY KEY(COLLTRL_ID, CUST_REF_NO));

L37 ¾ TABLE D_PROYEK_HOUSING CREATE TABLE D_PROYEK_HOUSING ( PROYEK_ID VARCHAR2(10 BYTE) DEVELOPER_ID VARCHAR2(10 BYTE), PROYEK_DESCRIPTION VARCHAR2(100 BYTE), DEVELOPER_CITY VARCHAR2(40 BYTE), KERJASAMA_FLAG VARCHAR2(1 BYTE), DEVELOPER_NAME VARCHAR2(100 BYTE), ID_KOTA VARCHAR2(5 BYTE), ID_LOKASI VARCHAR2(5 BYTE), PROYEK_GRLINE NUMBER(22,2), PROYEK_PLAFOND NUMBER(17,2), PROYEK_SRCCODE VARCHAR2(10 BYTE), DANAU VARCHAR2(10 BYTE), NUMBER_PARAMETER NUMBER(10), OLAH_RAGA VARCHAR2(10 BYTE), PUSAT_BELANJA VARCHAR2(10 BYTE), REMARK VARCHAR2(250 BYTE), RUMAH_SAKIT VARCHAR2(10 BYTE), SEKOLAH VARCHAR2(10 BYTE), TAMAN VARCHAR2(10 BYTE), RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12) );

NOT NULL,



NOT NOT NOT NOT


CREATE UNIQUE INDEX PK_D_PROYEK_HOUSING ON D_PROYEK_HOUSING(PROYEK_ID); CREATE PUBLIC SYNONYM D_PROYEK_HOUSING FOR D_PROYEK_HOUSING; ALTER TABLE D_PROYEK_HOUSING ADD ( CONSTRAINT PK_D_PROYEK_HOUSING PRIMARY KEY(PROYEK_ID));

¾ TABLE D_DEVELOPER_DTL CREATE TABLE D_DEVELOPER_DTL ( DEVELOPER_CODE VARCHAR2(10 BYTE) CD_SIBS VARCHAR2(10 BYTE), CITY_ID VARCHAR2(10 BYTE), DEVELOPER_ADDR VARCHAR2(90 BYTE), DEVELOPER_CITY VARCHAR2(40 BYTE), DEVELOPER_FAX_NO VARCHAR2(14 BYTE), DEVELOPER_GRLINE NUMBER(22,2), DEVELOPER_KERJASAMA VARCHAR2(1 BYTE), DEVELOPER_NAME VARCHAR2(100 BYTE), DEVELOPER_PHONE_NO VARCHAR2(14 BYTE), DEVELOPER_PHONE_EXT VARCHAR2(5 BYTE), DEVELOPER_SRCCODE VARCHAR2(10 BYTE), DEVELOPER_TOTGRLINE NUMBER(17,2), DEVELOPER_ZIPCODE VARCHAR2(5 BYTE), DEVELOPER_INTSUB NUMBER(17,2), DEVELOPER_BLOCKED VARCHAR2(1 BYTE), DEVELOPER_EXPDATE DATE, RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12) );

NOT NULL,



NOT NOT NOT NOT


CREATE UNIQUE INDEX PK_D_DEVELOPER_DTL ON D_DEVELOPER_DTL (DEVELOPER_CODE); CREATE PUBLIC SYNONYM D_DEVELOPER_DTL FOR D_DEVELOPER_DTL; ALTER TABLE D_DEVELOPER_DTL ADD ( CONSTRAINT PK_D_DEVELOPER_DTL PRIMARY KEY (DEVELOPER_CODE));

L38 ¾ TABLE D_COMPANY_CREDIT_DTL_ENH CREATE TABLE D_COMPANY_CREDIT_DTL_ENH ( COMPANY_TYPE VARCHAR2(100 BYTE), COMPANY_CD VARCHAR2(5 BYTE) COMPANY_NAME VARCHAR2(100 BYTE), GUARANTOR_LINE NUMBER(22,2), LINE_REALISED NUMBER(17,2), REMAIN_GUARANTOR_LINE NUMBER(17,2), EXPIRY_DATE DATE, MAIN_COMP_CD VARCHAR2(5 BYTE), BRANCH_NO VARCHAR2(10 BYTE), COMP_BLOCKED VARCHAR2(1 BYTE), DEBT_IND_LIMIT NUMBER(17,2), DEBT_IND_TENOR NUMBER(10), COMP_SRC_CODE VARCHAR2(16 BYTE), COMP_RATING_CODE VARCHAR2(10 BYTE), RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12) );

NOT NULL,



NOT NOT NOT NOT


CREATE UNIQUE INDEX PK_D_COMPANY_CREDIT_DTL_ENH ON D_COMPANY_CREDIT_DTL_ENH(COMPANY_CD); CREATE PUBLIC SYNONYM D_COMPANY_CREDIT_DTL_ENH FOR D_COMPANY_CREDIT_DTL_ENH; ALTER TABLE D_COMPANY_CREDIT_DTL_ENH ADD ( CONSTRAINT PK_D_COMPANY_CREDIT_DTL_ENH PRIMARY KEY (COMPANY_CD));

¾ TABLE D_GL_DTL_ENH CREATE TABLE D_GL_DTL_ENH ( PERIOD DATE, IDENTIFIER NUMBER(10), CURR_BAL NUMBER(19,2), ACCTNO NUMBER(19), MTDBAL NUMBER(19,2), CENTER NUMBER(5), RECORD_TO_DATE DATE, CREATED_USER VARCHAR2(12 BYTE) CREATED_DTM DATE UPDATED_USER VARCHAR2(12 BYTE) UPDATED_DTM DATE PROCESS_SEQ_NO NUMBER(12) );



NOT NOT NOT NOT


CREATE UNIQUE INDEX PK_D_GL_DTL ON D_GL_DTL_ENH(PERIOD, IDENTIFIER); CREATE PUBLIC SYNONYM D_GL_DTL_ENH FOR D_GL_DTL_ENH; ALTER TABLE D_GL_DTL_ENH ADD (CONSTRAINT PK_D_GL_DTL PRIMARY KEY (PERIOD, IDENTIFIER));

L39 ¾

FACT TABLE VW_MIS_KPI1_ENH CREATE TABLE VW_MIS_KPI1_ENH( COUNTRY_CD VARCHAR2(2 BYTE) NOT NULL, MIS_ID VARCHAR2(6 BYTE) NOT NULL, MIS_DATE DATE NOT NULL, TYPE VARCHAR2(10 BYTE) NOT NULL, REGION_CD VARCHAR2(5 BYTE) NOT NULL, MIS_SEQ_CD NUMBER NOT NULL, CUR_MIS_NVAL NUMBER, CUR_MIS_DVAL NUMBER, P1D_MIS_NVAL NUMBER, P1D_MIS_DVAL NUMBER, MTD_MIS_NVAL NUMBER, MTD_MIS_DVAL NUMBER, SDP_MIS_NVAL NUMBER, SDP_MIS_DVAL NUMBER, LDP_MIS_NVAL NUMBER, LDP_MIS_DVAL NUMBER, YTD_MIS_NVAL NUMBER, YTD_MIS_DVAL NUMBER, PROCESS_SEQ_NO NUMBER(12), BRANCH_CD VARCHAR2(5 BYTE) NOT NULL ) PARTITION BY RANGE (MIS_DATE) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))( PARTITION P_BEFORE_JAN07 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')), PARTITION P_JAN07 VALUES LESS THAN (TO_DATE('01-FEB-2007','DD-MON-YYYY')), PARTITION P_FEB07 VALUES LESS THAN (TO_DATE('01-MAR-2007','DD-MON-YYYY')), PARTITION P_MAR07 VALUES LESS THAN (TO_DATE('01-APR-2007','DD-MON-YYYY')), PARTITION P_APR07 VALUES LESS THAN (TO_DATE('01-MAY-2007','DD-MON-YYYY')), PARTITION P_MAY07 VALUES LESS THAN (TO_DATE('01-JUN-2007','DD-MON-YYYY')), PARTITION P_JUN07 VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')), PARTITION P_JUL07 VALUES LESS THAN (TO_DATE('01-AUG-2007','DD-MON-YYYY')), PARTITION P_AUG07 VALUES LESS THAN (TO_DATE('01-SEP-2007','DD-MON-YYYY')), PARTITION P_SEP07 VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')), PARTITION P_OCT07 VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')), PARTITION P_NOV07 VALUES LESS THAN (TO_DATE('01-DEC-2007','DD-MON-YYYY')), PARTITION P_DEC07 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))); CREATE UNIQUE INDEX PK_VW_MIS_KPI1_ENH ON VW_MIS_KPI1_ENH (COUNTRY_CD, MIS_ID, MIS_DATE, TYPE,REGION_CD, MIS_SEQ_CD, BRANCH_CD); ALTER TABLE VW_MIS_KPI1_ENH ADD ( CONSTRAINT PK_VW_MIS_KPI1_ENH PRIMARY KEY (COUNTRY_CD, MIS_ID, MIS_DATE, TYPE, REGION_CD, MIS_SEQ_CD,BRANCH_CD));

¾

FACT TABLE VW_MIS_AVG_TURNAROUND_TIME_ENH CREATE TABLE VW_MIS_AVG_TURNAROUND_TIME_ENH( MIS_ID VARCHAR2(6 BYTE) NOT NULL, MIS_DATE DATE NOT NULL, PROD_CD VARCHAR2(8 BYTE) NOT NULL, PROD_PRG_CD NUMBER(4) NOT NULL, REGION_CD VARCHAR2(5 BYTE) NOT NULL, MIS_SEQ_CD NUMBER(3) NOT NULL, COUNTRY_CD VARCHAR2(2 BYTE), CUR_NVAL NUMBER, CUR_DVAL NUMBER, MTD_MIS_NVAL NUMBER, MTD_MIS_DVAL NUMBER, YTD_MIS_NVAL NUMBER, YTD_MIS_DVAL NUMBER, BRANCH_CDVARCHAR2(5 BYTE) NOT NULL ) PARTITION BY RANGE (MIS_DATE) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))( PARTITION P_BEFORE_JAN07 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')), PARTITION P_JAN07 VALUES LESS THAN (TO_DATE('01-FEB-2007','DD-MON-YYYY')), PARTITION P_FEB07 VALUES LESS THAN (TO_DATE('01-MAR-2007','DD-MON-YYYY')), PARTITION P_MAR07 VALUES LESS THAN (TO_DATE('01-APR-2007','DD-MON-YYYY')), PARTITION P_APR07 VALUES LESS THAN (TO_DATE('01-MAY-2007','DD-MON-YYYY')), PARTITION P_MAY07 VALUES LESS THAN (TO_DATE('01-JUN-2007','DD-MON-YYYY')), PARTITION P_JUN07 VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')), PARTITION P_JUL07 VALUES LESS THAN (TO_DATE('01-AUG-2007','DD-MON-YYYY')), PARTITION P_AUG07 VALUES LESS THAN (TO_DATE('01-SEP-2007','DD-MON-YYYY')), PARTITION P_SEP07 VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')), PARTITION P_OCT07 VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')), PARTITION P_NOV07 VALUES LESS THAN (TO_DATE('01-DEC-2007','DD-MON-YYYY')), PARTITION P_DEC07 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) ); CREATE UNIQUE INDEX PK_VW_MIS_AVG_TA_TIME_ENH ON VW_MIS_AVG_TURNAROUND_TIME_ENH (MIS_ID, MIS_DATE, PROD_CD, PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, BRANCH_CD); ALTER TABLE VW_MIS_AVG_TURNAROUND_TIME_ENH ADD ( CONSTRAINT PK_VW_MIS_AVG_TA_TIME_ENH PRIMARY KEY(MIS_ID, MIS_DATE, PROD_CD, PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, BRANCH_CD));

L40 ¾

FACT TABLE MIS_LOAN_LINE_ANLS_ENH CREATE TABLE MIS_LOAN_LINE_ANLS_ENHCREATE TABLE ( MIS_ID VARCHAR2(6 BYTE) NOT NULL, YEAR_MONTH NUMBER(6) NOT NULL, PROD_CD VARCHAR2(8 BYTE) NOT NULL, PROD_PRG_CD NUMBER(4) NOT NULL, COMPANY_CD VARCHAR2(5 BYTE) NOT NULL, BRANCH_CD VARCHAR2(5 BYTE) NOT NULL, MIS_SEQ_CD NUMBER(3) NOT NULL, COUNTRY_CD VARCHAR2(2 BYTE), MIS_NVALUE NUMBER(18,2), MIS_DVALUE NUMBER(18,2) DEFAULT 0 PROCESS_SEQ_NO NUMBER(12), APPL_NO VARCHAR2(20 BYTE), SUB_COMPANY_NAME VARCHAR2(100 BYTE), MAIN_COMPANY_NAME VARCHAR2(100 BYTE), TOT_APPL_APP NUMBER(17,2), BAKI_DEBIT NUMBER(17,2), DPD_CD NUMBER(3) ) PARTITION BY RANGE (YEAR_MONTH)( PARTITION P_BEFORE_JAN08 VALUES LESS THAN (200801), PARTITION P_JAN08 VALUES LESS THAN (200802), PARTITION P_FEB08 VALUES LESS THAN (200803), PARTITION P_MAR08 VALUES LESS THAN (200804), PARTITION P_APR08 VALUES LESS THAN (200805), PARTITION P_MAY08 VALUES LESS THAN (200806), PARTITION P_JUN08 VALUES LESS THAN (200807), PARTITION P_JUL08 VALUES LESS THAN (200808), PARTITION P_AUG08 VALUES LESS THAN (200809), PARTITION P_SEP08 VALUES LESS THAN (200810), PARTITION P_OCT08 VALUES LESS THAN (200811), PARTITION P_NOV08 VALUES LESS THAN (200812), PARTITION P_DEC08 VALUES LESS THAN (200901), PARTITION P_JAN09 VALUES LESS THAN (200902), PARTITION P_FEB09 VALUES LESS THAN (200903), PARTITION P_MAR09 VALUES LESS THAN (200904), PARTITION P_APR09 VALUES LESS THAN (200905), PARTITION P_MAY09 VALUES LESS THAN (200906), PARTITION P_JUN09 VALUES LESS THAN (200907), PARTITION P_JUL09 VALUES LESS THAN (200908), PARTITION P_AUG09 VALUES LESS THAN (200909), PARTITION P_SEP09 VALUES LESS THAN (200910), PARTITION P_OCT09 VALUES LESS THAN (200911), PARTITION P_NOV09 VALUES LESS THAN (200912), PARTITION P_DEC09 VALUES LESS THAN (201001), PARTITION P_AFTER_DEC09 VALUES LESS THAN (MAXVALUE) );

NOT NULL,

CREATE UNIQUE INDEX PK_MIS_LOAN_LINE_ANLS_ENH ON MIS_LOAN_LINE_ANLS_ENH (MIS_ID, YEAR_MONTH, PROD_CD, PROD_PRG_CD, COMPANY_CD, BRANCH_CD, MIS_SEQ_CD, DPD_CD); ALTER TABLE MIS_LOAN_LINE_ANLS_ENH ADD ( CONSTRAINT PK_MIS_LOAN_LINE_ANLS_ENH PRIMARY KEY(MIS_ID, YEAR_MONTH, PROD_CD, PROD_PRG_CD, COMPANY_CD, BRANCH_CD, MIS_SEQ_CD, DPD_CD));

¾

FACT TABLE MIS_MITRA_LOAN_LINE_ANLS_ENH CREATE TABLE MIS_MITRA_LOAN_LINE_ANLS_ENH( MIS_ID VARCHAR2(6 BYTE) NOT NULL, YEAR_MONTH NUMBER(6) NOT NULL, PROD_CD VARCHAR2(8 BYTE) NOT NULL, PROD_PRG_CD NUMBER(4) NOT NULL, COMPANY_CD VARCHAR2(5 BYTE) NOT NULL, BRANCH_CD VARCHAR2(5 BYTE) NOT NULL, MIS_SEQ_CD NUMBER(3) NOT NULL, COUNTRY_CD VARCHAR2(2 BYTE), MIS_NVALUE NUMBER(18,2), MIS_DVALUE NUMBER(18,2) DEFAULT 0 NOT NULL, PROCESS_SEQ_NO NUMBER(12), APPL_NO VARCHAR2(20 BYTE), SUB_COMPANY_NAME VARCHAR2(100 BYTE), MAIN_COMPANY_NAME VARCHAR2(100 BYTE), TOT_APPL_APP NUMBER(17,2), BAKI_DEBIT NUMBER(17,2), DPD_CD NUMBER(3)) PARTITION BY RANGE (YEAR_MONTH)( PARTITION P_BEFORE_JAN08 VALUES LESS THAN (200801),

L41 PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION

P_JAN08 VALUES LESS THAN (200802), P_FEB08 VALUES LESS THAN (200803), P_MAR08 VALUES LESS THAN (200804), P_APR08 VALUES LESS THAN (200805), P_MAY08 VALUES LESS THAN (200806), P_JUN08 VALUES LESS THAN (200807), P_JUL08 VALUES LESS THAN (200808), P_AUG08 VALUES LESS THAN (200809), P_SEP08 VALUES LESS THAN (200810), P_OCT08 VALUES LESS THAN (200811), P_NOV08 VALUES LESS THAN (200812), P_DEC08 VALUES LESS THAN (200901), P_JAN09 VALUES LESS THAN (200902), P_FEB09 VALUES LESS THAN (200903), P_MAR09 VALUES LESS THAN (200904), P_APR09 VALUES LESS THAN (200905), P_MAY09 VALUES LESS THAN (200906), P_JUN09 VALUES LESS THAN (200907), P_JUL09 VALUES LESS THAN (200908), P_AUG09 VALUES LESS THAN (200909), P_SEP09 VALUES LESS THAN (200910), P_OCT09 VALUES LESS THAN (200911), P_NOV09 VALUES LESS THAN (200912), P_DEC09 VALUES LESS THAN (201001), P_AFTER_DEC09 VALUES LESS THAN (MAXVALUE)

); CREATE UNIQUE INDEX PK_MIS_MTRA_LOAN_LINE_ANLS_ENH ON MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID, YEAR_MONTH, PROD_CD, PROD_PRG_CD, COMPANY_CD, BRANCH_CD, MIS_SEQ_CD, DPD_CD); ALTER TABLE MIS_MITRA_LOAN_LINE_ANLS_ENH ADD ( CONSTRAINT PK_MIS_MTRA_LON_LINE_ANLS_ENH PRIMARY KEY (MIS_ID, YEAR_MONTH, PROD_CD, PROD_PRG_CD, COMPANY_CD, BRANCH_CD, MIS_SEQ_CD, DPD_CD));

¾

FACT TABLE MIS_PROFIT_LOSS_ALL CREATE TABLE MIS_PROFIT_LOSS_ALL( MIS_ID VARCHAR2(6 BYTE) YEAR_MONTH NUMBER(6) PROD_CD VARCHAR2(8 BYTE) REGION_CD VARCHAR2(5 BYTE) MIS_SEQ_CD NUMBER(3) COUNTRY_CD VARCHAR2(2 BYTE), MIS_NVALUE NUMBER(18,2), MIS_DVALUE NUMBER(18,2) PROCESS_SEQ_NO NUMBER(12) )

NOT NOT NOT NOT NOT

NULL, NULL, NULL, NULL, NULL,

DEFAULT 0 NOT NULL,

PARTITION BY RANGE (YEAR_MONTH)( PARTITION P_BEFORE_JAN08 VALUES LESS THAN (200801), PARTITION P_JAN08 VALUES LESS THAN (200802), PARTITION P_FEB08 VALUES LESS THAN (200803), PARTITION P_MAR08 VALUES LESS THAN (200804), PARTITION P_APR08 VALUES LESS THAN (200805), PARTITION P_MAY08 VALUES LESS THAN (200806), PARTITION P_JUN08 VALUES LESS THAN (200807), PARTITION P_JUL08 VALUES LESS THAN (200808), PARTITION P_AUG08 VALUES LESS THAN (200809), PARTITION P_SEP08 VALUES LESS THAN (200810), PARTITION P_OCT08 VALUES LESS THAN (200811), PARTITION P_NOV08 VALUES LESS THAN (200812), PARTITION P_DEC08 VALUES LESS THAN (200901), PARTITION P_JAN09 VALUES LESS THAN (200902), PARTITION P_FEB09 VALUES LESS THAN (200903), PARTITION P_MAR09 VALUES LESS THAN (200904), PARTITION P_APR09 VALUES LESS THAN (200905), PARTITION P_MAY09 VALUES LESS THAN (200906), PARTITION P_JUN09 VALUES LESS THAN (200907), PARTITION P_JUL09 VALUES LESS THAN (200908), PARTITION P_AUG09 VALUES LESS THAN (200909), PARTITION P_SEP09 VALUES LESS THAN (200910), PARTITION P_OCT09 VALUES LESS THAN (200911), PARTITION P_NOV09 VALUES LESS THAN (200912), PARTITION P_DEC09 VALUES LESS THAN (201001), PARTITION P_AFTER_DEC09 VALUES LESS THAN (MAXVALUE)); CREATE UNIQUE INDEX PK_MIS_PROFIT_LOSS_ALL ON MIS_PROFIT_LOSS_ALL (MIS_ID, YEAR_MONTH, PROD_CD, REGION_CD, MIS_SEQ_CD); ALTER TABLE MIS_PROFIT_LOSS_ALL ADD ( CONSTRAINT PK_MIS_PROFIT_LOSS_ALL PRIMARY KEY(MIS_ID, YEAR_MONTH, PROD_CD, REGION_CD, MIS_SEQ_CD));

L42 ¾

CREATE PROCEDURE DBP_MIS_AVG_TURNAROUND_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_MIS_AVG_TURNAROUND_ENH(I_CNT_CD IN VARCHAR2,I_INDATE IN DATE, O_STATUS OUT VARCHAR2,O_SEVERITY OUT VARCHAR2,O_REMARKS OUT VARCHAR2) IS V_SEQ_NO PLS_INTEGER := 0; V_EXCEP EXCEPTION; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.MIS_AVG_TURNAROUND_TIME_ENH; V_START_TIME DATE; V_TOT_CNT PLS_INTEGER := 0; V_MIS_ID MIS_AVG_TURNAROUND_TIME_ENH.MIS_ID%TYPE := '1DAT01'; CURSOR C_AVG_TURN IS SELECT B.PROD_CD, B.PROD_PRG_CD,A.REGIONAL_ID, COUNT(A.APPLN_NO) AS APPLN_CNT,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE) GROUP BY B.PROD_CD, B.PROD_PRG_CD, A.REGIONAL_ID, A.BRANCH_CD; CURSOR C_AVG_TURN_TOTAL IS SELECT B.PROD_CD, B.PROD_PRG_CD,A.REGIONAL_ID, COUNT(A.APPLN_NO) AS APPLN_CNT,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN D_FACILITY_LOS_DTL_BAK FL ON A.APPLN_NO = FL.APPLN_NO JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(NVL(A.DOC_COLL_SPPK_START_DATE,FL.DISBMT_START_DATE)) = TRUNC(I_INDATE) GROUP BY B.PROD_CD, B.PROD_PRG_CD, A.REGIONAL_ID, A.BRANCH_CD; PROCEDURE INSERT_MISSING IS CURSOR C3 IS SELECT DISTINCT PROD_CD,PROD_PRG_CD,REGION_CD,BRANCH_CD FROM MIS_AVG_TURNAROUND_TIME_ENH WHERE MIS_DATE BETWEEN I_INDATE-1 AND I_INDATE ; BEGIN FOR R3 IN C3 LOOP INSERT INTO MIS_AVG_TURNAROUND_TIME_ENH( MIS_ID, MIS_DATE, PROD_CD,PROD_PRG_CD, REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO, BRANCH_CD) SELECT V_MIS_ID,I_INDATE,R3.PROD_CD,R3.PROD_PRG_CD,R3.REGION_CD,B.RPT_SEQ_CD, BMGLOBAL_ENH.COUNTRY_CD,0,0,V_SEQ_NO,R3.BRANCH_CD FROM REF_RPT_FORMAT B WHERE B.RPT_ID = V_MIS_ID AND RPT_SEQ_CD NOT IN (SELECT MIS_SEQ_CD FROM MIS_AVG_TURNAROUND_TIME_ENH WHERE MIS_ID = V_MIS_ID AND TRUNC(MIS_DATE) = TRUNC(I_INDATE)AND PROD_CD = R3.PROD_CD AND PROD_PRG_CD = R3.PROD_PRG_CD AND REGION_CD = R3.REGION_CD AND BRANCH_CD = R3.BRANCH_CD) ; END LOOP ; END INSERT_MISSING ; PROCEDURE REFRESH_VIEW IS CURSOR V1C1 IS SELECT A.PROD_CD, A.PROD_PRG_CD, A.REGION_CD, A.MIS_SEQ_CD, NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE, A.BRANCH_CD FROM MIS_AVG_TURNAROUND_TIME_ENH A WHERE A.MIS_DATE BETWEEN TRUNC(I_INDATE,'MONTH') AND I_INDATE GROUP BY PROD_CD, PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, BRANCH_CD; CURSOR V1C2 IS SELECT A.PROD_CD, A.PROD_PRG_CD, A.REGION_CD, I_INDATE AS MIS_DATE, A.MIS_SEQ_CD, NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE, A.BRANCH_CD FROM MIS_AVG_TURNAROUND_TIME_ENH A WHERE A.MIS_DATE BETWEEN TRUNC(I_INDATE,'YEAR') AND I_INDATE GROUP BY A.PROD_CD, A.PROD_PRG_CD, A.REGION_CD, A.MIS_SEQ_CD, A.BRANCH_CD; PROCEDURE UPD_INS_VIEW( I_PROD_CD MIS_AVG_TURNAROUND_TIME_ENH.PROD_CD%TYPE, I_PROD_PRG_CD MIS_AVG_TURNAROUND_TIME_ENH.PROD_PRG_CD%TYPE, I_REGION_CD MIS_AVG_TURNAROUND_TIME_ENH.REGION_CD%TYPE, I_MIS_SEQ_CD MIS_AVG_TURNAROUND_TIME_ENH.MIS_SEQ_CD%TYPE, I_MTD_MIS_NVAL MIS_AVG_TURNAROUND_TIME_ENH.MIS_NVALUE%TYPE, I_MTD_MIS_DVAL MIS_AVG_TURNAROUND_TIME_ENH.MIS_NVALUE%TYPE, I_YTD_MIS_NVAL MIS_AVG_TURNAROUND_TIME_ENH.MIS_NVALUE%TYPE, I_YTD_MIS_DVAL MIS_AVG_TURNAROUND_TIME_ENH.MIS_NVALUE%TYPE, I_BRANCH_CD MIS_AVG_TURNAROUND_TIME_ENH.BRANCH_CD%TYPE) IS BEGIN UPDATE VW_MIS_AVG_TURNAROUND_TIME_ENH SET MTD_MIS_NVAL = NVL(MTD_MIS_NVAL,0) + NVL(I_MTD_MIS_NVAL,0), MTD_MIS_DVAL = NVL(MTD_MIS_DVAL,0) + NVL(I_MTD_MIS_DVAL,0), YTD_MIS_NVAL = NVL(YTD_MIS_NVAL,0) + NVL(I_YTD_MIS_NVAL,0), YTD_MIS_DVAL = NVL(YTD_MIS_DVAL,0) + NVL(I_YTD_MIS_DVAL,0) WHERE MIS_ID = V_MIS_ID AND TRUNC(MIS_DATE) = TRUNC(I_INDATE) AND PROD_CD = I_PROD_CD AND PROD_PRG_CD = I_PROD_PRG_CD AND REGION_CD AND MIS_SEQ_CD = I_MIS_SEQ_CD AND BRANCH_CD = I_BRANCH_CD ; IF (SQL%ROWCOUNT = 0) THEN

= I_REGION_CD

L43 INSERT INTO VW_MIS_AVG_TURNAROUND_TIME_ENH( COUNTRY_CD, MIS_ID, MIS_DATE,PROD_CD,PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, MTD_MIS_NVAL, MTD_MIS_DVAL,YTD_MIS_NVAL,YTD_MIS_DVAL,BRANCH_CD) VALUES( BMGLOBAL_ENH.COUNTRY_CD, V_MIS_ID, I_INDATE, I_PROD_CD, I_PROD_PRG_CD, I_REGION_CD, I_MIS_SEQ_CD, I_MTD_MIS_NVAL, I_MTD_MIS_DVAL, I_YTD_MIS_NVAL, I_YTD_MIS_DVAL, I_BRANCH_CD); END IF ; END UPD_INS_VIEW ; BEGIN BMGLOBAL_ENH.PROCLOG('DAILY DISBURSEMENT REFRESH REPORT PROCESS STARTED FOR THE DATE : ' || I_INDATE); INSERT INTO VW_MIS_AVG_TURNAROUND_TIME_ENH( COUNTRY_CD, MIS_ID, MIS_DATE,PROD_CD, PROD_PRG_CD,REGION_CD,MIS_SEQ_CD,CUR_NVAL,CUR_DVAL,BRANCH_CD) SELECT BMGLOBAL_ENH.COUNTRY_CD,MIS_ID,MIS_DATE,PROD_CD,PROD_PRG_CD,REGION_CD, MIS_SEQ_CD, NVL(SUM(A.MIS_NVALUE),0), NVL(SUM(A.MIS_DVALUE),0),BRANCH_CD FROM MIS_AVG_TURNAROUND_TIME_ENH A WHERE TRUNC(A.MIS_DATE) = TRUNC(I_INDATE) GROUP BY MIS_ID, MIS_DATE, PROD_CD, PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, BRANCH_CD ; BMGLOBAL_ENH.PROCLOG('PROCESSING C1'); FOR V1R1 IN V1C1 LOOP UPD_INS_VIEW(V1R1.PROD_CD, V1R1.PROD_PRG_CD,V1R1.REGION_CD,V1R1.MIS_SEQ_CD, V1R1.MIS_NVALUE,V1R1.MIS_DVALUE,0,0,V1R1.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C2'); FOR V1R2 IN V1C2 LOOP UPD_INS_VIEW(V1R2.PROD_CD, V1R2.PROD_PRG_CD, V1R2.REGION_CD, V1R2.MIS_SEQ_CD,0,0, V1R2.MIS_NVALUE, V1R2.MIS_DVALUE, V1R2.BRANCH_CD) ; END LOOP; COMMIT ; BMGLOBAL_ENH.PROCLOG('DAILY DISBURSEMENT REFRESH REPORT PROCESS COMPLETED SUCCESSFULLY'); END REFRESH_VIEW ; BEGIN V_START_TIME := SYSDATE; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_SUCC; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_NIL; O_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.PROCESS_NAME:= 'MIS_AVG_TURNAROUND_TIME_ENH'; BMGLOBAL_ENH.PROCLOG('DETAILED AVERAGE TURNAROUND TIME REPORT STARTED FOR THE DATE : ' || I_INDATE); BMGLOBAL_ENH.CHECK_PROCESS_STATUS(I_INDATE,V_PROCESS_ID,O_REMARKS, O_STATUS,V_SEQ_NO); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_ALD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS APPLICATION FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_FLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS FACILITY FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CPD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'CONSUMER PROMPT FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CLT,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'COLLATERAL FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR DETAILED AVERAGE TURNAROUND TIME BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR AIP - DOC COLLECTION'); EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_AVG_TA_TIME_ENH DROP STORAGE ' ; BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR AIP - DTBO');

REPORT');

INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO, B.PROD_CD, B.PROD_PRG_CD, A.REGIONAL_ID, 1,DTBO_END_DATE, CP.CU_INPUTDATE, DTBO_END_DATE - CP.CU_INPUTDATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN D_CUST_PROMPT_DTL CP ON A.PROSPECT_ID = CP.WARM_CUST_ID JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD

L44 WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR DTBO - APPRAISAL'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO, B.PROD_CD, B.PROD_PRG_CD,A.REGIONAL_ID, 3,APPRAISAL_END_DATE, DTBO_START_DATE, APPRAISAL_END_DATE - DTBO_START_DATE, 0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR DTBO - INVESTIGATION'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID, 4,INVST_END_DATE, DTBO_START_DATE,INVST_END_DATE - DTBO_START_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR DTBO - VERIFICATION'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO, B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID,5,VERFN_END_DATE, DTBO_START_DATE,VERFN_END_DATE - DTBO_START_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR DTBO - IDIBI'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID,6,VER_ASG_DATE, DTBO_START_DATE,VER_ASG_DATE - DTBO_START_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR FINAL SCORE AND LIMIT SETTING'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO, B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID,7, FS_LS_END_DATE, GREATEST(APPRAISAL_END_DATE,INVST_END_DATE,VERFN_END_DATE,VER_ASG_DATE), FS_LS_END_DATE GREATEST(APPRAISAL_END_DATE,INVST_END_DATE,VERFN_END_DATE,VER_ASG_DATE), 0, A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR FINAL SCORE AND LIMIT SETTING - APPROVAL'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID,8,APPR_END_DATE, FS_LS_END_DATE,APPR_END_DATE - FS_LS_END_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR APPROVAL - SPPK/SENT TO LOAN DISBURSEMENT'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID, 9,SEND_LD_START_DATE,APPR_END_DATE,SEND_LD_START_DATE - APPR_END_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR GRAND TOTAL'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID,10,SPPK_END_DATE, DTBO_START_DATE,SPPK_END_DATE - DTBO_START_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR DTBO - DISBURSEMENT (NEW HOUSE/OTHERS)'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID, DECODE(C.COLLTRL_SUB_TYPE,BMCONSTANT_ENH.LOAN_TYPE_NEW_HOUSE,12,11), A.DISBMT_END_DATE,A.DTBO_START_DATE,A.DISBMT_END_DATE - A.DTBO_START_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD JOIN D_COLLATERAL_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO WHERE TRUNC(A.DISBMT_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR SPPK CANCELLATION'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID,13,DOC_COLL_SPPK_START_DATE, APPR_END_DATE,DOC_COLL_SPPK_START_DATE - APPR_END_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.DOC_COLL_SPPK_START_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR SPPK - LOAN DISBURSEMENT (NEW HOUSE/OTHERS)'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD,A.REGIONAL_ID, DECODE(C.COLLTRL_SUB_TYPE,BMCONSTANT_ENH.LOAN_TYPE_NEW_HOUSE,16,14),

L45 DISBMT_END_DATE, APPR_END_DATE,DISBMT_END_DATE - APPR_END_DATE,0, A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD JOIN D_COLLATERAL_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO WHERE TRUNC(A.DISBMT_END_DATE) = TRUNC(I_INDATE) AND NVL(C.COLLTRL_SUB_TYPE,'-') <> BMCONSTANT_ENH.LOAN_TYPE_NEW_HOUSE; BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR TOTAL TIME AFTER SPPK'); INSERT INTO TMP_AVG_TA_TIME_ENH SELECT A.APPLN_NO,B.PROD_CD,B.PROD_PRG_CD, A.REGIONAL_ID,15, NVL(DOC_COLL_SPPK_START_DATE,FL.DISBMT_START_DATE),SPPK_END_DATE, NVL(DOC_COLL_SPPK_START_DATE,FL.DISBMT_START_DATE) - SPPK_END_DATE,0,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN D_FACILITY_LOS_DTL_BAK FL ON A.APPLN_NO = FL.APPLN_NO JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(NVL(DOC_COLL_SPPK_START_DATE,FL.DISBMT_START_DATE))=TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('UPDATING TEMP TABLE FOR HOLIDAY COUNT'); UPDATE TMP_AVG_TA_TIME_ENH A SET NO_HOLIDAYS = (SELECT NVL(COUNT(*),0) FROM REF_HOLIDAY WHERE HOLIDAY_DATE BETWEEN A.START_DATE AND A.END_DATE); UPDATE TMP_AVG_TA_TIME_ENH A SET NO_DAYS = NO_HOLIDAYS WHERE NO_DAYS - NO_HOLIDAYS < 0 ; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE'); INSERT INTO MIS_AVG_TURNAROUND_TIME_ENH( MIS_ID,MIS_DATE,PROD_CD,PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE, MIS_DVALUE, PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID,I_INDATE,PROD_CD,PROD_PRG_CD,REGION_CD,MIS_SEQ_CD, BMGLOBAL_ENH.COUNTRY_CD,NVL(SUM(NO_DAYS - NO_HOLIDAYS),0),COUNT(*),V_SEQ_NO,BRANCH_CD FROM TMP_AVG_TA_TIME_ENH GROUP BY PROD_CD, PROD_PRG_CD, REGION_CD, MIS_SEQ_CD, BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('UPDATING MIS TABLE DVALUE FOR ALL SEQUENCE EXCEPT 15'); FOR CUR IN C_AVG_TURN LOOP FOR I IN 1..16 LOOP IF (I NOT IN (13,15)) THEN UPDATE MIS_AVG_TURNAROUND_TIME_ENH SET MIS_DVALUE = CUR.APPLN_CNT WHERE TRUNC(MIS_DATE) = TRUNC(I_INDATE) AND PROD_CD = CUR.PROD_CD AND PROD_PRG_CD = CUR.PROD_PRG_CD AND REGION_CD = CUR.REGIONAL_ID AND MIS_SEQ_CD = I AND BRANCH_CD = CUR.BRANCH_CD; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO MIS_AVG_TURNAROUND_TIME_ENH( MIS_ID,MIS_DATE,PROD_CD,PROD_PRG_CD,REGION_CD,MIS_SEQ_CD, COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,BRANCH_CD) VALUES(V_MIS_ID, I_INDATE,CUR.PROD_CD, CUR.PROD_PRG_CD,CUR.REGIONAL_ID,I, BMGLOBAL_ENH.COUNTRY_CD,0,CUR.APPLN_CNT,V_SEQ_NO,CUR.BRANCH_CD) ; END IF; END IF; END LOOP ; END LOOP; BMGLOBAL_ENH.PROCLOG('UPDATING MIS TABLE DVALUE FOR SEQUENCE NUMBER 15'); FOR CUR IN C_AVG_TURN_TOTAL LOOP UPDATE MIS_AVG_TURNAROUND_TIME_ENH SET MIS_DVALUE = CUR.APPLN_CNT WHERE MIS_SEQ_CD = 15 AND TRUNC(MIS_DATE) = TRUNC(I_INDATE) AND PROD_CD = CUR.PROD_CD AND PROD_PRG_CD = CUR.PROD_PRG_CD AND REGION_CD = CUR.REGIONAL_ID AND BRANCH_CD = CUR.BRANCH_CD; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO MIS_AVG_TURNAROUND_TIME_ENH( MIS_ID, MIS_DATE,PROD_CD,PROD_PRG_CD,REGION_CD,MIS_SEQ_CD, COUNTRY_CD,MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO,BRANCH_CD) VALUES(V_MIS_ID, I_INDATE,CUR.PROD_CD, CUR.PROD_PRG_CD,CUR.REGIONAL_ID, 15, BMGLOBAL_ENH.COUNTRY_CD,0,CUR.APPLN_CNT, V_SEQ_NO,CUR.BRANCH_CD) ; END IF; END LOOP; INSERT_MISSING; COMMIT; REFRESH_VIEW; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS, V_TOT_CNT,0, O_REMARKS, V_SEQ_NO, I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG ('DETAILED AVERAGE TAREPORT COMPLETED SUCCESSFULLY FOR THE DATE : '||I_INDATE) ; COMMIT ; EXCEPTION WHEN V_EXCEP THEN ROLLBACK;

L46 O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_MED; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('DETAILED AVERAGE TA REPORT FAILED FOR THE DATE : ' || I_INDATE) ; BMGLOBAL_ENH.ERRORLOG(0,O_REMARKS); COMMIT; WHEN OTHERS THEN O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := TO_CHAR(SQLCODE) || SQLERRM; ROLLBACK; BMGLOBAL_ENH.ROLLBACK_MIS(I_INDATE, V_PROCESS_ID,'D'); BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('DETAILED AVERAGE TURNAROUND TIME REPORT FAILED FOR THE DATE:'|| I_INDATE); BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); COMMIT; END DBP_MIS_AVG_TURNAROUND_ENH; /

L47 ¾

CREATE PROCEDURE DBP_MIS_KPI_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_MIS_KPI_ENH( I_CNT_CD IN VARCHAR2,I_INDATE IN DATE, O_STATUS OUT VARCHAR2,O_SEVERITY OUT VARCHAR2,O_REMARKS OUT VARCHAR2 )IS V_SEQ_NO PLS_INTEGER := 0; V_EXCEP EXCEPTION; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.MIS_KPI_ENH; V_START_TIME DATE; V_TOT_CNT PLS_INTEGER := 0; V_INDATE MIS_KPI_ENH.MIS_DATE%TYPE; V_MIS_ID MIS_KPI_ENH.MIS_ID%TYPE := '1KPI01'; V_TYPE MIS_KPI_ENH.TYPE%TYPE; V_REGION_CD MIS_KPI_ENH.REGION_CD%TYPE; V_YEARMONTH NUMBER(6); V_STATUS VARCHAR2(2) := '0' ; V_SEVERITY VARCHAR2(2) := '0' ; V_REMARKS VARCHAR2(1000) := ' ' ; V_MONTH P_ACCT_DTL_ENH.MONTH_IDX%TYPE := TO_CHAR(I_INDATE,'MM'); CURSOR CUR_SPPK_CNT IS SELECT C.LOAN_TYPE,A.REGIONAL_ID, COUNT(A.APPLN_NO) AS APPLN_CNT, A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH WHERE TRUNC(SPPK_END_DATE) = TRUNC(I_INDATE) GROUP BY C.LOAN_TYPE, A.REGIONAL_ID, A.BRANCH_CD; CURSOR CUR_APPR_RATE IS SELECT C.LOAN_TYPE, A.REGIONAL_ID, COUNT(A.APPLN_NO) AS APPLN_CNT, A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX= V_MONTH JOIN D_CUST_PROMPT_DTL CP ON A.PROSPECT_ID = CP.WARM_CUST_ID JOIN D_CUST_SUMMARY CS ON A.APPLN_NO = CS.APPLN_NO WHERE TRUNC(CP.CU_INPUTDATE) = TRUNC(I_INDATE) AND CS.SCORE IN(BMCONSTANT_ENH.PRE_APPLN_STATUS_ACCEPT, BMCONSTANT_ENH.PRE_APPLN_STATUS_GREY, BMCONSTANT_ENH.PRE_APPLN_STATUS_CANCEL) GROUP BY C.LOAN_TYPE, A.REGIONAL_ID, A.BRANCH_CD; CURSOR CUR_TOTAL_AMT IS SELECT C.TYPE, A.REGION_CD, NVL(SUM(A.CURR_BAL1),0) AS ENR, A.BRANCH_CD FROM P_ACCT_DTL_ENH A JOIN REF_GROUP_PRODUCT C ON TRIM(A.LOAN_TYPE) = TRIM(C.TYPE) AND A.MONTH_IDX = V_MONTH GROUP BY C.TYPE, A.REGION_CD, A.BRANCH_CD ORDER BY C.TYPE, A.REGION_CD, A.BRANCH_CD; PROCEDURE INSERT_MISSING IS CURSOR C3 IS SELECT DISTINCT TYPE,REGION_CD,BRANCH_CD FROM MIS_KPI_ENH WHERE TRUNC(MIS_DATE) BETWEEN TRUNC(I_INDATE)-1 AND TRUNC(I_INDATE) ; BEGIN FOR R3 IN C3 LOOP INSERT INTO MIS_KPI_ENH( MIS_ID, MIS_DATE, TYPE, REGION_CD, MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO, BRANCH_CD) SELECT V_MIS_ID, I_INDATE, R3.TYPE, R3.REGION_CD, B.RPT_SEQ_CD,BMGLOBAL_ENH.COUNTRY_CD,0,0, V_SEQ_NO, R3.BRANCH_CD FROM REF_RPT_FORMAT B WHERE B.RPT_ID = V_MIS_ID AND RPT_SEQ_CD NOT IN (SELECT MIS_SEQ_CD FROM MIS_KPI_ENH WHERE MIS_ID = V_MIS_ID AND TRUNC(MIS_DATE)= TRUNC(I_INDATE) AND TYPE = R3.TYPE AND REGION_CD = R3.REGION_CD AND BRANCH_CD = R3.BRANCH_CD) ; END LOOP ; END INSERT_MISSING ; PROCEDURE INSERT_TO_MIS( I_TYPE IN MIS_KPI_ENH.TYPE%TYPE, I_REGION_CD IN MIS_KPI_ENH.REGION_CD%TYPE, I_MIS_SEQ_CD IN MIS_KPI_ENH.MIS_SEQ_CD%TYPE, I_MIS_NVALUE IN MIS_KPI_ENH.MIS_NVALUE%TYPE, I_MIS_DVALUE IN MIS_KPI_ENH.MIS_DVALUE%TYPE, I_BRANCH_CD IN MIS_KPI_ENH.BRANCH_CD%TYPE) IS BEGIN INSERT INTO MIS_KPI_ENH(MIS_ID, MIS_DATE,TYPE, REGION_CD,MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO, BRANCH_CD) VALUES(V_MIS_ID, I_INDATE, I_TYPE, I_REGION_CD, I_MIS_SEQ_CD,BMGLOBAL_ENH.COUNTRY_CD, I_MIS_NVALUE,I_MIS_DVALUE,V_SEQ_NO, I_BRANCH_CD ); END INSERT_TO_MIS; PROCEDURE UPDATE_TO_MIS( I_TYPE IN MIS_KPI_ENH.TYPE%TYPE, I_REGION_CD IN MIS_KPI_ENH.REGION_CD%TYPE, I_MIS_SEQ_CD IN MIS_KPI_ENH.MIS_SEQ_CD%TYPE, I_MIS_DVALUE IN MIS_KPI_ENH.MIS_DVALUE%TYPE, I_BRANCH_CD IN MIS_KPI_ENH.BRANCH_CD%TYPE) IS V_MIS_NVALUE MIS_KPI_ENH.MIS_NVALUE%TYPE := 0 ;

L48 BEGIN UPDATE MIS_KPI_ENH SET MIS_DVALUE = I_MIS_DVALUE WHERE MIS_ID = V_MIS_ID AND TRUNC(MIS_DATE) = TRUNC(I_INDATE)AND TYPE = I_TYPE AND REGION_CD = I_REGION_CD AND MIS_SEQ_CD = I_MIS_SEQ_CD AND BRANCH_CD = I_BRANCH_CD; IF (SQL%ROWCOUNT = 0) THEN INSERT_TO_MIS(I_TYPE,I_REGION_CD,I_MIS_SEQ_CD,V_MIS_NVALUE,I_MIS_DVALUE,I_BRANCH_CD); END IF ; END UPDATE_TO_MIS; PROCEDURE REFRESH_VIEW1 IS CURSOR V1C1 IS SELECT A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE,A.BRANCH_CD FROM MIS_KPI_ENH A WHERE TRUNC(A.MIS_DATE) = TRUNC(I_INDATE)-1 AND A.MIS_SEQ_CD IN (1,2,3,4,5,6,7) GROUP BY A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,A.BRANCH_CD; CURSOR V1C2 IS SELECT A.TYPE,A.REGION_CD,I_INDATE AS MIS_DATE,A.MIS_SEQ_CD,NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE,A.BRANCH_CD FROM MIS_KPI_ENH A WHERE TRUNC(A.MIS_DATE) BETWEEN TRUNC(I_INDATE,'MONTH') AND TRUNC(I_INDATE) AND A.MIS_SEQ_CD IN (1,2,4,5,6,7) GROUP BY A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,A.BRANCH_CD; CURSOR V1C3 IS SELECT A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE,A.BRANCH_CD FROM MIS_KPI_ENH A WHERE TRUNC(A.MIS_DATE) = ADD_MONTHS(TRUNC(I_INDATE),-1) AND A.MIS_SEQ_CD IN (1,2,3,4,5,6,7) GROUP BY A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,A.BRANCH_CD; CURSOR V1C4 IS SELECT A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE,A.BRANCH_CD FROM MIS_KPI_ENH A WHERE TRUNC(A.MIS_DATE) = LAST_DAY(ADD_MONTHS(TRUNC(I_INDATE),-1)) AND A.MIS_SEQ_CD IN (1,2,3,4,5,6,7) GROUP BY A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,A.BRANCH_CD; CURSOR V1C5 IS SELECT A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,NVL(SUM(A.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(A.MIS_DVALUE),0) AS MIS_DVALUE,A.BRANCH_CD FROM MIS_KPI_ENH A WHERE TRUNC(A.MIS_DATE) BETWEEN TRUNC(I_INDATE,'YEAR') AND TRUNC(I_INDATE) AND A.MIS_SEQ_CD IN (1,2,4,5,6,7) GROUP BY A.TYPE,A.REGION_CD,A.MIS_SEQ_CD,A.BRANCH_CD; CURSOR V1C6 IS SELECT B.TYPE,B.REGION_CD,B.MIS_SEQ_CD,NVL(SUM(B.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(B.MIS_DVALUE),0) AS MIS_DVALUE,B.BRANCH_CD FROM MIS_KPI_ENH B JOIN (SELECT TRUNC(I_INDATE,'YEAR') AS YEAR_MONTH,MAX(I_INDATE) AS MIS_DATE FROM MIS_KPI_ENH C WHERE C.MIS_SEQ_CD = 3 GROUP BY TRUNC(I_INDATE,'YEAR')) D ON TRUNC(B.MIS_DATE) = TRUNC(D.MIS_DATE) AND TRUNC(B.MIS_DATE,'YEAR') = D.YEAR_MONTH WHERE B.MIS_SEQ_CD IN (3) GROUP BY B.TYPE,B.REGION_CD,B.MIS_SEQ_CD,B.BRANCH_CD; CURSOR V1C7 IS SELECT B.TYPE,B.REGION_CD,B.MIS_SEQ_CD,NVL(SUM(B.MIS_NVALUE),0) AS MIS_NVALUE, NVL(SUM(B.MIS_DVALUE),0) AS MIS_DVALUE,B.BRANCH_CD FROM MIS_KPI_ENH B JOIN (SELECT TRUNC(I_INDATE,'YEAR') AS YEAR_MONTH,MAX(I_INDATE) AS MIS_DATE FROM MIS_KPI_ENH C WHERE C.MIS_SEQ_CD = 3 GROUP BY TRUNC(I_INDATE,'YEAR')) D ON TRUNC(B.MIS_DATE) = TRUNC(D.MIS_DATE) AND TRUNC(B.MIS_DATE,'YEAR') = D.YEAR_MONTH WHERE B.MIS_SEQ_CD IN (3) GROUP BY B.TYPE,B.REGION_CD,B.MIS_SEQ_CD,B.BRANCH_CD; PROCEDURE UPD_INS_VIEW1( I_TYPE MIS_KPI_ENH.TYPE%TYPE, I_REGION_CD MIS_KPI_ENH.REGION_CD%TYPE, I_MIS_SEQ_CD MIS_KPI_ENH.MIS_SEQ_CD%TYPE, I_P1D_MIS_NVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_P1D_MIS_DVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_MTD_MIS_NVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_MTD_MIS_DVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_SDP_MIS_NVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_SDP_MIS_DVAL MIS_KPI_ENH.MIS_NVALUE%TYPE,

L49 I_LDP_MIS_NVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_LDP_MIS_DVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_YTD_MIS_NVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_YTD_MIS_DVAL MIS_KPI_ENH.MIS_NVALUE%TYPE, I_BRANCH_CD MIS_KPI_ENH.BRANCH_CD%TYPE) IS BEGIN UPDATE VW_MIS_KPI1_ENH SET P1D_MIS_NVAL = NVL(P1D_MIS_NVAL,0) + NVL(I_P1D_MIS_NVAL,0), P1D_MIS_DVAL = NVL(P1D_MIS_DVAL,0) + NVL(I_P1D_MIS_DVAL,0), MTD_MIS_NVAL = NVL(MTD_MIS_NVAL,0) + NVL(I_MTD_MIS_NVAL,0), MTD_MIS_DVAL = NVL(MTD_MIS_DVAL,0) + NVL(I_MTD_MIS_DVAL,0), SDP_MIS_NVAL = NVL(SDP_MIS_NVAL,0) + NVL(I_SDP_MIS_NVAL,0), SDP_MIS_DVAL = NVL(SDP_MIS_DVAL,0) + NVL(I_SDP_MIS_DVAL,0), LDP_MIS_NVAL = NVL(LDP_MIS_NVAL,0) + NVL(I_LDP_MIS_NVAL,0), LDP_MIS_DVAL = NVL(LDP_MIS_DVAL,0) + NVL(I_LDP_MIS_DVAL,0), YTD_MIS_NVAL = NVL(YTD_MIS_NVAL,0) + NVL(I_YTD_MIS_NVAL,0), YTD_MIS_DVAL = NVL(YTD_MIS_DVAL,0) + NVL(I_YTD_MIS_DVAL,0) WHERE MIS_ID = V_MIS_ID AND TRUNC(MIS_DATE) = TRUNC(I_INDATE) AND TYPE = I_TYPE AND REGION_CD = I_REGION_CD AND MIS_SEQ_CD = I_MIS_SEQ_CD AND BRANCH_CD = I_BRANCH_CD; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO VW_MIS_KPI1_ENH(COUNTRY_CD, MIS_ID, MIS_DATE,TYPE, REGION_CD,MIS_SEQ_CD, P1D_MIS_NVAL, P1D_MIS_DVAL,MTD_MIS_NVAL, MTD_MIS_DVAL, SDP_MIS_NVAL, SDP_MIS_DVAL, LDP_MIS_NVAL,LDP_MIS_DVAL,YTD_MIS_NVAL,YTD_MIS_DVAL,BRANCH_CD) VALUES( BMGLOBAL_ENH.COUNTRY_CD, V_MIS_ID, I_INDATE,I_TYPE, I_REGION_CD,I_MIS_SEQ_CD, I_P1D_MIS_NVAL, I_P1D_MIS_DVAL,I_MTD_MIS_NVAL,I_MTD_MIS_DVAL,I_SDP_MIS_NVAL,I_SDP_MIS_DVAL, I_LDP_MIS_NVAL, I_LDP_MIS_DVAL,I_YTD_MIS_NVAL,I_YTD_MIS_DVAL,I_BRANCH_CD); END IF ; END UPD_INS_VIEW1 ; BEGIN BMGLOBAL_ENH.PROCLOG('KPI REPORT REFRESH VIEW1 PROCESS STARTED FOR THE DATE : ' || I_INDATE); INSERT INTO VW_MIS_KPI1_ENH( COUNTRY_CD, MIS_ID, MIS_DATE,TYPE, REGION_CD,MIS_SEQ_CD, CUR_MIS_NVAL, CUR_MIS_DVAL,BRANCH_CD) SELECT BMGLOBAL_ENH.COUNTRY_CD, MIS_ID, MIS_DATE,TYPE, REGION_CD,MIS_SEQ_CD, NVL(SUM(A.MIS_NVALUE),0), NVL(SUM(A.MIS_DVALUE),0),BRANCH_CD FROM MIS_KPI_ENH A WHERE TRUNC(A.MIS_DATE) = TRUNC(I_INDATE) AND MIS_SEQ_CD IN(1,2,3,4,5,6,7) GROUP BY MIS_ID, MIS_DATE, TYPE, REGION_CD, MIS_SEQ_CD, BRANCH_CD; BMGLOBAL_ENH.PROCLOG('PROCESSING C1'); FOR V1R1 IN V1C1 LOOP UPD_INS_VIEW1(V1R1.TYPE,V1R1.REGION_CD,V1R1.MIS_SEQ_CD, V1R1.MIS_NVALUE, V1R1.MIS_DVALUE, 0,0,0,0,0,0,0,0,V1R1.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C2'); FOR V1R2 IN V1C2 LOOP UPD_INS_VIEW1(V1R2.TYPE, V1R2.REGION_CD,V1R2.MIS_SEQ_CD,0,0,V1R2.MIS_NVALUE, V1R2.MIS_DVALUE,0,0,0,0,0,0,V1R2.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C3'); FOR V1R3 IN V1C3 LOOP UPD_INS_VIEW1(V1R3.TYPE, V1R3.REGION_CD,V1R3.MIS_SEQ_CD,0,0,0,0,V1R3.MIS_NVALUE, V1R3.MIS_DVALUE,0,0,0,0,V1R3.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C4'); FOR V1R4 IN V1C4 LOOP UPD_INS_VIEW1(V1R4.TYPE, V1R4.REGION_CD,V1R4.MIS_SEQ_CD,0,0,0,0,0,0, V1R4.MIS_NVALUE, V1R4.MIS_DVALUE,0,0,V1R4.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C5'); FOR V1R5 IN V1C5 LOOP UPD_INS_VIEW1(V1R5.TYPE, V1R5.REGION_CD,V1R5.MIS_SEQ_CD,0,0,0,0,0,0,0,0, V1R5.MIS_NVALUE, V1R5.MIS_DVALUE, V1R5.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C6'); FOR V1R6 IN V1C6 LOOP UPD_INS_VIEW1(V1R6.TYPE, V1R6.REGION_CD,V1R6.MIS_SEQ_CD, 0,0, V1R6.MIS_NVALUE, V1R6.MIS_DVALUE,0,0,0,0,0,0,V1R6.BRANCH_CD) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('PROCESSING C7'); FOR V1R7 IN V1C7 LOOP UPD_INS_VIEW1(V1R7.TYPE, V1R7.REGION_CD,V1R7.MIS_SEQ_CD, 0, 0,0, 0, 0,0, 0, 0, V1R7.MIS_NVALUE, V1R7.MIS_DVALUE, V1R7.BRANCH_CD); END LOOP ; COMMIT ;

L50 BMGLOBAL_ENH.PROCLOG('KPI REPORT REFRESH VIEW1 PROCESS COMPLETED SUCCESSFULLY'); COMMIT ; END REFRESH_VIEW1 ; BEGIN V_START_TIME := SYSDATE; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_SUCC; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_NIL; O_REMARKS := 'SUCCESS'; V_YEARMONTH := TO_NUMBER(TO_CHAR(I_INDATE,'YYYYMM')); BMGLOBAL_ENH.PROCESS_NAME := 'DBP_MIS_KPI_ENH'; BMGLOBAL_ENH.PROCLOG('KPI REPORT PROCESS STARTED FOR THE DATE : ' || I_INDATE); BMGLOBAL_ENH.CHECK_PROCESS_STATUS(I_INDATE,V_PROCESS_ID, O_REMARKS, O_STATUS, V_SEQ_NO); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.PRE_PROCESS,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'PRE-PROCESS DID NOT RUN SUCCESSFULLY FOR THE DATE '|| I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_ALD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS APPLICATION FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CPD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'CONSUMER PROMPT FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CSU,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'CUSTOMER SUMMARY FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_FLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'FACILITY LOS FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_KPI_ENH DROP STORAGE ' ; BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR AIP - SPPK TURNAROUND TIME'); INSERT INTO TMP_KPI_ENH SELECT A.APPLN_NO,C.LOAN_TYPE, A.REGIONAL_ID,1,A.SPPK_END_DATE, CP.CU_INPUTDATE, A.SPPK_END_DATE - CP.CU_INPUTDATE,0, A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH JOIN D_CUST_PROMPT_DTL CP ON A.PROSPECT_ID = CP.WARM_CUST_ID WHERE TRUNC(SPPK_END_DATE) = TRUNC(I_INDATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO TEMP TABLE FOR DTBO - SPPK TURNAROUND TIME'); INSERT INTO TMP_KPI_ENH SELECT A.APPLN_NO,C.LOAN_TYPE, A.REGIONAL_ID, 2, A.SPPK_END_DATE,A.DTBO_START_DATE, A.SPPK_END_DATE - A.DTBO_START_DATE,0, A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH WHERE TRUNC(SPPK_END_DATE) = TRUNC(I_INDATE); V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('UPDATING TEMP TABLE FOR HOLIDAY COUNT'); UPDATE TMP_KPI_ENH A SET NO_HOLIDAYS = (SELECT COUNT(*) FROM REF_HOLIDAY WHERE HOLIDAY_DATE BETWEEN A.START_DATE AND A.END_DATE); BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR AIP - SPPK AND DTBO - SPPK TURNAROUND TIME'); INSERT INTO MIS_KPI_ENH (MIS_ID, MIS_DATE, TYPE,REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE, PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID, I_INDATE,TYPE,REGION_CD, MIS_SEQ_CD,BMGLOBAL_ENH.COUNTRY_CD, SUM(NO_DAYS - NO_HOLIDAYS),0, V_SEQ_NO,BRANCH_CD FROM TMP_KPI_ENH GROUP BY TYPE, REGION_CD, MIS_SEQ_CD, BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('UPDATING MIS TABLE D VALUE FOR AIP - SPPK AND DTBO - SPPK'); FOR CUR IN CUR_SPPK_CNT LOOP UPDATE_TO_MIS(CUR.LOAN_TYPE,CUR.REGIONAL_ID,1,CUR.APPLN_CNT,CUR.BRANCH_CD); UPDATE_TO_MIS(CUR.LOAN_TYPE,CUR.REGIONAL_ID,2,CUR.APPLN_CNT,CUR.BRANCH_CD); END LOOP; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR NET OUTSTANDING'); INSERT INTO MIS_KPI_ENH (MIS_ID,MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD,

L51 COUNTRY_CD, MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID, I_INDATE,B.TYPE,A.REGION_CD,3,BMGLOBAL_ENH.COUNTRY_CD, SUM(A.CURR_BAL1),0,V_SEQ_NO,A.BRANCH_CD FROM P_ACCT_DTL_ENH A JOIN REF_GROUP_PRODUCT B ON TRIM(A.LOAN_TYPE) = TRIM(B.TYPE) AND A.ACCT_STATUS1 = BMCONSTANT_ENH.ACCT_STATUS_OPEN AND A.MONTH_IDX = V_MONTH GROUP BY B.TYPE, A.REGION_CD, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR INITIAL APPROVAL RATE N VALUE'); INSERT INTO MIS_KPI_ENH (MIS_ID,MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID, I_INDATE,C.LOAN_TYPE,A.REGIONAL_ID, 4,BMGLOBAL_ENH.COUNTRY_CD, COUNT(A.APPLN_NO),0, V_SEQ_NO,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH JOIN D_CUST_PROMPT_DTL CP ON A.PROSPECT_ID = CP.WARM_CUST_ID JOIN D_CUST_SUMMARY CS ON A.APPLN_NO = CS.APPLN_NO WHERE TRUNC(CP.CU_INPUTDATE) = (I_INDATE) AND CS.SCORE IN ( BMCONSTANT_ENH.PRE_APPLN_STATUS_ACCEPT,BMCONSTANT_ENH.PRE_APPLN_STATUS_GREY) GROUP BY C.LOAN_TYPE, A.REGIONAL_ID, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR FINAL APPROVAL RATE N VALUE'); INSERT INTO MIS_KPI_ENH (MIS_ID, MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD, COUNTRY_CD,MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID, I_INDATE,C.LOAN_TYPE,A.REGIONAL_ID, 5,BMGLOBAL_ENH.COUNTRY_CD, COUNT(A.APPLN_NO),0, V_SEQ_NO,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH WHERE TRUNC(A.APPROVAL_DATE) = TRUNC(I_INDATE) AND A.FINAL_APPLN_STATUS = BMCONSTANT_ENH.POST_APPLN_STATUS_APPROVED GROUP BY C.LOAN_TYPE, A.REGIONAL_ID, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('UPDATING MIS TABLE D VALUE FOR INITIAL APPROVAL RATE AND FINAL APPROVAL RATE'); FOR CUR IN CUR_APPR_RATE LOOP UPDATE_TO_MIS(CUR.LOAN_TYPE,CUR.REGIONAL_ID,4,CUR.APPLN_CNT,CUR.BRANCH_CD); UPDATE_TO_MIS(CUR.LOAN_TYPE,CUR.REGIONAL_ID,5,CUR.APPLN_CNT,CUR.BRANCH_CD); END LOOP; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR NUMBER OF SPPK'); INSERT INTO MIS_KPI_ENH (MIS_ID, MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID,I_INDATE,C.LOAN_TYPE,A.REGIONAL_ID,6,BMGLOBAL_ENH.COUNTRY_CD, COUNT(A.APPLN_NO),0,V_SEQ_NO,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE) GROUP BY C.LOAN_TYPE, A.REGIONAL_ID, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR AMOUNT OF LOAN AS PER SPPK'); INSERT INTO MIS_KPI_ENH (MIS_ID,MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID,I_INDATE,C.LOAN_TYPE,A.REGIONAL_ID,7,BMGLOBAL_ENH.COUNTRY_CD, SUM(FL.SPPK_LOAN_AMT),0,V_SEQ_NO,A.BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH C ON A.APPLN_NO = C.LOS_APPLN_NO AND C.MONTH_IDX = V_MONTH JOIN D_FACILITY_LOS_DTL_BAK FL ON A.APPLN_NO = FL.APPLN_NO JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD WHERE TRUNC(A.SPPK_END_DATE) = TRUNC(I_INDATE) GROUP BY C.LOAN_TYPE, A.REGIONAL_ID, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR AMOUNT DISBURSED'); INSERT INTO

MIS_KPI_ENH (MIS_ID, MIS_DATE,TYPE,REGION_CD,MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID, I_INDATE, B.TYPE,A.REGION_CD,8, BMGLOBAL_ENH.COUNTRY_CD, SUM(A.LTD_RELEASE_AMT1 - NVL(A.PREV_DAY_RELEASE_AMT,0)), 0, V_SEQ_NO,A.BRANCH_CD FROM P_ACCT_DTL_ENH A JOIN REF_GROUP_PRODUCT B ON TRIM(A.LOAN_TYPE) = TRIM(B.TYPE) AND A.MONTH_IDX = V_MONTH JOIN D_FACILITY_LOS_DTL_BAK F ON F.APPLN_NO = A.LOS_APPLN_NO JOIN D_APPLN_LOS_DTL_BAK AL ON AL.APPLN_NO = A.LOS_APPLN_NO WHERE TRUNC(F.DISBMT_START_DATE) >= TRUNC(I_INDATE,'MONTH') AND TRUNC(NVL(AL.DISBMT_END_DATE,I_INDATE)) <= TRUNC(I_INDATE) AND A.LTD_RELEASE_AMT1 > NVL(A.PREV_DAY_RELEASE_AMT,0) GROUP BY B.TYPE,A.REGION_CD, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR AVERAGE LOAN SIZE NVALUE AND DVALUE');

L52 INSERT INTO MIS_KPI_ENH (MIS_ID, MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID,I_INDATE,B.TYPE,A.REGION_CD,9,BMGLOBAL_ENH.COUNTRY_CD, SUM(A.CURR_BAL1), COUNT(A.ACCT_NO, V_SEQ_NO,A.BRANCH_CD FROM P_ACCT_DTL_ENH A JOIN REF_GROUP_PRODUCT B ON TRIM(A.LOAN_TYPE) = TRIM(B.TYPE) AND A.MONTH_IDX = V_MONTH AND A.ACCT_STATUS1 = BMCONSTANT_ENH.ACCT_STATUS_OPEN GROUP BY B.TYPE, A.REGION_CD, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR 30+ DPD'); INSERT INTO MIS_KPI_ENH (MIS_ID, MIS_DATE,TYPE,REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE, PROCESS_SEQ_NO, BRANCH_CD) SELECT V_MIS_ID,I_INDATE,C.TYPE,A.REGION_CD,10, BMGLOBAL_ENH.COUNTRY_CD,NVL(SUM(A.CURR_BAL1),0),0,V_SEQ_NO,A.BRANCH_CD FROM P_ACCT_DTL_ENH A JOIN REF_GROUP_PRODUCT C ON TRIM(A.LOAN_TYPE)= TRIM(C.TYPE) AND A.MONTH_IDX = V_MONTH, REF_GROUP_DPD B WHERE GREATEST(NVL(A.AMT_DPD1,0),NVL(A.INT_DPD1,0)) BETWEEN B.GRP_BEG_RANGE AND B.GRP_END_RANGE AND B.DPD_CD BETWEEN 2 AND 10 GROUP BY C.TYPE, A.REGION_CD, A.BRANCH_CD ORDER BY C.TYPE, A.REGION_CD, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('INSERTING INTO MIS TABLE FOR 90+ DPD'); INSERT INTO

MIS_KPI_ENH (MIS_ID, MIS_DATE,TYPE,REGION_CD,MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO,BRANCH_CD) SELECT V_MIS_ID,I_INDATE,C.TYPE,A.REGION_CD,11,BMGLOBAL_ENH.COUNTRY_CD, NVL(SUM(A.CURR_BAL1),0), 0,V_SEQ_NO, A.BRANCH_CD FROM P_ACCT_DTL_ENH A JOIN REF_GROUP_PRODUCT C ON TRIM(A.LOAN_TYPE) = TRIM(C.TYPE) AND A.MONTH_IDX = V_MONTH, REF_GROUP_DPD B WHERE GREATEST(NVL(A.AMT_DPD1,0),NVL(A.INT_DPD1,0)) BETWEEN B.GRP_BEG_RANGE AND B.GRP_END_RANGE AND B.DPD_CD BETWEEN 4 AND 10 GROUP BY C.TYPE, A.REGION_CD, A.BRANCH_CD ORDER BY C.TYPE, A.REGION_CD, A.BRANCH_CD; V_TOT_CNT := V_TOT_CNT + SQL%ROWCOUNT; BMGLOBAL_ENH.PROCLOG('UPDATING MIS TABLE D VALUE FOR 30+ DPD AND 90+ DPD DVALUE '); FOR CUR IN CUR_TOTAL_AMT LOOP UPDATE_TO_MIS(CUR.TYPE,CUR.REGION_CD,10,CUR.ENR,CUR.BRANCH_CD); UPDATE_TO_MIS(CUR.TYPE,CUR.REGION_CD,11,CUR.ENR,CUR.BRANCH_CD); END LOOP; BMGLOBAL_ENH.PROCLOG('INSERTING MISSING SEQUENCES'); INSERT_MISSING; COMMIT ; REFRESH_VIEW1; COMMIT; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.SUCCESS,V_TOT_CNT,0,'SUCCESS', V_SEQ_NO,I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('KPI REPORT PROCESS COMPLETED SUCCESSFULLY FOR THE DATE : ' || I_INDATE); COMMIT ; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_MED; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL, 0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('KPI REPORT PROCESS FAILED FOR THE DATE : ' || I_INDATE); BMGLOBAL_ENH.ERRORLOG(SQLCODE,O_REMARKS); COMMIT; WHEN OTHERS THEN O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := TO_CHAR(SQLCODE) || SQLERRM; ROLLBACK; BMGLOBAL_ENH.ROLLBACK_MIS(I_INDATE, V_PROCESS_ID,'D'); BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('KPI REPORT PROCESS FAILED FOR THE DATE : ' || I_INDATE); BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); COMMIT; END DBP_MIS_KPI_ENH; /

L53 ¾

CREATE PROCEDURE DBP_MIS_LOAN_LINE_ANLS_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_MIS_LOAN_LINE_ANLS_ENH(I_CNT_CD IN VARCHAR2, I_INDATE IN DATE,O_STATUS OUT VARCHAR2,O_SEVERITY OUT VARCHAR2,O_REMARKS OUT VARCHAR2)IS V_SEQ_NO PLS_INTEGER := 0; V_EXCEP EXCEPTION; V_START_TIME DATE; V_TOT_CNT PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.MIS_LOAN_LINE_ANLS_ENH; V_YEARMONTH MIS_LOAN_LINE_ANLS_ENH.YEAR_MONTH%TYPE := TO_NUMBER(TO_CHAR(I_INDATE,'RRRRMM')); V_MIS_ID MIS_LOAN_LINE_ANLS_ENH.MIS_ID%TYPE := '1LLA02'; V_MONTH P_ACCT_DTL_ENH.MONTH_IDX%TYPE := TO_CHAR(I_INDATE,'MM'); BEGIN V_START_TIME := SYSDATE; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_SUCC; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_NIL; O_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_MIS_LOAN_LINE_ANLS_ENH'; BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS REPORT STARTED FOR THE DATE : ' || I_INDATE) ; V_YEARMONTH := TO_NUMBER(TO_CHAR(I_INDATE, 'RRRRMM')) ; BMGLOBAL_ENH.CHECK_PROCESS_STATUS(I_INDATE,V_PROCESS_ID,O_REMARKS,O_STATUS,V_SEQ_NO); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_ALD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS APPLICATION FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CLT,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'COLLATERAL FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.PRE_PROCESS,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'PRE-PROCESS DID NOT RUN SUCCESSFULLY FOR THE DATE '|| I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_PRH,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'PROJECT HOUSING FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '|| I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_FLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS FACILITY FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_DVT,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'DEVELOPERS FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG('INSERTING GUARANTOR LINE'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH (MIS_ID, YEAR_MONTH,PROD_CD, PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO, SUB_COMPANY_NAME,MAIN_COMPANY_NAME,DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,X.PROD_CD, X.PROD_PRG_CD,X.DEVELOPER_CDG, X.BRANCH_CD,1, BMGLOBAL_ENH.COUNTRY_CD, Z.PROYEK_GRLINE, 0,V_SEQ_NO, NULL,NULL,X.DPD_CD FROM (SELECT RGD.DPD_CD,C.PROD_CD,C.PROD_PRG_CD, CD.DEVELOPER_CDG,MAX(NVL(A.BRANCH_CD,'99')) AS BRANCH_CD FROM D_APPLN_LOS_DTL_BAK A JOIN D_COLLATERAL_DTL_ENH CD ON A.CUST_NO = CD.CUST_REF_NO JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN P_ACCT_DTL_ENH PAD ON A.APPLN_NO = PAD.LOS_APPLN_NO AND PAD.MONTH_IDX = V_MONTH, REF_GROUP_DPD RGD,DPD DRILL DOWN=(1) WHERE C.PROD_CD = '1' AND GREATEST(PAD.INT_DPD1, PAD.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY C.PROD_CD,RGD.DPD_CD,C.PROD_PRG_CD,CD.DEVELOPER_CDG) X JOIN (SELECT B.PROYEK_ID, B.PROYEK_GRLINE FROM D_PROYEK_HOUSING B) Z ON X.DEVELOPER_CDG = Z.PROYEK_ID; BMGLOBAL_ENH.PROCLOG('INSERTING LINE REALIZED'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH (MIS_ID, YEAR_MONTH,PROD_CD, PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME,MAIN_COMPANY_NAME, DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,C.PROD_CD, C.PROD_PRG_CD, CD.DEVELOPER_CDG, MAX(NVL(A.BRANCH_CD, '99')) AS BRANCH_CD,2, BMGLOBAL_ENH.COUNTRY_CD,SUM(F.SPPK_LOAN_AMT) LINE_REALISED, 0,V_SEQ_NO, NULL,NULL,0 FROM D_APPLN_LOS_DTL_BAK A JOIN D_COLLATERAL_DTL_ENH CD ON A.CUST_NO = CD.CUST_REF_NO JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN D_PROYEK_HOUSING D ON CD.DEVELOPER_CDG = D.PROYEK_ID JOIN D_FACILITY_LOS_DTL_BAK F ON A.APPLN_NO = F.APPLN_NO WHERE C.PROD_CD = '1'AND TRUNC(A.APPROVAL_DATE) <= TRUNC(I_INDATE) AND A.FINAL_APPLN_STATUS = '4.1' GROUP BY C.PROD_CD, C.PROD_PRG_CD,CD.DEVELOPER_CDG,A.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING TOTAL APPLICATIONS APPROVED'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD,MIS_SEQ_CD, MCOUNTRY_CD,MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO, SUB_COMPANY_NAME,MAIN_COMPANY_NAME, DPD_CD)

L54 SELECT V_MIS_ID, V_YEARMONTH,C.PROD_CD, C.PROD_PRG_CD, CD.DEVELOPER_CDG, MAX(NVL(A.BRANCH_CD, '99')) AS BRANCH_CD,3, BMGLOBAL_ENH.COUNTRY_CD,COUNT(A.APPLN_NO) CNT_APPLN, 0,V_SEQ_NO, NULL, NULL,0 FROM D_APPLN_LOS_DTL_BAK A JOIN D_COLLATERAL_DTL_ENH CD ON A.CUST_NO = CD.CUST_REF_NO JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN D_PROYEK_HOUSING D ON CD.DEVELOPER_CDG = D.PROYEK_ID WHERE C.PROD_CD = '1'AND TRUNC(A.APPROVAL_DATE) <= TRUNC(I_INDATE) AND A.FINAL_APPLN_STATUS = '4.1' GROUP BY C.PROD_CD, C.PROD_PRG_CD,CD.DEVELOPER_CDG, A.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING AMOUNT DISBURSED/RELEASED'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH(MIS_ID,YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME,MAIN_COMPANY_NAME,DPD_CD) SELECT V_MIS_ID, V_YEARMONTH, C.PROD_CD, C.PROD_PRG_CD, CD.DEVELOPER_CDG,MAX(NVL(A.BRANCH_CD,'99')) AS BRANCH_CD,4,BMGLOBAL_ENH.COUNTRY_CD,SUM(B.LTD_RELEASE_AMT1) AMT_RELEASED,0,V_SEQ_NO,NULL, NULL,RGD.DPD_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH B ON A.APPLN_NO = B.LOS_APPLN_NO AND B.MONTH_IDX = V_MONTH JOIN REF_PRODUCT_PROGRAM C ON B.PRG_CODE = C.PROD_PRG_CD JOIN D_COLLATERAL_DTL_ENH CD ON A.CUST_NO = CD.CUST_REF_NO JOIN D_PROYEK_HOUSING D ON CD.DEVELOPER_CDG = D.PROYEK_ID, REF_GROUP_DPD RGD WHERE C.PROD_CD = '1'AND TRUNC(A.DISBMT_END_DATE) <= TRUNC(I_INDATE) AND GREATEST(B.INT_DPD1, B.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY RGD.DPD_CD,C.PROD_CD, C.PROD_PRG_CD,CD.DEVELOPER_CDG,A.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING NO. OF APPLICATIONS DISBURSED'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME,MAIN_COMPANY_NAME,DPD_CD) SELECT V_MIS_ID,V_YEARMONTH,C.PROD_CD, C.PROD_PRG_CD, CD.DEVELOPER_CDG, MAX(NVL(A.BRANCH_CD,'99')) AS BRANCH_CD,5, BMGLOBAL_ENH.COUNTRY_CD,COUNT(B.ACCT_NO) CNT_APPLN,0,V_SEQ_NO,NULL,NULL,RGD.DPD_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH B ON A.APPLN_NO = B.LOS_APPLN_NO AND B.MONTH_IDX = V_MONTH JOIN REF_PRODUCT_PROGRAM C ON B.PRG_CODE = C.PROD_PRG_CD JOIN D_COLLATERAL_DTL_ENH CD ON A.CUST_NO = CD.CUST_REF_NO JOIN D_PROYEK_HOUSING D ON CD.DEVELOPER_CDG = D.PROYEK_ID, REF_GROUP_DPD RGD WHERE C.PROD_CD = '1' AND TRUNC(A.DISBMT_END_DATE) <= TRUNC(I_INDATE) AND GREATEST(B.INT_DPD1, B.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY RGD.DPD_CD,C.PROD_CD, C.PROD_PRG_CD,CD.DEVELOPER_CDG,A.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING DEVELOPER GUARANTOR LINE'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD, COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO, SUB_COMPANY_NAME,MAIN_COMPANY_NAME, DPD_CD) SELECT V_MIS_ID,V_YEARMONTH,X.PROD_CD, X.PROD_PRG_CD,X.DEVELOPER_CDG, X.BRANCH_CD,6, BMGLOBAL_ENH.COUNTRY_CD, Z.DEVELOPER_TOTGRLINE, 0,V_SEQ_NO, NULL, NULL, X.DPD_CD FROM (SELECT RGD.DPD_CD,C.PROD_CD,C.PROD_PRG_CD, CD.DEVELOPER_CDG,MAX(NVL(A.BRANCH_CD, '99')) FROM D_APPLN_LOS_DTL_BAK A JOIN D_COLLATERAL_DTL_ENH ON A.CUST_NO = CD.CUST_REF_NO JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN P_ACCT_DTL_ENH PAD ON A.APPLN_NO = PAD.LOS_APPLN_NO AND PAD.MONTH_IDX = V_MONTH, REF_GROUP_DPD RGD,DPD DRILL DOWN=(1) WHERE C.PROD_CD = '1' AND GREATEST(PAD.INT_DPD1, PAD.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY C.PROD_CD,RGD.DPD_CD,C.PROD_PRG_CD,CD.DEVELOPER_CDG) X JOIN (SELECT D.PROYEK_ID, B.DEVELOPER_TOTGRLINE FROM D_DEVELOPER_DTL B JOIN D_PROYEK_HOUSING D ON D.DEVELOPER_ID = B.DEVELOPER_CODE ) Z ON X.DEVELOPER_CDG = Z.PROYEK_ID; BMGLOBAL_ENH.PROCLOG('INSERTING BAKI DEBIT'); INSERT INTO MIS_LOAN_LINE_ANLS_ENH (MIS_ID, YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO, SUB_COMPANY_NAME,MAIN_COMPANY_NAME, DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,C.PROD_CD, C.PROD_PRG_CD,CD.DEVELOPER_CDG, MAX(NVL(A.BRANCH_CD, '99')) AS BRANCH_CD,7, BMGLOBAL_ENH.COUNTRY_CD,SUM(B.CURR_BAL1) BAKI_DEBET,0,V_SEQ_NO,NULL, NULL, RGD.DPD_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH B ON A.APPLN_NO = B.LOS_APPLN_NO AND B.MONTH_IDX = V_MONTH JOIN REF_PRODUCT_PROGRAM C ON B.PRG_CODE = C.PROD_PRG_CD JOIN D_COLLATERAL_DTL_ENH CD ON A.CUST_NO = CD.CUST_REF_NO JOIN D_PROYEK_HOUSING D ON CD.DEVELOPER_CDG = D.PROYEK_ID, REF_GROUP_DPD RGD WHERE C.PROD_CD = '1' AND TRUNC(A.DISBMT_END_DATE) <= TRUNC(I_INDATE) AND GREATEST(B.INT_DPD1, B.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY RGD.DPD_CD,C.PROD_CD, C.PROD_PRG_CD,CD.DEVELOPER_CDG, A.BRANCH_CD; COMMIT; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.SUCCESS,V_TOT_CNT,0, 'SUCCESS',V_SEQ_NO,I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS REPORT COMPLETED SUCCESSFULLY FOR THE DATE:'|| I_INDATE);COMMIT; EXCEPTION WHEN V_EXCEP THENROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_MED; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.FAIL,0,V_TOT_CNT,O_REMARKS, V_SEQ_NO,I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS REPORT FAILED FOR THE DATE : ' ||I_INDATE); BMGLOBAL_ENH.ERRORLOG(0, O_REMARKS);COMMIT; WHEN OTHERS THENROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := TO_CHAR(SQLCODE) || SQLERRM; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.FAIL,0,V_TOT_CNT, O_REMARKS,V_SEQ_NO,I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS REPORT FAILED FOR THE DATE : ' ||I_INDATE); BMGLOBAL_ENH.ERRORLOG(SQLCODE, ' '); COMMIT; END DBP_MIS_LOAN_LINE_ANLS_ENH;/

L55 ¾ CREATE PROCEDURE DBP_MIS_MTR_LOAN_LINE_ANLS_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_MIS_MTR_LOAN_LINE_ANLS_ENH(I_CNT_CD IN VARCHAR2, I_INDATE IN DATE,O_STATUS OUT VARCHAR2,O_SEVERITY OUT VARCHAR2,O_REMARKS OUT VARCHAR2)IS V_SEQ_NO PLS_INTEGER := 0; V_EXCEP EXCEPTION; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.MIS_MITRA_LOAN_LINE_ANLS_ENH; V_START_TIME DATE; V_TOT_CNT PLS_INTEGER := 0; V_YEARMONTH MIS_LOAN_LINE_ANLS_ENH.YEAR_MONTH%TYPE := TO_NUMBER(TO_CHAR(I_INDATE, 'RRRRMM')); V_MIS_ID MIS_LOAN_LINE_ANLS_ENH.MIS_ID%TYPE := '1LLA03'; V_MONTH P_ACCT_DTL_ENH.MONTH_IDX%TYPE := TO_CHAR(I_INDATE,'MM'); BEGIN V_START_TIME := SYSDATE; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_SUCC; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_NIL; O_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_MIS_MTR_LOAN_LINE_ANLS_ENH'; BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS MITRAKARYA REPORT STARTED FOR THE DATE : ' || I_INDATE); V_YEARMONTH := TO_NUMBER(TO_CHAR(I_INDATE, 'RRRRMM')); BMGLOBAL_ENH.CHECK_PROCESS_STATUS(I_INDATE,V_PROCESS_ID,O_REMARKS,O_STATUS,V_SEQ_NO); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE, BMCONSTANT_ENH.FTD_CRL, O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'COMPANY CREDIT DTL ENH MAPPING DID NOT RUN SUCCESSFULLY FOR THE DATE'||I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_ALD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS APPLICATION FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.PRE_PROCESS,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'PRE-PROCESS DID NOT RUN SUCCESSFULLY FOR THE DATE '|| I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_FLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS FACILITY FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE;RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG('INSERTING GUARANTOR LINE'); INSERT INTO MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD, PROD_PRG_CD,COMPANY_CD, BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO, SUB_COMPANY_NAME,MAIN_COMPANY_NAME, DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,X.PROD_CD, X.PROD_PRG_CD,X.COMPANY_CD, X.BRANCH_CD,1,BMGLOBAL_ENH.COUNTRY_CD, Z.GUARANTOR_LINE, 0,V_SEQ_NO, Z.COMPANY_NAME,Z.COMPANY_TYPE, X.DPD_CD FROM (SELECT RGD.DPD_CD, NVL(C.PROD_CD,'99') PROD_CD,NVL(C.PROD_PRG_CD,'99') PROD_PRG_CD, A.COMPANY_CD,MAX(NVL(A.BRANCH_CD,'99')) FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN P_ACCT_DTL_ENH PAD ON A.APPLN_NO = PAD.LOS_APPLN_NO AND PAD.MONTH_IDX = V_MONTH, REF_GROUP_DPD RGD WHERE C.PROD_CD = '4'AND TRUNC(A.APPROVAL_DATE) <= TRUNC(I_INDATE) AND GREATEST(PAD.INT_DPD1, PAD.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY C.PROD_CD,RGD.DPD_CD,C.PROD_PRG_CD,A.COMPANY_CD) X JOIN (SELECT B.COMPANY_CD, B.GUARANTOR_LINE, B.COMPANY_NAME, B.COMPANY_TYPE FROM D_COMPANY_CREDIT_DTL_ENH B) Z ON X.COMPANY_CD = Z.COMPANY_CD; BMGLOBAL_ENH.PROCLOG('INSERTING LINE REALIZED'); INSERT INTO MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID, YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME,MAIN_COMPANY_NAME,DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,NVL(C.PROD_CD,'99') PROD_CD, NVL(C.PROD_PRG_CD,'99') PROD_PRG_CD, A.COMPANY_CD,MAX(NVL(A.BRANCH_CD, '99')) AS BRANCH_CD,2,BMGLOBAL_ENH.COUNTRY_CD, SUM(F.SPPK_LOAN_AMT) LINE_REALISED, 0,V_SEQ_NO, D.COMPANY_NAME, D.COMPANY_TYPE, 0 FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN D_COMPANY_CREDIT_DTL_ENH D ON A.COMPANY_CD = D.COMPANY_CD JOIN D_FACILITY_LOS_DTL_BAK F ON A.APPLN_NO = F.APPLN_NO WHERE C.PROD_CD = '4'AND TRUNC(A.APPROVAL_DATE) <= TRUNC(I_INDATE) AND A.FINAL_APPLN_STATUS = '4.1' GROUP BY C.PROD_CD,C.PROD_PRG_CD,A.COMPANY_CD,D.COMPANY_NAME,D.COMPANY_TYPE,A.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING TOTAL APPLICATIONS APPROVED'); INSERT INTO MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD, PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME,MAIN_COMPANY_NAME,DPD_CD) SELECT V_MIS_ID,V_YEARMONTH,NVL(C.PROD_CD,'99') PROD_CD, NVL(C.PROD_PRG_CD,'99') PROD_PRG_CD, A.COMPANY_CD, MAX(NVL(A.BRANCH_CD, '99'))AS BRANCH_CD,3,BMGLOBAL_ENH.COUNTRY_CD, COUNT(A.APPLN_NO) CNT_APPLN,0,V_SEQ_NO, D.COMPANY_NAME,D.COMPANY_TYPE,0 FROM D_APPLN_LOS_DTL_BAK A JOIN REF_PRODUCT_PROGRAM C ON A.PRG_CODE = C.PROD_PRG_CD JOIN D_COMPANY_CREDIT_DTL_ENH D ON A.COMPANY_CD = D.COMPANY_CD WHERE C.PROD_CD = '4'AND TRUNC(A.APPROVAL_DATE) <= TRUNC(I_INDATE) AND A.FINAL_APPLN_STATUS = '4.1' GROUP BY C.PROD_CD, C.PROD_PRG_CD,A.COMPANY_CD,D.COMPANY_NAME,D.COMPANY_TYPE,A.BRANCH_CD;

L56 BMGLOBAL_ENH.PROCLOG('INSERTING AMOUNT DISBURSED/RELEASED'); INSERT INTO MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD,MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME, MAIN_COMPANY_NAME, DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,NVL(C.PROD_CD,'99') PROD_CD, NVL(C.PROD_PRG_CD,'99') PROD_PRG_CD, B.COMPANY_CD, MAX(NVL(B.BRANCH_CD,'99')) AS BRANCH_CD,4,BMGLOBAL_ENH.COUNTRY_CD, SUM(B.LTD_RELEASE_AMT1) AMT_RELEASED, 0,V_SEQ_NO, D.COMPANY_NAME, D.COMPANY_TYPE, RGD.DPD_CD FROM P_ACCT_DTL_ENH B JOIN REF_PRODUCT_PROGRAM C ON B.PRG_CODE = C.PROD_PRG_CD AND B.MONTH_IDX = V_MONTH JOIN D_COMPANY_CREDIT_DTL_ENH D ON B.COMPANY_CD = D.COMPANY_CD REF_GROUP_DPD RGD WHERE C.PROD_CD = '4'AND TRUNC(B.DISBMT_END_DATE) <= TRUNC(I_INDATE) AND GREATEST(B.INT_DPD1, B.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY RGD.DPD_CD,C.PROD_CD,C.PROD_PRG_CD,B.COMPANY_CD,D.COMPANY_NAME,D.COMPANY_TYPE,B.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING NO. OF APPLICATIONS DISBURSED'); INSERT INTO MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD,PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO, SUB_COMPANY_NAME,MAIN_COMPANY_NAME, DPD_CD) SELECT V_MIS_ID, V_YEARMONTH,NVL(C.PROD_CD,'99') PROD_CD, NVL(C.PROD_PRG_CD,'99') PROD_PRG_CD, B.COMPANY_CD, MAX(NVL(B.BRANCH_CD,'99')) AS BRANCH_CD,5, BMGLOBAL_ENH.COUNTRY_CD,COUNT(B.ACCT_NO) CNT_APPLN, 0, V_SEQ_NO, D.COMPANY_NAME, D.COMPANY_TYPE, RGD.DPD_CD FROM P_ACCT_DTL_ENH B JOIN REF_PRODUCT_PROGRAM C ON B.PRG_CODE = C.PROD_PRG_CD AND B.MONTH_IDX= V_MONTH JOIN D_COMPANY_CREDIT_DTL_ENH D ON B.COMPANY_CD = D.COMPANY_CD, REF_GROUP_DPD RGD WHERE C.PROD_CD = '4' AND TRUNC(B.DISBMT_END_DATE) <= TRUNC(I_INDATE) AND GREATEST(B.INT_DPD1, B.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY RGD.DPD_CD,C.PROD_CD, C.PROD_PRG_CD,B.COMPANY_CD,D.COMPANY_NAME, D.COMPANY_TYPE,B.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('INSERTING BAKI DEBIT'); INSERT INTO MIS_MITRA_LOAN_LINE_ANLS_ENH (MIS_ID,YEAR_MONTH,PROD_CD, PROD_PRG_CD,COMPANY_CD,BRANCH_CD, MIS_SEQ_CD,COUNTRY_CD,MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO,SUB_COMPANY_NAME,MAIN_COMPANY_NAME,DPD_CD) SELECT V_MIS_ID, V_YEARMONTH, NVL(C.PROD_CD,'99') PROD_CD, NVL(C.PROD_PRG_CD,'99') PROD_PRG_CD, A.COMPANY_CD, MAX(NVL(A.BRANCH_CD, '99')) AS BRANCH_CD,7, BMGLOBAL_ENH.COUNTRY_CD,SUM(B.CURR_BAL1) BAKI_DEBET, 0,V_SEQ_NO, D.COMPANY_NAME, D.COMPANY_TYPE, RGD.DPD_CD FROM D_APPLN_LOS_DTL_BAK A JOIN P_ACCT_DTL_ENH B ON A.APPLN_NO = B.LOS_APPLN_NO AND B.MONTH_IDX = V_MONTH JOIN REF_PRODUCT_PROGRAM C ON B.PRG_CODE = C.PROD_PRG_CD JOIN D_COMPANY_CREDIT_DTL_ENH D ON A.COMPANY_CD = D.COMPANY_CD, REF_GROUP_DPD RGD WHERE C.PROD_CD = '4'AND TRUNC(A.DISBMT_END_DATE) <= TRUNC(I_INDATE) AND GREATEST(B.INT_DPD1, B.AMT_DPD1) BETWEEN RGD.GRP_BEG_RANGE AND RGD.GRP_END_RANGE GROUP BY RGD.DPD_CD,C.PROD_CD, C.PROD_PRG_CD,A.COMPANY_CD,D.COMPANY_NAME, D.COMPANY_TYPE, A.BRANCH_CD; COMMIT; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.SUCCESS,V_TOT_CNT,0,'SUCCESS',V_SEQ_NO, I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS MITRAKARYA REPORT COMPLETED SUCCESSFULLY FOR THE DATE:'|| I_INDATE); COMMIT; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_MED; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.FAIL,0,V_TOT_CNT,O_REMARKS, V_SEQ_NO, I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS MITRAKARYA REPORT FAILED FOR THE DATE : ' || I_INDATE); BMGLOBAL_ENH.ERRORLOG(0, O_REMARKS);COMMIT; WHEN OTHERS THEN ROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := TO_CHAR(SQLCODE) || SQLERRM; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.FAIL,0,V_TOT_CNT,O_REMARKS, V_SEQ_NO,I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG('LOAN LINE ANALYSIS MITRAKARYA REPORT FAILED FOR THE DATE : ' ||I_INDATE); BMGLOBAL_ENH.ERRORLOG(SQLCODE, ' '); COMMIT; END DBP_MIS_MTR_LOAN_LINE_ANLS_ENH; /

L57 ¾ CREATE PROCEDURE DBP_MIS_PROFIT_LOSS_ALL CREATE OR REPLACE PROCEDURE BMCLN2.DBP_MIS_PROFIT_LOSS_ALL( I_CNT_CD IN VARCHAR2, I_INDATE IN DATE, O_STATUS OUT VARCHAR2, O_SEVERITY OUT VARCHAR2, O_REMARKS OUT VARCHAR2 )IS V_SEQ_NO PLS_INTEGER := 0; V_IDF NUMBER(5); V_EXCEP EXCEPTION; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.MIS_PROFIT_LOSS_ALL; V_START_TIME DATE; V_TOT_CNT PLS_INTEGER := 0; V_YEARMONTH NUMBER(6) := TO_NUMBER(TO_CHAR(I_INDATE,'RRRRMM')); V_PREV_YEARMONTH NUMBER(6) := TO_NUMBER(TO_CHAR(ADD_MONTHS(I_INDATE,-1),'RRRRMM')) ; V_MIS_ID MIS_PROFIT_LOSS_ALL.MIS_ID%TYPE := '1PLR01'; V_INT_EXP MIS_PROFIT_LOSS_ALL.MIS_NVALUE%TYPE := 0 ; V_PREV_MONTH P_ACCT_DTL_ENH.MONTH_IDX%TYPE := TO_CHAR(ADD_MONTHS(I_INDATE,-1),'MM'); V_MONTH P_ACCT_DTL_ENH.MONTH_IDX%TYPE := TO_CHAR(I_INDATE,'MM'); CURSOR C1 IS SELECT A.PROD_CD , SUM(B.CURR_BAL1),RATIO_TO_REPORT(SUM(B.CURR_BAL1))OVER()*100 AS PL_PER FROM P_ACCT_DTL_ENH B JOIN REF_PRODUCT_PROGRAM A ON A.PROD_PRG_CD = B.PRG_CODE AND B.MONTH_IDX = V_MONTH GROUP BY A.PROD_CD; CURSOR C2 IS SELECT IDENTIFIER,SUM(ABS(CURR_BAL)) AS CURR_BAL FROM D_GL_DTL_ENH WHERE TRUNC(PERIOD,'MONTH') = TRUNC(I_INDATE,'MONTH')AND IDENTIFIER IN (3,4,5,6,7,8,10) GROUP BY IDENTIFIER; CURSOR C3 IS SELECT PROD_CD,REGION_CD,NVL(SUM(MIS_NVALUE),0) AS MIS_DVALUE FROM MIS_PROFIT_LOSS_ALL WHERE YEAR_MONTH = V_PREV_YEARMONT AND MIS_SEQ_CD = 1 GROUP BY PROD_CD, REGION_CD ; CURSOR C4 IS SELECT X.PROD_CD, SUM((X.CURR_OS - X.PREV_OS) * X.PPAP_PERCENT) AS MIS_VALUE FROM (SELECT C.PROD_CD,B.COLL_STATUS_CD,B.PPAP_PERCENT,SUM(A.CURR_BAL1) AS CURR_OS,SUM(NVL(A2.CURR_BAL1,0)) FROM P_ACCT_DTL_ENH A LEFT OUTER JOIN ( SELECT ACCT_NO, CURR_BAL1 FROM P_ACCT_DTL_ENH WHERE MONTH_IDX = V_PREV_MONTH) A2 ON A.ACCT_NO = A2.ACCT_NO JOIN REF_PRODUCT_PROGRAM C ON C.PROD_PRG_CD = A.PRG_CODE, REF_GROUP_PPAP B WHERE A.MONTH_IDX = V_MONTH AND A.ACCT_SUB_STATUS1 IN (BMCONSTANT_ENH.ACCT_SUBSTATUS_OPEN) AND GREATEST(A.AMT_DPD1,A.INT_DPD1) BETWEEN B.GRP_BEG_RANGE AND B.GRP_END_RANGE GROUP BY C.PROD_CD, B.COLL_STATUS_CD, B.PPAP_PERCENT) X GROUP BY X.PROD_CD ; CURSOR C5 IS SELECT D.PROD_CD, COUNT(A.ACCT_NO) AS TOTACCTS,MAX(D.ACOST_FACTOR1) AS COST_FACTOR1 FROM D_FACILITY_LOS_DTL_BAK FL JOIN P_ACCT_DTL_ENH A ON FL.APPLN_NO = A.LOS_APPLN_NO AND A.MONTH_IDX = V_MONTH JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD JOIN REF_PRODUCT D ON D.PROD_CD = B.PROD_CD WHERE TRUNC(FL.DISBMT_START_DATE) >= TRUNC(I_INDATE,'MONTH') AND TRUNC(NVL(A.DISBMT_END_DATE,BMCONSTANT_ENH.NULL_DATE)) <= I_INDATE GROUP BY D.PROD_CD ; CURSOR SELECT FROM

C6 IS D.PROD_CD,COUNT(A.ACCT_NO),MAX(D.ACOST_PERCENT)COST_PERCENT,MAX(D.ACOST_FACTOR2) COST_FACTOR2 P_ACCT_DTL_ENH A LEFT OUTER JOIN ( SELECT ACCT_NO, ACCT_STATUS1 FROM P_ACCT_DTL_ENH WHERE MONTH_IDX = V_PREV_MONTH ) A2 ON A.ACCT_NO = A2.ACCT_NO JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD JOIN REF_PRODUCT D ON D.PROD_CD = B.PROD_CD WHERE A.MONTH_IDX = V_MONTH AND A2.ACCT_STATUS1 = BMCONSTANT_ENH.ACCT_STATUS_OPEN GROUP BY D.PROD_CD ; CURSOR SELECT FROM

C7 IS D.PROD_CD, COUNT(A.ACCT_NO) AS TOTACCTS, MAX(D.ACOST_FACTOR3) AS COST_FACTOR3 P_ACCT_DTL_ENH A LEFT OUTER JOIN ( SELECT ACCT_NO, ACCT_STATUS1 FROM P_ACCT_DTL_ENH WHERE MONTH_IDX = V_PREV_MONTH ) A2 ON A.ACCT_NO = A2.ACCT_NO JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD JOIN REF_PRODUCT D ON D.PROD_CD = B.PROD_CD WHERE A.MONTH_IDX = V_MONTH AND A2.ACCT_STATUS1 = BMCONSTANT_ENH.ACCT_STATUS_OPEN GROUP BY D.PROD_CD ; BEGIN V_START_TIME := SYSDATE; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_SUCC; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_NIL; O_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_MIS_PROFIT_LOSS_ALL';

L58 BMGLOBAL_ENH.PROCLOG('DRAFT PROFIT & LOSS ALL REPORT STARTED FOR THE DATE : ' || I_INDATE); BMGLOBAL_ENH.CHECK_PROCESS_STATUS(I_INDATE,V_PROCESS_ID,O_REMARKS,O_STATUS,V_SEQ_NO); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_GLF,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'GL FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.PRE_PROCESS,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'PRE-PROCESS DID NOT RUN SUCCESSFULLY FOR THE DATE '|| I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_FLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS FACILITY FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG('UPDATING TMP_PROD_REGION TABLE FOR PL_PERCENT COLUMN'); EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_PROD_REGION_ENH DROP STORAGE ' ; INSERT INTO TMP_PROD_REGION_ENH SELECT I_INDATE, A.PROD_CD, REGION_CD,SUM(B.CURR_BAL1),RATIO_TO_REPORT(SUM(B.CURR_BAL1)),OVER()*100 FROM P_ACCT_DTL_ENH B JOIN REF_PRODUCT_PROGRAM A ON A.PROD_PRG_CD = B.PRG_CODE AND B.MONTH_IDX = V_MONTH WHERE B.ACCT_STATUS1 = BMCONSTANT_ENH.ACCT_STATUS_OPEN GROUP BY A.PROD_CD, B.REGION_CD; V_INT_EXP := 0 ; SELECT SUM(ABS(CURR_BAL)) INTO V_INT_EXP FROM D_GL_DTL_ENH WHERE TRUNC(PERIOD,'MONTH') = TRUNC(I_INDATE,'MONTH') AND IDENTIFIER IN (1,2) ; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR INTEREST INCOME'); INSERT INTO MIS_PROFIT_LOSS_ALL( MIS_ID,YEAR_MONTH, PROD_CD,REGION_CD, MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE, MIS_DVALUE, PROCESS_SEQ_NO) SELECT V_MIS_ID,V_YEARMONTH, PROD_CD,REGION_CD,1, BMGLOBAL_ENH.COUNTRY_CD, V_INT_EXP * PL_PERCENT/100,0, V_SEQ_NO FROM TMP_PROD_REGION_ENH ; BMGLOBAL_ENH.PROCLOG('UPDATING REF_PRODUCT TABLE FOR PL_PERCENT COLUMN'); UPDATE REF_PRODUCT SET PL_PERCENT = 0; FOR I IN C1 LOOP UPDATE REF_PRODUCT SET PL_PERCENT = I.PL_PER WHERE PROD_CD = I.PROD_CD; END LOOP; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR INTEREST EXPENSE NVALUE'); INSERT INTO MIS_PROFIT_LOSS_ALL( MIS_ID,YEAR_MONTH,PROD_CD,REGION_CD,MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO) SELECT V_MIS_ID,V_YEARMONTH,PROD_CD,REGION_CD, 2,BMGLOBAL_ENH.COUNTRY_CD, NVL(SUM(MIS_NVALUE),0,0,V_SEQ_NO FROM MIS_PROFIT_LOSS_ALL WHERE YEAR_MONTH = V_PREV_YEARMONTH AND MIS_SEQ_CD = 2 GROUP BY PROD_CD, REGION_CD ; FOR R3 IN C3 LOOP UPDATE MIS_PROFIT_LOSS_ALL SET MIS_DVALUE = R3.MIS_DVALUE WHERE MIS_ID = V_MIS_ID AND YEAR_MONTH = V_YEARMONTH AND PROD_CD = R3.PROD_CD AND REGION_CD = R3.REGION_CD AND MIS_SEQ_CD = 2 ; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO MIS_PROFIT_LOSS_ALL(MIS_ID,YEAR_MONTH,PROD_CD,REGION_CD, MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE, MIS_DVALUE,PROCESS_SEQ_NO) VALUES(V_MIS_ID, V_YEARMONTH, R3.PROD_CD,R3.REGION_CD,2,BMGLOBAL_ENH.COUNTRY_CD, 0, R3.MIS_DVALUE, V_SEQ_NO) ; END IF ; END LOOP ; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR PROVISION,INSURANCE AND OTHER FEES,SALARIES ,OTHER EXPENSES'); FOR J IN C2 LOOP INSERT INTO MIS_PROFIT_LOSS_ALL(MIS_ID,YEAR_MONTH, PROD_CD, REGION_CD,MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE, PROCESS_SEQ_NO) SELECT V_MIS_ID,V_YEARMONTH,PROD_CD,'99’,DECODE(J.IDENTIFIER,5,7,6,8,7,9,8,10,3,3,4,6,10,4), BMGLOBAL_ENH.COUNTRY_CD,(J.CURR_BAL * PL_PERCENT)/100,0, V_SEQ_NO FROM REF_PRODUCT; END LOOP;

L59 BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR ADMIN FEES'); INSERT INTO MIS_PROFIT_LOSS_ALL(MIS_ID,YEAR_MONTH,PROD_CD,REGION_CD, MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE, MIS_DVALUE, PROCESS_SEQ_NO) SELECT V_MIS_ID, V_YEARMONTH, D.PROD_CD,'99', 5, BMGLOBAL_ENH.COUNTRY_CD, COUNT(A.ACCT_NO) * MAX(D.PL_FACTOR), 0, V_SEQ_NO FROM D_FACILITY_LOS_DTL_BAK FL JOIN P_ACCT_DTL_ENH A ON FL.APPLN_NO = A.LOS_APPLN_NO AND A.MONTH_IDX = V_MONTH JOIN REF_PRODUCT_PROGRAM B ON A.PRG_CODE = B.PROD_PRG_CD JOIN REF_PRODUCT D ON B.PROD_CD = D.PROD_CD WHERE TRUNC(FL.DISBMT_START_DATE) >= TRUNC(I_INDATE,'MONTH') AND TRUNC(NVL(A.DISBMT_END_DATE,I_INDATE)) <= TRUNC(I_INDATE) GROUP BY D.PROD_CD ; BMGLOBAL_ENH.PROCLOG('COMPUTING PPAP'); FOR R4 IN C4 LOOP INSERT INTO MIS_PROFIT_LOSS_ALL( MIS_ID,YEAR_MONTH,PROD_CD,REGION_CD,MIS_SEQ_CD,COUNTRY_CD, MIS_NVALUE,MIS_DVALUE,PROCESS_SEQ_NO) VALUES(V_MIS_ID,V_YEARMONTH,R4.PROD_CD,'99’,11,BMGLOBAL_ENH.COUNTRY_CD,NVL(R4.MIS_VALUE,0),0, V_SEQ_NO); END LOOP ; BMGLOBAL_ENH.PROCLOG('INSERTING MIS TABLE FOR ALLOCATED COST'); FOR R5 IN C5 LOOP INSERT INTO MIS_PROFIT_LOSS_ALL(MIS_ID, YEAR_MONTH,PROD_CD, REGION_CD, MIS_SEQ_CD, COUNTRY_CD,MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO) VALUES (V_MIS_ID,V_YEARMONTH, R5.PROD_CD,'99',12,BMGLOBAL_ENH.COUNTRY_CD, (R5.TOTACCTS * R5.COST_FACTOR1),0,V_SEQ_NO) ; END LOOP ; BMGLOBAL_ENH.PROCLOG('UPDATING MIS TABLE FOR ALLOCATED COST'); FOR R6 IN C6 LOOP UPDATE MIS_PROFIT_LOSS_ALL SET MIS_NVALUE = NVL(MIS_NVALUE,0) + ROUND((R6.TOTACCTS * R6.COST_PERCENT * R6.COST_FACTOR2)/100,2) WHERE MIS_ID = V_MIS_ID AND YEAR_MONTH = V_YEARMONTH AND PROD_CD = R6.PROD_CD AND REGION_CD = '99'AND MIS_SEQ_CD = 12 ; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO MIS_PROFIT_LOSS_ALL( MIS_ID,YEAR_MONTH,PROD_CD,REGION_CD, MIS_SEQ_CD, COUNTRY_CD,MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO) VALUES(V_MIS_ID, V_YEARMONTH, R6.PROD_CD,'99', 12, BMGLOBAL_ENH.COUNTRY_CD, ROUND((R6.TOTACCTS * R6.COST_PERCENT * R6.COST_FACTOR2)/100,0),0,V_SEQ_NO) ; END IF ; END LOOP ; FOR R7 IN C7 LOOP UPDATE MIS_PROFIT_LOSS_ALL SET MIS_NVALUE = NVL(MIS_NVALUE,0) + ROUND((R7.TOTACCTS * R7.COST_FACTOR3),0) WHERE MIS_ID = V_MIS_ID AND YEAR_MONTH = V_YEARMONTH AND PROD_CD = R7.PROD_CD AND REGION_CD = '99' AND MIS_SEQ_CD = 12 ; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO MIS_PROFIT_LOSS_ALL( MIS_ID, YEAR_MONTH, PROD_CD,REGION_CD, MIS_SEQ_CD, COUNTRY_CD, MIS_NVALUE,MIS_DVALUE, PROCESS_SEQ_NO) VALUES(V_MIS_ID, V_YEARMONTH, R7.PROD_CD,'99', 12, BMGLOBAL_ENH.COUNTRY_CD, ROUND((R7.TOTACCTS * R7.COST_FACTOR3),0),0,V_SEQ_NO) ; END IF ; END LOOP ; COMMIT; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS, V_TOT_CNT,0, O_REMARKS, V_SEQ_NO, I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG ('DRAFT PROFIT & LOSS ALL REPORT COMPLETED SUCCESSFULLY FOR THE DATE : '||I_INDATE) ; COMMIT ; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_MED; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('DRAFT PROFIT & LOSS ALL REPORT FAILED FOR THE DATE : ' || I_INDATE) ; BMGLOBAL_ENH.ERRORLOG(0,O_REMARKS); COMMIT; WHEN OTHERS THEN O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := TO_CHAR(SQLCODE) || SQLERRM; ROLLBACK; BMGLOBAL_ENH.ROLLBACK_MIS(I_INDATE, V_PROCESS_ID); BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('DRAFT PROFIT & LOSS ALL REPORT FAILED FOR THE DATE : ' || I_INDATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); COMMIT; END DBP_MIS_PROFIT_LOSS_ALL; /

L60 ¾ PROCEDURE DBP_PRE_PROCESS CREATE OR REPLACE PROCEDURE BMCLN2.DBP_PRE_PROCESS(I_CNT_CD IN VARCHAR2, I_INDATE IN DATE, O_STATUS OUT VARCHAR2,O_SEVERITY OUT VARCHAR2,O_REMARKS OUT VARCHAR2 )IS TYPE TY_ROWID IS TABLE OF VARCHAR2(22) INDEX BY BINARY_INTEGER; TYPE TY_AMT_DPD IS TABLE OF P_ACCT_DTL_ENH.AMT_DPD1%TYPE INDEX BY BINARY_INTEGER; TYPE TY_INT_DPD IS TABLE OF P_ACCT_DTL_ENH.INT_DPD1%TYPE INDEX BY BINARY_INTEGER; TYPE TY_LOS_APPLN IS TABLE OF P_ACCT_DTL_ENH.LOS_APPLN_NO%TYPE INDEX BY BINARY_INTEGER; TYPE TY_REGION_CD IS TABLE OF P_ACCT_DTL_ENH.REGION_CD%TYPE INDEX BY BINARY_INTEGER; TYPE TY_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER; OBJ_REGION_CD TY_REGION_CD; OBJ_DROWID TY_ROWID; OBJ_PROWID TY_ROWID; OBJ_AMT_DPD TY_AMT_DPD; OBJ_INT_DPD TY_INT_DPD; OBJ_ROWID TY_ROWID; OBJ_LOS_APPLN TY_LOS_APPLN; OBJ_AROWID TY_ROWID; OBJ_LROWID TY_ROWID; OBJ_LPROWID TY_ROWID; OBJ_LDROWID TY_ROWID; OBJ_DATE TY_DATE; V_REC_COMMIT PLS_INTEGER := 50000; V_CNT PLS_INTEGER := 0; V_COUNT PLS_INTEGER := 0; V_CTR_LOOP PLS_INTEGER := 0; V_MOD_CNT PLS_INTEGER := 0; V_INITIAL_CNT PLS_INTEGER := 1; V_FINAL_CNT PLS_INTEGER := V_REC_COMMIT; V_SEQ_NO PLS_INTEGER := 0; V_TOT PLS_INTEGER := 0; V_EXCEP EXCEPTION; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.PRE_PROCESS; V_START_TIME DATE; V_MONTH P_ACCT_DTL_ENH.MONTH_IDX%TYPE := TO_CHAR(I_INDATE,'MM'); PROCEDURE OBJ_DELETE IS BEGIN OBJ_DROWID.DELETE; OBJ_PROWID.DELETE; OBJ_ROWID.DELETE; OBJ_REGION_CD.DELETE; OBJ_AROWID.DELETE; OBJ_LROWID.DELETE; V_FINAL_CNT := V_REC_COMMIT; V_INITIAL_CNT := 1; V_TOT := 0; V_CTR_LOOP := 0; V_MOD_CNT := 0; V_CNT := 0; END OBJ_DELETE; PROCEDURE SHIFT_RELEASE_AMT IS BEGIN UPDATE P_ACCT_DTL_ENH SET PREV_DAY_RELEASE_AMT = LTD_RELEASE_AMT1,AMT_DPD1 = 0,INT_DPD1 = 0; END SHIFT_RELEASE_AMT; PROCEDURE INS_FROM_LOAN IS CURSOR INS_ACCT IS SELECT DAD.ROWID FROM D_ACCOUNT_DTL DAD WHERE DAD.RECORD_TO_DATE = I_INDATE AND NOT EXISTS (SELECT 'X' FROM P_ACCT_DTL_ENH PAD WHERE PAD.ACCT_NO = DAD.ACCT_NO AND PAD.MONTH_IDX = V_MONTH); CURSOR UPD_ACCT IS SELECT A.ROWID AS PROWID,B.ROWID AS DROWID FROM D_ACCOUNT_DTL B JOIN P_ACCT_DTL_ENH A ON B.ACCT_NO = A.ACCT_NO AND A.MONTH_IDX = V_MONTH WHERE TRUNC(B.RECORD_TO_DATE) = TRUNC(I_INDATE); V_EXCEP1 EXCEPTION ; BEGIN BMGLOBAL_ENH.PROCLOG('UPDATION OF P_ACCT_DTL FROM ACCOUNT DETAIL STARTED '); OBJ_DELETE ; OPEN UPD_ACCT; FETCH UPD_ACCT BULK COLLECT INTO OBJ_PROWID,OBJ_DROWID; CLOSE UPD_ACCT; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT);

L61 BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED FOR UPDATE : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1;END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN FORALL CTR UPDATE SET

1..V_CTR_LOOP LOOP IN V_INITIAL_CNT..V_FINAL_CNT P_ACCT_DTL_ENH A (ACCT_STATUS1,ACCT_SUB_STATUS1,LTD_RELEASE_AMT1,CURR_BAL1,YTD_INT_AMT1, YTD_PRIN_AMT1,INST_AMT1,CHARGEOFF_DATE1,PAYMENT_AMT1,INT_RATE1,HOST_APPLN_NO) = ( SELECT CASE WHEN STATUS_CD IN (2,8) OR CURR_BAL <= 0 THEN BMCONSTANT_ENH.ACCT_STATUS_CLOSED ELSE BMCONSTANT_ENH.ACCT_STATUS_OPEN END ACCT_STATUS1, CASE WHEN STATUS_CD = 2 THEN BMCONSTANT_ENH.ACCT_SUBSTATUS_PAIDOFF WHEN STATUS_CD = 8 THEN BMCONSTANT_ENH.ACCT_SUBSTATUS_CHARGEOFF WHEN STATUS_CD NOT IN(2,8)AND CURR_BAL <=0 THEN BMCONSTANT_ENH.ACCT_SUBSTATUS_CLOSE ELSE BMCONSTANT_ENH.ACCT_SUBSTATUS_OPEN END ACCT_SUB_STATUS1, RELEASE_AMT, CURR_BAL, INT_PAID_YTD,PRINCIPAL_PAID_YTD,PAYMENT_AMT,CHARGEOFF_DATE,MTD_PAYMENT_RCVD,INT_RATE FROM D_ACCOUNT_DTL WHERE ROWID = OBJ_DROWID(CTR)) AND ROWID = OBJ_PROWID(CTR); COMMIT; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; BMGLOBAL_ENH.PROCLOG('UPDATION OF P_ACCT_DTL FROM ACCOUNT DETAIL FEED COMPLETED '); OBJ_PROWID.DELETE; OBJ_DROWID.DELETE; V_INITIAL_CNT := 1; V_FINAL_CNT := V_REC_COMMIT; V_CTR_LOOP := 0; V_MOD_CNT := 0; OPEN INS_ACCT; FETCH INS_ACCT BULK COLLECT INTO OBJ_DROWID ; CLOSE INS_ACCT; V_CTR_LOOP := TRUNC(OBJ_DROWID.COUNT/V_REC_COMMIT); V_MOD_CNT := MOD(OBJ_DROWID.COUNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE INSERTED : ' || OBJ_DROWID.COUNT); V_CNT := NVL(V_TOT,0) + OBJ_DROWID.COUNT; IF V_CNT = 0 THEN O_REMARKS := 'NEW DATA DOES NOT EXIST IN D_ACCOUNT_DETAIL TABLE FOR DATE'||I_INDATE; RAISE V_EXCEP1; END IF; IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1;END IF; IF V_FINAL_CNT > OBJ_DROWID.COUNT THEN V_FINAL_CNT := OBJ_DROWID.COUNT; END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT INSERT INTO P_ACCT_DTL_ENH(ACCT_NO, CUST_NO, LOS_APPLN_NO,HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ, LOAN_TYPE, ORIG_BAL, VINTAGE,ACCT_OPEN_DATE, LOAN_TENURE, ORIG_LOAN_DATE,PAYMENT_FREQUENCY, SALES_EXEC_ID, PRG_CODE,EMPLOYMENT_TYPE, OCC_CD, REGION_CD,BRANCH_CD, HUB_CD, DEVELOPER_CD,CHANNEL_CD, POSTAL_CD, TOTAL_INCOME,CREATED_USER, CREATED_DTM, AMT_DPD1,INT_DPD1, PROCESS_SEQ_NO, MONTH_IDX,ACCT_STATUS1, ACCT_SUB_STATUS1, LTD_RELEASE_AMT1,CURR_BAL1, YTD_INT_AMT1, YTD_PRIN_AMT1,INST_AMT1, CHARGEOFF_DATE1, PAYMENT_AMT1,INT_RATE1) SELECT ACCT_NO, CIF_NO, HOST_APPLN_NO,HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ,LOAN_TYPE, ORIG_BAL, TO_NUMBER(TO_CHAR(DATE_OPENED,'RRRRMM')), DATE_OPENED, LOAN_TERM, ORIG_LOAN_DATE, PAYMENT_FREQUENCY, '99','99','99','99','99','99','99', '99','99','99',0,UPPER(USER), SYSDATE, 0,0, PROCESS_SEQ_NO, V_MONTH, CASE WHEN STATUS_CD IN (2,8) OR CURR_BAL <= 0 THEN BMCONSTANT_ENH.ACCT_STATUS_CLOSED ELSE BMCONSTANT_ENH.ACCT_STATUS_OPEN END ACCT_STATUS1, CASE WHEN STATUS_CD = 2 THEN BMCONSTANT_ENH.ACCT_SUBSTATUS_PAIDOFF WHEN STATUS_CD = 8 THEN BMCONSTANT_ENH.ACCT_SUBSTATUS_CHARGEOFF WHEN STATUS_CD NOT IN (2,8) AND CURR_BAL <=0 THEN BMCONSTANT_ENH.ACCT_SUBSTATUS_CLOSED ELSE BMCONSTANT_ENH.ACCT_SUBSTATUS_OPEN END ACCT_SUB_STATUS1, RELEASE_AMT,CURR_BAL, INT_PAID_YTD, PRINCIPAL_PAID_YTD,PAYMENT_AMT, CHARGEOFF_DATE, MTD_PAYMENT_RCVD,INT_RATE FROM D_ACCOUNT_DTL WHERE ROWID = OBJ_DROWID(CTR); COMMIT ; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; V_TOT := V_TOT + SQL%ROWCOUNT; IF V_FINAL_CNT > OBJ_DROWID.COUNT THEN V_FINAL_CNT := OBJ_DROWID.COUNT;END IF; COMMIT; END LOOP; EXCEPTION WHEN V_EXCEP1 THEN NULL ; END INS_FROM_LOAN; PROCEDURE UPD_FROM_ACCT_DPD IS CURSOR UPD_CUR_ACCT IS

L62 SELECT PAD.ROWID AS PROWID,DAD.DPD AS AMT_DPD,DAD.INT_DPD AS INT_DPD FROM D_ACCT_DPD_ENH DAD JOIN P_ACCT_DTL_ENH PAD ON DAD.ACCT_NO = PAD.ACCT_NO AND PAD.MONTH_IDX = V_MONTH WHERE TRUNC(DAD.RECORD_TO_DATE) = TRUNC(I_INDATE); BEGIN BMGLOBAL_ENH.PROCLOG('UPDATION OF P_ACCT_DTL FROM ACCOUNT DPD FEED STARTED '); OBJ_DELETE ; OPEN UPD_CUR_ACCT; FETCH UPD_CUR_ACCT BULK COLLECT INTO OBJ_PROWID,OBJ_AMT_DPD,OBJ_INT_DPD; CLOSE UPD_CUR_ACCT; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED FOR UPDATE : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1; END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT;END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET AMT_DPD1 = OBJ_AMT_DPD(CTR), INT_DPD1 = OBJ_INT_DPD(CTR) WHERE ROWID = OBJ_PROWID(CTR); COMMIT; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; BMGLOBAL_ENH.PROCLOG('UPDATION OF P_ACCT_DTL FROM ACCOUNT DPD FEED COMPLETED '); END UPD_FROM_ACCT_DPD ; PROCEDURE UPD_LOS_APPLN IS CURSOR UPD_LOS_APPLN IS SELECT PAD.ROWID AS PROWID,DAD.ROWID AS DROWID,DAD.LOS_APPLN_NO AS LOS_APPLN FROM D_APPLN_HOST_DTL DAD JOIN P_ACCT_DTL_ENH PAD ON DAD.HOST_APPLN_NO = PAD.HOST_APPLN_NO AND DAD.FACILITY_CD = PAD.FACILITY_CD AND DAD.FACILITY_SEQ = PAD.FACILITY_SEQ AND PAD.MONTH_IDX = V_MONTH WHERE TRUNC(DAD.RECORD_TO_DATE) = TRUNC(I_INDATE); BEGIN BMGLOBAL_ENH.PROCLOG('UPDATION OF LOS APPLICATION NO IN P_ACCT_DTL FROM LOAN APPLICATION STARTED '); OBJ_DELETE ; OPEN UPD_LOS_APPLN; FETCH UPD_LOS_APPLN BULK COLLECT INTO OBJ_PROWID,OBJ_DROWID, OBJ_LOS_APPLN; CLOSE UPD_LOS_APPLN; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED FOR UPDATE : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1; END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET LOS_APPLN_NO = OBJ_LOS_APPLN(CTR) WHERE ROWID = OBJ_PROWID(CTR); COMMIT; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; BMGLOBAL_ENH.PROCLOG('UPDATION OF LOS APPLICATION NO IN P_ACCT_DTL FROM LOAN APPLICATION COMPLETED '); END UPD_LOS_APPLN; PROCEDURE REFRESH_TEMP_APPLN_DTL_ENH IS BEGIN BMGLOBAL_ENH.PROCLOG('REFRESHING DATA OF TEMP_APPLN_DTL_ENH STARTED '); EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_APPLN_DTL_ENH DROP STORAGE '; INSERT INTO TEMP_APPLN_DTL_ENH SELECT DISTINCT DAHD.HOST_APPLN_NO, DAHD.LOS_APPLN_NO, DAHD.FACILITY_CD, DAHD.FACILITY_SEQ, DAHD.RECORD_TO_DATE,NVL(DAHD.APPLIED_AMT,0) APPLIED_AMT, NVL(DAHD.APPROVED_AMT,0) APPROVED_AMT, NVL(DAD.DEVELOPER_CD,'99') DEVELOPER_CD, NVL(DCAD.POSTAL_CD,'99') POSTAL_CD, NVL(DALD.SALES_EXEC_ID,'99') SALES_EXEC_ID, NVL(DALD.PRG_CODE,'99') PRG_CODE, NVL(DALD.REGIONAL_ID,'99') REGIONAL_ID, NVL(DALD.BRANCH_CD,'99') BRANCH_CD, NVL(DALD.CHANNEL_CODE,'99') CHANNEL_CODE, NVL(DALD.COMPANY_CD,'99') COMPANY_CD, NVL(DALD.COMBRANCH_CODE,'99') COMBRANCH_CODE, NVL(DCLD.OCC_CD,'99') OCC_CD, NVL(DCLD.EMPLOYMENT_TYPE,'99') EMPLOYMENT_TYPE, DCLD.DOB, NVL(DCLD.TOTAL_INCOME,0) TOTAL_INCOME, DCS.FINAL_APPLN_SCORE, NVL(RB.HUB_CD,'99') HUB_CD, DALD.DISBMT_END_DATE FROM D_APPLN_HOST_DTL DAHD JOIN D_ACCOUNT_DTL DAD ON DAHD.FACILITY_CD NOT LIKE 'P%' AND DAHD.HOST_APPLN_NO = DAD.HOST_APPLN_NO AND DAHD.FACILITY_CD = DAD.FACILITY_CD

L63 AND DAHD.FACILITY_SEQ = DAD.FACILITY_SEQ JOIN D_CUST_ADDR_DTL DCAD ON DAD.CIF_NO = DCAD.CUST_NO AND DCAD.ADDR_SEQ_NO = ( SELECT MAX(B.ADDR_SEQ_NO) FROM D_CUST_ADDR_DTL B WHERE B.CUST_NO = DCAD.CUST_NO )LEFT OUTER JOIN D_APPLN_LOS_DTL_BAK DALD ON DAD.LOS_APPLN_NO = DALD.APPLN_NO LEFT OUTER JOIN D_CUST_LOS_DTL_BAK DCLD ON DALD.CUST_NO = DCLD.CUST_NO LEFT OUTER JOIN D_CUST_SUMMARY DCS ON DALD.APPLN_NO = DCS.APPLN_NO LEFT OUTER JOIN REF_BRANCH RB ON DALD.BRANCH_CD = RB.BRANCH_CD; BMGLOBAL_ENH.PROCLOG('REFRESHING DATA OF TEMP_APPLN_DTL_ENH COMPLETED '); COMMIT; END REFRESH_TEMP_APPLN_DTL_ENH; PROCEDURE UPD_FROM_HOST_APPLN IS CURSOR UPD_CUR_APPLN_HOST IS SELECT PAD.ROWID AS PROWID,DAD.ROWID AS DROWID FROM TEMP_APPLN_DTL_ENH DAD JOIN P_ACCT_DTL_ENH PAD ON DAD.HOST_APPLN_NO = PAD.HOST_APPLN_NO AND DAD.FACILITY_CD = PAD.FACILITY_CD AND DAD.FACILITY_SEQ = PAD.FACILITY_SEQ AND PAD.MONTH_IDX = V_MONTH WHERE TRUNC(DAD.RECORD_TO_DATE) = TRUNC(I_INDATE); BEGIN BMGLOBAL_ENH.PROCLOG('UPDATION OF P_ACCT_DTL FROM APPLICATION FEED STARTED '); OBJ_DELETE ; OPEN UPD_CUR_APPLN_HOST; FETCH UPD_CUR_APPLN_HOST BULK COLLECT INTO OBJ_PROWID,OBJ_DROWID; CLOSE UPD_CUR_APPLN_HOST; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED FOR UPDATE : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1; END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET (SALES_EXEC_ID,APPLIED_LOAN_AMT,SPPK_LOAN_AMT, PRG_CODE,OCC_CD, EMPLOYMENT_TYPE, REGION_CD,BRANCH_CD,HUB_CD,DEVELOPER_CD,CHANNEL_CD,POSTAL_CD,DOB,TOTAL_INCOME,FINAL_APPLN_ SCORE,COMPANY_CD,COMBRANCH_CODE,DISBMT_END_DATE) = ( SELECT SALES_EXEC_ID, APPLIED_AMT,APPROVED_AMT, PRG_CODE, OCC_CD,EMPLOYMENT_TYPE, REGIONAL_ID, BRANCH_CD, HUB_CD, DEVELOPER_CD,CHANNEL_CODE,POSTAL_CD, DOB, TOTAL_INCOME,FINAL_APPLN_SCORE, COMPANY_CD,COMBRANCH_CODE, DISBMT_END_DATE FROM TEMP_APPLN_DTL_ENH DAD WHERE DAD.ROWID = OBJ_DROWID(CTR))AND ROWID = OBJ_PROWID(CTR); COMMIT; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; COMMIT ; BMGLOBAL_ENH.PROCLOG('UPDATION OF P_ACCT_DTL FROM APPLICATION FEED COMPLETED '); END UPD_FROM_HOST_APPLN; PROCEDURE REGION_CLEANUP IS CURSOR RC1 IS SELECT A.ROWID AS PROWID,C.REGION_CD FROM P_ACCT_DTL_ENH A JOIN REF_BRANCH B ON A.BRANCH_CD = B.BRANCH_CD JOIN REF_HUB C ON B.HUB_CD = C.HUB_CD WHERE A.REGION_CD = '99';

AND A.MONTH_IDX = V_MONTH

CURSOR RC2 IS SELECT A.ROWID AS PROWID,C.REGION_CD FROM P_ACCT_DTL_ENH A JOIN REF_BRANCH B ON SUBSTR(A.HOST_APPLN_NO,1,5) = B.BRANCH_CD AND A.MONTH_IDX = V_MONTH JOIN REF_HUB C ON B.HUB_CD = C.HUB_CD WHERE A.REGION_CD = '99'; CURSOR RC4 IS SELECT A.ROWID AS PROWID,C.REGION_CD FROM D_APPLN_LOS_DTL_BAK A JOIN REF_BRANCH B ON A.BRANCH_CD = B.BRANCH_CD JOIN REF_HUB C ON B.HUB_CD = C.HUB_CD; BEGIN BMGLOBAL_ENH.PROCLOG ('REGION CODE CLEANUP IN D_APPLN_DTL PROCESS STARTED') ; OBJ_DELETE; OPEN RC4; FETCH RC4 BULK COLLECT INTO OBJ_PROWID,OBJ_REGION_CD; CLOSE RC4; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED(C4) : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1; END IF;

L64 IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE D_APPLN_LOS_DTL_BAK A SET REGIONAL_ID = OBJ_REGION_CD(CTR) WHERE ROWID = OBJ_PROWID(CTR); BMGLOBAL_ENH.PROCLOG('PROCESSED(C4) ' || V_FINAL_CNT || ' RECORDS '); COMMIT; V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; COMMIT ; BMGLOBAL_ENH.PROCLOG('PROCESSED(C4) ' || V_CNT || ' RECORDS '); BMGLOBAL_ENH.PROCLOG ('REGION CODE CLEANUP IN D_APPLN_DTL PROCESS COMPLETED SUCCESSFULLY') ; BMGLOBAL_ENH.PROCLOG ('REGION CODE CLEANUP IN P_ACCT_DTL PROCESS STARTED') ; OBJ_DELETE; OPEN RC1; FETCH RC1 BULK COLLECT INTO OBJ_PROWID,OBJ_REGION_CD; CLOSE RC1; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED(C1) : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1; END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET REGION_CD = OBJ_REGION_CD(CTR) WHERE ROWID = OBJ_PROWID(CTR); BMGLOBAL_ENH.PROCLOG('PROCESSED(C1) ' || V_FINAL_CNT || ' RECORDS ');COMMIT; V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; COMMIT ; V_CNT := 0; FOR R2 IN RC2 LOOP UPDATE P_ACCT_DTL_ENH SET REGION_CD = R2.REGION_CD WHERE ROWID = R2.PROWID ; V_CNT := V_CNT + 1 ; END LOOP ;COMMIT ; BMGLOBAL_ENH.PROCLOG('PROCESSED(C2) ' || V_CNT || ' RECORDS '); BMGLOBAL_ENH.PROCLOG ('REGION CODE CLEANUP IN P_ACCT_DTL PROCESS COMPLETED') ; END REGION_CLEANUP; PROCEDURE CLEANACCTREGION IS CURSOR RAC1 IS SELECT A.ROWID AS PROWID,C.REGION_CD FROM P_ACCT_DTL_ENH A JOIN D_ACCOUNT_DTL D ON A.ACCT_NO = D.ACCT_NO AND A.MONTH_IDX = V_MONTH JOIN REF_BRANCH B ON D.BRANCH_NO = B.BRANCH_CD JOIN REF_HUB C ON B.HUB_CD = C.HUB_CD; BEGIN BMGLOBAL_ENH.PROCLOG ('REGION CODE CLEANUP IN P_ACCT_DTL PROCESS STARTED') ; OBJ_DELETE ; OPEN RAC1; FETCH RAC1 BULK COLLECT INTO OBJ_PROWID,OBJ_REGION_CD; CLOSE RAC1; V_CNT := 0; V_CNT := OBJ_PROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED(RAC1) : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1;END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN 1..V_CTR_LOOP LOOP FORALL CTR IN V_INITIAL_CNT..V_FINAL_CNT UPDATE P_ACCT_DTL_ENH A SET REGION_CD = OBJ_REGION_CD(CTR) WHERE ROWID = OBJ_PROWID(CTR); BMGLOBAL_ENH.PROCLOG('PROCESSED(C4) ' || V_FINAL_CNT || ' RECORDS '); COMMIT; V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; COMMIT ; BMGLOBAL_ENH.PROCLOG ('REGION CODE CLEANUP IN P_ACCT_DTL PROCESS COMPLETED') ; END CLEANACCTREGION;

L65 BEGIN V_START_TIME := SYSDATE; O_STATUS := O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_NIL; O_REMARKS := BMGLOBAL_ENH.PROCESS_NAME := 'DBP_PRE_PROCESS' ; BMGLOBAL_ENH.PROCLOG ('PRE-PROCESS FOR THE DATE : ' || I_INDATE || BMGLOBAL_ENH.CHECK_PROCESS_STATUS(I_INDATE,V_PROCESS_ID,O_REMARKS, IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF;

BMCONSTANT_ENH.SCHD_STATUS_SUCC; 'SUCCESS'; ' STARTED') ; O_STATUS,V_SEQ_NO);

BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_ALD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOS APPLICATION FEED DID NOT RUN SUCCESSFULLY FOR DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_LNM,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOAN MASTER FEED DID NOT RUN SUCCESSFULLY FOR DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_DPD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'ACCOUNT DPD FEED DID NOT RUN SUCCESSFULLY FOR DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_LAF,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'LOAN APPLICATION FACILITY FEED DID NOT RUN SUCCESSFULLY FOR DATE'|| I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CAD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'CUSTOMER ADDRESS FEED DID NOT RUN SUCCESSFULLY FOR DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'CUSTOMER LOS FEED DID NOT RUN SUCCESSFULLY FOR THE DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_CSU,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'CUSTOMER SUMMARY FEED DID NOT RUN SUCCESSFULLY FOR DATE '||I_INDATE; RAISE V_EXCEP; END IF; BMGLOBAL_ENH.CHECK_DEPENDENT(I_INDATE,BMCONSTANT_ENH.FTD_FLD,O_STATUS); IF O_STATUS <> BMCONSTANT_ENH.SUCCESS THEN O_REMARKS := 'FACILITY LOS FEED DID NOT RUN SUCCESSFULLY FOR THE DATE ' || I_INDATE; RAISE V_EXCEP; END IF; SHIFT_RELEASE_AMT(); INS_FROM_LOAN(); UPD_FROM_ACCT_DPD(); REGION_CLEANUP(); UPD_LOS_APPLN(); REFRESH_TEMP_APPLN_DTL_ENH(); UPD_FROM_HOST_APPLN() ; CLEANACCTREGION();

COMMIT; COMMIT; COMMIT; COMMIT; COMMIT; COMMIT; COMMIT; COMMIT;

BMGLOBAL_ENH.INSERT_PROC_STATUS( V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0, O_REMARKS,V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG ('PRE-PROCESS FOR THE DATE : ' || I_INDATE || ' COMPLETED SUCCESSFULLY') ; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_MED; BMGLOBAL_ENH.INSERT_PROC_STATUS( V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, O_REMARKS,V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG ('PRE-PROCESS FOR THE DATE : ' || I_INDATE || ' FAILED') ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); WHEN OTHERS THEN O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := TO_CHAR(SQLCODE) || SQLERRM; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS( V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, O_REMARKS,V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG ('PRE-PROCESS FOR THE DATE : ' || I_INDATE || ' FAILED') ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' ');

L66 END DBP_PRE_PROCESS;/

¾

CREATE PROCEDURE DBP_CREATE_INDEX_APPLN_LOS_BAK CREATE OR REPLACE PROCEDURE BMCLN2.DBP_CREATE_INDEX_APPLN_LOS_BAK IS BEGIN EXECUTE IMMEDIATE 'CREATE INDEX IDX_PROSPECT_ID_APPLN_LOS_BAK ON D_APPLN_LOS_DTL_BAK(PROSPECT_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_PRG_CODE_BAK ON D_APPLN_LOS_DTL_BAK(PRG_CODE)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_SPPK_END_APPLN_LOS_BAK ON D_APPLN_LOS_DTL_BAK(SPPK_END_DATE)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_APPROVAL_DATE_LOS_BAK ON D_APPLN_LOS_DTL_BAK(APPROVAL_DATE)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_REGIONAL_ID_BAK ON D_APPLN_LOS_DTL_BAK(REGIONAL_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_DISBMT_END_DATE_LOS_BAK ON D_APPLN_LOS_DTL_BAK(DISBMT_END_DATE)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_BRANCH_CD_BAK ON D_APPLN_LOS_DTL_BAK(BRANCH_CD)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_DOC_SPPK_START_LOS_BAK ON D_APPLN_LOS_DTL_BAK(DOC_COLL_SPPK_START_DATE)'; COMMIT; END DBP_CREATE_INDEX_APPLN_LOS_BAK; /

¾

CREATE PROCEDURE DBP_CREATE_INDEX_D_ACCOUNT_DTL CREATE OR REPLACE PROCEDURE BMCLN2.DBP_CREATE_INDEX_D_ACCOUNT_DTL IS BEGIN EXECUTE IMMEDIATE 'CREATE INDEX BMCLN2.IDX_HOST_APPLN ON BMCLN2.D_ACCOUNT_DTL(HOST_APPLN_NO, FACILITY_CD, FACILITY_SEQ)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_RECORD_TO_DATE ON D_ACCOUNT_DTL(RECORD_TO_DATE)'; EXECUTE IMMEDIATE 'CREATE INDEX IDX_LOS_APPLN_NO_ACCT_DTL ON D_ACCOUNT_DTL(LOS_APPLN_NO)'; EXECUTE IMMEDIATE 'CREATE INDEX BMCLN2.IDX_BRANCH_NO_ACCT_DTL ON BMCLN2.D_ACCOUNT_DTL(BRANCH_NO)'; COMMIT; END DBP_CREATE_INDEX_D_ACCOUNT_DTL; /

¾

CREATE PROCEDURE DBP_DROP_INDEX_APPLN_LOS_BAK CREATE OR REPLACE PROCEDURE BMCLN2.DBP_DROP_INDEX_APPLN_LOS_BAK IS BEGIN EXECUTE IMMEDIATE 'DROP INDEX IDX_PROSPECT_ID_APPLN_LOS_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_PRG_CODE_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_SPPK_END_APPLN_LOS_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_APPROVAL_DATE_LOS_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_REGIONAL_ID_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_DISBMT_END_DATE_LOS_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_BRANCH_CD_BAK'; EXECUTE IMMEDIATE 'DROP INDEX IDX_DOC_SPPK_START_LOS_BAK'; COMMIT; END DBP_DROP_INDEX_APPLN_LOS_BAK; /

¾

CREATE PROCEDURE DBP_DROP_INDEX_D_ACCOUNT_DTL CREATE OR REPLACE PROCEDURE BMCLN2.DBP_DROP_INDEX_D_ACCOUNT_DTL IS BEGIN EXECUTE IMMEDIATE 'DROP INDEX IDX_BRANCH_NO_ACCT_DTL'; EXECUTE IMMEDIATE 'DROP INDEX IDX_HOST_APPLN'; EXECUTE IMMEDIATE 'DROP INDEX IDX_LOS_APPLN_NO_ACCT_DTL'; EXECUTE IMMEDIATE 'DROP INDEX IDX_RECORD_TO_DATE'; COMMIT; END DBP_DROP_INDEX_D_ACCOUNT_DTL; /

¾

CREATE PROCEDURE DBP_JOB_FULL_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_FULL_ENH DBP_JOB_MAIN_ENH; DBP_JOB_MIS_AVG_TURNAROUND_ENH; DBP_JOB_MIS_KPI_ENH; DBP_JOB_MIS_LOAN_LINE_ANLS_ENH; DBP_JOB_MIS_MTR_LOAN_ANLS_ENH; DBP_JOB_MIS_PROFIT_LOSS_ALL; END DBP_JOB_FULL_ENH; /

L67 ¾

CREATE PROCEDURE DBP_JOB_MAIN_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_MAIN_ENH IS V_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.SCHEDULER_JOB_MAIN_ENH; V_START_TIME DATE; V_END_TIME DATE; V_MAX_DATE DATE ; V_MAX_DATE1 DATE ; V_BUSINESS_DATE DATE ; V_REMARKS VARCHAR2(2000):= 'SUCCESS' ; V_STATUS VARCHAR2(2:='0' ; V_SEVERITY VARCHAR2(2):='0' ; V_EXCEP EXCEPTION; BEGIN V_START_TIME := SYSDATE; BMGLOBAL_ENH.PROCESS_NAME SELECT S_PROCESS_NO.NEXTVAL INTO V_SEQ_NO FROM DUAL;

:= 'DBP_JOB_MAIN_ENH' ;

SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS; SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE1 FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS AND PROCESS_ID IN (BMCONSTANT_ENH.FTD_LAF,BMCONSTANT_ENH.FTD_LNM,BMCONSTANT_ENH.FTD_DPD); V_BUSINESS_DATE := V_MAX_DATE1 ; BMGLOBAL_ENH.PROCLOG('SCHEDULER MAIN JOB STARTED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.CHECK_PROCESS_STATUS(V_BUSINESS_DATE,V_PROCESS_ID,V_REMARKS, V_STATUS,V_SEQ_NO); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG ('DROPPING INDEX D_APPLN_LOS_DTL_BAK STARTED'); DBP_DROP_INDEX_APPLN_LOS_BAK; BMGLOBAL_ENH.PROCLOG ('DROPPING INDEX D_APPLN_LOS_DTL_BAK COMPLETED'); COMMIT; BMGLOBAL_ENH.PROCLOG ('MAIN JOB STARTED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MAIN_ENH' ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE, BMCONSTANT_ENH.BACKUP_LOS, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB

: BACKUP_LOS PROCESS FAILED FOR V_BUSINESS_DATE) ;

THE DATE : '

||

BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MAIN_ENH' ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE, BMCONSTANT_ENH.DISBURSEMENTDATE_CLEANUP, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : DISBURSEMENT DATE CLEANUP FAILED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MAIN_ENH' ; BMGLOBAL_ENH.PROCLOG ('CREATING INDEX D_APPLN_LOS_DTL_BAK STARTED'); DBP_CREATE_INDEX_APPLN_L0S_BAK; BMGLOBAL_ENH.PROCLOG ('CREATING INDEX D_APPLN_LOS_DTL_BAK COMPLETED'); COMMIT; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE, BMCONSTANT_ENH.PRE_PROCESS, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : PRE PROCESS FAILED FOR THE DATE : ' || V_BUSINESS_DATE) ;

L68 BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MAIN_ENH' ; IF (V_MAX_DATE1 = LAST_DAY(V_MAX_DATE1)) THEN BMGLOBAL_ENH.PROCLOG('INSERTING D_APPPLN_HOST_DTL_HISTORY STARTED'); DBP_INS_HIST_TAB(BMCONSTANT_ENH.FTD_LAF); --PROCESS FOR D_APPLN_HOST_DTL_HISTORY BMGLOBAL_ENH.PROCLOG('INSERTING D_APPPLN_HOST_DTL_HISTORY COMPLETED'); BMGLOBAL_ENH.PROCLOG('INSERTING D_ACCOUNT_DTL_HISTORY STARTED'); DBP_INS_HIST_TAB(BMCONSTANT_ENH.FTD_LNM); --PROCESS FOR D_ACCOUNT_DTL_HISTORY BMGLOBAL_ENH.PROCLOG('INSERTING D_ACCOUNT_DTL_HISTORY COMPLETED'); BMGLOBAL_ENH.PROCLOG('INSERTING D_ACCT_DPD_ENH_HISTORY STARTED'); DBP_INS_HIST_TAB(BMCONSTANT_ENH.FTD_DPD); --PROCESS FOR D_ACCT_DPD_ENH_HISTORY BMGLOBAL_ENH.PROCLOG('INSERTING D_ACCT_DPD_ENH_HISTORY COMPLETED'); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MAIN_ENH' ; IF (V_BUSINESS_DATE = LAST_DAY(V_BUSINESS_DATE)) THEN BMGLOBAL_ENH.PROCLOG('INSERTING D_APPLN_LOS_DTL_HISTORY STARTED'); DBP_INS_HIST_TAB(BMCONSTANT_ENH.FTD_ALD); --PROCESS FOR D_APPLN_LOS_DTL_HISTORY BMGLOBAL_ENH.PROCLOG('INSERTING D_APPLN_LOS_DTL_HISTORY COMPLETED'); BMGLOBAL_ENH.PROCLOG('INSERTING D_FACILITY_LOS_DTL_HISTORY STARTED'); DBP_INS_HIST_TAB(BMCONSTANT_ENH.FTD_FLD); --PROCESS FOR D_FACILITY_LOS_DTL_HISTORY BMGLOBAL_ENH.PROCLOG('INSERTING D_FACILITY_LOS_DTL_HISTORY COMPLETED'); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MAIN_ENH' ; V_END_TIME := SYSDATE; V_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0,V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MAIN JOB COMPLETED SUCCESSFULLY FOR THE DATE '||V_BUSINESS_DATE) ; COMMIT; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; V_END_TIME := SYSDATE; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.FAIL,0,0,V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MAIN JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); COMMIT; WHEN OTHERS THEN V_REMARKS := SQLERRM; V_END_TIME := SYSDATE; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0,V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MAIN JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_JOB_MAIN_ENH; /

¾

CREATE PROCEDURE DBP_DISBURSEMENTDATE_CLEANUP CREATE OR REPLACE PROCEDURE DBP_DISBURSEMENTDATE_CLEANUP(I_CNT_CD IN VARCHAR2, I_INDATE IN DATE, O_STATUS OUT VARCHAR2, O_SEVERITY OUT VARCHAR2, O_REMARKS OUT VARCHAR2)IS V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.DISBURSEMENTDATE_CLEANUP; V_TOT_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_START_TIME DATE := SYSDATE; TYPE TY_ROWID IS TABLE OF VARCHAR2(22) INDEX BY BINARY_INTEGER; TYPE TY_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER; OBJ_AROWID TY_ROWID; OBJ_DATE TY_DATE; V_CNT PLS_INTEGER := 0; V_CTR_LOOP PLS_INTEGER := 0; V_MOD_CNT PLS_INTEGER := 0; V_REC_COMMIT PLS_INTEGER := 50000; V_INITIAL_CNT PLS_INTEGER := 1; V_FINAL_CNT PLS_INTEGER := V_REC_COMMIT; CURSOR C1 IS SELECT A.ROWID AS AROWID, B.FULL_RELASE_DATE FROM D_APPLN_LOS_DTL_BAK A JOIN D_ACCOUNT_DTL B ON A.APPLN_NO = B.LOS_APPLN_NO ;

L69 CURSOR C2 IS SELECT A.ROWID AS AROWID,B.FIRST_RELEASE_DATE FROM D_FACILITY_LOS_DTL_BAK A JOIN D_ACCOUNT_DTL B ON A.APPLN_NO = B.LOS_APPLN_NO ; BEGIN BMGLOBAL_ENH.PROCESS_NAME := 'DBP_DISBURSEMENTDATE_CLEANUP' ; BMGLOBAL_ENH.PROCLOG ('DISBURSEMENT DATE CLEANUP FOR LOS APPLICATIONS STARTED-LOS') ; UPDATE D_APPLN_LOS_DTL_BAK SET DISBMT_END_DATE = NULL; COMMIT ; UPDATE D_FACILITY_LOS_DTL_BAK SET DISBMT_START_DATE = NULL; COMMIT ; OBJ_AROWID.DELETE; OBJ_DATE.DELETE; OPEN C1; FETCH C1 BULK COLLECT INTO OBJ_AROWID,OBJ_DATE; CLOSE C1; V_CNT := 0; V_CNT := OBJ_AROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1;END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN FORALL CTR UPDATE SET WHERE

1..V_CTR_LOOP LOOP IN V_INITIAL_CNT..V_FINAL_CNT D_APPLN_LOS_DTL_BAK A A.DISBMT_END_DATE = OBJ_DATE(CTR) A.ROWID = OBJ_AROWID(CTR) ;

BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); COMMIT; V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; COMMIT ; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_CNT || ' RECORDS '); BMGLOBAL_ENH.PROCLOG ('DISBURSEMENT DATE CLEANUP FOR LOS APPLICATIONS COMPLETED-LOS') ; BMGLOBAL_ENH.PROCLOG ('DISBURSEMENT DATE CLEANUP FOR LOS FACILITY STARTED-LOS') ; OBJ_AROWID.DELETE; OBJ_DATE.DELETE; V_FINAL_CNT := V_REC_COMMIT; V_INITIAL_CNT := 1; V_CTR_LOOP := 0; V_MOD_CNT := 0; V_CNT := 0; OPEN C2; FETCH C2 BULK COLLECT INTO OBJ_AROWID,OBJ_DATE; CLOSE C2; V_CNT := 0; V_CNT := OBJ_AROWID.COUNT; V_CTR_LOOP := TRUNC(V_CNT/V_REC_COMMIT); V_MOD_CNT := MOD(V_CNT,V_REC_COMMIT); BMGLOBAL_ENH.PROCLOG('TOTAL RECORDS TO BE PROCESSED : ' || V_CNT); IF V_MOD_CNT > 0 THEN V_CTR_LOOP := V_CTR_LOOP + 1; END IF; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; FOR CTR_CNT IN FORALL CTR UPDATE SET WHERE

1..V_CTR_LOOP LOOP IN V_INITIAL_CNT..V_FINAL_CNT D_FACILITY_LOS_DTL_BAK A A.DISBMT_START_DATE = OBJ_DATE(CTR) A.ROWID = OBJ_AROWID(CTR) ;

BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_FINAL_CNT || ' RECORDS '); COMMIT; V_INITIAL_CNT := V_FINAL_CNT + 1; V_FINAL_CNT := V_FINAL_CNT + V_REC_COMMIT; IF V_FINAL_CNT > V_CNT THEN V_FINAL_CNT := V_CNT; END IF; END LOOP; COMMIT ; BMGLOBAL_ENH.PROCLOG('PROCESSED ' || V_CNT || ' RECORDS '); BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID,5,BMCONSTANT_ENH.SUCCESS,V_TOT_CNT,0,'SUCCESS', V_SEQ_NO,I_INDATE,NULL,V_START_TIME,SYSDATE); BMGLOBAL_ENH.PROCLOG ('DISBURSEMENT DATE CLEANUP FOR LOS FACILITY COMPLETED-LOS') ; EXCEPTION WHEN OTHERS THEN ROLLBACK; O_STATUS := BMCONSTANT_ENH.SCHD_STATUS_FAIL; O_SEVERITY := BMCONSTANT_ENH.SCHD_SEVRTY_HIGH; O_REMARKS := SQLERRM; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL, 0, V_TOT_CNT, O_REMARKS, V_SEQ_NO,I_INDATE, NULL, V_START_TIME, SYSDATE);

L70 BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_DISBURSEMENTDATE_CLEANUP; /

L71 ¾

CREATE PROCEDURE DBP_INS_HIST_TAB CREATE OR REPLACE PROCEDURE BMCLN2.DBP_INS_HIST_TAB(I_PROC_ID NUMBER) IS V_PERIOD_COUNT NUMBER; BEGIN BMGLOBAL_ENH.PROCESS_NAME

:= 'DBP_INS_HIST_TAB' ;

--PROCESS FOR D_APPLN_HOST_DTL_HISTORY IF I_PROC_ID=BMCONSTANT_ENH.FTD_LAF THEN INSERT INTO D_APPLN_HOST_DTL_HISTORY( HOST_APPLN_NO, LOS_APPLN_NO, FACILITY_CD, FACILITY_SEQ,APPLIED_AMT, APPROVED_AMT, ENTRY_DATE, APPLN_DATE, FINAL_PMT_AMT,TERM, TERM_CD, LOAN_PURPOSE,OFFER_DATE, APPROVED_DATE, BU_APPROVAL_BY,CRM_APPROVAL_BY, OFFICER_CD, LIMIT_STATUS, FACILITY_STATUS,MOD_INT,INT_RATE, PRIM_RATE, PRIM_RATE_FL, PRIM_RATE_CL, CURR_TYPE, FACILITY_CIF_NO, DEC_SENT_DATE,LAST_CHG_DT, BU_APPROVAL_DATE, CRM_APPROVAL_DATE, OFFER_ACCEPT_DATE, BRANCH_NO, PMT_AMT, APPROVAL_DATE, APPROVED_BY, ORG_AMT, OS_BAL, PROD_TYPE, SP3K_NO, PROV_BANK, NOTARY_FEE, APPRAISAL_FEE, SKMHT_FEE,LIFE_INS_FEE, FIRST_MTH_PMT, PRA_REALISASI_FLAG, INSURANCE_CD, FIRE_INSURANCE_CD, ADMINISTRATION_FEE,AUTO_INS_FEE, STAMP_FEE, CUST_GIRO, BLOCK_ACC_BAL, DOWN_PMT, AUTO_INS_CD, HOME_INS_FEE, OLD_CIF_OTH, OLD_CIF_ACC_NO, ADMIN_FEE_DEBT, SP2D_PRNT_DEBT, POSTING_DATE, TRANS_AMT, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO,PERIOD) SELECT HOST_APPLN_NO, LOS_APPLN_NO, FACILITY_CD, FACILITY_SEQ, APPLIED_AMT, APPROVED_AMT, ENTRY_DATE, APPLN_DATE, FINAL_PMT_AMT, TERM, TERM_CD, LOAN_PURPOSE, OFFER_DATE, APPROVED_DATE, BU_APPROVAL_BY,CRM_APPROVAL_BY,OFFICER_CD, LIMIT_STATUS, FACILITY_STATUS, MOD_INT, INT_RATE, PRIM_RATE, PRIM_RATE_FL,PRIM_RATE_CL,CURR_TYPE,FACILITY_CIF_NO, DEC_SENT_DATE, LAST_CHG_DT, BU_APPROVAL_DATE, CRM_APPROVAL_DATE, OFFER_ACCEPT_DATE, BRANCH_NO, PMT_AMT, APPROVAL_DATE, APPROVED_BY, ORG_AMT, OS_BAL, PROD_TYPE, SP3K_NO, PROV_BANK, NOTARY_FEE, APPRAISAL_FEE, SKMHT_FEE, LIFE_INS_FEE, FIRST_MTH_PMT, PRA_REALISASI_FLAG, INSURANCE_CD, FIRE_INSURANCE_CD, ADMINISTRATION_FEE, AUTO_INS_FEE, STAMP_FEE, CUST_GIRO, BLOCK_ACC_BAL, DOWN_PMT, AUTO_INS_CD, HOME_INS_FEE, OLD_CIF_OTH, OLD_CIF_ACC_NO, ADMIN_FEE_DEBT, SP2D_PRNT_DEBT, POSTING_DATE, TRANS_AMT, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO,TO_CHAR(RECORD_TO_DATE,'YYYYMM') PERIOD FROM D_APPLN_HOST_DTL; COMMIT; -- DELETING RECORDS FROM D_APPLN_HOST_DTL_HISTORY BY KEEPING 4 MONTHS RECORD SELECT COUNT(DISTINCT PERIOD) INTO V_PERIOD_COUNT FROM D_APPLN_HOST_DTL_HISTORY; IF V_PERIOD_COUNT > 4 THEN DELETE FROM D_APPLN_HOST_DTL_HISTORY WHERE PERIOD=(SELECT D_APPLN_HOST_DTL_HISTORY); COMMIT; END IF;

MIN(PERIOD)FROM

--PROCESS FOR D_ACCOUNT_DTL_HISTORY ELSIF I_PROC_ID=BMCONSTANT_ENH.FTD_LNM THEN INSERT INTO D_ACCOUNT_DTL_HISTORY( BRANCH_NO, ACCT_NO, ACCT_TYPE, HOST_APPLN_NO, CIF_NO, STATUS_CD, LOAN_TYPE, ORIG_BAL, RELEASE_AMT, CURR_BAL, MISC_INSURANCE_PREMIUM, OTH_CHARGES, MISC_COST, PAID_LATE_CHARGES, MISC_INSURANCE_REBATE, PAYMENT_AMT, CHARGEOFF_DATE, PAY_OFF_DATE, INT_RATE, LOAN_TERM, LOAN_TERM_CD, PAYMENT_FREQUENCY, ORIG_LOAN_DATE, DEVELOPER_CD, MTD_PAYMENT_RCVD, FACILITY_CD, FACILITY_SEQ, BANK_NO, REPORTING_BRANCH, SHORT_NAME, ACCT_NAME1, ACCT_NAME2, OFFICER, PREVIOUS_STATUS, YEARLY_OPENING_BAL, AMT_TO_BE_RELEASED, LAST_PAYMENT_AMT, LAST_YEAR_WRITE_OFF, DRAWING_LIMIT, REDUCED_BAL, ORIG_MATURITY_DATE, FIRST_RELEASE_DATE, FULL_RELASE_DATE, NPL_DATE, TIMES_DPD_10_29, TIMES_DPD_30_59, TIMES_DPD_60_89, TIMES_DPD_90_119, INT_PAID_YTD, PRINCIPAL_PAID_YTD, LATE_CHARGES_PAID_YTD, LT_DPD_10_29, LT_DPD_30_59, LT_DPD_60_89, LT_DPD_90_119, DATE_OPENED, GROUP_CD, CURR_TYPE, ACCR_BAL, ACCR_INT, CURR_MTH_PAYOFF, FINAL_PMT, PARTIAL_PMT, BILLED_PRN_AMT, BILLED_INT_AMT, BILLED_LATE_CHGS, BILLED_OTH_CHGS, YESTERDAY_BAL, PREV_MTH_OS, NEXT_SCHD_PMT_DATE, PMT_DAY_MTH, PAYOFF_DATE, MATURITY_DATE, FIRST_PMT_DATE, LAST_PMT_DATE, NEXT_PMT_DATE,

L72 PRIME_RT_NO, PRIME_RATE_FL, PRIME_RATE_CL, PMT_FREQ_CD, ALT_PMT_SCHD, INT_PAID_LASTYEAR, LC_PAID_LASTYEAR, TIME_EXT_LASTYEAR, LTD_INT_PAID, PURPOSE_CD, NPL_STATUS, STATUS_DATE, ALT_RATE, YST_ACCR_BAL, CLASS_ACCT_CD, UNDRAWN_LMT, PERCENT_UNDRAWN, PERCENT_UNDRAWN_ABV_LMT, MTD_ACCR_INT_DBT, MTD_LT_CHG_DBT, MTD_OTH_CHG_DBT, MTD_PRINCIPAL_PAID, MTD_INT_PAID, MTD_LC_PAID, MTD_MISC_CHG_PAID, MTD_OTH_CHG_PAID, YTD_PMT_RCVD, YTD_OTH_CHG_PAID, LTD_PMT_RECVD, LTD_ACCR_INT_DBT, LTD_LATE_CHG_DBT, LTD_OTH_CHG_DBT, LTD_PRINCIPAL_PAID, LTD_LC_PAID, LTD_OTH_CHG_PAID, ORIG_PMT_AMT, BUC, INT_IN_SUSPENSE, PK_NO, PK_DATE, KOLEKTIBILITAS, SECTOR_ECONOMY, ACC_TYPE, CREDIT_TYPE, BI_CREDIT_PURPOSE, BI_LOCATION, BI_KUK_NONUK, BI_DEBITOR, BI_ORIENTATION, BI_DEFAULT_CD, BI_DEFAULT_DATE, BI_PROJ_VALUE, BI_PROJ_LOC_CD, BI_PROJ_ADDR, BI_REASON_CD, LOS_APPLN_NO, BLACKLIST_FLAG, SP3K_NO, PROV_BANK, NOTARY_FEE, APPRAISAL_FEE, SKMHT_FEE, LIFE_INS_FEE, FIRST_PMT, PRA_REALISASI_FLAG, INSURANCE_CD, FIRE_INSURANCE_CD, ADMINISTRATION_FEE, AUTO_INS_FEE, STAMP_FEE, CUST_GIRO, BLOCK_ACC_BAL, DOWN_PMT, AUTO_INS_CD, HOME_INS_FEE, OLD_CIF_OTH, OLD_CIF_ACC_NO, ADMIN_FEE_DEBT, SP2D_PRNT_DEBT, POSTING_DATE, TRANS_AMT, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO, PERIOD) SELECT BRANCH_NO, ACCT_NO, ACCT_TYPE, HOST_APPLN_NO, CIF_NO, STATUS_CD, LOAN_TYPE, ORIG_BAL, RELEASE_AMT, CURR_BAL, MISC_INSURANCE_PREMIUM, OTH_CHARGES, MISC_COST, PAID_LATE_CHARGES, MISC_INSURANCE_REBATE, PAYMENT_AMT, CHARGEOFF_DATE, PAY_OFF_DATE, INT_RATE, LOAN_TERM, LOAN_TERM_CD, PAYMENT_FREQUENCY, ORIG_LOAN_DATE, DEVELOPER_CD, MTD_PAYMENT_RCVD, FACILITY_CD, FACILITY_SEQ, BANK_NO, REPORTING_BRANCH, SHORT_NAME, ACCT_NAME1, ACCT_NAME2, OFFICER, PREVIOUS_STATUS, YEARLY_OPENING_BAL, AMT_TO_BE_RELEASED, LAST_PAYMENT_AMT, LAST_YEAR_WRITE_OFF, DRAWING_LIMIT, REDUCED_BAL, ORIG_MATURITY_DATE, FIRST_RELEASE_DATE, FULL_RELASE_DATE, NPL_DATE, TIMES_DPD_10_29, TIMES_DPD_30_59, TIMES_DPD_60_89, TIMES_DPD_90_119, INT_PAID_YTD, PRINCIPAL_PAID_YTD, LATE_CHARGES_PAID_YTD, LT_DPD_10_29, LT_DPD_30_59, LT_DPD_60_89, LT_DPD_90_119, DATE_OPENED, GROUP_CD, CURR_TYPE, ACCR_BAL, ACCR_INT, CURR_MTH_PAYOFF, FINAL_PMT, PARTIAL_PMT, BILLED_PRN_AMT, BILLED_INT_AMT, BILLED_LATE_CHGS, BILLED_OTH_CHGS, YESTERDAY_BAL, PREV_MTH_OS, NEXT_SCHD_PMT_DATE, PMT_DAY_MTH, PAYOFF_DATE, MATURITY_DATE, FIRST_PMT_DATE, LAST_PMT_DATE, NEXT_PMT_DATE, PRIME_RT_NO, PRIME_RATE_FL, PRIME_RATE_CL, PMT_FREQ_CD, ALT_PMT_SCHD, INT_PAID_LASTYEAR, LC_PAID_LASTYEAR, TIME_EXT_LASTYEAR, LTD_INT_PAID, PURPOSE_CD, NPL_STATUS, STATUS_DATE, ALT_RATE, YST_ACCR_BAL, CLASS_ACCT_CD, UNDRAWN_LMT, PERCENT_UNDRAWN, PERCENT_UNDRAWN_ABV_LMT, MTD_ACCR_INT_DBT, MTD_LT_CHG_DBT, MTD_OTH_CHG_DBT, MTD_PRINCIPAL_PAID, MTD_INT_PAID, MTD_LC_PAID, MTD_MISC_CHG_PAID, MTD_OTH_CHG_PAID, YTD_PMT_RCVD, YTD_OTH_CHG_PAID, LTD_PMT_RECVD, LTD_ACCR_INT_DBT, LTD_LATE_CHG_DBT, LTD_OTH_CHG_DBT, LTD_PRINCIPAL_PAID, LTD_LC_PAID, LTD_OTH_CHG_PAID, ORIG_PMT_AMT, BUC, INT_IN_SUSPENSE, PK_NO, PK_DATE, KOLEKTIBILITAS, SECTOR_ECONOMY, ACC_TYPE, CREDIT_TYPE, BI_CREDIT_PURPOSE, BI_LOCATION, BI_KUK_NONUK, BI_DEBITOR, BI_ORIENTATION, BI_DEFAULT_CD, BI_DEFAULT_DATE, BI_PROJ_VALUE, BI_PROJ_LOC_CD, BI_PROJ_ADDR, BI_REASON_CD, LOS_APPLN_NO, BLACKLIST_FLAG, SP3K_NO, PROV_BANK, NOTARY_FEE, APPRAISAL_FEE, SKMHT_FEE, LIFE_INS_FEE, FIRST_PMT, PRA_REALISASI_FLAG, INSURANCE_CD, FIRE_INSURANCE_CD, ADMINISTRATION_FEE, AUTO_INS_FEE, STAMP_FEE, CUST_GIRO, BLOCK_ACC_BAL, DOWN_PMT, AUTO_INS_CD, HOME_INS_FEE, OLD_CIF_OTH, OLD_CIF_ACC_NO, ADMIN_FEE_DEBT, SP2D_PRNT_DEBT, POSTING_DATE, TRANS_AMT, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO,TO_CHAR(RECORD_TO_DATE,'YYYYMM') PERIOD FROM D_ACCOUNT_DTL; COMMIT; -- DELETING RECORDS FROM D_ACCOUNT_DTL_HISTORY BY KEEPING 4 MONTHS RECORD SELECT COUNT(DISTINCT PERIOD) INTO V_PERIOD_COUNT FROM D_ACCOUNT_DTL_HISTORY; IF V_PERIOD_COUNT > 4 THEN DELETE FROM D_ACCOUNT_DTL_HISTORY WHERE PERIOD=(SELECT MIN(PERIOD)FROM D_ACCOUNT_DTL_HISTORY); COMMIT; END IF; --PROCESS FOR D_ACCT_DPD_ENH_HISTORY ELSIF I_PROC_ID=BMCONSTANT_ENH.FTD_DPD THEN INSERT INTO D_ACCT_DPD_ENH_HISTORY( ACCT_NO, DPD, INT_DPD, BRANCH_NO, OFFICER_CD, NXT_PMT_DUE_DATE, PRINCIPAL_DUE, INT_DUE, LT_CHGS_DUE, OTH_CHGS_DUE, MISC_COST_DUE, TOT_AMT_DUE, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO, PERIOD) SELECT

L73 ACCT_NO, DPD, INT_DPD, BRANCH_NO, OFFICER_CD, NXT_PMT_DUE_DATE, PRINCIPAL_DUE, INT_DUE, LT_CHGS_DUE, OTH_CHGS_DUE, MISC_COST_DUE, TOT_AMT_DUE, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO,TO_CHAR(RECORD_TO_DATE,'YYYYMM') PERIOD FROM D_ACCT_DPD_ENH; COMMIT; -- DELETING RECORDS FROM D_ACCT_DPD_ENH_HISTORY BY KEEPING 4 MONTHS RECORD SELECT COUNT(DISTINCT PERIOD) INTO V_PERIOD_COUNT FROM D_ACCT_DPD_ENH_HISTORY; IF V_PERIOD_COUNT > 4 THEN DELETE FROM D_ACCT_DPD_ENH_HISTORY WHERE PERIOD=(SELECT MIN(PERIOD)FROM D_ACCT_DPD_ENH_HISTORY); COMMIT; END IF; --PROCESS FOR D_APPLN_LOS_DTL_HISTORY ELSIF I_PROC_ID=BMCONSTANT_ENH.FTD_ALD THEN INSERT INTO D_APPLN_LOS_DTL_HISTORY( SALES_EXEC_ID, APPRAISAL_END_DATE, APPRAISAL_START_DATE, BI_CHECK_DATE, DISBMT_END_DATE, DOC_COLL_SPPK_END_DATE, APPR_END_DATE, FINAL_APPLN_STATUS, DTBO_START_DATE, INVST_END_DATE, INVST_START_DATE, APPR_START_DATE, SEND_LD_END_DATE, APPLN_RCVD_DATE, REGIONAL_ID, APPLN_NO, FS_LS_END_DATE, SEND_LD_START_DATE, DOC_COLL_SPPK_START_DATE, SPPK_END_DATE, DTBO_END_DATE, VER_ASG_DATE, VERFN_END_DATE, VERFN_START_DATE, BRANCH_CD, CHANNEL_CODE, COMPANY_CD, CUST_NO, REJ_REASON_CD, BRANCH_NO, AGENCY_CD, AO_CD, APPLN_ENTRY_DATE, APPRAISAL_FLAG, APPLN_REMARK, APPROVAL_BY, APPROVAL_DATE, APROVAL_DEV_FLAG, APROVAL_DEV_BY, BI_CHECKING, BI_STA, BOOKING_BY, BOOKING_STATUS, CONFIRM_BOOKING, DATA_ENTRY_BY, INV_ENTRY_DATE, LAST_TRACK, LAST_TRACK_BY, LAST_TRACK_DATE, NAIK_ATAS, NAK_BY, NOTARY_RCVD_DATE, NOTARY_SEND_DATE, OVERRIDE_REASON_CD, ORG_MARKT_NAME, ORG_OFR_NAME, OVERRIDE_STATUS, OVERRIDE_BY, OVERRIDE_DATE, PROSPECT_ID, RCVD_BY_LD_BY, SCORING_BY, SPPK_BY, SPPK_CONFIRM_BY, TBO_BY, TBO_COPY_BY, TBO_COPY_DATE, VER_ENTRY_DATE, VER_FLAG, AREA_ID, MEDIA_ID, PRG_CODE, SOURCE_PROSPECT, BLACKLIST_STATUS, VVIP_FLAG,REL_BANK_MANDIRI, DATE_ENTRY_DATE, INV_DATE, NOTARY_DATE, REJECT_BY, REJECT_DATE, REJECT_LAST_TRACK_BY, REJECT_LAST_TRACK_DATE, SCORING_FLAG, SCORING_BY1, SCORING_DATE, SIGN_DATE, BANDING_STATUS, TBO_ORG_DATE, UNDO_LEGAL_DATE, VER_DATE, VER_OFFICER, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER,UPDATED_DTM, PROCESS_SEQ_NO, PERIOD) SELECT SALES_EXEC_ID, APPRAISAL_END_DATE, APPRAISAL_START_DATE, BI_CHECK_DATE, DISBMT_END_DATE, DOC_COLL_SPPK_END_DATE, APPR_END_DATE, FINAL_APPLN_STATUS, DTBO_START_DATE, INVST_END_DATE, INVST_START_DATE, APPR_START_DATE, SEND_LD_END_DATE, APPLN_RCVD_DATE, REGIONAL_ID, APPLN_NO, FS_LS_END_DATE, SEND_LD_START_DATE, DOC_COLL_SPPK_START_DATE, SPPK_END_DATE, DTBO_END_DATE, VER_ASG_DATE, VERFN_END_DATE, VERFN_START_DATE, BRANCH_CD, CHANNEL_CODE, COMPANY_CD, CUST_NO, REJ_REASON_CD, BRANCH_NO, AGENCY_CD, AO_CD, APPLN_ENTRY_DATE, APPRAISAL_FLAG, APPLN_REMARK, APPROVAL_BY, APPROVAL_DATE, APROVAL_DEV_FLAG, APROVAL_DEV_BY, BI_CHECKING, BI_STA, BOOKING_BY, BOOKING_STATUS, CONFIRM_BOOKING, DATA_ENTRY_BY, INV_ENTRY_DATE, LAST_TRACK, LAST_TRACK_BY, LAST_TRACK_DATE, NAIK_ATAS, NAK_BY, NOTARY_RCVD_DATE, NOTARY_SEND_DATE, OVERRIDE_REASON_CD, ORG_MARKT_NAME, ORG_OFR_NAME, OVERRIDE_STATUS, OVERRIDE_BY, OVERRIDE_DATE, PROSPECT_ID, RCVD_BY_LD_BY, SCORING_BY, SPPK_BY, SPPK_CONFIRM_BY, TBO_BY, TBO_COPY_BY, TBO_COPY_DATE, VER_ENTRY_DATE, VER_FLAG, AREA_ID, MEDIA_ID, PRG_CODE, SOURCE_PROSPECT, BLACKLIST_STATUS, VVIP_FLAG,REL_BANK_MANDIRI, DATE_ENTRY_DATE, INV_DATE, NOTARY_DATE, REJECT_BY, REJECT_DATE, REJECT_LAST_TRACK_BY,REJECT_LAST_TRACK_DATE, SCORING_FLAG, SCORING_BY1, SCORING_DATE, SIGN_DATE, BANDING_STATUS, TBO_ORG_DATE, UNDO_LEGAL_DATE, VER_DATE, VER_OFFICER, RECORD_TO_DATE, CREATED_USER, CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO, TO_CHAR(RECORD_TO_DATE,'YYYYMM')PERIOD FROM D_APPLN_LOS_DTL; COMMIT; SELECT COUNT(DISTINCT PERIOD) INTO V_PERIOD_COUNT FROM D_APPLN_LOS_DTL_HISTORY; IF V_PERIOD_COUNT > 4 THEN DELETE FROM D_APPLN_LOS_DTL_HISTORY WHERE PERIOD=(SELECT MIN(PERIOD)FROM D_APPLN_LOS_DTL_HISTORY); COMMIT; END IF; ELSIF I_PROC_ID=BMCONSTANT_ENH.FTD_FLD THEN INSERT INTO D_FACILITY_LOS_DTL_HISTORY( APPLN_NO, INT_RATE, SPPK_LOAN_AMT, FINAL_LOAN_TERM, DISBMT_START_DATE, APPLIED_LOAN_AMT, APPLIED_LOAN_TERM,PROD_PRG_CD, APPROVE_FLAG, INSTALLMENT_AMT, INT_TYPE, INSR_CASH_AMT, INSR_CASH_PERCENT, INSR_TYPE, APPLIED_INT,APPLIED_INT_TYPE, PK_DATE, PK_NO, SPPK_DATE, LOAN_PURPOSE, SPPK_NO, DSR, LTV, RECORD_TO_DATE, CREATED_USER,CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO, PERIOD,TOP_UP_FLAG , ACCT_NO,CURR_BAL, APPL_ADD_LIMIT, DECS_ADD_LIMIT,CARD_BUILDING_FLAG) SELECT APPLN_NO, INT_RATE, SPPK_LOAN_AMT, FINAL_LOAN_TERM, DISBMT_START_DATE, APPLIED_LOAN_AMT, PROD_PRG_CD, APPROVE_FLAG, INSTALLMENT_AMT, INT_TYPE, INSR_CASH_AMT, INSR_CASH_PERCENT, INSR_TYPE, APPLIED_INT,APPLIED_INT_TYPE, PK_DATE, PK_NO, SPPK_DATE, LOAN_PURPOSE, SPPK_NO, DSR, LTV, RECORD_TO_DATE, CREATED_USER,CREATED_DTM, UPDATED_USER, UPDATED_DTM, PROCESS_SEQ_NO,TO_CHAR(RECORD_TO_DATE,'YYYYMM') PERIOD,TOP_UP_FLAG , ACCT_NO,CURR_BAL,APPL_ADD_LIMIT,DECS_ADD_LIMIT, CARD_BUILDING_FLAG FROM D_FACILITY_LOS_DTL; COMMIT; SELECT COUNT(DISTINCT PERIOD) INTO V_PERIOD_COUNT FROM D_FACILITY_LOS_DTL_HISTORY; IF V_PERIOD_COUNT > 4 THEN DELETE FROM D_FACILITY_LOS_DTL_HISTORY WHERE PERIOD=(SELECT MIN(PERIOD)FROM D_FACILITY_LOS_DTL_HISTORY); COMMIT; END IF; ELSE BMGLOBAL_ENH.PROCLOG('HISTORY TABLE ENTRY IS NOT REQUIRED FOR ' || I_PROC_ID); END IF;

L74 EXCEPTION WHEN OTHERS THEN NULL; END;/

¾

CREATE PROCEDURE DBP_JOB_MIS_KPI_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_MIS_KPI_ENH IS V_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.SCHEDULER_JOB_MIS_KPI_ENH; V_START_TIME DATE; V_END_TIME DATE; V_MAX_DATE DATE ; V_MAX_DATE1 DATE ; V_BUSINESS_DATE DATE ; V_REMARKS VARCHAR2(2000) := 'SUCCESS' ; V_STATUS VARCHAR2(2) := '0' ; V_SEVERITY VARCHAR2(2) := '0' ; V_EXCEP EXCEPTION; BEGIN V_START_TIME := SYSDATE; BMGLOBAL_ENH.PROCESS_NAME

:= 'DBP_JOB_MIS_KPI_ENH' ;

-- SEQ NO GENERATION SELECT S_PROCESS_NO.NEXTVAL INTO V_SEQ_NO FROM DUAL; SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS AND PROCESS_ID IN (BMCONSTANT_ENH.FTD_LAF,BMCONSTANT_ENH.FTD_LNM,BMCONSTANT_ENH.FTD_DPD); V_BUSINESS_DATE := V_MAX_DATE ; BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS KPI ENH JOB STARTED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.CHECK_PROCESS_STATUS(V_BUSINESS_DATE,V_PROCESS_ID,V_REMARKS, V_STATUS,V_SEQ_NO); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG ('MIS KPI ENH JOB STARTED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_KPI_ENH' ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE, BMCONSTANT_ENH.MIS_KPI_ENH, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : KPI REPORT FAILED FOR THE DATE:' || V_BUSINESS_DATE); BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_KPI_ENH' ; V_END_TIME := SYSDATE; V_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS KPI ENH JOB COMPLETED SUCCESSFULLY FOR THE DATE'||V_BUSINESS_DATE); COMMIT; EXCEPTION WHEN V_EXCEP THEN,ROLLBACK; V_END_TIME := SYSDATE; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS KPI ENH JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); COMMIT; WHEN OTHERS THEN V_REMARKS := SQLERRM; V_END_TIME := SYSDATE; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0,V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS KPI ENH JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_JOB_MIS_KPI_ENH; /

L75 ¾

CREATE PROCEDURE DBP_JOB_MIS_AVG_TURNAROUND_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_MIS_AVG_TURNAROUND_ENH IS V_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.SCHEDULER_JOB_MIS_AVG_TA_ENH; V_START_TIME DATE; V_END_TIME DATE; V_MAX_DATE DATE ; V_MAX_DATE1 DATE ; V_BUSINESS_DATE DATE ; V_REMARKS VARCHAR2(2000) := 'SUCCESS' ; V_STATUS VARCHAR2(2) := '0' ; V_SEVERITY VARCHAR2(2) := '0' ; V_EXCEP EXCEPTION; BEGIN V_START_TIME := SYSDATE; BMGLOBAL_ENH.PROCESS_NAME

:= 'DBP_JOB_MIS_AVG_TURNAROUND_ENH' ;

-- SEQ NO GENERATION SELECT S_PROCESS_NO.NEXTVAL INTO V_SEQ_NO FROM DUAL; SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS AND PROCESS_ID IN (BMCONSTANT_ENH.FTD_LAF,BMCONSTANT_ENH.FTD_LNM,BMCONSTANT_ENH.FTD_DPD); V_BUSINESS_DATE := V_MAX_DATE ; BMGLOBAL_ENH.PROCLOG('SCHEDULER '||V_BUSINESS_DATE) ;

MIS

AVERAGE

TURNAROUND

TIME

ENH

JOB

STARTED

FOR

THE

DATE

BMGLOBAL_ENH.CHECK_PROCESS_STATUS(V_BUSINESS_DATE,V_PROCESS_ID,V_REMARKS, V_STATUS,V_SEQ_NO); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCLOG('MIS AVERAGE TURNAROUND TIME ENH JOB STARTED FOR THE DATE:' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_AVG_TURNAROUND_ENH' ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE, BMCONSTANT_ENH.MIS_AVG_TURNAROUND_TIME_ENH, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : DETAILED AVERAGE TURNAROUND TIME REPORT FAILED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_AVG_TURNAROUND_ENH' ; V_END_TIME := SYSDATE; V_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS AVERAGE TURNAROUND TIME ENH JOB COMPLETED SUCCESSFULLY FOR THE DATE '||V_BUSINESS_DATE) ; COMMIT; EXCEPTION WHEN V_EXCEP THENROLLBACK; V_END_TIME := SYSDATE; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS AVERAGE TURNAROUND TIME ENH JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); COMMIT; WHEN OTHERS THEN V_REMARKS := SQLERRM; V_END_TIME := SYSDATE; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_JOB_MIS_AVG_TURNAROUND_ENH;

AVERAGE TURNAROUND TIME ENH

JOB FAILED FOR

THE DATE

L76 /

¾

CREATE PROCEDURE DBP_JOB_MIS_LOAN_LINE_ANLS_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_MIS_LOAN_LINE_ANLS_ENH IS V_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.SCHEDULER_JOB_MIS_LLA_ENH; V_START_TIME DATE; V_END_TIME DATE; V_MAX_DATE DATE ; V_MAX_DATE1 DATE ; V_BUSINESS_DATE DATE ; V_REMARKS VARCHAR2(2000) := 'SUCCESS' ; V_STATUS VARCHAR2(2) := '0' ; V_SEVERITY VARCHAR2(2) := '0' ; V_EXCEP EXCEPTION; BEGIN V_START_TIME := SYSDATE; BMGLOBAL_ENH.PROCESS_NAME

:= 'DBP_JOB_MIS_LOAN_LINE_ANLS_ENH' ;

SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS AND PROCESS_ID IN (BMCONSTANT_ENH.FTD_LAF,BMCONSTANT_ENH.FTD_LNM,BMCONSTANT_ENH.FTD_DPD); V_BUSINESS_DATE := V_MAX_DATE ; IF (V_BUSINESS_DATE = LAST_DAY(V_BUSINESS_DATE)) THEN SELECT S_PROCESS_NO.NEXTVAL INTO V_SEQ_NO FROM DUAL; BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS LOAN LINE ANALYSIS '||V_BUSINESS_DATE) ;

ENH

JOB

STARTED

FOR

THE

DATE

BMGLOBAL_ENH.CHECK_PROCESS_STATUS(V_BUSINESS_DATE,V_PROCESS_ID,V_REMARKS, V_STATUS,V_SEQ_NO); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_LOAN_LINE_ANLS_ENH' ; BMGLOBAL_ENH.PROCLOG ('MIS LOAN LINE ANALYSIS ENH JOB STARTED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE,BMCONSTANT_ENH.MIS_LOAN_LINE_ANLS_ENH, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : LOAN LINE ANALYSIS REPORT FAILED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_LOAN_LINE_ANLS_ENH' ; V_END_TIME := SYSDATE; V_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS LOAN LINE ANALYSIS ENH JOB COMPLETED SUCCESSFULLY FOR THE DATE '||V_BUSINESS_DATE) ; COMMIT; END IF; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; V_END_TIME := SYSDATE; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); COMMIT;

LOAN

LINE

ANALYSIS

ENH

JOB

FAILED

FOR

THE

DATE

WHEN OTHERS THEN V_REMARKS := SQLERRM; V_END_TIME := SYSDATE; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS LOAN LINE ANALYSIS ENH JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_JOB_MIS_LOAN_LINE_ANLS_ENH; /

L77 ¾

CREATE PROCEDURE DBP_JOB_MIS_MTR_LOAN_ANLS_ENH CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_MIS_MTR_LOAN_ANLS_ENH IS V_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.SCHEDULER_JOB_MIS_MTR_LLA_ENH; V_START_TIME DATE; V_END_TIME DATE; V_MAX_DATE DATE ; V_MAX_DATE1 DATE ; V_BUSINESS_DATE DATE ; V_REMARKS VARCHAR2(2000) := 'SUCCESS' ; V_STATUS VARCHAR2(2) := '0' ; V_SEVERITY VARCHAR2(2) := '0' ; V_EXCEP EXCEPTION; BEGIN V_START_TIME := SYSDATE; BMGLOBAL_ENH.PROCESS_NAME

:= 'DBP_JOB_MIS_MTR_LOAN_ANLS_ENH' ;

SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS AND PROCESS_ID IN (BMCONSTANT_ENH.FTD_LAF,BMCONSTANT_ENH.FTD_LNM,BMCONSTANT_ENH.FTD_DPD); V_BUSINESS_DATE := V_MAX_DATE ; IF (V_BUSINESS_DATE = LAST_DAY(V_BUSINESS_DATE)) THEN -- SEQ NO GENERATION SELECT S_PROCESS_NO.NEXTVAL INTO V_SEQ_NO FROM DUAL; BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS MITRA LOAN LINE ANALYSIS ENH JOB STARTED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.CHECK_PROCESS_STATUS(V_BUSINESS_DATE,V_PROCESS_ID,V_REMARKS, V_STATUS,V_SEQ_NO); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_MTR_LOAN_ANLS_ENH' ; BMGLOBAL_ENH.PROCLOG ('MIS MITRA LOAN LINE ANALYSIS ENH JOB STARTED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE,BMCONSTANT_ENH.MIS_MITRA_LOAN_LINE_ANLS_ENH, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : MITRAKARYA LOAN LINE ANALYSIS REPORT FAILED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_MTR_LOAN_ANLS_ENH' ; V_END_TIME := SYSDATE; V_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS MITRA LOAN LINE ANALYSIS ENH JOB COMPLETED SUCCESSFULLY FOR THE DATE '||V_BUSINESS_DATE) ; COMMIT; END IF; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; V_END_TIME := SYSDATE; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS MITRA LOAN LINE ANALYSIS ENH JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); COMMIT; WHEN OTHERS THEN V_REMARKS := SQLERRM; V_END_TIME := SYSDATE; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS MITRA LOAN LINE ANALYSIS ENH JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_JOB_MIS_MTR_LOAN_ANLS_ENH; /

L78 ¾

CREATE PROCEDURE DBP_JOB_MIS_PROFIT_LOSS_ALL CREATE OR REPLACE PROCEDURE BMCLN2.DBP_JOB_MIS_PROFIT_LOSS_ALL IS V_CNT PLS_INTEGER := 0; V_SEQ_NO PLS_INTEGER := 0; V_PROCESS_ID PLS_INTEGER := BMCONSTANT_ENH.SCHEDULER_JOB_MIS_PL_ALL; V_START_TIME DATE; V_END_TIME DATE; V_MAX_DATE DATE ; V_MAX_DATE1 DATE ; V_BUSINESS_DATE DATE ; V_REMARKS VARCHAR2(2000) := 'SUCCESS' ; V_STATUS VARCHAR2(2) := '0' ; V_SEVERITY VARCHAR2(2) := '0' ; V_EXCEP EXCEPTION; BEGIN V_START_TIME := SYSDATE; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_PROFIT_LOSS_ALL' ; SELECT MAX(PROCESS_DATE) INTO V_MAX_DATE FROM PROCESS_STATUS_ENH WHERE PROCESS_STATUS = BMCONSTANT_ENH.SUCCESS AND PROCESS_ID IN (BMCONSTANT_ENH.FTD_LAF,BMCONSTANT_ENH.FTD_LNM,BMCONSTANT_ENH.FTD_DPD); V_BUSINESS_DATE := V_MAX_DATE ; IF (V_BUSINESS_DATE = LAST_DAY(V_BUSINESS_DATE)) THEN -- SEQ NO SELECT INTO FROM

GENERATION S_PROCESS_NO.NEXTVAL V_SEQ_NO DUAL;

BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS PROFIT LOSS ALL JOB STARTED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.CHECK_PROCESS_STATUS(V_BUSINESS_DATE,V_PROCESS_ID,V_REMARKS, V_STATUS,V_SEQ_NO); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN RAISE V_EXCEP; END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_PROFIT_LOSS_ALL' ; BMGLOBAL_ENH.PROCLOG ('MIS PROFIT LOSS ALL JOB STARTED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.RUN_PROC (V_BUSINESS_DATE,BMCONSTANT_ENH.MIS_PROFIT_LOSS_ALL, V_STATUS, V_SEVERITY, V_REMARKS); IF V_STATUS <> BMCONSTANT_ENH.SUCCESS THEN BMGLOBAL_ENH.PROCLOG('ENHANCEMENT MAIN JOB : MIS PROFIT LOSS ALL REPORT FAILED FOR THE DATE : ' || V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); END IF; BMGLOBAL_ENH.PROCESS_NAME := 'DBP_JOB_MIS_PROFIT_LOSS_ALL' ; V_END_TIME := SYSDATE; V_REMARKS := 'SUCCESS'; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.SUCCESS,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS PROFIT LOSS ALL JOB COMPLETED SUCCESSFULLY FOR THE DATE '||V_BUSINESS_DATE) ; COMMIT; END IF; EXCEPTION WHEN V_EXCEP THEN ROLLBACK; V_END_TIME := SYSDATE; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS PROFIT LOSS ALL JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(0,V_REMARKS); COMMIT; WHEN OTHERS THEN V_REMARKS := SQLERRM; V_END_TIME := SYSDATE; ROLLBACK; BMGLOBAL_ENH.INSERT_PROC_STATUS(V_PROCESS_ID, 5, BMCONSTANT_ENH.FAIL,0,0, V_REMARKS,V_SEQ_NO, V_BUSINESS_DATE, NULL, V_START_TIME, SYSDATE); BMGLOBAL_ENH.PROCLOG('SCHEDULER MIS MIS PROFIT LOSS ALL JOB FAILED FOR THE DATE '||V_BUSINESS_DATE) ; BMGLOBAL_ENH.ERRORLOG(SQLCODE,' '); END DBP_JOB_MIS_PROFIT_LOSS_ALL; /

L79 Lampiran 4 Keterangan Surat Survei

Jakarta, 30 Desember 2008

No

: 06/SP/10/2008

Hal

: Keterangan Surat Survei

Kepada Yth, Universitas Bina Nusantara Jl. K.H. S yahdan No.9 Kemanggisan/Palmerah Jakarta Barat 11480 Dengan Hormat, Dengan ini kami menyatakan bahwa mahasiswa Fakultas Ilmu Komputer, Jurusan Teknik Informatika, peminatan Oracle BiNus University berikut : No. 1. 2. 3.

N.I.M 0900788395 0900800135 0900820723

Nama Mahasiswa Willy Wijaya Lydia Theodore Gunawan Liauw

Jurusan Teknik Informatika Teknik Informatika Teknik Informatika

telah melakukan survei pada tanggal 4 September – 30 Desember 2008 pada perusahaan kami. Demikian Surat Keterangan ini untuk dapat dipergunakan sebagaimana mestinya.

Hormat Kami, PT. PT.Bank M andiri (Persero),Tbk

[ Achmad Taufik ] Pimpinan IT-BSA IM S PT.Bank M andiri

La mpiran 1 Hasil Wawancara

Recommend Documents