APPS.ENI_DBI_PCM_PKG SQL Statements

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

Line: 97

   select
     NVL(common_assembly_item_id,assembly_item_id),
     NVL(common_organization_id ,organization_id)
     INTO
     l_item,l_org
   from
     bom_bill_of_materials
   where
     assembly_item_id = l_item_temp and
     organization_id  = l_org_temp and
     alternate_bom_designator IS NULL;

Line: 118

	SELECT count(*)
	INTO l_org_exists
	FROM eni_dbi_part_count_org_temp
	WHERE organization_id = l_org_temp;

Line: 133

   x_custom_sql :=' SELECT NULL AS VIEWBY,
                NULL AS ENI_MEASURE1,
                NULL AS ENI_MEASURE2,
                NULL AS ENI_MEASURE4,
                NULL AS ENI_MEASURE5  FROM DUAL ';

Line: 148

	' SELECT
		name as VIEWBY,
		SUM(ENI_MEASURE1) AS ENI_MEASURE1,
		SUM(ENI_MEASURE2) AS ENI_MEASURE2,
		SUM(ENI_MEASURE4) AS ENI_MEASURE4,
		SUM(ENI_MEASURE5) AS ENI_MEASURE5
	  FROM
	  (
		SELECT
			t.start_date,
			t.name,
			SUM
			(
				CASE WHEN t.c_end_date BETWEEN pco.effectivity_date
				AND pco.disable_date
				THEN
					part_count
				ELSE
					NULL
				END
			) AS ENI_MEASURE1, -- part count current
			SUM
			(
				CASE WHEN t.p_end_date BETWEEN pco.effectivity_date
				AND pco.disable_date
				THEN
					part_count
				ELSE
					NULL
				END
			) AS ENI_MEASURE2, -- part count prior
			SUM(NULL) AS ENI_MEASURE4, -- mfg steps current
			SUM(NULL) AS ENI_MEASURE5 -- mfg steps prior
		FROM
			eni_dbi_part_count_mv pco , '||l_from_clause||'
		WHERE
			pco.assembly_item_id = :ITEM       --|| l_item || : Bug 5083568
			AND pco.organization_id = :ORG     --|| l_org ||  : Bug 5083568
			AND
			(
				t.c_end_date BETWEEN pco.effectivity_date AND pco.disable_date
				OR
				t.p_end_date BETWEEN pco.effectivity_date AND pco.disable_date
			)
		group by t.start_date,t.name
		UNION ALL
		SELECT -- mfg steps
			t.start_date,
			t.name,
			SUM(NULL) AS ENI_MEASURE1, -- part count current
			SUM(NULL) AS ENI_MEASURE2, -- part count prior
			SUM
			(
				CASE WHEN t.c_end_date
				BETWEEN trunc(effectivity_date)	AND
				nvl(trunc(disable_date),t.c_end_date+1)
				THEN
					mfgsteps_count
				ELSE
					NULL
				END
			) AS ENI_MEASURE4, -- mfg steps current
			SUM
			(
				CASE WHEN t.p_end_date
				BETWEEN trunc(effectivity_date) AND
				nvl(trunc(disable_date),t.p_end_date+1)
				THEN
					mfgsteps_count
				ELSE
					NULL
				END
			) AS ENI_MEASURE5 -- mfg steps prior
		FROM
			eni_dbi_mfg_steps_join_mv mfg , '||l_from_clause||'
		WHERE
			mfg.item_id = :ITEM_TEMP                --|| l_item_temp || : Bug 5083568
			AND mfg.organization_id = :ORG_TEMP     --|| l_org_temp ||  : Bug 5083568
			AND
			(
				t.c_end_date BETWEEN trunc(effectivity_date) AND
				nvl(trunc(disable_date),t.c_end_date + 1)
				OR
				t.p_end_date BETWEEN trunc(effectivity_date) AND
				nvl(trunc(disable_date),t.p_end_date + 1)
			)
		group by t.start_date,t.name
		UNION ALL
		SELECT
			t.start_date,
			t.name,
			NULL AS ENI_MEASURE1, -- part count current
			NULL AS ENI_MEASURE2, -- part count prior
			NULL AS ENI_MEASURE4, -- mfg steps current
			NULL AS ENI_MEASURE5 -- mfg steps prior
		FROM
			'||l_from_clause||'
		WHERE
			NOT(
				(EXISTS(select * from eni_dbi_part_count_mv
				 where assembly_item_id = :ITEM AND      --||l_item|| AND : Bug 5083568
				 organization_id = :ORG AND              --||l_org|| AND  : Bug 5083568
				 (t.c_end_date BETWEEN effectivity_date AND disable_date
				 OR t.p_end_date BETWEEN effectivity_date AND disable_date)))
			  OR
				(EXISTS(select * from eni_dbi_mfg_steps_join_mv
				 where item_id = :ITEM_TEMP AND          --||l_item_temp|| AND : Bug 5083568
				 organization_id = :ORG_TEMP AND         --||l_org_temp|| AND  : Bug 5083568
	       			 (t.c_end_date BETWEEN trunc(effectivity_date) AND
                                 nvl(trunc(disable_date),t.c_end_date + 1)
				 OR t.p_end_date BETWEEN trunc(effectivity_date) AND
                                 nvl(trunc(disable_date),t.p_end_date + 1)) ))
                           ))
		group by start_date,name
                order by '||l_order_by ;