1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
|
SET TERM ^^ ;
CREATE PROCEDURE P_HISTORY (
I_FROMMONTH Numeric(2,0),
I_FROMYEAR Numeric(4,0),
I_TOMONTH Numeric(2,0),
I_TOYEAR Numeric(4,0),
I_CUSTOMERS_ID Integer,
I_ITEMSIZES_ID Integer,
I_ITEMGRADES_ID Integer,
I_ITEMTYPES_ID Integer,
I_REPT_TYPE SmallInt)
AS
/*
Procedure:
Author : A LAKSHMI
Date : 02-SEP-09
Purpose : TAKING REPORT
Params
------
FROMDATE, TODATE : TO FILTER THE DATA
CUSTOMERS_ID : TO FILTER WITH CUSTOMER ID
ITEMSIZES_ID, ITEMGRADES_ID, ITEMTYPES_ID : TO FILTER WITH ITEM
REPT_TYPE : REPORT TYPE --> 1 - CUSTOMER WISE REPORT
2 - ITEM WISE REPORT
*/
declare variable x_FromDate DATE;
declare variable x_ToDate DATE;
declare variable v_str varchar(1000);
declare variable v_qryfields_str varchar(1000);
declare variable v_qrydata_str varchar(1000);
declare variable v_qrystr VARCHAR(1000);
declare variable x_itemsizes_id bigint; /*INTEGER*/
declare variable x_itemgrades_id bigint; /*INTEGER*/
declare variable x_itemtypes_id bigint; /*INTEGER*/
declare variable x_customers_id bigint; /*INTEGER*/
declare variable x_month integer;
declare variable x_year integer;
declare variable p_itemsizes_id integer;
declare variable p_itemgrades_id integer;
declare variable p_itemtypes_id integer;
declare variable p_customers_id integer;
declare variable x_qty double precision; /*numeric(18,4);*/
begin
x_FromDate = CAST(I_FROMYEAR || '-' || I_FROMMONTH || '-01' AS DATE);
IF (I_TOMONTH = 12) THEN
begin
v_str = (I_TOYEAR+1) || '-01-01';
x_ToDate = CAST(v_str AS DATE) - 1;
end
ELSE
begin
v_str = I_TOYEAR || '-' || (I_TOMONTH+1) || '-01';
x_ToDate = CAST(v_str AS DATE) - 1;
end
/**** Start - Creation of Table ****/
v_str = 'RECREATE TABLE TMP_HISTORY (ITEMSIZES_ID INTEGER, ITEMGRADES_ID INTEGER,'
|| ' ITEMTYPES_ID INTEGER, CUSTOMERS_ID INTEGER ';
while ((i_FromYear < i_ToYear) or ((i_FromYear = i_ToYear) and (i_FromMonth <= i_ToMonth))) do
begin
v_str = v_str || ', M' || i_FromMonth || '_' || i_FromYear || ' numeric(18,4)';
if (i_FromMonth = 12) then
begin
i_FromYear = i_FromYear + 1;
i_FromMonth = 1;
end
else
begin
i_FromMonth = i_FromMonth + 1;
end
end
v_str = v_str || ')';
execute statement v_str;
/**** Finish - Creation of Table ****/
v_str = 'SELECT ITEMS.ITEMSIZES_ID,ITEMS.ITEMGRADES_ID,ITEMS.ITEMTYPES_ID,CUSTOMERS.CUSTOMERS_ID, sum(QTY), '
|| 'EXTRACT(Month FROM INVOICES.INVOICEDATE), EXTRACT(year FROM INVOICES.INVOICEDATE) FROM INVOICES '
|| 'JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERS_ID = INVOICES.CUSTOMERS_ID '
|| 'JOIN INVOICEDETAILS ON INVOICEDETAILS.INVOICES_ID = INVOICES.INVOICES_ID '
|| 'JOIN ITEMS ON ITEMS.ITEMS_ID = INVOICEDETAILS.ITEMS_ID '
|| 'WHERE INVOICES.INVOICEDATE BETWEEN ''' || x_FromDate || ''' AND ''' || x_ToDate || '''';
if (I_REPT_TYPE = 1) then /* customer wise report */
BEGIN
if (I_CUSTOMERS_ID <> 0) then
v_str = v_str || ' AND CUSTOMERS.CUSTOMERS_ID=' || I_CUSTOMERS_ID;
END
IF (I_REPT_TYPE = 2) THEN
BEGIN
IF (I_ITEMSIZES_ID <> 0) THEN
V_STR = V_STR || ' AND ITEMS.ITEMSIZES_ID=' || I_ITEMSIZES_ID;
IF (I_ITEMTYPES_ID <> 0) THEN
V_STR = V_STR || ' AND ITEMS.ITEMTYPES_ID=' || I_ITEMTYPES_ID;
IF (I_ITEMGRADES_ID <> 0) THEN
V_STR = V_STR || ' AND ITEMS.ITEMGRADES_ID=' || I_ITEMGRADES_ID;
END
v_str = v_str || ' group by ITEMS.ITEMSIZES_ID,ITEMS.ITEMGRADES_ID,ITEMS.ITEMTYPES_ID,CUSTOMERS.CUSTOMERS_ID, EXTRACT(Month FROM '
|| 'INVOICEDATE), EXTRACT(year FROM INVOICEDATE)';
p_itemsizes_id = 0;
p_itemgrades_id = 0;
p_itemtypes_id = 0;
p_customers_id = 0;
v_qryfields_str = '';
v_qrydata_str = '';
FOR EXECUTE STATEMENT :v_str
INTO :x_itemsizes_id, :x_itemgrades_id, :x_itemtypes_id, :x_customers_id, :x_qty, :x_month, :x_year do
begin
if ((p_customers_id <> x_customers_id) or (p_itemsizes_id <> x_itemsizes_id)
or (p_itemgrades_id <> x_itemgrades_id) or (p_itemtypes_id <> x_itemtypes_id)) then
begin
if (v_qryfields_str <> '') then
begin
v_qrystr = 'INSERT INTO TMP_HISTORY (ITEMSIZES_ID, ITEMGRADES_ID, ITEMTYPES_ID, CUSTOMERS_ID'
|| v_qryfields_str || ') values (' || p_itemsizes_id || ', ' || p_itemgrades_id || ', '
|| p_itemtypes_id || ', ' || p_customers_id || v_qrydata_str || ')';
EXECUTE STATEMENT v_qrystr;
v_qryfields_str = '';
v_qrydata_str = '';
end
p_customers_id = x_customers_id;
p_itemsizes_id = x_itemsizes_id;
p_itemgrades_id = x_itemgrades_id;
p_itemtypes_id = x_itemtypes_id;
end
v_qryfields_str = v_qryfields_str || ', M' || x_Month || '_' || x_Year;
v_qrydata_str = v_qrydata_str || ', ' || x_qty;
end
end
^^
SET TERM ; ^^
|
