<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm
    $Date: 2013-12-28 下午1:31:12
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
	<bm:operations>
		<bm:operation name="query">
			<bm:query-sql><![CDATA[
        Select to_char(t1.transaction_date, 'yyyy-mm') csh_date,
			   Count(t1.transaction_id) csh_count,
			   Count(t1.csh_not_id) csh_not,
			   Count(t1.csh_partial_id) csh_partial,
			   decode(Count(t1.transaction_id), Count(t1.csh_confirm), 'Y', 'N') As csh_confirm_flag,
			   t1.ref_v04
		  From (Select t.transaction_date,
					   t.transaction_id,
					   (Select c.transaction_id
						  From csh_transaction c
						 Where c.transaction_id = t.transaction_id
						   And nvl(c.write_off_flag, 'NOT') = 'NOT') As csh_not_id,
					   (Select c1.transaction_id
						  From csh_transaction c1
						 Where c1.transaction_id = t.transaction_id
						   And nvl(c1.write_off_flag, 'NOT') = 'PARTIAL') As csh_partial_id,

					   (Select c2.transaction_id
						  From csh_transaction c2
						 Where c2.transaction_id = t.transaction_id
						   And nvl(c2.business_monthly_flag, 'N') = 'Y') As csh_confirm,
					   t.ref_v04
				  From csh_transaction t
				  Where t.confirmed_flag in ('ACCAUDITED','APPROVED', 'WF_REJECTED', 'WF_APPROVING')
				  And t.transaction_type not in ('DEPOSIT', 'PAYMENT')
				  And t.returned_flag not in ('RETURN', 'FULL')
				  And nvl(t.reversed_flag, 'N') = 'N') t1
		  Where to_char(t1.transaction_date, 'yyyymm') <= to_char(sysdate, 'yyyymm')
		  And   to_char(t1.transaction_date, 'yyyymm') > '202001'
		 Group By to_char(t1.transaction_date, 'yyyy-mm'), t1.ref_v04
		 Order By to_char(t1.transaction_date, 'yyyy-mm') desc
            ]]></bm:query-sql>
		</bm:operation>

		<bm:operation name="update">
			<bm:update-sql><![CDATA[
            	begin
					hls_monthend_new_pkg.csh_transaction_confirm(
									p_role_flag => ${@role_flag},
                                    p_csh_date  => ${@csh_date},
                                    p_ref_v04   => ${@ref_v04},
                                    p_user_id   => ${/session/@user_id},
                                    p_err_msg=> ${@p_err_msg});
            	end;
            ]]></bm:update-sql>
			<bm:parameters>
				<bm:parameter name="p_err_msg" dataType="java.lang.String" input="false" output="true" outputPath="@p_err_msg"/>
			</bm:parameters>
		</bm:operation>
		<bm:operation name="execute">
			<bm:update-sql><![CDATA[
            	begin
					hls_monthend_new_pkg.csh_check_acr(p_user_id   => ${/session/@user_id},p_err_msg=> ${@p_err_msg});
            	end;
            ]]></bm:update-sql>
			<bm:parameters>
				<bm:parameter name="p_err_msg" dataType="java.lang.String" input="false" output="true" outputPath="@p_err_msg"/>
			</bm:parameters>
		</bm:operation>
	</bm:operations>
</bm:model>
