[Home] [Help]
PACKAGE: APPS.CN_COMP_PLAN_PVT
Source
1 PACKAGE CN_COMP_PLAN_PVT AS
2 /*$Header: cnvcmpns.pls 120.5 2007/03/26 09:01:03 hanaraya ship $*/
3
4 -- comp plan
5 TYPE comp_plan_rec_type IS RECORD
6 (
7 COMP_PLAN_ID CN_COMP_PLANS.COMP_PLAN_ID%TYPE := CN_API.G_MISS_ID,
8 NAME CN_COMP_PLANS.NAME%TYPE := FND_API.G_MISS_CHAR,
9 VERSION CN_COMP_PLANS.VERSION%TYPE := FND_API.G_MISS_CHAR,
10 DESCRIPTION CN_COMP_PLANS.DESCRIPTION%TYPE := FND_API.G_MISS_CHAR,
11 STATUS_CODE CN_COMP_PLANS.STATUS_CODE%TYPE := FND_API.G_MISS_CHAR,
12 COMPLETE_FLAG CN_COMP_PLANS.COMPLETE_FLAG%TYPE := FND_API.G_MISS_CHAR,
13 ON_QUOTA_DATE CN_COMP_PLANS.ON_QUOTA_DATE%TYPE := FND_API.G_MISS_DATE,
14 ALLOW_REV_CLASS_OVERLAP CN_COMP_PLANS.ALLOW_REV_CLASS_OVERLAP%TYPE := FND_API.G_MISS_CHAR,
15 START_DATE CN_COMP_PLANS.START_DATE%TYPE := FND_API.G_MISS_DATE,
16 END_DATE CN_COMP_PLANS.END_DATE%TYPE := FND_API.G_MISS_DATE,
17 ATTRIBUTE_CATEGORY CN_COMP_PLANS.ATTRIBUTE_CATEGORY%TYPE := FND_API.G_MISS_CHAR,
18 ATTRIBUTE1 CN_COMP_PLANS.ATTRIBUTE1%TYPE := FND_API.G_MISS_CHAR,
19 ATTRIBUTE2 CN_COMP_PLANS.ATTRIBUTE2%TYPE := FND_API.G_MISS_CHAR,
20 ATTRIBUTE3 CN_COMP_PLANS.ATTRIBUTE3%TYPE := FND_API.G_MISS_CHAR,
21 ATTRIBUTE4 CN_COMP_PLANS.ATTRIBUTE4%TYPE := FND_API.G_MISS_CHAR,
22 ATTRIBUTE5 CN_COMP_PLANS.ATTRIBUTE5%TYPE := FND_API.G_MISS_CHAR,
23 ATTRIBUTE6 CN_COMP_PLANS.ATTRIBUTE6%TYPE := FND_API.G_MISS_CHAR,
24 ATTRIBUTE7 CN_COMP_PLANS.ATTRIBUTE7%TYPE := FND_API.G_MISS_CHAR,
25 ATTRIBUTE8 CN_COMP_PLANS.ATTRIBUTE8%TYPE := FND_API.G_MISS_CHAR,
26 ATTRIBUTE9 CN_COMP_PLANS.ATTRIBUTE9%TYPE := FND_API.G_MISS_CHAR,
27 ATTRIBUTE10 CN_COMP_PLANS.ATTRIBUTE10%TYPE := FND_API.G_MISS_CHAR,
28 ATTRIBUTE11 CN_COMP_PLANS.ATTRIBUTE11%TYPE := FND_API.G_MISS_CHAR,
29 ATTRIBUTE12 CN_COMP_PLANS.ATTRIBUTE12%TYPE := FND_API.G_MISS_CHAR,
30 ATTRIBUTE13 CN_COMP_PLANS.ATTRIBUTE13%TYPE := FND_API.G_MISS_CHAR,
31 ATTRIBUTE14 CN_COMP_PLANS.ATTRIBUTE14%TYPE := FND_API.G_MISS_CHAR,
32 ATTRIBUTE15 CN_COMP_PLANS.ATTRIBUTE15%TYPE := FND_API.G_MISS_CHAR,
33 OBJECT_VERSION_NUMBER CN_COMP_PLANS.OBJECT_VERSION_NUMBER%TYPE := NULL,
34 ORG_ID CN_COMP_PLANS.ORG_ID%TYPE := NULL /* ADDED OAFWK */
35 ) ;
36
37 TYPE comp_plan_tbl_type IS
38 TABLE OF comp_plan_rec_type INDEX BY BINARY_INTEGER ;
39
40 -- Global variable that represent missing values.
41
42 G_MISS_COMP_PLAN_REC comp_plan_rec_type;
43 G_MISS_COMP_PLAN_REC_TB comp_plan_tbl_type;
44
45 -- sales role
46 TYPE sales_role_rec_type IS RECORD
47 (
48 ROLE_PLAN_ID CN_ROLE_PLANS.ROLE_PLAN_ID%TYPE := CN_API.G_MISS_ID,
49 ROLE_ID CN_ROLE_PLANS.ROLE_ID%TYPE := CN_API.G_MISS_ID,
50 COMP_PLAN_ID CN_ROLE_PLANS.COMP_PLAN_ID%TYPE := CN_API.G_MISS_ID,
51 NAME CN_ROLES.NAME%TYPE := FND_API.G_MISS_CHAR,
52 DESCRIPTION CN_ROLES.DESCRIPTION%TYPE := FND_API.G_MISS_CHAR,
53 START_DATE CN_ROLE_PLANS.START_DATE%TYPE := FND_API.G_MISS_DATE,
54 END_DATE CN_ROLE_PLANS.END_DATE%TYPE := FND_API.G_MISS_DATE,
55 OBJECT_VERSION_NUMBER CN_ROLE_PLANS.OBJECT_VERSION_NUMBER%TYPE := NULL
56 ) ;
57
58 TYPE sales_role_tbl_type IS
59 TABLE OF sales_role_rec_type INDEX BY BINARY_INTEGER ;
60
61 -- salespeople assigned
62 TYPE srp_plan_assign_rec_type IS RECORD
63 (srp_plan_assign_id CN_SRP_PLAN_ASSIGNS.SRP_PLAN_ASSIGN_ID%TYPE,
64 salesrep_id CN_SRP_PLAN_ASSIGNS.SALESREP_ID%TYPE,
65 role_id CN_SRP_PLAN_ASSIGNS.ROLE_ID%TYPE,
66 role_name CN_ROLES.NAME%TYPE,
67 salesrep_name CN_SALESREPS.NAME%TYPE,
68 employee_number CN_SALESREPS.EMPLOYEE_NUMBER%TYPE,
69 start_date CN_SRP_PLAN_ASSIGNS.START_DATE%TYPE,
70 end_date CN_SRP_PLAN_ASSIGNS.END_DATE%TYPE);
71
72 TYPE srp_plan_assign_tbl_type IS
73 TABLE OF srp_plan_assign_rec_type INDEX BY BINARY_INTEGER;
74
75 -- Global variable that represent missing values.
76
77 G_MISS_SALES_ROLE_REC sales_role_rec_type;
78 G_MISS_SALES_ROLE_REC_TB sales_role_tbl_type;
79
80 -- Start of comments
81 -- API name : Create_Comp_Plan
82 -- Type : Private.
83 -- Function :
84 -- Pre-reqs : None.
85 -- Parameters :
86 -- IN : p_api_version IN NUMBER Required
87 -- p_init_msg_list IN VARCHAR2 Optional
88 -- Default = FND_API.G_FALSE
89 -- p_commit IN VARCHAR2 Optional
90 -- Default = FND_API.G_FALSE
91 -- p_validation_level IN NUMBER Optional
92 -- Default = FND_API.G_VALID_LEVEL_FULL
93 -- p_comp_plan IN comp_plan_rec_type
94 -- OUT : x_return_status OUT VARCHAR2(1)
95 -- x_msg_count OUT NUMBER
96 -- x_msg_data OUT VARCHAR2(2000)
97 -- x_comp_plan_id OUT NUMBER
98 -- Version : Current version 1.0
99 -- Notes : Note text
100 --
101 -- End of comments
102 PROCEDURE Create_Comp_Plan
103 (p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
105 p_commit IN VARCHAR2 := FND_API.G_FALSE,
106 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
107 p_comp_plan IN OUT NOCOPY comp_plan_rec_type,
108 x_comp_plan_id OUT NOCOPY NUMBER,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_count OUT NOCOPY NUMBER,
111 x_msg_data OUT NOCOPY VARCHAR2);
112
113 -- Start of comments
114 -- API name : Update_Comp_Plan
115 -- Type : Private.
116 -- Function :
117 -- Pre-reqs : None.
118 -- Parameters :
119 -- IN : p_api_version IN NUMBER Required
120 -- p_init_msg_list IN VARCHAR2 Optional
121 -- Default = FND_API.G_FALSE
122 -- p_commit IN VARCHAR2 Optional
123 -- Default = FND_API.G_FALSE
124 -- p_validation_level IN NUMBER Optional
125 -- Default = FND_API.G_VALID_LEVEL_FULL
126 -- p_comp_plan IN comp_plan_rec_type
127 -- OUT : x_return_status OUT VARCHAR2(1)
128 -- x_msg_count OUT NUMBER
129 -- x_msg_data OUT VARCHAR2(2000)
130 -- Version : Current version 1.0
131 -- Notes : Note text
132 --
133 -- End of comments
134 PROCEDURE Update_Comp_Plan
135 (p_api_version IN NUMBER,
136 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
137 p_commit IN VARCHAR2 := FND_API.G_FALSE,
138 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
139 p_comp_plan IN OUT NOCOPY comp_plan_rec_type,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2 );
143
144 -- Start of comments
145 -- API name : Delete_Comp_Plan
146 -- Type : Private.
147 -- Function :
148 -- Pre-reqs : None.
149 -- Parameters :
150 -- IN : p_api_version IN NUMBER Required
151 -- p_init_msg_list IN VARCHAR2 Optional
152 -- Default = FND_API.G_FALSE
153 -- p_commit IN VARCHAR2 Optional
154 -- Default = FND_API.G_FALSE
155 -- p_validation_level IN NUMBER Optional
156 -- Default = FND_API.G_VALID_LEVEL_FULL
157 -- p_comp_plan IN comp_plan_rec_type
158 -- OUT : x_return_status OUT VARCHAR2(1)
159 -- x_msg_count OUT NUMBER
160 -- x_msg_data OUT VARCHAR2(2000)
161 -- Version : Current version 1.0
162 -- Notes : Note text
163 --
164 -- End of comments
165 PROCEDURE Delete_Comp_Plan
166 (p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
168 p_commit IN VARCHAR2 := FND_API.G_FALSE,
169 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
170 p_comp_plan IN OUT NOCOPY comp_plan_rec_type,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2);
174
175 -- Start of comments
176 -- API name : Get_Comp_Plan_Sum
177 -- Type : Private.
178 -- Function :
179 -- Pre-reqs : None.
180 -- Parameters :
181 -- IN : p_api_version IN NUMBER Required
182 -- p_init_msg_list IN VARCHAR2 Optional
183 -- Default = FND_API.G_FALSE
184 -- p_commit IN VARCHAR2 Optional
185 -- Default = FND_API.G_FALSE
186 -- p_validation_level IN NUMBER Optional
187 -- Default = FND_API.G_VALID_LEVEL_FULL
188 -- p_start_record IN NUMBER
189 -- Default = -1
190 -- p_fetch_size IN NUMBER
191 -- Default = -1
192 -- p_search_name IN VARCHAR2
193 -- Default = '%'
194 -- p_search_date IN DATE
195 -- Default = FND_API.G_MISS_DATE
196 -- p_search_status IN VARCHAR2
197 -- Default = FND_API.G_MISS_CHAR
198 -- OUT : x_return_status OUT VARCHAR2(1)
199 -- x_msg_count OUT NUMBER
200 -- x_msg_data OUT VARCHAR2(2000)
201 -- x_comp_plan OUT comp_plan_tbl_type
202 -- x_total_record OUT NUMBER
203 -- Version : Current version 1.0
204 -- Notes : Note text
205 --
206 -- End of comments
207 PROCEDURE Get_Comp_Plan_Sum
208 (p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
210 p_commit IN VARCHAR2 := FND_API.G_FALSE,
211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
212 p_start_record IN NUMBER := -1,
213 p_fetch_size IN NUMBER := -1,
214 p_search_name IN VARCHAR2 := '%',
215 p_search_date IN DATE := FND_API.G_MISS_DATE,
216 p_search_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
217 x_comp_plan OUT NOCOPY comp_plan_tbl_type,
218 x_total_record OUT NOCOPY NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2);
222
223
224 -- Start of comments
225 -- API name : Get_Comp_Plan_Dtl
226 -- Type : Private.
227 -- Function :
228 -- Pre-reqs : None.
229 -- Parameters :
230 -- IN : p_api_version IN NUMBER Required
231 -- p_init_msg_list IN VARCHAR2 Optional
232 -- Default = FND_API.G_FALSE
233 -- p_commit IN VARCHAR2 Optional
234 -- Default = FND_API.G_FALSE
235 -- p_validation_level IN NUMBER Optional
236 -- Default = FND_API.G_VALID_LEVEL_FULL
237 -- p_comp_plan_id IN NUMBER
238 -- OUT : x_return_status OUT VARCHAR2(1)
239 -- x_msg_count OUT NUMBER
240 -- x_msg_data OUT VARCHAR2(2000)
241 -- x_comp_plan OUT comp_plan_rec_type
242 -- Version : Current version 1.0
243 -- Notes : Note text
244 --
245 -- End of comments
246 PROCEDURE Get_Comp_Plan_Dtl
247 (p_api_version IN NUMBER,
248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
249 p_commit IN VARCHAR2 := FND_API.G_FALSE,
250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
251 p_comp_plan_id IN NUMBER,
252 x_comp_plan OUT NOCOPY comp_plan_tbl_type,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2);
256
257
258 -- Start of comments
259 -- API name : Get_Sales_Role
260 -- Type : Private.
261 -- Function :
262 -- Pre-reqs : None.
263 -- Parameters :
264 -- IN : p_api_version IN NUMBER Required
265 -- p_init_msg_list IN VARCHAR2 Optional
266 -- Default = FND_API.G_FALSE
267 -- p_commit IN VARCHAR2 Optional
268 -- Default = FND_API.G_FALSE
269 -- p_validation_level IN NUMBER Optional
270 -- Default = FND_API.G_VALID_LEVEL_FULL
271 -- p_comp_plan_id IN NUMBER
272 -- OUT : x_return_status OUT VARCHAR2(1)
273 -- x_msg_count OUT NUMBER
274 -- x_msg_data OUT VARCHAR2(2000)
275 -- x_sales_role OUT sales_role_rec_type
276 -- Version : Current version 1.0
277 -- Notes : Note text
278 --
279 -- End of comments
280 PROCEDURE Get_Sales_Role
281 (p_api_version IN NUMBER,
282 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
286 x_sales_role OUT NOCOPY sales_role_tbl_type,
283 p_commit IN VARCHAR2 := FND_API.G_FALSE,
284 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
285 p_comp_plan_id IN NUMBER,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2);
290
291 -- Start of comments
292 -- API name : Validate_Comp_Plan
293 -- Type : Private.
294 -- Function :
295 -- Pre-reqs : None.
296 -- Parameters :
297 -- IN : p_api_version IN NUMBER Required
298 -- p_init_msg_list IN VARCHAR2 Optional
299 -- Default = FND_API.G_FALSE
300 -- p_commit IN VARCHAR2 Optional
301 -- Default = FND_API.G_FALSE
302 -- p_validation_level IN NUMBER Optional
303 -- Default = FND_API.G_VALID_LEVEL_FULL
304 -- p_comp_plan IN comp_plan_rec_type
305 -- OUT : x_return_status OUT VARCHAR2(1)
306 -- x_msg_count OUT NUMBER
307 -- x_msg_data OUT VARCHAR2(2000)
308 -- Version : Current version 1.0
309 -- Notes : Note text
310 --
311 -- End of comments
312 PROCEDURE Validate_Comp_Plan
313 (p_api_version IN NUMBER,
314 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
315 p_commit IN VARCHAR2 := FND_API.G_FALSE,
316 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
317 p_comp_plan IN comp_plan_rec_type,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2);
321
322 --| ---------------------------------------------------------------------+
323 --| Procedure Name : check_revenue_class_overlap
324 --| Desc : Pass in Comp Plan ID
325 --| pass in Comp Plan Name
326 --| pass in p_loading_status
327 --| out x_loading_status
328 --| out x_return_status
329 --| ---------------------------------------------------------------------+
330 PROCEDURE check_revenue_class_overlap
331 (
332 p_comp_plan_id IN NUMBER,
333 p_rc_overlap IN VARCHAR2,
334 p_loading_status IN VARCHAR2,
335 x_loading_status OUT NOCOPY VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2 );
337
338 -- Get salespeople assigned to the plan
339 PROCEDURE Get_Assigned_Salesreps
340 (p_comp_plan_id IN NUMBER,
341 p_range_low IN NUMBER,
342 p_range_high IN NUMBER,
343 x_total_rows OUT NOCOPY NUMBER,
344 x_result_tbl OUT NOCOPY srp_plan_assign_tbl_type);
345
346 -- =====================================================
347 -- || Procedure: Duplicate_Comp_plan
348 -- || Description: This Procedure creates a copy of Compplan
349 -- || in the same Instance and Operating Unit.
350 -- || This is a Shallow Copy means Children components
351 -- || are not copied. Children components from the
352 -- || original Compplan will point to this new
353 -- || Compplan.
354 -- =====================================================
355 PROCEDURE duplicate_comp_plan (
356 p_api_version IN NUMBER,
357 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
358 p_commit IN VARCHAR2 := FND_API.G_FALSE,
359 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
360 p_comp_plan_id IN CN_COMP_PLANS.COMP_PLAN_ID%TYPE,
361 p_org_id IN NUMBER,
362 x_return_status OUT NOCOPY VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364 x_msg_data OUT NOCOPY VARCHAR2,
365 x_comp_plan_id OUT NOCOPY CN_COMP_PLANS.COMP_PLAN_ID%TYPE);
366
367 END CN_COMP_PLAN_PVT;