title: Oracle PL/SQL code return null
body: <p>I write this function in oracle PL/SQL to return counter of employee in the university based on their sex and collage nationality,sex , grade and qualification ETC. </p>
  
  <pre><code>FUNCTION GET_EMP_COUNT_GENERIC (
     P_ORGANIZATION_ID         VARCHAR2,
     P_GRADE                   VARCHAR2,
     P_SEX                     VARCHAR2,
     P_NAT_TYPE                VARCHAR2,
     P_DATE                    DATE,
     P_EMP_TYPE                VARCHAR2,
     P_QUALIFIC_CAT            VARCHAR2 )    
  RETURN NUMBER IS 
     V_COUNT   NUMBER;
  BEGIN
       SELECT   COUNT (EMP.PERSON_ID)  INTO   V_COUNT 
       FROM   PER_ALL_ASSIGNMENTS_F ASS,
              PER_ALL_PEOPLE_F EMP,
              HR_ALL_ORGANIZATION_UNITS ORG,
              PAY_PEOPLE_GROUPS PPG,
              PER_GRADES GRD             
       WHERE  ASS.PERSON_ID = EMP.PERSON_ID
              AND ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
              AND ASS.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID
              AND ASS.GRADE_ID = GRD.GRADE_ID
              AND EMP.PERSON_TYPE_ID = 1145        
              AND (ASS.ORGANIZATION_ID = P_ORGANIZATION_ID OR P_ORGANIZATION_ID IS NULL)
              AND (GRD.NAME = P_GRADE                      OR P_GRADE IS NULL)
              AND (EMP.SEX = P_SEX                         OR P_SEX IS NULL)
              AND (PPG.SEGMENT2 = P_NAT_TYPE               OR P_NAT_TYPE IS NULL)
              AND (PPG.SEGMENT1 =P_EMP_TYPE                OR P_EMP_TYPE IS NULL)
              AND (XXPNU_HCM_PKG.GET_QUALIFICATION_NAME(EMP.PERSON_ID,EMP.BUSINESS_GROUP_ID) 
                                = P_QUALIFIC_CAT   OR P_QUALIFIC_CAT  IS NULL)
              AND P_DATE BETWEEN ASS.EFFECTIVE_START_DATE
              AND  ASS.EFFECTIVE_END_DATE
              AND P_DATE BETWEEN EMP.EFFECTIVE_START_DATE
              AND  EMP.EFFECTIVE_END_DATE;                                 
     RETURN V_COUNT;    
  END GET_EMP_COUNT_GENERIC;
  </code></pre>
  
  <p>But when I call it in query, its return null or zero value??
  i dont know what is my mistake</p>
  
  <pre><code>        SELECT
  XXPNU_HCM_PKG.GET_EMP_COUNT_GENERIC (null,null,'M',null,:P_DATE,'FACULTY',null) as FACULTY_M,
  XXPNU_HCM_PKG.GET_EMP_COUNT_GENERIC (null,null,'F',null,:P_DATE,'FACULTY',null) as FACULTY_F
  --------------------------------TABLES------------------------------------------
  
    FROM                PER_ALL_ASSIGNMENTS_F ASS,
                        PER_ALL_PEOPLE_F EMP,
                        HR_ALL_ORGANIZATION_UNITS ORG,
                        PAY_PEOPLE_GROUPS PPG,
                        PER_GRADES GRD
  
  --------------------------------- CONDITIONS -----------------------------------
    WHERE                 
                        ASS.PERSON_ID = EMP.PERSON_ID
                        AND ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
                        AND ASS.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID
                        AND ASS.GRADE_ID = GRD.GRADE_ID
                        AND EMP.PERSON_TYPE_ID = 1145
  
  --------------------------------THIS YEAR DATE ---------------------------------
  
                       AND :P_DATE BETWEEN ASS.EFFECTIVE_START_DATE
                           AND  ASS.EFFECTIVE_END_DATE
                       AND :P_DATE BETWEEN EMP.EFFECTIVE_START_DATE
                           AND  EMP.EFFECTIVE_END_DATE ;
  </code></pre>