Microsoft Word - 07_SI_Novan Zulkarnain_Analisa dan Perancangan-a2t-- 1.docx Analysis and Database Design … (Novan Zulkarnain) 67  ANALYSIS AND DATABASE DESIGN FROM IMPORT AND EXPORT REPORTING IN COMPANY IN INDONESIA Novan Zulkarnain Information Systems Department, School of Information Systems, Bina Nusantara University Jl. K.H. Syahdan No. 9, Palmerah, Jakarta Barat, 11480 novan.zulkarnain@gmail.com ABSTRACT Director General of Customs and Excise (DJBC) is a government agency that oversees exports and imports in Indonesia. Companies that receive exemption and tax returns are required to wipe Orkan activities export and import by using IT-based reporting. This study aimed to analyze and design databases to support the reporting of customs based report format for Director General of Customs and Excise No. PER-09/BC/2014. Data collection used was the Fact Finding Techniques consisted of studying the documents, interviews, observation, and literature study. The methods used for Design Database System is DB-SDLC (System Development Life Cycle Database), namely: Conceptual Design, Design Logical and Physical Design. The result obtained is ERD (Entity Relationship Diagram) that can be used in the development of Customs Reporting System in companies throughout Indonesia. In conclusions, ERD has been able to meet all the reporting elements of customs. Keywords: database, DB-SDLC, ERD, customs INTRODUCTION The role of government in building a national industry is important that one of them is to provide a variety of incentives, including fiscal incentives to the industry. One of fiscal incentives given by Law Number 10 Year 1995 regarding Customs as amended by Act No. 17 of 2006 (Customs Act) is the suspension of import duties, VAT or VAT and luxury sales tax, and income tax Article 22 of Import is not collected (Article 44 of the Customs Act), exemption from import duty and VAT or VAT and PPnBM (Article 26 of the Customs Act), as well as the return of import duty has been paid (Article 27 of the Customs Act). Fiscal facility was given to the company that received the status of entrepreneurs Bonded Hoarding (TPB) and the recipient company's Liberation facilities and / or restitution. On article 3, paragraph (2) B.PMK No: 176/PMK.04/2011 and article 3 (2) .b. PMK No: 177/PMK.04/2011: To obtain NIPER exemption and / or NIPER Returns must meet the criteria and requirements as follows: (1) having a good internal control system and (2) having a computer-based information system inventory. Director General of Customs and Excise (DGCE) does not require the company to create a computer-based information system for a new inventory. For companies that already have a computer- based information system inventory, they simply make adjustments to the computer-based information system inventory in accordance with the rules regarding the provision of facilities and amenities TPB exemption and/or restitution. Adjustments should be done among others, the addition of data field types, number, and date of customs documents, and gave access to DJBC on reports produced by the 68  ComTech Vol. 7 No. 1 March 2016: 67-74 system of computer-based inventory information related to customs activities. DJBC ensures that access to these reports will only be used for the benefit DGCE and/or the Director General of Taxation and maintains data confidentiality to be accessed. Companies in Indonesia are required to make adjustments based on IT Inventory according to predetermined criteria. The application can produce report of Regulation of the General Directorate in accordance to Annex IV No. 09/BC/2014 as follows: (1) Raw Material income statement. (2) Raw material usage reports. (3) Usage reports in progress in the framework of subcontracting activities. (4) The income statement production. (5) Production expense reports. (6) Reports mutation of raw materials. (7) Reports production results mutation. (8) Completion waste/scrap report. Majority of the companies in Indonesia are doing export and import have no standard format in making the reports. Thus, the report formats are different from one company to another. This resulted in the DJBC be overwhelmed in analyzing them. IT inventory have the ability to help finance, saving the storage capacity of the warehouse, improving employee performance, improve quality and streamline the rest of the raw materials used (Pons, 2010). It can help suppliers and customers to share their inventory stock so that they can have the same decision about raw material (Lee, Cho and Paik, 2015). DJBC here is simply monitoring residual imported raw materials. To overcome this, we need a database design that can be used by the entire companies in Indonesia. Thus, companies that want to make Inventory Information System-based computers can use the design of this study. Companies are no longer needed to seek out and start from scratch. METHODS The methods used are Fact Finding Method, Analysis Method and Database Design Method of DSDLC. Fact Finding Method that was conducted consisted of interviews, studying documents, observation, and literature study (Connolly, 2005). Interviews were conducted on users in different companies, namely PT. Taehan Textile, PT. Windu Eka and PT. Prima Makmur. The questions was open-ended as the user can respond to questions, so it can be seen the problems and needs (Connolly, 2005). Observations were carried out to see the culture of each company in making DJBC report. Also, to get the data and information in accordance with DJBC, document collection was performed such as sample of invoice, PO, letter of exports and imports, the inventory data of Used Raw Materials (BB) and the Exported Data Production (HP). In the Analysis Method, needs and weaknesses analysis of the current system in the companies were performed in making DJBC report. All documents related to DJBC reports were collected, either in the form of softcopy or hardcopy. Out of the three companies in observation, information system used was still done manually. However, it has been using computer and Internet in the report. The later method is a method of DSDLC Database Design. This method consists of three stages, i.e. Conceptual Design, Logical Design and Physical Design (Connolly, 2005). In Conceptual Design, the determination of the entities, attributes and metadata that are tailored to DJBC report were performed. In the Logical Design, the relationship between the entities that have been determined was made, the normalization process in determining the new entity that previously did not exist was also done. In the final stage, namely the Physical Design, the result of the logical ERD was applied by using SQL language. Analysis and Database Design … (Novan Zulkarnain) 69  SQL language consists of two types, DDL and DML. DDL or Data Definition Language is used to build the structure of the Database, where the structure consists of tables, columns, data types and relationships. On the other hand, DML or Data Manipulation Language is used to manipulate data that consists of a process of displaying the data, inputing data, modifying data, and deleting data (Sheldon and Moes, 2005). To display the interface used web programming language which consists of HTML, CSS, JavaScript and PHP. Hypertex Markup Language or HTML is a standard text document that is displayed using a browser (Shafer, 2005). A CSS or Cascade style sheet is used to set the display of documents. CSS allows us to display the same page with different formats (Shafer, 2005). JavaScript is an interpreted programming language that is mostly used to transform static web pages into dynamic and interactive pages after the web browser has finished downloading a web page (Shafer, 2005). PHP stands for Hypertext Prepocessor which is a programming language widely used for handling the creation and development of a website and can be used in conjunction with HTML (Shafer, 2005). RESULTS AND DISCUSSIONS System Inventory Information Required under the Provisions of DJBC In the order of importance of service and supervision of DJBC to the company that receives facilities of TPB/exemption and/or restitution, DJBC obliges the company to manage its inventory system using a computer-based inventory information system (IT Inventory), see Figure 1. Each part of the company associated with the management of inventory including machinery and office equipment integrated in one (1) Inventory and IT systems to manage data in accordance with the authority of each section. Every part of the operations of recording and performing on activities related to the income and expenditure in accordance with the systems and procedures as well as their respective authorities. For example, expenditure should be inputted in IT expenditure by Inventory section of finished goods. Results of operations in each part related to inventory is stored in the company database and the data that has been recorded in the database is then further processed and prepared for the needs of reporting to stakeholders (including DJBC). Figure 1 General IT Mechanism Inventory 70  ComTech Vol. 7 No. 1 March 2016: 67-74 bahanBaku hasilProduksi bbMasuk bbKeluar hpMasuk hpKeluar bbSubkontrak waste konversi bbmID {PK} WID{PK} hpkID {PK} HPID {PK} BBID{PK} konvID{PK} bbkID {PK} bbsID {PK} hpmID {PK} Conceptual Design Conceptual Design was resulted in this stage. In this stage, a Data Dictionary was created by identifying the entity, identifying relationships, identifying attributes, and determining the primary key. The entities from this stage can be seen in annex IV form of the existing provisions DJBC report. Table 1 Data Dictionary Required for Reporting DJBC No. Entities Attribute Description 1. bbMasuk bbmID, dokID, bbmNoPabean, bbmTglPabean, bbmSeriPabean, bbmNoTerima, bbmTglTerima, BBID, bbName, satID, bbmJml, MUID, bbmNilai, gudangID, subid, negaraID Transaction of imported raw materials 2. bbKeluar bbkID, bbkNoTerima, bbkTglTerima, BBID, bbName, satID, bbkJml, bbkSub, subid Transaction of imported raw materials used for production. 3. hpMasuk hpmID, hpmNoKeluar, hpmTglKeluar, hpID, hpName, satID, hpmJml, hpmSub, gudangID Transaction of production results to be sent to warehouse. 4. hpKeluar hpkID, dokID, hpkNoPEB, hpkTglPEB, hpkNoKeluar, hpkTglKeluar, cusID, negaraID, hpID, hpName, satID, hpkJml, MUID, hpkNilai Transaction of production results that will be exported 5. bbSubkontrak bbsID, bbsNoKeluar, bbsTglKeluar, BBID, bbName, satID, bbsJml, subid Transaction of raw materials that is subcontracted. 6. waste WID, wNoBC, wTglBC, BBID, bbName, satID, wJml, wNilai Waste/scrap transaction from the production process. 7. konversi konvID, hpID, BBID, konvKoefisien, konvTerkandung, konvWaste Conversion table of raw materials for production result From Data Dictionary in Table 1, a conceptual database model can be designed. By using UML symbol, ERD can be generated as follows. Figure 2 ERD Conceptual Design Database Logical Design In logical design, normalization process is carried out for at each report table as in the tables of: bbMasuk, bbKeluar, hpMasuk, hpKeluar, bbSubkontrak, and waste. The normalization process Analysis generate warehou No. 1. b 2. h 3. d 4. m 5. g 6. su 7. n 8. sa 9. c 10. b 11. b 12. h 13. h 14. b 15. w 16. k F designed and Databa es the mast use, state, and Entities bahanBaku hasilProduksi dokumen matauang gudang ubkontrak negara atuan ustomer bbMasuk bbKeluar hpMasuk hpKeluar bbSubkontrak waste konversi From the no d. By using U ase Design … ter table of d documents BBID, bbN hpID, hpN dokID, do MUID, mu gudangID subid, sub negaraID, satID, satN custID, cu bbmID, bbmSeriPa BBID, sat subid, neg bbkID, bb bbkJml, bb hpmID, satID, hpm hpkID, hpkTglKe hpkJml, M bbsID, bb bbsJml, su WID, wN wNilai konvID, konvTerka ormalization UML symbol … (Novan Zul f raw mater . Table 2 D At Name, bbStoc Name, hpStock okName uName , gudangNam bName negaraName Name ustName, custA dokID, bbm abean, bbmN tID, bbmJml, M garaID bkNoTerima, b bkSub, subid hpmNoKelua mJml, hpmSub dokID, h eluar, cusID, MUID, hpkNil bsNoKeluar, b ubid NoBC, wTgl hpID, andung, konvW results of lo l, then ERD Figure 3 ERD lkarnain) rials, produ Design Logica ttribute ckopname kopname me Almt, custTlp mNoPabean, NoTerima, MUID, bbmN bbkTglTerima ar, hpmTglK b, gudangID pkTglDok, negaraID, ai bbsTglKeluar lBC, BBID, BBID, Waste ogical stage can be gener D Logical Dat uction result l Database p, custEmail bbmTglPabea bbmTglTerim Nilai, gudangID a, BBID, satID Keluar, BBID hpkNoKelua BBID, satID r, BBID, satID satID, wJm konvKoefisie in Table 2, a rated as it ca tabase Design t, units, exc Raw mat Producti Docume Currency Warehou Subcontr State ma Unit mas Buyer/re an, ma, D, Transact materials D, Transact materials D, Transact results to ar, D, Transact producti D, Transact raw mate ml, Waste/sc the produ en, Conversi materials a Logical da an be seen in n change, sub Description terials master ion master nt master y master use name mas ract master aster ster eceiver master tion of impo s. tion of impo s used for pro tion of p o be sent to wa tion of on results tion of subc erial. crap transact uction process ion table s for productio atabase mode Figure 3. 71  bcontract, n ter r orted raw orted raw duction. production arehouse exported contracted tion from s. of raw on el can be 72  ComTech Vol. 7 No. 1 March 2016: 67-74 Physical Design Physical design is a process of implementing the logical design physically and creates table in a DBMS that has been set by using Data Definition Language (DDL) and also to create a report by using Data Manipulation Language (DML). Raw Materials Mutation Reports and Production Mutation Report are included within DJBC report. The entitiy of the report is not made , but the physical design is made by creating applications where there is Query to display it. SELECT bahanBaku.BbID, bahanBaku.BbNama, bahanBaku.Satuan, bbMasuk.Jumlah_BB_Masuk AS Saldo_Awal, (SUM(bbMasuk.Jumlah_BB_Masuk)- bbMasuk.Jumlah_BB_Masuk) AS Pemasukan, bbk.Digunakan AS Pengeluaran, SUM(bbMasuk.Jumlah_BB_Masuk)-bbk.Digunakan AS Saldo_Buku, bahanBaku.Stockopname, (SUM(bbMasuk.Jumlah_BB_Masuk)-bbk.Digunakan)- bahanBaku.Stockopname AS Penyesuaian, ABS(bahanBaku.Stockopname- (SUM(bbMasuk.Jumlah_BB_Masuk)-bbk.Digunakan))AS Selisih FROM bahanBaku LEFT JOIN bbMasuk ON bahanBaku.BbID=bbMasuk.BbID_BB_Masuk LEFT JOIN ( SELECT bbKeluar.BbID_BB_Keluar, SUM(bbKeluar.Digunakan) AS Digunakan FROM bbKeluar) GROUP BY bbKeluar.BbID_BB_Keluar) AS bbk ON bahanBaku.BbID=bbk.BbID_BB_Keluar GROUP BY bahanBaku.BbID Companies throughout Indonesia can build its IT Inventory ERD based on this research. One design that has been using the ERD in this study can be seen in figure 4. As it can be seen, user can login before they manage the database. After the login is success, user can choose or click the icon base on the action that the user wants (Figure 5). User can input data as it can be seen on Figure 6 or user can view and monitor all the data on the table that already being inputted before (Figure 7). Figure 4 Example of Login Display Analysis and Database Design … (Novan Zulkarnain) 73  Figure 5 Example of Home Display Figure 6 Display While Inputting Raw Materials Figure 7 Examples of Mutation Reports of Production Results 74  ComTech Vol. 7 No. 1 March 2016: 67-74 CONCLUSIONS Based on the research results, it can be concluded that: (1) ERD has been in accordance with Regulation Report of the Director General of Customs and Excise, so companies - companies can use in designing IT Inventory. (2) ERD has been successfully used in three companies: PT. Taehan Textile, PT. Windu Eka and PT. Prima Makmur. It also produces reports in accordance with the purposes of DJBC reporting. REFERENCES Connolly, T., & Carolyn B. (2005). Database Systems: a practical approach to design, implementation and management (4th ed.). Database Place: Wesley. Kementerian Keuangan Republik Indonesia. (2014). Peraturan Direktur Jenderal Bea dan Cukai, Nomor PER-09/BC/2014 tentang Penerapan Sistem Informasi Persediaan Berbasis Komputer pada Perusahaan Pengguna Fasilitas Pembebasan, Pengembalian, dan Tempat Penimbunan Berikat, serta Kerahasiaan Data dan/atau Informasi oleh Direktorat Jenderal Bea dan Cukai. Jakarta. Kementerian Keuangan Republik Indonesia. (2011). Peraturan Keuangan Republik Indonesia, Nomor 177/PMK.04/2011. Jakarta. Lee, J., Cho, R. K., & Paik, S. (2016). Supply chain coordination in vendor-managed inventory systems with stockout-cost sharing under limited storage capacity. European Journal of Operational Research, 248(1), 95-106. Pons, D. (2010). System model of production inventory control. International Journal of Manufacturing Technology and Management (IJMTM), 20(1-4), 120 - 155. Shafer, S. M. (2005). Web Standards Programmer’s Reference: HTML, CSS, Javascript, Perl, Phyton and PHP (1st ed.). Canada: Wiley Publishing Inc. Sheldon, R., & Moes, G. (2005). Beginning MySQL (1st ed.). Canada: Wiley Publishing Inc.