[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Select clause functions
****************************************/
/* Current Inventory Status */
-- Outer select clause
FUNCTION get_curr_inv_stat_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2;
Returns the select clause for the Current Inventory Expiration
Status (Table) report query.
Input
Outputs
1. l_sel_clause - Select clause of the report query
History
Date Author Action
07/13/05 Dinkar Gupta Wrote Function
*/
FUNCTION get_curr_inv_exp_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
-- {
l_sel_clause VARCHAR2 (32767);
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl => p_join_tbl);
'SELECT
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_description || ' OPI_ATTRIBUTE1,
' || l_uom || ' OPI_ATTRIBUTE2,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.raw_str
(p_str => 'c_expired_qty')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_expired_val')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.raw_str
(p_str => 'c_onhand_qty')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_onhand_val')
|| ' OPI_MEASURE6,
' || opi_dbi_rpt_util_pkg.percent_str
(p_numerator => 'c_expired_val',
p_denominator => 'c_onhand_val',
p_measure_name => 'OPI_MEASURE7') || ',
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_expired_val_total')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_onhand_val_total')
|| ' OPI_MEASURE9,
' || opi_dbi_rpt_util_pkg.percent_str
(p_numerator => 'c_expired_val_total',
p_denominator => 'c_onhand_val_total',
p_measure_name => 'OPI_MEASURE10');
Returns the select clause for the Inventory Days Onhand
Status (Table) report query.
Input
Outputs
1. l_sel_clause - Select clause of the report query
History
Date Author Action
07/13/05 Dinkar Gupta Wrote Function
*/
FUNCTION get_inv_doh_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
-- {
l_sel_clause VARCHAR2 (32767);
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl => p_join_tbl);
'SELECT /* outer query */
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_description || ' OPI_ATTRIBUTE1,
' || l_uom || ' OPI_ATTRIBUTE2,
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19
' || 'FROM
' || '(SELECT (rank () over /* rank clause */
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19
' || 'FROM
' || '(SELECT /* extra for paren_cnt = 5 */
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE16,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE17,
' || 'OPI_MEASURE18,
' || 'OPI_MEASURE19
' || 'FROM
' || '(SELECT /* days onhand computation */
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE13,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE14,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE15,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE16,
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str
(p_numerator => 'OPI_MEASURE3',
p_denominator => '(p_cons_daily_avg * 100)',
p_measure_name => 'OPI_MEASURE10') || ',
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str
(p_numerator => 'OPI_MEASURE4',
p_denominator => '(OPI_MEASURE9 * 100)',
p_measure_name => 'OPI_MEASURE11') || ',
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str
(p_numerator => 'OPI_MEASURE12',
p_denominator => '(OPI_MEASURE16 * 100)',
p_measure_name => 'OPI_MEASURE17') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str
(p_new_numerator => 'OPI_MEASURE4',
p_new_denominator => '(OPI_MEASURE9 * 100)',
p_old_numerator => 'OPI_MEASURE3',
p_old_denominator => '(p_cons_daily_avg * 100)',
p_measure_name => 'OPI_MEASURE18') || ',
' || opi_dbi_rpt_util_pkg.change_pct_str
(p_new_numerator => 'OPI_MEASURE12',
p_new_denominator => '(OPI_MEASURE16 * 100)',
p_old_numerator => 'p_onhand_val_total',
p_old_denominator => '(p_cons_daily_avg_total * 100)',
p_measure_name => 'OPI_MEASURE19') || '
' || 'FROM
' || '(SELECT /* basic measure computation */
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.raw_str
(p_str => 'c_onhand_qty')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'p_onhand_val')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_onhand_val')
|| ' OPI_MEASURE4,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_onhand_val_total')
|| ' OPI_MEASURE12,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_prod_usage_val')
|| ' OPI_MEASURE6,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_prod_usage_val_total')
|| ' OPI_MEASURE13,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_cogs_val')
|| ' OPI_MEASURE7,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_cogs_val_total')
|| ' OPI_MEASURE14,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str =>
opi_dbi_rpt_util_pkg.nvl_str
('c_prod_usage_val') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('c_cogs_val'))
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str =>
opi_dbi_rpt_util_pkg.nvl_str
('c_prod_usage_val_total') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('c_cogs_val_total'))
|| ' OPI_MEASURE15,
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str_basic
(p_numerator =>
opi_dbi_rpt_util_pkg.neg_str (
opi_dbi_rpt_util_pkg.nvl_str
('c_prod_usage_val') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('c_cogs_val')),
p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
p_measure_name => 'OPI_MEASURE9') || ',
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str_basic
(p_numerator =>
opi_dbi_rpt_util_pkg.neg_str (
opi_dbi_rpt_util_pkg.nvl_str
('c_prod_usage_val_total') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('c_cogs_val_total')),
p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
p_measure_name => 'OPI_MEASURE16') || ',
' || ' null OPI_MEASURE10,
' || ' null OPI_MEASURE11,
' || ' null OPI_MEASURE17,
' || ' null OPI_MEASURE18,
' || ' null OPI_MEASURE19,
' || ' p_onhand_val_total,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str =>
opi_dbi_rpt_util_pkg.nvl_str
('p_prod_usage_val') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('p_cogs_val'))
|| ' p_total_cons_val,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str =>
opi_dbi_rpt_util_pkg.nvl_str
('p_prod_usage_val_total') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('p_cogs_val_total'))
|| ' p_total_cons_val_total,
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str_basic
(p_numerator =>
opi_dbi_rpt_util_pkg.neg_str (
opi_dbi_rpt_util_pkg.nvl_str
('p_prod_usage_val') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('p_cogs_val')),
p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
p_measure_name => 'p_cons_daily_avg') || ',
-- not truly a percentage, so multiply denom by 100
' || opi_dbi_rpt_util_pkg.percent_str_basic
(p_numerator =>
opi_dbi_rpt_util_pkg.neg_str (
opi_dbi_rpt_util_pkg.nvl_str
('p_prod_usage_val_total') || ' + ' ||
opi_dbi_rpt_util_pkg.nvl_str
('p_cogs_val_total')),
p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
p_measure_name => 'p_cons_daily_avg_total');
' || ' (SELECT (substr (&ITEM+ENI_ITEM_ORG, 1, instr (&ITEM+ENI_ITEM_ORG, ''-'') - 1)) inventory_item_id FROM eni_oltp_item_star where id = &ITEM+ENI_ITEM_ORG) item_uom,
' || l_viewby_rank_clause;
select the grade_code from MTL_LOT_NUMBERS.
Also append the relevant join conditions to the where clause.
Inputs
1. p_params - table of parameters with which report was run.
2. p_query - query with placeholders
Outputs
1. p_query - query with MLN alias and join conditions.
History
Date Author Action
07/18/05 Dinkar Gupta Wrote Function
*/
PROCEDURE get_curr_inv_stat_mln
(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
p_query IN OUT NOCOPY VARCHAR2)
IS
-- {
l_join_mln BOOLEAN;
and select inv_category_id from ENI_OLTP_ITEM_STAR
Also append the relevant join conditions to the where clause.
Inputs
1. p_viewby - viewby dimension key.
2. p_query - query with placeholders
Outputs
1. p_query - query with ENI alias and join conditions.
History
Date Author Action
07/18/05 Dinkar Gupta Wrote Function
*/
PROCEDURE get_curr_inv_stat_eni
(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
p_query IN OUT NOCOPY VARCHAR2)
IS
-- {
l_join_eni BOOLEAN;
Returns the select clause for the Current Inventory
Status (Table) report query.
Input
Outputs
1. l_sel_clause - Select clause of the report query
History
Date Author Action
07/13/05 Dinkar Gupta Wrote Function
*/
FUNCTION get_curr_inv_stat_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
-- {
l_sel_clause VARCHAR2 (32767);
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl => p_join_tbl);
'SELECT /* outer select */
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| 'v2.unit_of_measure OPI_ATTRIBUTE1,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE5,
' || 'v3.unit_of_measure OPI_ATTRIBUTE2,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE6
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE6
' || 'FROM
' || '(SELECT /* measure computation */
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.raw_str
(p_str => 'c_primary_qty')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_primary_qty_total')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.raw_str
(p_str => 'c_secondary_qty')
|| ' OPI_MEASURE4,
' || opi_dbi_rpt_util_pkg.nvl_str
(p_str => 'c_secondary_qty_total')
|| ' OPI_MEASURE6
' || ' FROM ( /* OLTP select */
' || ' SELECT
' || ' msi.primary_uom_code,
' || ' msi.secondary_uom_code,
' || ' :OPI_CURR_INV_STAT_VIEWBY :OPI_CURR_INV_STAT_VIEWBY_ALIAS,
' || ' sum (fact.transaction_quantity) c_primary_qty,
' || ' sum (sum (fact.transaction_quantity)) over () c_primary_qty_total,
' || ' sum (fact.secondary_transaction_quantity) c_secondary_qty,
' || ' sum (sum (fact.secondary_transaction_quantity)) over () c_secondary_qty_total
' || ' FROM mtl_onhand_quantities fact,
' || ' :OPI_MLN_TABLE
' || ' :OPI_ENI_OLTP_STAR_TABLE
' || ' mtl_system_items_b msi
' || ' WHERE fact.inventory_item_id = msi.inventory_item_id
' || ' AND fact.organization_id = msi.organization_id
' || ' :OPI_MLN_CONDITIONS
' || ' :OPI_ENI_OLTP_STAR_COND
' || ' :OPI_PARAM_CONDITIONS
' || ' :OPI_SECURITY_CLAUSE
' || ' GROUP BY msi.primary_uom_code,
' || ' msi.secondary_uom_code,
' || ' :OPI_CURR_INV_STAT_VIEWBY
';