1 PACKAGE CN_RATE_DIMENSIONS_PVT AS
2 /*$Header: cnvrdims.pls 120.2 2006/01/18 15:28:23 jxsingh ship $*/
3
4 TYPE tier_rec_type IS RECORD
5 (rate_dim_tier_id CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE := NULL,
6 minimum_amount CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE,
7 maximum_amount CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE,
8 min_exp_id CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE,
9 max_exp_id CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE,
10 string_value CN_RATE_DIM_TIERS.STRING_VALUE%TYPE,
11 tier_sequence CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE,
12 object_version_number CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE,
13 tier_description VARCHAR2(1000)); -- tier_description is calculated
14
15 TYPE tiers_tbl_type IS TABLE OF tier_rec_type INDEX BY BINARY_INTEGER;
16 TYPE parent_rate_tables_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
17
18 g_miss_tiers_tbl tiers_tbl_type;
19
20 -- Notes : Create rate dimensions and dimension tiers
21 -- 1) Validate dimension name (should be unique)
22 -- 2) Validate dim_unit_code (valid values are AMOUNT,
23 -- PERCENT, STRING, EXPRESSION)
24 -- 3) Validate number_tier which should equal the number of
25 -- tiers in p_tiers_tbl if it is not empty
26 -- 4) Validate dimension tiers (max_amount > min_amount)
27 PROCEDURE Create_Dimension
28 (p_api_version IN NUMBER ,
29 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
30 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
31 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
33 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
34 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
35 p_number_tier IN CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE,
36 p_tiers_tbl IN tiers_tbl_type := g_miss_tiers_tbl,
37 --R12 MOAC Changes--Start
38 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE, --new
39 x_rate_dimension_id IN OUT NOCOPY CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE, --changed
40 --R12 MOAC Changes--End
41 x_return_status OUT NOCOPY VARCHAR2 ,
42 x_msg_count OUT NOCOPY NUMBER ,
43 x_msg_data OUT NOCOPY VARCHAR2 );
44
45 -- Notes : Update rate dimensions and dimension tiers
46 -- 1) Validate dimension name (should be unique)
47 -- 2) Validate dim_unit_code (valid values are AMOUNT,
48 -- PERCENT, STRING, EXPRESSION)
49 -- 3) Validate number_tier which should equal the number of
50 -- tiers in p_tiers_tbl if it is not empty
51 -- 4) Validate dimension tiers (max_amount > min_amount)
52 -- 5) Insert new tiers and delete obsolete tiers
53 -- 6) If this dimension is used in a rate table which is in
54 -- turn used in a formula, then dim_unit_code
55 -- can not be updated
56 PROCEDURE Update_Dimension
57 (p_api_version IN NUMBER ,
58 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
59 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
60 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
61 p_rate_dimension_id IN CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE,
62 p_name IN CN_RATE_DIMENSIONS.NAME%TYPE,
63 p_description IN CN_RATE_DIMENSIONS.DESCRIPTION%TYPE := NULL,
64 p_dim_unit_code IN CN_RATE_DIMENSIONS.DIM_UNIT_CODE%TYPE,
65 p_number_tier IN CN_RATE_DIMENSIONS.NUMBER_TIER%TYPE,
66 p_tiers_tbl IN tiers_tbl_type := g_miss_tiers_tbl,
67 --R12 MOAC Changes--Start
68 p_org_id IN CN_RATE_DIMENSIONS.ORG_ID%TYPE, --new
69 p_object_version_number IN OUT NOCOPY CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, --Changed
70 --R12 MOAC Changes--End
71 x_return_status OUT NOCOPY VARCHAR2 ,
72 x_msg_count OUT NOCOPY NUMBER ,
73 x_msg_data OUT NOCOPY VARCHAR2 );
74
75 -- Notes : Delete rate dimensions and dimension tiers
76 -- 1) If it is used in a rate table, it can not be deleted
77 PROCEDURE Delete_Dimension
78 (p_api_version IN NUMBER ,
79 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
80 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
81 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
82 p_rate_dimension_id IN CN_RATE_DIMENSIONS.RATE_DIMENSION_ID%TYPE,
83 -- R12 MOAC Changes --Start
84 p_object_version_number IN CN_RATE_DIMENSIONS.OBJECT_VERSION_NUMBER%TYPE, --new
85 -- R12 MOAC Changes --End
86 x_return_status OUT NOCOPY VARCHAR2 ,
87 x_msg_count OUT NOCOPY NUMBER ,
88 x_msg_data OUT NOCOPY VARCHAR2 );
89
90 -- Notes : Delete dimension tiers
91 -- 1) If the dimension is used in a rate table, at least one
92 -- tier should be left in the rate dimension
93 -- 2) If it is used in a rate table, delete the corresponding
94 -- records in cn_sch_dim_tiers,
95 -- cn_srp_rate_assigns, cn_rate_tiers, and cn_rate_dim_tiers
96 -- 3) update cn_rate_dimensions.number_tier
97 -- 4) tier_sequence is not adjusted here, users should take
98 -- care of the adjustment by calling update_tier
99 -- 5) the other validations should be done by users also
100 -- (like minimum_amount < maximum_amount, etc.)
101 PROCEDURE delete_tier
102 (p_api_version IN NUMBER ,
103 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
104 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
105 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
106 p_rate_dim_tier_id IN CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE,
107 x_return_status OUT NOCOPY VARCHAR2 ,
108 x_msg_count OUT NOCOPY NUMBER ,
109 x_msg_data OUT NOCOPY VARCHAR2 );
110
111 -- Notes : Update dimension tiers
112 PROCEDURE update_tier
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_dim_tier_id IN CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE,
118 p_rate_dimension_id IN CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
119 p_dim_unit_code IN CN_RATE_DIM_TIERS.DIM_UNIT_CODE%TYPE,
120 p_minimum_amount IN CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE := cn_api.g_miss_num,
121 p_maximum_amount IN CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE := cn_api.g_miss_num,
122 p_min_exp_id IN CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE := cn_api.g_miss_num,
123 p_max_exp_id IN CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE := cn_api.g_miss_num,
124 p_string_value IN CN_RATE_DIM_TIERS.STRING_VALUE%TYPE := cn_api.g_miss_char,
125 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := cn_api.g_miss_num,
126 -- R12 MOAC Changes --Start
127 p_object_version_number IN OUT NOCOPY CN_RATE_DIM_TIERS.OBJECT_VERSION_NUMBER%TYPE,
128 -- R12 MOAC Changes --End
129 x_return_status OUT NOCOPY VARCHAR2 ,
130 x_msg_count OUT NOCOPY NUMBER ,
131 x_msg_data OUT NOCOPY VARCHAR2 );
132
133 -- Notes : Create dimension tiers
134 -- 1) If it is used in a rate table, update cn_sch_dim_tiers,
135 -- cn_srp_rate_assigns, and cn_rate_tiers,
136 -- and adjust cn_rate_tiers.rate_sequence
137 -- 2) update cn_rate_dimensions.number_tier
138 -- 3) tier_sequence is not adjusted here, users should do it by calling
139 -- update_tier
140 -- 4) minimum_amount < maximum_amount
141 -- 5) validation of minimum_amount = previous maximum_amount should be
142 -- done by users
143 PROCEDURE create_tier
144 (p_api_version IN NUMBER ,
145 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
146 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
147 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
148 p_rate_dimension_id IN CN_RATE_DIM_TIERS.RATE_DIMENSION_ID%TYPE,
149 p_dim_unit_code IN CN_RATE_DIM_TIERS.DIM_UNIT_CODE%TYPE,
150 p_minimum_amount IN CN_RATE_DIM_TIERS.MINIMUM_AMOUNT%TYPE := null,
151 p_maximum_amount IN CN_RATE_DIM_TIERS.MAXIMUM_AMOUNT%TYPE := null,
152 p_min_exp_id IN CN_RATE_DIM_TIERS.MIN_EXP_ID%TYPE := null,
153 p_max_exp_id IN CN_RATE_DIM_TIERS.MAX_EXP_ID%TYPE := null,
154 p_string_value IN CN_RATE_DIM_TIERS.STRING_VALUE%TYPE := null,
155 p_tier_sequence IN CN_RATE_DIM_TIERS.TIER_SEQUENCE%TYPE := null,
156 -- R12 MOAC Changes --Start
157 p_org_id IN CN_RATE_DIM_TIERS.ORG_ID%TYPE, --new
158 x_rate_dim_tier_id IN OUT NOCOPY CN_RATE_DIM_TIERS.RATE_DIM_TIER_ID%TYPE, --changed
159 -- R12 MOAC Changes --End
160 x_return_status OUT NOCOPY VARCHAR2 ,
161 x_msg_count OUT NOCOPY NUMBER ,
162 x_msg_data OUT NOCOPY VARCHAR2 );
163
164
165 END CN_RATE_DIMENSIONS_PVT;
[Home] [Help]