[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_RETN_RULES_PKG
Source
1 PACKAGE BODY PA_PROJ_RETN_RULES_PKG as
2 /* $Header: PAPJRETB.pls 120.2 2005/08/19 16:41:17 mwasowic noship $ */
3
4
5 -----------------------------------------------------------------
6 -- Insert the retention record
7 -----------------------------------------------------------------
8
9 PROCEDURE Insert_Row(
10 X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11 P_Project_ID NUMBER,
12 P_Task_Number VARCHAR2,
13 P_Task_Name VARCHAR2,
14 P_Customer_ID NUMBER,
15 P_Retention_Level_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 P_Expenditure_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
17 P_Expenditure_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
18 P_Non_Labor_Resource IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19 P_Revenue_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
20 P_Event_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 P_Retention_Percentage NUMBER,
22 P_Retention_Amount NUMBER,
23 P_Threshold_Amount NUMBER,
24 P_Effective_Start_Date DATE,
25 P_Effective_End_Date DATE,
26 P_Task_Flag VARCHAR2,
27 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
29 ) IS
30
31 l_Revenue_Category_Code VARCHAR2(100);
32 l_Task_ID NUMBER;
33 l_Retention_Rule_ID NUMBER;
34 l_retention_percentage number;
35
36
37 l_row_id VARCHAR2(30) := X_Rowid;
38 l_retention_level_code VARCHAR2(30) := P_Retention_Level_Code;
39 l_expenditure_category VARCHAR2(30) := P_Expenditure_Category ;
40 l_expenditure_type VARCHAR2(30) := P_Expenditure_Type ;
41 l_non_labor_resource VARCHAR2(30) := P_Non_Labor_Resource ;
42 l_revenue_category VARCHAR2(30) := P_Revenue_Category ;
43 l_event_type VARCHAR2(30) := P_Event_Type ;
44
45 BEGIN
46
47 --- Performing Validations
48 /* As null cannot be passed in this parameter, 2004 is explicitly assigned in the RetentionEditCO .java
49 This is unset here */
50
51 if p_retention_percentage = 2004 then
52 l_retention_percentage := null;
53 else
54 l_retention_percentage := p_retention_percentage;
55 end if;
56
57 PA_Retention_Util.Validate_Retention_Data (
58 P_RowID => NULL,
59 P_Project_ID => P_Project_ID,
60 P_Task_Number => P_Task_Number,
61 P_Task_Name => P_Task_Name,
62 P_Customer_ID => P_Customer_ID,
63 P_Retention_Level_Code => P_Retention_Level_Code,
64 P_Expenditure_Category => P_Expenditure_Category,
65 P_Expenditure_Type => P_Expenditure_Type,
66 P_Non_Labor_Resource => P_Non_Labor_Resource,
67 X_Revenue_Category_Code => l_Revenue_Category_Code,
68 P_Revenue_Category => P_Revenue_Category,
69 P_Event_Type => P_Event_Type,
70 P_Retention_Percentage => l_Retention_Percentage,
71 P_Retention_Amount => P_Retention_Amount,
72 P_Threshold_Amount => P_Threshold_Amount,
73 P_Effective_Start_Date => P_Effective_Start_Date,
74 P_Effective_End_Date => P_Effective_End_Date,
75 P_Task_Flag => P_Task_Flag,
76 X_Task_ID => l_Task_ID,
77 X_Return_Status_Code => X_Return_Status_Code,
78 X_Error_Message_Code => X_Error_Message_Code
79 );
80
81 IF X_Return_Status_Code = FND_API.G_RET_STS_ERROR
82 THEN
83 RETURN;
84 ELSE
85 -- Fix for Bug 2671135
86 BEGIN
87 SELECT PA_PROJ_RETN_RULES_S.NextVal
88 INTO l_Retention_Rule_ID
89 FROM dual ;
90 END;
91 END IF;
92
93 INSERT INTO PA_PROJ_RETN_RULES (
94 Project_ID,
95 Task_ID,
96 Customer_ID,
97 Retention_Rule_ID,
98 Retention_Level_Code,
99 Effective_Start_Date,
100 Effective_End_Date,
101 Retention_Percentage,
102 Retention_Amount,
103 Threshold_Amount,
104 Expenditure_Category,
105 Expenditure_Type,
106 Non_Labor_Resource,
107 Revenue_Category_Code,
108 Event_Type,
109 Creation_Date,
110 Created_By,
111 Last_Update_Date,
112 Last_Updated_By
113 )
114 VALUES (
115 P_Project_ID,
116 l_Task_ID,
117 P_Customer_ID,
118 l_Retention_Rule_ID, -- PA_PROJ_RETN_RULES_S.NextVal, -- Fix for Bug 2671135
119 P_Retention_Level_Code,
120 P_Effective_Start_Date,
121 P_Effective_End_Date,
122 l_Retention_Percentage,
123 P_Retention_Amount,
124 P_Threshold_Amount,
125 P_Expenditure_Category,
126 P_Expenditure_Type,
127 P_Non_Labor_Resource,
128 l_Revenue_Category_Code,
129 P_Event_Type,
130 SYSDATE,
131 FND_GLOBAL.user_id,
132 SYSDATE,
133 FND_GLOBAL.user_id
134 );
135
136 BEGIN
137 UPDATE PA_Project_Customers
138 SET Retention_Level_Code = DECODE(l_Task_ID, NULL, 'PROJECT', 'TOP_TASK')
139 WHERE Project_ID = P_Project_ID
140 AND Customer_ID = P_Customer_ID;
141 END;
142
143 BEGIN
144 SELECT
145 RowIDtoChar(ROWID)
146 INTO
147 X_RowID
148 FROM
149 PA_PROJ_RETN_RULES
150 WHERE
151 Project_ID = P_Project_ID
152 AND Customer_ID = P_Customer_ID
153 AND Effective_Start_Date = P_Effective_Start_Date
154 AND Retention_Rule_ID = l_Retention_Rule_ID -- Fix for Bug 2671135
155 AND NVL(Task_ID, -1) = NVL(l_Task_ID, -1)
156 AND decode(Effective_End_Date, NULL, sysdate, Effective_End_Date ) =
157 decode(p_Effective_End_Date, NULL, sysdate, p_Effective_End_Date )
158 AND NVL(Retention_Percentage, -1) = NVL(l_Retention_Percentage, -1)
159 AND NVL(Retention_Amount, -1) = NVL(P_Retention_Amount, -1)
160 AND NVL(Threshold_Amount, -1) = NVL(P_Threshold_Amount, -1)
161 AND Retention_Level_Code = P_Retention_Level_Code
162 AND NVL(Expenditure_Category, 'z')= NVL(Expenditure_Category, 'z')
163 AND NVL(Expenditure_Type, 'z') = NVL(Expenditure_Type, 'z')
164 AND NVL(Non_Labor_Resource, 'z') = NVL(Non_Labor_Resource, 'z')
165 AND NVL(Revenue_Category_Code, 'z')= NVL(Revenue_Category_Code, 'z')
166 AND NVL(Event_Type, 'z') = NVL(Event_Type, 'z');
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 X_Return_Status_Code := FND_API.G_RET_STS_ERROR ;
170 X_Error_Message_Code := 'PA_DATA_ERROR';
171 END;
172
173 IF X_Return_Status_Code = FND_API.G_RET_STS_ERROR
174 THEN
175 RETURN;
176 ELSE
177 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
178 END IF;
179 EXCEPTION
180 WHEN OTHERS THEN
181 X_Rowid := l_row_id ; --NOCOPY
182 P_Retention_Level_Code := l_retention_level_code ; -- NOCOPY
183 P_Expenditure_Category := l_expenditure_category ; -- NOCOPY
184 P_Expenditure_Type := l_expenditure_type; -- NOCOPY
185 P_Non_Labor_Resource := l_non_labor_resource ; -- NOCOPY
186 P_Revenue_Category := l_revenue_category ; -- NOCOPY
187 P_Event_Type := l_event_type ; -- NOCOPY
188 END Insert_Row; -- Insert_Row;
189
190 -----------------------------------------------------------------
191 -- Update the retention record
192 -----------------------------------------------------------------
193
194 PROCEDURE Update_Row (
195 P_RowID VARCHAR2,
196 P_Project_ID NUMBER,
197 P_Customer_ID NUMBER,
198 P_Expenditure_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
199 P_Expenditure_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
200 P_Non_Labor_Resource IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
201 P_Revenue_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
202 P_Event_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
203 P_Retention_Percentage NUMBER,
204 P_Retention_Amount NUMBER,
205 P_Threshold_Amount NUMBER,
206 P_Effective_Start_Date DATE,
207 P_Effective_End_Date DATE,
208 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
209 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
210 ) IS
211
212 l_Retention_Level_Code VARCHAR2(30);
213 l_Revenue_Category_Code VARCHAR2(100);
214 l_Task_ID NUMBER;
215 l_Retained_Amount NUMBER;
216
217 l_retention_percentage number;
218
219 l_expenditure_category VARCHAR2(30) := P_Expenditure_Category ;
220 l_expenditure_type VARCHAR2(30) := P_Expenditure_Type ;
221 l_non_labor_resource VARCHAR2(30) := P_Non_Labor_Resource ;
222 l_revenue_category VARCHAR2(30) := P_Revenue_Category ;
223 l_event_type VARCHAR2(30) := P_Event_Type ;
224 BEGIN
225 BEGIN
226 SELECT
227 Retention_Level_Code,
228 Task_ID,
229 NVL(Total_Retained, 0)
230 INTO
231 l_Retention_Level_Code,
232 l_Task_ID,
233 l_Retained_Amount
234 FROM
235 PA_PROJ_RETN_RULES
236 WHERE
237 RowIDToChar(RowID) = P_RowID;
238 END;
239
240 IF P_Threshold_Amount < l_Retained_Amount
241 THEN
242 X_Return_Status_Code := FND_API.G_RET_STS_ERROR ;
243 X_Error_Message_Code := 'PA_RETN_THRSHLD_XD_RETAINED';
244 RETURN;
245 END IF;
246
247 /* As null cannot be passed in this parameter, 2004 is explicitly assigned in the RetentionEditCO .java
248 This is unset here */
249 if p_retention_percentage = 2004 then
250 l_retention_percentage := null;
251 else
252 l_retention_percentage := p_retention_percentage;
253 end if;
254 --- Performing Validations
255 PA_Retention_Util.Validate_Retention_Data (
256 P_RowID => P_RowID,
257 P_PROJECT_ID => P_Project_ID,
258 P_Task_Number => NULL,
259 P_Task_Name => NULL,
260 P_CUSTOMER_ID => P_Customer_ID,
261 P_Retention_Level_Code => l_Retention_Level_Code,
262 P_Expenditure_Category => P_Expenditure_Category,
263 P_Expenditure_Type => P_Expenditure_Type,
264 P_Non_Labor_Resource => P_Non_Labor_Resource,
265 X_Revenue_Category_Code => l_Revenue_Category_Code,
266 P_Revenue_Category => P_Revenue_Category,
267 P_Event_Type => P_Event_Type,
268 P_Retention_Percentage => l_Retention_Percentage,
269 P_Retention_Amount => P_Retention_Amount,
270 P_Threshold_Amount => P_Threshold_Amount,
271 P_EFFECTIVE_START_DATE => P_Effective_Start_Date,
272 P_EFFECTIVE_END_DATE => P_Effective_End_Date,
273 P_Task_Flag => 'N',
274 X_Task_ID => l_Task_ID,
275 X_RETURN_STATUS_CODE => X_RETURN_STATUS_CODE,
276 X_ERROR_MESSAGE_CODE => X_ERROR_MESSAGE_CODE
277 );
278
279 IF X_Return_Status_Code = FND_API.G_RET_STS_ERROR
280 THEN
281 RETURN;
282 END IF;
283
284 --- End of validations
285
286 UPDATE
287 PA_PROJ_RETN_RULES
288 SET
289 Retention_Level_Code = l_Retention_Level_Code,
290 Expenditure_Category = P_Expenditure_Category,
291 Expenditure_Type = P_Expenditure_Type,
292 Non_Labor_Resource = P_Non_Labor_Resource,
293 Revenue_Category_Code = l_Revenue_Category_Code,
294 Event_Type = P_Event_Type,
295 Retention_Percentage = l_Retention_Percentage,
296 Retention_Amount = P_Retention_Amount,
297 Threshold_Amount = P_Threshold_Amount,
298 Effective_Start_Date = P_Effective_Start_Date,
299 Effective_End_Date = P_Effective_End_Date,
300 Last_Update_Date = SYSDATE,
301 Last_Updated_By = FND_GLOBAL.user_id
302 WHERE
303 RowIDToChar(RowID) = P_RowID;
304
305 IF (SQL%NOTFOUND)
306 THEN
307 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
308 X_Error_Message_Code := 'PA_DATA_ERROR';
309 ELSE
310 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
311 X_Error_Message_Code := '';
312 END IF;
313 EXCEPTION
314 WHEN OTHERS THEN
315 P_Expenditure_Category := l_expenditure_category ; -- NOCOPY
316 P_Expenditure_Type := l_expenditure_type; -- NOCOPY
317 P_Non_Labor_Resource := l_non_labor_resource ; -- NOCOPY
318 P_Revenue_Category := l_revenue_category ; -- NOCOPY
319 P_Event_Type := l_event_type ; -- NOCOPY
320 END Update_Row; -- Update_Row;
321
322
323 -----------------------------------------------------------------
324 -- Delete the retention record
325 -----------------------------------------------------------------
326
327 PROCEDURE Delete_Row (
328 P_Rowid VARCHAR2,
329 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
330 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
331 ) IS
332
333 BEGIN
334
335 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
336
337 DELETE FROM PA_PROJ_RETN_RULES
338 WHERE ROWID = P_RowID;
339 EXCEPTION
340 WHEN OTHERS THEN
341 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
342 X_Error_Message_Code := 'PA_DATA_ERROR';
343
344 END Delete_Row; -- Delete Row
345
346 END PA_PROJ_RETN_RULES_PKG;