OPM Quality Basic Queries

select test_class Class,test_class_desc Description from GMD_TEST_CLASSES

select * from gmd_test_classes------------------------CLASSSES AVAILABLE

select * from  GMD_TEST_METHODS---------TEST METHODS\

SELECT TEST_METHOD_CODE METHOD,TEST_METHOD_DESC DESCRIPTION , TEST_QTY QTY, TEST_QTY_UOM UOM, DISPLAY_PRECISION Stored_Precision,
Days, hours,Minutes, seconds,TEST_REPLICATE Replicate from GMD_TEST_METHODS------------------------------------Test_Methods

select * from GMD_UNITS ---------------------------------Test_Units
select QCUNIT_CODE Units, QCUNIT_DESC Description from gmd_units-----------------Test Units

select * from MTL_ACTIONS --------------------action codes
select ACTION_CODE Action , DESCRIPTION from mtl_actions-----action codes


select ORGANIZATION_ID Organization, SAMPLE_LAST_ASSIGNED Sample_Last_Assigned,SS_LAST_ASSIGNED SS_Last_Assigned
from gmd_quality_config----------------------------------Process Quality Parameters
select * from gmd_quality_config --------------------------------Process Quality Parameters

select  SAMPLING_PLAN_NAME Plan_Name, SAMPLING_PLAN_DESC Description,SAMPLE_CNT_REQ Count,
SAMPLE_QTY Quantity, SAMPLE_QTY_UOM UOM,
Decode( Frequency_type , 'F' , 'Fixed Number') Frequency, FREQUENCY_CNT Per,
Decode(FREQUENCY_PER , 'FS','Batch step',
                       'FR', 'Receipt',
                       'FB', 'Batch') Sample_UOM,
RESERVE_CNT_REQ Res_Count, RESERVE_QTY Res_Qty,
ARCHIVE_CNT_REQ Arch_Count,ARCHIVE_QTY Arch_Qty
from gmd_sampling_plans----------------------------------------------------sampling Plan
select * from gmd_sampling_plans------------sampling Plan




select * from GMD_QC_TESTS where test_code='% BTM'--------------------TEST DATA

select TE.TEST_CODE Test, TE.TEST_DESC Description,TE.TEST_CLASS Class,
ME.TEST_METHOD_CODE Method,ME.TEST_METHOD_DESC Method_DESCRIPTION,
Decode (TE.TEST_TYPE,'L', 'Numeric range With Display Text',
                   'E', 'Expression',
                   'N','Numeric Range',
                   'T','Text Range',
                   'U','Non-Validated',
                   'V','List of Test Values') Data_Type,
TE.TEST_UNIT Unit,
Decode (TE.PRIORITY,'1L','Low',
                 '5N','Normal',
                 '8H','high') Priority,
TE.MIN_VALUE_NUM Range_from, TE.MAX_VALUE_NUM Range_To,
TE.DISPLAY_PRECISION Stored_Precision,TE.REPORT_PRECISION Report_Precision,
TE.EXPRESSION,
tv.MIN_NUM Min_Value,tv.max_num Max_Value,tv.DISPLAY_LABEL_NUMERIC_RANGE Display
from GMD_QC_TESTS TE,
GMD_TEST_METHODS ME,
gmd_qc_test_values tv
where te.test_method_id=me.test_method_id
and te.test_id=tv.test_id------------------------------------------------Test Data

select  * from gmd_qc_test_values---------------------------Values in Test Data Samples


select * from gmd_specifications where spec_name ='726 Sell / 200 Kg'----------------Process Quality Specifiction

select gmsp.Spec_name Spec,gmsp.SPEC_DESC Description,gmsp.SPEC_VERS Version,gms.description Status,
Decode (gmsp.SPEC_TYPE,'I','Item',
                  'M','Monitoring') Spec_Type,
gmsp.OVERLAY_IND Overlay,gmsp.base_spec_id Base_Spec,
gmsp.INVENTORY_ITEM_ID Item_ID,
kfiv.CONCATENATED_SEGMENTS Item_code,kfiv.description Item_desc,
gmsp.GRADE_CODE Grade,gmsp.OWNER_ORGANIZATION_ID Owner_Organization,fnu.user_name,
gst.seq Target_Seq,
(select gct.TEST_CLASS from GMD_QC_TESTS gct where gct.test_id=gst.test_id) Target_Class,
(select TEST_METHOD_CODE from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method,
(select TEST_METHOD_DESC from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method_Desc,
gst.MIN_VALUE_NUM Target_Minimum,gst.MAX_VALUE_NUM Target_Maximum,gst.TEST_QTY Target_Quantity,
gst.TEST_QTY_UOM Target_UOM, gst.TEST_REPLICATE Target_Replicate,
Decode(gst.TEST_PRIORITY,'1L','Low',
                 '5N','Normal',
                 '8H','High') Target_Priority,
gst.OPTIONAL_IND Target_optional,
gst.DISPLAY_PRECISION Formating_Stored_Precision,
gst.REPORT_PRECISION Report_Stored_Precision
from gmd_specifications gmsp,
gmd_status gms,
mtl_system_items_kfv kfiv,
gmd_spec_tests gst,
fnd_user fnu
where gmsp.spec_status=gms.status_code
and gmsp.spec_id=gst.spec_id
--and gst.test_id=gct.test_id
and gmsp.INVENTORY_ITEM_ID=kfiv.INVENTORY_ITEM_ID
and gmsp.OWNER_ORGANIZATION_ID=kfiv.ORGANIZATION_ID
and fnu.user_id=gmsp.owner_id---------------------------------------------Product Quality specifications



select gmsp.Spec_name Spec,gmsp.SPEC_DESC Description,gmsp.SPEC_VERS Version,gms.description Status,
Decode (gmsp.SPEC_TYPE,'I','Item',
                  'M','Monitoring') Spec_Type,
gmsp.OVERLAY_IND Overlay,gmsp.base_spec_id Base_Spec,
gmsp.INVENTORY_ITEM_ID Item_ID,
kfiv.CONCATENATED_SEGMENTS Item_code,kfiv.description Item_desc,
gmsp.GRADE_CODE Grade,gmsp.OWNER_ORGANIZATION_ID Owner_Organization,fnu.user_name,
gst.seq Target_Seq,
(select gct.TEST_CLASS from GMD_QC_TESTS gct where gct.test_id=gst.test_id) Target_Class,
(select TEST_METHOD_CODE from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method,
(select TEST_METHOD_DESC from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method_Desc,
gst.MIN_VALUE_NUM Target_Minimum,gst.MAX_VALUE_NUM Target_Maximum,gst.TEST_QTY Target_Quantity,
gst.TEST_QTY_UOM Target_UOM, gst.TEST_REPLICATE Target_Replicate,
Decode(gst.TEST_PRIORITY,'1L','Low',
                 '5N','Normal',
                 '8H','High') Target_Priority,
gst.OPTIONAL_IND Target_optional,
gst.DISPLAY_PRECISION Formating_Stored_Precision,
gst.REPORT_PRECISION Report_Stored_Precision,
ver.ORGANIZATION_CODE VALIDITY_ORG_CODE ,
Decode (ver.SPEC_TYPE,'I','Inventory',
                  'W','WIP',
                  'C','Customer',
                  'S','Supplier') VALIDITY_Spec_Type,
Decode (ver.DELETE_MARK,'0','No',
                  '1','Yes') Validity_Deleted,
ver.SPEC_VR_STATUS_DESC Validity_Spec_rule,
ver.start_date Validity_start_date,
ver.end_date Validity_end_date,
ver.PARENT_LOT_NUMBER Validity_Parent_Lot,
ver.LOT_NUMBER Validity_Lot_Number,
ver.SUBINVENTORY Validity_SubInventory,
ver.IN_SPEC_LOT_STATUS_CODE Validity_LOT_Status,
ver.OUT_OF_SPEC_LOT_STATUS_CODE Validity_out_of_spec_Lot
from gmd_specifications gmsp,
gmd_status gms,
mtl_system_items_kfv kfiv,
gmd_spec_tests gst,
GMD_ALL_SPEC_VRS_VL ver,
fnd_user fnu
where gmsp.spec_status=gms.status_code
and gmsp.spec_id=gst.spec_id
and ver.spec_id=gmsp.spec_id
and ver.spec_id=gst.spec_id
and gmsp.INVENTORY_ITEM_ID=kfiv.INVENTORY_ITEM_ID
and gmsp.OWNER_ORGANIZATION_ID=kfiv.ORGANIZATION_ID
and fnu.user_id=gmsp.owner_id---------------------------------------------Product Quality specifications With Validity Rules