1 package body BOM_DIAGUNITTEST_RTGHLCHK as
2 /* $Header: BOMDGRTB.pls 120.1 2007/12/26 09:56:14 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 BEGIN
30 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
31 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
32 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
33
34 /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
35 row_limit :=1000;
36 l_org_exists :=0; /* Initialize to zero */
37
38 -- accept input
39 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
40
41 /* l_org_id is NOT a mandatory input. If it is not entered, then run the scripts for all orgs.
42 However if a value is entered for org_id, then validate it for existence. */
43
44 If l_org_id is not null Then /* validate if input org_id exists*/
45 Begin
46 select 1 into l_org_exists
47 from mtl_parameters
48 where organization_id=l_org_id;
49 Exception
50 When others Then
51 l_org_exists :=0;
52 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Organization Id');
53 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please either provide a valid value for the Organization Id or leave it blank.');
54 statusStr := 'FAILURE';
55 isFatal := 'TRUE';
56 fixInfo := ' Please review the error message below and take corrective action. ';
57 errStr := ' Invalid value for input field Organization Id ';
58
59 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
60 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
61 End;
62 End If; /* End of l_org_id is not null */
63
64 If (l_org_id is null) or (l_org_exists = 1) Then
65
66 /* script 1- Orphan Rtg Rev Records: Routing Revisions exist but routings do not exist. */
67 sqltxt := ' select mif.item_number "Item Number", mp.organization_code "Organization Code", '||
68 ' mrir.process_revision "Routing Revision", '||
69 ' to_char(mrir.effectivity_date,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date", '||
70 ' to_char(mrir.implementation_date,''DD-MON-YYYY HH24:MI:SS'') "Implementation Date", '||
71 ' mrir.change_notice "ECO Number", '||
72 ' mif.inventory_item_id "Item Id", mrir.organization_id "Organization Id", '||
73 ' mrir.revised_item_sequence_id "Revised Item Seq Id" '||
74 ' from mtl_item_flexfields mif, mtl_rtg_item_revisions mrir, mtl_parameters mp '||
75 ' where mif.inventory_item_id=mrir.inventory_item_id '||
76 ' and mif.organization_id =mrir.organization_id '||
77 ' and mp.organization_id =mif.organization_id '||
78 ' and mrir.process_revision is not null '||
79 ' and mrir.change_notice is null '||
80 ' and mrir.revised_item_sequence_id is null '||
81 ' and not exists '||
82 ' (select 1 from bom_operational_routings bor '||
83 ' where bor.assembly_item_id=mrir.inventory_item_id '||
84 ' and bor.organization_id =mrir.organization_id ) ';
85
86 if l_org_id is not null then
87 sqltxt :=sqltxt||' and mrir.organization_id = '||l_org_id;
88 end if;
89
90 sqltxt :=sqltxt||' and rownum< '||row_limit;
91 sqltxt :=sqltxt||' order by mp.organization_code, mif.item_number, mrir.process_revision ';
92
93 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Orphan Routing Revisions - Routing Revisions exist for non-existent Routings');
94
95 If (num_rows = 0) Then /* Corrupt Data Not Found for this case*/
96 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/><BR/>');
97 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
98 If (num_rows = row_limit -1 ) Then
99 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
100 <BR/> to prevent an excessively big output file. <BR/>');
101 End If;
102 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
103 <BR/> Users will not be able to create Routings for these items. ');
104
105 reportStr := '<BR/><BR/><u>ACTION</u>:
106 <BR/><I><u>Important:</u> Try this action plan on a TEST INSTANCE first.</I>
107 <BR/>Please follow below steps to delete these routing revision records.
108 <BR/>(1) Take a backup of entire mtl_rtg_item_revisions table.
109 <BR/>(2) Use the below script to delete these orphan records.
110 <PRE>
111 delete from mtl_rtg_item_revisions
112 where inventory_item_id in
113 (select mif.inventory_item_id
114 from mtl_item_flexfields mif,
115 mtl_rtg_item_revisions mrir
116 where mif.inventory_item_id=mrir.inventory_item_id
117 and mif.organization_id =mrir.organization_id
118 and mrir.process_revision is not null
119 and mrir.change_notice is null
120 and mrir.revised_item_sequence_id is null
121 and not exists
122 (select 1 from bom_operational_routings bor
123 where bor.assembly_item_id=mrir.inventory_item_id
124 and bor.organization_id =mrir.organization_id )
125 );
126 </PRE>
127 (3) Make sure that the total number of records deleted
128 <BR/>are same as the number of records fetched above.
129 <BR/>(4) Commit the transaction.
130 <BR/>';
131 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
132 End If; /* End of Impact and Action */
133
134 statusStr := 'SUCCESS';
135 isFatal := 'FALSE';
136
137 <<l_test_end>>
138 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This Health Check test completed as expected <BR/>');
139 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
140 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
141 End If; /* End of l_org_id is null or l_org_exists=1 */
142
143 EXCEPTION
144 when others then
145 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
146 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
147 statusStr := 'FAILURE';
148 errStr := sqlerrm ||' occurred in script. ';
149 fixInfo := 'Unexpected Exception in BOMDGRTB.pls';
150 isFatal := 'FALSE';
151 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
152 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
153 END runTest;
154
155 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
156 BEGIN
157 name := 'Routings Data Health Check Details';
158 END getComponentName;
159
160 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
161 BEGIN
162 descStr := ' This diagnostic test performs a variety of health checks against Routings <BR/>
163 and provides suggestions on how to solve possible issues.<BR/>
164 It is recommended to run this health check periodically. ';
165 END getTestDesc;
166
167 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
168 BEGIN
169 name := 'Routings Data Health Check';
170 END getTestName;
171
172 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
173 tempDependencies JTF_DIAG_DEPENDTBL;
174
175 BEGIN
176 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
177 END getDependencies;
178
179 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
180 BEGIN
181 str := 'FALSE';
182 END isDependencyPipelined;
183
184
185 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
186 tempOutput JTF_DIAG_OUTPUTTBL;
187 BEGIN
188 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
189 outputValues := tempOutput;
190 EXCEPTION
191 when others then
192 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
193 END getOutputValues;
194
195
196 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
197 tempInput JTF_DIAG_INPUTTBL;
198 BEGIN
199 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
200 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
201 defaultInputValues := tempInput;
202 EXCEPTION
203 when others then
204 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
205 END getDefaultTestParams;
206
207 Function getTestMode return INTEGER IS
208 BEGIN
209 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
210
211 END getTestMode;
212
213 END BOM_DIAGUNITTEST_RTGHLCHK;
[Home] [Help]