Guidelines and Procedures on the Processing of BIR Collection Reports as Required by Department of Finance (DOF)

April 11, 1998 REVENUE MEMORANDUM ORDER NO. 37-98 SUBJECT : Guidelines and Procedures on the Processing of BIR Collection Reports as Required by Department of Finance (DOF) TO : All Regional Directors, Revenue District Officers and Other Internal Revenue Officers Concerned I. BACKGROUND The Department of Finance (DOF) required Bureau of Internal Revenue (BIR) to submit a monthly collection report of computerized RDOs, and a comparative year-to-date collection report for all RDOs every 12 th and 22 nd of the month, respectively. prcd II. OBJECTIVES This Revenue Memorandum Order is issued to prescribe the guidelines and procedures on the Processing of BIR Collection Reports as Required by DOF, in order to: 1. Delineate the functions of each office/division/unit in the processing of DOF required collection reports; and 2. Ensure timely processing and submission of collection reports to DOF. III. DEFINITION OF TERMS The following terms shall be used in this Order as follows: 1. SCRIPT . This refers to short programs developed to undertake specific functions. 2. REPORTING SCRIPT . This refers to a script whose function is to extract information for reporting purposes. 3. REPORT TEMPLATE . This refers to a blank report form where collection data will be fitted in. 4. MS EXCEL . This refers to the spreadsheet software which will be used in the preparation of the collection reports. 5. BIR-CDF . This refers to the BIR National Office ITS server. 6. FTP . This refers to File Transfer Facility. 7. BIR-APPL . This refers to BIR Development server. 8. MS MAIL/E-MAIL . This refers to the software which will be used to transfer data from one location to another. IV. GUIDELINES AND PROCEDURES In order to achieve the above-stated objectives, the following policies and procedures are hereby prescribed: A. Monthly Collection Report of Computerized Revenue District Offices (RDOs) 1. Data Preparation Process 1.1 Application Maintenance Office (AMO), Information Systems Development Service (ISDS) shall: 1.1.1 Prepare reporting scripts for each RDO to extract monthly RDO collection details from ITS, and forward the same to Systems Management Office (SMO). 1.1.2 Prepare a report template worksheet using MS Excel, and forward the same to Revenue Accounting Division (RAD). 1.2 Systems Management Office (SMO), Information Systems Operations Service (ISOS) shall: 1.2.1 Extract all monthly RDO collection details from ITS into flat files. Perform this by running scripts for each RDO, which generate flat files. Details follow: a) Telnet to bir-cdf. Change to the working directory. b) Run the script (bir-cdf: ~noparops/rdo_col.sql) for a certain RDO. c) Enter the filename (rdo###_col), RDO code and period end date when prompted. d) Run the script for each RDO. The script will produce a text file with an 1st extension, for ex . rdo030_col . 1st . 1.2.2 Transfer text files from server to local machine. Details follow: (a) For direct connection to bir-cdf: (a.1) Open FTP utility in Windows. (a.2) Login to bir-cdf. (a.3) Change to directory where text files were created. (a.4) FTP the text files to the local drive. (b) For direct connection to bir-appl: (b.1) Telnet to bir-appl. (b.2) In UNIX, FTP to bir-cdf. Change to working directory where the text files are located. (b.3) Run mget command to retrieve text files. (b.4) Follow step 1.2.2(a) to ftp the text files to the local drive. 1.2.3 Transfer flat files to RAD via E-mail. Details follow: a) Compose a mail using Microsoft Mail and address the appropriate recipients of the files. b) Attach the corresponding text files. c) Send the mail. 2. Data Consolidation and Report Preparation Process 2.1 Revenue Accounting Division (RAD) shall: 2.1.1 Consolidate information from flat files into Excel. Details follow: a) Run Microsoft Excel. b) Open the report template from ASM. c) Open a text file using '|' delimited option. d) Copy the collection column of the text file and paste the values on the appropriate RDO column in the report template. e) Open the rest of the text files, copy and paste the corresponding values to the appropriate RDO columns. f) Save the file using R01mmyyA.xls, where: R is a one letter abbreviation for BIR, 01 is the report number for this report, mmyy represents the month and year, A is a letter from A-Z, designating the version or revision level of the report. ( ex . R010498A . xls ) 2.1.2 Forward report to DOF via E-mail. B. Comparative Year-To-Date Collection Report For All RDOs 1. Report Template Preparation Process 1.1 Information Management Office (IMO), ISOS shall: 1.1.1 Create Monthly Collection data-entry worksheet for RAD using MS Excel. 1.1.2 Create Year-to-Date Collection consolidation worksheet for RAD using MS Excel. 1.1.3 Transmit MS Excel worksheet (data-entry and consolidation) files to RAD. 1.1.4 Replicate MS Excel worksheet files and forward to RAD for distribution to RDOs. 2. Report Template Distribution Process 2.1 Revenue Accounting Division (RAD) shall: 2.1.1 Transmit disks containing MS Excel worksheet files to all non-pilot RDOs with computers. 2.1.2 Transmit hard copies of MS Excel worksheet to all non-pilot RDOs without computers via fax or mail. 3. RDO Report Preparation Process 3.1 Revenue District Office (RDO) shall: 3.1.1 For RDOs with computers. a) Encode collection reports into the MS Excel worksheet template files. b) Save MS Excel files. Files should be saved with different filename, depending on RDO, and month of report. Format is: yyyymm.RDO (Ex: For RDO 029's collection report last March 1998, Filename should be: 199803.029) c) Print out hard copy of MS Excel files. 3.1.2 For RDOs without computers. a) Fill up collection reports into empty Collection Detail forms. b) Keep a copy of filled-up form. 3.1.3 Transmit completed disks/forms to RO. 4. RDO Reports Transmittal Process 4.1 Revenue Office (RO) shall: 4.1.1 Encode collection reports into MS Excel worksheet (For collection reports from RDOs without computers). 4.1.2 Ensure the receipt of disks and forms from all non-pilot RDOs. 4.1.3 Track inventory of diskettes and forms received. 4.1.4 Transmit MS Excel files with hard copies of reports to RAD via e-mail or courier. 5. Data Consolidation and Report Preparation Process 5.1 Revenue Accounting Division (RAD) shall: 5.1.1 Combine collection reports from all MS Excel files and text files into "Consolidated Cumulative Collection" MS Excel file from AMO. 5.1.2 Save the file using R01mmyyA.xls, where: R is a one letter abbreviation for BIR, 01 is the report number for this report, mmyy represents the month and year, A is a letter from A-Z, designating the version or revision level of the report. ( ex . R020498A . xls ) 5.1.3 Forward the report to DOF via E-mail. V. REPEALING CLAUSE This Order supersedes all revenue issuances or portions thereof inconsistent herewith. VI. EFFECTIVITY This Order shall take effect immediately. LIWAYWAY VINZONS-CHATO Commissioner of Internal Revenue ATTACHMENT B-1 ATTACHMENT B-2