You can also try running the select query and see whether it is returning any NULLs on that column
USE DmStagingVD
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then '19'
when PSEFCT = 0 then '19'
else '20'
end
+ case
when PSEFYR < 10 and PSEFYR <> 0 then '0' + Cast(PSEFYR as varchar(1))
when pSEFYR = 0 THEN '01'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then '0' + Cast(PSEFMO as varchar(1))
when len(PSEFMO) = 2 then cast(PSEFMO as varchar(2))
when PSEFMO = 0 then '01'
END
+ '-'
+ case
when len(PSEFDA) = 1 and PSEFDA <> 0 then '0' + Cast(PSEFDA as varchar(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as varchar(2))
when PSEFDA = 0 then '01'
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
,
case
when PSEXYR = 0 then '19'
when PSEXYR = 99 then '21'
when PSEXCT = 1 THEN '20'
end
+ case
when PSEXYR = 0 THEN '01'
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))
when pSEXYR = 99 THEN '99'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when PSEXMO = 0 THEN '01'
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))
when PSEXMO = 99 then '12'
END
+ '-'
+ case
when PSEXDA = 0 THEN '01'
when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))
when PSEXDA = 99 then '31'
end AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
BOM_APPROVED nvarchar(3)
BOM_APPROVERPARTYNAME nvarchar(100)
BOM_APPROVERPARTYNUMBER nvarchar(40)
BOM_APPROVERPERSONNELNUMBER nvarchar(25)
BOM_BOMID nvarchar(20)
BOM_CHECKBOM nvarchar(3)
BOM_ITEMGROUPID nvarchar(10)
BOM_NAME nvarchar(60)
BOM_ROUTE_CONFIGGROUPID nvarchar(10)
BOM_ROUTE_LINENUM numeric(32, 16)
BOM_ROUTE_NAME nvarchar(60)
BOMCONSUMP nvarchar(10)
BOMQTY numeric(32, 16)
BOMQTYSERIE numeric(32, 16)
BOMTYPE nvarchar(10)
CALCULATION nvarchar(10)
CONFIGGROUPID nvarchar(10)
CONFIGID nvarchar(10)
CONSTANT numeric(32, 16)
DEFINITIONGROUP nvarchar(60)
DENSITY numeric(32, 16)
DEPTH numeric(32, 16)
ENDSCHEDCONSUMP nvarchar(10)
EXECUTIONID nvarchar(90)
FORMULA nvarchar(10)
FROMDATE datetime
HEIGHT numeric(32, 16)
INVENTBATCHID nvarchar(20)
INVENTCOLORID nvarchar(10)
INVENTDIMID nvarchar(20)
INVENTLOCATIONID nvarchar(10)
INVENTSERIALID nvarchar(20)
INVENTSITEID nvarchar(10)
INVENTSIZEID nvarchar(10)
INVENTSTYLEID nvarchar(10)
ISSELECTED int
ITEMBOMID nvarchar(20)
ITEMID nvarchar(20)
ITEMPBAID nvarchar(20)
ITEMROUTEID nvarchar(20)
LINENUM numeric(32, 16)
OPRNUM int
PDSBASEVALUE nvarchar(30)
PDSCWQTY numeric(32, 16)
PDSINGREDIENTTYPE nvarchar(12)
PDSINHERITCOPRODUCTBATCHATTRIB nvarchar(3)
PDSINHERITCOPRODUCTSHELFLIFE nvarchar(3)
PDSINHERITENDITEMBATCHATTRIB nvarchar(3)
PDSINHERITENDITEMSHELFLIFE nvarchar(3)
PMFFORMULAPCT numeric(32, 16)
PMFPCTENABLE nvarchar(3)
PMFPLANGROUPID nvarchar(10)
PMFPLANGROUPPRIORITY int
PMFSCALABLE nvarchar(3)
POSITION nvarchar(30)
PRODFLUSHINGPRINCIP nvarchar(10)
PROJSETSUBPRODTOCONSUMED nvarchar(10)
ROUNDUP nvarchar(10)
ROUNDUPQTY numeric(32, 16)
ROWID int
SCRAPCONST numeric(32, 16)
SCRAPVAR numeric(32, 16)
TODATE datetime
TRANSFERSTATUS int
UNITID nvarchar(10)
VENDID nvarchar(20)
WIDTH numeric(32, 16)
WMSLOCATIONID nvarchar(10)
WMSPALLETID nvarchar(18)
WRKCTRCONSUMPTION nvarchar(10)
RECVERSION int
PARTITION bigint
RECID bigint
USE DmStagingVD
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then '19'
when PSEFCT = 0 then '19'
else '20'
end
+ case
when PSEFYR = NULL then '01'
when PSEFYR < 10 and PSEFYR <> 0 then '0' + Cast(PSEFYR as varchar(1))
when pSEFYR = 0 THEN '01'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when PSEFMO = NULL then '01'
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then '0' + Cast(PSEFMO as varchar(1))
when len(PSEFMO) = 2 then cast(PSEFMO as varchar(2))
when PSEFMO = 0 then '01'
END
+ '-'
+ case
when PSEFDA = NUll then '01'
when len(PSEFDA) = 1 and PSEFDA <> 0 then '0' + Cast(PSEFDA as varchar(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as varchar(2))
when PSEFDA = 0 then '01'
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
, convert(datetime,
case
when PSEXYR = 99 then '21'
when PSEXCT = 1 THEN '20'
else '19'
end
+ case
when PSEXYR = 0 THEN '01'
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))
when pSEXYR = 99 THEN '99'
else Cast(PSEFYR as varchar(2))
END
+ '-'
+ case
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))
when PSEXMO = 99 then '12'
else '01'
END
+ '-'
+ case
when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))
when PSEXDA = 99 then '31'
else '01'
end) AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
USE DmStagingVD
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then '19'
when PSEFCT = 0 then '19'
else '20'
end
+ case
when PSEFYR = NULL then '01'
when PSEFYR < 10 and PSEFYR <> 0 then '0' + Cast(PSEFYR as varchar(1))
when pSEFYR = 0 THEN '01'
else Right('0' + Cast(PSEFYR as varchar(2)), 2) --!!
END
+ '-'
+ case
when PSEFMO = NULL then '01'
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then '0' + Cast(PSEFMO as varchar(1))
when len(PSEFMO) = 2 then cast(PSEFMO as varchar(2))
when PSEFMO = 0 then '01'
--can PSEFMO *ever* have a length of 0 or 3+? --!!
END
+ '-'
+ case
when PSEFDA = NUll then '01'
when len(PSEFDA) = 1 and PSEFDA <> 0 then '0' + Cast(PSEFDA as varchar(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as varchar(2))
when PSEFDA = 0 then '01'
--can PSEFDA *ever* have a length of 0 or 3+? --!!
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
, convert(datetime,
case
when PSEXYR = 99 then '21'
when PSEXCT = 1 THEN '20'
else '19'
end
+ case
when PSEXYR = 0 THEN '01'
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as varchar(1))
when pSEXYR = 99 THEN '99'
else Right('0' + Cast(PSEFYR as varchar(2)), 2) --!!
END
+ '-'
+ case
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as varchar(1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as varchar(2))
when PSEXMO = 99 then '12'
else '01'
END
+ '-'
+ case
when len(PSEXDA) = 1 then '0' + Cast(PSEXDA as varchar(1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as varchar(2))
when PSEXDA = 99 then '31'
else '01'
end) AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
-- database to use
USE DmStagingVD
---- table the results write to
INSERT INTO [DMFBOMENTITY]
SELECT
cast('YES' as nvarchar(3)) as [BOM_APPROVED]
,' ' as [BOM_APPROVERPARTYNAME]
,' ' as [BOM_APPROVERPARTYNUMBER]
,' ' as [BOM_APPROVERPERSONNELNUMBER]
,[PSPMRN] as [BOM_BOMID]
,' ' as [BOM_CHECKBOM]
,' ' as [BOM_ITEMGROUPID]
,[IMDSC] as[BOM_NAME]
,' ' as [BOM_ROUTE_CONFIGGROUPID]
,0 as [BOM_ROUTE_LINENUM]
,' ' as [BOM_ROUTE_NAME]
,'Variable' as [BOMCONSUMP]
,CASE WHEN [PSQTY] > 0 THEN [PSQTY] ELSE 1 END as [BOMQTY]
,1 as [BOMQTYSERIE]
,CASE WHEN [IMPHAN] in ('N','P') THEN 'Phantom'
WHEN [IMPHAN] = ' ' THEN 'ITEM'
END as [BOMTYPE]
,'YES' as [CALCULATION]
,' ' as [CONFIGGROUPID]
,' ' as [CONFIGID]
,0 as [CONSTANT]
,' ' as [DEFINITIONGROUP]
,0 as [DENSITY]
,0 as [DEPTH]
,' ' as [ENDSCHEDCONSUMP]
,' ' as [EXECUTIONID]
,' ' as [FORMULA]
,
case
when PSEFYR =0 then 19
when PSEFCT = 0 then 19
else 20
end
+ case
when PSEFYR = NULL then 01
when PSEFYR < 10 and PSEFYR <> 0 then 0 + Cast(PSEFYR as numeric(1))
when pSEFYR = 0 THEN 01
else Cast(PSEFYR as numeric(2))
END
+ '-'
+ case
when PSEFMO = NULL then 01
when LEN(PSEFMO) = 1 and PSEFMO <> 0 then 0 + Cast(PSEFMO as numeric(1))
when len(PSEFMO) = 2 then cast(PSEFMO as numeric(2))
when PSEFMO = 0 then 01
END
+ '-'
+ case
when PSEFDA = NUll then 01
when len(PSEFDA) = 1 and PSEFDA <> 0 then 0 + Cast(PSEFDA as numeric(1))
when len(PSEFDA) = 2 then Cast(PSEFDA as numeric(2))
when PSEFDA = 0 then 01
end AS [FROMDATE]
,0 as [HEIGHT]
,' ' as [INVENTBATCHID]
,' ' as [INVENTCOLORID]
,' ' as [INVENTDIMID]
,' ' as [INVENTLOCATIONID]
,' ' as [INVENTSERIALID]
,'01' as [INVENTSITEID]
,' ' as [INVENTSIZEID]
,' ' as [INVENTSTYLEID]
,0 as [ISSELECTED]
,' ' as [ITEMBOMID]
,[PSPMRN] as [ITEMID]
,' ' as [ITEMPBAID]
,' ' as [ITEMROUTEID]
,CASE WHEN [PSSEQ] > 0 THEN [PSSEQ] ELSE 1 END as [LINENUM]
,0 as [OPRNUM]
,' ' as [PDSBASEVALUE]
,0 as [PDSCWQTY]
,' ' as [PDSINGREDIENTTYPE]
,' ' as [PDSINHERITCOPRODUCTBATCHATTRIB]
,' ' as [PDSINHERITCOPRODUCTSHELFLIFE]
,' ' as [PDSINHERITENDITEMBATCHATTRIB]
,' ' as [PDSINHERITENDITEMSHELFLIFE]
,0 as [PMFFORMULAPCT]
,' ' as [PMFPCTENABLE]
,' ' as [PMFPLANGROUPID]
,' ' as [PMFPLANGROUPPRIORITY]
,' ' as [PMFSCALABLE]
,' ' as [POSITION]
,'FINISH' as [PRODFLUSHINGPRINCIP]
,' ' as [PROJSETSUBPRODTOCONSUMED]
,' ' as [ROUNDUP]
,0 as [ROUNDUPQTY]
,0 as [ROWID]
,0 as [SCRAPCONST]
,0 as [SCRAPVAR]
,
case
when PSEXYR = 99 then 21
when PSEXCT = 1 THEN 20
else 19
end
+ case
when PSEXYR = 0 THEN 01
when PSEXYR < 10 and PSEFYR <> 99 then '0' + Cast(PSEXYR as numeric (1))
when pSEXYR = 99 THEN 99
else Cast(PSEFYR as numeric (2))
END
+ '-'
+ case
when LEN(PSEXMO) = 1 THEN '0' + Cast(PSEXMO as numeric (1))
when len(PSEXMO) = 2 AND PSEXMO <> 99 then cast(PSEXMO as numeric (2))
when PSEXMO = 99 then 12
else 01
END
+ '-'
+ case
when len(PSEXDA) = 1 then 0 + Cast(PSEXDA as numeric (1))
when len(PSEXDA) = 2 and PSEXDA <> 99 then Cast(PSEXDA as numeric (2))
when PSEXDA = 99 then 31
else 01
end AS [TODATE]
,0 as [TRANSFERSTATUS]
,' ' as [UNITID]
,' ' as [VENDID]
,0 as [WIDTH]
,' ' as [WMSLOCATIONID]
,' ' as [WMSPALLETID]
,' ' as [WRKCTRCONSUMPTION]
,1 as [RECVERSION]
,1 as [PARTITION]
,1 as [RECID]
FROM OPENQUERY
(KBM400MFG,
' SELECT
[PSPMRN]
,[IMDSC]
,[PSQTY]
,[PSEFCT]
,[IMPHAN]
,[PSEFDA]
,[PSEFMO]
,[PSEFYR]
,[PSSEQ]
,[PSEXDA]
,[PSEXMO]
,[PSEXYR]
,PSCMRN
,PSEXCT
,imco
FROM [VD_GPL].[VKITMSTR ] B
INNER JOIN [S1022466].[KBM400MFG].[FKPSTRUC] C on B.IMPN = C.PSPMRN and B.IMCO = C.PSCO
'
) AS A
where [IMCO] in(01,03,05,07)
order by [PSPMRN]
, CASE WHEN PSEXCT + PSEXDA + PSEXMO + PSEXYR = 0 THEN CAST('19000101' AS datetime)
WHEN PSEXDA = 99 AND PSEXMO = 99 AND PSEXYR = 99 THEN CAST('21991231' AS datetime)
ELSE DATEADD(DAY, (PSEXDA - 1), DATEADD(MONTH, (PSEXMO - 1), DATEADD(YEAR, (PSEXCT * 100) + PSEXYR, 0)))
END
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
How to pass 2 IN parameters to a stored procedure using SQL Server 2008? | 6 | 29 | |
How to get time difference in minutes and seconds only between 2 dates | 2 | 20 | |
Getting max record but maybe not use Group BY | 2 | 16 | |
SQL - SP needs a little help | 9 | 21 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!