DBA Data[Home] [Help]

APPS.AHL_ENIGMA_ROUTE_OP_PUB SQL Statements

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

Line: 133

	SELECT  RT.ENIGMA_ROUTE_ID,
		RT.ATA_CODE,
		RT.PDF_FILE_NAME,
		RT.ENIGMA_DOC_ID,
		RT.DML_OPERATION,
		RT.REVISION_DATE,
		RT.CONTEXT,
		RT.PUBLISH_DATE,
		RT.DESCRIPTION
	FROM	AHL_ENIGMA_RT_INTERFACE RT
	WHERE	ISRECOVERABLE = 'Y';
Line: 149

	SELECT	OP.ENIGMA_OP_ID,
		OP.ATA_CODE,
		OP.ENIGMA_DOC_ID,
		OP.DML_OPERATION,
		OP.OPERATOR,
		OP.EQUIPMENT,
		OP.OEM_DOC_TYPE,
		OP.CHAPTER_SECTION_SUBJECT,
		OP.AMTOSS_FUNCTION,
		OP.OP_SEQ,
		OP.CONFIG_LETTER,
		OP.PUBLISH_DATE,
		OP.CONTEXT,
		OP.DESCRIPTION
	FROM	AHL_ENIGMA_OP_INTERFACE OP
	WHERE   ISRECOVERABLE = 'Y';
Line: 229

	SELECT sysdate INTO timebefore from dual;
Line: 265

	DELETE FROM AHL_ENIGMA_OP_INTERFACE
	WHERE ISRECOVERABLE = 'Y'
	AND   PROCESS_DATE <= timebefore;
Line: 269

	SELECT sysdate INTO timebefore from dual;
Line: 300

	DELETE FROM AHL_ENIGMA_RT_INTERFACE
	WHERE ISRECOVERABLE = 'Y'
	AND   PROCESS_DATE <= timebefore;
Line: 353

		-- Delete all the routes in pending status from the stagin table
		-- as they have already been queried by the cursor
		-- moving this code out side of loop

		-- 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_ENIGMA_ROUTE_ID = l_enigma_route_id AND STATUS = 'PENDING';
Line: 409

	-- Delete the pending route records processed
	DELETE FROM AHL_RT_OPER_INTERFACE
	WHERE STATUS = 'PENDING' AND PARENT_enigma_route_id IS NULL;*/
Line: 644

PROCEDURE Update_Route_Interface_table
(
	p_enigma_route_rec	IN enigma_route_rec_type,
	p_context		IN VARCHAR2,
	p_pub_date		IN DATE,
	p_isrecoverable		IN VARCHAR2,
	p_reason		IN VARCHAR2
)
IS
l_debug_module  CONSTANT      VARCHAR2(100)     := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'Update_Route_Interface_table';
Line: 657

		fnd_log.string(fnd_log.level_statement,l_debug_module,'Before Inserting into interface table');
Line: 661

	INSERT INTO AHL_ENIGMA_RT_INTERFACE
	(
		CONTEXT,
		PUBLISH_DATE,
		ENIGMA_ROUTE_ID,
		PDF_FILE_NAME,
		REVISION_DATE,
		DML_OPERATION,
		ATA_CODE,
		DESCRIPTION,
		ERROR_MESSAGE,
		ENIGMA_DOC_ID,
		PROCESS_DATE,
		ISRECOVERABLE
	)
	VALUES
	(
		p_context,
		p_pub_date,
		p_enigma_route_rec.enigma_route_id,
		p_enigma_route_rec.pdf_file_name,
		p_enigma_route_rec.revision_date,
		p_enigma_route_rec.dml_operation,
		p_enigma_route_rec.ata_code,
		p_enigma_route_rec.description,
		p_reason,
		p_enigma_route_rec.enigma_doc_id,
		sysdate,
		p_isrecoverable
	);
Line: 692

		fnd_log.string(fnd_log.level_statement,l_debug_module,'After Inserting into interface table');
Line: 694

END Update_Route_Interface_table;
Line: 708

SELECT route_operation_id,operation_id,object_version_number
FROM ahl_route_operations
WHERE route_id = p_route_id;
Line: 714

SELECT operation_id
FROM ahl_operations_b
WHERE enigma_op_id = p_enigma_op_id
AND TRUNC(NVL(end_date_active, sysdate + 1)) > TRUNC(sysdate)
AND (revision_status_code = 'COMPLETE' OR revision_status_code='DRAFT') ;
Line: 722

SELECT NVL(max(step),0)
FROM ahl_route_operations
WHERE route_id = p_route_id;
Line: 764

	-- change = 0 : Delete
	-- change = 1 : Retained
	-- change = 2 : New Addition

	-- Get the existing associations details created through enigma.
	l_old_ctr := 1;
Line: 808

	-- Determine which associations are to be retained,which to be deleted and which are new associations
        l_upper_bound := old_op_assos_table.COUNT;
Line: 838

		--populating the record for delete
		IF (old_op_assos_table(l_old_ctr).change = 0) THEN
			l_route_op_asso_table(l_ctr).route_operation_id	        :=old_op_assos_table(l_old_ctr).assoc_id;
Line: 924

SELECT	route_id,object_version_number,revision_status_code revision_status
FROM	AHL_ROUTES_B
WHERE	UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_enigma_route_id))
AND REVISION_NUMBER =(
        SELECT MAX( revision_number )
        FROM AHL_ROUTES_B
	WHERE UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_enigma_route_id)));
Line: 936

 SELECT count(*)
 FROM AHL_ROUTES_B
 WHERE ENIGMA_ROUTE_ID = p_enigma_route_id;
Line: 1028

	--Functionality:IF DML_OPERATION = 'NC' Then Only pdf will be updated for the route.
	--The Pvt API,AHL_RM_ROUTE_PVT.process_route expects DML_OPERATION To be VARCHAR2(1),
	--So from this public wrapper,DML_OPERATION will be passed as 'N' to that api
	IF (p_enigma_route_rec.DML_OPERATION = 'NC') THEN
		-- 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
			FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
Line: 1037

			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'Y',
				'Route is in approval pending status'
			);*/
Line: 1072

			-- 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,
				 p_module_type,--NULL Sthilak for bug #14036337,,
				 x_return_status,
				 x_msg_count,
				 x_msg_data,
				 p_process_route_input_rec
			);
Line: 1087

				 fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling process_route in update mode x_return_status -> ' || x_return_status);
Line: 1094

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1105

			-- Insert the transaction record into the staging table, with status as success
			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'SUCCESS',
				'Route updated Successfully'
			);*/
Line: 1116

	-- Check if the flag id delete and if so delete the route from the CMRO system .
	IF (p_enigma_route_rec.DML_OPERATION = 'D') THEN
		-- 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
			FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
Line: 1123

			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'Y',
				'Route is in approval pending status'
			);*/
Line: 1132

			-- 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,
				 p_module_type,--'ENIGMA' Sthilak for bug #14036337,,
				 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: 1149

			    fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling delete_routes..Return Status: ' || x_return_status);
Line: 1155

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

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1171

		-- 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
			FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
Line: 1176

			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'Y',
				'Route is in approval pending status'
			);*/
Line: 1192

					INSERT INTO AHL_RT_OPER_INTERFACE
					(
						CONTEXT,
						PUBLISH_DATE,
						ENIGMA_OPERATION_ID,
						PARENT_ENIGMA_ROUTE_ID,
						DML_OPERATION,
						ATA_CODE,
						DESCRIPTION,
						STATUS,
						REASON,
						ENIGMA_DOC_ID
					)
					VALUES
					(
						p_context,
						p_pub_date,
						p_enigma_route_rec.op_asso_tbl(i),
						p_enigma_route_rec.enigma_route_id,
						NULL,--p_enigma_op_tbl(i).DML_OPERATION,
						NULL,--p_enigma_op_tbl(i).ata_code,
						NULL,--p_enigma_op_tbl(i).description,
						'PENDING',
						'Parent Route is in approval pending status',
						NULL--p_enigma_op_tbl(i).ENIGMA_DOC_ID
					);
Line: 1246

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1258

			SELECT object_version_number INTO l_ovn from
                        AHL_ROUTES_B where route_id = x_route_id;
Line: 1273

				fnd_log.string(fnd_log.level_statement,l_debug_module,'Before calling process_route in update mode');
Line: 1276

			-- 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,
				 p_module_type,--NULL Sthilak for bug #14036337,,
				 x_return_status,
				 x_msg_count,
				 x_msg_data,
				 p_process_route_input_rec
			);
Line: 1292

				 fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling process_route in update mode x_return_status -> ' || x_return_status);
Line: 1299

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1310

			-- Insert the transaction record into the staging table, with status as success
			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'SUCCESS',
				'Route updated Successfully'
			);*/
Line: 1334

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1358

			-- 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,
				 p_module_type,--NULL Sthilak for bug #14036337,,
				 x_return_status,
				 x_msg_count,
				 x_msg_data,
				 p_process_route_input_rec
			);
Line: 1381

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1392

			/*Update_Route_Interface_table(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'SUCCESS',
				'Route updated Successfully'
			);*/
Line: 1413

				/*Update_Route_Interface_table
				(
					p_enigma_route_rec,
					p_context,
					p_pub_date,
					'N',
					x_msg_data
				);*/
Line: 1459

			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'N',
				x_msg_data
			);*/
Line: 1504

			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'N',
				x_msg_data
			);*/
Line: 1516

		/*Update_Route_Interface_table
		(
			p_enigma_route_rec,
			p_context,
			p_pub_date,
			'SUCCESS',
			'Route Created Successfully'
		);*/
Line: 1538

			/*Update_Route_Interface_table
			(
				p_enigma_route_rec,
				p_context,
				p_pub_date,
				'N',
				x_msg_data
			);*/
Line: 1578

	Update_Route_Interface_table
	(
		p_enigma_route_rec,
		p_context,
		p_pub_date,
		isrecoverable,
		x_msg_data
	);
Line: 1594

	Update_Route_Interface_table
	(
		p_enigma_route_rec,
		p_context,
		p_pub_date,
		isrecoverable,
		x_msg_data
	);
Line: 1616

	Update_Route_Interface_table
	(
		p_enigma_route_rec,
		p_context,
		p_pub_date,
		isrecoverable,
		x_msg_data
	);
Line: 1626

PROCEDURE Update_Op_Interface_table
(
	p_enigma_op_rec		IN enigma_op_rec_type,
	p_context		IN VARCHAR2,
	p_pub_date		IN DATE,
	p_isrecoverable		IN VARCHAR2,
	p_reason		IN VARCHAR2
)
IS
l_debug_module  CONSTANT      VARCHAR2(100)     := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'Update_Op_Interface_table';
Line: 1643

	INSERT INTO AHL_ENIGMA_OP_INTERFACE
	(
		ENIGMA_OP_ID,
		ATA_CODE,
		ENIGMA_DOC_ID,
		DML_OPERATION,
		OPERATOR,
		EQUIPMENT,
		OEM_DOC_TYPE,
		CHAPTER_SECTION_SUBJECT,
		AMTOSS_FUNCTION,
		OP_SEQ,
		CONFIG_LETTER,
		PUBLISH_DATE,
		PROCESS_DATE,
		CONTEXT,
		DESCRIPTION,
		ERROR_MESSAGE,
		ISRECOVERABLE
	)
	VALUES
	(
		p_enigma_op_rec.enigma_op_id,
		p_enigma_op_rec.ata_code,
		p_enigma_op_rec.enigma_doc_id,
		p_enigma_op_rec.dml_operation,
		p_enigma_op_rec.operator,
		p_enigma_op_rec.equipment,
		p_enigma_op_rec.oem_doc_type,
		p_enigma_op_rec.chapter_section_subject,
		p_enigma_op_rec.amtoss_function,
		p_enigma_op_rec.op_seq,
		p_enigma_op_rec.config_letter,
		p_pub_date,
		sysdate,
		p_context,
		p_enigma_op_rec.description,
		p_reason,
		p_isrecoverable
	);
Line: 1684

End Update_Op_Interface_table;
Line: 1706

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

SELECT aop.object_version_number
FROM ahl_operations_b aop
WHERE aop.operation_id = c_oper_id;
Line: 1878

				SELECT AHL_ENIGMA_OP_SEQ_S.NEXTVAL
				INTO   l_op_sq
				FROM   DUAL;
Line: 1921

			-- 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,
				 p_module_type,--NULL Sthilak for bug #14036337,,
				 x_return_status,
				 x_msg_count,
				 x_msg_data,
				 p_process_oper_input_rec
			);
Line: 1963

			    fnd_log.string(fnd_log.level_statement,l_debug_module,'before inserting the operations into staging table ');
Line: 1966

			-- Insert the transaction record into the staging table, with status as success
			OP_interface_insert_row(
						X_CONTEXT => p_context,
						X_PUBLISH_DATE => p_pub_date,
						X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
						X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
						X_DESCRIPTION => p_enigma_op_rec.description,
						X_STATUS => 'SUCCESS',
						X_REASON => 'Operation created successfully',
						X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
					);*/
Line: 2037

				-- 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
					FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_APR_PENDING');
Line: 2043

					/*OP_interface_insert_row(
						X_CONTEXT => p_context,
						X_PUBLISH_DATE => p_pub_date,
						X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
						X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
						X_DESCRIPTION => p_enigma_op_rec.description,
						X_STATUS => 'PENDING',
						X_REASON => 'Operation is in approval pending status',
						X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
					);*/
Line: 2120

					-- 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,
						 p_module_type,--NULL Sthilak for bug #14036337,,
						 x_return_status,
						 x_msg_count,
						 x_msg_data,
						 p_process_oper_input_rec
						);
Line: 2156

					    fnd_log.string(fnd_log.level_statement,l_debug_module,'Update Operation-> Draft');
Line: 2197

					-- 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,
						 p_module_type,--NULL Sthilak for bug #14036337,,
						 x_return_status,
						 x_msg_count,
						 x_msg_data,
						 p_process_oper_input_rec
						);
Line: 2229

					    fnd_log.string(fnd_log.level_statement,l_debug_module,'Before inserting into the staging table');
Line: 2233

				-- Insert the transaction record into the staging table, with status as success
				/*OP_interface_insert_row(
						X_CONTEXT => p_context,
						X_PUBLISH_DATE => p_pub_date,
						X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
						X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
						X_DESCRIPTION => p_enigma_op_rec.description,
						X_STATUS => 'SUCCESS',
						X_REASON => 'Operation updated successfully',
						X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
					);*/
Line: 2247

				   fnd_log.string(fnd_log.level_statement,l_debug_module,'After inserting into the staging table');
Line: 2282

				    fnd_log.string(fnd_log.level_statement,l_debug_module,'Delete :Cursor Found :Operation Exist ');
Line: 2285

				-- IF the operation is in Approval Pending status , then insert the operation record into the staging table with status as pending.

				-- This table is going to be restructured and based on new table this part of code will be modified.
				IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
					FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_APR_PENDING');
Line: 2292

					/*OP_interface_insert_row(
						X_CONTEXT => p_context,
						X_PUBLISH_DATE => p_pub_date,
						X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
						X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
						X_DESCRIPTION => p_enigma_op_rec.description,
						X_STATUS => 'PENDING',
						X_REASON => 'Operation is in Pending Status',
						X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
					);*/
Line: 2305

					    fnd_log.string(fnd_log.level_statement,l_debug_module,'Inside else , calling delete operation');
Line: 2308

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

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

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

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

	Update_OP_Interface_table
	(
		p_enigma_op_rec,
		p_context,
		p_pub_date,
		isrecoverable,
		x_msg_data
	);
Line: 2394

	Update_OP_Interface_table
	(
		p_enigma_op_rec,
		p_context,
		p_pub_date,
		isrecoverable,
		x_msg_data
	);
Line: 2416

	Update_OP_Interface_table
	(
		p_enigma_op_rec,
		p_context,
		p_pub_date,
		isrecoverable,
		x_msg_data
	);
Line: 2483

  select fnd_lobs_s.nextval into seqNo from dual;
Line: 2485

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