r_pos_receipt_note_spooring

/*
 * pos receipt note spooring
 * filter mandatory : session_id, tenant_id, ou_id, pos_id, total_payment, cash
 */
CREATE OR REPLACE FUNCTION r_pos_receipt_note_spooring(character varying, bigint, bigint, bigint, double precision, character varying(1))
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pOuId                   ALIAS FOR $3;
    pPosId                  ALIAS FOR $4;
    pTotalPayment           ALIAS FOR $5;
    pCash                   ALIAS FOR $6;

    vEmptyValue             character varying(1);
    vEmptyId                bigint;
    vAllId                  bigint;
    vTotalAmount            numeric;
    vRefund                 numeric;
    vTotalItem              numeric;
    vOutletName             character varying;

BEGIN

    vEmptyValue := ' ';
    vEmptyId := -99;
    vAllId := -99;
    vTotalAmount := 0;
    vRefund := 0;
    vTotalItem := 0;
    vOutletName := ' ';

    DELETE FROM tr_receipt_note_item WHERE session_id = pSessionId;

    -- get outllet name
    SELECT f_get_ou_name(pOuId) INTO vOutletName;

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

    -- insert semua detail pos item nempil 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_nempil_barang_id, 3, vEmptyId, vEmptyValue, vEmptyValue,
           B.nempil_barang_balance_stock_id, A.product_code, B.product_name,
           A.qty, A.gross_sell_price, 0, A.sell_price
    FROM p_pos_item_nempil_barang A
    INNER JOIN p_nempil_barang_balance_stock B ON A.tenant_id = B.tenant_id AND A.product_code = B.product_code
    WHERE A.tenant_id = pTenantId AND
          A.pos_id = pPosId;

    -- sum total amount
    SELECT SUM(A.nett_amount_item) INTO vTotalAmount
    FROM tr_receipt_note_item A WHERE A.session_id = pSessionId;

    -- calculate refund
    vRefund := pTotalPayment - vTotalAmount;
    IF vRefund < 0 THEN
        vRefund := 0;
    END IF;

    -- sum total quantity
    SELECT SUM(A.qty) INTO vTotalItem
    FROM tr_receipt_note_item A WHERE A.session_id = pSessionId;

    Open pRefHeader FOR
    SELECT A.doc_no AS doc_no, B.partner_name AS salesman_name, A.create_username AS full_name,
           C.partner_name AS partner_name, D.license_number || ' ' || D.owner_name AS license_number,
           C.due_date AS due_date, C.customer_name AS customer_name, CAST(vTotalAmount AS character varying) AS total_amount,
           CAST(vTotalItem AS character varying) AS total_item, CAST(pTotalPayment AS character varying) AS total_payment,
           CAST(vRefund AS character varying) AS refund, pCash AS cash, vOutletName AS outlet_name,
           CAST(COALESCE(E.under_payment_amount, 0) AS character varying) AS under_payment_amount,
           TO_CHAR(TO_DATE(C.due_date, 'YYYYMMDD'), 'DD-Mon-YYYY') AS due_date_lt, CAST(C.vehicle_km AS character varying) AS vehicle_km
    FROM p_pos A
    INNER JOIN m_partner B ON A.tenant_id = B.tenant_id AND A.salesman_id = B.partner_id
    INNER JOIN p_pos_ext C ON A.pos_id = C.pos_id
    INNER JOIN m_vehicle_customer_own D ON C.vehicle_code = D.vehicle_code
    LEFT OUTER JOIN p_pos_termin_payment E ON A.pos_id = E.pos_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, CAST(A.qty AS character varying) AS qty,
    A.nett_amount_item/A.qty AS gross_sell_price,
    CASE WHEN A.discount_amount < 0 THEN '0' ELSE CAST(A.discount_amount AS character varying) END AS discount_amount,
    CAST(A.nett_amount_item AS character varying) 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;