create or replace view ke014t3.BPV_Submission_Flow
as
select rownum SEQ,branch, doc_type, pay_no,doc_date,bank_code,ccy,amount,oth_ref,chq_no,pay_details, payee,pay_by,created_on
from
(select 
'HO' Branch,ph_doc_type DOC_TYPE, ph_doc_number PAY_NO, ph_doc_date DOC_DATE,
PH_CASH_BANK_CODE BANK_CODE, PH_CURRENCY CCY, PH_FOREIGN_AMOUNT AMOUNT,
upper(PH_OTHER_REFERENCE) OTH_REF, PH_CHEQUE_NUMBER CHQ_NO, upper(PH_NARRATION) PAY_DETAILS,
upper(PH_NOTES) PAYEE, PH_CREATED_BY PAY_BY, PH_CREATED_ON CREATED_ON
from
ke014t3.id_payment_header
where 
--ph_doc_date>sysdate-30
 ph_status='P'
and ph_doc_type in ('BPV','BPVS','CPV','CPVS')
union all
select 
'SAFCOM' Branch,ph_doc_type DOC_TYPE, ph_doc_number PAY_NO, ph_doc_date DOC_DATE,
PH_CASH_BANK_CODE BANK_CODE, PH_CURRENCY CCY, PH_FOREIGN_AMOUNT AMOUNT,
upper(PH_OTHER_REFERENCE) OTH_REF, PH_CHEQUE_NUMBER CHQ_NO, upper(PH_NARRATION) PAY_DETAILS,
upper(PH_NOTES) PAYEE, PH_CREATED_BY PAY_BY, PH_CREATED_ON CREATED_ON
from
ke074t3.id_payment_header
where 
--ph_doc_date>sysdate-30
ph_status='P'
and ph_doc_type in ('BPV','BPVS','CPV','CPVS'))
order by rownum,doc_date Desc, PAY_NO Asc
