f_generate_return_note

CREATE OR REPLACE FUNCTION f_generate_return_note(bigint, bigint, character varying)
  RETURNS VOID AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pUserId             ALIAS FOR $2;
    pDatetime           ALIAS FOR $3;

    vNullRefId          bigint := -99;
    vFlgNo              character varying(1) :='N';
    vFlgYes             character varying(1) :='Y';

    vFlgInProgress      character varying(1) :='I';

    vRnDocTypeId        bigint := 502;
    vEmptyString        character varying :='';
    vSpaceString        character varying :=' ';
    vSoDocTypeId        bigint := 301;
    vDoDocTypeId        bigint := 311;
    vWarehouseId        bigint;
    vStatusDraft            character varying(1) :='D';
    vProductStatusReturn    character varying(1) :='RETURN';
    vDraft                  character varying(10) :='DRAFT';
BEGIN

    SELECT warehouse_id INTO vWarehouseId
    FROM m_warehouse
    WHERE warehouse_code = '';

    --ambil informasi product nya
    UPDATE tt_generate_return_note_return_purch_invoice Z
    SET product_id = A.product_id,
        product_balance_id = B.product_balance_id,
        base_uom_id = A.base_uom_id
    FROM m_product A
    INNER JOIN in_product_balance B ON B.product_id = A.product_id AND
                                       B.serial_number = vSpaceString AND
                                       B.lot_number = vSpaceString
    WHERE A.product_code = Z.product_code;

    --ambil informasi SO sampai Sales Invoice
    UPDATE tt_generate_return_note_return_purch_invoice Z
    SET so_id = A.ref_id,
        si_partner_id = A.partner_id,
        si_tax_no = B.tax_no,
        si_ou_id = Z.ou_id,
        do_id = D.do_id,
        do_item_id = D.do_item_id,
        curr_code = A.curr_code
    FROM sl_invoice A
    INNER JOIN sl_invoice_item C ON C.invoice_id = A.invoice_id
    INNER JOIN sl_do_item D ON D.do_item_id = C.ref_item_id AND C.ref_doc_type_id =  vDoDocTypeId
    LEFT JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
    WHERE A.doc_no = Z.sales_invoice_no AND
          A.doc_date =Z.sales_invoice_date AND
          C.product_id = Z.product_id;

    WITH return_note AS (
        SELECT sales_invoice_no, nrb_no, NEXTVAL('in_inventory_seq') AS return_note_id
        FROM tt_generate_return_note_return_purch_invoice
        WHERE Z.flg_process_return_note = vFlgNo AND si_id = vNullRefId AND po_id = vNullRefId
        GROUP BY sales_invoice_no, nrb_no;
    )
    UPDATE tt_generate_return_note_return_purch_invoice Z
    SET return_note_id = return_note_id,
        flg_process_return_note = vFlgInProgress
    FROM return_note A
    WHERE A.sales_invoice_no = Z.sales_invoice_no AND
          A.nrb_no = Z.nrb_no;

    UPDATE tt_generate_return_note_return_purch_invoice Z
    SET return_note_item_id = NEXTVAL('in_inventory_item_seq')
    WHERE flg_process_return_note = vFlgInProgress;


    INSERT INTO in_inventory(
            inventory_id, tenant_id, doc_type_id, doc_no, doc_date, ou_from_id,
            ou_to_id, partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id,
            ref_id, remark, warehouse_from_id, warehouse_to_id, transfer_date,
            receive_date, activity_gl_id, ou_rc_id, no_vehicle, flg_delivery,
            delivery_code, status_doc, workflow_status, version, create_datetime,
            create_user_id, update_datetime, update_user_id, segment_id,
            group_product_id)
    SELECT  return_note_id, pTenantId, vRnDocTypeId, nrb_no ||'.RN',doc_date, si_ou_id,
            si_ou_id, si_partner_id, vEmptyString, vEmptyString, vSoDocTypeId,
            so_id, 'GENERATE DARI SISTEM  '||draft_ref_no, vWarehouseId, vWarehouseId, doc_date,
            doc_date, vStatusDraft, vDraft, 0, pDatetime,
            pUserId, pDatetime, pUserId, pDatetime, vNullRefId,
            vNullRefId
    FROM tt_generate_return_note_return_purch_invoice
    WHERE flg_process_return_note = vFlgInProgress
    GROUP BY return_note_id, nrb_no, si_ou_id, si_partner_id, so_id, draft_ref_no, doc_date, doc_date;

    INSERT INTO in_inventory_item(
            inventory_item_id, tenant_id, inventory_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, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
    SELECT  return_note_item_id, pTenantId, return_note_id, ROW_NUMBER() OVER(PARTITION BY return_note_id, ORDER BY product_id), product_id,
            product_balance_id, vProductStatusGood, vSpaceString, vSpaceString,
            vSpaceString, vSpaceString, vDoDocTypeId, do_id, do_item_id,
            curr_code, 0, base_uom_id, qty_return, qty_return,
            vEmptyString, 0, pDatetime, pUserId, pDatetime,
            pUserId
    FROM tt_generate_return_note_return_purch_invoice
    WHERE flg_process_return_note = vFlgInProgress;

    INSERT INTO in_return_note_for_finance(
            inventory_id, flg_for_finance, so_id, po_id, create_datetime,
            create_user_id, update_datetime, update_user_id, version)
    SELECT inventory_id, vFlgYes, so_id, po_id, pDatetime,
            pUserId, pDatetime, pUserId, 0
    FROM tt_generate_return_note_return_purch_invoice
    WHERE flg_process_return_note = vFlgInProgress
    GROUP BY inventory_id;


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/