git.roojs.org Git - Pman.Xtuple/blob - pgsql/apaging.sql

   1 -- Function: apaging(date, boolean)
   2
   3 -- DROP FUNCTION apaging(date, boolean);
   4
   5 CREATE OR REPLACE FUNCTION apaging(pAsOfDate date, pUseDocDate boolean)
   6   RETURNS SETOF apaging AS
   7 $BODY$
   8 -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
   9 -- See www.xtuple.com/CPAL for the full text of the software license.
  10 DECLARE
  11   _row apaging%ROWTYPE;
  12
  13 BEGIN
  14
  15   FOR _row IN SELECT *
  16             FROM apaging(pAsOfDate, pUseDocDate, true)
  17   LOOP
  18     RETURN NEXT _row;
  19   END LOOP;
  20
  21   RETURN;
  22 END;
  23 $BODY$
  24   LANGUAGE plpgsql VOLATILE
  25   COST 100
  26   ROWS 1000;
  27 ALTER FUNCTION apaging(date, boolean)
  28   OWNER TO admin;
  29
  30
  31 -- Function: apaging(date, boolean, boolean)
  32
  33 -- fixed to include correct apply currency
  34
  35 -- DROP FUNCTION apaging(date, boolean, boolean);
  36
  37 CREATE OR REPLACE FUNCTION apaging(pAsOfDate date, pUseDocDate boolean, pConvBaseCurr boolean)
  38   RETURNS SETOF apaging AS
  39 $BODY$
  40 DECLARE
  41
  42   _row apaging%ROWTYPE;
  43   _x RECORD;
  44   _returnVal INTEGER;
  45   _asOfDate DATE;
  46 BEGIN
  47
  48   _asOfDate := COALESCE(pAsOfDate,current_date);
  49
  50   FOR _x IN
  51         SELECT
  52         --report uses currency rate snapshot to convert all amounts to base based on apopen_docdate to ensure the same exchange rate
  53
  54
  55         --- aptarget_paid - may be in a different currency
  56         --- if the target != the apopen...
  57
  58
  59
  60
  61
  62
  63         --today and greater
  64         CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)))
  65         THEN ((apopen.apopen_amount-apopen.apopen_paid+COALESCE(SUM( (apapply_target_paid *  apopen.apopen_curr_rate) /   target_ap.apopen_curr_rate  ),0))/
  66         CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END *
  67         CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS cur_val,
  68
  69         --0 to 30
  70         CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)-30) AND (apopen.apopen_duedate < DATE(_asOfDate)))
  71         THEN ((apopen.apopen_amount-apopen.apopen_paid+COALESCE(SUM( (apapply_target_paid *  apopen.apopen_curr_rate) /   target_ap.apopen_curr_rate  ),0))/
  72         CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END *
  73         CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS thirty_val,
  74
  75         --30-60
  76         CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)-60) AND (apopen.apopen_duedate < DATE(_asOfDate) - 30 ))
  77         THEN ((apopen.apopen_amount-apopen.apopen_paid+COALESCE(SUM( (apapply_target_paid *  apopen.apopen_curr_rate) /   target_ap.apopen_curr_rate  ),0))/
  78         CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END *
  79         CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS sixty_val,
  80
  81         --60-90
  82         CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)-90) AND (apopen.apopen_duedate < DATE(_asOfDate) - 60))
  83         THEN ((apopen.apopen_amount-apopen.apopen_paid+COALESCE(SUM( (apapply_target_paid *  apopen.apopen_curr_rate) /   target_ap.apopen_curr_rate  ),0))/
  84         CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END *
  85         CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS ninety_val,
  86
  87         --greater than 90
  88         CASE WHEN((apopen.apopen_duedate > DATE(_asOfDate)-10000) AND (apopen.apopen_duedate < DATE(_asOfDate) - 90))
  89         THEN ((apopen.apopen_amount-apopen.apopen_paid+COALESCE(SUM( (apapply_target_paid *  apopen.apopen_curr_rate) /   target_ap.apopen_curr_rate  ),0))/
  90         CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END *
  91         CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS plus_val,
  92
  93         --total amount
  94         CASE WHEN((apopen.apopen_duedate > DATE(_asOfDate)-10000))
  95         THEN ((apopen.apopen_amount-apopen.apopen_paid+COALESCE(SUM( (apapply_target_paid *  apopen.apopen_curr_rate) /   target_ap.apopen_curr_rate  ),0))/
  96         CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END *
  97         CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS total_val,
  98
  99         --AR Open Amount
 100         CASE WHEN apopen.apopen_doctype IN ('C', 'R')
 101         THEN (apopen.apopen_amount * -1) / CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END
 102         ELSE apopen.apopen_amount / CASE WHEN (pConvBaseCurr) THEN apopen.apopen_curr_rate ELSE 1.0 END
 103         END AS apopen_amount,
 104
 105
 106         apopen.apopen_docdate,
 107         apopen.apopen_duedate,
 108         apopen.apopen_ponumber,
 109         apopen.apopen_invcnumber,
 110         apopen.apopen_docnumber,
 111         apopen.apopen_doctype,
 112         vend_id,
 113         vend_name,
 114         vend_number,
 115         vend_vendtype_id,
 116         vendtype_code,
 117         terms_descrip,
 118         apopen.apopen_id::text as apopen_id
 119
 120         FROM vendinfo, vendtype, apopen
 121           LEFT OUTER JOIN terms ON (apopen.apopen_terms_id=terms_id)
 122           LEFT OUTER JOIN apapply ON (((apopen.apopen_id=apapply_target_apopen_id)
 123                                     OR (apopen.apopen_id=apapply_source_apopen_id))
 124                                    AND (apapply_postdate >=_asOfDate))
 125           LEFT OUTER JOIN apopen target_ap
 126             ON apapply_target_apopen_id = target_ap.apopen_id
 127
 128         WHERE ( (apopen.apopen_vend_id = vend_id)
 129         AND (vend_vendtype_id=vendtype_id)
 130         AND (CASE WHEN (pUseDocDate) THEN apopen.apopen_docdate ELSE apopen.apopen_distdate END <= _asOfDate)
 131         AND (COALESCE(apopen.apopen_closedate,_asOfDate)>=_asOfDate)
 132         -- VOID ???
 133         AND
 134         apopen.apopen_docnumber NOT IN (
 135                                 SELECT
 136                                                         apopen_docnumber
 137                                                 FROM
 138                                                         apopen
 139                                                 WHERE
 140                                                         apopen_notes LIKE 'Void Voucher%'
 141                                                     AND
 142                                                         apopen_discount
 143                                 )
 144
 145         )
 146         GROUP BY apopen.apopen_id,apopen.apopen_docdate,apopen.apopen_duedate,apopen.apopen_ponumber, apopen.apopen_invcnumber, apopen.apopen_docnumber,apopen.apopen_doctype,apopen.apopen_paid,
 147                  apopen.apopen_curr_id,apopen.apopen_amount,vend_id,vend_name,vend_number,vend_vendtype_id,vendtype_code,terms_descrip,
 148                  apopen.apopen_curr_rate
 149
 150         UNION
 151          SELECT
 152             0 as cur_val,
 153             0 as thirty_val,
 154             0 as sixty_val,
 155             0 as ninety_val,
 156             0 as plus_val,
 157             -- we should use checkitem_docdate rather than apapply_postdate..
 158             CASE WHEN checkhead_void THEN
 159                 checkhead_amount / checkhead_curr_rate * -1
 160             ELSE
 161                 apapply_target_paid / apopen_curr_rate * -1
 162             END AS total_val,
 163
 164             CASE WHEN checkhead_void THEN
 165                 checkhead_amount / checkhead_curr_rate * -1
 166             ELSE
 167                 apapply_target_paid / apopen_curr_rate * -1
 168             END AS apopen_amount,
 169
 170
 171             checkhead_checkdate as apopen_docdate,
 172             checkhead_checkdate as apopen_duedate,
 173             '' as apopen_ponumber,
 174             '' as apopen_invcnumber,
 175             checkhead_number::text as apopen_docnumber,
 176             'CK' as apopen_doctype,
 177             vend_id,
 178             vend_name,
 179             vend_number,
 180             vend_vendtype_id,
 181             vendtype_code,
 182             '' as terms_descrip,
 183             apopen_id::text as apopen_id
 184
 185             FROM
 186                     checkhead
 187                  LEFT JOIN
 188                     apapply
 189                 ON
 190                     apapply_journalnumber = checkhead_journalnumber
 191                     AND
 192                     checkhead_id = apapply_checkhead_id
 193                 LEFT JOIN
 194                          apopen
 195                 ON
 196                          apopen_id = apapply_target_apopen_id
 197
 198                LEFT JOIN
 199                    vendinfo
 200                    ON
 201                     checkhead_recip_id = vend_id
 202
 203                LEFT JOIN
 204                     vendtype
 205                ON
 206                    vend_vendtype_id=vendtype_id
 207
 208             WHERE (
 209                    checkhead_recip_type = 'V'
 210              --   AND NOT checkhead_deleted
 211                 AND
 212                    (
 213                         (NOT checkhead_void AND apapply_postdate >= _asOfDate)
 214                         OR
 215                         (checkhead_void AND checkhead_voided > _asOfDate)
 216                     )
 217
 218                 AND
 219                    (checkhead_checkdate <= _asOfDate)
 220
 221                  )
 222
 223
 224         ORDER BY
 225
 226
 227           vend_number, apopen_duedate
 228   LOOP
 229         _row.apaging_docdate := _x.apopen_docdate;
 230         _row.apaging_duedate := _x.apopen_duedate;
 231         _row.apaging_ponumber := _x.apopen_ponumber;
 232         _row.apaging_invcnumber := _x.apopen_invcnumber;
 233         _row.apaging_docnumber := _x.apopen_docnumber;
 234         _row.apaging_doctype := _x.apopen_doctype;
 235         _row.apaging_vend_id := _x.vend_id;
 236         _row.apaging_vend_number := _x.vend_number;
 237         _row.apaging_vend_name := _x.vend_name;
 238         _row.apaging_vend_vendtype_id := _x.vend_vendtype_id;
 239         _row.apaging_vendtype_code := _x.vendtype_code;
 240         _row.apaging_terms_descrip := _x.terms_descrip;
 241         _row.apaging_apopen_amount := _x.apopen_amount;
 242         _row.apaging_cur_val := _x.cur_val;
 243         _row.apaging_thirty_val := _x.thirty_val;
 244         _row.apaging_sixty_val := _x.sixty_val;
 245         _row.apaging_ninety_val := _x.ninety_val;
 246         _row.apaging_plus_val := _x.plus_val;
 247         _row.apaging_total_val := _x.total_val;
 248         _row.apaging_reference := _x.apopen_id::text;
 249         RETURN NEXT _row;
 250   END LOOP;
 251   RETURN;
 252 END;
 253 $BODY$
 254   LANGUAGE plpgsql VOLATILE
 255   COST 100
 256   ROWS 1000;
 257 ALTER FUNCTION apaging(date, boolean)
 258   OWNER TO admin;