c_yearperiods.sql

Java写的ERP系统

CREATE OR REPLACE PROCEDURE C_YearPeriods
(
    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_YearPeriods.sql,v 1.2 2002/05/22 02:48:28 jjanke Exp $
 ***
 * Title:	Create missing standard periods for Year_ID
 * Description:
 ************************************************************************/
AS
	--	Parameter
	CURSOR Cur_Parameter (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=PInstance
		AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
		ORDER BY p.SeqNo;
    Year_ID		NUMBER;
	-- 
	NextNo      NUMBER;
    MonthNo     NUMBER;
    StartDate   Date;
    Test        NUMBER;
    ResultStr   VARCHAR(60);
    --  C_Year Variables
    Client_ID   NUMBER;
    Org_ID      NUMBER;
    Calendar_ID NUMBER;
    Year_Str    VARCHAR(20);
    User_ID     NUMBER;
BEGIN
    --  Update AD_PInstance
--  DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing');
    ResultStr := 'PInstanceNotFound';    
    UPDATE AD_PInstance
    SET Created = SysDate,
        IsProcessing = 'Y'
    WHERE AD_PInstance_ID=PInstance_ID;
    COMMIT;

	--	Get Parameters
	ResultStr := 'ReadingParameters';
	FOR p IN Cur_Parameter (PInstance_ID) LOOP
		Year_ID := p.Record_ID;
	END LOOP;	--	Get Parameter
	DBMS_OUTPUT.PUT_LINE('  Record_ID=' || Year_ID);

    --  Get C_Year Record 
	DBMS_OUTPUT.PUT_LINE('Get Year info');
    ResultStr := 'YearNotFound';    
    SELECT AD_Client_ID, AD_Org_ID, C_Calendar_ID, Year, UpdatedBy
    INTO Client_ID, Org_ID, Calendar_ID, Year_Str, User_ID
    FROM C_Year
    WHERE   C_Year_ID=Year_ID;

    --  Start Date
	DBMS_OUTPUT.PUT_LINE('Calculating start date');
    ResultStr := 'Year not numeric: '||Year_Str;
    SELECT TO_DATE('1/1/'||Year_Str, 'MM/DD/YYYY')
    INTO StartDate FROM DUAL;
	DBMS_OUTPUT.PUT_LINE('Start: '||StartDate);

    -- Loop to all months and add missing periods
    FOR MonthNo IN 1..12 LOOP
        --  Do we have the month already?
--      DBMS_OUTPUT.PUT_LINE('Checking Month No: '||MonthNo);
        ResultStr := 'Checking Month '||MonthNo;
        SELECT MAX(PeriodNo) INTO Test
        FROM C_Period WHERE C_Year_ID=Year_ID AND PeriodNo=MonthNo;
        IF Test IS NULL THEN
            -- get new NextNo
            AD_Sequence_Next('C_Period', Year_ID, NextNo);
--          DBMS_OUTPUT.PUT_LINE('Adding Period ID: '||NextNo);
            INSERT INTO C_Period
                (C_Period_ID, AD_Client_ID, AD_Org_ID, IsActive,
                Created, CreatedBy, Updated, UpdatedBy, C_Year_ID,
                PeriodNo, StartDate, PeriodType,
                Name)
            VALUES
                (NextNo, Client_ID, Org_ID, 'Y',
                SysDate, User_ID, SysDate, User_ID, Year_ID, 
                MonthNo, ADD_MONTHS(StartDate, MonthNo-1),'S',
                INITCAP(TO_CHAR(ADD_MONTHS(StartDate, MonthNo-1),'MON'))||
                    '-'||TO_CHAR(ADD_MONTHS(StartDate, MonthNo-1),'YY'));
            DBMS_OUTPUT.PUT_LINE('Month Added');
        END IF;
    END LOOP;

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

EXCEPTION
    WHEN  OTHERS THEN
--      DBMS_OUTPUT.PUT_LINE('No Data Found Exception');
        UPDATE  AD_PInstance
        SET Updated = SysDate,
            IsProcessing = 'N',
            Result = 0,             -- failure
            ErrorMsg = ResultStr
        WHERE   AD_PInstance_ID=PInstance_ID;
        COMMIT;
        RETURN;
END C_YearPeriods;
/