c_invoice_post.sql

Java写的ERP系统

			SELECT l.C_Tax_ID, i.IsTaxIncluded, 
				SUM(l.LineNetAmt) + SUM(l.ChargeAmt) AS LineNet,
				SUM(i.ChargeAmt) AS HeaderNet,
				t.Rate, t.IsSummary, c.StdPrecision
			FROM C_Invoice i, C_InvoiceLine l, C_Tax t, C_Currency c
			WHERE i.C_Invoice_ID=l.C_Invoice_ID
			  AND i.C_Invoice_ID=v_Record_ID		-- Parameter
			  AND l.C_Tax_ID=t.C_Tax_ID
			  AND i.C_Currency_ID=c.C_Currency_ID
			GROUP BY l.C_Tax_ID, i.IsTaxIncluded, t.Rate, t.IsSummary, c.StdPrecision
			ORDER BY 3 DESC;
		CURSOR Cur_MultiTax (Parent_ID NUMBER) IS
			SELECT	C_Tax_ID, Rate
			FROM	C_Tax
			WHERE	Parent_Tax_ID=Parent_ID;

		xTotalLines						NUMBER := 0;
		xTaxBaseAmt						NUMBER := 0;
		xTaxAmt							NUMBER := 0;
		HeaderNotAdded					BOOLEAN := TRUE;
	BEGIN
		v_ResultStr := 'DeleteOldTaxes';
		DELETE FROM C_InvoiceTax
		WHERE C_Invoice_ID = v_Record_ID;
		--	For all Tax Rates
		v_ResultStr := 'InsertNewTaxes';
		FOR t IN Cur_Tax (v_Record_ID) LOOP
			xTaxBaseAmt := t.LineNet; 
			xTotalLines := xTotalLines + xTaxBaseAmt;		--	w/o Header Freight/Charge
			IF (HeaderNotAdded) THEN						--	add header to first 
				HeaderNotAdded := FALSE;
				xTaxBaseAmt := xTaxBaseAmt + t.HeaderNet;
			END IF;
			IF (t.IsSummary = 'N') THEN
				xTaxAmt := ROUND(xTaxBaseAmt * t.Rate / 100, t.StdPrecision);
				v_GrandTotal := v_GrandTotal + xTaxBaseAmt + xTaxAmt;
				--
				INSERT INTO C_InvoiceTax
					(C_Invoice_ID, C_Tax_ID,
					AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					TaxBaseAmt, TaxAmt)
				VALUES 
					(v_Record_ID, t.C_Tax_ID,
					v_Client_ID, v_Org_ID, 'Y', SysDate, v_UpdatedBy, SysDate, v_UpdatedBy,
					xTaxBaseAmt, xTaxAmt);
			ELSE	--	Multiple Taxes
				v_GrandTotal := v_GrandTotal + xTaxBaseAmt;
				FOR mt IN Cur_MultiTax (t.C_Tax_ID) LOOP
					xTaxAmt := ROUND(xTaxBaseAmt * mt.Rate / 100, t.StdPrecision);
					v_GrandTotal := v_GrandTotal + xTaxAmt;
					--
					INSERT INTO C_InvoiceTax
						(C_Invoice_ID, C_Tax_ID,
						AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
						TaxBaseAmt, TaxAmt)
					VALUES 
						(v_Record_ID, mt.C_Tax_ID,
						v_Client_ID, v_Org_ID, 'Y', SysDate, v_UpdatedBy, SysDate, v_UpdatedBy,
						xTaxBaseAmt, xTaxAmt);			
				END LOOP;
			END IF;
		END LOOP;	--	Insert New Taxes
		--	Update Header
		UPDATE	C_Invoice
		  SET	TotalLines = xTotalLines,
				GrandTotal = v_GrandTotal
		WHERE	C_Invoice_ID = v_Record_ID;
		DBMS_OUTPUT.PUT_LINE('GrandTotal=' || v_GrandTotal);
	END;	--	Calculate Tax and Totals

	--	Synchronize Client/Org Ownership
	v_ResultStr := 'SyncOwnership';
	UPDATE	C_InvoiceLine
	  SET	AD_Client_ID = v_Client_ID,
			AD_Org_ID = v_Org_ID
	WHERE	C_Invoice_ID = v_Record_ID
	  AND	(AD_Client_ID <> v_Client_ID OR AD_Org_ID <> v_Org_ID);


	/**************************************************************************
	 *	Is Approved?
	 *************************************************************************/
	DBMS_OUTPUT.PUT_LINE('Approval - IsApproved=' || v_IsApproved);
	IF (v_IsApproved = 'N') THEN
		--	Check if it needs to be approved 
		v_ResultStr := 'TestApproval DocType_ID=' || v_DocType_ID;
		SELECT	IsApproved
		  INTO	v_IsApproved
		FROM	C_DocType
		WHERE	C_DocType_ID=v_DocType_ID;
		DBMS_OUTPUT.PUT_LINE('IsApproved=' || v_IsApproved);
		IF (v_IsApproved <> 'N') THEN		--	Approval needed
			IF (v_DocAction <> 'AP') THEN
				UPDATE	C_Invoice
				  SET	DocStatus = 'NA'
				WHERE	C_Invoice_ID = v_Record_ID;
				v_Message := 'NotApproved';
				DBMS_OUTPUT.PUT_LINE('Approval needed');
				GOTO END_PROCESSING;
			END IF;
		ELSE
			UPDATE	C_Invoice
			  SET	IsApproved = 'Y'
			WHERE	C_Invoice_ID = v_Record_ID;
		END IF;
	END IF;

	/**************************************************************************
	 * Credit Multiplier
	 *************************************************************************/
	DECLARE
		v_DocBaseType		C_DocType.DocBaseType%TYPE;
	BEGIN
		--	Is it a Credit Memo?
		SELECT	DocBaseType
		  INTO	v_DocBaseType
		FROM	C_DocType
		WHERE	C_DocType_ID = v_DocType_ID;

		IF (v_DocBaseType IN ('ARC', 'API')) THEN
			v_Multiplier := -1;
		END IF;
	END;

	/**************************************************************************
	 *	Create default Cash entry
	 *************************************************************************/
	IF (v_PaymentRule = 'B') THEN
		--	It is cash and we don't have a cash line
		--	see MCashBook.java
		IF (v_CashLine_ID IS NULL OR v_CashLine_ID = 0) THEN
			--	Create CashLine
			DECLARE
				CURSOR CUR_CB IS
					SELECT	cb.C_CashBook_ID, c.ISO_Code
					FROM		C_CashBook cb, C_Currency c
					WHERE	cb.AD_Org_ID=v_Org_ID
					  AND	cb.C_Currency_ID=c.C_Currency_ID
					  AND	cb.IsActive='Y'
					ORDER BY cb.IsDefault DESC;
				v_CashBook_ID					NUMBER := NULL;
				v_ISO_Code					VARCHAR(10);
				v_Cash_ID						NUMBER := NULL;
				v_Line						NUMBER := 0;
			BEGIN
				--	Find Defaylt CashBook
				v_ResultStr := 'Find C_CashBook Org_ID=' || v_Org_ID;
				BEGIN
					--	First active default Book of Org
					FOR cb IN CUR_CB LOOP
						IF (v_CashBook_ID IS NULL) THEN
							v_CashBook_ID := cb.C_CashBook_ID;
							v_ISO_Code := cb.ISO_Code;
						END IF;
					END LOOP;
				EXCEPTION
					WHEN OTHERS THEN
						RAISE_APPLICATION_ERROR (-20012, 'CashBook not found for Org=' || v_Org_ID);
				END;
				IF (v_CashBook_ID IS NULL) THEN
					RAISE_APPLICATION_ERROR (-20012, 'CashBook not found for Org=' || v_Org_ID);
				END IF;
				DBMS_OUTPUT.PUT_LINE('CashBook_ID=' || v_CashBook_ID);
				--	Find/Create Cash Journal
				v_DateAcct := TRUNC (v_DateAcct);
				v_ResultStr := 'Find C_Cash for ' || v_DateAcct;
				BEGIN
					SELECT	C_Cash_ID 
					  INTO	v_Cash_ID
					FROM	C_Cash 
					WHERE	C_CashBook_ID=v_CashBook_ID 
					  AND TRUNC(StatementDate)=v_DateAcct
					  AND Processed='N' AND ROWNUM=1;
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
						NULL;
				END;
				IF (v_Cash_ID IS NULL) THEN
					v_ResultStr := 'Create C_Cash';
					AD_Sequence_Next('C_Cash', v_Org_ID, v_Cash_ID);
					INSERT INTO C_Cash 
						(C_Cash_ID,AD_Client_ID, AD_Org_ID, 
						IsActive, Created,CreatedBy, Updated,UpdatedBy, 
						C_CashBook_ID, Name, 
						StatementDate,DateAcct,
						BeginningBalance,EndingBalance,StatementDifference, 
						Processing,Processed,Posted) 
					VALUES 
						(v_Cash_ID, v_Client_ID, v_Org_ID,
						'Y', SysDate,v_UpdatedBy, SysDate,v_UpdatedBy,
						v_CashBook_ID, TO_CHAR(v_DateAcct, 'YYYY-MM-DD') || ' ' || v_ISO_Code, 
						v_DateAcct, v_DateAcct,
						0,0,0, 'N','N','N');
				END IF;
				DBMS_OUTPUT.PUT_LINE('  Cash_ID=' || v_Cash_ID);
				--	Create CashJournal Line in invoice currency
				v_ResultStr := 'Create C_CashLine';
				AD_Sequence_Next('C_CashLine', v_Org_ID, v_CashLine_ID);
				SELECT	NVL(MAX(Line),0)+10 
				  INTO	v_Line
				FROM		C_CashLine 
				WHERE	C_Cash_ID=v_Cash_ID;
				--
				INSERT INTO C_CashLine 
					(C_CashLine_ID,AD_Client_ID,AD_Org_ID, 
					IsActive,Created,CreatedBy,Updated,UpdatedBy, 
					C_Cash_ID, C_Invoice_ID, 
					Line, Description,Amount,CashType,
					DiscountAmt, WriteOffAmt, IsGenerated) 
				VALUES 
					(v_CashLine_ID, v_Client_ID, v_Org_ID,
					'Y', SysDate,v_UpdatedBy, SysDate,v_UpdatedBy,
					v_Cash_ID, v_Record_ID,
					v_Line, v_DocumentNo, v_GrandTotal * v_Multiplier, 'I',
					0, 0, 'Y');
				DBMS_OUTPUT.PUT_LINE('  CashLine_ID=' || v_CashLine_ID);
			END;
			--	Create Links
			UPDATE	C_Invoice 
			  SET	C_CashLine_ID = v_CashLine_ID
			WHERE	C_Invoice_ID = v_Record_ID;
			--	Create Link to Order
			UPDATE	C_Order o
			  SET	C_CashLine_ID = v_CashLine_ID
			WHERE EXISTS (SELECT * FROM C_Invoice i 
				WHERE o.C_Order_ID=i.C_Order_ID AND i.C_Invoice_ID=v_Record_ID);
		END IF;	--	CashLine_ID IS NULL OR CashLine_ID = 0
	END IF;		--	v_PaymentRule = 'B'

	/**************************************************************************
	 *	Update BP Statistics
	 *************************************************************************/
	--	First Sale
	UPDATE	C_BPartner
	  SET	FirstSale = v_DateAcct
	WHERE	C_BPartner_ID = BPartner_ID
	  AND	FirstSale IS NULL;
	--	Last Contact, Result
	UPDATE	C_BPartner_Contact
	  SET	LastContact = SysDate, 
			LastResult = v_DocumentNo
	WHERE	C_BPartner_Contact_ID=BPartner_Contact_ID;

	--	Update total revenue and credit limit
	--	It is reversed in C_Allocation_Trg
	IF (v_IsSOTrx = 'Y') THEN
		UPDATE	C_BPartner
		  SET	ActualLifeTimeValue = ActualLifeTimeValue + (v_Multiplier * 
					C_Base_Convert (v_GrandTotal, v_Currency_ID, v_Client_ID, v_DateAcct, v_Org_ID)),
				SO_CreditUsed = SO_CreditUsed + (v_Multiplier *
					C_Base_Convert (v_GrandTotal, v_Currency_ID, v_Client_ID, v_DateAcct, v_Org_ID))
		WHERE	C_BPartner_ID = BPartner_ID;
	END IF;

	/**************************************************************************
	 * Matching
	 *************************************************************************/
	IF (v_IsSOTrx = 'N') THEN
		DECLARE
			--	Invoice-Receipt Match
			CURSOR Cur_ILines_Receipt IS
				SELECT il.AD_Client_ID,il.AD_Org_ID,
					il.C_InvoiceLine_ID, ml.M_InOutLine_ID, ml.M_Product_ID,
					ml.MovementQty, il.QtyInvoiced, i.DateAcct
				FROM	C_InvoiceLine il
				  INNER JOIN M_InOutLine ml ON (il.M_InOutLine_ID=ml.M_InOutLine_ID)
				  INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID)
				WHERE	il.M_Product_ID=ml.M_Product_ID
				  AND	il.C_Invoice_ID=v_Record_ID;
			--	Invoice-PO Match
			CURSOR Cur_ILines_PO IS
				SELECT il.AD_Client_ID,il.AD_Org_ID,
					il.C_InvoiceLine_ID, ol.C_OrderLine_ID, 
					ol.M_Product_ID, ol.C_Charge_ID,
					ol.QtyOrdered, il.QtyInvoiced, i.DateAcct
				FROM	C_InvoiceLine il
				  INNER JOIN C_OrderLine ol ON (il.C_OrderLine_ID=ol.C_OrderLine_ID)
				  INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID)
				WHERE	(il.M_Product_ID=ol.M_Product_ID OR il.C_Charge_ID=ol.C_Charge_ID)
				  AND	il.C_Invoice_ID=v_Record_ID;
			v_Qty				NUMBER;
			v_MatchInv_ID			NUMBER(10);
			v_MatchPO_ID			NUMBER(10);
		BEGIN
			v_ResultStr := 'MatchInv-Receipt';
			FOR il IN Cur_ILines_Receipt LOOP
				-- The min qty
				v_Qty := il.MovementQty;
				IF (ABS(il.MovementQty) > ABS(il.QtyInvoiced)) THEN
					v_Qty := il.QtyInvoiced;
				END IF;
				AD_Sequence_Next('M_MatchInv', il.AD_Org_ID, v_MatchInv_ID);
				v_ResultStr := 'InsertMatchInv ' || v_MatchInv_ID;
				DBMS_OUTPUT.PUT_LINE('  M_MatchInv_ID=' || v_MatchInv_ID || ' - ' || v_Qty);
				INSERT INTO M_MatchInv
					(M_MatchInv_ID,
					AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
					M_InOutLine_ID,C_InvoiceLine_ID,
					M_Product_ID,DateTrx,Qty,
					Processing,Processed,Posted)
				VALUES
					(v_MatchInv_ID,
					il.AD_Client_ID,il.AD_Org_ID,'Y',SysDate,0,SysDate,0,
					il.M_InOutLine_ID,il.C_InvoiceLine_ID,
					il.M_Product_ID, il.DateAcct,v_Qty,
					'N','Y','N');
			END LOOP;

			v_ResultStr := 'MatchInv-PO';
			FOR il IN Cur_ILines_PO LOOP
				-- The min qty
				v_Qty := il.QtyOrdered;
				IF (ABS(il.QtyOrdered) > ABS(il.QtyInvoiced)) THEN
					v_Qty := il.QtyInvoiced;
				END IF;
				AD_Sequence_Next('M_MatchPO', il.AD_Org_ID, v_MatchPO_ID);
				v_ResultStr := 'InsertMatchPO ' || v_MatchPO_ID;
				DBMS_OUTPUT.PUT_LINE('  M_MatchPO_ID=' || v_MatchPO_ID || ' - ' || v_Qty);
				INSERT INTO M_MatchPO
					(M_MatchPO_ID,
					AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
					C_OrderLine_ID,M_InOutLine_ID,C_InvoiceLine_ID,
					M_Product_ID,DateTrx,Qty,
					Processing,Processed,Posted)
				VALUES
					(v_MatchPO_ID,
					il.AD_Client_ID,il.AD_Org_ID,'Y',SysDate,0,SysDate,0,
					il.C_OrderLine_ID,NULL,il.C_InvoiceLine_ID,
					il.M_Product_ID, il.DateAcct,v_Qty,
					'N','Y','N');
			END LOOP;
		END;
	END IF;

	--	Finish up -------------------------------------------------------------
	UPDATE	C_Invoice
	  SET	DocStatus = 'CO',
			Processed = 'Y',
			DocAction = '--',
			Updated = SysDate
	WHERE	C_Invoice_ID = v_Record_ID;

	--	Post it
	IF (v_IsTransferred = 'N') THEN
		C_Invoice_AcctGen (NULL, v_Record_ID);
	END IF;

	--	End Processing --------------------------------------------------------
<<END_PROCESSING>>
	v_ResultStr := 'UnLockingInvoice';
	UPDATE	C_Invoice
	  SET	Processing = 'N',
			Updated = SysDate
	WHERE	C_Invoice_ID = v_Record_ID;
	IF (p_PInstance_ID IS NOT NULL) THEN
		COMMIT;
	END IF;

<<FINISH_PROCESS>>
	IF (p_PInstance_ID IS NOT NULL) THEN
		--  Update AD_PInstance
		DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished - ' || v_Message);
		UPDATE	AD_PInstance
		SET Updated = SysDate,
			IsProcessing = 'N',
			Result = v_Result,				   -- 1=Success
			ErrorMsg = v_Message
		WHERE	AD_PInstance_ID=p_PInstance_ID;
		COMMIT;
	ELSE
		DBMS_OUTPUT.PUT_LINE('<<C_Invoive_Post finished>> ' || v_Message);
	END IF;
	RETURN;

EXCEPTION
	WHEN  OTHERS THEN
		v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
		DBMS_OUTPUT.PUT_LINE(v_ResultStr);
		ROLLBACK;
		IF (p_PInstance_ID IS NOT NULL) THEN
			UPDATE	AD_PInstance
			SET Updated = SysDate,
				IsProcessing = 'N',
				Result = 0,				-- failure
				ErrorMsg = v_ResultStr
			WHERE	AD_PInstance_ID=p_PInstance_ID;
			COMMIT;
		ELSE
			DECLARE
				v_Code	NUMBER			:= SQLCode;
			BEGIN
				INSERT INTO DBA_ErrorLog (DBA_ErrorLog_ID,Created,Code,Msg,Info)
				VALUES (DBA_ErrorLog_Seq.NextVal,SysDate,v_Code,v_ResultStr,'C_Invoice_Post');
			END;
		END IF;
		RETURN;

END C_Invoice_Post;
/