View


vw_penjualan (Done)


CREATE VIEW "VW_PENJUALAN" ("CNOFAKTUR","DTANGGAL","CKODE_CUSTOMER","CNAMA_CUST","CALAMAT","CKODEPOS","CNAMA_WILAYAH","CTELEPON","NBOTOL_PINJAMAN","NUANG_JAMINAN","CKODE_PRODUK","CNAMA_PRODUK","CNOPOLISI","CKODE_PENGEMUDI","CNAMA_PENGEMUDI","NHARGA_SATUAN","NJML_KIRIM","NBOTOL_KOSONG","CJENIS_BAYAR","CKODE_SALES","CNAMA_SALES","CCETAK_HARGA") AS 
select cnofaktur, dtanggal, tbl_penjualan.ckode_customer, tbl_customer.cnama as cnama_cust,
       tbl_customer.calamat, tbl_wilayah.ckodepos, tbl_wilayah.cketerangan as cnama_wilayah, tbl_customer.ctelepon, tbl_customer.nbotol_pinjaman, nuang_jaminan, tbl_penjualan.ckode_produk, tbl_produk.cnama as cnama_produk,
       tbl_penjualan.cnopolisi, tbl_penjualan.ckode_pengemudi, tbl_pengemudi.cnama as cnama_pengemudi,
       tbl_penjualan.nharga_satuan, njml_kirim, nbotol_kosong, cjenis_bayar, 
       tbl_penjualan.ckode_sales, tbl_sales.cnama as cnama_sales, tbl_customer.ccetak_harga
from tbl_penjualan, tbl_customer, tbl_wilayah, tbl_produk, tbl_pengemudi, tbl_sales
where tbl_penjualan.ckode_customer=tbl_customer.ckode_customer and
      tbl_penjualan.ckode_produk=tbl_produk.ckode_produk and
      tbl_penjualan.ckode_pengemudi=tbl_pengemudi.ckode_pengemudi(+) and
      tbl_penjualan.ckode_sales=tbl_sales.ckode_sales(+) and
      tbl_customer.ckode_pos=tbl_wilayah.ckodepos(+)

MySQL Version


CREATE OR REPLACE VIEW vw_penjualan (cnofaktur,dtanggal,ckode_customer,cnama_cust,calamat,ckodepos,cnama_wilayah,ctelepon,nbotol_pinjaman,nuang_jaminan,ckode_produk,cnama_produk,cnopolisi,ckode_pengemudi,cnama_pengemudi,nharga_satuan,njml_kirim,nbotol_kosong,cjenis_bayar,ckode_sales,cnama_sales,ccetak_harga, ckode_usaha, cnama_usaha) 
AS select cnofaktur, dtanggal, tbl_penjualan.ckode_customer, tbl_customer.cnama AS cnama_cust,
       tbl_customer.calamat, tbl_wilayah.ckodepos, tbl_wilayah.cketerangan as cnama_wilayah, tbl_customer.ctelepon, tbl_customer.nbotol_pinjaman,     
       nuang_jaminan, tbl_penjualan.ckode_produk, stock_master.description as cnama_produk,
       tbl_penjualan.cnopolisi, tbl_penjualan.ckode_pengemudi, tbl_pengemudi.cnama as cnama_pengemudi,
       tbl_penjualan.nharga_satuan, njml_kirim, nbotol_kosong, cjenis_bayar, 
       tbl_penjualan.ckode_sales, tbl_sales.cnama as cnama_sales, 
       tbl_customer.ccetak_harga,
       tbl_bidangusaha.ckode_usaha as ckode_usaha, 
      tbl_bidangusaha.cketerangan as cnama_usaha
from tbl_penjualan LEFT JOIN(tbl_pengemudi) ON (tbl_penjualan.ckode_pengemudi=tbl_pengemudi.ckode_pengemudi)
LEFT JOIN(tbl_sales) ON (tbl_penjualan.ckode_sales=tbl_sales.ckode_sales) 
INNER JOIN(tbl_customer, stock_master) ON (tbl_penjualan.ckode_customer=tbl_customer.ckode_customer  AND tbl_penjualan.ckode_produk=stock_master.stock_id), tbl_wilayah, tbl_bidangusaha
WHERE tbl_customer.ckode_pos=tbl_wilayah.ckodepos AND tbl_customer.ckode_usaha=tbl_bidangusaha.ckode_usaha


v_test

CREATE VIEW "TEST" ("CTRANS","CNOFAKTUR","CNO_RECEIPT","DTANGGAL","CKODE_CUSTOMER","NSALES","NDEBIT","NRECEIPT","NCREDIT") AS 
SELECT 'SL' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, sh.ckode_customer, NVL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales, 0 ndebit, 0 nreceipt, 0 ncredit    
 FROM tbl_sales_h sh,
      (SELECT sh.cnofaktur, NVL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit 
       FROM tbl_sales_h sh,
            tbl_sales_d sd
       WHERE sh.cnofaktur=sd.cnofaktur 
       GROUP BY sh.cnofaktur) sd
 WHERE sh.cnofaktur=sd.cnofaktur 
 GROUP BY sh.cnofaktur, sh.dtanggal, sh.ckode_customer

vapjurnal (Done)


CREATE VIEW "VAPJURNAL" ("DTANGGAL","CKODE_COA","DEBIT","CREDIT") AS 
  SELECT A.DTANGGAL,B.CKODE_COA,SUM(B.NDEBIT) DEBIT,SUM(B.NCREDIT) CREDIT
FROM TBL_AP_H A,TBL_AP_D B
WHERE A.CNO_AP=B.CNO_AP
GROUP BY A.DTANGGAL,B.CKODE_COA


MySQL Version 


CREATE OR REPLACE VIEW vapjurnal (cnofaktur,dtanggal,ckode_coa,debit,credit) AS 
  SELECT a.cnofaktur, a.dtanggal,b.ckode_coa,SUM(b.ndebit) debit,SUM(b.ncredit) credit
FROM tbl_ap_h a,tbl_ap_d b
WHERE a.cno_ap=b.cno_ap
GROUP BY a.dtanggal,b.ckode_coa ;

varjurnal (Done)


CREATE VIEW "VARJURNAL" ("DTANGGAL","CKODE_COA","DEBIT","CREDIT") AS 
SELECT A.DTANGGAL,B.CKODE_COA,SUM(B.NDEBIT) DEBIT,SUM(B.NCREDIT) CREDIT
FROM TBL_AR_H A,TBL_AR_D B
WHERE A.CNO_AR=B.CNO_AR
GROUP BY A.DTANGGAL,B.CKODE_COA

MySQL Version 


CREATE OR REPLACE VIEW varjurnal(dtanggal,ckode_coa,debit,credit) AS 
SELECT a.dtanggal,b.ckode_coa,SUM(b.ndebit) debit,SUM(b.ncredit) credit
FROM tbl_ar_h a,tbl_ar_d b
WHERE a.cno_ar=b.cno_ar
GROUP BY a.dtanggal,b.ckode_coa ;


vcashjurnal1


CREATE VIEW "VCASHJURNAL1" ("CREFFERENCE","CFROM","CKODE_COA","DTANGGAL","DEBIT","CREDIT") AS 
select a.crefference,a.cfrom,a.ckode_coa,a.dtanggal,sum(b.ndebit) Debit, sum(b.ncredit) Credit
from tbl_cash_h a, tbl_cash_d b
where a.cno_receipt=b.cno_receipt and cfrom in ('C','D')
group by a.crefference,a.cfrom,a.ckode_coa,a.dtanggal

vcashjurnal1A (Done)


CREATE VIEW "VCASHJURNAL1A" ("CIFC_AR","CREFFERENCE","CKODE_COA","DTANGGAL","CREDIT") AS 
SELECT D.CIFC_AR,C.CREFFERENCE,C.CKODE_COA,C.DTANGGAL,SUM(C.CREDIT) CREDIT
FROM TBL_CUSTOMER D,(
     select a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal, sum(b.ncredit) Credit
     from tbl_cash_h a, tbl_cash_d b
     where a.cno_receipt=b.cno_receipt and cfrom = 'C'
     group by a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal) C
WHERE D.CKODE_CUSTOMER=C.CKODE_FROM
GROUP BY D.CIFC_AR,C.CREFFERENCE,C.CKODE_COA,C.DTANGGAL

MySQL Version 


CREATE OR REPLACE VIEW vcashjurnal1a_subquery1 (crefference,ckode_from,ckode_coa,dtanggal,credit) AS
     SELECT a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal, SUM(b.ncredit) credit
     FROM tbl_cash_h a, tbl_cash_d b
     WHERE a.cno_receipt=b.cno_receipt AND cfrom = 'C'
     GROUP BY a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal ;

CREATE OR REPLACE VIEW vcashjurnal1a (cifc_ar,crefference,ckode_coa,dtanggal,credit) AS 
SELECT d.cifc_ar,c.crefference,c.ckode_coa,c.dtanggal,SUM(c.credit) credit
FROM tbl_customer d,vcashjurnal1a_subquery1 c
WHERE d.ckode_customer=c.ckode_from
GROUP BY d.cifc_ar,c.crefference,c.ckode_coa,c.dtanggal ;

vcashjurnal1B (Done)


CREATE VIEW "VCASHJURNAL1B" ("CIFC_AP","CREFFERENCE","CKODE_COA","DTANGGAL","DEBIT") AS 
SELECT D.CIFC_Ap,C.CREFFERENCE,C.CKODE_COA,C.DTANGGAL,SUM(C.debit) DEBIT
FROM TBL_Supplier D,(
     select a.crefference,ckode_from,a.ckode_coa,a.dtanggal, sum(b.ndebit) debit
     from tbl_cash_h a, tbl_cash_d b
     where a.cno_receipt=b.cno_receipt and cfrom = 'C'
     group by a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal) C
WHERE D.CKODE_supplier=C.CKODE_FROM
GROUP BY D.CIFC_Ap,C.CREFFERENCE,C.Ckode_FROM,C.CKODE_COA,C.DTANGGAL

MySQL Version 


CREATE OR REPLACE VIEW vcashjurnal1b_subquery1(crefference,ckode_from,ckode_coa,dtanggal,debit) AS
     SELECT a.crefference,ckode_from,a.ckode_coa,a.dtanggal, sum(b.ndebit) debit
     from tbl_cash_h a, tbl_cash_d b
     where a.cno_receipt=b.cno_receipt and cfrom = 'C'
     group by a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal ;

CREATE OR REPLACE VIEW  vcashjurnal1b (cifc_ap,crefference,ckode_coa,dtanggal,debit) AS 
SELECT d.payable_account,c.crefference,c.ckode_coa,c.dtanggal,SUM(c.debit) debit
FROM suppliers d,vcashjurnal1b_subquery1 c
WHERE d.supplier_id=c.ckode_from
GROUP BY d.payable_account,c.crefference,c.ckode_from,c.ckode_coa,c.dtanggal ;

vcashjurnal2 (Done)


CREATE VIEW "VCASHJURNAL2" ("CREFFERENCE","CTYPE","CFROM","CKODE_COA","DTANGGAL","CNOFAKTUR","DEBIT","CREDIT") AS 
select a.crefference,a.ctype,a.cfrom,a.ckode_coa,a.dtanggal,b.cnofaktur,sum(b.ndebit) Debit,sum(b.ncredit) Credit
from tbl_cash_h a, tbl_cash_d b
where a.cno_receipt=b.cno_receipt and cfrom ='G'
group by a.crefference,a.ctype,a.cfrom,a.ckode_coa,a.dtanggal,b.cnofaktur

MySQL Version 


CREATE OR REPLACE VIEW vcashjurnal2 (crefference,ctype,cfrom,ckode_coa,dtanggal,cnofaktur,debit,credit) AS 
SELECT a.crefference,a.ctype,a.cfrom,a.ckode_coa,a.dtanggal,b.cnofaktur,SUM(b.ndebit) debit,SUM(b.ncredit) credit
FROM tbl_cash_h a, tbl_cash_d b
WHERE a.cno_receipt=b.cno_receipt AND cfrom ='G'
GROUP BY a.crefference,a.ctype,a.cfrom,a.ckode_coa,a.dtanggal,b.cnofaktur ;

vdetail_gl


CREATE VIEW "VDETAIL_GL" ("CNOFAKTUR","CKODE_COA","ACCOUNT","CKETERANGAN","NDEBIT","NCREDIT","DTANGGAL","CTYPE") AS 
SELECT ALL A.CNOFAKTUR,B.CKODE_COA, C.CKETERANGAN ACCOUNT, B.CKETERANGAN,B.NDEBIT, B.NCREDIT, A.DTANGGAL,
A.CTYPE
FROM TBL_GL_H A, TBL_GL_D B,TBL_COA C
WHERE ((A.CNOFAKTUR=B.CNOFAKTUR)
 AND (B.CKODE_COA=C.CKODE_COA)) ;

MySQL Version 


CREATE OR REPLACE VIEW vdetail_gl (cnofaktur,ckode_coa,account,cketerangan,ndebit,ncredit,dtanggal,ctype) AS 
SELECT ALL a.cnofaktur,b.ckode_coa, c.account_name account, b.cketerangan,b.ndebit, b.ncredit, a.dtanggal,
a.ctype
FROM tbl_gl_h a, tbl_gl_d b,chart_master c
WHERE a.cnofaktur=b.cnofaktur
 AND b.ckode_coa=c.account_code ;

vpurch_jurnal (Done)


CREATE VIEW "VPURCH_JURNAL" ("DTANGGAL","CDEPOT","CDEPT","COTHER","CACCPURCH","CACCINV","NFREIGHT","PRICE","DISC","TAX") AS 
select a.dtanggal,a.cdepot,a.cdept,a.cother,a.caccpurch,c.caccinv,a.nfreight,
SUM(nvl(b.nqty,0)*nvl(b.nprice,0)) Price, sum(b.ndisc) Disc,
SUM((nvl(b.nqty,0)*nvl(b.nprice,0)-nvl(b.ndisc,0))*a.ntax/100) Tax
from tbl_purch_h a, tbl_purch_d b,tbl_produk c
where a.cnofaktur=b.cnofaktur and b.ckode_produk=c.ckode_produk
group by a.dtanggal,a.nfreight,a.cdepot,a.cdept,a.cother,c.caccinv,a.caccpurch

MySQL Version


CREATE OR REPLACE VIEW vpurch_jurnal (cnofaktur, dtanggal,cdepot,cdept,cother,caccpurch,caccinv,nfreight,price,disc,tax) AS
SELECT a.cnofaktur, a.dtanggal,a.cdepot,a.cdept,a.cother,a.caccpurch,c.inventory_account,a.nfreight, SUM(IFNULL(b.nqty,0)*IFNULL(b.nprice,0)) price, SUM(b.ndisc) disc, SUM((IFNULL(b.nqty,0)*IFNULL(b.nprice,0)-IFNULL(b.ndisc,0))*a.ntax/100) tax
FROM tbl_purch_h a,
     tbl_purch_d b,
     stock_master c
WHERE a.cnofaktur=b.cnofaktur
  AND b.ckode_produk=c.stock_id
GROUP BY a.dtanggal,
         a.nfreight,
         a.cdepot,
         a.cdept,
         a.cother,
         c.inventory_account,
         a.caccpurch ;

vsalesjurnal (Done)


CREATE VIEW "VSALESJURNAL" ("DTANGGAL","DEPOT","CACCSALES","CACCINV","CACCCOST","NFREIGHT","PRICE","TAX","DISC","HPP") AS 
SELECT A.DTANGGAL,SUBSTR(A.CNOFAKTUR,1,3) DEPOT,D.CACCSALES,D.CACCINV,D.CACCCOST, A.NFREIGHT,SUM(NVL(B.NQTY,0)*NVL(B.NPRICE,0)) PRICE,
           SUM(NVL(B.NQTY,0)*NVL(B.NPRICE,0)*A.NTAX/100) TAX, SUM(B.NDISC) DISC,
           SUM(NVL(C.NHPP,0)*NVL(C.NOUT,0)) HPP
           FROM TBL_SALES_H A,TBL_SALES_D B,TBL_FIFO C,TBL_PRODUK D
           WHERE A.CNOFAKTUR=B.CNOFAKTUR AND A.CNOFAKTUR=C.CNO_OUT AND B.CKODE_PRODUK=D.CKODE_PRODUK
           GROUP BY A.DTANGGAL,SUBSTR(A.CNOFAKTUR,1,3),A.NFREIGHT,D.CACCSALES,D.CACCINV,D.CACCCOST

MySQL Version


CREATE OR REPLACE  VIEW vsalesjurnal(cnofaktur, dtanggal,depot,caccsales,caccinv,cacccost,nfreight,price,tax,disc,hpp)
AS 
SELECT a.cnofaktur, a.dtanggal,SUBSTR(a.cnofaktur,1,3) depot,
       d.sales_account,d.inventory_account,d.cogs_account,a.nfreight,SUM(IFNULL(b.nqty,0)*IFNULL(b.nprice,0))
price, SUM(IFNULL(b.nqty,0)*IFNULL(b.nprice,0)*a.ntax/100) tax,
SUM(b.ndisc) disc,
           SUM(IFNULL(c.nhpp,0)*IFNULL(c.nout,0)) hpp
           FROM tbl_sales_h a,tbl_sales_d b,tbl_fifo c,stock_master d
           WHERE a.cnofaktur=b.cnofaktur AND a.cnofaktur=c.cno_out AND b.ckode_produk=d.stock_id
           GROUP BY a.dtanggal,SUBSTR(a.cnofaktur,1,3),a.nfreight,d.sales_account,d.inventory_account,d.cogs_account ;

v_ap


CREATE VIEW "V_AP" ("CTRANS","CNOFAKTUR","CNO_PAYMENT","DTANGGAL","CKODE_SUPPLIER","NPURCHASE","NCREDIT","NPAYMENT","NDEBIT") AS 
SELECT ctrans, cnofaktur, cno_payment, dtanggal, ckode_supplier, npurchase, ncredit, npayment, ndebit
FROM
(SELECT 'BG' ctrans, cnofaktur, '' cno_payment, max(dtanggal) dtanggal, ckode_supplier, 0 npurchase, namount ncredit, 0 npayment, 0 ndebit
 FROM tbl_supp_saldo
 GROUP BY cnofaktur, ckode_supplier, namount
UNION
 SELECT 'PR' ctrans, ph.cnofaktur, '' cno_payment, ph.dtanggal, ph.ckode_supplier, (pd.ncredit*(1+(ph.ntax/100)))+ph.nfreight npurchase, 0 ncredit, 0 npayment, 0 ndebit
 FROM tbl_purch_h ph,
     (SELECT ph.cnofaktur, sum((pd.nqty*pd.nprice)-pd.ndisc) ncredit
      FROM tbl_purch_h ph, tbl_purch_d pd
      WHERE ph.cnofaktur=pd.cnofaktur
      GROUP BY ph.cnofaktur) pd
 WHERE ph.cnofaktur=pd.cnofaktur
UNION
 SELECT 'PY' ctrans, pd.cnofaktur, ph.cno_receipt cno_payment, ph.dtanggal, ph.ckode_from  ckode_supplier, 0 npurchase, 0 ncredit, NVL(SUM(pd.ndebit-pd.ncredit),0) npayment, 0 ndebit
 FROM tbl_cash_h ph, tbl_cash_d pd
 WHERE ph.cno_receipt=pd.cno_receipt and
       ph.cfrom='S'
 GROUP by pd.cnofaktur, ph.cno_receipt, ph.dtanggal, ph.ckode_from
UNION
 SELECT 'AP' ctrans, ah.cnofaktur, '' cno_payment, ah.dtanggal, ah.ckode_supplier, 0 npurchase, sum(decode(ah.ctype,'C',ad.ncredit,decode(ah.ctype,'D',ad.ndebit*-1,0))) ncredit, 0 npayment, 0 ndebit
 FROM tbl_ap_h ah, tbl_ap_d ad
 WHERE ah.cno_ap=ad.cno_ap
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_supplier)
ORDER BY ckode_supplier, cnofaktur, dtanggal, cno_payment


MySQL Version 


CREATE OR REPLACE VIEW v_ap_subquery1_1(cnofaktur,ncredit) AS 
SELECT ph.cnofaktur, sum((pd.nqty*pd.nprice)-pd.ndisc) ncredit
      FROM tbl_purch_h ph, tbl_purch_d pd
      WHERE ph.cnofaktur=pd.cnofaktur
      GROUP BY ph.cnofaktur ;
CREATE OR REPLACE VIEW v_ap_subquery1(ctrans,cnofaktur,cno_payment,dtanggal,ckode_supplier,npurchase,ncredit,npayment,ndebit) AS 
SELECT 'PR' ctrans, ph.cnofaktur, '' cno_payment, ph.dtanggal, ph.ckode_supplier, (pd.ncredit*(1+(ph.ntax/100)))+ph.nfreight npurchase, 0 ncredit, 0 npayment, 0 ndebit
 FROM tbl_purch_h ph,
     v_ap_subquery1_1 pd
 WHERE ph.cnofaktur=pd.cnofaktur ;
CREATE OR REPLACE VIEW v_ap_subquery_top  (ctrans,cnofaktur,cno_payment,dtanggal,ckode_supplier,npurchase,ncredit,npayment,ndebit) AS
SELECT 'BG' ctrans, cnofaktur, '' cno_payment, max(dtanggal) dtanggal, ckode_supplier, 0 npurchase, namount ncredit, 0 npayment, 0 ndebit
 FROM tbl_supp_saldo
 GROUP BY cnofaktur, ckode_supplier, namount
UNION
 SELECT ctrans,cnofaktur,cno_payment,dtanggal,ckode_supplier,npurchase,ncredit,npayment,ndebit
 FROM v_ap_subquery1 
UNION
 SELECT 'PY' ctrans, pd.cnofaktur, ph.cno_receipt cno_payment, ph.dtanggal, ph.ckode_from  ckode_supplier, 0 npurchase, 0 ncredit, IFNULL(SUM(pd.ndebit-pd.ncredit),0) npayment, 0 ndebit
 FROM tbl_cash_h ph, tbl_cash_d pd
 WHERE ph.cno_receipt=pd.cno_receipt and
       ph.cfrom='S'
 GROUP by pd.cnofaktur, ph.cno_receipt, ph.dtanggal, ph.ckode_from
UNION
 SELECT 'AP' ctrans, ah.cnofaktur, '' cno_payment, ah.dtanggal, ah.ckode_supplier, 0 npurchase, sum(IF(ah.ctype='C',ad.ncredit,IF(ah.ctype='D',ad.ndebit*-1,0))) ncredit, 0 npayment, 0 ndebit
 FROM tbl_ap_h ah, tbl_ap_d ad
 WHERE ah.cno_ap=ad.cno_ap
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_supplier ;
CREATE OR REPLACE VIEW v_ap (ctrans,cnofaktur,cno_payment,dtanggal,ckode_supplier,npurchase,ncredit,npayment,ndebit) AS 
SELECT ctrans, cnofaktur, cno_payment, dtanggal, ckode_supplier, npurchase, ncredit, npayment, ndebit
FROM
v_ap_subquery_top 
ORDER BY ckode_supplier, cnofaktur, dtanggal, cno_payment ;


v_ar (Done)


CREATE VIEW "V_AR" ("CTRANS","CNOFAKTUR","CNO_RECEIPT","DTANGGAL","CKODE_CUSTOMER","NSALES","NDEBIT","NRECEIPT","NCREDIT") AS 
SELECT ctrans, cnofaktur, cno_receipt, dtanggal, ckode_customer, nsales, ndebit, nreceipt, ncredit
FROM
(SELECT 'BG' ctrans, cnofaktur, '' cno_receipt, max(dtanggal) dtanggal, ckode_customer, 0 nsales, namount ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_cust_saldo 
 GROUP BY cnofaktur, ckode_customer, namount
UNION
 SELECT 'SL' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, sh.ckode_customer, NVL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales, 0 ndebit, 0 nreceipt, 0 ncredit    
 FROM tbl_sales_h sh,
      (SELECT sh.cnofaktur, NVL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit 
       FROM tbl_sales_h sh,
            tbl_sales_d sd
       WHERE sh.cnofaktur=sd.cnofaktur 
       GROUP BY sh.cnofaktur) sd
 WHERE sh.cnofaktur=sd.cnofaktur 
 GROUP BY sh.cnofaktur, sh.dtanggal, sh.ckode_customer
UNION
 SELECT 'RC' ctrans, rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from ckode_customer, 0 nsales, 0 ndebit, NVL(SUM(rd.ncredit-rd.ndebit),0) nreceipt, 0 ncredit 
 FROM tbl_cash_h rh,
      tbl_cash_d rd
 WHERE rh.cno_receipt=rd.cno_receipt AND
       rh.cfrom='C' 
 GROUP BY rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from
UNION
 SELECT 'AR' ctrans, ah.cnofaktur, '' cno_receipt, ah.dtanggal ,ah.ckode_customer, 0 nsales, sum(decode(ah.ctype,'D',ad.ndebit,decode(ah.ctype,'C',ad.ncredit*-1,0))) ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_ar_h ah, tbl_ar_d ad
 WHERE ah.cno_ar=ad.cno_ar
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_customer)
ORDER BY ckode_customer, cnofaktur, dtanggal, cno_receipt


MySQL Version 


CREATE OR REPLACE VIEW v_ar_sub_query1 as
SELECT 'BG' ctrans, cnofaktur, '' cno_receipt, max(dtanggal) 
 dtanggal, ckode_customer, 0 nsales, namount ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_cust_saldo 
 GROUP BY cnofaktur, ckode_customer, namount ;

CREATE OR REPLACE VIEW v_ar_sub_sub_query2 as
SELECT sh.cnofaktur, IFNULL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit 
       FROM tbl_sales_h sh,
            tbl_sales_d sd
       WHERE sh.cnofaktur=sd.cnofaktur 
       GROUP BY sh.cnofaktur ;

CREATE OR REPLACE VIEW v_ar_sub_query2 as
 SELECT 'SL' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, sh.ckode_customer, 
 IFNULL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales, 0 ndebit, 0 nreceipt, 0 ncredit    
 FROM tbl_sales_h sh,v_ar_sub_sub_query2 sd
 WHERE sh.cnofaktur=sd.cnofaktur  
 GROUP BY sh.cnofaktur, sh.dtanggal, sh.ckode_customer ;

CREATE OR REPLACE VIEW v_ar_sub_query3 as
 SELECT 'RC' ctrans, rd.cnofaktur, 
 rh.cno_receipt, rh.dtanggal, rh.ckode_from ckode_customer, 0 nsales, 0 ndebit, 
 IFNULL(SUM(rd.ncredit-rd.ndebit),0) nreceipt, 0 ncredit 
 FROM tbl_cash_h rh,
      tbl_cash_d rd
 WHERE rh.cno_receipt=rd.cno_receipt AND
       rh.cfrom='C' 
 GROUP BY rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from ;

CREATE OR REPLACE VIEW v_ar_sub_query4 as
SELECT 'AR' ctrans, ah.cnofaktur, 
  '' cno_receipt, ah.dtanggal ,
  ah.ckode_customer, 0 nsales, 
  sum(IF(ah.ctype='D',ad.ndebit,IF(ah.ctype='C',ad.ncredit*-1,0))) ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_ar_h ah, tbl_ar_d ad
 WHERE ah.cno_ar=ad.cno_ar
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_customer ;

CREATE OR REPLACE VIEW v_ar_top_sub_query as
 SELECT * 
 FROM v_ar_sub_query1
UNION
 SELECT *
 FROM v_ar_sub_query2
UNION
 SELECT *
 FROM v_ar_sub_query3
UNION
 SELECT *
 FROM v_ar_sub_query4 ;

CREATE OR REPLACE VIEW 
v_ar(ctrans,cnofaktur,cno_receipt,dtanggal,ckode_customer,nsales,ndebit,nreceipt,ncredit) 
AS   
SELECT ctrans, cnofaktur, cno_receipt, dtanggal, ckode_customer, nsales, ndebit, nreceipt, ncredit
FROM  v_ar_top_sub_query
ORDER BY ckode_customer, cnofaktur, dtanggal, cno_receipt ;

MySQL Version (Filtered)

(

SELECT ctrans, cnofaktur, cno_receipt, dtanggal, ckode_customer, nsales, ndebit, nreceipt, ncredit
FROM
(SELECT 'BG' ctrans, cnofaktur, '' cno_receipt, max(dtanggal) dtanggal, ckode_customer, 0 nsales, namount ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_cust_saldo 
 WHERE ckode_customer LIKE 'U10%'
 GROUP BY cnofaktur, ckode_customer, namount  
 HAVING (MIN(dtanggal) BETWEEN '2005-03-01' AND '2005-03-15')
UNION
  SELECT 'SL' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, sh.ckode_customer, IFNULL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales, 0 ndebit, 0 nreceipt, 0 ncredit    
 FROM tbl_sales_h sh,
      (SELECT sh.ckode_customer, sh.cnofaktur, IFNULL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit 
       FROM tbl_sales_h sh,
            tbl_sales_d sd
       WHERE sh.cnofaktur=sd.cnofaktur AND ckode_customer LIKE 'U10%'
       GROUP BY sh.cnofaktur 
       HAVING (MIN(dtanggal) BETWEEN '2006-03-01' AND '2006-03-02')
      ) sd
 WHERE sh.cnofaktur=sd.cnofaktur  AND sh.ckode_customer LIKE 'U10%'
 GROUP BY sh.cnofaktur, sh.dtanggal, sh.ckode_customer 
 HAVING (MIN(dtanggal) BETWEEN '2006-03-01' AND '2006-03-02') 
UNION
 SELECT 'RC' ctrans, rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from ckode_customer, 0 nsales, 0 ndebit, IFNULL(SUM(rd.ncredit-rd.ndebit),0) nreceipt, 0 ncredit 
 FROM tbl_cash_h rh,
      tbl_cash_d rd
 WHERE rh.cno_receipt=rd.cno_receipt AND
       rh.cfrom='C' AND rh.ckode_from LIKE 'U10%'
 GROUP BY rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from 
 HAVING (MIN(dtanggal) BETWEEN '2006-03-01' AND '2006-03-02') 
UNION
 SELECT 'AR' ctrans, ah.cnofaktur, '' cno_receipt, ah.dtanggal ,ah.ckode_customer, 0 nsales, 
  sum(IF(ah.ctype='D',ad.ndebit,IF(ah.ctype='C',ad.ncredit*-1,0))) ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_ar_h ah, tbl_ar_d ad
 WHERE ah.cno_ar=ad.cno_ar AND ckode_customer LIKE 'U10%'
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_customer
 HAVING (MIN(dtanggal) BETWEEN '2006-03-01' AND '2006-03-02')
) as inner_query
ORDER BY ckode_customer, cnofaktur, dtanggal, cno_receipt 
)

v_cash_customer (Done)


CREATE VIEW "V_CASH_CUSTOMER" ("CNO_RECEIPT","CREFFERENCE","DTANGGAL","NAMOUNT","CNOFAKTUR","CKODE_CUSTOMER","CNAMA","CALAMAT") AS 
select a.cno_receipt,a.crefference,a.dtanggal,sum(b.ncredit-b.ndebit) namount,b.cnofaktur,
c.ckode_customer,c.cnama,c.calamat
from tbl_cash_h a,tbl_cash_d b,tbl_customer c
where a.cno_receipt=b.cno_receipt and
      a.cfrom='C' and
      a.ckode_from=c.ckode_customer
group by a.cno_receipt,a.crefference,a.dtanggal,b.cnofaktur,c.ckode_customer,c.cnama,c.calamat ;

MySQL Version 


CREATE OR REPLACE VIEW v_cash_customer (cno_receipt,crefference,dtanggal,namount,cnofaktur,ckode_customer,cnama,calamat) AS 
select a.cno_receipt,a.crefference,a.dtanggal,sum(b.ncredit-b.ndebit) namount,b.cnofaktur,
c.ckode_customer,c.cnama,c.calamat
from tbl_cash_h a,tbl_cash_d b,tbl_customer c
where a.cno_receipt=b.cno_receipt and
      a.cfrom='C' and
      a.ckode_from=c.ckode_customer
group by a.cno_receipt,a.crefference,a.dtanggal,b.cnofaktur,c.ckode_customer,c.cnama,c.calamat ;

v_cash_supplier (Done)


CREATE VIEW "V_CASH_SUPPLIER" ("CNO_RECEIPT","CREFFERENCE","DTANGGAL","NAMOUNT","CNOFAKTUR","CKODE_SUPPLIER","CNAMA","CALAMAT") AS 
select a.cno_receipt,a.crefference,a.dtanggal,sum(b.ndebit-b.ncredit) namount,b.cnofaktur,
c.ckode_supplier,c.cnama,c.calamat
from tbl_cash_h a,tbl_cash_d b,tbl_supplier c
where a.cno_receipt=b.cno_receipt and
      a.cfrom='S' and
      a.ckode_from=c.ckode_supplier
group by a.cno_receipt,a.crefference,a.dtanggal,b.cnofaktur,c.ckode_supplier,c.cnama,c.calamat

MySQL Version


CREATE OR REPLACE VIEW v_cash_supplier (cno_receipt,crefference,dtanggal,namount,cnofaktur,ckode_supplier,cnama,calamat) 
AS  SELECT a.cno_receipt,a.crefference,a.dtanggal,sum(b.ndebit-b.ncredit) namount,b.cnofaktur,
c.supplier_id,c.supp_name,c.address
from tbl_cash_h a,tbl_cash_d b,suppliers c
where a.cno_receipt=b.cno_receipt and
      a.cfrom='S' and
      a.ckode_from=c.supplier_id
group by a.cno_receipt,a.crefference,a.dtanggal,b.cnofaktur,c.supplier_id,c.supp_name,c.address ; 

v_payable (Done)


CREATE VIEW "V_PAYABLE" ("CNO_AP","DTANGGAL","CNOFAKTUR","NAMOUNT","CKODE_SUPPLIER","CNAMA","CALAMAT") AS 
select a.cno_ap,a.dtanggal,a.cnofaktur,
sum(decode(a.ctype,'C',b.ncredit,decode(a.ctype,'D',b.ndebit*-1,0))) namount,
c.ckode_supplier,c.cnama,c.calamat
from tbl_ap_h a,tbl_ap_d b,tbl_supplier c
where a.cno_ap=b.cno_ap and a.ckode_supplier=c.ckode_supplier
group by a.cno_ap,a.dtanggal,a.cnofaktur,a.ctype,c.ckode_supplier,c.cnama,c.calamat

MySQL Version 


CREATE OR REPLACE VIEW v_payable (cno_ap,dtanggal,cnofaktur,namount,ckode_supplier,cnama,calamat) 
AS 
SELECT a.cno_ap,a.dtanggal,a.cnofaktur,
sum(IF(a.ctype='C',b.ncredit,IF(a.ctype='D',b.ndebit*-1,0))) namount,
c.supplier_id,c.supp_name,c.address
FROM tbl_ap_h a,tbl_ap_d b,suppliers c
WHERE 
   a.cno_ap=b.cno_ap 
AND 
   a.ckode_supplier=c.supplier_id
GROUP BY a.cno_ap,a.dtanggal,
         a.cnofaktur,a.ctype,c.supplier_id,c.supp_name,c.address ;

v_purchase (Done)


CREATE VIEW "V_PURCHASE" ("CNOFAKTUR","DTANGGAL","CACCPURCH","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC","CKODE_SUPPLIER","CNAMA","CALAMAT") AS 
select a.cnofaktur,a.dtanggal,a.caccpurch,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_supplier,c.cnama,c.calamat
from tbl_purch_h a,tbl_purch_d b,tbl_supplier c
where a.cnofaktur=b.cnofaktur and
      c.ckode_supplier=a.ckode_supplier
group by a.cnofaktur,a.dtanggal,a.caccpurch,a.nfreight,a.ntax,b.ckode_produk,c.ckode_supplier,c.cnama,c.calamat

MySQL Version 


CREATE OR REPLACE VIEW v_purchase (cnofaktur,dtanggal,caccpurch,nfreight,tax,ckode_produk,namount,ndisc,ckode_supplier,cnama,calamat) 
AS  SELECT a.cnofaktur,a.dtanggal,a.caccpurch,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.supplier_id,c.supp_name,c.address
from tbl_purch_h a,tbl_purch_d b,suppliers c
where a.cnofaktur=b.cnofaktur and
      c.supplier_id=a.ckode_supplier
group by a.cnofaktur,a.dtanggal,a.caccpurch,a.nfreight,a.ntax,b.ckode_produk,c.supplier_id,c.supp_name,c.address ;

v_purchase_retur (Done)


CREATE VIEW "V_PURCHASE_RETUR" ("CNO_RETUR","CNOFAKTUR","DTANGGAL","CACCPURCH","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC","CKODE_SUPPLIER","CNAMA","CALAMAT") AS 
select a.cno_retur,a.cnofaktur,a.dtanggal,d.caccpurch,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_supplier,c.cnama,c.calamat
from tbl_retur_h a,tbl_retur_d b,tbl_supplier c,tbl_purch_h d
where a.cno_retur=b.cno_retur and
      a.cnofaktur=d.cnofaktur and a.ctype='P' and
      c.ckode_supplier=d.ckode_supplier
group by a.cno_retur,a.cnofaktur,a.dtanggal,d.caccpurch,a.nfreight,a.ntax,b.ckode_produk,c.ckode_supplier,c.cnama,c.calamat

MySQL Version 


CREATE OR REPLACE VIEW 
v_purchase_retur (cno_retur,cnofaktur,dtanggal,
                  caccpurch,nfreight,tax,ckode_produk,
                  namount,ndisc,ckode_supplier
                  ,cnama,calamat) 
AS 
SELECT a.cno_retur,a.cnofaktur,
       a.dtanggal,d.caccpurch,
       a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,
       b.ckode_produk,
       sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
       c.supplier_id,c.supp_name,c.address
FROM tbl_retur_h a,tbl_retur_d b,suppliers c,tbl_purch_h d
where a.cno_retur=b.cno_retur and
      a.cnofaktur=d.cnofaktur and a.ctype='P' and
      c.supplier_id=d.ckode_supplier
group by a.cno_retur,a.cnofaktur,a.dtanggal,
         d.caccpurch,a.nfreight,a.ntax,
         b.ckode_produk,c.supp_name,c.supp_name,c.address ;

v_receivable (Done)


CREATE VIEW "V_RECEIVABLE" ("CNO_AR","DTANGGAL","CNOFAKTUR","NAMOUNT","CKODE_CUSTOMER","CNAMA","CALAMAT") AS 
select a.cno_ar,a.dtanggal,a.cnofaktur,
sum(decode(a.ctype,'D',b.ndebit,decode(a.ctype,'C',b.ncredit*-1,0))) namount,
c.ckode_customer,c.cnama,c.calamat
from tbl_ar_h a,tbl_ar_d b,tbl_customer c
where a.cno_ar=b.cno_ar and a.ckode_customer=c.ckode_customer
group by a.cno_ar,a.dtanggal,a.cnofaktur,a.ctype,c.ckode_customer,c.cnama,c.calamat

MySQL Version

CREATE OR REPLACE VIEW v_receivable (cno_ar,dtanggal,cnofaktur,namount,ckode_customer,cnama,calamat) AS 
select a.cno_ar,a.dtanggal,a.cnofaktur,
sum(IF(a.ctype='D',b.ndebit,IF(a.ctype='C',b.ncredit*-1,0))) namount,
c.ckode_customer,c.cnama,c.calamat
from tbl_ar_h a,tbl_ar_d b,tbl_customer c
where a.cno_ar=b.cno_ar and a.ckode_customer=c.ckode_customer
group by a.cno_ar,a.dtanggal,a.cnofaktur,a.ctype,c.ckode_customer,c.cnama,c.calamat ;

v_saldo_customer (Done)


CREATE VIEW "V_SALDO_CUSTOMER" ("CKODE_CUSTOMER","DTANGGAL","CNOFAKTUR","DDUE","NAMOUNT","CNAMA","CALAMAT") AS 
select a.ckode_customer,a.dtanggal,a.cnofaktur,a.ddue,a.namount,
b.cnama,b.calamat
from tbl_cust_saldo a,tbl_customer b
where a.ckode_customer=b.ckode_customer


MySQL Version

CREATE OR REPLACE VIEW v_saldo_customer (ckode_customer,dtanggal,cnofaktur,ddue,namount,cnama,calamat) AS 

select a.ckode_customer,a.dtanggal,a.cnofaktur,a.ddue,a.namount,
b.cnama,b.calamat
from tbl_cust_saldo a,tbl_customer b
where a.ckode_customer=b.ckode_customer ;

v_saldo_supplier (Done)

CREATE VIEW "V_SALDO_SUPPLIER" ("CKODE_SUPPLIER","DTANGGAL","CNOFAKTUR","DDUE","NAMOUNT","CNAMA","CALAMAT") AS 
select a.ckode_supplier,a.dtanggal,a.cnofaktur,a.ddue,a.namount,
b.cnama,b.calamat
from tbl_supp_saldo a,tbl_supplier b
where a.ckode_supplier=b.ckode_supplier

MySQL Version 


CREATE OR REPLACE VIEW v_saldo_supplier 
  (ckode_supplier,dtanggal,cnofaktur,ddue,namount,cnama,calamat) 
AS 
  SELECT a.ckode_supplier,a.dtanggal,a.cnofaktur,a.ddue,a.namount,
         b.supp_name,b.address
  FROM tbl_supp_saldo a,suppliers b
  WHERE a.ckode_supplier=b.supplier_id ;

v_sales (Done)


CREATE VIEW "V_SALES" ("CNOFAKTUR","DTANGGAL","CACCAR","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC","CKODE_CUSTOMER","CNAMA","CALAMAT") AS 
  select a.cnofaktur,a.dtanggal,a.caccar,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_customer,c.cnama,c.calamat
from tbl_sales_h a,tbl_sales_d b,tbl_customer c
where a.cnofaktur=b.cnofaktur and
      c.ckode_customer=a.ckode_customer
group by a.cnofaktur,a.dtanggal,a.caccar,a.nfreight,a.ntax,b.ckode_produk,c.ckode_customer,c.cnama,c.calamat

MySQL Version 


CREATE OR REPLACE VIEW v_sales (cnofaktur,dtanggal,caccar,nfreiggt,tax,ckode_produk,namount,ndisc,ckode_customer,cnama,calamat) AS 
  select a.cnofaktur,a.dtanggal,a.caccar,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_customer,c.cnama,c.calamat
from tbl_sales_h a,tbl_sales_d b,tbl_customer c
where a.cnofaktur=b.cnofaktur and
      c.ckode_customer=a.ckode_customer
group by a.cnofaktur,a.dtanggal,a.caccar,a.nfreight,a.ntax,b.ckode_produk,c.ckode_customer,c.cnama,c.calamat ;

v_sales_retur (Done)


CREATE VIEW "V_SALES_RETUR" ("CNO_RETUR","CNOFAKTUR","DTANGGAL","CACCAR","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC","CKODE_CUSTOMER","CNAMA","CALAMAT") AS 
select a.cno_retur,a.cnofaktur,a.dtanggal,d.caccar,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_customer,c.cnama,c.calamat
from tbl_retur_h a,tbl_retur_d b,tbl_customer c,tbl_sales_h d
where a.cno_retur=b.cno_retur and
      a.cnofaktur=d.cnofaktur and a.ctype='S' and
      c.ckode_customer=d.ckode_customer
group by a.cno_retur,a.cnofaktur,a.dtanggal,d.caccar,a.nfreight,a.ntax,b.ckode_produk,c.ckode_customer,c.cnama,c.calamat

MySQL Version 


CREATE OR REPLACE VIEW v_sales_retur (cno_retur,cnofaktur,dtanggal,caccar,nfreight,tax,ckode_produk,namount,ndisc,ckode_customer,cnama,calamat) AS 
select a.cno_retur,a.cnofaktur,a.dtanggal,d.caccar,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_customer,c.cnama,c.calamat
from tbl_retur_h a,tbl_retur_d b,tbl_customer c,tbl_sales_h d
where a.cno_retur=b.cno_retur and
      a.cnofaktur=d.cnofaktur and a.ctype='S' and
      c.ckode_customer=d.ckode_customer
group by a.cno_retur,a.cnofaktur,a.dtanggal,d.caccar,a.nfreight,a.ntax,b.ckode_produk,c.ckode_customer,c.cnama,c.calamat ;

test


CREATE VIEW "TEST" ("CNOFAKTUR","DTANGGAL","PRICE","CNAMA") AS
select a.cnofaktur,a.dtanggal,sum(b.nqty*b.nprice) Price,c.cnama
from tbl_sales_h a,tbl_sales_d b,tbl_customer c
where a.cnofaktur=b.cnofaktur and
      c.ckode_customer=a.ckode_customer
group by a.cnofaktur,a.dtanggal,c.cnama ;

vapjurnal


CREATE VIEW "VAPJURNAL" ("DTANGGAL","CKODE_COA","DEBIT","CREDIT") AS
SELECT A.DTANGGAL,B.CKODE_COA,SUM(B.NDEBIT) DEBIT,SUM(B.NCREDIT) CREDIT
FROM TBL_AP_H A,TBL_AP_D B
WHERE A.CNO_AP=B.CNO_AP
GROUP BY A.DTANGGAL,B.CKODE_COA

varjurnal


CREATE VIEW "VARJURNAL" ("DTANGGAL","CKODE_COA","DEBIT","CREDIT") AS
SELECT A.DTANGGAL,B.CKODE_COA,SUM(B.NDEBIT) DEBIT,SUM(B.NCREDIT) CREDIT
FROM TBL_AR_H A,TBL_AR_D B
WHERE A.CNO_AR=B.CNO_AR
GROUP BY A.DTANGGAL,B.CKODE_COA ;

vcashjurnal1


CREATE VIEW "VCASHJURNAL1" ("CREFFERENCE","CFROM","CKODE_COA","DTANGGAL","DEBIT","CREDIT") AS
select a.crefference,a.cfrom,a.ckode_coa,a.dtanggal,sum(b.ndebit) Debit, sum(b.ncredit) Credit
from tbl_cash_h a, tbl_cash_d b
where a.cno_receipt=b.cno_receipt and cfrom in ('C','D')
group by a.crefference,a.cfrom,a.ckode_coa,a.dtanggal ;


vcashjurnal1a


CREATE VIEW "VCASHJURNAL1A" ("CIFC_AR","CREFFERENCE","CKODE_COA","DTANGGAL","CREDIT") AS
SELECT D.CIFC_AR,C.CREFFERENCE,C.CKODE_COA,C.DTANGGAL,SUM(C.CREDIT) CREDIT
FROM TBL_CUSTOMER D,(
     select a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal, sum(b.ncredit-b.ndebit) Credit
     from tbl_cash_h a, tbl_cash_d b
     where a.cno_receipt=b.cno_receipt and cfrom = 'C'
     group by a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal) C
WHERE D.CKODE_CUSTOMER=C.CKODE_FROM
GROUP BY D.CIFC_AR,C.CREFFERENCE,C.CKODE_COA,C.DTANGGAL ;

vcashjurnal1b


CREATE VIEW "VCASHJURNAL1B" ("CIFC_AP","CREFFERENCE","CKODE_COA","DTANGGAL","DEBIT") AS
SELECT D.CIFC_Ap,C.CREFFERENCE,C.CKODE_COA,C.DTANGGAL,SUM(C.debit) DEBIT
FROM TBL_Supplier D,(
     select a.crefference,ckode_from,a.ckode_coa,a.dtanggal, sum(b.ndebit-b.ncredit) debit
     from tbl_cash_h a, tbl_cash_d b
     where a.cno_receipt=b.cno_receipt and cfrom = 'S'
     group by a.crefference,a.ckode_from,a.ckode_coa,a.dtanggal) C
WHERE D.CKODE_supplier=C.CKODE_FROM
GROUP BY D.CIFC_Ap,C.CREFFERENCE,C.Ckode_FROM,C.CKODE_COA,C.DTANGGAL ;

vcashjurnal2


CREATE VIEW "VCASHJURNAL2" ("CREFFERENCE","CTYPE","CFROM","CKODE_COA","DTANGGAL","CNOFAKTUR","DEBIT","CREDIT") AS
select a.crefference,a.ctype,a.cfrom,a.ckode_coa,a.dtanggal,b.cnofaktur,sum(b.ndebit) Debit,sum(b.ncredit) Credit
from tbl_cash_h a, tbl_cash_d b
where a.cno_receipt=b.cno_receipt and cfrom ='G'
group by a.crefference,a.ctype,a.cfrom,a.ckode_coa,a.dtanggal,b.cnofaktur ;

vdetail_gl


CREATE VIEW "VDETAIL_GL" ("CNOFAKTUR","CKODE_COA","ACCOUNT","CKETERANGAN","NDEBIT","NCREDIT","DTANGGAL","CDEPOT","CDEPT","COTHER","CTYPE") AS
SELECT ALL A.CNOFAKTUR,B.CKODE_COA, C.CKETERANGAN ACCOUNT, B.CKETERANGAN,B.NDEBIT, B.NCREDIT, A.DTANGGAL,
B.CDEPOT, B.CDEPT, B.COTHER, A.CTYPE
FROM TBL_GL_H A, TBL_GL_D B, CONSOLIDATED.TBL_COA C
WHERE ((A.CNOFAKTUR=B.CNOFAKTUR)
 AND (B.CKODE_COA=C.CKODE_COA)) ;

vinventoryadj


CREATE VIEW "VINVENTORYADJ" ("CNOFAKTUR","DTANGGAL","CKODE_PRODUK","NQTY") AS
select a.cnobukti cnofaktur,a.dtanggal,b.ckode_produk,b.nqty
from tbl_inv_adj_h a,tbl_inv_adj_d b
where a.cnobukti=b.cnobukti ;

vmutasiproduk


CREATE VIEW "VMUTASIPRODUK" ("CKODE_PRODUK","CTRANS","CNOFAKTUR","DTANGGAL","NMASUK","NKELUAR") AS
SELECT ckode_produk, ctrans, cnofaktur, dtanggal, nmasuk, nkeluar
FROM
(SELECT ckode_produk, 'BG' ctrans, cperiode cnofaktur, 
 add_months(to_date('01/'||substr(cperiode,5,2)||'/'||substr(cperiode,1,4),'dd/mm/yyyy'),1) dtanggal, nqty nmasuk, 0 nkeluar
 FROM tbl_produk_saldo
UNION
 SELECT pd.ckode_produk, 'PR' ctrans, ph.cnofaktur, ph.dtanggal, pd.nqty nmasuk, 0 nkeluar
 FROM tbl_purch_h ph, tbl_purch_d pd
 WHERE ph.cnofaktur=pd.cnofaktur
UNION
 SELECT kd.ckode_produk, 'KP' ctrans, kh.cnofaktur, kh.dtanggal, 0 nmasuk, kd.nqty nkeluar
 FROM tbl_kirim_h kh, tbl_kirim_d kd
 WHERE kh.cnofaktur=kd.cnofaktur
UNION
 SELECT kd.ckode_produk, 'RP' ctrans, kh.cnofaktur, kh.dtanggal, kd.nqty nmasuk, 0 nkeluar
 FROM tbl_retprod_h kh, tbl_retprod_d kd
 WHERE kh.cnofaktur=kd.cnofaktur
UNION
 SELECT kd.ckode_produk, 'AD' ctrans, kh.cnobukti, kh.dtanggal, 0 nmasuk, kd.nqty*-1 nkeluar
 FROM tbl_inv_adj_h kh, tbl_inv_adj_d kd
 WHERE kh.cnobukti=kd.cnobukti and kd.nqty < 0
UNION
 SELECT kd.ckode_produk, 'AD' ctrans, kh.cnobukti, kh.dtanggal, kd.nqty nmasuk, 0 nkeluar
 FROM tbl_inv_adj_h kh, tbl_inv_adj_d kd
 WHERE kh.cnobukti=kd.cnobukti and kd.nqty > 0
UNION
 SELECT td.ckode_produk, 'TP' ctrans, th.cnofaktur, th.dtanggal, td.nqty nmasuk, 0 nkeluar
 FROM tbl_terima_h th, tbl_terima_d td
 WHERE th.cnofaktur=td.cnofaktur
UNION
 SELECT td.ckode_produk, 'RS' ctrans, th.cno_retur, th.dtanggal, td.nqty nmasuk, 0 nkeluar
 FROM tbl_retur_h th, tbl_retur_d td
 WHERE th.cno_retur=td.cno_retur
UNION
 SELECT sd.ckode_produk, 'SL' ctrans, sh.cnofaktur, sh.dtanggal, 0 nmasuk, sd.nqty nkeluar
 FROM tbl_sales_h sh, tbl_sales_d sd
 WHERE sh.cnofaktur=sd.cnofaktur)
ORDER BY ckode_produk, dtanggal, ctrans, cnofaktur ;

MySQL Version 


CREATE OR REPLACE VIEW vmutasiproduk (ckode_produk,ctrans,cnofaktur,dtanggal,nmasuk,nkeluar) AS
SELECT ckode_produk, ctrans, cnofaktur, dtanggal, nmasuk, nkeluar
FROM
(SELECT ckode_produk, 'BG' ctrans, cperiode cnofaktur, 
 DATE_ADD(DATE_FORMAT(CONCAT('01-',substr(cperiode,5,2),'-',substr(cperiode,1,4)),'%d-%m-%y'),INTERVAL 1 MONTH) dtanggal, nqty nmasuk, 0 nkeluar
 FROM tbl_produk_saldo
UNION
 SELECT pd.ckode_produk, 'PR' ctrans, ph.cnofaktur, ph.dtanggal, pd.nqty nmasuk, 0 nkeluar
 FROM tbl_purch_h ph, tbl_purch_d pd
 WHERE ph.cnofaktur=pd.cnofaktur
UNION
 SELECT kd.ckode_produk, 'KP' ctrans, kh.cnofaktur, kh.dtanggal, 0 nmasuk, kd.nqty nkeluar
 FROM tbl_kirim_h kh, tbl_kirim_d kd
 WHERE kh.cnofaktur=kd.cnofaktur
UNION
 SELECT kd.ckode_produk, 'RP' ctrans, kh.cnofaktur, kh.dtanggal, kd.nqty nmasuk, 0 nkeluar
 FROM tbl_retprod_h kh, tbl_retprod_d kd
 WHERE kh.cnofaktur=kd.cnofaktur
UNION
 SELECT kd.ckode_produk, 'AD' ctrans, kh.cnobukti, kh.dtanggal, 0 nmasuk, kd.nqty*-1 nkeluar
 FROM tbl_inv_adj_h kh, tbl_inv_adj_d kd
 WHERE kh.cnobukti=kd.cnobukti and kd.nqty < 0
UNION
 SELECT kd.ckode_produk, 'AD' ctrans, kh.cnobukti, kh.dtanggal, kd.nqty nmasuk, 0 nkeluar
 FROM tbl_inv_adj_h kh, tbl_inv_adj_d kd
 WHERE kh.cnobukti=kd.cnobukti and kd.nqty > 0
UNION
 SELECT td.ckode_produk, 'TP' ctrans, th.cnofaktur, th.dtanggal, td.nqty nmasuk, 0 nkeluar
 FROM tbl_terima_h th, tbl_terima_d td
 WHERE th.cnofaktur=td.cnofaktur
UNION
 SELECT td.ckode_produk, 'RS' ctrans, th.cno_retur, th.dtanggal, td.nqty nmasuk, 0 nkeluar
 FROM tbl_retur_h th, tbl_retur_d td
 WHERE th.cno_retur=td.cno_retur
UNION
 SELECT sd.ckode_produk, 'SL' ctrans, sh.cnofaktur, sh.dtanggal, 0 nmasuk, sd.nqty nkeluar
 FROM tbl_sales_h sh, tbl_sales_d sd
 WHERE sh.cnofaktur=sd.cnofaktur
)
ORDER BY ckode_produk, dtanggal, ctrans, cnofaktur ;

vpurch_jurnal


CREATE VIEW "VPURCH_JURNAL" ("DTANGGAL","CDEPOT","CDEPT","COTHER","CACCPURCH","CACCINV","NFREIGHT","PRICE","DISC","TAX") AS
select a.dtanggal,a.cdepot,a.cdept,a.cother,a.caccpurch,c.caccinv,a.nfreight,
SUM(nvl(b.nqty,0)*nvl(b.nprice,0)) Price, sum(b.ndisc) Disc,
SUM((nvl(b.nqty,0)*nvl(b.nprice,0)-nvl(b.ndisc,0))*a.ntax/100) Tax
from tbl_purch_h a, tbl_purch_d b,tbl_produk c
where a.cnofaktur=b.cnofaktur and b.ckode_produk=c.ckode_produk
group by a.dtanggal,a.nfreight,a.cdepot,a.cdept,a.cother,c.caccinv,a.caccpurch ;

vsales


CREATE VIEW "VSALES" ("CNOFAKTUR","DTANGGAL","CKODE_PRODUK","NQTY") AS
select a.cnofaktur,a.dtanggal,b.ckode_produk,b.nqty
from tbl_sales_h a,tbl_sales_d b
where a.cnofaktur=b.cnofaktur ;

vsalesjurnal


CREATE VIEW "VSALESJURNAL" ("DTANGGAL","CACCAR","CACCSALES","CACCINV","CACCCOST","NFREIGHT","PRICE","TAX","DISC","HPP") AS
SELECT A.DTANGGAL,A.CACCAR,D.CACCSALES,D.CACCINV,D.CACCCOST, A.NFREIGHT,SUM(NVL(B.NQTY,0)*NVL(B.NPRICE,0)) PRICE,
           SUM(NVL(B.NQTY,0)*NVL(B.NPRICE,0)*A.NTAX/100) TAX, SUM(B.NDISC) DISC,
           SUM(NVL(C.NHPP,0)*NVL(C.NOUT,0)) HPP
           FROM TBL_SALES_H A,TBL_SALES_D B,TBL_FIFO C,TBL_PRODUK D
           WHERE A.CNOFAKTUR=B.CNOFAKTUR AND A.CNOFAKTUR=C.CNO_OUT AND B.CKODE_PRODUK=D.CKODE_PRODUK
           GROUP BY A.DTANGGAL,A.NFREIGHT,D.CACCSALES,D.CACCINV,D.CACCCOST,A.CACCAR ;

v_ap


CREATE VIEW "V_AP" ("CTRANS","CNOFAKTUR","CNO_PAYMENT","DTANGGAL","CKODE_SUPPLIER","NPURCHASE","NCREDIT","NPAYMENT","NDEBIT") AS
SELECT ctrans, cnofaktur, cno_payment, dtanggal, ckode_supplier, npurchase, ncredit, npayment, ndebit
FROM
(SELECT 'BG' ctrans, cnofaktur, '' cno_payment, max(dtanggal) dtanggal, ckode_supplier, 0 npurchase, namount ncredit, 0 npayment, 0 ndebit
 FROM tbl_supp_saldo
 GROUP BY cnofaktur, ckode_supplier, namount
UNION
 SELECT 'PR' ctrans, ph.cnofaktur, '' cno_payment, ph.dtanggal, 
 ph.ckode_supplier, (pd.ncredit*(1+(NVL(ph.ntax,0)/100)))+NVL(ph.nfreight,0) npurchase, 0 ncredit, 0 npayment, 0 ndebit
 FROM tbl_purch_h ph,
     (SELECT ph.cnofaktur, sum((NVL(pd.nqty,0)*NVL(pd.nprice,0))-NVL(pd.ndisc,0)) ncredit
      FROM tbl_purch_h ph, tbl_purch_d pd
      WHERE ph.cnofaktur=pd.cnofaktur
      GROUP BY ph.cnofaktur) pd
 WHERE ph.cnofaktur=pd.cnofaktur
UNION
 SELECT 'PY' ctrans, pd.cnofaktur, 
 ph.cno_receipt cno_payment, ph.dtanggal, 
 ph.ckode_from  ckode_supplier, 0 npurchase, 0 ncredit, NVL(SUM(pd.ndebit-pd.ncredit),0) npayment, 0 ndebit
 FROM tbl_cash_h ph, tbl_cash_d pd
 WHERE ph.cno_receipt=pd.cno_receipt and
       ph.cfrom='S'
 GROUP by pd.cnofaktur, ph.cno_receipt, ph.dtanggal, ph.ckode_from
UNION
 SELECT 'AP' ctrans, ah.cnofaktur, '' cno_payment, 
 ah.dtanggal, ah.ckode_supplier, 0 npurchase, 
 sum(decode(ah.ctype,'C',ad.ncredit,decode(ah.ctype,'D',ad.ndebit*-1,0))) ncredit, 0 npayment, 0 ndebit
 FROM tbl_ap_h ah, tbl_ap_d ad
 WHERE ah.cno_ap=ad.cno_ap
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_supplier)
ORDER BY ckode_supplier, cnofaktur, dtanggal, cno_payment ;

v_ar


CREATE VIEW "V_AR" ("CTRANS","CNOFAKTUR","CNO_RECEIPT","DTANGGAL","CKODE_CUSTOMER","NSALES","NDEBIT","NRECEIPT","NCREDIT") AS
SELECT ctrans, cnofaktur, cno_receipt, dtanggal, ckode_customer, nsales, ndebit, nreceipt, ncredit
FROM
(SELECT 'BG' ctrans, cnofaktur, '' cno_receipt, max(dtanggal) dtanggal, ckode_customer, 0 nsales, namount ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_cust_saldo
 GROUP BY cnofaktur, ckode_customer, namount
UNION
 SELECT 'SL' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, 
 sh.ckode_customer, NVL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales, 0 ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_sales_h sh,
      (SELECT sh.cnofaktur, NVL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit
       FROM tbl_sales_h sh,
            tbl_sales_d sd
       WHERE sh.cnofaktur=sd.cnofaktur
       GROUP BY sh.cnofaktur) sd
 WHERE sh.cnofaktur=sd.cnofaktur
 GROUP BY sh.cnofaktur, sh.dtanggal, sh.ckode_customer
UNION
 SELECT 'SR' ctrans, sh.cnofaktur, '' cno_receipt, sh.dtanggal, 
 b.ckode_customer, NVL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales, 0 ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_retur_h sh,tbl_sales_h b,
      (SELECT sh.cnofaktur, NVL(SUM((-1*sd.nqty*sd.nprice)-sd.ndisc),0) ndebit
       FROM tbl_retur_h sh,
            tbl_retur_d sd
       WHERE sh.cno_retur=sd.cno_retur and ctype='S'
       GROUP BY sh.cnofaktur) sd
 WHERE sh.cnofaktur=sd.cnofaktur and b.cnofaktur=sh.cnofaktur
 GROUP BY sh.cnofaktur, sh.dtanggal, b.ckode_customer
UNION
 SELECT 'RC' ctrans, rd.cnofaktur, rh.cno_receipt, rh.dtanggal, 
 rh.ckode_from ckode_customer, 0 nsales, 0 ndebit, NVL(SUM(rd.ncredit-rd.ndebit),0) nreceipt, 0 ncredit
 FROM tbl_cash_h rh,
      tbl_cash_d rd
 WHERE rh.cno_receipt=rd.cno_receipt AND
       rh.cfrom='C'
 GROUP BY rd.cnofaktur, rh.cno_receipt, rh.dtanggal, rh.ckode_from
UNION
 SELECT 'AR' ctrans, ah.cnofaktur, '' cno_receipt, ah.dtanggal ,
 ah.ckode_customer, 0 nsales, sum(decode(ah.ctype,'D',ad.ndebit,decode(ah.ctype,'C',ad.ncredit*-1,0))) ndebit, 0 nreceipt, 0 ncredit
 FROM tbl_ar_h ah, tbl_ar_d ad
 WHERE ah.cno_ar=ad.cno_ar
 GROUP BY ah.cnofaktur, ah.dtanggal, ah.ckode_customer)
ORDER BY ckode_customer, cnofaktur, dtanggal, cno_receipt ;

v_cash_customer


CREATE VIEW "V_CASH_CUSTOMER" ("CNO_RECEIPT","CREFFERENCE","DTANGGAL","NAMOUNT","CNOFAKTUR","CKODE_CUSTOMER","CNAMA","CALAMAT") AS
j^Aselect a.cno_receipt,a.crefference,a.dtanggal,sum(b.ncredit-b.ndebit) namount,b.cnofaktur,
c.ckode_customer,c.cnama,c.calamat
from tbl_cash_h a,tbl_cash_d b,tbl_customer c
where a.cno_receipt=b.cno_receipt and
      a.cfrom='C' and
      a.ckode_from=c.ckode_customer
group by a.cno_receipt,a.crefference,a.dtanggal,b.cnofaktur,c.ckode_customer,c.cnama,c.calamat ;


v_cash_supplier


CREATE VIEW "V_CASH_SUPPLIER" ("CNO_RECEIPT","CREFFERENCE","DTANGGAL","NAMOUNT","CNOFAKTUR","CKODE_SUPPLIER","CNAMA","CALAMAT") AS
jselect a.cno_receipt,a.crefference,a.dtanggal,sum(b.ndebit-b.ncredit) namount,b.cnofaktur,
c.ckode_supplier,c.cnama,c.calamat
from tbl_cash_h a,tbl_cash_d b,tbl_supplier c
where a.cno_receipt=b.cno_receipt and
      a.cfrom='S' and
      a.ckode_from=c.ckode_supplier
group by 
a.cno_receipt,a.crefference,a.dtanggal,b.cnofaktur,c.ckode_supplier,c.cnama,c.calamat;

v_payable


CREATE VIEW "V_PAYABLE" ("CNO_AP","DTANGGAL","CNOFAKTUR","NAMOUNT","CKODE_SUPPLIER","CNAMA","CALAMAT") AS
select a.cno_ap,a.dtanggal,a.cnofaktur,
sum(decode(a.ctype,'C',b.ncredit,decode(a.ctype,'D',b.ndebit*-1,0))) namount,
c.ckode_supplier,c.cnama,c.calamat
from tbl_ap_h a,tbl_ap_d b,tbl_supplier c
where a.cno_ap=b.cno_ap and a.ckode_supplier=c.ckode_supplier
group by a.cno_ap,a.dtanggal,a.cnofaktur,a.ctype,c.ckode_supplier,c.cnama,c.calamat ;


v_purchase


CREATE VIEW "V_PURCHASE" ("CNOFAKTUR","DTANGGAL","CACCPURCH","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC","CKODE_SUPPLIER","CNAMA","CALAMAT") AS
select a.cnofaktur,a.dtanggal,a.caccpurch,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_supplier,c.cnama,c.calamat
from tbl_purch_h a,tbl_purch_d b,tbl_supplier c
where a.cnofaktur=b.cnofaktur and
      c.ckode_supplier=a.ckode_supplier
group by a.cnofaktur,a.dtanggal,a.caccpurch,a.nfreight,a.ntax,b.ckode_produk,c.ckode_supplier,c.cnama,c.calamat ;

v_purchase_retur


CREATE VIEW "V_PURCHASE_RETUR" ("CNO_RETUR","CNOFAKTUR","DTANGGAL","CACCPURCH","NFREIGHT","TAX",
"CKODE_PRODUK","NAMOUNT","NDISC","CKODE_SUPPLIER","CNAMA","CALAMAT") AS
select a.cno_retur,a.cnofaktur,a.dtanggal,d.caccpurch,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_supplier,c.cnama,c.calamat
from tbl_retur_h a,tbl_retur_d b,tbl_supplier c,tbl_purch_h d
where a.cno_retur=b.cno_retur and
      a.cnofaktur=d.cnofaktur and a.ctype='P' and
      c.ckode_supplier=d.ckode_supplier
group by a.cno_retur,a.cnofaktur,a.dtanggal,d.caccpurch,a.nfreight,a.ntax,b.ckode_produk,c.ckode_supplier,c.cnama,c.calamat ;

v_saldo_customer


CREATE VIEW "V_SALDO_CUSTOMER" ("CKODE_CUSTOMER","DTANGGAL","CNOFAKTUR","DDUE","NAMOUNT","CNAMA","CALAMAT") AS
select a.ckode_customer,a.dtanggal,a.cnofaktur,a.ddue,a.namount,
b.cnama,b.calamat
from tbl_cust_saldo a,tbl_customer b
where a.ckode_customer=b.ckode_customer ;

v_saldo_supplier


CREATE VIEW "V_SALDO_SUPPLIER" ("CKODE_SUPPLIER","DTANGGAL","CNOFAKTUR","DDUE","NAMOUNT","CNAMA","CALAMAT") AS
select a.ckode_supplier,a.dtanggal,a.cnofaktur,a.ddue,a.namount,
b.cnama,b.calamat
from tbl_supp_saldo a,tbl_supplier b
where a.ckode_supplier=b.ckode_supplier ;

v_sales


CREATE VIEW "V_SALES" ("CNOFAKTUR","DTANGGAL","CACCAR","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC","CKODE_CUSTOMER","CNAMA","CALAMAT") AS
select a.cnofaktur,a.dtanggal,a.caccar,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_customer,c.cnama,c.calamat
from tbl_sales_h a,tbl_sales_d b,tbl_customer c
where a.cnofaktur=b.cnofaktur and
      c.ckode_customer=a.ckode_customer
group by 
a.cnofaktur,a.dtanggal,a.caccar,a.nfreight,a.ntax,b.ckode_produk,c.ckode_customer,c.cnama,c.calamat;

v_sales_retur


CREATE VIEW "V_SALES_RETUR" ("CNO_RETUR","CNOFAKTUR","DTANGGAL","CACCAR","NFREIGHT","TAX","CKODE_PRODUK","NAMOUNT","NDISC",
"CKODE_CUSTOMER","CNAMA","CALAMAT") AS
select a.cno_retur,a.cnofaktur,a.dtanggal,d.caccar,a.nfreight,sum(b.nqty*b.nprice)*a.ntax/100 Tax,b.ckode_produk,
sum(b.nqty*b.nprice) namount,sum(b.ndisc) ndisc,
c.ckode_customer,c.cnama,c.calamat
from tbl_retur_h a,tbl_retur_d b,tbl_customer c,tbl_sales_h d
where a.cno_retur=b.cno_retur and
      a.cnofaktur=d.cnofaktur and a.ctype='S' and
      c.ckode_customer=d.ckode_customer
group by 
a.cno_retur,a.cnofaktur,a.dtanggal,d.caccar,a.nfreight,a.ntax,b.ckode_produk,c.ckode_customer,c.cnama,c.calamat;













Comments