1 package body BOM_DIAGUNITTEST_ECOHLCHK as
2 /* $Header: BOMDGECB.pls 120.1 2007/12/26 09:47:19 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
30 BEGIN
31 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
32 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
33 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
34
35 /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
36 row_limit :=1000;
37 l_org_exists :=0; /* Initialize to zero */
38
39 -- accept input
40 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
41
42 /* l_org_id is NOT a mandatory input. If it is not entered, then run the scripts for all orgs.
43 However if a value is entered for org_id, then validate it for existence. */
44
45 If l_org_id is not null Then /* validate if input org_id exists*/
46 Begin
47 select 1 into l_org_exists
48 from mtl_parameters
49 where organization_id=l_org_id;
50 Exception
51 When others Then
52 l_org_exists :=0;
53 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Organization Id');
54 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please either provide a valid value for the Organization Id or leave it blank. ');
55 statusStr := 'FAILURE';
56 isFatal := 'TRUE';
57 fixInfo := ' Please review the error message below and take corrective action.';
58 errStr := ' Invalid value for input field Organization Id ';
59
60 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
61 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
62 End;
63 End If; /* End of l_org_id is not null */
64
65 If (l_org_id is null) or (l_org_exists = 1) Then
66
67 /* Script 1 - Fetch ECOs whose header does not show implemented status though
68 all its revised items are implemented. */
69
70 sqltxt :=' select eec.change_notice "ECO Number", '||
71 ' mp.organization_code "Organization Code", '||
72 ' ecsvl.status_name "Current Status Name", '||
73 ' eec.status_type "Current Status Type", '||
74 ' eec.change_id "Change Id", '||
75 ' eec.organization_id "Organization Id" '||
76 ' from eng_engineering_changes eec, mtl_parameters mp '||
77 ' ,eng_change_statuses_vl ecsvl '||
78 ' where eec.organization_id = mp.organization_id '||
79 ' and eec.APPROVAL_STATUS_TYPE <> 4 '||
80 ' and eec.status_type not in (5,6) '||
81 ' and eec.status_type = ecsvl.status_code(+) '||
82 ' and not exists (select 1 from eng_revised_items eri '||
83 ' where eri.change_notice = eec.change_notice '||
84 ' and eri.organization_id = eec.organization_id '||
85 ' and eri.status_type not in (5,6)) '||
86 ' and exists (select 1 from eng_revised_items eri1 '||
87 ' where eri1.change_notice = eec.change_notice '||
88 ' and eri1.organization_id = eec.organization_id '||
89 ' and eri1.status_type = 6) ';
90
91 if l_org_id is not null then
92 sqltxt :=sqltxt||' and eec.organization_id = '||l_org_id;
93 end if;
94
95 sqltxt :=sqltxt||' and rownum< '||row_limit;
96 sqltxt :=sqltxt||' order by mp.organization_code, eec.change_notice';
97
98 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' ECOs with non ''Implemented'' Status though all their Revised Items are in ''Implemented'' Status');
99
100 If (num_rows = 0) Then /* Corrupt Data Not Found for this case*/
101 JTF_DIAGNOSTIC_COREAPI.Line_Out('No corrupt data found for this case. <BR/><BR/>');
102 ElsIf (num_rows > 0) Then /* Show Impact and Action only if rows are returned */
103 If (num_rows = row_limit -1 ) Then
104 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows
105 <BR/> to prevent an excessively big output file. <BR/>');
106 End If;
107 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><u>IMPACT:</u>
108 <BR/> The discrepency between the ECO Header Status and
109 <BR/> its Revised Items Statuses can mislead the users.
110 <BR/> Any reports based on the Eco Header status will report wrong data. <BR/><BR/> ');
111
112 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('<BR/> Please apply appropriate patches suggested in metalink note : ' , '393085.1', '' );
113
114 reportStr := '<BR/> If any ECOs are fetched above,
115 <BR/> then please follow the below steps to correct them.
116 <BR/> (1) Please apply the patch suggested in above metalink Note.
117 <BR/> Please read the patch description carefully before applying it.
118 <BR/> (2) Follow one of the below approaches for correcting the ECOs.
119 <BR/> Approach 1:
120 <BR/> (a) Open (N) Engineering > ECOs > ECOs form.
121 <BR/> (b) Query for the ECO to be corrected.
122 <BR/> (c) Use (M) Tools > Implement to implement this ECO.
123 <BR/> (d) Wait for the ''Engineering Change Order Implementation''
124 <BR/> concurrent program to complete successfully.
125 <BR/> (e) Requery the ECO.
126 <BR/> (f) ECO Header Status should now show ''Implemented''.
127 <BR/>
128 <BR/> Approach 2: Follow this approach if the number of ECOs
129 <BR/> to be corrected are huge.
130 <BR/> (a) Open (N) Engineering > ECOs > ECOs form.
131 <BR/> (b) Query for the ECO to be corrected.
132 <BR/> (c) Use (M) Tools > Schedule to schedule this ECO.
133 <BR/> (d) Repeat steps b and c for all the ECOs to be corrected.
134 <BR/> (e) Use (N) Engineering > Setup > ''Auto Implement'' feature
135 <BR/> to auto implement all the scheduled ECOs.
136 <BR/>';
137 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
138 End If; /* End of Impact and Action */
139
140 statusStr := 'SUCCESS';
141 isFatal := 'FALSE';
142 /* End of Script 1 */
143
144 <<l_test_end>>
145 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This Health Check test completed as expected <BR/>');
146 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
147 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
148
149 End If; /* End of l_org_id is null or l_org_exists=1 */
150
151 EXCEPTION
152 when others then
153 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
154 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
155 statusStr := 'FAILURE';
156 errStr := sqlerrm ||' occurred in script. ';
157 fixInfo := 'Unexpected Exception in BOMDGECB.pls';
158 isFatal := 'FALSE';
159 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
160 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
161
162 END runTest;
163
164 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
165 BEGIN
166 name := 'ECOs Data Health Check Details';
167 END getComponentName;
168
169 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
170 BEGIN
171 descStr := ' This diagnostic test performs a variety of health checks against ECOs <BR/>
172 and provides suggestions on how to solve possible issues.<BR/>
173 It is recommended to run this health check periodically. ';
174 END getTestDesc;
175
176 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
177 BEGIN
178 name := 'ECOs Data Health Check';
179 END getTestName;
180
181 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
182 tempDependencies JTF_DIAG_DEPENDTBL;
183
184 BEGIN
185 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
186 END getDependencies;
187
188 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
189 BEGIN
190 str := 'FALSE';
191 END isDependencyPipelined;
192
193
194 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
195 tempOutput JTF_DIAG_OUTPUTTBL;
196 BEGIN
197 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
198 outputValues := tempOutput;
199 EXCEPTION
200 when others then
201 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
202 END getOutputValues;
203
204
205 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
206 tempInput JTF_DIAG_INPUTTBL;
207 BEGIN
208 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
209 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
210 defaultInputValues := tempInput;
211 EXCEPTION
212 when others then
213 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
214 END getDefaultTestParams;
215
216 Function getTestMode return INTEGER IS
217 BEGIN
218 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
219
220 END getTestMode;
221
222 END BOM_DIAGUNITTEST_ECOHLCHK;
[Home] [Help]