SET TERM ^ ;

CREATE OR ALTER procedure SPC_CLIENTE_ULTIMA_COMPRA (
    ICLIENTE_ID integer not null)
returns (
    EMPRESA_ID integer,
    DATA date,
    NUMERO integer,
    SERIE varchar(5),
    ORIGEM varchar(10),
    DOCUMENTO varchar(50))
as
BEGIN

    FOR
        SELECT
            Y.EMPRESA_CODIGO,
            Y.DATA_EMISSAO,
            Y.NUMERO_NOTAFISCAL,
            Y.SERIE,

            Y.ORIGEM,
        
            FN_LPAD(Y.NUMERO_NOTAFISCAL, 8, '0')||'/'||FN_LPAD(Y.SERIE, 3, '0') AS DOCUMENTO
        
        FROM (
            SELECT
                X.EMPRESA_CODIGO,
                X.DATA_EMISSAO,
                X.NUMERO_NOTAFISCAL,
                X.SERIE,

                X.ORIGEM
            
            FROM (
                SELECT FIRST 1
                    MFE.EMPRESA_CODIGO,
                    MFE.NUMERO_NOTAFISCAL,
                    MFE.SERIE,
        
                    'MFE' AS ORIGEM,
        
                    MFE.DATA_EMISSAO
                
                FROM VWMFE MFE
                WHERE MFE.MFE_ID > 0
                AND MFE.EMPRESA_CODIGO = :ICLIENTE_ID
                AND ((MFE.NATUREZA = 1 AND MFE.SITUACAO = 2) OR (MFE.NATUREZA = 2 AND MFE.SITUACAO = 1))
                
                ORDER BY MFE.DATA_EMISSAO DESC
            ) X
            
            UNION ALL
            
            SELECT
                X.EMPRESA_CODIGO,
                X.DATA_EMISSAO,
                X.NUMERO_NOTAFISCAL,
                X.SERIE,

                X.ORIGEM
            
            FROM (
                SELECT FIRST 1
                    NF.EMPRESA_CODIGO,
                    NF.NUMERO_NOTAFISCAL,
                    NF.SERIE,
        
                    'NFS' AS ORIGEM,
        
                    NF.DATA_EMISSAO
                
                FROM TBNFS NF
                WHERE NF.CONTROLE > 0
                AND NF.EMPRESA_CODIGO = :ICLIENTE_ID
                AND ((NF.NATUREZA = 1 AND NF.SITUACAO = 2) OR (NF.NATUREZA = 2 AND NF.SITUACAO = 1))
                
                ORDER BY NF.DATA_EMISSAO DESC
            ) X
        ) Y
        
        ORDER BY Y.DATA_EMISSAO DESC

    INTO
        :EMPRESA_ID,
        :DATA,
        :NUMERO,
        :SERIE,
        :ORIGEM,
        :DOCUMENTO

    DO
    BEGIN

        SUSPEND;
    END
END
^

SET TERM ; ^

