eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_NZ_ASG_CAL_MTH_BAL_V

Source

View Text - Preformatted


SELECT /*+ INDEX(asg , PER_ASSIGNMENTS_F_PK) */asg.assignment_id , scl.segment1 , ses.effective_date , floor(sum(decode(pbt.balance_name, 'Gross Earnings',to_number(target.result_value)* feed.scale , 'Withholding Payments Record',to_number(target.result_value) * feed.scale , 'Retro Ordinary Taxable Earnings',to_number(target.result_value) * feed.scale , 0))) , floor(sum(decode(pbt.balance_name, 'Earnings Not Liable for ACC EP',to_number(target.result_value) , 'Retro Earnings Not Liable for ACC EP',to_number(target.result_value), 0))) , substr(pay_nz_tax.extra_emol_at_low_tax_rate(asg.assignment_id, ses.effective_date), 1, 1) , sum(decode(pbt.balance_name, 'PAYE Tax Deductions', to_number(target.result_value) , 'Withholding Tax Deductions Record',to_number(target.result_value) * feed.scale , 'Retro PAYE Tax Deductions',to_number(target.result_value) * feed.scale , 0)) , sum(decode(pbt.balance_name, 'Child Support Deductions', to_number(target.result_value) * feed.scale, 0)) , substr(pay_nz_tax.child_support_code(asg.assignment_id, ses.effective_date), 1, 1) , sum(decode(pbt.balance_name, 'Student Loan Deductions',to_number(target.result_value) * feed.scale , 'Retro Student Loan Deductions',to_number(target.result_value) * feed.scale, 0)) /* Added for bug 5846247 */ , sum(decode( pbt.balance_name, 'KiwiSaver Employee Contributions', to_number(target.result_value) * feed.scale, 0)) , sum(decode( pbt.balance_name, 'KiwiSaver Employer Contributions', to_number(target.result_value) * feed.scale, 0)) - sum(decode(pbt.balance_name, 'SSCWT Deductions', to_number(target.result_value) * feed.scale, 0)) /* Added below column for bug 9237657 */ , sum(decode( pbt.balance_name, 'Payroll Giving Tax Credits', to_number(target.result_value) * feed.scale, 0)) /* Added for bug 13627558 */ , sum(decode( pbt.balance_name, 'SLCIR Deduction', to_number(target.result_value) * feed.scale, 0)) , sum(decode( pbt.balance_name, 'SLBOR Deduction', to_number(target.result_value) * feed.scale, 0)) from hr_soft_coding_keyflex scl , per_all_assignments_f asg , pay_balance_types pbt , pay_run_result_values target , pay_run_results rr , pay_balance_feeds_f feed , pay_assignment_actions assact , pay_payroll_actions pact , fnd_sessions ses , pay_input_values_f piv where ses.session_id = userenv('SESSIONID') and FEED.input_value_id = TARGET.input_value_id and feed.balance_type_id = pbt.balance_type_id and pbt.legislation_code = 'NZ' and target.input_value_id = piv.input_value_id and piv.input_value_id = feed.input_value_id and feed.effective_start_date <= last_day(ses.effective_date) and feed.effective_end_date >= to_date('01/' || to_char(ses.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') and feed.effective_start_date = (select max(feed2.effective_start_date) from pay_balance_feeds_f feed2 where feed2.input_value_id = feed.input_value_id and feed2.balance_type_id = feed.balance_type_id and feed2.effective_start_date <= last_day(ses.effective_date) ) and TARGET.result_value <> '0' 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 pact.effective_date between to_date('01/' || to_char(ses.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') and last_day(ses.effective_date) and RR.status in ('P','PA') and asg.assignment_id = assact.assignment_id and asg.effective_start_date <= last_day(ses.effective_date) and asg.effective_end_date >= to_date('01/' || to_char(ses.effective_date, 'mm/yyyy'), 'dd/mm/yyyy') and asg.effective_start_date = (select /*+ INDEX(asg2 , PER_ASSIGNMENTS_F_PK) */ max(asg2.effective_start_date) from per_all_assignments_f asg2 where asg2.assignment_id = asg.assignment_id and asg2.effective_start_date <= last_day(ses.effective_date) ) and scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id and pbt.balance_name in ('Gross Earnings' ,'Earnings Not Liable for ACC EP' ,'PAYE Tax Deductions' ,'Child Support Deductions' ,'Student Loan Deductions' ,'Withholding Payments Record' ,'Withholding Tax Deductions Record' ,'Retro Ordinary Taxable Earnings' ,'Retro PAYE Tax Deductions' ,'Retro Student Loan Deductions' ,'Retro Earnings Not Liable for ACC EP' /*Added for bug 5846247 */ ,'KiwiSaver Employee Contributions' ,'KiwiSaver Employer Contributions' /* Added for bug 9237657 */ ,'Payroll Giving Tax Credits' /* Added for bug 13627558 */ ,'SLCIR Deduction' ,'SLBOR Deduction' ,'SSCWT Deductions') group by asg.assignment_id , scl.segment1 , ses.effective_date

View Text - HTML Formatted

SELECT /*+ INDEX(ASG
, PER_ASSIGNMENTS_F_PK) */ASG.ASSIGNMENT_ID
, SCL.SEGMENT1
, SES.EFFECTIVE_DATE
, FLOOR(SUM(DECODE(PBT.BALANCE_NAME
, 'GROSS EARNINGS'
, TO_NUMBER(TARGET.RESULT_VALUE)* FEED.SCALE
, 'WITHHOLDING PAYMENTS RECORD'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 'RETRO ORDINARY TAXABLE EARNINGS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)))
, FLOOR(SUM(DECODE(PBT.BALANCE_NAME
, 'EARNINGS NOT LIABLE FOR ACC EP'
, TO_NUMBER(TARGET.RESULT_VALUE)
, 'RETRO EARNINGS NOT LIABLE FOR ACC EP'
, TO_NUMBER(TARGET.RESULT_VALUE)
, 0)))
, SUBSTR(PAY_NZ_TAX.EXTRA_EMOL_AT_LOW_TAX_RATE(ASG.ASSIGNMENT_ID
, SES.EFFECTIVE_DATE)
, 1
, 1)
, SUM(DECODE(PBT.BALANCE_NAME
, 'PAYE TAX DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE)
, 'WITHHOLDING TAX DEDUCTIONS RECORD'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 'RETRO PAYE TAX DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUM(DECODE(PBT.BALANCE_NAME
, 'CHILD SUPPORT DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUBSTR(PAY_NZ_TAX.CHILD_SUPPORT_CODE(ASG.ASSIGNMENT_ID
, SES.EFFECTIVE_DATE)
, 1
, 1)
, SUM(DECODE(PBT.BALANCE_NAME
, 'STUDENT LOAN DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 'RETRO STUDENT LOAN DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)) /* ADDED FOR BUG 5846247 */
, SUM(DECODE( PBT.BALANCE_NAME
, 'KIWISAVER EMPLOYEE CONTRIBUTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUM(DECODE( PBT.BALANCE_NAME
, 'KIWISAVER EMPLOYER CONTRIBUTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)) - SUM(DECODE(PBT.BALANCE_NAME
, 'SSCWT DEDUCTIONS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)) /* ADDED BELOW COLUMN FOR BUG 9237657 */
, SUM(DECODE( PBT.BALANCE_NAME
, 'PAYROLL GIVING TAX CREDITS'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0)) /* ADDED FOR BUG 13627558 */
, SUM(DECODE( PBT.BALANCE_NAME
, 'SLCIR DEDUCTION'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
, SUM(DECODE( PBT.BALANCE_NAME
, 'SLBOR DEDUCTION'
, TO_NUMBER(TARGET.RESULT_VALUE) * FEED.SCALE
, 0))
FROM HR_SOFT_CODING_KEYFLEX SCL
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_BALANCE_TYPES PBT
, PAY_RUN_RESULT_VALUES TARGET
, PAY_RUN_RESULTS RR
, PAY_BALANCE_FEEDS_F FEED
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PAY_PAYROLL_ACTIONS PACT
, FND_SESSIONS SES
, PAY_INPUT_VALUES_F PIV
WHERE SES.SESSION_ID = USERENV('SESSIONID')
AND FEED.INPUT_VALUE_ID = TARGET.INPUT_VALUE_ID
AND FEED.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PBT.LEGISLATION_CODE = 'NZ'
AND TARGET.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.INPUT_VALUE_ID = FEED.INPUT_VALUE_ID
AND FEED.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE)
AND FEED.EFFECTIVE_END_DATE >= TO_DATE('01/' || TO_CHAR(SES.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND FEED.EFFECTIVE_START_DATE = (SELECT MAX(FEED2.EFFECTIVE_START_DATE)
FROM PAY_BALANCE_FEEDS_F FEED2
WHERE FEED2.INPUT_VALUE_ID = FEED.INPUT_VALUE_ID
AND FEED2.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND FEED2.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE) )
AND TARGET.RESULT_VALUE <> '0'
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 PACT.EFFECTIVE_DATE BETWEEN TO_DATE('01/' || TO_CHAR(SES.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND LAST_DAY(SES.EFFECTIVE_DATE)
AND RR.STATUS IN ('P'
, 'PA')
AND ASG.ASSIGNMENT_ID = ASSACT.ASSIGNMENT_ID
AND ASG.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE)
AND ASG.EFFECTIVE_END_DATE >= TO_DATE('01/' || TO_CHAR(SES.EFFECTIVE_DATE
, 'MM/YYYY')
, 'DD/MM/YYYY')
AND ASG.EFFECTIVE_START_DATE = (SELECT /*+ INDEX(ASG2
, PER_ASSIGNMENTS_F_PK) */ MAX(ASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_START_DATE <= LAST_DAY(SES.EFFECTIVE_DATE) )
AND SCL.SOFT_CODING_KEYFLEX_ID = ASG.SOFT_CODING_KEYFLEX_ID
AND PBT.BALANCE_NAME IN ('GROSS EARNINGS'
, 'EARNINGS NOT LIABLE FOR ACC EP'
, 'PAYE TAX DEDUCTIONS'
, 'CHILD SUPPORT DEDUCTIONS'
, 'STUDENT LOAN DEDUCTIONS'
, 'WITHHOLDING PAYMENTS RECORD'
, 'WITHHOLDING TAX DEDUCTIONS RECORD'
, 'RETRO ORDINARY TAXABLE EARNINGS'
, 'RETRO PAYE TAX DEDUCTIONS'
, 'RETRO STUDENT LOAN DEDUCTIONS'
, 'RETRO EARNINGS NOT LIABLE FOR ACC EP' /*ADDED FOR BUG 5846247 */
, 'KIWISAVER EMPLOYEE CONTRIBUTIONS'
, 'KIWISAVER EMPLOYER CONTRIBUTIONS' /* ADDED FOR BUG 9237657 */
, 'PAYROLL GIVING TAX CREDITS' /* ADDED FOR BUG 13627558 */
, 'SLCIR DEDUCTION'
, 'SLBOR DEDUCTION'
, 'SSCWT DEDUCTIONS') GROUP BY ASG.ASSIGNMENT_ID
, SCL.SEGMENT1
, SES.EFFECTIVE_DATE