1 PACKAGE BODY pa_client_extn_proj_status AS
2 /* $Header: PAXPCECB.pls 120.3 2006/02/20 22:34:52 sunkalya noship $ */
3
4 PROCEDURE Verify_Project_Status_Change
5 (x_calling_module IN VARCHAR2
6 ,X_project_id IN NUMBER
7 ,X_old_proj_status_code IN VARCHAR2
8 ,X_new_proj_status_code IN VARCHAR2
9 ,X_project_type IN VARCHAR2
10 ,X_project_start_date IN DATE
11 ,X_project_end_date IN DATE
12 ,X_public_sector_flag IN VARCHAR2
13 ,X_attribute_category IN VARCHAR2
14 ,X_attribute1 IN VARCHAR2
15 ,X_attribute2 IN VARCHAR2
16 ,X_attribute3 IN VARCHAR2
17 ,X_attribute4 IN VARCHAR2
18 ,X_attribute5 IN VARCHAR2
19 ,X_attribute6 IN VARCHAR2
20 ,X_attribute7 IN VARCHAR2
21 ,X_attribute8 IN VARCHAR2
22 ,X_attribute9 IN VARCHAR2
23 ,X_attribute10 IN VARCHAR2
24 ,x_pm_product_code IN VARCHAR2
25 ,x_err_code OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
26 ,x_warnings_only_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
27 )
28 --
29 IS
30 --
31
32 /*
33
34 You can use this procedure to build additional rules while moving from
35 one project status to another. For example,you could enforce a rule
36 that certain class categories and class codes have to be assigned to
37 a project before you can move to an 'APPROVED' status. Note that you
38 should check the project_system_status_code to determine whether you
39 are moving to an APPROVED status. Note that the projects form and the
40 Activity Management Gateway APIs would be making calls to this procedure
41 as part of the validations.You can enforce multiple rules and have all
42 the error messages appear in the front-end interface. Oracle Projects
43 enforces certain rules as part of validations while moving from one
44 status to another.You can have your messages appended to the ones
45 enforced by the product. The projects form displays all error messages
46 in the message window.In order to append the messages to the stack
47 you must follow certain API message standards
48 An example of how you would enforce the above given example and
49 append the messages is given below
50 Example :
51 Before the project can change to an APPROVED status
52
53 Rule -1.Ensure that a specific class category is always assigned to a
54 project
55
56 Rule -2 Ensure that a CONTRACT project type requires a customer with
57 a valid Shipping and Billing contact defined.
58
59 Steps :
60 a) Determine the error message you need to display. Check the
61 existing error messages and ascertain whether a message that
62 meets your requirements already exists. If not,use the
63 Messages window under Application Developer responsibility to
64 define your messages. Note that the Applications standards
65 expect you to prefix all Oracle Projects related messages with
66 'PA_'. Please refer to the relevant documentation on defining
67 and re-creating the client-side .msb file for PA.
68
69 b) Determine the project_system_status_code. The parameter
70 X_new_proj_status_code contains the status to which the
71 project is moving into . You will get the project_system_status
72 as follows:
73 IF X_new_proj_status_code IS NOT NULL THEN
74 select project_system_status_code
75 into l_system_status_code
76 from pa_project_statuses
77 where project_status_code = X_new_proj_status_code;
78 END IF;
79 IF (l_system_status_code = 'APPROVED', THEN
80 Note that you can specify tokens for the messages while
81 defining them so that a runtime value is displayed
82 to the user.
83 This would be useful , if you want to have a different
84 class category for each project type. Example, a
85 CONTRACT project (The project's project type has a
86 project type class of 'CONTRACT') may require one
87 class category while an INDIRECT project may require a
88 different one. You would achieve this by defining a
89 token for the message and substituting the value
90 of the token at runtime. You can define upto 5 tokens
91 for a message. You would first define your
92 message as follows
93
94 Message code :
95 PA_SPEC_CLASS_CATEGORY_REQD
96
97 Message text :
98 --You must assign the CLASS_CATEGORY class category to
99 --this project before it can be approved.
100
101 -- Please note to specify an '&' in front of the token
102 -- CLASS_CATEGORY while defining the message
103
104 --- do the validations to enforce the above-mentioned rules
105 --- Rule 1
106 Check whether a specific class category has been
107 assigned to the project, by doing a SELECT on the
108 pa_project_classifications table.
109 If the class category is not assigned to the project
110 you would then code the message handling as follows.
111
112 PA_UTILS.Add_Message
113 (p_app_short_name => 'PA',
114 p_msg_name => 'PA_SPEC_CLASS_CATEGORY_REQD',
115 p_token1 => 'CLASS_CATEGORY',
116 p_value1 => <your class category>);
117 If you have more than one token you can pass
118 values for p_token2 and p_value2 , etc
119 (upto p_token5).Make sure that the token name is
120 exactly the same as you defined for the message
121 (It is case sensitive).
122
123 --- Rule 2
124 Check whether a CONTRACT project has a customer defined
125 with a billing as well as a shipping contact.
126
127 You would first define your
128 message as follows :
129
130 Message code :
131 PA_SPEC_CUST_CONTACT_REQD
132
133 Message text :
134 --You must assign a customer with a CONTACT_TYPE contact to
135 --this project before it can be approved.
136
137 -- Please note to specify an '&' in front of the token
138 -- CONTACT_TYPE while defining the message
139
140 If the customer/contact is not assigned to the project
141 you would then code the message handling as follows.
142
143 PA_UTILS.Add_Message
144 (p_app_short_name => 'PA',
145 p_msg_name => 'PA_SPEC_CUST_CONTACT_REQD',
146 p_token1 => 'CONTACT_TYPE',
147 p_value1 => <your contact type>);
148 If you have more than one token you can pass
149 values for p_token2 and p_value2 , etc
150 (upto p_token5).Make sure that the token name is
151 exactly the same as you defined for the message
152 (It is case sensitive).
153
154 c) You can choose to classify the above violations as either warnings
155 or errors that would prevent a project from being approved.If
156 you decide to have these messages only displayed as warnings,but
157 would like to continue with approving the project,you can set
158 the x_warnings_only_flag to 'Y'. In this case user must set the
159 x_err_code = 0. If x_err_code > 0 then the warnings will show up
160 as error irrespective of the value of x_warnings_only_flag .
161 Note that, you will set this
162 flag to 'Y' only if you wish to classify all the above violations
163 as warnings. Even if one of them is a business rule that you wish
164 to impose, you should not be setting this flag. Also, note
165 that Oracle Projects enforces its own rules before calling
166 this procedure,and any violation of the product defined rules
167 would ensure that the project cannot move to the new status,
168 regardless of whether you classify your messages as warnings only
169 or not.
170
171 By following the above methods to add your error messages to the
172 message stack, you will ensure that all your messages are displayed
173 to the user in the messages window of the projects form.
174
175 */
176 l_api_name VARCHAR2(30) := 'verify_project_status_change'; -- Do not modify this
177 l_msg_count NUMBER;
178 l_msg_data VARCHAR2(2000);
179
180 BEGIN
181 null; -- to add some body to the procedure -mpuvathi
182 /* Make sure that you set the x_err_code to 0 in case of no errors
183 or > 100 in case of any errors. */
184 x_err_code := 0;
185 x_warnings_only_flag := 'N';
186
187 ----------------------------------------------------------------------------------------
188 -- Cost Accrual code
189 ----------------------------------------------------------------------------------------
190 -- Please uncomment the call to this procedure if you have cost accrual enabled
191 -- and checks for pre-requisites before a project is closed are to be performed
192 --
193 /* *****************************************************************************
194 PA_REV_CA.Verify_Project_Status_CA
195 (x_calling_module
196 ,X_project_id
197 ,X_old_proj_status_code
198 ,X_new_proj_status_code
199 ,X_project_type
200 ,X_project_start_date
201 ,X_project_end_date
202 ,X_public_sector_flag
203 ,X_attribute_category
204 ,X_attribute1
205 ,X_attribute2
206 ,X_attribute3
207 ,X_attribute4
208 ,X_attribute5
209 ,X_attribute6
210 ,X_attribute7
211 ,X_attribute8
212 ,X_attribute9
213 ,X_attribute10
214 ,x_pm_product_code
215 ,x_err_code
216 ,x_warnings_only_flag
217 )
218 ;
219 ******************
220 */
221
222 EXCEPTION
223
224 /* NOTE : Please ensure that you have the following code to hanlde
225 error messages in any of the other exceptions that you may code
226 The variable l_pkg_name is defined in the package specification
227 Also, do not change the error handling for the WHEN OTHERS exception
228 */
229
230 WHEN OTHERS THEN
231 x_err_code := SQLCODE;
232 FND_MSG_PUB.add_exc_msg
233 ( p_pkg_name => l_pkg_name
234 , p_procedure_name => l_api_name );
235 FND_MSG_PUB.Count_And_Get
236 (p_count => l_msg_count ,
237 p_data => l_msg_data );
238 RAISE;
239
240 END verify_project_status_change;
241 -- ==============================================
242
243 PROCEDURE Check_wf_enabled
244 (x_project_status_code IN VARCHAR2,
245 x_project_type IN VARCHAR2,
246 x_project_id IN NUMBER,
247 x_wf_enabled_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
248 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
249 x_status_type IN VARCHAR2 DEFAULT 'PROJECT'
250 )
251 --
252 IS
253 --
254 /*
255 You can use this procedure to add/modify the conditions to enable
256 workflow for project status changes. By default,Oracle Projects enables
257 and launches workflow based on the Project status and Project type setup.
258 You can choose to override these conditions with your own conditions
259
260 */
261 l_api_name VARCHAR2(30) := 'Check_wf_enabled'; -- Do not modify this
262 l_msg_count NUMBER;
263 l_msg_data VARCHAR2(2000);
264
265 --Commented the following cursor for Bug#5029322
266
267 /*
268 --MOAC Changes: Bug 4363092: removed nvl usage with org_id
269 CURSOR l_sel_proj_type_csr IS
270 select NVL(enable_project_wf_flag,'N')
271 FROM pa_project_types_all -- Bug#3807805 : Modified pa_project_types to pa_project_types_all
272 WHERE project_type = x_project_type
273 and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
274 */
275
276 --Modified the cursor l_sel_proj_type_csr as below for Bug#5029322
277
278 CURSOR l_sel_proj_type_csr
279 IS
280 SELECT
281 PPT.org_id,
282 NVL(PPT.enable_project_wf_flag,'N')
283 FROM
284 PA_PROJECT_TYPES_ALL PPT,
285 PA_PROJECTS_ALL PPA
286 WHERE
287 PPA.org_id = PPT.org_id AND
288 PPA.project_type = PPT.project_type AND
289 PPA.project_id = x_project_id AND
290 PPT.project_type = x_project_type;
291
292
293
294 CURSOR l_sel_proj_stus_csr IS
295 select NVL(enable_wf_flag,'N')
296 FROM pa_project_statuses
297 WHERE project_status_code = x_project_status_code;
298
299 l_wf_enabled_flag VARCHAR2(1) := 'N';
300 l_org_id NUMBER;
301
302 BEGIN
303 x_err_code := 0;
304
305 --Commented the code below For bug#5029322.Added the same code below after the cursor l_sel_proj_type_csr to make sure that
306 --the value of PA_PROJECT_REQUEST_PVT.G_ORG_ID is populated from the cursor. This is to make sure that no regression happens after
307 --Bug fix#5029322 as the value of PA_PROJECT_REQUEST_PVT.G_ORG_ID could be used at some other place also.
308
309 /*
310 if PA_PROJECT_REQUEST_PVT.G_ORG_ID is null and x_project_type is not null then -- Added the if block for Bug#3807805
311 select org_id into PA_PROJECT_REQUEST_PVT.G_ORG_ID from pa_project_types where project_type = x_project_type;
312 end if;
313
314 */ --End of commenting for Bug#5029322
315
316 OPEN l_sel_proj_type_csr;
317 FETCH l_sel_proj_type_csr INTO l_org_id,l_wf_enabled_flag;
318 IF l_sel_proj_type_csr%NOTFOUND THEN
319 l_wf_enabled_flag := 'N';
320 END IF;
321 CLOSE l_sel_proj_type_csr;
322
323 --Added the following IF condition for Bug#5029322
324
325 if PA_PROJECT_REQUEST_PVT.G_ORG_ID is null and x_project_type is not null then -- Added the if block for Bug#3807805
326 PA_PROJECT_REQUEST_PVT.G_ORG_ID := l_org_id;
327 end if;
328
329 -- End changes for Bug#5029322
330 -- If workflow has not been enabled for the project type then do not proceed
331 -- further
332
333 IF l_wf_enabled_flag = 'N' THEN
334 x_wf_enabled_flag := 'N';
335 RETURN;
336 END IF;
337
338 OPEN l_sel_proj_stus_csr;
339 FETCH l_sel_proj_stus_csr INTO l_wf_enabled_flag;
340 IF l_sel_proj_stus_csr%NOTFOUND THEN
341 l_wf_enabled_flag := 'N';
342 END IF;
343 CLOSE l_sel_proj_stus_csr;
344 x_wf_enabled_flag := NVL(l_wf_enabled_flag ,'N');
345
346
347 EXCEPTION
348
349 WHEN OTHERS THEN
350 FND_MSG_PUB.add_exc_msg
351 ( p_pkg_name => l_pkg_name
352 , p_procedure_name => l_api_name );
353 FND_MSG_PUB.Count_And_Get
354 (p_count => l_msg_count ,
355 p_data => l_msg_data );
356 x_err_code := SQLCODE;
357 RAISE;
358
359 END Check_wf_enabled;
360
361 END Pa_Client_Extn_Proj_Status;
[Home] [Help]