[Home] [Help]
PACKAGE: APPS.PA_RESOURCE_PVT
Source
1 PACKAGE PA_RESOURCE_PVT AUTHID CURRENT_USER AS
2 -- $Header: PARRESVS.pls 120.1 2005/08/19 17:00:10 mwasowic noship $
3 end_date DATE := null;
4 end_date1 DATE := null;
5 scale_type VARCHAR2(30) := null;
6 no_of_days NUMBER := 90;
7
8 g_manager_name VARCHAR2(100);
9 g_project_id NUMBER(15); /* Added this global parameter for bug#2604495 */
10 g_manager_resource_id NUMBER;
11
12 FUNCTION Get_Manager_Id(
13 P_PROJECT_ID IN NUMBER)
14 RETURN NUMBER;
15
16 FUNCTION Get_Manager_Name(p_project_id in number DEFAULT null) /* Added the parameter p_project_id for bug#2604495 */
17 RETURN VARCHAR2;
18
19 FUNCTION Get_Manager_Resource_Id
20 RETURN NUMBER;
21
22 Procedure Set_No_of_Days
23 (p_no_of_days IN NUMBER);
24
25 FUNCTION Get_Start_Date
26 (p_resource_id IN NUMBER,
27 p_no_of_days IN NUMBER)
28 RETURN
29 DATE;
30
31 FUNCTION Get_End_Date
32 (p_resource_id IN NUMBER)
33 RETURN
34 DATE;
35
36 FUNCTION Get_Start_Date1
37 (p_row_label_id IN NUMBER)
38 RETURN
39 DATE;
40
41 FUNCTION Get_End_Date1
42 RETURN
43 DATE;
44
45 FUNCTION get_scale_type
46 RETURN
47 VARCHAR2;
48
49
50 TYPE Resource_Denorm_Rec_Type
51 IS RECORD
52 ( person_id pa_resources_denorm.person_id%TYPE := FND_API.G_MISS_NUM,
53 resource_name pa_resources_denorm.resource_name%TYPE := FND_API.G_MISS_CHAR,
54 resource_type pa_resources_denorm.resource_type%TYPE := FND_API.G_MISS_CHAR,
55 resource_organization_id pa_resources_denorm.resource_organization_id%TYPE := FND_API.G_MISS_NUM,
56 resource_country_code pa_resources_denorm.resource_country_code%TYPE := FND_API.G_MISS_CHAR,
57 resource_country pa_resources_denorm.resource_country%TYPE := FND_API.G_MISS_CHAR,
58 resource_region pa_resources_denorm.resource_region%TYPE := FND_API.G_MISS_CHAR,
59 resource_city pa_resources_denorm.resource_city%TYPE := FND_API.G_MISS_CHAR,
60 job_id NUMBER := FND_API.G_MISS_NUM,
61 resource_job_level pa_resources_denorm.resource_job_level%TYPE := FND_API.G_MISS_NUM,
62 resource_effective_start_date pa_resources_denorm.resource_effective_start_date%TYPE := FND_API.G_MISS_DATE,
63 resource_effective_end_date pa_resources_denorm.resource_effective_end_date%TYPE := FND_API.G_MISS_DATE,
64 employee_flag pa_resources_denorm.employee_flag%TYPE := FND_API.G_MISS_CHAR,
65 manager_id NUMBER := FND_API.G_MISS_NUM,
66 manager_name pa_resources_denorm.manager_name%TYPE := FND_API.G_MISS_CHAR,
67 billable_flag pa_resources_denorm.billable_flag%TYPE := FND_API.G_MISS_CHAR,
68 utilization_flag pa_resources_denorm.utilization_flag%TYPE := FND_API.G_MISS_CHAR,
69 schedulable_flag pa_resources_denorm.schedulable_flag%TYPE := FND_API.G_MISS_CHAR,
70 resource_org_id pa_resources_denorm.resource_org_id%TYPE := FND_API.G_MISS_NUM);
71
72 Procedure Insert_resource_denorm
73 ( p_resource_denorm_rec IN Resource_denorm_Rec_type,
74 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
75 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
76 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
77
78 PROCEDURE check_required_fields
79 ( p_resource_denorm_rec IN Resource_denorm_Rec_type,
80 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
81 x_err_msg_code OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
82
83 Procedure Update_resource_denorm
84 ( p_resource_denorm_old_rec IN Resource_denorm_Rec_type,
85 p_resource_denorm_new_rec IN Resource_denorm_Rec_type,
86 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
87 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
88 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
89
90 PROCEDURE update_single_res_denorm_rec
91 (p_resource_denorm_rec IN resource_denorm_rec_type,
92 x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
93 x_err_msg_code OUT NOCOPY VARCHAR); --File.Sql.39 bug 4440895
94
95
96
97 PROCEDURE update_person_res_denorm_recs
98 (p_resource_denorm_rec IN resource_denorm_rec_type,
99 x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
100 x_err_msg_code OUT NOCOPY VARCHAR); --File.Sql.39 bug 4440895
101
102
103 PROCEDURE syncronize_manager_name
104 (p_new_resource_denorm_rec IN resource_denorm_rec_type,
105 x_return_status OUT NOCOPY VARCHAR); --File.Sql.39 bug 4440895
106
107 PROCEDURE update_job_res_denorm_recs
108 ( p_resource_denorm_rec IN resource_denorm_rec_type,
109 p_start_rowid IN rowid default NULL,
110 p_end_rowid IN rowid default NULL,
111 x_return_status OUT NOCOPY VARCHAR, --File.Sql.39 bug 4440895
112 x_err_msg_code OUT NOCOPY VARCHAR); --File.Sql.39 bug 4440895
113
114 PROCEDURE delete_resource_denorm
115 (p_person_id IN pa_resources_denorm.person_id%type,
116 p_res_effective_start_date IN pa_resources_denorm.resource_effective_start_date%type,
117 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
118 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
119 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
120
121
122 PROCEDURE Populate_Resources_Denorm ( p_resource_source_id IN NUMBER
123 , p_resource_id IN NUMBER
124 , p_resource_name IN VARCHAR2
125 , p_resource_type IN VARCHAR2
126 , p_person_type IN VARCHAR2
127 , p_resource_job_id IN NUMBER
128 , p_resource_job_group_id IN NUMBER
129 , p_resource_org_id IN NUMBER
130 , p_resource_organization_id IN NUMBER
131 , p_assignment_start_date IN DATE
132 , p_assignment_end_date IN DATE
133 , p_manager_id IN NUMBER
134 , p_manager_name IN VARCHAR2
135 , p_request_id IN NUMBER DEFAULT NULL
136 , p_program_application_id IN NUMBER DEFAULT NULL
137 , p_program_id IN NUMBER DEFAULT NULL
138 , p_commit IN VARCHAR2
139 , p_validate_only IN VARCHAR2
140 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
141 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
142 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
143 );
144
145 g_prev_res_source_id NUMBER := NULL;
146
147 FUNCTION Get_Resource_Avl_To_Date (p_resource_id IN NUMBER,
148 p_avl_from_date IN DATE)
149 RETURN DATE;
150
151 FUNCTION Get_Resource_Ovc_To_Date (p_resource_id IN NUMBER,
152 p_ovc_from_date IN DATE)
153 RETURN DATE;
154
155 FUNCTION Get_Resource_Ovc_hours(p_resource_id IN NUMBER,
156 p_ovc_from_date IN DATE,
157 p_ovc_to_date IN DATE)
158 RETURN NUMBER;
159
160 -- PROCEDURE
161 -- Validate_Staff_Filter_Values
162 -- PURPOSE
163 -- Specifically for staffing pages use.
164 -- Currrently used by StaffingHomeAMImpl and ResourceListAMImpl.
165 -- This procedure validates the organization or/and manager
166 -- parameters used in the staffing filters. It requires p_responsibility=RM
167 -- if the user has resource manager responsibility and p_check=Y
168 -- if the manager_name contains % character (from My Resources page).
169 -- HISTORY
170 -- 20-AUG-2002 Created adabdull
171 --+
172 PROCEDURE Validate_Staff_Filter_Values(
173 p_manager_name IN VARCHAR2
174 ,p_manager_id IN NUMBER DEFAULT NULL
175 ,p_org_name IN VARCHAR2
176 ,p_org_id IN NUMBER DEFAULT NULL
177 ,p_responsibility IN VARCHAR2 DEFAULT NULL
178 ,p_check IN VARCHAR2 DEFAULT 'N'
179 ,x_manager_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
180 ,x_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
181 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
182 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
183 ,x_msg_data OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
184
185 -------------------------------------------------------------------------------+
186 --
187 -- PROCEDURE : Update_Res_Availability
188 -- DESCRIPTION : This Procedure is called after FIs are generated
189 -- for any PJR assignment
190 -- This API updates PA_RES_AVAILABILITY based
191 -- on the new assignment created
192 --
193 --------------------------------------------------------------------------------+
194 PROCEDURE update_res_availability (
195 p_resource_id IN NUMBER,
196 p_start_date IN DATE,
197 p_end_date IN DATE,
198 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
199 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
200 x_msg_count OUT NOCOPY NUMBER ); --File.Sql.39 bug 4440895
201
202 -------------------------------------------------------------------------------+
203 --
204 -- PROCEDURE : Populate_Res_Availability
205 -- DESCRIPTION : This Procedure populates PA_RES_AVAILABILITY for the resource
206 -- for the given dates
207 -- It populates the following data slices
208 -- - (Confirmed) Availability/Overcommittment
209 -- - (Confirmed + Provisional) Availability/Overcommittment
210 -- This procedure is also called from the upgrade script
211 -- used to populate PA_RES_AVAILABILITY
212 --
213 --------------------------------------------------------------------------------+
214 PROCEDURE populate_res_availability (
215 p_resource_id IN NUMBER,
216 p_cstart_date IN DATE,
217 p_cend_date IN DATE,
218 p_bstart_date IN DATE,
219 p_bend_date IN DATE,
220 x_return_status OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
221
222 PROCEDURE refresh_res_availability (
223 errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
224 retcode OUT NOCOPY VARCHAR2 ); --File.Sql.39 bug 4440895
225
226 FUNCTION get_res_conf_availability( p_resource_id IN NUMBER,
227 p_start_date IN DATE,
228 p_end_date IN DATE)
229 RETURN NUMBER;
230
231 FUNCTION get_res_prov_conf_availability( p_resource_id IN NUMBER,
232 p_start_date IN DATE,
233 p_end_date IN DATE)
234 RETURN NUMBER;
235
236
237 -- FUNCTION
238 -- Get_Staff_Mgr_Org_Id
239 -- PURPOSE
240 -- Specifically for staffing pages use (Avl/Ovc CO objects)
241 -- It gets the Staffing Manager Organization (either SM organization
242 -- or from the profile option) to be used in the VO. It returns the
243 -- organization id.
244 FUNCTION Get_Staff_Mgr_Org_Id (p_user_id IN NUMBER
245 ,p_person_id IN NUMBER)
246 RETURN VARCHAR2;
247
248 end PA_RESOURCE_PVT;