[Home] [Help]
39: capt.subinventory_code,
40: capt.required_item_id,
41: sum(nvl(supplied_quantity,0)) supplied_quantity,
42: crpt.quantity required_quantity
43: from csp_available_parts_temp capt,csp_required_parts_temp crpt
44: where crpt.inventory_item_id = capt.required_item_id
45: and item_type = 'BASE'
46: group by capt.organization_id,
47: capt.subinventory_code,
351: and crh.requirement_header_id = p_search_params.requirement_header_id;
352:
353: cursor c_enough is
354: select sum(capt.supplied_quantity)-min(crpt.quantity)
355: from csp_required_parts_temp crpt,
356: csp_available_parts_temp capt
357: where crpt.item_type = 'BASE'
358: and capt.source_type_code = 'DEDICATED'
359: and capt.required_item_id = crpt.inventory_item_id
403: sysdate arrival_date,
404: 0 distance,
405: csites.geometry,
406: 'OPEN'
407: from csp_required_parts_temp crpt;
408: log('site_dedicated_spares','Records inserted:'||sql%rowcount);
409: end loop;
410: open c_enough;
411: fetch c_enough into l_enough;
459: sysdate arrival_date,
460: 0 distance,
461: l_my_location,
462: 'OPEN'
463: from csp_required_parts_temp crpt;
464: log('my_inventory','inserted records:'||sql%rowcount);
465: end if;
466: end;
467:
487: and csi.condition_type = 'G'
488: and exists
489: (select 'x'
490: from mtl_onhand_quantities moq,
491: csp_required_parts_temp crpt
492: where moq.organization_id = csi.organization_id
493: and moq.subinventory_code = csi.secondary_inventory_name
494: and moq.inventory_item_id = crpt.alternate_item_id);
495:
561: when 'CLOSED' then 'CLOSED'
562: else 'OPEN'
563: end
564: from
565: csp_required_parts_temp crpt,
566: cac_sr_object_capacity csoc,
567: csp_sec_inventories csi
568: where (cr.organization_id <> l_organization_id
569: or nvl(cr.secondary_inventory,'-1') <> l_subinventory_code)
640: l_server_timezone_id,
641: cpp.timezone_id,
642: sysdate)),2,'OPEN','CLOSED')
643: from
644: csp_required_parts_temp crpt,
645: csp_planning_parameters cpp,
646: csp_open_closed_v cocv,
647: hz_locations hl
648: where cpp.stocking_site_type = 'UNMANNED'
737: l_server_timezone_id,
738: cpp.timezone_id,
739: sysdate)),2,'OPEN','CLOSED')
740: from
741: csp_required_parts_temp crpt,
742: csp_planning_parameters cpp,
743: csp_open_closed_v cocv,
744: hz_locations hl
745: where cpp.stocking_site_type = 'MANNED'
832: l_server_timezone_id,
833: cpp.timezone_id,
834: sysdate)),2,'OPEN','CLOSED')
835: from
836: csp_required_parts_temp crpt,
837: csp_planning_parameters cpp,
838: csp_open_closed_v cocv,
839: hz_locations hl
840: where cpp.organization_id = p_search_params.source_organization_id
869: crpt.alternate_item_id,
870: crpt.item_type item_type,
871: crpt.revision,
872: crpt.quantity
873: from csp_required_parts_temp crpt;
874:
875: begin
876: log('replenishment_source','Begin');
877: if p_search_params.manned_warehouses then
1278:
1279: -- bug # 12554921
1280: cursor c_get_temp_items is
1281: select inventory_item_id, revision, quantity
1282: from csp_required_parts_temp;
1283:
1284: l_master_org_id number;
1285: l_insert_rec_c number;
1286:
1371: l_required_parts := p_required_parts;
1372: end if;
1373: for i in 1..l_required_parts.count loop
1374: log('insert_parts','In l_required_parts.count loop i:'||i);
1375: log('insert_parts','Insert into csp_required_parts_temp');
1376: insert into csp_required_parts_temp(inventory_item_id,
1377: revision,
1378: alternate_item_id,
1379: quantity,item_type)
1372: end if;
1373: for i in 1..l_required_parts.count loop
1374: log('insert_parts','In l_required_parts.count loop i:'||i);
1375: log('insert_parts','Insert into csp_required_parts_temp');
1376: insert into csp_required_parts_temp(inventory_item_id,
1377: revision,
1378: alternate_item_id,
1379: quantity,item_type)
1380: values (l_required_parts(i).inventory_item_id,
1396: loop
1397: for r_rel_item in c_get_sup_items(r_req_items.inventory_item_id, l_master_org_id, 8)
1398: loop
1399: if r_rel_item.related_item_id <> r_req_items.inventory_item_id then
1400: insert into csp_required_parts_temp(inventory_item_id,
1401: revision,
1402: alternate_item_id,
1403: quantity,
1404: item_type)
1415: log('insert_parts','Inserted records:' || l_insert_rec_c);
1416:
1417: -- Substitutes
1418: log('insert_parts','insert substitutes into CRPT');
1419: insert into csp_required_parts_temp(inventory_item_id,
1420: revision,
1421: alternate_item_id,
1422: quantity,item_type)
1423: select crpt.inventory_item_id,
1426: crpt.quantity,
1427: 'SUBSTITUTE'
1428: from mtl_related_items_view mriv,
1429: mtl_parameters mp,
1430: csp_required_parts_temp crpt
1431: where mp.organization_id = l_organization_id
1432: and mriv.organization_id = mp.master_organization_id
1433: and mriv.inventory_item_id = crpt.inventory_item_id
1434: and mriv.relationship_type_id = 2
1436: and trunc(sysdate) between trunc(nvl(mriv.start_date,sysdate))
1437: and trunc(nvl(mriv.end_date,sysdate))
1438: and not exists(
1439: select 'x'
1440: from csp_required_parts_temp
1441: where alternate_item_id = mriv.related_item_id);
1442:
1443: log('insert_parts','Inserted records:'||sql%rowcount);
1444:
1450: loop
1451: for r_rel_item in c_get_sup_items(r_req_items.inventory_item_id, l_master_org_id, 2)
1452: loop
1453: if r_rel_item.related_item_id <> r_req_items.inventory_item_id then
1454: insert into csp_required_parts_temp(inventory_item_id,
1455: revision,
1456: alternate_item_id,
1457: quantity,
1458: item_type)
1473: end;
1474: begin
1475: if p_search_params.resource_id is null then
1476: l_called_from_charges := 'Y';
1477: delete from csp_required_parts_temp;
1478: delete from csp_available_parts_temp;
1479: end if;
1480: log('main','Begin');
1481: log('main','p_search_params.search_method:'||p_search_params.search_method);