3 ) GROUP BY finucfitemtype.slipitemtype_code , finucfitemtype.item_desc union SELECT 5 as seq_no ,'FIN' as system_code, FINSLIPDET.slipitemtype_code as type_code , FINSLIPDET.slipitem_desc as type_desc , sum( FINSLIPDET.ITEMPAY_AMT - FINSLIPDET.TAX_AMT ) slip_netamt , count( distinct finslip.slip_no ) as count_slip FROM FINSLIP ,FINSLIPDET ,finucfitemtype , cmcoopmaster WHERE ( FINSLIP.SLIP_NO = FINSLIPDET.SLIP_NO ) AND ( FINSLIP.coop_id = FINSLIPDET.coop_id ) AND ( FINSLIPDET.slipitemtype_code = finucfitemtype.slipitemtype_code ) and ( cmcoopmaster.coop_control = finucfitemtype.coop_id ) and ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.RECEIVE_STATUS = 1 ) AND (cmcoopmaster.coop_id = finslip.coop_id) AND ( FINSLIP.COOP_ID like $P{coop_id}) AND ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND ( FINSLIP.FROM_SYSTEM IN ($P{as_type} ) ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) AND ( FINSLIP.RETAIL_FLAG <> 3 ) AND ( FINSLIPDET.account_id is null ) GROUP BY FINSLIPDET.slipitemtype_code , FINSLIPDET.slipitem_desc union SELECT 5 as seq_no ,'FIN' as system_code, '00' as type_code , 'ภาษีมูลค่าเพิ่ม ' as type_desc , sum( FINSLIPDET.vat_amt ) slip_netamt , count( distinct finslip.slip_no ) as count_slip FROM FINSLIP ,FINSLIPDET , cmcoopmaster WHERE ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND ( FINSLIP.SLIP_NO = FINSLIPDET.SLIP_NO ) AND ( FINSLIP.coop_id = FINSLIPDET.coop_id ) AND ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.RECEIVE_STATUS = 1 ) AND (cmcoopmaster.coop_id = finslip.coop_id) AND ( FINSLIP.COOP_ID like $P{coop_id}) AND ( FINSLIP.FROM_SYSTEM IN ($P{as_type} ) ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) AND ( FINSLIP.RETAIL_FLAG <> 3 ) having sum(FINSLIPDET.vat_amt ) > 0 union SELECT 5 as seq_no ,'FIN' as system_code, finucfitemtype.slipitemtype_code as type_code , finucfitemtype.item_desc as type_desc , sum( FINSLIPDET.ITEMPAY_AMT ) slip_netamt , count( distinct finslip.slip_no ) as count_slip FROM FINSLIP ,FINSLIPDET ,finucfitemtype , accmaster , cmcoopmaster WHERE ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND (accmaster.account_id = FINSLIPDET.account_id ) AND (accmaster.coop_id = cmcoopmaster.coop_id ) AND ( FINSLIP.SLIP_NO = FINSLIPDET.SLIP_NO ) AND ( FINSLIP.coop_id = FINSLIPDET.coop_id ) AND ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.RECEIVE_STATUS = 1 ) AND (cmcoopmaster.coop_id = finslip.coop_id) AND ( FINSLIP.COOP_ID like $P{coop_id}) AND ( FINSLIP.FROM_SYSTEM IN ($P{as_type} ) ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) AND ( FINSLIP.RETAIL_FLAG = 3 ) AND ( FINSLIPDET.slipitemtype_code = finucfitemtype.slipitemtype_code ) and ( cmcoopmaster.coop_id = finucfitemtype.coop_id ) GROUP BY finucfitemtype.slipitemtype_code , finucfitemtype.item_desc union SELECT 3 as seq_no ,'DEP' as system_code, DPDEPTTYPE.DEPTTYPE_CODE as type_code, 'ถอน' || DPDEPTTYPE.DEPTTYPE_DESC as type_desc , sum( DPDEPTSLIP.DEPTSLIP_NETAMT ) slip_netamt , count( distinct finslip.slip_no ) as count_slip FROM FINSLIP, DPDEPTSLIP, DPDEPTTYPE , cmcoopmaster WHERE ( DPDEPTSLIP.DEPTTYPE_CODE = DPDEPTTYPE.DEPTTYPE_CODE ) and ( FINSLIP.REF_SLIPNO = DPDEPTSLIP.DEPTSLIP_NO ) and ( FINSLIP.coop_id = DPDEPTSLIP.coop_id ) and ( ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND (dpdeptslip.deptitemtype_code not in ('WIE','WII','WIM', 'FEE' ) ) and ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.RECEIVE_STATUS = 1 ) AND (cmcoopmaster.coop_id = finslip.coop_id) AND ( FINSLIP.COOP_ID like $P{coop_id}) AND ( FINSLIP.FROM_SYSTEM = $P{as_type} ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) ) GROUP BY DPDEPTTYPE.DEPTTYPE_CODE, DPDEPTTYPE.DEPTTYPE_DESC union SELECT 1 as seq_no ,'LON' as system_code, LNLOANTYPE.loantype_code as type_code , LNLOANTYPE.loantype_desc as type_desc , sum(slslippayout.payout_amt) slip_netamt , count( distinct finslip.slip_no ) as count_slip FROM FINSLIP, slslippayout, LNLOANTYPE , cmcoopmaster WHERE ( FINSLIP.REF_SLIPNO = slslippayout.payoutslip_no ) and ( FINSLIP.coop_id = slslippayout.coop_id ) and ( slslippayout.shrlontype_code = LNLOANTYPE.LOANTYPE_CODE ) and ( ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.RECEIVE_STATUS = 1 ) AND ( slslippayout.slip_status = 1) and (cmcoopmaster.coop_id = finslip.coop_id) AND ( FINSLIP.COOP_ID like $P{coop_id}) AND ( FINSLIP.FROM_SYSTEM = $P{as_type} ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) ) GROUP BY LNLOANTYPE.loantype_code , LNLOANTYPE.loantype_desc union SELECT 2 as seq_no , 'SHP' as system_code, SHSHARETYPE.SHARETYPE_CODE as type_code , 'จ่ายคืนค่าหุ้น' as type_desc , sum( slslippayout.payoutnet_amt ) slip_netamt , count( distinct finslip.slip_no ) as count_slip FROM FINSLIP, slslippayout, SHSHARETYPE , cmcoopmaster WHERE ( FINSLIP.REF_SLIPNO = slslippayout.payoutslip_no ) and ( FINSLIP.coop_id = slslippayout.coop_id ) and ( slslippayout.shrlontype_code = SHSHARETYPE.SHARETYPE_CODE ) and ( slslippayout.slip_status = 1 ) AND ( ( trunc(FINSLIP.ENTRY_DATE) = $P{adtm_date} ) AND ( FINSLIP.PAYMENT_STATUS = 1 ) AND (cmcoopmaster.coop_id = finslip.coop_id) AND ( FINSLIP.COOP_ID like $P{coop_id}) AND ( FINSLIP.FROM_SYSTEM in ($P{as_type} ) ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) ) AND slslippayout.payoutnet_amt > 0 GROUP BY SHSHARETYPE.SHARETYPE_CODE, SHSHARETYPE.SHARETYPE_DESC union SELECT 6 as seq_no, 'DIV' as system_code, '01' as type_code, 'จ่ายปันผล' as type_desc, sum( yrslippayoutdet.div_payment ) slip_netamt, count( 1 ) as count_slip FROM FINSLIP, yrslippayout, yrslippayoutdet WHERE ( trim( FINSLIP.REF_SLIPNO ) = trim( yrslippayout.payoutslip_no ) ) and ( yrslippayout.payoutslip_no = yrslippayoutdet.payoutslip_no ) and ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.FROM_SYSTEM = $P{as_type} ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) AND ( yrslippayoutdet.div_payment > 0 ) having sum( yrslippayoutdet.div_payment ) > 0 union SELECT 7 as seq_no, 'AVG' as system_code, '02' as type_code, 'จ่ายเงินเฉลี่ยคืน' as type_desc, sum( yrslippayoutdet.avg_payment ) slip_netamt, count( 1 ) as count_slip FROM FINSLIP, yrslippayout, yrslippayoutdet WHERE ( trim( FINSLIP.REF_SLIPNO ) = trim( yrslippayout.payoutslip_no ) ) and ( yrslippayout.payoutslip_no = yrslippayoutdet.payoutslip_no ) and ( FINSLIP.ENTRY_DATE = $P{adtm_date} ) AND ( FINSLIP.PAYMENT_STATUS = 1 ) AND ( FINSLIP.FROM_SYSTEM = $P{as_type} ) AND ( FINSLIP.PAY_RECV_STATUS = 0 ) AND ( yrslippayoutdet.avg_payment > 0 ) having sum( yrslippayoutdet.avg_payment ) > 0]]>