The following lines contain the word 'select', 'insert', 'update' or 'delete':
Calculates the arrear excess figure to 'effectively' update the
Asg_Itd dimension of the arrear excess pension and retirement
annuity balances. Will only fire on siteperiod.
TrvAllYtd
Calculates the taxable travel allowance over a period of
time based on the effective global values at the time.
For a complete description see the tax module design document.
TrvAllCal
Calculates the taxable travel allowance over a period of
time based on the effective global values at the time.
For a complete description see the tax module design document.
NpVal
Validates the calculated category liabilities.
For a complete description see the tax module design document.
SitPaySplit
Calculates the site paye split of tax liabilities.
For a complete description see the tax module design document.
Trace
Traces the tax calculation.
For a complete description see the tax module design document.
ClearGlobals
Clears any set package globals.
For a complete description see the tax module design document.
SeaCalc
A main tax calculation.
For a complete description see the tax module design document.
SitCalc
A main tax calculation.
For a complete description see the tax module design document.
DirCalc
A main tax calculation.
For a complete description see the tax module design document.
BasCalc
A main tax calculation.
For a complete description see the tax module design document.
CalCalc
A main tax calculation.
For a complete description see the tax module design document.
YtdCalc
A main tax calculation.
For a complete description see the tax module design document.
NorCalc
A main tax calculation.
For a complete description see the tax module design document.
NOTES
.
MODIFICATION HISTORY
Person Date(DD-MM-YYYY) Version Comments
--------- ---------------- ------- -----------------------------------
J.N. Louw 06-03-2000 110.5 SitePayeSplit for 'G'
Added balance:
Total Seasonal Workers Days
Worked:
bal_TOT_SEA_WRK_DYS_WRK
c/p_PAYE_PTD,p_SITE_PTD,
bal_PAYE_PTD,bal_SITE_PTD
/p_PAYE_YTD,p_SITE_YTD,
bal_PAYE_YTD,bal_SITE_YTD
Updated Exception Handling:
to level of 01032000 pkg
Error when no number of days
worked for Tax Status 'G'
New Tax Directive default:
0000 instead of 0
J.N. Louw 09-02-2000 110.4 Fixed 65 Rebate and Threshold
Check
Altered Threshold Validation:
NorCalc, SitCalc
Tax on ... Refund
Altered Ytd Income Validation:
DirCalc, NorCalc, SitCalc
Tax on ... Refund
Altered ZaTx_01011001:
Tax Status Validation
Altered NpVal:
Override of Liability
J.N. Louw 02-02-2000 110.3 Added PreErnPeriod Function
Addded Balance Feed Functionality
for the Total Taxable Income
balance
bal_PRCH_ANU_TXB_RFI_RUN
Added bal_PAYE_PTD,bal_SITE_PTD
Fixed BasCalc
Added LstPeriod,EmpTermInPeriod,
EmpTermPrePeriod Functions
Removed TxbIncYtd check in
SitCalc
Fixed De-annualisation of Public
Office Allowance - SitCalc,
SeaCalc
J.N. Louw 20-01-2000 110.2 Fixed bug on NpVal when Net Pay
is zero
J.N. Louw 09-12-1999 110.1 Arrear Excess Processing
J.N. Louw 13-09-1999 110.0 First Created
*/
/* PACKAGE GLOBAL AREA */
-- Contexts
con_ASG_ACT_ID NUMBER;
/* this selects the effective date for the payroll_run*/
SELECT ppa.effective_date
INTO l_effective_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = con_PRL_ACT_ID;
/* Selects to get the relevant id's */
SELECT user_table_id
INTO l_user_table_id
FROM pay_user_tables
WHERE user_table_name = 'ZA_TAX_TABLE';
select user_column_id
into l_fixed_column_id
from pay_user_columns
where user_table_id = l_user_table_id
and user_column_name = 'Fixed';
select user_column_id
into l_limit_column_id
from pay_user_columns
where user_table_id = l_user_table_id
and user_column_name = 'Limit';
select user_column_id
into l_percentage_column_id
from pay_user_columns
where user_table_id = l_user_table_id
and user_column_name = 'Percentage';
select purf.user_row_id
into l_bracket_row
from pay_user_rows_f purf
where purf.user_table_id = l_user_table_id
and (l_effective_date >= purf.effective_start_date
and l_effective_date <= purf.effective_end_date)
and (l_TxbAmt >= purf.row_low_range_or_name
and l_TxbAmt <= purf.row_high_range);
/* Selects to get the actual values */
select pucif.value
into l_fixed
from pay_user_column_instances_f pucif
where pucif.user_row_id = l_bracket_row
and (l_effective_date >= pucif.effective_start_date
and l_effective_date <= pucif.effective_end_date)
and pucif.user_column_id = l_fixed_column_id;
select pucif.value
into l_limit
from pay_user_column_instances_f pucif
where pucif.user_row_id = l_bracket_row
and (l_effective_date >= pucif.effective_start_date
and l_effective_date <= pucif.effective_end_date)
and pucif.user_column_id = l_limit_column_id;
select pucif.value
into l_percentage
from pay_user_column_instances_f pucif
where pucif.user_row_id = l_bracket_row
and (l_effective_date >= pucif.effective_start_date
and l_effective_date <= pucif.effective_end_date)
and pucif.user_column_id = l_percentage_column_id;
SELECT effective_end_date,
to_number(global_value) global_value
FROM ff_globals_f
WHERE effective_end_date < p_ty_ed
AND effective_end_date > p_ty_sd
AND effective_end_date < dbi_ZA_CUR_PRD_END_DTE
AND global_name = 'ZA_CAR_ALLOW_TAX_PERC';
SELECT balance_type_id
INTO l_NrfiBalID
FROM pay_balance_types
WHERE legislation_code = 'ZA'
AND balance_name = 'Travel Allowance NRFI';
SELECT balance_type_id
INTO l_RfiBalID
FROM pay_balance_types
WHERE legislation_code = 'ZA'
AND balance_name = 'Travel Allowance RFI';
SELECT effective_end_date,
global_value
FROM ff_globals_f
WHERE effective_end_date > p_StrtDte
AND effective_end_date < p_EndDte
AND global_name = 'ZA_CAR_ALLOW_TAX_PERC';
SELECT balance_type_id
INTO l_NrfiBalID
FROM pay_balance_types
WHERE legislation_code = 'ZA'
AND balance_name = 'Travel Allowance NRFI';
SELECT balance_type_id
INTO l_RfiBalID
FROM pay_balance_types
WHERE legislation_code = 'ZA'
AND balance_name = 'Travel Allowance RFI';
SELECT TO_NUMBER(global_value)
INTO l_GlbVal
FROM ff_globals_f
WHERE l_EndDate between effective_start_date and effective_end_date
AND global_name = 'ZA_CAR_ALLOW_TAX_PERC';
/*DELETE FROM pay_za_tax_traces pztt
WHERE pztt.prl_act_id not in (
SELECT payroll_action_id
from pay_payroll_actions)
*/
INSERT INTO pay_za_tax_traces(
ASG_ACT_ID
,ASG_ID
,PRL_ACT_ID
,PRL_ID
,TX_STA
,PER_AGE
,CalTyp
,TX_DIR_VAL
,It3Ind
,TxPercVal
,ASG_STRT_DTE
,ZA_ACT_END_DTE
,ZA_CUR_PRD_STRT_DTE
,ZA_CUR_PRD_END_DTE
,ZA_TX_YR_STRT
,ZA_TX_YR_END
,SES_DTE
,PrdFactor
,PosFactor
,SitFactor
,ZA_PAY_PRDS_LFT
,ZA_PAY_PRDS_PER_YR
,ZA_DYS_IN_YR
,SEA_WRK_DYS_WRK
,ARR_PF_FRQ
,ARR_RA_FRQ
,BP_TX_RCV
,RA_FRQ
,TxbIncPtd
,BseErn
,TxbBseInc
,TotLibBse
,TxbIncYtd
,PerTxbInc
,PerPenFnd
,PerRfiCon
,PerRfiTxb
,PerPenFndMax
,PerPenFndAbm
,AnnTxbInc
,AnnPenFnd
,AnnRfiCon
,AnnRfiTxb
,AnnPenFndMax
,AnnPenFndAbm
,ArrPenFnd
,ArrPenFndAbm
,RetAnu
,NrfiCon
,RetAnuMax
,RetAnuAbm
,ArrRetAnu
,ArrRetAnuAbm
,Rebate
,Threshold
,MedAidAbm
,PerTotAbm
,AnnTotAbm
,NorIncYtd
,NorIncPtd
,NorErn
,TxbNorInc
,LibFyNI
,TX_ON_NI_YTD
,TX_ON_NI_PTD
,LibFpNI
,FrnBenYtd
,FrnBenPtd
,FrnBenErn
,TxbFrnInc
,LibFyFB
,TX_ON_FB_YTD
,TX_ON_FB_PTD
,LibFpFB
,TrvAllYtd
,TrvAllPtd
,TrvAllErn
,TxbTrvInc
,LibFyTA
,TX_ON_TA_YTD
,TX_ON_TA_PTD
,LibFpTA
,BonProYtd
,BonProPtd
,BonProErn
,TxbBonProInc
,LibFyBP
,TX_ON_BP_YTD
,TX_ON_BP_PTD
,LibFpBP
,AnnBonYtd
,AnnBonErn
,TxbAnnBonInc
,LibFyAB
,TX_ON_AB_YTD
,TX_ON_AB_PTD
,LibFpAB
,AnnPymYtd
,AnnPymPtd
,AnnPymErn
,TxbAnnPymInc
,LibFyAP
,TX_ON_AP_YTD
,TX_ON_AP_PTD
,LibFpAP
,PblOffYtd
,PblOffPtd
,PblOffErn
,LibFyPO
,TX_ON_PO_YTD
,TX_ON_PO_PTD
,LibFpPO
,MsgTxStatus
,LibWrn
,PayValue
,PayeVal
,SiteVal
)
VALUES(
con_ASG_ACT_ID
,con_ASG_ID
,con_PRL_ACT_ID
,con_PRL_ID
,dbi_TX_STA
,dbi_PER_AGE
,trc_CalTyp
,dbi_TX_DIR_VAL
,trc_It3Ind
,trc_TxPercVal
,dbi_ASG_STRT_DTE
,dbi_ZA_ACT_END_DTE
,dbi_ZA_CUR_PRD_STRT_DTE
,dbi_ZA_CUR_PRD_END_DTE
,dbi_ZA_TX_YR_STRT
,dbi_ZA_TX_YR_END
,dbi_SES_DTE
,trc_PrdFactor
,trc_PosFactor
,trc_SitFactor
,dbi_ZA_PAY_PRDS_LFT
,dbi_ZA_PAY_PRDS_PER_YR
,dbi_ZA_DYS_IN_YR
,dbi_SEA_WRK_DYS_WRK
,dbi_ARR_PF_FRQ
,dbi_ARR_RA_FRQ
,dbi_BP_TX_RCV
,dbi_RA_FRQ
,trc_TxbIncPtd
,trc_BseErn
,trc_TxbBseInc
,trc_TotLibBse
,trc_TxbIncYtd
,trc_PerTxbInc
,trc_PerPenFnd
,trc_PerRfiCon
,trc_PerRfiTxb
,trc_PerPenFndMax
,trc_PerPenFndAbm
,trc_AnnTxbInc
,trc_AnnPenFnd
,trc_AnnRfiCon
,trc_AnnRfiTxb
,trc_AnnPenFndMax
,trc_AnnPenFndAbm
,trc_ArrPenFnd
,trc_ArrPenFndAbm
,trc_RetAnu
,trc_NrfiCon
,trc_RetAnuMax
,trc_RetAnuAbm
,trc_ArrRetAnu
,trc_ArrRetAnuAbm
,trc_Rebate
,trc_Threshold
,trc_MedAidAbm
,trc_PerTotAbm
,trc_AnnTotAbm
,trc_NorIncYtd
,trc_NorIncPtd
,trc_NorErn
,trc_TxbNorInc
,trc_LibFyNI
,bal_TX_ON_NI_YTD
,bal_TX_ON_NI_PTD
,trc_LibFpNI
,trc_FrnBenYtd
,trc_FrnBenPtd
,trc_FrnBenErn
,trc_TxbFrnInc
,trc_LibFyFB
,bal_TX_ON_FB_YTD
,bal_TX_ON_FB_PTD
,trc_LibFpFB
,trc_TrvAllYtd
,trc_TrvAllPtd
,trc_TrvAllErn
,trc_TxbTrvInc
,trc_LibFyTA
,bal_TX_ON_TA_YTD
,bal_TX_ON_TA_PTD
,trc_LibFpTA
,trc_BonProYtd
,trc_BonProPtd
,trc_BonProErn
,trc_TxbBonProInc
,trc_LibFyBP
,bal_TX_ON_BP_YTD
,bal_TX_ON_BP_PTD
,trc_LibFpBP
,trc_AnnBonYtd
,trc_AnnBonErn
,trc_TxbAnnBonInc
,trc_LibFyAB
,bal_TX_ON_AB_YTD
,bal_TX_ON_AB_PTD
,trc_LibFpAB
,trc_AnnPymYtd
,trc_AnnPymPtd
,trc_AnnPymErn
,trc_TxbAnnPymInc
,trc_LibFyAP
,bal_TX_ON_AP_YTD
,bal_TX_ON_AP_PTD
,trc_LibFpAP
,trc_PblOffYtd
,trc_PblOffPtd
,trc_PblOffErn
,trc_LibFyPO
,bal_TX_ON_PO_YTD
,bal_TX_ON_PO_PTD
,trc_LibFpPO
,trc_MsgTxStatus
,trc_LibWrn
,trc_PayValue
,trc_PayeVal
,trc_SiteVal);