1 PACKAGE pa_fp_webadi_pkg AUTHID CURRENT_USER as
2 /* $Header: PAFPWAPS.pls 120.8.12020000.2 2013/04/12 08:58:19 bpottipa ship $ */
3
4 /* PL/SQL table type declaration */
5
6 TYPE l_budget_line_id_tbl_typ IS TABLE OF
7 pa_budget_lines.BUDGET_LINE_ID%TYPE INDEX BY BINARY_INTEGER ;
8 TYPE l_budget_version_id_tbl_typ IS TABLE OF
9 pa_budget_lines.BUDGET_VERSION_ID%TYPE INDEX BY BINARY_INTEGER ;
10 TYPE l_project_id_tbl_typ IS TABLE OF
11 pa_resource_assignments.PROJECT_ID%TYPE INDEX BY BINARY_INTEGER ;
12 TYPE l_res_assignment_id_tbl_typ IS TABLE OF
13 pa_resource_assignments.RESOURCE_ASSIGNMENT_ID%TYPE INDEX BY BINARY_INTEGER ;
14 TYPE l_parent_assign_id_tbl_typ IS TABLE OF
15 pa_resource_assignments.PARENT_ASSIGNMENT_ID%TYPE INDEX BY BINARY_INTEGER ;
16 TYPE l_res_group_name_tbl_typ IS TABLE OF
17 pa_resource_list_members.ALIAS%TYPE INDEX BY BINARY_INTEGER ;
18 TYPE l_res_list_member_id_tbl_typ IS TABLE OF
19 pa_resource_list_members.resource_list_member_id%TYPE INDEX BY BINARY_INTEGER ;
20 TYPE l_resource_id_tbl_typ IS TABLE OF
21 pa_resource_list_members.resource_id%TYPE INDEX BY BINARY_INTEGER ;
22 TYPE l_task_id_tbl_typ IS TABLE OF
23 pa_tasks.task_id%TYPE INDEX BY BINARY_INTEGER ;
24 TYPE l_res_alias_tbl_typ IS TABLE OF
25 pa_resource_list_members.ALIAS%TYPE INDEX BY BINARY_INTEGER ;
26 TYPE l_task_number_tbl_typ IS TABLE OF
27 pa_tasks.TASK_NUMBER%TYPE INDEX BY BINARY_INTEGER ;
28 TYPE l_start_date_tbl_typ IS TABLE OF
29 pa_budget_lines.START_DATE%TYPE INDEX BY BINARY_INTEGER ;
30 TYPE l_end_date_tbl_typ IS TABLE OF
31 pa_budget_lines.END_DATE%TYPE INDEX BY BINARY_INTEGER ;
32 TYPE l_amount_tbl_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
33 TYPE l_burdened_cost_tbl_typ IS TABLE OF
34 pa_budget_lines.BURDENED_COST%TYPE INDEX BY BINARY_INTEGER ;
35 TYPE l_revenue_tbl_typ IS TABLE OF
36 pa_budget_lines.REVENUE%TYPE INDEX BY BINARY_INTEGER ;
37 TYPE l_quantity_tbl_typ IS TABLE OF
38 pa_budget_lines.QUANTITY%TYPE INDEX BY BINARY_INTEGER ;
39 TYPE l_txn_currency_code_tbl_typ IS TABLE OF
40 pa_budget_lines.TXN_CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER ;
41 TYPE l_delete_flag_tbl_typ IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER ;
42 TYPE l_unit_of_measure_tbl_typ IS TABLE OF
43 pa_resource_assignments.UNIT_OF_MEASURE%TYPE INDEX BY BINARY_INTEGER ;
44 TYPE l_description_tbl_typ IS TABLE OF
45 pa_budget_lines.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER ;
46 TYPE l_change_reason_code_tbl_typ IS TABLE OF
47 pa_budget_lines.PERIOD_NAME%TYPE INDEX BY BINARY_INTEGER ;
48 TYPE l_meaning_tbl_typ IS TABLE OF
49 pa_lookups.meaning%TYPE INDEX BY BINARY_INTEGER ;
50
51 TYPE l_pf_cost_rate_type_tbl_typ IS TABLE OF
52 pa_budget_lines.PROJFUNC_COST_RATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
53 TYPE l_pf_cost_rt_dt_type_tbl_typ IS TABLE OF
54 pa_budget_lines.PROJFUNC_COST_RATE_DATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
55 TYPE l_pf_cost_exc_rate_tbl_typ IS TABLE OF
56 pa_budget_lines.PROJFUNC_COST_EXCHANGE_RATE%TYPE INDEX BY BINARY_INTEGER ;
57 TYPE l_pf_cost_rate_date_tbl_typ IS TABLE OF
58 pa_budget_lines.PROJFUNC_COST_RATE_DATE%TYPE INDEX BY BINARY_INTEGER ;
59
60 TYPE l_proj_cost_rate_type_tbl_typ IS TABLE OF
61 pa_budget_lines.PROJECT_COST_RATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
62 TYPE l_proj_cost_rt_dt_type_tbl_typ IS TABLE OF
63 pa_budget_lines.PROJECT_COST_RATE_DATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
64 TYPE l_proj_cost_exc_rate_tbl_typ IS TABLE OF
65 pa_budget_lines.PROJECT_COST_EXCHANGE_RATE%TYPE INDEX BY BINARY_INTEGER ;
66 TYPE l_proj_cost_rate_date_tbl_typ IS TABLE OF
67 pa_budget_lines.PROJECT_COST_RATE_DATE%TYPE INDEX BY BINARY_INTEGER ;
68
69 TYPE l_pf_rev_rate_type_tbl_typ IS TABLE OF
70 pa_budget_lines.PROJFUNC_REV_RATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
71 TYPE l_pf_rev_rt_dt_type_tbl_typ IS TABLE OF
72 pa_budget_lines.PROJFUNC_REV_RATE_DATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
73 TYPE l_pf_rev_exc_rate_tbl_typ IS TABLE OF
74 pa_budget_lines.PROJFUNC_REV_EXCHANGE_RATE%TYPE INDEX BY BINARY_INTEGER ;
75 TYPE l_pf_rev_rate_date_tbl_typ IS TABLE OF
76 pa_budget_lines.PROJFUNC_REV_RATE_DATE%TYPE INDEX BY BINARY_INTEGER ;
77
78 TYPE l_proj_rev_rate_type_tbl_typ IS TABLE OF
79 pa_budget_lines.PROJECT_REV_RATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
80 TYPE l_proj_rev_rt_dt_type_tbl_typ IS TABLE OF
81 pa_budget_lines.PROJECT_REV_RATE_DATE_TYPE%TYPE INDEX BY BINARY_INTEGER ;
82 TYPE l_proj_rev_exc_rate_tbl_typ IS TABLE OF
83 pa_budget_lines.PROJECT_REV_EXCHANGE_RATE%TYPE INDEX BY BINARY_INTEGER ;
84 TYPE l_proj_rev_rate_date_tbl_typ IS TABLE OF
85 pa_budget_lines.PROJECT_REV_RATE_DATE%TYPE INDEX BY BINARY_INTEGER ;
86
87 TYPE l_proj_currency_code_tbl_typ IS TABLE OF
88 pa_budget_lines.PROJECT_CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER ;
89 TYPE l_pf_currency_code_tbl_typ IS TABLE OF
90 pa_budget_lines.PROJFUNC_CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER ;
91
92
93 TYPE l_attribute_category_tbl_typ IS TABLE OF
94 pa_budget_lines.ATTRIBUTE_CATEGORY%TYPE INDEX BY BINARY_INTEGER ;
95 TYPE l_attribute_tbl_typ IS TABLE OF
96 pa_budget_lines.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER ;
97
98 TYPE l_error_flag_tbl_typ IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER ;
99 TYPE l_val_error_code_tbl_typ IS TABLE OF
100 pa_lookups.LOOKUP_CODE%TYPE INDEX BY BINARY_INTEGER ;
101
102 TYPE l_period_name_tbl_typ IS TABLE OF
103 pa_budget_lines.PERIOD_NAME%TYPE INDEX BY BINARY_INTEGER ;
104 TYPE l_bucketing_pd_code_tbl_typ IS TABLE OF
105 pa_budget_lines.BUCKETING_PERIOD_CODE%TYPE INDEX BY BINARY_INTEGER ;
106 TYPE l_pm_product_code_tbl_typ IS TABLE OF
107 pa_budget_lines.PM_PRODUCT_CODE%TYPE INDEX BY BINARY_INTEGER ;
108
109 TYPE l_raw_cost_source_tbl_typ IS TABLE OF
110 pa_budget_lines.RAW_COST_SOURCE%TYPE INDEX BY BINARY_INTEGER ;
111 TYPE l_burdened_cost_source_tbl_typ IS TABLE OF
112 pa_budget_lines.BURDENED_COST_SOURCE%TYPE INDEX BY BINARY_INTEGER ;
113 TYPE l_quantity_source_tbl_typ IS TABLE OF
114 pa_budget_lines.QUANTITY%TYPE INDEX BY BINARY_INTEGER ;
115 TYPE l_revenue_source_tbl_typ IS TABLE OF
116 pa_budget_lines.QUANTITY%TYPE INDEX BY BINARY_INTEGER ;
117
118 /* Bug 4431269: Added the record type gloabl variable.
119 * Using the global variable for the calling reference makes
120 * code changes and the subsequent impact very less as this variable would be only populated
121 * in excel upload flow only before calling calculate api and would be cleared away once the call
122 * to calculate api is over. This approach also get rid of the problem of introducing any new additional
123 * parameter to calculate api.
124
125 * The valid value for the attributes G_FP_WA_CALC_CALLING_CONTEXT is 'WEBADI_CALCULATE'
126 * If calculate api throws some validation error, then if the global variable has the value of
127 * 'WEBADI_CALCULATE', then a call to process_errors would be made to update the interface table
128 * with appropriate error code for the validation failure agains the corresponding invalid records.
129 */
130
131 G_FP_WA_CALC_CALLING_CONTEXT VARCHAR2(30);
132
133 TYPE G_FP_WA_GLOBAL_VAR_REC IS RECORD
134 (
135 task_id pa_resource_assignments.task_id%TYPE,
136 rlm_id pa_resource_assignments.resource_list_member_id%TYPE,
137 txn_currency pa_budget_lines.txn_currency_code%TYPE,
138 error_code pa_fp_webadi_upload_inf.val_error_code%TYPE
139 );
140
141 TYPE G_FP_WEBADI_GLOBAL IS TABLE OF G_FP_WA_GLOBAL_VAR_REC;
142
143 g_fp_webadi_rec_tbl G_FP_WEBADI_GLOBAL := G_FP_WEBADI_GLOBAL();
144
145 /* Start of changes done for Bug : 4584865*/
146
147 --Cursor based on Global Temporay Table pa_fp_webadi_xface_tmp.
148 CURSOR global_tmp_cur
149 IS
150 SELECT * FROM pa_fp_webadi_xface_tmp;
151 --Record based on Global Temporay Table pa_fp_webadi_xface_tmp.
152 -- l_global_tmp_rec pa_fp_webadi_xface_tmp%ROWTYPE; --Bug 5284640.
153 --Table based on Cursor global_tmp_cur.
154 TYPE global_tmp_tbl IS TABLE OF global_tmp_cur%ROWTYPE;
155 l_global_tmp_tbl global_tmp_tbl;
156 -- Bug 5284640 : Commented out the below variables.
157 /*
158 l_position NUMBER := 0;
159 l_return NUMBER := 0;
160 */
161 /*End of Changes done for Bug : 4584865*/
162
163 PROCEDURE delete_xface
164 ( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE
165 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
166 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
167 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
168 ,p_calling_module IN VARCHAR2 DEFAULT NULL
169 );
170
171 -- Bug 3986129: FP.M Web ADI Dev changes. Added the following procedures
172 Procedure validate_header_info
173 ( p_calling_mode IN VARCHAR2 DEFAULT NULL,
174 p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
175 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
176 p_record_version_number IN pa_budget_versions.record_version_number%TYPE,
177 p_pm_rec_version_number IN pa_period_masks_b.record_version_number%TYPE,
178 p_submit_flag IN VARCHAR2,
179 p_request_id IN pa_budget_versions.request_id%TYPE,
180 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
181 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
182 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
183
184 PROCEDURE process_errors
185 ( p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE ,
186 p_context IN VARCHAR2 DEFAULT NULL,
187 p_periodic_flag IN VARCHAR2 DEFAULT NULL,
188 p_error_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
189 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
190 p_rlm_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
191 p_txn_curr_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
192 p_amount_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE DEFAULT SYSTEM.PA_VARCHAR2_30_TBL_TYPE(),
193 p_request_id IN pa_budget_versions.request_id%TYPE DEFAULT NULL,
194 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
195 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
196 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
197
198 PROCEDURE process_budget_lines
199 ( p_context IN VARCHAR2,
200 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
201 p_version_info_rec IN pa_fp_gen_amount_utils.fp_cols,
202 p_task_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
203 p_rlm_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
204 p_cbs_element_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),-- cbs enhancement changes bug#16445106
205 p_ra_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
206 p_spread_curve_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
207 p_mfc_cost_type_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
208 p_etc_method_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
209 p_sp_fixed_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
210 p_res_class_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
211 p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type(),
212 p_rbs_elem_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
213 p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type(),
214 p_planning_start_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
215 p_planning_end_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
216 p_total_qty_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
217 p_total_raw_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
218 p_total_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
219 p_total_revenue_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
220 p_raw_cost_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
221 p_burdened_cost_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
222 p_bill_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
223 p_line_start_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
224 p_line_end_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
225 p_proj_cost_rate_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
226 p_proj_cost_rate_date_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
227 p_proj_cost_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
228 p_proj_cost_rate_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
229 p_proj_rev_rate_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
230 p_proj_rev_rate_date_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
231 p_proj_rev_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
232 p_proj_rev_rate_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
233 p_pfunc_cost_rate_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
234 p_pfunc_cost_rate_date_typ_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
235 p_pfunc_cost_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
236 p_pfunc_cost_rate_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
237 p_pfunc_rev_rate_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
238 p_pfunc_rev_rate_date_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
239 p_pfunc_rev_rate_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type(),
240 p_pfunc_rev_rate_date_tbl IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type(),
241 p_change_reason_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type DEFAULT SYSTEM.pa_varchar2_30_tbl_type(),
242 p_description_tbl IN SYSTEM.pa_varchar2_2000_tbl_type DEFAULT SYSTEM.pa_varchar2_2000_tbl_type(),
243 p_delete_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type(),
244 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
245 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
246 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
247
248
249 PROCEDURE switcher
250 (x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
251 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
252 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
253 p_submit_budget_flag IN VARCHAR2 DEFAULT 'N',
254 p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE,
255 x_success_msg OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
256 p_submit_forecast_flag IN VARCHAR2 DEFAULT 'N',
257 p_request_id IN pa_budget_versions.request_id%TYPE DEFAULT NULL,
258 p_calling_mode IN VARCHAR2 DEFAULT 'STANDARD'
259 );
260
261 -- Bug 3986129: FP.M Web ADI Dev changes
262
263 --This API will be called when thru the concurrent request that will be used to upload MS excel data to
264 --Oracle Applications.
265 PROCEDURE process_MSExcel_data
266 (errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
267 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
268 p_submit_ver_flag IN VARCHAR2,
269 p_run_id IN pa_fp_webadi_upload_inf.run_id%TYPE);
270
271 --Bug 4584865.
272 --This API is called to insert records into pa_fp_webadi_xface_tmp
273 --during downloading budget line details into excel spreadsheet.
274 PROCEDURE insert_periodic_tmp_table(p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2);
278
279 END pa_fp_webadi_pkg;
[Home] [Help]