eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_NL_ABP_REPORT SQL Statements

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

Line: 16


SELECT  meaning
FROM    fnd_lookup_values
WHERE   lookup_type = c_lookup_type
AND     language = c_language
AND     lookup_code = c_lookup_code;

Line: 43


        SELECT tag
          FROM fnd_lookup_values
         WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
           AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
                                    INSTR(USERENV('LANGUAGE'), '.') + 1)
           AND language = 'US';

Line: 133


SELECT  name
FROM    per_organization_structures
WHERE   organization_structure_id = p_org_struct_id;

Line: 138


SELECT  name
FROM    hr_organization_units
WHERE   organization_id = p_org_id;

Line: 199


SELECT  name
FROM    hr_organization_units
WHERE   organization_id = p_org_id;

Line: 276


SELECT  pai.action_information1 emp_num
       ,pai.action_information2 asg_num
       ,to_char(fnd_date.canonical_to_date(pai.action_information3),'RRRR-MM-DD') asg_start_date
       ,to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') asg_end_date
       ,pai.action_information5 term_reason
       ,decode (pai.action_information5
       ,'R', 'A'
       ,'A', 'R'
       ,'O', 'I'
       ,pai.action_information5) term_reason_pdf
       ,pai.action_information6 kind_emp
       ,pai.assignment_id assignment_id
FROM    pay_action_information pai
WHERE   pai.action_context_id = c_assignment_action_id
AND     pai.action_information_category = 'NL_ABP_ASG_INFO'
AND     pai.action_context_type = 'AAP';

Line: 294


SELECT  pai.action_information2 employee_number
       ,pai.action_information3 sofi_number
       ,pai.action_information4 last_name
       ,pai.action_information5 init
       ,pai.action_information6 prefix
       ,to_char(fnd_date.canonical_to_date(pai.action_information7),'RRRR-MM-DD') date_of_birth
       ,pai.action_information8 nationality
       ,pai.action_information9 gender
       ,to_char(fnd_date.canonical_to_date(pai.action_information10),'RRRR-MM-DD') date_of_death
       ,pai.action_information11 p_last_name
       ,pai.action_information12 p_prefix
       ,pai.action_information13 p_init
       ,pai.action_information14 street
       ,pai.action_information15 house_number
       ,pai.action_information16 house_number_addition
       ,pai.action_information17 location
       ,pai.action_information18 postal_code
       ,pai.action_information19 city
       ,pai.action_information20 region
       ,pai.action_information21 iso
       ,pai.action_information22 style
       ,pai.action_information23 marital_status
       ,to_char(fnd_date.canonical_to_date(pai.action_information24),'RRRR-MM-DD') marital_status_sd
       ,pai.action_information25 indv_exe
       ,to_char(fnd_date.canonical_to_date(pai.action_information26),'RRRR-MM-DD') start_date
       ,to_char(fnd_date.canonical_to_date(pai.action_information27),'RRRR-MM-DD') end_date
       ,pai.action_information28 person_data_flag
FROM    pay_action_information pai
WHERE   pai.action_context_id = c_assignment_action_id
AND     pai.action_information_category = 'NL_ABP_EMP_INFO'
AND     pai.action_context_type = 'AAP';

Line: 327


SELECT  to_char(fnd_date.canonical_to_date(pai.action_information1),'RRRR-MM-DD') start_date
       ,to_char(fnd_date.canonical_to_date(pai.action_information2),'RRRR-MM-DD') end_date
       ,pai.action_information3 kop
       ,pai.action_information4 income_code
       ,fnd_number.canonical_to_number(pai.action_information5) ptp
       ,pai.action_information6 code_inc
FROM    pay_action_information pai
WHERE   pai.action_context_id = c_assignment_action_id
AND     pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
AND     action_context_type = 'AAP'
ORDER BY start_date;

Line: 340


SELECT  pai.action_information1 pension_type
       ,pai.action_information2 pension_type_variant
       ,to_char(fnd_date.canonical_to_date(pai.action_information3),'RRRR-MM-DD') pension_type_sdate
       ,to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') pension_type_info_sdate
       ,pai.action_information5 wage_type
       ,fnd_number.canonical_to_number(pai.action_information6) abp_pension_base
       ,fnd_number.canonical_to_number(pai.action_information7) contr_base
       ,fnd_number.canonical_to_number(pai.action_information8) contr_amt
       ,fnd_number.canonical_to_number(pai.action_information9) vop
       ,to_char(fnd_date.canonical_to_date(pai.action_information10),'RRRR-MM-DD') pension_type_info_sdate_multi  --Bug 16301893
       ,to_char(fnd_date.canonical_to_date(pai.action_information11),'RRRR-MM-DD') pension_type_edate
FROM    pay_action_information pai
WHERE   pai.action_context_id = c_assignment_action_id
AND     pai.action_information_category = 'NL_ABP_PENSION_INFO'
AND     action_context_type = 'AAP'
AND     to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') <= c_effective_date; --Bug 16301893

Line: 358


SELECT  ppa.payroll_action_id
FROM    pay_payroll_actions ppa
       ,pay_assignment_actions paa
WHERE   paa.payroll_action_id = ppa.payroll_action_id
AND     paa.assignment_action_id = c_assignment_action_id;

Line: 365


SELECT shared_type_name
FROM   per_shared_types
WHERE  lookup_type        = 'NL_NATIONALITY'
AND    information1       = c_code
ORDER BY 1;

Line: 372


SELECT  pension_type
      , pension_type_variant
      , abp_pension_base
      , sum (contr_base) contr_base
      , sum (contr_amt) contr_amt
FROM    (
        SELECT  pai.action_information1 pension_type
              , pai.action_information2 pension_type_variant
              , pai.action_information4 pension_type_info_sdate
              , nvl (fnd_number.canonical_to_number (pai.action_information6), 0) abp_pension_base
              , avg (nvl (fnd_number.canonical_to_number (pai.action_information7), 0)) contr_base
              , avg (nvl (fnd_number.canonical_to_number (pai.action_information8), 0)) contr_amt
        FROM    pay_action_information pai
        WHERE   pai.action_context_id = c_action_context_id
        AND     pai.action_information_category = 'NL_ABP_PENSION_INFO'
        AND     action_context_type = 'AAP'
        GROUP BY pai.action_information1
               , pai.action_information2
               , pai.action_information4
	       , pai.action_information6
        )
GROUP BY pension_type
       , pension_type_variant
       , abp_pension_base;

Line: 665


SELECT action_information1 reg_num_sender
      ,to_char(fnd_date.canonical_to_date(action_information2),'YYYYMM') period
      ,action_information3 seq_num
      ,action_information4 version
      ,action_information5 creation_date
      ,action_information6 period_type
      ,action_information7 prod_test
      ,action_information8 name_sender
      ,action_information9 release
      ,to_char(fnd_date.canonical_to_date(action_information10),'MM YYYY') ref_file
FROM    pay_action_information pai
WHERE   pai.action_context_id = c_payroll_action_id
AND     pai.action_information_category = 'NL_ABP_SENDER_INFO'
AND     pai.action_context_type = 'PA';

Line: 682


  SELECT action_information1 er_num
        ,action_information2 freq
        ,to_char(fnd_date.canonical_to_date(action_information3),'RRRR-MM-DD') start_date
        ,to_char(fnd_date.canonical_to_date(action_information4),'RRRR-MM-DD') end_date
        ,action_information5 ref_er
        ,action_information6 org_id
  FROM    pay_action_information pai
  WHERE   pai.action_context_id = c_payroll_action_id
  AND     pai.action_information_category = 'NL_ABP_EMPLOYER_INFO'
  AND     ACTION_CONTEXT_TYPE = 'PA';

Line: 694


    SELECT  put.user_table_id
           ,puc.user_column_id
    FROM    pay_user_tables put
           ,pay_user_columns puc
    WHERE   put.user_table_id = puc.user_table_id
    AND     put.legislation_code = puc.legislation_code
    AND     put.user_table_name = 'NL_DIACRITICAL_MARKS'
    AND     put.legislation_code = 'NL';

Line: 707


    SELECT  DISTINCT
            UPPER (purf.row_low_range_or_name) Source
           ,UPPER (pucif.value) Target
    FROM    pay_user_column_instances_f pucif
           ,pay_user_rows_f purf
    WHERE   pucif.user_column_id = p_user_column_id
    AND     purf.user_table_id = p_user_table_id
    AND     pucif.user_row_id = purf.user_row_id
    AND     pucif.business_group_id = purf.business_group_id
    AND     pucif.business_group_id = p_business_group_id
    AND     p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
    AND     p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;

Line: 721


    SELECT business_group_id bg_id
          ,start_date start_date
    FROM  pay_payroll_actions
    WHERE payroll_action_id = p_payroll_action_id;

Line: 746


  SELECT  assignment_action_id ass_act_id
  FROM    pay_assignment_actions
  WHERE   source_action_id = c_assignment_action_id;

Line: 752


  SELECT  assignment_action_id
  FROM    pay_assignment_actions
  WHERE   payroll_action_id = c_payroll_action_id
  AND     tax_unit_id = c_employer_id;

Line: 758


  SELECT  1
  FROM    pay_action_information pai
  WHERE   pai.action_context_id = c_assignment_action_id
  AND     pai.action_information_category = 'NL_ABP_ASG_INFO'
  AND     pai.action_context_type = 'AAP';

Line: 765


  SELECT  pai.action_information1 emp_num
        , pai.action_information2 asg_num
        , to_char (fnd_date.canonical_to_date (pai.action_information3), 'RRRR-MM-DD') start_date
        , pai.action_information4 sofi_num
        , pai.action_information5 code_withdrawal
  FROM    pay_action_information pai
  WHERE   pai.action_context_id IN
          (
          SELECT  assignment_action_id
          FROM    pay_assignment_actions
          WHERE   payroll_action_id = c_payroll_action_id
          )
  AND     pai.action_context_type = 'AAP'
  AND     pai.action_information_category IN ('NL_ABP_COMP_WITHDRAWAL_INFO', 'NL_ABP_WITHDRAWAL_INFO')
  ORDER BY emp_num,asg_num,sofi_num,start_date;

Line: 937


xXMLTable.delete;

Line: 1033


  UPDATE  fnd_concurrent_requests
  SET     output_file_type = 'XML'
  WHERE   request_id = fnd_global.conc_request_id;