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

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

Line: 34


		SELECT * FROM ipa_apis_interface
		ORDER BY interface_id;

Line: 40


	    	SELECT project_id ,
			project_asset_id  , asset_name , asset_number,
		 	book_type_code     , asset_units  ,
		 	asset_category_id  , location_id  ,
		 	depreciate_flag    , depreciation_expense_ccid
		FROM  pa_project_assets_all
                WHERE   (nvl(attribute8,'!@#')  = nvl(grouping_method1,nvl(attribute8,'!@#'))
                AND      nvl(attribute9,'!@#')  = nvl(grouping_method2,nvl(attribute9,'!@#'))
                AND      nvl(attribute10,'!@#') = nvl(grouping_method3,nvl(attribute10,'!@#')))
                AND    project_id = l_project_id
		AND   capitalized_flag <> 'Y' ;

Line: 61


	    	SELECT project_id ,
			project_asset_id  , asset_name , asset_number,
		 	book_type_code     , asset_units  ,
		 	asset_category_id  , location_id  ,
		 	depreciate_flag    , depreciation_expense_ccid
		FROM  pa_project_assets_all
                WHERE   (nvl(attribute8,'!@#')  = grouping_method1
                AND      nvl(attribute9,'!@#')  = nvl(grouping_method2,nvl(attribute9,'!@#'))
                AND      nvl(attribute10,'!@#') = nvl(grouping_method3,nvl(attribute10,'!@#')))
		AND   capitalized_flag <> 'Y' ;

Line: 75


	    	SELECT project_id ,
			project_asset_id  , asset_name , asset_number,
		 	book_type_code     , asset_units  ,
		 	asset_category_id  , location_id  ,
		 	depreciate_flag    , depreciation_expense_ccid
		FROM  pa_project_assets_all
                WHERE   (nvl(attribute8,'!@#')  = nvl(grouping_method1,nvl(attribute8,'!@#'))
                AND      nvl(attribute9,'!@#')  = grouping_method2
                AND      nvl(attribute10,'!@#') = nvl(grouping_method3,nvl(attribute10,'!@#')))
		AND   capitalized_flag <> 'Y' ;

Line: 89


	    	SELECT project_id ,
			project_asset_id  , asset_name , asset_number,
		 	book_type_code     , asset_units  ,
		 	asset_category_id  , location_id  ,
		 	depreciate_flag    , depreciation_expense_ccid
		FROM  pa_project_assets_all
                WHERE   (nvl(attribute8,'!@#')  = nvl(grouping_method1,nvl(attribute8,'!@#'))
                AND      nvl(attribute9,'!@#')  = nvl(grouping_method2,nvl(attribute9,'!@#'))
                AND      nvl(attribute10,'!@#') = grouping_method3)
		AND   capitalized_flag <> 'Y' ;

Line: 101


		SELECT project_asset_id  , asset_name , asset_number ,
		 	book_type_code     , asset_units  ,
		 	asset_category_id  , location_id  ,
		 	depreciate_flag    , depreciation_expense_ccid
		FROM  pa_project_assets_all
	    	WHERE project_id        = i_project_id
		AND   capitalized_flag <> 'Y' ;

Line: 115


	-- Delete Old error records from error table
	DELETE FROM ipa_apis_interface_errors;

Line: 181


		INSERT INTO ipa_apis_interface_errors
		(  INTERFACE_ID           ,
			   BATCH_NAME             ,
			   PROJECT_ID             ,
			   PROJECT_ASSET_ID       ,
			   ERROR_MESSAGE
		)
		VALUES
			( apis_rec.interface_id 	,
			  apis_rec.batch_name		,
			  apis_rec.project_id		,
			  apis_rec.project_asset_id	,
			  x_error_msg		) ;

Line: 198


		   SELECT date_placed_in_service
		   INTO   l_earliest_fa_dpis
		   FROM	  fa_system_controls
		   WHERE  rownum < 2 ;

Line: 207


			INSERT INTO ipa_apis_interface_errors
			(  INTERFACE_ID           ,
			   BATCH_NAME             ,
			   PROJECT_ID             ,
			   PROJECT_ASSET_ID       ,
			   ERROR_MESSAGE
			)
			VALUES
			( apis_rec.interface_id 	,
			  apis_rec.batch_name		,
			  apis_rec.project_id		,
			  apis_rec.project_asset_id	,
			  'Date place in service is earlier than FA System Date Placed in service' );

Line: 226


	    		SELECT 	pp.project_id   , pp.name   ,
					pp.segment1     , pp.project_type,
					ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag ,
					pa.project_asset_id  , pa.asset_name , pa.asset_number ,
		 			pa.book_type_code     , pa.asset_units  ,
		 			pa.asset_category_id  , pa.location_id  ,
		 			pa.depreciate_flag    , pa.depreciation_expense_ccid
	    		INTO   	l_project_id , l_project_name ,
					l_project_number, l_project_type,
					l_interface_asset_cost_code , l_xface_complete_asset_flag,
					l_project_asset_id   ,l_asset_name , l_asset_number ,
		 			l_book_type_code     ,l_asset_units  ,
		 			l_asset_category_id  ,l_asset_location_id  ,
		 			l_depreciate_flag    ,l_depreciation_expense_ccid
	    		FROM   	pa_project_assets_all pa,
					pa_projects_all  pp,
					pa_project_types ppt
	    		WHERE  (    pa.project_asset_id = apis_rec.project_asset_id
			OR		pa.asset_number 	  = apis_rec.asset_number
			OR		pa.asset_name 	  = apis_rec.asset_name )
			AND   	capitalized_flag <> 'Y'
			AND		pp.project_id   = pa.project_id
			AND 		pp.project_type = ppt.project_type
                        AND             PP.org_id = ppt.org_id  ; -- Fix for bug: 4960534

Line: 252


				INSERT INTO ipa_apis_interface_errors
				(  INTERFACE_ID           ,
				   PROJECT_ID             ,
				   PROJECT_ASSET_ID       ,
				   ERROR_MESSAGE
				)
				VALUES
				( apis_rec.interface_id ,
				  l_project_id		,
				  l_project_asset_id	,
				  'NO RECORDS FOUND' 	) ;

Line: 265


			Update_dpis(apis_rec.interface_id ,
					l_project_id ,
					l_project_asset_id	 ,
					apis_rec.date_placed_in_service ,
					l_xface_complete_asset_flag ,
					l_book_type_code   ,
					l_asset_units  ,
		   	 		l_asset_category_id,
					l_asset_location_id ,
		   	 		l_depreciate_flag  ,
					l_depreciation_expense_ccid,
					apis_rec.asset_status,
                                        apis_rec.asset_units); -- added bug 9339798

Line: 284


	    		SELECT 	pp.project_id   , pp.name   ,
					pp.segment1     , pp.project_type,
					ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag
	    		INTO   	l_project_id , l_project_name ,
					l_project_number, l_project_type,
					l_interface_asset_cost_code , l_xface_complete_asset_flag
	    		FROM   	pa_projects_all  pp,
				pa_project_types ppt
	    		WHERE  	(pp.project_id  = apis_rec.project_id
	    		OR     	pp.name         = apis_rec.project_name
			OR 	      pp.segment1     = apis_rec.project_number)
			AND 		pp.project_type = ppt.project_type
                        AND             PP.org_id = ppt.org_id  ; -- Fix for bug: 4960534

Line: 301


	  			INSERT INTO ipa_apis_interface_errors
				(  INTERFACE_ID           ,
				   ERROR_MESSAGE
				)
				VALUES
				( apis_rec.interface_id   ,
				  'NO RECORDS FOUND' 	) ;

Line: 313


			-- Call update procedure

			Update_dpis(apis_rec.interface_id ,
					l_project_id ,
					asset_rec.project_asset_id	  ,
					apis_rec.date_placed_in_service ,
					l_xface_complete_asset_flag     ,
					asset_rec.book_type_code   	  ,
					asset_rec.asset_units           ,
		   	 		asset_rec.asset_category_id     ,
					asset_rec.location_id     ,
		   	 		asset_rec.depreciate_flag       ,
					asset_rec.depreciation_expense_ccid,
					apis_rec.asset_status,
                                        apis_rec.asset_units ); -- added bug 9339798

Line: 334


			  SELECT 	pp.project_id   , pp.name   ,
					  pp.segment1     , pp.project_type,
					  ppt.interface_asset_cost_code, ppt.interface_complete_asset_flag
			  INTO   	l_project_id , l_project_name ,
					  l_project_number, l_project_type,
					  l_interface_asset_cost_code , l_xface_complete_asset_flag
			  FROM   	pa_projects_all  pp,
				  pa_project_types ppt
			  WHERE  	(pp.project_id  = apis_rec.project_id
			  OR     	pp.name         = apis_rec.project_name
			  OR 	      pp.segment1     = apis_rec.project_number)
			  AND 		pp.project_type = ppt.project_type
                          AND (apis_rec.project_id is not null OR
                               apis_rec.project_name is not null OR
                               apis_rec.project_number is not null)
                         AND             PP.org_id = ppt.org_id  ; -- Fix for bug: 4960534

Line: 360


		  SELECT count(*)
                  INTO     l_count
                  FROM  pa_project_assets_all
                  WHERE   (
                       nvl(attribute8,'!@#')  = nvl(apis_rec.grouping_method1,nvl(attribute8,'!@#'))
                  AND       nvl(attribute9,'!@#')  = nvl(apis_rec.grouping_method2,nvl(attribute9,'!@#'))
                  AND       nvl(attribute10,'!@#') = nvl(apis_rec.grouping_method3,nvl(attribute10,'!@#'))
                         )
                 AND   capitalized_flag <> 'Y'
                 AND   project_id = nvl(l_project_id,project_id);

Line: 380


			-- Call update procedure.

                             l_count := l_count + 1;

Line: 383


				Update_dpis(apis_rec.interface_id 		  ,
						project_rec.project_id          ,
						project_rec.project_asset_id    ,
						apis_rec.date_placed_in_service ,
						l_xface_complete_asset_flag 	  ,
						project_rec.book_type_code      ,
						project_rec.asset_units         ,
		   	 			project_rec.asset_category_id   ,
						project_rec.location_id   	  ,
		   	 			project_rec.depreciate_flag     ,
						project_rec.depreciation_expense_ccid,
						apis_rec.asset_status,
                                                apis_rec.asset_units);  -- added bug 9339798

Line: 408


			-- Call update procedure.

                             l_count := l_count + 1;

Line: 411


				Update_dpis(apis_rec.interface_id 		  ,
						project_rec.project_id          ,
						project_rec.project_asset_id    ,
						apis_rec.date_placed_in_service ,
						l_xface_complete_asset_flag 	  ,
						project_rec.book_type_code      ,
						project_rec.asset_units         ,
		   	 			project_rec.asset_category_id   ,
						project_rec.location_id   	  ,
		   	 			project_rec.depreciate_flag     ,
						project_rec.depreciation_expense_ccid,
						apis_rec.asset_status,
                                                apis_rec.asset_units);  -- added bug 9339798

Line: 433


			-- Call update procedure.

                             l_count := l_count + 1;

Line: 436


				Update_dpis(apis_rec.interface_id 		  ,
						project_rec.project_id          ,
						project_rec.project_asset_id    ,
						apis_rec.date_placed_in_service ,
						l_xface_complete_asset_flag 	  ,
						project_rec.book_type_code      ,
						project_rec.asset_units         ,
		   	 			project_rec.asset_category_id   ,
						project_rec.location_id   	  ,
		   	 			project_rec.depreciate_flag     ,
						project_rec.depreciation_expense_ccid,
						apis_rec.asset_status,
                                                apis_rec.asset_units);  -- added bug 9339798

Line: 458


			-- Call update procedure.

                             l_count := l_count + 1;

Line: 461


				Update_dpis(apis_rec.interface_id 		  ,
						project_rec.project_id          ,
						project_rec.project_asset_id    ,
						apis_rec.date_placed_in_service ,
						l_xface_complete_asset_flag 	  ,
						project_rec.book_type_code      ,
						project_rec.asset_units         ,
		   	 			project_rec.asset_category_id   ,
						project_rec.location_id   	  ,
		   	 			project_rec.depreciate_flag     ,
						project_rec.depreciation_expense_ccid,
						apis_rec.asset_status,
                                                apis_rec.asset_units);  -- added bug 9339798

Line: 479


	  			INSERT INTO ipa_apis_interface_errors
				(  INTERFACE_ID           ,
				   ERROR_MESSAGE
				)
				VALUES
				( apis_rec.interface_id 	,
				  'NO RECORDS FOUND' 	) ;

Line: 497


procedure Update_expenditure_item
		(i_project_id 	IN 	NUMBER )
IS

CURSOR asset_cur (  i_project_id 	      NUMBER   )
IS
    	SELECT project_asset_id , date_placed_in_service
	FROM  pa_project_assets_all
    	WHERE project_id        = i_project_id
        and date_placed_in_service is not null;

Line: 522


		UPDATE 	pa_expenditure_items_all
		SET    	date_placed_in_service = asset_rec.date_placed_in_service
		WHERE  	expenditure_item_id  in (
			SELECT 	det.expenditure_item_id
			FROM	pa_project_asset_lines_all line,
				pa_project_asset_line_details det
			WHERE  	line.project_asset_id = asset_rec.project_asset_id
			AND 	line.project_asset_line_detail_id = det.project_asset_line_detail_id ) ;

Line: 532


END update_expenditure_item ;

Line: 534


PROCEDURE update_dpis ( i_interface_id 		IN   	NUMBER  ,
				i_project_id 	   		IN   	NUMBER  ,
				i_project_asset_id   		IN   	NUMBER  ,
				i_date_placed_in_service 	IN	DATE 	  ,
				i_xface_complete_asset_flag 	IN	VARCHAR2,
				i_book_type_code     		IN	VARCHAR2,
				i_asset_units        		IN	NUMBER, -- datatype changed bug 9339798
		   	 	i_asset_category_id  		IN	NUMBER,
				i_asset_location_id  		IN	NUMBER  ,
		   	 	i_depreciate_flag    		IN	VARCHAR2,
				i_depreciation_expense_ccid	IN	NUMBER,
				i_asset_status			IN	VARCHAR2,
                                i_xface_asset_units             IN      NUMBER  -- added bug 9339798
			     )
IS

	error_msg   			VARCHAR2(200);

Line: 558


        l_update_login    NUMBER := nvl(FND_GLOBAL.login_id, -1);

Line: 563


             SELECT estimated_asset_units
             FROM  pa_project_assets_all
             WHERE project_asset_id        = i_project_asset_id;

Line: 623


			INSERT INTO ipa_apis_interface_errors
			(  INTERFACE_ID           ,
			   PROJECT_ID             ,
			   PROJECT_ASSET_ID       ,
			   ERROR_MESSAGE
			)
			VALUES
			( i_interface_id 	,
			  i_project_id		,
			  i_project_asset_id	,
			  'ORA-'||to_char(result)	) ;

Line: 640


			   INSERT INTO ipa_apis_interface_errors
			   (  INTERFACE_ID           ,
			      PROJECT_ID             ,
			      PROJECT_ASSET_ID       ,
			      DATE_PLACED_IN_SERVICE ,
			      ASSET_STATUS		  ,
			      ERROR_MESSAGE
			   )
			   VALUES
			   ( i_interface_id 		,
			     i_project_id		,
			     i_project_asset_id	,
			     i_date_placed_in_service,
			     i_asset_status       ,
			     warning_msg) ;

Line: 665


			  UPDATE pa_project_assets_all
			  SET 	 date_placed_in_service = i_date_placed_in_service,
				   attribute6    = i_asset_status
                                 --Bug 3068204
                                 ,project_asset_type = 'AS-BUILT'
		                 ,asset_units = NVL(i_xface_asset_units,NVL(i_asset_units,l_est_asset_units)) -- added bug 9339798
                        	 /* Bug#3018526 Updating Who columns */
				 ,last_update_date   = SYSDATE
				 ,last_updated_by    = l_update_login
				 ,last_update_login  = l_update_login
				 ,request_id         = l_request_id
				 ,program_id         = l_program_id
				 ,program_update_date= SYSDATE
			  WHERE  project_id    = i_project_id
			  AND	 project_asset_id = i_project_asset_id ;

Line: 683


				  INSERT INTO ipa_apis_interface_errors
				  (  INTERFACE_ID           ,
				     PROJECT_ID             ,
				     PROJECT_ASSET_ID       ,
				     ERROR_MESSAGE
				  )
				  VALUES
				  ( i_interface_id ,
				    i_project_id		,
				    i_project_asset_id	,
				    'NO RECORDS FOUND' 	) ;

Line: 699


			INSERT INTO ipa_apis_interface_errors
			(  INTERFACE_ID           ,
			   PROJECT_ID             ,
			   PROJECT_ASSET_ID       ,
			   ERROR_MESSAGE
			)
			VALUES
			( i_interface_id 	,
			  i_project_id		,
			  i_project_asset_id	,
			  error_msg  		) ;

Line: 713


	update ipa_apis_interface
	set    record_status = 'PROCESSED'
	where  interface_id = i_interface_id ;

Line: 719


END update_dpis ;