ACL Training Materials Unit A ACL KPMG ACL

ACL Training Materials Unit A: ACL基本觀念及操作 主要參考資料來源: KPMG ACL課程講義資料 Price. Water. House. Cooper ACL課程講義資料 2020/11/3 1

Topics • • Getting Data from the clients Understanding data structure of imported data Structure of data within a Project Loading data into ACL 2020/11/3 2

Getting data from the client Pw. C 2020/11/3 3

Getting data from the client 2020/11/3 6

Understanding the Structure of Imported Data 2020/11/3 8

Sec 2. 1 擷取及下載資料（Accessing and Downloading Data） (p. 9~p. 11) §Data File §Record §Field §Data 2020/11/3 9

Structure of data within a Project Pw. C 2020/11/3 17

Structure of data within a Project The major physical components of a Project are: • Overview window (全覽視窗) • Data View window (個別資料視窗) • Command Log window (稽核軌跡) • Menu Bar • Button Bar • Status Bar 2020/11/3 18

Structure of data within a Project The major physical components of a Project are: • Overview window (全覽視窗) • Command Log window (稽核軌跡) • Data View window (個別資料視窗) • Menu Bar • Button Bar • Status Bar 2020/11/3 19

Structure of data within a Project The major physical components of a Project are: • Overview window (全覽視窗) • Command Log window (稽核軌跡) • Data View window (個別資料視窗) • Menu Bar • Button Bar • Status Bar 2020/11/3 20

Structure of data within a Project The major physical components of a Project are: • Overview window (全覽視窗) • Data View window (個別資料視窗) • Command Log window (稽核軌跡) • Menu Bar • Button Bar • Status Bar 2020/11/3 21

Structure of data within a Project The major physical components of a Project are: • Overview window (全覽視窗) • Data View window (個別資料視窗) • Command Log window (稽核軌跡) • Menu Bar • Button Bar • Status Bar 2020/11/3 22

Structure of data within a Project The major physical components of a Project are: • Overview window (全覽視窗) • Data View window (個別資料視窗) • Command Log window (稽核軌跡) • Menu Bar • Button Bar • Status Bar 2020/11/3 23

Structure of data within a Project • The major logical components of a Project are: • Batches • Input files • Views • Indexes 2020/11/3 26

Structure of data within a Project 檔案結構 Project Files (. ACL) Output Data Files (. FIL) Log Files (. LOG) Index Files (. INX) 2020/11/3 27

Loading data into ACL Pw. C 2020/11/3 29

Loading data into ACL • Data files need to be translated into a format that ACL can read and work with. This is done using an Input File Definition: An Input File Definition (IFD) is a format used to describe the structure and content of a data file and to specify where the data file can be found • IFD’s are defined by the user within ACL and then used by ACL to create an input file derived from the original data source. 2020/11/3 30

Loading data into ACL • There a number of steps involved in creating an IFD. • At the end of the process, an input file will have been created, linked to the source data file using the IFD, and loaded into ACL. The source data file location and characteristics are input into ACL and the input file is created Using the record definition obtained from the client, the record fields are defined and named The input file is loaded into the open ACL Project 2020/11/3 31

Loading data into ACL • Activity 1 - Loading data into ACL – 固定欄位檔 • Activity 2 - Loading data into ACL – 報表檔 2020/11/3 32

Loading data into ACL- Activity 1 步驟 1：點選 File/New Project from the Menu Bar 步驟 2：出現 Select Input File Definition 後， 點選 New 2020/11/3 33

Loading data into ACL- Activity 1 • ACL makes the process of defining an IFD fairly simple using a built in Data Definition Wizard 2020/11/3 34

Loading data into ACL- Activity 1 步驟 3： 選擇欲匯入之資 料檔的來源。 2020/11/3 35

Loading data into ACL- Activity 1 步驟 4： 選取最適合剖 析匯入資料的 檔案類型。 2020/11/3 36

Loading data into ACL- Activity 1 步驟 8： 完成資料檔的匯 入後的如左如示。 2020/11/3 41

Loading data into ACL- Activity 1 由 Edit/Input File Definition(IDF) 進入。 • 匯入資料後，欄位定 義仍可修改；但欄位名 稱則不可再修改。 2020/11/3 43

Input File Definition • 新增: • 啟動 Data Definition Wizard : [Overview]視窗, 點選[New] Icon • Select Data file to be defined • 將轉入資料命名及存檔: *. fil • Finish: 命名IFD名稱 • • 修改或手動設定: [Overview]視窗, 選取要修改的IFD 項目 選取 [Edit]/[Input File Definition] 選取 [Edit Fields/Expressions]頁面 2020/11/3 45

第三章輸入檔定義 (Input File Definition) Sec 3. 2詮譯日期(Interpreting Dates) 標準日期： Type Format American MM DD YY European DD MM YY 31 12 00 MMM DD DEC 31 Julian YYDDD 00365 2020/11/3 Example using December 31, 2000 12 31 00 47

第三章輸入檔定義 (Input File Definition) Sec 3. 2詮譯日期(Interpreting Dates) 設定起始紀元（Setting the Start of Century） 如果我們日期包含四個位數(four-digit years)的年份並已設定 21世紀為起始元年，就不需要設定起始紀元，只有將顯示西元年 後兩位的情形下，才須要設定起始紀元。 Start of Century 00 Date Years Display as Dates 00 to 99 1900– 1999 (cannot display after 2000) 05 05 to 99 1905– 1999 00 to 04 2000– 2004 60 60 to 99 1960– 1999 00 to 59 2000– 2059 77 77 to 99 1977– 1999 00 to 76 2000– 2076 2020/11/3 48

Workshop A-2 內部稽核人員打算利用ACL對ABC公司進行應收帳 款查核，查核規劃，乃依下列方式進行： 1. 自ABC公司下載應收帳款資料(AR. FIL) 2. 在ACL中，定義AR. FIL 各欄位相關定義如下： Name start Len Title No 1 6 Customer Number Date 7 8 Invoice Date Due 15 8 Due Date Ref 23 6 Reference Number Type 29 2 Transaction Type Amount 31 10 Transaction Amount 2020/11/3 49