[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECTS_PKG1
Source
1 PACKAGE BODY PA_PROJECTS_PKG1 as
2 /* $Header: PAXPRO1B.pls 120.2.12010000.2 2008/10/27 16:40:16 atshukla ship $ */
3
4 PROCEDURE Update_Row(X_Rowid VARCHAR2,
5 X_Project_Id NUMBER,
6 X_Name VARCHAR2,
7 X_Long_Name VARCHAR2,
8 X_Segment1 VARCHAR2,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Project_Type VARCHAR2,
13 X_Carrying_Out_Organization_Id NUMBER,
14 X_Public_Sector_Flag VARCHAR2,
15 -- Commented for Bug 3605235
16 --X_Project_Status_Code VARCHAR2,
17 X_Description VARCHAR2,
18 X_Start_Date DATE,
19 X_Completion_Date DATE,
20 X_Closed_Date DATE,
21 X_Distribution_Rule VARCHAR2,
22 X_Labor_Invoice_Format_Id NUMBER,
23 X_NL_Invoice_Format_Id NUMBER,
24 X_Retention_Invoice_Format_Id NUMBER,
25 X_Retention_Percentage NUMBER,
26 X_Billing_Offset NUMBER,
27 X_Billing_Cycle_Id NUMBER,
28 X_Labor_Std_Bill_Rate_Schdl VARCHAR2,
29 X_Labor_Bill_Rate_Org_Id NUMBER,
30 X_Labor_Schedule_Fixed_Date DATE,
31 X_Labor_Schedule_Discount NUMBER,
32 X_NL_Std_Bill_Rate_Schdl VARCHAR2,
33 X_NL_Bill_Rate_Org_Id NUMBER,
34 X_NL_Schedule_Fixed_Date DATE,
35 X_NL_Schedule_Discount NUMBER,
36 X_Limit_To_Txn_Controls_Flag VARCHAR2,
37 X_Project_Level_Funding_Flag VARCHAR2,
38 X_Invoice_Comment VARCHAR2,
39 X_Unbilled_Receivable_Dr NUMBER,
40 X_Unearned_Revenue_Cr NUMBER,
41 X_Summary_Flag VARCHAR2,
42 X_Enabled_Flag VARCHAR2,
43 X_Segment2 VARCHAR2,
44 X_Segment3 VARCHAR2,
45 X_Segment4 VARCHAR2,
46 X_Segment5 VARCHAR2,
47 X_Segment6 VARCHAR2,
48 X_Segment7 VARCHAR2,
49 X_Segment8 VARCHAR2,
50 X_Segment9 VARCHAR2,
51 X_Segment10 VARCHAR2,
52 X_Attribute_Category VARCHAR2,
53 X_Attribute1 VARCHAR2,
54 X_Attribute2 VARCHAR2,
55 X_Attribute3 VARCHAR2,
56 X_Attribute4 VARCHAR2,
57 X_Attribute5 VARCHAR2,
58 X_Attribute6 VARCHAR2,
59 X_Attribute7 VARCHAR2,
60 X_Attribute8 VARCHAR2,
61 X_Attribute9 VARCHAR2,
62 X_Attribute10 VARCHAR2,
63 X_Cost_Ind_Rate_Sch_Id NUMBER,
64 X_Rev_Ind_Rate_Sch_Id NUMBER,
65 X_Inv_Ind_Rate_Sch_Id NUMBER,
66 X_Cost_Ind_Sch_Fixed_Date DATE,
67 X_Rev_Ind_Sch_Fixed_Date DATE,
68 X_Inv_Ind_Sch_Fixed_Date DATE,
69 X_Labor_Sch_Type VARCHAR2,
70 X_Non_Labor_Sch_Type VARCHAR2,
71 X_Template_Flag VARCHAR2,
72 X_Verification_Date DATE,
73 X_Created_From_Project_Id NUMBER,
74 X_Template_Start_Date DATE,
75 X_Template_End_Date DATE,
76 --added to remove bug#594567 :ashia Bagai 6-jan-98
77 -- Commented for Bug 3605235
78 --X_Wf_Status_Code VARCHAR2,
79 X_Project_Currency_Code VARCHAR2,
80 X_Allow_Cross_Charge_Flag VARCHAR2,
81 X_Project_Rate_Date DATE,
82 X_Project_Rate_Type VARCHAR2,
83 X_Output_Tax_Code VARCHAR2,
84 X_Retention_Tax_Code VARCHAR2,
85 X_CC_Process_Labor_Flag VARCHAR2,
86 X_Labor_Tp_Schedule_Id NUMBER,
87 X_Labor_Tp_Fixed_Date DATE,
88 X_CC_Process_NL_Flag VARCHAR2,
89 X_Nl_Tp_Schedule_Id NUMBER,
90 X_Nl_Tp_Fixed_Date DATE,
91 X_CC_Tax_Task_Id NUMBER,
92 -- End of addtion to remove bug#594567
93 -- 17-MAY-00 kkekkar Added the following columns for CBGA project
94 -- bill_job_group_id, cost_job_group_id
95 x_bill_job_group_id NUMBER,
96 x_cost_job_group_id NUMBER,
97 x_role_list_id NUMBER,
98 x_work_type_id NUMBER,
99 x_calendar_id NUMBER,
100 x_location_id NUMBER,
101 x_probability_member_id NUMBER,
102 x_project_value NUMBER,
103 x_expected_approval_date DATE,
104 x_team_template_id NUMBER,
105 -- 21-MAR-2001 anlee
106 -- added job_bill_rate_schedule_id,
107 -- emp_bill_rate_schedule_id for
108 -- PRM forecasting changes
109 x_job_bill_rate_schedule_id NUMBER,
110 x_emp_bill_rate_schedule_id NUMBER,
111 --MCA Sakthi for MultiAgreementCurreny Project
112 x_competence_match_wt NUMBER,
113 x_availability_match_wt NUMBER,
114 x_job_level_match_wt NUMBER,
115 x_enable_automated_search VARCHAR2,
116 x_search_min_availability NUMBER,
117 x_search_org_hier_id NUMBER,
118 x_search_starting_org_id NUMBER,
119 x_search_country_code VARCHAR2,
120 x_min_cand_score_reqd_for_nom NUMBER,
121 x_non_lab_std_bill_rt_sch_id NUMBER,
122 x_invproc_currency_type VARCHAR2,
123 x_revproc_currency_code VARCHAR2,
124 x_project_bil_rate_date_code VARCHAR2,
125 x_project_bil_rate_type VARCHAR2,
126 x_project_bil_rate_date DATE,
127 x_project_bil_exchange_rate NUMBER,
128 x_projfunc_currency_code VARCHAR2,
129 x_projfunc_bil_rate_date_code VARCHAR2,
130 x_projfunc_bil_rate_type VARCHAR2,
131 x_projfunc_bil_rate_date DATE,
132 x_projfunc_bil_exchange_rate NUMBER,
133 x_funding_rate_date_code VARCHAR2,
134 x_funding_rate_type VARCHAR2,
135 x_funding_rate_date DATE,
136 x_funding_exchange_rate NUMBER,
137 x_baseline_funding_flag VARCHAR2,
138 x_projfunc_cost_rate_type VARCHAR2,
139 x_projfunc_cost_rate_date DATE,
140 x_multi_currency_billing_flag VARCHAR2,
141 x_inv_by_bill_trans_curr_flag VARCHAR2,
142 --MCA Sakthi for MultiAgreementCurrency Project
143 --MCA
144 x_assign_precedes_task VARCHAR2,
145 --MCA
146 --Structure
147 x_split_cost_from_wokplan_flag VARCHAR2,
148 x_split_cost_from_bill_flag VARCHAR2,
149 --Structure
150 --Advertisement
151 x_adv_action_set_id NUMBER,
152 x_start_adv_action_set_flag VARCHAR2,
153 --Advertisement
154 --Project Setup
155 x_priority_code VARCHAR2,
156 --Project Setup
157 --Retention
158 x_retn_billing_inv_format_id NUMBER,
159 x_retn_accounting_flag VARCHAR2,
160 --Retention
161 -- anlee
162 -- patchset K changes
163 x_revaluate_funding_flag VARCHAR2,
164 x_include_gains_losses_flag VARCHAR2,
165 -- msundare
166 x_security_level NUMBER,
167 x_labor_disc_reason_code VARCHAR2,
168 x_non_labor_disc_reason_code VARCHAR2,
169 -- End of changes
170 x_record_version_number NUMBER,
171 x_btc_cost_base_rev_code VARCHAR2, /* Bug#2638968 */
172 x_revtrans_currency_type VARCHAR2, /* R12 - Bug 4363092 */
173 --PA L
174 x_asset_allocation_method VARCHAR2,
175 x_capital_event_processing VARCHAR2,
176 x_cint_rate_sch_id NUMBER,
177 x_cint_eligible_flag VARCHAR2,
178 x_cint_stop_date DATE,
179 --FP_M Changes. Tracking Bug 3279981
180 x_en_top_task_customer_flag VARCHAR2,
181 x_en_top_task_inv_mth_flag VARCHAR2,
182 x_revenue_accrual_method VARCHAR2,
183 x_invoice_method VARCHAR2,
184 x_projfunc_attr_for_ar_flag VARCHAR2,
185 x_sys_program_flag VARCHAR2,
186 x_allow_multi_program_rollup VARCHAR2,
187 x_proj_req_res_format_id NUMBER,
188 x_proj_asgmt_res_format_id NUMBER,
189 x_date_eff_funds_flag VARCHAR2 --Bug 5511353
190 ,x_ar_rec_notify_flag VARCHAR2 -- 7508661 : EnC
191 ,x_auto_release_pwp_inv VARCHAR2 -- 7508661 : EnC
192 )
193 IS
194
195 l_return_status VARCHAR2(1);
196 BEGIN
197
198 UPDATE pa_projects
199 SET
200 project_id = X_Project_Id,
201 name = X_Name,
202 long_name = X_Long_Name,
203 segment1 = X_Segment1,
204 last_update_date = X_Last_Update_Date,
205 last_updated_by = X_Last_Updated_By,
206 last_update_login = X_Last_Update_Login,
207 project_type = X_Project_Type,
208 carrying_out_organization_id = X_Carrying_Out_Organization_Id,
209 public_sector_flag = X_Public_Sector_Flag,
210 -- Commented for Bug 3605235
211 --project_status_code = X_Project_Status_Code,
212 description = X_Description,
213 start_date = X_Start_Date,
214 completion_date = X_Completion_Date,
215 closed_date = X_Closed_Date,
216 distribution_rule = X_Distribution_Rule,
217 labor_invoice_format_id = X_Labor_Invoice_Format_Id,
218 non_labor_invoice_format_id = X_NL_Invoice_Format_Id,
219 retention_invoice_format_id = X_Retention_Invoice_Format_Id,
220 retention_percentage = X_Retention_Percentage,
221 billing_offset = X_Billing_Offset,
222 billing_cycle_id = X_Billing_Cycle_Id,
223 labor_std_bill_rate_schdl = X_Labor_Std_Bill_Rate_Schdl,
224 labor_bill_rate_org_id = X_Labor_Bill_Rate_Org_Id,
225 labor_schedule_fixed_date = X_Labor_Schedule_Fixed_Date,
226 labor_schedule_discount = X_Labor_Schedule_Discount,
227 non_labor_std_bill_rate_schdl = X_NL_Std_Bill_Rate_Schdl,
228 non_labor_bill_rate_org_id = X_NL_Bill_Rate_Org_Id,
229 non_labor_schedule_fixed_date = X_NL_Schedule_Fixed_Date,
230 non_labor_schedule_discount = X_NL_Schedule_Discount,
231 limit_to_txn_controls_flag = X_Limit_To_Txn_Controls_Flag,
232 project_level_funding_flag = X_Project_Level_Funding_Flag,
233 invoice_comment = X_Invoice_Comment,
234 unbilled_receivable_dr = X_Unbilled_Receivable_Dr,
235 unearned_revenue_cr = X_Unearned_Revenue_Cr,
236 summary_flag = X_Summary_Flag,
237 enabled_flag = X_Enabled_Flag,
238 segment2 = X_Segment2,
239 segment3 = X_Segment3,
240 segment4 = X_Segment4,
241 segment5 = X_Segment5,
242 segment6 = X_Segment6,
243 segment7 = X_Segment7,
244 segment8 = X_Segment8,
245 segment9 = X_Segment9,
246 segment10 = X_Segment10,
247 attribute_category = X_Attribute_Category,
248 attribute1 = X_Attribute1,
249 attribute2 = X_Attribute2,
250 attribute3 = X_Attribute3,
251 attribute4 = X_Attribute4,
252 attribute5 = X_Attribute5,
253 attribute6 = X_Attribute6,
254 attribute7 = X_Attribute7,
255 attribute8 = X_Attribute8,
256 attribute9 = X_Attribute9,
257 attribute10 = X_Attribute10,
258 cost_ind_rate_sch_id = X_Cost_Ind_Rate_Sch_Id,
259 rev_ind_rate_sch_id = X_Rev_Ind_Rate_Sch_Id,
263 inv_ind_sch_fixed_date = X_Inv_Ind_Sch_Fixed_Date,
260 inv_ind_rate_sch_id = X_Inv_Ind_Rate_Sch_Id,
261 cost_ind_sch_fixed_date = X_Cost_Ind_Sch_Fixed_Date,
262 rev_ind_sch_fixed_date = X_Rev_Ind_Sch_Fixed_Date,
264 labor_sch_type = X_Labor_Sch_Type,
265 non_labor_sch_type = X_Non_Labor_Sch_Type,
266 template_flag = X_Template_Flag,
267 verification_date = X_Verification_Date,
268 created_from_project_id = X_Created_From_Project_Id,
269 template_start_date_active = X_Template_Start_Date,
270 template_end_date_active = X_Template_End_Date,
271 --added to remove bug#594567 :ashia Bagai 6-jan-98
272 -- Commented for Bug 3605235
273 --wf_Status_Code = X_Wf_Status_Code,
274 --end of addition to remove bug#594567
275 Project_Currency_Code = X_Project_Currency_Code,
276 Allow_Cross_Charge_Flag = X_Allow_Cross_Charge_Flag,
277 Project_Rate_Date = X_Project_Rate_Date,
278 Project_Rate_Type = X_Project_Rate_Type,
279 Output_Tax_Code = X_Output_Tax_Code,
280 Retention_Tax_Code = X_Retention_Tax_Code,
281 CC_Process_Labor_Flag = X_CC_Process_Labor_Flag,
282 Labor_Tp_Schedule_Id = X_Labor_Tp_Schedule_Id,
283 Labor_Tp_Fixed_Date = X_Labor_Tp_Fixed_Date,
284 CC_Process_NL_Flag = X_CC_Process_NL_Flag,
285 Nl_Tp_Schedule_Id = X_Nl_Tp_Schedule_Id,
286 Nl_Tp_Fixed_Date = X_Nl_Tp_Fixed_Date,
287 CC_Tax_Task_Id = X_CC_Tax_Task_Id,
288 bill_job_group_id = X_bill_job_group_id,
289 cost_job_group_id = x_cost_job_group_id,
290 role_list_id = x_role_list_id,
291 work_type_id = x_work_type_id,
292 calendar_id = x_calendar_id,
293 location_id = x_location_id,
294 probability_member_id = x_probability_member_id,
295 project_value = x_project_value,
296 expected_approval_date = x_expected_approval_date,
297 initial_team_template_id = x_team_template_id,
298 job_bill_rate_schedule_id = x_job_bill_rate_schedule_id,
299 emp_bill_rate_schedule_id = x_emp_bill_rate_schedule_id,
300 --MCA Sakthi for MultiAgreementCurreny Project
301 competence_match_wt = x_competence_match_wt,
302 availability_match_wt = x_availability_match_wt,
303 job_level_match_wt = x_job_level_match_wt,
304 enable_automated_search = x_enable_automated_search,
305 search_min_availability = x_search_min_availability,
306 search_org_hier_id = x_search_org_hier_id,
307 search_starting_org_id = x_search_starting_org_id,
308 search_country_code = x_search_country_code,
309 min_cand_score_reqd_for_nom = x_min_cand_score_reqd_for_nom,
310 non_lab_std_bill_rt_sch_id = x_non_lab_std_bill_rt_sch_id,
311 invproc_currency_type = x_invproc_currency_type,
312 revproc_currency_code = x_revproc_currency_code,
313 project_bil_rate_date_code = x_project_bil_rate_date_code,
314 project_bil_rate_type = x_project_bil_rate_type,
315 project_bil_rate_date = x_project_bil_rate_date,
316 project_bil_exchange_rate = x_project_bil_exchange_rate,
317 projfunc_currency_code = x_projfunc_currency_code,
318 projfunc_bil_rate_date_code = x_projfunc_bil_rate_date_code,
319 projfunc_bil_rate_type = x_projfunc_bil_rate_type,
320 projfunc_bil_rate_date = x_projfunc_bil_rate_date,
321 projfunc_bil_exchange_rate = x_projfunc_bil_exchange_rate,
322 funding_rate_date_code = x_funding_rate_date_code,
323 funding_rate_type = x_funding_rate_type,
324 funding_rate_date = x_funding_rate_date,
325 funding_exchange_rate = x_funding_exchange_rate,
326 baseline_funding_flag = x_baseline_funding_flag,
327 multi_currency_billing_flag = x_multi_currency_billing_flag,
328 inv_by_bill_trans_curr_flag = x_inv_by_bill_trans_curr_flag,
329 projfunc_cost_rate_type = x_projfunc_cost_rate_type,
330 projfunc_cost_rate_date = x_projfunc_cost_rate_date,
331 --MCA Sakthi for MultiAgreementCurrency Project
332 --MCA
333 assign_precedes_task = x_assign_precedes_task,
334 --Structure
335 split_cost_from_workplan_flag = x_split_cost_from_wokplan_flag,
336 split_cost_from_bill_flag = x_split_cost_from_bill_flag,
337 --Structure
338 --Advertisement
339 adv_action_set_id = x_adv_action_set_id,
340 start_adv_action_set_flag = x_start_adv_action_set_flag,
341 --Advertisement
342 --Project Setup
343 priority_code = x_priority_code,
344 --Project Setup
345 --Retention
346 retn_billing_inv_format_id = x_retn_billing_inv_format_id,
347 retn_accounting_flag = x_retn_accounting_flag,
348 --Retention
349 -- anlee
350 -- patchset K changes
351 revaluate_funding_flag = x_revaluate_funding_flag,
352 include_gains_losses_flag = x_include_gains_losses_flag,
353 security_level = x_security_level,
354 labor_disc_reason_code = x_labor_disc_reason_code,
355 non_labor_disc_reason_code = x_non_labor_disc_reason_code,
356 -- End of changes
357 record_version_number = x_record_version_number,
358 btc_cost_base_rev_code = x_btc_cost_base_rev_code, /* Bug#2638968 */
359 revtrans_currency_type = x_revtrans_currency_type, /* R12 - Bug 4363092 */
360 --PA L
361 asset_allocation_method = x_asset_allocation_method,
362 capital_event_processing = x_capital_event_processing,
363 cint_rate_sch_id = x_cint_rate_sch_id,
364 cint_eligible_flag = x_cint_eligible_flag,
365 cint_stop_date = x_cint_stop_date,
366 --FP_M Changes. Tracking Bug 3279981
367 enable_top_task_customer_flag = x_en_top_task_customer_flag,
368 enable_top_task_inv_mth_flag = x_en_top_task_inv_mth_flag,
369 revenue_accrual_method = x_revenue_accrual_method,
370 invoice_method = x_invoice_method,
371 projfunc_attr_for_ar_flag = x_projfunc_attr_for_ar_flag,
372 sys_program_flag = x_sys_program_flag,
373 allow_multi_program_rollup=x_allow_multi_program_rollup,
374 proj_req_res_format_id =x_proj_req_res_format_id,
375 proj_asgmt_res_format_id =x_proj_asgmt_res_format_id,
376 date_eff_funds_consumption = x_date_eff_funds_flag -- Bug 5511353
377 ,ar_rec_notify_flag = x_ar_rec_notify_flag -- 7508661 : EnC
378 ,auto_release_pwp_inv = x_auto_release_pwp_inv -- 7508661 : EnC
379 WHERE rowid = X_Rowid;
380
381 if (SQL%NOTFOUND) then
382 Raise NO_DATA_FOUND;
383 end if;
384
385 -- anlee
386 -- Added for intermedia search
387 PA_PROJECT_CTX_SEARCH_PVT.UPDATE_ROW (
388 p_project_id => x_project_id
389 ,p_template_flag => x_template_flag
390 ,p_project_name => x_name
391 ,p_project_number => x_segment1
392 ,p_project_long_name => x_long_name
393 ,p_project_description => x_description
394 ,x_return_status => l_return_status );
395 -- anlee end of changes
396
397 END Update_Row;
398
399 PROCEDURE Delete_Row(X_project_id NUMBER) IS
400
401 CURSOR get_template_flag IS
402 SELECT template_flag
403 FROM PA_PROJECTS_ALL
404 WHERE project_id = x_project_id;
405
406 l_template_flag VARCHAR2(1);
407 l_return_status VARCHAR2(1);
408 BEGIN
409
410 -- anlee
411 -- Added for intermedia search
412 OPEN get_template_flag;
413 FETCH get_template_flag INTO l_template_flag;
414 CLOSE get_template_flag;
415
416 PA_PROJECT_CTX_SEARCH_PVT.DELETE_ROW (
417 p_project_id => x_project_id
418 ,p_template_flag => l_template_flag
419 ,x_return_status => l_return_status );
420 -- anlee end of changes
421
422 DELETE FROM pa_projects
423 WHERE project_id = X_project_id;
424
425 if (SQL%NOTFOUND) then
426 Raise NO_DATA_FOUND;
427 end if;
428
429 END Delete_Row;
430
431
432 END PA_PROJECTS_PKG1;