[Home] [Help]
PACKAGE BODY: APPS.CST_MOHRULES_PUB
Source
1 PACKAGE BODY cst_mohRules_pub AS
2 /* $Header: CSTMOHRB.pls 120.1 2006/02/23 20:14:15 umoogala noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cst_mohRules_pub';
5
6 PROCEDURE INSERT_ROW_MOH(
7 p_rule_id IN NUMBER,
8 p_last_update_date IN DATE,
9 p_creation_date IN DATE,
10 p_last_updated_by IN NUMBER,
11 p_created_by IN NUMBER,
12 p_organization_id IN NUMBER,
13 p_earn_moh IN NUMBER,
14 p_transaction_type IN NUMBER,
15 p_selection_criteria IN NUMBER ,
16 p_category_id IN NUMBER ,
17 p_item_from IN NUMBER ,
18 p_item_to IN NUMBER ,
19 p_item_type IN NUMBER,
20 p_ship_from_org IN NUMBER ,
21 p_cost_type_id IN NUMBER,
22 err_code OUT NOCOPY NUMBER,
23 err_msg OUT NOCOPY VARCHAR2 ) IS
24
25 l_stmt_num NUMBER;
26 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row_MOH';
27 l_api_version CONSTANT NUMBER := 1.0;
28
29
30
31 BEGIN
32
33 ---------------------------------------------
34 -- Standard start of API savepoint
35 ---------------------------------------------
36 SAVEPOINT insert_row_moh;
37
38 -------------------------------------------------------------
39 -- Initialize API return status to Success
40 -------------------------------------------------------------
41 l_stmt_num := 30;
42 err_code :=0;
43
44 l_stmt_num := 40;
45
46 INSERT INTO cst_material_ovhd_rules
47 (rule_id,
48 last_update_date,
49 creation_date,
50 last_updated_by,
51 created_by,
52 organization_id,
53 selection_criteria,
54 earn_moh,
55 item_type,
56 cost_type_id,
57 ship_from_org,
58 category_id,
59 item_from,
60 item_to,
61 transaction_type
62 )
63 VALUES
64 (p_rule_id,
65 p_last_update_date,
66 p_creation_date,
67 p_last_updated_by,
68 p_created_by,
69 p_organization_id,
70 p_selection_criteria,
71 p_earn_moh,
72 p_item_type,
73 p_cost_type_id,
74 p_ship_from_org,
75 p_category_id,
76 p_item_from,
77 p_item_to,
78 p_transaction_type
79 );
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 err_msg := 'CST_MOH_RULES_PUB.insert_row_moh(' ||l_stmt_num|| '): Error while inserting';
84 err_code := -1;
85 ROLLBACK;
86 END insert_row_moh;
87
88
89 PROCEDURE update_row_moh(
90 p_rule_id IN NUMBER,
91 p_last_update_date IN DATE,
92 p_last_updated_by IN NUMBER,
93 p_earn_moh IN NUMBER,
94 p_transaction_type IN NUMBER,
95 p_selection_criteria IN NUMBER ,
96 p_category_id IN NUMBER ,
97 p_item_from IN NUMBER ,
98 p_item_to IN NUMBER ,
99 p_item_type IN NUMBER,
100 p_ship_from_org IN NUMBER ,
101 p_cost_type_id IN NUMBER,
102 err_code OUT NOCOPY NUMBER,
103 err_msg OUT NOCOPY VARCHAR2
104 ) IS
105
106 BEGIN
107 err_code := 0;
108
109 UPDATE cst_material_ovhd_rules
110 SET last_update_date = p_last_update_date,
111 last_updated_by = p_last_updated_by,
112 earn_moh = p_earn_moh,
113 selection_criteria = p_selection_criteria,
114 category_id = p_category_id,
115 item_from = p_item_from,
116 item_to = p_item_to ,
117 item_type = p_item_type,
118 ship_from_org = p_ship_from_org,
119 cost_type_id = p_cost_type_id
120 WHERE rule_id = p_rule_id;
121
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 ROLLBACK;
126 err_code := -1;
127 err_msg := sqlerrm;
128
129 END update_row_moh;
130
131 PROCEDURE delete_row_moh(
132 p_rule_id IN NUMBER,
133 err_code OUT NOCOPY NUMBER,
134 err_msg OUT NOCOPY VARCHAR2
135 ) IS
136
137 BEGIN
138
139 err_code := 0;
140
141 DELETE FROM cst_material_ovhd_rules
142 WHERE rule_id = p_rule_id;
143
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 ROLLBACK;
148 err_code := -1;
149 err_msg := sqlerrm;
150
151 END delete_row_moh;
152
153 ----------------------------------------------------------------------------
154 -- PROCEDURE --
155 -- apply_moh --
156 -- --
157 -- DESCRIPTION --
158 -- This API determines if default MOH absorption is overriden for the --
159 -- given transaction. --
160 -- --
161 -- PURPOSE: --
162 -- Oracle Applications Rel 11i.8 --
163 -- Rules Engine for MOH Absorption --
164 -- --
165 -- --
166 -- HISTORY: --
167 -- 12/03/01 Anju Gupta Created --
168 ----------------------------------------------------------------------------
169
170 PROCEDURE apply_moh(
171 p_api_version IN NUMBER,
172 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
173 p_commit IN VARCHAR2 := FND_API.G_FALSE,
174 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
175 p_organization_id IN NUMBER,
176 p_earn_moh OUT NOCOPY NUMBER,
177 p_txn_id IN NUMBER,
178 p_item_id IN NUMBER,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2 ) IS
182
183 l_api_name CONSTANT VARCHAR2(30) := 'apply_moh';
184 l_api_version CONSTANT NUMBER := 1.0;
185
186 l_api_message VARCHAR2(240);
187
188 l_txn_type_id NUMBER;
189 l_txn_type NUMBER;
190 l_source_type_id NUMBER;
191 l_stmt_num NUMBER;
192 l_item_id NUMBER;
193 l_item_type NUMBER;
194 l_count NUMBER;
195 l_rule_item_type NUMBER;
196 l_earn_moh NUMBER;
197 l_rule_count NUMBER;
198 l_debug VARCHAR2(80);
199 l_rule_id NUMBER;
200
201 BEGIN
202
203 l_debug := fnd_profile.value('MRP_DEBUG');
204
205 ------------------------------------------------
206 -- Standard call to check for API compatibility
207 ------------------------------------------------
208 l_stmt_num := 10;
209 IF not fnd_api.compatible_api_call (
210 l_api_version,
211 p_api_version,
212 l_api_name,
213 G_PKG_NAME ) then
214 RAISE fnd_api.G_exc_unexpected_error;
215 END IF;
216
217 ------------------------------------------------------------
218
219 ------------------------------------------------------------
220 -- Initialize message list if p_init_msg_list is set to TRUE
221 -------------------------------------------------------------
222 l_stmt_num := 20;
223 IF fnd_api.to_Boolean(p_init_msg_list) then
224 fnd_msg_pub.initialize;
225 end if;
226
227 -------------------------------------------------------------
228 -- Initialize API return status to Success
229 -------------------------------------------------------------
230 l_stmt_num := 30;
231 x_return_status := fnd_api.g_ret_sts_success;
232
233 -------------------------------------------------------------
234 -- Select transaction and item details.Treat RTV
235 -- like PO Receipt txnx
236 -------------------------------------------------------------
237
238 l_stmt_num := 40;
239 p_earn_moh := 1;
240 l_txn_type := 0;
241
242 IF (l_debug = 'Y') THEN
243 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IN Rule Package CST_MOHRULES_PUB.apply_moh');
244 END IF;
245
246 SELECT mmt.transaction_type_id, mmt.transaction_source_type_id
247 INTO l_txn_type_id, l_source_type_id
248 FROM mtl_material_transactions mmt
249 WHERE transaction_id = p_txn_id;
250
251 l_stmt_num := 50;
252
253 SELECT planning_make_buy_code
254 INTO l_item_type
255 FROM mtl_system_items
256 WHERE inventory_item_id = p_item_id
257 AND organization_id = p_organization_id;
258
259 l_stmt_num := 60;
260
261 IF (l_txn_type_id = 36 OR l_txn_type_id = 18 OR l_txn_type_id = 71) THEN
262 /*PO Receipt/RTV*/
263 l_txn_type := 1;
264 ELSIF (l_txn_type_id = 44 OR l_txn_type_id = 17) THEN /* Assembly Completion/Return */
265 l_txn_type := 2;
266 ELSIF (l_txn_type_id = 54) THEN/*Internal Order direct transfer */
267 l_txn_type := 3;
268 ELSIF (l_txn_type_id = 3) THEN /* Inventory direct transfer */
269 l_txn_type := 4;
270 --
271 -- Bug 5021305: Added txn types (65, 76) and (59, 60) for process/discrete xfers.
272 --
273 ELSIF (l_txn_type_id = 61 OR l_txn_type_id = 62 OR l_txn_type = 72 OR l_txn_type_id = 65 OR l_txn_type_id = 76) THEN/* internal intransit*/
274 l_txn_type := 5;
275 ELSIF (l_txn_type_id = 12 OR l_txn_type_id = 21 OR l_txn_type = 70 OR l_txn_type_id = 59 OR l_txn_type_id = 60) THEN/* inventory intransit*/
276
277 l_txn_type := 6;
278 ELSIF (l_txn_type_id = 74 OR l_txn_type_id = 75) THEN/*Consigned ownership transfer transactions*/
279 l_txn_type := 7;
280 END IF;
281
282 l_stmt_num := 70;
283
284 IF (l_debug = 'Y') THEN
285 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_txn_type:' || l_txn_type);
286 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_txn_type_id:' || l_txn_type_id);
287 FND_FILE.PUT_LINE(FND_FILE.LOG, 'organization_id:' || p_organization_id);
288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'item_type:'|| l_item_type);
289 END IF;
290
291 IF(l_txn_type <> 0) THEN
292
293 BEGIN
294 SELECT count(*)
295 INTO l_count
296 FROM cst_material_ovhd_rules
297 WHERE transaction_type = l_txn_type
298 AND organization_id = p_organization_id;
299
300 IF (l_count > 0 ) THEN
301 BEGIN
302 SELECT earn_moh, rule_id, count(*)
303 INTO p_earn_moh,l_rule_id, l_rule_count
304 FROM cst_material_ovhd_rules
305 WHERE transaction_type = l_txn_type
306 AND organization_id = p_organization_id
307 AND item_type = l_item_type
308 GROUP BY earn_moh, rule_id;
309 EXCEPTION
310 WHEN others THEN
311 l_rule_count := 0;
312 END;
313
314 IF (l_rule_count = 0) THEN
315 SELECT earn_moh, rule_id,count(*)
316 INTO p_earn_moh,l_rule_id,l_rule_count
317 FROM cst_material_ovhd_rules
318 WHERE transaction_type = l_txn_type
319 AND organization_id = p_organization_id
320 AND item_type = 3
321 GROUP BY earn_moh, rule_id;
322 END IF;
323
324 END IF;
325 EXCEPTION
326 WHEN others THEN
327 p_earn_moh := 1;
328 END;
329 END IF;
330
331 IF(l_count > 1) THEN
332 FND_MESSAGE.SET_NAME('BOM', 'CST_RULE_MULTIPLE');
333 FND_MESSAGE.SET_TOKEN('RULE_ID',l_rule_id);
334 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
335 END IF;
336
337 IF (l_debug = 'Y') THEN
338 FND_FILE.PUT_LINE(FND_FILE.LOG, 'earn moh :' || p_earn_moh);
339 END IF;
340
341 EXCEPTION
342 WHEN fnd_api.g_exc_error then
343 x_return_status := fnd_api.g_ret_sts_error;
344
345 fnd_msg_pub.count_and_get
346 ( p_count => x_msg_count,
347 p_data => x_msg_data );
348
349 WHEN fnd_api.g_exc_unexpected_error then
350 x_return_status := fnd_api.g_ret_sts_unexp_error;
351
352 fnd_msg_pub.count_and_get
353 ( p_count => x_msg_count,
354 p_data => x_msg_data );
355
356 WHEN OTHERS THEN
357 x_return_status := fnd_api.g_ret_sts_unexp_error ;
358 If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
359 fnd_msg_pub.add_exc_msg
360 ( 'CST_MOH_RULES_PUB','apply_moh : Statement - ' || to_char(l_stmt_num));
361 end if;
362
363 fnd_msg_pub.count_and_get( p_count => x_msg_count,
364 p_data => x_msg_data );
365
366 END apply_moh;
367
368
369 END;