on 06-24-2015 7:15 PM
I have query returns records in TOAD, but when set up on Bi4 business object report, I kept getting no data retrieve. My reports point to same database as i ran on TOAD, I am certain that there should be record returns. Do you have idea why? BTW, it is "custom query" for BO report.
Surely something wrong with BO filters or custom script. Try remove all the filter conditions and see if it runs or go one object by one and refresh inside to see what field is causing the issue. Give me more details so I can look at it.
Kris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You are correct. I started cut out the complicate filter to make simple query and it works. It is my first report to create under Bi4 environment so I thought I misstepped while creating report.
My custom is complicate one (below) you have any clue how to make it works on BO
select ca.FACT_LOAN_MO_ROW_ID,
ca.BORR_SSN,ca.BORR_ACCT_SFX_NBR,ca.LOAN_SEQ_NBR,
(pca.LOAN_OSTD_PRIN_AMT + FA_PBO_AMT - ca.LOAN_OSTD_PRIN_AMT) as PBO_VAR_AMT,
(pca.LOAN_ACCR_INT_AMT + pca.IBR_ACCR_INT_AMT + FA_IRB_AMT) - (ca.LOAN_ACCR_INT_AMT + ca.IBR_ACCR_INT_AMT) as IRB_VAR_AMT,
FA_PBO_AMT,
FA_IRB_AMT,
pca.LOAN_OSTD_PRIN_AMT,
pca.LOAN_ACCR_INT_AMT,
pca.IBR_ACCR_INT_AMT,
pca.LOAN_ACCR_LFEE_AMT,
pca.RTI_FEE_OSTD_AMT,
pca.LOAN_MO_END_ORIG_FEE_BAL_AMT,
pca.LOAN_MO_END_RBTE_BAL_AMT,
ca.LOAN_OSTD_PRIN_AMT,
ca.LOAN_ACCR_INT_AMT,
ca.IBR_ACCR_INT_AMT,
ca.LOAN_ACCR_LFEE_AMT,
ca.RTI_FEE_OSTD_AMT,
ca.LOAN_MO_END_ORIG_FEE_BAL_AMT,
ca.LOAN_MO_END_RBTE_BAL_AMT
from
(
select fa.BORR_SSN, fa.BORR_ACCT_SFX_NBR,fa.LOAN_SEQ_NBR,
sum(PBO_amt) as FA_PBO_AMT ,
sum(fa.IRB_amt) as FA_IRB_AMT
from
(
select BORR_SSN,BORR_ACCT_SFX_NBR,LOAN_SEQ_NBR,
fsa_amt_col_nm,
bi.mtj_tran_col_nm||fsa_amt_col_nm,
Case when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'JRNL1_AMTPBO_AMT' Then (l.jrnl_1_amt)
when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'OFEE_AMTPBO_AMT' Then (l.LOAN_OFEE_AMT)
when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'INS_FEE_AMTPBO_AMT' Then (l.LOAN_IFEE_AMT)
when bi.mtj_tran_col_nm||fsa_amt_col_nm ='JRNL1_LOAN_IFEE_OFEE_AMTPBO_AMT' Then (l.JRNL_1_AMT + l.LOAN_IFEE_AMT + l.LOAN_OFEE_AMT)
when bi.mtj_tran_col_nm||fsa_amt_col_nm ='LOAN_INS_FEE_OFEE_AMTPBO_AMT' Then (l.LOAN_IFEE_AMT + LOAN_OFEE_AMT)
else 0.00
end as PBO_amt ,
Case when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'JRNL2_AMTIRB_AMT' Then (l.jrnl_2_amt)
when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'BR_AC_IN_AMTIRB_AMT' Then (l.BORR_ACCR_INT_AMT)
when bi.mtj_tran_col_nm||fsa_amt_col_nm = 'IBR_ED_BOR_AC_INT_AMTIRB_AMT' Then (l.BORR_ACCR_INT_AMT + l.LOAN_IBR_ED_INT_AMT)
else 0.00
end as IRB_amt
from fsa_ed.fsa_Btch_item bi,
FSA_ED.map_btch_item_clas m, FSA_ED.fact_loan_tran l
where bi.btch_item_tran_id = m.btch_item_tran_id
and m.fact_loan_tran_row_id = l.fact_loan_tran_row_id
and bi.fsa_loan_pgm_cd = 'PI1'
--and bi.btch_item_tran_id between 20150301000000 and 20150401999999
AND BI.PRCS_DT between '01-MAY-15' AND '31-MAY-15'
AND BI.BTCH_ITEM_SRC_CD = 'C'
AND BI.BTCH_ITEM_STAT_CD Not In ( 'FR','IP' )
) fa
group by fa.BORR_SSN, fa.BORR_ACCT_SFX_NBR, fa.LOAN_SEQ_NBR
) F,
FSA_ED.FACT_LOAN_MONTHLY ca,
FSA_ED.FACT_LOAN_MONTHLY pca
where ca.BORR_SSN = f.BORR_SSN
and ca.BORR_ACCT_SFX_NBR = f.BORR_ACCT_SFX_NBR
and ca.LOAN_SEQ_NBR = f.LOAN_SEQ_NBR
and ca.pstg_dt_row_id = 20150531
and pca.BORR_SSN = f.BORR_SSN
and pca.BORR_ACCT_SFX_NBR = f.BORR_ACCT_SFX_NBR
and pca.LOAN_SEQ_NBR = f.LOAN_SEQ_NBR
and pca.pstg_dt_row_id = 20150430
and ( (pca.LOAN_OSTD_PRIN_AMT + FA_PBO_AMT - ca.LOAN_OSTD_PRIN_AMT) <> 0 or
((pca.LOAN_ACCR_INT_AMT + pca.IBR_ACCR_INT_AMT + FA_IRB_AMT) - (ca.LOAN_ACCR_INT_AMT + ca.IBR_ACCR_INT_AMT )) <> 0 )
ORDER BY 1
?
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.