CREATE OR REPLACE FUNCTION public.sp_f003moload(
	fxml xml)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
BEGIN

Drop Table If Exists MO;
Create Temp Table MO
(
mokey varchar
 , tf_okato varchar
 , mcod varchar
 , nam_mop varchar
 , nam_mok varchar
 , inn varchar
 , ogrn varchar
 , kpp varchar
 , index_j varchar
 , addr_j varchar
 , okopf varchar
 , vedpri int 
 , org int
 , im_ruk varchar 
 , ot_ruk varchar
 , phone varchar 
 , fax varchar 
 , e_mail varchar 
 , d_begin date  
 , d_end date 
 , www varchar 
 , d_edit date 
);	

insert into mo
(mokey 
 , tf_okato 
 , mcod 
 , nam_mop 
 , nam_mok 
 , inn 
 , ogrn 
 , kpp 
 , index_j 
 , addr_j 
 , okopf 
 , vedpri  
 , org 
 , im_ruk  
 , ot_ruk 
 , phone  
 , fax  
 , e_mail  
 , d_begin 
 , d_end  
 , www  
 , d_edit )
SELECT 
		 cast(newid() as varchar)
		,trim(xpath('/medCompany/tf_okato1', Table_Assn_List)::varchar, '{}')
		,trim(xpath('/medCompany/mcod1', Table_Assn_List)::varchar, '{}')
		,trim(xpath('/medCompany/nam_mop1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/nam_mok1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/inn1', Table_Assn_List)::varchar, '{}')
		,trim(xpath('/medCompany/Ogrn1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/KPP1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/jurAddress1/index_j1',Table_Assn_List)::varchar , '{}')
		,trim(xpath('/medCompany/jurAddress1/addr_j1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/okopf1', Table_Assn_List) ::varchar, '{}')
		,case 
			when len(trim(xpath('/medCompany/vedpri1', Table_Assn_List), '{}'))=0 then null 
			else len(trim(xpath('/medCompany/vedpri1', Table_Assn_List), '{}')) 
			end 
		,trim(xpath('/medCompany/org1', Table_Assn_List)::varchar,'{}')::int
		,trim(xpath('/medCompany/fam_ruk1', Table_Assn_List)::varchar, '{}')
		,trim(xpath('/medCompany/im_ruk1', Table_Assn_List)::varchar, '{}')
		,trim(xpath('/medCompany/ot_ruk1',Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/phone1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/fax1', Table_Assn_List) ::varchar, '{}')
		,trim(xpath('/medCompany/e_mail1', Table_Assn_List)::varchar, '{}')
		,case 
			when len( trim(xpath('/medCompany/medInclude1/d_begin1',Table_Assn_List )::varchar, '{}')) < 10  then null 
			else to_char(trim(xpath('/medCompany/medInclude1/d_begin1',Table_Assn_List )::varchar,'{}'),'DD.MM.YYYY') 
			end 
		,case 
			when len( trim(xpath('./medInclude1/d_end1', Table_Assn_List)::varchar,'{}')) < 10  then null 
			else to_char(trim(xpath('/medCompany/medInclude1/d_end1', Table_Assn_List)::varchar,'{}'),'DD.MM.YYYY')  
			end 
		,trim(xpath('/medCompany/www1',Table_Assn_List ) ::varchar, '{}')
		,case when len( trim(xpath('./d_edit1', Table_Assn_List)::varchar,'{}')) < 10  then null 
			else to_char(trim(xpath('/medCompany/d_edit1', Table_Assn_List)::varchar,'{}'),'DD.MM.YYYY')  
			end 
		from 
		(
		select	unnest( xpath('/packet/medCompany', e.xmlEnt) )  Table_Assn_List
		,	e.xmlEnt  Root_Node 
		from FXml e
		) q;

Drop Table If Exists mo_license;
create temp table mo_license
(
mcod varchar
,n_doc varchar
,d_start date
,date_e date 
);

insert into mo_license
(
mcod 
,n_doc 
,d_start 
,date_e  
)
SELECT
		trim(xpath('/doc/mcod1',Table_Assn_List)::varchar,'{}')
		,trim(xpath('./n_doc1',Table_Assn_List)::varchar,'{}')
		,case 
			when len(trim(xpath('/doc/d_start1',Table_Assn_List)::varchar,'{}')) < 10 then null 
			else to_char(trim(xpath('/doc/d_start1',Table_Assn_List)::varchar,'{}'),'DD.MM.YYYY') 
			end as d_start
		,case 
			when len(trim(xpath('/doc/date_e1',Table_Assn_List)::varchar,'{}')) < 10 then null 
			else to_char(trim(xpath('/doc/date_e1',Table_Assn_List)::varchar,'{}'),'DD.MM.YYYY') 
			end as date_e
		from 
		(
		select	unnest( xpath('/packet/medCompany/doc', e.xmlEnt) )  Table_Assn_List
		,	e.xmlEnt  Root_Node 
		from FXml e
		) q;

Drop Table If Exists mo_mp;
create temp table mo_mp
(
mcod varchar
,n_doc varchar
,mp varchar
);

insert into mo_mp
(
mcod 
,n_doc 
,mp 
)
SELECT
		trim(xpath('/packet/medCompany/doc/mp/mcod1',Table_Assn_List)::varchar,'{}')
		,trim(xpath('/packet/medCompany/doc/mp/n_doc1',Table_Assn_List) ::varchar,'{}')
		,trim(xpath('/packet/medCompany/doc/mp',Table_Assn_List)::varchar,'{}')
		from 
		(
		select	unnest( xpath('/packet/medCompany/doc/mp', e.xmlEnt) )  Table_Assn_List
		,	e.xmlEnt  Root_Node 
		from FXml e
		) q;

   UPDATE t1
   SET t1.TF_OKATO = t2.TF_OKATO
      ,t1.NAM_MOP = t2.NAM_MOP --<NAM_MOP, varchar(250),>
      ,t1.NAM_MOK = t2.NAM_MOK --<NAM_MOK, varchar(250),>
      ,t1.INN = t2.INN --<INN, varchar(12),>
      ,t1.OGRN = t2.OGRN --<OGRN, varchar(15),>
      ,t1.KPP = coalesce(t2.KPP, '') --<KPP, varchar(9),>
      ,t1.INDEX_J = t2.INDEX_J --<INDEX_J, varchar(6),>
      ,t1.ADDR_J = t2.ADDR_J --<ADDR_J, varchar(254),>
      ,t1.OKOPF = tt1.O005OkopfID --<OKOPF, uuid,>
      ,t1.VEDPRI = t2.VEDPRI -- <VEDPRI, int,>
      ,t1.ORG = t2.ORG --<ORG, int,>
      ,t1.FAM_RUK = t2.FAM_RUK --<FAM_RUK, varchar(40),>
      ,t1.IM_RUK = coalesce(t2.IM_RUK, '') --<IM_RUK, varchar(40),>
      ,t1.OT_RUK = coalesce(t2.OT_RUK, '') --<OT_RUK, varchar(40),>
      ,t1.PHONE = coalesce(t2.PHONE, '') --<PHONE, varchar(40),>
      ,t1.FAX = coalesce(t2.FAX, '') --<FAX, varchar(40),>
      ,t1.E_MAIL = coalesce(t2.E_MAIL, '') --<E_MAIL, varchar(50),>
      ,t1.WWW = t2.WWW --<WWW, varchar(100),>
      ,t1.D_BEGIN = t2.D_BEGIN --<D_BEGIN, datetime,>
      ,t1.D_END = t2.D_END --<D_END, datetime,>
      ,t1.D_EDIT = t2.D_EDIT --<D_EDIT, datetime,>
 FROM FIES.T_MO t1
 JOIN mo t2 ON t1.MCOD = t2.MCOD
 left join FIES.T_O005_OKOPF tt1 on tt1.KOD = t2.OKOPF;
 
INSERT INTO FIES.T_MO
           (TF_OKATO
           ,MCOD
           ,NAM_MOP
           ,NAM_MOK
		   ,NAM_MFF
           ,INN
           ,OGRN
           ,KPP
           ,INDEX_J
           ,ADDR_J
           ,OKOPF
           ,VEDPRI
           ,ORG
           ,FAM_RUK
           ,IM_RUK
           ,OT_RUK
           ,PHONE
           ,FAX
           ,E_MAIL
           ,WWW
		   ,D_BEGIN           
           ,D_EDIT
           ,IsActive
		   ,MoOwnershipType)
    
select      TF_OKATO
           ,MCOD
           ,NAM_MOP
           ,NAM_MOK
		   ,NAM_MOK
           ,coalesce(INN,'')
           ,coalesce(OGRN,'')
           ,coalesce(KPP, '')
           ,INDEX_J
           ,ADDR_J
           ,tt1.O005OkopfID 
           ,VEDPRI
           ,ORG
           ,FAM_RUK
           ,coalesce(IM_RUK, '')
           ,coalesce(OT_RUK, '')
           ,coalesce(PHONE, '')
           ,coalesce(FAX, '')
           ,coalesce(E_MAIL, '')
           ,WWW
		   ,D_BEGIN
           ,D_EDIT
           ,0
		   ,0
from MO t1
 left join FIES.T_O005_OKOPF tt1 on tt1.KOD = t1.OKOPF
where t1.MCOD not in (select mcod from FIES.T_MO tt);
		
INSERT INTO FIES.T_MO_LICENSE(
			MolicenseID
			,Mo
			,N_DOC
			,D_START
			,DATA_E)
SELECT
			newid()
			,t1.MCOD
			,t1.n_doc
			,t1.d_start
			,t1.date_e
FROM 
	mo_license t1
LEFT JOIN FIES.T_MO_LICENSE t2 on (t1.n_doc=t2.N_DOC and t1.mcod=t2.Mo)
WHERE t2.MolicenseID is null and t1.n_doc is not null and t1.d_start is not null;

DROP TABLE mo_mp;
DROP TABLE MO;
DROP TABLE mo_license;
end
;$BODY$;
