Bom dia pessoal.
Perdoe-me a "leigice" mas não consigo encontrar onde está o erro, uma que executando ela pelo gerenciador TOAD o mesmo não é exibido, apenas na aplicação:
Selecionar tudo
CREATE OR REPLACE PROCEDURE USEROPER.SP_GERA_DESCFOLHA_MWM
(
P_DIRETORIO IN VARCHAR2,
P_DATA_INI IN DATE,
P_DATA_FIM IN DATE,
P_CLIENT IN VARCHAR2,
P_CLIENT_GROUP IN VARCHAR2,
P_CUSTOMER IN VARCHAR2,
P_TIPOFILE IN VARCHAR2,
P_TIPO IN VARCHAR2 DEFAULT 'P',
P_NOTA IN VARCHAR2 DEFAULT NULL
) IS
vSQL VARCHAR2(200);
vERR_CODE VARCHAR2(15);
vERR_MSG VARCHAR2(200);
vCONVENIADO VARCHAR2(3) := '030';
vCODFILIAL_1 VARCHAR2(3);
vDATA VARCHAR2(10);
SAIDA1 SYS.UTL_FILE.FILE_TYPE;
vNAME_FILE VARCHAR2(100);
vDIRECTORY VARCHAR2(100);
vDIRECTORY_PATH VARCHAR2(100);
vCONTADOR NUMBER;
-------------------------------------------------------------------------------------------------------------------
/******************************************************************************************************************/
/**************************************** DESCONTO EM FOLHA (600) *************************************************/
/******************************************************************************************************************/
-- PERÍODO
CURSOR P_DESCFOLHA_PERIODO ( P_DATA_INI IN DATE,
P_DATA_FIM IN DATE,
P_CLIENT IN VARCHAR2,
P_CLIENT_GROUP IN VARCHAR2,
P_CUSTOMER IN VARCHAR2) IS
-- desconto
SELECT MATRICULA,
SUM(VALOR_DESCONTO) AS VALOR,
COD_FILIAL,
FPAGTO
FROM(
SELECT
PATIENT_PIN_NUMBER AS MATRICULA,
DESC_FOLHA_PAGTO AS VALOR_DESCONTO,
CASE
WHEN MB.CLIENT_ID IN ('CANOAS') THEN LPAD('002', 3, '0')
ELSE LPAD('003', 3, '0')
END AS COD_FILIAL,
DECODE(FORMA_PAGTO, '-1', '536', '306') AS FPAGTO
FROM MEMBER MB, V_MOVIMENTACAO VF
WHERE MB.MEMBER_ID = VF.VIDA AND
MB.CUSTOMER_ID = VF.COD_PLANO AND
MB.PERSON_CODE = VF.PERSON_CODE AND
MB.CLIENT_GROUP_ID = VF.CLIENT_GROUP_ID AND
MB.CLIENT_ID = VF.CLIENT_ID AND
VENDA = 'S' AND
STATUS_VENDA = 'P' AND
STATUS_FATURAMENTO = '0' AND
STATUS_COBRANCA = '0' AND
FORMA_PAGTO = '-1' AND
DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 2 ), VF.CLIENT_GROUP_ID) = DECODE(P_CLIENT_GROUP,'T',DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 2 ), VF.CLIENT_GROUP_ID) ,P_CLIENT_GROUP) AND
DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 1 ), VF.CLIENT_ID) = DECODE(P_CLIENT,'T',DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 1 ), VF.CLIENT_ID) ,P_CLIENT) AND
COD_PLANO = P_CUSTOMER AND
DATE_FILLED BETWEEN P_DATA_INI AND
P_DATA_FIM)
GROUP BY MATRICULA, COD_FILIAL, FPAGTO
UNION ALL
-- devolucao
SELECT MATRICULA,
SUM(VALOR_DESCONTO),
COD_FILIAL,
FPAGTO
FROM(
SELECT
PATIENT_PIN_NUMBER AS MATRICULA,
DESC_FOLHA_PAGTO AS VALOR_DESCONTO,
CASE
WHEN MB.CLIENT_ID IN ('CANOAS') THEN LPAD('002', 3, '0')
ELSE LPAD('003', 3, '0')
END AS COD_FILIAL,
DECODE(FORMA_PAGTO, '-1', '536', '306') AS FPAGTO
FROM MEMBER MB, V_MOVIMENTACAO VF
WHERE MB.MEMBER_ID = VF.VIDA AND
MB.CUSTOMER_ID = VF.COD_PLANO AND
MB.PERSON_CODE = VF.PERSON_CODE AND
MB.CLIENT_GROUP_ID = VF.CLIENT_GROUP_ID AND
MB.CLIENT_ID = VF.CLIENT_ID AND
VENDA = 'S' AND
STATUS_VENDA = 'P' AND
STATUS_FATURAMENTO = '0' AND
STATUS_COBRANCA = '0' AND
FORMA_PAGTO in ('-3', '0') AND
DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 2 ), VF.CLIENT_GROUP_ID) = DECODE(P_CLIENT_GROUP,'T',DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 2 ), VF.CLIENT_GROUP_ID) ,P_CLIENT_GROUP) AND
DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 1 ), VF.CLIENT_ID) = DECODE(P_CLIENT,'T',DECODE(Fu_Get_Empresa_Ativa(VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, VF.VIDA), 'S', Fu_Ret_Client_Group_Atual( VF.VIDA, VF.COD_PLANO, VF.CLIENT_ID, VF.CLIENT_GROUP_ID, 1 ), VF.CLIENT_ID) ,P_CLIENT) AND
COD_PLANO = P_CUSTOMER AND
DATE_FILLED BETWEEN P_DATA_INI AND
P_DATA_FIM)
GROUP BY MATRICULA, COD_FILIAL, FPAGTO
ORDER BY COD_FILIAL;
-- NOTA
CURSOR P_DESCFOLHA_NOTA (P_NOTA IN VARCHAR2) IS
-- desconto
SELECT MATRICULA,
SUM(VALOR_DESCONTO) AS VALOR,
COD_FILIAL,
FPAGTO
FROM(
SELECT
PATIENT_PIN_NUMBER AS MATRICULA,
DESC_FOLHA_PAGTO AS VALOR_DESCONTO,
CASE
WHEN MB.CLIENT_ID IN ('CANOAS') THEN LPAD('002', 3, '0')
ELSE LPAD('003', 3, '0')
END AS COD_FILIAL,
DECODE(FORMA_PAGTO, '-1', '536', '306') AS FPAGTO
FROM MEMBER MB, V_MOVIMENTACAO VF
WHERE MB.MEMBER_ID = VF.VIDA AND
MB.CUSTOMER_ID = VF.COD_PLANO AND
MB.PERSON_CODE = VF.PERSON_CODE AND
MB.CLIENT_GROUP_ID = VF.CLIENT_GROUP_ID AND
MB.CLIENT_ID = VF.CLIENT_ID AND
VF.NUM_NF_CONV = P_NOTA AND
FORMA_PAGTO = '-1')
GROUP BY MATRICULA, COD_FILIAL, FPAGTO
UNION ALL
-- devolucao
SELECT MATRICULA,
SUM(VALOR_DESCONTO) AS VALOR,
COD_FILIAL,
FPAGTO
FROM(
SELECT
PATIENT_PIN_NUMBER AS MATRICULA,
DESC_FOLHA_PAGTO AS VALOR_DESCONTO,
CASE
WHEN MB.CLIENT_ID IN ('CANOAS') THEN LPAD('002', 3, '0')
ELSE LPAD('003', 3, '0')
END AS COD_FILIAL,
DECODE(FORMA_PAGTO, '-1', '536', '306') AS FPAGTO
FROM MEMBER MB, V_MOVIMENTACAO VF
WHERE MB.MEMBER_ID = VF.VIDA AND
MB.CUSTOMER_ID = VF.COD_PLANO AND
MB.PERSON_CODE = VF.PERSON_CODE AND
MB.CLIENT_GROUP_ID = VF.CLIENT_GROUP_ID AND
MB.CLIENT_ID = VF.CLIENT_ID AND
VF.NUM_NF_CONV = P_NOTA AND
FORMA_PAGTO = '-3')
GROUP BY MATRICULA, COD_FILIAL, FPAGTO
ORDER BY COD_FILIAL;
-------------------------------------------------------------------------------------------------------------------
/* DECLARACAO DOS CURSORES */
vTST_CURSOR P_DESCFOLHA_PERIODO%ROWTYPE; -- PERIODO
vTST_CURSOR2 P_DESCFOLHA_NOTA%ROWTYPE; -- NOTA
-------------------------------------------------------------------------------------------------------------------
BEGIN
vCODFILIAL_1 := '0';
vCONTADOR := 0;
vSQL := 'ALTER SESSION SET NLS_DATE_FORMAT = "DD-MM-YYYY"';
EXECUTE IMMEDIATE VSQL;
SELECT Pega_Path_Oracle(UPPER(P_DIRETORIO)) INTO vDIRECTORY_PATH FROM DUAL;
SELECT TO_CHAR(P_DATA_FIM,'YYYY-MM') INTO VDATA FROM DUAL;
-------------------------------------------------------------------------------------------------------------------
IF P_TIPO <> 'N' THEN -- PERÍODO
IF NOT P_DESCFOLHA_PERIODO%ISOPEN THEN
OPEN P_DESCFOLHA_PERIODO ( P_DATA_INI,
P_DATA_FIM,
P_CLIENT,
P_CLIENT_GROUP,
P_CUSTOMER );
END IF;
LOOP
FETCH P_DESCFOLHA_PERIODO INTO vTST_CURSOR;
EXIT WHEN P_DESCFOLHA_PERIODO%NOTFOUND;
IF vTST_CURSOR.COD_FILIAL <> vCODFILIAL_1 THEN
IF vCONTADOR > 0 THEN
SYS.UTL_FILE.FCLOSE(SAIDA1);
END IF;
vCONTADOR := vCONTADOR + 1 ;
vCODFILIAL_1 := vTST_CURSOR.COD_FILIAL;
vNAME_FILE := 'MWM_'||VDATA|| '_' || vCODFILIAL_1 || '.TXT';
SAIDA1 := SYS.UTL_FILE.FOPEN( VDIRECTORY_PATH, vNAME_FILE, 'W');
-- HEADER
SYS.UTL_FILE.PUT_LINE( SAIDA1,
LPAD( vCONVENIADO, 3, '0') ||
REPLACE(LAST_DAY(TO_DATE(SYSDATE)),'-','/') ||
vTST_CURSOR.COD_FILIAL);
END IF;
-- CORPO
IF vTST_CURSOR.FPAGTO = '600' THEN
SYS.UTL_FILE.PUT_LINE( SAIDA1,
LPAD( vTST_CURSOR.MATRICULA, 6, '0') ||
-- LPAD( '600', 3, 0) ||
vTST_CURSOR.FPAGTO ||
TO_CHAR(LPAD( vTST_CURSOR.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.'''));
--LPAD( vTST_CURSOR.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.''');
ELSE
SYS.UTL_FILE.PUT_LINE( SAIDA1,
LPAD( vTST_CURSOR.MATRICULA, 6, '0') ||
--LPAD( '284', 3, 0) ||
vTST_CURSOR.FPAGTO ||
TO_CHAR(LPAD( vTST_CURSOR.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.'''));
--LPAD( vTST_CURSOR.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.''');
END IF;
IF vTST_CURSOR.COD_FILIAL <> vCODFILIAL_1 THEN
vCODFILIAL_1 := vTST_CURSOR.COD_FILIAL;
END IF;
END LOOP;
ELSE -- NOTA
IF NOT P_DESCFOLHA_NOTA%ISOPEN THEN
OPEN P_DESCFOLHA_NOTA ( P_NOTA );
END IF;
LOOP
FETCH P_DESCFOLHA_NOTA INTO vTST_CURSOR2;
EXIT WHEN P_DESCFOLHA_NOTA%NOTFOUND;
IF vTST_CURSOR2.COD_FILIAL <> vCODFILIAL_1 THEN
IF vCONTADOR > 0 THEN
SYS.UTL_FILE.FCLOSE(SAIDA1);
END IF;
vCONTADOR := vCONTADOR + 1 ;
vCODFILIAL_1 := vTST_CURSOR2.COD_FILIAL;
vNAME_FILE := 'MWM_'||VDATA|| '_' || vCODFILIAL_1 || '.TXT';
SAIDA1 := SYS.UTL_FILE.FOPEN( VDIRECTORY_PATH, vNAME_FILE, 'W');
-- HEADER
SYS.UTL_FILE.PUT_LINE( SAIDA1,
LPAD( vCONVENIADO, 3, '0') ||
REPLACE(LAST_DAY(TO_DATE(SYSDATE)),'-','/') ||
vTST_CURSOR2.COD_FILIAL);
END IF;
-- CORPO
IF vTST_CURSOR2.FPAGTO = '536' THEN
SYS.UTL_FILE.PUT_LINE( SAIDA1,
LPAD( vTST_CURSOR2.MATRICULA, 6, '0') ||
LPAD( '536', 3, '0') ||
--vTST_CURSOR.FPAGTO ||
TO_CHAR(LPAD( vTST_CURSOR2.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.'''));
--LPAD( vTST_CURSOR2.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.''');
ELSE
SYS.UTL_FILE.PUT_LINE( SAIDA1,
LPAD( vTST_CURSOR2.MATRICULA, 6, '0') ||
LPAD( '306', 3, '0') ||
--vTST_CURSOR.FPAGTO ||
TO_CHAR(LPAD( vTST_CURSOR2.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.'''));
--LPAD( vTST_CURSOR2.VALOR, 10, '0'), '0999999D99', 'nls_numeric_characters='',.''');
END IF;
IF vTST_CURSOR2.COD_FILIAL <> vCODFILIAL_1 THEN
vCODFILIAL_1 := vTST_CURSOR2.COD_FILIAL;
END IF;
END LOOP;
END IF;
SYS.UTL_FILE.FCLOSE(SAIDA1);
A quem puder ajudar, muitíssimo obrigado.