1 PACKAGE pa_fin_plan_pub as
2 /* $Header: PAFPPUBS.pls 120.3 2008/06/19 15:23:04 jsundara ship $
3 Start of Comments
4 Package name : PA_FIN_PLAN_PUB
5 Purpose : utility API's for Org Forecast pages
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 rollback_on_error EXCEPTION;
12
13 TYPE p_res_assignment_tbl_typ IS TABLE OF
14 pa_budget_lines.resource_assignment_id%TYPE INDEX BY BINARY_INTEGER;
15
16 TYPE p_period_name_tbl_typ IS TABLE OF
17 pa_budget_lines.period_name%TYPE INDEX BY BINARY_INTEGER;
18 TYPE p_start_date_tbl_typ IS TABLE OF
19 pa_budget_lines.start_date%TYPE INDEX BY BINARY_INTEGER;
20 TYPE p_end_date_tbl_typ IS TABLE OF
21 pa_budget_lines.end_date%TYPE INDEX BY BINARY_INTEGER;
22 TYPE p_currency_code_tbl_typ IS TABLE OF
23 pa_budget_lines.txn_currency_code%TYPE INDEX BY BINARY_INTEGER;
24 TYPE p_cost_tbl_typ IS TABLE OF
25 pa_budget_lines.raw_cost%TYPE INDEX BY BINARY_INTEGER;
26 TYPE p_quantity_tbl_typ IS TABLE OF
27 pa_budget_lines.quantity%TYPE INDEX BY BINARY_INTEGER;
28 TYPE p_buck_period_code_tbl_typ IS TABLE OF
29 pa_budget_lines.BUCKETING_PERIOD_CODE%TYPE INDEX BY BINARY_INTEGER;
30 TYPE p_delete_flag_tbl_typ IS TABLE OF
31 pa_fin_plan_lines_tmp.delete_flag%TYPE INDEX BY BINARY_INTEGER;
32
33 --Bug 3964755. Introduced the parameter p_calling_context. Valid values are NULL and 'COPY_PROJECT'
34 procedure Submit_Current_Working
35 (p_calling_context IN VARCHAR2 DEFAULT NULL,
36 p_project_id IN pa_budget_versions.project_id%TYPE,
37 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
38 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
39 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
40 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
41 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
42
43 procedure Set_Current_Working
44 (p_project_id IN pa_budget_versions.project_id%TYPE,
45 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
46 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
47 p_orig_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
48 p_orig_record_version_number IN pa_budget_versions.record_version_number%TYPE,
49 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
50 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
51 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
52
53 procedure Rework_Submitted
54 (p_project_id IN pa_budget_versions.project_id%TYPE,
55 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
56 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
57 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
58 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
59 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
60
61 procedure Mark_As_Original
62 (p_project_id IN pa_budget_versions.project_id%TYPE,
63 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
64 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
65 p_orig_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
66 p_orig_record_version_number IN pa_budget_versions.record_version_number%TYPE,
67 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
68 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
69 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
70
71 procedure Delete_Version
72 (p_project_id IN pa_budget_versions.project_id%TYPE,
73 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
74 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
75 p_context IN VARCHAR2 DEFAULT PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET,
76 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
77 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
78 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
79
80 --p_context can be PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET or PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN.
81 --p_budget_version_id is mandatory whenever p_context is PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET.
82 --When PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_WORKPLAN, data for the version will be deleted when p_budget_version_id
83 --is passed. Otherwise when p_project_id is passed data for the entire project will be deleted.
84 procedure Delete_Version_Helper
85 (p_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL,
86 p_context IN VARCHAR2 DEFAULT PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET,
87 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
88 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
89 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
90 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
91
92 /* added ... for financial planning copy from needs */
93 procedure Copy_Version
94 (p_project_id IN pa_budget_versions.project_id%TYPE,
95 p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
96 p_copy_mode IN VARCHAR2,
97 p_adj_percentage IN NUMBER DEFAULT 0,
98 p_calling_module IN VARCHAR2 DEFAULT PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_ORG_FORECAST,
99 p_pji_rollup_required IN VARCHAR2 DEFAULT 'Y', --Bug 4200168
100 px_target_version_id IN OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
101 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
102 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
103 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
104
105
106 procedure Baseline
107 (p_project_id IN pa_budget_versions.project_id%TYPE,
108 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
109 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
110 p_orig_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
111 p_orig_record_version_number IN pa_budget_versions.record_version_number%TYPE,
112 x_fc_version_created_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
113 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
114 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
115 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
116
117 procedure Create_Version_OrgFcst
118 (p_project_id IN pa_budget_versions.project_id%TYPE,
119 p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE,
120 p_fin_plan_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE default NULL,
121 p_version_name IN pa_budget_versions.version_name%TYPE,
122 p_description IN pa_budget_versions.description%TYPE,
123 p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE,
124 x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
125 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
126 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
127 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
128
129 procedure Regenerate
130 (p_project_id IN pa_budget_versions.project_id%TYPE,
131 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
132 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
133 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
134 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
135 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
136
137 procedure Update_Version
138 (p_project_id IN pa_budget_versions.project_id%TYPE
139 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
140 ,p_record_version_number IN pa_budget_versions.record_version_number%TYPE
141 ,p_version_name IN pa_budget_versions.version_name%TYPE
142 ,p_description IN pa_budget_versions.description%TYPE
143 ,p_change_reason_code IN pa_budget_versions.change_reason_code%TYPE
144 -- Start of additional columns for Bug :- 3088010
145 ,p_attribute_category IN pa_budget_versions.attribute_category%TYPE default NULL
146 ,p_attribute1 IN pa_budget_versions.attribute1%TYPE default NULL
147 ,p_attribute2 IN pa_budget_versions.attribute2%TYPE default NULL
148 ,p_attribute3 IN pa_budget_versions.attribute3%TYPE default NULL
149 ,p_attribute4 IN pa_budget_versions.attribute4%TYPE default NULL
150 ,p_attribute5 IN pa_budget_versions.attribute5%TYPE default NULL
151 ,p_attribute6 IN pa_budget_versions.attribute6%TYPE default NULL
152 ,p_attribute7 IN pa_budget_versions.attribute7%TYPE default NULL
153 ,p_attribute8 IN pa_budget_versions.attribute8%TYPE default NULL
154 ,p_attribute9 IN pa_budget_versions.attribute9%TYPE default NULL
155 ,p_attribute10 IN pa_budget_versions.attribute10%TYPE default NULL
156 ,p_attribute11 IN pa_budget_versions.attribute11%TYPE default NULL
157 ,p_attribute12 IN pa_budget_versions.attribute12%TYPE default NULL
158 ,p_attribute13 IN pa_budget_versions.attribute13%TYPE default NULL
159 ,p_attribute14 IN pa_budget_versions.attribute14%TYPE default NULL
160 ,p_attribute15 IN pa_budget_versions.attribute15%TYPE default NULL
161 -- End of additional columns for Bug :- 3088010
162 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
163 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
164 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
165
166 /* added for financial planning needs*/
167
168 procedure Create_Org_Fcst_Elements (
169 p_project_id IN pa_projects_all.project_id%TYPE,
170 p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
171 p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
172 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
173 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
174 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
175
176 PROCEDURE Create_Version (
177 p_project_id IN NUMBER
178 ,p_fin_plan_type_id IN NUMBER
179 ,p_element_type IN VARCHAR2
180 ,p_version_name IN VARCHAR2
181 ,p_description IN VARCHAR2
182 -- Start of additional columns for Bug :- 2634900
183 ,p_ci_id IN pa_budget_versions.ci_id%TYPE := NULL
184 ,p_est_proj_raw_cost IN pa_budget_versions.est_project_raw_cost%TYPE := NULL
185 ,p_est_proj_bd_cost IN pa_budget_versions.est_project_burdened_cost%TYPE:= NULL
186 ,p_est_proj_revenue IN pa_budget_versions.est_project_revenue%TYPE := NULL
187 ,p_est_qty IN pa_budget_versions.est_quantity%TYPE := NULL
188 ,p_est_equip_qty IN pa_budget_versions.est_equipment_quantity%TYPE := NULL
189 ,p_impacted_task_id IN pa_tasks.task_id%TYPE := NULL
190 ,p_agreement_id IN pa_budget_versions.agreement_id%TYPE := NULL
191 ,p_calling_context IN VARCHAR2 := NULL
192 -- End of additional columns for Bug :- 2634900
193 -- Start of additional columns for Bug :- 2649474
194 ,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE := NULL
195 ,p_time_phased_code IN pa_proj_fp_options.cost_time_phased_code%TYPE := NULL
196 ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE := NULL
197 ,p_plan_in_multi_curr_flag IN pa_proj_fp_options.plan_in_multi_curr_flag%TYPE := NULL
198 ,p_amount_set_id IN pa_proj_fp_options.cost_amount_set_id%TYPE := NULL
199 -- End of additional columns for Bug :- 2649474
200 -- Start of additional columns for Bug :- 3088010
201 ,p_attribute_category IN pa_budget_versions.attribute_category%TYPE default NULL
202 ,p_attribute1 IN pa_budget_versions.attribute1%TYPE default NULL
203 ,p_attribute2 IN pa_budget_versions.attribute2%TYPE default NULL
204 ,p_attribute3 IN pa_budget_versions.attribute3%TYPE default NULL
205 ,p_attribute4 IN pa_budget_versions.attribute4%TYPE default NULL
206 ,p_attribute5 IN pa_budget_versions.attribute5%TYPE default NULL
207 ,p_attribute6 IN pa_budget_versions.attribute6%TYPE default NULL
208 ,p_attribute7 IN pa_budget_versions.attribute7%TYPE default NULL
209 ,p_attribute8 IN pa_budget_versions.attribute8%TYPE default NULL
210 ,p_attribute9 IN pa_budget_versions.attribute9%TYPE default NULL
211 ,p_attribute10 IN pa_budget_versions.attribute10%TYPE default NULL
212 ,p_attribute11 IN pa_budget_versions.attribute11%TYPE default NULL
213 ,p_attribute12 IN pa_budget_versions.attribute12%TYPE default NULL
214 ,p_attribute13 IN pa_budget_versions.attribute13%TYPE default NULL
215 ,p_attribute14 IN pa_budget_versions.attribute14%TYPE default NULL
219 ,p_struct_elem_version_id IN pa_proj_element_versions.element_version_id%TYPE default NULL -- BUG 3354518
216 ,p_attribute15 IN pa_budget_versions.attribute15%TYPE default NULL
217 -- End of additional columns for Bug :- 3088010
218 ,px_budget_version_id IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
220 ,p_pm_product_code IN pa_budget_versions.pm_product_code%TYPE DEFAULT NULL
221 ,p_finplan_reference IN pa_budget_versions.pm_budget_reference%TYPE DEFAULT NULL
222 ,p_change_reason_code IN pa_budget_versions.change_reason_code%TYPE DEFAULT NULL
223 ,p_pji_rollup_required IN VARCHAR2 DEFAULT 'Y' --Bug 4200168
224 ,x_proj_fp_option_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
225 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
226 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
227 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
228
229 /* added for financial planning needs*/
230
231 PROCEDURE Create_Fresh_Period_Profile(
232 p_project_id IN NUMBER
233 ,p_period_type IN VARCHAR2
234 ,x_period_profile_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
235 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
236 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
237 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
238
239 PROCEDURE Call_Maintain_Plan_Matrix (
240 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
241 ,p_data_source IN VARCHAR2
242 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
243 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
244 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
245
246 PROCEDURE INSERT_PLAN_LINES_TMP_BULK
247 (p_res_assignment_tbl IN p_res_assignment_tbl_typ
248 ,p_period_name_tbl IN p_period_name_tbl_typ
249 ,p_start_date_tbl IN p_start_date_tbl_typ
250 ,p_end_date_tbl IN p_end_date_tbl_typ
251 ,p_currency_type IN pa_proj_periods_denorm.currency_type%TYPE
252 ,p_currency_code_tbl IN p_currency_code_tbl_typ
253 ,p_quantity_tbl IN p_quantity_tbl_typ
254 ,p_raw_cost_tbl IN p_cost_tbl_typ
255 ,p_burdened_cost_tbl IN p_cost_tbl_typ
256 ,p_revenue_tbl IN p_cost_tbl_typ
257 ,p_old_quantity_tbl IN p_quantity_tbl_typ
258 ,p_old_raw_cost_tbl IN p_cost_tbl_typ
259 ,p_old_burdened_cost_tbl IN p_cost_tbl_typ
260 ,p_old_revenue_tbl IN p_cost_tbl_typ
261 ,p_margin_tbl IN p_cost_tbl_typ
262 ,p_margin_percent_tbl IN p_cost_tbl_typ
263 ,p_old_margin_tbl IN p_cost_tbl_typ
264 ,p_old_margin_percent_tbl IN p_cost_tbl_typ
265 ,p_buck_period_code_tbl IN p_buck_period_code_tbl_typ
266 ,p_parent_assignment_id_tbl IN p_res_assignment_tbl_typ
267 ,p_delete_flag_tbl IN p_delete_flag_tbl_typ
268 ,p_source_txn_curr_code_tbl IN p_currency_code_tbl_typ
269 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
270 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
271 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
272
273 PROCEDURE Refresh_res_list_assignment (
274 p_project_id IN pa_budget_versions.project_id%TYPE
275 ,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE
276 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
277 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
278 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
279
280 PROCEDURE create_default_plan_txn_rec
281 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
282 p_calling_module IN VARCHAR2,
283 p_ra_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(), /* 7161809 */
284 p_curr_code_tbl IN SYSTEM.PA_VARCHAR2_15_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_15_TBL_TYPE(), /* 7161809 */
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER,
287 x_msg_data OUT NOCOPY VARCHAR2);
288
289
290
291 END pa_fin_plan_pub;
[Home] [Help]