ECC Process Manufacturing, Nonconformances Details

select
x.*
from
(
Select * from(Select ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, ORGANIZATION_ID, ORGCODE, ORGNAME, BATCH_ID, BATCH_NO, BATCH_STATUS,
BATCH_STATUS_DESCRIPTION, BATCH_TYPE,BATCH_TYPE_DESCRIPTION, BATCH_DELAYED, BATCH_DELAY_TIME, BATCH_DELAY_STRING, BATCH_TERMINATED_IND,
RECIPE_VALIDITY_RULE_ID, ROUTINGID, PLANNEDSTARTDATE, PLANNEDCOMPDATE,ACTUALSTARTDATE, ACTUALCOMPDATE, CLOSEDATE, DUEDATE,
FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULADESC, RECIPE_ID, RECIPE_NO, RECIPE_STATUS, RECIPE_STATUS_DESC, RECIPE_VERSION,
RECIPE_DESCRIPTION, ROUTING_NO, ROUTING_VERSION, ROUTING_DESCRIPTION, ROUTING_CLASS, ROUTING_STATUS, ROUTING_STATUS_DESC,
FPO_ID, BATCH_HOLD_IND, HOLD_REASON, HOLD_START_DATE,HOLD_COMMENTS, HOLD_REQUESTOR, BATCH_HOLD_TYPE, MATERIAL_DETAIL_ID,
LINE_TYPE, LINE_DESCRIPTION, INVENTORY_ITEM_ID, ITEM_DESCRIPTION, ITEM, INVENTORY_PLANNING_CODE,PLANNER_CODE, LINE_NO, PLANQTY,
ACTUALQTY, WIPPLANQTY, DTL_UM, PLAN_MATERIAL_YIELD, WIP_PLAN_MATERIAL_YIELD, DTL_UNALLOC, STEPID, BATCHSTEP_NO, STEP_OPRN_NAME,
STEP_OPRN_DESC, STEP_STATUS, STEPPLANNEDSTARTDATE, STEPPLANNEDCOMPDATE, STEPACTUALSTARTDATE, STEPACTUALCOMPDATE,
STEPCLOSEDATE, STEPPLANNEDQTY, STEPACTUALQTY,ROUTINGSTEPID, STEP_DELAYED, STEP_DELAY_TIME, STEP_DELAY_STRING, ACTIVITY,
BATCHSTEP_ACTVITY_ID, ACTPLANSTARTDATE, ACTPLANCOMPDATE, ACTACTUALSTARTDATE,ACTACTUALCOMPDATE, OPRN_LINE_ID, RES,
RESOURCE_DESC, RESPRIM_RSRC_IND, RESCAPACITY_UM, RESPLANLUSG, RESACTUALUSG, RESPLANQTY, RESACTUALQTY, RESPLANSTARTDATE,
RESACTUALSTARTDATE, RESPLANCOMPDATE, RESACTUALCOMPDATE, RESUSAGE_UM, RESRESOURCE_QTY_UM, RESPLAN_RSRC_COUNT,
RESACTUAL_RSRC_COUNT, PP_PROCESS_PARAM_ID, PP_PARAMETER_NAME,PP_PARAMETER_DESCRIPTION, PP_PARAMETER_ID, PP_ACTUAL_VALUE,
PP_TARGET_VALUE, PP_MINIMUM_VALUE, PP_MAXIMUM_VALUE, PP_PARAMETER_UOM, CUSTOMER_NAME, ORDER_NUMBER,
RESERVATION_ID, SALES_ORDER_ID, ORD_LINE_ID, ORDERED_ITEM, REQUEST_DATE, PROMISE_DATE, ORDER_QUANTITY_UOM, ORDERED_QUANTITY, TRANSACTION_TYPE_ID,
TRANSACTION_TYPE_NAME, REAS_TRANSACTION_DATE, REAS_TRANS_ID, LOT_NUMBER, SUBINVENTORY, LOCATOR_ID, TRANSACTION_QUANTITY, PRIMARY_QUANTITY,
SECONDARY_TRANSACTION_QUANTITY, PRIMARY_UOM_CODE, SECONDARY_UOM_CODE, LPN_ID, REASON_ID, REASON_NAME, DESCRIPTION, EXP_LOT_NUMBER,
EXP_PARENT_LOT_NUMBER, EXP_SUPPLIER_LOT_NUMBER, EXP_LOT_GRADE_CODE, EXPIRATION_DATE, EXPIRATION_PERIOD, EXP_SUBINVENTORY, EXP_LOCATOR_ID,
OH_EXP_TRANSACTION_QUANTITY, OH_EXP_PRIMARY_QUANTITY, SAMPLE_ID, EVENT_SPEC_DISP_ID, RESULT_ID, SAMPLE_NUMBER, LAB_NAME, DISPOSITION,
DISPOSITION_CODE, SAMPLESTEP_NO, SEQUENCE, TEST, TEST_EVALUATION, RESULT, TARGET, UNIT, MIN_VALUE, MAX_VALUE, RESULT_DATE, TESTER, NONCONFORM_SEVERITY,
NONCONFORMANCE_TYPE, NONCONFORMANCE_STATUS, NCM_DATE_OPENED, NCM_PLAN_ID, NCM_COLLECTION_ID, NCM_OCCURRENCE, NCM_LAST_UPDATE_DATE, NONCONFORMANCE_NUMBER,
NONCONFORMANCE_OWNER, NONCONFORMANCE_DESC,  LANGUAGE, LAST_UPDATE_DATE from OPM_ECC_NCM_V where batch_status in(2,3) and language in ('US'))PIVOT (max(BATCH_HOLD_IND) AS BATCH_HOLD_IND  ,max(ORGNAME) AS ORGNAME,max(BATCH_STATUS_DESCRIPTION) AS BATCH_STATUS_DESCRIPTION,max(STEP_STATUS) AS STEP_STATUS,
max(LINE_DESCRIPTION) AS LINE_DESCRIPTION,max(DISPOSITION) AS DISPOSITION,
max(RECIPE_STATUS_DESC) as RECIPE_STATUS_DESC ,max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION, max(PLANNER_CODE) as PLANNER_CODE,max(RECIPE_DESCRIPTION) as RECIPE_DESCRIPTION,
max(FORMULADESC) as FORMULADESC,max(ROUTING_DESCRIPTION) as ROUTING_DESCRIPTION,
max(ROUTING_STATUS_DESC) as ROUTING_STATUS_DESC,max(PP_PARAMETER_DESCRIPTION) as PP_PARAMETER_DESCRIPTION,max(RESOURCE_DESC) as RESOURCE_DESC,
max(STEP_OPRN_DESC) as STEP_OPRN_DESC for LANGUAGE in ('US' "US"))
) x
where
2=2

select x.* from ( Select * from(Select ECC_SPEC_ID, RECORD_TYPE, RECORD_IDENTIFIER, ORGANIZATION_ID, ORGCODE, ORGNAME, BATCH_ID, BATCH_NO, BATCH_STATUS, BATCH_STATUS_DESCRIPTION, BATCH_TYPE,BATCH_TYPE_DESCRIPTION, BATCH_DELAYED, BATCH_DELAY_TIME, BATCH_DELAY_STRING, BATCH_TERMINATED_IND, RECIPE_VALIDITY_RULE_ID, ROUTINGID, PLANNEDSTARTDATE, PLANNEDCOMPDATE,ACTUALSTARTDATE, ACTUALCOMPDATE, CLOSEDATE, DUEDATE, FORMULA_ID, FORMULA_NO, FORMULA_VERS, FORMULADESC, RECIPE_ID, RECIPE_NO, RECIPE_STATUS, RECIPE_STATUS_DESC, RECIPE_VERSION, RECIPE_DESCRIPTION, ROUTING_NO, ROUTING_VERSION, ROUTING_DESCRIPTION, ROUTING_CLASS, ROUTING_STATUS, ROUTING_STATUS_DESC, FPO_ID, BATCH_HOLD_IND, HOLD_REASON, HOLD_START_DATE,HOLD_COMMENTS, HOLD_REQUESTOR, BATCH_HOLD_TYPE, MATERIAL_DETAIL_ID, LINE_TYPE, LINE_DESCRIPTION, INVENTORY_ITEM_ID, ITEM_DESCRIPTION, ITEM, INVENTORY_PLANNING_CODE,PLANNER_CODE, LINE_NO, PLANQTY, ACTUALQTY, WIPPLANQTY, DTL_UM, PLAN_MATERIAL_YIELD, WIP_PLAN_MATERIAL_YIELD, DTL_UNALLOC, STEPID, BATCHSTEP_NO, STEP_OPRN_NAME, STEP_OPRN_DESC, STEP_STATUS, STEPPLANNEDSTARTDATE, STEPPLANNEDCOMPDATE, STEPACTUALSTARTDATE, STEPACTUALCOMPDATE, STEPCLOSEDATE, STEPPLANNEDQTY, STEPACTUALQTY,ROUTINGSTEPID, STEP_DELAYED, STEP_DELAY_TIME, STEP_DELAY_STRING, ACTIVITY, BATCHSTEP_ACTVITY_ID, ACTPLANSTARTDATE, ACTPLANCOMPDATE, ACTACTUALSTARTDATE,ACTACTUALCOMPDATE, OPRN_LINE_ID, RES, RESOURCE_DESC, RESPRIM_RSRC_IND, RESCAPACITY_UM, RESPLANLUSG, RESACTUALUSG, RESPLANQTY, RESACTUALQTY, RESPLANSTARTDATE, RESACTUALSTARTDATE, RESPLANCOMPDATE, RESACTUALCOMPDATE, RESUSAGE_UM, RESRESOURCE_QTY_UM, RESPLAN_RSRC_COUNT, RESACTUAL_RSRC_COUNT, PP_PROCESS_PARAM_ID, PP_PARAMETER_NAME,PP_PARAMETER_DESCRIPTION, PP_PARAMETER_ID, PP_ACTUAL_VALUE, PP_TARGET_VALUE, PP_MINIMUM_VALUE, PP_MAXIMUM_VALUE, PP_PARAMETER_UOM, CUSTOMER_NAME, ORDER_NUMBER, RESERVATION_ID, SALES_ORDER_ID, ORD_LINE_ID, ORDERED_ITEM, REQUEST_DATE, PROMISE_DATE, ORDER_QUANTITY_UOM, ORDERED_QUANTITY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, REAS_TRANSACTION_DATE, REAS_TRANS_ID, LOT_NUMBER, SUBINVENTORY, LOCATOR_ID, TRANSACTION_QUANTITY, PRIMARY_QUANTITY, SECONDARY_TRANSACTION_QUANTITY, PRIMARY_UOM_CODE, SECONDARY_UOM_CODE, LPN_ID, REASON_ID, REASON_NAME, DESCRIPTION, EXP_LOT_NUMBER, EXP_PARENT_LOT_NUMBER, EXP_SUPPLIER_LOT_NUMBER, EXP_LOT_GRADE_CODE, EXPIRATION_DATE, EXPIRATION_PERIOD, EXP_SUBINVENTORY, EXP_LOCATOR_ID, OH_EXP_TRANSACTION_QUANTITY, OH_EXP_PRIMARY_QUANTITY, SAMPLE_ID, EVENT_SPEC_DISP_ID, RESULT_ID, SAMPLE_NUMBER, LAB_NAME, DISPOSITION, DISPOSITION_CODE, SAMPLESTEP_NO, SEQUENCE, TEST, TEST_EVALUATION, RESULT, TARGET, UNIT, MIN_VALUE, MAX_VALUE, RESULT_DATE, TESTER, NONCONFORM_SEVERITY, NONCONFORMANCE_TYPE, NONCONFORMANCE_STATUS, NCM_DATE_OPENED, NCM_PLAN_ID, NCM_COLLECTION_ID, NCM_OCCURRENCE, NCM_LAST_UPDATE_DATE, NONCONFORMANCE_NUMBER, NONCONFORMANCE_OWNER, NONCONFORMANCE_DESC, LANGUAGE, LAST_UPDATE_DATE from OPM_ECC_NCM_V where batch_status in(2,3) and language in ('US'))PIVOT (max(BATCH_HOLD_IND) AS BATCH_HOLD_IND ,max(ORGNAME) AS ORGNAME,max(BATCH_STATUS_DESCRIPTION) AS BATCH_STATUS_DESCRIPTION,max(STEP_STATUS) AS STEP_STATUS, max(LINE_DESCRIPTION) AS LINE_DESCRIPTION,max(DISPOSITION) AS DISPOSITION, max(RECIPE_STATUS_DESC) as RECIPE_STATUS_DESC ,max(ITEM_DESCRIPTION) as ITEM_DESCRIPTION, max(PLANNER_CODE) as PLANNER_CODE,max(RECIPE_DESCRIPTION) as RECIPE_DESCRIPTION, max(FORMULADESC) as FORMULADESC,max(ROUTING_DESCRIPTION) as ROUTING_DESCRIPTION, max(ROUTING_STATUS_DESC) as ROUTING_STATUS_DESC,max(PP_PARAMETER_DESCRIPTION) as PP_PARAMETER_DESCRIPTION,max(RESOURCE_DESC) as RESOURCE_DESC, max(STEP_OPRN_DESC) as STEP_OPRN_DESC for LANGUAGE in ('US' "US")) ) x where 2=2