CREATE OR REPLACE
PROCEDURE GERA_SOLICITACAO(
   P_CDFILILANC IN SOLICITA.CDFILLANCA%TYPE,
   P_NRNECECOMP IN TRABNECECOMP.NRNECECOMP%TYPE,
   P_CDOPERLANC IN SOLICITA.CDOPERLANC%TYPE,
   P_EXTRA      IN VARCHAR2 DEFAULT 'N',
   P_MSGEXTRA   IN VARCHAR2 DEFAULT NULL,
   P_MOBILE     IN VARCHAR2 DEFAULT 'N') IS

   V_PRXSOLICMP     VARCHAR2(20);
   V_CDFILCENT      VARCHAR2(4);
   V_CDFILCENTPME   VARCHAR2(4);
   V_CDFILITIPOCARD VARCHAR2(4);
   V_IDUTAPROVSOL     CHAR(1);
   V_IDFILCENTPME     CHAR(1);
   V_IDSOLIAPROV      CHAR(1);
   V_IDUTILDTPROC     CHAR(1);
   V_IDUTILCONSSOLIOL CHAR(1);
   V_IDPERALTSAPR     CHAR(1);
   V_DTPROCESS        DATE;
   V_NRSOLICMP_AUX    VARCHAR2(10);
   V_EXTRA            VARCHAR2(1);
   V_IDIGNORAPROVCMPPME CHAR(1);
   V_ERROR            VARCHAR2(2000);
BEGIN
	IF (P_EXTRA IS NULL) THEN
		V_EXTRA := 'N';
	ELSE
		V_EXTRA := P_EXTRA;
	END IF;
   BEGIN
      SELECT
         MAX(IDUTILDTPROC)
      INTO
         V_IDUTILDTPROC
      FROM
         PARAMGERAL;

      SELECT
         NVL(MAX(IDUTILCONSSOLIOL), 'N'), NVL(MAX(IDIGNORAPROVCMPPME), 'N')
      INTO
         V_IDUTILCONSSOLIOL, V_IDIGNORAPROVCMPPME
      FROM
         PARASUPRI
      WHERE
         PARASUPRI.CDFILIAL = (SELECT NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)
                               FROM   FILIAL
                               WHERE  FILIAL.CDFILIAL = P_CDFILILANC
                               );
      FOR CUR_FILIAL IN
         (SELECT DISTINCT
             TRABNECECOMP.CDFILNECECMP
          FROM
             TRABNECECOMP
          WHERE
             TRABNECECOMP.NRNECECOMP = P_NRNECECOMP)
      LOOP
         V_IDUTAPROVSOL := '';
         V_PRXSOLICMP   := '';
         V_CDFILCENT    := '';
         V_IDSOLIAPROV  := '';
         V_IDPERALTSAPR := '';
         NOVO_CODIGO('SOLICOMP'||CUR_FILIAL.CDFILNECECMP||P_CDFILILANC, V_PRXSOLICMP);
         V_PRXSOLICMP := SUBSTR(V_PRXSOLICMP,11,10);

         SELECT
            MAX(IDUTAPROVSOL),
            MAX(SUBSTR(NVL(IDPERALTSAPR, 'NNN'), 3,1))
         INTO
            V_IDUTAPROVSOL,
            V_IDPERALTSAPR
         FROM
            PARCMPSUP
         WHERE
            CDFILIAL = CUR_FILIAL.CDFILNECECMP;

         IF V_IDUTAPROVSOL = 'S' THEN
            IF V_IDPERALTSAPR = 'S' THEN
               V_IDSOLIAPROV := 'S';
            ELSE
               V_IDSOLIAPROV := 'N';
            END IF;
         ELSE
            V_IDSOLIAPROV := 'S';
         END IF;

         SELECT
            MAX(NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL)),
            MAX(CDFILCENTPME),
            MAX(IDFILCENTPME)
         INTO
            V_CDFILCENT,
            V_CDFILCENTPME,
            V_IDFILCENTPME
         FROM
            FILIAL
         WHERE
            FILIAL.CDFILIAL = CUR_FILIAL.CDFILNECECMP;

         INSERT INTO SOLICITA
           (CDFILSOLI,
            CDFILLANCA,
            NRSOLICMP,
            DTSOLCMP,
            IDTIPLAN,
            CDFILCENT,
            CDOPERLANC,
            NRNECECMPSOL,
            IDSOLEXTRA)
         VALUES
           (CUR_FILIAL.CDFILNECECMP,
            P_CDFILILANC,
            V_PRXSOLICMP,
            TO_DATE(TRUNC(SYSDATE)),
            'A',
            V_CDFILCENT,
            P_CDOPERLANC,
            P_NRNECECOMP,
            V_EXTRA);

         IF P_EXTRA = 'S' THEN
            INSERT INTO MOVSOLEXT
               (CDFILSOLEXT,
                CDFILLANCEXT,
                NRSOLIMOVEXT,
                DTMOVSOLEXT,
                CDOPERADOR,
                DSJUSTSOLEXT)
            VALUES
               (CUR_FILIAL.CDFILNECECMP,
                P_CDFILILANC,
                V_PRXSOLICMP,
                SYSDATE,
                P_CDOPERLANC,
                P_MSGEXTRA);
         END IF;

         IF (V_CDFILCENTPME IS NOT NULL) AND (NVL(V_IDFILCENTPME, 'N') = 'N') AND (V_IDIGNORAPROVCMPPME = 'N')THEN
            SELECT
               MAX(CDFILIAL)
            INTO
               V_CDFILITIPOCARD
            FROM
               FILITIPOCARD
            WHERE
               CDFILCENTPME = V_CDFILCENTPME AND
               CDFILIAL     =  CUR_FILIAL.CDFILNECECMP;

            IF (V_CDFILITIPOCARD IS NOT NULL) AND (V_IDUTAPROVSOL = 'S') THEN
               V_IDSOLIAPROV := 'N';
            END IF;
         END IF;

         IF V_IDUTILCONSSOLIOL = 'S' THEN
            INSERT INTO ITEMSOLI
              (CDFILSOLI,
               CDFILLAN,
               NRSOLICMP,
               CDPRODUTO,
               DTPROGENTR,
               DTUTILIZA,
               QTSOLIPROD,
               IDSOLIAPROV,
               CDFILIOPELOG)
            SELECT 
               CUR_FILIAL.CDFILNECECMP,
               P_CDFILILANC,
               V_PRXSOLICMP,
               CDPRODNECCMP,
               DTENTRNECCMP,
               MIN(DTPRIUTIPROD),
               SUM(QTPRCNVCOMPR),
               V_IDSOLIAPROV,
               ((SELECT
                   MAX(P.CDFILIOPELOG)
                FROM
                   PARAMPRODSUP P
                WHERE
                   P.CDFILIAL     = CUR_FILIAL.CDFILNECECMP AND
                   P.CDPRODUTO    = CDPRODNECCMP            AND
                   P.CDFILIOPELOG IS NOT NULL
                ))
            FROM
               TRABNECECOMP
            WHERE
               (NRNECECOMP   = P_NRNECECOMP)            AND
               (IDMARCNECCMP = 'S')                     AND
               (CDFILNECECMP = CUR_FILIAL.CDFILNECECMP)
            GROUP BY
               CUR_FILIAL.CDFILNECECMP,
               P_CDFILILANC,
               V_PRXSOLICMP,
               CDPRODNECCMP,
               DTENTRNECCMP,
               V_IDSOLIAPROV;
         ELSE
            INSERT INTO ITEMSOLI
              (CDFILSOLI,
               CDFILLAN,
               NRSOLICMP,
               CDPRODUTO,
               DTPROGENTR,
               DTUTILIZA,
               QTSOLIPROD,
               IDSOLIAPROV)
            SELECT
               CUR_FILIAL.CDFILNECECMP,
               P_CDFILILANC,
               V_PRXSOLICMP,
               CDPRODNECCMP,
               DTENTRNECCMP,
               MIN(DTPRIUTIPROD),
               SUM(QTPRCNVCOMPR),
               V_IDSOLIAPROV
            FROM
               TRABNECECOMP
            WHERE
               (NRNECECOMP   = P_NRNECECOMP)            AND
               (IDMARCNECCMP = 'S')                     AND
               (CDFILNECECMP = CUR_FILIAL.CDFILNECECMP)
            GROUP BY
                CUR_FILIAL.CDFILNECECMP,
                P_CDFILILANC,
                V_PRXSOLICMP,
                CDPRODNECCMP,
                DTENTRNECCMP,
                V_IDSOLIAPROV;
         END IF;

         IF V_IDUTILDTPROC <> 'S' THEN
            V_DTPROCESS := TRUNC(SYSDATE);
         ELSE
            SELECT
               NVL(MAX(DTPROCESSA), TRUNC(SYSDATE))
            INTO
               V_DTPROCESS
            FROM
               PARAMFILIAL
            WHERE
               CDFILIAL = CUR_FILIAL.CDFILNECECMP;
         END IF;

         FOR C_FILINECE IN
            (SELECT
                IT.ROWID
             FROM
                PRODUTO PR,
                ITEMSOLI IT,
                FILIAL,
                FILINECE FI,
                LOGNECECOMP LOG
             WHERE
                LOG.CDFILIAL             = CUR_FILIAL.CDFILNECECMP         AND
                LOG.NRNECECMPLOG         = P_NRNECECOMP                    AND
                FI.CDFILIAL              = LOG.CDFILIAL                    AND
                FI.IDSOLIPREV           <> 'P'                             AND
                FI.CDFILIAL              = FILIAL.CDFILIAL                 AND
                NVL(FILIAL.IDFILCENTPME, 'N') = 'N'                        AND
                FI.CDFILINECCMP          = IT.CDFILSOLI                    AND
                IT.CDPRODUTO             = PR.CDPRODUTO                    AND
                IT.DTPROGENTR           >= V_DTPROCESS                     AND
                PR.CDARVPROD BETWEEN LOG.CDPRODINICNC AND LOG.CDPRODFINANC
             )
         LOOP
            UPDATE ITEMSOLI
            SET IDITMCONS = 'S'
            WHERE
               ITEMSOLI.ROWID = C_FILINECE.ROWID;
         END LOOP;

         IF (V_CDFILCENTPME IS NULL) AND (V_IDFILCENTPME = 'S') THEN
            FOR CUR_NECECOMP IN
               (SELECT
                  TRABNECECOMP.CDFILNECECMP,
                  TRABNECECOMP.CDPRODNECCMP,
                  PRODUTO.CDPRODESTO,
                  TRABNECECOMP.DTENTRNECCMP
               FROM
                  PRODUTO,
                  TRABNECECOMP
               WHERE
                  PRODUTO.CDPRODUTO = TRABNECECOMP.CDPRODNECCMP AND
                  NRNECECOMP   = P_NRNECECOMP                   AND
                  IDMARCNECCMP = 'S'                            AND
                  CDFILNECECMP = CUR_FILIAL.CDFILNECECMP
               ORDER BY
                  DTENTRNECCMP DESC)
            LOOP
               UPDATE
                  ITEMSOLI
               SET
                  CDFILSOLCONS = CUR_NECECOMP.CDFILNECECMP,
                  CDFILLANCONS = P_CDFILILANC,
                  NRSOLCMPCONS = V_PRXSOLICMP,
                  CDPRODUTCONS = CUR_NECECOMP.CDPRODNECCMP,
                  DTPROGENCONS = CUR_NECECOMP.DTENTRNECCMP,
                  IDITMCONS    = 'S'
               WHERE
                  (DTPROGENTR, CDPRODUTO, CDFILSOLI) IN (SELECT
                                                            IT.DTPROGENTR,
                                                            IT.CDPRODUTO,
                                                            IT.CDFILSOLI
                                                         FROM
                                                            PRODUTO,
                                                            FILIAL,
                                                            ITEMSOLI IT
                                                         WHERE
                                                            IT.CDFILSOLCONS IS NULL                          AND
                                                            NVL(IT.IDITMCONS, 'N')  = 'N'                    AND
                                                            NVL(IT.IDSOLIAPROV, 'S')= 'S'                    AND
                                                            IT.CDPRODUTO         = PRODUTO.CDPRODUTO         AND
                                                            PRODUTO.CDPRODESTO   = CUR_NECECOMP.CDPRODESTO   AND
                                                            FILIAL.CDFILIAL      = IT.CDFILSOLI              AND
                                                            FILIAL.CDFILCENTPME  = CUR_NECECOMP.CDFILNECECMP AND
                                                            IT.DTPROGENTR >= CUR_NECECOMP.DTENTRNECCMP AND
                                                            NOT EXISTS  (SELECT
                                                                            CMPCENTENDIR.CDFILIAL,
                                                                            CMPCENTENDIR.CDPRODUTO
                                                                         FROM
                                                                            CMPCENTENDIR,
                                                                            PRODUTO PI,
                                                                            PRODUTO PC
                                                                         WHERE
                                                                            CMPCENTENDIR.CDFILIAL  = IT.CDFILSOLI AND
                                                                            CMPCENTENDIR.CDPRODUTO = PC.CDPRODUTO AND
                                                                            IT.CDPRODUTO           = PI.CDPRODUTO AND
                                                                            PI.CDARVPROD LIKE PC.CDARVPROD||'%')
                                                         );
            END LOOP;
         END IF;
         IF P_MOBILE = 'N' THEN
            DELETE FROM TRABNECECOMP
            WHERE
               (NRNECECOMP = P_NRNECECOMP)              AND
               (IDMARCNECCMP = 'S')                     AND
               (CDFILNECECMP = CUR_FILIAL.CDFILNECECMP);
         END IF;

         SELECT MAX(SOLICITA.NRSOLICMP)
           INTO V_NRSOLICMP_AUX
           FROM SOLICITA, ITEMSOLI
          WHERE SOLICITA.CDFILSOLI  = ITEMSOLI.CDFILSOLI
            AND SOLICITA.CDFILLANCA = ITEMSOLI.CDFILLAN
            AND SOLICITA.NRSOLICMP  = ITEMSOLI.NRSOLICMP
            AND SOLICITA.CDFILSOLI  = CUR_FILIAL.CDFILNECECMP
            AND SOLICITA.CDFILLANCA = P_CDFILILANC
            AND SOLICITA.NRSOLICMP  = V_PRXSOLICMP;

         IF (V_NRSOLICMP_AUX IS NULL) THEN
            DELETE
              FROM SOLICITA
             WHERE SOLICITA.CDFILSOLI  = CUR_FILIAL.CDFILNECECMP
               AND SOLICITA.CDFILLANCA = P_CDFILILANC
               AND SOLICITA.NRSOLICMP  = V_PRXSOLICMP;
         END IF;

         COMMIT;
      END LOOP;
   EXCEPTION
     WHEN OTHERS THEN BEGIN
        DELETE FROM SOLICITA WHERE NRNECECMPSOL = P_NRNECECOMP;
        COMMIT;
        V_ERROR:= SQLERRM(SQLCODE);
        RAISE_APPLICATION_ERROR(-20501, V_ERROR, TRUE);
     END;
  END;
END;
/