User Define Table In Oracle Apps Query

Query to Fetch UDT Value

SELECT pucif.VALUE
          FROM apps.pay_user_column_instances_f pucif
               ,pay_user_tables put
               ,pay_user_rows_f purf
               ,pay_user_columns puc
          WHERE put.user_table_name = p_table_name
            AND put.business_group_id = p_business_group_id
            AND put.user_table_id = purf.user_table_id
            AND purf.row_low_range_or_name = p_row_name
            AND put.user_table_id = puc.user_table_id
            AND puc.user_column_name = p_column_name
            AND pucif.user_row_id = purf.user_row_id
            AND pucif.user_column_id = puc.user_column_id
            AND p_effective_date BETWEEN purf.effective_start_date AND purf.effective_end_date
            AND p_effective_date BETWEEN pucif.effective_start_date AND pucif.effective_end_date;
     

FUNCTION SET_GLOBAL_USER_TABLES(p_fiscal_year IN VARCHAR2)
RETURN VARCHAR2 is

l_us_user_table_id number;
l_user_column_id number;
l_rowid varchar2(200);
l_ovn_Number number;
l_user_col_inst_id number;
l_eff_st_dt date;
l_eff_end_dt date;
l_update varchar2(1);
l_ret_val varchar2(1) := 'N';
l_fiscal_start_date date;
l_fiscal_end_date date;

CURSOR CUR_TABLES IS
SELECT *
FROM PAY_USER_TABLES
WHERE USER_TABLE_NAME = 'UDT NAME'
AND BUSINESS_GROUP_ID <> 81;

CURSOR CUR_TARGET_TABLES IS
SELECT *
FROM PAY_USER_TABLES
WHERE USER_TABLE_NAME = 'UDT NAME'
AND BUSINESS_GROUP_ID <> 81;

CURSOR cur_rows(P_USER_TABLE_ID NUMBER, P_FISCAL_YEAR VARCHAR2,P_END_DATE DATE) IS
SELECT A.*,
       C.ROW_LOW_RANGE_OR_NAME,
       B.EFFECTIVE_START_DATE,
       B.EFFECTIVE_END_DATE, B.USER_ROW_ID,
       B.VALUE
FROM   PAY_USER_COLUMNS A,
       PAY_USER_COLUMN_INSTANCES_F B,
       PAY_USER_ROWS_F C
WHERE C.USER_TABLE_ID = P_USER_TABLE_ID
  AND A.USER_COLUMN_NAME = NVL(P_FISCAL_YEAR, A.USER_COLUMN_NAME)
  AND A.USER_COLUMN_ID = B.USER_COLUMN_ID
  AND P_END_DATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
  AND P_END_DATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE
  AND A.USER_TABLE_ID = C.USER_TABLE_ID
  AND C.USER_ROW_ID = B.USER_ROW_ID;

CURSOR cur_row_name(P_USER_TABLE_ID NUMBER, ROW_NAME VARCHAR2) IS
SELECT *
FROM PAY_USER_ROWS_F
WHERE USER_TABLE_ID = P_USER_TABLE_ID
  AND ROW_LOW_RANGE_OR_NAME = ROW_NAME
  AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

BEGIN
    SELECT USER_TABLE_ID
    INTO L_US_USER_TABLE_ID
    FROM PAY_USER_TABLES
    WHERE USER_TABLE_NAME = 'UDT Name'
    AND BUSINESS_GROUP_ID = 81;

  FOR i IN cur_tables LOOP
    BEGIN
    l_rowid := NULL;
    l_ovn_number := NULL;
    l_user_column_id := NULL;
    l_update := 'N';

        BEGIN
            SELECT user_column_id
            INTO l_user_column_id
            FROM PAY_USER_COLUMNS
            WHERE user_table_id = i.user_table_id
            AND user_column_name = p_fiscal_year;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
              l_update := 'Y';
        WHEN OTHERS THEN
            fnd_file.put_line(fnd_file.LOG,'Unknown Error while getting User Column ' || substr(SQLERRM, 1, 512));
        END;

         IF(l_update = 'Y') THEN
         PAY_USER_COLUMN_API.create_user_column
          (p_validate                    => false
          ,p_business_group_id           => i.business_group_id
          ,p_user_table_id               => i.user_table_id
          ,p_user_column_name            => p_fiscal_year
          ,p_user_column_id              => l_user_column_id
          ,p_object_version_number       => l_ovn_number
          );
          END IF;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        fnd_file.put_line(fnd_file.LOG,'Unknown Error while creating User Column ' || substr(SQLERRM, 1, 512));
    END;
  END LOOP;

FOR j IN cur_rows(l_us_user_table_id, p_fiscal_year,l_fiscal_end_date) LOOP
BEGIN
FOR k IN cur_tables LOOP
BEGIN
SELECT user_column_id
INTO l_user_column_id
FROM PAY_USER_COLUMNS
WHERE user_table_id = k.user_table_id
AND user_column_name = p_fiscal_year;

   FOR l IN cur_row_name(k.user_table_id, j.row_low_range_or_name) LOOP
   BEGIN
   l_ovn_number := NULL;
   l_user_col_inst_id := NULL;
   l_eff_st_dt := null;
   l_eff_end_dt := null;
   l_update := 'N';

   BEGIN
   SELECT user_column_instance_id
   INTO l_user_col_inst_id
   FROM PAY_USER_COLUMN_INSTANCES_f
   WHERE user_row_id = l.user_row_id AND user_column_id = l_user_column_id
   AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

   FOR m IN (SELECT *
             FROM PAY_USER_COLUMN_INSTANCES_f
             WHERE user_column_instance_id  = l_user_col_inst_id
             AND  TRUNC(SYSDATE) BETWEEN effective_start_date and effective_end_date)
   LOOP
   BEGIN

   IF (nvl(m.value, '~~') != nvl(j.value, '~~')) THEN
    PAY_USER_COLUMN_INSTANCE_API.update_user_column_instance
    (
         p_validate                   => false
        ,p_effective_date             => m.effective_start_date
        ,p_value                      => j.value
        ,p_datetrack_update_mode      => 'CORRECTION'
        ,p_user_column_instance_id    => l_user_col_inst_id
        ,p_object_version_number      => m.object_version_number
        ,p_effective_start_date       => l_eff_st_dt
        ,p_effective_end_date         => l_eff_end_dt
    );
    COMMIT;
   END IF;
  EXCEPTION
     WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG, 'Error While Updating Table Values ' || substr(SQLERRM,1,512));
   END;
   END LOOP;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       l_update := 'Y';
     WHEN OTHERS THEN
     fnd_file.put_line(fnd_file.LOG,'UnKnown Error while Getting table values ' || substr(SQLERRM, 1, 512));
   END;
    IF (l_update = 'Y') THEN
   

 PAY_USER_COLUMN_INSTANCE_API.create_user_column_instance
        (p_validate                   => false
        ,p_effective_date             => j.effective_start_date
        ,p_user_row_id                => l.user_row_id
        ,p_user_column_id             => l_user_column_id
        ,p_value                      => j.value
        ,p_business_group_id          => k.business_group_id
        ,p_user_column_instance_id    => l_user_col_inst_id
        ,p_object_version_number      => l_ovn_number
        ,p_effective_start_date       => l_eff_st_dt
        ,p_effective_end_date         => l_eff_end_dt
        );
        l_ret_val := 'Y';
        COMMIT;
    END IF;
      EXCEPTION
       WHEN OTHERS THEN
     fnd_file.put_line(fnd_file.LOG,'UnKnown Error while Updating table values ' || substr(SQLERRM, 1, 512));
   END;
   END LOOP;
   END;
END LOOP;
END;
END LOOP;