SQL Queries

Table Meaning used in SQL Queries.

OBJ- resource object information

App_instance -  application instance information

EVT - Defines event handlers by providing a process and class name. In addition the scheduling time of when the event handler can execute is set to pre (insert, update, delete) or post (insert, update, delete)

PKD - Package dependency table holds the dependency relationships between child packages of a parent package

PKG - Consists of names and system keys of service processes, which consist of a group ofservices from the TOS table. Defines a Process in Xellerate.

TOS - Holds information about a process.

SDK - User define data object meta data definition.

SRE - Defines Which Pre-populate Rule Generator Will Run For A Field Of User Defined DataObject.

ADP - Defines an adapter created through the Adapter Factory.

RAV - Stores the runtime data mappings for 'Pre-populate' adapters. The data source being an Xellerate form or child table, or a user defined form.

ADV - Adapter variable table contains variables that have been created for specific adapters.

SDC - Column Metadata.

MIL - Holds information about tasks of a process

Different process tasks and mapped adapters in any process definition of a resource:

select mil.mil_name,evt.evt_name,obj.obj_name 

from obj join pkg on  pkg.obj_key=obj.obj_key join tos on tos.pkg_key=pkg.pkg_key join mil on

mil.tos_key=tos.tos_key  left outer join evt on mil.evt_key=evt.evt_key

where obj.obj_name='<Resource Object Name>';

Process forms attached to Resource Objects based on app display name :

select obj_name,sdk_name 

from 

tos,pkg,sdk,obj, app_instance where tos.pkg_key=pkg.pkg_key

and tos.sdk_key=sdk.sdk_key and obj.obj_key=app_instance.object_key

and PKG.OBJ_KEY=obj.obj_key and APP_INSTANCE_DISPLAY_NAME='<Application Instance Name>';

Pre-populate Adapter and Adapter Mapping  of  process form :

select  sdc_label,adp.adp_name, rav_map_to,rav_map_qualifier,rav_map_value,sre_order

from

sre, sdk, evt , adp, rav,  adv, sdc  where sre.SDK_KEY=sdk.sdk_key and sre.EVT_KEY=evt.evt_key

and adp.EVT_KEY=evt.evt_key and rav.SRE_KEY=sre.sre_key and sdc.SDC_NAME=sre.sre_sdc_name

and rav.adv_key=adv.adv_key and adv.adp_key=adp.adp_key

and sdk.sdk_name=<Process Form Name>  order by sre_order asc ;

ORA-00001: unique constraint (IDM_OIM.PK_USR) violated at line number xxxx

Issue - ORA-00001: unique constraint (IDM_OIM.PK_USR) violated at line number 1308
 

Problem Summary - Bulk Load Failed due to users already present with the usr_key, so find out the failed users from table
select * from USR_11G_FROM_PROD_EX1;
Update the usr_key and bulk load the failed users.
 

Solution - Onboard the failed users with different usr_key