APPS.ENI_DBI_CFM_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 10

    l_select_stmt           VARCHAR2(10000);

Line: 11

    l_union_select_stmt     VARCHAR2(10000);

Line: 13

    l_inner_select_stmt     VARCHAR2(10000);

Line: 98

        l_select_stmt := '
      SELECT  items.value       VIEWBY
            , NULL              ENI_ATTRIBUTE3 -- drill across url
            , items.description ENI_ATTRIBUTE4 -- item description
            -- , mtl.unit_of_measure ENI_ATTRIBUTE_5 -- item uom'
            || l_measures ||'
      FROM
        ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, item_id) ) - 1 rnk
                 , item_id'
                 || l_measures ||'
          FROM
          ( SELECT c.item_id ';

Line: 111

        l_inner_select_stmt := ' SELECT fact.master_item_id ITEM_ID';

Line: 112

        l_union_select_stmt := ' SELECT item_id  ITEM_ID';

Line: 122

        l_select_stmt := '
SELECT  org.name          VIEWBY
      , NULL              ENI_ATTRIBUTE3 -- drill across url
      , NULL              ENI_ATTRIBUTE4 -- item description
      -- ,  NULL  ISC_ATTRIBUTE_5 -- item uom'
      || l_measures ||'
FROM
  ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id) ) - 1 rnk
           , inv_org_id'
           || l_measures ||'
    FROM
    ( SELECT c.inv_org_id ';

Line: 135

        l_inner_select_stmt := ' SELECT fact.inv_org_id    INV_ORG_ID';

Line: 136

        l_union_select_stmt := ' SELECT inv_org_id      INV_ORG_ID';

Line: 147

        l_select_stmt := '
SELECT  cust.value        VIEWBY
      , NULL              ENI_ATTRIBUTE3 -- drill across url
      , NULL              ENI_ATTRIBUTE4 -- item description
      -- ,  NULL  ISC_ATTRIBUTE_5 -- item uom'
      || l_measures ||'
FROM
  ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, customer_id) ) - 1 rnk
           , customer_id'
           || l_measures ||'
    FROM
    ( SELECT c.customer_id ';

Line: 160

        l_inner_select_stmt := ' SELECT fact.customer_id CUSTOMER_ID';

Line: 161

        l_union_select_stmt := ' SELECT customer_id  CUSTOMER_ID';

Line: 170

        l_select_stmt := '
SELECT  eni_vbh.value     VIEWBY
      , eni_vbh.id        VIEWBYID
      , decode( eni_vbh.leaf_node_flag, ''Y''
              , '|| l_open_url1 ||'
              , '|| l_open_url2 ||' )  ENI_ATTRIBUTE3 -- drill across url
      , NULL              ENI_ATTRIBUTE4 -- item description
      -- ,  NULL  ISC_ATTRIBUTE_5 -- item uom'
      || l_measures ||'
FROM
    ( SELECT ( rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id) ) - 1 rnk
         , item_category_id'
         || l_measures ||'
      FROM
      ( SELECT c.item_category_id ';

Line: 187

            l_inner_select_stmt := ' SELECT eni_cat.parent_id  ITEM_CATEGORY_ID ';

Line: 189

            l_inner_select_stmt := ' SELECT eni_cat.imm_child_id ITEM_CATEGORY_ID ';

Line: 192

        l_union_select_stmt := ' SELECT item_category_id ITEM_CATEGORY_ID ';

Line: 212

            l_inner_select_stmt := ' SELECT fact.parent_id   ITEM_CATEGORY_ID ';

Line: 303

                                WHEN l_vb_cust THEN 0 -- customers selected
                                ELSE 1                -- all customers & not viewed by customer
                            END;

Line: 308

            l_cust_flag := 0; -- customer selected

Line: 312

  l_stmt := l_select_stmt || '
    , c.curr_booked_qty                                 ENI_MEASURE1  -- book qty
    , c.curr_booked_value                               ENI_MEASURE2  -- book
    , ( c.curr_booked_value-c.prev_booked_value)
    / decode( c.prev_booked_value, 0, NULL
            , abs(c.prev_booked_value)) * 100           ENI_MEASURE3  -- book change
    , c.curr_fulfill_qty                                ENI_MEASURE4  -- fulf qty
    , c.curr_fulfill_value                              ENI_MEASURE5  -- fulf
    , (c.curr_fulfill_value-c.prev_fulfill_value)
    / decode( c.prev_fulfill_value, 0, NULL
            , abs(c.prev_fulfill_value)) * 100          ENI_MEASURE6  -- fulf change
    , c.curr_booked_value
    / decode( c.curr_fulfill_value, 0, NULL
            , c.curr_fulfill_value)                     ENI_MEASURE7  -- book to fulf r
    , c.curr_booked_value
    / decode( c.curr_fulfill_value, 0, NULL
            , c.curr_fulfill_value)
    - c.prev_booked_value
    / decode( c.prev_fulfill_value, 0, NULL
            , c.prev_fulfill_value)                     ENI_MEASURE8  -- book to fulf r change
    , sum(c.curr_booked_value) over ()                  ENI_MEASURE9  -- gd total book
    , ( sum(c.curr_booked_value) over () - sum(c.prev_booked_value) over () )
    / decode( sum( c.prev_booked_value ) over (), 0, NULL
            , abs( sum(c.prev_booked_value) over () ) )
    * 100                                               ENI_MEASURE10 -- gd total book change
    , sum(c.curr_fulfill_value) over ()                 ENI_MEASURE11 -- gd total fulf
    , (sum(c.curr_fulfill_value) over () - sum(c.prev_fulfill_value) over ())
    / decode( sum(c.prev_fulfill_value) over (), 0, NULL
            , abs( sum(c.prev_fulfill_value) over () ) )
    * 100                                               ENI_MEASURE12 -- gd total fulf change
    , sum(c.curr_booked_value) over ()
    / decode( sum(c.curr_fulfill_value) over (), 0, NULL
            , sum(c.curr_fulfill_value) over () )       ENI_MEASURE13 -- gd total book to fulf r
    , sum(c.curr_booked_value) over ()
    / decode( sum(c.curr_fulfill_value) over (), 0, NULL
            , sum(c.curr_fulfill_value) over () )
    - sum(c.prev_booked_value) over ()
    / decode( sum(c.prev_fulfill_value) over (), 0, NULL
            , sum(c.prev_fulfill_value) over () )       ENI_MEASURE14 -- gd total book to fulf r change
    , c.curr_booked_value                               ENI_MEASURE15 -- KPI book
    , c.prev_booked_value                               ENI_MEASURE16 -- KPI book prior
    , c.curr_fulfill_value                              ENI_MEASURE17 -- KPI fulf
    , c.prev_fulfill_value                              ENI_MEASURE18 -- KPI fulf prior
    , c.curr_booked_value
    / decode( c.curr_fulfill_value, 0, NULL
            , c.curr_fulfill_value )                    ENI_MEASURE19 -- KPI book to fulf r
    , c.prev_booked_value
    / decode( c.prev_fulfill_value, 0, NULL
            , c.prev_fulfill_value )                    ENI_MEASURE20 -- KPI book to fulf r prior
    , sum(c.curr_booked_value) over ()                  ENI_MEASURE21 -- KPI gd total book value
    , sum(c.prev_booked_value) over ()                  ENI_MEASURE22 -- KPI gd total book prior
    , c.prev_booked_value
    / decode( c.prev_fulfill_value, 0, NULL
            , c.prev_fulfill_value )                    ENI_MEASURE24 -- KPI book to fulf r prior
    , sum(c.prev_booked_value) over ()
    / decode( sum(c.prev_fulfill_value) over (), 0, NULL
            , sum(c.prev_fulfill_value) over () )       ENI_MEASURE25 -- KPI gd total book to fulf r prior
    FROM ('||l_union_select_stmt||'
        , sum(curr_booked_qty)    CURR_BOOKED_QTY
        , sum(curr_booked_value)  CURR_BOOKED_VALUE
        , sum(prev_booked_value)  PREV_BOOKED_VALUE
        , sum(curr_fulfill_qty)   CURR_FULFILL_QTY
        , sum(curr_fulfill_value) CURR_FULFILL_VALUE
        , sum(prev_fulfill_value) PREV_FULFILL_VALUE
    FROM ('||l_inner_select_stmt||'
        , fact.inv_org_id                                   INV_ORG
        , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
                , fact.booked_qty, 0)                       CURR_BOOKED_QTY
        , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
                , fact.booked_amt_'||l_curr_suffix||', 0)   CURR_BOOKED_VALUE
        , decode( cal.report_date, &BIS_PREVIOUS_ASOF_DATE
                , fact.booked_amt_'||l_curr_suffix||', 0)   PREV_BOOKED_VALUE
        , 0                                                 CURR_FULFILL_QTY
        , 0                                                 CURR_FULFILL_VALUE
        , 0                                                 PREV_FULFILL_VALUE
    FROM '||l_mv1||'   fact
        , FII_TIME_RPT_STRUCT_V  cal'
        ||l_prod_cat_from||'
    WHERE fact.time_id = cal.time_id'
      || l_flags_where||'
      AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
      AND cal.period_type_id = fact.period_type_id
      AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
      || l_prod_cat_where
      || l_prod_where
      || l_cust_where||'
  UNION ALL
        '||l_inner_select_stmt||'
        , fact.inv_org_id                                       INV_ORG
        , 0                                                     CURR_BOOKED_QTY
        , 0                                                     CURR_BOOKED_VALUE
        , 0                                                     PREV_BOOKED_VALUE
        , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
                , fact.fulfilled_qty, 0)                        CURR_FULFILL_QTY
        , decode( cal.report_date, &BIS_CURRENT_ASOF_DATE
                , fact.fulfilled_amt_'||l_curr_suffix||', 0)    CURR_FULFILL_VALUE
        , decode( cal.report_date, &BIS_PREVIOUS_ASOF_DATE
                , fact.fulfilled_amt_'||l_curr_suffix||', 0)    PREV_FULFILL_VALUE
    FROM '||l_mv2||'   fact
      , FII_TIME_RPT_STRUCT_V  cal'
      ||l_prod_cat_from||'
   WHERE fact.time_id = cal.time_id'
     || l_flags_where||'
     AND fact.return_flag = 0
     AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
     AND cal.period_type_id = fact.period_type_id
     AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
     || l_prod_cat_where
     || l_prod_where
     || l_cust_where
     || ')'
   -- WHERE '||l_inv_org_where
     ||l_union_group_by_stmt||') c) ) a'
     ||l_where_stmt;