1. Tables and Views for SCM
  2. CML_TROP_CHARGE_ACTUALS_V

CML_TROP_CHARGE_ACTUALS_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_ACTUAL_AMOUNT

INVOICE_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.act_trop_doc_curr_conv_rate,0) * (nvl(ccia.func_currency_amount,0) + nvl(ccia.func_currency_tax,0))) as Total_Actual_Amount,

ccia.invoice_currency_code,

-1 as analysis_group_id, null as analysis_code

from cml_trade_operations cto, cml_trade_operation_charges ctoc,

cml_charge_invoice_assocs ccia, cml_charges_vl chvl,poz_suppliers poz, hz_parties hz

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

ccia.association_status(+) = 10 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, ccia.invoice_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_Actual_Amount,

x.invoice_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.act_trop_doc_curr_conv_rate,0) * (nvl(ccia.func_currency_amount,0) + nvl(ccia.func_currency_tax,0))) as Total_Actual_Amount,

ccia.invoice_currency_code,

cacb.analysis_group_id, cacb.analysis_id

from cml_trade_operations cto, cml_trade_operation_charges ctoc,

cml_charge_invoice_assocs ccia, 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 ccia.trade_operation_charge_id(+) = ctoc.trade_operation_charge_id and ccia.active_flag(+) = 'Y' and ctoc.line_status_code <> 'CANCELED' and ccia.association_status(+) = 10

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, cto.currency_code, ccia.invoice_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)