Introduction:
This blog has the SQL query that retrieves all Quality test samples along with their corresponding results.
Cause of the issue:
Business wants a report that contains the details of the test samples with their results.
How do we solve:
Create a report with the below SQL query
SELECT
gs.source,
gs.sample_no,
gs.sample_desc,
msi.description,
gs.lot_no,
gs.sublot_no,
(select description from fnd_lookup_values flv, gmd_sample_spec_disp gssd
where gssd.sample_id = gs.sample_id
and flv.lookup_type = ‘gmd_qc_sample_disp’
and language = ‘us’
and gssd.disposition = flv.lookup_code
and gssd.delete_mark = 0) sample_disposition,
gs.creation_date as sample_creation_date,
gs.attribute1 as quality_label_remarks,
gs.attribute2 as sample_comments,
gr.seq,
gr.test_id,
gr.test_replicate_cnt,
gr.result_value_num,
gr.result_value_char,
gr.result_date,
gr.tester,
gr.test_provider_code,
gr.attribute1 as supplier_result_yn,
gr.attribute2 as supplier_result_val,
gr.attribute3 as result_evaluation,
gr.attribute4 as action_code,
gr.attribute5 as min_value_num,
gr.attribute6 as target_value_char,
to_char(gs.creation_date, ‘yyyy’) as sample_year,
to_char(gs.creation_date, ‘q’) as sample_quarter,
to_char(gs.creation_date, ‘mm’) as sample_month,
to_char(gs.creation_date, ‘dd’) as sample_day,
to_char(gr.result_date, ‘yyyy’) as result_year,
to_char(gr.result_date, ‘q’) as result_quarter,
to_char(gr.result_date, ‘mm’) as result_month,
to_char(gr.result_date, ‘dd’) as result_day
FROM
gmd_samples gs,
gmd_results gr,
mtl_system_items_b msi
where gs.sample_id = gr.sample_id
and msi.inventory_item_id = gs.inventory_item_id(+)
and msi.organization_id = gs.organization_id(+)
and gr.result_date between nvl(:frm_date,’01-jan-1951′) and
nvl(:to_date,sysdate)
and gs.delete_mark = 0
and gr.delete_mark = 0;
