This appendix covers the following topics:
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;\