ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN DBE650AAED5DA722E05362FB09059FC8 select to_char(0) EBENE, -- Auflösungsebene in der STL to_char(:ASSEMBLY) ARTIKEL, -- Artikelnummer (select msit.description from mtl_system_items_b msi ,mtl_system_items_tl msit where 1 = 1 and msi.organization_id = 207 and msi.segment1 = :ASSEMBLY and msit.organization_id = msi.organization_id and msit.inventory_item_id = msi.inventory_item_id and msit.language = 'US') BEZEICHNUNG, -- Artikelbeschreibung null STL_MENGE, -- Menge in der Stückliste null FE_EK, -- Fertigen oder Einkaufen null DISPONENT, -- Planer aus Artikelstamm null BUYER, -- Einkäufer aus Artikelstamm Komponente null WBZ, -- Full Lead Time Komponente null WST, -- WIP Bereitstellungsart null BEDARF, -- gesamter Bedarf bis zum eingegebenen Datum null BESTAND, -- Summe Lagerbestand null EINKAUF, -- offene Bestellungen bis zum eingebenen Datum null MIN_EK_DATE, -- frühestest Promise oder Need by Date aus dem Einkauf null FERTIGUNG, -- offene Fertigungsaufträge bis zum eingegebenen Datum null from dual union all select detail.ebene EBENE, -- Auflösungsebene in der STL detail.component KOMPONENTE, -- Artikelnummer detail.comp_desc BEZEICHNUNG, -- Artikelbeschreibung detail.comp_quant STL_MENGE, -- Menge in der Stückliste detail.fe_ek FE_EK, -- Fertigen oder Einkaufen detail.planner DISPONENT, -- Planer aus Artikelstamm detail.buyer BUYER, -- Einkäufer aus Artikelstamm Komponente detail.wbz WBZ, -- Full Lead Time Komponente detail.wst WST, -- WIP Bereitstellungsart detail.bedarf BEDARF, -- gesamter Bedarf bis zum eingegebenen Datum detail.bestand BESTAND, -- Summe Lagerbestand detail.einkauf EINKAUF, -- offene Bestellungen bis zum eingebenen Datum detail.min_ek_date MIN_EK_DATE, -- frühestest Promise oder Need by Date aus dem Einkauf detail.fertigung FERTIGUNG, -- offene Fertigungsaufträge bis zum eingegebenen Datum detail.aufl from ( select lpad(' ' , level)||(level) EBENE, -- bom.assembly, -- bom.assy_desc, bom.item_num, bom.component, bom.planner, bom.buyer, bom.wbz, bom.comp_desc, bom.comp_quant, bom.fe_ek, bom.wst, bom.bedarf, bom.bestand, bom.einkauf, bom.min_ek_date, bom.fertigung, bom.aufl from ( select msi_assy.segment1 ASSEMBLY, substr(msit_assy.description , 1 , 60) ASSY_DESC, to_number(bic.item_num) ITEM_NUM, msi_comp.segment1 COMPONENT, msi_comp.planner_code PLANNER, msi_comp.full_lead_time WBZ, (select papf.last_name from po_agents pa, per_all_people_f papf where 1 = 1 and pa.agent_id = msi_comp.buyer_id and papf.person_id = pa.agent_id and rownum < 2) BUYER, substr(msit_comp.description , 1 , 30) COMP_DESC, bic.component_quantity COMP_QUANT, decode(msi_comp.planning_make_buy_code , 1 , 'FE' , 2 , 'EK') FE_EK, (select meaning from fnd_lookup_values where lookup_type = 'WIP_SUPPLY' and language = 'US' and lookup_code = nvl(bic.wip_supply_type , msi_comp.wip_supply_type)) WST, -- Berechnung ob Auflösen JA / NEIN case when (select nvl(sum(moqd.primary_transaction_quantity) , 0) from mtl_onhand_quantities_detail moqd where moqd.organization_id = msi_comp.organization_id and moqd.inventory_item_id = msi_comp.inventory_item_id) - (select nvl(sum(md.primary_uom_quantity - md.completed_quantity) , 0) from mtl_demand md where md.inventory_item_id = msi_comp.inventory_item_id and md.organization_id = msi_comp.organization_id and md.requirement_date <= nvl(:TERMIN , sysdate + 1000)) - (select nvl(sum(wro.required_quantity - wro.quantity_issued) , 0) from wip_requirement_operations wro, wip_discrete_jobs wdj where wro.inventory_item_id = msi_comp.inventory_item_id and wro.organization_id = msi_comp.organization_id and wro.operation_seq_num > 0 and wdj.wip_entity_id = wro.wip_entity_id and wdj.status_type < 5 and wro.date_required <= nvl(:TERMIN , sysdate + 1000)) - nvl(:MENGE , 0) >= 0 then 'N' else 'Y' end AUFL, -- Ende der Berechnung (select nvl(sum(wro.required_quantity - wro.quantity_issued) , 0) from wip_requirement_operations wro, wip_discrete_jobs wdj where wro.inventory_item_id = msi_comp.inventory_item_id and wro.organization_id = msi_comp.organization_id and wro.operation_seq_num > 0 and wdj.wip_entity_id = wro.wip_entity_id and wdj.status_type < 5 and wro.date_required <= nvl(:TERMIN , sysdate + 1000)) + (select nvl(sum(md.primary_uom_quantity - md.completed_quantity) , 0) from mtl_demand md where md.inventory_item_id = msi_comp.inventory_item_id and md.organization_id = msi_comp.organization_id and md.requirement_date <= nvl(:TERMIN , sysdate + 1000)) + nvl(:MENGE , 0) BEDARF, (select nvl(sum(moqd.primary_transaction_quantity) , 0) from mtl_onhand_quantities_detail moqd where moqd.organization_id = msi_comp.organization_id and moqd.inventory_item_id = msi_comp.inventory_item_id) BESTAND, (select nvl(sum(pll.quantity - pll.quantity_received - pll.quantity_cancelled) , 0) from po_lines_all pl, po_line_locations_all pll where pl.item_id = msi_comp.inventory_item_id and pll.po_line_id = pl.po_line_id and pll.ship_to_organization_id = msi_comp.organization_id and pll.closed_code = 'OPEN' and pll.shipment_type in ('STANDARD' , 'BLANKET') and nvl(pll.promised_date , pll.need_by_date) <= nvl(:TERMIN , sysdate + 1000)) EINKAUF, (select min(nvl(pll.promised_date , pll.need_by_date)) from po_lines_all pl, po_line_locations_all pll where pl.item_id = msi_comp.inventory_item_id and pll.po_line_id = pl.po_line_id and pll.ship_to_organization_id = msi_comp.organization_id and pll.closed_code = 'OPEN' and nvl(pll.promised_date , pll.need_by_date) <= nvl(:TERMIN , sysdate + 1000)) MIN_EK_DATE, (select nvl(sum(wdj.net_quantity - wdj.quantity_completed) , 0) from wip_discrete_jobs wdj where wdj.organization_id = msi_comp.organization_id and wdj.primary_item_id = msi_comp.inventory_item_id and wdj.status_type < 5 and wdj.scheduled_completion_date <= nvl(:TERMIN , sysdate + 1000)) FERTIGUNG from bom_bill_of_materials bbom, bom_inventory_components bic, mtl_system_items_b msi_assy, mtl_system_items_tl msit_assy, mtl_system_items_b msi_comp, mtl_system_items_tl msit_comp where bbom.organization_id = 207 and bic.bill_sequence_id = bbom.bill_sequence_id and bic.disable_date is null and msi_assy.organization_id = bbom.organization_id and msi_assy.inventory_item_id = bbom.assembly_item_id and msit_assy.organization_id = msi_assy.organization_id and msit_assy.inventory_item_id = msi_assy.inventory_item_id and msit_assy.language = 'US' and msi_comp.organization_id = msi_assy.organization_id and msi_comp.inventory_item_id = bic.component_item_id and msit_comp.organization_id = msi_comp.organization_id and msit_comp.inventory_item_id = msi_comp.inventory_item_id and msit_comp.language = 'US' ) BOM start with bom.assembly = :ASSEMBLY connect by prior bom.component = bom.assembly order siblings by bom.item_num ) DETAIL where 2=2 and detail.aufl = decode(nvl(upper(:ALLES) , 'Y') , 'N' , 'Y' , detail.aufl) and detail.wst != decode(nvl(upper(:MIT_BULK) , 'Y') , 'N' , 'Bulk' , 'Y' , 'xxxx') and detail.ebene <= to_number(nvl(:MAX_LEVEL , 1000)) Y US XXDPF Shortages Bill of Material for a given Assembly with Supply and Demand from ERP Instance 2=2 :alles :assembly :max_level :menge :mit_bulk :termin 1 10 :assembly Char Y US Assembly Assembly 2 20 :alles LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Include all Include All 3 30 :max_level Number 10 US max Level Max Level 4 40 :menge Number US Quantity Add QTY 5 50 :termin Date US Date Date 6 60 :mit_bulk LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US with Bulk With Bulk