1 PACKAGE PA_RES_ACCUMS AUTHID CURRENT_USER AS
2 /* $Header: PARESACS.pls 120.1 2005/08/19 16:50:41 mwasowic noship $ */
3
4 -- Standard who
5 x_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
6 x_last_update_date NUMBER(15) := FND_GLOBAL.USER_ID;
7 x_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
8 x_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
9 x_request_id NUMBER(15) := FND_GLOBAL.CONC_REQUEST_ID;
10 x_program_application_id NUMBER(15) := FND_GLOBAL.PROG_APPL_ID;
11 x_program_id NUMBER(15) := FND_GLOBAL.CONC_PROGRAM_ID;
12
13
14 TYPE resource_index IS RECORD
15 (resource_list_id NUMBER(15),
16 location NUMBER(15));
17
18 TYPE resource_index_tbl IS TABLE OF resource_index
19 INDEX BY BINARY_INTEGER;
20
21 TYPE resources_in_rec_type IS RECORD
22 (resource_list_assignment_id NUMBER(15),
23 resource_list_id NUMBER(15),
24 resource_list_member_id NUMBER(15),
25 member_level NUMBER(15),
26 resource_id NUMBER(15),
27 person_id NUMBER,
28 job_id NUMBER,
29 organization_id NUMBER,
30 vendor_id NUMBER,
31 expenditure_type VARCHAR2(30),
32 event_type VARCHAR2(30),
33 non_labor_resource VARCHAR2(20),
34 expenditure_category VARCHAR2(30),
35 revenue_category VARCHAR2(30),
36 non_labor_resource_org_id NUMBER,
37 event_type_classification VARCHAR2(30),
38 system_linkage_function VARCHAR2(30),
39 resource_format_id NUMBER(15),
40 resource_type_code VARCHAR2(20)
41 , job_group_id NUMBER
42 );
43
44 TYPE resources_tbl_type IS TABLE OF resources_in_rec_type
45 INDEX BY BINARY_INTEGER;
46
47 -- TYPE resource_list_asgn_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
48 -- TYPE resource_list_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
49 -- TYPE member_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
50 -- TYPE member_level_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
51 -- TYPE resource_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
52 -- TYPE person_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
53 -- TYPE job_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
54 -- TYPE organization_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
55 -- TYPE vendor_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
56 -- TYPE expenditure_type_tabtype IS TABLE OF
57 -- pa_txn_accum.expenditure_type%TYPE INDEX BY BINARY_INTEGER;
58 -- TYPE event_type_tabtype IS TABLE OF
59 -- pa_txn_accum.event_type%TYPE INDEX BY BINARY_INTEGER;
60 -- TYPE non_labor_resource_tabtype IS TABLE OF
61 -- pa_txn_accum.non_labor_resource%TYPE INDEX BY BINARY_INTEGER;
62 -- TYPE expenditure_category_tabtype IS TABLE OF
63 -- pa_txn_accum.expenditure_category%TYPE INDEX BY BINARY_INTEGER;
64 -- TYPE revenue_category_tabtype IS TABLE OF
65 -- pa_txn_accum.revenue_category%TYPE INDEX BY BINARY_INTEGER;
66 -- TYPE nlr_org_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
67 -- TYPE event_type_class_tabtype IS TABLE OF
68 -- pa_txn_accum.event_type_classification%TYPE INDEX BY BINARY_INTEGER;
69 -- TYPE system_linkage_tabtype IS TABLE OF
70 -- pa_txn_accum.system_linkage_function%TYPE INDEX BY BINARY_INTEGER;
71 -- TYPE resource_format_id_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
72 -- TYPE resource_type_code_tabtype IS TABLE OF
73 -- pa_resource_types.resource_type_code%TYPE INDEX BY BINARY_INTEGER;
74
75 FUNCTION Initialize RETURN NUMBER;
76
77 PROCEDURE get_resource_map
78 (x_resource_list_id IN NUMBER,
79 x_resource_list_assignment_id IN NUMBER,
80 x_person_id IN NUMBER,
81 x_job_id IN NUMBER,
82 x_organization_id IN NUMBER,
83 x_vendor_id IN NUMBER,
84 x_expenditure_type IN VARCHAR2,
85 x_event_type IN VARCHAR2,
86 x_non_labor_resource IN VARCHAR2,
87 x_expenditure_category IN VARCHAR2,
88 x_revenue_category IN VARCHAR2,
89 x_non_labor_resource_org_id IN NUMBER,
90 x_event_type_classification IN VARCHAR2,
91 x_system_linkage_function IN VARCHAR2,
92 x_resource_list_member_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
93 x_resource_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
94 x_resource_map_found IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
95 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
96 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
97
98 PROCEDURE get_resource_map_new
99 (x_resource_list_id IN NUMBER,
100 x_person_id IN NUMBER,
101 x_job_id IN NUMBER,
102 x_organization_id IN NUMBER,
103 x_vendor_id IN NUMBER,
104 x_expenditure_type IN VARCHAR2,
105 x_event_type IN VARCHAR2,
106 x_non_labor_resource IN VARCHAR2,
107 x_expenditure_category IN VARCHAR2,
108 x_revenue_category IN VARCHAR2,
109 x_non_labor_resource_org_id IN NUMBER,
110 x_event_type_classification IN VARCHAR2,
111 x_system_linkage_function IN VARCHAR2,
112 x_resource_list_member_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
113 x_resource_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
114 x_resource_map_found IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
115 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
116 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
117
118 -- deleting the resource maps
119
120 PROCEDURE delete_res_maps_on_asgn_id
121 (x_resource_list_assignment_id IN NUMBER,
122 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
123 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
124
125 PROCEDURE delete_res_maps_on_prj_id
126 (x_project_id IN NUMBER,
127 x_resource_list_id IN NUMBER,
128 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
129 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
130
131 -- the function given below creates a resource map
132
133 PROCEDURE create_resource_map
134 (x_resource_list_id IN NUMBER,
135 x_resource_list_assignment_id IN NUMBER,
136 x_resource_list_member_id IN NUMBER,
137 x_resource_id IN NUMBER,
138 x_person_id IN NUMBER,
139 x_job_id IN NUMBER,
140 x_organization_id IN NUMBER,
141 x_vendor_id IN NUMBER,
142 x_expenditure_type IN VARCHAR2,
143 x_event_type IN VARCHAR2,
144 x_non_labor_resource IN VARCHAR2,
145 x_expenditure_category IN VARCHAR2,
146 x_revenue_category IN VARCHAR2,
147 x_non_labor_resource_org_id IN NUMBER,
148 x_event_type_classification IN VARCHAR2,
149 x_system_linkage_function IN VARCHAR2,
150 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
151 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
152
153 -- change resource list assignment
154
155 PROCEDURE change_resource_list_status
156 (x_resource_list_assignment_id IN NUMBER,
157 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
158 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
159
160 FUNCTION get_resource_list_status
161 (x_resource_list_assignment_id IN NUMBER)
162 RETURN VARCHAR2 ;
163
164 FUNCTION get_resource_rank
165 (x_resource_format_id IN NUMBER,
166 x_txn_class_code IN VARCHAR2)
167 RETURN NUMBER ;
168
169 FUNCTION get_group_resource_type_code
170 (x_resource_list_id IN NUMBER)
171 RETURN VARCHAR2 ;
172
173 PROCEDURE create_resource_accum_details
174 (x_resource_list_id IN NUMBER,
175 x_resource_list_assignment_id IN NUMBER,
176 x_resource_list_member_id IN NUMBER,
177 x_resource_id IN NUMBER,
178 x_txn_accum_id IN NUMBER,
179 x_project_id IN NUMBER,
180 x_task_id IN NUMBER,
181 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
182 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
183
184 PROCEDURE delete_resource_accum_details
185 (x_resource_list_assignment_id IN NUMBER,
186 x_resource_list_id IN NUMBER,
187 x_project_id IN NUMBER,
188 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
189 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
190
191 PROCEDURE get_mappable_resources
192 (x_project_id IN NUMBER,
193 x_res_list_id IN NUMBER,
194 x_resource_ind IN OUT NOCOPY resource_index_tbl, /*Added nocopy for bug 2674619*/
195 x_resources_in IN OUT NOCOPY resources_tbl_type, /*Added nocopy for bug 2674619*/
196 x_no_of_resources IN OUT NOCOPY BINARY_INTEGER, --File.Sql.39 bug 4440895
197 x_index IN OUT NOCOPY BINARY_INTEGER, --File.Sql.39 bug 4440895
198 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
199 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
200
201 -- x_resource_list_id IN OUT resource_list_id_tabtype,
202 -- x_resource_list_assignment_id IN OUT resource_list_asgn_id_tabtype,
203 -- x_resource_list_member_id IN OUT member_id_tabtype,
204 -- x_resource_id IN OUT resource_id_tabtype,
205 -- x_member_level IN OUT member_level_tabtype,
206 -- x_person_id IN OUT person_id_tabtype,
207 -- x_job_id IN OUT job_id_tabtype,
208 -- x_organization_id IN OUT organization_id_tabtype,
209 -- x_vendor_id IN OUT vendor_id_tabtype,
210 -- x_expenditure_type IN OUT expenditure_type_tabtype,
211 -- x_event_type IN OUT event_type_tabtype,
212 -- x_non_labor_resource IN OUT non_labor_resource_tabtype,
213 -- x_expenditure_category IN OUT expenditure_category_tabtype,
214 -- x_revenue_category IN OUT revenue_category_tabtype,
215 -- x_non_labor_resource_org_id IN OUT nlr_org_id_tabtype,
216 -- x_event_type_classification IN OUT event_type_class_tabtype,
217 -- x_system_linkage_function IN OUT system_linkage_tabtype,
218 -- x_resource_format_id IN OUT resource_format_id_tabtype,
219 -- x_resource_type_code IN OUT resource_type_code_tabtype,
220
221 PROCEDURE old_map_txns
222 (x_project_id IN NUMBER,
223 x_res_list_id IN NUMBER,
224 x_mode IN VARCHAR2 DEFAULT 'I',
225 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
226 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
227
228 PROCEDURE map_txns
229 (x_project_id IN NUMBER,
230 x_res_list_id IN NUMBER,
231 x_mode IN VARCHAR2 DEFAULT 'I',
232 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
233 x_err_code IN OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
234
235 PROCEDURE new_map_txns /* Created for bug# 1889671 */
236 (x_resource_list_id IN NUMBER,
237 x_error_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
238 x_error_code OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
239
240 PROCEDURE update_parents_mem_id /* Created for bug# 1889671 */
241 (x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
242 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
243 x_err_code OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
244
245 PROCEDURE ins_temp_res_map_grp /* Created for bug# 1889671 */
246 (x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
247 x_rl_job_grp_id IN pa_resource_lists_all_bg.job_group_id%type,
248 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
249 x_err_code OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
250
251 PROCEDURE ins_temp_res_map_ungrp /* Created for bug# 1889671 */
252 (x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
253 x_rl_job_grp_id IN pa_resource_lists_all_bg.job_group_id%type,
254 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
255 x_err_code OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
256
257 END PA_RES_ACCUMS;