<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:o="leaf.database.local.oracle" xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="FND_COMPANY_PROVINCE">
    <bm:fields>
        <bm:field name="relate_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RELATE_ID" prompt="FND_COMPANY_PROVINCE.RELATE_ID"/>
        <bm:field name="company_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COMPANY_CODE" prompt="FND_COMPANY_PROVINCE.COMPANY_CODE"/>
        <bm:field name="province_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROVINCE_ID" prompt="FND_COMPANY_PROVINCE.PROVINCE_ID"/>
        <bm:field name="company_name"/>
        <bm:field name="province_id_n"/>
        <bm:field name="signing_location"/>
        <bm:field name="con_extra_code"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
        <o:sequence-pk/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="relate_id"/>
    </bm:primary-key>
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
    			 	declare
    			 	  v_relate_id number;
    				BEGIN
    				  if ${@relate_id} is null then
    				    v_relate_id:= FND_COMPANY_PROVINCE_S.NEXTVAL;
    				    INSERT INTO FND_COMPANY_PROVINCE
						    (RELATE_ID,
						     COMPANY_CODE,
						     PROVINCE_ID,
						     CREATED_BY,
						     CREATION_DATE,
						     LAST_UPDATED_BY,
						     LAST_UPDATE_DATE,
						     signing_location,
						     con_extra_code)
						  VALUES
						    (v_relate_id,
						     ${@company_code},
						     ${@province_id},
						     ${/session/@user_id},
						     sysdate,
						     ${/session/@user_id},
						     sysdate,
						     ${@signing_location},
						     ${@con_extra_code});
    				  else
    				  	v_relate_id := ${@relate_id};
    				    UPDATE FND_COMPANY_PROVINCE t1
						SET t1.PROVINCE_ID=${@province_id},t1.LAST_UPDATED_BY=${/session/@user_id},t1.LAST_UPDATE_DATE=sysdate,
						    t1.signing_location=${@signing_location},
						    t1.con_extra_code = ${@con_extra_code}
						WHERE t1.RELATE_ID = ${@relate_id};
    				  end if;
    				  select v_relate_id into ${@relate_id} from dual;
					END;
    			
    		]]></bm:update-sql>
            <bm:parameters>
                <bm:parameter name="relate_id" input="true" output="true"/>
            </bm:parameters>
        </bm:operation>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
    			
    				select fc.relate_id,
					       scv.code_value company_code,
					       scv.code_value_name company_name,
					       fc.province_id,
					       fc.con_extra_code,
					       (select fp.description
					          from fnd_province fp
					         where fp.province_id = fc.province_id) province_id_n,
					       fc.signing_location  
					  from fnd_company_province fc, sys_code_values_v scv
					 #WHERE_CLAUSE#
    			
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter name="init_filter" enforceOperations="query" expression="scv.code = &apos;BRANCH_COMPANY&apos; and scv.code_value_enabled_flag=&apos;Y&apos; and fc.company_code(+) = scv.code_value"/>
    </bm:data-filters>
    <bm:query-fields>
        <bm:query-field name="province_id" queryExpression="fc.province_id=${@province_id}"/>
        <bm:query-field name="company_code" queryExpression="scv.code_value=${@company_code}"/>
    </bm:query-fields>
</bm:model>
