· 9 years ago · Jan 04, 2017, 05:02 AM
1CREATE OR REPLACE FUNCTION f_process_stock_awal_bulan_januari_2017_grazia(bigint, character varying, character varying, bigint)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pTenantId ALIAS FOR $1;
6 pSessionId ALIAS FOR $2;
7 pDatetime ALIAS FOR $3;
8 pUserId ALIAS FOR $4;
9
10 vDocTypeGto bigint := 533;
11 vDocTypeGti bigint := 535;
12 vDocTypeAdjStock bigint := 521;
13 vDocTypeMobileSales bigint := 407;
14 vDocTypeMobileRetur bigint := 408;
15 vDocTypeMobileverifikasi bigint := 432;
16 vDateYearMonth character varying := '201701';
17 vPrevDateYearMonth character varying := '201608';
18 vStartDate character varying := '20160801';
19 vEndDate character varying := '20170101';
20
21 vOuIdGrazia bigint;
22 vProductStatusGood character varying(5);
23 vCount bigint;
24 vBaseUomId bigint;
25
26 vEmptyValue character varying := ' ';
27 vEmptyId bigint := -99;
28
29BEGIN
30 /*
31 Function ini akan membuatkan stock awal bulan untuk Januari 2017.
32 data yang digunakan adalah stock opname terbaru yang sudah di proses dengan memperhitungkan
33 transaksi stock dari tanggal setelah stock opname sampai 31 Desember 2016.
34
35 notes : - Untuk data yang tidak ada stock opname, qty_opname yang digunakan adalah qty stock awal bulan 201608
36 - Untuk data yang tidak ada stock opname dan tidak ada qty stock awal bulan 201608,
37 maka qty_opname dianggap 0 dan qty_akhir diambil dari nilai total_qty_transaksi
38
39
40 * 0. Cek apakah function ini sudah pernah dijalankan
41 * 1. Cari dan tampung ke table temp stock per outlet berdasarkan stock opname
42 * 2. cari dan tampung ke temp table semua data GTO yang GTInya sudah melewati lead-time atau GTO yang belum ada GTI dan sudah melewati lead time
43 * 3. berdasarkan data GTO no. 3, cari dan tampung ke table temp data di table in_log_product_balance_stock
44 * 4. cari data in_log_product_balance_stock untuk penjualan, retur dan verifikasi yang terjadi sebelum stock opaname karena akan di exclude juga
45 * 5. hitung stock awal bulan
46 */
47
48 SELECT uom_id INTO vBaseUomId
49 FROM m_uom where uom_code = 'PCS';
50
51 SELECT f_get_value_system_config_by_param_code(pTenantId, 'OU.GRAZIA.INDONESIA') INTO vOuIdGrazia;
52 SELECT f_get_value_system_config_by_param_code(pTenantId, 'PRODUCT.STATUS.GOOD') INTO vProductStatusGood;
53
54 -- 0. Cek apakah function ini sudah pernah dijalankan
55 SELECT COUNT(1) INTO vCount
56 FROM in_summary_monthly_qty
57 WHERE date_year_month = vDateYearMonth;
58
59 -- Proses dijalankan jika pada date_year_month yang diinput belum pernah dijalankan.
60 IF vCount = 0 THEN
61 RAISE NOTICE '1';
62 -- 1. Cari dan tampung ke table temp stock per outlet berdasarkan stock opname terbaru yang sudah pernah di proses
63 INSERT INTO tt_stock_opname_outlet (session_id, mobile_pos_stock_opname_id, inventory_id, tenant_id, outlet_id, warehouse_id, doc_date, user_process_id)
64 SELECT pSessionId, mobile_pos_stock_opname_id, inventory_id, tenant_id, outlet_id, warehouse_id, MAX(doc_date), user_process_id FROM i_mobile_pos_stock_opname A
65 INNER JOIN in_inventory_adjust_stock_ext B ON A.mobile_pos_stock_opname_id = B.ref_id
66 group by mobile_pos_stock_opname_id, inventory_id, tenant_id, outlet_id, warehouse_id, user_process_id;
67
68 RAISE NOTICE '2';
69 -- 2. cari dan tampung ke temp table semua data GTO yang GTInya sudah melewati lead-time dan terjadi sebelum sebelum stock opname berdasarkan create_datetime
70 INSERT INTO tt_exclude_gto_for_stok_awal_bulan (session_id, gto_id, gto_no, gto_date, gti_id, gti_no, gti_date, warehouse_from_id, warehouse_to_id)
71 SELECT pSessionId,
72 A.inventory_id AS gto_id, A.doc_no AS gto_no, A.doc_date AS gto_date,
73 B.inventory_id AS gti_id, B.doc_no AS gti_no, B.doc_date AS gti_date,
74 A.warehouse_from_id, A.warehouse_to_id
75 FROM in_inventory A
76 INNER JOIN in_inventory B ON A.inventory_id = B.ref_id AND B.doc_type_id = vDocTypeGti
77 LEFT JOIN m_warehouse_lead_time C ON A.warehouse_from_id = C.warehouse_from_id AND A.warehouse_to_id = C.warehouse_to_id
78 LEFT JOIN tt_stock_opname_outlet D ON A.warehouse_to_id = D.warehouse_id AND session_id = pSessionId
79 where A.doc_type_id = vDocTypeGto
80 AND SUBSTR(B.create_datetime, 1, 8) > to_char(to_date(A.doc_date, 'YYYYMMDD') + COALESCE(lead_time, '1 day')::INTERVAL, 'YYYYMMDD')
81 AND SUBSTR(B.create_datetime, 1, 8) < D.doc_date;
82
83 RAISE NOTICE '3';
84 -- 3. berdasarkan data GTO no. 3, cari dan tampung ke table temp data di table in_log_product_balance_stock
85 INSERT INTO tt_exclude_log_product_balance_stock_for_stok_awal_bulan (session_id, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id, doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty)
86 SELECT pSessionId, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id,
87 doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty
88 FROM in_log_product_balance_stock A
89 where doc_type_id = vDocTypeGti
90 and exists (select 1 FROM tt_exclude_gto_for_stok_awal_bulan Z where A.ref_id = Z.gti_id and session_id = pSessionId);
91
92 RAISE NOTICE '4';
93 -- 4. cari data in_log_product_balance_stock untuk penjualan, retur dan verifikasi yang terjadi sebelum stock opaname karena akan di exclude juga
94 -- Transaksi verifikasi di exclude karena transaksi verfikasi adalah
95 -- Transaksi penjualan atau retur yang sudah lewat, dan seharusnya sudah terhitung waktu stock opname tetapi baru diinput di tahap verifikasi
96 INSERT INTO tt_exclude_log_product_balance_stock_for_stok_awal_bulan (session_id, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id, doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty)
97 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
98 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
99 FROM in_log_product_balance_stock A
100 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
101 where doc_type_id = vDocTypeMobileverifikasi;
102
103 RAISE NOTICE '5';
104 -- Transaksi penjualan yang di exclude karena tanggalnya dibawah tanggal stock opname atau penjualan yang terjadi sebelum stock opname tetapi baru diinput setelah stock opname
105 INSERT INTO tt_exclude_log_product_balance_stock_for_stok_awal_bulan (session_id, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id, doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty)
106 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
107 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
108 FROM in_log_product_balance_stock A
109 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
110 where doc_type_id = vDocTypeMobileSales AND A.doc_date < B.doc_date
111 UNION
112 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
113 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
114 FROM in_log_product_balance_stock A
115 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
116 where doc_type_id = vDocTypeMobileSales AND A.doc_date < B.doc_date AND SUBSTR(A.create_datetime,1,8) > B.doc_date;
117
118 RAISE NOTICE '6';
119 -- Transaksi retur yang di exclude karena tanggalnya dibawah tanggal stock opname atau retur yang terjadi sebelum stock opname tetapi baru diinput setelah stock opname
120 INSERT INTO tt_exclude_log_product_balance_stock_for_stok_awal_bulan (session_id, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id, doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty)
121 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
122 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
123 FROM in_log_product_balance_stock A
124 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
125 where doc_type_id = vDocTypeMobileRetur AND A.doc_date < B.doc_date
126 UNION
127 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
128 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
129 FROM in_log_product_balance_stock A
130 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
131 where doc_type_id = vDocTypeMobileRetur AND A.doc_date < B.doc_date AND SUBSTR(A.create_datetime,1,8) > B.doc_date;
132
133 RAISE NOTICE '7';
134 -- exclude in_log_product_balance_stock untuk doc_type adj_qty dari stock opname.
135 INSERT INTO tt_exclude_log_product_balance_stock_for_stok_awal_bulan (session_id, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id, doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty)
136 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
137 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
138 FROM in_log_product_balance_stock A
139 INNER JOIN tt_stock_opname_outlet Y
140 ON A.ref_id = Y.inventory_id
141 WHERE A.doc_type_id = vDocTypeAdjStock
142 AND Y.session_id = pSessionId;
143
144
145 RAISE NOTICE '8';
146 -- 4. hitung stock awal bulan dan di tampung ke table tt_process_stok_awal_bulan (flg_stock_opname = 'Y')
147 -- notes: table utama yang digunakan adalah in_product_balance_stock
148 -- untuk product yang tidak ada di stock opname, maka akan dianggap product tersebut sudah habis atau di-nol-kan
149 -- untuk product yang tidak ada di stock opname, tetapi ada transaksi stock nya, maka stock yang digunakan adalah total transaksi stock
150 -- mutasi stock yang diperhitungkan hanya transaksi yang terjadi setelah stock opname sampai 31 Dec 2016
151 -- qty_akhir adalah qty_opname ditambah dengan total_transaksi_stock
152 WITH transaksi_product AS (
153 SELECT product_id, A.warehouse_id, SUM(qty) AS total_qty_transaksi FROM in_log_product_balance_stock A
154 WHERE not exists (
155 SELECT 1 FROM tt_exclude_log_product_balance_stock_for_stok_awal_bulan Z
156 where A.log_product_balance_stock_id = Z.log_product_balance_stock_id
157 AND A.product_id = Z.product_id
158 AND Z.session_id = pSessionId
159 )
160 AND doc_date < vEndDate
161 group by product_id, A.warehouse_id
162 )
163 INSERT INTO tt_process_stok_awal_bulan (session_id, date_year_month, tenant_id, ou_id, sub_ou_id, warehouse_id, product_id, product_balance_id, qty_opname, total_qty_transaksi, qty_akhir, flg_stock_opname)
164 SELECT pSessionId, vDateYearMonth, pTenantId, vOuIdGrazia, F.ou_id, A.warehouse_id, A.product_id, A.product_balance_id, COALESCE(D.qty_opname, 0), COALESCE(B.total_qty_transaksi, 0), COALESCE(D.qty_opname, 0) + COALESCE(B.total_qty_transaksi, 0) AS qty_akhir, 'Y'
165 FROM in_product_balance_stock A
166 LEFT JOIN transaksi_product B ON A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
167 INNER JOIN tt_stock_opname_outlet C ON A.warehouse_id = C.warehouse_id
168 LEFT JOIN i_mobile_pos_stock_opname_item D ON C.mobile_pos_stock_opname_id = D.mobile_pos_stock_opname_id AND A.product_id = D.product_id
169 LEFT JOIN in_product_balance E ON D.product_id = E.product_id
170 LEFT JOIN i_outlet F ON C.outlet_id = F.outlet_id
171 where C.session_id = pSessionId;
172
173 RAISE NOTICE '9';
174 -- INSERT ke table untuk warehouse yang tidak ada data stock opname termasuk (flg_stock_opname = 'N')
175 INSERT INTO tt_process_stok_awal_bulan (session_id, date_year_month, tenant_id, ou_id, sub_ou_id, warehouse_id, product_id, product_balance_id, qty_opname, total_qty_transaksi, qty_akhir, flg_stock_opname)
176 SELECT pSessionId, vDateYearMonth, pTenantId, vOuIdGrazia, B.ou_id, A.warehouse_id, A.product_id, A.product_balance_id, 0, 0, 0, 'N' FROM in_product_balance_stock A
177 inner JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
178 where not exists (SELECT 1 FROM tt_process_stok_awal_bulan Z WHERE A.warehouse_id = Z.warehouse_id AND A.product_id = Z.product_id);
179
180 RAISE NOTICE '10';
181 -- UPDATE qty_opname untuk warehouse dan product yang ada qty awal bulan 201608
182 UPDATE tt_process_stok_awal_bulan A
183 SET qty_opname = B.qty
184 FROM in_summary_monthly_qty B
185 where A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
186 and flg_stock_opname = 'N' and session_id = pSessionId;
187
188 RAISE NOTICE '11';
189 -- UPDATE total_qty_transaksi untuk warehouse dan product yang ada qty awal bulan 201608
190 WITH transaksi_stock AS (
191 SELECT A.warehouse_id, A.product_id, SUM(qty) AS total_qty_transaksi
192 FROM in_log_product_balance_stock A
193 INNER JOIN tt_process_stok_awal_bulan B
194 ON A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id AND flg_stock_opname = 'N' and session_id = pSessionId
195 WHERE doc_date BETWEEN vStartDate AND vEndDate
196 GROUP BY A.warehouse_id, A.product_id
197 )
198 UPDATE tt_process_stok_awal_bulan A
199 SET total_qty_transaksi = C.total_qty_transaksi
200 FROM in_summary_monthly_qty B
201 INNER JOIN transaksi_stock C ON B.warehouse_id = C.warehouse_id AND B.product_id = C.product_id
202 where A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
203 and flg_stock_opname = 'N' and session_id = pSessionId;
204
205 RAISE NOTICE '12';
206 -- UPDATE total_qty_transaksi untuk warehouse dan product yang tidak ada qty awal bulan 201608
207 WITH transaksi_stock AS (
208 SELECT warehouse_id, product_id, SUM(qty) AS total_qty_transaksi
209 FROM in_log_product_balance_stock A
210 where doc_date < vEndDate
211 and not exists (
212 SELECT 1 FROM in_summary_monthly_qty Z
213 WHERE A.warehouse_id = Z.warehouse_id AND A.product_id = Z.product_id
214 )
215 GROUP BY warehouse_id, product_id
216 )
217 UPDATE tt_process_stok_awal_bulan A
218 SET total_qty_transaksi = B.total_qty_transaksi
219 FROM transaksi_stock B
220 where A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
221 and A.flg_stock_opname = 'N' and A.session_id = pSessionId;
222
223 RAISE NOTICE '13';
224 -- UPDATE qty_akhir untuk data tt_process_stok_awal_bulan yang flg_stock_opname = 'N' (qty_akhir = qty_opname + total_qty_transaksi)
225 UPDATE tt_process_stok_awal_bulan
226 SET qty_akhir = qty_opname + total_qty_transaksi
227 WHERE flg_stock_opname = 'N' and session_id = pSessionId;
228
229 RAISE NOTICE '14';
230 -- 4.INSERT ke table in_summary_monthly_qty berdasarkan perhitungan di table tt_process_stok_awal_bulan
231 INSERT INTO in_summary_monthly_qty
232 (date_year_month, tenant_id, ou_id, sub_ou_id, doc_type_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
233 version, create_datetime, create_user_id, update_datetime, update_user_id)
234 SELECT date_year_month, A.tenant_id, A.ou_id, sub_ou_id, vEmptyId, B.warehouse_id, product_id, product_balance_id, vProductStatusGood, vBaseUomId, qty_akhir,
235 0, pDatetime, pUserId, pDatetime, pUserId
236 FROM tt_process_stok_awal_bulan A
237 LEFT JOIN i_outlet B ON A.sub_ou_id = B.ou_id
238 where session_id = pSessionId;
239
240
241 ELSE
242 RAISE EXCEPTION 'PROSES GENERATE STOCK AWAL BULAN SUDAH PERNAH DIJALANKAN';
243
244 END IF;
245
246END;
247$BODY$
248LANGUAGE plpgsql VOLATILE
249COST 100;