APPS.ISC_DBI_PDUE_SCH_SUM_PKG SQL Statements

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

Line: 11

  l_select_stmt			VARCHAR2(10000);

Line: 12

  l_inner_select_stmt		VARCHAR2(10000);

Line: 98

		(SELECT 1
		FROM org_access o
		WHERE o.responsibility_id = fnd_global.resp_id
		AND o.resp_application_id = fnd_global.resp_appl_id
		AND o.organization_id = fact.inv_org_id)
	OR EXISTS
		(SELECT 1
		FROM mtl_parameters org
		WHERE org.organization_id = fact.inv_org_id
		AND NOT EXISTS
			(SELECT 1
			FROM org_access ora
			WHERE org.organization_id = ora.organization_id)))';

Line: 208

	SELECT 1
	  FROM ISC_DBI_CFM_008_MV	fact
	 WHERE fact.time_snapshot_date_id BETWEEN :l_effective_start_date
					      AND :l_as_of_date
	   AND rownum = 1 ';

Line: 241

    THEN l_select_stmt := '
 SELECT	items.value						VIEWBY,
	items.id						VIEWBYID,
	NULL							ISC_ATTRIBUTE_3, -- drill across url
	items.description					ISC_ATTRIBUTE_4, -- item description
	mtl.unit_of_measure					ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1		rnk,
	item_id,
	uom,
	'||l_measures||'
   FROM
(SELECT	c.item_id,
	c.uom,		';

Line: 256

	l_inner_select_stmt := '
		 SELECT	fact.item_id					ITEM_ID,
			fact.uom					UOM,';

Line: 270

    THEN l_select_stmt := '
 SELECT	org.name						VIEWBY,
	org.organization_id					VIEWBYID,
	NULL							ISC_ATTRIBUTE_3, -- drill across url
	NULL							ISC_ATTRIBUTE_4, -- item description
	NULL							ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1		rnk,
	inv_org_id,
	'||l_measures||'
   FROM
(SELECT	c.inv_org_id,	';

Line: 283

	l_inner_select_stmt := '
		 SELECT	fact.inv_org_id					INV_ORG_ID,';

Line: 294

    THEN l_select_stmt := '
 SELECT	ecat.value					VIEWBY,
	ecat.id						VIEWBYID,
	decode(ecat.leaf_node_flag, ''Y'',
		''pFunctionName=ISC_DBI_PDUE_SCH_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
		''pFunctionName=ISC_DBI_PDUE_SCH_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
								ISC_ATTRIBUTE_3, -- drill across url
	NULL							ISC_ATTRIBUTE_4, -- item description
	NULL							ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1	rnk,
	item_category_id,
	'||l_measures||'
   FROM
(SELECT	c.item_category_id,	';

Line: 312

	l_inner_select_stmt := '
		 SELECT	eni_cat.parent_id				ITEM_CATEGORY_ID,';

Line: 317

	l_inner_select_stmt := '
		 SELECT	eni_cat.imm_child_id				ITEM_CATEGORY_ID,';

Line: 329

    l_select_stmt := '
 SELECT	cust.value						VIEWBY,
	cust.id							VIEWBYID,
	NULL							ISC_ATTRIBUTE_3, -- drill across url
	NULL							ISC_ATTRIBUTE_4, -- item description
	NULL							ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1		rnk,
	customer_id,
	'||l_measures||'
   FROM
(SELECT	c.customer_id,	';

Line: 342

	l_inner_select_stmt := '
		 SELECT	fact.customer_id				CUSTOMER_ID,';

Line: 359

	      l_inner_select_stmt := '
		 SELECT	fact.parent_id		ITEM_CATEGORY_ID,';

Line: 364

	      l_inner_select_stmt := '
		 SELECT fact.imm_child_id	ITEM_CATEGORY_ID,';

Line: 392

	 SELECT	0		VIEWBY,
		0		ISC_ATTRIBUTE_3,
		0		ISC_ATTRIBUTE_4,
		0		ISC_ATTRIBUTE_5,
		0		ISC_MEASURE_1,
		0		ISC_MEASURE_2,
		0		ISC_MEASURE_3,
		0		ISC_MEASURE_4,
		0		ISC_MEASURE_5,
		0		ISC_MEASURE_6,
		0		ISC_MEASURE_7,
		0		ISC_MEASURE_8,
		0		ISC_MEASURE_9
	   FROM	dual
	  WHERE 1 = 2 -- no snapshot taken in the current period';

Line: 408

  l_stmt := l_select_stmt || '
	c.curr_pdue_qty						ISC_MEASURE_1, -- pdue qty
	c.curr_pdue_value					ISC_MEASURE_2, -- pdue
	(c.curr_pdue_value - c.prev_pdue_value)
	  / decode(c.prev_pdue_value, 0, NULL,
		   abs(c.prev_pdue_value)) * 100		ISC_MEASURE_3, -- pdue change
	c.curr_line_cnt						ISC_MEASURE_4, -- pdue line cnt
	c.curr_days_late
	  / decode(c.curr_line_cnt, 0, NULL,
		   c.curr_line_cnt)				ISC_MEASURE_5, -- pdue avg days late
	sum(c.curr_pdue_value) over ()				ISC_MEASURE_6, -- gd total pdue
	(sum(c.curr_pdue_value) over () - sum(c.prev_pdue_value) over ())
	  / decode(sum(c.prev_pdue_value) over (), 0, NULL,
		   abs(sum(c.prev_pdue_value) over ())) * 100	ISC_MEASURE_7, -- gd total pdue change
	sum(c.curr_line_cnt) over ()				ISC_MEASURE_8, -- gd total pdue line cnt
	sum(c.curr_days_late) over ()
	  / decode(sum(c.curr_line_cnt) over (), 0, NULL,
		   sum(c.curr_line_cnt) over ())		ISC_MEASURE_9  -- gd total avg days late
	   FROM	('||l_inner_select_stmt||'
		sum(decode(fact.time_snapshot_date_id, a.day,
			   fact.'||l_pdue_qty||', 0))			CURR_PDUE_QTY,
		sum(decode(fact.time_snapshot_date_id, a.day,
			   fact.'||l_pdue_amt||', 0))			CURR_PDUE_VALUE,
		sum(decode(fact.time_snapshot_date_id, b.day,
			   fact.'||l_pdue_amt||', 0))			PREV_PDUE_VALUE,
		sum(decode(fact.time_snapshot_date_id, a.day,
			   fact.'||l_days_late||', 0))			CURR_DAYS_LATE,
		sum(decode(fact.time_snapshot_date_id, a.day,
			   fact.'||l_line_cnt||', 0))			CURR_LINE_CNT
	   FROM (SELECT max(time_snapshot_date_id)		day
		   FROM	'||l_mv||'			fact
		  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
						       AND &BIS_CURRENT_ASOF_DATE
					)	a,
		(SELECT max(time_snapshot_date_id)		day
		   FROM	'||l_mv||'			fact
		  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
						       AND &BIS_PREVIOUS_ASOF_DATE
					)	b,
		'||l_mv||'		fact'||l_prod_cat_from||'
	  WHERE fact.time_snapshot_date_id IN (a.day, b.day)
	    AND fact.late_schedule_flag = 1'||l_flags_where||'
	    AND fact.'||l_line_cnt||' <> 0'
		||l_inv_org_where
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where
		||l_inner_group_by_stmt||')	c))	a,'
		||l_where_stmt||'
  ORDER BY rnk';