APPS.OPI_DBI_ONTIME_PROD_RPT_PKG dependencies on POA_DBI_UTIL_PKG

Line 4: poa_dbi_util_pkg.POA_DBI_JOIN_TBL)

1: PACKAGE BODY OPI_DBI_ONTIME_PROD_RPT_PKG AS
2: /*$Header: OPIDOTPRPTB.pls 120.2 2005/09/29 23:12 sberi noship $ */
3: FUNCTION GET_ONTIME_PROD_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: /* -------------------------------------------------------------------------------------------

Line 29: l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;

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

Line 30: l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;

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

Line 35: l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;

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

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

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

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

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

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

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

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

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

Line 70: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,

66:
67: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
68: p_col_name => 'late_prod_cnt' ,
69: p_alias_name => 'late_prod_cnt',
70: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
71: p_grand_total => 'Y',
72: p_to_date_type => 'XTD'
73: );
74: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

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

70: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
71: p_grand_total => 'Y',
72: p_to_date_type => 'XTD'
73: );
74: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
75: p_col_name =>'late_prod_val_' || l_cur_suffix,
76: p_alias_name =>'late_prod_val',
77: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
78: p_grand_total => 'Y',

Line 77: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,

73: );
74: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
75: p_col_name =>'late_prod_val_' || l_cur_suffix,
76: p_alias_name =>'late_prod_val',
77: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
78: p_grand_total => 'Y',
79: p_to_date_type => 'XTD'
80: );
81: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

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

77: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
78: p_grand_total => 'Y',
79: p_to_date_type => 'XTD'
80: );
81: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
82: p_col_name => 'ontime_prod_cnt',
83: p_alias_name => 'ontime_prod_cnt',
84: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
85: p_grand_total => 'Y',

Line 84: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,

80: );
81: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
82: p_col_name => 'ontime_prod_cnt',
83: p_alias_name => 'ontime_prod_cnt',
84: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
85: p_grand_total => 'Y',
86: p_to_date_type => 'XTD'
87: );
88: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,

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

84: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
85: p_grand_total => 'Y',
86: p_to_date_type => 'XTD'
87: );
88: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
89: p_col_name =>'ontime_prod_val_' || l_cur_suffix,
90: p_alias_name =>'ontime_prod_val',
91: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
92: p_grand_total => 'Y',

Line 91: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,

87: );
88: poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
89: p_col_name =>'ontime_prod_val_' || l_cur_suffix,
90: p_alias_name =>'ontime_prod_val',
91: p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
92: p_grand_total => 'Y',
93: p_to_date_type => 'XTD'
94: );
95:

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

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

Line 102: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

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

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

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

Line 109: p_prior_code => poa_dbi_util_pkg.NO_PRIORS,

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

Line 132: poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);

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

Line 156: poa_dbi_util_pkg.POA_DBI_JOIN_TBL)

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