2 ALTER TYPE araging ADD ATTRIBUTE araging_aropen_id INTEGER;
5 -- Function: araging(date, boolean)
7 -- DROP FUNCTION araging(date, boolean);
9 CREATE OR REPLACE FUNCTION araging(date, boolean)
10 RETURNS SETOF araging AS
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.
15 pAsOfDate ALIAS FOR $1;
16 pUseDocDate ALIAS FOR $2;
22 FROM araging(pAsOfDate, pUseDocDate, true)
30 LANGUAGE plpgsql VOLATILE
33 ALTER FUNCTION araging(date, boolean)
38 -- Function: araging(date, boolean, boolean)
40 -- DROP FUNCTION araging(date, boolean, boolean);
42 CREATE OR REPLACE FUNCTION araging(date, boolean, boolean)
43 RETURNS SETOF araging AS
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.
48 pAsOfDate ALIAS FOR $1;
49 pUseDocDate ALIAS FOR $2;
50 pConvBaseCurr ALIAS FOR $3;
57 _asOfDate := COALESCE(pAsOfDate,current_date);
61 --report uses currtobase to convert all amounts to base based on aropen_docdate to ensure the same exchange rate
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,
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,
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,
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,
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,
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,
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,
114 COALESCE(arterms.terms_descrip, custterms.terms_descrip, '') AS terms_descrip,
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
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;
156 LANGUAGE plpgsql VOLATILE
159 ALTER FUNCTION araging(date, boolean, boolean)