制造业中流程卡的生成_sqlserver

流程卡的生成

流程卡在制造业中是一个被技术多年采用的一种管理与描述生产技术过程的一种表现形式，通过流程卡，可以很明了地体现了技术的过程，在MRPIIk中，可以通过多层BOM表提取通过处理后，由数据库自动生成流程卡，以下为流程卡的生成的数据库过程

CREATE  PROCEDURE  Pro_Card

http://www.gaodaima.com/35297.html制造业中流程卡的生成_sqlserver

AS

SELECT PARENT,MAX(PARNT_DESC) AS PARNT_DESC,

MAX(CASE COMPONENT WHEN ‘WC[R]101’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]101’,

MAX(CASE COMPONENT WHEN ‘WC[R]102’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]102’,

MAX(CASE COMPONENT WHEN ‘WC[R]103’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]103’,

MAX(CASE COMPONENT WHEN ‘WC[R]104’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]104’,

MAX(CASE COMPONENT WHEN ‘WC[R]105’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]105’,

MAX(CASE COMPONENT WHEN ‘WC[R]106’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]106’,

MAX(CASE COMPONENT WHEN ‘WC[R]107’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]107’,

MAX(CASE COMPONENT WHEN ‘WC[R]108’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]108’,

MAX(CASE COMPONENT WHEN ‘WC[R]109’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]109’,

MAX(CASE COMPONENT WHEN ‘WC[R]10B’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]10B’,

MAX(CASE COMPONENT WHEN ‘WC[R]10C’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]10C’,

MAX(CASE COMPONENT WHEN ‘WC[R]10D’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]10D’

MAX(CASE COMPONENT WHEN ‘WC[R]10E’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]10E’,

MAX(CASE COMPONENT WHEN ‘WC[R]201’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]201’,

MAX(CASE COMPONENT WHEN’WC[R]202′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]202’,

MAX(CASE COMPONENT WHEN’WC[R]203′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]203’,

MAX(CASE COMPONENT WHEN’WC[R]204′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]204’,

MAX(CASE COMPONENT WHEN’WC[R]205′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]205’,

MAX(CASE COMPONENT WHEN’WC[R]206′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]206’,

MAX(CASE COMPONENT WHEN’WC[R]207′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]207’,

MAX(CASE COMPONENT WHEN’WC[R]208′ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]208’,

MAX(CASE COMPONENT WHEN ‘WC[R]301’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]301’,

MAX(CASE COMPONENT WHEN ‘WC[R]302’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]302’,

MAX(CASE COMPONENT WHEN ‘WC[R]303’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]303’,

MAX(CASE COMPONENT WHEN ‘WC[R]304’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]304’,

MAX(CASE COMPONENT WHEN ‘WC[R]305’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]305’,

MAX(CASE COMPONENT WHEN ‘WC[R]306’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]306’,

MAX(CASE COMPONENT WHEN ‘WC[R]307’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]307’,

MAX(CASE COMPONENT WHEN ‘WC[R]308’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]308’,

MAX(CASE COMPONENT WHEN ‘WC[R]309’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]309’,

MAX(CASE COMPONENT WHEN ‘WC[R]501’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]501’,

MAX(CASE COMPONENT WHEN ‘WC[R]601’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]601’,

MAX(CASE COMPONENT WHEN ‘WC[R]701’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]701’,

MAX(CASE COMPONENT WHEN ‘WC[R]801’ THEN COMP_DESC ELSE NULL END) AS ‘WC[R]801’

FROM M_BOM

GROUP BY PARENT

GO

建立存储过程，产生数据交叉表，这是一个基础工作。

建立如下的DTS包，生成表P_Card

图一

在表P_Card的基础上生成视图也就是我们的最终要看到了结果Pr_Card

Pr_Card的代码如下:

CREATE  VIEW Pr_Card

AS

SELECT PARENT,PARNT_DESC,

LTRIM(ISNULL([WC[R]]101],space(1))+space(1))+LTRIM(ISNULL([WC[R]]102],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]103],space(1))+space(1))+LTRIM(ISNULL([WC[R]]104],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]105],space(1))+space(1))+LTRIM(ISNULL([WC[R]]106],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]107],space(1))+space(1))+LTRIM(ISNULL([WC[R]]108],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]109],space(1))+space(1))+LTRIM(ISNULL([WC[R]]10B],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]10C],space(1))+space(1))+LTRIM(ISNULL([WC[R]]10D],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]10E],space(1))+space(1))+LTRIM(ISNULL([WC[R]]201],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]202],space(1))+space(1))+LTRIM(ISNULL([WC[R]]203],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]204],space(1))+space(1))+LTRIM(ISNULL([WC[R]]205],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]206],space(1))+space(1))+LTRIM(ISNULL([WC[R]]207],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]208],space(1))+space(1))+LTRIM(ISNULL([WC[R]]301],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]302],space(1))+space(1))+LTRIM(ISNULL([WC[R]]303],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]304],space(1))+space(1))+LTRIM(ISNULL([WC[R]]305],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]306],space(1))+space(1))+LTRIM(ISNULL([WC[R]]307],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]308],space(1))+space(1))+LTRIM(ISNULL([WC[R]]309],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]501],space(1))+space(1))+LTRIM(ISNULL([WC[R]]601],space(1))+space(1))+

LTRIM(ISNULL([WC[R]]701),space(1))+space(1))+ISNULL([WC[R]]801),space(1)) AS 流程卡

FROM dbo.P_CARD

到些流程卡的设计就结束了，最终结果的形式如下：

其中，是利用空格来体现不同工位的表述。

本人为本科应界毕业生，愿从事企业信息化工作，希望有兴趣的公司或企业可以与我联系，我在静候

欢迎大家阅读《制造业中流程卡的生成_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)