[Home] [Help]
2820:
2821: -- Cursors
2822: CURSOR c_get_se_id (p_composite_spec_disp_id NUMBER) IS
2823: SELECT sampling_event_id
2824: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd
2825: WHERE csd.composite_spec_disp_id = p_composite_spec_disp_id
2826: AND csd.event_spec_disp_id = esd.event_spec_disp_id
2827: AND csd.latest_ind = 'Y'
2828: AND csd.delete_mark = 0
3375:
3376: -- Cursors
3377: CURSOR c_composite (p_event_spec_disp_id NUMBER) IS
3378: SELECT 1
3379: FROM gmd_composite_spec_disp
3380: WHERE event_spec_disp_id = p_event_spec_disp_id
3381: AND nvl(latest_ind, 'N') = 'Y'
3382: ;
3383:
3511:
3512: -- Cursors
3513: CURSOR c_composite (p_event_spec_disp_id NUMBER) IS
3514: SELECT 1
3515: FROM gmd_composite_spec_disp
3516: WHERE event_spec_disp_id = p_event_spec_disp_id
3517: AND nvl(latest_ind, 'N') = 'Y'
3518: ;
3519:
3595:
3596: -- Cursors
3597: CURSOR c_composite (p_event_spec_disp_id NUMBER, p_result_id NUMBER) IS
3598: SELECT 1
3599: FROM gmd_composite_spec_disp csd, gmd_composite_results cr, gmd_composite_result_assoc cra
3600: WHERE csd.event_spec_disp_id = p_event_spec_disp_id
3601: AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
3602: AND cr.composite_result_id = cra.composite_result_id
3603: AND cra.result_id = p_result_id
3967: l_test_type VARCHAR2(1);
3968: l_return_status VARCHAR2(1);
3969: sample_cnt PLS_INTEGER;
3970:
3971: l_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3972: l_composite_result GMD_COMPOSITE_RESULTS%ROWTYPE;
3973: l_composite_result_assoc GMD_COMPOSITE_RESULT_ASSOC%ROWTYPE;
3974:
3975: l_out_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3971: l_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3972: l_composite_result GMD_COMPOSITE_RESULTS%ROWTYPE;
3973: l_composite_result_assoc GMD_COMPOSITE_RESULT_ASSOC%ROWTYPE;
3974:
3975: l_out_composite_spec_disp GMD_COMPOSITE_SPEC_DISP%ROWTYPE;
3976: l_out_composite_result GMD_COMPOSITE_RESULTS%ROWTYPE;
3977:
3978: -- Exceptions
3979: e_comp_spec_disp_insert_error EXCEPTION;
4008: gmd_debug.put_line(' ESD ID-' || p_event_spec_disp_id || ' Spec ID -' || l_spec_id);
4009: END IF;
4010:
4011: -- Update all previous composite for this p_event_spec_disp_id as Not-Latest
4012: UPDATE gmd_composite_spec_disp
4013: SET latest_ind = NULL
4014: WHERE event_spec_disp_id = p_event_spec_disp_id
4015: AND latest_ind = 'Y'
4016: ;
4015: AND latest_ind = 'Y'
4016: ;
4017:
4018: IF (l_debug = 'Y') THEN
4019: gmd_debug.put_line(' Changed latest_ind for old rows in gmd_composite_spec_disp');
4020: END IF;
4021:
4022: -- Now, create a row in GMD_COMPOSITE_SPEC_DISP with latest_ind = 'Y'
4023:
4018: IF (l_debug = 'Y') THEN
4019: gmd_debug.put_line(' Changed latest_ind for old rows in gmd_composite_spec_disp');
4020: END IF;
4021:
4022: -- Now, create a row in GMD_COMPOSITE_SPEC_DISP with latest_ind = 'Y'
4023:
4024: -- Construct the record
4025: l_composite_spec_disp.EVENT_SPEC_DISP_ID := p_event_spec_disp_id;
4026: l_composite_spec_disp.DISPOSITION := '3C';
4031: l_composite_spec_disp.LAST_UPDATE_DATE := l_date;
4032: l_composite_spec_disp.LAST_UPDATED_BY := l_user_id;
4033:
4034: -- We are ready for insert in GME_COMPOSITE_SPEC_DISP, so then lets do it.
4035: IF NOT(GMD_COMPOSITE_SPEC_DISP_PVT.Insert_Row(
4036: p_composite_spec_disp => l_composite_spec_disp,
4037: x_composite_spec_disp => l_out_composite_spec_disp)
4038: )
4039: THEN
4042: END IF;
4043: l_composite_spec_disp.COMPOSITE_SPEC_DISP_ID := l_out_composite_spec_disp.COMPOSITE_SPEC_DISP_ID;
4044:
4045: IF (l_debug = 'Y') THEN
4046: gmd_debug.put_line(' Record inserted in GMD_COMPOSITE_SPEC_DISP, CSD ID-' || l_composite_spec_disp.composite_spec_disp_id);
4047: END IF;
4048:
4049: -- Go through all the unique tests for this event spec disp id that
4050: -- we are compositing.
7238: svr.out_of_spec_lot_status_id,
7239: svr.organization_id
7240: FROM gmd_event_spec_disp esd,
7241: gmd_com_spec_vrs_vl svr,
7242: gmd_composite_spec_disp csd
7243: WHERE esd.event_spec_disp_id = p_event_spec_disp_id
7244: AND esd.spec_used_for_lot_attrib_ind = 'Y'
7245: AND svr.spec_vr_id = esd.spec_vr_id
7246: AND esd.delete_mark = 0
7407: -- B2820787 START
7408: -- REPLACED FOLLOWING WITH
7409: -- SELECT count(1)
7410: -- INTO l_count
7411: -- FROM gmd_composite_spec_disp csd,
7412: -- gmd_composite_results cr
7413: -- WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7414: -- AND csd.latest_ind = 'Y'
7415: -- AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
7418: -- THE FOLLOWING
7419:
7420: SELECT count(1)
7421: INTO l_count_with_spec
7422: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
7423: gmd_composite_results cr, gmd_spec_tests_b st
7424: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7425: AND csd.latest_ind = 'Y'
7426: AND csd.event_spec_disp_id = esd.event_spec_disp_id
7434: ;
7435:
7436: SELECT count(1)
7437: INTO l_count_wo_spec
7438: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
7439: gmd_composite_results cr
7440: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7441: AND csd.latest_ind = 'Y'
7442: AND csd.event_spec_disp_id = esd.event_spec_disp_id
7471: END IF;
7472:
7473: SELECT composite_spec_disp_id
7474: INTO l_composite_spec_disp_id
7475: FROM gmd_composite_spec_disp csd
7476: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7477: AND csd.latest_ind = 'Y'
7478: ;
7479: