Query on interest orders
En Abfrog um Zinsorder abzfroge
select d.bp_imed_id bu_id
,d.id doc_id
,coalesce(wfs.intl_id, wfs.name)||' ('||d.wfc_status_id||')' wfc_status
,cot.intl_id||' ('||d.order_type_id||')' order_type
,d.trx_date
,d.val_date
-- ,d.perf_date
,dti.accr_sop sop
,dti.accr_eop eop
,di.intr_net
,di.intr_cred_cor cred_cor
,di.intr_deb_cor deb_cor
,dtc.val tax_amt
,to_number(pos_key.key_val) macc_key
,(select max(key_val) from obj_key where obj_id = d.cont_2_id and obj_key_id in (14,15)) target_cont
,(select to_number(key_val) from obj_key where obj_id = d.pos_2_id and obj_key_id = 12) target_macc
,su.name||' ('||d.ins_by_sec_user_id||')' inserted_by
,d.bp_1_id
,d.cont_1_id
,d.pos_1_id
from k.doc d
,k.doc_intr di
,k.doc_tab_intr dti
,k.doc_tab_cost dtc
,k.wfc_status wfs
,k.code_order_type cot
,k.sec_user su
,k.obj_key pos_key
where 1=1
and d.id in (123456)
and d.meta_typ_id = 16 -- intr
and d.trx_date >= '20-APR-2012'
--and d.trx_date between '21-may-2012' and '21-may-2012'
--and d.idx_wfc_status_id between 160000 and 169999
--and d.bp_1_id = 6012100
--and d.cont_1_id = 6884624
--and d.pos_1_id in (2662720,2705101,2704269)
--and d.wfc_status_id in (90)
--and d.order_type_id in (1610, 1611) -- credit & debit
--
and di.doc_id = d.id
and dti.doc_id = d.id
and dti.seq_nr = 1 -- 1=debit compo / 2=credit compo
-- UK tax amount
and dtc.doc_id (+)= d.id
and dtc.book_kind_id (+)= 8020
-- WF Status
and (wfs.id = d.wfc_status_id and wfs.meta_typ_id = d.meta_typ_id)
--
and pos_key.obj_id (+)= d.pos_1_id
and pos_key.obj_key_id (+)= 12
--
and cot.id (+)= d.order_type_id
and su.id (+)= d.ins_by_sec_user_id
;
