1 package body BOM_DIAGUNITTEST_ITMHLCHK as
2 /* $Header: BOMDGITB.pls 120.1 2007/12/26 09:54:57 vggarg noship $ */
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 column_name "Column_Name"
274 from all_ind_columns aic
275 where table_name = 'MTL_SYSTEM_ITEMS_B'
276 and aic.index_name =l_index.index_name
277 and index_owner=l_oracle_schema
278 and COLUMN_NAME LIKE 'SEGMENT%')
279 UNION
280 (
281 select column_name "Column_Name"
282 from all_ind_columns aic
283 where table_name = 'MTL_SYSTEM_ITEMS_B'
284 and aic.index_name =l_index.index_name
285 and index_owner=l_oracle_schema
286 and COLUMN_NAME LIKE 'SEGMENT%'
287 MINUS
288 select APPLICATION_COLUMN_NAME "Column_Name"
289 from FND_ID_FLEX_SEGMENTS
290 where ID_FLEX_CODE = 'MSTK'
291 and enabled_flag = 'Y')
292 );
293
294 If l_seg_count = 1 Then /* Segment Match Found*/
295 Begin
296 Select 1 into l_org_col_exists
297 from all_ind_columns aic
298 where table_name = 'MTL_SYSTEM_ITEMS_B'
299 and aic.index_name = l_index.index_name
300 and index_owner=l_oracle_schema
301 and COLUMN_NAME ='ORGANIZATION_ID';
302 Exception
303 When Others Then
304 Null;
305 End;
306 End If;
307
308 If l_seg_count=1 and l_org_col_exists=1 Then
309 l_index_match :=1; /* Matching Index Exists */
310 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Unique Index '||l_index.index_name||' matches the Enabled System Items Segments + Organization_Id columns. <BR/>');
311 Exit;
312 End If;
313
314 Exception
315 When Others Then
316 null;
317 End;
318 End Loop;
319
320 If l_index_match=0 Then /* Match Not Found*/
321 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> No Unique Index as described above exists.
322 <BR/> <BR/><u>ACTION:</u> Please create a Unique Index on MTL_SYSTEM_ITEMS_B table
323 <BR/> containing all the enabled segments plus the organization_id column. <BR/>');
324 End If;
325 End If; /* End of Impact and Action */
326 End;
327 JTF_DIAGNOSTIC_COREAPI.BRPrint;
328 End If; /* End of l_script */
329 /* End of Script for Unique Index suggestion for Item Number segments in MSIB */
330
331 /* Script for fetching items with invalid GL accounts. */
332 If l_script in ('ALL','IVGL_ACC') Then
333 sqltxt := ' SELECT glcc.PADDED_CONCATENATED_SEGMENTS "Code Combination", '||
334 ' mif.PADDED_ITEM_NUMBER "Item Number", '||
335 ' mp.organization_code "Org Code", '||
336 ' glcc.code_combination_id "Code Combination Id", '||
337 ' mif.inventory_item_id "Item Id", '||
338 ' mp.organization_id "Org Id", '||
339 ' decode(mif.cost_of_sales_account,glcc.code_combination_Id,''X'') "Cost Of Sales Account", '||
340 ' decode(mif.sales_account,glcc.code_combination_Id,''X'') "Sales Account", '||
341 ' decode(mif.expense_account,glcc.code_combination_Id,''X'') "Expense Account", '||
342 ' decode(mif.encumbrance_account,glcc.code_combination_Id,''X'') "Encumbrance Account" '||
343 ' FROM gl_code_combinations_kfv glcc, mtl_item_flexfields mif , mtl_parameters mp '||
344 ' WHERE glcc.code_combination_id in (mif.cost_of_sales_account, mif.SALES_ACCOUNT, '||
345 ' mif.EXPENSE_ACCOUNT, mif.ENCUMBRANCE_ACCOUNT) '||
346 ' AND nvl(glcc.END_DATE_ACTIVE,sysdate) < sysdate '||
347 ' AND DETAIL_POSTING_ALLOWED = ''Y'' '||
348 ' AND CHART_OF_ACCOUNTS_ID in (select chart_of_accounts_id '||
349 ' from ORG_ORGANIZATION_DEFINITIONS ood '||
350 ' where ood.organization_id = mif.organization_id and rownum = 1) '||
351 ' AND mif.organization_id = mp.organization_id ';
352
353 if l_org_id is not null then
354 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
355 end if;
356 sqltxt := sqltxt || ' and rownum< '||row_limit;
357 sqltxt := sqltxt || ' order by glcc.PADDED_CONCATENATED_SEGMENTS, mif.PADDED_ITEM_NUMBER, mp.organization_code';
358
362 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
359 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items with Invalid GL Accounts');
360
361 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
363 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
364 If (num_rows = row_limit -1 ) Then
365 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
366 <BR/> to prevent an excessively big output file <BR/>');
367 End If;
368 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/>Account Columns marked with ''X'' correspond to invalid GL Accounts.');
369 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
370 <BR/> Downstream applications using these items might refer to
371 <BR/> these invalid GL accounts thus causing data corruption. ');
372
373 reportStr :=
374 '<BR/> <BR/><u>ACTION</u>:
375 <BR/> Please follow the below steps to correct each of the items with invalid GL Accounts.
376 <BR/> 1) Open (N) Inventory > Items form (Master Items/Organization Items).
377 <BR/> 2) Query for that Item.
378 <BR/> 3) Go to the respective invalid GL Account Code field.
379 <BR/> (T) Costing > ''Cost of Goods Sold Account'', (T) Invoicing > ''Sales Account'',
380 <BR/> (T) Purchasing > ''Expense Account'', (T) Purchasing > ''Encumbrance Account''.
381 <BR/> 4) Choose an appropriate valid GL Account from the Lov attached.
382 <BR/> 5) Save changes to this item.
383 <BR/> <u>Note</u>: If the number of items to corrected are huge, then Item Import functionality
384 <BR/> can also be used for correcting the GL Account fields.
385 <BR/> For details on Item Import, please refer to
386 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
387 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
388 <BR/>';
389 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
390 End If; /* End of Impact and Action */
391
392 statusStr := 'SUCCESS';
393 isFatal := 'FALSE';
394
395 JTF_DIAGNOSTIC_COREAPI.BRPrint;
396 End If; /* End of l_script */
397 /* End of script for fetching items with invalid GL accounts. */
398
399 /* Script to identify items present in mtl_system_items_b but missing in mtl_system_items_tl */
400 If l_script in ('ALL','MISSING_TL') Then
401 sqltxt := 'SELECT mif.PADDED_ITEM_NUMBER "Item Number", '||
402 ' mp.Organization_code "Org Code", '||
403 ' mif.inventory_item_id "Item Id", '||
404 ' mif.organization_id "Org Id" '||
405 ' FROM mtl_item_flexfields mif , mtl_parameters mp '||
406 ' WHERE Not Exists (SELECT ''x'' '||
407 ' FROM mtl_system_items_tl msitl '||
408 ' WHERE msitl.organization_id = mif.organization_id '||
409 ' AND msitl.inventory_item_id = mif.inventory_item_id) '||
410 ' AND mif.organization_id = mp.organization_id ';
411
412 if l_org_id is not null then
413 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
414 end if;
415 sqltxt := sqltxt || ' and rownum< '||row_limit;
416 sqltxt := sqltxt || ' order by mif.PADDED_ITEM_NUMBER, mp.organization_code';
417
418 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');
419
420 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
421 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
422 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
423 If (num_rows = row_limit -1 ) Then
424 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
425 <BR/> to prevent an excessively big output file <BR/>');
426 End If;
427 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
428 <BR/> These items cannot be queried and used in multiple forms across the application .
429 <BR/> For e.g. Items , Purchasing, Transactions, Sales order form etc. <BR/><BR/>');
430
431 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('<BR/> If Rows are returned with missing data in translations table,
432 <BR/> then please refer to metalink note : ' , '388325.1', '' );
433 End If; /* End of Impact and Action */
434
435 statusStr := 'SUCCESS';
436 isFatal := 'FALSE';
437
438 JTF_DIAGNOSTIC_COREAPI.BRPrint;
439 End If; /* End of l_script */
440 /* End of Script to identiy items present in mtl_system_items_b but missing in mtl_system_items_tl */
441
442
443 /* Script to Identify costing enabled items with missing data in costing tables */
444 If l_script in ('ALL','COST_ENABLED') Then
445 sqltxt := ' select '||
446 ' mif.padded_item_number "PADDED ITEM NUMBER", '||
447 ' mp.organization_code "ORGANIZATION CODE", '||
448 ' mif.costing_enabled_flag "COSTING ENABLED FLAG", '||
449 ' mif.inventory_asset_flag "INVENTORY ASSET FLAG", '||
450 ' mif.inventory_item_id "INVENTORY ITEM ID", '||
451 ' mp.organization_id "ORGANIZATION ID" '||
452 ' from mtl_item_flexfields mif, mtl_parameters mp '||
453 ' where mif.organization_id = mp.organization_id '||
454 ' and mif.costing_enabled_flag = ''Y'' '||
455 ' and not exists (select null '||
456 ' from cst_item_costs cic '||
457 ' where cic.organization_id = mif.organization_id '||
458 ' and cic.inventory_item_id = mif.inventory_item_id) ';
459
460 if l_org_id is not null then
464 sqltxt :=sqltxt||' order by mif.padded_item_number, mp.organization_code';
461 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
462 end if;
463 sqltxt := sqltxt || ' and rownum< '||row_limit;
465
466 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Costing Enabled Items with missing data in costing tables. ');
467
468 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
469 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
470 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
471 If (num_rows = row_limit -1 ) Then
472 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
473 <BR/> to prevent an excessively big output file <BR/>');
474 End If;
475 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
476 <BR/>These items cannot be costed which will cause data corruption in Costing module. ');
477
478 reportStr :=
479 ' <BR/><BR/><u>ACTION</u>:
480 <BR/> Please follow the below steps to create the missing data in costing tables
481 <BR/> for each item returned above.
482 <BR/> Case 1: (On Hand Quantity does not exist for the item in the corresponding organization)
483 <BR/> 1) Open (N) Inventory > Items form (Master Items/Organization Items).
484 <BR/> 2) Query for that Item.
485 <BR/> 3) Go to (T) Costing.
486 <BR/> 4) Select ''Costing Enabled'' checkbox. Select ''Inventory Asset Value'' checkbox if required.
487 <BR/> If these checkboxes are already checked, then uncheck them first and then check again.
488 <BR/> 5) Save changes to this item.
489 <BR/>
490 <BR/> Case 2:(On Hand Quantity exists for the item in the corresponding organization)
491 <BR/> 1) Issue out the on hand quantity for this item.
492 <BR/> Now <b><u>NO</u></b> On Hand Quantity exists for this item.
493 <BR/> 2) Follow the steps in case 1 to enable the costing flags.
494 <BR/> 3) Receive the quantity back into the organization.
495 <BR/>
496 <BR/> Caution:Costing Flags should be flipped only when NO On Hand Qty exists for an Item.
497 <BR/> Otherwise it will lead to severe data corruption.
498 <BR/> <u>Note</u>: If the number of items to corrected are huge, then Item Import functionality
499 <BR/> can also be used for flipping the costing flags.
500 <BR/> For details on Item Import, please refer to
501 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
502 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
503 <BR/>';
504
505 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
506 End If; /* End of Impact and Action */
507
508 statusStr := 'SUCCESS';
509 isFatal := 'FALSE';
510
511 JTF_DIAGNOSTIC_COREAPI.BRPrint;
512 End If; /* End of l_script */
513 /* End of costing enabled items with missing data in costing tables. */
514
515
516 /* Script to identify items whose tracking and pricing UOMs are set to Primary but
517 Secondary UOM has a non null value. So we check for the existence of the columns ont_pricing_qty_source,tracking_quantity_ind
518 in table mtl_system_items_b */
519
520 If l_script in ('ALL','SECONDARY_UOM') Then
521
522 sqltxt := ' select '||
523 ' mif.padded_item_number "item number", '||
524 ' mp.organization_code "org code", '||
525 ' mif.tracking_quantity_ind "tracking quantity ind", '||
526 ' mif.ont_pricing_qty_source "ont pricing qty source", '||
527 ' mif.secondary_uom_code "secondary uom code", '||
528 ' mif.inventory_item_id "inventory item id", '||
529 ' mif.organization_id "organization id" '||
530 ' from mtl_item_flexfields mif, mtl_parameters mp '||
531 ' where mif.organization_id=mp.organization_id '||
532 ' and nvl(mif.tracking_quantity_ind,''P'') = ''P'' '||
533 ' and nvl(mif.ont_pricing_qty_source,''P'') = ''P'' '||
534 ' and mif.secondary_uom_code is not null ';
535
536 if l_org_id is not null then
537 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
538 end if;
539 sqltxt := sqltxt || ' and rownum< '||row_limit;
540 sqltxt :=sqltxt||' order by mif.padded_item_number, mp.organization_code';
541
542 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.');
543
547 If (num_rows = row_limit -1 ) Then
544 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
545 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
546 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
548 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
549 <BR/> to prevent an excessively big output file <BR/>');
550 End If;
551 JTF_DIAGNOSTIC_COREAPI.Line_Out(' <BR/> When an item''s Tracking or Pricing field (Items -> main tab)
552 <BR/> is set to Primary, then its Secondary UOM should not be populated.');
553
554 JTF_DIAGNOSTIC_COREAPI.Line_Out(' <BR/><u>IMPACT:</u>
555 <BR/> Item import process cannot be run to update any of the Item attributes
556 <BR/> as users will receive the following error message.
557 <BR/> ''If tracking and pricing are set to Primary,
558 <BR/> then Secondary Unit of measure can only have a null value. '' ');
559
560 reportStr := '<BR/><BR/><u>ACTION</u>:
561 <BR/> Use Item Import in UPDATE mode to update the column SECONDARY_UOM_CODE
562 <BR/> as NULL for these Items.
563 <BR/> To null out varchar attributes through Item Import, the value ''!'' (exclamation mark)
564 <BR/> has to be populated for the corresponding column in the interface record.
565 <BR/> For details on Item Import, please refer to
566 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
567 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
568 <BR/>';
569 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
570 End If; /* End of Impact and Action */
571
572 statusStr := 'SUCCESS';
573 isFatal := 'FALSE';
574
575 JTF_DIAGNOSTIC_COREAPI.BRPrint;
576 End If; /* End of l_script */
577 /* End of script to identify items whose tracking and pricing UOMs are set to Primary but
578 Secondary UOM has a non null value.*/
579
580 /* Script to identify items attached with inactive buyers*/
581 If l_script in ('ALL','BUYER_ID') Then
582 sqltxt := ' select '||
583 ' mif.padded_item_number "Item Number", '||
584 ' mp.organization_code "Organization Code", '||
585 ' pav.agent_name "Default Buyer", '||
586 ' to_char(pav.start_date_active,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active", '||
587 ' to_char(pav.end_date_active,''DD-MON-YYYY HH24:MI:SS'') "End Date Active", '||
588 ' mif.inventory_item_id "Inventory Item Id", '||
589 ' mif.organization_id "Organization Id", '||
590 ' mif.buyer_id "Default Buyer Id" '||
591 ' from mtl_item_flexfields mif, mtl_parameters mp, po_agents_v pav '||
592 ' where mif.organization_id=mp.organization_id '||
593 ' and mif.buyer_id=pav.agent_id '||
594 ' and sysdate not between nvl(pav.start_date_active, sysdate-1) '||
595 ' and nvl(pav.end_date_active, sysdate+1) ';
596
597 if l_org_id is not null then
598 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
599 end if;
600 sqltxt := sqltxt || ' and rownum< '||row_limit;
601 sqltxt := sqltxt ||' order by mif.padded_item_number, mp.organization_code';
602
603 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items whose buyer_id corresponds to Inactive Buyers');
604
605 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
606 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
607 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
608 If (num_rows = row_limit -1 ) Then
609 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
610 <BR/> to prevent an excessively big output file <BR/>');
611 End If;
612 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
613 <BR/> Item import process cannot be run to update any of the Item attributes as users
614 <BR/> will receive error messages corresponding to inactive buyers. ');
615
616 reportStr := '<BR/><BR/><u>ACTION</u>:
617 <BR/> If records are fetched with inactive buyer_id,
618 <BR/> then please follow the below steps to correct them.
619 <BR/> 1) Open (N) Inventory > Items > Items form.
620 <BR/> If attribute ''Default Buyer'' is Master Controlled, then use Master Items form,
621 <BR/> Else if it is Org Controlled, then use Organization Items form.
622 <BR/> 2) Query for an item fetched above.
623 <BR/> 3) Go to (T) Purchasing > ''Default Buyer'' field.
624 <BR/> 4) Currently it will be blank.
625 <BR/> 5) Click on this field and tab out without entering any value for it.
626 <BR/> This will clear the buyer_id column.
627 <BR/> 6) Save the changes to the form.
628 <BR/>';
629 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
630
631
632 /*<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.
633 <BR/> For nulling out numeric attributes through IOI the value -999999 has to be populated for the corresponding column in the interface record.
634 */
635 End If; /* End of Impact and Action */
636
637 statusStr := 'SUCCESS';
638 isFatal := 'FALSE';
639
640 JTF_DIAGNOSTIC_COREAPI.BRPrint;
641 End If; /* End of l_script */
642 /* End of Script to identify items attached with inactive buyers*/
643
644 /* Script to identify items attached with inactive planners*/
645
646 If l_script in ('ALL','PLANNER_INACT') Then
647 sqltxt := ' select '||
648 ' mif.padded_item_number "Item Number", '||
649 ' mp.organization_code "Organization Code", '||
650 ' mpl.planner_code "Planner", '||
654 ' from mtl_item_flexfields mif, mtl_parameters mp, mtl_planners mpl '||
651 ' to_char(mpl.disable_date,''DD-MON-YYYY HH24:MI:SS'') "Planner Disable Date", '||
652 ' mif.inventory_item_id "Inventory Item Id", '||
653 ' mif.organization_id "Organization Id" '||
655 ' where mif.organization_id=mp.organization_id '||
656 ' and mif.organization_id=mpl.organization_id '||
657 ' and mif.planner_code=mpl.planner_code '||
658 ' and nvl(mpl.disable_date, sysdate+1) < sysdate ';
659
660 if l_org_id is not null then
661 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
662 end if;
663 sqltxt := sqltxt || ' and rownum< '||row_limit;
664 sqltxt := sqltxt ||' order by mif.padded_item_number, mp.organization_code, mpl.planner_code';
665
666 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items with Inactive Planners ');
667
668 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
669 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
670 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
671 If (num_rows = row_limit -1 ) Then
672 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
673 <BR/> to prevent an excessively big output file <BR/>');
674 End If;
675
676 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
677 <BR/> Item import process cannot be run to update any of the Item attributes as users
678 <BR/> will receive error messages corresponding to invalid planners. ');
679
680 reportStr := '<BR/><BR/><u>ACTION</u>:
681 <BR/> If records are fetched with inactive planners,
682 <BR/> then please follow the below steps to correct them.
683 <BR/> 1) Open (N) Inventory > Items > Items form.
684 <BR/> If attribute ''Planner'' is Master Controlled, then use Master Items form,
685 <BR/> Else if it is Org Controlled, then use Organization Items form.
686 <BR/> 2) Query for an item fetched above.
687 <BR/> 3) Go to (T) General Planning > Planner field.
688 <BR/> 4) Currently it will show the inactive planner.
689 <BR/> 5) Either clear this field or choose a valid planner from the lov.
690 <BR/> 6) Save the changes to the form. <BR/>
691 <BR/> <U>Note:</U> If the number of items to be corrected are huge, then
692 <BR/> use Item Import in UPDATE mode to update the column PLANNER_CODE
693 <BR/> for these Items to either a NULL value or a valid Planner.
694 <BR/> To null out varchar attributes through Item Import, the value ''!'' (exclamation mark)
695 <BR/> has to be populated for the corresponding column in the interface record.
696 <BR/> For details on Item Import, please refer to
697 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
698 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
699 <BR/>';
700 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
701
702
703 End If; /* End of Impact and Action */
704
705 statusStr := 'SUCCESS';
706 isFatal := 'FALSE';
707
708 JTF_DIAGNOSTIC_COREAPI.BRPrint;
709 End If; /* End of l_script */
710 /* End of Script to identify items with inactive planners*/
711
712
713 /* Script to identify items that fail the Interdependencies betweem Status Attributes. */
714 If l_script in ('ALL','ATTR_DEPEND') Then
715 sqltxt := ' Select '||
716 ' mif.padded_item_number "Item Number", '||
717 ' mp.organization_code "Org Code", '||
718 ' mif.inventory_item_flag "INVENTORY ITEM FLAG", '||
719 ' mif.purchasing_item_flag "PURCHASING ITEM FLAG", '||
720 ' mif.customer_order_flag "CUSTOMER ORDER FLAG", '||
721 ' mif.internal_order_flag "INTERNAL ORDER FLAG", '||
722 ' mif.invoiceable_item_flag "INVOICEABLE ITEM FLAG", '||
723 ' decode(mif.bom_item_type,1,''Model'',2,''Option Class'',3,''Planning'',4,''Standard'',5,''Product Family'') "BOM ITEM TYPE", '||
724 ' decode(stock_enabled_flag,inventory_item_flag,null,''N'',null,''Y'',stock_enabled_flag) "STOCK ENABLED FLAG", '||
725 ' decode(mtl_transactions_enabled_flag,stock_enabled_flag,null,''N'',null,''Y'',mtl_transactions_enabled_flag) "MTL TRANSACTIONS ENABLED FLAG", '||
726 ' decode(purchasing_enabled_flag,purchasing_item_flag,null,''N'',null,''Y'',purchasing_enabled_flag) "PURCHASING ENABLED FLAG", '||
727 ' decode(customer_order_enabled_flag,customer_order_flag,null,''N'',null,''Y'',customer_order_enabled_flag) "CUSTOMER ORDER ENABLED FLAG", '||
728 ' decode(internal_order_enabled_flag,internal_order_flag,null,''N'',null,''Y'',internal_order_enabled_flag) "INTERNAL ORDER ENABLED FLAG", '||
729 ' decode(invoice_enabled_flag,invoiceable_item_flag,null,''N'',null,''Y'',invoice_enabled_flag) "INVOICE ENABLED FLAG", '||
730 ' 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), '||
731 ' ''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),'||
732 ' ''Y'',build_in_wip_flag) "BUILD IN WIP FLAG", '||
733 ' mif.inventory_item_id "Inventory Item Id",mif.organization_id "Org Id" '||
734 ' from mtl_item_flexfields mif, mtl_parameters mp '||
735 ' where '||
736 ' mif.organization_id=mp.organization_id '||
737 ' and '||
741 ' or (mif.purchasing_item_flag=''N'' and mif.purchasing_enabled_flag=''Y'') '||
738 ' ( '||
739 ' ( mif.inventory_item_flag=''N'' and mif.stock_enabled_flag=''Y'') '||
740 ' or (mif.inventory_item_flag=''N'' and mif.mtl_transactions_enabled_flag=''Y'') '||
742 ' or (mif.customer_order_flag=''N'' and mif.customer_order_enabled_flag=''Y'') '||
743 ' or (mif.internal_order_flag=''N'' and mif.internal_order_enabled_flag=''Y'') '||
744 ' or (mif.invoiceable_item_flag=''N'' and mif.invoice_enabled_flag=''Y'') '||
745 ' or ( (mif.inventory_item_flag=''N'' or mif.bom_item_type <> 4) and mif.build_in_wip_flag=''Y'') '||
746 ' ) ';
747
748 If l_org_id is not null then
749 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
750 End if;
751 sqltxt := sqltxt || ' and rownum< '||row_limit;
752 sqltxt := sqltxt ||' order by mif.padded_item_number, mp.organization_code';
753
754 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items that fail the Interdependencies between Status Attributes.');
755
756 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
757 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
758 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
759 If (num_rows = row_limit -1 ) Then
760 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
761 <BR/> to prevent an excessively big output file <BR/>');
762 End If;
763 reportStr := '<BR/> Listed below are the interdependencies for the Item Status Attributes.
764 <BR/><BR/> 1. Stockable (stock_enabled_flag) Must be set to No
765 <BR/> If Inventory Item (inventory_item_flag) is set to No.
766 <BR/> 2. Transactable (mtl_transactions_enabled_flag) Must be set to No
767 <BR/> If Stockable (stock_enabled_flag) is set to No.
768 <BR/> 3. Purchasable (purchasing_enabled_flag) Must be set to No
769 <BR/> If Purchased (purchasing_item_flag) is set to No.
770 <BR/> 4. Build in WIP (build_in_wip_flag) Must be set to No
771 <BR/> If Inventory Item (inventory_item_flag) is set to No
772 <BR/> OR BOM Item Type (bom_item_type) is NOT set to Standard.
773 <BR/> 5. Customer Orders Enabled (customer_order_enabled_flag) Must be set to No
774 <BR/> If Customer Ordered Item (customer_order_flag) is set to No.
775 <BR/> 6. Internal Orders Enabled (internal_order_enabled_flag) Must be set to No
776 <BR/> If Internal Ordered Item (internal_order_flag) is set to No.
777 <BR/> 7. Invoice Enabled (invoice_enabled_flag) Must be set to No
778 <BR/> If Invoiceable Item (invoiceable_item_flag) is set to No.
779 <BR/><BR/> Note 1: In the output above, the Item Status Attributes
780 <BR/> successfully meeting the interdepencies are indicated with a null value.
781 <BR/> The ones showing ''Y'' indicate the failed status attributes.
782 <BR/> The current values (''Y'' or ''N'') of the Independent attributes are always shown.
783 <BR/> Note 2: For an item, if either Stockable or Transactable attributes are wrong
784 <BR/> then please verify and correct both.
785 <BR/><BR/><u>ACTION</u>:
786 <BR/> If records are fetched above,then please use Item Import in UPDATE mode
787 <BR/> to correct the respective Item Status Attribute value.
788 <BR/> For details on Item Import, please refer to
789 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
790 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
791 <BR/>';
792 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
793 End If; /* End of Impact and Action */
794
795 statusStr := 'SUCCESS';
796 isFatal := 'FALSE';
797
798 JTF_DIAGNOSTIC_COREAPI.BRPrint;
799 End If; /* End of l_script */
800 /* End of Script to identify items that fail the Interdependencies betweem Status Attributes. */
801
802
803 /* Script to identify Items violating validations between status code and status control attributes . */
804 If l_script in ('ALL','STATTR_MISMATCH') Then
805 Begin
806 delete from bom_diag_temp_tab; -- Clear the temporary tables
807 For l_item_st_rec in c_item_status_codes loop
808 l_where_clause :='';
809 l_where_clause :=l_where_clause ||' and mif.INVENTORY_ITEM_STATUS_CODE = '''||l_item_st_rec.status||''' and ( 1=2 ';
810 for l_st_attr_rec in c_item_status_attr_val(l_item_st_rec.status) loop
811 l_where_clause := l_where_clause||' or mif.'||l_st_attr_rec.attribute_name||' <> '''||l_st_attr_rec.attribute_value||'''';
812 end loop;
813 l_where_clause :=l_where_clause ||' ) '; /* append closing brace */
814
815 /* Insert the fetched records into temporary table bom_diag_temp_tab */
816 sql_stmt := '';
817 sql_stmt := ' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id, '||
818 ' char_col1,char_col2,char_col3,char_col4,char_col5,char_col6,char_col7,char_col8,char_col9) '||
819 ' Select mif.inventory_item_id , '||
820 ' mif.organization_Id, '||
821 ' MIF.INVENTORY_ITEM_STATUS_CODE, '||
822 ' MIF.BOM_ENABLED_FLAG, '||
823 ' MIF.BUILD_IN_WIP_FLAG, '||
824 ' MIF.CUSTOMER_ORDER_ENABLED_FLAG, '||
825 ' MIF.INTERNAL_ORDER_ENABLED_FLAG, '||
826 ' MIF.INVOICE_ENABLED_FLAG, '||
827 ' MIF.MTL_TRANSACTIONS_ENABLED_FLAG, '||
828 ' MIF.PURCHASING_ENABLED_FLAG, '||
829 ' MIF.STOCK_ENABLED_FLAG '||
830 ' From mtl_item_flexfields mif '||
831 ' Where 1=1 '||l_where_clause;
835 /*sql_stmt := sql_stmt || ' and rownum< 5'; -- temporarily limiting the records to 5 */
832 if l_org_id is not null then
833 sql_stmt :=sql_stmt||' and mif.organization_id = '||l_org_id;
834 end if;
836
837 ln_cursor := dbms_sql.open_cursor;
838 DBMS_SQL.PARSE(ln_cursor,sql_stmt,dbms_sql.native);
839 ln_rows_proc := DBMS_SQL.EXECUTE(ln_cursor);
840 DBMS_SQL.CLOSE_CURSOR(ln_cursor);
841
842 End Loop;
843
844 /* Display the records stored in temporary table bom_diag_temp_tab */
845 sqltxt := ' Select Inventory_Item_Id "Inventory Item Id", '||
846 ' Organization_id "Organization id", '||
847 ' Char_Col1 "Inventory Item Status Code", '||
848 ' Char_Col2 "BOM ENABLED FLAG", '||
849 ' Char_Col3 "BUILD IN WIP FLAG", '||
850 ' Char_Col4 "CUSTOMER ORDER ENABLED FLAG", '||
851 ' Char_Col5 "INTERNAL ORDER ENABLED FLAG", '||
852 ' Char_Col6 "INVOICE ENABLED FLAG", '||
853 ' Char_Col7 "MTL TRANSACTIONS ENABLED FLAG", '||
854 ' Char_Col8 "PURCHASING ENABLED FLAG", '||
855 ' Char_Col9 "STOCK ENABLED FLAG" '||
856 ' From bom_diag_temp_tab where 1=1 ';
857
858 sqltxt := sqltxt || ' and rownum< '||row_limit;
859 sqltxt :=sqltxt||' order by inventory_item_id, organization_id';
860
861 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items violating validations between status code and status control attributes ');
862
863 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
864 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
865 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
866 If (num_rows = row_limit -1 ) Then
867 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
868 <BR/> to prevent an excessively big output file <BR/>');
869 End If;
870 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/>
871 The values of the Status Control Attributes that use ''Sets Value'' status setting should
872 <BR/> always be in sync with their corresponding values of the Item Status Code chosen.
873 <BR/> Listed above are the items that violate this validation.
874 <BR/> The values of all their Status Control Attributes
875 <BR/> along with the Item Status Code are also listed.
876 <BR/> Please refer to the below table that lists the Status Control Attributes
877 <BR/> using ''Sets Value'' status setting. If no rows are fetched,
878 <BR/> it implies that none of the Status Control Attributes use ''Sets Value''.
879 <BR/>');
880
881 sqltxt := ' select attribute_name "Attribute Name" '||
882 ' from mtl_item_attributes '||
883 ' where status_control_code = 1 ';
884 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,'');
885
886 reportStr := ('<BR/><u>ACTION</u>:
887 <BR/>If records are fetched above listing items that violate this validation, then
888 <BR/>1) Please use Item Import in UPDATE mode to modify the Item Status Code
889 <BR/>which will in turn set the values of all Status Control Attributes.
890 <BR/>2) If required, the values of the Status Controlled Attributes that
891 <BR/>do NOT use ''Sets Value'' setting, can then be changed.
892 <BR/> For details on Item Import, please refer to
893 <BR/> ''Oracle Manufacturing APIs and Open Interfaces Manual'' (Part No. A95955-03),
894 <BR/> Chapter 7 - ''Oracle Inventory Open Interfaces and APIs'', Section ''Open Item Interface''.
895 <BR/>');
896
897 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
898 End If; /* End of Impact and Action */
899
900 statusStr := 'SUCCESS';
901 isFatal := 'FALSE';
902
903 End;
904
905 JTF_DIAGNOSTIC_COREAPI.BRPrint;
906 End If; /* End of l_script */
907 /* End of Script to identify Items violating validations between status code and status control attributes . */
908
909 /* Script to fetch items whose start_date_active, end_date_active columns are not null */
910 If l_script in ('ALL','ACTDATE_NN') Then
911 sqltxt := ' SELECT mif.padded_item_number "Item Number", '||
912 ' mp.organization_code "Org Code", '||
913 ' to_char(mif.start_date_active,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active", '||
914 ' to_char(mif.end_date_active,''DD-MON-YYYY HH24:MI:SS'') "End Date Active", '||
915 ' mif.inventory_item_id "Item Id", '||
916 ' mp.organization_id "Org Id" '||
917 ' FROM mtl_item_flexfields mif , mtl_parameters mp '||
918 ' WHERE 1=1 '||
919 ' AND mif.organization_id=mp.organization_id '||
920 ' AND ( mif.end_date_active IS NOT NULL OR mif.start_date_active IS NOT NULL ) ';
921
922 if l_org_id is not null then
923 sqltxt :=sqltxt||' and mif.organization_id = '||l_org_id;
924 end if;
925 sqltxt := sqltxt || ' and rownum< '||row_limit;
926 sqltxt := sqltxt || ' order by mif.PADDED_ITEM_NUMBER, mp.organization_code';
927
928 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Items whose start_date_active, end_date_active columns are populated ');
929
930 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
931 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/>');
932 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
933 If (num_rows = row_limit -1 ) Then
934 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
938 <BR/> are NOT used to determine the effectivity of an item.
935 <BR/> to prevent an excessively big output file <BR/>');
936 End If;
937 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> start_date_active and end_date_active date fields
939 <BR/> The effectivity of an item is determined by its Item Status Code.
940 <BR/><BR/><u>IMPACT:</u>
941 <BR/> None of the attributes of these items can be updated.
942 <BR/> FRM-40654 error may also occur while trying to
943 <BR/> update these items through the Items form.
944 <BR/>');
945
946 reportStr := '<BR/><u>ACTION</u>:
947 <BR/> These two date fields have to be nulled out.
948 <BR/> Nulling them out will not effect any standard functionality in Items form.
949 <BR/> Please use the below update statements to null out these two date fields.
950 <BR/> <BR/>Important: Please try these scripts on a TEST instance first.
951 <pre>
952 The below update statement can be used to correct a specific item.
953 Substitute the bind variables orgid and itemid
954 with the appropriate values.
955
956 update mtl_system_items_b
957 set start_date_active = null
958 ,end_date_active = null
959 where organization_id = :orgid
960 and inventory_item_id = :itemid;
961
962 The below two update statements can be used
963 to collectively correct all the problematic items.
964
965 update mtl_system_items_b
966 set start_date_active = null
967 where start_date_active is not null;
968
969 update mtl_system_items_b
970 set end_date_active = null
971 where end_date_active is not null; </pre>
972 <BR/>';
973 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
974 End If; /* End of Impact and Action */
975
976 statusStr := 'SUCCESS';
977 isFatal := 'FALSE';
978
979 JTF_DIAGNOSTIC_COREAPI.BRPrint;
980 End If; /* End of l_script */
981 /* End of Script to fetch items whose start_date_active, end_date_active columns are not null */
982
983 ----------------
984 <<l_test_end>>
985 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This Health Check Test completed as expected <BR/>');
986 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
987 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
988
989 End If; /* End of l_org_id is null or l_org_exists=1 */
990
991 EXCEPTION
992 when others then
993 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
994 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
995 statusStr := 'FAILURE';
996 errStr := sqlerrm ||' occurred in script. ';
997 fixInfo := 'Unexpected Exception in BOMDGITB.pls';
998 isFatal := 'FALSE';
999 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1000 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1001
1002 END runTest;
1003
1004 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
1005 BEGIN
1006 name := 'Items Data Health Check Details';
1007 END getComponentName;
1008
1009 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
1010 BEGIN
1011 descStr := 'This diagnostic test performs a variety of health checks against Items <BR/>
1012 and provides suggestions on how to solve possible issues.<BR/>
1013 It is recommended to run this health check periodically.';
1014 END getTestDesc;
1015
1016 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
1017 BEGIN
1018 name := 'Items Data Health Check';
1019 END getTestName;
1020
1021 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
1022 tempDependencies JTF_DIAG_DEPENDTBL;
1023
1024 BEGIN
1025 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
1026 END getDependencies;
1027
1028 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
1029 BEGIN
1030 str := 'FALSE';
1031 END isDependencyPipelined;
1032
1033
1034 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
1035 tempOutput JTF_DIAG_OUTPUTTBL;
1036 BEGIN
1037 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1038 outputValues := tempOutput;
1039 EXCEPTION
1040 when others then
1041 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1042 END getOutputValues;
1043
1044
1045 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
1046 tempInput JTF_DIAG_INPUTTBL;
1047 BEGIN
1048 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1049 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
1050 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Script','LOV-oracle.apps.bom.diag.lov.ItemHealthLov');
1051 defaultInputValues := tempInput;
1052 EXCEPTION
1053 when others then
1054 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1055 END getDefaultTestParams;
1056
1057 Function getTestMode return INTEGER IS
1058 BEGIN
1059 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
1060
1061 END getTestMode;
1062
1063 END BOM_DIAGUNITTEST_ITMHLCHK;