APPS.PAY_KW_MONTHLY_REPORTS SQL Statements

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

Line: 10

    SELECT org_information10
    FROM   hr_organization_information
    WHERE  organization_id = p_business_group_id
    AND    org_information_context = 'Business Group Information';

Line: 38

    SELECT LPAD(org_information4,9,'0')
    FROM   hr_organization_information
    WHERE  organization_id = p_employer_id
    AND    org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';

Line: 45

    SELECT name
    FROM   hr_organization_units
    WHERE  organization_id = p_employer_id;

Line: 51

    SELECT  u.creator_id
    FROM    ff_user_entities  u,
            ff_database_items d
    WHERE   d.user_name = p_user_name
    AND     u.user_entity_id = d.user_entity_id
    AND     u.legislation_code = 'KW'
    AND     u.business_group_id is null
    AND     u.creator_type = 'B';

Line: 136

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('sessionid'), l_effective_date);

Line: 141

      SELECT org_information1
      INTO l_local_nationality
      FROM hr_organization_information
      WHERE org_information_context = 'KW_BG_DETAILS'
      AND organization_id = p_business_group_id;

Line: 152

    vXMLTable.DELETE;

Line: 410

      SELECT distinct asg.assignment_id
                    ,paa.assignment_action_id
      FROM   per_all_assignments_f asg  /*per_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,hr_soft_coding_keyflex hscl
           ,per_all_people_f ppf   /*per_people_f ppf*/
      WHERE  asg.assignment_id = paa.assignment_id
      AND    asg.person_id = ppf.person_id
      AND    ppf.nationality = l_local_nationality
      AND    paa.payroll_action_id = ppa.payroll_action_id
      AND    ppa.action_type in ('R','Q')
      AND    ppa.action_status = 'C'
      AND    paa.action_status = 'C'
      AND    trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
      AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
      AND    trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
      AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
      AND    hscl.segment1 = to_char(p_employer_id);

Line: 440

        SELECT NVL(SUM(rrv.RESULT_VALUE),0)
        INTO   l_deduction_amt
        FROM   pay_element_entries_f  pee
                     ,pay_run_results  prr
                     ,pay_run_result_values  rrv
                     ,pay_input_values_f piv
        WHERE  rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
        AND    prr.assignment_action_id = rec_get_assacts.assignment_action_id
        AND    prr.element_entry_id = pee.element_entry_id
        AND    pee.assignment_id = rec_get_assacts.assignment_id
        AND    TRUNC(l_effective_date,'MM')  between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
        AND    pee.entry_information3 is not null
        AND    pee.entry_information3 IN ('71','65','72','999','82','73','85')
        AND    rrv.result_value IS NOT NULL
        AND    rrv.input_value_id = piv.input_value_id
        AND    piv.name = 'Pay Value'
        AND    prr.element_type_id = piv.element_type_id
        AND    pee.element_type_id = piv.element_type_id
        AND    TRUNC(l_effective_date,'MM')  between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));

Line: 571

    SELECT LPAD(org_information4,9,'0')
    FROM   hr_organization_information
    WHERE  organization_id = p_employer_id
    AND    org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';

Line: 578

    SELECT name
    FROM   hr_organization_units
    WHERE  organization_id = p_employer_id;

Line: 584

    SELECT  u.creator_id
    FROM    ff_user_entities  u,
            ff_database_items d
    WHERE   d.user_name = p_user_name
    AND     u.user_entity_id = d.user_entity_id
    AND     u.legislation_code = 'KW'
    AND     u.business_group_id is null
    AND     u.creator_type = 'B';

Line: 595

    SELECT distinct asg.person_id
                    ,paa.assignment_action_id
                    ,hscl.segment2
    FROM   per_assignments_f asg   /*per_all_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,hr_soft_coding_keyflex hscl
           ,per_people_f ppf   /*per_all_people_f ppf*/
    WHERE  asg.assignment_id = paa.assignment_id
    AND    asg.person_id = ppf.person_id
    AND    ppf.nationality = l_local_nationality
    AND    paa.payroll_action_id = ppa.payroll_action_id
    AND    ppa.action_type in ('R','Q')
    AND    ppa.action_status = 'C'
    AND    paa.action_status = 'C'
    AND    trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
    AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
    AND    trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
    AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
    AND    hscl.segment1 = to_char(p_employer_id);

Line: 619

    SELECT --SUBSTR(full_name,1,30)
           hr_person_name.get_person_name
           (p_person_id
           ,l_effective_date
           ,'DISPLAY_NAME'
           ,l_user_format)
    FROM   per_people_f ppf   --per_all_people_f ppf
    WHERE  person_id = p_person_id
    AND    l_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 698

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('sessionid'), l_effective_date);

Line: 704

      SELECT org_information1
      INTO l_local_nationality
      FROM hr_organization_information
      WHERE org_information_context = 'KW_BG_DETAILS'
      AND organization_id = p_business_group_id;

Line: 980

    SELECT LPAD(org_information4,9,'0')
    FROM   hr_organization_information
    WHERE  organization_id = p_employer_id
    AND    org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';

Line: 987

    SELECT name
    FROM   hr_organization_units
    WHERE  organization_id = p_employer_id;

Line: 993

    SELECT  u.creator_id
    FROM    ff_user_entities  u,
            ff_database_items d
    WHERE   d.user_name = p_user_name
    AND     u.user_entity_id = d.user_entity_id
    AND     u.legislation_code = 'KW'
    AND     u.business_group_id is null
    AND     u.creator_type = 'B';

Line: 1004

    SELECT distinct asg.person_id
                    ,paa.assignment_action_id
                    ,hscl.segment2
                    ,asg.assignment_id
    FROM   per_assignments_f asg   /*per_all_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,hr_soft_coding_keyflex hscl
           ,per_people_f ppf   /*per_all_people_f ppf*/
    WHERE  asg.assignment_id = paa.assignment_id
    AND    asg.person_id = ppf.person_id
    AND    ppf.nationality = l_local_nationality
    AND    paa.payroll_action_id = ppa.payroll_action_id
    AND    ppa.action_type in ('R','Q')
    AND    ppa.action_status = 'C'
    AND    paa.action_status = 'C'
    AND    trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
    AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
    AND    trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
    AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
    AND    hscl.segment1 = to_char(p_employer_id);

Line: 1029

    SELECT --SUBSTR(full_name,1,30)
           hr_person_name.get_person_name
           (p_person_id
           ,l_effective_date
           ,'DISPLAY_NAME'
           ,l_user_format)
    FROM   per_people_f ppf   --per_all_people_f ppf
    WHERE  person_id = p_person_id
    AND    l_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 1042

        SELECT NVL(SUM(rrv.RESULT_VALUE),0)
        FROM   pay_element_entries_f  pee
                     ,pay_run_results  prr
                     ,pay_run_result_values  rrv
                     ,pay_input_values_f piv
        WHERE  rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
        AND    prr.assignment_action_id = rec_get_emp.assignment_action_id
        AND    prr.element_entry_id = pee.element_entry_id
        AND    pee.assignment_id = rec_get_emp.assignment_id
        AND    TRUNC(l_effective_date,'MM')  between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
        AND    pee.entry_information3 is not null
        AND    pee.entry_information3 IN (p_deduction_code)
         /*( SELECT i.value
          FROM   pay_user_column_instances_f i
                 ,pay_user_rows_f r
                 ,pay_user_columns c
                 ,pay_user_tables t
          WHERE  (i.legislation_code = 'KW' AND i.business_group_id IS NULL)
          AND    (r.legislation_code = 'KW' AND r.business_group_id IS NULL)
          AND    c.legislation_code = 'KW'
          AND    t.legislation_code = 'KW'
          AND    UPPER(t.user_table_name) = UPPER('KW_DEDUCTION_MAPPING')
          AND    t.user_table_id = r.user_table_id
          AND    t.user_table_id = c.user_table_id
          AND    r.row_low_range_or_name = p_deduction_code
          AND    r.user_row_id = i.user_row_id
          AND    UPPER(c.user_column_name) = UPPER('DEDUCTION_TYPE')
          AND    c.user_column_id = i.user_column_id
          AND    TRUNC(l_effective_date,'MM') BETWEEN r.effective_start_date AND r.effective_end_date
          AND    TRUNC(l_effective_date,'MM') BETWEEN i.effective_start_date AND i.effective_end_date
          )*/
        AND    rrv.result_value IS NOT NULL
        AND    rrv.input_value_id = piv.input_value_id
        AND    piv.name = 'Pay Value'
        AND    prr.element_type_id = piv.element_type_id
        AND    pee.element_type_id = piv.element_type_id
        AND    TRUNC(l_effective_date,'MM')  between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));

Line: 1082

    SELECT  address_line_1 || decode(address_line_2,null,null,',') || address_line_2 || decode(postal_code,null,null,',')|| postal_code
    FROM     hr_locations hl
                   , hr_organization_units hou   /*hr_all_organization_units hou*/
    WHERE   hl.location_id = hou.location_id
    AND        hou.organization_id = p_employer_id;

Line: 1203

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('sessionid'), l_effective_date);

Line: 1209

      SELECT org_information1
      INTO l_local_nationality
      FROM hr_organization_information
      WHERE org_information_context = 'KW_BG_DETAILS'
      AND organization_id = p_business_group_id;

Line: 1614

    SELECT LPAD(org_information4,9,'0')
    FROM   hr_organization_information
    WHERE  organization_id = p_employer_id
    AND    org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';

Line: 1621

    SELECT name
    FROM   hr_organization_units
    WHERE  organization_id = p_employer_id;

Line: 1627

    SELECT  u.creator_id
    FROM    ff_user_entities  u,
            ff_database_items d
    WHERE   d.user_name = p_user_name
    AND     u.user_entity_id = d.user_entity_id
    AND     u.legislation_code = 'KW'
    AND     u.business_group_id is null
    AND     u.creator_type = 'B';

Line: 1640

    SELECT distinct asg.person_id
                    ,paa.assignment_action_id
                    ,pos.date_start
    FROM   per_assignments_f asg  /*per_all_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,hr_soft_coding_keyflex hscl
           ,per_periods_of_service pos
           ,per_people_f ppf /*per_all_people_f ppf*/
    WHERE  asg.assignment_id = paa.assignment_id
    AND    asg.person_id = ppf.person_id
    AND    ppf.nationality = l_local_nationality
    AND    paa.payroll_action_id = ppa.payroll_action_id
    AND    pos.period_of_service_id = asg.period_of_service_id
    AND    ppa.action_type in ('R','Q')
    AND    ppa.action_status = 'C'
    AND    paa.action_status = 'C'
    AND    trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
    AND    trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
    AND    trunc(pos.date_start, 'MM') = trunc(l_effective_date, 'MM')
    AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
    AND    trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
    AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
    AND    hscl.segment1 = to_char(p_employer_id);

Line: 1669

    SELECT distinct asg.person_id
                    ,paa.assignment_action_id
                    ,pos.actual_termination_date
                    ,ppa.date_earned
    FROM   per_assignments_f asg   /*per_all_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,hr_soft_coding_keyflex hscl
           ,per_periods_of_service pos
           ,per_people_f  ppf  /*per_all_people_f ppf*/
    WHERE  asg.assignment_id = paa.assignment_id
    AND    asg.person_id = ppf.person_id
    AND    ppf.nationality = l_local_nationality
    AND    paa.payroll_action_id = ppa.payroll_action_id
    AND    pos.period_of_service_id = asg.period_of_service_id
    AND    ppa.action_type in ('R','Q')
    AND    ppa.action_status = 'C'
    AND    paa.action_status = 'C'
    AND    trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
    AND    trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_effective_date, 'MM')
    AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
    AND    trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
    AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
    AND    hscl.segment1 = to_char(p_employer_id);

Line: 1697

    SELECT date_earned, paa.assignment_action_id
    FROM   per_assignments_f asg  /*per_all_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,per_periods_of_service pos
    WHERE  asg.assignment_id = paa.assignment_id
    AND    paa.payroll_action_id = ppa.payroll_action_id
    AND    pos.period_of_service_id = asg.period_of_service_id
    AND    ppa.action_type in ('R','Q')
    AND    ppa.action_status = 'C'
    AND    paa.action_status = 'C'
    AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
    AND    trunc(ppa.date_earned, 'MM') < TRUNC(l_effective_date, 'MM')
    AND    asg.person_id = p_person_id
    order by date_earned desc;

Line: 1716

    SELECT distinct asg.person_id
                    ,paa.assignment_action_id
                    ,date_earned
    FROM   per_assignments_f asg  /*per_all_assignments_f asg*/
           ,pay_assignment_actions paa
           ,pay_payroll_actions ppa
           ,hr_soft_coding_keyflex hscl
           ,per_periods_of_service pos
           ,per_people_f ppf   /*per_all_people_f ppf*/
    WHERE  asg.assignment_id = paa.assignment_id
    AND    asg.person_id = ppf.person_id
    AND    ppf.nationality = l_local_nationality
    AND    paa.payroll_action_id = ppa.payroll_action_id
    AND    pos.period_of_service_id = asg.period_of_service_id
    AND    ppa.action_type in ('R','Q')
    AND    ppa.action_status = 'C'
    AND    paa.action_status = 'C'
    AND    trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
    AND    trunc(pos.date_start, 'MM') <> trunc(l_effective_date, 'MM')
    AND    trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
    AND    trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') >= TRUNC(l_effective_date, 'MM')
    AND    trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
    AND    trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
    AND    hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
    AND    hscl.segment1 = to_char(p_employer_id);

Line: 1746

    SELECT /*full_name
           hr_person_name.get_person_name
           (p_person_id
           ,l_effective_date
           ,'DISPLAY_NAME'
           ,l_user_format)*/
            national_identifier
    FROM   per_people_f ppf  /*per_all_people_f ppf*/
    WHERE  person_id = p_person_id
    AND    l_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 1793

    INSERT INTO fnd_sessions (session_id, effective_date)
    VALUES (userenv('sessionid'), l_effective_date);

Line: 1799

      SELECT org_information1
      INTO l_local_nationality
      FROM hr_organization_information
      WHERE org_information_context = 'KW_BG_DETAILS'
      AND organization_id = p_business_group_id;

Line: 1810

    vXMLTable.DELETE;

Line: 2283

    select userenv('LANGUAGE') into g_nls_db_char from dual;

Line: 2319

      Select file_data
      Into p_pdf_blob
      From fnd_lobs
      Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_R166_ar_KW.pdf'
                       and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );

Line: 2326

      Select file_data
      Into p_pdf_blob
      From fnd_lobs
      Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAYR167_ar_KW.rtf'
                       and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );

Line: 2332

      Select file_data
      Into p_pdf_blob
      From fnd_lobs
      Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_167_06_ar_KW.rtf'
                       and effective_start_date between to_date('01-01-2006','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );

Line: 2340

      Select file_data
      Into p_pdf_blob
      From fnd_lobs
      Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_R168_ar_KW.pdf'
                       and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );

Line: 2374

                SELECT value
                INTO l_audit_log_dir
                FROM v$parameter
                WHERE LOWER(name) = 'utl_file_dir';