CREATE OR REPLACE PROCEDURE DETERMINA_PRODUCAO_RECEITA
(
 P_CDFILIAL IN VARCHAR2,
 P_CDRECEITA IN VARCHAR2,
 P_NRTIPONEGOCIOINF IN NUMBER,
 P_NRORG IN NUMBER,
 P_CDSETOR OUT VARCHAR2,
 P_NRCATEGORIAPROD OUT NUMBER,
 P_NRSUBCATEGORIAPROD OUT NUMBER
)
AS
BEGIN
 SELECT
  NVL(E_PPR.CDSETOR, T_PPR.CDSETOR),
  NVL(E_PPR.NRCATEGORIAPROD, T_PPR.NRCATEGORIAPROD),
  NVL(E_PPR.NRSUBCATEGORIAPROD, T_PPR.NRSUBCATEGORIAPROD)
 INTO P_CDSETOR, P_NRCATEGORIAPROD, P_NRSUBCATEGORIAPROD FROM DUAL
 LEFT JOIN PADRAOPRODREC T_PPR
  ON T_PPR.CDFILIAL = P_CDFILIAL
 AND T_PPR.CDRECEITA = P_CDRECEITA
 AND T_PPR.NRTIPONEGOCIOINF IS NULL
 AND T_PPR.NRORG = P_NRORG
 LEFT JOIN PADRAOPRODREC E_PPR 
  ON E_PPR.CDFILIAL = P_CDFILIAL
 AND E_PPR.CDRECEITA = P_CDRECEITA   
 AND E_PPR.NRORG = P_NRORG
 AND E_PPR.NRTIPONEGOCIOINF = P_NRTIPONEGOCIOINF;
END;
/