CST_WORK_ORDER_BY_STATUS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID INVENTORY_ORGANIZATION_ID WO_STATUS_CODE WO_STATUS_NAME CONV_WIP_AMOUNT REPORTING_CURRENCY_CODE WIP_AMOUNT COST_ORG_CURRENCY_CODE

Name

COST_ORG_ID

INVENTORY_ORGANIZATION_ID

WO_STATUS_CODE

WO_STATUS_NAME

CONV_WIP_AMOUNT

REPORTING_CURRENCY_CODE

WIP_AMOUNT

COST_ORG_CURRENCY_CODE

Query

SQL_Statement
WITH w AS ( SELECT wo.cst_work_order_id, wo.inventory_organization_id, wos.wo_system_status_code as wo_status_code, flvl.meaning as wo_status_name FROM cst_work_orders wo, cst_wo_update_events woe, wie_wo_statuses_vl wos, fnd_lookup_values_vl flvl WHERE wo.cst_work_order_id = woe.cst_work_order_id (+) AND nvl(woe.latest_status_flag, 'Y') = 'Y' AND nvl(woe.work_order_status_id, wo.work_order_status_id) = wos.wo_status_id AND wos.wo_system_status_code IN ('COMPLETED','RELEASED','ON_HOLD') AND flvl.lookup_type = 'ORA_WIE_WO_SYSTEM_STATUS' AND flvl.lookup_code = wos.wo_system_status_code ) SELECT wc.cost_org_id, w.inventory_organization_id, w.wo_status_code, w.wo_status_name, sum(wc.quantity * wc.wip_txn_sign * wc.unit_cost) conv_wip_amount, wc.currency_code reporting_currency_code, sum(wc.quantity * wc.wip_txn_sign * wc.unit_cost) wip_amount, wc.currency_code cost_org_currency_code FROM cst_work_order_costs wc, w, cst_cost_org_books b WHERE wc.cost_org_id = b.cost_org_id AND wc.cost_book_id = b.cost_book_id AND b.primary_book_flag = 'Y' AND wc.cst_work_order_id = w.cst_work_order_id AND decode(wc.wip_cost_type, 'SCRAP', wc.absorption_type, 2) = 2 GROUP BY wc.cost_org_id, w.inventory_organization_id, w.wo_status_code, w.wo_status_name, wc.currency_code

SQL_Statement

WITH w AS

(

SELECT wo.cst_work_order_id,

wo.inventory_organization_id,

wos.wo_system_status_code as wo_status_code,

flvl.meaning as wo_status_name

FROM cst_work_orders wo,

cst_wo_update_events woe,

wie_wo_statuses_vl wos,

fnd_lookup_values_vl flvl

WHERE wo.cst_work_order_id = woe.cst_work_order_id (+)

AND nvl(woe.latest_status_flag, 'Y') = 'Y'

AND nvl(woe.work_order_status_id, wo.work_order_status_id) = wos.wo_status_id

AND wos.wo_system_status_code IN ('COMPLETED','RELEASED','ON_HOLD')

AND flvl.lookup_type = 'ORA_WIE_WO_SYSTEM_STATUS'

AND flvl.lookup_code = wos.wo_system_status_code

)

SELECT wc.cost_org_id,

w.inventory_organization_id,

w.wo_status_code,

w.wo_status_name,

sum(wc.quantity * wc.wip_txn_sign * wc.unit_cost) conv_wip_amount,

wc.currency_code reporting_currency_code,

sum(wc.quantity * wc.wip_txn_sign * wc.unit_cost) wip_amount,

wc.currency_code cost_org_currency_code

FROM cst_work_order_costs wc,

w,

cst_cost_org_books b

WHERE wc.cost_org_id = b.cost_org_id

AND wc.cost_book_id = b.cost_book_id

AND b.primary_book_flag = 'Y'

AND wc.cst_work_order_id = w.cst_work_order_id

AND decode(wc.wip_cost_type, 'SCRAP', wc.absorption_type, 2) = 2

GROUP BY wc.cost_org_id,

w.inventory_organization_id,

w.wo_status_code,

w.wo_status_name,

wc.currency_code