with grn_val as (select substring(b.LENQ_DATA,1,2) as CMPY
,substring(b.LENQ_DATA,3,12)  as SUPP
,substring(b.LENQ_DATA,15,1)  as DOC_IND
,substring(b.LENQ_DATA,16,20) as DOC_REF
,substring(b.LENQ_DATA,36,5)  as LINE_NUM
,substring(b.LENQ_DATA,48,8)  as RCPT_REF
,substring(b.LENQ_DATA,56,2)  as DOC_CODE
,substring(b.LENQ_DATA,58,5)  as SEQ_NUM
,a.ix_key,b.sysref,sum(b.fncl_val) as fncl_val
,b.REF_NUM,b.recon
from tedtrnix a, tedtran b, tplgrn c
where a.ix_cmpy = 'PG'
and a.IX_TYPE = '7'
and a.IX_KEY = c.GL_TRAN_REF
and c.cmpy = a.IX_CMPY
and c.grn_stat_ind in ('3','4')
and b.CMPY         = a.IX_CMPY
and b.PATH         = a.PATH
and b.SYSREF       = a.SYSREF
and b.BAL          = a.BAL
and b.TRAN_DATE    = a.TRAN_DATE
and b.BTCH_NUM     = a.BTCH_NUM
and b.BTCH_SEQ_NUM = a.BTCH_SEQ_NUM
and c.grn in ('00088234','00088235','00019124','00072020','00122066','00122068','00096673')
group by substring(b.LENQ_DATA,1,2) ,substring(b.LENQ_DATA,3,12)
,substring(b.LENQ_DATA,15,1),substring(b.LENQ_DATA,16,20)
,substring(b.LENQ_DATA,36,5),substring(b.LENQ_DATA,48,8)
,substring(b.LENQ_DATA,56,2)
,substring(b.LENQ_DATA,58,5)
,a.ix_key,b.sysref
,b.REF_NUM,b.recon
having SUM(b.fncl_val) <> 0)
insert into TEDHIFU (JOBTAG,CMPY,BTCH_NUM,REC_TYPE,BTCH_SEQ_NUM,LENQ_KEY,FNCL_VAL_S,FNCL_VAL,
REF_NUM_S,REF_NUM,RECON_S,RECON,ACCT_S,ACCT,ANAL_S,ANAL,TRAN_DATE,DETL_DESCR_S,DETL_DESCR)
select '        ','PG',1234566,'2',ROW_NUMBER() over(order by a.cmpy desc),
a.CMPY + a.SUPP + a.DOC_IND + a.DOC_REF + a.LINE_NUM + '0735406' + a.RCPT_REF + a.DOC_CODE + a.SEQ_NUM,
'Y',a.fncl_val * -1,'Y',a.ref_num,'Y',a.recon,'Y',c.acct,'Y',b.gl_anal,
'31-AUG-2013','Y','Correction Posting GRN-' + a.RCPT_REF
from grn_val a, tplanal b, tebacct c
where b.cmpy = a.cmpy
and b.grn = a.rcpt_ref
and b.seq_num = a.seq_num
and c.cmpy = a.cmpy
and c.path = 0
and c.sysref = a.sysref;