select purt1.attribute5 ASSG1,
replace(purt1.attribute9,'-') SSN1,
--select purt1.attribute5 ASSG,
-- replace(purt1.attribute9,'-') SSN,
sum(purt1.value2) RA_GRA_PLAN_BY_INST1,
sum(purt1.value3) RA_GRA_PLAN_REDUCT1,
sum(purt1.value4) RA_PLAN_DEDUCT1,
sum(purt1.value5) RA_ADDL_REDUCT1,
sum(purt1.value6) RA_ADDL_DEDUCT1,
sum(purt1.value7) SRA_GSRA_REDUCT1,
PAY_PAYUSTIM_XMLP_PKG.cf_ra_gra_plan_by_instformula(sum ( purt1.value2 )) CF_RA_GRA_PLAN_BY_INST,
PAY_PAYUSTIM_XMLP_PKG.cf_ra_gra_plan_reductformula(sum ( purt1.value3 )) CF_RA_GRA_PLAN_REDUCT,
PAY_PAYUSTIM_XMLP_PKG.cf_ra_plan_deductformula(sum ( purt1.value4 )) CF_RA_PLAN_DEDUCT,
PAY_PAYUSTIM_XMLP_PKG.cf_ra_addl_reductformula(sum ( purt1.value5 )) CF_RA_ADDL_REDUCT,
PAY_PAYUSTIM_XMLP_PKG.cf_ra_addl_deductformula(sum ( purt1.value6 )) CF_RA_ADDL_DEDUCT,
PAY_PAYUSTIM_XMLP_PKG.cf_sra_gsra_reductformula(sum ( purt1.value7 )) CF_SRA_GSRA_REDUCT
from pay_us_rpt_totals purt1
where purt1.tax_unit_id = :pactid
and purt1.attribute1 <> 'TIAA-CREF'
and purt1.attribute12 = :payment_mode
and purt1.attribute14 is null
and purt1.attribute5=:Assg
group by purt1.attribute5, replace(purt1.attribute9,'-') |
