DROP FUNCTION IF EXISTS sp_SchetBulkLoad_karelia(uuid);

CREATE OR REPLACE FUNCTION sp_SchetBulkLoad_karelia ("FileID" uuid DEFAULT NULL::uuid) RETURNS void
AS
    $BODY$
BEGIN
    RAISE NOTICE 'Обновление идентификаторов %', now()::TIMESTAMP;
    UPDATE
        idb.TEMP_ZGLV
    SET
        ZglvID = gen_random_uuid();
    UPDATE
        idb.TEMP_SCHET
    SET
        SchetID = gen_random_uuid();
    UPDATE
        idb.TEMP_ZAP
    SET
        ZapID = gen_random_uuid();
    UPDATE
        idb.TEMP_PACIENT
    SET
        PacientID = gen_random_uuid();
    UPDATE
        idb.TEMP_Z_SL
    SET
        ZSlID = gen_random_uuid();
    UPDATE
        idb.TEMP_VNOV_M
    SET
        VnovMID = gen_random_uuid();
    UPDATE
        idb.TEMP_OS_SLUCH
    SET
        OsSluchID = gen_random_uuid();
    UPDATE
        idb.TEMP_SL
    SET
        SlID = gen_random_uuid();
    UPDATE
        idb.TEMP_DS2
    SET
        Ds2ID = gen_random_uuid();
    UPDATE
        idb.TEMP_DS3
    SET
        Ds3ID = gen_random_uuid();
    UPDATE
        idb.TEMP_CODE_MES1
    SET
        CodeMes1ID = gen_random_uuid();
    UPDATE
        idb.TEMP_DS2_N
    SET
        Ds2NID = gen_random_uuid();
    UPDATE
        idb.TEMP_SANK
    SET
        SankID = gen_random_uuid();
    UPDATE
        idb.TEMP_SANK_SL_ID
    SET
        SankSlIdID = gen_random_uuid();
    UPDATE
        idb.TEMP_CODE_EXP
    SET
        CodeExpID = gen_random_uuid();
    UPDATE
        idb.TEMP_DS2_N
    SET
        Ds2NID = gen_random_uuid();
    UPDATE
        idb.TEMP_NAZ
    SET
        NazID = gen_random_uuid();
    UPDATE
        idb.TEMP_NAPR
    SET
        NaprID = gen_random_uuid();
    UPDATE
        idb.TEMP_CONS
    SET
        ConsID = gen_random_uuid();
    --UPDATE idb.TEMP_ONK_SL SET OnkSlID = gen_random_uuid(); --автогенерация ID
    UPDATE
        idb.TEMP_KSG_KPG
    SET
        KsgKpgID = gen_random_uuid();
    UPDATE
        idb.TEMP_SL_LEK_PR
    SET
        SlLekPrID = gen_random_uuid();
    UPDATE
        idb.TEMP_USL
    SET
        UslID = gen_random_uuid();
    UPDATE
        idb.TEMP_B_DIAG
    SET
        BDiagID = gen_random_uuid();
    UPDATE
        idb.TEMP_B_PROT
    SET
        BProtID = gen_random_uuid();
    --UPDATE idb.TEMP_ONK_USL SET OnkUslID = gen_random_uuid(); --автогенерация ID
    --UPDATE idb.TEMP_ONK_USL_LEK_PR SET LekPrID = gen_random_uuid(); --автогенерация ID
    UPDATE
        idb.TEMP_CRIT
    SET
        CritID = gen_random_uuid();
    UPDATE
        idb.TEMP_SL_KOEF
    SET
        SlKoefID = gen_random_uuid();
    UPDATE
        idb.TEMP_MR_USL_N
    SET
        MrUslNID = gen_random_uuid();
    UPDATE
        idb.TEMP_MED_DEV
    SET
        MedDevID = gen_random_uuid();
    UPDATE
        idb.TEMP_PERS
    SET
        PersID = gen_random_uuid();
    UPDATE
        idb.TEMP_DOST
    SET
        DostID = gen_random_uuid();
    UPDATE
        idb.TEMP_DOST_P
    SET
        DostPID = gen_random_uuid();
    RAISE NOTICE 'Удаление счетов курсором %', now()::TIMESTAMP;
    DECLARE _SchID uuid;
    _SchetID uuid;
    _CodeMo VARCHAR(6);
    _IsDisp BIT;
    _CurSchRec CURSOR FOR
    SELECT
        t1.SchetID
    FROM
        idb.TEMP_SCHET s
        join idb.TEMP_ZGLV z on 1 = 1
    JOIN
        iszl.T_SCHET t1
    ON
        s.NSCHET  = t1.NSCHET
    AND s.YEAR    = t1.YEAR
    AND s.MONTH   = t1.MONTH --and s.DSCHET = t1.DSCHET
    AND s.CODE_MO = t1.CODE_MO
    AND s.CODE    = t1.CODE::INT
    AND coalesce(z.FILENAME, gen_random_uuid()::varchar) = coalesce(t1.FILENAME, gen_random_uuid()::varchar)
    GROUP BY
        t1.SchetID;
    BEGIN
        OPEN _CurSchRec;
        FETCH NEXT
        FROM
            _CurSchRec
        INTO
            _SchID;
        WHILE (FOUND)
        LOOP PERFORM sp_SchetDelete(_SchID);
            FETCH NEXT
            FROM
                _CurSchRec
            INTO
                _SchID;
        END LOOP;
        CLOSE _CurSchRec;
        -- DEALLOCATE _CurSchRec;
        RAISE NOTICE 'INSERT SCHET %', now()::TIMESTAMP;
        --грузим SCHET
        INSERT INTO
            iszl.T_SCHET
                (
                    SchetID     ,
                    VERSION     ,
                    DATA        ,
                    YEAR        ,
                    MONTH       ,
                    NSCHET      ,
                    DSCHET      ,
                    ReceivedDate,
                    FILENAME    ,
                    CODE_MO     ,
                    PLAT        ,
                    SchetFile   ,
                    CODE        ,
                    DISP
                )
        SELECT
            s.SchetID       ,
            VERSION         ,
            DATA            ,
            YEAR            ,
            MONTH           ,
            NSCHET          ,
            DSCHET          ,
            now()::TIMESTAMP,
            FILENAME        ,
            CODE_MO         ,
            PLAT            ,
            "FileID"        ,
            CODE            ,
            DISP
        FROM
            idb.TEMP_SCHET s
        JOIN
            idb.TEMP_ZGLV z
        ON
            1 = 1;
        RAISE NOTICE 'Создание переменных по SCHET %', now()::TIMESTAMP;
        SELECT
            SchetID,
            CODE_MO,
            CASE
            WHEN
                z.FILENAME LIKE 'D%'
            THEN
                1
            ELSE
                0
            END
        INTO
            _SchetID,
            _CodeMo ,
            _IsDisp
        FROM
            idb.TEMP_SCHET s
        JOIN
            idb.TEMP_ZGLV z
        ON
            1 = 1 LIMIT 1;
        RAISE NOTICE 'CREATE INDEX ZAP, PACIENT, PERS %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_ZAP_N_ZAP
        ON idb.TEMP_ZAP
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_PACIENT_N_ZAP
        ON idb.TEMP_PACIENT
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_PACIENT_ID_PAC
        ON idb.TEMP_PACIENT
            (
                ID_PAC
            )
        ;
        CREATE INDEX IX_TEMP_PERS_ID_PAC
        ON idb.TEMP_PERS
            (
                ID_PAC
            )
        ;
        RAISE NOTICE 'INSERT ZAP %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_ZAP
                (
                    SchetZapID         ,
                    SPOLIS             ,
                    NPOLIS             ,
                    FAM                ,
                    IM                 ,
                    OT                 ,
                    DR                 ,
                    DOCSER             ,
                    DOCNUM             ,
                    SNILS              ,
                    VPOLIS             ,
                    W                  ,
                    DOCTYPE            ,
                    Schet              ,
                    InsuredPolicyRecord,
                    F002smo            ,
                    DOCDATE            ,
                    DOCORG             ,
                    ID_PAC             ,
                    ENP                ,
                    TEL                ,
                    Insured            ,
                    N_ZAP              ,
                    INV                ,
                    SS_DOCTOR
                )
        SELECT
            z.ZapID                        ,
            p.SPOLIS                  AS SPOLIS             ,
            coalesce(p.NPOLIS, p.ENP) AS NPOLIS             ,
            p.FAM                     AS FAM                ,
            p.IM                      AS IM                 ,
            p.OT                      AS OT                 ,
            p.DR                      AS DR                 ,
            p.DOCSER                  AS DOCSER             ,
            p.DOCNUM                  AS DOCNUM             ,
            p.SNILS                   AS SNILS              ,
            p.VPOLIS                  AS VPOLIS             ,
            p.W                       AS W                  ,
            p.DOCTYPE                 AS DOCTYPE            ,
            _SchetID                  AS Schet              ,
            NULL                      AS InsuredPolicyRecord,
            p.SMO                     AS F002smo            ,
            p.DOCDATE                 AS DOCDATE            ,
            p.DOCORG                  AS DOCORG             ,
            p.ID_PAC                  AS ID_PAC             ,
            p.ENP                     AS ENP                ,
            p.TEL                     AS TEL                ,
            NULL                      AS Insured            ,
            z.N_ZAP                   AS N_ZAP              ,
            p.INV                     AS INV                ,
            p.SS_DOCTOR               AS SS_DOCTOR
        FROM
            idb.TEMP_ZAP z
        INNER JOIN
            LATERAL
            (
                SELECT
                    t101.ID_PAC  ,
                    t101.VPOLIS  ,
                    t101.SPOLIS  ,
                    t101.NPOLIS  ,
                    t101.ENP     ,
                    t101.ST_OKATO,
                    t101.SMO     ,
                    t101.SMO_OGRN,
                    t101.SMO_OK  ,
                    t101.SMO_NAM ,
                    t101.INV     ,
                    t101.MSE     ,
                    t101.NOVOR   ,
                    t101.VNOV_D  ,
                    t100.FAM     ,
                    t100.IM      ,
                    t100.OT      ,
                    t100.W       ,
                    t100.DR      ,
                    t100.TEL     ,
                    t100.FAM_P   ,
                    t100.IM_P    ,
                    t100.OT_P    ,
                    t100.W_P     ,
                    t100.DR_P    ,
                    t100.MR      ,
                    t100.DOCTYPE ,
                    t100.DOCSER  ,
                    t100.DOCNUM  ,
                    t100.DOCDATE ,
                    t100.DOCORG  ,
                    t100.SNILS   ,
                    t100.OKATOG  ,
                    t100.OKATOP  ,
                    t100.COMENTP
                FROM
                    idb.TEMP_PERS t100
                JOIN
                    idb.TEMP_PACIENT t101
                ON
                    t100.ID_PAC = t101.ID_PAC
                WHERE
                    t101.N_ZAP = z.N_ZAP LIMIT 1 ) p
        ON
            TRUE;
        RAISE NOTICE 'CREATE INDEX Z_SL %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_Z_SL_N_ZAP
        ON idb.TEMP_Z_SL
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_Z_SL_IDCASE
        ON idb.TEMP_Z_SL
            (
                IDCASE
            )
        ;
        RAISE NOTICE 'INSERT Z_SL %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_ACCOMPLISHED
                (
                    SchetSluchAccomplishedID,
                    IDCASE                  ,
                    USL_OK                  ,
                    DATE_Z_1                ,
                    DATE_Z_2                ,
                    RSLT                    ,
                    ISHOD                   ,
                    SchetZap                ,
                    P_OTK                   ,
                    RSLT_D                  ,
                    MO                      ,
                    VIDPOM                  ,
                    FOR_POM                 ,
                    NPR_MO                  ,
                    NPR_DATE                ,
                    NOM_NAP                 ,
                    VID_NAP_IS              ,
                    NOM_NAP_IS
                )
        SELECT
            zsl.ZSlID          ,
            zsl.IDCASE         ,
            zsl.USL_OK         ,
            zsl.DATE_Z_1       ,
            zsl.DATE_Z_2       ,
            zsl.RSLT           ,
            zsl.ISHOD          ,
            z.ZapID AS SchetZap,
            zsl.P_OTK::BOOLEAN ,
            zsl.RSLT_D         ,
            _CodeMo AS MO      ,
            zsl.VIDPOM         ,
            zsl.FOR_POM        ,
            zsl.NPR_MO         ,
            zsl.NPR_DATE       ,
            zsl.NOM_NAP        ,
            zsl.VID_NAP_IS     ,
            zsl.NOM_NAP_IS
        FROM
            idb.TEMP_ZAP z
        JOIN
            idb.TEMP_Z_SL zsl
        ON
            zsl.N_ZAP = z.N_ZAP;
        RAISE NOTICE 'INSERT VNOV_M %', now()::TIMESTAMP;
        RAISE NOTICE 'INSERT OS_SLUCH %', now()::TIMESTAMP;
        RAISE NOTICE 'CREATE INDEX SL %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_SL_N_ZAP
        ON idb.TEMP_SL
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_SL_IDCASE
        ON idb.TEMP_SL
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_SL_SL_ID
        ON idb.TEMP_SL
            (
                SL_ID
            )
        ;
        RAISE NOTICE 'INSERT SL %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH
                (
                    SchetSluchID          ,
                    IDCASE                ,
                    SL_ID                 ,
                    NHISTORY              ,
                    DATE_1                ,
                    DATE_2                ,
                    DS1                   ,
                    LPU                   ,
                    RSLT                  ,
                    ISHOD                 ,
                    SchetZap              ,
                    Mkb                   ,
                    SchetSluchAccomplished,
                    PROFIL_K              ,
                    DN                    ,
                    RSLT_D                ,
                    DISP                  ,
                    C_ZAB                 ,
                    PRVS3                 ,
                    DSONK                 ,
                    CODE_MES1             ,
                    CODE_MES2             ,
                    VID_HMP               ,
                    METOD_HMP             ,
                    P_CEL                 ,
                    IDDOKT
                )
        SELECT
            sl.SlID            ,
            sl.IDCASE::BIGINT  ,
            sl.SL_ID           ,
            sl.NHISTORY        ,
            sl.DATE_1          ,
            sl.DATE_2          ,
            sl.DS1             ,
            _CodeMo AS LPU     ,
            zsl.RSLT           ,
            zsl.ISHOD          ,
            z.ZapID AS SchetZap,
            (
                SELECT
                    m.MkbID
                FROM
                    iszl.T_MKB m
                WHERE
                    m.MCBCode = sl.DS1 LIMIT 1 ) AS Mkb,
            zsl.ZSlID                                  ,
            sl.PROFIL_K                                ,
            coalesce(sl.DN, sl.PR_D_N)                 ,
            zsl.RSLT_D                                 ,
            _IsDisp::INT::BOOLEAN AS DISP              ,
            sl.C_ZAB                                   ,
            sl.PRVS                                    ,
            coalesce(sl.DS_ONK, 0)::BOOLEAN            ,
            (
                SELECT
                    cm.CODE_MES1
                FROM
                    idb.TEMP_CODE_MES1 cm
                WHERE
                    cm.IDCASE = sl.IDCASE
                AND cm.SL_ID  = sl.SL_ID LIMIT 1 ) AS CODE_MES1,
            sl.CODE_MES2                                       ,
            sl.VID_HMP                                         ,
            sl.METOD_HMP                                       ,
            sl.P_CEL                                           ,
            sl.IDDOKT
        FROM
            idb.TEMP_ZAP z
        JOIN
            idb.TEMP_Z_SL zsl
        ON
            zsl.N_ZAP = z.N_ZAP
        JOIN
            idb.TEMP_SL sl
        ON
            zsl.N_ZAP  = sl.N_ZAP
        AND zsl.IDCASE = sl.IDCASE;
        RAISE NOTICE 'INSERT DS2 %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_DS
                (
                    SchetSluchDsID,
                    MKB           ,
                    DS2_PR        ,
                    SchetSluch    ,
                    MKBType       ,
                    PR_DS2_N
                )
        SELECT
            ds2.Ds2ID           ,
            ds2.DS2             ,
            NULL    AS DS2_PR      ,
            sl.SlID AS SchetSluchID,
            0       AS MKBType --сопуствующие диагнозы
            ,
            NULL AS PR_DS2_N
        FROM
            idb.TEMP_DS2 ds2
        JOIN
            idb.TEMP_SL sl
        ON
            ds2.N_ZAP  = sl.N_ZAP
        AND ds2.IDCASE = sl.IDCASE
        AND ds2.SL_ID  = sl.SL_ID;
        RAISE NOTICE 'INSERT DS3 %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_DS
                (
                    SchetSluchDsID,
                    MKB           ,
                    DS2_PR        ,
                    SchetSluch    ,
                    MKBType       ,
                    PR_DS2_N
                )
        SELECT
            ds3.Ds3ID           ,
            ds3.DS3             ,
            NULL    AS DS2_PR      ,
            sl.SlID AS SchetSluchID,
            1       AS MKBType --диагнозы осложнения
            ,
            NULL AS PR_DS2_N
        FROM
            idb.TEMP_DS3 ds3
        JOIN
            idb.TEMP_SL sl
        ON
            ds3.N_ZAP  = sl.N_ZAP
        AND ds3.IDCASE = sl.IDCASE
        AND ds3.SL_ID  = sl.SL_ID;
        RAISE NOTICE 'INSERT DS3 %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_DS
                (
                    SchetSluchDsID,
                    MKB           ,
                    DS2_PR        ,
                    SchetSluch    ,
                    MKBType       ,
                    PR_DS2_N
                )
        SELECT
            ds2n.Ds2NID                         ,
            ds2n.DS2                            ,
            ds2n.DS2_PR::BOOLEAN AS DS2_PR      ,
            sl.SlID              AS SchetSluchID,
            0                    AS MKBType --сопуствующие диагнозы
            ,
            ds2n.PR_DS2_N AS PR_DS2_N
        FROM
            idb.TEMP_DS2_N ds2n
        JOIN
            idb.TEMP_SL sl
        ON
            ds2n.N_ZAP  = sl.N_ZAP
        AND ds2n.IDCASE = sl.IDCASE
        AND ds2n.SL_ID  = sl.SL_ID;
        RAISE NOTICE 'INSERT CODE_MES1 %', now()::TIMESTAMP;
        RAISE NOTICE 'CREATE INDEX ONK_SL %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_ONK_SL_N_ZAP
        ON idb.TEMP_ONK_SL
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_ONK_SL_IDCASE
        ON idb.TEMP_ONK_SL
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_ONK_SL_SL_ID
        ON idb.TEMP_ONK_SL
            (
                SL_ID
            )
        ;
        RAISE NOTICE 'INSERT ONK_SL %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_ONK
                (
                    SchetSluchOnkID,
                    WEI            ,
                    K_FR           ,
                    SOD            ,
                    Diagnosis      ,
                    STAD           ,
                    DS1_T          ,
                    ONK_T          ,
                    ONK_N          ,
                    ONK_M          ,
                    MTSTZ          ,
                    SchetSluch     ,
                    HEI            ,
                    BSA
                )
        SELECT
            onksl.OnkSlID,
            onksl.WEI    ,
            onksl.K_FR   ,
            onksl.SOD    ,
            (
                SELECT
                    m.Name
                FROM
                    iszl.T_MKB m
                WHERE
                    m.MCBCode = sl.DS1 LIMIT 1 ),
            onksl.STAD                          ,
            onksl.DS1_T                         ,
            onksl.ONK_T                         ,
            onksl.ONK_N                         ,
            onksl.ONK_M                         ,
            coalesce(onksl.MTSTZ, 0)::BOOLEAN   ,
            sl.SlID                             ,
            onksl.HEI                           ,
            onksl.BSA
        FROM
            idb.TEMP_ONK_SL onksl
        JOIN
            idb.TEMP_SL sl
        ON
            onksl.N_ZAP  = sl.N_ZAP
        AND onksl.IDCASE = sl.IDCASE
        AND onksl.SL_ID  = sl.SL_ID;
        RAISE NOTICE 'INSERT B_DIAG %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_ONK_DIAG
                (
                    SchetSluchOnkDiagID,
                    DIAG_DATE          ,
                    DIAG_TIP           ,
                    DIAG_CODE          ,
                    DIAG_RSLT          ,
                    REC_RSLT           ,
                    SchetSluchOnk
                )
        SELECT
            bd.BDiagID                       ,
            bd.DIAG_DATE                     ,
            bd.DIAG_TIP                      ,
            bd.DIAG_CODE                     ,
            bd.DIAG_RSLT                     ,
            coalesce(bd.REC_RSLT, 0)::BOOLEAN,
            bd.OnkSlID
        FROM
            idb.TEMP_B_DIAG bd;
        RAISE NOTICE 'INSERT B_PROT %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_ONK_PROT
                (
                    SchetSluchOnkProtID,
                    D_PROT             ,
                    PROT               ,
                    SchetSluchOnk
                )
        SELECT
            bp.BProtID,
            bp.D_PROT ,
            bp.PROT   ,
            bp.OnkSlID
        FROM
            idb.TEMP_B_PROT bp;
        RAISE NOTICE 'INSERT ONK_USL %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_USL_ONK
                (
                    SchetUslOnkID,
                    USL_TIP      ,
                    HIR_TIP      ,
                    LEK_TIP_L    ,
                    LEK_TIP_V    ,
                    PPTR         ,
                    SchetSluchOnk,
                    LUCH_TIP
                )
        SELECT
            ou.OnkUslID                  ,
            ou.USL_TIP                   ,
            ou.HIR_TIP                   ,
            ou.LEK_TIP_L                 ,
            ou.LEK_TIP_V                 ,
            coalesce(ou.PPTR, 0)::BOOLEAN,
            ou.OnkSlID                   ,
            ou.LUCH_TIP
        FROM
            idb.TEMP_ONK_USL ou;
        RAISE NOTICE 'INSERT ONK_USL_LEK_PR %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_USL_ONK_LEK_PR
                (
                    SchetUslOnkLekPrID,
                    SchetUslOnk       ,
                    REGNUM            ,
                    CODE_SH
                )
        SELECT
            lp.OnkUslLekPrId,
            lp.OnkUslID     ,
            lp.REGNUM       ,
            lp.CODE_SH
        FROM
            idb.TEMP_ONK_USL_LEK_PR lp;
        RAISE NOTICE 'INSERT ONK_USL_LEK_PR %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_USL_ONK_LEK_PR_DATE
                (
                    SchetUslOnkLekPrDateID,
                    DATE_INJ              ,
                    SchetUslOnkLekPr
                )
        SELECT
            gen_random_uuid(),
            lpdi.DATE_INJ    ,
            lpdi.OnkUslLekPrId
        FROM
            idb.TEMP_DATE_INJ lpdi;
        RAISE NOTICE 'CREATE INDEX SANK %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_SANK_N_ZAP
        ON idb.TEMP_SANK
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_SANK_IDCASE
        ON idb.TEMP_SANK
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_SANK_S_CODE
        ON idb.TEMP_SANK
            (
                S_CODE
            )
        ;
        CREATE INDEX IX_TEMP_SANK_S_SUM
        ON idb.TEMP_SANK
            (
                S_SUM
            )
        ;
        CREATE INDEX IX_TEMP_SANK_SL_ID_N_ZAP
        ON idb.TEMP_SANK_SL_ID
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_SANK_SL_ID_IDCASE
        ON idb.TEMP_SANK_SL_ID
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_SANK_SL_ID_S_CODE
        ON idb.TEMP_SANK_SL_ID
            (
                S_CODE
            )
        ;
        CREATE INDEX IX_TEMP_SANK_SL_ID_S_SUM
        ON idb.TEMP_SANK_SL_ID
            (
                S_SUM
            )
        ;
        RAISE NOTICE 'INSERT SANK %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_SANK
                (
                    SchetSluchSankID      ,
                    S_CODE                ,
                    SchetSluch            ,
                    S_TIP                 ,
                    S_IST                 ,
                    SchetSluchAccomplished,
                    DATE_ACT              ,
                    NUM_ACT               ,
                    S_OSN
                )
        SELECT
            sank.SankID       ,
            sank.S_CODE       ,
            NULL AS SchetSluch,
            sank.S_TIP        ,
            sank.S_IST        ,
            zsl.ZSlID         ,
            sank.DATE_ACT     ,
            sank.NUM_ACT      ,
            sank.S_OSN
        FROM
            idb.TEMP_SANK sank
        JOIN
            idb.TEMP_Z_SL zsl
        ON
            sank.N_ZAP  = zsl.N_ZAP
        AND sank.IDCASE = zsl.IDCASE
        WHERE NOT EXISTS
            (
                SELECT
                    t100.SlID
                FROM
                    idb.TEMP_SL t100
                JOIN
                    idb.TEMP_SANK_SL_ID t101
                ON
                    t100.N_ZAP  = t101.N_ZAP
                AND t100.IDCASE = t101.IDCASE
                AND t100.SL_ID  = t101.SL_ID
                WHERE
                    t101.N_ZAP              = sank.N_ZAP
                AND t101.IDCASE             = sank.IDCASE
                AND t101.S_CODE             = sank.S_CODE
                AND coalesce(t101.S_SUM, 0) = coalesce(sank.S_SUM, 0) LIMIT 1 );
        INSERT INTO
            iszl.T_SCHET_SLUCH_SANK
                (
                    SchetSluchSankID      ,
                    S_CODE                ,
                    SchetSluch            ,
                    S_TIP                 ,
                    S_IST                 ,
                    SchetSluchAccomplished,
                    DATE_ACT              ,
                    NUM_ACT               ,
                    S_OSN
                )
        SELECT
            sank.SankID          ,
            sank.S_CODE          ,
            sl.SlID AS SchetSluch,
            sank.S_TIP           ,
            sank.S_IST           ,
            zsl.ZSlID            ,
            sank.DATE_ACT        ,
            sank.NUM_ACT         ,
            sank.S_OSN
        FROM
            idb.TEMP_SANK sank
        JOIN
            idb.TEMP_Z_SL zsl
        ON
            sank.N_ZAP  = zsl.N_ZAP
        AND sank.IDCASE = zsl.IDCASE
        JOIN
            idb.TEMP_SANK_SL_ID ssi
        ON
            ssi.N_ZAP              = sank.N_ZAP
        AND ssi.IDCASE             = sank.IDCASE
        AND ssi.S_CODE             = sank.S_CODE
        AND coalesce(ssi.S_SUM, 0) = coalesce(sank.S_SUM, 0)
        JOIN
            idb.TEMP_SL sl
        ON
            sl.N_ZAP  = ssi.N_ZAP
        AND sl.IDCASE = ssi.IDCASE
        AND sl.SL_ID  = ssi.SL_ID;
        RAISE NOTICE 'CREATE INDEX NAPR %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_NAPR_N_ZAP
        ON idb.TEMP_NAPR
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_NAPR_IDCASE
        ON idb.TEMP_NAPR
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_NAPR_SL_ID
        ON idb.TEMP_NAPR
            (
                SL_ID
            )
        ;
        RAISE NOTICE 'INSERT NAPR %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_USL_NAPR
                (
                    SchetUslNaprID,
                    NAPR_DATE     ,
                    NAPR_MO       ,
                    NAPR_V        ,
                    MET_ISSL      ,
                    NAPR_USL      ,
                    SchetSluch
                )
        SELECT
            napr.NaprID   ,
            napr.NAPR_DATE,
            napr.NAPR_MO  ,
            napr.NAPR_V   ,
            napr.MET_ISSL ,
            napr.NAPR_USL ,
            s.SlID
        FROM
            idb.TEMP_NAPR napr
        JOIN
            idb.TEMP_SL s
        ON
            napr.N_ZAP  = s.N_ZAP
        AND napr.IDCASE = s.IDCASE
        AND napr.SL_ID  = s.SL_ID;
        RAISE NOTICE 'CREATE INDEX CONS %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_CONS_N_ZAP
        ON idb.TEMP_CONS
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_CONS_IDCASE
        ON idb.TEMP_CONS
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_CONS_SL_ID
        ON idb.TEMP_CONS
            (
                SL_ID
            )
        ;
        RAISE NOTICE 'INSERT CONS %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_CONS
                (
                    SchetSluchConsID,
                    SchetSluch      ,
                    DT_CONS         ,
                    PR_CONS
                )
        SELECT
            cons.ConsID ,
            s.SlID      ,
            cons.DT_CONS,
            cons.PR_CONS
        FROM
            idb.TEMP_CONS cons
        JOIN
            idb.TEMP_SL s
        ON
            cons.N_ZAP  = s.N_ZAP
        AND cons.IDCASE = s.IDCASE
        AND cons.SL_ID  = s.SL_ID;
        RAISE NOTICE 'CREATE INDEX USL %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_USL_N_ZAP
        ON idb.TEMP_USL
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_USL_IDCASE
        ON idb.TEMP_USL
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_USL_SL_ID
        ON idb.TEMP_USL
            (
                SL_ID
            )
        ;
        CREATE INDEX IX_TEMP_USL_IDSERV
        ON idb.TEMP_USL
            (
                IDSERV
            )
        ;
        RAISE NOTICE 'INSERT USL %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_USL
                (
                    SchetUslID,
                    IDSERV    ,
                    LPU       ,
                    DATE_IN   ,
                    DATE_OUT  ,
                    DS        ,
                    KOL_USL   ,
                    VID_VME   ,
                    SchetSluch,
                    CODE_USL  ,
                    P_OTK     ,
                    CODE_MD
                )
        SELECT
            u.UslID         ,
            u.IDSERV        ,
            u.LPU           ,
            u.DATE_IN       ,
            u.DATE_OUT      ,
            u.DS            ,
            u.KOL_USL       ,
            u.VID_VME       ,
            s.SlID          ,
            u.CODE_USL      ,
            u.P_OTK::BOOLEAN,
            u.CODE_MD
        FROM
            idb.TEMP_USL u
        JOIN
            idb.TEMP_SL s
        ON
            u.N_ZAP  = s.N_ZAP
        AND u.IDCASE = s.IDCASE
        AND u.SL_ID  = s.SL_ID;
        RAISE NOTICE 'CREATE INDEX NAZ %', now()::TIMESTAMP;
        CREATE INDEX IX_TEMP_NAZ_N_ZAP
        ON idb.TEMP_NAZ
            (
                N_ZAP
            )
        ;
        CREATE INDEX IX_TEMP_NAZ_IDCASE
        ON idb.TEMP_NAZ
            (
                IDCASE
            )
        ;
        CREATE INDEX IX_TEMP_NAZ_SL_ID
        ON idb.TEMP_NAZ
            (
                SL_ID
            )
        ;
        RAISE NOTICE 'INSERT NAZ %', now()::TIMESTAMP;
        INSERT INTO
            iszl.T_SCHET_SLUCH_NAZ
                (
                    SchetSluchNazID,
                    NAZ_N          ,
                    NAZR           ,
                    NAZ_SP         ,
                    NAZ_V          ,
                    NAZ_PK         ,
                    SchetSluch     ,
                    NAZ_USL        ,
                    NAPR_DATE      ,
                    NAPR_MO
                )
        SELECT
            n.NazID    ,
            n.NAZ_N    ,
            n.NAZ_R    ,
            n.NAZ_SP   ,
            n.NAZ_V    ,
            n.NAZ_PK   ,
            s.SlID     ,
            n.NAZ_USL  ,
            n.NAPR_DATE,
            n.NAPR_MO
        FROM
            idb.TEMP_NAZ n
        JOIN
            idb.TEMP_SL s
        ON
            n.N_ZAP  = s.N_ZAP
        AND n.IDCASE = s.IDCASE
        AND n.SL_ID  = s.SL_ID;
    END;
DROP TABLE idb.TEMP_ZGLV;
DROP TABLE idb.TEMP_SCHET;
DROP TABLE idb.TEMP_ZAP;
DROP TABLE idb.TEMP_PACIENT;
DROP TABLE idb.TEMP_Z_SL;
DROP TABLE idb.TEMP_VNOV_M;
DROP TABLE idb.TEMP_OS_SLUCH;
DROP TABLE idb.TEMP_SL;
DROP TABLE idb.TEMP_DS2;
DROP TABLE idb.TEMP_DS3;
DROP TABLE idb.TEMP_CODE_MES1;
DROP TABLE idb.TEMP_SANK;
DROP TABLE idb.TEMP_SANK_SL_ID;
DROP TABLE idb.TEMP_CODE_EXP;
DROP TABLE idb.TEMP_DS2_N;
DROP TABLE idb.TEMP_NAZ;
DROP TABLE idb.TEMP_NAPR;
DROP TABLE idb.TEMP_CONS;
DROP TABLE idb.TEMP_ONK_SL;
DROP TABLE idb.TEMP_KSG_KPG;
DROP TABLE idb.TEMP_CRIT;
DROP TABLE idb.TEMP_SL_LEK_PR;
DROP TABLE idb.TEMP_USL;
DROP TABLE idb.TEMP_B_DIAG;
DROP TABLE idb.TEMP_B_PROT;
DROP TABLE idb.TEMP_ONK_USL;
DROP TABLE idb.TEMP_ONK_USL_LEK_PR;
DROP TABLE idb.TEMP_DATE_INJ;
DROP TABLE idb.TEMP_SL_KOEF;
DROP TABLE idb.TEMP_MR_USL_N;
DROP TABLE idb.TEMP_MED_DEV;
DROP TABLE idb.TEMP_ZGLV_PERS;
DROP TABLE idb.TEMP_PERS;
DROP TABLE idb.TEMP_DOST;
DROP TABLE idb.TEMP_DOST_P;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;