/* Formatted on 2012/08/01 18:37 (Formatter Plus v4.8.8) */
Select Decode('', '', To_Char(Sysdate,'dd/Mon/yyyy'), '') From Dual
SELECT TYPE,stationname, accountcode, trusttype, openbalcredit, openbaldebit,
SUM (credit)
+ NVL
((SELECT CASE WHEN SUM (credit) - SUM (debit) < 0 THEN SUM (debit) - SUM (credit) ELSE 0 END
FROM cb_voucher_info info,
cb_voucher_details details,
cb_bank_info binfo
WHERE details.keyno = info.keyno
AND binfo.accountno = info.accountno
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN DECODE ('01/Jan/2010','', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
AND UPPER (TRIM (binfo.trusttype)) = UPPER (TRIM (vouinfo.trusttype))
AND binfo.accountcode = vouinfo.accounthead
GROUP BY info.accountno, binfo.accountcode, binfo.trusttype),
0
)
- NVL((SELECT SUM (credit)
FROM cb_voucher_info info,cb_voucher_details det,cb_bank_info bank
WHERE vouchertype = 'C' AND det.keyno = info.keyno AND bank.accountno = info.emp_party_code
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN DECODE ('01/Jan/2010','', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
AND bank.accountcode = vouinfo.accounthead),
0
) credit,
SUM (debit)
+ NVL((SELECT CASE WHEN SUM(credit) - SUM(debit) > 0 THEN SUM (credit) - SUM (debit) ELSE 0 END
FROM cb_voucher_info info, cb_voucher_details details,cb_bank_info binfo
WHERE details.keyno = info.keyno AND binfo.accountno = info.accountno
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN DECODE ('01/Jan/2010', '', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
AND UPPER (TRIM (binfo.trusttype)) = UPPER (TRIM (vouinfo.trusttype))
AND binfo.accountcode = vouinfo.accounthead
GROUP BY info.accountno, binfo.accountcode, binfo.trusttype),0)
- NVL
((SELECT SUM (credit) FROM cb_voucher_info info,cb_voucher_details det,cb_bank_info bank
WHERE vouchertype = 'C'
AND det.keyno = info.keyno AND bank.accountno = info.accountno
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN Decode('01/Jan/2010','','01/Apr/'||GET_FINYEAR_VAL('01/Apr/2012')||'','01/Jan/2010')
AND DECODE ('05/Dec/2012','','01/jan/3000','05/Dec/2012')
AND det.accounthead = vouinfo.accounthead),0) debit
FROM (SELECT accountcodetype TYPE,info.STATIONNAME stationname,--
UPPER (dt.accounthead || ' ' || ainfo.particular) accountcode,
dt.accounthead accounthead, info.trusttype trusttype,
getaccheadopenbal (dt.accounthead,info.trusttype,'01/Apr/2012','C') openbalcredit,
getaccheadopenbal (dt.accounthead,info.trusttype,'01/Apr/2012','D') openbaldebit,
SUM (credit) credit, SUM (debit) debit
FROM cb_voucher_info info,cb_voucher_details dt,
cb_accountcode_info ainfo,cb_accountcodetype_info acctype
WHERE acctype.code = ainfo.TYPE AND dt.keyno = info.keyno
AND ainfo.accounthead = dt.accounthead
AND info.cancel_status = 'N' AND info.vouchertype <> 'N'
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN DECODE ('01/Jan/2010','', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
GROUP BY dt.accounthead,info.trusttype,ainfo.particular,accountcodetype,info.stationname--
UNION ALL
SELECT accountcodetype TYPE, nvl(jinfo.unitcode,'') stationname,
UPPER (dt.accountcode || ' ' || ainfo.particular) accountcode,
dt.accountcode accounthead, jinfo.trusttype trusttype,
getaccheadopenbal(dt.accountcode,jinfo.trusttype,'01/Apr/2012','C') openbalcredit,
getaccheadopenbal(dt.accountcode,jinfo.trusttype,'01/Apr/2012','D') openbaldebit,
SUM (credit) credit, SUM (debit) debit
FROM cb_journalvoucher jinfo,cb_journalvoucher_details dt,
cb_accountcode_info ainfo,cb_accountcodetype_info acctype
WHERE acctype.code = ainfo.TYPE AND dt.keyno = jinfo.keyno AND ainfo.accounthead = dt.accountcode
AND UPPER (TRIM (jinfo.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND jinfo.voucher_dt BETWEEN DECODE ('01/Jan/2010','', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
GROUP BY dt.accountcode,jinfo.trusttype,ainfo.particular,accountcodetype,jinfo.unitcode
UNION ALL
SELECT accountcodetype TYPE,nvl(det.unitcode,'')stationname,
UPPER (det.accounthead || ' ' || info.particular) accountcode,
det.accounthead accounthead, det.trusttype trusttype,
getaccheadopenbal (det.accounthead,det.trusttype,'01/Apr/2012','C') openbalcredit,
getaccheadopenbal (det.accounthead,det.trusttype,'01/Apr/2012','D') openbaldebit,
0 credit, 0 debit
FROM cb_accountcode_details det,cb_accountcode_info info,cb_accountcodetype_info acctype
WHERE info.accounthead = det.accounthead
AND UPPER (TRIM (det.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND acctype.code = info.TYPE
AND det.accounthead || ' ' || det.trusttype NOT IN (
SELECT dt.accountcode || ' ' || info.trusttype
FROM cb_journalvoucher info,cb_journalvoucher_details dt,
cb_accountcode_info ainfo,cb_accountcodetype_info acctype
WHERE acctype.code = ainfo.TYPE AND dt.keyno = info.keyno
AND ainfo.accounthead = dt.accountcode
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN DECODE ('01/Jan/2010','', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
GROUP BY dt.accountcode, info.trusttype
UNION
SELECT dt.accounthead || ' ' || info.trusttype
FROM cb_voucher_info info,
cb_voucher_details dt,
cb_accountcode_info ainfo,
cb_accountcodetype_info acctype
WHERE acctype.code = ainfo.TYPE AND dt.keyno = info.keyno
AND ainfo.accounthead = dt.accounthead
AND UPPER (TRIM (info.trusttype)) LIKE UPPER (TRIM ('PreCheck'))
AND info.voucher_dt BETWEEN DECODE ('01/Jan/2010','', '01/jan/1000','01/Jan/2010')
AND DECODE ('05/Dec/2012','', '01/jan/3000','05/Dec/2012')
GROUP BY dt.accounthead, info.trusttype)) vouinfo
GROUP BY TYPE,accountcode,trusttype,openbalcredit,openbaldebit,vouinfo.accounthead,vouinfo.stationname
ORDER BY accountcode, trusttype, TYPE,vouinfo.stationname
INFO: CORE3282: stdout: - the compare query is...select (case when PREPERATION_D
T>=to_date('01/Aug/2012','dd/Mon/yyyy') then 'G2000' else 'L2000' end)voucompare
from cb_voucher_info where keyno='070812000013'
INFO: CORE3282: stdout: - the vouchergenkeyG2000
INFO: CORE3282: stdout: - the querey is..SELECT vouchertype || 'V/' ||(select BA
NKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) ||
'/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_
voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fy
ear=vinfo.fyear and vinfo.STATUS='A' and info.accountno = vinfo.accountno),4,0)
FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper(?)
No comments:
Post a Comment