-- ******************************************************************
-- *
-- * 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 = 'CIBLPRTP' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIBLPRTP 
GO

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

/******************************************************************************
   NAME:       CIBLPRTP
   PURPOSE:    Supply report extract for Crystal Report - Bill Print - Count Adjs per SA 

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

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_BILL_ID       CHAR(12)
   DECLARE   @V_SA_ID         CHAR(10)
   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   SC_USER
   WHERE  SC_USER.USER_ID  = @P_USER_ID

   IF @@ROWCOUNT = 0
      BEGIN
         SET @V_LANGUAGE_CD = 'ENG'
         SET @V_RETURN_CD = 1003  -- ERROR: INVALID USER ID 
      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
   -- Count Adjs per SA
   BEGIN
      SELECT @V_RETURN_CD RETURN_CODE
             ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
             ,COUNT(*) COUNT
      FROM   CI_FT FT 
             ,CI_ADJ ADJ 
             ,CI_SA SA  
      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 FT.BILL_ID = @V_BILL_ID 
             AND SA.SA_ID = @V_SA_ID 		  
   END
  
   ELSE  
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS INT) COUNT
         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

