c_invoice_post.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_Invoice_Post
(
	p_PInstance_ID					IN NUMBER,
	p_Invoice_ID					IN NUMBER	--	DEFAULT NULL
)
/*************************************************************************
 * 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+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_Invoice_Post.sql,v 1.30 2003/04/15 05:09:25 jjanke Exp $
 ***
 * Title:	 Post single Invoice
 * Description:
 *		Actions: COmplete, APprove, Reverse Correction, Void
 *
 *	OpenItem Amount: 
 * 	- C_BPartner.SO_CreditUsed is increased
 *	- if C_CashLine entry is created
 *		- C_Cash_Post creates C_Allocation
 *			- C_Allocation_Trg decreases C_BPartner.SO_CreditUsed	
 *
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr					VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID					NUMBER;
	v_Result						NUMBER := 1;	--	Success
	--	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;
	--	Record Info
	v_Client_ID					NUMBER;
	v_Org_ID						NUMBER;
	v_UpdatedBy					C_Invoice.UpdatedBy%TYPE;
	v_Processing					C_Invoice.Processing%TYPE;
	v_Processed					C_Invoice.Processed%TYPE;
	v_DocAction					C_Invoice.DocAction%TYPE;
	v_DocStatus					C_Invoice.DocStatus%TYPE;
	v_DocType_ID					NUMBER;
	v_DocTypeTarget_ID				NUMBER;
	v_IsApproved					C_Invoice.IsApproved%TYPE;
	v_IsTransferred				C_Invoice.IsTransferred%TYPE;
	v_PaymentRule					C_Invoice.PaymentRule%TYPE;
	v_CashLine_ID					NUMBER;
	Order_ID						NUMBER;
	v_DateAcct					DATE;
	v_DocumentNo					C_Invoice.DocumentNo%TYPE;
	BPartner_ID					NUMBER;
	BPartner_Contact_ID				NUMBER;
	v_IsSOTrx						C_Invoice.IsSOTrx%TYPE;
	--
	v_GrandTotal					NUMBER := 0;
	v_Currency_ID					NUMBER;
	v_Multiplier					NUMBER := 1;
	--
	RInvoice_ID					NUMBER;
	v_RDocumentNo					C_Invoice.DocumentNo%TYPE;
	NextNo						NUMBER;
	CURSOR Cur_InvoiceLine (ID NUMBER) IS
		SELECT	*
		FROM	C_InvoiceLine
		WHERE	C_Invoice_ID = ID
		ORDER BY Line;

BEGIN
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  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;
		END LOOP;	--	Get Parameter
		DBMS_OUTPUT.PUT_LINE('  v_Record_ID=' || v_Record_ID);
	ELSE
		DBMS_OUTPUT.PUT_LINE('<<C_Invoive_Post>>');
		v_Record_ID := p_Invoice_ID;
	END IF;

	/**
	 *	Read Invoice
	 */
	v_ResultStr := 'ReadingInvoice';
	SELECT	Processing, Processed, DocAction, DocStatus, C_DocType_ID, C_DocTypeTarget_ID, 
		IsApproved, IsTransferred, PaymentRule, C_CashLine_ID, DateAcct,
		AD_Client_ID, AD_Org_ID, UpdatedBy, DocumentNo, C_Order_ID, IsSOTrx,
		C_BPartner_ID, C_BPartner_Contact_ID, C_Currency_ID
	  INTO	v_Processing, v_Processed, v_DocAction, v_DocStatus, v_DocType_ID, v_DocTypeTarget_ID,
		v_IsApproved, v_IsTransferred, v_PaymentRule, v_CashLine_ID, v_DateAcct,
		v_Client_ID, v_Org_ID, v_UpdatedBy, v_DocumentNo, Order_ID, v_IsSOTrx,
		BPartner_ID, BPartner_Contact_ID, v_Currency_ID
	FROM		C_Invoice
	WHERE	C_Invoice_ID = v_Record_ID
	FOR UPDATE;

	DBMS_OUTPUT.PUT_LINE('Invoice_ID=' || v_Record_ID ||
		', DocAction=' || v_DocAction || ', DocStatus=' || v_DocStatus ||
		', DocType_ID=' || v_DocType_ID || ', DocTypeTarget_ID=' || v_DocTypeTarget_ID ||
		', IsApproved=' || v_IsApproved);
	
	/**
	 *	Invoice Voided, Closed, or Reversed - No Action
	 */
	IF (v_DocStatus IN ('VO', 'CL', 'RE')) THEN
		v_Message := '@AlreadyPosted@';
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Unlock
	 */
	IF (v_DocAction = 'XL') THEN
		UPDATE	C_Invoice
		  SET	Processing = 'N',
				DocAction = '--',
				Updated = SysDate
		WHERE	C_Invoice_ID = v_Record_ID;
		GOTO FINISH_PROCESS;
	END IF;
	IF (v_Processing = 'Y') THEN
		v_Message := '@OtherProcessActive@';
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Everything done
	 */
	IF (v_Processed = 'Y' AND v_DocAction <> 'RC') THEN
		v_Message := '@AlreadyPosted@';
		v_Result := 0;
		GOTO FINISH_PROCESS;
	END IF;

	/**
	 *	Void	if Document not processed
	 */
	IF (v_DocAction = 'VO' AND v_DocStatus NOT IN ('CO','RE')) THEN
		--	Reset Lines to 0
		UPDATE	C_InvoiceLine
		  SET	QtyInvoiced = 0,
				LineNetAmt = 0
		WHERE	C_Invoice_ID = v_Record_ID;
		--
		UPDATE	C_Invoice
		  SET	DocStatus = 'VO',
				DocAction = '--',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Invoice_ID = v_Record_ID;
		-- 
		GOTO FINISH_PROCESS;
	END IF;

	/**************************************************************************
	 *	Start Processing ------------------------------------------------------
	 *************************************************************************/
	v_ResultStr := 'LockingInvoice';
	UPDATE	C_Invoice
	  SET	Processing = 'Y'
	WHERE	C_Invoice_ID = v_Record_ID;
	--	Now, needs to go to END_PROCESSING to unlock
	IF (p_PInstance_ID IS NOT NULL) THEN
		COMMIT;
	END IF;

	/**
	 *	Reverse Correction	requires completes invoice ========================
	 */
	IF (v_DocAction = 'RC' AND v_DocStatus = 'CO') THEN
		v_ResultStr := 'ReverseCorrection';
		--	Copy Invoice with reverese Quantities (or Amounts)
		AD_Sequence_Next('C_Invoice', v_Record_ID, RInvoice_ID);
		AD_Sequence_DocType(v_DocType_ID, v_Record_ID, v_RDocumentNo);
		IF (v_RDocumentNo IS NULL) THEN
			AD_Sequence_Doc('DocumentNo_C_Invoice', v_Client_ID, v_RDocumentNo);
		END IF;
		v_Message := '@ReversedBy@: ' || v_RDocumentNo || '.';
		--
		DBMS_OUTPUT.PUT_LINE('Reversal Invoice_ID=' || RInvoice_ID || ' DocumentNo=' || v_RDocumentNo);
		v_ResultStr := 'InsertInvoice ID=' || RInvoice_ID;
		--	Don't copy C_Payment_ID or C_CashLine_ID
		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,	 C_Payment_ID, C_CashLine_ID,
			M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID)
		SELECT RInvoice_ID, C_Order_ID,
			AD_Client_ID, AD_Org_ID, IsActive, SysDate, UpdatedBy, SysDate, UpdatedBy,
			IsSOTrx, v_RDocumentNo, 'DR', 'CO', 'N', 'N',
			C_DocType_ID, C_DocTypeTarget_ID, '(*R*: ' || DocumentNo || ') ' || Description,
			IsApproved, 'N', SalesRep_ID,
			DateInvoiced, NULL, 'N', 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 * -1,
			TotalLines * -1, GrandTotal * -1,  null, null,
			M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID
		FROM	C_Invoice
		WHERE	C_Invoice_ID = v_Record_ID;
		
		--	Create Reversal Invoice Lines
		FOR il IN Cur_InvoiceLine (v_Record_ID) LOOP
			AD_Sequence_Next('C_InvoiceLine', v_Record_ID, NextNo);
			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, LineNetAmt,
				C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
			VALUES
				(NextNo,
				il.AD_Client_ID, il.AD_Org_ID, 'Y', SysDate, v_UpdatedBy, SysDate, v_UpdatedBy,
				RInvoice_ID, il.C_OrderLine_ID, null,
				il.Line, '*R*: ' || il.Description,
				il.M_Product_ID, il.QtyInvoiced * -1, il.PriceList, il.PriceActual, il.LineNetAmt * -1,
				il.C_Charge_ID, il.ChargeAmt * -1, il.C_UOM_ID, il.C_Tax_ID);
			--
			UPDATE	C_OrderLine
			  SET	QtyInvoiced = QtyInvoiced - il.QtyInvoiced,
					Updated = SysDate
			WHERE C_OrderLine_ID=il.C_OrderLine_ID;
		END LOOP;	--	Create Reversal Invoice Lines

		--	Close Invoice
		UPDATE	C_Invoice
		  SET	DocStatus = 'RE',			--	it IS reversed
				Description = NVL(Description, '') || ' (*R* -> ' || v_RDocumentNo || ')',
				DocAction = '--',
				Processed = 'Y',
				Updated = SysDate
		WHERE	C_Invoice_ID = v_Record_ID;

		--	Post Reversal
		C_Invoice_Post(NULL, RInvoice_ID);

		--	Reversal Transaction is closed
		UPDATE	C_Invoice
		  SET	DocStatus = 'CL',			-- the reversal transaction 
				DocAction = '--',
				Processed = 'Y'
		WHERE	C_Invoice_ID = RInvoice_ID;

		GOTO END_PROCESSING;
	END IF;

	/**************************************************************************
	 *	Actions allowed: COmplete, APprove
	 */
	IF (v_DocAction = 'AP' OR v_DocAction = 'CO') THEN
		WHILE (v_DocType_ID <> v_DocTypeTarget_ID) LOOP
			BEGIN
				v_ResultStr := 'UpdateDocType';
				UPDATE	C_Invoice
				  SET	C_DocType_ID = C_DocTypeTarget_ID
				WHERE	C_Invoice_ID = v_Record_ID;
				v_DocType_ID := v_DocTypeTarget_ID;
			EXCEPTION WHEN OTHERS THEN
				v_ResultStr := 'UpdateDocumentNo';
				UPDATE	C_Invoice
				  SET	DocumentNo = DocumentNo || '.'
				WHERE	C_Invoice_ID = v_Record_ID;
			END;
		END LOOP;
	ELSE
		v_Message := '@ActionNotAllowedHere@ (I-' || v_DocAction || ')';
		GOTO END_PROCESSING;
	END IF;


	/**************************************************************************
	 *	Resolve not-stocked BOMs
	 *************************************************************************/
	DECLARE
		--	Invoice Lines with non-stocked BOMs
		CURSOR CUR_BOM_Line	IS
			SELECT	*
			FROM	C_InvoiceLine l
			WHERE	l.C_Invoice_ID=v_Record_ID
			  AND	IsActive='Y'
			  AND EXISTS (SELECT * FROM M_Product p WHERE l.M_Product_ID=p.M_Product_ID
				  AND	p.IsBOM='Y' AND p.IsStocked='N')
			ORDER BY l.Line
			FOR UPDATE;

		--	BOM Product List
		CURSOR CUR_BOM (Product_ID NUMBER)	IS
			SELECT	b.M_ProductBOM_ID, p.C_UOM_ID, b.BOMQty, b.Description
			FROM	M_Product_BOM b, M_Product p
			WHERE	b.M_Product_ID=Product_ID
			  AND	b.M_ProductBOM_ID=p.M_Product_ID
			ORDER BY Line;
		--	
		CountNo						NUMBER;
		PriceList_Version_ID		NUMBER;
		NextNo						NUMBER;
		Line						NUMBER;
		ChargeAmt					NUMBER;
		--
	BEGIN
		v_ResultStr := 'ResolveBOM';
		LOOP
			--	How many BOMs do we have?
			SELECT	COUNT(*)
			  INTO	CountNo
			FROM	C_InvoiceLine l
			WHERE	l.C_Invoice_ID=v_Record_ID
			  AND EXISTS (SELECT * FROM M_Product p WHERE l.M_Product_ID=p.M_Product_ID
				  AND	p.IsBOM='Y' AND p.IsStocked='N');
			--	Nothing to do?
			EXIT WHEN CountNo = 0;
			DBMS_OUTPUT.PUT_LINE('  BOMs to resolve=' || CountNo);

			--	Get Price List Version
			SELECT	NVL(SUM(v.M_PriceList_Version_ID), 0)
			  INTO	PriceList_Version_ID
			FROM	M_PriceList_Version v, C_Invoice o
			WHERE	v.M_PriceList_ID=o.M_PriceList_ID
			  AND	v.ValidFrom <= o.DateOrdered
			  AND	v.IsActive='Y'
			  AND	o.C_Invoice_ID=v_Record_ID
			  AND	RowNum=1
			ORDER BY v.ValidFrom DESC;

			--	Replace Lines
			FOR l IN CUR_BOM_Line LOOP
				Line := l.Line;
				--	One Time variables
				ChargeAmt := l.ChargeAmt;
				--	Create New Lines
				FOR b IN CUR_BOM (l.M_Product_ID) LOOP
					AD_Sequence_Next('C_InvoiceLine', l.AD_Client_ID, NextNo);
					Line := Line + 10;
					INSERT INTO C_InvoiceLine
						(C_InvoiceLine_ID,
						AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
						C_Invoice_ID,Line,
						Description,
						M_Product_ID,C_UOM_ID,
						QtyInvoiced,
						PriceList,PriceActual,PriceLimit,LineNetAmt,
						C_Charge_ID,ChargeAmt,
						C_Tax_ID)
					VALUES
						(NextNo,
						l.AD_Client_ID,l.AD_Org_ID,l.IsActive,SysDate,v_UpdatedBy,SysDate,v_UpdatedBy,
						l.C_Invoice_ID,Line,
						b.Description,
						b.M_ProductBOM_ID, b.C_UOM_ID,
						l.QtyInvoiced*b.BOMQty,
						BOM_PriceList(b.M_ProductBOM_ID, PriceList_Version_ID),BOM_PriceStd(b.M_ProductBOM_ID, PriceList_Version_ID),
						BOM_PriceLimit(b.M_ProductBOM_ID, PriceList_Version_ID),
						BOM_PriceStd(b.M_ProductBOM_ID, PriceList_Version_ID) * l.QtyInvoiced*b.BOMQty,
						l.C_Charge_ID,ChargeAmt,
						l.C_Tax_ID);
					--	One Time variables
					ChargeAmt := 0;
				END LOOP;	--	Create New Lines
				--	Convert into Comment Line
				UPDATE	C_InvoiceLine ol
				  SET	M_Product_ID = NULL,
						PriceList = 0, PriceActual = 0, PriceLimit = 0, LineNetAmt = 0,
						ChargeAmt = 0,
						Description = (SELECT p.Name || ' ' || ol.Description FROM M_Product p 
							WHERE p.M_Product_ID=l.M_Product_ID)
				WHERE	C_InvoiceLine_ID=l.C_InvoiceLine_ID;
			END LOOP;	-- Replace Lines
		END LOOP;	--	BOM Loop
	END;


	/**************************************************************************
	 *	Calculate Invoice Taxes and Totals
	 *************************************************************************/
	DECLARE
		CURSOR Cur_Tax (Invoice_ID NUMBER) IS