[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM pay_us_rpt_totals
WHERE business_group_id=g_sum_bal_info(1).business_group_id
AND state_name='CARMILEAGE_UPGRADE'
AND tax_unit_id = 250
AND location_id = g_sum_bal_info(1).assignment_id;
g_sum_bal_info.delete;
g_comp_act_miles.delete;
g_payroll_det_cache.delete;
INSERT INTO
pay_us_rpt_totals(
business_group_id
,location_id
,location_name
,state_name
,organization_name
,tax_unit_id
) VALUES (
g_err_info(i).business_group_id
,g_err_info(i).assignment_id
,g_err_info(i).element_name
,'CARMILEAGE_UPGRADE'
,fnd_date.date_to_canonical(l_effective_date)
,250
);
g_err_info.delete;
SELECT ppa.payroll_id
,ppa.consolidation_set_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id= cp_assignment_id
AND ppa.payroll_action_id=paa.payroll_action_id
AND ppa.effective_date = cp_max_date
AND ppa.business_group_id= cp_business_group_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status='C'
AND paa.action_status='C'
AND paa.run_type_id IS NOT NULL;
SELECT max(ppa.effective_date) effective_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_id= cp_assignment_id
AND ppa.payroll_action_id=paa.payroll_action_id
AND ppa.business_group_id= cp_business_group_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status='C'
AND paa.action_status='C'
AND paa.run_type_id IS NOT NULL;
select element_type_id
from pay_element_types_f
where element_name = p_element_name
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
SELECT piv.input_value_id
,piv.display_sequence
,piv.name
FROM pay_input_values_f piv
WHERE piv.element_type_id =cp_element_id
AND p_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
ORDER BY piv.display_sequence;
SELECT balance_name,balance_type_id
FROM pay_balance_types
WHERE balance_name = p_balance_name;
select max(assignment_action_id)
from pay_assignment_actions
where assignment_id = p_assignment_id;
select /*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) *
FEED.scale),0) tot
from
pay_balance_feeds_f FEED
,pay_run_result_values TARGET
,pay_run_results RR
,pay_payroll_actions PACT
,pay_assignment_actions ASSACT
,pay_payroll_actions BACT
,per_time_periods BPTP
,per_time_periods PPTP
,pay_assignment_actions BAL_ASSACT
,per_assignments_f ASS
,per_assignments_f START_ASS
WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
AND BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
AND FEED.balance_type_id = cp_balance_type_id
AND FEED.input_value_id = TARGET.input_value_id
AND TARGET.run_result_id = RR.run_result_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND ASSACT.payroll_action_id = PACT.payroll_action_id
AND PACT.effective_date BETWEEN
FEED.effective_start_date AND FEED.effective_end_date
AND RR.status in ('P','PA')
AND BPTP.time_period_id = BACT.time_period_id
AND PPTP.time_period_id = PACT.time_period_id
AND START_ASS.assignment_id = BAL_ASSACT.assignment_id
AND ASS.period_of_service_id = START_ASS.period_of_service_id
AND ASSACT.assignment_id = ASS.assignment_id
AND BACT.effective_date BETWEEN
START_ASS.effective_start_date AND START_ASS.effective_end_date
AND PACT.effective_date BETWEEN
ASS.effective_start_date AND ASS.effective_end_date
AND PACT.effective_date >=
/* find the latest td payroll transfer date - compare each of the */
/* assignment rows with its predecessor looking for the payroll */
/* that had a different tax district at that date */
(SELECT nvl(max(NASS.effective_start_date),
to_date('01/01/0001', 'DD/MM/YYYY'))
FROM per_assignments_f NASS
,pay_payrolls_f ROLL
,hr_soft_coding_keyflex FLEX
,per_assignments_f PASS
,pay_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
WHERE NASS.assignment_id = ASS.assignment_id
AND ROLL.payroll_id = NASS.payroll_id
AND NASS.effective_start_date BETWEEN
ROLL.effective_start_date AND ROLL.effective_end_date
AND ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
AND NASS.assignment_id = PASS.assignment_id
AND PASS.effective_end_date = (NASS.effective_start_date - 1)
AND NASS.effective_start_date <= BACT.effective_date
AND PROLL.payroll_id = PASS.payroll_id
AND NASS.effective_start_date BETWEEN
PROLL.effective_start_date AND PROLL.effective_end_date
AND PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
AND NASS.payroll_id <> PASS.payroll_id
AND FLEX.segment1 <> PFLEX.segment1 )
AND EXISTS
/* check that the current assignment tax districts match */
(SELECT NULL
FROM pay_payrolls_f BROLL
,hr_soft_coding_keyflex BFLEX
,pay_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
WHERE BACT.payroll_id = BROLL.payroll_id
AND PACT.payroll_id = PROLL.payroll_id
AND BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
AND PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
AND BACT.effective_date BETWEEN
BROLL.effective_start_date AND BROLL.effective_end_date
AND BACT.effective_date BETWEEN
PROLL.effective_start_date AND PROLL.effective_end_date
AND BFLEX.segment1 = PFLEX.segment1 )
AND PPTP.regular_payment_date >=
/* fin year start is last two years for a even tax year and last one
* year for a odd tax year
*/
to_date('06-04-' || to_char( fnd_number.canonical_to_number(
to_char( BPTP.regular_payment_date,'YYYY'))
+ decode(sign(BPTP.regular_payment_date - to_date('06-04-'
|| to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
-1,-1,0) -
mod(
fnd_number.canonical_to_number(
to_char( BPTP.regular_payment_date,'YYYY'))
+ decode(sign( BPTP.regular_payment_date - to_date('06-04-'
|| to_char(BPTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
-1,0,-1),2)
),'DD-MM-YYYY')
AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence ;
SELECT balance_name,balance_type_id
FROM pay_balance_types
WHERE balance_name = p_balance_name;
SELECT nvl((fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale),0) tot
FROM pay_run_result_values TARGET
, pay_balance_feeds_f FEED
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_assignment_actions BAL_ASSACT
, pay_payroll_actions PACT
WHERE BAL_ASSACT.assignment_action_id = cp_assignment_action_id
AND FEED.balance_type_id = cp_balance_type_id
AND FEED.input_value_id = TARGET.input_value_id
AND TARGET.run_result_id = RR.run_result_id
AND RR.assignment_action_id = ASSACT.assignment_action_id
AND ASSACT.payroll_action_id = PACT.payroll_action_id
AND assact.payroll_action_id = cp_payroll_action_id
AND PACT.effective_date between FEED.effective_start_date
AND FEED.effective_end_date
AND RR.status in ('P','PA')
AND ASSACT.action_sequence <= BAL_ASSACT.action_sequence
AND ASSACT.assignment_id = BAL_ASSACT.assignment_id
AND (( RR.source_id = cp_element_entry_id and source_type in ( 'E','I'))
OR ( rr.source_type in ('R','V') /* reversal */
AND exists
( SELECT null from pay_run_results rr1
WHERE rr1.source_id = cp_element_entry_id
AND rr1.run_result_id = rr.source_id
AND rr1.source_type in ( 'E','I'))));
Select prr.source_id element_entry_id
,prr.assignment_action_id
,prr.element_type_id
,pet.element_name element_name
,pet.business_group_id business_group_id
,paa.assignment_id assignment_id
,paa.payroll_action_id
,petei.EEI_INFORMATION1 Vehicle_Type
,ppa.effective_date effective_start_date
,prr.run_result_id
,prr.source_type
,prr.source_id
,to_char(NULL) usage_type
,to_char(NULL) Ownership
,to_char(NULL) additional_passenger
,to_char(NULL) Paye_taxable
,to_date(NULL) Claim_end_date
,to_char(NULL) Rates_table
,to_number(NULL) Engine_capacity
,to_char(NULL) Calculation_Method
,to_number(NULL) Claimed_Mileage
,to_number(NULL) Actual_Mileage
From pay_element_types_f pet
,pay_element_type_extra_info petei
,pay_assignment_actions paa
,pay_run_results prr
,pay_payroll_actions ppa
WHERE pet.business_group_id = p_business_group_id
AND pet.element_type_id=petei.element_type_id
AND petei.information_type='PQP_VEHICLE_MILEAGE_INFO'
AND petei.eei_information_category='PQP_VEHICLE_MILEAGE_INFO'
AND petei.EEI_INFORMATION1 in ('C','P','CM','CP','PP','PM')
AND prr.element_type_id=pet.element_type_id
AND prr.assignment_action_id=paa.assignment_action_id
AND ppa.payroll_action_id=paa.payroll_action_id
AND ppa.business_group_id= pet.business_group_id
AND ppa.effective_date >= to_date('04/06/2003','MM/DD/YYYY')
AND TRUNC(ppa.creation_date) < cp_effective_date
AND (cp_patch_status='N'
OR Exists(SELECT 'X'
FROM pay_us_rpt_totals
WHERE state_name = 'CARMILEAGE_UPGRADE'
AND tax_unit_id=250
AND location_id = paa.assignment_id
AND business_group_id = p_business_group_id))
-- AND prr.source_type in ('E','R')
ORDER BY paa.assignment_id,ppa.effective_date,prr.run_result_id;
SELECT piv.input_value_id
,piv.name
,prrv.result_value entry_value
,piv.display_sequence
FROM pay_input_values_f piv
,pay_run_result_values prrv
WHERE prrv.run_result_id = cp_run_result_id
AND piv.input_value_id=prrv.input_value_id
AND piv.name IN ('Vehicle Type'
,'Rate Type'
,'No of Passengers'
,'PAYE Taxable'
,'Claim End Date'
,'User Rates Table'
,'Engine Capacity'
,'Calculation Method'
,'Claimed Mileage'
,'Actual Mileage'
);
select status
from pay_patch_status
where patch_name = 'CARMILEAGE_BALANCE_ADJ'
and patch_number = p_business_group_id
and phase = 'CARMILEAGE_BALANCE_ADJ';
select update_date
from pay_patch_status
where patch_name = 'CARMILEAGE_UPDATE'
and patch_number = -100;
l_update_date DATE;
FETCH c_upgrade_patch_status INTO l_update_date;
,l_update_date);
l_bal_info.delete;
INSERT INTO pay_patch_status
(ID
,PATCH_NUMBER
,PATCH_NAME
,STATUS
,PHASE
)
VALUES (pay_patch_status_s.NEXTVAL
,p_business_group_id
,'CARMILEAGE_BALANCE_ADJ'
,upgrade_status
,'CARMILEAGE_BALANCE_ADJ'
);
update pay_patch_status
set STATUS = upgrade_status
where patch_name = 'CARMILEAGE_BALANCE_ADJ'
and phase = 'CARMILEAGE_BALANCE_ADJ'
and PATCH_NUMBER = p_business_group_id;