DBA Data[Home] [Help]

APPS.PAY_CA_EOY_ARCHIVE SQL Statements

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

Line: 141

                                          select clause as this cursor selects
                                          the data for a particular GRE.
   05-DEC-2003 SSattineni    115.51       The Negative Balance Exists flag
                                          was archiving incorrect for some
                                          employees, so initialised the
                                          flag value with 'N'.
   05-FEB-2004 SSattineni    115.52       Fixed the bug#3422384, added
                                          additional logic to archive the
                                          CPP/QPP Exempt flag and EI Exempt
                                          flag correctly for an employee.
   06-FEB-2004 mmukherj      115.53       Added cursor c_get_latest_actid to
                                          improve performance of getting latest
                                          assignment action id.
   19-FEB-2004 SSattineni    115.55       Modified c_get_date_of_birth cursor
                                          to address the terminated employees
                                          issue for Box 28 validation. Part of
                                          fix#3422384.
   02-JUL-2004 mmukherj      115.56       Modified c_eoy_gre further to make
                                          it more performant.
   09-AUG-2004 SSattineni    115.58       Modified eoy_action_creation procedure
                                          to check 'T4 Non Taxable Earnings',
                                          'Gross Earnings' and 'T4 No Gross
                                          Earnings' balance values before
                                          creating the assignment action for T4.
                                          Fix for bug#3267520.
   20-AUG-2004 rigarg        115.59       Fix for bug#3564076
                                          Added archiver for DBI's for Technical
                                          Contact Extension and EMail.
   24-AUG-2004 ssmukher      115.60       Fix for bug# 3447439.Modified the
                                          cursor c_get_latest_asg to fetch the
                                          earn date and assignment action id.
                                          This earn date will be used to fetch
                                          the CPP/QPP and EE exempt flag for
                                          an employee in a particular province.
   02-NOV-2004 rigarg        115.61       Fix for bug# 3973040. Removed
                                          Transmitter Code 904 check.
   10-NOV-2004  ssouresr     115.63       Modified to use tables instead of
                                          views to remove problems with
                                          security groups
   12-NOV-2004  ssouresr     115.64       Added a date range to the cursor
                                          c_get_latest_asg to make sure records
                                          are only picked up in the year
   22-NOV-2004  mmukherj     115.65       bugfix #4025926
   01-DEC-2004  mmukherj     115.66       Archiving QPP Reduced Subject. Because
                                          this amunt has to be printed in BOX26
                                          for QC employee. Bugfix 4031227.
   02-DEC-2004  ssouresr     115.67       Added error message for security group
   07-DEC-2004  ssouresr     115.68       Removed the changes made for 3447439
                                          in 115.60
                                          as this was impacting performance
   08-JUN-2005  ssouresr     115.69       Removed error message for security
                                          group
   13-JUN-2005  mmukherj     115.70       Bug fix #4026689. Added call to
                                          eoy_archive_gre_data in
                                          eoy_archive_data.  So that when the
                                          Retry process calls eoy_archive_data,
                                          it re-archives the employer and
                                          transmitter data.
   29-JUL-2005  ssmukher     115.71       Bug Fix #4034155 Added code to remove
                                          the other information amounts from
                                          the Box 14
   03-AUG-2005  ssmukher     115.72       Bug Fix #4034155 Added code for
                                          checking the other information
                                          amt total not to exceed the gross
                                          earnings total displayed in Box 14.
                                          Also modified the check condition for
                                          flag l_negative_balance_exists
                                          in eoy_archive_data procedure.
   05-AUG-2005  saurgupt     115.73       Bug 4517693: Added Address_line3 for
                                          T4 archiver.
   11-Aug-2005  ssmukher     115.74       Bug 4547415  Substracted the amount
                                          associated with code 31,53 and 78
                                          from the Grosss Earnings(box 14)
   26-AUG-2005 mmukherj     115.75        Commented out the use of two cursors
                                          c_eoy_all and eoy_all_range. Since
                                          GRE is a mandatory parameter for
                                          Federal Yearend Archiver Process
                                          these two cursors will never be used.
   14-Sep-2005  ssmukher     115.76       Bug Fix 4028693 .Archive 0 value for
                                          'Gross Earnings' when the Employment
                                          code is either 11,12,13 and 17
   26-OCT-2005  ssouresr     115.77       range_cursor has been modified to
                                          avoid using hr_soft_coding_keyflex
   04-NOV-2005  ssouresr     115.78       Removed archiving of the Federal Youth
                                          Hire indicator flag
   4-NOV-2005   pganguly     115.79       Fixed bug# 4033041. Commented out
                                          archiver code for T4_BOX50.
   3-MAR-2006   ssmukher     115.80       Fixed Bug #5041252 .Removed the
                                          per_all_assignments_f table check
                                          from the select statement in the
                                          procedure eoy_archive_data to fetch
                                          the CPP/QPP exempt flag
                                          from pay_ca_emp_prov_tax_info_f.
   25-Jul-2006  ssmukher     115.81       Made modification in the
                                          eoy_archive_data procedure to
                                          incorporate the PPIP tax.
   28-AUG-2006  pganguly     115.82       Fixed bug# 4025900. Changed the code
                                          for Box 14 so that it subtracts OTHER
                                          _INFORMATION71 before archiving.
   30-Aug-2007  ssmukher     115.83       Bug 5706114 fix.T4 Box44 and T4 Box20
                                          should not be reported for Status
                                          indian employee.Modified the proc
                                          eoy_archive_data.
   4-SEPT-2007 ssmukher      115.84       Fix for bug# 3447439.Modified the
                                          cursor c_get_latest_asg in
                                          eoy_archive_data to fetch the
                                          earn date and assignment action id.
                                          This earn date will be used to fetch
                                          the CPP/QPP and EI exempt flag for
                                          an employee in a particular province.
   6-SEPT-2007 amigarg       115.85       Fix for bug# 5698016.Added the
                                          T4_other_info_amount for code 81-85.
   19-SEP-2007 amigarg       115.87       Fix for bug# 6399498.archived the
                                          registration number for status_indian
   11-DEC-2007 tclewis       115.88       In the package eoy_action_creation modified
                                          The cursor c_eoy_gre  removed the subquery
                                          Modified the cursor c_get_latest_asg added
                                          Hints.

   19-SEP-2008 sneelapa      115.89       Fix for bug# 6399498.
                                          During QA testing bug 6399498 was reopened.

                                          Modified CURSOR LOOP of c_balance_feed_info
                                          and IF condition before CURSOR LOOP
                                          so that c_balance_feed_info CURSOR will
                                          get the "registration number" value
                                          of T4_BOX52 Element incase of Status Indian Employee
                                          and for non status indian employee get
                                          the reg number of T4_BOX52 if value
                                          for T4_BOX52 exists else get reg number
                                          of T4_BOX20.

   23-SEP-2008 sneelapa      115.91       Fix for bug# 6399498.

   25-SEP-2008 sneelapa      115.93       Fix for bug# 6399498.
                                          Modified CURSOR Query of c_balance_feed_info
                                          previous version of package date was hardcoded
                                          as '31-DEC-4712', which is against coding standards.

   26-SEP-2008 sneelapa      115.95       Fix for bug# 6399498.
                                          Modified CURSOR Query of c_balance_feed_info
                                          WHERE Condition pee.effective_start_date >= l_year_start
                                          is modified as
                                          pee.effective_start_date <= l_year_end
                                          IF an Employee is having two PA elements
                                          One attached in 2006 and second one in 2008
                                          and Archiver is run for 2008, 2006 Element was
                                          not picked up.

*/


   sqwl_range varchar2(4000);
Line: 345

     select to_number(UE.creator_id)
     from  ff_user_entities  UE,
           ff_database_items DI
     where  DI.user_name            = p_db_item_name
       and  UE.user_entity_id       = DI.user_entity_id
       and  Ue.creator_type         = 'B'
       and  UE.legislation_code     = 'CA';
Line: 403

  /* Name    : get_selection_information
  Purpose    : Returns information used in the selection of people to be reported on.
  Arguments  :

  The following values are returned :-

    p_period_start         - The start of the period over which to select
                             the people.
    p_period_end           - The end of the period over which to select
                             the people.
    p_defined_balance_id   - The balance which must be non zero for each
                             person to be included in the report.
    p_group_by_gre         - should the people be grouped by GRE.
    p_tax_unit_context     - Should the TAX_UNIT_ID context be set up for
                             the testing of the balance.
    p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
                             for the testing of the balance.

  Notes      : This routine provides a way of coding explicit rules for
               individual reports where they are different from the
               standard selection criteria for the report type ie. in
               NY state the selection of people in the 4th quarter is
               different from the first 3.
  */

 procedure get_selection_information
 (

  /* Identifies the type of report, the authority for which it is being run,
     and the period being reported. */
  p_report_type          varchar2,
  p_quarter_start        date,
  p_quarter_end          date,
  p_year_start           date,
  p_year_end             date,
  /* Information returned is used to control the selection of people to
     report on. */
  p_period_start         in out nocopy date,
  p_period_end           in out nocopy date,
  p_defined_balance_id   in out nocopy number,
  p_group_by_gre         in out nocopy boolean,
  p_tax_unit_context     in out nocopy boolean,
  p_jurisdiction_context in out nocopy boolean
 ) is

 begin

   /* Depending on the report being processed, derive all the information
      required to be able to select the people to report on. */

   if    p_report_type = 'T4'  then

     /* Default settings for Year End Pre-process. */

     p_period_start         := p_year_start;
Line: 474

 end get_selection_information;
Line: 493

   /* Variables used to hold the select columns from the SQL statement.*/

   l_person_id              number;
Line: 562

     SELECT ASG.person_id            person_id,
            ASG.assignment_id        assignment_id,
            ASG.effective_end_date   effective_end_date
     FROM
       per_all_assignments_f ASG
     WHERE
        ASG.business_group_id = l_bus_group_id AND
        asg.assignment_type = 'E' AND
        ASG.person_id between stperson and  endperson AND
        EXISTS
        (SELECT 1
         FROM pay_payroll_actions ppa,
              pay_assignment_actions paa
         WHERE
              ppa.business_group_id = l_bus_group_id AND
              ppa.payroll_action_id = paa.payroll_action_id AND
              ppa.action_type in ('R','Q','V','B','I') AND
              ppa.effective_date BETWEEN ASG.effective_start_date AND
                                         ASG.effective_end_date AND
              ppa.effective_date between l_period_start AND
                                l_period_end AND
              paa.assignment_id = ASG.assignment_id AND
              paa.tax_unit_id = l_eoy_tax_unit_id)
     ORDER  BY 1, 3 DESC, 2;
Line: 589

    SELECT  /*+ Ordered
                INDEX (asg PER_ASSIGNMENTS_F_N12)
                INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
                INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
            DISTINCT ASG.person_id   person_id
      FROM
            per_all_assignments_f      ASG,
            pay_assignment_actions paa,
            pay_payroll_actions ppa

     WHERE  ppa.effective_date between l_period_start
                               and l_period_end
     and  ppa.action_type in ('R','Q','V','B','I')
     and  ppa.action_status = 'C'
     and  ppa.business_group_id + 0 = l_bus_group_id
     and  ppa.payroll_action_id = paa.payroll_action_id
     and  paa.tax_unit_id = l_eoy_tax_unit_id
     and  paa.action_status = 'C'
     and  paa.assignment_id = ASG.assignment_id
     and  ppa.business_group_id = ASG.business_group_id +0
     and  ppa.effective_date between ASG.effective_start_date
                             and  ASG.effective_end_date
     AND  ASG.person_id between stperson and endperson
     AND  ASG.assignment_type  = 'E';
Line: 615

/*    SELECT  DISTINCT
            ASG.person_id               person_id
      FROM
            per_all_assignments_f      ASG,
            pay_all_payrolls_f         PPY
     WHERE  exists
           (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
                       INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
/*                   'x'
              from pay_payroll_actions ppa,
                   pay_assignment_actions paa
             where ppa.effective_date between l_period_start
                                          and l_period_end
               and  ppa.action_type in ('R','Q','V','B','I')
               and  ppa.action_status = 'C'
               and  ppa.business_group_id + 0 = l_bus_group_id
               and  ppa.payroll_action_id = paa.payroll_action_id
               and  paa.tax_unit_id = l_eoy_tax_unit_id
               and  paa.action_status = 'C'
               and  paa.assignment_id = ASG.assignment_id
               and  ppa.business_group_id = ASG.business_group_id +0
               and  ppa.effective_date between ASG.effective_start_date
                                           and  ASG.effective_end_date)
       AND  ASG.person_id between stperson and endperson
       AND  ASG.assignment_type  = 'E'
       AND  PPY.payroll_id       = ASG.payroll_id;
Line: 647

     SELECT ASG.person_id               person_id,
            ASG.assignment_id           assignment_id,
            to_number(SCL.segment1)     tax_unit_id,
            ASG.effective_end_date      effective_end_date
     FROM   per_all_assignments_f      ASG,
            hr_soft_coding_keyflex SCL,
            pay_all_payrolls_f         PPY
     WHERE  ASG.business_group_id + 0  = l_bus_group_id
       AND  ASG.person_id between stperson and endperson
       AND  ASG.assignment_type        = 'E'
       AND  ASG.effective_start_date  <= l_period_end
       AND  ASG.effective_end_date    >= l_period_start
       AND  SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
       AND  PPY.payroll_id             = ASG.payroll_id
     ORDER  BY 1, 3, 4 DESC, 2;
Line: 666

     SELECT assignment_id
     from per_all_assignments_f paf
     where person_id = p_person_id
     and   primary_flag = 'Y'
     and   paf.effective_start_date  <= l_period_end
     and   paf.effective_end_date    >= l_period_start
     and   paf.assignment_type = 'E'
     ORDER BY assignment_id desc;
Line: 678

            select /*+ Ordered
                       INDEX (asg PER_ASSIGNMENTS_F_N12)
                       INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
                       INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
                paa.assignment_action_id
              from  per_all_assignments_f      paf,
                    pay_assignment_actions     paa,
                    pay_payroll_actions        ppa,
                    pay_action_classifications pac
              where paf.person_id     = p_person_id
               and paa.assignment_id = paf.assignment_id
               and paa.tax_unit_id   = l_tax_unit_id
               and paa.payroll_action_id = ppa.payroll_action_id
               and ppa.action_type = pac.action_type
               and pac.classification_name = 'SEQUENCED'
               and ppa.effective_date +0 between paf.effective_start_date
                                           and paf.effective_end_date
               and ppa.effective_date +0 between l_year_start and
                                               l_year_end
               and ((nvl(paa.run_type_id, ppa.run_type_id) is null
               and  paa.source_action_id is null)
                or (nvl(paa.run_type_id, ppa.run_type_id) is not null
               and paa.source_action_id is not null )
               or (ppa.action_type = 'V' and ppa.run_type_id is null
                    and paa.run_type_id is not null
                    and paa.source_action_id is null))
               order by paa.action_sequence desc;
Line: 708

            select paa.assignment_action_id
              from pay_assignment_actions     paa,
                   per_all_assignments_f      paf,
                   pay_payroll_actions        ppa,
                   pay_action_classifications pac
              where paf.person_id     = p_person_id
               and paa.assignment_id = paf.assignment_id
               and paa.tax_unit_id   = l_tax_unit_id
               and paa.payroll_action_id = ppa.payroll_action_id
               and ppa.action_type = pac.action_type
               and pac.classification_name = 'SEQUENCED'
               and ppa.effective_date +0 between paf.effective_start_date
                                           and paf.effective_end_date
               and ppa.effective_date +0 between l_year_start and
                                               l_year_end
               and ((nvl(paa.run_type_id, ppa.run_type_id) is null
               and  paa.source_action_id is null)
                or (nvl(paa.run_type_id, ppa.run_type_id) is not null
               and paa.source_action_id is not null )
               or (ppa.action_type = 'V' and ppa.run_type_id is null
                    and paa.run_type_id is not null
                    and paa.source_action_id is null))
               order by paa.action_sequence desc;
Line: 746

     select effective_date,
            report_type,
            business_group_id,
            legislative_parameters
     into   l_effective_date,
            l_report_type,
            l_bus_group_id,
            l_legislative_parameters
     from pay_payroll_actions
     where payroll_action_id = pactid;
Line: 769

     hr_utility.trace('getting selection information');
Line: 776

     get_selection_information
         (l_report_type,
          l_quarter_start,
          l_quarter_end,
          l_year_start,
          l_year_end,
          l_period_start,
          l_period_end,
          l_defined_balance_id,
          l_group_by_gre,
          l_tax_unit_context,
          l_jurisdiction_context);
Line: 924

             select pay_assignment_actions_s.nextval
             into   lockingactid
             from   dual;
Line: 928

             /* Insert into pay_assignment_actions. */

              hr_utility.trace('creating assignment action');
Line: 935

             /* Update the serial number column with the person id
                so that we can use in the Magnetic Media process
                to do an additional check against the assignment table */

              hr_utility.trace('updating assignment action');
Line: 941

              update pay_assignment_actions aa
              set    aa.serial_number = to_char(l_person_id)
              where  aa.assignment_action_id = lockingactid;
Line: 985

    select fdi.user_entity_id
    into l_user_entity_id
    from ff_database_items fdi,
         ff_user_entities  fui
    where user_name = p_dbi_name
    and   fdi.user_entity_id = fui.user_entity_id
    and   fui.legislation_code = 'CA';
Line: 1040

SELECT fai1.context1,fdi1.user_entity_id,fai1.value
FROM FF_ARCHIVE_ITEMS FAI1,
     ff_database_items fdi1,
     ff_archive_item_contexts faic,
     ff_contexts fc
WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
and fai1.archive_item_id = faic.archive_item_id
and fc.context_id = faic.context_id
and fc.context_name = 'JURISDICTION_CODE'
and faic.context = cp_jurisdiction
AND FAI1.CONTEXT1 = cp_asg_act_id
AND fdi1.user_name <> 'CAEOY_T4_AMENDMENT_FLAG'
order by fdi1.user_name;
Line: 1057

select fai.context1,fai.user_entity_id,fai.value
from   ff_archive_items   fai
where  fai.user_entity_id = cp_dbi_ue_id
and    fai.context1  =    cp_asg_act_id;
Line: 1074

       ltr_amend_arch_data.delete;
Line: 1078

       ltr_yepp_arch_data.delete;
Line: 1082

       ltr_amend_emp_data.delete;
Line: 1086

       ltr_yepp_emp_data.delete;
Line: 1090

       ltr_emp_ue_id.delete;
Line: 1350

  select
    business_group_id
  from
    hr_all_organization_units
  where
    organization_id = p_tax_unit_id1;
Line: 1358

  select
    nvl(hoi6.ORG_INFORMATION9,hou.name) GRE_stat_report_name,
    hoi6.ORG_INFORMATION1 Employer_identification_number,
    hl.ADDRESS_LINE_1 GRE_addrline1,
    hl.ADDRESS_LINE_2 GRE_addrline2,
    hl.ADDRESS_LINE_3 GRE_addrline3,
    hl.TOWN_OR_CITY   GRE_town_or_city,
    DECODE(hl.STYLE , 'US' , hl.REGION_2 ,
                       'CA' , hl.REGION_1 ,
                       'CA_GLB',hl.region_1, ' ')  GRE_province,
    hl.POSTAL_CODE GRE_postal_code,
    hl.COUNTRY     GRE_country,
    hoi6.org_information3 ei_rate
  from
    hr_all_organization_units hou,
    hr_organization_information hoi6,
    hr_locations_all hl
  where
    hou.organization_id = p_tax_unit_id
    and hou.organization_id = hoi6.organization_id
    and hoi6.org_information_context = 'Canada Employer Identification'
    and hoi6.org_information5 in ('T4/RL1','T4/RL2')
    and hou.location_id = hl.location_id;
Line: 1383

  select
    hoi5.ORG_INFORMATION10 GRE_acct_contact_name,
    hoi5.ORG_INFORMATION12 GRE_acct_contact_phone,
    hoi5.ORG_INFORMATION11 GRE_acct_contact_area_code,
    hoi5.ORG_INFORMATION13 GRE_acct_contact_extn,
    hoi5.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
    hoi5.ORG_INFORMATION15 GRE_Proprietor_SIN#2/*,
    hoi5.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind*/
  from
    hr_organization_information hoi5
  where
    hoi5.organization_id = p_tax_unit_id
    and hoi5.org_information_context = 'Fed Magnetic Reporting';
Line: 1398

  select
    nvl(hoi3.ORG_INFORMATION9,hou.name) trans_stat_report_name,
    hl.ADDRESS_LINE_1 trans_addrline1,
    hl.ADDRESS_LINE_2 trans_addrline2,
    hl.ADDRESS_LINE_3 trans_addrline3,
    hl.TOWN_OR_CITY   trans_town_or_city,
    DECODE(hl.STYLE , 'US', hl.REGION_2,
                      'CA', hl.REGION_1,
                      'CA_GLB',hl.region_1, ' ')  trans_province,
    hl.POSTAL_CODE trans_postal_code,
    hl.COUNTRY     trans_country,
    hoi2.org_information5 trans_type_indicator,
    hoi2.ORG_INFORMATION4 trans_number,
    hoi2.ORG_INFORMATION2 trans_type_code,
    hoi2.ORG_INFORMATION3 trans_datatype_code,
    hoi2.ORG_INFORMATION6 trans_tech_contact_name,
    hoi2.ORG_INFORMATION8 trans_tech_contact_phone,
    hoi2.ORG_INFORMATION7 trans_tech_contact_areacode,
    hoi2.ORG_INFORMATION9 trans_tech_contact_lang,
    hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
    hoi2.ORG_INFORMATION18 trans_tech_contact_email
  from
    hr_all_organization_units hou,
    hr_organization_information hoi2,
    hr_organization_information hoi3,
    hr_locations_all hl
  where
    hou.organization_id = p_transmitter_gre_id
    and hou.organization_id = hoi2.organization_id
    and hoi2.org_information_context = 'Fed Magnetic Reporting'
    and hoi2.org_information1 = 'Y'
--    and hoi2.org_information2 = '904'  --comented for bug 3973040
    and hou.organization_id = hoi3.organization_id
    and hoi3.org_information_context = 'Canada Employer Identification'
    and hou.location_id = hl.location_id;
Line: 1588

     select to_char(effective_date,'YYYY'),
     add_months(trunc(effective_date, 'Y'),12) - 1
     into   l_taxation_year,
            l_effective_date
     from pay_payroll_actions
     where payroll_action_id = p_payroll_action_id;
Line: 1601

 select context_id
 into l_taxunit_context_id
 from ff_contexts
 where context_name = 'TAX_UNIT_ID';
Line: 1807

     select 'Y'
     from dual
     where exists (select null
               from ff_archive_items fai
               where fai.context1 = p_payroll_action_id
               and archive_type = 'PA');
Line: 1861

 select min(paa.chunk_number)
 from pay_assignment_actions paa
 where paa.payroll_action_id = p_payroll_action_id;
Line: 2045

  SELECT
    PEOPLE.person_id,
    PEOPLE.first_name,
    PEOPLE.last_name,
    PEOPLE.employee_number,
    replace(PEOPLE.national_identifier,' '),
    PEOPLE.middle_names,
    ASSIGN.organization_id,
    ASSIGN.location_id
  FROM
    per_all_assignments_f  ASSIGN,
    per_all_people_f       PEOPLE
  WHERE   ASSIGN.assignment_id = p_asgid
  and     l_date_earned BETWEEN ASSIGN.effective_start_date
                                           AND ASSIGN.effective_end_date
    AND	PEOPLE.person_id     = ASSIGN.person_id
    AND PEOPLE.effective_end_date = (select max(effective_end_date) from
                                   per_all_people_f PEOPLE1
                                   where PEOPLE1.person_id = PEOPLE.person_id);
Line: 2070

     select distinct context_value
     from   pay_action_contexts pac
     where  pac.assignment_id = p_asgid;
Line: 2076

     select province_abbrev
     from   pay_ca_provinces_v pac;
Line: 2082

           select nvl(pet.element_information20,'NOT FOUND'),
                  pbt1.balance_name
           from pay_balance_feeds_f pbf,
                pay_balance_types pbt,
                pay_balance_types pbt1,
                pay_input_values_f piv,
                pay_element_types_f pet
           where pbt.balance_name = p_balance_name
           and   pbf.balance_type_id = pbt.balance_type_id
           and   pbf.input_value_id = piv.input_value_id
           and   piv.element_type_id = pet.element_type_id
           and   pbt1.balance_type_id = pet.element_information10
--           and   pet.element_information_category = 'CA_EARNINGS'
          and   pet.business_group_id = l_business_group_id
           and   pet.element_information20 is not null;
Line: 2102

        select nvl(pet.element_information20,'NOT FOUND'),
                  pbt1.balance_name,
                  pev.screen_entry_value
        from pay_balance_feeds_f pbf,
                pay_balance_types pbt,
                pay_balance_types pbt1,
                pay_input_values_f piv,
                pay_element_types_f pet,
                pay_element_entries_f pee,
                pay_element_entry_values_f pev
           where pbt.balance_name = p_balance_name
           and pee.assignment_id = l_asgid
           and   pbf.balance_type_id = pbt.balance_type_id
           and   pbf.input_value_id = piv.input_value_id
           and   piv.element_type_id = pet.element_type_id
           and   pbt1.balance_type_id = pet.element_information10
           and   pet.business_group_id = l_business_group_id
           and   pet.element_information20 is not null
           and   pet.element_type_id = pee.element_type_id
--           and   trunc(p_effective_date) between pee.effective_start_date and pee.effective_end_date
           and   ((pee.effective_start_date <= l_year_end
                    and pee.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
                    or
                    (pee.effective_end_date between l_year_start and l_year_end))
--           and   trunc(p_effective_date) between pev.effective_start_date and pev.effective_end_date
           and   ((pev.effective_start_date <= l_year_end
                    and pev.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
                    or
                    (pev.effective_end_date between l_year_start and l_year_end))
           and   pee.element_entry_id = pev.element_entry_id
--           and fnd_number.canonical_to_number(pev.screen_entry_value) >= 0
--           and   pet.element_information_category = 'CA_EARNINGS'
--           and   pev.input_value_id = piv.input_value_id
          ;
Line: 2139

  select business_group_id
  from hr_all_organization_units
  where organization_id = p_tax_unit_id1;
Line: 2145

  select fai.value
  from   ff_archive_items   fai,
         ff_database_items  fdi
  where  fdi.user_entity_id = fai.user_entity_id
  and    fai.context1  = cp_assignment_action_id
  and    fdi.user_name = 'CAEOY_PROVINCE_OF_EMPLOYMENT';
Line: 2153

  select ppa.report_type
  from pay_payroll_actions ppa,pay_assignment_actions paa
  where paa.assignment_action_id = cp_locked_actid
  and ppa.payroll_action_id = paa.payroll_action_id;
Line: 2159

  select locked_action_id
  from pay_action_interlocks
  where locking_action_id = cp_locking_act_id;
Line: 2167

   select pei_information2,
          pei_information3
   from per_people_extra_info
   where person_id = cp_person_id
     and pei_information_category = 'ADDITIONAL_T4_INFORMATION'
     and pei_information1 = cp_gre;
Line: 2177

  select /*+ Ordered */
         paa.assignment_action_id,
         ppa.date_earned
  from  per_all_assignments_f      paf,
        pay_assignment_actions     paa,
        pay_payroll_actions        ppa,
        pay_action_classifications pac,
        pay_action_contexts pac1,
        ff_contexts         fc
  where paf.person_id     = p_person_id
    and paa.assignment_id = paf.assignment_id
    and paa.tax_unit_id   = l_tax_unit_id
    and paa.payroll_action_id = ppa.payroll_action_id
    and ppa.action_type = pac.action_type
    and pac.classification_name = 'SEQUENCED'
    and ppa.effective_date +0 between paf.effective_start_date
                                  and paf.effective_end_date
    and ppa.effective_date +0 between l_year_start
                                  and l_year_end
    and ((nvl(paa.run_type_id, ppa.run_type_id) is null
        and  paa.source_action_id is null)
         or (nvl(paa.run_type_id, ppa.run_type_id) is not null
        and paa.source_action_id is not null )
         or (ppa.action_type = 'V' and ppa.run_type_id is null
        and paa.run_type_id is not null
        and paa.source_action_id is null))
    and pac1.assignment_action_id = paa.assignment_action_id
    and pac1.context_id     = fc.context_id
    and fc.context_name    = 'JURISDICTION_CODE'
    and pac1.context_value  =  p_jurisdiction
   order by paa.action_sequence desc;
Line: 2214

               select paa.assignment_action_id,
                      ppa.date_earned
	       from pay_assignment_actions     paa,
	            per_all_assignments_f      paf,
	            per_all_people_f ppf,
	            pay_payroll_actions        ppa,
	            pay_action_classifications pac,
	            pay_action_contexts pac1,
	            ff_contexts         fc
	       where ppf.person_id = cp_person_id
	       and paf.person_id = ppf.person_id
	       and paf.assignment_id = paa.assignment_id
	       and paa.tax_unit_id   = cp_tax_unit_id
	       and ppa.payroll_action_id = paa.payroll_action_id
               and ppa.effective_date+0 between l_year_start
                                            and l_year_end
	       and ppa.effective_date between ppf.effective_start_date
	                                  and ppf.effective_end_date
	       and ppa.effective_date between paf.effective_start_date
	                                  and paf.effective_end_date
	       and ppa.action_type = pac.action_type
	       and pac.classification_name = 'SEQUENCED'
	       and pac1.assignment_action_id = paa.assignment_action_id
	       and pac1.context_id     = fc.context_id
	       and fc.context_name     = 'JURISDICTION_CODE'
	       and pac1.context_value  =  cp_jurisdiction
   	       order by paa.action_sequence desc;
Line: 2246

   select ppf.date_of_birth
   from per_all_people_f ppf
   where ppf.person_id = ln_person_id
   and  ppf.effective_end_date  = (select max(ppf2.effective_end_date)
                                     from per_all_people_f ppf2
                                     where ppf2.person_id= ln_person_id
                                     and ppf2.effective_start_date
                                         <= ld_eff_date);
Line: 2258

   select ca_tax_information1
   from   pay_ca_emp_fed_tax_info_f pca
   where  pca.assignment_id = cp_assign
    and   cp_effec_date between pca.effective_start_date and
          pca.effective_end_date;
Line: 2284

  SELECT
    aa.assignment_id,
    pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
    aa.tax_unit_id,
    aa.chunk_number,
    aa.payroll_action_id,
    aa.serial_number
  INTO
    l_asgid,
    l_date_earned,
    l_tax_unit_id,
    l_chunk,
    l_payroll_action_id,
    lv_serial_number
  FROM
    pay_assignment_actions aa
  WHERE
    aa.assignment_action_id = p_assactid;
Line: 2324

     select org_information11
     into l_transmitter_gre_id
     from hr_organization_information
     where  organization_id = l_tax_unit_id
     and    org_information_context = 'Canada Employer Identification';
Line: 2385

  SELECT
    paa1.assignment_action_id
  INTO
    l_aaid
  FROM
    pay_assignment_actions paa1,
    per_all_assignments_f      paf2
  WHERE
    paa1.assignment_id = paf2.assignment_id
    and   paa1.tax_unit_id = l_tax_unit_id
    and (paa1.action_sequence , paf2.person_id) =
      (SELECT MAX(paa.action_sequence), paf.person_id
       FROM   pay_action_classifications pac,
              pay_payroll_actions ppa,
              pay_assignment_actions paa,
              per_all_assignments_f paf1,
              per_all_assignments_f paf
        WHERE paf.assignment_id = l_asgid
          AND paf1.person_id = paf.person_id
          AND paa.tax_unit_id = l_tax_unit_id
          AND paa.assignment_id = paf1.assignment_id
          AND paa.payroll_action_id = ppa.payroll_action_id
          AND ppa.action_type = pac.action_type
          AND pac.classification_name = 'SEQUENCED'
          AND ppa.effective_date <= p_effective_date
        group by paf.person_id)
    and rownum < 2;
Line: 3096

    /* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
     SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
       decode(target.EI_EXEMPT_FLAG,'Y','X',NULL)
     INTO   l_cpp_exempt_flag,
       l_ei_exempt_flag
     FROM   pay_ca_emp_fed_tax_info_f      target
     WHERE   target.assignment_id         = l_asgid
       and lv_actual_date/*p_effective_date*/ between target.effective_start_date
       and target.effective_end_date;
Line: 3139

        SELECT decode(target.QPP_EXEMPT_FLAG,'Y','X',NULL),
	       decode(target.PPIP_EXEMPT_FLAG,'Y','X',NULL)
        INTO lv_qpp_exempt_flag,
	     l_ppip_exempt_flag
        FROM pay_ca_emp_prov_tax_info_f      target
        WHERE target.assignment_id         = l_asgid
        and target.province_code         = 'QC'
        and lv_actual_date/*p_effective_date */ between target.effective_start_date
        and target.effective_end_date;
Line: 3448

  hr_utility.trace('selecting people');
Line: 3468

    select formula_id,
      effective_start_date
    into   l_formula_id,
      l_effective_start_date
    from   ff_formulas_f
    where  formula_name='NI_VALIDATION'
      and business_group_id is null
      and legislation_code='CA'
      and sysdate between effective_start_date and effective_end_date;
Line: 3510

       hr_utility.trace('selected people');
Line: 3596

 hr_utility.trace('selected address');
Line: 3665

     select effective_date,report_type
     into ld_fapp_effective_date,lv_fapp_report_type
     from pay_payroll_actions
     where payroll_action_id = l_payroll_action_id;
Line: 3741

     Purpose   : This returns the select statement that is used to created the
                 range rows for the Year End Pre-Process.
     Arguments :
     Notes     :
  */

  procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is

  l_legislative_parameters    varchar2(240);
Line: 3762

     select legislative_parameters,
            trunc(effective_date,'Y'),
            effective_date,
            business_group_id
     into   l_legislative_parameters,
            l_year_start,
            l_year_end,
            l_business_group
     from pay_payroll_actions
     where payroll_action_id = pactid;
Line: 3776

     select org_information11
     into l_transmitter_gre_id
     from hr_organization_information
     where  organization_id = l_eoy_tax_unit_id
     and    org_information_context = 'Canada Employer Identification';
Line: 3787

        sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
                                             PPA PAY_PAYROLL_ACTIONS_N51,
                                             PAA PAY_ASSIGNMENT_ACTIONS_N50,
                                             ASG PER_ASSIGNMENTS_F_PK,
                                             PPA1 PAY_PAYROLL_ACTIONS_PK)
                              USE_NL(PPY, PPA, PAA, ASG, PPA1) */
                         distinct asg.person_id
                   from pay_all_payrolls_f ppy,
                        pay_payroll_actions ppa,
                        pay_assignment_actions paa,
                        per_all_assignments_f asg,
                        pay_payroll_actions ppa1
                   where ppa1.payroll_action_id = :payroll_action_id
                   and   ppa.effective_date between
                               fnd_date.canonical_to_date('''||
                                             fnd_date.date_to_canonical(l_year_start)||''') and
                               fnd_date.canonical_to_date('''||
                                             fnd_date.date_to_canonical(l_year_end)||''')
                   and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
                   and ppa.action_status = ''C''
                   and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
                   and ppa.payroll_action_id = paa.payroll_action_id
                   and paa.tax_unit_id = '|| to_char(l_eoy_tax_unit_id)||'
                   and paa.action_status = ''C''
                   and paa.assignment_id = asg.assignment_id
                   and ppa.business_group_id = asg.business_group_id + 0
                   and ppa.effective_date between asg.effective_start_date
                                              and asg.effective_end_date
                   and asg.assignment_type = ''E''
                   and ppa.payroll_id = ppy.payroll_id
                   and ppy.business_group_id = '||to_char(l_business_group)||'
                   order by asg.person_id';