Cost Distributions Query

SELECT a.
transaction_id ,
a.event_id,
a.ledger_id,
a.distribution_id ,
a.rec_trxn_id ,
a.dep_trxn_id ,
a.join_date join_date ,
a.accounting_date ,
a.cost_element_id ,
a.cost_element_code ,
a.cost_element_type ,
a.entered_debit ,
a.entered_credit ,
a.entered_currency_code ,
a.accounted_debit ,
a.accounted_credit ,
a.accounted_currency_code ,
a.eff_date eff_date ,
a.layer_quantity ,
a.code_combination_id ,
a.unit_of_measure ,
a.entity_code ,
a.event_class_code ,
a.distribution_line_id ,
a.element_type ,
NVL(acc.name, a.accounting_line_type) line_type ,
a.accounted_flag ,
SUM(NVL(a.accounted_debit,0)) over (partition BY a.transaction_id, a.join_date,
a.rec_trxn_id, a.distribution_id) total_accounted_debit ,
SUM(NVL(a.accounted_credit,0)) over (partition BY a.transaction_id, a.join_date,
a.rec_trxn_id, a.distribution_id) total_accounted_credit ,
SUM(NVL(a.entered_debit,0)) over (partition BY a.transaction_id, a.join_date,
a.rec_trxn_id, a.distribution_id) total_entered_debit ,
SUM(NVL(a.entered_credit,0)) over (partition BY a.transaction_id, a.join_date,
a.rec_trxn_id, a.distribution_id) total_entered_credit ,
a.entered_precision ,
a.entered_extended_precision ,
a.accounted_precision ,
a.accounted_extended_precision,
a.dr_cr_sign,
a.le_timezone_code,
a.int_project_status_code
FROM
(SELECT ccd.transaction_id ,
ccd.event_id,
ccd.ledger_id,
ccd.distribution_id ,
ccd.rec_trxn_id ,
ccd.dep_trxn_id ,
ccd.gl_date accounting_date ,
ccdl.accounting_line_type ,
ccdl.cost_element_id ,
cce.cost_element_code ,
cce.cost_element_type ,
DECODE(SIGN(ccdl.ENTERED_CURRENCY_AMOUNT),1, ccdl.ENTERED_CURRENCY_AMOUNT, 0,
0, NULL) entered_debit ,
DECODE(SIGN(ccdl.ENTERED_CURRENCY_AMOUNT),-1, -ccdl.ENTERED_CURRENCY_AMOUNT, 0,
0, NULL) entered_credit ,
ccdl.ENTERED_CURRENCY_CODE entered_currency_code ,
DECODE(SIGN(ccdl.ENTERED_CURRENCY_AMOUNT),1, ccdl.ledger_amount, 0, 0, NULL)
accounted_debit ,
DECODE(SIGN(ccdl.ENTERED_CURRENCY_AMOUNT),-1, -ccdl.ledger_amount, 0, 0, NULL)
accounted_credit ,
ccd.base_currency_code accounted_currency_code ,
ccd.eff_date ,
DECODE(ccd.additional_processing_code, 'ACQUISITION', ccd.eff_date, 'USER',
ccd.eff_date, 'LAYER', ccd.eff_date, 'ITEM', ccd.eff_date, 'WIP_COST',
ccd.eff_date, NVL(ct.transaction_date, NVL(cct.transaction_date,
NVL(caot.txn_account_date, NVL(crt.cost_date, NVL(cot.cost_date,
cwuet.event_date)))))) join_date ,
ccdl.code_combination_id ,
uom.unit_of_measure,
ccd.entity_code ,
ccd.event_class_code ,
ccdl.distribution_line_id ,
cet.meaning element_type ,
flv.meaning accounted_flag ,
gl.chart_of_accounts_id ,
NVL(fce.precision,2) entered_precision ,
NVL(fce.extended_precision,5) entered_extended_precision ,
NVL(fca.precision,2) accounted_precision ,
NVL(fca.extended_precision,5) accounted_extended_precision ,
NVL(CCDL.DISTRIBUTION_QUANTITY_RATE, 1) * ccd.layer_quantity layer_quantity,
ccdl.dr_cr_sign,
COALESCE(ct.le_timezone_code,cct.le_timezone_code,caot.le_timezone_code,crt.le_time
zone_code,cot.le_timezone_code,cwuet.le_timezone_code) as le_timezone_code ,
ccdl.pjc_txn_status_code int_project_status_code
FROM fusion.cst_cost_distributions ccd ,
fusion.cst_cost_distribution_lines ccdl ,
fusion.cst_transactions ct ,
fusion.cst_cogs_transactions cct ,
fusion.cst_acctg_only_txns caot ,
fusion.cst_resource_transactions crt ,
fusion.cst_operation_transactions cot ,
fusion.cst_wo_update_event_txns cwuet ,
fusion.cst_cost_elements_b cce ,
fusion.fnd_lookup_values_vl cet ,
fusion.gl_ledgers gl ,
fusion.fnd_currencies_b fce ,
fusion.fnd_currencies_b fca ,
fusion.fnd_lookup_values_vl flv,
fusion.inv_units_of_measure_vl uom
WHERE ccd.distribution_id = ccdl.distribution_id
AND cce.cost_element_id(+) = ccdl.cost_element_id
AND ccd.transaction_id = ct.transaction_id (+)
AND ccd.transaction_id = cct.transaction_id (+)
AND ccd.transaction_id = caot.acctg_only_txns_id (+)
AND ccd.transaction_id = crt.resource_transaction_id (+)
AND ccd.transaction_id = cot.operation_transaction_id (+)
AND ccd.transaction_id = cwuet.WO_UPDATE_EVENT_TXN_ID (+)
AND cet.lookup_type(+) = 'CST_COST_ELEMENT_TYPES'
AND cet.lookup_code(+) = cce.cost_element_type
AND gl.ledger_id(+) = ccd.ledger_id
AND fce.currency_code = ccdl.entered_currency_code
AND fca.currency_code = ccd.base_currency_code
AND ccd.accounted_flag = flv.lookup_code
AND flv.lookup_type = 'CST_ACCOUNTED_FLAG'
AND ccd.cost_transaction_uom = uom.uom_code(+)
AND ccd.cost_organization_id = ----Enter cost_organization_id
AND ccd.cost_book_id = ------ Enter cost_book_id
AND ccd.eff_date < TO_DATE( '5 Jan 2020', 'DD MON YYYY' ) ---Enter the
appropriate date
) a ,
fusion.xla_acct_line_types_vl acc
WHERE acc.application_id(+) = 707
AND acc.accounting_line_type_code(+) = 'S'
AND acc.accounting_line_code(+) = A.accounting_line_type
AND acc.event_class_code(+) = A.event_class_code
;

Cost Distributions Query

Uploaded by

Copyright:

Available Formats

Cost Distributions Query

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Cost Distributions Query

Uploaded by

Copyright:

Available Formats

SELECT a.

You might also like