APPS.OPI_DBI_CURR_PROD_DEL_RPT_PKG dependencies on POA_DBI_UTIL_PKG

Line 4: poa_dbi_util_pkg.POA_DBI_JOIN_TBL)

1: PACKAGE BODY OPI_DBI_CURR_PROD_DEL_RPT_PKG AS
2: /*$Header: OPIDCPDRPTB.pls 120.1 2005/08/11 02:42 sberi noship $ */
3: FUNCTION GET_CURR_PROD_DEL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
4: poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
5: RETURN VARCHAR2;
6:
7: /* -------------------------------------------------------------------------------------------
8: Procedure Name: GET_CURR_PROD_DEL_SQL

Line 28: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

24: l_comparison_type VARCHAR2(1);
25: l_cur_suffix VARCHAR2(5);
26: l_custom_sql VARCHAR2 (10000);
27: l_subinv_val VARCHAR2 (120) := NULL;
28: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
29: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
30: l_where_clause VARCHAR2 (2000);
31: l_mv VARCHAR2 (30);
32: l_aggregation_level_flag VARCHAR2(10);

Line 29: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

25: l_cur_suffix VARCHAR2(5);
26: l_custom_sql VARCHAR2 (10000);
27: l_subinv_val VARCHAR2 (120) := NULL;
28: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
29: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
30: l_where_clause VARCHAR2 (2000);
31: l_mv VARCHAR2 (30);
32: l_aggregation_level_flag VARCHAR2(10);
33: l_custom_rec BIS_QUERY_ATTRIBUTES;

Line 34: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

30: l_where_clause VARCHAR2 (2000);
31: l_mv VARCHAR2 (30);
32: l_aggregation_level_flag VARCHAR2(10);
33: l_custom_rec BIS_QUERY_ATTRIBUTES;
34: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
35:
36: BEGIN
37: -- initialization block
38: l_comparison_type := 'Y';

Line 42: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();

38: l_comparison_type := 'Y';
39: l_aggregation_level_flag := '0';
40:
41: -- clear out the column and Join info tables.
42: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
43: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
44: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
45:
46: -- get all the query parameters

Line 43: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();

39: l_aggregation_level_flag := '0';
40:
41: -- clear out the column and Join info tables.
42: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
43: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
44: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
45:
46: -- get all the query parameters
47: opi_dbi_rpt_util_pkg.process_parameters (

Line 44: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();

40:
41: -- clear out the column and Join info tables.
42: l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
43: l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
44: l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
45:
46: -- get all the query parameters
47: opi_dbi_rpt_util_pkg.process_parameters (
48: p_param => p_param,

Line 66: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

62: p_mv_set => 'CPD',
63: p_mv_flag_type => 'ITEM_CAT');
64: -- Add measure columns that need to be aggregated
65:
66: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
67: p_col_name => 'late_jobs_cnt' ,
68: p_alias_name => 'late_jobs_cnt',
69: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
70: p_grand_total => 'Y',

Line 69: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

65:
66: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
67: p_col_name => 'late_jobs_cnt' ,
68: p_alias_name => 'late_jobs_cnt',
69: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
70: p_grand_total => 'Y',
71: p_to_date_type => 'NA'
72: );
73: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

Line 73: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

69: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
70: p_grand_total => 'Y',
71: p_to_date_type => 'NA'
72: );
73: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
74: p_col_name =>'late_jobs_val_' || l_cur_suffix,
75: p_alias_name =>'late_jobs_val',
76: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
77: p_grand_total => 'Y',

Line 76: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

72: );
73: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
74: p_col_name =>'late_jobs_val_' || l_cur_suffix,
75: p_alias_name =>'late_jobs_val',
76: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
77: p_grand_total => 'Y',
78: p_to_date_type => 'NA'
79: );
80: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

Line 80: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

76: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
77: p_grand_total => 'Y',
78: p_to_date_type => 'NA'
79: );
80: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
81: p_col_name => 'open_jobs_cnt',
82: p_alias_name => 'open_jobs_cnt',
83: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
84: p_grand_total => 'Y',

Line 83: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

79: );
80: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
81: p_col_name => 'open_jobs_cnt',
82: p_alias_name => 'open_jobs_cnt',
83: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
84: p_grand_total => 'Y',
85: p_to_date_type => 'NA'
86: );
87: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

Line 87: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

83: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
84: p_grand_total => 'Y',
85: p_to_date_type => 'NA'
86: );
87: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
88: p_col_name =>'open_jobs_val_' || l_cur_suffix,
89: p_alias_name =>'open_jobs_val',
90: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
91: p_grand_total => 'Y',

Line 90: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

86: );
87: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
88: p_col_name =>'open_jobs_val_' || l_cur_suffix,
89: p_alias_name =>'open_jobs_val',
90: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
91: p_grand_total => 'Y',
92: p_to_date_type => 'NA'
93: );
94:

Line 98: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

94:
95: -- Quantity columns are only needed for Item viewby.
96: IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
97: --{
98: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
99: p_col_name => 'late_jobs_qty' ,
100: p_alias_name => 'late_jobs_qty',
101: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
102: p_grand_total => 'N',

Line 101: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

97: --{
98: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
99: p_col_name => 'late_jobs_qty' ,
100: p_alias_name => 'late_jobs_qty',
101: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
102: p_grand_total => 'N',
103: p_to_date_type => 'NA'
104: );
105: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

Line 105: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

101: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
102: p_grand_total => 'N',
103: p_to_date_type => 'NA'
104: );
105: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
106: p_col_name => 'open_jobs_qty' ,
107: p_alias_name => 'open_jobs_qty',
108: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
109: p_grand_total => 'N',

Line 108: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

104: );
105: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
106: p_col_name => 'open_jobs_qty' ,
107: p_alias_name => 'open_jobs_qty',
108: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,
109: p_grand_total => 'N',
110: p_to_date_type => 'NA'
111: );
112: --}

Line 131: poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);

127: -- prepare output for bind variables
128: x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
129: l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
130: -- set the basic bind variables for the status SQL
131: poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
132:
133: -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
134: l_custom_rec.attribute_name := ':OPI_ITEM_CAT_FLAG';
135: l_custom_rec.attribute_value := l_aggregation_level_flag;

Line 155: poa_dbi_util_pkg.POA_DBI_JOIN_TBL)

151: ----------------------------------------------------------------------------------------------
152: */
153:
154: FUNCTION GET_CURR_PROD_DEL_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
155: poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
156: RETURN VARCHAR2
157: IS
158: l_sel_clause VARCHAR2(15000);
159: l_view_by_col_name VARCHAR2(120);