OPM Quality – Query to List Specification and Tests Information

/*********************************************************
*PURPOSE: Query to List Specification and Tests Information *
*AUTHOR: Shailender Thallam *
**********************************************************/
SELECT gsb.spec_id,
  gsb.spec_name,
  gsb.spec_vers version,
  gs.meaning spec_status,
  lkp1.meaning spec_type,
  msi.segment1 item,
  msi.description item_desc,
  fu.user_name owner,
  gstb.seq,
  gqtb.test_code,
  gtmb.test_method_code,
  gstb.test_qty,
  gstb.test_qty_uom,
  gstb.test_replicate
FROM gmd_specifications_b gsb,
  gmd_specifications_tl gst,
  fnd_user fu,
  gmd_status_tl gs,
  gem_lookups lkp1,
  mtl_system_items_b msi,
  gmd_spec_tests_b gstb,
  gmd_qc_tests_b gqtb,
  gmd_test_methods_b gtmb
WHERE 1             = 1
AND gsb.spec_id     = 523
AND gsb.spec_id     = gst.spec_id
AND gst.language    = userenv('lang')
AND gsb.owner_id    = fu.user_id
AND gsb.spec_status = gs.status_code
AND gs.language     = userenv('lang')
  --
AND gsb.spec_type    = lkp1.lookup_code
AND lkp1.lookup_type = 'GMD_QC_SPEC_TYPE'
  --
AND gsb.inventory_item_id     = msi.inventory_item_id
AND gsb.owner_organization_id = msi.organization_id
  --
AND gsb.spec_id = gstb.spec_id
  --
AND gstb.test_id = gqtb.test_id
  --
AND gqtb.test_method_id = gtmb.test_method_id
  --
ORDER BY gstb.seq ;