SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIZACLPA' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIZACLPA
GO


CREATE PROCEDURE RPTUSER.CIZACLPA
@P_ACCT_ID CHAR(10)

AS

/******************************************************************************
   NAME:       CIZACLPA
   PURPOSE:    

******************************************************************************/
BEGIN

    SELECT /*+ RULE */
        B.PAY_DT,
        SUM(A.PAY_AMT) PAY_AMT,	
        A.CURRENCY_CD,
        C.CUR_SYMBOL,
        C.DECIMAL_POSITIONS,
        C.SCALE_POSITIONS,
        C.CUR_POS_FLG
    FROM 
        CI_PAY A, 
        CI_PAY_EVENT B, 
        CI_CURRENCY_CD C
    WHERE 
        A.ACCT_ID = @P_ACCT_ID
        AND A.PAY_STATUS_FLG = '50'
        AND B.PAY_EVENT_ID = A.PAY_EVENT_ID
        AND B.PAY_DT = (
            SELECT MAX (E.PAY_DT)
            FROM CI_PAY D, CI_PAY_EVENT E
            WHERE  
                D.ACCT_ID = @P_ACCT_ID
                AND  D.PAY_STATUS_FLG = '50'
                AND  E.PAY_EVENT_ID = D.PAY_EVENT_ID)
        AND C.CURRENCY_CD = A.CURRENCY_CD
    GROUP BY 
        B.PAY_DT,
        A.CURRENCY_CD,
        C.CUR_SYMBOL,
        C.DECIMAL_POSITIONS,
        C.SCALE_POSITIONS,
        C.CUR_POS_FLG
			 
RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

