1 PACKAGE BODY PA_TIME_CLIENT_EXTN AS
2 /* $Header: PAPSSTCB.pls 120.2 2006/06/26 23:48:20 eyefimov noship $ */
3
4
5 PROCEDURE Set_Batch_Name (
6 p_expenditure_id IN NUMBER,
7 p_person_id IN NUMBER,
8 p_expenditure_ending_date IN DATE,
9 p_expenditure_organization_id IN NUMBER,
10 x_user_batch_name OUT NOCOPY VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_application_name OUT NOCOPY VARCHAR2,
13 x_message_data OUT NOCOPY VARCHAR2,
14 x_token_name1 OUT NOCOPY VARCHAR2,
15 x_token_val1 OUT NOCOPY VARCHAR2,
16 x_token_name2 OUT NOCOPY VARCHAR2,
17 x_token_val2 OUT NOCOPY VARCHAR2,
18 x_token_name3 OUT NOCOPY VARCHAR2,
19 x_token_val3 OUT NOCOPY VARCHAR2,
20 x_token_name4 OUT NOCOPY VARCHAR2,
21 x_token_val4 OUT NOCOPY VARCHAR2,
22 x_token_name5 OUT NOCOPY VARCHAR2,
23 x_token_val5 OUT NOCOPY VARCHAR2
24 )
25 IS
26
27 -- This API could be customized to derive a batch_name as per your
28 -- Business rules.
29 -- Oracle Projects provides the logic for deriving the default batch name
30 -- in this API. The default logic for deriving the batch name is
31 -- batch_name = (week_ending_date in MMDDYY format)-(random sequence that is
32 -- unique for week_ending_date and incurred_by_organization combination)
33 -- The default logic will work only for week_ending_dates that have less that
34 -- 1000 incurred_by_organizations
35 --
36 -- changed the query of c_user_batch_name to fix the bug 1572751
37 -- added AND expenditure_id = p_expenditure_id to where clause.
38 --
39
40 cursor c_user_batch_name is
41 SELECT user_batch_name
42 FROM PA_EXPENDITURES
43 WHERE incurred_by_organization_id =
44 p_expenditure_organization_id
45 AND trunc(Expenditure_ending_date) = p_expenditure_ending_date
46 AND expenditure_id = p_expenditure_id;
47
48 l_date_string VARCHAR2(6);
49 l_sequence_no NUMBER;
50
51 BEGIN
52
53 OPEN c_user_batch_name;
54 FETCH c_user_batch_name
55 INTO x_user_batch_name;
56
57 IF (c_user_batch_name%NOTFOUND) THEN
58
59 x_return_status := 'E';
60 x_msg_application_name := 'PA';
61 x_message_data := 'NO_EXPND_RECORD_FOUND';
62 x_token_name1 := 'TOKEN1';
63 x_token_val1 := to_char(p_expenditure_id);
64 x_token_name2 := 'TOKEN2';
65 x_token_val2 := to_char(p_expenditure_organization_id);
66 x_token_name3 := 'TOKEN3';
67 x_token_val3 := to_char(p_expenditure_ending_date);
68
69 ELSE
70
71 IF (x_user_batch_name IS NULL) THEN
72
73 /** Create the batch Name **/
74
75 SELECT To_Char(p_expenditure_ending_date,'MMDDYY')
76 INTO l_date_string
77 FROM dual;
78
79 /** Get the next value from sequence **/
80 SELECT mod(PA_EXPENDITURE_GROUPS_S.nextval,1000)
81 INTO l_sequence_no
82 FROM DUAL;
83
84 x_user_batch_name := l_date_string||'-'||to_char(l_sequence_no);
85
86 ELSE
87
88 x_return_status := null;
89
90 END IF;
91
92 END IF;
93
94
95 CLOSE c_user_batch_name;
96
97 EXCEPTION
98
99 WHEN OTHERS THEN
100 x_return_status := 'U';
101 x_message_data := to_char(SQLCODE);
102
103 END Set_Batch_Name;
104
105
106 --------------------------------------------------------------------------------
107
108 PROCEDURE Override_Match_Status(
109 p_person_id IN per_people_f.person_id%TYPE,
110 p_project_id IN pa_projects_all.project_id%TYPE,
111 p_task_id IN pa_tasks.task_id%TYPE,
112 p_expenditure_type IN pa_expenditure_types.expenditure_type%TYPE,
113 p_expenditure_type_class IN pa_system_linkages.function%TYPE,
114 p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
115 p_quantity IN pa_expenditure_items_all.quantity%TYPE,
116 p_expenditure_comment IN Varchar2,
117 p_attribute_category IN pa_expenditure_items_all.attribute_category%TYPE,
118 p_attribute1 IN pa_expenditure_items_all.attribute1%TYPE,
119 p_attribute2 IN pa_expenditure_items_all.attribute2%TYPE,
120 p_attribute3 IN pa_expenditure_items_all.attribute3%TYPE,
121 p_attribute4 IN pa_expenditure_items_all.attribute4%TYPE,
122 p_attribute5 IN pa_expenditure_items_all.attribute5%TYPE,
123 p_attribute6 IN pa_expenditure_items_all.attribute6%TYPE,
124 p_attribute7 IN pa_expenditure_items_all.attribute7%TYPE,
125 p_attribute8 IN pa_expenditure_items_all.attribute8%TYPE,
126 p_attribute9 IN pa_expenditure_items_all.attribute9%TYPE,
127 p_attribute10 IN pa_expenditure_items_all.attribute10%TYPE,
128 p_match_status IN Varchar2,
129 x_match_status OUT NOCOPY varchar2)
130 IS
131
132 l_stage Varchar2(200) := NULL;
133
134 --
135 -- If you want to match the txn to it's original then set
136 -- x_match_status to M. When the timecard is imported into
137 -- Oracle Projects via Transaction Import, this txn will be
138 -- matched to the original( i.e adjusted_expenditure_item_id will
139 -- be set to the original Expenditure Item id and
140 -- net_zero_adjustment_flag will be set to Y. )
141
142 -- If you do not want the txn to be matched the set the x_match_status
143 -- to 'U'
144
145 BEGIN
146
147 l_stage := 'Assigning p_match_status to x_match_status';
148
149
150 if p_quantity < 0 then
151 x_match_status := 'M';
152 else
153 x_match_status := p_match_status;
154 end if;
155
156
157 EXCEPTION WHEN others THEN
158 raise_application_error(-20003, 'OMS-'||l_stage||'-'||SQLERRM);
159 END Override_Match_Status;
160
161 -- ==========================================================================
162 -- Display_Business_Message API:
163 -- ==========================================================================
164 -- This API will be used to generate a customized message which will
165 -- be displayed when validation expendtiures and Workflow approval screens.
166 --
167 -- The message generated by this API could be used to ensure that the
168 -- employees are billable to a specified percentage determined as follows.
169 --
170 -- (billable hours in timecard )/(total hours on timecard) *100
171 --
172 -- The messages are customizable by customers, hence the customer could
173 -- generate message that would display total number of hours, % utilization
174 -- etc.
175 --
176 --
177 -- Parameter Description
178 -- =====================
179 -- P_Timecard_Table PL/SQL table holding data of OTL only
180 -- P_Module The module calling this routine
181 -- Can only be 'SST' or 'OTL'
182 -- P_Expenditure_id System generated identifier of the timecard
183 -- Will be NULL when P_Module = 'OTL'
184 -- P_Person_Id System generated identifire of the employee
185 -- from whome the timecard was created
186 -- P_Week_Ending_Date Week Ending date of the timecard
187 -- X_msg_Application_Name Message owning application short name.
188 -- X_Message_data Message Name
189 -- X_Msg_Token1_Name Optional parameter provided for use in
190 -- construction of the message. If this
191 -- parameter is being used, then the message
192 -- text should be constructed with token
193 -- name equal to the value in this parameter.
194 -- X_Msg_Token1_Value Optional parameter provided for use in
195 -- construction of message. If this parameter
196 -- is being used, then the token used in message
197 -- text should be populated with the value in this
198 -- parameter
199 -- X_Msg_Token2_Name Description, Same as X_Msg_Token1_Name.
200 --
201 -- X_Msg_Token2_Value Description, Same as X_Msg_Token1_Value.
202 --
203 -- X_Msg_Token3_Name Description, Same as X_Msg_Token1_Name.
204 --
205 -- X_Msg_Token3_Value Description, Same as X_Msg_Token1_Value.
206 --
207 --
208 -- ------------------------ For OTL ------------------------------
209 --
210 -- Oracle Time Capture (OTL) is a new product that captures and stores
211 -- timecard information in a system that is not directly tied to
212 -- Oracle Projects. Projects interfaces with OTC to validate the data.
213 -- After the timecard in OTL is approved, it can be imported
214 -- into Oracle Projects. Until it is imported into Projects, there are no
215 -- records of any kind within Projects for this data. Therefore, a
216 -- pl/sql table is provided that is in a Projects-compatible
217 -- structure so that summary-level validation can take place.
218 -- A new parameter, P_Timecard_Rec, is therefore being introduced for this purpose.
219 -- Below is the table record structure:
220 --
221 -- Project_Number Pa_Projects_All.Segment1%TYPE,
222 -- Project_Id Pa_Projects_All.Project_Id%TYPE,
223 -- Task_Number Pa_Tasks.Task_Number%TYPE,
224 -- Task_Id Pa_Tasks.Task_Id%TYPE,
225 -- Expenditure_Type Pa_Expenditure_Types.Expenditure_Type%TYPE,
226 -- System_Linkage_Function Pa_System_Linkages.Function%TYPE,
227 -- Quantity Pa_Expenditure_Items_All.quantity%TYPE,
228 -- Incurred_By_Person_Id Pa_Expenditures_All.Incurred_By_Person_Id%TYPE,
229 -- Override_Approver_Person_Id Pa_Expenditures_All.Overriding_Approver_Person_Id%TYPE,
230 -- Expenditure_Item_Date Pa_Expenditure_Items_All.Expenditure_Item_Date%TYPE,
231 -- Exp_Ending_Date Pa_Expenditures_All.Expenditure_Ending_Date%TYPE,
232 -- Attribute_Category Pa_Expenditure_Items_All.Attribute_Category%TYPE,
233 -- Attribute1 Pa_Expenditure_Items_All.Attribute1%TYPE,
234 -- Attribute2 Pa_Expenditure_Items_All.Attribute1%TYPE,
235 -- Attribute3 Pa_Expenditure_Items_All.Attribute1%TYPE,
236 -- Attribute4 Pa_Expenditure_Items_All.Attribute1%TYPE,
237 -- Attribute5 Pa_Expenditure_Items_All.Attribute1%TYPE,
238 -- Attribute6 Pa_Expenditure_Items_All.Attribute1%TYPE,
239 -- Attribute7 Pa_Expenditure_Items_All.Attribute1%TYPE,
240 -- Attribute8 Pa_Expenditure_Items_All.Attribute1%TYPE,
241 -- Attribute9 Pa_Expenditure_Items_All.Attribute1%TYPE,
242 -- Attribute10 Pa_Expenditure_Items_All.Attribute1%TYPE,
243 -- Billable_Flag Pa_Expenditure_Items_All.Billable_Flag%TYPE,
244 -- Expenditure_Item_Comment Pa_Expenditure_Comments.Expenditure_Comment%TYPE,
245 -- Orig_Exp_Txn_Reference1 Pa_Expenditures_All.Orig_Exp_Txn_Reference1%TYPE)
246 --
247 -- The following pl/sql table columns will always be NULL:
248 -- Orig_Exp_Txn_Reference1
249 --
250 -- The parameters P_Expenditure_Id and P_Week_Ending_Date will not be populated when called from OTL.
251 --
252 -- To search through the table pull out the data use a FOR LOOP
253 --
254
255 -- Coding calling programs:
256 -- ========================
257 -- Programs that call PA_TIME_CLIENT_EXTN.Display_Business_Message should
258 -- construct a translated message using the OUT parameters. The following
259 -- section will illustrate how to construct a message using the out parameters.
260 --
261 -- The calling procedure should has a out parameter X_return_message.
262 --
263 -- declare local variables
264 -- l_msg_application_name varchar2(50);
265 -- l_msg_token1_name varchar2(50);
266 -- l_msg_token1_value varchar2(50);
267 -- l_msg_token2_name varchar2(50);
268 -- l_msg_token2_value varchar2(50);
269 -- l_msg_token3_name varchar2(50);
270 -- l_msg_token3_value varchar2(50);
271 -- l_msg_data varchar2(30);
272 -- Begin
273 --
274 -- PA_TIME_CLIENT_EXTN.Display_Business_Message(
275 -- P_Timecard_Table => NULL,
276 -- P_Module => 'SST'
277 -- P_Expenditure_id => 1122,
278 -- P_Person_Id => 1234,
279 -- P_Week_Ending_Date => '01-JAN-97',
280 -- X_Msg_Application_Name => l_msg_application_name
281 -- X_Message_data => l_msg_data
282 -- X_Msg_Token1_Name => l_msg_token1_name
283 -- X_Msg_Token1_Value => l_msg_token1_value
284 -- X_Msg_Token2_Name => l_msg_token2_name
285 -- X_Msg_Token2_Value => l_msg_token2_value
286 -- X_Msg_Token3_Name => l_msg_token3_name
287 -- X_Msg_Token3_Value => l_msg_token3_value);
288 --
289 --
290 -- If( l_msg_data is NOT NULL ) THEN
291 --
292 -- fnd_message.set_name(l_msg_application_name,l_msg_data);
293 -- fnd_message.set_token(l_msg_token1_name,l_msg_token1_value);
294 -- fnd_message.set_token(l_msg_token2_name,l_msg_token2_value);
295 -- fnd_message.set_token(l_msg_token3_name,l_msg_token3_value);
296 -- fnd_message.set_token(l_msg_token2_name,l_msg_token2_value);
297 --
298 -- X_return_message := fnd_message.get;
299 -- where X_Return_Message is the out parameter of the calling program.
300 -- end if;
301 -- end;
302 --
303 PROCEDURE Display_Business_Message(
304 P_Timecard_Table IN Pa_Otc_Api.Timecard_Table,
305 P_Module IN VARCHAR2,
306 P_Expenditure_Id IN pa_expenditures_all.expenditure_id%TYPE DEFAULT NULL,
307 P_Person_id IN per_all_people_f.person_id%TYPE,
308 P_Week_Ending_Date IN Date,
309 X_Msg_Application_Name OUT NOCOPY Fnd_Application.Application_short_name%TYPE,
310 X_Message_data OUT NOCOPY Varchar2,
311 X_Msg_Token1_Name OUT NOCOPY Varchar2,
312 X_Msg_Token1_Value OUT NOCOPY Varchar2,
313 X_Msg_Token2_Name OUT NOCOPY Varchar2,
314 X_Msg_Token2_Value OUT NOCOPY Varchar2,
315 X_Msg_Token3_Name OUT NOCOPY Varchar2,
316 X_Msg_Token3_Value OUT NOCOPY Varchar2)
317
318 IS
319
320 -- CURSOR cur_billable IS
321 -- SELECT sum(nvl(quantity_1,0)) qty_1,
322 -- sum(nvl(quantity_2,0)) qty_2,
323 -- sum(nvl(quantity_3,0)) qty_3,
324 -- sum(nvl(quantity_4,0)) qty_4,
325 -- sum(nvl(quantity_5,0)) qty_5,
326 -- sum(nvl(quantity_6,0)) qty_6,
327 -- sum(nvl(quantity_7,0)) qty_7,
328 -- sum(decode(ei.billable_flag_1,'Y',ei.quantity_1,0)) bill_qty_1,
329 -- sum(decode(ei.billable_flag_2,'Y',ei.quantity_2,0)) bill_qty_2,
330 -- sum(decode(ei.billable_flag_3,'Y',ei.quantity_3,0)) bill_qty_3,
331 -- sum(decode(ei.billable_flag_4,'Y',ei.quantity_4,0)) bill_qty_4,
332 -- sum(decode(ei.billable_flag_5,'Y',ei.quantity_5,0)) bill_qty_5,
333 -- sum(decode(ei.billable_flag_6,'Y',ei.quantity_6,0)) bill_qty_6,
334 -- sum(decode(ei.billable_flag_7,'Y',ei.quantity_7,0)) bill_qty_7
335 -- from pa_expenditures exp,
336 -- pa_ei_denorm ei
337 -- where ei.expenditure_id = exp.expenditure_id
338 -- and exp.expenditure_id = p_expenditure_id
339 -- and exp.incurred_by_person_id = p_person_id
340 -- and exp.expenditure_ending_date = p_week_ending_date;
341 -- l_total_qty Number;
342 -- l_total_bill_qty Number;
343
344 BEGIN
345
346 /*
347 This client extension contains no default code, but can be used by customers
348 used to generate a customized message which will be displayed during validation and
349 Workflow approval screens.
350
351 The message generated by this API could be used to ensure that the
352 employees are billable to a specified percentage determined as follows.
353
354 (billable hours in timecard )/(total hours on timecard) *100
355
356 -- Initilize Total Variables
357
358 l_total_qty := 0;
359 l_total_bill_qty := 0;
360
361 FOR bill_rec IN cur_billable
362 LOOP
363
364 l_total_qty := l_total_qty + bill_rec.qty_1 + bill_rec.qty_2 +
365 bill_rec.qty_3 + bill_rec.qty_4 + bill_rec.qty_5 +
366 bill_rec.qty_6 + bill_rec.qty_7;
367
368 l_total_bill_qty := l_total_bill_qty + bill_rec.bill_qty_1 +
369 bill_rec.bill_qty_2 + bill_rec.bill_qty_3 +
370 bill_rec.bill_qty_4 + bill_rec.bill_qty_5 +
371 bill_rec.bill_qty_6 + bill_rec.bill_qty_7;
372
373 END LOOP;
374
375 -- Now calculate the % billable by using the formula
376 -- ( Total Billable hours/ Total hours ) *100
377
378 IF ( l_total_qty = 0 ) THEN
379
380 -- Bug 997075, division by zero. If the total qty is zero then
381 -- set it to 1 to avoid division by zero error.
382
383 l_total_qty := 1;
384
385 END IF;
386
387 X_msg_Token1_value :=
388 to_char( trunc((l_total_bill_qty/l_total_qty ) * 100,2));
389
390 -- Set the token name. this token name should be used in the message text
391 -- when the messages is created in Oracle Applications
392 --
393 X_msg_token1_name := 'BILLABLE';
394 X_Message_data := 'PA_BILLABLE_PERCENT';
395 X_Msg_Application_name := 'PA';
396
397 -- if customers want to customize the message
398 -- then they have to create a new message in a custom application and
399 -- then modify the client extension to pass back the customized message
400 -- name and custom application short name.
401
402 -- Example: steps involved in creating customized messages
403 --
404 -- 1. create a message using the create messages screen in application
405 -- developer responsibility
406 --
407 -- 2. Let's say a custom message( name = TK_UTILIZATION ) was created
408 -- in costom application ( name = TK ). The text of the message
409 -- is as follows.
410 -- " Utilization percentage = <ampersand>UTIL "
411 --
412 -- 3. Customize the client extension to calculate the utilization
413 -- percentage using your business rules .
414 --
415 -- 4. After calculating the utilization % set the message name and tokens
416 -- that will used to construct a meaningful message. Use the following
417 -- api's to set your messages
418 --
419 -- X_msg_application_name := 'TK';
420 -- X_message_data := 'TK_UTILIZATION';
421 -- X_msg_token1_name := 'UTIL';
422 -- X_msg_token1_value := <utilization derived from your business rules>
423 -- the value should be converted to a character using to_char function.
424 --
425 -- where TK is the custom application short name
426 -- and TK_UTILIZATION is the custom message name
427 -- make sure the custom messages are created in custom application.
428 -- Custom messages created in Oracle Projects Application will not
429 -- be supported, You might loose the contents during upgrade.
430 --
431
432 The messages are customizable by customers, hence the customer could
433 generate message that would display total number of hours, % utilization
434 etc.
435 */
436
437 NULL;
438
439 END Display_Business_Message;
440
441 END PA_TIME_CLIENT_EXTN;
[Home] [Help]