OracleMapper.LS
3.13 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.batch.mapper.secondary.OracleMapper">
<!-- 작업리스트 -->
<select id="getOriginalData" parameterType="map" resultType="map">
WITH COMP_INFO IS (
SELECT DISTINCT
COMP_CODE AS COMP_CODE
, (
SELECT
COMP_NAME_0
FROM TBBC_COMPANY_CODE_NLS
WHERE
SESSION_ID = A.SESSION_ID
AND COMP_CODE = A.COMP_CODE
AND #{accnutYm} BETWEEN START_YYMM AND END_YYMM
AND ROWNUM = 1
) AS COMP_NAME
, BOOK_CURRENCY AS COMP_CURRENCY
, COMP_COA AS COMP_COA
, CONS_COA AS CONS_COA
FROM TBBC_COMPANY_CODE A
WHERE
#{accnutYm} BETWEEN START_YYMM AND END_YYMM
)
SELECT
A.SESSION_ID,
A.CONS_GROUP,
A.LEDGER,
A.ACCOUNT_PERIOD,
A.SEQ,
A.TRAN_TYPE,
A.OWN_COMP,
B.COMP_NAME AS OWN_COMP_NAME
A.TRAN_COMP,
C.COMP_NAME AS TRAN_COMP_NAME
A.COMP_ACCT_CODE,
(
SELECT DISTINCT
COMP_ACCT_CODE
FROM COMP_ACCT_CODE
WHERE
FS_TYPE = '1'
AND COMP_COA = B.COMP_COA
AND COMP_ACCT_CODE = A.COMP_ACCT_CODE
AND #{accnutYm} BETWEEN START_YYMM AND END_YYMM
AND ROWNUM = 1
) AS COMP_ACCT_NAME
A.COMP_PROD_CODE,
A.RECON_KEY,
A.TRAN_DATE,
A.TRAN_CURRENCY,
A.TRAN_AMT,
A.BOOK_AMT,
A.GROUP_AMT,
A.BOOK_COST,
A.DOCUMENT_NO,
A.REMARK,
A.CONS_ACCT_CODE,
(
SELECT
CONS_ACCT_KR_NAME
FROM TBBC_ACCT_CODE
WHERE
CONS_ACCT_CODE = A.CONS_ACCT_CODE
AND CONS_COA = 'LS_COA'
AND FS_TYPE = '1'
AND #{accnutYm} BETWEEN START_YYMM AND END_YYMM
AND ROWNUM = 1
) AS CONS_ACCT_NAME,
A.PROD_CODE,
A.ADJUST_TYPE,
A.ADJUST_REMARK,
A.IF_ACCOUNT_PERIOD,
A.IF_COMP_CODE,
A.IF_FILE_ID,
A.IF_SOURCE,
A.EQUAL_CHECK,
A.CONS_OWN_COMP,
A.CONS_TRAN_COMP,
A.INVOICE_NO,
A.LC_NO,
A.BL_NO,
A.OWN_BIZ_NO,
A.PARTNER_BIZ_NO,
A.ACCT_DATE
FROM
TBCR_TRANSACTION_HISTORY A,
INNER JOIN COMP_INFO B
ON
B.COMP_CODE = A.OWN_COMP
INNER JOIN COMP_INFO C
ON
C.COMP_CODE = A.TRAN_COMP
WHERE
A.CONS_GROUP = #{sysSe}
AND A.ACCOUNT_PERIOD = #{accnutYm}
</select>
<select id="getMatchingInfo" parameterType="map" resultType="map">
SELECT
A.MATCH_KEY,
A.MATCHING_CAUSE,
B.SEQ
FROM TBCR_MATCHING A,
TBCR_MATCHING_DETAIL B
WHERE
A.SESSION_ID = B.SESSION_ID
AND A.CONS_GROUP = B.CONS_GROUP
AND A.LEDGER = B.LEDGER
AND A.MATCH_KEY = B.MATCH_KEY
AND A.ACCOUNT_PERIOD = B.ACCOUNT_PERIOD
AND A.CONS_GROUP = #{sysSe}
AND A.ACCOUNT_PERIOD = #{accnutYm}
</select>
<update id="updateMatchingResult" parameterType="map">
--결과 업데이트
</update>
</mapper>