create or replace function f_get_rqa_forRelLoanBilling(plpcid       number,
                                                       deliverydate date)
  return number is
  res            number;
  v_plpcid       number;
  v_deliverydate date;
begin
  v_plpcid       := plpcid;
  v_deliverydate := deliverydate;
  select /*+ index (DV XPKDELIVERYVARIATIONS)*/
   nvl(sum(RQA.QUANTITY), 0)
    into res
    from RENTALQTYADJUSTMENT RQA
    left outer join DELIVERYVARIATIONS DV
      on RQA.DELIVERYVARIATIONS_ID = DV.DELIVERYVARIATIONS_ID
   where (select count(1)
            from STANDARDDELIVERYLINE S
           where S.PRODUCTLISTPERDISTRIBPOINT_ID =
                 rqa.productlistperdistribpoint_id
             and S.PRODUCTLISTPERCUSTOMER_ID = v_plpcid) > 0
     and (DECODE(NVL(RQA.DELIVERYVARIATIONS_ID, 0),
                 0,
                 RQA.ADJUSTMENTDATE,
                 DV.DELIVERYDATE) <= v_deliverydate);
  return res;
end f_get_rqa_forRelLoanBilling;
/
