ProblemThis assignment is based on the We Keep It Storage (WKIS) Company’ssystem, an accountingsystem. Transactions (this is a double-entry accounting system)will be taken from a holdingtable (NEW_TRANSACTIONS) and inserted into the TRANSACTION_DETAILandTRANSACTION_HISTORY tables. At the same time, the appropriateaccount balance will beupdated in the ACCOUNT table. This system follows a double entryaccounting system with theaccounting rules presented in class.1. Ensure that you have created the WKIS database tablessuccessfully. These files for thisassignment are provided in Module 9 on D2L.2. Design, write a PL/SQL program, and thoroughly test the codedsolution using theinformation outlined above and the guidelines / restrictionsbelow.a. You can assume that every transaction number is unique (therewill be noduplicates) for each “transaction”. Remember that a transaction isa unit – will bemade up of more than one row. All rows that represent a singletransaction willhave the same transactional history information(TRANSACTION_NUMBER,TRANSACTION_DATE, DESCRIPTION).b. Using two nested cursors would make this problem easier tosolve. See Module 4slides and the Module 4 exercise for an example. You do not have touse thismethod in your solution, this is simply a hint and suggestion foran easiersolution.c. Another hint, get the overall structure of your program workingwith clean datafirst. Then start adding error checking and functionality.d. As long as the debits equal the credits in each transaction, youcan assume that theaccounting equation for each transaction holds true. For thisprogram, you do nothave to validate the accounting equation.e. After a transaction has been successfully processed, remove itfrom theNEW_TRANSACTIONS table. Transactions that produce an error shouldremainin the NEW_TRANSACTIONS table.f. An error in one transaction should not prevent the processing ofother transactions(i.e. make sure you do not leave the main looping structure when anerror occurs).g. Only the first error in a transaction should be recorded in theerror log table (i.e. aspecific transaction number should only appear once in the errorlog table). If theerror is a missing transaction number, a single entry can berecorded in the errorlog table for all rows missing a transaction number or can have anentry recordedfor each row missing a transaction number. h. When your instructor evaluates your program, only errors thatappear in the errorlog table will be evaluated. If you only print the error to thescreen, yourinstructor will consider this error as not being caught. Fordebugging you canhave an error written to the error log table AND print to thescreen, this is fine.i. All required tables for this assignment, including the errorlog, are created withthe provided scripts. Do not create any additional tables or modifythe existingtables (structure or constraints).j. Do not use a table of records, or any other type of array, inyour solution to thisproblem. We do not cover these in this course so if you do not knowwhat theyare that is fine. Note: Record data structures are okay; a table ofrecords isdifferent.k. SELECT INTO cannot be performed against the NEW_TRANSACTIONStable.A SELECT on NEW_TRANSACTIONS can only be performed by anexplicitcursor (use your cursor for any needed values from thistable).l. The solution must be done with one anonymous block (multipleembedded blocksare fine as these are not considered separate anonymous blocks). Ifmultipleanonymous blocks are submitted, only the first one in the scriptwill be evaluatedby your instructor.m. Stored programs cannot be used.n. Do not use GOTOs, EXITs, or SAVEPOINTs. CONTINUEs can be used ifdoneappropriately (do not use as you would a break in Java).o. There should be no hard coding of values anywhere in your codeexcept for thetransaction type (‘C’, ‘D’) which should be hard coded only in theDECLAREsection.
3. Your program should handle all exceptions and write thetransactional historyinformation of the transaction that caused the error as well as theerror message to theWKIS_ERROR_LOG table.• Error messages must be descriptive.• Specific errors that must be caught:a. Missing transaction number (NULL transaction number)b. Debits and credits are not equalc. Invalid account numberd. Negative value given for a transaction amounte. Invalid transaction type• All other errors (outside of those listed above) would beconsidered unanticipatederrors. These should be caught as well as we do not want ourprogram to crash.o For these unanticipated errors:The error message would be the system generated error message aswecannot provide a customized descriptive error for this type ofproblem.For our purposes, testing does not need to be performed for thistype oferror. Your instructor will simply be looking for the code to existthatwould handle unanticipated errors.• Your instructor will provide you with two testing datasets.o Clean datao Mix of clean and erroneous data• Your instructor will evaluate your program with a different setof data.o This means that your program should be able to work with anydata.o Your instructor’s data set will be evaluating everything outlinedin the evaluationsection below, so there will be no surprises. Erroneous data willonly be checkingfor those errors specified above.o Do not code to the data sets, code to the problem. The test datais simply there tohelp you confirm your code is working as it should.o It is highly recommended you also create additional test data toensure yourprogram works regardless of the data provided.
Problem This Assignment Is Based On The We Keep It Storage Wkis Company S System An Accounting System Transactions 1 (46.89 KiB) Viewed 43 times
SAIT Default_trans_type can only be 'D' or 'C' ACCOUNT TYPE Account_type_code (PK) Default_trans_type Description PAYROLL LOAD Payroll_date Employee_id Amount Status PAYROLL PROCESSING Month_end Payroll NEW_TRANSACTIONS Transaction_no Transaction_date Description Account_no Transaction_type Transaction_amount WKIS ERROR LOG Transaction_no Transaction_date Southern Alberta Institute of Technology Description Error_msg VARCHAR2(2) HO CHAR(1) VARCHAR2(100) DATE VARCHAR2(9) NUMBER CHAR(1) CHAR(1) CHAR(1) NUMBER DATE VARCHAR2(100) NUMBER CHAR(1) NUMBER We Keep It Storage (WKIS) Physical ERD NUMBER DATE VARCHAR2(100) VARCHAR2(200) ACCOUNT Account_no (PK) Account_name Account_type_code (FK) Account balance TRANSACTION_DETAIL Account_no (PK)(FK1) Transaction_no (PK)(FK2) Transaction_type Transaction_amount TRANSACTION HISTORY Transaction_no (PK) Transaction_date Description NUMBER VARCHAR2(30) VARCHAR2(2) NUMBER Transaction_type can only be 'D' or 'C' NUMBER NUMBER CHAR(1) NUMBER NUMBER DATE VARCHAR2(100)
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!