[Home] [Help]
PACKAGE BODY: APPS.FLM_SEQ_ID2NAME
Source
1 PACKAGE BODY flm_seq_id2name AS
2 /* $Header: FLMSQIDB.pls 120.1.12000000.2 2007/02/26 19:33:32 yulin ship $ */
3
4
5 /******************************************************************
6 * To check whether given attribute needs ID2Name translation *
7 ******************************************************************/
8 PROCEDURE ID2Name( p_attribute_id IN NUMBER,
9 p_init_msg_list IN VARCHAR2,
10 x_id2name OUT NOCOPY VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2
14 ) IS
15 i NUMBER;
16 l_type NUMBER;
17 l_tab VARCHAR2(40);
18 l_col VARCHAR2(40);
19 l_id2name VARCHAR2(2) := NULL;
20
21 l_return_status VARCHAR2(2);
22 l_msg_count NUMBER;
23 l_msg_data VARCHAR2(1000);
24 BEGIN
25 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
26 THEN
27 FND_MSG_PUB.initialize;
28 END IF;
29
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31 x_id2name := 'N';
32
33 select
34 attribute_type, attribute_source
35 into l_type, l_col
36 from flm_seq_attributes
37 where attribute_id = p_attribute_id;
38
39 if (l_type = 4) then
40 l_tab := 'MTL_SYSTEM_ITEMS';
41 elsif (l_type = 5) then
42 l_tab := 'OE_ORDER_LINES_ALL';
43 elsif (l_type = 6) then
44 l_tab := 'MRP_RECOMMENDATIONS';
45 elsif (l_type = 14) then
46 l_tab := 'WIP_FLOW_SCHEDULES';
47 end if;
48
49 ID2Name(l_tab, l_col, 'F', l_id2name, l_return_status, l_msg_count, l_msg_data);
50
51 x_return_status := l_return_status;
52 x_msg_count := l_msg_count;
53 x_msg_data := l_msg_data;
54 x_id2name := l_id2name;
55 return;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
60 x_id2name := 'N';
61
62 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
63 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_id2name' ,'ID2Name');
64 END IF;
65
66 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
67
68 END Id2Name;
69
70
71 /******************************************************************
72 * To get LOV query for given ID2Name attribute *
73 ******************************************************************/
74 PROCEDURE ID2NameLovQuery(
75 p_attribute_id IN NUMBER,
76 p_init_msg_list IN VARCHAR2,
77 x_query OUT NOCOPY VARCHAR2,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_count OUT NOCOPY NUMBER,
80 x_msg_data OUT NOCOPY VARCHAR2
81 ) IS
82 i NUMBER;
83 l_type NUMBER;
84 l_tab VARCHAR2(40);
85 l_col VARCHAR2(40);
86 l_query VARCHAR2(2000) := NULL;
87
88 l_return_status VARCHAR2(2);
89 l_msg_count NUMBER;
90 l_msg_data VARCHAR2(1000);
91 BEGIN
92 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
93 THEN
94 FND_MSG_PUB.initialize;
95 END IF;
96
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 select
100 attribute_type, attribute_source
101 into l_type, l_col
102 from flm_seq_attributes
103 where attribute_id = p_attribute_id;
104
105 if (l_type = 4) then
106 l_tab := 'MTL_SYSTEM_ITEMS';
107 elsif (l_type = 5) then
108 l_tab := 'OE_ORDER_LINES_ALL';
109 elsif (l_type = 6) then
110 l_tab := 'MRP_RECOMMENDATIONS';
111 elsif (l_type = 14) then
112 l_tab := 'WIP_FLOW_SCHEDULES';
113 end if;
114
115 ID2NameLovQuery(l_tab, l_col, 'F', l_query, l_return_status, l_msg_count, l_msg_data);
116
117 x_return_status := l_return_status;
118 x_msg_count := l_msg_count;
119 x_msg_data := l_msg_data;
120 x_query := l_query;
121 return;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
126 x_query := null;
127
128 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
129 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_id2name' ,'ID2NameLovQuery');
130 END IF;
131
132 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
133
134 END Id2NameLovQuery;
135
136
137 /******************************************************************
138 * To check whether given table.column needs ID2Name translation *
139 ******************************************************************/
140 PROCEDURE ID2Name( p_table IN VARCHAR2,
141 p_column IN VARCHAR2,
142 p_init_msg_list IN VARCHAR2,
143 x_id2name OUT NOCOPY VARCHAR2,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2
147 ) IS
148 i NUMBER;
149 BEGIN
150 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
151 THEN
152 FND_MSG_PUB.initialize;
153 END IF;
154
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156 x_id2name := 'N';
157
158 if (ID2Name_Table is null OR ID2Name_Table.COUNT <= 0) then
159 ID2NameInit;
160 end if;
161
162 if (ID2Name_Table is null OR ID2Name_Table.COUNT <= 0) then
163 return;
164 end if;
165
166 i := ID2Name_Table.FIRST;
167 LOOP
168 if ID2Name_Table(i).table_name = UPPER(p_table) AND
169 ID2Name_Table(i).column_name = UPPER(p_column)
170 then
171 x_id2name := 'Y';
172 exit;
173 end if;
174 exit when (i = ID2Name_Table.LAST);
175 i := ID2Name_Table.NEXT(i);
176 END LOOP;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181 x_id2name := 'N';
182
183 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
184 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_id2name' ,'ID2Name');
185 END IF;
186
187 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
188
189 END Id2Name;
190
191 /*****************************************************
192 * To return a LOV query for given ID column. *
193 *****************************************************/
194 PROCEDURE ID2NameLovQuery( p_table IN VARCHAR2,
195 p_column IN VARCHAR2,
196 p_init_msg_list IN VARCHAR2,
197 x_query OUT NOCOPY VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2)
201 IS
202 i NUMBER;
203 BEGIN
204 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
205 THEN
206 FND_MSG_PUB.initialize;
207 END IF;
208
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 x_query := null;
211
212 if (ID2Name_Table is null OR ID2Name_Table.COUNT <= 0) then
213 ID2NameInit;
214 end if;
215
216 if (ID2Name_Table is null OR ID2Name_Table.COUNT <= 0) then
217 return;
218 end if;
219
220 i := ID2Name_Table.FIRST;
221 LOOP
222 if ID2Name_Table(i).table_name = UPPER(p_table) AND
223 ID2Name_Table(i).column_name = UPPER(p_column)
224 then
225 x_query := ID2Name_Table(i).query_string;
226 exit;
227 end if;
228 exit when (i = ID2Name_Table.LAST);
229 i := ID2Name_Table.NEXT(i);
230 END LOOP;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 x_query := null;
236
237 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
238 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_id2name' ,'ID2NameLovQuery');
239 END IF;
240
241 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
242
243 END Id2NameLovQuery;
244
245
246 /*****************************************************
247 * To return a name for given ID column and ID value *
248 *****************************************************/
249 PROCEDURE ID2NameAttributeValue(
250 p_table IN VARCHAR2,
251 p_column IN VARCHAR2,
252 p_org_id IN NUMBER,
253 p_value IN NUMBER,
254 p_init_msg_list IN VARCHAR2,
255 x_name OUT NOCOPY VARCHAR2,
256 x_return_status OUT NOCOPY VARCHAR2,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_msg_data OUT NOCOPY VARCHAR2)
259 IS
260 l_query VARCHAR2(2000);
261 l_return_status VARCHAR2(2);
262 l_msg_count NUMBER;
263 l_msg_data VARCHAR2(1000);
264 l_cursor INTEGER;
265 l_dummy INTEGER;
266 l_name VARCHAR2(240) := null;
267 BEGIN
268 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
269 THEN
270 FND_MSG_PUB.initialize;
271 END IF;
272
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274
275 ID2NameLovQuery(p_table, p_column, p_init_msg_list, l_query,
276 l_return_status, l_msg_count, l_msg_data);
277
278 if (l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_query is null) then
279 x_return_status := l_return_status;
280 x_msg_count := l_msg_count;
281 x_msg_data := l_msg_data;
282 x_name := null;
283 return;
284 end if;
285
286 flm_util.init_bind;
287 flm_util.add_bind(':org_id', p_org_id);
288
289 l_query := l_query || ' WHERE ID_VALUE=:column_value';
290 flm_util.add_bind(':column_value', p_value);
291
292 l_cursor := DBMS_SQL.OPEN_CURSOR;
293 DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
294 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_name, 240);
295
296 flm_util.do_binds(l_cursor);
297
298 l_dummy := DBMS_SQL.EXECUTE(l_cursor);
299 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
300 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_name);
301 END IF;
302 DBMS_SQL.CLOSE_CURSOR(l_cursor);
303
304 x_name := l_name;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 x_name := null;
310
311 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
312 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_id2name' ,'ID2NameAttributeValue');
313 END IF;
314
315 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
316
317 END Id2NameAttributeValue;
318
319
320
321 /******************************************************************
322 * Initialize ID2Name_Table if it is not initialized. *
323 ******************************************************************/
324 PROCEDURE Id2NameInit IS
325 i NUMBER := 0;
326 BEGIN
327
328 -- ID2Name in MSI (Item)
329
330 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
331 ID2Name_Table(i).column_name := 'INVENTORY_ITEM_ID';
332 ID2Name_Table(i).query_string :=
333 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
334 '(' ||
335 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
336 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
337 'WHERE ORGANIZATION_ID = :ORG_ID' ||
338 ')';
339 i := i + 1;
340
341 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
342 ID2Name_Table(i).column_name := 'PRODUCT_FAMILY_ITEM_ID';
343 ID2Name_Table(i).query_string :=
344 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
345 '(' ||
346 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
347 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
348 'WHERE ORGANIZATION_ID = :ORG_ID AND BOM_ITEM_TYPE = 5' ||
349 ')';
350 i := i + 1;
351
352 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
353 ID2Name_Table(i).column_name := 'BASE_ITEM_ID';
354 ID2Name_Table(i).query_string :=
355 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
356 '(' ||
357 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
358 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
359 'WHERE ORGANIZATION_ID = :ORG_ID AND BOM_ITEM_TYPE = 1' ||
360 ')';
364 ID2Name_Table(i).column_name := 'ATP_RULE_ID';
361 i := i + 1;
362
363 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
365 ID2Name_Table(i).query_string :=
366 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
367 '(' ||
368 'SELECT RULE_NAME NAME_VALUE, RULE_ID ID_VALUE ' ||
369 'FROM MTL_ATP_RULES ' ||
370 'WHERE :ORG_ID IS NOT NULL' ||
371 ')';
372 i := i + 1;
373
374 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
375 ID2Name_Table(i).column_name := 'ITEM_CATALOG_GROUP_ID';
376 ID2Name_Table(i).query_string :=
377 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
378 '(' ||
379 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, ITEM_CATALOG_GROUP_ID ID_VALUE ' ||
380 'FROM MTL_ITEM_CATALOG_GROUPS_KFV ' ||
381 'WHERE :ORG_ID IS NOT NULL' ||
382 ')';
383 i := i + 1;
384
385 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
386 ID2Name_Table(i).column_name := 'PICKING_RULE_ID';
387 ID2Name_Table(i).query_string :=
388 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
389 '(' ||
390 'SELECT PICKING_RULE_NAME NAME_VALUE, PICKING_RULE_ID ID_VALUE ' ||
391 'FROM MTL_PICKING_RULES ' ||
392 'WHERE :ORG_ID IS NOT NULL' ||
393 ')';
394 i := i + 1;
395
396 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
397 ID2Name_Table(i).column_name := 'HAZARD_CLASS_ID';
398 ID2Name_Table(i).query_string :=
399 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
400 '(' ||
401 'SELECT HAZARD_CLASS NAME_VALUE, HAZARD_CLASS_ID ID_VALUE ' ||
402 'FROM PO_HAZARD_CLASSES ' ||
403 'WHERE :ORG_ID IS NOT NULL' ||
404 ')';
405 i := i + 1;
406
407 ID2Name_Table(i).table_name := 'MTL_SYSTEM_ITEMS';
408 ID2Name_Table(i).column_name := 'ENGINEERING_ITEM_ID';
409 ID2Name_Table(i).query_string :=
410 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
411 '(' ||
412 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
413 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
414 'WHERE ORGANIZATION_ID = :ORG_ID AND ENG_ITEM_FLAG = ''Y''' ||
415 ')';
416 i := i + 1;
417
418 -- ID2Name in SO (Sales Order)
419
420 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
421 ID2Name_Table(i).column_name := 'INVENTORY_ITEM_ID';
422 ID2Name_Table(i).query_string :=
423 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
424 '(' ||
425 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
426 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
427 'WHERE ORGANIZATION_ID = :ORG_ID' ||
428 ')';
429 i := i + 1;
430
431 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
432 ID2Name_Table(i).column_name := 'ARRIVAL_SET_ID';
433 ID2Name_Table(i).query_string :=
434 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
435 '(' ||
436 'SELECT SET_NAME NAME_VALUE, SET_ID ID_VALUE ' ||
437 'FROM OE_SETS ' ||
438 'WHERE :ORG_ID IS NOT NULL AND SET_TYPE = ''ARRIVAL''' ||
439 ')';
440 i := i + 1;
441
442 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
443 ID2Name_Table(i).column_name := 'SHIP_SET_ID';
444 ID2Name_Table(i).query_string :=
445 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
446 '(' ||
447 'SELECT SET_NAME NAME_VALUE, SET_ID ID_VALUE ' ||
448 'FROM OE_SETS ' ||
449 'WHERE :ORG_ID IS NOT NULL AND SET_TYPE = ''SHIP''' ||
450 ')';
451 i := i + 1;
452
453 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
454 ID2Name_Table(i).column_name := 'LINE_TYPE_ID';
455 ID2Name_Table(i).query_string :=
456 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
457 '(' ||
458 'SELECT TRANSACTION_TYPE_CODE NAME_VALUE, TRANSACTION_TYPE_ID ID_VALUE ' ||
459 'FROM OE_TRANSACTION_TYPES_ALL ' ||
460 'WHERE :ORG_ID IS NOT NULL' ||
461 ')';
462 i := i + 1;
463
464 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
465 ID2Name_Table(i).column_name := 'SHIP_FROM_ORG_ID';
466 ID2Name_Table(i).query_string :=
467 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
468 '(' ||
469 'SELECT NAME NAME_VALUE, ORGANIZATION_ID ID_VALUE ' ||
470 'FROM HR_ALL_ORGANIZATION_UNITS ' ||
471 'WHERE :ORG_ID IS NOT NULL' ||
472 ')';
473 i := i + 1;
474
475 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
476 ID2Name_Table(i).column_name := 'SHIP_TO_ORG_ID';
477 ID2Name_Table(i).query_string :=
478 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
479 '(' ||
480 'SELECT ORGANIZATION_CODE NAME_VALUE, ORGANIZATION_ID ID_VALUE ' ||
481 'FROM MTL_PARAMETERS ' ||
482 'WHERE :ORG_ID IS NOT NULL' ||
483 ')';
484 i := i + 1;
485
486 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
487 ID2Name_Table(i).column_name := 'PROJECT_ID';
488 ID2Name_Table(i).query_string :=
489 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
490 '(' ||
491 'SELECT segment1 NAME_VALUE, PROJECT_ID ID_VALUE ' ||
492 'FROM PA_PROJECTS_ALL ' ||
493 'WHERE :ORG_ID IS NOT NULL ' ||
494 'union ' ||
495 'select project_number NAME_VALUE, PROJECT_ID ID_VALUE ' ||
496 'from mrp_seiban_numbers ' ||
497 ')';
498 i := i + 1;
499
500 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
501 ID2Name_Table(i).column_name := 'TASK_ID';
502 ID2Name_Table(i).query_string :=
503 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
504 '(' ||
505 'SELECT TASK_NUMBER NAME_VALUE, TASK_ID ID_VALUE ' ||
506 'FROM PA_TASKS ' ||
507 'WHERE :ORG_ID IS NOT NULL' ||
508 ')';
509 i := i + 1;
510
511 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
512 ID2Name_Table(i).column_name := 'LINE_SET_ID';
513 ID2Name_Table(i).query_string :=
517 'FROM OE_SETS ' ||
514 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
515 '(' ||
516 'SELECT SET_NAME NAME_VALUE, SET_ID ID_VALUE ' ||
518 'WHERE :ORG_ID IS NOT NULL AND SET_TYPE = ''LINE''' ||
519 ')';
520 i := i + 1;
521
522 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
523 ID2Name_Table(i).column_name := 'ORDER_SOURCE_ID';
524 ID2Name_Table(i).query_string :=
525 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
526 '(' ||
527 'SELECT NAME NAME_VALUE, ORDER_SOURCE_ID ID_VALUE ' ||
528 'FROM OE_ORDER_SOURCES ' ||
529 'WHERE :ORG_ID IS NOT NULL' ||
530 ')';
531 i := i + 1;
532
533 /*
534 ID2Name_Table(i).table_name := 'OE_ORDER_LINES_ALL';
535 ID2Name_Table(i).column_name := 'CONFIG_HEADER_ID';
536 ID2Name_Table(i).query_string :=
537 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
538 '(' ||
539 'SELECT NAME NAME_VALUE, CONFIG_HDR_ID ID_VALUE ' ||
540 'FROM CZ_CONFIG_HDRS ' ||
541 'WHERE :ORG_ID IS NOT NULL' ||
542 ')';
543 i := i + 1;
544 */
545
546
547 -- ID2Name in PO (Plan Order)
548
549 ID2Name_Table(i).table_name := 'MRP_RECOMMENDATIONS';
550 ID2Name_Table(i).column_name := 'INVENTORY_ITEM_ID';
551 ID2Name_Table(i).query_string :=
552 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
553 '(' ||
554 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
555 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
556 'WHERE ORGANIZATION_ID = :ORG_ID' ||
557 ')';
558 i := i + 1;
559
560 ID2Name_Table(i).table_name := 'MRP_RECOMMENDATIONS';
561 ID2Name_Table(i).column_name := 'PROJECT_ID';
562 ID2Name_Table(i).query_string :=
563 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
564 '(' ||
565 'SELECT segment1 NAME_VALUE, PROJECT_ID ID_VALUE ' ||
566 'FROM PA_PROJECTS_ALL ' ||
567 'WHERE :ORG_ID IS NOT NULL ' ||
568 'union ' ||
569 'select project_number NAME_VALUE, PROJECT_ID ID_VALUE ' ||
570 'from mrp_seiban_numbers ' ||
571 ')';
572 i := i + 1;
573
574 ID2Name_Table(i).table_name := 'MRP_RECOMMENDATIONS';
575 ID2Name_Table(i).column_name := 'TASK_ID';
576 ID2Name_Table(i).query_string :=
577 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
578 '(' ||
579 'SELECT TASK_NUMBER NAME_VALUE, TASK_ID ID_VALUE ' ||
580 'FROM PA_TASKS ' ||
581 'WHERE :ORG_ID IS NOT NULL' ||
582 ')';
583 i := i + 1;
584
585
586 -- ID2Name in WFS (Flow Schedule)
587
588 ID2Name_Table(i).table_name := 'WIP_FLOW_SCHEDULES';
589 ID2Name_Table(i).column_name := 'PRIMARY_ITEM_ID';
590 ID2Name_Table(i).query_string :=
591 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
592 '(' ||
593 'SELECT CONCATENATED_SEGMENTS NAME_VALUE, INVENTORY_ITEM_ID ID_VALUE ' ||
594 'FROM MTL_SYSTEM_ITEMS_KFV ' ||
595 'WHERE ORGANIZATION_ID = :ORG_ID' ||
596 ')';
597 i := i + 1;
598
599 ID2Name_Table(i).table_name := 'WIP_FLOW_SCHEDULES';
600 ID2Name_Table(i).column_name := 'SCHEDULE_GROUP_ID';
601 ID2Name_Table(i).query_string :=
602 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
603 '(' ||
604 'SELECT SCHEDULE_GROUP_NAME NAME_VALUE, SCHEDULE_GROUP_ID ID_VALUE ' ||
605 'FROM WIP_SCHEDULE_GROUPS ' ||
606 'WHERE ORGANIZATION_ID = :ORG_ID' ||
607 ')';
608 i := i + 1;
609
610 ID2Name_Table(i).table_name := 'WIP_FLOW_SCHEDULES';
611 ID2Name_Table(i).column_name := 'KANBAN_CARD_ID';
612 ID2Name_Table(i).query_string :=
613 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
614 '(' ||
615 'SELECT KANBAN_CARD_NUMBER NAME_VALUE, KANBAN_CARD_ID ID_VALUE ' ||
616 'FROM MTL_KANBAN_CARDS ' ||
617 'WHERE ORGANIZATION_ID = :ORG_ID' ||
618 ')';
619 i := i + 1;
620
621 ID2Name_Table(i).table_name := 'WIP_FLOW_SCHEDULES';
622 ID2Name_Table(i).column_name := 'PROJECT_ID';
623 ID2Name_Table(i).query_string :=
624 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
625 '(' ||
626 'SELECT segment1 NAME_VALUE, PROJECT_ID ID_VALUE ' ||
627 'FROM PA_PROJECTS_ALL ' ||
628 'WHERE :ORG_ID IS NOT NULL ' ||
629 'union ' ||
630 'select project_number NAME_VALUE, PROJECT_ID ID_VALUE ' ||
631 'from mrp_seiban_numbers ' ||
632 ')';
633 i := i + 1;
634
635 ID2Name_Table(i).table_name := 'WIP_FLOW_SCHEDULES';
636 ID2Name_Table(i).column_name := 'TASK_ID';
637 ID2Name_Table(i).query_string :=
638 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
639 '(' ||
640 'SELECT TASK_NUMBER NAME_VALUE, TASK_ID ID_VALUE ' ||
641 'FROM PA_TASKS ' ||
642 'WHERE :ORG_ID IS NOT NULL' ||
643 ')';
644 i := i + 1;
645
646 ID2Name_Table(i).table_name := 'MTL_CATEGORIES';
647 ID2Name_Table(i).column_name := 'CATEGORY_ID';
648 ID2Name_Table(i).query_string :=
649 'SELECT NAME_VALUE, ID_VALUE FROM ' ||
650 '(' ||
651 'SELECT concatenated_segments NAME_VALUE, CATEGORY_ID ID_VALUE ' ||
652 'FROM MTL_CATEGORIES_KFV ' ||
653 'WHERE :ORG_ID IS NOT NULL' ||
654 ')';
655 i := i + 1;
656
657
658 END Id2NameInit;
659
660
661 END flm_seq_id2name;