[Home] [Help]
1: PACKAGE BODY Ams_Approvers_Pvt AS
2: /* $Header: amsvaprb.pls 120.0 2005/05/31 20:24:52 appldev noship $ */
3:
4: -----------------------------------------------------------
5: -- PACKAGE
2: /* $Header: amsvaprb.pls 120.0 2005/05/31 20:24:52 appldev noship $ */
3:
4: -----------------------------------------------------------
5: -- PACKAGE
6: -- AMS_APPROVERS_PVT
7: --
8: --
9: -- PURPOSE
10: -- This package is a Private API for managing Approvers
9: -- PURPOSE
10: -- This package is a Private API for managing Approvers
11: -- in AMS. It contains specification for pl/sql records and tables
12: --
13: -- AMS_APPROVERS:
14: -- Create_approver (see below for specification)
15: -- Update_approver (see below for specification)
16: -- Delete_approver (see below for specification)
17: -- Lock_approver (see below for specification)
38: -- 24-MAR-2005 vmodur SQL Repository Fixes
39: -----------------------------------------------------------
40:
41: -- Global CONSTANTS
42: G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_APPROVERS_PVT';
43:
44: AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
45: AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
46: AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
140: l_return_status VARCHAR2(1);
141: l_row_id VARCHAR2(40);
142:
143: CURSOR c_seq IS
144: SELECT ams_approvers_s.NEXTVAL
145: FROM dual;
146:
147: CURSOR c_id_exists (x_id IN NUMBER) IS
148: SELECT 1 FROM dual
145: FROM dual;
146:
147: CURSOR c_id_exists (x_id IN NUMBER) IS
148: SELECT 1 FROM dual
149: WHERE EXISTS (SELECT 1 FROM ams_approvers
150: WHERE approver_id = x_id);
151: BEGIN
152: --------------------- initialize -----------------------
153: SAVEPOINT Create_Approvers;
218: END IF;
219: --
220: -- Insert into mutli-language supported table.
221: --
222: Ams_Approvers_Pkg.INSERT_ROW (
223: X_ROWID => l_row_id,
224: X_APPROVER_ID => l_approvers_rec.approver_id,
225: X_SEEDED_FLAG => NVL(l_approvers_rec.SEEDED_FLAG, 'N'),
226: X_ACTIVE_FLAG => NVL(l_approvers_rec.ACTIVE_FLAG, 'Y'),
297: -- PURPOSE
298: -- Update an approvers entry.
299: --
300: -- PARAMETERS
301: -- p_approvers_rec: the record representing AMS_APPROVERS (without the ROW_ID column).
302: --
303: -- NOTES
304: -- 1. Raise exception if the object_version_number doesn't match.
305: -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
399: IF (AMS_DEBUG_HIGH_ON) THEN
400:
401: Ams_Utility_Pvt.debug_message (l_full_name || ': Update');
402: END IF;
403: Ams_Approvers_Pkg.UPDATE_ROW(
404: X_APPROVER_ID => l_approvers_rec.approver_id,
405: X_SEEDED_FLAG => l_approvers_rec.SEEDED_FLAG,
406: X_ACTIVE_FLAG => l_approvers_rec.ACTIVE_FLAG,
407: X_START_DATE_ACTIVE => l_approvers_rec.START_DATE_ACTIVE,
498: ) IS
499:
500: CURSOR c_approvers IS
501: SELECT *
502: FROM ams_approvers
503: WHERE approver_id = p_approver_id;
504: --
505: -- This is the only exception for using %ROWTYPE.
506: -- We are selecting from the VL view, which may
551: END IF;
552: -- Delete TL data
553: IF l_approvers_rec.seeded_flag='N'
554: THEN
555: Ams_Approvers_Pkg.DELETE_ROW (p_approver_id);
556: ELSE
557: IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
558: Fnd_Message.set_name ('AMS', 'AMS_API_SEED_DATA');
559: Fnd_Msg_Pub.ADD;
648: -- PURPOSE
649: -- Validate a approvers entry.
650: --
651: -- PARAMETERS
652: -- p_approvers_rec: the record representing AMS_APPROVERS (without ROW_ID).
653: --
654: -- NOTES
655: -- 1. p_approvers_rec should be the complete approvers record.
656: -- There should not be any FND_API.g_miss_char/num/date in it.
938: x_complete_rec OUT NOCOPY Approvers_Rec_Type
939: ) IS
940: CURSOR c_approvers IS
941: SELECT *
942: FROM ams_approvers
943: WHERE approver_id = p_approvers_rec.approver_id;
944: --
945: -- This is the only exception for using %ROWTYPE.
946: -- We are selecting from the VL view, which may
1042: IF p_approvers_rec.start_date_active IS NOT NULL THEN
1043: IF p_approvers_rec.end_Date_active IS NOT NULL THEN
1044: BEGIN
1045: SELECT 1 INTO l_count
1046: FROM AMS_APPROVERS
1047: WHERE approver_id =p_approvers_rec.approver_id
1048: AND start_date_active = p_approvers_rec.start_date_active
1049: AND end_date_active = p_approvers_rec.end_Date_active
1050: --AND security_group_id = p_approvers_rec.security_group_id
1061: END;
1062: ELSE -- for end date
1063: BEGIN
1064: SELECT 1 INTO l_count
1065: FROM AMS_APPROVERS
1066: WHERE approver_id =p_approvers_rec.approver_id
1067: AND start_date_active = p_approvers_rec.start_date_active
1068: AND end_date_active = p_approvers_rec.end_Date_active
1069: --AND security_group_id = p_approvers_rec.security_group_id
1081: END IF; -- for end date
1082: ELSE
1083: BEGIN
1084: SELECT 1 INTO l_count
1085: FROM AMS_APPROVERS
1086: WHERE approver_id =p_approvers_rec.approver_id
1087: AND start_date_active = p_approvers_rec.start_date_active
1088: AND end_date_active = p_approvers_rec.end_Date_active
1089: --AND security_group_id = p_approvers_rec.security_group_id
1120:
1121: BEGIN
1122: BEGIN
1123: SELECT 1 INTO l_count
1124: FROM AMS_APPROVERS
1125: WHERE approver_id = p_approvers_rec.approver_id
1126: AND seeded_flag = 'Y';
1127: EXCEPTION
1128: WHEN NO_DATA_FOUND THEN
1217: )IS
1218: l_dummy NUMBER;
1219: CURSOR c_appr_seq_exists(seq_num_in IN NUMBER, id_in IN NUMBER) IS
1220: SELECT 1 FROM dual
1221: WHERE EXISTS (SELECT 1 FROM ams_approvers
1222: WHERE approver_seq = seq_num_in
1223: AND ams_approval_detail_id = id_in
1224: AND active_flag = 'Y'
1225: );
1558: SELECT start_date_active , end_date_active FROM ams_approval_details
1559: WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;
1560:
1561: CURSOR c_approver IS
1562: SELECT start_date_active , end_date_active FROM ams_approvers
1563: WHERE approver_id = p_approvers_rec.approver_id;
1564:
1565: BEGIN
1566:
1808: END IF;
1809:
1810: END Check_Func_Use_Valid;
1811:
1812: END Ams_Approvers_Pvt;