DBA Data[Home] [Help]

APPS.PAY_US_W2_INFO_PKG SQL Statements

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

Line: 382

          select puw.assignment_action_id control_number,
                 nvl(ssn,'Applied For') SSN,
  		         first_name||
                 decode(middle_name,null,' ',
                        ' '||substr(middle_name,1,1)||' ') ||
                 pre_name_adjunt emp_name,
                 last_name ,                                      -- Bug 4523389
		 hr_us_w2_rep.get_per_item(p_asg_action_id,
                                          'A_PER_SUFFIX') emp_suffix,
                /* Bug  5575567
                  decode(pa.address_line1,null,null,pa.address_line1 ||'\r')||
                 decode(pa.address_line2,null,null,pa.address_line2||'\r') ||
                 decode(pa.address_line3,null,null,pa.address_line3||'\r') ||
                 decode(pa.town_or_city,null,null,pa.town_or_city ||' ')||
                 decode(pa.region_2,null,null,pa.region_2||' ')|| pa.postal_code                     employee_address,
               */
                 decode(W2_WAGES_TIPS_COMPENSATION,0,'',W2_WAGES_TIPS_COMPENSATION) wages_tips_compensation,
                 decode(W2_FED_IT_WITHHELD,0,'',W2_FED_IT_WITHHELD) fit_withheld,
                 decode(W2_SOCIAL_SECURITY_WAGES,0,'',W2_SOCIAL_SECURITY_WAGES) ss_wages,
                 decode(W2_SST_WITHHELD,0,'',W2_SST_WITHHELD) ss_withheld,
                 decode(W2_MED_WAGES_TIPS,0,'',W2_MED_WAGES_TIPS) med_wages,
                 decode(W2_MED_TAX_WITHHELD,0,'',W2_MED_TAX_WITHHELD) med_withheld,
                 decode(W2_SOCIAL_SECURITY_TIPS,0,'',W2_SOCIAL_SECURITY_TIPS) ss_tips,
                 decode(W2_ALLOCATED_TIPS,0,'',W2_ALLOCATED_TIPS) allocated_tips,
                 decode(W2_ADV_EIC_PAYMENT,0,'',W2_ADV_EIC_PAYMENT) eic_payment,
                 decode(W2_DEPENDENT_CARE_BEN,0,'',W2_DEPENDENT_CARE_BEN) dependent_care,
                 decode(W2_NONQUAL_PLANS,0,'',W2_NONQUAL_PLANS) non_qual_plan,
                 decode(W2_STATUTORY_EMPLOYEE,'X','Y',null,'N',' ','N')
                                                     stat_employee,
                 decode(W2_RETIREMENT_PLAN,'X','Y',null,'N',' ','N')
                                                     retirement_plan,
                 decode(W2_THIRD_PARTY_SICK_PAY,'X','Y',null,
                        'N',' ','N') sick_pay,
                 person_id , puw.assignment_id -- bug 5575567
          from pay_us_wages_w2_v puw
               --per_addresses pa
          where puw.assignment_action_id = p_asg_action_id
         /*  bug 5575567
           and pa.primary_flag = 'Y'
          and pa.person_id = puw.person_id */
          and puw.tax_unit_id = p_tax_unit_id
          and puw.year = p_year;
Line: 428

           select w2_balance_code,
                  w2_balance_code_value
           from   pay_us_code_w2_v
           where w2_balance_code_value > 0
           and assignment_action_id = p_asg_action_id
           order by w2_balance_code;
Line: 436

           SELECT substr(w2_other_meaning,1,10) w2_other_code,
                  w2_other_value
           from pay_us_other_w2_v
           where w2_other_value > 0
           and  assignment_action_id = p_asg_action_id;
Line: 443

           SELECT 'BOONOCC' w2_other_code,
                  w2_local_income_tax -
                  decode(sign(w2_local_wages - 16666), -1,
                         w2_local_wages * 0.0015, 25) w2_other_value
           from pay_us_locality_w2_v
           where state_abbrev = 'KY'
           and tax_type = 'COUNTY'
           and substr(jurisdiction,1,6) = '18-015'
           and assignment_action_id = p_asg_action_id
           and w2_local_income_tax > 0;
Line: 456

           SELECT 'BOONMH' w2_other_code,
                  decode(sign(w2_local_wages - 16666), -1,
                         w2_local_wages * 0.0015, 25) w2_other_value
           from pay_us_locality_w2_v
           where state_abbrev = 'KY'
           and tax_type = 'COUNTY'
           and substr(jurisdiction,1,6) = '18-015'
           and assignment_action_id = p_asg_action_id
           and w2_local_income_tax > 0;
Line: 467

           SELECT locality_name locality,
                  decode(w2_local_wages,0,'',w2_local_wages) locality_wages,
                  w2_local_income_tax locality_tax,
                  jurisdiction jurisdiction,
                  state_abbrev state_code,
                  tax_type
           FROM pay_us_locality_w2_v
           WHERE assignment_action_id = p_asg_action_id
          /*commented for 4102684
           and W2_LOCAL_INCOME_TAX > 0*/
--           and (jurisdiction <> '18-015-0000')
           order by state_code, tax_type;
Line: 481

           SELECT substr(state_abbrev,1,2) state_code,
                  substr(state_ein,1,20) state_ein,
                  decode(W2_STATE_WAGES,0,'',W2_STATE_WAGES) state_wages,
                  decode(W2_STATE_INCOME_TAX,0,'',W2_STATE_INCOME_TAX) state_tax
           FROM pay_us_state_w2_v  state
           WHERE assignment_action_id = p_asg_action_id
           and  ( (w2_state_wages > 0) or
	          (W2_STATE_INCOME_TAX > 0) )  /* 6809739  */
           order by state_code;
Line: 492

          select federal_ein federal_ein,
                 tax_unit_name employer_name,
                 substr(decode(put.address_line_1,null,null,put.address_line_1||pay_us_w2_info_pkg.EOL),1,45)||
                 substr(decode(put.address_line_2,null,null,put.address_line_2||pay_us_w2_info_pkg.EOL),1,45)||
                 substr(decode(put.address_line_3,null,null,put.address_line_3||pay_us_w2_info_pkg.EOL),1,45)||
                 decode(put.town_or_city,null,null,put.town_or_city||' ')||
                 decode(state,null,null,state||' ')||put.postal_code
                                                 employer_address
          from pay_us_w2_tax_unit_v put
          where tax_unit_id = p_tax_unit_id
          and year = p_year;
Line: 507

          select federal_ein federal_ein,
                 tax_unit_name employer_name,
                 decode(put.address_line_1,null,null,substr(put.address_line_1,1,45)||pay_us_w2_info_pkg.EOL)||
                 decode(put.address_line_2,null,null,substr(put.address_line_2,1,45)||pay_us_w2_info_pkg.EOL)||
                 decode(put.address_line_3,null,null,substr(put.address_line_3,1,45)||pay_us_w2_info_pkg.EOL)||
                 decode(put.town_or_city,null,null,put.town_or_city||' ')||
                 decode(state,null,null,state||' ')||put.postal_code
                 employer_address
          from pay_us_w2_tax_unit_v put
          where tax_unit_id = p_tax_unit_id
          and year = p_year;
Line: 521

            select business_group_id
            from hr_all_organization_units /*hr_organization_units*/
            where organization_id = p_tax_unit_id;
Line: 526

            select payroll_action_id
            from pay_assignment_actions
            where assignment_action_id = p_asg_action_id;
Line: 728

             select l_first_name||decode(l_middle_name,null,' ',
                                     ' '||substr(l_middle_name,1,1)||' ') ||
                                     hr_us_w2_rep.get_per_item(p_asg_action_id,
                                     'A_PER_PREFIX' ) into l_w2_fields.emp_name  from dual;
Line: 930

                       select nvl(value,'N') into l_nr_flag
                       from  ff_database_items fdi,
                             ff_archive_items fai
                       where user_name = 'A_IN_NR_FLAG'
                       and fdi.user_entity_id = fai.user_entity_id
                       and fai.context1 = p_asg_action_id;
Line: 939

                          select nvl(value,'00-000-0000') into l_nr_jd
                          from ff_database_items fdi,
                               ff_archive_items fai
                          where fdi.user_name = 'A_IN_RES_JD'
                          and fdi.user_entity_id = fai.user_entity_id
                          and context1 = p_asg_action_id;
Line: 1555

      l_state_local_tab.delete;
Line: 1556

      l_state_tab.delete;
Line: 1557

      l_local_tab.delete;
Line: 1558

      l_box12_tab.delete;
Line: 1559

      l_box14_tab.delete;
Line: 1697

         SELECT paa1.assignment_action_id, -- archiver asg action
               paa.assignment_action_id,
               hr_us_w2_mt.get_parameter('GRE_ID',ppa.legislative_parameters),
               hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
               hr_us_w2_mt.get_parameter('p_instr_template',ppa.legislative_parameters),
               hr_us_w2_mt.get_parameter('print_instrunction',ppa.legislative_parameters)
         FROM pay_assignment_actions paa,
              pay_payroll_actions ppa,
              pay_assignment_actions paa1,
              pay_payroll_actions ppa1
         where ppa.payroll_action_id = paa.payroll_action_id
         and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
         and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
         and paa.serial_number = paa1.assignment_action_id
         and paa1.payroll_action_id = ppa1.payroll_action_id
         and ppa1.report_type = 'YREND'
         and ppa1.action_type = 'X'
         and ppa1.action_status = 'C'
         and ppa1.effective_date = ppa.effective_date;
Line: 1722

         l_state_local_tab.delete;
Line: 1723

         l_state_tab.delete;
Line: 1724

         l_local_tab.delete;
Line: 1725

         l_box12_tab.delete;
Line: 1726

         l_box14_tab.delete;
Line: 1932

       select translate(ltrim(value),',',' ')
        into TEMP_UTL
        from v$parameter
       where name = 'utl_file_dir';
Line: 1938

        select substrb(TEMP_UTL, 1, instr(TEMP_UTL,' ') - 1)
          into g_temp_dir
          from dual ;