SELECT DISTINCT
       line.contract,
       line.part_no,
       -- MaCoCz ZPA000118 Begin
       --Decode(type_, '5' , NULL, line.component_part) component_part,
       -- MaCoCz ZPA000118 End
       line.eng_chg_level,
       line.alternative_no,
       Shop_Ord_Code_API.Decode(line.bom_type) bom_type_client,
       line.bom_type             bom_type_db,
       line.eff_phase_in_date,
       line.eff_phase_out_date,
       line.rowtype,
       line.alternative_no,
       -- MaCoCz ZPA000118 Begin
       --Decode(type_, '5' , NULL, line.qty_per_assembly) qty_per_assembly,
       -- MaCoCz ZPA000118 End
       -- MaCoCz ZPA000118 - 939937 Begin
       line.line_item_no,
       -- MaCoCz ZPA000118 - 939937 End
       alt.rowstate
      FROM   MANUF_STRUCTURE_TAB line, MANUF_STRUCT_ALTERNATE_TAB alt, INVENTORY_PART inv
         WHERE  alt.rowstate NOT IN ('Obsolete','Cancelled')
         AND    alt.alternative_no = line.alternative_no
         AND    alt.bom_type = line.bom_type
         AND    alt.eng_chg_level = line.eng_chg_level
         AND    alt.contract = line.contract
         AND    alt.part_no = line.part_no
         -- MaCoCz ZPA000118 Begin
         AND    line.component_part = inv.part_no
         AND    line.contract = inv.contract
         -- MaCoCz ZPA000118 End
         --AND    NVL(line.eff_phase_out_date, TO_DATE('31.12.99','DD.MM.YY')) > TO_DATE('16.11.23', 'DD.MM.YY')
         -- MaCoCz ZPA000118 - 832971 Begin
         AND line.part_no >=
         (select min (t.part_no) from MANUF_STRUCTURE_TAB t where t.part_no like NVL('A2903584', line.part_no))
         AND line.part_no <=
         (select max (t.part_no) from MANUF_STRUCTURE_TAB t where t.part_no like NVL('A2903584', line.part_no))
         -- MaCoCz ZPA000118 - 832971 End
         AND    line.part_no  = NVL('A2903584', line.part_no)
         AND    line.contract = 'ZPA'
         -- MaCoCz ZPA000118 Begin
        --AND line.component_part = nvl (part_out_,line.component_part)
        AND    line.component_part >= (select  min (t.component_part) from MANUF_STRUCTURE_TAB t where
               t.part_no = line.part_no
               AND t.contract = line.contract
               AND t.bom_type = line.bom_type
               AND t.eng_chg_level = line.eng_chg_level
               AND t.alternative_no = line.alternative_no
               AND t.component_part like NVL(NULL, t.component_part))
        AND line.component_part <= (select  max (t.component_part) from MANUF_STRUCTURE_TAB t where
               t.part_no = line.part_no
               AND t.contract = line.contract
               AND t.bom_type = line.bom_type
               AND t.eng_chg_level = line.eng_chg_level
               AND t.alternative_no = line.alternative_no
               AND t.component_part like NVL(NULL, t.component_part))
         -- MaCoCz ZPA000118 End
         -- MaCoCz ZPA000118 Begin
         AND NVL(inv.description, ' ') like NVL(NULL, '%')
         AND NVL(inv.type_designation, ' ') like NVL(NULL, '%')
         AND NVL(inv.dim_quality, ' ') like NVL(NULL, '%')
         -- MaCoCz ZPA000118 End
         ORDER BY line.contract, line.part_no, DECODE(line.bom_type , 'T', to_number(substr(line.eng_chg_level,2)), to_number(line.eng_chg_level)), bom_type_client;