· 9 years ago · Jan 04, 2017, 04: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 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
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 -- 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
69 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)
70 SELECT pSessionId,
71 A.inventory_id AS gto_id, A.doc_no AS gto_no, A.doc_date AS gto_date,
72 B.inventory_id AS gti_id, B.doc_no AS gti_no, B.doc_date AS gti_date,
73 A.warehouse_from_id, A.warehouse_to_id
74 FROM in_inventory A
75 INNER JOIN in_inventory B ON A.inventory_id = B.ref_id AND B.doc_type_id = vDocTypeGti
76 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
77 LEFT JOIN tt_stock_opname_outlet D ON A.warehouse_to_id = D.warehouse_id AND session_id = pSessionId
78 where A.doc_type_id = vDocTypeGto
79 AND SUBSTR(B.create_datetime, 1, 8) > to_char(to_date(A.doc_date, 'YYYYMMDD') + COALESCE(lead_time, '1 day')::INTERVAL, 'YYYYMMDD')
80 AND SUBSTR(B.create_datetime, 1, 8) < D.doc_date;
81
82 -- 3. berdasarkan data GTO no. 3, cari dan tampung ke table temp data di table in_log_product_balance_stock
83 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)
84 SELECT pSessionId, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id,
85 doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty
86 FROM in_log_product_balance_stock A
87 where doc_type_id = vDocTypeGti
88 and exists (select 1 FROM tt_exclude_gto_for_stok_awal_bulan Z where A.ref_id = Z.gti_id and session_id = pSessionId);
89
90 -- 4. cari data in_log_product_balance_stock untuk penjualan, retur dan verifikasi yang terjadi sebelum stock opaname karena akan di exclude juga
91 -- Transaksi verifikasi di exclude karena transaksi verfikasi adalah
92 -- Transaksi penjualan atau retur yang sudah lewat, dan seharusnya sudah terhitung waktu stock opname tetapi baru diinput di tahap verifikasi
93 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)
94 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
95 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
96 FROM in_log_product_balance_stock A
97 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
98 where doc_type_id = vDocTypeMobileverifikasi;
99
100 -- Transaksi penjualan yang di exclude karena tanggalnya dibawah tanggal stock opname atau penjualan yang terjadi sebelum stock opname tetapi baru diinput setelah stock opname
101 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)
102 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
103 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
104 FROM in_log_product_balance_stock A
105 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
106 where doc_type_id = vDocTypeMobileSales AND A.doc_date < B.doc_date
107 UNION
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 = vDocTypeMobileSales AND A.doc_date < B.doc_date AND SUBSTR(A.create_datetime,1,8) > B.doc_date;
113
114 -- Transaksi retur yang di exclude karena tanggalnya dibawah tanggal stock opname atau retur yang terjadi sebelum stock opname tetapi baru diinput setelah stock opname
115 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)
116 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
117 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
118 FROM in_log_product_balance_stock A
119 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
120 where doc_type_id = vDocTypeMobileRetur AND A.doc_date < B.doc_date
121 UNION
122 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
123 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
124 FROM in_log_product_balance_stock A
125 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
126 where doc_type_id = vDocTypeMobileRetur AND A.doc_date < B.doc_date AND SUBSTR(A.create_datetime,1,8) > B.doc_date;
127
128 -- 4. hitung stock awal bulan dan di tampung ke table tt_process_stok_awal_bulan (flg_stock_opname = 'Y')
129 -- notes: table utama yang digunakan adalah in_product_balance_stock
130 -- untuk product yang tidak ada di stock opname, maka akan dianggap product tersebut sudah habis atau di-nol-kan
131 -- untuk product yang tidak ada di stock opname, tetapi ada transaksi stock nya, maka stock yang digunakan adalah total transaksi stock
132 -- mutasi stock yang diperhitungkan hanya transaksi yang terjadi setelah stock opname sampai 31 Dec 2016
133 -- qty_akhir adalah qty_opname ditambah dengan total_transaksi_stock
134 WITH transaksi_product AS (
135 SELECT product_id, A.warehouse_id, SUM(qty) FROM in_log_product_balance_stock A
136 INNER JOIN tt_stock_opname_outlet B
137 ON A.warehouse_id = B.warehouse_id
138 AND B.session_id = pSessionId
139 AND A.doc_date >= B.doc_date -- tanggal in_log yang diambil hanya yang >= tanggal stock opname
140 WHERE not exists (
141 SELECT 1 FROM tt_exclude_log_product_balance_stock_for_stok_awal_bulan Z
142 where A.log_product_balance_stock_id = Z.log_product_balance_stock_id
143 AND A.product_id = Z.product_id
144 AND Z.session_id = pSessionId
145 )
146 AND not exists (
147 -- meng-exclude in_log_product_balance_stock untuk doc_type adj_qty dari stock opname.
148 SELECT 1 FROM tt_stock_opname_outlet Y
149 where A.ref_id = Y.inventory_id
150 AND A.doc_type_id = vDocTypeAdjStock
151 AND Y.session_id = pSessionId
152 )
153 AND doc_date < vEndDate
154 group by product_id, warehouse_id
155 )
156 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)
157 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'
158 FROM in_product_balance_stock A
159 LEFT JOIN transaksi_product B ON A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
160 INNER JOIN tt_stock_opname_outlet C ON A.warehouse_id = C.warehouse_id
161 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
162 LEFT JOIN in_product_balance E ON D.product_id = E.product_id
163 LEFT JOIN i_outlet F ON C.outlet_id = F.outlet_id
164 where C.session_id = pSessionId;
165
166 -- INSERT ke table untuk warehouse yang tidak ada data stock opname termasuk (flg_stock_opname = 'N')
167 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)
168 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
169 inner JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
170 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);
171
172 -- UPDATE qty_opname untuk warehouse dan product yang ada qty awal bulan 201608
173 UPDATE tt_process_stok_awal_bulan A
174 SET qty_opname = B.qty
175 FROM in_summary_monthly_qty B
176 where A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
177 and flg_stock_opname = 'N' and session_id = pSessionId;
178
179
180 -- UPDATE total_qty_transaksi untuk warehouse dan product yang ada qty awal bulan 201608
181 WITH transaksi_stock AS (
182 SELECT warehouse_id, product_id, SUM(qty) AS total_qty_transaksi
183 FROM in_log_product_balance_stock
184 WHERE doc_date BETWEEN vStartDate AND vEndDate
185 GROUP BY warehouse_id, product_id
186 )
187 UPDATE tt_process_stok_awal_bulan A
188 SET total_qty_transaksi = C.total_qty_transaksi
189 FROM in_summary_monthly_qty B
190 INNER JOIN transaksi_stock C ON B.warehouse_id = C.warehouse_id AND B.product_id = C.product_id
191 where A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
192 and flg_stock_opname = 'N' and session_id = pSessionId;
193
194
195 -- UPDATE total_qty_transaksi untuk warehouse dan product yang tidak ada qty awal bulan 201608
196 WITH transaksi_stock AS (
197 SELECT warehouse_id, product_id, SUM(qty) AS total_qty_transaksi
198 FROM in_log_product_balance_stock
199 where doc_date < vEndDate
200 GROUP BY warehouse_id, product_id
201 )
202 UPDATE tt_process_stok_awal_bulan A
203 SET total_qty_transaksi = B.total_qty_transaksi
204 FROM transaksi_stock B
205 where A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
206 and A.flg_stock_opname = 'N' and A.session_id = pSessionId
207 and not exists (
208 SELECT 1 FROM in_summary_monthly_qty Z
209 WHERE A.warehouse_id = Z.warehouse_id AND A.product_id = Z.product_id
210 );
211
212 -- UPDATE qty_akhir untuk data tt_process_stok_awal_bulan yang flg_stock_opname = 'N' (qty_akhir = qty_opname + total_qty_transaksi)
213 UPDATE tt_process_stok_awal_bulan
214 SET qty_akhir = qty_opname + total_qty_transaksi
215 WHERE flg_stock_opname = 'N' and session_id = pSessionId;
216
217 -- 4.INSERT ke table in_summary_monthly_qty berdasarkan perhitungan di table tt_process_stok_awal_bulan
218 INSERT INTO in_summary_monthly_qty
219 (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,
220 version, create_datetime, create_user_id, update_datetime, update_user_id)
221 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,
222 0, pDatetime, pUserId, pDatetime, pUserId
223 FROM tt_process_stok_awal_bulan A
224 LEFT JOIN i_outlet B ON A.sub_ou_id = B.ou_id
225 where session_id = pSessionId;
226
227
228 ELSE
229 RAISE EXCEPTION 'PROSES GENERATE STOCK AWAL BULAN SUDAH PERNAH DIJALANKAN';
230
231 END IF;
232
233END;
234$BODY$
235LANGUAGE plpgsql VOLATILE
236COST 100;