Vectren Under Over 40 Hours Report Xpay136r

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) Author: Srinivas Kalva Creation Date: 02-JAN-2002 Last Updated: 02-JAN-2002 Version: 1

Vectren Under Over 40 Hours Report (XPAY136R.rdf) Version 1

SIGECO

Request No. 1583

Date Complete 01-Feb-2002

Developer Srinivas Kalva

02/09/08

Notes New Program

1

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf)

Table of contents

Page

1.DESCRIPTION..........................................................................................................................................................3 2.REPORT LAYOUT....................................................................................................................................................3 2.1 BODY....................................................................................................................................................................3 2.2 MARGIN................................................................................................................................................................3 2.3 HEADER PAGE........................................................................................................................................................3 2.4TRAILER PAGE..........................................................................................................................................................4 3.RUN TIME PARAMETERS.....................................................................................................................................4 4.TABLES AND COLUMNS REFERENCED...........................................................................................................4 5.SQL..............................................................................................................................................................................5 5.1 NAME OF SQL STATEMENT.......................................................................................................................................5 5.2 NAME OF SQL STATEMENT.......................................................................................................................................6 5.3 DATA LAYOUT DIAGRAM.........................................................................................................................................6 6.SPECIAL LOGIC & FLOWCHART ....................................................................................................................6 6.1 6.2 6.3 6.4

SUMMARY COLUMNS...............................................................................................................................................6 FORMULA COLUMNS...............................................................................................................................................6 PLACEHOLDER COLUMNS.........................................................................................................................................6 OTHER LOGIC........................................................................................................................................................7 6.5 FLOWCHART....................................................................................................................................................7

7.REPORT INSTALLATION (CONCURRENT MANAGER)................................................................................7 7.1 REGISTER THE EXECUTABLE......................................................................................................................................8 7.2DEFINE THE REPORT AS A CONCURRENT PROGRAM.........................................................................................................9 7.3DEFINE FIRST RUN TIME PARAMETER........................................................................................................................10 ..................................................................................................................................................................................10 7.4DEFINE SECOND RUN TIME PARAMETER.....................................................................................................................10 7.5DEFINE THIRD RUN TIME PARAMETER.......................................................................................................................11 7.6ATTACH REPORT TO REQUEST GROUP........................................................................................................................11

SIGECO

02/09/08

2

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 1. Description The report follows Vectren format standards and has the following layout: UNDER/OVER 40 HOURS REPORT Employee #

Name

Week 1

2765

Doe, John

24

1639

Jones, Bill

38

8731

Smith, Sue

Report Field Employee # Name Date Hours

Table Column Display_employee Full_name Work_dt Hours

Week 2

32 42

Table Time_sheet_output per_all_people_f Time_sheet_output Time_sheet_output

Owner *workforce Hr *Workforce *Workforce

Database link Tasdevread Tasdevread Tasdevread

* The workforce owner is not currently defined. Use workforce2_0. The report is sorted by name. No totals are printed. 2. Report Layout 2.1 Body In general terms describe the data and cosmetic structure presented in the “body” section of the report layout. Cosmetic structure covers a description of the heading and control break totals and boilerplate text. Detail fields should be listed in left to right, top to bottom order. Special logic tied to a displayed field should also be explained here. Placeholder, Summary and Formula logic should be presented in the appropriate sub-sections of section 6. Special Logic (later in this document). If no part of the report is displayed in the “body” section, put ‘Not applicable’ under this sub-section.

2.2 Margin Same type of description as the “Body” section above. If no part of the report is displayed in the “margin” section put ‘Not applicable’ under this sub-section.

2.3 Header Page

SIGECO

02/09/08

3

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) Same type of description as the “Body” section above. If no part of the report is displayed in the “header” section put ‘Not applicable’ under this sub-section.

2.4 Trailer Page Same type of description as the “Body” section above. If no part of the report is displayed in the “trailer” section put ‘Not applicable’ under this sub-section.

3. Run Time Parameters The user must enter the Payroll Name This section should describe any information that is entered by the report requestor at run time to select or filter specific data for the report. Date range specifications, pull down lists, etc. should be clarified here. Any internal code that examines and/or changes the user input should also be explained here, e.g. date conversion, case changes and calculations. It might be a good idea to list the internal program data names and how they relate to their corresponding inputs. Enter ‘Not applicable.’ if no run time parameters are entered.

4. Tables and Columns Referenced This section describes the tables and columns used in the report. Please notice that columns that are primary keys are marked ‘PK’ in the Column Name column. Give the length of the data in the Format Column. The Null Column contains ‘Not’ if the column is indicated as Not Null in the TRM. The last column, Usage, contains codes that indicate how the column is used (see below). The ‘D’ code indicates the column will be displayed somewhere in the report. The ‘J’ code, which indicates usage in a SQL join, means that the column is used in a join in the Data Layout section of the report. The ‘S’ code indicates non-join usage in SQL in the Data Layout section of the report. Non-join usage would be any type of usage not used to tie two tables together, e.g. LIKE, IN, BETWEEN, etc. The ‘P’ code means that the column will be used in code in some other object in the program such as a Formula or Placeholder column. This could also mean that the column could be used in SQL that is not the base report SQL (found in the Data Layout section). Please leave the key and tables structure below in the document to list and explain the tables and columns.

D = Column is displayed on the report. J = Column is used in a SQL Join. Table Name qualifier.table_name_1 “ “ “ qualifier.table_name_2 “

SIGECO

Column Name column_1 column_2 column_3 column_4 column_5 column_6

02/09/08

S = Column is used in SQL other than a Join. P = Column is used in PL/SQL code.

PK

PK

Format Number(15) Date Varchar2(120) Number Number(15) Varchar2(30)

Null ? Not

Usage J,P D,P S D J,P D

Not Not

4

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) Table Name

Column Name

Format

Null ?

Usage

5. SQL select a.display_employee Employee_Num, b.first_name, b.last_name, sum(decode(a.week_end_dt,to_date(:ppend)-7,calc_value)) Week_1, sum (decode(a.week_end_dt,to_date(:ppend), calc_value)) Week_2 from wforcelink.time_sheet_output_summary@tas_dblink a ,wforcelink.employee@tas_dblink b where a.employee = b.employee and a.calc_value != 40 and to_date(ppend,'DD-MON-YY') = :ppend and b.ld4 like NVL(:PYNAME,'%') and b.other_string11 like NVL(:ORGNAME,'%') --and a.DISPLAY_EMPLOYEE = 951 and b.end_eff_dt in (select max(c.end_eff_dt) from wforcelink.employee@tas_dblink c) group by a.display_employee, b.first_name, b.last_name order by to_number (a.display_employee) The following sections document each SQL statement in the Data Layout section of the report and the relationship of one SQL statement to another (for example, master/detail). The name of the SQL statements are usually Q_1, Q_2, etc.; however, they may be relabeled with more meaningful names. The text of the appropriate SQL should be printed under each sub-section header followed by an explanation/description of the SQL. Filters, joins, unions, intersection, etc. should be documented here. There should be one sub-section for each SQL listed in the Data Layout section of the report. The last sub-section should display a screen shot of the Data Layout section of the report.

5.1 name of SQL statement Place text of SQL for first Data Layout group here. Selection criteria in the above SQL are as follows: Place explanation/description of above SQL here.

SIGECO

02/09/08

5

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 5.2 name of SQL statement Place text of SQL for second Data Layout group here. Selection criteria in the above SQL are as follows: Place explanation/description of above SQL here.

5.3 Data Layout Diagram Place a screen shot of the Data Layout diagram from the report here. You should import the screen shot into MS Paint and trim off the excess prior to importing it into the report. After trimming the screen shot, save it in 256-color bitmap format. Trimming and saving in 256 color will keep the documentation smaller and save disk space. If you feel it necessary, you may write a small description of the Data Layout structure following the screen shot of that structure.

6. Special Logic & Flowchart This section documents the three types of columns, Summary, Formula and Placeholder, that may be created and used within a report. It may also document other logic such as Report Triggers. Additionally, it may document execution events via a flowchart. If a particular column type is not used within a report, document this fact by entering ‘Not applicable.’ under the appropriate subsection type. Documentation should consist of the PL/SQL code (if any) tied to the column and a brief description of what the code does.

6.1 Summary Columns For each summary column, list the column name, the column that is being summed, when it is printed and when it is reset to zero. It is advisable to document summary columns with screen shots of the summary column form.

6.2 Formula Columns Place the text of the PL/SQL code and an explanation/description of that code here.

6.3 Placeholder Columns Place the text of the PL/SQL code and an explanation/description of that code here.

SIGECO

02/09/08

6

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 6.4 Other Logic Place the text and explanations/descriptions of other logic such as Report Triggers here.

6.5 Flowchart If the complexity of this report is more than trivial, place a flowchart here explaining the sequence of events within the execution of the report.

7. Report Installation (Concurrent Manager) The documentor should place the names and locations (NT network directories) of the report executable (rdf file) to be install/updated here. This area should also contain the UNIX target directory for the object listed. Any information about the FTP should be recorded here also. This information could include such information as the transfer mode (binary or ASCII). See the example below. Source: VDEV XPAY/Reports/XPAY136R.rdf Target: VPRD/$XPAY_TOP/Reports/XPAY136R.rdf FTP transfer mode = binary See the BMIS Report Registration documentation for more detailed information about how to install this report using the following screen images.

SIGECO

02/09/08

7

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 7.1 Register the Executable

Place a screen shot of the appropriate screen for registering the executable in the development database. You should import the screen shot into MS Paint and trim off the excess prior to importing it into the report. After trimming the screen shot, save it in 256color bitmap format. Trimming and saving in 256 color will keep the documentation smaller and save disk space.

SIGECO

02/09/08

8

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 7.2 Define the Report as a Concurrent Program

Place a screen shot of the appropriate screen for defining the report as a concurrent program in the development database. You should import the screen shot into MS Paint and trim off the excess prior to importing it into the report. After trimming the screen shot, save it in 256-color bitmap format. Trimming and saving in 256 color will keep the documentation smaller and save disk space.

SIGECO

02/09/08

9

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 7.3 Define First Run Time Parameter

7.4 Define Second Run Time Parameter

SIGECO

02/09/08

10

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) 7.5 Define Third Run Time Parameter

7.6 Attach Report to Request Group Responsibilities:

SIGECO

US HRMS Manager

02/09/08

11

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf)

SIGECO

02/09/08

12

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf) Place a screen shot of the appropriate screen for attaching a report to a request group development database. You should import the screen shot into MS Paint and trim off the excess prior to importing it into the report. After trimming the screen shot, save it in 256color bitmap format. Trimming and saving in 256 color will keep the documentation smaller and save disk space. In addition to trimming the report, it is advisable to rotate the screen shot 270 degrees so that it will fit on the page. Request Group:

SIGECO

VVC_PR_Workforce Time Reports

02/09/08

13

SIGECO CUSTOM REPORTS Vectren Under Over 40 Hours Report (XPAY136R.rdf)

APPROVALS Note: Use the following signoff schedule if there is no official request.

Design Completion (to FDEV): Name None

Signature

Date

Build and Test Completion (to FTST): Name None

Signature

Date

Signature

Date

In Production (to FPRD): Name Joy King Barb Crow ADDENDUM:-

XPAY136R.xls

End of Document

SIGECO

02/09/08

14