[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_AML_PUB
Source
1 PACKAGE BODY EGO_ITEM_AML_PUB AS
2 /* $Header: EGOITAMB.pls 120.7.12000000.2 2007/07/10 13:49:49 ksathupa ship $ */
3
4 -- =============================================================================
5 -- Package variables and cursors
6 -- =============================================================================
7
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EGOITAMB.pls';
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_AML_PUB';
10 G_EGO_ITEM CONSTANT VARCHAR2(30) := 'EGO_ITEM';
11
12 G_ADD_ACD_TYPE CONSTANT VARCHAR2(10) := 'ADD';
13 G_CHANGE_ACD_TYPE CONSTANT VARCHAR2(10) := 'CHANGE';
14 G_DELETE_ACD_TYPE CONSTANT VARCHAR2(10) := 'DELETE';
15 G_HISTORY_ACD_TYPE CONSTANT VARCHAR2(10) := 'HISTORY';
16
17 -- =============================================================================
18 -- Private Functions and Procedures
19 -- =============================================================================
20
21 --
22 -- write debug into log
23 --
24 PROCEDURE log_now (p_log_level IN NUMBER
25 ,p_module IN VARCHAR2
26 ,p_message IN VARCHAR2
27 ) IS
28 BEGIN
29 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
30 fnd_log.string(log_level => p_log_level
31 ,module => 'fnd.plsql.ego.EGO_ITEM_AML_PUB.'||p_module
32 ,message => p_message
33 );
34 END IF;
35 -- sri_debug (p_message);
36 EXCEPTION
37 WHEN OTHERS THEN
38 RAISE;
39 END log_now;
40
41
42 -- =============================================================================
43 -- Public Functions and Procedures
44 -- =============================================================================
45
46 FUNCTION Check_CM_Existance RETURN VARCHAR2 IS
47 ----------------------------------------------------------------------------
48 -- Start OF comments
49 -- API name : Check_Change_Management_Existance
50 -- TYPE : Public
51 -- Pre-reqs : None
52 -- FUNCTION : Check whether CM is installed and active
53 -- (the table ENG_CHANGE_MGMT_TYPES_VL is populated)
54 --
55 -- Return Parameter:
56 -- 'S' if view eng_change_mgmt_types_vl is populated
57 -- 'E' in all other cases
58 --
59 ----------------------------------------------------------------------------
60 l_product_exists VARCHAR2(1) := G_RET_STS_ERROR;
61 --l_status fnd_product_installations.status%TYPE;
62 --l_count NUMBER;
63
64 /*CURSOR c_product_check (cp_app_short_name IN VARCHAR2) IS
65 SELECT inst.status
66 FROM fnd_product_installations inst, fnd_application app
67 WHERE inst.application_id = app.application_id
68 AND app.application_short_name = cp_app_short_name
69 AND inst.status <> 'N';*/
70
71 BEGIN
72 -- Checking whether the product is installed.
73 /*OPEN c_product_check (cp_app_short_name => 'ENG');
74 FETCH c_product_check INTO l_status;
75 CLOSE c_product_check;
76 IF (l_status = 'I') THEN
77 -- package exists and DBI is installed
78 -- check if a record exists in eng_change_mgmt_types_vl
79 SELECT count(*)
80 INTO l_count
81 FROM eng_change_mgmt_types_vl
82 WHERE disable_flag = 'N';
83 IF l_count <> 0 THEN
84 l_product_exists := G_RET_STS_SUCCESS;
85 END IF;
86 END IF;
87 RETURN (l_product_exists);
88 EXCEPTION
89 WHEN OTHERS THEN
90 IF c_product_check%ISOPEN THEN
91 CLOSE c_product_check;
92 END IF;
93 RETURN (l_product_exists);*/
94
95 l_product_exists := EGO_COMMON_PVT.Is_EGO_Installed(1.0, '');
96 IF (l_product_exists = 'T') THEN
97 RETURN FND_API.G_RET_STS_SUCCESS;
98 ELSE
99 RETURN FND_API.G_RET_STS_ERROR;
100 END IF;
101 EXCEPTION
102 WHEN OTHERS THEN
103 RETURN FND_API.G_RET_STS_ERROR;
104 END Check_CM_Existance;
105
106 Procedure Implement_AML_Changes (
107 p_api_version IN NUMBER,
108 p_init_msg_list IN VARCHAR2,
109 p_commit IN VARCHAR2,
110 p_change_id IN NUMBER,
111 p_change_line_id IN NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2
115 ) IS
116 ----------------------------------------------------------------------------
117 -- Start OF comments
118 -- API name : Implement_AML_Changes
119 -- TYPE : Public
120 -- Pre-reqs : None
121 -- FUNCTION : Initialize the Item record with the values of the
122 -- item_id (p_inventory_item_id) and Org Id (p_Organization_id)
123 --
124 -- Parameters:
125 -- IN : p_change_id NUMBER
126 -- : p_change_line_id NUMBER
127 -- one of the above parameters is mandatory
128 --
129 --
130 -- OUT : x_return_status VARCHAR2
131 -- x_msg_count NUMBER
132 -- x_msg_data VARCHAR2
133 --
134 ----------------------------------------------------------------------------
135 l_api_version NUMBER := 1.0;
136 l_api_name VARCHAR2(30) := 'IMPLEMENT_AML_CHANGES';
137 l_user_id NUMBER;
138 l_login_id NUMBER;
139 event_dml_Type VARCHAR2(30) := ' ';
140
141 l_pend_data_row EGO_MFG_PART_NUM_CHGS%ROWTYPE;
142 l_prod_data_row MTL_MFG_PART_NUMBERS%ROWTYPE;
143
144 TYPE NUM_TABLE_TYPE IS TABLE OF mtl_mfg_part_numbers.manufacturer_id%TYPE;
145 TYPE CHAR150_TABLE_TYPE IS TABLE OF mtl_mfg_part_numbers.mfg_part_num%TYPE;
146
147 l_organization_id_tbl NUM_TABLE_TYPE;
148 l_manufacturer_id_tbl NUM_TABLE_TYPE;
149 l_mfg_part_num_tbl CHAR150_TABLE_TYPE;
150 l_inventory_item_id_tbl NUM_TABLE_TYPE;
151 l_change_line_id_tbl NUM_TABLE_TYPE;
152 l_acd_type_tbl CHAR150_TABLE_TYPE;
153 l_pending_row_count NUMBER;
154
155 l_sysdate DATE;
156 l_msg_data VARCHAR2(4000);
157 l_return_status VARCHAR2(1);
158
159 CURSOR c_get_pending_data (cp_mfg_id IN NUMBER
160 ,cp_item_id IN NUMBER
161 ,cp_org_id IN NUMBER
162 ,cp_mfg_part_num IN VARCHAR2
163 ,cp_change_line_id IN NUMBER
164 ,cp_acd_type IN VARCHAR2) IS
165 SELECT *
166 FROM ego_mfg_part_num_chgs
167 WHERE manufacturer_id = cp_mfg_id
168 AND inventory_item_id = cp_item_id
169 AND organization_id = cp_org_id
170 AND mfg_part_num = cp_mfg_part_num
171 AND change_line_id = cp_change_line_id
172 AND acd_type = cp_acd_type;
173 -- work around for 3446060
174 -- FOR UPDATE NOWAIT;
175 -- FOR UPDATE OF implmentation_date, last_update_date, last_updated_by, last_update_login NOWAIT;
176
177 CURSOR c_get_production_data (cp_inventory_item_id IN NUMBER
178 ,cp_organization_id IN NUMBER
179 ,cp_manufacturer_id IN NUMBER
180 ,cp_mfg_part_num IN VARCHAR2) IS
181 SELECT *
182 FROM mtl_mfg_part_numbers
183 WHERE inventory_item_id = cp_inventory_item_id
184 AND organization_id = cp_organization_id
185 AND manufacturer_id = cp_manufacturer_id
186 AND mfg_part_num = cp_mfg_part_num;
187
188 BEGIN
189 log_now (p_log_level => FND_LOG.LEVEL_PROCEDURE
190 ,p_module => l_api_name
191 ,p_message => 'p_api_version:'||p_api_version||'-'||
192 'p_init_msg_list:'||p_init_msg_list||'-'||
193 'p_commit'||p_commit||'-'||
194 'p_change_id:'||p_change_id||'-'||
195 'p_change_line_id:'||p_change_line_id
196 );
197
198 -- standard check for API validation
199 IF NOT FND_API.Compatible_API_Call (l_api_version,
200 p_api_version,
201 l_api_name,
202 G_PKG_NAME) THEN
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 END IF;
205 IF (p_change_id IS NULL AND p_change_line_id IS NULL) THEN
206 fnd_message.set_name('EGO','EGO_MAND_PARAM_MISSING');
207 fnd_message.set_token('PROGRAM', G_PKG_NAME || l_api_name);
208 fnd_msg_pub.add;
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 x_msg_count := 1;
211 fnd_msg_pub.Count_And_Get
212 (p_count => x_msg_count
213 ,p_data => x_msg_data
214 );
215 RETURN;
216 END IF;
217
218 BEGIN
219 SELECT ORGANIZATION_ID, MANUFACTURER_ID, MFG_PART_NUM,
220 INVENTORY_ITEM_ID, CHANGE_LINE_ID, ACD_TYPE
221 BULK COLLECT INTO
222 l_organization_id_tbl, l_manufacturer_id_tbl,
223 l_mfg_part_num_tbl, l_inventory_item_id_tbl,
224 l_change_line_id_tbl, l_acd_type_tbl
225 FROM ego_mfg_part_num_chgs
226 WHERE NVL(change_id,-1) = NVL(NVL(p_change_id, change_id),-1)
227 AND NVL(change_line_id, -1) =
228 NVL(NVL(p_change_line_id, change_line_id),-1)
229 AND implmentation_date IS NULL
230 AND acd_type IN
231 (G_ADD_ACD_TYPE, G_CHANGE_ACD_TYPE, G_DELETE_ACD_TYPE);
232 l_pending_row_count := SQL%ROWCOUNT;
233 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
234 ,p_module => l_api_name
235 ,p_message => ' no of rows to process '||l_pending_row_count);
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 log_now (p_log_level => FND_LOG.LEVEL_PROCEDURE
239 ,p_module => l_api_name
240 ,p_message => ' no rows to process - returning ');
241 RETURN;
242 END;
243
244 l_user_id := FND_GLOBAL.User_Id;
245 l_login_id := FND_GLOBAL.Conc_Login_Id;
246 l_sysdate := SYSDATE;
247
248 -- create save point
249 IF FND_API.To_Boolean(p_commit) THEN
250 SAVEPOINT IMPLEMENT_AML_CHANGES;
251 END IF;
252
253 -- Initialize message list
254 IF FND_API.To_Boolean(p_init_msg_list) THEN
255 FND_MSG_PUB.Initialize;
256 END IF;
257
258 FOR l_pend_index IN 1..l_pending_row_count LOOP
259 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
260 ,p_module => l_api_name
261 ,p_message => ' started changes for item id:'||l_inventory_item_id_tbl(l_pend_index)||'-'
262 ||' org id:'||l_organization_id_tbl(l_pend_index)||'-'
263 ||' mfg id:'||l_manufacturer_id_tbl(l_pend_index)||'-'
264 ||' mfg part num:'||l_mfg_part_num_tbl(l_pend_index)||'-'
265 ||' ACD type:'||l_acd_type_tbl(l_pend_index)
266 );
267 IF l_acd_type_tbl(l_pend_index) IN (G_CHANGE_ACD_TYPE, G_DELETE_ACD_TYPE) THEN
268 OPEN c_get_production_data
269 (cp_inventory_item_id => l_inventory_item_id_tbl(l_pend_index)
270 ,cp_organization_id => l_organization_id_tbl(l_pend_index)
271 ,cp_manufacturer_id => l_manufacturer_id_tbl(l_pend_index)
272 ,cp_mfg_part_num => l_mfg_part_num_tbl(l_pend_index));
273 FETCH c_get_production_data INTO l_prod_data_row;
274 IF c_get_production_data%FOUND THEN
275 -- create a history record in ego_mfg_part_num_changes
276 INSERT INTO ego_mfg_part_num_chgs
277 (manufacturer_id
278 ,mfg_part_num
279 ,inventory_item_id
280 ,last_update_date
281 ,last_updated_by
282 ,creation_date
283 ,created_by
284 ,last_update_login
285 ,organization_id
286 ,mrp_planning_code
287 ,description
288 ,first_article_status
289 ,approval_status
290 ,change_id
291 ,change_line_id
292 ,acd_type
293 ,attribute_category
294 ,attribute1
295 ,attribute2
296 ,attribute3
297 ,attribute4
298 ,attribute5
299 ,attribute6
300 ,attribute7
301 ,attribute8
302 ,attribute9
303 ,attribute10
304 ,attribute11
305 ,attribute12
306 ,attribute13
307 ,attribute14
308 ,attribute15
309 ,implmentation_date
310 ,start_date
311 ,end_date)
312 VALUES
313 (l_prod_data_row.manufacturer_id
314 ,l_prod_data_row.mfg_part_num
315 ,l_prod_data_row.inventory_item_id
316 ,l_sysdate
317 ,l_user_id
318 ,l_sysdate
319 ,l_user_id
320 ,l_login_id
321 ,l_prod_data_row.organization_id
322 ,l_prod_data_row.mrp_planning_code
323 ,l_prod_data_row.description
324 ,l_prod_data_row.first_article_status
325 ,l_prod_data_row.approval_status
326 ,p_change_id
330 ,l_prod_data_row.attribute1
327 ,p_change_line_id
328 ,G_HISTORY_ACD_TYPE
329 ,l_prod_data_row.attribute_category
331 ,l_prod_data_row.attribute2
332 ,l_prod_data_row.attribute3
333 ,l_prod_data_row.attribute4
334 ,l_prod_data_row.attribute5
335 ,l_prod_data_row.attribute6
336 ,l_prod_data_row.attribute7
337 ,l_prod_data_row.attribute8
338 ,l_prod_data_row.attribute9
339 ,l_prod_data_row.attribute10
340 ,l_prod_data_row.attribute11
341 ,l_prod_data_row.attribute12
342 ,l_prod_data_row.attribute13
343 ,l_prod_data_row.attribute14
344 ,l_prod_data_row.attribute15
345 ,NULL
346 ,l_prod_data_row.start_date
347 ,l_prod_data_row.end_date);
348 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
349 ,p_module => l_api_name
350 ,p_message => 'successfully created a history record in ego_mfg_part_num_chgs'
351 );
352 IF l_acd_type_tbl(l_pend_index) = G_CHANGE_ACD_TYPE THEN
353 -- copy the pending row into production (basically update)
354 OPEN c_get_pending_data
355 (cp_mfg_id => l_manufacturer_id_tbl(l_pend_index)
356 ,cp_item_id => l_inventory_item_id_tbl(l_pend_index)
357 ,cp_org_id => l_organization_id_tbl(l_pend_index)
358 ,cp_mfg_part_num => l_mfg_part_num_tbl(l_pend_index)
359 ,cp_change_line_id => l_change_line_id_tbl(l_pend_index)
360 ,cp_acd_type => l_acd_type_tbl(l_pend_index)
361 );
362 FETCH c_get_pending_data INTO l_pend_data_row;
363 CLOSE c_get_pending_data;
364 UPDATE mtl_mfg_part_numbers
365 SET first_article_status = l_pend_data_row.first_article_status
366 ,approval_status = l_pend_data_row.approval_status
367 ,start_date = l_pend_data_row.start_date
368 ,end_date = l_pend_data_row.end_date
369 ,attribute1 = l_pend_data_row.attribute1 --Added attribute 1 - 15 for bug 6109336
370 ,attribute2 = l_pend_data_row.attribute2
371 ,attribute3 = l_pend_data_row.attribute3
372 ,attribute4 = l_pend_data_row.attribute4
373 ,attribute5 = l_pend_data_row.attribute5
374 ,attribute6 = l_pend_data_row.attribute6
375 ,attribute7 = l_pend_data_row.attribute7
376 ,attribute8 = l_pend_data_row.attribute8
377 ,attribute9 = l_pend_data_row.attribute9
378 ,attribute10 = l_pend_data_row.attribute10
379 ,attribute11 = l_pend_data_row.attribute11
380 ,attribute12 = l_pend_data_row.attribute12
381 ,attribute13 = l_pend_data_row.attribute13
382 ,attribute14 = l_pend_data_row.attribute14
383 ,attribute15 = l_pend_data_row.attribute15
384 ,last_update_date = l_sysdate
385 ,last_updated_by = l_user_id
386 ,last_update_login = l_login_id
387 WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
388 AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
389 AND organization_id = l_organization_id_tbl(l_pend_index)
390 AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index);
391 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
392 ,p_module => l_api_name
393 ,p_message => 'successfully updated production row'
394 );
395 event_dml_Type := 'UPDATE';
396 ELSIF l_acd_type_tbl(l_pend_index) = G_DELETE_ACD_TYPE THEN
397 -- delete the record from mtl_mfg_part_numbers
398 DELETE mtl_mfg_part_numbers
399 WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
400 AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
401 AND organization_id = l_organization_id_tbl(l_pend_index)
402 AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index);
403 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
404 ,p_module => l_api_name
405 ,p_message => 'successfully deleted production row'
406 );
407 event_dml_Type := 'DELETE';
408 END IF;
409
410 UPDATE ego_mfg_part_num_chgs
411 SET implmentation_date = l_sysdate
412 ,last_update_date = l_sysdate
413 ,last_updated_by = l_user_id
414 ,last_update_login = l_login_id
415 WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
416 AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
417 AND organization_id = l_organization_id_tbl(l_pend_index)
418 AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index)
419 AND change_line_id = l_change_line_id_tbl(l_pend_index)
420 AND acd_type = l_acd_type_tbl(l_pend_index);
421 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
422 ,p_module => l_api_name
423 ,p_message => 'successfully updated pending row as implemented'
427
424 );
425 END IF;
426 CLOSE c_get_production_data;
428 ELSIF l_acd_type_tbl(l_pend_index) = G_ADD_ACD_TYPE THEN
429 OPEN c_get_pending_data
430 (cp_mfg_id => l_manufacturer_id_tbl(l_pend_index)
431 ,cp_item_id => l_inventory_item_id_tbl(l_pend_index)
432 ,cp_org_id => l_organization_id_tbl(l_pend_index)
433 ,cp_mfg_part_num => l_mfg_part_num_tbl(l_pend_index)
434 ,cp_change_line_id => l_change_line_id_tbl(l_pend_index)
435 ,cp_acd_type => l_acd_type_tbl(l_pend_index)
436 );
437 FETCH c_get_pending_data INTO l_pend_data_row;
438 CLOSE c_get_pending_data;
439 -- insert a new record into production table
440 INSERT INTO mtl_mfg_part_numbers
441 (manufacturer_id
442 ,mfg_part_num
443 ,inventory_item_id
444 ,last_update_date
445 ,last_updated_by
446 ,creation_date
447 ,created_by
448 ,last_update_login
449 ,organization_id
450 ,mrp_planning_code
451 ,description
452 ,attribute_category
453 ,attribute1
454 ,attribute2
455 ,attribute3
456 ,attribute4
457 ,attribute5
458 ,attribute6
459 ,attribute7
460 ,attribute8
461 ,attribute9
462 ,attribute10
463 ,attribute11
464 ,attribute12
465 ,attribute13
466 ,attribute14
467 ,attribute15
468 ,first_article_status
469 ,approval_status
470 ,start_date
471 ,end_date)
472 VALUES
473 (l_pend_data_row.manufacturer_id
474 ,l_pend_data_row.mfg_part_num
475 ,l_pend_data_row.inventory_item_id
476 ,l_sysdate
477 ,l_user_id
478 ,l_sysdate
479 ,l_user_id
480 ,l_login_id
481 ,l_pend_data_row.organization_id
482 ,l_pend_data_row.mrp_planning_code
483 ,l_pend_data_row.description
484 ,l_pend_data_row.attribute_category
485 ,l_pend_data_row.attribute1
486 ,l_pend_data_row.attribute2
487 ,l_pend_data_row.attribute3
488 ,l_pend_data_row.attribute4
489 ,l_pend_data_row.attribute5
490 ,l_pend_data_row.attribute6
491 ,l_pend_data_row.attribute7
492 ,l_pend_data_row.attribute8
493 ,l_pend_data_row.attribute9
494 ,l_pend_data_row.attribute10
495 ,l_pend_data_row.attribute11
496 ,l_pend_data_row.attribute12
497 ,l_pend_data_row.attribute13
498 ,l_pend_data_row.attribute14
499 ,l_pend_data_row.attribute15
500 ,l_pend_data_row.first_article_status
501 ,l_pend_data_row.approval_status
502 ,l_pend_data_row.start_date
503 ,l_pend_data_row.end_date);
504 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
505 ,p_module => l_api_name
506 ,p_message => 'successfully created a record in production table'
507 );
508
509 event_dml_Type := 'CREATE';
510 -- update the pending row as implemented
511 UPDATE ego_mfg_part_num_chgs
512 SET implmentation_date = l_sysdate
513 ,last_update_date = l_sysdate
514 ,last_updated_by = l_user_id
515 ,last_update_login = l_login_id
516 WHERE manufacturer_id = l_manufacturer_id_tbl(l_pend_index)
517 AND inventory_item_id = l_inventory_item_id_tbl(l_pend_index)
518 AND organization_id = l_organization_id_tbl(l_pend_index)
519 AND mfg_part_num = l_mfg_part_num_tbl(l_pend_index)
520 AND change_line_id = l_change_line_id_tbl(l_pend_index)
521 AND acd_type = l_acd_type_tbl(l_pend_index);
522 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
523 ,p_module => l_api_name
524 ,p_message => 'successfully updated pending row as implemented'
525 );
526 END IF; -- acd_type
527
528 --Start 4105841 : Business Event Enhancement
529 IF event_dml_type IN( 'CREATE', 'DELETE', 'UPDATE') THEN
530 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
531 ,p_module => l_api_name
532 ,p_message => ' calling Business Event '
533 );
534 EGO_WF_WRAPPER_PVT.Raise_AML_Event
535 (p_event_name => EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT
536 ,p_dml_type => event_dml_type
537 ,p_Inventory_Item_Id => l_inventory_item_id_tbl(l_pend_index)
538 ,p_Organization_Id => l_organization_id_tbl(l_pend_index)
539 ,p_Manufacturer_Id => l_manufacturer_id_tbl(l_pend_index)
540 ,p_Mfg_Part_Num => l_mfg_part_num_tbl(l_pend_index)
541 ,x_msg_data => l_msg_data
542 ,x_return_status => l_return_status
543 );
544 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
545 ,p_module => l_api_name
549 END IF;
546 ,p_message => ' calling Business Event done'
547 );
548 event_dml_type := ' ';
550 --End 4105841 : Business Event Enhancement
551 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
552 ,p_module => l_api_name
553 ,p_message => ' completed changes for item id:'||l_inventory_item_id_tbl(l_pend_index)||'-'
554 ||' org id:'||l_organization_id_tbl(l_pend_index)||'-'
555 ||' mfg id:'||l_manufacturer_id_tbl(l_pend_index)||'-'
556 ||' mfg part num:'||l_mfg_part_num_tbl(l_pend_index)||'-'
557 ||' ACD type:'||l_acd_type_tbl(l_pend_index)
558 );
559 END LOOP;
560
561 IF FND_API.To_Boolean(p_commit) THEN
562 COMMIT WORK;
563 --calling the Sync Index only if the work is committed.
564 --if needed else where before commit or if the commit is
565 --called else where before this commit pls call the same method.
566 log_now (p_log_level => FND_LOG.LEVEL_PROCEDURE
567 ,p_module => l_api_name
568 ,p_message => ' calling EGO_ITEM_TEXT_UTIL.Sync_Index '
569 );
570 EGO_ITEM_TEXT_UTIL.Sync_Index();
571 log_now (p_log_level => FND_LOG.LEVEL_PROCEDURE
572 ,p_module => l_api_name
573 ,p_message => ' returning from EGO_ITEM_TEXT_UTIL.Sync_Index '
574 );
575 END IF;
576 x_return_status := G_RET_STS_SUCCESS;
577 log_now (p_log_level => FND_LOG.LEVEL_PROCEDURE
578 ,p_module => l_api_name
579 ,p_message => ' returning with status '||x_return_status
580 );
581
582 EXCEPTION
583 WHEN FND_API.G_EXC_ERROR THEN
584 IF FND_API.To_Boolean(p_commit) THEN
585 ROLLBACK TO IMPLEMENT_AML_CHANGES;
586 END IF;
587 x_return_status := G_RET_STS_ERROR;
588 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
589 ,p_count => x_msg_count
590 ,p_data => x_msg_data);
591 log_now (p_log_level => FND_LOG.LEVEL_EXCEPTION
592 ,p_module => l_api_name
593 ,p_message => 'Expected Error as Exception '||x_msg_count ||'-'|| x_msg_data
594 );
595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
596 IF FND_API.To_Boolean(p_commit) THEN
597 ROLLBACK TO IMPLEMENT_AML_CHANGES;
598 END IF;
599 x_RETURN_STATUS := G_RET_STS_UNEXP_ERROR;
600 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
601 ,p_count => x_msg_count
602 ,p_data => x_msg_data);
603 log_now (p_log_level => FND_LOG.LEVEL_EXCEPTION
604 ,p_module => l_api_name
605 ,p_message => 'Unexpected Error as Exception '||x_msg_count ||'-'|| x_msg_data
606 );
607 WHEN OTHERS THEN
608 IF FND_API.To_Boolean(p_commit) THEN
609 ROLLBACK TO IMPLEMENT_AML_CHANGES;
610 END IF;
611 IF c_get_pending_data%ISOPEN THEN
612 CLOSE c_get_pending_data;
613 END IF;
614 IF c_get_production_data%ISOPEN THEN
615 CLOSE c_get_production_data;
616 END IF;
617 log_now (p_log_level => FND_LOG.LEVEL_EXCEPTION
618 ,p_module => l_api_name
619 ,p_message => 'Exception '||SQLERRM
620 );
621 x_return_status := G_RET_STS_UNEXP_ERROR;
622 -- for PL/SQL errors
623 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
624 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
625 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
626 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
627 FND_MSG_PUB.Add;
628 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
629 ,p_count => x_msg_count
630 ,p_data => x_msg_data);
631 END Implement_AML_Changes;
632
633 Procedure Delete_AML_Pending_Changes
634 (p_api_version IN NUMBER
635 ,p_init_msg_list IN VARCHAR2
636 ,p_commit IN VARCHAR2
637 ,p_inventory_item_id IN NUMBER
638 ,p_organization_id IN NUMBER
639 ,p_manufacturer_id IN NUMBER
640 ,p_mfg_part_num IN VARCHAR2
641 ,p_change_id IN NUMBER
642 ,p_change_line_id IN NUMBER
643 ,p_acd_type IN VARCHAR2
644 ,x_return_status OUT NOCOPY VARCHAR2
645 ,x_msg_count OUT NOCOPY VARCHAR2
646 ,x_msg_data OUT NOCOPY VARCHAR2
647 ) IS
648 ----------------------------------------------------------------------------
649 -- Start OF comments
650 -- API name : Delete_AML_Pending_Changes
651 -- TYPE : Public
652 -- Pre-reqs : None
653 -- FUNCTION : Delete the pending changes from EGO_MFG_PART_NUM_CHGS
654 --
655 -- Parameters:
656 -- IN : p_inventory_item_id NUMBER
657 -- : p_organization_id NUMBER
658 -- : p_manufacturer_id NUMBER
659 -- : p_mfg_part_num VARCHAR2
660 -- : p_change_id NUMBER
661 -- : p_change_line_id NUMBER
665 -- x_msg_count NUMBER
662 -- : p_acd_type VARCHAR2
663 --
664 -- OUT : x_return_status VARCHAR2
666 -- x_msg_data VARCHAR2
667 --
668 ----------------------------------------------------------------------------
669 l_api_version NUMBER := 1.0;
670 l_api_name VARCHAR2(50) := 'DELETE_AML_PENDING_CHANGES';
671
672 BEGIN
673
674 -- standard check for API validation
675 IF NOT FND_API.Compatible_API_Call (l_api_version,
676 p_api_version,
677 l_api_name,
678 G_PKG_NAME) THEN
679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
680 END IF;
681
682 IF p_change_id IS NULL
683 AND
684 p_change_line_id IS NULL THEN
685 -- what are you planning to delete?
686 RETURN;
687 END IF;
688
689 -- Initialize message list
690 IF FND_API.To_Boolean(p_init_msg_list) THEN
691 FND_MSG_PUB.Initialize;
692 END IF;
693
694 IF FND_API.To_Boolean(p_commit) THEN
695 SAVEPOINT DELETE_AML_PENDING_CHANGES;
696 END IF;
697
698 IF p_inventory_item_id IS NULL
699 AND
700 p_organization_id IS NULL THEN
701 --
702 -- bug 3648353
703 -- delete all unimplemented changes in the context of change order
704 --
705 DELETE EGO_MFG_PART_NUM_CHGS
706 WHERE change_id = NVL(p_change_id, change_id)
707 AND change_line_id = NVL(p_change_line_id, change_line_id)
708 AND implmentation_date IS NULL;
709 ELSE
710 --
711 -- deleting an individual line (all parameters are passed here)
712 --
713 DELETE EGO_MFG_PART_NUM_CHGS
714 WHERE inventory_item_id = p_inventory_item_id
715 AND organization_id = p_organization_id
716 AND manufacturer_id = p_manufacturer_id
717 AND mfg_part_num = p_mfg_part_num
718 AND change_id = p_change_id
719 AND change_line_id = p_change_line_id
720 -- fix for 3439187
721 -- AND acd_type = p_acd_type
722 AND implmentation_date IS NULL;
723 END IF;
724
725 IF FND_API.To_Boolean(p_commit) THEN
726 COMMIT WORK;
727 END IF;
728 x_return_status := G_RET_STS_SUCCESS;
729
730 EXCEPTION
731 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
732 x_RETURN_STATUS := G_RET_STS_UNEXP_ERROR;
733 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
734 ,p_count => x_msg_count
735 ,p_data => x_msg_data);
736 WHEN OTHERS THEN
737 log_now (p_log_level => FND_LOG.LEVEL_EXCEPTION
738 ,p_module => l_api_name
739 ,p_message => 'Exception '||SQLERRM
740 );
741 IF FND_API.To_Boolean(p_commit) THEN
742 ROLLBACK TO DELETE_AML_PENDING_CHANGES;
743 END IF;
744 x_return_status := G_RET_STS_UNEXP_ERROR;
745 -- for PL/SQL errors
746 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
747 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
748 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
749 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
750 FND_MSG_PUB.Add;
751 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
752 ,p_count => x_msg_count
753 ,p_data => x_msg_data);
754
755 END Delete_AML_Pending_Changes;
756
757
758 Procedure Check_AML_Policy_Allowed
759 (p_api_version IN NUMBER
760 ,p_inventory_item_id IN NUMBER
761 ,p_organization_id IN NUMBER
762 ,p_catalog_category_id IN NUMBER
763 ,p_lifecycle_id IN NUMBER
764 ,p_lifecycle_phase_id IN NUMBER
765 ,p_allowable_policy IN VARCHAR2
766 ,x_return_status OUT NOCOPY VARCHAR2
767 ,x_policy_name OUT NOCOPY VARCHAR2
768 ,x_item_number OUT NOCOPY VARCHAR2
769 ,x_org_name OUT NOCOPY VARCHAR2
770 ) IS
771 ----------------------------------------------------------------------------
772 -- Start OF comments
773 -- API name : Check_AML_Policy_Allowed
774 -- TYPE : Public
775 -- Pre-reqs : None
776 -- Function : To check whether the AML Changes are allowed
777 -- on the given item in the reqd organization
778 -- returns the status in x_return_status
779 --
780 -- Parameters:
781 -- IN : p_api_version NUMBER
782 -- : p_inventory_item_id NUMBER
783 -- : p_organization_id NUMBER
784 --
785 -- Return Parameter:
786 -- x_return_status
787 -- : 'Y' if Policy is allowed
788 -- : 'N' if all other cases
789 -- x_policy_name, x_item_number, x_org_name
790 -- : contains the strings for message
791 --
792 ----------------------------------------------------------------------------
793
794 l_api_name CONSTANT VARCHAR2(50) := 'CHECK_AML_POLICY_ALLOWED';
795 l_policy_object_name CONSTANT VARCHAR2(30) := 'CATALOG_LIFECYCLE_PHASE';
796 l_policy_code CONSTANT VARCHAR2(30) := 'CHANGE_POLICY';
800
797 l_attr_object_name CONSTANT VARCHAR2(30) := 'EGO_CATALOG_GROUP';
798 l_attr_code CONSTANT VARCHAR2(30) := 'AML_RULE';
799 l_acceptable_policy CONSTANT VARCHAR2(30) := 'ALLOWED';
801 l_api_version NUMBER := 1.0;
802 l_lc_catalog_cat_id NUMBER;
803 l_catalog_category_id NUMBER;
804 l_lifecycle_id NUMBER;
805 l_current_phase_id NUMBER;
806
807 l_dynamic_sql VARCHAR2(32767);
808 l_return_status VARCHAR2(1);
809 l_temp_status VARCHAR2(1);
810 l_policy_value VARCHAR2(100);
811 l_approval_status VARCHAR2(1);
812
813 CURSOR c_product_check (cp_app_short_name IN VARCHAR2) IS
814 SELECT inst.status
815 FROM fnd_product_installations inst, fnd_application app
816 WHERE inst.application_id = app.application_id
817 AND app.application_short_name = cp_app_short_name
818 AND inst.status <> 'N';
819
820 CURSOR c_get_lc_catalog_cat_id (cp_catalog_category_id IN NUMBER
821 ,cp_lifecycle_id IN NUMBER) IS
822 --
823 -- this code does not return the first catalog category id in the hierarchy
824 --
825 -- SELECT olc.object_classification_code
826 -- FROM ego_obj_type_lifecycles olc, fnd_objects o
827 -- WHERE o.obj_name = G_EGO_ITEM
828 -- AND olc.object_id = o.object_id
829 -- AND olc.lifecycle_id = cp_lifecycle_id
830 -- AND olc.object_classification_code IN
831 -- (SELECT TO_CHAR(ic.catalog_group_id)
832 -- FROM ego_catalog_groups_v ic
833 -- CONNECT BY PRIOR parent_catalog_group_id = catalog_group_id
834 -- START WITH catalog_group_id = cp_catalog_category_id
835 -- );
836 --
837 -- fix for bug 3681654
838 -- using mtl_item_catalog_groups_b instead of ego_catalog_groups_v
839 SELECT ic.item_catalog_group_id
840 FROM mtl_item_catalog_groups_b ic
841 WHERE EXISTS
842 (
843 SELECT olc.object_classification_code CatalogId
844 FROM ego_obj_type_lifecycles olc, fnd_objects o
845 WHERE o.obj_name = G_EGO_ITEM
846 AND olc.object_id = o.object_id
847 AND olc.lifecycle_id = cp_lifecycle_id
848 AND olc.object_classification_code = to_char(ic.item_catalog_group_id)
849 )
850 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
851 START WITH item_catalog_group_id = cp_catalog_category_id;
852
853 BEGIN
854 x_return_status := G_EGO_SHORT_NO;
855 -- standard check for API validation
856 IF NOT FND_API.Compatible_API_Call (l_api_version,
857 p_api_version,
858 l_api_name,
859 G_PKG_NAME) THEN
860 RETURN;
861 END IF;
862
863 IF p_inventory_item_id IS NULL THEN
864 -- expecting catalog category_id, lifecycle_id and lifecycle_phase_id
865 IF (p_catalog_category_id IS NULL
866 OR
867 p_lifecycle_id IS NULL
868 OR
869 p_lifecycle_phase_id IS NULL
870 ) THEN
871 x_return_status := G_EGO_SHORT_YES;
872 RETURN;
873 END IF;
874 ELSE
875 IF p_organization_id IS NULL THEN
876 RETURN;
877 END IF;
878 END IF;
879
880 -- Checking whether EGO product is installed.
881 OPEN c_product_check (cp_app_short_name => 'EGO');
882 FETCH c_product_check INTO l_temp_status;
883 CLOSE c_product_check;
884 IF (l_temp_status <> 'I') THEN
885 -- EGO does not exist
886 x_return_status := G_EGO_SHORT_YES;
887 RETURN;
888 END IF;
889
890 -- Checking whether ENG product is installed.
891 IF (Check_CM_Existance() <> G_RET_STS_SUCCESS) THEN
892 -- ENG does not exist
893 x_return_status := G_EGO_SHORT_YES;
894 RETURN;
895 END IF;
896
897 IF p_inventory_item_id IS NULL THEN
898 l_catalog_category_id := p_catalog_category_id;
899 l_lifecycle_id := p_lifecycle_id;
900 l_current_phase_id := p_lifecycle_phase_id;
901 ELSE
902 -- check for policy control
903 l_dynamic_sql :=
904 ' SELECT item_catalog_group_id, lifecycle_id, current_phase_id,approval_status' ||
905 ' FROM mtl_system_items_b ' ||
906 ' WHERE inventory_item_id = :1' ||
907 ' AND organization_id = :2';
908 EXECUTE IMMEDIATE l_dynamic_sql
909 INTO l_catalog_category_id, l_lifecycle_id, l_current_phase_id,l_approval_status USING p_inventory_item_id,p_organization_id;
910 END IF;
911 -- check if the values are present.
912 IF (l_catalog_category_id IS NULL
913 OR l_lifecycle_id IS NULL
914 OR l_current_phase_id IS NULL
915 OR (l_approval_status IS NOT NULL
916 AND l_approval_status <> 'A' )
917 ) THEN
918 x_return_status := G_EGO_SHORT_YES;
919 RETURN;
920 END IF;
921
922 -- get the catalog_group id from which the life cycle is associated
923 OPEN c_get_lc_catalog_cat_id
924 (cp_catalog_category_id => l_catalog_category_id
925 ,cp_lifecycle_id => l_lifecycle_id
926 );
930 l_dynamic_sql :=
927 FETCH c_get_lc_catalog_cat_id INTO l_lc_catalog_cat_id;
928 CLOSE c_get_lc_catalog_cat_id;
929
931 ' BEGIN '||
932 ' ENG_CHANGE_POLICY_PKG.GetChangePolicy '||
933 ' ( '||
934 ' p_policy_object_name => :l_policy_object_name '||
935 ' , p_policy_code => :l_policy_code '||
936 ' , p_policy_pk1_value => TO_CHAR(:l_lc_catalog_cat_id) '||
937 ' , p_policy_pk2_value => TO_CHAR(:l_lifecycle_id) '||
938 ' , p_policy_pk3_value => TO_CHAR(:l_current_phase_id) '||
939 ' , p_policy_pk4_value => NULL '||
940 ' , p_policy_pk5_value => NULL '||
941 ' , p_attribute_object_name => :l_attr_object_name '||
942 ' , p_attribute_code => :l_attr_code '||
943 ' , p_attribute_value => 1 '||
944 ' , x_policy_value => :l_policy_value '||
945 ' ); '||
946 ' END;';
947 EXECUTE IMMEDIATE l_dynamic_sql
948 USING IN l_policy_object_name,
949 IN l_policy_code,
950 IN l_lc_catalog_cat_id,
951 IN l_lifecycle_id,
952 IN l_current_phase_id,
953 IN l_attr_object_name,
954 IN l_attr_code,
955 OUT l_policy_value;
956
957 IF (NVL(l_policy_value, l_acceptable_policy)
958 IN (l_acceptable_policy
959 ,p_allowable_policy
960 )
961 ) THEN
962 x_return_status := G_EGO_SHORT_YES;
963 ELSE
964 -- the policy does not allow, put the message
965 BEGIN
966 -- get policy name
967 SELECT name
968 INTO x_policy_name
969 FROM pa_ego_phases_v
970 WHERE proj_element_id = l_current_phase_id;
971 IF p_inventory_item_id IS NOT NULL THEN
972 -- get concatenated segments
973 SELECT concatenated_segments
974 INTO x_item_number
975 FROM mtl_system_items_kfv
976 WHERE inventory_item_id = p_inventory_item_id
977 AND organization_id = p_organization_id;
978 END IF;
979 IF p_organization_id IS NOT NULL THEN
980 -- get organiation name
981 SELECT organization_name
982 INTO x_org_name
983 FROM org_organization_definitions
984 WHERE organization_id = p_organization_id;
985 END IF;
986 EXCEPTION
987 WHEN OTHERS THEN
988 x_policy_name := NULL;
989 x_item_number := NULL;
990 x_org_name := NULL;
991 END;
992 END IF;
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 IF c_product_check%ISOPEN THEN
997 CLOSE c_product_check;
998 END IF;
999 IF c_get_lc_catalog_cat_id%ISOPEN THEN
1000 CLOSE c_get_lc_catalog_cat_id;
1001 END IF;
1002 x_return_status := G_EGO_SHORT_NO;
1003 END Check_AML_Policy_Allowed;
1004
1005
1006 Function Check_No_AML_Priv
1007 (p_api_version IN NUMBER
1008 ,p_inventory_item_id IN NUMBER
1009 ,p_organization_id IN NUMBER
1010 ,p_privilege_name IN VARCHAR2
1011 ,p_party_id IN NUMBER DEFAULT NULL
1012 ,p_user_id IN NUMBER DEFAULT NULL
1013 ) RETURN VARCHAR2 IS
1014 ----------------------------------------------------------------------------
1015 -- Start OF comments
1016 -- API name : Check_No_AML_Priv
1017 -- TYPE : Public
1018 -- Pre-reqs : None
1019 -- FUNCTION : To check whether the user has the specified privilege
1020 -- on the given item in the reqd organization
1021 -- Returns 'Y' if the item can be edited
1022 -- Returns 'N' in all other cases
1023 --
1024 -- Parameters:
1025 -- IN : p_api_version NUMBER
1026 -- : p_inventory_item_id NUMBER
1027 -- : p_organization_id NUMBER
1028 -- : p_privilege_name VARCHAR2
1029 -- : p_party_id NUMBER
1030 -- : p_user_id NUMBER
1031 --
1032 -- Return Parameter:
1033 -- : 'Y' if the user has required privilege
1034 -- : 'N' if the user does not have required privilege
1035 --
1036 ----------------------------------------------------------------------------
1037
1038 l_api_version NUMBER := 1.0;
1039 l_api_name VARCHAR2(50) := 'CHECK_NO_AML_PRIV';
1040 l_dynamic_sql VARCHAR2(32767);
1041 l_return_status VARCHAR2(1);
1042 l_temp_status VARCHAR2(1);
1043 l_policy_value VARCHAR2(100);
1044
1045 l_party_key_prefix CONSTANT VARCHAR2(30) := 'HZ_PARTY:';
1046
1047 l_aml_view_priv CONSTANT VARCHAR2(30) := 'EGO_VIEW_ITEM_AML';
1048 l_aml_edit_priv CONSTANT VARCHAR2(30) := 'EGO_EDIT_ITEM_AML';
1049 l_relitem_view_priv CONSTANT VARCHAR2(30) := 'EGO_VIEW_RELATED_ITEMS';
1053 l_item_view_priv CONSTANT VARCHAR2(30) := 'EGO_VIEW_ITEM'; -- Added for 3577973
1050 l_relitem_edit_priv CONSTANT VARCHAR2(30) := 'EGO_EDIT_RELATED_ITEMS';
1051 l_custitem_xref_view_priv CONSTANT VARCHAR2(30) := 'EGO_VIEW_CUST_ITEM_XREFS'; -- Added for 3577973
1052 l_custitem_xref_edit_priv CONSTANT VARCHAR2(30) := 'EGO_EDIT_CUST_ITEM_XREFS'; -- Added for 3577973
1054 l_item_edit_priv CONSTANT VARCHAR2(30) := 'EGO_EDIT_ITEM'; -- Added for 3577973
1055
1056 l_item_xref_view_priv CONSTANT VARCHAR2(30) := 'EGO_VIEW_ITEM_XREFS'; -- Added for R12
1057 l_item_xref_edit_priv CONSTANT VARCHAR2(30) := 'EGO_EDIT_ITEM_XREFS'; -- Added for R12
1058 l_ss_item_xref_view_priv CONSTANT VARCHAR2(30) := 'EGO_VIEW_SS_ITEM_XREFS'; -- Added for R12
1059 l_ss_item_xref_edit_priv CONSTANT VARCHAR2(30) := 'EGO_EDIT_SS_ITEM_XREFS'; -- Added for R12
1060
1061 l_party_id NUMBER;
1062 l_user_id NUMBER;
1063 l_party_key VARCHAR2(50);
1064 l_dummy_msg VARCHAR2(2000);
1065 l_null VARCHAR2(1) := NULL;
1066
1067 CURSOR c_product_check (cp_app_short_name IN VARCHAR2) IS
1068 SELECT inst.status
1069 FROM fnd_product_installations inst, fnd_application app
1070 WHERE inst.application_id = app.application_id
1071 AND app.application_short_name = cp_app_short_name
1072 AND inst.status <> 'N';
1073
1074 BEGIN
1075 l_return_status := G_EGO_SHORT_YES;
1076 -- standard check for API validation
1077 IF NOT FND_API.Compatible_API_Call (l_api_version,
1078 p_api_version,
1079 l_api_name,
1080 G_PKG_NAME) THEN
1081 RETURN l_return_status;
1082 END IF;
1083 log_now (p_log_level => FND_LOG.LEVEL_PROCEDURE
1084 ,p_module => l_api_name
1085 ,p_message => 'p_inventory_item_id '||to_char(p_inventory_item_id)
1086 ||' - p_org_id '||to_char(p_organization_id)
1087 ||' - p_priv_name '|| p_privilege_name
1088 ||' - p_party_id '||p_party_id
1089 ||' - p_user_id '||p_user_id
1090 );
1091
1092 IF (p_inventory_item_id IS NULL OR
1093 p_organization_id IS NULL OR
1094 p_privilege_name IS NULL OR
1095 (
1096 p_privilege_name IS NOT NULL AND
1097 p_privilege_name NOT IN
1098 (l_aml_view_priv
1099 ,l_aml_edit_priv
1100 ,l_relitem_view_priv
1101 ,l_relitem_edit_priv
1102 ,l_custitem_xref_view_priv
1103 ,l_custitem_xref_edit_priv
1104 ,l_item_view_priv
1105 ,l_item_edit_priv
1106 ,l_item_xref_view_priv
1107 ,l_item_xref_edit_priv
1108 ,l_ss_item_xref_view_priv
1109 ,l_ss_item_xref_edit_priv
1110 )
1111 )
1112 ) THEN
1113 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1114 ,p_module => l_api_name
1115 ,p_message => ' returning status :'||l_return_status||': for invalid params '
1116 );
1117 RETURN l_return_status;
1118 END IF;
1119
1120 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1121 ,p_module => l_api_name
1122 ,p_message => ' params are valid '
1123 );
1124 -- Checking whether EGO product is installed.
1125 OPEN c_product_check (cp_app_short_name => 'EGO');
1126 FETCH c_product_check INTO l_temp_status;
1127 CLOSE c_product_check;
1128 IF (l_temp_status <> 'I') THEN
1129 -- EGO does not exist
1130 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1131 ,p_module => l_api_name
1132 ,p_message => ' returning status :'||G_EGO_SHORT_NO||': for product not existing '
1133 );
1134 RETURN G_EGO_SHORT_NO;
1135 END IF;
1136
1137 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1138 ,p_module => l_api_name
1139 ,p_message => ' EGO is installed '
1140 );
1141 l_party_id := p_party_id;
1142 IF l_party_id IS NULL THEN
1143 l_user_id := NVL(p_user_id, FND_GLOBAL.User_Id);
1144 IF l_user_id = -1 THEN
1145 -- bug 3600938 if user_id is returned as -1, query from user_name
1146 l_dynamic_sql := 'SELECT PARTY_ID FROM EGO_USER_V where user_name = :1';
1147 EXECUTE IMMEDIATE l_dynamic_sql INTO l_party_id USING FND_GLOBAL.USER_NAME;
1148 ELSE
1149 l_dynamic_sql := 'SELECT PARTY_ID FROM EGO_USER_V where user_id = :1';
1150 EXECUTE IMMEDIATE l_dynamic_sql INTO l_party_id USING IN l_user_id;
1151 END IF;
1152 -- EXECUTE IMMEDIATE l_dynamic_sql INTO l_party_id;
1153 END IF;
1154 l_party_key := l_party_key_prefix||l_party_id;
1155 l_dynamic_sql := 'SELECT EGO_DATA_SECURITY.CHECK_FUNCTION ('
1156 ||':1, '--l_api_version
1157 ||':2, '--p_privilege_name
1158 ||':3, '--G_EGO_ITEM
1159 ||':4, '--p_inventory_item_id
1160 ||':5, '--p_organization_id
1161 ||':6, '-- pk3 value NULL
1162 ||':7, '-- pk4 value NULL
1163 ||':8, '-- pk5 value NULL
1164 ||':9 )'--l_party_key
1165 ||' FROM DUAL';
1166 EXECUTE IMMEDIATE l_dynamic_sql INTO l_temp_status
1170 IN p_inventory_item_id,
1167 USING IN l_api_version,
1168 IN p_privilege_name,
1169 IN G_EGO_ITEM,
1171 IN p_organization_id,
1172 IN l_null,
1173 IN l_null,
1174 IN l_null,
1175 IN l_party_key;
1176 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1177 ,p_module => l_api_name
1178 ,p_message => ' return value from data security check :'||l_temp_status
1179 );
1180 IF l_temp_status = 'T' THEN
1181 -- fix for 3439190
1182 IF p_privilege_name IN
1183 (l_aml_view_priv
1184 ,l_relitem_view_priv
1185 ,l_relitem_edit_priv
1186 ,l_custitem_xref_view_priv
1187 ,l_custitem_xref_edit_priv
1188 ,l_item_view_priv
1189 ,l_item_edit_priv
1190 ,l_item_xref_view_priv
1191 ,l_item_xref_edit_priv
1192 ,l_ss_item_xref_view_priv
1193 ,l_ss_item_xref_edit_priv
1194 ) THEN
1195 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1196 ,p_module => l_api_name
1197 ,p_message => ' returrning status without policy check :'||G_EGO_SHORT_NO
1198 );
1199 RETURN G_EGO_SHORT_NO;
1200 END IF;
1201 -- p_privilege_name = l_aml_edit_priv
1202 Check_AML_Policy_Allowed (p_api_version => 1.0
1203 ,p_inventory_item_id => p_inventory_item_id
1204 ,p_organization_id => p_organization_id
1205 ,p_catalog_category_id => NULL
1206 ,p_lifecycle_id => NULL
1207 ,p_lifecycle_phase_id => NULL
1208 ,p_allowable_policy => 'ALLOWED'
1209 ,x_return_status => l_temp_status
1210 ,x_policy_name => l_dummy_msg
1211 ,x_item_number => l_dummy_msg
1212 ,x_org_name => l_dummy_msg
1213 );
1214 log_now (p_log_level => FND_LOG.LEVEL_STATEMENT
1215 ,p_module => l_api_name
1216 ,p_message => ' return status from policy check :'||l_temp_status
1217 );
1218 IF (l_temp_status = G_EGO_SHORT_YES) THEN
1219 --
1220 -- The policy control allows the change
1221 -- So, Check_No_AML_Privilege should return NO
1222 --
1223 RETURN G_EGO_SHORT_NO;
1224 ELSE
1225 RETURN l_return_status;
1226 END IF;
1227 END IF;
1228 return l_return_status;
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 log_now (p_log_level => FND_LOG.LEVEL_EXCEPTION
1232 ,p_module => l_api_name
1233 ,p_message => 'Exception '||SQLERRM
1234 );
1235 IF c_product_check%ISOPEN THEN
1236 CLOSE c_product_check;
1237 END IF;
1238 RETURN l_return_status;
1239 END Check_No_AML_Priv;
1240
1241 PROCEDURE Check_No_MFG_Associations
1242 (p_api_version IN NUMBER
1243 ,p_manufacturer_id IN NUMBER
1244 ,p_manufacturer_name IN VARCHAR2
1245 ,x_return_status OUT NOCOPY VARCHAR2
1246 ,x_message_name OUT NOCOPY VARCHAR2
1247 ,x_message_text OUT NOCOPY VARCHAR2
1248 ) IS
1249 ----------------------------------------------------------------------------
1250 -- Start OF comments
1251 -- API name : Check_No_MFG_Associations
1252 -- TYPE : Public
1253 -- Pre-reqs : None
1254 -- FUNCTION : To check if any associations exist on the manufacturer
1255 -- Returns 'Y' if no associations exist
1256 -- Returns 'N' in all other cases
1257 --
1258 -- Parameters:
1259 -- IN : p_api_version NUMBER
1260 -- : p_application_name VARCHAR2
1261 -- : p_manufacturer_id NUMBER
1262 --
1263 -- Return Parameter:
1264 -- x_return_status
1265 -- : 'Y' if no associations exist
1266 -- : 'N' if associations exist
1267 -- x_message_text
1268 -- : returns the message text
1269 ----------------------------------------------------------------------------
1270 l_api_version NUMBER;
1271 l_api_name VARCHAR2(50);
1272 l_return_status VARCHAR2(1);
1273 l_message_name VARCHAR2(30);
1274 l_mfg_name mtl_manufacturers.manufacturer_name%TYPE;
1275
1276 BEGIN
1277 l_api_version := 1.0;
1278 l_api_name := 'CHECK_NO_MFG_ASSOCIATIONS';
1279 l_message_name := NULL;
1280 x_return_status := G_EGO_SHORT_YES;
1281 IF NOT FND_API.Compatible_API_Call (l_api_version,
1282 p_api_version,
1283 l_api_name,
1284 G_PKG_NAME) THEN
1285 RETURN;
1286 END IF;
1287
1288 IF (p_manufacturer_id IS NULL) THEN
1289 RETURN;
1290 END IF;
1291 --
1292 -- check for mfg part num associations
1293 --
1294 BEGIN
1295 SELECT 'X'
1296 INTO l_return_status
1297 FROM mtl_mfg_part_numbers
1298 WHERE manufacturer_id = p_manufacturer_id
1299 AND rownum = 1;
1300 l_message_name := 'EGO_MTL_MFG_PART_NUM_EXIST';
1301 EXCEPTION
1302 WHEN NO_DATA_FOUND THEN
1303 NULL;
1304 END;
1305 --
1306 -- check for pending changes for mfg part num
1307 --
1308 IF l_message_name IS NULL THEN
1309 BEGIN
1310 SELECT 'X'
1311 INTO l_return_status
1312 FROM DUAL
1313 WHERE EXISTS
1314 ( SELECT 'Y' FROM ego_mfg_part_num_chgs
1315 WHERE manufacturer_id = p_manufacturer_id
1316 );
1317 l_message_name := 'EGO_CHG_MFG_PART_NUM_EXIST';
1318 EXCEPTION
1319 WHEN NO_DATA_FOUND THEN
1320 NULL;
1321 END;
1322 END IF;
1323 --
1324 -- check for PML list in catalog categories
1325 --
1326 IF l_message_name IS NULL THEN
1327 BEGIN
1328 SELECT 'X'
1329 INTO l_return_status
1330 FROM ego_cat_grp_mfg_assocs
1331 WHERE manufacturer_id = p_manufacturer_id
1332 AND rownum = 1;
1333 l_message_name := 'EGO_PML_MFG_ASSOC_EXIST';
1334 EXCEPTION
1335 WHEN NO_DATA_FOUND THEN
1336 NULL;
1337 END;
1338 END IF;
1339
1340 IF l_message_name IS NOT NULL THEN
1341 BEGIN
1342 SELECT manufacturer_name
1343 INTO l_mfg_name
1344 FROM mtl_manufacturers
1345 WHERE manufacturer_id = p_manufacturer_id;
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 l_mfg_name := NULL;
1349 END;
1350 x_message_name := l_message_name;
1351 fnd_message.set_name('EGO',l_message_name);
1352 fnd_message.set_token('MFG_NAME',l_mfg_name);
1353 x_message_text := fnd_message.get();
1354 x_return_status := G_EGO_SHORT_NO;
1355 END IF;
1356 EXCEPTION
1357 WHEN OTHERS THEN
1358 fnd_message.set_name('EGO', 'EGO_PLSQL_ERR');
1359 fnd_message.set_token('PKG_NAME', G_PKG_NAME);
1360 fnd_message.set_token('API_NAME', l_api_name);
1361 fnd_message.set_token('SQL_ERR_MSG', SQLERRM);
1362 x_message_name := 'EGO_PLSQL_ERR';
1363 x_message_text := fnd_message.get;
1364 END Check_No_MFG_Associations;
1365
1366 END EGO_ITEM_AML_PUB;