[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
-- gmd_specifications_b s,
-- gmd_inventory_spec_vrs ivr,
gmd_event_spec_disp esd
-- WHERE s.spec_id = ivr.spec_id
-- AND ivr.spec_vr_id = esd.spec_vr_id
-- AND esd.sampling_event_id = se.sampling_event_id
WHERE esd.sampling_event_id = se.sampling_event_id
AND ( (esd.spec_vr_id = p_spec_vr_id) OR
(esd.spec_vr_id is null and p_spec_vr_id is null ))
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.source = p_sample.source
AND se.organization_id = p_sample.organization_id
-- AND s.item_id = p_sample.inventory_item_id
AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
(se.lot_number = p_sample.lot_number)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND ((se.lot_retest_ind IS NULL AND p_sample.lot_retest_ind IS NULL) OR
(se.lot_retest_ind = p_sample.lot_retest_ind)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_event_spec_disp esd
WHERE esd.sampling_event_id = se.sampling_event_id
AND ( (esd.spec_vr_id = p_spec_vr_id) OR
(esd.spec_vr_id is null and p_spec_vr_id is null ))
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.source = p_sample.source
AND se.organization_id = p_sample.organization_id
AND ((se.batch_id is NULL AND p_sample.batch_id is NULL) OR
(se.batch_id = p_sample.batch_id)
)
AND ((se.recipe_id is NULL AND p_sample.recipe_id is NULL) OR
(se.recipe_id = p_sample.recipe_id)
)
AND ((se.formula_id is NULL AND p_sample.formula_id is NULL) OR
(se.formula_id = p_sample.formula_id)
)
AND ((se.formulaline_id is NULL AND p_sample.formulaline_id is NULL) OR
(se.formulaline_id = p_sample.formulaline_id
AND p_sample.batch_id IS NULL)
)
AND ((se.material_detail_id is NULL AND p_sample.material_detail_id is NULL) OR
(se.material_detail_id = p_sample.material_detail_id)
)
AND ((se.routing_id is NULL AND p_sample.routing_id is NULL) OR
(se.routing_id = p_sample.routing_id)
)
AND ((se.step_id is NULL AND p_sample.step_id is NULL) OR
(se.step_id = p_sample.step_id)
)
AND ((se.oprn_id is NULL AND p_sample.oprn_id is NULL) OR
(se.oprn_id = p_sample.oprn_id)
)
AND ((se.charge is NULL AND p_sample.charge is NULL) OR
(se.charge = p_sample.charge)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
-- AND s.delete_mark = 0 -- Spec is still active
-- AND ((s.spec_status between 400 and 499) OR
-- (s.spec_status between 700 and 799) OR
-- (s.spec_status between 900 and 999)
-- )
-- AND wvr.delete_mark = 0 -- Validity rule is still active
-- AND ((wvr.spec_vr_status between 400 and 499) OR
-- (wvr.spec_vr_status between 700 and 799) OR
-- (wvr.spec_vr_status between 900 and 999)
-- )
-- AND wvr.start_date <= SYSDATE
-- AND (wvr.end_date is NULL OR wvr.end_date >= SYSDATE)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot id
(se.lot_number = p_sample.lot_number)
)
AND ((se.subinventory IS NULL AND p_sample.source_subinventory IS NULL) OR --Bug# 3736716. Added Source warehouse
(se.subinventory = p_sample.source_subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.source_locator_id IS NULL) OR --Bug# 3736716. Added Source Location
(se.locator_id = p_sample.source_locator_id)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
-- gmd_specifications_b s,
-- gmd_customer_spec_vrs cvr,
gmd_event_spec_disp esd
-- WHERE s.spec_id = cvr.spec_id
-- AND cvr.spec_vr_id = esd.spec_vr_id
WHERE esd.sampling_event_id = se.sampling_event_id
AND ( (esd.spec_vr_id = p_spec_vr_id) OR
(esd.spec_vr_id is null and p_spec_vr_id is null ))
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.source = p_sample.source
AND se.organization_id = p_sample.organization_id
AND ((se.cust_id is NULL AND p_sample.cust_id is NULL) OR
(se.cust_id = p_sample.cust_id)
)
AND ((se.org_id is NULL AND p_sample.org_id is NULL) OR
(se.org_id = p_sample.org_id)
)
AND ((se.order_id is NULL AND p_sample.order_id is NULL) OR
(se.order_id = p_sample.order_id)
)
AND ((se.order_line_id is NULL AND p_sample.order_line_id is NULL) OR
(se.order_line_id = p_sample.order_line_id)
)
AND ((se.ship_to_site_id is NULL AND p_sample.ship_to_site_id is NULL) OR
(se.ship_to_site_id = p_sample.ship_to_site_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
-- AND s.delete_mark = 0 -- Spec is still active
-- AND ((s.spec_status between 400 and 499) OR
-- (s.spec_status between 700 and 799) OR
-- (s.spec_status between 900 and 999)
-- )
-- AND cvr.delete_mark = 0 -- Validity rule is still active
-- AND ((cvr.spec_vr_status between 400 and 499) OR
-- (cvr.spec_vr_status between 700 and 799) OR
-- (cvr.spec_vr_status between 900 and 999)
-- )
-- AND cvr.start_date <= SYSDATE
-- AND (cvr.end_date is NULL OR cvr.end_date >= SYSDATE)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot id
(se.lot_number = p_sample.lot_number)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
-- gmd_specifications_b s,
-- gmd_supplier_spec_vrs svr,
gmd_event_spec_disp esd
-- WHERE s.spec_id = svr.spec_id
-- AND svr.spec_vr_id = esd.spec_vr_id
WHERE esd.sampling_event_id = se.sampling_event_id
AND ( (esd.spec_vr_id = p_spec_vr_id) OR
(esd.spec_vr_id is null and p_spec_vr_id is null ))
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.source = p_sample.source
AND se.organization_id = p_sample.organization_id
AND ((se.supplier_id is NULL AND p_sample.supplier_id is NULL) OR
(se.supplier_id = p_sample.supplier_id)
)
AND ((se.supplier_site_id is NULL AND p_sample.supplier_site_id is NULL) OR
(se.supplier_site_id = p_sample.supplier_site_id)
)
AND ((se.po_header_id is NULL AND p_sample.po_header_id is NULL) OR
(se.po_header_id = p_sample.po_header_id)
)
AND ((se.po_line_id is NULL AND p_sample.po_line_id is NULL) OR
(se.po_line_id = p_sample.po_line_id)
)
AND ((se. subinventory is NULL AND p_sample.subinventory is NULL) OR
(se. subinventory = p_sample. subinventory)
)
AND ((se. locator_id is NULL AND p_sample.locator_id is NULL) OR
(se. locator_id = p_sample. locator_id)
)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
(se.lot_number = p_sample.lot_number)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
/* AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND svr.delete_mark = 0 -- Validity rule is still active
AND ((svr.spec_vr_status between 400 and 499) OR
(svr.spec_vr_status between 700 and 799) OR
(svr.spec_vr_status between 900 and 999)
)
AND svr.start_date <= SYSDATE
AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
*/
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
-- gmd_specifications_b s,
-- gmd_monitoring_spec_vrs svr,
gmd_event_spec_disp esd
-- WHERE s.spec_id = svr.spec_id
-- AND svr.spec_vr_id = esd.spec_vr_id
WHERE esd.sampling_event_id = se.sampling_event_id
AND ( (esd.spec_vr_id = p_spec_vr_id) OR
(esd.spec_vr_id is null and p_spec_vr_id is null ))
AND ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
(se.organization_id = p_sample.organization_id)
)
AND ((se.resources IS NULL and p_sample.resources IS NULL) OR
( (se.resources = p_sample.resources) AND
((se.instance_id IS NULL AND p_sample.instance_id IS NULL) OR
(se.instance_id = p_sample.instance_id) ) )
)
AND se.source = p_sample.source
AND se.disposition IN ('1P', '2I') -- Pending or In Process
/* AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND svr.delete_mark = 0 -- Validity rule is still active
AND ((svr.spec_vr_status between 400 and 499) OR
(svr.spec_vr_status between 700 and 799) OR
(svr.spec_vr_status between 900 and 999)
)
AND svr.start_date <= SYSDATE
AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
*/
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
-- gmd_specifications_b s,
-- gmd_monitoring_spec_vrs svr,
gmd_event_spec_disp esd
-- WHERE s.spec_id = svr.spec_id
-- AND svr.spec_vr_id = esd.spec_vr_id
WHERE esd.sampling_event_id = se.sampling_event_id
AND se.source = p_sample.source
AND ( (esd.spec_vr_id = p_spec_vr_id) OR
(esd.spec_vr_id is null and p_spec_vr_id is null ))
AND ((se.organization_id is NULL) OR
(se.organization_id = p_sample.organization_id )
)
AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
-- AND s.delete_mark = 0 -- Spec is still active
-- AND ((s.spec_status between 400 and 499) OR
-- (s.spec_status between 700 and 799) OR
-- (s.spec_status between 900 and 999)
-- )
-- AND svr.delete_mark = 0 -- Validity rule is still active
-- AND ((svr.spec_vr_status between 400 and 499) OR
-- (svr.spec_vr_status between 700 and 799) OR
-- (svr.spec_vr_status between 900 and 999)
-- )
-- AND svr.start_date <= SYSDATE
-- AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_specifications_b s,
gmd_inventory_spec_vrs ivr,
gmd_event_spec_disp esd
WHERE s.spec_id = ivr.spec_id
AND ivr.spec_vr_id = esd.spec_vr_id
AND esd.sampling_event_id = se.sampling_event_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.organization_id = p_sample.organization_id
AND s.inventory_item_id = p_sample.inventory_item_id
AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
(se.lot_number = p_sample.lot_number)
)
AND ((se.lot_retest_ind IS NULL AND p_sample.lot_retest_ind IS NULL) OR
(se.lot_retest_ind = p_sample.lot_retest_ind)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND ivr.delete_mark = 0 -- Validity rule is still active
AND ((ivr.spec_vr_status between 400 and 499) OR
(ivr.spec_vr_status between 700 and 799) OR
(ivr.spec_vr_status between 900 and 999)
)
AND ivr.start_date <= SYSDATE
AND (ivr.end_date is NULL OR ivr.end_date >= SYSDATE)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_specifications_b s,
gmd_wip_spec_vrs wvr,
gmd_event_spec_disp esd
WHERE s.spec_id = wvr.spec_id
AND wvr.spec_vr_id = esd.spec_vr_id
AND esd.sampling_event_id = se.sampling_event_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.organization_id = p_sample.organization_id
AND s.inventory_item_id = p_sample.inventory_item_id
AND ((se.batch_id is NULL AND p_sample.batch_id is NULL) OR
(se.batch_id = p_sample.batch_id)
)
AND ((se.recipe_id is NULL AND p_sample.recipe_id is NULL) OR
(se.recipe_id = p_sample.recipe_id)
)
AND ((se.formula_id is NULL AND p_sample.formula_id is NULL) OR
(se.formula_id = p_sample.formula_id)
)
AND ((se.formulaline_id is NULL AND p_sample.formulaline_id is NULL) OR
(se.formulaline_id = p_sample.formulaline_id
AND p_sample.batch_id IS NULL)
)
AND ((se.material_detail_id is NULL AND p_sample.material_detail_id is NULL) OR
(se.material_detail_id = p_sample.material_detail_id)
)
AND ((se.routing_id is NULL AND p_sample.routing_id is NULL) OR
(se.routing_id = p_sample.routing_id)
)
AND ((se.step_id is NULL AND p_sample.step_id is NULL) OR
(se.step_id = p_sample.step_id)
)
AND ((se.oprn_id is NULL AND p_sample.oprn_id is NULL) OR
(se.oprn_id = p_sample.oprn_id)
)
AND ((se.charge is NULL AND p_sample.charge is NULL) OR
(se.charge = p_sample.charge)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND wvr.delete_mark = 0 -- Validity rule is still active
AND ((wvr.spec_vr_status between 400 and 499) OR
(wvr.spec_vr_status between 700 and 799) OR
(wvr.spec_vr_status between 900 and 999)
)
AND wvr.start_date <= SYSDATE
AND (wvr.end_date is NULL OR wvr.end_date >= SYSDATE)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot no.
(se.lot_number = p_sample.lot_number)
)
AND ((se.subinventory IS NULL AND p_sample.source_subinventory IS NULL) OR --Bug# 3736716. Added Source warehouse
(se.subinventory = p_sample.source_subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.source_locator_id IS NULL) OR --Bug# 3736716. Added Source Location
(se.locator_id = p_sample.source_locator_id)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_specifications_b s,
gmd_customer_spec_vrs cvr,
gmd_event_spec_disp esd
WHERE s.spec_id = cvr.spec_id
AND cvr.spec_vr_id = esd.spec_vr_id
AND esd.sampling_event_id = se.sampling_event_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.organization_id = p_sample.organization_id
AND s.inventory_item_id = p_sample.inventory_item_id
AND ((se.cust_id is NULL AND p_sample.cust_id is NULL) OR
(se.cust_id = p_sample.cust_id)
)
AND ((se.org_id is NULL AND p_sample.org_id is NULL) OR
(se.org_id = p_sample.org_id)
)
AND ((se.order_id is NULL AND p_sample.order_id is NULL) OR
(se.order_id = p_sample.order_id)
)
AND ((se.order_line_id is NULL AND p_sample.order_line_id is NULL) OR
(se.order_line_id = p_sample.order_line_id)
)
AND ((se.ship_to_site_id is NULL AND p_sample.ship_to_site_id is NULL) OR
(se.ship_to_site_id = p_sample.ship_to_site_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND cvr.delete_mark = 0 -- Validity rule is still active
AND ((cvr.spec_vr_status between 400 and 499) OR
(cvr.spec_vr_status between 700 and 799) OR
(cvr.spec_vr_status between 900 and 999)
)
AND cvr.start_date <= SYSDATE
AND (cvr.end_date is NULL OR cvr.end_date >= SYSDATE)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot no.
(se.lot_number = p_sample.lot_number)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_specifications_b s,
gmd_supplier_spec_vrs svr,
gmd_event_spec_disp esd
WHERE s.spec_id = svr.spec_id
AND svr.spec_vr_id = esd.spec_vr_id
AND esd.sampling_event_id = se.sampling_event_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND se.organization_id = p_sample.organization_id
AND s.inventory_item_id = p_sample.inventory_item_id
AND ((se.supplier_id is NULL AND p_sample.supplier_id is NULL) OR
(se.supplier_id = p_sample.supplier_id)
)
AND ((se.supplier_site_id is NULL AND p_sample.supplier_site_id is NULL) OR
(se.supplier_site_id = p_sample.supplier_site_id)
)
AND ((se.po_header_id is NULL AND p_sample.po_header_id is NULL) OR
(se.po_header_id = p_sample.po_header_id)
)
AND ((se.po_line_id is NULL AND p_sample.po_line_id is NULL) OR
(se.po_line_id = p_sample.po_line_id)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND svr.delete_mark = 0 -- Validity rule is still active
AND ((svr.spec_vr_status between 400 and 499) OR
(svr.spec_vr_status between 700 and 799) OR
(svr.spec_vr_status between 900 and 999)
)
AND svr.start_date <= SYSDATE
AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_specifications_b s,
gmd_monitoring_spec_vrs svr,
gmd_event_spec_disp esd
WHERE s.spec_id = svr.spec_id
AND svr.spec_vr_id = esd.spec_vr_id
AND esd.sampling_event_id = se.sampling_event_id
AND ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
(se.organization_id = p_sample.organization_id)
)
AND ((se.resources IS NULL and p_sample.resources IS NULL) OR
( (se.resources = p_sample.resources) AND
((se.instance_id IS NULL AND p_sample.instance_id IS NULL) OR
(se.instance_id = p_sample.instance_id) ) )
)
AND se.source = p_sample.source
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND svr.delete_mark = 0 -- Validity rule is still active
AND ((svr.spec_vr_status between 400 and 499) OR
(svr.spec_vr_status between 700 and 799) OR
(svr.spec_vr_status between 900 and 999)
)
AND svr.start_date <= SYSDATE
AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se,
gmd_specifications_b s,
gmd_monitoring_spec_vrs svr,
gmd_event_spec_disp esd
WHERE s.spec_id = svr.spec_id
AND svr.spec_vr_id = esd.spec_vr_id
AND esd.sampling_event_id = se.sampling_event_id
AND se.source = p_sample.source
AND ((se.organization_id is NULL) OR
(se.organization_id = p_sample.organization_id )
)
AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND s.delete_mark = 0 -- Spec is still active
AND ((s.spec_status between 400 and 499) OR
(s.spec_status between 700 and 799) OR
(s.spec_status between 900 and 999)
)
AND svr.delete_mark = 0 -- Validity rule is still active
AND ((svr.spec_vr_status between 400 and 499) OR
(svr.spec_vr_status between 700 and 799) OR
(svr.spec_vr_status between 900 and 999)
)
AND svr.start_date <= SYSDATE
AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se
WHERE se.inventory_item_id = p_sample.inventory_item_id
AND se.organization_id = p_sample.organization_id
AND se.original_spec_vr_id IS NULL
AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
(se.lot_number = p_sample.lot_number)
)
AND ((se.lot_retest_ind IS NULL AND p_sample.lot_retest_ind IS NULL) OR
(se.lot_retest_ind = p_sample.lot_retest_ind)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se
WHERE se.inventory_item_id = p_sample.inventory_item_id
AND se.organization_id = p_sample.organization_id
AND se.original_spec_vr_id IS NULL
AND ((se.batch_id is NULL AND p_sample.batch_id is NULL) OR
(se.batch_id = p_sample.batch_id)
)
AND ((se.recipe_id is NULL AND p_sample.recipe_id is NULL) OR
(se.recipe_id = p_sample.recipe_id)
)
AND ((se.formula_id is NULL AND p_sample.formula_id is NULL) OR
(se.formula_id = p_sample.formula_id)
)
AND ((se.formulaline_id is NULL AND p_sample.formulaline_id is NULL) OR
(se.formulaline_id = p_sample.formulaline_id
AND p_sample.batch_id IS NULL)
)
AND ((se.material_detail_id is NULL AND p_sample.material_detail_id is NULL) OR
(se.material_detail_id = p_sample.material_detail_id)
)
AND ((se.routing_id is NULL AND p_sample.routing_id is NULL) OR
(se.routing_id = p_sample.routing_id)
)
AND ((se.step_id is NULL AND p_sample.step_id is NULL) OR
(se.step_id = p_sample.step_id)
)
AND ((se.oprn_id is NULL AND p_sample.oprn_id is NULL) OR
(se.oprn_id = p_sample.oprn_id)
)
AND ((se.charge is NULL AND p_sample.charge is NULL) OR
(se.charge = p_sample.charge)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added lot_number id
(se.lot_number = p_sample.lot_number)
)
AND ((se.subinventory IS NULL AND p_sample.source_subinventory IS NULL) OR --Bug# 3736716. Added Source warehouse
(se.subinventory = p_sample.source_subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.source_locator_id IS NULL) OR --Bug# 3736716. Added Source Location
(se.locator_id = p_sample.source_locator_id)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se
WHERE se.inventory_item_id = p_sample.inventory_item_id
AND se.organization_id = p_sample.organization_id
AND se.original_spec_vr_id IS NULL
AND ((se.cust_id is NULL AND p_sample.cust_id is NULL) OR
(se.cust_id = p_sample.cust_id)
)
AND ((se.org_id is NULL AND p_sample.org_id is NULL) OR
(se.org_id = p_sample.org_id)
)
AND ((se.order_id is NULL AND p_sample.order_id is NULL) OR
(se.order_id = p_sample.order_id)
)
AND ((se.order_line_id is NULL AND p_sample.order_line_id is NULL) OR
(se.order_line_id = p_sample.order_line_id)
)
AND ((se.ship_to_site_id is NULL AND p_sample.ship_to_site_id is NULL) OR
(se.ship_to_site_id = p_sample.ship_to_site_id)
)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot no.
(se.lot_number = p_sample.lot_number)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se
WHERE se.inventory_item_id = p_sample.inventory_item_id
AND se.organization_id = p_sample.organization_id
AND se.original_spec_vr_id IS NULL
AND ((se.supplier_id is NULL AND p_sample.supplier_id is NULL) OR
(se.supplier_id = p_sample.supplier_id)
)
AND ((se.supplier_site_id is NULL AND p_sample.supplier_site_id is NULL) OR
(se.supplier_site_id = p_sample.supplier_site_id)
)
AND ((se.po_header_id is NULL AND p_sample.po_header_id is NULL) OR
(se.po_header_id = p_sample.po_header_id)
)
AND ((se.po_line_id is NULL AND p_sample.po_line_id is NULL) OR
(se.po_line_id = p_sample.po_line_id)
)
AND ((se.subinventory is NULL AND p_sample.subinventory is NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id is NULL AND p_sample.locator_id is NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
(se.lot_number = p_sample.lot_number)
)
AND ((se.lpn_id IS NULL AND p_sample.lpn_id IS NULL) OR --RLNAGARA LPN ME 7027149
(se.lpn_id = p_sample.lpn_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se
WHERE ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
(se.organization_id = p_sample.organization_id)
)
AND se.source = p_sample.source
AND se.original_spec_vr_id IS NULL
AND ((se.resources IS NULL AND p_sample.resources IS NULL) OR
( (se.resources = p_sample.resources) AND
((se.instance_id is NULL AND p_sample.instance_id is NULL) OR
(se.instance_id = p_sample.instance_id) ) )
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
ORDER BY se.creation_date desc
;
SELECT se.sampling_event_id
FROM gmd_sampling_events se
WHERE ((se.organization_id is NULL) OR
(se.organization_id = p_sample.organization_id )
)
AND se.source = p_sample.source
AND se.original_spec_vr_id IS NULL
AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
(se.subinventory = p_sample.subinventory)
)
AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
(se.locator_id = p_sample.locator_id)
)
AND se.disposition IN ('1P', '2I') -- Pending or In Process
ORDER BY se.creation_date desc
;
SELECT 1
FROM gmd_samples
WHERE organization_id = p_organization_id
AND sample_no = p_sample_no
;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_sample.inventory_item_id
AND organization_id = p_sample.organization_id;
PROCEDURE update_sample_comp_disp
(
p_update_disp_rec IN UPDATE_DISP_REC
, p_to_disposition IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_message_data OUT NOCOPY VARCHAR2
) IS
l_event_spec_disp_id NUMBER(15);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date DATE ;
SELECT step_id,organization_id,sample_type,source, sampling_event_id -- added sampling_event_id for bug 8252179
FROM gmd_samples
WHERE sample_id = p_update_disp_rec.sample_id;
SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,GMD_SAMPLES gs
WHERE gs.SAMPLE_ID = p_update_disp_rec.sample_id
AND gse.SAMPLING_EVENT_ID = gs.SAMPLING_EVENT_ID
AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
SELECT step_id,organization_id,sample_type,source , disposition -- added disposition for bug 8252179
FROM gmd_sampling_events
WHERE sampling_event_id = p_sampling_event_id;
SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse
WHERE gse.SAMPLING_EVENT_ID = p_sampling_event_id
AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
SELECT disposition
FROM gmd_sampling_events
WHERE sampling_event_id = p_sampling_event_id;
gmd_debug.put_line('Entered Procedure UPDATE_SAMPLE_COMP_DISP');
gmd_debug.put_line('Sample ID: ' || p_update_disp_rec.sample_id);
gmd_debug.put_line('Composite Spec Disp ID: ' || p_update_disp_rec.composite_spec_disp_id);
gmd_debug.put_line('Event Spec Disp ID: ' || p_update_disp_rec.event_spec_disp_id);
gmd_debug.put_line('Change Disp From: ' || p_update_disp_rec.curr_disposition);
IF (p_update_disp_rec.sample_id IS NULL AND p_update_disp_rec.composite_spec_disp_id IS NULL) OR (p_to_disposition IS NULL) THEN
raise REQ_FIELDS_MISSING;
IF p_update_disp_rec.sample_id IS NOT NULL AND p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
raise INVALID_PARAMETER;
IF (p_update_disp_rec.curr_disposition IS NULL OR p_update_disp_rec.event_spec_disp_id IS NULL) THEN
raise REQ_FIELDS_MISSING;
l_last_updated_by := FND_GLOBAL.USER_ID ;
l_last_update_login := FND_GLOBAL.LOGIN_ID ;
l_last_update_date := SYSDATE ;
IF p_update_disp_rec.sample_id IS NOT NULL THEN
l_sample_curr_disp := p_update_disp_rec.curr_disposition;
l_event_spec_disp_id := p_update_disp_rec.event_spec_disp_id;
SELECT SPEC_USED_FOR_LOT_ATTRIB_IND ,sampling_event_id,disposition
INTO l_sample_disp_curr_flag , l_sampling_event_id , l_curr_event_disp
FROM gmd_event_spec_disp
WHERE event_spec_disp_id = l_event_spec_disp_id
FOR UPDATE OF SPEC_USED_FOR_LOT_ATTRIB_IND NOWAIT;
SELECT disposition INTO l_compare_sample_disp
FROM gmd_sample_spec_disp
WHERE event_spec_disp_id = l_event_spec_disp_id
AND sample_id = p_update_disp_rec.sample_id
FOR UPDATE OF disposition NOWAIT ;
UPDATE gmd_sample_spec_disp
SET disposition = p_to_disposition,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE event_spec_disp_id = l_event_spec_disp_id
AND sample_id = p_update_disp_rec.sample_id ;
SELECT nvl(sample_active_cnt, 0), nvl(sample_req_cnt, 1) -- peter lowe added this to get counts
INTO l_active_cnt, l_req_cnt
FROM gmd_sampling_events
WHERE sampling_event_id = l_sampling_event_id;
IF Nvl(p_update_disp_rec.sample_id, 0) <> 0 AND /* Added in 8252179 */
l_sg_event_id <> 0 AND
(Nvl(l_active_cnt, 0) = 1 AND Nvl(l_req_cnt, 0) = 1) THEN
-- only do so if the sample group disposition is in 4A or 5AV -- 8252179
-- IF (l_sg_disposition in ('4A', '5AV')) then -- if added for -- 8252179
IF (l_debug = 'Y') THEN
gmd_debug.put_line('Before Calling gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 6, BATCHSTEP_ID:'||l_step_id);
gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 6, return_status);
IF Nvl(p_update_disp_rec.sample_id, 0) <> 0 AND -- added for -- 8252179
l_sg_event_id <> 0 AND
(Nvl(l_active_cnt, 0) = 1 AND Nvl(l_req_cnt, 0) = 1) THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line('Before Calling gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 5, BATCHSTEP_ID:'||l_step_id);
gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 5, return_status);
UPDATE gmd_sampling_events
SET sample_active_cnt = sample_active_cnt - 1,
recomposite_ind = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE sampling_event_id = l_sampling_event_id ;
UPDATE gmd_sampling_events
SET sample_active_cnt = sample_active_cnt + 1,
recomposite_ind = 'Y',
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE sampling_event_id = l_sampling_event_id ;
SELECT nvl(sample_active_cnt,0),nvl(sample_req_cnt,1)
INTO l_active_cnt,l_req_cnt
FROM gmd_sampling_events
WHERE sampling_event_id = l_sampling_event_id
FOR UPDATE OF disposition NOWAIT ;
ELSIF p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
l_sample_curr_disp := p_update_disp_rec.curr_disposition;
l_event_spec_disp_id := p_update_disp_rec.event_spec_disp_id;
SELECT esd.SPEC_USED_FOR_LOT_ATTRIB_IND ,esd.sampling_event_id,csd.disposition
INTO l_sample_disp_curr_flag , l_sampling_event_id ,l_compare_sample_disp
FROM gmd_composite_spec_disp csd , gmd_event_spec_disp esd
WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
AND esd.event_spec_disp_id = csd.event_spec_disp_id
FOR UPDATE OF esd.SPEC_USED_FOR_LOT_ATTRIB_IND , csd.disposition NOWAIT;
UPDATE gmd_composite_spec_disp
SET disposition = p_to_disposition
WHERE composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id ;
SELECT nvl(sample_active_cnt,0),nvl(sample_req_cnt,1)
INTO l_active_cnt,l_req_cnt
FROM gmd_sampling_events
WHERE sampling_event_id = l_sampling_event_id
FOR UPDATE OF disposition NOWAIT ;
gmd_debug.put_line('Before Calling gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 6, BATCHSTEP_ID:'||l_step_id);
gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 6, return_status); -- 8252179
gmd_debug.put_line('Before Calling gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 5, BATCHSTEP_ID:'||l_step_id);
gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 5, return_status);
IF p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
l_final_event_disp := p_to_disposition ;
SELECT MAX(ssd.disposition) INTO l_max_disposition
FROM gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
WHERE esd.event_spec_disp_id = l_event_spec_disp_id
AND esd.event_spec_disp_id = ssd.event_spec_disp_id
AND esd.delete_mark = 0
AND ssd.delete_mark = 0
AND ssd.disposition NOT IN ('0RT', '7CN');
SELECT MAX(ssd.disposition),MIN(ssd.disposition)
INTO l_max_disposition,l_min_disposition
FROM gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
WHERE
esd.event_spec_disp_id = l_event_spec_disp_id
AND esd.event_spec_disp_id = ssd.event_spec_disp_id
AND esd.delete_mark = 0
AND ssd.delete_mark = 0
AND ssd.disposition NOT IN ('0RT','7CN');
UPDATE gmd_event_spec_disp
SET disposition = l_final_event_disp,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE event_spec_disp_id = l_event_spec_disp_id ;
UPDATE gmd_sampling_events
SET disposition = l_final_event_disp,
last_updated_by = l_last_updated_by,
last_update_date = l_last_update_date,
last_update_login = l_last_update_login
WHERE sampling_event_id = l_sampling_event_id ;
gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
gmd_api_pub.log_message('GMD_INVALID_PARAM','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
gmd_api_pub.log_message('GMD_SAMPLE_SPEC_CHANGED','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
gmd_api_pub.log_message('GMD_SMPL_DISP_CHANGE','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
END update_sample_comp_disp ;
PROCEDURE update_change_disp_table
(
p_update_change_disp_rec IN UPDATE_CHANGE_DISP_REC
, x_return_status OUT NOCOPY VARCHAR2
, x_message_data OUT NOCOPY VARCHAR2
) IS
--xxx
CURSOR Cur_get_seq IS
SELECT gmd_qc_change_disp_id_s.NEXTVAL
FROM DUAL;
SELECT lot_number
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id = p_update_change_disp_rec.inventory_item_id
AND organization_id = p_update_change_disp_rec.organization_id
AND parent_lot_number = p_update_change_disp_rec.parent_lot_number ;
gmd_debug.put_line('In Procedure update_change_disp_table and input parameters = ');
gmd_debug.put_line(' organization ID: ' || p_update_change_disp_rec.organization_id);
gmd_debug.put_line(' Sample ID: ' || p_update_change_disp_rec.sample_id);
gmd_debug.put_line(' sampling_event_id : ' || p_update_change_disp_rec.sampling_event_id);
gmd_debug.put_line(' disposition_from : ' || p_update_change_disp_rec.disposition_from);
gmd_debug.put_line(' disposition_to : ' || p_update_change_disp_rec.disposition_to);
gmd_debug.put_line(' parent lot number : ' || p_update_change_disp_rec.parent_lot_number);
gmd_debug.put_line(' lot number: ' || p_update_change_disp_rec.lot_number);
gmd_debug.put_line(' lot status id: ' || p_update_change_disp_rec.to_lot_status_id);
gmd_debug.put_line(' lot status id: ' || p_update_change_disp_rec.from_lot_status_id);
gmd_debug.put_line(' grade code: ' || p_update_change_disp_rec.to_grade_code);
gmd_debug.put_line(' grade code: ' || p_update_change_disp_rec.from_grade_code);
gmd_debug.put_line(' hold date: ' || p_update_change_disp_rec.hold_date);
gmd_debug.put_line(' reason id : ' || p_update_change_disp_rec.reason_id);
INSERT INTO GMD_CHANGE_DISPOSITION
(
CHANGE_DISP_ID
,ORGANIZATION_ID
,SAMPLE_ID
,SAMPLING_EVENT_ID
,DISPOSITION_FROM
,DISPOSITION_TO
,PARENT_LOT_NUMBER
,LOT_NUMBER
,LOT_STATUS_ID
,GRADE_CODE
,REASON_ID
,HOLD_DATE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES
(
l_change_disp_id
,p_update_change_disp_rec.ORGANIZATION_ID
,p_update_change_disp_rec.SAMPLE_ID
,p_update_change_disp_rec.SAMPLING_EVENT_ID
,p_update_change_disp_rec.DISPOSITION_FROM
,p_update_change_disp_rec.DISPOSITION_TO
,p_update_change_disp_rec.PARENT_LOT_NUMBER
,p_update_change_disp_rec.LOT_NUMBER
,p_update_change_disp_rec.TO_LOT_STATUS_ID
,p_update_change_disp_rec.TO_GRADE_CODE
,p_update_change_disp_rec.REASON_ID
,p_update_change_disp_rec.HOLD_DATE
,SYSDATE
,fnd_global.user_id
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);
gmd_api_pub.log_message('GMD_QM_CHANGE_DISP_ERR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_CHANGE_DISP_TABLE');
IF (p_update_change_disp_rec.LOT_NUMBER IS NOT NULL) THEN
-- just one lot updated
INSERT INTO GMD_CHANGE_LOTS
(
CHANGE_DISP_ID
,LOT_NUMBER
,FROM_LOT_STATUS_ID
,FROM_GRADE_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES
(
l_change_disp_id
,p_update_change_disp_rec.LOT_NUMBER
,p_update_change_disp_rec.FROM_LOT_STATUS_ID
,p_update_change_disp_rec.FROM_GRADE_CODE
,SYSDATE
,fnd_global.user_id
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);
ELSIF (p_update_change_disp_rec.PARENT_LOT_NUMBER IS NOT NULL) THEN
OPEN cur_get_lot;
INSERT INTO GMD_CHANGE_LOTS
(
CHANGE_DISP_ID
,LOT_NUMBER
,FROM_LOT_STATUS_ID
,FROM_GRADE_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES
(
l_change_disp_id
,L_LOT_NUMBER
,p_update_change_disp_rec.FROM_LOT_STATUS_ID
,p_update_change_disp_rec.FROM_GRADE_CODE
,SYSDATE
,fnd_global.user_id
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);
END IF; -- (p_update_change_disp_rec.LOT_NUMBER IS NOT NULL)
gmd_api_pub.log_message('GMD_QM_CHANGE_LOT_ERR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_CHANGE_DISP_TABLE');
END update_change_disp_table;
PROCEDURE update_lot_grade_batch
(
p_sample_id IN NUMBER DEFAULT NULL
, p_composite_spec_disp_id IN NUMBER DEFAULT NULL
, p_to_lot_status_id IN NUMBER
, p_from_lot_status_id IN NUMBER
, p_to_grade_code IN VARCHAR2
, p_from_grade_code IN VARCHAR2 DEFAULT NULL
, p_to_qc_status IN NUMBER
, p_reason_id IN NUMBER
, p_hold_date IN DATE DEFAULT SYSDATE
, x_return_status OUT NOCOPY VARCHAR2
, x_message_data OUT NOCOPY VARCHAR2
) IS
l_position VARCHAR2(3) := '010';
l_inv_status_upd_rec_type INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type; -- 12829066 changed position
SELECT hold_date
FROM mtl_lot_numbers
WHERE parent_lot_number = p_parent_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT 1
FROM mtl_lot_numbers
WHERE parent_lot_number = p_parent_lot_number
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT lot_number,
status_id
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND lot_number = l_lot_number ;
SELECT default_status_id, NVL(wms_enabled_flag,'N') -- 12829066
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT UNIQUE moqd.lpn_id FROM mtl_onhand_quantities_detail moqd
--lpn_id IS NOT NULL -- 12829066 taking this out
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
and lot_number = l_inv_status_upd_rec_type.lot_number;
cursor cur_get_loose_lpn IS -- 12829066 new cursor - -- adding this so that update for loose material as well - will pick up lpns and also any null values
SELECT moqd.lpn_id FROM mtl_onhand_quantities_detail moqd
where lpn_id IS NULL
AND inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
and lot_number = l_inv_status_upd_rec_type.lot_number;
SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,GMD_SAMPLES gs
WHERE gs.SAMPLE_ID = p_sample_id
AND gse.SAMPLING_EVENT_ID = gs.SAMPLING_EVENT_ID
AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,
GMD_EVENT_SPEC_DISP esd, GMD_COMPOSITE_SPEC_DISP csd
WHERE csd.COMPOSITE_SPEC_DISP_ID = p_composite_spec_disp_id
AND esd.EVENT_SPEC_DISP_ID = csd.EVENT_SPEC_DISP_ID
AND gse.SAMPLING_EVENT_ID = esd.SAMPLING_EVENT_ID
AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
l_lot_update_profile VARCHAR2(3);
l_lot_update_profile := NVL(fnd_profile.value('GMD: Update On-hand Status in All Locators'),'1');
gmd_debug.Log_Initialize('Update_lot_grade_batch');
gmd_debug.put_line('Entering update_lot_grade_batch p_sample_id :'||p_sample_id);
SELECT gs.organization_id ,
gs.inventory_item_id,
iim.concatenated_segments,
gs.parent_lot_number,
gs.lot_number,
gs.batch_id,
gs.step_no,
gs.locator_id, -- 50061731
gs.subinventory, -- 50061731
nvl(gs.lpn_id,0) -- 12829066
INTO l_organization_id,
l_inventory_item_id,
l_inventory_item_number,
l_parent_lot_number,
l_lot_number,
l_batch_id,
l_step_no,
l_locator_id, -- 50061731
l_subinventory, -- 50061731
l_lpn_id -- 12829066
FROM GMD_SAMPLES gs,
MTL_SYSTEM_ITEMS_b_kfv iim
WHERE gs.sample_id = p_sample_id
AND gs.inventory_item_id = iim.inventory_item_id
AND gs.organization_id = iim.organization_id;
gmd_debug.put_line('Inside Update_lot_grade_batch - lpn from sample = :'||l_lpn_id);
SELECT gse.organization_id,
gse.inventory_item_id,
iim.concatenated_segments,
gse.parent_lot_number,
gse.lot_number,
gse.sampling_event_id,
gse.batch_id,
gse.step_no
INTO l_organization_id,
l_inventory_item_id,
l_inventory_item_number,
l_parent_lot_number,
l_lot_number,
l_sampling_event_id,
l_batch_id,
l_step_no
FROM GMD_COMPOSITE_SPEC_DISP csd,
GMD_EVENT_SPEC_DISP esd ,
GMD_SAMPLING_EVENTS gse,
MTL_SYSTEM_ITEMS_b_kfv iim
WHERE csd.composite_spec_disp_id = p_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.inventory_item_id = iim.inventory_item_id
and gse.organization_id = iim.organization_id ;
-- select the orgn_code from the first sample.
-- Bug 4165704: took out the following code since orgn is now kept on sampling event.
-- added organization to select statement above
--SELECT orgn_code INTO l_orgn_code
--FROM GMD_SAMPLES
--WHERE sampling_event_id = l_sampling_event_id
--AND rownum = 1 ;
UPDATE mtl_lot_numbers
SET hold_date = p_hold_date
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND ((parent_lot_number = l_parent_lot_number )
OR ( lot_number = l_parent_lot_number
AND parent_lot_number IS NULL) );
UPDATE mtl_lot_numbers
SET hold_date = p_hold_date
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND lot_number = l_lot_number;
--SELECT lot_no,sublot_no FROM IC_LOTS_MST
--WHERE item_id = l_item_id
--AND lot_no = l_lot_no
--AND sublot_no = l_sublot_no
--AND qc_grade <> p_to_grade_code;
SELECT lot_number,
grade_code
FROM mtl_lot_numbers
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND ((parent_lot_number = l_parent_lot_number )
OR ( lot_number = l_parent_lot_number
AND parent_lot_number IS NULL) )
AND grade_code <> p_to_grade_code;
INV_GRADE_PKG.UPDATE_GRADE
( p_organization_id => l_organization_id
, p_update_method => 2 -- (Manual)
, p_inventory_item_id => l_inventory_item_id
, p_from_grade_code => p_from_grade_code
, p_to_grade_code => p_to_grade_code
, p_reason_id => p_reason_id
, p_lot_number => get_curr_grade_rec.lot_number
, x_Status => x_return_status
, x_Message => l_data
, p_update_from_mobile => 'N' -- default value
, p_primary_quantity => NULL -- not sure what this value is yet
, p_secondary_quantity => NULL ); --xxx not sure what this value is yet
SELECT lot_number,
grade_code
FROM mtl_lot_numbers
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND lot_number = l_lot_number
AND grade_code <> p_to_grade_code;
INV_GRADE_PKG.UPDATE_GRADE
( p_organization_id => l_organization_id
, p_update_method => 2 -- (Manual)
, p_inventory_item_id => l_inventory_item_id
, p_from_grade_code => p_from_grade_code
, p_to_grade_code => p_to_grade_code
, p_reason_id => p_reason_id
, p_lot_number => l_lot_number
, x_Status => x_return_status
, x_Message => l_data
, p_update_from_mobile => 'N' -- default value
, p_primary_quantity => NULL -- not sure what this value is yet
, p_secondary_quantity => NULL ); --xxx not sure what this value is yet
-- SELECT b.lot_no,a.whse_code,a.location
-- FROM IC_LOCT_INV a , IC_LOTS_MST b
-- WHERE a.item_id = l_inventory_item_id
-- AND b.item_id = a.item_id
-- AND b.lot_no = l_lot_number
-- AND a.lot_status <> p_to_lot_status;
SELECT lot_number,
status_id
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND ((parent_lot_number = l_parent_lot_number )
OR ( lot_number = l_parent_lot_number
AND parent_lot_number IS NULL) )
AND status_id <> p_to_lot_status_id; */
SELECT lot_number,
a.status_id
FROM MTL_LOT_NUMBERS a, mtl_item_locations_kfv b -- 50061731
WHERE a.inventory_item_id = l_inventory_item_id
AND a.organization_id = l_organization_id
AND ((a.parent_lot_number = l_parent_lot_number )
OR ( a.lot_number = l_parent_lot_number
AND a.parent_lot_number IS NULL) )
AND a.status_id <> p_to_lot_status_id
AND b.organization_id = l_organization_id -- 50061731
AND b.subinventory_code = nvl(l_subinventory, b.subinventory_code ) -- 50061731
AND b.inventory_location_id = nvl(l_locator_id,b.inventory_location_id ); -- 50061731
SELECT lot_number,
a.status_id
FROM MTL_LOT_NUMBERS a
WHERE a.inventory_item_id = l_inventory_item_id
AND a.organization_id = l_organization_id
AND ((a.parent_lot_number = l_parent_lot_number )
OR ( a.lot_number = l_parent_lot_number
AND a.parent_lot_number IS NULL) )
AND a.status_id <> p_to_lot_status_id;
-- Bug 4165704: replaced GMIPAPI.Inventory_Posting with Inv_Status_Pkg.update_status
-- l_trans_rec_lot_status.trans_type := 4;
l_inv_status_upd_rec_type.update_reason_id := p_reason_id;
l_inv_status_upd_rec_type.update_method := 2;
l_inv_status_upd_rec_type.last_updated_by := FND_GLOBAL.USER_ID;
l_inv_status_upd_rec_type.last_update_date := SYSDATE;
l_inv_status_upd_rec_type.last_update_login := FND_GLOBAL.LOGIN_ID;
gmd_debug.put_line('INV_MATERIAL_STATUS_PUB.update_status - lpn passed from sample = :'||l_lpn_id);
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status - lpn is passed from sample - with l_lpn_id :'||l_lpn_id);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
gmd_debug.put_line('about to try to call INV_MATERIAL_STATUS_PUB.update_status - lpn NOT passed from sample - org is wms enabled');
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status API with LPN in a loop with l_lpn_id :'||l_lpn_id);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status - lpn NOT passed from sample - org is NOT wms enabled with l_lpn_id :'||l_lpn_id);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
l_inv_status_upd_rec_type.update_reason_id := p_reason_id;
l_inv_status_upd_rec_type.update_method := 2;
l_inv_status_upd_rec_type.last_updated_by := FND_GLOBAL.USER_ID;
l_inv_status_upd_rec_type.last_update_date := SYSDATE;
l_inv_status_upd_rec_type.last_update_login := FND_GLOBAL.LOGIN_ID;
if( l_lot_update_profile = '0') THEN
l_inv_status_upd_rec_type.LOCATOR_ID := l_locator_id;
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status lot number is not null - lpn is passed from sample - with l_lpn_id :'||l_lpn_id);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status ; lot number is not null - LOOSE MATERIAL IN THIS mtl_onhand_quantities_detail FOR ORG, LOCATOR AND LOT . LOT = :'|| l_inv_status_upd_rec_type.lot_number);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status lot number is not null - lpn NOT passed from sample - org is wms enabled');
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status API with LPN in a loop with l_lpn_id :'||l_lpn_id);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status API with no LPN found lpn NOT passed from sample - org is wms enabled ; lot number :'|| l_inv_status_upd_rec_type.lot_number);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
gmd_debug.put_line('calling INV_MATERIAL_STATUS_PUB.update_status - lpn NOT passed from sample - org is NOT wms enabled with l_lpn_id :'||l_lpn_id);
INV_MATERIAL_STATUS_PUB.update_status
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => l_count,
x_msg_data => l_data,
p_object_type => l_object_type,
p_status_rec => l_inv_status_upd_rec_type);
/* update the one lot specified
Inv_Status_Pkg.update_status(
p_update_method => 2 --(Manual)
, p_organization_id => l_organization_id
, p_inventory_item_id => l_inventory_item_id
, p_sub_code => NULL
, p_sub_status_id => NULL
, p_sub_reason_id => NULL
, p_locator_id => NULL
, p_loc_status_id => NULL
, p_loc_reason_id => NULL
, p_from_lot_number => l_lot_number --from_lot_number
, p_to_lot_number => l_lot_number --to_lot_number
, p_lot_status_id => p_to_lot_status_id
, p_lot_reason_id => p_reason_id
, p_from_SN => NULL
, p_to_SN => NULL
, p_serial_status_id => 0 -- 91432301
, p_serial_reason_id => NULL
, x_Status => x_return_status
, x_Message => l_data
, p_update_from_mobile => 'N' --(DEFAULT 'Y')
, p_grade_code => NULL --(DEFAULT NULL)
, p_primary_onhand => NULL --(DEFAULT NULL)
, p_secondary_onhand => NULL
, p_onhand_status_id => l_onhand_status_id -- Added for # 91432301
, p_onhand_reason_id => l_onhand_reason_id -- Added for # 91432301
); */
-- Bug # 4619570 Allow update of batch step quality status if batch is not closed
/*SELECT batch_status INTO l_batch_status -- Bug # 4619570 Need to know if batch is closed
FROM gme_batch_header
WHERE batch_id = l_batch_id; */
SELECT step_status INTO l_step_status
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND batchstep_no = l_step_no;
SELECT quality_status,rowid, batchstep_id INTO l_curr_qc_status,l_rowid, l_bstep_id -- Bug# 5440347 Added batchstep_id
FROM GME_BATCH_STEPS
WHERE BATCH_ID = l_batch_id
AND batchstep_no = l_step_no
FOR UPDATE OF quality_status NOWAIT ;
gmd_debug.put_line('about to UPDATE GME_BATCH_STEPS quality status with p_to_qc_status :'||p_to_qc_status);
UPDATE GME_BATCH_STEPS
SET quality_status = p_to_qc_status -- NO ACTION BUT could be here too 8252179
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_date = SYSDATE
,last_update_login = FND_GLOBAL.LOGIN_ID
WHERE rowid = l_rowid ;
gmd_debug.put_line('exiting update_lot_grade_batch p_sample_id :'||p_sample_id);
gmd_api_pub.log_message('GMD_QM_CURRENT_LOT_VALUE_CHANG','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SAMPLES_GRP.UPDATE_LOT_GRADE_BATCH');
gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_LOT_GRADE_BATCH','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
END update_lot_grade_batch ;
SELECT 1
FROM mtl_parameters m,
org_access_view v
WHERE v.organization_id = m.organization_id
AND m.organization_id = p_organization_id
AND m. process_enabled_flag = 'Y' ;
SELECT 1
FROM FND_USER
WHERE user_id = p_sampler_id;
SELECT 1
FROM mtl_parameters m,
gmd_quality_config g ,
org_access_view v
WHERE g.quality_lab_ind = 'Y'
AND g.organization_id = m.organization_id
AND v.organization_id = m.organization_id
AND m.organization_id = p_organization_id
AND m. process_enabled_flag = 'Y' ;
SELECT 1
FROM mtl_system_items_b_kfv
WHERE organization_id = p_organization_id
AND process_quality_enabled_flag = 'Y'
AND inventory_item_id = p_inventory_item_id;
SELECT 1
FROM gmd_sampling_events
WHERE sampling_event_id = p_sampling_event_id
;
SELECT 1
FROM mtl_secondary_inventories s
WHERE s.organization_id = p_sample.organization_id
AND s.secondary_inventory_name = p_sample.subinventory;
SELECT 1
FROM mtl_item_locations_kfv
WHERE organization_id = p_sample.organization_id
AND subinventory_code = p_sample.subinventory
AND inventory_location_id = p_sample.locator_id;
SELECT 1
FROM mtl_lot_numbers
WHERE organization_id = p_sample.organization_id
AND inventory_item_id = p_sample.inventory_item_id
AND lot_number = p_sample.lot_number;
SELECT 1
FROM gme_batch_header bh
WHERE bh.batch_id = p_sample.batch_id
AND bh.batch_type = 0 -- Only Batches, No FPOs
AND ( ( bh.batch_status IN (1,2) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') ) -- Bug # 4619570 Pending or WIP Batches Only
OR ( bh.batch_status IN (1,2, 4 ) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) -- Pending or WIP Or Closed Batches Only
AND exists -- Only Batches with Spec Item in it
(SELECT 1
FROM gme_material_details md
WHERE md.batch_id = bh.batch_id
AND md.inventory_item_id = p_sample.inventory_item_id)
;
SELECT 1
FROM gmd_recipes r, gmd_status s
WHERE r.recipe_status = s.status_code
AND r.recipe_id = p_sample.recipe_id
AND s.status_type <> '1000'
AND r.delete_mark = 0
AND exists
(SELECT 1
FROM fm_matl_dtl md
WHERE md.formula_id = r.formula_id
AND md.inventory_item_id = p_sample.inventory_item_id)
;
SELECT 1
FROM fm_form_mst f, fm_matl_dtl md
WHERE f.formula_id = md.formula_id
AND f.formula_id = nvl(p_sample.formula_id, f.formula_id)
AND md.formulaline_id = p_sample.formulaline_id
AND md.inventory_item_id = p_sample.inventory_item_id
AND f.delete_mark = 0
;
SELECT 1
FROM gme_material_details
WHERE inventory_item_id = p_sample.inventory_item_id
AND batch_id = p_sample.batch_id
AND organization_id = p_sample.organization_id
AND material_detail_id = p_sample.material_detail_id;
SELECT 1
FROM gme_batch_steps
WHERE batch_id = p_sample.batch_id
AND batchstep_no = p_sample.step_no
;
SELECT 1
FROM gmd_routings_b
WHERE routing_id = p_sample.routing_id;
SELECT 1
FROM gmd_operations
WHERE oprn_id = p_sample.oprn_id
AND delete_mark = 0
;
SELECT 1
FROM hz_cust_accounts_all
WHERE cust_account_id = p_sample.cust_id
;
SELECT 1
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all s,
hz_cust_accounts_all c
WHERE a.cust_acct_site_id = s.cust_acct_site_id
AND a.org_id = s.org_id
AND a.cust_account_id = c.cust_account_id
AND c.cust_account_id = p_sample.cust_id
AND s.site_use_code = 'SHIP_TO'
AND s.org_id = p_sample.org_id
AND s.site_use_id = p_sample.ship_to_site_id
;
SELECT 1
FROM oe_order_headers_all h,
oe_transaction_types_tl t
WHERE h.sold_to_org_id = p_sample.cust_id
AND h.org_id = p_sample.org_id
AND h.header_id = p_sample.order_id
AND h.cancelled_flag <> 'Y'
AND h.order_type_id = t.transaction_type_id
AND t.language = USERENV('LANG')
;
SELECT 1
FROM oe_order_lines_all l,
mtl_system_items_b m,
mtl_parameters mp,
ic_item_mst i
WHERE l.header_id = p_sample.order_id
AND l.line_id = p_sample.order_line_id
AND l.ship_to_org_id = p_sample.ship_to_site_id
AND m.inventory_item_id = l.inventory_item_id
AND m.organization_id = l.ship_from_org_id
AND mp.organization_id = m.organization_id
AND mp.process_enabled_flag = 'Y'
AND i.item_id = p_sample.inventory_item_id
AND m.segment1 = i.item_no
AND l.cancelled_flag <> 'Y'
;
SELECT 1
FROM po_vendors v
WHERE v.vendor_id = p_sample.supplier_id
AND v.enabled_flag = 'Y'
AND sysdate between nvl(v.start_date_active, sysdate-1)
and nvl(v.end_date_active, sysdate+1)
;
SELECT 1
FROM po_headers_all
WHERE vendor_id = p_sample.supplier_id
AND po_header_id = p_sample.po_header_id
;
SELECT 1
FROM po_lines_all l
WHERE l.po_header_id = p_sample.po_header_id
AND l.po_line_id = p_sample.po_line_id
AND EXISTS
(SELECT 1
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = l.item_id
AND msi.inventory_item_id = p_sample.inventory_item_id) ;
SELECT 1
FROM
WMS_LICENSE_PLATE_NUMBERS WHERE lpn_id = p_sample.lpn_id;
SELECT wms_enabled_flag
FROM mtl_parameters
WHERE organization_id = p_sample.organization_id;
SELECT DISTINCT subinventory,
locator_id
FROM rcv_transactions rt
WHERE rt.shipment_header_id = p_sample.receipt_id
AND rt.shipment_line_id = p_sample.receipt_line_id
AND rt.destination_type_code = 'INVENTORY'
AND rt.transaction_type = 'DELIVER';
SELECT mtl_material_transactions_s.NEXTVAL
FROM DUAL;
INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id
, source_code
, source_line_id
, last_updated_by
, last_update_date
, created_by
, creation_date
, last_update_login
, transaction_quantity
, lot_number
, reason_id
, description )
VALUES
( p_transaction_interface_id
, 'SAMPLES'
, p_sample.sample_id
, p_user_name
, p_sample.date_drawn
, p_user_name
, p_sample.date_drawn
, p_user_name
, -1*p_sample.sample_qty
, p_sample.lot_number
, quality_config.transaction_reason_id
, 'Sample creation');
gmd_debug.put_line('after insert into lot table:');
INSERT INTO mtl_transactions_interface
(transaction_interface_id
, transaction_header_id
, source_code
, source_line_id
, source_header_id
, process_flag
, validation_required
, transaction_mode
, lock_flag
, last_updated_by
, last_update_date
, created_by
, creation_date
, last_update_login
, organization_id
, inventory_item_id
, revision
, transaction_quantity
, transaction_uom
, transaction_date
, subinventory_code
, locator_id
, transaction_source_id
, transaction_source_type_id
, transaction_type_id
, distribution_account_id
, reason_id
, lpn_id -- palpal
)
VALUES
( p_transaction_interface_id
, p_header_id
, 'SAMPLES'
, p_sample.sample_id
, p_sample.sampling_event_id
, 1 -- process enabled
, 1 -- (full validation required)
, 1 -- (process immediate)
, 2 -- (TM will not lock the trans)
, p_user_name
, p_sample.date_drawn
, p_user_name
, p_sample.date_drawn
, p_user_name
, p_sample.Organization_id
, p_sample.Inventory_Item_id
, p_sample.revision
, -1*p_sample.sample_qty
, P_sample.sample_qty_uom
, p_sample.date_drawn
, l_subinventory
, l_locator_id
, NULL
, 13 --(Inventory)
, 1001 --(Deduct Sample Qty)
, quality_config.distribution_account_id --hardcode 23843 take this out xxx
, quality_config.transaction_reason_id
, p_sample.lpn_id -- palpal
) ;
gmd_debug.put_line('after insert into transaction table:');
SELECT transaction_reason_id
FROM gmd_quality_config
WHERE organization_id = p_sample.organization_id
order by 1 ;
SELECT d.material_detail_id,
d.inventory_item_id, d.revision, d.dtl_um --RLNAGARA B5738041 Added Revision
FROM gme_material_details d,
gme_batch_step_items i
WHERE d.material_detail_id = i.material_detail_id
AND d.line_type = 2
AND d.release_type = 1
AND d.by_product_type = 'S'
AND d.batch_id = p_sample.batch_id
AND d.inventory_item_id = p_sample.inventory_item_id
AND (p_sample.step_id IS NULL
OR i.batchstep_id = p_sample.step_id);
SELECT d.material_detail_id,
d.inventory_item_id, d.revision, d.dtl_um --RLNAGARA B5738041 Added Revision
FROM gme_material_details d
WHERE d.line_type = 2
AND d.release_type = 1
AND d.by_product_type = 'S'
AND d.batch_id = p_sample.batch_id
AND d.inventory_item_id = p_sample.inventory_item_id;
SELECT concatenated_segments item_no ,
dual_uom_control,
primary_uom_code,
secondary_uom_code
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_sample.organization_id;
-- GME_API_PUB.insert_line_allocation(
-- p_api_version => 2.0
--, p_validation_level => p_validation_level
--, p_init_msg_list => FALSE
--, p_commit => FALSE
--, x_message_count => x_message_count
--, x_message_list => x_message_data
--, x_return_status => x_return_status
--, p_material_transaction_inter_rec => p_material_transaction_inter_rec
--, p_batch_no => NULL
--, p_org_code => NULL
--, p_line_no => NULL
--, p_line_type => NULL
--, p_create_lot => p_create_lot --TRUE
--, p_generate_lot => FALSE
--, p_generate_parent_lot => FALSE
--, p_transaction_lot_inter_tbl => FALSE -- lot info for lot interface table
--, x_material_trasaction_rec => x_material_detail -- contains the newly created transaction
--, x_transaction_lot_tbl => x_tran_row); -- contains info for lot transactions
'PACKAGE','INVENTORY_TRANS_INSERT',
'ERROR', SUBSTR(SQLERRM,1,100));
SELECT qc.test_code, mthd.test_duration
FROM gmd_qc_tests_vl qc,
gmd_test_methods_b mthd,
gmd_spec_tests_b spec
WHERE qc.test_method_id = mthd.test_method_id
AND qc.test_id = spec.test_id
AND spec.spec_id = p_spec_id
AND ( mthd.test_duration = (SELECT MAX(test_duration)
FROM gmd_test_methods_b mthd2,
gmd_spec_tests_b spec2
WHERE spec2.TEST_METHOD_ID = mthd2.test_method_id
AND spec2.spec_id = p_spec_id));
PROCEDURE update_remaining_qty
( p_result_id IN NUMBER,
p_sample_id IN NUMBER default 0,
qty IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
--RLNAGARA B5106191 Replaced the query in the cursor C_item_no which was using ic_item_mst.
/* CURSOR C_item_no(item_id VARCHAR2) IS
SELECT item_no
FROM ic_item_mst
WHERE item_id = item_id;
SELECT concatenated_segments item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT 'x' from gmd_samples
where sample_id = samp_id
for update of sample_id NOWAIT ;
gmd_debug.put_line('In Procedure update_remaining_qty');
gmd_debug.put_line('before update : ');
UPDATE gmd_samples
SET remaining_qty = remaining_qty - converted_qty
WHERE sample_id = l_samples.sample_id;
UPDATE gmd_samples
SET remaining_qty = remaining_qty - qty
WHERE sample_id = l_samples.sample_id;
END update_remaining_qty;
SELECT user_name, description
FROM fnd_user
WHERE user_id = name_id ;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = orgn_id;
SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = loc_id;
SELECT l.lookup_code,
l.meaning
FROM gmd_sample_spec_disp ssd,
gem_lookups l
WHERE ssd.sample_id = p_sample_id
AND l.lookup_type = 'GMD_QC_SAMPLE_DISP'
AND l.lookup_code = ssd.disposition ;
SELECT license_plate_number
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT grade_code
FROM mtl_lot_numbers
WHERE organization_id = v_organization
AND lot_number = v_lot_number;
SELECT concatenated_segments, -- (Item_Number)
description,
Restrict_subinventories_code,
restrict_locators_code,
location_control_code,
Revision_qty_control_code, -- (revision cntrl)
Lot_control_code,
Lot_status_enabled,
grade_control_flag,
Primary_uom_code,
Dual_uom_control,
Eng_item_flag, -- (experimental item)
Child_lot_flag, -- parent lot control
Indivisible_flag,
Serial_number_control_code, --(must = 0 to generate inv transaction)
process_yield_subinventory, -- replenish subinventory
process_yield_locator_id -- replenish locator_id
--FROM mtl_system_items_b_kfv -- comment out, bug 13528160
FROM mtl_system_items_vl --Bug 13528160
WHERE organization_id = p_sample_display.organization_id
AND inventory_item_id = p_sample_display.inventory_item_id;
SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_sample_display.source_locator_id;
SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_locator_id;
SELECT Locator_type, -- locator control
description
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory
AND organization_id = p_organization_id;
SELECT SEGMENT1, VENDOR_NAME
FROM PO_VENDORS
WHERE vendor_id = p_supplier_id;
SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = p_PO_HEADER_ID;
SELECT line_num
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT rsh.receipt_num receipt
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = p_receipt_id;
SELECT rsh.receipt_num receipt_no,
rsl.line_num receipt_line_num
FROM rcv_shipment_lines rsl ,
rcv_shipment_headers rsh
WHERE rsl.po_header_id = p_po_header_id
AND rsl.po_line_id = p_po_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = p_receipt_id ;
SELECT vendor_site_code
FROM po_vendor_sites_all
WHERE vendor_site_id = p_supplier_site_id;
SELECT name
FROM HR_OPERATING_UNITS
WHERE organization_id = p_org_id;*/
SELECT OTL.name
FROM HR_ALL_ORGANIZATION_UNITS_TL OTL,
HR_ORGANIZATION_INFORMATION O2
WHERE OTL.organization_id = p_org_id
AND OTL.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND OTL.LANGUAGE = userenv('LANG');
SELECT locator_type -- locator control
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory;
SELECT name
FROM HR_OPERATING_UNITS
WHERE organization_id = p_org_id;*/
SELECT OTL.name
FROM HR_ALL_ORGANIZATION_UNITS_TL OTL,
HR_ORGANIZATION_INFORMATION O2
WHERE OTL.organization_id = p_org_id
AND OTL.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND OTL.LANGUAGE = userenv('LANG');
SELECT location
FROM hz_cust_site_uses_all
WHERE site_use_id = p_ship_to_site_id;
SELECT h.order_number,
t.name
FROM oe_order_headers_all h,
oe_transaction_types_tl t
WHERE h.header_id = p_order_id
AND h.order_type_id = t.transaction_type_id
AND t.language = USERENV('LANG');
SELECT l.line_number||
decode(l.shipment_number,'','','.'|| l.shipment_number) ||
decode(l.option_number||l.component_number||l.service_number,'','','.'||l.option_number) ||
decode(l.component_number||l.service_number,'','','.'|| l.component_number) ||
decode(l.service_number,'','','.'|| l.service_number)
FROM oe_order_lines_all l
WHERE line_id = p_order_line_id ;
SELECT p.party_name /* cust_name */
FROM hz_cust_accounts_all a,
hz_parties p
WHERE a.cust_account_id = p_cust_id
AND a.party_id = p.party_id;
SELECT p.organization_code,
ss_no,
variant_no,
v.storage_locator_id, --xxx needs to changed to v.storage_locator
v.STORAGE_subinventory, -- needs to be changed to v.storage_subinventory,
v.resources,
ri.instance_number
FROM GMD_SS_VARIANTS v,
GMD_STABILITY_STUDIES_B ss,
GMP_RESOURCE_INSTANCES ri,
MTL_PARAMETERS p
WHERE variant_id = p_variant_id
AND ss.ss_id = v.ss_id
AND ri.instance_id(+) = v.resource_instance_id
AND p.organization_id = ss.organization_id;
SELECT tp.name , -- Time Point
tp.scheduled_date
FROM GMD_SS_TIME_POINTS tp
WHERE tp.time_point_id = p_time_point_id;
SELECT INSTANCE_NUMBER
FROM GMP_RESOURCE_INSTANCES
WHERE instance_id = p_instance_id;
SELECT batch_no
FROM gme_batch_header
WHERE batch_id = P_batch_id;
SELECT bs.batchstep_no,
o.oprn_no,
o.oprn_vers
FROM gme_batch_steps bs,
gmd_operations o
WHERE bs.oprn_id = o.oprn_id
AND bs.batch_id = p_batch_id
AND bs.batchstep_id = p_step_id
AND bs.delete_mark = 0;
SELECT recipe_no, recipe_version
FROM gmd_recipes
WHERE recipe_id = P_recipe_id;
SELECT formula_no, formula_vers
FROM fm_form_mst
WHERE formula_id = P_formula_id;
SELECT routing_no, routing_vers
FROM fm_rout_hdr
WHERE routing_id = P_routing_id;
SELECT oprn_no,oprn_vers
FROM fm_oprn_mst
WHERE oprn_id = P_oprn_id;
SELECT fd.line_no,
gem.meaning
FROM fm_matl_dtl fd,
gem_lookups gem
WHERE fd.formula_id = P_formula_id
AND fd.formulaline_id = P_formulaline_id
AND fd.inventory_item_id = P_inventory_item_id
AND gem.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND gem.lookup_code = fd.line_type; */
SELECT line_no,
gem.meaning
FROM gme_material_details md,
gem_lookups gem
WHERE batch_id = P_batch_id
AND material_detail_id = P_material_detail_id
AND inventory_item_id = P_inventory_item_id
AND organization_id = P_organization_id
AND gem.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
AND gem.lookup_code = md.line_type;
SELECT b.disposition,
d.meaning sample_disposition,
e.meaning source
FROM gmd_sample_spec_disp b,
gmd_event_spec_disp c,
gmd_samples s,
fnd_lookup_values_vl d,
fnd_lookup_values_vl e
WHERE b.sample_id = p_sample.sample_id
and b.event_spec_disp_id = c.event_spec_disp_id
and c.spec_used_for_lot_attrib_ind = 'Y'
and b.disposition = d.lookup_code
and d.lookup_type = 'GMD_QC_SAMPLE_DISP'
and e.lookup_type = 'GMD_QC_SOURCE'
and s.sample_id = b.sample_id
and e.lookup_code = s.source
UNION
SELECT b.disposition,
d.meaning sample_disposition,
e.meaning source
FROM gmd_sample_spec_disp b,
gmd_event_spec_disp c,
gmd_samples s,
fnd_lookup_values d,
fnd_lookup_values e
WHERE b.sample_id =p_sample.sample_id
and b.event_spec_disp_id = c.event_spec_disp_id
and c.spec_used_for_lot_attrib_ind = 'Y'
and b.disposition = d.lookup_code
and d.lookup_type = 'GMD_QC_SAMPLE_DISP'
and e.lookup_type = 'GMD_QC_MONITOR_RULE_TYPE'
and s.sample_id = b.sample_id
and e.lookup_code = s.source ;