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.AHL_UMP_UNPLANNED_PVT SQL Statements

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

Line: 44


	SELECT CSI_ITEM_INSTANCE_ID, MR_HEADER_ID, DESCENDENT_COUNT
	from
	AHL_APPLICABLE_MRS
	where
	MR_HEADER_ID = c_mr_header_id and
	CSI_ITEM_INSTANCE_ID =   c_instance_id;

Line: 54


        SELECT CSI_ITEM_INSTANCE_ID, MR_HEADER_ID, RELATED_CSI_ITEM_INSTANCE_ID, RELATED_MR_HEADER_ID, UE_ID
        FROM AHL_APPLICABLE_MR_RELNS
        ORDER BY TREE_DEPTH_LEVEL;

Line: 60


        SELECT UE_ID  FROM AHL_APPLICABLE_MR_RELNS
        where RELATED_MR_HEADER_ID = c_mr_header_id
        and RELATED_CSI_ITEM_INSTANCE_ID = c_item_instance_id; --amsriniv

Line: 67


	SELECT 'X'
	FROM
	AHL_MR_HEADERS_APP_V
	where
	MR_HEADER_ID = c_mr_header_id;

Line: 76


	SELECT 'X'
	FROM
	CSI_ITEM_INSTANCES
	where
	INSTANCE_ID = c_instance_id;

Line: 84


	SELECT 'x'
	FROM DUAL
	WHERE EXISTS (SELECT r.related_mr_header_id
	              FROM ahl_mr_relationships r
                      WHERE r.mr_header_id = p_mr_header_id
	                AND EXISTS (SELECT m.mr_header_id
	                            FROM ahl_mr_headers_b M -- perf bug 6266738
	                            WHERE m.mr_header_id = r.related_mr_header_id
	                              AND (m.version_number) in (SELECT max(M1.version_number)
	                                                         from ahl_mr_headers_b M1
	                                                         where M1.title = m.title -- perf bug 6266738
	                                                           AND m1.mr_status_code = 'COMPLETE'
	                                                           AND SYSDATE between trunc(m1.effective_from)
                                                                   and trunc(nvl(m1.effective_to,SYSDATE+1))
	                                                         )
	                           )
                     );

Line: 337


       	SELECT AHL_UNIT_EFFECTIVITIES_B_S.NEXTVAL INTO l_unit_effectivity_id FROM DUAL;

Line: 339


	AHL_UNIT_EFFECTIVITIES_PKG.INSERT_ROW (
	   X_ROWID                => l_rowid,
	   X_UNIT_EFFECTIVITY_ID  => l_unit_effectivity_id,
	   X_EARLIEST_DUE_DATE    => NULL ,
	   X_LATEST_DUE_DATE      => NULL ,
	   X_ACCOMPLISHED_DATE    => NULL ,
	   X_SERVICE_LINE_ID      => NULL ,
	   X_PROGRAM_MR_HEADER_ID => NULL,
	   X_CANCEL_REASON_CODE   => NULL ,
	   X_ATTRIBUTE_CATEGORY   => NULL ,
	   X_ATTRIBUTE1           => NULL ,
	   X_ATTRIBUTE2           => NULL ,
	   X_ATTRIBUTE3           => NULL ,
	   X_ATTRIBUTE4           => NULL ,
	   X_ATTRIBUTE5           => NULL ,
	   X_ATTRIBUTE6           => NULL ,
	   X_ATTRIBUTE7           => NULL ,
	   X_ATTRIBUTE8           => NULL ,
	   X_ATTRIBUTE9           => NULL ,
	   X_ATTRIBUTE10          => NULL ,
	   X_ATTRIBUTE11          => NULL ,
	   X_ATTRIBUTE12          => NULL ,
	   X_ATTRIBUTE13          => NULL ,
	   X_ATTRIBUTE14          => NULL ,
	   X_ATTRIBUTE15          => NULL ,
	   X_OBJECT_VERSION_NUMBER => 1 ,
	   --X_CSI_ITEM_INSTANCE_ID  => l_all_appl_mrs.CSI_ITEM_INSTANCE_ID,
	   --X_MR_HEADER_ID          => l_all_appl_mrs.MR_HEADER_ID,
	   X_CSI_ITEM_INSTANCE_ID  => p_instance_id,
	   X_MR_HEADER_ID          => p_mr_header_id,
	   X_MR_EFFECTIVITY_ID     => NULL ,
	   X_MR_INTERVAL_ID        => NULL ,
	   X_STATUS_CODE           => NULL ,
	   X_DUE_DATE              => NULL ,
	   X_DUE_COUNTER_VALUE     => NULL ,
	   X_FORECAST_SEQUENCE     => NULL ,
	   X_REPETITIVE_MR_FLAG    => NULL ,
	   X_TOLERANCE_FLAG        => NULL ,
	   X_MESSAGE_CODE          => NULL ,
	   X_DATE_RUN              => NULL ,
	   X_PRECEDING_UE_ID       => NULL ,
	   X_SET_DUE_DATE          => NULL ,
	   X_REMARKS               => NULL ,
	   X_DEFER_FROM_UE_ID      => NULL ,
	   X_CS_INCIDENT_ID        => NULL ,
	   X_QA_COLLECTION_ID      => NULL ,
	   X_ORIG_DEFERRAL_UE_ID   => NULL ,
	   X_APPLICATION_USG_CODE  => l_appln_code,
	   X_OBJECT_TYPE           => 'MR',
	   X_COUNTER_ID            => NULL ,
	   X_MANUALLY_PLANNED_FLAG => 'Y',
           X_LOG_SERIES_CODE       => NULL,
           X_LOG_SERIES_NUMBER     => NULL,
           X_FLIGHT_NUMBER         => NULL,
           X_MEL_CDL_TYPE_CODE     => NULL,
           X_POSITION_PATH_ID      => NULL,
           X_ATA_CODE              => NULL,
           X_UNIT_CONFIG_HEADER_ID  => NULL,
	   X_CREATION_DATE         => sysdate,
	   X_CREATED_BY            => G_USER_ID,
	   X_LAST_UPDATE_DATE      => sysdate,
	   X_LAST_UPDATED_BY       => G_USER_ID,
	   X_LAST_UPDATE_LOGIN     => G_LOGIN_ID );

Line: 435


		     FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' BEFORE Calling AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row for Related MRs from AHL_APPLICABLE_MR_RELNS' );

Line: 441


			SELECT AHL_UNIT_EFFECTIVITIES_B_S.NEXTVAL INTO l_unit_effectivity_id FROM DUAL;

Line: 443


			AHL_UNIT_EFFECTIVITIES_PKG.INSERT_ROW (
				   X_ROWID                => l_rowid,
				   X_UNIT_EFFECTIVITY_ID  => l_unit_effectivity_id,
				   X_EARLIEST_DUE_DATE    => NULL ,
				   X_LATEST_DUE_DATE      => NULL ,
				   X_ACCOMPLISHED_DATE    => NULL ,
				   X_SERVICE_LINE_ID      => NULL ,
				   X_PROGRAM_MR_HEADER_ID => NULL,
				   X_CANCEL_REASON_CODE   => NULL ,
				   X_ATTRIBUTE_CATEGORY   => NULL ,
				   X_ATTRIBUTE1           => NULL ,
				   X_ATTRIBUTE2           => NULL ,
				   X_ATTRIBUTE3           => NULL ,
				   X_ATTRIBUTE4           => NULL ,
				   X_ATTRIBUTE5           => NULL ,
				   X_ATTRIBUTE6           => NULL ,
				   X_ATTRIBUTE7           => NULL ,
				   X_ATTRIBUTE8           => NULL ,
				   X_ATTRIBUTE9           => NULL ,
				   X_ATTRIBUTE10          => NULL ,
				   X_ATTRIBUTE11          => NULL ,
				   X_ATTRIBUTE12          => NULL ,
				   X_ATTRIBUTE13          => NULL ,
				   X_ATTRIBUTE14          => NULL ,
				   X_ATTRIBUTE15          => NULL ,
				   X_OBJECT_VERSION_NUMBER => 1 ,
				   --X_CSI_ITEM_INSTANCE_ID  => l_appl_mrs_relns.CSI_ITEM_INSTANCE_ID,
				   X_CSI_ITEM_INSTANCE_ID  => l_appl_mrs_relns.RELATED_CSI_ITEM_INSTANCE_ID,
				   X_MR_HEADER_ID          => l_appl_mrs_relns.RELATED_MR_HEADER_ID,
				   X_MR_EFFECTIVITY_ID     => NULL ,
				   X_MR_INTERVAL_ID        => NULL ,
				   X_STATUS_CODE           => NULL ,
				   X_DUE_DATE              => NULL ,
				   X_DUE_COUNTER_VALUE     => NULL ,
				   X_FORECAST_SEQUENCE     => NULL ,
				   X_REPETITIVE_MR_FLAG    => NULL ,
				   X_TOLERANCE_FLAG        => NULL ,
				   X_MESSAGE_CODE          => NULL ,
				   X_DATE_RUN              => NULL ,
				   X_PRECEDING_UE_ID       => NULL ,
				   X_SET_DUE_DATE          => NULL ,
				   X_REMARKS               => NULL ,
				   X_DEFER_FROM_UE_ID      => NULL ,
				   X_CS_INCIDENT_ID        => NULL ,
				   X_QA_COLLECTION_ID      => NULL ,
				   X_ORIG_DEFERRAL_UE_ID   => NULL ,
				   X_APPLICATION_USG_CODE  => l_appln_code,
				   X_OBJECT_TYPE           => 'MR',
				   X_COUNTER_ID            => NULL ,
				   X_MANUALLY_PLANNED_FLAG => 'Y',
                                   X_LOG_SERIES_CODE       => NULL,
                                   X_LOG_SERIES_NUMBER     => NULL,
                                   X_FLIGHT_NUMBER         => NULL,
                                   X_MEL_CDL_TYPE_CODE     => NULL,
                                   X_POSITION_PATH_ID      => NULL,
                                   X_ATA_CODE              => NULL,
                                   X_UNIT_CONFIG_HEADER_ID  => NULL,
				   X_CREATION_DATE         => sysdate,
				   X_CREATED_BY            => G_USER_ID,
				   X_LAST_UPDATE_DATE      => sysdate,
				   X_LAST_UPDATED_BY       => G_USER_ID,
				   X_LAST_UPDATE_LOGIN     => G_LOGIN_ID );

Line: 507


			-- update the table AHL_APPLICABLE_MR_RELNS with the new UE_IDs generated in AHL_UNIT_EFFECTIVITIES

			UPDATE AHL_APPLICABLE_MR_RELNS
			SET
			 UE_ID = l_unit_effectivity_id  --MANUALLY_PLANNED = 'Y'
			where CSI_ITEM_INSTANCE_ID = l_appl_mrs_relns.CSI_ITEM_INSTANCE_ID
                        and MR_HEADER_ID = l_appl_mrs_relns.MR_HEADER_ID --amsriniv
			and RELATED_CSI_ITEM_INSTANCE_ID  = l_appl_mrs_relns.RELATED_CSI_ITEM_INSTANCE_ID
			and RELATED_MR_HEADER_ID  = l_appl_mrs_relns.RELATED_MR_HEADER_ID ;

Line: 520


		     FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER Calling AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row for Related MRs from AHL_APPLICABLE_MR_RELNS' );

Line: 523


		-- to update the AHL_UE_RELATIONSHIPS table with the Unit Effectivity Relationships

		FOR l_appl_mrs_relns IN Get_Appl_Mr_Relns
		LOOP

			IF ( l_appl_mrs_relns.MR_HEADER_ID =  p_mr_header_id)
			THEN

			    l_unit_effectivity_id := x_orig_ue_id;

Line: 541


			SELECT AHL_UE_RELATIONSHIPS_S.NEXTVAL INTO l_ue_relationship_id FROM DUAL;

Line: 543


			AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW
			(
			   X_UE_RELATIONSHIP_ID    => l_ue_relationship_id,
			   X_UE_ID                 => l_unit_effectivity_id,
			   X_RELATED_UE_ID         => l_appl_mrs_relns.ue_id,
			   X_RELATIONSHIP_CODE     => 'PARENT',
			   X_ORIGINATOR_UE_ID      => x_orig_ue_id,
			   X_ATTRIBUTE_CATEGORY    => NULL,
			   X_ATTRIBUTE1            => NULL,
			   X_ATTRIBUTE2            => NULL,
			   X_ATTRIBUTE3            => NULL,
			   X_ATTRIBUTE4            => NULL,
			   X_ATTRIBUTE5            => NULL,
			   X_ATTRIBUTE6            => NULL,
			   X_ATTRIBUTE7            => NULL,
			   X_ATTRIBUTE8            => NULL,
			   X_ATTRIBUTE9            => NULL,
			   X_ATTRIBUTE10           => NULL,
			   X_ATTRIBUTE11           => NULL,
			   X_ATTRIBUTE12           => NULL,
			   X_ATTRIBUTE13           => NULL,
			   X_ATTRIBUTE14           => NULL,
			   X_ATTRIBUTE15           => NULL,
			   X_OBJECT_VERSION_NUMBER => 1,
			   X_LAST_UPDATE_DATE      => sysdate,
			   X_LAST_UPDATED_BY       => G_USER_ID,
			   X_CREATION_DATE         => sysdate,
			   X_CREATED_BY            => G_USER_ID,
			   X_LAST_UPDATE_LOGIN     => G_LOGIN_ID
			);

Line: 576


		     FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER Calling AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW for Related MRs from AHL_APPLICABLE_MR_RELNS' );

Line: 669


PROCEDURE Delete_Unit_Effectivity
(
	p_api_version		IN 		NUMBER,
	p_init_msg_list         IN              VARCHAR2  := FND_API.G_TRUE,
	p_commit                IN              VARCHAR2  := FND_API.G_FALSE,
	p_validation_level      IN              NUMBER    := FND_API.G_VALID_LEVEL_FULL,
	x_return_status       	OUT 	NOCOPY  VARCHAR2,
	x_msg_count           	OUT 	NOCOPY  NUMBER,
	x_msg_data            	OUT 	NOCOPY  VARCHAR2,
	p_unit_effectivity_id	        IN	NUMBER
)
IS

        -- Cursor definitions

        -- given the ue id get the originator ue id.
        Cursor GetOrigUeId ( c_ue_id NUMBER)
        IS
        SELECT ORIGINATOR_UE_ID
        FROM
        AHL_UE_RELATIONSHIPS
        WHERE
        RELATED_UE_ID = c_ue_id ;

Line: 697


        SELECT UE_RELATIONSHIP_ID , UE_ID , RELATED_UE_ID , ORIGINATOR_UE_ID
        FROM
        AHL_UE_RELATIONSHIPS
	START WITH UE_ID = c_ue_id
	CONNECT BY UE_ID = PRIOR RELATED_UE_ID order by RELATED_UE_ID;

Line: 706


        SELECT STATUS_CODE,OBJECT_TYPE FROM AHL_UNIT_EFFECTIVITIES_APP_V
        WHERE UNIT_EFFECTIVITY_ID = c_ue_id
        and MANUALLY_PLANNED_FLAG = 'Y';

Line: 711


	l_api_name	CONSTANT	VARCHAR2(30)	:= 'Delete_Unit_Effectivity';

Line: 728


	SAVEPOINT sp_delete_unit_effectivity;

Line: 798


			FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_CANNOT_DELETE');

Line: 805


					'Cannot delete UE'
												);

Line: 827


													-- delete the corresponding relationships records too.
													AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW( l_all_ue_ids.UE_RELATIONSHIP_ID);

Line: 829


													-- deletes all related ue ids of the originator ue id of the ue id passed for deletion.
													AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW( l_all_ue_ids.RELATED_UE_ID);

Line: 833


												FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_CANNOT_DELETE');

Line: 840


												'Cannot delete UE'
												);

Line: 856


    AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(l_originator_ue_id);

Line: 891


                Rollback to sp_delete_unit_effectivity;

Line: 909


		Rollback to sp_delete_unit_effectivity;

Line: 927


		Rollback to sp_delete_unit_effectivity;

Line: 934


				p_procedure_name 	=> 'Delete_Unit_Effectivity',
				p_error_text     	=> SUBSTR(SQLERRM,1,240)
			);

Line: 952


End Delete_Unit_Effectivity;