APPS.ENI_DBI_PCO_PKG SQL Statements

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

Line: 83

   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 and
     organization_id  = l_org and
     alternate_bom_designator IS NULL;

Line: 103

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

Line: 114

IF (l_item IS NULL) THEN  --  When no Item is selected, a single row of N/A is displayed
   x_custom_sql := '  SELECT NULL AS VIEWBY,
			     NULL AS ENI_MEASURE1,
		  	     NULL AS ENI_MEASURE2,
		  	     NULL AS ENI_MEASURE3,
		  	     NULL AS ENI_MEASURE4,
		  	     NULL AS ENI_MEASURE5,
		  	     NULL AS ENI_MEASURE6,
		  	     NULL AS ENI_MEASURE7,
			     NULL AS ENI_MEASURE9
		      FROM DUAL ';

Line: 140

	'  SELECT
		eic.VALUE as VIEWBY,
		eic.ID as VIEWBYID,
		b.current_pco as ENI_MEASURE1,
		b.prior_pco as ENI_MEASURE2,
		b.change_percent as ENI_MEASURE3,
		b.percent_of_total as ENI_MEASURE4,
		sum(b.current_pco) over() as ENI_MEASURE5,
		sum(b.change_percent) over() AS ENI_MEASURE6,
		sum(b.percent_of_total) over() as ENI_MEASURE7,
		:PCO_LIST_DRILL || :PCO_CAT_DRILL || :PCO_COMP_DRILL || TO_CHAR(eic.ID) AS ENI_MEASURE9
	   FROM (SELECT
			a.ITEM_CATALOG_GROUP_ID,
			a.current_pco ,
			a.prior_pco ,
			round(DECODE(a.prior_pco,0 , NULL, (((a.current_pco - a.prior_pco)/a.prior_pco)*100)),2)
							AS change_percent,
			sum(a.current_pco) over() as grand_current,
			round(DECODE((sum(a.current_pco) over()),0 , NULL, ((a.current_pco/(sum(a.current_pco) over()))*100)),2)
							AS percent_of_total
			FROM
			(SELECT NVL(eiv.ITEM_CATALOG_GROUP_ID,-1) as item_catalog_group_id,
				SUM(CASE WHEN ('||'&'|| 'BIS_CURRENT_ASOF_DATE -- Condition modified to fix the Bug 3151377
					BETWEEN pco.effectivity_date AND pco.disable_date)
					THEN 1 ELSE 0 END) AS current_pco,
				SUM(CASE WHEN ('||'&'|| 'BIS_PREVIOUS_ASOF_DATE  -- Condition modified to fix the Bug 3151377
					BETWEEN pco.effectivity_date AND pco.disable_date)
					THEN 1 ELSE 0 END) AS prior_pco
			FROM ENI_DBI_PART_COUNT_F pco, ENI_ITEM_ORG_V eiv WHERE
			'||l_where_clause||'
			GROUP BY NVL(eiv.item_catalog_group_id,-1)) a) b, ENI_ITEM_ITM_CAT_V eic
	    WHERE b.ITEM_CATALOG_GROUP_ID = eic.ID and
			     (NOT(b.current_pco = 0)) and     -- Condition added to to fix the Bug 3131408
		  eic.ID = NVL(TO_NUMBER(eic.NODE),-1)
	    GROUP BY VALUE, ID, current_pco, prior_pco, change_percent, percent_of_total
	    ORDER BY
		' ||l_order_by;