DBA Data[Home] [Help]

APPS.GMD_AUTO_SAMPLE_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 30

		   --select nvl(item_desc1, '')
		   --from ic_item_mst
		   --where item_id = x_item_id_in ;
Line: 33

                SELECT  nvl(description, '')
                FROM     mtl_system_items_b_kfv
                WHERE    organization_id     = x_sampling_event.organization_id
                  AND    inventory_item_id   = x_inventory_item_id;
Line: 39

		select nvl(sample_cnt_req, 0) sample_cnt_req,
			nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
			nvl(ARCHIVE_CNT_REQ,0) archive_cnt_req,
			nvl(sample_qty, 0) sample_qty, sample_qty_uom,
			nvl(RESERVE_QTY, 0) reserve_qty,
			nvl(ARCHIVE_QTY,0) archive_qty
		from gmd_sampling_plans_b sm
		where sm.sampling_plan_id = x_sampling_plan_id ;
Line: 50

	/*	select nvl (SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
		from gmd_all_spec_vrs
		where spec_vr_id = x_spec_vr_id ; */
Line: 53

SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_INVENTORY_SPEC_VRS V ,
     GMD_SPECIFICATIONS_B S ,
     MTL_SYSTEM_ITEMS_KFV I ,
     GMD_QC_STATUS_TL T ,
     GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
  AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
  AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
  AND V.SPEC_VR_STATUS = T.STATUS_CODE
  AND T.ENTITY_TYPE = 'S'
  AND T.LANGUAGE = USERENV ( 'LANG' )
  AND S.SPEC_STATUS = P.STATUS_CODE
  AND P.ENTITY_TYPE = 'S'
  AND P.LANGUAGE = USERENV ( 'LANG' )
  and v.spec_vr_id = x_spec_vr_id
UNION
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_WIP_SPEC_VRS V ,
     GMD_SPECIFICATIONS_B S ,
     MTL_SYSTEM_ITEMS_KFV I ,
     GMD_QC_STATUS_TL T ,
     GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
  AND I.ORGANIZATION_ID = S.OWNER_ORGANIZATION_ID
  AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
  AND V.SPEC_VR_STATUS = T.STATUS_CODE
  AND T.ENTITY_TYPE = 'S'
  AND T.LANGUAGE = USERENV ( 'LANG' )
  AND S.SPEC_STATUS = P.STATUS_CODE
  AND P.ENTITY_TYPE = 'S'
  AND P.LANGUAGE = USERENV ( 'LANG' )
  and v.spec_vr_id = x_spec_vr_id
UNION
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_CUSTOMER_SPEC_VRS V ,
     GMD_SPECIFICATIONS_B S ,
     MTL_SYSTEM_ITEMS_KFV I ,
     GMD_QC_STATUS_TL T ,
     GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
  AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
  AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
  AND V.SPEC_VR_STATUS = T.STATUS_CODE
  AND T.ENTITY_TYPE = 'S'
  AND T.LANGUAGE = USERENV ( 'LANG' )
  AND S.SPEC_STATUS = P.STATUS_CODE --  NEW
 AND P.ENTITY_TYPE = 'S' --  NEW
AND P.LANGUAGE = USERENV ( 'LANG' ) --  NEW
and v.spec_vr_id = x_spec_vr_id
UNION
SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
FROM GMD_SUPPLIER_SPEC_VRS V ,
     GMD_SPECIFICATIONS_B S ,
     MTL_SYSTEM_ITEMS_KFV I ,
     GMD_QC_STATUS_TL T ,
     GMD_QC_STATUS_TL P
WHERE V.SPEC_ID = S.SPEC_ID
  AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
  AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
  AND V.SPEC_VR_STATUS = T.STATUS_CODE
  AND T.ENTITY_TYPE = 'S'
  AND T.LANGUAGE = USERENV ( 'LANG' )
  AND S.SPEC_STATUS = P.STATUS_CODE
  AND P.ENTITY_TYPE = 'S'
  AND P.LANGUAGE = USERENV ( 'LANG' )
  and v.spec_vr_id = x_spec_vr_id
UNION
SELECT 'N' SAMPLE_INV_TRANS_IND
FROM GMD_MONITORING_SPEC_VRS V ,
     GMD_SPECIFICATIONS_B S ,
     GMD_QC_STATUS_TL P ,
     GMD_QC_STATUS_TL T
WHERE V.SPEC_ID = S.SPEC_ID
  AND V.SPEC_VR_STATUS = T.STATUS_CODE
  AND T.ENTITY_TYPE = 'S'
  AND T.LANGUAGE = USERENV ( 'LANG' )
  AND S.SPEC_STATUS = P.STATUS_CODE
  AND P.ENTITY_TYPE = 'S'
  AND P.LANGUAGE = USERENV ( 'LANG' )
  and v.spec_vr_id = x_spec_vr_id
UNION
SELECT 'N' SAMPLE_INV_TRANS_IND
FROM GMD_STABILITY_SPEC_VRS V ,
     GMD_SPECIFICATIONS_B S ,
     GMD_QC_STATUS_TL T ,
     GMD_QC_STATUS_TL L
WHERE V.SPEC_ID = S.SPEC_ID
  AND V.SPEC_VR_STATUS = T.STATUS_CODE
  AND S.SPEC_STATUS = L.STATUS_CODE
  AND T.ENTITY_TYPE = 'S'
  AND L.ENTITY_TYPE = 'S'
  AND T.LANGUAGE = USERENV ( 'LANG' )
  AND L.LANGUAGE = USERENV ( 'LANG' )
  and v.spec_vr_id = x_spec_vr_id;
Line: 151

	/* Update created sampling event */
	p_event_spec_disp.sampling_event_id := x_sampling_event.sampling_event_id;
Line: 158

	p_event_spec_disp.DELETE_MARK := 0;
Line: 161

	p_event_spec_disp.LAST_UPDATE_DATE := sysdate;
Line: 162

	p_event_spec_disp.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 165

        IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row(
      		   p_event_spec_disp =>p_event_spec_disp,
                   x_event_spec_disp =>x_event_spec_disp) THEN
               RAISE FND_API.G_EXC_ERROR;
Line: 205

        p_sample.delete_mark        := 0;
Line: 208

        p_sample.last_update_date   := sysdate;
Line: 209

        p_sample.last_updated_by    := FND_GLOBAL.USER_ID;
Line: 258

		IF not GMD_SAMPLES_PVT.insert_row (
                                 p_sample,  x_sample )      THEN
                           raise fnd_api.g_exc_error;
Line: 266

                p_sample_spec_disp.delete_mark        := 0;
Line: 269

                p_sample_spec_disp.last_update_date   := sysdate;
Line: 270

                p_sample_spec_disp.last_updated_by    := FND_GLOBAL.USER_ID;
Line: 272

		IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row  (
                            p_sample_spec_disp  )    THEN
	                          raise fnd_api.g_exc_error;
Line: 292

                p_sample.delete_mark        := 0;
Line: 295

                p_sample.last_update_date   := sysdate;
Line: 296

                p_sample.last_updated_by    := FND_GLOBAL.USER_ID;
Line: 348

		IF not GMD_SAMPLES_PVT.insert_row (
                                 p_sample,  x_sample )      THEN
                           raise fnd_api.g_exc_error;
Line: 357

		IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row  (
                            p_sample_spec_disp  )    THEN
	                          raise fnd_api.g_exc_error;
Line: 376

                p_sample.delete_mark        := 0;
Line: 379

                p_sample.last_update_date   := sysdate;
Line: 380

                p_sample.last_updated_by    := FND_GLOBAL.USER_ID;
Line: 432

		IF not GMD_SAMPLES_PVT.insert_row (
                                 p_sample,  x_sample )      THEN
                           raise fnd_api.g_exc_error;
Line: 441

		IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row  (
                            p_sample_spec_disp  )    THEN
	            raise fnd_api.g_exc_error;
Line: 447

	/* Update the sampling event samples taken */
	update gmd_sampling_events
	set  SAMPLE_TAKEN_CNT =  0
	where sampling_event_id = x_sampling_event.sampling_event_id ;
Line: 452

	update gmd_sampling_events
	set  ARCHIVED_TAKEN =  0
	where sampling_event_id = x_sampling_event.sampling_event_id ;
Line: 456

	update gmd_sampling_events
	set  RESERVED_TAKEN =  0
	where sampling_event_id = x_sampling_event.sampling_event_id ;
Line: 460

	update gmd_sampling_events
	set  SAMPLE_REQ_CNT =  l_sample_cnt_req
	where sampling_event_id = x_sampling_event.sampling_event_id ;
Line: 464

	/* Update the sampling event disposition to Planned */
	update gmd_sampling_events
	set  disposition = '0PL'
	where sampling_event_id = x_sampling_event.sampling_event_id ;