PL/SQL: Tips for Oracle SQL Developer:

Query to see log files that have been created and which user they have been allocated to


select * from S_SRM_TASK_HIST;


ALTER SESSION to replicate Siebel when running SQL Queries

Did you know that when you run queries directly on SQL developer or TOAD, it runs it with different parameters than what Siebel does? To ensure a similar performance and SQL execution as Siebel, run the following ALTER SESSION commands before running your query:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE; 

ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE;

ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE;

The following flushes the Data Cache for testing SQL performance again and again

You need the correct rights to run this!

      alter system flush buffer_cache

Setting SQL Developer to use Datetime

Tools > Preferences > Database -- NLS Parameters

Datetime Format: DD-MON-RRRR HH24:MI:SS

Date Format: DD-MON-RR

Alternatively run the following command:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

Then you can run the following query without the TO_DATE function:

                SELECT count(*) 

                FROM SIEBEL.S_EVT_FUL_REQ T1, SIEBEL.S_USER

                WHERE T1.CREATED_BY = SIEBEL.S_USER.ROW_ID (+)

                AND SIEBEL.S_USER.LOGIN IN ('REHMANA')

                AND T1.CREATED >= '20/07/2014 07:00:00';

Using Dates in Queries

select COUNT(*) FROM SIEBEL.S_ORG_EXT

WHERE ACCNT_TYPE_CD = 'Provider'

AND X_HC_RENEWAL_DT >= TO_DATE('01/06/2013','DD/MM/YYYY')

AND X_HC_RENEWAL_DT < TO_DATE('01/09/2013','DD/MM/YYYY')

ORDER BY X_HC_RENEWAL_DT ASC;

UPDATING A DATE

UPDATE  EVENT_FUND
SET CREATION_DATE = TO_DATE('01042016', 'DDMMYYYY')where EVENT_ID = '3003650'

Using sysdate in Queries

select attrib_03, attrib_04, count(ext.row_id) from siebel.s_srv_req base, siebel.s_srv_req_x ext

where base.row_id = ext.row_id

and base.created > sysdate-365

and sr_stat_dt is not null

group by attrib_03, attrib_04

order by count(ext.row_id) desc;

Updating Dates

UPDATE SIEBEL.S_CASE_XSET X_FAC_DUE_DT =  to_date('27-APR-13', 'dd-mon-yy') WHERE PAR_ROW_ID = '1-BH0GR5'

Concatenating Columns in a select statement

select a.OU_ID || a.PROD_ID

from siebel.S_ORG_PROD a, siebel.S_PROD_INT b

where b.TYPE = 'GST'

and A.PROD_ID = b.ROW_ID

group by a.OU_ID || a.PROD_ID

having count(0) > 1

select distinct PER_TITLE || ' ' || FST_NAMe || ' ' || LAST_NAME, count(0) 

from siebel.S_CONTACT

group by PER_TITLE || ' ' || FST_NAMe || ' ' || LAST_NAME

having count(0) > 1

order by count(0) desc

A Select query feeding into Select query

Example 1

select

T1.ROW_ID,

T1.CREATED,

T4.LOGIN AS CREATED_BY,

T1.LAST_UPD,

T3.LOGIN AS LAST_UPD_BY,

T1.DB_LAST_UPD,

T1.DB_LAST_UPD_SRC,

T1.MODIFICATION_NUM,

T1.PARTY_ID,

T1.REL_PARTY_ID,

T2.NAME

from   SIEBEL.S_PARTY_REL T1

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T2

ON T1.PARTY_ID = T2.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T3

ON T1.LAST_UPD_BY = T3.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T4

ON T1.LAST_UPD_BY = T4.PAR_ROW_ID

WHERE PARTY_ID IN

(SELECT T1.PARTY_ID  from   SIEBEL.S_PARTY_REL T1

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T2 ON  T1.PARTY_ID = T2.ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T3 ON  T1.REL_PARTY_ID = T3.ROW_ID

WHERE   T1.REL_TYPE_CD = 'Provider'

AND     T2.ACCNT_TYPE_CD = 'Location'

AND     T3.ACCNT_TYPE_CD = 'Provider'

GROUP BY T1.PARTY_ID, T2.NAME

having count(*) > 1)

AND T1.REL_TYPE_CD = 'Provider'

ORDER BY T1.CREATED ASC 

Example 2


select ORG_ID, PROD_ID, LAST_UPD from 
(SELECT 
ORG_PROD.OU_ID ORG_ID, 
ORG_PROD.PROD_ID PROD_ID,
ORG_PROD.LAST_UPD LAST_UPD,
PROD.TYPE, COUNT(*)
FROM SIEBEL.S_ORG_PROD ORG_PROD INNER JOIN SIEBEL.S_PROD_INT PROD ON PROD.ROW_ID=ORG_PROD.PROD_ID AND TYPE='GST'
GROUP BY ORG_PROD.OU_ID, ORG_PROD.PROD_ID, PROD.TYPE
HAVING COUNT(*) > 1)

order by ORG_ID, PROD_ID desc


Group By and Count on Two or more Columns

Select 
Count(*),
B.NAME,
C.NAME
from SIEBEL.S_TMSHT_ITEM A
LEFT OUTER JOIN SIEBEL.S_PROJ B
ON A.PROJ_ID = B.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROJITEM C
ON A.PROJITEM_ID = C.ROW_ID
GROUP BY B.NAME, C.NAME

   select Count(*), A.PROC_NAME , A.STATUS_CD
   FROM SIEBEL.S_WFR_PROC A
   Left Outer Join SIEBEL.S_REPOSITORY B
   ON A.REPOSITORY_ID = B.ROW_ID
   WHERE B.NAME = 'Siebel Repository'
   AND A.STATUS_CD = 'COMPLETED'
   GROUP BY A.PROC_NAME, A.STATUS_CD
   HAVING Count(*) > 1

Using MINUS to subtract Select Query Result a from Select Query Result B

--------All Providers that have one Active Location------
--------SELECT QUERY A: NOTE Only outputs one Column-----
select
A.ROW_ID
from SIEBEL.S_ORG_EXT A
INNER JOIN SIEBEL.S_ORG_EXT B
ON A.PAR_ROW_ID = B.PAR_OU_ID
-------Provider-----------------
WHERE A.OU_TYPE_CD = 'Primary Dental Care'
AND A.ACCNT_TYPE_CD = 'Provider'
AND A.CUST_STAT_CD = 'Registered'
-------Location-----------------
AND B.OU_TYPE_CD = 'Primary Dental Care'
AND B.ACCNT_TYPE_CD = 'Location'
AND B.CUST_STAT_CD = 'Active'
GROUP BY A.ROW_ID
having count(*) = 1
MINUS
------- All Providers that have a location with Product DEN
--------SELECT QUERY B: NOTE Only outputs one Column--------
select
A.ROW_ID
from SIEBEL.S_ORG_EXT A
INNER JOIN SIEBEL.S_ORG_EXT B
ON A.PAR_ROW_ID = B.PAR_OU_ID
LEFT OUTER JOIN SIEBEL.S_ORG_PROD C
ON B.PAR_ROW_ID = C.OU_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT D
ON C.PROD_ID = D.ROW_ID
-------Provider-----------------
WHERE A.OU_TYPE_CD = 'Primary Dental Care'
AND A.ACCNT_TYPE_CD = 'Provider'
AND A.CUST_STAT_CD = 'Registered'
-------Location-----------------
AND B.OU_TYPE_CD = 'Primary Dental Care'
AND B.ACCNT_TYPE_CD = 'Location'
AND B.CUST_STAT_CD = 'Active'
-------Product-------------------
AND D.NAME =  'DEN'

select   A.INTEGRATION_ID  FROM SIEBEL.S_ORG_EXT A  LEFT OUTER JOIN SIEBEL.S_ORG_EXT C  ON A.ROW_ID = C.PAR_OU_ID  WHERE A.OU_TYPE_CD = 'Primary Medical Services'  AND C.ACCNT_TYPE_CD = 'Location'   AND C.CUST_STAT_CD = 'Active'  GROUP BY A.INTEGRATION_ID  HAVING Count(*) = 1 INTERSECT select   A.INTEGRATION_ID  FROM SIEBEL.S_ORG_EXT A  LEFT OUTER JOIN SIEBEL.S_ORG_EXT_XM B ON A.ROW_ID = B.PAR_ROW_ID  WHERE A.OU_TYPE_CD = 'Primary Medical Services' AND B.TYPE = 'Location' AND B.ATTRIB_04 = 'GP Practice'    GROUP BY A.INTEGRATION_ID  HAVING Count(*) = 1 

Bringing back the n number of rows

Just include the following AND statement in the WHERE clause

AND rownum <= 29001


Updating Two Columns on a Table and avoiding two updates

UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);

update EIM_POSITION a SET
a.PARTY_UID = (select b.ROW_ID from siebel.S_USER b where b.LOGIN = a.ACC_COMMENTS)
where a.ACC_COMMENTS in
(select b.LOGIN from siebel.S_USER b where a.ACC_COMMENTS = b.LOGIN)

Setting bring back a value according to a condition

SELECT
A.ROW_ID AS ORG_ROW_ID,
'UPDATE' AS ACTION,
CASE WHEN a.OU_TYPE_CD = 'NHS Healthcare Organisation'  THEN '01/04/2010  00:00:00'
     WHEN a.OU_TYPE_CD = 'Social Care Org'              THEN '01/10/2010  00:00:00'
     WHEN a.OU_TYPE_CD = 'Independent Healthcare Org'   THEN '01/10/2010  00:00:00'     
     WHEN a.OU_TYPE_CD = 'Primary Dental Care'          THEN '01/04/2011  00:00:00'   
     WHEN a.OU_TYPE_CD = 'Independent Ambulance'        THEN '01/04/2011  00:00:00'  
     WHEN a.OU_TYPE_CD = 'Primary Medical Services'     THEN '01/10/2011  00:00:00'    
     ELSE 'IGNORE'
END AS "NEW_DATE"
------------------------------------
from SIEBEL.S_ORG_EXT A
LEFT OUTER JOIN SIEBEL.S_ORG_PROD B
ON A.PAR_ROW_ID = B.OU_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT C
ON B.PROD_ID = C.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORG_EXT_X D
ON A.PAR_ROW_ID = D.ROW_ID
where 
a.OU_TYPE_CD = 'NHS Healthcare Organisation' 
AND b.WARRANTY_START_DT < TO_DATE('01/04/2010','DD/MM/YYYY')
AND C.TYPE = 'Regulated Activity'; 

Useful SQL to see all the Active Columns on a List Applet

SELECT
  a.name,
  c.name,
  e.name as List_name,
  e.field_name as Field_Name,
  f.row_id,
  g.type,
  g.join_name,
  g.col_name,
  g.mvlink_name
FROM siebel.s_applet a
LEFT OUTER JOIN siebel.s_appl_web_tmpl c ON a.row_id = c.applet_id
LEFT OUTER JOIN siebel.s_list d on A.row_id = d.applet_id
LEFT OUTER JOIN siebel.s_list_column e on d.row_id = e.LIST_ID
LEFT OUTER JOIN siebel.s_buscomp f on A.buscomp_name = f.NAME
LEFT OUTER JOIN siebel.s_field g on f.row_id = g.buscomp_id
WHERE a.name IN('Service Request List Applet')
 AND a.repository_id = '1-36VK-1'
 AND c.repository_id = '1-36VK-1'
 AND d.repository_id = '1-36VK-1'
 AND d.repository_id = '1-36VK-1'
 AND f.repository_id = '1-36VK-1'
 AND c.name = 'Edit List'
 AND e.HTML_TYPE IN ('Field','TextArea','CheckBox')
 AND e.INACTIVE_FLG = 'N'
 AND g.name = e.field_name

Working out if a field appears on any screen 
Select distinct
A.NAME AS VIEW_NAME
--B.WEB_TEMPLATE AS WEB_TEMPLATE,
--C.NAME AS APPLET_NAME
FROM Siebel.S_VIEW A
LEFT OUTER JOIN Siebel.S_VIEW_WEB_TMPL B
ON B.VIEW_ID = A.ROW_ID
LEFT OUTER JOIN Siebel.S_VIEW_WTMPL_IT C
ON C.VIEW_WEB_TMPL_ID = B.ROW_ID
WHERE C.NAME IN 
(-- SQL listing all List Columns
select 
A.NAME AS APPLET_NAME
from SIEBEL.S_APPLET A
LEFT OUTER JOIN SIEBEL.S_APPL_WEB_TMPL B
ON B.APPLET_ID = A.ROW_ID
LEFT OUTER JOIN SIEBEL.S_APPL_WTMPL_IT C
ON C.APPL_WEB_TMPL_ID = B.ROW_ID
WHERE 
--Code to find all relevant List Applet Controls
--Specifing the fields that are being searched for
((B.NAME = 'Edit List' AND (C.CTRL_NAME = 'Assigned To' OR C.CTRL_NAME = 'Owner') AND C.REPOS_TYPE = 'List Item')
OR
--Code to find all relevant Form Applet Controls
--Specifing the fields that are being searched for
(B.NAME = 'Edit' AND (C.CTRL_NAME = 'Assigned To' OR C.CTRL_NAME = 'Owner') AND C.REPOS_TYPE = 'FormattedHtml'))
)
--And additional filter on the View and it's name
AND A.NAME LIKE 'CQC%Enq%' OR A.NAME LIKE 'HC%Enq%'
ORDER BY A.NAME ASC

MAPs from LIST APPLET To BC To TABLE To COLUMN

select
Applet.NAME                           AS APPLET_NAME,
--AppletWebTemplate.NAME                AS WEB_TEMPLATE_NAME,
--AppletWebTemplateItem.CTRL_NAME       AS WEB_TEMPLATE_ITEM_CTRL_NAME,
--AppletList.NAME                       AS LIST_NAME,
--AppletList.ROW_ID                     AS LIST_ID,
--AppletListColumn.ROW_ID               AS APPLET_LIST_COLUMN_ROW_ID,
--AppletListColumn.LIST_ID              AS APPLET_LIST_COLUMN_LIST_ID,
--AppletListColumn.NAME                 AS LIST_COLUMN_NAME,
--AppletListColumn.DISPLAY_NAME_REF     AS DISPLAY_NAME_REF,
--SymbolicString.ROW_ID,
--SymbolicString.NAME                   AS SymbolicStringNAME,
--SymbolicStringTrans.STRING_VALUE    AS LABEL
CASE
    WHEN SymbolicStringTrans.STRING_VALUE IS NOT NULL THEN SymbolicStringTrans.STRING_VALUE
    ELSE AppletListColumn.DISPLAY_NAME
END                                   AS APPLET_FIELD_LABEL,
BusComp.NAME                          AS BUSCOMP,
Field.NAME                            AS FIELD_NAME,
BusComp.TABLE_NAME,
Field.COL_NAME,
--Field.COL_NAME
DBView.ALIAS AS DATABASE_VIEW,
DBViewColumn.ALIAS AS DATABASE_VIEW_COLUMN,
CONCAT(CONCAT(DBView.ALIAS,'_'),DBViewColumn.ALIAS) AS KEY

FROM SIEBEL.S_APPLET Applet
INNER JOIN SIEBEL.S_APPL_WEB_TMPL AppletWebTemplate ON AppletWebTemplate.APPLET_ID = Applet.ROW_ID
INNER JOIN SIEBEL.S_APPL_WTMPL_IT AppletWebTemplateItem ON AppletWebTemplateItem.APPL_WEB_TMPL_ID = AppletWebTemplate.ROW_ID
INNER JOIN SIEBEL.S_LIST AppletList ON AppletList.APPLET_ID = Applet.ROW_ID
INNER JOIN SIEBEL.S_LIST_COLUMN AppletListColumn ON AppletListColumn.LIST_ID = AppletList.ROW_ID
LEFT OUTER JOIN SIEBEL.S_SYM_STR SymbolicString ON AppletListColumn.DISPLAY_NAME_REF = SymbolicString.NAME
INNER JOIN SIEBEL.S_REPOSITORY Repository ON SymbolicString.REPOSITORY_ID = Repository.ROW_ID
LEFT OUTER JOIN SIEBEL.S_SYM_STR_INTL SymbolicStringTrans ON SymbolicStringTrans.SYM_STR_ID = SymbolicString.ROW_ID
INNER JOIN SIEBEL.S_BUSCOMP BusComp ON Applet.BUSCOMP_NAME = BusComp.NAME
INNER JOIN SIEBEL.S_FIELD Field ON Field.NAME = AppletListColumn.FIELD_NAME
INNER JOIN SIEBEL.S_TABLE DBView ON BusComp.TABLE_NAME = DBView.NAME
INNER JOIN SIEBEL.S_COLUMN DBViewColumn ON DBViewColumn.NAME = Field.COL_NAME
WHERE applet.NAME LIKE 'WP OES%List Applet'

AND applet.INACTIVE_FLG = 'N'
AND AppletListColumn.INACTIVE_FLG = 'N'
AND AppletWebTemplate.NAME = 'Edit List'
AND AppletList.NAME = 'List'
AND AppletWebTemplateItem.ITEM_NUM > 500
AND AppletWebTemplateItem.CTRL_NAME = AppletListColumn.NAME
AND Repository.NAME = 'Siebel Repository'
AND Field.BUSCOMP_ID = BusComp.ROW_ID
AND DBViewColumn.TBL_ID = DBView.ROW_ID

ORDER BY DBView.ALIAS DESC, DBViewColumn.ALIAS DESC
WORKFLOWS

SQL to find out if the current version of the Workflow.

Is the latest version in the Repository.

  

  SELECT  

          wdd.RoW_ID,

          wdd.created, 

          usr_crt.login created_login, 

          wdd.last_upd,

          usr_upd.login updated_login, 

          wdd.NAME  DEPLOYED_NAME, 

          wdd.REPOSITORY_VERSION  DEPLOYED_VERSION,

          wdd.deploy_status_cd  DEPLOYED_STATUS,

          REP_WF.NAME AS REP_NAME,

          REP_WF.VERSION  REP_VERSION,

          REP_WF.STATUS_CD  REP_STATUS

    FROM siebel.s_wfa_dploy_def wdd,

         siebel.s_user usr_crt,

         siebel.s_user usr_upd,

         SIEBEL.S_WFR_PROC REP_WF,

         SIEBEL.S_REPOSITORY REPOS

   WHERE wdd.created_by = usr_crt.row_id 

   AND wdd.last_upd_by = usr_upd.row_id

   AND REP_WF.NAME = concat(concat(wdd.NAME , ': '), wdd.REPOSITORY_VERSION)

   AND REP_WF.REPOSITORY_ID = REPOS.ROW_ID

   AND wdd.deploy_status_cd = 'ACTIVE'

   AND wdd.NAME NOT LIKE '%Simulation%'

   AND REPOS.NAME = 'Siebel Repository'

   AND REP_WF.STATUS_CD <> 'COMPLETED'

Running SQL a SCRIPT to generate results in DBMS Output Tab

DECLARE

  V_RESULT VARCHAR2(7);

  

BEGIN

  SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_ACTIVITY_ATT

    WHERE TRUNC(CREATED) = TRUNC(SYSDATE) - 1;

  DBMS_OUTPUT.PUT_LINE('Activities: ' || V_RESULT);

  

  SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_CONTACT_ATT

    WHERE TRUNC(CREATED) = TRUNC(SYSDATE) - 1;

  DBMS_OUTPUT.PUT_LINE('Contacts: ' || V_RESULT);

  

  SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_SR_ATT

    WHERE TRUNC(CREATED) = TRUNC(SYSDATE) - 1;

    DBMS_OUTPUT.PUT_LINE('Enquiries: ' || V_RESULT);

  

  SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_ACCNT_ATT

    WHERE TRUNC(CREATED) = TRUNC(SYSDATE) - 1;

    DBMS_OUTPUT.PUT_LINE('Organisations: ' || V_RESULT);

  

  SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_OPTY_ATT

    WHERE TRUNC(CREATED) = TRUNC(SYSDATE) - 1;

    DBMS_OUTPUT.PUT_LINE('Processes: ' || V_RESULT);

  

  SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_PROJ_ATT

    WHERE TRUNC(CREATED) = TRUNC(SYSDATE) - 1;

    DBMS_OUTPUT.PUT_LINE('Safeguarding: ' || V_RESULT);

  

END;

/

USING A SELECT STATEMENT AS A TABLE IN ANOTHER SELECT STATEMENT
SELECT DISTINCT EF.EVENT_ID,
      BAL.event_id As BANKING_ENTRY_EVENT_ID
  FROM EVENT_FUND@oes  EF
  INNER JOIN financial_doc_be_line_map@oes  DLM
  ON ef.sett_level_granularity = dlm.sett_level_granularity
  INNEr JOIN (select T1.*, ROW_NUMBER() 
    Over (PARTITION BY T1.financial_document_id, T1.financial_document_line_id ORDER BY creation_date ASC) as rn
     From event_banking_assoc_lines@oes  T1) BAL
  ON BAL.financial_document_id = dlm.bill_id
    AND BAL.financial_document_line_id = dlm.line_id
  WHERE EF.merchant_ledger_acc_type_cd = 'FUND'
  AND EF.invoiceable_ind = 'Y'
  AND bal.rn = 1) BE

Useful Script to find out if an RCR has failed:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

SELECT DISTINCT JOB.ROW_ID AS JOB_ROW_ID,

       --ACTION.NAME, ACTION.DISPLAY_NAME,

       --PARENT_ACTION.NAME, PARENT_ACTION.DISPLAY_NAME,

       --PARAM_NAME.NAME,

       to_char((from_tz(to_timestamp(to_char(JOB.SCHED_START_DT, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'UTC')

at time zone 'Europe/London'),'HH24:MI:SS') AS START_TIME,

  INST.STATUS AS LAST_RI_STATUS,

       to_char((from_tz(to_timestamp(to_char(INST.ACTL_END_DT, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'UTC')

at time zone 'Europe/London'),'DD/MM/YYYY HH24:MI:SS') as LAST_RI_DATE,

       PARAM.VALUE AS WORKFLOW_NAME

FROM SIEBEL.S_SRM_REQUEST JOB, SIEBEL.S_SRM_REQ_PARAM PARAM, SIEBEL.S_SRM_ACT_PARAM PARAM_NAME,

     SIEBEL.S_SRM_ACTION ACTION, SIEBEL.S_SRM_ACTION PARENT_ACTION, SIEBEL.S_SRM_REQUEST INST

WHERE JOB.ROW_ID = PARAM.REQ_ID (+)

AND PARAM.ACTPARAM_ID = PARAM_NAME.ROW_ID (+)

AND PARAM_NAME.NAME = 'Workflow Process Name'

AND JOB.ACTION_ID = ACTION.ROW_ID (+)

AND ACTION.PAR_ACTION_ID = PARENT_ACTION.ROW_ID (+)

AND (PARENT_ACTION.NAME IN ('WfProcBatchMgr', 'WfProcMgr')

    OR ACTION.NAME IN ('WfProcBatchMgr', 'WfProcMgr'))

AND JOB.REQ_TYPE_CD = 'RPT_PARENT'

AND JOB.STATUS = 'ACTIVE'

AND JOB.ROW_ID = INST.PAR_REQ_ID (+)

AND INST.STATUS IN ('SUCCESS', 'ERROR')

AND INST.ACTL_END_DT = (

    SELECT MAX(ACTL_END_DT)

    FROM SIEBEL.S_SRM_REQUEST

    WHERE INST.PAR_REQ_ID = PAR_REQ_ID

    AND STATUS IN ('SUCCESS', 'ERROR'))

AND PARAM.VALUE <> 'CQC Doc Server Blocked'

ORDER BY to_char((from_tz(to_timestamp(to_char(JOB.SCHED_START_DT, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'UTC')

at time zone 'Europe/London'),'HH24:MI:SS');

--ORDER BY PARAM.VALUE ASC;

Response Groups

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

select 

        (CASE 

          WHEN  (A.SR_CAT_TYPE_CD = 'Notifications' AND C.ATTRIB_03 =  'To be Classified' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Notifications/CQC CPI Enquiry Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Registration' AND C.ATTRIB_03 =  'Application' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Applications/CQC Fees Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Registration' AND C.ATTRIB_03 =  'Guidance/Information' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Registrations/CQC HSCA Reg Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Compliance' AND C.ATTRIB_03 =  'Provision of Evidence' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Compliance/CQC HSCA Comp Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Corporate CQC' AND C.ATTRIB_03 =  'To be Classified' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'Enquiries/CQC Enquiry Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Compliance' AND C.ATTRIB_03 =  'To be Classified' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'Safeguarding/CQC Safeguarding Response'

        ELSE 'Unidentified'

        END) AS Mail_Box_Response_Group,

        Count(*)

FROM SIEBEL.S_SRV_REQ A,  

SIEBEL.S_SRV_REQ_X C

WHERE C.PAR_ROW_ID = A.ROW_ID

        AND  A.SR_CAT_TYPE_CD IN ('Notifications','Registration','Compliance','Corporate CQC')

        AND C.ATTRIB_03 IN ('To be Classified','Application','Guidance/Information','Provision of Evidence')

        AND C.ATTRIB_04 = 'To be Classified'

        AND A.SR_SUBTYPE_CD = 'Email'  

        AND A.SR_STAT_ID = 'Open'

        --Change the Date and Time to the appropriate value

        AND A.CREATED >= '01/01/2015 15:00:00'

        GROUP BY  

        (CASE 

          WHEN  (A.SR_CAT_TYPE_CD = 'Notifications' AND C.ATTRIB_03 =  'To be Classified' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Notifications/CQC CPI Enquiry Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Registration' AND C.ATTRIB_03 =  'Application' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Applications/CQC Fees Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Registration' AND C.ATTRIB_03 =  'Guidance/Information' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Registrations/CQC HSCA Reg Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Compliance' AND C.ATTRIB_03 =  'Provision of Evidence' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'HSCA_Compliance/CQC HSCA Comp Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Corporate CQC' AND C.ATTRIB_03 =  'To be Classified' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'Enquiries/CQC Enquiry Response Group'

          WHEN  (A.SR_CAT_TYPE_CD = 'Compliance' AND C.ATTRIB_03 =  'To be Classified' AND C.ATTRIB_04 =  'To be Classified' AND A.SR_SUBTYPE_CD = 'Email')  THEN 'Safeguarding/CQC Safeguarding Response'

        ELSE 'Unidentified'

        END)

Referencing Database Links in SQL Statement to compare data sets between two separate Databases

SELECT

T2.ATTRIB_08      AS ACQUIRER_ATM_TERMINALS ,

T1.NAME           AS ACQUIRER_BIN ,

T2.ATTRIB_16      AS ACQUIRER_BI_ALLOCATION ,

T2.ATTRIB_18      AS ACQUIRER_DELIVERY_ROUTE ,

T1.DESC_TEXT      AS ACQUIRER_DESC ,

T2.ATTRIB_10      AS ACQUIRER_POS_TERMINALS ,

T2.ATTRIB_17      AS ACQUIRER_SCHEME_BUSINESS_ID ,

T2.ATTRIB_09      AS ACQUIRER_UAF_INDICATOR ,

T2.ATTRIB_35      AS COUNTRY_CD ,

T2.ATTRIB_26      AS VALID_FROM ,

T2.ATTRIB_27      AS VALID_TO,

T3.PARTY_UID     AS LCP_PARTY_ID,

T5.PARTY_UID     AS SCHEME_PARTY_ID,

T2.ATTRIB_11      AS ACQUIRER_NNSS_INDICATOR,

T2.ATTRIB_34      AS SCHEME_LIFE_CYCLE_CD

FROM

SIEBEL.S_ORG_EXT@BA2.WORLDPAYTD.LOCAL T1,

SIEBEL.S_PARTY@BA2.WORLDPAYTD.LOCAL T3,

SIEBEL.S_ORG_EXT_X@BA2.WORLDPAYTD.LOCAL T2,

SIEBEL.S_PARTY_REL@BA2.WORLDPAYTD.LOCAL T4,

SIEBEL.S_PARTY@BA2.WORLDPAYTD.LOCAL T5,

SIEBEL.S_USER@BA2.WORLDPAYTD.LOCAL T6, -- Created by User

SIEBEL.S_USER@BA2.WORLDPAYTD.LOCAL T7  -- Last upd by User

WHERE T1.ROW_ID=T2.PAR_ROW_ID

AND   T1.OU_TYPE_CD = 'ACQUIRER_BIN'

AND T1.PAR_OU_ID = T3.ROW_ID

AND T5.ROW_ID = T4.REL_PARTY_ID

AND T4.PARTY_ID = T1.ROW_ID

AND T1.CREATED_BY = T6.ROW_ID

AND T1.LAST_UPD_BY = T7.ROW_ID

AND T4.REL_TYPE_CD = 'ACQ_SCH'

MINUS

SELECT

T2.ATTRIB_08      AS ACQUIRER_ATM_TERMINALS ,

T1.NAME           AS ACQUIRER_BIN ,

T2.ATTRIB_16      AS ACQUIRER_BI_ALLOCATION ,

T2.ATTRIB_18      AS ACQUIRER_DELIVERY_ROUTE ,

T1.DESC_TEXT      AS ACQUIRER_DESC ,

T2.ATTRIB_10      AS ACQUIRER_POS_TERMINALS ,

T2.ATTRIB_17      AS ACQUIRER_SCHEME_BUSINESS_ID ,

T2.ATTRIB_09      AS ACQUIRER_UAF_INDICATOR ,

T2.ATTRIB_35      AS COUNTRY_CD ,

T2.ATTRIB_26      AS VALID_FROM ,

T2.ATTRIB_27      AS VALID_TO,

T3.PARTY_UID     AS LCP_PARTY_ID,

T5.PARTY_UID     AS SCHEME_PARTY_ID,

T2.ATTRIB_11      AS ACQUIRER_NNSS_INDICATOR,

T2.ATTRIB_34      AS SCHEME_LIFE_CYCLE_CD

FROM

SIEBEL.S_ORG_EXT@BA1.WORLDPAYTD.LOCAL T1,

SIEBEL.S_PARTY@BA1.WORLDPAYTD.LOCAL T3,

SIEBEL.S_ORG_EXT_X@BA1.WORLDPAYTD.LOCAL T2,

SIEBEL.S_PARTY_REL@BA1.WORLDPAYTD.LOCAL T4,

SIEBEL.S_PARTY@BA1.WORLDPAYTD.LOCAL T5,

SIEBEL.S_USER@BA1.WORLDPAYTD.LOCAL T6, -- Created by User

SIEBEL.S_USER@BA1.WORLDPAYTD.LOCAL T7  -- Last upd by User

WHERE T1.ROW_ID=T2.PAR_ROW_ID

AND   T1.OU_TYPE_CD = 'ACQUIRER_BIN'

AND T1.PAR_OU_ID = T3.ROW_ID

AND T5.ROW_ID = T4.REL_PARTY_ID

AND T4.PARTY_ID = T1.ROW_ID

AND T1.CREATED_BY = T6.ROW_I

AND T1.LAST_UPD_BY = T7.ROW_ID

AND T4.REL_TYPE_CD = 'ACQ_SCH'

Grabbing values from within XML - Part 1 = The SQL:


  SELECT AD.EVENT_ID,
        AD.EVENT_ADDENDUM_ID,
        NodePath,
        NodeValue
  FROM ADDENDUM_DATA AD,
     xmltable('
          for $i in $tmp/descendant::*
                 return <Element>
                                    <Path>{string-join($i/ancestor-or-self::*/name(), "/")}</Path>
                                    <Value>{$i/text()}</Value>
                              </Element>'
        passing AD.SC_ADDITIONAL_DATA AS "tmp" columns
        NodePath varchar2(255) path '//Path',
        NodeValue varchar2(100) path '//Value'
     )
  WHERE NodeValue is not null

Grabbing values from within XML - Part 2 - the XML

<PaymentPayload>
  <BTID>3665ca0b1aa74406b00b3b9f50cafd48</BTID>
  <WP8583>
    <MTI>1240</MTI>
    <D2>
      <S2>36078700000056</S2>
      <S1>U</S1>
    </D2>
    <D3>
      <S1>00</S1>
    </D3>
    <D4>00000008664</D4>
    <D12>
      <S1>150623</S1>
      <S2>185301</S2>
    </D12>
    <D14>1701</D14>
    <D22>
      <S1>C</S1>
      <S2>1</S2>
      <S3>9</S3>
      <S4>1</S4>
      <S5>0</S5>
      <S6>1</S6>
      <S7>F</S7>
      <S8>1</S8>
      <S9>1</S9>
      <S10>3</S10>
      <S11>4</S11>
      <S12>1</S12>
    </D22>
    <D23>001</D23>
    <D24>200</D24>
    <D37>VISAEU STANDARD PURC2</D37>
    <D38>100018</D38>
    <D39>
      <S2>00</S2>
    </D39>
    <D40>000</D40>
    <D42>10056634</D42>
    <D43>
      <S5>LON</S5>
      <S1>1-69UI</S1>
      <S2>31</S2>
      <S3>London</S3>
      <S4>E1 0HJ</S4>
      <S6>GBR</S6>
    </D43>
    <D48>
      <P1016>230615</P1016>
      <P1017>160211</P1017>
      <P508>VISAEU STANDARD PURC2</P508>
      <P1008>1</P1008>
      <P280>
        <S1>002</S1>
        <S2>150623</S2>
        <S3>00000000001</S3>
        <S4>00001</S4>
        <S5>3601</S5>
      </P280>
      <P1007>003601</P1007>
      <P1009>000001</P1009>
      <P1092>10</P1092>
      <P1232>82345678901234567890123456</P1232>
      <P1233>009</P1233>
      <P596>
        <S1/>
      </P596>
      <P552>01257 232510</P552>
      <P170>
        <S1>01257 232510</S1>
      </P170>
      <P175>www.PO4000173760.co.uk</P175>
      <P688>PO4000173760@gmail.com</P688>
      <P173>1-69UI</P173>
      <P2>DCI</P2>
      <P3>DCI</P3>
      <P158>
        <S1>DCI</S1>
        <S5>160211</S5>
        <S2>4</S2>
        <S3>826001</S3>
      </P158>
      <P1004>Y</P1004>
      <P1005>84200025540</P1005>
      <P1015>PO1400000001</P1015>
      <P595>
        <S2>0</S2>
        <S3>0</S3>
        <S4>0</S4>
        <S5>Y</S5>
        <S6>N</S6>
        <S7>N</S7>
        <S8>N</S8>
      </P595>
      <P1003>DC000</P1003>
    </D48>
    <D49>826</D49>
    <D55>
      <T5F2A>826</T5F2A>
      <T82>5C00</T82>
      <T95>0080000000</T95>
      <T9A>150127</T9A>
      <T9B>9B3B</T9B>
      <T9C>00</T9C>
      <T9F02>00000008664</T9F02>
      <T9F06>A0000000031010</T9F06>
      <T9F07>8C2C</T9F07>
      <T9F09>0083</T9F09>
      <T9F0D>1050000000</T9F0D>
      <T9F0E>E02800D800</T9F0E>
      <T9F0F>E020000000</T9F0F>
      <T9F10>06050A0390000000000000000000000000000000000000000000000000000000</T9F10>
      <T9F1A>250</T9F1A>
      <T9F26>796C82E233BBEB1B</T9F26>
      <T9F27>40</T9F27>
      <T9F33>204010</T9F33>
      <T9F34>420002</T9F34>
      <T9F35>22</T9F35>
      <T9F36>0022</T9F36>
      <T9F37>A1AFCAAF</T9F37>
      <T9F39>31</T9F39>
    </D55>
    <D26>5311</D26>
    <D41>P19-TID1</D41>
    <D20>GBR</D20>
    <D32>303638</D32>
    <D31>
      <S2>303638</S2>
      <S1>1</S1>
      <S4>91710000001</S4>
      <S5/>
    </D31>
    <D50>826</D50>
    <D71>1</D71>
  </WP8583>
</PaymentPayload>

CONVERTING A SINGLE ROW into multiple Columns!!!

I.E A, B, C (in a single row) becomes:

A

B

C

SELECT

  'CARD_DISPUTE_REQUEST' AS TYPE,

  WPNAME                 AS WPNAME,

  WPVAL                  AS WPVAL

FROM

(

  SELECT

    CAST (oes_event.EVENT_ID AS VARCHAR(255))                                               AS NAME,

    NVL (CAST (event_type.VAL AS VARCHAR(255)), ' ')                                        AS TYPE, -- 'Cardholder Name Address Check Event'

    NVL (CAST (oes_event_name_address_check.ASSOC_PAYER_TOKEN AS VARCHAR(255)), ' ')        AS ASSOC_PAYER_TOKEN,

    NVL (CAST (oes_event_name_address_check.SUPPLIER_TOKEN_NAME AS VARCHAR(255)), ' ')      AS SUPPLIER_TOKEN_NAME,

    NVL (CAST (oes_event_name_address_check.ADDRESS_TYPE_ID AS VARCHAR(255)), ' ')          AS ADDRESS_TYPE_ID,

    NVL (CAST (oes_event_name_address_check.ADDRESS_LINE1 AS VARCHAR(255)), ' ')            AS ADDRESS_LINE1,

    NVL (CAST (oes_event_name_address_check.ADDRESS_LINE2 AS VARCHAR(255)), ' ')            AS ADDRESS_LINE2,

    NVL (CAST (oes_event_name_address_check.ADDRESS_LINE3 AS VARCHAR(255)), ' ')            AS ADDRESS_LINE3,

    NVL (CAST (oes_event_name_address_check.ADDRESS_CODE AS VARCHAR(255)), ' ')             AS ADDRESS_CODE,

    NVL (CAST (oes_event_name_address_check.ADDRESS_REGION AS VARCHAR(255)), ' ')           AS ADDRESS_REGION,

    NVL (CAST (oes_event_name_address_check.ADDRESS_COUNTRY_CD AS VARCHAR(255)), ' ')       AS ADDRESS_COUNTRY_CD,

    NVL (CAST (oes_event_name_address_check.INFO_DELIVEY_CHANNEL AS VARCHAR(255)), ' ')     AS INFO_DELIVEY_CHANNEL,

    NVL (CAST (oes_event_name_address_check.ISS_CONTACT_TOKEN AS VARCHAR(255)), ' ')        AS ISS_CONTACT_TOKEN,

    NVL (CAST (oes_event_name_address_check.PAYER_TOKEN_CHK_ISS_REF AS VARCHAR(255)), ' ')  AS PAYER_TOKEN_CHK_ISS_REF,

    NVL (CAST (oes_event_name_address_check.NAME_ADDRESS_CHK_OUTCOME AS VARCHAR(255)), ' ') AS NAME_ADDRESS_CHK_OUTCOME,

    NVL (CAST (oes_event_name_address_check.ASSOC_PAYER_TOKEN_ISS_NO AS VARCHAR(255)), ' ') AS ASSOC_PAYER_TOKEN_ISS_NO,

    NVL (CAST (oes_event_name_address_check.ASSOC_PAYER_TOKEN_EXPIRY AS VARCHAR(255)), ' ') AS ASSOC_PAYER_TOKEN_EXPIRY,

    NVL (CAST (oes_event_name_address_check.CREATION_DATE AS VARCHAR(255)), ' ')            AS CREATION_DATE,

    NVL (CAST (oes_event_name_address_check.CREATED_BY  AS VARCHAR(255)), ' ')              AS CREATED_BY

FROM

             S_LST_OF_VAL                 event_type

  INNER JOIN EVENT@oes                    oes_event                    ON event_type.NAME    = CAST(oes_event.EVENT_TYPE_ID AS VARCHAR2(50))

  LEFT OUTER  JOIN EVENT_NAME_ADDRESS_CHECK@oes oes_event_name_address_check ON oes_event.EVENT_ID = oes_event_name_address_check.EVENT_ID

WHERE

      event_type.TYPE = 'WP_EVENT_TYPE'

--  AND event_type.NAME = '30'

  AND oes_event.EVENT_ID = 1004864

) temp

UNPIVOT (

  WPVAL FOR WPNAME IN

  (

    NAME,

    TYPE,

    ASSOC_PAYER_TOKEN,

    SUPPLIER_TOKEN_NAME,

    ADDRESS_TYPE_ID,

    ADDRESS_LINE1,

    ADDRESS_LINE2,

    ADDRESS_LINE3,

    ADDRESS_CODE,

    ADDRESS_REGION,

    ADDRESS_COUNTRY_CD,

    INFO_DELIVEY_CHANNEL,

    ISS_CONTACT_TOKEN,

    PAYER_TOKEN_CHK_ISS_REF,

    NAME_ADDRESS_CHK_OUTCOME,

    ASSOC_PAYER_TOKEN_ISS_NO,

    ASSOC_PAYER_TOKEN_EXPIRY,

    CREATION_DATE,

    CREATED_BY

  )

)