m_inout_createinvoice.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE M_InOut_CreateInvoice
(
	p_PInstance_ID    		IN NUMBER
)
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: M_InOut_CreateInvoice.sql,v 1.3 2002/10/23 03:16:57 jjanke Exp $
 ***
 * Title:	Create Invoice from Shipment
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID						NUMBER;
	--	Parameter
	CURSOR Cur_Parameter (ID NUMBER) IS
		SELECT 	i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date
		FROM 	AD_PInstance i, AD_PInstance_Para p
		WHERE	i.AD_PInstance_ID=ID
		  AND	i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--	Parameter Variables
	p_M_PriceList_Version_ID		NUMBER;

BEGIN
    --  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID);
	v_ResultStr := 'PInstanceNotFound';
    UPDATE AD_PInstance
    SET Created = SysDate,
        IsProcessing = 'Y'
    WHERE AD_PInstance_ID=p_PInstance_ID;
    COMMIT;

	--	Get Parameters
	v_ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (p_PInstance_ID) LOOP
		v_Record_ID := p.Record_ID;
		IF (p.ParameterName = 'M_PriceList_Version_ID') THEN
			p_M_PriceList_Version_ID := p.P_Number;
			DBMS_OUTPUT.PUT_LINE('  M_PriceList_Version_ID=' || p_M_PriceList_Version_ID);
		ELSE
			DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	 	END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);


	DECLARE
		CURSOR CUR_Shipment	IS
			SELECT	*
			FROM 	M_InOut
			WHERE	M_InOut_ID = v_Record_ID;
		CURSOR CUR_ShipmentLines IS
			SELECT	*
			FROM 	M_InOutLine
			WHERE	M_InOut_ID = v_Record_ID;
	   	--
		v_Invoice_ID					NUMBER(10);
		v_NextNo						NUMBER(10);
	   	v_DocType_ID					NUMBER(10);
		v_Approved						CHAR(1);
		v_InvoiceNo						NUMBER(10);
		v_DocumentNo					C_Invoice.DocumentNo%TYPE;
		v_IsDiscountPrinted				CHAR(1);
		v_PaymentRule					CHAR(1);
		v_C_PaymentTerm_ID				NUMBER(10);
		v_C_Currency_ID					NUMBER(10);
		v_M_PriceList_ID				NUMBER(10);
		--
		v_C_UOM_ID						NUMBER(10);
		v_C_Tax_ID						NUMBER(10);
		v_PriceList						NUMBER;
		v_PriceActual					NUMBER;
		v_PriceLimit					NUMBER;
		--
		v_LineNetAmt					NUMBER;
		v_TotalNet						NUMBER;
	BEGIN
		FOR s IN CUR_Shipment LOOP	-- Just to have all variables
			v_DocumentNo := NULL;
			BEGIN
			   	v_ResultStr := 'Check Invoice exists';	
				SELECT 	i.DocumentNo, i.C_Invoice_ID
				  INTO	v_DocumentNo, v_Invoice_ID
				FROM 	C_Invoice i, C_InvoiceLine il, M_InOutLine iol
				WHERE	i.C_Invoice_ID=il.C_Invoice_ID
				  AND	il.M_InOutLine_ID=iol.M_InOutLine_ID
				  AND	iol.M_InOut_ID=s.M_InOut_ID
				  AND	ROWNUM=1;
			EXCEPTION
	    		WHEN OTHERS THEN NULL;
			END;
			--	We have an Invoice
			IF (v_DocumentNo IS NOT NULL) THEN
				v_Message := '@ShipmentCreateDocAlreadyExists@ = ' 
					|| v_DocumentNo || ' (' || v_Invoice_ID || ')';
			
			--	Shipment must be complete
			ELSIF (s.DocStatus NOT IN ('CO','CL')) THEN
				v_Message := '@ShipmentCreateDocNotCompleted@';

			--	Create Invoice from Shipment
			ELSE
				v_ResultStr := 'GetBPartnerInfo';	-- P=OnCredit
				SELECT	IsDiscountPrinted, DECODE(PaymentRule,NULL,'P',PaymentRule), C_PaymentTerm_ID
				  INTO	v_IsDiscountPrinted, v_PaymentRule, v_C_PaymentTerm_ID
				FROM	C_BPartner
				WHERE	C_BPartner_ID = s.C_BPartner_ID;
				--	Get PaymentTerms 
				IF (v_C_PaymentTerm_ID IS NULL) THEN
					v_ResultStr := 'GetPaymentTerm';	--	let it fail if no unique record
					v_Message := '@NoPaymentTerm@';
					SELECT 	C_PaymentTerm_ID 
					  INTO	v_C_PaymentTerm_ID
					FROM 	C_PaymentTerm
					WHERE	AD_Client_ID = s.AD_Client_ID
					  AND	ROWNUM = 1
					ORDER BY IsDefault DESC, NetDays ASC;
				END IF;
				--
				IF (s.C_Order_ID IS NOT NULL) THEN
					v_ResultStr := 'GetCurrencyInfo-Order';
					SELECT	C_Currency_ID, M_PriceList_ID
					  INTO	v_C_Currency_ID, v_M_PriceList_ID
				   	FROM	C_Order
					WHERE	C_Order_ID = s.C_Order_ID;
				ELSE
					v_ResultStr := 'GetCurrencyInfo-PL';
					SELECT	pl.C_Currency_ID, pl.M_PriceList_ID
					  INTO	v_C_Currency_ID, v_M_PriceList_ID
					FROM	M_PriceList pl, M_PriceList_Version plv
					WHERE	pl.M_PriceList_ID=plv.M_PriceList_ID
					  AND	M_PriceList_Version_ID = p_M_PriceList_Version_ID;
				END IF;
				--
				v_ResultStr := 'GetDocTypeInfo';
				SELECT 	C_DocType_ID, DECODE(IsApproved, 'Y', 'N', 'Y')
				  INTO	v_DocType_ID, v_Approved
				FROM	C_DocType id
				WHERE	DocBaseType='API' AND RowNum=1
				  AND	AD_Client_ID=s.AD_Client_ID;
			  	--
				AD_Sequence_Next('C_Invoice', s.AD_Client_ID, v_Invoice_ID);
				AD_Sequence_DocType(v_DocType_ID, s.AD_Client_ID, v_DocumentNo);
				IF (v_DocumentNo IS NULL) THEN
					v_DocumentNo := s.DocumentNo;
				END IF;
				--
				DBMS_OUTPUT.PUT_LINE('  Invoice_ID=' || v_Invoice_ID || ' DocumentNo=' || v_DocumentNo);
				v_ResultStr := 'InsertInvoice ' || v_Invoice_ID;
				v_Message := '@DocumentNo@ = ' || v_DocumentNo;
				INSERT INTO C_Invoice
					(C_Invoice_ID, C_Order_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					IsSOTrx, DocumentNo, DocStatus, DocAction, Processing, Processed,
					C_DocType_ID, C_DocTypeTarget_ID, Description,
					IsApproved, IsTransferred, SalesRep_ID,
					DateInvoiced, DatePrinted, IsPrinted, DateAcct,
					C_PaymentTerm_ID, C_BPartner_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
					POReference, DateOrdered, IsDiscountPrinted,
					C_Currency_ID, PaymentRule, C_Charge_ID, ChargeAmt,
					TotalLines, GrandTotal,
					M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID, C_Payment_ID, C_CashLine_ID)
				VALUES
					(v_Invoice_ID, NULL,
					s.AD_Client_ID, s.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
					'N', v_DocumentNo, 'DR', 'CO', 'N', 'N',
					v_DocType_ID, v_DocType_ID, s.Description,
					v_Approved, 'N', NULL,
					SysDate, NULL, 'N', SysDate,
					v_C_PaymentTerm_ID, s.C_BPartner_ID, s.C_BPartner_Location_ID, s.C_BPartner_Contact_ID,
					NULL, s.DateOrdered, v_IsDiscountPrinted,
					v_C_Currency_ID, v_PaymentRule, NULL, 0,
					0, 0,
					v_M_PriceList_ID, NULL, NULL, NULL, NULL, NULL);

				--	Lines
				v_TotalNet := 0;
				FOR l IN CUR_ShipmentLines LOOP
					--	Get Price
					IF (l.C_OrderLine_ID IS NOT NULL) THEN
						v_ResultStr := 'GettingPrice-Order';
						SELECT	NVL(MAX(PriceList),0), NVL(MAX(PriceActual),0), NVL(MAX(PriceLimit),0)
						  INTO	v_PriceList, v_PriceActual, v_PriceLimit
						FROM	C_OrderLine
						WHERE	C_OrderLine_ID = l.C_OrderLine_ID;
					ELSE
						v_ResultStr := 'GettingPrice-PList';
						SELECT	NVL(MAX(PriceList),0), NVL(MAX(PriceStd),0), NVL(MAX(PriceLimit),0)
						  INTO	v_PriceList, v_PriceActual, v_PriceLimit
						FROM	M_ProductPrice
						WHERE	M_Product_ID = l.M_Product_ID
						  AND	M_PriceList_Version_ID = p_M_PriceList_Version_ID;
					END IF;

					-- Get UOM + Tax -- VERY simplified, but should work in most cases
					v_ResultStr := 'NoUOM+Tax';
					SELECT	NVL(MAX(C_UOM_ID),100), MAX(C_Tax_ID)	--	UOM 100=EA
					  INTO	v_C_UOM_ID, v_C_Tax_ID
					FROM	M_Product p, C_Tax t
					WHERE	p.C_TaxCategory_ID=t.C_TaxCategory_ID
					  AND	p.M_Product_ID = l.M_Product_ID
					ORDER BY t.IsDefault DESC;
					IF (v_C_Tax_ID IS NULL) THEN
						v_ResultStr := 'NoTax';
						SELECT 	C_Tax_ID 
						  INTO	v_C_Tax_ID
						FROM 	C_Tax
						WHERE	IsDefault='Y'
						  AND	AD_Client_ID = l.AD_Client_ID;
					END IF;

					--	v_UOM_ID, v_Tax_ID
					v_ResultStr := 'InsertInvoiceLine';
					AD_Sequence_Next('C_InvoiceLine', s.C_Order_ID, v_NextNo);
					v_LineNetAmt := ROUND(v_PriceActual*l.MovementQty,2);
					INSERT INTO C_InvoiceLine
						(C_InvoiceLine_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID,
						Line, Description,
						M_Product_ID, QtyInvoiced, 
						PriceList, PriceActual, PriceLimit, LineNetAmt,
						C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
					VALUES
						(v_NextNo,
						s.AD_Client_ID, l.AD_Org_ID, 'Y', SysDate, 100, SysDate, 0,	-- LineTrigger reqirement
						v_Invoice_ID, l.C_OrderLine_ID, l.M_InOutLine_ID,
						l.Line, l.Description,
						l.M_Product_ID, l.MovementQty, 
						v_PriceList, v_PriceActual, v_PriceLimit, v_LineNetAmt,
						null, 0, v_C_UOM_ID, v_C_Tax_ID);
					v_TotalNet := v_TotalNet + v_LineNetAmt;
				END LOOP;	--	ShipLines
			END IF;
		END LOOP;	-- All Shipments
	END;


<<FINISH_PROCESS>>
	--  Update AD_PInstance
	DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
    UPDATE  AD_PInstance
    SET Updated = SysDate,
        IsProcessing = 'N',
        Result = 1,                 -- success
        ErrorMsg = v_Message
    WHERE   AD_PInstance_ID=p_PInstance_ID;
    COMMIT;
    RETURN;

EXCEPTION
    WHEN  OTHERS THEN
		v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr);
		ROLLBACK;
        UPDATE  AD_PInstance
        SET Updated = SysDate,
            IsProcessing = 'N',
            Result = 0,             -- failure
            ErrorMsg = v_ResultStr
        WHERE   AD_PInstance_ID=p_PInstance_ID;
        COMMIT;
        RETURN;

END M_InOut_CreateInvoice;
/