· 9 years ago · Dec 29, 2016, 12:08 PM
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 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 -- qty_akhir adalah qty_opname ditambah dengan total_transaksi_stock
124 WITH transaksi_product AS (
125 SELECT product_id, warehouse_id, SUM(qty) AS total_qty_transaksi FROM in_log_product_balance_stock A
126 WHERE not exists (
127 SELECT 1 FROM tt_exclude_log_product_balance_stock_for_stok_awal_bulan Z
128 where A.log_product_balance_stock_id = Z.log_product_balance_stock_id
129 AND A.product_id = Z.product_id
130 AND Z.session_id = pSessionId
131 )
132 AND not exists (
133 -- meng-exclude in_log_product_balance_stock untuk doc_type adj_qty dari stock opname.
134 SELECT 1 FROM tt_stock_opname_outlet Y
135 where A.ref_id = Y.inventory_id
136 AND A.doc_type_id = vDocTypeAdjStock
137 AND Y.session_id = pSessionId
138 )
139 group by product_id, warehouse_id
140 )
141 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)
142 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
143 FROM in_product_balance_stock A
144 LEFT JOIN transaksi_product B ON A.warehouse_id = B.warehouse_id AND A.product_id = B.product_id
145 INNER JOIN tt_stock_opname_outlet C ON A.warehouse_id = C.warehouse_id
146 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
147 LEFT JOIN in_product_balance E ON D.product_id = E.product_id
148 LEFT JOIN i_outlet F ON C.outlet_id = F.outlet_id
149 where C.session_id = pSessionId;
150
151 -- 4.INSERT ke table in_summary_monthly_qty berdasarkan perhitungan di table tt_process_stok_awal_bulan
152 INSERT INTO in_summary_monthly_qty
153 (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,
154 version, create_datetime, create_user_id, update_datetime, update_user_id)
155 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,
156 0, pDatetime, pUserId, pDatetime, pUserId
157 FROM tt_process_stok_awal_bulan A
158 LEFT JOIN i_outlet B ON A.sub_ou_id = B.ou_id
159 where session_id = pSessionId;
160
161
162 ELSE
163 RAISE EXCEPTION 'PROSES GENERATE STOCK AWAL BULAN SUDAH PERNAH DIJALANKAN';
164
165 END IF;
166
167END;
168$BODY$
169LANGUAGE plpgsql VOLATILE
170COST 100;