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