DBA Data[Home] [Help]

PACKAGE BODY: APPS.BOM_DIAGUNITTEST_ITMHLCHK

Source


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;