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
