daily pastebin goal
39%
SHARE
TWEET

Untitled

a guest Feb 21st, 2019 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [Invoice]
  2. GO
  3.  
  4. /****** Object:  Table [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC]    Script Date: 1/30/2019 1:28:26 PM ******/
  5. DROP TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC]
  6. GO
  7.  
  8. /****** Object:  Table [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC]    Script Date: 1/30/2019 1:28:26 PM ******/
  9. SET ANSI_NULLS ON
  10. GO
  11.  
  12. SET QUOTED_IDENTIFIER ON
  13. GO
  14.  
  15. SET ANSI_PADDING ON
  16. GO
  17.  
  18. CREATE TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC](
  19.     [SUPPLIERID] [varchar](10) NOT NULL,
  20.     [COMPANYCODE] [varchar](4) NOT NULL,
  21.     [VATNO] [varchar](20) NOT NULL,
  22.     [CREABY] [varchar](200) NOT NULL,
  23.     [CREADATE] [datetime] NOT NULL,
  24.     [MUTBY] [varchar](200) NOT NULL,
  25.     [MUTDATE] [datetime] NOT NULL,
  26. ) ON [TABLEFG]
  27.  
  28. GO
  29.  
  30. SET ANSI_PADDING OFF
  31. GO
  32.  
  33.  
  34. USE [Invoice]
  35. GO
  36.  
  37. /****** Object:  Table [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER]    Script Date: 1/30/2019 1:29:00 PM ******/
  38. SET ANSI_NULLS ON
  39. GO
  40.  
  41. SET QUOTED_IDENTIFIER ON
  42. GO
  43.  
  44. SET ANSI_PADDING ON
  45. GO
  46. DROP  TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER]
  47. GO
  48. CREATE TABLE [dbo].[CUSTOM_INV_SUPPLIER_VATNUMBER](
  49.     [SUPPLIERID] [varchar](10) NOT NULL,
  50.     [COMPANYCODE] [varchar](4) NOT NULL,
  51.     [VATNO] [varchar](20) NOT NULL,
  52.     [CREABY] [varchar](200) NOT NULL,
  53.     [CREADATE] [datetime] NOT NULL,
  54.     [MUTBY] [varchar](200) NOT NULL,
  55.     [MUTDATE] [datetime] NOT NULL,
  56. ) ON [TABLEFG]
  57.  
  58. GO
  59.  
  60. SET ANSI_PADDING OFF
  61. GO
  62.  
  63.  
  64. USE [Invoice]
  65. GO
  66.  
  67. /****** Object:  StoredProcedure [dbo].[SYNC_SUPPLIERS_SAP]    Script Date: 1/30/2019 2:13:20 PM ******/
  68. DROP PROCEDURE [dbo].[SYNC_SUPPLIERS_SAP]
  69. GO
  70.  
  71. /****** Object:  StoredProcedure [dbo].[SYNC_SUPPLIERS_SAP]    Script Date: 1/30/2019 2:13:20 PM ******/
  72. SET ANSI_NULLS ON
  73. GO
  74.  
  75. SET QUOTED_IDENTIFIER ON
  76. GO
  77.  
  78.  
  79.  
  80.  
  81. CREATE PROCEDURE [dbo].[SYNC_SUPPLIERS_SAP] @result INT OUTPUT AS
  82. BEGIN
  83.  
  84.     set @result =1
  85.     BEGIN TRAN
  86.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER, CUSTOM_INV_SUPPLIER_TEMP
  87.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_SYNC, CUSTOM_INV_SUPPLIER
  88.  
  89.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_BANKACCOUNT, CUSTOM_INV_SUPPLIER_BANKACCOUNT_TEMP
  90.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_BANKACCOUNT_SYNC, CUSTOM_INV_SUPPLIER_BANKACCOUNT
  91.  
  92.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX, CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_TEMP
  93.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_SYNC, CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX
  94.    
  95.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_VATNUMBER, CUSTOM_INV_SUPPLIER_VATNUMBER_TEMP
  96.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC, CUSTOM_INV_SUPPLIER_VATNUMBER
  97.    
  98.     COMMIT TRAN
  99.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_TEMP, CUSTOM_INV_SUPPLIER_SYNC
  100.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_BANKACCOUNT_TEMP, CUSTOM_INV_SUPPLIER_BANKACCOUNT_SYNC
  101.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_TEMP, CUSTOM_INV_SUPPLIER_WITHHOLDINGTAX_SYNC
  102.     EXEC SP_RENAME CUSTOM_INV_SUPPLIER_VATNUMBER_TEMP, CUSTOM_INV_SUPPLIER_VATNUMBER_SYNC
  103.    
  104.     set @result = 0
  105. END;
  106.  
  107. GO
  108.  
  109.  
  110. CREATE VIEW [dbo].[VIEW_CUSTOM_INV_SUPPLIER_VATNUMBER]
  111. AS
  112. SELECT        SUPPLIERID, COMPANYCODE, VATNO, CREABY, CREADATE, MUTBY, MUTDATE
  113. FROM            dbo.CUSTOM_INV_SUPPLIER_VATNUMBER
  114.  
  115. --- add SUPPLIER_VAT to drum_invoices_ktm  nvarchar 255
  116.  
  117. CREATE VIEW [dbo].[VIEW_VF_UploadTable]
  118. AS
  119. 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,
  120.                          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,
  121.                          hd.DRUM_ERRORMSG, hd.RECEIVED_DATE, hd.EPDF_REFERENCE, hd.REJECTED_TYPE, hd.REJECTED_COMMENT, ha.path, hd.COMPANY_COUNTRY, hd.SUPPLIER_VAT
  122. FROM            dbo.drum_invoices_ktm AS hd INNER JOIN
  123.                          dbo.drum_invoices_image_ktm AS ha ON hd.DOC_ID = ha.doc_id
  124.  
  125. GO
  126.  
  127.  
  128. USE [Invoice]
  129. GO
  130.  
  131. /****** Object:  View [dbo].[VIEW_CUSTOM_INV_DRUM]    Script Date: 2/18/2019 1:56:37 PM ******/
  132. DROP VIEW [dbo].[VIEW_CUSTOM_INV_DRUM]
  133. GO
  134.  
  135. /****** Object:  View [dbo].[VIEW_CUSTOM_INV_DRUM]    Script Date: 2/18/2019 1:56:37 PM ******/
  136. SET ANSI_NULLS ON
  137. GO
  138.  
  139. SET QUOTED_IDENTIFIER ON
  140. GO
  141.  
  142.  
  143.  
  144.  
  145.  
  146.  
  147.  
  148. CREATE VIEW [dbo].[VIEW_CUSTOM_INV_DRUM] (
  149.   doc_id,
  150.   Batch_time,
  151.   Batch_date,
  152.   Batch_class,
  153.   site_id,
  154.   batch_number,
  155.   invoice_type,
  156.   supplier_name,
  157.   supplier_id,
  158.   SUPPLIER_IBAN,
  159.   company_id,
  160.   company_vat,
  161.   company_document_group,
  162.   company_freight_type,
  163.   company_country,
  164.   company_brand_group,
  165.   company_approver,
  166.   company_approver_id,
  167.   invoice_reference,
  168.   invoice_date,
  169.   invoice_total_excl_vat,
  170.   invoice_total_vat,
  171.   invoice_total,
  172.   invoice_currency,
  173.   bank_acc_checked,
  174.   path,
  175.   drum_status,
  176.   drum_errormsg,
  177.   drum_uploaddate,
  178.   dr_ftcid,
  179.   rt_techid,
  180.   vfc_dc,
  181.   image,
  182.   Rejected_type,
  183.   Rejected_comment,
  184.   scancomment,
  185.   EPDF_REFERENCE,
  186.   SUPPLIER_VAT
  187. ) AS
  188. SELECT DRUM_INVOICES_KTM.DOC_ID,BATCH_TIME,BATCH_DATE,BATCH_CLASS,SITE_ID, BATCH_NUMBER, INVOICE_TYPE,
  189. SUPPLIER_NAME, SUPPLIER_ID,SUPPLIER_IBAN,
  190. COMPANY_ID, COMPANY_VAT, COMPANY_DOCUMENT_GROUP, COMPANY_FREIGHT_TYPE,
  191. COMPANY_COUNTRY,COMPANY_BRAND_GROUP,COMPANY_APPROVER,COMPANY_APPROVER_ID,
  192. INVOICE_REFERENCE,INVOICE_DATE,replace(INVOICE_TOTAL_EXCL_VAT,',','.'),replace(INVOICE_TOTAL_VAT,',','.'),replace(INVOICE_TOTAL,',','.'),INVOICE_CURRENCY,
  193. BANK_ACC_CHECKED, PATH,
  194. DRUM_STATUS, DRUM_ERRORMSG, DRUM_UPLOADDATE, DR_FTCID, RT_TECHID  ,VFC_DC,image, REJECTED_TYPE,REJECTED_COMMENT, SCANCOMMENT,EPDF_REFERENCE,SUPPLIER_VAT
  195. FROM DRUM_INVOICES_KTM, DRUM_INVOICES_IMAGE_KTM
  196. WHERE DRUM_INVOICES_KTM.DOC_ID = DRUM_INVOICES_IMAGE_KTM.DOC_ID
  197. and  company_id in (
  198. select companycode from view_inv_company);
  199.  
  200.  
  201.  
  202. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top