1202 lines
34 KiB
Plaintext
1202 lines
34 KiB
Plaintext
DEFINE PKG = Map_Dwh
|
|
|
|
CREATE OR REPLACE PACKAGE BODY &PKG IS
|
|
|
|
|
|
-- Specific Method Declaration
|
|
|
|
PROCEDURE Ext_Staging (
|
|
date_ IN DATE,
|
|
user_ IN VARCHAR2,
|
|
seq_ IN NUMBER,
|
|
type_ IN VARCHAR2,
|
|
job_id_ IN NUMBER );
|
|
|
|
|
|
PROCEDURE Build_Reconcile (
|
|
date_ IN DATE,
|
|
user_ IN VARCHAR2,
|
|
data_ IN VARCHAR2,
|
|
prev_ IN NUMBER,
|
|
seq_ IN NUMBER,
|
|
job_id_ IN NUMBER );
|
|
|
|
|
|
FUNCTION Get_Seq_Process (
|
|
job_id_ IN NUMBER ) RETURN NUMBER;
|
|
|
|
|
|
PROCEDURE Build_History_Saldo (
|
|
date_ IN DATE );
|
|
|
|
|
|
PROCEDURE Build_Gl_Saldo (
|
|
date_ IN DATE );
|
|
|
|
|
|
PROCEDURE Build_Treasury_Transaction (
|
|
date_ IN DATE );
|
|
|
|
|
|
PROCEDURE Update_Status (
|
|
type_ IN VARCHAR2 );
|
|
|
|
|
|
-- Specific Method Implementation
|
|
|
|
|
|
FUNCTION Get_Job_Date (
|
|
modul_ IN VARCHAR2 ) RETURN NUMBER
|
|
IS
|
|
a0mo_ a0mo_staging%ROWTYPE;
|
|
dt_ NUMBER;
|
|
CURSOR get_data IS
|
|
SELECT *
|
|
--FROM a0mo_staging
|
|
FROM a0mo_ext
|
|
WHERE mocode = modul_;
|
|
BEGIN
|
|
OPEN get_data;
|
|
FETCH get_data INTO a0mo_;
|
|
CLOSE get_data;
|
|
dt_ := a0mo_.modtjb;
|
|
/* IF a0mo_.mofleo = 2 AND a0mo_.moflbo = 0 THEN
|
|
dt_ := a0mo_.modtpw;
|
|
ELSE
|
|
IF a0mo_.mofleo = 0 AND a0mo_.moflbo = 1 THEN
|
|
dt_ := a0mo_.modtjb;
|
|
END IF;
|
|
END IF;*/
|
|
RETURN dt_;
|
|
END Get_Job_Date;
|
|
|
|
|
|
FUNCTION Get_Prev_Saldo (
|
|
account_number_ IN VARCHAR2,
|
|
date_ IN DATE ) RETURN NUMBER
|
|
IS
|
|
saldo_ NUMBER;
|
|
date_chr_ VARCHAR2(8);
|
|
hist_ VARCHAR2(4000);
|
|
q_name_ VARCHAR2(6);
|
|
|
|
CURSOR Get_Saldo IS
|
|
SELECT hist_saldo
|
|
FROM hist_saldo_tab
|
|
WHERE nomor_rekening = account_number_
|
|
AND q_period = q_name_;
|
|
BEGIN
|
|
q_name_ := to_char(date_, 'YYYY"Q"Q');
|
|
date_chr_ := to_char(date_, 'YYYYMMDD');
|
|
OPEN get_saldo;
|
|
FETCH get_saldo INTO hist_;
|
|
IF (get_saldo%FOUND) THEN
|
|
CLOSE get_saldo;
|
|
saldo_ := to_number(sys_attr.Get_Item_Value(date_chr_, hist_));
|
|
ELSE
|
|
CLOSE get_saldo;
|
|
saldo_ := 0;
|
|
END IF;
|
|
RETURN NVL(saldo_, 0);
|
|
END Get_Prev_Saldo;
|
|
|
|
|
|
PROCEDURE Ext_Staging (
|
|
date_ IN DATE,
|
|
user_ IN VARCHAR2,
|
|
seq_ IN NUMBER,
|
|
type_ IN VARCHAR2,
|
|
job_id_ IN NUMBER )
|
|
IS
|
|
prev_ NUMBER;
|
|
stg_tab_ VARCHAR2(30);
|
|
block_ VARCHAR2(2000);
|
|
status_ VARCHAR2(2000);
|
|
modul_ VARCHAR2(3);
|
|
|
|
CURSOR get_table IS
|
|
SELECT tab_name
|
|
FROM external_data_tab
|
|
WHERE modul = modul_
|
|
ORDER BY tab_name;
|
|
|
|
TYPE tab_ IS TABLE OF VARCHAR2(30);
|
|
v_tab_ tab_;
|
|
BEGIN
|
|
IF type_ = '01' THEN -- AB2
|
|
modul_ := 'AB2';
|
|
ELSIF type_ = '02' THEN -- treasury
|
|
modul_ := 'TSY';
|
|
ELSIF type_ = '04' THEN -- GL AB2
|
|
modul_ := 'GL';
|
|
END IF;
|
|
|
|
OPEN get_table;
|
|
FETCH get_table BULK COLLECT INTO v_tab_;
|
|
CLOSE get_table;
|
|
|
|
IF (v_tab_.COUNT > 0) THEN
|
|
FOR i IN v_tab_.FIRST..v_tab_.LAST LOOP
|
|
stg_tab_ := v_tab_(i) || '_STAGING';
|
|
dbms_output.put_line('stg_tab_ ' || stg_tab_);
|
|
dbms_output.put_line('v_ab2_tab ' || v_tab_(i));
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || stg_tab_ INTO prev_;
|
|
dbms_output.put_line('prev_ ' || prev_);
|
|
IF type_ IN ('01', '04') THEN
|
|
block_ := 'BEGIN MAP_' ||v_tab_(i) || '_STAGING.Main(:status); END;';
|
|
dbms_output.put_line(block_);
|
|
EXECUTE IMMEDIATE block_ USING OUT status_;
|
|
ELSIF type_ = '02' THEN
|
|
block_ := 'BEGIN MAP_' ||v_tab_(i) || '_STAGING.Main(:status, :date); END;';
|
|
dbms_output.put_line(block_);
|
|
EXECUTE IMMEDIATE block_ USING OUT status_, IN to_char(date_, 'YYYYMMDD');
|
|
END IF;
|
|
Build_Reconcile(date_, user_, v_tab_(i), prev_, seq_, job_id_);
|
|
END LOOP;
|
|
COMMIT;
|
|
END IF;
|
|
IF type_ IN ('01', '04') THEN
|
|
Update_Status(type_);
|
|
COMMIT;
|
|
END IF;
|
|
END Ext_Staging;
|
|
|
|
|
|
PROCEDURE Build_Reconcile (
|
|
date_ IN DATE,
|
|
user_ IN VARCHAR2,
|
|
data_ IN VARCHAR2,
|
|
prev_ IN NUMBER,
|
|
seq_ IN NUMBER,
|
|
job_id_ IN NUMBER )
|
|
IS
|
|
target_ VARCHAR2(30);
|
|
source_ VARCHAR2(30);
|
|
|
|
cnt_t_ NUMBER;
|
|
cnt_s_ NUMBER;
|
|
BEGIN
|
|
target_ := data_ || '_STAGING';
|
|
source_ := data_ || '_EXT';
|
|
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || target_ INTO cnt_t_;
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || source_ INTO cnt_s_;
|
|
|
|
dbms_output.put_line('target_ ' || target_ || ' - ' || cnt_t_);
|
|
dbms_output.put_line('source_ ' || source_ || ' - ' || cnt_s_);
|
|
|
|
INSERT INTO reconcile_staging_tab (
|
|
date_process, seq_process, ab2_data, previous_data, current_data, ext_data,
|
|
user_process, time_process, source_data, target_data, job_id )
|
|
VALUES (
|
|
date_, seq_, data_, prev_, cnt_t_, cnt_s_,
|
|
user_, to_char(SYSDATE, 'HH24MISS'), source_, target_, job_id_);
|
|
END Build_Reconcile;
|
|
|
|
|
|
FUNCTION Get_Seq_Process (
|
|
job_id_ IN NUMBER ) RETURN NUMBER
|
|
IS
|
|
seq_ NUMBER;
|
|
CURSOR get_seq IS
|
|
SELECT NVL(max(seq_process), 0) seq_process
|
|
FROM reconcile_staging_tab
|
|
WHERE job_id = job_id_;
|
|
BEGIN
|
|
OPEN get_seq;
|
|
FETCH get_seq INTO seq_;
|
|
CLOSE get_seq;
|
|
seq_ := NVL(seq_,0) + 1;
|
|
RETURN seq_;
|
|
END Get_Seq_Process;
|
|
|
|
|
|
|
|
PROCEDURE Build_History_Saldo (
|
|
date_ IN DATE )
|
|
IS
|
|
q_name_ hist_saldo_tab.q_period%TYPE;
|
|
hist_ hist_saldo_tab.hist_saldo%TYPE;
|
|
prev_bal_ NUMBER;
|
|
bal_ NUMBER;
|
|
|
|
f_ CONSTANT VARCHAR2(1) := chr(30);
|
|
r_ CONSTANT VARCHAR2(1) := chr(31);
|
|
|
|
prev_ NUMBER;
|
|
curr_ NUMBER;
|
|
dt_prev_ DATE;
|
|
dt_curr_ DATE;
|
|
dt_ DATE;
|
|
dtn_ NUMBER;
|
|
|
|
sql_ VARCHAR2(2000);
|
|
amt_ NUMBER;
|
|
|
|
TYPE recode IS TABLE OF VARCHAR2(10);
|
|
TYPE reclbl IS TABLE OF NUMBER;
|
|
TYPE redten IS TABLE OF NUMBER;
|
|
|
|
v_recode recode;
|
|
v_reclbl reclbl;
|
|
v_redten redten;
|
|
|
|
CURSOR get_m5re IS
|
|
SELECT r.recode, r.reclbl, r.redten
|
|
FROM m5re_staging r
|
|
WHERE (r.restat = '1' OR r.redten = curr_)
|
|
ORDER BY r.recode;
|
|
|
|
|
|
CURSOR check_hist(acc_num_ IN VARCHAR2) IS
|
|
SELECT h.hist_saldo
|
|
FROM hist_saldo_tab h
|
|
WHERE h.nomor_rekening = acc_num_
|
|
AND h.q_period = q_name_;
|
|
BEGIN
|
|
prev_ := to_number(Sys_General.Get_SysParam('LAST_HIST_SALDO'));
|
|
curr_ := Get_Job_Date('RT');
|
|
--to_number(Sys_General.Get_Desc('A0MO_STAGING', 'MOCODE', 'MODTJB', 'RT'));
|
|
dt_prev_ := to_date(prev_, 'YYYYMMDD');
|
|
dt_curr_ := to_date(curr_, 'YYYYMMDD');
|
|
|
|
OPEN get_m5re;
|
|
FETCH get_m5re BULK COLLECT INTO v_recode, v_reclbl, v_redten;
|
|
CLOSE get_m5re;
|
|
|
|
FOR i IN v_recode.FIRST..v_recode.LAST LOOP
|
|
prev_bal_ := NVL(map_dwh.Get_Prev_Saldo(v_recode(i), dt_prev_), 0);
|
|
dt_ := dt_prev_ + 1;
|
|
bal_ := prev_bal_;
|
|
|
|
WHILE dt_ <= dt_curr_ LOOP
|
|
q_name_ := to_char(dt_, 'YYYY"Q"Q');
|
|
dtn_ := to_number(to_char(dt_, 'YYYYMMDD'));
|
|
|
|
IF (v_redten(i) = 0 OR dtn_ <= v_redten(i)) THEN
|
|
sql_ := 'SELECT NVL(SUM(h.poamnt), 0)
|
|
FROM h8po_staging h
|
|
WHERE h.poreco = :reco AND h.podtpo = :dtn';
|
|
EXECUTE IMMEDIATE sql_ INTO amt_ USING v_recode(i), dtn_;
|
|
|
|
bal_ := bal_ + NVL(amt_, 0);
|
|
|
|
OPEN check_hist(v_recode(i));
|
|
FETCH check_hist INTO hist_;
|
|
IF check_hist%FOUND THEN
|
|
CLOSE check_hist;
|
|
hist_ := hist_ || to_char(dt_, 'YYYYMMDD') || f_ || to_char(bal_) || r_;
|
|
sql_ := 'UPDATE hist_saldo_tab SET hist_saldo = :hist
|
|
WHERE nomor_rekening = :v1 AND q_period = :v2';
|
|
EXECUTE IMMEDIATE sql_ USING hist_, v_recode(i), q_name_;
|
|
ELSE
|
|
CLOSE check_hist;
|
|
hist_ := to_char(dt_, 'YYYYMMDD') || f_ || to_char(bal_) || r_;
|
|
sql_ := 'INSERT INTO hist_saldo_tab VALUES (:v1, :v2, :v3)';
|
|
EXECUTE IMMEDIATE sql_ USING v_recode(i), q_name_, hist_;
|
|
END IF;
|
|
dt_ := dt_ + 1;
|
|
ELSE
|
|
EXIT;
|
|
END IF;
|
|
END LOOP;
|
|
COMMIT;
|
|
END LOOP;
|
|
END Build_History_Saldo;
|
|
|
|
|
|
PROCEDURE Build_Gl_Saldo (
|
|
date_ IN DATE )
|
|
IS
|
|
prev_ NUMBER;
|
|
dt_prev_ DATE;
|
|
curr_ NUMBER;
|
|
dt_curr_ DATE;
|
|
dtn_ NUMBER;
|
|
dt_ DATE;
|
|
|
|
q_name_ VARCHAR2(6);
|
|
base_ VARCHAR2(4000);
|
|
orig_ VARCHAR2(4000);
|
|
|
|
f_ CONSTANT VARCHAR2(1) := chr(30);
|
|
r_ CONSTANT VARCHAR2(1) := chr(31);
|
|
|
|
hist_id_ ROWID;
|
|
sql_ VARCHAR2(2000);
|
|
|
|
CURSOR get_data IS
|
|
SELECT t.kode_cabang, t.kode_coa, t.kode_currency,
|
|
sum(t.base_amount) base_amount, SUM(t.original_amount) original_amount
|
|
FROM alco_staging t
|
|
WHERE t.tanggal = to_char(dt_, 'YYYYMMDD')
|
|
GROUP BY t.kode_cabang, t.kode_coa, t.kode_currency
|
|
ORDER BY t.kode_cabang, t.kode_coa, t.kode_currency;
|
|
|
|
CURSOR check_hist(cabang_ IN VARCHAR2, coa_ IN VARCHAR2, currency_ IN VARCHAR2) IS
|
|
SELECT h.base_saldo, h.original_saldo, ROWID hist_id
|
|
FROM gl_saldo_tab h
|
|
WHERE h.kode_cabang = cabang_
|
|
AND h.kode_coa = coa_
|
|
AND h.kode_currency = currency_
|
|
AND h.q_period = q_name_;
|
|
|
|
TYPE kode_cabang IS TABLE OF VARCHAR2(3);
|
|
TYPE kode_coa IS TABLE OF VARCHAR2(5);
|
|
TYPE kode_currency IS TABLE OF VARCHAR2(3);
|
|
TYPE base_amount IS TABLE OF NUMBER;
|
|
TYPE original_amount IS TABLE OF NUMBER;
|
|
|
|
v_cabang kode_cabang;
|
|
v_coa kode_coa;
|
|
v_currency kode_currency;
|
|
v_base base_amount;
|
|
v_original original_amount;
|
|
BEGIN
|
|
prev_ := to_number(Sys_General.Get_SysParam('LAST_HIST_SALDO'));
|
|
curr_ := to_number(Sys_General.Get_Desc('A0MO_STAGING', 'MOCODE', 'MODTJB', 'GL'));
|
|
dt_prev_ := to_date(prev_, 'YYYYMMDD');
|
|
dt_curr_ := to_date(curr_, 'YYYYMMDD');
|
|
|
|
dt_ := dt_prev_;
|
|
WHILE dt_ <= dt_curr_ LOOP
|
|
dtn_ := to_number(to_char(dt_, 'YYYYMMDD'));
|
|
q_name_ := to_char(dt_, 'YYYY"Q"Q');
|
|
|
|
OPEN get_data;
|
|
FETCH get_data BULK COLLECT INTO v_cabang, v_coa, v_currency, v_base, v_original;
|
|
CLOSE get_data;
|
|
|
|
FOR i IN v_cabang.FIRST..v_cabang.LAST LOOP
|
|
OPEN check_hist(v_cabang(i), v_coa(i), v_currency(i));
|
|
FETCH check_hist INTO base_, orig_, hist_id_;
|
|
IF check_hist%FOUND THEN
|
|
CLOSE check_hist;
|
|
base_ := base_ || to_char(dt_, 'YYYYMMDD') || f_ || to_char(v_base(i)) || r_;
|
|
orig_ := orig_ || to_char(dt_, 'YYYYMMDD') || f_ || to_char(v_original(i)) || r_;
|
|
sql_ := 'UPDATE gl_saldo_tab
|
|
SET base_saldo = :base, original_saldo = :orig
|
|
WHERE kode_cabang = :v1
|
|
AND kode_coa = :v2
|
|
AND kode_currency = :v3
|
|
AND q_period = :v4';
|
|
--dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_ USING base_, orig_, v_cabang(i), v_coa(i), v_currency(i), q_name_;
|
|
ELSE
|
|
CLOSE check_hist;
|
|
base_ := to_char(dt_, 'YYYYMMDD') || f_ || to_char(v_base(i)) || r_;
|
|
orig_ := to_char(dt_, 'YYYYMMDD') || f_ || to_char(v_original(i)) || r_;
|
|
sql_ := 'INSERT INTO gl_saldo_tab VALUES (:v1, :v2, :v3, :v4, :v5, :v6)';
|
|
--dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_ USING v_cabang(i), v_coa(i), v_currency(i), q_name_, base_, orig_;
|
|
END IF;
|
|
END LOOP;
|
|
COMMIT;
|
|
dt_ := dt_ + 1;
|
|
END LOOP;
|
|
END Build_Gl_Saldo;
|
|
|
|
|
|
PROCEDURE Build_Treasury_Transaction (
|
|
date_ IN DATE )
|
|
IS
|
|
start_ NUMBER;
|
|
dt_ DATE;
|
|
|
|
tran_code_ VARCHAR2(4);
|
|
tran_idr_ VARCHAR2(4);
|
|
type_ VARCHAR2(1);
|
|
bn_flag_ VARCHAR2(1);
|
|
|
|
amt_idr_ NUMBER;
|
|
kurs_ NUMBER;
|
|
|
|
CURSOR get_trans IS
|
|
SELECT *
|
|
FROM trsry_tran_code_tab t
|
|
ORDER BY t.trans_code;
|
|
|
|
CURSOR get_bn IS
|
|
SELECT t.podtpo, t.porefn, t.potrco,
|
|
t.podesc, t.poamnt, t.pocyco,
|
|
t.pobrco, t.pouser, b.brname
|
|
FROM h8po_staging t, a2br_staging b
|
|
WHERE t.pobrco LIKE '%'
|
|
AND t.pobrco = b.brcode
|
|
AND t.potrco = tran_code_
|
|
AND t.podtpo = start_
|
|
AND t.pocyco != 'IDR'
|
|
ORDER BY t.pobrco, t.potrco, t.podtpo, t.porefn;
|
|
|
|
CURSOR get_idr (brco_ IN VARCHAR2, refn_ IN VARCHAR2, dtvl_ IN NUMBER) IS
|
|
SELECT t.poamnt
|
|
FROM h8po_staging t
|
|
WHERE t.pobrco = brco_
|
|
AND t.potrco = tran_idr_
|
|
AND t.podtpo = dtvl_
|
|
AND t.porefn = refn_
|
|
AND t.pocyco = 'IDR';
|
|
|
|
TYPE dtpo_ IS TABLE OF NUMBER;
|
|
TYPE refn_ IS TABLE OF VARCHAR2(15);
|
|
TYPE trco_ IS TABLE OF VARCHAR2(4);
|
|
TYPE desc_ IS TABLE OF VARCHAR2(30);
|
|
TYPE amnt_ IS TABLE OF NUMBER;
|
|
TYPE cocy_ IS TABLE OF VARCHAR2(3);
|
|
TYPE brco_ IS TABLE OF VARCHAR2(3);
|
|
TYPE user_ IS TABLE OF VARCHAR2(10);
|
|
TYPE brnm_ IS TABLE OF VARCHAR2(30);
|
|
|
|
v_dtpo_ dtpo_;
|
|
v_refn_ refn_;
|
|
v_trco_ trco_;
|
|
v_desc_ desc_;
|
|
v_amnt_ amnt_;
|
|
v_cocy_ cocy_;
|
|
v_brco_ brco_;
|
|
v_user_ user_;
|
|
v_brnm_ brnm_;
|
|
BEGIN
|
|
start_ := to_number(to_char(date_, 'YYYYMMDD'));
|
|
|
|
FOR c_ IN get_trans LOOP
|
|
tran_code_ := c_.trans_code;
|
|
tran_idr_ := c_.trans_idr_code;
|
|
type_ := c_.trans_type;
|
|
IF c_.bn_flag = 'B' THEN
|
|
bn_flag_ := 'Y';
|
|
ELSE
|
|
bn_flag_ := 'N';
|
|
END IF;
|
|
|
|
OPEN get_bn;
|
|
FETCH get_bn BULK COLLECT INTO v_dtpo_, v_refn_, v_trco_, v_desc_, v_amnt_,
|
|
v_cocy_, v_brco_, v_user_, v_brnm_;
|
|
CLOSE get_bn;
|
|
|
|
IF (v_dtpo_.COUNT > 0) THEN
|
|
FOR i IN v_dtpo_.FIRST..v_dtpo_.LAST LOOP
|
|
OPEN get_idr(v_brco_(i), v_refn_(i), v_dtpo_(i));
|
|
FETCH get_idr INTO amt_idr_;
|
|
IF (get_idr%FOUND) THEN
|
|
CLOSE get_idr;
|
|
|
|
kurs_ := round(ABS(amt_idr_) / ABS(v_amnt_(i)), 2);
|
|
dt_ := to_date(to_char(v_dtpo_(i)), 'YYYYMMDD');
|
|
|
|
INSERT INTO fx_staging (
|
|
deal_no, trans_date, due_date, trans_type,
|
|
counterparty, user_confirm, banknote_flag,
|
|
buy_amount, buy_rate, idr_buy,
|
|
sell_amount, sell_rate, idr_sell,
|
|
currency_code, trans_source, counterparty_code, trans_code )
|
|
VALUES (
|
|
v_refn_(i), dt_, dt_, 'TOD',
|
|
v_brnm_(i), v_user_(i), bn_flag_,
|
|
DECODE(type_, 'B', ABS(v_amnt_(i)), 0), DECODE(type_, 'B', kurs_, 0), DECODE(type_, 'B', ABS(amt_idr_), 0),
|
|
DECODE(type_, 'S', ABS(v_amnt_(i)), 0), DECODE(type_, 'S', kurs_, 0), DECODE(type_, 'S', ABS(amt_idr_), 0),
|
|
v_cocy_(i), 'C', v_brco_(i), tran_code_
|
|
);
|
|
ELSE
|
|
CLOSE get_idr;
|
|
END IF;
|
|
END LOOP;
|
|
COMMIT;
|
|
END IF;
|
|
END LOOP;
|
|
END Build_Treasury_Transaction;
|
|
|
|
|
|
PROCEDURE Update_Status (
|
|
type_ IN VARCHAR2 )
|
|
IS
|
|
sql_del_ VARCHAR2(2000);
|
|
sql_ins_ VARCHAR2(2000);
|
|
BEGIN
|
|
IF type_ = '01' THEN -- DPK-CIF
|
|
sql_del_ := 'DELETE FROM a0mo_staging WHERE mocode != ''GL''';
|
|
sql_ins_ := 'INSERT INTO a0mo_staging SELECT * FROM a0mo_ext WHERE mocode != ''GL''';
|
|
ELSE
|
|
sql_del_ := 'DELETE FROM a0mo_staging WHERE mocode = ''GL''';
|
|
sql_ins_ := 'INSERT INTO a0mo_staging SELECT * FROM a0mo_ext WHERE mocode = ''GL''';
|
|
END IF;
|
|
EXECUTE IMMEDIATE sql_del_;
|
|
EXECUTE IMMEDIATE sql_ins_;
|
|
END Update_Status;
|
|
|
|
|
|
-- Base Method
|
|
|
|
PROCEDURE Process_Ext_Staging (
|
|
date_ IN VARCHAR2,
|
|
type_ IN VARCHAR2 )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
seq_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
job_ NUMBER;
|
|
dt_ DATE;
|
|
|
|
BEGIN
|
|
user_ := 'SYSTEM';
|
|
job_ := sys_job.Get_Job(type_);
|
|
dt_ := to_date(date_, 'YYYYMMDD');
|
|
|
|
seq_ := Get_Seq_Process(job_);
|
|
dbms_output.put_line('job_ ' || job_);
|
|
dbms_output.put_line('seq_ ' || seq_);
|
|
|
|
Ext_Staging(dt_, user_, seq_, type_, job_);
|
|
IF type_ = '01' THEN -- AB2
|
|
Build_History_Saldo(dt_);
|
|
Build_Treasury_Transaction(dt_);
|
|
UPDATE prm_system SET paramvalue = date_ WHERE paramid = 'LAST_HIST_SALDO';
|
|
END IF;
|
|
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_FINISHED', SYSDATE, attr_);
|
|
sys_job.Log_Transaction(attr_, 'U');
|
|
END Process_Ext_Staging;
|
|
|
|
|
|
PROCEDURE Import_Kredit (
|
|
date_ IN VARCHAR2,
|
|
type_ IN VARCHAR2 )
|
|
IS
|
|
job_ NUMBER;
|
|
attr_ VARCHAR2(2000);
|
|
dt_ DATE;
|
|
tgl_ DATE;
|
|
kredit_tab_ VARCHAR2(20);
|
|
agunan_tab_ VARCHAR2(20);
|
|
|
|
sql_ VARCHAR2(32000);
|
|
cols_ VARCHAR2(32000);
|
|
num_ NUMBER;
|
|
|
|
CURSOR get_tab(tab_ IN VARCHAR2) IS
|
|
SELECT t.COLUMN_NAME
|
|
FROM user_tab_columns t
|
|
WHERE t.TABLE_NAME = tab_
|
|
ORDER BY t.COLUMN_ID;
|
|
BEGIN
|
|
job_ := sys_job.Get_Job(type_);
|
|
dt_ := to_date(date_, 'YYYYMMDD');
|
|
tgl_ := add_months(dt_, -1);
|
|
kredit_tab_ := 't_kredit' || to_char(tgl_, 'MM');
|
|
dbms_output.put_line(kredit_tab_);
|
|
|
|
num_ := 0;
|
|
sql_ := 'SELECT COUNT(1) FROM kredit_tab';
|
|
sql_ := sql_ || ' WHERE krdt_bln = ''' || to_char(tgl_, 'MM') || ''' AND krdt_thn = ''' || to_char(tgl_, 'YYYY') || '''';
|
|
dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_ INTO num_;
|
|
dbms_output.put_line(num_);
|
|
|
|
IF num_ > 0 THEN
|
|
sql_ := 'DELETE FROM kredit_tab';
|
|
sql_ := sql_ || ' WHERE krdt_bln = ''' || to_char(tgl_, 'MM') || ''' AND krdt_thn = ''' || to_char(tgl_, 'YYYY') || '''';
|
|
dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_;
|
|
COMMIT;
|
|
END IF;
|
|
|
|
cols_ := NULL;
|
|
FOR c_ IN get_tab('KREDIT_TAB') LOOP
|
|
cols_ := cols_ || '"' || lower(c_.column_name) || '",';
|
|
END LOOP;
|
|
cols_ := substr(cols_, 1, length(cols_)-1);
|
|
|
|
sql_ := 'INSERT INTO kredit_tab SELECT ';
|
|
sql_ := sql_ || cols_;
|
|
sql_ := sql_ || ' FROM "' || kredit_tab_ || '"@ddkk';
|
|
sql_ := sql_ || ' WHERE "krdt_bln" = ''' || to_char(tgl_, 'MM');
|
|
sql_ := sql_ || ''' AND "krdt_thn" = ''' || to_char(tgl_, 'YYYY') || '''';
|
|
dbms_output.put_line('kredit_tab ' || sql_);
|
|
EXECUTE IMMEDIATE sql_;
|
|
COMMIT;
|
|
|
|
-- END Kredit, START agunan
|
|
|
|
agunan_tab_ := 't_agunan' || to_char(tgl_, 'MM');
|
|
dbms_output.put_line(agunan_tab_);
|
|
|
|
num_ := 0;
|
|
sql_ := 'SELECT COUNT(1) FROM agunan_tab';
|
|
sql_ := sql_ || ' WHERE agnn_bln = ''' || to_char(tgl_, 'MM') || ''' AND agnn_thn = ''' || to_char(tgl_, 'YYYY') || '''';
|
|
dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_ INTO num_;
|
|
dbms_output.put_line(num_);
|
|
|
|
IF num_ > 0 THEN
|
|
sql_ := 'DELETE FROM agunan_tab';
|
|
sql_ := sql_ || ' WHERE agnn_bln = ''' || to_char(tgl_, 'MM') || ''' AND agnn_thn = ''' || to_char(tgl_, 'YYYY') || '''';
|
|
dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_;
|
|
COMMIT;
|
|
END IF;
|
|
|
|
cols_ := NULL;
|
|
FOR c_ IN get_tab('AGUNAN_TAB') LOOP
|
|
cols_ := cols_ || '"' || lower(c_.column_name) || '",';
|
|
END LOOP;
|
|
cols_ := substr(cols_, 1, length(cols_)-1);
|
|
|
|
sql_ := 'INSERT INTO agunan_tab SELECT ';
|
|
sql_ := sql_ || cols_;
|
|
sql_ := sql_ || ' FROM "' || agunan_tab_ || '"@ddkk';
|
|
sql_ := sql_ || ' WHERE "agnn_bln" = ''' || to_char(tgl_, 'MM');
|
|
sql_ := sql_ || ''' AND "agnn_thn" = ''' || to_char(tgl_, 'YYYY') || '''';
|
|
dbms_output.put_line('agunan_tab ' || sql_);
|
|
EXECUTE IMMEDIATE sql_;
|
|
COMMIT;
|
|
|
|
-- END Agunan, START Currency Rate
|
|
|
|
cols_ := NULL;
|
|
FOR c_ IN get_tab('KREDIT_CURR_RATE_TAB') LOOP
|
|
cols_ := cols_ || '"' || lower(c_.column_name) || '",';
|
|
END LOOP;
|
|
cols_ := substr(cols_, 1, length(cols_)-1);
|
|
|
|
sql_ := 'INSERT INTO kredit_curr_rate_tab SELECT ';
|
|
sql_ := sql_ || cols_;
|
|
sql_ := sql_ || ' FROM "currency_rate"@ddkk';
|
|
sql_ := sql_ || ' WHERE "curr_blnthn" = ''' || to_char(tgl_, 'MMYYYY') || '''';
|
|
dbms_output.put_line('kredit_curr_rate_tab ' || sql_);
|
|
EXECUTE IMMEDIATE sql_;
|
|
COMMIT;
|
|
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_FINISHED', SYSDATE, attr_);
|
|
sys_job.Log_Transaction(attr_, 'U');
|
|
END Import_Kredit;
|
|
|
|
|
|
PROCEDURE Purging_Dwh (
|
|
date_ IN VARCHAR2,
|
|
type_ IN VARCHAR2 )
|
|
IS
|
|
job_ NUMBER;
|
|
attr_ VARCHAR2(2000);
|
|
dt_ DATE;
|
|
limit_ NUMBER;
|
|
stg_tab_ VARCHAR2(30);
|
|
sql_ VARCHAR2(4000);
|
|
dt_limit_ DATE;
|
|
n_dt_ NUMBER;
|
|
where_ NUMBER;
|
|
ret_ NUMBER;
|
|
|
|
chk_tab_ NUMBER;
|
|
|
|
CURSOR get_tab IS
|
|
SELECT tab_name, purging_field, purging_data_type, purging_filter
|
|
FROM external_data_tab
|
|
WHERE purging_type = 'Y'
|
|
ORDER BY modul, tab_name;
|
|
|
|
CURSOR check_tab IS
|
|
SELECT COUNT(1)
|
|
FROM user_tables
|
|
WHERE table_name = stg_tab_;
|
|
|
|
TYPE tab_ IS TABLE OF VARCHAR2(30);
|
|
TYPE field_ IS TABLE OF VARCHAR2(30);
|
|
TYPE dt_type_ IS TABLE OF VARCHAR2(1);
|
|
TYPE filter_ IS TABLE OF VARCHAR2(2000);
|
|
v_tab_ tab_;
|
|
v_field_ field_;
|
|
v_type_ dt_type_;
|
|
v_filter_ filter_;
|
|
|
|
BEGIN
|
|
job_ := sys_job.Get_Job(type_);
|
|
dt_ := to_date(date_, 'YYYYMMDD');
|
|
EXECUTE IMMEDIATE 'SELECT paramvalue FROM prm_system WHERE paramid = ''KEEP_STAGING''' INTO limit_;
|
|
limit_ := to_number(limit_) * 12;
|
|
dt_limit_ := add_months(dt_, -limit_);
|
|
n_dt_ := to_number(to_char(dt_limit_, 'YYYYMMDD'));
|
|
|
|
dbms_output.put_line(date_ || ' - ' || limit_ || ' - ' || n_dt_);
|
|
|
|
OPEN get_tab;
|
|
FETCH get_tab BULK COLLECT INTO v_tab_, v_field_, v_type_, v_filter_;
|
|
CLOSE get_tab;
|
|
|
|
IF (v_tab_.COUNT > 0) THEN
|
|
FOR i IN v_tab_.FIRST..v_tab_.LAST LOOP
|
|
where_ := 0;
|
|
stg_tab_ := v_tab_(i) || '_STAGING';
|
|
|
|
OPEN check_tab;
|
|
FETCH check_tab INTO chk_tab_;
|
|
CLOSE check_tab;
|
|
chk_tab_ := NVL(chk_tab_, 0);
|
|
|
|
IF (chk_tab_ > 0) THEN
|
|
sql_ := 'DELETE FROM ' || stg_tab_;
|
|
IF (length(v_field_(i)) > 0) THEN
|
|
IF (v_type_(i) = 'N') THEN
|
|
sql_ := sql_ || ' WHERE ' || v_field_(i) || ' <= ' || n_dt_;
|
|
ELSE
|
|
sql_ := sql_ || ' WHERE ' || v_field_(i) || ' <= to_date(''' || n_dt_ || ''', ''YYYYMMDD'')';
|
|
END IF;
|
|
where_ := 1;
|
|
END IF;
|
|
IF (length(v_filter_(i)) > 0) THEN
|
|
IF (where_ = 1) THEN
|
|
sql_ := sql_ || ' AND ' || v_filter_(i);
|
|
ELSE
|
|
sql_ := sql_ || ' WHERE ' || v_filter_(i);
|
|
END IF;
|
|
END IF;
|
|
sql_ := sql_ || ' RETURNING COUNT(1) INTO :ret';
|
|
dbms_output.put_line(stg_tab_ || ' - ' || sql_);
|
|
EXECUTE IMMEDIATE sql_ USING OUT ret_;
|
|
dbms_output.put_line(ret_);
|
|
END IF;
|
|
END LOOP;
|
|
END IF;
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_FINISHED', SYSDATE, attr_);
|
|
sys_job.Log_Transaction(attr_, 'U');
|
|
END Purging_Dwh;
|
|
|
|
|
|
|
|
PROCEDURE Staging_Lbu (
|
|
date_ IN VARCHAR2,
|
|
type_ IN VARCHAR2,
|
|
modul_ IN VARCHAR2 )
|
|
IS
|
|
job_ NUMBER;
|
|
attr_ VARCHAR2(2000);
|
|
sql_ VARCHAR2(2000);
|
|
start_ NUMBER;
|
|
end_ NUMBER;
|
|
dt_ DATE;
|
|
out_ NUMBER;
|
|
|
|
CURSOR get_table IS
|
|
SELECT *
|
|
FROM lbu_tab
|
|
WHERE status_table = '1'
|
|
AND modul = modul_
|
|
ORDER BY table_name;
|
|
BEGIN
|
|
job_ := sys_job.Get_Job(type_);
|
|
dt_ := TRUNC(to_date(date_,'YYYYMMDD'), 'MM')-1;
|
|
start_ := to_number(to_char(trunc(dt_,'MM'),'YYYYMMDD'));
|
|
end_ := to_number(to_char(dt_,'YYYYMMDD'));
|
|
|
|
dbms_output.put_line(dt_);
|
|
dbms_output.put_line(start_);
|
|
dbms_output.put_line(end_);
|
|
|
|
FOR c_ IN get_table LOOP
|
|
sql_ := 'INSERT INTO ' || c_.table_name || '@LBU';
|
|
sql_ := sql_ || ' SELECT * FROM ' || c_.table_name;
|
|
IF (nvl(c_.filter_1, 'XXX') != 'XXX') THEN
|
|
sql_ := sql_ || ' WHERE ' || c_.filter_1;
|
|
END IF;
|
|
IF (nvl(c_.filter_2, 'XXX') != 'XXX') THEN
|
|
sql_ := sql_ || ' AND ' || c_.filter_2 || ' BETWEEN ' || start_ || ' AND ' || end_;
|
|
END IF;
|
|
IF (c_.table_name = 'D5DO_STAGING') THEN
|
|
sql_ := sql_ || ' UNION SELECT * FROM D5DO_STAGING WHERE DOSTAT=''0'' AND DODTCL > ' || end_;
|
|
END IF;
|
|
dbms_output.put_line(sql_);
|
|
EXECUTE IMMEDIATE sql_;
|
|
COMMIT;
|
|
END LOOP;
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_FINISHED', SYSDATE, attr_);
|
|
sys_job.Log_Transaction(attr_, 'U');
|
|
END Staging_Lbu;
|
|
|
|
|
|
PROCEDURE Remove_Job (
|
|
job_id_ IN NUMBER )
|
|
IS
|
|
chk_job_ NUMBER;
|
|
attr_ VARCHAR2(2000);
|
|
no_job EXCEPTION;
|
|
CURSOR get_job IS
|
|
SELECT 1
|
|
FROM user_jobs b
|
|
WHERE b.job = job_id_;
|
|
BEGIN
|
|
OPEN get_job;
|
|
FETCH get_job INTO chk_job_;
|
|
IF (get_job%FOUND) THEN
|
|
CLOSE get_job;
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_id_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'D');
|
|
dbms_job.remove(job_id_);
|
|
ELSE
|
|
CLOSE get_job;
|
|
RAISE no_job;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN no_job THEN
|
|
raise_application_error(-20011, 'Job ID ' || job_id_ || ' tidak ada');
|
|
END Remove_Job;
|
|
|
|
|
|
-- Post Process to DBMS_JOB
|
|
|
|
|
|
PROCEDURE Post_Ab2_Transaction (
|
|
in_attr_ IN VARCHAR2,
|
|
ret_ OUT NUMBER )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
date_ DATE;
|
|
ipnum_ VARCHAR2(30);
|
|
|
|
job_ NUMBER;
|
|
proses_ NUMBER;
|
|
date_ext_ NUMBER;
|
|
date_stg_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
|
|
chk_job_ NUMBER;
|
|
job_exists EXCEPTION;
|
|
no_update EXCEPTION;
|
|
what_ VARCHAR2(32000);
|
|
type_ VARCHAR2(2) := '01';
|
|
|
|
CURSOR get_date_stg IS
|
|
SELECT a.modtjb
|
|
FROM a0mo_staging a
|
|
WHERE a.mocode = 'RT';
|
|
|
|
CURSOR get_date_ext IS
|
|
SELECT a.modtjb
|
|
FROM a0mo_ext a
|
|
WHERE a.mocode = 'RT';
|
|
BEGIN
|
|
proses_ := 0;
|
|
OPEN get_date_stg;
|
|
FETCH get_date_stg INTO date_stg_;
|
|
CLOSE get_date_stg;
|
|
|
|
OPEN get_date_ext;
|
|
FETCH get_date_ext INTO date_ext_;
|
|
CLOSE get_date_ext;
|
|
|
|
IF date_stg_ >= date_ext_ THEN
|
|
proses_ := 1;
|
|
END IF;
|
|
|
|
chk_job_ := sys_job.Check_Job(type_);
|
|
IF chk_job_ > 0 THEN
|
|
ret_ := 0;
|
|
RAISE job_exists;
|
|
END IF;
|
|
|
|
IF (proses_ + chk_job_) = 0 THEN
|
|
user_ := sys_attr.Get_Item_Value('USER_ID', in_attr_);
|
|
date_ := to_date(sys_attr.Get_Item_Value('TGL_PROSES',in_attr_), 'DD-MM-YYYY');
|
|
ipnum_ := sys_attr.Get_Item_Value('IPNUM', in_attr_);
|
|
|
|
what_ := 'MAP_DWH.Process_Ext_Staging('''||to_char(date_,'YYYYMMDD')||''', '''||type_||''');';
|
|
dbms_output.put_line(what_);
|
|
dbms_job.submit(job_, what_, SYSDATE);
|
|
dbms_output.put_line(job_);
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('PROCESS_ID', type_, attr_);
|
|
sys_attr.Add_To_Attr('USER_POSTING', user_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_POSTING', SYSDATE, attr_);
|
|
sys_attr.Add_To_Attr('IPNUM', ipnum_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'I');
|
|
COMMIT;
|
|
ret_ := job_;
|
|
ELSE
|
|
ret_ := 0;
|
|
RAISE no_update;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN job_exists THEN
|
|
raise_application_error(-20011, 'Posting AB2 sedang berjalan');
|
|
WHEN no_update THEN
|
|
raise_application_error(-20012, 'Tidak ada data baru dari AB2');
|
|
END Post_Ab2_Transaction;
|
|
|
|
|
|
PROCEDURE Post_Gl_Transaction (
|
|
in_attr_ IN VARCHAR2,
|
|
ret_ OUT NUMBER )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
date_ DATE;
|
|
ipnum_ VARCHAR2(30);
|
|
|
|
job_ NUMBER;
|
|
proses_ NUMBER;
|
|
date_ext_ NUMBER;
|
|
date_stg_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
|
|
chk_job_ NUMBER;
|
|
job_exists EXCEPTION;
|
|
no_update EXCEPTION;
|
|
what_ VARCHAR2(32000);
|
|
type_ VARCHAR2(2) := '04';
|
|
|
|
CURSOR get_date_stg IS
|
|
SELECT a.modtjb
|
|
FROM a0mo_staging a
|
|
WHERE a.mocode = 'GL';
|
|
|
|
CURSOR get_date_ext IS
|
|
SELECT a.modtjb
|
|
FROM a0mo_ext a
|
|
WHERE a.mocode = 'GL';
|
|
BEGIN
|
|
proses_ := 0;
|
|
OPEN get_date_stg;
|
|
FETCH get_date_stg INTO date_stg_;
|
|
CLOSE get_date_stg;
|
|
|
|
OPEN get_date_ext;
|
|
FETCH get_date_ext INTO date_ext_;
|
|
CLOSE get_date_ext;
|
|
|
|
IF date_stg_ >= date_ext_ THEN
|
|
proses_ := 1; --
|
|
END IF;
|
|
|
|
chk_job_ := sys_job.Check_Job(type_);
|
|
IF chk_job_ > 0 THEN
|
|
ret_ := 0;
|
|
RAISE job_exists;
|
|
END IF;
|
|
|
|
IF (proses_ + chk_job_) = 0 THEN
|
|
user_ := sys_attr.Get_Item_Value('USER_ID', in_attr_);
|
|
date_ := to_date(sys_attr.Get_Item_Value('TGL_PROSES',in_attr_), 'DD-MM-YYYY');
|
|
ipnum_ := sys_attr.Get_Item_Value('IPNUM', in_attr_);
|
|
|
|
what_ := 'MAP_DWH.Process_Ext_Staging('''||to_char(date_,'YYYYMMDD')||''', '''||type_||''');';
|
|
dbms_output.put_line(what_);
|
|
dbms_job.submit(job_, what_, SYSDATE);
|
|
dbms_output.put_line(job_);
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('PROCESS_ID', type_, attr_);
|
|
sys_attr.Add_To_Attr('USER_POSTING', user_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_POSTING', SYSDATE, attr_);
|
|
sys_attr.Add_To_Attr('IPNUM', ipnum_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'I');
|
|
COMMIT;
|
|
ret_ := job_;
|
|
ELSE
|
|
ret_ := 0;
|
|
RAISE no_update;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN job_exists THEN
|
|
raise_application_error(-20011, 'Posting AB2 sedang berjalan');
|
|
WHEN no_update THEN
|
|
raise_application_error(-20012, 'Tidak ada data baru dari AB2');
|
|
END Post_Gl_Transaction;
|
|
|
|
|
|
PROCEDURE Post_Kredit (
|
|
in_attr_ IN VARCHAR2,
|
|
ret_ OUT NUMBER )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
date_ DATE;
|
|
ipnum_ VARCHAR2(30);
|
|
|
|
job_ NUMBER;
|
|
code_ VARCHAR2(2);
|
|
proses_ NUMBER;
|
|
date_ext_ NUMBER;
|
|
date_stg_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
|
|
chk_job_ NUMBER;
|
|
job_exists EXCEPTION;
|
|
what_ VARCHAR2(32000);
|
|
type_ VARCHAR2(2) := '05';
|
|
|
|
BEGIN
|
|
proses_ := 0;
|
|
chk_job_ := sys_job.Check_Job(type_);
|
|
IF chk_job_ > 0 THEN
|
|
ret_ := 0;
|
|
RAISE job_exists;
|
|
END IF;
|
|
|
|
IF (proses_) = 0 THEN
|
|
user_ := sys_attr.Get_Item_Value('USER_ID', in_attr_);
|
|
date_ := to_date(sys_attr.Get_Item_Value('TGL_PROSES',in_attr_), 'DD-MM-YYYY');
|
|
ipnum_ := sys_attr.Get_Item_Value('IPNUM', in_attr_);
|
|
|
|
what_ := 'MAP_DWH.Import_Kredit('''||to_char(date_,'YYYYMMDD')||''', '''||type_||''');';
|
|
dbms_output.put_line(what_);
|
|
dbms_job.submit(job_, what_, SYSDATE);
|
|
dbms_output.put_line(job_);
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('PROCESS_ID', type_, attr_);
|
|
sys_attr.Add_To_Attr('USER_POSTING', user_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_POSTING', SYSDATE, attr_);
|
|
sys_attr.Add_To_Attr('IPNUM', ipnum_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'I');
|
|
COMMIT;
|
|
ret_ := job_;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN job_exists THEN
|
|
raise_application_error(-20011, 'Posting Kredit sedang berjalan');
|
|
END Post_Kredit;
|
|
|
|
|
|
PROCEDURE Post_Treasury_Transaction (
|
|
in_attr_ IN VARCHAR2,
|
|
ret_ OUT NUMBER )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
date_ DATE;
|
|
ipnum_ VARCHAR2(30);
|
|
|
|
job_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
|
|
chk_job_ NUMBER;
|
|
job_exists EXCEPTION;
|
|
what_ VARCHAR2(32000);
|
|
type_ VARCHAR2(2) := '02';
|
|
BEGIN
|
|
chk_job_ := sys_job.Check_Job(type_);
|
|
IF chk_job_ = 0 THEN
|
|
user_ := sys_attr.Get_Item_Value('USER_ID', in_attr_);
|
|
date_ := to_date(sys_attr.Get_Item_Value('TGL_PROSES',in_attr_), 'DD-MM-YYYY');
|
|
ipnum_ := sys_attr.Get_Item_Value('IPNUM', in_attr_);
|
|
|
|
what_ := 'MAP_DWH.Process_Ext_Staging('''||to_char(date_,'YYYYMMDD')||''', '''||type_||''');';
|
|
dbms_output.put_line(what_);
|
|
dbms_job.submit(job_, what_, SYSDATE);
|
|
dbms_output.put_line(job_);
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('PROCESS_ID', type_, attr_);
|
|
sys_attr.Add_To_Attr('USER_POSTING', user_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_POSTING', SYSDATE, attr_);
|
|
sys_attr.Add_To_Attr('IPNUM', ipnum_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'I');
|
|
COMMIT;
|
|
ret_ := job_;
|
|
ELSE
|
|
ret_ := 0;
|
|
RAISE job_exists;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN job_exists THEN
|
|
raise_application_error(-20011, 'Posting Treasury sedang berjalan');
|
|
END Post_Treasury_Transaction;
|
|
|
|
|
|
PROCEDURE Post_Purging_Dwh (
|
|
in_attr_ IN VARCHAR2,
|
|
ret_ OUT NUMBER )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
date_ DATE;
|
|
ipnum_ VARCHAR2(30);
|
|
job_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
chk_job_ NUMBER;
|
|
job_exists EXCEPTION;
|
|
what_ VARCHAR2(32000);
|
|
type_ VARCHAR2(2) := '03';
|
|
BEGIN
|
|
chk_job_ := sys_job.Check_Job(type_);
|
|
IF chk_job_ = 0 THEN
|
|
user_ := sys_attr.Get_Item_Value('USER_ID', in_attr_);
|
|
date_ := to_date(sys_attr.Get_Item_Value('TGL_PROSES',in_attr_), 'DD-MM-YYYY');
|
|
ipnum_ := sys_attr.Get_Item_Value('IPNUM', in_attr_);
|
|
|
|
what_ := 'MAP_DWH.Purging_Dwh('''||to_char(date_,'YYYYMMDD')||''', '''||type_||''');';
|
|
dbms_output.put_line(what_);
|
|
dbms_job.submit(job_, what_, SYSDATE);
|
|
dbms_output.put_line(job_);
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('PROCESS_ID', type_, attr_);
|
|
sys_attr.Add_To_Attr('USER_POSTING', user_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_POSTING', SYSDATE, attr_);
|
|
sys_attr.Add_To_Attr('IPNUM', ipnum_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'I');
|
|
COMMIT;
|
|
ret_ := job_;
|
|
ELSE
|
|
ret_ := 0;
|
|
RAISE job_exists;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN job_exists THEN
|
|
raise_application_error(-20011, 'Purging Data Warehouse sedang berjalan');
|
|
END Post_Purging_Dwh;
|
|
|
|
|
|
PROCEDURE Post_Staging_Lbu (
|
|
in_attr_ IN VARCHAR2,
|
|
ret_ OUT NUMBER )
|
|
IS
|
|
user_ VARCHAR2(10);
|
|
date_ DATE;
|
|
ipnum_ VARCHAR2(30);
|
|
job_ NUMBER;
|
|
attr_ VARCHAR2(32000);
|
|
chk_job_ NUMBER;
|
|
job_exists EXCEPTION;
|
|
what_ VARCHAR2(32000);
|
|
type_ VARCHAR2(2) := '06';
|
|
modul_ VARCHAR2(5);
|
|
BEGIN
|
|
chk_job_ := sys_job.Check_Job(type_);
|
|
IF chk_job_ = 0 THEN
|
|
user_ := sys_attr.Get_Item_Value('USER_ID', in_attr_);
|
|
date_ := to_date(sys_attr.Get_Item_Value('TGL_PROSES',in_attr_), 'DD-MM-YYYY');
|
|
ipnum_ := sys_attr.Get_Item_Value('IPNUM', in_attr_);
|
|
modul_ := sys_attr.Get_Item_Value('MODUL', in_attr_);
|
|
|
|
what_ := 'MAP_DWH.Staging_Lbu('''||to_char(date_,'YYYYMMDD')||''', '''||type_||''', '''||modul_||''');';
|
|
dbms_output.put_line(what_);
|
|
dbms_job.submit(job_, what_, SYSDATE);
|
|
dbms_output.put_line(job_);
|
|
sys_attr.Clear_Attr(attr_);
|
|
sys_attr.Add_To_Attr('JOB_ID', job_, attr_);
|
|
sys_attr.Add_To_Attr('PROCESS_ID', type_, attr_);
|
|
sys_attr.Add_To_Attr('USER_POSTING', user_, attr_);
|
|
sys_attr.Add_To_Attr('DATE_POSTING', SYSDATE, attr_);
|
|
sys_attr.Add_To_Attr('IPNUM', ipnum_, attr_);
|
|
sys_job.Log_Transaction(attr_, 'I');
|
|
COMMIT;
|
|
ret_ := job_;
|
|
ELSE
|
|
ret_ := 0;
|
|
RAISE job_exists;
|
|
END IF;
|
|
EXCEPTION
|
|
WHEN job_exists THEN
|
|
raise_application_error(-20011, 'Posting Data Staging to LBU Basel II sedang berjalan');
|
|
END Post_Staging_Lbu;
|
|
|
|
|
|
END &PKG;
|
|
/
|
|
SHOW ERROR
|