APPS.PAY_GB_EPS_13 SQL Statements

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

Line: 83

PROCEDURE insert_eps_record
        (
          p_effective_date            IN DATE,
          p_emp_paye_reference        IN VARCHAR2,
          p_account_office_number     IN VARCHAR2,
          p_business_group_id         IN NUMBER,
	        p_employer_name           IN VARCHAR2,
	        p_econ_number             IN VARCHAR2,
	        p_related_tax_year        IN VARCHAR2,
			p_final_submission_ceased  IN VARCHAR2,
			p_final_submission_year    IN VARCHAR2,
	        p_smp_sap_spp_recovery     IN NUMBER,
	        p_ssp_recovery             IN NUMBER,
	        p_nic_recovery             IN NUMBER,
	        p_service_company           IN VARCHAR2,
	        p_smp_amt_recovered_calc      IN NUMBER,
	        p_sap_amt_recovered_calc       IN NUMBER,
	        p_ospp_amt_recovered_calc    IN NUMBER,
	        p_aspp_amt_recovered_calc   IN NUMBER,
	        p_ssp_amt_recovered_calc    IN NUMBER,
	        p_nic_comp_smp_calc         IN NUMBER,
	        p_nic_comp_sap_calc         IN NUMBER,
	        p_nic_comp_ospp_calc        IN NUMBER,
	        p_nic_comp_aspp_calc        IN NUMBER,
	        p_nic_comp_ssp_calc         IN NUMBER,
	        p_status                    IN VARCHAR2,
	        p_user_id                   IN NUMBER,
	        p_approver_id               IN NUMBER,
          p_request_id                IN NUMBER,
          p_program_application_id    IN NUMBER,
          p_program_id                IN NUMBER,
          p_update_date               IN DATE
                                                                )
IS
l_proc    varchar2(72) := g_package||'insert_eps_record';

Line: 132

insert into PAY_GB_EPS_DETAILS
(                               EPS_RECORD_ID,
                                EMP_PAYE_REFERENCE,
                                ACCOUNT_OFFICE_NUMBER,
                                BUSINESS_GROUP_ID,
                                EMPLOYER_NAME,
                                ECON_NUMBER,
                                EFFECTIVE_DATE,
                                RELATED_TAX_YEAR,
								FINAL_SUBMISSION_CEASED,
								FINAL_SUBMISSION_YEAR,
                                SMP_SAP_SPP_RECOVERY,
                                SSP_RECOVERY,
                                NIC_RECOVERY,
                                SERVICE_COMPANY,
								SMP_AMT_RECOVERED,
                                SAP_AMT_RECOVERED,
                                OSPP_AMT_RECOVERED,
                                ASPP_AMT_RECOVERED,
                                SSP_AMT_RECOVERED,
                                NIC_COMP_SMP,
                                NIC_COMP_SAP,
                                NIC_COMP_OSPP,
                                NIC_COMP_ASPP,
                                NIC_COMP_SSP,
                                SMP_AMT_RECOVERED_CALC,
                                SAP_AMT_RECOVERED_CALC,
                                OSPP_AMT_RECOVERED_CALC,
                                ASPP_AMT_RECOVERED_CALC,
                                SSP_AMT_RECOVERED_CALC,
                                NIC_COMP_SMP_CALC,
                                NIC_COMP_SAP_CALC,
                                NIC_COMP_OSPP_CALC,
                                NIC_COMP_ASPP_CALC,
                                NIC_COMP_SSP_CALC,
                                CREATED_DATE,
                                LAST_UPDATED_DATE,
                                STATUS,
                                USER_ID,
                                APPROVER_ID,
                                OBJECT_VERSION_NUMBER,
                                REQUEST_ID,
                                PROGRAM_APPLICATION_ID,
                                PROGRAM_ID,
                                PROGRAM_UPDATE_DATE
)
VALUES
(
pay_gb_eps_details_s.nextval,
p_emp_paye_reference,
p_account_office_number,
p_business_group_id,
p_employer_name,
p_econ_number,
p_effective_date,
p_related_tax_year,
p_final_submission_ceased,
p_final_submission_year,
p_smp_sap_spp_recovery,
p_ssp_recovery,
p_nic_recovery,
p_service_company,
NVL(p_smp_amt_recovered_calc,0),
NVL(p_sap_amt_recovered_calc,0),
NVL(p_ospp_amt_recovered_calc,0),
NVL(p_aspp_amt_recovered_calc,0),
NVL(p_ssp_amt_recovered_calc,0),
NVL(p_nic_comp_smp_calc,0),
NVL(p_nic_comp_sap_calc,0),
NVL(p_nic_comp_ospp_calc,0),
NVL(p_nic_comp_aspp_calc,0),
NVL(p_nic_comp_ssp_calc,0),
p_smp_amt_recovered_calc,
p_sap_amt_recovered_calc,
p_ospp_amt_recovered_calc,
p_aspp_amt_recovered_calc,
p_ssp_amt_recovered_calc,
p_nic_comp_smp_calc,
p_nic_comp_sap_calc,
p_nic_comp_ospp_calc,
p_nic_comp_aspp_calc,
p_nic_comp_ssp_calc,
to_date(SYSDATE,'DD-MM-RRRR'),
SYSDATE,
p_status,
p_user_id,
l_approver_id,
1,
p_request_id,
p_program_application_id,
p_program_id,
p_update_date
);

Line: 227

                hr_utility.set_location('Inserted the EPS record',100);

Line: 230

END insert_eps_record;

Line: 232

PROCEDURE delete_eps_record
        ( p_effective_date             IN VARCHAR2,
          p_emp_paye_reference         IN VARCHAR2,
          p_business_group_id          IN VARCHAR2,
		  p_return_status              OUT NOCOPY VARCHAR2
         )
--pay_gb_eps.delete_eps_record
IS

l_proc    varchar2(72) := g_package||'delete_eps_record';

Line: 248

    DELETE
    FROM  pay_gb_eps_details
    WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
    AND   EFFECTIVE_DATE = p_effective_date
    and business_group_id = p_business_group_id;

Line: 257

	hr_utility.set_location('Deleted the EPS record',100);

Line: 265

END delete_eps_record;

Line: 279

select emp_paye_reference
from pay_gb_eps_details
where emp_paye_reference = p_emp_paye_reference
and business_group_id = p_business_group_id
and effective_date > p_effective_date
and status <> 'Saved';

Line: 316

select PROFILE_OPTION_VALUE from
fnd_profile_option_values fpov,fnd_profile_options fpo
where fpov.PROFILE_OPTION_ID = fpo.PROFILE_OPTION_ID
and fpo.PROFILE_OPTION_NAME = 'GB_EPS_APPROVAL';

Line: 345

PROCEDURE update_status_eps_record
        ( p_effective_date             IN VARCHAR2,
          p_emp_paye_reference         IN VARCHAR2,
          p_business_group_id          IN VARCHAR2,
					p_status                     IN VARCHAR2,
					p_object_version_number      IN NUMBER,
					p_workflow                   IN VARCHAR2,
					p_return_status              OUT NOCOPY VARCHAR2
         )
IS


	cursor c_get_approver is
	select papf.email_address email_address
	from per_all_people_f papf ,pay_gb_eps_details pged
	where pged.effective_date = p_effective_date
	and pged.emp_paye_reference = p_emp_paye_reference
	and papf.person_id = pged.approver_id
  order by papf.effective_start_date desc;

Line: 366

	select papf.email_address email_address
	from per_all_people_f papf ,pay_gb_eps_details pged
	where pged.effective_date = p_effective_date
	and pged.emp_paye_reference = p_emp_paye_reference
	and papf.person_id = pged.user_id
  order by papf.effective_start_date desc;

Line: 374

  select user_name from fnd_user
  where employee_id = personid;

Line: 378

	select object_version_number
	from pay_gb_eps_details
	where emp_paye_reference = p_emp_paye_reference
	and effective_date = p_effective_date
  and business_group_id = p_business_group_id;

Line: 385

	select user_id , approver_id
	from pay_gb_eps_details
	where emp_paye_reference = p_emp_paye_reference
	and effective_date = p_effective_date
  and business_group_id = p_business_group_id;

Line: 398

l_proc    varchar2(72) := g_package||'update_status_eps_record';

Line: 438

	UPDATE
	PAY_GB_EPS_DETAILS
    SET STATUS                 = l_status,
        REQUEST_ID             = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, request_id, FND_GLOBAL.CONC_REQUEST_ID),
        PROGRAM_APPLICATION_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_application_id, FND_GLOBAL.PROG_APPL_ID),
        PROGRAM_ID             = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_id, FND_GLOBAL.CONC_PROGRAM_ID),
        PROGRAM_UPDATE_DATE    = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_update_date, SYSDATE)
	WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
  AND   EFFECTIVE_DATE = p_effective_date
  AND   BUSINESS_GROUP_ID = p_business_group_id;

Line: 489

	hr_utility.set_location('Updated the status of the EPS record',100);

Line: 498

END update_status_eps_record;

Line: 501

PROCEDURE update_eps_record
        (
					p_effective_date            IN VARCHAR2,
					p_emp_paye_reference        IN VARCHAR2,
					p_tax_free_pymt             IN VARCHAR2,
					p_emp_third_party           IN VARCHAR2,
					p_exp_ben_othrs  			      IN VARCHAR2,
					p_p11d_due  				        IN VARCHAR2,
					p_emp_outside_uk  			    IN VARCHAR2,
					p_payment_due  			        IN VARCHAR2,
					p_final_submission_ceased  	IN VARCHAR2,
					p_final_submission_year  		IN VARCHAR2,
					p_service_company  			    IN VARCHAR2,
					p_smp_amt_recovered_calc  	IN NUMBER,
					p_sap_amt_recovered_calc  	IN NUMBER,
					p_ospp_amt_recovered_calc  	IN NUMBER,
					p_aspp_amt_recovered_calc   IN NUMBER,
					p_ssp_amt_recovered_calc    IN NUMBER,
					p_nic_comp_smp_calc    			IN NUMBER,
					p_nic_comp_sap_calc  				IN NUMBER,
					p_nic_comp_ospp_calc  			IN NUMBER,
					p_nic_comp_aspp_calc  			IN NUMBER,
					p_nic_comp_ssp_calc  				IN NUMBER,
					p_cis_deductions_suff  			IN NUMBER,
					p_nic_holiday_val  				  IN NUMBER,
					p_adv_from_hmrc  					  IN NUMBER,
					p_status  						      IN VARCHAR2,
					p_approver_id               IN NUMBER,
					p_object_version_number     IN NUMBER,
					p_business_group_id         IN VARCHAR2,
					p_no_payment_date_from      IN VARCHAR2,
					p_no_payment_date_to      IN VARCHAR2,
					p_period_of_inactivity_from  IN VARCHAR2,
					p_period_of_inactivity_to    IN VARCHAR2,
					p_date_scheme_ceased         IN VARCHAR2,
					p_return_status             OUT NOCOPY VARCHAR2
				)
IS



l_proc    varchar2(72) := g_package||'update_eps_record';

Line: 545

select object_version_number,related_tax_year
from pay_gb_eps_details
where emp_paye_reference = p_emp_paye_reference
and effective_date = p_effective_date
and business_group_id = p_business_group_id;

Line: 564

	 hr_utility.set_location(' before update smp recovered amount in pkb:' || p_smp_amt_recovered_calc,160);

Line: 572

	 --select effective_date into system_date from fnd_sessions where session_id = userenv('SESSIONID');

Line: 721

	 UPDATE PAY_GB_EPS_DETAILS
   SET
		TAX_FREE_PYMT           = p_tax_free_pymt,
		EMP_THIRD_PARTY         = p_emp_third_party,
		EXP_BEN_OTHERS           = p_exp_ben_othrs,
		P11D_DUE                = p_p11d_due,
		EMP_OUTSIDE_UK          = p_emp_outside_uk,
	  PAYMENT_DUE             = p_payment_due,
		FINAL_SUBMISSION_CEASED = p_final_submission_ceased,
		FINAL_SUBMISSION_YEAR   = p_final_submission_year,
		SERVICE_COMPANY         = p_service_company,
		SMP_AMT_RECOVERED_CALC  = p_smp_amt_recovered_calc,
		SAP_AMT_RECOVERED_CALC  = p_sap_amt_recovered_calc,
		OSPP_AMT_RECOVERED_CALC = p_ospp_amt_recovered_calc,
		ASPP_AMT_RECOVERED_CALC = p_aspp_amt_recovered_calc,
		SSP_AMT_RECOVERED_CALC  = p_ssp_amt_recovered_calc,
		NIC_COMP_SMP_CALC       = p_nic_comp_smp_calc,
		NIC_COMP_SAP_CALC       = p_nic_comp_sap_calc,
		NIC_COMP_OSPP_CALC      = p_nic_comp_ospp_calc,
		NIC_COMP_ASPP_CALC      = p_nic_comp_aspp_calc,
		NIC_COMP_SSP_CALC       = p_nic_comp_ssp_calc,
		CIS_DEDUCTIONS_STUFF     = p_cis_deductions_suff,
		NIC_HOLIDAY_VAL         = p_nic_holiday_val,
		ADV_FROM_HMRC           = p_adv_from_hmrc,
		APPROVER_ID             = l_approver_id,
		NO_PAYMENT_DATE_FROM    = p_no_payment_date_from,
		NO_PAYMENT_DATE_TO      = p_no_payment_date_to,
		PERIOD_OF_INACTIVITY_FROM = p_period_of_inactivity_from,
		PERIOD_OF_INACTIVITY_TO   = p_period_of_inactivity_to,
		DATE_SCHEME_CEASED      = p_date_scheme_ceased,
		REQUEST_ID              = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, request_id, FND_GLOBAL.CONC_REQUEST_ID),
		PROGRAM_APPLICATION_ID  = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_application_id, FND_GLOBAL.PROG_APPL_ID),
		PROGRAM_ID              = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_id, FND_GLOBAL.CONC_PROGRAM_ID),
		PROGRAM_UPDATE_DATE     = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_update_date, SYSDATE)
	 WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
   AND   EFFECTIVE_DATE     = p_effective_date
   AND   BUSINESS_GROUP_ID  = p_business_group_id;

Line: 762

	hr_utility.set_location('Updated the EPS record',100);

Line: 772

END update_eps_record;

Line: 774

procedure update_final_submission(
					p_effective_date            IN VARCHAR2,
          p_emp_paye_reference        IN VARCHAR2,
          p_business_group_id         IN VARCHAR2,
					p_final_submission_ceased   IN VARCHAR2,
					p_final_submission_year     IN VARCHAR2
	)
is
l_proc    varchar2(72) := g_package||'update_final_submission';

Line: 787

	UPDATE
	PAY_GB_EPS_DETAILS
	SET FINAL_SUBMISSION_CEASED = p_final_submission_ceased,
			FINAL_SUBMISSION_YEAR = p_final_submission_year
	WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
  AND   EFFECTIVE_DATE = p_effective_date
  AND   BUSINESS_GROUP_ID =   p_business_group_id;

Line: 880

    SELECT COUNT(*)
    INTO   n_ctr
    FROM   wf_local_roles
    WHERE  NAME = v_role_email;

Line: 911

select emp_paye_reference,object_version_number ovn,account_office_number,employer_name,econ_number,related_tax_year,final_submission_ceased,final_submission_year from
pay_gb_eps_details
where emp_paye_reference = p_emp_paye_reference
and effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
and business_group_id = p_business_group_id
and status = 'Verified';

Line: 920

select emp_paye_reference,object_version_number ovn,account_office_number,employer_name,econ_number,related_tax_year,final_submission_ceased,final_submission_year from
pay_gb_eps_details
where effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
and business_group_id = p_business_group_id
and status = 'Verified';

Line: 927

select REQUEST_ID from FND_CONC_REQ_SUMMARY_V
where PROGRAM_SHORT_NAME='PYGBRTIEPS13'
and REQUEST_DATE = (select max(request_date) from FND_CONC_REQ_SUMMARY_V where PROGRAM_SHORT_NAME='PYGBRTIEPS13');

Line: 957

select to_char(to_date(substr(p_effective_date,0,10),'YYYY-MM-DD'),'DD-MON-YYYY') into l_effective_date from dual;

Line: 991

				-- To update Final Submission ceased and Final Submission Year
				update_final_submission(l_effective_date,p_emp_paye_reference,p_business_group_id,l_csr_eps.final_submission_ceased,l_csr_eps.final_submission_year);

Line: 1015

								-- Update the status of the EPS Record to 'Sent'
								update_status_eps_record(l_effective_date,l_csr_eps.emp_paye_reference,p_business_group_id,'Sent',l_csr_eps.ovn+1,'N',l_result);

Line: 1024

											hr_utility.set_location('The record has been Updated successfully',100);

Line: 1058

				-- To update Final Submission ceased and Final Submission Year
				update_final_submission(l_effective_date,l_csr_eps_all_paye.emp_paye_reference,p_business_group_id,l_csr_eps_all_paye.final_submission_ceased,l_csr_eps_all_paye.final_submission_year);

Line: 1084

								-- Update the status of the EPS Record to 'Sent'
								update_status_eps_record(l_effective_date,l_csr_eps_all_paye.emp_paye_reference,p_business_group_id,'Sent',l_csr_eps_all_paye.ovn+1,'N',l_result);

Line: 1092

											hr_utility.set_location('The record has been Updated successfully',100);

Line: 1121

select emp_paye_reference,object_version_number ovn from
pay_gb_eps_details
where emp_paye_reference = p_emp_paye_reference
and effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
and business_group_id = p_business_group_id
and status = 'Sent';

Line: 1138

select to_char(to_date(substr(p_effective_date,0,10),'YYYY-MM-DD'),'DD-MON-YYYY') into l_effective_date from dual;

Line: 1148

			-- update the status to 'Sent'
      -- update final submission ceased and year to 'No'
			update_final_submission(l_effective_date,p_emp_paye_reference,p_business_group_id,'N','N');

Line: 1151

			update_status_eps_record(l_effective_date,p_emp_paye_reference,p_business_group_id,'Verified',l_csr_eps.ovn+1,'N',l_result);

Line: 1172

	select
	smp_amt_recovered_calc,
	sap_amt_recovered_calc,
	ospp_amt_recovered_calc,
	aspp_amt_recovered_calc,
	ssp_amt_recovered_calc,
	nic_comp_smp_calc,
	nic_comp_sap_calc,
	nic_comp_ospp_calc,
	nic_comp_aspp_calc,
	nic_comp_ssp_calc,
	cis_deductions_stuff,
	nic_holiday_val
  from pay_gb_eps_details
	where emp_paye_reference = p_emp_paye_reference
	and effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
  and business_group_id = p_business_group_id
	and status = 'Verified';

Line: 1293

select nvl(UPPER(substr(hoi.org_information11,1,35)),' ') SENDER_ID
from hr_organization_information hoi
where   hoi.org_information_context = 'Tax Details References'
        and   nvl(hoi.org_information10,'UK') = 'UK'
        and   hoi.org_information1 = emp_paye_reference;

Line: 1549

select effective_date into system_date from fnd_sessions where session_id = userenv('SESSIONID');