[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_NCP_PKG
Source
1 PACKAGE BODY poa_dbi_ncp_pkg
2 /* $Header: poadbincpb.pls 120.0 2005/06/01 15:03:27 appldev noship $ */
3
4 AS
5 FUNCTION get_status_sel_clause(p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2;
6 FUNCTION get_trend_sel_clause return VARCHAR2;
7 FUNCTION get_status_filter_where return VARCHAR2;
8
9 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
10 x_custom_sql OUT NOCOPY VARCHAR2,
11 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
12 IS
13 l_query varchar2(4000); l_view_by varchar2(120);
14 l_view_by_col varchar2(120);
15 l_as_of_date date;
16 l_prev_as_of_date date;
17 l_xtd varchar2(10);
18 l_comparison_type varchar2(1) := 'Y';
19 l_nested_pattern number;
20 l_cur_suffix varchar2(2);
21 l_url varchar2(300);
22 l_custom_sql varchar2(4000);
23 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
24 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
25 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
26 l_where_clause VARCHAR2(2000);
27 l_view_by_value VARCHAR2(100);
28 l_mv VARCHAR2(30);
29 BEGIN
30 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
31 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
32
33 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value, l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl, x_custom_output ,
34 'N','PO', '5.0', 'VPP','POD');
35
36 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
37 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
38
39
40 if((l_view_by = 'SUPPLIER+POA_SUPPLIERS') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
41 l_url := null;
42 else
43 l_url := 'pFunctionName=POA_DBI_NCP_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=SUPPLIER+POA_SUPPLIERS';
44 end if;
45
46 l_query := get_status_sel_clause(l_view_by_col, l_url) || ' from
47 '|| poa_dbi_template_pkg.status_sql(l_mv,
48 l_where_clause,
49 l_join_tbl,
50 p_use_windowing => 'Y',
51 p_col_name => l_col_tbl,
52 p_use_grpid => 'N',
53 p_filter_where => get_status_filter_where,
54 p_in_join_tables => l_in_join_tbl);
55
56 x_custom_sql := l_query;
57
58 end;
59
60 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
61 x_custom_sql OUT NOCOPY VARCHAR2,
62 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
63 IS
64 l_query varchar2(4000);
65 l_view_by varchar2(120);
66 l_view_by_col VARChar2(120);
67 l_as_of_date date;
68 l_prev_as_of_date date;
69 l_xtd varchar2(10);
70 l_comparison_type varchar2(1) := 'Y';
71 l_nested_pattern number;
72 l_cur_suffix varchar2(2);
73 l_custom_sql varchar2(4000);
74 l_custom_rec BIS_QUERY_ATTRIBUTES;
75 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
76 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
77 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
78 l_mv VARCHAR2(30);
79 l_where_clause VARCHAR2(2000);
80 l_view_by_value VARCHAR2(100);
81
82 BEGIN
83 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
84 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
85
86 poa_dbi_sutil_pkg.process_parameters(p_param
87 ,l_view_by
88 ,l_view_by_col
89 ,l_view_by_value
90 ,l_comparison_type
91 , l_xtd
92 , l_as_of_date
93 , l_prev_as_of_date
94 , l_cur_suffix
95 , l_nested_pattern
96 , l_where_clause
97 , l_mv
98 , l_join_tbl
99 , l_in_join_tbl
100 , x_custom_output
101 , 'Y','PO', '5.0', 'VPP','POD');
102
103 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
104 poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt', 'N');
105
106 l_query := get_trend_sel_clause || ' from
107 '|| poa_dbi_template_pkg.trend_sql(
108 l_xtd,
109 l_comparison_type,
110 l_mv,
111 l_where_clause,
112 l_col_tbl,
113 p_use_grpid => 'N',
114 p_in_join_tables => l_in_join_tbl);
115
116 x_custom_sql := l_query;
117
118 END;
119
120 FUNCTION get_status_filter_where return VARCHAR2
121 IS
122 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
123 BEGIN
124 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
125 l_col_tbl.extend;
126 l_col_tbl(1) := 'POA_MEASURE1';
127 l_col_tbl.extend;
128 l_col_tbl(2) := 'POA_PERCENT2';
129 l_col_tbl.extend;
130 l_col_tbl(3) := 'POA_PERCENT1';
131 l_col_tbl.extend;
132 l_col_tbl(4) := 'POA_MEASURE3';
133
134 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
135
136 END;
137
138 FUNCTION get_status_sel_clause(p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2
139 IS
140
141 l_sel_clause varchar2(4000);
142
143 BEGIN
144
145 l_sel_clause :=
146 'select v.value VIEWBY,
147 oset.POA_MEASURE1 POA_MEASURE1, --Non-Contract Purchases Amount
148 oset.POA_PERCENT1 POA_PERCENT1, --Change
149 oset.POA_MEASURE3 POA_MEASURE3, --PO Purchases Amount
150 oset.POA_PERCENT2 POA_PERCENT2, --Non-Contract Rate
151 oset.POA_MEASURE4 POA_MEASURE4, --Total Non-Contract Purchases Amount
152 oset.POA_MEASURE5 POA_MEASURE5, --Total PO Purchases Amount
153 oset.POA_MEASURE6 POA_MEASURE6, --Total Change
154 oset.POA_MEASURE7 POA_MEASURE7, --Total Non-Contract Rate
155 ''' || p_url || ''' POA_MEASURE8
156 from
157 (select (rank() over
158 (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
159 || p_view_by_col || ',
160 POA_MEASURE1, POA_PERCENT1, POA_MEASURE3, POA_PERCENT2, POA_MEASURE4,
161 POA_MEASURE5, POA_MEASURE6, POA_MEASURE7 from
162 (select ' || p_view_by_col || ',
163 ' || p_view_by_col || ' VIEWBY,
164 nvl(c_n_contract_amt,0) POA_MEASURE1,
165 ' || poa_dbi_util_pkg.change_clause('c_n_contract_amt','p_n_contract_amt') || ' POA_PERCENT1,
166 nvl(c_purchase_amt,0) POA_MEASURE3,
170 ' || poa_dbi_util_pkg.change_clause('c_n_contract_amt_total','p_n_contract_amt_total') || ' POA_MEASURE6,
167 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT2,
168 nvl(c_n_contract_amt_total,0) POA_MEASURE4,
169 nvl(c_purchase_amt_total,0) POA_MEASURE5,
171 ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE7';
172
173 return l_sel_clause;
174
175 END;
176
177 FUNCTION get_trend_sel_clause return VARCHAR2
178 IS
179
180 l_sel_clause varchar2(4000);
181
182 BEGIN
183
184 l_sel_clause :=
185 'select cal.name VIEWBY,
186 nvl(p_n_contract_amt,0) POA_MEASURE1,
187 nvl(c_n_contract_amt,0) POA_MEASURE2,
188 nvl(p_n_contract_amt,0) POA_PERCENT1,
189 ' || poa_dbi_util_pkg.change_clause('c_n_contract_amt','p_n_contract_amt') || ' POA_PERCENT3,
190 nvl(c_n_contract_amt,0) POA_PERCENT2';
191
192 return l_sel_clause;
193
194 END;
195
196 end poa_dbi_ncp_pkg;