[Home] [Help]
PACKAGE BODY: APPS.INV_GEN_LOC_KFV
Source
1 PACKAGE BODY INV_GEN_LOC_KFV AS
2 /* $Header: INVLKFFB.pls 120.6 2011/11/11 07:07:37 yintang ship $ */
3
4 G_compatibility_mode_1157 VARCHAR2(6) := '1157'; --Added for bug#5744890 /* For value 1157, ID to value conversion will happen for locator segments, if defined.*/
5 G_compatibility_mode_1158 VARCHAR2(6) := '1158'; --Added for bug#4345239
6 G_compatibility_mode_1159 VARCHAR2(6) := '1159'; --Added for bug#4345239
7
8
9 -- This regenerates WMS_ITEM_LOCATIONS_KFV with new Segment definitions if any.
10
11 PROCEDURE GENERATE_LOCATOR_KFF_VIEW(x_errbuf OUT NOCOPY VARCHAR2
12 ,x_retcode OUT NOCOPY NUMBER
13 ,p_compatibility IN VARCHAR2) IS --Added for bug#4345239
14
15 l_view_text VARCHAR2(10000);
16 l_concat_segments VARCHAR2(500);
17 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
18 --
19 CURSOR c_cur_seg (p_compatibility VARCHAR2) IS
20 SELECT application_column_name
21 FROM fnd_id_flex_segments ffs
22 WHERE application_id = 401 -- 'INV'
23 AND id_flex_code = 'MTLL'
24 AND id_flex_num = 101 -- 'STOCK_LOCATORS'
25 AND enabled_flag = 'Y'
26 AND display_flag = 'Y'
27 AND (application_column_name not in ('SEGMENT19','SEGMENT20')
28 OR p_compatibility = G_compatibility_mode_1158) --Added for bug#4345239
29 ORDER BY segment_num;
30
31 --
32 CURSOR c_delim is
33 SELECT concatenated_segment_delimiter
34 FROM fnd_id_flex_structures
35 WHERE id_flex_code = 'MTLL'
36 AND ROWNUM = 1;
37 --
38 l_segment_name VARCHAR2(50);
39 l_delim VARCHAR2(1);
40 l_sqlid INTEGER;
41 l_execute INTEGER;
42 l_compatibility VARCHAR2(10) := NVL( p_compatibility, '1159'); --Bug#4345239.Set the default Value
43 l_proj_task_segments VARCHAR2(20); --Added Bug#4345239
44 BEGIN
45 --
46 l_view_text := 'CREATE OR REPLACE VIEW WMS_ITEM_LOCATIONS_KFV AS SELECT';
47 l_view_text := l_view_text||' ROWID ROW_ID ,INVENTORY_LOCATION_ID,ORGANIZATION_ID,DROPPING_ORDER, SUBINVENTORY_CODE, LOCATION_WEIGHT_UOM_CODE, MAX_WEIGHT, VOLUME_UOM_CODE, ';
48 l_view_text := l_view_text||' MAX_CUBIC_AREA, X_COORDINATE, Y_COORDINATE, Z_COORDINATE, INVENTORY_ACCOUNT_ID, SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5, SEGMENT6, ';
49 l_view_text := l_view_text||' SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10, SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15, SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20, ';
50 l_view_text := l_view_text||' SUMMARY_FLAG, ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ';
51 l_view_text := l_view_text||' ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, REQUEST_ID, PROGRAM_APPLICATION_ID, ';
52 l_view_text := l_view_text||' PROGRAM_ID, PROGRAM_UPDATE_DATE, PROJECT_ID, TASK_ID, PHYSICAL_LOCATION_ID, PICK_UOM_CODE, DIMENSION_UOM_CODE, LENGTH, WIDTH, HEIGHT, LOCATOR_STATUS, ';
53 l_view_text := l_view_text||' STATUS_ID, CURRENT_CUBIC_AREA, AVAILABLE_CUBIC_AREA, CURRENT_WEIGHT, AVAILABLE_WEIGHT, LOCATION_CURRENT_UNITS, LOCATION_AVAILABLE_UNITS, INVENTORY_ITEM_ID, ';
54 l_view_text := l_view_text||' SUGGESTED_CUBIC_AREA, SUGGESTED_WEIGHT, LOCATION_SUGGESTED_UNITS, EMPTY_FLAG, MIXED_ITEMS_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,';
55 l_view_text := l_view_text||' DESCRIPTION, DESCRIPTIVE_TEXT, DISABLE_DATE, INVENTORY_LOCATION_TYPE, PICKING_ORDER, PHYSICAL_LOCATION_CODE, LOCATION_MAXIMUM_UNITS, ALIAS, ';
56
57 IF (l_debug = 1) THEN
58 inv_log_util.TRACE('Created the SQL to create the new VIEW ', 'INVLOC', 9);
59 END IF;
60 --
61 --Forming the concatenated segments\
62 --Fetchng the segment delimter
63 --
64 OPEN c_delim;
65 FETCH c_delim INTO l_delim;
66 CLOSE c_delim;
67
68 IF (l_debug = 1) THEN
69 inv_log_util.TRACE('Opened the cursor c_delim ', 'INVLOC', 9);
70 END IF;
71 ---Fetching the concatenated segments
72 OPEN c_cur_seg(l_compatibility);
73 FETCH c_cur_seg INTO l_segment_name;
74
75 IF (l_debug = 1) THEN
76 inv_log_util.TRACE('Opened the cursor c_cur_seg ', 'INVLOC', 9);
77 END IF;
78 --
79 IF c_cur_seg%NOTFOUND THEN
80
81 CLOSE c_cur_seg;
82 l_concat_segments:=l_concat_segments||''''||' X '||'''';
83 GOTO concat_segs;
84
85 ELSIF l_segment_name in ('SEGMENT19','SEGMENT20') THEN --Added bug#4345239.
86 l_proj_task_segments := ''''||l_delim||'''' ;
87 ELSE
88 l_concat_segments:=l_concat_segments||l_segment_name ;
89 END IF;
90
91 IF (l_debug = 1) THEN
92 inv_log_util.TRACE('About to loop on cursor c_cur_seg to get the concatenated segment.', 'INVLOC', 9);
93 END IF;
94 --
95 --Forming the concatenated segments
96 LOOP
97 FETCH c_cur_seg INTO l_segment_name;
98 EXIT WHEN c_cur_seg%NOTFOUND;
99 IF l_segment_name in ('SEGMENT19','SEGMENT20') THEN --bug#4345239.Added IF Block.
100 l_proj_task_segments := l_proj_task_segments || '||'||''''||l_delim||'''' ;
101 ELSE
102 l_concat_segments := l_concat_segments||'||'||''''||l_delim||''''||'||'||l_segment_name;
103 END IF;
104 END LOOP;
105 CLOSE c_cur_seg;
106 --
107 <<concat_segs>>
108
109 -- l_concat_segments := l_concat_segments||l_proj_task_segments||' CONCATENATED_SEGMENTS '; --Bug4345239.commented.
110
111 IF (l_debug = 1) THEN
112 inv_log_util.TRACE('The concatenated segment is '||l_concat_segments, 'INVLOC', 9);
113 inv_log_util.TRACE('The proj/task segments : '||l_proj_task_segments, 'INVLOC', 9);
114 END IF;
115 --
116
117
118 IF (l_compatibility <> g_compatibility_mode_1157) THEN
119 l_view_text := l_view_text||l_concat_segments||l_proj_task_segments||' CONCATENATED_SEGMENTS '; --Bug4345239.
120 l_view_text := l_view_text|| ','||l_concat_segments||' LOCATOR_SEGMENTS '; --Bug4345239.
121 ELSE
122 --l_view_text := l_view_text||l_concat_segments||l_proj_task_segments||' CONCATENATED_SEGMENTS '; --Bug4345239
123 l_view_text := l_view_text||'INV_PROJECT.GET_LOCSEGS(INVENTORY_LOCATION_ID, ORGANIZATION_ID)'||l_proj_task_segments||' CONCATENATED_SEGMENTS '; --Bug4345239 --Bug#12907080
124 l_view_text := l_view_text|| ', INV_PROJECT.GET_LOCSEGS(INVENTORY_LOCATION_ID, ORGANIZATION_ID) LOCATOR_SEGMENTS '; --Bug5744890
125 END IF;
126
127 l_view_text := l_view_text||' FROM MTL_ITEM_LOCATIONS ';
128 --
129
130 if ad_zd.get_edition('PATCH') is not null then
131 -- an online patch is in progress, return error
132 fnd_message.set_name('FND', 'AD_ZD_DISABLED_FEATURE');
133 raise_application_error ('-20000', fnd_message.get);
134 end if;
135
136 l_sqlid := dbms_sql.open_cursor;
137 dbms_sql.parse(l_sqlid,l_view_text,dbms_sql.native);
138 --
139 l_execute := dbms_sql.execute(l_sqlid);
140 dbms_sql.close_cursor(l_sqlid);
141 --
142 IF (l_debug = 1) THEN
143 inv_log_util.TRACE('Successfully Recompiled the View.', 'INVLOC', 9);
144 END IF;
145 x_errbuf := null;
146 x_retcode := retcode_success;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 --
151 if c_cur_seg%ISOPEN then
152 CLOSE c_cur_seg;
153 end if;
154 if c_delim%ISOPEN then
155 CLOSE c_delim;
156 end if;
157 --
158 IF (l_debug = 1) THEN
159 inv_log_util.TRACE('An Error has occurred ErrNum='||TO_CHAR(SQLCODE)||', Error Msg='||SQLERRM, 'INVLOC', 9);
160 END IF;
161 x_errbuf := SQLERRM;
162 x_retcode := retcode_error;
163 RAISE;
164 END GENERATE_LOCATOR_KFF_VIEW;
165
166 END INV_GEN_LOC_KFV;