1 PACKAGE BODY MRP_SOURCING_API_PK AS
2 /* $Header: MRPSAPIB.pls 120.2 2006/03/08 04:50:40 davashia noship $ */
3
4 p_cutoff_history_days NUMBER := NVL(TO_NUMBER(FND_PROFILE.VALUE('MRP_CUTOFF_HISTORY_DAYS')),9999);
5 mrdebug BOOLEAN := FALSE;
6 cutoff_history_date NUMBER := to_number(to_char(trunc(SYSDATE),'j')) - p_cutoff_history_days;
7
8 FUNCTION validate_item(
9 arg_item_id IN NUMBER,
13 DEFAULT NULL)
10 arg_org_id IN NUMBER,
11 arg_sub_inv IN VARCHAR2 /* Bug 1646303 - changed */
12 /* datatype to varchar2 */
14 RETURN BOOLEAN IS
15
16 var_stk VARCHAR2(10);
17 var_restrict NUMBER;
18 BEGIN
19
20 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
21 SELECT stock_enabled_flag, restrict_subinventories_code
22 INTO var_stk,
23 var_restrict
24 FROM mtl_system_items
25 WHERE organization_id = arg_org_id
26 AND inventory_item_id = arg_item_id;
27
28 if var_stk <> 'Y'
29 then
30 return FALSE;
31 end if;
32
33 if arg_sub_inv is not null AND var_restrict = 1
34 then
35 BEGIN
36 SELECT 'Y'
37 into var_stk
38 FROM mtl_item_sub_inventories
39 WHERE organization_id = arg_org_id
40 AND inventory_item_id = arg_item_id
41 AND secondary_inventory = arg_sub_inv;
42
43 EXCEPTION
44 WHEN no_data_found THEN
45 RETURN FALSE;
46 END;
47 end if;
48 RETURN TRUE;
49 END validate_item;
50
51 FUNCTION mrp_sourcing(
52 arg_mode IN VARCHAR2,
53 arg_item_id IN NUMBER,
54 arg_commodity_id IN NUMBER,
55 arg_dest_organization_id IN NUMBER,
56 arg_dest_subinventory IN VARCHAR2,
57 arg_autosource_date IN DATE,
58 arg_vendor_id OUT NOCOPY NUMBER,
59 arg_vendor_site_code OUT NOCOPY VARCHAR2,
60 arg_source_organization_id IN OUT NOCOPY NUMBER,
61 arg_source_subinventory IN OUT NOCOPY VARCHAR2,/* Bug # 1646303 - changed */
62 /* datatype to varchar2 */
63 arg_sourcing_rule_id OUT NOCOPY NUMBER,
64 arg_error_message OUT NOCOPY VARCHAR2)
65 RETURN BOOLEAN IS
66
67 var_set_id NUMBER;
68 var_vendor_id NUMBER;
69 var_site_id NUMBER;
70 var_new_site_code po_vendor_sites_all.vendor_site_code%type;
71 var_source_org NUMBER;
72 var_alloc_percent NUMBER;
73 var_rank NUMBER;
74 var_sr_id NUMBER;
75 var_source_sub VARCHAR2(30);
76
77 found_org BOOLEAN;
78 found_ven BOOLEAN;
79
80 cursor sourcing( p_source_organization_id NUMBER) is
81 SELECT misl.vendor_id,
82 misl.vendor_site_id,
83 misl.source_organization_id,
84 misl.allocation_percent,
85 NVL(misl.rank, 9999),
86 misl.sourcing_rule_id
87 FROM mrp_item_sourcing_levels_v misl
88 WHERE misl.source_type in (1,3)
89 and misl.inventory_item_id = arg_item_id
90 and misl.organization_id = arg_dest_organization_id
91 and misl.assignment_set_id = var_set_id
92 and arg_autosource_date between misl.effective_date and
93 NVL(disable_date, to_date(2634525, 'J'))
94 and PO_ASL_SV.check_asl_action('2_SOURCING',
95 misl.vendor_id, misl.vendor_site_id, arg_item_id, -1
96 , arg_dest_organization_id ) <> 0
97 AND nvl(nvl(p_source_organization_id,
98 misl.source_organization_id), -23453)
99 = nvl(misl.source_organization_id, -23453)
100 ORDER BY misl.sourcing_level ASC,
101 allocation_percent DESC, NVL(misl.rank, 9999) ASC;
102 BEGIN
103
104 IF arg_source_organization_id IS NOT NULL AND
105 arg_source_subinventory IS NOT NULL THEN
106 RETURN TRUE;
107 END IF;
108
109
110 found_org := FALSE;
111 found_ven := FALSE;
112 arg_error_message := null;
113 /* arg_source_organization_id := null; */
114 arg_vendor_id := null;
115 arg_vendor_site_code := null;
116 mrdebug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
117
118 /*------------------------------------------------------------+
119 | First check the item-sub level if the mode is Inventory |
120 | or both |
121 +------------------------------------------------------------*/
122 if arg_mode = 'INVENTORY' or arg_mode = 'BOTH'
123 then
124 BEGIN
125
126 select misi.source_organization_id,
127 misi.source_subinventory
128 into var_source_org,
129 var_source_sub
130 from mtl_item_sub_inventories misi,
131 org_organization_definitions ood,
132 financials_system_parameters fsp
133 where misi.organization_id = arg_dest_organization_id
134 and misi.inventory_item_id = arg_item_id
135 and misi.secondary_inventory = arg_dest_subinventory
136 and misi.organization_id = ood.organization_id
137 and ood.set_of_books_id = fsp.set_of_books_id
138 and ood.operating_unit = fsp.org_id -- bug 4968383
139 AND Nvl(arg_source_organization_id,
140 misi.source_organization_id) = misi.source_organization_id;
141
142 /*--------------------------------------+
143 | Validate the item in the source org |
144 +--------------------------------------*/
145 if validate_item(arg_item_id, var_source_org, var_source_sub)
146 = TRUE
147 then
148 arg_source_organization_id := var_source_org;
149 arg_source_subinventory := var_source_sub;
150 found_org := TRUE;
151 /*---------------------------------------+
152 | If mode is inventory you are done |
153 +---------------------------------------*/
154 if arg_mode = 'INVENTORY'
155 then
156 RETURN TRUE;
157 end if;
158 end if;
159 EXCEPTION
160 WHEN no_data_found THEN
161 NULL;
162 END;
163 end if;
164 /*------------------------------------------------------------+
165 | Then check the sub level if the mode is Inventory |
166 | or both |
167 +------------------------------------------------------------*/
168 if arg_mode = 'INVENTORY' or arg_mode = 'BOTH'
169 then
170 BEGIN
171
172 select msi.source_organization_id,
173 msi.source_subinventory
174 into var_source_org,
175 var_source_sub
176 from mtl_secondary_inventories msi,
177 org_organization_definitions ood,
178 financials_system_parameters fsp
179 where msi.organization_id = arg_dest_organization_id
180 and msi.secondary_inventory_name = arg_dest_subinventory
181 and msi.organization_id = ood.organization_id
182 and ood.set_of_books_id = fsp.set_of_books_id
183 and ood.operating_unit = fsp.org_id -- bug 4968383
184 AND Nvl(arg_source_organization_id,
185 msi.source_organization_id) = msi.source_organization_id;
186
187
188 /*--------------------------------------+
189 | Validate the item in the source org |
190 +--------------------------------------*/
191 if validate_item(arg_item_id, var_source_org, var_source_sub)
192 = TRUE
193 then
194 arg_source_organization_id := var_source_org;
195 arg_source_subinventory := var_source_sub;
196 found_org := TRUE;
197 /*---------------------------------------+
198 | If mode is inventory you are done |
199 +---------------------------------------*/
200 if arg_mode = 'INVENTORY'
201 then
202 RETURN TRUE;
203 end if;
204 end if;
205 EXCEPTION
206 WHEN no_data_found THEN
207 NULL;
208 END;
209 end if;
210
211 /* Bug # 1646303 - check the item-org level */
212
213 /*------------------------------------------------------------+
214 | Then check the item-org level if the mode is Inventory |
215 | or both |
216 +------------------------------------------------------------*/
217 if arg_mode = 'INVENTORY' or arg_mode = 'BOTH'
218 then
219 BEGIN
220 SELECT msi.source_organization_id,
221 msi.source_subinventory
222 INTO var_source_org,
223 var_source_sub
224 FROM mtl_system_items msi,
225 org_organization_definitions ood,
226 financials_system_parameters fsp
227 WHERE msi.organization_id = arg_dest_organization_id
228 AND msi.inventory_item_id = arg_item_id
229 AND msi.organization_id = ood.organization_id
230 AND ood.operating_unit = fsp.org_id -- bug 4968383
231 AND ood.set_of_books_id = fsp.set_of_books_id;
232
233 /*--------------------------------------+
234 | Validate the item in the source org |
235 +--------------------------------------*/
236 if validate_item(arg_item_id, var_source_org, var_source_sub)
237 = TRUE
238 then
239 arg_source_organization_id := var_source_org;
240 arg_source_subinventory := var_source_sub;
241 found_org := TRUE;
242 /*---------------------------------------+
243 | If mode is inventory you are done |
244 +---------------------------------------*/
245 if arg_mode = 'INVENTORY'
246 then
247 RETURN TRUE;
248 end if;
249 end if;
250 EXCEPTION
251 WHEN no_data_found THEN
252 NULL;
253 END;
254 end if;
255 /* end of item-org check */
256
257 /* Bug # 1646303 - check the Org level */
258
259 /*------------------------------------------------------------+
260 | Then check the Org level if the mode is Inventory |
261 | or both |
262 +------------------------------------------------------------*/
263 if arg_mode = 'INVENTORY' or arg_mode = 'BOTH'
264 then
265 BEGIN
266 SELECT mp.source_organization_id,
267 mp.source_subinventory
268 INTO var_source_org,
269 var_source_sub
270 FROM mtl_parameters mp,
271 org_organization_definitions ood,
272 financials_system_parameters fsp
273 WHERE mp.organization_id = arg_dest_organization_id
274 AND mp.organization_id = ood.organization_id
275 AND ood.operating_unit = fsp.org_id -- bug 4968383
276 AND ood.set_of_books_id = fsp.set_of_books_id;
277
278 /*--------------------------------------+
279 | Validate the item in the source org |
280 +--------------------------------------*/
281 if validate_item(arg_item_id, var_source_org, var_source_sub)
282 = TRUE
283 then
284 arg_source_organization_id := var_source_org;
285 arg_source_subinventory := var_source_sub;
286 found_org := TRUE;
287 /*---------------------------------------+
288 | If mode is inventory you are done |
289 +---------------------------------------*/
290 if arg_mode = 'INVENTORY'
291 then
292 RETURN TRUE;
293 end if;
294 end if;
295 EXCEPTION
296 WHEN no_data_found THEN
297 NULL;
298 END;
299 end if;
300 /* end of Org check */
301
302 /*-----------------------------------------+
303 | before checking MRP sources get the |
304 | default assignment set id |
305 +-----------------------------------------*/
306 var_set_id :=
307 TO_NUMBER(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
308
309 if var_set_id is null
310 then
311 fnd_message.set_name('MRP', 'MRCONC-CANNOT GET PROFILE');
312 fnd_message.set_token('PROFILE', 'MRP_DEFAULT_ASSIGNMENT_SET');
313 arg_error_message := fnd_message.get;
314 return FALSE;
315 end if;
316
317
318 BEGIN
319 OPEN sourcing(arg_source_organization_id);
320
321 /*------------------------------------------------+
322 | Loop through all the levels looking for first |
323 | valid source |
324 +------------------------------------------------*/
325 LOOP FETCH sourcing INTO
326 var_vendor_id,
327 var_site_id,
328 var_source_org,
329 var_alloc_percent,
330 var_rank,
331 var_sr_id;
332 EXIT WHEN sourcing%NOTFOUND;
333
334 var_new_site_code := '-23453' ;
335 if var_vendor_id is not null then
336 SELECT site.vendor_site_code
337 INTO var_new_site_code
338 FROM
339 po_vendor_sites_all site,
340 po_vendors ven
341 WHERE NVL(ven.enabled_flag, 'N') = 'Y'
342 AND SYSDATE BETWEEN NVL(ven.start_date_active, SYSDATE -1)
343 AND NVL(ven.end_date_active, sysdate+1)
344 AND SYSDATE < NVL(site.inactive_date, SYSDATE + 1)
345 AND ven.vendor_id = site.vendor_id(+)
346 AND site.vendor_site_id(+) = var_site_id
347 AND ven.vendor_id = var_vendor_id ;
348 end if;
349
350 if (var_vendor_id is null or var_new_site_code <> '-23453' or
351 (var_new_site_code is null and var_vendor_id is not null)) then
352
353 if (found_org = FALSE and
354 (arg_mode = 'INVENTORY' or arg_mode = 'BOTH')
355 and var_source_org is not null)
356 then
357 /*--------------------------------------+
358 | Validate the item in the source org |
359 +--------------------------------------*/
360 if validate_item(arg_item_id, var_source_org) = TRUE
361 then
362 found_org := TRUE;
363 arg_source_organization_id := var_source_org;
364 if (arg_mode = 'INVENTORY')
365 then
366 RETURN TRUE;
367 end if;
368 end if;
369 end if;
370 if (found_ven = FALSE and
371 (arg_mode = 'VENDOR' or arg_mode = 'BOTH') and
372 var_vendor_id is not null)
373 then
374 found_ven := TRUE;
375 arg_vendor_id := var_vendor_id;
376 arg_vendor_site_code := var_new_site_code;
377 if (arg_mode = 'VENDOR')
378 then
379 RETURN TRUE;
380 end if;
381 end if;
382 if (found_org = TRUE and found_ven = TRUE)
383 then
384 return TRUE;
385 end if;
386 end if;
387 END LOOP;
388 if (((arg_mode = 'INVENTORY' or arg_mode = 'BOTH') and
389 found_org = FALSE) or
390 ((arg_mode = 'VENDOR' or arg_mode = 'BOTH') and
391 found_ven = FALSE))
392 then
393 fnd_message.set_name('MRP', 'GEN-CANNOT SELECT');
394 fnd_message.set_token('SELECT', 'EC_SOURCE', TRUE);
398 end if;
395 fnd_message.set_token('ROUTINE', 'MRP_SOURCING', FALSE);
396 arg_error_message := fnd_message.get || 'JUNK12';
397 RETURN FALSE;
399 EXCEPTION
400 WHEN no_data_found THEN
401 fnd_message.set_name('MRP', 'GEN-NO ROWS SELECTED');
402 fnd_message.set_token('TABLE', 'mrp_sources_v');
403 arg_error_message := fnd_message.get;
404 RETURN FALSE;
405 END;
406 END mrp_sourcing;
407
408 FUNCTION mrp_sourcing(
409 arg_mode IN VARCHAR2,
410 arg_item_id IN NUMBER,
411 arg_commodity_id IN NUMBER,
412 arg_dest_organization_id IN NUMBER,
413 arg_dest_subinventory IN VARCHAR2,
414 arg_autosource_date IN DATE,
415 arg_vendor_id OUT NOCOPY NUMBER,
416 arg_vendor_site_id OUT NOCOPY NUMBER,
417 arg_source_organization_id IN OUT NOCOPY NUMBER,
418 arg_source_subinventory IN OUT NOCOPY VARCHAR2,
419 arg_sourcing_rule_id OUT NOCOPY NUMBER,
420 arg_error_message OUT NOCOPY VARCHAR2)
421 RETURN BOOLEAN IS
422 var_vendor_site_code po_vendor_sites_all.vendor_site_code%TYPE;
423 BEGIN
424 if( mrp_sourcing(
425 arg_mode => arg_mode,
426 arg_item_id => arg_item_id,
427 arg_commodity_id => arg_commodity_id,
428 arg_dest_organization_id => arg_dest_organization_id,
429 arg_dest_subinventory => arg_dest_subinventory,
430 arg_autosource_date => arg_autosource_date,
431 arg_vendor_id => arg_vendor_id,
432 arg_vendor_site_code => var_vendor_site_code,
433 arg_source_organization_id => arg_source_organization_id,
434 arg_source_subinventory => arg_source_subinventory,
435 arg_sourcing_rule_id => arg_sourcing_rule_id,
436 arg_error_message => arg_error_message))
437 THEN
438 IF var_vendor_site_code IS NOT NULL THEN
439 select dest_site.vendor_site_id
440 into arg_vendor_site_id
441 FROM org_organization_definitions oog,
442 po_vendor_sites_all dest_site,
443 po_vendors ven
444 WHERE NVL(ven.enabled_flag, 'N') = 'Y'
445 AND sysdate BETWEEN NVL(ven.start_date_active, sysdate -1)
446 AND NVL(ven.end_date_active, sysdate+1)
447 AND ven.vendor_id = arg_vendor_id
448 AND dest_site.vendor_id(+) = ven.vendor_id
449 AND dest_site.vendor_site_code(+) = var_vendor_site_code
450 AND nvl(dest_site.org_id,nvl(oog.operating_unit,-1)) =
451 nvl(oog.operating_unit,-1)
452 AND oog.organization_id = arg_dest_organization_id;
453 END IF;
454
455 return(TRUE);
456 else
457 arg_vendor_id := NULL;
458 return(FALSE);
459 end if;
460
461 EXCEPTION
462 WHEN no_data_found THEN
463 arg_vendor_id := NULL;
464 fnd_message.set_name('MRP', 'GEN-NO ROWS SELECTED');
465 fnd_message.set_token('TABLE', 'mrp_sources_v');
466 arg_error_message := fnd_message.get;
467 RETURN FALSE;
468 END mrp_sourcing;
469
470 /* This function checks all sourcing rules to see if
471 * any of them uses the item-supplier combination.
472 * It returns 1 if there are no such sourcing rules
473 * else returns 0
474 */
475
476 FUNCTION mrp_sourcing_rule_exists
477 (
478 arg_item_id IN NUMBER,
479 arg_category_id IN NUMBER,
480 arg_supplier_id IN NUMBER,
481 arg_supplier_site_id IN NUMBER,
482 arg_message OUT NOCOPY VARCHAR2
483 ) RETURN NUMBER IS
484
485 /* Only one among arg_item_id, arg_category_id will be NOT NULL
486 * and one of them will be not null.
487 */
488
489 CURSOR sr IS
490 SELECT distinct misl.sourcing_rule_name, ml.meaning
491 FROM
492 mfg_lookups ml,
493 mrp_item_sourcing_levels_v misl
494 WHERE misl.inventory_item_id = NVL(arg_item_id, misl.inventory_item_id)
495 and NVL(misl.category_id,-1) = NVL(arg_category_id, NVL(misl.category_id, -1))
496 and misl.vendor_id = arg_supplier_id
497 and NVL(misl.vendor_site_id,-1) = NVL(arg_supplier_site_id,-1)
498 and sysdate < NVL(misl.disable_date, sysdate + 1)
499 and ml.lookup_type = 'MRP_ASSIGNMENT_TYPE'
500 and ml.lookup_code = misl.assignment_type;
501
502 x_sourcing_rule_name VARCHAR2(50);
503 x_assignment_type VARCHAR2(80);
504 counter NUMBER;
505 BEGIN
506
507 OPEN sr;
508 counter := 1;
509
510 FND_MESSAGE.SET_NAME('MRP', 'MRP_SUPPLIER_IN_SOURCING_1');
511 arg_message := FND_MESSAGE.GET;
512 arg_message := arg_message||fnd_global.newline;
513
514 LOOP
515 FETCH sr into x_sourcing_rule_name, x_assignment_type;
516 EXIT WHEN sr%NOTFOUND;
517
518 IF counter <= 10 THEN
519 arg_message := arg_message||' '||x_sourcing_rule_name||' '||x_assignment_type||fnd_global.newline;
520 END IF;
521 counter := counter + 1;
525
522 END LOOP;
523
524 CLOSE sr;
526 IF counter = 1 THEN
527 return 1; -- There are no sourcing_rules found
528 END IF;
529
530 FND_MESSAGE.SET_NAME('MRP', 'MRP_SUPPLIER_IN_SOURCING_2');
531 fnd_message.set_token('COUNT', counter);
532 arg_message := arg_message||' '||FND_MESSAGE.GET;
533
534 return 0;
535 END mrp_sourcing_rule_exists;
536
537
538 function mrp_get_sourcing_history(
539 arg_source_org IN NUMBER,
540 arg_vendor_id IN NUMBER,
541 arg_vendor_site_id IN NUMBER,
542 arg_item_id IN NUMBER,
543 arg_org_id IN NUMBER,
544 arg_sourcing_rule_id IN NUMBER,
545 arg_start_date IN OUT NOCOPY NUMBER,
546 arg_end_date IN NUMBER,
547 arg_err_mesg OUT NOCOPY VARCHAR2)
548 return number is
549 PRAGMA AUTONOMOUS_TRANSACTION;
550 greater_than_plan_date number;
551 x_last_calculated_date date;
552 x_total_alloc_qty NUMBER :=0 ;
553 x_historical_allocation number := 0;
554 arg_end_date1 NUMBER;
555 arg_vendor_site_id1 NUMBER;
556 begin
557
558 arg_err_mesg := NULL;
559
560 select to_date(arg_start_date,'J') - sysdate
561 into greater_than_plan_date
562 from dual;
563
564 if greater_than_plan_date > 0 then
565 return 0;
566 -- there will be no history for any sourcing rule with start_date greater than sysdate.
567 end if;
568
569 if arg_end_date = NULL_VALUE then
570 arg_end_date1 := to_number(to_char(sysdate,'j'));
571 else
572 arg_end_date1 := arg_end_date;
573 end if;
574
575 if arg_vendor_site_id = NULL_VALUE then
576 arg_vendor_site_id1 := NULL;
577 else
578 arg_vendor_site_id1 := arg_vendor_site_id;
579 end if;
580
581 if cutoff_history_date > arg_start_date then
582 arg_start_date := cutoff_history_date;
583 end if;
584
585
586 /** Bug 1921263 : The total historical allocation will be computed everytime
587 (not just the delta as was being done earlier) the plan runs.
588 The historical allocations for items purchased from vendors
589 and internally transferred items will be calculated by PO
590 functions.
591 ***/
592
593 IF (arg_source_org = arg_org_id) THEN
594
595 select NVL(sum(PRIMARY_QUANTITY),0)
596 into x_total_alloc_qty
597 from mtl_material_transactions
598 where
599 inventory_item_id = arg_item_id
600 and ORGANIZATION_ID = arg_org_id
601 and transaction_action_id in (30, 31, 32 )
602 /* WIP scrap, Assembly compl , Assy Return qty is -ve */
603 and transaction_date between to_date(arg_start_date,'J')
604 and decode(arg_end_date,NULL_VALUE,transaction_date, to_date(arg_end_date,'J'));
605
606 if x_total_alloc_qty < 0 then
607 x_total_alloc_qty := 0; -- since we take into account returns also.
608 end if;
609
610 -- if there is no record then calculate history from start_date of sourcing rule
611 -- else calculate from the next day after the date for which it was calculated last time
612
613 ELSIF (arg_source_org <> arg_org_id AND arg_source_org <> NULL_VALUE ) THEN
614 -- Transfers
615
616
617 RCV_GET_DELIVERED_QTY.GET_INTERNAL_DETAILS(arg_source_org, arg_org_id, arg_item_id,
618 to_date(arg_start_date,'J'), to_date(arg_end_date1,'J'), x_total_alloc_qty);
619
620 ELSIF arg_vendor_id IS NOT NULL THEN
621 -- Note the usage of arg_org_id, we use it only in the case of
622 -- transfers and NOT buy from vendor, since we will support only
623 -- global ASL. we need to fix this if ever we decide to support
624 -- local ASL.
625
626
627 RCV_GET_DELIVERED_QTY.GET_TRANSACTION_DETAILS(arg_vendor_id, arg_vendor_site_id1, arg_item_id,
628 to_date(arg_start_date,'J'), to_date(arg_end_date1, 'J'), x_total_alloc_qty);
629
630 END IF;
631
632 -- delete the previous record for the previous date range in the Sourcing rule.
633 -- Here only the previous record is deleted. If a SR is changed for an item, then in the
634 -- form we must clean up unnecassary history
635
636 delete from mrp_sourcing_history msh
637 where
638 msh.inventory_item_id = arg_item_id
639 and msh.organization_id = arg_org_id
640 and msh.sourcing_rule_id = arg_sourcing_rule_id
641 and NVL(msh.source_org_id,-1) = decode(arg_source_org, NULL_VALUE, NVL(msh.source_org_id,-1), arg_source_org)
642 and NVL(msh.vendor_id,-1) = decode(arg_vendor_id, NULL_VALUE, NVL(msh.vendor_id,-1), arg_vendor_id)
643 and NVL(msh.vendor_site_id,-1) = decode(arg_vendor_site_id, NULL_VALUE, NVL(msh.vendor_site_id,-1), arg_vendor_site_id);
644
645 -- insert a new record
646 -- a record is inserted even if the quantity is 0 since we would be better off
647 -- next time if we don't search for those transaction dates.
648
649 -- sourcing_rule_id is there in the table so that if we change the SR, then the history
650 -- is recalculated. It is ok if the assignment changes, since history is related to the SR
651 -- only. that is y assignment set is not a column.
652
653 insert into mrp_sourcing_history
654 (
655 inventory_item_id,
656 organization_id,
657 sourcing_rule_id,
658 source_org_id,
659 vendor_id,
660 vendor_site_id,
661 historical_allocation,
665 creation_date,
662 last_calculated_date,
663 last_updated_by,
664 last_update_date,
666 created_by,
667 last_update_login,
668 request_id,
669 program_application_id,
670 program_id,
671 program_update_date
672 )
673 values (
674 arg_item_id,
675 arg_org_id,
676 arg_sourcing_rule_id,
677 decode(arg_source_org, NULL_VALUE, NULL, arg_source_org),
678 decode(arg_vendor_id, NULL_VALUE, NULL, arg_vendor_id),
679 decode(arg_vendor_site_id,NULL_VALUE, NULL, arg_vendor_site_id),
680 x_total_alloc_qty,
681 sysdate,
682 1,
683 sysdate,
684 sysdate,
685 1,
686 NULL,
687 NULL,
688 NULL,
689 NULL,
690 NULL
691 );
692
693 commit; -- autonomous transaction
694 return(x_total_alloc_qty);
695 exception
696 when others then
697 arg_err_mesg := substr(sqlerrm,1,100);
698 rollback;
699 return(0);
700 end mrp_get_sourcing_history;
701
702
703
704 FUNCTION mrp_po_historical_alloc
705 (
706 arg_source_org IN NUMBER,
707 arg_vendor_id IN NUMBER,
708 arg_vendor_site_id IN NUMBER,
709 arg_item_id IN NUMBER,
710 arg_org_id IN NUMBER,
711 arg_start_date IN DATE,
712 arg_end_date IN DATE)
713 RETURN NUMBER
714 IS
715
716 x_total_alloc_qty NUMBER := 0;
717 x_job_alloc_qty NUMBER := 0;
718 x_fs_alloc_qty NUMBER := 0;
719 x_rep_alloc_qty NUMBER := 0;
720 BEGIN
721 -- This means this is a make in the arg_org_id org
722
723 IF (arg_source_org = arg_org_id) THEN
724 SELECT NVL(SUM(NVL(jobs.quantity_completed,0)),0)
725 INTO x_job_alloc_qty
726 FROM wip_discrete_jobs jobs
727 WHERE jobs.primary_item_id = arg_item_id
728 AND jobs.organization_id = arg_org_id
729 AND DECODE(mps_consume_profile_value,
730 1, jobs.mps_scheduled_completion_date,
731 jobs.scheduled_completion_date) between
732 arg_start_date and NVL(arg_end_date,
733 Decode(mps_consume_profile_value,
734 1, jobs.mps_scheduled_completion_date,
735 jobs.scheduled_completion_date)
736 +1);
737
738 SELECT
739 Nvl(SUM(NVL(fs.quantity_completed,0)),0)
740 INTO x_fs_alloc_qty
741 FROM wip_flow_schedules fs
742 WHERE fs.primary_item_id = arg_item_id
743 AND fs.organization_id = arg_org_id
744 AND DECODE(mps_consume_profile_value,
745 1, fs.mps_scheduled_completion_date,
746 fs.scheduled_completion_date) between
747 arg_start_date and NVL(arg_end_date,
748 DECODE(mps_consume_profile_value,
749 1, fs.mps_scheduled_completion_date,
750 fs.scheduled_completion_date)+1);
751
752 SELECT
753 Nvl(SUM(NVL(rep.daily_production_rate * rep.processing_work_days,0)),0)
754 INTO x_rep_alloc_qty
755 FROM wip_repetitive_schedules rep,
756 wip_repetitive_items wri,
757 wip_entities we
758 WHERE
759 we.organization_id = arg_org_id
760 AND wri.wip_entity_id = we.wip_entity_id
761 AND we.primary_item_id = arg_item_id
762 AND wri.organization_id = we.organization_id
763 AND rep.wip_entity_id = we.wip_entity_id
764 AND rep.organization_id = wri.organization_id
765 AND rep.line_id = wri.line_id
766 AND rep.last_unit_completion_date between
767 arg_start_date and NVL(arg_end_date,rep.last_unit_completion_date+1);
768
769 x_total_alloc_qty := x_job_alloc_qty + x_fs_alloc_qty + x_rep_alloc_qty;
770 ELSIF (arg_source_org <> arg_org_id AND arg_source_org IS NOT NULL ) THEN
771 SELECT NVL(SUM(
772 INV_CONVERT.inv_um_convert
773 (
774 arg_item_id,
775 6,
776 Nvl(rct.quantity,0),
777 NULL,
778 NULL,
779 rsl.unit_of_measure,
780 rsl.primary_unit_of_measure
781 )),0)
782 INTO x_total_alloc_qty
783 FROM rcv_shipment_lines rsl,
784 rcv_transactions rct
785 WHERE rct.shipment_line_id = rsl.shipment_line_id
786 AND rct.transaction_type = 'DELIVER'
787 AND rsl.from_organization_id = arg_source_org
788 AND rsl.to_organization_id = arg_org_id
789 AND rsl.item_id = arg_item_id
790 AND rct.transaction_date BETWEEN
791 arg_start_date AND NVL(arg_end_date,rct.transaction_date +1);
792
793 ELSIF arg_vendor_id IS NOT NULL THEN
794 -- Note the usage of arg_org_id, we use it only in the case of
795 -- transfers and NOT buy from vendor, since we will support only
796 -- global ASL. we need to fix this if ever we decide to support
797 -- local ASL.
798 SELECT /*+ use_nl(rct,rsl, poh,pol)
799 INDEX(rct rcv_transactions_n15)
800 INDEX(rsl rcv_shipment_lines_u1)
801 INDEX(poh po_headers_u1)
802 INDEX(rol po_lines_u1) */
803 NVL(SUM(
807 6,
804 INV_CONVERT.inv_um_convert
805 (
806 arg_item_id,
808 Nvl(rct.quantity,0),
809 NULL,
810 NULL,
811 rsl.unit_of_measure,
812 rsl.primary_unit_of_measure
813 )),0)
814 INTO x_total_alloc_qty
815 FROM po_lines_all pol,
816 po_headers_all poh,
817 rcv_shipment_lines rsl,
818 rcv_transactions rct
819 WHERE rct.shipment_line_id = rsl.shipment_line_id
820 AND rct.transaction_type = 'DELIVER'
821 AND rsl.po_header_id = poh.po_header_id
822 AND rsl.po_line_id = pol.po_line_id
823 AND poh.vendor_id = arg_vendor_id
824 AND NVL(poh.vendor_site_id,-99) = NVL(arg_vendor_site_id,-99)
825 AND pol.item_id = arg_item_id
826 AND rct.transaction_date between arg_start_date AND
827 NVL(arg_end_date,rct.transaction_date +1);
828 END IF;
829 return(x_total_alloc_qty);
830 EXCEPTION
831 WHEN OTHERS THEN
832 return(0);
833 END mrp_po_historical_alloc;
834
835 END MRP_SOURCING_API_PK; -- package
[Home] [Help]