APPS.EAM_ASSET_OPERATION_TXN_PVT SQL Statements

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

Line: 22

	SELECT
	  created_by
	INTO l_created_by
	FROM eam_asset_operation_txn
	WHERE instance_id=p_instance_id
	 AND txn_date =
	  (
        SELECT
           max(txn_date)
        FROM eam_asset_operation_txn eaot
        WHERE eaot.instance_id=p_instance_id
        );

Line: 111

	EAM_ASSET_OPERATION_TXN_PVT.insert_txn(
				p_txn_date			=>	p_txn_date,
				p_txn_type			=>	p_txn_type,
				p_instance_id			=>	p_instance_id,
				p_comments			=>	p_comments,
				p_qa_collection_id		=>	p_qa_collection_id,
				p_operable_flag			=>	p_operable_flag,
				p_employee_id			=>	p_employee_id,
				p_eam_ops_quality_tbl		=>	l_eam_ops_quality_tbl,
				p_meter_reading_rec_tbl		=>	l_eam_meter_reading_tbl,
				p_counter_properties_tbl	=>	l_eam_counter_properties_tbl,
				p_attribute_category		=>	p_attribute_category,
				p_attribute1			=>	p_attribute1,
				p_attribute2			=>	p_attribute2,
				p_attribute3			=>	p_attribute3,
				p_attribute4			=>	p_attribute4,
				p_attribute5			=>	p_attribute5,
				p_attribute6			=>	p_attribute6,
				p_attribute7			=>	p_attribute7,
				p_attribute8			=>	p_attribute8,
				p_attribute9			=>	p_attribute9,
				p_attribute10			=>	p_attribute10,
				p_attribute11			=>	p_attribute11,
				p_attribute12			=>	p_attribute12,
				p_attribute13			=>	p_attribute13,
				p_attribute14			=>	p_attribute14,
				p_attribute15			=>	p_attribute15,
				x_return_status			=>	x_return_status,
				x_msg_count			=>	x_msg_count,
				x_msg_data			=>	x_msg_data
				);

Line: 187

PROCEDURE insert_quality_plans
(

        p_eam_ops_quality_tbl		IN		eam_asset_operation_txn_pub.eam_quality_tbl_type,
	p_instance_id			IN		number,
	p_txn_date			IN		date,
	p_comments			IN		varchar2,
	p_operable_flag			IN		number,
	p_organization_id		IN		number,
	p_employee_id			IN		number,
	p_asset_group_id		IN		number,
        p_asset_number			IN		varchar2,
	p_asset_instance_number		IN		varchar2,
	p_txn_number			IN		number,
        x_return_status			OUT NOCOPY	varchar2,
        x_msg_count			OUT NOCOPY	number,
	x_msg_data			OUT NOCOPY	varchar2
)

IS

	Type header_plan_id_tbl_type is table of NUMBER
			INDEX BY BINARY_INTEGER;

Line: 237

	SAVEPOINT eaot_insert_quality_plans;

Line: 285

		l_elements.delete;

Line: 294

				 select qa_collection_id_s.nextval into l_collection_id from dual;

Line: 300

		qa_results_pub.insert_row(
			p_api_version => 1.0,
			p_init_msg_list => fnd_api.g_true,
			p_org_id => l_org_id,
			p_plan_id => l_header_plan_id_tbl(plan_id),
			p_spec_id => null,
			p_transaction_number =>p_eam_ops_quality_tbl(0).transaction_number ,
			p_transaction_id => null,
			p_enabled_flag => 1,
			p_commit =>  fnd_api.g_false,
			x_collection_id => l_collection_id,
			x_occurrence => l_temp_occurence,
			x_row_elements => l_elements,
			x_msg_count => x_msg_count,
			x_msg_data  => x_msg_data,
			x_error_array => l_error_array,
			x_message_array => l_message_array,
			x_return_status => x_return_status,
			x_action_result => l_action_result
			);

Line: 328

		 select qp.name into l_plan_name
		    from qa_plans qp,
		    qa_results qr
		    where
		    qr.collection_id = l_collection_id
		    and
		    qr.plan_id = qp.plan_id;

Line: 337

		EAM_ASSET_LOG_PVT.INSERT_ROW(
		p_api_version		=>	1.0,
		p_init_msg_list		=>	fnd_api.g_true,
		p_commit		=>	fnd_api.g_false,
		p_event_date		=>	p_txn_date,
		p_event_type		=>	'EAM_SYSTEM_EVENTS',
		p_event_id		=>	12,
		p_instance_id		=>	p_instance_id,
		p_organization_id       =>      p_organization_id,
		p_employee_id           =>      p_employee_id,
		p_comments		=>	p_comments,
		p_reference		=>	l_plan_name,
		p_ref_id		=>	l_collection_id,
		p_operable_flag		=>	p_operable_flag,
		x_return_status		=>	l_assetops_return_status,
		x_msg_count		=>	x_msg_count,
		x_msg_data		=>	x_msg_data
				);

Line: 388

         SELECT distinct plan_id
         bulk collect into
         l_planid_tbl
         from
         QA_RESULTS
         where
         collection_id = l_collection_id;

Line: 406

	         SELECT
                 DISTINCT concatenated_segments
                 INTO
                 l_asset_group
                 FROM mtl_system_items_kfv
                 WHERE
                 inventory_item_id = p_asset_group_id;

Line: 450

ROLLBACK TO eaot_insert_quality_plans;

Line: 452

END insert_quality_plans;

Line: 457

PROCEDURE insert_meter_readings
(
        p_eam_meter_reading_tbl		IN		eam_asset_operation_txn_pub.meter_reading_rec_tbl_type,
        p_counter_properties_tbl	IN		eam_asset_operation_txn_pub.Ctr_Property_readings_Tbl,
	p_instance_id			IN		number,
	p_txn_id			IN		number,
	x_return_status			OUT NOCOPY	varchar2,
        x_msg_count			OUT NOCOPY	number,
	x_msg_data			OUT NOCOPY	varchar2
)

IS
	l_counter_properties_tbl		EAM_MeterReading_PUB.Ctr_Property_readings_Tbl;

Line: 476

	SAVEPOINT eaot_insert_meter_readings;

Line: 531

		l_counter_properties_tbl.DELETE;

Line: 591

ROLLBACK TO eaot_insert_meter_readings;

Line: 593

END insert_meter_readings;

Line: 654

	SELECT  MAX(txn_date)
	INTO l_last_txn_date
	FROM eam_asset_operation_txn
	WHERE instance_id=p_instance_id;

Line: 679

	SELECT
	count(*)
	INTO l_count
	FROM csi_item_instances
	WHERE instance_id=p_instance_id
	 AND
	p_txn_date BETWEEN nvl(active_start_date,sysdate) AND
	NVL(active_end_date, sysdate);

Line: 709

	SELECT
	count(*)
	INTO l_count
	FROM fnd_user
	WHERE p_employee_id=user_id;

Line: 737

		SELECT
		txn_type
		INTO l_txn_type
		FROM eam_asset_operation_txn
		WHERE instance_id=p_instance_id
		AND txn_date=l_last_txn_date;

Line: 785

	SELECT	count(*) INTO l_count
		FROM	mfg_lookups
		WHERE	lookup_type='SYS_YES_NO' AND
			enabled_flag='Y' AND
			p_txn_date BETWEEN NVL(start_date_active, p_txn_date) AND
			NVL(end_date_active,sysdate) AND
			lookup_code = p_operable_flag;

Line: 852

PROCEDURE insert_txn(

	p_api_version			IN		number		:= 1.0,
	p_init_msg_list			IN		varchar2	:= fnd_api.g_false,
	p_commit			IN		varchar2	:= fnd_api.g_false,
	p_validation_level		IN		number		:= fnd_api.g_valid_level_full,
	p_txn_date			IN		date		:= sysdate,
	p_txn_type			IN		number,
	p_instance_id			IN		number,
	p_comments			IN		varchar2	:= NULL,
	p_qa_collection_id		IN		number		:= NULL,
	p_operable_flag			IN		number,
	p_employee_id			IN		number,
	p_eam_ops_quality_tbl		IN		eam_asset_operation_txn_pub.eam_quality_tbl_type,
        p_meter_reading_rec_tbl		IN		eam_asset_operation_txn_pub.meter_reading_rec_tbl_type,
        p_counter_properties_tbl	IN		eam_asset_operation_txn_pub.Ctr_Property_readings_Tbl,
	p_attribute_category		IN		varchar2	:= NULL,
	p_attribute1			IN		varchar2	:= NULL,
	p_attribute2			IN		varchar2	:= NULL,
	p_attribute3			IN		varchar2	:= NULL,
	p_attribute4			IN		varchar2	:= NULL,
	p_attribute5			IN		varchar2	:= NULL,
	p_attribute6			IN		varchar2	:= NULL,
	p_attribute7			IN		varchar2	:= NULL,
	p_attribute8			IN		varchar2	:= NULL,
	p_attribute9			IN		varchar2	:= NULL,
	p_attribute10			IN		varchar2	:= NULL,
	p_attribute11			IN		varchar2	:= NULL,
	p_attribute12			IN		varchar2	:= NULL,
	p_attribute13			IN		varchar2	:= NULL,
	p_attribute14			IN		varchar2	:= NULL,
	p_attribute15			IN		varchar2	:= NULL,
	x_return_status			OUT NOCOPY	varchar2,
	x_msg_count			OUT NOCOPY	number,
	x_msg_data			OUT NOCOPY	varchar2
)

	IS

		l_api_name		constant	varchar2(30)	 := 'insert_txn';

Line: 933

			   (select qp.name
			    from qa_plans qp,
			    qa_results qr
			    where
			    qr.collection_id = qa_collection_id
			    and
			    qr.plan_id = qp.plan_id);

Line: 942

                (SELECT
                    distinct Plan_Id
                    from
                    QA_RESULTS
                    where
                    collection_id = p_qa_collection_id
                    );

Line: 953

      SAVEPOINT EAOT_INSERT_TXN;

Line: 972

      SELECT
	eam_asset_operation_txn_s.nextval
	INTO l_txn_id
	FROM dual;

Line: 992

      SELECT
      mp.maint_organization_id,
      cii.instance_number,
      cii.inventory_item_id,
      cii.serial_number
      INTO
      l_maint_org_id,
      l_asset_instance_number,
      l_asset_group_id,
      l_asset_number
      FROM
      mtl_parameters mp, csi_item_instances cii
      where
      cii.last_vld_organization_id = mp.organization_id
      AND cii.instance_id = p_instance_id;

Line: 1043

		insert_quality_plans(
			p_eam_ops_quality_tbl	=>   p_eam_ops_quality_tbl
		       ,p_instance_id		=>   p_instance_id
		       ,p_txn_date		=>   p_txn_date
		       ,p_comments		=>   p_comments
		       ,p_operable_flag		=>   p_operable_flag
		       ,p_organization_id	=>   l_maint_org_id
		       ,p_asset_group_id	=>   l_asset_group_id
		       ,p_asset_instance_number =>   l_asset_instance_number
		       ,p_asset_number		=>   l_asset_number
		       ,p_txn_number		=>   l_txn_number
		       ,p_employee_id		=>   p_employee_id
		       ,x_return_status         =>   x_return_status
		       ,x_msg_count		=>   x_msg_count
		       ,x_msg_data		=>   x_msg_data

			);

Line: 1069

		insert_meter_readings(
			p_eam_meter_reading_tbl	  =>   p_meter_reading_rec_tbl
		       ,p_counter_properties_tbl  =>   p_counter_properties_tbl
		       ,p_instance_id		  =>   p_instance_id
		       ,p_txn_id		  =>   l_txn_id
		       ,x_return_status		  =>   x_return_status
		       ,x_msg_count		  =>   x_msg_count
		       ,x_msg_data		  =>   x_msg_data
		        );

Line: 1127

	  --insert record in eam_asset_operation_txn table


	SELECT DISTINCT nvl(ppf.full_name,fu.user_name)
	INTO l_reference
	FROM fnd_user fu,per_people_f ppf
	WHERE fu.employee_id=ppf.person_id(+)
	AND fu.user_id=p_employee_id
	AND sysdate BETWEEN fu.start_date AND nvl(fu.end_date,sysdate)
        AND sysdate BETWEEN ppf.effective_start_date AND nvl(ppf.effective_end_date,sysdate)
	and rownum = 1;

Line: 1140

	insert into eam_asset_operation_txn(
			txn_id,
			txn_date,
			txn_type,
			instance_id,
			comments,
			user_id,
			operable,
			qa_collection_id,
			attribute_category,
			attribute1,
			attribute2,
			attribute3,
			attribute4,
			attribute5,
			attribute6,
			attribute7,
			attribute8,
			attribute9,
			attribute10,
			attribute11,
			attribute12,
			attribute13,
			attribute14,
			attribute15,
			created_by,
			creation_date,
			last_updated_by,
			last_update_date,
			last_update_login
			)
			VALUES
			(
			l_txn_id,
			p_txn_date,
			p_txn_type,
			p_instance_id,
			p_comments,
			p_employee_id,
			p_operable_flag,
			p_qa_collection_id,
			p_attribute_category,
			p_attribute1,
			p_attribute2,
			p_attribute3,
			p_attribute4,
			p_attribute5,
			p_attribute6,
			p_attribute7,
			p_attribute8,
			p_attribute9,
			p_attribute10,
			p_attribute11,
			p_attribute12,
			p_attribute13,
			p_attribute14,
			p_attribute15,
			FND_GLOBAL.user_id,
			sysdate,
			FND_GLOBAL.user_id,
			sysdate,
			FND_GLOBAL.login_id
			);

Line: 1235

	   	 SELECT
                 DISTINCT concatenated_segments
                 INTO
                 l_asset_group
                 FROM mtl_system_items_kfv
                 WHERE
                 inventory_item_id = l_asset_group_id;

Line: 1299

				EAM_ASSET_LOG_PVT.INSERT_ROW(
				p_api_version		=>	1.0,
				p_init_msg_list		=>	p_init_msg_list,
				p_commit		=>	p_commit,
				p_validation_level	=>	p_validation_level,
				p_event_date		=>	p_txn_date,
				p_event_type		=>	'EAM_SYSTEM_EVENTS',
				p_event_id		=>	12,
				p_instance_id		=>	p_instance_id,
				p_employee_id		=>	p_employee_id,
				p_organization_id	=>	l_maint_org_id,
				p_comments		=>	p_comments,
				p_reference		=>	l_plan_name,
				p_ref_id		=>	p_qa_collection_id,
				p_operable_flag		=>	p_operable_flag,
				x_return_status		=>	l_assetops_return_status,
				x_msg_count		=>	x_msg_count,
				x_msg_data		=>	x_msg_data
						);

Line: 1339

		EAM_ASSET_LOG_PVT.INSERT_ROW(
		p_api_version		=>	1.0,
		p_init_msg_list		=>	p_init_msg_list,
		p_commit		=>	p_commit,
		p_validation_level	=>	p_validation_level,
		p_event_date		=>	p_txn_date,
		p_event_type		=>	'EAM_SYSTEM_EVENTS',
		p_event_id		=>	l_event_id,
		p_employee_id		=>	p_employee_id,
		p_organization_id	=>	l_maint_org_id,
		p_instance_id		=>	p_instance_id,
		p_comments		=>	p_comments,
		p_reference		=>	l_reference,
		p_ref_id		=>	l_txn_id,
		p_operable_flag		=>	p_operable_flag,
		x_return_status		=>	l_assetops_return_status,
		x_msg_count		=>	x_msg_count,
		x_msg_data		=>	x_msg_data
				);

Line: 1367

		 select object_version_number
			into l_object_version_number
                  from csi_item_instances
                  where instance_id = p_instance_id;

Line: 1430

		csi_item_instance_pub.update_item_instance
				(
			p_api_version           =>    1.0
		       ,p_commit                =>    fnd_api.g_false
		       ,p_init_msg_list         =>    fnd_api.g_false
		       ,p_validation_level      =>    fnd_api.g_valid_level_full
		       ,p_instance_rec          =>    l_instance_rec
	               ,p_ext_attrib_values_tbl =>    l_ext_attrib_values_tbl
                       ,p_party_tbl             =>    l_party_tbl
                       ,p_account_tbl           =>    l_account_tbl
                       ,p_pricing_attrib_tbl    =>    l_pricing_attrib_tbl
                       ,p_org_assignments_tbl   =>    l_org_assignments_tbl
                       ,p_asset_assignment_tbl  =>    l_asset_assignment_tbl
                       ,p_txn_rec               =>    l_txn_rec
                       ,x_instance_id_lst       =>    x_instance_id_lst
                       ,x_return_status         =>    x_return_status
                       ,x_msg_count             =>    x_msg_count
                       ,x_msg_data              =>    x_msg_data
				);

Line: 1465

         ROLLBACK TO EAOT_INSERT_TXN;

Line: 1471

         ROLLBACK TO EAOT_INSERT_TXN;

Line: 1477

	ROLLBACK TO EAOT_INSERT_TXN;

Line: 1479

        ROLLBACK TO EAOT_INSERT_TXN;

Line: 1482

         ROLLBACK TO EAOT_INSERT_TXN;

Line: 1494

END insert_txn;