1 PACKAGE WSM_SPLIT_PERC_PVT AUTHID CURRENT_USER AS
2 /* $Header: WSMCOSPS.pls 115.1 2003/12/17 18:55:34 sthangad noship $ */
3
4 /*---------------------------------------------------------------------------+
5 | Procedure to insert a row in the split percentages table for a given |
6 | (co-product,co product group, effective date, disable date) |
7 +---------------------------------------------------------------------------*/
8
9 PROCEDURE insert_row(x_err_code OUT NOCOPY NUMBER,
10 x_err_msg OUT NOCOPY VARCHAR2,
11 p_co_product_id IN NUMBER,
12 p_co_product_group_id IN NUMBER,
13 p_organization_id IN NUMBER,
14 p_revision IN VARCHAR2,
15 p_split IN NUMBER,
16 p_primary_flag IN VARCHAR2,
17 p_effectivity_date IN DATE,
18 p_disable_date IN DATE,
19 p_creation_date IN DATE,
20 p_created_by IN NUMBER,
21 p_last_update_date IN DATE,
22 p_last_updated_by IN NUMBER,
23 p_last_update_login IN NUMBER DEFAULT NULL,
24 p_attribute_category IN VARCHAR2 DEFAULT NULL,
25 p_attribute1 IN VARCHAR2 DEFAULT NULL,
26 p_attribute2 IN VARCHAR2 DEFAULT NULL,
27 p_attribute3 IN VARCHAR2 DEFAULT NULL,
28 p_attribute4 IN VARCHAR2 DEFAULT NULL,
29 p_attribute5 IN VARCHAR2 DEFAULT NULL,
30 p_attribute6 IN VARCHAR2 DEFAULT NULL,
31 p_attribute7 IN VARCHAR2 DEFAULT NULL,
32 p_attribute8 IN VARCHAR2 DEFAULT NULL,
33 p_attribute9 IN VARCHAR2 DEFAULT NULL,
34 p_attribute10 IN VARCHAR2 DEFAULT NULL,
35 p_attribute11 IN VARCHAR2 DEFAULT NULL,
36 p_attribute12 IN VARCHAR2 DEFAULT NULL,
37 p_attribute13 IN VARCHAR2 DEFAULT NULL,
38 p_attribute14 IN VARCHAR2 DEFAULT NULL,
39 p_attribute15 IN VARCHAR2 DEFAULT NULL,
40 p_request_id IN NUMBER DEFAULT NULL,
41 p_program_application_id IN NUMBER DEFAULT NULL,
42 p_program_id IN NUMBER DEFAULT NULL,
43 p_program_update_date IN DATE DEFAULT NULL
44 );
45
46 /*---------------------------------------------------------------------------+
47 | Procedure to update a row in the split percentages table |
48 +---------------------------------------------------------------------------*/
49
50 PROCEDURE update_row(x_err_code OUT NOCOPY NUMBER,
51 x_err_msg OUT NOCOPY VARCHAR2,
52 p_rowid IN VARCHAR2,
53 p_co_product_id IN NUMBER,
54 p_co_product_group_id IN NUMBER,
55 p_organization_id IN NUMBER,
56 p_revision IN VARCHAR2,
57 p_split IN NUMBER,
58 p_primary_flag IN VARCHAR2,
59 p_effectivity_date IN DATE,
60 p_disable_date IN DATE,
61 p_creation_date IN DATE,
62 p_created_by IN NUMBER,
63 p_last_update_date IN DATE,
64 p_last_updated_by IN NUMBER,
65 p_last_update_login IN NUMBER,
66 p_attribute_category IN VARCHAR2,
67 p_attribute1 IN VARCHAR2,
68 p_attribute2 IN VARCHAR2,
69 p_attribute3 IN VARCHAR2,
70 p_attribute4 IN VARCHAR2,
71 p_attribute5 IN VARCHAR2,
72 p_attribute6 IN VARCHAR2,
73 p_attribute7 IN VARCHAR2,
74 p_attribute8 IN VARCHAR2,
75 p_attribute9 IN VARCHAR2,
76 p_attribute10 IN VARCHAR2,
77 p_attribute11 IN VARCHAR2,
78 p_attribute12 IN VARCHAR2,
79 p_attribute13 IN VARCHAR2,
80 p_attribute14 IN VARCHAR2,
81 p_attribute15 IN VARCHAR2,
82 p_request_id IN NUMBER,
83 p_program_application_id IN NUMBER,
84 p_program_id IN NUMBER,
85 p_program_update_date IN DATE
86 );
87 /*---------------------------------------------------------------------------+
88 | Lock row procedure |
89 | |
90 +---------------------------------------------------------------------------*/
91
92 PROCEDURE lock_row(x_err_code OUT NOCOPY NUMBER,
93 x_err_msg OUT NOCOPY VARCHAR2,
94 p_rowid IN VARCHAR2,
95 p_co_product_id IN NUMBER,
96 p_co_product_group_id IN NUMBER,
97 p_organization_id IN NUMBER,
98 p_revision IN VARCHAR2,
99 p_split IN NUMBER,
100 p_primary_flag IN VARCHAR2,
101 p_effectivity_date IN DATE,
102 p_disable_date IN DATE,
103 p_attribute_category IN VARCHAR2,
104 p_attribute1 IN VARCHAR2,
105 p_attribute2 IN VARCHAR2,
106 p_attribute3 IN VARCHAR2,
107 p_attribute4 IN VARCHAR2,
108 p_attribute5 IN VARCHAR2,
109 p_attribute6 IN VARCHAR2,
110 p_attribute7 IN VARCHAR2,
111 p_attribute8 IN VARCHAR2,
112 p_attribute9 IN VARCHAR2,
113 p_attribute10 IN VARCHAR2,
114 p_attribute11 IN VARCHAR2,
115 p_attribute12 IN VARCHAR2,
116 p_attribute13 IN VARCHAR2,
117 p_attribute14 IN VARCHAR2,
118 p_attribute15 IN VARCHAR2
119 );
120
121 /*---------------------------------------------------------------------------+
122 | Procedure to delete all the entries corresponding to a (co product id, |
123 | co product group id) pair in the split percentages table |
124 +---------------------------------------------------------------------------*/
125 PROCEDURE delete_row(x_err_code OUT NOCOPY NUMBER,
126 x_err_msg OUT NOCOPY VARCHAR2,
127 p_co_product_id IN NUMBER,
128 p_co_product_group_id IN NUMBER,
129 p_organization_id IN NUMBER
130 );
131 /*---------------------------------------------------------------------------+
132 | Procedure to delete all the records pertaining to a co product group id in |
133 | the split percentages table |
134 +---------------------------------------------------------------------------*/
135 PROCEDURE delete_all_range(x_err_code OUT NOCOPY NUMBER,
136 x_err_msg OUT NOCOPY VARCHAR2,
137 p_organization_id IN NUMBER,
138 p_co_product_group_id IN NUMBER);
139
140 /*---------------------------------------------------------------------------+
141 | Procedure to ensure that that no two ranges are overlapping in the time |
142 | frame. Called immediately after inserting a new split eff. range |
143 +---------------------------------------------------------------------------*/
144
145 PROCEDURE process_records (l_co_product_gr_id IN NUMBER,
146 from_eff_dt IN DATE,
147 to_eff_dt IN DATE,
148 x_err_code OUT NOCOPY NUMBER,
149 x_err_msg OUT NOCOPY VARCHAR2);
150
151 /*---------------------------------------------------------------------------+
152 | Procdure to check if the update of comp. eff/ disable date will cause |
153 | the deletion of any existent ranges |
154 +---------------------------------------------------------------------------*/
155
156 /* This procedure is not used as comp. eff./diable date will not be related to the
157 co product eff/disable dates */
158 FUNCTION validate_range ( p_co_product_group_id IN NUMBER,
159 p_organization_id IN NUMBER,
160 p_effectivity_date IN DATE,
161 p_disable_date IN DATE) RETURN NUMBER;
162
163 /*---------------------------------------------------------------------------+
164 | Procdure to update/delete any existent ranges that would be affected by the|
165 | the update of comp. eff. date/ disable date |
166 +---------------------------------------------------------------------------*/
167
168 /* This procedure is not used as comp. eff./diable date will not be related to the
169 co product eff/disable dates */
170
171 PROCEDURE update_split_range(x_err_code OUT NOCOPY NUMBER,
172 x_err_msg OUT NOCOPY VARCHAR2,
173 p_organization_id IN NUMBER,
174 p_co_product_group_id IN NUMBER,
175 p_effectivity_date IN DATE,
176 p_disable_date IN DATE,
177 p_update_range IN NUMBER
178 );
179
180 /*---------------------------------------------------------------------------+
181 | Procedure to insert a co-product in all ranges of a co-product group id |
182 | with split perc 0% in case of sec. co-product and 100% in case of |
183 | primary co-product |
184 +---------------------------------------------------------------------------*/
185 PROCEDURE insert_co_product_range(x_err_code OUT NOCOPY NUMBER,
186 x_err_msg OUT NOCOPY VARCHAR2,
187 p_co_product_group_id IN NUMBER,
188 p_co_product_id IN NUMBER,
189 p_revision IN VARCHAR2,
190 p_split IN NUMBER,
191 p_primary_flag IN VARCHAR2,
192 p_organization_id IN NUMBER,
193 p_effectivity_date IN DATE,
194 p_disable_date IN DATE,
195 p_creation_date IN DATE,
196 p_created_by IN NUMBER,
197 p_last_update_date IN DATE,
198 p_last_updated_by IN NUMBER
199 );
200 /*---------------------------------------------------------------------------+
201 | Procedure to check if there is atleast one range in wich the co product |
202 | passed has a non-zero split percentage.. |
203 +---------------------------------------------------------------------------*/
204 FUNCTION check_split_perc_exists(x_err_code OUT NOCOPY NUMBER,
205 x_err_msg OUT NOCOPY VARCHAR2,
206 p_co_product_id IN NUMBER,
207 p_co_product_group_id IN NUMBER,
208 p_organization_id IN NUMBER ) RETURN BOOLEAN;
209
210 /*---------------------------------------------------------------------------+
211 | Procedure to check if there is atleast one co product of a co-prod group |
212 | in the range passed that has a zero split percentage.. |
213 +--------------------------------------------------------------------------*/
214 FUNCTION check_split_perc_exists(x_err_code OUT NOCOPY NUMBER,
215 x_err_msg OUT NOCOPY VARCHAR2,
216 p_co_product_group_id IN NUMBER,
217 p_organization_id IN NUMBER,
218 p_effectivity_date IN DATE,
219 p_disable_date IN DATE) RETURN BOOLEAN;
220
221 /*---------------------------------------------------------------------------+
222 | Procedure to check if a co-product group id had got only one split |
223 | effectivity range |
224 +---------------------------------------------------------------------------*/
225 FUNCTION check_unique_range(x_err_code OUT NOCOPY NUMBER,
226 x_err_msg OUT NOCOPY VARCHAR2,
227 p_co_product_group_id IN NUMBER,
228 p_organization_id IN NUMBER ) RETURN BOOLEAN;
229
230 /*---------------------------------------------------------------------------+
231 | Procedure to check if the new eff. range ( eff date/ disable date ) |
232 | will cause any existing ranges to be deleted |
233 +---------------------------------------------------------------------------*/
234
235 FUNCTION check_any_del_range ( p_co_product_group_id IN NUMBER,
236 p_organization_id IN NUMBER,
237 p_effectivity_date IN DATE,
238 p_disable_date IN DATE) RETURN NUMBER;
239
240 /*---------------------------------------------------------------------------+
241 | Procedure to check if a range is preexisting |
242 +---------------------------------------------------------------------------*/
243
244 FUNCTION check_unique(x_err_code OUT NOCOPY NUMBER,
245 x_err_msg OUT NOCOPY VARCHAR2,
246 p_co_product_group_id IN NUMBER,
247 p_organization_id IN NUMBER,
248 p_effectivity_date IN DATE,
249 p_disable_date IN DATE) RETURN BOOLEAN;
250
251 END WSM_SPLIT_PERC_PVT;
[Home] [Help]