1 package body BOM_DIAGUNITTEST_ITMHLCHK as
2 /* $Header: BOMDGITB.pls 120.1.12020000.2 2012/11/14 08:28:17 leizhzha ship $ */
3 PROCEDURE init is
4 BEGIN
5 null;
6 END init;
7
8 PROCEDURE cleanup IS
9 BEGIN
10 -- test writer could insert special cleanup code here
11 NULL;
12 END cleanup;
13
14 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
15 report OUT NOCOPY JTF_DIAG_REPORT,
16 reportClob OUT NOCOPY CLOB) IS
17 reportStr LONG; -- REPORT
18 sqltxt VARCHAR2(9999); -- SQL select statement
19 c_username VARCHAR2(50); -- accept input for username
20 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
21 errStr VARCHAR2(4000); -- error message
22 fixInfo VARCHAR2(4000); -- fix tip
23 isFatal VARCHAR2(50); -- TRUE or FALSE
24 num_rows NUMBER;
25 dummy_num NUMBER;
26 row_limit NUMBER;
27 l_item_id NUMBER;
28 l_org_id NUMBER;
29 Value_link VARCHAR2(4000);
30 l_org_exists NUMBER;
31 l_script VARCHAR2(20);
32 l_index_match NUMBER;
33 l_seg_count NUMBER;
34 l_org_col_exists NUMBER;
35 l_where_clause VARCHAR2(500);
36 l_count NUMBER;
37 ln_cursor Integer;
38 sql_stmt VARCHAR2(4000);
39 ln_rows_proc INTEGER :=0;
40 l_ret_status BOOLEAN;
41 l_status VARCHAR2 (1);
42 l_industry VARCHAR2 (1);
43 l_oracle_schema VARCHAR2 (30);
44
45 Cursor c_item_status_codes is
46 select inventory_item_status_code status
47 from mtl_item_status_tl
48 order by inventory_item_status_code;
49
50 Cursor c_item_status_attr_val(l_item_status_code VARCHAR2) Is
51 select inventory_item_status_code,substr(attribute_name,18) attribute_name,attribute_value
52 from mtl_stat_attrib_values_all_v
53 where status_code_ndb = 1
54 and inventory_item_status_code=l_item_status_code
55 order by attribute_name;
56
57 Cursor list_of_unique_indexes(l_owner VARCHAR2) Is
58 select index_name
59 from all_indexes
60 where table_name = 'MTL_SYSTEM_ITEMS_B'
61 and owner = l_owner
62 and UNIQUENESS = 'UNIQUE';
63
64 BEGIN
65 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
66 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
67 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
68
69 /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
70 row_limit :=1000;
71 l_org_exists :=0; /* Initialize to zero */
72
73 -- accept input
74 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
75 l_script := nvl(upper(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Script',inputs)),'ALL');
76
77 -- JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Script Selected: '||l_script);
78
79
80 /* l_script is NOT a mandatory input. If it is not entered, then run all the scripts.
81 However if a script name is entered, then validate it for existence. */
82
83 If l_script not in ('DUP_ITEM','UNIQ_INDEX','IVGL_ACC','MISSING_TL','COST_ENABLED',
84 'SECONDARY_UOM','BUYER_ID','PLANNER_INACT',
85 'ATTR_DEPEND','STATTR_MISMATCH','ACTDATE_NN','ALL') Then
86
87 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Script Name');
88 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please choose a Script Name from the Lov or leave the field blank.');
89 statusStr := 'FAILURE';
90 isFatal := 'TRUE';
91 fixInfo := ' Please review the error message below and take corrective action.';
92 errStr := ' Invalid value for input field Script ';
93
94 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
95 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
96 Return;
97 End If;
98 /* End of script name validation*/
99
100 /* l_org_id is NOT a mandatory input. If it is not entered, then run the scripts for all orgs.
101 However if a value is entered for org_id, then validate it for existence. */
102
103 If l_org_id is not null Then /* validate if input org_id exists*/
104 Begin
105 select 1 into l_org_exists
106 from mtl_parameters
107 where organization_id = l_org_id;
108 Exception
109 When others Then
110 l_org_exists :=0;
111 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Organization Id');
112 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please either provide a valid value for the Organization Id or leave it blank.');
113 statusStr := 'FAILURE';
114 isFatal := 'TRUE';
115 fixInfo := ' Please review the error message below and take corrective action. ';
116 errStr := ' Invalid value for input field Organization Id ';
117
118 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
119 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
120 End;
121 End If; /* End of l_org_id is not null */
122
123 If (l_org_id is null) or (l_org_exists = 1) Then
124 /* Health Check scripts start */
125
126 /* Get the application installation info. References to Data Dictionary Objects without schema name
127 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
128 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
129
130 l_ret_status := fnd_installation.get_app_info ('INV'
131 , l_status
132 , l_industry
133 , l_oracle_schema
134 );
135
136 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
137
138 If l_script in ('ALL','DUP_ITEM') Then
139 /* Script 1 - Duplicate Item Names */
140 sqltxt := 'SELECT mif.PADDED_ITEM_NUMBER "Item Number", '||
141 ' mp.organization_code "Org Code", '||
142 ' mif.inventory_item_id "Item Id", '||
143 ' mif.organization_id "Org Id" '||
144 ' FROM mtl_item_flexfields mif , mtl_parameters mp '||
145 ' WHERE (mif.organization_id,PADDED_ITEM_NUMBER) in '||
146 ' (select organization_id , padded_item_number '||
147 ' from mtl_item_flexfields '||
148 ' group by organization_Id, PADDED_ITEM_NUMBER '||
149 ' having count(*) > 1 ) '||
150 ' AND mif.organization_id = mp.organization_id ';
151
152 if l_org_id is not null then
153 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
154 end if;
155 sqltxt := sqltxt || ' and rownum< '||row_limit;
156 sqltxt := sqltxt || ' order by mif.PADDED_ITEM_NUMBER, mp.organization_code';
157 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Duplicate Item Names ');
158
159 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
160 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
161 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
162 If (num_rows = row_limit -1 ) Then
163 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
164 <BR/> to prevent an excessively big output file <BR/>');
165 End If;
166 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
167 <BR/>Querying these items in Items form , transactions form , sales order form etc.
168 <BR/>will error thus making these items unusable.');
169
170 reportStr := '<BR/>
171 <BR/><u>ACTION</u>:
172 <BR/>If Duplicate Rows are returned:
173 <BR/> Case 1: Master Item duplicates:
174 <BR/> If Duplicate Item is present only in the Master org, then user needs to
175 <BR/> rename the Master Item which is not assigned to any child org.
176 <BR/> To rename the Master Item, make use of the below mentioned update script.
177 <BR/> Case 2: Both Child and Master org duplicates:
178 <BR/> If the Duplicate Item is present in both Master and Child orgs , then user can
179 <BR/> choose any one of the duplicated items and rename the same in the Master org.
180 <BR/> To rename the Master Item, make use of the below mentioned update script.
181 <BR/> Please use the below update statements to rename the Master Item.
182 <BR/> <BR/>Important: Please try these scripts on a TEST instance first.
183 <pre>
184 The below update statement can be used to correct a specific item.
185 Substitute the bind variables orgid and itemid with the appropriate values.
186
187 update mtl_system_items_b
188 set segment1 = ''segment1'' || ''_DUP''
189 where organization_id = :orgid
190 and inventory_item_id = :itemid;
191 </pre>
192 <BR/> Use ''Delete Items'' form to delete the renamed item.
193 <BR/>';
194 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
195 End If; /* End of Impact and Action */
196
197 statusStr := 'SUCCESS';
198 isFatal := 'FALSE';
199 JTF_DIAGNOSTIC_COREAPI.BRPrint;
200 End If; /* End of l_script */
201 /* End of Script 1 - Duplicate Item Names */
202
203 /* Script for Unique Index suggestion for Item Number segments in MSIB */
204
205 /* Get the application installation info. References to Data Dictionary Objects without schema name
206 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). For accessing all_indexes
207 in cursor list_of_unique_indexes and all_ind_columns in the query below we need to pass the schema name*/
208
209 l_ret_status := fnd_installation.get_app_info ('INV'
210 , l_status
211 , l_industry
212 , l_oracle_schema
213 );
214
215 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
216
217 If l_script in ('ALL','UNIQ_INDEX') Then
218
219 /* Logic of the script:
220 Let SetA = all the enabled segments for MSTK kff
221 SetB = all the columns in a particular index on table MTL_SYSTEM_ITEMS_B
222
223 If SetA - SetB = SetB - SetA Then SetA = SetB
224 */
225
226 Begin
227 /*Fetch Enabled System Items Segments */
228
229 sqltxt := 'select segment_num "Segment Number", segment_name "Segment Name", '||
230 ' application_column_name "Column Name", required_flag "Required Flag" '||
231 ' from fnd_id_flex_segments_vl '||
232 ' where application_id = 401 '||
233 ' and id_flex_code = ''MSTK'' '||
234 ' and id_flex_num = 101 '||
235 ' and enabled_flag = ''Y'' '||
236 ' order by segment_num ';
237
238 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Unique Index suggestion for Enabled Item Number Segments in mtl_system_items_b Table.');
239 statusStr := 'SUCCESS';
240 isFatal := 'FALSE';
241 /*End of Script to fetch Enabled System Items Segments */
242
243 If (num_rows = 0) Then
244 JTF_DIAGNOSTIC_COREAPI.Line_Out('None of the Item Number Segments are enabled. <BR/>');
245 Elsif (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
246 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Listed above are the enabled system items segments.
247 <BR/> Users are suggested to create an Unique Index on MTL_SYSTEM_ITEMS_B table
248 <BR/> containing all the enabled segments plus the organization_id column.
249 <BR/> For further details on creating such an index please refer to
250 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
251 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'',
252 <BR/> Section ''Open Item Interface'', SubSection ''Setting Up the Item Interface''.
253 <BR/> <BR/><u>IMPACT:</u> Presence of such an Index will improve the performance
254 <BR/> while querying up Items and will also avoid Duplicate Items from getting created.
255 <BR/><BR/> Verifying whether such an index exists....
256 <BR/> <u>Result:</u>');
257 l_index_match:=0;
258
259 For l_index in list_of_unique_indexes(l_oracle_schema) Loop
260 l_index_match:=0; /* reset to does not match*/
261 l_org_col_exists :=0;
262 l_seg_count :=0;
263
264 Begin
265
266 Select 1 into l_seg_count from dual
267 where not exists (
268 (select APPLICATION_COLUMN_NAME "Column_Name"
269 from FND_ID_FLEX_SEGMENTS
270 where ID_FLEX_CODE = 'MSTK'
271 and enabled_flag = 'Y'
272 MINUS
273 select ori.logical_column_name "Column_Name" from (select * from (select
274 tab.owner
275 , tab.table_name
276 , col.table_name real_table_name
277 , decode(ev.view_name, NULL, col.column_name, evc.view_column_name) logical_column_name
278 , col.column_name real_column_name
279 from
280 dba_tables tab
281 , dba_tab_columns col
282 , dba_editioning_views ev
283 , dba_editioning_view_cols evc
284 WHERE tab.table_name = 'MTL_SYSTEM_ITEMS_B'
285 and col.owner = tab.owner
286 and col.table_name = tab.table_name
287 and ev.owner(+) = tab.owner
288 and ev.table_name(+) = tab.table_name
289 and evc.owner(+) = ev.owner
290 AND evc.view_name(+) = ev.view_name
291 AND (ev.view_name IS NULL OR evc.table_column_name = col.column_name)) WHERE logical_column_name LIKE 'SEGMENT%') ori, all_ind_columns aic
292 WHERE ori.real_column_name = aic.column_name
293 and ori.real_table_name = aic.table_name
294 AND aic.index_name =l_index.index_name
295 AND aic.index_owner=l_oracle_schema)
296 UNION
297 (
298 select ori.logical_column_name "Column_Name" from (select * from (select
299 tab.owner
300 , tab.table_name
301 , col.table_name real_table_name
302 , decode(ev.view_name, NULL, col.column_name, evc.view_column_name) logical_column_name
303 , col.column_name real_column_name
304 from
305 dba_tables tab
306 , dba_tab_columns col
307 , dba_editioning_views ev
308 , dba_editioning_view_cols evc
309 WHERE tab.table_name = 'MTL_SYSTEM_ITEMS_B'
310 and col.owner = tab.owner
311 and col.table_name = tab.table_name
312 and ev.owner(+) = tab.owner
313 and ev.table_name(+) = tab.table_name
314 and evc.owner(+) = ev.owner
315 AND evc.view_name(+) = ev.view_name
316 AND (ev.view_name IS NULL OR evc.table_column_name = col.column_name)) WHERE logical_column_name LIKE 'SEGMENT%') ori, all_ind_columns aic
317 WHERE ori.real_column_name = aic.column_name
318 and ori.real_table_name = aic.table_name
319 AND aic.index_name =l_index.index_name
320 AND aic.index_owner=l_oracle_schema
321 MINUS
322 select APPLICATION_COLUMN_NAME "Column_Name"
323 from FND_ID_FLEX_SEGMENTS
324 where ID_FLEX_CODE = 'MSTK'
325 and enabled_flag = 'Y')
326 );
327
328 If l_seg_count = 1 Then /* Segment Match Found*/
329 Begin
330 select 1 into l_org_col_exists
331 from (select * from (select
332 tab.owner
333 , tab.table_name
334 , col.table_name real_table_name
335 , decode(ev.view_name, NULL, col.column_name, evc.view_column_name) logical_column_name
336 , col.column_name real_column_name
337 from
338 dba_tables tab
339 , dba_tab_columns col
340 , dba_editioning_views ev
341 , dba_editioning_view_cols evc
342 WHERE tab.table_name = 'MTL_SYSTEM_ITEMS_B'
343 and col.owner = tab.owner
344 and col.table_name = tab.table_name
345 and ev.owner(+) = tab.owner
346 and ev.table_name(+) = tab.table_name
347 and evc.owner(+) = ev.owner
348 AND evc.view_name(+) = ev.view_name
349 AND (ev.view_name IS NULL OR evc.table_column_name = col.column_name)) WHERE logical_column_name = 'ORGANIZATION_ID') ori, all_ind_columns aic
350 WHERE ori.real_column_name = aic.column_name
351 and ori.real_table_name = aic.table_name
352 AND aic.index_name =l_index.index_name
353 AND aic.index_owner=l_oracle_schema;
354 Exception
355 When Others Then
356 Null;
357 End;
358 End If;
359
360 If l_seg_count=1 and l_org_col_exists=1 Then
361 l_index_match :=1; /* Matching Index Exists */
362 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Unique Index '||l_index.index_name||' matches the Enabled System Items Segments + Organization_Id columns. <BR/>');
363 Exit;
364 End If;
365
366 Exception
367 When Others Then
368 null;
369 End;
370 End Loop;
371
372 If l_index_match=0 Then /* Match Not Found*/
373 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> No Unique Index as described above exists.
374 <BR/> <BR/><u>ACTION:</u> Please create a Unique Index on MTL_SYSTEM_ITEMS_B table
375 <BR/> containing all the enabled segments plus the organization_id column. <BR/>');
376 End If;
377 End If; /* End of Impact and Action */
378 End;
379 JTF_DIAGNOSTIC_COREAPI.BRPrint;
380 End If; /* End of l_script */
381 /* End of Script for Unique Index suggestion for Item Number segments in MSIB */
382
383 /* Script for fetching items with invalid GL accounts. */
384 If l_script in ('ALL','IVGL_ACC') Then
385 sqltxt := ' SELECT glcc.PADDED_CONCATENATED_SEGMENTS "Code Combination", '||
386 ' mif.PADDED_ITEM_NUMBER "Item Number", '||
387 ' mp.organization_code "Org Code", '||
388 ' glcc.code_combination_id "Code Combination Id", '||
389 ' mif.inventory_item_id "Item Id", '||
390 ' mp.organization_id "Org Id", '||
391 ' decode(mif.cost_of_sales_account,glcc.code_combination_Id,''X'') "Cost Of Sales Account", '||
392 ' decode(mif.sales_account,glcc.code_combination_Id,''X'') "Sales Account", '||
393 ' decode(mif.expense_account,glcc.code_combination_Id,''X'') "Expense Account", '||
394 ' decode(mif.encumbrance_account,glcc.code_combination_Id,''X'') "Encumbrance Account" '||
395 ' FROM gl_code_combinations_kfv glcc, mtl_item_flexfields mif , mtl_parameters mp '||
396 ' WHERE glcc.code_combination_id in (mif.cost_of_sales_account, mif.SALES_ACCOUNT, '||
397 ' mif.EXPENSE_ACCOUNT, mif.ENCUMBRANCE_ACCOUNT) '||
398 ' AND nvl(glcc.END_DATE_ACTIVE,sysdate) < sysdate '||
399 ' AND DETAIL_POSTING_ALLOWED = ''Y'' '||
400 ' AND CHART_OF_ACCOUNTS_ID in (select chart_of_accounts_id '||
401 ' from ORG_ORGANIZATION_DEFINITIONS ood '||
402 ' where ood.organization_id = mif.organization_id and rownum = 1) '||
403 ' AND mif.organization_id = mp.organization_id ';
404
405 if l_org_id is not null then
406 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
407 end if;
408 sqltxt := sqltxt || ' and rownum< '||row_limit;
409 sqltxt := sqltxt || ' order by glcc.PADDED_CONCATENATED_SEGMENTS, mif.PADDED_ITEM_NUMBER, mp.organization_code';
410
411 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items with Invalid GL Accounts');
412
413 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
414 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
415 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
416 If (num_rows = row_limit -1 ) Then
417 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
418 <BR/> to prevent an excessively big output file <BR/>');
419 End If;
420 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/>Account Columns marked with ''X'' correspond to invalid GL Accounts.');
421 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
422 <BR/> Downstream applications using these items might refer to
423 <BR/> these invalid GL accounts thus causing data corruption. ');
424
425 reportStr :=
426 '<BR/> <BR/><u>ACTION</u>:
427 <BR/> Please follow the below steps to correct each of the items with invalid GL Accounts.
428 <BR/> 1) Open (N) Inventory > Items form (Master Items/Organization Items).
429 <BR/> 2) Query for that Item.
430 <BR/> 3) Go to the respective invalid GL Account Code field.
431 <BR/> (T) Costing > ''Cost of Goods Sold Account'', (T) Invoicing > ''Sales Account'',
432 <BR/> (T) Purchasing > ''Expense Account'', (T) Purchasing > ''Encumbrance Account''.
433 <BR/> 4) Choose an appropriate valid GL Account from the Lov attached.
434 <BR/> 5) Save changes to this item.
435 <BR/> <u>Note</u>: If the number of items to corrected are huge, then Item Import functionality
436 <BR/> can also be used for correcting the GL Account fields.
437 <BR/> For details on Item Import, please refer to
438 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
439 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
440 <BR/>';
441 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
442 End If; /* End of Impact and Action */
443
444 statusStr := 'SUCCESS';
445 isFatal := 'FALSE';
446
447 JTF_DIAGNOSTIC_COREAPI.BRPrint;
448 End If; /* End of l_script */
449 /* End of script for fetching items with invalid GL accounts. */
450
451 /* Script to identify items present in mtl_system_items_b but missing in mtl_system_items_tl */
452 If l_script in ('ALL','MISSING_TL') Then
453 sqltxt := 'SELECT mif.PADDED_ITEM_NUMBER "Item Number", '||
454 ' mp.Organization_code "Org Code", '||
455 ' mif.inventory_item_id "Item Id", '||
456 ' mif.organization_id "Org Id" '||
457 ' FROM mtl_item_flexfields mif , mtl_parameters mp '||
458 ' WHERE Not Exists (SELECT ''x'' '||
459 ' FROM mtl_system_items_tl msitl '||
460 ' WHERE msitl.organization_id = mif.organization_id '||
461 ' AND msitl.inventory_item_id = mif.inventory_item_id) '||
462 ' AND mif.organization_id = mp.organization_id ';
463
464 if l_org_id is not null then
465 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
466 end if;
467 sqltxt := sqltxt || ' and rownum< '||row_limit;
468 sqltxt := sqltxt || ' order by mif.PADDED_ITEM_NUMBER, mp.organization_code';
469
470 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' List of Items present in mtl_system_items_b table but missing in mtl_system_items_tl table');
471
472 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
473 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
474 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
475 If (num_rows = row_limit -1 ) Then
476 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
477 <BR/> to prevent an excessively big output file <BR/>');
478 End If;
479 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
480 <BR/> These items cannot be queried and used in multiple forms across the application .
481 <BR/> For e.g. Items , Purchasing, Transactions, Sales order form etc. <BR/><BR/>');
482
483 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('<BR/> If Rows are returned with missing data in translations table,
484 <BR/> then please refer to metalink note : ' , '388325.1', '' );
485 End If; /* End of Impact and Action */
486
487 statusStr := 'SUCCESS';
488 isFatal := 'FALSE';
489
490 JTF_DIAGNOSTIC_COREAPI.BRPrint;
491 End If; /* End of l_script */
492 /* End of Script to identiy items present in mtl_system_items_b but missing in mtl_system_items_tl */
493
494
495 /* Script to Identify costing enabled items with missing data in costing tables */
496 If l_script in ('ALL','COST_ENABLED') Then
497 sqltxt := ' select '||
498 ' mif.padded_item_number "PADDED ITEM NUMBER", '||
499 ' mp.organization_code "ORGANIZATION CODE", '||
500 ' mif.costing_enabled_flag "COSTING ENABLED FLAG", '||
501 ' mif.inventory_asset_flag "INVENTORY ASSET FLAG", '||
502 ' mif.inventory_item_id "INVENTORY ITEM ID", '||
503 ' mp.organization_id "ORGANIZATION ID" '||
504 ' from mtl_item_flexfields mif, mtl_parameters mp '||
505 ' where mif.organization_id = mp.organization_id '||
506 ' and mif.costing_enabled_flag = ''Y'' '||
507 ' and not exists (select null '||
508 ' from cst_item_costs cic '||
509 ' where cic.organization_id = mif.organization_id '||
510 ' and cic.inventory_item_id = mif.inventory_item_id) ';
511
512 if l_org_id is not null then
513 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
514 end if;
515 sqltxt := sqltxt || ' and rownum< '||row_limit;
516 sqltxt :=sqltxt||' order by mif.padded_item_number, mp.organization_code';
517
518 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Costing Enabled Items with missing data in costing tables. ');
519
520 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
521 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
522 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
523 If (num_rows = row_limit -1 ) Then
524 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
525 <BR/> to prevent an excessively big output file <BR/>');
526 End If;
527 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
528 <BR/>These items cannot be costed which will cause data corruption in Costing module. ');
529
530 reportStr :=
531 ' <BR/><BR/><u>ACTION</u>:
532 <BR/> Please follow the below steps to create the missing data in costing tables
533 <BR/> for each item returned above.
534 <BR/> Case 1: (On Hand Quantity does not exist for the item in the corresponding organization)
535 <BR/> 1) Open (N) Inventory > Items form (Master Items/Organization Items).
536 <BR/> 2) Query for that Item.
537 <BR/> 3) Go to (T) Costing.
538 <BR/> 4) Select ''Costing Enabled'' checkbox. Select ''Inventory Asset Value'' checkbox if required.
539 <BR/> If these checkboxes are already checked, then uncheck them first and then check again.
540 <BR/> 5) Save changes to this item.
541 <BR/>
542 <BR/> Case 2:(On Hand Quantity exists for the item in the corresponding organization)
543 <BR/> 1) Issue out the on hand quantity for this item.
544 <BR/> Now <b><u>NO</u></b> On Hand Quantity exists for this item.
545 <BR/> 2) Follow the steps in case 1 to enable the costing flags.
546 <BR/> 3) Receive the quantity back into the organization.
547 <BR/>
548 <BR/> Caution:Costing Flags should be flipped only when NO On Hand Qty exists for an Item.
549 <BR/> Otherwise it will lead to severe data corruption.
550 <BR/> <u>Note</u>: If the number of items to corrected are huge, then Item Import functionality
551 <BR/> can also be used for flipping the costing flags.
552 <BR/> For details on Item Import, please refer to
553 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
554 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
555 <BR/>';
556
557 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
558 End If; /* End of Impact and Action */
559
560 statusStr := 'SUCCESS';
561 isFatal := 'FALSE';
562
563 JTF_DIAGNOSTIC_COREAPI.BRPrint;
564 End If; /* End of l_script */
565 /* End of costing enabled items with missing data in costing tables. */
566
567
568 /* Script to identify items whose tracking and pricing UOMs are set to Primary but
569 Secondary UOM has a non null value. So we check for the existence of the columns ont_pricing_qty_source,tracking_quantity_ind
570 in table mtl_system_items_b */
571
572 If l_script in ('ALL','SECONDARY_UOM') Then
573
574 sqltxt := ' select '||
575 ' mif.padded_item_number "item number", '||
576 ' mp.organization_code "org code", '||
577 ' mif.tracking_quantity_ind "tracking quantity ind", '||
578 ' mif.ont_pricing_qty_source "ont pricing qty source", '||
579 ' mif.secondary_uom_code "secondary uom code", '||
580 ' mif.inventory_item_id "inventory item id", '||
581 ' mif.organization_id "organization id" '||
582 ' from mtl_item_flexfields mif, mtl_parameters mp '||
583 ' where mif.organization_id=mp.organization_id '||
584 ' and nvl(mif.tracking_quantity_ind,''P'') = ''P'' '||
585 ' and nvl(mif.ont_pricing_qty_source,''P'') = ''P'' '||
586 ' and mif.secondary_uom_code is not null ';
587
588 if l_org_id is not null then
589 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
590 end if;
591 sqltxt := sqltxt || ' and rownum< '||row_limit;
592 sqltxt :=sqltxt||' order by mif.padded_item_number, mp.organization_code';
593
594 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items whose Tracking or Pricing fields (Items -> Main tab) set to Primary but Secondary UOM has a Non Null value.');
595
596 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
597 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
598 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
599 If (num_rows = row_limit -1 ) Then
600 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
601 <BR/> to prevent an excessively big output file <BR/>');
602 End If;
603 JTF_DIAGNOSTIC_COREAPI.Line_Out(' <BR/> When an item''s Tracking or Pricing field (Items -> main tab)
604 <BR/> is set to Primary, then its Secondary UOM should not be populated.');
605
606 JTF_DIAGNOSTIC_COREAPI.Line_Out(' <BR/><u>IMPACT:</u>
607 <BR/> Item import process cannot be run to update any of the Item attributes
608 <BR/> as users will receive the following error message.
609 <BR/> ''If tracking and pricing are set to Primary,
610 <BR/> then Secondary Unit of measure can only have a null value. '' ');
611
612 reportStr := '<BR/><BR/><u>ACTION</u>:
613 <BR/> Use Item Import in UPDATE mode to update the column SECONDARY_UOM_CODE
614 <BR/> as NULL for these Items.
615 <BR/> To null out varchar attributes through Item Import, the value ''!'' (exclamation mark)
616 <BR/> has to be populated for the corresponding column in the interface record.
617 <BR/> For details on Item Import, please refer to
618 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
619 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
620 <BR/>';
621 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
622 End If; /* End of Impact and Action */
623
624 statusStr := 'SUCCESS';
625 isFatal := 'FALSE';
626
627 JTF_DIAGNOSTIC_COREAPI.BRPrint;
628 End If; /* End of l_script */
629 /* End of script to identify items whose tracking and pricing UOMs are set to Primary but
630 Secondary UOM has a non null value.*/
631
632 /* Script to identify items attached with inactive buyers*/
633 If l_script in ('ALL','BUYER_ID') Then
634 sqltxt := ' select '||
635 ' mif.padded_item_number "Item Number", '||
636 ' mp.organization_code "Organization Code", '||
637 ' pav.agent_name "Default Buyer", '||
638 ' to_char(pav.start_date_active,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active", '||
639 ' to_char(pav.end_date_active,''DD-MON-YYYY HH24:MI:SS'') "End Date Active", '||
640 ' mif.inventory_item_id "Inventory Item Id", '||
641 ' mif.organization_id "Organization Id", '||
642 ' mif.buyer_id "Default Buyer Id" '||
643 ' from mtl_item_flexfields mif, mtl_parameters mp, po_agents_v pav '||
644 ' where mif.organization_id=mp.organization_id '||
645 ' and mif.buyer_id=pav.agent_id '||
646 ' and sysdate not between nvl(pav.start_date_active, sysdate-1) '||
647 ' and nvl(pav.end_date_active, sysdate+1) ';
648
649 if l_org_id is not null then
650 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
651 end if;
652 sqltxt := sqltxt || ' and rownum< '||row_limit;
653 sqltxt := sqltxt ||' order by mif.padded_item_number, mp.organization_code';
654
655 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items whose buyer_id corresponds to Inactive Buyers');
656
657 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
658 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
659 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
660 If (num_rows = row_limit -1 ) Then
661 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
662 <BR/> to prevent an excessively big output file <BR/>');
663 End If;
664 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
665 <BR/> Item import process cannot be run to update any of the Item attributes as users
666 <BR/> will receive error messages corresponding to inactive buyers. ');
667
668 reportStr := '<BR/><BR/><u>ACTION</u>:
669 <BR/> If records are fetched with inactive buyer_id,
670 <BR/> then please follow the below steps to correct them.
671 <BR/> 1) Open (N) Inventory > Items > Items form.
672 <BR/> If attribute ''Default Buyer'' is Master Controlled, then use Master Items form,
673 <BR/> Else if it is Org Controlled, then use Organization Items form.
674 <BR/> 2) Query for an item fetched above.
675 <BR/> 3) Go to (T) Purchasing > ''Default Buyer'' field.
676 <BR/> 4) Currently it will be blank.
677 <BR/> 5) Click on this field and tab out without entering any value for it.
678 <BR/> This will clear the buyer_id column.
679 <BR/> 6) Save the changes to the form.
680 <BR/>';
681 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
682
683
684 /*<BR/><BR/> Note: If the number of items to be corrected are huge, then use Item Import in UPDATE mode to null out the buyer_id column.
685 <BR/> For nulling out numeric attributes through IOI the value -999999 has to be populated for the corresponding column in the interface record.
686 */
687 End If; /* End of Impact and Action */
688
689 statusStr := 'SUCCESS';
690 isFatal := 'FALSE';
691
692 JTF_DIAGNOSTIC_COREAPI.BRPrint;
693 End If; /* End of l_script */
694 /* End of Script to identify items attached with inactive buyers*/
695
696 /* Script to identify items attached with inactive planners*/
697
698 If l_script in ('ALL','PLANNER_INACT') Then
699 sqltxt := ' select '||
700 ' mif.padded_item_number "Item Number", '||
701 ' mp.organization_code "Organization Code", '||
702 ' mpl.planner_code "Planner", '||
703 ' to_char(mpl.disable_date,''DD-MON-YYYY HH24:MI:SS'') "Planner Disable Date", '||
704 ' mif.inventory_item_id "Inventory Item Id", '||
705 ' mif.organization_id "Organization Id" '||
706 ' from mtl_item_flexfields mif, mtl_parameters mp, mtl_planners mpl '||
707 ' where mif.organization_id=mp.organization_id '||
708 ' and mif.organization_id=mpl.organization_id '||
709 ' and mif.planner_code=mpl.planner_code '||
710 ' and nvl(mpl.disable_date, sysdate+1) < sysdate ';
711
712 if l_org_id is not null then
713 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
714 end if;
715 sqltxt := sqltxt || ' and rownum< '||row_limit;
716 sqltxt := sqltxt ||' order by mif.padded_item_number, mp.organization_code, mpl.planner_code';
717
718 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items with Inactive Planners ');
719
720 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
721 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
722 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
723 If (num_rows = row_limit -1 ) Then
724 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
725 <BR/> to prevent an excessively big output file <BR/>');
726 End If;
727
728 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
729 <BR/> Item import process cannot be run to update any of the Item attributes as users
730 <BR/> will receive error messages corresponding to invalid planners. ');
731
732 reportStr := '<BR/><BR/><u>ACTION</u>:
733 <BR/> If records are fetched with inactive planners,
734 <BR/> then please follow the below steps to correct them.
735 <BR/> 1) Open (N) Inventory > Items > Items form.
736 <BR/> If attribute ''Planner'' is Master Controlled, then use Master Items form,
737 <BR/> Else if it is Org Controlled, then use Organization Items form.
738 <BR/> 2) Query for an item fetched above.
739 <BR/> 3) Go to (T) General Planning > Planner field.
740 <BR/> 4) Currently it will show the inactive planner.
741 <BR/> 5) Either clear this field or choose a valid planner from the lov.
742 <BR/> 6) Save the changes to the form. <BR/>
743 <BR/> <U>Note:</U> If the number of items to be corrected are huge, then
744 <BR/> use Item Import in UPDATE mode to update the column PLANNER_CODE
745 <BR/> for these Items to either a NULL value or a valid Planner.
746 <BR/> To null out varchar attributes through Item Import, the value ''!'' (exclamation mark)
747 <BR/> has to be populated for the corresponding column in the interface record.
748 <BR/> For details on Item Import, please refer to
749 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
750 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
751 <BR/>';
752 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
753
754
755 End If; /* End of Impact and Action */
756
757 statusStr := 'SUCCESS';
758 isFatal := 'FALSE';
759
760 JTF_DIAGNOSTIC_COREAPI.BRPrint;
761 End If; /* End of l_script */
762 /* End of Script to identify items with inactive planners*/
763
764
765 /* Script to identify items that fail the Interdependencies betweem Status Attributes. */
766 If l_script in ('ALL','ATTR_DEPEND') Then
767 sqltxt := ' Select '||
768 ' mif.padded_item_number "Item Number", '||
769 ' mp.organization_code "Org Code", '||
770 ' mif.inventory_item_flag "INVENTORY ITEM FLAG", '||
771 ' mif.purchasing_item_flag "PURCHASING ITEM FLAG", '||
772 ' mif.customer_order_flag "CUSTOMER ORDER FLAG", '||
773 ' mif.internal_order_flag "INTERNAL ORDER FLAG", '||
774 ' mif.invoiceable_item_flag "INVOICEABLE ITEM FLAG", '||
775 ' decode(mif.bom_item_type,1,''Model'',2,''Option Class'',3,''Planning'',4,''Standard'',5,''Product Family'') "BOM ITEM TYPE", '||
776 ' decode(stock_enabled_flag,inventory_item_flag,null,''N'',null,''Y'',stock_enabled_flag) "STOCK ENABLED FLAG", '||
777 ' decode(mtl_transactions_enabled_flag,stock_enabled_flag,null,''N'',null,''Y'',mtl_transactions_enabled_flag) "MTL TRANSACTIONS ENABLED FLAG", '||
778 ' decode(purchasing_enabled_flag,purchasing_item_flag,null,''N'',null,''Y'',purchasing_enabled_flag) "PURCHASING ENABLED FLAG", '||
779 ' decode(customer_order_enabled_flag,customer_order_flag,null,''N'',null,''Y'',customer_order_enabled_flag) "CUSTOMER ORDER ENABLED FLAG", '||
780 ' decode(internal_order_enabled_flag,internal_order_flag,null,''N'',null,''Y'',internal_order_enabled_flag) "INTERNAL ORDER ENABLED FLAG", '||
781 ' decode(invoice_enabled_flag,invoiceable_item_flag,null,''N'',null,''Y'',invoice_enabled_flag) "INVOICE ENABLED FLAG", '||
782 ' decode(build_in_wip_flag,inventory_item_flag,decode(build_in_wip_flag,decode(bom_item_type,1,''N'',2,''N'',3,''N'',5,''N'',build_in_wip_flag),null,build_in_wip_flag), '||
783 ' ''N'',decode(build_in_wip_flag,decode(bom_item_type,1,''N'',2,''N'',3,''N'',5,''N'',build_in_wip_flag),null,build_in_wip_flag),'||
784 ' ''Y'',build_in_wip_flag) "BUILD IN WIP FLAG", '||
785 ' mif.inventory_item_id "Inventory Item Id",mif.organization_id "Org Id" '||
786 ' from mtl_item_flexfields mif, mtl_parameters mp '||
787 ' where '||
788 ' mif.organization_id=mp.organization_id '||
789 ' and '||
790 ' ( '||
791 ' ( mif.inventory_item_flag=''N'' and mif.stock_enabled_flag=''Y'') '||
792 ' or (mif.inventory_item_flag=''N'' and mif.mtl_transactions_enabled_flag=''Y'') '||
793 ' or (mif.purchasing_item_flag=''N'' and mif.purchasing_enabled_flag=''Y'') '||
794 ' or (mif.customer_order_flag=''N'' and mif.customer_order_enabled_flag=''Y'') '||
795 ' or (mif.internal_order_flag=''N'' and mif.internal_order_enabled_flag=''Y'') '||
796 ' or (mif.invoiceable_item_flag=''N'' and mif.invoice_enabled_flag=''Y'') '||
797 ' or ( (mif.inventory_item_flag=''N'' or mif.bom_item_type <> 4) and mif.build_in_wip_flag=''Y'') '||
798 ' ) ';
799
800 If l_org_id is not null then
801 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
802 End if;
803 sqltxt := sqltxt || ' and rownum< '||row_limit;
804 sqltxt := sqltxt ||' order by mif.padded_item_number, mp.organization_code';
805
806 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items that fail the Interdependencies between Status Attributes.');
807
808 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
809 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
810 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
811 If (num_rows = row_limit -1 ) Then
812 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
813 <BR/> to prevent an excessively big output file <BR/>');
814 End If;
815 reportStr := '<BR/> Listed below are the interdependencies for the Item Status Attributes.
816 <BR/><BR/> 1. Stockable (stock_enabled_flag) Must be set to No
817 <BR/> If Inventory Item (inventory_item_flag) is set to No.
818 <BR/> 2. Transactable (mtl_transactions_enabled_flag) Must be set to No
819 <BR/> If Stockable (stock_enabled_flag) is set to No.
820 <BR/> 3. Purchasable (purchasing_enabled_flag) Must be set to No
821 <BR/> If Purchased (purchasing_item_flag) is set to No.
822 <BR/> 4. Build in WIP (build_in_wip_flag) Must be set to No
823 <BR/> If Inventory Item (inventory_item_flag) is set to No
824 <BR/> OR BOM Item Type (bom_item_type) is NOT set to Standard.
825 <BR/> 5. Customer Orders Enabled (customer_order_enabled_flag) Must be set to No
826 <BR/> If Customer Ordered Item (customer_order_flag) is set to No.
827 <BR/> 6. Internal Orders Enabled (internal_order_enabled_flag) Must be set to No
828 <BR/> If Internal Ordered Item (internal_order_flag) is set to No.
829 <BR/> 7. Invoice Enabled (invoice_enabled_flag) Must be set to No
830 <BR/> If Invoiceable Item (invoiceable_item_flag) is set to No.
831 <BR/><BR/> Note 1: In the output above, the Item Status Attributes
832 <BR/> successfully meeting the interdepencies are indicated with a null value.
833 <BR/> The ones showing ''Y'' indicate the failed status attributes.
834 <BR/> The current values (''Y'' or ''N'') of the Independent attributes are always shown.
835 <BR/> Note 2: For an item, if either Stockable or Transactable attributes are wrong
836 <BR/> then please verify and correct both.
837 <BR/><BR/><u>ACTION</u>:
838 <BR/> If records are fetched above,then please use Item Import in UPDATE mode
839 <BR/> to correct the respective Item Status Attribute value.
840 <BR/> For details on Item Import, please refer to
841 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
842 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
843 <BR/>';
844 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
845 End If; /* End of Impact and Action */
846
847 statusStr := 'SUCCESS';
848 isFatal := 'FALSE';
849
850 JTF_DIAGNOSTIC_COREAPI.BRPrint;
851 End If; /* End of l_script */
852 /* End of Script to identify items that fail the Interdependencies betweem Status Attributes. */
853
854
855 /* Script to identify Items violating validations between status code and status control attributes . */
856 If l_script in ('ALL','STATTR_MISMATCH') Then
857 Begin
858 delete from bom_diag_temp_tab; -- Clear the temporary tables
859 For l_item_st_rec in c_item_status_codes loop
860 l_where_clause :='';
861 l_where_clause :=l_where_clause ||' and mif.INVENTORY_ITEM_STATUS_CODE = '''||l_item_st_rec.status||''' and ( 1=2 ';
862 for l_st_attr_rec in c_item_status_attr_val(l_item_st_rec.status) loop
863 l_where_clause := l_where_clause||' or mif.'||l_st_attr_rec.attribute_name||' <> '''||l_st_attr_rec.attribute_value||'''';
864 end loop;
865 l_where_clause :=l_where_clause ||' ) '; /* append closing brace */
866
867 /* Insert the fetched records into temporary table bom_diag_temp_tab */
868 sql_stmt := '';
869 sql_stmt := ' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
870 ' char_col1,char_col2,char_col3,char_col4,char_col5,char_col6,char_col7,char_col8,char_col9) '||
871 ' Select mif.inventory_item_id , '||
872 ' mif.organization_Id, '||
873 ' MIF.INVENTORY_ITEM_STATUS_CODE, '||
874 ' MIF.BOM_ENABLED_FLAG, '||
875 ' MIF.BUILD_IN_WIP_FLAG, '||
876 ' MIF.CUSTOMER_ORDER_ENABLED_FLAG, '||
877 ' MIF.INTERNAL_ORDER_ENABLED_FLAG, '||
878 ' MIF.INVOICE_ENABLED_FLAG, '||
879 ' MIF.MTL_TRANSACTIONS_ENABLED_FLAG, '||
880 ' MIF.PURCHASING_ENABLED_FLAG, '||
881 ' MIF.STOCK_ENABLED_FLAG '||
882 ' From mtl_item_flexfields mif '||
883 ' Where 1=1 '||l_where_clause;
884 if l_org_id is not null then
885 sql_stmt :=sql_stmt||' and mif.organization_id = '||l_org_id;
886 end if;
887 /*sql_stmt := sql_stmt || ' and rownum< 5'; -- temporarily limiting the records to 5 */
888
889 ln_cursor := dbms_sql.open_cursor;
890 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
891 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
892 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
893
894 End Loop;
895
896 /* Display the records stored in temporary table bom_diag_temp_tab */
897 sqltxt := ' Select Inventory_Item_Id "Inventory Item Id", '||
898 ' Organization_id "Organization id", '||
899 ' Char_Col1 "Inventory Item Status Code", '||
900 ' Char_Col2 "BOM ENABLED FLAG", '||
901 ' Char_Col3 "BUILD IN WIP FLAG", '||
902 ' Char_Col4 "CUSTOMER ORDER ENABLED FLAG", '||
903 ' Char_Col5 "INTERNAL ORDER ENABLED FLAG", '||
904 ' Char_Col6 "INVOICE ENABLED FLAG", '||
905 ' Char_Col7 "MTL TRANSACTIONS ENABLED FLAG", '||
906 ' Char_Col8 "PURCHASING ENABLED FLAG", '||
907 ' Char_Col9 "STOCK ENABLED FLAG" '||
908 ' From bom_diag_temp_tab where 1=1 ';
909
910 sqltxt := sqltxt || ' and rownum< '||row_limit;
911 sqltxt :=sqltxt||' order by inventory_item_id, organization_id';
912
913 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items violating validations between status code and status control attributes ');
914
915 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
916 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
917 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
918 If (num_rows = row_limit -1 ) Then
919 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
920 <BR/> to prevent an excessively big output file <BR/>');
921 End If;
922 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/>
923 The values of the Status Control Attributes that use ''Sets Value'' status setting should
924 <BR/> always be in sync with their corresponding values of the Item Status Code chosen.
925 <BR/> Listed above are the items that violate this validation.
926 <BR/> The values of all their Status Control Attributes
927 <BR/> along with the Item Status Code are also listed.
928 <BR/> Please refer to the below table that lists the Status Control Attributes
929 <BR/> using ''Sets Value'' status setting. If no rows are fetched,
930 <BR/> it implies that none of the Status Control Attributes use ''Sets Value''.
931 <BR/>');
932
933 sqltxt := ' select attribute_name "Attribute Name" '||
934 ' from mtl_item_attributes '||
935 ' where status_control_code = 1 ';
936 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
937
938 reportStr := ('<BR/><u>ACTION</u>:
939 <BR/>If records are fetched above listing items that violate this validation, then
940 <BR/>1) Please use Item Import in UPDATE mode to modify the Item Status Code
941 <BR/>which will in turn set the values of all Status Control Attributes.
942 <BR/>2) If required, the values of the Status Controlled Attributes that
943 <BR/>do NOT use ''Sets Value'' setting, can then be changed.
944 <BR/> For details on Item Import, please refer to
945 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
946 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
947 <BR/>');
948
949 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
950 End If; /* End of Impact and Action */
951
952 statusStr := 'SUCCESS';
953 isFatal := 'FALSE';
954
955 End;
956
957 JTF_DIAGNOSTIC_COREAPI.BRPrint;
958 End If; /* End of l_script */
959 /* End of Script to identify Items violating validations between status code and status control attributes . */
960
961 /* Script to fetch items whose start_date_active, end_date_active columns are not null */
962 If l_script in ('ALL','ACTDATE_NN') Then
963 sqltxt := ' SELECT mif.padded_item_number "Item Number", '||
964 ' mp.organization_code "Org Code", '||
965 ' to_char(mif.start_date_active,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active", '||
966 ' to_char(mif.end_date_active,''DD-MON-YYYY HH24:MI:SS'') "End Date Active", '||
967 ' mif.inventory_item_id "Item Id", '||
968 ' mp.organization_id "Org Id" '||
969 ' FROM mtl_item_flexfields mif , mtl_parameters mp '||
970 ' WHERE 1=1 '||
971 ' AND mif.organization_id=mp.organization_id '||
972 ' AND ( mif.end_date_active IS NOT NULL OR mif.start_date_active IS NOT NULL ) ';
973
974 if l_org_id is not null then
975 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
976 end if;
977 sqltxt := sqltxt || ' and rownum< '||row_limit;
978 sqltxt := sqltxt || ' order by mif.PADDED_ITEM_NUMBER, mp.organization_code';
979
980 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items whose start_date_active, end_date_active columns are populated ');
981
982 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
983 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
984 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
985 If (num_rows = row_limit -1 ) Then
986 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
987 <BR/> to prevent an excessively big output file <BR/>');
988 End If;
989 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> start_date_active and end_date_active date fields
990 <BR/> are NOT used to determine the effectivity of an item.
991 <BR/> The effectivity of an item is determined by its Item Status Code.
992 <BR/><BR/><u>IMPACT:</u>
993 <BR/> None of the attributes of these items can be updated.
994 <BR/> FRM-40654 error may also occur while trying to
995 <BR/> update these items through the Items form.
996 <BR/>');
997
998 reportStr := '<BR/><u>ACTION</u>:
999 <BR/> These two date fields have to be nulled out.
1000 <BR/> Nulling them out will not effect any standard functionality in Items form.
1001 <BR/> Please use the below update statements to null out these two date fields.
1002 <BR/> <BR/>Important: Please try these scripts on a TEST instance first.
1003 <pre>
1004 The below update statement can be used to correct a specific item.
1005 Substitute the bind variables orgid and itemid
1006 with the appropriate values.
1007
1008 update mtl_system_items_b
1009 set start_date_active = null
1010 ,end_date_active = null
1011 where organization_id = :orgid
1012 and inventory_item_id = :itemid;
1013
1014 The below two update statements can be used
1015 to collectively correct all the problematic items.
1016
1017 update mtl_system_items_b
1018 set start_date_active = null
1019 where start_date_active is not null;
1020
1021 update mtl_system_items_b
1022 set end_date_active = null
1023 where end_date_active is not null; </pre>
1024 <BR/>';
1025 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
1026 End If; /* End of Impact and Action */
1027
1028 statusStr := 'SUCCESS';
1029 isFatal := 'FALSE';
1030
1031 JTF_DIAGNOSTIC_COREAPI.BRPrint;
1032 End If; /* End of l_script */
1033 /* End of Script to fetch items whose start_date_active, end_date_active columns are not null */
1034
1035 ----------------
1036 <<l_test_end>>
1037 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This Health Check Test completed as expected <BR/>');
1038 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1039 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1040
1041 End If; /* End of l_org_id is null or l_org_exists=1 */
1042
1043 EXCEPTION
1044 when others then
1045 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
1046 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
1047 statusStr := 'FAILURE';
1048 errStr := sqlerrm ||' occurred in script. ';
1049 fixInfo := 'Unexpected Exception in BOMDGITB.pls';
1050 isFatal := 'FALSE';
1051 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1052 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1053
1054 END runTest;
1055
1056 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
1057 BEGIN
1058 name := 'Items Data Health Check Details';
1059 END getComponentName;
1060
1061 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
1062 BEGIN
1063 descStr := 'This diagnostic test performs a variety of health checks against Items <BR/>
1064 and provides suggestions on how to solve possible issues.<BR/>
1065 It is recommended to run this health check periodically.';
1066 END getTestDesc;
1067
1068 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
1069 BEGIN
1070 name := 'Items Data Health Check';
1071 END getTestName;
1072
1073 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
1074 tempDependencies JTF_DIAG_DEPENDTBL;
1075
1076 BEGIN
1077 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
1078 END getDependencies;
1079
1080 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
1081 BEGIN
1082 str := 'FALSE';
1083 END isDependencyPipelined;
1084
1085
1086 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
1087 tempOutput JTF_DIAG_OUTPUTTBL;
1088 BEGIN
1089 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1090 outputValues := tempOutput;
1091 EXCEPTION
1092 when others then
1093 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1094 END getOutputValues;
1095
1096
1097 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
1098 tempInput JTF_DIAG_INPUTTBL;
1099 BEGIN
1100 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1101 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
1102 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Script','LOV-oracle.apps.bom.diag.lov.ItemHealthLov');
1103 defaultInputValues := tempInput;
1104 EXCEPTION
1105 when others then
1106 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1107 END getDefaultTestParams;
1108
1109 Function getTestMode return INTEGER IS
1110 BEGIN
1111 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
1112
1113 END getTestMode;
1114
1115 END BOM_DIAGUNITTEST_ITMHLCHK;
[Home] [Help]