[Home] [Help]
2655:
2656: -- Cursors
2657: CURSOR c_get_se_id (p_composite_spec_disp_id NUMBER) IS
2658: SELECT sampling_event_id
2659: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd
2660: WHERE csd.composite_spec_disp_id = p_composite_spec_disp_id
2661: AND csd.event_spec_disp_id = esd.event_spec_disp_id
2662: AND csd.latest_ind = 'Y'
2663: AND csd.delete_mark = 0
3206:
3207: -- Cursors
3208: CURSOR c_composite (p_event_spec_disp_id NUMBER) IS
3209: SELECT 1
3210: FROM gmd_composite_spec_disp
3211: WHERE event_spec_disp_id = p_event_spec_disp_id
3212: AND nvl(latest_ind, 'N') = 'Y'
3213: ;
3214:
3342:
3343: -- Cursors
3344: CURSOR c_composite (p_event_spec_disp_id NUMBER) IS
3345: SELECT 1
3346: FROM gmd_composite_spec_disp
3347: WHERE event_spec_disp_id = p_event_spec_disp_id
3348: AND nvl(latest_ind, 'N') = 'Y'
3349: ;
3350:
3426:
3427: -- Cursors
3428: CURSOR c_composite (p_event_spec_disp_id NUMBER, p_result_id NUMBER) IS
3429: SELECT 1
3430: FROM gmd_composite_spec_disp csd, gmd_composite_results cr, gmd_composite_result_assoc cra
3431: WHERE csd.event_spec_disp_id = p_event_spec_disp_id
3432: AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
3433: AND cr.composite_result_id = cra.composite_result_id
3434: AND cra.result_id = p_result_id
3798: l_test_type VARCHAR2(1);
3799: l_return_status VARCHAR2(1);
3800: sample_cnt PLS_INTEGER;
3801:
3802: l_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3803: l_composite_result GMD_COMPOSITE_RESULTS%ROWTYPE;
3804: l_composite_result_assoc GMD_COMPOSITE_RESULT_ASSOC%ROWTYPE;
3805:
3806: l_out_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3802: l_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3803: l_composite_result GMD_COMPOSITE_RESULTS%ROWTYPE;
3804: l_composite_result_assoc GMD_COMPOSITE_RESULT_ASSOC%ROWTYPE;
3805:
3806: l_out_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3807: l_out_composite_result GMD_COMPOSITE_RESULTS%ROWTYPE;
3808:
3809: -- Exceptions
3810: e_comp_spec_disp_insert_error EXCEPTION;
3839: gmd_debug.put_line(' ESD ID-' || p_event_spec_disp_id || ' Spec ID -' || l_spec_id);
3840: END IF;
3841:
3842: -- Update all previous composite for this p_event_spec_disp_id as Not-Latest
3843: UPDATE gmd_composite_spec_disp
3844: SET latest_ind = NULL
3845: WHERE event_spec_disp_id = p_event_spec_disp_id
3846: AND latest_ind = 'Y'
3847: ;
3846: AND latest_ind = 'Y'
3847: ;
3848:
3849: IF (l_debug = 'Y') THEN
3850: gmd_debug.put_line(' Changed latest_ind for old rows in gmd_composite_spec_disp');
3851: END IF;
3852:
3853: -- Now, create a row in GMD_COMPOSITE_SPEC_DISP with latest_ind = 'Y'
3854:
3849: IF (l_debug = 'Y') THEN
3850: gmd_debug.put_line(' Changed latest_ind for old rows in gmd_composite_spec_disp');
3851: END IF;
3852:
3853: -- Now, create a row in GMD_COMPOSITE_SPEC_DISP with latest_ind = 'Y'
3854:
3855: -- Construct the record
3856: l_composite_spec_disp.EVENT_SPEC_DISP_ID := p_event_spec_disp_id;
3857: l_composite_spec_disp.DISPOSITION := '3C';
3862: l_composite_spec_disp.LAST_UPDATE_DATE := l_date;
3863: l_composite_spec_disp.LAST_UPDATED_BY := l_user_id;
3864:
3865: -- We are ready for insert in GME_COMPOSITE_SPEC_DISP, so then lets do it.
3866: IF NOT(GMD_COMPOSITE_SPEC_DISP_PVT.Insert_Row(
3867: p_composite_spec_disp => l_composite_spec_disp,
3868: x_composite_spec_disp => l_out_composite_spec_disp)
3869: )
3870: THEN
3873: END IF;
3874: l_composite_spec_disp.COMPOSITE_SPEC_DISP_ID := l_out_composite_spec_disp.COMPOSITE_SPEC_DISP_ID;
3875:
3876: IF (l_debug = 'Y') THEN
3877: gmd_debug.put_line(' Record inserted in GMD_COMPOSITE_SPEC_DISP, CSD ID-' || l_composite_spec_disp.composite_spec_disp_id);
3878: END IF;
3879:
3880: -- Go through all the unique tests for this event spec disp id that
3881: -- we are compositing.
6861: svr.out_of_spec_lot_status_id,
6862: svr.organization_id
6863: FROM gmd_event_spec_disp esd,
6864: gmd_com_spec_vrs_vl svr,
6865: gmd_composite_spec_disp csd
6866: WHERE esd.event_spec_disp_id = p_event_spec_disp_id
6867: AND esd.spec_used_for_lot_attrib_ind = 'Y'
6868: AND svr.spec_vr_id = esd.spec_vr_id
6869: AND esd.delete_mark = 0
7030: -- B2820787 START
7031: -- REPLACED FOLLOWING WITH
7032: -- SELECT count(1)
7033: -- INTO l_count
7034: -- FROM gmd_composite_spec_disp csd,
7035: -- gmd_composite_results cr
7036: -- WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7037: -- AND csd.latest_ind = 'Y'
7038: -- AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
7041: -- THE FOLLOWING
7042:
7043: SELECT count(1)
7044: INTO l_count_with_spec
7045: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
7046: gmd_composite_results cr, gmd_spec_tests_b st
7047: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7048: AND csd.latest_ind = 'Y'
7049: AND csd.event_spec_disp_id = esd.event_spec_disp_id
7057: ;
7058:
7059: SELECT count(1)
7060: INTO l_count_wo_spec
7061: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
7062: gmd_composite_results cr
7063: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7064: AND csd.latest_ind = 'Y'
7065: AND csd.event_spec_disp_id = esd.event_spec_disp_id
7094: END IF;
7095:
7096: SELECT composite_spec_disp_id
7097: INTO l_composite_spec_disp_id
7098: FROM gmd_composite_spec_disp csd
7099: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7100: AND csd.latest_ind = 'Y'
7101: ;
7102: