APPS.PQP_UK_UNION_DEDUCTION SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 42

   SELECT TO_NUMBER(eei.eei_information1) -- Union Organisation_ID
         ,eei.eei_information2            -- Union Level Balance Name
         ,hrl.meaning                     -- Pension Rate Type Name
         ,eei.eei_information4            -- Union Funds Lookup Type - Fund List
   FROM   pay_element_types_f         ele
         ,pay_element_type_extra_info eei
         ,hr_lookups                  hrl
         ,fnd_sessions                fnd
   WHERE  ele.element_type_id  = p_element_type_id
     AND  eei.element_type_id  = ele.element_type_id
     AND  eei.information_type = g_union_ele_extra_info_type
     AND  NVL(hrl.lookup_type,'PQP_RATE_TYPE') = 'PQP_RATE_TYPE'
     AND  NVL(hrl.enabled_flag,'Y') = 'Y'
     AND  hrl.lookup_code(+)   = eei.eei_information3
     AND  fnd.effective_date BETWEEN ele.effective_start_date
                                 AND ele.effective_end_date
     AND  fnd.session_id = USERENV('sessionid');

Line: 63

   SELECT TO_NUMBER(eei.eei_information1) -- Union Organisation_ID
         ,eei.eei_information2            -- Union Level Balance Name
         ,hrl.meaning                     -- Pension Rate Type Name
         ,eei.eei_information4            -- Union Funds Lookup Type - Fund List
   FROM   pay_element_types_f         ele
         ,pay_element_type_extra_info eei
         ,fnd_sessions                fnd
         ,(SELECT *
             FROM fnd_lookup_values
            WHERE lookup_type       =   'PQP_RATE_TYPE'
              AND enabled_flag      =   'Y') hrl
   WHERE  ele.element_type_id  = p_element_type_id
     AND  eei.element_type_id  = ele.element_type_id
     AND  eei.information_type = g_union_ele_extra_info_type
     AND  hrl.lookup_code(+)   = eei.eei_information3
     AND  fnd.effective_date BETWEEN ele.effective_start_date
                                 AND ele.effective_end_date
     AND  fnd.session_id = USERENV('sessionid');

Line: 161

   SELECT TO_NUMBER(hoi.org_information1) -- Rates Table ID
         ,tbls.user_table_name            -- Rates Table Name
         ,tbls.range_or_match             -- Rates Table Type 'R' or 'M'
         ,to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-MON')||'-'|| -- Recalculation Date
          DECODE( -- Compare the recalculation month to the effective month
                 SIGN(  -- By checking the difference between
                      (
                       TO_CHAR(fnd_date.canonical_to_date(hoi.org_information2),'MM')
                        -1
                      )  -- The month of the recalculation date less 1
                     -
                      (
                       TO_CHAR(fnds.effective_date,'MM')
                      )  -- The month of the current effective date
                     )
                ,-1      -- Recalculation month < than current month
                   , TO_CHAR(fnds.effective_date,'YYYY') -- use current year
                         -- Recalculation month >= than current month
                ,TO_CHAR(fnds.effective_date-365,'YYYY') -- use previous year
                )
   FROM   hr_organization_information hoi
         ,pay_user_tables tbls
         ,fnd_sessions fnds
   WHERE  hoi.organization_id = p_union_organization_id
     AND  hoi.org_information_context = g_union_org_info_type
     AND  tbls.user_table_id = TO_NUMBER(hoi.org_information1)
     AND  fnds.session_id = USERENV('sessionid');

Line: 259

   Select To_Number(hoi.org_information1) -- Rates Table ID
         ,tbls.user_table_name            -- Rates Table Name
         ,tbls.range_or_match             -- Rates Table Type 'R' or 'M'
         ,-- Recalculation Date
          DECODE( -- Compare the recalculation month to the effective month
                 SIGN(  -- By checking the difference between
                      (
                       TO_CHAR(fnd_date.canonical_to_date(hoi.org_information2),'MM')
                        -1
                      )  -- The month of the recalculation date less 1
                     -
                      (
                       TO_CHAR(fnds.effective_date,'MM')
                      )  -- The month of the current effective date
                     )
                ,-1      -- Recalculation month < than current month
                   , TO_CHAR(fnds.effective_date,'YYYY') -- use current year
                         -- Recalculation month >= than current month
                ,TO_CHAR(fnds.effective_date-365,'YYYY') -- use previous year
                )
           ||'/'||to_char(fnd_date.canonical_to_date(hoi.org_information2),'MM/DD')
   FROM   hr_organization_information hoi
         ,pay_user_tables tbls
         ,fnd_sessions fnds
   WHERE  hoi.organization_id = p_union_organization_id
     AND  hoi.org_information_context = g_union_org_info_type
     AND  tbls.user_table_id = TO_NUMBER(hoi.org_information1)
     AND  fnds.session_id = USERENV('sessionid');

Line: 338

FUNCTION chk_uk_union_fund_selected
          (p_union_rates_column_name IN   VARCHAR2
          ,p_union_rates_table_name  IN   VARCHAR2
          ,p_ERROR_MESSAGE           IN OUT NOCOPY  VARCHAR2
          )
  RETURN NUMBER
IS

   l_proc     VARCHAR2(61):= g_proc||'chk_uk_union_fund_selected';

Line: 352

  CURSOR csr_uk_union_fund_selected IS
  SELECT NULL
  FROM   pay_user_columns cols
        ,pay_user_tables  tbls
  WHERE  tbls.user_table_name =  p_union_rates_table_name
    AND  tbls.user_table_id = cols.user_table_id
    AND  cols.user_column_name = p_union_rates_column_name;

Line: 366

  OPEN csr_uk_union_fund_selected;

Line: 368

  FETCH csr_uk_union_fund_selected
   INTO p_ERROR_MESSAGE;

Line: 371

  IF csr_uk_union_fund_selected%NOTFOUND THEN

     l_ret_vlu := -1;

Line: 376

'Recreate the selected union fund taking care to use the original name. '||
'You must also recreate the Union Rates table for this fund '||
'with separate columns for Union Fund Weekly and Union Fund Monthly.';

Line: 382

  CLOSE csr_uk_union_fund_selected;

Line: 395

END chk_uk_union_fund_selected;

Line: 412

  SELECT distinct urws.row_low_range_or_name
  FROM   pay_user_rows_f  urws
        ,pay_user_tables  tbls
        ,fnd_sessions     fnd
  WHERE  tbls.user_table_name = p_union_rates_table_name
    AND  tbls.range_or_match = 'M'
    AND  urws.user_table_id = tbls.user_table_id
	AND  urws.business_group_id = tbls.business_group_id
	AND	 urws.business_group_id = l_business_group_id
    AND  fnd.effective_date BETWEEN urws.effective_start_date
                                AND urws.effective_end_date
    AND  fnd.session_id = USERENV('sessionid');