· 8 years ago · Dec 18, 2017, 02:48 AM
1-- Function: f_generate_commsheet_supp_to_erp(bigint, bigint, character varying, character varying, character varying, character varying)
2
3-- DROP FUNCTION f_generate_commsheet_supp_to_erp(bigint, bigint, character varying, character varying, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION f_generate_commsheet_supp_to_erp(bigint, bigint, character varying, character varying, character varying, character varying)
6 RETURNS void AS
7$BODY$
8DECLARE
9 pTenantId ALIAS FOR $1;
10 pUserId ALIAS FOR $2;
11 pCatalogCode ALIAS FOR $3;
12 pDocNo ALIAS FOR $4;
13 pDocDate ALIAS FOR $5;
14 pDateTime ALIAS FOR $6;
15
16 vSessionId character varying(255);
17 vComSheetDocTypeId bigint;
18 vOuId bigint;
19 vNullValue bigint;
20 vNo character varying(1);
21 vYes character varying(1);
22 vPurchaseOfficerId bigint;
23 vWarehouseReceiveId bigint;
24 vCurrCodeIDR character varying(3);
25 vEmptyString character varying;
26 vSpaceValue character varying(1);
27 vStatusRelease character varying(1);
28 vWorkflowStatusApproved character varying(255);
29 vDefaultVersion bigint;
30 vMarginCatalog numeric;
31 vEmptyId bigint;
32 vRemark character varying(255);
33 vPoId bigint;
34 vMaxLineNo bigint;
35
36
37BEGIN
38
39 vDefaultVersion := 0;
40 vComSheetDocTypeId := 106;
41 vOuId := 14; -- default OU ID
42 vEmptyId := -99;
43 vNullValue := -99;
44 vNo := 'N';
45 vYes := 'Y';
46 vPurchaseOfficerId := 4175; -- Purchase Office MD Admin
47 vWarehouseReceiveId := 14; -- GUDANG TERIMA
48 vCurrCodeIDR := 'IDR';
49 vEmptyString := '';
50 vSpaceValue := ' ';
51 vStatusRelease := 'R';
52 vWorkflowStatusApproved := 'APPROVED';
53 vMarginCatalog := 5; -- 5%
54 vRemark := 'Commsheet generated by Integration supp to erp';
55 vMaxLineNo := 0;
56
57 DELETE FROM tt_m_product_catalog_preparation WHERE session_id = vSessionId;
58 DELETE FROM tt_pu_po WHERE session_id = vSessionId;
59 DELETE FROM tt_pu_po_item WHERE session_id = vSessionId;
60 DELETE FROM tt_pu_po_ext WHERE session_id = vSessionId;
61 DELETE FROM tt_pu_po_tax WHERE session_id = vSessionId;
62 DELETE FROM tt_pu_po_balance_item_consignment WHERE session_id = vSessionId;
63 DELETE FROM tt_pu_log_po_balance_item_consignment WHERE session_id = vSessionId;
64
65 ---------------------------------------------------------------------------------------
66 -- Generate vSessionId
67 ---------------------------------------------------------------------------------------
68
69 SELECT f_make_uid() INTO vSessionId;
70
71 ---------------------------------------------------------------------------------------
72 -- 1. Buat yang belum ada comsheet untuk yang flag_lanjut_catalog
73 -- Ambil semua yang belum ada comsheet, po_id -99 dan po_item_id -99
74 -- masukkan ke table temporer hilat juga ke table temporer tt_create_comsheet
75 ---------------------------------------------------------------------------------------
76 INSERT INTO tt_m_product_catalog_preparation(
77 session_id, product_catalog_id, supplier_code, brand_code, catalog_code,
78 flg_lanjut_catalog, remark, qty_commit, product_catalog_code,
79 product_code, flg_consign, supplier_price, cogs, catalog_price,
80 disc_member_percent, disc_promo_percent, price_after_disc, product_catalog_name,
81 product_catalog_desc, flg_discontinue, halaman_katalok, pemakaian_halaman,
82 po_id, po_item_id,
83 create_datetime, create_user_id, update_datetime,
84 update_user_id, version, margin_supplier_percentage, brand_id, supplier_id)
85 SELECT
86 vSessionId,A.product_catalog_id, A.supplier_code, C.brand_code, A.catalog_code,
87 A.flg_lanjut_catalog, A.remark, A.qty_commit, A.product_catalog_code,
88 A.product_code, A.flg_consign, A.supplier_price, A.cogs, A.catalog_price,
89 A.disc_member_percent, A.disc_promo_percent, A.price_after_disc, A.product_catalog_name,
90 A.product_catalog_desc, A.flg_discontinue, A.halaman_katalok, A.pemakaian_halaman,
91 A.po_id, A.po_item_id, A.create_datetime, A.create_user_id, A.update_datetime,
92 A.update_user_id, A.version, B.margin_supplier_percentage, C.brand_id, D.partner_id
93 FROM m_product_catalog_preparation A
94 INNER JOIN temp_data_integration_supp_to_erp B
95 ON A.product_catalog_code = B.product_catalog_code
96 AND A.brand_code = B.brand_code
97 AND A.catalog_code = B.catalog_code
98 INNER JOIN m_brand C ON A.brand_code = C.brand_code
99 INNER JOIN m_partner D ON A.supplier_code = D.partner_code
100 INNER JOIN m_catalog E ON A.catalog_code = E.catalog_code
101 WHERE flg_consign = vYes
102 AND po_id = vNullValue
103 AND po_item_id = vNullValue
104 AND B.doc_no = pDocNo;
105
106 ---------------------------------------------------------------------------------------
107 -- insert ke table temporer untuk data pu_po_item
108 ---------------------------------------------------------------------------------------
109 INSERT INTO tt_pu_po_item(
110 session_id, tenant_id, po_id, line_no, ref_doc_type_id,
111 ref_id, warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
112 flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
113 qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
114 discount_amount, gross_item_amount, nett_item_amount, tax_amount,
115 activity_gl_id, product_coa_id, ou_rc_id, eta, tolerance_rcv_qty,
116 remark, version, create_datetime, create_user_id, update_datetime,
117 update_user_id, segment_id, eta_day, flg_indent, supplier_price,
118 supplier_product_code, catalog_price, disc_member, disc_promo,
119 nett_price, margin_supplier_percentage, margin_supplier_calculate_amount,
120 margin_supplier_amount, margin_catalog_percentage, margin_catalog_calculate_amount,
121 margin_catalog_amount, product_catalog_code, product_catalog_name, brand_id, supplier_id)
122 SELECT vSessionId,
123 pTenantId, -99, ROW_NUMBER() OVER ( PARTITION BY pTenantId), vNullValue,
124 vNullValue, -99, B.product_id, vYes, vCurrCodeIDR, A.cogs::numeric,
125 vNo, vNullValue, vNullValue, 0, 0,
126 A.qty_commit::numeric, B.base_uom_id, A.qty_commit::numeric, B.base_uom_id, 0,
127 0, 0, 0, 0,
128 vNullValue, vNullValue, vNullValue, vSpaceValue, 0,
129 A.remark, vDefaultVersion, pDateTime, pUserId, pDateTime,
130 pUserId, vNullValue, 'ETADAY', vNo, A.supplier_price::numeric,
131 vSpaceValue,
132 A.catalog_price::numeric,
133 A.disc_member_percent::numeric,
134 A.disc_promo_percent::numeric,
135 A.price_after_disc::numeric,
136 A.margin_supplier_percentage::numeric,
137 A.cogs::numeric,
138 A.cogs::numeric,
139 vMarginCatalog,
140 ROUND(vMarginCatalog*0.01*supplier_price::numeric + supplier_price::numeric),
141 A.catalog_price::numeric,
142 product_catalog_code,
143 product_catalog_name,
144 A.brand_id, A.supplier_id
145 FROM tt_m_product_catalog_preparation A,
146 m_product B
147 WHERE A.session_id = vSessionId
148 AND A.product_code= B.product_code
149 AND A.po_id = -99
150 AND A.po_item_id = -99;
151
152 IF NOT EXISTS (SELECT 1 FROM pu_po WHERE doc_no = pDocNo) THEN
153 ---------------------------------------------------------------------------------------
154 -- Update po_id di tt_pu_po_item
155 ---------------------------------------------------------------------------------------
156 WITH data_po AS (
157 SELECT nextval('pu_po_seq') as po_id, supplier_id, brand_id FROM tt_pu_po_item
158 WHERE session_id = vSessionId
159 GROUP BY supplier_id, brand_id
160 )
161 UPDATE tt_pu_po_item Z
162 SET po_id = B.po_id
163 FROM data_po B
164 WHERE Z.session_id = vSessionId
165 AND Z.supplier_id = B.supplier_id
166 AND Z.brand_id = B.brand_id;
167
168 ---------------------------------------------------------------------------------------
169 -- insert ke table temporer untuk data pu_po yang baru
170 ---------------------------------------------------------------------------------------
171 INSERT INTO tt_pu_po(
172 session_id, po_id, tenant_id, doc_type_id, doc_no, doc_date,
173 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
174 partner_id, purchaser_id, warehouse_id, flg_delivery, curr_code,
175 add_discount_percentage, add_discount_amount, top_code, status_doc,
176 workflow_status, version, create_datetime, create_user_id, update_datetime,
177 update_user_id, brand_id)
178 SELECT vSessionId,
179 A.po_id,
180 pTenantId, vComSheetDocTypeId,
181 pDocNo, pDocDate,
182 vOuId, '-', pDocDate, vNullValue, vNullValue, vRemark,
183 A.supplier_id, vPurchaseOfficerId, vWarehouseReceiveId, vYes, vCurrCodeIDR,
184 0, 0, vEmptyString, vStatusRelease,
185 vWorkflowStatusApproved,
186 vDefaultVersion, pDateTime, pUserId, pDateTime, pUserId, A.brand_id
187 FROM tt_pu_po_item A
188 WHERE session_id = vSessionId
189 GROUP BY A.po_id, A.supplier_id, A.brand_id;
190
191 ---------------------------------------------------------------------------------------
192 -- Insert into table tt_pu_po_ext
193 ---------------------------------------------------------------------------------------
194 INSERT INTO tt_pu_po_ext
195 (session_id, po_id, flg_buy_consignment,
196 "version", create_datetime, create_user_id, update_datetime, update_user_id,
197 brand_id, catalog_id, flg_continue_po)
198 SELECT vSessionId, A.po_id, vYes,
199 0, pDatetime, pUserId, pDatetime, pUserId,
200 A.brand_id, B.catalog_id, 'N'
201 FROM tt_pu_po A, m_catalog B
202 WHERE A.session_id = vSessionId
203 AND B.catalog_code = pCatalogCode;
204 ELSE
205 SELECT po_id INTO vPoId FROM pu_po WHERE doc_no = pDocNo;
206
207 ---------------------------------------------------------------------------------------
208 -- Update po_id di tt_pu_po_item
209 ---------------------------------------------------------------------------------------
210 WITH data_po AS (
211 SELECT vPoId as po_id, supplier_id, brand_id FROM tt_pu_po_item
212 WHERE session_id = vSessionId
213 GROUP BY supplier_id, brand_id
214 )
215 UPDATE tt_pu_po_item Z
216 SET po_id = B.po_id
217 FROM data_po B
218 WHERE Z.session_id = vSessionId
219 AND Z.supplier_id = B.supplier_id
220 AND Z.brand_id = B.brand_id;
221
222 SELECT max(B.line_no) INTO vMaxLineNo
223 FROM pu_po A
224 INNER JOIN pu_po_item B ON A.po_id = B.po_id
225 WHERE A.doc_no = pDocNo;
226
227 ---------------------------------------------------------------------------------------
228 -- Insert into table tt_pu_po_balance_item_consignment
229 ---------------------------------------------------------------------------------------
230 INSERT INTO tt_pu_po_balance_item_consignment
231 (session_id, po_item_id, tenant_id, ou_id,
232 qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
233 qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
234 tolerance_rcv_qty, status_item,
235 "version", create_datetime, create_user_id, update_datetime, update_user_id)
236 SELECT vSessionId, A.po_item_id, A.tenant_id, B.ou_id,
237 A.qty_po, 0, 0, 0, 0, A.po_uom_id,
238 A.qty_int, 0, 0, 0, 0, A.base_uom_id,
239 A.tolerance_rcv_qty, vStatusRelease,
240 0, pDatetime, pUserId, pDatetime, pUserId
241 FROM tt_pu_po_item A, pu_po B
242 WHERE A.session_id = vSessionId
243 AND A.po_id = B.po_id;
244
245 ---------------------------------------------------------------------------------------
246 -- hanya po item id yang benar ( dari max po item id ) dan kuantiti nya sudah diakumulasi
247 ---------------------------------------------------------------------------------------
248 INSERT INTO tt_pu_log_po_balance_item_consignment
249 (session_id, tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
250 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
251 version, create_datetime, create_user_id, update_datetime, update_user_id)
252 SELECT vSessionId, B.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
253 A.qty_po, A.po_uom_id, A.qty_po, A.po_uom_id, B.remark,
254 0, pDatetime, pUserId, pDatetime, pUserId
255 FROM tt_pu_po_item A, pu_po B
256 WHERE A.session_id = vSessionId AND
257 A.po_id = B.po_id;
258
259 END IF;
260 ---------------------------------------------------------------------------------------
261 -- Insert into table tt_pu_po_balance_item_consignment
262 ---------------------------------------------------------------------------------------
263 INSERT INTO tt_pu_po_balance_item_consignment
264 (session_id, po_item_id, tenant_id, ou_id,
265 qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
266 qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
267 tolerance_rcv_qty, status_item,
268 "version", create_datetime, create_user_id, update_datetime, update_user_id)
269 SELECT vSessionId, A.po_item_id, A.tenant_id, B.ou_id,
270 A.qty_po, 0, 0, 0, 0, A.po_uom_id,
271 A.qty_int, 0, 0, 0, 0, A.base_uom_id,
272 A.tolerance_rcv_qty, vStatusRelease,
273 0, pDatetime, pUserId, pDatetime, pUserId
274 FROM tt_pu_po_item A, tt_pu_po B
275 WHERE A.session_id = vSessionId AND
276 A.session_id = B.session_id AND
277 A.po_id = B.po_id;
278
279 ---------------------------------------------------------------------------------------
280 -- hanya po item id yang benar ( dari max po item id ) dan kuantiti nya sudah diakumulasi
281 ---------------------------------------------------------------------------------------
282 INSERT INTO tt_pu_log_po_balance_item_consignment
283 (session_id, tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
284 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
285 version, create_datetime, create_user_id, update_datetime, update_user_id)
286 SELECT vSessionId, B.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
287 A.qty_po, A.po_uom_id, A.qty_po, A.po_uom_id, B.remark,
288 0, pDatetime, pUserId, pDatetime, pUserId
289 FROM tt_pu_po_item A, tt_pu_po B
290 WHERE A.session_id = vSessionId AND
291 A.session_id = B.session_id AND
292 A.po_id = B.po_id;
293
294 -- INSERT ke table asli
295 -- tt_pu_po ke pu_po
296 -- tt_pu_po_item ke pu_po_item
297 -- tt_pu_po_ext ke pu_po_ext
298 -- tt_pu_po_balance_item_consignment ke pu_po_balance_item_consignment
299 -- tt_pu_log_po_balance_item_consignment ke pu_log_po_balance_item_consignment
300
301 ---------------------------------------------------------------------------------------
302 -- Insert into table pu_po
303 ---------------------------------------------------------------------------------------
304 INSERT INTO pu_po(
305 po_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id, ext_doc_no,
306 ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id, purchaser_id,
307 warehouse_id, flg_delivery, curr_code, add_discount_percentage,
308 add_discount_amount, top_code, status_doc, workflow_status, version,
309 create_datetime, create_user_id, update_datetime, update_user_id)
310 SELECT po_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id, ext_doc_no,
311 ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id, purchaser_id,
312 warehouse_id, flg_delivery, curr_code, add_discount_percentage,
313 add_discount_amount, top_code, status_doc, workflow_status, version,
314 create_datetime, create_user_id, update_datetime, update_user_id
315 FROM tt_pu_po
316 WHERE session_id = vSessionId;
317
318 ---------------------------------------------------------------------------------------
319 -- Insert into table pu_po_item
320 ---------------------------------------------------------------------------------------
321 INSERT INTO pu_po_item(
322 po_item_id, tenant_id, po_id, line_no, ref_doc_type_id, ref_id,
323 warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
324 flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
325 qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
326 discount_amount, gross_item_amount, nett_item_amount, tax_amount,
327 activity_gl_id, product_coa_id, ou_rc_id, eta, tolerance_rcv_qty,
328 remark, version, create_datetime, create_user_id, update_datetime,
329 update_user_id, segment_id, eta_day, flg_indent, supplier_price,
330 supplier_product_code, catalog_price, disc_member, disc_promo,
331 nett_price, margin_supplier_percentage, margin_supplier_calculate_amount,
332 margin_supplier_amount, margin_catalog_percentage, margin_catalog_calculate_amount,
333 margin_catalog_amount, product_catalog_code, product_catalog_name)
334 SELECT po_item_id, tenant_id, po_id, line_no+vMaxLineNo, ref_doc_type_id, ref_id,
335 warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
336 flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
337 qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
338 discount_amount, gross_item_amount, nett_item_amount, tax_amount,
339 activity_gl_id, product_coa_id, ou_rc_id, eta, tolerance_rcv_qty,
340 remark, version, create_datetime, create_user_id, update_datetime,
341 update_user_id, segment_id, eta_day, flg_indent, supplier_price,
342 supplier_product_code, catalog_price, disc_member, disc_promo,
343 nett_price, margin_supplier_percentage, margin_supplier_calculate_amount,
344 margin_supplier_amount, margin_catalog_percentage, margin_catalog_calculate_amount,
345 margin_catalog_amount, product_catalog_code, product_catalog_name
346 FROM tt_pu_po_item
347 WHERE session_id = vSessionId;
348
349 ---------------------------------------------------------------------------------------
350 -- Insert into table pu_po_ext
351 ---------------------------------------------------------------------------------------
352 INSERT INTO pu_po_ext(
353 po_id, flg_buy_consignment, version, create_datetime, create_user_id,
354 update_datetime, update_user_id, brand_id, catalog_id, flg_continue_po)
355 SELECT po_id, flg_buy_consignment, version, create_datetime, create_user_id,
356 update_datetime, update_user_id, brand_id, catalog_id, flg_continue_po
357 FROM tt_pu_po_ext
358 WHERE session_id = vSessionId;
359
360 ---------------------------------------------------------------------------------------
361 -- Insert into table pu_po_balance_item_consignment
362 ---------------------------------------------------------------------------------------
363 INSERT INTO pu_po_balance_item_consignment(
364 po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel,
365 qty_add, po_uom_id, qty_int_po, qty_int_rcv, qty_int_return,
366 qty_int_cancel, qty_int_add, base_uom_id, tolerance_rcv_qty,
367 status_item, version, create_datetime, create_user_id, update_datetime,
368 update_user_id, qty_int_sell, qty_sell, qty_move)
369 SELECT po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel,
370 qty_add, po_uom_id, qty_int_po, qty_int_rcv, qty_int_return,
371 qty_int_cancel, qty_int_add, base_uom_id, tolerance_rcv_qty,
372 status_item, version, create_datetime, create_user_id, update_datetime,
373 update_user_id, qty_int_sell, qty_sell, qty_move
374 FROM tt_pu_po_balance_item_consignment
375 WHERE session_id = vSessionId;
376
377 ---------------------------------------------------------------------------------------
378 -- Insert into table pu_log_po_balance_item_consignment
379 ---------------------------------------------------------------------------------------
380 INSERT INTO pu_log_po_balance_item_consignment(
381 log_po_balance_item_consignment_id, tenant_id, po_id, po_item_id,
382 ref_doc_type_id, ref_id, ref_item_id, qty_trx, trx_uom_id, qty_int,
383 base_uom_id, remark, version, create_datetime, create_user_id,
384 update_datetime, update_user_id)
385 SELECT log_po_balance_item_consignment_id, tenant_id, po_id, po_item_id,
386 ref_doc_type_id, ref_id, ref_item_id, qty_trx, trx_uom_id, qty_int,
387 base_uom_id, remark, version, create_datetime, create_user_id,
388 update_datetime, update_user_id
389 FROM tt_pu_log_po_balance_item_consignment
390 WHERE session_id = vSessionId;
391
392 ---------------------------------------------------------------------------------------
393 -- Update kembali po_id dan po_item_id setelah comsheet dilengkapi
394 ---------------------------------------------------------------------------------------
395 UPDATE m_product_catalog_preparation Z
396 SET po_id = C.po_id, po_item_id = C.po_item_id, version=Z.version+1,
397 update_datetime = pDateTime, update_user_id = pUserId
398 FROM m_product_catalog_preparation A, pu_po_item C, pu_po_balance_item_consignment D, m_product E,pu_po_ext F,m_catalog G
399 WHERE C.po_item_id = D.po_item_id
400 AND C.product_id = E.product_id
401 AND D.status_item IN ('I','R')
402 AND A.product_code = E.product_code
403 AND Z.product_catalog_id = A.product_catalog_id
404 AND A.flg_consign = 'Y'
405 AND A.po_id = -99
406 AND A.po_item_id = -99
407 AND F.po_id=C.po_id
408 AND G.catalog_id =F.catalog_id
409 AND G.catalog_code =pCatalogCode
410 AND G.catalog_code =A.catalog_code;
411
412 ---------------------------------------------------------------------------------------
413 -- Update doc_no dan doc_date berdasarkan po_id pada tabel m_product_catalog_preparation
414 ---------------------------------------------------------------------------------------
415 UPDATE m_product_catalog_preparation Z
416 SET doc_no = A.doc_no, doc_date = A.doc_date
417 FROM pu_po A
418 WHERE A.po_id = Z.po_id
419 AND Z.po_id <> -99
420 AND Z.po_item_id <> -99;
421
422 ---------------------------------------------------------------------------------------
423 -- Delete from tt_m_product_catalog_preparation
424 ---------------------------------------------------------------------------------------
425 DELETE FROM tt_m_product_catalog_preparation WHERE session_id = vSessionId;
426 DELETE FROM tt_pu_po WHERE session_id = vSessionId;
427 DELETE FROM tt_pu_po_item WHERE session_id = vSessionId;
428 DELETE FROM tt_pu_po_ext WHERE session_id = vSessionId;
429 DELETE FROM tt_pu_po_balance_item_consignment WHERE session_id = vSessionId;
430 DELETE FROM tt_pu_log_po_balance_item_consignment WHERE session_id = vSessionId;
431
432END;
433$BODY$
434 LANGUAGE plpgsql VOLATILE
435 COST 100;
436ALTER FUNCTION f_generate_commsheet_supp_to_erp(bigint, bigint, character varying, character varying, character varying, character varying)
437 OWNER TO sts;