SQL - Transaction Control Flag Validation at Project/Task Level

FUNCTION OAL_TXN_CONTROL(p_project_id NUMBER,p_task_id NUMBER, P_EXPENDITURE_TYPE VARCHAR2,P_EXPENDITURE_CATEGORY VARCHAR2,P_EXPENDITURE_ITEM_DATE DATE)
RETURN VARCHAR2
AS
    lv_txn_flag VARCHAR2(10);
    ln_count    NUMBER;
    LD_DATE DATE;
    LV_CHRGEFLAG VARCHAR2(10);
    LN_ACTIVE    NUMBER;
    lv_exp varchar2(100);
lv_txn_level VARCHAR2(10);
  BEGIN
     
   
SELECT limit_to_txn_controls_flag
      INTO lv_txn_flag
      FROM PA_TASKS A
      WHERE PROJECT_ID = P_PROJECT_ID
      AND task_id      = P_task_id ;

      IF LV_TXN_FLAG   = 'Y' THEN -- IF 100
   
        SELECT COUNT(1)
                INTO ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists -- IF 110
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN  -- IF 120
            RETURN 'TRUE';
          ELSE
            RETURN 'T';
          END IF;   -- IF 120

        ELSE -- not exists 110
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE TASK_ID = P_task_id
              AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
 IF LN_COUNT <> 0 THEN -- IF 120
             RETURN 'TRUE';
          ELSE
             RETURN 'T';
          END IF; -- 120
        END IF; -- 110
      ELSE
-- check if any record exists in control table else call PA level vaidation
SELECT COUNT(1)
                INTO ln_count        
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id;

IF ln_count <> 0 THEN
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;

        IF LN_COUNT      <> 0 THEN -- IF type exists -- 210
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <> 0 THEN  -- 220
            RETURN 'T';
          ELSE
            RETURN 'TRUE';
          END IF;  -- 220
   
ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE TASK_ID = P_task_id
          AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
          IF ln_count <> 0 THEN  -- 220
            RETURN 'T';
          ELSE
            RETURN 'TRUE';
          END IF;  -- 220

        END IF; --210
ELSE -- Project Level Validations
SELECT limit_to_txn_controls_flag
 INTO lv_txn_flag
 FROM PA_PROJECTS_ALL A
 WHERE PROJECT_ID = P_PROJECT_ID;
 IF LV_TXN_FLAG   = 'Y' THEN
   
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'P';
          END IF;

        ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE project_id = P_project_id
AND task_id is NULL
              AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         

          IF ln_count <>0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'P';
          END IF;

        END IF;

      ELSE
   
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
       WHERE project_id = P_project_id
AND task_id is NULL
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN
            RETURN 'P';
          ELSE
            RETURN 'TRUE';
          END IF;

        ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
 IF ln_count <>0 THEN
            RETURN 'P';
          ELSE
            RETURN 'TRUE';
          END IF;

        END IF;
    END IF;
END IF;
END IF;  

END ;


Return Values - 

• TRUE - No Failure
• P - Failed at Project Level transaction control 
• T - Failed at Task Level transaction control