APPS.WIP_PERF_TO_PLAN SQL Statements

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

Line: 88

	-- and update the performance table
	Update_Actual_Quantity(
                p_errnum => p_errnum,
                p_errmesg => p_errmesg);

Line: 99

	--Delete old data in the performance table, and commit new populated data

	Post_Populate_Perf_Info(
				p_errnum => p_errnum,
				p_errmesg => p_errmesg);

Line: 144

	insert into wip_bis_perf_to_plan(
		ORGANIZATION_ID,
		INVENTORY_ITEM_ID,
		SCHEDULE_DATE,
		SCHEDULE_QUANTITY,
		ACTUAL_QUANTITY,
		ITEM_COST,
		EXISTING_FLAG,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		CREATION_DATE,
		CREATED_BY,
		PROGRAM_APPLICATION_ID
	)
	(select mbppv.organization_id,
		mbppv.inventory_item_id,
		mbppv.schedule_date,
		nvl(mbppv.schedule_quantity,0),
		0,
		0,
		0,
		sysdate,
		p_userid,
		sysdate,
		p_userid,
		p_applicationid
	from 	mrp_bis_plan_prod_v mbppv
	where	trunc(mbppv.schedule_date) between trunc(nvl(p_date_from,mbppv.schedule_date))
		and trunc(nvl(p_date_to,mbppv.schedule_date))
	);

Line: 190

		to update the actual_quantity,item_cost for performance table
 ------------------------------------------------------------------------------------*/

PROCEDURE Update_Actual_Quantity(
	p_errmesg OUT NOCOPY VARCHAR2,
	p_errnum OUT NOCOPY NUMBER)
IS
sum_quantity		NUMBER;

Line: 202

	SELECT 	organization_id,
		inventory_item_id,
		schedule_date,
		actual_quantity,
		item_cost
	FROM wip_bis_perf_to_plan WHERE existing_flag = 0 FOR UPDATE;

Line: 214

	--dbms_output.put_line('Start Update actual_quantity.');

Line: 221

	-- Update the item_cost based on table cst_item_costs, mtl_parameters
		BEGIN
        		select distinct cic.item_cost into p_item_cost
        		from
               			cst_item_costs cic,
				mtl_parameters mp
        		where   mp.organization_id = perf_rec.organization_id
        		and     cic.organization_id = mp.organization_id
        		and     cic.inventory_item_id = perf_rec.inventory_item_id
        		and     cic.cost_type_id = mp.primary_cost_method;

Line: 243

			select nvl(sum(mmt.primary_quantity),0) into complete_quantity
			from
				mtl_material_transactions mmt,
				wip_entities we,
				wip_discrete_jobs wdj

			where 	mmt.transaction_source_type_id = 5
			and	mmt.transaction_action_id in (31,32)
			and	wdj.organization_id = perf_rec.organization_id
			and	wdj.primary_item_id = perf_rec.inventory_item_id
			and	mmt.organization_id = wdj.organization_id
			and	mmt.inventory_item_id = wdj.primary_item_id
			and	mmt.transaction_source_id = wdj.wip_entity_id
			and	we.wip_entity_id = wdj.wip_entity_id
			and	we.entity_type in (1,3)
			and	trunc(mmt.transaction_date) <= trunc(wdj.scheduled_completion_date)
			and   	trunc(wdj.scheduled_completion_date) = trunc(perf_rec.schedule_date)

			group by
				mmt.inventory_item_id,
				trunc(wdj.scheduled_completion_date);

Line: 272

		select nvl(sum(mmt.primary_quantity),0) into complete_quantity
		from
			mtl_material_transactions mmt,
			wip_entities we,
			wip_discrete_jobs wdj

		where 	mmt.transaction_source_type_id = 5
		and	mmt.transaction_action_id in (31,32)
		and	wdj.organization_id = perf_rec.organization_id
		and	wdj.primary_item_id = perf_rec.inventory_item_id
		and	mmt.organization_id = wdj.organization_id
		and	mmt.inventory_item_id = wdj.primary_item_id
		and	mmt.transaction_source_id = wdj.wip_entity_id
		and	we.wip_entity_id = wdj.wip_entity_id
		and	we.entity_type in (1,3)
		and	trunc(mmt.transaction_date) > trunc(wdj.scheduled_completion_date)
		and	trunc(mmt.transaction_date) = trunc(perf_rec.schedule_date)

		group by
			mmt.inventory_item_id,
			trunc(mmt.transaction_date);

Line: 302

			select nvl(sum(mmt.primary_quantity),0) into complete_quantity
			from
				mtl_material_transactions mmt,
				wip_entities we,
	 			wip_flow_schedules wfs
			where 	mmt.transaction_source_type_id = 5
			and	mmt.transaction_action_id in (31,32)
			and	wfs.organization_id = perf_rec.organization_id
			and	wfs.primary_item_id = perf_rec.inventory_item_id
			and	mmt.organization_id = wfs.organization_id
			and	mmt.inventory_item_id = wfs.primary_item_id
			and	mmt.transaction_source_id = wfs.wip_entity_id
			and	we.wip_entity_id = wfs.wip_entity_id
			and	we.entity_type = 4
			and	trunc(mmt.transaction_date) <= trunc(wfs.scheduled_completion_date)
			and	trunc(wfs.scheduled_completion_date) = trunc(perf_rec.schedule_date)
			group by
				mmt.inventory_item_id,
				trunc(wfs.scheduled_completion_date);

Line: 329

		select 	nvl(sum(mmt.primary_quantity),0) into complete_quantity
		from
			mtl_material_transactions mmt,
			wip_entities we,
			wip_flow_schedules wfs

		where 	mmt.transaction_source_type_id = 5
		and	mmt.transaction_action_id in (31,32)
		and	wfs.organization_id = perf_rec.organization_id
		and	wfs.primary_item_id = perf_rec.inventory_item_id
		and	mmt.organization_id = wfs.organization_id
		and	mmt.inventory_item_id = wfs.primary_item_id
		and	mmt.transaction_source_id = wfs.wip_entity_id
		and	we.wip_entity_id = wfs.wip_entity_id
		and	we.entity_type = 4
		and	trunc(mmt.transaction_date) > trunc(wfs.scheduled_completion_date)
		and	trunc(mmt.transaction_date) = trunc(perf_rec.schedule_date)

		group by
			mmt.inventory_item_id,
			trunc(mmt.transaction_date);

Line: 358

			select  nvl(sum(mmta.primary_quantity),0) into complete_quantity
			from
				wip_repetitive_schedules wrs,
				wip_entities we,
				mtl_material_transactions mmt,
				mtl_material_txn_allocations mmta
			where 	mmt.transaction_source_type_id = 5
			and	mmt.transaction_action_id in (31,32)
			and	wrs.organization_id = perf_rec.organization_id
			and	we.primary_item_id = perf_rec.inventory_item_id
			and	we.entity_type = 2
			and 	we.wip_entity_id = wrs.wip_entity_id
			and 	we.organization_id = wrs.organization_id
			and	mmta.organization_id = wrs.organization_id
			and	mmta.repetitive_schedule_id = wrs.repetitive_schedule_id
			and	mmt.organization_id = wrs.organization_id
			and	mmt.inventory_item_id = we.primary_item_id
			and	mmt.transaction_source_id = we.wip_entity_id
			and	mmt.transaction_id = mmta.transaction_id
			and	trunc(mmta.transaction_date) <= trunc(wrs.last_unit_completion_date)
			and	trunc(wrs.last_unit_completion_date) = trunc(perf_rec.schedule_date)
			group by
				mmt.inventory_item_id,
				trunc(wrs.last_unit_completion_date);

Line: 390

		select nvl(sum(mmta.primary_quantity),0) into complete_quantity
		from
			wip_repetitive_schedules wrs,
			wip_entities we,
			mtl_material_transactions mmt,
			mtl_material_txn_allocations mmta
		where 	mmt.transaction_source_type_id = 5
		and	mmt.transaction_action_id in (31,32)
		and	wrs.organization_id = perf_rec.organization_id
		and	we.primary_item_id = perf_rec.inventory_item_id
		and	we.entity_type = 2
		and 	we.wip_entity_id = wrs.wip_entity_id
		and 	we.organization_id = wrs.organization_id
		and	mmta.organization_id = wrs.organization_id
		and	mmta.repetitive_schedule_id = wrs.repetitive_schedule_id
		and	mmt.organization_id = wrs.organization_id
		and	mmt.inventory_item_id = we.primary_item_id
		and	mmt.transaction_source_id = we.wip_entity_id
		and	mmt.transaction_id = mmta.transaction_id
		and	trunc(mmta.transaction_date) > trunc(wrs.last_unit_completion_date)
		and	trunc(mmta.transaction_date) = trunc(perf_rec.schedule_date)
		group by
			mmt.inventory_item_id,
			trunc(mmta.transaction_date);

Line: 420

		UPDATE wip_bis_perf_to_plan
		SET
			actual_quantity = actual_quantity + sum_quantity,
			item_cost = p_item_cost
		WHERE CURRENT OF perf_cur;

Line: 438

                p_errmesg :=  'Failed in Update actual_quantity: ' ||substr(SQLERRM, 1, 150);

Line: 446

END Update_Actual_Quantity;

Line: 460

	--dbms_output.put_line('Delete all the old information from performance table.');

Line: 461

	delete from wip_bis_perf_to_plan where existing_flag <> 0;

Line: 464

	update wip_bis_perf_to_plan set existing_flag = 1;

Line: 478

		delete from wip_bis_perf_to_plan;

Line: 479

		--dbms_output.put_line('All data deleted from wip_bis_perf_to_plan');

Line: 494

	-- Delete all the performance data not correctly populated
	delete from wip_bis_perf_to_plan
	where existing_flag = 0;

Line: 507

                delete from wip_bis_perf_to_plan;