PAY End of Year Reconciliation Detail

Description
Categories: BI Publisher
Columns: Pai Asg Assignment Id, Pai Asg Full Name, Pai Asg Assignment Number, Pai Asg Salary, Pai Asg Grade Step, Pai Asg Standard Hours, Pai Asg Termination Date, Pai Asg Manual Ps Year, Pai Asg Organization Name, Pai Asg Legal Employer Name ...
Application: Payroll
Source: End of Year Reconciliation Detail (XML)
Short Name: PYAUREPSR_XML
DB package: PAY_PYAURECD_XMLP_PKG
SELECT PAI_ASG.ASSIGNMENT_ID PAI_ASG_ASSIGNMENT_ID,
       substr(PAI_ASG.ACTION_INFORMATION1,1,80) PAI_ASG_Full_name ,
       PAI_ASG.ACTION_INFORMATION2 PAI_ASG_Assignment_Number,
       PAI_ASG.ACTION_INFORMATION3 PAI_ASG_Salary,
       PAI_ASG.ACTION_INFORMATION4 PAI_ASG_Grade_Step,
       PAI_ASG.ACTION_INFORMATION5 PAI_ASG_Standard_Hours,
       PAI_ASG.ACTION_INFORMATION6 PAI_ASG_Termination_Date,
       PAI_ASG.ACTION_INFORMATION7 PAI_ASG_Manual_PS_Year,
       PAI_ASG.ACTION_INFORMATION11 PAI_ASG_Organization_Name,
       PAI_ASG.ACTION_INFORMATION12 PAI_ASG_Legal_Employer_Name,
       PAI_ASG.ACTION_INFORMATION13 PAI_ASG_Payroll_Name,
       decode(PAI_ASG.ACTION_INFORMATION7,null,'N','Y') MANUAL_PS_ISSUED,
    PAY_PYAURECD_XMLP_PKG.cf_return_balancesformula(:ASG_TE, :ASG_NTE, :ASG_DED, :ASG_TAX, :ASG_NET, :ASG_CHG, :ASG_GE, :ASG_PT_DED, :ASG_DPAY, :ASG_GE_YTD, :ASG_PTDED_YTD, :ASG_DPAY_YTD, :ASG_TE_YTD, :ASG_NTE_YTD, :ASG_DED_YTD, :ASG_TAX_YTD, :ASG_NET_YTD, :ASG_CHG_YTD, decode ( PAI_ASG.ACTION_INFORMATION7 , null , 'N' , 'Y' ), :CS_PS_PSG, :CS_PS_WG, :CS_PS_RA, :CS_PS_ETP, :CS_PS_RFB, :CS_PS_AETP, :CS_PS_CDEP, :CS_PS_OI, :CS_PS_UF, :CS_PS_LPAY, :ASG_DNT, :ASG_DNT_YTD) CF_RETURN_BALANCES,
    PAY_PYAURECD_XMLP_PKG.CP_TAXABLE_EARNINGS_p CP_TAXABLE_EARNINGS,
    PAY_PYAURECD_XMLP_PKG.CP_NON_TAXABLE_EARNINGS_p CP_NON_TAXABLE_EARNINGS,
    PAY_PYAURECD_XMLP_PKG.CP_DEDUCTIONS_p CP_DEDUCTIONS,
    PAY_PYAURECD_XMLP_PKG.CP_PRE_TAX_DEDUCTIONS_p CP_PRE_TAX_DEDUCTIONS,
    PAY_PYAURECD_XMLP_PKG.CP_DIRECT_PAYMENTS_p CP_DIRECT_PAYMENTS,
    PAY_PYAURECD_XMLP_PKG.CP_TAX_p CP_TAX,
    PAY_PYAURECD_XMLP_PKG.CP_GROSS_EARNINGS_p CP_GROSS_EARNINGS,
    PAY_PYAURECD_XMLP_PKG.CP_NET_PAYMENT_p CP_NET_PAYMENT,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_TAXABLE_EARNINGS_p CP_YTD_TAXABLE_EARNINGS,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_NON_TAXABLE_EARNINGS_p CP_YTD_NON_TAXABLE_EARNINGS,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_GROSS_EARNINGS_p CP_YTD_GROSS_EARNINGS,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_PRE_TAX_DEDUCTIONS_p CP_YTD_PRE_TAX_DEDUCTIONS,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_DIRECT_PAYMENTS_p CP_YTD_DIRECT_PAYMENTS,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_DEDUCTIONS_p CP_YTD_DEDUCTIONS,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_TAX_p CP_YTD_TAX,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_NET_PAYMENT_p CP_YTD_NET_PAYMENT,
    PAY_PYAURECD_XMLP_PKG.CP_EMP_CHG_p CP_EMP_CHG,
    PAY_PYAURECD_XMLP_PKG.CP_YTD_EMP_CHG_p CP_YTD_EMP_CHG,
    PAY_PYAURECD_XMLP_PKG.CP_ASG_DEATH_NON_TAX_p CP_ASG_DEATH_NON_TAX_EARN,  
    PAY_PYAURECD_XMLP_PKG.CP_ASG_YTD_DEATH_NON_TAX_p CP_ASG_YTD_DEATH_NON_TAX_EARN
FROM PAY_ACTION_INFORMATION PAI_ASG
 --WHERE PAI_ASG.ACTION_CONTEXT_ID = :P_PAYROLL_ACTION_ID
 WHERE PAI_ASG.ACTION_CONTEXT_ID = :LP_PAYROLL_ACTION_ID
 AND PAI_ASG.ACTION_INFORMATION_CATEGORY = 'AU_EMPLOYEE_RECON_DETAILS'
 AND PAI_ASG.ACTION_CONTEXT_TYPE = 'PA'
&orderby_clause
--;