22,210
社区成员
发帖
与我相关
我的任务
分享
SELECT ASSEMBLY_ITEM_ID, (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = A.ASSEMBLY_ITEM_ID) AS SEGMENT1,
COMPONENT_ITEM_ID, (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = A.COMPONENT_ITEM_ID) AS SEGMENT1_1,
QUANTITY_PER_ASSEMBLY, (SELECT REFERENCE_PRICE FROM INVENTORY_REF_PRICE
WHERE INVENTORY_ITEM_ID = A.QUANTITY_PER_ASSEMBLY) AS REFERENCE_PRICE
FROM BOMBV_BOM_COMPONENTS AS A;
GO
DECLARE @T1 TABLE(
INVENTORY_ITEM_ID INT,
SEGMENT1 VARCHAR(50)
)
INSERT INTO @T1
SELECT 180, '373257-00000' UNION ALL
SELECT 280, '373258-10000' UNION ALL
SELECT 178, '373257-10000' UNION ALL
SELECT 176, '373257-20000' UNION ALL
SELECT 192, '373257-30000' UNION ALL
SELECT 146, '00TR00-00016' UNION ALL
SELECT 198, '00TR00-00106' UNION ALL
SELECT 142, '01QZ00-00168' UNION ALL
SELECT 148, '01RS03-01020' UNION ALL
SELECT 144, '03CN10-00018' UNION ALL
SELECT 150, '03PB01-00443' UNION ALL
SELECT 152, '08PG14-00021' UNION ALL
SELECT 154, '08PG14-00028' UNION ALL
SELECT 156, 'MS02-0006-01' UNION ALL
SELECT 58, 'MS02-0002-01'
DECLARE @T2 TABLE(
ASSEMBLY_ITEM_ID INT,
COMPONENT_ITEM_ID INT,
QUANTITY_PER_ASSEMBLY NUMERIC(19,6)
)
INSERT INTO @T2
SELECT 180,178,1 UNION ALL
SELECT 178,146,2 UNION ALL
SELECT 178,198,2 UNION ALL
SELECT 178,148,1 UNION ALL
SELECT 178,150,1 UNION ALL
SELECT 180,176,1 UNION ALL
SELECT 176,142,1 UNION ALL
SELECT 176,144,1 UNION ALL
SELECT 180,192,1 UNION ALL
SELECT 192,152,1 UNION ALL
SELECT 192,154,1 UNION ALL
SELECT 192,58,0.001 UNION ALL
SELECT 192,156,0.00075 UNION ALL
SELECT 280,178,1 UNION ALL
SELECT 280,144,1
DECLARE @T3 TABLE(
INVENTORY_ITEM_ID INT,
REFERENCE_PRICE NUMERIC(19,6)
)
INSERT INTO @T3
SELECT 146, 0.3 UNION ALL
SELECT 198, 0.5 UNION ALL
SELECT 144, 3.2 UNION ALL
SELECT 58, 5 UNION ALL
SELECT 156, 4
SELECT
CASE WHEN EXISTS(SELECT 1 FROM @T2 T5 WHERE T5.COMPONENT_ITEM_ID=T2.ASSEMBLY_ITEM_ID)
THEN 2 ELSE 1 END 'Level'
,T2.ASSEMBLY_ITEM_ID,T1.SEGMENT1,T2.COMPONENT_ITEM_ID
,T3.SEGMENT1,T2.QUANTITY_PER_ASSEMBLY,T4.REFERENCE_PRICE
FROM @T2 T2
INNER JOIN @T1 T1 ON T1.INVENTORY_ITEM_ID=T2.ASSEMBLY_ITEM_ID
INNER JOIN @T1 T3 ON T3.INVENTORY_ITEM_ID=T2.COMPONENT_ITEM_ID
LEFT JOIN @T3 T4 ON T4.INVENTORY_ITEM_ID=T2.COMPONENT_ITEM_ID
/*
2 192 373257-30000 58 MS02-0002-01 0.001000 5.000000
2 176 373257-20000 142 01QZ00-00168 1.000000 NULL
2 176 373257-20000 144 03CN10-00018 1.000000 3.200000
1 280 373258-10000 144 03CN10-00018 1.000000 3.200000
2 178 373257-10000 146 00TR00-00016 2.000000 0.300000
2 178 373257-10000 148 01RS03-01020 1.000000 NULL
2 178 373257-10000 150 03PB01-00443 1.000000 NULL
2 192 373257-30000 152 08PG14-00021 1.000000 NULL
2 192 373257-30000 154 08PG14-00028 1.000000 NULL
2 192 373257-30000 156 MS02-0006-01 0.000750 4.000000
1 180 373257-00000 176 373257-20000 1.000000 NULL
1 180 373257-00000 178 373257-10000 1.000000 NULL
1 280 373258-10000 178 373257-10000 1.000000 NULL
1 180 373257-00000 192 373257-30000 1.000000 NULL
2 178 373257-10000 198 00TR00-00106 2.000000 0.500000
*/