USE [Invoice]
GO

/****** Object:  Table [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC]    Script Date: 1/30/2019 1:28:26 PM ******/
DROP TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC]
GO

/****** Object:  Table [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC]    Script Date: 1/30/2019 1:28:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC](
	[SUPPLIERID] [varchar](10) NOT NULL,
	[COMPANYCODE] [varchar](4) NOT NULL,
	[VATNO] [varchar](20) NOT NULL,
	[CREABY] [varchar](200) NOT NULL,
	[CREADATE] [datetime] NOT NULL,
	[MUTBY] [varchar](200) NOT NULL,
	[MUTDATE] [datetime] NOT NULL,
) ON [TABLEFG]

GO

SET ANSI_PADDING OFF
GO


USE [Invoice]
GO

/****** Object:  Table [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER]    Script Date: 1/30/2019 1:29:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
DROP  TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER]
GO
CREATE TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER](
	[SUPPLIERID] [varchar](10) NOT NULL,
	[COMPANYCODE] [varchar](4) NOT NULL,
	[VATNO] [varchar](20) NOT NULL,
	[CREABY] [varchar](200) NOT NULL,
	[CREADATE] [datetime] NOT NULL,
	[MUTBY] [varchar](200) NOT NULL,
	[MUTDATE] [datetime] NOT NULL,
) ON [TABLEFG]

GO

SET ANSI_PADDING OFF
GO


USE [Invoice]
GO

/****** Object:  StoredProcedure [dbo].[SYNC_SUPPLIERS_SAP]    Script Date: 1/30/2019 2:13:20 PM ******/
DROP PROCEDURE [dbo].[SYNC_SUPPLIERS_SAP]
GO

/****** Object:  StoredProcedure [dbo].[SYNC_SUPPLIERS_SAP]    Script Date: 1/30/2019 2:13:20 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[SYNC_SUPPLIERS_SAP] @result INT OUTPUT AS
BEGIN

	set @result =1
	BEGIN TRAN
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER, CUSTOM_INV_SUPPLIER_TEMP
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_SYNC, CUSTOM_INV_SUPPLIER

	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_BANKACCOUNT, CUSTOM_INV_SUPPLIER_BANKACCOUNT_TEMP
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_BANKACCOUNT_SYNC, CUSTOM_INV_SUPPLIER_BANKACCOUNT

	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX, CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_TEMP
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_SYNC, CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX

	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_VATNUMBER, CUSTOM_INV_SUPPLIER_VATNUMBER_TEMP
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC, CUSTOM_INV_SUPPLIER_VATNUMBER

	COMMIT TRAN
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_TEMP, CUSTOM_INV_SUPPLIER_SYNC
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_BANKACCOUNT_TEMP, CUSTOM_INV_SUPPLIER_BANKACCOUNT_SYNC
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_TEMP, CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_SYNC
	EXEC SP_RENAME CUSTOM_INV_SUPPLIER_VATNUMBER_TEMP, CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC

	set @result = 0
END;

GO


CREATE VIEW [dbo].[VIEW_CUSTOM_INV_SUPPLIER_VATNUMBER]
AS
SELECT        SUPPLIERID, COMPANYCODE, VATNO, CREABY, CREADATE, MUTBY, MUTDATE
FROM            dbo.CUSTOM_INV_SUPPLIER_VATNUMBER

--- add SUPPLIER_VAT to drum_invoices_ktm  nvarchar 255

CREATE VIEW [dbo].[VIEW_VF_UploadTable]
AS
SELECT        hd.DOC_ID, hd.BATCH_CLASS, hd.BATCH_DATE, hd.BATCH_TIME, hd.BATCH_NUMBER, hd.SITE_ID, hd.INVOICE_TYPE, hd.SUPPLIER_NAME, hd.SUPPLIER_ID, hd.COMPANY_ID, hd.COMPANY_VAT,
                         hd.INVOICE_REFERENCE, hd.INVOICE_DATE, hd.INVOICE_TOTAL_EXCL_VAT, hd.INVOICE_TOTAL_VAT, hd.INVOICE_TOTAL, hd.INVOICE_CURRENCY, hd.SCANCOMMENT, hd.DRUM_STATUS,
                         hd.DRUM_ERRORMSG, hd.RECEIVED_DATE, hd.EPDF_REFERENCE, hd.REJECTED_TYPE, hd.REJECTED_COMMENT, ha.path, hd.COMPANY_COUNTRY, hd.SUPPLIER_VAT
FROM            dbo.drum_invoices_ktm AS hd INNER JOIN
                         dbo.drum_invoices_image_ktm AS ha ON hd.DOC_ID = ha.doc_id

GO


USE [Invoice]
GO

/****** Object:  View [dbo].[VIEW_CUSTOM_INV_DRUM]    Script Date: 2/18/2019 1:56:37 PM ******/
DROP VIEW [dbo].[VIEW_CUSTOM_INV_DRUM]
GO

/****** Object:  View [dbo].[VIEW_CUSTOM_INV_DRUM]    Script Date: 2/18/2019 1:56:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







CREATE VIEW [dbo].[VIEW_CUSTOM_INV_DRUM] (
  doc_id,
  Batch_time,
  Batch_date,
  Batch_class,
  site_id,
  batch_number,
  invoice_type,
  supplier_name,
  supplier_id,
  SUPPLIER_IBAN,
  company_id,
  company_vat,
  company_document_group,
  company_freight_type,
  company_country,
  company_brand_group,
  company_approver,
  company_approver_id,
  invoice_reference,
  invoice_date,
  invoice_total_excl_vat,
  invoice_total_vat,
  invoice_total,
  invoice_currency,
  bank_acc_checked,
  path,
  drum_status,
  drum_errormsg,
  drum_uploaddate,
  dr_ftcid,
  rt_techid,
  vfc_dc,
  image,
  Rejected_type,
  Rejected_comment,
  scancomment,
  EPDF_REFERENCE,
  SUPPLIER_VAT
) AS
SELECT DRUM_INVOICES_KTM.DOC_ID,BATCH_TIME,BATCH_DATE,BATCH_CLASS,SITE_ID, BATCH_NUMBER, INVOICE_TYPE,
SUPPLIER_NAME, SUPPLIER_ID,SUPPLIER_IBAN,
COMPANY_ID, COMPANY_VAT, COMPANY_DOCUMENT_GROUP, COMPANY_FREIGHT_TYPE,
COMPANY_COUNTRY,COMPANY_BRAND_GROUP,COMPANY_APPROVER,COMPANY_APPROVER_ID,
INVOICE_REFERENCE,INVOICE_DATE,replace(INVOICE_TOTAL_EXCL_VAT,',','.'),replace(INVOICE_TOTAL_VAT,',','.'),replace(INVOICE_TOTAL,',','.'),INVOICE_CURRENCY,
BANK_ACC_CHECKED, PATH,
DRUM_STATUS, DRUM_ERRORMSG, DRUM_UPLOADDATE, DR_FTCID, RT_TECHID  ,VFC_DC,image, REJECTED_TYPE,REJECTED_COMMENT, SCANCOMMENT,EPDF_REFERENCE,SUPPLIER_VAT
FROM DRUM_INVOICES_KTM, DRUM_INVOICES_IMAGE_KTM
WHERE DRUM_INVOICES_KTM.DOC_ID = DRUM_INVOICES_IMAGE_KTM.DOC_ID
and  company_id in (
select companycode from view_inv_company);



GO