[Home] [Help]
117: CURSOR C IS
118: SELECT NVL(SUM(ABV.VALUE),0)
119: FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV,
120: PER_ASSIGNMENT_STATUS_TYPES AST,
121: PER_ASSIGNMENTS_F A
122: WHERE A.POSITION_ID = X_POSITION_ID
123: AND A.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
124: AND X_START_DATE BETWEEN A.EFFECTIVE_START_DATE AND
125: A.EFFECTIVE_END_DATE
169: CURSOR C IS
170: SELECT NVL(SUM(ABV.VALUE),0)
171: FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV,
172: PER_ASSIGNMENT_STATUS_TYPES AST,
173: PER_ASSIGNMENTS_F A
174: WHERE A.POSITION_ID = X_POSITION_ID
175: AND A.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
176: AND X_END_DATE BETWEEN A.EFFECTIVE_START_DATE AND
177: A.EFFECTIVE_END_DATE
199: -- PROCEDURE GET_STARTERS: Obtain the number of assignments attaining a
200: -- position within a period.
201: --
202: -- G1448: the cursor has an error in that it joins directly to the
203: -- per_assignments_f table, with the result that a value is counted
204: -- once for *each occurrence* of an assignment record. So, if an assgt
205: -- has 2 date effective updates, that results in three records for the
206: -- assgt. Hence the starters count is often too high. Recoded using the
207: -- following rules:
244: CURSOR C IS
245: SELECT A.ASSIGNMENT_ID, A.POSITION_ID,
246: A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE, ABV.VALUE
247: FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV,
248: PER_ASSIGNMENTS_F A
249: WHERE A.POSITION_ID = X_POSITION_ID
250: AND A.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
251: AND A.EFFECTIVE_START_DATE <= X_END_DATE
252: AND A.EFFECTIVE_END_DATE >= X_START_DATE - 1
316: -- PROCEDURE GET_LEAVERS: Obtain the number of assignments leaving
317: -- a position within a period.
318: --
319: -- G1448: the cursor has an error in that it joins directly to the
320: -- per_assignments_f table, with the result that a value is counted
321: -- once for *each occurrence* of an assignment record. So, if an assgt
322: -- has 2 date effective updates, that results in three records for the
323: -- assgt. Hence the starters count is often too high. Recoded using the
324: -- following rules:
370: SELECT A.ASSIGNMENT_ID, A.POSITION_ID, AST.PER_SYSTEM_STATUS,
371: A.EFFECTIVE_START_DATE, A.EFFECTIVE_END_DATE, ABV.VALUE
372: FROM PER_ASSIGNMENT_BUDGET_VALUES_F ABV,
373: PER_ASSIGNMENT_STATUS_TYPES AST,
374: PER_ASSIGNMENTS_F A
375: WHERE A.POSITION_ID = X_POSITION_ID
376: AND A.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID
377: AND A.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
378: AND A.EFFECTIVE_START_DATE <= X_END_DATE + 1