[Home] [Help]
PACKAGE BODY: APPS.ZPB_EXTERNAL_BP_PUBLISH
Source
1 PACKAGE BODY ZPB_EXTERNAL_BP_PUBLISH AS
2 /* $Header: zpbpbpes.plb 120.12 2007/12/04 15:40:17 mbhat noship $ */
3
4 is_attached VARCHAR2(1);
5
6 PROCEDURE CLEANUP(l_codeaw VARCHAR2, l_dataaw VARCHAR2) AS
7 BEGIN
8 -- dettach the required AW in approp mode
9 IF (is_attached = 'Y') THEN
10 --ZPB_AW.EXECUTE('aw aliaslist ' ||l_dataaw ||' unalias SHARED');
11 --ZPB_AW.EXECUTE('aw detach ' || l_codeaw );
12 --ZPB_AW.EXECUTE('aw detach ' || l_dataaw );
13
14 --b4939451
15 ZPB_AW.DETACH_ALL;
16 is_attached := 'N';
17 END IF;
18 EXCEPTION
19 WHEN OTHERS THEN
20 NULL;
21 END CLEANUP;
22
23 PROCEDURE START_BUSINESS_PROCESS(
24 P_api_version IN NUMBER,
25 P_init_msg_list IN VARCHAR2,
26 P_validation_level IN NUMBER,
27 P_bp_name IN VARCHAR2,
28 P_ba_name IN VARCHAR2,
29 P_horizon_start IN DATE DEFAULT NULL,
30 P_horizon_end IN DATE DEFAULT NULL,
31 P_send_date IN DATE DEFAULT NULL,
32 X_start_member OUT NOCOPY VARCHAR2,
33 X_end_member OUT NOCOPY VARCHAR2,
34 X_item_key OUT NOCOPY VARCHAR2,
35 X_msg_count OUT NOCOPY NUMBER,
36 X_msg_data OUT NOCOPY VARCHAR2,
37 X_return_status OUT NOCOPY VARCHAR2
38 ) AS
39 l_edit_ac_id NUMBER;
40 l_published_id NUMBER;
41 l_user NUMBER;
42 l_isValid BOOLEAN;
43 l_start_id VARCHAR2(200);
44 l_end_id VARCHAR2(200);
45 l_timemems VARCHAR2(4000);
46 l_override_user_check VARCHAR2(10);
47 l_user_exists NUMBER;
48 l_codeAW VARCHAR2(30);
49 l_busArea NUMBER;
50 l_owner NUMBER;
51 l_DataAW VARCHAR2(30);
52 errbuf VARCHAR2(4000);
53 l_olap_call VARCHAR2(4000);
54 l_bp_start VARCHAR2(200);
55 l_count number;
56 l_hs_type VARCHAR2(30);
57 l_he_type VARCHAR2(30);
58 l_start_lvl VARCHAR2(30);
59 l_end_lvl VARCHAR2(30);
60 l_valid VARCHAR2(2);
61 l_time_fixed VARCHAR2(50);
62 l_time_relative VARCHAR2(50);
63 l_exist_start_mem VARCHAR2(4000);
64 l_exist_end_mem VARCHAR2(4000);
65 l_respID number;
66 l_respAppID number;
67
68
69
70
71
72
73
74
75 CURSOR c_publ_id IS SELECT analysis_cycle_id, BUSINESS_AREA_ID, owner_id
76 FROM zpb_analysis_cycles a , zpb_cycle_relationships b
77 WHERE a.name = p_bp_name
78 AND a.analysis_cycle_id = b.published_ac_id
79 and a.BUSINESS_AREA_ID in (select BUSINESS_AREA_ID
80 from zPB_BUSINESS_AREAS_VL where name = p_ba_name);
81
82 --AGB temporay change of = to be in for bug in Business Area naming. Should change back to = before release.
83
84 CURSOR c_horzstart_type IS SELECT value FROM zpb_ac_param_values
85 WHERE analysis_cycle_id = l_published_id
86 AND param_id = ( SELECT tag FROM fnd_lookup_values_vl
87 WHERE LOOKUP_TYPE = 'ZPB_PARAMS' AND lookup_code = 'CAL_HS_TYPE');
88
89 CURSOR c_horzend_type IS SELECT value FROM zpb_ac_param_values
90 WHERE analysis_cycle_id = l_published_id
91 AND param_id = ( SELECT tag FROM fnd_lookup_values_vl
92 WHERE LOOKUP_TYPE = 'ZPB_PARAMS' AND lookup_code = 'CAL_HE_TYPE');
93
94 CURSOR c_horzstart_mem IS SELECT value FROM zpb_ac_param_values
95 WHERE analysis_cycle_id = l_published_id
96 AND param_id = ( SELECT tag FROM fnd_lookup_values_vl
97 WHERE LOOKUP_TYPE = 'ZPB_PARAMS' AND lookup_code = 'CAL_HS_TIME_MEMBER');
98
99 CURSOR c_horzend_mem IS SELECT value FROM zpb_ac_param_values
100 WHERE analysis_cycle_id = l_published_id
101 AND param_id = ( SELECT tag FROM fnd_lookup_values_vl
102 WHERE LOOKUP_TYPE = 'ZPB_PARAMS' AND lookup_code = 'CAL_HE_TIME_MEMBER');
103
104 CURSOR c_bp_external IS SELECT value FROM zpb_ac_param_values
105 WHERE analysis_cycle_id = l_published_id
106 AND param_id =
107 ( SELECT tag FROM fnd_lookup_values_vl WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
108 AND lookup_code = 'OVERRIDE_EXTERNAL_USER_CHECK');
109
110 begin
111
112
113 -- b4594118 23Sep05 credentials of intial caller will set back to these at end of API
114 l_user := fnd_global.user_id;
115 l_respID := fnd_global.RESP_ID;
116 l_respAppID := fnd_global.RESP_APPL_ID;
117
118 l_time_fixed := 'FIXED_TIME';
119 l_time_relative := 'NUMBER_OF_PERIODS';
120
121 -- VALIDATIONS
122
123 -- 1. P_ba_name is not null and valid
124 select count(BUSINESS_AREA_ID) into l_count
125 from zPB_BUSINESS_AREAS_VL where name = p_ba_name;
126
127 IF (P_ba_name IS NULL) or (l_count = 0) then
128 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_API_BA_REQUIRED');
129 X_MSG_data := FND_MESSAGE.GET;
130 X_return_status := FND_API.G_RET_STS_ERROR ;
131 return;
132 end if;
133
134
135 -- 2. is a valid BP name , is it published
136 OPEN c_publ_id;
137 FETCH c_publ_id INTO l_published_id, l_busArea, l_owner;
138 CLOSE c_publ_id;
139 IF (l_published_id IS NULL) THEN
140 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_INV_OR_UNPUB_BP');
141 X_MSG_data := FND_MESSAGE.GET;
142 X_return_status := FND_API.G_RET_STS_ERROR ;
143 return;
144 END IF;
145
146 -- 3. is the requestor authorised
147 OPEN c_bp_external;
148 FETCH c_bp_external INTO l_override_user_check;
149 CLOSE c_bp_external;
150 IF (l_override_user_check IS NULL) THEN
151 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_INV_OR_UNPUB_BP');
152 X_MSG_data := FND_MESSAGE.GET;
153 X_return_status := FND_API.G_RET_STS_ERROR ;
154 return;
155 END IF;
156
157 --l_override_user_check := 'Y';
158 IF (l_override_user_check = 'N' ) THEN
159 SELECT COUNT(*) INTO l_user_exists FROM ZPB_BP_EXTERNAL_USERS
160 WHERE analysis_cycle_id = l_published_id
161 AND user_id = l_user;
162 IF (l_user_exists = 0) THEN
163 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_INV_EXTERNAL_USER');
164 X_MSG_data := FND_MESSAGE.GET;
165 X_return_status := FND_API.G_RET_STS_ERROR ;
166 return;
167 END IF;
168 END IF;
169
170 -- 4.a horizon can be overiden only if they are of FIXED type
171 OPEN c_horzstart_type;
172 FETCH c_horzstart_type INTO l_hs_type;
173 CLOSE c_horzstart_type;
174 IF (l_hs_type = l_time_relative) THEN
175 --dbms_output.put_line(' l_time_relative');
176 IF ( P_horizon_start IS NOT NULL) THEN
177 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_CANT_OVERIDE_REL_STIME');
178 X_return_status := FND_API.G_RET_STS_ERROR ;
179 X_MSG_data := FND_MESSAGE.GET;
180 return;
181 END IF;
182 ELSE
183 --dbms_output.put_line(' l_time_fixed');
184 OPEN c_horzstart_mem;
185 FETCH c_horzstart_mem INTO l_exist_start_mem;
186 CLOSE c_horzstart_mem;
187 END IF;
188
189 -- 4.a horizon can be overiden only if they are of FIXED type
190 OPEN c_horzend_type;
191 FETCH c_horzend_type INTO l_he_type;
192 CLOSE c_horzend_type;
193 IF (l_he_type = l_time_relative) THEN
194 IF (P_horizon_end IS NOT NULL) THEN
195 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_CANT_OVERIDE_REL_ETIME');
196 X_return_status := FND_API.G_RET_STS_ERROR ;
197 X_MSG_data := FND_MESSAGE.GET;
198 return;
199 END IF;
200 ELSE
201 OPEN c_horzend_mem;
202 FETCH c_horzend_mem INTO l_exist_end_mem;
203 CLOSE c_horzend_mem;
204 END IF;
205
206 -- 4. has the CALENDAR_START_DATE been reached
207 SELECT value into l_bp_start FROM zpb_ac_param_values
208 WHERE analysis_cycle_id = l_published_id
209 AND param_id =
210 ( SELECT tag FROM fnd_lookup_values_vl WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
211 AND lookup_code = 'CALENDAR_START_DATE');
212
213 --if start date is greater than sysdate the start date has not been reached.
214 if to_date(l_bp_start, 'YYYY/MM/DD HH24:MI:SS') - SYSDATE > 0 then
215 -- x_msg_data := 'The Business Process cannot be run yet because the start date has not been reached';
216 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_API_BEFORE_START');
217 X_MSG_data := FND_MESSAGE.GET;
218 X_return_status := FND_API.G_RET_STS_ERROR ;
219 return;
220 end if;
221 --
222 --
223
224
225 -- IF (P_horizon_start IS NOT NULL) OR (P_horizon_end IS NOT NULL) THEN
226 -- 3. are the start and end horizon params valid
227
228 -- b4594118 23Sep05 flip fnd_global.user_id to BP owner for horizion validation.
229 fnd_global.apps_initialize(l_owner, 0, 0);
230
231
232
233 -- attach the required AW in approp mode
234
235 -- b4939451
236 -- l_codeAW := zpb_aw.get_schema||'.'|| zpb_aw.get_code_aw(l_owner) ;
237 -- l_dataaw := zpb_aw.get_schema||'.ZPBDATA'|| l_busArea ;
238
239 -- ZPB_AW.EXECUTE('aw attach ' || l_codeaw || ' ro');
240 -- ZPB_AW.EXECUTE('aw attach ' || l_dataaw || ' ro');
241 -- ZPB_AW.EXECUTE('aw aliaslist ' || l_dataaw || ' alias SHARED');
242
243 ZPB_AW.INITIALIZE (P_API_VERSION => 1.0,
244 P_INIT_MSG_LIST => FND_API.G_TRUE,
245 X_RETURN_STATUS => x_return_status,
246 X_MSG_COUNT => x_msg_count,
247 X_MSG_DATA => errbuf,
248 P_BUSINESS_AREA_ID => l_busArea,
249 P_SHARED_RW => FND_API.G_FALSE);
250
251 is_attached := 'Y';
252
253
254 --dbms_output.put_line(l_exist_start_mem);
255 --IF (p_horizon_start IS NOT NULL) OR (p_horizon_end IS NOT NULL) THEN
256 IF ((l_hs_type = l_time_fixed) OR (l_he_type = l_time_fixed)) THEN
257 l_olap_call := 'shw cm.gettimemem(';
258 IF p_horizon_start IS NOT NULL THEN
259 l_olap_call := l_olap_call || '''' ||to_char(p_horizon_start,'MM-DD-YYYY') || '''' ||' ' ;
260 ELSE
261 l_olap_call := l_olap_call || 'na' || ' ';
262 END IF;
263
264 IF l_exist_start_mem IS NOT NULL THEN
265 l_olap_call := l_olap_call || ''''|| l_exist_start_mem || '''' || ' ';
266 ELSE
267 l_olap_call := l_olap_call || 'na'|| ' ';
268 END IF;
269
270 if p_horizon_end IS NOT NULL THEN
271 l_olap_call := l_olap_call || '''' ||to_char(p_horizon_end,'MM-DD-YYYY') || '''' ||' ' ;
272 ELSE
273 l_olap_call := l_olap_call || 'na' ||' ' ;
274 END IF;
275
276 IF l_exist_end_mem IS NOT NULL THEN
277 l_olap_call := l_olap_call || ''''|| l_exist_end_mem || '''' || ' )';
278 ELSE
279 l_olap_call := l_olap_call || 'na'|| ' )';
280 END IF;
281
282 -- l_olap_call := l_olap_call || l_exist_start_mem || '''' ||' ' || '''' || l_exist_end_mem || ''')';
283
284 --dbms_output.put_line(l_olap_call);
285 l_timemems := zpb_aw.interp( l_olap_call );
286 --dbms_output.put_line(l_timemems);
287
288 -- ex call:
289 -- show cm.gettimemem('1/12/1995' '1/12/2004' )
290 -- ex output:
291 -- <SMEM>24526710000000000000011000100140<SMEM><SMNAME>January 2003<SMNAME><EMEM>24530050000000000000121000100140<EMEM><EMNAME>December 2003<EMNAME><SLEVEL>140<SLEVEL><ELEVEL>140<ELEVEL>
292 -- tag SMEM = start member, EMEM = end member, SMNAME = start member name, EMNAME = end member name,
293
294 l_start_id := substr(l_timemems, instr(l_timemems, '<SMEM>')+6, instr(l_timemems, '<SMEM>', -1) - instr(l_timemems, '<SMEM>') -6);
295 l_end_id := substr(l_timemems, instr(l_timemems, '<EMEM>')+6, instr(l_timemems, '<EMEM>', -1) - instr(l_timemems, '<EMEM>') -6);
296 X_start_member := substr(l_timemems, instr(l_timemems, '<SMNAME>')+8, instr(l_timemems, '<SMNAME>', -1) - instr(l_timemems, '<SMNAME>') -8);
297 X_end_member := substr(l_timemems, instr(l_timemems, '<EMNAME>')+8, instr(l_timemems, '<EMNAME>', -1) - instr(l_timemems, '<EMNAME>') -8);
298 l_start_lvl := substr(l_timemems, instr(l_timemems, '<SLEVEL>')+8, instr(l_timemems, '<SLEVEL>', -1) - instr(l_timemems, '<SLEVEL>') -8);
299 l_end_lvl := substr(l_timemems, instr(l_timemems, '<ELEVEL>')+8, instr(l_timemems, '<ELEVEL>', -1) - instr(l_timemems, '<ELEVEL>') -8);
300 --dbms_output.put_line('l_start_lvl=' || l_start_lvl);
301 --dbms_output.put_line('l_end_lvl=' || l_end_lvl);
302
303 --dbms_output.put_line('X_start_member=' || X_start_member);
304 --dbms_output.put_line('X_end_member=' || X_end_member);
305
306 IF ((p_horizon_start IS NOT NULL AND X_start_member is null) OR (p_horizon_end IS NOT NULL AND X_end_member is null)) then
307 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_INVALID_DATES');
308 X_MSG_data := FND_MESSAGE.GET;
309 X_return_status := FND_API.G_RET_STS_ERROR ;
310 CLEANUP(l_codeaw , l_dataaw);
311 return;
312 END IF;
313
314
315
316 -- AGB 20APR06 moved from below b5015702
317
318 -- validate if the start mem is prior to end mem
319 l_olap_call := 'show &'|| 'obj(property ''ENDDATEVAR'' ''CAL_PERIODS'')(cal_periods '''||l_end_id||''') ge &'||'obj(property ''ENDDATEVAR'' ''CAL_PERIODS'')(cal_periods '''||l_start_id||''')';
320 --dbms_output.put_line(' l_olap_call=' || l_olap_call);
321 l_isvalid := ZPB_AW.INTERPBOOL (l_olap_call );
322
323 IF NOT(l_isvalid) THEN
324 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_INVALID_DATES');
325 X_MSG_data := FND_MESSAGE.GET;
326 X_return_status := FND_API.G_RET_STS_ERROR ;
327 CLEANUP(l_codeaw , l_dataaw);
328 return;
329 END IF;
330 -- end b5015702
331
332 END IF; -- p_horizon_start/end is not null
333
334
335 -- validate the BP for solve compliance, start horizon
336 IF (l_hs_type = l_time_relative) THEN
337 SELECT value INTO l_start_lvl FROM zpb_ac_param_values
338 WHERE analysis_cycle_id = l_published_id
339 AND param_id =
340 ( SELECT tag FROM fnd_lookup_values_vl WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
341 AND lookup_code = 'CAL_HS_LEVEL');
342 END IF;
343
344 --dbms_output.put_line('l_start_lvl=' || l_start_lvl);
345 zpb_acval_pvt.val_solve_hrzselections(
346 p_api_version => 1.0,
347 p_init_msg_list => FND_API.G_TRUE,
348 p_commit => FND_API.G_FALSE,
349 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
350 x_return_status => x_return_status,
351 x_msg_count => x_msg_count ,
352 x_msg_data => x_msg_data ,
353 p_analysis_cycle_id => l_published_id,
354 p_hrz_level => l_start_lvl,
355 x_isvalid => l_valid
356 );
357
358 IF (l_valid = 'N') THEN
359 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_HRZSLV_LEVELS_VALID_MSG');
360 X_MSG_data := FND_MESSAGE.GET;
361 X_return_status := FND_API.G_RET_STS_ERROR ;
362 CLEANUP(l_codeaw , l_dataaw);
363 return;
364 END IF;
365
366 -- validate the BP for solve compliance, end horizon
367 IF (l_he_type = l_time_relative) THEN
368 SELECT value INTO l_end_lvl FROM zpb_ac_param_values
369 WHERE analysis_cycle_id = l_published_id
370 AND param_id =
371 ( SELECT tag FROM fnd_lookup_values_vl WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
372 AND lookup_code = 'CAL_HE_LEVEL');
373 END IF;
374
375 --dbms_output.put_line('l_end_lvl=' || l_end_lvl);
376 zpb_acval_pvt.val_solve_hrzselections(
377 p_api_version => 1.0,
378 p_init_msg_list => FND_API.G_TRUE,
379 p_commit => FND_API.G_FALSE,
380 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
381 x_return_status => x_return_status,
382 x_msg_count => x_msg_count ,
383 x_msg_data => x_msg_data ,
384 p_analysis_cycle_id => l_published_id,
385 p_hrz_level => l_end_lvl,
386 x_isvalid => l_valid
387 );
388 IF (l_valid = 'N') THEN
389 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_HRZSLV_LEVELS_VALID_MSG');
390 X_MSG_data := FND_MESSAGE.GET;
391 X_return_status := FND_API.G_RET_STS_ERROR ;
392 CLEANUP(l_codeaw , l_dataaw);
393 return;
394 END IF;
395
396 -- dettach the required AW in approp mode
397
398 -- b4939451
399 -- ZPB_AW.EXECUTE('aw aliaslist ' ||l_dataaw ||' unalias SHARED');
400 -- ZPB_AW.EXECUTE('aw detach ' || l_codeaw );
401 -- ZPB_AW.EXECUTE('aw detach ' || l_dataaw );
402
403 ZPB_AW.DETACH_ALL;
404 is_attached := 'N';
405
406 -- POST VALIDATION SUCCESS
407 -- b4594118 23Sep05 flip fnd_global.user_id back to calling user ID for last update by audits
408 -- will set back to l_owner when starting WF so BP will be run under owner ID.
409
410 fnd_global.apps_initialize(l_user, 0, 0);
411
412 zpb_ac_ops.create_editable_copy(l_published_id, p_bp_name , l_user, l_edit_ac_id );
413
414 zpb_ac_ops.PUBLISH_CYCLE(
415 EDITABLE_AC_ID_IN => l_edit_ac_id
416 ,PUBLISHED_BY_IN => l_owner
417 ,PUBLISH_OPTIONS_IN => 'UPDATE_FOR_FUTURE'
418 ,p_bp_name_in => p_bp_name
419 ,p_external => 'Y'
420 ,p_START_MEM_IN => l_start_id
421 ,p_END_MEM_IN => l_end_id
422 ,p_send_date_in => p_send_date
423 ,PUBLISHED_AC_ID_OUT => l_published_id
424 ,X_item_key_out => X_item_key
425 );
426
427 -- b4594118 23Sep05 reset caller credentials
428 fnd_global.apps_initialize(l_user, l_respID, l_respAppID);
429
430 x_return_status := FND_API.G_RET_STS_SUCCESS;
431 commit;
432 exception
433 WHEN OTHERS THEN
434 CLEANUP(l_codeaw , l_dataaw);
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
436 X_MSG_data := SUBSTR(sqlerrm, 1, 255);
437
438 end START_BUSINESS_PROCESS;
439
440 END ZPB_EXTERNAL_BP_PUBLISH ;