1 PACKAGE BODY PA_BUDGET_LINES_UTILS as
2 /* $Header: PAFPBLUB.pls 120.2 2007/02/06 09:44:28 dthakker noship $
3 Start of Comments
4 Package name : PA_BUDGET_LINES_UTILS
5 Purpose : utility API's for pa_budget_lines table
6 NOTE : Used in Generation, WebADI, Change Document, upgrade, etc flows
7 in which pa_budget_lines are updated directly without going
8 through calculate API.
9 End of Comments
10 */
11
12
13 -- bug 5067200: Added this private API to null out display_quantity for
14 -- non rate based planning txns. It is called from populate_display_qty.
15 PROCEDURE clear_non_rate_res_disp_qty
16 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
17 x_return_status OUT NOCOPY VARCHAR2)
18 IS
19
20 CURSOR get_non_rate_based_asgn
21 IS
22 SELECT resource_assignment_id
23 FROM pa_resource_assignments
24 WHERE budget_version_id = p_budget_version_id
25 AND rate_based_flag = 'N';
26
27 l_non_rate_based_asgmt_id_tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
28 L_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
29 li_curr_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30
31 BEGIN
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 IF L_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
35 pa_debug.g_err_stage:='In PA_BUDGET_LINES_UTILS.clear_non_rate_res_disp_qty';
36 pa_debug.write('PA_BUDGET_LINES_UTILS',pa_debug.g_err_stage,3);
37 END IF;
38
39 OPEN get_non_rate_based_asgn;
40 FETCH get_non_rate_based_asgn BULK COLLECT INTO l_non_rate_based_asgmt_id_tab;
41 CLOSE get_non_rate_based_asgn;
42
43 IF l_non_rate_based_asgmt_id_tab.COUNT > 0 THEN
44
45 FORALL i IN l_non_rate_based_asgmt_id_tab.FIRST .. l_non_rate_based_asgmt_id_tab.LAST
46 UPDATE pa_budget_lines
47 SET display_quantity = null
48 WHERE budget_version_id = p_budget_version_id
49 AND resource_assignment_id = l_non_rate_based_asgmt_id_tab(i);
50 END IF;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
55 IF L_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
56 PA_DEBUG.write_log (x_module => 'PA_BUDGET_LINES_UTILS',
57 x_msg => 'Unexp. Error:' || 'clear_non_rate_res_disp_qty' || SQLERRM,
58 x_log_level => 6);
59 END IF;
60 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_LINES_UTILS',
61 p_procedure_name => 'clear_non_rate_res_disp_qty');
62 RAISE;
63 END clear_non_rate_res_disp_qty;
64
65 procedure Populate_Display_Qty
66 (p_budget_version_id IN NUMBER,
67 p_context IN VARCHAR2,
68 p_use_temp_table_flag IN VARCHAR2 DEFAULT 'N',
69 p_resource_assignment_id_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
70 p_set_disp_qty_null_for_nrbf IN VARCHAR2,
71 x_return_status OUT NOCOPY VARCHAR2) is
72
73 CURSOR get_rate_based_assignments IS
74 SELECT resource_assignment_id
75 FROM pa_resource_assignments
76 WHERE budget_version_id = p_budget_version_id
77 AND rate_based_flag = 'Y';
78
79 CURSOR get_rate_based_asgmts_temp_fp IS
80 SELECT ra.resource_assignment_id
81 FROM pa_resource_assignments ra,
82 pa_resource_asgn_curr_tmp ract
83 WHERE ra.budget_version_id = p_budget_version_id
84 AND ra.resource_assignment_id = ract.resource_assignment_id
85 AND ra.rate_based_flag = 'Y';
86
87 CURSOR get_rate_based_asgmts_temp_wp IS
88 SELECT resource_assignment_id
89 FROM pa_resource_asgn_curr_tmp;
90
91 l_rate_based_asgmt_id_tab SYSTEM.pa_num_tbl_type;
92 L_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
93 li_curr_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
94
95
96 BEGIN
97
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 IF L_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
101 pa_debug.g_err_stage:='In PA_BUDGET_LINES_UTILS.Populate_Display_Qty';
102 pa_debug.write('PA_BUDGET_LINES_UTILS',pa_debug.g_err_stage,3);
103 END IF;
104
105 -- Option #1: Process all pa_budget_lines of resource_assignment_id's read
106 -- from the pa_resource_asgn_curr_tmp temporary table.
107 -- Option #2: Process all pa_budget_lines of resource_assignment_id's read
108 -- from the IN parameter, p_resource_assignment_id_tab.
109 -- Option #3: Process all pa_budget_lines in the given budget version id.
110
111 -- IF p_context = 'WORKPLAN', simply copy quantity to display_quantity
112 -- IF p_context = 'FINANCIAL', copy quantity according to rate_based_flag
113
114 IF p_use_temp_table_flag = 'Y' THEN
115
116 IF p_context = 'WORKPLAN' THEN
117 OPEN get_rate_based_asgmts_temp_wp;
118 FETCH get_rate_based_asgmts_temp_wp BULK COLLECT INTO l_rate_based_asgmt_id_tab;
119 CLOSE get_rate_based_asgmts_temp_wp;
120 ELSE
121 OPEN get_rate_based_asgmts_temp_fp;
122 FETCH get_rate_based_asgmts_temp_fp BULK COLLECT INTO l_rate_based_asgmt_id_tab;
123 CLOSE get_rate_based_asgmts_temp_fp;
124 END IF;
125
126 IF l_rate_based_asgmt_id_tab IS NOT NULL AND l_rate_based_asgmt_id_tab.COUNT > 0 THEN
127
128 FORALL i IN l_rate_based_asgmt_id_tab.FIRST..l_rate_based_asgmt_id_tab.LAST
129 UPDATE pa_budget_lines
130 SET display_quantity = quantity
131 WHERE budget_version_id = p_budget_version_id
132 AND resource_assignment_id = l_rate_based_asgmt_id_tab(i);
133 END IF;
134
135 ELSIF p_resource_assignment_id_tab is not null AND p_resource_assignment_id_tab.count > 0 THEN
136
137 IF p_context = 'WORKPLAN' THEN
138
139 FORALL i IN p_resource_assignment_id_tab.FIRST..p_resource_assignment_id_tab.LAST
140 UPDATE pa_budget_lines
141 SET display_quantity = quantity
142 WHERE budget_version_id = p_budget_version_id
143 AND resource_assignment_id = p_resource_assignment_id_tab(i);
144
145 ELSE
146
147 FORALL i IN p_resource_assignment_id_tab.FIRST..p_resource_assignment_id_tab.LAST
148 UPDATE pa_budget_lines
149 SET display_quantity = quantity
150 WHERE budget_version_id = p_budget_version_id
151 AND resource_assignment_id = p_resource_assignment_id_tab(i)
152 AND resource_assignment_id in
153 (select resource_assignment_id
154 from pa_resource_assignments
155 where rate_based_flag = 'Y'
156 and budget_version_id = p_budget_version_id
157 and resource_assignment_id = p_resource_assignment_id_tab(i));
158 END IF;
159
160 ELSE
161
162 IF p_context = 'WORKPLAN' THEN
163
164 UPDATE pa_budget_lines
165 SET display_quantity = quantity
166 WHERE budget_version_id = p_budget_version_id;
167
168 ELSE
169
170 OPEN get_rate_based_assignments;
171 FETCH get_rate_based_assignments BULK COLLECT INTO l_rate_based_asgmt_id_tab;
172 CLOSE get_rate_based_assignments;
173
174 IF l_rate_based_asgmt_id_tab IS NOT NULL AND l_rate_based_asgmt_id_tab.COUNT > 0 THEN
175
176 FORALL i IN l_rate_based_asgmt_id_tab.FIRST .. l_rate_based_asgmt_id_tab.LAST
177 UPDATE pa_budget_lines
178 SET display_quantity = quantity
179 WHERE budget_version_id = p_budget_version_id
180 AND resource_assignment_id = l_rate_based_asgmt_id_tab(i);
181 END IF;
182 END IF;
183
184 END IF;
185
186 -- bug 5067200: Calling a private API clear_non_rate_res_disp_qty to clear out
187 -- display_quantity column for non rate based planning txns, as it is possible
188 -- that some rate based planning txn have been converted to a non rate based one
189 -- and we need to null out values from display_quantity in pa_budget_lines
190 IF p_context = 'FINANCIAL' AND
191 p_set_disp_qty_null_for_nrbf = 'Y' THEN -- bug 5006029: added this condition.
192 clear_non_rate_res_disp_qty
193 (p_budget_version_id => p_budget_version_id,
194 x_return_status => x_return_status);
195 END IF;
196
197 EXCEPTION
198
199 WHEN OTHERS THEN
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201 IF L_DEBUG_MODE = 'Y' AND (li_curr_level <= 6) THEN
202 PA_DEBUG.write_log (x_module => 'PA_BUDGET_LINES_UTILS',
203 x_msg => 'Unexp. Error:' || 'Populate_Display_Qty' || SQLERRM,
204 x_log_level => 6);
205 END IF;
206 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_LINES_UTILS',
207 p_procedure_name => 'Populate_Display_Qty');
208 RAISE;
209
210
211 END Populate_Display_Qty;
212
213
214 END PA_BUDGET_LINES_UTILS;
[Home] [Help]