1 PACKAGE CN_ROLE_PLANS_PUB AUTHID CURRENT_USER AS
2 /* $Header: cnprlpls.pls 120.1 2005/07/08 04:42:00 appldev ship $ */
3 /*#
4 * There are three APIs for CN_ROLE_PLANS_PUB.
5 * Create Role Plans: This procedure creates a Role-Plan Assignment. Records are inserted into cn_role_plans.
6 * There is a call to procedure cn_srp_plan_assign_pvt.create_srp_plan_assigns. This inserts records into
7 * cn_srp_plan_Assigns for all salesreps with the role. This results in records being created in
8 * cn_srp_quota_assigns, cn_srp_period_quotas, cn_Srp_periods, cn_srp_rollover_quotas(if exists),
9 * cn_srp_quota_rules, and cn_srp_rate_assigns.
10 *
11 * Update Role Plans: This procedure updates Role-Plan Assignment. Records are updated in cn_role_plans.
12 * There is a call to procedure .cn_srp_plan_assign_pvt.update_srp_plan_assigns. This results in update to
13 * cn_srp_quota_assigns, cn_srp_rate_tiers, cn_srp_periods, cn_srp_rollover, quotas.
14 *
15 * Delete Role Plans: This procedure deletes the Role-Plan assignment from cn_role_plans. This results in records being deleted from cn_srp_plan_assigns, cn_srp_quota_Assigns, and cn_srp_rate_tiers.
16 * @rep:scope public
17 * @rep:product CN
18 * @rep:displayname Role to Compensation Plan Assignment
19 * @rep:lifecycle active
20 * @rep:compatibility S
21 * @rep:category BUSINESS_ENTITY CN_COMP_PLANS
22 */
23
24 --
25 -- User defined Record Type
26 --
27 -- Note: form bug 725654 for G_MISS
28
29 TYPE role_plan_rec_type IS RECORD
30 (
31 role_name cn_roles.name%TYPE := cn_api.G_MISS_CHAR,
32 role_id cn_role_plans.role_id%TYPE := NULL,
33 comp_plan_name cn_comp_plans.name%TYPE := cn_api.G_MISS_CHAR,
34 comp_plan_id cn_role_plans.comp_plan_id%TYPE := NULL,
35 start_date cn_role_plans.start_date%TYPE := cn_api.G_MISS_DATE,
36 end_date cn_role_plans.end_date%TYPE := cn_api.G_MISS_DATE,
37 attribute_category cn_role_plans.attribute_category%TYPE := cn_api.G_MISS_CHAR,
41 attribute4 cn_role_plans.attribute4%TYPE := cn_api.G_MISS_CHAR,
38 attribute1 cn_role_plans.attribute1%TYPE := cn_api.G_MISS_CHAR,
39 attribute2 cn_role_plans.attribute2%TYPE := cn_api.G_MISS_CHAR,
40 attribute3 cn_role_plans.attribute3%TYPE := cn_api.G_MISS_CHAR,
42 attribute5 cn_role_plans.attribute5%TYPE := cn_api.G_MISS_CHAR,
43 attribute6 cn_role_plans.attribute6%TYPE := cn_api.G_MISS_CHAR,
44 attribute7 cn_role_plans.attribute7%TYPE := cn_api.G_MISS_CHAR,
45 attribute8 cn_role_plans.attribute8%TYPE := cn_api.G_MISS_CHAR,
46 attribute9 cn_role_plans.attribute9%TYPE := cn_api.G_MISS_CHAR,
47 attribute10 cn_role_plans.attribute10%TYPE := cn_api.G_MISS_CHAR,
48 attribute11 cn_role_plans.attribute11%TYPE := cn_api.G_MISS_CHAR,
49 attribute12 cn_role_plans.attribute12%TYPE := cn_api.G_MISS_CHAR,
50 attribute13 cn_role_plans.attribute13%TYPE := cn_api.G_MISS_CHAR,
51 attribute14 cn_role_plans.attribute14%TYPE := cn_api.G_MISS_CHAR,
52 attribute15 cn_role_plans.attribute15%TYPE := cn_api.G_MISS_CHAR,
53 object_version_number cn_role_plans.object_version_number%TYPE := NULL,
54 org_id cn_role_plans.org_id%TYPE := NULL
55
56 );
57
58 --
59 -- User defined Record Table Type
60 --
61 TYPE role_plan_rec_tbl_type IS TABLE OF role_plan_rec_type
62 INDEX BY BINARY_INTEGER;
63
64 --
65 -- Global variable that represent missing values.
66 --
67 G_MISS_ROLE_PLAN_REC role_plan_rec_type;
68 G_MISS_ROLE_PLAN_TBL role_plan_rec_tbl_type;
69 G_ROLE_NAME CONSTANT VARCHAR2(80)
70 := cn_api.get_lkup_meaning('ROLE_NAME','ROLE_OBJECT_TYPE');
71 G_CP_NAME CONSTANT VARCHAR2(80)
72 := cn_api.get_lkup_meaning('CP_NAME','CP_OBJECT_TYPE');
73 G_START_DATE CONSTANT VARCHAR2(80)
74 := cn_api.get_lkup_meaning('START_DATE','CN_OBJECT_TYPE');
75
76 -- Start of Comments
77 -- API name : Create_Role_Plan
78 -- Type : Public.
79 -- Pre-reqs : None.
80 -- Usage : Procedure to create a sales role and comp plan assignment.
81 -- Parameters :
82 -- IN : p_api_version IN NUMBER Require
83 -- : p_init_msg_list IN VARCHAR2 Optional
84 -- Default = FND_API.G_FALSE
85 -- : p_commit IN VARCHAR2 Optional
86 -- Default = FND_API.G_FALSE
87 -- : p_validation_level IN NUMBER Optional
88 -- Default = FND_API.G_VALID_LEVEL_FULL
89 -- : p_role_plan_rec IN ROLE_PLAN_REC_TYPE
90 -- OUT : x_return_status OUT VARCHAR2(1)
91 -- : x_msg_count OUT NUMBER
92 -- : x_msg_data OUT VARCHAR2(2000)
93 -- : x_loading_status OUT VARCHAR2
94 --
95 --
96 -- Version : Current version 1.0
97 -- Initial version 1.0
98 --
99 -- Description : This procedure is used to create a sales role and comp plan assignment.
100 -- Notes : 1. Role name can not be missing or null.
101 -- 2. Comp plan name can not be missing or null.
102 -- 3. Start_date can not be missing or null.
103 -- 4. Start_date <= end_date, if end_date is not null.
104 -- 5. Role name must exist in cn_roles already.
105 -- 6. Comp_plan_name must exist in cn_comp_plans already.
106 -- 7. Date range (start_date, en_date) of the assignment must be
107 -- within the date range (start_date, end_date) of the comp plan.
108 -- 8. No comp plan overlap for the any same sales role.
109 -- In other words, you can not have more than one comp plan for
110 -- the same role at the same time.
111 -- 9. Gap between two comp plans for the same role is allowed.
112 -- End of comments
113
114 /*#
115 * This procedure creates Role-Plan assignment. For all salesreps,with this Role-Plan assignment records are created in Salesrep-Plan Assigns, Salesrep-Quota Assigns, Salesrep-Periods Quotas, Salesrep-Rate Tiers, Salesrep-Rule Uplifts.
116 * @param p_api_version API version
117 * @param p_init_msg_list Initialize message list (default F)
118 * @param p_commit Commit flag (default F).
119 * @param p_validation_level Validation level (default 100).
120 * @param x_return_status Return Status
121 * @param x_msg_count Number of messages returned
122 * @param x_msg_data Contents of message if x_msg_count = 1
123 * @param x_loading_status Standard OUT parameters
124 * @param p_role_plan_rec Record of type role_plan_rec_type
125 * @rep:lifecycle active
126 * @rep:displayname Create Role Plan Assignment
127 */
128
129 PROCEDURE Create_Role_Plan
130 ( p_api_version IN NUMBER ,
131 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
132 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
133 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
134 x_return_status OUT NOCOPY VARCHAR2 ,
135 x_loading_status OUT NOCOPY VARCHAR2 ,
136 x_msg_count OUT NOCOPY NUMBER ,
137 x_msg_data OUT NOCOPY VARCHAR2 ,
138 p_role_plan_rec IN role_plan_rec_type := G_MISS_ROLE_PLAN_REC,
139 x_role_plan_id OUT NOCOPY NUMBER,
140 x_obj_ver_num OUT NOCOPY NUMBER
141 );
142
143 -- Start of Comments
144 -- API name : Update_Role_Plan
145 -- Type : Public.
146 -- Pre-reqs : None.
147 -- Usage : Procedure to update a sales role and comp plan assignment.
148 -- Parameters :
149 -- IN : p_api_version IN NUMBER Require
150 -- : p_init_msg_list IN VARCHAR2 Optional
151 -- Default = FND_API.G_FALSE
155 -- Default = FND_API.G_VALID_LEVEL_FULL
152 -- : p_commit IN VARCHAR2 Optional
153 -- Default = FND_API.G_FALSE
154 -- : p_validation_level IN NUMBER Optional
156 -- : p_role_plan_rec_old IN ROLE_PLAN_REC_TYPE
157 -- : p_role_plan_rec_new IN ROLE_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 -- : x_loading_status OUT VARCHAR2
162 --
163 --
164 -- Version : Current version 1.0
165 -- Initial version 1.0
166 --
167 -- Description : This procedure is used to update a sales role and comp plan assignment.
168 -- Notes : 1. Old role_plan_id must be found in cn_role_plans.
169 -- 2. New role name can not be null.
170 -- 3. New comp plan name can not be null.
171 -- 4. New start date can not be null.
172 -- 5. New start date <= new end date if new end date is not null.
173 -- 6. New role_name must exist in cn_roles already.
174 -- 8. New comp plan name must exist in cn_comp_plans already.
175 -- 9. The new date range (start_date, end_date) of the assignment must be
176 -- within the date range (start_date, end_date) of the comp plan.
177 -- 10. No comp plan overlap for the any same sales role.
178 -- In other words, you can not have more than one comp plan for
179 -- the same role at the same time.
180 -- 11. Gap between two comp plans for the same role is allowed.
181 -- End of comments
182
183 /*#
184 * This procedure updates Role-Plan assignment. For all salesreps, with this Role-Plan assignment records are updated in Salesrep-Plan Assigns, Salesrep-Quota Assigns, Salesrep-Periods Quotas, Salesrep-Rate Tiers, Salesrep-Rule Uplifts.
185 * @param p_api_version API version
186 * @param p_init_msg_list Initialize message list (default F)
187 * @param p_commit Commit flag (default F).
188 * @param p_validation_level Validation level (default 100).
189 * @param x_return_status Return Status
190 * @param x_msg_count Number of messages returned
191 * @param x_msg_data Contents of message if x_msg_count = 1
192 * @param x_loading_status Standard OUT parameters
193 * @param p_role_plan_rec_old Record of type role_plan_rec_type
194 * @param p_role_plan_rec_new Record of type role_plan_rec_type
195 * @param p_ovn Object Version Number
196 * @rep:lifecycle active
197 * @rep:displayname Update Role Plan Assignment
198 */
199
200
201 PROCEDURE Update_Role_Plan
202 ( p_api_version IN NUMBER ,
203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
204 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
205 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
206 x_return_status OUT NOCOPY VARCHAR2 ,
207 x_loading_status OUT NOCOPY VARCHAR2 ,
208 x_msg_count OUT NOCOPY NUMBER ,
209 x_msg_data OUT NOCOPY VARCHAR2 ,
210 p_role_plan_rec_old IN role_plan_rec_type := G_MISS_ROLE_PLAN_REC,
211 p_ovn IN OUT NOCOPY cn_role_plans.object_version_number%TYPE,
212 p_role_plan_rec_new IN role_plan_rec_type := G_MISS_ROLE_PLAN_REC,
213 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE
214 );
215
216
217
218 -- Start of Comments
219 -- API name : Delete_Role_Plan
220 -- Type : Public.
221 -- Pre-reqs : None.
222 -- Usage : Procedure to delete a sales role and comp plan assignment.
223 -- Parameters :
224 -- IN : p_api_version IN NUMBER Require
225 -- : p_init_msg_list IN VARCHAR2 Optional
226 -- Default = FND_API.G_FALSE
227 -- : p_commit IN VARCHAR2 Optional
228 -- Default = FND_API.G_FALSE
229 -- : p_validation_level IN NUMBER Optional
230 -- Default = FND_API.G_VALID_LEVEL_FULL
231 -- p_role_plan_rec IN ROLE_PLAN_REC_TYPE
232 -- OUT : x_return_status OUT VARCHAR2(1)
233 -- : x_msg_count OUT NUMBER
234 -- : x_msg_data OUT VARCHAR2(2000)
235 -- : x_loading_status OUT VARCHAR2
236 --
237 --
238 -- Version : Current version 1.0
239 -- Initial version 1.0
240 --
241 -- Description : This procedure is used to delete a sales role and comp plan assignment.
242 -- Notes : 1. the old p_role_plan_id must be found based on the
243 -- parameters passed in.
244 -- End of comments
245
246 /*#
247 * This procedure deletes Role-Plan assignment. For all salesreps, with this Role-Plan assignment records are deleted in Salesrep-Plan Assigns, Salesrep-Quota Assigns, Salesrep-Periods Quotas, Salesrep-Rate Tiers, Salesrep-Rule Uplifts.
248 * @param p_api_version API version
249 * @param p_init_msg_list Initialize message list (default F)
250 * @param p_commit Commit flag (default F).
251 * @param p_validation_level Validation level (default 100).
252 * @param x_return_status Return Status
253 * @param x_msg_count Number of messages returned
254 * @param x_msg_data Contents of message if x_msg_count = 1
255 * @param x_loading_status Standard OUT parameters
256 * @param p_role_plan_rec Record of type role_plan_rec_type
257 * @rep:lifecycle active
258 * @rep:displayname Delete Role Plan Assignment
259 */
260
261
262 PROCEDURE Delete_Role_Plan
263 ( p_api_version IN NUMBER ,
264 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
265 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
266 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
267 x_return_status OUT NOCOPY VARCHAR2 ,
268 x_loading_status OUT NOCOPY VARCHAR2 ,
269 x_msg_count OUT NOCOPY NUMBER ,
270 x_msg_data OUT NOCOPY VARCHAR2 ,
271 p_role_plan_rec IN role_plan_rec_type := G_MISS_ROLE_PLAN_REC
272 );
273
274 END CN_ROLE_PLANS_PUB;