Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Adrian, Feb 13, 2017
- CREATE OR REPLACE FUNCTION sl_submit_so_from_dgb(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vSoId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vStatusRelease character varying(1);
- vStatusInProgress character varying(1);
- vStatusFinal character varying(1);
- vEmptyId bigint;
- vNol numeric;
- vSalesOrderDocTypeId bigint;
- vRoundingModeNonTax character varying(5);
- vDeliveryBorrowDocType bigint;
- vReturnBorrowDocType bigint;
- vDeliveryOrderDocType bigint;
- vReturnBorrowDocNo character varying(30);
- vReturnBorrowNumId bigint;
- vDeliveryOrderDocNo character varying(30);
- vDeliveryOrderNumId bigint;
- vReturnBorrowId bigint;
- vDeliveryOrderId bigint;
- vFlagInvoice character varying(1);
- vStatusDraft character varying(1);
- vEmptyValue character varying(1);
- vProductStatus character varying(5);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vProductCOA character varying(10);
- vSystemCOA character varying(10);
- vUnfinishedItem bigint;
- vYes character varying(1);
- vFlagNo character varying(1);
- vParentOuId bigint;
- vJournalTrxId bigint;
- vJournalType character varying(20);
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vWorkflowApproved character varying(14);
- vFlowReturnBorrowId bigint;
- vSchemeReturnBorrow character varying;
- vFlowDeliveryOrderId bigint;
- vSchemeDeliveryOrder character varying;
- BEGIN
- vStatusRelease := 'R';
- vEmptyId := -99;
- vNol := 0;
- vFlagNo := 'N';
- vStatusInProgress := 'I';
- vStatusFinal := 'F';
- vEmptyValue := ' ';
- vProductStatus := 'GOOD';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vProductCOA := 'PRODUCT';
- vSystemCOA := 'SYSTEM';
- vUnfinishedItem := 0;
- vYes = 'Y';
- vDeliveryBorrowDocType := 551;
- vReturnBorrowDocType := 552;
- vDeliveryOrderDocType := 311;
- vWorkflowApproved := 'APPROVED';
- vSchemeReturnBorrow := 'EA52';
- vSchemeDeliveryOrder := 'FB01';
- vSalesOrderDocTypeId = 301;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'sl_submit_so_from_dgb' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vSoId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'soId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'userId';
- SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- SELECT CAST(A.process_parameter_value AS character varying(30)) INTO vReturnBorrowDocNo FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'RGB_NO';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vReturnBorrowNumId FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'RGB_NUM_ID';
- SELECT CAST(A.process_parameter_value AS character varying(30)) INTO vDeliveryOrderDocNo FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'DO_NO';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vDeliveryOrderNumId FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND A.process_parameter_key = 'DO_NUM_ID';
- /*
- * 1. update status doc sl_so
- * 2. add sl_so_tax
- * 3. add sl_so_balance_item
- * 4. add sl_log_so_balance_item
- */
- UPDATE sl_so SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE so_id = vSoId;
- 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)) + f_get_total_warranty_amount_for_so(A.so_item_id), 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)) + f_get_total_warranty_amount_for_so(A.so_item_id), 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 = vSoId 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_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, 0, 0, 0, 0, A.so_uom_id,
- A.qty_int, 0, 0, 0, 0, A.base_uom_id,
- A.tolerance_dlv_qty, vStatusRelease, 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_so_item A, sl_so B
- WHERE A.so_id = vSoId AND
- A.so_id = B.so_id;
- --modified by putra soliman, 29 Februari 2016
- -- menambahkan insert ke balance so warranty item
- INSERT INTO sl_so_balance_warranty_item(
- tenant_id, ou_id,
- so_warranty_item_id, so_item_id, flg_default_warranty, def_warranty_time,
- warranty_time, warranty_type_id, warranty_sell_qty, warranty_sell_price,
- warranty_sell_amount, status_item, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT C.tenant_id, C.ou_id,
- A.so_warranty_item_id, A.so_item_id, A.flg_default_warranty, A.def_warranty_time,
- A.warranty_time, A.warranty_type_id, A.warranty_sell_qty, A.warranty_sell_price,
- A.warranty_sell_amount, vStatusRelease, vNol, vDatetime, vUserId,
- vDatetime, vUserId
- FROM sl_so_warranty_item A, sl_so_item B, sl_so C
- WHERE A.so_item_id = B.so_item_id
- AND B.so_id = C.so_id
- AND C.so_id = vSoId;
- -- WTC, 20141222, insert juga ke table saldo qty SO terhadap PO, yang akan digunakan/diupdate saat input PO dari SO
- -- TODO: insert to sl_log_so_po_balance_item
- INSERT INTO sl_so_po_balance_item
- (so_item_id, tenant_id, ou_id, so_id, qty_po_int, qty_po_int_return, qty_po_int_cancel, qty_po_int_add, base_uom_id,
- status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.so_item_id, A.tenant_id, B.ou_id, B.so_id, 0, 0, 0, 0, A.base_uom_id,
- vStatusRelease, 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_so_item A, sl_so B
- WHERE A.so_id = vSoId AND
- A.so_id = B.so_id;
- 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 = vSoId;
- --modified by putra soliman, 29 Februari 2016
- -- menambahkan insert ke log untuk balance so warranty item
- INSERT INTO sl_log_so_balance_warranty_item(
- tenant_id, ou_id, so_id,
- so_warranty_item_id, so_item_id, flg_default_warranty, def_warranty_time,
- warranty_time, warranty_type_id, warranty_sell_qty, warranty_sell_price,
- warranty_sell_amount, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT C.tenant_id, C.ou_id, C.so_id,
- A.so_warranty_item_id, A.so_item_id, A.flg_default_warranty, A.def_warranty_time,
- A.warranty_time, A.warranty_type_id, A.warranty_sell_qty, A.warranty_sell_price,
- A.warranty_sell_amount, 0, vDatetime, vUserId,
- vDatetime, vUserId
- FROM sl_so_warranty_item A, sl_so_item B, sl_so C
- WHERE A.so_item_id = B.so_item_id
- AND B.so_id = C.so_id
- AND C.so_id = vSoId;
- /* GENERATE RGB AND DO */
- SELECT NEXTVAL('in_inventory_seq') INTO vReturnBorrowId;
- SELECT NEXTVAL('sl_do_seq') INTO vDeliveryOrderId;
- -- INSERT ke sl_so_from_dgb_doc_no
- INSERT INTO sl_so_from_dgb_doc_no(
- tenant_id, so_id,
- ref_doc_type_id, ref_doc_no, ref_id, version, create_datetime,
- create_user_id, update_datetime, update_user_id, autonum_id)
- VALUES
- (pTenantId, vSoId, vReturnBorrowDocType, vReturnBorrowDocNo, vReturnBorrowId, 0, vDatetime, vUserId, vDatetime, vUserId, vReturnBorrowNumId),
- (pTenantId, vSoId, vDeliveryOrderDocType, vDeliveryOrderDocNo, vDeliveryOrderId, 0, vDatetime, vUserId, vDatetime, vUserId, vDeliveryOrderNumId)
- ;
- /* GENERATE RGB */
- INSERT INTO in_inventory
- (inventory_id, doc_type_id, doc_no, doc_date,
- tenant_id, ou_from_id, ou_to_id,
- partner_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, warehouse_from_id, warehouse_to_id,
- transfer_date, receive_date, activity_gl_id,
- segment_id, ou_rc_id, status_doc, workflow_status,
- no_vehicle, flg_delivery, delivery_code,
- create_datetime, create_user_id,
- update_datetime, update_user_id,
- version)
- SELECT vReturnBorrowId, vReturnBorrowDocType, vReturnBorrowDocNo, A.doc_date,
- A.tenant_id, A.ou_id, A.ou_id,
- B.partner_id, vEmptyValue, vEmptyValue,
- B.doc_type_id, B.inventory_id, B.warehouse_from_id, B.warehouse_from_id,
- substring(vDatetime from 1 for 6), substring(vDatetime from 1 for 6), -99,
- vEmptyId, vEmptyId, vStatusRelease, vWorkflowApproved,
- B.no_vehicle, B.flg_delivery, B.delivery_code,
- vDatetime, vUserId,
- vDatetime, vUserId,
- vNol
- FROM sl_so A
- INNER JOIN in_inventory B ON B.inventory_id = A.ref_id
- WHERE A.so_id = vSoId AND A.ref_doc_type_id = vDeliveryBorrowDocType;
- INSERT INTO in_inventory_item
- (inventory_id, tenant_id, line_no,
- product_id, product_balance_id, product_status,
- serial_number, product_expired_date, product_year_made,
- lot_number,
- ref_doc_type_id, ref_id, ref_item_id,
- curr_code, amount, base_uom_id,
- qty_request, qty_realization, remark,
- create_datetime, create_user_id,
- update_datetime, update_user_id,
- version)
- SELECT vReturnBorrowId,
- A.tenant_id, A.line_no,
- E.product_id, E.product_balance_id, E.product_status,
- F.serial_number, F.product_expired_date, F.product_year_made,
- vEmptyValue,
- vDeliveryBorrowDocType, B.ref_id, C.inventory_item_id,
- vEmptyValue, vNol, G.base_uom_id,
- A.qty_so, A.qty_so, A.remark,
- vDatetime, vUserId,
- vDatetime, vUserId,
- vNol
- FROM sl_so_item A
- INNER JOIN sl_so B ON B.so_id = A.so_id
- --INNER JOIN in_inventory_item C ON C.inventory_id = B.ref_id AND C.product_id = A.product_id AND
- INNER JOIN in_inventory_item C ON C.inventory_item_id = A.ref_id
- INNER JOIN in_inventory_borrow_balance_item D ON D.inventory_item_id = C.inventory_item_id
- INNER JOIN in_borrowed_product_balance_stock E
- ON E.tenant_id = A.tenant_id AND E.ou_id = B.ou_id AND E.partner_id = D.partner_id AND E.partner_ship_address_id = D.partner_ship_address_id AND E.product_id = C.product_id AND E.product_balance_id = C.product_balance_id AND E.product_status = C.product_status
- INNER JOIN in_product_balance F ON F.product_balance_id = E.product_balance_id
- INNER JOIN m_product G ON G.product_id = E.product_id
- WHERE B.so_id = vSoId AND B.ref_doc_type_id = vDeliveryBorrowDocType;
- UPDATE in_inventory_borrow_balance_item Z
- SET qty_return = (Z.qty_return + A.qty_so),
- status_item = vStatusInProgress,
- update_datetime = vDatetime,
- update_user_id = vUserId,
- version = (Z.version + 1)
- FROM sl_so_item A
- INNER JOIN sl_so B ON B.so_id = A.so_id
- --INNER JOIN in_inventory_item C ON C.inventory_id = B.ref_id AND C.product_id = A.product_id
- INNER JOIN in_inventory_item C ON C.inventory_item_id = A.ref_id
- INNER JOIN in_inventory_borrow_balance_item D ON D.inventory_item_id = C.inventory_item_id
- WHERE B.so_id = vSoId AND B.ref_doc_type_id = vDeliveryBorrowDocType
- AND D.inventory_item_id = Z.inventory_item_id;
- INSERT INTO in_inventory_return_borrowing
- (tenant_id, inventory_id,
- partner_ship_to_id, partner_ship_address_id, partner_cp_id,
- flg_convert_to_sales,
- cp_name, cp_phone1, cp_phone2,
- ref_id, ref_doc_type_id,
- create_datetime, create_user_id,
- update_datetime, update_user_id,
- version)
- SELECT A.tenant_id, vReturnBorrowId,
- B.partner_ship_to_id, B.partner_ship_address_id, B.partner_cp_id,
- vYes,
- C.cp_name, C.phone1, C.phone2,
- vEmptyId, vEmptyId,
- vDatetime, vUserId,
- vDatetime, vUserId,
- vNol
- FROM sl_so A
- INNER JOIN in_inventory_borrow B ON B.inventory_id = A.ref_id
- INNER JOIN m_partner_cp C ON C.partner_cp_id = A.partner_cp_id
- WHERE A.so_id = vSoId AND A.ref_doc_type_id = vDeliveryBorrowDocType;
- /*UPDATE in_product_balance_stock SET qty = qty + B.qty_realization, update_datetime = vDatetime, update_user_id = vUserId
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = vReturnBorrowId AND
- A.inventory_id = B.inventory_id AND
- in_product_balance_stock.tenant_id = A.tenant_id AND
- in_product_balance_stock.warehouse_id = A.warehouse_to_id AND
- in_product_balance_stock.product_id = B.product_id AND
- in_product_balance_stock.product_balance_id = B.product_balance_id AND
- in_product_balance_stock.product_status = B.product_status;*/
- /*
- * insert data in_product_balance_stock
- */
- /*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_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = B.inventory_id AND
- A.inventory_id = vReturnBorrowId AND
- NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
- WHERE C.tenant_id = A.tenant_id AND
- C.warehouse_id = A.warehouse_to_id AND
- C.product_id = B.product_id AND
- C.product_balance_id = B.product_balance_id AND
- C.product_status = B.product_status)
- GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_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_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = vReturnBorrowId AND
- A.inventory_id = B.inventory_id
- GROUP BY A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- INSERT INTO in_log_borrowed_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id,
- doc_no, doc_date, partner_id, partner_ship_address_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.ou_to_id, A.doc_type_id, A.inventory_id,
- A.doc_no, A.doc_date, C.partner_ship_to_id, C.partner_ship_address_id,
- B.product_id, B.product_balance_id, B.product_status,
- B.base_uom_id, B.qty_realization * -1,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item B, in_inventory_return_borrowing C
- WHERE A.inventory_id = vReturnBorrowId AND
- A.inventory_id = B.inventory_id AND
- A.inventory_id = C.inventory_id;
- UPDATE in_inventory_borrow_balance_item A SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE EXISTS (
- SELECT 1 FROM in_inventory_item B WHERE B.inventory_id = vReturnBorrowId AND A.inventory_item_id = B.ref_item_id
- );
- UPDATE in_inventory_borrow_balance_item A SET status_item = vStatusFinal
- WHERE EXISTS (
- SELECT 1 FROM in_inventory_item B WHERE B.inventory_id = vReturnBorrowId AND A.inventory_item_id = B.ref_item_id
- ) AND
- (A.qty_do - A.qty_return - A.qty_sales) <= 0;
- /* GENERATE DO */
- 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,
- eta, eta_day,
- status_doc, workflow_status,
- create_datetime, create_user_id,
- update_datetime, update_user_id,
- version)
- SELECT vDeliveryOrderId, A.tenant_id,
- vDeliveryOrderDocType, vDeliveryOrderDocNo, A.doc_date,
- A.ou_id, A.ext_doc_no, A.ext_doc_date,
- A.doc_type_id, A.so_id,
- A.remark,
- A.partner_ship_to_id, A.partner_ship_address_id,
- B.warehouse_from_id, B.no_vehicle,
- B.flg_delivery, B.delivery_code,
- substring(vDatetime from 1 for 6), 'AFTERLUNCH',
- vStatusRelease, vWorkflowApproved,
- vDatetime, vUserId,
- vDatetime, vUserId,
- vNol
- FROM sl_so A
- INNER JOIN in_inventory B ON B.inventory_id = A.ref_id
- WHERE A.so_id = vSoId AND A.ref_doc_type_id = vDeliveryBorrowDocType;
- 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, product_status,
- create_datetime, create_user_id,
- update_datetime, update_user_id,
- version)
- SELECT B.tenant_id, vDeliveryOrderId, A.line_no,
- A.ref_doc_type_id, A.so_item_id,
- A.product_id,
- A.qty_so, A.so_uom_id,
- A.qty_int, A.base_uom_id,
- A.remark, C.product_status,
- vDatetime, vUserId,
- vDatetime, vUserId,
- vNol
- FROM sl_so_item A
- INNER JOIN sl_so B ON B.so_id = A.so_id
- INNER JOIN in_inventory_item C ON C.inventory_id = B.ref_id AND C.product_id = A.product_id
- WHERE B.so_id = vSoId AND B.ref_doc_type_id = vDeliveryBorrowDocType;
- UPDATE sl_so_balance_item Z
- SET status_item = vStatusInProgress,
- qty_dlv = B.qty_dlv + A.qty_dlv_so,
- qty_dlv_int = B.qty_dlv_int + A.qty_dlv_int,
- update_datetime = vDatetime,
- update_user_id = vUserId,
- version = (Z.version + 1)
- FROM sl_do_item A
- INNER JOIN sl_so_balance_item B ON B.so_item_id = A.ref_id
- INNER JOIN sl_so_item C ON C.so_item_id = B.so_item_id
- INNER JOIN sl_so D ON D.so_id = C.so_id
- WHERE D.so_id = vSoId AND D.ref_doc_type_id = vDeliveryBorrowDocType
- AND B.so_item_id = Z.so_item_id;
- DELETE FROM tt_journal_trx_item WHERE 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 = vDeliveryOrderId INTO result;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- 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 = vDeliveryOrderId 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,
- regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
- "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_and_disc(B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocType, C.curr_code), vRoundingModeNonTax),
- vFlagInvoice, vEmptyId,
- C.discount_amount * B.qty_dlv_so, 0, 0, 0,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so_item C, sl_so D
- WHERE A.do_id = vDeliveryOrderId 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_and_disc(B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocType, 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(vDeliveryOrderDocType, 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 = vDeliveryOrderId 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;
- /*
- * buat data log product balance stock
- * ref item id = do_product_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
- WHERE A.do_id = vDeliveryOrderId AND
- A.do_id = B.do_id AND
- B.do_item_id = C.do_item_id
- 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;
- /*
- * add data balance do item yang akan digunakan di inventory untuk pembuatan return note,
- * saat akan membuat return note
- */
- 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 = vDeliveryOrderId 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 = vDeliveryOrderId 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 = vDeliveryOrderId 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;
- /*
- * modified by Putra Soliman, 07 Maret 2016 untuk improvement Service RMA
- * update sl_so_balance_warranty_item --> status_item F seperti di atas.. untuk item yg sama dengan balance itemnya
- * INSERT ke sl_so_balance_warranty_invoice dan sl_so_balance_warranty_invoice_tax
- */
- INSERT INTO sl_so_balance_warranty_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, warranty_sell_price, warranty_sell_amount, flg_default_warranty, flg_invoice,
- invoice_id, warranty_type_id, flg_invoice_temp, do_receipt_item_id, def_warranty_time, warranty_time,
- 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, E.warranty_sell_price, B.qty_dlv_so * E.warranty_sell_price, E.flg_default_warranty, vFlagInvoice,
- vEmptyId, E.warranty_type_id, vFlagInvoice, vEmptyId, E.def_warranty_time, E.warranty_time,
- 0, vDatetime, vUserId,
- vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so_item C, sl_so D, sl_so_warranty_item E
- WHERE A.do_id = vDeliveryOrderId AND
- A.do_id = B.do_id AND
- B.ref_id = C.so_item_id AND
- C.so_id = D.so_id AND
- E.so_item_id = C.so_item_id;
- INSERT INTO sl_so_balance_warranty_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, warranty_type_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, B.do_item_id, C.tax_id, F.flg_amount,
- C.tax_percentage, C.curr_code,
- f_get_amount_before_tax_and_disc(B.qty_dlv_so * E.warranty_sell_price, vNol, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocType, C.curr_code), vRoundingModeNonTax),
- f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * E.warranty_sell_price, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocType, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
- vFlagInvoice, vEmptyId, E.warranty_type_id,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM sl_do A, sl_do_item B, sl_so_item C, sl_so D, sl_so_warranty_item E, m_tax F
- WHERE A.do_id = vDeliveryOrderId AND
- A.do_id = B.do_id AND
- B.ref_id = C.so_item_id AND
- C.so_id = D.so_id AND
- E.so_item_id = C.so_item_id AND
- F.tax_id = C.tax_id;
- UPDATE sl_so_balance_warranty_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- FROM sl_do_item A, sl_so_warranty_item B, sl_so_balance_item C
- WHERE B.so_item_id = A.ref_id
- AND sl_so_balance_warranty_item.tenant_id = A.tenant_id
- AND A.do_id = vDeliveryOrderId
- AND sl_so_balance_warranty_item.so_warranty_item_id = B.so_warranty_item_id
- AND sl_so_balance_warranty_item.so_item_id = C.so_item_id
- AND C.qty_so - C.qty_cancel + C.qty_add - C.qty_dlv > 0;
- UPDATE sl_so_balance_warranty_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
- FROM sl_do_item A, sl_so_warranty_item B, sl_so_balance_item C
- WHERE sl_so_balance_warranty_item.tenant_id = A.tenant_id
- AND A.do_id = vDeliveryOrderId
- AND sl_so_balance_warranty_item.so_warranty_item_id = B.so_warranty_item_id
- AND sl_so_balance_warranty_item.so_item_id = C.so_item_id
- AND C.qty_so - C.qty_cancel + C.qty_add - C.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;
- /*
- * membuat data transaksi jurnal :
- * 1. buat admin
- * 2. buat temlate jurnal
- */
- 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 = vDeliveryOrderId;
- 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 = vDeliveryOrderId 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 = vDeliveryOrderId 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;
- /* INSERT APPROVAL */
- -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
- SELECT awe_flow_id INTO vFlowReturnBorrowId
- FROM awe_flow
- WHERE scheme = vSchemeReturnBorrow AND
- flg_validate = 'Y' AND
- active = 'Y';
- -- Generate data awe_currdoc_status
- INSERT INTO awe_currdoc_status(
- req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
- remark, current_user_id, current_role_id, flg_user_role, label,
- data, flow_id, create_datetime, create_user_id, create_role_id,
- update_datetime, update_user_id, update_role_id, version)
- SELECT A.inventory_id||'_'||A.doc_no, A.tenant_id, vSchemeReturnBorrow, A.inventory_id, A.doc_no, A.doc_date, vWorkflowApproved,
- A.remark, A.create_user_id, vEmptyId, 'R', 'RETURN GOODS BORROWING'||A.doc_no,
- '{}', vFlowReturnBorrowId, vDatetime, vUserId, vEmptyId,
- vDatetime, vUserId, vEmptyId, 0
- FROM in_inventory A
- WHERE A.inventory_id = vReturnBorrowId;
- -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
- SELECT awe_flow_id INTO vFlowDeliveryOrderId
- FROM awe_flow
- WHERE scheme = vSchemeDeliveryOrder AND
- flg_validate = 'Y' AND
- active = 'Y';
- -- Generate data awe_currdoc_status
- INSERT INTO awe_currdoc_status(
- req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
- remark, current_user_id, current_role_id, flg_user_role, label,
- data, flow_id, create_datetime, create_user_id, create_role_id,
- update_datetime, update_user_id, update_role_id, version)
- SELECT A.do_id||'_'||A.doc_no, A.tenant_id, vSchemeDeliveryOrder, A.do_id, A.doc_no, A.doc_date, vWorkflowApproved,
- A.remark, A.create_user_id, vEmptyId, 'R', 'DELIVERY ORDER'||A.doc_no,
- '{}', vFlowDeliveryOrderId, vDatetime, vUserId, vEmptyId,
- vDatetime, vUserId, vEmptyId, 0
- FROM sl_do A
- WHERE A.do_id = vDeliveryOrderId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement