Scribd
Upload
66 views1 page

3

This SQL query selects data from three tables - cst_attr_onhand_details, cst_attr_onhand_details, and cst_cost_elements_b. It groups the results by several identifier fields and calculates sums of cost amounts from the current and prior periods to determine totals, changes, and unaccounted amounts at different levels for inventory costing.

Uploaded by

khaled_ghrbia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
66 views1 page

3

This SQL query selects data from three tables - cst_attr_onhand_details, cst_attr_onhand_details, and cst_cost_elements_b. It groups the results by several identifier fields and calculates sums of cost amounts from the current and prior periods to determine totals, changes, and unaccounted amounts at different levels for inventory costing.

Uploaded by

khaled_ghrbia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

SELECT caod.

cost_org_id,
caod.cost_book_id,
caod.val_unit_id,
caod.period_name,
caod.inventory_org_id,
caod.subinventory_code,
caod.inventory_item_id,
ce.cost_element_code cost_element_code,
MAX(caod.currency_code) currency_code,
MAX(caod.cost_method_code) cost_method_code,
SUM(caod.amount + caod.writeoff_amount) curr_total_costed_amount,
SUM(NVL(caod1.amount + caod1.writeoff_amount, 0)) prior_total_costed_amount,
SUM((caod.amount + caod.writeoff_amount) -
(NVL(caod1.amount + caod1.writeoff_amount, 0))) costed_amount,
SUM((caod.acctd_amount + caod.acctd_writeoff_amount) -
NVL((caod1.acctd_amount + caod1.acctd_writeoff_amount), 0))
acctd_amount,
SUM((((caod.amount + caod.writeoff_amount) -
NVL((caod1.amount + caod1.writeoff_amount), 0)) -
((caod.acctd_amount + caod.acctd_writeoff_amount +
caod.excl_from_acctg_amount + caod.excl_from_acctg_wo_amount) -
NVL((caod1.acctd_amount + caod1.acctd_writeoff_amount +
caod1.excl_from_acctg_amount +
caod1.excl_from_acctg_wo_amount),
0)))) AS unacctd_amount
FROM cst_attr_onhand_details caod,
cst_attr_onhand_details caod1,
cst_cost_elements_b ce
WHERE caod.cost_element_id = ce.cost_element_id
AND caod.prior_val_detail_id = caod1.val_detail_id(+)
AND caod.cost_org_id = caod1.cost_org_id(+)
AND caod.cost_book_id = caod1.cost_book_id(+)
GROUP BY caod.cost_org_id,
caod.cost_book_id,
caod.val_unit_id,
caod.period_name,
caod.inventory_org_id,
caod.subinventory_code,
caod.inventory_item_id,
ce.cost_element_code

You might also like