[Home] [Help]
PACKAGE BODY: APPS.PA_ALLOC_COPY_RULE_PKG
Source
1 PACKAGE BODY PA_ALLOC_COPY_RULE_PKG AS
2 /* $Header: PAXALCRB.pls 120.2 2005/08/09 11:19:15 dlanka noship $ */
3 procedure COPY_RULE(p_rule_id in number
4 ,p_to_rule_name in varchar2
5 ,p_to_description in varchar2
6 ,x_retcode out NOCOPY varchar2
7 ,x_errbuf out NOCOPY varchar2)
8 as
9 Cursor c_rule IS
10 select RULE_ID
11 ,RULE_NAME
12 ,ALLOCATION_METHOD
13 ,TARGET_EXP_TYPE_CLASS
14 ,TARGET_EXP_ORG_ID
15 ,TARGET_EXP_TYPE
16 ,TARGET_COST_TYPE
17 -- ,CREATION_DATE
18 -- ,CREATED_BY
19 -- ,LAST_UPDATE_DATE
20 -- ,LAST_UPDATED_BY
21 -- ,LAST_UPDATE_LOGIN
22 ,DESCRIPTION
23 ,POOL_PERCENT
24 ,PERIOD_TYPE
25 ,SOURCE_AMOUNT_TYPE
26 ,SOURCE_BALANCE_CATEGORY
27 ,SOURCE_BALANCE_TYPE
28 ,ALLOC_RESOURCE_LIST_ID
29 ,AUTO_RELEASE_FLAG
30 ,IMP_WITH_EXCEPTION
31 ,DUP_TARGETS_FLAG
32 ,OFFSET_EXP_TYPE_CLASS
33 ,OFFSET_EXP_ORG_ID
34 ,OFFSET_EXP_TYPE
35 ,OFFSET_COST_TYPE
36 ,OFFSET_METHOD
37 ,OFFSET_PROJECT_ID
38 ,OFFSET_TASK_ID
39 ,BASIS_METHOD
40 ,BASIS_RELATIVE_PERIOD
41 ,BASIS_AMOUNT_TYPE
42 ,BASIS_BALANCE_CATEGORY
43 ,BASIS_BUDGET_TYPE_CODE
44 ,BASIS_FIN_PLAN_TYPE_ID /* added bug2619977 */
45 ,BASIS_BALANCE_TYPE
46 ,BASIS_RESOURCE_LIST_ID
47 ,SOURCE_EXTN_FLAG
48 ,TARGET_EXTN_FLAG
49 ,FIXED_AMOUNT
50 ,START_DATE_ACTIVE
51 ,END_DATE_ACTIVE
52 ,ORG_ID
53 ,ATTRIBUTE_CATEGORY
54 ,ATTRIBUTE1
55 ,ATTRIBUTE2
56 ,ATTRIBUTE3
57 ,ATTRIBUTE4
58 ,ATTRIBUTE5
59 ,ATTRIBUTE6
60 ,ATTRIBUTE7
61 ,ATTRIBUTE8
62 ,ATTRIBUTE9
63 ,ATTRIBUTE10
64 ,ATTRIBUTE11
65 ,ATTRIBUTE12
66 ,ATTRIBUTE13
67 ,ATTRIBUTE14
68 ,ATTRIBUTE15
69 ,BASIS_BUDGET_ENTRY_METHOD_CODE
70 , LIMIT_TARGET_PROJECTS_CODE
71 /* FP.M : Allocation Impact : 3512552 */
72 , ALLOC_RESOURCE_STRUCT_TYPE
73 , BASIS_RESOURCE_STRUCT_TYPE
74 , ALLOC_RBS_VERSION
75 , BASIS_RBS_VERSION
76 from pa_alloc_rules_all
77 where rule_id = p_rule_id
78 for update of rule_name;
79
80 cursor Is_Rbs_Valid ( p_rbs_header_id In Number) Is
81 Select 'Y' From pa_rbs_headers_v
82 Where RBS_HEADER_ID = p_rbs_header_id
83 and Trunc(Sysdate) <= nvl(EFFECTIVE_TO_DATE,Sysdate);
84
85 Cursor Is_Fin_Plan_Valid (p_fin_plan_type_id in Number) Is
86 Select 'Y' From Pa_Fin_Plan_Types_Vl
87 Where FIN_PLAN_TYPE_ID = p_fin_plan_type_id
88 And Trunc(Sysdate) <= NVL(END_DATE_ACTIVE,Sysdate);
89
90 Cursor Is_Budget_Valid (p_budget_type_code in Varchar2) Is
91 Select 'Y' From Pa_Budget_Types
92 Where BUDGET_TYPE_CODE = p_budget_type_code
93 And Trunc(Sysdate) <= Nvl( END_DATE_ACTIVE, Sysdate);
94
95 --Cursor for Source
96 Cursor c_source is
97 select RULE_ID
98 , LINE_NUM
99 , EXCLUDE_FLAG
100 -- , CREATED_BY
101 -- , CREATION_DATE
102 -- , LAST_UPDATE_DATE
103 -- , LAST_UPDATED_BY
104 -- , LAST_UPDATE_LOGIN
105 , PROJECT_ORG_ID
106 , TASK_ORG_ID
107 , PROJECT_TYPE
108 , CLASS_CATEGORY
109 , CLASS_CODE
110 , SERVICE_TYPE
111 , PROJECT_ID
112 , TASK_ID
113 from pa_alloc_source_lines
114 where rule_id = p_rule_id
115 for update of line_num;
116 --Cursor for Gl source
117 Cursor C_gl_source is
118 Select RULE_ID
119 , LINE_NUM
120 , SOURCE_CCID
121 , SUBTRACT_FLAG
122 -- , CREATED_BY
123 -- , CREATION_DATE
124 -- , LAST_UPDATE_DATE
125 -- , LAST_UPDATED_BY
126 -- , LAST_UPDATE_LOGIN
127 , SOURCE_PERCENT
128 from pa_alloc_gl_lines
129 where rule_id = p_rule_id
130 for update of line_num;
131 --cursor for Targets
132 Cursor c_target is
133 Select RULE_ID
134 , LINE_NUM
135 , EXCLUDE_FLAG
136 -- , CREATED_BY
137 -- , CREATION_DATE
138 -- , LAST_UPDATE_DATE
139 -- , LAST_UPDATED_BY
140 -- , LAST_UPDATE_LOGIN
141 , PROJECT_ORG_ID
142 , TASK_ORG_ID
143 , PROJECT_TYPE
144 , CLASS_CATEGORY
145 , CLASS_CODE
146 , SERVICE_TYPE
147 , PROJECT_ID
148 , TASK_ID
149 , BILLABLE_ONLY_FLAG
150 , LINE_PERCENT
151 from pa_alloc_target_lines
152 where rule_id = p_rule_id
153 for update of line_num;
154 --Cursor for Resources
155 Cursor C_resources is
156 Select RULE_ID
157 , MEMBER_TYPE
158 , RESOURCE_LIST_MEMBER_ID
159 , EXCLUDE_FLAG
160 -- , CREATED_BY
161 -- , CREATION_DATE
162 -- , LAST_UPDATE_DATE
163 -- , LAST_UPDATED_BY
164 -- , LAST_UPDATE_LOGIN
165 , TARGET_EXPND_TYPE
166 , OFFSET_EXPND_TYPE
167 , RESOURCE_PERCENTAGE
168 from pa_alloc_resources
169 where rule_id = p_rule_id
170 for update of member_type;
171 --Cursor to get the Rule ID
172 CURSOR C1 is Select pa_alloc_rules_s.nextval from sys.dual;
173 --Initialise the Std Who columns
174 G_created_by number := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
175 G_last_updated_by number := G_created_by;
176 G_last_update_login number := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
177 G_creation_date Date := trunc(sysdate);
178 G_last_update_date Date := trunc(sysdate);
179 G_rule_id number;
180 G_rowid varchar2(60);
181 /* Bug# 3643304. ALLOC RULE SHOULD HONOR RBS EFFECTIVE DATES */
182 G_Alloc_Rbs_Valid_Flag Varchar2(1) := 'Y';
183 G_Alloc_Struct_Type Varchar2(3) ;
184 G_ALLOC_RESOURCE_LIST_ID Number;
185 G_Alloc_Rbs_Version_Id Number;
186 /* Bug# 3643304. ALLOC RULE SHOULD HONOR RBS EFFECTIVE DATES */
187 G_Basis_Rbs_Valid_Flag Varchar2(1) := 'Y';
188 G_Basis_Struct_Type Varchar2(3) ;
189 G_BASIS_RESOURCE_LIST_ID Number;
190 G_Basis_Rbs_Version_Id Number;
191
192 /* Bug# 3678479. ALLOC RULE SHOULD HONOR FIN PLAN AND BUDGET EFFECTIVE DATES */
193 G_BUDGET_FIN_PLAN_VALID_FLAG Varchar2(1) := 'Y';
194 G_Fin_Plan_Type_Id Number;
195 G_Budget_Type_Code VARCHAR (30) ;
196
197 BEGIN
198 --Get the latest rule ID
199 open C1;
200 fetch C1 into G_RULE_ID;
201 close C1;
202 -- Insert the Rule Information.
203 FOR rule_rec IN C_rule
204 LOOP
205 /* Bug# 3643304. ALLOC RULE SHOULD HONOR RBS EFFECTIVE DATES */
206 G_Alloc_Struct_Type := Rule_Rec.ALLOC_RESOURCE_STRUCT_TYPE ;
207 G_ALLOC_RESOURCE_LIST_ID := Rule_Rec.ALLOC_RESOURCE_LIST_ID;
208 G_Alloc_Rbs_Version_Id := Rule_Rec.Alloc_RBs_Version;
209 If RULE_REC.ALLOC_RESOURCE_STRUCT_TYPE = 'RBS' Then
210 Open Is_Rbs_Valid( Rule_Rec.ALLOC_RESOURCE_LIST_ID );
211 Fetch Is_Rbs_Valid Into G_Alloc_Rbs_Valid_Flag;
212 IF Is_Rbs_Valid%NOTFOUND Then
213 G_Alloc_Struct_Type := Null;
214 G_ALLOC_RESOURCE_LIST_ID := Null;
215 G_Alloc_Rbs_Version_Id := Null;
216 G_Alloc_Rbs_Valid_Flag := 'N';
217 x_retcode := 'PA_AL_COPY_VAL_RBS';
218 End If;
219 Close Is_Rbs_Valid;
220 End IF;
221 G_BASIS_Struct_Type := Rule_Rec.BASIS_RESOURCE_STRUCT_TYPE ;
222 G_BASIS_RESOURCE_LIST_ID := Rule_Rec.BASIS_RESOURCE_LIST_ID;
223 G_BASIS_Rbs_Version_Id := Rule_Rec.BASIS_RBs_Version;
224 If RULE_REC.BASIS_RESOURCE_STRUCT_TYPE = 'RBS' Then
225 Open Is_Rbs_Valid(Rule_Rec.BASIS_RESOURCE_LIST_ID );
226 Fetch Is_Rbs_Valid Into G_Basis_Rbs_Valid_Flag;
227 IF Is_Rbs_Valid%NOTFOUND Then
228 G_BASIS_Struct_Type := Null;
229 G_BASIS_RESOURCE_LIST_ID := Null;
230 G_BASIS_Rbs_Version_Id := Null;
231 G_Basis_Rbs_Valid_Flag := 'N';
232 x_retcode := 'PA_AL_COPY_VAL_RBS';
233 End If;
234 Close Is_Rbs_Valid;
235 End IF;
236 /* End of Bug# 3643304. ALLOC RULE SHOULD HONOR RBS EFFECTIVE DATES */
237
238 /* Bug# 3678479. ALLOC RULE SHOULD HONOR FIN PLAN AND BUDGET EFFECTIVE DATES */
239
240 If Rule_Rec.BASIS_BALANCE_CATEGORY = 'F' Then
241
242 G_Fin_Plan_Type_Id := RULE_REC.BASIS_FIN_PLAN_TYPE_ID;
243 Open Is_Fin_Plan_Valid ( G_Fin_Plan_Type_Id ) ;
244 Fetch Is_Fin_Plan_Valid Into G_BUDGET_FIN_PLAN_VALID_FLAG ;
245
246 If Is_Fin_Plan_Valid%NotFOund Then
247 G_BUDGET_FIN_PLAN_VALID_FLAG := 'N';
248 G_Fin_Plan_Type_Id := Null;
249 x_retcode := 'PA_AL_COPY_VAL_RBS';
250 End IF;
251
252 Elsif Rule_Rec.BASIS_BALANCE_CATEGORY = 'B' Then
253
254 G_Budget_Type_Code := RULE_REC.BASIS_BUDGET_TYPE_CODE;
255 Open Is_Budget_Valid ( G_Budget_Type_Code ) ;
256 Fetch Is_Budget_Valid Into G_BUDGET_FIN_PLAN_VALID_FLAG ;
257 If Is_Budget_Valid%NotFOund Then
258 G_BUDGET_FIN_PLAN_VALID_FLAG := 'N';
259 G_Budget_Type_Code := Null;
260 x_retcode := 'PA_AL_COPY_VAL_RBS';
261 End IF;
262
263 End If;
264
265 /* End Of Bug# 3678479. ALLOC RULE SHOULD HONOR FIN PLAN AND BUDGET EFFECTIVE DATES */
266
267
268
269
270 PA_ALLOC_RULES_ALL_PKG.INSERT_ROW
271 ( G_ROWID ,
272 G_RULE_ID ,
273 P_TO_RULE_NAME ,
274 P_TO_DESCRIPTION ,
275 RULE_REC.POOL_PERCENT ,
276 RULE_REC.PERIOD_TYPE ,
277 RULE_REC.SOURCE_AMOUNT_TYPE ,
278 RULE_REC.SOURCE_BALANCE_CATEGORY,
279 RULE_REC.SOURCE_BALANCE_TYPE ,
280 G_ALLOC_RESOURCE_LIST_ID ,
281 RULE_REC.AUTO_RELEASE_FLAG ,
282 RULE_REC.ALLOCATION_METHOD ,
283 RULE_REC.IMP_WITH_EXCEPTION ,
284 RULE_REC.DUP_TARGETS_FLAG ,
285 RULE_REC.TARGET_EXP_TYPE_CLASS ,
286 RULE_REC.TARGET_EXP_ORG_ID ,
287 RULE_REC.TARGET_EXP_TYPE ,
288 RULE_REC.TARGET_COST_TYPE ,
289 RULE_REC.OFFSET_EXP_TYPE_CLASS ,
290 RULE_REC.OFFSET_EXP_ORG_ID ,
291 RULE_REC.OFFSET_EXP_TYPE ,
292 RULE_REC.OFFSET_COST_TYPE ,
293 RULE_REC.OFFSET_METHOD ,
294 RULE_REC.OFFSET_PROJECT_ID ,
295 RULE_REC.OFFSET_TASK_ID ,
296 RULE_REC.BASIS_METHOD ,
297 RULE_REC.BASIS_RELATIVE_PERIOD ,
298 RULE_REC.BASIS_AMOUNT_TYPE ,
299 RULE_REC.BASIS_BALANCE_CATEGORY ,
300 G_Budget_Type_Code , /* Bug# 3678479. ALLOC RULE SHOULD HONOR FIN PLAN AND BUDGET EFFECTIVE DATES */
301 RULE_REC.BASIS_BUDGET_ENTRY_METHOD_CODE ,
302 RULE_REC.BASIS_BALANCE_TYPE ,
303 G_BASIS_RESOURCE_LIST_ID ,
304 RULE_REC.SOURCE_EXTN_FLAG ,
305 RULE_REC.TARGET_EXTN_FLAG ,
306 RULE_REC.FIXED_AMOUNT ,
307 RULE_REC.START_DATE_ACTIVE ,
308 RULE_REC.END_DATE_ACTIVE ,
309 RULE_REC.ATTRIBUTE_CATEGORY ,
310 RULE_REC.ATTRIBUTE1 ,
311 RULE_REC.ATTRIBUTE2 ,
312 RULE_REC.ATTRIBUTE3 ,
313 RULE_REC.ATTRIBUTE4 ,
314 RULE_REC.ATTRIBUTE5 ,
315 RULE_REC.ATTRIBUTE6 ,
316 RULE_REC.ATTRIBUTE7 ,
317 RULE_REC.ATTRIBUTE8 ,
318 RULE_REC.ATTRIBUTE9 ,
319 RULE_REC.ATTRIBUTE10 ,
320 G_CREATION_DATE ,
321 G_CREATED_BY ,
322 G_LAST_UPDATE_DATE ,
323 G_LAST_UPDATED_BY ,
324 G_LAST_UPDATE_LOGIN ,
325 RULE_REC.LIMIT_TARGET_PROJECTS_CODE ,
326 G_Fin_Plan_Type_Id /* added bug 21619977 */ , /* Bug# 3678479. ALLOC RULE SHOULD HONOR FIN PLAN AND BUDGET EFFECTIVE DATES */
327 /* FP.M : Allocation Impact : 3512552 */
328 /* Bug# 3643304. ALLOC RULE SHOULD HONOR RBS EFFECTIVE DATES */
329 G_Alloc_Struct_Type ,
330 G_BASIS_Struct_Type ,
331 G_Alloc_Rbs_Version_Id ,
332 G_BASIS_Rbs_Version_Id ,
333 RULE_REC.ORG_ID
334 );
335 END LOOP;
336 --Insert Project SOurce Information
337 FOR SRC_REC in C_Source
338 LOOP
339 PA_ALLOC_SOURCE_LINES_PKG.INSERT_ROW (
340 G_ROWID ,
341 G_RULE_ID ,
342 SRC_REC.LINE_NUM ,
343 SRC_REC.PROJECT_ORG_ID ,
344 SRC_REC.TASK_ORG_ID ,
345 SRC_REC.PROJECT_TYPE ,
346 SRC_REC.CLASS_CATEGORY ,
347 SRC_REC.CLASS_CODE ,
348 SRC_REC.SERVICE_TYPE ,
349 SRC_REC.PROJECT_ID ,
350 SRC_REC.TASK_ID ,
351 SRC_REC.EXCLUDE_FLAG ,
352 G_CREATED_BY,
353 G_CREATION_DATE ,
354 G_LAST_UPDATE_DATE,
355 G_LAST_UPDATED_BY ,
356 G_LAST_UPDATE_LOGIN );
357 END LOOP;
358 --Insert GL Source Information
359 FOR GL_REC IN C_GL_SOURCE
360 LOOP
361 INSERT INTO PA_ALLOC_GL_LINES
362 ( RULE_ID
363 , LINE_NUM
364 , SOURCE_CCID
365 , SUBTRACT_FLAG
366 , CREATED_BY
367 , CREATION_DATE
368 , LAST_UPDATE_DATE
369 , LAST_UPDATED_BY
370 , LAST_UPDATE_LOGIN
371 , SOURCE_PERCENT )
372 values
373 ( G_RULE_ID
374 , GL_REC.LINE_NUM
375 , GL_REC.SOURCE_CCID
376 , GL_REC.SUBTRACT_FLAG
377 , G_CREATED_BY
378 , G_CREATION_DATE
379 , G_LAST_UPDATE_DATE
380 , G_LAST_UPDATED_BY
381 , G_LAST_UPDATE_LOGIN
382 , GL_REC.SOURCE_PERCENT );
383 END LOOP; /* for C_GL_SOURCE */
384 --Insert Target Information
385 FOR TGT_REC IN C_TARGET
386 LOOP
387 PA_ALLOC_TARGET_LINES_PKG.INSERT_ROW (
388 G_ROWID ,
389 G_RULE_ID ,
390 TGT_REC.LINE_NUM ,
391 TGT_REC.PROJECT_ORG_ID ,
392 TGT_REC.TASK_ORG_ID ,
393 TGT_REC.PROJECT_TYPE ,
394 TGT_REC.CLASS_CATEGORY ,
395 TGT_REC.CLASS_CODE ,
396 TGT_REC.SERVICE_TYPE ,
397 TGT_REC.PROJECT_ID ,
398 TGT_REC.TASK_ID ,
399 TGT_REC.EXCLUDE_FLAG ,
400 TGT_REC.BILLABLE_ONLY_FLAG ,
401 TGT_REC.LINE_PERCENT ,
402 G_CREATED_BY ,
403 G_CREATION_DATE ,
404 G_LAST_UPDATE_DATE ,
405 G_LAST_UPDATED_BY ,
406 G_LAST_UPDATE_LOGIN );
407 END LOOP; /* End for C_TARGET */
408 --Insert Resource Information
409 FOR RSR_REC IN C_RESOURCES
410 LOOP
411 /* Bug# 3643304. ALLOC RULE SHOULD HONOR RBS EFFECTIVE DATES */
412 If (RSR_REC.MEMBER_TYPE = 'S' And G_Alloc_Rbs_Valid_Flag = 'Y' )
413 Or
414 (RSR_REC.MEMBER_TYPE = 'B' And G_Basis_Rbs_Valid_Flag = 'Y' )
415 Then
416 PA_ALLOC_RESOURCES_PKG.INSERT_ROW (
417 G_ROWID ,
418 G_RULE_ID ,
419 RSR_REC.MEMBER_TYPE ,
420 RSR_REC.RESOURCE_LIST_MEMBER_ID ,
421 RSR_REC.EXCLUDE_FLAG,
422 RSR_REC.TARGET_EXPND_TYPE ,
423 RSR_REC.OFFSET_EXPND_TYPE ,
424 RSR_REC.RESOURCE_PERCENTAGE ,
425 G_CREATED_BY ,
426 G_CREATION_DATE ,
427 G_LAST_UPDATE_DATE ,
428 G_LAST_UPDATED_BY ,
429 G_LAST_UPDATE_LOGIN
430 );
431 End If;
432 END LOOP; /* End for C_RESOURCES */
433 commit;
434 EXCEPTION
435 when others then
436 x_retcode := SQLCODE;
437 x_errbuf := SQLERRM;
438 END COPY_RULE;
439 END PA_ALLOC_COPY_RULE_PKG;