eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OPI_DBI_INV_RPT_PKG SQL Statements

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

Line: 116


/* construct the list of measures to be appended to the select clause of the dynamic SQL */
  l_col_rec.column_name := 'total_inv_val_' || l_cur_suffix;

Line: 140


    l_org_where := ' and fact.operating_unit_id in (select organization_id from per_organization_list where security_profile_id= nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) ) ';

Line: 146


    l_in_org_where := ' and insv.operating_unit_id in (select organization_id from per_organization_list where security_profile_id= nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) ) ';

Line: 189


/* Outer select clause of Inventory Turns table portlet query */

FUNCTION get_turns_sel_clause (p_view_by_col_name in VARCHAR2)
    RETURN VARCHAR2
IS

    l_sel_clause varchar2(7000);

Line: 200


  'select v.value VIEWBY, v.value OPI_ATTRIBUTE1, oset.OPI_MEASURE1 OPI_MEASURE1,
            oset.OPI_MEASURE2 OPI_MEASURE2, oset.OPI_MEASURE3 OPI_MEASURE3,
            oset.OPI_MEASURE4 OPI_MEASURE4, oset.OPI_MEASURE5 OPI_MEASURE5,
            oset.OPI_MEASURE6 OPI_MEASURE6, oset.OPI_MEASURE7 OPI_MEASURE7,
            oset.OPI_MEASURE8 OPI_MEASURE8, oset.OPI_MEASURE9 OPI_MEASURE9,
            oset.OPI_MEASURE10 OPI_MEASURE10, oset.OPI_MEASURE11 OPI_MEASURE11,
            oset.OPI_MEASURE12 OPI_MEASURE12, oset.OPI_MEASURE13 OPI_MEASURE13
            from
     (select (rank() over
                (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col_name || ')) - 1 rnk,'
        || p_view_by_col_name || ',
           OPI_MEASURE1, OPI_MEASURE2, OPI_MEASURE3, OPI_MEASURE4,
           OPI_MEASURE5, OPI_MEASURE6, OPI_MEASURE7, OPI_MEASURE8,
           OPI_MEASURE9, OPI_MEASURE10, OPI_MEASURE11, OPI_MEASURE12, OPI_MEASURE13
      from
     (select ' || p_view_by_col_name || ', ' || p_view_by_col_name || ' VIEW_BY,
           c_total_inv_val OPI_MEASURE1, c_cogs_val OPI_MEASURE2,
           round(decode(sign(p_total_inv_val), 0, NULL, -1, NULL, p_cogs_val/p_total_inv_val),5) OPI_MEASURE3,
           round(decode(sign(c_total_inv_val), 0, NULL, -1, NULL, c_cogs_val/c_total_inv_val),5) OPI_MEASURE4,
           round((decode(sign(c_total_inv_val), 0, NULL, -1, NULL, c_cogs_val/c_total_inv_val) - decode(p_total_inv_val, 0, NULL, p_cogs_val/p_total_inv_val)),5) OPI_MEASURE5,
           c_total_inv_val_total OPI_MEASURE6, c_cogs_val_total OPI_MEASURE7,
           decode(sign(c_total_inv_val_total), 0, NULL,-1,NULL,
           (c_cogs_val_total / c_total_inv_val_total)) OPI_MEASURE8,
           decode(sign(c_total_inv_val_total), 0, NULL, -1, NULL,
           decode(sign(p_total_inv_val_total), 0, NULL, -1, NULL,
           ((c_cogs_val_total / c_total_inv_val_total) -
           (p_cogs_val_total / p_total_inv_val_total)))) OPI_MEASURE9,
           round(decode(sign(c_total_inv_val), 0, NULL, -1, NULL, c_cogs_val/c_total_inv_val),5) OPI_MEASURE10,
           round(decode(sign(p_total_inv_val), 0, NULL, -1, NULL, p_cogs_val/p_total_inv_val),5) OPI_MEASURE11,
           decode(sign(c_total_inv_val_total), 0, NULL,-1,NULL,
           (c_cogs_val_total / c_total_inv_val_total)) OPI_MEASURE12,
           decode(sign(p_total_inv_val_total), 0, NULL,-1,NULL,
           (p_cogs_val_total / p_total_inv_val_total)) OPI_MEASURE13 from
           (select c.' || p_view_by_col_name || ' ,
            c.total_inv_val c_total_inv_val , c.cogs_val c_cogs_val ,
            p.total_inv_val p_total_inv_val , p.cogs_val p_cogs_val ,
            sum(c.total_inv_val) over () c_total_inv_val_total,
            sum(c.cogs_val) over () c_cogs_val_total,
            sum(p.total_inv_val) over () p_total_inv_val_total,
            sum(p.cogs_val) over () p_cogs_val_total';

Line: 294


  l_query := '(select ' || p_view_by_col_name ||', start_date_'|| p_xtd || ',
        sum(cogs_val_'|| p_curr || ') * 365 / ' || l_partial_weight || ' cogs_val,
        (sum(weight * inv_balance_' || p_curr || ') -
           (select distinct
             last_value(weight * inv_balance_' || p_curr || ')
            over (partition by organization_id, start_date_'|| p_xtd || '
            order by transaction_date asc
                range between unbounded preceding and unbounded following) -
             last_value(inv_balance_' || p_curr || ' * ( &BIS_CURRENT_ASOF_DATE  - transaction_date + 1))
            over (partition by organization_id, start_date_'|| p_xtd || '
            order by transaction_date asc
                range between unbounded preceding and unbounded following )
                    from ' || p_fact_name || ' insv
                    where
            fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
            and insv.organization_id = fact.organization_id
                ' || p_in_where_clause || '
                    and insv.transaction_date <= &BIS_CURRENT_ASOF_DATE))
            / ' || l_partial_weight || '  total_inv_val
                from ' || p_fact_name || ' fact
            where
(exists
(SELECT 1
FROM ORG_ACCESS o
WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
and o.organization_id = fact.organization_id)
or exists
(SELECT 1
FROM mtl_parameters org
where org.organization_id = fact.organization_id
and NOT EXISTS
(select 1
from org_access ora
where org.organization_id = ora.organization_id)))
             and    transaction_date >= &BIS_CURRENT_EFFECTIVE_START_DATE
             and    transaction_date <= &BIS_CURRENT_ASOF_DATE
        ' || p_where_clause || '
                group by fact.' || p_view_by_col_name || ', start_date_'|| p_xtd || p_kpi_in_group_by || '
--          having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
                      ) c,
        (select ' || p_view_by_col_name ||', start_date_'|| p_xtd || ',
                     sum(cogs_val_'|| p_curr || ') * 365 / ' || l_prev_partial_weight || '  cogs_val,
                     (sum(weight * inv_balance_' || p_curr || ') -
                     (select distinct
                     last_value(weight * inv_balance_' || p_curr || ')
                        over (partition by organization_id, start_date_'|| p_xtd || '
                        order by transaction_date asc
                        range between unbounded preceding and unbounded following) -
                     last_value(inv_balance_' || p_curr || ' * (&BIS_PREVIOUS_ASOF_DATE - transaction_date + 1))
                        over (partition by organization_id, start_date_'|| p_xtd || '
                        order by transaction_date asc
                        range between unbounded preceding and unbounded following )
                    from ' || p_fact_name || ' insv
                    where
                        fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
                        and insv.organization_id = fact.organization_id
                        ' || p_in_where_clause || '
                        and insv.transaction_date <= &BIS_PREVIOUS_ASOF_DATE))/ ' || l_prev_partial_weight || '  total_inv_val
                from ' || p_fact_name || ' fact
                where
(exists
(SELECT 1
FROM ORG_ACCESS o
WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
and o.organization_id = fact.organization_id)
or exists
(SELECT 1
FROM mtl_parameters org
where org.organization_id = fact.organization_id
and NOT EXISTS
(select 1
from org_access ora
where org.organization_id = ora.organization_id)))
                 and    transaction_date >= &BIS_PREVIOUS_EFFECTIVE_START_DATE
                 and    transaction_date <= &BIS_PREVIOUS_ASOF_DATE
                ' || p_where_clause || '
                group by fact.' || p_view_by_col_name || ', start_date_'|| p_xtd || p_kpi_in_group_by || '
--                having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
                      ) p
            where c.' || p_view_by_col_name || ' = p.' || p_view_by_col_name || ' (+) )' || p_kpi_group_by || ')) oset,
         ' || l_view_by_table || ' v
            where oset.' || p_view_by_col_name || ' = v.' || p_view_by_col_id;

Line: 437


/* construct the list of measures to be appended to the select clause of the dynamic SQL */
  l_col_rec.column_name := 'cogs_val_' || l_cur_suffix;

Line: 461


    l_org_where := ' and (fact.operating_unit_id is null or fact.operating_unit_id in (select organization_id
from per_organization_list where security_profile_id = nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) )) ';

Line: 468


    l_in_org_where := ' and insv.operating_unit_id in (select organization_id from per_organization_list where
 security_profile_id = nvl(fnd_profile.value(''XLA_MO_SECURITY_PROFILE_LEVEL''), -1) ) ' ;

Line: 518


/* Outer select clause of Inventory Turns Trend portlet query */
FUNCTION get_turns_trd_sel_clause
    RETURN VARCHAR2
IS

    l_sel_clause varchar2(4000);

Line: 528


    'select cal.name VIEWBY,
      cal.name OPI_ATTRIBUTE1,
          c.avg_daily_inv OPI_MEASURE1,
          c.annualized_cogs OPI_MEASURE2,
      decode(p.avg_daily_inv, 0, NULL, p.annualized_cogs/p.avg_daily_inv) OPI_MEASURE3,
      decode(c.avg_daily_inv, 0, NULL, c.annualized_cogs/c.avg_daily_inv) OPI_MEASURE4,
      (decode(c.avg_daily_inv, 0, NULL, c.annualized_cogs/c.avg_daily_inv)
      - decode(p.avg_daily_inv, 0, NULL, p.annualized_cogs/p.avg_daily_inv)) OPI_MEASURE5 ';

Line: 586


'(select start_date_' || p_xtd || ' period_name, sum(cogs_val) annualized_cogs, sum(total_inv_val) avg_daily_inv, id from
    (select organization_id, start_date_' || p_xtd || ' ,
                sum(cogs_val_' || p_curr || ') * 365 / sum(weight) cogs_val,
                sum(weight * inv_balance_' || p_curr || ') / sum(weight) total_inv_val,
        decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
                from ' || p_fact_name || ' fact
                where
(exists
(SELECT 1
FROM ORG_ACCESS o
WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
and o.organization_id = fact.organization_id)
or exists
(SELECT 1
FROM mtl_parameters org
where org.organization_id = fact.organization_id
and NOT EXISTS
(select 1
from org_access ora
where org.organization_id = ora.organization_id)))
                and     transaction_date >= &BIS_CURRENT_REPORT_START_DATE
                and     transaction_date < &BIS_CURRENT_EFFECTIVE_START_DATE
                ' || p_where_clause || '
                group by fact.organization_id, start_date_'|| p_xtd || ',
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')
--                having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
    union all
    select organization_id, start_date_'|| p_xtd || ',
                sum(cogs_val_'|| p_curr || ') * 365 /
        ' || l_partial_weight || ' cogs_val,
                (sum(weight * inv_balance_' || p_curr || ') -
                   (select distinct
                     last_value(weight * inv_balance_' || p_curr || ')
                        over (partition by organization_id, start_date_'|| p_xtd || '
                        order by transaction_date asc
                        range between unbounded preceding and unbounded following) -
                     last_value(inv_balance_' || p_curr || ' * (&BIS_CURRENT_ASOF_DATE - transaction_date + 1))
                        over (partition by organization_id, start_date_'|| p_xtd || '
                        order by transaction_date asc
                        range between unbounded preceding and unbounded following )
                    from ' || p_fact_name || ' insv
                    where
                        fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
                        and insv.organization_id = fact.organization_id
                        ' || p_in_where_clause || '
                        and insv.transaction_date <= &BIS_CURRENT_ASOF_DATE)) /
        ' || l_partial_weight || ' total_inv_val,
        decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
                from ' || p_fact_name || ' fact
                where
(exists
(SELECT 1
FROM ORG_ACCESS o
WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
and o.organization_id = fact.organization_id)
or exists
(SELECT 1
FROM mtl_parameters org
where org.organization_id = fact.organization_id
and NOT EXISTS
(select 1
from org_access ora
where org.organization_id = ora.organization_id)))
                 and    transaction_date >= &BIS_CURRENT_EFFECTIVE_START_DATE
                 and    transaction_date <= &BIS_CURRENT_ASOF_DATE
                ' || p_where_clause || '
                group by fact.organization_id, start_date_'|| p_xtd || ',
        decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
        round((&BIS_CURRENT_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || '))
--                having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
                     )
    group by start_date_' ||  p_xtd || ', id order by id asc) c,
(select start_date_' || p_xtd || ' period_name, sum(cogs_val) annualized_cogs, sum(total_inv_val) avg_daily_inv, id from
        (select organization_id, start_date_' || p_xtd || ',
                sum(cogs_val_' || p_curr || ') * 365 / sum(weight) cogs_val,
                sum(weight * inv_balance_' || p_curr || ') / sum(weight) total_inv_val,
        decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
        round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
        round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
                from ' || p_fact_name || ' fact
                where
(exists
(SELECT 1
FROM ORG_ACCESS o
WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
and o.organization_id = fact.organization_id)
or exists
(SELECT 1
FROM mtl_parameters org
where org.organization_id = fact.organization_id
and NOT EXISTS
(select 1
from org_access ora
where org.organization_id = ora.organization_id)))
                and     transaction_date >= &BIS_PREVIOUS_REPORT_START_DATE
                and     transaction_date < &BIS_PREVIOUS_EFFECTIVE_START_DATE
                ' || p_where_clause || '
                group by fact.organization_id, start_date_'|| p_xtd || ',
                round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')
--                having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
        union all
        select organization_id, start_date_'|| p_xtd || ',
                sum(cogs_val_'|| p_curr || ') * 365 /
        ' || l_prev_partial_weight || ' cogs_val,
                (sum(weight * inv_balance_' || p_curr || ') -
                   (select distinct
                     last_value(weight * inv_balance_' || p_curr || ')
                        over (partition by organization_id, start_date_'|| p_xtd || '
                        order by transaction_date asc
                        range between unbounded preceding and unbounded following) -
                     last_value(inv_balance_' || p_curr || ' * (&BIS_PREVIOUS_ASOF_DATE - transaction_date + 1))
                        over (partition by organization_id, start_date_'|| p_xtd || '
                        order by transaction_date asc
                        range between unbounded preceding and unbounded following )
                    from ' || p_fact_name || ' insv
                    where
                        fact.start_date_'|| p_xtd || ' = insv.start_date_'|| p_xtd || '
                        and insv.organization_id = fact.organization_id
                        ' || p_in_where_clause || '
                        and insv.transaction_date <= &BIS_PREVIOUS_ASOF_DATE)) /
        ' || l_prev_partial_weight || ' total_inv_val,
        decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
        round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
        round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ')) id
                from ' || p_fact_name || ' fact
                where
(exists
(SELECT 1
FROM ORG_ACCESS o
WHERE o.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND o.RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID
and o.organization_id = fact.organization_id)
or exists
(SELECT 1
FROM mtl_parameters org
where org.organization_id = fact.organization_id
and NOT EXISTS
(select 1
from org_access ora
where org.organization_id = ora.organization_id)))
                 and    transaction_date >= &BIS_PREVIOUS_EFFECTIVE_START_DATE
                 and    transaction_date <= &BIS_PREVIOUS_ASOF_DATE
                ' || p_where_clause || '
                group by fact.organization_id, start_date_'|| p_xtd || ',
        decode (fact.start_date_' || p_xtd || ', ''' || l_global_start_date || ''',
        round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || ') + 1,
        round((&BIS_PREVIOUS_EFFECTIVE_START_DATE - fact.start_date_' || p_xtd || ')/' || l_span || '))
--                having sum(weight * inv_balance_' || p_curr || ') <> 0 and sum(cogs_val_'|| p_curr || ') is not null
                     )
        group by start_date_' ||  p_xtd || ', id order by id asc) p,
 ' || get_calendar_table(p_xtd) || ' cal
     where
      c.id = p.id(+)
      and cal.start_date = c.period_name
     order by c.id desc';

Line: 773


  WHEN 'INV_ORG' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG'')) '
  WHEN 'PROD_CAT' THEN 'eni_item_vbh_nodes_v'
  WHEN 'INV_CAT' THEN 'eni_item_inv_cat_v'
  WHEN 'ITEM' THEN 'eni_item_org_v '
  ELSE ''
  END);