SELECT
		USE_DAY,
		SETTLE_EXEC_DIV,
		SONG_UID,
		CONTRACT_NUM,
		CONTRACT_VER,
		SALE_PRICE,
		SITE_CATE_CODE,
		SERVICE_CATE_NUM,
		COPYRIGHT_RATE,
		COPYRIGHT_WORDS_RATE,
		COPYRIGHT_COMPOSE_RATE,
		COPYRIGHT_ARRANGE_RATE,
		SETTLE_SEQ,
		SALE_CNT,
		ADMIN_UID,
		UNIT_COST,
		STREAM_CNT,
		SERVICE_DIV_SD,
		POSS_RATE
	FROM (
		SELECT
			A.USE_DAY																		AS USE_DAY,
			PI_ACC_TYPE																		AS SETTLE_EXEC_DIV,
			A.SONG_UID																		AS SONG_UID,
			DECODE(C.RATE_DIV, 'Y', C.CONTRACT_NUM, D.CONTRACT_NUM)							AS CONTRACT_NUM,
			DECODE(C.RATE_DIV, 'Y', C.CONTRACT_VER, D.CONTRACT_VER)							AS CONTRACT_VER,
			ROUND(A.SALE_AMOUNT * A.POSS_RATE / 100 / P_DATE_TERM, 0)						AS SALE_PRICE,
			A.SITE_CATE_CODE																AS SITE_CATE_CODE,
			A.SERVICE_CATE_NUM																AS SERVICE_CATE_NUM,
			DECODE(C.RATE_DIV, 'Y', C.COPYRIGHT_RATE, D.COPYRIGHT_RATE)						AS COPYRIGHT_RATE,
			DECODE(C.RATE_DIV, 'Y', C.COPYRIGHT_WORDS_RATE, D.COPYRIGHT_WORDS_RATE)			AS COPYRIGHT_WORDS_RATE,
			DECODE(C.RATE_DIV, 'Y', C.COPYRIGHT_COMPOSE_RATE, D.COPYRIGHT_COMPOSE_RATE)		AS COPYRIGHT_COMPOSE_RATE,
			DECODE(C.RATE_DIV, 'Y', C.COPYRIGHT_ARRANGE_RATE, D.COPYRIGHT_ARRANGE_RATE)		AS COPYRIGHT_ARRANGE_RATE,
			P_SETTLE_SEQ																	AS SETTLE_SEQ,
			A.SALE_CNT																		AS SALE_CNT,
			PI_ADMIN_UID																	AS ADMIN_UID,
			A.UNIT_COST																		AS UNIT_COST, 
			A.STREAM_CNT																	AS STREAM_CNT, 
			A.SERVICE_DIV_SD																AS SERVICE_DIV_SD,
			A.POSS_RATE																		AS POSS_RATE
		FROM (
		    SELECT  /*+ index(KTULDA PK_USE_LOG_DAY_AMOUNT) */
			    KTULDS.USE_DAY						AS USE_DAY,
			    KTULDS.SERVICE_CATE_NUM				AS SERVICE_CATE_NUM,
			    KTULDS.SITE_CATE_CODE				AS SITE_CATE_CODE,
			    KTULDS.SONG_UID						AS SONG_UID,
			    KTULDS.SERVICE_DIV_SD				AS SERVICE_DIV_SD,
			    KTULDS.POSS_RATE             		AS POSS_RATE,
			    KTULDS.SALE_CNT				        AS SALE_CNT,
			    KTULDS.UNIT_COST				    AS UNIT_COST,
			    KTULDS.STREAM_CNT				    AS STREAM_CNT,
			    NVL(KTULDA.SALE_AMOUNT, 0)      	AS SALE_AMOUNT           
		    FROM
					(SELECT /*+ index(KTULDS KTM_I_USELOGDAYSUM) */
			    KTULDS.USE_DAY						AS USE_DAY,
			    KTULDS.SERVICE_CATE_NUM				AS SERVICE_CATE_NUM,
			    KTULDS.SITE_CATE_CODE				AS SITE_CATE_CODE,
			    KTULDS.SONG_UID						AS SONG_UID,
			    KTULDS.SERVICE_DIV_SD				AS SERVICE_DIV_SD,
			    ROUND(SUM(KTULDS.POSS_RATE), 4)		AS POSS_RATE,
			    SUM(KTULDS.SALE_CNT)				AS SALE_CNT,
			    SUM(KTULDS.UNIT_COST)				AS UNIT_COST,
			    SUM(KTULDS.STREAM_CNT)				AS STREAM_CNT
			FROM
			    KTM_T_USE_LOG_DAY_SUM KTULDS
			WHERE
			    KTULDS.USE_DAY BETWEEN PI_START_DATE AND PI_END_DATE
			    AND KTULDS.SERVICE_CATE_NUM = PI_SERVICE_CATE_NUM
			    AND KTULDS.SITE_CATE_CODE = PI_SITE_CATE_CODE
			    AND KTULDS.DIV_FLAG = '4811'
			GROUP BY
			    KTULDS.USE_DAY,
			    KTULDS.SERVICE_CATE_NUM,
			    KTULDS.SITE_CATE_CODE,
			    KTULDS.SONG_UID,
			    KTULDS.SERVICE_DIV_SD)KTULDS, KTM_T_USE_LOG_DAY_AMOUNT KTULDA
		    WHERE 
			KTULDS.USE_DAY = KTULDA.USE_DAY
			AND KTULDA.SERVICE_CATE_NUM = KTULDA.SERVICE_CATE_NUM
			AND KTULDA.SITE_CATE_CODE = KTULDA.SITE_CATE_CODE
			AND KTULDA.USE_DAY BETWEEN PI_START_DATE AND PI_END_DATE
			AND KTULDA.SERVICE_CATE_NUM = PI_SERVICE_CATE_NUM
			AND KTULDA.SITE_CATE_CODE = PI_SITE_CATE_CODE               
		) A
		LEFT OUTER JOIN (
			SELECT  /*+ index(TBLB ktm_i_cpct_sersite) index(TBLA PK_EACH_CONTRACT) */
				TBLA.SONG_UID,
				TBLA.RATE_DIV,
				TBLA.COPYRIGHT_RATE,
				TBLA.COPYRIGHT_WORDS_RATE,
				TBLA.COPYRIGHT_COMPOSE_RATE,
				TBLA.COPYRIGHT_ARRANGE_RATE,
				TBLB.SETTLE_APPLY_DAY,
				TBLB.SETTLE_END_DAY,
				TBLB.CONTRACT_NUM,
				TBLB.CONTRACT_VER  
			FROM
				KTM_T_EACH_CONTRACT TBLA 
				INNER JOIN KTM_T_CP_CONTRACT TBLB ON TBLA.CONTRACT_NUM = TBLB.CONTRACT_NUM
					AND TBLA.CONTRACT_VER = TBLB.CONTRACT_VER
					AND TBLB.SERVICE_CATE_NUM = PI_SERVICE_CATE_NUM
					AND TBLB.SITE_CATE_CODE = PI_SITE_CATE_CODE
			WHERE
				TBLB.SETTLE_END_DAY >= PI_START_DATE AND TBLB.SETTLE_APPLY_DAY <= PI_END_DATE
				AND TBLA.COPYRIGHT_RATE > 0
				AND TBLA.RATE_DIV > ' '
		) C ON A.SONG_UID = C.SONG_UID
			AND A.USE_DAY BETWEEN C.SETTLE_APPLY_DAY AND C.SETTLE_END_DAY
			AND C.COPYRIGHT_RATE > 0
		LEFT OUTER JOIN (
			SELECT /*+ use_nl(TBLD TBLE TBLF) use_nl(TBLC) */
				TBLC.SONG_UID,
				TBLD.COPYRIGHT_RATE,
				TBLD.COPYRIGHT_WORDS_RATE,
				TBLD.COPYRIGHT_COMPOSE_RATE,
				TBLD.COPYRIGHT_ARRANGE_RATE,
				TBLD.SETTLE_APPLY_DAY,
				TBLD.SETTLE_END_DAY,
				TBLD.CONTRACT_NUM,
				TBLD.CONTRACT_VER
			FROM
				KTM_T_MUSIC_SRC_HISTORY TBLC
				INNER JOIN KTM_T_CONTRACT TBLF ON TBLC.OID = TBLF.COMPANY_NUM
				INNER JOIN KTM_T_CP_CONTRACT TBLD ON TBLF.CONTRACT_NUM = TBLD.CONTRACT_NUM
					AND TBLF.CONTRACT_VER = TBLD.CONTRACT_VER
					AND TBLD.SERVICE_CATE_NUM = PI_SERVICE_CATE_NUM
					AND TBLD.SITE_CATE_CODE = PI_SITE_CATE_CODE
				INNER JOIN KTM_T_COMPANY_TYPE TBLE ON TBLD.COMPANY_NUM = TBLE.COMPANY_NUM AND TBLE.TYPE_CODE = '2015'
			WHERE
				TBLD.SETTLE_END_DAY >= PI_START_DATE AND TBLD.SETTLE_APPLY_DAY <= PI_END_DATE
				AND TBLC.APPLY_START_DAY <= PI_END_DATE AND TBLC.APPLY_END_DAY >= PI_START_DATE
				AND TBLD.COPYRIGHT_RATE > 0
		) D ON A.SONG_UID = D.SONG_UID AND A.USE_DAY BETWEEN D.SETTLE_APPLY_DAY AND D.SETTLE_END_DAY
	WHERE
		(C.RATE_DIV > ' ' OR D.COPYRIGHT_RATE > 0)
		AND NOT EXISTS (
			SELECT /*+ ordered use_nl(KTUS CTC KTUSC) index(KTUS KTM_I_USE_SETTLE_DY) */
				CTC.CALENDARDAY
			FROM 
				KTM_T_USE_SETTLE KTUS, COM_T_CALENDAR CTC, KTM_T_USE_SETTLE_CONTRACT KTUSC
			WHERE
				SETTLE_END_YN = 'Y'
				AND KTUS.SETTLE_SEQ = KTUSC.SETTLE_SEQ
				AND KTUS.SETTLE_EXEC_DIV = PI_ACC_TYPE				-- Á¤»êŸÀÔ
				AND KTUS.SERVICE_CATE_NUM = PI_SERVICE_CATE_NUM		-- ¼­ºñ½º
				AND KTUS.SETTLE_END_DAY >= PI_START_DATE AND KTUS.SETTLE_START_DAY <= PI_END_DATE 
				AND CTC.CALENDARDAY BETWEEN KTUS.SETTLE_START_DAY AND KTUS.SETTLE_END_DAY
				AND KTUSC.SITE_CATE_CODE = PI_SITE_CATE_CODE
				AND USE_DAY = CTC.CALENDARDAY
		)
	)