Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- @author Developer -- @action createHeader for output file xixi_lame1 with formatted fields -- @includes using external function(made by octavio coreta [occo] and stored in xixi) -- @created on 2010-02-25 CREATE PROCEDURE [dbo].[ADESOES_INS_Header] @data_envio as date, @campanha as varchar(20) AS BEGIN DECLARE @lastID int; DECLARE @idFormatted varchar(6); DECLARE @dayFormatted varchar(2); DECLARE @monthFormatted varchar(2); DECLARE @dateFormatted varchar(8); SET NOCOUNT ON; IF( @campanha = 'xixi_lame1' ) BEGIN SET @lastID = ( SELECT ISNULL(MAX(idHeader),0) FROM xixi_lame1_adesoes_header ) + 1; SET @idFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(@lastID,0,6,1) ); SET @dayFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(DAY(GETDATE()),0,2,1) ); SET @monthFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(MONTH(GETDATE()),0,2,1) ); SET @dateFormatted = @dayFormatted + @monthFormatted + CAST(YEAR(GETDATE()) AS VARCHAR(4)); INSERT INTO [dbo].xixi_lame1_adesoes_header( [data_envio] ,[idHeader] ,[RECORD_TYPE] ,[BRAND_CODE] ,[SERVICE_CODE] ,[SERIAL_NUMBER] ,[FILE_TYPE] ,[CREATION_DATE] ,[FILLER]) VALUES( @data_envio, @lastID, 'H', 'CP', 'CP', @idFormatted, 'APC', @dateFormatted, ''); END IF( @campanha = 'xixi_lame2' ) BEGIN SET @lastID = ( SELECT ISNULL(MAX(idHeader),0) FROM xixi_lame2_adesoes_header ) + 1; SET @idFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(@lastID,0,6,1) ); SET @dayFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(DAY(GETDATE()),0,2,1) ); SET @monthFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(MONTH(GETDATE()),0,2,1) ); SET @dateFormatted = @dayFormatted + @monthFormatted + CAST(YEAR(GETDATE()) AS VARCHAR(4)); INSERT INTO [dbo].xixi_lame2_adesoes_header( [data_envio] ,[idHeader] ,[RECORD_TYPE] ,[BRAND_CODE] ,[SERVICE_CODE] ,[SERIAL_NUMBER] ,[FILE_TYPE] ,[CREATION_DATE] ,[FILLER]) VALUES( @data_envio, @lastID, 'H', 'CP', 'CP', @idFormatted, 'APC', @dateFormatted, ''); END IF( @campanha = 'xixi_bst' ) BEGIN SET @lastID = ( SELECT ISNULL(MAX(idHeader),0) FROM xixi_bst_adesoes_header ) + 1; SET @idFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(@lastID,0,6,1) ); SET @dayFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(DAY(GETDATE()),0,2,1) ); SET @monthFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(MONTH(GETDATE()),0,2,1) ); SET @dateFormatted = @dayFormatted + @monthFormatted + CAST(YEAR(GETDATE()) AS VARCHAR(4)); INSERT INTO [dbo].xixi_bst_adesoes_header( [data_envio] ,[idHeader] ,[RECORD_TYPE] ,[BRAND_CODE] ,[SERVICE_CODE] ,[SERIAL_NUMBER] ,[FILE_TYPE] ,[CREATION_DATE] ,[FILLER]) VALUES( @data_envio, @lastID, 'H', 'CP', 'CP', @idFormatted, 'APC', @dateFormatted, ''); END IF( @campanha = 'xixi_cgd' ) BEGIN SET @lastID = ( SELECT ISNULL(MAX(idHeader),0) FROM xixi_cgd_adesoes_header ) + 1; SET @idFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(@lastID,0,6,1) ); SET @dayFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(DAY(GETDATE()),0,2,1) ); SET @monthFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(MONTH(GETDATE()),0,2,1) ); SET @dateFormatted = @dayFormatted + @monthFormatted + CAST(YEAR(GETDATE()) AS VARCHAR(4)); INSERT INTO [dbo].xixi_cgd_adesoes_header( [data_envio] ,[idHeader] ,[RECORD_TYPE] ,[BRAND_CODE] ,[SERVICE_CODE] ,[SERIAL_NUMBER] ,[FILE_TYPE] ,[CREATION_DATE] ,[FILLER]) VALUES( @data_envio, @lastID, 'H', 'CP', 'CP', @idFormatted, 'APC', @dateFormatted, ''); END IF( @campanha = 'xixi_ccall' ) BEGIN SET @lastID = ( SELECT ISNULL(MAX(idHeader),0) FROM xixi_ccall_adesoes_header ) + 1; SET @idFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(@lastID,0,6,1) ); SET @dayFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(DAY(GETDATE()),0,2,1) ); SET @monthFormatted = ( SELECT TP_clientes_xixi.dbo.fnPreencheEspacos(MONTH(GETDATE()),0,2,1) ); SET @dateFormatted = @dayFormatted + @monthFormatted + CAST(YEAR(GETDATE()) AS VARCHAR(4)); INSERT INTO [dbo].xixi_ccall_adesoes_header( [data_envio] ,[idHeader] ,[RECORD_TYPE] ,[BRAND_CODE] ,[SERVICE_CODE] ,[SERIAL_NUMBER] ,[FILE_TYPE] ,[CREATION_DATE] ,[FILLER]) VALUES( @data_envio, @lastID, 'H', 'CP', 'CP', @idFormatted, 'APC', @dateFormatted, ''); END END
Advertisement
Add Comment
Please, Sign In to add comment
