1 package PA_FP_ELEMENTS_PUB as
2 /* $Header: PAFPELPS.pls 120.1 2005/08/19 16:26:29 mwasowic noship $ */
3
4 Invalid_Arg_Exc EXCEPTION;
5
6 /* PLSQL record types */
7
8 TYPE l_impacted_task_in_rec_typ IS RECORD
9 (impacted_task_id pa_tasks.task_id%TYPE
10 ,old_parent_task_id pa_tasks.task_id%TYPE
11 ,new_parent_task_id pa_tasks.task_id%TYPE
12 ,top_task_id pa_tasks.task_id%TYPE
13 ,action VARCHAR2(30));
14
15 --For Bug 2976168.
16 TYPE l_wbs_refresh_tasks_rec_typ IS RECORD
17 ( task_id pa_tasks.task_id%TYPE
18 ,parent_task_id pa_tasks.parent_task_id%TYPE
19 ,top_task_id pa_tasks.top_task_id%TYPE
20 ,task_level VARCHAR2(1) -- T - Top Task, M - Middle Task, L - Lowest Task
21 );
22
23 /* PLSQL table types */
24
25 TYPE l_wbs_refresh_tasks_tbl_typ is TABLE OF
26 l_wbs_refresh_tasks_rec_typ INDEX BY BINARY_INTEGER;--For Bug 2976168.
27
28 TYPE l_task_id_tbl_typ IS TABLE OF
29 pa_fp_elements.TASK_ID%TYPE INDEX BY BINARY_INTEGER;
30 TYPE l_top_task_id_tbl_typ IS TABLE OF
31 pa_fp_elements.TOP_TASK_ID%TYPE INDEX BY BINARY_INTEGER;
32 TYPE l_res_list_mem_id_tbl_typ IS TABLE OF
33 pa_fp_elements.RESOURCE_LIST_MEMBER_ID%TYPE INDEX BY BINARY_INTEGER;
34 TYPE l_task_planning_level_tbl_typ IS TABLE OF
35 pa_fp_elements.TOP_TASK_PLANNING_LEVEL%TYPE INDEX BY BINARY_INTEGER;
36 TYPE l_track_as_labor_flag_tbl_typ IS TABLE OF
37 pa_resource_assignments.TRACK_AS_LABOR_FLAG%TYPE INDEX BY BINARY_INTEGER;
38 TYPE l_unit_of_measure_tbl_typ IS TABLE OF
39 pa_resource_assignments.UNIT_OF_MEASURE%TYPE INDEX BY BINARY_INTEGER;
40 TYPE l_res_planning_level_tbl_typ IS TABLE OF
41 pa_fp_elements.RESOURCE_PLANNING_LEVEL%TYPE INDEX BY BINARY_INTEGER;
42 TYPE l_plannable_flag_tbl_typ IS TABLE OF
43 pa_fp_elements.PLANNABLE_FLAG%TYPE INDEX BY BINARY_INTEGER;
44 TYPE l_res_planned_for_task_tbl_typ IS TABLE OF
45 pa_fp_elements.RESOURCES_PLANNED_FOR_TASK%TYPE INDEX BY BINARY_INTEGER;
46 TYPE l_planamount_exists_tbl_typ IS TABLE OF
47 pa_fp_elements.PLAN_AMOUNT_EXISTS_FLAG%TYPE INDEX BY BINARY_INTEGER;
48 TYPE l_impacted_task_in_tbl_typ IS TABLE OF
49 l_impacted_task_in_rec_typ INDEX BY BINARY_INTEGER;
50
51 /* PLSQL table types */
52
53
54
55 PROCEDURE Refresh_FP_Elements (
56 p_proj_fp_options_id IN NUMBER
57 ,p_cost_planning_level IN VARCHAR2
58 ,p_revenue_planning_level IN VARCHAR2
59 ,p_all_planning_level IN VARCHAR2
60 ,p_cost_resource_list_id IN NUMBER
61 ,p_revenue_resource_list_id IN NUMBER
62 ,p_all_resource_list_id IN NUMBER
63 /* Bug :- 2920954 start of new parameters added for post fp-K one off patch */
64 ,p_select_cost_res_auto_flag IN pa_proj_fp_options.select_cost_res_auto_flag%TYPE
65 ,p_cost_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
66 ,p_select_rev_res_auto_flag IN pa_proj_fp_options.select_rev_res_auto_flag%TYPE
67 ,p_revenue_res_planning_level IN pa_proj_fp_options.revenue_res_planning_level%TYPE
68 ,p_select_all_res_auto_flag IN pa_proj_fp_options.select_all_res_auto_flag%TYPE
69 ,p_all_res_planning_level IN pa_proj_fp_options.all_res_planning_level%TYPE
70 /* Bug :- 2920954 end of new parameters added for post fp-K one off patch */
71 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
72 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
73 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
74
75 PROCEDURE Copy_Elements (
76 p_from_proj_fp_options_id IN NUMBER
77 ,p_from_element_type IN VARCHAR2
78 ,p_to_proj_fp_options_id IN NUMBER
79 ,p_to_element_type IN VARCHAR2
80 ,p_to_resource_list_id IN NUMBER
81 ,p_copy_mode IN VARCHAR2 DEFAULT 'W' /* Bug 2920954 */
82 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
83 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
84 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
85
86 PROCEDURE Insert_Default (
87 p_proj_fp_options_id IN NUMBER
88 ,p_element_type IN VARCHAR2
89 ,p_planning_level IN VARCHAR2
90 ,p_resource_list_id IN NUMBER
91 /* Bug :- 2920954 start of parameters added for post fp-K one off patch */
92 ,p_select_res_auto_flag IN pa_proj_fp_options.select_cost_res_auto_flag%TYPE
93 ,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
94 /* Bug :- 2920954 end of parameters added for post fp-K one off patch */
95 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
96 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
97 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
98
99 PROCEDURE Delete_Elements (
100 p_proj_fp_options_id IN NUMBER
101 ,p_element_type IN VARCHAR2
102 ,p_element_level IN VARCHAR2
103 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
104 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
105 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
106
107 PROCEDURE Delete_Element (
108 p_task_id IN NUMBER
109 ,p_resource_list_member_id IN NUMBER
110 ,p_budget_version_id IN NUMBER
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 Insert_Bulk_Rows (
117 p_proj_fp_options_id IN NUMBER
118 ,p_project_id IN NUMBER
119 ,p_fin_plan_type_id IN NUMBER
120 ,p_element_type IN VARCHAR2
121 ,p_plan_version_id IN NUMBER
122 ,p_task_id_tbl IN l_task_id_tbl_typ
123 ,p_top_task_id_tbl IN l_top_task_id_tbl_typ
124 ,p_res_list_mem_id_tbl IN l_res_list_mem_id_tbl_typ
125 ,p_task_planning_level_tbl IN l_task_planning_level_tbl_typ
126 ,p_res_planning_level_tbl IN l_res_planning_level_tbl_typ
127 ,p_plannable_flag_tbl IN l_plannable_flag_tbl_typ
128 ,p_res_planned_for_task_tbl IN l_res_planned_for_task_tbl_typ
129 ,p_planamount_exists_tbl IN l_planamount_exists_tbl_typ
130 ,p_res_uncategorized_flag IN VARCHAR2
131 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
132 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
133 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
134
135 PROCEDURE Insert_Bulk_Rows_Res (
136 p_project_id IN NUMBER
137 ,p_plan_version_id IN NUMBER
138 ,p_task_id_tbl IN l_task_id_tbl_typ
139 ,p_res_list_mem_id_tbl IN l_res_list_mem_id_tbl_typ
140 ,p_unit_of_measure_tbl IN l_unit_of_measure_tbl_typ
141 ,p_track_as_labor_flag_tbl IN l_track_as_labor_flag_tbl_typ
142 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
143 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
144 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
145
146 PROCEDURE Create_Enterable_Resources (
147 p_plan_version_id IN NUMBER
148 ,p_task_id IN pa_tasks.task_id%TYPE DEFAULT NULL
149 ,p_res_del_req_flag IN VARCHAR2 DEFAULT 'Y'
150 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
151 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
152 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
153
154 FUNCTION get_element_id (
155 p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
156 ,p_element_type IN pa_fp_elements.element_type%TYPE
157 ,p_task_id IN pa_tasks.task_id%TYPE
158 ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
159 RETURN pa_fp_elements.proj_fp_elements_id%TYPE;
160
161 FUNCTION get_element_plannable_flag (
162 p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
163 ,p_element_type IN pa_fp_elements.element_type%TYPE
164 ,p_task_id IN pa_tasks.task_id%TYPE
165 ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
166 RETURN pa_fp_elements.plannable_flag%TYPE;
167
168 FUNCTION get_plan_amount_exists_flag (
169 p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
170 ,p_element_type IN pa_fp_elements.element_type%TYPE
171 ,p_task_id IN pa_tasks.task_id%TYPE
172 ,p_resource_list_member_id IN pa_resource_list_members.resource_list_member_id%TYPE)
173 RETURN pa_fp_elements.plan_amount_exists_flag%TYPE;
174
175 /*=================================================================================
176 The follwing function has been created to get resource_planning_level value in
177 Create_elements_from_version api.
178 =================================================================================*/
179 FUNCTION get_resource_planning_level(
180 p_parent_member_id IN pa_resource_list_members.parent_member_id%TYPE
181 ,p_uncategorized_flag IN pa_resource_lists.uncategorized_flag%TYPE
182 ,p_grouped_flag IN VARCHAR2)
183 RETURN pa_fp_elements.resource_planning_level%TYPE;
184
185 PROCEDURE Create_elements_from_version(
186 p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
187 ,p_element_type IN pa_fp_elements.element_type%TYPE
188 ,p_from_version_id IN pa_budget_versions.budget_version_id%TYPE
189 ,p_resource_list_id IN pa_budget_versions.resource_list_id%TYPE
190 ,x_mixed_resource_planned_flag OUT NOCOPY VARCHAR2 -- new parameter for Bug :- 2625872 --File.Sql.39 bug 4440895
191 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
192 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
193 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
194
195
196 PROCEDURE refresh_res_list_changes (
197 p_proj_fp_options_id IN PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE
198 ,p_element_type IN PA_FP_ELEMENTS.ELEMENT_TYPE%TYPE /* COST,REVENUE,ALL,BOTH */
199 ,p_cost_resource_list_id IN PA_PROJ_FP_OPTIONS.COST_RESOURCE_LIST_ID%TYPE
200 ,p_rev_resource_list_id IN PA_PROJ_FP_OPTIONS.REVENUE_RESOURCE_LIST_ID%TYPE
201 ,p_all_resource_list_id IN PA_PROJ_FP_OPTIONS.ALL_RESOURCE_LIST_ID%TYPE
202 /* Bug :- 2920954 start of new parameters added for post fp-K one off patch */
203 ,p_select_cost_res_auto_flag IN pa_proj_fp_options.select_cost_res_auto_flag%TYPE
204 ,p_cost_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
205 ,p_select_rev_res_auto_flag IN pa_proj_fp_options.select_rev_res_auto_flag%TYPE
206 ,p_revenue_res_planning_level IN pa_proj_fp_options.revenue_res_planning_level%TYPE
207 ,p_select_all_res_auto_flag IN pa_proj_fp_options.select_all_res_auto_flag%TYPE
208 ,p_all_res_planning_level IN pa_proj_fp_options.all_res_planning_level%TYPE
209 /* Bug :- 2920954 end of new parameters added for post fp-K one off patch */
210 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
211 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
212 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
213
214 PROCEDURE create_assgmt_from_rolluptmp
215 ( p_fin_plan_version_id IN pa_budget_versions.budget_version_id%TYPE
216 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
217 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
218 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
219
220 PROCEDURE Create_CI_Resource_Assignments
221 ( p_project_id IN pa_budget_versions.project_id%TYPE
222 ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
223 ,p_version_type IN pa_budget_versions.version_type%TYPE
224 ,p_impacted_task_id IN pa_resource_assignments.task_id%TYPE
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 PROCEDURE Add_resources_automatically
230 ( p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
234 ,p_res_planning_level IN pa_proj_fp_options.cost_res_planning_level%TYPE
231 ,p_element_type IN pa_fp_elements.element_type%TYPE
232 ,p_fin_plan_level_code IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
233 ,p_resource_list_id IN pa_resource_lists_all_bg.resource_list_id%TYPE
235 ,p_entire_option IN VARCHAR2
236 ,p_element_task_id_tbl IN pa_fp_elements_pub.l_task_id_tbl_typ
237 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
238 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
239 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
240
241 PROCEDURE Delete_task_elements
242 ( p_task_id IN pa_tasks.task_id%TYPE
243 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
244 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
245 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
246
247 PROCEDURE maintain_plannable_tasks
248 (p_project_id IN pa_projects_all.project_id%TYPE
249 ,p_impacted_tasks_tbl IN l_impacted_task_in_tbl_typ
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
254 PROCEDURE make_new_tasks_plannable
255 ( p_project_id IN pa_projects_all.project_id%TYPE
256 ,p_tasks_tbl IN pa_fp_elements_pub.l_wbs_refresh_tasks_tbl_typ
257 ,p_refresh_fp_options_tbl IN PA_PLSQL_DATATYPES.IdTabTyp
258 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
259 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
260 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
261
262
263
264 end PA_FP_ELEMENTS_PUB;
265
[Home] [Help]