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