1 PACKAGE BODY pa_self_service_msg_pub AS
2 /* $Header: PAXPSSMB.pls 120.2 2005/08/24 14:00:05 rmarcel noship $ */
6 -- be displayed in the final review screen and supervisor approval screens.
3
4 -- ==========================================================================
5 -- This API will be used to generate a customized message which will
7 --
8 -- The message generated by this API could be used to ensure that the
9 -- employees are billable to a specified percentage determined as follows.
10 --
11 -- (billable hours in timecard )/(total hours on timecard) *100
12 --
13 -- The messages are customizable by customers, hence the customer could
14 -- generate message that would display total number of hours, % utilization
15 -- etc.
16 --
17 --
18 -- Parameter Description
19 -- =====================
20 --
21 -- P_Expenditure_id System generated identifier of the timecard
22 -- P_Person_Id System generated identifire of the employee
23 -- from whome the timecard was created
24 -- P_Week_Ending_Date Week Ending date of the timecard
25 -- X_Application_Name Message owning application short name.
26 -- X_Message_Name Message Name
27 -- X_Msg_Token1_Name Optional parameter provided for use in
28 -- construction of the message. If this
29 -- parameter is being used, then the message
30 -- text should be constructed with token
31 -- name equal to the value in this parameter.
32 -- X_Msg_Token1_Value Optional parameter provided for use in
33 -- construction of message. If this parameter
34 -- is being used, then the token used in message
35 -- text should be populated with the value in this
36 -- parameter
37 -- X_Msg_Token2_Name Description, Same as X_Msg_Token1_Name.
38 --
39 -- X_Msg_Token2_Value Description, Same as X_Msg_Token1_Value.
40 --
41 -- X_Msg_Token3_Name Description, Same as X_Msg_Token1_Name.
42 --
43 -- X_Msg_Token3_Value Description, Same as X_Msg_Token1_Value.
44 --
45 --
46 -- Coding calling programs:
47 -- ========================
48 -- Programs that call pa_self_service_msg_pub.business_message should
49 -- construct a translated message using the OUT parameters. The following
50 -- section will illustrate how to construct a message using the out parameters.
51 --
52 -- The calling procedure should has a out parameter X_return_message.
53 --
54 -- declare local variables
55 -- l_msg_application_name varchar2(50);
56 -- l_msg_token1_name varchar2(50);
57 -- l_msg_token1_value varchar2(50);
58 -- l_msg_token2_name varchar2(50);
59 -- l_msg_token2_value varchar2(50);
60 -- l_msg_token3_name varchar2(50);
61 -- l_msg_token3_value varchar2(50);
62 -- l_msg_name varchar2(30);
63 -- Begin
64 --
65 -- pa_self_Service_msg_pub.business_message( P_Expenditure_id => 1122,
66 -- P_Person_Id => 1234,
67 -- P_Week_Ending_Date => '01-JAN-97',
68 -- X_Msg_Application_Name => l_msg_application_name
69 -- X_Message_Name => l_msg_name
70 -- X_Msg_Token1_Name => l_msg_token1_name
71 -- X_Msg_Token1_Value => l_msg_token1_value
72 -- X_Msg_Token2_Name => l_msg_token2_name
73 -- X_Msg_Token2_Value => l_msg_token2_value
74 -- X_Msg_Token3_Name => l_msg_token3_name
75 -- X_Msg_Token3_Value => l_msg_token3_value);
76 --
77 --
78 -- If( l_msg_name is NOT NULL ) THEN
79 --
80 -- fnd_message.set_name(l_msg_application_name,l_msg_name);
81 -- fnd_message.set_token(l_msg_token1_name,l_msg_token1_value);
82 -- fnd_message.set_token(l_msg_token2_name,l_msg_token2_value);
83 -- fnd_message.set_token(l_msg_token3_name,l_msg_token3_value);
84 -- fnd_message.set_token(l_msg_token2_name,l_msg_token2_value);
85 --
86 -- X_return_message := fnd_message.get;
87 -- where X_Return_Message is the out parameter of the calling program.
88 -- end if;
89 -- end;
90 --
91 PROCEDURE business_message(
92 P_Expenditure_Id IN pa_expenditures_all.expenditure_id%TYPE,
93 P_Person_id IN per_all_people_f.person_id%TYPE,
94 P_Week_Ending_Date IN Date,
95 X_Msg_Application_Name OUT NOCOPY Fnd_Application.Application_short_name%TYPE, --File.Sql.39 bug 4440895
96 X_Msg_Name OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
97 X_Msg_Token1_Name OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
98 X_Msg_Token1_Value OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
99 X_Msg_Token2_Name OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
100 X_Msg_Token2_Value OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
101 X_Msg_Token3_Name OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
102 X_Msg_Token3_Value OUT NOCOPY Varchar2) IS --File.Sql.39 bug 4440895
103
104 CURSOR cur_billable IS
105 SELECT sum(nvl(quantity_1,0)) qty_1,
106 sum(nvl(quantity_2,0)) qty_2,
107 sum(nvl(quantity_3,0)) qty_3,
108 sum(nvl(quantity_4,0)) qty_4,
109 sum(nvl(quantity_5,0)) qty_5,
110 sum(nvl(quantity_6,0)) qty_6,
111 sum(nvl(quantity_7,0)) qty_7,
112 sum(decode(ei.billable_flag_1,'Y',ei.quantity_1,0)) bill_qty_1,
113 sum(decode(ei.billable_flag_2,'Y',ei.quantity_2,0)) bill_qty_2,
114 sum(decode(ei.billable_flag_3,'Y',ei.quantity_3,0)) bill_qty_3,
115 sum(decode(ei.billable_flag_4,'Y',ei.quantity_4,0)) bill_qty_4,
116 sum(decode(ei.billable_flag_5,'Y',ei.quantity_5,0)) bill_qty_5,
117 sum(decode(ei.billable_flag_6,'Y',ei.quantity_6,0)) bill_qty_6,
118 sum(decode(ei.billable_flag_7,'Y',ei.quantity_7,0)) bill_qty_7
119 from pa_expenditures exp,
120 pa_ei_denorm ei
121 where ei.expenditure_id = exp.expenditure_id
122 and exp.expenditure_id = p_expenditure_id
123 and exp.incurred_by_person_id = p_person_id
124 and exp.expenditure_ending_date = p_week_ending_date;
125 l_total_qty Number;
126 l_total_bill_qty Number;
127
128 BEGIN
129
130 -- initilize total variables
131
132 l_total_qty := 0;
133 l_total_bill_qty := 0;
134
135 FOR bill_rec IN cur_billable
136 LOOP
137
138 l_total_qty := l_total_qty + bill_rec.qty_1 + bill_rec.qty_2 +
139 bill_rec.qty_3 + bill_rec.qty_4 + bill_rec.qty_5 +
140 bill_rec.qty_6 + bill_rec.qty_7;
141
142 l_total_bill_qty := l_total_bill_qty + bill_rec.bill_qty_1 +
143 bill_rec.bill_qty_2 + bill_rec.bill_qty_3 +
144 bill_rec.bill_qty_4 + bill_rec.bill_qty_5 +
145 bill_rec.bill_qty_6 + bill_rec.bill_qty_7;
146
147 END LOOP;
148
149 -- Now calculate the % billable by using the formula
150 -- ( Total Billable hours/ Total hours ) *100
151
152 IF ( l_total_qty = 0 ) THEN
153
154 -- Bug 997075, division by zero
155 l_total_qty := 1;
156
157 END IF;
158
159 X_msg_Token1_value := to_char( trunc((l_total_bill_qty/l_total_qty ) * 100,2));
160
161 -- Set the token name. this token name should be used in the message text
162 -- when the messages is created in Oracle Applications
163 --
164 X_msg_token1_name := 'BILLABLE';
165 X_Msg_name := 'PA_BILLABLE_PERCENT';
166 X_Msg_Application_name := 'PA';
167
168 -- if customers want to customize the message
169 -- then they have to create a new message in a custom application and
170 -- then modify the client extension to pass back the customized message
171 -- name and custom application short name.
172
173 -- Example: steps involved in creating customized messages
174 --
175 -- 1. create a message using the create messages screen in application
176 -- developer responsibility
177 --
178 -- 2. Let's say a custom message( name = TK_UTILIZATION ) was created
179 -- in costom application ( name = TK ). The text of the message
180 -- is as follows.
181 -- " Utilization percentage = <ampersand>UTIL "
182 --
183 -- 3. Customize the client extension to calculate the utilization
184 -- percentage using your business rules .
185 --
186 -- 4. After calculating the utilization % set the message name and tokens
187 -- that will used to construct a meaningful message. Use teh following
188 -- api's to set your messages
189 --
190 -- X_msg_application := 'TK';
191 -- X_msg_Name := 'TK_UTILIZATION';
192 -- X_msg_token1_name := 'UTIL';
193 -- X_msg_token1_value := <utilization derived from your business rules>
194 -- the value should be converted to a character using to_char function.
195 --
196 -- where TK is the custom application short name
197 -- and TK_UTILIZATION is the custom message name
198 -- make sure the custom messages are created in custom application.
199 -- Custom messages created in Oracle Projects Application will not
200 -- be supported, You might loose the contents during upgrade.
201 --
202
203 END business_message;
204
205 END pa_self_service_msg_pub;
[Home] [Help]