查詢一.
SELECT distinct
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
--MMT.SUBINVENTORY_CODE ,
--MMT.TRANSACTION_UOM
sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
--sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
-- and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
--and bd.department_code in('&s_dept')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
結果一.
itemid item name qty
45843 T404072601 40726(3~5#)背坑貼紙 7500
45844 T404072602 40726(6~7#)背坑貼紙 11000
45845 T404072603 40726(8~PW#)背坑貼紙 36000
45846 T404072604 40726(AW~SW#)背坑貼紙 5000
45919 T40N176880 貼紙Shaft Sticker 3-7 3600
45920 T40N176890 貼紙Shaft Sticker 8 4560
45968 Q021000011 透明常溫膠帶120MM*60Y 81
46061 A4073000R06270A 40730 (N1763801) 15125
46357 A9078200RPT000A 90782 (N0952401) FO 9
46577 M01870C0143 美國烤漆 870C0143 8
46578 M01870C0144 美國烤漆 870C0144 8
查詢二
select MSIF.SEGMENT1 "item",
BIC.COMPONENT_QUANTITY "QTY",
BIC.COMPONENT_ITEM_ID "itemid"
from BOM_BILL_OF_MATERIALS BBOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_b MSIF,
(select msib.segment1 item,
mcb.SEGMENT1 model
from mtl_system_items_b msib,
mtl_system_items_tl msit,
MTL_ITEM_CATEGORIES mic,
MTL_CATEGORIES_B mcb
where msib.ORGANIZATION_ID = '83'
and msib.ORGANIZATION_ID = msit.ORGANIZATION_ID
and msit.LANGUAGE = 'ZHT'
and msib.INVENTORY_ITEM_ID = msit.INVENTORY_ITEM_ID
and mic.inventory_item_id = msib.INVENTORY_ITEM_ID
and mic.ORGANIZATION_ID = '88'
and mic.category_set_id = 1100000001
and mic.category_id = mcb.category_id) msim,
(select msib.segment1 item,
NVL(mdev.element_value,'') semicolo
from MTL_DESCR_ELEMENT_VALUES mdev,
mtl_system_items_b msib
where msib.ORGANIZATION_ID = '88'
and mdev.element_name = '分號'
and mdev.inventory_item_id = msib.INVENTORY_ITEM_ID) msis,
(select msib.segment1 item,
NVL(mdev.element_value,'') degree
from MTL_DESCR_ELEMENT_VALUES mdev,
mtl_system_items_b msib
where msib.ORGANIZATION_ID = '88'
and mdev.element_name ='度數'
and mdev.inventory_item_id = msib.INVENTORY_ITEM_ID) msid
-- and msib.segment1 = '&item_num') msid
where BBOM.BILL_SEQUENCE_ID=BIC.BILL_SEQUENCE_ID
and BBOM.ORGANIZATION_ID = '88'
and BIC.DISABLE_DATE IS NULL
-- and MSIF.SEGMENT1 ='AMW25MTCR09260A'
and MSIF.ORGANIZATION_ID = BBOM.ORGANIZATION_ID
and BBOM.ASSEMBLY_ITEM_ID=MSIF.INVENTORY_ITEM_ID
and msim.item=msif.segment1
-- and msim.model like '&p_model%'
and MSIF.SEGMENT1 like 'C%'
and msis.item(+)=msif.segment1
-- and nvl(msis.semicolo,' ') like '&p_semicolo%'
and msid.item(+)=msif.segment1
-- and nvl(msid.degree,' ') like '&p_degree%'
and msif.inventory_item_status_code='Active'
order by MSIF.SEGMENT1,ITEM_NUM
結果二.
item qty itemid
C1032000R03150GM1 1 42292
C1032000R03150GM1 1 42620
C1032000R03150GM1 1 42258
C1032000R03150GM1 1 42623
C1032000R03150GM1 1 42721
C1032000R03150GM1 0.04 43765
C1032000R03150GM1 0.01 34621
C1032000R03150GM1 0.0008 43143
C1032000R03150GM1 0.00025 9652
C1032000R03150GM1 0.0002 42737
C1032000R03150GM1 0.0008 23258
C1032000R03150GM1 0.0002 31088
C1032000R03150GM1 0.0002 31089查詢三。
select distinct
MMT.TRANSACTION_QUANTITY "QTY",
MSIF.SEGMENT1 "item",
from
WIP_ENTITIES WE,
WIP_MOVE_TRANSACTIONS WMT,
WIP_DISCRETE_JOBS WDJ,
(SELECT MSIF.INVENTORY_ITEM_ID,MICV.CATEGORY_CONCAT_SEGS
FROM MTL_ITEM_CATEGORIES_V MICV,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MICV.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID = 88 AND MSIF.ORGANIZATION_ID = 88
AND MICV.CATEGORY_SET_ID = '1100000002') msis,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_FVL MSIF
where
TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date'
-- (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD') BETWEEN :P_FROM_DATE AND :P_TO_DATE)
AND MMT.SUBINVENTORY_CODE='WEF600'
-- AND (MMT.SUBINVENTORY_CODE BETWEEN ':P_FROM_INV' AND ':P_TO_INV')
and msis.inventory_item_id=MSIF.INVENTORY_ITEM_ID
AND (MMT.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MSIF.ORGANIZATION_ID = 88)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTT.TRANSACTION_TYPE_ID not in (24) -- Sales Order Pick,Standard cost update
AND MTT.TRANSACTION_TYPE_ID not in (24) -- Sales Order Pick,Standard cost update
AND MTT.TRANSACTION_TYPE_ID =44
--AND MTT.TRANSACTION_TYPE_ID =nvl('&TYPE_ID',MTT.TRANSACTION_TYPE_ID)
AND we.wip_entity_id=MMT.transaction_source_id
AND WMT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WMT.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID結果三。
qty item
20 C4072800R04210GS1
40 C4073200RSW550GS1
40 C4073200RAW500GS1
60 C4073200RSW550GR1
70 C4073200RAW500GR1
100 C1032000R03150GS1其中,
結果一和結果二有相同的,可以關聯的itemid
結果二和結果三有相同的,可以關聯的item想把三個查詢結果橫向串接在一起結果一. 結果二. 結果三。
itemid item name qty item qty itemid qty item
45843 T404072601 背坑貼紙 7500 C1032000R03150GM1 1 42292 20 C4072800R04210GS1
45844 T404072602 背坑貼紙 11000 C1032000R03150GM1 1 42620 40 C4073200RSW550GS1
45845 T404072603 背坑貼紙 36000 C1032000R03150GM1 1 42258 40 C4073200RAW500GS1
45846 T404072604 背坑貼紙 5000 C1032000R03150GM1 1 42623 60 C4073200RSW550GR1
45919 T40N176880 貼紙Shaft 3600 C1032000R03150GM1 1 42721 70 C4073200RAW500GR1
45920 T40N176890 貼紙Shaft 4560 C1032000R03150GM1 0.04 43765 100 C1032000R03150GS1
45968 Q021000011 透明膠帶 81 C1032000R03150GM1 0.01 34621
46577 M01870C0143 美國烤漆 8 C1032000R03150GM1 0.0002 42737
46578 M01870C0144 美國烤漆 8 C1032000R03150GM1 0.0008 23258
記得可以用join
哪位幫幫忙啊!!!!
SELECT distinct
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
--MMT.SUBINVENTORY_CODE ,
--MMT.TRANSACTION_UOM
sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
--sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
-- and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
--and bd.department_code in('&s_dept')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
結果一.
itemid item name qty
45843 T404072601 40726(3~5#)背坑貼紙 7500
45844 T404072602 40726(6~7#)背坑貼紙 11000
45845 T404072603 40726(8~PW#)背坑貼紙 36000
45846 T404072604 40726(AW~SW#)背坑貼紙 5000
45919 T40N176880 貼紙Shaft Sticker 3-7 3600
45920 T40N176890 貼紙Shaft Sticker 8 4560
45968 Q021000011 透明常溫膠帶120MM*60Y 81
46061 A4073000R06270A 40730 (N1763801) 15125
46357 A9078200RPT000A 90782 (N0952401) FO 9
46577 M01870C0143 美國烤漆 870C0143 8
46578 M01870C0144 美國烤漆 870C0144 8
查詢二
select MSIF.SEGMENT1 "item",
BIC.COMPONENT_QUANTITY "QTY",
BIC.COMPONENT_ITEM_ID "itemid"
from BOM_BILL_OF_MATERIALS BBOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_b MSIF,
(select msib.segment1 item,
mcb.SEGMENT1 model
from mtl_system_items_b msib,
mtl_system_items_tl msit,
MTL_ITEM_CATEGORIES mic,
MTL_CATEGORIES_B mcb
where msib.ORGANIZATION_ID = '83'
and msib.ORGANIZATION_ID = msit.ORGANIZATION_ID
and msit.LANGUAGE = 'ZHT'
and msib.INVENTORY_ITEM_ID = msit.INVENTORY_ITEM_ID
and mic.inventory_item_id = msib.INVENTORY_ITEM_ID
and mic.ORGANIZATION_ID = '88'
and mic.category_set_id = 1100000001
and mic.category_id = mcb.category_id) msim,
(select msib.segment1 item,
NVL(mdev.element_value,'') semicolo
from MTL_DESCR_ELEMENT_VALUES mdev,
mtl_system_items_b msib
where msib.ORGANIZATION_ID = '88'
and mdev.element_name = '分號'
and mdev.inventory_item_id = msib.INVENTORY_ITEM_ID) msis,
(select msib.segment1 item,
NVL(mdev.element_value,'') degree
from MTL_DESCR_ELEMENT_VALUES mdev,
mtl_system_items_b msib
where msib.ORGANIZATION_ID = '88'
and mdev.element_name ='度數'
and mdev.inventory_item_id = msib.INVENTORY_ITEM_ID) msid
-- and msib.segment1 = '&item_num') msid
where BBOM.BILL_SEQUENCE_ID=BIC.BILL_SEQUENCE_ID
and BBOM.ORGANIZATION_ID = '88'
and BIC.DISABLE_DATE IS NULL
-- and MSIF.SEGMENT1 ='AMW25MTCR09260A'
and MSIF.ORGANIZATION_ID = BBOM.ORGANIZATION_ID
and BBOM.ASSEMBLY_ITEM_ID=MSIF.INVENTORY_ITEM_ID
and msim.item=msif.segment1
-- and msim.model like '&p_model%'
and MSIF.SEGMENT1 like 'C%'
and msis.item(+)=msif.segment1
-- and nvl(msis.semicolo,' ') like '&p_semicolo%'
and msid.item(+)=msif.segment1
-- and nvl(msid.degree,' ') like '&p_degree%'
and msif.inventory_item_status_code='Active'
order by MSIF.SEGMENT1,ITEM_NUM
結果二.
item qty itemid
C1032000R03150GM1 1 42292
C1032000R03150GM1 1 42620
C1032000R03150GM1 1 42258
C1032000R03150GM1 1 42623
C1032000R03150GM1 1 42721
C1032000R03150GM1 0.04 43765
C1032000R03150GM1 0.01 34621
C1032000R03150GM1 0.0008 43143
C1032000R03150GM1 0.00025 9652
C1032000R03150GM1 0.0002 42737
C1032000R03150GM1 0.0008 23258
C1032000R03150GM1 0.0002 31088
C1032000R03150GM1 0.0002 31089查詢三。
select distinct
MMT.TRANSACTION_QUANTITY "QTY",
MSIF.SEGMENT1 "item",
from
WIP_ENTITIES WE,
WIP_MOVE_TRANSACTIONS WMT,
WIP_DISCRETE_JOBS WDJ,
(SELECT MSIF.INVENTORY_ITEM_ID,MICV.CATEGORY_CONCAT_SEGS
FROM MTL_ITEM_CATEGORIES_V MICV,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MICV.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MICV.ORGANIZATION_ID = 88 AND MSIF.ORGANIZATION_ID = 88
AND MICV.CATEGORY_SET_ID = '1100000002') msis,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_FVL MSIF
where
TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date'
-- (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD') BETWEEN :P_FROM_DATE AND :P_TO_DATE)
AND MMT.SUBINVENTORY_CODE='WEF600'
-- AND (MMT.SUBINVENTORY_CODE BETWEEN ':P_FROM_INV' AND ':P_TO_INV')
and msis.inventory_item_id=MSIF.INVENTORY_ITEM_ID
AND (MMT.INVENTORY_ITEM_ID = MSIF.INVENTORY_ITEM_ID
AND MSIF.ORGANIZATION_ID = 88)
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTT.TRANSACTION_TYPE_ID not in (24) -- Sales Order Pick,Standard cost update
AND MTT.TRANSACTION_TYPE_ID not in (24) -- Sales Order Pick,Standard cost update
AND MTT.TRANSACTION_TYPE_ID =44
--AND MTT.TRANSACTION_TYPE_ID =nvl('&TYPE_ID',MTT.TRANSACTION_TYPE_ID)
AND we.wip_entity_id=MMT.transaction_source_id
AND WMT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WMT.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID結果三。
qty item
20 C4072800R04210GS1
40 C4073200RSW550GS1
40 C4073200RAW500GS1
60 C4073200RSW550GR1
70 C4073200RAW500GR1
100 C1032000R03150GS1其中,
結果一和結果二有相同的,可以關聯的itemid
結果二和結果三有相同的,可以關聯的item想把三個查詢結果橫向串接在一起結果一. 結果二. 結果三。
itemid item name qty item qty itemid qty item
45843 T404072601 背坑貼紙 7500 C1032000R03150GM1 1 42292 20 C4072800R04210GS1
45844 T404072602 背坑貼紙 11000 C1032000R03150GM1 1 42620 40 C4073200RSW550GS1
45845 T404072603 背坑貼紙 36000 C1032000R03150GM1 1 42258 40 C4073200RAW500GS1
45846 T404072604 背坑貼紙 5000 C1032000R03150GM1 1 42623 60 C4073200RSW550GR1
45919 T40N176880 貼紙Shaft 3600 C1032000R03150GM1 1 42721 70 C4073200RAW500GR1
45920 T40N176890 貼紙Shaft 4560 C1032000R03150GM1 0.04 43765 100 C1032000R03150GS1
45968 Q021000011 透明膠帶 81 C1032000R03150GM1 0.01 34621
46577 M01870C0143 美國烤漆 8 C1032000R03150GM1 0.0002 42737
46578 M01870C0144 美國烤漆 8 C1032000R03150GM1 0.0008 23258
記得可以用join
哪位幫幫忙啊!!!!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货