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: 34

                SELECT   substrb(nvl(description, ''),1,80)
                --QZENG Bug 13881118 Change view from mtl_system_items_b_kfv to mtl_system_items_vl to support NLS
                --FROM     mtl_system_items_b_kfv
                FROM mtl_system_items_vl
                WHERE    organization_id     = x_sampling_event.organization_id
                  AND    inventory_item_id   = x_inventory_item_id;
Line: 42

		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: 53

	/*	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: 56

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: 155

        fnd_global.apps_initialize (user_id   => x_sampling_event.last_updated_by,
                                              resp_id           => NULL,
                                              resp_appl_id      => NULL
                                   );
Line: 159

        fnd_profile.initialize (x_sampling_event.last_updated_by);
Line: 161

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

	p_event_spec_disp.DELETE_MARK := 0;
Line: 171

	p_event_spec_disp.LAST_UPDATE_DATE := sysdate;
Line: 172

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

        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: 215

        p_sample.delete_mark        := 0;
Line: 218

        p_sample.last_update_date   := sysdate;
Line: 219

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

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

                p_sample_spec_disp.delete_mark        := 0;
Line: 279

                p_sample_spec_disp.last_update_date   := sysdate;
Line: 280

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

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

                p_sample.delete_mark        := 0;
Line: 305

                p_sample.last_update_date   := sysdate;
Line: 306

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

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

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

                p_sample.delete_mark        := 0;
Line: 389

                p_sample.last_update_date   := sysdate;
Line: 390

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

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

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

	/* 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: 462

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

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

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

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