1 package body BOM_DIAGUNITTEST_CATHLCHK as
2 /* $Header: BOMDGICB.pls 120.0.12000000.1 2007/07/26 13:28:16 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 row_limit NUMBER;
26 l_item_id NUMBER;
27 l_org_id NUMBER;
28 l_org_exists NUMBER;
29 l_master_org_id NUMBER;
30 l_master_org_code VARCHAR2(3);
31 l_script VARCHAR2(20);
32
33 BEGIN
34 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
35 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
36 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
37
38 /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
39 row_limit :=1000;
40
41 -- accept input
42 l_script := nvl(upper(JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Script',inputs)),'ALL');
43
44 -- JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Script Selected: '||l_script);
45
46 /* l_script is NOT a mandatory input. If it is not entered, then run all the scripts.
47 However if a script name is entered, then validate it for existence. */
48
49 If l_script not in ('MULT_CAT','SEED_CAT','ALL') Then
50 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Script Name');
51 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please choose a Script Name from the Lov or leave the field blank.');
52 statusStr := 'FAILURE';
53 isFatal := 'TRUE';
54 fixInfo := ' Please review the error message below and take corrective action.';
55 errStr := ' Invalid value for input field Script ';
56
57 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
58 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
59 Return;
60 End If;
61 /* End of script name validation*/
62
63 /* Script 1 - Multiple item category assignments exists for category set which do not allow multiple assignments.
64 This script is run for all orgs.*/
65 /*List of Category Sets that do not allow Multiple Item Category Assignments
66 but actually have Multiple Item Category Assignments*/
67
68 If l_script in ('ALL','MULT_CAT') Then
69
70 sqltxt := ' Select * from ( '||
71 ' select mcsv.category_set_name "Category Set Name", '||
72 ' decode(mcsv.control_level,1,''Master'',2,''Org'') "Control Level", '||
73 ' mp.organization_code "Organization Code", '||
74 ' mif.padded_item_number "Item Number",count(*) "Count of categories assigned", '||
75 ' mic.category_set_id "Category Set Id", '||
76 ' mic.organization_id "Organization Id" ,mic.inventory_item_id "Item Id" '||
77 ' from mtl_item_categories mic, mtl_category_sets_v mcsv, '||
78 ' mtl_parameters mp, mtl_item_flexfields mif '||
79 ' where mif.inventory_item_id = mic.inventory_item_id '||
80 ' and mif.organization_id = mic.organization_id '||
81 ' and mic.organization_id = mp.organization_id '||
82 ' and mcsv.category_set_id = mic.category_set_id '||
83 ' and mcsv.mult_item_cat_assign_flag = ''N'' '||
84 ' group by mcsv.category_set_name,mcsv.control_level, mp.organization_code,mif.padded_item_number,'||
85 ' mic.category_set_id,mic.organization_id,mic.inventory_item_id '||
86 ' having count(*) > 1 '||
87 ' order by mcsv.category_set_name,mp.organization_code,mif.padded_item_number '||
88 ' ) where rownum < '||row_limit;
89
90 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Multiple Item Category Assignments exists for Category Sets which do not allow multiple assignments.');
91
92 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
93 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/><BR/>');
94 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
95 If (num_rows = row_limit -1 ) Then
96 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
97 <BR/> to prevent an excessively big output file. <BR/>');
98 End If;
99 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Please note that this script is run for all the organizations.<BR/>');
100 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
101 <BR/> This affects inventory transactions , POs, Quotations, iProcurement, Costing etc
102 <BR/> as all these modules assume a single category for an item
103 <BR/> for their respective functional area category set. ');
104
105 reportStr :=
106 '<BR/><BR/><u>ACTION</u>:
107 <BR/> If Rows are returned with multiple category assignments,
108 <BR/> please follow the below steps for correcting them.
109 <BR/> Case 1: For Org controlled Category Sets.
110 <BR/> 1) Open (N) Inventory > Organization Items form.
111 <BR/> 2) Query for an Item fetched above.
112 <BR/> 3) Go to (M) Tools > Categories.
113 <BR/> 4) Query for the corresponding Category Set containing multiple assignments.
114 <BR/> (i.e.) This item will be assigned to multiple categories
115 <BR/> under this category set which is not allowed.
116 <BR/> 5) Delete the unwanted item category assignments.
117 <BR/> 6) Save the changes to the Item.
118 <BR/>
119 <BR/> Case 2: For Master controlled Category Sets.
120 <BR/> 1) Open (N) Inventory > Master Items form.
121 <BR/> 2) Query for an Item fetched above.
122 <BR/> 3) Go to (M) Tools > Categories.
123 <BR/> 4) Query for the corresponding Category Set containing multiple assignments.
124 <BR/> (i.e.) This item will be assigned to multiple categories
125 <BR/> under this category set which is not allowed.
126 <BR/> 5) Delete the unwanted item category assignments.
127 <BR/> 6) Save the changes to the Item.
128 <BR/>';
129 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
130 End If; /* End of Impact and Action */
131
132 statusStr := 'SUCCESS';
133 isFatal := 'FALSE';
134
135 JTF_DIAGNOSTIC_COREAPI.BRPrint;
136 End If; /* End of l_script */
137 /* End of Script 1 */
138
139 /* Script 2 - Check whether seeded Category set id 5,11,12 are attached to structures other than
140 (SALES_CATEGORIES for 5 and CARTONIZATION for 11,12) */
141 If l_script in ('ALL','SEED_CAT') Then
142 sqltxt := ' select mcsv.category_set_name "CATEGORY SET NAME", '||
143 ' mcsv.description "CATEGORY SET DESCRIPTION", '||
144 ' fifsv.id_flex_structure_code "ID FLEX STRUCTURE CODE", '||
145 ' fifsv.id_flex_structure_name "ID FLEX STRUCTURE NAME", '||
146 ' fifsv.description "ID FLEX STRUCTURE DESCRIPTION", '||
147 ' mcsv.category_set_id "CATEGORY SET ID", '||
148 ' mcsv.structure_id "STRUCTURE ID", '||
149 ' mcsv.default_category_id "DEFAULT CATEGORY ID", '||
150 ' mcsv.validate_flag "VALIDATE FLAG", '||
151 ' mcsv.control_level "CONTROL LEVEL", '||
152 ' mcsv.mult_item_cat_assign_flag "MULT ITEM CAT ASSIGN FLAG" '||
153 ' from mtl_category_sets_vl mcsv, fnd_id_flex_structures_vl fifsv '||
154 ' where mcsv.structure_id=fifsv.id_flex_num '||
155 ' and fifsv.id_flex_code = ''MCAT'' '||
156 ' and ( ( mcsv.category_set_id =5 '||
157 ' and fifsv.id_flex_structure_code <> ''SALES_CATEGORIES'' '||
158 ' ) '||
159 ' or '||
160 ' ( mcsv.category_set_id in (11,12) '||
161 ' and fifsv.id_flex_structure_code <> ''CARTONIZATION'' '||
162 ' ) '||
163 ' ) ';
164
165 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Seeded Category Sets with invalid structure code ');
166
167 If (num_rows = 0) Then /* Corrupt Data Found for this case*/
168 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/><BR/>');
169 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
170 /*It is not required to check if num_rows = (row_limit-1) since the output of this query
171 can atmost be 3 records only. */
172 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/>Seeded Category Sets -
173 <BR/>''Sales and Marketing'' (category set id = 5) should be attached
174 <BR/> to Structure code ''SALES_CATEGORIES'' only.
175 <BR/> ''Contained Item'' (category set id = 11) and ''Container Item'' (category set id = 12)
176 <BR/> should be attached to Structure code ''CARTONIZATION'' only. ');
177
178 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/><u>IMPACT:</u>
179 <BR/> User defined category set overwrites the seeded category set values
180 <BR/> thus causing data corruption.
181 <BR/> Some upgrade scripts fail with unique constraint violation error.');
182
183 reportStr := '<BR/>
184 <BR/><u>ACTION</u>:
185 <BR/> <u>Important:</u> Try this action plan on a TEST instance first.
186 <BR/> If Rows are returned with corrupt Seeded Category Sets,
187 <BR/> please follow the below steps for correcting them.
188 <BR/> 1) Run the below queries. Substitute the bind variable :cat_set_id
189 <BR/> with the category_set_id of the corrupted Category Set.
190 <pre>
191 select category_set_id,count(*)
192 from mtl_item_categories
193 where category_set_id = :cat_set_id
194 group by category_set_id ;
195
196 select category_set_id,count(*)
197 from mtl_category_set_valid_cats
198 where category_set_id = :cat_set_id
199 group by category_set_id ;
200
201 select category_set_id,count(*)
202 from cst_cost_updates
203 where category_set_id = :cat_set_id
204 group by category_set_id ;
205
206 select category_set_id,count(*)
207 from cst_cost_type_history
208 where category_set_id = :cat_set_id
209 group by category_set_id ;
210
211 select category_set_id,count(*)
212 from mtl_default_category_sets
213 where category_set_id =:cat_set_id
214 group by category_set_id ;
215
216 select category_set_id,count(*)
217 from cst_ap_variance_batches
218 where category_set_id = :cat_set_id
219 group by category_set_id ;
220
221 select category_set_id,count(*)
222 from cst_item_overhead_defaults
223 where category_set_id = :cat_set_id
224 group by category_set_id ;
225
226 select category_set_id,count(*)
227 from cst_sc_rollup_history
228 where category_set_id = :cat_set_id
229 group by category_set_id ;
230 </pre>
231 2) If all the above queries return NO rows, proceed to Step 3.
232 <BR/>3) Take a backup of entire mtl_category_sets_b table.
233 <BR/>4) Run the below query to get the correct Structure Id
234 <BR/> to be set to these seeded Category Sets.
235 <pre>
236 select id_flex_structure_code "Structure Code",
237 id_flex_num "New Structure Id"
238 from fnd_id_flex_structures_vl
239 where id_flex_code = ''MCAT''
240 and id_flex_structure_code
241 in (''SALES_CATEGORIES'',''CARTONIZATION'');
242 </pre>
243 5) Use the below update statement to correct the seeded category sets.
244 <BR/> The bind variables
245 <BR/> cat_set_id stands for the category_set_id of the corrupted seeded category set.
246 <BR/> old_structure_id stands for the incorrect structure_id
247 <BR/> currently associated with this category set.
248 <BR/> new_structure_id stands for the New structure Id
249 <BR/> retrieved through the select statement in step 4.
250 <pre>
251 update mtl_category_sets_b
252 set structure_id = :new_structure_id
253 where category_set_id = :cat_set_id
254 and structure_id = :old_structure_id ;
255 </pre>
256 6) Commit the transaction.
257 <BR/>';
258 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
259 End If; /* End of Impact and Action */
260
261 statusStr := 'SUCCESS';
262 isFatal := 'FALSE';
263
264 JTF_DIAGNOSTIC_COREAPI.BRPrint;
265 End If; /* End of l_script */
266 /* End of script 2*/
267
268 <<l_test_end>>
269 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This Health Check test completed as expected <BR/>');
270 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
271 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
272
273 EXCEPTION
274 when others then
275 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
276 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
277 statusStr := 'FAILURE';
278 errStr := sqlerrm ||' occurred in script. ';
279 fixInfo := 'Unexpected Exception in BOMDGICB.pls';
280 isFatal := 'FALSE';
284
281 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
282 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
283 END runTest;
285 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
286 BEGIN
287 name := 'Item Categories Data Health Check Details';
288 END getComponentName;
289
290 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
291 BEGIN
292 descStr := ' This diagnostic test performs a variety of health checks against Item Categories<BR/>
293 and provides suggestions on how to solve possible issues.<BR/>
294 It is recommended to run this health check periodically. <BR/> ';
295 END getTestDesc;
296
297 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
298 BEGIN
299 name := 'Item Categories Data Health Check';
300 END getTestName;
301
302 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
303 tempDependencies JTF_DIAG_DEPENDTBL;
304
305 BEGIN
306 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
307 END getDependencies;
308
309 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
310 BEGIN
311 str := 'FALSE';
312 END isDependencyPipelined;
313
314
315 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
316 tempOutput JTF_DIAG_OUTPUTTBL;
317 BEGIN
318 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
319 outputValues := tempOutput;
320 EXCEPTION
321 when others then
322 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
323 END getOutputValues;
324
325
326 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
327 tempInput JTF_DIAG_INPUTTBL;
328 BEGIN
329 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
330 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Script','LOV-oracle.apps.bom.diag.lov.CatHealthLov');
331 defaultInputValues := tempInput;
332 EXCEPTION
333 when others then
334 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
335 END getDefaultTestParams;
336
337 Function getTestMode return INTEGER IS
338 BEGIN
339 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
340
341 END getTestMode;
342
343 END BOM_DIAGUNITTEST_CATHLCHK;
[Home] [Help]