[Home] [Help]
PACKAGE BODY: APPS.PA_BILL_RATES_PKG
Source
1 PACKAGE BODY PA_BILL_RATES_PKG as
2 /* $Header: PASUDBRB.pls 120.3 2005/08/19 17:03:17 mwasowic noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5 X_Bill_Rate_Organization_Id NUMBER,
6 X_Std_Bill_Rate_Schedule VARCHAR2,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Start_Date_Active DATE,
13 X_Person_Id NUMBER,
14 X_Job_Id NUMBER,
15 X_Expenditure_Type VARCHAR2,
16 X_Non_Labor_Resource VARCHAR2,
17 X_Rate NUMBER,
18 X_Bill_Rate_Unit VARCHAR2,
19 X_Markup_Percentage NUMBER,
20 X_End_Date_Active DATE,
21 X_Bill_Rate_Sch_Id NUMBER,
22 X_job_group_id NUMBER,
23 x_Rate_Currency_Code VARCHAR2,
24 X_Resource_Class_Code VARCHAR2,
25 X_Res_Class_Organization_Id NUMBER
26 ) IS
27 CURSOR C IS SELECT rowid FROM PA_BILL_RATES
28 WHERE
29
30 -- Bug 2078409
31 /* bill_rate_organization_id = X_Bill_Rate_Organization_Id
32 AND std_bill_rate_schedule = X_Std_Bill_Rate_Schedule
33 */
34 bill_rate_sch_id= X_Bill_Rate_Sch_id
35 AND start_date_active = X_Start_Date_Active
36 AND nvl(person_id, -1) = nvl(X_Person_Id, -1)
37 AND nvl(job_id, -1) = nvl(X_Job_Id, -1)
38 AND nvl(expenditure_type, '-1') =
39 nvl(X_Expenditure_Type, '-1')
40 AND nvl(non_labor_resource, '-1') =
41 nvl(X_Non_Labor_Resource, '-1');
42
43
44 l_org_id NUMBER;
45
46
47 l_rowid varchar2(30);
48
49
50 BEGIN
51
52 /* ATG NOCOPY change */
53
54 l_rowid := x_rowid;
55
56
57
58 /* Shared service changes: Get the Current org Id from context and insert org_id into
59 the pa_bill_rates table, The context is already set it in the forms */
60
61
62 l_org_id := MO_GLOBAL.get_current_org_id ;
63
64
65 INSERT INTO PA_BILL_RATES(
66 Bill_Rate_Sch_Id,
67 bill_rate_organization_id,
68 std_bill_rate_schedule,
69 last_update_date,
70 last_updated_by,
71 creation_date,
72 created_by,
73 last_update_login,
74 start_date_active,
75 person_id,
76 job_id,
77 expenditure_type,
78 non_labor_resource,
79 rate,
80 bill_rate_unit,
81 markup_percentage,
82 end_date_active,
83 job_group_id,
84 Rate_Currency_Code,
85 Resource_Class_Code,
86 Res_Class_Organization_Id,
87 Org_Id
88 ) VALUES (
89 X_Bill_Rate_Sch_Id,
90 X_Bill_Rate_Organization_Id,
91 X_Std_Bill_Rate_Schedule,
92 X_Last_Update_Date,
93 X_Last_Updated_By,
94 X_Creation_Date,
95 X_Created_By,
96 X_Last_Update_Login,
97 X_Start_Date_Active,
98 X_Person_Id,
99 X_Job_Id,
100 X_Expenditure_Type,
101 X_Non_Labor_Resource,
102 X_Rate,
103 X_Bill_Rate_Unit,
104 X_Markup_Percentage,
105 X_End_Date_Active,
106 X_job_group_id,
107 X_Rate_Currency_Code,
108 X_Resource_Class_Code,
109 X_Res_Class_Organization_Id,
110 l_org_id
111 );
112
113 OPEN C;
114 FETCH C INTO X_Rowid;
115 if (C%NOTFOUND) then
116 CLOSE C;
117
118 x_rowid := l_rowid;
119 Raise NO_DATA_FOUND;
120 end if;
121 CLOSE C;
122 END Insert_Row;
123
124
125 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
126 X_Bill_Rate_Organization_Id NUMBER,
127 X_Std_Bill_Rate_Schedule VARCHAR2,
128 X_Start_Date_Active DATE,
129 X_Person_Id NUMBER,
130 X_Job_Id NUMBER,
131 X_Expenditure_Type VARCHAR2,
132 X_Non_Labor_Resource VARCHAR2,
133 X_Rate NUMBER,
134 X_Bill_Rate_Unit VARCHAR2,
135 X_Markup_Percentage NUMBER,
136 X_End_Date_Active DATE,
137 -- Bug 2078409
138 X_Bill_Rate_Sch_id NUMBER,
139 X_Rate_Currency_Code VARCHAR2,
140 X_Resource_Class_Code VARCHAR2,
141 X_Res_Class_Organization_Id NUMBER
142 ) IS
143 CURSOR C IS
144 SELECT *
145 FROM PA_BILL_RATES
146 WHERE rowid = X_Rowid
147 FOR UPDATE of Bill_Rate_Organization_Id NOWAIT;
148 Recinfo C%ROWTYPE;
149
150
151 BEGIN
152 OPEN C;
153 FETCH C INTO Recinfo;
154 if (C%NOTFOUND) then
155 CLOSE C;
156 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
157 APP_EXCEPTION.Raise_Exception;
158 end if;
159 CLOSE C;
160 if (
161
162 -- Bug 2078409
163 /* (Recinfo.bill_rate_organization_id = X_Bill_Rate_Organization_Id)
164 AND (Recinfo.std_bill_rate_schedule = X_Std_Bill_Rate_Schedule) */
165
166 (Recinfo.bill_rate_sch_id= X_Bill_Rate_Sch_id)
167 AND (Recinfo.start_date_active = X_Start_Date_Active)
168 AND ( (Recinfo.person_id = X_Person_Id)
169 OR ( (Recinfo.person_id IS NULL)
170 AND (X_Person_Id IS NULL)))
171 AND ( (Recinfo.job_id = X_Job_Id)
172 OR ( (Recinfo.job_id IS NULL)
173 AND (X_Job_Id IS NULL)))
174 AND ( (Recinfo.expenditure_type = X_Expenditure_Type)
175 OR ( (Recinfo.expenditure_type IS NULL)
176 AND (X_Expenditure_Type IS NULL)))
177 AND ( (Recinfo.non_labor_resource = X_Non_Labor_Resource)
178 OR ( (Recinfo.non_labor_resource IS NULL)
179 AND (X_Non_Labor_Resource IS NULL)))
180 AND ( (Recinfo.rate = X_Rate)
181 OR ( (Recinfo.rate IS NULL)
182 AND (X_Rate IS NULL)))
183 AND ( (Recinfo.bill_rate_unit = X_Bill_Rate_Unit)
184 OR ( (Recinfo.bill_rate_unit IS NULL)
185 AND (X_Bill_Rate_Unit IS NULL)))
186 AND ( (Recinfo.markup_percentage = X_Markup_Percentage)
187 OR ( (Recinfo.markup_percentage IS NULL)
188 AND (X_Markup_Percentage IS NULL)))
189 AND ( (Recinfo.end_date_active
190 = X_End_Date_Active)
191 OR ( (Recinfo.end_date_active IS NULL)
192 AND (X_End_Date_Active IS NULL)))
193 AND ( (Recinfo.Rate_Currency_Code
194 = X_Rate_Currency_Code)
195 OR ( (Recinfo.Rate_Currency_Code IS NULL)
196 AND (X_Rate_Currency_Code IS NULL)))
197 AND ( (Recinfo.Resource_Class_Code
198 = X_Resource_Class_Code)
199 OR ( (Recinfo.Resource_Class_Code IS NULL)
200 AND (X_Resource_Class_Code IS NULL)))
201 AND ( (Recinfo.Res_Class_Organization_Id
202 = X_Res_Class_Organization_Id)
203 OR ( (Recinfo.Res_Class_Organization_Id IS NULL)
204 AND (X_Res_Class_Organization_Id IS NULL)))
205
206 ) then
207 return;
208 else
209 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
210 APP_EXCEPTION.Raise_Exception;
211 end if;
212 END Lock_Row;
213
214
215
216 PROCEDURE Update_Row(X_Rowid VARCHAR2,
217 X_Bill_Rate_Organization_Id NUMBER,
218 X_Std_Bill_Rate_Schedule VARCHAR2,
219 X_Last_Update_Date DATE,
220 X_Last_Updated_By NUMBER,
221 X_Last_Update_Login NUMBER,
222 X_Start_Date_Active DATE,
223 X_Person_Id NUMBER,
224 X_Job_Id NUMBER,
225 X_Expenditure_Type VARCHAR2,
226 X_Non_Labor_Resource VARCHAR2,
227 X_Rate NUMBER,
228 X_Bill_Rate_Unit VARCHAR2,
229 X_Markup_Percentage NUMBER,
230 X_End_Date_Active DATE,
231 X_job_group_id NUMBER,
232 X_Rate_Currency_Code VARCHAR2,
233 X_Resource_Class_Code VARCHAR2,
234 X_Res_Class_Organization_Id NUMBER
235
236 ) IS
237 BEGIN
238 UPDATE PA_BILL_RATES
239 SET bill_rate_organization_id = X_Bill_Rate_Organization_Id,
240 std_bill_rate_schedule = X_Std_Bill_Rate_Schedule,
241 last_update_date = X_Last_Update_Date,
242 last_updated_by = X_Last_Updated_By,
243 last_update_login = X_Last_Update_Login,
244 start_date_active = X_Start_Date_Active,
245 person_id = X_Person_Id,
246 job_id = X_Job_Id,
247 expenditure_type = X_Expenditure_Type,
248 non_labor_resource = X_Non_Labor_Resource,
249 rate = X_Rate,
250 bill_rate_unit = X_Bill_Rate_Unit,
251 markup_percentage = X_Markup_Percentage,
252 end_date_active = X_End_Date_Active,
253 job_group_id = X_job_group_id,
254 Rate_Currency_Code = X_Rate_Currency_Code,
255 Resource_Class_Code=X_Resource_Class_Code ,
256 Res_Class_Organization_Id = X_Res_Class_Organization_Id
257 WHERE rowid = X_Rowid;
258
259 if (SQL%NOTFOUND) then
260 Raise NO_DATA_FOUND;
261 end if;
262 END Update_Row;
263
264 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
265 BEGIN
266 DELETE FROM PA_BILL_RATES
267 WHERE rowid = X_Rowid;
268
269 if (SQL%NOTFOUND) then
270 Raise NO_DATA_FOUND;
271 end if;
272 END Delete_Row;
273
274
275 END PA_BILL_RATES_PKG;