CML_TROP_CHARGES

Details

Schema: FUSION
Object owner: CML
Object type: VIEW

Columns

Name
TRADE_OPERATION_ID TRADE_OPERATION_NUMBER TRADE_OPERATION_CHARGE_ID CHARGE_LINE_NUMBER CHARGE_ID CHARGE_NAME VENDOR_ID PARTY_NAME CURRENCY_CODE TOTAL_ESTIMATED_AMOUNT TOTAL_ACTUAL_AMOUNT

Name

TRADE_OPERATION_ID

TRADE_OPERATION_NUMBER

TRADE_OPERATION_CHARGE_ID

CHARGE_LINE_NUMBER

CHARGE_ID

CHARGE_NAME

VENDOR_ID

PARTY_NAME

CURRENCY_CODE

TOTAL_ESTIMATED_AMOUNT

TOTAL_ACTUAL_AMOUNT

Query

SQL_Statement
select ctoc.trade_operation_id, cto.trade_operation_number, ctoc.trade_operation_charge_id, ctoc.charge_line_number, chvl.charge_id, chvl.name charge_name, ctoc.vendor_id, hz.party_name, cto.currency_code, sum( nvl(ctoc.trop_doc_currency_conv_rate,0) * (nvl(ctce.entered_amount,0) + nvl(ctce.nonrecoverable_tax,0)) ) as Total_Estimated_Amount, sum( nvl(ctoc.act_trop_doc_curr_conv_rate,0) * (nvl(ccia.func_currency_amount,0) + nvl(ccia.func_currency_tax,0))) as Total_Actual_Amount from cml_trade_operations cto, cml_trade_operation_charges ctoc, cml_trop_charge_estimates ctce, (select trade_operation_charge_id, sum(nvl(func_currency_amount,0)) as func_currency_amount,sum(nvl(func_currency_tax,0)) as func_currency_tax from cml_charge_invoice_assocs where active_flag='Y' and association_status = 10 group by trade_operation_charge_id) ccia, cml_charges_vl chvl,poz_suppliers poz, hz_parties hz where cto.trade_operation_id = ctoc.trade_operation_id and ctce.trade_operation_charge_id(+) = ctoc.trade_operation_charge_id and ctoc.line_status_code <> 'CANCELED' and ctce.active_flag(+) = 'Y' and ccia.trade_operation_charge_id(+) = ctoc.trade_operation_charge_id and ctoc.charge_id = chvl.charge_id and ctoc.vendor_id = poz.vendor_id and poz.party_id = hz.party_id group by ctoc.trade_operation_id, cto.trade_operation_number, ctoc.trade_operation_charge_id, ctoc.charge_line_number, chvl.charge_id, chvl.name, ctoc.vendor_id, hz.party_name, cto.currency_code

SQL_Statement

select ctoc.trade_operation_id, cto.trade_operation_number, ctoc.trade_operation_charge_id, ctoc.charge_line_number,

chvl.charge_id, chvl.name charge_name, ctoc.vendor_id, hz.party_name, cto.currency_code,

sum( nvl(ctoc.trop_doc_currency_conv_rate,0) * (nvl(ctce.entered_amount,0) + nvl(ctce.nonrecoverable_tax,0)) ) as Total_Estimated_Amount,

sum( nvl(ctoc.act_trop_doc_curr_conv_rate,0) * (nvl(ccia.func_currency_amount,0) + nvl(ccia.func_currency_tax,0))) as Total_Actual_Amount

from cml_trade_operations cto, cml_trade_operation_charges ctoc, cml_trop_charge_estimates ctce,

(select trade_operation_charge_id, sum(nvl(func_currency_amount,0)) as func_currency_amount,sum(nvl(func_currency_tax,0)) as func_currency_tax from cml_charge_invoice_assocs where active_flag='Y' and association_status = 10

group by trade_operation_charge_id) ccia,

cml_charges_vl chvl,poz_suppliers poz, hz_parties hz

where cto.trade_operation_id = ctoc.trade_operation_id and ctce.trade_operation_charge_id(+) = ctoc.trade_operation_charge_id and ctoc.line_status_code <> 'CANCELED' and

ctce.active_flag(+) = 'Y' and ccia.trade_operation_charge_id(+) = ctoc.trade_operation_charge_id and ctoc.charge_id = chvl.charge_id and

ctoc.vendor_id = poz.vendor_id and poz.party_id = hz.party_id

group by ctoc.trade_operation_id, cto.trade_operation_number, ctoc.trade_operation_charge_id, ctoc.charge_line_number,

chvl.charge_id, chvl.name, ctoc.vendor_id, hz.party_name, cto.currency_code