CREATE OR REPLACE PROCEDURE DETERMINA_TIPOPROD
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_CDMANCIA IN VARCHAR2,
P_NRSERVICOCIAFILI IN NUMBER,
P_CDCLASSE IN VARCHAR2,
P_CDSUBGRU IN VARCHAR2,
P_NRSEQSUBGRUSEMAM IN NUMBER,
P_NRORG IN NUMBER,
P_NRTIPOPROD OUT NUMBER
)
AS
BEGIN
  SELECT NVL(SUBTP.NRTIPOPROD, NVL(CIATP.NRTIPOPROD, CLATP.NRTIPOPROD))
  INTO P_NRTIPOPROD
  FROM DUAL
  LEFT JOIN CLASSETIPOPROD CLATP
    ON CLATP.CDFILIAL  = P_CDFILIAL
   AND CLATP.CDCLASSE = P_CDCLASSE
   AND CLATP.NRORG    = P_NRORG
  LEFT JOIN CLASSECIATIPOPROD CIATP
    ON CIATP.CDFILIAL  = P_CDFILIAL
   AND CIATP.CDCIA    = P_CDCIA
   AND CIATP.CDCLASSE = P_CDCLASSE
   AND CIATP.NRORG    = P_NRORG
  LEFT JOIN SUBGRUSEMAMTIPOPROD SUBTP
    ON SUBTP.CDFILIAL          = P_CDFILIAL
   AND SUBTP.CDCIA            = P_CDCIA
   AND SUBTP.CDMANCIA         = P_CDMANCIA
   AND SUBTP.NRSERVICOCIAFILI = P_NRSERVICOCIAFILI
   AND SUBTP.CDCLASSE         = P_CDCLASSE
   AND SUBTP.CDSUBGRU         = P_CDSUBGRU
   AND SUBTP.NRSEQSUBGRUSEMAM = P_NRSEQSUBGRUSEMAM
   AND SUBTP.NRORG            = P_NRORG;
END;
/