/*
 * pos receipt note
 * filter mandatory : session_id, tenant_id, pos_id
 */
CREATE OR REPLACE FUNCTION r_pos_receipt_note(CHARACTER VARYING, BIGINT, BIGINT)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pPosId                  ALIAS FOR $3;

    vEmptyValue             CHARACTER VARYING(1);
    vEmptyId                BIGINT;
    vAllId                  BIGINT;

BEGIN

    vEmptyValue := ' ';
    vEmptyId := -99;
    vAllId := -99;

    DELETE FROM tr_receipt_note_item WHERE session_id = pSessionId;

    -- insert semua detail pos item yg dibeli
    INSERT INTO tr_receipt_note_item(
        session_id, tenant_id, pos_item_id, sort_no, group_product_ou_id, group_product_ou_code, group_product_ou_name,
        product_id, product_code, product_name,
        qty, gross_sell_price, discount_amount, nett_amount_item
    )
    SELECT pSessionId, pTenantId, A.pos_item_id, 1, A.group_product_ou_id, C.group_product_ou_code, C.group_product_ou_name,
           A.product_id, B.product_code, B.product_name,
           A.qty, A.gross_sell_price, A.discount_amount, A.nett_amount_item
    FROM p_pos_item A
    INNER JOIN m_product B ON A.tenant_id = B.tenant_id AND A.product_id = B.product_id
    INNER JOIN m_group_product_ou C ON A.tenant_id = C.tenant_id AND A.group_product_ou_id = C.group_product_ou_id
    WHERE A.tenant_id = pTenantId AND
          A.pos_id = pPosId;

    -- insert detail pos item khusus assembly
    INSERT INTO tr_receipt_note_item(
        session_id, tenant_id, pos_item_id, sort_no, group_product_ou_id, group_product_ou_code, group_product_ou_name,
        product_id, product_code, product_name,
        qty, gross_sell_price, discount_amount, nett_amount_item
    )
    SELECT pSessionId, pTenantId, A.pos_item_id, 2, C.group_product_ou_id, D.group_product_ou_code, D.group_product_ou_name,
           A.product_id, B.product_code, B.product_name,
           SUM(A.qty), 0, 0, 0
    FROM p_pos_assembly_product A
    INNER JOIN m_product B ON A.tenant_id = B.tenant_id AND A.product_id = B.product_id
    INNER JOIN p_pos_item C ON A.tenant_id = C.tenant_id AND A.pos_item_id = C.pos_item_id
    INNER JOIN m_group_product_ou D ON C.tenant_id = D.tenant_id AND C.group_product_ou_id = D.group_product_ou_id
    WHERE A.tenant_id = pTenantId AND
          C.pos_id = pPosId
    GROUP BY A.pos_item_id, A.product_id, B.product_code, B.product_name, C.group_product_ou_id, D.group_product_ou_code, D.group_product_ou_name;

    OPEN pRefHeader FOR
    SELECT A.doc_no AS doc_no, A.doc_date AS doc_date, B.partner_name AS salesman_name, A.create_username AS full_name
    FROM p_pos A
    INNER JOIN m_partner B ON A.tenant_id = B.tenant_id AND A.salesman_id = B.partner_id
    WHERE A.tenant_id = pTenantId
    AND A.pos_id = pPosId;

    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR
    SELECT A.pos_item_id AS id, A.group_product_ou_name AS dept_code, A.product_code AS product_code, A.product_name AS product_name, A.qty AS qty, A.gross_sell_price AS gross_sell_price, A.discount_amount AS discount_amount, A.nett_amount_item AS sub_total
    FROM tr_receipt_note_item A
    WHERE A.session_id = pSessionId AND
          A.tenant_id = pTenantId
    ORDER BY A.group_product_ou_code, A.pos_item_id, A.sort_no, A.product_code, A.product_name, A.qty, A.gross_sell_price, A.discount_amount, A.nett_amount_item;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_receipt_note_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;