how to fetch data into the cursor.

User_5U3UI

Hi using cursor, i need to fetch the end date from fnd_user table and update it in XXDEV_CU165_HR_APPR_LIMITS table.How to achieve this.

PROCEDURE XX_PROC IS

CURSOR CUR_INACTIVE_USER IS

 SELECT fu.user_id,fu.end_date

 FROM fnd_user fu, cu165_hr_appr_limits hral,

 where fu.employee_id =hral.person_id

 AND fu.end_date IS NOT NULL;

BEGIN

 FOR rec_inactive_user IN cur_inactive_user LOOP

     UPDATE XXDEV_CU165_HR_APPR_LIMITS HRAL

SET HRAL.END_DATE_ACTIVE = (

                              SELECT FU.END_DATE

                                FROM FND_USER FU

                                WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID

                             )

  WHERE EXISTS(

               SELECT 1

                FROM FND_USER FU

                WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID

                AND FU.END_DATE IS NOT NULL

              )

   AND (

           HRAL.END_DATE_ACTIVE IS NULL

        OR

           HRAL.END_DATE_ACTIVE > sysdate

       );

 END LOOP;

 COMMIT;

END XX_PROC;

Solomon Yakobson

Using PL/SQL for something that can be done via relatively simple SQL statement is in most cases inefficient. Anyway:

CREATE OR REPLACE
  PROCEDURE XX_PROC
    IS
        CURSOR CUR_INACTIVE_USER
          IS
            SELECT  FU.EMPLOYEE_ID,
                    FU.END_DATE
              FROM  FND_USER FU,
                    CU165_HR_APPR_LIMITS HRAL,
              WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID
                AND FU.END_DATE IS NOT NULL
                AND (
                        HRAL.END_DATE_ACTIVE IS NULL
                     OR
                        HRAL.END_DATE_ACTIVE > SYSDATE
                    );
    BEGIN
        FOR REC_INACTIVE_USER IN CUR_INACTIVE_USER LOOP
          UPDATE XXDEV_CU165_HR_APPR_LIMITS HRAL
             SET HRAL.END_DATE_ACTIVE = REC_INACTIVE_USER.END_DATE
           WHERE HRAL.PERSON_ID = REC_INACTIVE_USER.EMPLOYEE_ID;
        END LOOP;
        COMMIT;
END XX_PROC;
/

SY.

BEDE

Why do you need the cursor? One update is enough and is definitely more efficient than that cursor loop.

User_5U3UI

Here the requirement is

We need the closing of NASA user access to simultaneously close the same users “ fuldmagter” in the Fuldmagtsregister in NASA. Consequently the closing functionality in the FMP will have to be changed to immediately closing instead of closing the “ Fuldmagt” within the change to a new date, and we need the Fuldmagtsregister table to be linked to the NASA user Table.

Here cu165_hr_appr_limits is a Fuldmagtsregister table and fnd_user is the NASA user Table.so i thought of using cursor loop to update.

Is this the efficient way to achieve above requriment

CREATE OR REPLACE
  PROCEDURE XX_PROC
    IS
        CURSOR CUR_INACTIVE_USER
          IS
            SELECT  FU.EMPLOYEE_ID,
                    FU.END_DATE
              FROM  FND_USER FU,
                    CU165_HR_APPR_LIMITS HRAL,
              WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID
                AND FU.END_DATE IS NOT NULL
                AND (
                        HRAL.END_DATE_ACTIVE IS NULL
                     OR
                        HRAL.END_DATE_ACTIVE > SYSDATE
                    );
    BEGIN
        FOR REC_INACTIVE_USER IN CUR_INACTIVE_USER LOOP
          UPDATE XXDEV_CU165_HR_APPR_LIMITS HRAL
             SET HRAL.END_DATE_ACTIVE = REC_INACTIVE_USER.END_DATE
           WHERE HRAL.PERSON_ID = REC_INACTIVE_USER.EMPLOYEE_ID;
        END LOOP;
        COMMIT;
END XX_PROC;
/

Mike Kutz

Is this the efficient way to achieve above requriment

no. Use MERGE instead.

merge into REC_INACTIVE_USER hral
using ( /* sql from cursor */
            SELECT  FU.EMPLOYEE_ID,
                    FU.END_DATE
              FROM  FND_USER FU,
                    CU165_HR_APPR_LIMITS HRAL,
              WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID
                AND FU.END_DATE IS NOT NULL
                AND (
                        HRAL.END_DATE_ACTIVE IS NULL
                     OR
                        HRAL.END_DATE_ACTIVE > SYSDATE
                    )
) REC_INACTIVE_USER
on ( HRAL.PERSON_ID = REC_INACTIVE_USER.EMPLOYEE_ID ) /* same WHERE clause */ 
When matched then update set
  HRAL.END_DATE_ACTIVE = REC_INACTIVE_USER.END_DATE; /* same SET clause */