[Home] [Help]
PACKAGE BODY: APPS.QPR_CREATE_AW
Source
1 PACKAGE BODY QPR_CREATE_AW AS
2 /* $Header: QPRUCAWB.pls 120.0 2007/10/11 13:05:32 agbennet noship $ */
3
4
5 function get_measure_code(p_price_plan_id number, p_term_code varchar2) return varchar2
6 is
7 o_meas varchar2(100);
8 begin
9 SELECT c.cube_code||'_'||b.measure_ppa_code
10 into o_meas
11 from qpr_measures b , qpr_cubes c
12 where b.price_plan_id = p_price_plan_id
13 and b.measure_ppa_code = p_term_code
14 and c.cube_id=b.cube_id;
15 return o_meas;
16 exception
17 when others then
18 return null;
19 end;
20
21 PROCEDURE CREATE_MODEL (p_price_plan_id number, p_cube_code varchar2) is
22
23 CURSOR qprmodels_cur IS
24 SELECT a1.model_id, a1.model_name, c.dim_code dim_code
25 FROM qpr_models a1, qpr_cube_dims a,
26 qpr_cubes b, qpr_dimensions c
27 WHERE a.price_plan_id = p_price_plan_id
28 AND b.cube_id = a.cube_id
29 AND b.cube_ppa_code = p_cube_code
30 -- AND c.dim_code = a.dim_code
31 and c.price_plan_dim_id = a.price_plan_dim_id
32 and a1.model_meas_dim_code = c.dim_ppa_code;
33
34 qprmodels_rec qprmodels_cur%ROWTYPE;
35 CURSOR qprmodeleqns_cur(modelId Number) IS
36 SELECT model_id,execution_sequence,lhs_expression_id,rhs_expression_id,operator
37 from qpr_model_equations
38 where model_id = modelId
39 order by execution_sequence;
40 qprmodeleqns_rec qprmodeleqns_cur%ROWTYPE;
41 CURSOR qprexpressions_cur(exprId Number) IS
42 SELECT expression_id,sequence,term_id
43 from qpr_expressions
44 where expression_id = exprId
45 order by sequence;
46 qprexpressions_rec qprexpressions_cur%ROWTYPE;
47
48 CURSOR qprterms_cur(termId Number)IS
49 SELECT term_id,term_code,term_type
50 from qpr_terms
51 where term_id = termId;
52
53 qprterms_rec qprterms_cur%ROWTYPE;
54 tempString varchar2(4000);
55 BEGIN
56 dbms_aw.execute('aw attach '||'QPR'||p_price_plan_id||' rw;');
57 for qprmodels_rec in qprmodels_cur loop
58 dbms_aw.execute('define '||qprmodels_rec.model_name||' model ;');
59 dbms_aw.execute('consider '||qprmodels_rec.model_name||'; ');
60 tempString := 'DIMENSION '||qprmodels_rec.dim_code||'\n';
61 for qprmodeleqns_rec in qprmodeleqns_cur(qprmodels_rec.model_id) loop
62 for qprexpressions_rec in qprexpressions_cur(qprmodeleqns_rec.lhs_expression_id) loop
63 for qprterms_rec in qprterms_cur(qprexpressions_rec.term_id) loop
64 if(qprterms_rec.term_type = 'MEASURE')
65 THEN
66 tempString := tempString ||get_measure_code(p_price_plan_id, qprterms_rec.term_code)||'_STORED';
67 ELSE
68 tempString := tempString || qprterms_rec.term_code;
69 END IF;
70 end loop;-- terms;
71 end loop;-- expressions;
72 tempString := tempString || qprmodeleqns_rec.operator;
73 for qprexpressions_rec in qprexpressions_cur(qprmodeleqns_rec.rhs_expression_id) loop
74 for qprterms_rec in qprterms_cur(qprexpressions_rec.term_id) loop
75 if(qprterms_rec.term_type = 'MEASURE')
76 THEN
77 tempString := tempString ||get_measure_code(p_price_plan_id, qprterms_rec.term_code)||'_STORED';
78 ELSE
79 tempString := tempString || qprterms_rec.term_code;
80 END IF;
81 end loop;-- terms;
82 end loop;-- expressions;
83 tempString := tempString || '\n';
84 end loop;-- modeleqns;
85 dbms_aw.execute('MODEL JOINLINES( '||''''||tempString||''''||' '||''''||'END;'||''''||')');
86 dbms_aw.EXECUTE('update;commit;');
87
88 tempString := '';
89 end loop;-- qprmodel;
90 dbms_aw.execute('aw detach '||'QPR'||p_price_plan_id||';');
91 exception
92 when others then
93 dbms_aw.execute('aw detach '||'QPR'||p_price_plan_id||';');
94 END;
95
96
97 PROCEDURE SUBMIT_REQUEST_SET(P_PRICE_PLAN_ID IN NUMBER,
98 P_REQUEST_ID OUT NOCOPY NUMBER,
99 P_STMT_NUM OUT NOCOPY NUMBER,
100 ERRBUF OUT NOCOPY VARCHAR2,
101 RETCODE OUT NOCOPY VARCHAR2)
102
103 IS
104
105 --L_STMT_NUM NUMBER;
106 SUCC BOOLEAN;
107
108 BEGIN
109
110 --FND_GLOBAL.APPS_INITIALIZE(1008177,60007,667);
111 P_STMT_NUM := 10;
112 SUCC := FND_SUBMIT.SET_REQUEST_SET('QPR','FNDRSSUB2064');
113 P_STMT_NUM := 20;
114 IF SUCC THEN
115 SUCC := FND_SUBMIT.SUBMIT_PROGRAM('QPR','QPRUCAWB','STAGE10',P_PRICE_PLAN_ID);
116 P_STMT_NUM :=30;
117 IF SUCC THEN
118 SUCC := FND_SUBMIT.SUBMIT_PROGRAM('QPR','QPRUC2AWB','STAGE20',P_PRICE_PLAN_ID);
119 P_STMT_NUM := 40;
120 IF SUCC THEN
121 P_REQUEST_ID := FND_SUBMIT.SUBMIT_SET(NULL,FALSE);
122 P_STMT_NUM := 50;
123 END IF;
124 END IF;
125 END IF;
126 P_STMT_NUM := 60;
127 COMMIT;
128 P_STMT_NUM := 70;
129
130 EXCEPTION
131
132 WHEN OTHERS THEN
133 RETCODE:= SQLCODE;
134 ERRBUF := SQLERRM(-RETCODE);
135 ERRBUF := errbuf||'-'||' ERROR OCCURED WHILE SUBMITTING REQUEST SET FOR PRICE_PLAN_ID = '||P_PRICE_PLAN_ID||' '||to_char(sysdate, 'hh24:mi:ss')||' '||P_STMT_NUM;
136 P_REQUEST_ID := 0;
137 -- DBMS_OUTPUT.PUT_LINE(errbuf);
138
139 END SUBMIT_REQUEST_SET;
140
141
142
143
144 PROCEDURE CREATE_AW ( errbuf OUT NOCOPY VARCHAR2,
145 retcode OUT NOCOPY varchar2,
146 P_PRICE_PLAN_ID NUMBER)
147
148 IS
149
150 L_AW_EXISTS2 EXCEPTION;
151 L_AW_NAME1 VARCHAR2(200);
152 L_AW_OUTPUT VARCHAR2(200);
153 L_CLB CLOB;
154 L_STMTNUM NUMBER;
155
156 BEGIN
157
158 L_STMTNUM := 10;
159
160 SELECT AW_XML,AW_CODE
161 INTO L_CLB,L_AW_NAME1
162 FROM QPR_PRICE_PLANS_B
163 WHERE PRICE_PLAN_ID = P_PRICE_PLAN_ID;
164
165 L_STMTNUM := 20;
166
167 IF(AW_EXISTS(L_AW_NAME1)) THEN
168
169 L_STMTNUM := 30;
170
171 l_aw_output := DBMS_AW_XML.EXECUTE(L_CLB);
172
173 L_STMTNUM := 40;
174
175 DBMS_AW.AW_UPDATE('APPS',L_AW_NAME1);
176
177 L_STMTNUM := 50;
178
179 L_STMTNUM := 60;
180
181 UPDATE QPR_PRICE_PLANS_B
182 SET AW_CREATED_FLAG = 'Y'
183 WHERE PRICE_PLAN_ID = P_PRICE_PLAN_ID;
184 COMMIT;
185
186 L_STMTNUM := 70;
187
188 create_model(p_price_plan_id, 'DEAL_MOD');
189
190 --Update the DML Program in the aw for deal.
191
192 qpr_dml_pvt.loaddmlprog(l_aw_name1);
193
194 errbuf := errbuf||' '||to_char(sysdate, 'hh24:mi:ss')||' '||'-'||'. ANALYTIC WORKSPACE CREATED '||L_STMTNUM;
195
196 ELSE
197
198 RAISE L_AW_EXISTS2;
199
200 END IF;
201
202
203 EXCEPTION
204
205 WHEN L_AW_EXISTS2 THEN
206
207 retcode := SQLCODE;
208 errbuf := SQLERRM(-retcode);
209 errbuf := errbuf||'-'||' ANALYTIC WORKSPACE '||L_AW_NAME1||' ALREADY EXCISTS IN THE GIVEN SCHEMA.'||to_char(sysdate, 'hh24:mi:ss');
210
211 WHEN OTHERS THEN
212
213 retcode := SQLCODE;
214 errbuf := SQLERRM(-retcode);
215 errbuf := errbuf||'-'||' ERROR OCCURED WHILE CREATING ANALYTIC WORKSPACE '|| L_AW_NAME1||' '||to_char(sysdate, 'hh24:mi:ss')||' '||L_STMTNUM;
216
217
218 END CREATE_AW;
219
220
221
222 FUNCTION AW_EXISTS(
223 P_AW_NAME2 VARCHAR2 )
224
225 RETURN BOOLEAN
226 IS
227
228 L_AW_EXISTS1 EXCEPTION;
229 L_RES BOOLEAN;
230 PRAGMA EXCEPTION_INIT (L_AW_EXISTS1,-33262);
231
232 BEGIN
233
234 DBMS_AW.AW_ATTACH('APPS',P_AW_NAME2,true);
235 L_RES := FALSE;
236 RETURN L_RES;
237
238 EXCEPTION
239
240 WHEN L_AW_EXISTS1 THEN
241
242 L_RES := TRUE;
243 RETURN L_RES;
244
245 END AW_EXISTS;
246
247 procedure create_aw_java(p_price_plan_is NUMBER) as
248 language java name 'oracle.apps.qpr.etl.aw.AWCreate.runProgram(int)';
249
250 PROCEDURE CREATE_AWXML ( errbuf OUT nocopy VARCHAR2, retcode OUT nocopy varchar2,
251 P_PRICE_PLAN_ID IN NUMBER) IS
252 l_dummy number;
253 begin
254 --cwm2_olap_manager.set_echo_on;
255 create_aw_java(p_price_plan_id);
256
257 exception
258 WHEN others THEN
259 retcode := 2;
260 errbuf := 'Unexpected error '||substr(sqlerrm,1200);
261 end;
262 END;