c_commissionrun_process.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_CommissionRun_Process
(
	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: C_CommissionRun_Process.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $
 ***
 * Title:	Create AP Invoice from Commission Run
 * Description:
 ************************************************************************/
AS
	--	Logistice
	v_ResultStr						VARCHAR2(2000);
	v_Message						VARCHAR2(2000);
	v_Record_ID						NUMBER(10);
	v_Result						NUMBER(10) := 1;	-- Success
	--	Parameter
	CURSOR Cur_Parameter (pp_PInstance 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=pp_PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
	--	Parameter Variables

	CURSOR CUR_ComRun IS
		SELECT	*
		FROM	C_CommissionRun
		WHERE	C_CommissionRun_ID = v_Record_ID;
	--
	v_C_DocType_ID					NUMBER;
	v_Approved						CHAR(1);
	v_C_Invoice_ID					NUMBER;
	v_NextNo						NUMBER;
	v_DocumentNo					VARCHAR2(40);
	v_SalesRep_ID					NUMBER;
	--
	v_C_BPartner_ID					NUMBER;
	v_C_BPartner_Location_ID		NUMBER;
	v_C_PaymentTerm_ID				NUMBER;
	v_C_Currency_ID					NUMBER;
	v_PaymentRule					CHAR(1);
	v_M_PriceList_ID				NUMBER;
	v_POReference					NUMBER;
	v_IsDiscountPrinted				CHAR(1);

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 = 'xx') THEN
 	--		xx := p.P_String;
	--		DBMS_OUTPUT.PUT_LINE('  xx=' || xx);
	--	ELSE
	--		DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
	-- 	END IF;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || v_Record_ID);


	FOR c IN CUR_ComRun LOOP
		/**
		 *	Create Header
		 */
 		v_ResultStr := 'GetDocTypeInfo';
		SELECT	C_DocType_ID, DECODE(IsApproved, 'Y', 'N', 'Y')
		  INTO	v_C_DocType_ID, v_Approved
		FROM	C_DocType
		WHERE	DocBaseType = 'API' AND RowNum = 1
		  AND	AD_Client_ID = c.AD_Client_ID;
		--
		SELECT	com.C_Currency_ID, com.C_BPartner_ID, pl.C_BPartner_Location_ID, 
			PaymentRule, NVL(PO_PaymentTerm_ID, C_PaymentTerm_ID), NVL(PO_PriceList_ID, M_PriceList_ID),
			POReference, IsDiscountPrinted, SalesRep_ID
		  INTO	v_C_Currency_ID, v_C_BPartner_ID, v_C_BPartner_Location_ID, 
			v_PaymentRule, v_C_PaymentTerm_ID, v_M_PriceList_ID, 
			v_POReference, v_IsDiscountPrinted, v_SalesRep_ID
		FROM	C_Commission com, C_BPartner p, C_BPartner_Location pl
		WHERE	com.C_Commission_ID = c.C_Commission_ID
		  AND	com.C_BPartner_ID = p.C_BPartner_ID
		  AND	com.C_BPartner_ID = pl.C_BPartner_ID(+)
		  AND	RowNum = 1;
		--
		IF (v_PaymentRule IS NULL) THEN
			v_PaymentRule := 'P';
		END IF;
		IF (v_IsDiscountPrinted IS NULL) THEN
			v_IsDiscountPrinted := 'N';
		END IF;
		IF (v_C_BPartner_Location_ID IS NULL OR v_C_PaymentTerm_ID IS NULL
				OR v_M_PriceList_ID IS NULL) THEN
		   	-- TODO: Translation
			v_Message := 'Missing Partner Location or Payment Term or PriceList';
			v_Result := 0;
			GOTO FINISH_PROCESS;
		END IF; 
		--
		v_ResultStr := 'GetDocSequenceInfo';
		AD_Sequence_Next('C_Invoice', c.AD_Client_ID, v_C_Invoice_ID);
		BEGIN
			AD_Sequence_DocType(v_C_DocType_ID, c.AD_Client_ID, v_DocumentNo);
		EXCEPTION
			WHEN OTHERS THEN
				AD_Sequence_Doc('DocumentNo_C_Invoice', c.AD_Client_ID, v_DocumentNo);
	   	END;
		IF (v_DocumentNo IS NULL) THEN
			v_DocumentNo := c.DocumentNo;
	   	END IF;
		--
		DBMS_OUTPUT.PUT_LINE('  Invoice_ID=' || v_C_Invoice_ID || ' DocumentNo=' || v_DocumentNo);
		--
		v_ResultStr := 'InsertInvoice ' || v_C_Invoice_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,
			M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID, C_Payment_ID, C_CashLine_ID)
		VALUES
			(v_C_Invoice_ID, NULL,
			c.AD_Client_ID, c.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			'N', v_DocumentNo, 'DR', 'CO', 'N', 'N',
			v_C_DocType_ID, v_C_DocType_ID, c.Description,
			v_Approved, 'N', v_SalesRep_ID,
			c.Updated, NULL, 'N', c.Updated,	--	DateInvoiced=DateAcct
			v_C_PaymentTerm_ID, v_C_BPartner_ID, v_C_BPartner_Location_ID, NULL,
			v_POReference, c.Updated, v_IsDiscountPrinted,
			v_C_Currency_ID, v_PaymentRule, NULL, 0,
			0, 0,
			v_M_PriceList_ID, NULL, NULL, NULL, NULL, NULL);

		--	One line with Total (TODO: Tax, UOM)
		v_ResultStr := 'InsertLine';
		AD_Sequence_Next('C_InvoiceLine', c.AD_Client_ID, v_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, PriceLimit, LineNetAmt,
			C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
		VALUES
			(v_NextNo,
			c.AD_Client_ID, c.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
			v_C_Invoice_ID, NULL, NULL,
			10, NULL,
			null, 1, c.GrandTotal, c.GrandTotal, c.GrandTotal, c.GrandTotal,
			null, 0, null, null);
	END LOOP;
	v_Message := '@C_Invoice_ID@ = ' || v_DocumentNo;


<<FINISH_PROCESS>>
	--  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;
    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 C_CommissionRun_Process;
/