Sample Variable for PL/SQL Procedure Source Type

This appendix covers the following topics:
Sample for PL/SQL Procedure Source Type
Sample for PL/SQL Procedure Source Type

REM |  CUSTOM PACKAGE SCPCIFICATION
REM |  =================================================================
REM |Package specification for the custom package to source user defined
REM |variales with procedures.
REM |Write one procedure to return a variable value from a database
REM |table for a given document. The procedure returns a variable 
REM |value for avariable code passed during invocation. The same  
REM |procedure can be overloaded forretunning variable value for
REM |multiple variable codes. For better maintenance,create 
REM |separate procedures for SELL and BUY variables.    
REM |You need to decide whether to use the same or separate procedures
REM |to handle variables for different document types under same inten.
REM |Make sure that you grant execute permission for your procedure 
REM |to the Oracle Application user (APPS by default). 
REM |Please remove Comments /* */ on the package spec and body before 
REM |using.
+=======================================================================*/ 

REM  The following is a sample package specification for contract 
REM  expert custom package. P_VARIABLE_CODE in used to pass the 
REM  user variablecode you enter in the variable creation page 
REM  for a user variablewith procedure and the owner package and 
REM  procedure name of this API should match the value provided 
REM  in"PL/SQL procedure Name" column.X_VARIABLE_VALUE_ID will
REM  be used to return the value of thevariable for a given document.
REM  Name the package meaningfully to show it is a custom package used
REM  for Contract Expert. You can use a single or multiple packages for
REM  multiple variables; but make sure you do not overload to the extent
REM  that the API causes performance issues. The following parameters 
REM  are used in the API:P_DOC_TYPE :Document type parameter.Represents 
REM  document type such as sales order, purchase order, quote etc.
REM  P_DOC_ID : Document header id. For PO this is the
REM  PO_HEADERS.HEADER_ID.
REM  For sales order, this is the OE_ORDER_HEADERS_ALL.HEADER_ID
REM  P_VARIABLE_CODE: Variable code for which the value needs to 
REM  be derived. This it the unique code given when defining the User
REM  variable during setup X_VARIABLE_VALUE_ID : Standard in out
REM  variable to return the value for a given variable code	
REM  X_RETURN_STATUS: Standard out variable to return final
REM  API execution status.
REM  X_MSG_COUNT:Standard out variable to return the number of messages.
REM  X_MSG_DATA:Standard out variable to return the message string.
REM  NOTE: Use all OUT and IN OUT parameter with NOCOPY option.
REM  This API template (specification and body) assumes that the user 
REM  created the following 3 user variables with procedure name as 
REM  "OKC_XPRT_CUSTOM_PACKAGE.GET_OE_HEADER_VALUES".
REM   OE$BLANKET_NUMBER - blanket number associated with a sales order
REM   OE$USER_STATUS_CODE- user status code on the sales order header
REM   table OE$CONTEXT - context value on the sales order header table 

SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
========================================================================
 -  TO CUSTOMIZE : Change the Package Name & Procedure Name only 
 -  in the following package specification.
========================================================================
/* CREATE OR REPLACE PACKAGE OKC_XPRT_CUSTOM_PACKAGE AS

    PROCEDURE GET_OE_HEADER_VALUES (
        P_DOC_TYPE		     IN VARCHAR2,
        P_DOC_ID		     IN NUMBER,
			   P_VARIABLE_CODE		IN VARCHAR2,
	   		X_VARIABLE_VALUE_ID	IN OUT NOCOPY VARCHAR2,
        X_RETURN_STATUS	     OUT NOCOPY VARCHAR2,
        X_MSG_COUNT		     OUT NOCOPY NUMBER,
        X_MSG_DATA		     OUT NOCOPY VARCHAR2
	);
END;
/
commit; */

REM 
/*===================================================================+
REM |  CUSTOM PACKAGE BODY   
REM |  ===================                                            
REM |  Package body for the custom package to source user defined 
REM |  variables with procedures.                            
REM |  This package body will explain how to create a package  
REM |  /procedures for sourcing user defined variable values  
REM |  for a document.Contract Expert will invoke this procedure   
REM |  See sample codeat the end this file)by passing the   
REM |  variable code. This variablecode may be used in the   
REM |  clauses and rules in the contract document.       
REM |  The procedure in this sample package is named as 
REM |  GET_OE_HEADER_VALUES 
REM |  The procedure GET_OE_HEADER_VALUES reads data from 
REM |  OE_ORDER_HEADERS_ALL
REM |  table and returns the value of the three user defined variables 
REM |  OE$BLANK_NUMBER, OE$USER_STATUS_CODE and OE$CONTEXT 
REM |  to the VARIABLE_VALUE_ID when called with   
REM |  the corresponding Variable code.     
REM |
+=====================================================================*/ 

======================================================================
TO CUSTOMIZE: Change the Package Name & Procedure Name only 
in the following package body.
 -   Change SQL query, variable code, document type etc. appropriately.
 -   Replace G_PKG_NAME value with customer procedure name.
======================================================================

/* CREATE OR REPLACE PACKAGE BODY OKC_XPRT_CUSTOM_PACKAGE AS
======================================================================
  - -  Define GLOBAL CONSTANTS
  - -  Always use the package name and procedure name in 
  - -  error messages for easy debug
======================================================================
G_PKG_NAME   CONSTANT   VARCHAR2(200) := 'OKC_XPRT_CUSTOM_PACKAGE'; 
G_APP_NAME		CONSTANT   VARCHAR2(3)   := OKC_API.G_APP_NAME;
G_MODULE     CONSTANT   VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';

    - - 
    - -  The following lines define true, false and product constants
    - - 
    
 G_FALSE     CONSTANT   VARCHAR2(1) := FND_API.G_FALSE;
 G_TRUE      CONSTANT   VARCHAR2(1) := FND_API.G_TRUE;
 G_OKC       CONSTANT   VARCHAR2(3) := 'OKC';

 - - 
 - -  The following lines define The return status from the procedure
 - -  The procedure must return on of these statuses in X_RETURN_STATUS
 - - 
 G_RET_STS_SUCCESS          CONSTANT   varchar2(1) := FND_API.G_RET_STS_SUCCESS;
 G_RET_STS_ERROR            CONSTANT   varchar2(1) := FND_API.G_RET_STS_ERROR;
 G_RET_STS_UNEXP_ERROR      CONSTANT   varchar2(1) := 
FND_API.G_RET_STS_UNEXP_ERROR;

 G_UNEXPECTED_ERROR  CONSTANT   varchar2(200) := 'OKC_UNEXPECTED_ERROR';
 G_SQLERRM_TOKEN     CONSTANT   varchar2(200) := 'ERROR_MESSAGE';
 G_SQLCODE_TOKEN     CONSTANT   varchar2(200) := 'ERROR_CODE';


    PROCEDURE GET_OE_HEADER_VALUES (
        P_DOC_TYPE		     IN VARCHAR2,
        P_DOC_ID		     IN NUMBER,	
			P_VARIABLE_CODE		IN VARCHAR2,
			X_VARIABLE_VALUE_ID	IN OUT NOCOPY VARCHAR2,
        X_RETURN_STATUS	     OUT NOCOPY VARCHAR2,
        X_MSG_COUNT		     OUT NOCOPY NUMBER,
        X_MSG_DATA		     OUT NOCOPY VARCHAR2
	)
    IS

=======================================================================
- - TO CUSTOMIZE: Change the l_api_name value to this custom procedure 
- -  Define local variables for variables addressed in this API
- -  Create appropriate cursor statements (use p_doc_id parameter)
=======================================================================
l_api_name	CONSTANT VARCHAR2(30) := 'GET_OE_HEADER_VALUES';
l_blanket_number	OE_ORDER_HEADERS_ALL.BLANKET_NUMBER%TYPE;
l_user_status_code
	OE_ORDER_HEADERS_ALL.USER_STATUS_CODE%TYPE;
	   l_context OE_ORDER_HEADERS_ALL.CONTEXT%TYPE; 

	   - - 
	   - -  Define Cursor to read the variable value for the document
	   - -  If you are reading data from multiple tables with multiple SELECT statements,
	    -  define all cursors here with appropriate names.
	    - 
	    -  The following cursor is defined to retrieve values for the user defined variables
	    -  for a sales order (DOCUMENT_TYPE = 'O')
	    - 
        Cursor l_oe_header_csr Is
	    SELECT BLANKET_NUMBER, 
              USER_STATUS_CODE, 
              CONTEXT
	    FROM OE_ORDER_HEADERS_ALL
	    WHERE HEADER_ID = p_doc_id;

    BEGIN


	IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) 
THEN
	     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
        '100: Entered '||G_PKG_NAME ||'.'||l_api_name);
	     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name, 
'P_DOC_TYPE: '||P_DOC_TYPE);
	      FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name, 
'P_DOC_ID: '||P_DOC_ID);
	      FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name, 
'P_VARIABLE_CODE: '||P_VARIABLE_CODE);
     END IF;

	x_return_status := G_RET_STS_SUCCESS;
======================================================================
TO CUSTOMIZE: Check for appropriate P_DOC_TYPE
Change cursor names and INTO variables with local variables 
defined above  
Modify IF...ELSE statements appropriately to assign correct 
to the parameter
======================================================================
        	
	IF P_DOC_TYPE = 'O' THEN
              OPEN l_oe_header_csr;
	      FETCH l_oe_header_csr INTO l_blanket_number, l_user_status_code, l_context;
	      CLOSE l_oe_header_csr;

              IF P_VARIABLE_CODE = 'OE$BLANKET_NUMBER' THEN
			            X_VARIABLE_VALUE_ID := l_blanket_number;
			      
              ELSIF P_VARIABLE_CODE = 'OE$USER_STATUS_CODE' THEN
			            X_VARIABLE_VALUE_ID := l_user_status_code;
			      
              ELSIF P_VARIABLE_CODE = 'OE$CONTEXT' THEN
			            X_VARIABLE_VALUE_ID := l_context;
			      
              END IF;
        END IF;


	IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) 
THEN

	     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
        '1000: Variable Codes along with values:');

              FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
			     'Variable Code and Value' || ' ' || P_VARIABLE_CODE ||
	           ' = ' || X_VARIABLE_VALUE_ID );

	     FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
        '1000: Leaving '||G_PKG_NAME ||'.'||l_api_name);
        END IF;

    EXCEPTION
         - -
         - - retain all error handling below as it is. Do not change
	      - - 
========================================================================
- -  TO CUSTOMIZE: Close all cursors
- -  DO NOT delete debug statements.
- -  Add more debug statements if required. 
- -  Follow the same structure for debug statements
========================================================================
                
        WHEN FND_API.G_EXC_ERROR THEN
	    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) 
THEN
	           FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
           '1001: Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
            END IF;

            IF l_oe_header_csr%ISOPEN THEN
            CLOSE l_oe_header_csr;
            END IF;

	    - - 
	    - -  if you have more cursors, add cursor closing statements here as shown above
	     - 

        x_return_status := G_RET_STS_ERROR ;
		  FND_MSG_PUB.Count_And_Get(p_encoded =>'F',
	     p_count   => x_msg_count,
	     p_data    => x_msg_data );

       WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
	    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) 
THEN
	    FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
      '1002: Unexpected Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
       END IF;

       IF l_oe_header_csr%ISOPEN THEN
       CLOSE l_oe_header_csr;
       END IF;

	    - - 
	    - -  if you have more cursors, add close cursor statements here as shown above
	    - - 

        x_return_status := G_RET_STS_UNEXP_ERROR ;
		  FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => 
x_msg_count, p_data => x_msg_data );

       WHEN OTHERS THEN
	    IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) 
THEN
	    FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
       '1003: Other Error: Leaving '||G_PKG_NAME ||'.'||l_api_name);
       END IF;

       IF l_oe_header_csr%ISOPEN THEN
       CLOSE l_oe_header_csr;
       END IF;

- -
- -   if you have more cursors, add close cursor statements here as shown above
- - 

      x_return_status := G_RET_STS_UNEXP_ERROR ;
		FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, 
p_data => x_msg_data );

    END GET_OE_HEADER_VALUES;
END;
/
commit; */

REM /********************************************************************
REM TO CUSTOMIZE: DELETE ALL CODE FROM THE ABOVE LINE TO THE END 
REM  OF THIS FILE
REM Change values of p_doc_type, p_doc_id, l_variable_code and  
REM the procedure name
REM The following sample code shows how to invoke the above procedure 
REM in PL/SQL. Customers are not required to write this code, since  
REM the customer procedures are always invokedfrom Contract Expert.
REM 
REM Customers can copy the following code and create a test procedure  
REM to test their custom APIs to check return variable values. 
REM Create a document and pass the document type and document id withthe 
REM variable used in the clause or rule (in the document)
REM 
**************************************************************************/

DECLARE

   l_msg_count NUMBER;
   l_msg_data VARCHAR2(100);
   l_return_status VARCHAR2(1);
   l_variable_code VARCHAR2(30) := 'OE$BLANKET_NUMBER';
   l_variable_value_id VARCHAR2(200):= NULL;

BEGIN

   OKC_XPRT_CUSTOM_PACKAGE.GET_OE_HEADER_VALUES (
   p_doc_type => 'O',
	p_doc_id	=> 154333,
	p_variable_code => l_variable_code,
	x_variable_value_id => l_variable_value_id,
	x_return_status => l_return_status,
	x_msg_count	=> l_msg_count,
	x_msg_data	=> l_msg_data
   );

   dbms_output.put_line ( 'Return Status is ' || l_return_status);
   dbms_output.put_line ( 'and the table Variable value after the procedure execution is:');
  
   dbms_output.put_line(SubStr('Variable Code = '||l_variable_code,1,255));
   dbms_output.put_line(SubStr('Variable Vale Id = '||l_variable_value_id,1,255));


END;
/
COMMIT;
EXIT;\