CREATE TABLE [tb_ap_adjustap] (
	[apprecord] [varchar] (10) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[sendno_ref] [varchar] (10) NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[taxno] [varchar] (20) NULL ,
	[taxdate] [datetime] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[product_type] [int] NULL ,
	[taxtype] [int] NULL ,
	[taxtype_percent] [decimal](18, 2) NULL ,
	[taxtype_value] [decimal](18, 2) NULL ,
	[vattype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[doc_effact] [varchar] (1) NULL ,
	[sys_vou] [varchar] (10) NULL ,
	[user_vou] [varchar] (10) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_beginterm] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[taxno] [varchar] (20) NULL ,
	[credit_term] [int] NULL ,
	[due_date] [datetime] NULL ,
	[amount] [decimal](18, 2) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_bill] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[duedate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[use_yn] [varchar] (1) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_bill_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[docdate_ref] [datetime] NULL ,
	[duedate_ref] [datetime] NULL ,
	[description_ref] [varchar] (200) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[remain_pay] [decimal](18, 2) NULL ,
	[holdbill] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_createap] (
	[apprecord] [varchar] (10) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[sendno] [varchar] (10) NULL ,
	[taxno] [varchar] (20) NULL ,
	[taxdate] [datetime] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[product_type] [int] NULL ,
	[taxtype] [int] NULL ,
	[taxtype_percent] [decimal](18, 2) NULL ,
	[taxtype_value] [decimal](18, 2) NULL ,
	[vattype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[doc_effact] [varchar] (1) NULL ,
	[sys_vou] [varchar] (10) NULL ,
	[user_vou] [varchar] (10) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_payment] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[holdbill] [varchar] (10) NULL ,
	[product_type] [int] NULL ,
	[taxtype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[grandtotal] [decimal](18, 2) NULL ,
	[pay_chash] [decimal](18, 2) NULL ,
	[pay_cheque] [decimal](18, 2) NULL ,
	[pay_transfer] [decimal](18, 2) NULL ,
	[pay_other] [decimal](18, 2) NULL ,
	[pay_psd] [decimal](18, 2) NULL ,
	[Pay_Convention] [varchar] (1) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_payment_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[docdate_ref] [datetime] NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[remain_pay] [decimal](18, 2) NULL ,
	[payment] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ap_person] (
	[ID] [varchar] (10) NOT NULL ,
	[PrefixID] [varchar] (5) NULL ,
	[Name] [varchar] (150) NULL ,
	[Address] [varchar] (100) NULL ,
	[Moo] [varchar] (50) NULL ,
	[Soi] [varchar] (100) NULL ,
	[Road] [varchar] (150) NULL ,
	[District] [varchar] (100) NULL ,
	[Zone] [varchar] (100) NULL ,
	[Province] [varchar] (100) NULL ,
	[PostCode] [varchar] (5) NULL ,
	[Telephone] [varchar] (150) NULL ,
	[FAX] [varchar] (150) NULL ,
	[Country] [varchar] (100) NULL ,
	[TumbonType] [int] NULL ,
	[AumperType] [int] NULL ,
	[email] [varchar] (150) NULL ,
	[contactname] [varchar] (200) NULL ,
	[tax_no] [varchar] (20) NULL ,
	[tax_status] [varchar] (1) NULL ,
	[send_address1] [varchar] (150) NULL ,
	[send_address2] [varchar] (150) NULL ,
	[send_address3] [varchar] (150) NULL ,
	[comment] [varchar] (150) NULL ,
	[acc_no] [varchar] (10) NULL ,
	[credit_money] [money] NULL ,
	[credit_term] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_adjustar] (
	[apprecord] [varchar] (10) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[sendno_ref] [varchar] (10) NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[taxno] [varchar] (20) NULL ,
	[taxdate] [datetime] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[product_type] [int] NULL ,
	[taxtype] [int] NULL ,
	[taxtype_percent] [decimal](18, 2) NULL ,
	[taxtype_value] [decimal](18, 2) NULL ,
	[vattype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[doc_effact] [varchar] (1) NULL ,
	[sys_vou] [varchar] (10) NULL ,
	[user_vou] [varchar] (10) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_beginterm] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[taxno] [varchar] (20) NULL ,
	[credit_term] [int] NULL ,
	[due_date] [datetime] NULL ,
	[amount] [money] NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_bill] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[duedate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[use_yn] [varchar] (1) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_bill_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[docdate_ref] [datetime] NULL ,
	[duedate_ref] [datetime] NULL ,
	[description_ref] [varchar] (200) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[remain_pay] [decimal](18, 2) NULL ,
	[holdbill] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_createar] (
	[apprecord] [varchar] (10) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[sendno] [varchar] (10) NULL ,
	[taxno] [varchar] (20) NULL ,
	[taxdate] [datetime] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[product_type] [int] NULL ,
	[taxtype] [int] NULL ,
	[taxtype_percent] [decimal](18, 2) NULL ,
	[taxtype_value] [decimal](18, 2) NULL ,
	[vattype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[voucherid] [varchar] (10) NULL ,
	[doc_effact] [varchar] (1) NULL ,
	[sys_vou] [varchar] (10) NULL ,
	[user_vou] [varchar] (10) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_person] (
	[ID] [varchar] (10) NOT NULL ,
	[PrefixID] [varchar] (5) NULL ,
	[Name] [varchar] (150) NULL ,
	[Address] [varchar] (100) NULL ,
	[Moo] [varchar] (50) NULL ,
	[Soi] [varchar] (100) NULL ,
	[Road] [varchar] (150) NULL ,
	[District] [varchar] (100) NULL ,
	[Zone] [varchar] (100) NULL ,
	[Province] [varchar] (100) NULL ,
	[PostCode] [varchar] (5) NULL ,
	[Telephone] [varchar] (150) NULL ,
	[FAX] [varchar] (150) NULL ,
	[Country] [varchar] (100) NULL ,
	[TumbonType] [int] NULL ,
	[AumperType] [int] NULL ,
	[email] [varchar] (150) NULL ,
	[contactname] [varchar] (200) NULL ,
	[tax_no] [varchar] (20) NULL ,
	[tax_status] [varchar] (1) NULL ,
	[send_address1] [varchar] (150) NULL ,
	[send_address2] [varchar] (150) NULL ,
	[send_address3] [varchar] (150) NULL ,
	[comment] [varchar] (150) NULL ,
	[acc_no] [varchar] (10) NULL ,
	[credit_money] [money] NULL ,
	[credit_term] [int] NULL ,
	[pricesale_level] [int] NULL ,
	[black_list] [char] (1) NULL ,
	[discount_FFA] [money] NULL ,
	[discount_FFD] [money] NULL ,
	[discount_NSD] [money] NULL ,
	[discount_REDATE] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_receipt] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[holdbill] [varchar] (10) NULL ,
	[product_type] [int] NULL ,
	[taxtype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[grandtotal] [decimal](18, 2) NULL ,
	[pay_chash] [decimal](18, 2) NULL ,
	[pay_cheque] [decimal](18, 2) NULL ,
	[pay_transfer] [decimal](18, 2) NULL ,
	[pay_other] [decimal](18, 2) NULL ,
	[pay_psd] [decimal](18, 2) NULL ,
	[Pay_Convention] [varchar] (1) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ar_receipt_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[docdate_ref] [datetime] NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[remain_pay] [decimal](18, 2) NULL ,
	[payment] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_chequeresult] (
	[docno] [varchar] (10) NOT NULL ,
	[doctype] [varchar] (5) NOT NULL ,
	[docdate] [datetime] NULL ,
	[acc_id] [varchar] (20) NULL ,
	[description] [varchar] (200) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_chequeresult_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[doctype] [varchar] (5) NOT NULL ,
	[chqno] [varchar] (20) NOT NULL ,
	[bankid] [varchar] (5) NULL ,
	[chqdate] [datetime] NULL ,
	[amount] [money] NULL ,
	[feeamount] [money] NULL ,
	[netamount] [money] NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_chequetrans] (
	[apprecord] [varchar] (10) NULL ,
	[docno] [varchar] (10) NULL ,
	[docdate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[acc_id] [varchar] (20) NULL ,
	[acc_type] [varchar] (5) NULL ,
	[bankid] [varchar] (5) NOT NULL ,
	[bankbranchid] [varchar] (5) NULL ,
	[chqno] [varchar] (20) NOT NULL ,
	[chqdate] [datetime] NULL ,
	[chqstatus] [varchar] (1) NULL ,
	[amount] [money] NULL ,
	[description] [varchar] (200) NULL ,
	[paydesc] [varchar] (200) NULL ,
	[inout] [varchar] (1) NOT NULL ,
	[depositchq] [varchar] (1) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_depositchq] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[acc_id] [varchar] (20) NULL ,
	[description] [varchar] (200) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_depositchq_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[chqno] [varchar] (20) NOT NULL ,
	[bankid] [varchar] (5) NULL ,
	[chqdate] [datetime] NULL ,
	[amount] [money] NULL ,
	[feeamount] [money] NULL ,
	[netamount] [money] NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_depositdraw] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[acc_id] [varchar] (20) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[description] [varchar] (200) NULL ,
	[flagstatus] [varchar] (1) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_incomeexpense] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[acc_id] [varchar] (20) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[amountpsd] [decimal](18, 2) NULL ,
	[description] [varchar] (200) NULL ,
	[flagstatus] [varchar] (1) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_chq_transfer] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[description] [varchar] (200) NULL ,
	[acc_id_out] [varchar] (20) NULL ,
	[acc_id_in] [varchar] (20) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[fee_account] [decimal](18, 2) NULL ,
	[fee_cash] [decimal](18, 2) NULL ,
	[chqno] [varchar] (20) NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_accchart] (
	[branchid] [varchar] (10) NOT NULL ,
	[accno] [varchar] (15) NOT NULL ,
	[accname] [varchar] (150) NULL ,
	[typeid] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_book] (
	[bookid] [varchar] (5) NOT NULL ,
	[bookname] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_expense] (
	[apprecord] [varchar] (10) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[taxdate] [datetime] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[taxtype] [int] NULL ,
	[taxtype_percent] [decimal](18, 2) NULL ,
	[taxtype_value] [decimal](18, 2) NULL ,
	[vattype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[tempid] [varchar] (10) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_income] (
	[apprecord] [varchar] (10) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[taxdate] [datetime] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[taxtype] [int] NULL ,
	[taxtype_percent] [decimal](18, 2) NULL ,
	[taxtype_value] [decimal](18, 2) NULL ,
	[vattype] [int] NULL ,
	[total] [decimal](18, 2) NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[tempid] [varchar] (10) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_pattern_voucher] (
	[patternid] [varchar] (10) NOT NULL ,
	[patternname] [varchar] (200) NULL ,
	[branchid] [varchar] (5) NULL ,
	[bookid] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_pattern_voucher_sub] (
	[patternid] [varchar] (10) NOT NULL ,
	[rowitem] [int] NOT NULL ,
	[row_desc] [varchar] (200) NULL ,
	[accno] [varchar] (10) NULL ,
	[drcr] [varchar] (1) NULL ,
	[filltype] [varchar] (1) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_template] (
	[tempid] [varchar] (10) NOT NULL ,
	[tempname] [varchar] (200) NULL ,
	[branchid] [varchar] (10) NULL ,
	[bookid] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_template_sub] (
	[tempid] [varchar] (10) NOT NULL ,
	[accno] [varchar] (15) NULL ,
	[accname] [varchar] (150) NULL ,
	[drcr] [varchar] (2) NULL ,
	[varid] [varchar] (5) NULL ,
	[rowitem] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_trans2apt] (
	[branchid] [varchar] (6) NULL ,
	[docdate] [datetime] NULL ,
	[bookid] [varchar] (2) NOT NULL ,
	[docno] [varchar] (20) NOT NULL ,
	[description] [varchar] (255) NULL ,
	[TotalBalance] [money] NULL ,
	[Post_YN] [tinyint] NULL ,
	[RecDate] [datetime] NULL ,
	[CommitPost] [tinyint] NULL ,
	[docno_ref] [varchar] (20) NOT NULL ,
	[app_rec] [varchar] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_transsub2apt] (
	[branchid] [varchar] (6) NULL ,
	[bookid] [varchar] (2) NOT NULL ,
	[docno] [varchar] (20) NOT NULL ,
	[accno] [varchar] (15) NULL ,
	[accname] [varchar] (150) NULL ,
	[Amount] [decimal](18, 2) NULL ,
	[docdate] [datetime] NULL ,
	[drcr] [tinyint] NULL ,
	[rowitem] [smallint] NOT NULL ,
	[docno_ref] [varchar] (20) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_gl_variable] (
	[varid] [varchar] (5) NOT NULL ,
	[varname] [varchar] (150) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_adjuststock] (
	[doctype] [varchar] (1) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[description] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_adjuststock_sub] (
	[doctype] [varchar] (1) NOT NULL ,
	[docno] [varchar] (10) NOT NULL ,
	[ProductID] [varchar] (20) NULL ,
	[ProductName] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[costperunit] [decimal](18, 2) NULL ,
	[total] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_beginterm] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[description] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_beginterm_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[ProductID] [varchar] (20) NULL ,
	[ProductName] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[costperunit] [decimal](18, 2) NULL ,
	[total] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_calstock] (
	[PROID] [varchar] (20) NOT NULL ,
	[ITEMNO] [numeric](18, 0) NOT NULL ,
	[QTY] [numeric](18, 5) NULL ,
	[COST] [numeric](18, 5) NULL ,
	[AMOUNT] [numeric](18, 5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_countstock] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[empcount] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_countstock_doc] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[description] [varchar] (200) NULL ,
	[productid_b] [varchar] (20) NULL ,
	[productid_n] [varchar] (20) NULL ,
	[storeid_b] [varchar] (5) NULL ,
	[storeid_n] [varchar] (5) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_countstock_doc_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NULL ,
	[productname] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_countstock_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NULL ,
	[productname] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[countst] [decimal](18, 2) NULL ,
	[diffst] [decimal](18, 2) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_location] (
	[LocationID] [varchar] (5) NOT NULL ,
	[LocationName] [varchar] (100) NULL ,
	[Description] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_moreunit] (
	[ProductID] [varchar] (20) NOT NULL ,
	[unitid] [varchar] (5) NOT NULL ,
	[ratio] [decimal](18, 2) NULL ,
	[ratio_level] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_product] (
	[ProductID] [varchar] (20) NOT NULL ,
	[ProductName] [varchar] (200) NULL ,
	[acc_dr] [varchar] (10) NULL ,
	[acc_cr] [varchar] (10) NULL ,
	[GroupID] [varchar] (5) NULL ,
	[Description] [varchar] (200) NULL ,
	[PictureName] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[default_unit] [varchar] (5) NULL ,
	[many_unit_yn] [varchar] (1) NULL ,
	[WeightPack] [decimal](18, 2) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_product_detail] (
	[ProductID] [varchar] (20) NOT NULL ,
	[pur_min_qty] [decimal](18, 2) NULL ,
	[pur_max_qty] [decimal](18, 2) NULL ,
	[pur_unit] [varchar] (5) NULL ,
	[sale_price_level1] [decimal](18, 2) NULL ,
	[sale_price_level2] [decimal](18, 2) NULL ,
	[sale_price_level3] [decimal](18, 2) NULL ,
	[sale_unit] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_productgroup] (
	[GroupID] [varchar] (5) NOT NULL ,
	[GroupName] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_remain] (
	[doctype] [varchar] (5) NULL ,
	[flagstatus] [varchar] (1) NULL ,
	[productid] [varchar] (20) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_stockcard] (
	[ITEMNO] [numeric](18, 0) NOT NULL ,
	[DOCNO] [varchar] (10) NOT NULL ,
	[DOCDATE] [datetime] NULL ,
	[IN_QTY] [numeric](18, 5) NULL ,
	[IN_COST] [numeric](18, 5) NULL ,
	[IN_AMOUNT] [numeric](18, 5) NULL ,
	[BAL_QTY] [numeric](18, 5) NULL ,
	[BAL_COST] [numeric](18, 5) NULL ,
	[BAL_AMOUNT] [numeric](18, 5) NULL ,
	[OUT_QTY] [numeric](18, 5) NULL ,
	[OUT_COST] [numeric](18, 5) NULL ,
	[OUT_AMOUNT] [numeric](18, 5) NULL ,
	[PROID] [varchar] (20) NULL ,
	[UNITID] [varchar] (5) NULL ,
	[STOREID] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_store] (
	[storeid] [varchar] (5) NOT NULL ,
	[storename] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_trans] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[empid] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[flagdraw] [varchar] (1) NOT NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_trans_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[ProductID] [varchar] (20) NULL ,
	[ProductName] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[costperunit] [decimal](18, 2) NULL ,
	[total] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL ,
	[flagdraw] [varchar] (1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_transdraw] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[empid] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[flagdraw] [varchar] (1) NOT NULL ,
	[docno_ref] [varchar] (10) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_transdraw_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[ProductID] [varchar] (20) NULL ,
	[ProductName] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[costperunit] [decimal](18, 2) NULL ,
	[total] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL ,
	[flagdraw] [varchar] (1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_transfer] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[description] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_transfer_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[ProductID] [varchar] (20) NULL ,
	[ProductName] [varchar] (200) NULL ,
	[unitid] [varchar] (5) NULL ,
	[qty] [decimal](18, 2) NULL ,
	[stockout] [varchar] (5) NULL ,
	[stockin] [varchar] (5) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_unit] (
	[unitid] [varchar] (5) NOT NULL ,
	[unitname] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_warehouse] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[effactstock] [varchar] (1) NULL ,
	[doctype] [varchar] (10) NOT NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_ic_warehouse_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[ProductID] [varchar] (20) NULL ,
	[ProductName] [varchar] (200) NULL ,
	[storeid] [varchar] (5) NULL ,
	[unitid] [varchar] (5) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[costperunit] [decimal](18, 2) NULL ,
	[totalamount] [decimal](18, 2) NULL ,
	[rowitem] [int] NOT NULL ,
	[doctype] [varchar] (10) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_cash] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[docno_po] [varchar] (10) NULL ,
	[personid] [varchar] (10) NULL ,
	[transportid] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[taxtype] [int] NULL ,
	[vatrate] [decimal](2, 0) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[pay_chash] [decimal](18, 2) NULL ,
	[pay_cheque] [decimal](18, 2) NULL ,
	[pay_transfer] [decimal](18, 2) NULL ,
	[pay_other] [decimal](18, 2) NULL ,
	[pay_psd] [decimal](18, 2) NULL ,
	[Pay_Convention] [varchar] (1) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_cash_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_debit] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[docno_po] [varchar] (10) NULL ,
	[personid] [varchar] (10) NULL ,
	[transportid] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](2, 0) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_debit_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_inc] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[taxdate_ref] [datetime] NULL ,
	[taxvalue_ref] [decimal](18, 2) NULL ,
	[taxno] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[effactstock] [varchar] (1) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_inc_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_return] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[taxdate_ref] [datetime] NULL ,
	[taxvalue_ref] [decimal](18, 2) NULL ,
	[taxno] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[effactstock] [varchar] (1) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_return_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_sheet] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[personid] [varchar] (10) NULL ,
	[senddate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](2, 0) NULL ,
	[description] [varchar] (200) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[use_yn] [varchar] (50) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_pur_purchase_sheet_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_cancel] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[taxno] [varchar] (20) NULL ,
	[docno_po] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](2, 0) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[pay_chash] [decimal](18, 2) NULL ,
	[pay_cheque] [decimal](18, 2) NULL ,
	[pay_transfer] [decimal](18, 2) NULL ,
	[pay_other] [decimal](18, 2) NULL ,
	[pay_psd] [decimal](18, 2) NULL ,
	[FFA_Percent] [decimal](18, 2) NULL ,
	[FFD_Percent] [decimal](18, 2) NULL ,
	[NSD_Percent] [decimal](18, 2) NULL ,
	[REDATE_Percent] [decimal](18, 2) NULL ,
	[discount_FFA] [decimal](18, 2) NULL ,
	[discount_FFD] [decimal](18, 2) NULL ,
	[discount_NSD] [decimal](18, 2) NULL ,
	[discount_REDATE] [decimal](18, 2) NULL ,
	[transportid] [varchar] (5) NULL ,
	[transport_unitid] [varchar] (5) NULL ,
	[transport_rate] [decimal](18, 2) NULL ,
	[transport_qty] [decimal](18, 2) NULL ,
	[transport_value] [decimal](18, 2) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL ,
	[vendercode] [varchar] (20) NULL ,
	[cancel_remark] [varchar] (300) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_cancel_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_salecash] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno] [varchar] (20) NULL ,
	[docno_po] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](2, 0) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[pay_chash] [decimal](18, 2) NULL ,
	[pay_cheque] [decimal](18, 2) NULL ,
	[pay_transfer] [decimal](18, 2) NULL ,
	[pay_other] [decimal](18, 2) NULL ,
	[pay_psd] [decimal](18, 2) NULL ,
	[Pay_Convention] [varchar] (1) NULL ,
	[FFA_Percent] [decimal](18, 2) NULL ,
	[FFD_Percent] [decimal](18, 2) NULL ,
	[NSD_Percent] [decimal](18, 2) NULL ,
	[REDATE_Percent] [decimal](18, 2) NULL ,
	[discount_FFA] [decimal](18, 2) NULL ,
	[discount_FFD] [decimal](18, 2) NULL ,
	[discount_NSD] [decimal](18, 2) NULL ,
	[discount_REDATE] [decimal](18, 2) NULL ,
	[transportid] [varchar] (5) NULL ,
	[transport_unitid] [varchar] (5) NULL ,
	[transport_rate] [decimal](18, 2) NULL ,
	[transport_qty] [decimal](18, 2) NULL ,
	[transport_value] [decimal](18, 2) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL ,
	[vendercode] [varchar] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_salecash_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_saledebit] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[duedate] [datetime] NULL ,
	[credit_term] [int] NULL ,
	[taxno] [varchar] (20) NULL ,
	[docno_po] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](2, 0) NULL ,
	[discount] [decimal](18, 2) NULL ,
	[discount_percent] [decimal](18, 2) NULL ,
	[afterdiscount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[pay_chash] [decimal](18, 2) NULL ,
	[pay_cheque] [decimal](18, 2) NULL ,
	[pay_transfer] [decimal](18, 2) NULL ,
	[pay_other] [decimal](18, 2) NULL ,
	[pay_psd] [decimal](18, 2) NULL ,
	[FFA_Percent] [decimal](18, 2) NULL ,
	[FFD_Percent] [decimal](18, 2) NULL ,
	[NSD_Percent] [decimal](18, 2) NULL ,
	[REDATE_Percent] [decimal](18, 2) NULL ,
	[discount_FFA] [decimal](18, 2) NULL ,
	[discount_FFD] [decimal](18, 2) NULL ,
	[discount_NSD] [decimal](18, 2) NULL ,
	[discount_REDATE] [decimal](18, 2) NULL ,
	[transportid] [varchar] (5) NULL ,
	[transport_unitid] [varchar] (5) NULL ,
	[transport_rate] [decimal](18, 2) NULL ,
	[transport_qty] [decimal](18, 2) NULL ,
	[transport_value] [decimal](18, 2) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL ,
	[vendercode] [varchar] (20) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_saledebit_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_saleinc] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[taxdate_ref] [datetime] NULL ,
	[taxvalue_ref] [decimal](18, 2) NULL ,
	[taxno] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[FFA_Percent] [decimal](18, 2) NULL ,
	[FFD_Percent] [decimal](18, 2) NULL ,
	[NSD_Percent] [decimal](18, 2) NULL ,
	[REDATE_Percent] [decimal](18, 2) NULL ,
	[discount_FFA] [decimal](18, 2) NULL ,
	[discount_FFD] [decimal](18, 2) NULL ,
	[discount_NSD] [decimal](18, 2) NULL ,
	[discount_REDATE] [decimal](18, 2) NULL ,
	[effactstock] [varchar] (1) NULL ,
	[transportid] [varchar] (5) NULL ,
	[transport_unitid] [varchar] (5) NULL ,
	[transport_rate] [decimal](18, 2) NULL ,
	[transport_qty] [decimal](18, 2) NULL ,
	[transport_value] [decimal](18, 2) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_saleinc_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_salereturn] (
	[docno] [varchar] (10) NOT NULL ,
	[docdate] [datetime] NULL ,
	[taxno_ref] [varchar] (20) NULL ,
	[taxdate_ref] [datetime] NULL ,
	[taxvalue_ref] [decimal](18, 2) NULL ,
	[taxno] [varchar] (20) NULL ,
	[personid] [varchar] (10) NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[amountvat] [decimal](18, 2) NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[FFA_Percent] [decimal](18, 2) NULL ,
	[FFD_Percent] [decimal](18, 2) NULL ,
	[NSD_Percent] [decimal](18, 2) NULL ,
	[REDATE_Percent] [decimal](18, 2) NULL ,
	[discount_FFA] [decimal](18, 2) NULL ,
	[discount_FFD] [decimal](18, 2) NULL ,
	[discount_NSD] [decimal](18, 2) NULL ,
	[discount_REDATE] [decimal](18, 2) NULL ,
	[effactstock] [varchar] (1) NULL ,
	[transportid] [varchar] (5) NULL ,
	[transport_unitid] [varchar] (5) NULL ,
	[transport_rate] [decimal](18, 2) NULL ,
	[transport_qty] [decimal](18, 2) NULL ,
	[transport_value] [decimal](18, 2) NULL ,
	[remark_1] [varchar] (200) NULL ,
	[remark_2] [varchar] (200) NULL ,
	[remark_3] [varchar] (200) NULL ,
	[emprec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sal_salereturn_sub] (
	[docno] [varchar] (10) NOT NULL ,
	[productid] [varchar] (20) NOT NULL ,
	[productname] [varchar] (200) NULL ,
	[vattype] [varchar] (1) NULL ,
	[storeid] [varchar] (5) NULL ,
	[quantity] [decimal](18, 4) NULL ,
	[priceperunit] [decimal](18, 4) NULL ,
	[discount] [decimal](18, 4) NULL ,
	[total] [decimal](18, 4) NULL ,
	[vat] [decimal](18, 4) NULL ,
	[unit] [varchar] (5) NULL ,
	[description] [varchar] (200) NULL ,
	[rowitem] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_bank] (
	[BankID] [varchar] (5) NOT NULL ,
	[BankName] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_bankbook] (
	[acc_id] [varchar] (20) NOT NULL ,
	[acc_name] [varchar] (200) NULL ,
	[acc_type] [varchar] (5) NOT NULL ,
	[bankid] [varchar] (5) NULL ,
	[bankbranchid] [varchar] (5) NULL ,
	[acc_no] [varchar] (10) NULL ,
	[bf_term] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_bankbranch] (
	[bankbranchid] [varchar] (5) NOT NULL ,
	[bankbranchname] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_branch] (
	[branchid] [varchar] (5) NOT NULL ,
	[branchname] [varchar] (200) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_constant] (
	[Tax_No] [varchar] (20) NULL ,
	[CompanyName] [varchar] (150) NULL ,
	[Address] [varchar] (200) NULL ,
	[Telephone] [varchar] (100) NULL ,
	[Fax] [varchar] (100) NULL ,
	[WebSite] [varchar] (200) NULL ,
	[email] [varchar] (200) NULL ,
	[yearlang] [int] NULL ,
	[vattype] [varchar] (50) NULL ,
	[vat_rate] [decimal](2, 0) NULL ,
	[psd_rate] [decimal](2, 0) NULL ,
	[showdec] [int] NULL ,
	[docsametax] [varchar] (1) NULL ,
	[boundsystem] [int] NULL ,
	[FormPreview_PoForm] [int] NULL ,
	[FormName_PoForm] [varchar] (50) NULL ,
	[FormPreview_InvCForm] [int] NULL ,
	[FormName_InvCForm] [varchar] (50) NULL ,
	[FormPreview_InvDForm] [int] NULL ,
	[FormName_InvDForm] [varchar] (50) NULL ,
	[FormPreview_TaxForm] [int] NULL ,
	[FormName_TaxForm] [varchar] (50) NULL ,
	[FormPreview_BillForm] [int] NULL ,
	[FormName_BillForm] [varchar] (50) NULL ,
	[FormPreview_IncForm] [int] NULL ,
	[FormName_IncForm] [varchar] (50) NULL ,
	[FormPreview_DecForm] [int] NULL ,
	[FormName_DecForm] [varchar] (50) NULL ,
	[Gl_Realtime] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_docruning] (
	[id] [varchar] (5) NOT NULL ,
	[name] [varchar] (200) NULL ,
	[pattern] [varchar] (15) NULL ,
	[lastdocno] [varchar] (10) NULL ,
	[running_yn] [int] NULL ,
	[maxlength] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_employee] (
	[EmpID] [varchar] (5) NOT NULL ,
	[EmpName] [varchar] (100) NULL ,
	[Address] [varchar] (200) NULL ,
	[Telephone] [varchar] (50) NULL ,
	[NickName] [varchar] (70) NULL ,
	[EmpType] [varchar] (3) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_emptype] (
	[EmpType] [varchar] (3) NOT NULL ,
	[TypeName] [varchar] (30) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_grouppermission] (
	[GroupID] [varchar] (5) NOT NULL ,
	[MenuID] [varchar] (10) NOT NULL ,
	[Permission] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_groupuser] (
	[GroupID] [varchar] (5) NOT NULL ,
	[GroupName] [varchar] (50) NULL ,
	[Description] [varchar] (150) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_history] (
	[PinCode] [int] NOT NULL ,
	[EmpID] [varchar] (5) NULL ,
	[BRecDate] [datetime] NULL ,
	[BRecTime] [datetime] NULL ,
	[NRecDate] [datetime] NULL ,
	[NRecTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_menu] (
	[MenuID] [varchar] (10) NOT NULL ,
	[MenuName1] [varchar] (100) NULL ,
	[MenuName2] [varchar] (100) NULL ,
	[Description] [varchar] (255) NULL ,
	[MenuType] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_prefix] (
	[prefixid] [varchar] (5) NOT NULL ,
	[prefixname] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_printform] (
	[formid] [varchar] (20) NOT NULL ,
	[formname] [varchar] (100) NULL ,
	[filename] [varchar] (50) NULL ,
	[preview_yn] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_transfermoney] (
	[Docno_ref] [varchar] (20) NOT NULL ,
	[docdate_ref] [datetime] NULL ,
	[acc_bookid] [varchar] (20) NOT NULL ,
	[Amount] [money] NULL ,
	[Amount_Cenvention] [money] NULL ,
	[flagstatus] [varchar] (1) NOT NULL ,
	[feeeffact] [varchar] (1) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_transport] (
	[transportid] [varchar] (5) NOT NULL ,
	[transportname] [varchar] (200) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_transport_sub] (
	[transportid] [varchar] (5) NOT NULL ,
	[unitid] [varchar] (5) NULL ,
	[transport_value] [money] NULL ,
	[row_item] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_type_bankbook] (
	[typeid] [varchar] (5) NOT NULL ,
	[typename] [varchar] (150) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_user] (
	[GroupID] [varchar] (5) NOT NULL ,
	[UserID] [varchar] (10) NOT NULL ,
	[UserName] [varchar] (50) NULL ,
	[Description] [varchar] (255) NULL ,
	[Password] [varchar] (200) NULL ,
	[Revoke] [varchar] (50) NULL ,
	[AllowSetPermission] [varchar] (1) NULL ,
	[EmpID] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_userpermission] (
	[GroupID] [varchar] (5) NOT NULL ,
	[UserID] [varchar] (10) NOT NULL ,
	[MenuID] [varchar] (10) NOT NULL ,
	[Permission] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_sys_vendercode] (
	[personid] [varchar] (10) NOT NULL ,
	[vendercode] [varchar] (20) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tb_vat_doctax] (
	[apprecord] [varchar] (10) NULL ,
	[taxno] [varchar] (20) NOT NULL ,
	[taxdate] [datetime] NULL ,
	[docno_ref] [varchar] (20) NULL ,
	[effact_tax] [varchar] (1) NULL ,
	[personid] [varchar] (10) NOT NULL ,
	[description] [varchar] (200) NULL ,
	[product_type] [int] NULL ,
	[vattype] [int] NULL ,
	[taxmonth] [int] NULL ,
	[taxyear] [int] NULL ,
	[vatrate] [decimal](18, 2) NULL ,
	[amount] [decimal](18, 2) NULL ,
	[vat] [decimal](18, 2) NULL ,
	[totalamount] [decimal](18, 2) NULL ,
	[statustax] [varchar] (1) NOT NULL ,
	[EmpRec] [varchar] (5) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [tbAdjustPoint] (
	[PointBegin_1] [smallint] NULL ,
	[PointBegin_2] [smallint] NULL ,
	[PointBegin_3] [smallint] NULL ,
	[PointBegin_4] [smallint] NULL ,
	[PointBegin_5] [smallint] NULL ,
	[PointEnd_1] [smallint] NULL ,
	[PointEnd_2] [smallint] NULL ,
	[PointEnd_3] [smallint] NULL ,
	[PointEnd_4] [smallint] NULL ,
	[PointEnd_5] [smallint] NULL ,
	[AdjPoint_1] [smallint] NULL ,
	[AdjPoint_2] [smallint] NULL ,
	[AdjPoint_3] [smallint] NULL ,
	[AdjPoint_4] [smallint] NULL ,
	[AdjPoint_5] [smallint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [tb_ap_adjustap] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ap_adjustap] PRIMARY KEY  NONCLUSTERED 
	(
		[apprecord],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_beginterm] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ap_beginterm] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_bill] WITH NOCHECK ADD 
	CONSTRAINT [DF_tb_ap_bill_use_yn] DEFAULT ('N') FOR [use_yn],
	CONSTRAINT [PK_tb_ap_bill] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_bill_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ap_bill_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_createap] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ap_createap] PRIMARY KEY  NONCLUSTERED 
	(
		[apprecord],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_payment] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_payment] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_payment_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ap_payment_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ap_person] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ap_person] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_adjustar] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_adjustar] PRIMARY KEY  NONCLUSTERED 
	(
		[apprecord],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_beginterm] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_beginterm] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_bill] WITH NOCHECK ADD 
	CONSTRAINT [DF_tb_ar_bill_use_yn] DEFAULT ('N') FOR [use_yn],
	CONSTRAINT [PK_tb_ar_bill] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_bill_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_bill_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_createar] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_createar] PRIMARY KEY  NONCLUSTERED 
	(
		[apprecord],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_person] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_person] PRIMARY KEY  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_receipt] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_receipt] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ar_receipt_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ar_receipt_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_chequeresult] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_chequeresult] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[doctype]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_chequeresult_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_chequeresult_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[doctype],
		[chqno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_chequetrans] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_chequetrans] PRIMARY KEY  NONCLUSTERED 
	(
		[bankid],
		[chqno],
		[inout]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_depositchq] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_depositchq] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_depositchq_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_depositchq_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[chqno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_depositdraw] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_depositdraw] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_incomeexpense] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_incomeexpense] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_chq_transfer] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_chq_transfer] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_accchart] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_accchart] PRIMARY KEY  NONCLUSTERED 
	(
		[branchid],
		[accno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_book] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_book] PRIMARY KEY  NONCLUSTERED 
	(
		[bookid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_expense] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_expense] PRIMARY KEY  NONCLUSTERED 
	(
		[apprecord],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_income] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_income] PRIMARY KEY  NONCLUSTERED 
	(
		[apprecord],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_pattern_voucher] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_pattern_voucher] PRIMARY KEY  NONCLUSTERED 
	(
		[patternid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_pattern_voucher_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_pattern_voucher_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[patternid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_template] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_template] PRIMARY KEY  NONCLUSTERED 
	(
		[tempid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_template_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_template_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[tempid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_trans2apt] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_trans2apt] PRIMARY KEY  NONCLUSTERED 
	(
		[bookid],
		[docno_ref]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_transsub2apt] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_transsub2apt] PRIMARY KEY  NONCLUSTERED 
	(
		[bookid],
		[rowitem],
		[docno_ref]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_gl_variable] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_gl_variable] PRIMARY KEY  NONCLUSTERED 
	(
		[varid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_adjuststock] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_adjuststock] PRIMARY KEY  NONCLUSTERED 
	(
		[doctype],
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_adjuststock_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_adjuststock_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[doctype],
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_beginterm] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_beginterm] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_beginterm_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_beginterm_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_countstock] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_countstock] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_countstock_doc] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_countstock_doc] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_countstock_doc_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_countstock_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_countstock_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_countstock_doc_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_location] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_location] PRIMARY KEY  NONCLUSTERED 
	(
		[LocationID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_moreunit] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_moreunit] PRIMARY KEY  NONCLUSTERED 
	(
		[ProductID],
		[ratio_level]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_product] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_product] PRIMARY KEY  NONCLUSTERED 
	(
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_product_detail] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_product_detail] PRIMARY KEY  NONCLUSTERED 
	(
		[ProductID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_productgroup] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_productgroup] PRIMARY KEY  NONCLUSTERED 
	(
		[GroupID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_store] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_store] PRIMARY KEY  NONCLUSTERED 
	(
		[storeid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_trans] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_trans] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[flagdraw]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_trans_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_trans_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem],
		[flagdraw]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_transdraw] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_transdraw] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[flagdraw]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_transdraw_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_transdraw_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem],
		[flagdraw]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_transfer] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_transfer] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_transfer_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_transfer_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_unit] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_unit] PRIMARY KEY  NONCLUSTERED 
	(
		[unitid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_warehouse] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_warehouse] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[doctype]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_ic_warehouse_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_ic_warehouse_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[rowitem],
		[doctype]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_cash] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_cash] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_cash_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_cash_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_debit] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_debit] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_debit_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_debit_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_inc] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_inc] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_inc_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_inc_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_return] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_return] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_return_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_return_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_sheet] WITH NOCHECK ADD 
	CONSTRAINT [DF_tb_pur_purchase_sheet_use_yn] DEFAULT ('N') FOR [use_yn],
	CONSTRAINT [PK_tb_pur_purchase_sheet] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_pur_purchase_sheet_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_pur_purchase_sheet_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_cancel] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_cancel] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_cancel_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_cancel_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_salecash] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_salecash] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_salecash_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_salecash_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_saledebit] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_saledebit] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_saledebit_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_saledebit_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_saleinc] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_saleinc] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_saleinc_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_saleinc_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_salereturn] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_salereturn] PRIMARY KEY  NONCLUSTERED 
	(
		[docno]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sal_salereturn_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sal_salereturn_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[docno],
		[productid],
		[rowitem]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_bank] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_bank] PRIMARY KEY  NONCLUSTERED 
	(
		[BankID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_bankbook] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_bankbook] PRIMARY KEY  NONCLUSTERED 
	(
		[acc_id],
		[acc_type]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_bankbranch] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_bankbranch] PRIMARY KEY  NONCLUSTERED 
	(
		[bankbranchid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_branch] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_branch] PRIMARY KEY  NONCLUSTERED 
	(
		[branchid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_docruning] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_docruning] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_employee] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_employee] PRIMARY KEY  NONCLUSTERED 
	(
		[EmpID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_emptype] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_emptype] PRIMARY KEY  NONCLUSTERED 
	(
		[EmpType]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_grouppermission] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_grouppermission] PRIMARY KEY  NONCLUSTERED 
	(
		[GroupID],
		[MenuID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_groupuser] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_groupuser] PRIMARY KEY  NONCLUSTERED 
	(
		[GroupID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_history] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_history] PRIMARY KEY  NONCLUSTERED 
	(
		[PinCode]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_menu] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_menu] PRIMARY KEY  NONCLUSTERED 
	(
		[MenuID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_prefix] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_prefix] PRIMARY KEY  NONCLUSTERED 
	(
		[prefixid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_printform] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_printform] PRIMARY KEY  NONCLUSTERED 
	(
		[formid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_transfermoney] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_Sys_TransferMoney] PRIMARY KEY  NONCLUSTERED 
	(
		[Docno_ref],
		[acc_bookid],
		[flagstatus]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_transport] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_transport] PRIMARY KEY  NONCLUSTERED 
	(
		[transportid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_transport_sub] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_transport_sub] PRIMARY KEY  NONCLUSTERED 
	(
		[transportid],
		[row_item]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_type_bankbook] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_type_bankbook] PRIMARY KEY  NONCLUSTERED 
	(
		[typeid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_user] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_user] PRIMARY KEY  NONCLUSTERED 
	(
		[GroupID],
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_userpermission] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_userpermission] PRIMARY KEY  NONCLUSTERED 
	(
		[GroupID],
		[UserID],
		[MenuID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_sys_vendercode] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_sys_vendercode] PRIMARY KEY  NONCLUSTERED 
	(
		[personid],
		[vendercode]
	)  ON [PRIMARY] 
GO

ALTER TABLE [tb_vat_doctax] WITH NOCHECK ADD 
	CONSTRAINT [PK_tb_vat_doctax] PRIMARY KEY  NONCLUSTERED 
	(
		[taxno],
		[personid],
		[statustax]
	)  ON [PRIMARY] 
GO

CREATE VIEW v_ap_label 
AS 
SELECT tb_ap_person.id AS PersonID,ltrim(isnull(tb_sys_prefix.prefixname, '') + ' ') + tb_ap_person.Name AS Personname,(tb_ap_person.Address + CASE WHEN tb_ap_person.Moo IS NULL OR tb_ap_person.Moo = '' THEN '' ELSE '  หมู่ที่ ' + tb_ap_person.Moo END) AS Row_1,(CASE WHEN tb_ap_person.Soi IS NULL OR tb_ap_person.Soi = '' THEN '' ELSE '  ซอย' + tb_ap_person.Soi END + CASE WHEN tb_ap_person.Road IS NULL OR  tb_ap_person.Road = '' THEN '' ELSE '  ถนน' + tb_ap_person.Road  END) AS Row_2,  (CASE WHEN NOT (tb_ap_person.District IS NULL OR tb_ap_person.District = '') THEN (CASE WHEN tb_ap_person.TumbonType = 0 THEN '  ตำบล' ELSE '  แขวง' END) + tb_ap_person.District ELSE '' END)  AS Row_3,  (CASE WHEN NOT (tb_ap_person.Zone IS NULL OR tb_ap_person.Zone = '') THEN (CASE WHEN tb_ap_person.AumperType = 0 THEN '  อำเภอ' ELSE '  เขต' END) + tb_ap_person.Zone ELSE '' END) AS Row_4, (CASE WHEN tb_ap_person.Province IS NULL OR tb_ap_person.Province = '' THEN '' ELSE '  จังหวัด' + tb_ap_person.Province END) AS Row_5, isnull(tb_ap_person.Postcode, '') AS Row_6  FROM tb_ap_person LEFT OUTER JOIN tb_sys_prefix ON tb_ap_person.PrefixID = tb_sys_prefix.prefixid 
GO

CREATE VIEW v_ap_label_noprefix 
AS 
SELECT tb_ap_person.id AS PersonID,ltrim(isnull(tb_sys_prefix.prefixname, '') + ' ') + tb_ap_person.Name AS Personname,(tb_ap_person.Address + CASE WHEN tb_ap_person.Moo IS NULL OR tb_ap_person.Moo = '' THEN '' ELSE '  ' + tb_ap_person.Moo END) AS Row_1, (CASE WHEN tb_ap_person.Soi IS NULL OR tb_ap_person.Soi = '' THEN '' ELSE '  ' + tb_ap_person.Soi END + CASE WHEN tb_ap_person.Road IS NULL OR tb_ap_person.Road = '' THEN '' ELSE '  ' + tb_ap_person.Road END) AS Row_2,(CASE WHEN NOT (tb_ap_person.District IS NULL OR tb_ap_person.District = '') THEN (CASE WHEN tb_ap_person.TumbonType = 0 THEN '  ' ELSE '  ' END) + tb_ap_person.District ELSE '' END) AS Row_3,(CASE WHEN NOT (tb_ap_person.Zone IS NULL OR tb_ap_person.Zone = '') THEN (CASE WHEN tb_ap_person.AumperType = 0 THEN '  ' ELSE  '  ' END) + tb_ap_person.Zone ELSE '' END) AS Row_4,(CASE WHEN tb_ap_person.Province IS NULL OR tb_ap_person.Province = '' THEN '' ELSE '  ' + tb_ap_person.Province END) AS Row_5, isnull(tb_ap_person.Postcode, '') AS Row_6 FROM tb_ap_person LEFT OUTER JOIN tb_sys_prefix ON tb_ap_person.PrefixID = tb_sys_prefix.prefixid 
GO

CREATE VIEW v_ap_list_adjustap
AS
SELECT tb_ap_adjustap.docno, tb_ap_adjustap.docdate, 
    tb_ap_adjustap.personid, 
    tb_ap_person.Name AS personname, 
    tb_ap_adjustap.taxno, tb_ap_adjustap.taxno_ref, 
    tb_ap_adjustap.description, 
    (CASE doc_effact WHEN '+' THEN tb_ap_adjustap.afterdiscount
     + tb_ap_adjustap.amountvat ELSE 0 END) AS sumdr, 
    (CASE doc_effact WHEN '-' THEN tb_ap_adjustap.afterdiscount
     + tb_ap_adjustap.amountvat ELSE 0 END) AS sumcr, 
    'C' AS doctype, tb_ap_adjustap.doc_effact
FROM tb_ap_adjustap LEFT OUTER JOIN
    tb_ap_person ON 
    tb_ap_adjustap.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_list_bfterm
AS
SELECT tb_ap_beginterm.docno, tb_ap_beginterm.docdate, 
    tb_ap_beginterm.personid, 
    tb_ap_person.Name AS personname, tb_ap_beginterm.taxno, 
    '' AS taxno_ref, '' AS description, 
    tb_ap_beginterm.amount AS sumdr, 0 AS sumcr, 
    'A' AS doctype
FROM tb_ap_beginterm LEFT OUTER JOIN
    tb_ap_person ON 
    tb_ap_beginterm.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_list_createap
AS
SELECT tb_ap_createap.docno, tb_ap_createap.docdate, 
    tb_ap_createap.personid, 
    tb_ap_person.Name AS personname, tb_ap_createap.taxno, 
    '' AS taxno_ref, tb_ap_createap.description, 
    (CASE WHEN tb_ap_createap.doc_effact = '+' THEN tb_ap_createap.afterdiscount
     + tb_ap_createap.amountvat ELSE 0 END) AS sumdr, 
    (CASE WHEN tb_ap_createap.doc_effact = '-' THEN tb_ap_createap.afterdiscount
     + tb_ap_createap.amountvat ELSE 0 END) AS sumcr, 
    'B' AS doctype
FROM tb_ap_createap LEFT OUTER JOIN
    tb_ap_person ON 
    tb_ap_createap.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_list_dec
AS
SELECT tb_pur_purchase_return.docno, 
    tb_pur_purchase_return.docdate, 
    tb_pur_purchase_return.personid, 
    tb_ap_person.Name AS personname, 
    tb_pur_purchase_return.taxno, 
    tb_pur_purchase_return.taxno_ref, 
    tb_pur_purchase_return.description, 0 AS sumdr, 
    tb_pur_purchase_return.amount + tb_pur_purchase_return.amountvat
     AS sumcr, 'B' AS doctype
FROM tb_pur_purchase_return LEFT OUTER JOIN
    tb_ap_person ON 
    tb_pur_purchase_return.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_list_inc
AS
SELECT tb_pur_purchase_inc.docno, tb_pur_purchase_inc.docdate, 
    tb_pur_purchase_inc.personid, 
    tb_ap_person.Name AS personname, 
    tb_pur_purchase_inc.taxno, tb_pur_purchase_inc.taxno_ref, 
    tb_pur_purchase_inc.description, 
    tb_pur_purchase_inc.amount + tb_pur_purchase_inc.amountvat AS
     sumdr, 0 AS sumcr, 'B' AS doctype
FROM tb_pur_purchase_inc LEFT OUTER JOIN
    tb_ap_person ON 
    tb_pur_purchase_inc.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_listpayment
AS
SELECT tb_ap_payment.docdate, tb_ap_payment.docno, 
    tb_ap_payment.personid, 
    tb_ap_person.Name AS personname, tb_ap_payment.taxno, 
    tb_ap_payment.description, 0 AS sumdr, 
    tb_ap_payment.pay_chash + tb_ap_payment.pay_cheque + tb_ap_payment.pay_transfer
     + tb_ap_payment.pay_other AS sumcr, 'D' AS doctype
FROM tb_ap_payment LEFT OUTER JOIN
    tb_ap_person ON 
    tb_ap_payment.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_listpur
AS
SELECT tb_pur_purchase_debit.docno, 
    tb_pur_purchase_debit.docdate, 
    tb_pur_purchase_debit.personid, 
    tb_ap_person.Name AS personname, 
    tb_pur_purchase_debit.taxno, 
    tb_pur_purchase_debit.description, 
    tb_pur_purchase_debit.afterdiscount + tb_pur_purchase_debit.amountvat
     AS sumdr, 0 AS sumcr, 'A' AS doctype
FROM tb_pur_purchase_debit LEFT OUTER JOIN
    tb_ap_person ON 
    tb_pur_purchase_debit.personid = tb_ap_person.ID
GO

CREATE VIEW v_ap_sumpayment AS SELECT tb_ap_payment_sub.docno_ref, MAX(tb_ap_payment_sub.taxno_ref) AS taxno_ref, SUM(ISNULL(tb_ap_payment_sub.payment, 0))  AS sumpayment, MAX(tb_ap_payment.personid) AS personid, MAX(tb_ap_payment.docno) AS docno FROM tb_ap_payment INNER JOIN tb_ap_payment_sub ON  tb_ap_payment.docno = tb_ap_payment_sub.docno GROUP BY tb_ap_payment_sub.docno_ref 
GO

CREATE VIEW v_ap_payment_remain
AS
SELECT 'A_BF' AS doctype, docno AS docno_ref, 
    docdate AS docdate_ref, taxno AS taxno_ref, 
    due_date AS duedate_ref, personid, 
    'ยอดยกมา' AS description_ref, amount, amount - isnull
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_beginterm.docno),
     0) AS remain_pay, amount - isnull
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_beginterm.docno),
     0) AS payment
FROM tb_ap_beginterm
WHERE amount - isnull
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_beginterm.docno),
     0) <> 0
UNION ALL
SELECT 'B_N' AS doctype, docno AS docno_ref, 
    docdate AS docdate_ref, taxno AS taxno_ref, 
    duedate AS duedate_ref, personid, 'ตั้งหนี้' AS description_ref, 
    ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) AS amount, 
    (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0)) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_createap.sendno),
     0) AS remain_pay, (ISNULL(afterdiscount, 0) 
    + ISNULL(amountvat, 0)) - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_createap.sendno),
     0) AS payment
FROM tb_ap_createap
WHERE (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0)) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_createap.sendno),
     0) <> 0
UNION ALL
SELECT 'C_AJ' AS doctype, docno AS docno_ref, 
    docdate AS docdate_ref, taxno AS taxno_ref, 
    duedate AS duedate_ref, personid, 
    (CASE doc_effact WHEN '-' THEN 'ลดหนี้' ELSE 'เพิ่มหนี้' END) 
    AS description_ref, 
    (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0)) 
    AS amount, 
    (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_adjustap.sendno_ref),
     0)) AS remain_pay, 
    (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_adjustap.sendno_ref),
     0)) AS payment
FROM tb_ap_adjustap
WHERE (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ap_sumpayment
       WHERE v_ap_sumpayment.docno_ref = tb_ap_adjustap.sendno_ref),
     0)) <> 0
GO

CREATE VIEW v_ap_remain_payment AS SELECT docno AS docno_ref, docdate AS docdate_ref, taxno AS taxno_ref, doc_effact, tb_ap_createap.personid AS personid,afterdiscount + amountvat AS amount, (CASE WHEN doc_effact = '+' THEN (afterdiscount + amountvat) - isnull((SELECT SUM(sumpayment) AS sumpayment FROM v_ap_sumpayment WHERE v_ap_sumpayment.taxno_ref = tb_ap_createap.taxno), 0) ELSE 0 END) AS remain_pay, ISNULL(description, '')  AS description FROM tb_ap_createap  
GO

CREATE VIEW v_ap_rep_adjustap
AS
SELECT tb_ap_adjustap.docdate, tb_ap_adjustap.docno, 
    tb_ap_adjustap.taxno, tb_ap_adjustap.taxdate, 
    tb_ap_adjustap.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ap_person.Name AS personname, 
    tb_ap_adjustap.duedate, 
    tb_ap_adjustap.total AS amount, 
    tb_ap_adjustap.discount, tb_ap_adjustap.amountvat, 
    ISNULL(tb_ap_adjustap.afterdiscount, 0) 
    + ISNULL(tb_ap_adjustap.amountvat, 0) AS netamount, 
    tb_ap_adjustap.doc_effact
FROM tb_ap_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ap_person.PrefixID = tb_sys_prefix.prefixid RIGHT OUTER
     JOIN
    tb_ap_adjustap ON 
    tb_ap_person.ID = tb_ap_adjustap.personid
GO

create view v_ap_rep_apperson as SELECT tb_ap_person.id as personid, (ltrim(isnull(tb_sys_prefix.prefixname, '') + ' ') + tb_ap_person.name ) AS personname, (Address + CASE WHEN Moo IS NULL OR Moo = '' THEN '' ELSE ' หมู่ที่ ' + Moo END + CASE WHEN Soi IS NULL OR Soi = '' THEN '' ELSE ' ซอย' + Soi END + CASE WHEN Road IS NULL OR Road = '' THEN '' ELSE ' ถนน' + Road END + CASE WHEN NOT (District  IS NULL OR  District = '') THEN (CASE WHEN TumbonType = 0 THEN ' ตำบล' ELSE ' แขวง' END) + District ELSE '' END + CASE WHEN NOT (Zone IS NULL OR Zone = '') THEN (CASE WHEN AumperType = 0 THEN ' อำเภอ' ELSE ' เขต' END) + Zone ELSE '' END + CASE WHEN Province IS NULL OR Province = '' THEN '' ELSE ' จังหวัด' + Province END + ' ' + Postcode)  AS Address, Telephone FROM tb_ap_person LEFT OUTER JOIN  tb_sys_prefix ON tb_ap_person.prefixid = tb_sys_prefix.prefixid  
GO

CREATE VIEW v_ap_rep_beginterm
AS
SELECT tb_ap_beginterm.docdate, tb_ap_beginterm.docno, 
    tb_ap_beginterm.taxno, 
    ISNULL(tb_ap_beginterm.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-') AS personname, 
    tb_ap_beginterm.amount AS total, 
    tb_ap_beginterm.due_date, 
    tb_ap_beginterm.credit_term
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_ap_beginterm ON 
    tb_ap_person.ID = tb_ap_beginterm.personid
GO

CREATE VIEW v_ap_rep_beginterm_sum
AS
SELECT ISNULL(tb_ap_beginterm.personid, '-') AS personid, 
    MAX(ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-')) AS personname, 
    SUM(tb_ap_beginterm.amount) AS total
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_ap_beginterm ON 
    tb_ap_person.ID = tb_ap_beginterm.personid
GROUP BY tb_ap_beginterm.personid
GO

CREATE VIEW v_ap_rep_cardap AS SELECT docno, docdate, personid, personname, taxno, taxno_ref,description, sumdr, sumcr, doctype FROM v_ap_list_bfterm UNION ALL SELECT docno, docdate, personid, personname, taxno, taxno_ref, description, sumdr, sumcr, doctype FROM v_ap_list_createap UNION ALL SELECT docno, docdate, personid, personname, taxno, taxno_ref,description, sumdr, sumcr, doctype FROM v_ap_list_adjustap UNION ALL SELECT docno, docdate, personid, personname, taxno, '' AS taxno_ref, description, sumdr, sumcr, doctype FROM v_ap_listpayment 
GO

CREATE VIEW v_ap_rep_createap
AS
SELECT tb_ap_createap.docdate, tb_ap_createap.docno, 
    tb_ap_createap.taxno, tb_ap_createap.taxdate, 
    tb_ap_createap.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ap_person.Name AS personname, 
    tb_ap_createap.duedate, 
    tb_ap_createap.total AS amount, 
    tb_ap_createap.discount, tb_ap_createap.amountvat, 
    ISNULL(tb_ap_createap.afterdiscount, 0) 
    + ISNULL(tb_ap_createap.amountvat, 0) 
    AS netamount
FROM tb_ap_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ap_person.PrefixID = tb_sys_prefix.prefixid RIGHT OUTER
     JOIN
    tb_ap_createap ON 
    tb_ap_person.ID = tb_ap_createap.personid
GO

CREATE VIEW v_ap_rep_payment
AS
SELECT tb_ap_payment.docdate, tb_ap_payment.docno, 
    tb_ap_payment_sub.taxno_ref AS taxno, 
    tb_ap_payment.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ap_person.Name AS name, 
    tb_ap_payment.pay_transfer, 
    tb_ap_payment.pay_chash, 
    tb_ap_payment.pay_cheque, 
    tb_ap_payment.pay_psd, tb_ap_payment.discount, 
    tb_ap_payment.discount_percent, 
    tb_ap_payment.pay_other, 
    ISNULL(tb_ap_payment.pay_transfer, 0) 
    + ISNULL(tb_ap_payment.pay_chash, 0) 
    + ISNULL(tb_ap_payment.pay_cheque, 0) 
    + ISNULL(tb_ap_payment.pay_psd, 0) 
    - ISNULL(tb_ap_payment.discount, 0) 
    + ISNULL(tb_ap_payment.pay_other, 0) AS grandtotal, 
    tb_ap_payment_sub.payment, 
    tb_ap_payment.holdbill
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_ap_payment LEFT OUTER JOIN
    tb_ap_payment_sub ON 
    tb_ap_payment.docno = tb_ap_payment_sub.docno ON 
    tb_ap_person.ID = tb_ap_payment.personid
GO

CREATE VIEW v_ap_rep_StatusAp
AS
SELECT ID AS personid, Name AS personname, ISNULL
        ((SELECT SUM(amount)
       FROM tb_ap_beginterm
       WHERE tb_ap_beginterm.personid = tb_ap_person.id), 0) 
    AS sum_bf, ISNULL
        ((SELECT SUM(afterdiscount + amountvat)
       FROM tb_pur_purchase_debit
       WHERE tb_pur_purchase_debit.personid = tb_ap_person.id),
     0) AS sum_pur_debit, ISNULL
        ((SELECT SUM(afterdiscount + amountvat)
       FROM tb_pur_purchase_cash
       WHERE tb_pur_purchase_cash.personid = tb_ap_person.id),
     0) AS sum_pur_cash, ISNULL
        ((SELECT SUM(amount + amountvat)
       FROM tb_pur_purchase_inc
       WHERE tb_pur_purchase_inc.personid = tb_ap_person.id), 
    0) AS sum_inc, ISNULL
        ((SELECT SUM(amount + amountvat)
       FROM tb_pur_purchase_return
       WHERE tb_pur_purchase_return.personid = tb_ap_person.id),
     0) AS sum_dec
FROM tb_ap_person
GO

CREATE VIEW v_ap_sadjustap_dec AS SELECT tb_ap_adjustap.docno, tb_ap_adjustap.docdate, tb_ap_adjustap.personid, tb_ap_person.Name AS personname, tb_ap_adjustap.apprecord FROM tb_ap_adjustap LEFT OUTER JOIN tb_ap_person ON  tb_ap_adjustap.personid = tb_ap_person.ID WHERE (tb_ap_adjustap.doc_effact = '-') 
GO

CREATE VIEW v_ap_sadjustap_inc AS SELECT tb_ap_adjustap.docno, tb_ap_adjustap.docdate, tb_ap_adjustap.personid,tb_ap_person.Name AS personname,tb_ap_adjustap.apprecord FROM tb_ap_adjustap LEFT OUTER JOIN tb_ap_person ON  tb_ap_adjustap.personid = tb_ap_person.ID WHERE (tb_ap_adjustap.doc_effact = '+') 
GO

CREATE VIEW v_ap_sbeginterm AS SELECT tb_ap_beginterm.docno, tb_ap_beginterm.docdate,tb_ap_beginterm.taxno, tb_ap_person.Name FROM tb_ap_beginterm LEFT OUTER JOIN tb_ap_person ON tb_ap_beginterm.personid = tb_ap_person.ID 
GO

CREATE VIEW v_ap_sbill AS SELECT tb_ap_bill.docno, tb_ap_bill.docdate, tb_ap_bill.personid,ISNULL(tb_ap_person.Name, '') AS personname,tb_ap_bill.duedate, ISNULL ((SELECT SUM(holdbill) FROM tb_ap_bill_sub WHERE tb_ap_bill_sub.docno = tb_ap_bill.docno), 0) AS holdbill FROM tb_ap_bill LEFT OUTER JOIN tb_ap_person ON  tb_ap_bill.personid = tb_ap_person.ID 
GO

CREATE VIEW v_ap_screateap AS SELECT tb_ap_createap.apprecord, tb_ap_createap.docno,tb_ap_createap.docdate, tb_ap_createap.sendno,tb_ap_createap.taxno,ISNULL(tb_ap_createap.personid, '-') AS personid,ISNULL(tb_ap_person.Name, '-') AS personname FROM tb_ap_createap LEFT OUTER JOIN tb_ap_person ON tb_ap_createap.personid = tb_ap_person.ID 
GO

CREATE VIEW v_ap_spayment AS SELECT tb_ap_payment.docno, tb_ap_payment.docdate, tb_ap_payment.taxno, ISNULL(tb_ap_payment.personid, '-')  AS personid, ISNULL(tb_ap_person.Name, '-')  AS personname FROM tb_ap_payment LEFT OUTER JOIN  tb_ap_person ON tb_ap_payment.personid = tb_ap_person.ID  
GO

CREATE VIEW v_ap_sperson
AS
SELECT tb_ap_person.ID, 
    ISNULL(tb_sys_prefix.prefixname, '') AS prefixname, 
    tb_ap_person.Name
FROM tb_ap_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ap_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_ap_utrans AS SELECT 'BF' AS doctype, 'AP' AS apprecord, docno, docdate,docno AS sendno, taxno, NULL AS taxdate, 0 AS taxmonth,0 AS taxyear, personid, '' AS description, due_date, credit_term, 0 AS product_type, 0 AS taxtype,0 AS taxtype_percent, 0 AS taxtype_value, 0 AS vattype, amount AS total, 0 AS vatrate, 0 AS discount,0 AS discount_percent, amount AS afterdiscount, 0 AS amountvat, '+' AS doc_effact FROM tb_ap_beginterm UNION ALL SELECT 'CA' AS doctype, apprecord, docno, docdate, sendno, taxno, taxdate, taxmonth, taxyear, personid, description, duedate,credit_term, product_type, taxtype, taxtype_percent, taxtype_value, vattype, total, vatrate, discount,discount_percent, afterdiscount, amountvat, doc_effact FROM tb_ap_createap UNION ALL SELECT 'AJ' AS doctype, apprecord, docno, docdate,docno AS sendno, taxno, taxdate, taxmonth, taxyear, personid,description, duedate, credit_term, product_type, taxtype,taxtype_percent, taxtype_value, vattype, total, vatrate, discount, discount_percent, afterdiscount, amountvat, doc_effact FROM tb_ap_adjustap 
GO

CREATE VIEW v_ar_label
AS
SELECT tb_ar_person.id AS PersonID, 
    ltrim(isnull(tb_sys_prefix.prefixname, '') + ' ') 
    + tb_ar_person.Name AS Personname, 
    (tb_ar_person.Address + CASE WHEN tb_ar_person.Moo IS NULL
     OR
    tb_ar_person.Moo = '' THEN '' ELSE '  หมู่ที่ ' + tb_ar_person.Moo
     END) AS Row_1, 
    (CASE WHEN tb_ar_person.Soi IS NULL OR
    tb_ar_person.Soi = '' THEN '' ELSE '  ซอย' + tb_ar_person.Soi END
     + CASE WHEN tb_ar_person.Road IS NULL OR
    tb_ar_person.Road = '' THEN '' ELSE '  ถนน' + tb_ar_person.Road
     END) AS Row_2, 
    (CASE WHEN NOT (tb_ar_person.District IS NULL OR
    tb_ar_person.District = '') 
    THEN (CASE WHEN tb_ar_person.TumbonType = 0 THEN '  ตำบล'
     ELSE '  แขวง' END) + tb_ar_person.District ELSE '' END) 
    AS Row_3, 
    (CASE WHEN NOT (tb_ar_person.Zone IS NULL OR
    tb_ar_person.Zone = '') 
    THEN (CASE WHEN tb_ar_person.AumperType = 0 THEN '  อำเภอ'
     ELSE '  เขต' END) + tb_ar_person.Zone ELSE '' END) 
    AS Row_4, (CASE WHEN tb_ar_person.Province IS NULL OR
    tb_ar_person.Province = '' THEN '' ELSE '  จังหวัด' + tb_ar_person.Province
     END) AS Row_5, isnull(tb_ar_person.Postcode, '') 
    AS Row_6
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_ar_label_noprefix
AS
SELECT tb_ar_person.id AS PersonID, 
    ltrim(isnull(tb_sys_prefix.prefixname, '') + ' ') 
    + tb_ar_person.Name AS Personname, 
    (tb_ar_person.Address + CASE WHEN tb_ar_person.Moo IS NULL
     OR
    tb_ar_person.Moo = '' THEN '' ELSE '  ' + tb_ar_person.Moo END)
     AS Row_1, (CASE WHEN tb_ar_person.Soi IS NULL OR
    tb_ar_person.Soi = '' THEN '' ELSE '  ' + tb_ar_person.Soi END +
     CASE WHEN tb_ar_person.Road IS NULL OR
    tb_ar_person.Road = '' THEN '' ELSE '  ' + tb_ar_person.Road END)
     AS Row_2, 
    (CASE WHEN NOT (tb_ar_person.District IS NULL OR
    tb_ar_person.District = '') 
    THEN (CASE WHEN tb_ar_person.TumbonType = 0 THEN '  ' ELSE
     '  ' END) + tb_ar_person.District ELSE '' END) AS Row_3, 
    (CASE WHEN NOT (tb_ar_person.Zone IS NULL OR
    tb_ar_person.Zone = '') 
    THEN (CASE WHEN tb_ar_person.AumperType = 0 THEN '  ' ELSE
     '  ' END) + tb_ar_person.Zone ELSE '' END) AS Row_4, 
    (CASE WHEN tb_ar_person.Province IS NULL OR
    tb_ar_person.Province = '' THEN '' ELSE '  ' + tb_ar_person.Province
     END) AS Row_5, isnull(tb_ar_person.Postcode, '') 
    AS Row_6
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_ar_list_adjustar
AS
SELECT tb_ar_adjustar.docno, tb_ar_adjustar.docdate, 
    tb_ar_adjustar.personid, 
    tb_ar_person.Name AS personname, 
    tb_ar_adjustar.taxno, tb_ar_adjustar.taxno_ref, 
    tb_ar_adjustar.description, 
    (CASE doc_effact WHEN '+' THEN tb_ar_adjustar.afterdiscount
     + tb_ar_adjustar.amountvat ELSE 0 END) AS sumdr, 
    (CASE doc_effact WHEN '-' THEN tb_ar_adjustar.afterdiscount
     + tb_ar_adjustar.amountvat ELSE 0 END) AS sumcr, 
    'C' AS doctype, tb_ar_adjustar.doc_effact
FROM tb_ar_adjustar LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_adjustar.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_list_bfterm
AS
SELECT tb_ar_beginterm.docno, tb_ar_beginterm.docdate, 
    tb_ar_beginterm.personid, 
    tb_ar_person.Name AS personname, tb_ar_beginterm.taxno, 
    '' AS taxno_ref, '' AS description, 
    tb_ar_beginterm.amount AS sumdr, 0 AS sumcr, 
    'A' AS doctype
FROM tb_ar_beginterm LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_beginterm.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_list_createar
AS
SELECT tb_ar_createar.docno, tb_ar_createar.docdate, 
    tb_ar_createar.personid, 
    tb_ar_person.Name AS personname, 
    tb_ar_createar.taxno, '' AS taxno_ref, 
    tb_ar_createar.description, 
    CASE WHEN tb_ar_createar.doc_effact = '+' THEN tb_ar_createar.afterdiscount
     + tb_ar_createar.amountvat ELSE 0 END AS sumdr, 
    CASE WHEN tb_ar_createar.doc_effact = '-' THEN tb_ar_createar.afterdiscount
     + tb_ar_createar.amountvat ELSE 0 END AS sumcr, 
    'B' AS doctype
FROM tb_ar_createar LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_createar.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_list_dec
AS
SELECT tb_sal_salereturn.docno, tb_sal_salereturn.docdate, 
    tb_sal_salereturn.personid, 
    tb_ar_person.Name AS personname, tb_sal_salereturn.taxno, 
    tb_sal_salereturn.taxno_ref, tb_sal_salereturn.description, 
    0 AS sumdr, 
    tb_sal_salereturn.amount + tb_sal_salereturn.amountvat AS sumcr,
     'B' AS doctype
FROM tb_sal_salereturn LEFT OUTER JOIN
    tb_ar_person ON tb_sal_salereturn.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_list_inc
AS
SELECT tb_sal_saleinc.docno, tb_sal_saleinc.docdate, 
    tb_sal_saleinc.personid, tb_ar_person.Name AS personname, 
    tb_sal_saleinc.taxno, tb_sal_saleinc.taxno_ref, 
    tb_sal_saleinc.description, 
    tb_sal_saleinc.amount + tb_sal_saleinc.amountvat AS sumdr, 
    0 AS sumcr, 'B' AS doctype
FROM tb_sal_saleinc LEFT OUTER JOIN
    tb_ar_person ON tb_sal_saleinc.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_listreceipt
AS
SELECT tb_ar_receipt.docdate, tb_ar_receipt.docno, 
    tb_ar_receipt.personid, tb_ar_person.Name AS personname, 
    tb_ar_receipt.taxno, tb_ar_receipt.description, 0 AS sumdr, 
    tb_ar_receipt.pay_chash + tb_ar_receipt.pay_cheque + tb_ar_receipt.pay_transfer
     + tb_ar_receipt.pay_other AS sumcr, 'D' AS doctype
FROM tb_ar_receipt LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_receipt.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_listsale
AS
SELECT tb_sal_saledebit.docno, tb_sal_saledebit.docdate, 
    tb_sal_saledebit.personid, 
    tb_ar_person.Name AS personname, tb_sal_saledebit.taxno, 
    tb_sal_saledebit.description, 
    tb_sal_saledebit.afterdiscount + tb_sal_saledebit.amountvat AS sumdr,
     0 AS sumcr, 'A' AS doctype
FROM tb_sal_saledebit LEFT OUTER JOIN
    tb_ar_person ON tb_sal_saledebit.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_sumpayment
AS
SELECT tb_ar_receipt_sub.taxno_ref, 
    SUM(ISNULL(tb_ar_receipt_sub.payment, 0)) 
    AS sumpayment, MAX(tb_ar_receipt.personid) 
    AS personid, MAX(tb_ar_receipt.docno) AS docno, 
    tb_ar_receipt_sub.docno_ref
FROM tb_ar_receipt INNER JOIN
    tb_ar_receipt_sub ON 
    tb_ar_receipt.docno = tb_ar_receipt_sub.docno
GROUP BY tb_ar_receipt_sub.taxno_ref, 
    tb_ar_receipt.docno, tb_ar_receipt_sub.docno_ref
GO

CREATE VIEW v_ar_payment_remain
AS
SELECT 'A_BF' AS doctype, docno AS docno_ref, 
    docdate AS docdate_ref, taxno AS taxno_ref, 
    due_date AS duedate_ref, personid, 
    'ยอดยกมา' AS description_ref, amount, amount - isnull
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_beginterm.docno),
     0) AS remain_pay, amount - isnull
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_beginterm.docno),
     0) AS payment
FROM tb_ar_beginterm
WHERE amount - isnull
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_beginterm.docno),
     0) <> 0
UNION ALL
SELECT 'B_N' AS doctype, docno AS docno_ref, 
    docdate AS docdate_ref, taxno AS taxno_ref, 
    duedate AS duedate_ref, personid, 'ตั้งหนี้' AS description_ref, 
    ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) AS amount, 
    (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0)) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_createar.sendno),
     0) AS remain_pay, (ISNULL(afterdiscount, 0) 
    + ISNULL(amountvat, 0)) - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_createar.sendno),
     0) AS payment
FROM tb_ar_createar
WHERE (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0)) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_createar.sendno),
     0) <> 0
UNION ALL
SELECT 'C_AJ' AS doctype, docno AS docno_ref, 
    docdate AS docdate_ref, taxno AS taxno_ref, 
    duedate AS duedate_ref, personid, 
    (CASE doc_effact WHEN '-' THEN 'ลดหนี้' ELSE 'เพิ่มหนี้' END) 
    AS description_ref, 
    (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0)) 
    AS amount, 
    (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_adjustar.sendno_ref),
     0)) AS remain_pay, 
    (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_adjustar.sendno_ref),
     0)) AS payment
FROM tb_ar_adjustar
WHERE (CASE doc_effact WHEN '-' THEN - 1 ELSE 1 END) 
    * (ISNULL(afterdiscount, 0) + ISNULL(amountvat, 0) 
    - ISNULL
        ((SELECT SUM(sumpayment)
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.docno_ref = tb_ar_adjustar.sendno_ref),
     0)) <> 0
GO

CREATE VIEW v_ar_remain_payment
AS
SELECT docno AS docno_ref, docdate AS docdate_ref, 
    taxno AS taxno_ref, doc_effact, 
    tb_ar_createar.duedate AS duedate_ref, 
    tb_ar_createar.description AS description_ref, 
    tb_ar_createar.personid AS personid, 
    afterdiscount + amountvat AS amount, 
    (CASE WHEN doc_effact = '+' THEN (afterdiscount + amountvat) 
    - isnull
        ((SELECT SUM(sumpayment) AS sumpayment
       FROM v_ar_sumpayment
       WHERE v_ar_sumpayment.taxno_ref = tb_ar_createar.taxno),
     0) ELSE 0 END) AS remain_pay, ISNULL(description, '') 
    AS description
FROM tb_ar_createar
GO

CREATE VIEW v_ar_rep_adjustar
AS
SELECT tb_ar_adjustar.docdate, tb_ar_adjustar.docno, 
    tb_ar_adjustar.taxno, tb_ar_adjustar.taxdate, 
    tb_ar_adjustar.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    tb_ar_adjustar.duedate, 
    tb_ar_adjustar.total AS amount, 
    tb_ar_adjustar.discount, tb_ar_adjustar.amountvat, 
    ISNULL(tb_ar_adjustar.afterdiscount, 0) 
    + ISNULL(tb_ar_adjustar.amountvat, 0) AS netamount, 
    tb_ar_adjustar.doc_effact
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid RIGHT OUTER
     JOIN
    tb_ar_adjustar ON 
    tb_ar_person.ID = tb_ar_adjustar.personid
GO

CREATE VIEW v_ar_rep_arperson
AS
SELECT tb_ar_person.id AS personid, 
    (ltrim(isnull(tb_sys_prefix.prefixname, '') + ' ') 
    + tb_ar_person.name + ' ') AS personname, 
    ltrim((Address + CASE WHEN Moo IS NULL OR
    Moo = '' THEN '' ELSE '  หมู่ที่ ' + Moo END + CASE WHEN Soi IS NULL
     OR
    Soi = '' THEN '' ELSE '  ซอย' + Soi END + CASE WHEN Road IS NULL
     OR
    Road = '' THEN '' ELSE '  ถนน' + Road END + CASE WHEN NOT (District
     IS NULL OR
    District = '') 
    THEN (CASE WHEN TumbonType = 0 THEN '  ตำบล' ELSE '  แขวง'
     END) 
    + District ELSE '' END + CASE WHEN NOT (Zone IS NULL OR
    Zone = '') 
    THEN (CASE WHEN AumperType = 0 THEN '  อำเภอ' ELSE '  เขต' END)
     + Zone ELSE '' END + CASE WHEN Province IS NULL OR
    Province = '' THEN '' ELSE '  จังหวัด' + Province END + ' ' + Postcode))
     AS Address, Telephone
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.prefixid = tb_sys_prefix.prefixid
GO

CREATE VIEW v_ar_rep_beginterm
AS
SELECT tb_ar_beginterm.docdate, tb_ar_beginterm.docno, 
    tb_ar_beginterm.taxno, ISNULL(tb_ar_beginterm.personid, '-') 
    AS personid, ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-') AS personname, 
    tb_ar_beginterm.amount AS total, tb_ar_beginterm.due_date, 
    tb_ar_beginterm.credit_term
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_ar_beginterm ON 
    tb_ar_person.ID = tb_ar_beginterm.personid
GO

CREATE VIEW v_ar_rep_beginterm_sum
AS
SELECT ISNULL(tb_ar_beginterm.personid, '-') AS personid, 
    MAX(ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-')) AS personname, 
    SUM(tb_ar_beginterm.amount) AS total
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_ar_beginterm ON 
    tb_ar_person.ID = tb_ar_beginterm.personid
GROUP BY tb_ar_beginterm.personid
GO

CREATE VIEW v_ar_rep_cardar AS
SELECT docno, docdate, personid, personname, taxno, taxno_ref, 
    description, sumdr, sumcr, doctype
FROM v_ar_list_bfterm
UNION ALL
SELECT docno, docdate, personid, personname, taxno, taxno_ref, 
    description, sumdr, sumcr, doctype
FROM v_ar_list_createar
UNION ALL
SELECT docno, docdate, personid, personname, taxno, taxno_ref, 
    description, sumdr, sumcr, doctype
FROM v_ar_list_adjustar
UNION ALL
SELECT docno, docdate, personid, personname, taxno, 
    '' AS taxno_ref, description, sumdr, sumcr, doctype
FROM v_ar_listreceipt
GO

CREATE VIEW v_ar_rep_createar
AS
SELECT tb_ar_createar.docdate, tb_ar_createar.docno, 
    tb_ar_createar.taxno, tb_ar_createar.taxdate, 
    tb_ar_createar.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    tb_ar_createar.duedate, 
    tb_ar_createar.total AS amount, 
    tb_ar_createar.discount, tb_ar_createar.amountvat, 
    ISNULL(tb_ar_createar.afterdiscount, 0) 
    + ISNULL(tb_ar_createar.amountvat, 0) 
    AS netamount
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid RIGHT OUTER
     JOIN
    tb_ar_createar ON 
    tb_ar_person.ID = tb_ar_createar.personid
GO

CREATE VIEW v_ar_rep_receipt
AS
SELECT tb_ar_receipt.docdate, tb_ar_receipt.docno, 
    tb_ar_receipt_sub.taxno_ref AS taxno, 
    tb_ar_receipt.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS name, 
    tb_ar_receipt.pay_transfer, tb_ar_receipt.pay_chash, 
    tb_ar_receipt.pay_cheque, tb_ar_receipt.pay_psd, 
    tb_ar_receipt.discount, 
    tb_ar_receipt.discount_percent, 
    tb_ar_receipt.pay_other, 
    ISNULL(tb_ar_receipt.pay_transfer, 0) 
    + ISNULL(tb_ar_receipt.pay_chash, 0) 
    + ISNULL(tb_ar_receipt.pay_cheque, 0) 
    + ISNULL(tb_ar_receipt.pay_psd, 0) 
    - ISNULL(tb_ar_receipt.discount, 0) 
    + ISNULL(tb_ar_receipt.pay_other, 0) AS grandtotal, 
    tb_ar_receipt_sub.payment, 
    tb_ar_receipt.holdbill
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_ar_receipt LEFT OUTER JOIN
    tb_ar_receipt_sub ON 
    tb_ar_receipt.docno = tb_ar_receipt_sub.docno ON 
    tb_ar_person.ID = tb_ar_receipt.personid
GO

CREATE VIEW v_ar_sadjustar_dec
AS
SELECT tb_ar_adjustar.docno, tb_ar_adjustar.docdate, 
    tb_ar_adjustar.personid, tb_ar_person.Name AS personname, 
    tb_ar_adjustar.apprecord
FROM tb_ar_adjustar LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_adjustar.personid = tb_ar_person.ID
WHERE (tb_ar_adjustar.doc_effact = '-')
GO

CREATE VIEW v_ar_sadjustar_inc
AS
SELECT tb_ar_adjustar.docno, tb_ar_adjustar.docdate, 
    tb_ar_adjustar.personid, tb_ar_person.Name AS personname, 
    tb_ar_adjustar.apprecord
FROM tb_ar_adjustar LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_adjustar.personid = tb_ar_person.ID
WHERE (tb_ar_adjustar.doc_effact = '+')
GO

CREATE VIEW v_ar_sbeginterm
AS
SELECT tb_ar_beginterm.docno, tb_ar_beginterm.docdate, 
    tb_ar_beginterm.taxno, tb_ar_person.Name
FROM tb_ar_beginterm LEFT OUTER JOIN
    tb_ar_person ON tb_ar_beginterm.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_sbill
AS
SELECT tb_ar_bill.docno, tb_ar_bill.docdate, tb_ar_bill.personid, 
    ISNULL(tb_ar_person.Name, '') AS personname, 
    tb_ar_bill.duedate, ISNULL
        ((SELECT SUM(holdbill)
       FROM tb_ar_bill_sub
       WHERE tb_ar_bill_sub.docno = tb_ar_bill.docno), 0) 
    AS holdbill
FROM tb_ar_bill LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_bill.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_screatear
AS
SELECT tb_ar_createar.apprecord, tb_ar_createar.docno, 
    tb_ar_createar.docdate, tb_ar_createar.taxno, 
    ISNULL(tb_ar_createar.personid, '-') AS personid, 
    ISNULL(tb_ar_person.Name, '-') AS personname, 
    tb_ar_createar.sendno
FROM tb_ar_createar LEFT OUTER JOIN
    tb_ar_person ON 
    tb_ar_createar.personid = tb_ar_person.ID
GO

CREATE VIEW v_ar_sperson
AS
SELECT tb_ar_person.ID, 
    ISNULL(tb_sys_prefix.prefixname, '') AS prefixname, 
    tb_ar_person.Name
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_ar_sreceipt
AS
SELECT tb_ar_receipt.docno, tb_ar_receipt.docdate, 
    tb_ar_receipt.taxno, ISNULL(tb_ar_receipt.personid, '-') 
    AS personid, ISNULL(tb_ar_person.Name, '-') 
    AS personname
FROM tb_ar_receipt LEFT OUTER JOIN
    tb_ar_person ON tb_ar_receipt.personid = tb_ar_person.ID
GO

create view v_chq_chequeresult as
SELECT tb_chq_chequeresult.docdate, 
    tb_chq_chequeresult.acc_id, 
case tb_chq_chequeresult.doctype 
	when 'IP' then tb_chq_chequeresult_sub.netamount
	when 'OP' then tb_chq_chequeresult_sub.netamount * -1
	else 0
end as amount
FROM tb_chq_chequeresult_sub INNER JOIN
    tb_chq_chequeresult ON 
    tb_chq_chequeresult_sub.docno = tb_chq_chequeresult.docno
     AND 
    tb_chq_chequeresult_sub.doctype = tb_chq_chequeresult.doctype
GO

CREATE VIEW v_chq_depositdraw
AS
SELECT docdate, acc_id, 
    CASE flagstatus WHEN '+' THEN amount WHEN '-' THEN - amount
     ELSE 0 END AS amount
FROM tb_chq_depositdraw
GO

CREATE VIEW v_chq_incomeexpense
AS
SELECT docdate, acc_id, 
    CASE flagstatus WHEN '+' THEN amount WHEN '-' THEN - amount
     ELSE 0 END AS amount
FROM tb_chq_incomeexpense
GO

CREATE VIEW v_chq_intrans
AS
SELECT tb_chq_chequetrans.chqno, tb_chq_chequetrans.chqdate, 
    tb_chq_chequetrans.acc_id, tb_sys_bankbook.acc_name, 
    tb_chq_chequetrans.bankid, tb_sys_bank.BankName, 
    tb_chq_chequetrans.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_chq_chequetrans.chqstatus, tb_chq_chequetrans.docno, 
    tb_chq_chequetrans.docdate, tb_chq_chequetrans.personid, 
    isnull(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    CASE chqstatus WHEN 'W' THEN amount ELSE 0 END AS chqwait,
     CASE chqstatus WHEN 'P' THEN amount ELSE 0 END AS chqpass,
     CASE chqstatus WHEN 'R' THEN amount ELSE 0 END AS chqreturn
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_chq_chequetrans ON 
    tb_ar_person.ID = tb_chq_chequetrans.personid LEFT OUTER
     JOIN
    tb_sys_bankbook ON 
    tb_chq_chequetrans.acc_id = tb_sys_bankbook.acc_id LEFT
     OUTER JOIN
    tb_sys_bankbranch ON 
    tb_chq_chequetrans.bankbranchid = tb_sys_bankbranch.bankbranchid
     LEFT OUTER JOIN
    tb_sys_bank ON 
    tb_chq_chequetrans.bankid = tb_sys_bank.BankID
WHERE (tb_chq_chequetrans.inout = 'I')
GO

CREATE VIEW v_chq_listchequeresult
AS
SELECT tb_chq_chequeresult.docno, 
    tb_chq_chequeresult.docdate, 
    tb_chq_chequeresult_sub.chqno, 
    tb_chq_chequetrans.paydesc AS description, 
    tb_chq_chequeresult.acc_id, 
    CASE tb_chq_chequetrans.inout WHEN 'I' THEN tb_chq_chequetrans.amount
     - feeamount ELSE 0 END AS amountin, 
    CASE tb_chq_chequetrans.inout WHEN 'O' THEN tb_chq_chequetrans.amount
     + feeamount ELSE 0 END AS amountout
FROM tb_chq_chequeresult_sub INNER JOIN
    tb_chq_chequeresult ON 
    tb_chq_chequeresult_sub.docno = tb_chq_chequeresult.docno
     AND 
    tb_chq_chequeresult_sub.doctype = tb_chq_chequeresult.doctype
     INNER JOIN
    tb_chq_chequetrans ON 
    tb_chq_chequeresult_sub.chqno = tb_chq_chequetrans.chqno
     AND 
    tb_chq_chequeresult_sub.bankid = tb_chq_chequetrans.bankid
GO

create view v_chq_listdepositdraw as
SELECT docno, docdate, '' AS chqno, 
case  flagstatus
 when '+'  then 'ฝากเงินสด' 
 when '-'  then 'ถอนเงินสด' 
else '' end  AS description,
 acc_id, 
case  flagstatus  when '+'  then amount else 0 end  AS amountin,
case  flagstatus  when '-'  then amount else 0 end  AS amountout,
 flagstatus
FROM tb_chq_depositdraw
GO

CREATE VIEW v_chq_listtransfer
AS
SELECT docno, docdate, chqno, 'โอนเงินออก' AS description, 
    acc_id_out AS acc_id, 0 AS amountin, 
    amount + fee_account AS amountout, '-' AS flagstatus
FROM tb_chq_transfer
UNION ALL
SELECT docno, docdate, chqno, 'โอนเงินเข้า' AS description, 
    acc_id_in AS acc_id, amount AS amountin, 0 AS amountout, 
    '+' AS flagstatus
FROM tb_chq_transfer
GO

CREATE VIEW v_chq_listtransfermoney
AS
SELECT docno_ref AS docno, docdate_ref AS docdate, '' AS chqno, 
    CASE flagstatus WHEN '+' THEN 'รับเงินโอน' WHEN '-' THEN 'จ่ายเงินโอน'
     ELSE '' END AS description, acc_bookid, 
    CASE flagstatus WHEN '+' THEN amount ELSE 0 END AS amountin,
     CASE flagstatus WHEN '-' THEN amount ELSE 0 END AS amountout,
     CASE feeeffact WHEN 'Y' THEN Amount_Cenvention ELSE 0 END
     AS feeamount, flagstatus
FROM tb_sys_transfermoney
GO

CREATE VIEW v_chq_outtrans
AS
SELECT tb_chq_chequetrans.chqno, tb_chq_chequetrans.chqdate, 
    tb_chq_chequetrans.acc_id, tb_sys_bankbook.acc_name, 
    tb_chq_chequetrans.bankid, tb_sys_bank.BankName, 
    tb_chq_chequetrans.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_chq_chequetrans.chqstatus, tb_chq_chequetrans.docno, 
    tb_chq_chequetrans.docdate, tb_chq_chequetrans.personid, 
    isnull(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ap_person.Name AS personname, 
    CASE chqstatus WHEN 'W' THEN amount ELSE 0 END AS chqwait,
     CASE chqstatus WHEN 'P' THEN amount ELSE 0 END AS chqpass,
     CASE chqstatus WHEN 'R' THEN amount ELSE 0 END AS chqreturn
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_chq_chequetrans ON 
    tb_ap_person.ID = tb_chq_chequetrans.personid LEFT OUTER
     JOIN
    tb_sys_bankbook ON 
    tb_chq_chequetrans.acc_id = tb_sys_bankbook.acc_id LEFT
     OUTER JOIN
    tb_sys_bankbranch ON 
    tb_chq_chequetrans.bankbranchid = tb_sys_bankbranch.bankbranchid
     LEFT OUTER JOIN
    tb_sys_bank ON 
    tb_chq_chequetrans.bankid = tb_sys_bank.BankID
WHERE (tb_chq_chequetrans.inout = 'O')
GO

CREATE VIEW v_chq_rep_chequedeposit
AS
SELECT tb_chq_depositchq.docno, tb_chq_depositchq.docdate, 
    tb_chq_depositchq.acc_id, tb_chq_depositchq.description, 
    tb_chq_depositchq_sub.chqno, tb_chq_chequetrans.bankid, 
    tb_chq_chequetrans.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    tb_chq_depositchq_sub.amount, 
    tb_chq_depositchq_sub.feeamount, 
    tb_chq_depositchq_sub.netamount, 
    tb_chq_depositchq_sub.chqdate
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_chq_chequetrans LEFT OUTER JOIN
    tb_ar_person ON 
    tb_chq_chequetrans.personid = tb_ar_person.ID LEFT OUTER
     JOIN
    tb_sys_bankbranch ON 
    tb_chq_chequetrans.bankbranchid = tb_sys_bankbranch.bankbranchid
     RIGHT OUTER JOIN
    tb_chq_depositchq INNER JOIN
    tb_chq_depositchq_sub ON 
    tb_chq_depositchq.docno = tb_chq_depositchq_sub.docno
     ON 
    tb_chq_chequetrans.chqno = tb_chq_depositchq_sub.chqno
     AND 
    tb_chq_chequetrans.bankid = tb_chq_depositchq_sub.bankid
     ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID
WHERE (tb_chq_chequetrans.inout = 'I')
GO

CREATE VIEW v_chq_rep_deposit
AS
SELECT tb_chq_depositdraw.docno, 
    tb_chq_depositdraw.docdate, 
    tb_chq_depositdraw.acc_id, 
    tb_sys_bankbook.acc_name, 
    tb_sys_bankbook.bankid, 
    tb_sys_bankbook.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_chq_depositdraw.amount, 
    tb_chq_depositdraw.flagstatus
FROM tb_chq_depositdraw INNER JOIN
    tb_sys_bankbook ON 
    tb_chq_depositdraw.acc_id = tb_sys_bankbook.acc_id LEFT
     OUTER JOIN
    tb_sys_bankbranch ON 
    tb_sys_bankbook.bankbranchid = tb_sys_bankbranch.bankbranchid
WHERE (tb_chq_depositdraw.flagstatus = '+')
GO

CREATE VIEW v_chq_rep_draw
AS
SELECT tb_chq_depositdraw.docno, 
    tb_chq_depositdraw.docdate, 
    tb_chq_depositdraw.acc_id, 
    tb_sys_bankbook.acc_name, 
    tb_sys_bankbook.bankid, 
    tb_sys_bankbook.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_chq_depositdraw.amount, 
    tb_chq_depositdraw.flagstatus
FROM tb_chq_depositdraw INNER JOIN
    tb_sys_bankbook ON 
    tb_chq_depositdraw.acc_id = tb_sys_bankbook.acc_id LEFT
     OUTER JOIN
    tb_sys_bankbranch ON 
    tb_sys_bankbook.bankbranchid = tb_sys_bankbranch.bankbranchid
WHERE (tb_chq_depositdraw.flagstatus = '-')
GO

CREATE VIEW v_chq_rep_expense
AS
SELECT tb_chq_incomeexpense.docno, 
    tb_chq_incomeexpense.docdate, 
    tb_chq_incomeexpense.acc_id, 
    tb_sys_bankbook.acc_name, 
    tb_sys_bankbook.bankid, 
    tb_sys_bankbook.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_chq_incomeexpense.amount, 
    tb_chq_incomeexpense.amountpsd, 
    tb_chq_incomeexpense.flagstatus
FROM tb_sys_bankbranch RIGHT OUTER JOIN
    tb_sys_bankbook ON 
    tb_sys_bankbranch.bankbranchid = tb_sys_bankbook.bankbranchid
     RIGHT OUTER JOIN
    tb_chq_incomeexpense ON 
    tb_sys_bankbook.acc_id = tb_chq_incomeexpense.acc_id
WHERE (tb_chq_incomeexpense.flagstatus = '-')
GO

CREATE VIEW v_chq_rep_income
AS
SELECT tb_chq_incomeexpense.docno, 
    tb_chq_incomeexpense.docdate, 
    tb_chq_incomeexpense.acc_id, 
    tb_sys_bankbook.acc_name, 
    tb_sys_bankbook.bankid, 
    tb_sys_bankbook.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_chq_incomeexpense.amount, 
    tb_chq_incomeexpense.amountpsd, 
    tb_chq_incomeexpense.flagstatus
FROM tb_sys_bankbranch RIGHT OUTER JOIN
    tb_sys_bankbook ON 
    tb_sys_bankbranch.bankbranchid = tb_sys_bankbook.bankbranchid
     RIGHT OUTER JOIN
    tb_chq_incomeexpense ON 
    tb_sys_bankbook.acc_id = tb_chq_incomeexpense.acc_id
WHERE (tb_chq_incomeexpense.flagstatus = '+')
GO

CREATE VIEW v_chq_rep_transferbook
AS
SELECT tb_chq_transfer.docno, tb_chq_transfer.docdate, 
    tb_chq_transfer.acc_id_out AS accid_out, 
    tb_sys_bankbook.acc_name AS accname_out, 
    tb_sys_bankbook.bankid AS bankid_out, 
    tb_chq_transfer.acc_id_in AS accid_in, 
    tb_sys_bankbook1.acc_name AS accname_in, 
    tb_sys_bankbook1.bankid AS bankid_in, 
    tb_chq_transfer.chqno, tb_chq_transfer.amount
FROM tb_chq_transfer LEFT OUTER JOIN
    tb_sys_bankbook tb_sys_bankbook1 ON 
    tb_chq_transfer.acc_id_in = tb_sys_bankbook1.acc_id LEFT
     OUTER JOIN
    tb_sys_bankbook ON 
    tb_chq_transfer.acc_id_out = tb_sys_bankbook.acc_id
GO

CREATE VIEW v_chq_transfer
AS
SELECT docdate, acc_id_out AS acc_id, - (amount + fee_account) 
    AS amount
FROM tb_chq_transfer
UNION ALL
SELECT docdate, acc_id_in AS acc_id, amount AS amount
FROM tb_chq_transfer
GO

CREATE VIEW v_chq_transfermoney
AS
SELECT docdate_ref AS docdate, acc_bookid AS acc_id, 
    CASE flagstatus WHEN '+' THEN amount WHEN '-' THEN - amount
     ELSE 0 END AS amount, 
    CASE feeeffact WHEN 'Y' THEN Amount_Cenvention ELSE 0 END
     AS feeamount
FROM tb_sys_transfermoney
GO

CREATE VIEW v_chq_accremain
AS
SELECT docdate, acc_id, amount
FROM v_chq_chequeresult
UNION ALL
SELECT docdate, acc_id, amount
FROM v_chq_depositdraw
UNION ALL
SELECT docdate, acc_id, amount
FROM v_chq_incomeexpense
UNION ALL
SELECT docdate, acc_id, amount
FROM v_chq_transfer
UNION ALL
SELECT docdate, acc_id, amount + feeamount
FROM v_chq_transfermoney
GO

CREATE VIEW v_chq_bankstatement
AS
SELECT docno, docdate, chqno, description, acc_id, amountin, 
    amountout
FROM v_chq_listdepositdraw
UNION ALL
SELECT docno, docdate, chqno, description, acc_id, amountin, 
    amountout
FROM v_chq_listchequeresult
UNION ALL
SELECT docno, docdate, chqno, description, acc_id, amountin, 
    amountout
FROM v_chq_listtransfer
UNION ALL
SELECT docno, docdate, chqno, description, acc_bookid, 
    CASE WHEN amountin <> 0 THEN amountin + feeamount ELSE 0
     END AS amountin, 
    CASE WHEN amountout <> 0 THEN amountout + feeamount ELSE
     0 END AS amountout
FROM v_chq_listtransfermoney
GO

CREATE VIEW v_gl_rep_expense
AS
SELECT tb_gl_expense.docdate, tb_gl_expense.docno, 
    tb_gl_expense.taxno, tb_gl_expense.personid, 
    tb_sys_prefix.prefixname + ' ' + tb_ap_person.Name AS personname,
     tb_gl_expense.total AS amount, tb_gl_expense.discount, 
    tb_gl_expense.amountvat, ISNULL(tb_gl_expense.afterdiscount, 
    0) + ISNULL(tb_gl_expense.amountvat, 0) AS total
FROM tb_gl_expense LEFT OUTER JOIN
    tb_ap_person ON 
    tb_gl_expense.personid = tb_ap_person.ID LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ap_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_gl_rep_income
AS
SELECT tb_gl_income.docdate, tb_gl_income.docno, 
    tb_gl_income.taxno, tb_gl_income.personid, 
    tb_sys_prefix.prefixname + ' ' + tb_ar_person.Name AS personname,
     tb_gl_income.total AS amount, tb_gl_income.discount, 
    tb_gl_income.amountvat, ISNULL(tb_gl_income.afterdiscount, 
    0) + ISNULL(tb_gl_income.amountvat, 0) AS total
FROM tb_gl_income LEFT OUTER JOIN
    tb_ar_person ON 
    tb_gl_income.personid = tb_ar_person.ID LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_gl_rep_pattern_sys
AS
SELECT tb_gl_pattern_voucher.patternid, 
    tb_gl_pattern_voucher.patternname, 
    tb_gl_pattern_voucher.branchid, 
    tb_sys_branch.branchname, 
    tb_gl_pattern_voucher.bookid, tb_gl_book.bookname, 
    tb_gl_pattern_voucher.description, 
    tb_gl_pattern_voucher_sub.row_desc, 
    tb_gl_pattern_voucher_sub.accno, 
    tb_gl_accchart.accname, 
    tb_gl_pattern_voucher_sub.drcr, 
    tb_gl_pattern_voucher_sub.filltype, 
    tb_gl_pattern_voucher_sub.rowitem
FROM tb_gl_accchart RIGHT OUTER JOIN
    tb_gl_pattern_voucher_sub ON 
    tb_gl_accchart.accno = tb_gl_pattern_voucher_sub.accno
     RIGHT OUTER JOIN
    tb_gl_pattern_voucher LEFT OUTER JOIN
    tb_gl_book ON 
    tb_gl_pattern_voucher.bookid = tb_gl_book.bookid LEFT
     OUTER JOIN
    tb_sys_branch ON 
    tb_gl_pattern_voucher.branchid = tb_sys_branch.branchid
     ON 
    tb_gl_pattern_voucher_sub.patternid = tb_gl_pattern_voucher.patternid
GO

CREATE VIEW v_gl_rep_pattern_user
AS
SELECT tb_gl_template.tempid, tb_gl_template.tempname, 
    tb_gl_template.branchid, tb_sys_branch.branchname, 
    tb_gl_template.bookid, tb_gl_book.bookname, 
    tb_gl_template.description, 
    tb_gl_variable.varname AS row_desc, 
    tb_gl_template_sub.accno, tb_gl_accchart.accname, 
    tb_gl_template_sub.drcr, 
    tb_gl_template_sub.rowitem
FROM tb_gl_book RIGHT OUTER JOIN
    tb_gl_accchart RIGHT OUTER JOIN
    tb_gl_template LEFT OUTER JOIN
    tb_gl_variable RIGHT OUTER JOIN
    tb_gl_template_sub ON 
    tb_gl_variable.varid = tb_gl_template_sub.varid ON 
    tb_gl_template.tempid = tb_gl_template_sub.tempid ON
     tb_gl_accchart.accno = tb_gl_template_sub.accno ON 
    tb_gl_book.bookid = tb_gl_template.bookid LEFT OUTER
     JOIN
    tb_sys_branch ON 
    tb_gl_template.branchid = tb_sys_branch.branchid
GO

CREATE VIEW v_gl_sbook
AS
SELECT tb_sys_bankbook.acc_id, tb_sys_bankbook.acc_name, 
    tb_sys_bank.BankName, tb_sys_bankbook.acc_type
FROM tb_sys_bankbook LEFT OUTER JOIN
    tb_sys_bank ON tb_sys_bankbook.bankid = tb_sys_bank.BankID
GO

CREATE VIEW v_gl_sexpense
AS
SELECT tb_gl_expense.docno, tb_gl_expense.docdate, 
    tb_gl_expense.personid, 
    tb_ap_person.Name AS personname, 
    tb_gl_expense.description
FROM tb_gl_expense LEFT OUTER JOIN
    tb_ap_person ON tb_gl_expense.personid = tb_ap_person.ID
GO

CREATE VIEW v_gl_sincome
AS
SELECT tb_gl_income.docno, tb_gl_income.docdate, 
    tb_gl_income.personid, tb_ar_person.Name AS personname, 
    tb_gl_income.description
FROM tb_gl_income LEFT OUTER JOIN
    tb_ar_person ON tb_gl_income.personid = tb_ar_person.ID
GO

CREATE VIEW v_ic_caladjuststock
AS
SELECT tb_ic_adjuststock.docno, tb_ic_adjuststock.docdate, 
    '' AS personid, tb_ic_adjuststock.description, 
    tb_ic_adjuststock_sub.ProductID, 
    tb_ic_adjuststock_sub.ProductName, 
    tb_ic_adjuststock_sub.storeid, tb_ic_adjuststock_sub.unitid, 
    tb_ic_adjuststock_sub.qty AS AMOUNT, 
    tb_ic_adjuststock_sub.costperunit, 
    tb_ic_adjuststock.doctype AS EFFACTSTOCK, 
    'V' AS DOCTYPE
FROM tb_ic_adjuststock INNER JOIN
    tb_ic_adjuststock_sub ON 
    tb_ic_adjuststock.doctype = tb_ic_adjuststock_sub.doctype
GO

CREATE VIEW v_ic_calbfterm
AS
SELECT tb_ic_beginterm.docno, tb_ic_beginterm.docdate, 
    '' AS personid, tb_ic_beginterm.description, 
    tb_ic_beginterm_sub.ProductID, 
    tb_ic_beginterm_sub.ProductName, 
    tb_ic_beginterm_sub.storeid, tb_ic_beginterm_sub.unitid, 
    tb_ic_beginterm_sub.qty AS AMOUNT, 
    tb_ic_beginterm_sub.costperunit, '+' AS EFFACTSTOCK, 
    'A' AS DOCTYPE
FROM tb_ic_beginterm INNER JOIN
    tb_ic_beginterm_sub ON 
    tb_ic_beginterm.docno = tb_ic_beginterm_sub.docno
GO

CREATE VIEW v_ic_caltrans
AS
SELECT tb_ic_trans.docno, tb_ic_trans.docdate, 
    '' AS personid, tb_ic_trans.description, 
    tb_ic_trans_sub.ProductID, 
    tb_ic_trans_sub.ProductName, 
    tb_ic_trans_sub.storeid, tb_ic_trans_sub.unitid, 
    tb_ic_trans_sub.qty AS AMOUNT, 
    tb_ic_trans_sub.costperunit, 
    (CASE tb_ic_trans.flagdraw WHEN 'O' THEN '-' ELSE '+' END)
     AS EFFACTSTOCK, 
    (CASE tb_ic_trans.flagdraw WHEN 'O' THEN 'UB' ELSE 'UA' END)
     AS DOCTYPE
FROM tb_ic_trans INNER JOIN
    tb_ic_trans_sub ON 
    tb_ic_trans.docno = tb_ic_trans_sub.docno
GO

CREATE VIEW v_ic_caltransdraw
AS
SELECT tb_ic_transdraw.docno, tb_ic_transdraw.docdate, 
    '' AS personid, tb_ic_transdraw.description, 
    tb_ic_transdraw_sub.ProductID, 
    tb_ic_transdraw_sub.ProductName, 
    tb_ic_transdraw_sub.storeid, 
    tb_ic_transdraw_sub.unitid, 
    tb_ic_transdraw_sub.qty AS AMOUNT, 
    tb_ic_transdraw_sub.costperunit, 
    (CASE tb_ic_transdraw.flagdraw WHEN 'O' THEN '-' ELSE '+'
     END) AS EFFACTSTOCK, 
    (CASE tb_ic_transdraw.flagdraw WHEN 'O' THEN 'TB' ELSE 'TA'
     END) AS DOCTYPE
FROM tb_ic_transdraw INNER JOIN
    tb_ic_transdraw_sub ON 
    tb_ic_transdraw.docno = tb_ic_transdraw_sub.docno
GO

CREATE VIEW v_ic_list_adjuststock
AS
SELECT tb_ic_adjuststock.docno, 
    tb_ic_adjuststock.docdate, 
    tb_ic_adjuststock_sub.ProductID, 
    tb_ic_adjuststock_sub.ProductName, 
    tb_ic_adjuststock_sub.unitid, 
    tb_ic_adjuststock_sub.storeid, 
    (CASE tb_ic_adjuststock.doctype WHEN '+' THEN 'ปรับปรุงสินค้า เพิ่ม'
     ELSE 'ปรับปรุงสินค้า ลด' END) AS description, 
    (CASE tb_ic_adjuststock.doctype WHEN '+' THEN tb_ic_adjuststock_sub.qty
     ELSE 0 END) AS sumin, 
    (CASE tb_ic_adjuststock.doctype WHEN '-' THEN tb_ic_adjuststock_sub.qty
     ELSE 0 END) AS sumout, 'G' AS doctype
FROM tb_ic_adjuststock INNER JOIN
    tb_ic_adjuststock_sub ON 
    tb_ic_adjuststock.docno = tb_ic_adjuststock_sub.docno
GO

CREATE VIEW v_ic_list_bfterm
AS
SELECT tb_ic_beginterm.docno, tb_ic_beginterm.docdate, 
    tb_ic_beginterm_sub.ProductID, 
    tb_ic_beginterm_sub.ProductName, 
    tb_ic_beginterm_sub.unitid, tb_ic_beginterm_sub.storeid, 
    'สินค้าคงเหลือยกมา' AS description, 
    tb_ic_beginterm_sub.qty AS sumin, 0 AS sumout, 
    'A' AS doctype
FROM tb_ic_beginterm INNER JOIN
    tb_ic_beginterm_sub ON 
    tb_ic_beginterm.docno = tb_ic_beginterm_sub.docno
GO

CREATE VIEW v_ic_list_trans
AS
SELECT tb_ic_trans.docno, tb_ic_trans.docdate, 
    tb_ic_trans_sub.ProductID, 
    tb_ic_trans_sub.ProductName, tb_ic_trans_sub.unitid, 
    tb_ic_trans_sub.storeid, 
    (CASE tb_ic_trans.flagdraw WHEN 'I' THEN 'รับ สินค้าสำเร็จรูป'
     ELSE 'คืน สินค้าสำเร็จรูป' END) AS description, 
    (CASE tb_ic_trans.flagdraw WHEN 'I' THEN tb_ic_trans_sub.qty
     ELSE 0 END) AS sumin, 
    (CASE tb_ic_trans.flagdraw WHEN 'O' THEN tb_ic_trans_sub.qty
     ELSE 0 END) AS sumout, 
    (CASE tb_ic_trans.flagdraw WHEN 'I' THEN 'E' ELSE 'F' END)
     AS doctype
FROM tb_ic_trans INNER JOIN
    tb_ic_trans_sub ON 
    tb_ic_trans.docno = tb_ic_trans_sub.docno
GO

CREATE VIEW v_ic_list_transdraw
AS
SELECT tb_ic_transdraw.docno, tb_ic_transdraw.docdate, 
    tb_ic_transdraw_sub.ProductID, 
    tb_ic_transdraw_sub.ProductName, 
    tb_ic_transdraw_sub.unitid, 
    tb_ic_transdraw_sub.storeid, 
    (CASE tb_ic_transdraw.flagdraw WHEN 'I' THEN 'คืน สินค้า' ELSE
     'เบิก สินค้า' END) AS description, 
    (CASE tb_ic_transdraw.flagdraw WHEN 'I' THEN tb_ic_transdraw_sub.qty
     ELSE 0 END) AS sumin, 
    (CASE tb_ic_transdraw.flagdraw WHEN 'O' THEN tb_ic_transdraw_sub.qty
     ELSE 0 END) AS sumout, 
    (CASE tb_ic_transdraw.flagdraw WHEN 'O' THEN 'C' ELSE 'D'
     END) AS doctype
FROM tb_ic_transdraw INNER JOIN
    tb_ic_transdraw_sub ON 
    tb_ic_transdraw.docno = tb_ic_transdraw_sub.docno
GO

CREATE VIEW v_ic_list_warehouse
AS
SELECT tb_ic_warehouse.docno, tb_ic_warehouse.docdate, 
    tb_ic_warehouse_sub.ProductID, 
    tb_ic_warehouse_sub.ProductName, 
    tb_ic_warehouse_sub.unitid, 
    tb_ic_warehouse_sub.storeid, 
    tb_ic_warehouse.description, 
    (CASE tb_ic_warehouse.effactstock WHEN '+' THEN tb_ic_warehouse_sub.amount
     ELSE 0 END) AS sumin, 
    (CASE tb_ic_warehouse.effactstock WHEN '-' THEN tb_ic_warehouse_sub.amount
     ELSE 0 END) AS sumout, 
    (CASE tb_ic_warehouse.effactstock WHEN '+' THEN 'A' ELSE
     'B' END) AS doctype
FROM tb_ic_warehouse INNER JOIN
    tb_ic_warehouse_sub ON 
    tb_ic_warehouse.docno = tb_ic_warehouse_sub.docno
GO

create view v_ic_remain as
SELECT productid, storeid, unitid,
sum(case  flagstatus
 when '+'  then qty
 when '-'  then -qty
else 0 end)  AS amount
FROM tb_ic_remain
GROUP BY productid, storeid, unitid
GO

CREATE VIEW v_ic_rep_adjuststock
AS
SELECT tb_ic_adjuststock.doctype, 
    tb_ic_adjuststock.docno, tb_ic_adjuststock.docdate, 
    tb_ic_adjuststock.description, 
    tb_ic_adjuststock_sub.ProductID, 
    tb_ic_adjuststock_sub.ProductName, 
    tb_ic_adjuststock_sub.storeid, tb_ic_store.storename, 
    tb_ic_adjuststock_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_adjuststock_sub.qty, 
    tb_ic_adjuststock_sub.costperunit, 
    tb_ic_adjuststock_sub.total, 
    tb_ic_adjuststock_sub.rowitem
FROM tb_ic_adjuststock LEFT OUTER JOIN
    tb_ic_adjuststock_sub ON 
    tb_ic_adjuststock.docno = tb_ic_adjuststock_sub.docno
     AND 
    tb_ic_adjuststock.doctype = tb_ic_adjuststock_sub.doctype
     LEFT OUTER JOIN
    tb_ic_store ON 
    tb_ic_adjuststock_sub.storeid = tb_ic_store.storeid LEFT
     OUTER JOIN
    tb_ic_unit ON 
    tb_ic_adjuststock_sub.unitid = tb_ic_unit.unitid
GO

CREATE VIEW v_ic_rep_beginterm
AS
SELECT tb_ic_beginterm.docno, tb_ic_beginterm.docdate, 
    tb_ic_beginterm.description, 
    tb_ic_beginterm_sub.ProductID, 
    tb_ic_beginterm_sub.ProductName, 
    tb_ic_beginterm_sub.storeid, 
    tb_ic_beginterm_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_beginterm_sub.qty, 
    tb_ic_beginterm_sub.costperunit, 
    tb_ic_beginterm_sub.total, 
    tb_ic_beginterm_sub.rowitem
FROM tb_ic_unit RIGHT OUTER JOIN
    tb_ic_beginterm_sub ON 
    tb_ic_unit.unitid = tb_ic_beginterm_sub.unitid RIGHT OUTER
     JOIN
    tb_ic_beginterm ON 
    tb_ic_beginterm_sub.docno = tb_ic_beginterm.docno
GO

CREATE VIEW v_ic_rep_cost
AS
SELECT tb_ic_stockcard.DOCNO, 
    tb_ic_stockcard.DOCDATE, 
    ISNULL(tb_ic_stockcard.IN_QTY, 0) AS IN_QTY, 
    ISNULL(tb_ic_stockcard.IN_COST, 0) AS IN_COST, 
    ISNULL(tb_ic_stockcard.IN_AMOUNT, 0) AS IN_AMOUNT, 
    ISNULL(tb_ic_stockcard.OUT_QTY, 0) AS OUT_QTY, 
    ISNULL(tb_ic_stockcard.OUT_COST, 0) AS OUT_COST, 
    ISNULL(tb_ic_stockcard.OUT_AMOUNT, 0) 
    AS OUT_AMOUNT, ISNULL(tb_ic_stockcard.BAL_QTY, 0) 
    AS BAL_QTY, ISNULL(tb_ic_stockcard.BAL_COST, 0) 
    AS BAL_COST, ISNULL(tb_ic_stockcard.BAL_AMOUNT, 0) 
    AS BAL_AMOUNT, tb_ic_stockcard.UNITID, 
    tb_ic_unit.unitname, tb_ic_stockcard.PROID, 
    tb_ic_product.ProductName, 
    tb_ic_stockcard.ITEMNO
FROM tb_ic_stockcard LEFT OUTER JOIN
    tb_ic_unit ON 
    tb_ic_stockcard.UNITID = tb_ic_unit.unitid LEFT OUTER JOIN
    tb_ic_product ON 
    tb_ic_stockcard.PROID = tb_ic_product.ProductID
GO

CREATE VIEW v_ic_rep_countstock
AS
SELECT tb_ic_countstock.docno, tb_ic_countstock.docdate, 
    tb_ic_countstock.empcount, 
    tb_sys_employee.EmpName, 
    tb_ic_countstock.description, 
    tb_ic_countstock_sub.productid, 
    tb_ic_countstock_sub.productname, 
    tb_ic_countstock_sub.storeid, tb_ic_store.storename, 
    tb_ic_countstock_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_countstock_sub.qty, 
    tb_ic_countstock_sub.countst, 
    tb_ic_countstock_sub.diffst, 
    tb_ic_countstock_sub.description AS row_description, 
    tb_ic_countstock_sub.rowitem
FROM tb_ic_store RIGHT OUTER JOIN
    tb_ic_countstock_sub ON 
    tb_ic_store.storeid = tb_ic_countstock_sub.storeid LEFT
     OUTER JOIN
    tb_ic_unit ON 
    tb_ic_countstock_sub.unitid = tb_ic_unit.unitid RIGHT OUTER
     JOIN
    tb_ic_countstock LEFT OUTER JOIN
    tb_sys_employee ON 
    tb_ic_countstock.empcount = tb_sys_employee.EmpID ON
     tb_ic_countstock_sub.docno = tb_ic_countstock.docno
GO

CREATE VIEW v_ic_rep_countstock_doc
AS
SELECT tb_ic_countstock_doc.docno, 
    tb_ic_countstock_doc.docdate, 
    tb_ic_countstock_doc_sub.productid, 
    tb_ic_countstock_doc_sub.productname, 
    tb_ic_countstock_doc_sub.storeid, 
    tb_ic_store.storename, 
    tb_ic_countstock_doc_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_countstock_doc_sub.qty, 
    tb_ic_countstock_doc_sub.rowitem
FROM tb_ic_unit RIGHT OUTER JOIN
    tb_ic_countstock_doc_sub ON 
    tb_ic_unit.unitid = tb_ic_countstock_doc_sub.unitid LEFT
     OUTER JOIN
    tb_ic_store ON 
    tb_ic_countstock_doc_sub.storeid = tb_ic_store.storeid RIGHT
     OUTER JOIN
    tb_ic_countstock_doc ON 
    tb_ic_countstock_doc_sub.docno = tb_ic_countstock_doc.docno
GO

CREATE VIEW v_Ic_rep_productraw
AS
SELECT docno, docdate, productid, productname, unitid, description, 
    sumin, sumout, doctype
FROM v_ic_list_bfterm
UNION ALL
SELECT docno, docdate, productid, productname, unitid, description, 
    sumin, sumout, doctype
FROM v_ic_list_warehouse
UNION ALL
SELECT docno, docdate, productid, productname, unitid, description, 
    sumin, sumout, doctype
FROM v_ic_list_transdraw
UNION ALL
SELECT docno, docdate, productid, productname, unitid, description, 
    sumin, sumout, doctype
FROM v_ic_list_trans
UNION ALL
SELECT docno, docdate, productid, productname, unitid, description, 
    sumin, sumout, doctype
FROM v_ic_list_adjuststock
GO

CREATE VIEW v_ic_rep_remain
AS
SELECT v_ic_remain.productid, 
    tb_ic_product.ProductName, v_ic_remain.storeid, 
    tb_ic_store.storename, v_ic_remain.unitid, 
    tb_ic_unit.unitname, v_ic_remain.amount
FROM tb_ic_unit RIGHT OUTER JOIN
    v_ic_remain ON 
    tb_ic_unit.unitid = v_ic_remain.unitid LEFT OUTER JOIN
    tb_ic_store ON 
    v_ic_remain.storeid = tb_ic_store.storeid LEFT OUTER JOIN
    tb_ic_product ON 
    v_ic_remain.productid = tb_ic_product.ProductID
GO

CREATE VIEW v_ic_rep_trans
AS
SELECT tb_ic_trans.docno, tb_ic_trans.docdate, 
    tb_ic_trans.empid, tb_sys_employee.EmpName, 
    tb_ic_trans.description, tb_ic_trans.flagdraw, 
    tb_ic_trans_sub.ProductID, 
    tb_ic_trans_sub.ProductName, 
    tb_ic_trans_sub.storeid, tb_ic_store.storename, 
    tb_ic_trans_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_trans_sub.qty, tb_ic_trans_sub.costperunit, 
    tb_ic_trans_sub.total, tb_ic_trans_sub.rowitem
FROM tb_ic_store RIGHT OUTER JOIN
    tb_ic_trans_sub ON 
    tb_ic_store.storeid = tb_ic_trans_sub.storeid RIGHT OUTER
     JOIN
    tb_ic_trans LEFT OUTER JOIN
    tb_sys_employee ON 
    tb_ic_trans.empid = tb_sys_employee.EmpID ON 
    tb_ic_trans_sub.docno = tb_ic_trans.docno LEFT OUTER
     JOIN
    tb_ic_unit ON 
    tb_ic_trans_sub.unitid = tb_ic_unit.unitid
GO

CREATE VIEW v_ic_rep_transdraw
AS
SELECT tb_ic_transdraw.docno, tb_ic_transdraw.docdate, 
    tb_ic_transdraw.empid, tb_sys_employee.EmpName, 
    tb_ic_transdraw.description, tb_ic_transdraw.flagdraw, 
    tb_ic_transdraw_sub.ProductID, 
    tb_ic_transdraw_sub.ProductName, 
    tb_ic_transdraw_sub.storeid, 
    tb_ic_transdraw_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_transdraw_sub.qty, 
    tb_ic_transdraw_sub.costperunit, 
    tb_ic_transdraw_sub.total, 
    tb_ic_transdraw_sub.rowitem
FROM tb_ic_transdraw LEFT OUTER JOIN
    tb_sys_employee ON 
    tb_ic_transdraw.empid = tb_sys_employee.EmpID LEFT
     OUTER JOIN
    tb_ic_transdraw_sub ON 
    tb_ic_transdraw.docno = tb_ic_transdraw_sub.docno LEFT
     OUTER JOIN
    tb_ic_unit ON 
    tb_ic_transdraw_sub.unitid = tb_ic_unit.unitid
GO

CREATE VIEW v_ic_rep_transferstock
AS
SELECT tb_ic_transfer.docno, tb_ic_transfer.docdate, 
    tb_ic_transfer.description, 
    tb_ic_transfer_sub.ProductID, 
    tb_ic_transfer_sub.ProductName, 
    tb_ic_transfer_sub.unitid, tb_ic_unit.unitname, 
    tb_ic_transfer_sub.qty, 
    tb_ic_transfer_sub.stockout AS stockid_out, 
    tb_ic_store.storename AS stockname_out, 
    tb_ic_transfer_sub.stockin AS stockid_in, 
    tb_ic_store1.storename AS stockname_in
FROM tb_ic_transfer LEFT OUTER JOIN
    tb_ic_transfer_sub ON 
    tb_ic_transfer.docno = tb_ic_transfer_sub.docno LEFT OUTER
     JOIN
    tb_ic_unit ON 
    tb_ic_transfer_sub.unitid = tb_ic_unit.unitid LEFT OUTER
     JOIN
    tb_ic_store tb_ic_store1 ON 
    tb_ic_transfer_sub.stockin = tb_ic_store1.storeid LEFT OUTER
     JOIN
    tb_ic_store ON 
    tb_ic_transfer_sub.stockout = tb_ic_store.storeid
GO

CREATE VIEW v_ic_warehouse
AS
SELECT tb_ic_warehouse.docno, tb_ic_warehouse.docdate, 
    tb_ic_warehouse.personid, 
    tb_ic_warehouse.description, 
    tb_ic_warehouse.effactstock, 
    tb_ic_warehouse_sub.ProductID, 
    tb_ic_warehouse_sub.ProductName, 
    tb_ic_warehouse_sub.storeid, 
    tb_ic_warehouse_sub.unitid, 
    tb_ic_warehouse_sub.amount, 
    tb_ic_warehouse_sub.costperunit, 
    tb_ic_warehouse.doctype
FROM tb_ic_warehouse INNER JOIN
    tb_ic_warehouse_sub ON 
    tb_ic_warehouse.docno = tb_ic_warehouse_sub.docno
GO

CREATE VIEW V_IC_CAL AS
SELECT DOCNO,DOCDATE,PERSONID,DESCRIPTION,PRODUCTID,PRODUCTNAME,STOREID,UNITID,AMOUNT,COSTPERUNIT,EFFACTSTOCK,DOCTYPE  FROM V_IC_WAREHOUSE
UNION ALL
SELECT DOCNO,DOCDATE,PERSONID,DESCRIPTION,PRODUCTID,PRODUCTNAME,STOREID,UNITID,AMOUNT,COSTPERUNIT,EFFACTSTOCK, DOCTYPE  FROM v_ic_calbfterm
UNION ALL
SELECT DOCNO,DOCDATE,PERSONID,DESCRIPTION,PRODUCTID,PRODUCTNAME,STOREID,UNITID,AMOUNT,COSTPERUNIT,EFFACTSTOCK, DOCTYPE  FROM v_ic_caltransdraw
UNION ALL
SELECT DOCNO,DOCDATE,PERSONID,DESCRIPTION,PRODUCTID,PRODUCTNAME,STOREID,UNITID,AMOUNT,COSTPERUNIT,EFFACTSTOCK , DOCTYPE FROM v_ic_caltrans
UNION ALL
SELECT DOCNO,DOCDATE,PERSONID,DESCRIPTION,PRODUCTID,PRODUCTNAME,STOREID,UNITID,AMOUNT,COSTPERUNIT,EFFACTSTOCK , DOCTYPE FROM v_ic_caladjuststock
GO

create view v_pur_aunion_purchase
as
SELECT 'PC' AS doctype, docno, docdate, taxno, docno_po, 
    personid, transportid, description, product_type, vattype, 
    vatrate,
        (SELECT SUM(isnull(total, 0))
      FROM tb_pur_purchase_cash_sub
      WHERE tb_pur_purchase_cash.docno = tb_pur_purchase_cash_sub.docno)
     AS grandtotal, discount, discount_percent, afterdiscount, 
    amountvat
FROM tb_pur_purchase_cash
UNION ALL
SELECT 'PD' AS doctype, docno, docdate, taxno, docno_po, 
    personid, transportid, description, product_type, vattype, 
    vatrate,
        (SELECT SUM(isnull(total, 0))
      FROM tb_pur_purchase_debit_sub
      WHERE tb_pur_purchase_debit.docno = tb_pur_purchase_debit_sub.docno)
     AS grandtotal, discount, discount_percent, afterdiscount, 
    amountvat
FROM tb_pur_purchase_debit
GO

CREATE VIEW v_pur_aunion_purchase_sub
AS
SELECT 'PC' AS doctype, docno, productid, productname, vattype, 
    storeid, quantity, priceperunit, discount, total, vat, unit, 
    description, rowitem
FROM tb_pur_purchase_cash_sub
UNION ALL
SELECT 'PD' AS doctype, docno, productid, productname, vattype, 
    storeid, quantity, priceperunit, discount, total, vat, unit, 
    description, rowitem
FROM tb_pur_purchase_debit_sub
GO

CREATE VIEW v_po_lastprice
AS
SELECT v_pur_aunion_purchase.doctype, 
    v_pur_aunion_purchase.docno, 
    v_pur_aunion_purchase.docdate, 
    v_pur_aunion_purchase.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ap_person.Name AS personname, 
    v_pur_aunion_purchase_sub.productid, 
    v_pur_aunion_purchase_sub.productname, 
    v_pur_aunion_purchase_sub.quantity, 
    tb_ic_unit.unitname, 
    v_pur_aunion_purchase_sub.priceperunit, 
    v_pur_aunion_purchase_sub.discount, 
    v_pur_aunion_purchase_sub.total
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    v_pur_aunion_purchase INNER JOIN
    v_pur_aunion_purchase_sub ON 
    v_pur_aunion_purchase.doctype = v_pur_aunion_purchase_sub.doctype
     AND 
    v_pur_aunion_purchase.docno = v_pur_aunion_purchase_sub.docno
     ON 
    tb_ap_person.ID = v_pur_aunion_purchase.personid LEFT
     OUTER JOIN
    tb_ic_unit ON 
    v_pur_aunion_purchase_sub.unit = tb_ic_unit.unitid
GO

CREATE VIEW v_po_rep_purchase_cash
AS
SELECT tb_pur_purchase_cash.docdate, 
    tb_pur_purchase_cash.docno, 
    tb_pur_purchase_cash.taxno, 
    tb_pur_purchase_cash.docno_po, 
    ISNULL(tb_pur_purchase_cash.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-') AS Personname,
        (SELECT SUM(isnull(total, 0))
      FROM tb_pur_purchase_cash_sub
      WHERE tb_pur_purchase_cash.docno = tb_pur_purchase_cash_sub.docno)
     AS grandtotal, tb_pur_purchase_cash.discount, 
    tb_pur_purchase_cash.amountvat, 
    tb_pur_purchase_cash.afterdiscount + tb_pur_purchase_cash.amountvat
     AS amountnet
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_pur_purchase_cash ON 
    tb_ap_person.ID = tb_pur_purchase_cash.personid
GO

CREATE VIEW v_pur_sum_purchasereturn
AS
SELECT taxno_ref, SUM(amount) AS amount, SUM(amountvat) 
    AS amountvat
FROM tb_pur_purchase_return
GROUP BY taxno_ref
GO

CREATE VIEW v_pur_sum_purchaseinc
AS
SELECT taxno_ref, SUM(amount) AS amount, SUM(amountvat) 
    AS amountvat
FROM tb_pur_purchase_inc
GROUP BY taxno_ref
GO

CREATE VIEW v_po_rep_purchase_cash_sum
AS
SELECT v_po_rep_purchase_cash.docdate, 
    v_po_rep_purchase_cash.docno, 
    v_po_rep_purchase_cash.docno_po, 
    v_po_rep_purchase_cash.personid, 
    v_po_rep_purchase_cash.Personname, 
    v_po_rep_purchase_cash.amountnet AS AmountSale, 
    v_po_rep_purchase_cash.amountvat AS VatSale, 
    v_pur_sum_purchaseinc.amount + ISNULL(v_pur_sum_purchaseinc.amountvat,
     0) AS AmountInc, 
    v_pur_sum_purchaseinc.amountvat AS VatInc, 
    v_pur_sum_purchasereturn.amount + ISNULL(v_pur_sum_purchasereturn.amountvat,
     0) AS AmountDec, 
    v_pur_sum_purchasereturn.amountvat AS VatDec, 
    v_po_rep_purchase_cash.amountvat + ISNULL(v_pur_sum_purchaseinc.amountvat,
     0) - ISNULL(v_pur_sum_purchasereturn.amountvat, 0) 
    AS diffVat, 
    v_po_rep_purchase_cash.amountnet + ISNULL(v_pur_sum_purchaseinc.amount,
     0) + ISNULL(v_pur_sum_purchaseinc.amountvat, 0) 
    - ISNULL(v_pur_sum_purchasereturn.amount, 0) 
    + ISNULL(v_pur_sum_purchasereturn.amountvat, 0) 
    AS amountnet
FROM v_po_rep_purchase_cash LEFT OUTER JOIN
    v_pur_sum_purchasereturn ON 
    v_po_rep_purchase_cash.taxno = v_pur_sum_purchasereturn.taxno_ref
     LEFT OUTER JOIN
    v_pur_sum_purchaseinc ON 
    v_po_rep_purchase_cash.taxno = v_pur_sum_purchaseinc.taxno_ref
GO

CREATE VIEW v_po_rep_purchase_debit
AS
SELECT tb_pur_purchase_debit.docdate, 
    tb_pur_purchase_debit.docno, tb_pur_purchase_debit.taxno, 
    tb_pur_purchase_debit.docno_po, 
    ISNULL(tb_pur_purchase_debit.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-') AS Personname,
        (SELECT SUM(isnull(total, 0))
      FROM tb_pur_purchase_debit_sub
      WHERE tb_pur_purchase_debit.docno = tb_pur_purchase_debit_sub.docno)
     AS grandtotal, tb_pur_purchase_debit.discount, 
    tb_pur_purchase_debit.amountvat, 
    tb_pur_purchase_debit.afterdiscount + tb_pur_purchase_debit.amountvat
     AS amountnet, tb_pur_purchase_debit.duedate
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER JOIN
    tb_pur_purchase_debit ON 
    tb_ap_person.ID = tb_pur_purchase_debit.personid
GO

CREATE VIEW v_po_rep_purchase_debit_sum
AS
SELECT v_po_rep_purchase_debit.docdate, 
    v_po_rep_purchase_debit.docno, 
    v_po_rep_purchase_debit.docno_po, 
    v_po_rep_purchase_debit.personid, 
    v_po_rep_purchase_debit.Personname, 
    v_po_rep_purchase_debit.amountnet AS AmountSale, 
    v_po_rep_purchase_debit.amountvat AS VatSale, 
    v_pur_sum_purchaseinc.amount + ISNULL(v_pur_sum_purchaseinc.amountvat,
     0) AS AmountInc, 
    v_pur_sum_purchaseinc.amountvat AS VatInc, 
    v_pur_sum_purchasereturn.amount + ISNULL(v_pur_sum_purchasereturn.amountvat,
     0) AS AmountDec, 
    v_pur_sum_purchasereturn.amountvat AS VatDec, 
    v_po_rep_purchase_debit.amountvat + ISNULL(v_pur_sum_purchaseinc.amountvat,
     0) - ISNULL(v_pur_sum_purchasereturn.amountvat, 0) 
    AS diffVat, 
    v_po_rep_purchase_debit.amountnet + ISNULL(v_pur_sum_purchaseinc.amount,
     0) + ISNULL(v_pur_sum_purchaseinc.amountvat, 0) 
    - ISNULL(v_pur_sum_purchasereturn.amount, 0) 
    + ISNULL(v_pur_sum_purchasereturn.amountvat, 0) 
    AS amountnet, v_po_rep_purchase_debit.duedate
FROM v_po_rep_purchase_debit LEFT OUTER JOIN
    v_pur_sum_purchasereturn ON 
    v_po_rep_purchase_debit.taxno = v_pur_sum_purchasereturn.taxno_ref
     LEFT OUTER JOIN
    v_pur_sum_purchaseinc ON 
    v_po_rep_purchase_debit.taxno = v_pur_sum_purchaseinc.taxno_ref
GO

CREATE VIEW v_po_rep_purchase_inc
AS
SELECT tb_pur_purchase_inc.docdate, 
    tb_pur_purchase_inc.docno, 
    tb_pur_purchase_inc.taxno, 
    tb_pur_purchase_inc.taxno_ref, 
    ISNULL(tb_pur_purchase_inc.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-') AS personname, 
    tb_pur_purchase_inc.taxvalue_ref, 
    tb_pur_purchase_inc.taxvalue_ref + tb_pur_purchase_inc.amount
     AS amount_correct, 
    tb_pur_purchase_inc.amount AS amountdiff, 
    tb_pur_purchase_inc.amountvat, 
    tb_pur_purchase_inc.amount + tb_pur_purchase_inc.amountvat
     AS amountnet
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_pur_purchase_inc ON 
    tb_ap_person.ID = tb_pur_purchase_inc.personid
GO

CREATE VIEW v_po_rep_purchase_return
AS
SELECT tb_pur_purchase_return.docdate, 
    tb_pur_purchase_return.docno, 
    tb_pur_purchase_return.taxno, 
    tb_pur_purchase_return.taxno_ref, 
    ISNULL(tb_pur_purchase_return.personid, '-') 
    AS personid, ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-') AS personname, 
    tb_pur_purchase_return.taxvalue_ref, 
    tb_pur_purchase_return.taxvalue_ref - tb_pur_purchase_return.amount
     AS amount_correct, 
    tb_pur_purchase_return.amount AS amountdiff, 
    tb_pur_purchase_return.amountvat, 
    tb_pur_purchase_return.amount + tb_pur_purchase_return.amountvat
     AS amountnet
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_pur_purchase_return ON 
    tb_ap_person.ID = tb_pur_purchase_return.personid
GO

CREATE VIEW v_po_rep_purchase_sheet
AS
SELECT tb_pur_purchase_sheet.docdate, 
    tb_pur_purchase_sheet.docno, 
    ISNULL(tb_pur_purchase_sheet.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ap_person.Name, '-') AS personname,
        (SELECT SUM(isnull(total, 0))
      FROM tb_pur_purchase_sheet_sub
      WHERE tb_pur_purchase_sheet.docno = tb_pur_purchase_sheet_sub.docno)
     AS grandtotal, tb_pur_purchase_sheet.discount, 
    tb_pur_purchase_sheet.amountvat, 
    tb_pur_purchase_sheet.afterdiscount + tb_pur_purchase_sheet.amountvat
     AS amountnet
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_pur_purchase_sheet ON 
    tb_ap_person.ID = tb_pur_purchase_sheet.personid
GO

CREATE VIEW v_pur_sumlistpur
AS
SELECT v_pur_aunion_purchase.docno_po, 
    v_pur_aunion_purchase_sub.productid, 
    SUM(v_pur_aunion_purchase_sub.quantity) AS quantity
FROM v_pur_aunion_purchase_sub RIGHT OUTER JOIN
    v_pur_aunion_purchase ON 
    v_pur_aunion_purchase_sub.doctype = v_pur_aunion_purchase.doctype
     AND 
    v_pur_aunion_purchase_sub.docno = v_pur_aunion_purchase.docno
GROUP BY v_pur_aunion_purchase.docno_po, 
    v_pur_aunion_purchase_sub.productid
GO

CREATE VIEW v_pur_remainpo
AS
SELECT tb_pur_purchase_sheet_sub.docno, 
    tb_pur_purchase_sheet_sub.productid, 
    MAX(tb_pur_purchase_sheet_sub.quantity) AS QtyOrder, 
    MAX(v_pur_sumlistpur.quantity) AS QtyIn, 
    SUM(tb_pur_purchase_sheet_sub.quantity - ISNULL(v_pur_sumlistpur.quantity,
     0)) AS RemainPo
FROM v_pur_sumlistpur RIGHT OUTER JOIN
    tb_pur_purchase_sheet_sub ON 
    v_pur_sumlistpur.productid = tb_pur_purchase_sheet_sub.productid
     AND 
    v_pur_sumlistpur.docno_po = tb_pur_purchase_sheet_sub.docno
GROUP BY tb_pur_purchase_sheet_sub.docno, 
    tb_pur_purchase_sheet_sub.productid
HAVING (SUM(tb_pur_purchase_sheet_sub.quantity - ISNULL(v_pur_sumlistpur.quantity,
     0)) > 0)
GO

CREATE VIEW v_po_rep_remainpo
AS
SELECT tb_pur_purchase_sheet.docno, 
    tb_pur_purchase_sheet.docdate, 
    tb_pur_purchase_sheet.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ap_person.Name AS personname, 
    tb_pur_purchase_sheet_sub.productid, 
    tb_pur_purchase_sheet_sub.productname, 
    tb_ic_unit.unitname, tb_pur_purchase_sheet_sub.priceperunit, 
    tb_pur_purchase_sheet_sub.discount, 
    tb_pur_purchase_sheet_sub.vat, 
    tb_pur_purchase_sheet_sub.total, v_pur_remainpo.QtyOrder, 
    v_pur_remainpo.QtyIn, v_pur_remainpo.RemainPo, 
    tb_pur_purchase_sheet_sub.rowitem
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ap_person ON 
    tb_sys_prefix.prefixid = tb_ap_person.PrefixID RIGHT OUTER
     JOIN
    tb_pur_purchase_sheet_sub INNER JOIN
    tb_pur_purchase_sheet ON 
    tb_pur_purchase_sheet_sub.docno = tb_pur_purchase_sheet.docno
     ON 
    tb_ap_person.ID = tb_pur_purchase_sheet.personid LEFT
     OUTER JOIN
    tb_ic_unit ON 
    tb_pur_purchase_sheet_sub.unit = tb_ic_unit.unitid LEFT
     OUTER JOIN
    v_pur_remainpo ON 
    tb_pur_purchase_sheet_sub.docno = v_pur_remainpo.docno
     AND 
    tb_pur_purchase_sheet_sub.productid = v_pur_remainpo.productid
GO

create view v_po_rep_Summary_CompareAllYear_Qty
as
SELECT year(v_pur_aunion_purchase.docdate) as puryear,
	v_pur_aunion_purchase_sub.productid, 
    MAX(tb_ic_product.ProductName) AS ProductName, 
    SUM(CASE WHEN month(docdate)= 1 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_1,
    SUM(CASE WHEN month(docdate) = 2 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_2,
    SUM(CASE WHEN month(docdate) = 3 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END)  AS Month_3,
    SUM(CASE WHEN month(docdate) = 4 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END)   AS Month_4,
    SUM(CASE WHEN month(docdate) = 5 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END)   AS Month_5,
    SUM(CASE WHEN month(docdate)= 6 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_6 ,
   SUM(CASE WHEN month(docdate) = 7 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_7 ,
  SUM(CASE WHEN month(docdate) = 8 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END)  AS Month_8,
  SUM(CASE WHEN month(docdate)= 9 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_9,
  SUM(CASE WHEN month(docdate)= 10 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END)AS Month_10,
 SUM(CASE WHEN month(docdate) = 11 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_11,
 SUM(CASE WHEN month(docdate) = 12 THEN v_pur_aunion_purchase_sub.quantity ELSE 0 END) AS Month_12,
 SUM(v_pur_aunion_purchase_sub.quantity) AS Month_Total
FROM v_pur_aunion_purchase INNER JOIN
    v_pur_aunion_purchase_sub ON 
    v_pur_aunion_purchase.doctype = v_pur_aunion_purchase_sub.doctype
     AND 
    v_pur_aunion_purchase.docno = v_pur_aunion_purchase_sub.docno
     LEFT OUTER JOIN
    tb_ic_product ON 
    v_pur_aunion_purchase_sub.productid = tb_ic_product.ProductID
GROUP BY year(v_pur_aunion_purchase.docdate),v_pur_aunion_purchase_sub.productid
GO

create view v_po_rep_Summary_CompareAllYear_total
as
SELECT year(v_pur_aunion_purchase.docdate) AS puryear, 
    v_pur_aunion_purchase_sub.productid, 
    MAX(tb_ic_product.ProductName) AS ProductName, 
SUM(CASE WHEN month(docdate)= 1 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0)  ELSE 0 END) AS Month_1, 
SUM(CASE WHEN month(docdate)= 2 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0)  ELSE 0 END) AS Month_2, 
SUM(CASE WHEN month(docdate)= 3 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END) AS Month_3, 
SUM(CASE WHEN month(docdate)= 4 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0)  ELSE 0 END) AS Month_4, 
SUM(CASE WHEN month(docdate)= 5 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0)  ELSE 0 END) AS Month_5, 
SUM(CASE WHEN month(docdate)= 6 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END) AS Month_6, 
SUM(CASE WHEN month(docdate)= 7 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0)ELSE 0 END)  AS Month_7, 
SUM(CASE WHEN month(docdate)= 8 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END)  AS Month_8, 
SUM(CASE WHEN month(docdate)= 9 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END)  AS Month_9,
SUM(CASE WHEN month(docdate)= 10 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END)  AS Month_10, 
SUM(CASE WHEN month(docdate)= 11 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END) AS Month_11, 
SUM(CASE WHEN month(docdate)= 12 THEN v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0) ELSE 0 END)  AS Month_12, 
SUM(v_pur_aunion_purchase_sub.total + isnull(v_pur_aunion_purchase_sub.vat,0))  AS Month_Total
FROM v_pur_aunion_purchase INNER JOIN
    v_pur_aunion_purchase_sub ON 
    v_pur_aunion_purchase.doctype = v_pur_aunion_purchase_sub.doctype
     AND 
    v_pur_aunion_purchase.docno = v_pur_aunion_purchase_sub.docno
     LEFT OUTER JOIN
    tb_ic_product ON 
    v_pur_aunion_purchase_sub.productid = tb_ic_product.ProductID
GROUP BY year(v_pur_aunion_purchase.docdate), 
    v_pur_aunion_purchase_sub.productid
GO

CREATE VIEW v_pur_aaddress_ap
AS
SELECT tb_ap_person.id, tb_ap_person.name, 
    tb_ap_person.Telephone, tb_ap_person.Fax, 
    (tb_ap_person.Address + CASE WHEN tb_ap_person.Moo IS NULL
     OR
    tb_ap_person.Moo = '' THEN '' ELSE '  หมู่ที่ ' + tb_ap_person.Moo
     END) + (CASE WHEN tb_ap_person.Soi IS NULL OR
    tb_ap_person.Soi = '' THEN '' ELSE '  ซ.' + tb_ap_person.Soi END
     + CASE WHEN tb_ap_person.Road IS NULL OR
    tb_ap_person.Road = '' THEN '' ELSE '  ถ.' + tb_ap_person.Road
     END) 
    + (CASE WHEN NOT (tb_ap_person.District IS NULL OR
    tb_ap_person.District = '') 
    THEN (CASE WHEN tb_ap_person.TumbonType = 0 THEN '  ต.' ELSE
     '  แขวง ' END) + tb_ap_person.District ELSE '' END) 
    AS Row_1, 
    (CASE WHEN NOT (tb_ap_person.Zone IS NULL OR
    tb_ap_person.Zone = '') 
    THEN (CASE WHEN tb_ap_person.AumperType = 0 THEN '  อ.' ELSE
     '  เขต ' END) + tb_ap_person.Zone ELSE '' END) 
    + (CASE WHEN tb_ap_person.Province IS NULL OR
    tb_ap_person.Province = '' THEN '' ELSE '  จ.' + tb_ap_person.Province
     END + '    ' + tb_ap_person.Postcode) AS Row_2
FROM tb_ap_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ap_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_pur_aheadpo
AS
SELECT docno, product_type, vattype, vatrate, discount_percent, 
    discount, afterdiscount, amountvat
FROM tb_pur_purchase_sheet
GO

CREATE VIEW v_pur_listpur
AS
SELECT v_pur_aunion_purchase.doctype, 
    v_pur_aunion_purchase.docno, 
    v_pur_aunion_purchase.docdate, 
    v_pur_aunion_purchase.taxno, 
    v_pur_aunion_purchase.docno_po, 
    v_pur_aunion_purchase.personid, 
    v_pur_aunion_purchase_sub.productid, 
    v_pur_aunion_purchase_sub.quantity, 
    v_pur_aunion_purchase_sub.priceperunit, 
    v_pur_aunion_purchase_sub.discount, 
    v_pur_aunion_purchase_sub.total, 
    v_pur_aunion_purchase_sub.unit, 
    v_pur_aunion_purchase_sub.rowitem
FROM v_pur_aunion_purchase_sub RIGHT OUTER JOIN
    v_pur_aunion_purchase ON 
    v_pur_aunion_purchase_sub.doctype = v_pur_aunion_purchase.doctype
     AND 
    v_pur_aunion_purchase_sub.docno = v_pur_aunion_purchase.docno
GO

CREATE VIEW v_pur_spurchase_sheet
AS
SELECT tb_pur_purchase_sheet.docno, 
    tb_pur_purchase_sheet.docdate, tb_ap_person.Name, 
    tb_pur_purchase_sheet.use_yn
FROM tb_pur_purchase_sheet LEFT OUTER JOIN
    tb_ap_person ON 
    tb_pur_purchase_sheet.personid = tb_ap_person.ID
GO

CREATE VIEW v_pur_slist_po
AS
SELECT v_pur_spurchase_sheet.docno, 
    v_pur_spurchase_sheet.docdate, 
    v_pur_spurchase_sheet.Name
FROM v_pur_spurchase_sheet LEFT OUTER JOIN
    v_pur_remainpo ON 
    v_pur_spurchase_sheet.docno = v_pur_remainpo.docno
WHERE (v_pur_remainpo.RemainPo > 0)
GO

CREATE VIEW v_pur_spurchase_cash
AS
SELECT docno, docdate, taxno, docno_po, description
FROM tb_pur_purchase_cash
GO

CREATE VIEW v_pur_spurchase_debit
AS
SELECT docno, docdate, taxno, docno_po, description
FROM tb_pur_purchase_debit
GO

CREATE VIEW v_pur_spurchase_inc
AS
SELECT docno, docdate, taxno, taxno_ref, description
FROM tb_pur_purchase_inc
GO

CREATE VIEW v_pur_spurchase_return
AS
SELECT docno, docdate, taxno, taxno_ref, description
FROM tb_pur_purchase_return
GO

CREATE VIEW v_pur_staxpurchase_cash
AS
SELECT tb_pur_purchase_cash.taxno, 
    tb_pur_purchase_cash.docdate, 
    tb_pur_purchase_cash.personid, tb_ap_person.Name, 
    'C' AS Status
FROM tb_pur_purchase_cash LEFT OUTER JOIN
    tb_ap_person ON 
    tb_pur_purchase_cash.personid = tb_ap_person.ID
GO

CREATE VIEW v_pur_staxpurchase_debit
AS
SELECT tb_pur_purchase_debit.taxno, 
    tb_pur_purchase_debit.docdate, 
    tb_pur_purchase_debit.personid, tb_ap_person.Name, 
    'D' AS Status
FROM tb_ap_person RIGHT OUTER JOIN
    tb_pur_purchase_debit ON 
    tb_ap_person.ID = tb_pur_purchase_debit.personid
GO

create  view v_pur_staxno_ref
as
SELECT v_pur_staxpurchase_cash.taxno, 
    v_pur_staxpurchase_cash.docdate, 
    v_pur_staxpurchase_cash.personid, 
    v_pur_staxpurchase_cash.Name, 
    v_pur_staxpurchase_cash.Status
FROM v_pur_staxpurchase_cash
UNION ALL
SELECT v_pur_staxpurchase_debit.taxno, 
    v_pur_staxpurchase_debit.docdate, 
    v_pur_staxpurchase_debit.personid, 
    v_pur_staxpurchase_debit.Name, 
    v_pur_staxpurchase_debit.Status
FROM v_pur_staxpurchase_debit
GO

CREATE VIEW v_s_ar_beginterm
AS
SELECT tb_ar_beginterm.docno, tb_ar_beginterm.docdate, 
    tb_ar_beginterm.taxno, tb_ar_person.Name
FROM tb_ar_beginterm LEFT OUTER JOIN
    tb_ar_person ON tb_ar_beginterm.personid = tb_ar_person.ID
GO

create view v_sal_aaddress_ar
as
SELECT tb_ar_person.id, tb_ar_person.name, 
    tb_ar_person.Telephone, tb_ar_person.Fax, 
    (tb_ar_person.Address + CASE WHEN tb_ar_person.Moo IS NULL
     OR
    tb_ar_person.Moo = '' THEN '' ELSE '  หมู่ที่ ' + tb_ar_person.Moo
     END) + (CASE WHEN tb_ar_person.Soi IS NULL OR
    tb_ar_person.Soi = '' THEN '' ELSE '  ซ.' + tb_ar_person.Soi END
     + CASE WHEN tb_ar_person.Road IS NULL OR
    tb_ar_person.Road = '' THEN '' ELSE '  ถ.' + tb_ar_person.Road
     END) 
    + (CASE WHEN NOT (tb_ar_person.District IS NULL OR
    tb_ar_person.District = '') 
    THEN (CASE WHEN tb_ar_person.TumbonType = 0 THEN '  ต.' ELSE
     '  แขวง ' END) + tb_ar_person.District ELSE '' END) 
    AS Row_1, 
    (CASE WHEN NOT (tb_ar_person.Zone IS NULL OR
    tb_ar_person.Zone = '') 
    THEN (CASE WHEN tb_ar_person.AumperType = 0 THEN '  อ.' ELSE
     '  เขต ' END) + tb_ar_person.Zone ELSE '' END) 
    + (CASE WHEN tb_ar_person.Province IS NULL OR
    tb_ar_person.Province = '' THEN '' ELSE '  จ.' + tb_ar_person.Province
     END + '    ' + tb_ar_person.Postcode) AS Row_2
FROM tb_ar_person LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid
GO

CREATE VIEW v_sal_aunion_sale
AS
SELECT 'SC' AS doctype, docno, docdate, taxno, docno_po, 
    '' AS duedate, 0 AS credit_term, personid, transportid, 
    transport_unitid, transport_rate, transport_qty, transport_value, 
    description, discount_FFA, discount_FFD, discount_NSD, 
    discount_REDATE, product_type, vattype, vatrate,
        (SELECT SUM(isnull(total, 0))
      FROM tb_sal_salecash_sub
      WHERE tb_sal_salecash.docno = tb_sal_salecash_sub.docno)
     AS grandtotal, discount, discount_percent, afterdiscount, 
    amountvat
FROM tb_sal_salecash
UNION ALL
SELECT 'SD' AS doctype, docno, docdate, taxno, docno_po, 
    duedate, credit_term, personid, transportid, transport_unitid, 
    transport_rate, transport_qty, transport_value, description, 
    discount_FFA, discount_FFD, discount_NSD, 
    discount_REDATE, product_type, vattype, vatrate,
        (SELECT SUM(isnull(total, 0))
      FROM tb_sal_saledebit_sub
      WHERE tb_sal_saledebit.docno = tb_sal_saledebit_sub.docno)
     AS grandtotal, discount, discount_percent, afterdiscount, 
    amountvat
FROM tb_sal_saledebit
GO

CREATE VIEW v_sal_aunion_sale_sub
AS
SELECT 'SC' AS doctype, docno, productid, productname, vattype, 
    storeid, quantity, priceperunit, discount, total, vat, unit, 
    description, rowitem
FROM tb_sal_salecash_sub
UNION ALL
SELECT 'SD' AS doctype, docno, productid, productname, vattype, 
    storeid, quantity, priceperunit, discount, total, vat, unit, 
    description, rowitem
FROM tb_sal_saledebit_sub
GO

CREATE VIEW v_sal_rep_cancel
AS
SELECT tb_sal_cancel.docdate, tb_sal_cancel.docno, 
    tb_sal_cancel.taxno, ISNULL(tb_sal_cancel.personid, '-') 
    AS personid, ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-') AS Personname,
        (SELECT SUM(isnull(total, 0))
      FROM tb_sal_cancel_sub
      WHERE tb_sal_cancel.docno = tb_sal_cancel_sub.docno) 
    AS grandtotal, tb_sal_cancel.discount, 
    tb_sal_cancel.amountvat, 
    tb_sal_cancel.afterdiscount + tb_sal_cancel.amountvat AS amountnet,
     tb_sal_cancel.docno_po
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER JOIN
    tb_sal_cancel ON tb_ar_person.ID = tb_sal_cancel.personid
GO

CREATE VIEW v_sal_rep_salecash
AS
SELECT tb_sal_salecash.docdate, tb_sal_salecash.docno, 
    tb_sal_salecash.taxno, 
    ISNULL(tb_sal_salecash.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-') AS Personname,
        (SELECT SUM(isnull(total, 0))
      FROM tb_sal_salecash_sub
      WHERE tb_sal_salecash.docno = tb_sal_salecash_sub.docno)
     AS grandtotal, tb_sal_salecash.discount, 
    tb_sal_salecash.amountvat, 
    tb_sal_salecash.afterdiscount + tb_sal_salecash.amountvat
     AS amountnet, tb_sal_salecash.docno_po
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_sal_salecash ON 
    tb_ar_person.ID = tb_sal_salecash.personid
GO

CREATE VIEW v_sal_sum_salereturn
AS
SELECT taxno_ref, SUM(amount) AS amount, SUM(amountvat) 
    AS amountvat
FROM tb_sal_salereturn
GROUP BY taxno_ref
GO

CREATE VIEW v_sal_sum_saleinc
AS
SELECT taxno_ref, SUM(amount) AS amount, SUM(amountvat) 
    AS amountvat
FROM tb_sal_saleinc
GROUP BY taxno_ref
GO

CREATE VIEW v_sal_rep_salecash_sum
AS
SELECT v_sal_rep_salecash.docdate, v_sal_rep_salecash.docno, 
    v_sal_rep_salecash.docno_po, v_sal_rep_salecash.personid, 
    v_sal_rep_salecash.Personname, 
    v_sal_rep_salecash.amountnet AS AmountSale, 
    v_sal_rep_salecash.amountvat AS VatSale, 
    v_sal_sum_saleinc.amount + ISNULL(v_sal_sum_saleinc.amountvat,
     0) AS AmountInc, v_sal_sum_saleinc.amountvat AS VatInc, 
    v_sal_sum_salereturn.amount + ISNULL(v_sal_sum_salereturn.amountvat,
     0) AS AmountDec, 
    v_sal_sum_salereturn.amountvat AS VatDec, 
    v_sal_rep_salecash.amountvat + ISNULL(v_sal_sum_saleinc.amountvat,
     0) - ISNULL(v_sal_sum_salereturn.amountvat, 0) AS diffVat, 
    v_sal_rep_salecash.amountnet + ISNULL(v_sal_sum_saleinc.amount,
     0) + ISNULL(v_sal_sum_saleinc.amountvat, 0) 
    - ISNULL(v_sal_sum_salereturn.amount, 0) 
    + ISNULL(v_sal_sum_salereturn.amountvat, 0) 
    AS amountnet
FROM v_sal_rep_salecash LEFT OUTER JOIN
    v_sal_sum_salereturn ON 
    v_sal_rep_salecash.taxno = v_sal_sum_salereturn.taxno_ref LEFT
     OUTER JOIN
    v_sal_sum_saleinc ON 
    v_sal_rep_salecash.taxno = v_sal_sum_saleinc.taxno_ref
GO

CREATE VIEW v_sal_rep_saledebit
AS
SELECT tb_sal_saledebit.docdate, tb_sal_saledebit.docno, 
    tb_sal_saledebit.taxno, 
    ISNULL(tb_sal_saledebit.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-') AS Personname,
        (SELECT SUM(isnull(total, 0))
      FROM tb_sal_saledebit_sub
      WHERE tb_sal_saledebit.docno = tb_sal_saledebit_sub.docno)
     AS grandtotal, tb_sal_saledebit.discount, 
    tb_sal_saledebit.amountvat, 
    tb_sal_saledebit.afterdiscount + tb_sal_saledebit.amountvat
     AS amountnet, tb_sal_saledebit.duedate, 
    tb_sal_saledebit.credit_term, 
    tb_sal_saledebit.docno_po
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_sal_saledebit ON 
    tb_ar_person.ID = tb_sal_saledebit.personid
GO

CREATE VIEW v_sal_rep_saledebit_sum
AS
SELECT v_sal_rep_saledebit.docdate, v_sal_rep_saledebit.docno, 
    v_sal_rep_saledebit.docno_po, v_sal_rep_saledebit.personid, 
    v_sal_rep_saledebit.Personname, 
    v_sal_rep_saledebit.amountnet AS AmountSale, 
    v_sal_rep_saledebit.amountvat AS VatSale, 
    v_sal_sum_saleinc.amount + ISNULL(v_sal_sum_saleinc.amountvat,
     0) AS AmountInc, v_sal_sum_saleinc.amountvat AS VatInc, 
    v_sal_sum_salereturn.amount + ISNULL(v_sal_sum_salereturn.amountvat,
     0) AS AmountDec, 
    v_sal_sum_salereturn.amountvat AS VatDec, 
    v_sal_rep_saledebit.amountvat + ISNULL(v_sal_sum_saleinc.amountvat,
     0) - ISNULL(v_sal_sum_salereturn.amountvat, 0) AS diffVat, 
    v_sal_rep_saledebit.amountnet + ISNULL(v_sal_sum_saleinc.amount,
     0) + ISNULL(v_sal_sum_saleinc.amountvat, 0) 
    - ISNULL(v_sal_sum_salereturn.amount, 0) 
    + ISNULL(v_sal_sum_salereturn.amountvat, 0) AS amountnet, 
    v_sal_rep_saledebit.duedate
FROM v_sal_rep_saledebit LEFT OUTER JOIN
    v_sal_sum_salereturn ON 
    v_sal_rep_saledebit.taxno = v_sal_sum_salereturn.taxno_ref LEFT
     OUTER JOIN
    v_sal_sum_saleinc ON 
    v_sal_rep_saledebit.taxno = v_sal_sum_saleinc.taxno_ref
GO

CREATE VIEW v_sal_rep_saleinc
AS
SELECT tb_sal_saleinc.docdate, tb_sal_saleinc.docno, 
    tb_sal_saleinc.taxno, tb_sal_saleinc.taxno_ref, 
    ISNULL(tb_sal_saleinc.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-') AS personname, 
    tb_sal_saleinc.taxvalue_ref, 
    tb_sal_saleinc.taxvalue_ref + tb_sal_saleinc.amount AS
     amount_correct, tb_sal_saleinc.amount AS amountdiff, 
    tb_sal_saleinc.amountvat, 
    tb_sal_saleinc.amount + tb_sal_saleinc.amountvat AS amountnet
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_sal_saleinc ON 
    tb_ar_person.ID = tb_sal_saleinc.personid
GO

CREATE VIEW v_sal_rep_salereturn
AS
SELECT tb_sal_salereturn.docdate, 
    tb_sal_salereturn.docno, tb_sal_salereturn.taxno, 
    tb_sal_salereturn.taxno_ref, 
    ISNULL(tb_sal_salereturn.personid, '-') AS personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + ISNULL(tb_ar_person.Name, '-') AS personname, 
    tb_sal_salereturn.taxvalue_ref, 
    tb_sal_salereturn.taxvalue_ref - tb_sal_salereturn.amount
     AS amount_correct, 
    tb_sal_salereturn.amount AS amountdiff, 
    tb_sal_salereturn.amountvat, 
    tb_sal_salereturn.amount + tb_sal_salereturn.amountvat
     AS amountnet
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER
     JOIN
    tb_sal_salereturn ON 
    tb_ar_person.ID = tb_sal_salereturn.personid
GO

create view  v_sal_rep_Summary_CompareAllYear_Qty
as
SELECT year(v_sal_aunion_sale.docdate) AS puryear, 
    v_sal_aunion_sale_sub.productid, 
    MAX(tb_ic_product.ProductName) AS ProductName, 
    SUM(CASE WHEN month(docdate) 
    = 1 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_1, SUM(CASE WHEN month(docdate) 
    = 2 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_2, SUM(CASE WHEN month(docdate) 
    = 3 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_3, SUM(CASE WHEN month(docdate) 
    = 4 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_4, SUM(CASE WHEN month(docdate) 
    = 5 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_5, SUM(CASE WHEN month(docdate) 
    = 6 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_6, SUM(CASE WHEN month(docdate) 
    = 7 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_7, SUM(CASE WHEN month(docdate) 
    = 8 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_8, SUM(CASE WHEN month(docdate) 
    = 9 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_9, SUM(CASE WHEN month(docdate) 
    = 10 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_10, SUM(CASE WHEN month(docdate) 
    = 11 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_11, SUM(CASE WHEN month(docdate) 
    = 12 THEN v_sal_aunion_sale_sub.quantity ELSE 0 END) 
    AS Month_12, SUM(v_sal_aunion_sale_sub.quantity) 
    AS Month_Total
FROM v_sal_aunion_sale INNER JOIN
    v_sal_aunion_sale_sub ON 
    v_sal_aunion_sale.doctype = v_sal_aunion_sale_sub.doctype
     AND 
    v_sal_aunion_sale.docno = v_sal_aunion_sale_sub.docno
     LEFT OUTER JOIN
    tb_ic_product ON 
    v_sal_aunion_sale_sub.productid = tb_ic_product.ProductID
GROUP BY year(v_sal_aunion_sale.docdate), 
    v_sal_aunion_sale_sub.productid
GO

create view v_sal_rep_Summary_CompareAllYear_total
as
SELECT year(v_sal_aunion_sale.docdate) AS puryear, 
    v_sal_aunion_sale_sub.productid, 
    MAX(tb_ic_product.ProductName) AS ProductName, 
SUM(CASE WHEN month(docdate)= 1 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END) AS Month_1, 
SUM(CASE WHEN month(docdate)= 2 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END)  AS Month_2,
SUM(CASE WHEN month(docdate)= 3 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END)  AS Month_3, 
SUM(CASE WHEN month(docdate)= 4 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0) ELSE 0 END)  AS Month_4, 
SUM(CASE WHEN month(docdate)= 5 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0) ELSE 0 END) AS Month_5, 
SUM(CASE WHEN month(docdate)= 6 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0) ELSE 0 END) AS Month_6, 
SUM(CASE WHEN month(docdate)= 7 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END) AS Month_7, 
SUM(CASE WHEN month(docdate)= 8 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END) AS Month_8, 
SUM(CASE WHEN month(docdate)= 9 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END)  AS Month_9, 
SUM(CASE WHEN month(docdate)= 10 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0)  ELSE 0 END) AS Month_10, 
SUM(CASE WHEN month(docdate)= 11 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0) ELSE 0 END) AS Month_11, 
SUM(CASE WHEN month(docdate)= 12 THEN v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0) ELSE 0 END)  AS Month_12, 
SUM(v_sal_aunion_sale_sub.total + isnull(v_sal_aunion_sale_sub.vat,0) ) AS Month_Total 
FROM v_sal_aunion_sale INNER JOIN
    v_sal_aunion_sale_sub ON 
    v_sal_aunion_sale.doctype = v_sal_aunion_sale_sub.doctype AND
     v_sal_aunion_sale.docno = v_sal_aunion_sale_sub.docno LEFT
     OUTER JOIN
    tb_ic_product ON 
    v_sal_aunion_sale_sub.productid = tb_ic_product.ProductID
GROUP BY year(v_sal_aunion_sale.docdate), 
    v_sal_aunion_sale_sub.productid
GO

CREATE VIEW v_sal_rep_Summary_Discount
AS
SELECT v_sal_aunion_sale.docno, v_sal_aunion_sale.docdate, 
    v_sal_aunion_sale.taxno, v_sal_aunion_sale.docno_po, 
    v_sal_aunion_sale.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    v_sal_aunion_sale.discount_FFA + ISNULL(tb_sal_saleinc.discount_FFA,
     0) - ISNULL(tb_sal_salereturn.discount_FFA, 0) AS SumFFA, 
    v_sal_aunion_sale.discount_FFD + ISNULL(tb_sal_saleinc.discount_FFD,
     0) - ISNULL(tb_sal_salereturn.discount_FFD, 0) AS SumFFD, 
    v_sal_aunion_sale.discount_NSD + ISNULL(tb_sal_saleinc.discount_NSD,
     0) - ISNULL(tb_sal_salereturn.discount_NSD, 0) AS SumNSD, 
    v_sal_aunion_sale.discount_REDATE + ISNULL(tb_sal_saleinc.discount_REDATE,
     0) - ISNULL(tb_sal_salereturn.discount_REDATE, 0) 
    AS SumREDATE, 
    v_sal_aunion_sale.discount_FFA + ISNULL(tb_sal_saleinc.discount_FFA,
     0) - ISNULL(tb_sal_salereturn.discount_FFA, 0) 
    + v_sal_aunion_sale.discount_FFD + ISNULL(tb_sal_saleinc.discount_FFD,
     0) - ISNULL(tb_sal_salereturn.discount_FFD, 0) 
    + v_sal_aunion_sale.discount_NSD + ISNULL(tb_sal_saleinc.discount_NSD,
     0) - ISNULL(tb_sal_salereturn.discount_NSD, 0) 
    + v_sal_aunion_sale.discount_REDATE + ISNULL(tb_sal_saleinc.discount_REDATE,
     0) - ISNULL(tb_sal_salereturn.discount_REDATE, 0) 
    AS totalamount, (ISNULL(v_sal_aunion_sale.afterdiscount, 0) 
    + ISNULL(tb_sal_saleinc.amount + tb_sal_saleinc.amountvat, 
    0) 
    - ISNULL(tb_sal_salereturn.amount + tb_sal_salereturn.amountvat,
     0)) AS amount
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER JOIN
    v_sal_aunion_sale LEFT OUTER JOIN
    tb_sal_saleinc ON 
    v_sal_aunion_sale.taxno = tb_sal_saleinc.taxno_ref LEFT OUTER
     JOIN
    tb_sal_salereturn ON 
    v_sal_aunion_sale.taxno = tb_sal_salereturn.taxno_ref ON 
    tb_ar_person.ID = v_sal_aunion_sale.personid
GO

CREATE VIEW v_sal_rep_sumtransport
AS
SELECT v_sal_aunion_sale.docdate, 
    v_sal_aunion_sale.docno, v_sal_aunion_sale.taxno, 
    v_sal_aunion_sale.docno_po, 
    v_sal_aunion_sale.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    v_sal_aunion_sale.transportid, 
    tb_sys_transport.transportname, 
    v_sal_aunion_sale.transport_unitid, 
    tb_ic_unit.unitname AS transport_unitname, 
    v_sal_aunion_sale.transport_rate, 
    v_sal_aunion_sale.transport_qty + ISNULL(tb_sal_saleinc.transport_qty,
     0) - ISNULL(tb_sal_salereturn.transport_qty, 0) 
    AS transport_qty, 
    v_sal_aunion_sale.transport_value + ISNULL(tb_sal_saleinc.transport_value,
     0) - ISNULL(tb_sal_salereturn.transport_value, 0) 
    transport_value
FROM tb_ic_unit RIGHT OUTER JOIN
    tb_sal_salereturn RIGHT OUTER JOIN
    v_sal_aunion_sale ON 
    tb_sal_salereturn.taxno_ref = v_sal_aunion_sale.taxno
     LEFT OUTER JOIN
    tb_sal_saleinc ON 
    v_sal_aunion_sale.taxno = tb_sal_saleinc.taxno_ref ON
     tb_ic_unit.unitid = v_sal_aunion_sale.transport_unitid LEFT
     OUTER JOIN
    tb_sys_transport ON 
    v_sal_aunion_sale.transportid = tb_sys_transport.transportid
     LEFT OUTER JOIN
    tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID ON 
    v_sal_aunion_sale.personid = tb_ar_person.ID
GO

CREATE VIEW v_sal_ssalecash
AS
SELECT docno, docdate, taxno, description
FROM tb_sal_salecash
GO

CREATE VIEW v_sal_ssaledebit
AS
SELECT docno, docdate, taxno, description
FROM tb_sal_saledebit
GO

CREATE VIEW v_sal_ssaleinc
AS
SELECT docno, docdate, taxno, taxno_ref, description
FROM tb_sal_saleinc
GO

CREATE VIEW v_sal_ssalereturn
AS
SELECT docno, docdate, taxno, taxno_ref, description
FROM tb_sal_salereturn
GO

CREATE VIEW v_sal_stax_salecash
AS
SELECT tb_sal_salecash.taxno, tb_sal_salecash.docdate, 
    tb_sal_salecash.personid, tb_ar_person.Name, 
    'C' AS Status
FROM tb_sal_salecash LEFT OUTER JOIN
    tb_ar_person ON tb_sal_salecash.personid = tb_ar_person.ID
GO

CREATE VIEW v_sal_stax_saledebit
AS
SELECT tb_sal_saledebit.taxno, tb_sal_saledebit.docdate, 
    tb_sal_saledebit.personid, tb_ar_person.Name, 
    'D' AS Status
FROM tb_sal_saledebit LEFT OUTER JOIN
    tb_ar_person ON tb_sal_saledebit.personid = tb_ar_person.ID
GO

create view v_sal_staxno_ref
as
SELECT v_sal_stax_salecash.taxno, 
    v_sal_stax_salecash.docdate, 
    v_sal_stax_salecash.personid, 
    v_sal_stax_salecash.name, 
    v_sal_stax_salecash.Status
FROM v_sal_stax_salecash
UNION ALL
SELECT v_sal_stax_saledebit.taxno, 
    v_sal_stax_saledebit.docdate, 
    v_sal_stax_saledebit.personid, 
    v_sal_stax_saledebit.Name, 
    v_sal_stax_saledebit.Status
FROM v_sal_stax_saledebit
GO

CREATE VIEW v_sal_stransport_unit
AS
SELECT tb_sys_transport.transportid, 
    tb_sys_transport.transportname, tb_sys_transport_sub.unitid, 
    tb_ic_unit.unitname, tb_sys_transport_sub.transport_value, 
    tb_sys_transport_sub.row_item
FROM tb_ic_unit RIGHT OUTER JOIN
    tb_sys_transport_sub ON 
    tb_ic_unit.unitid = tb_sys_transport_sub.unitid RIGHT OUTER JOIN
    tb_sys_transport ON 
    tb_sys_transport_sub.transportid = tb_sys_transport.transportid
GO

CREATE VIEW v_sys_adetailuser AS SELECT tb_sys_user.GroupID, tb_sys_groupuser.GroupName, tb_sys_user.UserID,      tb_sys_employee.EmpName AS UserName, tb_sys_user.Description,      tb_sys_user.Password, tb_sys_user.[REVOKE],      tb_sys_userpermission.MenuID, tb_sys_menu.MenuName1,      tb_sys_userpermission.Permission, tb_sys_menu.MenuType,      tb_sys_user.AllowSetPermission, tb_sys_user.EmpID FROM ((tb_sys_user LEFT JOIN     tb_sys_groupuser ON tb_sys_user.GroupID = tb_sys_groupuser.GroupID) LEFT      JOIN     tb_sys_employee ON tb_sys_user.EmpID = tb_sys_employee.EmpID) LEFT JOIN     (tb_sys_menu RIGHT JOIN     tb_sys_userpermission ON      tb_sys_menu.MenuID = tb_sys_userpermission.MenuID) ON      (tb_sys_user.UserID = tb_sys_userpermission.UserID) AND      (tb_sys_user.GroupID = tb_sys_userpermission.GroupID)  
GO

CREATE VIEW v_sys_agroupuser AS SELECT tb_sys_grouppermission.GroupID, tb_sys_groupuser.GroupName,      tb_sys_groupuser.Description, tb_sys_grouppermission.Permission,      tb_sys_grouppermission.MenuID, tb_sys_menu.MenuName1,      tb_sys_menu.MenuType FROM tb_sys_groupuser RIGHT JOIN     (tb_sys_menu RIGHT JOIN     tb_sys_grouppermission ON      tb_sys_menu.MenuID = tb_sys_grouppermission.MenuID) ON      tb_sys_groupuser.GroupID = tb_sys_grouppermission.GroupID  
GO

create view v_sys_alogin_logout as SELECT tb_sys_history.EmpID, tb_sys_employee.EmpName, tb_sys_history.BRecDate, tb_sys_history.BRecTime, tb_sys_history.NRecDate, tb_sys_history.NRecTime FROM tb_sys_history LEFT JOIN tb_sys_employee ON tb_sys_history.EmpID = tb_sys_employee.EmpID  
GO

CREATE VIEW v_sys_rep_bankbook
AS
SELECT tb_sys_bankbook.acc_type AS typeid, 
    tb_sys_type_bankbook.typename, 
    tb_sys_bankbook.acc_id, 
    tb_sys_bankbook.acc_name, 
    tb_sys_bankbook.bankid, tb_sys_bank.BankName, 
    tb_sys_bankbook.bankbranchid, 
    tb_sys_bankbranch.bankbranchname, 
    tb_sys_bankbook.bf_term
FROM tb_sys_type_bankbook RIGHT OUTER JOIN
    tb_sys_bankbook ON 
    tb_sys_type_bankbook.typeid = tb_sys_bankbook.acc_type
     LEFT OUTER JOIN
    tb_sys_bankbranch ON 
    tb_sys_bankbook.bankbranchid = tb_sys_bankbranch.bankbranchid
     LEFT OUTER JOIN
    tb_sys_bank ON 
    tb_sys_bankbook.bankid = tb_sys_bank.BankID
GO

CREATE VIEW v_sys_rep_vender
AS
SELECT tb_sys_vendercode.personid, 
    ISNULL(tb_sys_prefix.prefixname, '') 
    + ' ' + tb_ar_person.Name AS personname, 
    tb_sys_vendercode.vendercode
FROM tb_sys_prefix RIGHT OUTER JOIN
    tb_ar_person ON 
    tb_sys_prefix.prefixid = tb_ar_person.PrefixID RIGHT OUTER JOIN
    tb_sys_vendercode ON 
    tb_ar_person.ID = tb_sys_vendercode.personid
GO

CREATE VIEW v_sys_svendercode
AS
SELECT tb_ar_person.ID AS personid, 
    tb_sys_prefix.prefixname + ' ' + tb_ar_person.Name AS personname,
     ISNULL(tb_sys_vendercode.vendercode, '') 
    AS vendercode
FROM tb_sys_vendercode LEFT OUTER JOIN
    tb_ar_person ON 
    tb_sys_vendercode.personid = tb_ar_person.ID LEFT OUTER JOIN
    tb_sys_prefix ON 
    tb_ar_person.PrefixID = tb_sys_prefix.prefixid
GO

create view v_vat_listtax
as
SELECT taxmonth, taxyear, taxdate, taxno, docno_ref, 
    description,vatrate,
   case when effact_tax='-' then  -amount else amount end  as amount, 
  case when effact_tax='-' then -vat else vat end as vat, 
 case when effact_tax='-' then -totalamount else totalamount  end as totalamount ,
 statustax,tb_sys_constant.CompanyName, 
    tb_sys_constant.Address, tb_sys_constant.Tax_No
FROM tb_vat_doctax, tb_sys_constant
GO

CREATE VIEW v_vat_sdoctax
AS
SELECT tb_vat_doctax.taxno, tb_vat_doctax.taxdate, 
    tb_vat_doctax.personid, tb_ap_person.Name, 
    tb_vat_doctax.docno_ref, tb_vat_doctax.statustax
FROM tb_vat_doctax LEFT OUTER JOIN
    tb_ap_person ON tb_vat_doctax.personid = tb_ap_person.ID
GO

CREATE VIEW v_vat_sdoctax_pur
AS
SELECT tb_vat_doctax.taxno, tb_vat_doctax.taxdate, 
    tb_vat_doctax.personid, tb_ap_person.Name, 
    tb_vat_doctax.docno_ref, tb_vat_doctax.statustax
FROM tb_vat_doctax LEFT OUTER JOIN
    tb_ap_person ON 
    tb_vat_doctax.personid = tb_ap_person.ID
WHERE (tb_vat_doctax.statustax = 'P')
GO

CREATE VIEW v_vat_sdoctax_sal
AS
SELECT tb_vat_doctax.taxno, tb_vat_doctax.taxdate, 
    tb_vat_doctax.personid, tb_ar_person.Name, 
    tb_vat_doctax.docno_ref, tb_vat_doctax.statustax
FROM tb_vat_doctax LEFT OUTER JOIN
    tb_ar_person ON 
    tb_vat_doctax.personid = tb_ar_person.ID
WHERE (tb_vat_doctax.statustax = 'S')
GO

