Mass Data Updates in CC&B using PL/SQL & XAI services

CC&B Business users enjoy sending requests to IT department regarding mass updates. Mass volume is  something relative as it could be hundreds or could be millions of records. Recently I was requested to update around 3000 Service Agreements from "Active" Status to "Pending Stop" status as this number of records will take weeks from the Customer Service department to accomplish.

I had several options on the table:

Solution 1 (Quick Solution):
Directly update from the Database !!! This is the danger Zone as contractually with the vendor, if something goes wrong, we are toasted, NO SUPPORT will be provided.

Solution 2 (Not worth the effort):
Create a CC&B Java batch job to do it that will probably read from a flat file or a staging table and perform the job. This will take considerable effort and as its a one time job, it looks like its not worth it.

Solution 3 (The winning Card):
Write a small PL/SQL script that calls XAI service of "Start Stop" page. This a fast method to perform a one-time job such as this example.

Demo
In this post I will demonstrate how to loop for SA records and invoke the XAI directly from the Oracle Database using an anonymous PL/SQL block.

Assumption (No details will be provided on the basics of the below topics):
-You have the XAI service setup and running ( here I named the XAI service "CM_StartStopPageMaintenance")
-You know how the XML SOAP message looks like for the XAI service as it will be constructed in the PL/SQL script.
-You have basic PL/SQL knowledge, including the UTL_HTTP Oracle supplied package.


The following script will loop for all requires SA's and construct an XML message for each SA and invoke the XAI service to stop the SA

-------------------
-------------------


DECLARE
   v_xml_message       VARCHAR2 (4000);
   v_acct_per_rec      ci_acct_per%ROWTYPE;
   v_acct_rec          ci_acct%ROWTYPE;
   vc_url              VARCHAR2 (12)         := '10.1.1.2';  --IP Address of the XAI server 
   ip_port             VARCHAR2 (12)         := '6500'; --Port of the CCB webserver 
   xai_directory       VARCHAR2 (100)        := 'XAIApp/xaiserver';
   auth_id             VARCHAR2 (100)        := 'SYSUSER'; --User name to log into the XAI server
   auth_pswd           VARCHAR2 (100)        := 'password01'; --Password
   http_req            UTL_HTTP.req;
   http_resp           UTL_HTTP.resp;
   response_env_clob   CLOB;
BEGIN
   FOR ri IN (SELECT *
                FROM ci_sa
               WHERE sa_status_flg = '20'
                 AND (sa_id IN
                         ('0000000968', '0000000059', '0000000260',
                          '0000000722', '0000000492', '0000000286',
                          '0000000302', '0000000321', '0000000083')
                     ))  --to keep it simple I put only few dummy SA IDs 
   LOOP
      SELECT *
        INTO v_acct_per_rec
        FROM ci_acct_per
       WHERE acct_id = ri.acct_id AND main_cust_sw = 'Y';


      SELECT *
        INTO v_acct_rec
        FROM ci_acct
       WHERE acct_id = ri.acct_id;
--Construct the XML message that will passed to the XAI service from the current SA record values 
      v_xml_message :=
            '<SOAP-ENV:Envelope xmlns:SOAP-ENV="urn:schemas-xmlsoap-org:envelope">
  <SOAP-ENV:Body>
    <CM_StartStopPageMaintenance transactionType="CHNG">
      <CM_StartStopPageMaintenanceService>
        <CM_StartStopPageMaintenanceDetails AccountID="'
         || ri.acct_id
         || '" CurrencyCode="AED" StartDate="'
         || TO_CHAR (SYSDATE, 'yyyy-mm-dd')
         || '" CodeTotalToBillAmount="0" STOPENDDT="'
         || TO_CHAR (SYSDATE, 'yyyy-mm-dd')
         || '" StopMethod="SA" StopServiceagreementID="'
         || ri.sa_id
         || '" ACTIONSTARTSW="false" ACTIONSTOPSW="true" BUILDSTARTSASW="false" StartPremiseSPs="false" STARTSELECTSASW="false" ERRORCREATESASW="false" CREATESTARTFASW="true" CREATESTOPFASW="true" StopSelected="false" StopSelectedSA="false" ChangeStartStopDate="false" Reject="false" ScheduledFromDate="'
         || TO_CHAR (SYSDATE, 'yyyy-mm-dd')
         || '" ScheduledToDate="'
         || TO_CHAR (SYSDATE, 'yyyy-mm-dd')
         || '" StartStopDate="'
         || TO_CHAR (SYSDATE, 'yyyy-mm-dd')
         || '" AccountID2="'
         || ri.acct_id
         || '" Address12="0" AddressLine4="" BillAddressSource="PER" BillRouteType="OUTSRC" Country="UAE" Version="" MainCustomerPersonID="'
         || v_acct_per_rec.per_id
         || '" BillAddressSrceFlg2="PER" BillRoutingMethod="P" MailingPremiseID="'
         || v_acct_rec.mailing_prem_id
         || '" MessageCategory="0" MessageNumber="0" PhoneCollFrDflt="Y" Country2="UAE" Address1Available="true" Address2Available="true" Address3Available="true" Address4Available="true" CityAvailable="true" Version2="" Number1Available="false" HouseTypeAvailable="false" CountyAvailable="true" StateAvailable="true" PostalAvailable="true" GeoCodeAvailable="false" Language="ENG" County="Abu Dhabi" State="AD" InCityLimit="false" CollTypeFlag="D" StartRecCnt="0" StopRecCnt="0" StopDate="'
         || TO_CHAR (SYSDATE, 'yyyy-mm-dd')
         || '" Attrribute="Value">
          <PendingStartStop />
        </CM_StartStopPageMaintenanceDetails>
      </CM_StartStopPageMaintenanceService>
    </CM_StartStopPageMaintenance>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
      UTL_HTTP.set_response_error_check (FALSE);
      UTL_HTTP.set_transfer_timeout (60);
      http_req :=
         UTL_HTTP.begin_request (   'http://'
                                 || vc_url
                                 || ':'
                                 || ip_port
                                 || '/'
                                 || xai_directory,
                                 'POST',
                                 UTL_HTTP.http_version_1_1
                                );
      UTL_HTTP.set_authentication (http_req, auth_id, auth_pswd);
      UTL_HTTP.set_header (http_req, 'Content-Type',
                           'text/xml; charset=utf-8');
      UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (v_xml_message));
      UTL_HTTP.write_text (http_req, v_xml_message);
      http_resp := UTL_HTTP.get_response (http_req);


      DECLARE
         l_buffer_size   NUMBER (10) := 512;
         response_env    BLOB;
      BEGIN
         LOOP
            UTL_HTTP.read_raw (http_resp, response_env, l_buffer_size);
            response_env_clob :=
                 response_env_clob || UTL_RAW.cast_to_varchar2 (response_env);
         END LOOP;
      EXCEPTION
         WHEN UTL_HTTP.end_of_body
         THEN
            UTL_HTTP.end_response (http_resp);
      END;
   END LOOP;
END;



-------------------
-------------------


Note: I tested this script and all SA's were closed successfully on test environment, but in other more complicated cases, I would add exception handling and log the failed calls to study how to solve them.

Your comments are welcomed to enhance this post so that the benefit can be spread around ;)

DISCLAIMER:  This information is provided "as is". The author of this information disclaim any loss or liability, 
either directly or indirectly as a consequence of applying the information presented herein, or in regard to the use
 and application of said information. No guarantee is given, either expressed or implied, in regard to the
 merchantability, accuracy, or acceptability of the information

Publish SQL query results as an XAI service

Introduction

Publishing SQL query results as an XAI service enables external applications to retrieve data from CC&B using XML documents (XML request & XML response). This facility enables CC&B administrators to tailor data and tune SQL performance to its best instead of having to use existing business services or business objects as it’s provided out of the box.

In this example, I will demonstrate creating a CC&B Query Zone (based on a robust SQL query that retrieves payment data based on input parameters) , Business Service (to define XML structure of the input/output XML documents), and an XAI inbound service that publishes the SQL query results. So to fully understand this example, you need fair understanding of these CC&B tools in addition to XML, and service oriented architecture (SOA).

Steps are summarized as follows:

1.     Create an Applications service (this will be required when creating the query Zone)

2.     Create a Query Zone based on an SQL query that retrieved payment data based on tender source, from data, and to data input parameters.

3.     Create a Business Service (BS) and define the XML Schema that maps the Query zone results an SQL document.

4.     Create an XAI inbound service to publish the results as a service.

Detailed Steps

1- In Application Services screen (Application Service +), enter the following data:

Application service : CM_PMNT_INQ

Description : Payment Inquiry

Access Mode: Inquire  

(Save)

2-      In Zone screen (Zone +), enter the following data:

Zone: CM_PMNT_INQ

Description : Payment Inquiry used for XAI service

Zone Type: F1-DE-QUERY

Application Service : CM_PMNT_INQ

Width : Full

Zone Help Text:

 <HTML>

<BODY>

This zone will retrieve payment data for a certain payment tender source.

</BODY>

</HTML>

Zone Parameters (you can check the parameter usage in the help field next to each parameter):

Parameter Name	Value
User Filter 1	type=STRING label='Tender Source Code'
User Filter 2	type=STRING label='From Date'
User Filter 3	type=STRING label=To Date'
SQL 1 Condition	F1 F2 F3
SQL Statement 1	SELECT DISTINCT p.acct_id accountid,   (SELECT a.acct_id ||' - '|| b.entity_name || ' - ' || c.descr    FROM ci_acct_per a, ci_per_name b, ci_cust_cl_l c , ci_acct ac   WHERE a.acct_id = p.acct_id   and ac.acct_id=a.acct_id   AND a.per_id = b.per_id   and c.language_cd='ENG'   and AC.cust_cl_cd = c.cust_cl_cd   AND a.main_cust_sw = 'Y'   AND b.PRIM_NAME_SW = 'Y') accountinfo,   t.micr_id micrid, pe.pay_event_id paymenteventid,   p.pay_status_flg paymentstatus,   lk.descr paymentstatusdescription,   tsl.descr tendersrcdescription,   tc.tndr_source_cd tendersource, pe.pay_dt paymentdate,   p.pay_amt paymentamount, p.pay_id  FROM ci_pay_event pe,   ci_pay p,   ci_acct_per ap,   ci_per_name pn,   ci_lookup lk,   ci_pay_tndr t,   ci_acct_per ap1,   ci_per_name pn1,   ci_lookup lk1,   ci_tndr_ctl tc,   ci_tndr_srce_l tsl   WHERE pe.pay_event_id = p.pay_event_id   AND lk.field_name = 'PAY_STATUS_FLG'   AND lk.field_value = p.pay_status_flg   AND lk.language_cd = 'ENG'   AND pe.pay_event_id = t.pay_event_id   AND lk1.field_name = 'TNDR_STATUS_FLG'   AND lk1.field_value = t.tndr_status_flg   AND lk1.language_cd = 'ENG'   AND t.tndr_ctl_id = tc.tndr_ctl_id   AND tc.tndr_source_cd = :F1   AND tc.tndr_source_cd = tsl.tndr_source_cd   AND tsl.language_cd = 'ENG'   AND pe.pay_dt BETWEEN to_date(:F2, 'yyyy-mm-dd') AND to_date(:F3||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')   AND p.acct_id = ap.acct_id   AND ap.per_id = pn.per_id   AND ap.main_cust_sw = 'Y'   AND pn.prim_name_sw = 'Y'   AND t.payor_acct_id = ap1.acct_id   AND ap1.per_id = pn1.per_id   AND ap1.main_cust_sw = 'Y'   AND pn1.prim_name_sw = 'Y'  ORDER BY pe.pay_dt DESC, p.pay_id
Column 1 for SQL 1	source=SQLCOL sqlcol=ACCOUNTID label='accountid'
Column 2 for SQL 1	source=SQLCOL sqlcol=ACCOUNTINFO label='accountinfo'
Column 3 for SQL 1	source=SQLCOL sqlcol=MICRID label='micrid'
Column 4 for SQL 1	source=SQLCOL sqlcol=PAYMENTEVENTID label='paymenteventid'
Column 5 for SQL 1	source=SQLCOL sqlcol=PAYMENTSTATUS label='paymentstatus'
Column 6 for SQL 1	source=SQLCOL sqlcol=PAYMENTSTATUSDESCRIPTION   label='paymentstatusdescription'
Column 7 for SQL 1	source=SQLCOL sqlcol=TENDERSRCDESCRIPTION label='tendersrcdescription'
Column 8 for SQL 1	source=SQLCOL sqlcol=TENDERSOURCE label='tendersource'
Column 9 for SQL 1	source=SQLCOL sqlcol=PAYMENTDATE label='paymentdate'
Column 10 for SQL 1	source=SQLCOL sqlcol=PAYMENTAMOUNT label='paymentamount'
Display Columns for SQL 1	C1 C2 C3 C4 C5 C6 C7 C8 C9 C10

3-      In Business Service screen (Business Service +), enter the following data:

Business Service: CM_PMNT_INQ

Description: Payment Inquiry Business Service

Detailed Description: Payment Inquiry Business Service used for Payment search through XAI service (Enter something more comprehensive)

Service Name: FWLZDEXP

Schema:

<schema>

    <zoneCd mapField="ZONE_CD" default="CM_PMNT_INQ"/> 

    <TenderSource mapField="F1_VALUE"/> 

    <FromDate mapField="F2_VALUE"/> 

    <ToDate mapField="F3_VALUE"/> 

    <rowCount mapField="ROW_CNT"/> 

    <result type="group">

        <selectList type="list" mapList="DE">

            <AccountID mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="1"/>

                </row>

            </AccountID> 

            <AccountInfo mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="2"/>

                </row>

            </AccountInfo> 

            <MICRID mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="3"/>

                </row>

            </MICRID> 

            <PaymentEventID mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="4"/>

                </row>

            </PaymentEventID> 

            <PaymentStatus mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="5"/>

                </row>

            </PaymentStatus> 

            <PaymentStatusDescription mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="6"/>

                </row>

            </PaymentStatusDescription> 

            <TenderSrcDescription mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="7"/>

                </row>

            </TenderSrcDescription> 

            <TenderSource mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="8"/>

                </row>

            </TenderSource> 

            <PaymentDate mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="9"/>

                </row>

            </PaymentDate> 

            <PaymentAmount mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="10"/>

                </row>

            </PaymentAmount>

        </selectList>

    </result>

</schema>

Note that in the schema we are mapping 3 XML elements as input parameters, for instance we are mapping element TenderSource to input parameter F1 that appears in the SQL query as a bind variable:

    <TenderSource mapField="F1_VALUE"/> 

The 10 result column in the Zone are also mapped to XML elements. Ex element AccountID is mapped to the first column (C1) in the query zone and this is specified by using the position of the column (1) :

            <AccountID mapField="COL_VALUE">

                <row mapList="DE_VAL">

                    <SEQNO is="1"/>

                </row>

            </AccountID> 

4-      In XAI Inbound Service (XAI Inbound Service +), enter the following data:

XAI In Service Name	CM_PMNT_INQ
Adapter	BusinessAdaptor
Schema Type	BusinessService
Schema Name	CM_PMNT_INQ
Description	payment inquiry XAI service
Long Description	payment inquiry XAI service
Active ?	Y
Transaction Type	Read

You will need to refresh the service CM_PMNT_INQ, then refresh the registry, and probably the Schemas using the XAI command screen.

Now you can test your new XAI service using the XAI submission screen (or any external client that can submit and retrieve XML request) using the following XML message after replacing the tender source, from date, and to date values with applicable values:

<SOAP-ENV:Envelope xmlns:SOAP-ENV="urn:schemas-xmlsoap-org:envelope">

  <SOAP-ENV:Body>

    <CM_PMNT_INQ transactionType="READ" searchType="">

      <TenderSource>??????</TenderSource>

      <FromDate>2011-01-01</FromDate>

      <ToDate>2011-01-10</ToDate>

    </CM_PMNT_INQ>

  </SOAP-ENV:Body>

</SOAP-ENV:Envelope>

Enjoy Publishing Data from CC&B as required by external applications !

DISCLAIMER:  This information is provided "as is". The author of this information disclaim any loss or liability, 
either directly or indirectly as a consequence of applying the information presented herein, or in regard to the use
 and application of said information. No guarantee is given, either expressed or implied, in regard to the
 merchantability, accuracy, or acceptability of the information