[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_APPLET_PKG
Source
1 PACKAGE BODY BOM_RTG_APPLET_PKG AS
2 /* $Header: BOMJONWB.pls 120.1 2006/03/02 01:38:03 vhymavat noship $ */
3
4 PROCEDURE Associate_Event(
5 x_event_op_seq_id NUMBER,
6 x_operation_type NUMBER,
7 x_new_parent_op_seq_id NUMBER,
8 x_last_updated_by NUMBER,
9 x_last_update_date DATE,
10 x_return_code OUT NOCOPY VARCHAR,
11 x_error_msg OUT NOCOPY VARCHAR
12 ) IS
13 BEGIN
14 IF (x_operation_type = 2) THEN
15 UPDATE BOM_OPERATION_SEQUENCES
16 SET
17 process_op_seq_id = x_new_parent_op_seq_id,
18 last_updated_by = x_last_updated_by,
19 last_update_date = NVL(x_last_update_date, SYSDATE)
20 WHERE operation_sequence_id = x_event_op_seq_id;
21 ELSIF (x_operation_type = 3) THEN
22 UPDATE BOM_OPERATION_SEQUENCES
23 SET
24 line_op_seq_id = x_new_parent_op_seq_id,
25 last_updated_by = x_last_updated_by,
26 last_update_date = NVL(x_last_update_date, SYSDATE)
27 WHERE operation_sequence_id = x_event_op_seq_id;
28 END IF;
29 IF (SQL%NOTFOUND) THEN
30 FND_MESSAGE.SET_NAME('BOM','BOM_EVNT_DOES_NOT_EXIST');
31 x_return_code := 'F';
32 x_error_msg := FND_MESSAGE.GET;
33 ELSE
34 x_return_code := 'S';
35 x_error_msg := '';
36 END IF;
37
38 END Associate_Event;
39
40 PROCEDURE Alter_Link(
41 x_from_op_seq_id NUMBER,
42 x_to_op_seq_id NUMBER,
43 x_transition_type NUMBER,
44 x_planning_pct NUMBER,
45 x_transaction_type VARCHAR2,
46 -- x_effectivity_date DATE,
47 -- x_disable_date DATE,
48 x_last_updated_by NUMBER,
49 x_creation_date DATE,
50 x_last_update_date DATE,
51 x_created_by NUMBER,
52 x_last_update_login NUMBER,
53 x_attribute_category VARCHAR2,
54 x_attribute1 VARCHAR2,
55 x_attribute2 VARCHAR2,
56 x_attribute3 VARCHAR2,
57 x_attribute4 VARCHAR2,
58 x_attribute5 VARCHAR2,
59 x_attribute6 VARCHAR2,
60 x_attribute7 VARCHAR2,
61 x_attribute8 VARCHAR2,
62 x_attribute9 VARCHAR2,
63 x_attribute10 VARCHAR2,
64 x_attribute11 VARCHAR2,
65 x_attribute12 VARCHAR2,
66 x_attribute13 VARCHAR2,
67 x_attribute14 VARCHAR2,
68 x_attribute15 VARCHAR2,
69 x_return_code OUT NOCOPY VARCHAR2,
70 x_error_msg OUT NOCOPY VARCHAR2
71 ) IS
72
73 l_return_code VARCHAR2(1) := 'S';
74 l_error_msg VARCHAR2(2000) := '';
75
76 BEGIN
77 IF (x_transaction_type = 'insert') THEN
78
79 -- Call validate and continue if valid
80 Validate_Link(
81 x_from_op_seq_id => x_from_op_seq_id,
82 x_to_op_seq_id => x_to_op_seq_id,
83 x_transition_type => x_transition_type,
84 x_planning_pct => x_planning_pct,
85 x_transaction_type => x_transaction_type,
86 x_return_code => l_return_code,
87 x_error_msg => l_error_msg
88 );
89
90 IF (l_return_code = 'S') THEN
91 INSERT INTO BOM_OPERATION_NETWORKS(
92 from_op_seq_id,
93 to_op_seq_id,
94 transition_type,
95 planning_pct,
96 last_updated_by,
97 creation_date,
98 last_update_date,
99 created_by,
100 last_update_login,
101 attribute_category,
102 attribute1,
103 attribute2,
104 attribute3,
105 attribute4,
106 attribute5,
107 attribute6,
108 attribute7,
109 attribute8,
110 attribute9,
111 attribute10,
112 attribute11,
113 attribute12,
114 attribute13,
115 attribute14,
116 attribute15
117 ) VALUES (
118 x_from_op_seq_id,
119 x_to_op_seq_id,
120 x_transition_type,
121 x_planning_pct,
122 x_last_updated_by,
123 NVL(x_creation_date, SYSDATE),
124 NVL(x_last_update_date, SYSDATE),
125 x_created_by,
126 x_last_update_login,
127 x_attribute_category,
128 x_attribute1,
129 x_attribute2,
130 x_attribute3,
131 x_attribute4,
132 x_attribute5,
133 x_attribute6,
134 x_attribute7,
135 x_attribute8,
136 x_attribute9,
137 x_attribute10,
138 x_attribute11,
139 x_attribute12,
140 x_attribute13,
141 x_attribute14,
142 x_attribute15
143 );
144 END IF;
145
146 ELSIF (x_transaction_type = 'update') THEN
147 -- Call Lock row before updation
148
149 -- Call validate and continue if valid
150 Validate_Link(
151 x_from_op_seq_id => x_from_op_seq_id,
152 x_to_op_seq_id => x_to_op_seq_id,
153 x_transition_type => x_transition_type,
154 x_planning_pct => x_planning_pct,
155 x_transaction_type => x_transaction_type,
156 x_return_code => l_return_code,
157 x_error_msg => l_error_msg
158 );
159
160 IF (l_return_code = 'S') THEN
161 UPDATE BOM_OPERATION_NETWORKS
162 SET
163 transition_type = x_transition_type,
164 planning_pct = x_planning_pct,
165 last_update_date = NVL(x_last_update_date, SYSDATE),
166 last_updated_by = x_last_updated_by
167 WHERE from_op_seq_id = x_from_op_seq_id
168 and to_op_seq_id = x_to_op_seq_id;
169 END IF;
170
171 ELSIF (x_transaction_type = 'delete') THEN
172 -- Call Lock row before deletion
173
174 DELETE FROM BOM_OPERATION_NETWORKS
175 WHERE from_op_seq_id = x_from_op_seq_id
176 and to_op_seq_id = x_to_op_seq_id;
177 END IF;
178
179 IF (SQL%NOTFOUND) THEN
180 FND_MESSAGE.SET_NAME('BOM','BOM_EVNT_DOES_NOT_EXIST');
181 x_return_code := 'F';
182 x_error_msg := FND_MESSAGE.GET;
183 ELSE
184 x_return_code := l_return_code;
185 x_error_msg := l_error_msg;
186 END IF;
187
188 END Alter_Link;
189
190 PROCEDURE Validate_Link(
191 x_from_op_seq_id IN NUMBER,
192 x_to_op_seq_id IN NUMBER,
193 x_transition_type IN NUMBER,
194 x_planning_pct IN NUMBER,
195 x_transaction_type IN VARCHAR2,
196 x_return_code OUT NOCOPY VARCHAR2,
197 x_error_msg OUT NOCOPY VARCHAR2
198 ) IS
199
200 dummy NUMBER;
201 primary_exists NUMBER := 0;
202 link_exists NUMBER := 0;
203 sum_planning_pct NUMBER := 0;
204 from_op_seq_num NUMBER;
205 to_op_seq_num NUMBER;
206 op_type NUMBER;
207 l_from_op_seq_id NUMBER;
208 l_to_op_seq_id NUMBER;
209 l_planning_pct NUMBER := 0;
210 l_transition_type NUMBER;
211
212 BEGIN
213
214 IF (x_transaction_type = 'insert') THEN
215 -- Only one primary
216 IF (x_transition_type = 1) THEN
217 SELECT count(*)
218 INTO primary_exists
219 FROM BOM_OPERATION_NETWORKS
220 WHERE from_op_seq_id = x_from_op_seq_id
221 and transition_type = 1;
222 END IF;
223
224 -- Only one link between the same nodes
225 SELECT count(*)
226 INTO link_exists
227 FROM BOM_OPERATION_NETWORKS
228 WHERE from_op_seq_id = x_from_op_seq_id
229 and to_op_seq_id = x_to_op_seq_id;
230
231 -- Sum of planning_pct should be <= 100
232 SELECT sum(planning_pct)
233 INTO sum_planning_pct
234 FROM BOM_OPERATION_NETWORKS
235 WHERE from_op_seq_id = x_from_op_seq_id
236 AND transition_type IN (1, 2);
237
238 ELSIF (x_transaction_type = 'update') THEN
239 BEGIN
240 SELECT from_op_seq_id, to_op_seq_id,
241 transition_type, planning_pct
242 INTO l_from_op_seq_id, l_to_op_seq_id,
243 l_transition_type, l_planning_pct
244 FROM BOM_OPERATION_NETWORKS
245 WHERE from_op_seq_id = x_from_op_seq_id
246 and to_op_seq_id = x_to_op_seq_id
247 and transition_type IN (1, 2);
248 EXCEPTION
249 WHEN NO_DATA_FOUND THEN
250 SELECT from_op_seq_id, to_op_seq_id,
251 transition_type, planning_pct
252 INTO l_from_op_seq_id, l_to_op_seq_id,
253 l_transition_type, l_planning_pct
254 FROM BOM_OPERATION_NETWORKS
255 WHERE from_op_seq_id = x_from_op_seq_id
256 and to_op_seq_id = x_to_op_seq_id
257 and transition_type = 3;
258 END;
259 -- Only one primary
260 IF (l_transition_type <> x_transition_type
261 and l_transition_type <> 1 and x_transition_type = 1) THEN
262 SELECT count(*)
263 INTO primary_exists
264 FROM BOM_OPERATION_NETWORKS
265 WHERE from_op_seq_id = x_from_op_seq_id
266 and to_op_seq_id <> x_to_op_seq_id
267 and transition_type = 1;
268 END IF;
269
270 -- Only one link validation NOT reqd for update
271
272 -- Sum of planning_pct should be <= 100
273 if l_transition_type <> 3 then
274 SELECT sum(planning_pct) - l_planning_pct
275 INTO sum_planning_pct
276 FROM BOM_OPERATION_NETWORKS
277 WHERE from_op_seq_id = x_from_op_seq_id
278 AND transition_type IN (1, 2);
279 else
280 sum_planning_pct := l_planning_pct;
281 end if;
282
283 END IF;
284
285 IF (primary_exists = 1) THEN
286
287 SELECT operation_type, operation_seq_num
288 INTO op_type, from_op_seq_num
289 FROM bom_operation_sequences
290 WHERE operation_sequence_id = x_from_op_seq_id;
291
292 FND_MESSAGE.SET_NAME('BOM','BOM_CHECK_UNIQUE_PRIMARY');
293 IF (op_type = 2) THEN
294 FND_MESSAGE.SET_TOKEN('OPERATION', 'BOM_PROCESS', TRUE);
295 ELSE
296 FND_MESSAGE.SET_TOKEN('OPERATION', 'BOM_LINE_OPERATION', TRUE);
297 END IF;
298 FND_MESSAGE.SET_TOKEN('SEQUENCE_NUMBER',to_char(from_op_seq_num), FALSE);
299
300 x_return_code := 'F';
301 x_error_msg := FND_MESSAGE.GET;
302
303 ELSIF (link_exists = 1) THEN
304
305 SELECT operation_seq_num
306 INTO from_op_seq_num
307 FROM bom_operation_sequences
308 WHERE operation_sequence_id = x_from_op_seq_id;
309
310 SELECT operation_seq_num
311 INTO to_op_seq_num
312 FROM bom_operation_sequences
313 WHERE operation_sequence_id = x_to_op_seq_id;
314
315 FND_MESSAGE.SET_NAME('BOM','BOM_LINK_ALREADY_EXISTS');
316 FND_MESSAGE.SET_TOKEN('FROM_OP_SEQ_ID',to_char(from_op_seq_num), FALSE);
317 FND_MESSAGE.SET_TOKEN('TO_OP_SEQ_ID',to_char(to_op_seq_num), FALSE);
318 x_return_code := 'F';
319 x_error_msg := FND_MESSAGE.GET;
320
321 ELSIF (x_transition_type <> 3
322 and (nvl(sum_planning_pct,0) + x_planning_pct) > 100)
323 OR (x_transition_type = 3 and (nvl(sum_planning_pct,0) > 100)) THEN
324
325 SELECT operation_type, operation_seq_num
326 INTO op_type, from_op_seq_num
327 FROM bom_operation_sequences
328 WHERE operation_sequence_id = x_from_op_seq_id;
329
330 FND_MESSAGE.SET_NAME('BOM','BOM_CHECK_PLANNING_PERCENT');
331 IF (op_type = 2) THEN
332 FND_MESSAGE.SET_TOKEN('OPERATION', 'BOM_PROCESS', TRUE);
333 ELSE
334 FND_MESSAGE.SET_TOKEN('OPERATION', 'BOM_LINE_OPERATION', TRUE);
335 END IF;
336 FND_MESSAGE.SET_TOKEN('SEQUENCE_NUMBER',to_char(from_op_seq_num), FALSE);
337
338 x_return_code := 'F';
339 x_error_msg := FND_MESSAGE.GET;
340
341 ELSE
342 x_return_code := 'S';
343 x_error_msg := '';
344 END IF;
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 FND_MESSAGE.SET_NAME('BOM','BOM_EVNT_DOES_NOT_EXIST');
349 x_return_code := 'F';
350 x_error_msg := FND_MESSAGE.GET;
351
352 END Validate_Link;
353
354 PROCEDURE Move_Node(
355 x_operation_sequence_id NUMBER,
356 x_x_coordinate NUMBER,
357 x_y_coordinate NUMBER,
358 x_last_updated_by NUMBER,
359 x_last_update_date DATE,
360 x_return_code OUT NOCOPY VARCHAR2,
361 x_error_msg OUT NOCOPY VARCHAR2
362 ) IS
363 BEGIN
364 UPDATE BOM_OPERATION_SEQUENCES
365 SET
366 x_coordinate = x_x_coordinate,
367 y_coordinate = x_y_coordinate,
368 last_updated_by = x_last_updated_by,
369 last_update_date = NVL(x_last_update_date, SYSDATE)
370 WHERE operation_sequence_id = x_operation_sequence_id;
371 IF (SQL%NOTFOUND) THEN
372 FND_MESSAGE.SET_NAME('BOM','BOM_EVNT_DOES_NOT_EXIST');
373 x_return_code := 'F';
374 x_error_msg := FND_MESSAGE.GET;
375 ELSE
376 x_return_code := 'S';
377 x_error_msg := '';
378 END IF;
379 END Move_Node;
380
381 END BOM_RTG_APPLET_PKG;