[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* DELETE_SAMPLES *
* *
* *
* Use This is the public layer for the QC SAMPLES *
* *
* History *
* Written by H Verdding, OPM Development (EMEA) *
* *
* Updated By For *
* *
* HVerddin B2711643: Added call to set user_context *
* *
* 10-APR-2003 H.Verdding -- Added the following Validation *
* -- Validate_item_controls *
* -- Validate_inv_sample *
* -- Validate_wip_sample *
* -- Validate_cust_sample *
* -- Validate_supp_sample *
* -- Validate_sample *
* *
* 20-Mar-2003 Chetan Nagar In error message GMD_SAMPLE_SOURCE_INVALID
* pass proper column SOURCE *
*
* 19-NOV-2003 M. Anil Kumar Bug#3256248 *
* Modified cursor c_batch in validate_wip_sample procedure to *
* consider completed batches also. *
*
* 27-JAN-2004 S. Feinstein Bug #3401377
* Updated for Mini Pack K (API Version 2.0)
* Added the following Validations
* -- Validate_stability_sample
* -- Validate_resource_sample
* -- Validate_location_sample
*
* 03-JUN-2004 Saikiran Vankadari Bug# 3576573. added
* validations for receipt information
* in 'VALIDATE_SUPP_SAMPLE' procedure
*
* 20-MAY-2005 Susan Feinstein Bug 4165704 Inventory Convergence
* 18-OCT-2005 Susan Feinstein Bug 4640143 Added material detail id to gmd_wip_spec_vrs table
* 05-JAN-2006 Joe DiIorio Bug#4691545 Removed profile reference to
* NVL(fnd_profile.value('QC$EXACTSPECMATCH'),'N');
* depending upon the profile option but inv is not updated
* 07-JUN-2006 Peter Lowe - bug 5291723 - check for plant code is obsolete - replace
* organization 5291495
* 09-JUN-2006 Peter Lowe - bug 5291495 - Make sure that the input UOM for the sample qty is validated
* for spec type of 'I' (not 'M')
* 14-JUN-2006 Peter Lowe - bug 5283854 various API creation item sample errors
* 16-Jun-2006 PLOWE Fixed bug# 5335008 in PROCEDURE VALIDATE_CUST_SAMPLE
* CURSOR c_order rewritten as part of bug# 5335008
*************************************************************************************************
*/
-- Global variables
G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_SAMPLES_PUB';
/*SELECT s.sample_inv_trans_ind INTO l_sample_inv_trans_ind
FROM gmd_all_spec_vrs WHERE spec_vr_id = p_spec_vr_id ;*/
select s.sample_inv_trans_ind INTO l_sample_inv_trans_ind
FROM (select spec_vr_id, sample_inv_trans_ind from gmd_inventory_spec_vrs
union all
select spec_vr_id, sample_inv_trans_ind from gmd_wip_spec_vrs
union all
select spec_vr_id, sample_inv_trans_ind from gmd_customer_spec_vrs
union all
select spec_vr_id, sample_inv_trans_ind from gmd_supplier_spec_vrs
union all
select spec_vr_id, NULL sample_inv_trans_ind from gmd_monitoring_spec_vrs
union all
select spec_vr_id, NULL sample_inv_trans_ind from gmd_stability_spec_vrs) s
WHERE s.spec_vr_id = p_spec_vr_id ;
SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
FROM gmd_inventory_spec_vrs
WHERE spec_vr_id = p_spec_vr_id ;
SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
FROM gmd_customer_spec_vrs
WHERE spec_vr_id = p_spec_vr_id ;
SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
FROM gmd_wip_spec_vrs
WHERE spec_vr_id = p_spec_vr_id ;
SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
FROM gmd_supplier_spec_vrs
WHERE spec_vr_id = p_spec_vr_id ;
SELECT b.sample_cnt_req
FROM gmd_all_spec_vrs s, gmd_sampling_plans b
WHERE s.spec_vr_id = p_spec_vr_id
AND s.sampling_plan_id = b.sampling_plan_id;*/
SELECT b.sample_cnt_req
FROM
(select spec_vr_id, sampling_plan_id from gmd_inventory_spec_vrs
union all
select spec_vr_id, sampling_plan_id from gmd_wip_spec_vrs
union all
select spec_vr_id, sampling_plan_id from gmd_customer_spec_vrs
union all
select spec_vr_id, sampling_plan_id from gmd_supplier_spec_vrs
union all
select spec_vr_id, sampling_plan_id from gmd_monitoring_spec_vrs
union all
select spec_vr_id, sampling_plan_id from gmd_stability_spec_vrs) s, gmd_sampling_plans b
WHERE s.spec_vr_id = p_spec_vr_id
AND s.sampling_plan_id = b.sampling_plan_id;
SELECT whse_code
FROM ps_whse_eff
WHERE plant_code = l_qc_samples_out_rec.orgn_code
AND whse_item_id = replenish_item_id
AND replen_ind = 1
AND delete_mark = 0;
SELECT whse_code
FROM ps_whse_eff
WHERE plant_code = l_qc_samples_out_rec.orgn_code
AND replen_ind = 1
AND delete_mark = 0;
SELECT batch_status
FROM gme_batch_header
WHERE batch_id = l_qc_samples_out_rec.batch_id;
SELECT LOT_OPTIONAL_ON_SAMPLE
FROM
(select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_inventory_spec_vrs
union all
select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_wip_spec_vrs
union all
select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_customer_spec_vrs
union all
select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_supplier_spec_vrs
)
WHERE spec_vr_id = p_spec_vr_id;
l_samples_val_rec.last_updated_by := l_user_id;
l_sampling_events.LAST_UPDATED_BY := l_user_id;
l_sampling_events.LAST_UPDATE_DATE := l_date;
IF NOT GMD_SAMPLING_EVENTS_PVT.insert_row (
p_sampling_events => l_sampling_events,
x_sampling_events => l_sampling_events_out) THEN
RAISE FND_API.G_EXC_ERROR;
ELSE -- WE need to update the SE table.
--dbms_output.put_line('WE need to update the SE table. ');
UPDATE GMD_SAMPLING_EVENTS
SET SAMPLE_TAKEN_CNT = sample_taken_cnt + 1,
SAMPLE_ACTIVE_CNT = sample_active_cnt + l_sample_active_cnt ,
ARCHIVED_TAKEN = NVL(ARCHIVED_TAKEN, 0) + l_sampling_events.archived_taken ,
RESERVED_TAKEN = NVL(RESERVED_TAKEN, 0) + l_sampling_events.reserved_taken ,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = l_date
WHERE SAMPLING_EVENT_ID = l_sampling_events.sampling_event_id;
SELECT sample_taken_cnt INTO l_qc_samples_rec.sample_instance
FROM GMD_SAMPLING_EVENTS
WHERE SAMPLING_EVENT_ID = l_sampling_events.sampling_event_id;
l_qc_samples_rec.delete_mark := 0;
l_qc_samples_rec.last_update_date := l_date;
IF NOT GMD_SAMPLES_PVT.insert_row (
p_samples => l_qc_samples_rec,
x_samples => l_qc_samples_out_rec) THEN
RAISE FND_API.G_EXC_ERROR;
UPDATE gmd_samples
SET SAMPLE_INV_TRANS_IND = l_sample_inv_trans_ind -- Bug # 4619570
WHERE sample_id = l_qc_samples_out_rec.sample_id ;
-- need to update source whse back to samples since insert of sample has already taken place.
UPDATE GMD_SAMPLES
SET source_subinventory = l_qc_samples_out_rec.source_subinventory
WHERE sample_id = l_qc_samples_out_rec.sample_id ;
SELECT exact_spec_match_ind
FROM gmd_quality_config
WHERE organization_id = p_org_id;
PROCEDURE DELETE_SAMPLES
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, p_qc_samples_rec IN GMD_SAMPLES%ROWTYPE
, p_user_name IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SAMPLES';
SAVEPOINT DELETE_SAMPLES;
IF l_qc_samples_out_rec.delete_mark = 1 THEN
GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
'l_table_name', 'GMD_SAMPLES',
'l_column_name', 'SAMPLE_ID',
'l_key_value', l_qc_samples_out_rec.sample_id);
IF NOT GMD_SAMPLES_PVT.delete_row(
p_sample_id => l_qc_samples_out_rec.sample_id,
p_organization_id => l_qc_samples_out_rec.organization_id,
p_sample_no => l_qc_samples_out_rec.sample_no
) THEN
GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
'l_table_name', 'GMD_SAMPLES',
'l_column_name','SAMPLE_ID',
'l_key_value', l_qc_samples_out_rec.sample_id);
UPDATE GMD_SAMPLING_EVENTS
SET SAMPLE_ACTIVE_CNT = sample_active_cnt -1,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = SYSDATE
WHERE SAMPLING_EVENT_ID = l_qc_samples_out_rec.sampling_event_id;
ROLLBACK TO DELETE_SAMPLES;
ROLLBACK TO DELETE_SAMPLES;
ROLLBACK TO DELETE_SAMPLES;
END DELETE_SAMPLES;
SELECT 1
FROM mtl_lot_numbers
WHERE inventory_item_id = p_sample_rec.inventory_item_id
--AND lot_number = p_sample_rec.lot_number
AND organization_id = p_sample_rec.organization_id
AND ((p_sample_rec.parent_lot_number IS NULL )
OR (parent_lot_number = p_sample_rec.parent_lot_number));*/
SELECT 1
FROM mtl_lot_numbers
WHERE inventory_item_id = p_sample_rec.inventory_item_id
--AND lot_number = p_sample_rec.lot_number
AND((p_child_lot_flag = 'Y' and p_sample_rec.lot_number IS NOT NULL and lot_number = p_sample_rec.lot_number)
OR (p_child_lot_flag = 'Y' and p_sample_rec.lot_number IS NULL)
OR (p_child_lot_flag = 'N' and lot_number = p_sample_rec.lot_number))
AND organization_id = p_sample_rec.organization_id
AND ((p_sample_rec.parent_lot_number IS NULL )
OR (parent_lot_number = p_sample_rec.parent_lot_number));
SELECT 1
FROM mtl_item_locations
WHERE organization_id = p_sample_rec.organization_id
AND inventory_location_id = p_sample_rec.locator_id
AND subinventory_code = p_sample_rec.subinventory;
SELECT DISTINCT gr.recipe_id,
ffm.formula_id,
rout.routing_id
FROM gme_batch_header bh
, gme_material_details md
, gmd_recipes_b gr/*gmd_recipes gr bug#4916871*/
, gmd_recipe_validity_rules rvr
, gmd_status gs
, fm_matl_dtl fmd
, fm_form_mst_b ffm /*fm_form_mst ffm bug# 4916871*/
, gmd_routings_b rout /*gmd_routings rout bug#4916871*/
, gem_lookups gl
, gem_lookups gl2
WHERE rout.routing_id(+) = bh.routing_id
AND rvr.recipe_validity_rule_id = bh.recipe_validity_rule_id
AND rvr.recipe_id = gr.recipe_id
AND ffm.formula_id = bh.formula_id
AND ffm.formula_id = fmd.formula_id
AND fmd.formula_id = bh.formula_id
AND ffm.delete_mark = 0
AND fmd.formula_id = gr.formula_id
AND fmd.inventory_item_id = p_sample_rec.inventory_item_id
AND gr.recipe_status = gs.status_code
AND gs.status_code <> '1000'
AND gr.delete_mark = 0
AND gr.formula_id = bh.formula_id
AND bh.batch_id = md.batch_id
AND bh.batch_type = 0
AND ( ( bh.batch_status IN (2, 3) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') ) /*-- wip or completed */
OR ( bh.batch_status IN (2, 3,4 ) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) /*-- 4619570 wip or completed or closed */
AND md.inventory_item_id = p_sample_rec.inventory_item_id /*--batch must be for item*/
--AND bh.plant_code = p_sample_rec.organization_id -- bug 5291723 - plant code replace with organization
AND bh.organization_id = p_sample_rec.organization_id -- bug 5291723
AND bh.batch_status = gl.lookup_code
AND gl.lookup_type = 'GME_BATCH_STATUS'
AND gl2.lookup_type = 'GME_YES_NO'
AND gl2.lookup_code = bh.terminated_ind
AND bh.batch_id = p_sample_rec.batch_id
AND NVL( p_sample_rec.recipe_id, gr.recipe_id) = gr.recipe_id
AND NVL( p_sample_rec.formula_id, bh.formula_id) = bh.formula_id
AND ((p_sample_rec.routing_id IS NULL) OR (p_sample_rec.routing_id = bh.routing_id)) ;
SELECT 1
FROM fm_matl_dtl fmd
WHERE fmd.inventory_item_id = p_sample_rec.inventory_item_id
AND fmd.formula_id = l_formula_id
AND fmd.formulaline_id = p_sample_rec.formulaline_id;
SELECT 1
FROM gme_material_details
WHERE inventory_item_id = p_sample_rec.inventory_item_id
AND organization_id = p_sample_rec.organization_id
AND batch_id = l_batch_id
AND material_detail_id = p_sample_rec.material_detail_id;
SELECT bs.batchstep_no, bs.oprn_id
FROM gme_batch_steps bs,
gmd_operations o
WHERE bs.oprn_id = o.oprn_id
AND bs.batchstep_id = p_sample_rec.step_id
AND bs.batch_id = p_sample_rec.batch_id
AND NVL( p_sample_rec.step_no, bs.batchstep_no) = bs.batchstep_no
AND o.delete_mark = 0
AND bs.delete_mark = 0;
SELECT 1
FROM gmd_operations o
WHERE o.delete_mark = 0
AND o.oprn_id = p_sample_rec.oprn_id;
SELECT csua.org_id
FROM hr_operating_units ou
, hz_cust_acct_sites_all casa
, hz_cust_site_uses_all csua
, hz_parties hzp
, hz_cust_accounts_all hzca
WHERE ou.organization_id = csua.org_id
AND casa.cust_acct_site_id = csua.cust_acct_site_id
AND casa.cust_account_id = hzca.cust_account_id
AND casa.org_id = csua.org_id
AND hzp.party_id = hzca.party_id
AND NVL( p_sample_rec.org_id, csua.org_id) = csua.org_id
AND hzca.cust_account_id = p_sample_rec.cust_id;
SELECT 1
FROM hz_cust_acct_sites_all casa
, hz_cust_site_uses_all csua
, hz_cust_accounts_all caa
WHERE casa.cust_acct_site_id = csua.cust_acct_site_id
AND casa.org_id = csua.org_id
AND casa.cust_account_id = caa.cust_account_id
AND csua.site_use_code = 'SHIP_TO'
AND NVL( p_sample_rec.org_id, csua.org_id) = csua.org_id
AND caa.cust_account_id = p_sample_rec.cust_id
AND csua.site_use_id = p_sample_rec.ship_to_site_id;
SELECT 1
FROM oe_order_headers_all oha
, oe_order_lines_all oola
, oe_transaction_types_tl ttt
WHERE oola.header_id = oha.header_id
AND oola.inventory_item_id IN
(SELECT msi.inventory_item_id
FROM mtl_system_items msi
WHERE msi.segment1 IN
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = p_sample_rec.inventory_item_id))
AND oha.order_type_id = ttt.transaction_type_id
AND NVL( p_sample_rec.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
AND NVL( p_sample_rec.organization_id, oha.org_id) = oha.org_id
AND p_sample_rec.cust_id = oha.sold_to_org_id
AND oha.header_id = p_sample_rec.order_id
AND oha.cancelled_flag <> 'Y'
AND ttt.language = USERENV('LANG');*/
SELECT 1
FROM oe_order_headers_all oha
, oe_order_lines_all oola
, oe_transaction_types_tl ttt
WHERE oola.header_id = oha.header_id
AND oola.inventory_item_id = p_sample_rec.inventory_item_id
AND oha.order_type_id = ttt.transaction_type_id
AND (NVL( p_sample_rec.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id -- 5335008
OR NVL( p_sample_rec.ship_to_site_id, oola.invoice_to_org_id) = oola.invoice_to_org_id) -- 5335008
AND NVL( p_sample_rec.org_id, oha.org_id) = oha.org_id -- 5335008
AND NVL( p_sample_rec.cust_id,oha.sold_to_org_id) = oha.sold_to_org_id -- 5335008
AND oha.header_id = p_sample_rec.order_id
AND oha.cancelled_flag <> 'Y'
AND ttt.language = USERENV('LANG');
SELECT 1
FROM oe_order_lines_all oola
WHERE oola.header_id = p_sample_rec.order_id
AND NVL( p_sample_rec.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
AND oola.inventory_item_id IN
(SELECT msi.inventory_item_id
FROM mtl_system_items msi
WHERE msi.segment1 IN
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = p_sample_rec.inventory_item_id))
AND oola.header_id = p_sample_rec.order_id
AND oola.line_id = p_sample_rec.order_line_id;
SELECT 1
FROM po_vendors v
WHERE v.vendor_id = p_sample_rec.supplier_id
AND v.enabled_flag = 'Y';
SELECT 1
FROM po_vendor_sites_all v
WHERE (v.purchasing_site_flag = 'Y'
OR v.rfq_only_site_flag = 'Y')
AND sysdate < NVL(inactive_date, sysdate + 1)
AND v.vendor_id = p_sample_rec.supplier_id
AND v.vendor_site_id = p_sample_rec.supplier_site_id;
SELECT 1
FROM po_headers_all pha
WHERE pha.po_header_id IN
(SELECT pla.po_header_id
FROM po_lines_all pla
WHERE pla.po_header_id = pha.po_header_id
AND pla.item_id IN
(SELECT msi.inventory_item_id
FROM mtl_system_items_b msi
WHERE organization_id = p_sample_rec.organization_id
AND inventory_item_id = p_sample_rec.inventory_item_id))
AND pha.vendor_id = p_sample_rec.supplier_id
AND pha.vendor_site_id = p_sample_rec.supplier_site_id
AND pha.po_header_id = p_sample_rec.po_header_id;
SELECT 1
FROM po_lines_all pla
WHERE pla.item_id IN
(SELECT msi.inventory_item_id
FROM mtl_system_items msi
WHERE msi.segment1 IN
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = p_sample_rec.inventory_item_id))
AND pla.po_header_id = p_sample_rec.po_header_id
AND pla.po_line_id = p_sample_rec.po_line_id;
SELECT 1
FROM rcv_shipment_headers rsh , rcv_transactions rt
WHERE (p_sample_rec.receipt_id, p_sample_rec.receipt_line_id) IN
(SELECT rsh.shipment_header_id,
rsl.shipment_line_id -- rt.transaction_id
FROM rcv_shipment_lines rsl
WHERE rsl.po_header_id = p_sample_rec.po_header_id
AND rsl.item_id IN
(SELECT msi.inventory_item_id
FROM mtl_system_items msi
WHERE msi.segment1 IN
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = p_sample_rec.inventory_item_id))
AND rsl.po_line_id = p_sample_rec.po_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id);*/
SELECT 1
FROM rcv_shipment_headers rsh
WHERE (p_sample_rec.receipt_id, p_sample_rec.receipt_line_id) IN
(SELECT rsh.shipment_header_id,
rsl.shipment_line_id
FROM rcv_shipment_lines rsl
WHERE rsl.po_header_id = p_sample_rec.po_header_id
AND rsl.item_id = p_sample_rec.inventory_item_id
AND rsl.po_line_id = p_sample_rec.po_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id);
SELECT 1
FROM cr_rsrc_mst
WHERE delete_mark = 0
and resources = p_sample_rec.resources ;
SELECT 1
FROM mtl_item_locations
WHERE organization_id = p_sample_rec.organization_id
AND inventory_location_id = p_sample_rec.locator_id
AND subinventory_code = p_sample_rec.subinventory;
SELECT 1
FROM mtl_parameters m,
gmd_quality_config g
WHERE g.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
WHERE g.organization_id = m.organization_id
AND g.quality_lab_ind = 'Y'
AND m.organization_id = p_organization_id
AND m. process_enabled_flag = 'Y' ;
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_rec.organization_id
AND s.secondary_inventory_name = p_sample_rec.subinventory;
SELECT 1
FROM mtl_item_locations
WHERE subinventory_code = p_sample_rec.subinventory
AND organization_id = p_sample_rec.organization_id
AND inventory_location_id = p_sample_rec.locator_id;
SELECT PRIMARY_UOM_CODE
FROM mtl_system_items_b
WHERE organization_id = p_sample_rec.organization_id
AND inventory_item_id = p_sample_rec.inventory_item_id;
SELECT 1
FROM mtl_item_locations
WHERE subinventory_code = p_sample_rec.storage_subinventory
AND organization_id = p_sample_rec.storage_organization_id
AND inventory_location_id = p_sample_rec.storage_locator_id;
p_operation => 'INSERT',
x_return_status => l_return_status
);