tercnem

Untitled

Sep 22nd, 2020
832
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION rpt.f_print_invoice_pos(
  2.     character varying,
  3.     bigint,
  4.     bigint,
  5.     character varying)
  6.   RETURNS SETOF refcursor AS
  7. $BODY$
  8. DECLARE
  9.     pRefHeader                  REFCURSOR := 'refHeader';
  10.     pRefDetail                  REFCURSOR := 'refDetail';
  11.    
  12.     pSessionId                  ALIAS FOR $1;
  13.     pRecordOwnerId              ALIAS FOR $2;
  14.     pTrxPosId                   ALIAS FOR $3;
  15.     pDatetime                   ALIAS FOR $4;
  16.  
  17.     vContactCenter              record;
  18.    
  19.     vOuCodeAlias                character varying;
  20.     vPatientCode                character varying;
  21.     vTotalPayment               numeric = 0;
  22.     vSubTotal                   numeric = 0;
  23.     vSubTotalDisc               numeric = 0;
  24.     vSubTotalAfterDisc          numeric = 0;
  25.     vTotalDiscountItem          numeric = 0;
  26.     vTotalDiscountHeader        numeric = 0;
  27.     vTotalAdditionalFee         numeric = 0;
  28.     vTotalNettPaymentAmount     numeric = 0;
  29.    
  30. BEGIN
  31.    
  32.    
  33.     SELECT ou_code_alias INTO vOuCodeAlias FROM m_record_owner WHERE record_owner_id = pRecordOwnerId;
  34.    
  35.     SELECT * FROM m_contact_center WHERE contact_center_code = vOuCodeAlias INTO vContactCenter;
  36.    
  37.     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),
  38.         COALESCE(SUM(item_discount_amount), 0), COALESCE(SUM(discount_header_on_item), 0)
  39.     INTO    vSubTotal, vSubTotalDisc, vSubTotalAfterDisc,
  40.         vTotalDiscountItem, vTotalDiscountHeader
  41.     FROM    trx_pos_item
  42.     WHERE   pos_id = pTrxPosId;
  43.  
  44.     SELECT COALESCE(SUM(payment_amount), 0) INTO vTotalPayment
  45.     FROM trx_pos_payment
  46.     WHERE pos_id = pTrxPosId;
  47.    
  48.     SELECT SUM(additional_fee), SUM(nett_payment_amount) INTO vTotalAdditionalFee, vTotalNettPaymentAmount
  49.     FROM trx_pos_payment
  50.     WHERE pos_id = pTrxPosId;
  51.    
  52.     OPEN pRefHeader FOR
  53.         SELECT vContactCenter.address1, vContactCenter.address2, vContactCenter.address3, vContactCenter.city,
  54.                 vContactCenter.zip_code, trx_pos.create_datetime, vContactCenter.fax1, vContactCenter.fax2,
  55.                 trx_pos.update_username, trx_pos.doc_no, trx_pos.doc_date,f_get_customer_code(trx_pos.customer_id) AS customer_code,
  56.                 f_get_customer_name(trx_pos.customer_id) AS customer_name,
  57.                 vSubTotal AS sub_total, vSubTotalDisc AS sub_total_disc, vSubTotalAfterDisc+vTotalAdditionalFee AS sub_total_after_disc,
  58.                 vTotalPayment AS total_payment, vTotalPayment-(vSubTotalAfterDisc+vTotalAdditionalFee) AS refund_payment,
  59.                 m_promo.promo_code AS promo_code, m_voucher_balance.voucher_no AS voucher_code,
  60.                 B.salutation, B.short_name,
  61.                 TRUNC(vTotalDiscountItem) AS total_discount_item, TRUNC(vTotalDiscountHeader) AS total_discount_header,
  62.                 f_get_trx_pos_payment(pos_id, 'CASH') AS cash_amount, f_get_trx_pos_payment(pos_id, 'DEBIT') AS debit_amount,
  63.                 f_get_trx_pos_payment(pos_id, 'CREDIT_CARD') AS credit_amount, vTotalAdditionalFee AS total_additional_fee,
  64.                 vTotalNettPaymentAmount AS total_nett_payment_amount
  65.         FROM trx_pos
  66.         INNER JOIN m_patient B ON f_get_customer_code(trx_pos.customer_id) = B.patient_code
  67.         LEFT JOIN m_promo ON trx_pos.promo_id = m_promo.promo_id
  68.         LEFT JOIN m_voucher_balance ON trx_pos.voucher_balance_id = m_voucher_balance.voucher_balance_id
  69.         WHERE pos_id = pTrxPosId;
  70.     RETURN NEXT pRefHeader;
  71.    
  72.     OPEN pRefDetail FOR
  73.         SELECT f_get_product_code(product_id) AS product_code, f_get_product_name(product_id) AS product_name, qty,
  74.                 unit_sell_price, item_amount, f_get_salesman_short_name(salesman_id) AS salesman_name,
  75.                 f_get_salesman_short_name(tindakan_by_1_salesman_id) AS salesman_action_1,
  76.                 f_get_salesman_short_name(tindakan_by_2_salesman_id) AS salesman_action_2,
  77.                 f_get_salesman_short_name(tindakan_by_3_salesman_id) AS salesman_action_3,
  78.                 item_remark, TRUNC(item_discount_amount) AS item_discount_amount
  79.         FROM trx_pos_item
  80.         WHERE pos_id = pTrxPosId;
  81.     RETURN NEXT pRefDetail;
  82.    
  83. END;
  84. $BODY$
  85.   LANGUAGE plpgsql VOLATILE
  86.   SET search_path = public, mstr, pj
  87.   COST 100;
  88. /
RAW Paste Data