[Home] [Help]
112:
113: -- Cursors
114: CURSOR c_event_disp_sm(p_sample_id NUMBER) IS
115: SELECT event_spec_disp_id
116: FROM gmd_samples gs, gmd_event_spec_disp ge
117: WHERE gs.sampling_event_id = ge.sampling_event_id
118: AND ge.spec_used_for_lot_attrib_ind = 'Y'
119: AND gs.sample_id = p_id
120: AND ge.delete_mark = 0
121: ;
122:
123: CURSOR c_event_disp_se(p_sample_id NUMBER) IS
124: SELECT DISTINCT event_spec_disp_id
125: FROM gmd_samples gs, gmd_event_spec_disp ge
126: WHERE gs.sampling_event_id = ge.sampling_event_id
127: AND gs.sampling_event_id = p_id
128: AND ge.spec_used_for_lot_attrib_ind = 'Y'
129: AND ge.delete_mark = 0
506: l_pos VARCHAR2(10);
507:
508: CURSOR c_non_accept_w_spec IS
509: SELECT count(1)
510: FROM gmd_event_spec_disp esd,
511: gmd_results r,
512: gmd_spec_results sr,
513: gmd_spec_tests_b st
514: WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
540: AND delete_mark = 0 ;
541:
542: CURSOR c_non_accept_w_spec_cr IS
543: SELECT count(1)
544: FROM gmd_event_spec_disp esd, gmd_composite_spec_disp csd,
545: gmd_composite_results cr, gmd_spec_tests_b st
546: WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
547: AND csd.event_spec_disp_id = esd.event_spec_disp_id
548: AND csd.latest_ind = 'Y'
553: AND (st.optional_ind IS NULL OR (l_include_optional = 'Y' and st.optional_ind = 'Y' and (cr.mean IS NOT NULL or cr.mode_char IS NOT NULL)));
554:
555: CURSOR c_non_accept_wo_spec_cr IS
556: SELECT count(1)
557: FROM gmd_event_spec_disp esd, gmd_composite_spec_disp csd,
558: gmd_composite_results cr
559: WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
560: AND csd.event_spec_disp_id = esd.event_spec_disp_id
561: AND csd.latest_ind = 'Y'
564: AND cr.test_id NOT IN (SELECT st.test_id FROM gmd_spec_tests_b st WHERE st.spec_id = esd.spec_id);
565:
566: CURSOR c_non_reject_w_spec IS
567: SELECT count(1)
568: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
569: gmd_spec_tests_b st
570: WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
571: AND esd.event_spec_disp_id = sr.event_spec_disp_id
572: AND sr.result_id = r.result_id
589: AND sr.delete_mark = 0;
590:
591: CURSOR c_reject_w_spec_no_av IS
592: SELECT count(1)
593: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
594: gmd_spec_tests_b st
595: WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
596: AND esd.event_spec_disp_id = sr.event_spec_disp_id
597: AND sr.result_id = r.result_id
621:
622: CURSOR c_get_evt_org_id IS
623: SELECT organization_id
624: FROM gmd_sampling_events gse,
625: gmd_event_spec_disp gesd,
626: gmd_composite_spec_disp gcsd
627: WHERE gse.sampling_event_id = gesd.sampling_event_id
628: AND gesd.event_spec_disp_id = gcsd.event_spec_disp_id
629: AND gcsd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id;
1047: INTO l_update_disp_rec.curr_disposition,l_update_disp_rec.event_spec_disp_id,
1048: l_spec_id,l_spec_vr_id,
1049: l_spec_name,l_spec_vers
1050: FROM gmd_sample_spec_disp a,
1051: gmd_event_spec_disp b,
1052: gmd_specifications_b c
1053: WHERE a.sample_id = l_update_disp_rec.sample_id
1054: AND a.event_spec_disp_id = b.event_spec_disp_id
1055: AND a.delete_mark = 0
1164: INTO l_organization_id,l_inventory_item_id,l_parent_lot_number,l_lot_number,l_grade_control_flag,l_lot_control_code,l_child_lot_flag,l_lot_status_enabled,
1165: l_sampling_event_date,l_subinventory,l_locator_id,l_locator,l_lpn_id,l_lpn,l_wms_enabled_flag,
1166: l_sample_type,l_source,l_spec_id,l_spec_vr_id,l_update_disp_rec.curr_disposition,l_spec_name,l_spec_vers
1167: FROM gmd_composite_spec_disp csd,
1168: gmd_event_spec_disp esd ,
1169: gmd_sampling_events gse,
1170: mtl_system_items_b msi,
1171: gmd_specifications_b gsb,
1172: mtl_item_locations_kfv mil,