際際滷

際際滷Share a Scribd company logo

RSSP Franchise Service Plan Automation

Download as PPTX, PDF
J
Jon Fortman

This document outlines a proposal to automate the franchise service plan transactions currently processed through the 010118 administrative unit. The proposal would change how transactions are loaded to the DB2 tables to include identifiers linking each transaction to the original franchise store transaction. This is intended to improve customer support, make it easier for corporate staff to locate franchise transactions, and improve Assurant reconciliation of transactions, especially refunds. It would also reduce manual labor.

1 of 13
Download to read offline
RSSP Franchise Service Plan Automation [sales/refunds leg - 010118] Broad Design Overview
The premise of this project is to automate what we have todaynot to redesign it from the ground up. BROAD FACTS
In 1991, the 010118 admin DR was not designated to be a container/conduit for franchise svp transactions exclusively. BROAD FACTS Going forward, ~1-5% of this DR will contain non-franchise transactions on a daily basis and they will be present in journals and DB2 just as they are today. 010118 does contain only service plan transactions; there are no tangibles transacted through this unit.
Currently, no USER_NBR = 22 exists on the DB2 transactional tables. BROAD FACTS
010118 admin unit DR container FRAN STR sells service plan BIZTALK / SQL SERVER .XLSX FROM SQL SERVER VSAM 010118 admin unit DR BATCH PROCESSING Journaling all done under 010118 010118 DR DB2 load process CURRENT DAY
DB2 010118 DR DB2 load process CURRENT DAY SALE_TKT_SVP SVP_ITEM SALE_TKT SVP_SLTKT_INFO Primary Key: USER_NBR =01 STR_ID=0118 SLTKT_NBR = tkt# of 010118 SLTKT_DT = date of 010118 tkt SLTKT_TM = time of 010118 tkt SLTKT_LINSEQ_NBR = that of 010118 tkt SLTKT_SVPSEQ_NBR = 1 FRAN_USER_NBR = 22 FRAN_STR_ID = A105 FRAN_SLTKT_DT = that of fran str trans FRAN_SLTKT_NBR = that of fran str trans
Assurant customer support hindered because the customer has no knowledge of the 010118 transaction Locating franchise transactions difficult for corporate staff Assurant reconciliation hindered especially with refunds BROAD ISSUES
010118 admin unit DR container FRAN STR sells service plan. POS changes needed for addnl data BIZTALK / SQL SERVER addnl data needed VSAM 010118 admin unit DR BATCH PROCESSING Journaling done at FRAN STR level 010118 DR DB2 load process. Change PK load and SVP_SLTKT_INFO PROPOSED ETL + new and leveraged existing batch programs
DB2 010118 DR DB2 load process SALE_TKT_SVP SVP_ITEM SALE_TKT SVP_SLTKT_INFO Primary Key: USER_NBR =22 STR_ID=A105 SLTKT_NBR = tkt# of fran str* SLTKT_DT = date of fran str tkt SLTKT_TM = time of fran str tkt SLTKT_LINSEQ_NBR = that of fran str tkt SLTKT_SVPSEQ_NBR = 1 FRAN_USER_NBR = 01 FRAN_STR_ID = 0118 FRAN_SLTKT_DT = that of 010118 trans FRAN_SLTKT_NBR = that of 010118 trans PROPOSED *len=10. Only right most 6 digits would be loaded
Customer support improved because the customer, Assurant, and Radioshack can refer to transactions with common information Eases difficulty in locating franchise transactions for corporate staff [allows direct lookup in ANSO, CICS Lookup] Assurant reconciliation improved especially with refunds BROAD BENEFITS
Reduces manual labor by agents BROAD BENEFITS
Several areas/processes make hardcoded checks/filtering such as STR_ID = 0118. BROAD FACTS SALE_TKT_SVP SVP_ITEM SALE_TKT SVP_SLTKT_INFO Primary Key: USER_NBR =22 STR_ID=A105 SLTKT_NBR = tkt# of fran str SLTKT_DT = date of fran str tkt SLTKT_TM = time of fran str tkt SLTKT_LINSEQ_NBR = that of fran str tkt SLTKT_SVPSEQ_NBR = 1 FRAN_USER_NBR = 01 FRAN_STR_ID = 0118 FRAN_SLTKT_DT = that of 010118 trans FRAN_SLTKT_NBR = that of 010118 trans In the new fran svp world, this would still be a valid check. The only difference would be not what is checked but where.
BROAD FACTS The previous statement does NOT apply to 010118 DR processing other than journals.

More Related Content

RSSP Franchise Service Plan Automation

  • 1. RSSP Franchise Service Plan Automation [sales/refunds leg - 010118] Broad Design Overview
  • 2. The premise of this project is to automate what we have todaynot to redesign it from the ground up. BROAD FACTS
  • 3. In 1991, the 010118 admin DR was not designated to be a container/conduit for franchise svp transactions exclusively. BROAD FACTS Going forward, ~1-5% of this DR will contain non-franchise transactions on a daily basis and they will be present in journals and DB2 just as they are today. 010118 does contain only service plan transactions; there are no tangibles transacted through this unit.
  • 4. Currently, no USER_NBR = 22 exists on the DB2 transactional tables. BROAD FACTS
  • 5. 010118 admin unit DR container FRAN STR sells service plan BIZTALK / SQL SERVER .XLSX FROM SQL SERVER VSAM 010118 admin unit DR BATCH PROCESSING Journaling all done under 010118 010118 DR DB2 load process CURRENT DAY
  • 6. DB2 010118 DR DB2 load process CURRENT DAY SALE_TKT_SVP SVP_ITEM SALE_TKT SVP_SLTKT_INFO Primary Key: USER_NBR =01 STR_ID=0118 SLTKT_NBR = tkt# of 010118 SLTKT_DT = date of 010118 tkt SLTKT_TM = time of 010118 tkt SLTKT_LINSEQ_NBR = that of 010118 tkt SLTKT_SVPSEQ_NBR = 1 FRAN_USER_NBR = 22 FRAN_STR_ID = A105 FRAN_SLTKT_DT = that of fran str trans FRAN_SLTKT_NBR = that of fran str trans
  • 7. Assurant customer support hindered because the customer has no knowledge of the 010118 transaction Locating franchise transactions difficult for corporate staff Assurant reconciliation hindered especially with refunds BROAD ISSUES
  • 8. 010118 admin unit DR container FRAN STR sells service plan. POS changes needed for addnl data BIZTALK / SQL SERVER addnl data needed VSAM 010118 admin unit DR BATCH PROCESSING Journaling done at FRAN STR level 010118 DR DB2 load process. Change PK load and SVP_SLTKT_INFO PROPOSED ETL + new and leveraged existing batch programs
  • 9. DB2 010118 DR DB2 load process SALE_TKT_SVP SVP_ITEM SALE_TKT SVP_SLTKT_INFO Primary Key: USER_NBR =22 STR_ID=A105 SLTKT_NBR = tkt# of fran str* SLTKT_DT = date of fran str tkt SLTKT_TM = time of fran str tkt SLTKT_LINSEQ_NBR = that of fran str tkt SLTKT_SVPSEQ_NBR = 1 FRAN_USER_NBR = 01 FRAN_STR_ID = 0118 FRAN_SLTKT_DT = that of 010118 trans FRAN_SLTKT_NBR = that of 010118 trans PROPOSED *len=10. Only right most 6 digits would be loaded
  • 10. Customer support improved because the customer, Assurant, and Radioshack can refer to transactions with common information Eases difficulty in locating franchise transactions for corporate staff [allows direct lookup in ANSO, CICS Lookup] Assurant reconciliation improved especially with refunds BROAD BENEFITS
  • 11. Reduces manual labor by agents BROAD BENEFITS
  • 12. Several areas/processes make hardcoded checks/filtering such as STR_ID = 0118. BROAD FACTS SALE_TKT_SVP SVP_ITEM SALE_TKT SVP_SLTKT_INFO Primary Key: USER_NBR =22 STR_ID=A105 SLTKT_NBR = tkt# of fran str SLTKT_DT = date of fran str tkt SLTKT_TM = time of fran str tkt SLTKT_LINSEQ_NBR = that of fran str tkt SLTKT_SVPSEQ_NBR = 1 FRAN_USER_NBR = 01 FRAN_STR_ID = 0118 FRAN_SLTKT_DT = that of 010118 trans FRAN_SLTKT_NBR = that of 010118 trans In the new fran svp world, this would still be a valid check. The only difference would be not what is checked but where.
  • 13. BROAD FACTS The previous statement does NOT apply to 010118 DR processing other than journals.