<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
          select * from(
            SELECT pci.description,
			       pci.note,
			       'CHANCE' choice_flag,
			       lgt.seq_num seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pp.check_id,
                                                        p_table_name => 'PRJ_CDD_ITEM_CHECK')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pp.check_id,
	                                               p_table_name => 'PRJ_CDD_ITEM_CHECK') file_name
			  FROM (SELECT pcf.check_id,
			               pcf.document_id,
			               pcf.document_table,
			               pck.cdd_item_id
			          FROM prj_cdd_item_doc_ref pcf, prj_cdd_item_check pck
			         WHERE pcf.document_table = 'PRJ_CHANCE'
			           AND pcf.document_id =  ${@head_id}
			           AND pck.check_id = pcf.check_id) pp,
			       prj_cdd_item pci,
			       prj_cdd_item_list_grp_tab lgt,
			       prj_cdd_item_tab_group tg
			 WHERE pci.cdd_item_id = lgt.cdd_item_id
			   AND lgt.TAB_GROUP_ID = tg.TAB_GROUP_ID(+)
			   AND nvl(lgt.enabled_flag, 'Y') = 'Y'
			   AND nvl(tg.enabled_flag, 'Y') = 'Y'
			   AND tg.tab_group_id = (select pcitg.tab_group_id from prj_cdd_item_tab_group pcitg 
			                           where pcitg.tab_group = 'CHANCE' and pcitg.enabled_flag = 'Y')
			   AND pci.cdd_item_id = pp.cdd_item_id(+)
			   AND (nvl(pci.sys_flag, 'N') = 'Y' or
			       (nvl(pci.sys_flag, 'N') = 'N' and pp.check_id is not null))
			   AND pci.cdd_list_id = (select pc.cdd_list_id from prj_chance pc where pc.chance_id = ${@head_id})
			   AND pci.enabled_flag = 'Y'
			   and lgt.cdd_list_id = pci.cdd_list_id 
			   and ${@choice_flag}='CHANCE' 
		UNION
			SELECT pci.description,
			       pci.note,
			       'PROJECT' choice_flag,
			       lgt.seq_num seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pp.check_id,
                                                        p_table_name => 'PRJ_CDD_ITEM_CHECK')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pp.check_id,
	                                               p_table_name => 'PRJ_CDD_ITEM_CHECK') file_name
			  FROM (SELECT pcf.check_id,
			               pcf.document_id,
			               pcf.document_table,
			               pck.cdd_item_id
			          FROM prj_cdd_item_doc_ref pcf, prj_cdd_item_check pck
			         WHERE pcf.document_table = 'PRJ_PROJECT'
			           AND pcf.document_id = ${@head_id}
			           AND pck.check_id = pcf.check_id) pp,
			       prj_cdd_item pci,
			       prj_cdd_item_list_grp_tab lgt,
			       prj_cdd_item_tab_group tg
			 WHERE pci.cdd_item_id = lgt.cdd_item_id
			   AND lgt.TAB_GROUP_ID = tg.TAB_GROUP_ID(+)
			   AND nvl(lgt.enabled_flag, 'Y') = 'Y'
			   AND nvl(tg.enabled_flag, 'Y') = 'Y'
			   AND tg.tab_group_id = (select pcitg.tab_group_id from prj_cdd_item_tab_group pcitg 
			                           where pcitg.tab_group = 'PROJECT' and pcitg.enabled_flag = 'Y')
			   AND pci.cdd_item_id = pp.cdd_item_id(+)
			   AND (nvl(pci.sys_flag, 'N') = 'Y' or
			       (nvl(pci.sys_flag, 'N') = 'N' and pp.check_id is not null))
			   AND pci.cdd_list_id = (select pp.cdd_list_id from prj_project pp where pp.project_id = ${@head_id})
			   AND pci.enabled_flag = 'Y'
			   and lgt.cdd_list_id = pci.cdd_list_id
			   and ${@choice_flag}='PROJECT'
		UNION
		    SELECT pci.description,
			       pci.note,
			       'PROJECT' choice_flag,
			       pcc.check_id seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pcc.check_id,
                                                        p_table_name => 'PRJ_CDD_ITEM_CHECK')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pcc.check_id,
	                                               p_table_name => 'PRJ_CDD_ITEM_CHECK') file_name
			  FROM prj_cdd_item pci, prj_cdd_item_check pcc, prj_cdd_item_doc_ref cf
			 WHERE pci.cdd_item_id = pcc.cdd_item_id(+)
			   AND pci.enabled_flag = 'Y'
			   AND cf.check_id = pcc.check_id
			   AND cf.document_table = 'PRJ_PROJECT_DOC'
			   AND cf.document_id = ${@head_id}
			   AND ${@choice_flag}='PROJECT' 
		UNION
		    SELECT pci.description,
			       pci.note,
			       'PROJECT' choice_flag,
			       pcc.check_id seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pcc.check_id,
                                                        p_table_name => 'PRJ_PROJECT_APPROVAL')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pcc.check_id,
	                                               p_table_name => 'PRJ_PROJECT_APPROVAL') file_name
			  FROM prj_cdd_item pci, prj_cdd_item_check pcc, prj_cdd_item_doc_ref cf
			 WHERE pci.cdd_item_id = pcc.cdd_item_id(+)
			   AND pci.enabled_flag = 'Y'
			   AND cf.check_id = pcc.check_id
			   AND cf.document_table = 'PRJ_PROJECT_APPROVAL'
			   AND cf.document_id = ${@head_id}
			   AND ${@choice_flag}='PROJECT' 
		UNION
			SELECT t1.description,
			       t1.note,
			       'PROJECT' choice_flag,
			       t1.condition_id seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => t1.condition_id,
                                                        p_table_name => 'BGFL_CSH_PAYMENT_CONDITIONS')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => t1.condition_id,
	                                               p_table_name => 'BGFL_CSH_PAYMENT_CONDITIONS') file_name
			  FROM BGFL_CSH_PAYMENT_CONDITIONS t1
			 WHERE t1.project_id = ${@head_id}
			   AND exists (select 1 from fnd_atm_attachment_multi faam,fnd_atm_attachment faa
                      where faam.table_pk_value = t1.condition_id and faam.table_name = 'BGFL_CSH_PAYMENT_CONDITIONS'
                        and faa.attachment_id = faam.attachment_id )
			   AND ${@choice_flag}='PROJECT' 
		UNION
			SELECT null description,
			       null note,
			       'PROJECT' choice_flag,
			       pcc.check_id seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pcc.check_id,
                                                        p_table_name => 'ZJ_WFL_APPROVE_RECORD')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pcc.check_id,
	                                               p_table_name => 'ZJ_WFL_APPROVE_RECORD') file_name
			  FROM prj_cdd_item_check pcc, prj_project_lv ppl
			 WHERE ppl.approval_id = pcc.check_id
         	   AND ppl.project_id = ${@head_id}
			   AND ${@choice_flag}='PROJECT' 
		UNION
			SELECT pci.description,
			       pci.note,
			       'VIRTUAL' choice_flag,
			       lgt.seq_num seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pp.check_id,
                                                        p_table_name => 'PRJ_CDD_ITEM_CHECK')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pp.check_id,
	                                               p_table_name => 'PRJ_CDD_ITEM_CHECK') file_name
			  FROM (SELECT pcf.check_id,
			               pcf.document_id,
			               pcf.document_table,
			               pck.cdd_item_id
			          FROM prj_cdd_item_doc_ref pcf, prj_cdd_item_check pck
			         WHERE pcf.document_table = 'PRJ_PROJECT'
			           AND pcf.document_id = ${@head_id}
			           AND pck.check_id = pcf.check_id) pp,
			       prj_cdd_item pci,
			       prj_cdd_item_list_grp_tab lgt,
			       prj_cdd_item_tab_group tg
			 WHERE pci.cdd_item_id = lgt.cdd_item_id
			   AND lgt.TAB_GROUP_ID = tg.TAB_GROUP_ID(+)
			   AND nvl(lgt.enabled_flag, 'Y') = 'Y'
			   AND nvl(tg.enabled_flag, 'Y') = 'Y'
			   AND tg.tab_group in ('CONTRACT','CON_SIGN','CON_OTHER')
			   AND pci.cdd_item_id = pp.cdd_item_id(+)
			   AND (nvl(pci.sys_flag, 'N') = 'Y' or
			       (nvl(pci.sys_flag, 'N') = 'N' and pp.check_id is not null))
			   AND pci.cdd_list_id = (select pp.cdd_list_id from prj_project pp 
			   						   where pp.data_class = 'VIRTUAL_CON' and pp.project_id = ${@head_id})
			   AND pci.enabled_flag = 'Y'
			   and lgt.cdd_list_id = pci.cdd_list_id
			   and ${@choice_flag}='VIRTUAL' 
		UNION
			SELECT t1.description,
			       t1.note,
			       'CONTRACT' choice_flag,
			       t1.condition_id seq_num,
			       (select bgfl_special_parameter_pkg.get_con_condition_file_names(t1.condition_id)
			          from dual) AS attachment_id,
			       hn_report_pkg.get_prj_filr_name(p_check_id => t1.condition_id,
	                                               p_table_name => 'HN_CON_PAYMENT_CONDITIONS') file_name
			  FROM HN_CON_PAYMENT_CONDITIONS t1
			 WHERE t1.hd_id = (select hcp.hd_id from hn_con_payment_con_hd hcp 
			 					where hcp.data_class = 'NORMAL' and hcp.contract_id = ${@head_id})
			   AND exists (select 1 from fnd_atm_attachment_multi faam 
			                where faam.table_pk_value = t1.condition_id and faam.table_name = 'HN_CON_PAYMENT_CONDITIONS')
			   AND ${@choice_flag}='CONTRACT' 
		UNION
			SELECT pci.description,
			       pci.note,
			       'REPAYMENT' choice_flag,
			       lgt.seq_num seq_num,
			       (hn_report_pkg.get_prj_attachment_id(p_check_id => pp.check_id,
                                                        p_table_name => 'PRJ_CDD_ITEM_CHECK')) attachment_id,
	               hn_report_pkg.get_prj_filr_name(p_check_id => pp.check_id,
	                                               p_table_name => 'PRJ_CDD_ITEM_CHECK') file_name
			  FROM (SELECT pcf.check_id,
			               pcf.document_id,
			               pcf.document_table,
			               pck.cdd_item_id
			          FROM prj_cdd_item_doc_ref pcf, prj_cdd_item_check pck
			         WHERE pcf.document_table = 'CSH_PAYMENT_REQ_HD'
			           AND pcf.document_id = ${@head_id}
			           AND pck.check_id = pcf.check_id) pp,
			       prj_cdd_item pci,
			       prj_cdd_item_list_grp_tab lgt,
			       prj_cdd_item_tab_group tg
			 WHERE pci.cdd_item_id = lgt.cdd_item_id
			   AND lgt.TAB_GROUP_ID = tg.TAB_GROUP_ID(+)
			   AND nvl(lgt.enabled_flag, 'Y') = 'Y'
			   AND nvl(tg.enabled_flag, 'Y') = 'Y'
			   AND tg.tab_group = 'CSH_PAYMENT_DOC'
			   AND pci.cdd_item_id = pp.cdd_item_id(+)
			   AND (nvl(pci.sys_flag, 'N') = 'Y' or
			       (nvl(pci.sys_flag, 'N') = 'N' and pp.check_id is not null))
			   and pci.cdd_list_id = (select cprh.cdd_list_id from csh_payment_req_hd cprh where cprh.payment_req_id = ${@head_id})
			   AND pci.enabled_flag = 'Y'
			   and lgt.cdd_list_id = pci.cdd_list_id
			   AND ${@choice_flag}='REPAYMENT' 
			   ) t1 #WHERE_CLAUSE# order by t1.seq_num
   			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
