1 PACKAGE BODY AHL_STATUS_ORDER_RULES_PVT AS
2 /* $Header: AHLVSORB.pls 115.1 2003/10/20 19:37:12 sikumar noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_STATUS_ORDER_RULES_PVT';
5 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7
8 --
9 -----------------------------------------------------------
10 -- PACKAGE
11 -- AHL_STATUS_ORDER_RULES_PVT
12 --
13 -- PURPOSE
14 -- This package is a Private API for retrieving the valid
15 -- statuses for the current status
16 -- Advanced Services Online. It contains specification for pl/sql records and tables
17 --
18 -- AHL_STATUS_ORDER_RULES
19 -- Get_Valid_Status_Order_Values (see below for specification)
20 --
21 --
22 -- NOTES
23 --
24 --
25 -- HISTORY
26 -- 09-May-2003 sdevaki Created
27
28 -------------------------------------------------------------
29 -- procedure name: Get_Status_Order_Rules(private procedure)
30 -- description : To Retrieve the valid Status Order Rules for the current Status
31 --------------------------------------------------------------
32
33 PROCEDURE Get_Status_Order_Rules (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
36 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
37 p_module_type IN VARCHAR2 := 'JSP',
38 p_current_status_code IN VARCHAR2,
39 p_system_status_type IN VARCHAR2,
40 x_status_order_rules_tbl OUT NOCOPY Status_Order_Rules_Tbl,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2
44 )
45
46 IS
47
48
49 l_api_name CONSTANT VARCHAR2(30) := 'Get_Status_Order_Rules';
50 l_api_version CONSTANT NUMBER := 1.0;
51
52 l_index NUMBER := 0;
53
54 CURSOR status_order_rules_cur
55 ( c_current_status_code
56 AHL_STATUS_ORDER_RULES.CURRENT_STATUS_CODE%TYPE,
57 c_system_status_type
58 AHL_STATUS_ORDER_RULES.SYSTEM_STATUS_TYPE%TYPE
59 )
60 IS
61 SELECT
62 SOR.NEXT_STATUS_CODE,
63 FND.MEANING
64
65 FROM
66 AHL_STATUS_ORDER_RULES SOR, FND_LOOKUP_VALUES_VL FND
67 WHERE
68 SOR.CURRENT_STATUS_CODE = c_current_status_code AND
69 SOR.SYSTEM_STATUS_TYPE = c_system_status_type AND
70 FND.LOOKUP_TYPE(+) = c_system_status_type AND
71 FND.LOOKUP_CODE(+) = SOR.NEXT_STATUS_CODE;
72
73 v_status_order_rules_rec status_order_rules_cur%ROWTYPE;
74
75 BEGIN
76
77 -- Check if API is called in debug mode. If yes, enable debug.
78 IF G_DEBUG='Y' THEN
79 --FND_PROFILE.put('AHL_API_FILE_DEBUG_NAME','ahlsdevakidebug.log');
80 AHL_DEBUG_PUB.enable_debug;
81 END IF;
82 -- Debug info.
83 IF G_DEBUG='Y' THEN
84 AHL_DEBUG_PUB.debug( 'enter ahl_status_order_rules_pvt.get_status_order_rules','+STORULE+');
85 END IF;
86 -- Standard call to check for call compatibility.
87 IF FND_API.to_boolean(p_init_msg_list)
88 THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91 -- Initialize API return status to success
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
95 p_api_version,
96 l_api_name,G_PKG_NAME)
97 THEN
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100
101 IF G_DEBUG='Y'
102 THEN
103 AHL_DEBUG_PUB.debug( 'p_current_status_code ' || p_current_status_code);
104 AHL_DEBUG_PUB.debug( 'p_system_status_type ' || p_system_status_type);
105 END IF;
106
107 OPEN status_order_rules_cur( p_current_status_code, p_system_status_type );
108
109 LOOP
110 FETCH status_order_rules_cur INTO v_status_order_rules_rec;
111
112 EXIT WHEN status_order_rules_cur%NOTFOUND;
113
114 x_status_order_rules_tbl(l_index).next_status_code := v_status_order_rules_rec.next_status_code;
115 x_status_order_rules_tbl(l_index).next_status_meaning := v_status_order_rules_rec.meaning;
116
117 IF G_DEBUG='Y'
118 THEN
119 AHL_DEBUG_PUB.debug( 'next_status_code ' || v_status_order_rules_rec.next_status_code );
120 AHL_DEBUG_PUB.debug( 'meaning ' || v_status_order_rules_rec.meaning );
121 AHL_DEBUG_PUB.debug( 'l_index ' || l_index );
122 END IF;
123
124 l_index := l_index + 1;
125
126 END LOOP;
127
128 IF G_DEBUG='Y'
129 THEN
130 AHL_DEBUG_PUB.debug( 'Number of Status Order Rules is : ' || status_order_rules_cur%ROWCOUNT );
131 END IF;
132
133 CLOSE status_order_rules_cur;
134
135 -- Check if API is called in debug mode. If yes, enable debug.
136
137
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 x_return_status := Fnd_Api.G_RET_STS_ERROR;
141 IF G_DEBUG='Y' THEN
142 AHL_DEBUG_PUB.disable_debug;
143 END IF;
144
145 WHEN TOO_MANY_ROWS THEN
146 x_return_status := Fnd_Api.G_RET_STS_ERROR;
147 IF G_DEBUG='Y' THEN
148 AHL_DEBUG_PUB.disable_debug;
149 END IF;
150
151 WHEN OTHERS THEN
152 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
153 IF G_DEBUG='Y' THEN
154 AHL_DEBUG_PUB.disable_debug;
155 END IF;
156 RAISE;
157 END Get_Status_Order_Rules;
158
159 END AHL_STATUS_ORDER_RULES_PVT;