[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into v_count
from pay_all_payrolls_f pap,
hr_soft_coding_keyflex scl
where pap.business_group_id = (select pap2.business_group_id from pay_all_payrolls_f pap2 where pap2.payroll_id=p_payroll_id and rownum=1)
and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment8 = p_irp5no
and to_date(v_tax_end_date,'DD-MM-YYYY') between pap.effective_start_date and pap.effective_end_date;
select to_char(min(start_date),'dd-mon-yyyy'),to_char(max(end_date),'dd-mon-yyyy')
into p_tax_start_date,p_tax_end_date
from per_time_periods
where payroll_id = p_payroll_id
and prd_information1 = p_tax_year;
/* Cursor to select all Income Sars Codes which have negative balances*/
g_default_clrno CONSTANT VARCHAR2(11) := '99999999999' ;
select irp5.code,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
WHERE dbi.user_entity_id = arc.user_entity_id
and irp5.user_name = dbi.user_name
AND arc.context1 = p_asgn_action_id
and irp5.balance_sequence = 1
and (
(irp5.code BETWEEN 3601 AND 3607)
OR
(irp5.code BETWEEN 3609 AND 3613)
or
(irp5.code BETWEEN 3615 AND 3617 ) -- 3608 and 3614 are LMPSM balance
or
(irp5.code BETWEEN 3651 AND 3667)
OR
(irp5.code BETWEEN 3701 AND 3706)
OR
(irp5.code BETWEEN 3708 AND 3717) -- 3707 and 3718 are LMPSM balances
OR
(irp5.code BETWEEN 3751 AND 3768)
or
(irp5.code BETWEEN 3801 and 3810)
or
(irp5.code BETWEEN 3851 and 3860)
or
(irp5.code BETWEEN 3813 and 3863)
OR -- 3901 to 3907 are LMPSM balances
(irp5.code BETWEEN 3951 and 3957)
or
(irp5.code BETWEEN 3695 and 3699)
OR -- 4001 to 4004, 4006, 4007 are Deduction balances
(irp5.code = 4005 )
or
(irp5.code = 4018)
or
(irp5.code BETWEEN 4024 and 4025)
or
(irp5.code BETWEEN 4101 and 4103)
or
(irp5.code BETWEEN 4472 and 4474)
or
(irp5.code BETWEEN 4485 and 4487)
or
(irp5.code = 4493)
)
group by irp5.code
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) < 0 ;
select irp5.code,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
ff_archive_item_contexts faic2,
ff_contexts ffc2
WHERE irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3908, 3909, 3915)
AND irp5.balance_sequence = 3
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
AND faic2.archive_item_id = arc.archive_item_id
AND ffc2.context_id = faic2.context_id
AND ffc2.context_name = 'SOURCE_TEXT'
AND faic2.CONTEXT = 'To Be Advised'
group by irp5.code
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0 ;
select sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) value
, faic2.CONTEXT Tax_Directive_Number
, irp5.code code
from pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
ff_archive_item_contexts faic2,
ff_contexts ffc2
where arc.context1 in (select ch.assignment_action_id
from pay_assignment_actions main
, pay_assignment_actions ch
where main.assignment_action_id = p_asg_act_id
and ch.payroll_action_id = main.payroll_action_id
and ch.assignment_action_id < main.assignment_action_id
AND ch.assignment_id = main.assignment_id)
and
(
arc.value is not null
or
(
arc.value is not null
and arc.value <> 0
)
)
and dbi.user_entity_id = arc.user_entity_id
and irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3908, 3909, 3915)
AND irp5.balance_sequence = 3
AND dbi.user_name = irp5.user_name
AND faic2.archive_item_id = arc.archive_item_id
AND ffc2.context_id = faic2.context_id
AND ffc2.context_name = 'SOURCE_TEXT'
group BY faic2.CONTEXT
, irp5.code
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) < 0;
select irp5.code ,
faic2.CONTEXT clearance_num,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
ff_archive_item_contexts faic2,
ff_contexts ffc2
WHERE irp5.code IN (4001, 4002, 4003, 4004, 4006, 4007)
AND irp5.balance_sequence = 1
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
AND faic2.archive_item_id = arc.archive_item_id
AND ffc2.context_id = faic2.context_id
AND ffc2.context_name = 'SOURCE_NUMBER'
group by irp5.code,
faic2.CONTEXT
HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0)
OR (faic2.CONTEXT = g_default_clrno
and sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) <> 0);
select irp5.code,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
WHERE irp5.code IN (3810, 3813, 4005, 4024, 4025, 4485, 4486)
AND irp5.balance_sequence = 1
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
group by irp5.code;
SELECT assignment_id,
max(assignment_action_id) assignment_action_id -- max assignment_action_id relates to Main Certificate
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
GROUP BY assignment_id ;
select paa.assignment_action_id
from pay_action_contexts pac,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_contexts ffc
where paa.assignment_id = p_asgn_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q','B')
AND pac.assignment_Action_id = paa.assignment_action_id
And pac.context_value = g_default_clrno
and ffc.context_name = 'SOURCE_NUMBER'
and ffc.context_id = pac.context_id
and ppa.effective_date >= p_start_date
and ppa.effective_date <= p_end_date;
Select element_name
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prv
Where paa.assignment_action_id = p_asgn_ac_id
and prr.assignment_Action_id = paa.assignment_action_id
and pet.element_type_id = prr.element_type_id
and piv.element_type_id = pet.element_type_id
and piv.name = 'Clearance Number'
and prv.run_result_id = prr.run_result_id
and prv.input_value_id = piv.input_value_id
and prv.RESULT_VALUE = g_default_clrno
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between piv.effective_start_date and piv.effective_end_date ;
select irp5.code,
irp5.full_balance_name bal_name,
irp5.balance_type_id bal_type_id,
trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
per_assignment_extra_info paei,
pay_assignment_actions paa
WHERE dbi.user_name in
(
'A_ANNUAL_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
'A_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
'A_USE_OF_MOTOR_VEHICLE_PKG_ASG_TAX_YTD',
'A_RIGHT_OF_USE_OF_ASSET_PKG_ASG_TAX_YTD',
'A_MEALS_REFRESHMENTS_AND_VOUCHERS_PKG_ASG_TAX_YTD',
'A_FREE_OR_CHEAP_ACCOMMODATION_PKG_ASG_TAX_YTD',
'A_FREE_OR_CHEAP_SERVICES_PKG_ASG_TAX_YTD',
'A_LOW_OR_INTEREST_FREE_LOANS_PKG_ASG_TAX_YTD',
'A_ANNUAL_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
'A_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
'A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
'A_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
'A_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
'A_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
'A_TAXABLE_INCOME_PKG_ASG_TAX_YTD',
'A_TAXABLE_PENSION_PKG_ASG_TAX_YTD',
'A_ANNUAL_BONUS_PKG_ASG_TAX_YTD',
'A_TAXABLE_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
'A_ANNUAL_COMMISSION_PKG_ASG_TAX_YTD',
'A_COMMISSION_PKG_ASG_TAX_YTD',
'A_ANNUAL_OVERTIME_PKG_ASG_TAX_YTD',
'A_OVERTIME_PKG_ASG_TAX_YTD',
'A_ANNUITY_FROM_RETIREMENT_FUND_PKG_ASG_TAX_YTD',
'A_PURCHASED_ANNUITY_TAXABLE_PKG_ASG_TAX_YTD',
'A_ANNUAL_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
'A_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
'A_ANNUAL_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
'A_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
'A_ANNUAL_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
'A_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
'A_TRAVEL_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TAXABLE_REIMBURSIVE_TRAVEL_PKG_ASG_TAX_YTD',
'A_TAXABLE_SUBSISTENCE_PKG_ASG_TAX_YTD',
'A_ENTERTAINMENT_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_PUBLIC_OFFICE_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TOOL_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_COMPUTER_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TELEPHONE_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_OTHER_TAXABLE_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_PKG_ASG_TAX_YTD',
'A_EE_BROADBASED_SHARE_PLAN_PKG_ASG_TAX_YTD',
'A_OTHER_LUMP_SUM_TAXED_AS_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD',
'A_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD'
)
AND irp5.balance_sequence = 1
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND paei.assignment_id = paa.assignment_id
AND arc.context1 = p_asgn_action_id
AND arc.context1 = paa.assignment_action_id
AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis: Fixed Percentage of Specific Income
AND paei.information_type = 'ZA_SPECIFIC_INFO'
AND pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)) <> '0';
SELECT ELEM.element_name element_name,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
from pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, per_time_periods PPTP
, per_time_periods BPTP
, pay_payroll_actions PACT
, pay_assignment_actions ASSACT
, pay_payroll_actions BACT
, pay_assignment_actions BAL_ASSACT
, pay_element_types_f ELEM
where BAL_ASSACT.assignment_action_id = p_asgn_action_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_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
+ decode(PPTP.year_number, 0, 0, 0)
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and BPTP.payroll_id = BACT.payroll_id
and PPTP.payroll_id = PACT.payroll_id
and nvl(BACT.date_earned,BACT.effective_date)
between BPTP.start_date and BPTP.end_date
and PACT.date_earned between PPTP.start_date and PPTP.end_date
and RR.status in ('P','PA')
AND ELEM.element_type_id = RR.element_type_id
and PPTP.prd_information1 = BPTP.prd_information1
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
AND feed.BALANCE_TYPE_ID = p_bal_typ_id
GROUP BY ELEM.element_name
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
SELECT per.employee_number empno, asgn2.assignment_number assgno
FROM pay_assignment_actions paa,
per_all_assignments_f asgn2,
per_all_people_f per,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = asgn_ac_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND asgn2.assignment_id = paa.assignment_id
AND per.person_id = asgn2.person_id
AND asgn2.effective_start_date =
( select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.effective_start_date <= ppa.effective_date
and paf2.assignment_id = asgn2.assignment_id
)
AND per.effective_start_date =
( select max(per2.effective_start_date)
from per_all_people_f per2
where per2.effective_start_date <= ppa.effective_date
and per2.person_id = per.person_id
);
SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM ff_archive_items arc,
ff_database_items dbi,
pay_za_irp5_bal_codes irp5
WHERE arc.user_entity_id = dbi.user_entity_id
and irp5.user_name = dbi.user_name
and arc.context1 in (p_asgn_action_id)
and code in (3915,4115)
GROUP BY irp5.code ;
select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
into l_tax_ytd
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_TAX_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = asgn.assignment_action_id;
select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
into l_site
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = asgn.assignment_action_id;
select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
into l_paye
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_PAYE_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = asgn.assignment_action_id;