1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
<?xml version="1.0" encoding="UTF-8"?>
<!--
$Author: gaoyang
$Date: 2013-5-14 下午01:23:36
$Revision: 1.0
$Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="CSH_TRANSACTION" needAccessControl="false">
<bm:fields>
<bm:field name="transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TRANSACTION_ID" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_ID"/>
<bm:field name="transaction_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_NUM" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_NUM"/>
<bm:field name="transaction_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_CATEGORY" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_CATEGORY"/>
<bm:field name="transaction_type" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="CSH513_WRITE_OFF_TYPE" lookupField="transaction_type_desc" physicalName="TRANSACTION_TYPE" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_TYPE"/>
<bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date" physicalName="TRANSACTION_DATE" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_DATE"/>
<bm:field name="penalty_calc_date" databaseType="DATE" datatype="java.util.Date" physicalName="PENALTY_CALC_DATE" prompt="CSH511.CSH_TRANSACTION.PENALTY_CALC_DATE"/>
<bm:field name="bank_slip_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BANK_SLIP_NUM" prompt="CSH511.CSH_TRANSACTION.BANK_SLIP_NUM"/>
<bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID" prompt="CSH511.CSH_TRANSACTION.COMPANY_ID"/>
<bm:field name="internal_period_num" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INTERNAL_PERIOD_NUM" prompt="CSH511.CSH_TRANSACTION.INTERNAL_PERIOD_NUM"/>
<bm:field name="period_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PERIOD_NAME" prompt="HLS.ACCOUNT_PERIOD_NAME"/>
<bm:field name="payment_method_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PAYMENT_METHOD_ID" prompt="HLS.PAYMENT_METHOD"/>
<bm:field name="distribution_set_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DISTRIBUTION_SET_ID" prompt="CSH511.CSH_TRANSACTION.DISTRIBUTION_SET_ID"/>
<bm:field name="cashflow_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="CASHFLOW_AMOUNT" prompt="CSH511.CSH_TRANSACTION.CASHFLOW_AMOUNT"/>
<bm:field name="currency_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_CODE" prompt="HLS.CURRENCY"/>
<bm:field name="transaction_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="TRANSACTION_AMOUNT" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_AMOUNT"/>
<bm:field name="exchange_rate_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXCHANGE_RATE_TYPE" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
<bm:field name="exchange_rate_quotation" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EXCHANGE_RATE_QUOTATION" prompt="CSH511.CSH_TRANSACTION.EXCHANGE_RATE_QUOTATION"/>
<bm:field name="exchange_rate" databaseType="NUMBER" datatype="java.lang.Long" physicalName="EXCHANGE_RATE" prompt="HLS.EXCHANGE_RATE"/>
<bm:field name="bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BANK_ACCOUNT_ID" prompt="CSH511.CSH_TRANSACTION.BANK_ACCOUNT_ID"/>
<bm:field name="bp_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_CATEGORY" prompt="CSH511.CSH_TRANSACTION.BP_CATEGORY"/>
<bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID" prompt="CSH511.CSH_TRANSACTION.BP_ID"/>
<bm:field name="bp_bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_BANK_ACCOUNT_ID" prompt="CSH511.CSH_TRANSACTION.BP_BANK_ACCOUNT_ID"/>
<bm:field name="bp_bank_account_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_BANK_ACCOUNT_NUM" prompt="CSH511.BANK_ACCOUNT_NUM"/>
<bm:field name="description" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DESCRIPTION" prompt="HLS.NOTE"/>
<bm:field name="handling_charge" databaseType="NUMBER" datatype="java.lang.Double" physicalName="HANDLING_CHARGE" prompt="CSH511.CSH_TRANSACTION.HANDLING_CHARGE"/>
<bm:field name="posted_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="POSTED_FLAG" prompt="CSH511.CSH_TRANSACTION.POSTED_FLAG"/>
<bm:field name="reversed_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REVERSED_FLAG" prompt="CSH511.CSH_TRANSACTION.REVERSED_FLAG"/>
<bm:field name="reversed_date" databaseType="DATE" datatype="java.util.Date" physicalName="REVERSED_DATE" prompt="CSH511.CSH_TRANSACTION.REVERSED_DATE"/>
<bm:field name="returned_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="RETURNED_FLAG" prompt="CSH511.CSH_TRANSACTION.RETURNED_FLAG"/>
<bm:field name="returned_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="RETURNED_AMOUNT" prompt="CSH511.CSH_TRANSACTION.RETURNED_AMOUNT"/>
<bm:field name="write_off_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WRITE_OFF_FLAG" prompt="CSH511.CSH_TRANSACTION.WRITE_OFF_FLAG"/>
<bm:field name="write_off_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="WRITE_OFF_AMOUNT" prompt="CSH511.CSH_TRANSACTION.WRITE_OFF_AMOUNT"/>
<bm:field name="full_write_off_date" databaseType="DATE" datatype="java.util.Date" physicalName="FULL_WRITE_OFF_DATE" prompt="CSH511.CSH_TRANSACTION.FULL_WRITE_OFF_DATE"/>
<bm:field name="twin_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TWIN_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.TWIN_CSH_TRX_ID"/>
<bm:field name="return_from_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RETURN_FROM_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.RETURN_FROM_CSH_TRX_ID"/>
<bm:field name="reversed_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REVERSED_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.REVERSED_CSH_TRX_ID"/>
<bm:field name="source_csh_trx_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_CSH_TRX_TYPE" prompt="CSH511.CSH_TRANSACTION.SOURCE_CSH_TRX_TYPE"/>
<bm:field name="source_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_CSH_TRX_ID"/>
<bm:field name="source_doc_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_DOC_CATEGORY"/>
<bm:field name="source_doc_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SOURCE_DOC_TYPE" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_TYPE"/>
<bm:field name="source_doc_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_DOC_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_ID"/>
<bm:field name="source_doc_line_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_DOC_LINE_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_LINE_ID"/>
<bm:field name="create_je_mothed" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_JE_MOTHED"/>
<bm:field name="create_je_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_JE_FLAG"/>
<bm:field name="gld_interface_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="GLD_INTERFACE_FLAG" prompt="CSH511.CSH_TRANSACTION.GLD_INTERFACE_FLAG"/>
<bm:field name="bp_code" expression="(select d.bp_code from hls_bp_master d where d.bp_id=t1.bp_id)" forInsert="false" forUpdate="false" prompt="HLS.BP_CODE"/>
<bm:field name="bp_name" expression="(select d.bp_name from hls_bp_master d where d.bp_id=t1.bp_id)" forInsert="false" forUpdate="false" prompt="HLS.BP_TITLE"/>
<bm:field name="payment_method_display" expression="(select cp.description from csh_payment_method cp where cp.payment_method_id=t1.payment_method_id)" forInsert="false" forUpdate="false" prompt="HLS.PAYMENT_METHOD"/>
<bm:field name="currency_name" expression="(select gc.currency_name from gld_currency_vl gc where gc.currency_code=t1.currency_code)" forInsert="false" forUpdate="false" prompt="HLS.CURRENCY"/>
<bm:field name="bp_category_display" expression="(select b.description from hls_bp_category b where b.bp_category=t1.bp_category)" forInsert="false" forUpdate="false" prompt="CSH511.CSH_TRANSACTION.BP_CATEGORY"/>
<bm:field name="exchange_rate_type_display" expression="(select tv.type_name from gld_exchangerate_types_vl tv where tv.type_code=t1.exchange_rate_type)" forInsert="false" forUpdate="false" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
<bm:field name="transaction_functional_amount" expression="gld_exchange_rate_pkg.get_currency_exchange(t1.transaction_amount,t1.exchange_rate,t1.exchange_rate_quotation)" forInsert="false" forUpdate="false" prompt="HLS.FUNCTIONAL_AMOUNT"/>
<bm:field name="bank_account_code" expression="(select a.bank_account_code from csh_bank_account a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_CODE"/>
<bm:field name="bank_account_name" expression="(select a.bank_account_name from csh_bank_account a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NAME"/>
<bm:field name="bank_account_num" expression="(select a.bank_account_num from csh_bank_account a where a.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NUM"/>
<bm:field name="bp_bank_account_code" expression="(select a.bank_account_code from hls_bp_master_bank_account a where a.bank_account_id = t1.bp_bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BP_BANK_ACCOUNT_CODE"/>
<bm:field name="bp_bank_account_name" expression="(select a.bank_account_name from hls_bp_master_bank_account a where a.bank_account_id = t1.bp_bank_account_id)" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NAME"/>
<bm:field name="functional_currency_code" expression="(select gb.functional_currency_code from fnd_companies fc, gld_set_of_books gb where fc.company_id = ${/session/@company_id} and fc.set_of_books_id = gb.set_of_books_id)" forInsert="false" forUpdate="false"/>
<bm:field name="rate_method_code" expression="(select t.rate_method_code from gld_exchangerate_types t where t.type_code=t1.exchange_rate_type)" forInsert="false" forUpdate="false"/>
<bm:field name="zero_amounts_allowed" expression="(select d.zero_amounts_allowed from csh_bank_account_v d where d.bank_account_id=t1.bank_account_id)" forInsert="false" forUpdate="false"/>
<bm:field name="transaction_type_desc" forInsert="false" forUpdate="false"/>
<bm:field name="unwrite_off_amount" expression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0))" forInsert="false" forUpdate="false"/>
<bm:field name="precision" expression="(select g.precision from gld_currency g where g.currency_code =t1.currency_code)" forInsert="false" forUpdate="false"/>
<bm:field name="reverse_amount" expression="(-1*t1.transaction_amount)" forInsert="false" forUpdate="false" prompt="CSH511.REVERSE_AMOUNT"/>
<bm:field name="bp_id_agent_level1" expression="(select bp_id_agent_level1 from con_contract_lv a where a.contract_id=t1.source_doc_id)" forInsert="false" forUpdate="false"/>
<bm:field name="bp_id_agent_n" expression="(select bp_id_agent_level1_n from con_contract_lv a where a.contract_id=t1.source_doc_id)" forInsert="false" forUpdate="false"/>
<bm:field name="bp_id_tenant" expression="(select bp_id_tenant from con_contract_lv a where a.contract_id=t1.source_doc_id)" forInsert="false" forUpdate="false"/>
<bm:field name="bp_id_tenant_desc" expression="(select h.bp_code||'-'||h.bp_name from hls_bp_master h,con_contract_bp c where c.contract_id=t1.source_doc_id and c.bp_id = h.bp_id and c.bp_category = 'TENANT')" forInsert="false" forUpdate="false"/>
<bm:field name="contract_id" expression="(select contract_id from con_contract_lv a where a.contract_id=t1.source_doc_id)" forInsert="false" forUpdate="false"/>
<bm:field name="cashflow_id" expression="(select cashflow_id from con_contract_cashflow a where a.cashflow_id=source_doc_line_id)" forInsert="false" forUpdate="false"/>
<bm:field name="contract_number" expression="(select contract_number from con_contract a where a.contract_id=t1.source_doc_id )" forInsert="false" forUpdate="false"/>
<bm:field name="due_amount" expression="(select nvl(due_amount,0) from con_contract_cashflow a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)" forInsert="false" forUpdate="false"/>
<bm:field name="due_date" databaseType="DATE" datatype="java.util.Date" expression="(select due_date from con_contract_cashflow a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)" forInsert="false" forUpdate="false"/>
<bm:field name="received_amount" expression="(select nvl(sum(a.write_off_due_amount),0) from csh_write_off a where a.csh_transaction_id = t1.transaction_id and a.write_off_type='RECEIPT_CREDIT' and a.reversed_flag !='R')" forInsert="false" forUpdate="false"/>
<bm:field name="cf_item" expression="(select v.cf_item from con_contract_cashflow_all_v v where v.cashflow_id = t1.source_doc_line_id and v.contract_id = t1.source_doc_id)" forInsert="false" forUpdate="false"/>
<bm:field name="cf_item_desc" expression="(select v.cf_item_desc from con_contract_cashflow_all_v v where v.cashflow_id = t1.source_doc_line_id and v.contract_id = t1.source_doc_id)" forInsert="false" forUpdate="false"/>
</bm:fields>
<bm:features>
<f:standard-who/>
</bm:features>
<bm:query-fields>
<bm:query-field field="reversed_flag" queryOperator="="/>
<bm:query-field name="deposit_amount_from" queryExpression="(select due_amount from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)>=${@deposit_amount_from}"/>
<bm:query-field name="deposit_amount_to" queryExpression="(select due_amount from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)<=${@deposit_amount_to}"/>
<bm:query-field field="bp_id_agent_level1" queryExpression="(select bp_id_agent_level1 from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)=${@bp_id_agent_level1}"/>
<bm:query-field field="bp_id_tenant" queryExpression="(select bp_id_tenant from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)=${@bp_id_tenant}"/>
<bm:query-field name="due_date_from" queryExpression="(select due_date from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id) >= to_date(${@due_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="due_date_to" queryExpression="(select due_date from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id) <= to_date(${@due_date_to},'yyyy-mm-dd')"/>
<bm:query-field field="contract_id" queryExpression="(select contract_id from csh_deposit_write_off_v a where a.contract_id=t1.source_doc_id and a.cashflow_id=source_doc_line_id)=${@contract_id}"/>
</bm:query-fields>
<bm:primary-key>
<bm:pk-field name="transaction_id"/>
</bm:primary-key>
<bm:data-filters>
<bm:data-filter name="query" expression="(t1.write_off_flag='FULL' and t1.company_id=${/session/@company_id} and t1.transaction_amount = 0)"/>
</bm:data-filters>
</bm:model>