1 PACKAGE CN_PLAN_ELEMENT_PUB AS
2 /* $Header: cnppes.pls 120.7.12000000.2 2007/10/08 18:59:41 rnagired ship $ */
3 /*#
4 * The procedure in this package can be used to create, update, delete and duplicate a plan element.
5 * @rep:scope public
6 * @rep:product CN
7 * @rep:displayname Plan Element
8 * @rep:lifecycle active
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY CN_COMP_PLANS
11 */
12
13 --
14 -- Record Type for Plan Elements ( CN_QUOTAS )
15 --
16 TYPE plan_element_rec_type IS RECORD (
17 NAME cn_quotas.NAME%TYPE := cn_api.g_miss_char,
18 description cn_quotas.description%TYPE := cn_api.g_miss_char,
19 period_type cn_lookups.meaning%TYPE := cn_api.g_miss_char,
20 element_type cn_lookups.meaning%TYPE := cn_api.g_miss_char,
21 target cn_quotas.target%TYPE := 0,
22 incentive_type cn_lookups.meaning%TYPE := cn_api.g_miss_char,
23 credit_type cn_credit_types.NAME%TYPE := cn_api.g_miss_char,
24 calc_formula_name cn_calc_formulas.NAME%TYPE := cn_api.g_miss_char,
25 rt_sched_custom_flag cn_quotas.rt_sched_custom_flag%TYPE := cn_api.g_miss_char,
26 package_name cn_quotas.package_name%TYPE := cn_api.g_miss_char,
27 performance_goal cn_quotas.performance_goal%TYPE := 0,
28 payment_amount cn_quotas.payment_amount%TYPE := 0,
29 start_date cn_quotas.start_date%TYPE := cn_api.g_miss_date,
30 end_date cn_quotas.end_date%TYPE := cn_api.g_miss_date,
31 status cn_quotas.quota_status%TYPE := cn_api.g_miss_char,
32 interval_name cn_interval_types.NAME%TYPE := cn_api.g_miss_char,
33 payee_assign_flag cn_quotas.payee_assign_flag%TYPE := cn_api.g_miss_char,
34 vesting_flag cn_quotas.vesting_flag%TYPE := cn_api.g_miss_char,
35 addup_from_rev_class_flag cn_quotas.addup_from_rev_class_flag%TYPE := cn_api.g_miss_char,
36 expense_account_id cn_quotas_all.expense_account_id%TYPE := 0,
37 liability_account_id cn_quotas_all.liability_account_id%TYPE := 0,
38 quota_group_code cn_quotas.quota_group_code%TYPE := cn_api.g_miss_char,
39 -- clku, PAYMENT ENHANCEMENT
40 payment_group_code cn_quotas.payment_group_code%TYPE := cn_api.g_miss_char,
41 attribute_category cn_quotas.attribute_category%TYPE := cn_api.g_miss_char,
42 attribute1 cn_quotas.attribute1%TYPE := cn_api.g_miss_char,
43 attribute2 cn_quotas.attribute2%TYPE := cn_api.g_miss_char,
44 attribute3 cn_quotas.attribute3%TYPE := cn_api.g_miss_char,
45 attribute4 cn_quotas.attribute4%TYPE := cn_api.g_miss_char,
46 attribute5 cn_quotas.attribute5%TYPE := cn_api.g_miss_char,
47 attribute6 cn_quotas.attribute6%TYPE := cn_api.g_miss_char,
48 attribute7 cn_quotas.attribute7%TYPE := cn_api.g_miss_char,
49 attribute8 cn_quotas.attribute8%TYPE := cn_api.g_miss_char,
50 attribute9 cn_quotas.attribute9%TYPE := cn_api.g_miss_char,
51 attribute10 cn_quotas.attribute10%TYPE := cn_api.g_miss_char,
52 attribute11 cn_quotas.attribute11%TYPE := cn_api.g_miss_char,
53 attribute12 cn_quotas.attribute12%TYPE := cn_api.g_miss_char,
54 attribute13 cn_quotas.attribute13%TYPE := cn_api.g_miss_char,
55 attribute14 cn_quotas.attribute14%TYPE := cn_api.g_miss_char,
56 attribute15 cn_quotas.attribute15%TYPE := cn_api.g_miss_char,
57 org_id cn_quotas.org_id%TYPE := 0, -- Will be necessary when User has multiple OU access and will be validated
58 quota_id cn_quotas.quota_id%TYPE := 0, -- Will be ignored even it is set
59 indirect_credit cn_quotas.indirect_credit%TYPE := cn_api.g_miss_char,
60 sreps_enddated_flag cn_quotas.salesreps_enddated_flag%TYPE := NULL
61 );
62
63 --
64 -- Global variable that represent missing values.
65 -- User Defined Record Type for Plan Element
66 --
67 g_miss_plan_element_rec plan_element_rec_type;
68
69 --
70 -- User defined Table Record Type
71 --
72 TYPE plan_element_rec_tbl_type IS TABLE OF plan_element_rec_type
73 INDEX BY BINARY_INTEGER;
74
75 --
76 -- Global variable for Plan Element table
77 --
78 g_miss_plan_element_rec_tbl plan_element_rec_tbl_type;
79
80 --
81 -- Period quotas rec ( CN_PERIOD_QUOTAS )
82 --
83 TYPE period_quotas_rec_type IS RECORD (
84 period_name cn_periods.period_name%TYPE := cn_api.g_miss_char,
85 period_target cn_period_quotas.period_target%TYPE := 0,
86 period_payment cn_period_quotas.period_payment%TYPE := 0,
87 performance_goal cn_period_quotas.performance_goal%TYPE := 0,
88 attribute1 cn_period_quotas.attribute1%TYPE := cn_api.g_miss_char,
89 attribute2 cn_period_quotas.attribute2%TYPE := cn_api.g_miss_char,
90 attribute3 cn_period_quotas.attribute3%TYPE := cn_api.g_miss_char,
91 attribute4 cn_period_quotas.attribute4%TYPE := cn_api.g_miss_char,
92 attribute5 cn_period_quotas.attribute5%TYPE := cn_api.g_miss_char,
93 attribute6 cn_period_quotas.attribute6%TYPE := cn_api.g_miss_char,
94 attribute7 cn_period_quotas.attribute7%TYPE := cn_api.g_miss_char,
95 attribute8 cn_period_quotas.attribute8%TYPE := cn_api.g_miss_char,
96 attribute9 cn_period_quotas.attribute9%TYPE := cn_api.g_miss_char,
97 attribute10 cn_period_quotas.attribute10%TYPE := cn_api.g_miss_char,
98 attribute11 cn_period_quotas.attribute11%TYPE := cn_api.g_miss_char,
99 attribute12 cn_period_quotas.attribute12%TYPE := cn_api.g_miss_char,
100 attribute13 cn_period_quotas.attribute13%TYPE := cn_api.g_miss_char,
101 attribute14 cn_period_quotas.attribute14%TYPE := cn_api.g_miss_char,
102 attribute15 cn_period_quotas.attribute15%TYPE := cn_api.g_miss_char,
103 period_name_old cn_periods.period_name%TYPE := cn_api.g_miss_char,
104 org_id cn_period_quotas.org_id%TYPE := 0
105 );
106
107 --
108 -- Period Quotas table Type
109 --
110 TYPE period_quotas_rec_tbl_type IS TABLE OF period_quotas_rec_type
111 INDEX BY BINARY_INTEGER;
112
113 --
114 -- Period Quotas G Miss
115 --
116 g_miss_period_quotas_rec_tbl period_quotas_rec_tbl_type;
117
118 --
119 -- Record Type for Revenue Classes ( CN_Quota_Rules )
120 --
121 TYPE revenue_class_rec_type IS RECORD (
122 rev_class_name cn_quota_rules.NAME%TYPE := cn_api.g_miss_char,
123 rev_class_target cn_quota_rules.target%TYPE := 0,
124 rev_class_payment_amount cn_quota_rules.payment_amount%TYPE := 0,
125 rev_class_performance_goal cn_quota_rules.performance_goal%TYPE := 0,
126 description cn_quota_rules.description%TYPE := cn_api.g_miss_char,
127 attribute_category cn_quotas.attribute_category%TYPE := cn_api.g_miss_char,
128 attribute1 cn_quotas.attribute1%TYPE := cn_api.g_miss_char,
129 attribute2 cn_quotas.attribute2%TYPE := cn_api.g_miss_char,
130 attribute3 cn_quotas.attribute3%TYPE := cn_api.g_miss_char,
131 attribute4 cn_quotas.attribute4%TYPE := cn_api.g_miss_char,
132 attribute5 cn_quotas.attribute5%TYPE := cn_api.g_miss_char,
133 attribute6 cn_quotas.attribute6%TYPE := cn_api.g_miss_char,
134 attribute7 cn_quotas.attribute7%TYPE := cn_api.g_miss_char,
135 attribute8 cn_quotas.attribute8%TYPE := cn_api.g_miss_char,
136 attribute9 cn_quotas.attribute9%TYPE := cn_api.g_miss_char,
137 attribute10 cn_quotas.attribute10%TYPE := cn_api.g_miss_char,
138 attribute11 cn_quotas.attribute11%TYPE := cn_api.g_miss_char,
139 attribute12 cn_quotas.attribute12%TYPE := cn_api.g_miss_char,
140 attribute13 cn_quotas.attribute13%TYPE := cn_api.g_miss_char,
141 attribute14 cn_quotas.attribute14%TYPE := cn_api.g_miss_char,
142 attribute15 cn_quotas.attribute15%TYPE := cn_api.g_miss_char,
143 rev_class_name_old cn_quota_rules.NAME%TYPE := cn_api.g_miss_char,
144 org_id cn_quota_rules.org_id%TYPE := 0
145
146 );
147
148 --
149 -- User Defined Quota Rules Record Table Type
150 --
151 TYPE revenue_class_rec_tbl_type IS TABLE OF revenue_class_rec_type
152 INDEX BY BINARY_INTEGER;
153
154 g_miss_revenue_class_rec_tbl revenue_class_rec_tbl_type;
155
156 --
157 -- User Defined Record For Uplift Factors ( CN_QUOTA_RULE_UPLIFTS )
158 --
159 TYPE rev_uplift_rec_type IS RECORD (
160 rev_class_name cn_quota_rules.NAME%TYPE := cn_api.g_miss_char,
161 start_date cn_quota_rule_uplifts.start_date%TYPE := cn_api.g_miss_date,
162 end_date cn_quota_rule_uplifts.end_date%TYPE := cn_api.g_miss_date,
163 rev_class_payment_uplift NUMBER := 0,
164 rev_class_quota_uplift NUMBER := 0,
165 attribute_category cn_quota_rule_uplifts.attribute_category%TYPE := cn_api.g_miss_char,
166 attribute1 cn_quota_rule_uplifts.attribute1%TYPE := cn_api.g_miss_char,
167 attribute2 cn_quota_rule_uplifts.attribute2%TYPE := cn_api.g_miss_char,
168 attribute3 cn_quota_rule_uplifts.attribute3%TYPE := cn_api.g_miss_char,
169 attribute4 cn_quota_rule_uplifts.attribute4%TYPE := cn_api.g_miss_char,
170 attribute5 cn_quota_rule_uplifts.attribute5%TYPE := cn_api.g_miss_char,
171 attribute6 cn_quota_rule_uplifts.attribute6%TYPE := cn_api.g_miss_char,
172 attribute7 cn_quota_rule_uplifts.attribute7%TYPE := cn_api.g_miss_char,
173 attribute8 cn_quota_rule_uplifts.attribute8%TYPE := cn_api.g_miss_char,
174 attribute9 cn_quota_rule_uplifts.attribute9%TYPE := cn_api.g_miss_char,
175 attribute10 cn_quota_rule_uplifts.attribute10%TYPE := cn_api.g_miss_char,
176 attribute11 cn_quota_rule_uplifts.attribute11%TYPE := cn_api.g_miss_char,
177 attribute12 cn_quota_rule_uplifts.attribute12%TYPE := cn_api.g_miss_char,
178 attribute13 cn_quota_rule_uplifts.attribute13%TYPE := cn_api.g_miss_char,
179 attribute14 cn_quota_rule_uplifts.attribute14%TYPE := cn_api.g_miss_char,
180 attribute15 cn_quota_rule_uplifts.attribute15%TYPE := cn_api.g_miss_char,
181 rev_class_name_old cn_quota_rules.NAME%TYPE := cn_api.g_miss_char,
182 start_date_old cn_quota_rule_uplifts.start_date%TYPE := cn_api.g_miss_date,
183 end_date_old cn_quota_rule_uplifts.end_date%TYPE := cn_api.g_miss_date,
184 org_id cn_quota_rule_uplifts.org_id%TYPE := 0,
185 object_version_number cn_quota_rule_uplifts.object_version_number%type
186 );
187
188 --
189 -- User defined Quota Rule Uplift Record Table Type.
190 --
191 TYPE rev_uplift_rec_tbl_type IS TABLE OF rev_uplift_rec_type
192 INDEX BY BINARY_INTEGER;
193
194 g_miss_rev_uplift_rec_tbl rev_uplift_rec_tbl_type;
195
196 --
197 -- User Defined Trx Factors Record Type ( CN_TRX_TYPES )
198 --
199 TYPE trx_factor_rec_type IS RECORD (
200 trx_type cn_trx_factors.trx_type%TYPE := cn_api.g_miss_char,
201 event_factor cn_trx_factors.event_factor%TYPE := 0,
202 rev_class_name cn_quota_rules.NAME%TYPE := cn_api.g_miss_char,
203 org_id cn_trx_factors.org_id%TYPE := 0
204 );
205
206 --
207 -- User Defined Trx Factors Record Table Type.
208 --
209 TYPE trx_factor_rec_tbl_type IS TABLE OF trx_factor_rec_type
210 INDEX BY BINARY_INTEGER;
211
212 g_miss_trx_factor_rec_tbl trx_factor_rec_tbl_type;
213
214 --
215 -- user defined Rt_quota_asgns Record Type.( CN_RT_QUOTA_ASGNS )
216 --
217 TYPE rt_quota_asgns_rec_type IS RECORD (
218 rate_schedule_name cn_rate_schedules.NAME%TYPE := cn_api.g_miss_char,
219 calc_formula_name cn_calc_formulas.NAME%TYPE := cn_api.g_miss_char,
220 start_date cn_rt_quota_asgns.start_date%TYPE := cn_api.g_miss_date,
221 end_date cn_rt_quota_asgns.end_date%TYPE := cn_api.g_miss_date,
222 attribute_category cn_quotas.attribute_category%TYPE := cn_api.g_miss_char,
223 attribute1 cn_quotas.attribute1%TYPE := cn_api.g_miss_char,
224 attribute2 cn_quotas.attribute2%TYPE := cn_api.g_miss_char,
225 attribute3 cn_quotas.attribute3%TYPE := cn_api.g_miss_char,
226 attribute4 cn_quotas.attribute4%TYPE := cn_api.g_miss_char,
227 attribute5 cn_quotas.attribute5%TYPE := cn_api.g_miss_char,
228 attribute6 cn_quotas.attribute6%TYPE := cn_api.g_miss_char,
229 attribute7 cn_quotas.attribute7%TYPE := cn_api.g_miss_char,
230 attribute8 cn_quotas.attribute8%TYPE := cn_api.g_miss_char,
231 attribute9 cn_quotas.attribute9%TYPE := cn_api.g_miss_char,
232 attribute10 cn_quotas.attribute10%TYPE := cn_api.g_miss_char,
233 attribute11 cn_quotas.attribute11%TYPE := cn_api.g_miss_char,
234 attribute12 cn_quotas.attribute12%TYPE := cn_api.g_miss_char,
235 attribute13 cn_quotas.attribute13%TYPE := cn_api.g_miss_char,
236 attribute14 cn_quotas.attribute14%TYPE := cn_api.g_miss_char,
237 attribute15 cn_quotas.attribute15%TYPE := cn_api.g_miss_char,
238 rate_schedule_name_old cn_rate_schedules.NAME%TYPE := cn_api.g_miss_char,
239 start_date_old cn_rt_quota_asgns.start_date%TYPE := cn_api.g_miss_date,
240 end_date_old cn_rt_quota_asgns.end_date%TYPE := cn_api.g_miss_date,
241 org_id cn_rt_quota_asgns.org_id%TYPE := 0
242 );
243
244 g_miss_rt_quota_asgns_rec rt_quota_asgns_rec_type;
245
246 TYPE rt_quota_asgns_rec_tbl_type IS TABLE OF rt_quota_asgns_rec_type
247 INDEX BY BINARY_INTEGER;
248
249 g_miss_rt_quota_asgns_rec_tbl rt_quota_asgns_rec_tbl_type;
250
251 -- Start of Comments
252 -- API name : Create_Plan_Element
253 -- Type : Public.
254 -- Pre-reqs : None.
255 -- Usage : Procedure to create a plan element
256 -- Parameters :
257 -- IN : p_api_version IN NUMBER Require
258 -- p_init_msg_list IN VARCHAR2 Optional
259 -- Default = FND_API.G_FALSE
260 -- p_commit IN VARCHAR2 Optional
261 -- Default = FND_API.G_FALSE
262 -- p_validation_level IN NUMBER Optional
263 -- Default = FND_API.G_VALID_LEVEL_FULL
264 -- p_plan_element_rec IN PLAN_ELEMENT_REC_TYPE
265 -- p_revenue_class_rec_tbl IN REVENUE_CLASS_REC_TBL_TYPE
266 -- Optional
267 -- p_rev_uplift_rec_tbl IN REV_CLASS_REC_TBL_TYPE
268 -- Optional
269 -- p_trx_factors_rec_tbl IN TRX_FACTORS_REC_TBL
270 -- Optional
271 -- p_rt_quota_asgns_rec_tbl IN Optional
272 -- p_period_quotas_rec_tbl IN Optional
273 -- OUT : x_return_status OUT VARCHAR2(1)
274 -- : x_msg_count OUT NUMBER
275 -- : x_msg_data OUT VARCHAR2(2000)
276 -- : x_status OUT VARCHAR2
277 --
278 --
279 -- Version : Current version 1.0
280 -- Initial version 1.0
281 --
282 -- Notes : This Package Procedure is Use to Create the Plan Element.
283 -- Which Allows the User to create the Plan Element in a
284 -- various form.
285 --
286 -- Descrption :
287 ----------------+
288 -- Create Plan Element is a Public API which helps the user to create plan
289 -- element and other related plan element information.
290 -- 1. Quotas ( Plan Element Parent Record )
291 -- 1. quota rules ( Child for Plan Element )
292 -- 2. rule uplifts ( Child for Quota Rules )
293 -- 3. rate quota assigns ( Child for Plan Element )
294 -- 4. trx factors ( Child for Quota Rules )
295 -- 5. period quotas.( Child for Plan Element )
296
297 -- There are various Steps to create the plan element using the Create plan
298 -- element Public API . It has its own record structure and the table structu
299 -- res.
300 --
301 -- Detail description about each parameters.
302 ------------------------------------------+
303 -- p_plan_element_rec has its own structure. It is an Input Parameter for the
304 -- Plan Element ( CN_QUOTAS ), for detail see the declaration above .
305 -- This structure mainly expects the quota informations. If you pass the plan
306 -- element information in the p_plan_element_rec it will validate and commits
307 -- into the database.
308 -- If you pass any Child it will validate and insert into the record into the
309 -- Database
310 -- like quota rules, uplifts, trx factors, period quotas, rt_quota_assgns
311 -- if so it will call the respective modules to validate and
312 -- commits the record.
313 --
314 -- If you Pass the Quota Rules it will call the *Create_quota_rules* package
315 -- Procedure which is private/Group Package procedure to validate and ins
316 -- rt the record
317 --
318 -- Note: Assume that the plan Element record you passed is exists in the
319 -- database and not passing any child record it will through the error message
320 -- saying that record already exists in the database. if not it will call the
321 -- corresponding modules.
322 --
323 -- Method of calling the API to create the plan Element
324 ------------------------------------------------------+
325 -- case 1: You can just create the plan Element.
326 --
327 -- Case 2: You can create the plan Element with one or many child records.
328 --
329 -- Case 3: You can just add one of the child record, if the plan element exists
330 --
331 -- Child record table parameters ( you can pass one or many records in each )
332 -- --------------------------------------------------------------------------
333 -- Note: For Any child record you create you need to pass the plan Element
334 -- p_plan_element_rec, at least Name .
335 --
336 -- P_REVENUE_CLASS_REC_TBL. This is an input parmameter for Quota rules.
337 -- it will allow you to add one or more quota rules for a given plan element.
338 -- For detail quota rule structure see the Deaclaration above
339 --
340 -- for each revenue class rec it will call the create_quota_rules package
341 -- procedure as i said before. it will validate and commits the record
342 -- For information: Each revenue class record you create will automatically
343 -- creates the trx factors bydefault and then you can also customised by
344 -- passing the custom value in the p_trx_factors_rec_tbl
345 --
346 -- P_TRX_FACTORS_REC_TBL. This is an input parameter which will allow you to
347 -- customize the existing trx factors. Trx factors types are standard,
348 -- comes as seed data and you can customise the trx factors.
349 -- you probablly thinking to create the trx factors but internally it
350 -- updates the existing record as i mention you before each revenue class
351 -- or quota rules you create by default
352 -- it creates the trx factors. Trx factors should not exits 100% for
353 -- ( INV, ORD, PMT)
354 --
355 -- P_REV_UPLIFT_REC_TBL. This is an input parameter to create the uplifts
356 -- factors for each plan element, quota rules. you cannot create the
357 -- uplift without plan element and revenue class. here also you need to pass
358 -- plan element rec but no need to pass the revenue class.
359 ----------------------------------------------------------------------------+
360 -- **** Important ***** It is very hard to make the link between tables.
361 ----------------------------------------------------------------------------+
362 -- if you are creating new quota rules and uplifts you can pass the
363 -- p_plan_element_rec, p_revenue_class_rec_tbl and p_rev_uplift_rec_tbl
364 -- ***
365 -- if you are creating only uplifts you cannot pass the revenue class table
366 -- it will error out.this is an important.
367 --
368 -- P_RT_QUOTA_ASGNS_REC_TBL This is an input parameter to create the rt_quota
369 -- Assigns. If you pass multiple records with different start date you have
370 -- sequence it before you pass it into the API. otherwise it will error out.
371 -- Make sure you pass the p_plan_element_rec.name parameter it is mandatory
372 -- in all the cases.
373 --
374 -- P_PERIOD_QUOTAS_REC_TBL. This is an input parameter to create the customised
375 -- period quotas.
376 -- To create the Period quotas you must the pass plan_element and you need to
377 -- pass the period informations
378 --
379 -- Let Us go more detail about each parameters and possible cases.
380 ----------------------------------------+
381 -- Case 1: Simply create the Plan Element
382 ----------------------------------------+
383 -- To create a simple plan Element you need to pass just the
384 -- p_plan_element_rec with other manadatory parameters.
385 --
386 -- Calling Method
387 -- Create_plan_element
388 -- ( p_api_version -- Version No
389 -- p_init_msg_list -- default F ( initilize message list )
390 -- p_commit -- Default F ( Commit )
391 -- p_validation_level -- Validation Level Default FULL
392 -- x_return_status -- Return Status S - Success
393 -- x_msg_count -- Return Message Count
394 -- x_msg_data -- Return Message Data
395 -- p_plan_element_rec -- Plan Element Structure
396 -- x_loading_status -- Detail return loading status );
397 --
398 -- p_plan_element_rec
399 -- name Plan Element Name ( Must be unique )
400 -- description Description about the plan Element ( optional )
401 -- period_type No longer used
402 -- element_type Element Type Possible Values are FORMULA, EXTERNAL, NONE,
403 -- Manadatory
404 -- target Amount columns, no validations Default to 0 ( optional )
405 -- incentive_type Incentive_name ( cn_incentive_types.name, Seeded Data )
406 -- credit_type Credit type ) cn_credit_types, Seeded Data )
407 -- calc_formula_name Calc formula Name is is mandatory if quota type is FORMULA
408 -- rt_sched_custom_flag Rate schedule Custom Flag Default to N
409 -- package_name Package name is manadatory if quota type = 'EXTERNAL'
410 -- performance_goal Amount no validations default to 0
411 -- payment_amount Amount no validations default to 0
412 -- quota_unspecified No longer used
413 -- start_date Start date Mandatory
414 -- end_date end must be greater than start date
415 -- status no longer used
416 -- interval_name Interval Name is mandatory ( cn_interval_types.name )
417 -- payee_assign_flag Default to N ( Y/N)
418 -- vesting_flag Default to N ( Y/N )
419 -- quota_group_code No longer Used
420 -- addup_from_rev_class_flag Default to N ( Y/N )
421 -- attribute_category
422 -- All other attributes 1 to 15
423 ---------------------------------------------------------------------+
424 -- Case 2: Create the Plan Element with one or multiple child records
425 ---------------------------------------------------------------------+
426 -- To create the Plan Element and one or many child records.
427 -- Let us assume that you want to create two different child records like
428 -- Quota rules and uplifts
429 -- other manadatory parameters.
430 --
431 -- Calling Method
432 -- Create_plan_element
433 -- ( p_api_version -- Version No
434 -- p_init_msg_list -- default F ( initilize message list )
435 -- p_commit -- Default F ( Commit )
436 -- p_validation_level -- Validation Level Default FULL
437 -- x_return_status -- Return Status S - Success
438 -- x_msg_count -- Return Message Count
439 -- x_msg_data -- Return Message Data
440 -- p_plan_element_rec -- Plan Element Structure
441 -- p_revenue_class_rec_tbl -- quota rules
442 -- p_rev_uplift_rec_tbl -- quota rule uplifts
443 -- x_loading_status -- Detail return loading status );
444 --
445 -- p_plan_element_rec
446 -- name Plan Element Name ( Must be unique )
447 -- description Description about the plan Element ( optional )
448 -- period_type No longer used
449 -- element_type Element Type Possible Values are FORMULA, EXTERNAL, NONE, Manadatory
450 -- target Amount columns, no validations Default to 0 ( optional )
451 -- incentive_type Incentive_name ( cn_incentive_types.name, Seeded Data )
452 -- credit_type Credit type ) cn_credit_types, Seeded Data )
453 -- calc_formula_name Calc formula Name is is mandatory if quota type is FORMULA
454 -- rt_sched_custom_flag Rate schedule Custom Flag Default to N
455 -- package_name Package name is manadatory if quota type = 'EXTERNAL'
456 -- performance_goal Amount no validations default to 0
457 -- payment_amount Amount no validations default to 0
458 -- quota_unspecified No longer used
459 -- start_date Start date Mandatory
460 -- end_date end must be greater than start date
461 -- status no longer used
462 -- interval_name Interval Name is mandatory ( cn_interval_types.name )
463 -- payee_assign_flag Default to N ( Y/N)
464 -- vesting_flag Default to N ( Y/N )
465 -- quota_group_code No longer Used
466 -- addup_from_rev_class_flag Default to N ( Y/N )
467 -- attribute_category
468 -- All other attributes 1 to 15
469 ------------------------+
470 -- P_REVENUE_CLASS_REC_TBL This is an input parameter which carries the revenue class
471 ------------------------+
472 -- data
473 -- Detail description about the p_revenue_class_rec_tbl structure
474 -- p_revenue_class_rec
475 -- rev_class_name -- Revenue class name Mandatory( must be unique with in the Quota )
476 -- rev_class_target -- Number, default to 0 Optional
477 -- rev_class_payment_amount -- Number default to 0 Optional
478 -- rev_class_performance_goal -- Number default to 0 Optional
479 -- description -- Description Optional
480 -- attribute_category -- Attribute Category Optional
481 -- other attribute 1 to 15 -- Optional columns
482 -- rev_class_name_old *** Pass Null, it will be used only UPDATE,
483 --
484 ----------------------+
485 -- P_REV_UPLIFT_REC_TBL This is an input Parameter which holds the uplift data
486 ----------------------+
487 -- Detail Description about the p_rev_uplift_rec_tbl structure
488 -- rev_class_name Uplift belongs to which revenue classes, Mandatory
489 -- start_date Uplift Start, Mandatory, must be greater quota start date
490 -- end_date Uplift End Date Optional if not null Must be less than quota
491 -- end date
492 -- rev_class_payment_uplift Number, Default to 0
493 -- rev_class_quota_uplift Number, Default to 0
494 -- attribute_category Optional
495 -- rev_class_name_old Don't pass anything, only used for update
496 -- start_date_old Don't pass anything, only used for update
497 -- end_date_old Don't pass anything, only used for update
498 -- *** Same for the Child records
499 -------------------------------------------------------+
500 -- CASE 3 CREATE only child records. *** important ****
501 -------------------------------------------------------+
502 --FOR creating the child RECORD plan element must exists IN the DATABASE and you
503 -- should pass only the plan element name using the p_plan_element_rec
504 -- Pass the child record tables.
505 -------------------------+
506 -- P_REVENUE_CLASS_REC_TBL This is an input parameter which carries the revenue class
507 -------------------------+
508 -- data
509 -- Detail description about the p_revenue_class_rec_tbl structure
510 -- p_revenue_class_rec
511 -- rev_class_name -- Revenue class name Mandatory( must be unique with in the Quota )
512 -- rev_class_target -- Number, default to 0 Optional
513 -- rev_class_payment_amount -- Number default to 0 Optional
514 -- rev_class_performance_goal -- Number default to 0 Optional
515 -- description -- Description Optional
516 -- attribute_category -- Attribute Category Optional
517 -- other attribute 1 to 15 -- Optional columns
518 -- rev_class_name_old *** Pass Null, it will be used only UPDATE,
519 --
520 ----------------------+
521 -- P_REV_UPLIFT_REC_TBL This is an input Parameter which holds the uplift data
522 ----------------------+
523 -- Detail Description about the p_rev_uplift_rec_tbl structure
524 -- rev_class_name Uplift belongs to which revenue classes, Mandatory
525 -- start_date Uplift Start, Mandatory, must be greater quota start date
526 -- end_date Uplift End Date Optional if not null Must be less than quota
527 -- end date
528 -- rev_class_payment_uplift Number, Default to 0
529 -- rev_class_quota_uplift Number, Default to 0
530 -- attribute_category Optional
531 -- rev_class_name_old Don't pass anything, only used for update
532 -- start_date_old Don't pass anything, only used for update
533 -- end_date_old Don't pass anything, only used for update
534 -- *** Same for the Child records
535 ------------------------------------------------------------------------------+
536 -- NOTE: If the one child record exists or failed in a single group of record it
537 -- will not post any records in the database.
538 -- Example : You are passing multiple quota rules 1. All Hardware
539 -- 2. All Services
540 -- 3. All Hardware
541 -- In this example the third record is getting duplicated for one plan element, so
542 -- it will never post the first two records because the commit will takes place only
543 -- after all the records successfully processed.if one fails every thing will be
544 -- rollback. and never continue further.
545 --
546 -- End of comments
547 --
548 ------------------------------------------------------------------------------+
549 -- Create_Plan_Element
550 ------------------------------------------------------------------------------+
551
552 /*#
553 * This procedure creates a plan element and create entries in the child tables.
554 * The possible child table entries are for:
555 * <li>Quota Rules</li>
556 * <li>Rule Uplifts</li>
557 * <li>Transaction Factors</li>
558 * <li>Rollover Quotas</li>
559 * <li>Period quotas </li>
560 * @param p_api_version API version
561 * @param p_init_msg_list Initialize message list (default F)
562 * @param p_commit Commit flag (default F).
563 * @param p_validation_level Validation level (default 100).
564 * @param x_return_status Return Status
565 * @param x_msg_count Number of messages returned
566 * @param x_msg_data Contents of message if x_msg_count = 1
567 * @param x_loading_status Loading Status
568 * @param p_plan_element_rec Plan element details
569 * @param p_revenue_class_rec_tbl Revenue class details
570 * @param p_rev_uplift_rec_tbl Revenue class uplift factor details
571 * @param p_trx_factor_rec_tbl Transaction factors details
572 * @param p_period_quotas_rec_tbl Period Quotas details
573 * @param p_rt_quota_asgns_rec_tbl Rate quota assigns details
574 * @param p_is_duplicate Duplicate/Create flag
575 * @rep:scope public
576 * @rep:lifecycle active
577 * @rep:displayname Create Plan Element
578 */
579 PROCEDURE create_plan_element (
580 p_api_version IN NUMBER := 0,
581 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
582 p_commit IN VARCHAR2 := cn_api.g_false,
583 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2,
587 p_plan_element_rec IN plan_element_rec_type := g_miss_plan_element_rec,
588 p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
589 p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
590 p_trx_factor_rec_tbl IN trx_factor_rec_tbl_type := g_miss_trx_factor_rec_tbl,
591 p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
592 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
593 x_loading_status OUT NOCOPY VARCHAR2,
594 p_is_duplicate IN VARCHAR2 DEFAULT 'N'
595 );
596
597 -- Start of Comments
598 -- API name : Update_Plan_Element
599 -- Type : Public.
600 -- Pre-reqs : None.
601 -- Usage : Procedure to Update a plan element
602 -- Parameters :
603 -- IN : p_api_version IN NUMBER Require
604 -- p_init_msg_list IN VARCHAR2 Optional
605 -- Default = FND_API.G_FALSE
606 -- p_commit IN VARCHAR2 Optional
607 -- Default = FND_API.G_FALSE
608 -- p_validation_level IN NUMBER Optional
609 -- Default = FND_API.G_VALID_LEVEL_FULL
610 -- p_quota_name_old IN VARCHAR2
611 -- (Depends upon the case)
612 -- p_new_plan_element_rec IN PLAN_ELEMENT_REC_TYPE
613 -- p_revenue_class_rec_tbl IN REVENUE_CLASS_REC_TBL_TYPE
614 -- Optional
615 -- p_rev_uplift_rec_tbl IN REV_CLASS_REC_TBL_TYPE
616 -- Optional
617 -- p_trx_factors_rec_tbl IN TRX_FACTORS_REC_TBL
618 -- Optional
619 -- p_period_quotas_rec_tbl IN Optional
620 -- OUT : x_return_status OUT VARCHAR2
621 -- : x_msg_count OUT NUMBER
622 -- : x_msg_data OUT VARCHAR2
623 -- : x_status OUT VARCHAR2
624 --
625 --
626 -- Version : Current version 1.0
627 -- Initial version 1.0
628 --
629 -- Notes : This Package is Use to Create the Plan Element which
630 -- Allows the User to create the the Plan Element in a
631 -- various form.
632
633 -- Update Plan Element is also same like the Create but excepts that
634 -- old value to be passed for each record you update
635 -- For updating the Plan Element you have to pass the old_quota_name
636 -- it is an element with in the p_plan_element_rec.
637 -- ************* Important Note: *****************
638 -- For modifying records you have pass the modified column value and the not modified
639 -- column values which is already in the table also
640 -- For example you want to modify only one column in the table then you have to pass
641 -- all the old columns value and new columns value and the modified value based on
642 -- the parameters.
643 -- This way you can validate the record again, because there will be lots of cross
644 -- validations with in a record.
645 -- End of comments
646 --
647 ------------------------------------------------------------------------------+
648 -- Update_Plan_Element
649 ------------------------------------------------------------------------------+
650 /*#
651 * This procedure updates a plan element and updates respective entries in the child tables. The possible child table entries are for:
652 * <li>Quota Rules</li>
653 * <li>Rule Uplifts</li>
654 * <li>Transaction Factors </li>
655 * <li>Rollover quotas</li>
656 * If the plan element belongs to a compensation plan, the status is set to Incomplete.
657 * If the plan element has been assigned to the salesreps, then the records are also updated in
658 * srp quota assigns, srp rate assigns, srp period quotas, srp quota rules and srp rollover
659 * quotas only if the plan element to salesrep assignment is not customizable.
660 * @param p_api_version API version
661 * @param p_init_msg_list Initialize message list (default F)
662 * @param p_commit Commit flag (default F).
663 * @param p_validation_level Validation level (default 100).
664 * @param x_return_status Return Status
665 * @param x_msg_count Number of messages returned
666 * @param x_msg_data Contents of message if x_msg_count = 1
667 * @param x_loading_status Loading Status
668 * @param p_new_plan_element_rec Plan element details
669 * @param p_quota_name_old Old plan element name that needs to be updated.
670 * @param p_revenue_class_rec_tbl Revenue class details
671 * @param p_rev_uplift_rec_tbl Revenue class uplift factor details
672 * @param p_trx_factor_rec_tbl Transaction factors details
673 * @param p_period_quotas_rec_tbl Period Quotas details
674 * @param p_rt_quota_asgns_rec_tbl Rate quota assigns details
675 * @rep:scope public
676 * @rep:lifecycle active
677 * @rep:displayname Update Plan Element
678 */
679 PROCEDURE update_plan_element (
680 p_api_version IN NUMBER := 0,
681 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
682 p_commit IN VARCHAR2 := cn_api.g_false,
683 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2,
687 p_new_plan_element_rec IN plan_element_rec_type := g_miss_plan_element_rec,
688 p_quota_name_old IN VARCHAR2 := cn_api.g_miss_char,
689 p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
690 p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
691 p_trx_factor_rec_tbl IN trx_factor_rec_tbl_type := g_miss_trx_factor_rec_tbl,
692 p_period_quotas_rec_tbl IN period_quotas_rec_tbl_type := g_miss_period_quotas_rec_tbl,
693 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
694 x_loading_status OUT NOCOPY VARCHAR2
695 );
696
697 -- Start of Comments
698 -- API name : Delete_Plan_Element
699 -- Type : Public.
700 -- Pre-reqs : None.
701 -- Usage : Procedure to Delete a plan element
702 -- Parameters :
703 -- IN : p_api_version IN NUMBER Require
704 -- p_init_msg_list IN VARCHAR2 Optional
705 -- Default = FND_API.G_FALSE
706 -- p_commit IN VARCHAR2 Optional
707 -- Default = FND_API.G_FALSE
708 -- p_validation_level IN NUMBER Optional
709 -- Default = FND_API.G_VALID_LEVEL_FULL
710 -- p_quota_name IN VARCHAR2
711 -- Required
712 -- Optional
713 -- p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type
714 -- := g_miss_revenue_class_rec_tbl
715 -- p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type
716 -- := g_miss_rev_uplift_rec_tbl
717 -- p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type
718 -- := g_miss_rt_quota_asgns_rec_tbl
719 -- OUT : x_return_status OUT VARCHAR2(1)
720 -- : x_msg_count OUT NUMBER
721 -- : x_msg_data OUT VARCHAR2(2000)
722 --
723 -- Version : Current version 1.0
724 -- Initial version 1.0
725 --
726 -- Notes : This Package is Use to Delete the Plan ELement
727 -- Currently this program supports to delete the complete quotas
728 -- You need to pass only the P_quota_name it deletes all the related
729 -- Child records as well.
730 -- Note: You cannot delete the plan Element, if it already assigns to the
731 -- Comp Plans.
732 -- End of comments
733 --
734 ------------------------------------------------------------------------------+
735 -- Delete_Plan_Element
736 ------------------------------------------------------------------------------+
737 /*#
738 * This procedure deletes a plan element and deletes respective
739 * entries in the child tables. The possible child table entries are for:
740 * <li>Quota Rules</li>
741 * <li>Rule Uplifts</li>
742 * <li>Transaction Factors </li>
743 * <li>Rollover quotas</li>
744 * If the plan element belongs to a compensation plan, the status is set to Incomplete.
745 * If the plan element has been assigned to the salesreps, then the records are also
746 * deleted from srp quota assigns, srp rate assigns, srp period quotas, srp quota rules
747 * and srp rollover quotas.
748 * @param p_api_version API version
749 * @param p_init_msg_list Initialize message list (default F)
750 * @param p_commit Commit flag (default F).
751 * @param p_validation_level Validation level (default 100).
752 * @param x_return_status Return Status
753 * @param x_msg_count Number of messages returned
754 * @param x_msg_data Contents of message if x_msg_count = 1
755 * @param x_loading_status Loading Status
756 * @param p_quota_rec Plan Element Details
757 * @param p_revenue_class_rec_tbl Revenue class details
758 * @param p_rev_uplift_rec_tbl Revenue class uplift factor details
759 * @param p_rt_quota_asgns_rec_tbl Rate quota assigns details
760 * @rep:scope public
761 * @rep:lifecycle active
762 * @rep:displayname Delete Plan Element
763 */
764 PROCEDURE delete_plan_element (
765 p_api_version IN NUMBER := 0,
766 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
767 p_commit IN VARCHAR2 := cn_api.g_false,
768 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
769 x_return_status OUT NOCOPY VARCHAR2,
770 x_msg_count OUT NOCOPY NUMBER,
771 x_msg_data OUT NOCOPY VARCHAR2,
772 p_quota_rec IN plan_element_rec_type := g_miss_plan_element_rec,
773 p_revenue_class_rec_tbl IN revenue_class_rec_tbl_type := g_miss_revenue_class_rec_tbl,
774 p_rev_uplift_rec_tbl IN rev_uplift_rec_tbl_type := g_miss_rev_uplift_rec_tbl,
775 p_rt_quota_asgns_rec_tbl IN rt_quota_asgns_rec_tbl_type := g_miss_rt_quota_asgns_rec_tbl,
776 x_loading_status OUT NOCOPY VARCHAR2
777 );
778
779 -- Start Comments
780 -- API name : Duplicate_Plan_Element
781 -- Type : Public.
782 -- Pre-reqs : None.
783 -- Usage : Procedure to copy a PE. the new PE will be named as
784 -- <original pe name>_2. May get truncate if the PE name is
785 -- long.
786 --
787 -- Parameters :
788 -- IN : p_api_version IN NUMBER Require
789 -- p_init_msg_list IN VARCHAR2 Optional
790 -- Default = FND_API.G_FALSE
791 -- p_commit IN VARCHAR2 Optional
792 -- Default = FND_API.G_FALSE
793 -- p_validation_level IN NUMBER Optional
794 -- Default = FND_API.G_VALID_LEVEL_FULL
795 -- OUT : x_return_status OUT VARCHAR2(1)
796 -- x_msg_count OUT NUMBER
797 -- x_msg_data OUT VARCHAR2(2000)
798 -- IN : p_plan_element_name IN cn_quotas.name%TYPE
799 -- OUT : x_plan_element_name OUT cn_quotas.name%TYPE
800 --
801 -- Version : Current version 1.0
802 -- Initial version 1.0
803 --
804 -- Notes : Duplicate Plan Element is a public API will help the user to duplicate
805 -- the existing plan Element to new plan element just _02 with the existing
806 -- plan Element name. It is kind of template we used to copy the
807 -- existing plan Element
808 -- Remember it creates all the related table data as well.
809 -- p_plan_element_name is Mandatory and the x_plan_element_name is the new
810 -- plan Element name
811 -- End of comments
812 ------------------------------------------------------------------------------+
813 -- Duplicate_Plan_Element
814 ------------------------------------------------------------------------------+
815 /*#
816 * Duplicate_Plan_Element in cn_plan_element_pub copies the information of an existing plan element.
817 * The child table entries are also copied from the source.
818 * @param p_api_version API version
819 * @param p_init_msg_list Initialize message list (default F)
820 * @param p_commit Commit flag (default F).
821 * @param p_validation_level Validation level (default 100).
822 * @param x_return_status Return Status
823 * @param x_msg_count Number of messages returned
824 * @param x_msg_data Contents of message if x_msg_count = 1
825 * @param x_loading_status Loading Status
826 * @param p_plan_element_name Source Plan element name
827 * @param p_org_id Organization Id
828 * @param x_plan_element_name New Plan element name
829 * @rep:scope public
830 * @rep:lifecycle active
831 * @rep:displayname Duplicate Plan Element
832 */
833 PROCEDURE duplicate_plan_element (
834 p_api_version IN NUMBER := 0,
835 p_init_msg_list IN VARCHAR2 := cn_api.g_false,
836 p_commit IN VARCHAR2 := cn_api.g_false,
837 p_validation_level IN NUMBER := cn_api.g_valid_level_full,
838 x_return_status OUT NOCOPY VARCHAR2,
839 x_msg_count OUT NOCOPY NUMBER,
840 x_msg_data OUT NOCOPY VARCHAR2,
841 p_plan_element_name IN cn_quotas.NAME%TYPE := cn_api.g_miss_char,
842 p_org_id IN NUMBER,
843 x_plan_element_name OUT NOCOPY cn_quotas.NAME%TYPE,
844 x_loading_status OUT NOCOPY VARCHAR2
845 );
846 END cn_plan_element_pub;
[Home] [Help]