[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_AML_PVT
Source
1 PACKAGE BODY EGO_ITEM_AML_PVT AS
2 /* $Header: EGOVAMLB.pls 120.9.12010000.2 2009/03/19 10:23:16 minxie ship $ */
3
4 -- ==========================================================================
5 -- Package variables and cursors
6 -- ==========================================================================
7
8 G_FILE_NAME VARCHAR2(12);
9 G_PKG_NAME VARCHAR2(30);
10
11 G_USER_ID fnd_user.user_id%TYPE;
12 G_PARTY_ID hz_parties.party_id%TYPE;
13 G_PARTY_NAME hz_parties.party_name%TYPE;
14 G_LOGIN_ID fnd_user.last_update_login%TYPE;
15 G_REQUEST_ID NUMBER;
16 G_PROG_APPID ego_aml_intf.program_application_id%TYPE;
17 G_PROG_ID ego_aml_intf.program_id%TYPE;
18 G_SYSDATE fnd_user.creation_date%TYPE;
19 G_SESSION_LANG VARCHAR2(99);
20 G_FND_OBJECT_NAME fnd_objects.obj_name%TYPE;
21 G_FND_OBJECT_ID fnd_objects.object_id%TYPE;
22
23 G_ERROR_TABLE_NAME VARCHAR2(99);
24 G_ERROR_ENTITY_CODE VARCHAR2(99);
25 G_ERROR_FILE_NAME VARCHAR2(99);
26 G_BO_IDENTIFIER VARCHAR2(99);
27
28 G_CP_ALLOWED VARCHAR2(99);
29 G_CP_NOT_ALLOWED VARCHAR2(99);
30 G_CP_CO_REQUIRED VARCHAR2(99);
31
32 G_CONC_RET_STS_SUCCESS VARCHAR2(1);
33 G_CONC_RET_STS_WARNING VARCHAR2(1);
34 G_CONC_RET_STS_ERROR VARCHAR2(1);
35
36 G_DEBUG_LEVEL_UNEXPECTED NUMBER;
37 G_DEBUG_LEVEL_ERROR NUMBER;
38 G_DEBUG_LEVEL_EXCEPTION NUMBER;
39 G_DEBUG_LEVEL_EVENT NUMBER;
40 G_DEBUG_LEVEL_PROCEDURE NUMBER;
41 G_DEBUG_LEVEL_STATEMENT NUMBER;
42 G_DEBUG_LOG_HEAD VARCHAR2(30);
43
44 G_PS_TO_BE_PROCESSED NUMBER;
45 G_PS_IN_PROCESS NUMBER;
46 G_PS_GENERIC_ERROR NUMBER;
47 G_PS_VAL_TO_ID_COMPLETE NUMBER;
48 G_PS_TRANSFER_TO_CM NUMBER;
49 G_PS_DFF_VAL_COMPLETE NUMBER;
50 G_PS_SUCCESS NUMBER;
51
52 G_PS_MAND_PARAM_MISSING NUMBER;
53 G_PS_INVALID_TRANS_TYPE NUMBER;
54 G_PS_SD_GT_ED_ERROR NUMBER;
55 G_PS_FA_STATUS_ERR NUMBER;
56 G_PS_APPROVAL_STATUS_ERR NUMBER;
57 G_PS_MANUFACTURER_ERR NUMBER;
58 G_PS_ORGANIZATION_ERR NUMBER;
59 G_PS_NOT_MASTER_ORG_ERR NUMBER;
60 G_PS_ITEM_ERR NUMBER;
61 G_PS_CREATE_REC_EXISTS NUMBER;
62 G_PS_REC_NOT_EXISTS NUMBER;
63 G_PS_DUP_INTF_RECORDS NUMBER;
64 G_PS_CHANGE_NOT_ALLOWED NUMBER;
65 G_PS_NO_AML_PRIV NUMBER;
66 G_PS_SD_NOT_NULL NUMBER;
67 G_PS_ED_LT_SYSDATE NUMBER;
68 G_PS_DFF_INVALID NUMBER;
69
70 TYPE G_MTL_DFF_ATTRIBUTES_REC IS RECORD
71 (attribute1 VARCHAR2(1)
72 ,attribute2 VARCHAR2(1)
73 ,attribute3 VARCHAR2(1)
74 ,attribute4 VARCHAR2(1)
75 ,attribute5 VARCHAR2(1)
76 ,attribute6 VARCHAR2(1)
77 ,attribute7 VARCHAR2(1)
78 ,attribute8 VARCHAR2(1)
79 ,attribute9 VARCHAR2(1)
80 ,attribute10 VARCHAR2(1)
81 ,attribute11 VARCHAR2(1)
82 ,attribute12 VARCHAR2(1)
83 ,attribute13 VARCHAR2(1)
84 ,attribute14 VARCHAR2(1)
85 ,attribute15 VARCHAR2(1)
86 );
87
88 -- ==========================================================================
89 -- Private Functions and Procedures
90 -- ==========================================================================
91 PROCEDURE write_aml_rec (p_data_set_id IN NUMBER
92 ,p_watch_data IN VARCHAR2
93 ) IS
94 l_aml_rec ego_aml_intf%ROWTYPE;
95 BEGIN
96 SELECT *
97 INTO l_aml_rec
98 from ego_aml_intf
99 where data_set_id = p_data_set_id;
100 -- sri_debug( p_watch_data ||': flag '||l_aml_rec.process_flag||
101 -- ' item id '||l_aml_rec.inventory_item_id||
102 -- ' item number '||l_aml_rec.item_number||
103 -- ' org id '||l_aml_rec.organization_id ||
104 -- ' org code '||l_aml_rec.organization_code
105 -- );
106 EXCEPTION
107 WHEN OTHERS THEN
108 -- sri_debug (' write_aml_rec unable to get the data :-( ');
109 NULL;
110 END;
111
112 --
113 -- write to debug into concurrent log
114 --
115 PROCEDURE log_now (p_log_level IN NUMBER
116 ,p_module IN VARCHAR2
117 ,p_message IN VARCHAR2
118 ) IS
119 BEGIN
120 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
121 fnd_log.string(log_level => p_log_level
122 ,module => G_DEBUG_LOG_HEAD||p_module
123 ,message => p_message
124 );
125 END IF;
126 --
127 -- writing to concurrent log
128 --
129 IF G_REQUEST_ID <> -1 AND p_log_level >= G_DEBUG_LEVEL_PROCEDURE THEN
130 FND_FILE.put_line(which => FND_FILE.LOG
131 ,buff => '['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')
132 ||'] '||p_message);
133 END IF;
134 -- sri_debug(G_PKG_NAME||' - '||p_message);
135 EXCEPTION
136 WHEN OTHERS THEN
137 RAISE;
138 END log_now;
139
140 --
141 -- Set Global Variables that will be used by the programs
142 --
143 PROCEDURE SetGobals IS
144 BEGIN
145 --
146 -- fine names
147 --
148 G_FILE_NAME := NVL(G_FILE_NAME,'EGOAMPVB.pls');
149 G_PKG_NAME := NVL(G_PKG_NAME,'EGO_ITEM_AML_PVT');
150 --
151 -- user values
152 --
153 G_USER_ID := FND_GLOBAL.user_id;
154 G_LOGIN_ID := FND_GLOBAL.login_id;
155 G_REQUEST_ID := NVL(FND_GLOBAL.conc_request_id, -1);
156 G_PROG_APPID := FND_GLOBAL.prog_appl_id;
157 G_PROG_ID := FND_GLOBAL.conc_program_id;
158 G_SYSDATE := NVL(G_SYSDATE,SYSDATE);
159 G_SESSION_LANG := USERENV('LANG');
160 BEGIN
161 SELECT party_id, party_name
162 INTO G_PARTY_ID, G_PARTY_NAME
163 FROM ego_user_v
164 WHERE USER_ID = G_USER_ID;
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 -- 3600938 sometimes user_id is not being retrieved
168 SELECT party_id, party_name, user_id
169 INTO G_PARTY_ID, G_PARTY_NAME, G_USER_ID
170 FROM ego_user_v
171 WHERE USER_NAME = FND_GLOBAL.USER_NAME;
172 END;
173 --
174 -- error handler parameters
175 --
176 G_ERROR_TABLE_NAME := NVL(G_ERROR_TABLE_NAME,'EGO_AML_INTF');
177 G_ERROR_ENTITY_CODE := NVL(G_ERROR_ENTITY_CODE,'EGO_AML');
178 G_ERROR_FILE_NAME := NULL;
179 G_BO_IDENTIFIER := NVL(G_BO_IDENTIFIER,'EGO_AML');
180 --
181 -- Change Policy constants
182 --
183 G_CP_ALLOWED := 'ALLOWED';
184 G_CP_NOT_ALLOWED := 'NOT_ALLOWED';
185 G_CP_CO_REQUIRED := 'CHANGE_ORDER_REQUIRED';
186 --
187 -- concurrent program return status
188 --
189 G_CONC_RET_STS_SUCCESS := '0';
190 G_CONC_RET_STS_WARNING := '1';
191 G_CONC_RET_STS_ERROR := '2';
192 --
193 -- debug parameter constants
194 --
195 G_DEBUG_LEVEL_UNEXPECTED := FND_LOG.LEVEL_UNEXPECTED;
196 G_DEBUG_LEVEL_ERROR := FND_LOG.LEVEL_ERROR;
197 G_DEBUG_LEVEL_EXCEPTION := FND_LOG.LEVEL_EXCEPTION;
198 G_DEBUG_LEVEL_EVENT := FND_LOG.LEVEL_EVENT;
199 G_DEBUG_LEVEL_PROCEDURE := FND_LOG.LEVEL_PROCEDURE;
200 G_DEBUG_LEVEL_STATEMENT := FND_LOG.LEVEL_STATEMENT;
201 G_DEBUG_LOG_HEAD := 'fnd.plsql.'||G_PKG_NAME||'.';
202 --
203 -- object parameters
204 --
205 G_FND_OBJECT_NAME := NVL(G_FND_OBJECT_NAME,'EGO_ITEM');
206 IF G_FND_OBJECT_ID IS NULL THEN
207 SELECT object_id
208 INTO G_FND_OBJECT_ID
209 FROM fnd_objects
210 WHERE obj_name = G_FND_OBJECT_NAME;
211 END IF;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 log_now (p_log_level => G_DEBUG_LEVEL_UNEXPECTED
216 ,p_module => 'SetGlobals'
217 ,p_message => 'Unable to intialize Globals'
218 );
219 END SetGobals;
220
221 --
222 -- Set Process Constants
223 --
224 PROCEDURE SetProcessConstants IS
225 BEGIN
226 --
227 -- status flags
228 --
229 G_PS_TO_BE_PROCESSED := 1;
230 G_PS_IN_PROCESS := 2;
231 G_PS_GENERIC_ERROR := 3;
232 G_PS_VAL_TO_ID_COMPLETE := 4;
233 G_PS_TRANSFER_TO_CM := 5;
234 G_PS_DFF_VAL_COMPLETE := 6;
235 G_PS_SUCCESS := 7;
236 --
237 -- error flags
238 --
239 G_PS_MAND_PARAM_MISSING := POWER(2,3); -- 8
240 G_PS_INVALID_TRANS_TYPE := POWER(2,4); -- 16
241 G_PS_SD_GT_ED_ERROR := POWER(2,5); -- 32
242 G_PS_FA_STATUS_ERR := POWER(2,6); -- 64
243 G_PS_APPROVAL_STATUS_ERR := POWER(2,7); -- 128
244 G_PS_MANUFACTURER_ERR := POWER(2,8); -- 256
245 G_PS_ORGANIZATION_ERR := POWER(2,9); -- 512
246 G_PS_NOT_MASTER_ORG_ERR := POWER(2,10); -- 1024
247 G_PS_ITEM_ERR := POWER(2,11); -- 2048
248 G_PS_CREATE_REC_EXISTS := POWER(2,12); -- 4096
249 G_PS_REC_NOT_EXISTS := POWER(2,13); -- 8192
250 G_PS_DUP_INTF_RECORDS := POWER(2,14); -- 16384
251 G_PS_CHANGE_NOT_ALLOWED := POWER(2,15); -- 32768
252 G_PS_NO_AML_PRIV := POWER(2,16); -- 65536
253 G_PS_SD_NOT_NULL := POWER(2,17); -- 131072
254 G_PS_ED_LT_SYSDATE := POWER(2,18); -- 262144
255 G_PS_DFF_INVALID := POWER(2,19); -- 524288
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 log_now (p_log_level => G_DEBUG_LEVEL_UNEXPECTED
260 ,p_module => 'SetProcessConstants'
261 ,p_message => 'Unable to set Global Constants'
262 );
263 END SetProcessConstants;
264
265 PROCEDURE ValueToIdConversion (p_data_set_id IN NUMBER
266 ,x_return_status OUT NOCOPY VARCHAR2
267 ,x_msg_count OUT NOCOPY NUMBER
268 ,x_msg_data OUT NOCOPY VARCHAR2) IS
269 l_api_name VARCHAR2(30);
270 BEGIN
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272 x_msg_count := 0;
273 x_msg_data := NULL;
274 l_api_name := 'ValueToIdConversion';
275 --
276 -- records will be processed with process_flag = G_PS_IN_PROCESS
277 -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
278 --
279 UPDATE ego_aml_intf
280 SET process_flag = G_PS_INVALID_TRANS_TYPE
281 WHERE data_set_id = p_data_set_id
282 AND process_flag = G_PS_IN_PROCESS
283 AND transaction_type NOT IN
284 (EGO_ITEM_PUB.G_TTYPE_CREATE
285 ,EGO_ITEM_PUB.G_TTYPE_UPDATE
286 ,EGO_ITEM_PUB.G_TTYPE_SYNC
287 ,EGO_ITEM_PUB.G_TTYPE_DELETE
288 );
289 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
290 ,p_module => l_api_name
291 ,p_message => ' Transaction type validation complete'
292 );
293
294 UPDATE ego_aml_intf aml_intf
295 SET process_flag = G_PS_SD_GT_ED_ERROR
296 WHERE data_set_id = p_data_set_id
297 AND process_flag = G_PS_IN_PROCESS
298 AND NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
299 > NVL(end_date,NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE));
300 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
301 ,p_module => l_api_name
302 ,p_message => ' Start Date - End Date validation complete'
303 );
304
305 UPDATE ego_aml_intf aml_intf
306 SET process_flag = G_PS_FA_STATUS_ERR
307 WHERE data_set_id = p_data_set_id
308 AND process_flag = G_PS_IN_PROCESS
309 AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
310 AND ( ( NVL(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
311 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
312 AND
313 first_article_status NOT IN
314 (SELECT lookup_code
315 FROM fnd_lookup_values fa_lookup
316 WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_FST_ATCLE_STS'
317 AND fa_lookup.language = G_SESSION_LANG)
318 )
319 OR
320 ( first_article_status IS NULL
321 AND
322 NVL(first_article_status_meaning,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
323 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
324 AND
325 first_article_status_meaning NOT IN
326 (SELECT meaning
327 FROM fnd_lookup_values fa_lookup
328 WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_FST_ATCLE_STS'
329 AND fa_lookup.language = G_SESSION_LANG)
330 )
331 );
332
333 UPDATE ego_aml_intf aml_intf
334 SET first_article_status =
335 DECODE (first_article_status_meaning, EGO_ITEM_PUB.G_INTF_NULL_CHAR,
336 EGO_ITEM_PUB.G_INTF_NULL_CHAR,
337 (Select lookup_code
338 from fnd_lookup_values fa_lookup
339 where fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_FST_ATCLE_STS'
340 and fa_lookup.meaning = aml_intf.first_article_status_meaning
341 and fa_lookup.language = G_SESSION_LANG)
342 )
343 WHERE data_set_id = p_data_set_id
344 AND process_flag = G_PS_IN_PROCESS
345 AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
346 AND first_article_status IS NULL
347 AND first_article_status_meaning IS NOT NULL;
348 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
349 ,p_module => l_api_name
350 ,p_message => 'VtoID Conversion Complete for First Article Status'
351 );
352
353 UPDATE ego_aml_intf aml_intf
354 SET process_flag = G_PS_APPROVAL_STATUS_ERR
355 WHERE data_set_id = p_data_set_id
356 AND process_flag = G_PS_IN_PROCESS
357 AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
358 AND ( ( NVL(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
359 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
360 AND
361 approval_status NOT IN
362 (SELECT lookup_code
363 FROM fnd_lookup_values fa_lookup
364 WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_APPR_STS'
365 AND fa_lookup.language = G_SESSION_LANG)
366 )
367 OR
368 ( approval_status IS NULL
369 AND
370 NVL(approval_status_meaning,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
371 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
372 AND
373 approval_status_meaning NOT IN
374 (SELECT meaning
375 FROM fnd_lookup_values fa_lookup
376 WHERE fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_APPR_STS'
377 AND fa_lookup.language = G_SESSION_LANG)
378 )
379 );
380
381 UPDATE ego_aml_intf aml_intf
382 SET approval_status =
383 DECODE (approval_status_meaning, EGO_ITEM_PUB.G_INTF_NULL_CHAR,
384 EGO_ITEM_PUB.G_INTF_NULL_CHAR,
385 (Select lookup_code
386 from fnd_lookup_values fa_lookup
387 where fa_lookup.lookup_type = 'EGO_CAT_GRP_MFG_APPR_STS'
388 and fa_lookup.meaning = aml_intf.approval_status_meaning
389 and fa_lookup.language = G_SESSION_LANG)
390 )
391 WHERE data_set_id = p_data_set_id
392 AND process_flag = G_PS_IN_PROCESS
393 AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
394 AND approval_status IS NULL
395 AND approval_status_meaning IS NOT NULL;
396 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
397 ,p_module => l_api_name
398 ,p_message => 'VtoID Conversion Complete for Approval Status'
399 );
400
401 UPDATE ego_aml_intf aml_intf
402 SET process_flag = G_PS_MANUFACTURER_ERR
403 WHERE data_set_id = p_data_set_id
404 AND process_flag = G_PS_IN_PROCESS
405 AND ( (manufacturer_id IS NOT NULL
406 AND
407 NOT EXISTS
408 (SELECT 'x' FROM mtl_manufacturers manu
409 WHERE manu.manufacturer_id = aml_intf.manufacturer_id)
410 )
411 OR
412 (manufacturer_id IS NULL
413 AND
414 NOT EXISTS
415 (SELECT 'x' FROM mtl_manufacturers manu
416 WHERE manu.manufacturer_name = aml_intf.manufacturer_name)
417 )
418 );
419
420 UPDATE ego_aml_intf aml_intf
421 SET manufacturer_id =
422 (Select manufacturer_id
423 from mtl_manufacturers manu
424 where manu.manufacturer_name = aml_intf.manufacturer_name)
425 WHERE data_set_id = p_data_set_id
426 AND process_flag = G_PS_IN_PROCESS
427 AND manufacturer_id IS NULL;
428 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
429 ,p_module => l_api_name
430 ,p_message => 'VtoID Conversion Complete for Manufacturers'
431 );
432
433 UPDATE ego_aml_intf aml_intf
434 SET process_flag = G_PS_ORGANIZATION_ERR
435 WHERE data_set_id = p_data_set_id
436 AND process_flag = G_PS_IN_PROCESS
437 AND ( (organization_id IS NOT NULL
438 AND
439 NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
440 WHERE mp.organization_id = aml_intf.organization_id)
441 )
442 OR
443 (organization_id IS NULL
444 AND
445 NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
446 WHERE mp.organization_code = aml_intf.organization_code)
447 )
448 );
449
450 UPDATE ego_aml_intf aml_intf
451 SET process_flag = G_PS_NOT_MASTER_ORG_ERR
452 WHERE data_set_id = p_data_set_id
453 AND process_flag = G_PS_IN_PROCESS
454 AND ( (organization_id IS NOT NULL
455 AND
456 NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
457 WHERE mp.organization_id = aml_intf.organization_id
458 AND mp.organization_id = mp.master_organization_id)
459 )
460 OR
461 (organization_id IS NULL
462 AND
463 NOT EXISTS (SELECT 'x' FROM mtl_parameters mp
464 WHERE mp.organization_code = aml_intf.organization_code
465 AND mp.organization_id = mp.master_organization_id)
466 )
467 );
468
469 UPDATE ego_aml_intf aml_intf
470 SET organization_id =
471 (Select organization_id
472 from mtl_parameters mp
473 where mp.organization_code = aml_intf.organization_code)
474 WHERE data_set_id = p_data_set_id
475 AND process_flag = G_PS_IN_PROCESS
476 AND organization_id IS NULL;
477 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
478 ,p_module => l_api_name
479 ,p_message => 'VtoID Conversion Complete for Organization'
480 );
481
482 UPDATE ego_aml_intf aml_intf
483 SET process_flag = G_PS_ITEM_ERR
484 WHERE data_set_id = p_data_set_id
485 AND process_flag = G_PS_IN_PROCESS
486 AND ( (inventory_item_id IS NOT NULL
487 AND
488 NOT EXISTS
489 (SELECT 'x' FROM mtl_system_items_b_kfv item
490 WHERE item.organization_id = aml_intf.organization_id
491 AND item.inventory_item_id = aml_intf.inventory_item_id)
492 )
493 OR
494 (inventory_item_id IS NULL
495 AND
496 NOT EXISTS
497 (SELECT 'x' FROM mtl_system_items_b_kfv item
498 WHERE item.organization_id = aml_intf.organization_id
499 AND item.concatenated_segments = aml_intf.item_number)
500 )
501 );
502
503 UPDATE ego_aml_intf aml_intf
504 SET (item_number, prog_int_num1, prog_int_num2,
505 prog_int_num3, prog_int_char1) =
506 (Select concatenated_segments, item_catalog_group_id, lifecycle_id,
507 current_phase_id, NVL(approval_status,'A')
508 from mtl_system_items_b_kfv item
509 where item.organization_id = aml_intf.organization_id
510 and item.inventory_item_id = aml_intf.inventory_item_id)
511 WHERE data_set_id = p_data_set_id
512 AND process_flag = G_PS_IN_PROCESS
513 AND inventory_item_id IS NOT NULL;
514
515 UPDATE ego_aml_intf aml_intf
516 SET (inventory_item_id, prog_int_num1, prog_int_num2,
517 prog_int_num3, prog_int_char1) =
518 (Select inventory_item_id, item_catalog_group_id, lifecycle_id,
519 current_phase_id, NVL(APPROVAL_STATUS,'A')
520 from mtl_system_items_b_kfv item
521 where item.organization_id = aml_intf.organization_id
522 and item.concatenated_segments = aml_intf.item_number)
523 WHERE data_set_id = p_data_set_id
524 AND process_flag = G_PS_IN_PROCESS
525 AND inventory_item_id IS NULL;
526
527 UPDATE ego_aml_intf aml_intf
528 SET process_flag = G_PS_VAL_TO_ID_COMPLETE
529 WHERE data_set_id = p_data_set_id
530 AND process_flag = G_PS_IN_PROCESS;
531
532 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
533 ,p_module => l_api_name
534 ,p_message => 'VtoID Conversion Complete for Item'
535 );
536 EXCEPTION
537 WHEN OTHERS THEN
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 x_msg_count := 1;
540 -- for SQL errors
541 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
542 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
543 FND_MESSAGE.Set_Token('API_NAME', 'ValueToIdConversion');
544 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
545 x_msg_data := FND_MESSAGE.get();
546 END ValueToIdConversion;
547
548
549 PROCEDURE TransactionCheck (p_data_set_id IN NUMBER
550 ,p_mode IN VARCHAR2
551 ,x_return_status OUT NOCOPY VARCHAR2
552 ,x_msg_count OUT NOCOPY NUMBER
553 ,x_msg_data OUT NOCOPY VARCHAR2) IS
554 l_api_name VARCHAR2(30);
555 BEGIN
556 x_return_status := FND_API.G_RET_STS_SUCCESS;
557 x_msg_count := 0;
558 x_msg_data := NULL;
559 l_api_name := 'TransactionCheck';
560 --
561 -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
562 -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
563 --
564 UPDATE ego_aml_intf aml_intf
565 SET process_flag = G_PS_CREATE_REC_EXISTS
566 WHERE data_set_id = p_data_set_id
567 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
568 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
569 AND EXISTS
570 (Select 'x'
571 from mtl_mfg_part_numbers part_num
572 where part_num.inventory_item_id = aml_intf.inventory_item_id
573 and part_num.organization_id = aml_intf.organization_id
574 and part_num.manufacturer_id = aml_intf.manufacturer_id
575 and part_num.mfg_part_num = aml_intf.mfg_part_num
576 );
577 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
578 ,p_module => l_api_name
579 ,p_message => 'Transaction check complete for CREATE'
580 );
581
582 UPDATE ego_aml_intf aml_intf
583 SET process_flag = G_PS_REC_NOT_EXISTS
584 WHERE data_set_id = p_data_set_id
585 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
586 AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
587 ,EGO_ITEM_PUB.G_TTYPE_DELETE
588 )
589 AND NOT EXISTS
590 (Select 'x'
591 from mtl_mfg_part_numbers part_num
592 where part_num.inventory_item_id = aml_intf.inventory_item_id
593 and part_num.organization_id = aml_intf.organization_id
594 and part_num.manufacturer_id = aml_intf.manufacturer_id
595 and part_num.mfg_part_num = aml_intf.mfg_part_num
596 );
597 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
598 ,p_module => l_api_name
599 ,p_message => 'Transaction check complete for UPDATE'
600 );
601
602 UPDATE ego_aml_intf aml_intf
603 SET transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
604 WHERE data_set_id = p_data_set_id
605 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
606 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_SYNC
607 AND EXISTS
608 (Select 'x'
609 from mtl_mfg_part_numbers part_num
610 where part_num.inventory_item_id = aml_intf.inventory_item_id
611 and part_num.organization_id = aml_intf.organization_id
612 and part_num.manufacturer_id = aml_intf.manufacturer_id
613 and part_num.mfg_part_num = aml_intf.mfg_part_num
614 );
615
616 UPDATE ego_aml_intf aml_intf
617 SET transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
618 WHERE data_set_id = p_data_set_id
619 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
620 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_SYNC;
621 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
622 ,p_module => l_api_name
623 ,p_message => 'Transaction check complete for SYNC'
624 );
625
626 -- I think we do not need this
627 -- UPDATE ego_aml_intf aml_intf
628 -- SET process_flag = G_PS_SD_NOT_NULL
629 -- WHERE data_set_id = p_data_set_id
630 -- AND process_flag = G_PS_VAL_TO_ID_COMPLETE
631 -- AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
632 -- AND start_date = EGO_ITEM_PUB.G_INTF_NULL_DATE;
633
634 IF p_mode <> MODE_HISTORICAL THEN
635 UPDATE ego_aml_intf aml_intf
636 SET process_flag = G_PS_ED_LT_SYSDATE
637 WHERE data_set_id = p_data_set_id
638 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
639 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
640 AND NVL(end_date,G_SYSDATE) <> EGO_ITEM_PUB.g_INTF_NULL_DATE
641 AND NVL(end_date,G_SYSDATE) < G_SYSDATE;
642 END IF;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 x_msg_count := 1;
648 -- for SQL errors
649 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
650 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
651 FND_MESSAGE.Set_Token('API_NAME', 'TransactionCheck');
652 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
653 x_msg_data := FND_MESSAGE.get();
654 END TransactionCheck;
655
656
657 PROCEDURE performDupRecordCheck
658 (p_data_set_id IN NUMBER
659 ,x_return_status OUT NOCOPY VARCHAR2
660 ,x_msg_count OUT NOCOPY NUMBER
661 ,x_msg_data OUT NOCOPY VARCHAR2) IS
662 l_api_name VARCHAR2(30);
663 BEGIN
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665 x_msg_count := 0;
666 x_msg_data := NULL;
667 l_api_name := 'performDupRecordCheck';
668 --
669 -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
670 -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
671 --
672 UPDATE ego_aml_intf orig
673 SET process_flag = G_PS_DUP_INTF_RECORDS
674 WHERE data_set_id = p_data_set_id
675 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
676 AND EXISTS
677 (SELECT 'X'
678 FROM ego_aml_intf
679 WHERE data_set_id = p_data_set_id
680 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
681 AND transaction_id <> orig.transaction_id
682 AND inventory_item_id = orig.inventory_item_id
683 AND organization_id = orig.organization_id
684 AND manufacturer_id = orig.manufacturer_id
685 AND mfg_part_num = orig.mfg_part_num
686 );
687
688 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
689 ,p_module => l_api_name
690 ,p_message => 'Dup Check Complete'
691 );
692 EXCEPTION
693 WHEN OTHERS THEN
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695 x_msg_count := 1;
696 -- for SQL errors
697 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
698 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
699 FND_MESSAGE.Set_Token('API_NAME', 'performDupRecordCheck');
700 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
701 x_msg_data := FND_MESSAGE.get();
702 END performDupRecordCheck;
703
704
705 PROCEDURE performCMSeggregation (p_data_set_id IN NUMBER
706 ,x_return_status OUT NOCOPY VARCHAR2
707 ,x_msg_count OUT NOCOPY NUMBER
708 ,x_msg_data OUT NOCOPY VARCHAR2) IS
709
710 CURSOR c_item_records (cp_data_set_id IN NUMBER) IS
711 SELECT *
712 FROM ego_aml_intf
713 WHERE data_set_id = cp_data_set_id
714 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
715 AND NVL(prog_int_char2,'N') <> 'Y'
716 AND prog_int_num4 IS NOT NULL
717 FOR UPDATE OF transaction_id;
718
719 l_dynamic_sql VARCHAR2(4000);
720 l_policy_object_name VARCHAR2(30);
721 l_policy_code VARCHAR2(30);
722 l_attr_object_name VARCHAR2(30);
723 l_attr_code VARCHAR2(30);
724 l_policy_value VARCHAR2(99);
725 l_api_name VARCHAR2(30);
726 l_add_all_to_cm VARCHAR2(1);
727
728 BEGIN
729 x_return_status := FND_API.G_RET_STS_SUCCESS;
730 x_msg_count := 0;
731 x_msg_data := NULL;
732 l_api_name := 'performCMSeggregation';
733 --
734 -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
735 --
736 l_add_all_to_cm :=
737 EGO_IMPORT_PVT.getAddAllToChangeFlag(p_batch_id => p_data_set_id);
738 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
739 ,p_module => l_api_name
740 ,p_message => 'Value of Add all to CM '||l_add_all_to_cm
741 );
742
743 UPDATE ego_aml_intf aml_intf
744 SET prog_int_char2 = 'Y'
745 WHERE data_set_id = p_data_set_id
746 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
747 AND EXISTS (Select 1
748 from mtl_system_items_interface
749 where request_id = aml_intf.request_id
750 and inventory_item_id = aml_intf.inventory_item_id
751 and organization_id = aml_intf.organization_id
752 and transaction_type = 'CREATE'
753 and process_flag = 7
754 );
755
756 UPDATE ego_aml_intf aml_intf
757 SET prog_int_num4 =
758 (SELECT ic.item_catalog_group_id
759 FROM mtl_item_catalog_groups_b ic
760 WHERE EXISTS
761 (SELECT olc.object_classification_code CatalogId
762 FROM ego_obj_type_lifecycles olc
763 WHERE olc.object_id = G_FND_OBJECT_ID
764 AND olc.lifecycle_id = aml_intf.prog_int_num2
765 AND olc.object_classification_code =
766 to_char(ic.item_catalog_group_id)
767 )
768 AND ROWNUM = 1
769 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
770 START WITH item_catalog_group_id = aml_intf.prog_int_num1
771 )
772 WHERE data_set_id = p_data_set_id
773 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
774 AND prog_int_num2 IS NOT NULL;
775 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
776 ,p_module => l_api_name
777 ,p_message => 'CC at which policy is associated obtained'
778 );
779
780 IF l_add_all_to_cm = 'Y' THEN
781 UPDATE ego_aml_intf aml_intf
782 SET process_flag = G_PS_TRANSFER_TO_CM
783 WHERE data_set_id = p_data_set_id
784 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
785 AND NVL(prog_int_char2,'N') <> 'Y'
786 AND prog_int_num4 IS NULL;
787 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
788 ,p_module => l_api_name
789 ,p_message => 'All items with no LC are moved forcefully to CM'
790 );
791 END IF;
792
793 l_policy_object_name := 'CATALOG_LIFECYCLE_PHASE';
794 l_policy_code := 'CHANGE_POLICY';
795 l_attr_object_name := 'EGO_CATALOG_GROUP';
796 l_attr_code := 'AML_RULE';
797 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
798 ,p_module => l_api_name
799 ,p_message => 'Policy check started'
800 );
801 FOR cr in c_item_records(cp_data_set_id => p_data_set_id) LOOP
802 l_dynamic_sql :=
803 ' BEGIN '||
804 ' ENG_CHANGE_POLICY_PKG.GetChangePolicy '||
805 ' (p_policy_object_name => :l_policy_object_name '||
806 ' ,p_policy_code => :l_policy_code '||
807 ' ,p_policy_pk1_value => TO_CHAR(:catalog_cat_id) '||
808 ' ,p_policy_pk2_value => TO_CHAR(:lifecycle_id) '||
809 ' ,p_policy_pk3_value => TO_CHAR(:current_phase_id) '||
810 ' ,p_policy_pk4_value => NULL '||
811 ' ,p_policy_pk5_value => NULL '||
812 ' ,p_attribute_object_name => :l_attr_object_name '||
813 ' ,p_attribute_code => :l_attr_code '||
814 ' ,p_attribute_value => 1 '||
815 ' ,x_policy_value => :l_policy_value '||
816 ' ); '||
817 ' END;';
818 EXECUTE IMMEDIATE l_dynamic_sql
819 USING IN l_policy_object_name,
820 IN l_policy_code,
821 IN cr.prog_int_num4,
822 IN cr.prog_int_num2,
823 IN cr.prog_int_num3,
824 IN l_attr_object_name,
825 IN l_attr_code,
826 OUT l_policy_value;
827 l_policy_value := NVL(l_policy_value ,G_CP_ALLOWED);
828 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
829 ,p_module => l_api_name
830 ,p_message => 'For Item '||cr.item_number ||
831 ' under transaction '|| cr.transaction_id ||
832 ' policy is '||l_policy_value
833 );
834 IF l_policy_value = G_CP_NOT_ALLOWED THEN
835 UPDATE ego_aml_intf aml_intf
836 SET process_flag = G_PS_CHANGE_NOT_ALLOWED
837 WHERE CURRENT OF c_item_records;
838 ELSIF l_policy_value = G_CP_CO_REQUIRED OR l_add_all_to_cm = 'Y' THEN
839 UPDATE ego_aml_intf aml_intf
840 SET process_flag = G_PS_TRANSFER_TO_CM
841 WHERE CURRENT OF c_item_records;
842 END IF;
843 END LOOP;
844 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
845 ,p_module => l_api_name
846 ,p_message => 'Policy check completed'
847 );
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 IF c_item_records%ISOPEN THEN
852 CLOSE c_item_records;
853 END IF;
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
855 x_msg_count := 1;
856 -- for SQL errors
857 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
858 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
859 FND_MESSAGE.Set_Token('API_NAME', 'performCMSeggregation');
860 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
861 x_msg_data := FND_MESSAGE.get();
862 END performCMSeggregation;
863
864
865 PROCEDURE performItemValidation (p_data_set_id IN NUMBER
866 ,p_perform_security_check IN BOOLEAN
867 ,x_return_status OUT NOCOPY VARCHAR2
868 ,x_msg_count OUT NOCOPY NUMBER
869 ,x_msg_data OUT NOCOPY VARCHAR2
870 ) IS
871 l_aml_edit_priv VARCHAR2(30);
872 l_sec_predicate VARCHAR2(32767);
873 l_dynamic_sql VARCHAR2(32767);
874 l_debug_number NUMBER;
875 l_api_name VARCHAR2(30);
876
877 CURSOR c_item_records (cp_data_set_id IN NUMBER) IS
878 SELECT *
879 FROM ego_aml_intf
880 WHERE data_set_id = cp_data_set_id
881 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
882 AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
883 ,EGO_ITEM_PUB.G_TTYPE_UPDATE
884 )
885 FOR UPDATE OF transaction_id;
886
887 BEGIN
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889 x_msg_count := 0;
890 x_msg_data := NULL;
891 l_api_name := 'performItemValidation';
892 --
893 -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
894 -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
895 --
896 IF p_perform_security_check THEN
897 l_aml_edit_priv := 'EGO_EDIT_ITEM_AML';
898 EGO_DATA_SECURITY.get_security_predicate
899 (p_api_version => 1.0
900 ,p_function => l_aml_edit_priv
901 ,p_object_name => G_FND_OBJECT_NAME
902 ,p_user_name => 'HZ_PARTY:'||TO_CHAR(G_PARTY_ID)
903 ,p_statement_type => 'EXISTS'
904 ,p_pk1_alias => 'aml_intf.inventory_item_id'
905 ,p_pk2_alias => 'aml_intf.organization_id'
906 ,p_pk3_alias => NULL
907 ,p_pk4_alias => NULL
908 ,p_pk5_alias => NULL
909 ,x_predicate => l_sec_predicate
910 ,x_return_status => x_return_status );
911 -- Result of all the operations
912 -- 'T' Successfully got predicate
913 -- 'F' No predicates granted
914 -- 'E' Error
915 -- 'U' Unexpected Error
916 -- 'L' Value too long- predicate too large for
917 -- database VPD.
918 --
919 -- If 'E', 'U, or 'L' is returned, there will be an error
920 -- message on the FND_MESSAGE stack which
921 -- can be retrieved with FND_MESSAGE.GET_ENCODED()
922 -- If that message is not used, it must be cleared.
923 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
924 ,p_module => l_api_name
925 ,p_message => 'Returning get Security Predicate with status - '
926 ||x_return_status
927 );
928 IF x_return_status IN ('T','F') THEN
929 x_return_status := FND_API.G_RET_STS_SUCCESS;
930 IF l_sec_predicate IS NULL THEN
931 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
932 ,p_module => l_api_name
933 ,p_message => ' Security Predicate is NULL'
934 );
935 ELSE
936 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
937 ,p_module => l_api_name
938 ,p_message => 'Security Predicate is as follows'
939 );
940 l_debug_number := CEIL(LENGTH(l_sec_predicate)/100);
941 FOR i IN 1..l_debug_number LOOP
942 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
943 ,p_module => l_api_name
944 ,p_message => SUBSTR(l_sec_predicate,(i-1)*100,100)
945 );
946 END LOOP;
947 l_dynamic_sql :=
948 ' UPDATE EGO_AML_INTF aml_intf ' ||
949 ' SET process_flag = '||G_PS_NO_AML_PRIV ||
950 ' WHERE data_set_id = :1'||
951 ' AND process_flag = '||G_PS_VAL_TO_ID_COMPLETE||
952 ' AND NVL(prog_int_char2,''N'') <> ''Y'''||
953 ' AND NOT '|| l_sec_predicate;
954 EXECUTE IMMEDIATE l_dynamic_sql
955 USING IN p_data_set_id;
956 END IF;
957 ELSE
958 IF x_return_status = 'L' THEN
959 x_return_status := FND_API.G_RET_STS_ERROR;
960 END IF;
961 x_msg_data := FND_MESSAGE.GET_ENCODED();
962 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
963 ,p_module => l_api_name
964 ,p_message => 'Security Predicate has returned with message - '
965 ||x_msg_data
966 );
967 END IF;
968 ELSE
969 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
970 ,p_module => l_api_name
971 ,p_message => 'No need to perform Security check'
972 );
973 END IF;
974
975 UPDATE ego_aml_intf
976 SET mrp_planning_code =
977 DECODE(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
978 mrp_planning_code),
979 description =
980 DECODE(description,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
981 description),
982 attribute_category =
983 DECODE(attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
984 attribute_category),
985 attribute1 =
986 DECODE(attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
987 attribute1),
988 attribute2 =
989 DECODE(attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
990 attribute2),
991 attribute3 =
992 DECODE(attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
993 attribute3),
994 attribute4 =
995 DECODE(attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
996 attribute4),
997 attribute5 =
998 DECODE(attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
999 attribute5),
1000 attribute6 =
1001 DECODE(attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1002 attribute6),
1003 attribute7 =
1004 DECODE(attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1005 attribute7),
1006 attribute8 =
1007 DECODE(attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1008 attribute8),
1009 attribute9 =
1010 DECODE(attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1011 attribute9),
1012 attribute10 =
1013 DECODE(attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1014 attribute10),
1015 attribute11 =
1016 DECODE(attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1017 attribute11),
1018 attribute12 =
1019 DECODE(attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1020 attribute12),
1021 attribute13 =
1022 DECODE(attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1023 attribute13),
1024 attribute14 =
1025 DECODE(attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1026 attribute14),
1027 attribute15 =
1028 DECODE(attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1029 attribute15),
1030 first_article_status =
1031 DECODE(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1032 first_article_status),
1033 approval_status =
1034 DECODE(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1035 approval_status),
1036 start_date =
1037 DECODE(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
1038 start_date),
1039 end_date =
1040 DECODE(end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
1041 end_date)
1042 WHERE data_set_id = p_data_set_id
1043 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1044 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
1045 AND ( NVL(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM)
1046 <> EGO_ITEM_PUB.G_INTF_NULL_NUM
1047 OR
1048 NVL(description,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1049 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1050 OR
1051 NVL(attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1052 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1053 OR
1054 NVL(attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1055 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1056 OR
1057 NVL(attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1058 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1059 OR
1060 NVL(attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1061 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1062 OR
1063 NVL(attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1064 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1065 OR
1066 NVL(attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1067 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1068 OR
1069 NVL(attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1070 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1071 OR
1072 NVL(attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1073 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1074 OR
1075 NVL(attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1076 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1077 OR
1078 NVL(attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1079 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1080 OR
1081 NVL(attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1082 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1083 OR
1084 NVL(attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1085 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1086 OR
1087 NVL(attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1088 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1089 OR
1090 NVL(attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1091 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1092 OR
1093 NVL(attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1094 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1095 OR
1096 NVL(attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1097 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1098 OR
1099 NVL(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1100 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1101 OR
1102 NVL(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1103 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1104 OR
1105 NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
1106 <> EGO_ITEM_PUB.G_INTF_NULL_DATE
1107 OR
1108 NVL(end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
1109 <> EGO_ITEM_PUB.G_INTF_NULL_DATE
1110 );
1111 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1112 ,p_module => l_api_name
1113 ,p_message => 'Changing intf table with NULL during CREATE'
1114 );
1115
1116 UPDATE ego_aml_intf intf
1117 SET (mrp_planning_code
1118 ,description
1119 ,attribute_category
1120 ,attribute1
1121 ,attribute2
1122 ,attribute3
1123 ,attribute4
1124 ,attribute5
1125 ,attribute6
1126 ,attribute7
1127 ,attribute8
1128 ,attribute9
1129 ,attribute10
1130 ,attribute11
1131 ,attribute12
1132 ,attribute13
1133 ,attribute14
1134 ,attribute15
1135 ,first_article_status
1136 ,approval_status
1137 ,start_date
1138 ,end_date
1139 )
1140 = (SELECT
1141 DECODE(intf.mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
1142 NULL,prod.mrp_planning_code,
1143 intf.mrp_planning_code),
1144 DECODE(intf.description,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1145 NULL,prod.description,
1146 intf.description),
1147 DECODE(intf.attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1148 NULL,prod.attribute_category,
1149 intf.attribute_category),
1150 DECODE(intf.attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1151 NULL,prod.attribute1,
1152 intf.attribute1),
1153 DECODE(intf.attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1154 NULL,prod.attribute2,
1155 intf.attribute2),
1156 DECODE(intf.attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1157 NULL,prod.attribute3,
1158 intf.attribute3),
1159 DECODE(intf.attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1160 NULL,prod.attribute4,
1161 intf.attribute4),
1162 DECODE(intf.attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1163 NULL,prod.attribute5,
1164 intf.attribute5),
1165 DECODE(intf.attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1166 NULL,prod.attribute6,
1167 intf.attribute6),
1168 DECODE(intf.attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1169 NULL,prod.attribute7,
1170 intf.attribute7),
1171 DECODE(intf.attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1172 NULL,prod.attribute8,
1173 intf.attribute8),
1174 DECODE(intf.attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1175 NULL,prod.attribute9,
1176 intf.attribute9),
1177 DECODE(intf.attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1178 NULL,prod.attribute10,
1179 intf.attribute10),
1180 DECODE(intf.attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1181 NULL,prod.attribute11,
1182 intf.attribute11),
1183 DECODE(intf.attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1184 NULL,prod.attribute12,
1185 intf.attribute12),
1186 DECODE(intf.attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1187 NULL,prod.attribute13,
1188 intf.attribute13),
1189 DECODE(intf.attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1190 NULL,prod.attribute14,
1191 intf.attribute14),
1192 DECODE(intf.attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1193 NULL,prod.attribute15,
1194 intf.attribute15),
1195 DECODE(intf.first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1196 NULL,prod.first_article_status,
1197 intf.first_article_status),
1198 DECODE(intf.approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR,NULL,
1199 NULL,prod.approval_status,
1200 intf.approval_status),
1201 DECODE(intf.start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
1202 NULL,prod.start_date,
1203 intf.start_date),
1204 DECODE(intf.end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE,NULL,
1205 NULL,prod.end_date,
1206 intf.end_date)
1207 FROM mtl_mfg_part_numbers prod
1208 WHERE intf.inventory_item_id = prod.inventory_item_id
1209 AND intf.organization_id = prod.organization_id
1210 AND intf.manufacturer_id = prod.manufacturer_id
1211 AND intf.mfg_part_num = prod.mfg_part_num
1212 )
1213 WHERE data_set_id = p_data_set_id
1214 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1215 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
1216 AND ( NVL(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM)
1217 <> EGO_ITEM_PUB.G_INTF_NULL_NUM
1218 OR
1219 NVL(description,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1220 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1221 OR
1222 NVL(attribute_category,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1223 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1224 OR
1225 NVL(attribute1,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1226 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1227 OR
1228 NVL(attribute2,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1229 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1230 OR
1231 NVL(attribute3,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1232 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1233 OR
1234 NVL(attribute4,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1235 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1236 OR
1237 NVL(attribute5,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1238 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1239 OR
1240 NVL(attribute6,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1241 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1242 OR
1243 NVL(attribute7,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1244 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1245 OR
1246 NVL(attribute8,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1247 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1248 OR
1249 NVL(attribute9,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1250 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1251 OR
1252 NVL(attribute10,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1253 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1254 OR
1255 NVL(attribute11,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1256 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1257 OR
1258 NVL(attribute12,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1259 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1260 OR
1261 NVL(attribute13,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1262 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1263 OR
1264 NVL(attribute14,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1265 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1266 OR
1267 NVL(attribute15,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1268 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1269 OR
1270 NVL(first_article_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1271 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1272 OR
1273 NVL(approval_status,EGO_ITEM_PUB.G_INTF_NULL_CHAR)
1274 <> EGO_ITEM_PUB.G_INTF_NULL_CHAR
1275 OR
1276 NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
1277 <> EGO_ITEM_PUB.G_INTF_NULL_DATE
1278 OR
1279 NVL(end_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
1280 <> EGO_ITEM_PUB.G_INTF_NULL_DATE
1281 );
1282 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1283 ,p_module => l_api_name
1284 ,p_message => 'Populate intf table with prod data for UPDATE done'
1285 );
1286
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 IF c_item_records%ISOPEN THEN
1290 CLOSE c_item_records;
1291 END IF;
1292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1293 x_msg_count := 1;
1294 -- for SQL errors
1295 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1296 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1297 FND_MESSAGE.Set_Token('API_NAME', 'performItemValidation');
1298 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1299 x_msg_data := FND_MESSAGE.get();
1300 END performItemValidation;
1301
1302
1303 PROCEDURE resetDFFFieldUsage
1304 (p_dff_usage_record IN OUT NOCOPY G_MTL_DFF_ATTRIBUTES_REC
1305 ) IS
1306 BEGIN
1307 p_dff_usage_record.attribute1 := FND_API.G_FALSE;
1308 p_dff_usage_record.attribute2 := FND_API.G_FALSE;
1309 p_dff_usage_record.attribute3 := FND_API.G_FALSE;
1310 p_dff_usage_record.attribute4 := FND_API.G_FALSE;
1311 p_dff_usage_record.attribute5 := FND_API.G_FALSE;
1312 p_dff_usage_record.attribute6 := FND_API.G_FALSE;
1313 p_dff_usage_record.attribute7 := FND_API.G_FALSE;
1314 p_dff_usage_record.attribute8 := FND_API.G_FALSE;
1315 p_dff_usage_record.attribute9 := FND_API.G_FALSE;
1316 p_dff_usage_record.attribute10 := FND_API.G_FALSE;
1317 p_dff_usage_record.attribute11 := FND_API.G_FALSE;
1318 p_dff_usage_record.attribute12 := FND_API.G_FALSE;
1319 p_dff_usage_record.attribute13 := FND_API.G_FALSE;
1320 p_dff_usage_record.attribute14 := FND_API.G_FALSE;
1321 p_dff_usage_record.attribute15 := FND_API.G_FALSE;
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 NULL;
1325 END resetDFFFieldUsage;
1326
1327 FUNCTION getAttrValue (
1328 p_part_num_rec IN ego_aml_intf%ROWTYPE
1329 ,p_column_name IN VARCHAR2
1330 ,p_dff_usage_record IN OUT NOCOPY G_MTL_DFF_ATTRIBUTES_REC
1331 ) RETURN VARCHAR2 IS
1332 BEGIN
1333 IF p_column_name = 'ATTRIBUTE1' THEN
1334 p_dff_usage_record.attribute1 := FND_API.G_TRUE;
1335 RETURN p_part_num_rec.attribute1;
1336 ELSIF p_column_name = 'ATTRIBUTE2' THEN
1337 p_dff_usage_record.attribute2 := FND_API.G_TRUE;
1338 RETURN p_part_num_rec.attribute2;
1339 ELSIF p_column_name = 'ATTRIBUTE3' THEN
1340 p_dff_usage_record.attribute3 := FND_API.G_TRUE;
1341 RETURN p_part_num_rec.attribute3;
1342 ELSIF p_column_name = 'ATTRIBUTE4' THEN
1343 p_dff_usage_record.attribute4 := FND_API.G_TRUE;
1344 RETURN p_part_num_rec.attribute4;
1345 ELSIF p_column_name = 'ATTRIBUTE5' THEN
1346 p_dff_usage_record.attribute5 := FND_API.G_TRUE;
1347 RETURN p_part_num_rec.attribute5;
1348 ELSIF p_column_name = 'ATTRIBUTE6' THEN
1349 p_dff_usage_record.attribute6 := FND_API.G_TRUE;
1350 RETURN p_part_num_rec.attribute6;
1351 ELSIF p_column_name = 'ATTRIBUTE7' THEN
1352 p_dff_usage_record.attribute7 := FND_API.G_TRUE;
1353 RETURN p_part_num_rec.attribute7;
1354 ELSIF p_column_name = 'ATTRIBUTE8' THEN
1355 p_dff_usage_record.attribute8 := FND_API.G_TRUE;
1356 RETURN p_part_num_rec.attribute8;
1357 ELSIF p_column_name = 'ATTRIBUTE9' THEN
1358 p_dff_usage_record.attribute9 := FND_API.G_TRUE;
1359 RETURN p_part_num_rec.attribute9;
1360 ELSIF p_column_name = 'ATTRIBUTE10' THEN
1361 p_dff_usage_record.attribute10 := FND_API.G_TRUE;
1362 RETURN p_part_num_rec.attribute10;
1363 ELSIF p_column_name = 'ATTRIBUTE11' THEN
1364 p_dff_usage_record.attribute11 := FND_API.G_TRUE;
1365 RETURN p_part_num_rec.attribute11;
1366 ELSIF p_column_name = 'ATTRIBUTE12' THEN
1367 p_dff_usage_record.attribute12 := FND_API.G_TRUE;
1368 RETURN p_part_num_rec.attribute12;
1369 ELSIF p_column_name = 'ATTRIBUTE13' THEN
1370 p_dff_usage_record.attribute13 := FND_API.G_TRUE;
1371 RETURN p_part_num_rec.attribute13;
1372 ELSIF p_column_name = 'ATTRIBUTE14' THEN
1373 p_dff_usage_record.attribute14 := FND_API.G_TRUE;
1374 RETURN p_part_num_rec.attribute14;
1375 ELSIF p_column_name = 'ATTRIBUTE15' THEN
1376 p_dff_usage_record.attribute15 := FND_API.G_TRUE;
1377 RETURN p_part_num_rec.attribute15;
1378 ELSE
1379 RETURN NULL;
1380 END IF;
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 RETURN NULL;
1384 END getAttrValue;
1385
1386 PROCEDURE performDFFValidation (p_data_set_id IN NUMBER
1387 ,p_perform_security_check IN BOOLEAN
1388 ,x_return_status OUT NOCOPY VARCHAR2
1389 ,x_msg_count OUT NOCOPY NUMBER
1390 ,x_msg_data OUT NOCOPY VARCHAR2
1391 ) IS
1392 -- REFERENCE FROM FND_DFLEX specification
1393 -- TYPE dflex_r IS RECORD
1394 -- (application_id fnd_application.application_id%TYPE,
1395 -- flexfield_name fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE);
1396 l_dflex_r fnd_dflex.dflex_r;
1397
1398 -- REFERENCE FROM FND_DFLEX specification
1399 -- TYPE dflex_dr IS RECORD
1400 -- (title fnd_descriptive_flexs_vl.title%TYPE,
1401 -- table_name fnd_descriptive_flexs_vl.application_table_name%TYPE,
1402 -- table_app fnd_application.application_short_name%TYPE,
1403 -- description fnd_descriptive_flexs_vl.description%TYPE,
1404 -- segment_delimeter fnd_descriptive_flexs_vl.concatenated_segment_delimiter%TYPE,
1405 -- default_context_field fnd_descriptive_flexs_vl.default_context_field_name%TYPE,
1406 -- default_context_value fnd_descriptive_flexs_vl.default_context_value%TYPE,
1407 -- protected_flag fnd_descriptive_flexs_vl.protected_flag%TYPE,
1408 -- form_context_prompt fnd_descriptive_flexs_vl.form_context_prompt%TYPE,
1409 -- context_column_name fnd_descriptive_flexs_vl.context_column_name%TYPE);
1410 l_dflex_dr fnd_dflex.dflex_dr;
1411
1412 -- REFERENCE FROM FND_DFLEX specification
1413 -- TYPE context_r IS RECORD
1414 -- (flexfield dflex_r
1415 -- ,context_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE);
1416 l_global_ctx_r fnd_dflex.context_r;
1417 l_dff_ctx_r fnd_dflex.context_r;
1418
1419 -- REFERENCE FROM FND_DFLEX specification
1420 -- TYPE context_code_a IS TABLE OF
1421 -- fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE
1422 -- INDEX BY BINARY_INTEGER;
1423 -- TYPE context_name_a IS TABLE OF
1424 -- fnd_descr_flex_contexts_vl.descriptive_flex_context_name%TYPE
1425 -- INDEX BY BINARY_INTEGER;
1426 -- TYPE context_description_a IS TABLE OF
1427 -- fnd_descr_flex_contexts_vl.description%TYPE
1428 -- INDEX BY BINARY_INTEGER;
1429 -- TYPE boolean_a IS TABLE OF
1430 -- BOOLEAN
1431 -- INDEX BY BINARY_INTEGER;
1432 l_tbl_ctx_code fnd_dflex.context_code_a;
1433 l_tbl_ctx_is_global fnd_dflex.boolean_a;
1434 -- REFERENCE FROM FND_DFLEX specification
1435 -- TYPE contexts_dr IS RECORD
1436 -- (ncontexts BINARY_INTEGER,
1437 -- global_context BINARY_INTEGER,
1438 -- context_code context_code_a,
1439 -- context_name context_name_a,
1440 -- context_description context_description_a,
1441 -- is_enabled boolean_a,
1442 -- is_global boolean_a);
1443 l_ctx_dr fnd_dflex.contexts_dr;
1444
1445 -- REFERENCE FROM FND_DFLEX specification
1446 -- TYPE segment_description_a IS TABLE OF
1447 -- fnd_descr_flex_col_usage_vl.description%TYPE
1448 -- INDEX BY BINARY_INTEGER;
1449 -- TYPE application_column_name_a IS TABLE OF
1450 -- fnd_descr_flex_col_usage_vl.application_column_name%TYPE
1451 -- INDEX BY BINARY_INTEGER;
1452 -- TYPE segment_name_a IS TABLE OF
1453 -- fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE
1454 -- INDEX BY BINARY_INTEGER;
1455 -- TYPE sequence_a IS TABLE OF
1456 -- fnd_descr_flex_col_usage_vl.column_seq_num%TYPE
1457 -- INDEX BY BINARY_INTEGER;
1458 -- TYPE display_size_a IS TABLE OF
1459 -- fnd_descr_flex_col_usage_vl.display_size%TYPE
1460 -- INDEX BY BINARY_INTEGER;
1461 -- TYPE row_prompt_a IS TABLE OF
1462 -- fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE
1463 -- INDEX BY BINARY_INTEGER;
1464 -- TYPE column_prompt_a IS TABLE OF
1465 -- fnd_descr_flex_col_usage_vl.form_above_prompt%TYPE
1466 -- INDEX BY BINARY_INTEGER;
1467 -- TYPE value_set_a IS TABLE OF
1468 -- fnd_descr_flex_col_usage_vl.flex_value_set_id%TYPE
1469 -- INDEX BY BINARY_INTEGER;
1470 -- TYPE default_type_a IS TABLE OF
1471 -- fnd_descr_flex_col_usage_vl.default_type%TYPE
1472 -- INDEX BY BINARY_INTEGER;
1473 -- TYPE default_value_a IS TABLE OF
1474 -- fnd_descr_flex_col_usage_vl.default_value%TYPE
1475 -- INDEX BY BINARY_INTEGER;
1476
1477 -- REFERENCE FROM FND_DFLEX specification
1478 -- TYPE segments_dr IS RECORD
1479 -- (nsegments BINARY_INTEGER,
1480 -- application_column_name application_column_name_a,
1481 -- segment_name segment_name_a,
1482 -- sequence sequence_a,
1483 -- is_displayed boolean_a,
1484 -- display_size display_size_a,
1485 -- row_prompt row_prompt_a,
1486 -- column_prompt column_prompt_a,
1487 -- is_enabled boolean_a,
1488 -- is_required boolean_a,
1489 -- description segment_description_a,
1490 -- value_set value_set_a,
1491 -- default_type default_type_a,
1492 -- default_value default_value_a);
1493 l_global_seg_dr fnd_dflex.segments_dr;
1494 l_dff_seg_dr fnd_dflex.segments_dr;
1495
1496 l_std_ctx_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1497 l_dff_ctx_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1498
1499 l_last_attribute_category ego_aml_intf.attribute_category%TYPE;
1500 l_global_data_elements ego_aml_intf.attribute_category%TYPE;
1501 l_global_ctx_index NUMBER;
1502
1503 l_dff_fields_used G_MTL_DFF_ATTRIBUTES_REC;
1504
1505 l_api_name VARCHAR2(30);
1506 l_count NUMBER;
1507
1508 CURSOR c_item_records (cp_data_set_id IN NUMBER) IS
1509 SELECT *
1510 FROM ego_aml_intf
1511 WHERE data_set_id = cp_data_set_id
1512 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1513 AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
1514 ,EGO_ITEM_PUB.G_TTYPE_UPDATE
1515 )
1516 ORDER BY attribute_category desc
1517 FOR UPDATE OF transaction_id;
1518
1519 TYPE DYNAMIC_CUR IS REF CURSOR;
1520 c_err_cursor DYNAMIC_CUR;
1521
1522 BEGIN
1523 x_return_status := FND_API.G_RET_STS_SUCCESS;
1524 x_msg_count := 0;
1525 x_msg_data := NULL;
1526 l_api_name := 'performDFFValidation';
1527 --
1528 -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
1529 -- records will be ended with process_flag = G_PS_DFF_VAL_COMPLETE
1530 --
1531 -- check if there are any records for which DFF is input
1532 --
1533 SELECT COUNT(*)
1534 INTO l_count
1535 FROM ego_aml_intf
1536 WHERE data_set_id = p_data_set_id
1537 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1538 AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
1539 ,EGO_ITEM_PUB.G_TTYPE_UPDATE
1540 )
1541 AND ( attribute_category IS NOT NULL
1542 OR attribute1 IS NOT NULL
1543 OR attribute2 IS NOT NULL
1544 OR attribute3 IS NOT NULL
1545 OR attribute4 IS NOT NULL
1546 OR attribute5 IS NOT NULL
1547 OR attribute6 IS NOT NULL
1548 OR attribute7 IS NOT NULL
1549 OR attribute8 IS NOT NULL
1550 OR attribute9 IS NOT NULL
1551 OR attribute10 IS NOT NULL
1552 OR attribute11 IS NOT NULL
1553 OR attribute12 IS NOT NULL
1554 OR attribute13 IS NOT NULL
1555 OR attribute14 IS NOT NULL
1556 OR attribute15 IS NOT NULL
1557 );
1558 IF l_count <> 0 THEN
1559 -- validate the flex field
1560 FND_DFLEX.get_flexfield
1561 (appl_short_name => 'INV'
1562 ,flexfield_name => 'MTL_MFG_PART_NUMBERS'
1563 ,flexfield => l_dflex_r
1564 ,flexinfo => l_dflex_dr
1565 );
1566 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1567 ,p_module => l_api_name
1568 ,p_message => 'Call to FND_DFLEX.get_flexfield complete'
1569 );
1570 --
1571 -- get the contexts defined for the flex field.
1572 --
1573 FND_DFLEX.get_contexts
1574 (flexfield => l_dflex_r
1575 ,contexts => l_ctx_dr
1576 );
1577 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1578 ,p_module => l_api_name
1579 ,p_message => 'Call to FND_DFLEX.get_contexts complete'
1580 );
1581 resetDFFFieldUsage (p_dff_usage_record => l_dff_fields_used);
1582 l_tbl_ctx_is_global := l_ctx_dr.is_global;
1583 IF (l_tbl_ctx_is_global.COUNT > 0) THEN
1584 --
1585 -- context fields defined
1586 --
1587 FOR i IN l_tbl_ctx_is_global.FIRST .. l_tbl_ctx_is_global.LAST LOOP
1588 l_global_ctx_index := i;
1589 EXIT WHEN l_tbl_ctx_is_global(i);
1590 END LOOP;
1591 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1592 ,p_module => l_api_name
1593 ,p_message => 'Identified Global DFF at loc '
1594 ||l_global_ctx_index||' in context table'
1595 );
1596 l_dff_ctx_r.flexfield := l_dflex_r;
1597 l_global_ctx_r.flexfield := l_dflex_r;
1598 l_global_ctx_r.context_code := l_ctx_dr.context_code(l_global_ctx_index);
1599 l_last_attribute_category := l_global_ctx_r.context_code;
1600 l_global_data_elements := l_global_ctx_r.context_code;
1601
1602 FND_DFLEX.get_segments
1603 (context => l_global_ctx_r
1604 ,segments => l_global_seg_dr
1605 ,enabled_only => TRUE
1606 );
1607 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1608 ,p_module => l_api_name
1609 ,p_message => 'Identified Segments associated with Global DFF'
1610 );
1611
1612 FOR cr in c_item_records (cp_data_set_id => p_data_set_id) LOOP
1613 resetDFFFieldUsage (p_dff_usage_record => l_dff_fields_used);
1614 IF NVL(cr.attribute_category,l_last_attribute_category)
1615 <> l_last_attribute_category THEN
1616 -- attribute category has changed get the dff record
1617 l_tbl_ctx_code := l_ctx_dr.context_code;
1618 FOR i IN l_tbl_ctx_code.FIRST .. l_tbl_ctx_code.LAST LOOP
1619 IF l_tbl_ctx_code(i) = cr.attribute_category THEN
1620 l_last_attribute_category := cr.attribute_category;
1621 l_dff_ctx_r.context_code := cr.attribute_category;
1622 FND_DFLEX.get_segments
1623 (context => l_dff_ctx_r
1624 ,segments => l_dff_seg_dr
1625 ,enabled_only => TRUE
1626 );
1627 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1628 ,p_module => l_api_name
1629 ,p_message => 'Identified Segments for '
1630 || cr.attribute_category
1631 );
1632 EXIT; -- for loop
1633 END IF;
1634 END LOOP;
1635 END IF;
1636 -- global segments are available at l_global_seg_dr
1637 -- dff segments are available at l_dff_seg_dr
1638 FND_FLEX_DESCVAL.set_context_value(l_last_attribute_category);
1639 IF l_global_seg_dr.application_column_name.COUNT > 0 THEN
1640 FOR i IN l_global_seg_dr.application_column_name.FIRST ..
1641 l_global_seg_dr.application_column_name.LAST LOOP
1642 fnd_flex_descval.set_column_value
1643 (l_global_seg_dr.application_column_name(i)
1644 ,getAttrValue
1645 (p_part_num_rec => cr
1646 ,p_column_name => l_global_seg_dr.application_column_name(i)
1647 ,p_dff_usage_record => l_dff_fields_used
1648 )
1649 );
1650 END LOOP;
1651 END IF;
1652 IF l_last_attribute_category <> l_global_data_elements THEN
1653 FOR i IN l_dff_seg_dr.application_column_name.FIRST ..
1654 l_dff_seg_dr.application_column_name.LAST LOOP
1655 fnd_flex_descval.set_column_value
1656 (l_dff_seg_dr.application_column_name(i)
1657 ,getAttrValue
1658 (p_part_num_rec => cr
1659 ,p_column_name => l_dff_seg_dr.application_column_name(i)
1660 ,p_dff_usage_record => l_dff_fields_used
1661 )
1662 );
1663 END LOOP;
1664 END IF;
1665 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1666 ,p_module => l_api_name
1667 ,p_message => 'Calling FND_FLEX_DESCVAL.validate_desccols'
1668 );
1669 IF NOT FND_FLEX_DESCVAL.validate_desccols
1670 (appl_short_name => 'INV'
1671 ,desc_flex_name => 'MTL_MFG_PART_NUMBERS'
1672 -- ,values_or_ids IN VARCHAR2 DEFAULT 'I'
1673 -- ,validation_date IN DATE DEFAULT SYSDATE
1674 -- ,enabled_activation IN BOOLEAN DEFAULT TRUE
1675 -- ,resp_appl_id IN NUMBER DEFAULT NULL
1676 -- ,resp_id IN NUMBER DEFAULT NULL
1677 ) THEN
1678 UPDATE ego_aml_intf
1679 SET process_flag = G_PS_DFF_INVALID,
1680 prog_int_char2 = fnd_flex_descval.error_message
1681 WHERE CURRENT OF c_item_records;
1682 ELSE
1683 -- set to NULL all other DFF fields which are not defined
1684 UPDATE ego_aml_intf intf
1685 SET attribute1 =
1686 (SELECT CASE WHEN l_dff_fields_used.attribute1 = FND_API.G_TRUE
1687 THEN intf.attribute1 ELSE NULL END
1688 FROM DUAL)
1689 ,attribute2 =
1690 (SELECT CASE WHEN l_dff_fields_used.attribute2 = FND_API.G_TRUE
1691 THEN intf.attribute2 ELSE NULL END
1692 FROM DUAL)
1693 ,attribute3 =
1694 (SELECT CASE WHEN l_dff_fields_used.attribute3 = FND_API.G_TRUE
1695 THEN intf.attribute3 ELSE NULL END
1696 FROM DUAL)
1697 ,attribute4 =
1698 (SELECT CASE WHEN l_dff_fields_used.attribute4 = FND_API.G_TRUE
1699 THEN intf.attribute4 ELSE NULL END
1700 FROM DUAL)
1701 ,attribute5 =
1702 (SELECT CASE WHEN l_dff_fields_used.attribute5 = FND_API.G_TRUE
1703 THEN intf.attribute5 ELSE NULL END
1704 FROM DUAL)
1705 ,attribute6 =
1706 (SELECT CASE WHEN l_dff_fields_used.attribute6 = FND_API.G_TRUE
1707 THEN intf.attribute6 ELSE NULL END
1708 FROM DUAL)
1709 ,attribute7 =
1710 (SELECT CASE WHEN l_dff_fields_used.attribute7 = FND_API.G_TRUE
1711 THEN intf.attribute7 ELSE NULL END
1712 FROM DUAL)
1713 ,attribute8 =
1714 (SELECT CASE WHEN l_dff_fields_used.attribute8 = FND_API.G_TRUE
1715 THEN intf.attribute8 ELSE NULL END
1716 FROM DUAL)
1717 ,attribute9 =
1718 (SELECT CASE WHEN l_dff_fields_used.attribute9 = FND_API.G_TRUE
1719 THEN intf.attribute9 ELSE NULL END
1720 FROM DUAL)
1721 ,attribute10 =
1722 (SELECT CASE WHEN l_dff_fields_used.attribute10 = FND_API.G_TRUE
1723 THEN intf.attribute10 ELSE NULL END
1724 FROM DUAL)
1725 ,attribute11 =
1726 (SELECT CASE WHEN l_dff_fields_used.attribute11 = FND_API.G_TRUE
1727 THEN intf.attribute11 ELSE NULL END
1728 FROM DUAL)
1729 ,attribute12 =
1730 (SELECT CASE WHEN l_dff_fields_used.attribute12 = FND_API.G_TRUE
1731 THEN intf.attribute12 ELSE NULL END
1732 FROM DUAL)
1733 ,attribute13 =
1734 (SELECT CASE WHEN l_dff_fields_used.attribute13 = FND_API.G_TRUE
1735 THEN intf.attribute13 ELSE NULL END
1736 FROM DUAL)
1737 ,attribute14 =
1738 (SELECT CASE WHEN l_dff_fields_used.attribute14 = FND_API.G_TRUE
1739 THEN intf.attribute14 ELSE NULL END
1740 FROM DUAL)
1741 ,attribute15 =
1742 (SELECT CASE WHEN l_dff_fields_used.attribute15 = FND_API.G_TRUE
1743 THEN intf.attribute15 ELSE NULL END
1744 FROM DUAL)
1745 WHERE CURRENT OF c_item_records;
1746 END IF;
1747 END LOOP;
1748 ELSE
1749 -- no context fields defined.
1750 UPDATE ego_aml_intf
1751 SET process_flag = G_PS_DFF_INVALID
1752 WHERE data_set_id = p_data_set_id
1753 AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1754 AND (attribute1 IS NOT NULL
1755 OR
1756 attribute2 IS NOT NULL
1757 OR
1758 attribute3 IS NOT NULL
1759 OR
1760 attribute4 IS NOT NULL
1761 OR
1762 attribute5 IS NOT NULL
1763 OR
1764 attribute6 IS NOT NULL
1765 OR
1766 attribute7 IS NOT NULL
1767 OR
1768 attribute8 IS NOT NULL
1769 OR
1770 attribute9 IS NOT NULL
1771 OR
1772 attribute10 IS NOT NULL
1773 OR
1774 attribute11 IS NOT NULL
1775 OR
1776 attribute12 IS NOT NULL
1777 OR
1778 attribute13 IS NOT NULL
1779 OR
1780 attribute14 IS NOT NULL
1781 OR
1782 attribute15 IS NOT NULL
1783 );
1784 END IF;
1785 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1786 ,p_module => l_api_name
1787 ,p_message => ' DFF validation complete'
1788 );
1789 ELSE
1790 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1791 ,p_module => l_api_name
1792 ,p_message => 'No DFF fields are present for validation'
1793 );
1794 END IF; -- there exists some records with DFF fields
1795 UPDATE ego_aml_intf
1796 SET process_flag = G_PS_DFF_VAL_COMPLETE
1797 WHERE data_set_id = p_data_set_id
1798 AND process_flag = G_PS_VAL_TO_ID_COMPLETE;
1799
1800 EXCEPTION
1801 WHEN OTHERS THEN
1802 IF c_item_records%ISOPEN THEN
1803 CLOSE c_item_records;
1804 END IF;
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 x_msg_count := 1;
1807 -- for SQL errors
1808 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
1809 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
1810 FND_MESSAGE.Set_Token('API_NAME', 'performDFFValidation');
1811 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
1812 x_msg_data := FND_MESSAGE.get();
1813 END performDFFValidation;
1814
1815 PROCEDURE populateProductionTable (p_data_set_id IN NUMBER
1816 ,x_return_status OUT NOCOPY VARCHAR2
1817 ,x_msg_count OUT NOCOPY NUMBER
1818 ,x_msg_data OUT NOCOPY VARCHAR2
1819 ) IS
1820 l_api_name VARCHAR2(30);
1821 BEGIN
1822 x_return_status := FND_API.G_RET_STS_SUCCESS;
1823 x_msg_count := 0;
1824 x_msg_data := NULL;
1825 l_api_name := 'populateProductionTable';
1826 INSERT INTO mtl_mfg_part_numbers
1827 (manufacturer_id
1828 ,mfg_part_num
1829 ,inventory_item_id
1830 ,organization_id
1831 ,mrp_planning_code
1832 ,description
1833 ,attribute_category
1834 ,attribute1
1835 ,attribute2
1836 ,attribute3
1837 ,attribute4
1838 ,attribute5
1839 ,attribute6
1840 ,attribute7
1841 ,attribute8
1842 ,attribute9
1843 ,attribute10
1844 ,attribute11
1845 ,attribute12
1846 ,attribute13
1847 ,attribute14
1848 ,attribute15
1849 ,first_article_status
1850 ,approval_status
1851 ,start_date
1852 ,end_date
1853 ,request_id
1854 ,creation_date
1855 ,created_by
1856 ,last_update_date
1857 ,last_updated_by
1858 ,last_update_login)
1859 SELECT
1860 manufacturer_id
1861 ,mfg_part_num
1862 ,inventory_item_id
1863 ,organization_id
1864 ,mrp_planning_code
1865 ,description
1866 ,attribute_category
1867 ,attribute1
1868 ,attribute2
1869 ,attribute3
1870 ,attribute4
1871 ,attribute5
1872 ,attribute6
1873 ,attribute7
1874 ,attribute8
1875 ,attribute9
1876 ,attribute10
1877 ,attribute11
1878 ,attribute12
1879 ,attribute13
1880 ,attribute14
1881 ,attribute15
1882 ,first_article_status
1883 ,approval_status
1884 ,start_date
1885 ,end_date
1886 ,request_id
1887 ,G_SYSDATE
1888 ,G_USER_ID
1889 ,G_SYSDATE
1890 ,G_USER_ID
1891 ,G_LOGIN_ID
1892 FROM ego_aml_intf
1893 WHERE data_set_id = p_data_set_id
1894 AND process_flag = G_PS_DFF_VAL_COMPLETE
1895 AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE;
1896 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1897 ,p_module => l_api_name
1898 ,p_message => 'Insert into production table done'
1899 );
1900
1901 UPDATE mtl_mfg_part_numbers prod SET
1902 (mrp_planning_code
1903 ,description
1904 ,attribute_category
1905 ,attribute1
1906 ,attribute2
1907 ,attribute3
1908 ,attribute4
1909 ,attribute5
1910 ,attribute6
1911 ,attribute7
1912 ,attribute8
1913 ,attribute9
1914 ,attribute10
1915 ,attribute11
1916 ,attribute12
1917 ,attribute13
1918 ,attribute14
1919 ,attribute15
1920 ,first_article_status
1921 ,approval_status
1922 ,start_date
1923 ,end_date
1924 ,request_id
1925 ,last_update_date
1926 ,last_updated_by
1927 ,last_update_login)
1928 = (SELECT intf.mrp_planning_code
1929 ,intf.description
1930 ,intf.attribute_category
1931 ,intf.attribute1
1932 ,intf.attribute2
1933 ,intf.attribute3
1934 ,intf.attribute4
1935 ,intf.attribute5
1936 ,intf.attribute6
1937 ,intf.attribute7
1938 ,intf.attribute8
1939 ,intf.attribute9
1940 ,intf.attribute10
1941 ,intf.attribute11
1942 ,intf.attribute12
1943 ,intf.attribute13
1944 ,intf.attribute14
1945 ,intf.attribute15
1946 ,intf.first_article_status
1947 ,intf.approval_status
1948 ,intf.start_date
1949 ,intf.end_date
1950 ,intf.request_id
1951 ,G_SYSDATE
1952 ,G_USER_ID
1953 ,G_LOGIN_ID
1954 FROM ego_aml_intf intf
1955 WHERE intf.data_set_id = p_data_set_id
1956 AND intf.process_flag = G_PS_DFF_VAL_COMPLETE
1957 AND intf.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
1958 AND intf.inventory_item_id = prod.inventory_item_id
1959 AND intf.organization_id = prod.organization_id
1960 AND intf.manufacturer_id = prod.manufacturer_id
1961 AND intf.mfg_part_num = prod.mfg_part_num
1962 )
1963 WHERE EXISTS (select 1
1964 from ego_aml_intf intf1
1965 where intf1.data_set_id = p_data_set_id
1966 and intf1.process_flag = G_PS_DFF_VAL_COMPLETE
1967 and intf1.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
1968 and intf1.inventory_item_id = prod.inventory_item_id
1969 and intf1.organization_id = prod.organization_id
1970 and intf1.manufacturer_id = prod.manufacturer_id
1971 and intf1.mfg_part_num = prod.mfg_part_num
1972 );
1973 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1974 ,p_module => l_api_name
1975 ,p_message => 'Update into production table done'
1976 );
1977
1978 DELETE mtl_mfg_part_numbers prod
1979 WHERE EXISTS
1980 (Select 1
1981 From ego_aml_intf intf
1982 Where intf.data_set_id = p_data_set_id
1983 and intf.process_flag = G_PS_DFF_VAL_COMPLETE
1984 and intf.transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE
1985 and intf.inventory_item_id = prod.inventory_item_id
1986 and intf.organization_id = prod.organization_id
1987 and intf.manufacturer_id = prod.manufacturer_id
1988 and intf.mfg_part_num = prod.mfg_part_num
1989 );
1990 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1991 ,p_module => l_api_name
1992 ,p_message => ' Deletion of items from production table done'
1993 );
1994
1995 UPDATE ego_aml_intf
1996 SET process_flag = G_PS_SUCCESS
1997 WHERE data_set_id = p_data_set_id
1998 AND process_flag = G_PS_DFF_VAL_COMPLETE;
1999
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2003 x_msg_count := 1;
2004 -- for SQL errors
2005 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2006 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2007 FND_MESSAGE.Set_Token('API_NAME', 'populateProductionTable');
2008 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2009 x_msg_data := FND_MESSAGE.get();
2010 END populateProductionTable;
2011
2012
2013 --
2014 -- Log Error Messages
2015 --
2016 FUNCTION Log_Errors_Now (p_data_set_id IN NUMBER
2017 ,x_return_status OUT NOCOPY VARCHAR2
2018 ,x_msg_count OUT NOCOPY NUMBER
2019 ,x_msg_data OUT NOCOPY VARCHAR2
2020 )
2021 RETURN BOOLEAN IS
2022
2023 CURSOR c_err_records (cp_data_set_id IN NUMBER) IS
2024 SELECT *
2025 FROM ego_aml_intf
2026 WHERE data_set_id = cp_data_set_id
2027 AND process_flag > G_PS_SUCCESS;
2028
2029 l_dummy_message fnd_new_messages.message_text%TYPE;
2030 l_application_context VARCHAR2(3);
2031 l_err_token_table ERROR_HANDLER.Token_Tbl_Type;
2032 l_err_msg_name VARCHAR2(99);
2033 l_message_type VARCHAR2(9);
2034 l_entity_index NUMBER;
2035 l_intf_table_name VARCHAR2(99);
2036 l_entity_code VARCHAR2(99);
2037 l_add_to_error_stack VARCHAR2(99);
2038 l_message_has_token BOOLEAN;
2039 l_api_name VARCHAR2(30);
2040
2041 BEGIN
2042 x_return_status := FND_API.G_RET_STS_SUCCESS;
2043 x_msg_count := 0;
2044 x_msg_data := NULL;
2045 l_api_name := 'Log_Errors_Now';
2046 l_application_context := 'EGO';
2047 l_message_type := FND_API.G_RET_STS_ERROR;
2048 -- this takes precedence over entity id
2049 l_entity_index := 0;
2050 l_intf_table_name := 'EGO_AML_INTF';
2051 l_message_has_token := FALSE;
2052 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2053 ,p_module => l_api_name
2054 ,p_message => 'Started processing errors'
2055 );
2056
2057 -- populate the interface table which uses tokens
2058 UPDATE ego_aml_intf aml_intf
2059 SET manufacturer_name =
2060 (Select manufacturer_name
2061 from mtl_manufacturers manu
2062 where manu.manufacturer_id = aml_intf.manufacturer_id)
2063 WHERE data_set_id = p_data_set_id
2064 AND manufacturer_id IS NOT NULL
2065 AND process_flag IN (G_PS_CREATE_REC_EXISTS
2066 ,G_PS_REC_NOT_EXISTS
2067 ,G_PS_DUP_INTF_RECORDS
2068 );
2069
2070 UPDATE ego_aml_intf aml_intf
2071 SET organization_code =
2072 (Select organization_code
2073 from mtl_parameters mp
2074 where mp.organization_id = aml_intf.organization_id)
2075 WHERE data_set_id = p_data_set_id
2076 AND organization_id IS NOT NULL
2077 AND process_flag IN (G_PS_ITEM_ERR
2078 ,G_PS_CREATE_REC_EXISTS
2079 ,G_PS_REC_NOT_EXISTS
2080 ,G_PS_DUP_INTF_RECORDS
2081 ,G_PS_CHANGE_NOT_ALLOWED
2082 ,G_PS_NO_AML_PRIV
2083 );
2084
2085 FOR error_rec IN c_err_records (cp_data_set_id => p_data_set_id) LOOP
2086 l_entity_index := l_entity_index+1;
2087 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2088 ,p_module => l_api_name
2089 ,p_message => 'Error at row '||error_rec.transaction_id||
2090 ' with status '||error_rec.process_flag
2091 );
2092 IF error_rec.process_flag = G_PS_MAND_PARAM_MISSING THEN
2093 l_err_msg_name := 'EGO_INTF_MAND_PARAM_MISSING';
2094 ELSIF error_rec.process_flag = G_PS_INVALID_TRANS_TYPE THEN
2095 l_message_has_token := TRUE;
2096 fnd_message.set_name('EGO', 'EGO_TRANSACTION_TYPE');
2097 l_dummy_message := fnd_message.get();
2098 l_err_msg_name := 'EGO_IPI_INVALID_VALUE';
2099 l_err_token_table(1).TOKEN_NAME := 'NAME';
2100 l_err_token_table(1).TOKEN_VALUE := l_err_msg_name;
2101 l_err_token_table(2).TOKEN_NAME := 'VALUE';
2102 l_err_token_table(2).TOKEN_VALUE := error_rec.transaction_type;
2103 ELSIF error_rec.process_flag = G_PS_SD_GT_ED_ERROR THEN
2104 l_err_msg_name := 'EGO_STARTDATE_PRECEDES_ENDDATE';
2105 ELSIF error_rec.process_flag = G_PS_FA_STATUS_ERR THEN
2106 l_message_has_token := TRUE;
2107 fnd_message.set_name('EGO', 'EGO_FIRST_ARTICLE_STATUS');
2108 l_dummy_message := fnd_message.get();
2109 l_err_msg_name := 'EGO_IPI_INVALID_VALUE';
2110 l_err_token_table(1).TOKEN_NAME := 'NAME';
2111 l_err_token_table(1).TOKEN_VALUE := l_dummy_message;
2112 l_err_token_table(2).TOKEN_NAME := 'VALUE';
2113 IF error_rec.first_article_status IS NOT NULL THEN
2114 l_err_token_table(2).TOKEN_VALUE := error_rec.first_article_status;
2115 ELSE
2116 l_err_token_table(2).TOKEN_VALUE
2117 := error_rec.first_article_status_meaning;
2118 END IF;
2119 ELSIF error_rec.process_flag = G_PS_APPROVAL_STATUS_ERR THEN
2120 l_message_has_token := TRUE;
2121 fnd_message.set_name('EGO', 'EGO_APPROVAL_STATUS');
2122 l_dummy_message := fnd_message.get();
2123 l_err_msg_name := 'EGO_IPI_INVALID_VALUE';
2124 l_err_token_table(1).TOKEN_NAME := 'NAME';
2125 l_err_token_table(1).TOKEN_VALUE := l_dummy_message;
2126 l_err_token_table(2).TOKEN_NAME := 'VALUE';
2127 IF error_rec.approval_status IS NOT NULL THEN
2128 l_err_token_table(2).TOKEN_VALUE := error_rec.approval_status;
2129 ELSE
2130 l_err_token_table(2).TOKEN_VALUE := error_rec.approval_status_meaning;
2131 END IF;
2132 ELSIF error_rec.process_flag = G_PS_MANUFACTURER_ERR THEN
2133 l_message_has_token := TRUE;
2134 fnd_message.set_name('EGO', 'EGO_MFG');
2135 l_dummy_message := fnd_message.get();
2136 l_err_msg_name := 'EGO_IPI_INVALID_VALUE';
2137 l_err_token_table(1).TOKEN_NAME := 'NAME';
2138 l_err_token_table(1).TOKEN_VALUE := l_dummy_message;
2139 l_err_token_table(2).TOKEN_NAME := 'VALUE';
2140 IF error_rec.manufacturer_id IS NOT NULL THEN
2141 l_err_token_table(2).TOKEN_VALUE := error_rec.manufacturer_id;
2142 ELSE
2143 l_err_token_table(2).TOKEN_VALUE := error_rec.manufacturer_name;
2144 END IF;
2145 ELSIF error_rec.process_flag = G_PS_ORGANIZATION_ERR THEN
2146 l_message_has_token := TRUE;
2147 fnd_message.set_name('EGO', 'EGO_ORGANIZATION');
2148 l_dummy_message := fnd_message.get();
2149 l_err_msg_name := 'EGO_IPI_INVALID_VALUE';
2150 l_err_token_table(1).TOKEN_NAME := 'NAME';
2151 l_err_token_table(1).TOKEN_VALUE := l_dummy_message;
2152 l_err_token_table(2).TOKEN_NAME := 'VALUE';
2153 IF error_rec.organization_id IS NOT NULL THEN
2154 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_id;
2155 ELSE
2156 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_code;
2157 END IF;
2158 ELSIF error_rec.process_flag = G_PS_NOT_MASTER_ORG_ERR THEN
2159 l_message_has_token := TRUE;
2160 l_err_msg_name := 'EGO_AML_NOT_MASTER_ORG';
2161 l_err_token_table(1).TOKEN_NAME := 'ORGANIZATION';
2162 IF error_rec.organization_id IS NOT NULL THEN
2163 l_err_token_table(1).TOKEN_VALUE := error_rec.organization_id;
2164 ELSE
2165 l_err_token_table(1).TOKEN_VALUE := error_rec.organization_code;
2166 END IF;
2167 ELSIF error_rec.process_flag = G_PS_ITEM_ERR THEN
2168 l_message_has_token := TRUE;
2169 IF error_rec.inventory_item_id IS NOT NULL THEN
2170 l_err_msg_name := 'EGO_ITEMID_NOTASSGN_TO_ORGID';
2171 l_err_token_table(1).TOKEN_NAME := 'ITEM_ID';
2172 l_err_token_table(1).TOKEN_VALUE := error_rec.inventory_item_id;
2173 l_err_token_table(2).TOKEN_NAME := 'ORG_ID';
2174 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_id;
2175 ELSE
2176 l_err_msg_name := 'EGO_ITEM_NOTASSGN_TO_ORG';
2177 l_err_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2178 l_err_token_table(1).TOKEN_VALUE := error_rec.item_number;
2179 l_err_token_table(2).TOKEN_NAME := 'ORG_CODE';
2180 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_code;
2181 END IF;
2182 ELSIF error_rec.process_flag = G_PS_CREATE_REC_EXISTS THEN
2183 l_message_has_token := TRUE;
2184 l_err_msg_name := 'EGO_MPN_EXISTS';
2185 l_err_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2186 l_err_token_table(1).TOKEN_VALUE := error_rec.item_number;
2187 l_err_token_table(2).TOKEN_NAME := 'ORG_CODE';
2188 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_code;
2189 l_err_token_table(3).TOKEN_NAME := 'MFG_PART_NUM';
2190 l_err_token_table(3).TOKEN_VALUE := error_rec.mfg_part_num;
2191 l_err_token_table(4).TOKEN_NAME := 'MFG';
2192 l_err_token_table(4).TOKEN_VALUE := error_rec.manufacturer_name;
2193 ELSIF error_rec.process_flag = G_PS_REC_NOT_EXISTS THEN
2194 l_message_has_token := TRUE;
2195 IF error_rec.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
2196 l_err_msg_name := 'EGO_MPN_NOT_EXISTS_UPDATE';
2197 ELSE
2198 l_err_msg_name := 'EGO_MPN_NOT_EXISTS_DELETE';
2199 END IF;
2200 l_err_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2201 l_err_token_table(1).TOKEN_VALUE := error_rec.item_number;
2202 l_err_token_table(2).TOKEN_NAME := 'ORG_CODE';
2203 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_code;
2204 l_err_token_table(3).TOKEN_NAME := 'MFG_PART_NUM';
2205 l_err_token_table(3).TOKEN_VALUE := error_rec.mfg_part_num;
2206 l_err_token_table(4).TOKEN_NAME := 'MFG';
2207 l_err_token_table(4).TOKEN_VALUE := error_rec.manufacturer_name;
2208 ELSIF error_rec.process_flag = G_PS_DUP_INTF_RECORDS THEN
2209 l_message_has_token := TRUE;
2210 l_err_msg_name := 'EGO_MPN_INTF_DUP_REC_EXISTS';
2211 l_err_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2212 l_err_token_table(1).TOKEN_VALUE := error_rec.item_number;
2213 l_err_token_table(2).TOKEN_NAME := 'ORG_CODE';
2214 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_code;
2215 l_err_token_table(3).TOKEN_NAME := 'MFG_PART_NUM';
2216 l_err_token_table(3).TOKEN_VALUE := error_rec.mfg_part_num;
2217 l_err_token_table(4).TOKEN_NAME := 'MFG';
2218 l_err_token_table(4).TOKEN_VALUE := error_rec.manufacturer_name;
2219 ELSIF error_rec.process_flag = G_PS_CHANGE_NOT_ALLOWED THEN
2220 l_message_has_token := TRUE;
2221 SELECT name
2222 INTO l_dummy_message
2223 FROM pa_ego_phases_v
2224 WHERE proj_element_id = error_rec.prog_int_num3;
2225 IF error_rec.transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE THEN
2226 l_err_msg_name := 'EGO_ITEM_LC_PREVENTS_AML';
2227 ELSE
2228 l_err_msg_name := 'EGO_LC_PREVENTS_AML_MOD';
2229 END IF;
2230 l_err_token_table(1).TOKEN_NAME := 'LC_PHASE';
2231 l_err_token_table(1).TOKEN_VALUE := l_dummy_message;
2232 l_err_token_table(2).TOKEN_NAME := 'ITEM_NUMBER';
2233 l_err_token_table(2).TOKEN_VALUE := error_rec.item_number;
2234 l_err_token_table(3).TOKEN_NAME := 'ORGANIZATION_NAME';
2235 l_err_token_table(3).TOKEN_VALUE := error_rec.organization_code;
2236 ELSIF error_rec.process_flag = G_PS_NO_AML_PRIV THEN
2237 l_message_has_token := TRUE;
2238 l_err_msg_name := 'EGO_AML_EDIT_PRIV_REQD';
2239 l_err_token_table(1).TOKEN_NAME := 'ITEM_NUMBER';
2240 l_err_token_table(1).TOKEN_VALUE := error_rec.item_number;
2241 l_err_token_table(2).TOKEN_NAME := 'ORG_CODE';
2242 l_err_token_table(2).TOKEN_VALUE := error_rec.organization_code;
2243 ELSIF error_rec.process_flag = G_PS_SD_NOT_NULL THEN
2244 l_err_msg_name := 'EGO_CANNOT_UPD_SD_TO_NULL';
2245 ELSIF error_rec.process_flag = G_PS_ED_LT_SYSDATE THEN
2246 l_err_msg_name := 'EGO_ENDDATE_EXCEEDS_SYSDATE';
2247 ELSIF error_rec.process_flag = G_PS_DFF_INVALID THEN
2248 l_message_has_token := TRUE;
2249 l_err_msg_name := 'EGO_GENERIC_MSG_TEXT';
2250 l_err_token_table(1).TOKEN_NAME := 'MESSAGE';
2251 l_err_token_table(1).TOKEN_VALUE := error_rec.prog_int_char2;
2252 END IF;
2253 ERROR_HANDLER.Add_Error_Message
2254 (p_message_name => l_err_msg_name
2255 ,p_application_id => l_application_context
2256 ,p_token_tbl => l_err_token_table
2257 ,p_message_type => l_message_type
2258 ,p_row_identifier => error_rec.transaction_id
2259 ,p_entity_id => NULL
2260 ,p_entity_index => l_entity_index
2261 ,p_table_name => l_intf_table_name
2262 ,p_entity_code => l_intf_table_name
2263 ,p_addto_fnd_stack => 'N'
2264 );
2265 IF l_message_has_token THEN
2266 l_err_token_table.DELETE();
2267 l_message_has_token := FALSE;
2268 END IF;
2269 END LOOP;
2270
2271 --
2272 -- to flush the buffer into the table
2273 --
2274 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2275 ,p_module => l_api_name
2276 ,p_message => 'Start writing errors to table'
2277 );
2278 ERROR_HANDLER.Log_Error(p_write_err_to_inttable => 'Y'
2279 ,p_write_err_to_conclog => 'N'
2280 ,p_write_err_to_debugfile => 'N');
2281 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2282 ,p_module => l_api_name
2283 ,p_message => 'Completed Logging errors to table'
2284 );
2285
2286 UPDATE ego_aml_intf
2287 SET process_flag = G_PS_GENERIC_ERROR
2288 WHERE data_set_id = p_data_set_id
2289 AND process_flag > G_PS_SUCCESS;
2290
2291 IF l_entity_index = 0 THEN
2292 RETURN FALSE;
2293 ELSE
2294 RETURN TRUE;
2295 END IF;
2296
2297 EXCEPTION
2298 WHEN OTHERS THEN
2299 IF c_err_records%ISOPEN THEN
2300 CLOSE c_err_records;
2301 END IF;
2302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2303 x_msg_count := 1;
2304 -- for SQL errors
2305 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2306 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2307 FND_MESSAGE.Set_Token('API_NAME', 'Log_Errors_Now');
2308 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2309 x_msg_data := FND_MESSAGE.get();
2310 RETURN FALSE;
2311 END Log_Errors_Now;
2312
2313
2314 -- ==========================================================================
2315 -- Public Functions and Procedures
2316 -- ==========================================================================
2317
2318 Procedure Delete_AML_Interface_Lines (
2319 p_api_version IN NUMBER
2320 ,p_commit IN VARCHAR2
2321 ,p_data_set_id IN NUMBER
2322 ,p_delete_line_type IN NUMBER
2323 ,x_return_status OUT NOCOPY VARCHAR2
2324 ,x_msg_count OUT NOCOPY NUMBER
2325 ,x_msg_data OUT NOCOPY VARCHAR2
2326 ) IS
2327
2328 ---------------------------------------------------------------------------
2329 -- Start of comments
2330 -- API name : Delete AML Interface Lines
2331 -- Type : Public
2332 -- Pre-reqs : None
2333 -- FUNCTION : To delete Interface Lines and corresponding error messages
2334 -- if logged.
2335 --
2336 -- Return Parameter:
2337 -- x_return_status
2338 -- 'S' if successful
2339 -- 'E' in case of any errors
2340 --
2341 ---------------------------------------------------------------------------
2342 l_api_version NUMBER;
2343 l_api_name VARCHAR2(50);
2344 l_table_name VARCHAR2(50);
2345
2346 BEGIN
2347 x_return_status := FND_API.G_RET_STS_SUCCESS;
2348 x_msg_count := 0;
2349 x_msg_data := NULL;
2350 l_api_version := 1.0;
2351 l_api_name := 'DELETE_AML_INTF_LINES';
2352 SetGobals();
2353 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2354 ,p_module => l_api_name
2355 ,p_message => ' p_api_version -'||p_api_version
2356 ||' p_commit -'||p_commit
2357 ||' p_data_set_id -'||p_data_set_id
2358 ||' p_delete_line_type -'||p_delete_line_type
2359 );
2360 -- standard check for API validation
2361 IF NOT FND_API.Compatible_API_Call (l_api_version,
2362 p_api_version,
2363 l_api_name,
2364 G_PKG_NAME) THEN
2365 x_return_status := FND_API.G_RET_STS_ERROR;
2366 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2367 ,p_count => x_msg_count
2368 ,p_data => x_msg_data);
2369 RETURN;
2370 END IF;
2371 IF (p_data_set_id IS NULL
2372 OR
2373 p_delete_line_type NOT IN
2374 (EGO_ITEM_PUB.G_INTF_DELETE_ALL
2375 ,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
2376 ,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
2377 ,EGO_ITEM_PUB.G_INTF_DELETE_NONE
2378 )
2379 ) THEN
2380 x_return_status := FND_API.G_RET_STS_ERROR;
2381 fnd_message.set_name('EGO','EGO_IPI_INSUFFICIENT_PARAMS');
2382 fnd_message.set_token('PROG_NAME',G_PKG_NAME||'.'||l_api_name);
2383 fnd_msg_pub.Add;
2384 fnd_msg_pub.Count_And_Get(p_encoded => FND_API.G_FALSE
2385 ,p_count => x_msg_count
2386 ,p_data => x_msg_data);
2387 RETURN;
2388 END IF;
2389
2390 IF FND_API.To_Boolean(p_commit) THEN
2391 SAVEPOINT DELETE_AML_INTF_LINES_SP;
2392 END IF;
2393
2394 l_table_name := 'EGO_AML_INTF';
2395 IF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ALL THEN
2396 --
2397 -- delete all lines
2398 --
2399 DELETE MTL_INTERFACE_ERRORS
2400 WHERE table_name = l_table_name
2401 AND transaction_id IN
2402 (SELECT transaction_id
2403 FROM EGO_AML_INTF
2404 WHERE data_set_id = p_data_set_id
2405 );
2406
2407 DELETE EGO_AML_INTF
2408 WHERE data_set_id = p_data_set_id;
2409
2410 ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ERROR THEN
2411 --
2412 -- delete all error lines
2413 --
2414 DELETE MTL_INTERFACE_ERRORS
2415 WHERE table_name = l_table_name
2416 AND transaction_id IN
2417 (SELECT transaction_id
2418 FROM EGO_AML_INTF
2419 WHERE data_set_id = p_data_set_id
2420 AND process_flag = G_PS_GENERIC_ERROR
2421 );
2422
2423 DELETE EGO_AML_INTF
2424 WHERE data_set_id = p_data_set_id
2425 AND process_flag = G_PS_GENERIC_ERROR;
2426
2427 ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS THEN
2428 --
2429 -- delete all success lines
2430 --
2431 DELETE EGO_AML_INTF
2432 WHERE data_set_id = p_data_set_id
2433 AND process_flag = G_PS_SUCCESS;
2434 END IF;
2435
2436 IF FND_API.To_Boolean(p_commit) THEN
2437 COMMIT WORK;
2438 END IF;
2439 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2440 ,p_module => l_api_name
2441 ,p_message => 'Completed '||l_api_name
2442 );
2443
2444 EXCEPTION
2445 WHEN OTHERS THEN
2446 IF FND_API.To_Boolean(p_commit) THEN
2447 ROLLBACK TO DELETE_AML_INTF_LINES_SP;
2448 END IF;
2449 x_return_status := FND_API.G_RET_STS_ERROR;
2450 x_msg_count := 1;
2451 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2452 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2453 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
2454 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2455 x_msg_data := FND_MESSAGE.get();
2456 log_now (p_log_level => G_DEBUG_LEVEL_UNEXPECTED
2457 ,p_module => l_api_name
2458 ,p_message => x_msg_data
2459 );
2460 END Delete_AML_Interface_Lines;
2461
2462
2463
2464 Procedure Load_Interface_Lines (
2465 ERRBUF OUT NOCOPY VARCHAR2
2466 ,RETCODE OUT NOCOPY VARCHAR2
2467 ,p_data_set_id IN NUMBER
2468 ,p_delete_line_type IN NUMBER
2469 ,p_mode IN VARCHAR2
2470 ,p_perform_security_check IN VARCHAR2
2471 ) IS
2472 ---------------------------------------------------------------------------
2473 -- Start of comments
2474 -- API name : Load_Interface_Lines
2475 -- Type : Public
2476 -- Pre-reqs : None
2477 -- FUNCTION : To bulkload the Interface records into the Production
2478 -- and Pending changes table.
2479 --
2480 -- Parameters:
2481 -- IN : p_data_set_id VARCHAR2
2482 -- batch identifier
2483 -- : p_delete_line_type NUMBER
2484 -- How the lines are to be processed in the interface table:
2485 -- DELETE_ALL = 0 (delete all lines)
2486 -- DELETE_ERROR = 3 (delete all error lines)
2487 -- DELETE_SUCCESS = 7 (delete all successful lines)
2488 -- : p_mode VARCHAR2
2489 -- currently only mode 'NORMAL' is supported
2490 -- How the data to be processed:
2491 -- MODE_HISTORICAL = 'HISTORICAL'
2492 -- user is populating historical data, so no date
2493 -- check and security check will be performed.
2494 -- MODE_NORMAL = 'NORMAL'
2495 -- user is populating normal data, so perform date
2496 -- check and security check.
2497 -- : p_perform_security_check VARCHAR2
2498 -- currently only FND_API.G_TRUE is supported
2499 -- Whether security check needs to be done
2500 -- FND_API.G_TRUE - Perform data security check
2501 -- FND_API.G_FALSE - No data security check is done
2502 --
2503 --
2504 -- OUT : ERRBUF VARCHAR2
2505 -- has the error message details
2506 -- RETCODE VARCHAR2
2507 -- '0' if the program is success
2508 -- '1' if the program has a warning
2509 -- '2' if the program has an error
2510 --
2511 ---------------------------------------------------------------------------
2512 l_api_version NUMBER;
2513 l_api_name VARCHAR2(30);
2514
2515 NO_ROWS_IN_INTF_TABLE EXCEPTION;
2516
2517 l_delete_line_type NUMBER;
2518 l_mode VARCHAR2(30);
2519 l_perform_security_check BOOLEAN;
2520 l_prog_mode_history BOOLEAN;
2521
2522 l_pend_data_row EGO_MFG_PART_NUM_CHGS%ROWTYPE;
2523 l_prod_data_row MTL_MFG_PART_NUMBERS%ROWTYPE;
2524
2525 l_msg_data VARCHAR2(4000);
2526 l_msg_count NUMBER;
2527 l_return_status VARCHAR2(1);
2528 l_err_msg_sql VARCHAR2(4000);
2529
2530 BEGIN
2531 l_api_version := 1.0;
2532 l_api_name := 'LOAD_INTERFACE_LINES';
2533 ERRBUF := NULL;
2534 RETCODE := G_CONC_RET_STS_SUCCESS;
2535 SetGobals();
2536 SetProcessConstants();
2537 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2538 ,p_module => l_api_name
2539 ,p_message => 'All globals initialized'
2540 );
2541 IF (p_data_set_id IS NULL) THEN
2542 fnd_message.set_name('EGO', 'EGO_DATA_SET_ID');
2543 l_msg_data := fnd_msg_pub.get();
2544 fnd_message.set_name('EGO','EGO_PKG_MAND_VALUES_MISS1');
2545 fnd_message.set_token('PACKAGE', G_PKG_NAME ||'.'|| l_api_name);
2546 fnd_message.set_token('VALUE', l_msg_data);
2547 ERRBUF := fnd_message.get();
2548 RETCODE := G_CONC_RET_STS_ERROR;
2549 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2550 ,p_module => l_api_name
2551 ,p_message => ERRBUF
2552 );
2553 RETURN;
2554 END IF;
2555 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2556 ,p_module => l_api_name
2557 ,p_message => 'Mand param check successful'
2558 );
2559
2560 BEGIN
2561 SELECT 'S' INTO l_return_status
2562 FROM EGO_AML_INTF
2563 WHERE DATA_SET_ID = p_data_set_id
2564 AND PROCESS_FLAG = G_PS_TO_BE_PROCESSED
2565 AND ROWNUM = 1;
2566 EXCEPTION
2567 WHEN NO_DATA_FOUND THEN
2568 fnd_message.set_name('EGO','EGO_IPI_NO_LINES');
2569 l_msg_data := fnd_message.get();
2570 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2571 ,p_module => l_api_name
2572 ,p_message => l_msg_data
2573 );
2574 RAISE NO_ROWS_IN_INTF_TABLE;
2575 END;
2576 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2577 ,p_module => l_api_name
2578 ,p_message => 'Found Interface Lines to be processed'
2579 );
2580
2581 -- create save point
2582 SAVEPOINT LOAD_INTERFACE_LINES_SP;
2583
2584 -- Initialize message list
2585 ERROR_HANDLER.initialize();
2586 ERROR_HANDLER.set_bo_identifier(G_BO_IDENTIFIER);
2587 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2588 ,p_module => l_api_name
2589 ,p_message => 'Error Handler Initialization successful '
2590 );
2591 l_delete_line_type
2592 := NVL(p_delete_line_type, EGO_ITEM_PUB.G_INTF_DELETE_NONE);
2593 l_mode := NVL(p_mode, MODE_NORMAL);
2594 IF l_mode = MODE_NORMAL THEN
2595 l_prog_mode_history := FALSE;
2596 ELSE
2597 l_prog_mode_history := TRUE;
2598 END IF;
2599 l_perform_security_check :=
2600 FND_API.to_boolean(NVL(p_perform_security_check,FND_API.G_TRUE));
2601 --
2602 -- initialize default values on interface table
2603 --
2604 UPDATE ego_aml_intf
2605 SET process_flag = G_PS_IN_PROCESS,
2606 transaction_type = UPPER(transaction_type),
2607 transaction_id = NVL(transaction_id, EGO_IPI_TRANSACTION_ID_S.nextval),
2608 first_article_status =
2609 (SELECT CASE WHEN
2610 (first_article_status_meaning = EGO_ITEM_PUB.G_INTF_NULL_CHAR
2611 AND
2612 first_article_status IS NULL
2613 )
2614 THEN EGO_ITEM_PUB.G_INTF_NULL_CHAR
2615 ELSE first_article_status
2616 END
2617 FROM DUAL),
2618 approval_status =
2619 (SELECT CASE WHEN
2620 (approval_status_meaning = EGO_ITEM_PUB.G_INTF_NULL_CHAR
2621 AND
2622 approval_status IS NULL
2623 )
2624 THEN EGO_ITEM_PUB.G_INTF_NULL_CHAR
2625 ELSE approval_status
2626 END
2627 FROM DUAL),
2628 request_id = G_REQUEST_ID,
2629 program_application_id = G_PROG_APPID,
2630 program_id = G_PROG_ID,
2631 program_update_date = SYSDATE,
2632 prog_int_num1 = NULL,
2633 prog_int_num2 = NULL,
2634 prog_int_num3 = NULL,
2635 prog_int_num4 = NULL,
2636 prog_int_char1 = NULL,
2637 prog_int_char2 = 'N'
2638 WHERE data_set_id = p_data_set_id
2639 AND process_flag = G_PS_TO_BE_PROCESSED;
2640 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2641 ,p_module => l_api_name
2642 ,p_message => 'Defalting values complete'
2643 );
2644
2645 UPDATE ego_aml_intf
2646 SET process_flag = G_PS_MAND_PARAM_MISSING
2647 WHERE data_set_id = p_data_set_id
2648 AND process_flag = G_PS_IN_PROCESS
2649 AND ( mfg_part_num IS NULL
2650 OR
2651 (manufacturer_id IS NULL AND manufacturer_name IS NULL)
2652 OR
2653 (organization_id IS NULL AND organization_code IS NULL)
2654 OR
2655 (inventory_item_id IS NULL AND item_number IS NULL)
2656 );
2657 log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2658 ,p_module => l_api_name
2659 ,p_message => 'Mand params check in each row complete '
2660 );
2661
2662 valueToIdConversion(p_data_set_id => p_data_set_id
2663 ,x_return_status => l_return_status
2664 ,x_msg_count => l_msg_count
2665 ,x_msg_data => l_msg_data);
2666 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2667 ,p_module => l_api_name
2668 ,p_message => 'Value to ID Conversion returned with status '
2669 ||l_return_status
2670 );
2671 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2672 <> FND_API.G_RET_STS_SUCCESS THEN
2673 ERRBUF := l_msg_data;
2674 RETCODE := G_CONC_RET_STS_ERROR;
2675 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2676 ,p_module => l_api_name
2677 ,p_message => ERRBUF
2678 );
2679 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2680 RETURN;
2681 END IF;
2682
2683 TransactionCheck(p_data_set_id => p_data_set_id
2684 ,p_mode => l_mode
2685 ,x_return_status => l_return_status
2686 ,x_msg_count => l_msg_count
2687 ,x_msg_data => l_msg_data);
2688 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2689 ,p_module => l_api_name
2690 ,p_message => 'Trans and Date validation returned with status '
2691 ||l_return_status
2692 );
2693 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2694 <> FND_API.G_RET_STS_SUCCESS THEN
2695 ERRBUF := l_msg_data;
2696 RETCODE := G_CONC_RET_STS_ERROR;
2697 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2698 ,p_module => l_api_name
2699 ,p_message => ERRBUF
2700 );
2701 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2702 RETURN;
2703 END IF;
2704
2705 performDupRecordCheck (p_data_set_id => p_data_set_id
2706 ,x_return_status => l_return_status
2707 ,x_msg_count => l_msg_count
2708 ,x_msg_data => l_msg_data);
2709 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2710 ,p_module => l_api_name
2711 ,p_message => 'Duplicate records check done with status '
2712 ||l_return_status
2713 );
2714 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2715 <> FND_API.G_RET_STS_SUCCESS THEN
2716 ERRBUF := l_msg_data;
2717 RETCODE := G_CONC_RET_STS_ERROR;
2718 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2719 ,p_module => l_api_name
2720 ,p_message => ERRBUF
2721 );
2722 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2723 RETURN;
2724 END IF;
2725
2726 performCMSeggregation(p_data_set_id => p_data_set_id
2727 ,x_return_status => l_return_status
2728 ,x_msg_count => l_msg_count
2729 ,x_msg_data => l_msg_data);
2730 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2731 ,p_module => l_api_name
2732 ,p_message => 'CM Seggregation returned with status '
2733 ||l_return_status
2734 );
2735 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2736 <> FND_API.G_RET_STS_SUCCESS THEN
2737 ERRBUF := l_msg_data;
2738 RETCODE := G_CONC_RET_STS_ERROR;
2739 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2740 ,p_module => l_api_name
2741 ,p_message => ERRBUF
2742 );
2743 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2744 RETURN;
2745 END IF;
2746
2747 performItemValidation(p_data_set_id => p_data_set_id
2748 ,p_perform_security_check => l_perform_security_check
2749 ,x_return_status => l_return_status
2750 ,x_msg_count => l_msg_count
2751 ,x_msg_data => l_msg_data);
2752 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2753 ,p_module => l_api_name
2754 ,p_message => 'Item Validation returned with status '
2755 ||l_return_status
2756 );
2757 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2758 <> FND_API.G_RET_STS_SUCCESS THEN
2759 ERRBUF := l_msg_data;
2760 RETCODE := G_CONC_RET_STS_ERROR;
2761 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2762 ,p_module => l_api_name
2763 ,p_message => ERRBUF
2764 );
2765 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2766 RETURN;
2767 END IF;
2768
2769 performDFFValidation(p_data_set_id => p_data_set_id
2770 ,p_perform_security_check => l_perform_security_check
2771 ,x_return_status => l_return_status
2772 ,x_msg_count => l_msg_count
2773 ,x_msg_data => l_msg_data);
2774 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2775 ,p_module => l_api_name
2776 ,p_message => 'Item DFF Validation returned with status '
2777 ||l_return_status
2778 );
2779 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2780 <> FND_API.G_RET_STS_SUCCESS THEN
2781 ERRBUF := l_msg_data;
2782 RETCODE := G_CONC_RET_STS_ERROR;
2783 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2784 ,p_module => l_api_name
2785 ,p_message => ERRBUF
2786 );
2787 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2788 RETURN;
2789 END IF;
2790
2791 populateProductionTable(p_data_set_id => p_data_set_id
2792 ,x_return_status => l_return_status
2793 ,x_msg_count => l_msg_count
2794 ,x_msg_data => l_msg_data);
2795 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2796 ,p_module => l_api_name
2797 ,p_message => 'Production table population returned with status '
2798 ||l_return_status
2799 );
2800 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2801 <> FND_API.G_RET_STS_SUCCESS THEN
2802 ERRBUF := l_msg_data;
2803 RETCODE := G_CONC_RET_STS_ERROR;
2804 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2805 ,p_module => l_api_name
2806 ,p_message => ERRBUF
2807 );
2808 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2809 RETURN;
2810 END IF;
2811
2812 IF Log_Errors_Now (p_data_set_id => p_data_set_id
2813 ,x_return_status => l_return_status
2814 ,x_msg_count => l_msg_count
2815 ,x_msg_data => l_msg_data) THEN
2816 RETCODE := G_CONC_RET_STS_WARNING;
2817 ELSE
2818 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2819 <> FND_API.G_RET_STS_SUCCESS THEN
2820 ERRBUF := l_msg_data;
2821 RETCODE := G_CONC_RET_STS_ERROR;
2822 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2823 ,p_module => l_api_name
2824 ,p_message => ERRBUF
2825 );
2826 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2827 RETURN;
2828 END IF;
2829 END IF;
2830
2831 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2832 ,p_module => l_api_name
2833 ,p_message => 'Error Logging Returned with status '
2834 ||l_return_status
2835 );
2836
2837 --
2838 -- writing the errors into the concurrent log
2839 --
2840 l_err_msg_sql := 'SELECT INTF.ITEM_NUMBER as ITEM_NUMBER, '||
2841 ' INTF.ORGANIZATION_CODE as ORGANIZATINO_CODE, '||
2842 -- ' NULL AS REVISION_CODE, '||
2843 ' MIERR.ERROR_MESSAGE as ERROR_MESSAGE '||
2844 ' FROM EGO_AML_INTF INTF, MTL_INTERFACE_ERRORS MIERR '||
2845 ' WHERE MIERR.TRANSACTION_ID = INTF.TRANSACTION_ID '||
2846 ' AND MIERR.REQUEST_ID = INTF.REQUEST_ID '||
2847 ' AND MIERR.request_id = :1';
2848 EGO_ITEM_OPEN_INTERFACE_PVT.Write_Error_into_ConcurrentLog
2849 (p_entity_name => 'EGO_AML'
2850 ,p_table_name => 'EGO_AML_INTF'
2851 ,p_selectQuery => l_err_msg_sql
2852 ,p_request_id => G_REQUEST_ID
2853 ,x_return_status => l_return_status
2854 ,x_msg_count => l_msg_count
2855 ,x_msg_data => l_msg_data
2856 );
2857
2858 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2859 ,p_module => l_api_name
2860 ,p_message => 'Returned from EGO_ITEM_OPEN_INTERFACE_PVT.'||
2861 'Write_Error_into_concurrentlog with status '||
2862 l_return_status
2863 );
2864 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2865 = FND_API.G_RET_STS_UNEXP_ERROR THEN
2866 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2867 ,p_module => l_api_name
2868 ,p_message => l_msg_data
2869 );
2870 END IF;
2871
2872 IF p_delete_line_type <> EGO_ITEM_PUB.G_INTF_DELETE_NONE THEN
2873 Delete_AML_Interface_Lines(p_api_version => 1.0
2874 ,p_commit => FND_API.G_FALSE
2875 ,p_data_set_id => p_data_set_id
2876 ,p_delete_line_type => p_delete_line_type
2877 ,x_return_status => l_return_status
2878 ,x_msg_count => l_msg_count
2879 ,x_msg_data => l_msg_data
2880 );
2881 IF NVL(l_return_status,FND_API.G_RET_STS_SUCCESS)
2882 <> FND_API.G_RET_STS_SUCCESS THEN
2883 ERRBUF := l_msg_data;
2884 RETCODE := G_CONC_RET_STS_ERROR;
2885 log_now (p_log_level => G_DEBUG_LEVEL_EXCEPTION
2886 ,p_module => l_api_name
2887 ,p_message => ERRBUF
2888 );
2889 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2890 RETURN;
2891 END IF;
2892 END IF;
2893
2894 COMMIT WORK;
2895
2896 --
2897 -- calling sync im index from here
2898 --
2899 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2900 ,p_module => l_api_name
2901 ,p_message => 'Calling Sync IM Index for Mfg Part Nums'
2902 );
2903 EGO_ITEM_TEXT_UTIL.Sync_Index();
2904 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2905 ,p_module => l_api_name
2906 ,p_message => 'Successfully called Sync IM Index for Mfg Part Nums'
2907 );
2908 --
2909 -- calling the business event now
2910 -- just call once for the entire batch
2911 --
2912 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2913 ,p_module => l_api_name
2914 ,p_message => 'Calling Business Events for Mfg Part Nums'
2915 );
2916 EGO_WF_WRAPPER_PVT.Raise_Item_Event(
2917 p_event_name => EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT
2918 -- ,p_dml_type IN VARCHAR2 DEFAULT NULL
2919 ,p_request_id => G_REQUEST_ID
2920 -- ,p_Inventory_Item_Id IN NUMBER DEFAULT NULL
2921 -- ,p_Organization_Id IN NUMBER DEFAULT NULL
2922 -- ,p_Revision_id IN NUMBER DEFAULT NULL
2923 -- ,p_category_id IN VARCHAR2 DEFAULT NULL
2924 -- ,p_catalog_id IN VARCHAR2 DEFAULT NULL
2925 ,x_msg_data => l_msg_data
2926 ,x_return_status => l_return_status
2927 );
2928 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2929 ,p_module => l_api_name
2930 ,p_message => 'Returning BE for Mfg Part Nums with status '||
2931 l_return_status ||' and message: '||l_msg_data
2932 );
2933 log_now (p_log_level => G_DEBUG_LEVEL_PROCEDURE
2934 ,p_module => l_api_name
2935 ,p_message => 'Procedure completed with status '||RETCODE
2936 );
2937 EXCEPTION
2938 WHEN NO_ROWS_IN_INTF_TABLE THEN
2939 RETCODE := G_CONC_RET_STS_SUCCESS;
2940 WHEN OTHERS THEN
2941 ROLLBACK TO LOAD_INTERFACE_LINES_SP;
2942 RETCODE := G_CONC_RET_STS_ERROR;
2943 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
2944 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
2945 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
2946 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
2947 ERRBUF := FND_MSG_PUB.get();
2948 log_now (p_log_level => G_DEBUG_LEVEL_UNEXPECTED
2949 ,p_module => l_api_name
2950 ,p_message => ERRBUF
2951 );
2952 END Load_Interface_Lines;
2953
2954
2955 END EGO_ITEM_AML_PVT;