VIEW: APPS.GMD_ALL_SPEC_VRS_VL

Source

View Text - Preformatted

SELECT v.spec_vr_id, v.spec_id, v.organization_id, pv.organization_code, 'I' spec_type, s.spec_name, s.spec_vers, s.owner_organization_id, s.owner_id, s.inventory_item_id, i.concatenated_segments item_number, i.description, s.revision, s.grade_code, s.spec_status, p.description spec_status_desc, v.parent_lot_number, v.lot_number, v.subinventory, v.locator_id, l.concatenated_segments LOCATOR, v.spec_vr_status, t.description spec_vr_status_desc, v.start_date, v.end_date, v.sampling_plan_id, v.sample_inv_trans_ind, v.control_lot_attrib_ind, v.out_of_spec_lot_status_id, ms1.status_code out_of_spec_lot_status_code, v.in_spec_lot_status_id, ms2.status_code in_spec_lot_status_code, NULL control_batch_step_ind, v.lot_optional_on_sample, v.delayed_lot_entry, v.coa_type, v.coa_at_ship_ind, v.coa_at_invoice_ind, v.coa_req_from_supl_ind, v.auto_sample_ind, TO_NUMBER (NULL) batch_id, TO_NUMBER (NULL) recipe_id, NULL recipe_no, TO_NUMBER (NULL) recipe_version, TO_NUMBER (NULL) formula_id, TO_NUMBER (NULL) formulaline_id, NULL formula_no, TO_NUMBER (NULL) formula_vers, TO_NUMBER (NULL) routing_id, NULL routing_no, TO_NUMBER (NULL) routing_vers, TO_NUMBER (NULL) step_id, TO_NUMBER (NULL) step_no, TO_NUMBER (NULL) oprn_id, NULL oprn_no, TO_NUMBER (NULL) oprn_vers, TO_NUMBER (NULL) charge, TO_NUMBER (NULL) cust_id, TO_NUMBER (NULL) order_id, TO_NUMBER (NULL) order_line, TO_NUMBER (NULL) order_line_id, TO_NUMBER (NULL) ship_to_site_id, TO_NUMBER (NULL) org_id, TO_NUMBER (NULL) supplier_id, TO_NUMBER (NULL) supplier_site_id, TO_NUMBER (NULL) po_header_id, TO_NUMBER (NULL) po_line_id, NULL resources, TO_NUMBER (NULL) resource_instance_id, v.delete_mark, v.text_code, v.creation_date, v.created_by, v.last_updated_by, v.last_update_date, v.last_update_login, TO_NUMBER (NULL) material_detail_id FROM gmd_inventory_spec_vrs v, gmd_specifications_b s, mtl_system_items_kfv i, mtl_item_locations_kfv l, gmd_qc_status_tl t, gmd_qc_status_tl p, mtl_parameters pv, mtl_material_statuses ms1, mtl_material_statuses ms2 WHERE v.spec_id = s.spec_id AND s.owner_organization_id = i.organization_id AND s.inventory_item_id = i.inventory_item_id AND l.organization_id(+) = v.organization_id AND l.inventory_location_id(+) = v.locator_id AND ms1.status_id(+) = v.out_of_spec_lot_status_id AND ms2.status_id(+) = v.in_spec_lot_status_id AND v.organization_id = pv.organization_id(+) AND v.spec_vr_status = t.status_code AND t.entity_type = 'S' AND t.LANGUAGE = USERENV ('LANG') AND s.spec_status = p.status_code AND p.entity_type = 'S' AND p.LANGUAGE = USERENV ('LANG') UNION SELECT v.spec_vr_id, v.spec_id, v.organization_id, pv.organization_code, 'W' spec_type, s.spec_name, s.spec_vers, s.owner_organization_id, s.owner_id, s.inventory_item_id, i.concatenated_segments item_number, i.description, s.revision, s.grade_code, s.spec_status, p.description spec_status_desc, NULL parent_lot_number, NULL lot_number, NULL subinventory, TO_NUMBER (NULL) locator_id, NULL LOCATOR, v.spec_vr_status, t.description spec_vr_status_desc, v.start_date, v.end_date, v.sampling_plan_id, v.sample_inv_trans_ind, v.control_lot_attrib_ind, v.out_of_spec_lot_status_id, st2.status_code out_of_spec_lot_status_code, v.in_spec_lot_status_id, st1.status_code in_spec_lot_status_code, v.control_batch_step_ind, v.lot_optional_on_sample, v.delayed_lot_entry, v.coa_type, v.coa_at_ship_ind, NULL coa_at_invoice_ind, v.coa_req_from_supl_ind, v.auto_sample_ind, v.batch_id, v.recipe_id, v.recipe_no, v.recipe_version, v.formula_id, v.formulaline_id, v.formula_no, v.formula_vers, v.routing_id, v.routing_no, v.routing_vers, v.step_id, v.step_no, v.oprn_id, v.oprn_no, v.oprn_vers, v.charge, TO_NUMBER (NULL) cust_id, TO_NUMBER (NULL) order_id, TO_NUMBER (NULL) order_line, TO_NUMBER (NULL) order_line_id, TO_NUMBER (NULL) ship_to_site_id, TO_NUMBER (NULL) org_id, TO_NUMBER (NULL) supplier_id, TO_NUMBER (NULL) supplier_site_id, TO_NUMBER (NULL) po_header_id, TO_NUMBER (NULL) po_line_id, NULL resources, TO_NUMBER (NULL) resource_instance_id, v.delete_mark, v.text_code, v.creation_date, v.created_by, v.last_updated_by, v.last_update_date, v.last_update_login, v.material_detail_id FROM gmd_wip_spec_vrs v, gmd_specifications_b s, mtl_system_items_kfv i, mtl_parameters pv, mtl_material_statuses st1, mtl_material_statuses st2, gmd_qc_status_tl t, gmd_qc_status_tl p WHERE v.spec_id = s.spec_id AND i.organization_id = s.owner_organization_id AND i.inventory_item_id = s.inventory_item_id AND pv.organization_id(+) = v.organization_id AND v.spec_vr_status = t.status_code AND st1.status_id(+) = v.in_spec_lot_status_id AND st2.status_id(+) = v.out_of_spec_lot_status_id AND t.entity_type = 'S' AND t.LANGUAGE = USERENV ('LANG') AND s.spec_status = p.status_code AND p.entity_type = 'S' AND p.LANGUAGE = USERENV ('LANG') UNION SELECT v.spec_vr_id, v.spec_id, v.organization_id, pv.organization_code, 'C' spec_type, s.spec_name, s.spec_vers, s.owner_organization_id, s.owner_id, s.inventory_item_id, i.concatenated_segments item_number, i.description, s.revision, s.grade_code, s.spec_status, p.description spec_status_desc, NULL parent_lot_number, NULL lot_number, NULL subinventory, TO_NUMBER (NULL) locator_id, NULL LOCATOR, v.spec_vr_status, t.description spec_vr_status_desc, v.start_date, v.end_date, v.sampling_plan_id, v.sample_inv_trans_ind, NULL control_lot_attrib_ind, TO_NUMBER (NULL) out_of_spec_lot_status_id, NULL out_of_spec_lot_status_code, TO_NUMBER (NULL) in_spec_lot_status_id, NULL in_spec_lot_status_code, NULL control_batch_step_ind, v.lot_optional_on_sample, NULL delayed_lot_entry, v.coa_type, v.coa_at_ship_ind, v.coa_at_invoice_ind, v.coa_req_from_supl_ind, NULL auto_sample_ind, TO_NUMBER (NULL) batch_id, TO_NUMBER (NULL) recipe_id, NULL recipe_no, TO_NUMBER (NULL) recipe_version, TO_NUMBER (NULL) formula_id, TO_NUMBER (NULL) formulaline_id, NULL formula_no, TO_NUMBER (NULL) formula_vers, TO_NUMBER (NULL) routing_id, NULL routing_no, TO_NUMBER (NULL) routing_vers, TO_NUMBER (NULL) step_id, TO_NUMBER (NULL) step_no, TO_NUMBER (NULL) oprn_id, NULL oprn_no, TO_NUMBER (NULL) oprn_vers, TO_NUMBER (NULL) charge, cust_id, order_id, order_line, order_line_id, ship_to_site_id, org_id, TO_NUMBER (NULL) supplier_id, TO_NUMBER (NULL) supplier_site_id, TO_NUMBER (NULL) po_header_id, TO_NUMBER (NULL) po_line_id, NULL resources, TO_NUMBER (NULL) resource_instance_id, v.delete_mark, v.text_code, v.creation_date, v.created_by, v.last_updated_by, v.last_update_date, v.last_update_login, TO_NUMBER (NULL) material_detail_id FROM gmd_customer_spec_vrs v, gmd_specifications_b s, mtl_system_items_kfv i, mtl_parameters pv, gmd_qc_status_tl t, gmd_qc_status_tl p WHERE v.spec_id = s.spec_id AND s.owner_organization_id = i.organization_id AND s.inventory_item_id = i.inventory_item_id AND pv.organization_id(+) = v.organization_id AND v.spec_vr_status = t.status_code AND t.entity_type = 'S' AND t.LANGUAGE = USERENV ('LANG') AND s.spec_status = p.status_code AND p.entity_type = 'S' AND p.LANGUAGE = USERENV ('LANG') UNION SELECT v.spec_vr_id, v.spec_id, v.organization_id, pv.organization_code, 'S' spec_type, s.spec_name, s.spec_vers, s.owner_organization_id, s.owner_id, s.inventory_item_id, i.concatenated_segments item_number, i.description, s.revision, s.grade_code, s.spec_status, p.description spec_status_desc, NULL parent_lot_number, NULL lot_number, NULL subinventory, TO_NUMBER (NULL) locator_id, NULL LOCATOR, v.spec_vr_status, t.description spec_vr_status_desc, v.start_date, v.end_date, v.sampling_plan_id, v.sample_inv_trans_ind, v.control_lot_attrib_ind, v.out_of_spec_lot_status_id, st2.status_code out_of_spec_lot_status_code, v.in_spec_lot_status_id, st1.status_code in_spec_lot_status_code, NULL control_batch_step_ind, v.lot_optional_on_sample, v.delayed_lot_entry, v.coa_type, NULL coa_at_ship_ind, NULL coa_at_invoice_ind, coa_req_from_supl_ind, v.auto_sample_ind, TO_NUMBER (NULL) batch_id, TO_NUMBER (NULL) recipe_id, NULL recipe_no, TO_NUMBER (NULL) recipe_version, TO_NUMBER (NULL) formula_id, TO_NUMBER (NULL) formulaline_id, NULL formula_no, TO_NUMBER (NULL) formula_vers, TO_NUMBER (NULL) routing_id, NULL routing_no, TO_NUMBER (NULL) routing_vers, TO_NUMBER (NULL) step_id, TO_NUMBER (NULL) step_no, TO_NUMBER (NULL) oprn_id, NULL oprn_no, TO_NUMBER (NULL) oprn_vers, TO_NUMBER (NULL) charge, TO_NUMBER (NULL) cust_id, TO_NUMBER (NULL) order_id, TO_NUMBER (NULL) order_line, TO_NUMBER (NULL) order_line_id, TO_NUMBER (NULL) ship_to_site_id, org_id, v.supplier_id, v.supplier_site_id, v.po_header_id, v.po_line_id, NULL resources, TO_NUMBER (NULL) resource_instance_id, v.delete_mark, v.text_code, v.creation_date, v.created_by, v.last_updated_by, v.last_update_date, v.last_update_login, TO_NUMBER (NULL) material_detail_id FROM gmd_supplier_spec_vrs v, gmd_specifications_b s, mtl_system_items_kfv i, mtl_parameters pv, gmd_qc_status_tl t, gmd_qc_status_tl p, mtl_material_statuses st1, mtl_material_statuses st2 WHERE v.spec_id = s.spec_id AND s.owner_organization_id = i.organization_id AND s.inventory_item_id = i.inventory_item_id AND pv.organization_id(+) = v.organization_id AND st1.status_id(+) = v.in_spec_lot_status_id AND st2.status_id(+) = v.out_of_spec_lot_status_id AND v.spec_vr_status = t.status_code AND t.entity_type = 'S' AND t.LANGUAGE = USERENV ('LANG') AND s.spec_status = p.status_code AND p.entity_type = 'S' AND p.LANGUAGE = USERENV ('LANG') UNION SELECT v.spec_vr_id, v.spec_id, DECODE (rule_type, 'R', v.resource_organization_id, 'L', v.locator_organization_id, TO_NUMBER (NULL) ), pv.organization_code, v.rule_type, s.spec_name, s.spec_vers, s.owner_organization_id, s.owner_id, TO_NUMBER (NULL) inventory_item_id, NULL item_number, NULL description, NULL revision, NULL grade_code, s.spec_status, p.description spec_status_desc, NULL parent_lot_number, NULL lot_number, v.subinventory, v.locator_id, l.concatenated_segments LOCATOR, v.spec_vr_status, t.description spec_vr_status_desc, v.start_date, v.end_date, v.sampling_plan_id, NULL sample_inv_trans_ind, NULL control_lot_attrib_ind, NULL out_of_spec_lot_status_id, NULL out_of_spec_lot_status_code, NULL in_spec_lot_status_id, NULL in_spec_lot_status_code, NULL control_batch_step_ind, NULL lot_optional_on_sample, NULL delayed_lot_entry, NULL coa_type, NULL coa_at_ship_ind, NULL coa_at_invoice_ind, NULL coa_req_from_supl_ind, NULL auto_sample_ind, TO_NUMBER (NULL) batch_id, TO_NUMBER (NULL) recipe_id, NULL recipe_no, TO_NUMBER (NULL) recipe_version, TO_NUMBER (NULL) formula_id, TO_NUMBER (NULL) formulaline_id, NULL formula_no, TO_NUMBER (NULL) formula_vers, TO_NUMBER (NULL) routing_id, NULL routing_no, TO_NUMBER (NULL) routing_vers, TO_NUMBER (NULL) step_id, TO_NUMBER (NULL) step_no, TO_NUMBER (NULL) oprn_id, NULL oprn_no, TO_NUMBER (NULL) oprn_vers, TO_NUMBER (NULL) charge, TO_NUMBER (NULL) cust_id, TO_NUMBER (NULL) order_id, TO_NUMBER (NULL) order_line, TO_NUMBER (NULL) order_line_id, TO_NUMBER (NULL) ship_to_site_id, TO_NUMBER (NULL) org_id, TO_NUMBER (NULL) supplier_id, TO_NUMBER (NULL) supplier_site_id, TO_NUMBER (NULL) po_header_id, TO_NUMBER (NULL) po_line_id, v.resources, v.resource_instance_id, v.delete_mark, v.text_code, v.creation_date, v.created_by, v.last_updated_by, v.last_update_date, v.last_update_login, TO_NUMBER (NULL) material_detail_id FROM gmd_monitoring_spec_vrs v, gmd_specifications_b s, mtl_parameters pv, mtl_item_locations_kfv l, gmd_qc_status_tl p, gmd_qc_status_tl t WHERE v.spec_id = s.spec_id AND pv.organization_id(+) = DECODE (rule_type, 'R', v.resource_organization_id, 'L', v.locator_organization_id, NULL ) AND l.organization_id(+) = DECODE (rule_type, 'R', v.resource_organization_id, 'L', v.locator_organization_id, NULL ) AND l.inventory_location_id(+) = v.locator_id AND v.spec_vr_status = t.status_code AND t.entity_type = 'S' AND t.LANGUAGE = USERENV ('LANG') AND s.spec_status = p.status_code AND p.entity_type = 'S' AND p.LANGUAGE = USERENV ('LANG') UNION SELECT v.spec_vr_id, v.spec_id, TO_NUMBER (NULL), NULL, 'T' spec_type, s.spec_name, s.spec_vers, s.owner_organization_id, s.owner_id, TO_NUMBER (NULL) inventory_item_id, NULL item_number, NULL description, NULL revision, NULL grade_code, s.spec_status, l.description spec_status_desc, NULL parent_lot_number, NULL lot_number, NULL subinventory, TO_NUMBER (NULL) locator_id, NULL LOCATOR, v.spec_vr_status, t.description spec_vr_status_desc, v.start_date, v.end_date, v.sampling_plan_id, NULL sample_inv_trans_ind, NULL control_lot_attrib_ind, TO_NUMBER (NULL) out_of_spec_lot_status_id, NULL out_of_spec_lot_status_code, TO_NUMBER (NULL) in_spec_lot_status_id, NULL in_spec_lot_status_code, NULL control_batch_step_ind, NULL lot_optional_on_sample, NULL delayed_lot_entry, NULL coa_type, NULL coa_at_ship_ind, NULL coa_at_invoice_ind, NULL coa_req_from_supl_ind, NULL auto_sample_ind, TO_NUMBER (NULL) batch_id, TO_NUMBER (NULL) recipe_id, NULL recipe_no, TO_NUMBER (NULL) recipe_version, TO_NUMBER (NULL) formula_id, TO_NUMBER (NULL) formulaline_id, NULL formula_no, TO_NUMBER (NULL) formula_vers, TO_NUMBER (NULL) routing_id, NULL routing_no, TO_NUMBER (NULL) routing_vers, TO_NUMBER (NULL) step_id, TO_NUMBER (NULL) step_no, TO_NUMBER (NULL) oprn_id, NULL oprn_no, TO_NUMBER (NULL) oprn_vers, TO_NUMBER (NULL) charge, TO_NUMBER (NULL) cust_id, TO_NUMBER (NULL) order_id, TO_NUMBER (NULL) order_line, TO_NUMBER (NULL) order_line_id, TO_NUMBER (NULL) ship_to_site_id, TO_NUMBER (NULL) org_id, TO_NUMBER (NULL) supplier_id, TO_NUMBER (NULL) supplier_site_id, TO_NUMBER (NULL) po_header_id, TO_NUMBER (NULL) po_line_id, NULL resources, TO_NUMBER (NULL) resource_instance_id, v.delete_mark, TO_NUMBER (NULL) text_code, v.creation_date, v.created_by, v.last_updated_by, v.last_update_date, v.last_update_login, TO_NUMBER (NULL) material_detail_id FROM gmd_stability_spec_vrs v, gmd_specifications_b s, gmd_qc_status_tl t, gmd_qc_status_tl l WHERE v.spec_id = s.spec_id AND v.spec_vr_status = t.status_code AND s.spec_status = l.status_code AND t.entity_type = 'S' AND l.entity_type = 'S' AND t.LANGUAGE = USERENV ('LANG') AND l.LANGUAGE = USERENV ('LANG')

View Text - HTML Formatted

SELECT V.SPEC_VR_ID
, V.SPEC_ID
, V.ORGANIZATION_ID
, PV.ORGANIZATION_CODE
, 'I' SPEC_TYPE
, S.SPEC_NAME
, S.SPEC_VERS
, S.OWNER_ORGANIZATION_ID
, S.OWNER_ID
, S.INVENTORY_ITEM_ID
, I.CONCATENATED_SEGMENTS ITEM_NUMBER
, I.DESCRIPTION
, S.REVISION
, S.GRADE_CODE
, S.SPEC_STATUS
, P.DESCRIPTION SPEC_STATUS_DESC
, V.PARENT_LOT_NUMBER
, V.LOT_NUMBER
, V.SUBINVENTORY
, V.LOCATOR_ID
, L.CONCATENATED_SEGMENTS LOCATOR
, V.SPEC_VR_STATUS
, T.DESCRIPTION SPEC_VR_STATUS_DESC
, V.START_DATE
, V.END_DATE
, V.SAMPLING_PLAN_ID
, V.SAMPLE_INV_TRANS_IND
, V.CONTROL_LOT_ATTRIB_IND
, V.OUT_OF_SPEC_LOT_STATUS_ID
, MS1.STATUS_CODE OUT_OF_SPEC_LOT_STATUS_CODE
, V.IN_SPEC_LOT_STATUS_ID
, MS2.STATUS_CODE IN_SPEC_LOT_STATUS_CODE
, NULL CONTROL_BATCH_STEP_IND
, V.LOT_OPTIONAL_ON_SAMPLE
, V.DELAYED_LOT_ENTRY
, V.COA_TYPE
, V.COA_AT_SHIP_IND
, V.COA_AT_INVOICE_IND
, V.COA_REQ_FROM_SUPL_IND
, V.AUTO_SAMPLE_IND
, TO_NUMBER (NULL) BATCH_ID
, TO_NUMBER (NULL) RECIPE_ID
, NULL RECIPE_NO
, TO_NUMBER (NULL) RECIPE_VERSION
, TO_NUMBER (NULL) FORMULA_ID
, TO_NUMBER (NULL) FORMULALINE_ID
, NULL FORMULA_NO
, TO_NUMBER (NULL) FORMULA_VERS
, TO_NUMBER (NULL) ROUTING_ID
, NULL ROUTING_NO
, TO_NUMBER (NULL) ROUTING_VERS
, TO_NUMBER (NULL) STEP_ID
, TO_NUMBER (NULL) STEP_NO
, TO_NUMBER (NULL) OPRN_ID
, NULL OPRN_NO
, TO_NUMBER (NULL) OPRN_VERS
, TO_NUMBER (NULL) CHARGE
, TO_NUMBER (NULL) CUST_ID
, TO_NUMBER (NULL) ORDER_ID
, TO_NUMBER (NULL) ORDER_LINE
, TO_NUMBER (NULL) ORDER_LINE_ID
, TO_NUMBER (NULL) SHIP_TO_SITE_ID
, TO_NUMBER (NULL) ORG_ID
, TO_NUMBER (NULL) SUPPLIER_ID
, TO_NUMBER (NULL) SUPPLIER_SITE_ID
, TO_NUMBER (NULL) PO_HEADER_ID
, TO_NUMBER (NULL) PO_LINE_ID
, NULL RESOURCES
, TO_NUMBER (NULL) RESOURCE_INSTANCE_ID
, V.DELETE_MARK
, V.TEXT_CODE
, V.CREATION_DATE
, V.CREATED_BY
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_DATE
, V.LAST_UPDATE_LOGIN
, TO_NUMBER (NULL) MATERIAL_DETAIL_ID
FROM GMD_INVENTORY_SPEC_VRS V
, GMD_SPECIFICATIONS_B S
, MTL_SYSTEM_ITEMS_KFV I
, MTL_ITEM_LOCATIONS_KFV L
, GMD_QC_STATUS_TL T
, GMD_QC_STATUS_TL P
, MTL_PARAMETERS PV
, MTL_MATERIAL_STATUSES MS1
, MTL_MATERIAL_STATUSES MS2
WHERE V.SPEC_ID = S.SPEC_ID
AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND L.ORGANIZATION_ID(+) = V.ORGANIZATION_ID
AND L.INVENTORY_LOCATION_ID(+) = V.LOCATOR_ID
AND MS1.STATUS_ID(+) = V.OUT_OF_SPEC_LOT_STATUS_ID
AND MS2.STATUS_ID(+) = V.IN_SPEC_LOT_STATUS_ID
AND V.ORGANIZATION_ID = PV.ORGANIZATION_ID(+)
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ('LANG')
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ('LANG') UNION SELECT V.SPEC_VR_ID
, V.SPEC_ID
, V.ORGANIZATION_ID
, PV.ORGANIZATION_CODE
, 'W' SPEC_TYPE
, S.SPEC_NAME
, S.SPEC_VERS
, S.OWNER_ORGANIZATION_ID
, S.OWNER_ID
, S.INVENTORY_ITEM_ID
, I.CONCATENATED_SEGMENTS ITEM_NUMBER
, I.DESCRIPTION
, S.REVISION
, S.GRADE_CODE
, S.SPEC_STATUS
, P.DESCRIPTION SPEC_STATUS_DESC
, NULL PARENT_LOT_NUMBER
, NULL LOT_NUMBER
, NULL SUBINVENTORY
, TO_NUMBER (NULL) LOCATOR_ID
, NULL LOCATOR
, V.SPEC_VR_STATUS
, T.DESCRIPTION SPEC_VR_STATUS_DESC
, V.START_DATE
, V.END_DATE
, V.SAMPLING_PLAN_ID
, V.SAMPLE_INV_TRANS_IND
, V.CONTROL_LOT_ATTRIB_IND
, V.OUT_OF_SPEC_LOT_STATUS_ID
, ST2.STATUS_CODE OUT_OF_SPEC_LOT_STATUS_CODE
, V.IN_SPEC_LOT_STATUS_ID
, ST1.STATUS_CODE IN_SPEC_LOT_STATUS_CODE
, V.CONTROL_BATCH_STEP_IND
, V.LOT_OPTIONAL_ON_SAMPLE
, V.DELAYED_LOT_ENTRY
, V.COA_TYPE
, V.COA_AT_SHIP_IND
, NULL COA_AT_INVOICE_IND
, V.COA_REQ_FROM_SUPL_IND
, V.AUTO_SAMPLE_IND
, V.BATCH_ID
, V.RECIPE_ID
, V.RECIPE_NO
, V.RECIPE_VERSION
, V.FORMULA_ID
, V.FORMULALINE_ID
, V.FORMULA_NO
, V.FORMULA_VERS
, V.ROUTING_ID
, V.ROUTING_NO
, V.ROUTING_VERS
, V.STEP_ID
, V.STEP_NO
, V.OPRN_ID
, V.OPRN_NO
, V.OPRN_VERS
, V.CHARGE
, TO_NUMBER (NULL) CUST_ID
, TO_NUMBER (NULL) ORDER_ID
, TO_NUMBER (NULL) ORDER_LINE
, TO_NUMBER (NULL) ORDER_LINE_ID
, TO_NUMBER (NULL) SHIP_TO_SITE_ID
, TO_NUMBER (NULL) ORG_ID
, TO_NUMBER (NULL) SUPPLIER_ID
, TO_NUMBER (NULL) SUPPLIER_SITE_ID
, TO_NUMBER (NULL) PO_HEADER_ID
, TO_NUMBER (NULL) PO_LINE_ID
, NULL RESOURCES
, TO_NUMBER (NULL) RESOURCE_INSTANCE_ID
, V.DELETE_MARK
, V.TEXT_CODE
, V.CREATION_DATE
, V.CREATED_BY
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_DATE
, V.LAST_UPDATE_LOGIN
, V.MATERIAL_DETAIL_ID
FROM GMD_WIP_SPEC_VRS V
, GMD_SPECIFICATIONS_B S
, MTL_SYSTEM_ITEMS_KFV I
, MTL_PARAMETERS PV
, MTL_MATERIAL_STATUSES ST1
, MTL_MATERIAL_STATUSES ST2
, GMD_QC_STATUS_TL T
, GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
AND I.ORGANIZATION_ID = S.OWNER_ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND PV.ORGANIZATION_ID(+) = V.ORGANIZATION_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND ST1.STATUS_ID(+) = V.IN_SPEC_LOT_STATUS_ID
AND ST2.STATUS_ID(+) = V.OUT_OF_SPEC_LOT_STATUS_ID
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ('LANG')
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ('LANG') UNION SELECT V.SPEC_VR_ID
, V.SPEC_ID
, V.ORGANIZATION_ID
, PV.ORGANIZATION_CODE
, 'C' SPEC_TYPE
, S.SPEC_NAME
, S.SPEC_VERS
, S.OWNER_ORGANIZATION_ID
, S.OWNER_ID
, S.INVENTORY_ITEM_ID
, I.CONCATENATED_SEGMENTS ITEM_NUMBER
, I.DESCRIPTION
, S.REVISION
, S.GRADE_CODE
, S.SPEC_STATUS
, P.DESCRIPTION SPEC_STATUS_DESC
, NULL PARENT_LOT_NUMBER
, NULL LOT_NUMBER
, NULL SUBINVENTORY
, TO_NUMBER (NULL) LOCATOR_ID
, NULL LOCATOR
, V.SPEC_VR_STATUS
, T.DESCRIPTION SPEC_VR_STATUS_DESC
, V.START_DATE
, V.END_DATE
, V.SAMPLING_PLAN_ID
, V.SAMPLE_INV_TRANS_IND
, NULL CONTROL_LOT_ATTRIB_IND
, TO_NUMBER (NULL) OUT_OF_SPEC_LOT_STATUS_ID
, NULL OUT_OF_SPEC_LOT_STATUS_CODE
, TO_NUMBER (NULL) IN_SPEC_LOT_STATUS_ID
, NULL IN_SPEC_LOT_STATUS_CODE
, NULL CONTROL_BATCH_STEP_IND
, V.LOT_OPTIONAL_ON_SAMPLE
, NULL DELAYED_LOT_ENTRY
, V.COA_TYPE
, V.COA_AT_SHIP_IND
, V.COA_AT_INVOICE_IND
, V.COA_REQ_FROM_SUPL_IND
, NULL AUTO_SAMPLE_IND
, TO_NUMBER (NULL) BATCH_ID
, TO_NUMBER (NULL) RECIPE_ID
, NULL RECIPE_NO
, TO_NUMBER (NULL) RECIPE_VERSION
, TO_NUMBER (NULL) FORMULA_ID
, TO_NUMBER (NULL) FORMULALINE_ID
, NULL FORMULA_NO
, TO_NUMBER (NULL) FORMULA_VERS
, TO_NUMBER (NULL) ROUTING_ID
, NULL ROUTING_NO
, TO_NUMBER (NULL) ROUTING_VERS
, TO_NUMBER (NULL) STEP_ID
, TO_NUMBER (NULL) STEP_NO
, TO_NUMBER (NULL) OPRN_ID
, NULL OPRN_NO
, TO_NUMBER (NULL) OPRN_VERS
, TO_NUMBER (NULL) CHARGE
, CUST_ID
, ORDER_ID
, ORDER_LINE
, ORDER_LINE_ID
, SHIP_TO_SITE_ID
, ORG_ID
, TO_NUMBER (NULL) SUPPLIER_ID
, TO_NUMBER (NULL) SUPPLIER_SITE_ID
, TO_NUMBER (NULL) PO_HEADER_ID
, TO_NUMBER (NULL) PO_LINE_ID
, NULL RESOURCES
, TO_NUMBER (NULL) RESOURCE_INSTANCE_ID
, V.DELETE_MARK
, V.TEXT_CODE
, V.CREATION_DATE
, V.CREATED_BY
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_DATE
, V.LAST_UPDATE_LOGIN
, TO_NUMBER (NULL) MATERIAL_DETAIL_ID
FROM GMD_CUSTOMER_SPEC_VRS V
, GMD_SPECIFICATIONS_B S
, MTL_SYSTEM_ITEMS_KFV I
, MTL_PARAMETERS PV
, GMD_QC_STATUS_TL T
, GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND PV.ORGANIZATION_ID(+) = V.ORGANIZATION_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ('LANG')
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ('LANG') UNION SELECT V.SPEC_VR_ID
, V.SPEC_ID
, V.ORGANIZATION_ID
, PV.ORGANIZATION_CODE
, 'S' SPEC_TYPE
, S.SPEC_NAME
, S.SPEC_VERS
, S.OWNER_ORGANIZATION_ID
, S.OWNER_ID
, S.INVENTORY_ITEM_ID
, I.CONCATENATED_SEGMENTS ITEM_NUMBER
, I.DESCRIPTION
, S.REVISION
, S.GRADE_CODE
, S.SPEC_STATUS
, P.DESCRIPTION SPEC_STATUS_DESC
, NULL PARENT_LOT_NUMBER
, NULL LOT_NUMBER
, NULL SUBINVENTORY
, TO_NUMBER (NULL) LOCATOR_ID
, NULL LOCATOR
, V.SPEC_VR_STATUS
, T.DESCRIPTION SPEC_VR_STATUS_DESC
, V.START_DATE
, V.END_DATE
, V.SAMPLING_PLAN_ID
, V.SAMPLE_INV_TRANS_IND
, V.CONTROL_LOT_ATTRIB_IND
, V.OUT_OF_SPEC_LOT_STATUS_ID
, ST2.STATUS_CODE OUT_OF_SPEC_LOT_STATUS_CODE
, V.IN_SPEC_LOT_STATUS_ID
, ST1.STATUS_CODE IN_SPEC_LOT_STATUS_CODE
, NULL CONTROL_BATCH_STEP_IND
, V.LOT_OPTIONAL_ON_SAMPLE
, V.DELAYED_LOT_ENTRY
, V.COA_TYPE
, NULL COA_AT_SHIP_IND
, NULL COA_AT_INVOICE_IND
, COA_REQ_FROM_SUPL_IND
, V.AUTO_SAMPLE_IND
, TO_NUMBER (NULL) BATCH_ID
, TO_NUMBER (NULL) RECIPE_ID
, NULL RECIPE_NO
, TO_NUMBER (NULL) RECIPE_VERSION
, TO_NUMBER (NULL) FORMULA_ID
, TO_NUMBER (NULL) FORMULALINE_ID
, NULL FORMULA_NO
, TO_NUMBER (NULL) FORMULA_VERS
, TO_NUMBER (NULL) ROUTING_ID
, NULL ROUTING_NO
, TO_NUMBER (NULL) ROUTING_VERS
, TO_NUMBER (NULL) STEP_ID
, TO_NUMBER (NULL) STEP_NO
, TO_NUMBER (NULL) OPRN_ID
, NULL OPRN_NO
, TO_NUMBER (NULL) OPRN_VERS
, TO_NUMBER (NULL) CHARGE
, TO_NUMBER (NULL) CUST_ID
, TO_NUMBER (NULL) ORDER_ID
, TO_NUMBER (NULL) ORDER_LINE
, TO_NUMBER (NULL) ORDER_LINE_ID
, TO_NUMBER (NULL) SHIP_TO_SITE_ID
, ORG_ID
, V.SUPPLIER_ID
, V.SUPPLIER_SITE_ID
, V.PO_HEADER_ID
, V.PO_LINE_ID
, NULL RESOURCES
, TO_NUMBER (NULL) RESOURCE_INSTANCE_ID
, V.DELETE_MARK
, V.TEXT_CODE
, V.CREATION_DATE
, V.CREATED_BY
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_DATE
, V.LAST_UPDATE_LOGIN
, TO_NUMBER (NULL) MATERIAL_DETAIL_ID
FROM GMD_SUPPLIER_SPEC_VRS V
, GMD_SPECIFICATIONS_B S
, MTL_SYSTEM_ITEMS_KFV I
, MTL_PARAMETERS PV
, GMD_QC_STATUS_TL T
, GMD_QC_STATUS_TL P
, MTL_MATERIAL_STATUSES ST1
, MTL_MATERIAL_STATUSES ST2
WHERE V.SPEC_ID = S.SPEC_ID
AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND PV.ORGANIZATION_ID(+) = V.ORGANIZATION_ID
AND ST1.STATUS_ID(+) = V.IN_SPEC_LOT_STATUS_ID
AND ST2.STATUS_ID(+) = V.OUT_OF_SPEC_LOT_STATUS_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ('LANG')
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ('LANG') UNION SELECT V.SPEC_VR_ID
, V.SPEC_ID
, DECODE (RULE_TYPE
, 'R'
, V.RESOURCE_ORGANIZATION_ID
, 'L'
, V.LOCATOR_ORGANIZATION_ID
, TO_NUMBER (NULL) )
, PV.ORGANIZATION_CODE
, V.RULE_TYPE
, S.SPEC_NAME
, S.SPEC_VERS
, S.OWNER_ORGANIZATION_ID
, S.OWNER_ID
, TO_NUMBER (NULL) INVENTORY_ITEM_ID
, NULL ITEM_NUMBER
, NULL DESCRIPTION
, NULL REVISION
, NULL GRADE_CODE
, S.SPEC_STATUS
, P.DESCRIPTION SPEC_STATUS_DESC
, NULL PARENT_LOT_NUMBER
, NULL LOT_NUMBER
, V.SUBINVENTORY
, V.LOCATOR_ID
, L.CONCATENATED_SEGMENTS LOCATOR
, V.SPEC_VR_STATUS
, T.DESCRIPTION SPEC_VR_STATUS_DESC
, V.START_DATE
, V.END_DATE
, V.SAMPLING_PLAN_ID
, NULL SAMPLE_INV_TRANS_IND
, NULL CONTROL_LOT_ATTRIB_IND
, NULL OUT_OF_SPEC_LOT_STATUS_ID
, NULL OUT_OF_SPEC_LOT_STATUS_CODE
, NULL IN_SPEC_LOT_STATUS_ID
, NULL IN_SPEC_LOT_STATUS_CODE
, NULL CONTROL_BATCH_STEP_IND
, NULL LOT_OPTIONAL_ON_SAMPLE
, NULL DELAYED_LOT_ENTRY
, NULL COA_TYPE
, NULL COA_AT_SHIP_IND
, NULL COA_AT_INVOICE_IND
, NULL COA_REQ_FROM_SUPL_IND
, NULL AUTO_SAMPLE_IND
, TO_NUMBER (NULL) BATCH_ID
, TO_NUMBER (NULL) RECIPE_ID
, NULL RECIPE_NO
, TO_NUMBER (NULL) RECIPE_VERSION
, TO_NUMBER (NULL) FORMULA_ID
, TO_NUMBER (NULL) FORMULALINE_ID
, NULL FORMULA_NO
, TO_NUMBER (NULL) FORMULA_VERS
, TO_NUMBER (NULL) ROUTING_ID
, NULL ROUTING_NO
, TO_NUMBER (NULL) ROUTING_VERS
, TO_NUMBER (NULL) STEP_ID
, TO_NUMBER (NULL) STEP_NO
, TO_NUMBER (NULL) OPRN_ID
, NULL OPRN_NO
, TO_NUMBER (NULL) OPRN_VERS
, TO_NUMBER (NULL) CHARGE
, TO_NUMBER (NULL) CUST_ID
, TO_NUMBER (NULL) ORDER_ID
, TO_NUMBER (NULL) ORDER_LINE
, TO_NUMBER (NULL) ORDER_LINE_ID
, TO_NUMBER (NULL) SHIP_TO_SITE_ID
, TO_NUMBER (NULL) ORG_ID
, TO_NUMBER (NULL) SUPPLIER_ID
, TO_NUMBER (NULL) SUPPLIER_SITE_ID
, TO_NUMBER (NULL) PO_HEADER_ID
, TO_NUMBER (NULL) PO_LINE_ID
, V.RESOURCES
, V.RESOURCE_INSTANCE_ID
, V.DELETE_MARK
, V.TEXT_CODE
, V.CREATION_DATE
, V.CREATED_BY
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_DATE
, V.LAST_UPDATE_LOGIN
, TO_NUMBER (NULL) MATERIAL_DETAIL_ID
FROM GMD_MONITORING_SPEC_VRS V
, GMD_SPECIFICATIONS_B S
, MTL_PARAMETERS PV
, MTL_ITEM_LOCATIONS_KFV L
, GMD_QC_STATUS_TL P
, GMD_QC_STATUS_TL T
WHERE V.SPEC_ID = S.SPEC_ID
AND PV.ORGANIZATION_ID(+) = DECODE (RULE_TYPE
, 'R'
, V.RESOURCE_ORGANIZATION_ID
, 'L'
, V.LOCATOR_ORGANIZATION_ID
, NULL )
AND L.ORGANIZATION_ID(+) = DECODE (RULE_TYPE
, 'R'
, V.RESOURCE_ORGANIZATION_ID
, 'L'
, V.LOCATOR_ORGANIZATION_ID
, NULL )
AND L.INVENTORY_LOCATION_ID(+) = V.LOCATOR_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ('LANG')
AND S.SPEC_STATUS = P.STATUS_CODE
AND P.ENTITY_TYPE = 'S'
AND P.LANGUAGE = USERENV ('LANG') UNION SELECT V.SPEC_VR_ID
, V.SPEC_ID
, TO_NUMBER (NULL)
, NULL
, 'T' SPEC_TYPE
, S.SPEC_NAME
, S.SPEC_VERS
, S.OWNER_ORGANIZATION_ID
, S.OWNER_ID
, TO_NUMBER (NULL) INVENTORY_ITEM_ID
, NULL ITEM_NUMBER
, NULL DESCRIPTION
, NULL REVISION
, NULL GRADE_CODE
, S.SPEC_STATUS
, L.DESCRIPTION SPEC_STATUS_DESC
, NULL PARENT_LOT_NUMBER
, NULL LOT_NUMBER
, NULL SUBINVENTORY
, TO_NUMBER (NULL) LOCATOR_ID
, NULL LOCATOR
, V.SPEC_VR_STATUS
, T.DESCRIPTION SPEC_VR_STATUS_DESC
, V.START_DATE
, V.END_DATE
, V.SAMPLING_PLAN_ID
, NULL SAMPLE_INV_TRANS_IND
, NULL CONTROL_LOT_ATTRIB_IND
, TO_NUMBER (NULL) OUT_OF_SPEC_LOT_STATUS_ID
, NULL OUT_OF_SPEC_LOT_STATUS_CODE
, TO_NUMBER (NULL) IN_SPEC_LOT_STATUS_ID
, NULL IN_SPEC_LOT_STATUS_CODE
, NULL CONTROL_BATCH_STEP_IND
, NULL LOT_OPTIONAL_ON_SAMPLE
, NULL DELAYED_LOT_ENTRY
, NULL COA_TYPE
, NULL COA_AT_SHIP_IND
, NULL COA_AT_INVOICE_IND
, NULL COA_REQ_FROM_SUPL_IND
, NULL AUTO_SAMPLE_IND
, TO_NUMBER (NULL) BATCH_ID
, TO_NUMBER (NULL) RECIPE_ID
, NULL RECIPE_NO
, TO_NUMBER (NULL) RECIPE_VERSION
, TO_NUMBER (NULL) FORMULA_ID
, TO_NUMBER (NULL) FORMULALINE_ID
, NULL FORMULA_NO
, TO_NUMBER (NULL) FORMULA_VERS
, TO_NUMBER (NULL) ROUTING_ID
, NULL ROUTING_NO
, TO_NUMBER (NULL) ROUTING_VERS
, TO_NUMBER (NULL) STEP_ID
, TO_NUMBER (NULL) STEP_NO
, TO_NUMBER (NULL) OPRN_ID
, NULL OPRN_NO
, TO_NUMBER (NULL) OPRN_VERS
, TO_NUMBER (NULL) CHARGE
, TO_NUMBER (NULL) CUST_ID
, TO_NUMBER (NULL) ORDER_ID
, TO_NUMBER (NULL) ORDER_LINE
, TO_NUMBER (NULL) ORDER_LINE_ID
, TO_NUMBER (NULL) SHIP_TO_SITE_ID
, TO_NUMBER (NULL) ORG_ID
, TO_NUMBER (NULL) SUPPLIER_ID
, TO_NUMBER (NULL) SUPPLIER_SITE_ID
, TO_NUMBER (NULL) PO_HEADER_ID
, TO_NUMBER (NULL) PO_LINE_ID
, NULL RESOURCES
, TO_NUMBER (NULL) RESOURCE_INSTANCE_ID
, V.DELETE_MARK
, TO_NUMBER (NULL) TEXT_CODE
, V.CREATION_DATE
, V.CREATED_BY
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_DATE
, V.LAST_UPDATE_LOGIN
, TO_NUMBER (NULL) MATERIAL_DETAIL_ID
FROM GMD_STABILITY_SPEC_VRS V
, GMD_SPECIFICATIONS_B S
, GMD_QC_STATUS_TL T
, GMD_QC_STATUS_TL L
WHERE V.SPEC_ID = S.SPEC_ID
AND V.SPEC_VR_STATUS = T.STATUS_CODE
AND S.SPEC_STATUS = L.STATUS_CODE
AND T.ENTITY_TYPE = 'S'
AND L.ENTITY_TYPE = 'S'
AND T.LANGUAGE = USERENV ('LANG')
AND L.LANGUAGE = USERENV ('LANG')