PAY End of Year Reconciliation Detail

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
--;