[Home] [Help]
PACKAGE BODY: APPS.EGO_METADATA_BULKLOAD_PVT
Source
1 PACKAGE BODY ego_metadata_bulkload_pvt AS
2 /* $Header: EGOVMDBB.pls 120.0.12010000.6 2010/06/11 13:53:29 kjonnala noship $ */
3
4 /********************************************************************************
5 -- Procedure : SetGlobals
6 -- Purpose : Sets the WHO columns for the concurrent program.
7 -- IN Parameters :
8 -- None
9 -- OUT Parameters:
10 -- None
11 ********************************************************************************/
12
13 PROCEDURE SetGlobals IS
14 BEGIN
15 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
16 G_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
17 G_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
18 G_USER_NAME := FND_GLOBAL.USER_NAME;
19 G_USER_ID := FND_GLOBAL.USER_ID;
20 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
21 G_DEBUG := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
22 END;
23
24
25 /********************************************************************************
26 -- Procedure : write_debug
27 -- Purpose : Writes the debug messages into concurrent program log
28 -- IN Parameters :
29 -- p_msg - string to be written onto concurrent program log
30 -- OUT Parameters:
31 -- None
32 ********************************************************************************/
33
34 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
35 l_err_msg VARCHAR2(240);
36 BEGIN
37 -- If Profile set to TRUE --
38 IF (G_DEBUG = 1) THEN
39 FND_FILE.put_line(FND_FILE.LOG, p_msg);
40 END IF;
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 l_err_msg := SUBSTRB(SQLERRM, 1,240);
45 FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
46 END Write_Debug;
47
48
49
50 /***********************************************************************************
51 -- Procedure : delete_processed_metadata
52 -- Purpose : Delete successfully processed records (process_status=7)
53 -- from the interface tables.
54 -- IN Parameters :
55 -- p_set_process_id - set_process_id for the batch, can be null
56 -- OUT Parameters:
57 -- x_return_status - return status of the procedure
58 -- 'S' - success, 'E' - error
59 -- 'U' - unexpected error
60 *************************************************************************************/
61 PROCEDURE delete_processed_metadata(p_import_vs IN VARCHAR2,
62 p_import_ag IN VARCHAR2,
63 p_import_icc IN VARCHAR2,
64 p_set_process_id IN NUMBER,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_return_msg OUT NOCOPY VARCHAR2)
67 IS
68 l_proc_name VARCHAR2(30) := 'delete_processed_metadata';
69 BEGIN
70 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entered delete_processed_metadata() ');
71
72 x_return_status :=G_RET_STS_SUCCESS;
73
74 /* Delete Successfully processed records (process_status=7) for each of the entities */
75 IF (p_import_vs='Y') THEN
76
77 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call EGO_VS_BULKLOAD_PVT.delete_processed_value_sets()');
78 EGO_VS_BULKLOAD_PVT.delete_processed_value_sets(p_set_process_id, x_return_status,x_return_msg);
79 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned from EGO_VS_BULKLOAD_PVT.delete_processed_value_sets() with return_status: '||x_return_status);
80
81 END IF;
82
83 IF Nvl(x_return_status,G_RET_STS_SUCCESS) <> G_RET_STS_UNEXP_ERROR THEN
84 IF (p_import_ag='Y') THEN
85
86 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call EGO_AG_BULKLOAD_PVT.delete_processed_attr_groups() ');
87 EGO_AG_BULKLOAD_PVT.delete_processed_attr_groups(p_set_process_id, x_return_status,x_return_msg);
88 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned from EGO_AG_BULKLOAD_PVT.delete_processed_attr_groups() with return_status: '||x_return_status);
89
90 END IF;
91 END IF;
92
93 IF Nvl(x_return_status,G_RET_STS_SUCCESS) <> G_RET_STS_UNEXP_ERROR THEN
94 IF (p_import_icc='Y') THEN
95
96 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call EGO_ICC_BULKLOAD_PVT.delete_processed_icc() ');
97 EGO_ICC_BULKLOAD_PVT.delete_processed_icc(p_set_process_id, x_return_status,x_return_msg);
98 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned from EGO_ICC_BULKLOAD_PVT.delete_processed_icc() with return_status: '||x_return_status);
99
100 EGO_TA_BULKLOAD_PVT.delete_processed_trans_attrs(p_set_process_id, x_return_status,x_return_msg);
101 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned from EGO_TA_BULKLOAD_PVT.delete_processed_trans_attrs() with return_status: '||x_return_status);
102
103 EGO_PAGES_BULKLOAD_PVT.delete_processed_pages(p_set_process_id, x_return_status,x_return_msg);
104 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned from EGO_PAGES_BULKLOAD_PVT.delete_processed_pages() with return_status: '||x_return_status);
105
106 EGO_FUNCTIONS_BULKLOAD_PVT.delete_processed_functions(p_set_process_id, x_return_status,x_return_msg);
107 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned from EGO_FUNCTIONS_BULKLOAD_PVT.delete_processed_functions() with return_status: '||x_return_status);
108
109 END IF;
110 END IF;
111
112
113 IF Nvl(x_return_status,G_RET_STS_SUCCESS) <> G_RET_STS_UNEXP_ERROR THEN
114 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Commit deleted data ');
115 /* if all records got successfully deleted then commit*/
116 COMMIT;
117 END IF;
118
119 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exit from delete_processed_metadata()');
120 EXCEPTION
121 WHEN OTHERS THEN
122 x_return_status := G_RET_STS_UNEXP_ERROR;
123 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'->'||'Exception occurred ->'||SQLERRM;
124 END delete_processed_metadata;
125
126
127 /*************************************************************************************************
128 -- Procedure : import_metadata
129 -- Purpose : Main method called by the concurrent program EGOIMDCP executable
130 -- Co-ordinates the import of all metadata entities.
131 -- IN Parameters :
132 -- p_import_vs - indicates whether valuesets should be imported or not
133 -- p_import_ag - indicates whether attribute groups should be imported or not
134 -- p_import_icc - indicates whether Item Catalog Categories should be imported
135 -- or not
136 -- p_set_process_id - batch_id/set_processed_id for grouping the records to be
137 -- processed together in a batch.
138 -- p_del_proc_recs - indicates whether successfully imported records
139 -- (process_status=7) should be deleted or not
140 -- from the interface tables.
141 --
142 -- OUT Parameters:
143 -- errbuf - error msg to be returned back to concurrent program
144 -- incase of any failure.
145 -- retcode - return code to be passed to concurrent program
146 -- 0 - SUCCESS, 1- WARNING , 2- ERROR
147 **************************************************************************************************/
148
149 PROCEDURE import_metadata( errbuf OUT NOCOPY VARCHAR2,
150 retcode OUT NOCOPY NUMBER,
151 p_import_vs IN VARCHAR2,
152 p_import_ag IN VARCHAR2,
153 p_import_icc IN VARCHAR2,
154 p_set_process_id IN NUMBER,
155 p_del_proc_recs IN VARCHAR2
156 )
157 IS
158 l_proc_name VARCHAR2(30) := 'import_metadata';
159
160 l_token_table ERROR_HANDLER.Token_Tbl_Type;
161
162 l_error_message_name VARCHAR2(240);
163 l_error_row_identifier NUMBER;
164
165 l_return_status VARCHAR2(1);
166 l_return_msg VARCHAR2(2000);
167
168 BEGIN
169
170 retcode := G_CONC_RETCODE_SUCCESS; /* return code for concurrent program set to success by default*/
171 l_return_status := G_RET_STS_SUCCESS; /*return code of each api set to success by default */
172
173 /* Dump information on parmeters passed into the concurrent log file irrespective of whether debug is turned on or not */
174 FND_FILE.put_line (FND_FILE.log, ' ');
175 FND_FILE.put_line (FND_FILE.log, 'EGO Import Metadata');
176 FND_FILE.put_line (FND_FILE.log, '--------------------------------------------------------------------------------');
177 FND_FILE.put_line (FND_FILE.log, 'Argument 1 (Import Value Sets) = '||p_import_vs);
178 FND_FILE.put_line (FND_FILE.log, 'Argument 2 (Import Attribute Groups) = '||p_import_ag);
179 FND_FILE.put_line (FND_FILE.log, 'Argument 3 (Import Item Catalog Cateogories) = '||p_import_icc);
180 FND_FILE.put_line (FND_FILE.log, 'Argument 4 (Batch Id (Null for All) ) = '||p_set_process_id);
181 FND_FILE.put_line (FND_FILE.log, 'Argument 5 (Delete Processed Records) = '||p_del_proc_recs);
182 FND_FILE.put_line (FND_FILE.log, '--------------------------------------------------------------------------------');
183 FND_FILE.put_line (FND_FILE.log, ' ');
184
185 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entered Import Metadata (EGOIMDCP) concurrent program');
186 --======================--
187 -- WHO COLUMNS SET-UP --
188 --======================--
189 SetGlobals();
190 FND_FILE.put_line (FND_FILE.log, 'Profile INV Debug Trace = '||G_DEBUG);
191 IF (G_DEBUG <= 0) THEN
192 FND_FILE.put_line (FND_FILE.log, 'Profile INV Debug Trace is not set, so debug messages will not be printed.');
193 END IF;
194 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Setup Global variables');
195
196 --======================--
197 -- ERROR_HANDLER SET-UP --
198 --======================--
199
200 ERROR_HANDLER.Initialize();
201 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Initialized Error Handler');
202
203 /* Based on Concurrent Program's parameters, call the individual entity's import APIs. */
204
205 /* Process Value Sets*/
206 IF (p_import_vs='Y') THEN
207 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call ego_vs_bulkload_pvt.import_value_set_intf()');
208 ego_vs_bulkload_pvt.import_value_set_intf(p_set_process_id, l_return_status,l_return_msg);
209 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned back from ego_vs_bulkload_pvt.import_value_set_intf() with return status: '||l_return_status);
210 END IF;
211
212 IF Nvl(l_return_status,G_RET_STS_SUCCESS) <> G_RET_STS_UNEXP_ERROR THEN /* If VS got imported without any unexpected errors or import_vs = 'N'*/
213 /* Process Attribute Groups*/
214 IF (p_import_ag='Y') THEN
215 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call ego_ag_bulkload_pvt.import_ag_intf()');
216 ego_ag_bulkload_pvt.import_ag_intf(p_set_process_id, l_return_status,l_return_msg);
217 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned back from ego_ag_bulkload_pvt.import_ag_intf() with return status: '||l_return_status);
218 END IF;
219 END IF; /* l_return_status <> G_RET_STS_UNEXP_ERROR */
220
221 IF Nvl(l_return_status,G_RET_STS_SUCCESS) <> G_RET_STS_UNEXP_ERROR THEN
222 /* If VS,AG got imported without any unexpected errors or (import_vs = 'N' and import_ag='N')*/
223 /* Process Item Catalog Categories*/
224 IF (p_import_icc='Y') THEN
225 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call ego_icc_bulkload_pvt.import_icc_intf()');
226 ego_icc_bulkload_pvt.import_icc_intf(p_set_process_id, l_return_status,l_return_msg);
227 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Returned back from ego_icc_bulkload_pvt.import_icc_intf() with return status: '||l_return_status);
228 END IF;
229 END IF; /* l_return_status <> G_RET_STS_UNEXP_ERROR */
230
231 IF Nvl(l_return_status,G_RET_STS_SUCCESS) = G_RET_STS_UNEXP_ERROR THEN
232 /* if VS or AG or ICC import failed with unexpected errors*/
233 FND_FILE.put_line (FND_FILE.log, G_PKG_NAME||'.'||l_proc_name||'->'||'Exceptions occured during Import Metadata program');
234 FND_FILE.put_line (FND_FILE.log, l_return_msg);
235 ERRBUF := l_return_msg;
236 RETCODE := G_CONC_RETCODE_ERROR;
237 END IF;
238
239
240 -------------------------------------------------------------------
241 -- Finally, we log any errors that we've accumulated throughout --
242 -- the processing of our concurrent program. These errors will --
243 -- logged both into mtl_interface_errors table and --
244 -- concurrent program log file. --
245 -------------------------------------------------------------------
246 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Calling Log_Error Procedure');
247 ERROR_HANDLER.Log_Error(
248 p_write_err_to_inttable => 'Y'
249 ,p_write_err_to_conclog => 'Y'
250 );
251
252
253 IF Nvl(l_return_status,G_RET_STS_SUCCESS) <> G_RET_STS_UNEXP_ERROR THEN
254 /* If VS,AG,ICC got imported without any unexpected errors
255 or (import_vs = 'N' and import_ag='N' and import_icc='N')*/
256 /* Delete successfully processed records if Delete Processed Records parameter is set*/
257 IF (p_del_proc_recs = 'Y') THEN
258 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||' inside If condition for p_del_proc_recs=Y');
259
260 IF (p_import_vs='Y' OR p_import_ag='Y' OR p_import_icc='Y') THEN
261 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Call delete_processed_metadata() ');
262 delete_processed_metadata(p_import_vs, p_import_ag,p_import_icc,p_set_process_id, l_return_status,l_return_msg);
263 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'returned from delete_processed_metadata() with return status: '||l_return_status);
264
265 IF Nvl(l_return_status,G_RET_STS_SUCCESS) =G_RET_STS_UNEXP_ERROR THEN
266 FND_FILE.put_line (FND_FILE.log, G_PKG_NAME||'.'||l_proc_name||'->'||'Exceptions occured during Import Metadata program');
267 FND_FILE.put_line (FND_FILE.log, l_return_msg);
268 ERRBUF := l_return_msg;
269 RETCODE := G_CONC_RETCODE_ERROR;
270 END IF; /* end of if l_return_status =G_RET_STS_UNEXP_ERROR*/
271
272 END IF; /* end of if (p_import_vs='Y' OR p_import_ag='Y' OR p_import_icc='Y')*/
273 END IF; /*end of if p_del_proc_recs = 'Y' */
274 END IF; /* end of if l_return_status <> G_RET_STS_UNEXP_ERROR */
275
276
277 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exit Import Metadata (EGOIMDCP) concurrent program');
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 FND_FILE.put_line (FND_FILE.log, G_PKG_NAME||'.'||l_proc_name||'->'||'Exceptions occured during Import Metadata program');
282 FND_FILE.put_line (FND_FILE.log, SQLERRM);
283 ERRBUF := SQLERRM;
284 RETCODE := G_CONC_RETCODE_ERROR;
285 END import_metadata;
286
287
288 PROCEDURE Get_Lock_Info ( p_object_name IN VARCHAR2
289 ,p_pk1_value IN VARCHAR2 DEFAULT NULL
290 ,p_pk2_value IN VARCHAR2 DEFAULT NULL
291 ,p_pk3_value IN VARCHAR2 DEFAULT NULL
292 ,p_pk4_value IN VARCHAR2 DEFAULT NULL
293 ,p_pk5_value IN VARCHAR2 DEFAULT NULL
294 ,x_locking_party_id OUT NOCOPY NUMBER
295 ,x_lock_flag OUT NOCOPY VARCHAR2
296 ,x_return_msg OUT NOCOPY VARCHAR2
297 ,x_return_status OUT NOCOPY VARCHAR2
298 )
299 IS
300 l_proc_name VARCHAR2(30) := 'Get_Lock_Info';
301 l_token_table ERROR_HANDLER.Token_Tbl_Type;
302 l_appl_name VARCHAR2(3) := 'EGO';
303 l_entity_code VARCHAR2(30) := NULL ;
304 l_table_name VARCHAR2(30) := NULL ;
305
306
307 CURSOR cur_get_lock_info
308 IS
309 SELECT a.*
310 FROM EGO_OBJECT_LOCK a
311 WHERE a.object_name = p_object_name
312 AND NVL(a.pk1_value, chr(0)) = NVL(p_pk1_value, chr(0))
313 AND NVL(a.pk2_value, chr(0)) = NVL(p_pk2_value, chr(0))
314 AND NVL(a.pk3_value, chr(0)) = NVL(p_pk3_value, chr(0))
315 AND NVL(a.pk4_value, chr(0)) = NVL(p_pk4_value, chr(0))
316 AND NVL(a.pk5_value, chr(0)) = NVL(p_pk5_value, chr(0))
317 AND a.lock_id = (
318 SELECT max(a.lock_id)
319 FROM EGO_OBJECT_LOCK a
320 WHERE a.object_name = p_object_name
321 AND NVL(a.pk1_value, chr(0)) = NVL(p_pk1_value, chr(0))
322 AND NVL(a.pk2_value, chr(0)) = NVL(p_pk2_value, chr(0))
323 AND NVL(a.pk3_value, chr(0)) = NVL(p_pk3_value, chr(0))
324 AND NVL(a.pk4_value, chr(0)) = NVL(p_pk4_value, chr(0))
325 AND NVL(a.pk5_value, chr(0)) = NVL(p_pk5_value, chr(0))
326 )
327 ;
328
329
330
331
332 BEGIN
333 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Start');
334 x_return_status := G_RET_STS_SUCCESS;
335
336
337
338
339 IF p_object_name = G_VALUE_SET THEN
340
341 l_entity_code := G_ENTITY_VS_VER;
342 l_table_name := G_ENTITY_VS_HEADER_TAB;
343
344 ELSE
345
346 l_entity_code := G_ENTITY_ICC_VER;
347 l_table_name := G_ENTITY_ICC_HEADER_TAB;
348
349 END IF;
350
351
352
353
354 IF ( p_pk1_value IS NULL AND
355 p_pk2_value IS NULL AND
356 p_pk3_value IS NULL AND
357 p_pk4_value IS NULL AND
358 p_pk5_value IS NULL )
359 OR p_object_name IS NULL OR length(trim(p_object_name)) = 0
360 THEN
361
362 x_return_status := G_RET_STS_ERROR;
363 l_token_table(1).token_name := 'ENTITY';
364 l_token_table(1).token_value := 'LOCK';
365
366 ERROR_HANDLER.Add_Error_Message(
367 p_message_name => 'EGO_REQ_COLMS_MISSING'
368 ,p_application_id => l_appl_name
369 --,p_row_identifier => transaction_id
370 ,p_token_tbl => l_token_table
371 ,p_entity_code => l_entity_code
372 ,p_table_name => l_table_name
373 );
374 l_token_table.delete;
375 RETURN;
376 END IF;
377
378
379 FOR rec_cur_get_lock_info IN cur_get_lock_info
380 LOOP
381 x_locking_party_id := rec_cur_get_lock_info.locking_party_id;
382 x_lock_flag := rec_cur_get_lock_info.lock_flag;
383 --x_lock_id := rec_cur_get_lock_info.lock_id;
384 EXIT;
385 END LOOP;
386
387
388 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End');
389 EXCEPTION
390 WHEN OTHERS THEN
391 x_return_status := G_RET_STS_UNEXP_ERROR;
392 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'->'||'Exception occurred ->'||SQLERRM;
393 END Get_Lock_Info;
394
395
396
397
398 PROCEDURE Lock_Unlock_Object ( p_object_name IN VARCHAR2
399 ,p_pk1_value IN VARCHAR2 DEFAULT NULL
400 ,p_pk2_value IN VARCHAR2 DEFAULT NULL
401 ,p_pk3_value IN VARCHAR2 DEFAULT NULL
402 ,p_pk4_value IN VARCHAR2 DEFAULT NULL
403 ,p_pk5_value IN VARCHAR2 DEFAULT NULL
404 ,p_party_id IN NUMBER
405 ,p_lock_flag IN BOOLEAN
406 ,x_return_msg OUT NOCOPY VARCHAR2
407 ,x_return_status OUT NOCOPY VARCHAR2
408 )
409 IS
410 l_proc_name VARCHAR2(30) := 'Lock_Unlock_Object';
411 l_token_table ERROR_HANDLER.Token_Tbl_Type;
412 l_sysdate DATE := SYSDATE;
413 l_appl_name VARCHAR2(3) := 'EGO';
414 l_entity_code VARCHAR2(30) := NULL ;
415 l_table_name VARCHAR2(30) := NULL ;
416
417 BEGIN
418 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Start');
419 x_return_status := G_RET_STS_SUCCESS;
420
421
422 G_USER_ID := FND_GLOBAL.USER_ID;
423 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
424
425
426
427
428 IF p_object_name = G_VALUE_SET THEN
429
430 l_entity_code := G_ENTITY_VS_VER;
431 l_table_name := G_ENTITY_VS_HEADER_TAB;
432
433 ELSE
434
435 l_entity_code := G_ENTITY_ICC_VER;
436 l_table_name := G_ENTITY_ICC_HEADER_TAB;
437
438 END IF;
439
440
441
442
443
444 IF ( p_pk1_value IS NULL AND
445 p_pk2_value IS NULL AND
446 p_pk3_value IS NULL AND
447 p_pk4_value IS NULL AND
448 p_pk5_value IS NULL )
449 OR p_object_name IS NULL OR length(trim(p_object_name)) = 0
450 THEN
451
452 x_return_status := G_RET_STS_ERROR;
453 l_token_table(1).token_name := 'ENTITY';
454 l_token_table(1).token_value := 'LOCK';
455
456 ERROR_HANDLER.Add_Error_Message(
457 p_message_name => 'EGO_REQ_COLMS_MISSING'
458 ,p_application_id => l_appl_name
459 --,p_row_identifier => transaction_id
460 ,p_token_tbl => l_token_table
461 ,p_entity_code => l_entity_code
462 ,p_table_name => l_table_name
463 );
464 l_token_table.delete;
465 RETURN;
466 END IF;
467
468
469
470 IF p_lock_flag THEN
471 ---
472 --- Lock the object
473 ---
474 INSERT INTO EGO_OBJECT_LOCK
475 ( lock_id,
476 object_name,
477 pk1_value,
478 pk2_value,
479 pk3_value,
480 pk4_value,
481 pk5_value,
482 locking_party_id,
483 lock_flag,
484 created_by,
485 creation_date,
486 last_updated_by,
487 last_update_date,
488 last_update_login)
489 VALUES ( EGO_OBJECT_LOCK_S.NEXTVAL,
490 p_object_name,
491 p_pk1_value,
492 p_pk2_value,
493 p_pk3_value,
494 p_pk4_value,
495 p_pk5_value,
496 p_party_id,
497 'U', --- unlocked record is inserted
498 G_USER_ID,
499 l_sysdate,
500 G_USER_ID,
501 l_sysdate,
502 G_LOGIN_ID);
503 ELSE
504 ---
505 --- Unlock the object
506 ---
507 UPDATE EGO_OBJECT_LOCK l
508 SET l.lock_flag = 'U'
509 , l.last_updated_by = g_user_id
510 , l.last_update_date = l_sysdate
511 , l.last_update_login = g_login_id
512 WHERE l.object_name = p_object_name
513 AND NVL(l.pk1_value, chr(0)) = NVL(p_pk1_value, chr(0))
514 AND NVL(l.pk2_value, chr(0)) = NVL(p_pk2_value, chr(0))
515 AND NVL(l.pk3_value, chr(0)) = NVL(p_pk3_value, chr(0))
516 AND NVL(l.pk4_value, chr(0)) = NVL(p_pk4_value, chr(0))
517 AND NVL(l.pk5_value, chr(0)) = NVL(p_pk5_value, chr(0))
518 AND l.lock_id = ( SELECT max(a.lock_id)
519 FROM EGO_OBJECT_LOCK a
520 WHERE a.object_name = p_object_name
521 AND NVL(a.pk1_value, chr(0)) = NVL(p_pk1_value, chr(0))
522 AND NVL(a.pk2_value, chr(0)) = NVL(p_pk2_value, chr(0))
523 AND NVL(a.pk3_value, chr(0)) = NVL(p_pk3_value, chr(0))
524 AND NVL(a.pk4_value, chr(0)) = NVL(p_pk4_value, chr(0))
525 AND NVL(a.pk5_value, chr(0)) = NVL(p_pk5_value, chr(0))
526 )
527 ;
528 END IF;
529
530 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End');
531 EXCEPTION
532 WHEN OTHERS THEN
533 x_return_status := G_RET_STS_UNEXP_ERROR;
534 x_return_msg := G_PKG_NAME||'.'||l_proc_name||'->'||'Exception occurred ->'||SQLERRM;
535 END Lock_Unlock_Object;
536
537 /*************************************************************************************************
538 -- Procedure : Get_Party_Name
539 -- Purpose : Procedure which gets the party name given the party_id
540 -- IN Parameters :
541 -- p_party_id - id of the party
542
543 -- OUT Parameters:
544 -- p_party_name - name of the party
545 **************************************************************************************************/
546
547 PROCEDURE Get_Party_Name ( p_party_id IN NUMBER,
548 x_party_name OUT NOCOPY VARCHAR2 )
549
550
551 IS
552 l_proc_name VARCHAR2(30) :='Get_Party_Name';
553 l_Party_Name VARCHAR2(100) := NULL;
554 l_api_name VARCHAR2(100) := 'Get_Party_Name';
555
556 BEGIN
557
558 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||' Start of API ');
559
560
561 -- Get Party_Name
562 SELECT Party_Name
563 INTO l_Party_Name
564 FROM HZ_PARTIES
565 WHERE party_Id= p_party_id;
566
567
568 x_party_name := l_Party_Name;
569
570 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||' End of API G_Party_Name = '||l_Party_Name);
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 Write_Debug(G_PKG_NAME||'.'||l_proc_name||'->'||' In Exception of API. Error : '||SubStr(SQLERRM,1,500) );
575 x_party_name := NULL;
576 END Get_Party_Name;
577
578
579 END EGO_METADATA_BULKLOAD_PVT;