내부회계관리 운영감사 끝나고 퇴사 3주전에 만들기 시작한 프로그램..... 입사하여 처음 파워빌더를 시작할떄 프로그램을 어떻게 만들지 막막했는데... 퇴사 직전에는 나름 복잡한 프로그램까지 만든거같다.
얼마 다루지도 않은 파워빌더로 뚝딱뚝딱 어찌저찌 만들어서 현업분들 사용하시라고 ERP 개발하는거보니 파워빌더가 왜 생산성 하나는 인정받은건지 알꺼같다. CS 위주라서 웹 시대에 뒤쳐져서 사장된게 아쉬울뿐... 앞으로 살면서 내가 다룰일이 있을지는 의문이다.
Divide and Conquer 방법으로 우선 임시테이블에 영업관리팀의 엑셀자료를 업로드 시켜준후 데이터 검증을 거쳐
최종적으로 수금전표를 생성하게 만들어주었다. (2개의 프로그램)
임시테이블에 엑셀자료 업로드 시키는 프로그램
DW 내용
SELECT A.COLLECT_DATE,
A.KEY_NO,
A.ROW_NO,
A.ACCOUNT_NO,
A.SALES_CODE,
A.SALES_NAME,
A.EMP_NO,
A.SALES_EMP_NO,
B.EMP_NO AS S_EMP_NO,
A.SALES_DEPT_CODE,
B.DEPT_CODE AS S_DEPT_NO,
A.EMP_NAME,
A.COLLECT_AMT,
A.BANK_CODE,
(SELECT KORFNM FROM SYSCODEDET WHERE CODETP = 'S029' AND CODEID = A.BANK_CODE) AS BANK_NAME,
A.PROC_YN,
A.FIRSTEMP,
A.FIRSTYMD,
A.LASTEMP,
A.LASTYMD,
'0' AS CHK
FROM ACCOUNT_EXCEL A,
SALES_1 B
WHERE A.COLLECT_DATE BETWEEN :as_frdate AND :as_todate
AND PROC_YN LIKE :as_procyn
AND A.EMP_NO = B.EMP_NO(+)
AND A.SALES_CODE = B.SALES_CODE(+)
ORDER BY A.KEY_NOKEY 스크립트 내용
Datetime ld_today
Boolean lb_eof
Double ld_amt , ld_fee , ld_real
Long ll_rtn , i, ll_upper, ll_row , ll_newrow, ll_seqno , ll_count
Integer ls_row_no
String ls_collect_date, ls_path, ls_file[], ls_key, ls_account_no, ls_sales_code, ls_sales_name, ls_emp_no, ls_sales_emp_no, ls_sales_dept_code, ls_emp_name, ls_bank_code, ls_proc_yn, ls_bank_name, ls_filenm, ls_dt
Any la_data
Double ld_vat
OleObject ole_excel
ll_rtn = GetFileOpenName ("보통예금자료", ls_path, ls_file, "XLSX", "엑셀파일(*.xlsx), *.xlsx")
IF ll_rtn = 0 THEN RETURN
ll_upper = UpperBound(ls_file)
ole_excel = CREATE OleObject
ll_rtn = ole_excel.ConnectToNewObject("Excel.Application")
IF ll_rtn <> 0 THEN
MessageBox("확인", "EXCEL OLE 오브젝트 연결 중 오류가 발생하였습니다.(" + String(ll_rtn) + ")")
DESTROY ole_excel
RETURN
END IF
SELECT SYSDATE INTO :ld_today FROM DUAL;
SetPointer(HourGlass!)
dw_1.SetRedraw(FALSE)
dw_1.Reset()
FOR i = 1 TO ll_upper
ll_seqno = 0
IF ll_upper = 1 THEN
ls_filenm = ls_path
ELSE
ls_filenm = ls_path + '\' + ls_file[i]
END IF
ole_excel.Application.WorkBooks.Open(ls_filenm)
ll_row = 2
DO WHILE NOT lb_eof
ls_key = String(ole_excel.Application.Cells(ll_row, 1).Value) //KEY
IF IsNull(ls_key) or ls_key ='' THEN EXIT
ls_row_no = Integer(ole_excel.Application.Cells(ll_row, 2).Value) // 행 번호
ls_account_no = String(ole_excel.Application.Cells(ll_row, 3).Value) // 계좌번호
ls_sales_code = String(ole_excel.Application.Cells(ll_row, 4).Value) // 거래처코드
ls_emp_name = String(ole_excel.Application.Cells(ll_row, 5).Value) // 담당자 이름
ls_emp_no = String(ole_excel.Application.Cells(ll_row, 6).Value) // 담당자 사번
ls_sales_name = String(ole_excel.Application.Cells(ll_row, 7).Value) // 거래처명
ld_amt = Double(ole_excel.Application.Cells(ll_row, 8).Value) // 수금액
ls_collect_date = String(ole_excel.Application.Cells(ll_row, 9).Value) // 수금일자
ls_dt = Left(ls_collect_date, 4) + '-' + Mid(ls_collect_date, 5, 2) + '-' + Mid(ls_collect_date, 7 , 2)//수금일자
ls_bank_code = String(ole_excel.Application.Cells(ll_row, 10).Value) // 은행코드
ls_proc_yn = 'N'
IF ll_count > 0 THEN
MessageBox("확인", "이미 등록된 보통예금 자료입니다. 확인하십시오.~r~n" + &
"승인일자 : " + String(ls_collect_date, '@@@@-@@-@@') + "~r~n" + &
"은행코드 : " + ls_bank_code + "~r~n" + &
"계좌번호 : " + ls_account_no + "~r~n" + &
"수 금 액 : " + String(ld_amt, '#,##0'))
dw_1.SetRedraw(TRUE)
ole_excel.Application.Quit()
ole_excel.DisConnectObject()
RETURN
END IF
ll_newrow = dw_1.InsertRow(0)
SELECT EMP_NO, DEPT_CODE
INTO :ls_sales_emp_no, :ls_sales_dept_code
FROM SALES_1
WHERE SALES_CODE = :ls_sales_code
AND EMP_NO = : ls_emp_no
USING SQLCA;
IF SQLCA.SQLCODE <> 0 THEN
ls_sales_emp_no = ''
ls_sales_dept_code = ''
END IF
SELECT KORFNM
INTO :ls_bank_name
FROM SYSCODEDET
WHERE CODETP = 'S029'
AND CODEID = :ls_bank_code;
dw_1.SetItem(ll_newrow, 'ROW_NO' , ls_row_no)
dw_1.SetItem(ll_newrow, 'KEY_NO' , ls_key )
dw_1.SetItem(ll_newrow, 'COLLECT_DATE' , Date(ls_dt))
dw_1.SetItem(ll_newrow, 'BANK_CODE', ls_bank_code)
dw_1.SetItem(ll_newrow, 'BANK_NAME' , ls_bank_name )
dw_1.SetItem(ll_newrow, 'ACCOUNT_NO' , ls_account_no)
dw_1.SetItem(ll_newrow, 'COLLECT_AMT' , ld_amt )
dw_1.SetItem(ll_newrow, 'SALES_CODE' , ls_sales_code)
dw_1.SetItem(ll_newrow, 'SALES_NAME' , ls_sales_name)
dw_1.SetItem(ll_newrow, 'sales_emp_no' , ls_sales_emp_no)
dw_1.SetItem(ll_newrow, 'sales_dept_code' , ls_sales_dept_code)
dw_1.SetItem(ll_newrow, 'EMP_NO' , ls_emp_no)
dw_1.SetItem(ll_newrow, 'EMP_NAME' , ls_emp_name)
dw_1.SetItem(ll_newrow, 'proc_yn' , ls_proc_yn)
dw_1.SetItem(ll_newrow, 'FIRSTEMP' , gsEmpcd )
dw_1.SetItem(ll_newrow, 'FIRSTYMD' , ld_today )
dw_1.SetItem(ll_newrow, 'LASTEMP' , gsEmpcd )
dw_1.SetItem(ll_newrow, 'LASTYMD' , ld_today )
ll_row++
LOOP
NEXT
ole_excel.Application.Quit
ole_excel.DisConnectObject()
DESTROY ole_excel
wf_save()
wf_retrieve()
dw_1.SetRedraw(TRUE)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
임시테이블에 생긴 데이터를 기반으로 보통예금 전표를 생성해주는 프로그램
DW 내용
SELECT A.COLLECT_DATE
,A.KEY_NO
,A.EMP_NO
,A.EMP_NAME
,A.SALES_DEPT_CODE
,(SELECT DIV_NAME FROM PER_DIVCD WHERE DIV_CODE=A.SALES_DEPT_CODE) AS SALES_DEPT_NAME
,A.SALES_CODE
,B.TRADE_NAME
,A.COLLECT_AMT
,A.PROC_YN
,C.CODEID, C.KORFNM, C.KORSNM
,B.FLOW
,B.INDIRECT_GUBUN
,A.FIRSTEMP
,A.FIRSTYMD
,A.LASTEMP
,A.LASTYMD
,'0' AS CHK
FROM ACCOUNT_EXCEL A,
SALES_1 B,
(SELECT CODEID, KORFNM, KORSNM
FROM SYSCODEDET
WHERE CODETP = 'S029'
) C
WHERE A.COLLECT_DATE BETWEEN :as_frdate AND :as_todate
AND PROC_YN = 'N'
AND A.SALES_CODE = B.SALES_CODE
AND A.EMP_NO = A.SALES_EMP_NO
AND B.SALES_FROM <> '4'
AND A.BANK_CODE = C.CODEID
ORDER BY A.COLLECT_DATE, A.EMP_NO
KEY 스크립트 내용
Long ll_row, ll_chk, ll_collect_amt, ll_collect_no, ll_cnt, ll_rowcnt, ll_seq, ll_line
String ls_collect_date, ls_approval_date, ls_sales_code, ls_bus_code, ls_dept_code, ls_emp_no, ls_flow, ls_indirect_gubun, ls_approval_dt, ls_emp_no_b, ls_key_no
String ls_branch_ho,ls_card_acc, ls_bank_code_card, ls_halbu_gubun, ls_end_flag
Date ld_approval_dt
// 회계전표생성
long ll_autono, ll_sum, ll_row1, ll_row2
long ll_amt, ll_roop, ll_count , ll_installamt
string ls_accdate, ls_account, ls_account_no, ls_salescode, ls_baldate, ls_baldatet, ls_deposit
string ls_drcode, ls_crcode, ls_dname, ls_cname, ls_enddate, ls_kwnname, ls_enddatet, ls_exid
string ls_billno1, ls_billno2, ls_dupno, ls_dupno2, ls_kumcode, ls_acccode, ls_deptcode, ls_user_dept
string ls_temp, ls_temp2, ls_emp, ls_sale , ls_drcode1 , ls_bank_code, ls_real_date
string ls_cardno, ls_approval_ymd, ls_card_end_ym
string ls_gubun, ls_card_gubun, ls_balhang, ls_baeseo
date ld_date
datetime ldt_ymd
string ls_codeid
//ys 회계 전표/전표상세/관리항목 insert 항목 정의
//
string ls_accsaup = '1' //회계단위 1:본사
string ls_accgb = '5' //전표구분 5:대체전표
//accac02t용
string ls_yn = 'N' //회계승인상태, 삭제여부
string ls_null = '' //회계부서, 승인자, 결재구분
int li_modcnt = 0 //수정건수
int li_prtcnt = 1 //출력건수
int li_totline
int li_noteterm = 0 //어음일수
string ls_exsystem = 'w_collect_insert15' //타시스템이체전표(window id)
string ls_exsign = '10' //영업확정구분
string ls_dr, ls_cr //차대구분
//accac03t용
string ls_docgbn = '00'
dw_1.AcceptText()
dw_cdt.AcceptText()
ls_approval_date = String(dw_cdt.GetItemDate(1, 'frdate_1'), 'YYYYMMDD')
ll_rowcnt = dw_1.RowCount()
FOR ll_row = 1 TO ll_rowcnt
ls_collect_date = String(dw_1.GetItemDateTime(ll_row, 'COLLECT_DATE'), 'YYYYMMDD')
ls_approval_dt = String(dw_1.GetItemDateTime(ll_row, 'COLLECT_DATE'), 'YYYY-MM-DD')
ls_key_no = dw_1.GetItemString(ll_row, 'KEY_NO')
ls_sales_code = dw_1.GetItemString(ll_row, 'SALES_CODE')
ls_dept_code = dw_1.GetItemString(ll_row, 'SALES_DEPT_CODE')
ll_collect_amt = dw_1.GetItemNumber(ll_row, 'COLLECT_AMT')
ls_indirect_gubun = dw_1.GetItemString(ll_row, 'indirect_gubun')
ls_emp_no = dw_1.GetItemString(ll_row, 'emp_no')
ls_flow = dw_1.GetItemString(ll_row, 'flow')
IF ll_row = 1 THEN
ls_emp_no_b = ls_emp_no
END IF
IF ll_row > 1 THEN
ls_emp_no_b = dw_1.GetItemString((ll_row - 1), 'emp_no')
END IF
SELECT TRIM(COLLECT_YN)
INTO :ls_end_flag
FROM END_MANAGER
WHERE END_DATE = substr(:ls_collect_date,1,6);
IF ls_end_flag = 'Y' THEN
MessageBox("확인", '해당월은 마감되었습니다!')
Return
END IF
// 수금일자 가져옴
//dw_data.accepttext()
ls_accdate = ls_collect_date
ls_deptcode = gsdeptcd
// 귀속부서 추가(2011.04.25)
//dw_dept.accepttext()
ls_user_dept = ls_dept_code
// 회계 - 채번 테이블
// 회계단위 : 1, 전표구분 : 5, 비교일자 : 수금일자
//ll_autono = 0
IF (ll_row = 1) THEN
select autono
into :ll_autono
from accac01t
where accsaup = :ls_accsaup and accgb = :ls_accgb and yyyymmdd = :ls_accdate;
// 해당 채번에 대한 정보가 없으면 insert (ACCGB - 1:지출품의서 5:대체전표)
if sqlca.sqlcode = 100 then
ll_autono = 1
//'accgb = '5'
insert into accac01t(accsaup, accgb, yyyymmdd, autono)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono);
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 채번 테이블] ' + gf_msg_call('Z035')
return
else
commit using sqlca ;
end if
// 있으면 update
elseif sqlca.sqlcode = 0 then
if ll_autono = 0 or isnull(ll_autono) then
ll_autono = 1
else
ll_autono ++
end if
update accac01T
set autono = :ll_autono
where accsaup = :ls_accsaup and accgb = :ls_accgb and yyyymmdd = :ls_accdate;
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 채번 테이블] ' + gf_msg_call('Z035')
return
else
commit using sqlca ;
end if
else
rollback using sqlca;
sle_msg.text = '[회계 채번 테이블] ' + gf_msg_call('Z035')
return
end if
END IF //ll_row = 1
IF ((ll_row > 1) and (ls_emp_no <> ls_emp_no_b)) THEN //담당자가 다를경우 새로운 전표번호 생성
select autono
into :ll_autono
from accac01t
where accsaup = :ls_accsaup and accgb = :ls_accgb and yyyymmdd = :ls_accdate;
// 해당 채번에 대한 정보가 없으면 insert (ACCGB - 1:지출품의서 5:대체전표)
if sqlca.sqlcode = 100 then
ll_autono = 1
//'accgb = '5'
insert into accac01t(accsaup, accgb, yyyymmdd, autono)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono);
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 채번 테이블] ' + gf_msg_call('Z035')
return
else
commit using sqlca ;
end if
// 있으면 update
elseif sqlca.sqlcode = 0 then
if ll_autono = 0 or isnull(ll_autono) then
ll_autono = 1
else
ll_autono ++
end if
update accac01T
set autono = :ll_autono
where accsaup = :ls_accsaup and accgb = :ls_accgb and yyyymmdd = :ls_accdate;
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 채번 테이블] ' + gf_msg_call('Z035')
return
else
commit using sqlca ;
end if
else
rollback using sqlca;
sle_msg.text = '[회계 채번 테이블] ' + gf_msg_call('Z035')
return
end if
END IF //(ll_row > 1) and (ls_emp_no <> ls_emp_no_b)
// 회계 - 회계전표
ld_date = date(mid(ls_accdate, 1, 4) + '-' + mid(ls_accdate, 5, 2) + '-' + mid(ls_accdate, 7, 2))
ldt_ymd = gf_sys_datetime()
ls_exid = '01' + ls_accdate + string(ll_autono, '0000')
IF ((ll_row > 1) and (ls_emp_no = ls_emp_no_b)) THEN
ll_sum = ll_sum + ll_collect_amt
ll_row1 += 1
ll_seq += 1
END IF
IF ((ll_row = 1) or (ls_emp_no <> ls_emp_no_b)) THEN
ll_sum = ll_collect_amt
ll_row1 = 1
ll_seq = 1
END IF
IF ((ll_row = 1) OR (ls_emp_no <> ls_emp_no_b)) THEN
insert into accac02T(accsaup, accgb, accym, accno, accdate,
insdept, inspsn, signyn, accdept,
accpsn, modcnt, prtcnt, totline, drtotamt,
crtotamt, setkind, noteterm, delyn,
exsystem, exkey, extrtime,
firstemp, firstymd, lastemp, lastymd, exsign)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono, :ld_date,
:gsdeptcd, :gsempcd, :ls_yn, :ls_null,
:ls_null, :li_modcnt,:li_prtcnt, :li_totline, :ll_sum,
:ll_sum, :ls_null, :li_noteterm, :ls_yn,
'w_collect_insert15', :ls_exid, :ldt_ymd,
:gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd, :ls_exsign);
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 회계전표 테이블] ' + gf_msg_call('Z035')
return
end if
END IF
IF (ll_row > 1) AND (ls_emp_no = ls_emp_no_b) THEN
UPDATE ACCAC02T
SET DRTOTAMT = :ll_sum
,CRTOTAMT = :ll_sum
WHERE ACCYM = :ls_accdate
AND ACCNO = :ll_autono
AND ACCSAUP = :ls_accsaup
AND ACCGB = :ls_accgb;
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 회계전표 테이블] ' + gf_msg_call('Z035')
return
end if
END IF
// 회계 - 회계전표(상세)
//계정코드 수금-상대계정(S058) 차변
SELECT KORSNM, 'D'
INTO :ls_drcode, :ls_dr
FROM SYSCODEDET
WHERE CODETP = 'S058'
AND ENGFNM = '15';
// 차변명 대신에 담당자/거래처코드/거래처명/입금표번호
ls_emp = ls_emp_no
ls_salescode = ls_sales_code
setnull(ls_temp)
select korname into :ls_temp from per_mast where empcd = :ls_emp;
ls_dname = trim(ls_temp)
//거래처명, 거래처코드
setnull(ls_temp)
setnull(ls_temp2)
ls_temp2 = ls_sales_code
// ls_temp2 = dw_1.GetItemString(ll_row, 'SALES_CODE')
select trade_name into :ls_temp from sales_1 where sales_code = :ls_temp2;
ls_dname = '[' + ls_dname + ' ' + trim(ls_temp) + '(' + ls_temp2 + ')' + ']'
ll_amt = ll_collect_amt
setnull(ls_crcode)
IF ll_row1 = 1 THEN
insert into accac03T(accsaup, accgb, accym, accno, lineno,
acccd, oppacc, drcr,
amt, retdept, sayongja, userdept, docgbn,
jugyo, accchul, firstemp, firstymd, lastemp, lastymd)
values ( :ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono, :ll_row1,
:ls_drcode, :ls_crcode, :ls_dr,
:ll_amt, :ls_null, :ls_null, :ls_user_dept, :ls_docgbn,
:ls_dname, :ls_null, :gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd);
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 회계전표( - 차변) 테이블] ' + gf_msg_call('Z035')
return
end if
END IF
IF ll_row1 > 1 THEN
ll_line = ll_row2 + 1
insert into accac03T(accsaup, accgb, accym, accno, lineno,
acccd, oppacc, drcr,
amt, retdept, sayongja, userdept, docgbn,
jugyo, accchul, firstemp, firstymd, lastemp, lastymd)
values ( :ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono, :ll_line,
:ls_drcode, :ls_crcode, :ls_dr,
:ll_amt, :ls_null, :ls_null, :ls_user_dept, :ls_docgbn,
:ls_dname, :ls_null, :gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd);
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 회계전표( - 차변) 테이블] ' + gf_msg_call('Z035')
return
end if
END IF
// 관리항목 SET /////////////////////////////////////////////////////////////////////////////////////
ls_salescode = ls_sales_code
ls_approval_ymd = ls_collect_date
ls_card_end_ym = '000000'
if isnull(ls_kwnname) then
ls_kwnname = ''
end if
//(001)거래처
if isnull(ls_kwnname) then
ls_kwnname = ''
end if
IF ll_row1 = 1 THEN
INSERT INTO ACCAC031T
VALUES (:ls_accsaup
,:ls_accgb
,:ls_accdate
,:ll_autono
,:ll_row1
,'175'
,:ls_deposit
,NULL
,:gsEmpcd
,SYSDATE
,NULL
,NULL
,99
)
USING SQLCA;
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류(입금표번호)", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
// 보통예금
ls_kumcode = trim(dw_1.getitemstring(ll_row, 'codeid'))
ls_acccode = trim(dw_1.getitemstring(ll_row, 'korsnm'))
// ll_dupno = dw_data.getitemnumber(ll_row, 'collect_seq')
ls_temp = ''
select korfnm, engfnm
into:ls_kwnname, :ls_temp
from syscodedet
where codetp = 'S029'
and codeid = :ls_kumcode;
if isnull(ls_kwnname) then
ls_kwnname = ''
end if
if isnull(ls_temp) then
ls_kumcode = ''
else
ls_kumcode = trim(ls_temp)
end if
setnull(ls_temp)
ls_temp = '004'
insert into accac031T (accsaup, accgb, accym, accno,
lineno, kwanri, kwanrinm, kwanricnt,
firstemp, firstymd, lastemp, lastymd, sortkey)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono,
:ll_row1, :ls_temp, :ls_kumcode, :ls_kwnname,
:gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd, (select F_SORTKEY('15', :ls_temp) from dual) );
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '은행(004)' + 'ls_kumcode====' + ls_kumcode
// sle_msg.text = '[회계 회계전표(관리항목) 테이블] ' + gf_msg_call('Z035')
return
end if
setnull(ls_temp)
ls_temp = '005'
insert into accac031T (accsaup, accgb, accym, accno,
lineno, kwanri, kwanrinm, kwanricnt,
firstemp, firstymd, lastemp, lastymd, sortkey)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono,
:ll_row1, :ls_temp, :ls_acccode, :ls_kwnname,
:gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd, (select F_SORTKEY('15', :ls_temp) from dual));
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '계좌번호(005)' + 'ls_acccode' + ls_acccode
// sle_msg.text = '[회계 회계전표(관리항목) 테이블] ' + gf_msg_call('Z035')
return
end if
END IF //IF ll_row1 = 1
IF ll_row1 > 1 THEN
INSERT INTO ACCAC031T
VALUES (:ls_accsaup
,:ls_accgb
,:ls_accdate
,:ll_autono
,:ll_line
,'175'
,:ls_deposit
,NULL
,:gsEmpcd
,SYSDATE
,NULL
,NULL
,99
)
USING SQLCA;
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류(입금표번호)", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
// 보통예금
ls_kumcode = trim(dw_1.getitemstring(ll_row, 'codeid'))
ls_acccode = trim(dw_1.getitemstring(ll_row, 'korsnm'))
ls_temp = ''
select korfnm, engfnm
into:ls_kwnname, :ls_temp
from syscodedet
where codetp = 'S029'
and codeid = :ls_kumcode;
if isnull(ls_kwnname) then
ls_kwnname = ''
end if
if isnull(ls_temp) then
ls_kumcode = ''
else
ls_kumcode = trim(ls_temp)
end if
setnull(ls_temp)
ls_temp = '004'
insert into accac031T (accsaup, accgb, accym, accno,
lineno, kwanri, kwanrinm, kwanricnt,
firstemp, firstymd, lastemp, lastymd, sortkey)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono,
:ll_line, :ls_temp, :ls_kumcode, :ls_kwnname,
:gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd, (select F_SORTKEY('15', :ls_temp) from dual) );
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '은행(004)' + 'ls_kumcode====' + ls_kumcode
// sle_msg.text = '[회계 회계전표(관리항목) 테이블] ' + gf_msg_call('Z035')
return
end if
setnull(ls_temp)
ls_temp = '005'
insert into accac031T (accsaup, accgb, accym, accno,
lineno, kwanri, kwanrinm, kwanricnt,
firstemp, firstymd, lastemp, lastymd, sortkey)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono,
:ll_line, :ls_temp, :ls_acccode, :ls_kwnname,
:gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd, (select F_SORTKEY('15', :ls_temp) from dual));
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '계좌번호(005)' + 'ls_acccode' + ls_acccode
// sle_msg.text = '[회계 회계전표(관리항목) 테이블] ' + gf_msg_call('Z035')
return
end if
END IF //IF ll_row1 > 1
ls_cname = mid(ls_accdate, 5, 2) + '월 보통예금수금'
//계정코드 수금-부도회수상대계정(S160) 대변 (외상매출금)
SELECT KORSNM, 'C'
INTO :ls_crcode, :ls_cr
FROM SYSCODEDET
WHERE CODETP = 'S160'
AND CODEID = '1';
ll_row2 = ll_row1 * 2
ll_amt = ll_collect_amt
ls_salescode = ls_sales_code
insert into accac03T(accsaup, accgb, accym, accno, lineno,
acccd, oppacc, drcr,
amt, retdept, sayongja, userdept, docgbn,
jugyo, accchul, firstemp, firstymd, lastemp, lastymd)
values ( :ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono, :ll_row2,
:ls_crcode, :ls_null, :ls_cr,
:ll_amt, :ls_null, :ls_null, :ls_user_dept, :ls_docgbn,
:ls_cname, :ls_null, :gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd);
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '[회계 회계전표( - 대변) 테이블] ' + gf_msg_call('Z035')
return
end if
//(001)거래처
if isnull(ls_kwnname) then
ls_kwnname = ''
end if
setnull(ls_temp)
ls_temp = '001'
insert into accac031T (accsaup, accgb, accym, accno,
lineno, kwanri, kwanrinm, kwanricnt,
firstemp, firstymd, lastemp, lastymd, sortkey)
values (:ls_accsaup, :ls_accgb, :ls_accdate, :ll_autono,
:ll_row2, :ls_temp, :ls_salescode, (select trade_name from sales_1 where sales_code = :ls_salescode),
:gsempcd, :ldt_ymd, :gsempcd, :ldt_ymd, (select F_SORTKEY('15', :ls_temp) from dual));
if sqlca.sqlcode <> 0 then
rollback using sqlca;
sle_msg.text = '(001)거래처' + 'ls_salescode' + ls_salescode
// sle_msg.text = '[회계 회계전표(관리항목) 테이블] ' + gf_msg_call('Z035')
return
end if
// 사업자등록번호
INSERT INTO ACCAC031T
VALUES (:ls_accsaup
,:ls_accgb
,:ls_accdate
,:ll_autono
,:ll_row2
,'028'
,(SELECT TAX_NO FROM SALES_1 WHERE SALES_CODE = :ls_salescode)
,NULL
,:gsEmpcd
,SYSDATE
,NULL
,NULL
,98
)
USING SQLCA;
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류(사업자번호)", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
//// 회계전표문서번호 생성 및 set
//ls_account = ls_accsaup + ls_accgb + ls_accdate + string(ll_autono,'0000')
//for ll_row = 1 to dw_1.rowcount()
// ls_account_no = ls_account + string(ll_row, '0000')
// dw_1.setitem(ll_row, 'korsnm', ls_account_no)
//next
//// 회계전표문서번호 생성 및 set
ls_account = ls_accsaup + ls_accgb + ls_accdate + string(ll_autono,'0000')
ls_account_no = ls_account + string((ll_row1 * 2 - 1), '0000')
//ls_account_no = ls_account + string(ll_row1, '0000')
//--수금반제취소때문에 차변의 라인수에 맞추기위해 수정 : 2016.02.26
//ls_account_no = ls_account + string(ll_line, '0000')
/*** 수금생성 ***/
IF (ll_row = 1) THEN
SELECT SLIP_NO
INTO :ll_collect_no
FROM SALES_SLIPNO
WHERE SLIP_DATE = :ls_collect_date
USING SQLCA;
CHOOSE CASE SQLCA.SQLCode
CASE -1
MessageBox("오류", SQLCA.SQLErrText)
RETURN
CASE 100
ll_collect_no = 1
INSERT INTO SALES_SLIPNO
VALUES (:ls_collect_date
,:ll_collect_no );
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
CASE 0
ll_collect_no += 1
UPDATE SALES_SLIPNO
SET SLIP_NO = :ll_collect_no
WHERE SLIP_DATE = :ls_collect_date;
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
END CHOOSE
END IF
IF ((ll_row > 1) and (ls_emp_no <> ls_emp_no_b)) THEN //담당자가 다를경우 새로운 전표번호 생성
SELECT SLIP_NO
INTO :ll_collect_no
FROM SALES_SLIPNO
WHERE SLIP_DATE = :ls_collect_date
USING SQLCA;
CHOOSE CASE SQLCA.SQLCode
CASE -1
MessageBox("오류", SQLCA.SQLErrText)
RETURN
CASE 100
ll_collect_no = 1
INSERT INTO SALES_SLIPNO
VALUES (:ls_collect_date
,:ll_collect_no );
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
CASE 0
ll_collect_no += 1
UPDATE SALES_SLIPNO
SET SLIP_NO = :ll_collect_no
WHERE SLIP_DATE = :ls_collect_date;
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
RETURN
END IF
END CHOOSE
END IF
SELECT BANK_CODE
INTO:ls_bank_code
FROM ACCOUNT_EXCEL
WHERE COLLECT_DATE = :ls_collect_date
AND KEY_NO = :ls_key_no
USING SQLCA;
INSERT INTO COLLECT_ORDER (COLLECT_DATE, COLLECT_NO, COLLECT_SEQ, BUSINESS_GUBUN, COLLECT_CLASS, SLIP_STATUS,
SALES_CODE, FLOW, DEPT_CODE, EMP_NO, INDIRECT_GUBUN, BANK_DATE, BANK_TYPE, BANK_ACC,
COLLECT_AMT, REDUCTION_YN, RMK, WITHDRAWAL_ACCOUNT,
REDUCTION_AMT, ACCOUNT_NO, FIRSTEMP, FIRSTYMD, LASTEMP, LASTYMD)
VALUES (:ls_collect_date, :ll_collect_no, :ll_row1, '1', '15', '10',
:ls_sales_code, :ls_flow, :ls_dept_code, :ls_emp_no, :ls_indirect_gubun,:ls_approval_dt, :ls_bank_code, :ls_acccode,
:ll_collect_amt, 'N', :ls_sales_code, '1',
'0', :ls_account_no, :gsEmpcd, :id_sysdate, :gsEmpcd, :id_sysdate);
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
sle_msg.Text = '자료 저장 중 오류가 발생하였습니다.'
RETURN
END IF
IF SQLCA.SQLCode = 0 THEN
// COMMIT USING SQLCA;
END IF
UPDATE ACCOUNT_EXCEL
SET PROC_YN = 'Y'
,ACCOUNT_NO = :ls_acccode
,LASTEMP = :gsEmpcd
,LASTYMD = :id_sysdate
WHERE COLLECT_DATE = :ls_collect_date
AND KEY_NO = :ls_key_no
USING SQLCA;
IF SQLCA.SQLCode = -1 THEN
MessageBox("오류", SQLCA.SQLErrText)
ROLLBACK USING SQLCA;
sle_msg.Text = '자료 저장 중 오류가 발생하였습니다.'
RETURN
END IF
IF SQLCA.SQLCode = 0 THEN
// COMMIT USING SQLCA;
END IF
COMMIT USING SQLCA;
NEXT
MessageBox("확인", '수금전표생성완료!')
Parent.wf_retrieve()
수금전표생성까지 처리완료하면 다른 프로그램에서 전표생성까지 완료된걸 확인할 수 있다.
'Powerbuilder' 카테고리의 다른 글
| 파워빌더 스크립트 SP_CONTRACT_VALUE_ITEM_INSERT& SP_CONTRACT_VALUE_ITEM_UPDATE 처리 (0) | 2022.08.26 |
|---|
