Pengembangan Sistem Reporting Purchasing Berbasis Python dan SQL pada PT. X
Abstract
The Purchasing Division of a pharmaceutical company plays a strategic role in inventory control and distribution fulfillment across retail outlets. Previously, the Key Performance Indicator (KPI) reporting system at PT. X was conducted manually using spreadsheets, requiring up to two working days and being prone to calculation errors that affected employee incentive payments. This study aims to design and implement a web-based KPI reporting system using Python, the Django framework, SQL Server with stored procedures, and the OpenPyXL library. The system was developed using the Waterfall methodology with a modified prototyping approach. Eighteen KPI indicators were classified into four categories: procurement efficiency, inventory and expiration management, distribution planning, and data compliance and negotiation. Data processing employed an SQL Server stored-procedure-based ETL pipeline integrated with Pandas and NumPy. The system reduced report generation time from two working days to less than one hour while eliminating incentive calculation errors.
Keywords: Key performance indicator; Reporting automation; ETL pipeline; Purchasing; information systems.
Abstrak
Divisi purchasing pada perusahaan farmasi menjalankan peran strategis dalam pengendalian stok dan pemenuhan distribusi ke jaringan outlet. Sistem pelaporan Key Performance Indicator (KPI) PT. X sebelumnya dilakukan secara manual menggunakan spreadsheet dengan dua hari kerja dan rentan terhadap kesalahan kalkulasi yang berimplikasi pada pembayaran insentif karyawan. Penelitian ini bertujuan merancang dan mengimplementasikan sistem pelaporan KPI berbasis web menggunakan Python dan framework Django, SQL server dengan stored procedure, dan library openyxl. Sistem dikembangkan menggunakan metode waterfall dan dimodifikasi dengan pendekatan prototipe. 18 indikator KPI dikelompokkan kedalam empat kategori: efisiensi pengadaan (KPI 1), manajemen stok, dan kedaluwarsa (KPI 2), perencanaan distribusi (KPI 3), serta kepatuhan data dan negosiasi (KPI 4). Pengolahan data dilakukan melalui pipeline ETL berbasis SQL Server Stored Procedure dengan mengintegrasikan data heterogen, kemudian ditransformasi menggunakan pandas dan NumPy. Sistem berhasil mereduksi waktu penyusunan laporan dari rata-rata dua hari kerja menjadi kurang dari satu jam dan mengeliminasi kesalahan kalkulasi insentif.
Keywords
References
J. Shen et al., “Management of drug supply chain information based on ‘artificial intelligence + vendor managed inventory’ in China: perspective based on a case study,” Front. Pharmacol., vol. 15, p. 1373642, Jul. 2024, doi: 10.3389/fphar.2024.1373642.
F. Rasool, M. Greco, and M. Grimaldi, “Digital supply chain performance metrics: a literature review,” Meas. Bus. Excell., vol. 26, no. 1, pp. 23–38, 2022, doi: 10.1108/MBE-11-2020-0147.
A. Al-Rawahi, A. Al-Harrasi, and A. Al-Balushi, “Key performance indicators for procurement process: A framework development using analytical hierarchy process,” in Proc. IEEE Int. Conf. Industrial Engineering and Engineering Management (IEEM), Manama, Bahrain, Dec. 2024, pp. 1184–1188, doi: 10.1109/IEEM59794.2024.10459542.
K. Govindan, D. Kannan, T. B. Jørgensen, and T. S. Nielsen, “Supply Chain 4.0 performance measurement: A systematic literature review, framework development, and empirical evidence,” Transp. Res. Part E Logist. Transp. Rev., vol. 164, p. 102725, Aug. 2022, doi: 10.1016/j.tre.2022.102725.
N. Zhao, J. Hong, and K. H. Lau, “Impact of supply chain digitalization on supply chain resilience and performance: A multi-mediation model,” Int. J. Prod. Econ., vol. 259, p. 108817, May 2023, doi: 10.1016/j.ijpe.2023.108817.
F. Rasool, M. Greco, and S. Strazzullo, “Understanding the future KPI needs for digital supply chain,” Supply Chain Forum An Int. J., vol. 25, no. 4, pp. 392–408, 2024, doi: 10.1080/16258312.2023.2253524.
V. I. Adebayo, P. O. Paul, and N. L. Eyo-Udo, “The role of data analysis and reporting in modern procurement: Enhancing decision-making and supplier management,” GSC Adv. Res. Rev., vol. 20, no. 1, pp. 88–97, 2024, doi: 10.30574/gscarr.2024.20.1.0246.
P.-L. Poon, M. F. Lau, Y. T. Yu, and S.-F. Tang, “Spreadsheet quality assurance: a literature review,” Front. Comput. Sci., vol. 18, no. 2, p. 182203, Jan. 2024, doi: 10.1007/s11704-023-2384-6.
A. Y. Anggie, J. T. Beng, and Wasino, “Perancangan aplikasi berbasis web untuk pemesanan produk eksterior dan interior pada bengkel las Krisna,” Jurnal Ilmu Komputer dan Sistem Informasi, vol. 11, no. 1, pp. 1–7, Jun. 2023, doi: 10.24912/jiksi.v11i1.24087.
A. Leovin, J. Tji Beng, and E. Dewayani, “Business to business e-commerce sales system using web-based quotation: A case study on company X,” in IOP Conference Series: Materials Science and Engineering, IOP Publishing, Dec. 2020, p. 12156. doi: 10.1088/1757-899X/1007/1/012156.
V. H. Wangi, J. Tji Beng, and Wasino, “Start to end: Recommended travel routes based on tourist preference,” in IOP Conference Series: Materials Science and Engineering, IOP Publishing, Jul. 2020, p. 12163. doi: 10.1088/1757-899X/852/1/012163.
K. Manoj and N. Rainu, “Role of Python in Rapid Web Application Development Using Django,” in Proc. Int. Conf. Innovative Computing and Communication (ICICC 2024), Singapore: Springer Nature Singapore, 2024, pp. 551–560, doi: 10.2139/ssrn.4751833.
R. Kimball and M. Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed. Indianapolis, IN: Wiley, 2013.
W. Chen and S. Ahmmed, “Analysis of Python web development applications based on the Django framework,” in Proc. SPIE 13181, Third International Conference on Electronic Information Engineering, Big Data, and Computer Technology (EIBDCT 2024), Wuhan, China: SPIE, 2024, Art. no. 131816C, doi: 10.1117/12.3031411.
W. McKinney, Python for Data Analysis: Data Wrangling with pandas, NumPy, and Jupyter, 3rd ed. Sebastopol, CA: O’Reilly Media, 2022.
F. Lusiana, J. Tji Beng, and Wasino, “Grouping of tourism objects using geotagged photo with hierarchical clustering method in Bantul and Sleman,” in IOP Conference Series: Materials Science and Engineering, IOP Publishing, Jul. 2020, p. 12166. doi: 10.1088/1757-899X/852/1/012166.
H. Jing and Y. Fan, “Digital transformation, supply chain integration and supply chain performance: Evidence from Chinese manufacturing listed firms,” SAGE Open, vol. 14, no. 3, pp. 1–15, Sep. 2024, doi: 10.1177/21582440241281616.
J. He, M. Fan, and Y. Fan, “Digital transformation and supply chain efficiency improvement: An empirical study from A-share listed companies in China,” PLoS One, vol. 19, no. 4, p. e0302133, Apr. 2024, doi: 10.1371/journal.pone.0302133.
K. L. Lee, C. X. Teong, H. M. Alzoubi, M. T. Alshurideh, M. El Khatib, and S. M. Al-Gharaibeh, “Digital supply chain transformation: The role of smart technologies on operational performance in manufacturing industry,” SAGE Open, vol. 14, no. 1, 2024, doi: 10.1177/18479790241234986.
N. O. Mandala, I. R. Ayoyi, and S. K. Too, “The impact of information technology adoption on efficiency and transparency in public procurement processes in Kenya,” Eur. Sci. J., vol. 20, no. 13, p. 167, 2024, doi: 10.19044/esj.2024.v20n13p167.
R. C. Martin, Clean Architecture: A Craftsman’s Guide to Software Structure and Design. Upper Saddle River, NJ: Prentice Hall, 2018.
A. Saravanos and M. X. Curinga, “Simulating the Software Development Lifecycle: The Waterfall Model,” Appl. Syst. Innov., vol. 6, no. 6, p. 108, Nov. 2023, doi: 10.3390/asi6060108.
I. K. Kirpitsas and T. P. Pachidis, “Evolution towards Hybrid Software Development Methods and Information Systems Audit Challenges,” Software, vol. 1, no. 3, pp. 316–363, Aug. 2022, doi: 10.3390/software1030015.
T. D. Capote, “A Comparative Study of Black Box and White Box Testing Techniques in Modern Software Development,” Front. Eng. Technol., vol. 5, no. 1, pp. 1–7, 2023, [Online]. Available: https://iaeme.com/Home/article_id/FET_05_01_001
How To Cite This :
Refbacks
- There are currently no refbacks.
