sv_submit_product_usage_for_service

-- Function: sv_submit_product_usage_for_service(bigint, character varying, character varying)

-- DROP FUNCTION sv_submit_product_usage_for_service(bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION sv_submit_product_usage_for_service(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId                   ALIAS FOR $1;
    pSessionId                  ALIAS FOR $2;
    pProcessNo                  ALIAS FOR $3;

    vProcessId                  bigint;
    vProductUsageId             bigint;
    vUserId                     bigint;
    vDatetime                   character varying(14);
    vEmptyId                    bigint := -99;
    vJournalTrxId               bigint := -99;
    vOne                        numeric := 1;
    vEmptyValue                 character varying := '';
    vOuId                       bigint;
    vOuWarehouseId              bigint;
    vOuStructureJournalItem     OU_BU_STRUCTURE%ROWTYPE;

    vDocJournal                 DOC_JOURNAL%ROWTYPE;
    vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
    result                      RECORD;
    vProductUsageDocNo          character varying(100);
    vProductUsageDocDate        character varying(8);
    vReason                     character varying(1);
    vReasonKanibal              character varying := 'K';
    vReasonReplacement          character varying := 'R';
    vSignCredit                 character varying := 'K';
    vSignDebit                  character varying := 'D';
    vYes                        character varying := 'Y';
    vStatusDraft                character varying := 'D';
    vStatusRelease              character varying := 'R';
    vProductCOA                 character varying := 'PRODUCT';
    vTypeRate                   character varying := 'COM';
    vActivityCOA                character varying := 'ACTIVITY';
    vWarehouseReplacementId     bigint;
    vQtyPlusOne                 numeric := 1;
    vQtyMinusOne                numeric := -1;
BEGIN
    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'sv_submit_product_usage_for_service' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vProductUsageId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'productUsageId';

    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';

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, A.doc_no, A.doc_date, A.reason,
            B.warehouse_id
    FROM sv_product_usage A
    INNER JOIN sv_product_usage_item B ON A.tenant_id = B.tenant_id AND A.product_usage_id = B.product_usage_id
    WHERE A.product_usage_id = vProductUsageId INTO result;

    vOuStructure := result.ou;
    vDocJournal := result.doc;
    vProductUsageDocNo := result.doc_no;
    vProductUsageDocDate := result.doc_date;
    vReason := result.reason;
    vWarehouseReplacementId := result.warehouse_id;

    RAISE NOTICE 'results : %',result;

    UPDATE sv_product_usage SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE product_usage_id = vProductUsageId;

    SELECT A.ou_from_id, C.ou_id INTO vOuId, vOuWarehouseId
    FROM sv_product_usage A
    INNER JOIN sv_product_usage_item B ON A.tenant_id = B.tenant_id AND A.product_usage_id = B.product_usage_id
    INNER JOIN m_warehouse_ou C ON B.warehouse_id = C.warehouse_id
    WHERE A.product_usage_id = vProductUsageId;

    IF (vOuId <> vOuWarehouseId) THEN
        SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
        vOuStructureJournalItem := result.ou_structure;
    ELSE
        vOuStructureJournalItem := ROW(-99, -99, -99);
    END IF;


    --replacement
    -- plus barang masuk bekas/rusak in_product_balance_stock
    -- tulis log product balance stock
    -- update serial number current yang ada di balance rma + log --

--  IF vReason = vReasonReplacement THEN
--      RAISE NOTICE 'REPLACEMENT';
--
    UPDATE in_product_balance_stock
    SET qty = in_product_balance_stock.qty + 1,
        update_datetime = vDatetime, update_user_id = vUserId, version = in_product_balance_stock.version + 1
    FROM sv_product_usage A
    INNER JOIN in_product_balance B ON A.tenant_id = B.tenant_id AND A.serial_number_service = B.serial_number
    WHERE in_product_balance_stock.tenant_id = A.tenant_id
        AND in_product_balance_stock.product_balance_id = B.product_balance_id
        AND in_product_balance_stock.warehouse_id = A.warehouse_from_id
        AND A.product_usage_id = vProductUsageId;

    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_from_id, B.product_id, B.product_balance_id, A.product_status, C.base_uom_id, vOne,
            0, vDatetime, vUserId, vDatetime, vUserId
    FROM sv_product_usage A
    INNER JOIN in_product_balance B ON A.tenant_id = B.tenant_id AND A.serial_number_service = B.serial_number
    INNER JOIN m_product C ON B.product_id = C.product_id
    WHERE A.product_usage_id = vProductUsageId AND
        NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
                    WHERE C.tenant_id = A.tenant_id AND
                        C.warehouse_id = A.warehouse_from_id AND
                        C.product_id = B.product_id AND
                        C.product_balance_id = B.product_balance_id AND
                        C.product_status = A.product_status);

    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, B.ou_from_id, B.doc_type_id,
        B.product_usage_id, B.doc_no, B.doc_date, B.partner_id, A.product_id, A.warehouse_id,
        A.product_balance_id, A.product_status, A.base_uom_id, vOne, A.version,
        vDatetime, vUserId, vDatetime, vUserId
    FROM in_product_balance_stock A
    INNER JOIN sv_product_usage B ON A.tenant_id = B.tenant_id  AND A.product_status = B.product_status
    INNER JOIN in_product_balance C ON A.tenant_id = C.tenant_id AND B.serial_number_service = C.serial_number AND A.product_balance_id = C.product_balance_id
    WHERE A.product_status = B.product_status
        AND A.warehouse_id = B.warehouse_from_id
        AND B.product_usage_id = vProductUsageId;

    --Adrian, Nov 17, 2017
    --Insert in_log_product_balance_stock untuk product pengganti
    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 B.tenant_id, A.ou_from_id, A.doc_type_id,
        B.product_usage_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, B.warehouse_id,
        B.product_balance_id, B.product_status, B.base_uom_id, -1 * vOne, B.version,
        vDatetime, vUserId, vDatetime, vUserId
    FROM sv_product_usage A
    INNER JOIN sv_product_usage_item B ON A.product_usage_id = B.product_usage_id
    INNER JOIN in_product_balance C ON B.product_balance_id = C.product_balance_id
    INNER JOIN in_product_balance_stock D ON B.tenant_id = D.tenant_id
        AND D.warehouse_id = A.warehouse_from_id
        AND B.product_id = D.product_id
        AND C.product_balance_id = D.product_balance_id
        AND B.product_status = D.product_status
    WHERE B.product_usage_id = vProductUsageId;

    UPDATE sv_product_warranty_balance
    SET product_balance_current_id = B.product_balance_id,
        product_current_id = B.product_id,
        flg_substitute = vYes,
        version = sv_product_warranty_balance.version + 1,
        update_user_id = vUserId,
        update_datetime = vDatetime
    FROM sv_product_usage A
    INNER JOIN sv_product_usage_item B ON A.tenant_id = B.tenant_id AND A.product_usage_id = B.product_usage_id
    INNER JOIN in_product_balance C ON A.tenant_id = C.tenant_id AND A.serial_number_service = C.serial_number
    WHERE A.product_usage_id = vProductUsageId
        AND sv_product_warranty_balance.invoice_id = A.ref_id
        AND sv_product_warranty_balance.product_current_id = A.product_service_id
        AND sv_product_warranty_balance.product_balance_current_id = C.product_balance_id;

    INSERT INTO sv_log_product_warranty_balance(
        product_warranty_balance_id, tenant_id, ou_id, product_id, product_balance_id,
        qty, product_current_id, product_balance_current_id, flg_substitute,
        invoice_id, invoice_doc_no, invoice_doc_date, do_item_id, do_doc_no,
        do_doc_date, partner_id, partner_cp_id, partner_bill_to_id, partner_ship_to_id,
        partner_ship_address_id, flg_replacement, status_item, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.product_warranty_balance_id, A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
            A.qty, A.product_current_id, A.product_balance_current_id, A.flg_substitute,
            A.invoice_id, A.invoice_doc_no, A.invoice_doc_date, A.do_item_id, A.do_doc_no,
            A.do_doc_date, A.partner_id, A.partner_cp_id, A.partner_bill_to_id, A.partner_ship_to_id,
            A. partner_ship_address_id, A.flg_replacement, A.status_item, A.version,
            vDatetime, vUserId, vDatetime, vUserId
    FROM sv_product_warranty_balance A
    INNER JOIN sv_product_usage B ON A.invoice_id = B.ref_id
                                    AND A.tenant_id = B.tenant_id
                                    AND A.ou_id = B.ou_from_id
                                    AND A.product_current_id = B.product_service_id
    INNER JOIN sv_product_usage_item C ON B.tenant_id = C.tenant_id
                                    AND B.product_usage_id = C.product_usage_id
                                    AND A.product_balance_current_id = C.product_balance_id
    WHERE B.product_usage_id = vProductUsageId;

--  END IF;
--
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
    FROM sv_product_usage A
    WHERE A.product_usage_id = vProductUsageId;

    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.product_usage_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sv_product_usage A
    WHERE A.product_usage_id = vProductUsageId;

    -- product yang didapat dari customer dari service
    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, A.product_usage_id,
        A.partner_id, A.product_service_id, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, A.product_service_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), vQtyPlusOne, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', A.remark
    FROM sv_product_usage A
    INNER JOIN m_product B ON A.tenant_id = B.tenant_id AND A.product_service_id = B.product_id
    WHERE A.product_usage_id = vProductUsageId;

    -- product yang dikeluarkan dari stock untuk service
    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.product_usage_item_id,
        A.partner_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'), ABS(vQtyPlusOne), C.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM sv_product_usage A
    INNER JOIN sv_product_usage_item B ON A.tenant_id = B.tenant_id AND A.product_usage_id = B.product_usage_id
    INNER JOIN m_product C ON B.tenant_id = C.tenant_id AND B.product_id = C.product_id
    WHERE A.product_usage_id = vProductUsageId;

    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,
        vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
        A.segment_id, vSignCredit, vActivityCOA, A.activity_gl_id,
        D.coa_id, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
        0 , A.doc_date, vTypeRate,
        1, 1, 'ADJUSMENT_STOCK', vEmptyValue
    FROM sv_product_usage A, m_activity_gl D
    WHERE A.product_usage_id = vProductUsageId AND
        A.activity_gl_id = D.activity_gl_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,
    ou_branch_id, ou_sub_bu_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,
        (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'PRODUCT_STOCK';

    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),
        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 AND
        A.journal_desc = 'ADJUSMENT_STOCK';

--  DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sv_submit_product_usage_for_service(bigint, character varying, character varying)
  OWNER TO postgres;