Additional Parameter Operating Unit in R12

You must have observed additional parameter (Operating Unit) option while running the concurrent program in R12. It's due to MOAC feature to run the program for selective OU only.

This additional parameter is controlled from System Administrator responsibility Self service version.

This post is to help how to pass value to this parameter if we are triggering such programs from database.

DECLARE
l_request_id  NUMBER;
BEGIN
           dbms_output.put_line('OAL - START::');
           FND_REQUEST.SET_ORG_ID(82); 
          -- This is required to set the value for additional Operating Unit Parameter

         l_request_id :=
         fnd_request.submit_request( 'APPLICATION_SHORT_NAME' ,'CPSHORTNAME' ,NULL ,NULL ,TRUE,Arg1,Arg2......);


           dbms_output.put_line('OAL - END::'|| l_request_id );

EXCEPTION
WHEN OTHERS THEN
      dbms_output.put_line('OAL - Exception ::'||SQLERRM);
END;

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