Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- FUNCTION: sl_submit_so_from_magento_with_complete_date_and_shipping_cost(bigint, CHARACTER varying, bigint, bigint, bigint, CHARACTER varying, CHARACTER varying, numeric)
- -- DROP FUNCTION sl_submit_so_from_magento_with_complete_date_and_shipping_cost(bigint, CHARACTER varying, bigint, bigint, bigint, CHARACTER varying, CHARACTER varying, numeric);
- CREATE OR REPLACE FUNCTION sl_submit_so_from_magento_with_complete_date_and_shipping_cost(
- bigint,
- CHARACTER varying,
- bigint,
- bigint,
- bigint,
- CHARACTER varying,
- CHARACTER varying,
- numeric)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pSoId ALIAS FOR $3;
- pWarehouseId ALIAS FOR $4;
- vUserId ALIAS FOR $5;
- vDatetime ALIAS FOR $6;
- vCompleteDate ALIAS FOR $7;
- vShippingAmount ALIAS FOR $8;
- vProcessId bigint;
- vDoId bigint;
- vFlagInvoice CHARACTER varying(1);
- vEmptyId bigint;
- vStatusRelease CHARACTER varying(1);
- vStatusDraft CHARACTER varying(1);
- vStatusFinal CHARACTER varying(1);
- vEmptyValue CHARACTER varying(1);
- vProductStatus CHARACTER varying(5);
- vDaftarProdukTidakCukup TEXT;
- vProductStatusReturn CHARACTER varying(50);
- vSignDebit CHARACTER varying(1);
- vSignCredit CHARACTER varying(1);
- vTypeRate CHARACTER varying(3);
- vProductCOA CHARACTER varying(10);
- vSystemCOA CHARACTER varying(10);
- vSoId bigint;
- vUnfinishedItem bigint;
- vParentOuId bigint;
- vJournalTrxId bigint;
- vJournalType CHARACTER varying(20);
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vSalesOrderDocTypeId bigint;
- vDeliveryOrderDocTypeId bigint;
- vActivityOngkir bigint;
- vOuRcOngkir bigint;
- vZero numeric;
- vErrorMessage CHARACTER varying(1000);
- vSoNo CHARACTER varying(100);
- vRoundingModeNonTax CHARACTER varying(5);
- vWarehouseECommerceId bigint;
- vMaxLineNo bigint;
- BEGIN
- vFlagInvoice := 'N';
- vEmptyId := -99;
- vStatusRelease := 'R';
- vStatusDraft := 'D';
- vStatusFinal := 'F';
- vEmptyValue := ' ';
- vProductStatus := 'GOOD';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vProductCOA := 'PRODUCT';
- vSystemCOA := 'SYSTEM';
- vUnfinishedItem := 0;
- vZero := 0;
- vSalesOrderDocTypeId = 301;
- vDeliveryOrderDocTypeId = 311;
- SELECT activity_gl_id INTO vActivityOngkir FROM m_activity_gl WHERE activity_gl_code = 'Ongkir';
- SELECT ou_id INTO vOuRcOngkir FROM t_ou WHERE ou_code = 'LOG';
- SELECT product_status_code INTO vProductStatusReturn FROM m_product_status WHERE flg_return = 'Y';
- SELECT NEXTVAL('sl_do_seq') INTO vDoId;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'id.warehouse.ecommerce') INTO vWarehouseECommerceId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- SELECT doc_no INTO vSoNo FROM sl_so WHERE so_id = pSoId LIMIT 1;
- UPDATE sl_so SET doc_type_id = vSalesOrderDocTypeId, status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE so_id = pSoId;
- INSERT INTO sl_so_tax
- (tenant_id, so_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.tenant_id, A.so_id, A.tax_id, B.flg_amount,
- A.tax_percentage, SUM(f_get_amount_before_tax((A.qty_so * ( A.gross_sell_price - A.discount_amount) ) , A.flg_tax_amount, A.tax_percentage,f_get_digit_decimal_doc_curr(vSalesOrderDocTypeId, A.curr_code), vRoundingModeNonTax)),
- f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_so * ( A.gross_sell_price - A.discount_amount) ) , A.flg_tax_amount, A.tax_percentage,f_get_digit_decimal_doc_curr(vSalesOrderDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage), B.tax_name,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_so_item A, m_tax B
- WHERE A.tax_id = B.tax_id AND
- A.so_id = pSoId AND
- A.tax_id <> vEmptyId
- GROUP BY A.tenant_id, A.so_id, A.tax_id, B.flg_amount,
- A.tax_percentage, B.tax_name, A.curr_code;
- INSERT INTO sl_so_cost
- (tenant_id, so_id, line_no,
- ref_doc_type_id, ref_id, activity_gl_id, curr_code,
- percentage, base_amount, tax_id, tax_percentage,
- add_amount, tax_amount, remark, version,
- create_datetime, create_user_id, update_datetime, update_user_id,
- segment_id, ou_rc_id)
- SELECT A.tenant_id, A.so_id, 1, vEmptyId, vEmptyId, vActivityOngkir, A.curr_code,
- vZero, vZero, vEmptyId, vZero,
- vShippingAmount, vZero, A.remark, A.version,
- create_datetime, create_user_id, update_datetime, update_user_id,
- vEmptyId, vOuRcOngkir
- FROM sl_so A
- WHERE A.so_id = pSoId;
- RAISE NOTICE 'INSERT INTO sl_so_tax';
- INSERT INTO sl_so_balance_item
- (so_item_id, tenant_id, ou_id, qty_so, qty_dlv, qty_return, qty_cancel, qty_add, so_uom_id,
- qty_so_int, qty_dlv_int, qty_return_int, qty_cancel_int, qty_add_int, base_uom_id,
- tolerance_dlv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.so_item_id, A.tenant_id, B.ou_id, A.qty_so, A.qty_so, 0, 0, 0, A.so_uom_id,
- A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
- A.tolerance_dlv_qty, vStatusFinal, 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_so_item A, sl_so B
- WHERE A.so_id = pSoId AND
- A.so_id = B.so_id;
- RAISE NOTICE 'INSERT INTO sl_so_balance_item';
- INSERT INTO sl_log_so_balance_item
- (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.so_id, A.so_item_id, vEmptyId, vEmptyId, vEmptyId,
- A.qty_so, A.so_uom_id, A.qty_int, A.base_uom_id, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_so_item A
- WHERE A.so_id = pSoId;
- RAISE NOTICE 'INSERT INTO sl_log_so_balance_item';
- /*
- * Update By Sarah
- * 7 Maret 2017
- * doc_date di ubah menyesuaikan tanggal process, tidak menggunakan complite date lagi.
- */
- INSERT INTO sl_do(
- do_id, 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_ship_to_id,
- partner_ship_address_id, warehouse_id, no_vehicle, flg_delivery,
- delivery_code, status_doc, workflow_status, version, create_datetime,
- create_user_id, update_datetime, update_user_id, eta, eta_day)
- SELECT vDoId, A.tenant_id, vDeliveryOrderDocTypeId, A.doc_no, to_char(NOW(), 'yyyyMMdd'), A.ou_id, '',
- '', vSalesOrderDocTypeId, A.so_id, A.remark, A.partner_ship_to_id,
- A.partner_ship_address_id, pWarehouseId, '', 'Y',
- '','R','APPROVED', 0, vDatetime,
- vUserId, vDatetime, vUserId, '', ''
- FROM sl_so A
- JOIN sl_so_item B
- ON A.so_id = B.so_id
- WHERE A.so_id = pSoId
- GROUP BY A.so_id;
- RAISE NOTICE 'INSERT INTO sl_do';
- INSERT INTO sl_do_item
- (tenant_id, do_id, line_no, ref_doc_type_id, ref_id,
- product_id, qty_dlv_so, so_uom_id, qty_dlv_int, base_uom_id,
- remark, "version", create_datetime, create_user_id, update_datetime, update_user_id, product_status)
- SELECT A.tenant_id, vDoId, A.line_no, vSalesOrderDocTypeId, A.so_item_id,
- A.product_id, A.qty_so, A.so_uom_id, A.qty_int, A.base_uom_id,
- A.remark, 0, vDatetime, vUserId, vDatetime, vUserId, vProductStatus
- FROM sl_so_item A
- WHERE A.so_id = pSoId;
- RAISE NOTICE 'INSERT INTO sl_do_item';
- IF EXISTS (
- SELECT A.do_item_id, A.product_id, B.product_balance_id, C.product_code
- FROM sl_do_item A
- JOIN m_product C ON A.product_id = C.product_id
- LEFT JOIN in_product_balance B ON A.product_id = B.product_id
- WHERE A.tenant_id = pTenantId
- AND A.do_id = vDoId
- AND product_balance_id IS NULL
- ) THEN
- SELECT 'The products: '||string_agg(product_code, ',')||' has no stock in inventory' INTO vErrorMessage
- FROM (
- SELECT A.do_item_id, A.product_id, B.product_balance_id, C.product_code
- FROM sl_do_item A
- JOIN m_product C ON A.product_id = C.product_id
- LEFT JOIN in_product_balance B ON A.product_id = B.product_id
- WHERE A.tenant_id = pTenantId
- AND A.do_id = vDoId
- AND product_balance_id IS NULL ) Z;
- RAISE EXCEPTION 'Order Magento % cannot be processed ', vSoNo
- USING HINT = vErrorMessage;
- END IF;
- INSERT INTO sl_do_product
- (tenant_id, do_item_id, line_no, product_id, product_balance_id,
- product_status, qty_dlv_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT C.tenant_id, C.do_item_id, ROW_NUMBER() OVER (), B.product_id, A.product_balance_id,
- A.product_status, CASE WHEN C.qty_dlv_int < A.qty THEN C.qty_dlv_int ELSE A.qty END, B.base_uom_id,B.remark,
- 0, B.create_datetime, B.create_user_id, B.update_datetime, B.update_user_id
- FROM in_product_balance_stock A, sl_so_item B, sl_do_item C
- WHERE A.product_id = B.product_id AND A.product_id=C.product_id
- AND B.so_item_id = C.ref_id
- AND B.so_id = pSoId
- AND A.product_status = vProductStatusReturn;
- SELECT COALESCE(MAX(A.line_no), 0) INTO vMaxLineNo
- FROM sl_do_product A
- INNER JOIN sl_do_item B ON A.do_item_id = B.do_item_id
- INNER JOIN sl_so_item C ON C.so_item_id = B.ref_id
- WHERE C.so_id = pSoId;
- INSERT INTO sl_do_product
- (tenant_id, do_item_id, line_no, product_id, product_balance_id,
- product_status, qty_dlv_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT C.tenant_id, C.do_item_id, vMaxLineNo + ROW_NUMBER() OVER (), B.product_id, A.product_balance_id,
- A.product_status, C.qty_dlv_int - COALESCE(D.qty_dlv_int,0), B.base_uom_id,B.remark,
- 0, B.create_datetime, B.create_user_id, B.update_datetime, B.update_user_id
- FROM in_product_balance_stock A, sl_so_item B, sl_do_item C
- LEFT JOIN sl_do_product D ON C.do_item_id = D.do_item_id
- WHERE A.product_id = B.product_id
- AND A.product_id = C.product_id
- AND B.so_item_id = C.ref_id
- AND B.so_id = pSoId
- AND A.product_status = vProductStatus;
- RAISE NOTICE 'INSERT INTO sl_do_product';
- RAISE NOTICE 'UPDATE sl_so_balance_item';
- UPDATE sl_do SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
- WHERE do_id = vDoId;
- INSERT INTO sl_log_so_balance_item
- (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, C.so_id, C.so_item_id, A.doc_type_id, A.do_id, B.do_item_id,
- B.qty_dlv_so * -1, B.so_uom_id, B.qty_dlv_int *-1, B.base_uom_id, B.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so_item C
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- B.ref_id = C.so_item_id;
- INSERT INTO sl_so_balance_invoice
- (tenant_id, ou_id, partner_id, so_id,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
- curr_code, price_so, item_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, D.partner_bill_to_id, A.ref_id,
- A.doc_type_id, A.do_id, A.doc_no, A.doc_date, B.do_item_id, B.qty_dlv_so, B.so_uom_id,
- C.curr_code, C.nett_sell_price,
- f_get_amount_before_tax((B.qty_dlv_so * ( C.gross_sell_price - C.discount_amount )), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax),
- vFlagInvoice, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so_item C, sl_so D
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- B.ref_id = C.so_item_id AND
- C.so_id = D.so_id;
- INSERT INTO sl_so_balance_invoice_tax
- (tenant_id, ou_id, partner_id, so_id,
- ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
- tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, A.ref_id,
- A.doc_type_id, A.do_id, B.do_item_id, C.tax_id, D.flg_amount,
- C.tax_percentage, C.curr_code,
- f_get_amount_before_tax((B.qty_dlv_so * ( C.gross_sell_price - C.discount_amount) ), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax),
- f_tax_rounding(A.tenant_id, f_get_amount_before_tax((B.qty_dlv_so * C.gross_sell_price) - C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage), vFlagInvoice, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so_item C, m_tax D, sl_so E
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- B.ref_id = C.so_item_id AND
- C.tax_id = D.tax_id AND
- C.so_id = E.so_id;
- INSERT INTO tt_check_stock_available
- (session_id, do_id, do_item_id, product_id, qty_do, available_qty)
- WITH summary_available AS (
- SELECT A.product_id, COALESCE(SUM(A.qty),0) AS available_qty FROM in_product_balance_stock A, sl_do_item B
- WHERE A.product_id = B.product_id
- AND A.warehouse_id = vWarehouseECommerceId
- AND B.do_id = vDoId
- GROUP BY A.product_id
- )
- SELECT pSessionId, B.do_id, B.do_item_id, B.product_id, B.qty_dlv_int AS qty_do, A.available_qty
- FROM summary_available A, sl_do_item B
- WHERE B.do_id = vDoId
- AND A.product_id = B.product_id;
- IF EXISTS ( SELECT 1 FROM tt_check_stock_available WHERE session_id = pSessionId
- AND available_qty - qty_do < 0 )
- THEN
- SELECT string_agg(B.product_code,', ') INTO vDaftarProdukTidakCukup
- FROM tt_check_stock_available A, m_product B
- WHERE session_id = pSessionId
- AND available_qty - qty_do < 0
- AND A.product_id = B.product_id;
- RAISE EXCEPTION 'DO tidak bisa diproses karena stok tidak cukup: %', vDaftarProdukTidakCukup;
- END IF;
- UPDATE in_product_balance_stock Y SET qty = D.qty - Z.qty_dlv_int
- FROM
- in_product_balance_stock D,
- (SELECT A.do_item_id, A.product_id, B.product_balance_id, C.product_balance_stock_id, C.qty, B.qty_dlv_int
- FROM sl_do_item A
- INNER JOIN sl_do_product B ON A.tenant_id = B.tenant_id AND A.do_item_id = B.do_item_id
- INNER JOIN in_product_balance_stock C ON B.tenant_id = C.tenant_id AND B.product_id = C.product_id AND C.product_balance_id = B.product_balance_id AND C.product_status = B.product_status
- WHERE A.tenant_id = pTenantId
- AND A.do_id = vDoId) Z
- WHERE D.product_balance_stock_id = Z.product_balance_stock_id
- AND Y.product_balance_stock_id = Z.product_balance_stock_id;
- INSERT INTO in_log_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, product_balance_id, warehouse_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.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_do_product C, m_product D, m_product_custom E
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- B.do_item_id = C.do_item_id AND
- C.product_id = D.product_id AND
- D.product_id = E.product_id AND
- E.flg_buy_konsinyasi='N'
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;
- INSERT INTO in_log_product_consignment_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, product_balance_id, warehouse_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.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_do_product C, m_product D, m_product_custom E
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- B.do_item_id = C.do_item_id AND
- C.product_id = D.product_id AND
- D.product_id = E.product_id AND
- E.flg_buy_konsinyasi = 'Y'
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;
- INSERT INTO in_balance_do_item
- (do_item_id, tenant_id, ou_id, do_id, doc_no, doc_date, partner_id,
- so_id, so_no, so_date, so_item_id,
- qty_dlv, qty_return, so_uom_id, qty_dlv_int,
- qty_return_int, base_uom_id, status_item,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- A.ref_id, C.doc_no, C.doc_date, B.ref_id,
- SUM(B.qty_dlv_so), 0, B.so_uom_id, SUM(B.qty_dlv_int),
- 0, B.base_uom_id, vStatusRelease,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so C
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- A.ref_id = C.so_id
- GROUP BY B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- A.ref_id, C.doc_no, C.doc_date, B.ref_id, B.so_uom_id, B.base_uom_id;
- UPDATE sl_so_balance_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- FROM sl_do_item A
- WHERE sl_so_balance_item.so_item_id = A.ref_id AND
- sl_so_balance_item.tenant_id = A.tenant_id AND
- A.do_id = vDoId AND
- sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv > 0;
- UPDATE sl_so_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
- FROM sl_do_item A
- WHERE sl_so_balance_item.so_item_id = A.ref_id AND
- sl_so_balance_item.tenant_id = A.tenant_id AND
- A.do_id = vDoId AND
- sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv <= 0;
- SELECT COUNT(1) INTO vUnfinishedItem
- FROM sl_so_balance_item A, sl_so_item B
- WHERE A.so_item_id = B.so_item_id AND
- B.so_id = vSoId AND
- A.status_item = vStatusRelease;
- IF vUnfinishedItem = 0 THEN
- UPDATE sl_so SET status_doc = vStatusFinal
- WHERE so_id = vSoId;
- END IF;
- INSERT INTO tt_pu_po_balance_item_consignment_sold(
- session_id, tenant_id, po_id, po_item_id, so_id, so_item_id,
- normal_price, sold_price, discount, margin_internal, margin_supp,
- sold_price_after_margin, qty_so, qty_return)
- SELECT pSessionId, pTenantId, B.po_id, B.po_item_id, C.so_id, C.so_item_id,
- B.gross_price_po, C.gross_sell_price,
- 0 AS discount,
- 0 AS margin_internal,
- 0 AS margin_supp,
- 0 AS sold_price_after_margin,
- qty_so,
- 0 AS qty_return
- FROM pu_po_balance_item_consignment A
- JOIN pu_po_item B ON A.po_item_id = B.po_item_id AND A.status_item IN ('R', 'I')
- JOIN pu_po G ON G.po_id = B.po_id
- RIGHT JOIN sl_so_item C ON C.product_id = B.product_id
- JOIN m_product_custom E ON C.product_id = E.product_id
- JOIN m_product F ON F.product_id=C.product_id
- RIGHT JOIN sl_so D ON C.so_id=D.so_id
- WHERE
- C.so_id = pSoId AND
- flg_buy_konsinyasi = 'Y' AND
- gross_price_po IS NULL;
- IF EXISTS ( SELECT 1 FROM tt_pu_po_balance_item_consignment_sold WHERE session_id = pSessionId AND tenant_id = pTenantId AND so_id = pSoId ) THEN
- DELETE FROM tt_pu_po_balance_item_consignment_sold WHERE session_id = pSessionId AND tenant_id = pTenantId AND so_id = pSoId;
- RAISE EXCEPTION 'Order Magento % cannot be processed ', vSoNo
- USING HINT = 'There is sold product with no purchase data';
- ELSE
- DELETE FROM tt_pu_po_balance_item_consignment_sold WHERE session_id = pSessionId AND tenant_id = pTenantId AND so_id = pSoId;
- END IF;
- INSERT INTO tt_pu_po_balance_item_consignment_sold(
- session_id, tenant_id, supplier_id, po_id, po_item_id, so_id, so_item_id,
- normal_price, sold_price, discount, margin_internal, margin_supp,
- sold_price_after_margin, qty_so, qty_return, remark)
- SELECT pSessionId, pTenantId, G.partner_id, B.po_id, B.po_item_id, C.so_id, C.so_item_id,
- B.gross_price_po, C.gross_sell_price,
- 0 AS discount,
- 0 AS margin_internal,
- 0 AS margin_supp,
- 0 AS sold_price_after_margin,
- qty_so,
- 0 AS qty_return,
- '' AS remark
- FROM pu_po_balance_item_consignment A
- JOIN pu_po_item B ON A.po_item_id = B.po_item_id AND A.status_item IN ('R', 'I')
- JOIN pu_po G ON G.po_id = B.po_id
- JOIN sl_so_item C ON C.product_id = B.product_id
- JOIN m_product_custom E ON C.product_id = E.product_id
- JOIN m_product F ON F.product_id=C.product_id
- JOIN sl_so D ON C.so_id=D.so_id
- WHERE
- C.so_id = pSoId AND
- flg_buy_konsinyasi = 'Y' AND gross_price_po IS NOT NULL;
- UPDATE tt_pu_po_balance_item_consignment_sold
- SET discount = ((normal_price-sold_price)/normal_price)*100
- WHERE session_id = pSessionId AND tenant_id = pTenantId AND so_id = pSoId;
- UPDATE tt_pu_po_balance_item_consignment_sold W SET
- margin_internal = Z.internal_percentage,
- margin_supp = Z.supplier_percentage
- FROM
- (select B.session_id, B.tenant_id, B.po_id, B.po_item_id, B.so_item_id, A.internal_percentage, A.supplier_percentage
- from pr_m_margin_sell_price A JOIN tt_pu_po_balance_item_consignment_sold B ON A.partner_id = B.supplier_id
- WHERE discount BETWEEN disc_from AND disc_to ) Z
- WHERE
- W.session_id = Z.session_id
- AND W.tenant_id = Z.tenant_id
- AND W.po_id = Z.po_id
- AND W.po_item_id = Z.po_item_id
- AND W.so_item_id = Z.so_item_id;
- UPDATE tt_pu_po_balance_item_consignment_sold SET
- remark = 'MARGIN NOT SET UP'
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId
- AND margin_internal=0
- AND margin_supp=0;
- UPDATE tt_pu_po_balance_item_consignment_sold SET sold_price_after_margin = margin_supp * 0.01 * sold_price
- WHERE session_id = pSessionId AND tenant_id = pTenantId;
- INSERT INTO pu_po_balance_item_consignment_sold(
- tenant_id, supplier_id, po_id, po_item_id, so_id, so_item_id,
- normal_price, normal_price_correction, sold_price, sold_price_used, discount, margin_internal, margin_supp, margin_supp_correction,
- sold_price_after_margin, qty_so, qty_return, version, create_datetime, create_user_id, update_datetime, update_user_id, remark)
- SELECT tenant_id, supplier_id, po_id, po_item_id, so_id, so_item_id,
- normal_price, normal_price, sold_price, sold_price, discount, margin_internal, margin_supp, margin_supp,
- sold_price_after_margin, qty_so, qty_return, 0, vDatetime, vUserId, vDatetime, vUserId, remark
- FROM tt_pu_po_balance_item_consignment_sold
- WHERE session_id = pSessionId AND tenant_id = pTenantId;
- DELETE FROM tt_pu_po_balance_item_consignment_sold WHERE session_id = pSessionId AND tenant_id = pTenantId;
- INSERT INTO tt_do_po_item_consignment
- (session_id, doc_type_id, do_id, do_item_id,
- po_id, po_item_id, remark,
- qty_po, qty_sell, po_uom_id,
- qty_int_po, qty_int_sell, base_uom_id)
- SELECT pSessionId, A.doc_type_id, A.do_id, B.do_item_id,
- C.po_id, C.po_item_id, B.remark,
- D.qty_po, B.qty_dlv_int * D.qty_po / D.qty_int_po, D.po_uom_id,
- D.qty_int_po, B.qty_dlv_int, D.base_uom_id
- FROM sl_do A, sl_do_item B, pu_po_balance_item_consignment_sold C, pu_po_balance_item_consignment D
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- A.ref_id = C.so_id AND
- B.ref_id = C.so_item_id AND
- C.po_item_id = D.po_item_id;
- UPDATE pu_po_balance_item_consignment B SET
- qty_int_sell = B.qty_int_sell + A.qty_int_sell, qty_sell = B.qty_sell + A.qty_sell,
- version = B.version + 1, update_datetime = vDatetime, update_user_id = vUserId
- FROM tt_do_po_item_consignment A
- WHERE A.session_id = pSessionId AND
- B.po_item_id = A.po_item_id;
- INSERT INTO pu_log_po_balance_item_consignment
- (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
- qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pTenantId, A.po_id, A.po_item_id, A.doc_type_id, A.do_id, A.do_item_id,
- A.qty_sell, A.po_uom_id, A.qty_int_sell, A.base_uom_id, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_do_po_item_consignment A
- WHERE A.session_id = pSessionId;
- SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) AS doc
- FROM sl_do A
- WHERE A.do_id = vDoId INTO result;
- vSoId := result.ref_id;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
- FROM sl_do A
- WHERE A.do_id = vDoId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- INSERT INTO gl_journal_trx
- (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.do_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_ship_to_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_so B
- WHERE A.do_id = vDoId AND
- A.ref_doc_type_id = B.doc_type_id AND
- A.ref_id = B.so_id;
- INSERT INTO tt_journal_trx_item
- (session_id, tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
- A.doc_type_id, B.do_item_id,
- A.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vProductCOA, vEmptyId,
- f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_dlv_int, B.base_uom_id,
- 0, A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', B.remark
- FROM sl_do A, sl_do_item B, sl_so_item C
- WHERE A.do_id = vDoId AND
- A.do_id = B.do_id AND
- B.ref_id = C.so_item_id;
- INSERT INTO gl_journal_trx_item
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId;
- INSERT INTO gl_journal_trx_mapping
- (tenant_id, journal_trx_id, line_no,
- ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id,
- segmen_id, sign_journal, flg_source_coa, activity_gl_id,
- coa_id, curr_code, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- vEmptyId, vEmptyId,
- vEmptyId, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
- 0, A.journal_date, A.type_rate,
- 1, 1, 'COGS', vEmptyValue,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement