Oracle Specification Queries

Queries to get Specifications and its tests-

select gs.Spec_name "Specification",gs.spec_vers "Version",

gasv.organization_code "Owner Organization", fu.user_name " owner",

gasv.spec_status_desc "Spec STatus",

decode(gs.Spec_type,'I','Item','M','Monitoring')Spec_Type,

gs.Base_spec_id "Need name",

gasv.item_number "Item",

gs.spec_desc "Item Description",

gs.grade_code "Grade",

gqt.TEST_CLASS TEST,

gtm.TEST_METHOD_CODE,

gtm.TEST_METHOD_DESC,

Decode (gqt.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,

Decode (gqt.PRIORITY,'1L','Low',

'5N','Normal',

'8H','high') Priority,

gst.seq,

gst.from_base_ind,

gst.exclude_ind,

gst.modified_ind,

gst.test_qty,

gst.test_qty_uom,

gst.min_value_num,

gst.target_value_num,

gst.max_value_num,

gst.min_value_char,

gst.target_value_char,

gst.max_value_char,

gst.test_replicate,

Decode(gst.TEST_PRIORITY,'1L','Low',

'5N','Normal',

'8H','High') Target_Priority,

gst.optional_ind,

gst.retest_lot_expiry_ind,

gst.out_of_spec_action,

gst.check_result_interval,

gst.calc_uom_conv_ind,

gst.to_qty_uom,

gst.exp_error_type,

gst.below_spec_min,

gst.below_min_action_code,

gst.above_spec_min,

gst.above_min_action_code,

gst.below_spec_max,

gst.below_max_action_code,

gst.above_spec_max,

gst.above_max_action_code,

gst.test_display,

gst.print_spec_ind,

gst.print_result_ind,

gst.display_precision,

gst.report_precision,

gst.text_code,

gst.days,

gst.hours,

gst.minutes,

gst.seconds,

gst.viability_duration,

gst.attribute_category,

gst.attribute1,

gst.attribute2,

gst.attribute3,

gst.attribute4,

gst.attribute5,

gst.attribute6,

gst.attribute7,

gst.attribute8,

gst.attribute9,

gst.attribute10,

gst.attribute11,

gst.attribute12,

gst.attribute13,

gst.attribute14,

gst.attribute15,

gst.attribute16,

gst.attribute17,

gst.attribute18,

gst.attribute19,

gst.attribute20,

gst.attribute21,

gst.attribute22,

gst.attribute23,

gst.attribute24,

gst.attribute25,

gst.attribute26,

gst.attribute27,

gst.attribute28,

gst.attribute29,

gst.attribute30,

gst.creation_date,

gst.created_by,

gst.last_updated_by,

gst.last_update_date,

gst.last_update_login,

gst.use_to_control_step

from gmd_specifications gs, GMD_ALL_SPEC_VRS_VL gasv,fnd_user fu,gmd_spec_tests gst,

GMD_QC_TESTS gqt, GMD_TEST_METHODS gtm

where gs.spec_id= gasv.spec_id

and gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID

and gqt.test_id=gst.test_id

and gs.spec_id= gst.spec_id

and fu.user_id=gs.owner_id

--and gs.spec_id = &SPEC_ID --gs.spec_name like '&SPEC_NAME'

order by gs.spec_id,gst.seq;

Query to get the specifications header details-