c23092_1doc HRS BRS LMS ACQ DRA CARL PCS GL INTERFACE MAPPING .doc

Document Sample
c23092_1doc  HRS BRS LMS ACQ DRA CARL PCS  GL INTERFACE MAPPING .doc Powered By Docstoc
					TOUCHNET/CARL - GL
INTERFACE MAPPING AND
DESIGN DOCUMENT [C.23092]
Maricopa Community College
District
OGF

Author:                OGF Technical & Functional teams
Creation Date:         June 16, 1998
Last Updated:          Oct 12, 1999
Control Number:        1.00
Version:               1




Approvals:


Financial Operations


ITS Representative
                                                                                                                           1.00




Change Record



                                 Date        Author                Version   Change Reference

                                 09-15-99    Janet Price           1         Added Content
                                 10-05-99    Janet Price           1         Added Content




Reviewers



                                 Name                                           Position

                                 Mike Murphy                                    MCCCD GL Mgr.
                                 David Steele                                   MCCCD OGF Tech Mgr.
                                 Barbara Yancy                                  MCCCD GL Lead
                                 Janet Price                                    MCCCD-TouchNet Project Manager




Distribution



                                 Copy No    Name                                     Location

                                 1          Library Master                           Project Library
                                 2          Mike Murphy                              MCCCD GL Manager
                                 3          David Steele                             MCCCD OGF Tech Manager
                                 4          Barbara Yancy                            MCCCD GL Lead
                                 5          Janet Price                              MCCCD-TouchNet Project Manager




OGF                                                              Topical Essay Touchnet/CARL to GL                    ii
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc         MCCCD Confidential - For internal use only
                                                                                                                                                                                1.00




                                 Contents


                                 Topical Essay Touchnet/CARL to GL ....................................................................................... 5
                                       Basic Business Needs – TOUCHNET/CARL ................................................................... 10
                                       Major Features – TOUCHNET/CARL .............................................................................. 10
                                       Definitions – TOUCHNET/CARL ..................................................................................... 11
                                       Error Messages – TOUCHNET/CARL .............................................................................. 11
                                       User Procedures – TOUCHNET/CARL ............................................................................ 14
                                       Accounting Treatment – TOUCHNET/CARL ................................................................. 15
                                       Technical Overview – TOUCHNET/CARL ..................................................................... 16
                                       Technical Overview – GL Interface .................................................................................... 16
                                 GL Feeds Interface Data Mapping ............................................................................................. 18

                                 Download Process ....................................................................................................................... 24

                                 Upload Process ............................................................................................................................. 25

                                 File Descriptions........................................................................................................................... 26

                                 TABLES ......................................................................................................................................... 27
                                       Database Design.................................................................................................................... 27
                                       Desired Table Changes ........................................................................................................ 27
                                       Descriptive Flexfields ........................................................................................................... 28
                                       Value Sets ............................................................................................................................... 28
                                       Grants/Synonyms ................................................................................................................ 28
                                       Installation/Upgrade ........................................................................................................... 28
                                       Archiving ............................................................................................................................... 28
                                       Tables, Indexes, Sequences .................................................................................................. 28
                                 Concurrent Manager ................................................................................................................... 30

                                 Test Plans ...................................................................................................................................... 31
                                       Unit Tests ............................................................................................................................... 31
                                       Integration Test ..................................................................................................................... 31
                                 Integration Issues ......................................................................................................................... 33
                                       Changes Required................................................................................................................. 33
                                       Shared Components ............................................................................................................. 33
                                       Alert conditions..................................................................................................................... 33
                                       Incompatabilites .................................................................................................................... 34
                                       Performance Issues ............................................................................................................... 35
                                       Design ..................................................................................................................................... 35
                                       Testing .................................................................................................................................... 35
                                 Installation .................................................................................................................................... 36

OGF                                                                      Topical Essay Touchnet/CARL to GL                                                           iii
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc                 MCCCD Confidential - For internal use only
                                                                                                                                                                           1.00

                                       Installation Steps ................................................................................................................... 36
                                 Open/Closed Issues .................................................................................................................... 37
                                       Open Issues............................................................................................................................ 37
                                       Closed Issues ......................................................................................................................... 37
                                 Appendix A .................................................................................................................................. 38
                                       Cash Receipts Layout (03x)                                                  Example ..................................... 39
                                       TOUCHNET Layout............................................................................................................. 39
                                       CCTLOG Layout ................................................................................................................... 40
                                       FSFRSOGF.SCH .................................................................................................................... 41




OGF                                                                     Topical Essay Touchnet/CARL to GL                                                        iv
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc                MCCCD Confidential - For internal use only
                                                                                                                                        1.00




Topical Essay Touchnet/CARL to GL
                                           TOUCHNET Credit Card Accounting Feed Interface

                                 Touchnet is integrated to MCCCD legacy system that allows students not only to register
                                 for classes, but also make credit card payments at the same time. Touchnet is a
                                 convenience to the student (no more standing in long lines at the college cashier office)
                                 and also to the college registration and fiscal offices. Students using the TOUCHNET
                                 interface solution will have access to a menu option, which includes payment information.

                                 - Student selects college

                                 - Student selects menu option for payment information

                                 - Student enters Student Identification Number (SID) and Personal Identification Number
                                 (PIN).

                                 - Student selects term

                                  - System calculates and reports the student's tuition and fees for the selected semester

                                 - Student selects menu option to pay tuition and fees by American Express, Novas,
                                 Master Card, Visa, or College card.

                                  - Student enters credit card number

                                 - Student enters expiration date

                                 - Student is notified of the amount that is about to be charged, student has the option to
                                 continue or cancel the transaction.

                                 - System sends authorization request Envoy (credit card clearinghouse) and Envoy sends
                                 back its response (either authorization, rejection or error) to TouchNet payment Gateway.
                                 This response is logged to the payment transaction log (TFDCLOG.txt) for the selected
                                 college.

                                 - If the transaction is authorized by Envoy, system sends a request to update student's
                                 account balance on student's BRS record.

                                 - If both the authorization and the account balance update to student's BRS record are
                                 successful, the system sends the transaction to the batch settlement file and the system
                                 will return a "success" message to the student.

                                 - If the transaction fails, the failed transaction and any associated error codes are recorded
                                 in the payment transaction log (TFDCLOG.txt) for the selected college. And the student
                                 will receive an error message.

                                  - System prints an invoice at the college in either fiscal or Admissions and Records

                                 - College mails student invoice



OGF                                                                                                 Topical Essay Touchnet/CARL to GL          5
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




OGF                                                                                                 Topical Essay Touchnet/CARL to GL          6
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




OGF                                                                                                 Topical Essay Touchnet/CARL to GL          7
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




OGF                                                                                                 Topical Essay Touchnet/CARL to GL          8
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                         TouchNet - GL Interface
                                                                                                                                         1.00




                                                               File is renamed after            IVR files
           IVR                     IVR Transactions
                                                                     settling to:             Encrypted and                 FJA999creates a
       Transactions                     settled
                                                              tfdclogCCivr_date.txt        Transferred to David              CCTLOG.dat
  tfdclog.txt created daily          8:30pm MST                  (1999mmdd.txt)                   Steele




                                   Settled to Envoy                                                                           Focus report
                                    5:00pm MST                                                                                  creates
                                                                                                                             IVRrp1_xx.prt




                                                                                                                           Controllers Dept.
                                  Settled to First Data
                                                                                                                               receives
                                     8:30pm MST
                                                                                                                            FOCUS Report




                                                                                                                            Controllers Dept.
                                  Settled to Bank One                                                                    verifies reports to OGF
                                         7:00am                                                                                      (?)
                                                                                                                           (Jeanette Cernetic)




                                                                                                                         TT&A verifies reports
                                   Settled to MCCD
                                                                                                                           to bank statement
                                        5:00 PM
                                                                                                                               (Jane Situ)




OGF                                                                                                 Topical Essay Touchnet/CARL to GL           9
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




Basic Business Needs – TOUCHNET/CARL

                                 Transfer Touchnet Payment Gateway transaction information to the OGF-GL system
                                 daily.

                                 The previous day's transactions for comparison to the report generated by the Touchnet
                                 solution.

                                 Generate monthly reports for reconciliation to the monthly bank statement.

                                 NOTE: Examples of the reports referred to above include:

                                 a) touch(college)_today’s date file on TOUCHNET Payment Gateway (Windows NT)

                                 b) FJA996 creates a batch, daily., Mon / Sun. - Journal Entry report(s) by batch.

                                 c) FJA996 creates CCTLOG.dat

                                 d) Focus report reads cctlog.dat, creates daily report

                                 c) FOCUS reports: CCTRP1_(college initial).prt – daily touchnet transactions

                                 d) Bank statement Faxed from Bank



Major Features – TOUCHNET/CARL

                                 This system will primarily operate in the background with no direct user interaction. For
                                 this reason, the features of the system are limited to reports generated for review of the
                                 transactions initiated by the system. There are some accounting entries, however, that
                                 need to be reviewed to ensure that deposit amounts (from TOUCHNET payments) are
                                 accurately recorded.

                                 Reporting Features

                                 Daily reports are generated as part of the end of day. These reports are printed at the end
                                 of each day.




OGF                                                                                                 Topical Essay Touchnet/CARL to GL     10
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




Definitions – TOUCHNET/CARL

                                 CARL - (Computer Assisted Registration Line) A touch tone telephone based system
                                 that allows students to enroll in classes and pay fees by credit card.

                                 Touchnet – An integrated interface to MCCD’s current SIS legacy system. It provides
                                 students with access to SIS data and applications; including course enrollment and tuition
                                 and fees payment by credit card




Error Messages – TOUCHNET/CARL

                                 ^-1^Host is down. Try again later.

                                 ^-3^Student is not registered for the selected term. Please select another term.

                                 ^-4^The Credit Card # or Expiration Date is missing. Please enter missing information
                                 and submit again.

                                 ^-5^The new PIN numbers entered do not match. Please try again.

                                 //

                                 // TPG Response Codes --

                                 //

                                 ^-1601^Refer to issuer

                                 ^-1602^Refer to issuer -- special condition

                                 ^-1603^File is temporarily unavailable

                                 ^-1604^Issuer or switch is unavailable

                                 ^-1605^Pick up card

                                 ^-1606^Pick up card -- special condition

                                 ^-1607^Pick up card -- lost

                                 ^-1608^Pick up card -- stolen

                                 ^-1609^Verification error

                                 ^-1610^Already reversed at switch

OGF                                                                                                 Topical Essay Touchnet/CARL to GL     11
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00



                                 ^-1611^Invalid Amount. Please reenter.

                                 ^-1612^Invalid Card Number, please reenter.

                                 ^-1613^Invalid Date, please reenter.

                                 ^-1614^System malfunction

                                 ^-1615^Invalid merchant ID

                                 ^-1616^Cannot verify PIN

                                 ^-1617^Cannot verify PIN

                                 ^-1618^Incorrect PIN

                                 ^-1619^Cashback limit exceeded

                                 ^-1620^Cashback service not available

                                 ^-1621^Verification error - check digit

                                 ^-1622^Verification error - CID format

                                 ^-1623^Decline - do not honor

                                 ^-1624^Decline - insufficient funds

                                 ^-1625^Decline - exceeds issuer withdrawal limit

                                 ^-1626^Decline - exceeds withdrawal limit

                                 ^-1627^Decline - invalid service code, restricted

                                 ^-1628^Decline - activity limit exceeded

                                 ^-1629^Decline - violation, cannot complete

                                 ^-1630^Cryptographic error

                                 ^-1631^General error

                                 ^-1632^Expired card

                                 ^-1633^Destination not found

                                 ^-1634^Invalid transaction

                                 ^-1635^No account

                                 ^-1636^No checking account

                                 ^-1637^No credit account

OGF                                                                                                 Topical Essay Touchnet/CARL to GL     12
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00



                                 ^-1638^No savings account

                                 ^-1639^No action taken, unable to back out trans

                                 ^-1640^No action taken, unable to locate, no match

                                 ^-1641^No action taken, inconsistent data, reversal or repeat

                                 ^-1642^No such issuer

                                 ^-1643^PIN tries exceeded

                                 ^-1644^Reenter transaction

                                 ^-1645^Security violation

                                 ^-1646^Transaction not permitted - card

                                 ^-1647^Transaction not permitted - terminal

                                 ^-1648^Duplicate batch transactions

                                 ^-1649^Rejected batch transactions

                                 ^-1650^Retransmit batch transactions




OGF                                                                                                 Topical Essay Touchnet/CARL to GL     13
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




User Procedures – TOUCHNET/CARL

                                 The initiation of the processing performed by this system does not require user
                                 intervention. The required procedures are to verify the accuracy of the transactions
                                 compared to the transactions recorded by the bank on a daily and monthly basis.

                                 The procedures required to monitor the accuracy of the system and to insure the accuracy
                                 of the bank records are performed primarily in the Controller's department. A summary
                                 of the TOUCHNET monitoring process follows:

                                 Daily, someone in General Ledger receives FOCUS reports (CCTRP1_"C", where "C" is the
                                 campuses initials) which represents prior day transactions on the TOUCHNET Payment
                                 Gateway. (NOTE: An e-mail message is sent if NO activity) .

                                 Someone in General Ledger compares the information on the FOCUS report to the OGF
                                 Journal Entry reports.......... these amounts must agree.

                                 Daily, someone in General Ledger will receive a fax from the bank, which lists credit card
                                 transactions for the prior day. General Ledger compares the information on the fax to
                                 FOCUS report.........these amounts must agree.(NOTE: This step performed only in the
                                 event of initial testing and/or problem identification, resolution).

                                 Monthly, someone in General Ledger reconciles the General Ledger accounts (Focus
                                 report TOUCHNET_MP) to TOUCHNET (legacy account number, all colleges, is 0-2700-
                                 c-1653) activity in SIS/BRS. OGF account number 210-ccc-249000-16075.

                                 Bank auditing reconciles the Browser report to the BR-CARL and the bank statement.

                                 Browser Reports – ogfsave.br_carl master/visa credit card

                                                        ogfsave.br_carl discover credit card

                                                        ogfsave br_carl american express credit card




OGF                                                                                                 Topical Essay Touchnet/CARL to GL     14
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




Accounting Treatment – TOUCHNET/CARL

                                 The accounting entries associated with TOUCHNET payments are basically one of two
                                 transactions:

                                     a) The recording of the cash received at the bank, after the student has authorized the
                                 credit card payment to MCCCD for tuition/registration

                                     b) The recording of the student receivable and corresponding revenue to reflect
                                 charges and recognize revenue.

                                    The first of these transaction types (cash received at the bank, for and crediting the
                                 MCCCD bank account for TOUCHNET credit card payments) results in the following
                                 accounting entries:

                                                                    Payments

                                   The accounting entries for TOUCHNET credit card payments are based on the SIS
                                 subcode 64500, which is associated with:

                                       Acct1 0-2700-c-1653                             Acct2 0-2700-c-2130 *

                                    When a payment is made through TOUCHNET the following journal entry is fed to
                                 OGF on a daily basis (Batch is "CARL01" transaction code = TC030) by an automated feed
                                 from TOUCHNET Payment Gateway to the LTCU (creates flat file) to OGF (General
                                 Ledger) GL = Journal entry (actual) in OGF GL (see cash receipts layout Appendix A).

                                          Debit                                     Credit

                                    ---------------------                  -------------------

                                     (CASH*, 02700-C-1100)                  0-2700-c-1653

                                     (Example TC030 entry to record cash payment, from TOUCHNET, daily to GL)

                                   * Indirect entry for cash, system-generated; where "c" is college code)

                                 In addition to this daily feed (TC030 cash receipt entry) an automated BRS process posts
                                 the following weekly entry, for the TOUCHNET accounting process:

                                       0-2700-C-1653                 0-2799-C-1660 (BRS clearing account)

                                 (Entry created via TC061, for TOUCHNET payments, via weekly BRS accounting feed)

                                   Finally, TOUCHNET affects yet another entry when a student registers. However, for
                                 purposes of this interface write-up, the accounting entries associated with ALL student
                                 registrations will be covered in another essay (see the SIS/BRS-FRS 230-06 interface
                                 writeup).


OGF                                                                                                 Topical Essay Touchnet/CARL to GL     15
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




Technical Overview – TOUCHNET/CARL

                                 -    Daily (Monday through Sunday) a batch settlement program runs at each college to
                                      settle all successful transactions recorded in payment transaction log (TFDCLOG.txt)
                                      to Envoy credit card clearinghouse.

                                 -    The TFDCLOG.txt is renamed to TFDCLOGCCIVR_YYYYMMDD.txt where "CC" is
                                      the campus initials, "YYYY" is the year, "MM" is the month and "DD" is the day. The
                                      new TFDCLOGCCIVR_YYYYMMDD.pgp file is now encrypted and transferred to
                                      VAX

                                 -    FJA996 is the DCL (Digital Communication Language) that process the
                                      TFDCLOGCCIVR_YYYYMMDD.pgp once the file is on the VAX. The first job that
                                      runs is touchivr_DECRYPT, this job decrypts the
                                      TFDCLOGCCIVR_YYYYMMDD.pgp and creates TOUCHIVR.DAT. The program
                                      FBA996 reads the TOUCHIVR.DAT and creates 030 transactions to feed to OGF GL
                                      and creates a file called CCTLOG.DAT that the focus report program (CCTRP1) will
                                      process against. An email is created and sent to designated individuals if there were
                                      no IVR transactions that day. A separate IVR print file (CCTRP1_XXX.PRT) is created
                                      by CCTRP1 for each college and transmitted to each college. An email is sent to each
                                      college notifying them of the report. All CCTRP1_XXX.PRT are combined into
                                      IVRRPt_ALL.PRT and printed at District

                                 - - The 030 IVR transactions along with the other GL transaction are processed daily
                                 (Monday – Friday) in FSFRSOGF com, which runs FJGLOGF.




Technical Overview – GL Interface

                                 Daily (Monday through Sunday) a program runs that reads the TOUCHNET Payment
                                 Gateway transaction file and pulls out the credit card transactions that were processed in
                                 the last 24 hours. These transactions are moved to District (see appendix A touchnet
                                 layout) . FBA996 runs against the credit card transactions. A new transaction file (see
                                 appendix A cctlog layout) is created, credit card reports are run against this new
                                 transaction file. Credit card reports are transmitted via the net to each college for printing.
                                 A combined credit card report is printed at District for the Business area user’s. Credit
                                 card transactions (see appendix A, 030 layout) are created that will be processed on the
                                 next OGF process, these transactions will be stored in a safe directory until OGF is ready
                                 to process.

                                 When the process for OGF starts the first process that happens is all transactions that
                                 affect GL are now appended together for the OGF process.

                                 These transactions are moved to a safe directory where the OGF process will pick up these
                                 transactions and process them (see appendix A, FSFRSOGF).


OGF                                                                                                 Topical Essay Touchnet/CARL to GL     16
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




Banking Related Issues – TOUCHNET/CARL




OGF                                                                                                 Topical Essay Touchnet/CARL to GL     17
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00




GL Feeds Interface Data Mapping


Key to Data Mapping Table

Target column : GL_INTERFACE Oracle Government Financials column name for the field which is the target for the source
data.

Format(size) : The data format of the target data field.

         c(9) - character field with a length equal to the numeric value (9) specified.
         date - date format field
         number or n(9) - numeric format field

Null:    Database required--NN=not null R = Required

SRC: The source of the source data.

         C - Constant "hard coded" value.
         D - Legacy system data.
         G - Generated by the import program
         S - Selected fromOracle Government Financials tables based on legacy data

Source column or constant value : The legacy system column name for the source data or the constant data value which will
"hard coded" into the target field.

Format(size) : The data format of the source data when it is from the legacy system.

         c(9) - character field with a length equal to the numeric value (9) specified.
         date - date format field
         number - numeric format field

Validation/Comments : Narrative describing the options and/or reasoning behind the method the target field is being populated.




OGF                                                                                                   GL Feeds Interface Data Mapping     18
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
GL_INTERFACE

Target Column                  Format   Null   SRC   Source column or    Format     Validation/Comments
                               (size)                constant value      (size)
STATUS                         c(50)    NN     C     NEW
SET_OF_BOOKS_ID                n(15)    NN     C     1                              From GL_SETS_OF_BOOKS.SET_OF_
                                                                                    BOOKS_ID.
ACCOUNTING_DATE                date     NN     D     tran_date           (8)yyyym
                                                                         mdd
CURRENCY_CODE                  c(15)    NN     C     USD                            USD or STAT. From
                                                                                    FND_CURRENCIES.CURRENCY_CODE.
DATE_CREATED                   date     NN     C     sysdate
CREATED_BY                     n(15)    NN     C     0                              From FND_USER.USER_ID.
                                                                                    (SYSADMIN)
ACTUAL_FLAG                    c(1)     NN     D     tran_abe            c(1)       (A)ctual, (E)ncumbrance, or (B)udget
USER_JE_CATEGORY_NAME          c(25)    NN     C     MCCCD FEEDS                    From GL_JE_CATEGORIES.
                                                                                    USER_JE_CATEGORY_NAME.
USER_JE_SOURCE_NAME            c(25)    NN     S     GL_JE_SOURCES.U                From GL_JE_SOURCES.
                                                     SER_JE_SOURCE_N                USER_JE_SOURCE_NAME
                                                     AME                            where user_je_source_name like <feed>
                                                                                    where <feed> = ‘BRS%’, ‘CARL%’,
                                                                                    ‘DRA%’, ‘HRS PAY%’, ‘LMS%’
                                                                                    depending on the source system such as
                                                                                    CARL, DRA, HRS etc.
CURRENCY_CONVERSION_DATE       date            C     null
ENCUMBRANCE_TYPE_ID            number          S     GL_ENCUMBRANCE                 Enter only if ACTUAL_FLAG=E.
                                                     _TYPES.ENCUMBRA                Select ENCUMBRANCE_TYPE_ID
                                                     NCE_TYPE_ID                    From GL_ENCUMBRANCE_TYPES
                                                     Only if                        where ENCUMBRANCE_TYPE =
                                                     ACTUAL_FLAG = ‘E’              ‘Obligation’
BUDGET_VERSION_ID              number          C     null                           Enter only if ACTUAL_FLAG=B. From
                                                                                    GL_BUDGET_VERSIONS.
                                                                                    BUDGET_VERSION_ID.
USER_CURRENCY_CONVERSION_TYP   c(30)           C     null
E
CURRENCY_CONVERSION_RATE       number          C     null
SEGMENT1                       c(25)    R      S     LTCU Procedure                 Use LTCU Procedure to obtain Accounting
                                                                                    key Flexfield Segment FUND
SEGMENT2                       c(25)    R      S     LTCU Procedure                 Use LTCU Procedure to obtain Accounting
                                                                                    key Flexfield Segment UNIT value.
SEGMENT3                       c(25)    R      S     LTCU Procedure                 Use LTCU Procedure to obtain Accounting
                                                                                    key Flexfield Segment CHARGE CENTER
                                                                                    value.
                                                                                                                                        1.00

Target Column                                       Format        Null      SRC      Source column or           Format        Validation/Comments
                                                    (size)                           constant value             (size)
SEGMENT4                                            c(25)         R         S        LTCU Procedure                           Use LTCU Procedure to obtain Accounting
                                                                                                                              key Flexfield Segment OBJECT value.
SEGMENT5                                            c(25)         R         S        LTCU Procedure                           Use LTCU Procedure to obtain Accounting
                                                                                                                              key Flexfield Segment SUBOBJECT value.
SEGMENT6                                            c(25)         R         S        LTCU Procedure                           Use LTCU Procedure to obtain Accounting
                                                                                                                              key Flexfield Segment GRANT value
SEGMENT7                                            c(25)         R         S        LTCU Procedure                           Use LTCU Procedure to obtain Accounting
                                                                                                                              key Flexfield Segment GRANT
                                                                                                                              CATEGORY value
SEGMENT8                                            c(25)         R         S        LTCU Procedure                           Use LTCU Procedure to obtain Accounting
                                                                                                                              key Flexfield Segment PROJECT Value
SEGMENT9                                            c(25)                   C        null
SEGMENT10                                           c(25)         R         C        00000                                    Leave blank if filling out the
                                                                                                                              CODE_COMBINATION_ID field.
SEGMENT11                                           c(25)         R         C        00000                                    Leave blank if filling out the
                                                                                                                              CODE_COMBINATION_ID field.
SEGMENT12                                           c(25)                   C        null
SEGMENT13                                           c(25)                   C        null
SEGMENT14                                           c(25)                   C        null
SEGMENT15                                           c(25)                   C        null
SEGMENT16                                           c(25)                   C        null
SEGMENT17                                           c(25)                   C        null
SEGMENT18                                           c(25)                   C        null
SEGMENT19                                           c(25)                   C        null
SEGMENT20                                           c(25)                   C        null
SEGMENT21                                           c(25)                   C        null
SEGMENT22                                           c(25)                   C        null
SEGMENT23                                           c(25)                   C        null
SEGMENT24                                           c(25)                   C        null
SEGMENT25                                           c(25)                   C        null
SEGMENT26                                           c(25)                   C        null
SEGMENT27                                           c(25)                   C        null
SEGMENT28                                           c(25)                   C        null
SEGMENT29                                           c(25)                   C        null
SEGMENT30                                           c(25)                   C        null
ENTERED_DR                                          number                  D        tran_dr                    n(11)
ENTERED_CR                                          number                  D        tran_cr                    n(11)
ACCOUNTED_DR                                        number                  C        null                                     For foreign currency.
ACCOUNTED_CR                                        number                  C        null                                     For foreign currency.
TRANSACTION_DATE                                    date                    C        null                                     Always leave null.


OGF                                                                                                   GL Feeds Interface Data Mapping                                   20
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00

Target Column                                       Format        Null      SRC      Source column or           Format        Validation/Comments
                                                    (size)                           constant value             (size)
REFERENCE1                                          c(100)                  D        batch_id                   c(26)         Batch Name appended with (Source)
                                                                                                                              (RequestID) (Actual Flag) (GroupID).
REFERENCE2                                          c(240)                  D        select description via                   Batch Description appended with (Source)
                                                                                     user_je_source_name                      (RequestID).
REFERENCE3                                          c(100)                  C        null                                     Always leave null.
REFERENCE4                                          c(100)                  D        REFERENCE1 :               c(14)         Journal Entry Name appended with
                                                                                     REFERENCE2                               (Category Name) (Currency) (Encumbrance
                                                                                                                              Type ID, if applicable) (Budget Version ID,
                                                                                                                              if applicable).
REFERENCE5                                          c(240)                  C        null                                     Journal Entry Description.
REFERENCE6                                          c(100)                  C        null                                     Journal Entry Reference.
REFERENCE7                                          c(100)                  C        null                                     Journal Entry Reversal Flag.
REFERENCE8                                          c(100)                  C        null                                     Journal Entry Reversal Period. (If needed,
                                                                                                                              from GL_PERIODS.PERIOD_NAME).
REFERENCE9                                          c(100)                  C        null                                     Always leave null.
REFERENCE10                                         c(240)                  D        account : object           c(10)         Journal Entry Line Description.
REFERENCE11                                         c(100)                  C        null                                     Always leave null.
REFERENCE12                                         c(100)                  C        null                                     Always leave null.
REFERENCE13                                         c(100)                  C        null                                     Always leave null.
REFERENCE14                                         c(100)                  C        null                                     Always leave null.
REFERENCE15                                         c(100)                  C        null                                     Always leave null.
REFERENCE16                                         c(100)                  C        null                                     Always leave null.
REFERENCE17                                         c(100)                  C        null                                     Always leave null.
REFERENCE18                                         c(100)                  C        null                                     Always leave null.
REFERENCE19                                         c(100)                  C        null                                     Always leave null.
REFERENCE20                                         c(100)                  C        null                                     Always leave null.
REFERENCE21                                         c(240)                  C        account : object :         c(57)         Maps to GL_JE_LINES.REFERENCE_1.
                                                                                     tran_desc : reference1 :                 (Prints out on std. reports with line detail.)
                                                                                     reference2 : tran_code
REFERENCE22                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_2.
REFERENCE23                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_3.
REFERENCE24                                         c(240)                  S        user_je_source_name        c(25)         Maps to GL_JE_LINES.REFERENCE_4.
                                                                                                                              (Prints out on std. reports with source
                                                                                                                              detail.)
REFERENCE25                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_5.
REFERENCE26                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_6.
REFERENCE27                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_7.
REFERENCE28                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_8.
REFERENCE29                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_9.
REFERENCE30                                         c(240)                  C        null                                     Maps to GL_JE_LINES.REFERENCE_10.
JE_BATCH_ID                                         n(15)                   C        null                                     Always leave null.

OGF                                                                                                   GL Feeds Interface Data Mapping                                          21
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00

Target Column                                       Format        Null      SRC      Source column or           Format        Validation/Comments
                                                    (size)                           constant value             (size)
PERIOD_NAME                                         c(15)                   C        null                                     Enter only if ACTUAL_FLAG=B. From
                                                                                                                              GL_PERIODS.PERIOD_NAME.
JE_HEADER_ID                                        n(15)                   C        null                                     Always leave null.
JE_LINE_NUM                                         n(15)                   C        null                                     Always leave null.
CHART_OF_ACCOUNTS_ID                                n(15)                   C        null                                     Always leave null.
FUNCTIONAL_CURRENCY_CODE                            c(15)                   C        null                                     Always leave null.
CODE_COMBINATION_ID                                 n(15)                   C        null                                     If SEGMENTS 1-11 are null, then you must
                                                                                                                              fill out this field.
DATE_CREATED_IN_GL                                  date                    C        null                                     Always leave null.
WARNING_CODE                                        c(4)                    C        null                                     Always leave null.
STATUS_DESCRIPTION                                  c(240)                  C        null                                     Always leave null.
STAT_AMOUNT                                         number                  C        null
GROUP_ID                                            n(15)                   C        null
REQUEST_ID                                          n(15)                   C        null                                     Always leave null.
SUBLEDGER_DOC_SEQUENCE_ID                           number                  C        null                                     Always leave null.
SUBLEDGER_DOC_SEQUENCE_VALU                         number                  C        null                                     Always leave null.
E
ATTRIBUTE1                                          c(150)                  C        null
ATTRIBUTE2                                          c(150)                  C        null
ATTRIBUTE3                                          c(150)                  C        null
ATTRIBUTE4                                          c(150)                  C        null
ATTRIBUTE5                                          c(150)                  C        null
ATTRIBUTE6                                          c(150)                  C        null
ATTRIBUTE7                                          c(150)                  C        null
ATTRIBUTE8                                          c(150)                  C        null
ATTRIBUTE9                                          c(150)                  C        null
ATTRIBUTE10                                         c(150)                  C        null
ATTRIBUTE11                                         c(150)                  C        null
ATTRIBUTE12                                         c(150)                  C        null
ATTRIBUTE13                                         c(150)                  C        null
ATTRIBUTE14                                         c(150)                  C        null
ATTRIBUTE15                                         c(150)                  C        null
ATTRIBUTE16                                         c(150)                  C        null
ATTRIBUTE17                                         c(150)                  C        null
ATTRIBUTE18                                         c(150)                  C        null
ATTRIBUTE19                                         c(150)                  C        null
ATTRIBUTE20                                         c(150)                  C        null
CONTEXT                                             c(150)                  C        null
CONTEXT2                                            c(150)                  C        null
INVOICE_DATE                                        date                    C        null


OGF                                                                                                   GL Feeds Interface Data Mapping                                    22
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                        1.00

Target Column                                       Format        Null      SRC      Source column or           Format        Validation/Comments
                                                    (size)                           constant value             (size)
TAX_CODE                                            c(15)                   C        null
INVOICE_IDENTIFIER                                  c(20)                   C        null
INVOICE_AMOUNT                                      number                  C        null
CONTEXT3                                            c(150)                  C        null
USSGL_TRANSACTION_CODE                              c(30)                   C        null                                     Transaction code.
DESCR_FLEX_ERROR_MESSAGE                            c(240)                  C        null                                     Always leave null.




OGF                                                                                                   GL Feeds Interface Data Mapping               23
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
Download Process
The GL Interface is divided into two primary processes. The first process delivers legacy data to the VAX server in the form of a
flat file. The second process loads the legacy data from the flat file into the OGF Application. This section deals with the first
process.

The first process will append all of the GL transactions from the MCCCD Legacy systems TOUCHNET into one temporay file.
The appended transactions will then be processed by a program that will format these transactions into MCC_GL flat file. A job
will then copy the MCC_GL from the Vax server to the inbox directory on the Dec Alpha (see Appendix A, FSFRSOGF.SCH).
                                                                                                                                     1.00



Upload Process
The GL Interface is divided into two primary processes. The first process delivers legacy data to the OGF computer in the form
of a flat file. The second process loads the legacy data from the flat file into the OGF Application. This section deals with the
second process.

The second process is a report set that operates once each night under the control of the Concurrent Manager, and needs no user
intervention to operate. The report set is divided into three tasks depicted in the data flow diagram below. There are also two
manual processes depicted which are for error handling.


1.   The first task reads the data from the flat file and loads it into a temporary table. This is implemented using SQL*Loader.
     SQL*Loader uses a control file - mcg30101.ctl file - to describe the data layout in the flat file. It also defines which flat file
     fields map to which table fields and what kind of data the flat file field is, i.e. date, number etc. SQL*Loader checks that
     required fields are present and that some fields like dates meet format requirements. Records that do not meet these
     requirements are written to a bad records file. In addition, SQL*Loader creates a log file of each load. There will be no
     discard file since there is no conditional loading.

2.   The second task is implemented as a PL/SQL script - mcg30102.sql - which reads records from the temporary table. The
     script uses the account and the object fields in the temporary table row to find the corresponding COA flex fields by using the
     Database Procedure documented in C60.240.01 and loads the data in the GL_INTERFACE table. If no COA exists in the
     MCC_LTCU tables for an account number, the record is written to the GL_Interface table, with the Legacy Account mapped
     to the Suspense Account corresponding to the Source of the Feed (See closed issues).

3.   The third task is the Journal Import program. This program reads the interface table and populates the appropriate OGF
     Application tables. It populates these tables only if the batch is complete and the records make valid journal entries. Invalid
     records are printed in the Journal Import Execution Report and are left in the interface table.

4.   The fourth task is not part of the Concurrent Manager Report Set. This is a manual process to correct errors in the Journal
     Import. If there is a problem with a batch all records stay in the GL_Interface Table. A functional person uses the Journal
     Import Execution Report to determine the problems encountered. Corrections are made using the Correct Journal Import
     Data Form. Records that are part of a batch but were not imported can be found in the Bad Records Log and are put into the
     GL_Interface table using the Correct Journal Import Data Form. After all corrections are made the Journal Import program is
     run again to complete the import.
5.   The final step, which is not part of the Report Set, is to run the Journal Post program.




OGF                                                                                                                Upload Process     25
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                     1.00



File Descriptions

FLAT FILE
File Name:   mcg30101.tfd
BATCH_ID            6
BATCH_DATE          DATE format YYYYMMDD
ACCOUNT             6
OBJECT_GL_SL        1
OBJECT              4
TRAN_DATE           DATE format YYYYMMDD
TRAN_DR             number 11
TRAN_CR             number 11
TRAN_DESC           20
TRAN_ABE            1
TRAN_CODE           3
REFERENCE1          7
REFERENCE2          7

After processing, the flat file is moved to the archive directory with an extension of the process date.



CONTROL FILE
File Name:         mcg30101.ctl
This file describes the flat file for SQL*Loader and tells SQL*Loader which flat file fields map to which database fields.


SQL*LOADER LOG FILE
File Name:          mcg30101.log
This file is created automatically by SQL* Loader. The name of the file is l<requestid>.log.


SQL*LOADER BAD FILE
File Name:          mcg30101.bad
This file is created automatically by SQL*Loader. The name of the file is <requestor>.requestid>.


LOAD LEGACY TRANSACTIONS IN GL_INTERFACE
File Name:         mcg30102.sql
This PL*SQL script uses the account and object number from the legacy record to find the COA flexfields adds the records in the
Interface table. If there is no matching ten digit account code the record is entered into the GL Interface table with the account
code stored in the Journal Entry Description Field and the Suspense Account corresponding to the source (HRS , BRS etc.) is
used insted. See APPENDIX for list of Suspense Accounts.

RUN JOURNAL IMPORT FOR LEGACY TRANSACTIONS
File Name:      mcg30103.sql
This PL*SQL script calls the OGF Journal Import Module with the appropriate parameters.

INSTALLATION SCRIPT
File Name:                   mcg30100.sql
This file creates the temporay table and one day archive table. It also creates grants, etc. for the tables. This file is run only once
and is not part of the regular process flow.




OGF                                                                                                              File Descriptions    26
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                               1.00



TABLES
Account Mapping Table is described in C.60.240.01 (LTCU)

Temporary table
Table Name:      MCC_GL301_TMP
Field Name                       Field Type
BATCH_ID                         VARCHAR2(6)
BATCH_DATE                       DATE
ACCOUNT                          VARCHAR2(6)
OBJECT_GL_SL                     VARCHAR2(1)
OBJECT                           VARCHAR2(4)
TRAN_DATE                        DATE
TRAN_DR                          NUMBER(11,2)
TRAN_CR                          NUMBER(11,2)
TRAN_DESCRIPTION                 VARCHAR2(20)
TRAN_ABE                         VARCHAR2(1)
TRAN_CODE                        VARCHAR2(3)
REFERENCE1                       VARCHAR2(7)
REFERENEC2                       VARCHAR2(7)
STATUS                           VARCHAR2(1)
                                 P - Posted
                                 R - Rejection
REJ_RSN                          VARCHAR2(70)
No indexes are needed since all records are processed and no lookups on this table are performed.

One day archive table
Table Name:      MCC_GL301_TMP_ARCH
Field Name                Field Type
BATCH_ID                  VARCHAR2(6)
BATCH_DATE                DATE
ACCOUNT                   VARCHAR2(6)
OBJECT_GL_SL              VARCHAR2(1)
OBJECT                    VARCHAR2(4)
TRAN_DATE                 DATE
TRAN_DR                   NUMBER(11,2)
TRAN_CR                   NUMBER(11,2)
TRAN_DESC                 VARCHAR2(20)
TRAN_ABE                  VARCHAR2(1)
TRAN_CODE                 VARCHAR2(3)
REFERENCE1                VARCHAR2(7)
REFERENCE2                VARCHAR2(7)
No indexes are needed since no lookups are performed on this table.


Database Design


Desired Table Changes

                                  None.




OGF                                                                                                   TABLES    27
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                        1.00


Descriptive Flexfields

                                  None.



Value Sets

                                  None.



Grants/Synonyms

Owner                                            Object                                           Grantees

GL                                               GL_INTERFACE                                     MCC




Installation/Upgrade
                                       1.   Create MCC database user
                                       2.   Create new tables
                                       3.   Run grant/synonym script
                                       4.   Complete GL Setup
                                       5.   Define Value sets and Validation Tables
                                       6.   Define Descriptive Flexfields
                                       7.   Define Concurrent Program, Executable, and assign the Concurrent Program to
                                            the GL Report Group; also Define a Report Set and assign the Report Set to the
                                            Report Group



Archiving

                                  Archive the posted records in Mcc_GL301_TMP table AND THE mcg30101.tfd daily.



Tables, Indexes, Sequences

                                  Temporary table for loading GL Feed Transactions from Legacy Flat file

                                  CREATE TABLE MCC_GL301_TMP values (
BATCH_ID                            VARCHAR2(6)
BATCH_DATE                          DATE
ACCOUNT                             VARCHAR2(6)
OBJECT_GL_SL                        VARCHAR2(1)
OBJECT                              VARCHAR2(4)
TRAN_DATE                           DATE
TRAN_DR                             NUMBER(11,2)
TRAN_CR                             NUMBER(11,2)


OGF                                                                                                            TABLES        28
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                               1.00
TRAN_DESCRIPTION                    VARCHAR2(20)
TRAN_ABE                            VARCHAR2(1)
TRAN_CODE                           VARCHAR2(3)
REFERENCE1                          VARCHAR2(7)
REFERENEC2                          VARCHAR2(7)
STATUS                              VARCHAR2(1)
REJ_RSN                             VARCHAR2(70)



                                  );

                                  It is not necessary to register this table.




OGF                                                                                                   TABLES    29
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                      1.00


Concurrent Manager


                             Concurrent Program Executable

                               Application Name:                   MCCCD Custom                       Report Set Sequence #

                               Name:                               mcg30101.ctl                       1

                               Description                         SQL*Loader

                               Execution File:                     TBD

                               Execution Method                    SQL*Loader

                               Application Name:                   MCCCD Custom

                               Name:                               mcg30101.sql                       2

                               Description:                        Account Map and Load

                               Execution File:                     TBD

                               Execution Method                    SQL*PLUS

                               Application Name                    MCCCD

                               Name:                               MCG30103                           3

                               Description:                        Run Journal Import
                                                                   Program




OGF                                                                                                              Concurrent Manager          30
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                      1.00


Test Plans


Unit Tests

                                        Module:               GL Download

                                        Tested by:

                                        Date:
       Seq Action                                                Expected Results                     Actual Results
        1 Append all GL batches together into one file, daily.   All GL batches are now combined into
                                                                 one file
        2    Run program that will read the appended GL          All GL batch transactions are now in
             batches and will load them into mcg30101.tfd file   mcg30101.tfd file
        3    Run com that will move mcg30101.tfd file from       mcg30101.tfd file should now appear
             DEC Vax to DEC Alpha inbox directory under          now on the DEC Alpha
             $MCC_TOP


                                        Module:               GL Upload

                                        Tested by:

                                        Date:
       Seq Action                                                Expected Results                       Actual Results
        1  Run SQL*Loader with combined good and bad             Only good data should be in
           data flat file                                        Temporary table. Bad records should
                                                                 be in bad record log.
         2    Run ‘Load Legacy Data in GL Interface’ script with All records should be in the GL
              some data having invalid account numbers           Interface table, with Suspense
                                                                 Account segment values loaded for
                                                                 the invalid accounts
         3    Run ‘Run Journal Import for Legacy GL              The Journal Import Program
              Transactions’ script                               completes .
         4    Run the Journal Entry Report and Journal Entry     All the transactions in the input file
              Line Report and verify that all the journal entry  have been loaded in OGF
              transactions in the input file have been loaded in
              OGF




Integration Test

                                        Tested by:

                                        Date:
       Seq Action                                Navigation      Expected Results                      Actual Results
        1  Run the OGF Trial Balances for                        The Trial Balances print out.
           Budget and Actuals before the
           run




OGF                                                                                                                      Test Plans          31
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                                      1.00
       Seq Action                               Navigation       Expected Results                       Actual Results
        2  Run the Report Set                                    One day archive table should contain
                                                                 data from previous test. Temporary
                                                                 table should have data from current
                                                                 flat file. There should be Journal
                                                                 entries for all records. Check The
                                                                 Journal Import Log to ensure there
                                                                 are no errors
         3   Run Journal Post                                    The batch is posted without any
                                                                 errors
         4   Run the Trial Balance Budget and                    The Account Balances for Budget and
             Actuals and verify the balances                     Actuals i.e. the difference beteen the
                                                                 amounts printed in step1 and step4 -
                                                                 tally with the transaction amounts,
                                                                 by account, for this batch.




OGF                                                                                                                      Test Plans          32
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                              1.00


Integration Issues


Changes Required

                                  Within Product

                                  No Changes required


                                  Other Products
                                  No changes required



Shared Components

Alert conditions
The following are the alert conditions that are created by the OGF journal exception report:

Period Error Codes
------------------
EP01 This date is not in any open or future enterable period.
EP02 This set of books does not have any open or future enterable periods.
EP03 This date is not within any period in an open encumbrance year.

Unbalanced Journal Error Codes
------------------------------
WU01 Warning: This journal entry is unbalanced. It is accepted because suspense posting is allowed in this set of books.
EU02 This journal entry is unbalanced and suspense posting is not allowed in this set of books.
EU03 This encumbrance journal entry is unbalanced and the Reserve for Encumbrance account is not defined.

Flexfield Error Codes
---------------------
EF01 This Accounting Flexfield is inactive for this accounting date.
EF02 Detail posting not allowed for this Accounting Flexfield.
EF03 Disabled Accounting Flexfield.
EF04 This is an invalid Accounting Flexfield. Check your cross-validation rules and segment values.
EF05 There is no Accounting Flexfield with this Code Combination ID.

Foreign Currency Error Codes
----------------------------
EC01 A conversion rate must be entered when using the User conversion rate type.
EC02 There is no conversion date supplied.
EC03 A conversion rate type must be supplied when entering foreign currency journal lines.
EC04 There is no conversion rate entered for this conversion date.
EC05 There is no conversion rate entered for this conversion rate type.
EC06 There are no conversion rates for this currency.
EC08 Invalid currency code.
EC09 No currencies are enabled.
EC10 Encumbrance journals cannot be created in a foreign currency.
EC11 Invalid conversion rate type.
EC12 The entered amount must equal the accounted amount in a functional or STAT currency journal line.
EC13 The entered amount multiplied by the conversion rate must equal the accounted amount.
ECW1 Warning: Converted amounts could not be validated because the conversion rate type is not specified.

OGF                                                                                                      Integration Issues          33
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                               1.00
Budget Error Codes
------------------
EB01 A budget version is required for budget lines.
EB02 Journals cannot be created for a frozen budget.
EB03 The budget year is not open.
EB04 This budget does not exist for this set of books.
EB05 The encumbrance_type_id column must be null for budget journals.
EB06 A period name is required for budget journals.
EB07 This period name is not valid. Check calendar for valid periods.

Encumbrance Error Codes
-----------------------
EE01 An encumbrance type is required for encumbrance lines.
EE02 Invalid or disabled encumbrance type.
EE03 Encumbrance journals cannot be created in the STAT currency.
EE04 The budget_version_id column must be null for encumbrance lines.

Descriptive Flexfield Error Codes
---------------------------------
ED01 The context and attribute values do not form a valid descriptive flexfield for Journals - Journal Entry Lines.
ED02 The context and attribute values do not form a valid descriptive flexfield for Journals - Captured Information.
ED03 The context and attribute values do not form a valid descriptive flexfield for Value Added Tax.

Miscellaneous Error Codes
-------------------------
EM01 Invalid journal entry category.
EM02 There are no journal entry categories defined.
EM03 Invalid set of books id.
EM04 The value in the actual_flag must be "A" (actuals), "B" (budgets), or "E" (encumbrances).
EM05 The encumbrance_type_id column must be null for actual journals.
EM06 The budget_version_id column must be null for actual journals.
EM07 A statistical amount belongs in the entered_dr(cr) column when entering a STAT currency journal line.
EM08 There is no reversal period name.
EM09 There is no Transaction Code defined.
EM10 Invalid Transaction Code.
EM12 An Oracle error occurred when generating sequential numbering.
EM13 The assigned sequence is inactive.
EM14 There is a sequential numbering setup error resulting from a missing grant or synonym.

Miscellaneous Error Codes
-------------------------
EM17 Sequential numbering is always used and there is no assignment for this set of books and journal entry category.
EM18 Manual document sequences cannot be used with Journal Import.
EM19 Value Added Tax data is only valid in conjunction with actual journals.
EM20 This reversal period name is invalid. Check your calendar for valid periods.
EM21 Budgetary Control must be enabled to import this batch.
EM22 A conversion rate must be defined for this accounting date, your default conversion rate type, and your dual currency.
EM23 There is no value entered for the Dual Currency Default Rate Type profile option.



Incompatabilites

                                  None




OGF                                                                                                       Integration Issues          34
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                           1.00


Performance Issues

                                  It is estimated that the maxium number OGF GL transactions should not exceed 3000 per
                                  day, and this number will not affect performance of the Dec Alpha.



Design

                                  Initial design




                                  Customer approval




                                  Detailed design




                                  Final sign-off




Testing

                                  The customizations will be tested in MCCCD test environment before being moved to
                                  production. See the Test Plans section for more information.




OGF                                                                                                   Integration Issues          35
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                              1.00


Installation
                                  All modules are installed as if part of a separate product. A directory structure is created
                                  under $APPL_TOP as follows:
                              $Appl_top
                                   |
                                  mcc
                                   |
                                 1.0.0
_____________________________________|____________________________________________
    |          |       |      |      |        |          |    |
archive     forms    bin    src    sql     log    out srw install
    |                                                       |----
|       |
outbox inbox                                                 sql

                                  The directories contain the components of the enhancement as shown below (directories not
                                  listed are empty). All future customizations will also be stored in these directories.
                             archive
                             inbox
                             log
                             out
                             sql/
                             install/sql/
                                  The concurrent programs are registered in Application Object Library under the MCCCD
                                  application.



Installation Steps
                                        ) Create custom application directory (run only once)
                                        ) Load table create files into custom application directory
                                        ) Edit APPLSYS.env file to include definition of MCC_TOP
                                        ) Register custom application with Application Object Library
                                        ) Shutdown and restart concurrent manager
                                        ) Create custom database user (run only once in each user)
                                        ) Run installation script to create database objects
                                       cd $MCC_TOP/install/sql
                                        ) Define and register programs and report set
                                        Add report set to appropriate report security group
                                        ) Test interfaces




OGF                                                                                                            Installation          36
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                           1.00


Open/Closed Issues


Open Issues

                                       None.



Closed Issues




OGF                                                                                                   Open/Closed Issues          37
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                   1.00


Appendix A


Batch Header Layout                                                          Example


Transaction code                   Required                     C (03)       $$$

Batch ID                           Required                     C(06)        CARL01

Batch Date                         Required                     C (06)       101698

Datch Description                  Optional                     C (15)       CREDIT CARDS

Batch List Option                  Optional                     C (01)       Y

User ID                            Optional                     C (02)       FA

Batch Trans Count                  Optional                     N (05)       00002

Batch Trans Amount                 Optional                     N (11)       00000007300

Batch Special ID                   Optional in FA               C (03)

Originaotor                        Optional                     C (08)

Batch Bank Bal #                   Optional                     C (02)       26

Hold Flag                          Optional                     C (01)

FAS Feed Flag                      Optional for AP only         C (01)

Voucher Feed Flag                  Optional                     C (01)

Accept Out Of Balance              Optional                     C (01)

Batch Special Process              Optional                     C (01)




OGF                                                                                                   Appendix A          38
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                   1.00




Cash Receipts Layout (03x)                                                   Example


Transaction Code                   Required                     N (03)       030

Account Number                     Required                     C (10)       0270041659

Reference One                      Optional                     C (07)       CARLMC

Date                               Optional                     N (04)       0923

Description                        Optional                     C (20)       601101304018441DS

Amount                             Required                     N (11)       00000001000

D/C Indicator                      Optional                     C (01)

Reference two                      Optional                     C (07)       DS

User ID                            Optional                     C (11)

Filler                             Required                     C (44)




TOUCHNET Layout

RECORD-TYPE                        Required                     C (01)
TRANSACTION-TYPE                   Required                     C (03)
DATE                               Required                     C (10)
TIME                               Required                     C (08)
CREDIT-FLAG                        Required                     C (01)
ACCOUNT-NUMBER                     Required                     C (16)
EXPIRATION-DATE                    Required                     C (04)
AMOUNT                             Required                     C (09)
TRANSACTION-ID                     Required                     C (15)
APPROVAL-CODE                      Required                     C (20)
ANCILLARY-DATA                     (see below)
 SID                               Required                     C (09)
 TRANS-CODE                        Required                     C (05)
 TERM                              Required                     C (03)
 HCODE                             Required                     C (20)
 CLIENTID                          Required                     C (10)
ERROR-CODE                         Required                     C (01)




OGF                                                                                                   Appendix A          39
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                   1.00




CCTLOG Layout

TRANSACTION-DATE                   Required                     C (08)
TRANSACTION-TIME                   Required                     C (06)
CUSTOMER-ID                        Required                     C (20)
CREDIT-CARD-TYPE                   Required                     C (02)
CREDIT-CARD-NUMBER                 Required                     C (16)
EXPIRE-DATE                        Required                     C (04)
DOLLAR-AMOUNT                      Required                     C (08)
DEBIT-CREDIT-                      Required                     C (01)
INDICATOR
ERROR-CODE                         Required                     C (04)
AUTHORIZATION-ID                   Required                     C (06)
PROTOBASE-                         Required                     C (08)
SEQUENCE-NUMBER
OPER-ID                            Required                     C (12)
TERM-YEAR                          Required                     C (04)
TERM                               Required                     C (02)
CAMPUS-SCHOOL                      Required                     C (03)
DEPARTMENT                         Required                     C (06)
USER-DEFINED                       Required                     C (50)




OGF                                                                                                   Appendix A          40
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only
                                                                                                                   1.00




FSFRSOGF.SCH

!+BATCH SCHEDULER: FRSPB/FRS                           JOB SCHEDULE = FSFRSOGF
!+            ---------                       -------
!+             RUN AT 6:00 AM IN THE MORNING!!!!!
!+             ________________________________
!+
!+ Gathers the A/P and G/L Legacy transactions for processing in OGF
!+
!+OPS NOTES: If AQBATCH is running in DIST$BATCH do NOT start schedule until it
!+       is finished.
!+
!+ FJDCHK - AQ BATCH CHECK
!    a special .com to display a message
!    that reminds the operator to check for AQBATCH
FJDCHK
!
!+ FJACQ - LIB ACQUISITIONS TRANSACTION FEED
!    FjAcq feeds ACQ & DRA transactions
!
FJACQ "FA=YES","AP=YES"
!
!+ VJBRS - SIS TO BRS BBD105 TRANSACTION FEED
!    VjBrs feeds SIS/BRS BJD105 transactions
VJBRS
!
!+ FJBRS - SIS TO BRS BBD100 TRANSACTION FEED
!    FjBrs feeds SIS/BRS BJD100 transactions
FJBRS
!
!+ VJAPOGF - Will gather the A/P Legacy transactions to feed info OGF/AP
!
VJAPOGF
!
!+ FJGLOGF - Will gather the G/L Legacy transactions to feed into OGF/GL
!
FJGLOGF
!
!+ FJDTCU - Create OGF-GL Interface flat file(s) and RCP to FBS system
!
FJDTCU
!
!+ VJT230 - CREATE OGF-AP INTERFACE FLAT FILE AND RCP TO FBS
!
VJT230
!
!+ PBEMP1 - Create OGF Employee Interface flat file and RCP to FBS
!
PJEMP1
!
!End FSFRSOGF




OGF                                                                                                   Appendix A          41
25f1945c-4cc7-41eb-ab6b-cb8270e9c30e.doc (v. 1 ) Oracle/Client Confidential - For internal use only

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:54
posted:6/30/2010
language:English
pages:41
lily cole lily cole
About