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 );

MTL_SAFETY_STOCKS 沒有 '04G185009492'

 SELECT COUNT(*)
           FROM MTL_SAFETY_STOCKS s
--     INNER JOIN mtl_parameters             e  ON e.organization_id     = s.organization_id  --sn.organization_id
--                                             AND e.organization_code   = 'T21'
     INNER JOIN mtl_system_items_b      b  ON s.organization_id     = b.organization_id
                                          AND s.inventory_item_id   = b.inventory_item_id
                                          AND b.segment1 ='04G185009492'

SAP: OKOK

[上午 12:46:36] Adempiere/Compiere: 好
[上午 01:00:10] Adempiere/Compiere: 請試用
[上午 01:00:15] SAP: OKOK
[上午 01:01:40] SAP: 很多都有塞 0
[上午 01:01:46] SAP: 但, SS 還是沒有正確
[上午 01:01:54] Adempiere/Compiere: 不要嗎 ?
[上午 01:02:00] Adempiere/Compiere: 很多都有塞 0
[上午 01:02:22] Adempiere/Compiere: [但, SS 還是沒有正確] ?
[上午 01:02:22] SAP: SO, PO,  OH, ... 可以不用塞, ....<== 要塞也 OK
[上午 01:02:28] SAP: 但, SS 還是不正確
[上午 01:02:54] Adempiere/Compiere: , SS 還是不正確  怎麼不正確 法?
[上午 01:02:59] Adempiere/Compiere: 哪裡出錯
[上午 01:04:37] *** SAP 傳送 IMG_26112012_010524.png ***
SS 是用 MDS 計算而來
[上午 01:04:48] SAP: 我查一下 TABLE
[上午 01:05:22] SAP: 像 ROW 72
[上午 01:05:34] SAP: 應該會有資料
[上午 01:05:44] SAP: 目前, 只有 04 G 有一筆 30
[上午 01:06:16] SAP: 這個情形跟剛剛 MDS 很類似
[上午 01:06:24] SAP: 剛 MDS 也是只有一筆
[上午 01:06:38] Adempiere/Compiere: 好
[上午 01:06:45] Adempiere/Compiere: 我看一下
[上午 01:11:16] SAP: 等一下
[上午 01:11:21] SAP: 是我的錯....
[上午 01:11:34] SAP: 應該是 SS 的 資料問題
[上午 01:12:17] SAP: 只要查: 04G185009492 有 SS 即可
[上午 01:16:11] *** 來自 SAP 的電話，通話時間 00:13。 ***
[上午 01:19:21] SAP: 我先 發 MAIL , 請 USER 測試
[上午 01:19:36] SAP: 明天還需要你協助 計算 Planned Order 的部份,
[上午 01:19:44] SAP: 先謝咯!!
[上午 01:26:09] Adempiere/Compiere: Ok
[下午 02:38:59] SAP: HIHI
[下午 02:39:02] SAP: HIHI
[下午 02:39:07] SAP: 有通嗎?
[下午 02:43:22] Adempiere/Compiere: HI
[下午 02:43:30] SAP: OKOK
[下午 02:43:38] Adempiere/Compiere: [只要查: 04G185009492 有 SS 即可]
[下午 02:44:22] SAP: mgln

groovy:ImportData

import groovy.sql.Sql
import java.sql.Timestamp
//  sqlinformix = Sql.newInstance('jdbc:informix-sqli://192.168.170.211:1534/hisipddb:informixserver=hisipdsrv','informix','db@w94u6', 'com.informix.jdbc.IfxDriver' )
// sqlOracle = Sql.newInstance( 'jdbc:oracle:thin:@192.168.170.251:1521:orcl',  'adempiere','adempiere','oracle.jdbc.OracleDriver' )
//"jdbc:postgresql://hostname:port/dbname","username", "password");

sqlPostgres=Sql.newInstance('jdbc:postgresql://192.168.0.101:5433/adempiere', "adempiere", "adempiere","org.postgresql.Driver")
def m_created = new Timestamp(System.currentTimeMillis())
def  table_id=0
def v_IsActive="Y"
sqlPostgres.eachRow( 'SELECT *  FROM ADEMPIERE.M_Product' )
{
  table_id=table_id+1
  println "$it.Value -- ${it.Name} --"
 sqlPostgres.execute(
 'INSERT INTO ADEMPIERE.M_Product_T(M_Product_T_ID,AD_Client_ID,AD_Org_ID,Updated, UpdatedBy,Created,CreatedBy, IsActive,M_Product_ID,Value,Name,Description,C_UOM_ID,M_Product_Category_ID,C_TaxCategory_ID)'
 +'VALUES ( ?, 11, 11, now(), 0, now(), 0, ?, ?, ?, ?, ?,?,?,? )' ,
 [table_id, v_IsActive, it.M_Product_ID,it.Value,it.Name,it.Description,it.C_UOM_ID,it.M_Product_Category_ID,it.C_TaxCategory_ID]  )
}
result=""

如何將文字串 output 到 cvs

   如何將文字串 output 到 cvs
 
    procedure proc_csv_output is
        w_output_string long;
        v_inv           varchar2_varray;
        w_title         varchar2(10000);
        inv_name        varchar2(4000);
        lv_desc1        varchar2(240);
        lv_desc2        varchar2(240);
        lv_class_code   wip_discrete_jobs.class_code%type;
        v_inv_names     varchar2(4000);
        v_isactive      varchar2(1);
        l_str           varchar2(30);

    begin

        w_title := 'WO_Number,Class Code,EMS ,Item No,Description,OP,PrimaryItem,Description'
                 ||',Subs Flag,ParentItem,Required,Issued,OpenQty,EMS_Onhand,WO_Shortage,Subs Qty,';

        v_inv_names := '';
        if inv_names.count > 0 then
        for ix in 1 .. inv_names.count loop
             if (inv_names(ix) is null or inv_names(ix) = '') then exit; end if;
                v_inv_names := v_inv_names || inv_names(ix) || ',';
        end loop;
        end if;
        dbms_output.put_line('g_csv_output.COUNT='||g_csv_output.count);

        fnd_file.put_line(fnd_file.log,g_csv_output.count);

        w_title := w_title || v_inv_names || 'PackMtl.PO';

        dbms_output.put_line('w_title='||w_title);

        fnd_file.put_line(2, w_title);

        v_isactive := 'Y';
        for i in 1 .. g_csv_output.count loop
            if g_csv_output(i).isactive is not null then
               v_isactive := g_csv_output(i).isactive;
            end if;

            if g_item is not null and g_item != '%' then
               l_str := g_item||'%';
               if g_csv_output(i).item_no not like l_str then
                  goto xx_next_record;
               end if;
            end if;

            if (g_csv_output(i).job_no is not null and v_isactive='Y') then
                w_output_string :=
                g_csv_output(i).job_no           || ','  ||
                g_csv_output(i).lv_class_code    || ','  ||
                g_csv_output(i).ems_name         || ','  ||
                g_csv_output(i).item_no          || ',"' ||
                g_csv_output(i).lv_desc1         || '",' ||
                g_csv_output(i).op               || ','  ||
                g_csv_output(i).primary_item     || ',"' ||
                g_csv_output(i).lv_desc2         || '",' ||
                g_csv_output(i).replace_falg     || ','  ||
                g_csv_output(i).parent_item      || ','  ||
                g_csv_output(i).required_quantity || ',' ||
                g_csv_output(i).issued_quantity  || ','  ||
                g_csv_output(i).open_quantity    || ','  ||
                g_csv_output(i).on_hand_quantity || ','  ||
                g_csv_output(i).need_quantity    || ',';

                v_inv := varchar2_varray(); --create empty varray
                v_inv.extend(25);
                v_inv := g_csv_output(i).inv;

                v_inv_names := '';
                if inv_names.count > 0 then
                for ix in 1 .. inv_names.count loop
                    if (inv_names(ix) is null or inv_names(ix) = '') then exit;
                    end if;
                    v_inv_names := v_inv_names || nvl(v_inv(ix),' ') || ',';
                end loop;
                end if;
                w_output_string :=  w_output_string || ',' || v_inv_names;
            
                fnd_file.put_line(2, w_output_string);
            end if;

            
            <>
            null;
            
        end loop; -- FOR i IN 1 .. g_csv_output.COUNT LOOP
        <>
        null;
    exception
        when e_program_err then
            raise e_program_err;
        when others then
            raise;
    end proc_csv_output; 


   type inv_array is table of varchar2(50) index by binary_integer;

    inv_names        varchar2_varray;

    type tbl_csv_output is table of grec_csv_output index by binary_integer;

    g_csv_output  tbl_csv_output;

    type grec_csv_output is record(
        job_no            varchar2(200),
        lv_class_code     varchar2(200),
        ems_name          varchar2(200),
        item_no           varchar2(200),
        lv_desc1          varchar2(200),
        op                number,
        replace_falg      varchar2(1),
        parent_item       varchar2(200),
        primary_item      varchar2(200),
        lv_desc2          varchar2(200),
        required_quantity varchar2(200),
        issued_quantity   varchar2(200),
        open_quantity     varchar2(200),
        on_hand_quantity  varchar2(200),
        need_quantity     varchar2(200),
        replace_quantity  varchar2(200),
    --  inv               NUMBER_VARRAY25,
        inv               varchar2_varray,
        po                varchar2(200),
        isactive          varchar2(1));


CREATE SYNONYM varchar2_varray
  FOR VARCHAR2_VARRAY
/

-- Start of DDL Script for Type SYSTEM.VARCHAR2_VARRAY
-- Generated 19-Nov-2012 10:54:58 from SYSTEM@EBS_8005

CREATE OR REPLACE
TYPE varchar2_varray AS VARRAY(10000) OF VARCHAR2(240);
/

-- Grants for Type
GRANT EXECUTE ON varchar2_varray TO apps
WITH GRANT OPTION
/
GRANT DEBUG ON varchar2_varray TO apps
WITH GRANT OPTION
/

xxapsp0058

要將　Oracle EBS MRP 改寫
Organization_ID,Inventory_Item_ID,
CurrWeek,...1stWeek,...11Week,...21Week,...26Week

如果有　oe_odr_lines_sn snapshot
有　promise_date / schedule_ship_date


TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW') promise_date,
FLOOR(
(
TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW')
)/7) weeks,




PACKAGE BODY xxapsp0058_pkg
IS
/*************************************************************************************
     NAME:     XXAPSP0057_PKG.  GET_WHEREUSE
     PURPOSE:  1. XXAPSP0057 ON HANDS
     REVISIONS:
     Ver        Date        Author           Description
     ---------  ----------  ---------------  ------------------------------------
     1.0        2012/11/11  Albert           1. Created this Package.
*************************************************************************************/
   PROCEDURE main (
      errbuf    OUT   VARCHAR2,
      retcode   OUT   VARCHAR2,
      --    P_BU                    VARCHAR2 , --remove this paramenter form user request(once for all)
      --    P_EMS                   VARCHAR2 , --(same with above)
      --    P_PRODUCT_LINE          VARCHAR2 , --(same with above)
      p_oh            VARCHAR2,                                        --'Y/N'
      p_so            VARCHAR2,                                        --'Y/N'
      p_wip           VARCHAR2,                                        --'Y/N'
      p_po            VARCHAR2,                                        --'Y/N'
      p_mds           VARCHAR2                                         --'Y/N'
   )
   IS
      CURSOR c_so
      IS
         SELECT /*+PARALLEL */
                TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW') promise_date,
                FLOOR(
                (TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW'))
                /7) weeks,
                sn.inventory_item_id, sn.organization_id,
            --  a.segment1 AS inventory_item,A
            --  mp.organization_code,   -- mtl_parameters.organization_code=mtl_secondary_inventories.attribute10
            --  oh.order_number||'-'||ol.line_number||'.'|| shipment_number AS order_number,
           SUM(NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0)) AS quantity
           FROM oe_odr_lines_sn sn
     INNER JOIN oe_order_lines_all ol ON ol.line_id = sn.line_id
                                     AND ol.flow_status_code = 'AWAITING_SHIPPING'
                                     AND TRUNC (ol.schedule_ship_date) < TRUNC (SYSDATE, 'WW') --  + 6
                                    
     INNER JOIN oe_order_headers_all       oh ON ol.header_id = oh.header_id
     INNER JOIN mtl_secondary_inventories msi ON msi.organization_id          = sn.organization_id
                                             AND msi.secondary_inventory_name = ol.subinventory
                                             AND msi.availability_type        = 1
     INNER JOIN mtl_parameters             mp ON mp.organization_code  = msi.attribute10  --EMS mtl_secondary_inventories
     INNER JOIN mtl_item_categories       mic ON mic.inventory_item_id = sn.inventory_item_id
                                             AND mic.organization_id   = sn.organization_id
     INNER JOIN mtl_categories_b           mc ON mc.category_id = mic.category_id
                                             AND mc.segment1 IN ('FG', 'SM')
------------------------------------------------
     INNER JOIN mtl_parameters          e  ON e.organization_id     = mp.organization_id  --sn.organization_id
                                        --AND e.attribute12         = 'Open'  -- this not a index key
                                        --AND e.attribute10         IN (2,5)  -- (same with above)
     INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id  --sn.organization_id
                                          AND b.inventory_item_id   = sn.inventory_item_id
                                          AND b.category_set_id     = 1
     INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
     INNER JOIN mtl_system_items_b      a  ON a.organization_id     =b.organization_id   --a.segment1 AS inventory_item
                                          AND a.inventory_item_id   =b.inventory_item_id
                                          AND a.wip_supply_type    <> 6
          WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0
          AND   FLOOR((TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW')) /7) < 26
          GROUP BY
                TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW'),
                FLOOR(
                (TRUNC(coalesce(sn.promise_date,sn.schedule_ship_date)+1,'WW')- TRUNC(SYSDATE+1,'WW'))
                /7),
                sn.inventory_item_id, sn.organization_id;
   --  GROUP BY TRUNC(sn.promise_date+1,'WW'), -- promise_date,
   --                 sn.inventory_item_id,
   --                 mp.organization_id,
   --                 a.segment1;              -- AS inventory_item,

-- MDS --
      CURSOR c_mds
      IS
         SELECT /*+PARALLEL */
                msd.inventory_item_id,
                msd.organization_id,
                a.segment1 AS inventory_item,
                msd.schedule_designator AS order_number,
                msd.schedule_quantity                      --schedule_quantity
           FROM mrp_schedule_dates msd
     INNER JOIN mtl_parameters mp ON mp.organization_id   = msd.organization_id
                            --   AND mp.ORGANIZATION_CODE = NVL(P_EMS,mp.ORGANIZATION_CODE )
                            --   AND mp.ATTRIBUTE13       = NVL(P_BU, mp.ATTRIBUTE13)
     INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = msd.inventory_item_id
                                       AND mic.organization_id   = msd.organization_id
     INNER JOIN mtl_categories_b mc ON mc.category_id = mic.category_id
                                   AND mc.segment1 IN ('FG', 'SM')
                               --  AND mc.SEGMENT2 = NVL(P_PRODUCT_LINE, mc.SEGMENT
     ------------
     INNER JOIN mtl_parameters          e  ON e.organization_id     = mp.organization_id
     --msd.organization_id  --1,183,923
                                        --AND e.attribute12         = 'Open'
                                        --AND e.attribute10         IN (2,5)
     INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id
     --msd.organization_id
                                          AND b.inventory_item_id   = msd.inventory_item_id
                                          AND b.category_set_id     = 1
     INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
     INNER JOIN mtl_system_items_b      a  ON b.organization_id     = a.organization_id  --a.segment1 AS inventory_item,
                                          AND b.inventory_item_id   = a.inventory_item_id
                                          AND a.wip_supply_type    <> 6 --
     WHERE  msd.schedule_designator LIKE '%PMALLC';

      -- Work Order  Order Scrap --
      -- MSC_SUPPLIES  3 Work order
      -- MSC_DEMANDS  17 Work Order scrap
      CURSOR c_wo
      IS
            SELECT /*+PARALLEL */
                   we.wip_entity_name order_number, a.segment1 inventory_item,
                   mp.organization_id, wdj.primary_item_id inventory_item_id,
                   NVL  (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0)  wip_qty,
                   --CEIL (wdj.net_quantity * NVL (msb.shrinkage_rate, 0))  wip_scrap_qty
                   LEAST(CEIL (NVL(wdj.net_quantity,0) * NVL (msb.shrinkage_rate, 0)),NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) ) as wip_scrap_qty
              FROM wip_dscr_jobs_sn wip
        INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id = wip.wip_entity_id
                                        AND wdj.status_type IN (1, 3, 6)
        INNER JOIN wip_entities we ON we.wip_entity_id = wip.wip_entity_id
        INNER JOIN mtl_secondary_inventories msi ON msi.organization_id          = wdj.organization_id
                                                AND msi.secondary_inventory_name = wdj.completion_subinventory
                                                AND msi.availability_type        = 1
        INNER JOIN mtl_parameters             mp ON mp.organization_code = msi.attribute10
        INNER JOIN mtl_system_items_b        msb ON msb.organization_id  = mp.organization_id
                                                AND msb.inventory_item_id = wdj.primary_item_id
        INNER JOIN mtl_item_categories mic  ON mic.inventory_item_id = wdj.primary_item_id
                                           AND mic.organization_id = wdj.organization_id
        INNER JOIN mtl_categories_b mc  ON  mc.category_id = mic.category_id
                                       AND mc.segment1 IN ('FG', 'SM')

     INNER JOIN mtl_parameters          e  ON e.organization_id     = mp.organization_id
     INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id
                                          AND b.inventory_item_id   = wdj.primary_item_id
                                          AND b.category_set_id     = 1
     INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
     INNER JOIN mtl_system_items_b      a  ON b.organization_id     = a.organization_id --a.segment1 AS inventory_item,
                                          AND b.inventory_item_id   = a.inventory_item_id
                                          AND a.wip_supply_type    <> 6 --
             --AND NVL (wdj.start_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
               AND NVL (wdj.net_quantity, 0) - NVL (wdj.quantity_completed, 0) > 0;
      -- Work Order Demand --Component --  [Wip ???[status ??1. 3. 6 ???????H} OK
      CURSOR c_wo_demand
      IS
         SELECT /*+PARALLEL */
                wwo.inventory_item_id,
                a.segment1  AS inventory_item,
                mp.organization_id,  -- wwo.ORGANIZATION_ID,
                we.wip_entity_name AS order_number,
                  NVL (wwo.required_quantity, 0)
                - NVL (wwo.quantity_issued, 0) AS wo_demand_qty
           FROM wip_wreq_oprs_sn wwo
     INNER JOIN wip_discrete_jobs wdj ON wdj.wip_entity_id=wwo.wip_entity_id
                                     AND wdj.status_type IN (1, 3, 6) --UNRELEASED, RELEASED, ON HOLD
     INNER JOIN wip_entities we ON we.wip_entity_id = wwo.wip_entity_id
     INNER JOIN mtl_secondary_inventories msi  ON msi.organization_id         =wdj.organization_id
                                              AND msi.secondary_inventory_name=wdj.completion_subinventory
                                              AND msi.availability_type = 1
     INNER JOIN mtl_parameters       mp ON mp.organization_code = msi.attribute10
     INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = wwo.inventory_item_id
                                       AND mic.organization_id   = wwo.organization_id
     INNER JOIN mtl_categories_b     mc ON mc.category_id = mic.category_id
                                       AND mc.segment1 IN ('FG', 'SM')

     INNER JOIN mtl_parameters          e  ON e.organization_id     = mp.organization_id --wwo.organization_id
     INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id --wwo.organization_id
                                          AND b.inventory_item_id   = wwo.inventory_item_id
                                          AND b.category_set_id     = 1
     INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
     INNER JOIN mtl_system_items_b      a  ON b.organization_id     = a.organization_id --a.segment1 AS inventory_item,
                                          AND b.inventory_item_id   = a.inventory_item_id
                                          AND a.wip_supply_type    <> 6 --
          WHERE NVL (wwo.required_quantity, 0) - NVL (wwo.quantity_issued, 0) <> 0;

      -- MSC_SUPPLIES  1 Purchase order
      -- MSC_SUPPLIES  2 Purchase requisition
      -- MSC_SUPPLIES  8 PO in receiving
      CURSOR c_po
      IS
         SELECT /*+PARALLEL */
                   COALESCE (po.segment1,
                             ro.segment1,
                             so.shipment_num
                            )
                || '-'
                || COALESCE (pl.line_num, rl.line_num, sl.line_num)
                                                              AS order_number,
                CASE
                   WHEN po.segment1 IS NOT NULL
                      THEN 1
                   WHEN ro.segment1 IS NOT NULL
                      THEN 2
                   WHEN so.shipment_num IS NOT NULL
                      THEN 8
                   ELSE 0
                END AS order_type,
                sn.to_subinventory, msi.secondary_inventory_name,
                mp.organization_id, sn.item_id AS inventory_item_id,
                a.segment1  AS inventory_item, sn.supply_type_code, sn.quantity
            FROM mtl_supply_sn sn
 LEFT OUTER JOIN po_headers_all             po ON sn.po_header_id  =po.po_header_id
 LEFT OUTER JOIN po_lines_all               pl ON sn.po_line_id    =pl.po_line_id
 LEFT OUTER JOIN po_requisition_headers_all ro ON sn.req_header_id =ro.requisition_header_id
 LEFT OUTER JOIN po_requisition_lines_all   rl ON sn.req_line_id   =rl.requisition_line_id
 LEFT OUTER JOIN rcv_shipment_headers       so ON sn.shipment_header_id=so.shipment_header_id
 LEFT OUTER JOIN rcv_shipment_lines         sl ON sn.shipment_line_id  =sl.shipment_line_id
      INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=
                                     COALESCE (po.org_id, ro.org_id, so.organization_id)
                                     AND msi.secondary_inventory_name=sn.to_subinventory
                                     AND msi.availability_type = 1
      INNER JOIN mtl_parameters       mp  ON mp.organization_code  = msi.attribute10
      INNER JOIN mtl_item_categories mic  ON mic.inventory_item_id = sn.item_id
                                         AND mic.organization_id   = COALESCE (po.org_id, ro.org_id, so.organization_id)
      INNER JOIN mtl_categories_b     mc  ON mc.category_id = mic.category_id
                                         AND mc.segment1 IN ('FG', 'SM')

   INNER JOIN mtl_parameters          e  ON e.organization_id     =  mp.organization_id
       --COALESCE (po.org_id, ro.org_id, so.organization_id)
                                        --AND e.attribute12         = 'Open'
                                        --AND e.attribute10         IN (2,5)
     INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id
       --COALESCE (po.org_id, ro.org_id, so.organization_id)
                                          AND b.inventory_item_id   = sn.item_id
                                          AND b.category_set_id     = 1
     INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
     INNER JOIN mtl_system_items_b      a  ON b.organization_id     = a.organization_id    --a.segment1 AS inventory_item,
                                          AND b.inventory_item_id   = a.inventory_item_id
                                          AND a.wip_supply_type    <> 6 --

                                    --   AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
      INNER JOIN cux.xx_c_mcatp_rule_t zz  ON zz.inventory_item_id = sn.item_id
                                          AND zz.organization_id   = COALESCE (po.org_id, ro.org_id, so.organization_id)

      WHERE 1=1;

      --ON HwAND
      CURSOR c_onhand
      IS
         SELECT   /*+PARALLEL */
                  mp.attribute1        AS ems_group,
                  mp.organization_code AS ems,
                  miq.inventory_item_id,
                  mp.organization_id,
                  a.segment1                     AS inventory_item,
                  msi.secondary_inventory_name   AS subinventory_code,
                  SUM (miq.transaction_quantity) AS on_hand_quantity
 FROM mtl_oh_qtys_sn miq
INNER JOIN mtl_secondary_inventories msi ON msi.organization_id=miq.organization_id
       AND msi.secondary_inventory_name=miq.subinventory_code
       AND msi.availability_type = 1
 INNER JOIN mtl_parameters mp  ON mp.organization_code = msi.attribute10
                        --    AND mp.ORGANIZATION_CODE = NVL(P_EMS , mp.ORGANIZATION_CODE)
                        --    AND mp.ATTRIBUTE13       = NVL(P_BU  , mp.ATTRIBUTE13)
 INNER JOIN mtl_item_categories mic ON mic.inventory_item_id = miq.inventory_item_id
                                   AND mic.organization_id   = miq.organization_id
 INNER JOIN mtl_categories_b mc  ON mc.category_id = mic.category_id
                                AND mc.segment1 IN ('FG', 'SM')
                            --  AND MC.SEGMENT2 = NVL(P_PRODUCT_LINE, MC.SEGMENT2)
 INNER JOIN mtl_parameters    e  ON e.organization_id     = mp.organization_id  --1,183,923
                              --AND e.attribute12         = 'Open'
                              --AND e.attribute10         IN (2,5)
INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id
                                     AND b.inventory_item_id   = miq.inventory_item_id
                                     AND b.category_set_id     = 1
INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
INNER JOIN mtl_system_items_b      a  ON b.organization_id     = a.organization_id   
  --a.segment1 AS inventory_item,
                                     AND b.inventory_item_id   = a.inventory_item_id
                                     AND a.wip_supply_type    <> 6 --
 WHERE 1 = 1
GROUP BY msi.secondary_inventory_name,
         mp.organization_id,
         mp.attribute1,                                  -- EMS_GROUP
         mp.organization_code,                                 -- EMS
         miq.inventory_item_id,
         a.segment1
ORDER BY msi.secondary_inventory_name,
         mp.organization_id,
         mp.attribute1,
         mp.organization_code,
         miq.inventory_item_id,
         a.segment1 ;


      v_user_id             NUMBER          := fnd_global.user_id;
      v_login_id            NUMBER          := fnd_global.conc_login_id;
      v_bu_id               NUMBER;
      v_assembly_item_id    NUMBER;
      v_cnt                 NUMBER;
      v_mod                 NUMBER          := 0;
      v_end_item_id         NUMBER;
      v_organization_id     NUMBER;
      v_component_item_id   NUMBER;
      v_counttable          NUMBER;
      v_char                VARCHAR2 (10);
      v_sql                 VARCHAR2 (2000);
      v_string              VARCHAR2 (2000);
      v_plan_id             NUMBER          := 0;
      v_instance_code       VARCHAR2 (10)   := 'EBS';
   BEGIN
         /*
         v_mod := 0;
         FOR r1 IN c_mds
         LOOP
            v_mod := v_mod + 1;

         END LOOP;
         DBMS_OUTPUT.put_line (   'c_mds v_mod='||v_mod);
         --MSC_DEMANDS 3 Work order demand
         v_mod := 0;
         FOR r1 IN c_wo_demand
         LOOP
            v_mod := v_mod + 1;
         END LOOP;
         DBMS_OUTPUT.put_line (   'c_wo_demand v_mod='||v_mod);
         -- MSC_SUPPLIES  3 Work order
         -- MSC_DEMANDS  17 Work Order scrap
         v_mod := 0;
         FOR r1 IN c_wo
         LOOP
            v_mod := v_mod + 1;

         END LOOP;
         DBMS_OUTPUT.put_line (   'c_wo v_mod='||v_mod);
         -- MSC_SUPPLIES  1 Purchase order
         -- MSC_SUPPLIES  2 Purchase requisition
         -- MSC_SUPPLIES  8 PO in receiving
         v_mod := 0;
         FOR r1 IN c_po
         LOOP
            v_mod := v_mod + 1;
         END LOOP;
         DBMS_OUTPUT.put_line (   'c_po v_mod='||v_mod);
         */
         -- MSC_DEMANDS 30 Sales Orders
         -- DBMS_OUTPUT.PUT_LINE('FOR R1 IN C10 LOOP');
         v_mod := 0;
         FOR r1 IN c_so
         LOOP
            v_mod := v_mod + 1;
         END LOOP;
         DBMS_OUTPUT.put_line (   'c_so v_mod='||v_mod);
         /*
         -- MSC_SUPPLIES 18 On Hand
         v_mod := 0;
         FOR r1 IN c_onhand
         LOOP
            v_mod := v_mod + 1;
         END LOOP;
         DBMS_OUTPUT.put_line (   'c_so v_mod='||v_mod);
         */
         COMMIT;

      <>
      DBMS_OUTPUT.put_line ('....END....');
   END main;

   FUNCTION get_whereuse (p_component_item_id NUMBER, p_organization_id NUMBER)
      RETURN NUMBER
   IS
      v_return              NUMBER := 0;
      v_count               NUMBER := 0;

      CURSOR c4 (x_component_item_id NUMBER, x_organization_id NUMBER)
      IS
         --SELECT * FROM (
                 SELECT   bbo.assembly_item_id
                     -- , bic.component_item_id, bsc.substitute_component_id
                     FROM bom_structures_b bbo
               INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
                     --   bom_substitute_components bsc
                    WHERE bic.component_item_id = x_component_item_id
                   -- AND bsc.component_sequence_id = bic.component_sequence_id
                   -- AND bsc.substitute_component_id = x_component_item_id
                      AND bbo.organization_id  = x_organization_id
                 ORDER BY bbo.assembly_item_id;
         --) WHERE ROWNUM = 1;

      CURSOR c4s (x_component_item_id NUMBER, x_organization_id NUMBER)
      IS
        -- SELECT * FROM (
                   SELECT bbo.assembly_item_id
                    --  , bic.component_item_id, bsc.substitute_component_id
                     FROM bom_structures_b bbo
               INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
               INNER JOIN bom_substitute_components bsc ON bsc.component_sequence_id = bic.component_sequence_id
                    WHERE 1 = 1
                      AND bsc.substitute_component_id = x_component_item_id
                      AND bbo.organization_id = x_organization_id
                 ORDER BY bbo.assembly_item_id;
        --   ) WHERE ROWNUM = 1;

      v_component_item_id   NUMBER := 0;
      v_organization_id     NUMBER := 0;
      v_assembly_item_id    NUMBER := 0;
   BEGIN
      v_assembly_item_id := p_component_item_id;

      -- ?? cmponent ???X assembly
      FOR r4 IN c4 (p_component_item_id, p_organization_id)
      LOOP
         v_assembly_item_id := r4.assembly_item_id;

         SELECT COUNT (*)
           INTO v_count
           FROM bom_structures_b bbo
     INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
          WHERE 1 = 1
            AND bic.component_item_id = v_assembly_item_id
            AND bbo.organization_id   = p_organization_id;

         IF v_count > 0
         THEN
            v_assembly_item_id := get_whereuse (v_assembly_item_id, p_organization_id);
         END IF;
         IF v_assembly_item_id <> p_component_item_id THEN
            GOTO substitute;
         END IF;
      END LOOP;
      <>
      IF v_assembly_item_id = p_component_item_id
      THEN                                                                   --
         FOR r4 IN c4s (p_component_item_id, p_organization_id)
         LOOP
            v_assembly_item_id := r4.assembly_item_id;

            SELECT COUNT (*)
              INTO v_count
              FROM bom_structures_b bbo
        INNER JOIN bom_components_b bic ON bbo.common_bill_sequence_id = bic.bill_sequence_id
             WHERE bic.component_item_id = v_assembly_item_id
               AND bbo.organization_id   = p_organization_id;

            IF v_count > 0
            THEN
               v_assembly_item_id := get_whereuse(v_assembly_item_id, p_organization_id);
            END IF;

          IF v_assembly_item_id <> p_component_item_id THEN
            GOTO end_substitute;
          END IF;

          END LOOP;
      END IF;
      <>
-- FND_FILE.PUT_LINE(FND_FILE.OUTPUT , 'LOOP-4: ' || 'Y' ) ;
      RETURN v_assembly_item_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN v_assembly_item_id;
   END get_whereuse;
END XXAPSP0058_PKG;