Query for CRG problematic manufacture items (with input item amounts)

Query for CRG problematic manufacture items

with Input_Item_Amount as
 (select case t.Coa_Id
           when 101336 then
            'M'
           when 101356 then
            'P'
           when 101360 then
            'G'
         end Inventory_Kind,
         k.Source_Id Manufacture_Id,
         Mk_Util.Find_Ref_Id(t.Ref_Set, 1014) Product_Id,
         sum(Mk_Util.Calc_Amount(i_Company_Id       => t.Company_Id,
                                 i_Filial_Id        => t.Filial_Id,
                                 i_From_Currency_Id => t.Currency_Id,
                                 i_To_Currency_Id   => (select w.Currency_Id
                                                          from Mfg_Manufactures w
                                                         where w.Company_Id = k.Company_Id
                                                           and w.Manufacture_Id = k.Source_Id),
                                 i_Rate_Date        => t.Trans_Date,
                                 i_Amount           => t.Amount)) Amount
    from Mk_Ledger t
    join Mk_Journals k
      on k.Company_Id = t.Company_Id
     and k.Journal_Id = t.Journal_Id
   where t.Company_Id = 1081
     and t.Dc_Sign = -1
     and Mk_Util.Find_Ref_Id(t.Ref_Set, 1014) is not null
     and k.Source_Table = 'MFG_MANUFACTURES'
   group by t.Coa_Id, k.Source_Id, Mk_Util.Find_Ref_Id(t.Ref_Set, 1014)),
Manufacture_Items as
 (select 'I' Item_Kind,
         Mi.Company_Id,
         Mi.Input_Item_Id Item_Id,
         Mi.Manufacture_Id,
         Mi.Product_Id,
         Mi.Inventory_Kind,
         Mi.Quantity,
         (select w.Amount
            from Input_Item_Amount w
           where w.Manufacture_Id = Mi.Manufacture_Id
             and w.Inventory_Kind = Mi.Inventory_Kind
             and w.Product_Id = Mi.Product_Id) Amount
    from Mfg_Manufacture_Inputs Mi
   where Mi.Company_Id = 1081
  union all
  select 'O' Item_Kind,
         Mo.Company_Id,
         Mo.Output_Item_Id Item_Id,
         Mo.Manufacture_Id,
         Mo.Product_Id,
         Mo.Inventory_Kind,
         Mo.Quantity,
         Mo.Amount
    from Mfg_Manufacture_Outputs Mo
   where Mo.Company_Id = 1081)
select t.Filial_Id,
       t.Input_Date,
       t.Output_Date,
       (select w.Name
          from Mk_Currencies w
         where w.Company_Id = Mi.Company_Id
           and w.Currency_Id = t.Currency_Id) Currency_Name,
       case t.Status
         when 'D' then
          'Черновик'
         when 'W' then
          'В ожидании'
         when 'C' then
          'Завершено'
       end Status,
       Mi.Product_Id,
       (select w.Name
          from Mr_Products w
         where w.Company_Id = Mi.Company_Id
           and w.Product_Id = Mi.Product_Id) Product_Name,
       Mi.Inventory_Kind,
       case Mi.Inventory_Kind
         when 'G' then
          'Товар'
         when 'M' then
          'Сырье'
         when 'P' then
          'Продукция'
         when 'E' then
          'Рекламное оборудование'
       end Inv_Kind,
       case Mi.Item_Kind
         when 'I' then
          'Исходные материалы'
         when 'O' then
          'Результат производства'
       end Item_Kind,
       t.Manufacture_Id,
       Mi.Item_Id,
       Mi.Quantity,
       Decode(Mi.Item_Kind, 'I', Mi.Amount, null) Input_Amount,
       t.c_Amount Total_Amount,
       Decode(Mi.Item_Kind, 'O', Mi.Amount, null) Output_Amount,
       null New_Amount
  from Mfg_Manufactures t
  join Manufacture_Items Mi
    on Mi.Company_Id = t.Company_Id
   and Mi.Manufacture_Id = t.Manufacture_Id
 where t.Company_Id = 1081
   and t.Status = 'C'
   and exists (select 1
          from Manufacture_Items w
         where w.Company_Id = t.Company_Id
           and w.Item_Kind = 'O'
           and w.Manufacture_Id = t.Manufacture_Id
           and w.Amount = 0)
 order by Filial_Id, t.Manufacture_Id desc, Input_Date desc, Mi.Item_Kind desc