1 PACKAGE CN_MULTI_RATE_SCHEDULES_VUHK AS
2 /*$Header: cnirschs.pls 120.1 2005/06/27 19:15:26 appldev ship $*/
3
4 -- Create rate schedule and schedule dimensions
5 -- Select a name, a commission unit code (AMOUNT or PERCENT), and a table of rate
6 -- dimensions. For each dimension assignment, select a rate dimension by its name
7 -- and a sequence number (counting up, starting from one). Leave the rate schedule name
8 -- and object_version_number blank. This API creates the rate schedule, assigns the
9 -- dimensions, and creates the appropriate set of tiers. The original object_version_number
10 -- is zero. When tiers are first created their commission amounts are null.
11 -- They can be set using the Update_Tier API.
12 PROCEDURE Create_Schedule_Pre
13 (p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
14 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
15 p_dims_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.dim_assign_tbl_type :=
16 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_dim_assign_tbl,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2);
20 PROCEDURE Create_Schedule_Post
21 (p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
22 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE,
23 p_dims_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.dim_assign_tbl_type :=
24 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_dim_assign_tbl,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2);
28
29 -- Update rate schedule and schedule dimensions
30 -- Identify the rate schedule to pass by its original name, select a new name,
31 -- commission_unit_code and dimension assignment table as in the create API.
32 -- If the dimension assignment table is empty or not passed in, only the name and
33 -- commission_unit_code will be updated and the tiers are not affected. Otherwise,
34 -- the table is rebuilt according to the new set of dimension assignments and the
35 -- tiers are re-created (resetting the commission amounts). If all the validations
36 -- pass, the rate schedule is updated and its object version number is incremented.
37 PROCEDURE Update_Schedule_Pre
38 (p_original_name IN CN_RATE_SCHEDULES.NAME%TYPE,
39 p_new_name IN CN_RATE_SCHEDULES.NAME%TYPE :=
40 cn_api.g_miss_char,
41 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE :=
42 cn_api.g_miss_char,
43 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE,
44 p_dims_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.dim_assign_tbl_type :=
45 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_dim_assign_tbl,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2);
49 PROCEDURE Update_Schedule_Post
50 (p_original_name IN CN_RATE_SCHEDULES.NAME%TYPE,
51 p_new_name IN CN_RATE_SCHEDULES.NAME%TYPE :=
52 cn_api.g_miss_char,
53 p_commission_unit_code IN CN_RATE_SCHEDULES.COMMISSION_UNIT_CODE%TYPE :=
54 cn_api.g_miss_char,
55 p_object_version_number IN CN_RATE_SCHEDULES.OBJECT_VERSION_NUMBER%TYPE,
56 p_dims_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.dim_assign_tbl_type :=
57 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_dim_assign_tbl,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2);
61
62 -- Delete rate schedule, dimension assignments, and rate tiers.
63 -- Identify the rate schedule to be deleted by its name.
64 PROCEDURE Delete_Schedule_Pre
65 (p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2);
69 PROCEDURE Delete_Schedule_Post
70 (p_name IN CN_RATE_SCHEDULES.NAME%TYPE,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2);
74
75 -- Assign a rate dimension to an existing rate schedule. Identify the rate schedule
76 -- and its dimension by name. Also give the sequence number indicating where you want
77 -- to insert the dimension. If a dimension is inserted in the middle, all the existing
78 -- dimension assignments with equal or higher sequence number are pushed up by one sequence
79 -- number.
80 PROCEDURE Create_Dimension_Assign_Pre
81 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
82 p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
83 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2);
87 PROCEDURE Create_Dimension_Assign_Post
88 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
89 p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
90 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2);
94
95 -- Update a rate dimension to an existing rate schedule. Pass in the rate schedule name
96 -- according to which rate schedule you wish to update, and pass in the original and
97 -- new dimension name according to the dimension you wish to reassign. Finally pass in the
98 -- rate dimension sequence number and object_version_number. If you do not pass in the
99 -- rate dimension sequence, the new dimension takes the same sequence number.
100 PROCEDURE Update_Dimension_Assign_Pre
101 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
102 p_orig_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
103 p_new_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE := cn_api.g_miss_char,
104 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE :=
105 cn_api.g_miss_num,
106 p_object_version_number IN CN_RATE_SCH_DIMS.OBJECT_VERSION_NUMBER%TYPE,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_msg_count OUT NOCOPY NUMBER,
109 x_msg_data OUT NOCOPY VARCHAR2);
110 PROCEDURE Update_Dimension_Assign_Post
111 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
112 p_orig_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
113 p_new_rate_dim_name IN CN_RATE_DIMENSIONS.NAME%TYPE := cn_api.g_miss_char,
114 p_rate_dim_sequence IN CN_RATE_SCH_DIMS.RATE_DIM_SEQUENCE%TYPE :=
115 cn_api.g_miss_num,
116 p_object_version_number IN CN_RATE_SCH_DIMS.OBJECT_VERSION_NUMBER%TYPE,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2);
120
121 -- Delete a rate dimension assignment by identifying the rate schedule and rate dimension
122 -- name.
123 PROCEDURE Delete_Dimension_Assign_Pre
124 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
125 p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2);
129 PROCEDURE Delete_Dimension_Assign_Post
130 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
131 p_rate_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
132 x_return_status OUT NOCOPY VARCHAR2,
133 x_msg_count OUT NOCOPY NUMBER,
134 x_msg_data OUT NOCOPY VARCHAR2);
135
136 -- Update an individual rate tier by identifying the rate schedule and the set of
137 -- sequence numbers. The number of values in the rate dimension tier sequence table
138 -- should correspond to the number of dimensions and the values should be in the
139 -- same order as the dimensions.
140 PROCEDURE Update_Rate_Pre
141 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
142 p_tier_coordinates_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.tier_coordinates_tbl,
143 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
144 p_object_version_number IN CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2);
148 PROCEDURE Update_Rate_Post
149 (p_rate_schedule_name IN CN_RATE_SCHEDULES.NAME%TYPE,
150 p_tier_coordinates_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.tier_coordinates_tbl,
151 p_commission_amount IN CN_RATE_TIERS.COMMISSION_AMOUNT%TYPE,
152 p_object_version_number IN CN_RATE_TIERS.OBJECT_VERSION_NUMBER%TYPE,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2);
156
157 -- Create a dimension
158 -- Choose a name, description, a unit code (AMOUNT, PERCENT, EXPRESSION or STRING), and a set
159 -- of tiers. For each tier, populate value1 and value2 (except for STRING which only uses
160 -- value1).
161 PROCEDURE Create_Dimension_Pre
162 (p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
163 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
164 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
165 p_tiers_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.rate_tier_tbl_type :=
166 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_rate_tier_tbl,
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2);
170 PROCEDURE Create_Dimension_Post
171 (p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
172 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
173 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
174 p_tiers_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.rate_tier_tbl_type :=
175 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_rate_tier_tbl,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2);
179
180 -- Update a dimension
181 -- Identify the dimension to be updated by its original name. Select the new name,
182 -- description, unit code and tiers table. If unit code and tiers table are not passed in,
183 -- only the name and description are updated.
184 PROCEDURE Update_Dimension_Pre
185 (p_original_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
186 p_new_name IN CN_RATE_DIMENSIONS.NAME%TYPE :=
187 cn_api.g_miss_char,
188 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE :=
189 cn_api.g_miss_char,
190 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE :=
191 cn_api.g_miss_char,
192 p_tiers_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.rate_tier_tbl_type :=
193 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_rate_tier_tbl,
194 p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2);
198 PROCEDURE Update_Dimension_Post
199 (p_original_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
200 p_new_name IN CN_RATE_DIMENSIONS.NAME%TYPE :=
201 cn_api.g_miss_char,
202 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE :=
203 cn_api.g_miss_char,
204 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE :=
205 cn_api.g_miss_char,
206 p_tiers_tbl IN CN_MULTI_RATE_SCHEDULES_PUB.rate_tier_tbl_type :=
207 CN_MULTI_RATE_SCHEDULES_PUB.g_miss_rate_tier_tbl,
208 p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2);
212
213 -- Delete a dimension
214 -- Pass in the name of dimension to be deleted.
215 PROCEDURE Delete_Dimension_Pre
216 (p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2);
220 PROCEDURE Delete_Dimension_Post
221 (p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2);
225
226 -- Create a tier for a dimension
227 -- Pass in the dimension name and value1 and value2 (if the type of the
228 -- specified dimension is STRING, value2 is not used... just pass in null).
229 -- Also pass in the tier sequence number. All existing tiers with equal or higher
230 -- sequence numbers get pushed up by one.
231 PROCEDURE Create_Tier_Pre
232 (p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
233 p_value1 IN VARCHAR2,
234 p_value2 IN VARCHAR2,
235 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2);
239 PROCEDURE Create_Tier_Post
240 (p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
241 p_value1 IN VARCHAR2,
242 p_value2 IN VARCHAR2,
243 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2);
247
248 -- Update a tier for a dimension
249 -- Pass in the dimension name and sequence number to uniquely identify the tier.
250 -- Indicate the new minimum and maximum values as p_value1 and p_value2 (STRING
251 -- value tiers don't use p_value2).
252 PROCEDURE Update_Tier_Pre
253 (p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
254 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
255 p_value1 IN VARCHAR2,
256 p_value2 IN VARCHAR2,
257 p_object_version_number IN CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2);
261 PROCEDURE Update_Tier_Post
262 (p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
263 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
264 p_value1 IN VARCHAR2,
265 p_value2 IN VARCHAR2,
266 p_object_version_number IN CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2);
270
271 -- Delete a tier for a dimension
272 -- Pass in the dimension name and sequence number to uniquely identify the tier.
273 PROCEDURE Delete_Tier_Pre
274 (p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
275 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2);
279 PROCEDURE Delete_Tier_Post
280 (p_dimension_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
281 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2);
285
286 END CN_MULTI_RATE_SCHEDULES_VUHK;
[Home] [Help]