CREATE TABLE iposaccounting.kraitemclass (
itemClsCd CHAR(10) NOT NULL DEFAULT '',
itemClsNm VARCHAR(200) NOT NULL DEFAULT '',
itemClsLvl NUMERIC(10,0) NOT NULL DEFAULT 0,
taxTyCd CHAR(5) NOT NULL DEFAULT '',
mjrTgYn CHAR(1) NOT NULL DEFAULT '',
useYn CHAR(1) NOT NULL DEFAULT '',
CONSTRAINT PK_kraitemclass PRIMARY KEY (itemClsCd)
);
go

ALTER TABLE [iPOSAccounting].[Pack] drop CONSTRAINT [DF_Pack_pack_desc]
GO
alter table iPOSAccounting.pack alter column pack_desc varchar(80)
GO
ALTER TABLE [iPOSAccounting].[Pack] ADD CONSTRAINT [DF_Pack_pack_desc] DEFAULT ('') FOR [pack_desc]
GO

DROP INDEX [IX_Units] ON [iPOSAccounting].[units]
go

GO
ALTER TABLE [iPOSAccounting].[Units] drop CONSTRAINT [DF_Units_unit_desc]
GO

alter table iPOSAccounting.units alter column unit_desc varchar(80)
GO
ALTER TABLE [iPOSAccounting].[Units] ADD CONSTRAINT [DF_Units_unit_desc] DEFAULT ('') FOR [unit_desc]
GO

/****** Object: Index [IX_Units] Script Date: 24/11/2025 17:12:38 ******/
CREATE NONCLUSTERED INDEX [IX_Units] ON [iPOSAccounting].[Units]
(
[unit_desc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = *** ON [PRIMARY]
GO

ALTER table [iPOSAccounting].[VSCU_API_Endpoints_logs]
drop constraint DF__VSCU_API___JSON___548C6944
go
ALTER table [iPOSAccounting].[VSCU_API_Endpoints_logs]
alter COLUMN JSON_In varchar(max)
go

ALTER TABLE [iPOSAccounting].[VSCU_API_Endpoints_logs]
ADD CONSTRAINT DF__VSCU_API___JSON___548C6944 DEFAULT ('')
FOR JSON_In
GO

alter table iPOSAccounting.account
add branch_id varchar(50) NOT NULL default ''
GO

alter table iPOSAccounting.account
add kra_lastupdate datetime NOT NULL default ''
go

alter table iPOSAccounting.stock_f
add kra_lastupdate datetime NOT NULL default ''
go

alter table iPOSAccounting.users
add kra_lastupdate datetime NOT NULL default ''
go

alter table iPOSAccounting.users
add lastupdate datetime NOT NULL default ''
go

CREATE TABLE [iPOSAccounting].[KraNotices](
noticeNo VARCHAR(20) NOT NULL DEFAULT (''),
title VARCHAR(2000) NOT NULL DEFAULT (''),
cont VARCHAR(max) NOT NULL DEFAULT (''),
dtlUrl VARCHAR(300) NOT NULL DEFAULT (''),
regrNm VARCHAR(100) NOT NULL DEFAULT (''),
regDt VARCHAR(20) NOT NULL DEFAULT (''),
[timestamp_column] [timestamp] NOT NULL,
[identity_column] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newsequentialid()),
[LastDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_KraNotices] PRIMARY KEY CLUSTERED
(
[identity_column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = *** ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER table [iPOSAccounting].[KraNotices]
drop constraint DF__KraNotices__cont__6932806F
go
alter table iposaccounting.KraNotices
alter column cont VARCHAR(max)
GO

ALTER TABLE [iPOSAccounting].[KraNotices]
ADD CONSTRAINT DF__KraNotices__cont__6932806F DEFAULT ('')
FOR cont
GO

alter table iposaccounting.account
alter column salesman_id char(5)
go


CREATE TABLE [iposaccounting].[ImportItems] (
-- Primary Key
[Recno] BIGINT IDENTITY(1,1) PRIMARY KEY,
-- Foreign Key to Header
[TaskCd] VARCHAR(50) NOT NULL,
-- Item Details
[DclDe] VARCHAR(16) NOT NULL,
[ItemSeq] INT NOT NULL,
[DclNo] VARCHAR(20) NOT NULL,
[HsCd] VARCHAR(20) NOT NULL,
[ItemNm] NVARCHAR(500) NOT NULL, -- NVARCHAR for special characters
[ImptItemsttsCd] VARCHAR(10) NOT NULL,
-- Country Information
[OrgnNatCd] VARCHAR(5) NOT NULL,
[ExptNatCd] VARCHAR(5) NOT NULL,
-- Package Information
[Pkg] DECIMAL(18, 2),
[PkgUnitCd] VARCHAR(10),
[Qty] DECIMAL(18, 2),
[QtyUnitCd] VARCHAR(10),
[TotWt] DECIMAL(18, 2),
[NetWt] DECIMAL(18, 2),
-- Parties Involved
[SpplrNm] NVARCHAR(255),
[AgntNm] NVARCHAR(255),
-- Financial Information
[InvcFcurAmt] DECIMAL(18, 2),
[InvcFcurCd] VARCHAR(5),
[InvcFcurExcrt] DECIMAL(18, 6),
-- Local Currency Equivalent (calculated column)
[InvcLocalAmt] DECIMAL(18, 6),
[StockCode] VARCHAR(12) default '',
[KraLastUpdate] DATETIME ,
[KRAProcesed] char(1) default '0',
-- Timestamps
[CreatedAt] DATETIME DEFAULT GETDATE(),
kralresultdate char(16) not null default '',
kraupdate char(1) not null default '0',
-- Indexes
INDEX IX_CustomsDeclarationItems_Recno (Recno),
INDEX IX_CustomsDeclarationItems_ItemSeq (ItemSeq),
INDEX IX_CustomsDeclarationItems_HsCd (HsCd)
);
go

alter table iposaccounting.ImportItems
add kralresultdate char(16) not null default ''
go

alter table iposaccounting.ImportItems
add kraupdate char(1) not null default '0'
GO

alter table iposaccounting.account
add kralresultdate char(16) not null default ''
go

alter table iposaccounting.account
add kraupdate char(1) not null default '0'
GO

alter table iposaccounting.account
add loca_id char(4) not null default ''
go

alter table iposaccounting.stock_f
add kraupdate char(1) not null default '0'
GO

alter table iposaccounting.stock_f
add loca_id char(4) not null default ''
go

alter table iposaccounting.stock_f
add kralresultdate char(16) not null default ''
go

alter table iposaccounting.users
add loca_id char(4) not null default ''
go

alter table iposaccounting.users
add kraupdate char(1) not null default '0'
GO

alter table iposaccounting.users
add kralresultdate char(16) not null default ''
go

alter table iposaccounting.location
add kraupdate char(1) not null default '0'
GO

alter table iposaccounting.location
add kralresultdate char(16) not null default ''
go

alter table iposaccounting.Bom
add kralresultdate char(16) not null default ''
GO

alter table iposaccounting.Bom
add kraupdate char(1) not null default '0'
GO

CREATE TABLE [iposaccounting].Reasons (
-- Primary Key
[Recno] BIGINT IDENTITY(1,1) PRIMARY KEY,
Reason_ID char(2) not null default '',
Reason_Desc varchar(50) not null default '',

[CreatedAt] DATETIME DEFAULT GETDATE(),

-- Indexes
INDEX IX_reson_Recno (Recno),
INDEX IX_reasonid (Reason_ID ),
INDEX IX_reasondesc (Reason_Desc )

);
go
alter table iposaccounting.TransactionSummary
add Reason_ID char(2) not null default ''
GO


go
alter table iposaccounting.TransactionSummary
add Status_ID char(2) not null default ''
GO

alter table iposaccounting.TransactionSummary
add ProgressStatus_ID char(2) not null default ''

go
alter table iposaccounting.OrderSummary
add Reason_ID char(2) not null default ''
GO

alter table iposaccounting.OrderSummary
add Status_ID char(2) not null default ''
GO

alter table iposaccounting.OrderSummary
add ProgressStatus_ID char(2) not null default ''
GO


alter table iposaccounting.Vouchers
add KRAMap_ID char(2) not null default ''
GO

CREATE TABLE [iposaccounting].Status (
-- Primary Key
[Recno] BIGINT IDENTITY(1,1) PRIMARY KEY,
Status_ID char(2) not null default '',
Status_Desc varchar(50) not null default '',

[CreatedAt] DATETIME DEFAULT GETDATE(),

-- Indexes
INDEX IX_staRecno (Recno),
INDEX IX_Statusid (Status_ID ),
INDEX IX_Statusdesc (Status_Desc )

);
go

CREATE TABLE [iposaccounting].ProgessStatus (
-- Primary Key
[Recno] BIGINT IDENTITY(1,1) PRIMARY KEY,
ProgessStatus_ID char(2) not null default '',
ProgessStatus_Desc varchar(50) not null default '',

[CreatedAt] DATETIME DEFAULT GETDATE(),

-- Indexes
INDEX IX_ps_Recno (Recno),
INDEX IX_ProgessStatusid (ProgessStatus_ID ),
INDEX IX_ProgessStatusdesc (ProgessStatus_Desc )

);
go

CREATE TABLE [iposaccounting].Paymethod (
-- Primary Key
[Recno] BIGINT IDENTITY(1,1) PRIMARY KEY,
Paymethod_ID char(2) not null default '',
Paymethod_Desc varchar(50) not null default '',
[CreatedAt] DATETIME DEFAULT GETDATE(),
-- Indexes
INDEX IX_Recno (Recno),
INDEX IX_Paymethodid (Paymethod_ID ),
INDEX IX_Paymethoddesc (Paymethod_Desc )

);
go

CREATE TABLE [iposaccounting].TransactionSummaryPayment(
-- Primary Key
[Recno] BIGINT IDENTITY(1,1) PRIMARY KEY,
Loca_id char(4) not null default '',
Till_id char(2) not null default '',
Type_ID char(2) not null default '',
Docno char(10) not null default '',
Paymethod_ID char(2) not null default '',
Amount decimal(14,4) ,
Reference char(10) not null default '',
Account char(10) not null default '',
Notes char(10) not null default '',
[CreatedAt] DATETIME DEFAULT GETDATE(),
-- Indexes
INDEX IX_Recno (Recno),
INDEX IX_tp_docno (Docno),
INDEX IX_till_id (Till_Id),
INDEX IX_type_id (Type_ID),
INDEX IX_loca_id (Loca_ID),
INDEX IX_Paymethodid (Paymethod_ID )


);
go

alter table iposaccounting.Location
add KRA_Bhfld char(2) not null default ''
GO

alter table iposaccounting.shoptill
add KRA_dvcsrlno varchar(100) not null default ''
GO

alter table iposaccounting.vat
add isactive bit not null default 1
GO

alter table iposaccounting.pack
add isactive bit not null default 1
GO

alter table iposaccounting.units
add isactive bit not null default 1
GO

alter table iposaccounting.account
add kra_data varchar(1500) not null default ''
GO

alter table iposaccounting.account
add iskra_valid bit not null default 1
GO

CREATE TABLE iposaccounting.kra_purchaseheader (
recno INT PRIMARY KEY IDENTITY(1,1),
spplrTin VARCHAR(20) DEFAULT '',
spplrNm VARCHAR(100) DEFAULT '',
spplrBhfId VARCHAR(10) DEFAULT '',
spplrInvcNo INT DEFAULT 0,
rcptTyCd CHAR(1) DEFAULT '',
pmtTyCd VARCHAR(5) DEFAULT '',
cfmDt DATETIME NULL,
salesDt VARCHAR(8) DEFAULT '',
stockRlsDt DATETIME NULL,
totItemCnt INT DEFAULT 0,
taxblAmtA DECIMAL(18,2) DEFAULT 0,
taxblAmtB DECIMAL(18,2) DEFAULT 0,
taxblAmtC DECIMAL(18,2) DEFAULT 0,
taxblAmtD DECIMAL(18,2) DEFAULT 0,
totTaxblAmt DECIMAL(18,2) DEFAULT 0,
totTaxAmt DECIMAL(18,2) DEFAULT 0,
totAmt DECIMAL(18,2) DEFAULT 0,
remark NVARCHAR(MAX) DEFAULT '',
resultDt VARCHAR(14) DEFAULT '',
createdat DATETIME DEFAULT GETDATE(),
[KraLastUpdate] DATETIME ,
[KRAProcesed] char(1) default '0'
);
go
CREATE TABLE iposaccounting.kra_purchase_detail (
recno INT PRIMARY KEY IDENTITY(1,1),
spplrInvcNo INT DEFAULT 0, -- Added as per your request
itemSeq INT DEFAULT 0,
itemCd VARCHAR(50) DEFAULT '',
itemClsCd VARCHAR(50) DEFAULT '',
itemNm VARCHAR(250) DEFAULT '',
bcd VARCHAR(50) DEFAULT '',
pkgUnitCd VARCHAR(10) DEFAULT '',
pkg DECIMAL(18,2) DEFAULT 0,
qtyUnitCd VARCHAR(10) DEFAULT '',
qty DECIMAL(18,4) DEFAULT 0,
prc DECIMAL(18,2) DEFAULT 0,
splyAmt DECIMAL(18,2) DEFAULT 0,
dcRt DECIMAL(18,2) DEFAULT 0,
dcAmt DECIMAL(18,2) DEFAULT 0,
taxTyCd CHAR(1) DEFAULT '',
taxblAmt DECIMAL(18,2) DEFAULT 0,
taxAmt DECIMAL(18,2) DEFAULT 0,
totAmt DECIMAL(18,2) DEFAULT 0,
[StockCode] VARCHAR(12) default '',
[KraLastUpdate] DATETIME ,
[KRAProcesed] char(1) default '0'
);
go

alter table iposaccounting.vouchers
add KRA_docno char(10) not null default ''
GO

alter table iposaccounting.VoucherTransactions
add KRA_docno char(10) not null default ''
GO

alter table iposaccounting.TransactionSummary
add KRA_docno char(10) not null default ''
GO

alter table iposaccounting.TransactionSummary
add KRA_E_Tims_Return_variable varchar(2500) not null default ''
GO

alter table iposaccounting.TransactionSummary
add VSCU_API_Endpoints_logs_Recno bigint
GO

alter table iposaccounting.OrderSummary
add KRA_docno char(10) not null default ''
GO

alter table iposaccounting.OrderSummary
add KRA_E_Tims_Return_variable varchar(2500) not null default ''
GO

alter table iposaccounting.OrderSummary
add VSCU_API_Endpoints_logs_Recno bigint
GO

GO