1 PACKAGE CN_MULTI_RATE_SCHEDULES_PVT AS
2 /*$Header: cnvrschs.pls 120.10 2007/03/27 15:12:10 kkanyara ship $*/
3
4 TYPE comm_rec_type IS RECORD (
5 p_rate_sequence CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
6 p_commission_amount CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
7 p_object_version_number CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
8 p_org_id CN_RATE_TIERS.ORG_ID%TYPE
9 );
10
11
12 -- record type of the rate table dimensions
13 TYPE dim_rec_type IS RECORD
14 (rate_sch_dim_id CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE := NULL,
15 rate_dimension_id CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE,
16 rate_schedule_id CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
17 rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
18 rate_dim_name CN_RATE_DIMENSIONS.NAME%TYPE,
19 number_tier CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE,
20 dim_unit_code CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
21 object_version_number CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE);
22
23 -- record type of the rate table summary
24 TYPE rate_table_rec_type IS RECORD
25 (rate_schedule_id CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
26 name CN_RATE_SCHEDULES.NAME%TYPE,
27 type CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
28 number_dim CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
29 object_version_number CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE);
30
31 -- record type of the plan element assignment summary
32 TYPE plan_elt_rec_type IS RECORD
33 (quota_id CN_RT_QUOTA_ASGNS.QUOTA_ID%TYPE,
34 quota_name CN_QUOTAS.NAME%TYPE,
35 incentive_type CN_LOOKUPS.MEANING%TYPE,
36 calc_formula_id CN_RT_QUOTA_ASGNS.CALC_FORMULA_ID%TYPE,
37 formula_name CN_CALC_FORMULAS.NAME%TYPE,
38 start_date CN_RT_QUOTA_ASGNS.START_DATE%TYPE,
39 end_date CN_RT_QUOTA_ASGNS.END_DATE%TYPE);
40
41 -- record type of the formula assignment summary
42 TYPE formula_rec_type IS RECORD
43 (calc_formula_id CN_RT_FORMULA_ASGNS.CALC_FORMULA_ID%TYPE,
44 formula_name CN_CALC_FORMULAS.NAME%TYPE,
45 formula_type CN_LOOKUPS.MEANING%TYPE,
46 start_date CN_RT_FORMULA_ASGNS.START_DATE%TYPE,
47 end_date CN_RT_FORMULA_ASGNS.END_DATE%TYPE);
48
49 TYPE rate_table_tbl_type IS TABLE OF rate_table_rec_type INDEX BY BINARY_INTEGER;
50 TYPE dims_tbl_type IS TABLE OF dim_rec_type INDEX BY BINARY_INTEGER;
51 TYPE parents_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
52 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
53 TYPE plan_elt_tbl_type IS TABLE OF plan_elt_rec_type INDEX BY BINARY_INTEGER;
54 TYPE formula_tbl_type IS TABLE OF formula_rec_type INDEX BY BINARY_INTEGER;
55 TYPE comm_tbl_type IS TABLE OF comm_rec_type INDEX BY BINARY_INTEGER;
56
57 G_MISS_DIMS_TBL dims_tbl_type;
58
59 -- Notes : Create rate schedule and schedule dimensions
60 -- 1) Validate schedule name (should be unique)
61 -- 2) Validate commission_unit_code (valid values are AMOUNT, PERCENT)
62 -- 3) Validate number_dim which should equal the number of dimensions in
63 -- p_dims_tbl if it is not empty
64 PROCEDURE Create_Schedule
65 (p_api_version IN NUMBER ,
66 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
67 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
68 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
69 p_name IN CN_RATE_SCHEDULES.NAME%TYPE ,
70 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
71 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
72 p_dims_tbl IN dims_tbl_type := g_miss_dims_tbl,
73 --R12 MOAC Changes--Start
74 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
75 x_rate_schedule_id IN OUT NOCOPY CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE, --changed
76 --R12 MOAC Changes--End
77 x_return_status OUT NOCOPY VARCHAR2 ,
78 x_msg_count OUT NOCOPY NUMBER ,
79 x_msg_data OUT NOCOPY VARCHAR2 );
80
81 -- Notes : Update rate schedule and schedule dimensions
82 -- 1) Validate schedule name (should be unique)
83 -- 2) Validate commission_unit_code (valid values are AMOUNT, PERCENT)
84 -- 3) Validate number_dim which should equal the number of dimensions in
85 -- p_dims_tbl if it is not empty
86 -- 4) Insert new dimensions and delete obsolete dimensions
87 -- 5) Update rate tiers also
88 -- 6) If this rate table is used, then update of dimensions and
89 -- commission_unit_code is not allowed
90 PROCEDURE Update_Schedule
91 (p_api_version IN NUMBER ,
92 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
93 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
94 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
95 p_rate_schedule_id IN CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
96 p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
97 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
98 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
99 --R12 MOAC Changes--Start
100 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
101 p_object_version_number IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- Changed
102 --R12 MOAC Changes--End
103 p_dims_tbl IN dims_tbl_type := g_miss_dims_tbl,
104 x_return_status OUT NOCOPY VARCHAR2 ,
105 x_msg_count OUT NOCOPY NUMBER ,
106 x_msg_data OUT NOCOPY VARCHAR2 );
107
108 -- Notes : Delete rate schedule
109 -- 1) If it is used, it can not be deleted
110 -- 2) If it can be deleted, delete corresponding records in
111 -- cn_rate_sch_dims and cn_rate_tiers
112 PROCEDURE Delete_Schedule
113 (p_api_version IN NUMBER ,
114 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
115 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
116 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
117 p_rate_schedule_id IN CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE,
118 --R12 MOAC Changes--Start
119 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
120 --R12 MOAC Changes--End
121 x_return_status OUT NOCOPY VARCHAR2 ,
122 x_msg_count OUT NOCOPY NUMBER ,
123 x_msg_data OUT NOCOPY VARCHAR2 );
124
125 -- Notes : Delete schedule dimension
126 -- 1) If the rate schedule is used, its dimensions can not be deleted
127 -- 2) delete the corresponding records in cn_rate_sch_dims and cn_rate_tiers
128 -- 3) update cn_rate_schedules.number_dim if not called from form
129 -- 4) rate_dim_sequence is not adjusted here, users should take
130 -- care of the adjustment by calling update_dimension_assign
131 PROCEDURE delete_dimension_assign
132 (p_api_version IN NUMBER ,
133 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
134 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
135 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
136 p_rate_sch_dim_id IN CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE,
137 p_rate_schedule_id IN CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
138 --R12 MOAC Changes--Start
139 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, -- new
140 --R12 MOAC Changes--End
141 x_return_status OUT NOCOPY VARCHAR2 ,
142 x_msg_count OUT NOCOPY NUMBER ,
143 x_msg_data OUT NOCOPY VARCHAR2 );
144
145 -- Notes : Update dimension assignment
146 -- 1) If the rate table is used, then update is not allowed
147 -- 2) If it can be updated, update records in cn_rate_sch_dims
148 -- and cn_rate_tiers
149 PROCEDURE update_dimension_assign
150 (p_api_version IN NUMBER ,
151 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
152 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
153 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
154 p_rate_sch_dim_id IN CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE,
155 p_rate_schedule_id IN CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
156 p_rate_dimension_id IN CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE := cn_api.g_miss_num,
157 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := cn_api.g_miss_num,
158 --R12 MOAC Changes--Start
159 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
160 p_object_version_number IN OUT NOCOPY CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE, --changed
161 --R12 MOAC Changes--End
162 x_return_status OUT NOCOPY VARCHAR2 ,
163 x_msg_count OUT NOCOPY NUMBER ,
164 x_msg_data OUT NOCOPY VARCHAR2 );
165
166 -- Notes : Create dimension assignment
167 -- 1) If the rate table is used, new assignment can not be created
168 -- 2) if the rate table is not used, update and cn_rate_tiers;
169 -- and adjust cn_rate_tiers.rate_sequence
170 -- 3) update cn_rate_schedules.number_dim
171 -- 4) rate_dim_sequence is not adjusted here, users should do it by
172 -- calling update_dimension_assign
173 PROCEDURE create_dimension_assign
174 (p_api_version IN NUMBER ,
175 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
176 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
177 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
178 p_rate_schedule_id IN CN_RATE_SCH_DIMS.RATE_SCHEDULE_ID%TYPE,
179 p_rate_dimension_id IN CN_RATE_SCH_DIMS.RATE_DIMENSION_ID%TYPE,
180 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
181 --R12 MOAC Changes--Start
182 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
183 x_rate_sch_dim_id IN OUT NOCOPY CN_RATE_SCH_DIMS.RATE_SCH_DIM_ID%TYPE, --changed
184 --R12 MOAC Changes--End
185 x_return_status OUT NOCOPY VARCHAR2 ,
186 x_msg_count OUT NOCOPY NUMBER ,
187 x_msg_data OUT NOCOPY VARCHAR2 );
188
189 -- procedure to create rate tiers upon insert of rate dimension assignment or dimension tiers.
190 PROCEDURE create_rate_tiers
191 (p_rate_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
192 p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE := NULL,
193 p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
194 p_num_tiers NUMBER := 1,
195 --R12 MOAC Changes--Start
196 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE);
197 --R12 MOAC Changes--End
198
199 -- procedure to delete rate tiers upon delete of rate dimension assignment or dimension tiers.
200 PROCEDURE delete_rate_tiers
201 (p_rate_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
202 p_rate_dim_sequence CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
203 p_tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := NULL,
204 p_num_tiers NUMBER := 1);
205
206 -- procedure to update a rate
207 PROCEDURE update_rate
208 (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
209 p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
210 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
211 --R12 MOAC Changes--Start
212 p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, --changed
213 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE --new
214 --R12 MOAC Changes--End
215 );
216
217 PROCEDURE update_srp_rate
218 (p_srp_quota_assign_id IN CN_SRP_QUOTA_ASSIGNS.SRP_QUOTA_ASSIGN_ID%TYPE,
219 p_rt_quota_asgn_id IN CN_SRP_RATE_ASSIGNS.RT_QUOTA_ASGN_ID%TYPE,
220 p_rate_sequence IN CN_RATE_TIERS.RATE_SEQUENCE%TYPE,
221 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
222 p_object_version_number IN OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE, -- changed
223 --R12 MOAC Changes--Start
224 p_org_id CN_RATE_TIERS.ORG_ID%TYPE, --new
225 --R12 MOAC Changes--End
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_loading_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2
230
231 );
232
233 -- utility function to get the rate_tier_id and commission amount when given the tier combination
234 PROCEDURE get_rate_tier_info
235 (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
236 p_rate_dim_tier_id_tbl IN num_tbl_type ,
237 x_rate_tier_id OUT NOCOPY CN_RATE_TIERS.RATE_TIER_ID%TYPE ,
238 x_rate_sequence OUT NOCOPY CN_RATE_TIERS.RATE_SEQUENCE%TYPE ,
239 x_commission_amount OUT NOCOPY CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
240 x_object_version_number OUT NOCOPY CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE);
241
242 PROCEDURE update_comm_rate
243 (p_rate_schedule_id IN CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,
244 x_result_tbl IN comm_tbl_type,
245 --R12 MOAC Changes--Start
246 p_org_id IN CN_RATE_TIERS.ORG_ID%TYPE --new
247 --R12 MOAC Changes--End
248 );
249
250 PROCEDURE duplicate_rate_Schedule
251 (p_api_version IN NUMBER ,
252 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
253 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
254 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
255 p_name IN OUT NOCOPY CN_RATE_SCHEDULES.NAME%TYPE ,
256 p_org_id IN CN_RATE_SCHEDULES.ORG_ID%TYPE, --new
257 --R12 MOAC Changes--End
258 p_rate_schedule_id IN OUT NOCOPY CN_RATE_SCHEDULES.RATE_SCHEDULE_ID%TYPE, --changed
259 p_number_dim IN CN_RATE_SCHEDULES.NUMBER_DIM%TYPE,
260 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2
264
265 );
266
267
268 FUNCTION get_sequence(x_schedule_id CN_RATE_TIERS.RATE_SCHEDULE_ID%TYPE,sbuf varchar2)
269 RETURN Number;
270 END CN_MULTI_RATE_SCHEDULES_PVT;
[Home] [Help]