[Home] [Help]
PACKAGE BODY: APPS.WSH_ITM_ITEM_SYNC
Source
1 PACKAGE BODY WSH_ITM_ITEM_SYNC AS
2 /* $Header: WSHITISB.pls 120.2.12010000.1 2008/07/29 06:13:37 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ITM_ITEM_SYNC';
5
6 /*===========================================================================+
7 | PROCEDURE |
8 | BUILD_ITEM_WHERE_CLAUSE |
9 | |
10 | DESCRIPTION |
11 | This procedure builds the where clause for the item field |
12 | based on the segments which are enabled for the Item |
13 | flexfield |
14 | |
15 +===========================================================================*/
16
17 -- Private method to Build Item Where Condition
18 PROCEDURE BUILD_ITEM_WHERE_CLAUSE(
19 p_item_from IN VARCHAR2,
20 p_item_to IN VARCHAR2,
21 l_Item_Table IN OUT NOCOPY WSH_ITM_QUERY_CUSTOM.g_CondnValTableType
22 )
23
24 IS
25 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
26 l_structure_rec FND_FLEX_KEY_API.structure_type;
27 l_segment_rec FND_FLEX_KEY_API.segment_type;
28 l_segment_tbl FND_FLEX_KEY_API.segment_list;
29
30 l_Item_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
31 l_Item_Condn22Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
32
33 l_segment_number NUMBER;
34 l_mstk_segs VARCHAR2(850);
35 l_mcat_segs VARCHAR2(850);
36 l_mcat_f VARCHAR2(2000);
37 l_mcat_w1 VARCHAR2(2000);
38 l_mcat_w2 VARCHAR2(2000);
39 l_mstk_w VARCHAR2(2000);
40 --
41 l_debug_on BOOLEAN;
42 --
43 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BUILD_ITEM_WHERE_CLAUSE';
44 --
45
46 BEGIN
47 --
48 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
49 --
50 IF l_debug_on IS NULL
51 THEN
52 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
53 END IF;
54 --
55 --
56 -- Debug Statements
57 --
58 IF l_debug_on THEN
59 WSH_DEBUG_SV.push(l_module_name);
60 --
61 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_FROM',P_ITEM_FROM);
62 WSH_DEBUG_SV.log(l_module_name,'P_ITEM_TO',P_ITEM_TO);
63
64 END IF;
65 --
66
67 FND_FLEX_KEY_API.set_session_mode('customer_data');
68 -- retrieve system item concatenated flexfield
69 l_mstk_segs := '';
70 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield('INV', 'MSTK');
71 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, 101);
72
73 FND_FLEX_KEY_API.get_segments
74 ( flexfield => l_flexfield_rec
75 , structure => l_structure_rec
76 , nsegments => l_segment_number
77 , segments => l_segment_tbl
78 );
79
80 FOR l_idx IN 1..l_segment_number LOOP
81 l_segment_rec := FND_FLEX_KEY_API.find_segment
82 ( l_flexfield_rec
83 , l_structure_rec
84 , l_segment_tbl(l_idx)
85 );
86 l_mstk_segs := l_mstk_segs ||'B.'||l_segment_rec.column_name;
87 IF l_idx < l_segment_number THEN
88 l_mstk_segs := l_mstk_segs||'||'||''''||l_structure_rec.segment_separator||''''||'||';
89 END IF;
90 END LOOP;
91
92 IF p_item_from IS NOT NULL AND p_item_to IS NOT NULL THEN
93 l_mstk_w := ' AND '||l_mstk_segs||' >= :b_item_from ';
94 l_Item_Condn2Tab(1).g_varchar_val := p_item_from;
95 l_Item_Condn2Tab(1).g_Bind_Literal := ':b_item_from';
96 --
97 -- Debug Statements
98 --
99 IF l_debug_on THEN
100 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
101 END IF;
102 --
103 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, l_mstk_w, l_Item_Condn2Tab, 'VARCHAR');
104
105 l_mstk_w := ' AND '||l_mstk_segs||' <= :b_item_to ';
106 l_Item_Condn22Tab(1).g_varchar_val := p_item_to;
107 l_Item_Condn22Tab(1).g_Bind_Literal := ':b_item_to';
108 --
109 -- Debug Statements
110 --
111 IF l_debug_on THEN
112 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
113 END IF;
114 --
115 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, l_mstk_w, l_Item_Condn22Tab, 'VARCHAR');
116 ELSIF p_item_from IS NOT NULL AND p_item_to IS NULL THEN
117 l_mstk_w := ' AND '||l_mstk_segs||' >= :b_item_from';
118 l_Item_Condn2Tab(1).g_varchar_val := p_item_from;
119 l_Item_Condn2Tab(1).g_Bind_Literal := ':b_item_from';
120 --
121 -- Debug Statements
122 --
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
125 END IF;
126 --
127 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, l_mstk_w, l_Item_Condn2Tab, 'VARCHAR');
128 ELSIF p_item_from IS NULL AND p_item_to IS NOT NULL THEN
129 l_mstk_w := ' AND '||l_mstk_segs||' <= :b_item_to';
130 l_Item_Condn2Tab(1).g_varchar_val := p_item_to;
131 l_Item_Condn2Tab(1).g_Bind_Literal := ':b_item_to';
132 --
133 -- Debug Statements
134 --
135 IF l_debug_on THEN
136 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
137 END IF;
138 --
139 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, l_mstk_w, l_Item_Condn2Tab, 'VARCHAR');
140 END IF;
141 end BUILD_ITEM_WHERE_CLAUSE;
142
143
144
145 /*===========================================================================+
146 | PROCEDURE |
147 | POPULATE_DATA |
148 | |
149 | DESCRIPTION |
150 | This procedure is called when the Item Synchronization |
151 | Concurrent Program is launched. It populates the data |
152 | into WSH_ITM_REQUEST_CONTROL and WSH_ITM_ITEMS |
153 | based on the parameters selected. |
154 | |
155 +===========================================================================*/
156
157
158
159 PROCEDURE POPULATE_DATA (
160 errbuf OUT NOCOPY VARCHAR2,
161 retcode OUT NOCOPY NUMBER,
162 p_from_organization_code IN VARCHAR2,
163 p_to_organization_code IN VARCHAR2,
164 p_from_item IN VARCHAR2 ,
165 p_to_item IN VARCHAR2,
166 p_user_item_type IN VARCHAR2,
167 p_created_n_days IN NUMBER,
168 p_updated_n_days IN NUMBER
169 )IS
170
171 l_SQLQuery VARCHAR2(12000) := ' SELECT '||
172 'B.INVENTORY_ITEM_ID ITEM_ID,'||
173 'WSH_UTIL_CORE.GET_ITEM_NAME (B.INVENTORY_ITEM_ID, B.ORGANIZATION_ID) PRODUCT_CODE, '||
174 'T.DESCRIPTION DESCRIPTION, '||
175 'T.LONG_DESCRIPTION LONG_DESCRIPTION, '||
176 'POHC.HAZARD_CLASS HAZARD_CLASS, '||
177 'B.ORGANIZATION_ID ORGANIZATION_ID, '||
178 'B.SOURCE_ORGANIZATION_ID SRC_ORGANIZATION_ID,'||
179 'FLV.MEANING ITEM_TYPE, '||
180 'B.PRIMARY_UOM_CODE UNIT_OF_MEASURE, '||
181 'B.LIST_PRICE_PER_UNIT ITEM_VALUE, '||
182 'GLPV.CURRENCY_CODE INCOMING_CURRENCY, '||
183 'HL.COUNTRY COUNTRY, '||
184 ' B.ATTRIBUTE1 , B.ATTRIBUTE2 , B.ATTRIBUTE3 , B.ATTRIBUTE4 , '||
185 ' B.ATTRIBUTE5 , B.ATTRIBUTE6 , B.ATTRIBUTE7 , B.ATTRIBUTE8 ,'||
186 ' B.ATTRIBUTE9 , B.ATTRIBUTE10, B.ATTRIBUTE11, B.ATTRIBUTE12,'||
187 ' B.ATTRIBUTE13, B.ATTRIBUTE14, B.ATTRIBUTE15, '||
188 ' OOD.OPERATING_UNIT '||
189 ' FROM '||
190 ' MTL_SYSTEM_ITEMS_TL T , '||
191 ' MTL_SYSTEM_ITEMS_B B, '||
192 ' HR_ALL_ORGANIZATION_UNITS HU,'||
193 ' HR_LOCATIONS HL, '||
194 ' GL_LEDGERS_PUBLIC_V GLPV, '||
195 ' PO_HAZARD_CLASSES_TL POHC, '||
196 ' FND_LANGUAGES FNDL, '||
197 ' FND_LOOKUP_VALUES FLV, '||
198 ' MTL_PARAMETERS MP, '||
199 ' ORG_ORGANIZATION_DEFINITIONS OOD '||
200 'WHERE '||
201 ' B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID '||
202 ' AND B.ORGANIZATION_ID = T.ORGANIZATION_ID '||
203 ' AND HU.LOCATION_ID = HL.LOCATION_ID '||
204 ' AND HU.ORGANIZATION_ID = B.ORGANIZATION_ID '||
205 ' AND GLPV.LEDGER_ID = OOD.SET_OF_BOOKS_ID '||
206 ' AND POHC.HAZARD_CLASS_ID(+) = B.HAZARD_CLASS_ID'||
207 ' AND FNDL.INSTALLED_FLAG = ''B'' '||
208 ' AND B.INVENTORY_ITEM_STATUS_CODE <> ''Inactive'' '||
209 ' AND FNDL.LANGUAGE_CODE = T.LANGUAGE '||
210 ' AND FNDL.LANGUAGE_CODE = NVL(POHC.LANGUAGE,FNDL.LANGUAGE_CODE) '||
211 ' AND FLV.LOOKUP_TYPE = ''ITEM_TYPE'' '||
212 ' AND FLV.LANGUAGE = FNDL.LANGUAGE_CODE '||
213 ' AND FLV.VIEW_APPLICATION_ID = 3 '||
214 ' AND FLV.LOOKUP_CODE = B.ITEM_TYPE'||
215 ' AND B.ORGANIZATION_ID = MP.ORGANIZATION_ID '||
216 ' AND OOD.ORGANIZATION_ID = MP.ORGANIZATION_ID' ;
217
218
219 l_Item_Table WSH_ITM_QUERY_CUSTOM.g_CondnValTableType;
220
221 l_Item_Condn1Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
222 l_Item_Condn11Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
223 l_Item_Condn2Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
224 l_item_where_clause VARCHAR(1000);
225 l_Item_Condn3Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
226 l_Item_Condn4Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
227 l_Item_Condn5Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
228 l_Item_Condn6Tab WSH_ITM_QUERY_CUSTOM.g_ValueTableType;
229
230 l_tempStr VARCHAR2(10000) := ' ';
231 l_CursorID NUMBER;
232 l_ignore NUMBER;
233
234 --PL/SQL Table used for Bulk Select
235 l_num_invItem_tab DBMS_SQL.Number_Table;
236 l_varchar_PrdCode_tab DBMS_SQL.Varchar2_Table;
237 l_varchar_Desc_tab DBMS_SQL.Varchar2_Table;
238 l_varchar_LongDesc_tab DBMS_SQL.Clob_Table;
239 l_varchar_hazClass_tab DBMS_SQL.Varchar2_Table;
240 l_num_orgId_tab DBMS_SQL.Number_Table;
241 l_num_srcOrgId_tab DBMS_SQL.Number_Table;
242 l_varchar_ItmType_tab DBMS_SQL.Varchar2_Table;
243 l_varchar_UOM_tab DBMS_SQL.Varchar2_Table;
244 l_num_itmVal_tab DBMS_SQL.Number_Table;
245 l_varchar_Curr_tab DBMS_SQL.Varchar2_Table;
246 l_varchar_Coun_tab DBMS_SQL.Varchar2_Table;
247 l_LanguageCode VARCHAR2(4);
248
249 l_varchar_Attrib1_tab DBMS_SQL.Varchar2_Table;
250 l_varchar_Attrib2_tab DBMS_SQL.Varchar2_Table;
251 l_varchar_Attrib3_tab DBMS_SQL.Varchar2_Table;
252 l_varchar_Attrib4_tab DBMS_SQL.Varchar2_Table;
253 l_varchar_Attrib5_tab DBMS_SQL.Varchar2_Table;
254 l_varchar_Attrib6_tab DBMS_SQL.Varchar2_Table;
255 l_varchar_Attrib7_tab DBMS_SQL.Varchar2_Table;
256 l_varchar_Attrib8_tab DBMS_SQL.Varchar2_Table;
257 l_varchar_Attrib9_tab DBMS_SQL.Varchar2_Table;
258 l_varchar_Attrib10_tab DBMS_SQL.Varchar2_Table;
259 l_varchar_Attrib11_tab DBMS_SQL.Varchar2_Table;
260 l_varchar_Attrib12_tab DBMS_SQL.Varchar2_Table;
261 l_varchar_Attrib13_tab DBMS_SQL.Varchar2_Table;
262 l_varchar_Attrib14_tab DBMS_SQL.Varchar2_Table;
263 l_varchar_Attrib15_tab DBMS_SQL.Varchar2_Table;
264 l_num_Operunit_tab DBMS_SQL.Number_Table;
265
266 --For Insert to ITM Inteface Tables
267 l_num_ReqCtrl_tab DBMS_SQL.Number_Table;
268 l_num_ItmReqCtrl_tab DBMS_SQL.Number_Table;
269 l_num_invItemID_tab DBMS_SQL.Number_Table;
270
271 l_tempInvItem NUMBER := -999;
272
273 l_user_id NUMBER;
274 l_login_id NUMBER;
275 l_temp BOOLEAN;
276
277
278
279 --
280 l_debug_on BOOLEAN;
281 --
282 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_DATA';
283 --
284 BEGIN
285 --Frame draft SQL
286
287 --
288 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
289 --
290 IF l_debug_on IS NULL
291 THEN
292 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
293 END IF;
294 --
295 --
296 -- Debug Statements
297 --
298 IF l_debug_on THEN
299 WSH_DEBUG_SV.push(l_module_name);
300 --
301 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ORGANIZATION_CODE',P_FROM_ORGANIZATION_CODE);
302 WSH_DEBUG_SV.log(l_module_name,'P_TO_ORGANIZATION_CODE',P_TO_ORGANIZATION_CODE);
303 WSH_DEBUG_SV.log(l_module_name,'P_FROM_ITEM',P_FROM_ITEM);
304 WSH_DEBUG_SV.log(l_module_name,'P_TO_ITEM',P_TO_ITEM);
305 WSH_DEBUG_SV.log(l_module_name,'P_USER_ITEM_TYPE',P_USER_ITEM_TYPE);
306 WSH_DEBUG_SV.log(l_module_name,'P_CREATED_N_DAYS',P_CREATED_N_DAYS);
307 WSH_DEBUG_SV.log(l_module_name,'P_UPDATED_N_DAYS',P_UPDATED_N_DAYS);
308 END IF;
309 --
310
311 -- Fetch user and login information
312 l_user_id := FND_GLOBAL.USER_ID;
313 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
314
315 IF p_from_organization_code IS NOT NULL AND p_to_organization_code IS NOT NULL THEN
316 --Adding a ORGANIZATION_CODE Condn
317 l_Item_Condn1Tab(1).g_varchar_val := p_from_organization_code;
318 l_Item_Condn1Tab(1).g_Bind_Literal := ':b_from_organization_code';
319 --
320 -- Debug Statements
321 --
322 IF l_debug_on THEN
323 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
324 END IF;
325 --
326 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND MP.ORGANIZATION_CODE >= :b_from_organization_code', l_Item_Condn1Tab, 'VARCHAR');
327
328 l_Item_Condn11Tab(1).g_varchar_val := p_to_organization_code;
329 l_Item_Condn11Tab(1).g_Bind_Literal := ':b_to_organization_code';
330 --
331 -- Debug Statements
332 --
333 IF l_debug_on THEN
334 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
335 END IF;
336 --
337 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND MP.ORGANIZATION_CODE <= :b_to_organization_code', l_Item_Condn11Tab, 'VARCHAR');
338
339 ELSIF p_from_organization_code IS NOT NULL THEN
340 --Adding a ORGANIZATION_CODE Condn
341 l_Item_Condn1Tab(1).g_varchar_val := p_from_organization_code;
342 l_Item_Condn1Tab(1).g_Bind_Literal := ':b_from_organization_code';
343 --
344 -- Debug Statements
345 --
346 IF l_debug_on THEN
347 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
348 END IF;
349 --
350 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND MP.ORGANIZATION_CODE >= :b_from_organization_code ', l_Item_Condn1Tab, 'VARCHAR');
351
352 ELSIF p_to_organization_code IS NOT NULL THEN
353 --Adding a ORGANIZATION_CODE Condn
354 l_Item_Condn1Tab(1).g_varchar_val := p_to_organization_code;
355 l_Item_Condn1Tab(1).g_Bind_Literal := ':b_to_organization_code';
356 --
357 -- Debug Statements
358 --
359 IF l_debug_on THEN
360 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
361 END IF;
362 --
363 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND MP.ORGANIZATION_CODE <= :b_to_organization_code', l_Item_Condn1Tab, 'VARCHAR');
364 END IF;
365
366 IF p_from_item IS NOT NULL OR p_to_item IS NOT NULL THEN
367 --
368 -- Debug Statements
369 --
370 IF l_debug_on THEN
371 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_ITEM_SYNC.BUILD_ITEM_WHERE_CLUASE',WSH_DEBUG_SV.C_PROC_LEVEL);
372 END IF;
373 --
374 BUILD_ITEM_WHERE_CLAUSE(p_from_item,p_to_item,l_Item_Table);
375 END IF;
376 --Adding ITEM Type Condn
377 IF p_user_item_type IS NOT NULL THEN
378 l_Item_Condn3Tab(1).g_varchar_val := p_user_item_type;
379 l_Item_Condn3Tab(1).g_Bind_Literal := ':b_user_item_type';
380 --
381 -- Debug Statements
382 --
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
385 END IF;
386 --
387 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND B.ITEM_TYPE = :b_user_item_type ', l_Item_Condn3Tab, 'VARCHAR');
388 END IF;
389
390 --Adding Creates LAst N Days Condn
391 IF p_created_n_days IS NOT NULL THEN
392 l_Item_Condn4Tab(1).g_number_val := p_created_n_days;
393 l_Item_Condn4Tab(1).g_Bind_Literal := ':b_created_n_days';
394 --
395 -- Debug Statements
396 --
397 IF l_debug_on THEN
398 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
399 END IF;
400 --
401 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND B.CREATION_DATE >= SYSDATE - :b_created_n_days ', l_Item_Condn4Tab, 'NUMBER');
402 END IF;
403
404 --Adding Creates LAst N Days Condn
405 IF p_updated_n_days IS NOT NULL THEN
406 l_Item_Condn5Tab(1).g_number_val := p_updated_n_days;
407 l_Item_Condn5Tab(1).g_Bind_Literal := ':b_updated_n_days';
408 --
409 -- Debug Statements
410 --
411 IF l_debug_on THEN
412 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.ADD_CONDITION',WSH_DEBUG_SV.C_PROC_LEVEL);
413 END IF;
414 --
415 WSH_ITM_QUERY_CUSTOM.ADD_CONDITION(l_Item_Table, ' AND B.LAST_UPDATE_DATE >= SYSDATE - :b_updated_n_days ', l_Item_Condn5Tab, 'NUMBER');
416 END IF;
417
418
419 --Printing the SQL before calling Customization procedure
420 FOR I IN 1..l_Item_Table.COUNT
421 LOOP
422 l_tempStr := l_tempStr || ' ' || l_item_table(i).g_Condn_Qry;
423 END LOOP;
424 l_tempStr := l_SQLQuery || l_tempStr || ' ORDER BY B.INVENTORY_ITEM_ID';
425 IF l_debug_on THEN
426 WSH_DEBUG_SV.LOG (l_module_name, 'Query ', l_tempStr, WSH_DEBUG_SV.C_STMT_LEVEL);
427 END IF;
428 -- Clearing sql query
429 l_tempStr := '';
430
431
432 --Call to custom Procedure which could be edited by the Customer.
433 --
434 -- Debug Statements
435 --
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_CUSTOMIZE.ALTER_ITEM_SYNC',WSH_DEBUG_SV.C_PROC_LEVEL);
438 END IF;
439 --
440 WSH_ITM_CUSTOMIZE.ALTER_ITEM_SYNC(l_Item_Table);
441
442 --Create SQL and bind parameters
443 FOR I IN 1..l_Item_Table.COUNT
444 LOOP
445 l_tempStr := l_tempStr || ' ' || l_item_table(i).g_Condn_Qry;
446 END LOOP;
447
448 --Concatenating Main SQL with Condition SQL
449 l_SQLQuery := l_SQLQuery || l_tempStr || ' ORDER BY B.INVENTORY_ITEM_ID';
450
451 IF l_debug_on THEN
452 WSH_DEBUG_SV.LOG (l_module_name, 'Query ', l_SQLQuery, WSH_DEBUG_SV.C_STMT_LEVEL);
453 END IF;
454
455
456 -- Parse cursor
457 l_CursorID := DBMS_SQL.Open_Cursor;
458 DBMS_SQL.PARSE(l_CursorID, l_SQLQuery, DBMS_SQL.v7);
459
460 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 1, l_num_invItem_tab, 100, 0);
461 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 2, l_varchar_PrdCode_tab, 100, 0);
462 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 3, l_varchar_Desc_tab, 100, 0);
463 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 4, l_varchar_LongDesc_tab, 100, 0);
464 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 5, l_varchar_hazClass_tab, 100, 0);
465 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 6, l_num_orgId_tab, 100, 0);
466 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 7, l_num_srcOrgId_tab, 100, 0);
467 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 8, l_varchar_ItmType_tab, 100, 0);
468 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 9, l_varchar_UOM_tab, 100, 0);
469 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 10, l_num_itmVal_tab, 100, 0);
470 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 11, l_varchar_Curr_tab, 100, 0);
471 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 12, l_varchar_Coun_tab, 100, 0);
472 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 13, l_varchar_Attrib1_tab, 100, 0);
473 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 14, l_varchar_Attrib2_tab, 100, 0);
474 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 15, l_varchar_Attrib3_tab, 100, 0);
475 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 16, l_varchar_Attrib4_tab, 100, 0);
476 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 17, l_varchar_Attrib5_tab, 100, 0);
477 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 18, l_varchar_Attrib6_tab, 100, 0);
478 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 19, l_varchar_Attrib7_tab, 100, 0);
479 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 20, l_varchar_Attrib8_tab, 100, 0);
480 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 21, l_varchar_Attrib9_tab, 100, 0);
481 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 22, l_varchar_Attrib10_tab, 100, 0);
482 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 23, l_varchar_Attrib11_tab, 100, 0);
483 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 24, l_varchar_Attrib12_tab, 100, 0);
484 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 25, l_varchar_Attrib13_tab, 100, 0);
485 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 26, l_varchar_Attrib14_tab, 100, 0);
486 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 27, l_varchar_Attrib15_tab, 100, 0);
487 DBMS_SQL.DEFINE_ARRAY(l_CursorID, 28, l_num_Operunit_tab , 100, 0);
488
489 --
490 -- Debug Statements
491 --
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ITM_QUERY_CUSTOM.BIND_VALUES',WSH_DEBUG_SV.C_PROC_LEVEL);
494 END IF;
495 --
496 WSH_ITM_QUERY_CUSTOM.BIND_VALUES(l_Item_Table,l_CursorID);
497
498
499 IF l_debug_on THEN
500 WSH_DEBUG_SV.LOGMSG (l_module_name,'Successfull bind values',WSH_DEBUG_SV.C_STMT_LEVEL);
501 END IF;
502
503 l_ignore := DBMS_SQL.EXECUTE(l_CursorID);
504
505 --Bulk Collect customized SQL
506 LOOP
507 l_ignore := DBMS_SQL.FETCH_ROWS(l_CursorID);
508
509 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_num_invItem_tab);
510 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, l_varchar_PrdCode_tab);
511 DBMS_SQL.COLUMN_VALUE(l_CursorID, 3, l_varchar_Desc_tab);
512 DBMS_SQL.COLUMN_VALUE(l_CursorID, 4, l_varchar_LongDesc_tab);
513 DBMS_SQL.COLUMN_VALUE(l_CursorID, 5, l_varchar_hazClass_tab);
514 DBMS_SQL.COLUMN_VALUE(l_CursorID, 6, l_num_orgId_tab);
515 DBMS_SQL.COLUMN_VALUE(l_CursorID, 7, l_num_srcOrgId_tab);
516 DBMS_SQL.COLUMN_VALUE(l_CursorID, 8, l_varchar_ItmType_tab);
517 DBMS_SQL.COLUMN_VALUE(l_CursorID, 9, l_varchar_UOM_tab);
518 DBMS_SQL.COLUMN_VALUE(l_CursorID, 10, l_num_itmVal_tab);
519 DBMS_SQL.COLUMN_VALUE(l_CursorID, 11, l_varchar_Curr_tab);
520 DBMS_SQL.COLUMN_VALUE(l_CursorID, 12, l_varchar_Coun_tab);
521 DBMS_SQL.COLUMN_VALUE(l_CursorID, 13, l_varchar_Attrib1_tab);
522 DBMS_SQL.COLUMN_VALUE(l_CursorID, 14, l_varchar_Attrib2_tab);
523 DBMS_SQL.COLUMN_VALUE(l_CursorID, 15, l_varchar_Attrib3_tab);
524 DBMS_SQL.COLUMN_VALUE(l_CursorID, 16, l_varchar_Attrib4_tab);
525 DBMS_SQL.COLUMN_VALUE(l_CursorID, 17, l_varchar_Attrib5_tab);
526 DBMS_SQL.COLUMN_VALUE(l_CursorID, 18, l_varchar_Attrib6_tab);
527 DBMS_SQL.COLUMN_VALUE(l_CursorID, 19, l_varchar_Attrib7_tab);
528 DBMS_SQL.COLUMN_VALUE(l_CursorID, 20, l_varchar_Attrib8_tab);
529 DBMS_SQL.COLUMN_VALUE(l_CursorID, 21, l_varchar_Attrib9_tab);
530 DBMS_SQL.COLUMN_VALUE(l_CursorID, 22, l_varchar_Attrib10_tab);
531 DBMS_SQL.COLUMN_VALUE(l_CursorID, 23, l_varchar_Attrib11_tab);
532 DBMS_SQL.COLUMN_VALUE(l_CursorID, 24, l_varchar_Attrib12_tab);
533 DBMS_SQL.COLUMN_VALUE(l_CursorID, 25, l_varchar_Attrib13_tab);
534 DBMS_SQL.COLUMN_VALUE(l_CursorID, 26, l_varchar_Attrib14_tab);
535 DBMS_SQL.COLUMN_VALUE(l_CursorID, 27, l_varchar_Attrib15_tab);
536 DBMS_SQL.COLUMN_VALUE(l_CursorID, 28, l_num_Operunit_tab );
537 EXIT WHEN l_ignore <> 100;
538 END LOOP;
539 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
540
541 IF l_debug_on THEN
542 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Items queried: ' , l_num_invItem_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
543 END IF;
544
545
546 IF l_num_invItem_tab.COUNT <> 0 THEN
547 --Bulk Insert into Interface Tables Appropriately
548 FOR i in l_num_invItem_tab.FIRST..l_num_invItem_tab.LAST
549 LOOP
550 --Check if New Item
551 IF l_num_invItem_tab(i) <> l_tempInvItem THEN
552 l_tempInvItem := l_num_invItem_tab(i);
553 --Create a new Request Control Seq
554 select WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
555 into
556 l_num_ReqCtrl_tab(l_num_ReqCtrl_tab.COUNT + 1)
557 from dual;
558
559 --Save Item ID in RC Record
560 l_num_invItemID_tab(l_num_invItemID_tab.COUNT+1) := l_tempInvItem;
561
562 END IF;
563 --Saving Request Control for Child WSH_ITM_ITEM Table
564 l_num_ItmReqCtrl_tab(i) := l_num_ReqCtrl_tab(l_num_ReqCtrl_tab.COUNT);
565 END LOOP;
566
567 --Getting the Base Language into the variable
568 SELECT LANGUAGE_CODE INTO l_LanguageCode FROM
569 FND_LANGUAGES WHERE
570 INSTALLED_FLAG = 'B';
571 IF l_debug_on THEN
572 WSH_DEBUG_SV.LOG (l_module_name, 'Base Language : ', l_LanguageCode, WSH_DEBUG_SV.C_STMT_LEVEL);
573 END IF;
574
575 IF l_debug_on THEN
576 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Request Controls to be inserted : ' , l_num_ReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
577 END IF;
578
579 --Bulk Insert to Request Control Table
580 --ApplicationID = 702 (Bill of Materials)
581 FORALL i IN l_num_ReqCtrl_tab.FIRST..l_num_ReqCtrl_tab.LAST
582 INSERT INTO WSH_ITM_REQUEST_CONTROL(
583 REQUEST_CONTROL_ID,
584 APPLICATION_ID,
585 PROCESS_FLAG,
586 SERVICE_TYPE_CODE,
587 ORIGINAL_SYSTEM_REFERENCE,
588 LANGUAGE_CODE,
589 LAST_UPDATE_DATE,
590 LAST_UPDATED_BY,
591 CREATION_DATE,
592 CREATED_BY,
593 LAST_UPDATE_LOGIN
594 )
595 VALUES(
596 l_num_ReqCtrl_tab(i),
597 702,
598 0,
599 'ITEM_SYNC',
600 l_num_invItemID_tab(i),
601 l_LanguageCode,
602 SYSDATE,
603 l_user_id,
604 SYSDATE,
605 l_user_id,
606 l_login_id
607 );
608
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.LOG (l_module_name, 'Number of Items to be inserted : ' , l_num_ItmReqCtrl_tab.COUNT,WSH_DEBUG_SV.C_STMT_LEVEL);
611 END IF;
612
613 --Bulk Insert into Items Table
614 FORALL I IN l_num_ItmReqCtrl_tab.FIRST..l_num_ItmReqCtrl_tab.LAST
615 INSERT INTO WSH_ITM_ITEMS (
616 ITEM_ID,
617 REQUEST_CONTROL_ID,
618 INVENTORY_ITEM_ID,
619 ORGANIZATION_ID,
620 PRODUCT_CODE,
621 SRC_ORGANIZATION_ID,
622 DESCRIPTION,
623 LONG_DESCRIPTION,
624 HAZARD_CLASS,
625 ITEM_TYPE,
626 UNIT_OF_MEASURE,
627 ITEM_VALUE,
628 INCOMING_CURRENCY,
629 COUNTRY,
630 ATTRIBUTE1_VALUE,
631 ATTRIBUTE2_VALUE,
632 ATTRIBUTE3_VALUE,
633 ATTRIBUTE4_VALUE,
634 ATTRIBUTE5_VALUE,
635 ATTRIBUTE6_VALUE,
636 ATTRIBUTE7_VALUE,
637 ATTRIBUTE8_VALUE,
638 ATTRIBUTE9_VALUE,
639 ATTRIBUTE10_VALUE,
640 ATTRIBUTE11_VALUE,
641 ATTRIBUTE12_VALUE,
642 ATTRIBUTE13_VALUE,
643 ATTRIBUTE14_VALUE,
644 ATTRIBUTE15_VALUE,
645 LAST_UPDATE_DATE,
646 LAST_UPDATED_BY,
647 CREATION_DATE,
648 CREATED_BY,
649 LAST_UPDATE_LOGIN,
650 OPERATING_UNIT
651 )
652 VALUES(
653 WSH_ITM_ITEMS_S.NEXTVAL,
654 l_num_ItmReqCtrl_tab(i),
655 l_num_invItem_tab(i),
656 l_num_orgId_tab(i),
657 l_varchar_PrdCode_tab(i),
658 l_num_srcOrgId_tab(i),
659 l_varchar_Desc_tab(i),
660 l_varchar_LongDesc_tab(i),
661 l_varchar_hazClass_tab(i),
662 l_varchar_ItmType_tab(i),
663 l_varchar_UOM_tab(i),
664 l_num_itmVal_tab(i),
665 l_varchar_Curr_tab(i),
666 l_varchar_Coun_tab(i),
667 l_varchar_Attrib1_tab(i),
668 l_varchar_Attrib2_tab(i),
669 l_varchar_Attrib3_tab(i),
670 l_varchar_Attrib4_tab(i),
671 l_varchar_Attrib5_tab(i),
672 l_varchar_Attrib6_tab(i),
673 l_varchar_Attrib7_tab(i),
674 l_varchar_Attrib8_tab(i),
675 l_varchar_Attrib9_tab(i),
676 l_varchar_Attrib10_tab(i),
677 l_varchar_Attrib11_tab(i),
678 l_varchar_Attrib12_tab(i),
679 l_varchar_Attrib13_tab(i),
680 l_varchar_Attrib14_tab(i),
681 l_varchar_Attrib15_tab(i),
682 SYSDATE,
683 l_user_id,
684 SYSDATE,
685 l_user_id,
686 l_login_id,
687 l_num_Operunit_tab(i)
688 );
689 END IF;
690
691 --
692 -- Debug Statements
693 --
694 IF l_debug_on THEN
695 WSH_DEBUG_SV.pop(l_module_name);
696 END IF;
697 --
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error in procedure WSH_ITM_CUSTOMIZE.POPULATE_DATA');
702 errbuf := 'Error in procedure WSH_ITM_CUSTOMIZE.POPULATE_DATA failed with unexpected error';
703 retcode := '2';
704 IF l_debug_on THEN
705 WSH_DEBUG_SV.logmsg(l_module_name,'The unexpected Error Code ' || SQLCODE || ' : ' || SQLERRM);
706 WSH_DEBUG_SV.pop(l_module_name);
707 END IF;
708 END POPULATE_DATA;
709 END WSH_ITM_ITEM_SYNC;