Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_print_delivery_order_xc(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, BIGINT)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pDoId ALIAS FOR $6;
- vOuBuInfoReport OU_INFO_REPORT%ROWTYPE;
- RESULT record;
- vEmptyValue CHARACTER VARYING(1);
- vAllId BIGINT;
- vEmptyId BIGINT;
- vDeliveryOrderDocTypeId BIGINT;
- vRoundingModeNonTax CHARACTER VARYING(5);
- vSubTotal NUMERIC;
- vTaxAmount NUMERIC;
- vGrandTotal NUMERIC;
- vTotalItemDo BIGINT;
- BEGIN
- vEmptyValue := ' ';
- vAllId := -99;
- vEmptyId := -99;
- vSubTotal := 0;
- vTaxAmount := 0;
- vGrandTotal := 0;
- vDeliveryOrderDocTypeId := 311;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- 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
- FROM sl_do_item B
- INNER JOIN sl_so_item D ON D.so_item_id = B.ref_id
- WHERE B.do_id = pDoId;
- 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
- 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
- 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
- GROUP BY D.tax_percentage;
- ELSE
- vTaxAmount := 0;
- END IF;
- vGrandTotal := vSubTotal + vTaxAmount;
- SELECT COUNT(1) INTO vTotalItemDo
- FROM sl_do_item A
- WHERE A.do_id = pDoId;
- SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
- FROM sl_do A
- WHERE A.do_id = pDoId INTO RESULT ;
- vOuBuInfoReport := RESULT.info;
- OPEN pRefHeader FOR
- SELECT f_get_ou_name(A.ou_id) AS main_ou, A.workflow_status AS status_doc,
- A.doc_no AS delivery_order_no, A.doc_date AS delivery_order_date,
- B.doc_no AS sales_order_no, B.doc_date AS sales_order_date,
- f_get_warehouse_name(A.warehouse_id) AS warehouse_from,
- f_get_partner_name(A.partner_ship_to_id) AS customer_name,
- f_get_partner_cp_name(B.partner_ship_cp_id) AS customer_contact_person,
- B.due_date AS expired_date,
- COALESCE(C.address1, ' ') AS customer_ship_to_1,
- COALESCE(C.address2 || ' ' || C.address3, ' ') AS customer_ship_to_2,
- COALESCE(C.city || ' - ' || C.zip_code, ' ') AS customer_ship_to_3,
- B.ext_doc_no AS customer_po_no, B.ext_doc_date AS customer_po_date,
- A.remark AS remark, A.no_vehicle AS vehicle_no, A.flg_delivery AS delivery_method,
- f_get_user_fullname(A.create_user_id) AS create_by, A.create_datetime AS created_time,
- f_get_user_fullname(A.update_user_id) AS approved_by, A.update_datetime AS approved_time,
- vOuBuInfoReport.city AS city,
- vSubTotal AS total_item_do, vTaxAmount AS total_gov_tax, vGrandTotal AS grand_total, B.curr_code AS curr_code
- FROM sl_do A
- INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
- LEFT OUTER JOIN m_partner_address C ON A.partner_ship_address_id = C.partner_address_id
- WHERE A.tenant_id = pTenantId AND A.do_id = pDoId;
- RETURN NEXT pRefHeader;
- OPEN pRefDetail FOR
- SELECT f_get_product_code(A.product_id) || ' - ' || f_get_product_name(A.product_id) AS product_name,
- C.serial_number AS serial_no, A.qty_dlv_int AS qty_delivery, f_get_uom_name(A.base_uom_id) AS uom_delivery,
- B.line_no AS line_no,
- 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,
- 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
- FROM sl_do_product A
- INNER JOIN sl_do_item B ON A.do_item_id = B.do_item_id
- INNER JOIN in_product_balance C ON A.product_balance_id = C.product_balance_id
- INNER JOIN sl_so_item D ON D.so_item_id = B.ref_id
- WHERE B.do_id = pDoId
- ORDER BY B.line_no, product_name, serial_no;
- RETURN NEXT pRefDetail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement