[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_disp_rec IN GMD_SAMPLES_GRP.update_disp_rec
,p_to_disposition IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
);
SELECT event_spec_disp_id
FROM gmd_samples gs, gmd_event_spec_disp ge
WHERE gs.sampling_event_id = ge.sampling_event_id
AND ge.spec_used_for_lot_attrib_ind = 'Y'
AND gs.sample_id = p_id
AND ge.delete_mark = 0
;
SELECT DISTINCT event_spec_disp_id
FROM gmd_samples gs, gmd_event_spec_disp ge
WHERE gs.sampling_event_id = ge.sampling_event_id
AND gs.sampling_event_id = p_id
AND ge.spec_used_for_lot_attrib_ind = 'Y'
AND ge.delete_mark = 0
;
SELECT 1
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND parent_lot_number = p_parent_lot_number;
SELECT 1
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND parent_lot_number = p_parent_lot_number
AND lot_number = p_lot_number;
SELECT distinct wlpn.lpn_id, wlpn.license_plate_number
FROM wms_license_plate_numbers wlpn,
wms_lpn_contents wlc
WHERE wlpn.lpn_id = wlc.parent_lpn_id
AND wlpn.organization_id = p_organization_id
AND wlpn.parent_lpn_id is null
AND wlc.inventory_item_id = p_inventory_item_id
AND (wlc.lot_number = p_lot_number or p_lot_number is null)
AND wlpn.lpn_id = x_lpn_id;
SELECT distinct wlpn.lpn_id, wlpn.license_plate_number
FROM wms_license_plate_numbers wlpn,
wms_lpn_contents wlc
WHERE wlpn.lpn_id = wlc.parent_lpn_id
AND wlpn.organization_id = p_organization_id
AND wlpn.parent_lpn_id is null
AND wlc.inventory_item_id = p_inventory_item_id
AND (wlc.lot_number = p_lot_number or p_lot_number is null)
AND wlpn.license_plate_number = x_lpn;
SELECT reason_id
FROM mtl_transaction_reasons
WHERE NVL(disable_date, SYSDATE) >= SYSDATE
AND reason_name = p_reason_code;
SELECT creation_date, hold_date
FROM mtl_lot_numbers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number
AND ((p_parent_lot_number IS NULL)
OR (parent_lot_number = p_parent_lot_number));
SELECT max(creation_date)
INTO x_lot_created
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT max(hold_date)
INTO x_hold_date
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT status_id, status_code
FROM mtl_material_statuses
WHERE status_code = p_status
AND enabled_flag = 1;
SELECT grade_code
FROM mtl_grades
WHERE grade_code = p_status
AND disable_flag = 'N';
p_update_disp_rec IN GMD_SAMPLES_GRP.update_disp_rec
,p_to_disposition IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_msg_count NUMBER :=0;
SELECT count(1)
FROM gmd_event_spec_disp esd,
gmd_results r,
gmd_spec_results sr,
gmd_spec_tests_b st
WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
AND esd.event_spec_disp_id = sr.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_update_disp_rec.sample_id
AND (sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('0A', '4C', '5O'))
AND r.delete_mark = 0
AND sr.delete_mark = 0
AND esd.spec_id = st.spec_id
AND st.test_id = r.test_id
AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR (l_include_optional = 'Y'));
SELECT count(1)
FROM gmd_results r, gmd_spec_results sr
WHERE sr.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_update_disp_rec.sample_id
AND sr.additional_test_ind = 'Y'
AND (sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('0A', '4C', '5O'))
AND r.delete_mark = 0
AND sr.delete_mark = 0;
SELECT COUNT(1)
FROM gmd_composite_results
WHERE composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
AND delete_mark = 0 ;
SELECT count(1)
FROM gmd_event_spec_disp esd, gmd_composite_spec_disp csd,
gmd_composite_results cr, gmd_spec_tests_b st
WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
AND csd.event_spec_disp_id = esd.event_spec_disp_id
AND csd.latest_ind = 'Y'
AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
AND cr.in_spec_ind IS NULL
AND st.spec_id = esd.spec_id
AND st.test_id = cr.test_id
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)));
SELECT count(1)
FROM gmd_event_spec_disp esd, gmd_composite_spec_disp csd,
gmd_composite_results cr
WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
AND csd.event_spec_disp_id = esd.event_spec_disp_id
AND csd.latest_ind = 'Y'
AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
AND cr.in_spec_ind IS NULL
AND cr.test_id NOT IN (SELECT st.test_id FROM gmd_spec_tests_b st WHERE st.spec_id = esd.spec_id);
SELECT count(1)
FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
gmd_spec_tests_b st
WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
AND esd.event_spec_disp_id = sr.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_update_disp_rec.sample_id
AND ((sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('2R')) OR sr.in_spec_ind = 'Y' )
AND r.delete_mark = 0
AND sr.delete_mark = 0
AND esd.spec_id = st.spec_id
AND st.test_id = r.test_id ;
SELECT count(1)
FROM gmd_results r, gmd_spec_results sr
WHERE sr.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_update_disp_rec.sample_id
AND sr.additional_test_ind = 'Y'
AND (sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('2R'))
AND r.delete_mark = 0
AND sr.delete_mark = 0;
SELECT count(1)
FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
gmd_spec_tests_b st
WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
AND esd.event_spec_disp_id = sr.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_update_disp_rec.sample_id
AND (sr.in_spec_ind IS NULL AND (sr.evaluation_ind IS NULL))
AND r.delete_mark = 0
AND sr.delete_mark = 0
AND esd.spec_id = st.spec_id
AND st.test_id = r.test_id
AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR (l_include_optional = 'Y'));
SELECT count(1)
FROM gmd_results r, gmd_spec_results sr
WHERE sr.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_update_disp_rec.sample_id
AND sr.additional_test_ind = 'Y'
AND (sr.evaluation_ind IS NULL)
AND r.delete_mark = 0
AND sr.delete_mark = 0;
SELECT organization_id
FROM gmd_samples
WHERE sample_id = p_update_disp_rec.sample_id;
SELECT organization_id
FROM gmd_sampling_events gse,
gmd_event_spec_disp gesd,
gmd_composite_spec_disp gcsd
WHERE gse.sampling_event_id = gesd.sampling_event_id
AND gesd.event_spec_disp_id = gcsd.event_spec_disp_id
AND gcsd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id;
IF p_update_disp_rec.sample_id IS NOT NULL THEN
OPEN c_get_smpl_org_id;
IF (p_update_disp_rec.sample_id IS NOT NULL AND p_update_disp_rec.composite_spec_disp_id IS NOT NULL)
OR (p_update_disp_rec.sample_id IS NULL AND p_update_disp_rec.composite_spec_disp_id IS NULL)
THEN
RAISE INVALID_PARAMETERS; --'GMD_INVALID_PARAMETERS'
IF p_update_disp_rec.sample_id IS NOT NULL THEN
OPEN c_non_accept_w_spec ;
IF p_update_disp_rec.curr_disposition = '1P' THEN
IF p_to_disposition NOT IN ('7CN') THEN
RAISE INVALID_DISPOSITION ;
ELSIF p_update_disp_rec.curr_disposition = '2I' THEN
IF l_reject_w_spec_no_av + l_reject_wo_spec_no_av > 0 THEN
IF p_to_disposition NOT IN ('7CN','6RJ') THEN
RAISE INVALID_DISPOSITION ;
ELSIF p_update_disp_rec.curr_disposition = '3C' THEN
IF l_non_accept_w_spec + l_non_accept_wo_spec = 0 THEN
IF p_to_disposition NOT IN ('4A','6RJ') THEN
RAISE INVALID_DISPOSITION ;
ELSIF p_update_disp_rec.curr_disposition = '4A' AND p_update_disp_rec.no_of_samples_for_event = 1 THEN
IF p_to_disposition NOT IN ('7CN','6RJ') THEN
RAISE INVALID_DISPOSITION ;
ELSIF p_update_disp_rec.curr_disposition = '5AV' AND p_update_disp_rec.no_of_samples_for_event = 1 THEN
IF p_to_disposition NOT IN ('7CN','6RJ') THEN
RAISE INVALID_DISPOSITION ;
ELSIF p_update_disp_rec.curr_disposition = '6RJ' AND p_update_disp_rec.no_of_samples_for_event = 1 THEN
IF p_to_disposition NOT IN ('5AV','7CN') THEN
RAISE INVALID_DISPOSITION ;
ELSIF p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
OPEN cr_check_comp_results_in_spec ;
IF NVL(l_test_count,0) = 0 AND p_update_disp_rec.curr_disposition = '3C' THEN
RAISE NO_TESTS_CMP;
IF p_update_disp_rec.curr_disposition = '3C' THEN
IF l_non_accept_w_spec + l_non_accept_wo_spec = 0 THEN
IF p_to_disposition NOT IN ('4A','6RJ') THEN
RAISE INVALID_DISPOSITION;
ELSIF p_update_disp_rec.curr_disposition = '2I' THEN
IF p_to_disposition NOT IN ('5AV','6RJ') THEN
RAISE INVALID_DISPOSITION;
END IF; -- p_update_disp_rec.sample_id IS NOT NULL
l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
l_change_rec GMD_SAMPLES_GRP.update_change_disp_rec;
SELECT sample_req_cnt, sample_active_cnt, sample_taken_cnt
FROM gmd_sampling_events
WHERE sampling_event_id = p_change_disp_rec.sampling_event_id;
SELECT sample_id
FROM gmd_samples
WHERE sampling_event_id = p_change_disp_rec.sampling_event_id;
SELECT composite_spec_disp_id
FROM gmd_composite_spec_disp
WHERE event_spec_disp_id = l_event_spec_disp_id;
SELECT control_lot_attrib_ind,
in_spec_lot_status_id,
out_of_spec_lot_status_id,
null
FROM gmd_inventory_spec_vrs
WHERE spec_vr_id = l_spec_vr_id
UNION ALL
SELECT control_lot_attrib_ind,
in_spec_lot_status_id,
out_of_spec_lot_status_id,
control_batch_step_ind
FROM gmd_wip_spec_vrs
WHERE spec_vr_id = l_spec_vr_id
UNION ALL
SELECT control_lot_attrib_ind,
in_spec_lot_status_id,
out_of_spec_lot_status_id,
null
FROM gmd_supplier_spec_vrs
WHERE spec_vr_id = l_spec_vr_id ;
SELECT delayed_lot_entry
FROM GMD_INVENTORY_SPEC_VRS
WHERE spec_vr_id = l_spec_vr_id
UNION ALL
SELECT delayed_lot_entry
FROM GMD_WIP_SPEC_VRS
WHERE spec_vr_id = l_spec_vr_id
UNION ALL
SELECT delayed_lot_entry
FROM GMD_SUPPLIER_SPEC_VRS
WHERE spec_vr_id = l_spec_vr_id ;
SELECT delayed_lpn_entry
FROM GMD_INVENTORY_SPEC_VRS
WHERE spec_vr_id = l_spec_vr_id
UNION ALL
SELECT delayed_lpn_entry
FROM GMD_WIP_SPEC_VRS
WHERE spec_vr_id = l_spec_vr_id
UNION ALL
SELECT delayed_lpn_entry
FROM GMD_SUPPLIER_SPEC_VRS
WHERE spec_vr_id = l_spec_vr_id ;
l_update_disp_rec.sample_id := p_change_disp_rec.sample_id;
l_update_disp_rec.sampling_event_id := p_change_disp_rec.sampling_event_id;
IF l_update_disp_rec.sample_id IS NOT NULL THEN
l_update_disp_rec.event_spec_disp_id := get_current_event_spec_disp_id(l_update_disp_rec.sample_id,'Y');
l_update_disp_rec.event_spec_disp_id := get_current_event_spec_disp_id(l_update_disp_rec.sampling_event_id,'N');
IF l_update_disp_rec.event_spec_disp_id IS NOT NULL THEN
OPEN composite_spec_disp(l_update_disp_rec.event_spec_disp_id);
FETCH composite_spec_disp INTO l_update_disp_rec.composite_spec_disp_id;
l_update_disp_rec.called_from_results :='N';
l_update_disp_rec.no_of_samples_for_event := 1;
IF l_update_disp_rec.sample_id IS NULL THEN
OPEN get_sample_id;
FETCH get_sample_id INTO l_update_disp_rec.sample_id;
l_update_disp_rec.no_of_samples_for_event := 2;
IF l_update_disp_rec.sample_id IS NOT NULL THEN
SELECT a.organization_id,b.lot_control_code,b.grade_control_flag,b.child_lot_flag,b.lot_status_enabled,a.sample_type,a.source,
a.inventory_item_id,a.parent_lot_number,a.lot_number,a.lpn_id,a.subinventory,a.locator_id,c.concatenated_segments,a.lpn_id,d.license_plate_number,e.wms_enabled_flag
INTO l_organization_id,l_lot_control_code,l_grade_control_flag,l_child_lot_flag,l_lot_status_enabled,l_sample_type,l_source,
l_inventory_item_id,l_parent_lot_number,l_lot_number,l_lpn_id,l_subinventory,l_locator_id,l_locator,l_lpn_id,l_lpn,l_wms_enabled_flag
FROM gmd_samples a,
mtl_system_items_b b,
mtl_item_locations_kfv c,
wms_license_plate_numbers d,
mtl_parameters e
WHERE a.sample_id = l_update_disp_rec.sample_id
AND a.delete_mark = 0
AND a.organization_id = b.organization_id(+)
AND a.inventory_item_id = b.inventory_item_id(+)
AND a.organization_id = c.organization_id(+)
AND a.locator_id = c.inventory_location_id(+)
AND a.lpn_id = d.lpn_id(+)
AND a.organization_id = e.organization_id;
SELECT a.DISPOSITION,
b.EVENT_SPEC_DISP_ID,
b.spec_id,b.spec_vr_id,
c.spec_name,
c.spec_vers
INTO l_update_disp_rec.curr_disposition,l_update_disp_rec.event_spec_disp_id,
l_spec_id,l_spec_vr_id,
l_spec_name,l_spec_vers
FROM gmd_sample_spec_disp a,
gmd_event_spec_disp b,
gmd_specifications_b c
WHERE a.sample_id = l_update_disp_rec.sample_id
AND a.event_spec_disp_id = b.event_spec_disp_id
AND a.delete_mark = 0
AND b.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND b.delete_mark = 0
AND b.spec_id = c.spec_id(+);
IF (l_update_disp_rec.no_of_samples_for_event = 1)
AND (l_sample_type <> 'M') AND (l_source <> 'T') --Not a Monitoring Sample and Stability Study Sample
AND (l_spec_vr_id IS NOT NULL)
AND (l_lot_control_code = 2) -- Lot controlled item
AND (l_lot_number IS NULL)
AND (l_update_disp_rec.curr_disposition = '3C') THEN
OPEN cr_get_delayed_lot_entry;
UPDATE GMD_SAMPLES
SET parent_lot_number = p_change_disp_rec.parent_lot_number,
lot_number = p_change_disp_rec.lot_number
WHERE sample_id = l_update_disp_rec.sample_id ;
UPDATE GMD_SAMPLING_EVENTS
SET parent_lot_number = p_change_disp_rec.parent_lot_number,
lot_number = p_change_disp_rec.lot_number
WHERE sampling_event_id = (SELECT sampling_event_id FROM gmd_samples
WHERE sample_id = l_update_disp_rec.sample_id );
IF (l_update_disp_rec.no_of_samples_for_event = 1)
AND (l_sample_type <> 'M') AND (l_source <> 'T') --Not a Monitoring Sample and Stability Study Sample
AND (l_spec_vr_id IS NOT NULL)
AND (l_wms_enabled_flag = 'Y') -- Org is WMS Enabled
AND (l_lpn_id IS NULL)
AND (l_update_disp_rec.curr_disposition = '3C') THEN
OPEN cr_get_delayed_lpn_entry;
UPDATE GMD_SAMPLES
SET lpn_id = l_lpn_id
WHERE sample_id = l_update_disp_rec.sample_id ;
UPDATE GMD_SAMPLING_EVENTS
SET lpn_id = l_lpn_id
WHERE sampling_event_id = (SELECT sampling_event_id FROM gmd_samples
WHERE sample_id = l_update_disp_rec.sample_id );
ELSIF l_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
SELECT gse.organization_id,gse.inventory_item_id,gse.parent_lot_number, gse.lot_number,msi.grade_control_flag,msi.lot_control_code,msi.child_lot_flag,msi.lot_status_enabled,
gse.creation_date,gse.subinventory,gse.locator_id,mil.concatenated_segments,gse.lpn_id,wlpn.license_plate_number,mp.wms_enabled_flag,
NVL(gse.sample_type, 'I'),nvl(gse.source,'I'),esd.spec_id,esd.spec_vr_id,csd.disposition,gsb.spec_name,gsb.spec_vers
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,
l_sampling_event_date,l_subinventory,l_locator_id,l_locator,l_lpn_id,l_lpn,l_wms_enabled_flag,
l_sample_type,l_source,l_spec_id,l_spec_vr_id,l_update_disp_rec.curr_disposition,l_spec_name,l_spec_vers
FROM gmd_composite_spec_disp csd,
gmd_event_spec_disp esd ,
gmd_sampling_events gse,
mtl_system_items_b msi,
gmd_specifications_b gsb,
mtl_item_locations_kfv mil,
wms_license_plate_numbers wlpn,
mtl_parameters mp
WHERE csd.composite_spec_disp_id = l_update_disp_rec.composite_spec_disp_id
AND csd.event_spec_disp_id = esd.event_spec_disp_id
AND esd.sampling_event_id = gse.sampling_event_id
AND gse.organization_id = msi.organization_id(+)
AND gse.inventory_item_id = msi.inventory_item_id(+)
AND esd.spec_id = gsb.spec_id(+)
AND gse.organization_id = mil.organization_id(+)
AND gse.locator_id = mil.inventory_location_id(+)
AND gse.lpn_id = wlpn.lpn_id(+)
AND gse.organization_id = mp.organization_id;
AND (l_update_disp_rec.curr_disposition = '3C') THEN
OPEN cr_get_delayed_lot_entry;
UPDATE GMD_SAMPLES
SET parent_lot_number = p_change_disp_rec.parent_lot_number,
lot_number = p_change_disp_rec.lot_number
WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
UPDATE GMD_SAMPLING_EVENTS
SET parent_lot_number = p_change_disp_rec.parent_lot_number,
lot_number = p_change_disp_rec.lot_number
WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
AND (l_update_disp_rec.curr_disposition = '3C') THEN
OPEN cr_get_delayed_lpn_entry;
UPDATE GMD_SAMPLES
SET lpn_id = l_lpn_id
WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
UPDATE GMD_SAMPLING_EVENTS
SET lpn_id = l_lpn_id
WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
IF l_update_disp_rec.curr_disposition IN ('0PL','1P','7CN','0RT') THEN
RAISE INVALID_DISPOSITION;
ELSIF l_update_disp_rec.curr_disposition IN ('4A','5AV','6RJ')
AND l_update_disp_rec.sample_id IS NOT NULL THEN
RAISE INVALID_DISPOSITION; --Already in Final Disposition. No target disp available.
IF (l_update_disp_rec.no_of_samples_for_event = 1 OR l_update_disp_rec.composite_spec_disp_id IS NOT NULL) THEN
IF p_change_disp_rec.to_disposition IN ('4A','5AV','6RJ') THEN
IF l_spec_vr_id IS NOT NULL THEN
OPEN cr_get_lot_status;
SELECT grade_code, status_id
INTO l_from_grade_code, l_from_lot_status_id
FROM mtl_lot_numbers
WHERE ((lot_number = l_lot_number) OR (l_lot_number IS NULL))
AND ((parent_lot_number = l_parent_lot_number) OR (l_parent_lot_number IS NULL))
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id;
SELECT status_id INTO l_to_lot_status_id
FROM mtl_material_statuses
WHERE status_code = p_change_disp_rec.to_lot_status
AND enabled_flag = 1;
SELECT grade_code INTO l_to_grade_code
FROM GMD_SPECIFICATIONS_B
WHERE spec_id = l_spec_id ;
SELECT transaction_reason_id INTO l_reason_id
FROM GMD_QUALITY_CONFIG
WHERE organization_id = l_organization_id ;
END IF; -- l_update_disp_rec.no_of_samples_for_event = 1
p_update_disp_rec => l_update_disp_rec
,p_to_disposition => p_change_disp_rec.to_disposition
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_message_data
);
GMD_DISP_GRP.update_sample_comp_disp
( p_update_disp_rec => l_update_disp_rec
, p_to_disposition => p_change_disp_rec.to_disposition
, x_return_status => l_return_status
, x_message_data => l_message_data );
Gmd_Disp_Grp.update_lot_grade_batch (
p_sample_id => l_update_disp_rec.sample_id
, p_composite_spec_disp_id => l_update_disp_rec.composite_spec_disp_id
, p_to_lot_status_id => l_to_lot_status_id
, p_from_lot_status_id => l_from_lot_status_id
, p_to_grade_code => NVL(p_change_disp_rec.to_grade_code,l_to_grade_code)
, p_from_grade_code => l_from_grade_code
, p_to_qc_status => l_to_qc_status
, p_reason_id => l_reason_id
, p_hold_date => l_hold_date
, x_return_status => l_return_status
, x_message_data => l_message_data );