[Home] [Help]
73: --| the necessary cursors |
74: --| PLOWE LPN ME 7027149 15-May-2008 support got LPN in group api |
75: --| KISHORE Bug No.8679485 Dt.16-Jul-2009 |
76: --| Added code to change Disposition in the tables GMD_SAMPLING_EVENTS, |
77: --| GMD_EVENT_SPEC_DISP while changing disposition from final to final like Accept to Reject
78: --| Reject -> Accept, Accept -> Accept With Variance etc...
79: --| Raju Bug 10169386 is fixed by calling the INV_MATERIAL_STATUS_PUB.update_status |
80: --| instead if private api to update the material status. |
81: --| PLOWE Bug 12829066 01-Nov-2011 Bug is that with multiple LPNs |
160: SELECT se.sampling_event_id
161: FROM gmd_sampling_events se,
162: -- gmd_specifications_b s,
163: -- gmd_inventory_spec_vrs ivr,
164: gmd_event_spec_disp esd
165: -- WHERE s.spec_id = ivr.spec_id
166: -- AND ivr.spec_vr_id = esd.spec_vr_id
167: -- AND esd.sampling_event_id = se.sampling_event_id
168: WHERE esd.sampling_event_id = se.sampling_event_id
197: -- Bug 4640143: added material detail id
198: CURSOR c_wip_sampling_event IS
199: SELECT se.sampling_event_id
200: FROM gmd_sampling_events se,
201: gmd_event_spec_disp esd
202: WHERE esd.sampling_event_id = se.sampling_event_id
203: AND ( (esd.spec_vr_id = p_spec_vr_id) OR
204: (esd.spec_vr_id is null and p_spec_vr_id is null ))
205: AND esd.spec_used_for_lot_attrib_ind = 'Y'
269: SELECT se.sampling_event_id
270: FROM gmd_sampling_events se,
271: -- gmd_specifications_b s,
272: -- gmd_customer_spec_vrs cvr,
273: gmd_event_spec_disp esd
274: -- WHERE s.spec_id = cvr.spec_id
275: -- AND cvr.spec_vr_id = esd.spec_vr_id
276: WHERE esd.sampling_event_id = se.sampling_event_id
277: AND ( (esd.spec_vr_id = p_spec_vr_id) OR
325: SELECT se.sampling_event_id
326: FROM gmd_sampling_events se,
327: -- gmd_specifications_b s,
328: -- gmd_supplier_spec_vrs svr,
329: gmd_event_spec_disp esd
330: -- WHERE s.spec_id = svr.spec_id
331: -- AND svr.spec_vr_id = esd.spec_vr_id
332: WHERE esd.sampling_event_id = se.sampling_event_id
333: AND ( (esd.spec_vr_id = p_spec_vr_id) OR
383: SELECT se.sampling_event_id
384: FROM gmd_sampling_events se,
385: -- gmd_specifications_b s,
386: -- gmd_monitoring_spec_vrs svr,
387: gmd_event_spec_disp esd
388: -- WHERE s.spec_id = svr.spec_id
389: -- AND svr.spec_vr_id = esd.spec_vr_id
390: WHERE esd.sampling_event_id = se.sampling_event_id
391: AND ( (esd.spec_vr_id = p_spec_vr_id) OR
429: SELECT se.sampling_event_id
430: FROM gmd_sampling_events se,
431: -- gmd_specifications_b s,
432: -- gmd_monitoring_spec_vrs svr,
433: gmd_event_spec_disp esd
434: -- WHERE s.spec_id = svr.spec_id
435: -- AND svr.spec_vr_id = esd.spec_vr_id
436: WHERE esd.sampling_event_id = se.sampling_event_id
437: AND se.source = p_sample.source
573: SELECT se.sampling_event_id
574: FROM gmd_sampling_events se,
575: gmd_specifications_b s,
576: gmd_inventory_spec_vrs ivr,
577: gmd_event_spec_disp esd
578: WHERE s.spec_id = ivr.spec_id
579: AND ivr.spec_vr_id = esd.spec_vr_id
580: AND esd.sampling_event_id = se.sampling_event_id
581: AND esd.spec_used_for_lot_attrib_ind = 'Y'
617: SELECT se.sampling_event_id
618: FROM gmd_sampling_events se,
619: gmd_specifications_b s,
620: gmd_wip_spec_vrs wvr,
621: gmd_event_spec_disp esd
622: WHERE s.spec_id = wvr.spec_id
623: AND wvr.spec_vr_id = esd.spec_vr_id
624: AND esd.sampling_event_id = se.sampling_event_id
625: AND esd.spec_used_for_lot_attrib_ind = 'Y'
686: SELECT se.sampling_event_id
687: FROM gmd_sampling_events se,
688: gmd_specifications_b s,
689: gmd_customer_spec_vrs cvr,
690: gmd_event_spec_disp esd
691: WHERE s.spec_id = cvr.spec_id
692: AND cvr.spec_vr_id = esd.spec_vr_id
693: AND esd.sampling_event_id = se.sampling_event_id
694: AND esd.spec_used_for_lot_attrib_ind = 'Y'
736: SELECT se.sampling_event_id
737: FROM gmd_sampling_events se,
738: gmd_specifications_b s,
739: gmd_supplier_spec_vrs svr,
740: gmd_event_spec_disp esd
741: WHERE s.spec_id = svr.spec_id
742: AND svr.spec_vr_id = esd.spec_vr_id
743: AND esd.sampling_event_id = se.sampling_event_id
744: AND esd.spec_used_for_lot_attrib_ind = 'Y'
782: SELECT se.sampling_event_id
783: FROM gmd_sampling_events se,
784: gmd_specifications_b s,
785: gmd_monitoring_spec_vrs svr,
786: gmd_event_spec_disp esd
787: WHERE s.spec_id = svr.spec_id
788: AND svr.spec_vr_id = esd.spec_vr_id
789: AND esd.sampling_event_id = se.sampling_event_id
790: AND ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
825: SELECT se.sampling_event_id
826: FROM gmd_sampling_events se,
827: gmd_specifications_b s,
828: gmd_monitoring_spec_vrs svr,
829: gmd_event_spec_disp esd
830: WHERE s.spec_id = svr.spec_id
831: AND svr.spec_vr_id = esd.spec_vr_id
832: AND esd.sampling_event_id = se.sampling_event_id
833: AND se.source = p_sample.source
1668:
1669: -- check whether the passed driving spec is current.If not raise error.
1670: SELECT SPEC_USED_FOR_LOT_ATTRIB_IND ,sampling_event_id,disposition
1671: INTO l_sample_disp_curr_flag , l_sampling_event_id , l_curr_event_disp
1672: FROM gmd_event_spec_disp
1673: WHERE event_spec_disp_id = l_event_spec_disp_id
1674: FOR UPDATE OF SPEC_USED_FOR_LOT_ATTRIB_IND NOWAIT;
1675:
1676: IF NVL(l_sample_disp_curr_flag,'N') = 'N' THEN
1870:
1871: -- check whether the passed driving spec is current.If not raise error.
1872: SELECT esd.SPEC_USED_FOR_LOT_ATTRIB_IND ,esd.sampling_event_id,csd.disposition
1873: INTO l_sample_disp_curr_flag , l_sampling_event_id ,l_compare_sample_disp
1874: FROM gmd_composite_spec_disp csd , gmd_event_spec_disp esd
1875: WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
1876: AND esd.event_spec_disp_id = csd.event_spec_disp_id
1877: FOR UPDATE OF esd.SPEC_USED_FOR_LOT_ATTRIB_IND , csd.disposition NOWAIT;
1878:
2032: -- SG disp formalised here
2033:
2034: -- get MAXIMUM sample disposition from all the samples for that event.
2035: SELECT MAX(ssd.disposition) INTO l_max_disposition
2036: FROM gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
2037: WHERE esd.event_spec_disp_id = l_event_spec_disp_id
2038: AND esd.event_spec_disp_id = ssd.event_spec_disp_id
2039: AND esd.delete_mark = 0
2040: AND ssd.delete_mark = 0
2068: END IF;
2069:
2070: SELECT MAX(ssd.disposition),MIN(ssd.disposition)
2071: INTO l_max_disposition,l_min_disposition
2072: FROM gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
2073: WHERE
2074: esd.event_spec_disp_id = l_event_spec_disp_id
2075: AND esd.event_spec_disp_id = ssd.event_spec_disp_id
2076: AND esd.delete_mark = 0
2110: gmd_debug.put_line('for l_sampling_event_id =>'||l_sampling_event_id);
2111: END IF;
2112:
2113: -- Set the disposition of the Event spec disp
2114: UPDATE gmd_event_spec_disp
2115: SET disposition = l_final_event_disp,
2116: last_updated_by = l_last_updated_by,
2117: last_update_date = l_last_update_date,
2118: last_update_login = l_last_update_login
2538:
2539: CURSOR cur_auto_complete_bstep_comp IS
2540: SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
2541: FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,
2542: GMD_EVENT_SPEC_DISP esd, GMD_COMPOSITE_SPEC_DISP csd
2543: WHERE csd.COMPOSITE_SPEC_DISP_ID = p_composite_spec_disp_id
2544: AND esd.EVENT_SPEC_DISP_ID = csd.EVENT_SPEC_DISP_ID
2545: AND gse.SAMPLING_EVENT_ID = esd.SAMPLING_EVENT_ID
2546: AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
2648: l_sampling_event_id,
2649: l_batch_id,
2650: l_step_no
2651: FROM GMD_COMPOSITE_SPEC_DISP csd,
2652: GMD_EVENT_SPEC_DISP esd ,
2653: GMD_SAMPLING_EVENTS gse,
2654: MTL_SYSTEM_ITEMS_b_kfv iim
2655: WHERE csd.composite_spec_disp_id = p_composite_spec_disp_id
2656: and csd.event_spec_disp_id = esd.event_spec_disp_id
6261: SELECT b.disposition,
6262: d.meaning sample_disposition,
6263: e.meaning source
6264: FROM gmd_sample_spec_disp b,
6265: gmd_event_spec_disp c,
6266: gmd_samples s,
6267: fnd_lookup_values_vl d,
6268: fnd_lookup_values_vl e
6269: WHERE b.sample_id = p_sample.sample_id
6278: SELECT b.disposition,
6279: d.meaning sample_disposition,
6280: e.meaning source
6281: FROM gmd_sample_spec_disp b,
6282: gmd_event_spec_disp c,
6283: gmd_samples s,
6284: fnd_lookup_values d,
6285: fnd_lookup_values e
6286: WHERE b.sample_id =p_sample.sample_id