October 25th, 2012, 10:36 AM
Join Date: Oct 2012
Time spent in forums: 14 m 29 sec
Reputation Power: 0
Query for transformation rule.
I need help with tranformation rule below. Can somebody help me building this query?
** Use the following logic to derive claim type cd
when fdr_valdtn_t.rev_cd <> 0 then claimtypecd = 'H'
when fdr_valdtn_t.type_of_service = '004' then claim type cd = 'R'
else claim tpe cd = 'P'
if claim type cd = R, then temp-coverage_cd = R else temp-coverage-cd = M
Use rec_src_cd = 'COSMOS' and src_prdct_id = HMO_ID||'|'||LGL_ENTITY_CD||'|'||FNCL_PRDCT_CD AND src_covrg_type_cd = temp-coverage_cd and dw_hist_end_dtm = 12/31/2999 11:59:59 PM for look-up to PRDCT_CD_T.prdct_id.
If look-up is null,
create an RI row in PRDCT_CD_T
curr_indc = 'Y' ,ri_cd = Y-FDR
prdct_id = hMO_ID|| '|' || LGL_ENTITY_CD|| '|' ||FNCL_PRDCT_CD
src_covrg_type_cd = temp-coverage-cd
Set all other fields as they are set for RI rows today
PRDCT_CD_KEY = sequence number prdct_hist_seq_nbr = sequence number
MED_PRDCT_KEY = -99 med_prdct_hist_seq_nbr = -99 dw_hist_start_dtm = 1/1/1900
dw_hist_end_dtm = 12/31/2999 11:59:59 PM src_hist_start_dtm = 1/1/1900
src_hist_end_dtm = 12/31/2999 11:59:59 PM insert_dtm = SESSSTARTTIME
dw_updt_dtm = SESSSTARTTIME src_updt_dtm = 1/1/1900 suplr_updt_dtm = 1/1/1900
src_prdct_cd = ~ gl_cd_key = -99 gl_cd_hist_seq_nbr = -99
REC_SRC_CD = COSMOS Set other fields to null