· 5 years ago · May 12, 2020, 01:06 AM
1CREATE OR REPLACE FUNCTION r_report_pemenuhan_stock(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying)
2 RETURNS SETOF refcursor AS
3$BODY$
4DECLARE
5 pRefHeader REFCURSOR := 'refHeader';
6 pRefDetail REFCURSOR := 'refDetail';
7 pSessionId ALIAS FOR $1;
8 pTenantId ALIAS FOR $2;
9 pUserId ALIAS FOR $3;
10 pRoleId ALIAS FOR $4;
11 pDatetime ALIAS FOR $5;
12
13 pOuId ALIAS FOR $6;
14 pWarehouseId ALIAS FOR $7;
15 pGroupBrandId ALIAS FOR $8;
16 pProductCodeName ALIAS FOR $9;
17
18 vOuCodeName character varying(100);
19 vWarehouseCodeName character varying(100);
20 vGroupBrandCodeName character varying(100);
21
22 vFilterProduct text := '';
23 vEmptyString character varying := '';
24 vFlgYes character varying := 'Y';
25 vStatusReleased character varying := 'R';
26 vStatusInProgress character varying := 'I';
27
28 vEmptyId bigint := -99;
29
30
31 vDocTypeDO bigint := 311;
32 vDocTypeRgto bigint := 538;
33 vDocTypeGTO bigint := 533;
34 vDocTypeRG bigint := 111;
35
36BEGIN
37
38 DELETE FROM tt_r_pemenuhan_stock WHERE session_id = pSessionId;
39
40 -- filter ou id
41 IF(pOuId <> vEmptyId) THEN
42 SELECT ou_code||' - '||ou_name INTO vOuCodeName
43 FROM t_ou
44 WHERE ou_id = pOuId;
45 END IF;
46
47 -- filter warehouse id
48 IF(pWarehouseId <> vEmptyId) THEN
49 SELECT warehouse_code||' - '||warehouse_name INTO vWarehouseCodeName
50 FROM m_warehouse
51 WHERE warehouse_id = pWarehouseId;
52 END IF;
53
54 -- filter group brand id
55 IF(pGroupBrandId <> vEmptyId) THEN
56 SELECT group_brand_code||' - '||group_brand_name INTO vGroupBrandCodeName
57 FROM m_group_brand
58 WHERE group_brand_id = pGroupBrandId;
59 END IF;
60
61
62 -- insert qty current into table tt_r_pemenuhan_stock from table in_product_balance_stock
63 INSERT INTO tt_r_pemenuhan_stock(
64 session_id, product_id, product_code, product_name,
65 group_brand, qty_current)
66 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
67 vEmptyString AS group_brand, COALESCE (SUM(A.qty),0) AS qty_current
68 FROM in_product_balance_stock A
69 INNER JOIN m_product_status B ON A.tenant_id = B.tenant_id AND A.product_status = B.product_status_code
70 WHERE B.flg_sell = vFlgYes
71 AND A.tenant_id = pTenantId
72 AND A.warehouse_id = pWarehouseId
73 GROUP BY A.product_id;
74
75 -- insert qty_os_so (Belum Jadi DO) into table tt_r_pemenuhan_stock from table sl_so_balance_item
76 INSERT INTO tt_r_pemenuhan_stock(
77 session_id, product_id, product_code, product_name,
78 group_brand, qty_os_so)
79 SELECT pSessionId, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name,
80 vEmptyString AS group_brand, COALESCE (SUM(C.qty_so),0) AS qty_os_so
81 FROM sl_so A
82 INNER JOIN sl_so_item B ON A.so_id = B.so_id
83 INNER JOIN sl_so_balance_item C ON B.so_item_id = C.so_item_id
84 WHERE C.status_item IN (vStatusReleased, vStatusInProgress)
85 AND A.tenant_id = pTenantId
86 AND A.ou_id = pOuId
87 AND NOT EXISTS (SELECT 1 FROM sl_do Y
88 WHERE Y.ref_id = A.so_id
89 AND Y.ref_doc_type_id = A.doc_type_id
90 AND Y.doc_type_id = vDocTypeDO)
91 GROUP BY B.product_id;
92
93 -- insert qty_rgto (Belum kebuat GTO) into table tt_r_pemenuhan_stock from table in_inventory
94 INSERT INTO tt_r_pemenuhan_stock(
95 session_id, product_id, product_code, product_name,
96 group_brand, qty_rgto)
97 SELECT pSessionId, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name,
98 vEmptyString AS group_brand, COALESCE (SUM(B.qty_request),0) AS qty_rgto
99 FROM in_inventory A
100 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
101 WHERE A.status_doc = vStatusReleased
102 AND A.doc_type_id = vDocTypeRgto
103 AND A.tenant_id = pTenantId
104 AND A.ou_from_id = pOuId
105 AND A.warehouse_from_id = pWarehouseId
106 AND NOT EXISTS (SELECT 1 FROM in_inventory Y
107 WHERE Y.ref_id = A.inventory_id
108 AND Y.ref_doc_type_id = A.doc_type_id
109 AND Y.doc_type_id = vDocTypeGTO)
110 GROUP BY B.product_id;
111
112 -- insert qty_os_po (belum kebuat received Good) into table tt_r_pemenuhan_stock from table in_inventory pu_po_balance_item
113 INSERT INTO tt_r_pemenuhan_stock(
114 session_id, product_id, product_code, product_name,
115 group_brand, qty_os_po)
116 SELECT pSessionId, B.product_id, f_get_product_code(B.product_id) AS product_code, f_get_product_name(B.product_id) AS product_name,
117 vEmptyString AS group_brand, COALESCE (SUM(C.qty_po),0) AS qty_os_po
118 FROM pu_po A
119 INNER JOIN pu_po_item B ON A.po_id = B.po_id
120 INNER JOIN pu_po_balance_item C ON B.po_item_id = C.po_item_id
121 WHERE C.status_item IN (vStatusReleased, vStatusInProgress)
122 AND A.ou_id = pOuId
123 AND A.tenant_id = pTenantId
124 AND A.warehouse_id = pWarehouseId
125 AND NOT EXISTS (SELECT 1 FROM pu_receive_goods Y
126 WHERE Y.ref_id = A.po_id
127 AND Y.ref_doc_type_id = A.doc_type_id
128 AND Y.doc_type_id = vDocTypeRG)
129 GROUP BY B.product_id;
130
131 OPEN pRefHeader FOR
132 SELECT CASE WHEN pOuId = vEmptyId THEN -99 ELSE pOuId END AS ou_id, vOuCodeName AS ou_code_name,
133 CASE WHEN pWarehouseId = vEmptyId THEN -99 ELSE pWarehouseId END AS warehouse_id, vWarehouseCodeName AS warehouse_code_name,
134 CASE WHEN pGroupBrandId = vEmptyId THEN -99 ELSE pGroupBrandId END AS group_brand_id, vGroupBrandCodeName AS group_brand_code_name,
135 pDatetime AS datetime, f_get_username(pUserId) AS username;
136 RETURN NEXT pRefHeader;
137
138 -- filter product code / name
139 IF (pProductCodeName <> vEmptyString) THEN
140 vFilterProduct := ' AND (UPPER(f_get_product_code(A.product_id)) LIKE UPPER(''%' || pProductCodeName || '%'') OR UPPER(f_get_product_name(A.product_id)) LIKE UPPER(''%' || pProductCodeName || '%''))';
141 END IF;
142
143 OPEN pRefDetail FOR
144 SELECT A.product_code, A.product_name, CONCAT(B.group_brand_code, ' - ', B.group_brand_name) AS group_brand,
145 SUM(A.qty_current) AS qty_current, SUM(A.qty_os_so) AS qty_os_so, SUM(A.qty_rgto) AS qty_rgto, SUM(A.qty_os_po) AS qty_os_po,
146 (SUM(A.qty_current) + SUM(A.qty_os_po) - SUM(A.qty_os_so) - SUM(A.qty_rgto)) AS qty_need_to_order
147 FROM tt_r_pemenuhan_stock A
148 INNER JOIN m_group_brand B ON pGroupBrandId = B.group_brand_id AND B.tenant_id = pTenantId
149 WHERE A.session_id = pSessionId
150 GROUP BY A.product_code, A.product_name, B.group_brand_code, B.group_brand_name
151 HAVING SUM(A.qty_os_so) != 0 OR SUM(A.qty_os_po) != 0 OR SUM(A.qty_rgto) != 0
152 ORDER BY product_name;
153
154
155 RETURN NEXT pRefDetail;
156
157 DELETE FROM tt_r_pemenuhan_stock WHERE session_id = pSessionId;
158
159END;
160$BODY$
161 LANGUAGE plpgsql VOLATILE
162 COST 100
163 ROWS 1000;
164/