create or replace PROCEDURE INSERE_SERIALSNCM (
    P_CDFILIAL      IN  ORDPRODUCAO.CDFILIAL%TYPE,
    P_NRORDPROD     IN  ORDPRODUCAO.NRORDPROD%TYPE,
    P_NRSUBORDPROD  IN  ORDPRODUCAO.NRSUBORDPROD%TYPE,
    P_NRLOTEESTQ    IN  ORDPRODUCAO.NRLOTEESTQ%TYPE,
    P_ACAO          IN  VARCHAR
) IS

    V_INILOOP           INTEGER;
    V_CDSERIALSCNM      VARCHAR2(20);
    V_COUNTREG          INTEGER;
    V_NRLOTE            VARCHAR(20);
    V_VALIDADELOTE      VARCHAR2(6);
    V_NRGETIN           VARCHAR2(14);
    V_NRREGISTROANVISA  VARCHAR2(20);
    V_QTORDPROD         NUMBER;
    V_DIFQTPRODUTOS     NUMBER;
    V_INDEX             NUMBER := 0;
    TYPE TYP_REGISTROS IS RECORD (
        CDFILIAL          ORDPRODUCAO.CDFILIAL%TYPE,
        CDPRODUTO         ORDPRODUCAO.CDPRODUTO%TYPE,
        NRLOTEESTQ        ORDPRODUCAO.NRLOTEESTQ%TYPE,
        NRSUBLOTE         ORDPRODUCAO.NRSUBLOTE%TYPE,
        NRORDPROD         ORDPRODUCAO.NRORDPROD%TYPE,
        NRSUBORDPROD      ORDPRODUCAO.NRSUBORDPROD%TYPE,
        CDSERIALSCNM      VARCHAR2(20),
        CDGTIN            VARCHAR2(14),
        NRREGISTROANVISA  VARCHAR2(20),
        DTVALIDLOTE       VARCHAR2(6),
        DTFABRLOTE        DATE,
        CDIUMPROD         VARCHAR2(100)
    );
    TYPE TYP_TABLE IS
        TABLE OF TYP_REGISTROS INDEX BY PLS_INTEGER;
    AR_RECORD           TYP_TABLE;
BEGIN
    V_INILOOP := 0;
    FOR C_ORDPRODUCAO IN (
        SELECT
            ORD.CDFILIAL,
            ORD.NRORDPROD,
            ORD.NRSUBORDPROD,
            ORD.CDPRODUTO,
            ORD.QTAPRODPREOP,
            NVL(PROD.QTPRACABADO, 0) QTPRACABADO,
            ORD.NRLOTEESTQ,
            ORD.NRSUBLOTE,
            ORD.DTHRINICIAOP,
            LOTE.DTFABRPROD  DTFABRLOTE
        FROM
            ORDPRODUCAO  ORD,
            PRODACABADO  PROD,
            LOTE
        WHERE
                ORD.CDFILIAL     = P_CDFILIAL
            AND ORD.NRORDPROD    = P_NRORDPROD
            AND ORD.NRSUBORDPROD = P_NRSUBORDPROD
            AND ORD.NRLOTEESTQ   = P_NRLOTEESTQ
            AND ORD.CDFILIAL     = LOTE.CDFILIAL
            AND ORD.CDPRODUTO    = LOTE.CDPRODUTO
            AND ORD.NRLOTEESTQ   = LOTE.NRLOTEESTQ
            AND ORD.NRSUBLOTE    = LOTE.NRSUBLOTE
            AND ORD.CDFILIAL     = PROD.CDFILIAL (+)
            AND ORD.NRORDPROD    = PROD.NRORDPROD (+)
            AND ORD.NRSUBORDPROD = PROD.NRSUBORDPROD (+)
            AND ORD.NRLOTEESTQ   = PROD.NRLOTEESTQ (+)
            AND ORD.NRSUBLOTE    = PROD.NRSUBLOTE (+)
    ) LOOP
        IF P_ACAO = 'CRIAR' THEN

             /* 1 - GTIN e REGISTRO ANVISA*/
            SELECT
                CDDUNPROD,
                LPAD(NVL(NRREGISTROANVISA, 0), 20, '0') NRREGISTROANVISA
            INTO
                V_NRGETIN,
                V_NRREGISTROANVISA
            FROM
                INFADICPROD
            WHERE
                CDPRODUTO = C_ORDPRODUCAO.CDPRODUTO;
    
            /*2 - LOTE E VALIDADE - Lote substitui os caracteres  "."ponto  "-"traco e  "/ "barra por zero e completa 20 posicoes com zero a esquerda. Validade - esta no formado YYMMDD*/
            SELECT
                LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRLOTEESTQ, '.', '0'), ',', '0'), '-', '0'), '/', '0'), 20, '0')                             NRLOTEESTQ,
                TO_CHAR(L.DTVALILOTE, 'YYMMDD')                                                                                                DTVALILOTE
            INTO
                V_NRLOTE,
                V_VALIDADELOTE
            FROM
                LOTE L
            WHERE
                ( L.CDFILIAL       = C_ORDPRODUCAO.CDFILIAL )
                AND ( L.CDPRODUTO  = C_ORDPRODUCAO.CDPRODUTO )
                AND ( L.NRLOTEESTQ = C_ORDPRODUCAO.NRLOTEESTQ );

            IF C_ORDPRODUCAO.QTAPRODPREOP > C_ORDPRODUCAO.QTPRACABADO THEN
                V_QTORDPROD := C_ORDPRODUCAO.QTAPRODPREOP;
            ELSE
                V_QTORDPROD := C_ORDPRODUCAO.QTPRACABADO;
            END IF;

            BEGIN
                UPDATE ORDPRODSNCM
                SET
                    QTPRODACABADO = V_QTORDPROD,
                    DTINIORDPROD  = C_ORDPRODUCAO.DTHRINICIAOP
                WHERE
                        CDFILIAL     = C_ORDPRODUCAO.CDFILIAL
                    AND CDPRODUTO    = C_ORDPRODUCAO.CDPRODUTO
                    AND NRORDPROD    = C_ORDPRODUCAO.NRORDPROD
                    AND NRSUBORDPROD = C_ORDPRODUCAO.NRSUBORDPROD
                    AND NRLOTEESTQ   = C_ORDPRODUCAO.NRLOTEESTQ
                    AND NRSUBLOTE    = C_ORDPRODUCAO.NRSUBLOTE;

            END;

            SELECT
                COUNT(*) AS COUNTREG
            INTO V_COUNTREG
            FROM
                SERIALPRODSCNM
            WHERE
                    CDFILIAL   = C_ORDPRODUCAO.CDFILIAL
                AND CDPRODUTO  = C_ORDPRODUCAO.CDPRODUTO
                AND NRLOTEESTQ = C_ORDPRODUCAO.NRLOTEESTQ
                AND NRSUBLOTE  = C_ORDPRODUCAO.NRSUBLOTE;

            V_DIFQTPRODUTOS := V_QTORDPROD - V_COUNTREG;
            FOR V_INILOOP IN 1..V_DIFQTPRODUTOS LOOP
                AR_RECORD(V_INDEX).CDFILIAL     := C_ORDPRODUCAO.CDFILIAL;
                AR_RECORD(V_INDEX).CDPRODUTO    := C_ORDPRODUCAO.CDPRODUTO;
                AR_RECORD(V_INDEX).NRLOTEESTQ   := C_ORDPRODUCAO.NRLOTEESTQ;
                AR_RECORD(V_INDEX).NRSUBLOTE    := C_ORDPRODUCAO.NRSUBLOTE;
                AR_RECORD(V_INDEX).NRORDPROD    := C_ORDPRODUCAO.NRORDPROD;
                AR_RECORD(V_INDEX).NRSUBORDPROD := C_ORDPRODUCAO.NRSUBORDPROD;
                AR_RECORD(V_INDEX).CDGTIN       := V_NRGETIN;
                AR_RECORD(V_INDEX).NRREGISTROANVISA := V_NRREGISTROANVISA;
                AR_RECORD(V_INDEX).DTVALIDLOTE  := V_VALIDADELOTE;
                AR_RECORD(V_INDEX).DTFABRLOTE   := C_ORDPRODUCAO.DTFABRLOTE;
    
                    /*Geracao do Serial unico a partir da novo codigo*/
                NOVO_CODIGO_SC('SNCM'|| C_ORDPRODUCAO.CDFILIAL|| C_ORDPRODUCAO.CDPRODUTO|| C_ORDPRODUCAO.NRLOTEESTQ|| C_ORDPRODUCAO.NRSUBLOTE,V_CDSERIALSCNM);

                AR_RECORD(V_INDEX).CDSERIALSCNM := V_CDSERIALSCNM;
    
                     /*FORMACAO IUM   GTIN - REGISTRO ANVISA - SERIAL UNICO - VALIDADE LOTE E LOTE*/
                AR_RECORD(V_INDEX).CDIUMPROD := V_NRGETIN || V_NRREGISTROANVISA || V_CDSERIALSCNM || V_VALIDADELOTE|| V_NRLOTE;

                V_INDEX := V_INDEX + 1;
                IF ( MOD(V_INDEX, 10000) = 0 ) THEN
                    BEGIN
                        FORALL J IN AR_RECORD.FIRST..AR_RECORD.LAST
                            INSERT INTO SERIALPRODSCNM (
                                CDFILIAL,
                                CDPRODUTO,
                                NRLOTEESTQ,
                                NRSUBLOTE,
                                NRORDPROD,
                                NRSUBORDPROD,
                                CDSERIALSCNM,
                                CDGTIN,
                                NRREGISTROANVISA,
                                DTVALIDLOTE,
                                DTFABRLOTE,
                                CDIUMPROD,
                                IDSTATUSSCNM,
                                OBSSTATUS 
                            ) VALUES (
                                AR_RECORD(J).CDFILIAL,
                                AR_RECORD(J).CDPRODUTO,
                                NVL(AR_RECORD(J).NRLOTEESTQ, ' '),
                                NVL(AR_RECORD(J).NRSUBLOTE, ' '),
                                AR_RECORD(J).NRORDPROD,
                                AR_RECORD(J).NRSUBORDPROD,
                                AR_RECORD(J).CDSERIALSCNM,
                                AR_RECORD(J).CDGTIN,
                                AR_RECORD(J).NRREGISTROANVISA,
                                AR_RECORD(J).DTVALIDLOTE,
                                AR_RECORD(J).DTFABRLOTE,
                                AR_RECORD(J).CDIUMPROD,
                                'PENDENTE',
                                'Codigo IUM pronto para ativacao'   
                            );

                        AR_RECORD.DELETE;
                        V_INDEX := 1;
                    END;
                END IF;

            END LOOP; /* FOR  V_INILOOP*/

            IF AR_RECORD.FIRST IS NOT NULL THEN
                BEGIN
                    FORALL J IN AR_RECORD.FIRST..AR_RECORD.LAST
                        INSERT INTO SERIALPRODSCNM (
                            CDFILIAL,
                            CDPRODUTO,
                            NRLOTEESTQ,
                            NRSUBLOTE,
                            NRORDPROD,
                            NRSUBORDPROD,
                            CDSERIALSCNM,
                            CDGTIN,
                            NRREGISTROANVISA,
                            DTVALIDLOTE,
                            DTFABRLOTE,
                            CDIUMPROD,
                            IDSTATUSSCNM,
                            OBSSTATUS 
                        ) VALUES (
                            AR_RECORD(J).CDFILIAL,
                            AR_RECORD(J).CDPRODUTO,
                            NVL(AR_RECORD(J).NRLOTEESTQ, ' '),
                            NVL(AR_RECORD(J).NRSUBLOTE, ' '),
                            AR_RECORD(J).NRORDPROD,
                            AR_RECORD(J).NRSUBORDPROD,
                            AR_RECORD(J).CDSERIALSCNM,
                            AR_RECORD(J).CDGTIN,
                            AR_RECORD(J).NRREGISTROANVISA,
                            AR_RECORD(J).DTVALIDLOTE,
                            AR_RECORD(J).DTFABRLOTE,
                            AR_RECORD(J).CDIUMPROD,
                            'PENDENTE',
                            'Codigo IUM pronto para ativacao'   
                        );

                END;
            END IF;

            V_INDEX := 1;
        ELSE
        
            UPDATE SERIALPRODSCNM
            SET
                IDSTATUSSCNM = 'ATIVO',
                OBSSTATUS    = 'Codigo IUM pronto para expedicao'
            WHERE
                    CDFILIAL     = C_ORDPRODUCAO.CDFILIAL
                AND CDPRODUTO    = C_ORDPRODUCAO.CDPRODUTO
                AND NRORDPROD    = C_ORDPRODUCAO.NRORDPROD
                AND NRSUBORDPROD = C_ORDPRODUCAO.NRSUBORDPROD
                AND NRLOTEESTQ   = C_ORDPRODUCAO.NRLOTEESTQ
                AND NRSUBLOTE    = C_ORDPRODUCAO.NRSUBLOTE;

        END IF;
    END LOOP; /*C_ORDPRODUCAO */

END;
/