APPS.ENI_DBI_RVA_PKG SQL Statements

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

Line: 11

  l_select_stmt         VARCHAR2(32000);

Line: 13

  l_inner_select_stmt   VARCHAR2(32000);

Line: 15

  l_union_select_stmt   VARCHAR2(32000);

Line: 38

  l_cust_flag       NUMBER; -- 0 for customer and 1 for no customer selected

Line: 152

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

Line: 158

        l_cust_flag := 0; -- customer selected

Line: 190

    l_select_stmt := '
 SELECT org.name                    VIEWBY
      , org.organization_id         VIEWBYID
      , NULL                        ENI_ATTRIBUTE3 -- drill across url
      , NULL                        ENI_ATTRIBUTE4 -- item description
      ' || l_measures || '
      , NULL ENI_ATTRIBUTE5
      , NULL ENI_ATTRIBUTE6
   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: 205

    l_inner_select_stmt := '
         SELECT fact.inv_org_id     INV_ORG_ID';

Line: 207

    l_union_select_stmt := '
         SELECT inv_org_id          INV_ORG_ID';

Line: 221

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

Line: 235

    l_inner_select_stmt := '
         SELECT fact.master_item_id ITEM_ID';

Line: 239

    l_union_select_stmt := '
         SELECT item_id             ITEM_ID';

Line: 258

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

Line: 270

    l_inner_select_stmt := '
         SELECT fact.customer_id    CUSTOMER_ID';

Line: 272

    l_union_select_stmt := '
         SELECT customer_id         CUSTOMER_ID';

Line: 285

    l_select_stmt := '
 SELECT eni_vbh.value               VIEWBY
      , eni_vbh.id                  VIEWBYID
      , DECODE(eni_vbh.leaf_node_flag, ''Y'',
            ''pFunctionName=ENI_DBI_RVA_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
            ''pFunctionName=ENI_DBI_RVA_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
        ENI_ATTRIBUTE3              -- drill across url
      , NULL                        ENI_ATTRIBUTE4 -- item description
      '||l_measures||l_open_urls||'
   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: 302

        l_inner_select_stmt := '
         SELECT eni_cat.parent_id               ITEM_CATEGORY_ID';

Line: 307

        l_inner_select_stmt := '
         SELECT eni_cat.imm_child_id            ITEM_CATEGORY_ID';

Line: 312

    l_union_select_stmt := '
         SELECT item_category_id                ITEM_CATEGORY_ID';

Line: 338

            l_inner_select_stmt := '
                SELECT  fact.parent_id  ITEM_CATEGORY_ID';

Line: 343

            l_inner_select_stmt := '
                SELECT  fact.inv_org_id INV_ORG_ID';

Line: 416

    THEN l_inner_sql := l_union_select_stmt||'
      , sum(curr_return)                    CURR_RETURN
      , sum(prev_return)                    PREV_RETURN
      , sum(curr_ship)                      CURR_SHIP
      , sum(prev_ship)                      PREV_SHIP
      , sum(lines_cnt)                      LINES_CNT
      , sum(return_qty)                     RETURN_QTY
       FROM ('||l_inner_select_stmt||'
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               fact.returned_amt_'||l_curr_suffix||', 0))       CURR_RETURN
      , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
               fact.returned_amt_'||l_curr_suffix||', 0))       PREV_RETURN
      , 0                                                       CURR_SHIP
      , 0                                                       PREV_SHIP
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               fact.lines_cnt, 0))                              LINES_CNT
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               fact.returned_qty, 0))                           RETURN_QTY
       FROM ISC_DBI_CFM_007_MV  fact
          , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
      WHERE fact.time_id = cal.time_id
        AND fact.period_type_id = cal.period_type_id
        AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
        AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
        AND fact.customer_flag = :ENI_CUST_FLAG
        AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG
        AND fact.return_reason_flag = :ENI_REASON_FLAG'
        --||l_org_where
        ||l_prod_cat_where
        ||l_prod_where
        ||l_cust_where
        ||l_ret_reason_where
        ||l_inner_group_by_stmt||'
      UNION ALL
        '||l_inner_select_stmt||'
      , 0                                                       CURR_RETURN
      , 0                                                       PREV_RETURN
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               fact.fulfilled_amt2_'||l_curr_suffix||', 0))     CURR_SHIP
      , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
               fact.fulfilled_amt2_'||l_curr_suffix||', 0))     PREV_SHIP
      , 0                                                       LINES_CNT
      , 0                                                       RETURN_QTY
       FROM ISC_DBI_CFM_002_MV  fact
          , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
      WHERE fact.time_id = cal.time_id
        AND fact.return_flag = 0
        AND fact.period_type_id = cal.period_type_id
        AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
        AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
        AND fact.customer_flag = :ENI_CUST_FLAG
        AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG'
        -- ||l_org_where
        ||l_prod_cat_where
        ||l_prod_where
        ||l_cust_where
        ||l_inner_group_by_stmt||')'
        ||l_union_group_by_stmt;

Line: 476

    l_inner_sql := l_inner_select_stmt||'
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               decode(fact.return_flag, 1,
                  fact.returned_amt_'||l_curr_suffix||', 0), 0))        CURR_RETURN
      , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
               decode(fact.return_flag, 1,
                  fact.returned_amt_'||l_curr_suffix||', 0), 0))        PREV_RETURN
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               decode(fact.return_flag, 0,
                  fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0))      CURR_SHIP
      , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
               decode(fact.return_flag, 0,
                  fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0))      PREV_SHIP
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               decode(fact.return_flag, 1,
                  fact.lines_cnt, 0), 0))                               LINES_CNT
      , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
               decode(fact.return_flag, 1,
                  fact.returned_qty, 0), 0))                            RETURN_QTY
       FROM '||l_mv1||'    fact
          , FII_TIME_RPT_STRUCT   cal'||l_prod_cat_from||'
      WHERE fact.time_id = cal.time_id
        AND fact.period_type_id = cal.period_type_id
        AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
        AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)'
        ||l_flags_where
    --  ||l_org_where
        ||l_prod_cat_where
        ||l_prod_where
        ||l_cust_where
        ||l_inner_group_by_stmt;

Line: 510

  l_stmt := l_select_stmt||'
    c.curr_return                                   ENI_MEASURE1 -- return value
  , (c.curr_return - c.prev_return)
      / decode(c.prev_return, 0, NULL,
           abs(c.prev_return)) * 100                ENI_MEASURE2 -- return value change
  , c.curr_return
      / decode(c.curr_ship, 0, NULL,
           c.curr_ship) * 100                       ENI_MEASURE3 -- return rate
  , c.curr_return
      / decode(c.curr_ship, 0, NULL,
           c.curr_ship) * 100 -
    c.prev_return
      / decode(c.prev_ship, 0, NULL,
           c.prev_ship) * 100                       ENI_MEASURE4 -- return rate change
  , c.lines_cnt                                     ENI_MEASURE5 -- past due lines
  , sum(c.curr_return) over ()                      ENI_MEASURE6 -- gd total return value
  , (sum(c.curr_return) over () - sum(c.prev_return) over ())
      / decode(sum(c.prev_return) over (), 0, NULL,
           abs(sum(c.prev_return) over ())) * 100   ENI_MEASURE7 -- gd total return change
  , sum(c.curr_return) over ()
      / decode(sum(c.curr_ship) over (), 0, NULL,
           sum(c.curr_ship) over ()) * 100          ENI_MEASURE8 -- gd total return rate
  , sum(c.curr_return) over ()
      / decode(sum(c.curr_ship) over (), 0, NULL,
           sum(c.curr_ship) over ()) * 100 -
    sum(c.prev_return) over()
      / decode(sum(c.prev_ship) over (), 0, NULL,
           sum(c.prev_ship) over ()) * 100          ENI_MEASURE9 -- gd total return rate change
  , sum(c.lines_cnt) over ()                        ENI_MEASURE10   -- gd return lines
   FROM ('||l_inner_sql||')  c)
  WHERE ENI_MEASURE1 <> 0
     OR ENI_MEASURE2 IS NOT NULL
     OR ENI_MEASURE3 IS NOT NULL
     OR ENI_MEASURE4 IS NOT NULL
     OR ENI_MEASURE5 <> 0)  a,'
    ||l_where_stmt;