- Queue tables will be designed to maximize re-use and multiple inbound and outbound queues will reside in a single queue table.
- Message data being published or subscribed by ERP via the Hub will be in XML format.
User Roles/Object Privileges: On the ERP instance, all queueing and messaging should be run under the APPS user and, as such, APPS requires and should already have the user role:
AQ_ADMINISTRATOR_ROLE
And the object privileges:
EXECUTE ON DBMS_AQ
EXECUTE ON DBMS_AQADM
Since the queue tables, indexes etc will reside under a custom schema (see next heading), that schema will also require this role and object privileges.
Queue Tables: ERP customizations using queues should create the queue tables and indexes in the customization’s schema. For example, interfaces to/from external applications will use the INFX schema.
Queue tables should be designed to maximize use. Multiple inbound and outbound queues for manipulating messages on various types of business data such as customer, order, invoice, product, purchase order, etc. can utilize a single queue table. The important factors to consider in deciding whether to implement a new queue table depend on the personality assigned to the queue table at creation (will the messages have a single or multiple consumers, will the messages be sorted in ascending or descending order and by what attributes, what is the object type of the message’s payload).
For ERP interfaces using the ERP Hub, a single queue table will be defined as follows in the INFX schema. This queue table uses a custom object type to define the payload as an XML-formatted message in a CLOB, it sets the sorting order by priority and timestamp, and sets it for multiple consumer consumption as required by the Hub.. Interface developers should use this queue table for both inbound and outbound messages that fit within the properties of this queue table. Following are examples: These database objects do not yet exist in ERP:
Create the Queue Table, Inbound and Outbound Queues as the INFX user:
ASC_OAI_STD_MSG_TYPE (custom object type)
ASC_OAI_QTAB (custom queue table)
ASC_OAI_INQ (inbound queue)
ASC_OAI_OUTQ (outbound queue)
Here is how this queue table is created:
Custom Object Type:
Sign onto SQL*Plus as the custom user (ie, INFX/INFX) and create the custom object type for the message payload, if this object type does not already exist (script NOAImsgtyp.sql):
CREATE OR REPLACE
TYPE ASC_OAI_STD_MSG_TYPE AS OBJECT(
APPLICATION VARCHAR2(30),
PUBLISH_DATE DATE,
OBJECT_NAME VARCHAR2(100),
EVENT_NAME VARCHAR2(100),
CORRELATION_ID VARCHAR2(100),
XML_DATA CLOB)
/
GRANT EXECUTE ON ASC_OAI_STD_MSG_TYPE TO public
/
Queue Table and In/Out Queues:
Then execute the following NOAIqcreat.sql script that will create the ASC_OAI_QTAB table, then define the ASC_OAI_INQ and ASC_OAI_OUTQ queues. This script checks whether the queues already exists and execute the correct DBMS_AQADM procedures:
REM============================================================
REM
REM Create New Queue Table
REM
REM============================================================
declare
queue_table_exists exception;
pragma EXCEPTION_INIT(queue_table_exists, -24001);
begin
dbms_output.put_line('===================================');
dbms_output.put_line('Creating OAI Queue Tables ');
dbms_output.put_line('===================================');
begin
dbms_aqadm.create_queue_table
(
queue_table => 'ASC_OAI_QTAB',
queue_payload_type => 'ASC_OAI_STD_MSG_TYPE',
sort_list => 'PRIORITY,ENQ_TIME',
comment => 'OAI application queue',
multiple_consumers => TRUE,
compatible => '8.1'
);
exception
when queue_table_exists then
null;
when others then
dbms_output.put_line('Oracle Server Error = 'to_char(sqlcode));
dbms_output.put_line('Oracle Server Message = 'sqlerrm);
raise_application_error(-20000, 'Oracle Error Mkr1= '
to_char(sqlcode)' - 'sqlerrm);
end;
end;
/
REM====================================================================
REM
REM Create New Queue
REM
REM====================================================================
declare
queue_exists exception;
pragma EXCEPTION_INIT(queue_exists, -24006);
BEGIN
dbms_output.put_line('=================================');
dbms_output.put_line('Creating all Queues');
dbms_output.put_line('=================================');
begin
dbms_aqadm.create_queue
(
queue_name => 'ASC_OAI_INQ',
queue_table => 'ASC_OAI_QTAB',
comment => 'OAI Application In Queue',
retention_time => 60*60*24 /* ONE day in seconds */
);
dbms_aqadm.create_queue
(
queue_name => 'ASC_OAI_OUTQ',
queue_table => 'ASC_OAI_QTAB',
comment => 'OAI Application Out queue',
retention_time => 60*60*24 /* ONE day in seconds */
);
exception
when queue_exists then
null;
when others then
dbms_output.put_line('Oracle Server Error = 'to_char(sqlcode));
dbms_output.put_line('Oracle Server Message = 'sqlerrm);
raise_application_error(-20000, 'Oracle Error Mkr4= '
to_char(sqlcode)' - 'sqlerrm);
end;
END;
/
Start the Queues as the APPS user:
See script NOAIqstrt.sql
REM===================================================================
REM
REM Start Queue
REM
REM===================================================================
declare
begin
dbms_output.put_line('============================');
dbms_output.put_line('Starting all Queues ');
dbms_output.put_line('============================');
dbms_aqadm.start_queue(queue_name => 'ASC_OAI_INQ');
dbms_aqadm.start_queue(queue_name => 'ASC_OAI_OUTQ');
exception
when others then
dbms_output.put_line('Oracle Server Error = 'to_char(sqlcode));
dbms_output.put_line('Oracle Server Message = 'sqlerrm);
raise_application_error(-20000, 'Oracle Error Mkr7= '
to_char(sqlcode)' - 'sqlerrm);
end;
/
commit;
Views/Triggers/Packages/Functions : All views, triggers, stored procedures, packages and functions for ERP AQ Messaging to the Hub should be prefixed ASC_OAI_ , be suffixed as described in the Database Objects document and be created in the APPS schema.
Source Files/SQL Scripts
All source files, SQL scripts etc related to ERP AQ Messaging to the Hub should be named with the prefix NOAI followed by meaningful string and extension as documented in the Database Objects document. See the Interface Standards document for more information on file naming standards.
Defining Metadata and the Hub Repository :
- Get the ERP adapter updated and registered
- Generate the XML DTDs
- iStudio tool to map Common Views of the data and set up subscribers/publishers
- Identify the ways to monitor / test messages routed through the Hub and processes executing on the Hub
Processing on the Hub : If interface design drives the moving of processing from the ERP instance to the HUB instance, additional guidelines should be developed. There is no APPS schema on the hub. This is an Oracle 8i database with 9iAS for message management and is where the messaging repository resides. The apps are not installed so there is no Concurrent Manager, etc. However, there is the Business Events System and Workflow 2.6 available to developers on this instance.
External Applications : Any database objects that ERP developers may help to create on an external application’s database in moving to AQ Messaging will follow that application’s development standards wherever possible.
No comments:
Post a Comment