eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

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


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


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


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

Line: 373


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

Line: 391


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


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

Line: 475


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


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


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


	DELETE FROM GMO_BATCH_STEP_LOCK_DETAILS WHERE BATCHSTEP_ID = P_BATCHSTEP_ID;

Line: 614


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


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


		DELETE FROM GMO_BATCH_STEP_LOCK_DETAILS WHERE BATCHSTEP_ID = l_batchstep_id;

Line: 692


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

Line: 693


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

Line: 694


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

Line: 695


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

Line: 784


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

Line: 785


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

Line: 786


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

Line: 871


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


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


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

Line: 915


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


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

Line: 918


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


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

Line: 921


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


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

Line: 924


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


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

Line: 954


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


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


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


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


END update_task_attribute;

Line: 1145


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


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


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


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


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


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


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


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


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


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

Line: 1555


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


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


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


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


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


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


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


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


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

Line: 1569


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


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


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

Line: 1652


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


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


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


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


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

Line: 2069


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

Line: 2071


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

Line: 2073


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

Line: 2076


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

Line: 2077


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

Line: 2079


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

Line: 2081


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

Line: 2267


		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_MATERIAL) THEN

			l_material_detail_id := P_TASK_ATTRIBUTE;

Line: 2288


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


		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_ACTIVITY) THEN

			l_batchstep_activity_id := P_TASK_ATTRIBUTE;

Line: 2482


		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_RESOURCE) THEN

			l_batchstep_resource_id := P_TASK_ATTRIBUTE;

Line: 2499


		ELSIF (P_TASK = GMO_CONSTANTS_GRP.ACTION_DELETE_MATERIAL) THEN

			l_material_detail_id := P_TASK_ATTRIBUTE;

Line: 2698


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

Line: 2699


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


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

Line: 2702


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

Line: 2703


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

Line: 2705


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

Line: 2865


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


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


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


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


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


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


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


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


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


		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
		);