CMR_TRANSACTION_DETAILS

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
ACCT_LT TXN_DT BU_ID BU_NAME CURRENCY ITEM_ID PO_ID PO BILL2BU_ID CAT_ID CAT_NAME NOTACCTDEBIT NOTACCTCREDIT ACCTDEBIT ACCTCREDIT

Name

ACCT_LT

TXN_DT

BU_ID

BU_NAME

CURRENCY

ITEM_ID

PO_ID

PO

BILL2BU_ID

CAT_ID

CAT_NAME

NOTACCTDEBIT

NOTACCTCREDIT

ACCTDEBIT

ACCTCREDIT

Query

SQL_Statement
select crd.accounting_line_type as acct_lt, cre.transaction_date as txn_dt, fabu.organization_id as bu_id, fabu.name as bu_name, crd.entered_currency_code as currency, cre.inventory_item_id as item_id, cre.cmr_po_distribution_id as po_id, cre.source_doc_number as po, cre.bill_to_business_unit_id as bill2bu_id, ec.category_id as cat_id, ec.category_name as cat_name, sum(decode(accounted_flag, 'F', 0, crd.accounteddebitamt)) notacctdebit, sum(decode(accounted_flag, 'F', 0, crd.accountedcreditamt)) notacctcredit, sum(decode(accounted_flag, 'F', crd.accounteddebitamt, 0)) acctdebit, sum(decode(accounted_flag, 'F', crd.accountedcreditamt, 0)) acctcredit from cmr_rcv_events cre, cmr_rcv_distributions_v crd, cmr_rcv_event_costs crec, hr_organization_units_f_tl fabu, egp_item_categories eic, egp_default_category_sets edc, egp_categories_vl ec where cre.business_unit_id = fabu.organization_id and fabu.LANGUAGE = USERENV('LANG') and TRUNC(sysdate) between fabu.effective_start_date and fabu.EFFECTIVE_END_DATE and cre.accounting_event_id = crd.accounting_event_id and cre.accounting_event_id = crec.accounting_event_id and crec.ACCOUNTING_EVENT_ID =crd.ACCOUNTING_EVENT_ID and crec.EVENT_COST_ID = crd.EVENT_COST_ID and cre.cmr_po_distribution_id is NOT NULL and cre.inventory_item_id = eic.inventory_item_id AND cre.inventory_org_id = eic.organization_id and eic.category_set_id = edc.category_set_id AND edc.functional_area_id = 2 AND eic.category_id = ec.category_id group by crd.accounting_line_type, cre.transaction_date, fabu.organization_id, fabu.name, crd.entered_currency_code, cre.inventory_item_id, cre.cmr_po_distribution_id, cre.source_doc_number, cre.bill_to_business_unit_id, ec.category_id, ec.category_name

SQL_Statement

select crd.accounting_line_type as acct_lt,

cre.transaction_date as txn_dt,

fabu.organization_id as bu_id,

fabu.name as bu_name,

crd.entered_currency_code as currency,

cre.inventory_item_id as item_id,

cre.cmr_po_distribution_id as po_id,

cre.source_doc_number as po,

cre.bill_to_business_unit_id as bill2bu_id,

ec.category_id as cat_id,

ec.category_name as cat_name,

sum(decode(accounted_flag, 'F', 0, crd.accounteddebitamt)) notacctdebit,

sum(decode(accounted_flag, 'F', 0, crd.accountedcreditamt)) notacctcredit,

sum(decode(accounted_flag, 'F', crd.accounteddebitamt, 0)) acctdebit,

sum(decode(accounted_flag, 'F', crd.accountedcreditamt, 0)) acctcredit

from cmr_rcv_events cre,

cmr_rcv_distributions_v crd,

cmr_rcv_event_costs crec,

hr_organization_units_f_tl fabu,

egp_item_categories eic,

egp_default_category_sets edc,

egp_categories_vl ec

where cre.business_unit_id = fabu.organization_id

and fabu.LANGUAGE = USERENV('LANG') and TRUNC(sysdate) between fabu.effective_start_date and fabu.EFFECTIVE_END_DATE

and cre.accounting_event_id = crd.accounting_event_id

and cre.accounting_event_id = crec.accounting_event_id

and crec.ACCOUNTING_EVENT_ID =crd.ACCOUNTING_EVENT_ID

and crec.EVENT_COST_ID = crd.EVENT_COST_ID

and cre.cmr_po_distribution_id is NOT NULL

and cre.inventory_item_id = eic.inventory_item_id

AND cre.inventory_org_id = eic.organization_id

and eic.category_set_id = edc.category_set_id

AND edc.functional_area_id = 2

AND eic.category_id = ec.category_id

group by crd.accounting_line_type,

cre.transaction_date,

fabu.organization_id,

fabu.name,

crd.entered_currency_code,

cre.inventory_item_id,

cre.cmr_po_distribution_id,

cre.source_doc_number,

cre.bill_to_business_unit_id,

ec.category_id,

ec.category_name