Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Fitra 2018 Des 14
- --NOTE : type payment di web clinic memakai camel case
- CREATE OR REPLACE FUNCTION i_process_pos_from_so_online(character varying,character varying)
- RETURNS bigint AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pDatetime ALIAS FOR $2;
- vFlgInprogress character varying := 'I';
- vFlgNo character varying := 'N';
- vFlgYes character varying := 'Y';
- vFlgSkip character varying := 'S';
- vDocTypeIdSalesRetailShop bigint := 401;
- vEmptyString character varying := '';
- vSpaceString character varying := ' ';
- vNullRefId bigint := -99;
- vStatusSubmit character varying(1) := 'S';
- --vDatetime character varying(14);
- vUserId bigint := -1;
- vUserName character varying;
- --vStatusDocRelease character varying := 'R';
- vStatusDocShipped character varying := 'S';
- vTaxId bigint := -99;
- vTaxPercentage numeric := 0;
- vSessionId character varying;
- --vRoundingModeNonTax character varying(5);
- vCash character varying :='CASH';
- vProductStatusGood character varying :='GOOD';
- vModeLogAdd character varying :='A';
- vFlgCashBankCASH character varying :='C';
- vRecTypeKredit character varying :='K';
- vRecTypeDebit character varying :='D';
- vProcessName character varying :='i_ho_process_pos_shop';
- vCardTypeDebit character varying :='DEBIT';
- vCardTypeCredit character varying :='CREDIT';
- vTotalPos bigint :=0;
- vParamCodeWarehouseId character varying :='pos.from.so.online.default.warehouse.id';
- vTenantId bigint;
- vOuId bigint;
- vOutletId bigint;
- vFlgOuBuPkp character varying;
- BEGIN
- --vDatetime := to_char(pTimestamp,'yyyyMMddHH24mmss');
- vUserName := f_get_username(vUserId);
- vSessionId := pSessionId;
- DELETE FROM tt_i_trx_pos WHERE session_id = vSessionId;
- DELETE FROM tt_i_trx_pos_item WHERE session_id = vSessionId;
- --SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- --beri tanda untuk so online yg akan diproses (status S, dan ada resinya
- UPDATE sl_order A
- SET flg_process = vFlgInprogress,
- process_intgr_datetime = pDatetime
- WHERE A.flg_process = vFlgNo AND
- A.status = vStatusDocShipped AND
- A.delivery_datetime < pDatetime AND
- NOT (A.delivery_datetime IS NULL) AND
- TRIM(A.delivery_datetime) != vEmptyString AND
- EXISTS( SELECT 1 FROM sl_order_address B WHERE A.order_id=B.order_id AND TRIM(B.waybill_number) <> vEmptyString);
- --ambil informasi tenant, ouId, outletId dan flg pkp
- SELECT tenant_id, ou_id, outlet_id INTO vTenantId, vOuId, vOutletId
- FROM i_clinic_registration_mapping
- WHERE flg_online = vFlgYes
- ORDER BY clinic_code
- LIMIT 1;
- vTenantId := COALESCE(vTenantId, vNullRefId);
- vOuId := COALESCE(vOuId, vNullRefId);
- vOutletId := COALESCE(vOutletId, vNullRefId);
- SELECT COALESCE(B.flg_pkp, vFlgNo) INTO vFlgOuBuPkp
- FROM m_ou_structure A
- LEFT JOIN t_ou_legal B ON A.ou_bu_id = B.ou_id
- WHERE A.ou_id = vOuId;
- vFlgOuBuPkp := COALESCE(vFlgOuBuPkp, vFlgNo);
- --insert ke header pos temporary
- -- tenantnya patut diperhatikan
- INSERT INTO tt_i_trx_pos(
- session_id, trx_pos_id, process_no, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
- partner_id, partner_bill_to_id, partner_ship_to_id, salesman_id,
- curr_code, add_discount_percentage, add_discount_amount, gross_amount,
- nett_amount, tax_amount, add_amount, total_amount, rounding_amount,
- total_payment, total_refund, status, version, create_datetime,
- create_user_id, create_user_name, update_datetime, update_user_id,
- update_user_name, return_amount, warehouse_id, outlet_id)
- SELECT vSessionId, A.order_id, A.order_id||'_'||A.doc_no||'_'||vSessionId, vTenantId, vDocTypeIdSalesRetailShop, A.doc_no, SUBSTRING(A.delivery_datetime, 1,8),
- vOuId,vEmptyString, vEmptyString, A.doc_type_id, A.order_id, A.remark,
- vNullRefId, vNullRefId, vNullRefId, vNullRefId,
- A.curr_code, 0, 0, 0,
- 0, 0, (CASE WHEN C.shipping_fee - C.shipping_fee_discount < 0 THEN 0 ELSE C.shipping_fee - C.shipping_fee_discount END) + A.handling_fee, 0, 0,
- 0, 0, vStatusSubmit, 0, pDatetime,
- vUserId, vUserName, pDatetime, vUserId,
- vUserName, 0, f_get_value_system_config_by_param_code(vTenantId, vParamCodeWarehouseId)::bigint, vOutletId
- FROM sl_order A
- INNER JOIN sl_order_address C ON C.order_id = A.order_id
- WHERE A.flg_process = vFlgInprogress;
- --insert header ke pos item temporary
- INSERT INTO tt_i_trx_pos_item(
- session_id, process_no, tenant_id, trx_pos_id, line_no,
- product_id, product_balance_id,
- base_uom_id, qty, curr_code,
- gross_sell_price, flg_tax_amount, tax_id,
- tax_percentage, tax_price,
- promo_code, discount_percentage, discount_amount, nett_sell_price,
- tax_amount, nett_amount_item, remark, version, group_product_ou_id,
- create_datetime, create_user_id, update_datetime, update_user_id,
- ref_item_id, ref_assembly_product_id)
- SELECT vSessionId, A.process_no, A.tenant_id, A.trx_pos_id, ROW_NUMBER () OVER(ORDER BY C.product_id),
- C.product_id, f_get_product_balance_non_serialnumber_by_product_id(C.product_id, A.tenant_id),
- f_get_product_base_uom_id(C.product_id), C.qty_order, A.curr_code,
- C.unit_price, vFlgOuBuPkp, CASE WHEN vFlgOuBuPkp = vFlgYes THEN f_get_tax_by_tenant_id(A.tenant_id) ELSE vNullRefId END,
- CASE WHEN vFlgOuBuPkp = vFlgYes THEN f_get_tax_percentage_by_tenant_id(A.tenant_id) ELSE 0 END, 0,
- vEmptyString, 0, (C.item_discount_order + C.discount_header_on_item)/C.qty_order, 0,
- 0, C.sub_grand_total_order_amount - C.discount_header_on_item, vEmptyString, 0, f_get_group_product_ou(A.ou_id, C.product_id),
- pDatetime, vUserId, pDatetime, vUserId,
- vNullRefId, vNullRefId
- FROM tt_i_trx_pos A
- INNER JOIN sl_order_item C ON C.order_id = A.trx_pos_id
- WHERE A.session_id = vSessionId;
- --buatkan product balance untuk yg belum ada product balance nya
- UPDATE tt_i_trx_pos_item A
- SET product_balance_id = nextval('in_product_balance_seq')
- WHERE A.session_id = vSessionId AND
- A.product_balance_id = vNullRefId AND
- NOT EXISTS (
- SELECT 1 FROM in_product_balance B
- WHERE B.product_id = A.product_id AND
- B.lot_number = vSpaceString AND
- B.serial_number = vSpaceString AND
- B.tenant_id = A.tenant_id
- );
- INSERT INTO public.in_product_balance(
- product_balance_id, tenant_id, product_id, serial_number, lot_number,
- product_expired_date, product_year_made, version, create_datetime,
- create_user_id, update_datetime, update_user_id)
- SELECT product_balance_id, tenant_id, product_id, vSpaceString, vSpaceString,
- vSpaceString, vSpaceString, 0, pDatetime,
- vUserId, pDatetime, vUserId
- FROM tt_i_trx_pos_item A
- WHERE A.session_id = vSessionId AND
- NOT EXISTS ( SELECT 1
- FROM in_product_balance B
- WHERE B.product_balance_id = A.product_balance_id);
- --menghitung tax price, tax_amount
- --ini ikut perhitungan di submit SO
- UPDATE tt_i_trx_pos_item
- SET nett_sell_price = gross_sell_price - discount_amount,
- tax_price = f_tax_rounding(tenant_id, f_get_amount_before_tax((gross_sell_price - discount_amount), flg_tax_amount, tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdSalesRetailShop, curr_code), f_get_value_system_config_by_param_code(tenant_id, 'rounding.mode.non.tax')), tax_percentage),
- tax_amount = f_tax_rounding(tenant_id, f_get_amount_before_tax((qty * (gross_sell_price - discount_amount)), flg_tax_amount, tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdSalesRetailShop, curr_code), f_get_value_system_config_by_param_code(tenant_id, 'rounding.mode.non.tax')), tax_percentage)
- WHERE session_id = vSessionId;
- --menghitung nett_sell_price, nett_amount_item
- --ini ikut perhitungan BFnya addrian
- --UPDATE tt_i_trx_pos_item
- --SET nett_sell_price = gross_sell_price -tax_price,
- -- nett_amount_item = (gross_sell_price * qty) - tax_amount
- --WHERE A.session_id = vSessionId;
- --insert ke trx pos tax
- IF vFlgOuBuPkp = vFlgYes THEN
- INSERT INTO i_trx_pos_tax(
- process_no, tenant_id, trx_pos_id, tax_id, flg_amount,
- tax_percentage, base_amount, tax_amount, remark, version, create_datetime,
- create_user_id, update_datetime, update_user_id)
- SELECT A.process_no, A.tenant_id, A.trx_pos_id, A.tax_id, B.flg_amount,
- A.tax_percentage, SUM(A.nett_amount_item - A.tax_amount), SUM(A.tax_amount), vEmptyString, 0,pDatetime,
- vUserId, pDatetime, vUserId
- FROM tt_i_trx_pos_item A
- INNER JOIN m_tax B ON B.tax_id = A.tax_id
- WHERE A.session_id = vSessionId
- GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.tax_id, B.flg_amount,
- A.tax_percentage;
- END IF;
- --buatkan product balance stock jika tidak ada product balance stocknya
- WITH summary_qty AS(
- SELECT SUM(B.qty) AS qty,
- B.product_id,
- B.product_balance_id,
- vProductStatusGood AS product_status,
- A.warehouse_id,
- B.tenant_id
- FROM tt_i_trx_pos A
- INNER JOIN tt_i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no AND A.tenant_id = B.tenant_id
- WHERE B.session_id = vSessionId
- GROUP BY B.product_id, B.product_balance_id, A.warehouse_id, B.tenant_id
- )
- INSERT INTO in_product_balance_stock(
- tenant_id, warehouse_id, product_id,
- product_balance_id, product_status, base_uom_id, qty, version,
- create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.warehouse_id, A.product_id,
- A.product_balance_id, A.product_status, f_get_product_base_uom_id(A.product_id), 0, 0,
- pDatetime, vUserId, pDatetime, vUserId
- FROM summary_qty A
- WHERE NOT EXISTS (
- SELECT 1 FROM in_product_balance_stock B
- WHERE B.product_id = A.product_id AND
- B.product_balance_id = A.product_balance_id AND
- B.product_status = A.product_status AND
- B.tenant_id = A.tenant_id AND
- B.warehouse_id = A.warehouse_id);
- --update data product balance stock
- WITH summary_qty AS(
- SELECT SUM(B.qty) AS qty,
- B.product_id,
- B.product_balance_id,
- vProductStatusGood AS product_status,
- A.warehouse_id,
- B.tenant_id
- FROM tt_i_trx_pos A
- INNER JOIN tt_i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no AND A.tenant_id = B.tenant_id
- WHERE B.session_id = vSessionId
- GROUP BY B.product_id, B.product_balance_id, A.warehouse_id, B.tenant_id
- )
- UPDATE in_product_balance_stock Z
- SET qty = Z.qty - A.qty,
- version = Z.version + 1,
- update_datetime = pDatetime,
- update_user_id = vUserId
- FROM summary_qty A
- WHERE Z.product_id = A.product_id AND
- Z.warehouse_id = A.warehouse_id AND
- Z.product_balance_id = A.product_balance_id AND
- Z.product_status = A.product_status AND
- Z.tenant_id = A.tenant_id;
- --insert data log balance stock
- INSERT INTO in_log_product_balance_stock(
- tenant_id, ou_id, doc_type_id,
- ref_id, doc_no, doc_date, partner_id, product_id, warehouse_id,
- product_balance_id, product_status, base_uom_id, qty, version,
- create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id,
- A.trx_pos_id, A.doc_no, A.doc_date, vNullRefId, B.product_id, A.warehouse_id,
- B.product_balance_id, vProductStatusGood, B.base_uom_id, -1 * SUM(B.qty), 0,
- pDatetime, vUserId, pDatetime, vUserId
- FROM tt_i_trx_pos A
- INNER JOIN tt_i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND A.tenant_id = B.tenant_id AND
- A.session_id = B.session_id
- WHERE A.session_id = vSessionId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date,
- B.product_id, B.product_balance_id, B.base_uom_id, A.warehouse_id;
- --insert data i_trx_data_log
- INSERT INTO i_trx_data_log(
- tenant_id, ou_id, doc_date, db_version, type_data,
- mode_log, data_log, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_date , '1.0', 'in_log_product_balance_stock',
- vModeLogAdd, row_to_json(B)::TEXT, 0, pDatetime, vUserId,
- pDatetime, vUserId
- FROM tt_i_trx_pos A
- INNER JOIN in_log_product_balance_stock B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id AND
- A.doc_type_id = B.doc_type_id AND A.trx_pos_id = B.ref_id
- WHERE A.session_id = vSessionId
- GROUP BY B.log_product_balance_stock_id, A.tenant_id, A.ou_id, A.doc_date;
- --update gross_amount, nett_amount, total_amount
- WITH item_grouped AS (
- SELECT SUM(A.qty * A.discount_amount) AS discount_amount,
- SUM(A.qty * A.gross_sell_price) AS gross_amount,
- SUM(A.nett_amount_item) AS nett_amount,
- SUM(A.tax_amount) AS tax_amount,
- A.process_no,
- A.tenant_id,
- A.trx_pos_id
- FROM tt_i_trx_pos_item A
- WHERE A.session_id = vSessionId
- GROUP BY A.process_no, A.tenant_id, A.trx_pos_id
- )
- UPDATE tt_i_trx_pos Z
- SET gross_amount = Z.gross_amount + A.gross_amount,
- nett_amount = Z.nett_amount + A.nett_amount,
- total_amount = Z.total_amount + A.nett_amount + Z.add_amount
- FROM item_grouped A
- WHERE Z.session_id = vSessionId AND
- A.process_no = Z.process_no AND
- A.tenant_id = Z.tenant_id AND
- A.trx_pos_id = Z.trx_pos_id;
- --===============INSERT TERKAIT PAYMENT=========================
- -- buat paymentnya CASH
- -- karena currencynya cuma ada IDR, tidak perlu carikan nilai exchange
- INSERT INTO i_trx_pos_cash_payment(
- process_no, tenant_id, trx_pos_id, curr_payment_code,
- payment_amount, date_exchange_rate, numerator_rate, denominator_rate,
- conversion_amount, remark, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT A.process_no, A.tenant_id, A.trx_pos_id, A.curr_code,
- A.total_amount, A.doc_date, 1, 1,
- A.total_amount, vEmptyString, 0, pDatetime, vUserId,
- pDatetime, vUserId
- FROM tt_i_trx_pos A
- WHERE A.session_id = vSessionId;
- --insert ke cash bank balance degan amount 0
- WITH cashbank_balance_grouped AS (
- SELECT A.tenant_id, A.ou_id, f_get_bank_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code) AS cashbank_id,
- A.doc_date AS cash_bank_date, B.curr_payment_code AS curr_code
- FROM tt_i_trx_pos A
- INNER JOIN i_trx_pos_cash_payment B ON B.process_no = A.process_no AND B.tenant_id = A.tenant_id AND
- B.trx_pos_id = A.trx_pos_id
- WHERE A.session_id = vSessionId
- GROUP BY A.tenant_id, A.ou_id, f_get_bank_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code), A.doc_date,
- B.curr_payment_code
- )
- INSERT INTO cb_cashbank_balance(
- tenant_id, ou_id, cashbank_id, cash_bank_date,
- rec_type, curr_code, amount, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.cashbank_id, A.cash_bank_date,
- vRecTypeDebit, A.curr_code, 0, 0, pDatetime, vUserId,
- pDatetime, vUserId
- FROM cashbank_balance_grouped A
- WHERE NOT EXISTS(
- SELECT 1
- FROM cb_cashbank_balance B
- WHERE B.tenant_id = A.tenant_id AND
- B.ou_id = A.ou_id AND
- B.cashbank_id = A.cashbank_id AND
- B.cash_bank_date = A.cash_bank_date AND
- B.rec_type = vRecTypeDebit );
- --update cash bank balance untuk semua payment cash
- WITH cashbank_balance_grouped AS (
- SELECT A.tenant_id, A.ou_id, f_get_bank_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code) AS cashbank_id,
- A.doc_date AS cash_bank_date, B.curr_payment_code AS curr_code, sum(B.conversion_amount) AS amount
- FROM tt_i_trx_pos A
- INNER JOIN i_trx_pos_cash_payment B ON B.process_no = A.process_no AND B.tenant_id = A.tenant_id AND
- B.trx_pos_id = A.trx_pos_id
- WHERE A.session_id = vSessionId
- GROUP BY A.tenant_id, A.ou_id, f_get_bank_oulet_by_ou_id_and_currcode(A.ou_id, A.curr_code), A.doc_date,
- B.curr_payment_code
- )
- UPDATE cb_cashbank_balance Z
- SET amount = Z.amount + A.amount,
- version = Z.version + 1,
- update_datetime = pDatetime,
- update_user_id = vUserId
- FROM cashbank_balance_grouped A
- WHERE Z.tenant_id = A.tenant_id AND
- Z.ou_id = A.ou_id AND
- Z.cashbank_id = A.cashbank_id AND
- Z.cash_bank_date = A.cash_bank_date AND
- Z.rec_type = vRecTypeDebit;
- --update payment amount untuk pembayaran CASH
- WITH payment_cash_grouped AS (
- SELECT SUM(A.conversion_amount) AS payment_amount,
- B.process_no,
- B.tenant_id,
- B.trx_pos_id
- FROM i_trx_pos_cash_payment A
- INNER JOIN tt_i_trx_pos B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id AND A.trx_pos_id = B.trx_pos_id
- WHERE B.session_id = vSessionId
- GROUP BY B.process_no, B.tenant_id, B.trx_pos_id
- )
- UPDATE tt_i_trx_pos Z
- SET total_payment = Z.total_payment + A.payment_amount
- FROM payment_cash_grouped A
- WHERE Z.session_id = vSessionId AND
- A.process_no = Z.process_no AND
- A.tenant_id = Z.tenant_id AND
- A.trx_pos_id = Z.trx_pos_id;
- --buat data header POS
- INSERT INTO i_trx_pos(
- trx_pos_id, process_no, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
- partner_id, partner_bill_to_id, partner_ship_to_id, salesman_id,
- curr_code, add_discount_percentage, add_discount_amount, gross_amount,
- nett_amount, tax_amount, add_amount, total_amount, rounding_amount,
- total_payment, total_refund, status, version, create_datetime,
- create_user_id, create_user_name, update_datetime, update_user_id,
- update_user_name, return_amount)
- SELECT trx_pos_id, process_no, tenant_id, doc_type_id, doc_no, doc_date,
- ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
- partner_id, partner_bill_to_id, partner_ship_to_id, salesman_id,
- curr_code, add_discount_percentage, add_discount_amount, gross_amount,
- nett_amount, tax_amount, add_amount, total_amount, rounding_amount,
- total_payment, total_refund, status, version, create_datetime,
- create_user_id, create_user_name, update_datetime, update_user_id,
- update_user_name, return_amount
- FROM tt_i_trx_pos WHERE session_id = vSessionId;
- --buat data item POS
- INSERT INTO i_trx_pos_item(
- process_no, tenant_id, trx_pos_id, line_no,
- product_id, product_balance_id, base_uom_id, qty, curr_code,
- gross_sell_price, flg_tax_amount, tax_id, tax_percentage, tax_price,
- promo_code, discount_percentage, discount_amount, nett_sell_price,
- tax_amount, nett_amount_item, remark, version, group_product_ou_id,
- create_datetime, create_user_id, update_datetime, update_user_id,
- ref_item_id, ref_assembly_product_id)
- SELECT process_no, tenant_id, trx_pos_id, line_no,
- product_id, product_balance_id, base_uom_id, qty, curr_code,
- gross_sell_price, flg_tax_amount, tax_id, tax_percentage, tax_price,
- promo_code, discount_percentage, discount_amount, nett_sell_price,
- tax_amount, nett_amount_item, remark, version, group_product_ou_id,
- create_datetime, create_user_id, update_datetime, update_user_id,
- ref_item_id, ref_assembly_product_id
- FROM tt_i_trx_pos_item
- WHERE session_id = vSessionId;
- --panggil function submit
- PERFORM generate_process_message_for_submit_pos_shop(vSessionId, A.tenant_id, vProcessName, A.process_no,
- pDatetime, A.outlet_id, A.ou_id, A.warehouse_id, vUserId)
- FROM tt_i_trx_pos A
- WHERE A.session_id = vSessionId;
- --update trx_pos flg_process = Y
- UPDATE sl_order A
- SET flg_process = vFlgYes,
- end_process_intgr_datetime = to_char(current_timestamp,'yyyyMMddHH24mmss')
- FROM tt_i_trx_pos B
- WHERE A.order_id = B.ref_id AND
- A.doc_type_id = B.ref_doc_type_id;
- SElECT count(1) INTO vTotalPos
- FROM tt_i_trx_pos A
- WHERE A.session_id = vSessionId;
- DELETE FROM tt_i_trx_pos WHERE session_id = vSessionId;
- DELETE FROM tt_i_trx_pos_item WHERE session_id = vSessionId;
- return vTotalPos;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement