· 7 years ago · Sep 20, 2018, 03:46 AM
1-- Function: f_hitung_stok_awal_katalok(character varying, bigint, bigint, character varying, character varying)
2
3-- DROP FUNCTION f_hitung_stok_awal_katalok(character varying, bigint, bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION f_hitung_stok_awal_katalok(character varying, bigint, bigint, character varying, character varying)
6 RETURNS void AS
7$BODY$
8DECLARE
9 pSessionId alias for $1;
10 pTenantId alias for $2;
11 pUserId alias for $3;
12 pDatetime alias for $4;
13 pCatalogCode alias for $5;
14
15 vCatalogId bigint;
16 vDocTypeRg bigint;
17 vCatalogStartDate character varying(8);
18 vProcessDate character varying(8);
19 vLastCloseMonth character varying;
20 vStartDate character varying;
21 vYes character varying;
22 vNo character varying;
23
24BEGIN
25 vDocTypeRg := 111;
26 vYes :='Y';
27 vNo :='N';
28
29 DELETE FROM tt_m_product_catalog_stock_awal WHERE session_id = pSessionId;
30
31 SELECT COALESCE(catalog_id, -99) INTO vCatalogId
32 FROM m_catalog WHERE catalog_code = pCatalogCode;
33 SELECT start_date INTO vCatalogStartDate
34 FROM m_catalog WHERE catalog_code = pCatalogCode;
35
36 IF vCatalogId = -99 THEN
37 RAISE EXCEPTION 'Catalog code not valid';
38 END IF;
39
40-- Mengambil tanggal terakhir tutup bulan
41
42 SELECT start_date INTO vStartDate
43 FROM m_catalog
44 WHERE catalog_id = vCatalogId;
45
46 SELECT date_year_month into vLastCloseMonth
47 FROM m_admin_process_ledger WHERE
48 ledger_code='INV'
49 AND status_ledger='1'
50 AND flag_process ='Y'
51 AND date_year_month <= vStartDate
52 ORDER BY date_year_month DESC LIMIT 1;
53
54 --SELECT to_char(to_date(vLastCloseMonth,'YYYYMM') + interval '1 month','YYYYMM') into vProcessDate;
55 vProcessDate:=vLastCloseMonth;
56
57 -- Masukkan ke table temporer untuk data Produk Katalog di catalog bersangkutan
58 INSERT INTO tt_m_product_catalog_stock_awal (session_id,
59 product_catalog_id,
60 product_id,
61 product_catalog_code,
62 flg_lanjut_catalog,
63 product_catalog_id_sebelumnya,
64 qty_stok_awal)
65 SELECT pSessionId, product_catalog_id, B.product_id, product_catalog_code, flg_lanjut_catalog, f_get_product_catalog_id_before(product_catalog_id), 0
66 FROM m_product_catalog_preparation A, m_product B
67 WHERE catalog_code = pCatalogCode
68 AND A.flg_cancelled =vNo
69 AND A.product_code = B.product_code;
70
71 ANALYZE tt_m_product_catalog_stock_awal;
72
73
74 -- Update data stok awal Katalok berdasarkan Product ID consign
75 WITH prod_kat AS (
76 SELECT X.product_id
77 FROM m_product_catalog X
78 JOIN m_product_custom Z ON X.product_id = Z.product_id
79 WHERE catalog_id = vCatalogId
80 AND Z.flg_buy_konsinyasi = vYes
81 ), stok_awal_mutasi AS (
82 SELECT product_id, SUM(qty) AS qty_awal_cons,0 AS qty_mutasi,0 AS qty_mutasi_cons
83 FROM prod_kat INNER JOIN in_summary_monthly_qty_consignment USING(product_id)
84 WHERE date_year_month = SUBSTR(vProcessDate,1,6)
85 GROUP BY product_id
86 UNION
87 SELECT product_id, 0 AS qty_awal_cons,SUM(qty) AS qty_mutasi,0
88 FROM prod_kat INNER JOIN in_log_product_balance_stock Z USING(product_id)
89 WHERE doc_date >= vProcessDate and doc_date < vCatalogStartDate
90 AND NOT EXISTS (SELECT 1 FROM pu_po_rg_generated P WHERE P.doc_type_id = vDocTypeRg and Z.ref_id = P.ref_id AND Z.doc_type_id= P.doc_type_id)
91 GROUP BY product_id
92 UNION
93 SELECT product_id,0 AS qty_awal_cons,0 AS qty_mutasi, sum(qty) AS qty_mutasi_cons
94 FROM prod_kat INNER JOIN in_log_product_consignment_balance_stock USING(product_id)
95 WHERE doc_date >= vProcessDate AND doc_date < vCatalogStartDate
96 GROUP BY product_id
97 ),stock_summarized AS(
98 SELECT product_id,SUM(qty_awal_cons) AS qty_awal_cons,SUM(qty_mutasi) AS qty_mutasi,SUM(qty_mutasi_cons) AS qty_mutasi_cons
99 FROM stok_awal_mutasi
100 GROUP Y product_id
101 )
102 UPDATE tt_m_product_catalog_stock_awal Z
103 SET qty_stok_awal =
104 coalesce(B.qty_awal_cons, 0)+ coalesce(B.qty_mutasi, 0)+ coalesce(B.qty_mutasi_cons, 0),
105 qty_terima_sebelum_launching =0
106 FROM prod_kat A
107 LEFT JOIN stock_summarized B USING(product_id)
108 WHERE A.product_id = Z.product_id
109 AND Z.session_id = pSessionId;
110
111
112
113 -- Update data stok awal Katalok berdasarkan Product ID
114 WITH prod_kat AS (
115 SELECT X.product_id
116 FROM m_product_catalog X
117 JOIN m_product_custom Z ON X.product_id = Z.product_id
118 WHERE catalog_id = vCatalogId
119 AND Z.flg_buy_konsinyasi = vNo
120 ), stok_awal_mutasi AS (
121 SELECT product_id, SUM(qty) AS qty_awal, 0 AS qty_mutasi_cons, 0 AS qty_mutasi
122 FROM prod_kat INNER JOIN in_summary_monthly_qty USING(product_id)
123 WHERE date_year_month = SUBSTR(vProcessDate,1,6)
124 GROUP BY product_id
125 UNION
126 SELECT product_id, 0 AS qty_awal, SUM(qty) AS qty_mutasi_cons, 0 AS qty_mutasi
127 FROM prod_kat INNER JOIN in_log_product_consignment_balance_stock USING(product_id)
128 WHERE doc_date >= vProcessDate AND doc_date < vCatalogStartDate
129 GROUP BY product_id
130 UNION
131 SELECT product_id, 0 AS qty_awal, 0 AS qty_mutasi_cons, SUM(qty) AS qty_mutasi
132 FROM prod_kat INNER JOIN in_log_product_balance_stock Z USING(product_id)
133 WHERE doc_date >= vProcessDate and doc_date < vCatalogStartDate
134 AND NOT EXISTS (SELECT 1 FROM pu_po_rg_generated P WHERE P.doc_type_id = vDocTypeRg and Z.ref_id = P.ref_id AND Z.doc_type_id= P.doc_type_id)
135 GROUP BY product_id
136 ),stock_summarized AS(
137 SELECT product_id,SUM(qty_awal_cons) AS qty_awal_cons,SUM(qty_mutasi) AS qty_mutasi,SUM(qty_mutasi_cons) AS qty_mutasi_cons
138 FROM stok_awal_mutasi
139 GROUP Y product_id
140 )
141 UPDATE tt_m_product_catalog_stock_awal Z
142 SET qty_stok_awal =
143 coalesce(B.qty_awal, 0) + coalesce(B.qty_mutasi, 0)+ coalesce(B.qty_mutasi_cons, 0),
144 qty_terima_sebelum_launching =0
145 FROM prod_kat A
146 LEFT JOIN stock_summarized B USING (product_id)
147 WHERE A.product_id = Z.product_id
148 AND Z.session_id = pSessionId;
149
150 -- BACKUP m_product_catalog_stock_awal
151 INSERT INTO backup_stok_awal
152 (session_id, product_catalog_id, tenant_id, product_id, catalog_id, qty_awal,
153 create_datetime, create_user_id, update_datetime, update_user_id,
154 version, backup_datetime)
155 SELECT pSessionId, product_catalog_id, tenant_id, product_id, catalog_id, qty_awal,
156 create_datetime, create_user_id, update_datetime, update_user_id,
157 version, pDateTime
158 FROM m_product_catalog_stock_awal WHERE catalog_id = vCatalogId;
159
160 -- Hapus data m_product_catalog_stock_awal untuk catalog id bersangkutan
161 DELETE FROM m_product_catalog_stock_awal WHERE catalog_id = vCatalogId;
162
163 INSERT INTO m_product_catalog_stock_awal(
164 product_catalog_id, tenant_id, product_id, catalog_id,
165 qty_awal,
166 create_datetime, update_datetime, create_user_id, update_user_id,
167 version)
168 SELECT A.product_catalog_id, B.tenant_id, B.product_id, vCatalogId,
169 qty_stok_awal,
170 pDateTime, pDateTime, pUserId, pUserId, 0
171 FROM tt_m_product_catalog_stock_awal A, m_product_catalog B
172 WHERE session_id = pSessionId
173 AND A.product_catalog_id = B.product_catalog_id;
174
175 DELETE FROM tt_m_product_catalog_stock_awal WHERE session_id = pSessionId;
176
177END;
178$BODY$
179 LANGUAGE plpgsql VOLATILE
180 COST 100;
181/