· 9 years ago · Jan 03, 2017, 04:14 AM
1CREATE OR REPLACE FUNCTION f_process_stock_awal_bulan_grazia(bigint, character varying, character varying, character varying, bigint)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pTenantId ALIAS FOR $1;
6 pDateYearMonth ALIAS FOR $2;
7 pSessionId ALIAS FOR $3;
8 pDatetime ALIAS FOR $4;
9 pUserId ALIAS FOR $5;
10
11 vDocTypeGto bigint := 533;
12 vDocTypeGti bigint := 535;
13 vDocTypeAdjStock bigint := 521;
14 vDocTypeMobileSales bigint := 407;
15 vDocTypeMobileRetur bigint := 408;
16 vDocTypeMobileverifikasi bigint := 432;
17
18 vOuIdGrazia bigint;
19 vProductStatusGood character varying(5);
20 vCount bigint;
21 vBaseUomId bigint;
22
23 vEmptyValue character varying := ' ';
24 vEmptyId bigint := -99;
25
26BEGIN
27 /*
28 Function ini akan membuatkan stock awal bulan untuk date year month yang diinput
29 dan menggunakan data stock opname terbaru yang sudah di proses dengan memperhitungkan transaksi stock dari tanggal setelah stock opname
30
31 0. Cek apakah function ini sudah pernah dijalankan
32 1. Cari dan tampung ke table temp stock per outlet berdasarkan stock opname
33 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
34 3. berdasarkan data GTO no. 3, cari dan tampung ke table temp data di table in_log_product_balance_stock
35 4. cari data in_log_product_balance_stock untuk penjualan, retur dan verifikasi yang terjadi sebelum stock opaname karena akan di exclude juga
36 5. hitung stock awal bulan
37 */
38
39 SELECT uom_id INTO vBaseUomId
40 FROM m_uom where uom_code = 'PCS';
41
42 SELECT f_get_value_system_config_by_param_code(pTenantId, 'OU.GRAZIA.INDONESIA') INTO vOuIdGrazia;
43 SELECT f_get_value_system_config_by_param_code(pTenantId, 'PRODUCT.STATUS.GOOD') INTO vProductStatusGood;
44
45 -- 0. Cek apakah function ini sudah pernah dijalankan
46 SELECT COUNT(1) INTO vCount
47 FROM in_summary_monthly_qty
48 WHERE date_year_month = pDateYearMonth;
49
50 -- Proses dijalankan jika pada date_year_month yang diinput belum pernah dijalankan.
51 IF vCount = 0 THEN
52
53 -- 1. Cari dan tampung ke table temp stock per outlet berdasarkan stock opname terbaru yang sudah pernah di proses
54 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)
55 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
56 INNER JOIN in_inventory_adjust_stock_ext B ON A.mobile_pos_stock_opname_id = B.ref_id
57 group by mobile_pos_stock_opname_id, inventory_id, tenant_id, outlet_id, warehouse_id, user_process_id;
58
59 -- 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
60 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)
61 SELECT pSessionId,
62 A.inventory_id AS gto_id, A.doc_no AS gto_no, A.doc_date AS gto_date,
63 B.inventory_id AS gti_id, B.doc_no AS gti_no, B.doc_date AS gti_date,
64 A.warehouse_from_id, A.warehouse_to_id
65 FROM in_inventory A
66 INNER JOIN in_inventory B ON A.inventory_id = B.ref_id AND B.doc_type_id = vDocTypeGti
67 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
68 LEFT JOIN tt_stock_opname_outlet D ON A.warehouse_to_id = D.warehouse_id AND session_id = pSessionId
69 where A.doc_type_id = vDocTypeGto
70 AND SUBSTR(B.create_datetime, 1, 8) > to_char(to_date(A.doc_date, 'YYYYMMDD') + COALESCE(lead_time, '1 day')::INTERVAL, 'YYYYMMDD')
71 AND SUBSTR(B.create_datetime, 1, 8) < D.doc_date;
72
73 -- 3. berdasarkan data GTO no. 3, cari dan tampung ke table temp data di table in_log_product_balance_stock
74 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)
75 SELECT pSessionId, log_product_balance_stock_id, tenant_id, doc_type_id, ref_id,
76 doc_no, doc_date, product_id, product_balance_id, warehouse_id, qty
77 FROM in_log_product_balance_stock A
78 where doc_type_id = vDocTypeGti
79 and exists (select 1 FROM tt_exclude_gto_for_stok_awal_bulan Z where A.ref_id = Z.gti_id and session_id = pSessionId);
80
81 -- 4. cari data in_log_product_balance_stock untuk penjualan, retur dan verifikasi yang terjadi sebelum stock opaname karena akan di exclude juga
82 -- Transaksi verifikasi di exclude karena transaksi verfikasi adalah
83 -- Transaksi penjualan atau retur yang sudah lewat, dan seharusnya sudah terhitung waktu stock opname tetapi baru diinput di tahap verifikasi
84 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)
85 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
86 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
87 FROM in_log_product_balance_stock A
88 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
89 where doc_type_id = vDocTypeMobileverifikasi;
90
91 -- Transaksi penjualan yang di exclude karena tanggalnya dibawah tanggal stock opname atau penjualan yang terjadi sebelum stock opname tetapi baru diinput setelah stock opname
92 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)
93 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
94 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
95 FROM in_log_product_balance_stock A
96 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
97 where doc_type_id = vDocTypeMobileSales AND A.doc_date < B.doc_date
98 UNION
99 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
100 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
101 FROM in_log_product_balance_stock A
102 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
103 where doc_type_id = vDocTypeMobileSales AND A.doc_date < B.doc_date AND SUBSTR(A.create_datetime,1,8) > B.doc_date;
104
105 -- Transaksi retur yang di exclude karena tanggalnya dibawah tanggal stock opname atau retur yang terjadi sebelum stock opname tetapi baru diinput setelah stock opname
106 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)
107 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
108 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
109 FROM in_log_product_balance_stock A
110 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
111 where doc_type_id = vDocTypeMobileRetur AND A.doc_date < B.doc_date
112 UNION
113 SELECT pSessionId, log_product_balance_stock_id, A.tenant_id, doc_type_id, ref_id,
114 doc_no, A.doc_date, product_id, product_balance_id, A.warehouse_id, qty
115 FROM in_log_product_balance_stock A
116 inner join tt_stock_opname_outlet B ON A.warehouse_id = B.warehouse_id
117 where doc_type_id = vDocTypeMobileRetur AND A.doc_date < B.doc_date AND SUBSTR(A.create_datetime,1,8) > B.doc_date;
118
119 -- 4. hitung stock awal bulan dan di tampung ke table tt_process_stok_awal_bulan
120 -- notes: table utama yang digunakan adalah in_product_balance_stock
121 -- untuk product yang tidak ada di stock opname, maka akan dianggap product tersebut sudah habis atau di-nol-kan
122 -- untuk product yang tidak ada di stock opname, tetapi ada transaksi stock nya, maka stock yang digunakan adalah total transaksi stock
123 -- mutasi stock yang diperhitungkan hanya transaksi yang terjadi setelah stock opname
124 -- qty_akhir adalah qty_opname ditambah dengan total_transaksi_stock
125 WITH transaksi_product AS (
126 SELECT product_id, A.warehouse_id, SUM(qty) FROM in_log_product_balance_stock A
127 INNER JOIN tt_stock_opname_outlet B
128 ON A.warehouse_id = B.warehouse_id
129 AND B.session_id = pSessionId
130 AND A.doc_date >= B.doc_date -- tanggal in_log yang diambil hanya yang >= tanggal stock opname
131 WHERE not exists (
132 SELECT 1 FROM tt_exclude_log_product_balance_stock_for_stok_awal_bulan Z
133 where A.log_product_balance_stock_id = Z.log_product_balance_stock_id
134 AND A.product_id = Z.product_id
135 AND Z.session_id = pSessionId
136 )
137 AND not exists (
138 -- meng-exclude in_log_product_balance_stock untuk doc_type adj_qty dari stock opname.
139 SELECT 1 FROM tt_stock_opname_outlet Y
140 where A.ref_id = Y.inventory_id
141 AND A.doc_type_id = vDocTypeAdjStock
142 AND Y.session_id = pSessionId
143 )
144 group by product_id, warehouse_id
145 )
146 INSERT INTO tt_process_stok_awal_bulan (session_id, date_year_month, tenant_id, ou_id, sub_ou_id, product_id, product_balance_id, qty_opname, total_qty_transaksi, qty_akhir)
147 SELECT pSessionId, pDateYearMonth, pTenantId, vOuIdGrazia, F.ou_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
148 FROM in_product_balance_stock A
149 LEFT JOIN transaksi_product B ON A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
150 INNER JOIN tt_stock_opname_outlet C ON A.warehouse_id = C.warehouse_id
151 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
152 LEFT JOIN in_product_balance E ON D.product_id = E.product_id
153 LEFT JOIN i_outlet F ON C.outlet_id = F.outlet_id
154 where C.session_id = pSessionId;
155
156 -- 4.INSERT ke table in_summary_monthly_qty berdasarkan perhitungan di table tt_process_stok_awal_bulan
157 INSERT INTO in_summary_monthly_qty
158 (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,
159 version, create_datetime, create_user_id, update_datetime, update_user_id)
160 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,
161 0, pDatetime, pUserId, pDatetime, pUserId
162 FROM tt_process_stok_awal_bulan A
163 LEFT JOIN i_outlet B ON A.sub_ou_id = B.ou_id
164 where session_id = pSessionId;
165
166
167 ELSE
168 RAISE EXCEPTION 'PROSES GENERATE STOCK AWAL BULAN SUDAH PERNAH DIJALANKAN';
169
170 END IF;
171
172END;
173$BODY$
174LANGUAGE plpgsql VOLATILE
175COST 100;