XXDPF Shortages

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))