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