1 PACKAGE Pa_Planning_Resource_Pvt AUTHID CURRENT_USER AS
2 /* $Header: PAPRESVS.pls 120.3 2012/01/11 11:59:43 djambhek ship $*/
3
4 /*********************************************************
5 * Package : PA_PLANNING_RESOURCE_PVT
6 * Description : This Package contains the foll proc/func
7 * Check_pl_alias_unique, Create_Planning_Resource,
8 * Update_Planning_Resource, Delete_Planning_Resource
9 * The details are specified in the Body.
10 ********************************************************/
11 -- Standard who
12 g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
13 g_last_update_date DATE := SYSDATE;
14 g_creation_date DATE := SYSDATE;
15 g_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
16 g_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
17
18 -- Global variable for token in error messages for add multiple/single
19 -- and AMG API's
20
21 g_token VARCHAR2(1000) := null;
22 g_amg_flow VARCHAR2(1) := 'N';
23
24 /***************************************************************
25 * Function : Check_pl_alias_unique
26 * Description : This Function is used to check the
27 * uniqueness of the resource alias if it is not null.
28 * Further details are specified in the Body.
29 *************************************************************/
30 FUNCTION Check_pl_alias_unique(
31 p_resource_list_id IN VARCHAR2,
32 p_resource_alias IN VARCHAR2,
33 p_resource_list_member_id IN VARCHAR2,
34 p_object_type IN VARCHAR2,
35 p_object_id IN NUMBER)
36 RETURN VARCHAR2;
37
38 /**************************************************************
39 * Procedure : Create_Planning_Resource
40 * Description : The purpose of this procedure is to Validate
41 * and create a new planning resource for a
42 * resource list.
43 * Further details specified in the Body.
44 * The reason why the p_resource_list_member_id is being
45 * passed as an IN parameter is because the Mapping
46 * Algorithm is going to pass the resource_list_member_id
47 * in Bulk while calling this proc(instead of using the sequence)
48 ****************************************************************/
49 PROCEDURE Create_Planning_Resource(
50 p_resource_list_member_id IN NUMBER DEFAULT NULL,
51 p_resource_list_id IN VARCHAR2,
52 p_resource_alias IN VARCHAR2 DEFAULT NULL,
53 p_person_id IN NUMBER DEFAULT NULL,
54 p_person_name IN VARCHAR2 DEFAULT NULL,
55 p_job_id IN NUMBER DEFAULT NULL,
56 p_job_name IN VARCHAR2 DEFAULT NULL,
57 p_organization_id IN NUMBER DEFAULT NULL,
58 p_organization_name IN VARCHAR2 DEFAULT NULL,
59 p_vendor_id IN NUMBER DEFAULT NULL,
60 p_vendor_name IN VARCHAR2 DEFAULT NULL,
61 p_fin_category_name IN VARCHAR2 DEFAULT NULL,
62 p_non_labor_resource IN VARCHAR2 DEFAULT NULL,
63 p_project_role_id IN NUMBER DEFAULT NULL,
64 p_project_role_name IN VARCHAR2 DEFAULT NULL,
65 p_resource_class_id IN NUMBER DEFAULT NULL,
66 p_resource_class_code IN VARCHAR2 DEFAULT NULL,
67 p_res_format_id IN NUMBER ,
68 p_spread_curve_id IN NUMBER DEFAULT NULL,
69 p_etc_method_code IN VARCHAR2 DEFAULT NULL,
70 p_mfc_cost_type_id IN NUMBER DEFAULT NULL,
71 p_copy_from_rl_flag IN VARCHAR2 DEFAULT NULL,
72 p_resource_class_flag IN VARCHAR2 DEFAULT NULL,
73 p_fc_res_type_code IN VARCHAR2 DEFAULT NULL,
74 p_inventory_item_id IN NUMBER DEFAULT NULL,
75 p_inventory_item_name IN VARCHAR2 DEFAULT NULL,
76 p_item_category_id IN NUMBER DEFAULT NULL,
77 p_item_category_name IN VARCHAR2 DEFAULT NULL,
78 p_migration_code IN VARCHAR2 DEFAULT 'N',
79 p_attribute_category IN VARCHAR2 DEFAULT NULL,
80 p_attribute1 IN VARCHAR2 DEFAULT NULL,
81 p_attribute2 IN VARCHAR2 DEFAULT NULL,
82 p_attribute3 IN VARCHAR2 DEFAULT NULL,
83 p_attribute4 IN VARCHAR2 DEFAULT NULL,
84 p_attribute5 IN VARCHAR2 DEFAULT NULL,
85 p_attribute6 IN VARCHAR2 DEFAULT NULL,
86 p_attribute7 IN VARCHAR2 DEFAULT NULL,
87 p_attribute8 IN VARCHAR2 DEFAULT NULL,
88 p_attribute9 IN VARCHAR2 DEFAULT NULL,
89 p_attribute10 IN VARCHAR2 DEFAULT NULL,
90 p_attribute11 IN VARCHAR2 DEFAULT NULL,
91 p_attribute12 IN VARCHAR2 DEFAULT NULL,
92 p_attribute13 IN VARCHAR2 DEFAULT NULL,
93 p_attribute14 IN VARCHAR2 DEFAULT NULL,
94 p_attribute15 IN VARCHAR2 DEFAULT NULL,
95 p_attribute16 IN VARCHAR2 DEFAULT NULL,
96 p_attribute17 IN VARCHAR2 DEFAULT NULL,
97 p_attribute18 IN VARCHAR2 DEFAULT NULL,
98 p_attribute19 IN VARCHAR2 DEFAULT NULL,
99 p_attribute20 IN VARCHAR2 DEFAULT NULL,
100 p_attribute21 IN VARCHAR2 DEFAULT NULL,
101 p_attribute22 IN VARCHAR2 DEFAULT NULL,
102 p_attribute23 IN VARCHAR2 DEFAULT NULL,
103 p_attribute24 IN VARCHAR2 DEFAULT NULL,
104 p_attribute25 IN VARCHAR2 DEFAULT NULL,
105 p_attribute26 IN VARCHAR2 DEFAULT NULL,
106 p_attribute27 IN VARCHAR2 DEFAULT NULL,
107 p_attribute28 IN VARCHAR2 DEFAULT NULL,
108 p_attribute29 IN VARCHAR2 DEFAULT NULL,
109 p_attribute30 IN VARCHAR2 DEFAULT NULL,
110 p_person_type_code IN VARCHAR2 DEFAULT NULL,
111 p_bom_resource_id IN NUMBER DEFAULT NULL,
112 p_bom_resource_name IN VARCHAR2 DEFAULT NULL,
113 p_team_role IN VARCHAR2 DEFAULT NULL,
114 --p_named_role IN VARCHAR2 DEFAULT NULL,
115 p_incur_by_res_code IN VARCHAR2 DEFAULT NULL,
116 p_incur_by_res_type IN VARCHAR2 DEFAULT NULL,
117 p_project_id IN NUMBER DEFAULT NULL,
118 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE, -- Added for bug#4350589
119 p_expenditure_type_2 IN VARCHAR2 DEFAULT NULL, -- Added for Bug13535688 & 13546557 for CBS project
120 x_resource_list_member_id OUT NOCOPY NUMBER ,
121 x_record_version_number OUT NOCOPY NUMBER ,
122 x_return_status OUT NOCOPY VARCHAR2 ,
123 x_msg_count OUT NOCOPY NUMBER ,
124 x_error_msg_data OUT NOCOPY VARCHAR2 );
125
126 /***************************************************
127 * Procedure : Update_Planning_Resource
128 * Description : The purpose of this procedure is to
129 * Validate and update attributes on an existing
130 * planning resource for a resource list.
131 * Further details in the Body.
132 ***************************************************/
133 PROCEDURE Update_Planning_Resource(
134 p_resource_list_id IN NUMBER,
135 p_resource_list_member_id IN NUMBER,
136 p_enabled_flag IN VARCHAR2,
137 p_resource_alias IN VARCHAR2 ,
138 p_spread_curve_id IN NUMBER DEFAULT NULL,
139 p_etc_method_code IN VARCHAR2 DEFAULT NULL,
140 p_mfc_cost_type_id IN NUMBER DEFAULT NULL,
141 p_attribute_category IN VARCHAR2 DEFAULT NULL,
142 p_attribute1 IN VARCHAR2 DEFAULT NULL,
143 p_attribute2 IN VARCHAR2 DEFAULT NULL,
144 p_attribute3 IN VARCHAR2 DEFAULT NULL,
145 p_attribute4 IN VARCHAR2 DEFAULT NULL,
146 p_attribute5 IN VARCHAR2 DEFAULT NULL,
147 p_attribute6 IN VARCHAR2 DEFAULT NULL,
148 p_attribute7 IN VARCHAR2 DEFAULT NULL,
149 p_attribute8 IN VARCHAR2 DEFAULT NULL,
150 p_attribute9 IN VARCHAR2 DEFAULT NULL,
151 p_attribute10 IN VARCHAR2 DEFAULT NULL,
152 p_attribute11 IN VARCHAR2 DEFAULT NULL,
153 p_attribute12 IN VARCHAR2 DEFAULT NULL,
154 p_attribute13 IN VARCHAR2 DEFAULT NULL,
155 p_attribute14 IN VARCHAR2 DEFAULT NULL,
156 p_attribute15 IN VARCHAR2 DEFAULT NULL,
157 p_attribute16 IN VARCHAR2 DEFAULT NULL,
158 p_attribute17 IN VARCHAR2 DEFAULT NULL,
159 p_attribute18 IN VARCHAR2 DEFAULT NULL,
160 p_attribute19 IN VARCHAR2 DEFAULT NULL,
161 p_attribute20 IN VARCHAR2 DEFAULT NULL,
162 p_attribute21 IN VARCHAR2 DEFAULT NULL,
163 p_attribute22 IN VARCHAR2 DEFAULT NULL,
164 p_attribute23 IN VARCHAR2 DEFAULT NULL,
165 p_attribute24 IN VARCHAR2 DEFAULT NULL,
166 p_attribute25 IN VARCHAR2 DEFAULT NULL,
167 p_attribute26 IN VARCHAR2 DEFAULT NULL,
168 p_attribute27 IN VARCHAR2 DEFAULT NULL,
169 p_attribute28 IN VARCHAR2 DEFAULT NULL,
170 p_attribute29 IN VARCHAR2 DEFAULT NULL,
171 p_attribute30 IN VARCHAR2 DEFAULT NULL,
172 p_record_version_number IN NUMBER,
173 p_expenditure_type_2 IN VARCHAR2 DEFAULT NULL, -- Added for Bug13535688 & 13546557 for CBS project
174 x_record_version_number OUT NOCOPY NUMBER ,
175 x_return_status OUT NOCOPY VARCHAR2 ,
176 x_msg_count OUT NOCOPY NUMBER ,
177 x_error_msg_data OUT NOCOPY VARCHAR2 );
178
179 /*************************************************
180 * Procedure : Delete_Planning_Resource
181 * Description : The purpose of this procedure is to
182 * delete a planning resource if it is not
183 * being used, else disable it.
184 * Further details in the Body.
185 ***************************************************/
186 PROCEDURE Delete_Planning_Resource(
187 p_resource_list_member_id IN NUMBER,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_error_msg_data OUT NOCOPY VARCHAR2);
191
192
193 PROCEDURE Copy_Planning_Resources(
194 p_source_resource_list_id IN Number,
195 p_destination_resource_list_id IN Number,
196 p_src_res_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
197 x_dest_res_list_member_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
198 p_destination_project_id IN Number DEFAULT NULL);
199
200
201
202 END Pa_Planning_Resource_Pvt;