ERP系统常用SQL集锦(转)

carcoon

太好了，谢谢楼主！太好了，谢谢楼主！太好了，谢谢楼主！

_珊珊_

技术型啊，学习...

xmzksl

回复是一种友谊，一种美德★

liusp525

BOM 展开：
select DISTINCT
msi1.segment1 P_item,
to_char(b.lvl) LV,
c.Department_Code,
nvl(bom.alternate_bom_designator,'P') Alternate_bom_designator,
msi2.segment1 C_item,
c.Usage,
b.component_quantity Component_Qty
from inv.mtl_system_items_b msi1,
inv.mtl_system_items_b msi2,
bom.bom_bill_of_materials bom,
(select level lvl,
bic.bill_sequence_id,
bic.component_item_id,
bic.component_quantity,
bic.COMPONENT_YIELD_FACTOR,
bic.item_num,
bic.wip_supply_type,
bic.supply_subinventory,
bic.effectivity_date
FROM bom.bom_inventory_components bic
where disable_date IS NULL
start with bic.bill_sequence_id in
( select bill_sequence_id
from bom.bom_bill_of_materials bom2,
inv.mtl_system_items_b msi
where bom2.assembly_item_id = msi.inventory_item_id
and bom2.organization_id = msi.organization_id
and msi.segment1 = '98-222-13011'
and msi.organization_id = 89
and bom2.alternate_bom_designator is null
)
CONNECT BY bic.bill_sequence_id =
prior (SELECT distinct bill_sequence_id
FROM bom.bom_bill_of_materials BO,
inv.mtl_system_items_b msi
WHERE BO.assembly_item_id = bic.component_item_id
AND BO.organization_id = 89
and bo.ORGANIZATION_ID = msi.ORGANIZATION_ID
and bo.ASSEMBLY_ITEM_ID = msi.INVENTORY_ITEM_ID
and bo.alternate_bom_designator is null
and disable_date IS NULL
)
) b,
(select msib.inventory_item_id inventory_item_id,
nvl(bor.alternate_routing_designator,'P') Alternate,
bd.department_code Department_Code,
sum(bore.usage_rate_or_amount) Usage
from inv.mtl_system_items_b msib,
bom.bom_operational_routings bor,
bom.bom_operation_sequences bos,
bom.bom_operation_resources bore,
bom.bom_departments bd,
bom. bom_resources br
where msib.inventory_item_id = bor.assembly_item_id
and msib.organization_id = bor.organization_id
and msib.organization_id = 89
and bor.routing_sequence_id = bos.routing_sequence_id
and bos.operation_sequence_id = bore.operation_sequence_id
and bos.department_id = bd.department_id
and bore.resource_id = br.resource_id
and bos.DISABLE_DATE is null
and bor.alternate_routing_designator is null
and bore.usage_rate_or_amount != 0
group by msib.inventory_item_id, bor.alternate_routing_designator, bd.department_code
) c
where b.bill_sequence_id = bom.bill_sequence_id
and bom.ORGANIZATION_ID = 89
and bom.ORGANIZATION_ID = msi1.ORGANIZATION_ID
and bom.ASSEMBLY_ITEM_ID = msi1.INVENTORY_ITEM_ID
and bom.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND b.component_item_id = MSI2.INVENTORY_ITEM_ID
AND MSI2.SEGMENT1 not lIKE '98%'
AND MSI2.SEGMENT1 not like '99%'
and msi2.segment1 not like '90%'
and msi2.segment1 not like '97%'
and msi2.segment1 not like '92%'
and msi2.segment1 not like '95%'
and msi2.segment1 not like '65%'
and bom.ASSEMBLY_ITEM_ID = c.inventory_item_id
order by 2