[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_TYPES_PVT
Source
1 PACKAGE BODY CSD_REPAIR_TYPES_PVT as
2 /* $Header: csdvrtdb.pls 120.0 2005/06/30 21:09:42 vkjain noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIR_TYPES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrtdb.pls';
6
7 /*--------------------------------------------------*/
8 /* procedure name: Get_Start_Flow_Status */
9 /* description : The procedure returns the start */
10 /* flow status and status or */
11 /* a given repair type. */
12 /* */
13 /*--------------------------------------------------*/
14 PROCEDURE Get_Start_Flow_Status
15 (
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_repair_type_id IN NUMBER,
20 x_start_flow_status_id OUT NOCOPY NUMBER,
21 x_start_flow_status_code OUT NOCOPY VARCHAR2,
22 x_start_flow_status_meaning OUT NOCOPY VARCHAR2,
23 x_status_code OUT NOCOPY VARCHAR2
24 ) IS
25
26 -- CONSTANTS --
27 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIR_TYPES_PVT.get_start_flow_status';
28 lc_api_name CONSTANT VARCHAR2(30) := 'Get_Start_Flow_Status';
29 lc_api_version CONSTANT NUMBER := 1.0;
30 lc_FLOW_STATUS_LOOKUP_TYPE CONSTANT VARCHAR2(30) := 'CSD_REPAIR_FLOW_STATUS';
31
32 -- VARIABLES --
33 l_msg_count NUMBER;
34 l_msg_data VARCHAR2(100);
35 l_msg_index NUMBER;
36 l_dummy VARCHAR2(1) := null;
37 l_obj_ver_num NUMBER := 1;
38 l_rowid ROWID;
39
40 -- CURSORS --
41 CURSOR cursor_get_start_status IS
42 SELECT RT_B.START_FLOW_STATUS_ID,
43 FS_B.FLOW_STATUS_CODE START_FLOW_STATUS_CODE,
44 FS_LKUP.MEANING START_FLOW_STATUS_MEANING,
45 FS_B.STATUS_CODE STATUS_CODE
46 FROM CSD_REPAIR_TYPES_B RT_B,
47 CSD_FLOW_STATUSES_B FS_B,
48 FND_LOOKUPS FS_LKUP
49 WHERE RT_B.REPAIR_TYPE_ID = p_repair_type_id AND
50 FS_B.FLOW_STATUS_ID = RT_B.START_FLOW_STATUS_ID AND
51 FS_LKUP.lookup_type = lc_FLOW_STATUS_LOOKUP_TYPE AND
52 FS_LKUP.enabled_flag = 'Y' AND
53 TRUNC(SYSDATE) BETWEEN
54 TRUNC(NVL(FS_LKUP.start_date_active, SYSDATE)) AND
55 TRUNC(NVL(FS_LKUP.end_date_active, SYSDATE)) AND
56 FS_LKUP.lookup_code = FS_B.FLOW_STATUS_CODE;
57
58 BEGIN
59 -- Standard Start of API savepoint
60 SAVEPOINT Get_Start_Flow_Status;
61
62 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
63 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
64 'Entered Get_Start_Flow_Status');
65 END IF;
66
67 -- Initialize API return status to success
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 -- Api body starts
71
72 -- Check the required parameters
73 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
74 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name,
75 'Checking required parameters');
76 end if;
77
78 -- Check the required parameters
79 CSD_PROCESS_UTIL.Check_Reqd_Param
80 ( p_param_value => p_repair_type_id,
81 p_param_name => 'P_REPAIR_TYPE_ID',
82 p_api_name => lc_api_name);
83
84 -- Initialize the out parameter.
85 x_start_flow_status_id := NULL;
86
87 -- This will never return more than one rows,
88 -- as repair_type_id is unique.
89 FOR irow IN cursor_get_start_status LOOP
90 x_start_flow_status_id := irow.start_flow_status_id;
91 x_start_flow_status_code := irow.start_flow_status_code;
92 x_start_flow_status_meaning := irow.start_flow_status_meaning;
93 x_status_code := irow.status_code;
94 END LOOP;
95
96 IF x_start_flow_status_id IS NULL THEN
97 -- Unable to get the start status of the repair type.
98 -- For the repair type, either the start status has
99 -- not been defined or is not active.
100 FND_MESSAGE.Set_Name('CSD', 'CSD_RT_START_STATUS_NOT_FOUND');
101 FND_MSG_PUB.ADD;
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105 -- Api body ends here
106
107 -- Standard call to get message count and IF count is get message info.
108 FND_MSG_PUB.Count_And_Get
109 (p_count => x_msg_count,
110 p_data => x_msg_data );
111
112 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
113 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, lc_mod_name || '.END',
114 'Leaving Get_Start_Flow_Status');
115 END IF;
116
117 EXCEPTION
118 WHEN FND_API.G_EXC_ERROR THEN
119 ROLLBACK TO Get_Start_Flow_Status;
120 x_return_status := FND_API.G_RET_STS_ERROR;
121
122 FND_MSG_PUB.Count_And_Get
123 (p_count => x_msg_count,
124 p_data => x_msg_data );
125
126 -- save message in debug log
127 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
128 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
129 'EXC_ERROR['||x_msg_data||']');
130 END IF;
131
132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
133 ROLLBACK TO Get_Start_Flow_Status;
134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
135
136 -- save message in fnd stack
137 IF FND_MSG_PUB.Check_Msg_Level
138 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
139 THEN
140 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
141 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
142 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
143 end if;
144 FND_MSG_PUB.Add_Exc_Msg
145 (G_PKG_NAME ,
146 lc_api_name );
147 END IF;
148
149 FND_MSG_PUB.Count_And_Get
150 ( p_count => x_msg_count,
151 p_data => x_msg_data );
152
153 -- save message in debug log
154 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
155 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
156 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
157 END IF;
158
159 WHEN OTHERS THEN
160 ROLLBACK TO Get_Start_Flow_Status;
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162
163 -- save message in fnd stack
164 IF FND_MSG_PUB.Check_Msg_Level
165 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
166 THEN
167 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
168 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name,
169 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
170 end if;
171 FND_MSG_PUB.Add_Exc_Msg
172 (G_PKG_NAME ,
173 lc_api_name );
174 END IF;
175
176 FND_MSG_PUB.Count_And_Get
177 (p_count => x_msg_count,
178 p_data => x_msg_data );
179
180 -- save message in debug log
181 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
182 -- create a seeded message
183 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, lc_mod_name,
184 'SQL Message['||sqlerrm||']' );
185 END IF;
186
187 END Get_Start_Flow_Status;
188
189 End CSD_REPAIR_TYPES_PVT;