recharge_report_automation
TRANSCRIPT
Automation of Recharge Report Sumarry
Presenter– Aman RajMentor - Mr.
Prasant Kumar
Date - 05/06/2015
Telecom 4.1 AssessmentOSS/BSS Competency
Case Study - Plan
Case Study TopicsMIS Information – Brief IntroductionExisting process for MIS reporting and availabilityLimitations of existing processProposed steps to enhance Information availability on MIS ReportingBenefits of the new report creationSteps to achieve the new ProcessRecommendationsGlossary
Case Study Topics
Following are the topics that are proposed for the case study that aimed at optimizing the existing process critical information availability to take action MIS Reporting
A. Delivery of Dump based information – To provide a dump on a specified frequency with the business critical information of users business actions
B. To provide an automated information Report – To develop an automate report generation system by which hourly Recharge Reporting Sumarry could be delivered to business for market driving and to enhance the Revenue.
C. To create a separate report file in .XLS format for business so that details could be analyzed – To develop an report which helps business/End user for hourly online report Sumarry so that they can access that in one click and save the time.
After discussion with my mentor we selected to go with the 2nd topic To provide an automated information Report for assessment.
Automated MIS Recharge Report Sumarry – Brief Introduction
The Recharge report which is back bone of any Telecom Operators which generate the
revenue and lead to net profit.
Presently, business/End users prepare information's of the customer’s recharge details using
MIS Reports which takes a long time and multiple process.
The new enhanced system helps in squeezing the three keys steps of information gathering, processing and display all on a Sumarry Report.
This project is an FTP based report which involves requirement gathering, downloading the online data from database, creating the file in
constant name for insertion in database , generation of report in .xls and to upload at FTP
for users , The system is developed using the PL/SQL (Oracle) and Batch Scripting.
This system arms the Business with quick decision making capabilities and helps in quickly building a strategy to retaining the existing customers.
Existing steps for MIS Recharge Report information analysis
The users goes through various MIS reports and manually consolidates the information with painstaking effort.
To Process the information regarding recharge
Based on the information available the business criticaldecisions are made.
Limitations of existing process
Manual Activity is cumbersome for the users
For big circles, it takes lot of time to even download and later process the voluminous data
Manipulating the data using Excel functions becomes very tedious
By the time data is converted to information for business decisions, there is no time for strategic business action to retain clients.
Proposed steps to for Information
Data
Steps to achieve the MIS Recharge Report Automation
Through FTP Server data uploaded into Local DB
Processed data through Batch Script and Sql
Store the login credentials in tables for accessing the web application so that any addition or changes in future can be accommodated .
Estimated efforts:72 hrs. ( Analysis, Designing, Coding , UAT, Production)
Language: PL/SQL , Batch Scripting
Software : Oracle 10g, GUNZIP, DOS
User restricted access to the business critical data.
Data availability on the click of a button for the taking further course of action Report Sumarry through FTP.
Eliminates the manual processing effort .
Very user friendly and easy to use which requires no technical knowledge to know the subscriber data.
Saves tons of precious time which is saved by automated process of data handling through backend.
Benefits of the new process
Recommendations
Based on the success of the proposed solution the same can be extended to other regions which have high count of Recharge .
Coding to download the data from FTP and to rename in constant Name
@echo off
set yyyy=
set $tok=1-3
for /f "tokens=1 delims=.:/-, " %%u in ('date /t') do set $d1=%%u
if "%$d1:~0,1%" GTR "9" set $tok=2-4
for /f "tokens=%$tok% delims=.:/-, " %%u in ('date /t') do (
for /f "skip=1 tokens=2-4 delims=/-,()." %%x in ('echo.^|date') do (
set %%x=%%u
set %%y=%%v
set %%z=%%w
set $d1=
set $tok=))
if "%yyyy%"=="" set yyyy=%yy% if /I %yyyy% LSS 100 set /A yyyy=2000 + 1%yyyy% - 100
set CurDate=%mm%/%dd%/%yyyy%
set dayCnt=%1
if "%dayCnt%"=="" set dayCnt=1
REM Substract your days here set /A dd=1%dd% - 100 - %dayCnt% set /A mm=1%mm% - 100
:CHKDAY if /I %dd% GTR 0 goto DONE set /A mm=%mm% - 1 if /I %mm% GTR 0 goto ADJUSTDAY set /A mm=12 set /A yyyy=%yyyy% - 1 :ADJUSTDAY if %mm%==1 goto SET31 if %mm%==2 goto LEAPCHK if %mm%==3 goto SET31 if %mm%==4 goto SET30 if %mm%==5 goto SET31 if %mm%==6 goto SET30 if %mm%==7 goto SET31 if %mm%==8 goto SET31 if %mm%==9 goto SET30 if %mm%==10 goto SET31 if %mm%==11 goto SET30 REM ** Month 12 falls through
:SET31
set /A dd=31 + %dd%
goto CHKDAY
:SET30
set /A dd=30 + %dd%
goto CHKDAY
Coding to download the data from FTP and to rename in constant Name
:LEAPCHK
set /A tt=%yyyy% %% 4
if not %tt%==0 goto SET28
set /A tt=%yyyy% %% 100
if not %tt%==0 goto SET29
set /A tt=%yyyy% %% 400
if %tt%==0 goto SET29
:SET28
set /A dd=28 + %dd%
goto CHKDAY
:SET29
set /A dd=29 + %dd%
goto CHKDAY
:DONE
if /I %mm% LSS 10 set mm=0%mm% if /I %dd% LSS 10 set dd=0%dd%
echo Date %dayCnt% day(s) before %CurDate% is %mm%/%dd%/%yyyy%
Coding to download the data from FTP and to rename in constant Name :: Today's date in YYYYMMDD format
SET yesterday=%dd%-%mm%-%yyyy%
echo %yesterday%
REM ##***For getting the files from the ftp location and renaming it to a constant name
PATH=%PATH%;c:\Program Files\WinSCP
ECHO open ftp://Bh_IT:uninor@[email protected] > cmd.txt
echo option transfer binary >> cmd.txt
echo option confirm off >> cmd.txt
echo cd dump_today >> cmd.txt
echo synchronize local
for /f "tokens=1-5 delims=/ " %%d in ("%date%") do echo get RPT_6_RECHARGE_REPORT_Bihar%yesterday%.csv.gz HOURLY_RECHARGE_DUMP.csv.gz>> cmd.txt
echo close >> cmd.txt
echo exit >> cmd.txt
winscp /script:cmd.txt
REM ##********************COMMAND TO ZIP THE FILE
echo on
D:
cd D:\AUTOMATION\HOURLY-RECHARGE-DATA\RAW_DATA\
del HOURLY_RECHARGE_DUMP.csv
gunzip -f *.csv.gz
REN *.CSV HOURLY_RECHARGE_DUMP.csv
exit;
Coding to download the data from FTP and to rename in constant NameREM ##*********************For truncate the table and insert data in Databse
echo on
sqlplus scott/tiger@ORCL @TRUNCATE_HOURLY_RECHARGE.sql
sqlldr userid=scott/tiger@ORCL control='HOURLY.CTL' log='HOURLY.log' bad='HOURLY.bad' direct='true'
date/t
time/t
REM ##*********************For generate report from database
@echo on
sqlplus scott/tiger@ORCL @HOURLY.SQL
date/t time/t
pause; exit; winscp /script:cmd.txt
SQL Query which is called by batch Script
update HOURLY_RECHARGE_RPT6 set TRDL_UPLOADED=sysdate;
commit;
truncate table HOURLY_RECHARGE_RPT6;
commit;
EXIT;
SQL Query which is called by batch Script
connect sqlplus scott/tiger@ORCL;
column dcol new_value dt noprint
select to_char(sysdate-1,'rrrrmmdd') dcol from dual;
set colsep "|"
set linesize 9999
set trimspool on
set heading on
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 5000
spool D:\AUTOMATION\HOURLY-RECHARGE-DATA\OUTPUT_DATA\JUN15\RECHARGE_TRANS_STATUS&dt..xls;
SELECT 'TIME1' || '|' || 'RECHARGE_COUNT' || '|' || 'TYPE_OF_RECHARGE'
FROM DUAL;
SQL Query which is called by batch Script
SELECT DECODE ( (SUBSTR (recharge_date_time, 11, 3)),
00,
'00 AM',
01,
'01 AM',
02,
'02 AM',
03,
'03 AM',
04,
'04 AM',
05,
'05 AM',
06,
'06 AM',
07, '07 AM', 08, '08 AM', 09, '09 AM', 10, '10 AM', 11, '11 AM', 12, '12 PM', 13, '13 PM', 14, '14 PM', 15, '15 PM',
SQL Query which is called by batch Script
16,
'16 PM',
17,
'17 PM',
18,
'18 PM',
19,
'19 PM',
20,
'20 PM',
21,
'21 PM',
22,
'22 PM',
23,
'23 PM')
AS time1,
COUNT (SUBSTR (recharge_date_time, 11, 3)) AS recharge_count,
exit;
DECODE (type_of_recharge, 'EVDA', 'EVDA-E-Load-STV_RCV', 'EVDFA', 'EVDFA-E-Load-FR', 'EVDFT', 'EVDFT-E-Load-FR', 'EVDS', 'EVDS-USSD', 'EVDT', 'EVDT-E-Load-STV_RCV_ONLINE', 'VCH', 'VCH1') RECHARGE_TYPE FROM scott.hourly_recharge_rpt6 GROUP BY type_of_recharge, SUBSTR (recharge_date_time,
11, 3) ORDER BY time1;
spool off
Resource Plan & Effort Estimation
#Resources #Resources buldgme(1-5 Years Experienced) Rupeeration(5 to 10 Years Experienced)Batch Scripting 1 1
Oracle 1 1
Activities PDs Start Date End Date
Number of Senior Resources
(5 to 10 Years)Junior Resources
( 1 to 5 Years)Requirement
Gathering 1 1 Design 1 1
Coding and Unit Testing
1 1 1
UAT 2 1 1
Production 1 1 1
Glossary
MIS – Management Information system
Thank You
Aman Raj