[Home] [Help]
PACKAGE BODY: APPS.MRP_SOURCING_RULE_PVT
Source
1 PACKAGE BODY MRP_Sourcing_Rule_PVT AS
2 /* $Header: MRPVSRLB.pls 120.1 2006/05/25 05:25:41 atsrivas noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Sourcing_Rule_PVT';
7
8 -- Sourcing_Rule
9
10 PROCEDURE Sourcing_Rule
11 ( p_validation_level IN NUMBER
12 , p_control_rec IN MRP_GLOBALS.Control_Rec_Type
13 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
14 , p_old_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
15 , x_Sourcing_Rule_rec OUT NOCOPY MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
16 , x_old_Sourcing_Rule_rec OUT NOCOPY MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
17 )
18 IS
19 l_return_status VARCHAR2(1);
20 l_control_rec MRP_GLOBALS.Control_Rec_Type;
21 l_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type := p_Sourcing_Rule_rec;
22 -- Nocopy Change
23 l_Sourcing_Rule_out_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type ;
24 l_old_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type := p_old_Sourcing_Rule_rec;
25 BEGIN
26
27 -- Load API control record
28
29 -- dbms_output.put_line ('Oper : ' || l_Sourcing_Rule_rec.operation);
30 l_control_rec := MRP_GLOBALS.Init_Control_Rec
31 ( p_operation => l_Sourcing_Rule_rec.operation
32 , p_control_rec => p_control_rec
33 );
34
35 -- Set record return status.
36
37 l_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_SUCCESS;
38
39 -- Prepare record.
40
41 IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_CREATE THEN
42
43 l_Sourcing_Rule_rec.db_flag := FND_API.G_FALSE;
44
45 -- Set missing old record elements to NULL.
46
47 l_old_Sourcing_Rule_rec :=
48 MRP_Sourcing_Rule_Util.Convert_Miss_To_Null (l_old_Sourcing_Rule_rec);
49
50
51 ELSIF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_UPDATE
52 OR l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_DELETE
53 THEN
54
55 l_Sourcing_Rule_rec.db_flag := FND_API.G_TRUE;
56
57 -- Query Old if missing
58
59 IF l_old_Sourcing_Rule_rec.Sourcing_Rule_Id = FND_API.G_MISS_NUM
60 THEN
61
62 l_old_Sourcing_Rule_rec := MRP_Sourcing_Rule_Handlers.Query_Row
63 ( p_Sourcing_Rule_Id => l_Sourcing_Rule_rec.Sourcing_Rule_Id
64 );
65
66 ELSE
67
68 -- Set missing old record elements to NULL.
69
70 l_old_Sourcing_Rule_rec :=
71 MRP_Sourcing_Rule_Util.Convert_Miss_To_Null (l_old_Sourcing_Rule_rec);
72
73 END IF;
74
75 -- Complete new record from old
76
77 l_Sourcing_Rule_rec := MRP_Sourcing_Rule_Util.Complete_Record
78 ( p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
79 , p_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_rec
80 );
81
82 END IF;
83
84 -- Attribute level validation.
85
86 IF l_control_rec.default_attributes
87 OR l_control_rec.change_attributes
88 THEN
89
90 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
91
92 MRP_Validate_Sourcing_Rule.Attributes
93 ( x_return_status => l_return_status
94 , p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
95 , p_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_rec
96 );
97
98 -- dbms_output.put_line('after attributes : ' || l_return_status);
99
100 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
103 RAISE FND_API.G_EXC_ERROR;
104 END IF;
105
106 END IF;
107
108 END IF;
109
110 -- Clear dependent attributes.
111
112 IF l_control_rec.change_attributes THEN
113
114 MRP_Sourcing_Rule_Util.Clear_Dependent_Attr
115 ( p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
116 , p_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_rec
117 , x_Sourcing_Rule_rec => l_Sourcing_Rule_out_rec -- Nocopy Change
118 );
119 l_Sourcing_Rule_rec := l_Sourcing_Rule_out_rec ; -- Nocopy Change
120
121 END IF;
122
123 -- Default missing attributes
124
125 IF l_control_rec.default_attributes
126 OR l_control_rec.change_attributes
127 THEN
128
129 MRP_Default_Sourcing_Rule.Attributes
130 ( p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
131 , x_Sourcing_Rule_rec => l_Sourcing_Rule_out_rec -- Nocopy Change
132 );
133 l_Sourcing_Rule_rec := l_Sourcing_Rule_out_rec ; -- Nocopy Change
134
135 END IF;
136
137 -- Apply attribute changes
138
139 IF l_control_rec.default_attributes
140 OR l_control_rec.change_attributes
141 THEN
142
143 MRP_Sourcing_Rule_Util.Apply_Attribute_Changes
144 ( p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
145 , p_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_rec
146 , x_Sourcing_Rule_rec => l_Sourcing_Rule_out_rec -- Nocopy Change
147 );
148 l_Sourcing_Rule_rec := l_Sourcing_Rule_out_rec ; -- Nocopy Change
149
150 END IF;
151
152 -- Entity level validation.
153
154 IF l_control_rec.validate_entity THEN
155
156 IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_DELETE THEN
157
158 MRP_Validate_Sourcing_Rule.Entity_Delete
159 ( x_return_status => l_return_status
160 , p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
161 );
162
163 ELSE
164
165 MRP_Validate_Sourcing_Rule.Entity
166 ( x_return_status => l_return_status
167 , p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
168 , p_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_rec
169 );
170
171 END IF;
172
173 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178
179 END IF;
180
181 -- Step 4. Write to DB
182
183 IF l_control_rec.write_to_db THEN
184
185 IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_DELETE THEN
186
187 MRP_Sourcing_Rule_Handlers.Delete_Row
188 ( p_Sourcing_Rule_Id => l_Sourcing_Rule_rec.Sourcing_Rule_Id
189 );
190
191 ELSE
192
193 -- Get Who Information
194
195 l_Sourcing_Rule_rec.last_update_date := SYSDATE;
196 l_Sourcing_Rule_rec.last_updated_by := FND_GLOBAL.USER_ID;
197 l_Sourcing_Rule_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
198
199 IF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_UPDATE THEN
200
201 MRP_Sourcing_Rule_Handlers.Update_Row (l_Sourcing_Rule_rec);
202
203 ELSIF l_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_CREATE THEN
204
205 l_Sourcing_Rule_rec.creation_date := SYSDATE;
206 l_Sourcing_Rule_rec.created_by := FND_GLOBAL.USER_ID;
207
208 MRP_Sourcing_Rule_Handlers.Insert_Row (l_Sourcing_Rule_rec);
209
210 END IF;
211
212 END IF;
213
214 END IF;
215
216 -- Load OUT parameters
217
218 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
219 x_old_Sourcing_Rule_rec := l_old_Sourcing_Rule_rec;
220
221 EXCEPTION
222
223 WHEN FND_API.G_EXC_ERROR THEN
224
225 l_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_ERROR;
226 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
227 x_old_Sourcing_Rule_rec := l_old_Sourcing_Rule_rec;
228 RAISE;
229
230 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
231
232 l_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
234 x_old_Sourcing_Rule_rec := l_old_Sourcing_Rule_rec;
235
236 RAISE;
237
238 WHEN OTHERS THEN
239
240 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
241 THEN
242 FND_MSG_PUB.Add_Exc_Msg
243 ( G_PKG_NAME
244 , 'Sourcing_Rule'
245 );
246 END IF;
247
248 l_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
250 x_old_Sourcing_Rule_rec := l_old_Sourcing_Rule_rec;
251
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253
254 END Sourcing_Rule;
255
256 -- Receiving_Orgs
257
258 PROCEDURE Receiving_Orgs
259 ( p_validation_level IN NUMBER
260 , p_control_rec IN MRP_GLOBALS.Control_Rec_Type
261 , p_Receiving_Org_tbl IN MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
262 , p_old_Receiving_Org_tbl IN MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
263 , x_Receiving_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
264 , x_old_Receiving_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
265 )
266 IS
267 l_return_status VARCHAR2(1);
268 l_control_rec MRP_GLOBALS.Control_Rec_Type;
269 l_Receiving_Org_rec MRP_Sourcing_Rule_PUB.Receiving_Org_Rec_Type;
270 l_Receiving_Org_out_rec MRP_Sourcing_Rule_PUB.Receiving_Org_Rec_Type; -- Nocopy Change
271 l_Receiving_Org_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type;
272 l_old_Receiving_Org_rec MRP_Sourcing_Rule_PUB.Receiving_Org_Rec_Type;
273 l_old_Receiving_Org_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type;
274 BEGIN
275
276 -- Init local table variables.
277
278 l_Receiving_Org_tbl := p_Receiving_Org_tbl;
279 l_old_Receiving_Org_tbl := p_old_Receiving_Org_tbl;
280
281 FOR I IN 1..l_Receiving_Org_tbl.COUNT LOOP
282 BEGIN
283
284 -- Load local records.
285
286 l_Receiving_Org_rec := l_Receiving_Org_tbl(I);
287
288 IF l_old_Receiving_Org_tbl.EXISTS(I) THEN
289 l_old_Receiving_Org_rec := l_old_Receiving_Org_tbl(I);
290 ELSE
291 l_old_Receiving_Org_rec := MRP_Sourcing_Rule_PUB.G_MISS_RECEIVING_ORG_REC;
292 END IF;
293
294 -- Load API control record
295
296 l_control_rec := MRP_GLOBALS.Init_Control_Rec
297 ( p_operation => l_Receiving_Org_rec.operation
298 , p_control_rec => p_control_rec
299 );
300
301 -- Set record return status.
302
303 l_Receiving_Org_rec.return_status := FND_API.G_RET_STS_SUCCESS;
304
305 -- Prepare record.
306
307 IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_CREATE THEN
308
309 l_Receiving_Org_rec.db_flag := FND_API.G_FALSE;
310
311 -- Set missing old record elements to NULL.
312
313 l_old_Receiving_Org_rec :=
314 MRP_Receiving_Org_Util.Convert_Miss_To_Null (l_old_Receiving_Org_rec);
315
316 ELSIF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_UPDATE
317 OR l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_DELETE
318 THEN
319
320 l_Receiving_Org_rec.db_flag := FND_API.G_TRUE;
321
322 -- Query Old if missing
323
324 IF l_old_Receiving_Org_rec.Sr_Receipt_Id = FND_API.G_MISS_NUM
325 THEN
326
327 l_old_Receiving_Org_rec := MRP_Receiving_Org_Handlers.Query_Row
328 ( p_Sr_Receipt_Id => l_Receiving_Org_rec.Sr_Receipt_Id
329 );
330
331 ELSE
332
333 -- Set missing old record elements to NULL.
334
335 l_old_Receiving_Org_rec :=
336 MRP_Receiving_Org_Util.Convert_Miss_To_Null (l_old_Receiving_Org_rec);
337
338 END IF;
339
340 -- Complete new record from old
341
342 l_Receiving_Org_rec := MRP_Receiving_Org_Util.Complete_Record
343 ( p_Receiving_Org_rec => l_Receiving_Org_rec
344 , p_old_Receiving_Org_rec => l_old_Receiving_Org_rec
345 );
346
347 END IF;
348
349 -- Attribute level validation.
350
351 IF l_control_rec.default_attributes
352 OR l_control_rec.change_attributes
353 THEN
354
355 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
356
357 MRP_Validate_Receiving_Org.Attributes
358 ( x_return_status => l_return_status
359 , p_Receiving_Org_rec => l_Receiving_Org_rec
360 , p_old_Receiving_Org_rec => l_old_Receiving_Org_rec
361 );
362
363 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 END IF;
370
371 END IF;
372
373 -- Clear dependent attributes.
374
375 IF l_control_rec.change_attributes THEN
376
377 MRP_Receiving_Org_Util.Clear_Dependent_Attr
378 ( p_Receiving_Org_rec => l_Receiving_Org_rec
379 , p_old_Receiving_Org_rec => l_old_Receiving_Org_rec
380 , x_Receiving_Org_rec => l_Receiving_Org_out_rec -- Nocopy Change
381 );
382 l_Receiving_Org_rec := l_Receiving_Org_out_rec ; -- Nocopy Change
383
384 END IF;
385
386 -- Default missing attributes
387
388 IF l_control_rec.default_attributes
389 OR l_control_rec.change_attributes
390 THEN
391
392 MRP_Default_Receiving_Org.Attributes
393 ( p_Receiving_Org_rec => l_Receiving_Org_rec
394 , x_Receiving_Org_rec => l_Receiving_Org_out_rec -- Nocopy Change
395 );
396 l_Receiving_Org_rec := l_Receiving_Org_out_rec ; -- Nocopy Change
397
398 END IF;
399
400 -- Apply attribute changes
401
402 IF l_control_rec.default_attributes
403 OR l_control_rec.change_attributes
404 THEN
405
406 MRP_Receiving_Org_Util.Apply_Attribute_Changes
407 ( p_Receiving_Org_rec => l_Receiving_Org_rec
408 , p_old_Receiving_Org_rec => l_old_Receiving_Org_rec
409 , x_Receiving_Org_rec => l_Receiving_Org_out_rec -- Nocopy Change
410 );
411 l_Receiving_Org_rec := l_Receiving_Org_out_rec ; -- Nocopy Change
412
413 END IF;
414
415 -- Entity level validation.
416
417 IF l_control_rec.validate_entity THEN
418
419 IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
420
421 MRP_Validate_Receiving_Org.Entity_Delete
422 ( x_return_status => l_return_status
423 , p_Receiving_Org_rec => l_Receiving_Org_rec
424 );
425
426 ELSE
427
428 MRP_Validate_Receiving_Org.Entity
429 ( x_return_status => l_return_status
430 , p_Receiving_Org_rec => l_Receiving_Org_rec
431 , p_old_Receiving_Org_rec => l_old_Receiving_Org_rec
432 );
433
434 END IF;
435
436 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
439 RAISE FND_API.G_EXC_ERROR;
440 END IF;
441
442 END IF;
443
444 -- Step 4. Write to DB
445
446 IF l_control_rec.write_to_db THEN
447
448 IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
449
450 MRP_Receiving_Org_Handlers.Delete_Row
451 ( p_Sr_Receipt_Id => l_Receiving_Org_rec.Sr_Receipt_Id
452 );
453
454 ELSE
455
456 -- Get Who Information
457
458 l_Receiving_Org_rec.last_update_date := SYSDATE;
459 l_Receiving_Org_rec.last_updated_by := FND_GLOBAL.USER_ID;
460 l_Receiving_Org_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
461
462 IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_UPDATE THEN
463
464 MRP_Receiving_Org_Handlers.Update_Row (l_Receiving_Org_rec);
465
466 ELSIF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_CREATE THEN
467
468 l_Receiving_Org_rec.creation_date := SYSDATE;
469 l_Receiving_Org_rec.created_by := FND_GLOBAL.USER_ID;
470
471 -- dbms_output.put_line ('Inserting Rec Org');
472 MRP_Receiving_Org_Handlers.Insert_Row (l_Receiving_Org_rec);
473
474 END IF;
475
476 END IF;
477
478 END IF;
479
480 -- Load tables.
481
482 l_Receiving_Org_tbl(I) := l_Receiving_Org_rec;
483 l_old_Receiving_Org_tbl(I) := l_old_Receiving_Org_rec;
484
485 -- For loop exception handler.
486
487
488 EXCEPTION
489
490 WHEN FND_API.G_EXC_ERROR THEN
491
492 l_Receiving_Org_rec.return_status := FND_API.G_RET_STS_ERROR;
493 l_Receiving_Org_tbl(I) := l_Receiving_Org_rec;
494 l_old_Receiving_Org_tbl(I) := l_old_Receiving_Org_rec;
495
496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
497
498 l_Receiving_Org_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
499 l_Receiving_Org_tbl(I) := l_Receiving_Org_rec;
500 l_old_Receiving_Org_tbl(I) := l_old_Receiving_Org_rec;
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502
503 WHEN OTHERS THEN
504
505 l_Receiving_Org_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 l_Receiving_Org_tbl(I) := l_Receiving_Org_rec;
507 l_old_Receiving_Org_tbl(I) := l_old_Receiving_Org_rec;
508
509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
510 THEN
511 FND_MSG_PUB.Add_Exc_Msg
512 ( G_PKG_NAME
513 , 'Receiving_Orgs'
514 );
515 END IF;
516
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518
519 END;
520 END LOOP;
521
522 -- Load OUT parameters
523
524 x_Receiving_Org_tbl := l_Receiving_Org_tbl;
525 x_old_Receiving_Org_tbl := l_old_Receiving_Org_tbl;
526
527 EXCEPTION
528
529 WHEN FND_API.G_EXC_ERROR THEN
530
531 RAISE;
532
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534
535
536 RAISE;
537
538 WHEN OTHERS THEN
539
540 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
541 THEN
542 FND_MSG_PUB.Add_Exc_Msg
543 ( G_PKG_NAME
544 , 'Receiving_Orgs'
545 );
546 END IF;
547
548
549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550
551 END Receiving_Orgs;
552
553 -- Shipping_Orgs
554
555 PROCEDURE Shipping_Orgs
556 ( p_validation_level IN NUMBER
557 , p_control_rec IN MRP_GLOBALS.Control_Rec_Type
558 , p_Shipping_Org_tbl IN MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
559 , p_old_Shipping_Org_tbl IN MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
560 , x_Shipping_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
561 , x_old_Shipping_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
562 )
563 IS
564 l_return_status VARCHAR2(1);
565 l_control_rec MRP_GLOBALS.Control_Rec_Type;
566 l_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
567 l_Shipping_Org_out_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type; -- Nocopy Change
568 l_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
569 l_old_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
570 l_old_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
571 BEGIN
572
573 -- Init local table variables.
574
575 l_Shipping_Org_tbl := p_Shipping_Org_tbl;
576 l_old_Shipping_Org_tbl := p_old_Shipping_Org_tbl;
577
578 FOR I IN 1..l_Shipping_Org_tbl.COUNT LOOP
579 BEGIN
580
581 -- Load local records.
582
583 l_Shipping_Org_rec := l_Shipping_Org_tbl(I);
584
585 IF l_old_Shipping_Org_tbl.EXISTS(I) THEN
586 l_old_Shipping_Org_rec := l_old_Shipping_Org_tbl(I);
587 ELSE
588 l_old_Shipping_Org_rec := MRP_Sourcing_Rule_PUB.G_MISS_SHIPPING_ORG_REC;
589 END IF;
590
591 -- Load API control record
592
593 l_control_rec := MRP_GLOBALS.Init_Control_Rec
594 ( p_operation => l_Shipping_Org_rec.operation
595 , p_control_rec => p_control_rec
596 );
597
598 -- Set record return status.
599
600 l_Shipping_Org_rec.return_status := FND_API.G_RET_STS_SUCCESS;
601
602 -- Prepare record.
603
604 IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_CREATE THEN
605
606 l_Shipping_Org_rec.db_flag := FND_API.G_FALSE;
607
608 -- Set missing old record elements to NULL.
609
610 l_old_Shipping_Org_rec :=
611 MRP_Shipping_Org_Util.Convert_Miss_To_Null (l_old_Shipping_Org_rec);
612
613 ELSIF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_UPDATE
614 OR l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_DELETE
615 THEN
616
617 l_Shipping_Org_rec.db_flag := FND_API.G_TRUE;
618
619 -- Query Old if missing
620
621 IF l_old_Shipping_Org_rec.Sr_Source_Id = FND_API.G_MISS_NUM
622 THEN
623
624 l_old_Shipping_Org_rec := MRP_Shipping_Org_Handlers.Query_Row
625 ( p_Sr_Source_Id => l_Shipping_Org_rec.Sr_Source_Id
626 );
627
628 ELSE
629
630 -- Set missing old record elements to NULL.
631
632 l_old_Shipping_Org_rec :=
633 MRP_Shipping_Org_Util.Convert_Miss_To_Null (l_old_Shipping_Org_rec);
634
635 END IF;
636
637 -- Complete new record from old
638
639 l_Shipping_Org_rec := MRP_Shipping_Org_Util.Complete_Record
640 ( p_Shipping_Org_rec => l_Shipping_Org_rec
641 , p_old_Shipping_Org_rec => l_old_Shipping_Org_rec
642 );
643
644 END IF;
645
646 -- Attribute level validation.
647
648 IF l_control_rec.default_attributes
649 OR l_control_rec.change_attributes
650 THEN
651
652 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
653
654 MRP_Validate_Shipping_Org.Attributes
655 ( x_return_status => l_return_status
656 , p_Shipping_Org_rec => l_Shipping_Org_rec
657 , p_old_Shipping_Org_rec => l_old_Shipping_Org_rec
658 );
659
660 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
662 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666 END IF;
667
668 END IF;
669
670 -- Clear dependent attributes.
671
672 IF l_control_rec.change_attributes THEN
673
674 MRP_Shipping_Org_Util.Clear_Dependent_Attr
675 ( p_Shipping_Org_rec => l_Shipping_Org_rec
676 , p_old_Shipping_Org_rec => l_old_Shipping_Org_rec
677 , x_Shipping_Org_rec => l_Shipping_Org_out_rec -- Nocopy Change
678 );
679 l_Shipping_Org_rec := l_Shipping_Org_out_rec; -- Nocopy Change
680
681 END IF;
682
683 -- Default missing attributes
684
685 IF l_control_rec.default_attributes
686 OR l_control_rec.change_attributes
687 THEN
688
689 MRP_Default_Shipping_Org.Attributes
690 ( p_Shipping_Org_rec => l_Shipping_Org_rec
691 , x_Shipping_Org_rec => l_Shipping_Org_out_rec -- Nocopy Change
692 );
693 l_Shipping_Org_rec := l_Shipping_Org_out_rec; -- Nocopy Change
694
695 END IF;
696
697 -- Apply attribute changes
698
699 IF l_control_rec.default_attributes
700 OR l_control_rec.change_attributes
701 THEN
702
703 MRP_Shipping_Org_Util.Apply_Attribute_Changes
704 ( p_Shipping_Org_rec => l_Shipping_Org_rec
705 , p_old_Shipping_Org_rec => l_old_Shipping_Org_rec
706 , x_Shipping_Org_rec => l_Shipping_Org_out_rec -- Nocopy Change
707 );
708 l_Shipping_Org_rec := l_Shipping_Org_out_rec; -- Nocopy Change
709
710 END IF;
711
712 -- Entity level validation.
713
714 IF l_control_rec.validate_entity THEN
715
716 IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
717
718 MRP_Validate_Shipping_Org.Entity_Delete
719 ( x_return_status => l_return_status
720 , p_Shipping_Org_rec => l_Shipping_Org_rec
721 );
722
723 ELSE
724
725 MRP_Validate_Shipping_Org.Entity
726 ( x_return_status => l_return_status
727 , p_Shipping_Org_rec => l_Shipping_Org_rec
728 , p_old_Shipping_Org_rec => l_old_Shipping_Org_rec
729 );
730
731 END IF;
732
733 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738
739 END IF;
740
741 -- Step 4. Write to DB
742
743 IF l_control_rec.write_to_db THEN
744
745 IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_DELETE THEN
746
747 MRP_Shipping_Org_Handlers.Delete_Row
748 ( p_Sr_Source_Id => l_Shipping_Org_rec.Sr_Source_Id
749 );
750
751 ELSE
752
753 -- Get Who Information
754
755 l_Shipping_Org_rec.last_update_date := SYSDATE;
756 l_Shipping_Org_rec.last_updated_by := FND_GLOBAL.USER_ID;
757 l_Shipping_Org_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
758
759 IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_UPDATE THEN
760
761 MRP_Shipping_Org_Handlers.Update_Row (l_Shipping_Org_rec);
762
763 ELSIF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_CREATE THEN
764
765 l_Shipping_Org_rec.creation_date := SYSDATE;
766 l_Shipping_Org_rec.created_by := FND_GLOBAL.USER_ID;
767
768 MRP_Shipping_Org_Handlers.Insert_Row (l_Shipping_Org_rec);
769
770 END IF;
771
772 END IF;
773
774 END IF;
775
776 -- Load tables.
777
778 l_Shipping_Org_tbl(I) := l_Shipping_Org_rec;
779 l_old_Shipping_Org_tbl(I) := l_old_Shipping_Org_rec;
780
781 -- For loop exception handler.
782
783
784 EXCEPTION
785
786 WHEN FND_API.G_EXC_ERROR THEN
787
788 l_Shipping_Org_rec.return_status := FND_API.G_RET_STS_ERROR;
789 l_Shipping_Org_tbl(I) := l_Shipping_Org_rec;
790 l_old_Shipping_Org_tbl(I) := l_old_Shipping_Org_rec;
791
792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
793
794 l_Shipping_Org_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 l_Shipping_Org_tbl(I) := l_Shipping_Org_rec;
796 l_old_Shipping_Org_tbl(I) := l_old_Shipping_Org_rec;
797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
798
799 WHEN OTHERS THEN
800
801 l_Shipping_Org_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
802 l_Shipping_Org_tbl(I) := l_Shipping_Org_rec;
803 l_old_Shipping_Org_tbl(I) := l_old_Shipping_Org_rec;
804
805 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
806 THEN
807 FND_MSG_PUB.Add_Exc_Msg
808 ( G_PKG_NAME
809 , 'Shipping_Orgs'
810 );
811 END IF;
812
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
814
815 END;
816 END LOOP;
817
818 -- Load OUT parameters
819
820 x_Shipping_Org_tbl := l_Shipping_Org_tbl;
821 x_old_Shipping_Org_tbl := l_old_Shipping_Org_tbl;
822
823 EXCEPTION
824
825 WHEN FND_API.G_EXC_ERROR THEN
826
827 RAISE;
828
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830
831
832 RAISE;
833
834 WHEN OTHERS THEN
835
836 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
837 THEN
838 FND_MSG_PUB.Add_Exc_Msg
839 ( G_PKG_NAME
840 , 'Shipping_Orgs'
841 );
842 END IF;
843
844
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846
847 END Shipping_Orgs;
848
849 -- Start of Comments
850 -- API name Process_Sourcing_Rule
851 -- Type Private
852 -- Function
853 --
854 -- Pre-reqs
855 --
856 -- Parameters
857 --
858 -- Version Current version = 1.0
859 -- Initial version = 1.0
860 --
861 -- Notes
862 --
863 -- End of Comments
864
865 PROCEDURE Process_Sourcing_Rule
866 ( p_api_version_number IN NUMBER
867 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
868 , p_commit IN VARCHAR2 := FND_API.G_FALSE
869 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
870 , p_control_rec IN MRP_GLOBALS.Control_Rec_Type :=
871 MRP_GLOBALS.G_MISS_CONTROL_REC
872 , x_return_status OUT NOCOPY VARCHAR2
873 , x_msg_count OUT NOCOPY NUMBER
874 , x_msg_data OUT NOCOPY VARCHAR2
875 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type :=
876 MRP_Sourcing_Rule_PUB.G_MISS_SOURCING_RULE_REC
877 , p_old_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type :=
878 MRP_Sourcing_Rule_PUB.G_MISS_SOURCING_RULE_REC
879 , p_Receiving_Org_tbl IN MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type :=
880 MRP_Sourcing_Rule_PUB.G_MISS_RECEIVING_ORG_TBL
881 , p_old_Receiving_Org_tbl IN MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type :=
882 MRP_Sourcing_Rule_PUB.G_MISS_RECEIVING_ORG_TBL
883 , p_Shipping_Org_tbl IN MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type :=
884 MRP_Sourcing_Rule_PUB.G_MISS_SHIPPING_ORG_TBL
885 , p_old_Shipping_Org_tbl IN MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type :=
886 MRP_Sourcing_Rule_PUB.G_MISS_SHIPPING_ORG_TBL
887 , x_Sourcing_Rule_rec OUT NOCOPY MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
888 , x_Receiving_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
889 , x_Shipping_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
890 )
891 IS
892 l_api_version_number CONSTANT NUMBER := 1.0;
893 l_api_name CONSTANT VARCHAR2(30):= 'Process_Sourcing_Rule';
894 l_return_status VARCHAR2(1);
895 l_control_rec MRP_GLOBALS.Control_Rec_Type;
896 l_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type := p_Sourcing_Rule_rec;
897 l_Sourcing_Rule_out_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type ; -- Nocopy Change
898 l_old_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type := p_old_Sourcing_Rule_rec;
899 l_old_Sourcing_Rule_out_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type ; -- Nocopy Change
900 l_Receiving_Org_rec MRP_Sourcing_Rule_PUB.Receiving_Org_Rec_Type;
901 l_Receiving_Org_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type;
902 l_Receiving_Org_out_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type; -- Nocopy Change
903 l_old_Receiving_Org_rec MRP_Sourcing_Rule_PUB.Receiving_Org_Rec_Type;
904 l_old_Receiving_Org_out_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type; -- Nocopy Change
905 l_old_Receiving_Org_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type;
906 l_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
907 l_Shipping_Org_out_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type; --Nocopy Change
908 l_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
909 l_old_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
910 l_old_Shipping_Org_out_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type; -- Nocopy Change
911 l_old_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
912
913 l_tot_alloc_percent NUMBER;
914 l_curr_rco_index NUMBER;
915 l_curr_rank NUMBER;
916 l_count NUMBER;
917 l_organization_id NUMBER := FND_API.G_MISS_NUM;
918 org_exists NUMBER;
919
920 BEGIN
921
922 --dbms_output.put_line ('Oper : ' || l_Sourcing_Rule_rec.operation);
923 -- Standard call to check for call compatibility
924
925 IF NOT FND_API.Compatible_API_Call
926 ( l_api_version_number
927 , p_api_version_number
928 , l_api_name
929 , G_PKG_NAME
930 )
931 THEN
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934
935 -- Set Save point.
936 SAVEPOINT Process_Sourcing_Rule_PVT;
937
938 -- Initialize message list.
939
940 IF FND_API.to_Boolean(p_init_msg_list) THEN
941 FND_MSG_PUB.initialize;
942 END IF;
943
944 -- Init local table variables.
945
946 l_Receiving_Org_tbl := p_Receiving_Org_tbl;
947 l_old_Receiving_Org_tbl := p_old_Receiving_Org_tbl;
948
949 -- Init local table variables.
950
951 l_Shipping_Org_tbl := p_Shipping_Org_tbl;
952 l_old_Shipping_Org_tbl := p_old_Shipping_Org_tbl;
953
954 -- Sourcing_Rule
955
956 Sourcing_Rule
957 ( p_validation_level => p_validation_level
958 , p_control_rec => p_control_rec
959 , p_Sourcing_Rule_rec => l_Sourcing_Rule_rec
960 , p_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_rec
961 , x_Sourcing_Rule_rec => l_Sourcing_Rule_out_rec -- Nocopy Change
962 , x_old_Sourcing_Rule_rec => l_old_Sourcing_Rule_out_rec -- Nocopy Change
963 );
964 l_Sourcing_Rule_rec := l_Sourcing_Rule_out_rec; -- Nocopy Change
965 l_old_Sourcing_Rule_rec := l_old_Sourcing_Rule_out_rec; -- Nocopy Change
966
967 -- Perform Sourcing_Rule group requests.
968
969 IF p_control_rec.process AND
970 (p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_ALL OR
971 p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_SOURCING_RULE)
972 THEN
973
974 NULL;
975
976 END IF;
977
978 -- Load parent key if missing and operation is create.
979
980 FOR I IN 1..l_Receiving_Org_tbl.COUNT LOOP
981
982 l_Receiving_Org_rec := l_Receiving_Org_tbl(I);
983
984 IF l_Receiving_Org_rec.operation = MRP_Globals.G_OPR_CREATE
985 AND (l_Receiving_Org_rec.Sourcing_Rule_Id IS NULL OR
986 l_Receiving_Org_rec.Sourcing_Rule_Id = FND_API.G_MISS_NUM)
987 THEN
988
989 -- Copy parent_id.
990 -- dbms_output.put_line ('Parent SR id is : ' || to_char (l_Sourcing_Rule_rec.Sourcing_Rule_Id));
991
992 l_Receiving_Org_tbl(I).Sourcing_Rule_Id := l_Sourcing_Rule_rec.Sourcing_Rule_Id;
993 END IF;
994 END LOOP;
995
996 -- Receiving_Orgs
997
998 Receiving_Orgs
999 ( p_validation_level => p_validation_level
1000 , p_control_rec => p_control_rec
1001 , p_Receiving_Org_tbl => l_Receiving_Org_tbl
1002 , p_old_Receiving_Org_tbl => l_old_Receiving_Org_tbl
1003 , x_Receiving_Org_tbl => l_Receiving_Org_out_tbl -- Nocopy Change
1004 , x_old_Receiving_Org_tbl => l_old_Receiving_Org_out_tbl -- Nocopy Change
1005 );
1006 -- Nocopy Change
1007 l_Receiving_Org_tbl := l_Receiving_Org_out_tbl ;
1008 l_old_Receiving_Org_tbl := l_old_Receiving_Org_out_tbl;
1009
1010 -- Perform Receiving_Org group requests.
1011
1012 IF p_control_rec.process AND
1013 (p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_ALL OR
1014 p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_RECEIVING_ORG) AND
1015 l_sourcing_rule_rec.operation <> MRP_GLOBALS.G_OPR_DELETE
1016 THEN
1017
1018 FOR I IN 1..l_Receiving_Org_tbl.COUNT LOOP
1019
1020 l_Receiving_Org_rec := l_Receiving_Org_tbl(I);
1021
1022 /** Bug 2257098 : Put a check that for Sourcing rules, one cannot
1023 pass a receiving organization that is different from the
1024 Organization for which the sourcing rule is defined.
1025 **/
1026
1027 IF (l_sourcing_rule_rec.sourcing_rule_type = 1 AND
1028 NVL(l_Receiving_Org_rec.receipt_organization_id,-23453) <>
1029 NVL(l_sourcing_rule_rec.organization_id,-23453)) THEN
1030
1031 -- dbms_output.put_line ('Invalid Receiving Organization');
1032 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1033 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Receiving_Organization');
1034 FND_MESSAGE.SET_TOKEN('DETAILS', 'Mismatch between SR Organization and Receiving Organization');
1035 FND_MSG_PUB.Add;
1036 l_Receiving_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1037
1038 END IF;
1039
1040 -- The sourcing rule should not have receiving orgs with
1041 -- overlapping effectivity dates
1042
1043 SELECT count(*)
1044 INTO l_count
1045 FROM MRP_SR_RECEIPT_ORG RO1,
1046 MRP_SR_RECEIPT_ORG RO2
1047 WHERE RO1.sourcing_rule_id =
1048 l_sourcing_rule_rec.sourcing_rule_id
1049 AND RO1.sr_receipt_id = l_Receiving_Org_rec.sr_receipt_id
1050 AND RO2.sourcing_rule_id = RO1.sourcing_rule_id
1051 AND RO2.sr_receipt_id <> RO1.sr_receipt_id
1052 /** Bug 2257098
1053 AND RO1.EFFECTIVE_DATE >= RO2.EFFECTIVE_DATE
1054 AND RO1.EFFECTIVE_DATE <
1055 NVL(RO2.DISABLE_DATE, RO1.EFFECTIVE_DATE + 1);
1056 **/
1057 AND NVL(RO2.receipt_organization_id,-23453) = NVL(RO1.receipt_organization_id,-23453)
1058 AND ((RO1.EFFECTIVE_DATE = RO2.EFFECTIVE_DATE)
1059 OR
1060 (RO1.EFFECTIVE_DATE > RO2.EFFECTIVE_DATE
1061 AND RO1.EFFECTIVE_DATE <= NVL(RO2.DISABLE_DATE, RO1.EFFECTIVE_DATE))
1062 OR
1063 (RO1.EFFECTIVE_DATE < RO2.EFFECTIVE_DATE
1064 AND NVL(RO1.DISABLE_DATE,RO2.EFFECTIVE_DATE) >= RO2.EFFECTIVE_DATE));
1065
1066 IF l_count > 0 THEN
1067 -- dbms_output.put_line ('Overlapping effective dates');
1068 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1069 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Effective_Date');
1070 FND_MESSAGE.SET_TOKEN('DETAILS', 'Overlapping Effective ' ||
1071 'Dates not allowed for Receiving Organizations');
1072 FND_MSG_PUB.Add;
1073 l_Receiving_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1074 END IF;
1075
1076 END LOOP;
1077
1078 END IF;
1079
1080 -- Load parent key if missing and operation is create.
1081
1082 FOR I IN 1..l_Shipping_Org_tbl.COUNT LOOP
1083
1084 l_Shipping_Org_rec := l_Shipping_Org_tbl(I);
1085
1086 IF l_Shipping_Org_rec.operation = MRP_Globals.G_OPR_CREATE
1087 AND (l_Shipping_Org_rec.Sr_Receipt_Id IS NULL OR
1088 l_Shipping_Org_rec.Sr_Receipt_Id = FND_API.G_MISS_NUM)
1089 THEN
1090
1091 -- Check If parent exists.
1092
1093 IF l_Receiving_Org_tbl.EXISTS(l_Shipping_Org_rec.Receiving_Org_index) THEN
1094
1095 -- Copy parent_id.
1096
1097 l_Shipping_Org_tbl(I).Sr_Receipt_Id := l_Receiving_Org_tbl(l_Shipping_Org_rec.Receiving_Org_index).Sr_Receipt_Id;
1098
1099 ELSE
1100
1101 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1102 THEN
1103
1104 FND_MESSAGE.SET_NAME('MRP','MRP_API_INV_PARENT_INDEX');
1105 FND_MESSAGE.SET_TOKEN('ENTITY_NAME','Shipping_Org');
1106 FND_MESSAGE.SET_TOKEN('ENTITY_INDEX',I);
1107 FND_MESSAGE.SET_TOKEN('PARENT_INDEX',l_Shipping_Org_rec.Receiving_Org_index);
1108 FND_MSG_PUB.Add;
1109
1110 END IF;
1111 END IF;
1112 END IF;
1113 END LOOP;
1114
1115 -- Shipping_Orgs
1116
1117 Shipping_Orgs
1118 ( p_validation_level => p_validation_level
1119 , p_control_rec => p_control_rec
1120 , p_Shipping_Org_tbl => l_Shipping_Org_tbl
1121 , p_old_Shipping_Org_tbl => l_old_Shipping_Org_tbl
1122 , x_Shipping_Org_tbl => l_Shipping_Org_out_tbl -- Nocopy Change
1123 , x_old_Shipping_Org_tbl => l_old_Shipping_Org_out_tbl -- Nocopy Change
1124 );
1125
1126 -- Nocopy Change
1127 l_Shipping_Org_tbl := l_Shipping_Org_out_tbl ;
1128 l_old_Shipping_Org_tbl := l_old_Shipping_Org_out_tbl;
1129
1130 -- Perform Shipping_Org group requests.
1131
1132 /** Bug 2263575
1133 1. Commented out nocopy the check that there cannot be two sources
1134 with the same rank since in 11i this is not true.
1135 2. When setting the planning_active flag to 2 based on the total
1136 allocation % (should be 100 for a plan to be active) added
1137 a check on rank too.
1138 3. Wherever we are checking l_Shipping_Org_rec.source_organization_id
1139 wrt l_sourcing_rule_rec.organization_id,
1140 replaced l_sourcing_rule_rec.organization_id with
1141 l_Receiving_Org_rec.receipt_organization_id since for BOD, the
1142 l_sourcing_rule_rec.organization_id and
1143 l_Receiving_Org_rec.receipt_organization_id may be different.
1144 4. For a local SR/BOD, if the source_type = 1(Transfer From) put a check
1145 that there should be a shipping network defined between the shipping
1146 org and receiving org.
1147 5. Put a check that if the SR is global you cannot have source_type
1148 = 2 (Make At)
1149 **/
1150
1151 IF p_control_rec.process AND
1152 (p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_ALL OR
1153 p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_SHIPPING_ORG) AND
1154 l_sourcing_rule_rec.operation <> MRP_GLOBALS.G_OPR_DELETE
1155 THEN
1156
1157 l_tot_alloc_percent := 0;
1158 l_curr_rco_index := 0;
1159 l_curr_rank := -23453;
1160
1161 FOR I IN 1..l_Shipping_Org_tbl.COUNT LOOP
1162
1163 IF ((l_curr_rco_index <>
1164 l_Shipping_Org_tbl(I).receiving_org_index)
1165 OR
1166 (l_curr_rank <> l_Shipping_Org_tbl(I).rank)) /* Bug 2263575 */
1167 THEN
1168
1169 IF l_curr_rco_index <> 0 AND l_tot_alloc_percent < 100 THEN
1170 UPDATE mrp_sourcing_rules
1171 SET planning_active = 2
1172 WHERE sourcing_rule_id =
1173 l_sourcing_rule_rec.sourcing_rule_id;
1174 END IF;
1175
1176 l_tot_alloc_percent := 0;
1177 END IF;
1178
1179 l_Shipping_Org_rec := l_Shipping_Org_tbl(I);
1180 l_Receiving_Org_rec := l_Receiving_Org_tbl(l_Shipping_Org_rec.Receiving_Org_index); /* Bug 2263575 */
1181 l_curr_rco_index := l_Shipping_Org_rec.receiving_org_index;
1182 l_curr_rank := l_Shipping_Org_rec.rank; /* Bug 2263575 */
1183
1184 IF l_Shipping_Org_rec.source_type = 1 AND
1185 l_Receiving_Org_rec.receipt_organization_id IS NOT NULL /* Bug 2263575 */
1186 THEN
1187 IF /** Bug 2263575 l_sourcing_rule_rec.organization_id = **/
1188 l_Receiving_Org_rec.receipt_organization_id =
1189 l_Shipping_Org_rec.source_organization_id THEN
1190 -- dbms_output.put_line ('Source Org should be different');
1191 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1192 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Organization_Id');
1193 FND_MESSAGE.SET_TOKEN('DETAILS', 'Source Organization ' ||
1194 'should be different from Receiving Organization');
1195 FND_MSG_PUB.Add;
1196 l_Shipping_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1197
1198 ELSE /* Bug 2263575 */
1199 BEGIN
1200 SELECT 1 INTO org_exists
1201 FROM mtl_interorg_parameters
1202 WHERE to_organization_id = l_Receiving_Org_rec.receipt_organization_id
1203 AND from_organization_id = l_Shipping_Org_rec.source_organization_id;
1204 EXCEPTION WHEN NO_DATA_FOUND THEN
1205 -- dbms_output.put_line ('Shipping Network not defined between source org and receiving org');
1206 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1207 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Organization_Id');
1208 FND_MESSAGE.SET_TOKEN('DETAILS', 'Shipping Network ' ||
1209 'not defined between source org and receiving org');
1210 FND_MSG_PUB.Add;
1211 l_Shipping_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1212 END;
1213 END IF;
1214 END IF;
1215
1216 IF l_Shipping_Org_rec.source_type = 2 AND /* Bug 2263575 */
1217 l_sourcing_rule_rec.sourcing_rule_type = 1 AND /* Bug 5238229 */
1218 l_sourcing_rule_rec.organization_id IS NULL THEN
1219 -- dbms_output.put_line ('Cannot have source type of Make At for Global SR');
1220 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1221 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Type');
1222 FND_MESSAGE.SET_TOKEN('DETAILS', 'Cannot have source type of Make At for Global SR');
1223 FND_MSG_PUB.Add;
1224 l_Shipping_Org_tbl(I).return_status :=
1225 FND_API.G_RET_STS_ERROR;
1226 END IF;
1227
1228 IF l_Shipping_Org_rec.source_type = 2 AND
1229 /** Bug 2263575 l_sourcing_rule_rec.organization_id IS NOT NULL **/
1230 l_Receiving_Org_rec.receipt_organization_id IS NOT NULL
1231 THEN
1232 IF /** Bug 2263575 l_sourcing_rule_rec.organization_id <> **/
1233 l_Receiving_Org_rec.receipt_organization_id <>
1234 l_Shipping_Org_rec.source_organization_id THEN
1235 -- dbms_output.put_line ('Source Org should be same');
1236 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1237 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Organization_Id');
1238 FND_MESSAGE.SET_TOKEN('DETAILS', 'Source Organization ' ||
1239 'should be same as Receiving Organization');
1240 FND_MSG_PUB.Add;
1241 l_Shipping_Org_tbl(I).return_status :=
1242 FND_API.G_RET_STS_ERROR;
1243 END IF;
1244 END IF;
1245
1246 IF l_Shipping_Org_rec.source_type = 3 AND
1247 l_Shipping_Org_rec.source_organization_id IS NOT NULL THEN
1248 -- dbms_output.put_line ('Source Org should be NULL');
1249 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1250 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source_Organization_Id');
1251 FND_MESSAGE.SET_TOKEN('DETAILS',
1252 'Source Organization should be NULL');
1253 FND_MSG_PUB.Add;
1254 l_Shipping_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1255 END IF;
1256
1257 -- We cannot have two shipping orgs with the same rank
1258
1259 /**** Bug 2263575
1260 SELECT count(*)
1261 INTO l_count
1262 FROM MRP_SR_SOURCE_ORG SO1,
1263 MRP_SR_SOURCE_ORG SO2
1264 WHERE SO1.sr_receipt_id =
1265 l_Shipping_Org_rec.sr_receipt_id
1266 AND SO1.sr_source_id = l_Shipping_Org_rec.sr_source_id
1267 AND SO2.sr_receipt_id = SO1.sr_receipt_id
1268 AND SO2.sr_source_id <> SO1.sr_source_id
1269 AND NVL(SO2.rank, -999) = NVL(SO1.rank,-9999);
1270
1271 IF l_count > 0 THEN
1272 -- dbms_output.put_line ('Cannot have Duplicate Ranks on SHO s');
1273 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1274 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Rank');
1275 FND_MESSAGE.SET_TOKEN('DETAILS',
1276 'Cannot have Duplicate Ranks on Shipping Organizations');
1277 FND_MSG_PUB.Add;
1278 l_Shipping_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1279 END IF;
1280
1281 ***/
1282
1283 l_tot_alloc_percent := l_tot_alloc_percent +
1284 l_Shipping_Org_rec.allocation_percent;
1285
1286 -- if supplier and supplier site are passed in and there is
1287 -- an organization modelled as this supplier at this site,
1288 -- populate the source_organization_id
1289
1290 l_organization_id := FND_API.G_MISS_NUM;
1291
1292 /* 2448893 - Add to_char to the vendor_id and vendor_site_id in
1293 l_shipping_org_rec since supplier_id and supplier_site_id in
1294 mrp_cust_sup_org_v are varchar2 */
1295
1296 BEGIN
1297 SELECT organization_id
1298 INTO l_organization_id
1299 FROM mrp_cust_sup_org_v
1300 WHERE supplier_id = to_char(l_Shipping_Org_rec.vendor_id)
1301 AND supplier_site_id = to_char(l_Shipping_Org_rec.vendor_site_id);
1302
1303 IF l_organization_id IS NOT NULL AND
1304 l_organization_id <> FND_API.G_MISS_NUM THEN
1305 UPDATE mrp_sr_source_org
1306 SET source_organization_id = l_organization_id
1307 WHERE sr_source_id = l_Shipping_Org_rec.sr_source_id;
1308
1309 END IF;
1310 EXCEPTION
1311 WHEN NO_DATA_FOUND THEN
1312 NULL;
1313 END;
1314
1315 IF l_tot_alloc_percent > 100 THEN
1316 -- dbms_output.put_line ('total alloc percent cannot be > 100');
1317 FND_MESSAGE.SET_NAME('MRP','MRP_ATTRIBUTE_VALUE_ERROR');
1318 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Rank');
1319 FND_MESSAGE.SET_TOKEN('DETAILS',
1320 'Total Allocation Percent cannot be greater than 100');
1321 FND_MSG_PUB.Add;
1322 l_Shipping_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1323 END IF;
1324
1325 END LOOP;
1326
1327 IF l_tot_alloc_percent < 100 THEN /* bug 2263575 : For the last record */
1328 UPDATE mrp_sourcing_rules
1329 SET planning_active = 2
1330 WHERE sourcing_rule_id =
1331 l_sourcing_rule_rec.sourcing_rule_id;
1332 END IF;
1333
1334
1335 END IF;
1336
1337 -- Step 6. Perform Object group logic
1338
1339 IF p_control_rec.process AND
1340 p_control_rec.process_entity = MRP_GLOBALS.G_ENTITY_ALL AND
1341 l_sourcing_rule_rec.operation <> MRP_GLOBALS.G_OPR_DELETE
1342 THEN
1343
1344 -- Every sourcing rule should have at least one receiving org
1345 -- and every receiving org should have at least one source org
1346
1347 SELECT count(*)
1348 INTO l_count
1349 FROM MRP_SR_RECEIPT_ORG
1350 WHERE sourcing_rule_id = l_sourcing_rule_rec.sourcing_rule_id;
1351
1352 IF l_count = 0 THEN
1353 -- dbms_output.put_line ('At least one receiving org req');
1354 FND_MESSAGE.SET_NAME('MRP','MRP_INCOMPLETE_OBJECT');
1355 FND_MESSAGE.SET_TOKEN('OBJECT','Sourcing_Rule');
1356 FND_MESSAGE.SET_TOKEN('DETAILS',
1357 'At least one receiving organization is required');
1358 FND_MSG_PUB.Add;
1359 l_sourcing_rule_rec.return_status := FND_API.G_RET_STS_ERROR;
1360 END IF;
1361
1362 FOR I IN 1..l_Receiving_Org_tbl.COUNT LOOP
1363 SELECT count(*)
1364 INTO l_count
1365 FROM MRP_SR_SOURCE_ORG
1366 WHERE sr_receipt_id = l_Receiving_Org_tbl(I).Sr_Receipt_Id;
1367
1368 IF l_count = 0 THEN
1369 -- dbms_output.put_line ('At least one source req');
1370 FND_MESSAGE.SET_NAME('MRP','MRP_INCOMPLETE_ENTITY');
1371 FND_MESSAGE.SET_TOKEN('ENTITY','Receiving_Org');
1372 FND_MESSAGE.SET_TOKEN('DETAILS',
1373 'At least one source organization is required');
1374 FND_MSG_PUB.Add;
1375 l_Receiving_Org_tbl(I).return_status := FND_API.G_RET_STS_ERROR;
1376 END IF;
1377 END LOOP;
1378
1379 END IF;
1380
1381 -- Done processing, load OUT parameters.
1382
1383 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
1384 x_Receiving_Org_tbl := l_Receiving_Org_tbl;
1385 x_Shipping_Org_tbl := l_Shipping_Org_tbl;
1386
1387 -- Clear API cache.
1388
1389 IF p_control_rec.clear_api_cache THEN
1390
1391 NULL;
1392
1393 END IF;
1394
1395 -- Clear API request tbl.
1396
1397 IF p_control_rec.clear_api_requests THEN
1398
1399 NULL;
1400
1401 END IF;
1402
1403 -- Derive return status.
1404
1405 x_return_status := FND_API.G_RET_STS_SUCCESS;
1406
1407 IF l_Sourcing_Rule_rec.return_status = FND_API.G_RET_STS_ERROR THEN
1408 ROLLBACK TO Process_Sourcing_Rule_PVT;
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 END IF;
1411
1412 FOR I IN 1..l_Receiving_Org_tbl.COUNT LOOP
1413
1414 IF l_Receiving_Org_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
1415 ROLLBACK TO Process_Sourcing_Rule_PVT;
1416 x_return_status := FND_API.G_RET_STS_ERROR;
1417 END IF;
1418
1419 END LOOP;
1420
1421 FOR I IN 1..l_Shipping_Org_tbl.COUNT LOOP
1422
1423 IF l_Shipping_Org_tbl(I).return_status = FND_API.G_RET_STS_ERROR THEN
1424 ROLLBACK TO Process_Sourcing_Rule_PVT;
1425 x_return_status := FND_API.G_RET_STS_ERROR;
1426 END IF;
1427
1428 END LOOP;
1429
1430 -- Get message count and data
1431
1432 FND_MSG_PUB.Count_And_Get
1433 ( p_count => x_msg_count
1434 , p_data => x_msg_data
1435 );
1436
1437
1438 EXCEPTION
1439
1440 WHEN FND_API.G_EXC_ERROR THEN
1441
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443
1444 -- Get message count and data
1445
1446 FND_MSG_PUB.Count_And_Get
1447 ( p_count => x_msg_count
1448 , p_data => x_msg_data
1449 );
1450
1451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1452
1453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1454
1455 -- Get message count and data
1456
1457 FND_MSG_PUB.Count_And_Get
1458 ( p_count => x_msg_count
1459 , p_data => x_msg_data
1460 );
1461
1462 WHEN OTHERS THEN
1463
1464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1465
1466 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1467 THEN
1468 FND_MSG_PUB.Add_Exc_Msg
1469 ( G_PKG_NAME
1470 , 'Process_Sourcing_Rule'
1471 );
1472 END IF;
1473
1474 -- Get message count and data
1475
1476 FND_MSG_PUB.Count_And_Get
1477 ( p_count => x_msg_count
1478 , p_data => x_msg_data
1479 );
1480
1481 END Process_Sourcing_Rule;
1482
1483 -- Start of Comments
1484 -- API name Lock_Sourcing_Rule
1485 -- Type Private
1486 -- Function
1487 --
1488 -- Pre-reqs
1489 --
1490 -- Parameters
1491 --
1492 -- Version Current version = 1.0
1493 -- Initial version = 1.0
1494 --
1495 -- Notes
1496 --
1497 -- End of Comments
1498
1499 PROCEDURE Lock_Sourcing_Rule
1500 ( p_api_version_number IN NUMBER
1501 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1502 , x_return_status OUT NOCOPY VARCHAR2
1503 , x_msg_count OUT NOCOPY NUMBER
1504 , x_msg_data OUT NOCOPY VARCHAR2
1505 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type :=
1506 MRP_Sourcing_Rule_PUB.G_MISS_SOURCING_RULE_REC
1507 , p_Receiving_Org_tbl IN MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type :=
1508 MRP_Sourcing_Rule_PUB.G_MISS_RECEIVING_ORG_TBL
1509 , p_Shipping_Org_tbl IN MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type :=
1510 MRP_Sourcing_Rule_PUB.G_MISS_SHIPPING_ORG_TBL
1511 , x_Sourcing_Rule_rec OUT NOCOPY MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
1512 , x_Receiving_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
1513 , x_Shipping_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
1514 )
1515 IS
1516 l_api_version_number CONSTANT NUMBER := 1.0;
1517 l_api_name CONSTANT VARCHAR2(30):= 'Lock_Sourcing_Rule';
1518 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1519 l_Receiving_Org_rec MRP_Sourcing_Rule_PUB.Receiving_Org_Rec_Type;
1520 l_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
1521 BEGIN
1522
1523 -- Standard call to check for call compatibility
1524
1525 IF NOT FND_API.Compatible_API_Call
1526 ( l_api_version_number
1527 , p_api_version_number
1528 , l_api_name
1529 , G_PKG_NAME
1530 )
1531 THEN
1532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1533 END IF;
1534
1535 -- Initialize message list.
1536
1537 IF FND_API.to_Boolean(p_init_msg_list) THEN
1538 FND_MSG_PUB.initialize;
1539 END IF;
1540
1541 -- Set Savepoint
1542
1543 SAVEPOINT Lock_Sourcing_Rule_PVT;
1544
1545 -- Lock Sourcing_Rule
1546
1547 IF p_Sourcing_Rule_rec.operation = MRP_Globals.G_OPR_LOCK THEN
1548
1549 MRP_Sourcing_Rule_Handlers.Lock_Row
1550 ( p_Sourcing_Rule_rec => p_Sourcing_Rule_rec
1551 , x_Sourcing_Rule_rec => x_Sourcing_Rule_rec
1552 , x_return_status => l_return_status
1553 );
1554
1555 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1557 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1558 RAISE FND_API.G_EXC_ERROR;
1559 END IF;
1560
1561
1562 END IF;
1563
1564 -- Lock Receiving_Org
1565
1566 FOR I IN 1..p_Receiving_Org_tbl.COUNT LOOP
1567
1568 IF p_Receiving_Org_tbl(I).operation = MRP_Globals.G_OPR_LOCK THEN
1569
1570 MRP_Receiving_Org_Handlers.Lock_Row
1571 ( p_Receiving_Org_rec => p_Receiving_Org_tbl(I)
1572 , x_Receiving_Org_rec => l_Receiving_Org_rec
1573 , x_return_status => l_return_status
1574 );
1575
1576 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1578 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1579 RAISE FND_API.G_EXC_ERROR;
1580 END IF;
1581
1582 x_Receiving_Org_tbl(I) := l_Receiving_Org_rec;
1583
1584 END IF;
1585
1586 END LOOP;
1587
1588 -- Lock Shipping_Org
1589
1590 FOR I IN 1..p_Shipping_Org_tbl.COUNT LOOP
1591
1592 IF p_Shipping_Org_tbl(I).operation = MRP_Globals.G_OPR_LOCK THEN
1593
1594 MRP_Shipping_Org_Handlers.Lock_Row
1595 ( p_Shipping_Org_rec => p_Shipping_Org_tbl(I)
1596 , x_Shipping_Org_rec => l_Shipping_Org_rec
1597 , x_return_status => l_return_status
1598 );
1599
1600 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1602 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF;
1605
1606 x_Shipping_Org_tbl(I) := l_Shipping_Org_rec;
1607
1608 END IF;
1609
1610 END LOOP;
1611
1612 -- Set return status
1613
1614 x_return_status := FND_API.G_RET_STS_SUCCESS;
1615
1616 -- Get message count and data
1617
1618 FND_MSG_PUB.Count_And_Get
1619 ( p_count => x_msg_count
1620 , p_data => x_msg_data
1621 );
1622
1623
1624 EXCEPTION
1625
1626 WHEN FND_API.G_EXC_ERROR THEN
1627
1628 x_return_status := FND_API.G_RET_STS_ERROR;
1629
1630 -- Get message count and data
1631
1632 FND_MSG_PUB.Count_And_Get
1633 ( p_count => x_msg_count
1634 , p_data => x_msg_data
1635 );
1636
1637 -- Rollback
1638
1639 ROLLBACK TO Lock_Sourcing_Rule_PVT;
1640
1641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1642
1643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1644
1645 -- Get message count and data
1646
1647 FND_MSG_PUB.Count_And_Get
1648 ( p_count => x_msg_count
1649 , p_data => x_msg_data
1650 );
1651
1652 -- Rollback
1653
1654 ROLLBACK TO Lock_Sourcing_Rule_PVT;
1655
1656 WHEN OTHERS THEN
1657
1658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1659
1660 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1661 THEN
1662 FND_MSG_PUB.Add_Exc_Msg
1663 ( G_PKG_NAME
1664 , 'Lock_Sourcing_Rule'
1665 );
1666 END IF;
1667
1668 -- Get message count and data
1669
1670 FND_MSG_PUB.Count_And_Get
1671 ( p_count => x_msg_count
1672 , p_data => x_msg_data
1673 );
1674
1675 -- Rollback
1676
1677 ROLLBACK TO Lock_Sourcing_Rule_PVT;
1678
1679 END Lock_Sourcing_Rule;
1680
1681 -- Start of Comments
1682 -- API name Get_Sourcing_Rule
1683 -- Type Private
1684 -- Function
1685 --
1686 -- Pre-reqs
1687 --
1688 -- Parameters
1689 --
1690 -- Version Current version = 1.0
1691 -- Initial version = 1.0
1692 --
1693 -- Notes
1694 --
1695 -- End of Comments
1696
1697 PROCEDURE Get_Sourcing_Rule
1698 ( p_api_version_number IN NUMBER
1699 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1700 , x_return_status OUT NOCOPY VARCHAR2
1701 , x_msg_count OUT NOCOPY NUMBER
1702 , x_msg_data OUT NOCOPY VARCHAR2
1703 , p_Sourcing_Rule_Id IN NUMBER
1704 , x_Sourcing_Rule_rec OUT NOCOPY MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
1705 , x_Receiving_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type
1706 , x_Shipping_Org_tbl OUT NOCOPY MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
1707 )
1708 IS
1709 l_api_version_number CONSTANT NUMBER := 1.0;
1710 l_api_name CONSTANT VARCHAR2(30):= 'Get_Sourcing_Rule';
1711 l_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type;
1712 l_Receiving_Org_tbl MRP_Sourcing_Rule_PUB.Receiving_Org_Tbl_Type;
1713 l_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
1714 l_x_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
1715 BEGIN
1716
1717 -- Standard call to check for call compatibility
1718
1719 IF NOT FND_API.Compatible_API_Call
1720 ( l_api_version_number
1721 , p_api_version_number
1722 , l_api_name
1723 , G_PKG_NAME
1724 )
1725 THEN
1726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1727 END IF;
1728
1729 -- Initialize message list.
1730
1731 IF FND_API.to_Boolean(p_init_msg_list) THEN
1732 FND_MSG_PUB.initialize;
1733 END IF;
1734
1735 -- Get Sourcing_Rule
1736
1737 l_Sourcing_Rule_rec := MRP_Sourcing_Rule_Handlers.Query_Row
1738 ( p_Sourcing_Rule_Id => p_Sourcing_Rule_Id
1739 );
1740
1741 -- Get Receiving_Org ( parent = Sourcing_Rule )
1742
1743 l_Receiving_Org_tbl := MRP_Receiving_Org_Handlers.Query_Rows
1744 ( p_Sourcing_Rule_Id => l_Sourcing_Rule_rec.Sourcing_Rule_Id
1745 );
1746
1747
1748 -- Loop over Receiving_Org's children
1749
1750 FOR I1 IN 1..l_Receiving_Org_tbl.COUNT LOOP
1751
1752 -- Get Shipping_Org ( parent = Receiving_Org )
1753
1754 l_Shipping_Org_tbl := MRP_Shipping_Org_Handlers.Query_Rows
1755 ( p_Sr_Receipt_Id => l_Receiving_Org_tbl(I1).Sr_Receipt_Id
1756 );
1757
1758 FOR I2 IN 1..l_Shipping_Org_tbl.COUNT LOOP
1759 l_Shipping_Org_tbl(I2).Receiving_Org_Index := I1;
1760 l_x_Shipping_Org_tbl
1761 (l_x_Shipping_Org_tbl.COUNT + 1) := l_Shipping_Org_tbl(I2);
1762 END LOOP;
1763
1764 END LOOP;
1765
1766 -- Load out parameters
1767
1768 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
1769 x_Receiving_Org_tbl := l_Receiving_Org_tbl;
1770 x_Shipping_Org_tbl := l_x_Shipping_Org_tbl;
1771
1772 -- Set return status
1773
1774 x_return_status := FND_API.G_RET_STS_SUCCESS;
1775
1776 -- Get message count and data
1777
1778 FND_MSG_PUB.Count_And_Get
1779 ( p_count => x_msg_count
1780 , p_data => x_msg_data
1781 );
1782
1783
1784 EXCEPTION
1785
1786 WHEN FND_API.G_EXC_ERROR THEN
1787
1788 x_return_status := FND_API.G_RET_STS_ERROR;
1789
1790 -- Get message count and data
1791
1792 FND_MSG_PUB.Count_And_Get
1793 ( p_count => x_msg_count
1794 , p_data => x_msg_data
1795 );
1796
1797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1798
1799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1800
1801 -- Get message count and data
1802
1803 FND_MSG_PUB.Count_And_Get
1804 ( p_count => x_msg_count
1805 , p_data => x_msg_data
1806 );
1807
1808 WHEN OTHERS THEN
1809
1810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1811
1812 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1813 THEN
1814 FND_MSG_PUB.Add_Exc_Msg
1815 ( G_PKG_NAME
1816 , 'Get_Sourcing_Rule'
1817 );
1818 END IF;
1819
1820 -- Get message count and data
1821
1822 FND_MSG_PUB.Count_And_Get
1823 ( p_count => x_msg_count
1824 , p_data => x_msg_data
1825 );
1826
1827 END Get_Sourcing_Rule;
1828
1829 END MRP_Sourcing_Rule_PVT;