Get Payment Status Query
function xxp (p_check_id in number,p_from_date in date,p_to_date in date) return varchar2
as
lv_status varchar2(100):=null ;
lv_cnt number:=0;
begin
begin
select count(*) into lv_cnt from ap_checks_all where check_id=p_check_id;
end;
if lv_cnt>=1 then
select case
when apha2.transaction_type = 'PAYMENT CREATED' and apha2.posted_flag = 'N' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT CREATED' and apha2.posted_flag = 'Y' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT UNCLEARING' and apha2.posted_flag = 'N' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT UNCLEARING' and apha2.posted_flag = 'Y' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT ADJUSTED' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT CLEARING' and apha2.posted_flag = 'N' then 'RECONCILED UNACCOUNTED'
when apha2.transaction_type = 'PAYMENT CLEARING' and apha2.posted_flag = 'Y' then 'RECONCILED'
when apha2.transaction_type = 'PAYMENT CLEARING ADJUSTED' and apha2.posted_flag = 'N' then 'RECONCILED'
when apha2.transaction_type = 'PAYMENT CLEARING ADJUSTED' and apha2.posted_flag = 'Y' then 'RECONCILED'
when apha2.transaction_type = 'REFUND RECORDED' and apha2.posted_flag = 'N' then 'NEGOTIABLE'
when apha2.transaction_type = 'REFUND RECORDED' and apha2.posted_flag = 'Y' then 'NEGOTIABLE'
when apha2.transaction_type = 'REFUND ADJUSTED' and apha2.posted_flag = 'N' then 'NEGOTIABLE'
when apha2.transaction_type = 'REFUND ADJUSTED' and apha2.posted_flag = 'Y' then 'NEGOTIABLE'
when apha2.transaction_type = 'REFUND CANCELLED' and apha2.posted_flag = 'N' then 'VOIDED'
when apha2.transaction_type = 'REFUND CANCELLED' and apha2.posted_flag = 'Y' then 'VOIDED'
when apha2.transaction_type = 'PAYMENT CANCELLED' and apha2.posted_flag = 'N' then 'VOIDED'
when apha2.transaction_type = 'PAYMENT CANCELLED' and apha2.posted_flag = 'Y' then 'VOIDED'
when apha2.transaction_type = 'PAYMENT MATURITY' and apha2.posted_flag = 'N' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT MATURITY' and apha2.posted_flag = 'Y' then 'NEGOTIABLE'
when apha2.transaction_type = 'PAYMENT MATURITY ADJUSTED' and apha2.posted_flag = 'N' then 'RECONCILED'
when apha2.transaction_type = 'PAYMENT MATURITY ADJUSTED' and apha2.posted_flag = 'Y' then 'RECONCILED'
when apha2.transaction_type = 'PAYMENT MATURITY REVERSAL' and apha2.posted_flag = 'N' then 'VOIDED'
when apha2.transaction_type = 'PAYMENT MATURITY REVERSAL' and apha2.posted_flag = 'Y' then 'VOIDED'
when apha2.transaction_type = 'MANUAL PAYMENT ADJUSTED' and apha2.posted_flag = 'N' then 'RECONCILED'
when apha2.transaction_type = 'MANUAL PAYMENT ADJUSTED' and apha2.posted_flag = 'Y' then 'RECONCILED'
--ELSE NULL
end status
into lv_status
from ap_payment_history_all apha2
where apha2.check_id = p_check_id -- 4268324
and apha2.accounting_date = (select max (apha1.accounting_date)
from ap_payment_history_all apha1
where apha1.check_id = apha2.check_id
--and apha1.POSTED_FLAG = 'Y'
and apha1.accounting_date between trunc (p_from_date) and trunc(p_to_date)
)
and apha2.payment_history_id =(select max (apha3.payment_history_id)
from ap_payment_history_all apha3
where apha3.check_id = apha2.check_id
and apha3.accounting_date =
(select max (apha1.accounting_date)
from ap_payment_history_all apha1
where apha1.check_id = apha2.check_id
--AND apha1.POSTED_FLAG = 'Y'
and apha1.accounting_date between trunc (p_from_date) and trunc(p_to_date)
))
and apha2.accounting_date between trunc(p_from_date) and trunc(p_to_date)
;
else
lv_status:='Un Paid';
end if;
return lv_status ;
exception when others then
lv_status:='Un Paid';
return lv_status;
end xxp;