1 PACKAGE body PA_FP_FCST_GEN_AMT_UTILS as
2 /* $Header: PAFPGFUB.pls 120.3 2007/02/06 09:59:05 dthakker noship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 /**
7 * This procedure verifies that the following attributes match for the specified
8 * Source plan version(s) and Target plan version:
9 * i. Fully shared WBS (if source WP bvid not null) -- Bug 4251201
10 * ii. Resource List
11 * iii. Time Phasing
12 * iv. Planning Level
13 * v. Multi-Currency Option
14 * With the exception of multi-currency and planning level, attributes are said
15 * to match if equal.
16 * The following table defines the conditions under which multi-currency matches:
17 * Source Target Match
18 * Y Y Y
19 * N Y Y
20 * N N Y
21 * Y N N
22 * Note that the only case for multi-currency mismatch is when the Source is
23 * multi-currency enabled and the Target is not. Additionally, note that when
24 * both Source versions are supplied, we check that multi-currency matches
25 * between each Source and the Target (Source multi-currency options do not
26 * have to match each other).
27 * The following table defines when planning levels *do not* match:
28 * Source Target Match
29 * L T N
30 * L P N
31 * T P N
32 * All other planning level combinations (i.e. when the source is planned at
33 * the same or higher level compared to the target) are considered to match.
34 *
35 * The parameters P_PROJECT_ID and P_FP_TARGET_PLAN_VER_ID must be non-null.
36 * If at least one of P_WP_SRC_PLAN_VER_ID and P_FP_SRC_PLAN_VER_ID is non-null
37 * and the attributes match for all specified plan versions, then the OUT
38 * parameter X_SAME_PLANNING_OPTION_FLAG will have value 'Y'.Otherwise,
39 * X_SAME_PLANNING_OPTION_FLAG will have value 'N'.
40 */
41 PROCEDURE COMPARE_ETC_SRC_TARGET_FP_OPT
42 (P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
43 P_WP_SRC_PLAN_VER_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
44 P_FP_SRC_PLAN_VER_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
45 P_FP_TARGET_PLAN_VER_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
46 X_SAME_PLANNING_OPTION_FLAG OUT NOCOPY VARCHAR2,
47 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
48 X_MSG_COUNT OUT NOCOPY NUMBER,
49 X_MSG_DATA OUT NOCOPY VARCHAR2)
50 IS
51 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_FCST_GEN_AMT_UTILS.COMPARE_ETC_SRC_TARGET_FP_OPT';
52 l_log_level CONSTANT PLS_INTEGER := 5;
53 l_count NUMBER;
54 l_msg_count NUMBER;
55 l_data VARCHAR2(1000);
56 l_msg_data VARCHAR2(1000);
57 l_msg_index_out NUMBER;
58
59 l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
60 l_fp_cols_rec_target PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
61 l_fp_cols_rec_src PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
62 BEGIN
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 x_msg_count := 0;
65
66 IF p_pa_debug_mode = 'Y' THEN
67 PA_DEBUG.SET_CURR_FUNCTION( p_function => 'COMPARE_ETC_SRC_TARGET_FP_OPT',
68 p_debug_mode => p_pa_debug_mode );
69 END IF;
70
71 /* Initialize the planning option flag */
72 x_same_planning_option_flag := 'N';
73
74 /* Enforce that p_project_id and p_fp_target_plan_ver_id are non-null */
75 IF p_project_id IS NULL OR p_fp_target_plan_ver_id IS NULL THEN
76 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
77 p_msg_name => 'PA_FP_INV_PARAM_PASSED' );
78 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
79 END IF;
80
81 /* Check that the project has Fully Shared WBS,
82 * and that one of p_wp_src_plan_ver_id or p_fp_src_plan_ver_id is non-null */
83
84 -- Bug 4251201: We should only check that the WBS is Fully Shared
85 -- when the passed Workplan budget_version_id parameter is not null.
86
87 l_stru_sharing_code :=
88 PA_PROJECT_STRUCTURE_UTILS.GET_STRUCTURE_SHARING_CODE( p_project_id => p_project_id );
89 IF (p_wp_src_plan_ver_id IS NULL AND p_fp_src_plan_ver_id IS NULL) OR
90 (p_wp_src_plan_ver_id IS NOT NULL AND l_stru_sharing_code <> 'SHARE_FULL') THEN
91 IF p_pa_debug_mode = 'Y' THEN
92 PA_DEBUG.RESET_CURR_FUNCTION;
93 END IF;
94 RETURN;
95 END IF;
96
97 /* CAll API to get Target data into l_fp_cols_rec_target. */
98 IF p_pa_debug_mode = 'Y' THEN
99 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
100 ( p_msg => 'Before calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
101 p_module_name => l_module_name,
102 p_log_level => l_log_level );
103 END IF;
104 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS( p_project_id => p_project_id,
105 p_budget_version_id => p_fp_target_plan_ver_id,
106 x_fp_cols_rec => l_fp_cols_rec_target,
107 x_return_status => x_return_status,
108 x_msg_count => x_msg_count,
109 x_msg_data => x_msg_data );
110 IF p_pa_debug_mode = 'Y' THEN
111 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
112 ( p_msg => 'Status after calling
113 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
114 || x_return_status,
115 p_module_name => l_module_name,
116 p_log_level => l_log_level );
117 END IF;
118 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
119 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
120 END IF;
121
122 /* Re-Initialize flag */
123 x_same_planning_option_flag := 'Y';
124
125 /* Check Source workplan attributes against Target plan */
126 IF p_wp_src_plan_ver_id IS NOT NULL THEN
127
128 /* CAll API to get Source data into l_fp_cols_rec_src */
129 IF p_pa_debug_mode = 'Y' THEN
130 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
131 ( p_msg => 'Before calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
132 p_module_name => l_module_name,
133 p_log_level => l_log_level );
134 END IF;
135 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS( p_project_id => p_project_id,
136 p_budget_version_id => p_wp_src_plan_ver_id,
137 x_fp_cols_rec => l_fp_cols_rec_src,
138 x_return_status => x_return_status,
139 x_msg_count => x_msg_count,
140 x_msg_data => x_msg_data );
141 IF p_pa_debug_mode = 'Y' THEN
142 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
143 ( p_msg => 'Status after calling
144 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
145 || x_return_status,
146 p_module_name => l_module_name,
147 p_log_level => l_log_level );
148 END IF;
149 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
150 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
151 END IF;
152
153 /* Verify that Source and Target plan attributes of interest match */
154 IF l_fp_cols_rec_src.x_resource_list_id <> l_fp_cols_rec_target.x_resource_list_id OR
155 l_fp_cols_rec_src.x_time_phased_code <> l_fp_cols_rec_target.x_time_phased_code OR
156 (l_fp_cols_rec_src.x_fin_plan_level_code = 'L' AND
157 l_fp_cols_rec_target.x_fin_plan_level_code IN ('T','P')) OR
158 (l_fp_cols_rec_src.x_fin_plan_level_code = 'T' AND
159 l_fp_cols_rec_target.x_fin_plan_level_code = 'P' ) OR
160 (l_fp_cols_rec_src.x_plan_in_multi_curr_flag = 'Y' AND
161 l_fp_cols_rec_target.x_plan_in_multi_curr_flag = 'N') THEN
162 x_same_planning_option_flag := 'N';
163 IF p_pa_debug_mode = 'Y' THEN
164 PA_DEBUG.RESET_CURR_FUNCTION;
165 END IF;
166 RETURN;
167 END IF;
168 END IF;
169
170 /* Check Source financial plan attributes against Target plan */
171 IF p_fp_src_plan_ver_id IS NOT NULL THEN
172
173 /* CAll API to get Source data into l_fp_cols_rec_src */
174 IF p_pa_debug_mode = 'Y' THEN
175 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
176 ( p_msg => 'Before calling PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS',
177 p_module_name => l_module_name,
178 p_log_level => l_log_level );
179 END IF;
180 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS( p_project_id => p_project_id,
181 p_budget_version_id => p_fp_src_plan_ver_id,
182 x_fp_cols_rec => l_fp_cols_rec_src,
183 x_return_status => x_return_status,
184 x_msg_count => x_msg_count,
185 x_msg_data => x_msg_data );
186 IF p_pa_debug_mode = 'Y' THEN
187 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
188 ( p_msg => 'Status after calling
189 PA_FP_GEN_AMOUNT_UTILS.GET_PLAN_VERSION_DTLS: '
190 || x_return_status,
191 p_module_name => l_module_name,
192 p_log_level => l_log_level );
193 END IF;
194 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
195 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
196 END IF;
197
198 /* Verify that Source and Target plan attributes of interest match */
199 IF l_fp_cols_rec_src.x_resource_list_id <> l_fp_cols_rec_target.x_resource_list_id OR
200 l_fp_cols_rec_src.x_time_phased_code <> l_fp_cols_rec_target.x_time_phased_code OR
201 (l_fp_cols_rec_src.x_fin_plan_level_code = 'L' AND
202 l_fp_cols_rec_target.x_fin_plan_level_code IN ('T','P')) OR
203 (l_fp_cols_rec_src.x_fin_plan_level_code = 'T' AND
204 l_fp_cols_rec_target.x_fin_plan_level_code = 'P' ) OR
205 (l_fp_cols_rec_src.x_plan_in_multi_curr_flag = 'Y' AND
206 l_fp_cols_rec_target.x_plan_in_multi_curr_flag = 'N') THEN
207 x_same_planning_option_flag := 'N';
208 END IF;
209 END IF;
210
211 IF p_pa_debug_mode = 'Y' THEN
212 PA_DEBUG.RESET_CURR_FUNCTION;
213 END IF;
214 EXCEPTION
215 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
216 l_msg_count := FND_MSG_PUB.count_msg;
217 IF l_msg_count = 1 THEN
218 PA_INTERFACE_UTILS_PUB.GET_MESSAGES
219 ( p_encoded => FND_API.G_TRUE,
220 p_msg_index => 1,
221 p_msg_count => l_msg_count,
222 p_msg_data => l_msg_data,
223 p_data => l_data,
224 p_msg_index_out => l_msg_index_out );
225 x_msg_data := l_data;
226 x_msg_count := l_msg_count;
227 ELSE
228 x_msg_count := l_msg_count;
229 END IF;
230
231 ROLLBACK;
232 x_return_status := FND_API.G_RET_STS_ERROR;
233
234 IF p_pa_debug_mode = 'Y' THEN
235 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
236 ( p_msg => 'Invalid Arguments Passed',
237 p_module_name => l_module_name,
238 p_log_level => l_log_level );
239 PA_DEBUG.RESET_CURR_FUNCTION;
240 END IF;
241 RAISE;
242 WHEN OTHERS THEN
243 ROLLBACK;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245 x_msg_count := 1;
246 x_msg_data := substr(sqlerrm,1,240);
247 FND_MSG_PUB.ADD_EXC_MSG
248 ( p_pkg_name => 'PA_FP_FCST_GEN_AMT_UTILS',
249 p_procedure_name => 'COMPARE_ETC_SRC_TARGET_FP_OPT',
250 p_error_text => substr(sqlerrm,1,240) );
251
252 IF P_PA_DEBUG_MODE = 'Y' THEN
253 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
254 ( p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
255 p_module_name => l_module_name,
256 p_log_level => l_log_level );
257 PA_DEBUG.RESET_CURR_FUNCTION;
258 END IF;
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260
261 END COMPARE_ETC_SRC_TARGET_FP_OPT;
262
263 /***
264 * For Remaining Plan, Work Quantity, and non-Commitment ETC,
265 * whether an ETC quantity/amount should be generated or not
266 * is a function of its sign (i.e. positive or negative) in
267 * relation to the sign of the (plan) amount it is derived from.
268 * This function tests whether plan and ETC quantity/amounts
269 * having matching signs.
270 *
271 * The function behaves as follows:
272 * Returns TRUE if (plan >= 0 and etc > 0) or (plan < 0 and etc < 0).
273 * Returns FALSE otherwise.
274 *
275 * Parameters:
276 * P_PLAN_QTY_OR_AMT
277 * The plan quantity or amount used to compute the value
278 * of p_etc_qty_or_amt.
279 * P_ETC_QTY_OR_AMT
280 * The ETC quantity or amount computed from p_plan_qty_or_amt.
281 * P_CALLING_MODULE
282 * The module calling this function. Valid values include:
283 * 'FORECAST_GENERATION'
284 * 'WORKPLAN'
285 * This parameter can be used later to change the behavior
286 * of this function based on the needs of a calling module.
287 */
288 FUNCTION PLAN_ETC_SIGNS_MATCH
289 ( P_PLAN_QTY_OR_AMT IN NUMBER,
290 P_ETC_QTY_OR_AMT IN NUMBER,
291 P_CALLING_MODULE IN VARCHAR2 ) RETURN BOOLEAN
292 IS
293 BEGIN
294 RETURN (p_plan_qty_or_amt >= 0 AND p_etc_qty_or_amt > 0) OR
295 (p_plan_qty_or_amt < 0 AND p_etc_qty_or_amt < 0);
296 END PLAN_ETC_SIGNS_MATCH;
297
298 /*
299 * For Workplan progress, this function computes the ETC quantity based
300 * on Planned and Actual Qty by applying the following rules. This function
301 * is another variation of function PLAN_ETC_SIGNS_MATCH in this package.
302 *
303 * 1. If actual > plan and plan is negative, ETC = Plan - Actual
304 * 2. If actual > plan and plan is positive, ETC = 0
305 * 3. If actual < plan and plan is negative, ETC = 0
306 * 4. If actual < plan and plan is positive, ETC = Plan - Actual
307 * 5. If actual = plan, ETC = 0
308 *
309 * Parameters:
310 * P_PLAN_QTY_OR_AMT
311 * The plan quantity or amount used to compute the value
312 * of p_etc_qty_or_amt.
313 * P_ACT_QTY_OR_AMT
314 * The actual quantity or amount computed from p_plan_qty_or_amt.
315 * P_CALLING_MODULE
316 * The module calling this function. Valid values include:
317 * 'FORECAST_GENERATION'
318 * 'WORKPLAN'
319 * This parameter can be used later to change the behavior
320 * of this function based on the needs of a calling module.
321 */
322 FUNCTION GET_ETC_FROM_PLAN_ACT
323 ( P_PLAN_QTY_OR_AMT IN NUMBER,
324 P_ACT_QTY_OR_AMT IN NUMBER,
325 P_CALLING_MODULE IN VARCHAR2 DEFAULT 'FORECAST_GENERATION' ) RETURN NUMBER
326 IS
327 l_etc_qty_or_amt NUMBER;
328 BEGIN
329 l_etc_qty_or_amt := nvl(p_plan_qty_or_amt,0) - nvl(p_act_qty_or_amt,0);
330 IF NOT PLAN_ETC_SIGNS_MATCH
331 (p_plan_qty_or_amt,l_etc_qty_or_amt,p_calling_module) THEN
332 l_etc_qty_or_amt := 0;
333 END IF;
334 RETURN l_etc_qty_or_amt;
335 END get_etc_from_plan_act;
336
337 END PA_FP_FCST_GEN_AMT_UTILS;
[Home] [Help]