APPS.BSC_PORTLET_KPILISTCUST SQL Statements

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

Line: 79

        SELECT p.RESPONSIBILITY_ID
        FROM icx_portlet_customizations p
        WHERE
	    p.PLUG_ID = p_plug_id;

Line: 115

PROCEDURE insert_row (
    p_plug_id IN NUMBER,
    p_details_flag IN NUMBER,
    p_group_flag IN NUMBER,
    p_kpi_measure_details_flag IN NUMBER,
    p_last_update_date IN DATE,
    p_last_updated_by IN NUMBER
) IS
    insert_err  EXCEPTION;

Line: 125

    INSERT INTO BSC_USER_KPILIST_PLUGS (
	PLUG_ID,
	DETAILS_FLAG,
	GROUP_FLAG,
        KPI_MEASURE_DETAILS_FLAG,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
    ) VALUES (
	p_plug_id,
	p_details_flag,
	p_group_flag,
        p_kpi_measure_details_flag,
	p_last_update_date,
	p_last_updated_by,
	p_last_update_date,
	p_last_updated_by,
	p_last_updated_by
    );

Line: 148

       RAISE insert_err;

Line: 151

END insert_row;

Line: 168

PROCEDURE insert_row (
    p_plug_id IN NUMBER,
    p_reference_path IN VARCHAR2,
    p_details_flag IN NUMBER,
    p_group_flag IN NUMBER,
    p_kpi_measure_details_flag IN NUMBER,
    p_last_update_date IN DATE,
    p_last_updated_by IN NUMBER
) IS
    insert_err  EXCEPTION;

Line: 179

    INSERT INTO BSC_USER_KPILIST_PLUGS (
	PLUG_ID,
	DETAILS_FLAG,
	GROUP_FLAG,
        KPI_MEASURE_DETAILS_FLAG,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	REFERENCE_PATH
    ) VALUES (
	p_plug_id,
	p_details_flag,
	p_group_flag,
	p_kpi_measure_details_flag,
	p_last_update_date,
	p_last_updated_by,
	p_last_update_date,
	p_last_updated_by,
	p_last_updated_by,
	p_reference_path
    );

Line: 204

       RAISE insert_err;

Line: 207

END insert_row;

Line: 225

PROCEDURE update_row (
    p_plug_id IN NUMBER,
    p_details_flag IN NUMBER,
    p_group_flag IN NUMBER,
    p_kpi_measure_details_flag IN NUMBER,
    p_last_update_date IN DATE,
    p_last_updated_by IN NUMBER
) IS

    update_err  EXCEPTION;

Line: 236

	UPDATE
	    BSC_USER_KPILIST_PLUGS
  	SET
	    DETAILS_FLAG = p_details_flag,
	    GROUP_FLAG = p_group_flag,
            KPI_MEASURE_DETAILS_FLAG = p_kpi_measure_details_flag,
	    LAST_UPDATE_DATE = SYSDATE,
	    LAST_UPDATED_BY = p_last_updated_by,
	    LAST_UPDATE_LOGIN = p_last_updated_by
	WHERE
	    PLUG_ID = p_plug_id;

Line: 249

              RAISE update_err;

Line: 251

END update_row;

Line: 267

PROCEDURE update_row (
    p_plug_id IN NUMBER,
    p_reference_path IN VARCHAR2,
    p_details_flag IN NUMBER,
    p_group_flag IN NUMBER,
    p_kpi_measure_details_flag IN NUMBER,
    p_last_update_date IN DATE,
    p_last_updated_by IN NUMBER
) IS

    update_err  EXCEPTION;

Line: 279

	UPDATE
	    BSC_USER_KPILIST_PLUGS
  	SET
	    DETAILS_FLAG = p_details_flag,
	    GROUP_FLAG = p_group_flag,
	    KPI_MEASURE_DETAILS_FLAG = p_kpi_measure_details_flag,
	    LAST_UPDATE_DATE = SYSDATE,
	    LAST_UPDATED_BY = p_last_updated_by,
	    LAST_UPDATE_LOGIN = p_last_updated_by
	WHERE
	    PLUG_ID = p_plug_id
    AND REFERENCE_PATH = p_reference_path;

Line: 293

              RAISE update_err;

Line: 295

END update_row;

Line: 313

PROCEDURE update_icx_portlet_cust (
    p_plug_id IN NUMBER,
    p_reference_path IN VARCHAR2,
    p_resp_id IN NUMBER,
    p_portlet_name IN VARCHAR
) IS

    update_err  EXCEPTION;

Line: 325

	UPDATE
	    ICX_PORTLET_CUSTOMIZATIONS
  	SET
	    CACHING_KEY        = TO_CHAR(TO_NUMBER(NVL(caching_key, 0))+1)
	  , RESPONSIBILITY_ID  = p_resp_id
	  , TITLE              = p_portlet_name
	  , PLUG_ID            = p_plug_id
	WHERE
	    REFERENCE_PATH = p_reference_path;

Line: 336

              RAISE update_err;

Line: 338

END update_icx_portlet_cust;

Line: 354

PROCEDURE update_indicators (
    p_resp_id IN NUMBER,
    p_last_update_date IN DATE,
    p_last_updated_by IN NUMBER,
    p_number_array IN BSC_NUM_LIST
) IS

    l_kpi_id Number;

Line: 366

	SELECT BK.INDICATOR
	FROM
	BSC_KPIS_B BK,
	BSC_TAB_INDICATORS BTI,
	BSC_USER_TAB_ACCESS BTA,
	BSC_USER_KPI_ACCESS BA
	WHERE
	BA.RESPONSIBILITY_ID = p_resp_id AND
	BA.INDICATOR IN (
            SELECT
                t.COLUMN_VALUE
	    FROM TABLE(CAST(p_number_array AS BSC_NUM_LIST)) t) AND
	BA.INDICATOR = BK.INDICATOR AND
	BTI.INDICATOR = BK.INDICATOR AND
	BTA.TAB_ID = BTI.TAB_ID AND
	BTA.RESPONSIBILITY_ID = BA.RESPONSIBILITY_ID AND
	SYSDATE BETWEEN NVL(BTA.START_DATE(+), SYSDATE) AND
	NVL(BTA.END_DATE(+), SYSDATE) AND
	BK.PROTOTYPE_FLAG <> 2  AND
	not exists (
	SELECT BUP.INDICATOR
	FROM BSC_USER_PARAMETERS_B BUP
	WHERE BUP.INDICATOR = BA.INDICATOR AND
	BUP. VIEW_TYPE = 1
	)
	ORDER BY BK.INDICATOR;

Line: 402

	BSC_USER_PARAMETERS_PKG.INSERT_ROW(l_paramlist_id,
	NULL,
	BSC_PORTLET_KPILISTCUST.APPLICATION_ID,
	l_kpi_id,
	1,1,
 	NULL,NULL,NULL,NULL,NULL,
	NULL,NULL,NULL,NULL,NULL,
 	NULL,NULL,NULL,NULL,NULL,
	p_last_update_date,
	p_last_updated_by,
	p_last_update_date,
	p_last_updated_by,
	p_last_updated_by);

Line: 418

END update_indicators;

Line: 436

PROCEDURE insert_param_id(
    p_plug_id IN NUMBER,
    p_resp_id IN NUMBER,
    p_last_update_date IN DATE,
    p_last_updated_by IN NUMBER,
    p_number_array IN BSC_NUM_LIST
) IS



BEGIN

    DELETE from bsc_user_kpilist_kpis
    WHERE plug_id = p_plug_id;

Line: 451

    INSERT INTO bsc_user_kpilist_kpis (
	PLUG_ID,
	PARAM_LIST_ID,
 	CREATION_DATE,
	CREATED_BY,
 	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN
    ) SELECT
	p_plug_id,
	bup.PARAM_LIST_ID,
   	p_last_update_date,
    	p_last_updated_by,
   	p_last_update_date,
    	p_last_updated_by,
    	p_last_updated_by
    FROM bsc_user_parameters_b bup,
	 BSC_KPIS_B BK,
	 BSC_TAB_INDICATORS BTI,
	 BSC_USER_TAB_ACCESS BTA,
	 BSC_USER_KPI_ACCESS BA
    WHERE
	BA.RESPONSIBILITY_ID = p_resp_id AND
	BA.INDICATOR IN (
            SELECT
                t.COLUMN_VALUE
	    FROM TABLE(CAST(p_number_array AS BSC_NUM_LIST)) t) AND
	BA.INDICATOR = BK.INDICATOR AND
	BTI.INDICATOR = BK.INDICATOR AND
	BTA.TAB_ID = BTI.TAB_ID AND
	BTA.RESPONSIBILITY_ID = BA.RESPONSIBILITY_ID AND
	SYSDATE BETWEEN NVL(BTA.START_DATE(+), SYSDATE) AND
	NVL(BTA.END_DATE(+), SYSDATE) AND
	BK.PROTOTYPE_FLAG <> 2 AND
	bup.INDICATOR = bk.INDICATOR AND
        bup. VIEW_TYPE = 1;

Line: 493

END insert_param_id;

Line: 520

    p_last_updated_by IN NUMBER,
    p_porlet_name IN VARCHAR2,
    p_number_array IN BSC_NUM_LIST,
    p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS

    insert_err  EXCEPTION;

Line: 526

    update_err  EXCEPTION;

Line: 532

     SELECT count(*)
     INTO   l_count
     FROM   BSC_USER_KPILIST_PLUGS k
     WHERE
	 k.PLUG_ID = p_plug_id;

Line: 544

    IF (l_count > 0) THEN  -- record exists, need to update
	update_row(p_plug_id, p_reference_path ,p_details_flag, p_group_flag, p_kpi_measure_details_flag, sysdate, p_last_updated_by);

Line: 546

    ELSE -- record does not exist, insert it
	insert_row(p_plug_id, p_reference_path, p_details_flag, p_group_flag, p_kpi_measure_details_flag, sysdate, p_last_updated_by);

Line: 554

    update_icx_portlet_cust(p_plug_id, p_reference_path, p_resp_id, p_porlet_name);

Line: 556

    update_indicators(p_resp_id, sysdate, p_last_updated_by, p_number_array);

Line: 558

    insert_param_id(p_plug_id, p_resp_id, sysdate, p_last_updated_by, p_number_array);

Line: 568

    WHEN insert_err THEN
        ROLLBACK;

Line: 571

        l_errmesg := 'Error inserting to BSC_USER_KPILIST_PLUGS';

Line: 575

    WHEN update_err THEN
        ROLLBACK;

Line: 617

    p_has_selected_kpi OUT NOCOPY NUMBER,
    p_kpi_measure_details_flag OUT NOCOPY NUMBER) RETURN NUMBER IS

    l_session_id NUMBER;

Line: 626

        SELECT p.RESPONSIBILITY_ID, NVL(k.DETAILS_FLAG, 0), NVL(k.GROUP_FLAG, 0), p.TITLE, NVL(k.kpi_measure_details_flag, 0)
        FROM   bsc_user_kpilist_plugs k,
               icx_portlet_customizations p
        WHERE
	    p.PLUG_ID = p_plug_id AND
	    k.PLUG_ID(+)= p.PLUG_ID;

Line: 635

	SELECT USER_FUNCTION_NAME
        FROM   FND_FORM_FUNCTIONS_VL
        WHERE  FUNCTION_ID = p_portlet_id;

Line: 665

	    -- checks if there is any selected kpis
            SELECT COUNT(*) INTO l_num_sel_kpis
            FROM bsc_user_kpilist_kpis
            WHERE plug_id = p_plug_id;

Line: 672

		p_has_selected_kpi := 0;

Line: 674

		p_has_selected_kpi := 1;