1 PACKAGE PA_FP_WEBADI_UTILS as
2 /* $Header: PAFPWAUS.pls 120.5 2007/02/06 10:17:33 dthakker noship $ */
3
4 PROCEDURE GET_METADATA_INFO
5 ( p_budget_version_id IN NUMBER
6 ,x_content_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
7 ,x_mapping_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8 ,x_layout_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
9 ,x_integrator_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
10 ,x_rej_lines_exist OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
11 ,x_submit_budget OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
12 ,x_submit_forecast OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
13 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
14 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
15 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
16 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17 );
18
19 PROCEDURE VALIDATE_BEFORE_LAUNCH
20 ( p_budget_version_id IN NUMBER
21 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
22 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
23 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
24 );
25
26 PROCEDURE CONVERT_TASK_NUM_TO_ID
27 ( p_project_id IN NUMBER
28 ,p_task_num IN VARCHAR2
29 ,x_task_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
30 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
31 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
32 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
33 );
34
35 PROCEDURE VALIDATE_CURRENCY_CODE
36 (p_budget_version_id IN NUMBER
37 ,p_currency_code IN VARCHAR2
38 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
39 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
40 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
41 );
42
43 PROCEDURE VALIDATE_RESOURCE_INFO
44 (p_budget_version_id IN NUMBER
45 ,p_resource_group_name IN VARCHAR2
46 ,p_resource_alias IN VARCHAR2
47 ,x_resource_list_member_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
48 ,x_resource_gp_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
49 ,x_resource_alias_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
50 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
51 );
52
53 PROCEDURE GET_RES_ASSIGNMENT_INFO
54 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
55 ,p_planning_level IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
56 ,x_task_number OUT NOCOPY pa_tasks.task_number%TYPE --File.Sql.39 bug 4440895
57 ,x_task_id OUT NOCOPY pa_tasks.task_id%TYPE --File.Sql.39 bug 4440895
58 ,x_resource_alias OUT NOCOPY pa_resource_list_members.alias%TYPE --File.Sql.39 bug 4440895
59 ,x_resource_group_alias OUT NOCOPY pa_resource_list_members.alias%TYPE --File.Sql.39 bug 4440895
60 ,x_parent_assignment_id OUT NOCOPY pa_resource_assignments.parent_assignment_id%TYPE --File.Sql.39 bug 4440895
61 ,x_resource_list_member_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE --File.Sql.39 bug 4440895
62 ,x_resource_id OUT NOCOPY pa_resource_list_members.resource_id%TYPE --File.Sql.39 bug 4440895
63 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
64 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
65 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
66 ) ;
67 /* Bug 5350437: Commented the below API
68 PROCEDURE VALIDATE_CHANGE_REASON_CODE
69 (p_change_reason_code IN pa_budget_lines.change_reason_code%TYPE
70 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
71 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
72 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
73 );
74 */
75
76 PROCEDURE VALIDATE_TXN_CURRENCY_CODE
77 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
78 ,p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
79 ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
80 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
81 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
82 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
83 );
84 /* Bug 5350437: Commented the below API
85 PROCEDURE GET_VERSION_PERIODS_INFO
86 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
87 ,x_period_name_tbl OUT NOCOPY pa_fp_webadi_pkg.l_period_name_tbl_typ --File.Sql.39 bug 4440895
88 ,x_start_date_tbl OUT NOCOPY pa_fp_webadi_pkg.l_start_date_tbl_typ --File.Sql.39 bug 4440895
89 ,x_end_date_tbl OUT NOCOPY pa_fp_webadi_pkg.l_end_date_tbl_typ --File.Sql.39 bug 4440895
90 ,x_number_of_pds OUT NOCOPY pa_proj_period_profiles.number_of_periods%TYPE --File.Sql.39 bug 4440895
91 ,x_period_profile_id OUT NOCOPY pa_budget_versions.period_profile_id%TYPE --File.Sql.39 bug 4440895
92 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
93 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
94 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
95 ) ;
96 */
97
98 PROCEDURE CHECK_OVERLAPPING_DATES
99 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
100 ,x_rec_failed_validation OUT NOCOPY VARCHAR2 --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 GET_MC_ERROR_LOOKUP_CODE
107 (p_mc_error_code IN pa_lookups.lookup_code%TYPE
108 ,p_attr_set_cost_rev IN VARCHAR2
109 ,p_attr_set_pc_pfc IN VARCHAR2
110 ,x_error_lookup_code OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
111 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
112 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
113 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
114 );
115
116 PROCEDURE CONV_MC_ATTR_MEANING_TO_CODE
117 (p_pc_cost_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
118 ,p_pc_cost_rate_date_type_name IN pa_lookups.meaning%TYPE
119 ,p_pfc_cost_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
120 ,p_pfc_cost_rate_date_type_name IN pa_lookups.meaning%TYPE
121 ,p_pc_rev_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
122 ,p_pc_rev_rate_date_type_name IN pa_lookups.meaning%TYPE
123 ,p_pfc_rev_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
124 ,p_pfc_rev_rate_date_type_name IN pa_lookups.meaning%TYPE
125 ,x_pc_cost_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
126 ,x_pc_cost_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
127 ,x_pfc_cost_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
128 ,x_pfc_cost_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
129 ,x_pc_rev_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
130 ,x_pc_rev_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
131 ,x_pfc_rev_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
132 ,x_pfc_rev_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
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
138 FUNCTION GET_AMOUNT_TYPE_NAME (
139 p_amount_type_code IN PA_AMOUNT_TYPES_B.AMOUNT_TYPE_CODE%TYPE )
140 RETURN PA_AMOUNT_TYPES_VL.AMOUNT_TYPE_NAME%TYPE ;
141
142 /*==================================================================================
143 This procedure is used to get the layout name and the layout type code when the
144 layout type is passed.
145 06-Apr-2005 prachand Created as a part of WebAdi changes.
146 Initial Creation
147 ===================================================================================*/
148
149
150
151 PROCEDURE GET_LAYOUT_DETAILS
152 (p_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
153 ,p_integrator_code IN bne_integrators_b.integrator_code%TYPE
154 ,x_layout_name OUT NOCOPY bne_layouts_tl.user_name%TYPE --File.Sql.39 bug 4440895
155 ,x_layout_type_code OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
156 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
157 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
158 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
159 );
160
161 -- Bug 3986129: FP.M Web ADI Dev changes: Added the follwoing apis
162
163 -- This api would be called from a java method when the user wants to delete the data from the excel interface
164 -- that is downloaded for a session.
165
166 PROCEDURE delete_interface_tbl_data
167 (p_request_id IN pa_budget_versions.request_id%TYPE,
168 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
169 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
170 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
171
172 -- This api would be called from a java method when the user wants to resubmit the request for the concurrent
173 -- program, if the upload processing of the plan version fails for some reason.
174
175 PROCEDURE resubmit_conc_request
176 (p_old_request_id IN pa_budget_versions.request_id%TYPE,
177 x_new_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE, --File.Sql.39 bug 4440895
178 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
179 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
180 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
181
182 -- Bug 3986129: FP.M Web ADI Dev changes: Ends
183
184 -- Bug 3986129: FP.M Web ADI Dev changes: Added the follwoing apis
185 /* =================================================================================
186 This function is used is FPM's Budget and Forecasting webadi download query to get the period amounts
187 of the current baselined plan version
188 =======================================================================================*/
189 FUNCTION get_current_amount(
190 p_fin_plan_type_id NUMBER,
191 p_plan_class_code VARCHAR2,
192 p_project_id NUMBER,
193 p_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE,
194 p_task_id NUMBER,
195 p_resource_list_member_id NUMBER,
196 p_uom pa_resource_assignments.unit_of_measure%TYPE,
197 p_txn_curr_code pa_budget_lines.txn_currency_code%TYPE,
198 p_amount VARCHAR2)
199 RETURN NUMBER;
200
201
202 /* =================================================================================
203 This function is used is FPM's Budget and Forecasting webadi download query to
204 get the period amounts of the original baselined plan version
205 =======================================================================================*/
206 FUNCTION get_original_amount(
207 p_fin_plan_type_id NUMBER,
208 p_plan_class_code VARCHAR2,
209 p_project_id NUMBER,
210 p_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE,
211 p_task_id NUMBER,
212 p_resource_list_member_id NUMBER,
213 p_uom pa_resource_assignments.unit_of_measure%TYPE,
214 p_txn_curr_code pa_budget_lines.txn_currency_code%TYPE,
215 p_amount VARCHAR2)
216 RETURN NUMBER;
217
218 /* =================================================================================
219 This function is used is FPM's Budget and Forecasting webadi download query to
220 get the period amounts of the prior forecast plan version
221 =======================================================================================*/
222 FUNCTION get_prior_forecast_amount(
223 p_fin_plan_type_id NUMBER,
224 p_plan_class_code VARCHAR2,
225 p_project_id NUMBER,
226 p_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE,
227 p_task_id NUMBER,
228 p_resource_list_member_id NUMBER,
229 p_uom pa_resource_assignments.unit_of_measure%TYPE,
230 p_txn_curr_code pa_budget_lines.txn_currency_code%TYPE,
231 p_amount VARCHAR2)
232 RETURN NUMBER;
233
234
235 /* =================================================================================
236 This function is used is FPM's Budget and Forecasting webadi download query to get the
237 period amounts for the following amount types: RAW_COST_RATE,BURDENED_COST_RATE,BILL_RATE,
238 'TOTAL_QTY''FCST_QTY',TOTAL_RAW_COST,FCST_RAW_COST,TOTAL_REV,FCST_REV,TOTAL_BURDENED_COST,
239 FCST_BURDENED_COST,ACTUAL_QTY,ACTUAL_RAW_COST,ACTUAL_BURD_COST,ACTUAL_REVENUE,ETC_QTY,
240 ETC_RAW_COST,ETC_BURDENED_COST,ETC_REVENUE
241 =======================================================================================*/
242 FUNCTION get_period_amounts(
243 p_budget_version_id NUMBER,
244 p_amount_code VARCHAR2,
245 p_resource_assignment_id pa_budget_lines.resource_assignment_id%TYPE,
246 p_txn_currency_code pa_budget_lines.txn_currency_code%TYPE,
247 p_prd_start_date DATE,
248 p_prd_end_date DATE,
249 preceding_date DATE,
250 succedeing_date DATE)
251 RETURN NUMBER;
252 -- Bug 3986129: FP.M Web ADI Dev changes: Ends
253
254
255
256 END PA_FP_WEBADI_UTILS;
[Home] [Help]