APPS.AHL_ENIGMA_ROUTE_OP_PUB SQL Statements

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

Line: 89

	SELECT 'X'
	FROM AHL_RT_OPER_INTERFACE
	WHERE ROUTE_ID = p_route_id
	AND STATUS = 'PENDING';*/

Line: 97

	SELECT
		ROUTE_ID,
		STATUS,
		ATA_CODE,
		DESCRIPTION,
		REVISION_DATE,
		ENIGMA_ID,
		CHANGE_FLAG,
		PDF
	FROM
		AHL_RT_OPER_INTERFACE
	WHERE
		STATUS = 'PENDING'
	AND	PARENT_ROUTE_ID IS NULL;

Line: 116

	SELECT
		OPERATION_ID,
		STATUS,
		ATA_CODE,
		DESCRIPTION,
		PARENT_ROUTE_ID,
		ENIGMA_ID,
		CHANGE_FLAG
	FROM
		AHL_RT_OPER_INTERFACE
	WHERE PARENT_ROUTE_ID = p_route_id
	AND STATUS = 'PENDING';

Line: 218

		-- Delete all the routes in pending status from the stagin table
		-- as they have already been queried by the cursor
		DELETE FROM AHL_RT_OPER_INTERFACE
		WHERE STATUS = 'PENDING' AND PARENT_ROUTE_ID IS NULL;

Line: 223

		-- Delete all the operations from the stagin table that are in the pending status
		-- and that correspond to the parent route

		DELETE FROM AHL_RT_OPER_INTERFACE
		WHERE PARENT_ROUTE_ID = l_route_id AND STATUS = 'PENDING';

Line: 340

	-- staging table and the operation is not delete ?!?!
	PROCESS_ROUTE_DETAILS
	(
		'1.0',
		FND_API.G_TRUE,
		FND_API.G_FALSE,
		FND_API.G_VALID_LEVEL_FULL,
		FND_API.G_FALSE,
		NULL,
		x_return_status,
		x_msg_count,
		x_msg_data,
		p_enigma_route_rec,
		p_enigma_op_tbl,
		p_context,
		p_pub_date
	);

Line: 428

	SELECT
		route_id,
		object_version_number,
		revision_status_code revision_status
	FROM	AHL_ROUTES_V
	WHERE	UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_route_id))
	AND REVISION_NUMBER =
	(	SELECT
			MAX( revision_number )
		FROM
			AHL_ROUTES_V
		WHERE
			UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_route_id)) );

Line: 541

	-- Check if the flag id delete and if so delete the route from the CMRO system .
	IF (p_enigma_route_rec.change_flag = 'D') THEN

		IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
		THEN
		    fnd_log.string
		    (
		      fnd_log.level_statement,
		      l_debug_module,
		      'Inside Delete'
		    );

Line: 573

			-- IF the route is in Approval Pending status , then insert the Enigma Record into the
			-- staging table with status as pending.
			IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN

				IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
				THEN
				    fnd_log.string
				    (
				      fnd_log.level_statement,
				      l_debug_module,
				      'Route is in Approval Pending'
				    );

Line: 589

				      'Before Inserting into the staging table'
				    );

Line: 593

				INSERT INTO AHL_RT_OPER_INTERFACE
				(
					CONTEXT,
					PUBLISH_DATE,
					ROUTE_ID,
					PDF,
					REVISION_DATE,
					CHANGE_FLAG,
					ATA_CODE,
					DESCRIPTION,
					STATUS,
					REASON,
					ENIGMA_ID
				)
				VALUES
				(
					p_context,
					p_pub_date,
					p_enigma_route_rec.route_id,
					p_enigma_route_rec.pdf,
					p_enigma_route_rec.revision_date,
					p_enigma_route_rec.change_flag,
					p_enigma_route_rec.ata_code,
					p_enigma_route_rec.description,
					'PENDING',
					'Route is in approval pending status',
					p_enigma_route_rec.enigma_id
				);

Line: 628

				      'After Inserting into the staging table'
				    );

Line: 639

				      'Before calling delete_routes'
				    );

Line: 655

				-- Call delete_route procedure to delete the route from CMRO End.
				AHL_RM_ROUTE_PVT.delete_route
					(
					 '1.0',
					 FND_API.G_TRUE,
					 FND_API.G_FALSE,
					 FND_API.G_VALID_LEVEL_FULL,
					 FND_API.G_FALSE,
					 'ENIGMA',
					 x_return_status,
					 x_msg_count,
					 x_msg_data,
					 l_get_latest_route_rev.route_id,
					 l_get_latest_route_rev.object_version_number
					);

Line: 677

				      'After calling delete_routes'
				    );

Line: 694

						fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.delete_route Error');

Line: 700

					fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.delete_route -> Deleted');

Line: 722

		      'Inside update'
		    );

Line: 770

					INSERT INTO AHL_RT_OPER_INTERFACE
					(
						CONTEXT,
						PUBLISH_DATE,
						ROUTE_ID,
						PDF,
						REVISION_DATE,
						CHANGE_FLAG,
						ATA_CODE,
						DESCRIPTION,
						STATUS,
						REASON,
						ENIGMA_ID
					)
					VALUES
					(
						p_context,
						p_pub_date,
						p_enigma_route_rec.route_id,
						p_enigma_route_rec.pdf,
						p_enigma_route_rec.revision_date,
						p_enigma_route_rec.change_flag,
						p_enigma_route_rec.ata_code,
						p_enigma_route_rec.description,
						'PENDING',
						'Route is in approval pending status',
						p_enigma_route_rec.enigma_id
					);

Line: 821

							      'Inserting the operations into the staging table'
							    );

Line: 825

							INSERT INTO AHL_RT_OPER_INTERFACE
							(
								CONTEXT,
								PUBLISH_DATE,
								OPERATION_ID,
								PARENT_ROUTE_ID,
								CHANGE_FLAG,
								ATA_CODE,
								DESCRIPTION,
								STATUS,
								REASON,
								ENIGMA_ID
							)
							VALUES
							(
								p_context,
								p_pub_date,
								p_enigma_op_tbl(i).operation_id,
								p_enigma_op_tbl(i).parent_route_id,
								p_enigma_op_tbl(i).change_flag,
								p_enigma_op_tbl(i).ata_code,
								p_enigma_op_tbl(i).description,
								'PENDING',
								'Parent Route is in approval pending status',
								p_enigma_op_tbl(i).enigma_id
							);

Line: 992

						-- Update the route revision with file_id
						/*
						UPDATE
						  AHL_ROUTES_B
						SET
						  FILE_ID = x_file_id,
						  OBJECT_VERSION_NUMBER = l_get_latest_route_rev.object_version_number + 1
						WHERE
						   ROUTE_ID = l_get_latest_route_rev.route_id
						   AND OBJECT_VERSION_NUMBER = l_get_latest_route_rev.object_version_number;

Line: 1005

					SELECT object_version_number INTO l_ovn from
					AHL_ROUTES_V where route_id = x_route_id;

Line: 1051

					      'Before calling process_route in update mode'
					    );

Line: 1055

					-- Call the API for update
					AHL_RM_ROUTE_PVT.process_route
						(
						 '1.0',
						 FND_API.G_TRUE,
						 FND_API.G_FALSE,
						 FND_API.G_VALID_LEVEL_FULL,
						 FND_API.G_FALSE,
						 NULL,
						 x_return_status,
						 x_msg_count,
						 x_msg_data,
						 p_process_route_input_rec
						);

Line: 1077

					      'After calling process_route in update mode'
					    );

Line: 1109

					      'before insertion into staging table'
					    );

Line: 1113

					-- Insert the transaction record into the staging table, with status as success
					INSERT INTO AHL_RT_OPER_INTERFACE
					(
						CONTEXT,
						PUBLISH_DATE,
						ROUTE_ID,
						PDF,
						REVISION_DATE,
						CHANGE_FLAG,
						ATA_CODE,
						DESCRIPTION,
						STATUS,
						REASON,
						ENIGMA_ID
					)
					VALUES
					(
						p_context,
						p_pub_date,
						p_enigma_route_rec.route_id,
						p_enigma_route_rec.pdf,
						p_enigma_route_rec.revision_date,
						p_enigma_route_rec.change_flag,
						p_enigma_route_rec.ata_code,
						p_enigma_route_rec.description,
						'SUCCESS',
						'Route updated Successfully',
						p_enigma_route_rec.enigma_id
					);

Line: 1150

					      'After insertion into staging table'
					    );

Line: 1369

					-- Call the API for update.
					AHL_RM_ROUTE_PVT.process_route
						(
						 '1.0',
						 FND_API.G_TRUE,
						 FND_API.G_FALSE,
						 FND_API.G_VALID_LEVEL_FULL,
						 FND_API.G_FALSE,
						 NULL,
						 x_return_status,
						 x_msg_count,
						 x_msg_data,
						 p_process_route_input_rec
						);

Line: 1407

					INSERT INTO AHL_RT_OPER_INTERFACE
					(
						CONTEXT,
						PUBLISH_DATE,
						ROUTE_ID,
						PDF,
						REVISION_DATE,
						CHANGE_FLAG,
						ATA_CODE,
						DESCRIPTION,
						STATUS,
						REASON,
						ENIGMA_ID
					)
					VALUES
					(
						p_context,
						p_pub_date,
						p_enigma_route_rec.route_id,
						p_enigma_route_rec.pdf,
						p_enigma_route_rec.revision_date,
						p_enigma_route_rec.change_flag,
						p_enigma_route_rec.ata_code,
						p_enigma_route_rec.description,
						'SUCCESS',
						'Route updates Successfully',
						p_enigma_route_rec.enigma_id
					);

Line: 1753

			-- Call the API for update.
			AHL_RM_ROUTE_PVT.process_route
				(
				 '1.0',
				 FND_API.G_TRUE,
				 FND_API.G_FALSE,
				 FND_API.G_VALID_LEVEL_FULL,
				 FND_API.G_FALSE,
				 NULL,
				 x_return_status,
				 x_msg_count,
				 x_msg_data,
				 p_process_route_input_rec
				);

Line: 1797

			      'Before inserting into the staging table'
			    );

Line: 1802

			INSERT INTO AHL_RT_OPER_INTERFACE
			(
				CONTEXT,
				PUBLISH_DATE,
				ROUTE_ID,
				PDF,
				REVISION_DATE,
				CHANGE_FLAG,
				ATA_CODE,
				DESCRIPTION,
				STATUS,
				REASON,
				ENIGMA_ID
			)
			VALUES
			(
				p_context,
				p_pub_date,
				p_enigma_route_rec.route_id,
				p_enigma_route_rec.pdf,
				p_enigma_route_rec.revision_date,
				p_enigma_route_rec.change_flag,
				p_enigma_route_rec.ata_code,
				p_enigma_route_rec.description,
				'SUCCESS',
				'Route created Successfully',
				p_enigma_route_rec.enigma_id
			);

Line: 1838

			      'After inserting into the staging table'
			    );

Line: 1960

	SELECT
		operation_id,
		object_version_number,
		revision_status_code revision_status
	FROM	AHL_OPERATIONS_B
	WHERE	UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_operation_id))
	AND REVISION_NUMBER =
	(	SELECT
			MAX( revision_number )
		FROM
			AHL_OPERATIONS_B
		WHERE
			UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_operation_id))
	);

Line: 2167

					-- IF the operation is in Approval Pending status , then insert the operation record into the
					-- staging table with status as pending.
					IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
						INSERT INTO AHL_RT_OPER_INTERFACE
						(
							CONTEXT,
							PUBLISH_DATE,
							OPERATION_ID,
							PARENT_ROUTE_ID,
							CHANGE_FLAG,
							ATA_CODE,
							DESCRIPTION,
							STATUS,
							REASON,
							ENIGMA_ID
						)
						VALUES
						(
							p_context,
							p_pub_date,
							p_enigma_op_tbl(i).operation_id,
							parent_route_id,
							p_enigma_op_tbl(i).change_flag,
							p_enigma_op_tbl(i).ata_code,
							p_enigma_op_tbl(i).description,
							'PENDING',
							'Operation is in approval pending status',
							p_enigma_op_tbl(i).enigma_id
						);

Line: 2204

						      'Inside else , calling delete operation'
						    );

Line: 2208

						-- Call the delete operation API
						AHL_RM_OPERATION_PVT.delete_operation
						(
							1.0,
							FND_API.G_TRUE,
							FND_API.G_FALSE,
							FND_API.G_VALID_LEVEL_FULL,
							FND_API.G_FALSE,
							NULL,
							x_return_status,
							x_msg_count,
							x_msg_data,
							l_get_latest_oper_rev.operation_id,
							l_get_latest_oper_rev.object_version_number
						);

Line: 2225

							fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.delete_operation');

Line: 2230

								fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation Error');

Line: 2236

							fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation -> deletion Successful');

Line: 2311

					-- If the operation is in Approval Pending status , then insert the operation record into the
					-- staging table with status as pending.
					IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
						INSERT INTO AHL_RT_OPER_INTERFACE
						(
							CONTEXT,
							PUBLISH_DATE,
							OPERATION_ID,
							PARENT_ROUTE_ID,
							CHANGE_FLAG,
							ATA_CODE,
							DESCRIPTION,
							STATUS,
							REASON,
							ENIGMA_ID
						)
						VALUES
						(
							p_context,
							p_pub_date,
							p_enigma_op_tbl(i).operation_id,
							parent_route_id,
							p_enigma_op_tbl(i).change_flag,
							p_enigma_op_tbl(i).ata_code,
							p_enigma_op_tbl(i).description,
							'PENDING',
							'Operation is in approval pending status',
							p_enigma_op_tbl(i).enigma_id
						);

Line: 2426

						-- Call the API for update
						AHL_RM_OPERATION_PVT.process_operation
							(
							 '1.0',
							 FND_API.G_TRUE,
							 FND_API.G_FALSE,
							 FND_API.G_VALID_LEVEL_FULL,
							 FND_API.G_FALSE,
							 NULL,
							 x_return_status,
							 x_msg_count,
							 x_msg_data,
							 p_process_oper_input_rec
							);

Line: 2456

						-- Insert the transaction record into the staging table, with status as success
						INSERT INTO AHL_RT_OPER_INTERFACE
						(
							CONTEXT,
							PUBLISH_DATE,
							OPERATION_ID,
							PARENT_ROUTE_ID,
							CHANGE_FLAG,
							ATA_CODE,
							DESCRIPTION,
							STATUS,
							REASON,
							ENIGMA_ID
						)
						VALUES
						(
							p_context,
							p_pub_date,
							p_enigma_op_tbl(i).operation_id,
							parent_route_id,
							p_enigma_op_tbl(i).change_flag,
							p_enigma_op_tbl(i).ata_code,
							p_enigma_op_tbl(i).description,
							'SUCCESS',
							'Operation updated successfully',
							p_enigma_op_tbl(i).enigma_id
						);

Line: 2495

						      'Update Operation-> Draft'
						    );

Line: 2606

						-- Call the API for update
						AHL_RM_OPERATION_PVT.process_operation
							(
							 '1.0',
							 FND_API.G_TRUE,
							 FND_API.G_FALSE,
							 FND_API.G_VALID_LEVEL_FULL,
							 FND_API.G_FALSE,
							 NULL,
							 x_return_status,
							 x_msg_count,
							 x_msg_data,
							 p_process_oper_input_rec
							);

Line: 2643

						      'Before inserting into the staging table'
						    );

Line: 2647

						-- Insert the transaction record into the staging table, with status as success
						INSERT INTO AHL_RT_OPER_INTERFACE
						(
							CONTEXT,
							PUBLISH_DATE,
							OPERATION_ID,
							PARENT_ROUTE_ID,
							CHANGE_FLAG,
							ATA_CODE,
							DESCRIPTION,
							STATUS,
							REASON,
							ENIGMA_ID
						)
						VALUES
						(
							p_context,
							p_pub_date,
							p_enigma_op_tbl(i).operation_id,
							parent_route_id,
							p_enigma_op_tbl(i).change_flag,
							p_enigma_op_tbl(i).ata_code,
							p_enigma_op_tbl(i).description,
							'SUCCESS',
							'Operation updated successfully',
							p_enigma_op_tbl(i).enigma_id
						);

Line: 2685

					      'After inserting into the staging table'
					    );

Line: 2747

				-- Call the API for update
				AHL_RM_OPERATION_PVT.process_operation
				(
					 '1.0',
					 FND_API.G_TRUE,
					 FND_API.G_FALSE,
					 FND_API.G_VALID_LEVEL_FULL,
					 FND_API.G_FALSE,
					 NULL,
					 x_return_status,
					 x_msg_count,
					 x_msg_data,
					 p_process_oper_input_rec
				);

Line: 2810

				      'before inserting the operations into staging table '
				    );

Line: 2814

				-- Insert the transaction record into the staging table, with status as success
				INSERT INTO AHL_RT_OPER_INTERFACE
				(
					CONTEXT,
					PUBLISH_DATE,
					OPERATION_ID,
					PARENT_ROUTE_ID,
					CHANGE_FLAG,
					ATA_CODE,
					DESCRIPTION,
					STATUS,
					REASON,
					ENIGMA_ID
				)
				VALUES
				(
					p_context,
					p_pub_date,
					p_enigma_op_tbl(i).operation_id,
					parent_route_id,
					p_enigma_op_tbl(i).change_flag,
					p_enigma_op_tbl(i).ata_code,
					p_enigma_op_tbl(i).description,
					'SUCCESS',
					'Operation created successfully',
					p_enigma_op_tbl(i).enigma_id
				);

Line: 2844

				SELECT route_id INTO p_route_id FROM AHL_ROUTES_B
				WHERE ENIGMA_ROUTE_ID = parent_route_id;

Line: 3047

  select fnd_lobs_s.nextval into seqNo from dual;

Line: 3049

  insert into fnd_lobs(
	     file_id,
	     file_name,
	     file_content_type,
	     language,
	     file_data,
	     file_format,
	     upload_date
	    )
   values(seqNo,
	  p_file_name,
	  'application/octet-stream',
	  'US',
	  l_contentBlob,
	  'binary',
	  sysdate
   );