-- ******************************************************************
-- *
-- * Copyright (c) 2000, 2007, Oracle. All rights reserved.
-- *
-- ******************************************************************

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIBLPRTI' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIBLPRTI 
GO

CREATE PROCEDURE RPTUSER.CIBLPRTI
@P_USER_ID CHAR(8),
@P_BILL_ID CHAR(12),
@P_SA_ID CHAR(10),
@P_LANGUAGE_CD CHAR(3)
AS

/******************************************************************************
   NAME:       CIBLPRTI
   PURPOSE:    Supply report extract for Crystal Report - Bill Print - Adjustments 

******************************************************************************/

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_USER_LANG_CD  CHAR(3)
   DECLARE   @V_BILL_ID       CHAR(12)
   DECLARE   @V_SA_ID         CHAR(12)
   DECLARE   @V_RETURN_CD     INT

   SET @V_RETURN_CD = 0

BEGIN


/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/

   SELECT @V_LANGUAGE_CD = LANGUAGE_CD
   FROM   CI_LANGUAGE
   WHERE  LANGUAGE_CD = @P_LANGUAGE_CD

   IF @@ROWCOUNT = 0
      SET @V_LANGUAGE_CD = 'ENG'

   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_USER_LANG_CD = LANGUAGE_CD
         FROM   SC_USER
         WHERE  SC_USER.USER_ID = @P_USER_ID

         IF @@ROWCOUNT = 0
            BEGIN
               SET @V_USER_LANG_CD = 'ENG'
               SET @V_RETURN_CD = 1003  -- ERROR: INVALID USER ID
            END
      END

   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_BILL_ID = BILL_ID
         FROM CI_BILL
         WHERE BILL_ID = @P_BILL_ID
      
         IF @@ROWCOUNT = 0
            SET @V_RETURN_CD = 1027  -- ERROR: INVALID BILL ID
      END
   
   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_SA_ID = SA_ID
         FROM CI_SA
         WHERE SA_ID = @P_SA_ID
      
         IF @@ROWCOUNT = 0
            SET @V_RETURN_CD = 1028  -- ERROR: INVALID SA ID
      END
   
   IF @V_RETURN_CD = 0
   BEGIN
      SELECT @V_RETURN_CD RETURN_CODE
             ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
             ,FT.BILL_ID
             ,FT.ACCOUNTING_DT
             ,FT.ARS_DT
             ,FT.PARENT_ID
             ,FT.SIBLING_ID
             ,FT.FT_ID
             ,ADJ.ADJ_TYPE_CD
             ,ADJ.CAN_RSN_CD
             ,FT.CUR_AMT
             ,FT.TOT_AMT
             ,FT.FT_TYPE_FLG
             ,FT.SHOW_ON_BILL_SW
             ,FT.CURRENCY_CD
             ,FT.XFERRED_OUT_SW
             ,ADJ.SA_ID
             ,SA.CHAR_PREM_ID
             ,ATL.DESCR_ON_BILL 
      FROM   CI_FT FT
             ,CI_ADJ ADJ
             ,CI_SA SA
             ,CI_ADJ_TYPE ADT
             ,CI_ADJ_TYPE_L ATL  
      WHERE  FT.FT_TYPE_FLG IN ('AD','AX') 
             AND FT.SHOW_ON_BILL_SW = 'Y'  
             AND ADJ.ADJ_ID = FT.SIBLING_ID 
             AND SA.SA_ID = ADJ.SA_ID
             AND ADT.ADJ_TYPE_CD = ADJ.ADJ_TYPE_CD
             AND ATL.ADJ_TYPE_CD = ADT.ADJ_TYPE_CD 
             AND ATL.LANGUAGE_CD = @V_LANGUAGE_CD 
             AND FT.BILL_ID = @V_BILL_ID 
             AND ADJ.SA_ID = @V_SA_ID
      ORDER BY FT.BILL_ID, FT.ARS_DT  
   END
  
   ELSE  
   BEGIN
      SELECT MESSAGE_NBR RETURN_CODE
             ,MESSAGE_TEXT
             ,CAST(NULL AS CHAR) BILL_ID
             ,CAST(NULL AS DATETIME) ACCOUNTING_DT
             ,CAST(NULL AS DATETIME) ARS_DT
             ,CAST(NULL AS CHAR) PARENT_ID
             ,CAST(NULL AS CHAR) SIBLING_ID
             ,CAST(NULL AS CHAR) FT_ID
             ,CAST(NULL AS CHAR) ADJ_TYPE_CD
             ,CAST(NULL AS CHAR) CAN_RSN_CD
             ,CAST(NULL AS INT) CUR_AMT
             ,CAST(NULL AS INT) TOT_AMT
             ,CAST(NULL AS CHAR) FT_TYPE_FLG
             ,CAST(NULL AS CHAR) SHOW_ON_BILL_SW
             ,CAST(NULL AS CHAR) CURRENCY_CD
             ,CAST(NULL AS CHAR) XFERRED_OUT_SW
             ,CAST(NULL AS CHAR) SA_ID
             ,CAST(NULL AS CHAR) CHAR_PREM_ID
             ,CAST(NULL AS VARCHAR) DESCR_ON_BILL
      FROM   CI_MSG_L
      WHERE  MESSAGE_CAT_NBR = 30
             AND MESSAGE_NBR = @V_RETURN_CD
             AND LANGUAGE_CD = @V_LANGUAGE_CD
   END

RETURN
         
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

