[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
into l_tax_status_name
from fnd_common_lookups
where lookup_type = 'ZA_TAX_STATUS'
and lookup_code = l_tax_status;
l_sql := 'Select nvl(ettl.reporting_name,et.element_type_id) COL01
,nvl(ettl.reporting_name,ettl.element_name) COL02
,to_char(sum(fnd_number.canonical_to_number(rrv.result_value)),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
,decode(count(*),1,''1'',''2'') COL17 -- destination indicator
,decode(count(*),1,max(rr.run_result_id),max(et.element_type_id)) COL18
From pay_assignment_actions aa
,pay_run_results rr
,pay_run_result_values rrv
,pay_input_values_f iv
,pay_input_values_f_tl ivtl
,pay_element_types_f et
,pay_element_types_f_tl ettl
,pay_element_set_members esm
,pay_element_sets es
Where aa.assignment_action_id :action_clause
and aa.assignment_action_id = rr.assignment_action_id
and rr.status in (''P'',''PA'')
and rr.run_result_id = rrv.run_result_id
and rr.element_type_id = et.element_type_id
and :effective_date between et.effective_start_date and et.effective_end_date
and et.element_type_id = ettl.element_type_id
and rrv.input_value_id = iv.input_value_id
and iv.name = ''Pay Value''
and :effective_date between iv.effective_start_date and iv.effective_end_date
and iv.input_value_id = ivtl.input_value_id
and ettl.language = userenv(''LANG'')
and ivtl.language = userenv(''LANG'')
and et.element_type_id = esm.element_type_id
and esm.element_set_id = es.element_set_id
and ( es.BUSINESS_GROUP_ID is null
or es.BUSINESS_GROUP_ID = :business_group_id )
and ( es.LEGISLATION_CODE is null
or es.LEGISLATION_CODE = '':legislation_code'' )
and es.element_set_name = '''|| p_element_set_name ||'''
group by nvl(ettl.reporting_name,ettl.element_name)
,ettl.reporting_name
,nvl(ettl.reporting_name,et.element_type_id)
having nvl(sum(fnd_number.canonical_to_number(rrv.result_value)),0) != 0
order by nvl(ettl.reporting_name,ettl.element_name)';
Select org.name as COL01 -- Organisation Name
,org2.name as COL02 -- Legal Entity Name
,job.name as COL03
,loc.location_code as COL04
,grd.name as COL05
,pos.name as COL06
,peo.national_identifier as COL07
,employee_number as COL08
,hl.meaning as COL09 -- Nationality
,asg.assignment_number as COL10
,fnd_date.date_to_displaydate(pps.date_start) as COL11
,fnd_date.date_to_displaydate(pps.actual_termination_date) as COL12
,to_char(fnd_number.canonical_to_number(nvl(ppb1.salary,''0'')),fnd_currency.get_format_mask(:g_currency_code,40)) as COL13
,peo.per_information1 as COL14 -- Tax Reference Number
,pay_za_soe.get_tax_status(asg.assignment_id, :effective_date) as COL15
,ptp.period_num as COL16
From per_all_people_f peo
,per_all_assignments_f asg
,per_jobs_vl job
,pay_assignment_actions paa
,per_assignment_extra_info pae
,per_periods_of_service pps
,pay_payroll_actions ppa
,per_time_periods ptp
,per_time_period_types ptt
,hr_all_organization_units_vl org
,hr_all_organization_units_vl org2
,hr_locations loc
,per_grades_vl grd
,per_all_positions pos
,pay_payrolls_f pay
,pay_people_groups pg
,hr_lookups hl
,(select ppb2.pay_basis_id
,ppb2.business_group_id
,ee.assignment_id
,eev.screen_entry_value as salary
from per_pay_bases ppb2
,pay_element_entries_f ee
,pay_element_entry_values_f eev
where ppb2.input_value_id = eev.input_value_id
and ee.element_entry_id = eev.element_entry_id
and :effective_date between ee.effective_start_date and ee.effective_end_date
and :effective_date between eev.effective_start_date and eev.effective_end_date
) ppb1
Where asg.assignment_id = :assignment_id and
:effective_date between asg.effective_start_date and asg.effective_end_date and
asg.person_id = peo.person_id and
:effective_date between peo.effective_start_date and peo.effective_end_date and
asg.job_id = job.job_id(+) and
asg.pay_basis_id = ppb1.pay_basis_id(+) and
asg.assignment_id = ppb1.assignment_id(+) and
asg.business_group_id = ppb1.business_group_id(+) and
paa.assignment_action_id = ''' || p_assignment_action_id || ''' and
ppa.payroll_action_id = paa.payroll_action_id and
ptp.payroll_id = ppa.payroll_id AND '
|| 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')' || ' between ptp.start_date and ptp.end_date and
ptp.period_type = ptt.period_type and
pps.period_of_service_id = asg.period_of_service_id and
asg.organization_id = org.organization_id and
:effective_date between org.date_from and nvl(org.date_to, :effective_date) and
pae.assignment_id(+) = asg.assignment_id and
pae.aei_information_category(+) = ''ZA_SPECIFIC_INFO'' and
org2.organization_id(+) = pae.aei_information7 and
org2.date_from(+) <= :effective_date and
nvl(org2.date_to(+), :effective_date) >= :effective_date and
asg.location_id = loc.location_id(+) and
asg.grade_id = grd.grade_id(+) and
asg.people_group_id = pg.people_group_id(+) and
asg.position_id = pos.position_id(+) and
asg.payroll_id = pay.payroll_id(+) and
:effective_date between pay.effective_start_date(+) and pay.effective_end_date(+) and
hl.application_id (+) = 800 and
hl.lookup_type (+) = ''NATIONALITY'' and
hl.lookup_code (+) = peo.nationality';
'Select ptp.period_name as COL01 -- Period Name
,fnd_date.date_to_displaydate(ppa.effective_date) as COL02 -- Pay Date
,ptp.period_type as COL03 -- Period Type
,fnd_date.date_to_displaydate(ptp.start_date) as COL04 -- Period Start Date
,fnd_date.date_to_displaydate(ptp.end_date) as COL05 -- Period End Date
From per_time_periods ptp
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_period_types ptt
Where paa.assignment_action_id = ''' || p_assignment_action_id || ''' and
paa.payroll_action_id = ppa.payroll_action_id and
ptp.payroll_id = ppa.payroll_id AND '
|| 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')' || ' between ptp.start_date and ptp.end_date and
ptp.period_type = ptt.period_type';
This function is used to update balance details of Assignment Action Id.
There are 125 balances listed to include into balance region. Apart from
this wser can update 'SOE Detail Information' DFF to include those
balances to appear on Balances region of Online ZA SOE.
------------------------------------------------------------------------ */
function Balance_Details(p_assignment_action_id in number) return long is
--
l_sql long;
Select pbt.balance_name as balance_name
,nvl(org.org_information7,pbt.balance_name) as display_name
,pbd.dimension_name as balance_suffix
From pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_all_assignments_f asg
,hr_organization_information org
Where paa.assignment_action_id = p_assignment_action_id and
paa.assignment_id = asg.assignment_id and
paa.payroll_action_id = ppa.payroll_action_id and
ppa.effective_date between asg.effective_start_date and asg.effective_end_date and
asg.organization_id = org.organization_id and
org.org_information_context = 'Business Group:SOE Detail' and
org.org_information1 = 'BALANCE' and
pbt.balance_type_id = org.org_information4 and
pbd.balance_dimension_id = org.org_information5;
select decode
(
name_count,
1 , 'Taxable Income RFI',
2 , 'Taxable Income NRFI',
3 , 'Taxable Income PKG',
4 , 'Non Taxable Income',
5 , 'Taxable Pension RFI',
6 , 'Taxable Pension NRFI',
7 , 'Taxable Pension PKG',
8 , 'Non Taxable Pension',
9 , 'Taxable Annual Payment RFI',
10 , 'Taxable Annual Payment NRFI',
11 , 'Taxable Annual Payment PKG',
12 , 'Annual Bonus RFI',
13 , 'Annual Bonus NRFI',
14 , 'Annual Bonus PKG',
15 , 'Commission RFI',
16 , 'Commission NRFI',
17 , 'Commission PKG',
18 , 'Overtime RFI',
19 , 'Overtime NRFI',
20 , 'Overtime PKG',
21 , 'Taxable Arbitration Award RFI',
22 , 'Taxable Arbitration Award NRFI',
23 , 'Non Taxable Arbitration Award',
24 , 'Annuity from Retirement Fund RFI',
25 , 'Annuity from Retirement Fund NRFI',
26 , 'Annuity from Retirement Fund PKG',
27 , 'Purchased Annuity Taxable RFI',
28 , 'Purchased Annuity Taxable NRFI',
29 , 'Purchased Annuity Taxable PKG',
30 , 'Purchased Annuity Non Taxable',
31 , 'Travel Allowance RFI',
32 , 'Travel Allowance NRFI',
33 , 'Travel Allowance PKG',
34 , 'Taxable Reimbursive Travel RFI',
35 , 'Taxable Reimbursive Travel NRFI',
36 , 'Taxable Reimbursive Travel PKG',
37 , 'Non Taxable Reimbursive Travel',
38 , 'Taxable Subsistence RFI',
39 , 'Taxable Subsistence NRFI',
40 , 'Taxable Subsistence PKG',
41 , 'Non Taxable Subsistence',
42 , 'Entertainment Allowance RFI',
43 , 'Entertainment Allowance NRFI',
44 , 'Entertainment Allowance PKG',
45 , 'Share Options Exercised RFI',
46 , 'Share Options Exercised NRFI',
47 , 'Public Office Allowance RFI',
48 , 'Public Office Allowance NRFI',
49 , 'Public Office Allowance PKG',
50 , 'Uniform Allowance',
51 , 'Tool Allowance RFI',
52 , 'Tool Allowance NRFI',
53 , 'Tool Allowance PKG',
54 , 'Computer Allowance RFI',
55 , 'Computer Allowance NRFI',
56 , 'Computer Allowance PKG',
57 , 'Telephone Allowance RFI',
58 , 'Telephone Allowance NRFI',
59 , 'Telephone Allowance PKG',
60 , 'Other Taxable Allowance RFI',
61 , 'Other Taxable Allowance NRFI',
62 , 'Other Taxable Allowance PKG',
63 , 'Other Non Taxable Allowance',
64 , 'Asset Purchased at Reduced Value RFI',
65 , 'Asset Purchased at Reduced Value NRFI',
66 , 'Asset Purchased at Reduced Value PKG',
67 , 'Use of Motor Vehicle RFI',
68 , 'Use of Motor Vehicle NRFI',
69 , 'Use of Motor Vehicle PKG',
70 , 'Right of Use of Asset RFI',
71 , 'Right of Use of Asset NRFI',
72 , 'Right of Use of Asset PKG',
73 , 'Meals Refreshments and Vouchers RFI',
74 , 'Meals Refreshments and Vouchers NRFI',
75 , 'Meals Refreshments and Vouchers PKG',
76 , 'Free or Cheap Accommodation RFI',
77 , 'Free or Cheap Accommodation NRFI',
78 , 'Free or Cheap Accommodation PKG',
79 , 'Free or Cheap Services RFI',
80 , 'Free or Cheap Services NRFI',
81 , 'Free or Cheap Services PKG',
82 , 'Low or Interest Free Loans RFI',
83 , 'Low or Interest Free Loans NRFI',
84 , 'Low or Interest Free Loans PKG',
85 , 'Payment of Employee Debt RFI',
86 , 'Payment of Employee Debt NRFI',
87 , 'Payment of Employee Debt PKG',
88 , 'Bursaries and Scholarships RFI',
89 , 'Bursaries and Scholarships NRFI',
90 , 'Bursaries and Scholarships PKG',
91 , 'Medical Aid Paid on Behalf of Employee RFI',
92 , 'Medical Aid Paid on Behalf of Employee NRFI',
93 , 'Medical Aid Paid on Behalf of Employee PKG',
94 , 'Retirement or Retrenchment Gratuities',
95 , 'Resignation Pension and RAF Lump Sums',
96 , 'Retirement Pension and RAF Lump Sums',
97 , 'Resignation Provident Lump Sums',
98 , 'Retirement Provident Lump Sums',
99 , 'Special Remuneration',
100, 'Other Lump Sums',
101, 'Current Pension Fund',
102, 'Arrear Pension Fund',
103, 'Current Provident Fund',
104, 'Arrear Provident Fund',
105, 'Medical Aid Contribution',
106, 'Current Retirement Annuity',
107, 'Arrear Retirement Annuity',
108, 'Tax on Lump Sums',
109, 'Tax',
110, 'UIF Employee Contribution',
111, 'Voluntary Tax',
112, 'Bonus Provision',
113, 'SITE',
114, 'PAYE',
115, 'Annual Pension Fund',
116, 'Annual Commission RFI',
117, 'Annual Commission NRFI',
118, 'Annual Commission PKG',
119, 'Annual Provident Fund',
120, 'Restraint of Trade RFI',
121, 'Restraint of Trade NRFI',
122, 'Restraint of Trade PKG',
123, 'Annual Restraint of Trade RFI'
)
into balance_name
from dual;
select decode
(
name_count,
124, 'Annual Restraint of Trade NRFI',
125, 'Annual Restraint of Trade PKG',
126, 'Annual Asset Purchased at Reduced Value RFI',
127, 'Annual Asset Purchased at Reduced Value NRFI',
128, 'Annual Asset Purchased at Reduced Value PKG',
129, 'Annual Retirement Annuity',
130, 'Annual Arrear Pension Fund',
131, 'Annual Arrear Retirement Annuity',
132, 'Other Retirement Lump Sums',
133, 'Directors Deemed Remuneration',
134, 'Annual Bursaries and Scholarships RFI',
135, 'Annual Bursaries and Scholarships NRFI',
136, 'Annual Bursaries and Scholarships PKG',
137, 'Labour Broker Payments RFI',
138, 'Labour Broker Payments NRFI',
139, 'Labour Broker Payments PKG',
140, 'Annual Labour Broker Payments RFI',
141, 'Annual Labour Broker Payments NRFI',
142, 'Annual Labour Broker Payments PKG',
143, 'Independent Contractor Payments RFI',
144, 'Independent Contractor Payments NRFI',
145, 'Independent Contractor Payments PKG',
146, 'Annual Independent Contractor Payments RFI',
147, 'Annual Independent Contractor Payments NRFI',
148, 'Annual Independent Contractor Payments PKG',
149, 'Annual Payment of Employee Debt RFI',
150, 'Annual Payment of Employee Debt NRFI',
151, 'Annual Payment of Employee Debt PKG',
152, 'Annual Taxable Package Components RFI',
153, 'Taxable Package Components RFI',
154, 'Annual Taxable Package Components NRFI',
155, 'Taxable Package Components NRFI',
156, 'Taxable Subsistence Allowance Foreign Travel RFI',
157, 'Taxable Subsistence Allowance Foreign Travel NRFI',
158, 'Taxable Subsistence Allowance Foreign Travel PKG',
159, 'Non Taxable Subsistence Allowance Foreign Travel',
160, 'Executive Equity Shares RFI',
161, 'Executive Equity Shares NRFI',
162, 'EE Income Protection Policy Contributions',
163, 'Annual EE Income Protection Policy Contributions',
164, 'EE Broadbased Share Plan NRFI',
165, 'EE Broadbased Share Plan RFI',
166, 'EE Broadbased Share Plan PKG',
167, 'Other Lump Sum Taxed as Annual Payment NRFI',
168, 'Other Lump Sum Taxed as Annual Payment RFI',
169, 'Other Lump Sum Taxed as Annual Payment PKG',
-- Begin: New Balances for TYS 06-07
170, 'Med Costs Pd by ER IRO EE_Family RFI',
171, 'Med Costs Pd by ER IRO EE_Family NRFI',
172, 'Med Costs Pd by ER IRO EE_Family PKG',
173, 'Annual Med Costs Pd by ER IRO EE_Family RFI',
174, 'Annual Med Costs Pd by ER IRO EE_Family NRFI',
175, 'Annual Med Costs Pd by ER IRO EE_Family PKG',
176, 'Annual Med Costs Pd by ER IRO Other RFI',
177, 'Annual Med Costs Pd by ER IRO Other NRFI',
178, 'Annual Med Costs Pd by ER IRO Other PKG',
179, 'Med Costs Pd by ER IRO Other RFI',
180, 'Med Costs Pd by ER IRO Other NRFI',
181, 'Med Costs Pd by ER IRO Other PKG',
182, 'Medical Contributions Abatement',
183, 'Annual Medical Contributions Abatement',
184, 'Medical Fund Capping Amount',
185, 'Med Costs Dmd Pd by EE EE_Family RFI',
186, 'Med Costs Dmd Pd by EE EE_Family NRFI',
187, 'Med Costs Dmd Pd by EE EE_Family PKG',
188, 'Annual Med Costs Dmd Pd by EE EE_Family RFI',
189, 'Annual Med Costs Dmd Pd by EE EE_Family NRFI',
190, 'Annual Med Costs Dmd Pd by EE EE_Family PKG',
191, 'Med Costs Dmd Pd by EE Other RFI',
192, 'Med Costs Dmd Pd by EE Other NRFI',
193, 'Med Costs Dmd Pd by EE Other PKG',
194, 'Annual Med Costs Dmd Pd by EE Other RFI',
195, 'Annual Med Costs Dmd Pd by EE Other NRFI',
196, 'Annual Med Costs Dmd Pd by EE Other PKG',
197, 'Non Taxable Med Costs Pd by ER',
-- End: New Balances for TYS 06-07
-- Start for 2008 SARS codes
198, 'Employers Retirement Annuity Fund Contributions',
199, 'Employers Premium paid on Loss of Income Policies',
200, 'Medical Contr Pd by ER for Retired EE',
201, 'Surplus Apportionment',
202, 'Unclaimed Benefits',
203, 'Retire Pen RAF Prov Fund Ben on Ret or Death RFI',
204, 'Retire Pen RAF Prov Fund Ben on Ret or Death NRFI',
205, 'Tax on Retirement Fund Lump Sums'
)
into balance_name
from dual;
'Select substr(popmf.org_payment_method_name, 1, 30) as COL01 -- Payment Method
,ppt.payment_type_name as COL02 -- Payment Type
,cdv.bank_name as COL03 -- Bank
,pea.segment1 as COL04 -- Branch Code
,pea.segment3 as COL05 -- Account No
,to_char(ppp.value, fnd_currency.get_format_mask(:g_currency_code,40)) as COL16 -- Payment Amount -- Bug 4392560
,fnd_date.date_to_displaydate( '
|| 'to_date(''' || l_date_earned ||''','|| '''YYYY/MM/DD''' ||')'
||') as COL06 -- Payment_date
From pay_pre_payments ppp
,pay_personal_payment_methods_f pppmf
,pay_org_payment_methods_f popmf
,pay_external_accounts pea
,pay_za_branch_cdv_details cdv
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_payment_types_tl ppt
,pay_action_interlocks pai
Where (pai.locked_action_id = :assignment_action_id or pai.locking_action_id = :assignment_action_id) and
paa.assignment_action_id = pai.locking_action_id and
paa.payroll_action_id = ppa.payroll_action_id and
ppa.action_type in (''P'' , ''U'') and
ppp.assignment_action_id = paa.assignment_action_id and
pppmf.personal_payment_method_id (+) = ppp.personal_payment_method_id and
ppa.effective_date between nvl(pppmf.effective_start_date, ppa.effective_date) and
nvl(pppmf.effective_end_date, ppa.effective_date) and
ppa.effective_date between popmf.effective_start_date and
popmf.effective_end_date and
popmf.org_payment_method_id = ppp.org_payment_method_id and
pea.external_account_id (+) = pppmf.external_account_id and
cdv.branch_code (+) = pea.segment1 and
popmf.payment_type_id = ppt.payment_type_id(+) and
ppt.language(+) = userenv(''LANG'')';