CST_B_ACCTD_ATTR_VALUES_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID BUSINESS_UNIT_ID INVENTORY_ORG_ID SUBINVENTORY_CODE LOCATOR_ID PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_CODE VAL_STRUCTURE_TYPE_CODE SNAPSHOT_DATE UOM_CODE CURRENCY_CODE EFF_TO_DATE COSTED_VALUE ACCOUNTED_VALUE UNACCOUNTED_VALUE EXCLUDED_FROM_ACCTG_VALUE

Query

SQL_Statement

SELECT cost_org_id, cost_book_id, inventory_item_id, val_unit_id, business_unit_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code, val_structure_type_code, snapshot_date, uom_code, currency_code, nvl(lead (snapshot_date, 1) OVER (PARTITION BY cost_org_id, cost_book_id, val_unit_id,inventory_item_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code ORDER BY snapshot_date), to_date('4712-12-31', 'yyyy-mm-dd')) eff_to_date, sum(costed_value) OVER (PARTITION BY cost_org_id, cost_book_id, val_unit_id,inventory_item_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code ORDER BY snapshot_date) costed_value, sum(accounted_value) OVER (PARTITION BY cost_org_id, cost_book_id,val_unit_id, inventory_item_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code ORDER BY snapshot_date) accounted_value, sum(unaccounted_value) OVER (PARTITION BY cost_org_id, cost_book_id, val_unit_id,inventory_item_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code ORDER BY snapshot_date) unaccounted_value, sum(excluded_from_acctg_value) OVER (PARTITION BY cost_org_id, cost_book_id, val_unit_id,inventory_item_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code ORDER BY snapshot_date) excluded_from_acctg_value FROM ( SELECT ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.val_unit_id, ct.business_unit_id, ct.inventory_org_id, cit.subinventory_code, cit.locator_id, cit.project_id, cit.task_id, cit.country_of_origin_code, vs.val_structure_type_code, decode(ct.period_name, NULL, trunc(nvl(l1.cost_date,nvl(ct.cost_date,ct.transaction_date))), cps.start_date) snapshot_date, sum(l1.unit_cost* l1.quantity) costed_value, sum(decode(nvl(ccd.accounted_flag, 'N'), 'F', l1.unit_cost * l1.quantity, 0)) accounted_value, sum(decode(nvl(ccd.accounted_flag, 'N'), 'F', 0, 'X', 0, l1.unit_cost * l1.quantity)) unaccounted_value, sum(decode(nvl(ccd.accounted_flag, 'N'), 'X', l1.unit_cost * l1.quantity, 0)) excluded_from_acctg_value, ct.uom_code, l1.currency_code FROM cst_layer_costs l1, cst_cost_distributions ccd, cst_transactions ct, cst_inv_transactions cit, cst_val_structures_b vs, cst_period_statuses cps WHERE nvl(l1.absorption_type, 1) = 1 AND l1.distribution_id = ccd.distribution_id (+) AND l1.transaction_id (+) = ct.transaction_id AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id (+) AND ct.val_structure_id = vs.val_structure_id AND DECODE(L1.COST_ORG_ID, NULL, CT.POSTED_FLAG, 'X') = 'X' AND CT.INTRANSIT_FLAG = 'N' AND ct.cost_org_id = cps.cost_org_id(+) AND ct.cost_book_id = cps.cost_book_id(+) AND ct.period_name = cps.period_name(+) GROUP BY ct.cost_org_id, ct.cost_book_id, ct.inventory_item_id, ct.val_unit_id, ct.business_unit_id, ct.inventory_org_id, cit.subinventory_code, cit.locator_id, cit.project_id, cit.task_id, cit.country_of_origin_code, vs.val_structure_type_code, decode(ct.period_name, NULL, trunc(nvl(l1.cost_date,nvl(ct.cost_date,ct.transaction_date))), cps.start_date), ct.uom_code, l1.currency_code )