<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei  
    $Date: 2015-3-4 下午4:24:38  
    $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 * from
                    ( select 'CON_CONTRACT_CREDIT' document_table,
                    t.contract_id document_id,
                    t.document_category,
                    pci.cdd_item_id,
                    pci.cdd_list_id,
                    pci.cdd_item,
                    pci.description,
                    pci.enabled_flag,
                    pci.note,
                    pci.note2,
                    pci.note3,
                    pci.note4,
                    pci.note5,
                    NVL(pci.sys_flag, 'N') sys_flag,
                    pcc.paper_required,
                    pcc.attachment_required,
                    pcc.not_applicable,
                    pcc.check_id,
                    pci.order_seq,
                    pci.sign_required_flag,
                    hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => pcc.check_id,p_source_type => 'PRJ_CDD_ITEM_CHECK',p_user_id => ${/session/@user_id}) AS file_name
                    --(SELECT
                    --    MAX(fm.creation_date)
                    --FROM
                    --    fnd_atm_attachment_multi fm
                    --WHERE
                    --    fm.table_name     = 'PRJ_CDD_ITEM_CHECK' AND
                    --    fm.table_pk_value = to_char(pcc.check_id)
                    --) creation_date
                FROM
                    prj_cdd_item pci,
                    prj_cdd_item_check pcc,
                    con_contract t
                WHERE
                    pci.cdd_item_id  = pcc.cdd_item_id(+) AND
                    t.cdd_list_id    = pci.cdd_list_id AND
                    pci.cdd_list_id  = t.cdd_list_id AND
                    pci.enabled_flag = 'Y' AND
                      nvl(pci.lender_required_flag,'N') != 'Y' and
                    nvl(pci.lender_display_flag,'N') !='Y' and
                    (pci.cdd_item_id in (select t1.cdd_item_id from prj_cdd_item_list_grp_tab T1,PRJ_CDD_ITEM_TAB_GROUP T2 where t1.cdd_list_id=t.cdd_list_id
AND T2.TAB_GROUP='CONTRACT' and t2.tab_group_id=t1.tab_group_id) 
or pcc.check_id IN (SELECT check_id FROM prj_cdd_item_doc_ref WHERE document_id = t.contract_id
                    AND document_table = 'CON_CONTRACT_CREDIT')
                    ) and
                    t.contract_id    = (select ln.ref_doc_id 
                                       from csh_payment_req_hd hd,
                                             csh_payment_req_ln ln 
                                             where hd.payment_req_id=ln.payment_req_id
                                             and ln.ref_doc_category='CONTRACT'
                                             and hd.payment_req_id=${@payment_req_id}
                                             and rownum=1)) t1 
                   order by order_seq
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
