1 PACKAGE cn_calc_submission_pub AS
2 /* $Header: cnpcsbs.pls 120.2 2005/08/08 10:03:05 ymao noship $ */
3 /*#
4 * This package provides the APIs for creating and updating a calculation submission batch.
5 * @rep:scope public
6 * @rep:product CN
7 * @rep:displayname Create/Update Calculation Submission Batch
8 * @rep:lifecycle active
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY CN_COMP_PLANS
11 */
12
13 -- Comments for datatype, global constant or variables
14
15 TYPE salesrep_rec_type IS RECORD
16 ( employee_number cn_salesreps.employee_number%TYPE := FND_API.G_MISS_CHAR,
17 type cn_salesreps.type%TYPE := FND_API.G_MISS_CHAR,
18 hierarchy_flag cn_calc_submission_entries.hierarchy_flag%TYPE := 'N'
19 );
20
21
22 TYPE salesrep_tbl_type IS TABLE OF salesrep_rec_type
23 INDEX BY BINARY_INTEGER;
24
25 TYPE plan_element_tbl_type IS TABLE OF cn_quotas.name%TYPE
26 INDEX BY BINARY_INTEGER;
27
28 TYPE app_user_resp_rec_type IS RECORD
29 ( user_name fnd_user.user_name%TYPE := FND_API.G_MISS_CHAR,
30 responsibility_name fnd_responsibility_vl.responsibility_name%TYPE := FND_API.G_MISS_CHAR
31 );
32
33 g_miss_app_user_resp_rec app_user_resp_rec_type;
34 g_miss_salesrep_tbl salesrep_tbl_type;
35 g_miss_pe_tbl plan_element_tbl_type;
36
37 -- calc submission batch record type
38 TYPE calc_submission_rec_type IS RECORD
39 ( batch_name cn_calc_submission_batches.name%TYPE := FND_API.G_MISS_CHAR,
40 start_date cn_calc_submission_batches.start_date%TYPE := FND_API.G_MISS_DATE,
41 end_date cn_calc_submission_batches.end_date%TYPE := FND_API.G_MISS_DATE,
42 calculation_type cn_calc_submission_batches.calc_type%TYPE := FND_API.G_MISS_CHAR,
43 salesrep_option cn_calc_submission_batches.salesrep_option%TYPE := FND_API.G_MISS_CHAR,
44 entire_hierarchy cn_calc_submission_batches.hierarchy_flag%TYPE := FND_API.G_MISS_CHAR,
45 concurrent_calculation cn_calc_submission_batches.concurrent_flag%TYPE := FND_API.G_MISS_CHAR,
46 incremental_calculation cn_calc_submission_batches.intelligent_flag%TYPE := FND_API.G_MISS_CHAR,
47 interval_type cn_interval_types.name%type := FND_API.G_MISS_CHAR,
48 org_id cn_calc_submission_batches.org_id%TYPE := NULL,
49 attribute_category cn_comp_plans.attribute_category%TYPE := FND_API.G_MISS_CHAR,
50 attribute1 cn_comp_plans.attribute1%TYPE := FND_API.G_MISS_CHAR,
51 attribute2 cn_comp_plans.attribute2%TYPE := FND_API.G_MISS_CHAR,
52 attribute3 cn_comp_plans.attribute3%TYPE := FND_API.G_MISS_CHAR,
53 attribute4 cn_comp_plans.attribute4%TYPE := FND_API.G_MISS_CHAR,
54 attribute5 cn_comp_plans.attribute5%TYPE := FND_API.G_MISS_CHAR,
55 attribute6 cn_comp_plans.attribute6%TYPE := FND_API.G_MISS_CHAR,
56 attribute7 cn_comp_plans.attribute7%TYPE := FND_API.G_MISS_CHAR,
57 attribute8 cn_comp_plans.attribute8%TYPE := FND_API.G_MISS_CHAR,
58 attribute9 cn_comp_plans.attribute9%TYPE := FND_API.G_MISS_CHAR,
59 attribute10 cn_comp_plans.attribute10%TYPE := FND_API.G_MISS_CHAR,
60 attribute11 cn_comp_plans.attribute11%TYPE := FND_API.G_MISS_CHAR,
61 attribute12 cn_comp_plans.attribute12%TYPE := FND_API.G_MISS_CHAR,
62 attribute13 cn_comp_plans.attribute13%TYPE := FND_API.G_MISS_CHAR,
63 attribute14 cn_comp_plans.attribute14%TYPE := FND_API.G_MISS_CHAR,
64 attribute15 cn_comp_plans.attribute15%TYPE := FND_API.G_MISS_CHAR
65 );
66
67 -- Global variable that represent missing values.
68 g_miss_calc_submission_rec calc_submission_rec_type;
69
70 -- Start of Comments
71 -- API name : Create_Calc_Submission
72 -- Type : Public.
73 -- Pre-reqs : None.
74 -- Usage : Used to create a new calculation submission batch with passed_in
75 -- salesreps/ passed_in bonus plan elements
76 -- And submit the calculation after all validations are successful
77 -- Desc : Procedure to create a new calculation submission batch with passed_in
78 -- salesreps/ passed_in bonus plan elements
79 -- Parameters :
80 -- IN : p_api_version IN NUMBER Require
81 -- p_init_msg_list IN VARCHAR2 Optional
82 -- Default = FND_API.G_FALSE
83 -- p_commit IN VARCHAR2 Optional
84 -- Default = FND_API.G_FALSE
85 -- p_validation_level IN NUMBER Optional
86 -- Default = FND_API.G_VALID_LEVEL_FULL
87 -- OUT : x_return_status OUT VARCHAR2(1)
88 -- x_msg_count OUT NUMBER
89 -- x_msg_data OUT VARCHAR2(2000)
90 -- IN : p_calc_submission_rec IN calc_submission_rec_type
91 -- p_app_user_resp_rec IN app_user_resp_rec_type
92 -- p_salesrep_tbl IN salesrep_tbl_type
93 -- p_bonus_pe_tbl IN plan_element_tbl_type
94 -- Version : Current version 1.0
95 -- Initial version 1.0
96 --
97 -- Notes
98 --
99 --
100 -- Description :
101 -- Create Calc Submission is a Public Package which allows us to create
102 -- the calculation submission batch.
103 ------------------+
104 -- p_calc_submission_rec Input parameter
105 -- batch_name calculation submission batch name, Mandatory
106 -- Should uniquely identify the batch
107 -- start_date start date Mandatory
108 -- Must be within opened period
109 -- end_date end date must be within opened period Mandatory
110 -- Must be within opened period
111 -- calculation_type type of calculation Mandatory
112 -- Valid values: COMMISSION/BONUS
113 -- salesrep_option salesrep option Mandatory
114 -- Valid values: ALL_REPS/USER_SPECIFY/REPS_IN_NOTIFY_LOG
115 -- IF calc_type = BONUS , REPS_IN_NOTIFY_LOG is not valid.
116 -- entire_hierarchy entire hierarchy or not Mandatory
117 -- Valid values: Y/N
118 -- IF salesrep_option = ALL_REPS or REPS_IN_NOTIFY_LOG,
119 -- hierarchy_flag should be 'N'.
120 -- concurrent_calculation concurrent calculation or not ( Y/N ) Mandatory
121 -- Valid values: Y/N
122 -- incremental_calculation incremental calculation or not ( Y/N) Mandatory
123 -- Valid values: Y/N
124 -- IF salesrep_option = REPS_IN_NOTIFY_LOG,
125 -- intelligent_flag should be 'Y'.
126 -- interval_type interval type for bonus plan elements Optional
127 -- Valid values: PERIOD/QUARTER/YEAR/ALL
128 -- Mandatory when calc_type = 'BONUS'
129 --
130 --
131 -- p_app_user_resp_rec IN parameter Optional
132 -- Information required to submit concurrent calculation
133 -- Valid when concurrent_calculation = 'Y'
134 -- user_name should be a valid application user name.
135 -- responsibility_name should be a valid responsibility name
136 --
137 -- p_salesrep_tbl IN parameter
138 -- list of salesreps' employee number /employee type Optional
139 -- Valid when salesrep_option = 'USER_SPECIFY'
140 -- salesrep_rec_type.employee number can not be missing or null
141 -- salesrep_rec_type.type can not be missing or null
142 -- Sales persons listed currently have or previously had
143 -- compensation plan assigned.
144 --
145 -- p_bonus_pe_tbl IN parameter
146 -- list of bonus plan elements' name Optional
147 -- Valid when calc_type = BONUS
148 -- Plan elements listed should be 'BONUS' type and their interval type should
149 -- match the value of p_calc_submission_rec.interval_type
150 -- or if p_calc_submission_rec.interval_type = 'ALL', then their interval
151 -- type can be any of 'PERIOD'/'QUARTER'/'YEAR'
152 --
153 --
154 -- Special Notes:
155 -- IF p_commit is not fnd_api.g_true, then the calculation will not be submitted even if all
156 -- the validations are successful.
157 --
158 ------------------------+
159 -- End of comments
160
161 /*#
162 * This procedure creates a new calculation submission batch with the given specifications.
163 * @param p_api_version API version
164 * @param p_init_msg_list Initialize message list (default F)
165 * @param p_commit Commit flag (default F). If p_commit is not fnd_api.g_true, then the calculation will not be submitted even if all of the validations are successful.
166 * @param p_validation_level Validation level (default Full)
167 * @param x_return_status Return status
168 * @param x_msg_count Number of messages returned
169 * @param x_msg_data Contents of message if x_msg_count = 1
170 * @param p_calc_submission_rec This is the contents of calculation submission record
171 * @param p_app_user_resp_rec This is the information required to submit concurrent calculation. It is valid when concurrent_calculation = Y. User_name should be a valid application user name.
172 * @param p_salesrep_tbl This is a list of salesreps' employee number and employee type. It is valid when salesrep_option = USER SPECIFY. salesrep_rec_type.employee number cannot be missing or null.
173 * salesrep_rec_type.type cannot be missing or null. Salespeople listed currently have or previously had a compensation plan assigned.
174 * @param p_bonus_pe_tbl This is a list of bonus plan elements. It is valid when calc_type = BONUS. Plan elements listed should be BONUS type and their interval type should match the value of p_calc_submission_rec.interval_type.
175 * Or, if p_calc_submission_rec.interval_type = ALL, then the interval type can be PERIOD, QUARTER, or YEAR.
176 * @param x_loading_status Loading status
177 * @rep:scope public
178 * @rep:lifecycle active
179 * @rep:displayname Create Calculation Submission Batch
180 */
181
182 PROCEDURE Create_Calc_Submission
183 (
184 p_api_version IN NUMBER,
185 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
186 p_commit IN VARCHAR2 := FND_API.G_FALSE,
187 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191 p_calc_submission_rec IN calc_submission_rec_type := g_miss_calc_submission_rec,
192 p_app_user_resp_rec IN app_user_resp_rec_type := g_miss_app_user_resp_rec,
193 p_salesrep_tbl IN salesrep_tbl_type := g_miss_salesrep_tbl,
194 p_bonus_pe_tbl IN plan_element_tbl_type := g_miss_pe_tbl,
195 x_loading_status OUT NOCOPY VARCHAR2
196 );
197
198 -- Start of Comments
199 -- API name : Update_Calc_Submission
200 -- Type : Public.
201 -- Pre-reqs : None.
202 -- Usage : Used to update a calculation submission batch with passed_in
203 -- salesreps/ passed_in bonus plan elements
204 -- And submit the calculation after all validations are successful
205 -- Desc : Procedure to update calculation submission batch with passed_in
206 -- salesreps/ passed_in bonus plan elements
207 -- Parameters :
208 -- IN : p_api_version IN NUMBER Require
209 -- p_init_msg_list IN VARCHAR2 Optional
210 -- Default = FND_API.G_FALSE
211 -- p_commit IN VARCHAR2 Optional
212 -- Default = FND_API.G_FALSE
213 -- p_validation_level IN NUMBER Optional
214 -- Default = FND_API.G_VALID_LEVEL_FULL
215 -- OUT : x_return_status OUT VARCHAR2(1)
216 -- x_msg_count OUT NUMBER
217 -- x_msg_data OUT VARCHAR2(2000)
218 -- IN : p_calc_submission_rec IN calc_submission_rec_type
219 -- Version : Current version 1.0
220 -- Initial version 1.0
221 --
222 -- Notes
223 --
224 -- Description : This procedure is used to update a calculation submission
225 -- Notes :
226 --
227 -- p_calc_submission_rec_old Mandatory
228 -- old calculation submission batch must be found based
229 -- on p_calc_submission_rec_old.batch_name
230 -- If the old calculation submission batch is either completed or
231 -- or in progress, then it can not be updated.
232 -- p_calc_submission_rec_new Mandatory
233 -- all the validation rules in create_calc_submission holds here
234 --
235 -- p_app_user_resp_rec IN parameter Optional
236 -- Information required to submit concurrent calculation
237 -- Valid when concurrent_calculation = 'Y'
238 -- user_name should be a valid application user name.
239 -- responsibility_name should be a valid responsibility name
240 --
241 -- p_salesrep_tbl IN parameter Optional
242 -- list of salesreps' employee number /employee type
243 -- Valid when salesrep_option = 'USER_SPECIFY'
244 -- salesrep_rec_type.employee number can not be missing or null
245 -- salesrep_rec_type.type can not be missing or null
246 -- Sales persons listed currently have or previously had
247 -- compensation plan assigned.
248 -- p_salesrep_tbl_action Mandatory
249 -- Valid Values: ADD/DELETE
250 -- either add the listed sales persons to table or delete the listed
251 -- sales persons from the table.
252 -- if the sales person already exists or there are duplicates in p_salesrep_tbl,
253 -- give out a message without failing the call
254 -- p_bonus_pe_tbl IN parameter Optional
255 -- list of bonus plan elements' name
256 -- Valid when calc_type = BONUS
257 -- Plan elements listed should be 'BONUS' type and their interval type should
258 -- match the value of p_calc_submission_rec.interval_type
259 -- or if p_calc_submission_rec.interval_type = 'ALL', then their interval
260 -- type can be any of 'PERIOD'/'QUARTER'/'YEAR'
261 -- p_bonus_pe_tbl_action Mandatory
262 -- Valid Values: ADD/DELETE
263 -- either add the listed bonus plan elements to table or delete the listed
264 -- bonus plan elements from the table.
265 -- if the plan element already exists or there are duplicates in p_bonus_pe_tbl,
266 -- give out a message without failing the call
267 --
268 -- Special Notes:
269 -- IF p_commit is not fnd_api.g_true, then the calculation will not be submitted even if all
270 -- the validations are successful.
271 --
272 --
273 -- End of comments
274 ------------------------+
275 -- End of comments
276
277 /*#
278 * This procedure updates a calculation submission batch with the given specifications.
279 * @param p_api_version API version
280 * @param p_init_msg_list Initialize message list (default F)
281 * @param p_commit Commit flag (default F). If p_commit is not fnd_api.g_true, then the calculation will not be submitted even if all of the validations are successful.
282 * @param p_validation_level Validation level (default 100)
283 * @param x_return_status Return status
284 * @param x_msg_count Number of messages returned
285 * @param x_msg_data Contents of message if x_msg_count = 1
286 * @param p_calc_submission_rec_old The old calculation submission batch must be found based on p_calc_submission_rec_old.batch_name. If the old calculation submission batch is either completed or in progress, then it cannot be updated.
287 * @param p_calc_submission_rec_new Content of calculation submission record
288 * @param p_app_user_resp_rec This is the information required to submit concurrent calculation. It is valid when concurrent_calculation = Y. User_name should be a valid application user name.
289 * @param p_salesrep_tbl This is a list of salesreps' employee number and employee type. It is valid when salesrep_option = USER SPECIFY. salesrep_rec_type.employee number cannot be missing or null.
290 * salesrep_rec_type.type cannot be missing or null. Salespeople listed currently have or previously had a compensation plan assigned.
291 * @param p_salesrep_tbl_action Valid Values: ADD/DELETE. ADD adds the listed salespeople to the table. If the salesperson already exists or there are duplicates in p_salesrep_tbl, it displays a message without failing the call.
292 * DELETE deletes the listed salespeople from the table.
293 * @param p_bonus_pe_tbl This is a list of bonus plan elements. It is valid when calc_type = BONUS. Plan elements listed should be BONUS type and their interval type should match the value of p_calc_submission_rec.interval_type.
294 * Or, if p_calc_submission_rec.interval_type = ALL, then the interval type can be PERIOD, QUARTER, or YEAR.
295 * @param p_bonus_pe_tbl_action Valid Values: ADD/DELETE. ADD adds the listed bonus plan elements to the table. If the plan element already exists or there are duplicates in p_bonus_pe_tbl, it displays a message without failing the call.
296 * DELETE deletes the listed bonus plan elements from the table.
297 * @param x_loading_status Loading status
298 * @rep:scope public
299 * @rep:lifecycle active
300 * @rep:displayname Update Calculation Submission Batch
301 */
302 PROCEDURE Update_Calc_Submission
303 (
304 p_api_version IN NUMBER,
305 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
306 p_commit IN VARCHAR2 := FND_API.G_FALSE,
307 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_msg_count OUT NOCOPY NUMBER,
310 x_msg_data OUT NOCOPY VARCHAR2,
311 p_calc_submission_rec_old IN calc_submission_rec_type := g_miss_calc_submission_rec,
312 p_calc_submission_rec_new IN calc_submission_rec_type := g_miss_calc_submission_rec,
313 p_app_user_resp_rec IN app_user_resp_rec_type := g_miss_app_user_resp_rec,
314 p_salesrep_tbl IN salesrep_tbl_type := g_miss_salesrep_tbl,
315 p_salesrep_tbl_action IN VARCHAR2,
316 p_bonus_pe_tbl IN plan_element_tbl_type := g_miss_pe_tbl,
317 p_bonus_pe_tbl_action IN VARCHAR2,
318 x_loading_status OUT NOCOPY VARCHAR2
319 );
320
321 END cn_calc_submission_pub;