[Home] [Help]
PACKAGE BODY: APPS.CST_MOHRULES_PUB
Source
1 PACKAGE BODY cst_mohRules_pub AS
2 /* $Header: CSTMOHRB.pls 120.2.12020000.2 2012/07/11 13:06:38 vkatakam ship $*/
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_action_id NUMBER;
190 l_txn_type NUMBER;
191 l_source_type_id NUMBER;
192 l_stmt_num NUMBER;
193 l_item_id NUMBER;
194 l_item_type NUMBER;
195 l_count NUMBER;
196 l_rule_item_type NUMBER;
197 l_earn_moh NUMBER;
198 l_rule_count NUMBER;
199 l_debug VARCHAR2(80);
200 l_rule_id NUMBER;
201
202 BEGIN
203
204 l_debug := fnd_profile.value('MRP_DEBUG');
205
206 ------------------------------------------------
207 -- Standard call to check for API compatibility
208 ------------------------------------------------
209 l_stmt_num := 10;
210 IF not fnd_api.compatible_api_call (
211 l_api_version,
212 p_api_version,
213 l_api_name,
214 G_PKG_NAME ) then
215 RAISE fnd_api.G_exc_unexpected_error;
216 END IF;
217
218 ------------------------------------------------------------
219
220 ------------------------------------------------------------
221 -- Initialize message list if p_init_msg_list is set to TRUE
222 -------------------------------------------------------------
223 l_stmt_num := 20;
224 IF fnd_api.to_Boolean(p_init_msg_list) then
225 fnd_msg_pub.initialize;
226 end if;
227
228 -------------------------------------------------------------
229 -- Initialize API return status to Success
230 -------------------------------------------------------------
231 l_stmt_num := 30;
232 x_return_status := fnd_api.g_ret_sts_success;
233
234 -------------------------------------------------------------
235 -- Select transaction and item details.Treat RTV
236 -- like PO Receipt txnx
237 -------------------------------------------------------------
238
239 l_stmt_num := 40;
240 p_earn_moh := 1;
241 l_txn_type := 0;
242
243 IF (l_debug = 'Y') THEN
244 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IN Rule Package CST_MOHRULES_PUB.apply_moh');
245 END IF;
246
247 SELECT mmt.transaction_type_id, mmt.transaction_source_type_id, mmt.transaction_action_id
248 INTO l_txn_type_id, l_source_type_id, l_txn_action_id
249 FROM mtl_material_transactions mmt
250 WHERE transaction_id = p_txn_id;
251
252 l_stmt_num := 50;
253
254 SELECT planning_make_buy_code
255 INTO l_item_type
256 FROM mtl_system_items
257 WHERE inventory_item_id = p_item_id
258 AND organization_id = p_organization_id;
259
260 l_stmt_num := 60;
261
262 IF (l_source_type_id = 1 and ((l_txn_action_id = 27) or (l_txn_action_id = 1) or (l_txn_action_id = 29))) THEN /*PO Receipt/RTV*/
263 l_txn_type := 1;
264 ELSIF (l_source_type_id = 5 and ((l_txn_action_id = 32) or (l_txn_action_id = 31))) THEN
265 /* Assembly Completion/Return */
266 l_txn_type := 2;
267 ELSIF ((l_source_type_id = 8 and l_txn_action_id = 3) or (l_source_type_id = 7 and l_txn_action_id = 3)) THEN /*Internal Order direct transfer */
268 l_txn_type := 3;
269 ELSIF (l_source_type_id = 13 and l_txn_action_id = 3) THEN /* Inventory direct transfer */
270 l_txn_type := 4;
271 --
275 or (l_source_type_id = 7 and l_txn_action_id = 29)
272 -- Bug 5021305: Added txn types (65, 76) and (59, 60) for process/discrete xfers.
273 --
274 ELSIF ((l_source_type_id = 7 and l_txn_action_id = 12)
276 or (l_source_type_id = 8 and l_txn_action_id = 21)
277 or (l_source_type_id = 8 and l_txn_action_id = 22)
278 or (l_source_type_id = 7 and l_txn_action_id = 15)) THEN /* internal intransit*/
279 l_txn_type := 5;
280 ELSIF (l_source_type_id = 13 and ((l_txn_action_id = 12) or (l_txn_action_id = 21) or (l_txn_action_id = 29) or (l_txn_action_id = 15) or (l_txn_action_id = 22))) THEN /* inventory intransit*/
281 l_txn_type := 6;
282 ELSIF ((l_source_type_id = 1 and l_txn_action_id = 6)
283 or (l_source_type_id = 13 and l_txn_action_id = 6)) THEN /*Consigned ownership transfer transactions*/
284 l_txn_type := 7;
285 END IF;
286
287 l_stmt_num := 70;
288
289 IF (l_debug = 'Y') THEN
290 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_txn_type:' || l_txn_type);
291 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_txn_type_id:' || l_txn_type_id);
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'organization_id:' || p_organization_id);
293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'item_type:'|| l_item_type);
294 END IF;
295
296 IF(l_txn_type <> 0) THEN
297
298 BEGIN
299 SELECT count(*)
300 INTO l_count
301 FROM cst_material_ovhd_rules
302 WHERE transaction_type = l_txn_type
303 AND organization_id = p_organization_id;
304
305 IF (l_count > 0 ) THEN
306 BEGIN
307 SELECT earn_moh, rule_id, count(*)
308 INTO p_earn_moh,l_rule_id, l_rule_count
309 FROM cst_material_ovhd_rules
310 WHERE transaction_type = l_txn_type
311 AND organization_id = p_organization_id
312 AND item_type = l_item_type
313 GROUP BY earn_moh, rule_id;
314 EXCEPTION
315 WHEN others THEN
316 l_rule_count := 0;
317 END;
318
319 IF (l_rule_count = 0) THEN
320 SELECT earn_moh, rule_id,count(*)
321 INTO p_earn_moh,l_rule_id,l_rule_count
322 FROM cst_material_ovhd_rules
323 WHERE transaction_type = l_txn_type
324 AND organization_id = p_organization_id
325 AND item_type = 3
326 GROUP BY earn_moh, rule_id;
327 END IF;
328
329 END IF;
330 EXCEPTION
331 WHEN others THEN
332 p_earn_moh := 1;
333 END;
334 END IF;
335
336 IF(l_count > 1) THEN
337 FND_MESSAGE.SET_NAME('BOM', 'CST_RULE_MULTIPLE');
338 FND_MESSAGE.SET_TOKEN('RULE_ID',l_rule_id);
339 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
340 END IF;
341
342 IF (l_debug = 'Y') THEN
346 EXCEPTION
343 FND_FILE.PUT_LINE(FND_FILE.LOG, 'earn moh :' || p_earn_moh);
344 END IF;
345
347 WHEN fnd_api.g_exc_error then
348 x_return_status := fnd_api.g_ret_sts_error;
349
350 fnd_msg_pub.count_and_get
351 ( p_count => x_msg_count,
352 p_data => x_msg_data );
353
354 WHEN fnd_api.g_exc_unexpected_error then
355 x_return_status := fnd_api.g_ret_sts_unexp_error;
356
357 fnd_msg_pub.count_and_get
358 ( p_count => x_msg_count,
359 p_data => x_msg_data );
360
361 WHEN OTHERS THEN
362 x_return_status := fnd_api.g_ret_sts_unexp_error ;
363 If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
364 fnd_msg_pub.add_exc_msg
365 ( 'CST_MOH_RULES_PUB','apply_moh : Statement - ' || to_char(l_stmt_num));
366 end if;
367
368 fnd_msg_pub.count_and_get( p_count => x_msg_count,
369 p_data => x_msg_data );
370
371 END apply_moh;
372
373
374 END;