1 PACKAGE BODY CSM_SETUP_PROFILE AS
2 /* $Header: csmdprfb.pls 120.3 2006/02/16 07:52:03 trajasek noship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13 ------------------------------------------------------------
14 -- procedure to initialize test datastructures
15 -- executeds prior to test run (not currently being called)
16 ------------------------------------------------------------
17 PROCEDURE init IS
18 BEGIN
19 -- test writer could insert special setup code here
20 null;
21 END init;
22
23 ------------------------------------------------------------
24 -- procedure to cleanup any test datastructures that were setup in the init
25 -- procedure call executes after test run (not currently being called)
26 ------------------------------------------------------------
27 PROCEDURE cleanup IS
28 BEGIN
29 -- test writer could insert special cleanup code here
30 NULL;
31 END cleanup;
32
33 ------------------------------------------------------------
34 -- procedure to execute the PLSQL test
35 -- the inputs needed for the test are passed in and a report object and CLOB are -- returned.
36 ------------------------------------------------------------
37 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
38 report OUT NOCOPY JTF_DIAG_REPORT,
39 reportClob OUT NOCOPY CLOB) IS
40 reportStr LONG;
41 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
42 errStr VARCHAR2(4000);
43 fixInfo VARCHAR2(4000);
44 errStr1 VARCHAR2(4000);
45 fixInfo1 VARCHAR2(4000);
46 itemCatErrStr VARCHAR2(4000);
47 itemCatFixInfo VARCHAR2(4000);
48 isFatal VARCHAR2(50); -- TRUE or FALSE
49 jtmProfileValue VARCHAR2(255) ;
50 csmProfileValue VARCHAR2(255) ;
51 itemCatProf VARCHAR2(255) ;
52 itemCatSetProf VARCHAR2(255) ;
53 itemCatProfName VARCHAR2(255) := 'CSM_ITEM_CATEGORY_FILTER' ;
54 itemCatProfUser VARCHAR2(255) := 'CSM: Item Category Filter' ;
55 itemCatSetProfName VARCHAR2(255) := 'CSM_ITEM_CATEGORY_SET_FILTER';
56 itemCatSetProfUser VARCHAR2(255) := 'CSM: Item Category Set Filter';
57 ProfileValue VARCHAR2(255) ;
58 respId NUMBER ;
59 dummy varchar2(1) ;
60 TYPE profileRec IS RECORD (profileName varchar2 (255), profileUserName varchar2(255)) ;
61 TYPE profileTab IS TABLE OF profileRec INDEX BY BINARY_INTEGER;
62 profileTable profileTab ;
63
64 cursor itemProfVal(itemCat in Number,
65 itemCatSet in Number) is
66 SELECT 'X'
67 FROM mtl_categories_kfv mck,
68 mtl_categories_vl mcv,
69 mtl_category_sets_vl mcs
70 WHERE mck.category_id = mcv.category_id
71 AND mcv.enabled_flag = 'Y'
72 AND NVL(mcv.disable_date, sysdate) >= SYSDATE
73 AND mcs.category_set_id = itemCatSet
74 AND mcv.category_id = itemCat
75 AND mcs.structure_id = mck.structure_id
76 AND mcs.validate_flag = 'N'
77 UNION
78 SELECT 'X'
79 FROM mtl_category_set_valid_cats valid_cat,
80 mtl_categories_kfv mck,
81 mtl_categories_vl mcv,
82 mtl_category_sets_vl mcs
83 WHERE valid_cat.category_set_id = mcs.category_set_id
84 AND mcs.category_set_id = itemCatSet
85 AND mck.category_id = itemCat
86 AND mcs.validate_flag = 'Y'
87 AND valid_cat.category_id = mck.category_id
88 AND mck.category_id = mcv.category_id
89 AND mcv.enabled_flag = 'Y'
90 AND NVL(mcv.disable_date, sysdate) >= SYSDATE ;
91 itemProfValNotFound exception ;
92
93 BEGIN
94 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars(reportClob);
95 --R12 Changes
96 -- profileTable(1).profileName := 'CSF_M_HISTORY' ;
97 -- profileTable(1).profileUserName := 'CSF: History_M' ;
98 -- profileTable(2).profileName := 'CSF_PALM_ITEM_ORGANIZATION' ;
99 -- profileTable(2).profileUserName := 'CSF: Palm Inventory Organization' ;
100 profileTable(1).profileName := 'JTM_APPL_CONFLICT_RULE' ;
101 profileTable(1).profileUserName := 'JTM: Application Conflict Rule' ;
102 profileTable(2).profileName := 'CSF_M_RECIPIENTS_BOUNDARY' ;
103 profileTable(2).profileUserName := 'CSM: Notifications Scope' ;
104 profileTable(3).profileName := 'CSF_M_AGENDA_ALLOWCHANGESCOMPLETEDTASK' ;
105 profileTable(3).profileUserName := 'CSM: Allow Changes to Completed Tasks' ;
106 --R12 Changes
107 -- profileTable(6).profileName := 'JTM_CREDIT_CARD_ENABLED' ;
108 -- profileTable(6).profileUserName := 'JTM : Enable credit card functionality' ;
109 -- profileTable(7).profileName := 'CSF_DEFAULT_TASK_NEW_STATUS' ;
110 -- profileTable(7).profileUserName := 'CSF: Default New task status' ;
111 profileTable(4).profileName := 'INC_DEFAULT_INCIDENT_SEVERITY' ;
112 profileTable(4).profileUserName := 'Service: Default Service Request Severity' ;
113 profileTable(5).profileName := 'INC_DEFAULT_INCIDENT_URGENCY' ;
114 profileTable(5).profileUserName := 'Service: Default Service Request Urgency' ;
115 profileTable(6).profileName := 'JTF_TIME_UOM_CLASS' ;
116 profileTable(6).profileUserName := 'Time unit of measure class' ;
117 profileTable(7).profileName := 'CSM_HISTORY_COUNT' ;
118 profileTable(7).profileUserName := 'CSM : Number of Previously Closed Service Requests' ;
119 profileTable(8).profileName := 'CSM_SYNCHRONOUS_HISTORY' ;
120 profileTable(8).profileUserName := 'CSM : Synchronous History Collection' ;
121 profileTable(9).profileName := 'ICX_PREFERRED_CURRENCY' ;
122 profileTable(9).profileUserName := 'ICX : Preferred Currency' ;
123 profileTable(10).profileName := 'JTF_TASK_DEFAULT_ASSIGNEE_STATUS' ;
124 profileTable(10).profileUserName := 'Task Manager: Default assignee status' ;
125 profileTable(11).profileName := 'SERVER_TIMEZONE_ID' ;
126 profileTable(11).profileUserName := 'Server Timezone' ;
127 profileTable(12).profileName := 'CLIENT_TIMEZONE_ID' ;
128 profileTable(12).profileUserName := 'Client Timezone' ;
129
130 jtmProfileValue := fnd_profile.VALUE_SPECIFIC(
131 'JTM_MOB_APPS_ENABLED', null, null, 874); -- JTM
132
133 BEGIN
134 select responsibility_id
135 into respId
136 from fnd_responsibility
137 where application_id = 883
138 and responsibility_key = 'OMFS_PALM'
139 and sysdate between start_date and nvl(end_date, sysdate) ;
140 exception
141 WHEN NO_DATA_FOUND THEN
142 null ;
143 END ;
144 csmProfileValue := fnd_profile.VALUE_SPECIFIC(
145 'JTM_MOB_APPS_ENABLED', null, respId, 883); --Field Service Palm resp
146
147 IF (jtmProfileValue = 'Y') and (csmProfileValue = 'Y') then
148 reportStr := 'Profile option values set Yes for profile JTM: Mobile Applications Enabled' ;
149 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
150 statusStr := 'SUCCESS';
151 ELSE
152 statusStr := 'FAILURE';
153 errStr := 'JTM application level and/or Oracle Mobile Field Service responsibility level JTM: Mobile Applications Enabled profile value is not set.';
154 fixInfo := 'Set up JTM application and Oracle Mobile Field Service responsibility level JTM: Mobile Applications Enabled profile value to Y.' ;
155 isFatal := 'FALSE';
156 END IF;
157 FOR i IN 1..profileTable.COUNT LOOP
158 profileValue := null ;
159 fnd_profile.get(profileTable(i).profileName,profileValue) ;
160 IF profileValue IS NOT NULL then
161 if reportStr is null then
162 reportStr := 'Profile option value set for profile ' || profileTable(i).profileUserName || ' as '|| profileValue ;
163 else
164 reportStr := ', ' || profileTable(i).profileUserName || ' as '|| profileValue;
165 end if ;
166 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
167 if statusStr is null or statusStr = 'SUCCESS' then
168 statusStr := 'SUCCESS';
169 end if ;
170 ELSE
171 statusStr := 'FAILURE';
172 if errStr1 is null then
173 errStr1 := errStr || 'Profile option value not set for ' || profileTable(i).profileUserName;
174 else
175 errStr1 := errStr1 || ', ' || profileTable(i).profileUserName ;
176 end if ;
177 if fixInfo1 is null then
178 fixInfo1 := fixInfo || 'Set up Profile option values for ' || profileTable(i).profileUserName;
179 else
180 fixInfo1 := fixInfo1 || ', ' || profileTable(i).profileUserName ;
181 end if ;
182 isFatal := 'FALSE';
183 END IF;
184 END LOOP ;
185 -- Check for Item Category and Item Category Set
186 fnd_profile.get(itemCatProfName,itemCatProf) ;
187 fnd_profile.get(itemCatSetProfName,itemCatSetProf) ;
188 IF (itemCatProf IS NULL AND itemCatSetProf IS NULL) then
189 reportStr := ' Profile option value not set for profile ' || itemCatProfUser || ' and '|| itemCatSetProfUser || '.' ;
190 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
191 if statusStr is null or statusStr = 'SUCCESS' then
192 statusStr := 'SUCCESS';
193 end if ;
194 ELSIF (itemCatProf IS NOT NULL AND itemCatSetProf IS NOT NULL) THEN
195 BEGIN
196 open itemProfVal(to_number(itemCatProf), to_number(itemCatSetProf)) ;
197 fetch itemProfVal into dummy ;
198 if itemProfVal%found then
199 reportStr := ' Profile option value set for profile ' || itemCatProfUser || ' and '|| itemCatSetProfUser || '.' ;
200 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportClob,reportStr);
201 if statusStr is null or statusStr = 'SUCCESS' then
202 statusStr := 'SUCCESS';
203 end if ;
204 else
205 raise itemProfValNotFound ;
206 end if;
207 close itemProfVal ;
208 EXCEPTION
209 WHEN others THEN
210 statusStr := 'FAILURE';
211 itemCatErrStr := ' Profile option values combination for profile ' || itemCatProfUser || ' and '|| itemCatSetProfUser || ' not valid.' ;
212 itemCatFixInfo := ' Set up valid values for combination of profile ' || itemCatProfUser || ' and '|| itemCatSetProfUser || '.';
213 isFatal := 'FALSE';
214 END ;
215 ELSE
216 statusStr := 'FAILURE';
217 itemCatErrStr := ' Profile option values should be set for profile ' || itemCatProfUser || ' and '|| itemCatSetProfUser || ' or both should not be set.' ;
218 itemCatFixInfo := ' Set up Profile option values for combination of profile ' || itemCatProfUser || ' and '|| itemCatSetProfUser || '.';
219 isFatal := 'FALSE';
220 END IF;
221
222
223 if (errStr1 is null and errStr is not null ) then
224 errStr1 := errStr ;
225 end if ;
226 if (fixInfo1 is null and fixInfo is not null ) then
227 fixInfo1 := fixInfo ;
228 end if ;
229 errStr1 := errStr1 || itemCatErrStr ;
230 fixInfo1 := fixInfo1 || itemCatFixInfo ;
231 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr1,fixInfo1,isFatal);
232 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob ;
233 END runTest;
234
235 ------------------------------------------------------------
236 -- procedure to report name back to framework
237 ------------------------------------------------------------
238 PROCEDURE getComponentName(str OUT NOCOPY VARCHAR2) IS
239 BEGIN
240 str := 'Profile setup';
241 END getComponentName;
242
243 ------------------------------------------------------------
244 -- procedure to report test description back to framework
245 ------------------------------------------------------------
246 PROCEDURE getTestDesc(str OUT NOCOPY VARCHAR2) IS
247 BEGIN
248 str := 'Profile values check';
249 END getTestDesc;
250
251 ------------------------------------------------------------
252 -- procedure to report test name back to framework
253 ------------------------------------------------------------
254 PROCEDURE getTestName(str OUT NOCOPY VARCHAR2) IS
255 BEGIN
256 str := 'Check Profiles';
257 END getTestName;
258 END;
[Home] [Help]