[Home] [Help]
PACKAGE BODY: APPS.EGO_PUB_FWK_PK
Source
1 package body EGO_PUB_FWK_PK AS
2 /* $Header: EGOPFWKB.pls 120.15.12020000.7 2013/05/22 09:02:28 qyou ship $*/
3
4 /*----------------------------------------------------------------------------+
5 | Copyright (c) 2003 Oracle Corporation RedwoodShores, California, USA
6 | All rights reserved.
7 | Oracle Manufacturing
8 |
9 |FILE NAME : EGOPFWKB.pls
10 |
11 |DESCRIPTION :
12 |
13 -- Start of comments
14 -- API name :
15 -- Type :
16 -- Function :
17 -- Pre-reqs :
18 -- Parameters :
19 -- IN :
20 -- OUT :
21 -- End of comments
22 */
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_PUB_FWK_PK';
25
26
27 --Start procedural declaration
28 PROCEDURE process_entities
29 ( p_batch_id IN NUMBER
30 ,x_return_status OUT NOCOPY VARCHAR2
31 ,x_msg_count OUT NOCOPY NUMBER
32 ,x_msg_data OUT NOCOPY VARCHAR2
33 );
34
35
36 PROCEDURE Basic_Validation(
37 p_batch_id IN NUMBER
38 ,p_mode In Number
39 ,x_return_status OUT NOCOPY VARCHAR2
40 ,x_msg_count OUT NOCOPY NUMBER
41 ,x_msg_data OUT NOCOPY VARCHAR2);
42
43
44 Procedure validate_batch_id(p_batch_id In Number
45 ,x_return_status OUT NOCOPY VARCHAR2
46 ,x_msg_count OUT NOCOPY NUMBER
47 ,x_msg_data OUT NOCOPY VARCHAR2);
48
49 Procedure validateStatus(p_batch_id In Number);
50
51 Procedure valdiateBatSystem(p_batch_id IN Number);
52
53 Procedure validateBatSysEnt(p_batch_id IN Number);
54
55 Procedure Process_Pub_Status(
56 p_mode IN Number
57 ,p_batch_id IN Number
58 ,x_return_status OUT NOCOPY VARCHAR2
59 ,x_msg_count OUT NOCOPY NUMBER
60 ,x_msg_data OUT NOCOPY VARCHAR2
61 );
62
63 FUNCTION calc_return_status(p_batch_id IN NUMBER) RETURN VARCHAR2;
64 --end procedural declaration
65
66 PROCEDURE add_derived_entities
67 (
68 der_bat_ent_objs IN TBL_OF_BAT_ENT_OBJ_TYPE
69 ,x_return_status OUT NOCOPY VARCHAR2
70 ,x_msg_count OUT NOCOPY NUMBER
71 ,x_msg_data OUT NOCOPY VARCHAR2
72 )
73 IS
74 user_entered CHAR1_ARR_TBL_TYPE;
75 pk1_value CHAR150_ARR_TBL_TYPE;
76 pk2_value CHAR150_ARR_TBL_TYPE;
77 pk3_value CHAR150_ARR_TBL_TYPE;
78 pk4_value CHAR150_ARR_TBL_TYPE;
79 pk5_value CHAR150_ARR_TBL_TYPE;
80
81 -- Adding column for entity_type_id . Added for Metadata Sync
82
83 entity_type_id NUMBER_ARR_TBL_TYPE;
84
85 l_batch_id NUMBER;
86 EGO_NO_DERIVED_ENTITIES EXCEPTION;
87 EGO_PUB_BATCHID_NULL EXCEPTION;
88 l_stmt_num NUMBER;
89 --i NUMBER; --FOR LOOP
90
91
92 BEGIN --Procedure add_derived_entities
93
94 l_stmt_num := 0;
95 delete from Ego_Publication_Batch_GT;
96 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
97 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
98 ,p_message => 'Enter EGO_PUB_FWK_PK.add_derived_entities ... '
99 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
100
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 --check if der_bat_ent_objs is null
104 --return error message
105 IF ( der_bat_ent_objs.count() = 0 OR der_bat_ent_objs IS NULL ) THEN
106 RAISE EGO_NO_DERIVED_ENTITIES;
107 END IF;
108
109 l_batch_id := der_bat_ent_objs(1).batch_id;
110
111 -- Adding column for entity_type_id . Added for Metadata Sync
112
113
114 l_stmt_num := 5;
115 validate_batch_id(p_batch_id => l_batch_id
116 ,x_return_status => x_return_status
117 ,x_msg_count => x_msg_count
118 ,x_msg_data => x_msg_data
119 );
120
121 If (x_return_status = C_FAILED) THEN
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 return;
124 Elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 End if;
127
128 /* IF ( l_batch_id IS NULL ) THEN
129 RAISE EGO_PUB_BATCHID_NULL;
130 END IF;*/
131
132 l_stmt_num := 10;
133
134 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
135 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
136 ,p_message =>' i --' ||
137 ' pk1_value(i) --' ||
138 ' pk2_value(i) --' ||
139 ' pk3_value(i) --' ||
140 ' pk4_value(i) --' ||
141 ' pk5_value(i) --' ||
142 ' entity_type_id(i) --' ||
143 ' user_entered(i)' );
144 FOR i IN der_bat_ent_objs.FIRST..der_bat_ent_objs.LAST LOOP
145 pk1_value(i) := der_bat_ent_objs(i).pk1_value;
146 pk2_value(i) := der_bat_ent_objs(i).pk2_value;
147 pk3_value(i) := der_bat_ent_objs(i).pk3_value;
148 pk4_value(i) := der_bat_ent_objs(i).pk4_value;
149 pk5_value(i) := der_bat_ent_objs(i).pk5_value;
150 user_entered(i) := nvl(der_bat_ent_objs(i).user_entered, C_NO);
151
152 -- Adding column for entity_type_id . Added for Metadata Sync
153
154 entity_type_id(i) := der_bat_ent_objs(i).entity_type_id;
155
156
157 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
158 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
159 ,p_message => i || ' --' ||
160 der_bat_ent_objs(i).pk1_value || ' --' ||
161 der_bat_ent_objs(i).pk2_value || ' --' ||
162 der_bat_ent_objs(i).pk3_value || ' --' ||
163 der_bat_ent_objs(i).pk4_value || ' --' ||
164 der_bat_ent_objs(i).pk5_value || ' --' ||
165 der_bat_ent_objs(i).entity_type_id || ' --' ||
166 der_bat_ent_objs(i).user_entered );
167
168 END LOOP;
169
170 l_stmt_num := 20;
171
172 -- Adding column for entity_type_id . Added for Metadata Sync
173 FORALL i IN pk1_value.FIRST..pk1_value.LAST
174 INSERT INTO EGO_PUBLICATION_BATCH_GT
175 (
176 Batch_id
177 ,Pk1_Value
178 ,Pk2_value
179 ,Pk3_value
180 ,Pk4_Value
181 ,Pk5_value
182 ,user_entered
183 ,entity_type_id
184 ,LAST_UPDATE_DATE
185 ,LAST_UPDATED_BY
186 ,CREATION_DATE
187 ,CREATED_BY
188 ,LAST_UPDATE_LOGIN
189
190 )
191 VALUES
192 (
193 l_batch_id
194 ,pk1_value(i)
195 ,pk2_value(i)
196 ,pk3_value(i)
197 ,pk4_value(i)
198 ,pk5_value(i)
199 ,user_entered(i)
200 ,entity_type_id(i)
201 ,SYSDATE
202 ,FND_GLOBAL.USER_ID
203 ,SYSDATE
204 ,FND_GLOBAL.USER_ID
205 ,FND_GLOBAL.LOGIN_ID
206 );
207 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
208 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
209 ,p_message => 'lStmtNum=' || to_char(l_stmt_num)
210 ||' ' || sql%rowcount || ' rows are inserted into EGO_PUBLICATION_BATCH_GT.');
211
212
213 l_stmt_num := 30;
214 process_entities ( p_batch_id => l_batch_id
215 ,x_return_status => x_return_status
216 ,x_msg_count => x_msg_count
217 ,x_msg_data => x_msg_data);
218
219 --we need to check return messages
220 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
221 RAISE FND_API.G_EXC_ERROR;
222 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225
226 delete from EGO_PUBLICATION_BATCH_GT;
227
228 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
229 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
230 ,p_message => 'Exit EGO_PUB_FWK_PK.add_derived_entities successfully... '
231 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
232
233 EXCEPTION
234 WHEN EGO_NO_DERIVED_ENTITIES THEN
235 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
236 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
237 ,p_message => 'Exception EGO_NO_DERIVED_ENTITIES in stmt num: ' || l_stmt_num|| ': '||'sqlerrm=>' ||sqlerrm);
238
239
240 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
241 ,p_message =>'EGO_NO_DERIVED_ENTITIES');
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
244 ,p_msg_data => x_msg_data );
245 WHEN EGO_PUB_BATCHID_NULL THEN
246 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
247 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
248 ,p_message => 'Exception EGO_PUB_BATCHID_NULL in stmt num: ' || l_stmt_num|| ': '||'sqlerrm=>' ||sqlerrm );
249 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
250 ,p_message =>'EGO_PUB_BATCHID_NULL');
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
253 ,p_msg_data => x_msg_data );
254
255 WHEN FND_API.G_EXC_ERROR THEN
256 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
257 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
258 ,p_message => 'Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
259
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
262 ,p_msg_data => x_msg_data );
263
264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
265 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
266 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
267 ,p_message => 'Unexpedted Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
269 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
270 ,p_msg_data => x_msg_data );
271
272 WHEN OTHERS THEN
273 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
274 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
275 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
278 ,p_msg_data => x_msg_data );
279
280 END add_derived_entities;
281
282
283 PROCEDURE process_entities
284 ( p_batch_id IN NUMBER
285 ,x_return_status OUT NOCOPY VARCHAR2
286 ,x_msg_count OUT NOCOPY NUMBER
287 ,X_msg_data OUT NOCOPY VARCHAR2
288 )
289 IS
290
291
292 l_bat_ent_obj_id NUMBER;
293 l_system_code VARCHAR2(30); --Fixed number 30 is fine? Is it possible to use TYPE%cur_systems
294
295 t_batch_ent_obj_id NUMBER_ARR_TBL_TYPE;
296 t_system_code CHAR30_ARR_TBL_TYPE;
297
298 EGO_NO_BATCH_SYSTEMS EXCEPTION;
299 l_stmt_num NUMBER;
300 --i NUMBER; --FOR LOOP
301
302 BEGIN
303 l_stmt_num := 0;
304 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
305 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
306 ,p_message => 'Enter EGO_PUB_FWK_PK.process_entities ... '
307 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
308
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310
311 -- Adding column for entity_type_id . Added for Metadata Sync
312
313 l_stmt_num := 10;
314 INSERT INTO Ego_Pub_Bat_Ent_Objs_B
315 ( Batch_Entity_Object_id
316 ,Batch_id
317 ,PK1_value
318 ,PK2_value
319 ,PK3_value
320 ,PK4_value
321 ,PK5_value
322 ,user_entered
323 ,entity_type_id
324 ,LAST_UPDATE_DATE
325 ,LAST_UPDATED_BY
326 ,CREATION_DATE
327 ,CREATED_BY
328 ,LAST_UPDATE_LOGIN
329 ,object_version_number
330 )
331 SELECT
332 Ego_Pub_Bat_Ent_Objs_S1.NEXTVAL
333 ,Batch_Id
334 ,Pk1_Value
335 ,Pk2_Value
336 ,Pk3_Value
337 ,Pk4_Value
338 ,Pk5_Value
339 ,User_Entered
340 ,Entity_Type_Id
341 ,SYSDATE
342 ,FND_GLOBAL.USER_ID
343 ,SYSDATE
344 ,FND_GLOBAL.USER_ID
345 ,FND_GLOBAL.LOGIN_ID
346 ,1
347 FROM Ego_Publication_Batch_GT
348 WHERE Batch_Id = p_batch_id;
349 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
350 ,p_module => 'EGO_PUB_FWK_PK.process_entities'
351 ,p_message => 'lStmtNum=' || to_char(l_stmt_num)
352 ||' ' || sql%rowcount || ' rows are inserted into EGO_PUB_BAT_ENT_OBJS_B.');
353
354
355 l_stmt_num := 20;
356 SELECT Batch_Entity_Object_id
357 ,System_Code
358 BULK COLLECT INTO
359 t_batch_ent_obj_id
360 ,t_system_code
361 FROM Ego_Pub_Bat_Ent_Objs_B EO,
362 EGO_PUB_BAT_SYSTEMS_B SYS
363 WHERE EO.Batch_Id = p_batch_id
364 AND EO.User_Entered = C_NO
365 AND SYS.Batch_Id = EO.Batch_Id;
366
367 IF t_system_code.count() = 0 THEN
368 RAISE EGO_NO_BATCH_SYSTEMS;
369 END IF;
370
371 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
372 ,p_module => 'EGO_PUB_FWK_PK.process_entities'
373 ,p_message => 'i'|| '---' || 't_batch_ent_obj_id(i)' || '---'|| 't_system_code(i)'
374 );
375 FOR i in t_batch_ent_obj_id.FIRST..t_batch_ent_obj_id.LAST
376 LOOP
377 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
378 ,p_module => 'EGO_PUB_FWK_PK.process_entities'
379 ,p_message => i ||'---' || t_batch_ent_obj_id(i) || '---'|| t_system_code(i)
380 );
381 END LOOP; --for debugging message
382
383
384 l_stmt_num := 30;
385 FORALL i in t_batch_ent_obj_id.FIRST..t_batch_ent_obj_id.LAST
386 INSERT INTO Ego_Pub_Bat_Status_B
387 (
388 Batch_Id
389 ,System_Code
390 ,Batch_Entity_Object_id
391 ,Status_Code
392 ,LAST_UPDATE_DATE
393 ,LAST_UPDATED_BY
394 ,CREATION_DATE
395 ,CREATED_BY
396 ,LAST_UPDATE_LOGIN
397 ,object_version_number
398 )
399 VALUES
400 (
401 p_batch_id
402 ,t_system_code(i)
403 ,t_batch_ent_obj_id(i)
404 ,C_IN_PROCESS
405 ,SYSDATE
406 ,FND_GLOBAL.USER_ID
407 ,SYSDATE
408 ,FND_GLOBAL.USER_ID
409 ,FND_GLOBAL.LOGIN_ID
410 ,1
411 );
412 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
413 ,p_module => 'EGO_PUB_FWK_PK.process_entities'
414 ,p_message => 'lStmtNum=' || to_char(l_stmt_num)
415 ||' ' || sql%rowcount || ' rows are inserted into EGO_PUB_BAT_STATUS_B.');
416
417 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
418 ,p_module => 'EGO_PUB_FWK_PK.add_derived_entities'
419 ,p_message => 'Exit EGO_PUB_FWK_PK.process_entities successfully... '
420 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
421
422 EXCEPTION
423 WHEN EGO_NO_BATCH_SYSTEMS THEN
424 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
425 ,p_module => 'EGO_PUB_FWK_PK.process_entities'
426 ,p_message => 'Exception EGO_NO_BATCH_SYSTEMS in stmt num: '||l_stmt_num || ':' ||'sqlerrm=>'|| sqlerrm);
427 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
428 ,p_message =>'EGO_NO_BATCH_SYSTEMS');
429 x_return_status := FND_API.G_RET_STS_ERROR;
430 EGO_UTIL_PK.count_and_get
431 (p_msg_count => x_msg_count
432 ,p_msg_data => x_msg_data );
433
434 WHEN OTHERS THEN
435 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
436 ,p_module => 'EGO_PUB_FWK_PK.process_entities'
437 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 EGO_UTIL_PK.count_and_get
440 (p_msg_count => x_msg_count
441 ,p_msg_data => x_msg_data );
442
443
444 END process_entities;
445
446 --Returns S =Success
447 -- F = failure
448 -- W = warning
449 -- U= Unexpected Error
450 Procedure Update_Pub_Status_Thru_AIA(p_batch_id IN NUMBER
451 ,p_mode In Number
452 ,x_return_status OUT NOCOPY VARCHAR2
453 ,x_msg_count OUT NOCOPY NUMBER
454 ,x_msg_data OUT NOCOPY VARCHAR2)
455 IS
456 l_stmt_num NUMBER;
457 l_ret_status VARCHAR2(1);
458
459 dbg_pk1_value CHAR150_ARR_TBL_TYPE;
460 dbg_pk2_value CHAR150_ARR_TBL_TYPE;
461 dbg_pk3_value CHAR150_ARR_TBL_TYPE;
462 dbg_pk4_value CHAR150_ARR_TBL_TYPE;
463 dbg_pk5_value CHAR150_ARR_TBL_TYPE;
464 t_dbg_batchid NUMBER_ARR_TBL_TYPE;
465 t_dbg_system_code CHAR30_ARR_TBL_TYPE;
466 t_dbg_err_msg_lang CHAR4_ARR_TBL_TYPE;
467 t_dbg_status CHAR1_ARR_TBL_TYPE;
468 t_dbg_msg CHAR_ARR_TBL_TYPE;
469 t_dbg_err_code CHAR_ARR_TBL_TYPE;
470
471 BEGIN
472 l_stmt_num := 0;
473
474 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
475 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
476 ,p_message => 'Enter EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA ... '
477 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
478
479
480
481
482
483
484 Basic_Validation(
485 p_batch_id => p_batch_id
486 ,p_mode => p_mode
487 ,x_return_status => x_return_status
488 ,x_msg_count => x_msg_count
489 ,x_msg_data =>x_msg_data);
490
491 If (x_return_status = C_FAILED) THEN
492 return;
493 Elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
494 --revisit
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 End if;
497
498 --debug statement to look at Input GT data
499 Select Batch_id
500 ,System_Code
501 ,Pk1_Value
502 ,Pk2_value
503 ,Pk3_value
504 ,Pk4_Value
505 ,Pk5_value
506 ,Status
507 ,Message
508 BULK COLLECT INTO
509 t_dbg_batchid
510 ,t_dbg_system_code
511 ,dbg_pk1_value
512 ,dbg_pk2_value
513 ,dbg_pk3_value
514 ,dbg_pk4_value
515 ,dbg_pk5_value
516 ,t_dbg_status
517 ,t_dbg_msg
518 FROM Ego_Publication_Batch_GT
519 WHERE Batch_id = p_batch_id;
520
521
522 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
523 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
524 ,p_message =>' i --' ||
525 ' batch_id(i) --' ||
526 ' pk1_value(i) --' ||
527 ' pk2_value(i) --' ||
528 ' pk3_value(i) --' ||
529 ' pk4_value(i) --' ||
530 ' pk5_value(i) --' ||
531 ' system_code(i) --' ||
532 ' status(i) --' ||
533 ' msg(i)--'
534 );
535
536 FOR i IN t_dbg_batchid.FIRST..t_dbg_batchid.LAST LOOP
537 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
538 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
539 ,p_message => i || ' --' ||
540 t_dbg_batchid(i) || ' --' ||
541 dbg_pk1_value(i) || ' --' ||
542 dbg_pk2_value(i) || ' --' ||
543 dbg_pk3_value(i) || ' --' ||
544 dbg_pk4_value(i) || ' --' ||
545 dbg_pk5_value(i) || ' --' ||
546 t_dbg_system_code(i) || ' --' ||
547 t_dbg_status(i)|| ' --' ||
548 t_dbg_msg(i)
549 );
550 END LOOP;
551
552 Process_Pub_Status(
553 p_mode => p_mode
554 ,p_batch_id => p_batch_id
555 ,x_return_status => l_ret_status
556 ,x_msg_count => x_msg_count
557 ,x_msg_data => x_msg_data)
558 ;
559
560 IF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
561 x_return_status := l_ret_status;
562 return;
563 END IF;
564
565 x_return_status := calc_return_status(p_batch_id=>p_batch_id);
566
567 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
568 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
569 ,p_message => 'Exit EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA successfully... '
570 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
571
572
573
574
575 Exception
576
577 When OTHERS Then
578 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
579 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
580 ,p_message => 'Unexpedted Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
583 ,p_msg_data => x_msg_data );
584
585
586 END Update_Pub_Status_Thru_AIA;
587
588 FUNCTION calc_return_status(p_batch_id IN NUMBER)
589 RETURN VARCHAR2
590 IS
591 l_ct_S NUMBER ;
592 l_ct_bat NUMBER;
593 l_stmt_num NUMBER;
594 BEGIN
595 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
596 ,p_module => 'EGO_PUB_FWK_PK.calc_return_status'
597 ,p_message => 'Enter EGO_PUB_FWK_PK.calc_return_status ... '
598 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
599
600 BEGIN
601 l_stmt_num := 10;
602 Select count(*)
603 into l_ct_S
604 from ego_publication_batch_gt
605 WHERE batch_id = p_batch_id
606 and RETURN_STATUS = C_SUCCESS;
607 EXCEPTION
608 WHEN no_data_found THEN
609 return C_FAILED;
610 END;
611
612 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_STATEMENT
613 ,p_module => 'EGO_PUB_FWK_PK.calc_return_status'
614 ,p_message => 'row_count_Success=> '||l_ct_S
615 );
616
617 IF l_ct_S = 0 THEN
618 l_stmt_num := 20;
619 return C_FAILED;
620 END IF;
621
622 l_stmt_num := 30;
623 select count(*)
624 into l_ct_bat
625 from ego_publication_batch_gt
626 WHERE batch_id = p_batch_id;
627
628 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_STATEMENT
629 ,p_module => 'EGO_PUB_FWK_PK.calc_return_status'
630 ,p_message => 'total rows=> '||l_ct_bat
631 );
632
633 IF l_ct_bat = l_ct_S THEN
634 l_stmt_num := 40;
635 return C_SUCCESS;
636 ELSE
637 l_stmt_num := 50;
638 return C_WARNING;
639
640 END IF;
641
642 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
643 ,p_module => 'EGO_PUB_FWK_PK.calc_return_status'
644 ,p_message => 'Exit EGO_PUB_FWK_PK.calc_return_status ... '
645 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
646
647 END calc_return_status;
648
649
650 --Basic Validation Validates batchId and Mode
651 PROCEDURE Basic_Validation(
652 p_batch_id IN NUMBER
653 ,p_mode In Number
654 ,x_return_status OUT NOCOPY VARCHAR2
655 ,x_msg_count OUT NOCOPY NUMBER
656 ,x_msg_data OUT NOCOPY VARCHAR2)
657 IS
658 -- l_ret_status VARCHAR2(1);
659 l_stmt_num NUMBER;
660
661 EGO_INVALID_MODE Exception;
662 BEGIN
663 l_stmt_num := 0;
664 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
665 ,p_module => 'EGO_PUB_FWK_PK.Basic_Validation'
666 ,p_message => 'Enter EGO_PUB_FWK_PK.Basic_Validation ... '
667 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
668
669 x_return_status := C_SUCCESS;
670
671 l_stmt_num := 10;
672 validate_batch_id(p_batch_id => p_batch_id
673 ,x_return_status => x_return_status
674 ,x_msg_count => x_msg_count
675 ,x_msg_data => x_msg_data
676 );
677
678 If (x_return_status = C_FAILED) THEN
679 return;
680 Elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 End if;
683
684 l_stmt_num := 20;
685 -- 8773131 for mode validation, added mode C_BATCH_MODE
686 If ( nvl(p_mode,-99) not in (C_BATCH_MODE, C_BATCH_SYSTEM_MODE,
687 C_BATCH_SYSTEM_ENTITY_MODE)) THEN
688 Raise EGO_INVALID_MODE;
689 END IF;
690
691 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
692 ,p_module => 'EGO_PUB_FWK_PK.Basic_Validation'
693 ,p_message => 'Exit EGO_PUB_FWK_PK.Basic_Validation ... '
694 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
695
696 Exception
697
698 When FND_API.G_EXC_UNEXPECTED_ERROR Then
699 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
700 ,p_module => 'EGO_PUB_FWK_PK.Basic_validation'
701 ,p_message => 'Unexpedted Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
704 ,p_msg_data => x_msg_data );
705
706
707 When EGO_INVALID_MODE Then
708 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
709 ,p_module => 'EGO_PUB_FWK_PK.Basic_validation'
710 ,p_message => 'Inavlid Batch mode in stmt num: ' || l_stmt_num );
711
712 l_stmt_num := 30;
713 Update ego_publication_batch_gt
714 set return_status = C_FAILED,
715 return_error_code = 'EGO_INVALID_MODE',
716 return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_MODE'),
717 ret_err_msg_lang = USERENV('LANG')
718 where batch_id = p_batch_id;
719
720 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
721 ,p_message =>'EGO_INVALID_MODE');
722 EGO_UTIL_PK.count_and_get
723 (p_msg_count => x_msg_count
724 ,p_msg_data => x_msg_data );
725
726
727
728
729 x_return_status := C_FAILED;
730
731 END Basic_Validation;
732
733 --validates batch id
734 --
735 Procedure validate_batch_id(p_batch_id In Number
736 ,x_return_status OUT NOCOPY VARCHAR2
737 ,x_msg_count OUT NOCOPY NUMBER
738 ,x_msg_data OUT NOCOPY VARCHAR2)
739 IS
740 l_batch_id NUMBER;
741 l_stmt_num NUMBER;
742
743 EGO_NULL_BATCH_ID EXCEPTION;
744 EGO_NO_HEADER EXCEPTION;
745 BEGIN
746 l_stmt_num := 0;
747 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
748 ,p_module => 'EGO_PUB_FWK_PK.validate_batch_id'
749 ,p_message => 'Enter EGO_PUB_FWK_PK.validate_batch_id ... '
750 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
751
752 x_return_status := C_SUCCESS;
753
754 If p_batch_id is null THEN
755 Raise EGO_NULL_BATCH_ID;
756 END IF;
757
758
759
760 l_stmt_num := 10;
761 Begin
762
763 Select batch_id
764 into l_batch_id
765 from Ego_Pub_Bat_Hdr_B
766 where batch_id = p_batch_Id;
767 Exception
768 When No_Data_Found THEN
769 RAISE EGO_NO_HEADER;
770 End;
771
772 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
773 ,p_module => 'EGO_PUB_FWK_PK.validate_batch_id'
774 ,p_message => 'Exit EGO_PUB_FWK_PK.validate_batch_id ... '
775 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
776
777
778
779 EXCEPTION
780 When EGO_NULL_BATCH_ID Then
781 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
782 ,p_module => 'EGO_PUB_FWK_PK.validate_batch_id'
783 ,p_message => 'Exception EGO_PUB_BATCHID_NULL in stmt num: '||l_stmt_num || ':' ||'sqlerrm=>'|| sqlerrm);
784
785
786 l_stmt_num := 20;
787 Update ego_publication_batch_gt
788 set return_status = C_FAILED,
789 return_error_code = 'EGO_PUB_BATCHID_NULL',
790 return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_PUB_BATCHID_NULL'),
791 ret_err_msg_lang = USERENV('LANG')
792 where batch_id =p_batch_id;
793
794 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
795 ,p_message =>'EGO_PUB_BATCHID_NULL');
796 EGO_UTIL_PK.count_and_get
797 (p_msg_count => x_msg_count
798 ,p_msg_data => x_msg_data );
799
800 x_return_status := C_FAILED;
801
802 WHEN EGO_NO_HEADER THEN
803
804 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
805 ,p_module => 'EGO_PUB_FWK_PK.validate_batch_id'
806 ,p_message => 'Exception EGO_NO_HEADER in stmt num: '||l_stmt_num || ':' ||'sqlerrm=>'|| sqlerrm);
807
808
809 l_stmt_num := 30;
810 Update ego_publication_batch_gt
811 set return_status = C_FAILED,
812 return_error_code = 'EGO_NO_HEADER',
813 return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_NO_HEADER'),
814 ret_err_msg_lang = USERENV('LANG')
815 where batch_id =p_batch_id;
816
817 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
818 ,p_message =>'EGO_NO_HEADER');
819 EGO_UTIL_PK.count_and_get
820 (p_msg_count => x_msg_count
821 ,p_msg_data => x_msg_data );
822
823
824
825
826 x_return_status := C_FAILED;
827 WHEN OTHERS THEN
828 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
829 ,p_module => 'EGO_PUB_FWK_PK.validate_batch_id'
830 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
831
832
833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 EGO_UTIL_PK.count_and_get
835 (p_msg_count => x_msg_count
836 ,p_msg_data => x_msg_data );
837
838 END validate_batch_id;
839
840 Procedure validateStatus(p_batch_id In Number)
841 IS
842 l_stmt_num NUMBER;
843 BEGIN
844 l_stmt_num := 0;
845 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
846 ,p_module => 'EGO_PUB_FWK_PK.validateStatus'
847 ,p_message => 'Enter EGO_PUB_FWK_PK.validateStatus ... '
848 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
849
850 l_stmt_num := 10;
851 Update Ego_Publication_Batch_GT
852 Set Return_status = C_FAILED,
853 process_flag = 1,
854 return_error_code = 'EGO_INVALID_STATUS_CODE',
855 return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_STATUS_CODE'),
856 ret_err_msg_lang = USERENV('LANG')
857 where Status not in (select lookup_code
858 from fnd_lookups
859 where Lookup_Type = 'EGO_PUBLICATION_STATUS')
860 and batch_id = p_batch_id;
861
862 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
863 ,p_module => 'EGO_PUB_FWK_PK.validateStatus'
864 ,p_message => 'Exit EGO_PUB_FWK_PK.validateStatus ... '
865 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
866
867
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
872 ,p_module => 'EGO_PUB_FWK_PK.validate_status'
873 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
874
875
876
877 RAISE;
878
879
880 END validateStatus;
881
882
883 Procedure valdiateBatSystem(p_batch_id IN Number)
884 IS
885
886 l_stmt_num NUMBER;
887 BEGIN
888 l_stmt_num := 0;
889 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
890 ,p_module => 'EGO_PUB_FWK_PK.valdiateBatSystem'
891 ,p_message => 'Enter EGO_PUB_FWK_PK.valdiateBatSystem ... '
892 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
893
894
895 l_stmt_num := 10;
896 Update Ego_Publication_Batch_GT
897 Set Return_status = C_FAILED,
898 process_flag = 2,
899 return_error_code = 'EGO_INVALID_BAT_SYS',
900 return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_BAT_SYS'),
901 ret_err_msg_lang = USERENV('LANG')
902 where (batch_id,
903 system_Code) not in (select batch_id,
904 system_code
905 from Ego_Pub_Bat_status_b
906 where batch_id = p_batch_id)
907 and batch_id = p_batch_id
908 and process_flag is null;
909
910 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
911 ,p_module => 'EGO_PUB_FWK_PK.valdiateBatSystem'
912 ,p_message => 'Exit EGO_PUB_FWK_PK.valdiateBatSystem ... '
913 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
914
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
919 ,p_module => 'EGO_PUB_FWK_PK.valdiateBatSystem'
920 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
921
922
923
924 RAISE;
925
926 END valdiateBatSystem;
927
928
929
930 Procedure validateBatSysEnt(p_batch_id IN Number)
931 IS
932 l_stmt_num NUMBER;
933
934 BEGIN
935 l_stmt_num := 0;
936 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
937 ,p_module => 'EGO_PUB_FWK_PK.validateBatSysEnt'
938 ,p_message => 'Enter EGO_PUB_FWK_PK.validateBatSysEnt ... '
939 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
940
941
942 l_stmt_num := 10;
943
944 UPDATE ego_publication_batch_gt gt
945 SET batch_entity_object_id =
946 (SELECT eo.batch_entity_object_id
947 FROM ego_pub_bat_ent_objs_b eo
948 WHERE batch_id = gt.batch_id
949 AND eo.pk1_value = nvl(gt.pk1_value, -99)
950 AND nvl(eo.pk2_value, -99) = nvl(gt.pk2_value, -99)
951 AND nvl(eo.pk3_value, -99) = nvl(gt.pk3_value, -99)
952 AND nvl(eo.pk4_value, -99) = nvl(gt.pk4_value, -99)
953 AND nvl(eo.pk5_value, -99) = nvl(gt.pk5_value, -99)
954 and gt.batch_id = p_batch_id);
955
956 l_stmt_num := 20;
957 UPDATE ego_publication_batch_gt
958 SET return_status = C_FAILED,
959 process_flag = 3,
960 return_error_code = 'EGO_INVALID_BAT_SYS_ENT',
961 return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_BAT_SYS_ENT'),
962 ret_err_msg_lang = USERENV('LANG')
963 WHERE ( nvl(batch_entity_object_id,-99),
964 nvl(batch_id,-99),
965 nvl(system_code,-99)) NOT IN
966 (SELECT
967 batch_entity_object_id,
968 batch_id,
969 system_code
970 FROM ego_pub_bat_status_b
971 WHERE batch_id = p_batch_id)
972 AND batch_id = p_batch_id
973 and process_flag is null;
974
975 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
976 ,p_module => 'EGO_PUB_FWK_PK.validateBatSysEnt'
977 ,p_message => 'Exit EGO_PUB_FWK_PK.validateBatSysEnt ... '
978 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
979
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
984 ,p_module => 'EGO_PUB_FWK_PK.validateBatSysEnt'
985 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
986
987
988 RAISE;
989
990
991 END validateBatSysEnt;
992
993
994
995
996 PROCEDURE Process_Pub_Status(
997 p_mode IN Number
998 ,p_batch_id IN Number
999 ,x_return_status OUT NOCOPY VARCHAR2
1000 ,x_msg_count OUT NOCOPY NUMBER
1001 ,x_msg_data OUT NOCOPY VARCHAR2
1002 )
1003 IS
1004
1005 t_batch_ent_obj_id NUMBER_ARR_TBL_TYPE;
1006 t_batch_id NUMBER_ARR_TBL_TYPE;
1007 t_system_code CHAR30_ARR_TBL_TYPE;
1008 t_status CHAR1_ARR_TBL_TYPE;
1009 t_message CHAR_ARR_TBL_TYPE;
1010 l_stmt_num NUMBER;
1011 --i NUMBER; --FOR LOOP
1012 BEGIN
1013 l_stmt_num := 0;
1014 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1015 ,p_module => 'EGO_PUB_FWK_PK.Process_Pub_Status'
1016 ,p_message => 'Enter EGO_PUB_FWK_PK.Process_Pub_Status ... '
1017 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1018
1019 x_return_status := C_SUCCESS;
1020
1021 --Validity check of status in GT table against lookup
1022 l_stmt_num :=10;
1023 validateStatus(p_batch_id=>p_batch_id);
1024
1025 l_stmt_num :=20;
1026
1027 IF p_mode = C_BATCH_SYSTEM_MODE THEN
1028
1029 l_stmt_num := 30;
1030 valdiateBatSystem(p_batch_id =>p_batch_id);
1031 ELSIF p_mode = C_BATCH_SYSTEM_ENTITY_MODE THEN
1032
1033 l_stmt_num := 40;
1034 validateBatSysEnt(p_batch_id =>p_batch_id);
1035 END IF;
1036
1037 l_stmt_num:=50;
1038 SELECT batch_entity_object_id,
1039 batch_id,
1040 system_code,
1041 status,
1042 message
1043 BULK COLLECT INTO
1044 t_batch_ent_obj_id,
1045 t_batch_id,
1046 t_system_code,
1047 t_status,
1048 t_message
1049 from ego_publication_batch_gt
1050 where batch_id = p_batch_id
1051 and return_status is null
1052 and process_flag is null;
1053
1054 l_stmt_num := 60;
1055
1056 -- 8773131 added IF condition for mode BATCH_MODE
1057 IF p_mode = C_BATCH_MODE THEN
1058
1059 l_stmt_num := 65;
1060 FORALL i IN t_batch_id.FIRST .. t_batch_id.LAST
1061 UPDATE ego_pub_bat_status_b
1062 SET status_code = t_status(i),
1063 message = t_message(i)
1064 WHERE batch_id = t_batch_id(i);
1065
1066 ELSIF p_mode = C_BATCH_SYSTEM_MODE THEN
1067
1068 l_stmt_num := 70;
1069 FORALL i IN t_batch_id.FIRST .. t_batch_id.LAST
1070 UPDATE ego_pub_bat_status_b
1071 SET status_code = t_status(i),
1072 message = t_message(i)
1073 WHERE batch_id = t_batch_id(i)
1074 AND system_code = t_system_code(i);
1075
1076 ELSIF p_mode = C_BATCH_SYSTEM_ENTITY_MODE THEN
1077
1078 l_stmt_num := 80;
1079 FORALL i IN t_batch_id.FIRST .. t_batch_id.LAST
1080 UPDATE ego_pub_bat_status_b
1081 SET status_code = t_status(i),
1082 message = t_message(i)
1083 WHERE batch_entity_object_id = t_batch_ent_obj_id(i)
1084 AND batch_id = t_batch_id(i)
1085 AND system_code = t_system_code(i);
1086
1087 END IF;
1088
1089 l_stmt_num := 90;
1090 update ego_publication_batch_gt
1091 set return_status = C_SUCCESS
1092 where batch_id = p_batch_id
1093 and process_flag is null;
1094
1095 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1096 ,p_module => 'EGO_PUB_FWK_PK.Process_Pub_Status'
1097 ,p_message => 'Exit EGO_PUB_FWK_PK.Process_Pub_Status ... '
1098 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1099
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
1104 ,p_module => 'EGO_PUB_FWK_PK.Process_Pub_Status'
1105 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
1106
1107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1108 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1109 ,p_msg_data => x_msg_data );
1110
1111 END Process_Pub_Status;
1112
1113
1114 --Returns S =Success
1115 -- F = failure
1116 -- W = warning
1117 -- U= Unexpected Error
1118 PROCEDURE Update_Pub_Status ( p_batch_id IN NUMBER
1119 ,p_mode In Number
1120 ,p_bat_status_in IN TBL_OF_BAT_ENT_OBJ_STAT_TYPE
1121 ,x_bat_status_out OUT NOCOPY TBL_OF_BAT_ENT_OBJ_RSTS_TYPE
1122 ,x_return_status OUT NOCOPY VARCHAR2
1123 ,x_msg_count OUT NOCOPY NUMBER
1124 ,x_msg_data OUT NOCOPY VARCHAR2)
1125 IS
1126 --The following variables types should match with Ego_Publication_Batch_GT
1127 pk1_value CHAR150_ARR_TBL_TYPE;
1128 pk2_value CHAR150_ARR_TBL_TYPE;
1129 pk3_value CHAR150_ARR_TBL_TYPE;
1130 pk4_value CHAR150_ARR_TBL_TYPE;
1131 pk5_value CHAR150_ARR_TBL_TYPE;
1132 t_system_code CHAR30_ARR_TBL_TYPE;
1133 t_message CHAR_ARR_TBL_TYPE;
1134 t_status CHAR1_ARR_TBL_TYPE;
1135
1136 ret_pk1_value CHAR150_ARR_TBL_TYPE;
1137 ret_pk2_value CHAR150_ARR_TBL_TYPE;
1138 ret_pk3_value CHAR150_ARR_TBL_TYPE;
1139 ret_pk4_value CHAR150_ARR_TBL_TYPE;
1140 ret_pk5_value CHAR150_ARR_TBL_TYPE;
1141 t_ret_batchid NUMBER_ARR_TBL_TYPE;
1142 t_ret_system_code CHAR30_ARR_TBL_TYPE;
1143 t_ret_err_msg_lang CHAR4_ARR_TBL_TYPE;
1144 t_ret_status CHAR1_ARR_TBL_TYPE;
1145 t_ret_err_msg CHAR_ARR_TBL_TYPE;
1146 t_ret_err_code CHAR_ARR_TBL_TYPE;
1147
1148 --i NUMBER; --FOR LOOP
1149 l_stmt_num NUMBER;
1150 l_ret_status VARCHAR2(1);
1151
1152 EGO_NO_DATA EXCEPTION;
1153 EGO_NO_BAT_STS_IN EXCEPTION;
1154
1155 BEGIN
1156 delete from Ego_Publication_Batch_GT;
1157
1158 l_stmt_num := 0;
1159 EGO_COMMON_PVT.WRITE_DIAGNOSTIC( p_log_level => FND_LOG.LEVEL_PROCEDURE
1160 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1161 ,p_message => 'Enter EGO_PUB_FWK_PK.Update_Pub_Status ...'
1162 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1163
1164 l_stmt_num := 10;
1165 Basic_Validation ( p_batch_id => p_batch_id
1166 ,p_mode => p_mode
1167 ,x_return_status => x_return_status
1168 ,x_msg_count => x_msg_count
1169 ,x_msg_data => x_msg_data);
1170
1171 IF (x_return_status = C_FAILED) THEN
1172 return;
1173 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1174 --revisit
1175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1176 End if;
1177
1178 IF ( p_bat_status_in.count() = 0 OR p_bat_status_in IS NULL ) THEN
1179 RAISE EGO_NO_BAT_STS_IN; --!!!!!!!!! should I raise exception or put error message into GT
1180 END IF;
1181
1182 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
1183 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1184 ,p_message =>' i --' ||
1185 ' pk1_value(i) --' ||
1186 ' pk2_value(i) --' ||
1187 ' pk3_value(i) --' ||
1188 ' pk4_value(i) --' ||
1189 ' pk5_value(i) --' ||
1190 ' system_code(i) --' ||
1191 ' status(i) --' ||
1192 ' message(i)' );
1193 l_stmt_num := 20;
1194 FOR i IN p_bat_status_in.FIRST..p_bat_status_in.LAST LOOP
1195 pk1_value(i) := p_bat_status_in(i).pk1_value;
1196 pk2_value(i) := p_bat_status_in(i).pk2_value;
1197 pk3_value(i) := p_bat_status_in(i).pk3_value;
1198 pk4_value(i) := p_bat_status_in(i).pk4_value;
1199 pk5_value(i) := p_bat_status_in(i).pk5_value;
1200 t_system_code(i) := p_bat_status_in(i).system_code;
1201 t_status(i) := p_bat_status_in(i).status;
1202 t_message(i) := p_bat_status_in(i).message;
1203 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
1204 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1205 ,p_message => i || ' --' ||
1206 p_bat_status_in(i).pk1_value || ' --' ||
1207 p_bat_status_in(i).pk2_value || ' --' ||
1208 p_bat_status_in(i).pk3_value || ' --' ||
1209 p_bat_status_in(i).pk4_value || ' --' ||
1210 p_bat_status_in(i).pk5_value || ' --' ||
1211 p_bat_status_in(i).system_code || ' --' ||
1212 p_bat_status_in(i).status || ' --' ||
1213 p_bat_status_in(i).message);
1214 END LOOP;
1215
1216 l_stmt_num := 30;
1217 FORALL i IN t_system_code.FIRST..t_system_code.LAST
1218 INSERT INTO EGO_PUBLICATION_BATCH_GT
1219 (
1220 Batch_id
1221 ,pk1_value
1222 ,pk2_value
1223 ,pk3_value
1224 ,pk4_value
1225 ,pk5_value
1226 ,system_code
1227 ,status
1228 ,message
1229 ,LAST_UPDATE_DATE
1230 ,LAST_UPDATED_BY
1231 ,CREATION_DATE
1232 ,CREATED_BY
1233 ,LAST_UPDATE_LOGIN
1234 )
1235 VALUES
1236 (
1237 p_batch_id
1238 ,pk1_value(i)
1239 ,pk2_value(i)
1240 ,pk3_value(i)
1241 ,pk4_value(i)
1242 ,pk5_value(i)
1243 ,t_system_code(i)
1244 ,t_status(i)
1245 ,t_message(i)
1246 ,SYSDATE
1247 ,FND_GLOBAL.USER_ID
1248 ,SYSDATE
1249 ,FND_GLOBAL.USER_ID
1250 ,FND_GLOBAL.LOGIN_ID
1251 );
1252 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
1253 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1254 ,p_message => 'lStmtNum=' || to_char(l_stmt_num)
1255 ||' ' ||sql%rowcount || ' rows are inserted into EGO_PUBLICATION_BATCH_GT.');
1256
1257
1258 l_stmt_num := 40;
1259 Process_Pub_Status( p_mode => p_mode
1260 ,p_batch_id => p_batch_id
1261 ,x_return_status => l_ret_status
1262 ,x_msg_count => x_msg_count
1263 ,x_msg_data => x_msg_data);
1264
1265 IF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1266 x_return_status := l_ret_status;
1267 return;
1268 END IF;
1269 x_return_status := calc_return_status(p_batch_id=>p_batch_id);
1270
1271 l_stmt_num := 50;
1272
1273 Select Batch_id
1274 ,System_Code
1275 ,Pk1_Value
1276 ,Pk2_value
1277 ,Pk3_value
1278 ,Pk4_Value
1279 ,Pk5_value
1280 ,Return_Status
1281 ,Return_Error_Message
1282 ,RETURN_ERROR_CODE
1283 ,RET_ERR_MSG_LANG
1284 BULK COLLECT INTO
1285 t_ret_batchid
1286 ,t_ret_system_code
1287 ,ret_pk1_value
1288 ,ret_pk2_value
1289 ,ret_pk3_value
1290 ,ret_pk4_value
1291 ,ret_pk5_value
1292 ,t_ret_status
1293 ,t_ret_err_msg
1294 ,t_ret_err_code
1295 ,t_ret_err_msg_lang
1296 FROM Ego_Publication_Batch_GT
1297 WHERE Batch_id = p_batch_id;
1298
1299
1300 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
1301 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1302 ,p_message =>' i --' ||
1303 ' batch_id(i) --' ||
1304 ' pk1_value(i) --' ||
1305 ' pk2_value(i) --' ||
1306 ' pk3_value(i) --' ||
1307 ' pk4_value(i) --' ||
1308 ' pk5_value(i) --' ||
1309 ' system_code(i) --' ||
1310 ' ret_status(i) --' ||
1311 ' ret_err_msg(i) --' ||
1312 ' ret_err_code(i) --' ||
1313 ' ret_err_msg_lang(i)' );
1314 l_stmt_num := 60;
1315 FOR i IN t_ret_batchid.FIRST..t_ret_batchid.LAST LOOP
1316 x_bat_status_out(i).batch_id := t_ret_batchid(i);
1317 x_bat_status_out(i).pk1_value := ret_pk1_value(i);
1318 x_bat_status_out(i).pk2_value := ret_pk2_value(i);
1319 x_bat_status_out(i).pk3_value := ret_pk3_value(i);
1320 x_bat_status_out(i).pk4_value := ret_pk4_value(i);
1321 x_bat_status_out(i).pk5_value := ret_pk5_value(i);
1322 x_bat_status_out(i).system_code := t_ret_system_code(i);
1323 x_bat_status_out(i).ret_status := t_ret_status(i);
1324 x_bat_status_out(i).ret_err_msg := t_ret_err_msg(i);
1325 x_bat_status_out(i).ret_err_code := t_ret_err_code(i);
1326 x_bat_status_out(i).ret_err_msg_lang := t_ret_err_msg_lang(i);
1327 EGO_COMMON_PVT.WRITE_DIAGNOSTIC ( p_log_level => FND_LOG.LEVEL_STATEMENT
1328 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1329 ,p_message => i || ' --' ||
1330 t_ret_batchid(i) || ' --' ||
1331 ret_pk1_value(i) || ' --' ||
1332 ret_pk2_value(i) || ' --' ||
1333 ret_pk3_value(i) || ' --' ||
1334 ret_pk4_value(i) || ' --' ||
1335 ret_pk5_value(i) || ' --' ||
1336 t_ret_system_code(i) || ' --' ||
1337 t_ret_status(i)|| ' --' ||
1338 t_ret_err_msg(i) || ' --' ||
1339 t_ret_err_code(i) || ' --' ||
1340 t_ret_err_msg_lang(i) );
1341 END LOOP;
1342
1343 delete from Ego_Publication_Batch_GT;
1344
1345 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1346 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1347 ,p_message => 'Exit EGO_PUB_FWK_PK.Update_Pub_Status successfully... '
1348 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
1349
1350 EXCEPTION
1351 WHEN EGO_NO_BAT_STS_IN THEN
1352 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_EXCEPTION
1353 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1354 ,p_message => 'Exception EGO_NO_BAT_STS_IN in stmt num: ' || l_stmt_num|| ': '||'sqlerrm=>' ||sqlerrm);
1355
1356
1357 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
1358 ,p_message =>'EGO_NO_BAT_STS_IN');
1359 x_return_status := FND_API.G_RET_STS_ERROR;
1360 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1361 ,p_msg_data => x_msg_data );
1362 WHEN OTHERS THEN
1363 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
1364 ,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
1365 ,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
1366
1367
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1369 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1370 ,p_msg_data => x_msg_data );
1371
1372
1373 END Update_Pub_Status;
1374
1375 Procedure DeleteGTTableData(x_return_status OUT NOCOPY VARCHAR2)
1376 IS
1377
1378 BEGIN
1379 x_return_status := FND_API.G_RET_STS_SUCCESS;
1380
1381 Delete from Ego_Publication_Batch_GT;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
1386 ,p_module => 'EGO_PUB_FWK_PK.DeleteGTTableData'
1387 ,p_message => 'sqlerrm=>' ||sqlerrm );
1388
1389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1390 END DeleteGTTableData;
1391
1392
1393 -- public apis
1394
1395 -- private functions of public apis
1396 procedure debugBatchParam(p_module_name in varchar2, p_pub_param in BAT_PARAM_REC_TYPE )
1397 is
1398 begin
1399 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1400 ,p_module => p_module_name
1401 ,p_message => 'l_pub_params, name:' || p_pub_param.param_name
1402 || ', param type:' || to_char(p_pub_param.param_type)
1403 || ', data type:' || to_char(p_pub_param.data_type)
1404 || ', char val:' || p_pub_param.char_value
1405 || ', date val:' || p_pub_param.date_value
1406 || ', num val:' || p_pub_param.number_value
1407 --|| ', date val:' || to_char(nvl(p_pub_param.date_value, to_date('1900-01-01', 'yyyy-mm-dd')), 'yyyy-dd-mm')
1408 --|| ', num val:' || to_char(nvl(p_pub_param.number_value, -999))
1409 );
1410 END debugBatchParam;
1411
1412 procedure validateSystems(
1413 p_target_systems in TBL_OF_BAT_SYS_CODE_TYPE,
1414 l_target_systems OUT NOCOPY TBL_OF_BAT_SYS_CODE_TYPE,
1415 x_return_status OUT NOCOPY VARCHAR2,
1416 x_msg_count OUT NOCOPY NUMBER,
1417 x_msg_data OUT NOCOPY VARCHAR2
1418 )
1419 IS
1420 l_module_name varchar2(30) := 'EGO_PUB_FWK_PK.validateSystems';
1421 l_system_name varchar2(100);
1422 l_system_code varchar2(30) ;
1423 l_valid boolean := true;
1424 begin
1425 x_return_status := FND_API.G_RET_STS_SUCCESS;
1426
1427 for i in 1..p_target_systems.count loop
1428 l_system_name := p_target_systems(i).system_name;
1429 l_system_code := p_target_systems(i).system_code;
1430
1431 if (l_system_code is not null) then
1432 begin
1433 select ORIG_SYSTEM into l_system_code
1434 from hz_orig_systems_vl ssm
1435 where ssm.Status = 'A' AND ssm.orig_system_type = 'SPOKE'
1436 AND ORIG_SYSTEM = l_system_code;
1437 exception
1438 when no_data_found then
1439 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1440 ,p_module => l_module_name
1441 ,p_message => l_system_code || ' is invalid, by code');
1442 l_valid := false;
1443 end;
1444 elsif (l_system_code is null and l_system_name is not null) then
1445 begin
1446 select ORIG_SYSTEM into l_system_code
1447 from hz_orig_systems_vl ssm
1448 where ssm.Status = 'A' AND ssm.orig_system_type = 'SPOKE'
1449 AND ORIG_SYSTEM_NAME = l_system_name;
1450 exception
1451 when no_data_found then
1452 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1453 ,p_module => l_module_name
1454 ,p_message => l_system_name || ' is invalid, by name');
1455 l_valid := false;
1456 end;
1457 else -- l_system_code, l_system_name both null
1458 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1459 ,p_module => l_module_name
1460 ,p_message => ' system name and code are null');
1461 l_valid := false;
1462 end if;
1463
1464 -- check for duplicate
1465 if (l_target_systems.count > 0) then
1466 FOR j IN 1..l_target_systems.count loop
1467 IF (l_target_systems(j).system_code = l_system_code) THEN
1468 x_return_status := FND_API.G_RET_STS_ERROR;
1469 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1470 ,p_module => l_module_name
1471 ,p_message => 'target systems have duplicate, ' || l_system_code);
1472 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
1473 ,p_message =>'SELECT_DIFFERENT_SYSTEM');
1474 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1475 ,p_msg_data => x_msg_data );
1476 return ;
1477 end if;
1478 END loop;
1479 end if;
1480 -- end check for duplicate
1481
1482 l_target_systems(i).system_code := l_system_code;
1483 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1484 ,P_MODULE => l_module_name
1485 ,P_MESSAGE => ' system code: ' || l_system_code );
1486 end loop;
1487
1488 if (not l_valid ) then
1489 x_return_status := FND_API.G_RET_STS_ERROR;
1490 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1491 ,p_module => l_module_name
1492 ,p_message => 'target systems have invalid');
1493 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
1494 ,p_message =>'ADD_TARGET_SYSTEMS');
1495 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1496 ,p_msg_data => x_msg_data );
1497 end if;
1498 end validateSystems;
1499
1500 Procedure assignParamVal(p_param_name in varchar2,
1501 p_param_type in number,
1502 p_data_type in number,
1503 p_char_val in varchar2,
1504 p_number_val in number,
1505 p_date_val in date,
1506 x_pub_param OUT NOCOPY BAT_PARAM_REC_TYPE)
1507 is
1508 begin
1509 x_pub_param.param_name := p_param_name;
1510 x_pub_param.param_type := p_param_type;
1511 x_pub_param.data_type := p_data_type;
1512 x_pub_param.char_value := p_char_val;
1513 x_pub_param.date_value := p_date_val;
1514 x_pub_param.number_value := p_number_val;
1515
1516 end assignParamVal;
1517
1518 /*
1519 * check type and param name, check parameters value,
1520 * common params: Workspace Name, Auto-Release, Launch Sync Program
1521 * item params: STRUCTURE_NAME, EXPLOSION_DATE, LEVELS_TO_EXPLODE,
1522 * EXPLODE_STD_BOM, EXPLODE_OPTION
1523 * if struct_name is null, others value is default, it creates them if
1524 * caller doesn't provide them, below are val
1525 * date: null, level: 60, explode_std: N, option: current(2)
1526 * if struct_name is not null, date is required, others can use default val
1527 * level: 60, explode_std: N, option: current(2)
1528 * metadata param: BATCH_PROCESS_TYPE (AG can only be SYNC, create it
1529 * with SYNC, if it doesnot has it)
1530 * icc params: PublishParent, PublishChild (create it with N if it doesnot has it)
1531 */
1532 procedure validateBatchParams(
1533 p_batch_type IN number,
1534 p_batch_name IN VARCHAR2,
1535 p_pub_params IN TBL_OF_BAT_PARAM_TYPE,
1536 l_pub_params OUT NOCOPY TBL_OF_BAT_PARAM_TYPE,
1537 x_return_status OUT NOCOPY VARCHAR2,
1538 x_msg_count OUT NOCOPY NUMBER,
1539 x_msg_data OUT NOCOPY VARCHAR2
1540 ) is
1541 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.validateBatchParams';
1542 l_pub_type varchar2(10);
1543 l_sync varchar2(1);
1544 l_has_wkspname varchar2(1) := 'N';
1545 l_has_autorel varchar2(1) := 'N';
1546 l_has_autoimp varchar2(1) := 'N';
1547 l_has_expl_date varchar2(1) := 'N';
1548 l_has_expl_levl varchar2(1) := 'N';
1549 l_has_expl_stdb varchar2(1) := 'N';
1550 l_has_expl_optn varchar2(1) := 'N';
1551 l_has_pub_type varchar2(1) := 'N';
1552 l_has_pub_parent varchar2(1) := 'N';
1553 l_has_pub_child varchar2(1) := 'N';
1554 l_struct_name varchar2(100) ;
1555 l_expl_date date;
1556
1557 l_param_name EGO_PUB_BAT_PARAMS_B.PARAMETER_NAME%TYPE;
1558 l_param_char_val EGO_PUB_BAT_PARAMS_B.CHAR_VALUE%TYPE;
1559 l_param_date_val date;
1560 l_param_num_val number;
1561 l_icc_id number;
1562 l_counter number;
1563 l_token EGO_UTIL_PK.token_tbl;
1564 EGO_PUB_PARAM_VALUE_INVL exception;
1565 EGO_PUB_PARAM_INVL exception;
1566 EGO_PUB_PARAM_REQ_EXPL_DATE exception;
1567 begin
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569 l_pub_params := p_pub_params;
1570 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1571 ,p_module => l_module_name
1572 ,p_message => 'batch_type: ' || p_batch_type);
1573
1574 -- validate param: data_type, param_type, value
1575 for i in 1..l_pub_params.count loop
1576 debugBatchParam(l_module_name, l_pub_params(i));
1577
1578 l_param_name := l_pub_params(i).param_name;
1579 -- if workspace name is null, set it as batch name
1580 if (l_param_name = C_PARAM_WKSPNAME) then
1581 l_param_char_val := l_pub_params(i).char_value;
1582 if (l_param_char_val is null ) then
1583 l_pub_params(i).char_value := p_batch_name;
1584 end if;
1585 l_has_wkspname := 'Y';
1586 l_pub_params(i).param_type := C_PARAM_TYPE_DEST;
1587 l_pub_params(i).data_type := C_STRING_TYPE;
1588 l_pub_params(i).date_value := null;
1589 l_pub_params(i).number_value := null;
1590 end if;
1591 if (l_param_name = C_PARAM_AUTOREL) then
1592 l_param_char_val := l_pub_params(i).char_value;
1593
1594 if not (l_param_char_val is null
1595 or l_param_char_val = C_PARAM_VAL_AUTOREL_DEFAULT
1596 or l_param_char_val = C_PARAM_VAL_YES
1597 or l_param_char_val = C_PARAM_VAL_NO) then
1598 raise EGO_PUB_PARAM_VALUE_INVL;
1599 end if;
1600
1601 l_has_autorel := 'Y';
1602 l_pub_params(i).param_type := C_PARAM_TYPE_DEST;
1603 l_pub_params(i).data_type := C_STRING_TYPE;
1604 l_pub_params(i).date_value := null;
1605 l_pub_params(i).number_value := null;
1606 end if;
1607 if (l_param_name = C_PARAM_PUBTOPIM) then
1608 -- attribute group, force sync as Y
1609 if (p_batch_type = C_BATCH_ENTITY_AG) then
1610 l_pub_params(i).char_value := C_PARAM_VAL_YES;
1611 end if;
1612
1613 l_param_char_val := l_pub_params(i).char_value;
1614 if not (l_param_char_val is null
1615 or l_param_char_val = C_PARAM_VAL_YES
1616 or l_param_char_val = C_PARAM_VAL_NO ) then
1617 raise EGO_PUB_PARAM_VALUE_INVL;
1618 end if;
1619
1620 l_has_autoimp := 'Y';
1621 l_sync := l_pub_params(i).char_value;
1622 l_pub_params(i).param_type := C_PARAM_TYPE_DEST;
1623 l_pub_params(i).data_type := C_STRING_TYPE;
1624 l_pub_params(i).date_value := null;
1625 l_pub_params(i).number_value := null;
1626 end if;
1627
1628 -- item params
1629 -- valid it with item structure
1630 if (l_param_name = C_PARAM_STRUCTN) then
1631 if (p_batch_type <> C_BATCH_ENTITY_ITEM) then
1632 raise EGO_PUB_PARAM_INVL;
1633 end if;
1634
1635 l_struct_name := l_pub_params(i).char_value;
1636 if (l_struct_name is not null ) then
1637 -- valid structure name by EgoStructureNamesVO
1638 begin
1639 select inner_name into l_struct_name
1640 from (
1641 SELECT bom_globals.retrieve_message('BOM', 'BOM_PRIMARY') displayname,
1642 'PRIMARY' inner_name
1643 FROM dual
1644 UNION
1645 SELECT bad.display_name displayname,
1646 bad.alternate_designator_code inner_name
1647 FROM bom_alternate_designators_vl bad
1648 WHERE bad.alternate_designator_code IS NOT NULL
1649 AND Nvl(bad.disable_date,SYSDATE + 1) > SYSDATE
1650 ) struct
1651 where (struct.displayname = l_struct_name
1652 or struct.inner_name = l_struct_name)
1653 and rownum = 1;
1654
1655 -- set the valid inner name to it
1656 l_pub_params(i).char_value := l_struct_name;
1657 exception
1658 when no_data_found then
1659 raise EGO_PUB_PARAM_VALUE_INVL;
1660 end;
1661 end if;
1662
1663 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1664 l_pub_params(i).data_type := C_STRING_TYPE;
1665 l_pub_params(i).date_value := null;
1666 l_pub_params(i).number_value := null;
1667 end if;
1668 if (l_param_name = C_PARAM_EXPLDATE
1669 or l_param_name = C_PARAM_LEVLEXPL
1670 or l_param_name = C_PARAM_EXPLOPTN
1671 or l_param_name = C_PARAM_EXPLSTDB) then
1672
1673 if (p_batch_type <> C_BATCH_ENTITY_ITEM) then
1674 raise EGO_PUB_PARAM_INVL;
1675 end if;
1676
1677 end if;
1678 -- end item params
1679 -- meta params
1680 if (l_param_name = C_PARAM_PUBPARENT or l_param_name = C_PARAM_PUBCHILD) then
1681 if (p_batch_type <> C_BATCH_ENTITY_ICC) then
1682 raise EGO_PUB_PARAM_INVL;
1683 end if;
1684
1685 if (l_param_name = C_PARAM_PUBPARENT) then
1686 l_has_pub_parent := 'Y';
1687 else
1688 l_has_pub_child := 'Y';
1689 end if;
1690
1691 l_param_char_val := l_pub_params(i).char_value;
1692 if not (l_param_char_val = C_PARAM_VAL_TRUE
1693 or l_param_char_val = C_PARAM_VAL_FALSE) then
1694 raise EGO_PUB_PARAM_VALUE_INVL;
1695 end if;
1696
1697 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1698 l_pub_params(i).data_type := C_STRING_TYPE;
1699 l_pub_params(i).date_value := null;
1700 l_pub_params(i).number_value := null;
1701 end if;
1702 -- only meta publish has the param
1703 if (l_param_name = C_PARAM_PUBTYPE) then
1704 if (p_batch_type = C_BATCH_ENTITY_ITEM) then
1705 raise EGO_PUB_PARAM_INVL;
1706 end if;
1707
1708 l_param_char_val := l_pub_params(i).char_value;
1709 if not (l_param_char_val = C_PARAM_VAL_PUBTYPE_SYNC
1710 or l_param_char_val = C_PARAM_VAL_PUBTYPE_PUBL)
1711 -- attribute group, publish should be SYNC
1712 or (p_batch_type = C_BATCH_ENTITY_AG
1713 and l_param_char_val <> C_PARAM_VAL_PUBTYPE_SYNC) then
1714 raise EGO_PUB_PARAM_VALUE_INVL;
1715 end if;
1716
1717 l_has_pub_type := 'Y';
1718
1719 l_pub_type := l_pub_params(i).char_value;
1720 l_pub_params(i).data_type := C_STRING_TYPE;
1721 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1722 l_pub_params(i).date_value := null;
1723 l_pub_params(i).number_value := null;
1724 end if;
1725 -- end meta params
1726 end loop;
1727
1728 -- for item, if no structure name, explosion_date also null, other params are not required
1729 -- if it has structure name, these value
1730 if (p_batch_type = C_BATCH_ENTITY_ITEM ) then
1731 for i in 1..l_pub_params.count loop
1732 l_param_name := l_pub_params(i).param_name;
1733
1734 if (l_param_name = C_PARAM_EXPLDATE) then
1735 if (l_struct_name is null) then
1736 l_pub_params(i).date_value := null;
1737 else
1738 l_param_date_val := l_pub_params(i).date_value;
1739 if (l_param_date_val is null) then
1740 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1741 ,p_module => l_module_name
1742 ,p_message => 'param: structure name is not null, explosion date cannot be null');
1743 raise EGO_PUB_PARAM_VALUE_INVL;
1744 end if;
1745 end if;
1746
1747 l_has_expl_date := 'Y';
1748 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1749 l_pub_params(i).data_type := C_DATE_TYPE;
1750 l_pub_params(i).char_value := null;
1751 l_pub_params(i).number_value := null;
1752 end if;
1753 if (l_param_name = C_PARAM_LEVLEXPL) then
1754
1755 l_has_expl_levl := 'Y';
1756 -- valid range: 0, 60
1757 l_param_num_val := l_pub_params(i).number_value;
1758 if (l_param_num_val < 0 or l_param_num_val > 60) then
1759 l_pub_params(i).number_value := 60; -- reset it as maximum level+++
1760 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1761 ,p_module => l_module_name
1762 ,p_message => 'param: explosion level not in 0, 60, reset it as 60');
1763 --raise EGO_PUB_PARM_LEVLEXPL_INVL;
1764 end if;
1765
1766 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1767 l_pub_params(i).data_type := C_NUMBER_TYPE;
1768 l_pub_params(i).date_value := null;
1769 l_pub_params(i).char_value := null;
1770 end if;
1771 if (l_param_name = C_PARAM_EXPLOPTN) then
1772
1773 l_has_expl_optn := 'Y';
1774 -- BomLookupsVO, lookup_type = 'BOM_SSA_FILTER',
1775 -- All:1, Current:2, Current and Future:3
1776 l_param_num_val := l_pub_params(i).number_value;
1777 -- no structure name, it's default value 2 (current)
1778 if (l_struct_name is null) then
1779 l_pub_params(i).number_value := C_PARAM_VAL_EXPLTYPE_CUR;
1780 else
1781 if not (l_param_num_val = C_PARAM_VAL_EXPLTYPE_ALL
1782 or l_param_num_val = C_PARAM_VAL_EXPLTYPE_CUR
1783 or l_param_num_val = C_PARAM_VAL_EXPLTYPE_CUF) then
1784 raise EGO_PUB_PARAM_VALUE_INVL;
1785 end if;
1786 end if;
1787
1788 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1789 l_pub_params(i).data_type := C_NUMBER_TYPE;
1790 l_pub_params(i).char_value := null;
1791 l_pub_params(i).date_value := null;
1792 end if;
1793 if (l_param_name = C_PARAM_EXPLSTDB) then
1794
1795 l_has_expl_stdb := 'Y';
1796 l_param_char_val := l_pub_params(i).char_value;
1797 if (l_struct_name is null) then
1798 l_pub_params(i).char_value := C_PARAM_VAL_NO;
1799 else
1800 if not (l_param_char_val = C_PARAM_VAL_YES
1801 or l_param_char_val = C_PARAM_VAL_NO) then
1802 raise EGO_PUB_PARAM_VALUE_INVL;
1803 end if;
1804 end if;
1805
1806 l_pub_params(i).param_type := C_PARAM_TYPE_BATCH;
1807 l_pub_params(i).data_type := C_STRING_TYPE;
1808 l_pub_params(i).date_value := null;
1809 l_pub_params(i).number_value := null;
1810 end if;
1811 -- end item params
1812 end loop;
1813
1814 -- struct_name is not null, expl_date is required
1815 if (l_struct_name is not null and l_has_expl_date = 'N') then
1816 raise EGO_PUB_PARAM_REQ_EXPL_DATE;
1817 else
1818 -- create params with default value if caller doesn't provide them
1819 if (l_has_expl_date = 'N') then
1820 l_counter := l_pub_params.count + 1;
1821 l_pub_params(l_counter).param_name := C_PARAM_EXPLDATE;
1822 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1823 l_pub_params(l_counter).data_type := C_DATE_TYPE;
1824 end if;
1825 if (l_has_expl_levl = 'N') then
1826 l_counter := l_pub_params.count + 1;
1827 l_pub_params(l_counter).param_name := C_PARAM_LEVLEXPL;
1828 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1829 l_pub_params(l_counter).data_type := C_NUMBER_TYPE;
1830 l_pub_params(l_counter).number_value := 60;
1831 end if;
1832 if (l_has_expl_optn = 'N') then
1833 l_counter := l_pub_params.count + 1;
1834 l_pub_params(l_counter).param_name := C_PARAM_EXPLOPTN;
1835 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1836 l_pub_params(l_counter).data_type := C_NUMBER_TYPE;
1837 l_pub_params(l_counter).number_value := C_PARAM_VAL_EXPLTYPE_CUR;
1838 end if;
1839 if (l_has_expl_stdb = 'N') then
1840 l_counter := l_pub_params.count + 1;
1841 l_pub_params(l_counter).param_name := C_PARAM_EXPLSTDB;
1842 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1843 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1844 l_pub_params(l_counter).char_value := C_PARAM_VAL_NO;
1845 end if;
1846 end if;
1847
1848 else
1849 if (l_has_pub_type = 'N') then
1850 l_counter := l_pub_params.count + 1;
1851 l_pub_type := C_PARAM_VAL_PUBTYPE_SYNC;
1852 l_pub_params(l_counter).param_name := C_PARAM_PUBTYPE;
1853 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1854 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1855 l_pub_params(l_counter).char_value := C_PARAM_VAL_PUBTYPE_SYNC;
1856 end if;
1857 if (p_batch_type = C_BATCH_ENTITY_ICC ) then
1858 if (l_has_pub_parent = 'N') then
1859 l_counter := l_pub_params.count + 1;
1860 l_pub_params(l_counter).param_name := C_PARAM_PUBPARENT;
1861 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1862 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1863 l_pub_params(l_counter).char_value := C_PARAM_VAL_FALSE;
1864 end if;
1865 if (l_has_pub_parent = 'N') then
1866 l_counter := l_pub_params.count + 1;
1867 l_pub_params(l_counter).param_name := C_PARAM_PUBCHILD;
1868 l_pub_params(l_counter).param_type := C_PARAM_TYPE_BATCH;
1869 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1870 l_pub_params(l_counter).char_value := C_PARAM_VAL_FALSE;
1871 end if;
1872 end if;
1873 end if;
1874
1875 if (l_has_wkspname = 'N') then
1876 l_counter := l_pub_params.count + 1;
1877 l_pub_params(l_counter).param_name := C_PARAM_WKSPNAME;
1878 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1879 l_pub_params(l_counter).char_value := p_batch_name;
1880 l_pub_params(l_counter).param_type := C_PARAM_TYPE_DEST;
1881 end if;
1882 if (l_has_autorel = 'N') then
1883 l_counter := l_pub_params.count + 1;
1884 l_pub_params(l_counter).param_name := C_PARAM_AUTOREL;
1885 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1886 l_pub_params(l_counter).param_type := C_PARAM_TYPE_DEST;
1887 end if;
1888 if (l_has_autoimp = 'N') then
1889 l_counter := l_pub_params.count + 1;
1890 l_sync := C_PARAM_VAL_NO;
1891 l_pub_params(l_counter).param_name := C_PARAM_PUBTOPIM;
1892 l_pub_params(l_counter).data_type := C_STRING_TYPE;
1893 l_pub_params(l_counter).param_type := C_PARAM_TYPE_DEST;
1894 l_pub_params(l_counter).char_value := C_PARAM_VAL_NO;
1895 end if;
1896
1897 exception
1898 when EGO_PUB_PARAM_VALUE_INVL then
1899 x_return_status := FND_API.G_RET_STS_ERROR;
1900 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1901 ,p_module => l_module_name
1902 ,p_message => l_param_name || ' value is invalid');
1903 l_token(1).token_name := 'PARAM_NAME';
1904 l_token(1).token_value := l_param_name;
1905 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
1906 , p_message => 'EGO_PUB_PARAM_VALUE_INVL'
1907 , p_token => l_token);
1908 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1909 ,p_msg_data => x_msg_data );
1910 when EGO_PUB_PARAM_INVL then
1911 x_return_status := FND_API.G_RET_STS_ERROR;
1912 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1913 ,p_module => l_module_name
1914 ,p_message => 'the type should not has ' || l_param_name);
1915 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
1916 , p_message => 'EGO_PUB_PARAM_INVL'
1917 , p_token => l_token);
1918 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1919 ,p_msg_data => x_msg_data );
1920 when EGO_PUB_PARAM_REQ_EXPL_DATE then
1921 x_return_status := FND_API.G_RET_STS_ERROR;
1922 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1923 ,p_module => l_module_name
1924 ,p_message => ' struct_name is not null, explosion date is required');
1925 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
1926 ,p_message => 'EGO_PUB_PARAM_REQ_EXPL_DATE');
1927 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
1928 ,p_msg_data => x_msg_data );
1929 when others then
1930 x_return_status := FND_API.G_RET_STS_ERROR;
1931 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1932 ,p_module => l_module_name
1933 ,p_message => 'unknown excpetion!!!' || sqlerrm);
1934 end validateBatchParams;
1935
1936 -- only for check ICC, AG, VS public menu access
1937 Procedure hasPublishSyncPriv (p_user_id IN NUMBER,
1938 p_entity_type IN NUMBER,
1939 x_return_status OUT NOCOPY VARCHAR2,
1940 x_msg_count OUT NOCOPY NUMBER,
1941 x_msg_data OUT NOCOPY VARCHAR2
1942 )
1943 is
1944 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.hasPublishSyncPriv';
1945 l_err_msg varchar2(2000) := null;
1946 l_func_name varchar2(50) ;
1947
1948 l_user_id number;
1949 l_resp_id number;
1950 l_resp_key varchar2(30);
1951 begin
1952 x_return_status := FND_API.G_RET_STS_SUCCESS;
1953 if (p_entity_type = C_BATCH_ENTITY_ICC) THEN
1954 l_func_name := 'EGO_ITEM_ADMINISTRATION';
1955 elsif (p_entity_type = C_BATCH_ENTITY_AG) THEN
1956 l_func_name := 'EGO_ITEM_USER_ATTRS';
1957 elsif (p_entity_type = C_BATCH_ENTITY_VS) THEN
1958 l_func_name := 'EGO_CREATE_ATTR_EXT';
1959 END IF;
1960
1961 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1962 ,p_module => l_module_name
1963 ,p_message => 'check func: ' || l_func_name);
1964 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1965 ,p_module => l_module_name
1966 ,p_message => 'check user has the resp or not ');
1967
1968 -- check the user has the responsiblity
1969 begin
1970 l_user_id := FND_GLOBAL.user_id;
1971 l_resp_id := FND_GLOBAL.resp_id;
1972 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
1973 ,p_module => l_module_name
1974 ,p_message => 'user_id :' || l_user_id || ', resp_id: ' || l_resp_id);
1975
1976 select rkey into l_resp_key from (
1977 select r.responsibility_key rkey
1978 from FND_USER_RESP_GROUPS_DIRECT gd,
1979 fnd_responsibility r,
1980 fnd_user u
1981 where gd.responsibility_id = r.responsibility_id
1982 and gd.responsibility_application_id = r.application_id
1983 and gd.user_id = u.user_id
1984 and (gd.end_date > sysdate or gd.end_date is null)
1985 and u.user_id = l_user_id
1986 and r.responsibility_id = l_resp_id
1987 union all
1988 select r.responsibility_key rkey
1989 from FND_USER_RESP_GROUPS_INDIRECT gi,
1990 fnd_responsibility r,
1991 fnd_user u
1992 where gi.responsibility_id = r.responsibility_id
1993 and gi.responsibility_application_id = r.application_id
1994 and gi.user_id = u.user_id
1995 and (gi.end_date > sysdate or gi.end_date is null)
1996 and u.user_id = l_user_id
1997 and r.responsibility_id = l_resp_id) rdi;
1998 exception
1999 when no_data_found then
2000 x_return_status := FND_API.G_RET_STS_ERROR;
2001 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2002 ,p_module => l_module_name
2003 ,p_message => 'user doesnot has the resp');
2004 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2005 ,p_message =>'EGO_NO_FUNC_PRIVILEGE_FOR_USER');
2006 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2007 ,p_msg_data => x_msg_data );
2008 return ;
2009 end;
2010
2011 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2012 ,p_module => l_module_name
2013 ,p_message => 'call fnd_function.test ');
2014
2015 if fnd_function.test(function_name => l_func_name) then
2016 return ;
2017 else
2018 x_return_status := FND_API.G_RET_STS_ERROR;
2019 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2020 ,p_message =>'EGO_NO_FUNC_PRIVILEGE_FOR_USER');
2021 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2022 ,p_msg_data => x_msg_data );
2023 return ;
2024 end if;
2025 exception
2026 when others then
2027 l_err_msg := ' others error ' ||sqlerrm ;
2028 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2029 ,p_module => l_module_name
2030 ,p_message => l_err_msg
2031 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
2032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2033 end hasPublishSyncPriv;
2034
2035 -- refer to EgoItemPrivilegeQueryContext.hasPrivilege, the privileges is cached by the EgoPrivilegeQueryContext
2036 -- check item public api, does it has privilege check , FUNCTION
2037 -- p_privilege_name : EGO_PUBLISH_ITEM / EGO_VIEW_ITEM
2038 Procedure hasItemPriv (p_user_id IN NUMBER,
2039 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2040 p_privilege_name IN varchar2,--'EGO_PUBLISH_ITEM'/'EGO_VIEW_ITEM'
2041 x_return_status OUT NOCOPY VARCHAR2,
2042 x_msg_count OUT NOCOPY NUMBER,
2043 x_msg_data OUT NOCOPY VARCHAR2
2044 )
2045 IS
2046 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.hasItemPriv';
2047 l_has_prv varchar2(1) ;
2048
2049 l_item_number varchar2(500);
2050 l_item_list varchar2(6000);
2051 l_token EGO_UTIL_PK.token_tbl;
2052 l_err_msg varchar2(2000) := null;
2053 l_msg_name varchar2(50) ;
2054 l_party_id number ;
2055 l_party_idstr varchar2(100) ;
2056 l_item_id number;
2057 l_org_id number;
2058 begin
2059 x_return_status := FND_API.G_RET_STS_SUCCESS;
2060
2061 begin
2062 SELECT party_id INTO l_party_id from ego_user_v
2063 where user_id = p_user_id;
2064 exception
2065 when no_data_found then
2066 l_err_msg := 'Cannot get party_id by user_id: ' || to_char(p_user_id);
2067 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2068 ,p_module => l_module_name
2069 ,p_message => l_err_msg
2070 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
2071 x_return_status := FND_API.G_RET_STS_ERROR;
2072 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2073 ,p_message =>'EGO_PERSON_INVALID');
2074 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2075 ,p_msg_data => x_msg_data );
2076 return;
2077 end ;
2078
2079 l_party_idstr := 'HZ_PARTY:' || to_char(l_party_id);
2080
2081 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2082 ,p_module => l_module_name
2083 ,p_message => 'party_id: ' || l_party_idstr);
2084
2085 FOR i IN p_batch_entities.FIRST..p_batch_entities.LAST LOOP
2086 l_item_id := to_number(p_batch_entities(i).pk1_value);
2087 l_org_id := to_number(p_batch_entities(i).pk2_value);
2088 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2089 ,p_module => l_module_name
2090 ,p_message => p_privilege_name || ' ids: ' || l_item_id || ',' || l_org_id);
2091 l_has_prv := EGO_DATA_SECURITY.check_function(
2092 p_api_version => 1.0,
2093 p_function => p_privilege_name,--'EGO_PUBLISH_ITEM'/'EGO_VIEW_ITEM',
2094 p_object_name => 'EGO_ITEM',
2095 p_instance_pk1_value => l_item_id,
2096 p_instance_pk2_value => l_org_id,
2097 p_user_name => l_party_idstr
2098 );
2099
2100 -- refer to impl: EGOCHUAB.pls
2101 if (l_has_prv = 'F') then
2102 l_err_msg := ', no item privilege '|| p_privilege_name ||', item: '
2103 || p_batch_entities(i).pk1_value || ', org: ' ||p_batch_entities(i).pk2_value;
2104 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2105 ,p_module => l_module_name
2106 ,p_message => l_err_msg
2107 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
2108
2109 IF (P_PRIVILEGE_NAME = 'EGO_PUBLISH_ITEM') THEN
2110 begin
2111 select concatenated_segments into l_item_number
2112 from mtl_system_items_kfv
2113 where inventory_item_id = l_item_id and organization_id = l_org_id;
2114 exception
2115 when no_data_found then
2116 l_item_number := '(item_id: ' || l_item_id || ', org_id: ' || l_org_id || ')';
2117 end;
2118 IF (l_item_list is NULL ) THEN
2119 l_item_list := l_item_number;
2120 ELSE
2121 l_item_list := l_item_list || ', ' || l_item_number;
2122 end if;
2123 end if;
2124 elsif (l_has_prv <> 'T') then
2125 l_err_msg := ', error while check item privilege '
2126 || p_privilege_name ||', item: ' || p_batch_entities(i).pk1_value
2127 || ', org: ' ||p_batch_entities(i).pk2_value;
2128 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2129 ,p_module => l_module_name
2130 ,p_message => l_err_msg
2131 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
2132 end if;
2133 end loop;
2134
2135 IF (l_err_msg is not null) THEN
2136 x_return_status := FND_API.G_RET_STS_ERROR;
2137 if (p_privilege_name = 'EGO_PUBLISH_ITEM') then
2138 l_msg_name := 'EGO_NO_PUBLISH_PRIVILEGE';
2139 l_token(1).token_name := 'ITEM_LIST';
2140 l_token(1).token_value := l_item_list;
2141 else
2142 l_msg_name := 'EGO_NO_VIEW_PRIVILEGE';
2143 end if;
2144
2145 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2146 , p_message => l_msg_name
2147 , p_token => l_token);
2148 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2149 ,p_msg_data => x_msg_data );
2150 END IF;
2151 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2152 ,P_MODULE => L_MODULE_NAME
2153 ,p_message => 'checking Item Privs, x_return_status ' || x_return_status);
2154
2155 exception
2156 when others then
2157 l_err_msg := ' others error ' ||sqlerrm ;
2158 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2159 ,p_module => l_module_name
2160 ,p_message => l_err_msg
2161 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
2162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2163 end hasItemPriv;
2164
2165 procedure hasItemsRevsImplExplValid(
2166 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2167 p_explosion_date IN date,
2168 x_return_status OUT NOCOPY VARCHAR2,
2169 x_msg_count OUT NOCOPY NUMBER,
2170 x_msg_data OUT NOCOPY VARCHAR2
2171 )
2172 IS
2173 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.hasItemsRevsImplExplValid';
2174 l_item_id number;
2175 l_org_id number;
2176 l_item_number VARCHAR2(500);
2177 l_item_list varchar2(6000);
2178 l_token EGO_UTIL_PK.token_tbl;
2179 l_err_msg varchar2(2000) ;
2180 l_rev_id number;
2181 l_impl varchar2(1);
2182 l_valid_expl varchar2(1);
2183 l_has_impl_err BOOLEAN := false;
2184 l_has_expl_err BOOLEAN := false;
2185
2186 cursor c_rev_impl (c_rev_id number) is
2187 SELECT 'N'
2188 FROM MTL_ITEM_REVISIONS_B
2189 WHERE IMPLEMENTATION_DATE IS null AND REVISION_ID = c_rev_id;
2190
2191 cursor c_valid_expl (c_item_id number, c_org_id number, c_expl_date date) is
2192 SELECT Max(revision_id) RevisionId
2193 FROM mtl_item_revisions_b
2194 WHERE inventory_item_id = c_item_id
2195 AND organization_id = c_org_id
2196 AND effectivity_date <= c_expl_date
2197 AND implementation_date IS NOT NULL;
2198
2199 begin
2200 x_return_status := FND_API.G_RET_STS_SUCCESS;
2201
2202 FOR i IN p_batch_entities.FIRST..p_batch_entities.LAST LOOP
2203 l_item_id := to_number(p_batch_entities(i).pk1_value);
2204 l_org_id := to_number(p_batch_entities(i).pk2_value);
2205 l_rev_id := to_number(p_batch_entities(i).pk3_value);
2206
2207 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2208 ,p_module => l_module_name
2209 ,p_message => 'item:' || l_item_id
2210 || ', org_id:' || l_org_id
2211 || ', rev_id:' || l_rev_id);
2212
2213 open c_rev_impl (c_rev_id => l_rev_id);
2214 fetch c_rev_impl into l_impl;
2215 if (c_rev_impl%NOTFOUND) then
2216 l_impl := 'Y';
2217 close c_rev_impl;
2218 end if;
2219 if (c_rev_impl%ISOPEN) then
2220 close c_rev_impl;
2221 end if;
2222
2223 open c_valid_expl(c_item_id => l_item_id, c_org_id => l_org_id, c_expl_date => p_explosion_date);
2224 fetch c_valid_expl into l_rev_id;
2225 if (c_valid_expl%NOTFOUND) then
2226 l_valid_expl := 'N';
2227 close c_valid_expl;
2228 end if;
2229 if (c_valid_expl%ISOPEN) then
2230 close c_valid_expl;
2231 end if;
2232
2233 if (l_impl = 'N' or l_valid_expl = 'N') then
2234 if (l_impl = 'N') then
2235 l_err_msg := 'item revision is not implemented ';
2236 l_has_impl_err := true;
2237 end if;
2238 if (l_valid_expl = 'N') then
2239 l_err_msg := l_err_msg || ' item explosion date is invalid';
2240 l_has_expl_err := true;
2241 end if;
2242
2243 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2244 ,p_module => l_module_name
2245 ,p_message => l_err_msg);
2246
2247 begin
2248 select concatenated_segments into l_item_number
2249 from mtl_system_items_kfv
2250 where inventory_item_id = l_item_id
2251 and organization_id = l_org_id;
2252 exception
2253 when no_data_found then
2254 l_item_number := '(item_id: ' || l_item_id
2255 || ', org_id: ' || l_org_id || ')';
2256 end;
2257 if (l_item_list is null ) then
2258 l_item_list := l_item_number;
2259 ELSE
2260 l_item_list := l_item_list || ', ' || l_item_number;
2261 end if;
2262 end if;
2263 END LOOP;
2264
2265 IF (l_has_impl_err or l_has_expl_err) THEN
2266 x_return_status := FND_API.G_RET_STS_ERROR;
2267 if (l_has_impl_err ) then
2268 l_token(1).token_name := 'ITEM_LIST';
2269 l_token(1).token_value := l_item_list;
2270 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2271 , p_message => 'EGO_UNIMP_REV_NO_PUB'
2272 , p_token => l_token);
2273 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2274 ,p_msg_data => x_msg_data );
2275 end if;
2276 if (l_has_expl_err) then
2277 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2278 , p_message => 'EGO_PUBLISH_DATE_INVALID'
2279 , p_token => l_token);
2280 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2281 ,p_msg_data => x_msg_data );
2282 end if;
2283 END IF;
2284 END hasItemsRevsImplExplValid;
2285
2286 Function getExplosionDate(
2287 p_pub_params IN TBL_OF_BAT_PARAM_TYPE
2288 ) return date is
2289 BEGIN
2290 for i in 1..p_pub_params.count loop
2291 if (p_pub_params(i).param_name = C_PARAM_EXPLDATE) then
2292 return p_pub_params(i).date_value;
2293 end if;
2294 END loop;
2295
2296 return null;
2297 END getExplosionDate;
2298
2299 Procedure hasPublishPrivilege(p_user_id IN NUMBER,
2300 p_batch_type IN NUMBER,
2301 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2302 p_expl_date IN date,
2303 x_return_status OUT NOCOPY VARCHAR2,
2304 x_msg_count OUT NOCOPY NUMBER,
2305 x_msg_data OUT NOCOPY VARCHAR2
2306 ) is
2307 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.hasPublishPrivilege';
2308 begin
2309 if (p_batch_type = C_BATCH_ENTITY_ITEM ) then
2310 -- check user has view item priv 'EGO_VIEW_ITEM'
2311 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2312 ,p_module => l_module_name
2313 ,p_message => 'check item view privilege');
2314 hasItemPriv(p_user_id, p_batch_entities, 'EGO_VIEW_ITEM',
2315 x_return_status, x_msg_count, x_msg_data);
2316 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2317 return ;
2318 end if;
2319 -- check user has publish item priv 'EGO_PUBLISH_ITEM'
2320 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2321 ,p_module => l_module_name
2322 ,p_message => 'check item publish privilege');
2323 hasItemPriv(p_user_id, p_batch_entities, 'EGO_PUBLISH_ITEM',
2324 x_return_status, x_msg_count, x_msg_data);
2325 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2326 return ;
2327 end if;
2328
2329 -- check the selected item rev is implemented
2330 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2331 ,p_module => l_module_name
2332 ,p_message => 'check item rev implemented status, explosion date valid');
2333
2334 hasItemsRevsImplExplValid(p_batch_entities, p_expl_date,
2335 x_return_status, x_msg_count, x_msg_data);
2336 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2337 return ;
2338 end if;
2339 else
2340 -- check user has pub/sync privileges of icc/ag/vs
2341 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2342 ,p_module => l_module_name
2343 ,p_message => 'check icc/ag/vs access priv');
2344 hasPublishSyncPriv(p_user_id, p_batch_type,
2345 x_return_status, x_msg_count, x_msg_data);
2346 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2347 return ;
2348 end if;
2349 end if;
2350 end hasPublishPrivilege;
2351
2352 FUNCTION removeDupEntities (p_batch_type IN NUMBER, p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE)
2353 RETURN TBL_OF_BAT_ENT_OBJ_TYPE IS
2354 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
2355 l_counter number ;
2356 l_has_it boolean ;
2357 BEGIN
2358 if p_batch_entities.count = 1 then
2359 return p_batch_entities;
2360 end if;
2361 -- currently, it only support publish one icc per batch
2362 if (p_batch_type = C_BATCH_ENTITY_ICC) then
2363 l_batch_entities(1).pk1_value := p_batch_entities(1).pk1_value;
2364 l_batch_entities(1).pk2_value := p_batch_entities(1).pk2_value;
2365 l_batch_entities(1).pk3_value := p_batch_entities(1).pk3_value;
2366 l_batch_entities(1).pk4_value := p_batch_entities(1).pk4_value;
2367 l_batch_entities(1).pk5_value := p_batch_entities(1).pk5_value;
2368 l_batch_entities(1).user_entered := 'Y';
2369 return l_batch_entities;
2370 end if;
2371
2372 for i in 1..p_batch_entities.count loop
2373 if (l_batch_entities is null or l_batch_entities.count = 0 ) then
2374 l_batch_entities(i).pk1_value := p_batch_entities(i).pk1_value;
2375 l_batch_entities(i).pk2_value := p_batch_entities(i).pk2_value;
2376 l_batch_entities(i).pk3_value := p_batch_entities(i).pk3_value;
2377 l_batch_entities(i).pk4_value := p_batch_entities(i).pk4_value;
2378 l_batch_entities(i).pk5_value := p_batch_entities(i).pk5_value;
2379 l_batch_entities(i).user_entered := 'Y';
2380 l_counter := 2;
2381 else
2382 l_has_it := false;
2383 for j in 1..l_batch_entities.count loop
2384 if (nvl(l_batch_entities(j).pk1_value, 0) = nvl(p_batch_entities(i).pk1_value, 0)
2385 and nvl(l_batch_entities(j).pk2_value, 0) = nvl(p_batch_entities(i).pk2_value, 0)
2386 and nvl(l_batch_entities(j).pk3_value, 0) = nvl(p_batch_entities(i).pk3_value, 0)
2387 and nvl(l_batch_entities(j).pk4_value, 0) = nvl(p_batch_entities(i).pk4_value, 0)
2388 and nvl(l_batch_entities(j).pk5_value, 0) = nvl(p_batch_entities(i).pk5_value, 0)) then
2389 l_has_it := true;
2390 continue;
2391 end if;
2392 end loop;
2393 if (not l_has_it ) then
2394 l_batch_entities(l_counter).pk1_value := p_batch_entities(i).pk1_value;
2395 l_batch_entities(l_counter).pk2_value := p_batch_entities(i).pk2_value;
2396 l_batch_entities(l_counter).pk3_value := p_batch_entities(i).pk3_value;
2397 l_batch_entities(l_counter).pk4_value := p_batch_entities(i).pk4_value;
2398 l_batch_entities(l_counter).pk5_value := p_batch_entities(i).pk5_value;
2399 l_batch_entities(l_counter).user_entered := 'Y';
2400 l_counter := l_counter + 1;
2401 end if;
2402 end if;
2403 end loop;
2404
2405 return l_batch_entities;
2406 END removeDupEntities;
2407
2408 procedure validateEntities(
2409 p_batch_type IN NUMBER,
2410 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2411 x_return_status OUT NOCOPY VARCHAR2,
2412 x_msg_count OUT NOCOPY NUMBER,
2413 x_msg_data OUT NOCOPY VARCHAR2
2414 )
2415 is
2416 l_module_name varchar2(30) := 'validateEntities';
2417 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
2418 l_entity_id number;
2419 l_valid boolean := true;
2420 l_msg varchar2(300);
2421 l_token EGO_UTIL_PK.token_tbl;
2422
2423 l_pk1_value number ;
2424 l_pk2_value number ;
2425 l_pk3_value number ;
2426 begin
2427 x_return_status := FND_API.G_RET_STS_SUCCESS;
2428 for i in 1..p_batch_entities.count loop
2429 l_pk1_value := nvl(p_batch_entities(i).pk1_value, -1);
2430 l_pk2_value := nvl(p_batch_entities(i).pk2_value, -1);
2431 l_pk3_value := nvl(p_batch_entities(i).pk3_value, -1);
2432 if (p_batch_type = C_BATCH_ENTITY_ITEM) then
2433 begin
2434 select mi.inventory_item_id into l_entity_id
2435 from mtl_system_items_b mi, mtl_item_revisions_b mr
2436 where mi.inventory_item_id = mr.inventory_item_id
2437 and mi.organization_id = mr.organization_id
2438 and mi.inventory_item_id = l_pk1_value
2439 and mi.organization_id = l_pk2_value
2440 and mr.REVISION_ID = l_pk3_value
2441 and SYSDATE BETWEEN NVL(mi.START_DATE_ACTIVE, SYSDATE) and NVL(mi.END_DATE_ACTIVE, SYSDATE);
2442 exception
2443 when no_data_found then
2444 l_valid := false;
2445 l_msg := ' item_id: ' || p_batch_entities(i).pk1_value
2446 || ', org_id: ' || p_batch_entities(i).pk2_value
2447 || ', rev_id: ' || p_batch_entities(i).pk3_value;
2448 end;
2449 elsif (p_batch_type = C_BATCH_ENTITY_ICC) then
2450 begin
2451 if (l_pk2_value <> -1) then
2452 select icc.ITEM_CATALOG_GROUP_ID into l_entity_id
2453 from MTL_ITEM_CATALOG_GROUPS_B icc, EGO_MTL_CATALOG_GRP_VERS_B icc_ver
2454 where icc.ITEM_CATALOG_GROUP_ID = icc_ver.ITEM_CATALOG_GROUP_ID
2455 and icc.ITEM_CATALOG_GROUP_ID = l_pk1_value
2456 and icc_ver.VERSION_SEQ_ID = l_pk2_value
2457 and SYSDATE BETWEEN NVL(icc.START_DATE_ACTIVE, SYSDATE) and NVL(icc.END_DATE_ACTIVE, SYSDATE);
2458 -- pk2_value version_id
2459 else
2460 select icc.ITEM_CATALOG_GROUP_ID into l_entity_id
2461 from MTL_ITEM_CATALOG_GROUPS_B icc
2462 where icc.ITEM_CATALOG_GROUP_ID = l_pk1_value
2463 and SYSDATE BETWEEN NVL(icc.START_DATE_ACTIVE, SYSDATE) and NVL(icc.END_DATE_ACTIVE, SYSDATE);
2464 end if;
2465 exception
2466 when no_data_found then
2467 l_valid := false;
2468 l_msg := ' icc_id: ' || p_batch_entities(i).pk1_value;
2469 if (l_pk2_value <> -1) then
2470 l_msg := l_msg || 'icc_ver_id: ' || p_batch_entities(i).pk2_value;
2471 end if;
2472 end;
2473 elsif (p_batch_type = C_BATCH_ENTITY_VS) then
2474 begin
2475 select value_set_id into l_entity_id
2476 from EGO_VALUE_SETS_V
2477 where value_set_id = l_pk1_value;
2478 exception
2479 when no_data_found then
2480 l_valid := false;
2481 l_msg := ' valueset_id: ' || p_batch_entities(i).pk1_value;
2482 end;
2483 elsif (p_batch_type = C_BATCH_ENTITY_AG) then
2484 begin
2485 select ATTR_GROUP_ID into l_entity_id
2486 from EGO_ATTR_GROUPS_V
2487 where ATTR_GROUP_ID = l_pk1_value;
2488 exception
2489 when no_data_found then
2490 l_valid := false;
2491 l_msg := ' ag_id: ' || p_batch_entities(i).pk1_value;
2492 end;
2493 end if;
2494
2495 if (not l_valid ) then
2496 x_return_status := FND_API.G_RET_STS_ERROR;
2497 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2498 ,p_module => l_module_name
2499 ,p_message => 'invalid entity data, ' || l_msg);
2500 end if;
2501 end loop;
2502
2503 if (x_return_status = FND_API.G_RET_STS_ERROR) then
2504 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2505 ,p_module => l_module_name
2506 ,p_message => ' entities have invalid '
2507 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
2508 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2509 ,p_message =>'EGO_PUB_ENTITY_INVL');
2510 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2511 ,p_msg_data => x_msg_data );
2512 end if;
2513
2514 end validateEntities;
2515
2516 procedure hasMultiEntRev(
2517 p_batch_type IN NUMBER,
2518 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2519 x_return_status OUT NOCOPY VARCHAR2,
2520 x_msg_count OUT NOCOPY NUMBER,
2521 x_msg_data OUT NOCOPY VARCHAR2
2522 )
2523 is
2524 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.hasMultiEntRev';
2525 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
2526 begin
2527 x_return_status := FND_API.G_RET_STS_SUCCESS;
2528 l_batch_entities := p_batch_entities;
2529 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2530 ,p_module => l_module_name
2531 ,p_message => 'check entity with multi-rev' );
2532
2533 for i in 1..p_batch_entities.count loop
2534 for j in 1..l_batch_entities.count loop
2535 if (p_batch_type = C_BATCH_ENTITY_ITEM
2536 and nvl(l_batch_entities(j).pk1_value, 0) = nvl(l_batch_entities(i).pk1_value, 0)
2537 and nvl(l_batch_entities(j).pk2_value, 0) = nvl(l_batch_entities(i).pk2_value, 0)
2538 and nvl(l_batch_entities(j).pk3_value, 0) <> nvl(l_batch_entities(i).pk3_value, 0)
2539 ) then
2540 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2541 ,p_module => l_module_name
2542 ,p_message => 'item:' || l_batch_entities(i).pk1_value
2543 || ', org_id:' || l_batch_entities(i).pk2_value
2544 || ', rev_id:' || l_batch_entities(i).pk3_value
2545 || ' has another rev !!!' );
2546 x_return_status := FND_API.G_RET_STS_ERROR;
2547 end if;
2548 if (p_batch_type = C_BATCH_ENTITY_VS
2549 and nvl(l_batch_entities(j).pk1_value, 0) = nvl(l_batch_entities(i).pk1_value, 0)
2550 and nvl(l_batch_entities(j).pk2_value, 0) <> nvl(l_batch_entities(i).pk2_value, 0)
2551 ) then
2552 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2553 ,p_module => l_module_name
2554 ,p_message => 'valueset:' || l_batch_entities(i).pk1_value
2555 || ', rev_id:' || l_batch_entities(i).pk2_value
2556 || ' has another rev !!!' );
2557 x_return_status := FND_API.G_RET_STS_ERROR;
2558 end if;
2559 end loop;
2560 end loop;
2561 end hasMultiEntRev;
2562
2563 FUNCTION getBatchEntities (p_batch_id IN NUMBER)
2564 return TBL_OF_BAT_ENT_OBJ_TYPE
2565 IS
2566 l_counter number;
2567 l_pk1_vals dbms_sql.varchar2_table;
2568 l_pk2_vals dbms_sql.varchar2_table;
2569 l_pk3_vals dbms_sql.varchar2_table;
2570 l_pk4_vals dbms_sql.varchar2_table;
2571 l_pk5_vals dbms_sql.varchar2_table;
2572 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
2573 begin
2574 -- build entities
2575 select PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE
2576 BULK COLLECT INTO l_pk1_vals, l_pk2_vals, l_pk3_vals, l_pk4_vals, l_pk5_vals
2577 from EGO_PUB_BAT_ENT_OBJS_B where BATCH_ID = p_batch_id and USER_ENTERED = 'Y';
2578 l_counter := 1;
2579 for i in 1..l_pk1_vals.COUNT loop
2580 l_batch_entities(l_counter).pk1_value := l_pk1_vals(i);
2581 l_batch_entities(l_counter).pk2_value := l_pk2_vals(i);
2582 l_batch_entities(l_counter).pk3_value := l_pk3_vals(i);
2583 l_batch_entities(l_counter).pk4_value := l_pk4_vals(i);
2584 l_batch_entities(l_counter).pk5_value := l_pk5_vals(i);
2585 l_batch_entities(l_counter).user_entered := 'Y';
2586 l_counter := l_counter + 1;
2587 end loop;
2588 return l_batch_entities;
2589 end getBatchEntities;
2590
2591 -- it raises biz event for the batch
2592 procedure publish_pvt(p_batch_id IN NUMBER,
2593 p_batch_type IN NUMBER,
2594 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2595 p_pub_type IN VARCHAR2,
2596 p_check_priv IN BOOLEAN default true,
2597 x_return_status OUT NOCOPY VARCHAR2,
2598 x_msg_count OUT NOCOPY NUMBER,
2599 x_msg_data OUT NOCOPY VARCHAR2)
2600 IS
2601 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.publish_pvt';
2602 l_batch_id number;
2603 l_user_id number;
2604 l_party_id number;
2605
2606 l_expl_date date;
2607 l_struct_flag varchar2(1);
2608 l_icc_parent_flag varchar2(10);
2609 l_icc_child_flag varchar2(10);
2610 l_has_priv boolean;
2611 l_passwd_mod varchar2(30);
2612
2613 l_event_name varchar2(240);
2614 l_event_key varchar2(240);
2615 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
2616 l_user_name varchar2(100);
2617 l_wsdl_url varchar2(1000);
2618 l_event_data varchar2(3000);
2619
2620 BEGIN
2621 l_batch_id := p_batch_id;
2622 l_user_id := FND_GLOBAL.user_id;
2623 x_return_status := FND_API.G_RET_STS_SUCCESS;
2624
2625 /*
2626 * check current user equals the batch creator, batch in draft status!!
2627 * check user has pub/sync privileges of icc/ag/vs
2628 * check user has view item priv
2629 * check user has publish item priv
2630 * check the selected item rev is implemented
2631 */
2632 if p_check_priv then
2633 begin
2634 select batch_id into l_batch_id
2635 from EGO_PUB_BAT_HDR_B
2636 where batch_id = p_batch_id and created_by = l_user_id;
2637 exception
2638 when no_data_found then
2639 x_return_status := FND_API.G_RET_STS_ERROR;
2640 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2641 ,p_module => l_module_name
2642 ,p_message => to_char(p_batch_id) || ', not batch creator');
2643 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2644 ,p_message =>'EGO_PUB_USR_NOT_OWNER');
2645 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2646 ,p_msg_data => x_msg_data );
2647 return ;
2648 end;
2649 begin
2650 select distinct batch_id into l_batch_id
2651 from EGO_PUB_BAT_STATUS_B
2652 where batch_id = p_batch_id and STATUS_CODE = C_STATUS_DRAFT;
2653 exception
2654 when no_data_found then
2655 x_return_status := FND_API.G_RET_STS_ERROR;
2656 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2657 ,p_module => l_module_name
2658 ,p_message => to_char(p_batch_id) || ', not draft batch');
2659 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
2660 ,p_message =>'EGO_PUB_STATUS_NOT_DRAFT');
2661 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
2662 ,p_msg_data => x_msg_data );
2663 return ;
2664 end;
2665
2666 begin
2667 select date_value into l_expl_date
2668 from EGO_PUB_BAT_PARAMS_B
2669 where type_id = p_batch_id and parameter_name = C_PARAM_EXPLDATE;
2670 exception
2671 when no_data_found then
2672 null; -- it's not item publish
2673 end;
2674
2675 hasPublishPrivilege(l_user_id, p_batch_type, p_batch_entities,
2676 l_expl_date, x_return_status, x_msg_count, x_msg_data);
2677 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2678 return ;
2679 end if;
2680
2681 end if;
2682 -- end of privileges validation
2683
2684 SELECT nvl(customer_id , person_party_id) into l_party_id
2685 FROM fnd_user WHERE user_id = l_user_id;
2686
2687 SAVEPOINT PUB_BAT_STATUS_CHANGE;
2688 update EGO_PUB_BAT_HDR_B set PUBLISHED_BY = l_party_id, BATCH_CREATION_DATE = sysdate
2689 where batch_id = l_batch_id;
2690
2691 update EGO_PUB_BAT_STATUS_B set STATUS_CODE = C_STATUS_SUBMITTED
2692 where batch_id = l_batch_id and STATUS_CODE <> C_STATUS_SUBMITTED;
2693
2694 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2695 ,p_module => l_module_name
2696 ,p_message => 'prepare evt params');
2697
2698 -- invoke business event
2699 -- get key from sequence MTL_BUSINESS_EVENTS_S
2700 select MTL_BUSINESS_EVENTS_S.nextval into l_event_key from dual;
2701 -- params: batch_id, $pub_params, workspace_name, auto_release
2702 if (p_batch_type = C_BATCH_ENTITY_ITEM) THEN
2703 select CASE WHEN (CHAR_VALUE IS NULL) THEN 'N' ELSE 'Y' END
2704 into l_struct_flag
2705 from EGO_PUB_BAT_PARAMS_B
2706 where TYPE_ID = l_batch_id AND PARAMETER_NAME = C_PARAM_STRUCTN;
2707
2708 l_event_name := 'oracle.apps.ego.item.publishItem';
2709 wf_event.AddParameterToList(p_name => 'STRUCTURE_FLAG'
2710 ,p_value => l_struct_flag
2711 ,p_ParameterList => l_parameter_List);
2712 end if;
2713
2714 if (p_batch_type = C_BATCH_ENTITY_ICC) then
2715 l_event_name := 'oracle.apps.ego.item.publishItemCatalogCategory';
2716
2717 select CHAR_VALUE into l_icc_parent_flag
2718 from EGO_PUB_BAT_PARAMS_B
2719 where TYPE_ID = l_batch_id AND PARAMETER_NAME = C_PARAM_PUBPARENT;
2720
2721 select CHAR_VALUE into l_icc_child_flag
2722 from EGO_PUB_BAT_PARAMS_B
2723 where TYPE_ID = l_batch_id AND PARAMETER_NAME = C_PARAM_PUBCHILD;
2724
2725 wf_event.AddParameterToList(p_name => 'publish_parent_hierarchy'
2726 ,p_value => l_icc_parent_flag
2727 ,p_ParameterList => l_parameter_List);
2728 wf_event.AddParameterToList(p_name => 'publish_child_hierarchy'
2729 ,p_value => l_icc_child_flag
2730 ,p_ParameterList => l_parameter_List);
2731
2732 if (p_pub_type = 'SYNC') then
2733 l_event_name := 'oracle.apps.ego.item.syncItemCatalogCategory';
2734 l_wsdl_url := FND_PROFILE.value('EGO_SYNC_ICC_WSDL_LOC');
2735 l_event_data := '<item:IccQueryIn xmlns:item=\"http://xmlns.oracle.com/ItemCatalogCategorySvc\">\n' ||
2736 '<ICCQueryParam>\n' ||
2737 '<BatchId>' || l_batch_id || '</BatchId>\n' ||
2738 '<ICCPubEntityObject>\n' ||
2739 '<UserDefAttrGrps/>\n' ||
2740 '<Valuesets/>\n' ||
2741 '<ICCVersions/>\n' ||
2742 '<TransAttrs/>\n' ||
2743 '<ICCPages/>\n' ||
2744 '<ICCFunctions/>\n' ||
2745 '<ICCStructure/>\n' ||
2746 '<ParentICCs/>\n' ||
2747 '<ChildICCs/>\n' ||
2748 '<Sync/>\n' ||
2749 '<TriggerImport/>\n' ||
2750 '<ReturnPayload>false</ReturnPayload>\n' ||
2751 '</ICCPubEntityObject>\n' ||
2752 '</ICCQueryParam>\n' ||
2753 '<FndSecurity>\n' ||
2754 '<RESPONSIBILITY_NAME>string</RESPONSIBILITY_NAME>\n' ||
2755 '<RESPONSIBILITY_APPL_NAME>string</RESPONSIBILITY_APPL_NAME>\n' ||
2756 '<SECURITY_GROUP_NAME>string</SECURITY_GROUP_NAME>\n' ||
2757 '<NLS_LANGUAGE>string</NLS_LANGUAGE>\n' ||
2758 '</FndSecurity>\n' ||
2759 '</item:IccQueryIn>';
2760
2761 wf_event.AddParameterToList(p_name => C_SRV_WSDL_URL
2762 ,p_value => l_wsdl_url
2763 ,p_ParameterList => l_parameter_List);
2764 wf_event.AddParameterToList(p_name => C_SRV_NAME
2765 ,p_value => 'ItemCatalogService'
2766 ,p_ParameterList => l_parameter_List);
2767 wf_event.AddParameterToList(p_name => C_SRV_PORT
2768 ,p_value => 'ItemCatalogServicePort'
2769 ,p_ParameterList => l_parameter_List);
2770 wf_event.AddParameterToList(p_name => C_SRV_PORT_TYPE
2771 ,p_value => 'ItemCatalogService'
2772 ,p_ParameterList => l_parameter_List);
2773 wf_event.AddParameterToList(p_name => C_SRV_OPERATION
2774 ,p_value => 'getICCDetails'
2775 ,p_ParameterList => l_parameter_List);
2776 end if; -- end of p_pub_type
2777 end if;
2778
2779 if (p_batch_type = C_BATCH_ENTITY_VS) THEN
2780 l_event_name := 'oracle.apps.ego.extfwk.publishValueSet';
2781
2782 if (p_pub_type = 'SYNC') then
2783 l_event_name := 'oracle.apps.ego.extfwk.syncValueSet';
2784 l_wsdl_url := FND_PROFILE.value('EGO_SYNC_VS_WSDL_LOC');
2785 l_event_data := '<val:ValuesetQueryIn xmlns:val=\"http://xmlns.oracle.com/ValuesetService\">\n' ||
2786 '<ValuesetQueryParam>\n' ||
2787 '<BatchId>' || l_batch_id || '</BatchId>\n' ||
2788 '<Sync/>\n' ||
2789 '<TriggerImport/>\n' ||
2790 '<ReturnPayload>false</ReturnPayload>\n' ||
2791 '</ValuesetQueryParam>\n' ||
2792 '<FndSecurity>\n' ||
2793 '<RESPONSIBILITY_NAME>string</RESPONSIBILITY_NAME>\n' ||
2794 '<RESPONSIBILITY_APPL_NAME>string</RESPONSIBILITY_APPL_NAME>\n' ||
2795 '<SECURITY_GROUP_NAME>string</SECURITY_GROUP_NAME>\n' ||
2796 '<NLS_LANGUAGE>string</NLS_LANGUAGE>\n' ||
2797 '</FndSecurity>\n' ||
2798 '</val:ValuesetQueryIn>';
2799 wf_event.AddParameterToList(p_name => C_SRV_WSDL_URL
2800 ,p_value => l_wsdl_url
2801 ,p_ParameterList => l_parameter_List);
2802 wf_event.AddParameterToList(p_name => C_SRV_NAME
2803 ,p_value => 'ValueSetService'
2804 ,p_ParameterList => l_parameter_List);
2805 wf_event.AddParameterToList(p_name => C_SRV_PORT
2806 ,p_value => 'ValueSetServicePort'
2807 ,p_ParameterList => l_parameter_List);
2808 wf_event.AddParameterToList(p_name => C_SRV_PORT_TYPE
2809 ,p_value => 'ValueSetService'
2810 ,p_ParameterList => l_parameter_List);
2811 wf_event.AddParameterToList(p_name => C_SRV_OPERATION
2812 ,p_value => 'getValueSetDetails'
2813 ,p_ParameterList => l_parameter_List);
2814 end if; -- enf of p_pub_type
2815 end if;
2816
2817 if (p_batch_type = C_BATCH_ENTITY_AG) THEN
2818 l_event_name := 'oracle.apps.ego.extfwk.syncAttributeGroup';
2819 l_wsdl_url := FND_PROFILE.value('EGO_SYNC_AG_WSDL_LOC');
2820 l_event_data := '<att:AGQueryIn xmlns:att=\"http://xmlns.oracle.com/AttributeGroupService\">\n' ||
2821 '<AGQueryParam>\n' ||
2822 '<BatchId>' || l_batch_id || '</BatchId>\n' ||
2823 '<AGPubEntityObject>\n' ||
2824 '<Sync/>\n' ||
2825 '<TriggerImport/>\n' ||
2826 '<ReturnPayload/>\n' ||
2827 '</AGPubEntityObject>\n' ||
2828 '</AGQueryParam>\n' ||
2829 '<FndSecurity>\n' ||
2830 '<RESPONSIBILITY_NAME>string</RESPONSIBILITY_NAME>\n' ||
2831 '<RESPONSIBILITY_APPL_NAME>string</RESPONSIBILITY_APPL_NAME>\n' ||
2832 '<SECURITY_GROUP_NAME>string</SECURITY_GROUP_NAME>\n' ||
2833 '<NLS_LANGUAGE>string</NLS_LANGUAGE>\n' ||
2834 '</FndSecurity>\n' ||
2835 '</att:AGQueryIn>';
2836 wf_event.AddParameterToList(p_name => C_SRV_WSDL_URL
2837 ,p_value => l_wsdl_url
2838 ,p_ParameterList => l_parameter_List);
2839 wf_event.AddParameterToList(p_name => C_SRV_NAME
2840 ,p_value => 'AttributeGroupService'
2841 ,p_ParameterList => l_parameter_List);
2842 wf_event.AddParameterToList(p_name => C_SRV_PORT
2843 ,p_value => 'AttributeGroupServicePort'
2844 ,p_ParameterList => l_parameter_List);
2845 wf_event.AddParameterToList(p_name => C_SRV_PORT_TYPE
2846 ,p_value => 'AttributeGroupService'
2847 ,p_ParameterList => l_parameter_List);
2848 wf_event.AddParameterToList(p_name => C_SRV_OPERATION
2849 ,p_value => 'getAGDetails'
2850 ,p_ParameterList => l_parameter_List);
2851 end if;
2852
2853 l_user_name := fnd_profile.value('USERNAME');--FND_GLOBAL.user_name;
2854 l_passwd_mod := 'EGO' ; -- to avoid compliance failure when arcs in
2855 l_passwd_mod := lower(l_passwd_mod) || '.syncservice';
2856
2857 wf_event.AddParameterToList(p_name => 'WFBES_SOAP_USERNAME'
2858 ,p_value => l_user_name
2859 ,p_ParameterList => l_parameter_List);
2860 wf_event.AddParameterToList(p_name => 'WFBES_SOAP_PASSWORD_MOD'
2861 ,p_value => l_passwd_mod
2862 ,p_ParameterList => l_parameter_List);
2863 wf_event.AddParameterToList(p_name => 'WFBES_SOAP_PASSWORD_KEY'
2864 ,p_value => lower(l_user_name) || '.password'
2865 ,p_ParameterList => l_parameter_List);
2866
2867 l_event_key := l_event_name || '-' || l_event_key;
2868 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2869 ,p_module => l_module_name
2870 ,p_message => 'raise evt ' || l_event_key);
2871
2872 begin
2873 WF_EVENT.Raise(p_event_name => l_event_name
2874 ,p_event_key => l_event_key
2875 ,p_event_data => l_event_data
2876 ,p_parameters => l_parameter_List
2877 ,p_send_date => sysdate);
2878 --l_parameter_List.DELETE;
2879
2880 commit;
2881 exception
2882 WHEN OTHERS THEN
2883 ROLLBACK TO PUB_BAT_STATUS_CHANGE;
2884 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2885 ,p_module => l_module_name
2886 ,p_message => ' publish failure: ' || sqlerrm);
2887 x_return_status := FND_API.G_RET_STS_ERROR;
2888 end;
2889
2890 exception
2891 WHEN OTHERS THEN
2892 ROLLBACK TO PUB_BAT_STATUS_CHANGE;
2893 x_return_status := FND_API.G_RET_STS_ERROR;
2894 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_UNEXPECTED
2895 ,p_module => l_module_name
2896 ,p_message => ' publish failure, general: ' || sqlerrm);
2897
2898 END publish_pvt;
2899
2900 -- end of private functions of public apis
2901
2902 PROCEDURE Create_batch (p_api_version IN NUMBER,
2903 p_batch_name IN VARCHAR2,
2904 p_batch_type IN NUMBER,
2905 p_pub_params IN TBL_OF_BAT_PARAM_TYPE,
2906 p_batch_entities IN TBL_OF_BAT_ENT_OBJ_TYPE,
2907 p_target_systems IN TBL_OF_BAT_SYS_CODE_TYPE,
2908 p_submit_flag IN boolean,
2909 x_new_batch_id OUT NOCOPY NUMBER,
2910 x_return_status OUT NOCOPY VARCHAR2,
2911 x_msg_count OUT NOCOPY NUMBER,
2912 x_msg_data OUT NOCOPY varchar2
2913 )
2914 IS
2915 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.Create_batch';
2916 l_user_id number ;
2917 l_batch_id number ;
2918 l_auto_release VARCHAR2(30);
2919 l_workspace_name varchar2(4000);
2920 l_system_name varchar2(300);
2921 l_system_code varchar2(30);
2922 l_has_invl_sys boolean ;
2923 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
2924 l_pub_params TBL_OF_BAT_PARAM_TYPE;
2925 l_target_systems TBL_OF_BAT_SYS_CODE_TYPE;
2926 l_counter number;
2927 l_batch_status varchar2(1) := C_STATUS_DRAFT; -- draft status
2928 l_batch_entity_obj_ids dbms_sql.number_table;
2929 l_pub_type varchar2(10);
2930 l_expl_date date;
2931
2932 l_token EGO_UTIL_PK.token_tbl;
2933 l_err_msg varchar2(2000) := null;
2934 l_msg_name varchar2(50) ;
2935 l_has_priv BOOLEAN ;
2936
2937 l_resp_id Number;
2938 l_party_id Number;
2939 l_login_id Number;
2940 l_type_param_id Number;
2941 l_ent_obj_id Number;
2942 l_submit_date DATE;
2943
2944 l_name_ct Number;
2945
2946
2947 EGO_PUB_BATCHNAME_NULL EXCEPTION;
2948 EGO_PUB_BATCHNAME_DUP EXCEPTION;
2949 EGO_PUB_BATCHTYPE_NULL EXCEPTION;
2950 EGO_PUB_BATCHTYPE_INVL EXCEPTION;
2951 EGO_PUB_PARAMS_NULL EXCEPTION;
2952 EGO_PUB_ENTITIES_NULL EXCEPTION;
2953 EGO_PUB_TARGET_SYS_NULL EXCEPTION;
2954 EGO_PUB_MUL_ENTITY_REVS EXCEPTION;
2955 BEGIN
2956 x_return_status := FND_API.G_RET_STS_SUCCESS;
2957 /*
2958 * check required parameters: name, type, work space name
2959 */
2960 if (p_batch_name is null) then
2961 raise EGO_PUB_BATCHNAME_NULL;
2962 end if;
2963
2964 if (p_batch_type is null) then
2965 raise EGO_PUB_BATCHTYPE_NULL;
2966 end if;
2967
2968 if (p_pub_params is null or p_pub_params.count = 0) then
2969 raise EGO_PUB_PARAMS_NULL;
2970 end if;
2971
2972 if (p_batch_entities is null or p_batch_entities.count = 0) then
2973 raise EGO_PUB_ENTITIES_NULL;
2974 end if;
2975
2976 if (p_target_systems is null or p_target_systems.count = 0) then
2977 raise EGO_PUB_TARGET_SYS_NULL;
2978 end if;
2979
2980 if (p_batch_type not in (C_BATCH_ENTITY_ICC, C_BATCH_ENTITY_VS, C_BATCH_ENTITY_ITEM, C_BATCH_ENTITY_AG) ) THEN
2981 raise EGO_PUB_BATCHTYPE_INVL;
2982 END IF;
2983
2984 begin
2985 select 1 into l_name_ct from EGO_PUB_BAT_HDR_B
2986 where batch_name = p_batch_name;
2987 if l_name_ct >= 1 then
2988 raise EGO_PUB_BATCHNAME_DUP;
2989 end if;
2990 exception
2991 when no_data_found then
2992 null; -- it should be unqiue
2993 end;
2994
2995 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2996 ,p_module => l_module_name
2997 ,p_message => '-- Create_batch Params --');
2998 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
2999 ,p_module => l_module_name
3000 ,p_message => 'p_api_version ' || to_char(p_api_version));
3001 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3002 ,p_module => l_module_name
3003 ,p_message => 'p_batch_name ' || p_batch_name);
3004 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3005 ,p_module => l_module_name
3006 ,p_message => 'p_batch_type ' || to_char(p_batch_type));
3007 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3008 ,p_module => l_module_name
3009 ,p_message => 'p_submit_flag ' ||
3010 (case p_submit_flag when true then 'true' when false then 'false' end));
3011
3012 -- check batch parameters
3013 /*l_pub_params := p_pub_params;*/
3014 validateBatchParams(p_batch_type, p_batch_name, p_pub_params,
3015 l_pub_params, x_return_status, x_msg_count, x_msg_data);
3016 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3017 return ;
3018 end if;
3019
3020 -- check target system valid or not
3021 validateSystems(p_target_systems, l_target_systems,
3022 x_return_status, x_msg_count, x_msg_data);
3023 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3024 return ;
3025 end if;
3026
3027 -- initialize these local varialbes
3028 l_user_id := FND_GLOBAL.user_id;
3029 l_resp_id := FND_GLOBAL.resp_id;
3030 l_login_id := FND_GLOBAL.login_id;
3031
3032 -- remove duplicated entities
3033 l_batch_entities := removeDupEntities(p_batch_type, p_batch_entities);
3034 -- entities should be valid
3035 validateEntities(p_batch_type, l_batch_entities,
3036 x_return_status, x_msg_count, x_msg_data);
3037 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3038 return ;
3039 end if;
3040 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3041 ,P_MODULE => L_MODULE_NAME
3042 ,p_message => 'checked invalid Entities, x_return_status ' || x_return_status);
3043
3044 -- entities with same id and different version are exception case
3045 hasMultiEntRev(p_batch_type, l_batch_entities,
3046 x_return_status, x_msg_count, x_msg_data);
3047 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3048 raise EGO_PUB_MUL_ENTITY_REVS;
3049 end if;
3050 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3051 ,p_module => l_module_name
3052 ,p_message => 'checked multi EntRevs, x_return_status ' || x_return_status);
3053
3054 l_expl_date := getExplosionDate(l_pub_params);
3055 hasPublishPrivilege(l_user_id, p_batch_type, p_batch_entities,
3056 l_expl_date, x_return_status, x_msg_count, x_msg_data);
3057 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3058 return ;
3059 end if;
3060 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3061 ,P_MODULE => L_MODULE_NAME
3062 ,p_message => 'checked Privs, x_return_status ' || x_return_status);
3063
3064 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3065 ,p_module => l_module_name
3066 ,p_message => 'user has privilege to publish');
3067 -- end of input validation
3068
3069 if (p_submit_flag ) then
3070 SELECT nvl(customer_id , person_party_id) into l_party_id
3071 FROM fnd_user WHERE user_id = l_user_id;
3072 l_submit_date := sysdate ;
3073 else
3074 l_party_id := null;
3075 l_submit_date := null;
3076 end if;
3077
3078 x_return_status := FND_API.G_RET_STS_SUCCESS;
3079 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3080 ,P_MODULE => L_MODULE_NAME
3081 ,p_message => 'x_return_status ' || x_return_status);
3082
3083 -- insert data into batch tables
3084 -- header, params, entities, target systems, status(entities*systems)
3085 select EGO_PUB_BAT_HDR_S1.nextVal into l_batch_id from dual;
3086 -- insert batch header with batch_name and batch_type, batch doesn't has tl tables
3087 insert into EGO_PUB_BAT_HDR_B (
3088 BATCH_ID,
3089 BATCH_NAME,
3090 BATCH_TYPE,
3091 PUBLISHED_BY,
3092 RESPONSIBILITY_ID,
3093 BATCH_CREATION_DATE,
3094 LAST_UPDATE_DATE,
3095 LAST_UPDATED_BY,
3096 CREATION_DATE,
3097 CREATED_BY,
3098 LAST_UPDATE_LOGIN,
3099 OBJECT_VERSION_NUMBER
3100 )
3101 Values (
3102 l_batch_id,
3103 p_batch_name,
3104 p_batch_type,
3105 l_party_id,
3106 l_resp_id,
3107 l_submit_date,
3108 sysdate,
3109 l_user_id,
3110 sysdate,
3111 l_user_id,
3112 l_login_id,
3113 p_api_version
3114 );
3115
3116 -- insert batch params with pub_params and batch_id (mapping to type_id)
3117 For i in 1..l_pub_params.count Loop
3118 debugBatchParam(l_module_name, l_pub_params(i));
3119
3120 select EGO_PUB_BAT_PARAMS_S1.NEXTVAL into l_type_param_id from dual;
3121 insert into EGO_PUB_BAT_PARAMS_B (
3122 TYPE_PARAM_ID,
3123 TYPE_ID,
3124 TYPE,
3125 PARAMETER_NAME,
3126 DATA_TYPE,
3127 DATE_VALUE,
3128 CHAR_VALUE,
3129 NUMERIC_VALUE,
3130 LAST_UPDATE_DATE,
3131 LAST_UPDATED_BY,
3132 CREATION_DATE,
3133 CREATED_BY,
3134 LAST_UPDATE_LOGIN,
3135 OBJECT_VERSION_NUMBER
3136 )
3137 values (
3138 l_type_param_id,
3139 l_batch_id,
3140 l_pub_params(i).param_type,
3141 l_pub_params(i).param_name,
3142 l_pub_params(i).data_type,
3143 l_pub_params(i).date_value,
3144 l_pub_params(i).char_value,
3145 l_pub_params(i).number_value,
3146 sysdate,
3147 l_user_id,
3148 sysdate,
3149 l_user_id,
3150 l_login_id,
3151 p_api_version
3152 );
3153 end LOOP;
3154
3155 -- insert batch entities with batch_entities and batch_id
3156 for i in 1..l_batch_entities.count loop
3157 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3158 ,p_module => l_module_name
3159 ,p_message => 'l_batch_entities '
3160 || ', pk1:' || nvl(l_batch_entities(i).pk1_value, 'null')
3161 || ', pk2:' || nvl(l_batch_entities(i).pk2_value, 'null')
3162 || ', pk3:' || nvl(l_batch_entities(i).pk3_value, 'null')
3163 || ', pk4:' || nvl(l_batch_entities(i).pk4_value, 'null')
3164 || ', pk5:' || nvl(l_batch_entities(i).pk5_value, 'null')
3165 || ', user_entered:' || nvl(l_batch_entities(i).user_entered, 'null')
3166 );
3167 select EGO_PUB_BAT_ENT_OBJS_S1.NEXTVAL into l_ent_obj_id from dual;
3168 insert into EGO_PUB_BAT_ENT_OBJS_B (
3169 batch_entity_object_id,
3170 batch_id,
3171 entity_type_id,
3172 pk1_value,
3173 pk2_value,
3174 pk3_value,
3175 pk4_value,
3176 pk5_value,
3177 user_entered,
3178 LAST_UPDATE_DATE,
3179 LAST_UPDATED_BY,
3180 CREATION_DATE,
3181 CREATED_BY,
3182 LAST_UPDATE_LOGIN,
3183 OBJECT_VERSION_NUMBER
3184 )
3185 values (
3186 l_ent_obj_id,
3187 l_batch_id,
3188 p_batch_type,
3189 l_batch_entities(i).pk1_value,
3190 l_batch_entities(i).pk2_value,
3191 l_batch_entities(i).pk3_value,
3192 l_batch_entities(i).pk4_value,
3193 l_batch_entities(i).pk5_value,
3194 l_batch_entities(i).user_entered,
3195 sysdate,
3196 l_user_id,
3197 sysdate,
3198 l_user_id,
3199 l_login_id,
3200 p_api_version
3201 );
3202 l_batch_entity_obj_ids(i) := l_ent_obj_id;
3203 end loop;
3204
3205 -- insert target systems with target_systems and batch_id
3206 for i in 1..l_target_systems.count loop
3207 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3208 ,P_MODULE => L_MODULE_NAME
3209 ,P_MESSAGE => ' system code: ' || l_target_systems(i).SYSTEM_CODE );
3210 insert into EGO_PUB_BAT_SYSTEMS_B (
3211 batch_id,
3212 system_code,
3213 LAST_UPDATE_DATE,
3214 LAST_UPDATED_BY,
3215 CREATION_DATE,
3216 CREATED_BY,
3217 LAST_UPDATE_LOGIN,
3218 OBJECT_VERSION_NUMBER
3219 )
3220 VALUES (
3221 l_batch_id,
3222 l_target_systems(i).system_code,
3223 sysdate,
3224 l_user_id,
3225 sysdate,
3226 l_user_id,
3227 l_login_id,
3228 p_api_version
3229 );
3230 end loop;
3231
3232 if p_submit_flag then
3233 l_batch_status := C_STATUS_SUBMITTED;
3234 end if;
3235 -- insert batch status for each entity, system pair
3236 for i in 1..l_batch_entity_obj_ids.count loop
3237 for j in 1..l_target_systems.count loop
3238 insert into EGO_PUB_BAT_STATUS_B (
3239 batch_id,
3240 system_code,
3241 batch_entity_object_id,
3242 status_code,
3243 LAST_UPDATE_DATE,
3244 LAST_UPDATED_BY,
3245 CREATION_DATE,
3246 CREATED_BY,
3247 LAST_UPDATE_LOGIN,
3248 OBJECT_VERSION_NUMBER
3249 )
3250 values (
3251 l_batch_id,
3252 l_target_systems(j).system_code,
3253 l_batch_entity_obj_ids(i),
3254 l_batch_status,
3255 sysdate,
3256 l_user_id,
3257 sysdate,
3258 l_user_id,
3259 l_login_id,
3260 p_api_version
3261 );
3262 end loop;
3263 end loop;
3264
3265 commit;
3266
3267 x_new_batch_id := l_batch_id;
3268 if (p_submit_flag ) then
3269 begin
3270 publish_pvt(l_batch_id, p_batch_type, p_batch_entities,
3271 l_pub_type, false, x_return_status, x_msg_count, x_msg_data);
3272
3273 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3274 raise EGO_PUB_PUBLISH_FAILED;
3275 end if;
3276 exception
3277 when others then
3278 l_err_msg := ' publish failure: ' || l_batch_id || '; ' ||sqlerrm ;
3279 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3280 ,p_module => l_module_name
3281 ,p_message => l_err_msg
3282 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3283 x_return_status := FND_API.G_RET_STS_ERROR;
3284 x_msg_data := l_err_msg;
3285 raise EGO_PUB_PUBLISH_FAILED;
3286 end;
3287 end if;
3288 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3289 ,P_MODULE => L_MODULE_NAME
3290 ,p_message => 'creat_batch end: x_return_status ' || x_return_status);
3291
3292 exception
3293 -- EGO_INVALID_RESPONSIBILITY_ID
3294 when EGO_PUB_BATCHNAME_NULL then
3295 x_return_status := FND_API.G_RET_STS_ERROR;
3296 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3297 ,p_module => l_module_name
3298 ,p_message => ' batch name is null'
3299 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3300 l_token(1).token_name := 'PARAM';
3301 l_token(1).token_value := 'p_batch_name';
3302 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3303 , p_message =>'EGO_PUB_API_PARAM_NULL'
3304 , p_token => l_token);
3305 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3306 ,p_msg_data => x_msg_data );
3307 when EGO_PUB_BATCHNAME_DUP then
3308 x_return_status := FND_API.G_RET_STS_ERROR;
3309 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3310 ,p_module => l_module_name
3311 ,p_message => p_batch_name || ', batch name is duplicated '
3312 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3313 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3314 ,p_message =>'EGO_BATCH_DUP_NAME');
3315 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3316 ,p_msg_data => x_msg_data );
3317
3318 when EGO_PUB_BATCHTYPE_NULL then
3319 x_return_status := FND_API.G_RET_STS_ERROR;
3320 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3321 ,p_module => l_module_name
3322 ,p_message => ' batch type is null'
3323 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3324 l_token(1).token_name := 'PARAM';
3325 l_token(1).token_value := 'p_batch_type';
3326 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3327 , p_message =>'EGO_PUB_API_PARAM_NULL'
3328 , p_token => l_token);
3329 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3330 ,p_msg_data => x_msg_data );
3331
3332 when EGO_PUB_BATCHTYPE_INVL then
3333 x_return_status := FND_API.G_RET_STS_ERROR;
3334 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3335 ,p_module => l_module_name
3336 ,p_message => ' batch type is invalid'
3337 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3338 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3339 ,p_message =>'EGO_INVALID_BATCH_TYPE');
3340 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3341 ,p_msg_data => x_msg_data );
3342
3343 when EGO_PUB_PARAMS_NULL then
3344 x_return_status := FND_API.G_RET_STS_ERROR;
3345 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3346 ,p_module => l_module_name
3347 ,p_message => ' batch param is null'
3348 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3349 l_token(1).token_name := 'PARAM';
3350 l_token(1).token_value := 'p_pub_params';
3351 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3352 , p_message =>'EGO_PUB_API_PARAM_NULL'
3353 , p_token => l_token);
3354 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3355 ,p_msg_data => x_msg_data );
3356
3357 when EGO_PUB_ENTITIES_NULL then
3358 x_return_status := FND_API.G_RET_STS_ERROR;
3359 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3360 ,p_module => l_module_name
3361 ,p_message => ' EGO_NO_ITEMS_TO_PUBLISH'
3362 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3363 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3364 ,p_message =>'EGO_NO_ITEMS_TO_PUBLISH');
3365 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3366 ,p_msg_data => x_msg_data );
3367
3368 when EGO_PUB_TARGET_SYS_NULL then
3369 x_return_status := FND_API.G_RET_STS_ERROR;
3370 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3371 ,p_module => l_module_name
3372 ,p_message => ' target system is null'
3373 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3374 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3375 ,p_message =>'ADD_TARGET_SYSTEMS');
3376 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3377 ,p_msg_data => x_msg_data );
3378
3379 when EGO_PUB_MUL_ENTITY_REVS then
3380 x_return_status := FND_API.G_RET_STS_ERROR;
3381 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3382 ,p_module => l_module_name
3383 ,p_message => ' it has entities with multi-rev '
3384 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3385 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3386 ,p_message =>'EGO_PUB_MUL_ENTITY_REVS');
3387 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3388 ,p_msg_data => x_msg_data );
3389
3390 when EGO_PUB_PUBLISH_FAILED then
3391 x_return_status := FND_API.G_RET_STS_ERROR;
3392 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3393 ,p_module => l_module_name
3394 ,p_message => ' raise event failed'
3395 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3396 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3397 ,p_message =>'EGO_PUBLISH_EVENT_FAILED');
3398 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3399 ,p_msg_data => x_msg_data );
3400
3401 when others then
3402 l_err_msg := ' others error ' ||sqlerrm ;
3403 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3404 ,p_module => l_module_name
3405 ,p_message => l_err_msg
3406 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3408 x_msg_data := l_err_msg;
3409
3410 END Create_batch;
3411
3412 --It publish the not submitted batch, it supports publish multi-batches
3413 -- oracle.apps.ego.extfwk.admin.webui.EgoAGPublishSystemsCO, it populate
3414 -- entities, systems rows, set batch status, populate system/status pair
3415 -- oracle.apps.ego.extfwk.admin.webui.EgoAGPubBtnBrCO, it calls
3416 -- EgoUtil.raiseBusinessEvent with event name and dest params
3417 -- oracle.apps.ego.common.util.EgoUtil.java, raiseBusinessEvent,
3418 -- event names with entity
3419 -- Item: oracle.apps.ego.item.publishItem / oracle.apps.bom.structure.publishStructure
3420 -- ICC: oracle.apps.ego.item.syncItemCatalogCategory
3421 -- AG: oracle.apps.ego.extfwk.syncAttributeGroup
3422 -- VS: oracle.apps.ego.extfwk.syncValueSet
3423 PROCEDURE publish_batch (p_api_version IN NUMBER,
3424 p_batch_names IN dbms_sql.varchar2_table,
3425 p_batch_ids IN dbms_sql.number_table,
3426 x_return_status OUT NOCOPY VARCHAR2,
3427 x_msg_count OUT NOCOPY NUMBER,
3428 x_msg_data OUT NOCOPY VARCHAR2
3429 )
3430 IS
3431 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.publish_batch';
3432 --l_batch_names dbms_sql.varchar2_table ;
3433 --l_batch_ids dbms_sql.number_table ;
3434 l_batch_name varchar2(50);
3435 l_batch_id number;
3436 l_batch_type number;
3437 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
3438 l_pub_type varchar2(10);
3439 l_creator_id number;
3440 l_status varchar2(1);
3441 l_params_count number;
3442
3443 l_invl_count number := 0;
3444 l_failed_count number := 0;
3445 l_token EGO_UTIL_PK.token_tbl;
3446 l_err_msg varchar2(2000) := null;
3447
3448 EGO_PUB_BATCH_NAMEIDS_NULL EXCEPTION;
3449 EGO_PUB_BATCH_NAMEIDS_INVL EXCEPTION;
3450 EGO_PUB_BATCH_NOT_CREATOR EXCEPTION;
3451 EGO_PUB_BATCH_NOT_DRAFT EXCEPTION;
3452 BEGIN
3453 x_return_status := FND_API.G_RET_STS_SUCCESS;
3454
3455 if (p_batch_names.count = 0 and p_batch_ids.count = 0) then
3456 raise EGO_PUB_BATCH_NAMEIDS_NULL;
3457 end if;
3458
3459 if (p_batch_names.count > 0 and p_batch_ids.count > 0
3460 and p_batch_names.count <> p_batch_ids.count) then
3461 raise EGO_PUB_BATCH_NAMEIDS_INVL;
3462 end if;
3463
3464 if (p_batch_names.count > 0 and p_batch_ids.count = 0) then
3465 l_params_count := p_batch_names.count;
3466 elsif (p_batch_names.count = 0 and p_batch_ids.count > 0) then
3467 l_params_count := p_batch_ids.count;
3468 else
3469 l_params_count := p_batch_names.count;
3470 end if;
3471 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3472 ,p_module => l_module_name
3473 ,p_message => ' params count: ' || l_params_count);
3474
3475 -- raise biz event
3476 FOR i IN 1..l_params_count LOOP
3477 l_batch_name := null;
3478 l_batch_id := null;
3479 if (p_batch_names.count > 0) then
3480 l_batch_name := p_batch_names(i);
3481 end if;
3482 if (p_batch_ids.count > 0) then
3483 l_batch_id := p_batch_ids(i);
3484 end if;
3485
3486 begin
3487 if (l_batch_name is not null and l_batch_id is not null) then
3488 select batch_id, batch_type, created_by
3489 into l_batch_id, l_batch_type, l_creator_id
3490 from ego_pub_bat_hdr_b
3491 where batch_name = l_batch_name and batch_id = l_batch_id;
3492 elsif (l_batch_name is not null and l_batch_id is null) then
3493 select batch_id, batch_type, created_by
3494 into l_batch_id, l_batch_type, l_creator_id
3495 from ego_pub_bat_hdr_b
3496 where batch_name = l_batch_name ;
3497 elsif (l_batch_name is null and l_batch_id is not null) then
3498 select batch_id, batch_type, created_by
3499 into l_batch_id, l_batch_type, l_creator_id
3500 from ego_pub_bat_hdr_b
3501 where batch_id = l_batch_id;
3502 else
3503 raise no_data_found;
3504 end if;
3505
3506 if (l_creator_id <> FND_GLOBAL.user_id) then
3507 raise EGO_PUB_BATCH_NOT_CREATOR;
3508 end if;
3509
3510 select status_code into l_status
3511 from EGO_PUB_BAT_STATUS_B
3512 where batch_id = l_batch_id and rownum = 1;
3513 if (l_status <> EGO_PUB_FWK_PK.C_STATUS_DRAFT) then
3514 raise EGO_PUB_BATCH_NOT_DRAFT;
3515 end if;
3516
3517 -- for item type, get batch entities to check privileges also
3518 if (l_batch_type = C_BATCH_ENTITY_ITEM ) then
3519 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3520 ,p_module => l_module_name
3521 ,p_message => 'get items');
3522 l_batch_entities := getBatchEntities(l_batch_id);
3523 else
3524 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3525 ,p_module => l_module_name
3526 ,p_message => 'get meta pub type');
3527 select nvl(char_value, 'PUBLISH') into l_pub_type
3528 from ego_pub_bat_params_b
3529 where type_id = l_batch_id AND PARAMETER_NAME = C_PARAM_PUBTYPE;
3530 end if;
3531
3532 publish_pvt(l_batch_id, l_batch_type, l_batch_entities,
3533 l_pub_type, true, x_return_status, x_msg_count, x_msg_data);
3534
3535 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
3536 l_failed_count := l_failed_count + 1;
3537 end if;
3538 exception
3539 when no_data_found then
3540 l_err_msg := ' invalid batch name/id pair: ' || l_batch_name
3541 || ', ' || l_batch_id;
3542 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3543 ,p_module => l_module_name
3544 ,p_message => l_err_msg);
3545 l_invl_count := l_invl_count + 1;
3546
3547 when EGO_PUB_BATCH_NOT_CREATOR then
3548 l_err_msg := ' the user is not batch creator ';
3549 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3550 ,p_module => l_module_name
3551 ,p_message => l_err_msg);
3552 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3553 ,p_message =>'EGO_PUB_BATCH_NOT_CREATOR');
3554 x_return_status := FND_API.G_RET_STS_ERROR;
3555 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3556 ,p_msg_data => x_msg_data );
3557 l_invl_count := l_invl_count + 1;
3558
3559 when EGO_PUB_BATCH_NOT_DRAFT then
3560 l_err_msg := ' the batch status is not draft ';
3561 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3562 ,p_module => l_module_name
3563 ,p_message => l_err_msg);
3564 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3565 ,p_message =>'EGO_PUB_BATCH_NOT_DRAFT');
3566 x_return_status := FND_API.G_RET_STS_ERROR;
3567 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3568 ,p_msg_data => x_msg_data );
3569 l_invl_count := l_invl_count + 1;
3570
3571 when others then
3572 l_invl_count := l_invl_count + 1;
3573 l_err_msg := ' others error ' || l_batch_name || ',' ||sqlerrm ;
3574 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3575 ,p_module => l_module_name
3576 ,p_message => l_err_msg);
3577 end;
3578 END LOOP;
3579
3580 if (l_invl_count = l_params_count) then
3581 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3582 ,p_module => l_module_name
3583 ,p_message => 'no valid name, id pair');
3584 raise EGO_PUB_BATCH_NAMEIDS_INVL;
3585 end if;
3586 if (l_failed_count > 0 ) then
3587 raise EGO_PUB_PUBLISH_FAILED;
3588 end if;
3589
3590 EXCEPTION
3591 when EGO_PUB_BATCH_NAMEIDS_NULL then
3592 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3593 ,p_module => l_module_name
3594 ,p_message => ' names & ids are null ');
3595 l_token(1).token_name := 'PARAM';
3596 l_token(1).token_value := 'p_batch_ids & p_batch_names';
3597 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3598 , p_message =>'EGO_PUB_API_PARAM_NULL'
3599 , p_token => l_token);
3600 x_return_status := FND_API.G_RET_STS_ERROR;
3601 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3602 ,p_msg_data => x_msg_data );
3603
3604 when EGO_PUB_BATCH_NAMEIDS_INVL then
3605 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3606 ,p_module => l_module_name
3607 ,p_message => ' names, ids length not match, no valid pair');
3608 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3609 ,p_message =>'EGO_PUB_BATCH_NAMEIDS_INVL');
3610 x_return_status := FND_API.G_RET_STS_ERROR;
3611 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3612 ,p_msg_data => x_msg_data );
3613
3614 when EGO_PUB_PUBLISH_FAILED then
3615 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3616 ,p_module => l_module_name
3617 ,p_message => ' it has failed publish batches'
3618 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3619 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3620 ,p_message =>'EGO_PUBLISH_EVENT_FAILED');
3621 x_return_status := FND_API.G_RET_STS_ERROR;
3622 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3623 ,p_msg_data => x_msg_data );
3624
3625 end publish_batch;
3626
3627 --It create new batch with existing batch detail by the batch_name or batch_id,
3628 -- if both not null, using them to find batch, otherwise using name/id to find
3629 -- refer to EgoPublishBatchItemAMImpl.CopyEntityFromBatchId
3630 -- impl the item view/publis priv check
3631 PROCEDURE Copy_batch (p_api_version IN NUMBER,
3632 p_batch_name IN VARCHAR2,
3633 p_batch_id IN NUMBER,
3634 p_submit_flag IN boolean,
3635 x_new_batch_id OUT NOCOPY NUMBER,
3636 x_return_status OUT NOCOPY VARCHAR2,
3637 x_msg_count OUT NOCOPY NUMBER,
3638 x_msg_data OUT NOCOPY VARCHAR2
3639 )
3640 IS
3641 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.copy_batch';
3642
3643 l_batch_id number;
3644 l_batch_name varchar2(4000);
3645 l_batch_type varchar2(30);
3646 l_workspace_name varchar2(100);
3647 l_auto_release VARCHAR2(30);
3648 l_pub_params TBL_OF_BAT_PARAM_TYPE;
3649 l_batch_entities TBL_OF_BAT_ENT_OBJ_TYPE;
3650 l_target_systems TBL_OF_BAT_SYS_CODE_TYPE;
3651 l_system_codes dbms_sql.varchar2_table;
3652 l_param_types dbms_sql.number_table;
3653 l_param_names dbms_sql.varchar2_table;
3654 l_param_data_types dbms_sql.number_table;
3655 l_param_date_vals dbms_sql.date_table;
3656 l_param_char_vals dbms_sql.varchar2_table;
3657 l_param_num_vals dbms_sql.number_table;
3658 l_counter number;
3659
3660 l_token EGO_UTIL_PK.token_tbl;
3661 l_err_msg varchar2(2000) := null;
3662 EGO_PUB_BATCHNAMEID_NULL EXCEPTION;
3663 EGO_PUB_BATCHNAMEID_INVL EXCEPTION;
3664 EGO_PUB_BATCHNAMEID_DUP EXCEPTION;
3665 begin
3666 /*
3667 * check parameter valid: name, id
3668 * check user has pub/sync privileges of icc/ag/vs
3669 * check user has view item priv
3670 * check user has publish item priv
3671 * check the selected item rev is implemented
3672 */
3673 x_return_status := FND_API.G_RET_STS_SUCCESS;
3674
3675 if (p_batch_id is null and p_batch_name is null) then
3676 raise EGO_PUB_BATCHNAMEID_NULL;
3677 end if;
3678
3679 begin
3680 if (p_batch_name is not null and p_batch_id is not null) then
3681 select batch_id, batch_name, batch_type
3682 into l_batch_id, l_batch_name, l_batch_type
3683 from ego_pub_bat_hdr_b
3684 where batch_name = p_batch_name and batch_id = p_batch_id;
3685 elsif (p_batch_name is not null and p_batch_id is null) then
3686 select batch_id, batch_name, batch_type
3687 into l_batch_id, l_batch_name, l_batch_type
3688 from ego_pub_bat_hdr_b
3689 where batch_name = p_batch_name ;
3690 elsif (p_batch_name is null and p_batch_id is not null) then
3691 select batch_id, batch_name, batch_type
3692 into l_batch_id, l_batch_name, l_batch_type
3693 from ego_pub_bat_hdr_b
3694 where batch_id = p_batch_id;
3695 else
3696 raise no_data_found;
3697 end if;
3698
3699 -- build params
3700 select TYPE, PARAMETER_NAME, DATA_TYPE, DATE_VALUE,
3701 CHAR_VALUE, NUMERIC_VALUE
3702 BULK COLLECT INTO l_param_types, l_param_names, l_param_data_types,
3703 l_param_date_vals, l_param_char_vals, l_param_num_vals
3704 from EGO_PUB_BAT_PARAMS_B where TYPE_ID = l_batch_id ;
3705 l_counter := 1;
3706
3707 for i in 1..l_param_types.COUNT loop
3708 l_pub_params(l_counter).param_type := l_param_types(i);
3709 l_pub_params(l_counter).param_name := l_param_names(i);
3710 l_pub_params(l_counter).data_type := l_param_data_types(i);
3711 l_pub_params(l_counter).date_value := l_param_date_vals(i);
3712 l_pub_params(l_counter).char_value := l_param_char_vals(i);
3713 l_pub_params(l_counter).number_value := l_param_num_vals(i);
3714 l_counter := l_counter + 1;
3715 end loop;
3716 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3717 ,p_module => l_module_name
3718 ,p_message => 'get pub params, ' || l_pub_params.count);
3719
3720 -- build entities
3721 l_batch_entities := getBatchEntities(l_batch_id);
3722 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3723 ,p_module => l_module_name
3724 ,p_message => 'get pub entities, ' || l_batch_entities.count);
3725
3726 -- build target systems
3727 select SYSTEM_CODE
3728 BULK COLLECT INTO l_system_codes
3729 from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = l_batch_id;
3730 l_counter := 1;
3731 for i in 1..l_system_codes.COUNT loop
3732 l_target_systems(l_counter).system_code := l_system_codes(i);
3733 l_counter := l_counter + 1;
3734 end loop;
3735 exception
3736 when too_many_rows then
3737 raise EGO_PUB_BATCHNAMEID_DUP;
3738 when no_data_found then
3739 raise EGO_PUB_BATCHNAMEID_INVL;
3740 end;
3741 -- after get batch info, build variables to call create_batch
3742
3743 -- if the batch is copied, add a counter to the name
3744 l_counter := 0;
3745 l_batch_name := l_batch_name || '_copy';
3746 select count(1) into l_counter
3747 from ego_pub_bat_hdr_b
3748 where batch_name like l_batch_name || '%';
3749
3750 if (l_counter > 0) then
3751 l_batch_name := l_batch_name || to_char(l_counter + 1);
3752 end if;
3753
3754 create_batch(p_api_version, l_batch_name, l_batch_type,
3755 l_pub_params, l_batch_entities, l_target_systems, p_submit_flag,
3756 x_new_batch_id, x_return_status, x_msg_count, x_msg_data);
3757
3758 exception
3759 when EGO_PUB_BATCHNAMEID_NULL then
3760 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3761 ,p_module => l_module_name
3762 ,p_message => ' batch_name & id are null '
3763 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3764 x_return_status := FND_API.G_RET_STS_ERROR;
3765 l_token(1).token_name := 'PARAM';
3766 l_token(1).token_value := 'p_batch_name & p_batch_id';
3767 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3768 , p_message =>'EGO_PUB_API_PARAM_NULL'
3769 , p_token => l_token);
3770 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3771 ,p_msg_data => x_msg_data );
3772
3773 when EGO_PUB_BATCHNAMEID_DUP then
3774 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3775 ,p_module => l_module_name
3776 ,p_message => ' batch_name, id pair has dup '
3777 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3778 x_return_status := FND_API.G_RET_STS_ERROR;
3779 ego_util_pk.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3780 ,p_message =>'EGO_PUB_BATCHNAMEID_DUP');
3781 ego_util_pk.count_and_get (p_msg_count => x_msg_count
3782 ,p_msg_data => x_msg_data );
3783
3784 when EGO_PUB_BATCHNAMEID_INVL then
3785 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3786 ,p_module => l_module_name
3787 ,p_message => ' batch_name, id pair is invalid '
3788 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3789 x_return_status := FND_API.G_RET_STS_ERROR;
3790 ego_util_pk.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3791 ,p_message =>'EGO_PUB_BATCHNAMEID_INVL');
3792 ego_util_pk.count_and_get (p_msg_count => x_msg_count
3793 ,p_msg_data => x_msg_data );
3794
3795 when others then
3796 l_err_msg := ' others error ' ||sqlerrm ;
3797 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3798 ,p_module => l_module_name
3799 ,p_message => l_err_msg
3800 ||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
3801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3802 x_msg_data := l_err_msg;
3803 end copy_batch;
3804
3805 PROCEDURE delete_batch (p_api_version IN NUMBER,
3806 p_batch_names IN dbms_sql.varchar2_table,
3807 p_batch_ids IN dbms_sql.number_table,
3808 p_purge_days IN NUMBER,
3809 x_con_req_id OUT NOCOPY NUMBER,
3810 x_return_status OUT NOCOPY VARCHAR2,
3811 x_msg_count OUT NOCOPY NUMBER,
3812 x_msg_data OUT NOCOPY VARCHAR2
3813 )
3814 IS
3815 l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.delete_batch';
3816 l_batch_name varchar2(50);
3817 l_batch_id number;
3818 l_batch_ids varchar2(3000);
3819 l_user_id number;
3820 l_has_priv boolean;
3821 l_params_count number;
3822
3823 l_con_req_id number := -1;
3824 l_token EGO_UTIL_PK.token_tbl;
3825 l_err_msg varchar2(2000) := null;
3826
3827 EGO_PUB_BATCH_PARAMS_NULL EXCEPTION;
3828 EGO_PUB_BATCH_NAMEIDS_INVL EXCEPTION;
3829 EGO_PUB_BATCH_PURGE_CP_FAIL EXCEPTION;
3830 begin
3831 /*
3832 * check parameter valid: names, ids
3833 */
3834 x_return_status := FND_API.G_RET_STS_SUCCESS;
3835
3836 if (p_batch_names.count = 0
3837 and p_batch_ids.count = 0
3838 and p_purge_days is null) then
3839 raise EGO_PUB_BATCH_PARAMS_NULL;
3840 end if;
3841
3842 if (p_batch_names.count > 0 and p_batch_ids.count > 0
3843 and p_batch_names.count <> p_batch_ids.count) then
3844 raise EGO_PUB_BATCH_NAMEIDS_INVL;
3845 end if;
3846
3847 if (p_batch_names.count > 0 and p_batch_ids.count = 0) then
3848 l_params_count := p_batch_names.count;
3849 elsif (p_batch_names.count = 0 and p_batch_ids.count > 0) then
3850 l_params_count := p_batch_ids.count;
3851 else
3852 l_params_count := p_batch_names.count;
3853 end if;
3854 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3855 ,p_module => l_module_name
3856 ,p_message => ' params count: ' || l_params_count);
3857
3858 if (p_batch_names.count > 0 or p_batch_ids.count > 0) then
3859 FOR i IN 1..l_params_count LOOP
3860 if (p_batch_names.count > 0) then
3861 l_batch_name := p_batch_names(i);
3862 end if;
3863 if (p_batch_ids.count > 0) then
3864 l_batch_id := p_batch_ids(i);
3865 end if;
3866 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3867 ,p_module => l_module_name
3868 ,p_message => ' name:' || l_batch_name
3869 || ', id:' || l_batch_id);
3870
3871 begin
3872 if (l_batch_name is not null and l_batch_id is not null) then
3873 select batch_id into l_batch_id
3874 from ego_pub_bat_hdr_b
3875 where batch_name = l_batch_name and batch_id = l_batch_id;
3876 elsif (l_batch_name is not null and l_batch_id is null) then
3877 select batch_id into l_batch_id
3878 from ego_pub_bat_hdr_b
3879 where batch_name = l_batch_name ;
3880 elsif (l_batch_name is null and l_batch_id is not null) then
3881 select batch_id into l_batch_id
3882 from ego_pub_bat_hdr_b
3883 where batch_id = l_batch_id;
3884 else
3885 raise no_data_found;
3886 end if;
3887
3888 -- concurrent's input limit the length as 200
3889 --exit when length(l_batch_ids) + length(l_batch_id) + 2 > 200 ;
3890
3891 if (l_batch_ids is null) then
3892 l_batch_ids := l_batch_id;
3893 else
3894 l_batch_ids := l_batch_ids || ', ' || l_batch_id;
3895 end if;
3896 exception
3897 when no_data_found then
3898 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3899 ,p_module => l_module_name
3900 ,p_message => 'invalid name/id pair, no data');
3901 when too_many_rows then
3902 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3903 ,p_module => l_module_name
3904 ,p_message => 'invalid name/id pair, dup data');
3905 end;
3906 end loop;
3907
3908 if (l_batch_ids is null or '' = l_batch_ids or length(l_batch_ids) = 0) then
3909 raise EGO_PUB_BATCH_NAMEIDS_INVL;
3910 end if;
3911 end if;
3912
3913
3914 -- the publish history use attribute group's function name
3915 l_user_id := FND_GLOBAL.user_id;
3916 hasPublishSyncPriv(l_user_id, C_BATCH_ENTITY_AG,
3917 x_return_status, x_msg_count, x_msg_data);
3918 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
3919 return ;
3920 end if;
3921
3922 -- call purge concurrent request
3923 l_con_req_id := fnd_request.submit_request(
3924 application => 'EGO',
3925 program => 'EGOPHPRG2',
3926 start_time => NULL,
3927 sub_request => FALSE,
3928 argument1 => l_batch_ids,
3929 argument2 => p_purge_days,
3930 argument3 => NULL,
3931 argument4 => NULL,
3932 argument5 => NULL,
3933 argument6 => NULL,
3934 argument7 => NULL
3935 );
3936 commit;
3937 x_con_req_id := l_con_req_id;
3938 if (l_con_req_id = 0) then
3939 raise EGO_PUB_BATCH_PURGE_CP_FAIL;
3940 end if;
3941
3942 EXCEPTION
3943 when EGO_PUB_BATCH_PARAMS_NULL then
3944 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3945 ,p_module => l_module_name
3946 ,p_message => ' params are null');
3947 l_token(1).token_name := 'PARAM';
3948 l_token(1).token_value := 'p_batch_names, p_batch_ids & p_purge_days';
3949 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3950 , p_message =>'EGO_PUB_API_PARAM_NULL'
3951 , p_token => l_token);
3952 x_return_status := FND_API.G_RET_STS_ERROR;
3953 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3954 ,p_msg_data => x_msg_data );
3955
3956 when EGO_PUB_BATCH_NAMEIDS_INVL then
3957 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3958 ,p_module => l_module_name
3959 ,p_message => ' names/ids length not match, or invalid pair ');
3960 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3961 ,p_message =>'EGO_PUB_BATCH_NAMEIDS_INVL');
3962 x_return_status := FND_API.G_RET_STS_ERROR;
3963 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3964 ,p_msg_data => x_msg_data );
3965
3966 when EGO_PUB_BATCH_PURGE_CP_FAIL then
3967 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3968 ,p_module => l_module_name
3969 ,p_message => ' cannot launch the purge cp, pls contact admin ');
3970 EGO_UTIL_PK.put_fnd_stack_msg (p_appln_short_name=>'EGO'
3971 ,p_message =>'EGO_PUB_BATCH_PURGE_CP_FAIL');
3972 x_return_status := FND_API.G_RET_STS_ERROR;
3973 EGO_UTIL_PK.count_and_get (p_msg_count => x_msg_count
3974 ,p_msg_data => x_msg_data );
3975
3976 when others then
3977 l_err_msg := l_err_msg || ' others error ' || sqlerrm ;
3978 EGO_COMMON_PVT.WRITE_DIAGNOSTIC(p_log_level => FND_LOG.LEVEL_PROCEDURE
3979 ,p_module => l_module_name
3980 ,p_message => l_err_msg);
3981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3982 x_msg_data := l_err_msg;
3983
3984 end delete_batch;
3985 -- end of public apis
3986
3987
3988 END EGO_PUB_FWK_PK;