Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION rpt.f_print_invoice_pos(
- character varying,
- bigint,
- bigint,
- character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pRecordOwnerId ALIAS FOR $2;
- pTrxPosId ALIAS FOR $3;
- pDatetime ALIAS FOR $4;
- vContactCenter record;
- vOuCodeAlias character varying;
- vPatientCode character varying;
- vTotalPayment numeric = 0;
- vSubTotal numeric = 0;
- vSubTotalDisc numeric = 0;
- vSubTotalAfterDisc numeric = 0;
- vTotalDiscountItem numeric = 0;
- vTotalDiscountHeader numeric = 0;
- vTotalAdditionalFee numeric = 0;
- vTotalNettPaymentAmount numeric = 0;
- BEGIN
- SELECT ou_code_alias INTO vOuCodeAlias FROM m_record_owner WHERE record_owner_id = pRecordOwnerId;
- SELECT * FROM m_contact_center WHERE contact_center_code = vOuCodeAlias INTO vContactCenter;
- SELECT COALESCE(SUM(item_amount), 0), COALESCE(SUM(item_discount_amount), 0) + COALESCE(SUM(discount_header_on_item), 0), COALESCE(SUM(item_amount_after_discount), 0) - COALESCE(SUM(discount_header_on_item), 0),
- COALESCE(SUM(item_discount_amount), 0), COALESCE(SUM(discount_header_on_item), 0)
- INTO vSubTotal, vSubTotalDisc, vSubTotalAfterDisc,
- vTotalDiscountItem, vTotalDiscountHeader
- FROM trx_pos_item
- WHERE pos_id = pTrxPosId;
- SELECT COALESCE(SUM(payment_amount), 0) INTO vTotalPayment
- FROM trx_pos_payment
- WHERE pos_id = pTrxPosId;
- SELECT SUM(additional_fee), SUM(nett_payment_amount) INTO vTotalAdditionalFee, vTotalNettPaymentAmount
- FROM trx_pos_payment
- WHERE pos_id = pTrxPosId;
- OPEN pRefHeader FOR
- SELECT vContactCenter.address1, vContactCenter.address2, vContactCenter.address3, vContactCenter.city,
- vContactCenter.zip_code, trx_pos.create_datetime, vContactCenter.fax1, vContactCenter.fax2,
- trx_pos.update_username, trx_pos.doc_no, trx_pos.doc_date,f_get_customer_code(trx_pos.customer_id) AS customer_code,
- f_get_customer_name(trx_pos.customer_id) AS customer_name,
- vSubTotal AS sub_total, vSubTotalDisc AS sub_total_disc, vSubTotalAfterDisc+vTotalAdditionalFee AS sub_total_after_disc,
- vTotalPayment AS total_payment, vTotalPayment-(vSubTotalAfterDisc+vTotalAdditionalFee) AS refund_payment,
- m_promo.promo_code AS promo_code, m_voucher_balance.voucher_no AS voucher_code,
- B.salutation, B.short_name,
- TRUNC(vTotalDiscountItem) AS total_discount_item, TRUNC(vTotalDiscountHeader) AS total_discount_header,
- f_get_trx_pos_payment(pos_id, 'CASH') AS cash_amount, f_get_trx_pos_payment(pos_id, 'DEBIT') AS debit_amount,
- f_get_trx_pos_payment(pos_id, 'CREDIT_CARD') AS credit_amount, vTotalAdditionalFee AS total_additional_fee,
- vTotalNettPaymentAmount AS total_nett_payment_amount
- FROM trx_pos
- INNER JOIN m_patient B ON f_get_customer_code(trx_pos.customer_id) = B.patient_code
- LEFT JOIN m_promo ON trx_pos.promo_id = m_promo.promo_id
- LEFT JOIN m_voucher_balance ON trx_pos.voucher_balance_id = m_voucher_balance.voucher_balance_id
- WHERE pos_id = pTrxPosId;
- RETURN NEXT pRefHeader;
- OPEN pRefDetail FOR
- SELECT f_get_product_code(product_id) AS product_code, f_get_product_name(product_id) AS product_name, qty,
- unit_sell_price, item_amount, f_get_salesman_short_name(salesman_id) AS salesman_name,
- f_get_salesman_short_name(tindakan_by_1_salesman_id) AS salesman_action_1,
- f_get_salesman_short_name(tindakan_by_2_salesman_id) AS salesman_action_2,
- f_get_salesman_short_name(tindakan_by_3_salesman_id) AS salesman_action_3,
- item_remark, TRUNC(item_discount_amount) AS item_discount_amount
- FROM trx_pos_item
- WHERE pos_id = pTrxPosId;
- RETURN NEXT pRefDetail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- SET search_path = public, mstr, pj
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement