Scribd
Upload
38 views1 page

Update Query

This SQL query selects the branch, a remarks column indicating if it is "Clear" or "Pending", and several count columns from various tables related to transactions, users, and general ledger balances for each branch. It joins data from multiple tables to aggregate pending counts for each branch and indicates the overall status as "Clear" if all counts are zero, or "Pending" otherwise.

Uploaded by

Adeel Ahmed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
38 views1 page

Update Query

This SQL query selects the branch, a remarks column indicating if it is "Clear" or "Pending", and several count columns from various tables related to transactions, users, and general ledger balances for each branch. It joins data from multiple tables to aggregate pending counts for each branch and indicates the overall status as "Clear" if all counts are zero, or "Pending" otherwise.

Uploaded by

Adeel Ahmed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT a.branch, CASE WHEN a.Pending_Maintainance + a.all_tran + a.

teller_tran +
NVL(a.Tr_Diff,0) + NVL(a.Till_Vault,0) + a.Users + a.pending_gl = 0 THEN 'Clear'
ELSE 'Pending' END Remarks, a.Pending_Maintainance, a.all_tran, a.teller_tran,
a.Tr_Diff, a.Till_Vault, a.pending_gl, a.Users fROM (SELECT br.branch_code ||'-'||
br.branch_name Branch, NVL(pm.Pending_Maintainance,0) Pending_Maintainance,
NVL(tt.Teller_Tran,0) Teller_Tran, NVL(at.ALL_Tran,0) ALL_Tran, NVL(us.users,0)
users, NVL(trd.Tr_Diff,0) Tr_Diff, NVL(tv.Till_Vault,0) Till_Vault,
NVL(glb.pending_gl,0) pending_gl fROM flexcube.sttm_branch br LEFT OUTER JOIN
(SELECT BRANCH_CODE, COUNT(*) Pending_Maintainance FROM flexcube.sttb_record_log
WHERE (Auth_Stat = 'U' AND Nvl (Tanking_Status, 'N') <> 'T') GROUP BY
BRANCH_CODE ) pm ON (pm.BRANCH_CODE = br.BRANCH_CODE) LEFT OUTER JOIN (SELECT
branchcode, COUNT (*) Teller_Tran FROM flexcube.fbtb_txnlog_master b WHERE
(b.txnstatus = 'IPR' OR b.txnstatus = 'MAR') GROUP BY branchcode ) tt ON
(tt.branchcode = br.branch_code) LEFT OUTER JOIN (SELECT SUBSTR(trn_ref_no,1,3)
branchcode, COUNT (*) ALL_Tran FROM flexcube.actb_daily_log WHERE auth_stat = 'U'
AND delete_stat = ' ' GROUP BY SUBSTR(trn_ref_no,1,3) ) at ON (at.branchcode =
br.BRANCH_CODE) LEFT OUTER JOIN (SELECT home_branch, COUNT(*) users FROM
flexcube.smtb_current_users GROUP BY HOME_BRANCH ) us ON (us.home_branch =
br.BRANCH_CODE) LEFT OUTER JOIN (SELECT SUBSTR (a.trn_ref_no, 1, 3) branch, SUM
(DECODE (drcr_ind, 'C', a.lcy_amount, -a.lcy_amount)) Tr_Diff FROM
flexcube.actb_daily_log a WHERE NVL (delete_stat, 'X') <> 'D' AND auth_stat
<> 'U' AND a.CATEGORY IN ('1', '2', '3', '4') GROUP BY SUBSTR
(a.trn_ref_no, 1, 3) ) trd ON (trd.branch = br.BRANCH_CODE) LEFT OUTER JOIN
(SELECT b.ac_branch, SUM( CASE wHEN b.balance <> 0 THEN 1 ELSE 0 END ) pending_gl
FROM (SELECT gt.ac_branch, gt.ac_no, glm.gl_desc,
ABS(SUM(DECODE(gt.DRCR_IND,'C',gt.lcy_amount,-gt.lcy_amount))) Balance FROM
flexcube.acvw_all_ac_entries gt JOIN flexcube.GLTM_GLMASTER glm ON (glm.gl_code =
gt.ac_no) WHERE gt.ac_no IN ('404071001','404071002','605011201') GROUP BY
gt.ac_branch, gt.ac_no, glm.gl_desc ) b GROUP BY b.ac_branch ) glb ON
(glb.ac_branch = br.branch_code) LEFT OUTER JOIN (SELECT BRANCH_CODE,
NVL(COUNT(*),0) Till_Vault FROM flexcube.fbtb_till_master WHERE user_id IS NOT NULL
GROUP BY BRANCH_CODE ) tv ON (tv.branch_code = br.branch_code) ) a where
a.Pending_Maintainance + a.all_tran + a.teller_tran + NVL(a.Tr_Diff,0) +
NVL(a.Till_Vault,0) + a.Users + a.pending_gl <> 0 ORDER BY 1

You might also like