(
SP_Date IN NVARCHAR2,
UserId IN NUMBER,
branchcode IN NVARCHAR2
)
IS
DTSaldoAwal DATE;
NeracaDate DATE;
OldDate DATE;
ID_COA1 NUMBER;
ID_COA2 NUMBER;
SaldoAwal NUMBER;
Var_LabaRugiBerjalan NUMBER; --2011-05-28--
Var_LabaRugiDitahan NUMBER; --2011-05-28--
BEGIN
NeracaDate := TO_DATE(SP_Date,'DD/MM/YYYY');
-- 1. CLEAR OLD DATA --
DELETE temporary.FNC_PL_BS WHERE USR_ID = UserId;
-- 2. GET ALL NERACA COA --
INSERT INTO temporary.FNC_PL_BS(USR_ID, LVL, ID_COA, NAMA_COA, TOP_ID_COA, HDR_DTL, SALDO, LAST_SALDO)
SELECT UserId, LEVEL L, A.ID_COA, LPAD(' ',4*(LEVEL-1)) || A.CD_COA ||' - '|| A.NAMA_COA, A.TOP_ID_COA, A.HDR_DTL, 0, 0
FROM
( SELECT ID_COA, CD_COA, NAMA_COA, TOP_ID_COA, HDR_DTL
FROM DT_COA START WITH TOP_ID_COA IS NULL AND BS_PL='BS' CONNECT BY PRIOR ID_COA = TOP_ID_COA
) A
START WITH A.TOP_ID_COA IS NULL CONNECT BY PRIOR A.ID_COA = A.TOP_ID_COA;
-- 3. GET ALL JURNAL di bulan NERACA --
IF branchcode <> '0' THEN
UPDATE temporary.FNC_PL_BS SET SALDO = NVL(
(SELECT SALDO
FROM
( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) SALDO
FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
WHERE A.TGL_FNC_GJ <= NeracaDate AND B.USR_ID = UserId AND A.ID_COA = B.ID_COA AND A.ID_COA = C.ID_COA AND
CD_FNC_GJ LIKE '%-'||branchcode||'%'
GROUP BY A.ID_COA
) A
WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
WHERE temporary.FNC_PL_BS.USR_ID = UserId;
ELSE
UPDATE temporary.FNC_PL_BS SET SALDO = NVL(
(SELECT SALDO
FROM
( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) SALDO
FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
WHERE A.TGL_FNC_GJ <= NeracaDate AND B.USR_ID = UserId AND A.ID_COA = B.ID_COA AND A.ID_COA = C.ID_COA
GROUP BY A.ID_COA
) A
WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
WHERE temporary.FNC_PL_BS.USR_ID = UserId;
END IF;
-- 4. GET ALL JURNAL di bulan sebelumnya --
OldDate := TO_DATE('01/'||TO_CHAR(NeracaDate,'MM/YYYY'),'DD/MM/YYYY')-1;
IF branchcode <> '0' THEN
UPDATE temporary.FNC_PL_BS SET LAST_SALDO = NVL(
(SELECT LAST_SALDO
FROM
( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) LAST_SALDO
FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
WHERE A.TGL_FNC_GJ <= OldDate AND B.USR_ID = UserId AND A.ID_COA = B.ID_COA AND A.ID_COA = C.ID_COA AND
CD_FNC_GJ LIKE '%-'||branchcode||'%'
GROUP BY A.ID_COA
) A
WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
WHERE temporary.FNC_PL_BS.USR_ID = UserId;
ELSE
UPDATE temporary.FNC_PL_BS SET LAST_SALDO = NVL(
(SELECT LAST_SALDO
FROM
( SELECT A.ID_COA, SUM( CASE WHEN C.DBT_CRDT='D' THEN NVL(A.DBT,0) - NVL(A.CRDT,0) ELSE NVL(A.CRDT,0) - NVL(A.DBT,0) END) LAST_SALDO
FROM FNC_GJ_DTL A, temporary.FNC_PL_BS B, DT_COA C
WHERE A.TGL_FNC_GJ <= OldDate AND B.USR_ID = UserId AND A.ID_COA = B.ID_COA AND A.ID_COA = C.ID_COA
GROUP BY A.ID_COA
) A
WHERE temporary.FNC_PL_BS.ID_COA = A.ID_COA),0)
WHERE temporary.FNC_PL_BS.USR_ID = UserId;
END IF;
SELECT TO_DATE(PREF_VAL,'DD-MM-YYYY') INTO DTSaldoAwal FROM SY_PREF WHERE PREF_KEY='A1A1';
SELECT PREF_VAL2 INTO ID_COA1 FROM SY_PREF WHERE PREF_KEY='COA_BS1';
SELECT PREF_VAL2 INTO ID_COA2 FROM SY_PREF WHERE PREF_KEY='COA_BS2';
/* -------------------- 1. RUGI/LABA bulan ini -------------------- */
Sp_Fnc_Rugilaba(TO_DATE('01/'||TO_CHAR(NeracaDate,'MM/YYYY'),'DD/MM/YYYY'), NeracaDate, UserId, branchcode);
-- 1.1 L/R BERJALAN ATAU L/R pada bulan tgl Neraca --
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN SALDO ELSE -SALDO END) INTO Var_LabaRugiBerjalan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);
UPDATE temporary.FNC_PL_BS SET SALDO = Var_LabaRugiBerjalan WHERE USR_ID = UserId AND ID_COA = ID_COA1;
-- 1.2 L/R DITAHAN ATAU AKUMULASI LABA-RUGI awal Tahun sampai tgl Neraca
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN LAST_SALDO ELSE -LAST_SALDO END) INTO Var_LabaRugiDitahan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);
UPDATE temporary.FNC_PL_BS SET SALDO = SALDO + Var_LabaRugiDitahan WHERE USR_ID = UserId AND ID_COA = ID_COA2;
-- 1.3 Pasiva ditambah LABA-RUGI Berjalan --2011-05-28--
UPDATE temporary.FNC_PL_BS SET SALDO = SALDO + Var_LabaRugiDitahan
WHERE USR_ID = UserId AND ID_COA = (SELECT ID_COA FROM DT_COA WHERE CD_COA='2000');
/* -------------------- 2. RUGI/LABA bulan sebelumnya -------------------- */
OldDate := TO_DATE('01/'||TO_CHAR(NeracaDate,'MM/YYYY'),'DD/MM/YYYY') - 1;
Sp_Fnc_Rugilaba(TO_DATE('01/'||TO_CHAR(OldDate,'MM/YYYY'),'DD/MM/YYYY'), OldDate, UserId, branchcode);
-- 2.1 L/R BERJALAN ATAU L/R pada bulan tgl Neraca --
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN SALDO ELSE -SALDO END) INTO Var_LabaRugiBerjalan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);
UPDATE temporary.FNC_PL_BS SET LAST_SALDO = Var_LabaRugiBerjalan WHERE USR_ID = UserId AND ID_COA = ID_COA1;
-- 1.2 L/R DITAHAN ATAU AKUMULASI LABA-RUGI awal Tahun sampai tgl Neraca
SELECT SUM(CASE WHEN ID_COA IN (3,345) THEN LAST_SALDO ELSE -LAST_SALDO END) INTO Var_LabaRugiDitahan
FROM temporary.FNC_PL
WHERE USR_ID = UserId AND ID_COA IN (3,4,6,7,345);
UPDATE temporary.FNC_PL_BS SET LAST_SALDO = LAST_SALDO + Var_LabaRugiDitahan WHERE USR_ID = UserId AND ID_COA = ID_COA2;
-- 1.3 Pasiva ditambah LABA-RUGI Berjalan --2011-05-28--
UPDATE temporary.FNC_PL_BS SET LAST_SALDO = LAST_SALDO + Var_LabaRugiDitahan
WHERE USR_ID = UserId AND ID_COA = (SELECT ID_COA FROM DT_COA WHERE CD_COA='2000');
-- 5.1 HITUNG HEADER YG MASIH ADA TOP HEADER-NYA --
FOR REC IN
(
SELECT A.TOP_ID_COA, A.ID_COA, A.LVL, B.DBT_CRDT
FROM temporary.FNC_PL_BS A, DT_COA B
WHERE A.USR_ID = UserId AND A.HDR_DTL = 'H' AND A.TOP_ID_COA IS NOT NULL AND A.ID_COA = B.ID_COA
ORDER BY A.LVL DESC
)
LOOP
FOR REC2 IN
(
SELECT SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
ELSE
CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
END
) SALDO,
SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
ELSE
CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
END
) LAST_SALDO
FROM
( SELECT A2.ID_COA, A2.LVL, A2.SALDO, A2.LAST_SALDO
FROM temporary.FNC_PL_BS A2
WHERE A2.USR_ID = UserId --AND A2.ID_COA <> ID_COA1
START WITH A2.USR_ID = UserId AND A2.TOP_ID_COA = REC.TOP_ID_COA AND A2.ID_COA = REC.ID_COA CONNECT BY PRIOR A2.ID_COA = A2.TOP_ID_COA
) A3, DT_COA B3
WHERE
A3.LVL = rec.LVL + 1 AND A3.ID_COA = B3.ID_COA
)
LOOP
UPDATE temporary.FNC_PL_BS SET SALDO = REC2.SALDO, LAST_SALDO = REC2.LAST_SALDO WHERE USR_ID =UserId AND ID_COA = REC.ID_COA;
END LOOP;
END LOOP;
-- 5.2 HITUNG TOP HEADER-NYA --
FOR REC IN
(
SELECT A.ID_COA, A.LVL, B.DBT_CRDT
FROM temporary.FNC_PL_BS A, DT_COA B
WHERE A.USR_ID = UserId AND A.HDR_DTL ='H' AND A.TOP_ID_COA IS NULL AND A.ID_COA = B.ID_COA
)
LOOP
FOR REC2 IN
(
SELECT SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
ELSE
CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.SALDO,0) ELSE -NVL(A3.SALDO,0) END
END
) SALDO,
SUM( CASE WHEN REC.DBT_CRDT = 'D' THEN
CASE WHEN B3.DBT_CRDT = 'D' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
ELSE
CASE WHEN B3.DBT_CRDT = 'C' THEN NVL(A3.LAST_SALDO,0) ELSE -NVL(A3.LAST_SALDO,0) END
END
) LAST_SALDO
FROM
( SELECT A2.ID_COA, A2.LVL, A2.SALDO, A2.LAST_SALDO
FROM temporary.FNC_PL_BS A2
WHERE A2.USR_ID = UserId
START WITH A2.USR_ID = UserId AND A2.TOP_ID_COA IS NULL AND A2.ID_COA = REC.ID_COA CONNECT BY PRIOR A2.ID_COA = A2.TOP_ID_COA
) A3, DT_COA B3
WHERE
A3.LVL = rec.LVL + 1 AND A3.ID_COA = B3.ID_COA
)
LOOP
UPDATE temporary.FNC_PL_BS SET SALDO = REC2.SALDO, LAST_SALDO = REC2.LAST_SALDO WHERE USR_ID = UserId AND ID_COA = REC.ID_COA;
END LOOP;
END LOOP;
-- HAPUS YG SALDO BULAN INI DAN YG LALU = NOL
DELETE temporary.FNC_PL_BS WHERE USR_ID = UserId AND NVL(SALDO,0) = 0 AND NVL(LAST_SALDO,0) = 0;
COMMIT;
END Sp_Fnc_Neraca_Report;
/
Tidak ada komentar:
Posting Komentar