DBA Data[Home] [Help]

APPS.PAY_US_SQWL_GRE SQL Statements

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

Line: 97

SELECT  DISTINCT
        hoi.organization_id
FROM    hr_organization_information hoi
       ,hr_organization_units hou1
WHERE   hoi.org_information1 = p_state
AND     hou1.business_group_id = p_business_group_id
AND     hoi.organization_id = hou1.organization_id
AND     hoi.org_information_context = 'State Tax Rules'
AND     nvl (p_tax_unit_id
             ,hoi.organization_id) = hoi.organization_id
AND     nvl (hoi.org_information16
            ,'No') = 'No'
AND     nvl (hoi.org_information20
            ,'No') = 'No'
AND     (
                (
                        p_state IN ('CA','ME')
                )
        OR      NOT EXISTS
                        (
                        SELECT  'x'
                        FROM    hr_organization_information hoi2
                               ,hr_organization_units hou2
                        WHERE   hoi2.organization_id = hoi.organization_id
                        AND     hoi2.org_information_context = '1099R Magnetic Report Rules'
                        AND     hoi2.org_information2 IS NOT NULL
                        AND     hou2.business_group_id = p_business_group_id
                        AND     hoi2.organization_id = hou2.organization_id
                        )
        );
Line: 129

SELECT  flv.meaning
FROM    fnd_lookup_values flv
WHERE   flv.lookup_type = cp_state_abbrv||'_SQWL_MEDIA_TYPE'
AND     flv.lookup_code = cp_format
AND     flv.enabled_flag = 'Y'
AND     LANGUAGE='US';
Line: 138

/*SELECT distinct puar.tax_unit_id
          FROM   hr_organization_information HOI,
                 per_assignments_f           ASG,
                 pay_us_asg_reporting        puar,
                 pay_state_rules             SR
          WHERE  SR.state_code            =  'NY' --l_state
            AND  puar.jurisdiction_code like substr(SR.jurisdiction_code  ,1,2)||'%'
            AND  ASG.assignment_id           = puar.assignment_id
            AND  ASG.assignment_type         = 'E'
            AND  ASG.effective_start_date   <= to_date('31-MAR-2010') --l_effective_date
            AND  ASG.effective_end_date     >= to_date('01-JAN-2010')  --l_start_date
            AND  ASG.business_group_id + 0   = 0 --l_business_group_id
            AND  (('NY' IN ( 'CA','ME'))
                   OR (not exists (select 'x'
                            from hr_organization_information HOI2
                            where HOI2.organization_id = puar.tax_unit_id
	                    AND  HOI2.ORG_INFORMATION_CONTEXT = '1099R Magnetic Report Rules'
                            AND  HOI2.ORG_INFORMATION2 is not null)))
            AND  HOI.organization_id = puar.tax_unit_id
	    AND  HOI.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
	    AND  HOI.ORG_INFORMATION1 = 'NY' --l_state
	    AND  NVL(HOI.ORG_INFORMATION16,'No') = 'No'
	    AND  NVL(HOI.ORG_INFORMATION20,'No') = 'No'
            AND  ASG.payroll_id is not null; */
Line: 181

SELECT pay_payroll_actions_s.nextval
  INTO ln_locking_action_id
  FROM dual;
Line: 195

hr_utility.trace('inserting payroll action: '||ln_locking_action_id);
Line: 196

INSERT
INTO    pay_payroll_actions
        (payroll_action_id
        ,action_type
        ,business_group_id
        ,consolidation_set_id
        ,payroll_id
        ,action_population_status
        ,action_status
        ,effective_date
        ,comments
        ,object_version_number
        ,pay_advice_message
        ,report_type
        ,report_qualifier
        ,request_id
        ,report_category
        ,start_date)
VALUES
        (ln_locking_action_id
        ,'X'
        ,p_business_group_id
        ,NULL
        ,NULL
        ,'C'
        ,'C'
        ,ld_quater_end_date
        ,NULL
        ,1
        ,NULL
        ,'SQWL_PARENT'
        ,p_state
        ,fnd_profile.value ('CONC_REQUEST_ID')
        ,p_format
        ,ld_quater_start_date);
Line: 237

SELECT state_code INTO lv_state_code
FROM pay_us_states
WHERE STATE_ABBREV =  p_state;
Line: 245

SELECT count(1) into l_previous_sqwl_run_check from pay_payroll_actions
WHERE report_type = 'SQWL'
AND report_qualifier = p_state
AND business_group_id = p_business_group_id
AND effective_date = fnd_date.canonical_to_date (p_effective_date)
AND pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) =  c_get_all_gres_rec.organization_id;
Line: 254

 select count(paa.assignment_action_id)
        into l_chk_gre_assign_actions
        from  pay_assignment_actions paa,
              pay_payroll_actions pact,
              pay_payrolls_f ppf,
              pay_us_emp_fed_tax_rules_f peft
        where pact.effective_date
        between ld_Quater_start_date
            and ld_Quater_end_date
            and pact.payroll_action_id=paa.payroll_action_id
            and pact.action_type in ('R', 'Q', 'I','B')
			and paa.action_status = 'C'
			and pact.action_status = 'C'
            and paa.tax_unit_id = c_get_all_gres_rec.organization_id
            and ppf.payroll_id = pact.payroll_id
            and ppf.business_group_id =p_business_group_id
	    and  peft.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
	    and pact.EFFECTIVE_DATE between
	    peft.EFFECTIVE_START_DATE and peft.EFFECTIVE_END_DATE
	    and peft.SUI_STATE_CODE = lv_state_code;
Line: 279

   SELECT pay_us_reporting_utils_pkg.get_file_name(c_get_all_gres_rec.organization_id,
                                       'SQWL',
                                       p_state,
                                       to_char(ld_Quater_end_date,'YYYY/MM/DD') ||' 00:00:00',
                                       substr(lv_format_meaning,1,1) )
   INTO l_file_name
   FROM dual;
Line: 346

   SELECT pay_us_reporting_utils_pkg.get_file_name( p_business_group_id,
                                          'SQWL',
                                          p_state,
                                          to_char(ld_Quater_end_date,'YYYY/MM/DD') ||' 00:00:00',
                                          substr(lv_format_meaning,1,1) )
   INTO l_file_name
   FROM dual;
Line: 412

SELECT  pact.payroll_action_id
FROM    pay_payroll_actions pact
WHERE   action_type = 'X'
AND     report_type in ('SQWL', 'SMWL')    /* Modified for Bug 14456648 */
AND     pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
                                           ,pact.legislative_parameters) = p_payroll_action_id;
Line: 420

SELECT  report_type
FROM    pay_payroll_actions
WHERE   payroll_action_id = p_payroll_action_id;
Line: 425

SELECT  count (payroll_action_id)
FROM    pay_payroll_actions p1
WHERE   pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
                                           ,p1.legislative_parameters) IN
        (
        SELECT  pay_us_payroll_utils.get_parameter ('TRANSFER_PARENT_PAYROLL_ACTION'
                                                   ,legislative_parameters)
        FROM    pay_payroll_actions
        WHERE   payroll_action_id = cp_payroll_action_id
        );
Line: 448

  SELECT report_type INTO l_report_type
	FROM pay_payroll_actions
	WHERE payroll_action_id = p_payroll_action_id;
Line: 513

       SELECT pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',legislative_parameters)
       INTO l_parent_payroll_action_id from pay_payroll_actions
       WHERE payroll_action_id = p_payroll_action_id;
Line: 526

         SELECT count(payroll_action_id) into l_child_count
    	   FROM pay_payroll_actions p1
    	   WHERE pay_us_payroll_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',p1.legislative_parameters)
               = l_parent_payroll_action_id;
Line: 639

SELECT  DISTINCT
        hoi.organization_id
FROM    hr_organization_information hoi
       ,hr_organization_units hou1
WHERE   hoi.org_information1 = p_state
AND     hou1.business_group_id = p_business_group_id
AND     hoi.organization_id = hou1.organization_id
AND     hoi.org_information_context = 'State Tax Rules'
AND     nvl (p_tax_unit_id
             ,hoi.organization_id) = hoi.organization_id
AND     nvl (hoi.org_information16
            ,'No') = 'No'
AND     nvl (hoi.org_information20
            ,'No') = 'No'
AND     (
                (
                        p_state IN ('CA','ME')
                )
        OR      NOT EXISTS
                        (
                        SELECT  'x'
                        FROM    hr_organization_information hoi2
                               ,hr_organization_units hou2
                        WHERE   hoi2.organization_id = hoi.organization_id
                        AND     hoi2.org_information_context = '1099R Magnetic Report Rules'
                        AND     hoi2.org_information2 IS NOT NULL
                        AND     hou2.business_group_id = p_business_group_id
                        AND     hoi2.organization_id = hou2.organization_id
                        )
        );
Line: 671

SELECT  flv.meaning
FROM    fnd_lookup_values flv
WHERE   flv.lookup_type = cp_state_abbrv||'_SMWL_MEDIA_TYPE'
AND     flv.lookup_code = cp_format
AND     flv.enabled_flag = 'Y'
AND     LANGUAGE='US';
Line: 681

/*SELECT distinct puar.tax_unit_id
          FROM   hr_organization_information HOI,
                 per_assignments_f           ASG,
                 pay_us_asg_reporting        puar,
                 pay_state_rules             SR
          WHERE  SR.state_code            =  'NY' --l_state
            AND  puar.jurisdiction_code like substr(SR.jurisdiction_code  ,1,2)||'%'
            AND  ASG.assignment_id           = puar.assignment_id
            AND  ASG.assignment_type         = 'E'
            AND  ASG.effective_start_date   <= to_date('31-MAR-2010') --l_effective_date
            AND  ASG.effective_end_date     >= to_date('01-JAN-2010')  --l_start_date
            AND  ASG.business_group_id + 0   = 0 --l_business_group_id
            AND  (('NY' IN ( 'CA','ME'))
                   OR (not exists (select 'x'
                            from hr_organization_information HOI2
                            where HOI2.organization_id = puar.tax_unit_id
	                    AND  HOI2.ORG_INFORMATION_CONTEXT = '1099R Magnetic Report Rules'
                            AND  HOI2.ORG_INFORMATION2 is not null)))
            AND  HOI.organization_id = puar.tax_unit_id
	    AND  HOI.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
	    AND  HOI.ORG_INFORMATION1 = 'NY' --l_state
	    AND  NVL(HOI.ORG_INFORMATION16,'No') = 'No'
	    AND  NVL(HOI.ORG_INFORMATION20,'No') = 'No'
            AND  ASG.payroll_id is not null; */
Line: 724

SELECT pay_payroll_actions_s.nextval
  INTO ln_locking_action_id
  FROM dual;
Line: 738

hr_utility.trace('inserting payroll action: '||ln_locking_action_id);
Line: 739

INSERT
INTO    pay_payroll_actions
        (payroll_action_id
        ,action_type
        ,business_group_id
        ,consolidation_set_id
        ,payroll_id
        ,action_population_status
        ,action_status
        ,effective_date
        ,comments
        ,object_version_number
        ,pay_advice_message
        ,report_type
        ,report_qualifier
        ,request_id
        ,report_category
        ,start_date)
VALUES
        (ln_locking_action_id
        ,'X'
        ,p_business_group_id
        ,NULL
        ,NULL
        ,'C'
        ,'C'
        ,ld_month_end_date
        ,NULL
        ,1
        ,NULL
        ,'SMWL_PARENT'
        ,p_state
        ,fnd_profile.value ('CONC_REQUEST_ID')
        ,p_format
        ,ld_month_start_date);
Line: 780

SELECT state_code INTO lv_state_code
FROM pay_us_states
WHERE STATE_ABBREV =  p_state;
Line: 788

SELECT count(1) into l_previous_smwl_run_check from pay_payroll_actions
WHERE report_type = 'SMWL'
AND report_qualifier = p_state
AND business_group_id = p_business_group_id
AND effective_date = fnd_date.canonical_to_date (p_effective_date)
AND pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) =  c_get_all_gres_rec.organization_id;
Line: 797

 select count(paa.assignment_action_id)
        into l_chk_gre_assign_actions
        from  pay_assignment_actions paa,
              pay_payroll_actions pact,
              pay_payrolls_f ppf,
              pay_us_emp_fed_tax_rules_f peft
        where pact.effective_date
        between ld_Month_start_date
            and ld_Month_end_date
            and pact.payroll_action_id=paa.payroll_action_id
            and pact.action_type in ('R', 'Q', 'I','B')
			and paa.action_status = 'C'
			and pact.action_status = 'C'
            and paa.tax_unit_id = c_get_all_gres_rec.organization_id
            and ppf.payroll_id = pact.payroll_id
            and ppf.business_group_id =p_business_group_id
	    and  peft.ASSIGNMENT_ID = paa.ASSIGNMENT_ID
	    and pact.EFFECTIVE_DATE between
	    peft.EFFECTIVE_START_DATE and peft.EFFECTIVE_END_DATE
	    and peft.SUI_STATE_CODE = lv_state_code;
Line: 822

   SELECT pay_us_reporting_utils_pkg.get_file_name(c_get_all_gres_rec.organization_id,
                                       'SQWL',
                                       p_state,
                                       to_char(ld_Month_end_date,'YYYY/MM/DD') ||' 00:00:00',
                                       substr(lv_format_meaning,1,1) )
   INTO l_file_name
   FROM dual;
Line: 889

   SELECT pay_us_reporting_utils_pkg.get_file_name( p_business_group_id,
                                          'SQWL',
                                          p_state,
                                          to_char(ld_Month_end_date,'YYYY/MM/DD') ||' 00:00:00',
                                          substr(lv_format_meaning,1,1) )
   INTO l_file_name
   FROM dual;