RE: There is a data source column with no name.Each data source column must have a name.

rashid nizam

SSCommitted

Points: 1605

June 16, 2008 at 3:48 am

#829096

yes SET NOCOUNT is ON in the beginnig of SP,

let me paste the code here.........

/****** Object: StoredProcedure [dbo].[usp_Inv_Lot_Ship_History_2201s] Script Date: 06/16/2008 02:40:37 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[usp_Inv_Lot_Ship_History_2201s]

** This proc replaces the view Inv_Lot_Ship_History_2201s.

** Version History

** 1 04/20/2007 kchong Initial Version

** 2 05/27/2008 bsmet Add time restriction

if (datepart(hh, getdate()) not between 12 and 24)

begin

select 'Error - Running outside allowed time window'

select 1/0

return

end

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

CREATE TABLE #PSXLATITEM (

FIELDNAME varchar(18),

FIELDVALUE varchar(4),

EFF_STATUS varchar(1),

XLATLONGNAME varchar(30)

)

INSERT INTO #PSXLATITEM

SELECT

FIELDNAME,

FIELDVALUE,

EFF_STATUS,

XLATLONGNAME

FROM SPOT_RAW..PSXLATITEM

CREATE INDEX IDX_PSXLATITEM ON #PSXLATITEM (FIELDNAME, FIELDVALUE, EFF_STATUS, XLATLONGNAME)

CREATE TABLE #PS_EN_BOM_COMPS (

INV_ITEM_ID varchar(18),

COMPONENT_ID varchar(18),

DATE_IN_EFFECT datetime,

DATE_OBSOLETE datetime)

INSERT INTO #PS_EN_BOM_COMPS

SELECT

INV_ITEM_ID,

COMPONENT_ID,

CONVERT(VARCHAR(10),DATE_IN_EFFECT,101),

CONVERT(VARCHAR(10),DATE_OBSOLETE,101)

FROM

SPOT_RAW..PS_EN_BOM_COMPS

CREATE INDEX IDX_PS_EN_BOM_COMPS ON #PS_EN_BOM_COMPS (INV_ITEM_ID, DATE_IN_EFFECT, DATE_OBSOLETE)

CREATE TABLE #PS_INV_ITEMS

(

SETID varchar(5),

INV_ITEM_ID varchar(18),

EFFDT datetime,

INV_ITEM_TYPE varchar(5)

)

INSERT INTO #PS_INV_ITEMS

SELECT

SETID,

INV_ITEM_ID,

EFFDT,

INV_ITEM_TYPE

FROM SPOT_RAW..PS_INV_ITEMS F

WHERE

F.SETID='SHARE' AND (F.INV_ITEM_TYPE='CHIP' OR F.INV_ITEM_TYPE='TRNKY')

CREATE INDEX IDX_PS_INV_ITEMS ON #PS_INV_ITEMS (INV_ITEM_ID)

CREATE INDEX IDX_PS_INV_ITEMS1 ON #PS_INV_ITEMS (SETID,INV_ITEM_ID,EFFDT)

SELECT

A.BUSINESS_UNIT,

A.DEMAND_SOURCE,

A.SOURCE_BUS_UNIT,

A.ORDER_NO,

A.ORDER_INT_LINE_NO,

A.SCHED_LINE_NO,

A.INV_ITEM_ID,

A.DEMAND_LINE_NO,

A.INV_LOT_ID,

A.DT_TIMESTAMP,

SH.SHIP_CUST_NAME1,

SH.CUST_NAME,

SH.PARENT_PROD_ID,

SH.PRODUCT_ID

INTO #A_SH

FROM SPOT_RAW..PS_DEMAND_PHYS_INV A with (nolock)

INNER JOIN

SPOT_RAW..PS_SHIP_INF_INV SH with (nolock)

ON A.BUSINESS_UNIT=SH.BUSINESS_UNIT

AND A.DEMAND_SOURCE=SH.DEMAND_SOURCE

AND A.SOURCE_BUS_UNIT=SH.SOURCE_BUS_UNIT

AND A.ORDER_NO=SH.ORDER_NO

AND A.ORDER_INT_LINE_NO=SH.ORDER_INT_LINE_NO

AND A.SCHED_LINE_NO=SH.SCHED_LINE_NO

AND A.INV_ITEM_ID=SH.INV_ITEM_ID

AND A.DEMAND_LINE_NO=SH.DEMAND_LINE_NO

AND SH.SHIPPED_FLAG='Y'

where A.DEMAND_SOURCE='OM'

CREATE INDEX IDX_A_SH ON #A_SH (INV_ITEM_ID, INV_LOT_ID)

DECLARE @DATE VARCHAR(10)

SET @DATE = CONVERT(VARCHAR(10),GETDATE(),101)

SELECT

A.INV_ITEM_ID AS "BRCM Item",

B.MODEL_NBR AS "Marketing Part",

RIGHT(A.INV_LOT_ID,9) AS "Lot ID",

LEFT(A.INV_LOT_ID,6) AS "Date Code",

SUBSTRING(A.INV_LOT_ID,3,4) AS "Datecode YYWW",

A.ORDER_NO AS "Order No",

A.ORDER_INT_LINE_NO AS "Order Line",

A.SHIP_CUST_NAME1 AS "ShipTo Name",

A.CUST_NAME AS "SoldTo Name",

"End Cust Name" =

ISNULL((SELECT max(CU.NAME1)

FROM

SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

SPOT_RAW..PS_SET_CNTRL_REC SI,

SPOT_RAW..PS_CUSTOMER CU

WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

AND A.ORDER_NO = BOL.ORDER_NO

AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

AND SI.REC_GROUP_ID='FS_23'

AND SI.RECNAME='INV_CUST_ADR_VW'

AND SI.SETID=CU.SETID

AND BOL.BRC_CUST_ID_LVL1=CU.CUST_ID),'N/A'),

"End-End Cust Name" =

ISNULL((SELECT max(CU.NAME1)

FROM

SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

SPOT_RAW..PS_SET_CNTRL_REC SI,

SPOT_RAW..PS_CUSTOMER CU

WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

AND A.ORDER_NO = BOL.ORDER_NO

AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

AND SI.REC_GROUP_ID='FS_23'

AND SI.RECNAME='INV_CUST_ADR_VW'

AND SI.SETID=CU.SETID

AND BOL.BRC_CUST_ID_LVL2=CU.CUST_ID),'N/A'),

"Customer Product ID" =

ISNULL((SELECT max(KIT.CUSTOMER_ITEM_NBR)

FROM

SPOT_RAW..PS_BRC_ORD_PRODKIT KIT

WHERE A.SOURCE_BUS_UNIT = KIT.BUSINESS_UNIT

AND A.ORDER_NO = KIT.ORDER_NO

AND A.ORDER_INT_LINE_NO = KIT.ORDER_INT_LINE_NO

AND A.PARENT_PROD_ID=KIT.PRODUCT_ID

AND A.PRODUCT_ID=KIT.PROD_COMPONENT_ID),OL.CUSTOMER_ITEM_NBR),

C.ITEM_FIELD_C4 as "P-Code",

"Die Item"=

ISNULL((SELECT max(BOM.COMPONENT_ID)

FROM #PS_EN_BOM_COMPS BOM

WHERE

A.INV_ITEM_ID=BOM.INV_ITEM_ID

AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE

AND 1=(SELECT COUNT(*) FROM #PS_EN_BOM_COMPS BOM2

WHERE A.INV_ITEM_ID=BOM2.INV_ITEM_ID

AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE )),

J.BRC_PART_DIE),

C.ITEM_FIELD_C10_C AS "Core Part",

D.BRC_FAB_ID AS "Fab Lot ID",

D.COUNTRY_ORIGIN AS "COO",

G.BRC_A_VEND AS "Assy Vendor ID",

H.VNDR_NAME_SHRT_USR AS "Assy Vendor",

G.BRC_F_VEND AS "Fab Vendor ID",

I.VNDR_NAME_SHRT_USR AS "Fab Vendor",

XPROCESS.XLATLONGNAME as "Fab Process",

XPHASE.XLATLONGNAME as "Item Phase",

XPKGTYP.XLATLONGNAME as "Package Type",

XPKGSIZ.XLATLONGNAME as "Package Size",

J.BRC_PKG_PIN_COUNT as "Pin Count",

F.INV_ITEM_TYPE as "Item Type",

A.DT_TIMESTAMP

FROM

#A_SH A

LEFT OUTER JOIN

SPOT_RAW..PS_BRC_LOT_CNTL D

ON A.INV_ITEM_ID=D.INV_ITEM_ID

AND A.INV_LOT_ID=D.INV_LOT_ID

INNER JOIN

#PS_INV_ITEMS F

ON A.INV_ITEM_ID=F.INV_ITEM_ID

INNER JOIN

SPOT_RAW..PS_PROD_ITEM B

ON A.INV_ITEM_ID=B.INV_ITEM_ID

AND B.SETID='SHARE'

INNER JOIN

SPOT_RAW..PS_BRC_ITEM_TBL J

ON B.SETID=J.SETID

AND B.INV_ITEM_ID=J.INV_ITEM_ID

INNER JOIN

SPOT_RAW..PS_MASTER_ITEM_TBL C

ON A.INV_ITEM_ID=C.INV_ITEM_ID AND C.SETID='SHARE'

LEFT OUTER JOIN

#PSXLATITEM AS XPHASE

ON C.ITEM_FIELD_C1_C=XPHASE.FIELDVALUE

AND XPHASE.FIELDNAME='ITEM_FIELD_C1_C'

AND XPHASE.EFF_STATUS='A'

INNER JOIN

SPOT_RAW..PS_BRC_DC_PREFX G

ON SUBSTRING(A.INV_LOT_ID,1,2)= G.BRC_DC_PREFIX

AND G.SETID='SHARE'

LEFT OUTER JOIN

SPOT_RAW..PS_VENDOR H

ON G.BRC_A_VEND_SETID=H.SETID

AND G.BRC_A_VEND=H.VENDOR_ID

LEFT OUTER JOIN

SPOT_RAW..PS_VENDOR I

ON G.BRC_F_VEND_SETID=I.SETID --

AND G.BRC_F_VEND=I.VENDOR_ID

LEFT OUTER JOIN

#PSXLATITEM AS XPKGTYP

ON J.BRC_PACKAGE=XPKGTYP.FIELDVALUE

AND XPKGTYP.FIELDNAME='BRC_PACKAGE'

AND XPKGTYP.EFF_STATUS='A'

LEFT OUTER JOIN

#PSXLATITEM AS XPROCESS

ON J.BRC_PROCESS=XPROCESS.FIELDVALUE

AND XPROCESS.FIELDNAME='BRC_PROCESS'

AND XPROCESS.EFF_STATUS='A'

LEFT OUTER JOIN

#PSXLATITEM AS XPKGSIZ

ON J.BRC_PKG_SIZE=XPKGSIZ.FIELDVALUE

AND XPKGSIZ.FIELDNAME='BRC_PKG_SIZE'

AND XPKGSIZ.EFF_STATUS='A'

INNER JOIN

SPOT_RAW..PS_ORD_LINE OL

ON A.SOURCE_BUS_UNIT=OL.BUSINESS_UNIT

AND A.ORDER_NO=OL.ORDER_NO

AND A.ORDER_INT_LINE_NO=OL.ORDER_INT_LINE_NO

WHERE

F.EFFDT=

(SELECT MAX(INV.EFFDT) FROM #PS_INV_ITEMS INV

WHERE F.SETID = INV.SETID

AND F.INV_ITEM_ID=INV.INV_ITEM_ID

AND CONVERT(VARCHAR(10),INV.EFFDT,101) <= @DATE) --SUBSTRING(CONVERT(CHAR,@DATE,121), 1, 10))

UNION ALL

SELECT

A.INV_ITEM_ID AS "BRCM Item",

B.MODEL_NBR AS "Marketing Part",

A.INV_LOT_ID AS "Lot ID",

LEFT(A.INV_LOT_ID,6) AS "Date Code",

'N/A' AS "Datecode YYWW",

A.ORDER_NO AS "Order No",

A.ORDER_INT_LINE_NO AS "Order Line",

A.SHIP_CUST_NAME1 AS "ShipTo Name",

A.CUST_NAME AS "SoldTo Name",

"End Cust Name" =

ISNULL((SELECT max(CU.NAME1)

FROM

SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

SPOT_RAW..PS_SET_CNTRL_REC SI,

SPOT_RAW..PS_CUSTOMER CU

WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

AND A.ORDER_NO = BOL.ORDER_NO

AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

AND SI.REC_GROUP_ID='FS_23'

AND SI.RECNAME='INV_CUST_ADR_VW'

AND SI.SETID=CU.SETID

AND BOL.BRC_CUST_ID_LVL1=CU.CUST_ID),'N/A'),

"End-End Cust Name" =

ISNULL((SELECT max(CU.NAME1)

FROM

SPOT_RAW..PS_BRC_ORD_LN_CUST BOL,

SPOT_RAW..PS_SET_CNTRL_REC SI,

SPOT_RAW..PS_CUSTOMER CU

WHERE A.SOURCE_BUS_UNIT = BOL.BUSINESS_UNIT

AND A.ORDER_NO = BOL.ORDER_NO

AND A.ORDER_INT_LINE_NO = BOL.ORDER_INT_LINE_NO

AND BOL.BUSINESS_UNIT=SI.SETCNTRLVALUE

AND SI.REC_GROUP_ID='FS_23'

AND SI.RECNAME='INV_CUST_ADR_VW'

AND SI.SETID=CU.SETID

AND BOL.BRC_CUST_ID_LVL2=CU.CUST_ID),'N/A'),

"Customer Product ID" =

ISNULL((SELECT max(KIT.CUSTOMER_ITEM_NBR)

FROM

SPOT_RAW..PS_BRC_ORD_PRODKIT KIT

WHERE A.SOURCE_BUS_UNIT = KIT.BUSINESS_UNIT

AND A.ORDER_NO = KIT.ORDER_NO

AND A.ORDER_INT_LINE_NO = KIT.ORDER_INT_LINE_NO

AND A.PARENT_PROD_ID=KIT.PRODUCT_ID

AND A.PRODUCT_ID=KIT.PROD_COMPONENT_ID),OL.CUSTOMER_ITEM_NBR),

C.ITEM_FIELD_C4 as "P-Code",

"Die Item"=

ISNULL((SELECT max(BOM.COMPONENT_ID)

FROM

#PS_EN_BOM_COMPS BOM

WHERE

A.INV_ITEM_ID=BOM.INV_ITEM_ID

AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE

AND 1=(SELECT COUNT(*) FROM

#PS_EN_BOM_COMPS BOM2

WHERE A.INV_ITEM_ID=BOM2.INV_ITEM_ID

AND CONVERT(VARCHAR(10),BOM.DATE_IN_EFFECT,101) <= @DATE

AND CONVERT(VARCHAR(10),BOM.DATE_OBSOLETE,101) > @DATE )),

J.BRC_PART_DIE),

C.ITEM_FIELD_C10_C AS "Core Part",

D.BRC_FAB_ID AS "Fab Lot ID",

D.COUNTRY_ORIGIN AS "COO",

'N/A' AS "Assy Vendor ID", 'N/A' AS "Assy Vendor",

'N/A' AS "Fab Vendor ID", 'N/A' AS "Fab Vendor",

XPROCESS.XLATLONGNAME as "Fab Process",

XPHASE.XLATLONGNAME as "Item Phase",

XPKGTYP.XLATLONGNAME as "Package Type",

XPKGSIZ.XLATLONGNAME as "Package Size",

J.BRC_PKG_PIN_COUNT as "Pin Count",

F.INV_ITEM_TYPE as "Item Type",

A.DT_TIMESTAMP

FROM

#A_SH A

LEFT OUTER JOIN

SPOT_RAW..PS_BRC_LOT_CNTL D

ON A.INV_ITEM_ID=D.INV_ITEM_ID

AND A.INV_LOT_ID=D.INV_LOT_ID

INNER JOIN

#PS_INV_ITEMS F

ON A.INV_ITEM_ID=F.INV_ITEM_ID

INNER JOIN

SPOT_RAW..PS_PROD_ITEM B

ON A.INV_ITEM_ID=B.INV_ITEM_ID

AND B.SETID='SHARE'

INNER JOIN

SPOT_RAW..PS_BRC_ITEM_TBL J

ON B.SETID=J.SETID

AND B.INV_ITEM_ID=J.INV_ITEM_ID

INNER JOIN

SPOT_RAW..PS_MASTER_ITEM_TBL C

ON A.INV_ITEM_ID=C.INV_ITEM_ID AND C.SETID='SHARE'

LEFT OUTER JOIN

#PSXLATITEM AS XPHASE

ON C.ITEM_FIELD_C1_C=XPHASE.FIELDVALUE

AND XPHASE.FIELDNAME='ITEM_FIELD_C1_C'

AND XPHASE.EFF_STATUS='A'

LEFT OUTER JOIN

#PSXLATITEM AS XPKGTYP

ON J.BRC_PACKAGE=XPKGTYP.FIELDVALUE

AND XPKGTYP.FIELDNAME='BRC_PACKAGE'

AND XPKGTYP.EFF_STATUS='A'

LEFT OUTER JOIN

#PSXLATITEM AS XPROCESS

ON J.BRC_PROCESS=XPROCESS.FIELDVALUE

AND XPROCESS.FIELDNAME='BRC_PROCESS'

AND XPROCESS.EFF_STATUS='A'

LEFT OUTER JOIN

#PSXLATITEM AS XPKGSIZ

ON J.BRC_PKG_SIZE=XPKGSIZ.FIELDVALUE

AND XPKGSIZ.FIELDNAME='BRC_PKG_SIZE'

AND XPKGSIZ.EFF_STATUS='A'

INNER JOIN

SPOT_RAW..PS_ORD_LINE OL

ON A.SOURCE_BUS_UNIT=OL.BUSINESS_UNIT

AND A.ORDER_NO=OL.ORDER_NO

AND A.ORDER_INT_LINE_NO=OL.ORDER_INT_LINE_NO

WHERE

F.EFFDT=

(SELECT MAX(INV.EFFDT) FROM #PS_INV_ITEMS INV

WHERE F.SETID = INV.SETID

AND F.INV_ITEM_ID=INV.INV_ITEM_ID

AND CONVERT(VARCHAR(10),INV.EFFDT,101) <= @DATE) --SUBSTRING(CONVERT(CHAR,@DATE,121), 1, 10))

AND NOT EXISTS

(SELECT * FROM SPOT_RAW..PS_BRC_DC_PREFX DC

WHERE SUBSTRING(A.INV_LOT_ID,1,2)= DC.BRC_DC_PREFIX

AND DC.SETID='SHARE')

DROP TABLE #PSXLATITEM

DROP TABLE #PS_EN_BOM_COMPS

DROP TABLE #PS_INV_ITEMS

DROP TABLE #A_SH