[Home] [Help]
158: params.weighted_parameter_id
159: BULK COLLECT INTO
160: l_Param_Ids
161: FROM
162: bis_weighted_measure_params params
163: WHERE
164: params.weighted_definition_id = p_weighted_definition_id;
165:
166: IF l_Param_Ids.COUNT > 0 THEN
185: END IF;
186:
187: FORALL i in 1..l_Param_Ids.COUNT
188: DELETE FROM
189: bis_weighted_measure_params
190: WHERE
191: weighted_parameter_id = l_Param_Ids(i);
192:
193: END IF;
987: --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
988: raise;
989: END Delete_WM_Definition;
990:
991: ------- APIs for table BIS_WEIGHTED_MEASURE_PARAMS
992:
993: /*******************************************************************
994: *******************************************************************/
995: PROCEDURE Create_WM_Parameter(
1024: END IF;
1025:
1026: -- Set the weighted_parameter_id
1027: IF p_Bis_WM_Rec.weighted_parameter_id IS NULL THEN
1028: SELECT BIS_WEIGHTED_MEASURE_PARAMS_S.NEXTVAL
1029: INTO l_Bis_WM_Rec.weighted_parameter_id
1030: FROM DUAL;
1031: END IF;
1032:
1334:
1335:
1336: SELECT COUNT(1)
1337: INTO l_count
1338: FROM BIS_WEIGHTED_MEASURE_PARAMS
1339: WHERE WEIGHTED_PARAMETER_ID = p_Bis_WM_Rec.weighted_parameter_id;
1340: IF l_count = 0 THEN
1341: FND_MESSAGE.SET_NAME('BIS','BIS_WM_INVALIDE_PARAM');
1342: FND_MESSAGE.SET_TOKEN('PARAM', p_Bis_WM_Rec.weighted_parameter_id);
2791: -- Cursor to count Weights <> 0 associated to a Dependent Measure
2792: CURSOR c_weights_by_dep_measure IS
2793: SELECT COUNT(C.WEIGHT_ID) C1
2794: FROM BIS_WEIGHTED_MEASURE_DEFNS A
2795: ,BIS_WEIGHTED_MEASURE_PARAMS B
2796: ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2797: WHERE A.weighted_measure_id = p_weighted_measure_id
2798: AND A.weighted_definition_id = B.weighted_definition_id
2799: AND B.weighted_parameter_id = C.weighted_parameter_id
2803: -- Cursor to count Weights associated to a Dependent Measure
2804: CURSOR c_weights0_by_dep_measure IS
2805: SELECT COUNT(WEIGHT_ID) C1
2806: FROM BIS_WEIGHTED_MEASURE_DEFNS A
2807: ,BIS_WEIGHTED_MEASURE_PARAMS B
2808: ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2809: WHERE A.weighted_measure_id = p_weighted_measure_id
2810: AND A.weighted_definition_id = B.weighted_definition_id
2811: AND B.weighted_parameter_id = C.weighted_parameter_id
2815: -- Cursor to count the Weights <> 0 associated to Weighted Measure
2816: CURSOR c_weights IS
2817: SELECT COUNT(WEIGHT_ID) C1
2818: FROM BIS_WEIGHTED_MEASURE_DEFNS A
2819: ,BIS_WEIGHTED_MEASURE_PARAMS B
2820: ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2821: WHERE A.weighted_measure_id = p_weighted_measure_id
2822: AND A.weighted_definition_id = B.weighted_definition_id
2823: AND B.weighted_parameter_id = C.weighted_parameter_id
2826: -- Cursor to count the Weights <> 0 associated to Weighted Measure
2827: CURSOR c_weights0 IS
2828: SELECT COUNT(WEIGHT_ID) C1
2829: FROM BIS_WEIGHTED_MEASURE_DEFNS A
2830: ,BIS_WEIGHTED_MEASURE_PARAMS B
2831: ,BIS_WEIGHTED_MEASURE_WEIGHTS C
2832: WHERE A.weighted_measure_id = p_weighted_measure_id
2833: AND A.weighted_definition_id = B.weighted_definition_id
2834: AND B.weighted_parameter_id = C.weighted_parameter_id
3214: params.weighted_parameter_id
3215: BULK COLLECT INTO
3216: l_Param_Ids
3217: FROM
3218: bis_weighted_measure_params params
3219: WHERE
3220: params.weighted_definition_id in (SELECT column_value FROM TABLE(CAST(l_Definition_Ids AS FND_TABLE_OF_NUMBER)));
3221:
3222: IF l_Param_Ids.COUNT > 0 THEN
3526: params.weighted_parameter_id
3527: BULK COLLECT INTO
3528: l_Param_Ids
3529: FROM
3530: bis_weighted_measure_params params,
3531: (SELECT
3532: a.column_value filter_level_value_id,
3533: b.column_value time_level_value_id
3534: FROM
3590: -- combinations are completely deleted before calling this
3591: -- Logic :
3592: -- 1. Finds the list of filter,time level combinations that are
3593: -- defined for the first time.
3594: -- 2. Inserts the entries found in step1 into bis_weighted_measure_params
3595: -- 3. Fetch parameter ids for all the combinations(inserted + updated)
3596: -- 4. Fetch the default weights for the corresponding definition
3597: -- 5. For all the combinations found in Step3 insert weights
3598: -- 6. Fetch the weights ids that are defined in step5
3625: l_Count NUMBER := 0;
3626: l_Is_Update BOOLEAN;
3627: l_User_Id NUMBER;
3628: l_Login_Id NUMBER;
3629: l_Weighted_Definition_Id bis_weighted_measure_params.weighted_definition_id%type;
3630:
3631: BEGIN
3632:
3633: SAVEPOINT SAVE_Insert_Mass_Update_Data;
3655: SELECT
3656: params.filter_level_value_id ,
3657: params.time_level_value_id
3658: FROM
3659: bis_weighted_measure_params params
3660: WHERE
3661: params.weighted_definition_id = p_weighted_definition_id AND
3662: params.time_level_value_id <> 'DEFAULT' AND
3663: params.filter_level_value_id <> 'DEFAULT') insertedParams;
3663: params.filter_level_value_id <> 'DEFAULT') insertedParams;
3664:
3665:
3666: FORALL i IN 1..l_Create_Time_Values.COUNT
3667: INSERT INTO bis_weighted_measure_params(
3668: weighted_parameter_id
3669: ,weighted_definition_id
3670: ,time_level_value_id
3671: ,filter_level_value_id
3674: ,last_update_date
3675: ,last_updated_by
3676: ,last_update_login
3677: ) VALUES (
3678: bis_weighted_measure_params_s.nextval
3679: ,p_weighted_definition_id
3680: ,l_Create_Time_Values(i)
3681: ,l_Create_Filter_Values(i)
3682: ,SYSDATE
3690: params.weighted_parameter_id
3691: BULK COLLECT INTO
3692: l_Param_Ids
3693: FROM
3694: bis_weighted_measure_params params,
3695: (SELECT
3696: a.column_value filter_level_value_id,
3697: b.column_value time_level_value_id
3698: FROM
3709: w.weight
3710: INTO
3711: l_Default_Weight
3712: FROM
3713: bis_weighted_measure_params p,
3714: bis_weighted_measure_weights w
3715: WHERE
3716: p.weighted_parameter_id = w.weighted_parameter_id AND
3717: p.time_level_value_id = 'DEFAULT' AND
3822: ,x_msg_count OUT NOCOPY NUMBER
3823: ,x_msg_data OUT NOCOPY VARCHAR2
3824: )
3825: IS
3826: l_Weighted_Definition_Id bis_weighted_measure_params.weighted_definition_id%type;
3827: BEGIN
3828:
3829: SAVEPOINT BisSave_Mass_Update_Values_Pub;
3830: x_return_status := FND_API.G_RET_STS_SUCCESS;
3924: params.weighted_parameter_id
3925: BULK COLLECT INTO
3926: l_Param_Ids
3927: FROM
3928: bis_weighted_measure_params params,
3929: bis_weighted_measure_defns defns,
3930: (SELECT
3931: a.column_value filter_level_value_id,
3932: b.column_value time_level_value_id