<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wushuang  
    $Date: 2011-11-24 下午1:55:05  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT *
			  FROM (SELECT i.request_info_id,
			               i.request_code,
			               i.request_name,
			               i.description,
			               (SELECT COUNT(1)
			                  FROM fnd_program_parameters p, fnd_request_program g
			                 WHERE g.program_id = p.program_id
			                   AND g.request_info_id = i.request_info_id) AS parameter_count
			          FROM fnd_request_info i
			         where EXISTS (SELECT 1
				          FROM fnd_request_responsibility r
				         WHERE r.request_info_id = i.request_info_id
				               AND (r.role_id = ${/session/@role_id} OR r.role_id IS NULL)
				               AND (r.user_id = ${/session/@user_id} OR r.user_id IS NULL)
				               AND (r.company_id = ${/session/@company_id} OR r.company_id IS NULL)
				               AND r.start_date < sysdate
				               AND (r.end_date > sysdate - 1 or r.end_date is null) )
			           and i.enable_flag = 'Y'
			         ORDER BY i.request_code ASC) 
         #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:parameters>
                <bm:parameter name="request_id" dataType="java.lang.Long" output="true" outputPath="/parameter/@request_id"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
            	begin
            	     fnd_request_pkg.insert_request(x_request_id => ${@request_id},
					                                p_request_info_id => ${@request_info_id},
					                                p_role_id => ${/session/@role_id},
                                                    p_company_id => ${/session/@company_id},
					                                p_user_id => ${/session/@user_id});
                end;
        	]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                BEGIN
                    fnd_request_pkg.submit_request(p_request_id => ${/parameter/@request_id}, p_user_id => ${/session/@user_id});
                END;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="request_code" queryExpression="upper(request_code) like  upper(&apos;%&apos;||${@request_code}||&apos;%&apos;)"/>
        <bm:query-field name="request_name" queryExpression="upper(request_name) like  upper(&apos;%&apos;||${@request_name}||&apos;%&apos;)"/>
        <bm:query-field name="description" queryExpression="upper(description) like  upper(&apos;%&apos;||${@description}||&apos;%&apos;)"/>
    </bm:query-fields>
</bm:model>
