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

   1
   2 ALTER TYPE araging ADD ATTRIBUTE araging_aropen_id INTEGER;
   3
   4
   5 -- Function: araging(date, boolean)
   6
   7 -- DROP FUNCTION araging(date, boolean);
   8
   9 CREATE OR REPLACE FUNCTION araging(date, boolean)
  10   RETURNS SETOF araging AS
  11 $BODY$
  12 -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
  13 -- See www.xtuple.com/CPAL for the full text of the software license.
  14 DECLARE
  15   pAsOfDate ALIAS FOR $1;
  16   pUseDocDate ALIAS FOR $2;
  17   _row araging%ROWTYPE;
  18
  19 BEGIN
  20
  21   FOR _row IN SELECT *
  22             FROM araging(pAsOfDate, pUseDocDate, true)
  23   LOOP
  24     RETURN NEXT _row;
  25   END LOOP;
  26
  27   RETURN;
  28 END;
  29 $BODY$
  30   LANGUAGE plpgsql VOLATILE
  31   COST 100
  32   ROWS 1000;
  33 ALTER FUNCTION araging(date, boolean)
  34   OWNER TO admin;
  35
  36
  37
  38 -- Function: araging(date, boolean, boolean)
  39
  40 -- DROP FUNCTION araging(date, boolean, boolean);
  41
  42 CREATE OR REPLACE FUNCTION araging(date, boolean, boolean)
  43   RETURNS SETOF araging AS
  44 $BODY$
  45 -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
  46 -- See www.xtuple.com/CPAL for the full text of the software license.
  47 DECLARE
  48   pAsOfDate ALIAS FOR $1;
  49   pUseDocDate ALIAS FOR $2;
  50   pConvBaseCurr ALIAS FOR $3;
  51   _row araging%ROWTYPE;
  52   _x RECORD;
  53   _returnVal INTEGER;
  54   _asOfDate DATE;
  55 BEGIN
  56
  57   _asOfDate := COALESCE(pAsOfDate,current_date);
  58
  59   FOR _x IN
  60         SELECT
  61         --report uses currtobase to convert all amounts to base based on aropen_docdate to ensure the same exchange rate
  62
  63         --today and greater base:
  64         CASE WHEN((aropen_duedate >= DATE(_asOfDate)))
  65         THEN (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/
  66         CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END *
  67         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END) ELSE 0 END AS cur_val,
  68
  69         --0 to 30 base
  70         CASE WHEN((aropen_duedate >= DATE(_asOfDate)-30) AND (aropen_duedate < DATE(_asOfDate)))
  71         THEN (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/
  72         CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END *
  73         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END) ELSE 0 END AS thirty_val,
  74
  75         --30-60 base
  76         CASE WHEN((aropen_duedate >= DATE(_asOfDate)-60) AND (aropen_duedate < DATE(_asOfDate) - 30 ))
  77         THEN (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/
  78         CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END *
  79         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END) ELSE 0 END AS sixty_val,
  80
  81         --60-90 base
  82         CASE WHEN((aropen_duedate >= DATE(_asOfDate)-90) AND (aropen_duedate < DATE(_asOfDate) - 60))
  83         THEN (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/
  84         CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END *
  85         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END) ELSE 0 END AS ninety_val,
  86
  87         --greater than 90 base:
  88         CASE WHEN((aropen_duedate > DATE(_asOfDate)-10000) AND (aropen_duedate < DATE(_asOfDate) - 90))
  89         THEN (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/
  90         CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END *
  91         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END) ELSE 0 END AS plus_val,
  92
  93         --total amount base:
  94         CASE WHEN((aropen_duedate > DATE(_asOfDate)-10000))
  95         THEN (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/
  96         CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END *
  97         CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END) ELSE 0 END AS total_val,
  98
  99         --AR Open Amount base
 100         CASE WHEN aropen_doctype IN ('C', 'R')
 101         THEN (aropen_amount * -1) / CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END
 102         ELSE aropen_amount / CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate ELSE 1.0 END END AS aropen_amount,
 103
 104         aropen_docdate,
 105         aropen_duedate,
 106         aropen_ponumber,
 107         aropen_docnumber,
 108         aropen_doctype,
 109         cust_id,
 110         cust_name,
 111         cust_number,
 112         cust_custtype_id,
 113         custtype_code,
 114         COALESCE(arterms.terms_descrip, custterms.terms_descrip, '') AS terms_descrip,
 115         aropen_id
 116
 117         FROM aropen
 118           JOIN custinfo ON (cust_id=aropen_cust_id)
 119           JOIN custtype ON (custtype_id=cust_custtype_id)
 120           LEFT OUTER JOIN terms arterms ON (arterms.terms_id=aropen_terms_id)
 121           LEFT OUTER JOIN terms custterms ON (custterms.terms_id=cust_terms_id)
 122           LEFT OUTER JOIN arapply ON (((aropen_id=arapply_target_aropen_id)
 123                                     OR (aropen_id=arapply_source_aropen_id))
 124                                    AND (arapply_distdate>_asOfDate))
 125         WHERE ( (CASE WHEN (pUseDocDate) THEN aropen_docdate ELSE aropen_distdate END <= _asOfDate)
 126         AND (COALESCE(aropen_closedate,_asOfDate+1)>_asOfDate) )
 127         GROUP BY aropen_id,aropen_docdate,aropen_duedate,aropen_ponumber,aropen_docnumber,aropen_doctype,aropen_paid,
 128                  aropen_curr_id,aropen_amount,cust_id,cust_name,cust_number,cust_custtype_id,custtype_code,
 129                  arterms.terms_descrip,custterms.terms_descrip, aropen_curr_rate
 130         ORDER BY cust_number, aropen_duedate
 131   LOOP
 132         _row.araging_docdate := _x.aropen_docdate;
 133         _row.araging_duedate := _x.aropen_duedate;
 134         _row.araging_ponumber := _x.aropen_ponumber;
 135         _row.araging_docnumber := _x.aropen_docnumber;
 136         _row.araging_doctype := _x.aropen_doctype;
 137         _row.araging_cust_id := _x.cust_id;
 138         _row.araging_cust_number := _x.cust_number;
 139         _row.araging_cust_name := _x.cust_name;
 140         _row.araging_cust_custtype_id := _x.cust_custtype_id;
 141         _row.araging_custtype_code := _x.custtype_code;
 142         _row.araging_terms_descrip := _x.terms_descrip;
 143         _row.araging_aropen_amount := _x.aropen_amount;
 144         _row.araging_cur_val := _x.cur_val;
 145         _row.araging_thirty_val := _x.thirty_val;
 146         _row.araging_sixty_val := _x.sixty_val;
 147         _row.araging_ninety_val := _x.ninety_val;
 148         _row.araging_plus_val := _x.plus_val;
 149         _row.araging_aropen_id := _x.aropen_id;
 150         _row.araging_total_val := _x.total_val;
 151         RETURN NEXT _row;
 152   END LOOP;
 153   RETURN;
 154 END;
 155 $BODY$
 156   LANGUAGE plpgsql VOLATILE
 157   COST 100
 158   ROWS 1000;
 159 ALTER FUNCTION araging(date, boolean, boolean)
 160   OWNER TO admin;