Advertisement
Guest User

Untitled

a guest
Mar 30th, 2015
226
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.72 KB | None | 0 0
  1.  
  2. CREATE OR REPLACE FUNCTION r_print_delivery_order_xc(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, BIGINT)
  3.   RETURNS SETOF refcursor AS
  4. $BODY$
  5. DECLARE
  6.     pRefHeader              REFCURSOR := 'refHeader';
  7.     pRefDetail              REFCURSOR := 'refDetail';
  8.     pSessionId              ALIAS FOR $1;
  9.     pTenantId               ALIAS FOR $2;
  10.     pUserId                 ALIAS FOR $3;
  11.     pRoleId                 ALIAS FOR $4;
  12.     pDatetime               ALIAS FOR $5;
  13.     pDoId                   ALIAS FOR $6;
  14.    
  15.     vOuBuInfoReport         OU_INFO_REPORT%ROWTYPE;
  16.     RESULT                  record;
  17.    
  18.     vEmptyValue             CHARACTER VARYING(1);
  19.     vAllId                  BIGINT;
  20.     vEmptyId                BIGINT;
  21.    
  22.     vDeliveryOrderDocTypeId BIGINT;
  23.     vRoundingModeNonTax     CHARACTER VARYING(5);
  24.     vSubTotal               NUMERIC;
  25.     vTaxAmount              NUMERIC;
  26.     vGrandTotal             NUMERIC;
  27.     vTotalItemDo            BIGINT;
  28. BEGIN
  29.    
  30.     vEmptyValue := ' ';
  31.     vAllId := -99;
  32.     vEmptyId := -99;
  33.     vSubTotal := 0;
  34.     vTaxAmount := 0;
  35.     vGrandTotal := 0;
  36.    
  37.     vDeliveryOrderDocTypeId := 311;
  38.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  39.  
  40.     SELECT SUM(f_get_amount_before_tax(B.qty_dlv_int * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, D.curr_code), vRoundingModeNonTax)) INTO vSubTotal
  41.     FROM sl_do_item B
  42.     INNER JOIN sl_so_item D ON  D.so_item_id = B.ref_id
  43.     WHERE B.do_id = pDoId;
  44.    
  45.     IF EXISTS (SELECT 1 FROM sl_do_item B INNER JOIN sl_so_item D ON  D.so_item_id = B.ref_id WHERE B.do_id = pDoId AND D.tax_percentage <> 0) THEN
  46.         SELECT f_tax_rounding(pTenantId, SUM(f_get_amount_before_tax(B.qty_dlv_int * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, D.curr_code), vRoundingModeNonTax)), D.tax_percentage) INTO vTaxAmount
  47.         FROM sl_do_item B
  48.         INNER JOIN sl_so_item D ON  D.so_item_id = B.ref_id
  49.         WHERE B.do_id = pDoId
  50.         AND D.tax_percentage <> 0
  51.         GROUP BY D.tax_percentage;
  52.     ELSE
  53.         vTaxAmount := 0;
  54.     END IF;
  55.    
  56.     vGrandTotal := vSubTotal + vTaxAmount;
  57.    
  58.     SELECT COUNT(1) INTO vTotalItemDo
  59.     FROM sl_do_item A
  60.     WHERE A.do_id = pDoId;
  61.    
  62.     SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
  63.     FROM sl_do A
  64.     WHERE A.do_id = pDoId INTO RESULT ;
  65.     vOuBuInfoReport := RESULT.info;
  66.    
  67.     OPEN pRefHeader FOR
  68.     SELECT f_get_ou_name(A.ou_id) AS main_ou, A.workflow_status AS status_doc,
  69.            A.doc_no AS delivery_order_no, A.doc_date AS delivery_order_date,
  70.            B.doc_no AS sales_order_no, B.doc_date AS sales_order_date,
  71.            f_get_warehouse_name(A.warehouse_id) AS warehouse_from,
  72.            f_get_partner_name(A.partner_ship_to_id) AS customer_name,
  73.            f_get_partner_cp_name(B.partner_ship_cp_id) AS customer_contact_person,
  74.            B.due_date AS expired_date,
  75.            COALESCE(C.address1, ' ') AS customer_ship_to_1,
  76.            COALESCE(C.address2 || ' ' || C.address3, ' ') AS customer_ship_to_2,
  77.            COALESCE(C.city || ' - ' || C.zip_code, ' ') AS customer_ship_to_3,
  78.            B.ext_doc_no AS customer_po_no, B.ext_doc_date AS customer_po_date,
  79.            A.remark AS remark, A.no_vehicle AS vehicle_no, A.flg_delivery AS delivery_method,
  80.            f_get_user_fullname(A.create_user_id) AS create_by, A.create_datetime AS created_time,
  81.            f_get_user_fullname(A.update_user_id) AS approved_by, A.update_datetime AS approved_time,
  82.            vOuBuInfoReport.city AS city,
  83.            vSubTotal AS total_item_do, vTaxAmount AS total_gov_tax, vGrandTotal AS grand_total, B.curr_code AS curr_code
  84.     FROM sl_do A
  85.     INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
  86.     LEFT OUTER JOIN m_partner_address C  ON A.partner_ship_address_id = C.partner_address_id
  87.     WHERE A.tenant_id = pTenantId AND A.do_id = pDoId;
  88.            
  89.     RETURN NEXT pRefHeader;
  90.  
  91.     OPEN pRefDetail FOR
  92.     SELECT f_get_product_code(A.product_id) || ' - ' || f_get_product_name(A.product_id) AS product_name,
  93.            C.serial_number AS serial_no, A.qty_dlv_int AS qty_delivery, f_get_uom_name(A.base_uom_id) AS uom_delivery,
  94.            B.line_no AS line_no,
  95.            f_get_amount_before_tax((D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, D.curr_code), vRoundingModeNonTax) AS price,
  96.            f_get_amount_before_tax(A.qty_dlv_int * (D.gross_sell_price - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, D.curr_code), vRoundingModeNonTax) AS amount
  97.     FROM sl_do_product A
  98.     INNER JOIN sl_do_item B ON A.do_item_id = B.do_item_id
  99.     INNER JOIN in_product_balance C ON A.product_balance_id = C.product_balance_id
  100.     INNER JOIN sl_so_item D ON  D.so_item_id = B.ref_id
  101.     WHERE B.do_id = pDoId
  102.     ORDER BY B.line_no, product_name, serial_no;
  103.    
  104.     RETURN NEXT pRefDetail;
  105.    
  106.    
  107. END;
  108. $BODY$
  109.   LANGUAGE plpgsql VOLATILE
  110.   COST 100
  111.   ROWS 1000;
  112. /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement