CREATE OR REPLACE VIEW V_ARTRIALBALANCE AS
SELECT P.CODE PLANTCODE, P.NAME PLANTNAME,
  BU.CODE BUCODE, BU.DESCRIPTION BUDESCRIPTION,
  C.CUSTOMERNUMBER, C.NAME CUSTOMERNAME, C.PHONENO CUSTOMERPHONENO, C.PAYMENTMETHOD,
  B.DELINQUENTMARKAFTER, B.CREDITLIMIT,
  D.STREET, D.STREET2, D.ZIPCODE, D.CITY, D.STATE,
  I.CODE CHAINCODE, I.NAME CHAINNAME,
  E1.EMPLOYEENUMBER COLLECTIONRESPNUMBER,
  case when ar.debitcredit = 'D' and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) < 30 then
   (ar.amount-ar.totalappliedamount)   
 else 0 end CURRENT_AMOUNT,
 case when ar.debitcredit = 'D' and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) >= 30 and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) < 60 then
   (ar.amount-ar.totalappliedamount)   
 else 0 end AMOUNT_30,
 case when ar.debitcredit = 'D' and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) >= 60 and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) < 90 then
   (ar.amount-ar.totalappliedamount)   
 else 0 end AMOUNT_60,
 case when ar.debitcredit = 'D' and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) >= 90 and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) < 120 then
   (ar.amount-ar.totalappliedamount)   
 else 0 end AMOUNT_90,
 case when ar.debitcredit = 'D' and trunc(sysdate)-nvl(ihe.paymenttermsduedate,ar.artransactiondate) >= 120 then
   (ar.amount-ar.totalappliedamount)   
 else 0 end AMOUNT_120,
 case when ar.debitcredit = 'C' then
   (ar.amount-ar.totalappliedamount)   
 else 0 end AMOUNT_CREDIT,
  COALESCE(V1.DAY,0) DAY, V1.ROUTE ROUTE, V1.STOPNUMBER STOPNUMBER, E2.EMPLOYEENUMBER DRIVERNUMBER,
  V2.LASTPAYMENTDATE, GET_LASTPAYMENT(BU.BUSINESSUNIT_ID,C.CUSTOMER_ID,V2.LASTPAYMENTDATE) LASTPAYMENT,
   coalesce((select min(y.artransactiondate) lastpaymentdate
           from   artransaction y
           where  y.debitcredit = 'D'
           and    nvl(y.parentartransaction_id, y.artransaction_id) = y.artransaction_id
           and    nvl(y.closingtransaction_id, 0) = 0
           and    y.billtocustomer_link_id = c.customer_id
           and    y.businessunit_id = c.defaultbusinessunit_id
           group  by y.billtocustomer_link_id, y.businessunit_id), trunc(sysdate))  LASTDEBITDATE
, REPORTPARAM.REPORTPARAM_ID, BU.BUSINESSUNIT_ID, C.CUSTOMER_ID
, (SELECT DT.CODE FROM DISTRICT DT, ROUTE RO WHERE RO.ROUTENUMBER = V1.ROUTE AND RO.DRIVINGDAY_ID = V1.DAY AND RO.PLANT_ID = P.PLANT_ID AND RO.DISTRICT_ID = DT.DISTRICT_ID) DISTRICTCODE
, (SELECT DT1.DESCRIPTION FROM DISTRICT DT1, ROUTE RO WHERE RO.ROUTENUMBER = V1.ROUTE AND RO.DRIVINGDAY_ID = V1.DAY AND RO.PLANT_ID = P.PLANT_ID AND RO.DISTRICT_ID = DT1.DISTRICT_ID) DISTRICTDESC
, (SELECT RM.EMPLOYEENUMBER FROM EMPLOYEE RM, ROUTE RO WHERE RO.ROUTENUMBER = V1.ROUTE AND RO.DRIVINGDAY_ID = V1.DAY AND RO.PLANT_ID = P.PLANT_ID AND RO.ROUTEMANAGER_ID = RM.EMPLOYEE_ID) ROUTEMANAGERNO
, (SELECT RM1.NAME FROM EMPLOYEE RM1, ROUTE RO WHERE RO.ROUTENUMBER = V1.ROUTE AND RO.DRIVINGDAY_ID = V1.DAY AND RO.PLANT_ID = P.PLANT_ID AND RO.ROUTEMANAGER_ID = RM1.EMPLOYEE_ID) ROUTEMANAGERNAME
, ar.billtocustomer_link_id, ar.invoiceheader_id, ar.invoicenumber, ar.artransactiondate, sysdate currentdate          
, ihe.paymenttermsduedate duedate
FROM CUSTOMER C
    LEFT OUTER JOIN CHAIN I ON (C.CHAIN_ID = I.CHAIN_ID)
    LEFT OUTER JOIN V_LASTPAYMENT V2 ON (C.CUSTOMER_ID = V2.BILLTOCUSTOMER_LINK_ID AND C.DEFAULTBUSINESSUNIT_ID = V2.BUSINESSUNIT_ID)
    LEFT OUTER JOIN V_MIN_DAY_ROUTE_STOP V1
         LEFT OUTER JOIN EMPLOYEE E2 ON V1.DRIVER_ID = E2.EMPLOYEE_ID
    ON C.CUSTOMER_ID = V1.CUSTOMER_ID
    INNER JOIN CUSTOMERBILLINGINFO B
          LEFT OUTER JOIN EMPLOYEE E1 ON (B.COLLECTIONRESPONSIBLE_ID = E1.EMPLOYEE_ID)
    ON C.DEBTOR_ID = B.CUSTOMER_ID
    INNER JOIN ADDRESS D ON C.VISITADDRESS_ID = D.ADDRESS_ID
    INNER JOIN BUSINESSUNIT BU
          INNER JOIN PLANT P ON BU.PLANT_ID = P.PLANT_ID
    ON C.DEFAULTBUSINESSUNIT_ID = BU.BUSINESSUNIT_ID
    LEFT OUTER JOIN artransaction ar 
          left outer join invoiceheaderextrainfo ihe on ar.invoiceheader_id = ihe.invoiceheader_id
          inner join artransactiontype art on ar.artransactiontype_id = art.artransactiontype_id
    on C.CUSTOMER_ID = ar.billtocustomer_link_id,
  REPORTPARAM
where coalesce(ar.isopen_yn, 'Y') = 'Y' and coalesce(art.typenumber, 0) <> 2
      and coalesce(ar.parentartransaction_id, ar.artransaction_id, 0) = coalesce(ar.artransaction_id, 0)
/
