1. Tables and Views for SCM
  2. CML_TROP_CHARGE_ESTIMATES_V

CML_TROP_CHARGE_ESTIMATES_V

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

ENTERED_CURRENCY_CODE

ANALYSIS_GROUP_ID

ANALYSIS_CODE

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,

ctoc.entered_currency_code,

-1 as analysis_group_id, null as analysis_code

from cml_trade_operations cto, cml_trade_operation_charges ctoc, cml_trop_charge_estimates ctce,

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

ctce.active_flag(+) = 'Y' and ctoc.line_status_code <> 'CANCELED' 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, ctoc.entered_currency_code, cto.currency_code

UNION ALL

select x.trade_operation_id, x.trade_operation_number, x.trade_operation_charge_id, x.charge_line_number,

x.charge_id, x.name charge_name, x.vendor_id, x.party_name, x.currency_code,

x.Total_Estimated_Amount, x.entered_currency_code,

z.analysis_group_id, z.analysis_code

from

(select 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,

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

ctoc.entered_currency_code,

cacb.analysis_group_id, cacb.analysis_id

from cml_trade_operations cto, cml_trade_operation_charges ctoc, cml_trop_charge_estimates ctce,

cml_charges_vl chvl,poz_suppliers poz, hz_parties hz, cst_analysis_codes_b cacb

where cto.trade_operation_id = ctoc.trade_operation_id and

ctce.trade_operation_charge_id(+) = ctoc.trade_operation_charge_id and ctce.active_flag(+) = 'Y' and ctoc.line_status_code <> 'CANCELED'

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

cacb.default_analysis_code_flag = 'Y'

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, ctoc.entered_currency_code, cto.currency_code,

cacb.analysis_group_id, cacb.analysis_id ) x, cml_charge_analysis_codes y, cst_analysis_codes_b z

where x.charge_id = y.charge_id (+)

AND x.analysis_group_id = y.analysis_group_id (+)

AND z.analysis_id = NVL(y.analysis_code_id,x.analysis_id)