[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENTS_HISTORY_PKG
Source
1 PACKAGE BODY PA_ASSIGNMENTS_HISTORY_PKG AS
2 /*$Header: PARAAPKB.pls 120.1 2005/08/19 16:47:02 mwasowic noship $*/
3
4 PROCEDURE Insert_Row
5 ( p_assignment_id IN pa_assignments_history.assignment_id%TYPE := FND_API.G_MISS_NUM
6 ,p_assignment_name IN pa_assignments_history.assignment_name%TYPE := FND_API.G_MISS_CHAR
7 ,p_assignment_number IN pa_assignments_history.assignment_number%TYPE := FND_API.G_MISS_NUM
8 ,p_assignment_type IN pa_assignments_history.assignment_type%TYPE := FND_API.G_MISS_CHAR
9 ,p_multiple_status_flag IN pa_assignments_history.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
10 ,p_record_version_number IN pa_assignments_history.record_version_number%TYPE := FND_API.G_MISS_NUM
11 ,p_apprvl_status_code IN pa_assignments_history.apprvl_status_code%TYPE := FND_API.G_MISS_CHAR
12 ,p_status_code IN pa_assignments_history.status_code%TYPE := FND_API.G_MISS_CHAR
13 ,p_staffing_priority_code IN pa_assignments_history.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
14 ,p_staffing_owner_person_id IN pa_assignments_history.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
15 ,p_project_id IN pa_assignments_history.project_id%TYPE := FND_API.G_MISS_NUM
16 ,p_project_role_id IN pa_assignments_history.project_role_id%TYPE := FND_API.G_MISS_NUM
17 ,p_resource_id IN pa_assignments_history.resource_id%TYPE := FND_API.G_MISS_NUM
18 ,p_project_party_id IN pa_assignments_history.project_party_id%TYPE := FND_API.G_MISS_NUM
19 ,p_project_subteam_id IN pa_assignments_history.project_subteam_id%TYPE := FND_API.G_MISS_NUM
20 ,p_description IN pa_assignments_history.description%TYPE := FND_API.G_MISS_CHAR
21 ,p_note_to_approver IN pa_assignments_history.note_to_approver%TYPE := FND_API.G_MISS_CHAR
22 ,p_start_date IN pa_assignments_history.start_date%TYPE := FND_API.G_MISS_DATE
23 ,p_end_date IN pa_assignments_history.end_date%TYPE := FND_API.G_MISS_DATE
24 ,p_assignment_effort IN pa_assignments_history.assignment_effort%TYPE := FND_API.G_MISS_NUM
25 ,p_extension_possible IN pa_assignments_history.extension_possible%TYPE := FND_API.G_MISS_CHAR
26 ,p_source_assignment_id IN pa_assignments_history.source_assignment_id%TYPE := FND_API.G_MISS_NUM
27 ,p_assignment_template_id IN pa_assignments_history.assignment_template_id%TYPE := FND_API.G_MISS_NUM
28 ,p_min_resource_job_level IN pa_assignments_history.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
29 ,p_max_resource_job_level IN pa_assignments_history.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
30 ,p_additional_information IN pa_assignments_history.additional_information%TYPE := FND_API.G_MISS_CHAR
31 ,p_work_type_id IN pa_assignments_history.work_type_id%TYPE := FND_API.G_MISS_NUM
32 ,p_revenue_currency_code IN pa_assignments_history.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
33 ,p_revenue_bill_rate IN pa_assignments_history.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
34 ,p_expense_owner IN pa_assignments_history.expense_owner%TYPE := FND_API.G_MISS_CHAR
35 ,p_expense_limit IN pa_assignments_history.expense_limit%TYPE := FND_API.G_MISS_NUM
36 ,p_expense_limit_currency_code IN pa_assignments_history.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
37 ,p_fcst_tp_amount_type IN pa_assignments_history.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
38 ,p_fcst_job_id IN pa_assignments_history.fcst_job_id%TYPE := FND_API.G_MISS_NUM
39 ,p_fcst_job_group_id IN pa_assignments_history.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
40 ,p_expenditure_org_id IN pa_assignments_history.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
41 ,p_expenditure_organization_id IN pa_assignments_history.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
42 ,p_expenditure_type_class IN pa_assignments_history.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
43 ,p_expenditure_type IN pa_assignments_history.expenditure_type%TYPE := FND_API.G_MISS_CHAR
44 ,p_location_id IN pa_assignments_history.location_id%TYPE := FND_API.G_MISS_NUM
45 ,p_calendar_type IN pa_assignments_history.calendar_type%TYPE := FND_API.G_MISS_CHAR
46 ,p_calendar_id IN pa_assignments_history.calendar_id%TYPE := FND_API.G_MISS_NUM
47 ,p_resource_calendar_percent IN pa_assignments_history.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
48 ,p_attribute_category IN pa_assignments_history.attribute_category%TYPE := FND_API.G_MISS_CHAR
49 ,p_pending_approval_flag IN pa_assignments_history.pending_approval_flag%TYPE := FND_API.G_MISS_CHAR
50 ,p_last_approved_flag IN pa_assignments_history.last_approved_flag%TYPE := FND_API.G_MISS_CHAR
51 ,p_no_of_active_candidates IN pa_assignments_history.no_of_active_candidates%TYPE := FND_API.G_MISS_NUM
52 ,p_comp_match_weighting IN pa_assignments_history.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
53 ,p_avail_match_weighting IN pa_assignments_history.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
54 ,p_job_level_match_weighting IN pa_assignments_history.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
55 ,p_search_min_availability IN pa_assignments_history.search_min_availability%TYPE := FND_API.G_MISS_NUM
56 ,p_search_country_code IN pa_assignments_history.search_country_code%TYPE := FND_API.G_MISS_CHAR
57 ,p_search_exp_org_struct_ver_id IN pa_assignments_history.search_exp_org_struct_ver_id%TYPE:= FND_API.G_MISS_NUM
58 ,p_search_exp_start_org_id IN pa_assignments_history.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
59 ,p_search_min_candidate_score IN pa_assignments_history.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
60 ,p_last_auto_search_date IN pa_assignments_history.last_auto_search_date%TYPE := FND_API.G_MISS_DATE
61 ,p_enable_auto_cand_nom_flag IN pa_assignments_history.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
62 ,p_mass_wf_in_progress_flag IN pa_assignments_history.mass_wf_in_progress_flag%TYPE := FND_API.G_MISS_CHAR
63 ,p_bill_rate_override IN pa_assignments_history.bill_rate_override%TYPE := FND_API.G_MISS_NUM
64 ,p_bill_rate_curr_override IN pa_assignments_history.bill_rate_curr_override%TYPE := FND_API.G_MISS_CHAR
65 ,p_markup_percent_override IN pa_assignments_history.markup_percent_override%TYPE := FND_API.G_MISS_NUM
66 ,p_tp_rate_override IN pa_assignments_history.tp_rate_override%TYPE := FND_API.G_MISS_NUM
67 ,p_tp_currency_override IN pa_assignments_history.tp_currency_override%TYPE := FND_API.G_MISS_CHAR
68 ,p_tp_calc_base_code_override IN pa_assignments_history.tp_calc_base_code_override%TYPE := FND_API.G_MISS_CHAR
69 ,p_tp_percent_applied_override IN pa_assignments_history.tp_percent_applied_override%TYPE := FND_API.G_MISS_NUM
70 ,p_markup_percent IN pa_assignments_history.markup_percent%TYPE := FND_API.G_MISS_NUM
71 ,p_attribute1 IN pa_assignments_history.attribute1%TYPE := FND_API.G_MISS_CHAR
72 ,p_attribute2 IN pa_assignments_history.attribute2%TYPE := FND_API.G_MISS_CHAR
73 ,p_attribute3 IN pa_assignments_history.attribute3%TYPE := FND_API.G_MISS_CHAR
74 ,p_attribute4 IN pa_assignments_history.attribute4%TYPE := FND_API.G_MISS_CHAR
75 ,p_attribute5 IN pa_assignments_history.attribute5%TYPE := FND_API.G_MISS_CHAR
76 ,p_attribute6 IN pa_assignments_history.attribute6%TYPE := FND_API.G_MISS_CHAR
77 ,p_attribute7 IN pa_assignments_history.attribute7%TYPE := FND_API.G_MISS_CHAR
78 ,p_attribute8 IN pa_assignments_history.attribute8%TYPE := FND_API.G_MISS_CHAR
79 ,p_attribute9 IN pa_assignments_history.attribute9%TYPE := FND_API.G_MISS_CHAR
80 ,p_attribute10 IN pa_assignments_history.attribute10%TYPE := FND_API.G_MISS_CHAR
81 ,p_attribute11 IN pa_assignments_history.attribute11%TYPE := FND_API.G_MISS_CHAR
82 ,p_attribute12 IN pa_assignments_history.attribute12%TYPE := FND_API.G_MISS_CHAR
83 ,p_attribute13 IN pa_assignments_history.attribute13%TYPE := FND_API.G_MISS_CHAR
84 ,p_attribute14 IN pa_assignments_history.attribute14%TYPE := FND_API.G_MISS_CHAR
85 ,p_attribute15 IN pa_assignments_history.attribute15%TYPE := FND_API.G_MISS_CHAR
86 /* Added 2 columns for 3051110 */
87 ,p_transfer_price_rate IN pa_project_assignments.transfer_price_rate%TYPE := FND_API.G_MISS_NUM
88 ,p_transfer_pr_rate_curr IN pa_project_assignments.transfer_pr_rate_curr%TYPE := FND_API.G_MISS_CHAR
89 /* Added 2 columns for 3041583 */
90 ,p_discount_percentage IN pa_assignments_history.discount_percentage%TYPE := FND_API.G_MISS_NUM
91 ,p_rate_disc_reason_code IN pa_assignments_history.rate_disc_reason_code%TYPE := FND_API.G_MISS_CHAR
92 ,x_assignment_row_id OUT NOCOPY ROWID --File.Sql.39 bug 4440895
93 ,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
94 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
95 )
96 IS
97
98 BEGIN
99
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 INSERT INTO pa_assignments_history
103 (assignment_id,
104 assignment_name,
105 assignment_type,
106 multiple_status_flag,
107 record_version_number,
108 change_id,
109 apprvl_status_code,
110 status_code,
111 staffing_priority_code,
112 staffing_owner_person_id,
113 project_id,
114 project_role_id,
115 project_party_id,
116 project_subteam_id,
117 description,
118 note_to_approver,
119 start_date,
120 end_date,
121 resource_id,
122 assignment_effort,
123 extension_possible,
124 source_assignment_id,
125 assignment_template_id,
126 min_resource_job_level,
127 max_resource_job_level,
128 assignment_number,
129 additional_information,
130 work_type_id,
131 revenue_currency_code,
132 revenue_bill_rate,
133 expense_owner,
134 expense_limit,
135 expense_limit_currency_code,
136 fcst_tp_amount_type,
137 fcst_job_id,
138 fcst_job_group_id,
139 expenditure_org_id,
140 expenditure_organization_id,
141 expenditure_type_class,
142 expenditure_type,
143 location_id,
144 calendar_type,
145 calendar_id,
146 resource_calendar_percent,
147 pending_approval_flag,
148 last_approved_flag,
149 no_of_active_candidates,
150 competence_match_weighting,
151 availability_match_weighting,
152 job_level_match_weighting,
153 search_min_availability,
154 search_country_code,
155 search_exp_org_struct_ver_id,
156 search_exp_start_org_id,
157 search_min_candidate_score,
158 last_auto_search_date,
159 enable_auto_cand_nom_flag,
160 mass_wf_in_progress_flag,
161 bill_rate_override,
162 bill_rate_curr_override,
163 markup_percent_override,
164 tp_rate_override,
165 tp_currency_override,
166 tp_calc_base_code_override,
167 tp_percent_applied_override,
168 markup_percent,
169 attribute_category,
170 attribute1,
171 attribute2,
172 attribute3,
173 attribute4,
174 attribute5,
175 attribute6,
176 attribute7,
177 attribute8,
178 attribute9,
179 attribute10,
180 attribute11,
181 attribute12,
182 attribute13,
183 attribute14,
184 attribute15,
185 creation_date,
186 created_by,
187 last_update_date,
188 last_updated_by,
189 last_update_login,
190 transfer_price_rate, --For bug 3051110
191 transfer_pr_rate_curr,
192 discount_percentage, -- Added for bug 3041583
193 rate_disc_reason_code -- Added for bug 3041583
194 )
195 VALUES
196 ( DECODE(p_assignment_id, FND_API.G_MISS_NUM, NULL, p_assignment_id),
197 DECODE(p_assignment_name, FND_API.G_MISS_CHAR, NULL, p_assignment_name),
198 DECODE(p_assignment_type, FND_API.G_MISS_CHAR, NULL, p_assignment_type),
199 DECODE(p_multiple_status_flag, FND_API.G_MISS_CHAR, NULL, p_multiple_status_flag),
200 DECODE(p_record_version_number, FND_API.G_MISS_NUM, NULL, p_record_version_number),
201 pa_assignments_history_s.NEXTVAL,
202 DECODE(p_apprvl_status_code, FND_API.G_MISS_CHAR, NULL, p_apprvl_status_code),
203 DECODE(p_status_code, FND_API.G_MISS_CHAR, NULL, p_status_code),
204 DECODE(p_staffing_priority_code, FND_API.G_MISS_CHAR, NULL, p_staffing_priority_code),
205 DECODE(p_staffing_owner_person_id, FND_API.G_MISS_NUM, NULL, p_staffing_owner_person_id),
206 DECODE(p_project_id, FND_API.G_MISS_NUM, NULL, p_project_id),
207 DECODE(p_project_role_id, FND_API.G_MISS_NUM, NULL, p_project_role_id),
208 DECODE(p_project_party_id, FND_API.G_MISS_NUM, NULL, p_project_party_id),
209 DECODE(p_project_subteam_id, FND_API.G_MISS_NUM, NULL, p_project_subteam_id),
210 DECODE(p_description, FND_API.G_MISS_CHAR, NULL, p_description),
211 DECODE(p_note_to_approver, FND_API.G_MISS_CHAR, NULL, p_note_to_approver),
212 DECODE(p_start_date, FND_API.G_MISS_DATE, NULL, p_start_date),
213 DECODE(p_end_date, FND_API.G_MISS_DATE, NULL, p_end_date),
214 DECODE(p_resource_id, FND_API.G_MISS_NUM, NULL, p_resource_id),
215 DECODE(p_assignment_effort, FND_API.G_MISS_NUM, NULL, p_assignment_effort),
216 DECODE(p_extension_possible, FND_API.G_MISS_CHAR, NULL, p_extension_possible),
217 DECODE(p_source_assignment_id, FND_API.G_MISS_NUM, NULL, p_source_assignment_id),
218 DECODE(p_assignment_template_id, FND_API.G_MISS_NUM, NULL, p_assignment_template_id),
219 DECODE(p_min_resource_job_level, FND_API.G_MISS_NUM, NULL, p_min_resource_job_level),
220 DECODE(p_max_resource_job_level, FND_API.G_MISS_NUM, NULL, p_max_resource_job_level),
221 DECODE(p_assignment_number, FND_API.G_MISS_NUM, NULL, p_assignment_number),
222 DECODE(p_additional_information, FND_API.G_MISS_CHAR, NULL, p_additional_information),
223 DECODE(p_work_type_id, FND_API.G_MISS_NUM, NULL, p_work_type_id),
224 DECODE(p_revenue_currency_code, FND_API.G_MISS_CHAR, NULL, p_revenue_currency_code),
225 DECODE(p_revenue_bill_rate, FND_API.G_MISS_NUM, NULL, p_revenue_bill_rate),
226 DECODE(p_expense_owner, FND_API.G_MISS_CHAR, NULL, p_expense_owner),
227 DECODE(p_expense_limit, FND_API.G_MISS_NUM, NULL, p_expense_limit),
228 DECODE(p_expense_limit_currency_code, FND_API.G_MISS_CHAR, NULL, p_expense_limit_currency_code),
229 DECODE(p_fcst_tp_amount_type, FND_API.G_MISS_CHAR, NULL, p_fcst_tp_amount_type),
230 DECODE(p_fcst_job_id, FND_API.G_MISS_NUM, NULL, p_fcst_job_id),
231 DECODE(p_fcst_job_group_id, FND_API.G_MISS_NUM, NULL, p_fcst_job_group_id),
232 DECODE(p_expenditure_org_id, FND_API.G_MISS_NUM, NULL, p_expenditure_org_id),
233 DECODE(p_expenditure_organization_id, FND_API.G_MISS_NUM, NULL, p_expenditure_organization_id),
234 DECODE(p_expenditure_type_class, FND_API.G_MISS_CHAR, NULL, p_expenditure_type_class),
235 DECODE(p_expenditure_type, FND_API.G_MISS_CHAR, NULL, p_expenditure_type),
236 DECODE(p_location_id, FND_API.G_MISS_NUM, NULL, p_location_id),
237 DECODE(p_calendar_type, FND_API.G_MISS_CHAR, NULL, p_calendar_type),
238 DECODE(p_calendar_id, FND_API.G_MISS_NUM, NULL, p_calendar_id),
239 DECODE(p_resource_calendar_percent, FND_API.G_MISS_NUM, NULL, p_resource_calendar_percent),
240 DECODE(p_pending_approval_flag, FND_API.G_MISS_CHAR, NULL, p_pending_approval_flag),
241 DECODE(p_last_approved_flag, FND_API.G_MISS_CHAR, NULL, p_last_approved_flag),
242 DECODE(p_no_of_active_candidates, FND_API.G_MISS_NUM, NULL, p_no_of_active_candidates),
243 DECODE(p_comp_match_weighting, FND_API.G_MISS_NUM, NULL, p_comp_match_weighting),
244 DECODE(p_avail_match_weighting, FND_API.G_MISS_CHAR, NULL, p_avail_match_weighting),
245 DECODE(p_job_level_match_weighting, FND_API.G_MISS_NUM, NULL, p_job_level_match_weighting),
246 DECODE(p_search_min_availability, FND_API.G_MISS_NUM, NULL, p_search_min_availability),
250 DECODE(p_search_min_candidate_score, FND_API.G_MISS_NUM, NULL, p_search_min_candidate_score),
247 DECODE(p_search_country_code, FND_API.G_MISS_CHAR, NULL, p_search_country_code),
248 DECODE(p_search_exp_org_struct_ver_id, FND_API.G_MISS_NUM, NULL, p_search_exp_org_struct_ver_id),
249 DECODE(p_search_exp_start_org_id, FND_API.G_MISS_NUM, NULL, p_search_exp_start_org_id),
251 DECODE(p_last_auto_search_date, FND_API.G_MISS_DATE, NULL, p_last_auto_search_date),
252 DECODE(p_enable_auto_cand_nom_flag, FND_API.G_MISS_CHAR, NULL, p_enable_auto_cand_nom_flag),
253 DECODE(p_mass_wf_in_progress_flag, FND_API.G_MISS_CHAR, NULL, p_mass_wf_in_progress_flag),
254 DECODE(p_bill_rate_override , FND_API.G_MISS_NUM, NULL, p_bill_rate_override ),
255 DECODE(p_bill_rate_curr_override, FND_API.G_MISS_CHAR, NULL, p_bill_rate_curr_override),
256 DECODE(p_markup_percent_override, FND_API.G_MISS_NUM, NULL, p_markup_percent_override),
257 DECODE(p_tp_rate_override, FND_API.G_MISS_NUM, NULL, p_tp_rate_override),
258 DECODE(p_tp_currency_override, FND_API.G_MISS_CHAR, NULL, p_tp_currency_override),
259 DECODE(p_tp_calc_base_code_override, FND_API.G_MISS_CHAR, NULL, p_tp_calc_base_code_override),
260 DECODE(p_tp_percent_applied_override, FND_API.G_MISS_NUM, NULL, p_tp_percent_applied_override),
261 DECODE(p_markup_percent, FND_API.G_MISS_NUM, NULL, p_markup_percent),
262 DECODE(p_attribute_category, FND_API.G_MISS_CHAR, NULL, p_attribute_category),
263 DECODE(p_attribute1, FND_API.G_MISS_CHAR, NULL, p_attribute1),
264 DECODE(p_attribute2, FND_API.G_MISS_CHAR, NULL, p_attribute2),
265 DECODE(p_attribute3, FND_API.G_MISS_CHAR, NULL, p_attribute3),
266 DECODE(p_attribute4, FND_API.G_MISS_CHAR, NULL, p_attribute4),
267 DECODE(p_attribute5, FND_API.G_MISS_CHAR, NULL, p_attribute5),
268 DECODE(p_attribute6, FND_API.G_MISS_CHAR, NULL, p_attribute6),
269 DECODE(p_attribute7, FND_API.G_MISS_CHAR, NULL, p_attribute7),
270 DECODE(p_attribute8, FND_API.G_MISS_CHAR, NULL, p_attribute8),
271 DECODE(p_attribute9, FND_API.G_MISS_CHAR, NULL, p_attribute9),
272 DECODE(p_attribute10, FND_API.G_MISS_CHAR, NULL, p_attribute10),
273 DECODE(p_attribute11, FND_API.G_MISS_CHAR, NULL, p_attribute11),
274 DECODE(p_attribute12, FND_API.G_MISS_CHAR, NULL, p_attribute12),
275 DECODE(p_attribute13, FND_API.G_MISS_CHAR, NULL, p_attribute13),
276 DECODE(p_attribute14, FND_API.G_MISS_CHAR, NULL, p_attribute14),
277 DECODE(p_attribute15, FND_API.G_MISS_CHAR, NULL, p_attribute15),
278 sysdate,
279 fnd_global.user_id,
280 sysdate,
281 fnd_global.user_id,
282 fnd_global.login_id,
283 DECODE(p_transfer_price_rate, FND_API.G_MISS_NUM, NULL, p_transfer_price_rate), -- Added for 3051110
284 DECODE(p_transfer_pr_rate_curr, FND_API.G_MISS_CHAR, NULL, p_transfer_pr_rate_curr),
285 DECODE(p_discount_percentage, FND_API.G_MISS_NUM, NULL, p_discount_percentage), -- Added for 3041583
286 DECODE(p_rate_disc_reason_code, FND_API.G_MISS_CHAR, NULL, p_rate_disc_reason_code) -- Added for 3041583
287 )
288
289 RETURNING rowid, change_id INTO x_assignment_row_id, x_change_id;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 -- Set the exception Message and the stack
294 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_HISTORY_PKG.Insert_Row'
295 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
297 RAISE;
298 END Insert_Row;
299
300
301 PROCEDURE Update_Row
302 ( p_assignment_row_id IN ROWID :=NULL
303 ,p_assignment_id IN pa_assignments_history.assignment_id%TYPE
304 ,p_change_id IN pa_assignments_history.change_id%TYPE :=NULL
305 ,p_last_approved_flag IN pa_assignments_history.last_approved_flag%TYPE :=NULL
306 ,p_record_version_number IN pa_assignments_history.record_version_number%TYPE := FND_API.G_MISS_NUM
307 ,p_assignment_name IN pa_assignments_history.assignment_name%TYPE := FND_API.G_MISS_CHAR
308 ,p_assignment_type IN pa_assignments_history.assignment_type%TYPE := FND_API.G_MISS_CHAR
309 ,p_multiple_status_flag IN pa_assignments_history.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
310 ,p_apprvl_status_code IN pa_assignments_history.apprvl_status_code%TYPE := FND_API.G_MISS_CHAR
311 ,p_status_code IN pa_assignments_history.status_code%TYPE := FND_API.G_MISS_CHAR
312 ,p_staffing_priority_code IN pa_assignments_history.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
313 ,p_staffing_owner_person_id IN pa_assignments_history.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
314 ,p_project_id IN pa_assignments_history.project_id%TYPE := FND_API.G_MISS_NUM
315 ,p_project_role_id IN pa_assignments_history.project_role_id%TYPE := FND_API.G_MISS_NUM
316 ,p_project_party_id IN pa_assignments_history.project_party_id%TYPE := FND_API.G_MISS_NUM
317 ,p_project_subteam_id IN pa_assignments_history.project_subteam_id%TYPE := FND_API.G_MISS_NUM
318 ,p_description IN pa_assignments_history.description%TYPE := FND_API.G_MISS_CHAR
319 ,p_note_to_approver IN pa_assignments_history.note_to_approver%TYPE := FND_API.G_MISS_CHAR
320 ,p_start_date IN pa_assignments_history.start_date%TYPE := FND_API.G_MISS_DATE
321 ,p_end_date IN pa_assignments_history.end_date%TYPE := FND_API.G_MISS_DATE
325 ,p_assignment_template_id IN pa_assignments_history.assignment_template_id%TYPE := FND_API.G_MISS_NUM
322 ,p_assignment_effort IN pa_assignments_history.assignment_effort%TYPE := FND_API.G_MISS_NUM
323 ,p_extension_possible IN pa_assignments_history.extension_possible%TYPE := FND_API.G_MISS_CHAR
324 ,p_source_assignment_id IN pa_assignments_history.source_assignment_id%TYPE := FND_API.G_MISS_NUM
326 ,p_min_resource_job_level IN pa_assignments_history.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
327 ,p_max_resource_job_level IN pa_assignments_history.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
328 ,p_assignment_number IN pa_assignments_history.assignment_number%TYPE := FND_API.G_MISS_NUM
329 ,p_additional_information IN pa_assignments_history.additional_information%TYPE := FND_API.G_MISS_CHAR
330 ,p_work_type_id IN pa_assignments_history.work_type_id%TYPE := FND_API.G_MISS_NUM
331 ,p_revenue_currency_code IN pa_assignments_history.revenue_currency_code%TYPE := FND_API.G_MISS_CHAR
332 ,p_revenue_bill_rate IN pa_assignments_history.revenue_bill_rate%TYPE := FND_API.G_MISS_NUM
333 ,p_expense_owner IN pa_assignments_history.expense_owner%TYPE := FND_API.G_MISS_CHAR
334 ,p_expense_limit IN pa_assignments_history.expense_limit%TYPE := FND_API.G_MISS_NUM
335 ,p_expense_limit_currency_code IN pa_assignments_history.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
336 ,p_fcst_tp_amount_type IN pa_assignments_history.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
337 ,p_fcst_job_id IN pa_assignments_history.fcst_job_id%TYPE := FND_API.G_MISS_NUM
338 ,p_fcst_job_group_id IN pa_assignments_history.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
339 ,p_expenditure_org_id IN pa_assignments_history.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
340 ,p_expenditure_organization_id IN pa_assignments_history.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
341 ,p_expenditure_type_class IN pa_assignments_history.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
342 ,p_expenditure_type IN pa_assignments_history.expenditure_type%TYPE := FND_API.G_MISS_CHAR
343 ,p_location_id IN pa_assignments_history.location_id%TYPE := FND_API.G_MISS_NUM
344 ,p_calendar_type IN pa_assignments_history.calendar_type%TYPE := FND_API.G_MISS_CHAR
345 ,p_calendar_id IN pa_assignments_history.calendar_id%TYPE := FND_API.G_MISS_NUM
346 ,p_resource_calendar_percent IN pa_assignments_history.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
347 ,p_pending_approval_flag IN pa_assignments_history.pending_approval_flag%TYPE := FND_API.G_MISS_CHAR
348 ,p_no_of_active_candidates IN pa_assignments_history.no_of_active_candidates%TYPE := FND_API.G_MISS_NUM
349 ,p_attribute_category IN pa_assignments_history.attribute_category%TYPE := FND_API.G_MISS_CHAR
350 ,p_attribute1 IN pa_assignments_history.attribute1%TYPE := FND_API.G_MISS_CHAR
351 ,p_attribute2 IN pa_assignments_history.attribute2%TYPE := FND_API.G_MISS_CHAR
352 ,p_attribute3 IN pa_assignments_history.attribute3%TYPE := FND_API.G_MISS_CHAR
353 ,p_attribute4 IN pa_assignments_history.attribute4%TYPE := FND_API.G_MISS_CHAR
354 ,p_attribute5 IN pa_assignments_history.attribute5%TYPE := FND_API.G_MISS_CHAR
355 ,p_attribute6 IN pa_assignments_history.attribute6%TYPE := FND_API.G_MISS_CHAR
356 ,p_attribute7 IN pa_assignments_history.attribute7%TYPE := FND_API.G_MISS_CHAR
357 ,p_attribute8 IN pa_assignments_history.attribute8%TYPE := FND_API.G_MISS_CHAR
358 ,p_attribute9 IN pa_assignments_history.attribute9%TYPE := FND_API.G_MISS_CHAR
359 ,p_attribute10 IN pa_assignments_history.attribute10%TYPE := FND_API.G_MISS_CHAR
360 ,p_attribute11 IN pa_assignments_history.attribute11%TYPE := FND_API.G_MISS_CHAR
361 ,p_attribute12 IN pa_assignments_history.attribute12%TYPE := FND_API.G_MISS_CHAR
362 ,p_attribute13 IN pa_assignments_history.attribute13%TYPE := FND_API.G_MISS_CHAR
363 ,p_attribute14 IN pa_assignments_history.attribute14%TYPE := FND_API.G_MISS_CHAR
364 ,p_attribute15 IN pa_assignments_history.attribute15%TYPE := FND_API.G_MISS_CHAR
365 /* Added 2 columns for 3051110 */
366 ,p_transfer_price_rate IN pa_project_assignments.transfer_price_rate%TYPE := FND_API.G_MISS_NUM
367 ,p_transfer_pr_rate_curr IN pa_project_assignments.transfer_pr_rate_curr%TYPE := FND_API.G_MISS_CHAR
368 /* Added 2 columns for 3041583 */
369 ,p_discount_percentage IN pa_assignments_history.discount_percentage%TYPE := FND_API.G_MISS_NUM
370 ,p_rate_disc_reason_code IN pa_assignments_history.rate_disc_reason_code%TYPE := FND_API.G_MISS_CHAR
371 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
372 )IS
373
374
375 l_row_id ROWID := p_assignment_row_id;
376
377
378 CURSOR get_row_id IS
379 SELECT rowid
380 FROM pa_assignments_history
381 WHERE assignment_id = p_assignment_id
382 AND last_approved_flag = nvl(p_last_approved_flag, last_approved_flag)
386
383 AND change_id = nvl(p_change_id, change_id);
384
385 BEGIN
387 x_return_status := FND_API.G_RET_STS_SUCCESS;
388
389 /*
390 -- Lock the row first
391 SELECT rowid INTO l_row_id
392 FROM pa_assignments_history
393 WHERE assignment_id = p_assignment_id
394 OR rowid = p_assignment_row_id
395 FOR UPDATE NOWAIT;
396 */
397
398
399 --get the ROWID for the row to be updated if
400 --p_assignment_row_id is not passed to the API.
401
402 IF l_row_id IS NULL THEN
403
404 OPEN get_row_id;
405
406 FETCH get_row_id INTO l_row_id;
407
408 CLOSE get_row_id;
409
410 END IF;
411
412
413 UPDATE pa_assignments_history
414 SET assignment_name = DECODE(p_assignment_name, FND_API.G_MISS_CHAR, assignment_name, p_assignment_name),
415 assignment_type = DECODE(p_assignment_type, FND_API.G_MISS_CHAR, assignment_type, p_assignment_type),
416 change_id = DECODE(p_change_id, NULL, change_id, p_change_id),
417 last_approved_flag = DECODE(p_last_approved_flag,NULL, last_approved_flag, p_last_approved_flag),
418 record_version_number = DECODE(p_record_version_number,FND_API.G_MISS_NUM,record_version_number, p_record_version_number),
419 multiple_status_flag = DECODE(p_multiple_status_flag, FND_API.G_MISS_CHAR, multiple_status_flag, p_multiple_status_flag),
420 apprvl_status_code = DECODE(p_apprvl_status_code, FND_API.G_MISS_CHAR, apprvl_status_code, p_apprvl_status_code),
421 status_code = DECODE(p_status_code, FND_API.G_MISS_CHAR, status_code, p_status_code),
422 staffing_priority_code = DECODE(p_staffing_priority_code, FND_API.G_MISS_CHAR, staffing_priority_code, p_staffing_priority_code),
423 staffing_owner_person_id = DECODE(p_staffing_owner_person_id, FND_API.G_MISS_NUM, staffing_owner_person_id, p_staffing_owner_person_id),
424 project_id = DECODE(p_project_id, FND_API.G_MISS_NUM, project_id, p_project_id),
425 project_role_id = DECODE(p_project_role_id, FND_API.G_MISS_NUM, project_role_id, p_project_role_id),
426 project_party_id = DECODE(p_project_party_id, FND_API.G_MISS_NUM, project_party_id, p_project_party_id),
427 project_subteam_id = DECODE(p_project_subteam_id, FND_API.G_MISS_NUM, project_subteam_id, p_project_subteam_id),
428 description = DECODE(p_description, FND_API.G_MISS_CHAR, description, p_description),
429 note_to_approver = DECODE(p_note_to_approver, FND_API.G_MISS_CHAR, note_to_approver, p_note_to_approver),
430 start_date = DECODE(p_start_date, FND_API.G_MISS_DATE, start_date, p_start_date),
431 end_date = DECODE(p_end_date, FND_API.G_MISS_DATE, end_date, p_end_date),
432 assignment_effort = DECODE(p_assignment_effort, FND_API.G_MISS_NUM, assignment_effort, p_assignment_effort),
433 extension_possible = DECODE(p_extension_possible, FND_API.G_MISS_CHAR, extension_possible, p_extension_possible),
434 source_assignment_id = DECODE(p_source_assignment_id, FND_API.G_MISS_NUM, source_assignment_id, p_source_assignment_id),
435 assignment_template_id = DECODE(p_assignment_template_id, FND_API.G_MISS_NUM, assignment_template_id, p_assignment_template_id),
436 min_resource_job_level = DECODE(p_min_resource_job_level, FND_API.G_MISS_NUM, min_resource_job_level, p_min_resource_job_level),
437 max_resource_job_level = DECODE(p_max_resource_job_level, FND_API.G_MISS_NUM, max_resource_job_level, p_max_resource_job_level),
438 assignment_number = DECODE(p_assignment_number, FND_API.G_MISS_NUM, assignment_number, p_assignment_number),
439 additional_information = DECODE(p_additional_information, FND_API.G_MISS_CHAR, additional_information, p_additional_information),
440 work_type_id = DECODE(p_work_type_id, FND_API.G_MISS_NUM, work_type_id, p_work_type_id),
441 revenue_currency_code = DECODE(p_revenue_currency_code, FND_API.G_MISS_CHAR, revenue_currency_code, p_revenue_currency_code),
442 revenue_bill_rate = DECODE(p_revenue_bill_rate, FND_API.G_MISS_NUM, revenue_bill_rate, p_revenue_bill_rate),
443 expense_owner = DECODE(p_expense_owner, FND_API.G_MISS_CHAR, expense_owner, p_expense_owner),
444 expense_limit = DECODE(p_expense_limit, FND_API.G_MISS_NUM, expense_limit, p_expense_limit),
445 expense_limit_currency_code = DECODE(p_expense_limit_currency_code, FND_API.G_MISS_CHAR, expense_limit_currency_code, p_expense_limit_currency_code),
446 fcst_tp_amount_type = DECODE(p_fcst_tp_amount_type, FND_API.G_MISS_CHAR, fcst_tp_amount_type, p_fcst_tp_amount_type),
447 fcst_job_id = DECODE(p_fcst_job_id, FND_API.G_MISS_NUM, fcst_job_id, p_fcst_job_id),
448 fcst_job_group_id = DECODE(p_fcst_job_group_id, FND_API.G_MISS_NUM,fcst_job_group_id, p_fcst_job_group_id),
449 expenditure_org_id = DECODE(p_expenditure_org_id, FND_API.G_MISS_NUM,expenditure_org_id , p_expenditure_org_id),
450 expenditure_organization_id = DECODE(p_expenditure_organization_id, FND_API.G_MISS_NUM,expenditure_organization_id , p_expenditure_organization_id),
451 expenditure_type_class = DECODE(p_expenditure_type_class, FND_API.G_MISS_CHAR,expenditure_type_class , p_expenditure_type_class),
452 expenditure_type = DECODE(p_expenditure_type, FND_API.G_MISS_CHAR, expenditure_type, p_expenditure_type),
453 location_id = DECODE(p_location_id, FND_API.G_MISS_NUM, location_id, p_location_id),
454 calendar_type = DECODE(p_calendar_type, FND_API.G_MISS_CHAR, calendar_type, p_calendar_type),
455 calendar_id = DECODE(p_calendar_id, FND_API.G_MISS_NUM, calendar_id, p_calendar_id),
456 resource_calendar_percent = DECODE(p_resource_calendar_percent, FND_API.G_MISS_NUM, resource_calendar_percent, p_resource_calendar_percent),
460 attribute1 = DECODE(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1),
457 pending_approval_flag = DECODE(p_pending_approval_flag, FND_API.G_MISS_CHAR, pending_approval_flag, p_pending_approval_flag),
458 no_of_active_candidates = DECODE(p_no_of_active_candidates, FND_API.G_MISS_NUM,no_of_active_candidates, p_no_of_active_candidates),
459 attribute_category = DECODE(p_attribute_category, FND_API.G_MISS_CHAR, attribute_category, p_attribute_category),
461 attribute2 = DECODE(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2),
462 attribute3 = DECODE(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3),
463 attribute4 = DECODE(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4),
464 attribute5 = DECODE(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5),
465 attribute6 = DECODE(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6),
466 attribute7 = DECODE(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7),
467 attribute8 = DECODE(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8),
468 attribute9 = DECODE(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9),
469 attribute10 = DECODE(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10),
470 attribute11 = DECODE(p_attribute11, FND_API.G_MISS_CHAR, attribute11, p_attribute11),
471 attribute12 = DECODE(p_attribute12, FND_API.G_MISS_CHAR, attribute12, p_attribute12),
472 attribute13 = DECODE(p_attribute13, FND_API.G_MISS_CHAR, attribute13, p_attribute13),
473 attribute14 = DECODE(p_attribute14, FND_API.G_MISS_CHAR, attribute14, p_attribute14),
474 attribute15 = DECODE(p_attribute15, FND_API.G_MISS_CHAR, attribute15, p_attribute15),
475 last_update_date = sysdate,
476 last_updated_by = fnd_global.user_id,
477 last_update_login = fnd_global.login_id,
478 transfer_price_rate = DECODE(p_transfer_price_rate, FND_API.G_MISS_NUM, transfer_price_rate, p_transfer_price_rate), -- Added for 3051110
479 transfer_pr_rate_curr = DECODE(p_transfer_pr_rate_curr, FND_API.G_MISS_CHAR, transfer_pr_rate_curr, p_transfer_pr_rate_curr),
480 discount_percentage = DECODE(p_discount_percentage, FND_API.G_MISS_NUM, NULL, p_discount_percentage), -- Added for 3041583
481 rate_disc_reason_code = DECODE(p_rate_disc_reason_code, FND_API.G_MISS_CHAR, NULL, p_rate_disc_reason_code) -- Added for 3041583
482 WHERE rowid = l_row_id
483 AND nvl(p_change_id, change_id) = change_id;
484
485
486 IF (SQL%NOTFOUND) THEN
487 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
488 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
489 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
490 x_return_status := FND_API.G_RET_STS_ERROR;
491 END IF;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 -- Set the exception Message and the stack
496 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_HISTORY_PKG.Update_Row'
497 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
499 RAISE;
500 END Update_Row;
501
502
503 PROCEDURE Delete_Row
504 ( p_assignment_row_id IN ROWID :=NULL
505 ,p_assignment_id IN pa_assignments_history.assignment_id%TYPE
506 ,p_change_id IN pa_assignments_history.change_id%TYPE := NULL
507 ,p_last_approved_flag IN pa_assignments_history.last_approved_flag%TYPE := NULL
508 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
509 )
510 IS
511
512 l_row_id ROWID;
513
514 BEGIN
515
516 x_return_status := FND_API.G_RET_STS_SUCCESS;
517
518
519 /*
520 -- Lock the Assignment;
521 SELECT rowid INTO l_row_id
522 FROM pa_assignments_history
523 WHERE assignment_id = p_assignment_id
524 FOR UPDATE NOWAIT;
525 */
526 DELETE FROM pa_assignments_history
527 WHERE assignment_id = p_assignment_id
528 AND nvl (p_last_approved_flag, last_approved_flag)=last_approved_flag
529 OR rowid = p_assignment_row_id
530 AND nvl(p_change_id, change_id) = change_id;
531 --
532 IF (SQL%NOTFOUND) THEN
533
534 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
535 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
536 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
537 x_return_status := FND_API.G_RET_STS_ERROR;
538
539 END IF;
540 --
541 --
542
543 EXCEPTION
544 WHEN OTHERS THEN
545 -- Set the current program unit name in the error stack
546 -- PA_Error_Utils.Set_Error_Stack('PA_ASSIGNMENTS_HISTORY_PKG.Delete_Row');
547 -- Set the exception Message and the stack
548 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_HISTORY_PKG.Delete_Row'
549 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
551 RAISE;
552 END Delete_Row;
553
554
555
556 PROCEDURE Delete_By_Assignment
557 ( p_assignment_id IN pa_assignments_history.assignment_id%TYPE
558 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
559 )
560 IS
561
562 BEGIN
563 x_return_status := FND_API.G_RET_STS_SUCCESS;
564
565
566 /*
567 -- Lock the Assignment;
568 SELECT rowid INTO l_row_id
569 FROM pa_assignments_history
570 WHERE assignment_id = p_assignment_id
571 FOR UPDATE NOWAIT;
572 */
573 DELETE FROM pa_assignments_history
574 WHERE assignment_id = p_assignment_id;
575 --
576 /* IF (SQL%NOTFOUND) THEN
577
578 --dbms_output.put_line('Assignment not previously approved');
579 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
580 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
581 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
582 x_return_status := FND_API.G_RET_STS_ERROR;
583
584 --No error, since possible that an assignment has not been previously approved.
585 END IF;
586 */
587 --
588 --
589
590 EXCEPTION
591 WHEN OTHERS THEN
592 -- Set the exception Message and the stack
593 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENTS_HISTORY_PKG.Delete_By_Assignment'
594 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
596 RAISE;
597 END Delete_By_Assignment;
598
599 --
600 --
601 END PA_ASSIGNMENTS_HISTORY_PKG;