[Home] [Help]
PACKAGE BODY: APPS.ICX_ITEM_DIAG_PVT
Source
1 PACKAGE body ICX_ITEM_DIAG_PVT AS
2 /* $Header: ICX_ITEM_DIAG_PVT.plb 120.7.12020000.2 2013/02/11 13:33:30 vegajula noship $*/
3 procedure create_missing_data;
4
5 PROCEDURE logUnexpectedException
6 ( p_pkg_name IN VARCHAR2 ,
7 p_proc_name IN VARCHAR2 ,
8 p_log_string IN VARCHAR2
9 )
10 IS
11 l_err_loc PLS_INTEGER;
12 BEGIN
13 l_err_loc := 100;
14 FND_FILE.PUT_LINE(FND_FILE.LOG, 'icx.plsql.' || UPPER(p_pkg_name) || '.' || UPPER(p_proc_name)|| 'EXCEPTION::'|| p_log_string);
15 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
16 l_err_loc := 200;
17 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,'icx.plsql.' || UPPER(p_pkg_name) || '.' || UPPER(p_proc_name) , p_log_string);
18 l_err_loc := 300;
19 END IF;
20 EXCEPTION
21 WHEN OTHERS THEN
22 l_err_loc := 400;
23 END logUnexpectedException;
24 procedure add_error(char1 IN VARCHAR2 default null, --error name
25 num1 IN NUMBER default null, --inventory_item_id
26 num2 IN NUMBER default null, --organization_id
27 num3 IN NUMBER default null, --po_line_id
28 num4 IN NUMBER default null, --org_id
29 char2 IN VARCHAR2 default null, --req template name
30 char3 IN VARCHAR2 default null, --req template line num
31 char4 IN VARCHAR2 default null,
32 char5 IN VARCHAR2 default null)
33 is
34 begin
35 INSERT INTO po_session_gt
36 (index_char1
37 ,num1
38 ,num2
39 ,num3
40 ,num4
41 ,char1
42 ,char2
43 ,char3
44 ,char4
45 ,char5)
46 VALUES
47 (g_error_key
48 ,num1
49 ,num2
50 ,num3
51 ,num4
52 ,char1
53 ,char2
54 ,char3
55 ,char4
56 ,char5);
57
58 exception when others then
59 logUnexpectedException (g_pkg_name, 'add_error','Exception '||sqlerrm||' code='||sqlcode);
60 end add_error;
61
62 FUNCTION Split
63 (
64 in_str IN VARCHAR2, -- input string
65 token_num IN PLS_INTEGER, -- token number
66 delim IN VARCHAR2 DEFAULT ' ' -- separator character
67 )
68 RETURN VARCHAR2
69 IS
70 l_str VARCHAR2(32767) := delim || in_str ;
71 l_int PLS_INTEGER ;
72 l_int2 PLS_INTEGER ;
73 BEGIN
74 l_int := INSTR( l_str, delim, 1, token_num ) ;
75 IF l_int > 0 THEN
76 l_int2 := INSTR( l_str, delim, 1, token_num + 1) ;
77 IF l_int2 = 0 THEN l_int2 := LENGTH( l_str ) + 1 ; END IF ;
78 RETURN( SUBSTR( l_str, l_int+1, l_int2 - l_int-1 ) ) ;
79 ELSE
80 RETURN NULL ;
81 END IF ;
82 exception when others then
83
84 logUnexpectedException (g_pkg_name, 'Split','Exception sqlerrm'||sqlerrm||' code='||sqlcode);
85
86 END Split;
87
88 --120 for should_version contains 12000000 ; 121 for should_version contains 12010000 ; 12 for should_version like 120.x only
89 -- -1 for garbage
90 FUNCTION update_num
91 (
92 p_version VARCHAR2)
93 RETURN NUMBER
94 IS
95 BEGIN
96
97 If ( p_version LIKE '120.%' AND NOT p_version LIKE '120.%.%' )
98 or p_version LIKE '120.%.%.1' THEN
99 RETURN 12;
100 elsif INSTR( p_version , '.12000000.' ) > 0 THEN
101 RETURN 120;
102 elsif INSTR( p_version , '.12010000.' ) > 0 THEN
103 RETURN 121;
104 END IF;
105 RETURN -1;
106 exception when others then
107 logUnexpectedException (g_pkg_name, 'update_num','Exception sqlerrm'||sqlerrm||' code='||sqlcode);
108 END update_num;
109 -- num1 is 0 for instance_versions equal or greater versions than should_version.
110 -- num2 is 120 for should_version contains 12000000 ; 121 for should_version contains 12010000 ; 12 for should_version like 120.x only
111 -- num3 is 120 for instance_versions contains 12000000 ; 121 for instance_versions contains 12010000 ; 12 for instance_versions like 120.x only
112 -- char4 is apply patch or not
113 FUNCTION check_file
114 RETURN VARCHAR2
115 IS
116 l_ctr NUMBER;
117 l_shld_ver varchar2(100);
118 l_inst_ver varchar2(100);
119 l_num2 number;
120 l_num3 number;
121 l_apply_patch varchar2(5):='FALSE';
122 l_br_iv varchar2(100); -- rep 69 of 120.69.12010000.2 of instance version
123 l_br_sv varchar2(100); -- rep 69 of 120.69.12010000.2 of should have version
124 l_api_name varchar2(30):= 'check_file';
125 BEGIN
126 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start check_file');
127 forall i IN 1 .. g_file_count
128 UPDATE po_session_gt
129 SET num3 = update_num( g_instance_versions_tbl(i) ) ,
130 num2 = update_num( g_file_versions_tbl(i) )
131 WHERE index_char1 =ICX_ITEM_DIAG_PVT.g_file_key
132 AND char1 = g_file_tbl(i);
133
134 for i IN 1 .. g_file_count loop
135 -- check if instance file is less than should have version then update apply patch as Y
136 select char2, char3, num2, num3 into l_shld_ver, l_inst_ver , l_num2,l_num3
137 FROM po_session_gt
138 WHERE index_char1 =ICX_ITEM_DIAG_PVT.g_file_key
139 AND char1 = g_file_tbl(i);
140
141 if ( l_num2 <> l_num3) then
142 l_apply_patch:= 'TRUE';
143 elsif ( l_num2 <> 12) then
144 l_br_sv:= to_number(split(l_shld_ver,2,'.') );
145 l_br_iv:= to_number(split(l_inst_ver,2,'.') );
146
147 if l_br_sv > l_br_iv then
148 l_apply_patch:= 'TRUE';
149 elsif l_br_sv < l_br_iv then
150 l_apply_patch:= 'FALSE';
151 else
152 l_br_sv:= to_number(split(l_shld_ver,4,'.') ); -- last segment 2 of 120.69.12010000.2
153 l_br_iv:= to_number(split(l_inst_ver,4,'.') );
154 if l_br_sv > l_br_iv then
155 l_apply_patch:= 'TRUE';
156 else
157 l_apply_patch:= 'FALSE';
158 end if;
159 end if;
160 else
161 l_br_sv:= to_number(split(l_shld_ver,2,'.') );
162 l_br_iv:= to_number(split(l_inst_ver,2,'.') );
163 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'char4 ver 1 ' ||l_shld_ver || ' ver2 ' || l_inst_ver);
164 if l_br_sv > l_br_iv then
165 l_apply_patch:= 'TRUE';
166 else
167 l_apply_patch:= 'FALSE';
168 end if;
169 end if;
170 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'char4 check_file' ||g_file_tbl(i) || l_apply_patch);
171 UPDATE po_session_gt set char4 = l_apply_patch
172 WHERE index_char1 =ICX_ITEM_DIAG_PVT.g_file_key
173 AND char1 = g_file_tbl(i);
174
175 end loop;
176 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'end check_file');
177 RETURN 'Y';
178 exception when others then
179 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'check_file '||sqlerrm||' code='||sqlcode);
180 END check_file;
181
182 FUNCTION init_file_versions
183 RETURN VARCHAR2
184 IS
185 l_ctr NUMBER :=0;
186 l_return_status VARCHAR2(20):='SUCCESS';
187 l_file_name VARCHAR2(30);
188 l_file_version VARCHAR2(20);
189 l_api_name VARCHAR2(30):='init_file_versions';
190 CURSOR instance_ver( i NUMBER)
191 IS
192 SELECT filename ,
193 version
194 FROM
195 (SELECT filename ,
196 version
197 FROM ad_file_versions v ,
198 ad_files f
199 WHERE f.file_id = v.file_id
200 AND app_short_name IN ( 'ICX', 'PO')
201 AND subdir = 'patch/115/sql'
202 AND filename = g_file_tbl(i)
203 ORDER BY file_version_id DESC
204 )
205 WHERE rownum = 1
206 ORDER BY filename;
207 BEGIN
208 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start init_file_versions');
209 /*g_file_tbl := DBMS_SQL.VARCHAR2_TABLE(null);
210 g_file_versions_tbl := DBMS_SQL.VARCHAR2_TABLE(null);
211 g_instance_versions_tbl := DBMS_SQL.VARCHAR2_TABLE (null);
212 */
213 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPBB.pls'; g_file_versions_tbl(l_ctr) := '120.1.12010000.1';
214 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPBS.pls'; g_file_versions_tbl(l_ctr) := '120.0.12010000.1';
215 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPCB.pls'; g_file_versions_tbl(l_ctr) := '120.2.12010000.1';
216 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPCS.pls'; g_file_versions_tbl(l_ctr) := '120.1.12010000.1';
217 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPMB.pls'; g_file_versions_tbl(l_ctr) := '120.5.12010000.1';
218 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPMS.pls'; g_file_versions_tbl(l_ctr) := '120.1.12010000.1';
219 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPQB.pls'; g_file_versions_tbl(l_ctr) := '120.2.12010000.1';
220 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPQS.pls'; g_file_versions_tbl(l_ctr) := '120.0.12010000.1';
221 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPRB.pls'; g_file_versions_tbl(l_ctr) := '120.2.12010000.1';
222 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXGPPRS.pls'; g_file_versions_tbl(l_ctr) := '120.0.12010000.1';
223 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVBCSB.pls'; g_file_versions_tbl(l_ctr) := '120.8.12010000.3';
224 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVBCSS.pls'; g_file_versions_tbl(l_ctr) := '120.1.12010000.1';
225 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPCSB.pls'; g_file_versions_tbl(l_ctr) := '120.7.12010000.1';
226 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPCSS.pls'; g_file_versions_tbl(l_ctr) := '120.2.12010000.1';
227 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPCB.pls'; g_file_versions_tbl(l_ctr) := '120.3.12010000.1';
228 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPCS.pls'; g_file_versions_tbl(l_ctr) := '120.0.12010000.1';
229 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPDB.pls'; g_file_versions_tbl(l_ctr) := '120.14.12010000.1';
230 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPDS.pls'; g_file_versions_tbl(l_ctr) := '120.3.12010000.1';
231 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPIB.pls'; g_file_versions_tbl(l_ctr) := '120.14.12010000.5';
232 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPIS.pls'; g_file_versions_tbl(l_ctr) := '120.6.12010000.2';
233 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPMB.pls'; g_file_versions_tbl(l_ctr) := '120.8.12010000.10';
234 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPMS.pls'; g_file_versions_tbl(l_ctr) := '120.2.12010000.2';
235 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPRB.pls'; g_file_versions_tbl(l_ctr) := '120.10.12010000.1';
236 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPRS.pls'; g_file_versions_tbl(l_ctr) := '120.3.12010000.1';
237 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPSB.pls'; g_file_versions_tbl(l_ctr) := '120.6.12010000.2';
238 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVPPSS.pls'; g_file_versions_tbl(l_ctr) := '120.6.12010000.1';
239 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVUTLB.pls'; g_file_versions_tbl(l_ctr) := '120.18.12010000.8';
240 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'ICXVUTLS.pls'; g_file_versions_tbl(l_ctr) := '120.14.12010000.2';
241 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'PO_ATTRIBUTE_VALUES_PVT.plb'; g_file_versions_tbl(l_ctr) := '120.30.12010000.7';
242 l_ctr:=l_ctr+1; g_file_tbl(l_ctr) := 'PO_ATTRIBUTE_VALUES_PVT.pls'; g_file_versions_tbl(l_ctr) := '120.12.12010000.3';
243
244
245 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'g_file_tbl.count '|| g_file_tbl.count );
246 FOR idx IN 1 .. g_file_tbl.count
247 LOOP
248 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'global variables '|| idx);
249 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name, ' file :' || g_file_tbl(idx) || ' version :' ||g_file_versions_tbl(idx));
250 END LOOP;
251 -- populate instance versions
252 FOR i IN 1 .. g_file_count
253 LOOP
254 OPEN instance_ver(i);
255 FETCH instance_ver INTO l_file_name,l_file_version;
256
257 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'in loop init_file_versions i='|| i|| ' '||l_file_name||' g_instance_versions_tbl(i)='||l_file_version);
258 g_instance_versions_tbl(i):=l_file_version;
259 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,g_instance_versions_tbl(i));
260 CLOSE instance_ver;
261 END LOOP;
262
263 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'deleting done');
264 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start for' ||g_file_count );
265 FOR i IN 1 .. g_file_count
266 LOOP
267 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start of insert '|| i || ' file :' || g_file_tbl(i)||g_file_versions_tbl(i)|| g_instance_versions_tbl(i));
268 INSERT
269 INTO po_session_gt
270 (
271 index_char1 --'ITEM_DIAG_FILE_VERSIONS'
272 ,
273 char1 -- pls file name
274 ,
275 char2 -- should have version
276 ,
277 char3 -- instance version
278 )
279 VALUES
280 (
281 ICX_ITEM_DIAG_PVT.g_file_key ,
282 g_file_tbl(i) ,
283 g_file_versions_tbl(i) ,
284 g_instance_versions_tbl(i)
285 );
286
287 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name, 'for alll completed calll check file' );
288
289 END LOOP;
290 -- check old files.
291 l_return_status:= check_file;
292
293 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name, 'end check file' );
294 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name, 'end init_file_versions' );
295
296 RETURN l_return_status;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 logUnexpectedException(g_pkg_name, l_api_name, 'exception init_file_versions' || sqlerrm || SQLCODE ) ;
301 l_return_status :='FAILURE';
302 RETURN l_return_status;
303 END init_file_versions;
304
305 PROCEDURE file_versions
306 (
307 status IN VARCHAR2
308 )
309 IS
310 l_ret VARCHAR2 (20) ;
311 num1_tbl DBMS_SQL.NUMBER_TABLE;
312 num2_tbl DBMS_SQL.NUMBER_TABLE;
313 num3_tbl DBMS_SQL.NUMBER_TABLE;
314 l_char1_tbl DBMS_SQL.VARCHAR2_TABLE;
315 l_char2_tbl DBMS_SQL.VARCHAR2_TABLE;
316 l_char3_tbl DBMS_SQL.VARCHAR2_TABLE;
317 l_api_name VARCHAR2 (20):='file_versions';
318 l_log VARCHAR2
319 (
320 4000
321 )
322 ;
323 CURSOR c
324 IS
325 SELECT char1 ,
326 char2 ,
327 char3,
328 num1,
329 num2,
330 num3
331 FROM po_session_gt
332 WHERE index_char1 = ICX_ITEM_DIAG_PVT.g_file_key;
333
334 l_limit NUMBER:=100;
335 BEGIN
336 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start file_versions');
337 l_ret := init_file_versions;
338 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'printing session gt ');
339 OPEN c ;
340 LOOP
341 FETCH c BULK COLLECT
342 INTO l_char1_tbl,
343 l_char2_tbl,
344 l_char3_tbl,
345 num1_tbl,
346 num2_tbl,
347 num3_tbl LIMIT l_limit;
348 EXIT
349 WHEN l_char1_tbl.COUNT = 0;
350 FOR i IN 1 .. l_char1_tbl.COUNT
351 LOOP
352 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'finally i='|| l_char1_tbl(i)|| ' l_char2_tbl='||l_char2_tbl(i)||' l_char3_tbl='||l_char3_tbl(i)||' num1_tbl='||num1_tbl(i)||' num2_tbl='||num2_tbl(i)||' num3_tbl='||num3_tbl(i));
353 END LOOP ;
354 END LOOP ;
355 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'end file_versions');
356 EXCEPTION
357 WHEN OTHERS THEN
358 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'exception file_versions'||sqlerrm||SQLCODE );
359 END file_versions;
360
361 procedure get_setup_values ( p_table_name in VARCHAR2 ,
362 p_col_val out NOCOPY DBMS_SQL.VARCHAR2_TABLE,
363 p_row_val out NOCOPY ICX_ITEM_DIAG_GRP.VARCHAR_TABLE)
364 is
365
366 cursor OU_INV is
367 select ORG_ID, org.SHORT_CODE OU_CODE, org.NAME OU_NAME ,
368 fsp.INVENTORY_ORGANIZATION_ID inventory_ORGANIZATION, org1.ORGANIZATION_CODE INV_ORG_CODE,org1.ORGANIZATION_NAME INV_ORG_NAME
369 ,mparams.MASTER_ORGANIZATION_ID
370 from financials_system_params_all fsp, hr_operating_units org ,org_organization_definitions org1 ,mtl_parameters mparams
371 where fsp.org_id =ICX_ITEM_DIAG_PVT.g_org_id
372 and fsp.org_id =org.ORGANIZATION_ID
373 and fsp.INVENTORY_ORGANIZATION_ID=org1.ORGANIZATION_ID
374 AND mparams.organization_id=fsp.INVENTORY_ORGANIZATION_ID;
375
376 cursor cat_set is
377 SELECT functional_area_id
378 ,category_set_id
379 ,validate_flag
380 ,structure_id
381 FROM mtl_default_sets_view
382 WHERE functional_area_id = 2;
383
384 l_api_name VARCHAR2 (20):='get_setup_values';
385
386 begin
387
388 p_col_val(1):= 'Operating Unit ID'; p_col_val(2):= 'ORG_CODE'; p_col_val(3):= 'ORG_NAME';
389 p_col_val(4):= 'Inventory Oragnization ID'; p_col_val(5):= 'INV_ORG_CODE'; p_col_val(6):= 'INV_ORG_NAME';
390 p_col_val(7):= 'Master Organization ID';
391 p_col_val(8):= 'Functional Area ID'; p_col_val(9):= 'Category Set Id'; p_col_val(10):= 'Validate Flag';
392 p_col_val(11):= 'Structure ID';
393
394
395 p_row_val(1)(1):='';p_row_val(1)(2):='';p_row_val(1)(3):='';p_row_val(1)(4):='';
396 p_row_val(1)(5):='';p_row_val(1)(6):='';p_row_val(1)(7):='';p_row_val(1)(8):='';
397 p_row_val(1)(9):='';p_row_val(1)(10):='';p_row_val(1)(11):='';
398
399
400 open OU_INV;
401 fetch OU_INV into p_row_val(1)(1),p_row_val(1)(2),p_row_val(1)(3),p_row_val(1)(4),p_row_val(1)(5),p_row_val(1)(6),p_row_val(1)(7);
402 close OU_INV;
403
404 open cat_set;
405 fetch cat_set into p_row_val(1)(8),p_row_val(1)(9),p_row_val(1)(10),p_row_val(1)(11);
406 close cat_set;
407
408 g_organization_id := p_row_val(1)(4);
409 g_master_organization_id := p_row_val(1)(7);
410 g_category_set_id := p_row_val(1)(9);
411 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'g_organization_id='||g_organization_id||' g_master_organization_id'||g_master_organization_id||' g_category_set_id'||g_category_set_id);
412 exception when others then
413 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'get_setup_values '||sqlerrm||' code='||sqlcode);
414
415 end get_setup_values ;
416
417 procedure get_IDs_values ( p_table_name in VARCHAR2
418 , p_col_num out NOCOPY NUMBER
419 , p_row_num out NOCOPY NUMBER
420 , p_col_val out NOCOPY DBMS_SQL.VARCHAR2_TABLE
421 , p_row_val out NOCOPY ICX_ITEM_DIAG_GRP.VARCHAR_TABLE)
422 is
423 i number;
424 l_cat_num number;
425 l_old_organization_id varchar2(100);
426 cursor mi(j number) is
427 SELECT inventory_item_id ,organization_id ,segment1 ,internal_order_enabled_flag
428 ,purchasing_enabled_flag ,outside_operation_flag,list_price_per_unit
429 ,rfq_required_flag ,primary_uom_code ,replenish_to_order_flag ,base_item_id
430 ,auto_created_config_flag,nvl( (select 'Not Valid Record' from dual where replenish_to_order_flag = 'Y'
431 AND base_item_id IS NOT NULL
432 AND auto_created_config_flag = 'Y'), 'Valid Record') RULE_1_ISVALID ,
433 nvl( ( select 'Not Valid Record' from dual
434 where nvl(internal_order_enabled_flag,'N') ='N'
435 and ( list_price_per_unit is null or nvl(outside_operation_flag,'Y') ='Y' ) ),'Valid Record') RULE_2_ISVALID
436 FROM mtl_system_items_b
437 WHERE inventory_item_id = g_source_ids(j)
438 AND organization_id =nvl(g_organization_id,organization_id) ;
439
440 cursor mtl(j number) is
441 SELECT inventory_item_id,organization_id,language
442 ,source_lang ,description ,long_description
443 ,nvl(( select 'Not Valid Record' from dual
444 where mtl.language <> mtl.source_lang), 'Valid Record') RULE_1_ISVALID
445 FROM mtl_system_items_tl mtl
446 WHERE mtl.inventory_item_id = g_source_ids(j)
447 AND mtl.organization_id = nvl(g_organization_id,organization_id) ;
448
449 cursor mic(j number) is
450 select inventory_item_id ,organization_id,category_set_id, mic.category_id PO_CATEGORY, nvl(i.category_key ,-2) category_key,'Validate'
451 FROM mtl_item_categories mic , icx_por_category_data_sources i
452 WHERE mic.inventory_item_id = g_source_ids(j)
453 AND mic.organization_id = nvl(g_organization_id,mic.organization_id)
454 AND mic.category_set_id = g_category_set_id
455 and i.external_source(+) = 'Oracle'
456 and i.external_source_key(+) = TO_CHAR(mic.category_id)
457 order by mic.inventory_item_id,mic.organization_id;
458
459 cursor muom(j number) is
460 SELECT mi.inventory_item_id ,mi.organization_id , muom.unit_of_measure,muom.uom_code
461 FROM mtl_units_of_measure muom,mtl_system_items_b mi
462 WHERE mi.inventory_item_id = g_source_ids(j)
463 AND mi.organization_id = nvl(g_organization_id,mi.organization_id)
464 AND muom.uom_code = mi.primary_uom_code ;
465
466 cursor ip_cat(j number) is
467 SELECT mtl.inventory_item_id ,mtl.organization_id, i.language,
468 nvl (i.rt_category_id,- 2) ip_category_id,i.category_name ip_category_name
469 FROM icx_cat_categories_tl i
470 ,mtl_system_items_tl mtl
471 WHERE mtl.inventory_item_id = g_source_ids(j)
472 AND mtl.organization_id = nvl(g_organization_id,mtl.organization_id)
473 AND i.key = g_category_key(j)
474 AND i.type = 2
475 AND i.language = mtl.language;
476
477 cursor hdrs(j number) is
478 SELECT inventory_item_id ,po_line_id ,req_template_name
479 ,req_template_line_num ,org_id ,language
480 ,unit_price ,unit_meas_lookup_code ,line_type_id
481 ,document_number ,item_type ,supplier_site_id
482 ,supplier_id ,po_category_id ,ip_category_id
483 ,ip_category_name ,source_type , decode(ip_category_id,-2,'May not be searchable',decode(supplier_id,-2,'May not be searchable',null)) WARNING
484 FROM icx_cat_items_ctx_hdrs_tlp
485 WHERE inventory_item_id =g_source_ids(j)
486 AND org_id = nvl(g_org_id,org_id)
487 order by language,source_type;
488
489 cursor dtls(j number) is
490 SELECT inventory_item_id ,po_line_id ,req_template_name
491 ,req_template_line_num ,org_id ,language
492 ,SEQUENCE , htf.escape_sc(CTX_DESC)
493 FROM icx_cat_items_ctx_dtls_tlp
494 WHERE inventory_item_id =g_source_ids(j)
495 AND org_id = nvl(g_org_id,org_id)
496 order by po_line_id ,req_template_name,language , sequence;
497
498 cursor sqe is
499 select rownum,SQE_OWNER#,SQE_NAME,SQE_QUERY from ctxsys.dr$sqe
500 where SQE_NAME in (
501 SELECT UPPER(decode( fnd_profile.value('REQUISITION_TYPE'),'INTERNAL','icxzi','PURCHASE','icxzp','icxzb') || SQE_SEQUENCE)
502 FROM
503 ICX_CAT_CONTENT_ZONES_B zoneb,
504 ICX_CAT_STORE_CONTENTS_V contentv
505 WHERE
506 zoneb.ZONE_ID = contentv.CONTENT_ID AND
507 zoneb.TYPE ='LOCAL' );
508
509
510 cursor po_attr(j number) is
511 select
512 INVENTORY_ITEM_ID,ORG_ID,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,MANUFACTURER_PART_NUM,LEAD_TIME,PICTURE,THUMBNAIL_IMAGE,SUPPLIER_URL,MANUFACTURER_URL,ATTACHMENT_URL,UNSPSC,AVAILABILITY
513 from po_attribute_values
514 WHERE INVENTORY_ITEM_ID =g_source_ids(j)
515 and org_id = g_org_id;
516
517 cursor po_attr_tlp(j number) is
518 select INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,DESCRIPTION,MANUFACTURER,LONG_DESCRIPTION
519 from po_attribute_values_tlp
520 WHERE INVENTORY_ITEM_ID =g_source_ids(j)
521 and org_id = g_org_id;
522
523 cursor icx_attr(j number) is
524 select
525 INVENTORY_ITEM_ID,ORG_ID,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,MANUFACTURER_PART_NUM,LEAD_TIME,PICTURE,THUMBNAIL_IMAGE,SUPPLIER_URL,MANUFACTURER_URL,ATTACHMENT_URL,UNSPSC,AVAILABILITY
526 from icx_cat_attribute_values
527 WHERE INVENTORY_ITEM_ID =g_source_ids(j)
528 and org_id = g_org_id;
529
530 cursor icx_attr_tlp(j number) is
531 select INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,PO_LINE_ID,REQ_TEMPLATE_NAME,REQ_TEMPLATE_LINE_NUM,IP_CATEGORY_ID,DESCRIPTION,MANUFACTURER,LONG_DESCRIPTION
532 from icx_cat_attribute_values_tlp
533 WHERE INVENTORY_ITEM_ID =g_source_ids(j)
534 and org_id = g_org_id;
535
536 cursor po_gt is
537 select index_char1,num1,num2,num3,num4,char1,char2,char3,char4
538 from po_session_gt
539 WHERE index_char1 = ICX_ITEM_DIAG_PVT.g_error_key ;
540
541 l_api_name VARCHAR2 (20):='get_IDs_values';
542
543 begin
544 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'start');
545 if p_table_name = 'MTL_SYSTEM_ITEMS_B' then
546 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'1');
547 p_col_val(1):= 'INVENTORY_ITEM_ID';
548 p_col_val(2):= 'ORGANIZATION_ID';
549 p_col_val(3):= 'SEGMENT1';
550 p_col_val(4):= 'IS INTERNAL?';
551 p_col_val(5):= 'IS PURCHASABLE?';
552 p_col_val(6):= 'OUTSIDE OPERATION FLAG';
553 p_col_val(7):= 'LIST PRICE';
554 p_col_val(8):= 'RFQ REQUIRED FLAG';
555 p_col_val(9):= 'PRIMARY UOM CODE';
556 p_col_val(10):= 'REPLENISH TO ORDER FLAG';
557 p_col_val(11):= 'BASE ITEM ID';
558 p_col_val(12):= 'AUTO CREATED CONFIG FLAG';
559 p_col_val(13):= 'Rule 1';
560 p_col_val(14):= 'Rule 2';
561
562 p_row_num:=1;
563
564 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'12 g_source_ids.count'||g_source_ids.count);
565 for j in 1.. g_source_ids.count loop
566 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'12 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
567 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
568
569 open mi(j);
570 loop
571 fetch mi into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
572 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
573 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9),
574 p_row_val(p_row_num)(10),p_row_val(p_row_num)(11),p_row_val(p_row_num)(12),
575 p_row_val(p_row_num)(13),p_row_val(p_row_num)(14);
576 if mi%ROWCOUNT <1 then
577 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(5),
578 num1 => g_source_ids(j),
579 num2 => g_organization_id,
580 num3 => -2,
581 num4 => g_org_id,
582 char2 =>'-2',
583 char3 =>'-2');
584 end if;
585 exit when mi%NOTFOUND ;
586 p_row_num:=p_row_num+1;
587 end loop;
588
589 close mi;
590 end loop;
591 p_col_num :=14;
592 p_row_num:=p_row_num-1;
593 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
594
595 elsif p_table_name = 'MTL_SYSTEM_ITEMS_TL' then
596 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'2');
597 p_col_val(1):= 'INVENTORY_ITEM_ID';
598 p_col_val(2):= 'ORGANIZATION_ID';
599 p_col_val(3):= 'LANGUAGE';
600 p_col_val(4):= 'SOURCE_LANG';
601 p_col_val(5):= 'DESCRIPTION';
602 p_col_val(6):= 'LONG_DESCRIPTION';
603 p_col_val(7):= 'Rule 1';
604 p_row_num:=1;
605 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'22 g_source_ids.count'||g_source_ids.count);
606 for j in 1.. g_source_ids.count loop
607 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'22 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
608 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
609 open mtl(j);
610 loop
611 fetch mtl into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
612 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
613 p_row_val(p_row_num)(7);
614 exit when mtl%NOTFOUND ;
615 p_row_num:=p_row_num+1;
616 end loop;
617 close mtl;
618 end loop;
619 p_col_num :=7;
620 p_row_num:=p_row_num-1;
621 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
622
623 elsif p_table_name = 'MTL_ITEM_CATEGORIES' then
624 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'3');
625 p_col_val(1):= 'INVENTORY_ITEM_ID';
626 p_col_val(2):= 'ORGANIZATION_ID';
627 p_col_val(3):= 'CATEGORY_SET_ID';
628 p_col_val(4):= 'PO_CATEGORY_ID';
629 p_col_val(5):= 'CATEGORY_KEY';
630 p_col_val(6):= 'Rule 1';
631 p_row_num:=1;
632 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'32 g_source_ids.count'||g_source_ids.count);
633 for j in 1.. g_source_ids.count loop
634 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'32 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
635 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
636 l_cat_num:=0;
637 l_old_organization_id :='-2';
638 open mic(j);
639 loop
640 fetch mic into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
641 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6);
642 exit when mic%NOTFOUND ;
643 if(l_old_organization_id = '-2') then
644 l_old_organization_id:=p_row_val(p_row_num)(2);
645 end if;
646 g_category_key(j) := p_row_val(p_row_num)(5);
647 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'32 categoryid='||g_category_key(j)||' l_old_organization_id='||l_old_organization_id|| ' compare with '||p_row_val(p_row_num)(2) || ' l_cat_num='||l_cat_num);
648
649
650 if(l_old_organization_id=p_row_val(p_row_num)(2)) then
651 l_cat_num:=l_cat_num+1;
652 else
653 if l_cat_num >1 then
654 p_row_val(p_row_num-1)(6):='NOT VALID';
655 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(1),
656 num1 => g_source_ids(j),
657 num2 => p_row_val(p_row_num-1)(2),
658 num3 => -2,
659 num4 => g_org_id,
660 char2 =>'-2',
661 char3 =>'-2',
662 char4 => p_row_val(p_row_num-1)(4) );
663 elsif l_cat_num =1 AND p_row_val(p_row_num-1)(5) ='-2' then
664 p_row_val(p_row_num-1)(6):='May not be searchable';
665 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(2),
666 num1 => g_source_ids(j),
667 num2 => p_row_val(p_row_num-1)(2),
668 num3 => -2,
669 num4 => g_org_id,
670 char2 =>'-2',
671 char3 =>'-2',
672 char4 => p_row_val(p_row_num-1)(4) );
673 end if;
674 end if;
675
676 /*-- l_cat_num:=l_cat_num+1;
677 if(l_old_organization_id<>p_row_val(p_row_num)(2)) then
678 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'l_cat_num='||l_cat_num );
679 -- too many po category assigned to master item.
680 if l_cat_num <>1 then
681 p_row_val(p_row_num-1)(6):='May not be searchable';
682 g_category_key(j) := '-2';
683 if l_cat_num >1 then
684 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(1),
685 num1 => g_source_ids(j),
686 num2 => p_row_val(p_row_num)(2),
687 num3 => -2,
688 num4 => g_org_id,
689 char2 =>'-2',
690 char3 =>'-2',
691 char4 => p_row_val(1)(4) );
692 else
693 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(2),
694 num1 => g_source_ids(j),
695 num2 => p_row_val(p_row_num)(2),
696 num3 => -2,
697 num4 => g_org_id,
698 char2 =>'-2',
699 char3 =>'-2',
700 char4 => p_row_val(1)(4) );
701 end if;
702 else
703 g_category_key(j) := p_row_val(1)(5);
704 if ( g_category_key(j) ='-2' ) then
705 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(3),
706 num1 => g_source_ids(j),
707 num2 => p_row_val(p_row_num)(2),
708 num3 => -2,
709 num4 => g_org_id,
710 char2 =>'-2',
711 char3 =>'-2',
712 char4 => p_row_val(1)(4));
713 end if;
714 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'po category id='||p_row_val(1)(4)||'g_category_key='||g_category_key(j) );
715 end if;
716
717 l_old_organization_id:=p_row_val(p_row_num)(2);
718 l_cat_num:=1;
719 else
720 l_cat_num:=l_cat_num+1;
721 end if;
722 if p_row_val(p_row_num)(5) = '-2' then
723 p_row_val(p_row_num)(6):='May not be searchable';
724 else
725 p_row_val(p_row_num)(6):='';
726 end if;*/
727
728 p_row_num:=p_row_num+1;
729 end loop;
730 close mic;
731 end loop;
732 p_col_num :=6;
733 p_row_num:=p_row_num-1;
734 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
735
736 elsif p_table_name = 'MTL_UNITS_OF_MEASURE' then
737 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'4');
738 p_col_val(1):= 'INVENTORY_ITEM_ID';
739 p_col_val(2):= 'ORGANIZATION_ID';
740 p_col_val(3):= 'Unit of Measure';
741 p_col_val(4):= 'UOM Code';
742 p_row_num:=1;
743 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'42 g_source_ids.count'||g_source_ids.count);
744 for j in 1.. g_source_ids.count loop
745 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'42 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
746 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
747 open muom(j);
748 loop
749 fetch muom into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
750 p_row_val(p_row_num)(4);
751 exit when muom%NOTFOUND ;
752 p_row_num:=p_row_num+1;
753 end loop;
754 close muom;
755 end loop;
756 p_col_num :=4;
757 p_row_num:=p_row_num-1;
758 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
759
760 elsif p_table_name = 'ICX_CAT_CATEGORIES_TL' then
761 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'5');
762 p_col_val(1):= 'INVENTORY_ITEM_ID';
763 p_col_val(2):= 'ORGANIZATION_ID';
764 p_col_val(3):= 'Language';
765 p_col_val(4):= 'IP Category ID';
766 p_col_val(5):= 'IP Category Name';
767 p_row_num:=1;
768 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'52 g_source_ids.count'||g_source_ids.count);
769 for j in 1.. g_source_ids.count loop
770 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'52 g_category_key(j) ='||g_category_key(j) );
771 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
772 if g_category_key(j) <> '-2' then
773 open ip_cat(j);
774 loop
775 fetch ip_cat into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
776 p_row_val(p_row_num)(4), p_row_val(p_row_num)(5);
777 exit when ip_cat%NOTFOUND ;
778 p_row_num:=p_row_num+1;
779 end loop;
780 close ip_cat;
781
782 end if;
783 end loop;
784 p_col_num :=5;
785 p_row_num:=p_row_num-1;
786 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
787
788 elsif p_table_name = 'ICX_CAT_ITEMS_CTX_HDRS_TLP' then
789 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'6');
790 p_col_val(1):='INVENTORY_ITEM_ID';
791 p_col_val(2):='PO_LINE_ID';
792 p_col_val(3):='REQ_TEMPLATE_NAME';
793 p_col_val(4):='REQ_TEMPLATE_LINE_NUM';
794 p_col_val(5):='ORG_ID';
795 p_col_val(6):='LANGUAGE';
796 p_col_val(7):='UNIT_PRICE';
797 p_col_val(8):='UNIT_MEAS_LOOKUP_CODE';
798 p_col_val(9):='LINE_TYPE_ID';
799 p_col_val(10):='DOCUMENT_NUMBER';
800 p_col_val(11):='ITEM_TYPE';
801 p_col_val(12):='SUPPLIER_SITE_ID';
802 p_col_val(13):='SUPPLIER_ID';
803 p_col_val(14):='PO_CATEGORY_ID';
804 p_col_val(15):='IP_CATEGORY_ID';
805 p_col_val(16):='IP_CATEGORY_NAME';
806 p_col_val(17):='SOURCE_TYPE';
807 p_col_val(18):='WARNING';
808
809 p_row_num:=1;
810 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'62 g_source_ids.count'||g_source_ids.count);
811 for j in 1.. g_source_ids.count loop
812 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'62 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
813 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
814
815 open hdrs(j);
816 loop
817 fetch hdrs into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
818 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
819 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9),
820 p_row_val(p_row_num)(10),p_row_val(p_row_num)(11),p_row_val(p_row_num)(12),
821 p_row_val(p_row_num)(13),p_row_val(p_row_num)(14),p_row_val(p_row_num)(15),
822 p_row_val(p_row_num)(16),p_row_val(p_row_num)(17),p_row_val(p_row_num)(18);
823 exit when hdrs%NOTFOUND ;
824 p_row_num:=p_row_num+1;
825 end loop;
826 close hdrs;
827 end loop;
828 p_col_num :=18;
829 p_row_num:=p_row_num-1;
830 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
831
832 elsif p_table_name = 'ICX_CAT_ITEMS_CTX_DTLS_TLP' then
833 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'7');
834 p_col_val(1):='INVENTORY_ITEM_ID';
835 p_col_val(2):='PO_LINE_ID';
836 p_col_val(3):='REQ_TEMPLATE_NAME';
837 p_col_val(4):='REQ_TEMPLATE_LINE_NUM';
838 p_col_val(5):='ORG_ID';
839 p_col_val(6):='LANGUAGE';
840 p_col_val(7):='SEQUENCE';
841 p_col_val(8):='CTX_DESC';
842
843 p_row_num:=1;
844 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'72 g_source_ids.count'||g_source_ids.count);
845 for j in 1.. g_source_ids.count loop
846 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'72 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
847 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
848
849 open dtls(j);
850 loop
851 fetch dtls into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
852 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
853 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8);
854 exit when dtls%NOTFOUND ;
855 p_row_num:=p_row_num+1;
856 end loop;
857 close dtls;
858 end loop;
859 p_col_num :=8;
860 p_row_num:=p_row_num-1;
861 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
862
863 elsif p_table_name = 'DR$SQE' then
864 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'8');
865 p_col_val(1):='ROWNUM';
866 p_col_val(2):='SQE_OWNER#';
867 p_col_val(3):='SQE_NAME';
868 p_col_val(4):='SQE_QUERY';
869
870
871 p_row_num:=1;
872 --ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'82 g_source_ids.count'||g_source_ids.count);
873 for j in 1.. g_source_ids.count loop
874 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'82 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
875 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
876
877 open sqe;
878 loop
879 fetch sqe into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
880 p_row_val(p_row_num)(4);
881 exit when sqe%NOTFOUND ;
882 p_row_num:=p_row_num+1;
883 end loop;
884 close sqe;
885 end loop;
886 p_col_num :=4;
887 p_row_num:=p_row_num-1;
888 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
889
890 elsif p_table_name = 'PO_ATTRIBUTE_VALUES' then
891 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'9');
892 p_col_val(1):='INVENTORY_ITEM_ID';
893 p_col_val(2):='ORG_ID';
894 p_col_val(3):='PO_LINE_ID';
895 p_col_val(4):='REQ_TEMPLATE_NAME';
896 p_col_val(5):='REQ_TEMPLATE_LINE_NUM';
897 p_col_val(6):='IP_CATEGORY_ID';
898 p_col_val(7):='MANUFACTURER_PART_NUM';
899 p_col_val(8):='LEAD_TIME';
900 p_col_val(9):='PICTURE';
901 p_col_val(10):='THUMBNAIL_IMAGE';
902 p_col_val(11):='SUPPLIER_URL';
903 p_col_val(12):='MANUFACTURER_URL';
904 p_col_val(13):='ATTACHMENT_URL';
905 p_col_val(14):='UNSPSC';
906 p_col_val(15):='AVAILABILITY';
907
908
909 p_row_num:=1;
910 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'92 g_source_ids.count'||g_source_ids.count);
911 for j in 1.. g_source_ids.count loop
912 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'92 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
913 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
914
915 open po_attr(j);
916 loop
917 fetch po_attr into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
918 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
919 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9),
920 p_row_val(p_row_num)(10),p_row_val(p_row_num)(11),p_row_val(p_row_num)(12),
921 p_row_val(p_row_num)(13),p_row_val(p_row_num)(14),p_row_val(p_row_num)(15);
922 exit when po_attr%NOTFOUND ;
923 p_row_num:=p_row_num+1;
924 end loop;
925 close po_attr;
926 end loop;
927 p_col_num :=15;
928 p_row_num:=p_row_num-1;
929 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
930
931
932 elsif p_table_name = 'PO_ATTRIBUTE_VALUES_TLP' then
933 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'10');
934 p_col_val(1):='INVENTORY_ITEM_ID';
935 p_col_val(2):='ORG_ID';
936 p_col_val(3):='LANGUAGE';
937 p_col_val(4):='PO_LINE_ID';
938 p_col_val(5):='REQ_TEMPLATE_NAME';
939 p_col_val(6):='REQ_TEMPLATE_LINE_NUM';
940 p_col_val(7):='IP_CATEGORY_ID';
941 p_col_val(8):='DESCRIPTION';
942 p_col_val(9):='MANUFACTURER';
943 p_col_val(10):='LONG_DESCRIPTION';
944
945 p_row_num:=1;
946 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'102 g_source_ids.count'||g_source_ids.count);
947 for j in 1.. g_source_ids.count loop
948 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'102 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
949 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
950
951 open po_attr_tlp(j);
952 loop
953 fetch po_attr_tlp into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
954 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
955 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9),
956 p_row_val(p_row_num)(10);
957 exit when po_attr_tlp%NOTFOUND ;
958 p_row_num:=p_row_num+1;
959 end loop;
960 close po_attr_tlp;
961 end loop;
962 p_col_num :=10;
963 p_row_num:=p_row_num-1;
964 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
965
966 elsif p_table_name = 'ICX_CAT_ATTRIBUTE_VALUES' then
967 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'11');
968 p_col_val(1):='INVENTORY_ITEM_ID';
969 p_col_val(2):='ORG_ID';
970 p_col_val(3):='PO_LINE_ID';
971 p_col_val(4):='REQ_TEMPLATE_NAME';
972 p_col_val(5):='REQ_TEMPLATE_LINE_NUM';
973 p_col_val(6):='IP_CATEGORY_ID';
974 p_col_val(7):='MANUFACTURER_PART_NUM';
975 p_col_val(8):='LEAD_TIME';
976 p_col_val(9):='PICTURE';
977 p_col_val(10):='THUMBNAIL_IMAGE';
978 p_col_val(11):='SUPPLIER_URL';
979 p_col_val(12):='MANUFACTURER_URL';
980 p_col_val(13):='ATTACHMENT_URL';
981 p_col_val(14):='UNSPSC';
982 p_col_val(15):='AVAILABILITY';
983
984
985 p_row_num:=1;
986 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'112 g_source_ids.count'||g_source_ids.count);
987 for j in 1.. g_source_ids.count loop
988 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'112 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
989 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
990
991 open icx_attr(j);
992 loop
993 fetch icx_attr into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
994 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
995 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9),
996 p_row_val(p_row_num)(10),p_row_val(p_row_num)(11),p_row_val(p_row_num)(12),
997 p_row_val(p_row_num)(13),p_row_val(p_row_num)(14),p_row_val(p_row_num)(15);
998 exit when icx_attr%NOTFOUND ;
999 p_row_num:=p_row_num+1;
1000 end loop;
1001 close icx_attr;
1002 end loop;
1003 p_col_num :=15;
1004 p_row_num:=p_row_num-1;
1005 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
1006
1007
1008 elsif p_table_name = 'ICX_CAT_ATTRIBUTE_VALUES_TLP' then
1009 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'12');
1010 p_col_val(1):='INVENTORY_ITEM_ID';
1011 p_col_val(2):='ORG_ID';
1012 p_col_val(3):='LANGUAGE';
1013 p_col_val(4):='PO_LINE_ID';
1014 p_col_val(5):='REQ_TEMPLATE_NAME';
1015 p_col_val(6):='REQ_TEMPLATE_LINE_NUM';
1016 p_col_val(7):='IP_CATEGORY_ID';
1017 p_col_val(8):='DESCRIPTION';
1018 p_col_val(9):='MANUFACTURER';
1019 p_col_val(10):='LONG_DESCRIPTION';
1020
1021 p_row_num:=1;
1022 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'122 g_source_ids.count'||g_source_ids.count);
1023 for j in 1.. g_source_ids.count loop
1024 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'122 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
1025 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
1026
1027 open icx_attr_tlp(j);
1028 loop
1029 fetch icx_attr_tlp into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
1030 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
1031 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9),
1032 p_row_val(p_row_num)(10);
1033 exit when icx_attr_tlp%NOTFOUND ;
1034 p_row_num:=p_row_num+1;
1035 end loop;
1036 close icx_attr_tlp;
1037 end loop;
1038 p_col_num :=10;
1039 p_row_num:=p_row_num-1;
1040 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
1041
1042 elsif p_table_name = 'PO_SESSION_GT' then
1043 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'13');
1044 p_col_val(1):='INDEX_CHAR1';
1045 p_col_val(2):='NUM1';
1046 p_col_val(3):='NUM2';
1047 p_col_val(4):='NUM3';
1048 p_col_val(5):='NUM4';
1049 p_col_val(6):='CHAR1';
1050 p_col_val(7):='CHAR2';
1051 p_col_val(8):='CHAR3';
1052 p_col_val(9):='CHAR4';
1053
1054
1055 p_row_num:=1;
1056 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'132 g_source_ids.count'||g_source_ids.count);
1057 for j in 1.. g_source_ids.count loop
1058 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'132 g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id'||g_organization_id);
1059 -- ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num);
1060
1061 open po_gt;
1062 loop
1063 fetch po_gt into p_row_val(p_row_num)(1),p_row_val(p_row_num)(2),p_row_val(p_row_num)(3),
1064 p_row_val(p_row_num)(4),p_row_val(p_row_num)(5),p_row_val(p_row_num)(6),
1065 p_row_val(p_row_num)(7),p_row_val(p_row_num)(8),p_row_val(p_row_num)(9);
1066 exit when po_gt%NOTFOUND ;
1067 p_row_num:=p_row_num+1;
1068 end loop;
1069 close po_gt;
1070 end loop;
1071 p_col_num :=9;
1072 p_row_num:=p_row_num-1;
1073 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || ' p_col_num='||p_col_num);
1074
1075 end if;
1076 exception when others then
1077 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'get_IDs_values '||sqlerrm||' code='||sqlcode);
1078
1079 end get_IDs_values;
1080
1081
1082 procedure validate_values ( p_table_name in VARCHAR2
1083 , p_col_num out NOCOPY NUMBER
1084 , p_row_num out NOCOPY NUMBER
1085 , p_col_val out NOCOPY DBMS_SQL.VARCHAR2_TABLE
1086 , p_row_val out NOCOPY ICX_ITEM_DIAG_GRP.VARCHAR_TABLE) is
1087
1088 l_row_val ICX_ITEM_DIAG_GRP.VARCHAR_TABLE;
1089 l_api_name VARCHAR2 (20):='validate_values';
1090
1091 cursor validate(j number) is
1092 SELECT /*+ LEADING(doc) */
1093 doc.*,
1094 nvl(ic1.rt_category_id, -2) ip_category_id,
1095 ic1.category_name ip_category_name,
1096 ctx.inventory_item_id ctx_inventory_item_id,
1097 ctx.source_type ctx_source_type,
1098 ctx.item_type ctx_item_type,
1099 ctx.purchasing_org_id ctx_purchasing_org_id,
1100 ctx.supplier_id ctx_supplier_id,
1101 ctx.supplier_site_id ctx_supplier_site_id,
1102 ctx.supplier_part_num ctx_supplier_part_num,
1103 ctx.supplier_part_auxid ctx_supplier_part_auxid,
1104 ctx.ip_category_id ctx_ip_category_id,
1105 ctx.po_category_id ctx_po_category_id,
1106 ctx.ip_category_name ctx_ip_category_name,
1107 ROWIDTOCHAR(ctx.rowid) ctx_rowid,
1108 null IS_VALID
1109 FROM
1110 (
1111 SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
1112 mi.inventory_item_id inventory_item_id,
1113 -2 po_line_id,
1114 -2 req_template_name,
1115 -2 req_template_line_num,
1116 NVL(fsp.org_id, -2) org_id,
1117 mitl.language,
1118 'MASTER_ITEM' source_type,
1119 NVL(fsp.org_id, -2) purchasing_org_id,
1120 mic.category_id po_category_id,
1121 catMap.category_key category_key,
1122 mi.internal_order_enabled_flag,
1123 mi.purchasing_enabled_flag,
1124 mi.outside_operation_flag,
1125 muom.unit_of_measure unit_meas_lookup_code,
1126 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
1127 mi.rfq_required_flag,
1128 mitl.description,
1129 mitl.long_description,
1130 mparams.organization_id,
1131 mparams.master_organization_id
1132 FROM mtl_system_items_b mi,
1133 mtl_parameters mparams,
1134 mtl_system_items_tl mitl,
1135 mtl_item_categories mic,
1136 mtl_units_of_measure muom,
1137 financials_system_params_all fsp,
1138 icx_por_category_data_sources catMap
1139 WHERE mi.inventory_item_id = g_source_ids(j)
1140 AND mi.organization_id = mparams.organization_id
1141 AND (mparams.organization_id = nvl(g_organization_id,mparams.organization_id)
1142 OR mparams.master_organization_id =nvl(g_organization_id,mparams.master_organization_id))
1143 AND mi.inventory_item_id = mitl.inventory_item_id
1144 AND mi.organization_id = mitl.organization_id
1145 AND mitl.language = mitl.source_lang
1146 AND mic.inventory_item_id = mi.inventory_item_id
1147 AND mic.organization_id = mi.organization_id
1148 AND mic.category_set_id = 2
1149 AND muom.uom_code = mi.primary_uom_code
1150 AND NOT (mi.replenish_to_order_flag = 'Y'
1151 AND mi.base_item_id IS NOT NULL
1152 AND mi.auto_created_config_flag = 'Y')
1153 AND mi.organization_id = fsp.inventory_organization_id
1154 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
1155 AND catMap.external_source (+) = 'Oracle'
1156 ) doc,
1157 icx_cat_categories_tl ic1,
1158 icx_cat_items_ctx_hdrs_tlp ctx
1159 WHERE ic1.key (+) = doc.category_key
1160 AND ic1.type (+) = 2
1161 AND ic1.language (+) = doc.language
1162 AND doc.inventory_item_id = ctx.inventory_item_id (+)
1163 AND doc.po_line_id = ctx.po_line_id (+)
1164 AND doc.req_template_name = ctx.req_template_name (+)
1165 AND doc.req_template_line_num = ctx.req_template_line_num (+)
1166 AND doc.org_id = ctx.org_id (+)
1167 AND doc.language = ctx.language (+)
1168 AND doc.source_type = ctx.source_type (+)
1169 order by doc.ORG_ID,doc.LANGUAGE ;
1170 begin
1171 p_col_val(1):='INVENTORY_ITEM_ID';
1172 p_col_val(2):='PO_LINE_ID';
1173 p_col_val(3):='REQ_TEMPLATE_NAME';
1174 p_col_val(4):='REQ_TEMPLATE_LINE_NUM';
1175 p_col_val(5):='ORG_ID';
1176 p_col_val(6):='LANGUAGE';
1177 p_col_val(7):='SOURCE_TYPE';
1178 p_col_val(8):='PURCHASING_ORG_ID';
1179 p_col_val(9):='PO_CATEGORY_ID';
1180 p_col_val(10):='CATEGORY_KEY';
1181 p_col_val(11):='INTERNAL_ORDER_ENABLED_FLAG';
1182 p_col_val(12):='PURCHASING_ENABLED_FLAG';
1183 p_col_val(13):='OUTSIDE_OPERATION_FLAG';
1184 p_col_val(14):='UNIT_MEAS_LOOKUP_CODE';
1185 p_col_val(15):='UNIT_PRICE';
1186 p_col_val(16):='RFQ_REQUIRED_FLAG';
1187 p_col_val(17):='DESCRIPTION';
1188 p_col_val(18):='LONG_DESCRIPTION';
1189 p_col_val(19):='ORGANIZATION_ID';
1190 p_col_val(20):='MASTER_ORGANIZATION_ID';
1191 p_col_val(21):='IP_CATEGORY_ID';
1192 p_col_val(22):='IP_CATEGORY_NAME';
1193 p_col_val(23):='CTX_INVENTORY_ITEM_ID';
1194 p_col_val(24):='CTX_SOURCE_TYPE';
1195 p_col_val(25):='CTX_ITEM_TYPE';
1196 p_col_val(26):='CTX_PURCHASING_ORG_ID';
1197 p_col_val(27):='CTX_SUPPLIER_ID';
1198 p_col_val(28):='CTX_SUPPLIER_SITE_ID';
1199 p_col_val(29):='CTX_SUPPLIER_PART_NUM';
1200 p_col_val(30):='CTX_SUPPLIER_PART_AUXID';
1201 p_col_val(31):='CTX_IP_CATEGORY_ID';
1202 p_col_val(32):='CTX_PO_CATEGORY_ID';
1203 p_col_val(33):='CTX_IP_CATEGORY_NAME';
1204 p_col_val(34):='CTX_ROWID';
1205 p_col_val(35):='IS VALID?';
1206
1207 p_row_num:=1;
1208 for j in 1.. g_source_ids.count loop
1209 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'validate g_source_ids(j)='||g_source_ids(j)|| ' g_organization_id='||g_organization_id);
1210
1211 --if generic mode then at j=1 open validate(null) ; at j=2 exit
1212
1213 open validate(j);
1214 loop
1215 fetch validate into l_row_val(p_row_num)(1),l_row_val(p_row_num)(2),l_row_val(p_row_num)(3),
1216 l_row_val(p_row_num)(4),l_row_val(p_row_num)(5),l_row_val(p_row_num)(6),
1217 l_row_val(p_row_num)(7),l_row_val(p_row_num)(8),l_row_val(p_row_num)(9),
1218 l_row_val(p_row_num)(10),l_row_val(p_row_num)(11),l_row_val(p_row_num)(12),
1219 l_row_val(p_row_num)(13),l_row_val(p_row_num)(14),l_row_val(p_row_num)(15),
1220 l_row_val(p_row_num)(16),l_row_val(p_row_num)(17),l_row_val(p_row_num)(18),
1221 l_row_val(p_row_num)(19),l_row_val(p_row_num)(20),l_row_val(p_row_num)(21),
1222 l_row_val(p_row_num)(22),l_row_val(p_row_num)(23),l_row_val(p_row_num)(24),
1223 l_row_val(p_row_num)(25),l_row_val(p_row_num)(26),l_row_val(p_row_num)(27),
1224 l_row_val(p_row_num)(28),l_row_val(p_row_num)(29),l_row_val(p_row_num)(30),
1225 l_row_val(p_row_num)(31),l_row_val(p_row_num)(32),l_row_val(p_row_num)(33),
1226 l_row_val(p_row_num)(34),l_row_val(p_row_num)(35);
1227
1228 exit when validate%NOTFOUND ;
1229
1230 /*
1231 validation 1 # If CATEGORY_KEY is null , then you need to create shopping category and mapping
1232 programmatically or manually
1233 validation 2 # If CTX_IP_INVENTORY_ITEM_ID is -2 , then you need to reextract the item.
1234 icx_cat_items_ctx_hdrs_tlp record missing.
1235 validation 3 # If CTX_IP_CATEGORY_ID is -2 , then you need to reextract the item.
1236 validation 4 # validate item using api ICX_CAT_UTIL_PVT.is_item_valid_for_search
1237 */
1238 if l_row_val(p_row_num)(10) IS NULL THEN
1239 l_row_val(p_row_num)(35):=g_error_code(2);
1240 add_error(char1 => g_error_code(2),
1241 num1 => l_row_val(p_row_num)(1),
1242 num2 => l_row_val(p_row_num)(19),
1243 num3 => l_row_val(p_row_num)(2),
1244 num4 => l_row_val(p_row_num)(5),
1245 char2 =>l_row_val(p_row_num)(3),
1246 char3 =>l_row_val(p_row_num)(4),
1247 char4 =>l_row_val(p_row_num)(9));
1248 elsif l_row_val(p_row_num)(23) is null or l_row_val(p_row_num)(23) = '-2' then
1249 l_row_val(p_row_num)(35):=g_error_code(6);
1250 add_error(char1 => g_error_code(6),
1251 num1 => l_row_val(p_row_num)(1),
1252 num2 => l_row_val(p_row_num)(19),
1253 num3 => l_row_val(p_row_num)(2),
1254 num4 => l_row_val(p_row_num)(5),
1255 char2 =>l_row_val(p_row_num)(3),
1256 char3 =>l_row_val(p_row_num)(4));
1257
1258 elsif l_row_val(p_row_num)(31) is null or l_row_val(p_row_num)(31) = '-2' then
1259 l_row_val(p_row_num)(35):=g_error_code(3);
1260 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(3),
1261 num1 => l_row_val(p_row_num)(1),
1262 num2 => l_row_val(p_row_num)(19),
1263 num3 => l_row_val(p_row_num)(2),
1264 num4 => l_row_val(p_row_num)(5),
1265 char2 =>l_row_val(p_row_num)(3),
1266 char3 =>l_row_val(p_row_num)(4),
1267 char4 =>l_row_val(p_row_num)(9));
1268
1269 elsif ( ICX_CAT_UTIL_PVT.is_item_valid_for_search(l_row_val(p_row_num)(7),
1270 to_number(l_row_val(p_row_num)(2)),
1271 l_row_val(p_row_num)(3),
1272 to_number(l_row_val(p_row_num)(4)),
1273 to_number(l_row_val(p_row_num)(9)),
1274 to_number(l_row_val(p_row_num)(5))
1275 ) =0 ) then
1276 l_row_val(p_row_num)(35):=g_error_code(7);
1277 add_error(char1 => ICX_ITEM_DIAG_PVT.g_error_code(7),
1278 num1 => l_row_val(p_row_num)(9), --po_category_id
1279 num2 => l_row_val(p_row_num)(19),
1280 num3 => l_row_val(p_row_num)(2),
1281 num4 => l_row_val(p_row_num)(5),
1282 char2 =>l_row_val(p_row_num)(3),
1283 char3 =>l_row_val(p_row_num)(4),
1284 char4 =>l_row_val(p_row_num)(7)); --source_type
1285 end if;
1286
1287 if l_row_val(p_row_num)(35) is not null then
1288 p_row_val(p_row_num):= l_row_val(p_row_num);
1289 p_row_num:=p_row_num+1;
1290 end if;
1291
1292 end loop;
1293 close validate;
1294 end loop;
1295 p_col_num :=35;
1296 p_row_num:=p_row_num-1;
1297 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'p_row_num='||p_row_num || 'p_col_num='||p_col_num);
1298
1299
1300 create_missing_data;
1301
1302 exception when others then
1303 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'validate_values '||sqlerrm||' code='||sqlcode);
1304
1305 end validate_values;
1306
1307 procedure create_missing_data
1308 is
1309 l_option_value varchar2(10);
1310 l_api_name VARCHAR2 (20):='create_missing_data';
1311
1312 cursor create_ip_cat is
1313 select distinct to_number(CHAR4) from po_session_gt
1314 where INDEX_CHAR1='ITEM_DIAG_ERRORS'
1315 and CHAR1='IP_CATEGORY_MISSING';
1316
1317 cursor create_mappings is
1318 select distinct to_number(CHAR4) from po_session_gt
1319 where INDEX_CHAR1='ITEM_DIAG_ERRORS'
1320 and CHAR1=g_error_code(3)
1321 AND CHAR4 NOT IN (select distinct CHAR4 from po_session_gt
1322 where INDEX_CHAR1='ITEM_DIAG_ERRORS'
1323 and CHAR1='IP_CATEGORY_MISSING') ;
1324
1325 cursor create_ctx_hdrs is
1326 select distinct num1, num2 from po_session_gt
1327 where INDEX_CHAR1='ITEM_DIAG_ERRORS'
1328 and CHAR1='ICX_CTX_HDRS_MISSING';
1329
1330 l_inv_item_id number;
1331 l_organization_id number;
1332 l_po_category_id number;
1333 l_ret varchar2(1000);
1334 begin
1335 IF g_auto_map_category = 'Y' THEN
1336 fnd_profile.get('POR_AUTO_CREATE_SHOPPING_CAT', l_option_value);
1337 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'1 POR_AUTO_CREATE_SHOPPING_CAT='||l_option_value);
1338 fnd_profile.put('POR_AUTO_CREATE_SHOPPING_CAT', 'Y');
1339
1340 open create_ip_cat;
1341 loop
1342 fetch create_ip_cat into l_po_category_id;
1343 exit when create_ip_cat%NOTFOUND;
1344 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting po_category_id='||l_po_category_id);
1345 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := TRUE;
1346 ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetInsert
1347 ( p_api_version =>1.0 ,
1348 p_commit =>FND_API.G_TRUE ,
1349 x_return_status => l_ret ,
1350 p_category_set_id => g_category_set_id ,
1351 p_category_id =>l_po_category_id
1352 );
1353 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting over');
1354
1355 end loop;
1356 close create_ip_cat;
1357
1358 fnd_profile.put('POR_AUTO_CREATE_SHOPPING_CAT', l_option_value);
1359
1360 END IF;
1361 l_po_category_id:=0;
1362
1363 open create_mappings;
1364 loop
1365 fetch create_mappings into l_po_category_id;
1366 exit when create_mappings%NOTFOUND;
1367 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting item='||l_po_category_id);
1368 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := TRUE;
1369 ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetInsert
1370 (p_api_version =>1.0 ,
1371 p_commit =>FND_API.G_TRUE ,
1372 x_return_status => l_ret ,
1373 p_category_set_id => g_category_set_id ,
1374 p_category_id =>l_po_category_id
1375 );
1376
1377 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting over');
1378
1379 end loop;
1380 close create_mappings;
1381
1382 open create_ctx_hdrs;
1383 loop
1384 fetch create_ctx_hdrs into l_inv_item_id,l_organization_id;
1385 exit when create_ctx_hdrs%NOTFOUND;
1386 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting inv_item_id='||l_inv_item_id|| ' organization_id'||l_organization_id );
1387
1388 ICX_CAT_UTIL_PVT.setCommitParameter(FND_API.G_TRUE);
1389 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := FALSE;
1390 ICX_CAT_POPULATE_MI_PVT.populateItemChange(l_inv_item_id, l_organization_id, NULL, NULL);
1391 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
1392
1393 end loop;
1394 close create_ctx_hdrs;
1395
1396 commit;
1397 exception when others then
1398 logUnexpectedException(g_pkg_name, l_api_name,'create_missing_data '||sqlerrm||' code='||sqlcode);
1399
1400 end create_missing_data;
1401
1402 procedure PO_ATTRIBUTE_VALUES_DATA_FIX is
1403
1404 CURSOR master_csr IS
1405 SELECT *
1406 FROM icx_cat_items_ctx_hdrs_tlp ctx
1407 WHERE ctx.PO_LINE_ID=-2
1408 AND ctx.REQ_TEMPLATE_NAME='-2'
1409 AND ctx.REQ_TEMPLATE_LINE_NUM = -2
1410 AND ctx.SOURCE_TYPE = 'MASTER_ITEM'
1411 AND NOT EXISTS ( SELECT 'Row Found for inventory item id'
1412 FROM po_attribute_values poav
1413 WHERE poav.INVENTORY_ITEM_ID = ctx.INVENTORY_ITEM_ID
1414 AND poav.ORG_ID = ctx.ORG_ID
1415 AND poav.PO_LINE_ID=-2
1416 AND poav.REQ_TEMPLATE_NAME='-2'
1417 AND poav.REQ_TEMPLATE_LINE_NUM = -2)
1418 ORDER BY INVENTORY_ITEM_ID;
1419
1420 rec_MI icx_cat_items_ctx_hdrs_tlp%ROWTYPE;
1421 l_organization_id NUMBER;
1422 l_master_organization_id NUMBER;
1423 l_long_description PO_ATTRIBUTE_VALUES_TLP.LONG_DESCRIPTION%TYPE;
1424 l_api_name VARCHAR2(50) := 'PO_ATTRIBUTE_VALUES_DATA_FIX';
1425 l_progress VARCHAR2(4);
1426 l_counter NUMBER := 0;
1427 l_message VARCHAR2(4000);
1428 l_item_invalid BOOLEAN := FALSE;
1429 l_dummy VARCHAR2(10);
1430 l_skip number;
1431 BEGIN
1432
1433
1434 l_progress := '000';
1435
1436 l_message :='Start of data fix';
1437
1438 logStatement(g_pkg_name, l_api_name ,l_message);
1439
1440
1441 OPEN master_csr ;
1442 LOOP
1443 l_skip:=0;
1444 FETCH master_csr into rec_MI;
1445 exit when master_csr%notfound;
1446
1447 l_message := 'Cursor count=' || master_csr%ROWCOUNT;
1448
1449 logStatement(g_pkg_name, l_api_name ,l_message);
1450 begin
1451 SELECT organization_id, master_organization_id
1452 INTO l_organization_id,l_master_organization_id
1453 FROM(
1454 SELECT mparams.organization_id organization_id, mparams.master_organization_id master_organization_id
1455 FROM mtl_parameters mparams,financials_system_params_all fsp, mtl_system_items_b mtlb
1456 WHERE fsp.ORG_ID = rec_MI.ORG_ID
1457 AND mtlb.inventory_item_id = rec_MI.inventory_item_id
1458 AND mtlb.organization_id = mparams.organization_id
1459 AND (mparams.organization_id = fsp.INVENTORY_ORGANIZATION_ID
1460 OR mparams.master_organization_id = fsp.INVENTORY_ORGANIZATION_ID)
1461 ) WHERE ROWNUM =1;
1462 exception when others then
1463 l_message := 'Exception at '||sqlerrm ||' sqlcode:'|| sqlcode ||'INVENTORY_ITEM_ID=' || rec_MI.INVENTORY_ITEM_ID || ', org_id: ' || rec_MI.ORG_ID ;
1464 logStatement(g_pkg_name, l_api_name ,l_message);
1465 -- continue;
1466 l_skip:=1;
1467 end;
1468 if l_skip <> 1 then
1469 l_message := 'INVENTORY_ITEM_ID=' || rec_MI.INVENTORY_ITEM_ID || ' ,l_organization_id: ' || l_organization_id ||
1470 ', org_id: ' || rec_MI.ORG_ID || ', l_master_organization_id: ' || l_master_organization_id;
1471
1472
1473 logStatement(g_pkg_name, l_api_name ,l_message);
1474
1475
1476
1477 l_item_invalid := FALSE;
1478 BEGIN
1479 SELECT 1
1480 INTO l_dummy
1481 FROM mtl_system_items_b
1482 WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
1483 AND organization_id = l_organization_id;
1484 EXCEPTION
1485 WHEN No_Data_Found THEN
1486 --Item not assigend to the org now
1487 l_item_invalid := TRUE;
1488
1489 logStatement(g_pkg_name, l_api_name ,'item is not assigned to this org now');
1490
1491 END;
1492
1493 IF NOT l_item_invalid THEN
1494 PO_ATTRIBUTE_VALUES_PVT.create_default_attributes_MI
1495 (
1496 p_ip_category_id => rec_MI.IP_CATEGORY_ID,
1497 p_inventory_item_id => rec_MI.INVENTORY_ITEM_ID,
1498 p_org_id => rec_MI.ORG_ID,
1499 p_description => rec_MI.DESCRIPTION,
1500 p_organization_id => l_organization_id,
1501 p_master_organization_id => l_master_organization_id
1502 );
1503
1504 Begin
1505 SELECT LONG_DESCRIPTION
1506 INTO l_long_description
1507 FROM MTL_SYSTEM_ITEMS_TL
1508 WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
1509 AND organization_id = l_master_organization_id
1510 AND LANGUAGE = rec_MI.LANGUAGE;
1511 EXCEPTION
1512 WHEN No_Data_Found THEN
1513 l_long_description:='';
1514 END;
1515 PO_ATTRIBUTE_VALUES_PVT.create_attributes_tlp_MI
1516 (
1517 p_inventory_item_id => rec_MI.INVENTORY_ITEM_ID,
1518 p_ip_category_id => rec_MI.IP_CATEGORY_ID,
1519 p_org_id => rec_MI.ORG_ID,
1520 p_language => rec_MI.LANGUAGE,
1521 p_description => rec_MI.DESCRIPTION,
1522 p_long_description => l_long_description,
1523 p_organization_id => l_organization_id,
1524 p_master_organization_id => l_master_organization_id
1525 );
1526
1527 MERGE INTO icx_cat_attribute_values icav
1528 USING (SELECT *
1529 FROM po_attribute_values
1530 WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
1531 AND po_line_id = -2
1532 AND req_template_name = '-2'
1533 AND req_template_line_num = -2
1534 AND org_id = rec_MI.ORG_ID) temp
1535 ON (icav.inventory_item_id = temp.inventory_item_id AND
1536 icav.po_line_id = temp.po_line_id AND
1537 icav.req_template_name = temp.req_template_name AND
1538 icav.req_template_line_num = temp.req_template_line_num AND
1539 icav.org_id = temp.org_id)
1540 WHEN NOT MATCHED THEN INSERT VALUES (
1541 temp.attribute_values_id, temp.po_line_id, temp.req_template_name,
1542 temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
1543 temp.org_id, temp.manufacturer_part_num, temp.picture, temp.thumbnail_image,
1544 temp.supplier_url, temp.manufacturer_url, temp.attachment_url, temp.unspsc,
1545 temp.availability, temp.lead_time,
1546 temp.text_base_attribute1, temp.text_base_attribute2, temp.text_base_attribute3,
1547 temp.text_base_attribute4, temp.text_base_attribute5, temp.text_base_attribute6,
1548 temp.text_base_attribute7, temp.text_base_attribute8, temp.text_base_attribute9,
1549 temp.text_base_attribute10, temp.text_base_attribute11, temp.text_base_attribute12,
1550 temp.text_base_attribute13, temp.text_base_attribute14, temp.text_base_attribute15,
1551 temp.text_base_attribute16, temp.text_base_attribute17, temp.text_base_attribute18,
1552 temp.text_base_attribute19, temp.text_base_attribute20, temp.text_base_attribute21,
1553 temp.text_base_attribute22, temp.text_base_attribute23, temp.text_base_attribute24,
1554 temp.text_base_attribute25, temp.text_base_attribute26, temp.text_base_attribute27,
1555 temp.text_base_attribute28, temp.text_base_attribute29, temp.text_base_attribute30,
1556 temp.text_base_attribute31, temp.text_base_attribute32, temp.text_base_attribute33,
1557 temp.text_base_attribute34, temp.text_base_attribute35, temp.text_base_attribute36,
1558 temp.text_base_attribute37, temp.text_base_attribute38, temp.text_base_attribute39,
1559 temp.text_base_attribute40, temp.text_base_attribute41, temp.text_base_attribute42,
1560 temp.text_base_attribute43, temp.text_base_attribute44, temp.text_base_attribute45,
1561 temp.text_base_attribute46, temp.text_base_attribute47, temp.text_base_attribute48,
1562 temp.text_base_attribute49, temp.text_base_attribute50, temp.text_base_attribute51,
1563 temp.text_base_attribute52, temp.text_base_attribute53, temp.text_base_attribute54,
1564 temp.text_base_attribute55, temp.text_base_attribute56, temp.text_base_attribute57,
1565 temp.text_base_attribute58, temp.text_base_attribute59, temp.text_base_attribute60,
1566 temp.text_base_attribute61, temp.text_base_attribute62, temp.text_base_attribute63,
1567 temp.text_base_attribute64, temp.text_base_attribute65, temp.text_base_attribute66,
1568 temp.text_base_attribute67, temp.text_base_attribute68, temp.text_base_attribute69,
1569 temp.text_base_attribute70, temp.text_base_attribute71, temp.text_base_attribute72,
1570 temp.text_base_attribute73, temp.text_base_attribute74, temp.text_base_attribute75,
1571 temp.text_base_attribute76, temp.text_base_attribute77, temp.text_base_attribute78,
1572 temp.text_base_attribute79, temp.text_base_attribute80, temp.text_base_attribute81,
1573 temp.text_base_attribute82, temp.text_base_attribute83, temp.text_base_attribute84,
1574 temp.text_base_attribute85, temp.text_base_attribute86, temp.text_base_attribute87,
1575 temp.text_base_attribute88, temp.text_base_attribute89, temp.text_base_attribute90,
1576 temp.text_base_attribute91, temp.text_base_attribute92, temp.text_base_attribute93,
1577 temp.text_base_attribute94, temp.text_base_attribute95, temp.text_base_attribute96,
1578 temp.text_base_attribute97, temp.text_base_attribute98, temp.text_base_attribute99,
1579 temp.text_base_attribute100,
1580 temp.num_base_attribute1, temp.num_base_attribute2, temp.num_base_attribute3,
1581 temp.num_base_attribute4, temp.num_base_attribute5, temp.num_base_attribute6,
1582 temp.num_base_attribute7, temp.num_base_attribute8, temp.num_base_attribute9,
1583 temp.num_base_attribute10, temp.num_base_attribute11, temp.num_base_attribute12,
1584 temp.num_base_attribute13, temp.num_base_attribute14, temp.num_base_attribute15,
1585 temp.num_base_attribute16, temp.num_base_attribute17, temp.num_base_attribute18,
1586 temp.num_base_attribute19, temp.num_base_attribute20, temp.num_base_attribute21,
1587 temp.num_base_attribute22, temp.num_base_attribute23, temp.num_base_attribute24,
1588 temp.num_base_attribute25, temp.num_base_attribute26, temp.num_base_attribute27,
1589 temp.num_base_attribute28, temp.num_base_attribute29, temp.num_base_attribute30,
1590 temp.num_base_attribute31, temp.num_base_attribute32, temp.num_base_attribute33,
1591 temp.num_base_attribute34, temp.num_base_attribute35, temp.num_base_attribute36,
1592 temp.num_base_attribute37, temp.num_base_attribute38, temp.num_base_attribute39,
1593 temp.num_base_attribute40, temp.num_base_attribute41, temp.num_base_attribute42,
1594 temp.num_base_attribute43, temp.num_base_attribute44, temp.num_base_attribute45,
1595 temp.num_base_attribute46, temp.num_base_attribute47, temp.num_base_attribute48,
1596 temp.num_base_attribute49, temp.num_base_attribute50, temp.num_base_attribute51,
1597 temp.num_base_attribute52, temp.num_base_attribute53, temp.num_base_attribute54,
1598 temp.num_base_attribute55, temp.num_base_attribute56, temp.num_base_attribute57,
1599 temp.num_base_attribute58, temp.num_base_attribute59, temp.num_base_attribute60,
1600 temp.num_base_attribute61, temp.num_base_attribute62, temp.num_base_attribute63,
1601 temp.num_base_attribute64, temp.num_base_attribute65, temp.num_base_attribute66,
1602 temp.num_base_attribute67, temp.num_base_attribute68, temp.num_base_attribute69,
1603 temp.num_base_attribute70, temp.num_base_attribute71, temp.num_base_attribute72,
1604 temp.num_base_attribute73, temp.num_base_attribute74, temp.num_base_attribute75,
1605 temp.num_base_attribute76, temp.num_base_attribute77, temp.num_base_attribute78,
1606 temp.num_base_attribute79, temp.num_base_attribute80, temp.num_base_attribute81,
1607 temp.num_base_attribute82, temp.num_base_attribute83, temp.num_base_attribute84,
1608 temp.num_base_attribute85, temp.num_base_attribute86, temp.num_base_attribute87,
1609 temp.num_base_attribute88, temp.num_base_attribute89, temp.num_base_attribute90,
1610 temp.num_base_attribute91, temp.num_base_attribute92, temp.num_base_attribute93,
1611 temp.num_base_attribute94, temp.num_base_attribute95, temp.num_base_attribute96,
1612 temp.num_base_attribute97, temp.num_base_attribute98, temp.num_base_attribute99,
1613 temp.num_base_attribute100,
1614 temp.text_cat_attribute1, temp.text_cat_attribute2, temp.text_cat_attribute3,
1615 temp.text_cat_attribute4, temp.text_cat_attribute5, temp.text_cat_attribute6,
1616 temp.text_cat_attribute7, temp.text_cat_attribute8, temp.text_cat_attribute9,
1617 temp.text_cat_attribute10, temp.text_cat_attribute11, temp.text_cat_attribute12,
1618 temp.text_cat_attribute13, temp.text_cat_attribute14, temp.text_cat_attribute15,
1619 temp.text_cat_attribute16, temp.text_cat_attribute17, temp.text_cat_attribute18,
1620 temp.text_cat_attribute19, temp.text_cat_attribute20, temp.text_cat_attribute21,
1621 temp.text_cat_attribute22, temp.text_cat_attribute23, temp.text_cat_attribute24,
1622 temp.text_cat_attribute25, temp.text_cat_attribute26, temp.text_cat_attribute27,
1623 temp.text_cat_attribute28, temp.text_cat_attribute29, temp.text_cat_attribute30,
1624 temp.text_cat_attribute31, temp.text_cat_attribute32, temp.text_cat_attribute33,
1625 temp.text_cat_attribute34, temp.text_cat_attribute35, temp.text_cat_attribute36,
1626 temp.text_cat_attribute37, temp.text_cat_attribute38, temp.text_cat_attribute39,
1627 temp.text_cat_attribute40, temp.text_cat_attribute41, temp.text_cat_attribute42,
1628 temp.text_cat_attribute43, temp.text_cat_attribute44, temp.text_cat_attribute45,
1629 temp.text_cat_attribute46, temp.text_cat_attribute47, temp.text_cat_attribute48,
1630 temp.text_cat_attribute49, temp.text_cat_attribute50,
1631 temp.num_cat_attribute1, temp.num_cat_attribute2, temp.num_cat_attribute3,
1632 temp.num_cat_attribute4, temp.num_cat_attribute5, temp.num_cat_attribute6,
1633 temp.num_cat_attribute7, temp.num_cat_attribute8, temp.num_cat_attribute9,
1634 temp.num_cat_attribute10, temp.num_cat_attribute11, temp.num_cat_attribute12,
1635 temp.num_cat_attribute13, temp.num_cat_attribute14, temp.num_cat_attribute15,
1636 temp.num_cat_attribute16, temp.num_cat_attribute17, temp.num_cat_attribute18,
1637 temp.num_cat_attribute19, temp.num_cat_attribute20, temp.num_cat_attribute21,
1638 temp.num_cat_attribute22, temp.num_cat_attribute23, temp.num_cat_attribute24,
1639 temp.num_cat_attribute25, temp.num_cat_attribute26, temp.num_cat_attribute27,
1640 temp.num_cat_attribute28, temp.num_cat_attribute29, temp.num_cat_attribute30,
1641 temp.num_cat_attribute31, temp.num_cat_attribute32, temp.num_cat_attribute33,
1642 temp.num_cat_attribute34, temp.num_cat_attribute35, temp.num_cat_attribute36,
1643 temp.num_cat_attribute37, temp.num_cat_attribute38, temp.num_cat_attribute39,
1644 temp.num_cat_attribute40, temp.num_cat_attribute41, temp.num_cat_attribute42,
1645 temp.num_cat_attribute43, temp.num_cat_attribute44, temp.num_cat_attribute45,
1646 temp.num_cat_attribute46, temp.num_cat_attribute47, temp.num_cat_attribute48,
1647 temp.num_cat_attribute49, temp.num_cat_attribute50,
1648 temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
1649 temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
1650 temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
1651
1652
1653 logStatement(g_pkg_name, l_api_name ,'Num. of rows inserted into icx_cat_attribute_values:' ||SQL%ROWCOUNT);
1654
1655 MERGE INTO icx_cat_attribute_values_tlp icavt
1656 USING (SELECT *
1657 FROM po_attribute_values_tlp
1658 WHERE inventory_item_id = rec_MI.INVENTORY_ITEM_ID
1659 AND po_line_id = -2
1660 AND req_template_name = '-2'
1661 AND req_template_line_num = -2
1662 AND org_id = rec_MI.ORG_ID
1663 AND language = rec_MI.LANGUAGE ) temp
1664 ON (icavt.inventory_item_id = temp.inventory_item_id AND
1665 icavt.po_line_id = temp.po_line_id AND
1666 icavt.req_template_name = temp.req_template_name AND
1667 icavt.req_template_line_num = temp.req_template_line_num AND
1668 icavt.org_id = temp.org_id AND
1669 icavt.language = temp.language)
1670 WHEN NOT MATCHED THEN INSERT VALUES (
1671 temp.attribute_values_tlp_id, temp.po_line_id, temp.req_template_name,
1672 temp.req_template_line_num, temp.ip_category_id, temp.inventory_item_id,
1673 temp.org_id, temp.language, temp.description, temp.manufacturer,
1674 temp.comments, temp.alias, temp.long_description,
1675 temp.tl_text_base_attribute1, temp.tl_text_base_attribute2, temp.tl_text_base_attribute3,
1676 temp.tl_text_base_attribute4, temp.tl_text_base_attribute5, temp.tl_text_base_attribute6,
1677 temp.tl_text_base_attribute7, temp.tl_text_base_attribute8, temp.tl_text_base_attribute9,
1678 temp.tl_text_base_attribute10, temp.tl_text_base_attribute11, temp.tl_text_base_attribute12,
1679 temp.tl_text_base_attribute13, temp.tl_text_base_attribute14, temp.tl_text_base_attribute15,
1680 temp.tl_text_base_attribute16, temp.tl_text_base_attribute17, temp.tl_text_base_attribute18,
1681 temp.tl_text_base_attribute19, temp.tl_text_base_attribute20, temp.tl_text_base_attribute21,
1682 temp.tl_text_base_attribute22, temp.tl_text_base_attribute23, temp.tl_text_base_attribute24,
1683 temp.tl_text_base_attribute25, temp.tl_text_base_attribute26, temp.tl_text_base_attribute27,
1684 temp.tl_text_base_attribute28, temp.tl_text_base_attribute29, temp.tl_text_base_attribute30,
1685 temp.tl_text_base_attribute31, temp.tl_text_base_attribute32, temp.tl_text_base_attribute33,
1686 temp.tl_text_base_attribute34, temp.tl_text_base_attribute35, temp.tl_text_base_attribute36,
1687 temp.tl_text_base_attribute37, temp.tl_text_base_attribute38, temp.tl_text_base_attribute39,
1688 temp.tl_text_base_attribute40, temp.tl_text_base_attribute41, temp.tl_text_base_attribute42,
1689 temp.tl_text_base_attribute43, temp.tl_text_base_attribute44, temp.tl_text_base_attribute45,
1690 temp.tl_text_base_attribute46, temp.tl_text_base_attribute47, temp.tl_text_base_attribute48,
1691 temp.tl_text_base_attribute49, temp.tl_text_base_attribute50, temp.tl_text_base_attribute51,
1692 temp.tl_text_base_attribute52, temp.tl_text_base_attribute53, temp.tl_text_base_attribute54,
1693 temp.tl_text_base_attribute55, temp.tl_text_base_attribute56, temp.tl_text_base_attribute57,
1694 temp.tl_text_base_attribute58, temp.tl_text_base_attribute59, temp.tl_text_base_attribute60,
1695 temp.tl_text_base_attribute61, temp.tl_text_base_attribute62, temp.tl_text_base_attribute63,
1696 temp.tl_text_base_attribute64, temp.tl_text_base_attribute65, temp.tl_text_base_attribute66,
1697 temp.tl_text_base_attribute67, temp.tl_text_base_attribute68, temp.tl_text_base_attribute69,
1698 temp.tl_text_base_attribute70, temp.tl_text_base_attribute71, temp.tl_text_base_attribute72,
1699 temp.tl_text_base_attribute73, temp.tl_text_base_attribute74, temp.tl_text_base_attribute75,
1700 temp.tl_text_base_attribute76, temp.tl_text_base_attribute77, temp.tl_text_base_attribute78,
1701 temp.tl_text_base_attribute79, temp.tl_text_base_attribute80, temp.tl_text_base_attribute81,
1702 temp.tl_text_base_attribute82, temp.tl_text_base_attribute83, temp.tl_text_base_attribute84,
1703 temp.tl_text_base_attribute85, temp.tl_text_base_attribute86, temp.tl_text_base_attribute87,
1704 temp.tl_text_base_attribute88, temp.tl_text_base_attribute89, temp.tl_text_base_attribute90,
1705 temp.tl_text_base_attribute91, temp.tl_text_base_attribute92, temp.tl_text_base_attribute93,
1706 temp.tl_text_base_attribute94, temp.tl_text_base_attribute95, temp.tl_text_base_attribute96,
1707 temp.tl_text_base_attribute97, temp.tl_text_base_attribute98, temp.tl_text_base_attribute99,
1708 temp.tl_text_base_attribute100,
1709 temp.tl_text_cat_attribute1, temp.tl_text_cat_attribute2, temp.tl_text_cat_attribute3,
1710 temp.tl_text_cat_attribute4, temp.tl_text_cat_attribute5, temp.tl_text_cat_attribute6,
1711 temp.tl_text_cat_attribute7, temp.tl_text_cat_attribute8, temp.tl_text_cat_attribute9,
1712 temp.tl_text_cat_attribute10, temp.tl_text_cat_attribute11, temp.tl_text_cat_attribute12,
1713 temp.tl_text_cat_attribute13, temp.tl_text_cat_attribute14, temp.tl_text_cat_attribute15,
1714 temp.tl_text_cat_attribute16, temp.tl_text_cat_attribute17, temp.tl_text_cat_attribute18,
1715 temp.tl_text_cat_attribute19, temp.tl_text_cat_attribute20, temp.tl_text_cat_attribute21,
1716 temp.tl_text_cat_attribute22, temp.tl_text_cat_attribute23, temp.tl_text_cat_attribute24,
1717 temp.tl_text_cat_attribute25, temp.tl_text_cat_attribute26, temp.tl_text_cat_attribute27,
1718 temp.tl_text_cat_attribute28, temp.tl_text_cat_attribute29, temp.tl_text_cat_attribute30,
1719 temp.tl_text_cat_attribute31, temp.tl_text_cat_attribute32, temp.tl_text_cat_attribute33,
1720 temp.tl_text_cat_attribute34, temp.tl_text_cat_attribute35, temp.tl_text_cat_attribute36,
1721 temp.tl_text_cat_attribute37, temp.tl_text_cat_attribute38, temp.tl_text_cat_attribute39,
1722 temp.tl_text_cat_attribute40, temp.tl_text_cat_attribute41, temp.tl_text_cat_attribute42,
1723 temp.tl_text_cat_attribute43, temp.tl_text_cat_attribute44, temp.tl_text_cat_attribute45,
1724 temp.tl_text_cat_attribute46, temp.tl_text_cat_attribute47, temp.tl_text_cat_attribute48,
1725 temp.tl_text_cat_attribute49, temp.tl_text_cat_attribute50,
1726 temp.last_update_login, temp.last_updated_by, temp.last_update_date, temp.created_by,
1727 temp.creation_date, temp.request_id, temp.program_application_id, temp.program_id,
1728 temp.program_update_date, temp.last_updated_program, temp.rebuild_search_index_flag);
1729
1730
1731
1732 logStatement(g_pkg_name, l_api_name ,'Num. of rows inserted into icx_cat_attribute_values_tlp:' ||SQL%ROWCOUNT);
1733 END IF;
1734 end if;
1735 END LOOP;
1736
1737 l_message := 'Total Cursor count=' || master_csr%ROWCOUNT;
1738
1739 logStatement(g_pkg_name, l_api_name ,l_message);
1740
1741 logStatement(g_pkg_name, l_api_name ,'END of data fix');
1742
1743 exception when others then
1744 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'exception '||sqlerrm||' code='||sqlcode);
1745
1746 END PO_ATTRIBUTE_VALUES_DATA_FIX;
1747
1748 procedure sync_sources(p_org_id in number,
1749 p_source_type in varchar2,
1750 x_return_status out NOCOPY varchar2 )
1751 is
1752 l_min_row_id ROWID;
1753 l_max_row_id ROWID;
1754 l_api_name VARCHAR2 (20):='sync_sources';
1755 l_inv_item_id number;
1756 l_organization_id number;
1757
1758
1759 Cursor master_item_sync is
1760 select distinct inventory_item_id,organization_id from ( SELECT /*+ LEADING(doc) */
1761 doc.*,
1762 nvl(ic1.rt_category_id, -2) ip_category_id,
1763 ic1.category_name ip_category_name,
1764 ctx.inventory_item_id ctx_inventory_item_id,
1765 ctx.source_type ctx_source_type,
1766 ctx.item_type ctx_item_type,
1767 ctx.purchasing_org_id ctx_purchasing_org_id,
1768 ctx.supplier_id ctx_supplier_id,
1769 ctx.supplier_site_id ctx_supplier_site_id,
1770 ctx.supplier_part_num ctx_supplier_part_num,
1771 ctx.supplier_part_auxid ctx_supplier_part_auxid,
1772 ctx.ip_category_id ctx_ip_category_id,
1773 ctx.po_category_id ctx_po_category_id,
1774 ctx.ip_category_name ctx_ip_category_name,
1775 ctx.unit_price ctx_unit_price,
1776 ROWIDTOCHAR(ctx.rowid) ctx_rowid
1777
1778 FROM
1779 (
1780 SELECT /*+ ROWID(mi) NO_EXPAND use_nl(mitl,mic,catMap) */
1781 mi.inventory_item_id inventory_item_id,
1782 -2 po_line_id,
1783 '-2' req_template_name,
1784 -2 req_template_line_num,
1785 NVL(fsp.org_id, -2) org_id,
1786 mitl.language,
1787 'MASTER_ITEM' source_type,
1788 NVL(fsp.org_id, -2) purchasing_org_id,
1789 mic.category_id po_category_id,
1790 catMap.category_key category_key,
1791 mi.internal_order_enabled_flag,
1792 mi.purchasing_enabled_flag,
1793 mi.outside_operation_flag,
1794 muom.unit_of_measure unit_meas_lookup_code,
1795 DECODE(mi.purchasing_enabled_flag, 'Y', mi.list_price_per_unit, to_number(null)) unit_price,
1796 mi.rfq_required_flag,
1797 mitl.description,
1798 mitl.long_description,
1799 mparams.organization_id,
1800 mparams.master_organization_id
1801 FROM mtl_system_items_b mi,
1802 mtl_parameters mparams,
1803 mtl_system_items_tl mitl,
1804 mtl_item_categories mic,
1805 mtl_units_of_measure muom,
1806 financials_system_params_all fsp,
1807 icx_por_category_data_sources catMap
1808 WHERE
1809 mi.organization_id = mparams.organization_id
1810 AND (mparams.organization_id = nvl(p_org_id,mparams.organization_id)
1811 OR mparams.master_organization_id = nvl(p_org_id,mparams.master_organization_id))
1812 AND mi.inventory_item_id = mitl.inventory_item_id
1813 AND mi.organization_id = mitl.organization_id
1814 AND mitl.language = mitl.source_lang
1815 AND mic.inventory_item_id = mi.inventory_item_id
1816 AND mic.organization_id = mi.organization_id
1817 AND mic.category_set_id = 2
1818 AND muom.uom_code = mi.primary_uom_code
1819 AND NOT (mi.replenish_to_order_flag = 'Y'
1820 AND mi.base_item_id IS NOT NULL
1821 AND mi.auto_created_config_flag = 'Y')
1822 AND mi.organization_id = fsp.inventory_organization_id
1823 AND catMap.external_source_key (+) = TO_CHAR(mic.category_id)
1824 AND catMap.external_source (+) = 'Oracle'
1825 ) doc,
1826 icx_cat_categories_tl ic1,
1827 icx_cat_items_ctx_hdrs_tlp ctx
1828 WHERE ic1.key (+) = doc.category_key
1829 AND ic1.type (+) = 2
1830 AND ic1.language (+) = doc.language
1831 AND doc.inventory_item_id = ctx.inventory_item_id (+)
1832 AND doc.po_line_id = ctx.po_line_id (+)
1833 AND doc.req_template_name = ctx.req_template_name (+)
1834 AND doc.req_template_line_num = ctx.req_template_line_num (+)
1835 AND doc.org_id = ctx.org_id (+)
1836 AND doc.language = ctx.language (+)
1837 AND doc.source_type = ctx.source_type (+))
1838 where ip_category_id <> ctx_ip_category_id
1839 or unit_price <> ctx_unit_price
1840 or ctx_inventory_item_id <> inventory_item_id ;
1841
1842 l_api_version CONSTANT NUMBER := 1.0;
1843 l_err_loc PLS_INTEGER;
1844 l_start_date DATE;
1845 l_end_date DATE;
1846 l_log_string VARCHAR2(2000);
1847 BEGIN
1848 l_err_loc := 100;
1849 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1850
1851 x_return_status := FND_API.G_RET_STS_SUCCESS;
1852 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1853
1854 l_err_loc := 200;
1855 -- Standard Start of API savepoint
1856 SAVEPOINT populateItemSync_sp;
1857 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1858
1859 l_err_loc := 300;
1860 l_start_date := sysdate;
1861 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1862 l_log_string := 'Started at:' || TO_CHAR(l_start_date, 'DD-MON-YYYY HH24:MI:SS')|| ' p_source_type=' ||p_source_type ;
1863
1864 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1865 ICX_CAT_UTIL_PVT.logProcBegin(g_pkg_name, l_api_name, l_log_string);
1866 END IF;
1867 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1868
1869 l_err_loc := 600;
1870 --Initialize the purchasing category set info.
1871 ICX_CAT_UTIL_PVT.getPurchasingCategorySetInfo;
1872 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1873
1874 l_err_loc := 800;
1875 ICX_CAT_UTIL_PVT.setCommitParameter(FND_API.G_TRUE);
1876 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1877
1878 l_err_loc := 900;
1879 -- Set the global parameter ICX_CAT_UTIL_PVT.g_ItemCatgChange_const
1880 ICX_CAT_UTIL_PVT.g_ItemCatgChange_const := TRUE;
1881 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1882
1883 l_err_loc := 1000;
1884 -- Set the batch_size for the online case
1885 ICX_CAT_UTIL_PVT.setBatchSize;
1886 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,l_log_string||l_err_loc);
1887
1888 l_err_loc := 400;
1889 ICX_CAT_UTIL_PVT.setWhoColumns(g_request_id);
1890 if p_source_type = 'MASTER_ITEM' then
1891
1892 PO_ATTRIBUTE_VALUES_DATA_FIX;
1893
1894 open master_item_sync;
1895 loop
1896 fetch master_item_sync into l_inv_item_id,l_organization_id;
1897 exit when master_item_sync%NOTFOUND;
1898 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting inv_item_id='||l_inv_item_id|| ' organization_id'||l_organization_id );
1899
1900 ICX_CAT_POPULATE_MI_PVT.populateItemChange(l_inv_item_id, l_ORGANIZATION_ID,null,null);
1901 end loop;
1902 close master_item_sync;
1903 end if;
1904 if p_source_type = 'BLANKET' then
1905 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,p_source_type);
1906
1907 update po_headers_all set last_update_date = sysdate
1908 where po_header_id in ( select distinct pol.po_header_id from po_attribute_values_tlp po , icx_cat_items_ctx_hdrs_tlp ctx, po_lines_all pol
1909 where po.po_line_id=ctx.po_line_id
1910 and po.po_line_id=pol.po_line_id
1911 and ctx.source_type in ('BLANKET','QUOTATION','GLOBAL_BLANKET')
1912 and (po.ip_category_id <> ctx.ip_category_id or pol.unit_price <> ctx.unit_price)
1913 );
1914 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting BPA count='||Sql%ROWCOUNT);
1915
1916 select min(rowid), max(rowid) into l_min_row_id,l_max_row_id from po_headers_all;
1917 ICX_CAT_POPULATE_PODOCS_PVT.upgradeR12PODocs(sysdate-1,l_min_row_id,l_max_row_id);
1918 ICX_ITEM_DIAG_PVT.logStatement(g_pkg_name, l_api_name,'extracting done for bpa ');
1919
1920 end if;
1921 l_err_loc := 1200;
1922 COMMIT;
1923 l_err_loc := 1400;
1924 -- Call the rebuild index
1925 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
1926 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1927 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,
1928 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1929 'Rebuild indexes called.');
1930 END IF;
1931
1932 l_err_loc := 1600;
1933 l_end_date := sysdate;
1934 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1935 ICX_CAT_UTIL_PVT.logProcEnd(g_pkg_name, l_api_name,
1936 ' done in:' || ICX_CAT_UTIL_PVT.getTimeStats(l_start_date, l_end_date));
1937 END IF;
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 BEGIN
1941 ROLLBACK TO populateItemSync_sp;
1942 EXCEPTION
1943 WHEN OTHERS THEN
1944 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1945 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1946 ICX_CAT_UTIL_PVT.getModuleNameForDebug(g_pkg_name, l_api_name),
1947 'ROLLBACK TO the savepoint caused the exception -->'
1948 || SQLERRM);
1949 END IF;
1950 NULL;
1951 END;
1952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1953
1954 logUnexpectedException(g_pkg_name, l_api_name,'sync_sources '||sqlerrm||' code='||sqlcode);
1955
1956 end sync_sources;
1957
1958
1959 PROCEDURE logStatement
1960 ( p_pkg_name IN VARCHAR2 ,
1961 p_proc_name IN VARCHAR2 ,
1962 p_log_string IN VARCHAR2
1963 )
1964 IS
1965 l_err_loc PLS_INTEGER;
1966 BEGIN
1967 l_err_loc := 100;
1968 FND_FILE.PUT_LINE(FND_FILE.LOG, 'icx.plsql.' || UPPER(p_pkg_name) || '.' || UPPER(p_proc_name)|| '::'|| p_log_string);
1969 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1970 l_err_loc := 200;
1971 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, 'icx.plsql.' || UPPER(p_pkg_name) || '.' || UPPER(p_proc_name), p_log_string);
1972 l_err_loc := 400;
1973 END IF;
1974 EXCEPTION WHEN OTHERS THEN
1975 l_err_loc := 500;
1976 END logStatement;
1977
1978
1979 END ICX_ITEM_DIAG_PVT;