ERP extract data

Extract ERP data6.xlsm에 대한 설명입니다.

사용 관련 사항

ERP는 php 모듈로 해당 화면을 만들 수 있으나, Dolibarr 모듈 가이드를 따라야 하기 때문에, 우선 업무적 편의를 위해서 만든 간이 프로그램이다.

Dolibarr php 가이드에 따른 개발

웹으로 직접 개발하는 경우는 다음의 영향이다.

1. Dolibarr 개발자 가이드 - 디렉토리 위치, DB 설명 참조한다.
2. 모듈로 개발하는 것은 나중에 업그레이드가 되더라도 영향을 받지 않기 위해서 이다.
3. ERP 사이트에서 완료하는 것을 기준으로 해야한다.

작업의 설명

매월 1회 경리월보를 작성하여 제출한다.

항목은 다음의 3가지가 중요하다.

1. 급여
2. 비용(Expense) - 영수증
3. 자산

비용의 경우, ERP에 개별적으로 입력하고, 영수증을 증빙한다.

본사 및 현지 회계 법인에 1) 급여, 2) 비용 (영수증)을 제출해야 한다.

• 업무의 변화

1. ERP(23.09)를 도입하여, 모든 조회와 기록을 ERP에 기록한다.
2. 따라서, ERP 데이터가 무결성을 확보하여, 이를 기준으로 모든 데이터를 만든다.

특히, ERP는 검사(validation)과 결재(approval)을 거치기 때문에 데이터 무결성이 확보된다.

회계 담당자의 업무, 검사, accouting 작업(분개장 작성)을 제대로 해야지만, 모든 데이터의 무결성이 확보된다.

• 영수증 처리

가장 시간을 많이 소모하게 되는 것이 영수증 개별 표시와 사진 자료 첨부이다.

이러한 작업을 줄이기 위해서, 입력 단계 부터 ERP에 입력하고, 해당 데이터를 이용해서 경리월보를 작성 (혹은 개발 후, 조회) 하여 사용 하도록 한다.

엑셀 자동화

경리월보에서 중요한 것은 다음과 같다.

1) 경비 내역과 증빙(영수증)
2) 프로젝트 개별비(직접비, direct cost)
3) 자산 현황 - * 별도 관리 (ERP 자산 resource)

데이터 추출 엑셀의 역할은 경리월보 보고를 위한 작성에서 복사해서 바로 사용할 수 있도록 구성한 메타 파일이다. 복사해서 붙여넣기로 사용하면 되도록 구성하는 것이다. 이것은 임시적인 구성이며, ERP에서는 모든 담당자와 다른 프로그램이 ERP에서 직접 access해서 데이터를 확인하는 것이 목적이다.

현장에서도 1회 작성을 통해서(ERP) 모든 데이터를 이를 기준으로 재사용하기 때문에, 별도로 재작성하는 것을 방지한다. 즉, ERP 데이터를 입력하고(ER), 회계담당자와 PM이 결재를 통해서, 자료의 정합성을 확인하면, 이후 데이터 활용은 오류가 없다.

엑셀 sheet의 구성

사용하는 sheet설명

1. LOGIN_ERP sheet - ID/PW, 조회기간, 프로젝트를 선택하여, ERP web에 자동으로 접속한다.
2. ERP_OUTPUT sheet - (1)의 조건에 따라, raw 데이터를 취득한다. (ERP는 비용전표 기준(여러개 영수증)이기 때문에, 다시 영수증 단위로 searching)
3. Monthly_Report sheet - ERP 데이터를 경리월보(엑셀, 월 1회 보고) 양식에 맞게 구성한다.
4. PJT COST sheet - 프로젝트 직접비(Direct Cost) 후보 항목(?)을 추출

기본 데이터 in Monthly_Report sheet

엑셀 자동화를 통해서 얻을 수 있는 자료는 다음과 같다.

1. 날짜 (영수증) - 사용자가 작성 날짜, 회계 담당자는 결재 중, 영수증 날짜와 사용자 날짜를 반드시 확인해야 한다.
2. 경리월보 기준 비용 계정(COA) - 한글, 영어 분류표
3. 금액 (현지통화)
4. 증빙 - 영수증 파일 이름 (ERP에 upload되면, 파일명에 "비용 전표 이름" +  "파일명" 으로 ERP에 기록된다.
5. ERP 링크 (기본 브라우저에 로그인 상태이어야 함) - 파일을 다운로드 할 필요 없이 웹페이지에서 직접 확인

사용자는 상기의 데이터를 복사하여, 본사 보고용 경리월보 파일에 copy & paste로 붙여 넣는다.

프로젝트 직접비(Direct Cost) : PJT COST sheet

# 운영 방식 (임시)
비용전표(ER, Expense Report)작성시 적요(comment)란에 pjt_cost라는 단어를 사용한 경우, 해당 내용은 프로젝트 직접비로 구분

직접비 항목은 다음 6가지 경우이다. 단, 운반비는 부가세를 포함해서 계산되므로, 같은 코드를 사용한다.

항목	ERP 코드	경리월보명칭	비고
제조지급수수료(용역), 교육관련 - 제조교육훈련비(기타)	617	교육훈련비
제조지급수수료(용역)	6112	검정(검수)수수료
제조지급수수료(기타)	678	기타
제조운반비 - 현지 물품대에 대한 VAT	624	운반비
제조소모품비(기타) - 안전 공구	606	기타
제조운반비 - 현장인도비용	624	(없음)	경리월보 항목 없음

• 상기 내용은 경리월보에 지정되어 있지 않고, 각 프로젝트 별로 코드 구분이 아닌, 개별 기록으로 제공
• ERP에서는 이러한 문제를 해결하기 위해, 코드를 할당하여 사용하나, 현지 세금 신고등의 문제가 있어, 다른 구분 방법으로 확정하고자 함

주의 사항

경리월보 기타 입력 사항은 PM의 권한이라, 비용과 증빙에 관련된 부분만 발췌하여 사용한다.

나머지 정보는 추후 php개발시, 현장의견을 반영하여 확정한다.

기타

추후 php로 작성하는 화면은 프로젝트와 취득 영수증 기반으로 화면 구성이 된다. (영수증 건수 rows)

ERP에서는 결재가 완료된 문서 기준으로 무결성이 확보되었다고 가정하기 때문에, ERP 입력 부터 검사, 결재까지 과정이 중요하다.

• 영수증 파일

영수증 파일은 ERP에서 항상 조회 가능하므로, 별도로 영수증 사진 파일을 매번 전송하지 않는다.

경리월보에는 ERP에 기록된 영수증 파일 주소를 제공하여, 경리월보에서 바로 클릭해서 사진 파일을 확인한다.

• 특히, 이 부분은 추후 AI를 적용할 때, AI가 직접 accesss하여 판단할 수 있도록 하기 위해서 이다.

파일 링크를 사용하던지, 파일을 별도로 저장하던지 방법은 상황에 따라 선택 자유이다.

개발 관련 사항

나중에 수정 보완 작업을 위한 내용 정리이다.

• 가장 큰 차이

ERP는 비용전표 (Expense Report) 단위이다. 즉, 1개의 비용전표에 여러 개의 영수증을 첨부할 수 있다. 따라서, 비용전표를 조회해서는 하위 영수증 까지는 직접 비용전표를 조회하여야 한다.

그러나, 경리월보는 기준이 영수증 단위라서, 증빙(영수증) 검사부터 순차적으로 진행한다.

엑셀 자동화 파일(ERP extract data6.xlsm)은 ERP의 2단계 조회를 영수증 기준으로 다시 재배치 하고, 영수증 파일명(ERP에 upload 되어 있으므로)을 제공하여, 경리월보에 첨부할 수 있도록 하는 방향이다.

• LOGIN_ERP sheet 관련

앞에서 엑셀 시트 구성을 참조한다.

흐름

1. 버튼 : Sub btnFocus_Click() 을 호출한다.
2. Sub WebLoginAndNavigate() 으로 IE를 기동시킨다.
3. 주소 만들기 Function GetURL() As String - 여러개 프로젝트 조회하는 주소 구성
4. Search Sub ReadERandRecord(IE, tmpID2)
   1. 탐색 경로 만들기 - 비용전표 리스트 확인, 비용전표별로 다시 탐색
   2. ERP_OUTPUT 만듬
5. 경리월보 형태로 ERP데이터 수정Sub CopyERPDataToMonthlyReport()

• 각 모듈 역할

btnFocus_Click()

화면에 표시 하기 위한 단순 시작접, 모든 ERP 관련 실행은 다음 프로시저에서 시작된다.

• 이전 다른 자동로그인과 구분하기 위함임

프로시저 콜

Call WebLoginAndNavigate    'Module 2에서 호출하기

WebLoginAndNavigate

main과 같은 역할로 시작과 끝을 나타낸다.

1. LOGIN_ERP sheet에 기록된 내용을 읽는다
2. Function으로 주소 정보를 만든다. (사용자, 기간, 프로젝트들)
3. 로그인 (읽어온 정보 기반), 웹화면의 클릭 수행
4. ReadTableAndRecord(IE) - 사용중인 IE 핸들러를 넘겨서, 만든 주소를 통해서 접속한다,(RAW)
5. LoopThroughERP_ERN(IE) - 사용한 비용전표 리스트를 뽑는다.
6. CopyERPDataToMonthlyReport - 경리월보 양식으로 자료를 만든다.
7. IE를 끄고, 종료

LOGIN_ERP 읽어오기

사용자 ID/PW, 기간, 프로젝트 등의 항목을 읽어 온다.

Set ws1 = ThisWorkbook.Sheets("LOGIN_ERP") ' LOGIN_ERP Sheet를 사용한다.
....
TargetURL = GetURL  'GetURL은 funtion으로 요청한 프로젝트 리스트로 주소를 만든다.

• Function GetURL() As String

리턴 값은 String으로 넘겨 준다. LOGIN_ERP Sheet의 자료를 이용하므로 별도의 파라미터를 받지 않는다. (괄호) 안의 값이 필요없다.

작업 사항

1. 시작날짜 분리: 년, 월, 일 - 양식에 주의 한다. '##' 형태가 아니라 '#'를 사용한다
2. 끝날짜 분리: 년, 월, 일
3. 각 프로젝트 회계장부 - "&search_ledger_code[]=" & pjt_CODE 형식이다.

리턴하기
GetURL = str_URL   'Function GetURL() As String 으로 선언되어서, GetURL은 String으로 리턴된다.

• IE 기동시키기

Edge 부터는 Chrome과 같은 Selenium 모델로 호출 형태가 달라진다.

VBA는 기본 브라우저로 IE: Internet Explorer를 사용한다. 일부 기능이 Edge와 다르게 사용되지만 대부분은 지원된다. 만약 문제가 생기면, Chrome이나 Edge로 변경한다.

ID/PW를 가지고,

IE.document.getElementById("login-submit-wrapper").getElementsByTagName("input")(0).Click

으로 ERP 사이트에 로그인 한다.

로그인 후에는 타겟 주소로 이동한다

    IE.Navigate TargetURL

Sub ReadTableAndRecord(IE)

ERP RAW값을 추출하고, 비용전표 번호를 별도로 추출한다.

1. ReadTableAndRecord() 수행 후에
2. RemoveDuplicatesFromERP_RAW() 호출하여 서브루틴을 끝낸다. (Sub WebLoginAndNavigate()으로 돌아감)

주의: ReadTableXXXX 서브루틴이 많으므로, 이름 주의 할 것

역할 - RAW값을 ERP RAW sheet에 작성한다. 이것은 ERP 페이지 자료 가공을 안한 상태

ERP_RAW sheet

분개장 메뉴에서 읽어온 전표 RAW data이다. (ERP 화면과 동일)

421과 같은 비용전표(ER)에 있지 않은 회계정보 자료까지 있다.
sub Ledger 이름은 421인 경우 발췌하는 것이 좋을 듯...

ERP_ERN sheet

비용전표 번호를 발췌한다. LoopThroughERP_ERN()을 호출한다. 여기서 Sub ReadERandRecord(IE, tmpID2)도 호출함

여러개의 비용전표 번호를 순서대로 찾아 적는다.

이것은 추가로 조회할 주소를 만드는데 사용된다.
LoopThroughERP_ERN()은 다시 ReadERandRecord(IE, tmpID2)을 호출함
* 이게 아마 function call로 하려다가 에러가 계속나서 (버그?) 다시 그냥 sub로 바꿈

ReadERandRecord(IE, tmpID2)

여기서 "ERP_OUTPUT" sheet에 자료를 적는다.

호출의 호출임...주의

CopyERPDataToMonthlyReport()

ERP_OUT sheet의 데이터 내용을 경리월보 형식의 Monthly_Report sheet로 옮김

• 대상 sheet 사용

' 소스 시트 설정
   Set wsSource = ThisWorkbook.Sheets("ERP_OUTPUT")
       
' 대상 시트 확인 및 필요시 생성
   Set wsTarget = ThisWorkbook.Sheets("Monthly_Report")

• 회사 COA 규정 반영 재 수정

회사 규정은 master_COA (sheet)의 테이블에 기록되어 있다.

해당 파일은 hidden 처리 되었음 (ERP_RAW, ERP_ERN)로 모두 숨겨져 있음

TW ERP에서 사용중인 CoA