-- ######################################################### 1001_당일매출요청 ######################################################### START -- Q. 데이터셋 전표별, 품목별 2개 생성하는게 맞나요? (맞을 시 전표데이터셋에 매출전표번호 내 인덱스 확인) -- 1. 매출 (전표별) SELECT TO_CHAR(A.SAL_DT, 'YYYYMMDD') AS SAL_DT -- 매출발생날짜 , A.SAL_NO -- 매출전표번호 -- 매출전표번호 내 인덱스 , A.CUST_CD -- 거래처코드 , B.CUST_NM -- 거래처명 , A.SAL_CHRG_CD -- 담당사원코드 , SUM( A.SUPP_AMT + A.VAT_AMT + A.GRNT_AMT ) AS TOT_AMT -- 매출합계 , A.SAL_TYP -- 매출타입 FROM ( SELECT A.SAL_DT , A.SAL_NO , A.SAL_SEQNO , A.CUST_CD , A.SAL_CHRG_CD , 0 AS TOT_AMT , A.SAL_TYP , 'P' AS ITM_TYP , A.ITM_CD , A.BOX_QTY , A.BOTL_QTY , A.SUPP_AMT , A.VAT_AMT , A.GRNT_AMT FROM CUST_A3910.DA251V0 A WHERE A.SAL_DT = '20181101' UNION ALL SELECT A.SAL_DT , A.SAL_NO , A.SAL_LEND_SEQ_NO AS SAL_SEQNO , A.CUST_CD , A.SAL_CHRG_CD , 0 AS TOT_AMT , A.SAL_TYP , 'Y' AS ITM_TYP , A.LEND_ITM_CD AS ITM_CD , A.VES_QTY AS BOX_QTY , A.EMPTY_BOT_QTY AS BOTL_QTY , 0 AS SUPP_AMT , 0 AS VAT_AMT , A.GRNT_AMT FROM CUST_A3910.DA476V0 A WHERE A.SAL_DT = '20181101' AND A.SAL_SEQNO IS NULL ) A JOIN CUST_A3910.DA150T0 B ON A.CUST_CD = B.CUST_CD GROUP BY A.SAL_DT, A.SAL_NO, A.CUST_CD, B.CUST_NM, A.SAL_CHRG_CD, A.SAL_TYP ORDER BY SAL_DT, SAL_NO, CUST_CD ; -- 2. 매출 (품목별) SELECT TO_CHAR(A.SAL_DT, 'YYYYMMDD') AS SAL_DT -- 매출발생날짜 , A.SAL_NO -- 매출전표번호 , A.SAL_SEQNO -- 매출전표번호 내 인덱스 , A.CUST_CD -- 거래처코드 , B.CUST_NM -- 거래처명 , A.SAL_CHRG_CD -- 담당사원명 , SUM( A.SUPP_AMT + A.VAT_AMT + A.GRNT_AMT ) OVER( PARTITION BY A.SAL_DT, A.SAL_NO, A.CUST_CD ) AS TOT_AMT -- 매출합계 , A.SAL_TYP -- 매출타입 , A.ITM_TYP -- 품목/용공 구분 , A.ITM_CD -- 품목코드 , A.BOX_QTY -- 박스수량 , A.BOTL_QTY -- 개발수량 , A.SUPP_AMT -- 공급가 , A.VAT_AMT -- 부가세 , A.GRNT_AMT -- 용공보증 FROM ( SELECT A.SAL_DT , A.SAL_NO , A.SAL_SEQNO , A.CUST_CD , A.SAL_CHRG_CD , 0 AS TOT_AMT , A.SAL_TYP , 'P' AS ITM_TYP , A.ITM_CD , A.BOX_QTY , A.BOTL_QTY , A.SUPP_AMT , A.VAT_AMT , A.GRNT_AMT FROM CUST_A3910.DA251V0 A WHERE A.SAL_DT = '20181101' UNION ALL SELECT A.SAL_DT , A.SAL_NO , A.SAL_LEND_SEQ_NO AS SAL_SEQNO , A.CUST_CD , A.SAL_CHRG_CD , 0 AS TOT_AMT , A.SAL_TYP , 'Y' AS ITM_TYP , A.LEND_ITM_CD AS ITM_CD , A.VES_QTY AS BOX_QTY , A.EMPTY_BOT_QTY AS BOTL_QTY , 0 AS SUPP_AMT , 0 AS VAT_AMT , A.GRNT_AMT FROM CUST_A3910.DA476V0 A WHERE A.SAL_DT = '20181101' AND A.SAL_SEQNO IS NULL ) A JOIN CUST_A3910.DA150T0 B ON A.CUST_CD = B.CUST_CD ORDER BY SAL_DT, SAL_NO, CUST_CD, SAL_SEQNO ; -- ######################################################### 1001_당일매출요청 ######################################################### END -- ######################################################### 1002_거래처수금데이터 ######################################################### START -- Q. 해당수금날짜 = 입금날짜? 똑같은 데이터 2번 조회됩니다. SELECT A.SLIP_NO -- 해당수금거래전표번호 , A.ACCT_FG -- 수금타입 , TO_CHAR(A.SLIP_DT,'YYYYMMDD') AS SLIP_DT -- 해당수금날짜 , A.CUST_CD -- 거래처코드 , A.SAL_CHRG_CD -- 담당사원코드 , TO_CHAR(A.SLIP_DT,'YYYYMMDD') AS SLIP_DT_2 -- 입금날짜 , SUM(A.AMT) AS AMT -- 입금합계 FROM CUST_A3910.DA311T0 A WHERE A.SLIP_DT = '20181101' GROUP BY A.SLIP_NO, A.ACCT_FG, A.SLIP_DT, A.CUST_CD, A.SAL_CHRG_CD ORDER BY SLIP_DT, SLIP_NO, CUST_CD E -- ######################################################### 1002_거래처수금데이터 ######################################################### END -- ######################################################### 1003_거래처최초대여금 ######################################################### START -- Q. 상환예정일 : 가장 마지막 회수예정일자의 일(DD) 형식? -- Q. 조회일자 기준 = 대여일자 기준? SELECT A.LEND_NO -- 대여번호 , TO_CHAR(A.LEND_DT,'YYYYMMDD') AS LEND_DT -- 대여날짜 , A.CUST_CD -- 거래처코드 , A.SAL_CHRG_CD -- 담당사원코드 , TO_CHAR(MIN(C.MIN_LEND_DT),'YYYYMMDD') AS MIN_LEND_DT -- 대여시작일 , TO_CHAR(MAX(C.MAX_LEND_DT),'YYYYMMDD') AS MAX_LEND_DT -- 최종대여일 , A.LEND_AMT -- 전체대여금 , A.INSPAY_MMCNT -- 상환개월수 , TO_CHAR(MAX(B.RETRV_PREARR_DD),'DD') AS RETRV_PREARR_DD -- 상환예정일 FROM CUST_A3910.DA901T0 A LEFT JOIN ( SELECT NODE_CODE, CUST_CD, LEND_NO, MAX(RETRV_PREARR_DT) AS RETRV_PREARR_DD FROM CUST_A3910.DA901T1 GROUP BY NODE_CODE, CUST_CD, LEND_NO ) B ON A.NODE_CODE = B.NODE_CODE AND A.CUST_CD = B.CUST_CD AND A.LEND_NO = B.LEND_NO LEFT JOIN ( SELECT CUST_CD, MIN(LEND_DT) AS MIN_LEND_DT, MAX(LEND_DT) AS MAX_LEND_DT FROM CUST_A3910.DA901T0 GROUP BY CUST_CD ) C ON A.CUST_CD = C.CUST_CD WHERE A.LEND_DT = '20170928' GROUP BY A.LEND_NO, A.LEND_DT, A.CUST_CD, A.SAL_CHRG_CD, A.LEND_AMT, A.INSPAY_MMCNT ; -- ######################################################### 1003_거래처최초대여금 ######################################################### END -- ######################################################### 1004_대여금회수데이터 ######################################################### START -- Q. 마지막 인덱스가 뭔지 잘 모르겠습니다. -- A. 날짜의 마지막 인덱스 이후의 회수작업 조회 --> 회수회차는 거래처-대여번호별로 순번대로 따지는 것 같습니다. -- Q. 입금날짜 = 회수날짜 , 입금회차 = 회수전표번호? 똑같은 데이터 2번 조회됩니다. SELECT A.LEND_NO -- 대여번호 , A.RETRV_NO -- 회수번호 , TO_CHAR(A.RETRV_DT,'YYYYMMDD') AS RETRV_DT -- 해당 회수 날짜 , A.CUST_CD -- 거래처코드 , TO_CHAR(A.RETRV_DT,'YYYYMMDD') AS RETRV_DT_2 -- 입금날짜 , A.RETRV_NO AS RETRV_NO_2 -- 입금회차 , A.RETRV_AMT -- 회수금액 , NVL((SELECT SUM(B.TOT_AMT) FROM CUST_A3910.DA901T0 B WHERE A.CUST_CD = B.CUST_CD AND B.LEND_DT <= '20181030'),0) - NVL((SELECT SUM(C.RETRV_AMT) FROM CUST_A3910.DA902T1 C WHERE A.CUST_CD = C.CUST_CD AND C.RETRV_DT <= '20181030'),0) AS AMT -- 대여금잔액 FROM CUST_A3910.DA902T1 A WHERE RETRV_DT = '20181030' ; -- ######################################################### 1004_대여금회수데이터 ######################################################### END -- ######################################################### 1004_대여금회수데이터 ######################################################### START -- Q. 최초등록일자가 최초로 등록한 시간 맞나요? -- Q. "업종" : 사용자 입력 데이터? SELECT A.CUST_CD -- 거래처코드 , A.CUST_NM -- 거래처명 , B.SAL_CHRG_CD -- 담당사원코드 , TO_CHAR(A.INS_DTTM,'YYYYMMDD') AS INS_DT -- 최초등록일자 , A.BIZR_REG_NO -- 사업자번호 , A.BIZCND -- 업태 , A.INDSTYP -- 업종 , A.REPR_NM -- 대표자명 , A.BIZPLC_TEL_NO1 -- 사업장전화번호 , A.BIZPLC_ADDR1 || ' ' || A.BIZPLC_ADDR2 AS BIZPLC_ADDR -- 사업장주소 , A.CUST_STAT -- 거래처상태 FROM CUST_A3910.DA150T0 A JOIN CUST_A3910.DA150T2 B ON A.CUST_CD = B.CUST_CD WHERE A.PRCH_SAL_FG = '2' ; -- ######################################################### 1004_대여금회수데이터 ######################################################### END -- ######################################################### 1006_영업사원 ######################################################### START SELECT B.SAL_CNT -- 전체담당자수 , A.SAL_CHRG_CD -- 영업담당코드 , A.SAL_CHRG_NM -- 영업사원명 , A.UZ_YN -- 영업사원상태 FROM CUST_A3910.DA104T0 A LEFT JOIN ( SELECT COUNT(*) AS SAL_CNT FROM CUST_A3910.DA104T0 ) B ON 1=1 ; -- ######################################################### 1006_영업사원 ######################################################### END -- ######################################################### 1007_품목 ######################################################### START SELECT B.ITM_CNT -- 전체품목수 , A.ITM_CD -- 품목코드 , A.ITM_NM -- 품목명 , A.STND -- 규격(용량) , A.OBTIN_QTY -- 입수량 , A.UZ_FG -- 용도 , A.UT -- 단위 , A.UZ_YN -- 사용여부 , A.DRK_KND_FG -- 주종코드 , A.DESCR -- 비고 FROM CUST_A3910.DA121T0 A LEFT JOIN ( SELECT COUNT(*) AS ITM_CNT FROM CUST_A3910.DA121T0 ) B ON 1=1 ; -- ######################################################### 1007_품목 ######################################################### END