APPS-ORACLE.RU

  procedure XX_TEST( itemtype  IN  VARCHAR2
                   , itemkey   IN  VARCHAR2
                   , actid     IN  NUMBER
                   , funcmode  IN  VARCHAR2
                   , resultout OUT VARCHAR2
                   )
  is
    l_proj_element_id NUMBER;
    --
    l_marker NUMBER; -- служит для обозначения шага
  begin

    l_marker := 10;

    xxdebug('XX_TEST');
    xxdebug('itemtype = '||itemtype  ,'itemkey = ' ||itemkey);
    xxdebug('actid = '   ||actid     ,'funcmode = '||funcmode);
    xxdebug('--');

    -- Проверяем статус workflow
    IF funcmode != WF_ENGINE.ENG_RUN THEN
      wf_log_pkg.string(5,'XX_TEST','Not in RUN mode');
      xxdebug_error('funcmode = '||funcmode);
      RETURN;
    END IF;

    -- далее основная логика процедуры

    -- получаем ID позиции поставки
    l_proj_element_id := WF_ENGINE.GetItemAttrText ( itemtype => itemtype
                                                   , itemkey  => itemkey
                                                   , aname    => 'PROJ_ELEMENT_ID' );

    xxdebug('proj_element_id='||l_proj_element_id);
    l_marker := 20;

    -- устанавливаем нового владельца
    WF_ENGINE.SetItemAttrText(itemtype, itemkey, 'NEW_DELIV_OWNER_ID', 141);

    resultout := WF_ENGINE.ENG_COMPLETED;
    RETURN;

  EXCEPTION
    WHEN OTHERS THEN
      xxdebug_error(SQLERRM, 'marker = '||l_marker);
      WF_CORE.context ( g_pkg_name
                      , 'XX_TEST'
                      , itemtype
                      , itemkey
                      , TO_CHAR(actid)
                      , funcmode
                      , SQLERRM
                      , 'marker = '||l_marker );
      RAISE;
  END;

grant debug connect session to user;

SELECT msi.organization_id,
       msi.inventory_item_id,
       msi.segment1,
       msi.description,
       mic.category_set_name,
       mic.segment1,
       mic.segment2
FROM mtl_system_items_vl   msi,
     mtl_org_assign_v      moa,
     mtl_item_categories_v mic
WHERE moa.organization_id = msi.organization_id
      AND moa.inventory_item_id = msi.inventory_item_id
      AND mic.organization_id = msi.organization_id
      AND mic.inventory_item_id = msi.inventory_item_id
      AND moa.assigned_flag = 'Y'
order by msi.organization_id, msi.inventory_item_id;

WFLOAD username/password 0 Y DOWNLOAD XX_TEST.wft XX_TEST

WFLOAD username/password 0 Y UPGRADE XX_TEST.wft

   update WF_ITEM_TYPES_TL
   set DISPLAY_NAME = RTRIM(DISPLAY_NAME);

   update WF_ITEM_TYPES_TL
   set DESCRIPTION = RTRIM(DESCRIPTION);

PERCENTILE_DISC(expr) WITHIN GROUP
   (ORDER BY expr [ DESC | ASC ])
   [ OVER (query_partition_clause) ]

with t as
(select 1 id, 10 val  from dual
  union all
 select 2 id, 20 val  from dual
  union all
 select 3 id, 30 val  from dual
  union all
 select 4 id, 50 val  from dual
  union all
 select 5 id, 70 val  from dual
)
select  id,
        val,
        cume_dist() over (order by val) "Cume_Dist",
        percentile_disc(0.50) within group (order by val) over() "percentile_disc"
from t
order by val;

        ID        VAL  Cume_Dist percentile_disc
---------- ---------- ---------- ---------------
         1         10        0,2              30
         2         20        0,4              30
         3         30        0,6              30
         4         50        0,8              30
         5         70          1              30

CUME_DIST( ) OVER ( [ query_partition_clause ] order_by_clause )

with t as
(
 select 1 id, 'Сидоров' s from dual
  union all
 select 2, 'Cидoров' from dual
  union all
 select 3, 'Пирогов' from dual
)
select * from t t0
where soundex(t0.s) is not null;

        ID S
---------- -------
         2 Cидoров

with t as
(
 select 1 id, 'Сидоров' s from dual
  union all
 select 2, 'Cидoров' from dual
  union all
 select 3, 'Пирогов' from dual
)
select * from t t0, t t1
where UTL_MATCH.JARO_WINKLER_SIMILARITY(t0.s,t1.s)>=70
      and soundex(t0.s) is null
      and soundex(t1.s) is not null
      and t0.id<>t1.id;

        ID S               ID S
---------- ------- ---------- -------
         1 Сидоров          2 Cидoров
         3 Пирогов          2 Cидoров

with t as
(
 select 1 id, 'Сидоров' s from dual
  union all
 select 2, 'Cидoров' from dual
  union all
 select 3, 'Пирогов' from dual
)
select * from t t0, t t1
where translate(t1.s,'AaBbCcEegHKkMmOoPpTuXxy','АаВьСсЕедНКкМтОоРрТиХху')=t0.s
      and t0.id<>t1.id;

        ID S               ID S
---------- ------- ---------- -------
         1 Сидоров          2 Cидoров