Release Notes: Delivery Receipt Performance Enhancements

CR 1642

Updated 2/8/99

Problem:

The current applications can take upwards of 10 minutes after a document is delivered to deliver the Delivery Receipt to the end user. Currently time is possibly lost between the Network and BO communication timing as well as amongst the many steps taken to send a DR on the BO.

LongTerm Resolution:

This problem will be resolved as part of the CSS project.

Temporary Resolution:

The TEMPORARY resolution is to three fold.

The first portion is to utilize the events database versus Uxserver/historical_envelope. The Network should notify the events database of delivery of a document within SECONDS of actual delivery. (versus 1 to 5 minutes of notification via UXServer).

The second portion is to eliminate a couple of steps on the BO. Current steps are:

  1. Post-insert on historical_envelope to create ar_schedule record
  2. Schedule_ar process moves from ar_schedule to fi_envelope
  3. Ar_submit reads fi_envelope and submits request.

This solution changes the steps to :

  1. Post-update on envelope creates an fi_envelope record (if one does not already exist).
  2. Ar_submit reads fi_envelope and submits request.

The third portion of modifications is to the existing pre-insert trigger on historical_envelope. It has been changed to FIRST check for a matching record (document number /ar category) in the fi_envelope table PRIOR to creating an ar_schedule record. If a match is found no records are inserted into ar_schedule. This ensures if HSETS is down for maintenance, problems, …., DR's will still be sent to customers.

Summary of Software Changes

New Software

Current Business Office Database:

Sp_delivery_ar New procedure that inserts a DR request directly into the fi_envelope table.

Sp_deliver_ar (document_number, fax_number, cust_number, Subject, originator_address, gmt_delivery_datetime, Product_id, initial_address, number pages, acceptance date and time)
faxprocedure.grant grant execute on sp_delivery_ar to public
newsyn.sql create public synonym sp_delivery_ar for faxprocedure.sp_delivery_ar;

Envelope Repository Database:

Envelope_post_upd  

Post-update trigger on envelope table that calls sp_deliver_ar if Current_status_cd = 'DELVRD';

If :new.current_status_cd = 'DELVRD' then

Sp_delivery_ar (:new.document_number,

:new.delivery_address,
:new.cust_number,
:new.Subject,
:new.originator_address,
:new.current_status_date
:new.product_id,
:new.initial_dest_address,
:new.number_pages,
:new.first_event_date);

end if;

 

Modified Software

Hist_env_postins_destsum Modify to check if record exists in fi_envelope for document/category prior to creating an ar_schedule record.
Sf_ar_submit_email.sql Modify to retrieve DR specific information from fi_envelope is a record can not be found in historical_envelope.

Implementation Plan

  1. Stop existing processes
    1. Stop DES Rules (on NT Box)
    2. Stop DEFER Rules (on NT Box)
      (can not terminate documents)
    3. Stop "C" programs for DES
      1. termdoc
      2. termcred
      3. submitfi
      4. submitdi
      5. submitprm
      6. deferdoc
      7. transferda
    4. Stop UXServer
    5. Stop HSETS
    6. Any other processes that place documents into historical_envelope table
    7. AR Processing
  2. Save existing software to holding area
    1. hist_env_postins_destsum (trigger)
    2. sf_ar_submit_email (function) - each LOCAL database
  3. Implement new / modified software
    1. re-load hist_env_postins_destsum.trg
    2. grant priv's to the UES & UES_ADMIN users on BO
      1. select, insert on FI_ENVELOPE
      2. select, insert on FI_ENVELOPE_MANUAL_AR
      3. select, insert on DESTINATION_FAX
      4. select, insert on DOCUMENT_ERR
      5. select on CUSTOMER
      6. select on CUSTOMER_PRODUCT
    3. as faxprocedure load
      1. sp_delivery_ar.sql
      2. faxprocedure.priv
    4. as "system" load
      1. newsyn.sql
  4. Local DataBase impacts
    1. save sf_ar_submit_email to a holding area
    2. load sf_ar_submit_email.sql
  5.  Implement new software on Envelope Repository database
    1. envelope_post_upd.trg
  6. Start existing processes
    1. Start DES Rules (on NT Box)
    2. Start DEFER Rules (on NT Box)
    3. Start "C" programs for DES
      1. termdoc
      2. termcred
      3. submitfi
      4. submitdi
      5. submitprm
      6. deferdoc
      7. transferda
    4. Start UXServer
    5. Start HSETS
    6. Start any other processes that place documents into historical_envelope table
    7. Start all AR Processing

Rollback Plan

  1. Stop existing processes
    1. Stop DES Rules (on NT Box)
    2. Stop DEFER Rules (on NT Box)
      (can not terminate documents)
    3. Stop "C" programs for DES
      1. termdoc
      2. termcred
      3. submitfi
      4. submitdi
      5. submitprm
      6. deferdoc
      7. transferda
    4. Stop UXServer
    5. Stop HSETS
    6. Any other processes that place documents into historical_envelope table
    7. Stop AR Processing
  2. Replace modified software
    1. re-load hist_env_postins_destsum.trg for HOLDING area
    2. re-load sf_ar_submit_email.sql from HOLDING area - EACH LOCAL DATABASE
    3. drop priv's from the UES & UES_ADMIN users on BO
      1. select, insert on FI_ENVELOPE
      2. select, insert on FI_ENVELOPE_MANUAL_AR
      3. select, insert on DESTINATION_FAX
      4. select, insert on DOCUMENT_ERR
      5. select on CUSTOMER
      6. select on CUSTOMER_PRODUCT
    4. as faxprocedure DROP
      1. sp_delivery_ar procedure
  3. Drop new trigger from Envelope Repository database
    1. envelope_post_upd
  4. Start existing processes
    1. Start DES Rules (on NT Box)
    2. Start DEFER Rules (on NT Box)
    3. Start "C" programs for DES
      1. termdoc
      2. termcred
      3. submitfi
      4. submitdi
      5. submitprm
      6. deferdoc
      7. transferda
    4. Start UXServer
    5. Start HSETS
    6. Start any other processes that place documents into historical_envelope table
    7. Start all AR Processing