APPS.GMO_VBATCH_PVT SQL Statements

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

Line: 14

cursor is_enhanced_pi is select count(*) from gme_batch_header where enhanced_pi_ind = 'Y' and batch_id = l_batch_id;

Line: 54

cursor is_step_locked_by_user is select count(*) from gmo_batch_step_lock_details where batchstep_id = p_batchstep_id and lock_requester = p_requester;

Line: 55

cursor is_step_locked is select count(*) from gmo_batch_step_lock_details where batchstep_id = p_batchstep_id;

Line: 131

	select dtl_um into l_detail_uom from gme_material_details where material_detail_id = P_MATERIAL_DETAIL_ID;

Line: 224

    SELECT transaction_quantity
    FROM mtl_material_transactions_temp
    WHERE inventory_item_id=cp_inventory_item_id
    AND trx_source_line_id= cp_material_detail_id
    AND transaction_source_type_id=5 ;

Line: 233

      SELECT actual_qty ,line_type
      INTO l_actual_quantity,l_line_type
      FROM gme_material_details
      WHERE material_detail_id=P_MATERIAL_DETAIL_ID
      AND inventory_item_id= P_INVENTORY_ITEM_ID;

Line: 298

cursor c_get_batch_details is select a.batch_id, a.organization_id from gme_batch_header a, gme_batch_steps b where a.batch_id = b.batch_id and b.batchstep_id = p_batchstep_id;

Line: 299

cursor c_is_material_reqd_for_step is select count(*) from gme_batch_step_items where material_detail_id = l_material_detail_id and batchstep_id = p_batchstep_id;

Line: 394

cursor c_get_step_lock_by is select lock_requester from gmo_batch_step_lock_details where batchstep_id = p_batchstep_id;

Line: 418

cursor c_get_batch_id is select batch_id from gme_batch_steps where batchstep_id = P_BATCHSTEP_ID;

Line: 436

	INSERT into GMO_BATCH_STEP_LOCK_DETAILS (BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
	VALUES (l_batch_id, P_BATCHSTEP_ID, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);

Line: 497

cursor c_get_batch_id is select batch_id from gme_batch_steps where batchstep_id = l_batchstep_id;

Line: 520

		INSERT into GMO_BATCH_STEP_LOCK_DETAILS (BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
		VALUES (l_batch_id, l_batchstep_id, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);

Line: 586

cursor c_get_lock_details is select batch_id, lock_requester,lock_date from gmo_batch_step_lock_details where batchstep_id = P_BATCHSTEP_ID;

Line: 603

	INSERT into GMO_BATCH_STEP_LOCK_HIST (LOCK_HIST_SEQ, BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, UNLOCK_REQUESTER, UNLOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
	VALUES (GMO_BATCH_STEP_LOCK_HIST_S.NEXTVAL, l_batch_id, P_BATCHSTEP_ID, l_lock_requester, l_lock_date, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);

Line: 606

	DELETE FROM GMO_BATCH_STEP_LOCK_DETAILS WHERE BATCHSTEP_ID = P_BATCHSTEP_ID;

Line: 659

cursor c_get_lock_details is select batch_id, lock_requester,lock_date from gmo_batch_step_lock_details where batchstep_id = l_batchstep_id;

Line: 681

		INSERT into GMO_BATCH_STEP_LOCK_HIST (LOCK_HIST_SEQ, BATCH_ID, BATCHSTEP_ID, LOCK_REQUESTER, LOCK_DATE, UNLOCK_REQUESTER, UNLOCK_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
		VALUES (GMO_BATCH_STEP_LOCK_HIST_S.NEXTVAL, l_batch_id, l_batchstep_id, l_lock_requester, l_lock_date, P_REQUESTER, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.USER_ID, sysdate, FND_GLOBAL.LOGIN_ID);

Line: 684

		DELETE FROM GMO_BATCH_STEP_LOCK_DETAILS WHERE BATCHSTEP_ID = l_batchstep_id;

Line: 737

cursor get_batch_id_for_step is select batch_id from gme_batch_steps where batchstep_id = p_entity_key;

Line: 738

cursor get_batch_id_for_resource is select batch_id from gme_batch_step_resources where batchstep_resource_id = p_entity_key;

Line: 739

cursor get_batch_id_for_activity is select batch_id from gme_batch_step_activities where batchstep_activity_id = p_entity_key;

Line: 740

cursor get_batch_id_for_material is select batch_id from gme_material_details where material_detail_id = p_entity_key;

Line: 829

cursor get_batchstep_id_for_resource is select batchstep_id from gme_batch_step_resources where batchstep_resource_id = p_entity_key;

Line: 830

cursor get_batchstep_id_for_activity is select batchstep_id from gme_batch_step_activities where batchstep_activity_id = p_entity_key;

Line: 831

cursor get_batchstep_id_for_material is select batchstep_id from gme_batch_step_items where material_detail_id = p_entity_key;

Line: 916

procedure update_task_attribute
(
P_ENTITY_NAME IN VARCHAR2,
P_ENTITY_KEY IN VARCHAR2,
P_INSTRUCTION_SET_ID IN NUMBER,
X_ATTRIBUTE_STATUS OUT NOCOPY VARCHAR2,
X_INVALID_INSTR OUT NOCOPY FND_TABLE_OF_VARCHAR2_255
)

IS

l_batch_id number;

Line: 957

cursor get_activity is select batchstep_activity_id from gme_batch_step_activities where batchstep_id = l_batchstep_id and oprn_line_id = l_oprn_line_id;

Line: 958

cursor get_min_activity is select min(batchstep_activity_id) from gme_batch_step_activities  where batchstep_id = l_batchstep_id;

Line: 960

cursor get_resource is select batchstep_resource_id from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id and resources = l_resources;

Line: 961

cursor get_min_resource is select min(batchstep_resource_id) from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id;

Line: 963

cursor get_param is select process_param_id from gme_process_parameters where batchstep_resource_id = l_batchstep_resource_id and parameter_id = l_param_id;

Line: 964

cursor get_min_param is select (process_param_id) from gme_process_parameters where batchstep_resource_id = l_batchstep_resource_id;

Line: 966

cursor get_material is select material_detail_id from gme_material_details where formulaline_id = l_formula_line_id and batch_id = l_batch_id;

Line: 967

cursor get_min_material_for_step is select min(material_detail_id) from gme_batch_step_items where batchstep_id = l_batchstep_id;

Line: 969

cursor get_instr_details is select instr_number, instruction_id, task_id, task_attribute_id from gmo_instr_instance_b where instruction_set_id = P_INSTRUCTION_SET_ID;

Line: 971

cursor get_task is select task_name from gmo_instr_task_defn_b where task_id = l_task_id;

Line: 999

		IF (L_TASK = 'UPDATE_ACTIVITY' or L_TASK='VIEW_ACTIVITY') then
			IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) then
				l_batchstep_id := to_number(p_entity_key);

Line: 1017

		ELSIF (L_TASK = 'UPDATE_RESOURCE' or L_TASK='VIEW_RESOURCE' or L_TASK =  GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION) then

			IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY ) THEN
		                -- pattern = OprnLineId$Resources
				l_oprn_line_id := to_number (substr(l_task_attribute, 1, instr (l_task_attribute, '$') - 1));

Line: 1093

		ELSIF (L_TASK = 'MATERIAL' OR L_TASK = 'RESERVATIONS' OR L_TASK = 'VIEW_MATERIAL' OR L_TASK = 'UPDATE_MATERIAL' OR L_TASK='MATERIAL_TRANSACTIONS' OR L_TASK = 'QUALITY') THEN

			IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_ACTIVITY
				or P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_RESOURCE) THEN

				GET_BATCH_ID_FOR_ENTITY(P_ENTITY_NAME => P_ENTITY_NAME,
				   			P_ENTITY_KEY => P_ENTITY_KEY,
				   			X_BATCH_ID => l_batch_id,
				   			X_RETURN_STATUS => l_return_status,
							X_MSG_COUNT => l_msg_count,
							X_MSG_DATA => l_msg_data);

Line: 1132

				update gmo_instr_instance_b set task_attribute_id = l_task_attribute_id_inst
				where instruction_set_id = P_INSTRUCTION_SET_ID
				and instruction_id = l_instruction_id;

Line: 1151

END update_task_attribute;

Line: 1190

  select grv.recipe_id
  from   gme_batch_header gbh,
         gmd_recipe_validity_rules grv
  where gbh.recipe_validity_rule_id = grv.recipe_validity_rule_id and gbh.batch_id = p_entity_key;

Line: 1196

  select
    batchstep_id,
    routingstep_id,
    a.oprn_id,
    batchstep_no || '-' || b.oprn_no
  from gme_batch_steps a, gmd_operations_b b where  a.oprn_id = b.oprn_id and batch_id = p_entity_key;

Line: 1204

  select batchstep_activity_id,
        routingstep_id,
        oprn_line_id ,
	activity
  from  gme_batch_step_activities gbsa,
        gme_batch_steps gbs
  where gbsa.batchstep_id = gbs.batchstep_id and gbsa.batch_id = p_entity_key;

Line: 1213

  select batchstep_resource_id,
         routingstep_id,
         oprn_line_id,
         resources ,
         gbsa.activity || '-' || resources
  from   gme_batch_step_resources gbsr,
         gme_batch_step_Activities gbsa,
         gme_batch_steps gbs
  where  gbsa.batchstep_activity_id = gbsr.batchstep_activity_id
  and    gbsa.batchstep_id = gbs.batchstep_id and gbsr.batch_id = p_entity_key;

Line: 1225

  select a.material_detail_id, a.formulaline_id, a.inventory_item_id, b.concatenated_segments from gme_material_details a, mtl_system_items_kfv b
  where a.organization_id = b.organization_id
  and a.inventory_item_id = b.inventory_item_id
  and a.batch_id = p_entity_key;

Line: 1284

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
			P_ENTITY_KEY =>  l_batchstep_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);

Line: 1337

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
			P_ENTITY_KEY =>  l_batchstep_activity_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);

Line: 1390

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
			P_ENTITY_KEY =>  l_batchstep_resource_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);

Line: 1443

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
			P_ENTITY_KEY =>  l_material_detail_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);

Line: 1598

cursor get_step_detail is select batch_id, batchstep_id from gme_batch_steps where batchstep_id = l_batchstep_id;

Line: 1600

cursor get_activity_detail is select batch_id, batchstep_id, batchstep_activity_id from gme_batch_step_activities where batchstep_id = l_batchstep_id and oprn_line_id = l_oprn_line_id;

Line: 1601

cursor get_activity_detail_from_id is select batch_id, batchstep_id, batchstep_activity_id from gme_batch_step_activities where batchstep_activity_id = l_batchstep_activity_id;

Line: 1603

cursor get_resource_detail is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id and resources = l_resources;

Line: 1604

cursor get_resource_detail_from_id is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id from gme_batch_step_resources where batchstep_resource_id = l_batchstep_resource_id;

Line: 1606

cursor get_param_detail is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id, process_param_id from gme_process_parameters where batchstep_resource_id = l_batchstep_resource_id and parameter_id = l_param_id;

Line: 1607

cursor get_param_detail_from_id is select batch_id, batchstep_id, batchstep_activity_id, batchstep_resource_id, process_param_id from gme_process_parameters where process_param_id = l_process_param_id;

Line: 1610

cursor get_material_detail is select batch_id, material_detail_id, inventory_item_id, line_no, line_type from gme_material_details where formulaline_id = l_formula_line_id and batch_id = l_batch_id;

Line: 1611

cursor get_material_detail_from_id is select batch_id, material_detail_id, inventory_item_id, line_no, line_type from gme_material_details where material_detail_id = l_material_detail_id;

Line: 1612

cursor get_step_id_for_material is select batchstep_id from gme_batch_step_items where material_detail_id = l_material_detail_id;

Line: 1614

cursor get_org_detail is select a.organization_id, a.organization_code from mtl_parameters a, gme_batch_header b where a.organization_id = b.organization_id and b.batch_id = l_batch_id;

Line: 1679

	IF (P_TASK = 'VIEW_BATCH' or P_TASK = 'UPDATE_BATCH') THEN

		IF (P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_BATCH) THEN
			RAISE GMO_INVALID_TASK_ERR;

Line: 1690

		IF (P_TASK = 'UPDATE_BATCH') THEN
			X_READ_ONLY := GMO_CONSTANTS_GRP.READ_ONLY_NO;

Line: 1697

	ELSIF (P_TASK = 'VIEW_OPERATION' or P_TASK = 'UPDATE_OPERATION' or P_TASK = 'RELEASE_STEP' or P_TASK = 'CLOSE_STEP') THEN

		IF (P_ENTITY_NAME <> GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
			RAISE GMO_INVALID_TASK_ERR;

Line: 1722

	ELSIF (P_TASK = 'UPDATE_ACTIVITY' OR P_TASK = 'VIEW_ACTIVITY') THEN

		IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
			l_batchstep_id := to_number (p_entity_key);

Line: 1759

	ELSIF (P_TASK = 'UPDATE_RESOURCE' OR P_TASK = 'VIEW_RESOURCE' OR P_TASK = GMO_CONSTANTS_GRP.TASK_RESOURCE_TRANSACTION) THEN

		IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_OPERATION) THEN
			l_batchstep_id := to_number (p_entity_key);

Line: 1838

	ELSIF (P_TASK = 'MATERIAL' OR P_TASK = 'RESERVATIONS' OR P_TASK = 'VIEW_MATERIAL' OR P_TASK = 'UPDATE_MATERIAL' OR P_TASK='MATERIAL_TRANSACTIONS' OR P_TASK = 'QUALITY') THEN


		IF (P_ENTITY_NAME = GMO_CONSTANTS_GRP.ENTITY_BATCH) THEN

			l_batch_id := to_number (p_entity_key);

Line: 2112

cursor c_get_steps is select batchstep_id from gme_batch_steps where batch_id = p_entity_key;

Line: 2114

cursor c_get_activities is select batchstep_activity_id from gme_batch_step_activities where batch_id = p_entity_key;

Line: 2116

cursor c_get_resources is select batchstep_resource_id from gme_batch_step_resources where batch_id = p_entity_key;

Line: 2118

cursor c_get_materials is select material_detail_id from gme_material_details where batch_id = p_entity_key;

Line: 2121

cursor c_get_step_activities is select batchstep_activity_id from gme_batch_step_activities where batchstep_id = p_entity_key;

Line: 2122

cursor c_get_step_resources is select batchstep_resource_id from gme_batch_step_resources where batchstep_id = p_entity_key;

Line: 2124

cursor c_get_step_material is select material_detail_id from gme_batch_step_items where batchstep_id = p_entity_key;

Line: 2126

cursor c_get_activity_resources is select batchstep_resource_id from gme_batch_step_resources where batchstep_activity_id = l_batchstep_activity_id;

Line: 2312

		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_MATERIAL) THEN

			l_material_detail_id := P_TASK_ATTRIBUTE;

Line: 2333

		IF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_OPERATION OR P_TASK = GMO_CONSTANTS_GRP.ACTION_UNRELEASE) THEN

			l_batchstep_id := p_entity_key;

Line: 2488

		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_ACTIVITY) THEN

			l_batchstep_activity_id := P_TASK_ATTRIBUTE;

Line: 2527

		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_RESOURCE) THEN

			l_batchstep_resource_id := P_TASK_ATTRIBUTE;

Line: 2544

		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_MATERIAL) THEN

			l_material_detail_id := P_TASK_ATTRIBUTE;

Line: 2743

cursor is_step_locked is select count(*) from gmo_batch_step_lock_details where batchstep_id = l_batchstep_id;

Line: 2744

cursor is_step_locked_by is select count(*) from gmo_batch_step_lock_details where batchstep_id = l_batchstep_id and lock_requester = P_REQUESTER;

Line: 2746

cursor get_step_id_for_resource is select batchstep_id from gme_batch_step_resources where batchstep_resource_id = p_entity_key;

Line: 2747

cursor get_step_id_for_activity is select batchstep_id from gme_batch_step_activities where batchstep_activity_id = p_entity_key;

Line: 2748

cursor get_step_id_for_material is select batchstep_id from gme_batch_step_items where material_detail_id = p_entity_key;

Line: 2750

cursor get_step_detail is select step_status from gme_batch_steps where batchstep_id = l_batchstep_id;

Line: 2910

cursor c_get_oprn is select a.batchstep_no || '-' || oprn_no, batchstep_id from
gme_batch_steps a, gmd_operations_b b where a.oprn_id = b.oprn_id and
batchstep_id= P_BATCHSTEP_ID and batch_id = P_BATCH_ID;

Line: 2914

cursor c_get_activity is select activity, batchstep_activity_id from
gme_batch_step_activities where batchstep_id=P_BATCHSTEP_ID  and batch_id =
P_BATCH_ID;

Line: 2918

cursor c_get_resource is select resources, batchstep_resource_id from
gme_batch_step_resources where batchstep_id=P_BATCHSTEP_ID  and batch_id =
P_BATCH_ID and batchstep_activity_id = l_batchstep_activity_id;

Line: 2922

cursor c_get_material is select e.Concatenated_segments, a.material_detail_id
from gme_material_details a, gme_batch_step_items b,  MTL_SYSTEM_ITEMS_KFV e
where a.material_detail_id = b.material_detail_id
and (a.inventory_item_id = e.inventory_item_id and a.organization_id =
e.organization_id)
and b.batchstep_id = P_BATCHSTEP_ID and a.batch_id = P_BATCH_ID;

Line: 3099

  select
    batchstep_id,
    routingstep_id,
    a.oprn_id,
    batchstep_no || '-' || b.oprn_no
  from gme_batch_steps a, gmd_operations_b b where  a.oprn_id = b.oprn_id and batch_id = p_entity_key;

Line: 3107

  select batchstep_activity_id,
        routingstep_id,
        oprn_line_id ,
	activity
  from  gme_batch_step_activities gbsa,
        gme_batch_steps gbs
  where gbsa.batchstep_id = gbs.batchstep_id and gbsa.batch_id = p_entity_key;

Line: 3116

  select batchstep_resource_id,
         routingstep_id,
         oprn_line_id,
         resources ,
         gbsa.activity || '-' || resources
  from   gme_batch_step_resources gbsr,
         gme_batch_step_Activities gbsa,
         gme_batch_steps gbs
  where  gbsa.batchstep_activity_id = gbsr.batchstep_activity_id
  and    gbsa.batchstep_id = gbs.batchstep_id and gbsr.batch_id = p_entity_key;

Line: 3156

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
			P_ENTITY_KEY =>  l_batchstep_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);

Line: 3209

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
			P_ENTITY_KEY =>  l_batchstep_activity_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);

Line: 3262

		update_task_attribute
		(
			P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
			P_ENTITY_KEY =>  l_batchstep_resource_id,
			P_INSTRUCTION_SET_ID => l_instruction_set_id,
			X_ATTRIBUTE_STATUS => l_attribute_status,
			X_INVALID_INSTR => l_invalid_instr
		);