-- modified by PS, 09 March 2015 delete approval_name
-- modified by PS, 13 July 2015 add ou_name in refHeader
CREATE OR REPLACE FUNCTION r_print_sales_invoice_temporary(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;
	pInvoiceTempId			ALIAS FOR $6;

	vFormTemporarySalesInvoice  character varying := 'FormTemporarySalesInvoice';
	vTermsAndConditions			character varying := 'terms_and_conditions';
	vBankAcc1					character varying := 'bank_acc_1';
	vBankAcc2					character varying := 'bank_acc_2';
	vDoDocTypeId				bigint;
	vReportName					character varying;
	vDoNo						character varying;
	vAdditionalCost				numeric;
	vDownPaymentAmount			numeric;
	vDownPaymentAmountWithTax	numeric;
	vTaxDownPaymentAmount		numeric;
	vFlgShowPpn					character varying := 'Y';
	vOuBuInfoReport				OU_INFO_REPORT%ROWTYPE;
	result						record;
	vAll						character varying(5);
	vSubTotal					numeric;
	vCurrCode					character varying(50);

	vRoundingModeTax			character varying(5);
    vRoundingModeNonTax			character varying(5);
    vRoundingScalePrintTotal	integer;
    vGroupDataAsli              character varying := 'ASLI';
    vGroupDataCopy              character varying := 'COPY';
    vReceipt                    numeric;

BEGIN
	vDoDocTypeId := 311;
	vReportName := 'FormTemporarySalesInvoice';
	vAll := 'ALL';
	vReceipt := -99;

	SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.tax') INTO vRoundingModeTax;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
    SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.print.total') AS integer) INTO vRoundingScalePrintTotal;

	SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
	FROM sl_invoice_temp A
	WHERE A.invoice_temp_id = pInvoiceTempId INTO result ;
	vOuBuInfoReport := result.info;

	DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;


	/* insert into table tr_sl_invoice_item*/
	INSERT INTO tr_sl_invoice_item(
        session_id, tenant_id, product_code, product_name, qty_so, uom_name,
        nett_sell_price, gross_sell_price, nett_item_amount, gross_item_amount,
        tax_price, tax_amount, warranty, line_no)
	SELECT pSessionId, pTenantId, f_get_product_code(D.product_id) AS product_code, f_get_product_name(D.product_id) AS product_name, G.qty_dlv_so,
	f_get_uom_name(G.so_uom_id) AS uom_name, G.price_so AS nett_sell_price,
	E.gross_sell_price AS gross_sell_price,
	(G.item_amount + COALESCE(I.tax_amount,0)) - (G.regular_disc_amount + G.promo_disc_amount + G.adj_regular_disc_amount + G.adj_promo_disc_amount) AS nett_item_amount,
	(G.item_amount + COALESCE(I.tax_amount,0)) AS gross_item_amount,
	E.tax_price AS tax_price,
	COALESCE(I.tax_amount,0) AS tax_amount,
	(f_get_product_warranty_service(D.product_id) :: integer) AS warranty, ROW_NUMBER() OVER (PARTITION BY B.invoice_temp_id ORDER BY B.invoice_temp_id, A.line_no) AS line_no
	FROM sl_invoice_temp_item A
		INNER JOIN sl_invoice_temp B ON A.invoice_temp_id = B.invoice_temp_id
		INNER JOIN sl_do C ON A.ref_id = C.do_id AND C.doc_type_id = A.ref_doc_type_id
		INNER JOIN sl_do_item D ON A.ref_item_id = D.do_item_id AND C.do_id = D.do_id
		INNER JOIN sl_so_item E ON E.so_item_id = D.ref_id AND E.so_id = C.ref_id
		INNER JOIN sl_so_balance_invoice G ON A.tenant_id = G.tenant_id AND G.ou_id = B.ou_id AND G.ref_doc_type_id = vDoDocTypeId AND G.ref_id = D.do_id AND G.so_id = C.ref_id AND G.ref_item_id = A.ref_item_id
		LEFT JOIN sl_so_balance_invoice_tax I ON A.tenant_id = I.tenant_id AND I.ou_id = B.ou_id AND I.ref_doc_type_id = vDoDocTypeId AND I.ref_id = D.do_id AND I.so_id = C.ref_id AND I.ref_item_id = A.ref_item_id AND I.do_receipt_item_id = A.do_receipt_item_id
	WHERE A.tenant_id = pTenantId
		AND A.invoice_temp_id = pInvoiceTempId
		AND A.do_receipt_item_id = vReceipt;


	SELECT SUM(nett_item_amount) FROM tr_sl_invoice_item WHERE session_id = pSessionId INTO vSubTotal;

	SELECT curr_code FROM sl_invoice_temp WHERE invoice_temp_id = pInvoiceTempId INTO vCurrCode;


	Open pRefHeader FOR
		SELECT f_get_report_parameter_config_value(pTenantId,vFormTemporarySalesInvoice,A.ou_id,vTermsAndConditions) AS terms_and_conditions,
			f_get_report_parameter_config_value(pTenantId,vFormTemporarySalesInvoice,A.ou_id,vBankAcc1) AS bank_acc_1,
			f_get_report_parameter_config_value(pTenantId,vFormTemporarySalesInvoice,A.ou_id,vBankAcc2) AS bank_acc_2,
			f_get_partner_code(A.partner_id) AS customer_code, f_get_partner_name(A.partner_id) AS customer_name, B.address1, B.address2, B.address3,
			A.doc_no, A.doc_date, D.doc_no AS do_no, D.doc_date AS do_date, C.doc_no AS so_no, C.doc_date AS so_date,
			f_get_username(pUserId) AS username, pDatetime AS datetime, vSubTotal AS sub_total, vCurrCode AS curr_code
		FROM sl_invoice_temp A INNER JOIN m_partner_address B ON A.partner_id = B.partner_id
			INNER JOIN sl_so C ON A.ref_id = C.so_id
			INNER JOIN sl_do D ON D.ref_id = C.so_id AND D.doc_type_id = vDoDocTypeId
		WHERE A.tenant_id = pTenantId
			AND A.invoice_temp_id = pInvoiceTempId;
	RETURN NEXT pRefHeader;

	Open pRefDetail FOR
		SELECT vGroupDataAsli AS origin, A.line_no AS row_number, A.product_code, A.product_name, SUM(A.qty_so) AS qty, A.uom_name,
		   A.nett_sell_price AS unit_price, (A.nett_sell_price * A.qty_so) AS amount, SUM(A.nett_sell_price * A.qty_so) AS sub_total, A.warranty
		FROM tr_sl_invoice_item A
		INNER JOIN sl_invoice_temp_item B ON A.tenant_id = B.tenant_id AND B.invoice_temp_id = pInvoiceTempId AND B.ref_doc_type_id = vDoDocTypeId
		WHERE A.session_id = pSessionId
			AND A.tenant_id = pTenantId
			AND B.do_receipt_item_id = vReceipt
		GROUP BY line_no, product_code, product_name, uom_name, nett_sell_price, warranty,qty_so

		UNION ALL

		SELECT vGroupDataCopy AS origin, A.line_no AS row_number, A.product_code, A.product_name, SUM(A.qty_so) AS qty, A.uom_name,
		   A.nett_sell_price AS unit_price, (A.nett_sell_price * A.qty_so) AS amount, SUM(A.nett_sell_price * A.qty_so) AS sub_total, A.warranty
		FROM tr_sl_invoice_item A
		INNER JOIN sl_invoice_temp_item B ON A.tenant_id = B.tenant_id AND B.invoice_temp_id = pInvoiceTempId AND B.ref_doc_type_id = vDoDocTypeId
		WHERE A.session_id = pSessionId
			AND A.tenant_id = pTenantId
			AND B.do_receipt_item_id = vReceipt
		GROUP BY line_no, product_code, product_name, uom_name, nett_sell_price, warranty,qty_so
		ORDER BY origin, row_number, product_code, product_name;
	RETURN NEXT pRefDetail;


	DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/