Oracle EBS-SQL (WIP-14):检查车间需求与BOM差异对照.sql

select

a.*,

b.BOM定额,

a.WIP定额 - b.BOM定额     差异

from

(select

                     WE.WIP_ENTITY_NAME                             任务号,

                     Decode(wdj.JOB_TYPE,1,'标准',3,'非标准') 作业类型,

                     MSI1.SEGMENT1                                    项目编码,

                     Msi1.Description                                    项目描述,

                     nvl(WDJ.START_QUANTITY,0)                 任务数量,

                     nvl(WDJ.QUANTITY_COMPLETED,0)         完成数量,

                     MSI2.SEGMENT1                                    物料编码,

                     Msi2.Description                                     物料描述,

                     nvl(WRO.REQUIRED_QUANTITY,0)           需求数量,

                     nvl(WRO.QUANTITY_ISSUED,0)               发送数量,

                     nvl(WRO.REQUIRED_QUANTITY,0)-nvl(WRO.QUANTITY_ISSUED,0) 未发,

                     wro.quantity_per_assembly                     WIP定额

from

                     WIP.WIP_DISCRETE_JOBS                           WDJ,

                     WIP.WIP_REQUIREMENT_OPERATIONS        WRO,

                     apps.Mtl_System_Items                             MSI1,

                     apps.Mtl_System_Items                             MSI2,

                     WIP.WIP_ENTITIES                                       we

where

                    WE.ORGANIZATION_ID =x

                    AND   WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID

                    AND   WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID

                    AND   Msi1.INVENTORY_ITEM_ID(+) = We.PRIMARY_ITEM_ID

                    AND   Msi1.ORGANIZATION_ID = We.ORGANIZATION_ID

                    AND   WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID

                    AND   WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID

                    AND   MSI2.INVENTORY_ITEM_ID(+)= WRO.INVENTORY_ITEM_ID

                    AND   MSI2.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID

                    AND   msi2.item_type(+) <>'PH'

)a,

(select

         msi.segment1                                          项目编码,

         msi1.segment1                                        物料编码,

         msi1.description                                      物料描述,

         COM.COMPONENT_QUANTITY                  BOM定额

from

         apps.MTL_SYSTEM_ITEMS msi,

         apps.MTL_SYSTEM_ITEMS msi1,

         apps.BOM_BILL_OF_MATERIALS                  BOM,

         apps.BOM_INVENTORY_COMPONENTS         COM

where

         msi.ORGANIZATION_ID=x

         AND msi.segment1 in

(select

                        MSI1.SEGMENT1

 from

                        WIP.WIP_DISCRETE_JOBS                      WDJ,

                        WIP.WIP_REQUIREMENT_OPERATIONS   WRO,

                        apps.Mtl_System_Items                        MSI1,

                        apps.Mtl_System_Items                        MSI2,

                        WIP.WIP_ENTITIES                                  we

   where

                   AND WE.ORGANIZATION_ID = x

                   AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID

                   AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID

                   AND Msi1.INVENTORY_ITEM_ID(+) = We.PRIMARY_ITEM_ID

                   AND Msi1.ORGANIZATION_ID(+) = We.ORGANIZATION_ID

                   AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID

                   AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID

                   AND MSI2.INVENTORY_ITEM_ID(+)= WRO.INVENTORY_ITEM_ID

                   AND msi2.item_type(+) <>'PH'

                   AND MSI2.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID

 )

    AND   bom.assembly_item_id=msi.inventory_item_id

    AND   bom.bill_sequence_id=COM.bill_sequence_id

    AND   COM.component_item_id=msi1.inventory_item_id

    AND   msi1.organization_id=msi.organization_id

    AND   COM.DISABLE_DATE is null

)b

where

           a.项目编码(+)=b.项目编码

    AND a.物料编码(+)=b.物料编码

    AND (a.WIP定额 - b.BOM定额 <> 0 or a.WIP定额 - b.BOM定额  is null)