Hello,
Good morning.
Appreciate your valuable suggestion.
Each product must have to go through certain job stages before moving to stock inventory.
Each product hold unique job stage (asssume the job stages from A-Z) sequence.
For example product 1 must go through each job stage assigned to that product (A -> C -> D -> E -> G -> F -> B -> Y -> Z) like wise product 2 will also have its unique job stages (A -> B -> D -> C -> G -> F -> I -> Y -> Z) and so on...
Now i need to add additional Rework job (assume the job stages from RA - RZ) for each job stage.
Currently i am using SQL SERVER 2012.
JobStageRelation table was newely created for this requirment purpose. Please correct me if the table design is wrong.
Please refer to the below table script and sample data for your kind perusal and do the need full.
DROP TABLE JobStage;
CREATE TABLE JobStage(
JobStageId INTEGER NOT NULL PRIMARY KEY
,JobStageName NVARCHAR(50) NOT NULL
,StatusId INTEGER NOT NULL
);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (11,N'Casting',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (12,N'Curing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (13,N'Deburring',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (15,N'Electrical (HV)',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (16,N'X-Ray',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (17,N'Painting',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (20,N'Transfer to FG',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (21,N'Touch Up', 8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (22,N'Polishing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (23,N'Tapping',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (24,N'Visual Checking',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (25,N'Sizing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (26,N'Bending',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (27,N'End Control',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (28,N'Gas Leakage',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (29,N'External Service',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (30,N'Surface Roughness',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (31,N'Capacitance',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (33,N'Rework',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (34,N'Megger',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (35,N'Tensile',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (36,N'Flatness Checking',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (37,N'Resistance',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (38,N'Degreasing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (39,N'Torque',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (40,N'MECHANICAL',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (41,N'GROOVING',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (42,N'Copper Alignment',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (43,N'Finishing Visual',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (44,N'Hardness Test',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (46,N'Electrical (PD)',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (47,N'Perpendicularity Checking',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (48,N'Parallel Checking',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (49,N'Silver Painting',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (50,N'Drying',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (51,N'Recurring',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (52,N'Critical Point',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (53,N'Bonding Checking',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (54,N'Concentricity Checking',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (55,N'Voltage Indicator',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (57,N'Sivler Coating',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (58,N'Cycle Test',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (59,N'Plating',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (60,N'Go Gauge',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (61,N'No Go',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (62,N'External Sizing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (63,N'Fixing Multicontact',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (64,N'Tumbling',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (65,N'Jig Fixing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (66,N'Compression',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (67,N'Go No',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (68,N'Winding Coil',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (69,N'Remove',Wire);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (70,N'Assy CT',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (71,N'HV Test',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (72,N'Error Test',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (73,N'Finishing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (74,N'Electrical (HV)');
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (75,N'Sandblasting',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (76,N'Electrical (PD)',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (77,N'Copper Cleaning',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (78,N'Rework-X-Ray',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (79,N'Rework-Electrical',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (80,N'Rework-Curing',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (81,N'Rework-Deburring',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (83,N'Rework-Painting',8);
INSERT INTO JobStage(JobStageId,JobStageName,StatusId) VALUES (84,N'Rework-Polishing',8);
DROP TABLE JobStageRelation;
CREATE TABLE JobStageRelation(
JobStageId INTEGER NOT NULL PRIMARY KEY
,ParentJobStageId INTEGER NOT NULL
);
INSERT INTO JobStageRelation(JobStageId,ParentJobStageId) VALUES (78,16);
INSERT INTO JobStageRelation(JobStageId,ParentJobStageId) VALUES (79,15);
INSERT INTO JobStageRelation(JobStageId,ParentJobStageId) VALUES (80,12);
INSERT INTO JobStageRelation(JobStageId,ParentJobStageId) VALUES (81,13);
INSERT INTO JobStageRelation(JobStageId,ParentJobStageId) VALUES (83,17);
INSERT INTO JobStageRelation(JobStageId,ParentJobStageId) VALUES (84,22);
DROP TABLE Output;
CREATE TABLE Output(
Uniid INT NOT NULL PRIMARY KEY
,StockCode INTEGER NOT NULL
,JobStageId INTEGER NOT NULL
);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (1,1254,11);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (2,1254,12);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (3,1254,80);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (4,1254,21);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (5,1254,13);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (6,1254,81);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (7,1254,22);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (8,1254,43);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (9,1254,16);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (10,1254,78);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (11,1254,46);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (12,1254,27);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (13,1254,20);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (14,1657,11);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (15,1657,12);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (16,1657,80);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (17,1657,21);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (18,1657,13);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (19,1657,81);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (20,1657,22);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (21,1657,23);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (22,1657,17);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (23,1657,83);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (24,1657,43);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (25,1657,16);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (26,1657,78);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (27,1657,15);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (28,1657,79);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (29,1657,34);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (30,1657,31);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (31,1657,24);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (32,1657,27);
INSERT INTO Output(Uniid,StockCode,JobStageId) VALUES (33,1657,20);
DROP TABLE StockItemRouting;
CREATE TABLE StockItemRouting(
Uniid INT NOT NULL PRIMARY KEY
,StockCode INTEGER NOT NULL
,JobStageId INTEGER NOT NULL
,TestProcedureId INTEGER NOT NULL
,WorkCenterId INTEGER NOT NULL
,SamplingId INTEGER NOT NULL
,AQLId INTEGER NOT NULL
);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221719,1254,11,NULL,6,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221720,1254,12,NULL,7,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221721,1254,21,NULL,12,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221722,1254,13,NULL,8,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221723,1254,22,NULL,13,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221724,1254,43,NULL,10,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221725,1254,16,NULL,16,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221726,1254,46,14,17,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221728,1254,27,NULL,21,4,7);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (221729,1254,20,NULL,10,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198323,1657,11,NULL,6,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198324,1657,12,NULL,7,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198325,1657,21,NULL,12,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198326,1657,13,NULL,8,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198327,1657,22,NULL,13,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198328,1657,23,NULL,15,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198329,1657,17,NULL,14,NULL,NULL);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198330,1657,43,NULL,10,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198331,1657,16,NULL,16,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198332,1657,15,14,17,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198333,1657,34,NULL,18,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198334,1657,31,NULL,18,1,14);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198335,1657,24,NULL,18,4,7);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198336,1657,27,NULL,21,4,7);
INSERT INTO StockItemRouting(Uniid,StockCode,JobStageId,TestProcedureId,WorkCenterId,SamplingId,AQLId) VALUES (198337,1657,20,NULL,10,NULL,NULL);
Expected Output:
Uniid StockCode JobStageName
1 1254 Casting
2 1254 Curing
3 1254 Rework Curing
4 1254 Touch Up
5 1254 Deburring
6 1254 Rework Deburring
7 1254 Polishing
8 1254 Finishing Visual Checking
9 1254 X- Ray
10 1254 Rework X-Ray
11 1254 Electrical (PD)
12 1254 End Control
13 1254 Transfer to FG
14 1657 Casting
15 1657 Curing
16 1657 Rework Curing
17 1657 Touch Up
18 1657 Deburring
19 1657 Rework Deburring
20 1657 Polishing
21 1657 Rework Polishing
22 1657 Tapping
23 1657 Painting
24 1657 Rework Painting
25 1657 Finishing Visual Checking
26 1657 X- Ray
27 1657 Rework X-Ray
28 1657 Electrical (HV)
29 1657 Rework Electrical (HV)
30 1657 Megger
30 1657 Capacitance
31 1657 Visual Checking End Control
32 1657 End Control
33 1657 Transfer to FG
Thank you.