FUNCTION GET_ADV62(p_packid IN NUMBER) RETURN XMLTYPE
is
  ResXML XMLTYPE;
BEGIN
  with
  pk as
   (select p.id, p.accounting_year, p.accounting_quarter, p.accounting_descr, p.pack_number, p.form_type_id,
           p.information_types_id, p.readjustment_kinds_id, p.rank_types_id,
           p.auxiliary_tariff_codes_id, p.territorial_code_id, p.parent_id,
           p.action_date, p.date_from, p.date_to,
           ft.form_code, ft.form_code_7_0, it.code_7_0 itcode, rk.code rkcode,
           rt.code rtcode, rt.code_7_0 rtcode_7_0, atc.code atccode,
           (select count(pp.id) from packs pp where pp.parent_id = p.id and pp.information_types_id = 1) pack_count,
           (select count(pp.id) from packs pp where pp.parent_id = p.id and pp.information_types_id = 3) pack_count_corr,           
           (select count(ie.id) from insurance_experiences ie where ie.pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 1)) workers_count,
           (select count(ie.id) from insurance_experiences ie where ie.pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 3)) workers_count_corr,
       (select TRIM(to_char(sum(NVL(INSURANCE_PART, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 1)) INSURANCE_PART,
       (select TRIM(to_char(sum(NVL(ACCUMULATION_PART, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 1)) ACCUMULATION_PART,
       (select TRIM(to_char(sum(NVL(INSURANCE_PART_PAID, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 1)) INSURANCE_PART_PAID,
       (select TRIM(to_char(sum(NVL(ACCUMULATION_PART_PAID, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 1)) ACCUMULATION_PART_PAID,

       (select TRIM(to_char(sum(NVL(INSURANCE_PART, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 3)) INSURANCE_PART_CORR,
       (select TRIM(to_char(sum(NVL(ACCUMULATION_PART, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 3)) ACCUMULATION_PART_CORR,
       (select TRIM(to_char(sum(NVL(INSURANCE_PART_PAID, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 3)) INSURANCE_PART_PAID_CORR,
       (select TRIM(to_char(sum(NVL(ACCUMULATION_PART_PAID, 0)),'99999999990')) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.parent_id = p.id and pp.information_types_id = 3)) ACCUMULATION_PART_PAID_CORR
             
      from packs p, form_types ft, information_types it,
           readjustment_kinds rk, rank_types rt, auxiliary_tariff_codes atc, personnel.territorial_code tc
     where p.id = p_packid
       and p.form_type_id = ft.id (+)
       and p.information_types_id = it.id (+)
       and p.readjustment_kinds_id = rk.id (+)
       and p.rank_types_id = rt.id (+)
       and p.auxiliary_tariff_codes_id = atc.id (+)
       and p.territorial_code_id = tc.id (+)       
       order by rt.code),
  inf as
  (
    select rownum rn, dd.* from (
        select 2 tp, rtcode, sum(workers_count) workers_count,
               TRIM(to_char(sum(insurance_part),'99999999990')) insurance_part,
               TRIM(to_char(sum(accumulation_part),'99999999990')) accumulation_part,
               TRIM(to_char(sum(insurance_part_paid),'99999999990')) insurance_part_paid,
               TRIM(to_char(sum(accumulation_part_paid),'99999999990')) accumulation_part_paid, '' xml_name
        from
        (
        select rt.code rtcode,         
          (select count(ie.id) from insurance_experiences ie where ie.pack_id in (select pp.id from packs pp where pp.id = p.id and pp.rank_types_id = rt.id)) workers_count,
          (select sum(NVL(INSURANCE_PART, 0)) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.id = p.id  and pp.rank_types_id = rt.id)) INSURANCE_PART,
          (select sum(NVL(ACCUMULATION_PART, 0)) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.id = p.id  and pp.rank_types_id = rt.id)) ACCUMULATION_PART,
          (select sum(NVL(INSURANCE_PART_PAID, 0)) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.id = p.id  and pp.rank_types_id = rt.id)) INSURANCE_PART_PAID,
          (select sum(NVL(ACCUMULATION_PART_PAID, 0)) from insurance_experiences where pack_id in (select pp.id from packs pp where pp.id = p.id  and pp.rank_types_id = rt.id)) ACCUMULATION_PART_PAID
                         
            from packs p, rank_types rt
           where p.parent_id = p_packid
             and p.rank_types_id = rt.id (+)
             and p.information_types_id = 1
           group by p.id, rt.code, rt.id
        )
        group by rtcode
    union all       
      select 1 tp, code, workers_count, INSURANCE_PART,
       ACCUMULATION_PART, INSURANCE_PART_PAID, ACCUMULATION_PART_PAID, xml_name
    from(
    select p.id, rt.code, rt.name, rt.code_7_0,
       (select count(*) from insurance_experiences where pack_id = p.id) workers_count,
       (select TRIM(to_char(sum(NVL(INSURANCE_PART, 0)),'99999999990')) from insurance_experiences where pack_id = p.id) INSURANCE_PART,
       (select TRIM(to_char(sum(NVL(ACCUMULATION_PART, 0)),'99999999990')) from insurance_experiences where pack_id = p.id) ACCUMULATION_PART,
       (select TRIM(to_char(sum(NVL(INSURANCE_PART_PAID, 0)),'99999999990')) from insurance_experiences where pack_id = p.id) INSURANCE_PART_PAID,
       (select TRIM(to_char(sum(NVL(ACCUMULATION_PART_PAID, 0)),'99999999990')) from insurance_experiences where pack_id = p.id) ACCUMULATION_PART_PAID,
       max('PFR-700-Y-'||p.accounting_year||'-ORG-086-004-000027-DCK-'||LPAD(p.pack_number, 5, '0')||'-DPT-000000-DCK-00000.XML') xml_name   
       from packs p, rank_types rt
       where p.parent_id = p_packid
       and p.rank_types_id = rt.id
       and p.information_types_id = 1       
       group by p.id, p.pack_number, rt.code, rt.name, rt.code_7_0
--       order by p.pack_number))
       order by rt.code)
       order by rtcode, tp) dd   
  ),
 inf2 as (
 select --TRIM(to_char(sum(NVL(INSURANCE_PART, 0)),'99999999990'))
  p.id, p.xml_file_name, rt.code, rt.name, rt.code_7_0, ie.worker_id,
--(select person_id from personnel.workers where id = ie.worker_id) person_id,
( select sum(NVL(ie.insurance_part,0)) - sum(NVL(i.insurance_part,0))
  from insurance_experiences i, packs pk
 where i.pack_id = pk.id
  and pk.form_type_id in (162, 163) 
  and pk.information_types_id <> 3
  and (pk.accounting_year = (select accounting_year_corr from packs where id = p_packid) and
      (pk.accounting_quarter = (select accounting_quarter_corr from packs where id = p_packid)))
  and (select person_id from personnel.workers where id = ie.worker_id) = (select person_id from personnel.workers where id = i.worker_id)
) insurance_part,
( select sum(NVL(ie.accumulation_part,0)) - sum(NVL(i.accumulation_part,0))
  from insurance_experiences i, packs pk
 where i.pack_id = pk.id
  and pk.form_type_id in (162, 163) 
  and pk.information_types_id <> 3
  and (pk.accounting_year = (select accounting_year_corr from packs where id = p_packid) and
      (pk.accounting_quarter = (select accounting_quarter_corr from packs where id = p_packid)))
  and (select person_id from personnel.workers where id = ie.worker_id) = (select person_id from personnel.workers where id = i.worker_id)
) accumulation_part         
 from insurance_experiences ie, packs p, rank_types rt
where ie.pack_id = p.id
  and p.parent_id = p_packid
  and p.information_types_id = 3
  and p.rank_types_id = rt.id
),
inf3 as
  (select rownum rn, yy.* from
  (select 1 tp, qq.* from (select 
          max(inf2.code) code, max(inf2.name) name, max(inf2.code_7_0) code_7_0, max(inf2.xml_file_name) xml_file_name,         
          count(inf2.worker_id) workers_count,
          TRIM(to_char(sum(NVL(inf2.ACCUMULATION_PART, 0)),'99999999990')) ACCUMULATION_PART,                 
            TRIM(to_char(sum(NVL(inf2.INSURANCE_PART, 0)),'99999999990')) INSURANCE_PART                 
   from inf2
   group by inf2.id, inf2.code, inf2.name, inf2.code_7_0, inf2.xml_file_name) qq         

   union
   
    select 2 tp, qq.* from (select
          inf2.code code, max(inf2.name) name, max(inf2.code_7_0) code_7_0, '' xml_file_name,
          count(inf2.worker_id) workers_count,
          TRIM(to_char(sum(NVL(inf2.ACCUMULATION_PART, 0)),'99999999990')) ACCUMULATION_PART,                 
          TRIM(to_char(sum(NVL(inf2.INSURANCE_PART, 0)),'99999999990')) INSURANCE_PART                 
   from inf2
   group by inf2.code) qq             
 ) yy
 order by code, tp )
 
    select
    XMLelement("ОПИСЬ_СВЕДЕНИЙ_ПЕРЕДАВАЕМЫХ_СТРАХОВАТЕЛЕМ",
         XMLelement("НомерВпачке", '2'),
         XMLelement("Страхователь", GET_ORGANIZATION),
         XMLElement("ОтчетныйПериод",
             XMLelement("Квартал", pk.accounting_quarter),
             XMLelement("Год", pk.accounting_year),
             XMLelement("Название", pk.accounting_descr)       
         ),
         XMLelement("ЧислоИсходныхПачек", pk.pack_count),
         XMLelement("ЧислоКорректирующихПачек", pk.pack_count_corr),                 
         XMLelement("ДатаЗаполнения", to_char(pk.action_date, 'dd.mm.yyyy')),
         (select XMLConcat(XMLAgg(
            XMLelement("СведенияОбИсходныхСведениях",
                XMLelement("ПорядковыйНомер", inf.rn),
                XMLelement("ТипСтрокиОсведениях", case when tp = 1 then 'ПО ПАЧКЕ' else 'ПО КАТЕГОРИИ' end),
                XMLelement("КодКатегории", inf.rtcode),
                XMLelement("СуммаВзносовНаСтраховую",
                    XMLelement("Начислено", inf.INSURANCE_PART),
                    XMLelement("Уплачено", inf.INSURANCE_PART_PAID)
                ),
                XMLelement("СуммаВзносовНаНакопительную",
                    XMLelement("Начислено", inf.ACCUMULATION_PART),
                    XMLelement("Уплачено", inf.ACCUMULATION_PART_PAID)
                ),
                XMLelement("КоличествоЗЛ", inf.workers_count),
                case when tp = 1 then XMLelement("ИмяФайла", inf.xml_name) else null end
            )       
         ))
          from inf
         ),
         XMLelement("СведенияОбИсходныхСведениях",
             XMLelement("ПорядковыйНомер", (select count(*)+1 from inf)),
             XMLelement("ТипСтрокиОсведениях", 'ПО СТРАХОВАТЕЛЮ'),
             XMLelement("СуммаВзносовНаСтраховую",
                 XMLelement("Начислено", pk.INSURANCE_PART),
                 XMLelement("Уплачено", pk.INSURANCE_PART_PAID)
             ),
             XMLelement("СуммаВзносовНаНакопительную",
                 XMLelement("Начислено", pk.ACCUMULATION_PART),
                 XMLelement("Уплачено", pk.ACCUMULATION_PART_PAID)
             ),
             XMLelement("КоличествоЗЛ", pk.workers_count)
         ),
         
         case when (select count(*) from inf2) > 0 then
         XMLConcat(
         (select XMLConcat(XMLAgg(
            XMLelement("СведенияОкорректирующихСведениях",
                XMLelement("ПорядковыйНомер", inf3.rn),
                XMLelement("ТипСтрокиОсведениях", case when tp = 1 then 'ПО ПАЧКЕ' else 'ПО КАТЕГОРИИ' end),
                XMLelement("КодКатегории", inf3.code),
                XMLelement("ДоначисленоНаСтраховую",
                    inf3.INSURANCE_PART
                ),
                XMLelement("ДоначисленоНаНакопительную",
                    inf3.ACCUMULATION_PART
                ),
                XMLelement("КоличествоЗЛ", inf3.workers_count),
                case when tp = 1 then XMLelement("ИмяФайла", inf3.xml_file_name) else null end
            )       
         ))
          from inf3
         ),

         XMLelement("СведенияОкорректирующихСведениях",
             XMLelement("ПорядковыйНомер", (select count(*)+1 from inf3)),
             XMLelement("ТипСтрокиОсведениях", 'ПО СТРАХОВАТЕЛЮ'),
             XMLelement("ДоначисленоНаСтраховую",
                 (select TRIM(to_char(sum(NVL(inf2.INSURANCE_PART, 0)),'99999999990')) from inf2)
             ),
             XMLelement("ДоначисленоНаНакопительную",
                 (select TRIM(to_char(sum(NVL(inf2.ACCUMULATION_PART, 0)),'99999999990')) from inf2)
             ),
             XMLelement("КоличествоЗЛ", (select count(inf2.worker_id) from inf2))
         ) 
         ) end
    )

   
  into ResXML
  from pk;

  RETURN ResXML;

END;