MDS Organization 是 BU
MTL_Secondary_Inventories.Attribute10 =MTL_Parameters.Organization_Code

   INNER JOIN cux.xx_aps_asy_mds_bom bx ON bx.organization_code =six.attribute10

           WHERE six.organization_id                  =miq.organization_id

                AND six.secondary_inventory_name=miq.subinventory_code

                AND six.availability_type                 = 1

cux.xx_aps_asy_mds_bom bx ON bx.organization_code =six.attribute10

              WHERE six.organization_id         =miq.organization_id

                AND six.secondary_inventory_name=miq.subinventory_code

                AND six.availability_type       = 1

                AND bx.substitute_item_id=miq.inventory_item_id )

-  On Hand

   CURSOR c_onhand_sum   --Product_Line EMS CODE

   IS

      SELECT  COUNT(*)  --,miq.inventory_item_id,miq.organization_id

                        -- SUM(miq.transaction_quantity) AS quantity

        FROM  mtl_oh_qtys_sn miq

WHERE EXISTS(SELECT 1 FROM mtl_secondary_inventories six

                INNER JOIN cux.xx_aps_asy_mds_bom bx ON bx.organization_code =six.attribute10

              WHERE six.organization_id         =miq.organization_id

                AND six.secondary_inventory_name=miq.subinventory_code

                AND six.availability_type       = 1

                AND bx.substitute_item_id=miq.inventory_item_id )

GROUP BY miq.inventory_item_id,miq.organization_id;

   CURSOR c_onhand   --Product_Line EMS CODE

   IS

      SELECT-- b.organization_id   EMS_ID,   -- product line ems id

            -- b.organization_code EMS_CODE, -- si.attribute10 EMS_CODE

           --  b.component_item_id,          --,b.organization_id,b.organization_code,  --b.assembly_item_id  , primary_item_id

             miq.inventory_item_id,

             si.attribute10  organization_code,

             si.organization_id AS bu_org, -- asus bu (product line)

             si.secondary_inventory_name,  -- availabe sub-inventory

             miq.transaction_quantity AS quantity

        FROM mtl_oh_qtys_sn miq

  INNER JOIN mtl_secondary_inventories si ON si.organization_id         =miq.organization_id

                                         AND si.secondary_inventory_name=miq.subinventory_code

                                         AND si.availability_type       = 1

 -- INNER JOIN cux.xx_aps_asy_mds_bom b ON b.substitute_item_id=miq.inventory_item_id

 --                                    AND b.organization_code =si.attribute10

WHERE EXISTS(SELECT 1 FROM mtl_secondary_inventories six

                INNER JOIN cux.xx_aps_asy_mds_bom bx ON bx.organization_code =six.attribute10

              WHERE six.organization_id         =miq.organization_id

                AND six.secondary_inventory_name=miq.subinventory_code

                AND six.availability_type       = 1

                AND bx.substitute_item_id=miq.inventory_item_id );