[Home] [Help]
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.
TrvAll
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.
PRIVATE FUNCTIONS
GetTableValue
Returns the value from a user table date effectively
PRIVATE PROCEDURES
NOTES
.
MODIFICATION HISTORY
Person Date Version Comments
--------- ---------------- ------- -----------------------------------
A. Mahanty 14/04/2005 115.15 Bug 3491357 BRA Enhancement
Balance value retrieval modified.
J.N. Louw 12/10/2001 115.10 Fixed PossiblePeriodsFactor
calculation, changed Annualise and
DeAnnualise accordingly
J.N. Louw 24/09/2001 115.9 Invalid trc_NegPtd reference
J.N. Louw 24/09/2001 115.8 Qa raised abatement error fixed
Daysworked, NegPtd SitCalc Fix
J.N. Louw 24/09/2001 115.7 Added to trace output variables
J.N. Louw 04/09/2001 115.6 Abatements set_location sequence
J.N. Louw 22/08/2001 115.5 Bug: 1902369 Negative Ptd
Bug: 1887835
New Annual Classifications
J.N. Louw 13/06/2001 115.4 Annual Commission Added to
PreErnPeriod
J.N. Louw 13/06/2001 115.3 Bug: 1820687
Restraint of Trade
J.N. Louw 31/05/2001 115.2 Bug: 1706149
DaysWorked retuned null for
Amount Override
J.N. Louw 30/03/2001 115.1 Bug: 1705874
Added Annual Pension Fund to
Retirement Annuity Calculation
in Abatements Procedure
Bug: 1706162
Split procedure updated to
calculate when terminated
pre period
L.Kloppers 31/01/2001 115.0 Changed ptp.attribute1 to
ptp.prd_information1
J.N. Louw 13/12/2000 110.2 Updated SitPaySplit to fire for
Latepayperiod
J.N. Louw 27/09/2000 110.1 Added SetRebates procedure
Modified Abatements procedure
J.N. Louw 27/09/2000 110.0 New version ZA Tax Utility
package
For detail history see
py_za_tx_utl_01032000
*/
-------------------------------------------------------------------------------
-- PACKAGE BODY --
-------------------------------------------------------------------------------
-- StartHrTrace
-- Wrapper for hr_utility.trace_on
PROCEDURE StartHrTrace AS
BEGIN
IF g_HrTraceEnabled THEN
hr_utility.trace_on(null,g_HrTracePipeName);
SELECT TO_NUMBER(global_value)
INTO l_GlbVal
FROM ff_globals_f
WHERE p_EffDte between effective_start_date and effective_end_date
AND global_name = p_GlbNme;
SELECT 1
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE paa.assignment_id = p_AsgId
AND paa.action_sequence < p_AsgActSeq
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = p_TimPrdId
AND ppa.action_status = 'C'
AND ppa.action_type IN ('O','G');
SELECT paa.action_sequence
, ppa.time_period_id
INTO l_AsgActSeq
, l_TimPrdId
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE paa.assignment_action_id = py_za_tx_01082000.con_ASG_ACT_ID
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT ptp.prd_information1
INTO py_za_tx_01082000.trc_AsgTxYear
FROM per_time_periods ptp
WHERE ptp.payroll_id = py_za_tx_01082000.con_PRL_ID
AND py_za_tx_01082000.dbi_ZA_ACT_END_DTE BETWEEN ptp.start_date AND ptp.end_date;
SELECT MAX(ptp.end_date) "EndDate"
INTO l_EndDate
FROM per_time_periods ptp
WHERE ptp.payroll_id = py_za_tx_01082000.con_PRL_ID
AND ptp.prd_information1 = py_za_tx_01082000.trc_AsgTxYear
GROUP BY ptp.prd_information1;
SELECT MAX(ptp.end_date) "EndDate"
INTO l_EndDate
FROM per_time_periods ptp
WHERE ptp.payroll_id = py_za_tx_01082000.con_PRL_ID
AND ptp.prd_information1 = py_za_tx_01082000.trc_AsgTxYear
GROUP BY ptp.prd_information1;
SELECT pucif.value
INTO l_TableValue
FROM pay_user_column_instances_f pucif
, pay_user_columns puc
, pay_user_rows_f pur
, pay_user_tables put
WHERE upper(put.user_table_name) = upper(p_TableName)
AND put.legislation_code = 'ZA'
AND puc.user_table_id = put.user_table_id
AND puc.legislation_code = 'ZA'
AND upper(puc.user_column_name) = upper(p_ColumnName)
AND pucif.user_column_id = puc.user_column_id
AND pur.user_table_id = put.user_table_id
AND p_EffectiveDate BETWEEN pur.effective_start_date AND pur.effective_end_date
AND pur.legislation_code = 'ZA'
AND p_RowValue BETWEEN pur.row_low_range_or_name AND pur.row_high_range
AND put.user_key_units = 'N'
AND pucif.user_row_id = pur.user_row_id
AND p_EffectiveDate BETWEEN pucif.effective_start_date AND pucif.effective_end_date
AND pucif.legislation_code = 'ZA';
/* 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 = py_za_tx_01082000.con_PRL_ACT_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 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 MIN(ptp.start_date) "StartDate"
, MAX(ptp.end_date) "EndDate"
INTO l_StrtDate
, l_EndDate
FROM per_time_periods ptp
WHERE ptp.payroll_id = py_za_tx_01082000.con_PRL_ID
AND ptp.prd_information1 = py_za_tx_01082000.trc_AsgTxYear
GROUP BY ptp.prd_information1;
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';
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';
SELECT MIN(ptp.start_date)
INTO l_StrtDte
FROM per_time_periods ptp
WHERE ptp.prd_information1 = py_za_tx_01082000.trc_AsgTxYear
AND ptp.payroll_id = py_za_tx_01082000.con_PRL_ID;
/*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
,AnnArrPenFnd
,AnnArrPenFndAbm
,RetAnu
,NrfiCon
,RetAnuMax
,RetAnuAbm
,AnnRetAnu
,AnnNrfiCon
,AnnRetAnuMax
,AnnRetAnuAbm
,ArrRetAnu
,ArrRetAnuAbm
,AnnArrRetAnu
,AnnArrRetAnuAbm
,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(
py_za_tx_01082000.con_ASG_ACT_ID
,py_za_tx_01082000.con_ASG_ID
,py_za_tx_01082000.con_PRL_ACT_ID
,py_za_tx_01082000.con_PRL_ID
,py_za_tx_01082000.dbi_TX_STA
,py_za_tx_01082000.dbi_PER_AGE
,py_za_tx_01082000.trc_CalTyp
,py_za_tx_01082000.dbi_TX_DIR_VAL
,py_za_tx_01082000.trc_It3Ind
,py_za_tx_01082000.trc_TxPercVal
,py_za_tx_01082000.dbi_ZA_ACT_STRT_DTE
,py_za_tx_01082000.dbi_ZA_ACT_END_DTE
,py_za_tx_01082000.dbi_ZA_CUR_PRD_STRT_DTE
,py_za_tx_01082000.dbi_ZA_CUR_PRD_END_DTE
,py_za_tx_01082000.dbi_ZA_TX_YR_STRT
,py_za_tx_01082000.dbi_ZA_TX_YR_END
,py_za_tx_01082000.dbi_SES_DTE
,py_za_tx_01082000.trc_PrdFactor
,py_za_tx_01082000.trc_PosFactor
,py_za_tx_01082000.trc_SitFactor
,py_za_tx_01082000.dbi_ZA_PAY_PRDS_LFT
,py_za_tx_01082000.dbi_ZA_PAY_PRDS_PER_YR
,py_za_tx_01082000.dbi_ZA_DYS_IN_YR
,py_za_tx_01082000.dbi_SEA_WRK_DYS_WRK
,py_za_tx_01082000.dbi_ARR_PF_FRQ
,py_za_tx_01082000.dbi_ARR_RA_FRQ
,py_za_tx_01082000.dbi_BP_TX_RCV
,py_za_tx_01082000.dbi_RA_FRQ
,py_za_tx_01082000.trc_TxbIncPtd
,py_za_tx_01082000.trc_BseErn
,py_za_tx_01082000.trc_TxbBseInc
,py_za_tx_01082000.trc_TotLibBse
,py_za_tx_01082000.trc_TxbIncYtd
,py_za_tx_01082000.trc_PerTxbInc
,py_za_tx_01082000.trc_PerPenFnd
,py_za_tx_01082000.trc_PerRfiCon
,py_za_tx_01082000.trc_PerRfiTxb
,py_za_tx_01082000.trc_PerPenFndMax
,py_za_tx_01082000.trc_PerPenFndAbm
,py_za_tx_01082000.trc_AnnTxbInc
,py_za_tx_01082000.trc_AnnPenFnd
,py_za_tx_01082000.trc_AnnRfiCon
,py_za_tx_01082000.trc_AnnRfiTxb
,py_za_tx_01082000.trc_AnnPenFndMax
,py_za_tx_01082000.trc_AnnPenFndAbm
,py_za_tx_01082000.trc_PerArrPenFnd
,py_za_tx_01082000.trc_PerArrPenFndAbm
,py_za_tx_01082000.trc_AnnArrPenFnd
,py_za_tx_01082000.trc_AnnArrPenFndAbm
,py_za_tx_01082000.trc_PerRetAnu
,py_za_tx_01082000.trc_PerNrfiCon
,py_za_tx_01082000.trc_PerRetAnuMax
,py_za_tx_01082000.trc_PerRetAnuAbm
,py_za_tx_01082000.trc_AnnRetAnu
,py_za_tx_01082000.trc_AnnNrfiCon
,py_za_tx_01082000.trc_AnnRetAnuMax
,py_za_tx_01082000.trc_AnnRetAnuAbm
,py_za_tx_01082000.trc_PerArrRetAnu
,py_za_tx_01082000.trc_PerArrRetAnuAbm
,py_za_tx_01082000.trc_AnnArrRetAnu
,py_za_tx_01082000.trc_AnnArrRetAnuAbm
,py_za_tx_01082000.trc_Rebate
,py_za_tx_01082000.trc_Threshold
,py_za_tx_01082000.trc_MedAidAbm
,py_za_tx_01082000.trc_PerTotAbm
,py_za_tx_01082000.trc_AnnTotAbm
,py_za_tx_01082000.trc_NorIncYtd
,py_za_tx_01082000.trc_NorIncPtd
,py_za_tx_01082000.trc_NorErn
,py_za_tx_01082000.trc_TxbNorInc
,py_za_tx_01082000.trc_LibFyNI
,py_za_tx_01082000.bal_TX_ON_NI_YTD
,py_za_tx_01082000.bal_TX_ON_NI_PTD
,py_za_tx_01082000.trc_LibFpNI
,py_za_tx_01082000.trc_FrnBenYtd
,py_za_tx_01082000.trc_FrnBenPtd
,py_za_tx_01082000.trc_FrnBenErn
,py_za_tx_01082000.trc_TxbFrnInc
,py_za_tx_01082000.trc_LibFyFB
,py_za_tx_01082000.bal_TX_ON_FB_YTD
,py_za_tx_01082000.bal_TX_ON_FB_PTD
,py_za_tx_01082000.trc_LibFpFB
,py_za_tx_01082000.trc_TrvAllYtd
,py_za_tx_01082000.trc_TrvAllPtd
,py_za_tx_01082000.trc_TrvAllErn
,py_za_tx_01082000.trc_TxbTrvInc
,py_za_tx_01082000.trc_LibFyTA
,py_za_tx_01082000.bal_TX_ON_TA_YTD
,py_za_tx_01082000.bal_TX_ON_TA_PTD
,py_za_tx_01082000.trc_LibFpTA
,py_za_tx_01082000.trc_BonProYtd
,py_za_tx_01082000.trc_BonProPtd
,py_za_tx_01082000.trc_BonProErn
,py_za_tx_01082000.trc_TxbBonProInc
,py_za_tx_01082000.trc_LibFyBP
,py_za_tx_01082000.bal_TX_ON_BP_YTD
,py_za_tx_01082000.bal_TX_ON_BP_PTD
,py_za_tx_01082000.trc_LibFpBP
,py_za_tx_01082000.trc_AnnBonYtd
,py_za_tx_01082000.trc_AnnBonErn
,py_za_tx_01082000.trc_TxbAnnBonInc
,py_za_tx_01082000.trc_LibFyAB
,py_za_tx_01082000.bal_TX_ON_AB_YTD
,py_za_tx_01082000.bal_TX_ON_AB_PTD
,py_za_tx_01082000.trc_LibFpAB
,py_za_tx_01082000.trc_AnnPymYtd
,py_za_tx_01082000.trc_AnnPymPtd
,py_za_tx_01082000.trc_AnnPymErn
,py_za_tx_01082000.trc_TxbAnnPymInc
,py_za_tx_01082000.trc_LibFyAP
,py_za_tx_01082000.bal_TX_ON_AP_YTD
,py_za_tx_01082000.bal_TX_ON_AP_PTD
,py_za_tx_01082000.trc_LibFpAP
,py_za_tx_01082000.trc_PblOffYtd
,py_za_tx_01082000.trc_PblOffPtd
,py_za_tx_01082000.trc_PblOffErn
,py_za_tx_01082000.trc_LibFyPO
,py_za_tx_01082000.bal_TX_ON_PO_YTD
,py_za_tx_01082000.bal_TX_ON_PO_PTD
,py_za_tx_01082000.trc_LibFpPO
,''
,py_za_tx_01082000.trc_LibWrn
,py_za_tx_01082000.trc_PayValue
,py_za_tx_01082000.trc_PayeVal
,py_za_tx_01082000.trc_SiteVal);