1 PACKAGE BODY CSI_WEB_SERVICE_PKG AS
2 /* $Header: csiwsb.pls 120.1 2007/12/04 17:40:57 fli noship $ */
3
4 -- --------------------------------------------------------
5 -- Define global variables
6 -- --------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSI_WEB_SERVICE_PKG';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csiwsb.pls';
9
10 /*----------------------------------------------------------*/
11 /* procedure name: get_item_instance_obj */
12 /* description : procedure used to get instance details */
13 /* given the instance id or instance number */
14 /*----------------------------------------------------------*/
15 PROCEDURE get_item_instance_obj
16 ( p_api_version IN NUMBER,
17 p_commit IN VARCHAR2 := FND_API.g_FALSE,
18 p_init_msg_list IN VARCHAR2 := FND_API.g_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 p_instance_id IN NUMBER,
21 p_instance_number IN VARCHAR2,
22 x_item_instance_obj OUT NOCOPY CSI_ITEM_INSTANCE_OBJ,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2)
26 IS
27 l_api_name CONSTANT VARCHAR2(30) := 'get_item_instance_obj';
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_debug_level NUMBER;
30 l_instance_id NUMBER;
31 l_msg_index NUMBER;
32 l_msg_count NUMBER;
33 BEGIN
34 -- Standard Start of API savepoint
35 SAVEPOINT get_item_instance_obj;
36
37 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
38 csi_utility_grp.check_ib_active;
39
40 -- Standard call to check for call compatibility.
41 IF NOT FND_API.Compatible_API_Call(l_api_version,
42 p_api_version,
43 l_api_name,
44 G_PKG_NAME) THEN
45 csi_gen_utility_pvt.put_line('Incompatible API call');
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48
49 -- Initialize message list if p_init_msg_list is set to TRUE.
50 IF FND_API.to_Boolean(p_init_msg_list) THEN
51 FND_MSG_PUB.initialize;
52 END IF;
53
54 -- Check the profile option debug_level for debug message reporting
55 l_debug_level := fnd_profile.value('CSI_DEBUG_LEVEL');
56
57 -- If debug_level = 1 then dump the procedure name
58 IF (l_debug_level > 0) THEN
59 csi_gen_utility_pvt.put_line('CSI_WEB_SERVICE_PKG.get_item_instance_obj');
60 END IF;
61
62 -- If the debug level = 2 then dump all the parameters values.
63 IF (l_debug_level > 1) THEN
64 csi_gen_utility_pvt.put_line('get_item_instance_obj ' ||
65 p_api_version ||'-'||
66 p_commit ||'-'||
67 p_init_msg_list ||'-'||
68 p_validation_level );
69 csi_gen_utility_pvt.put_line(' Instance Id : '||p_instance_id);
70 csi_gen_utility_pvt.put_line(' Instance Number : '||p_instance_number);
71 END IF;
72
73 -- Initialize API return status to success
74 x_return_status := FND_API.G_RET_STS_SUCCESS;
75
76 IF p_instance_id IS NOT NULL THEN
77 IF p_instance_number IS NOT NULL THEN
78 BEGIN
79 SELECT csi.instance_id
80 INTO l_instance_id
81 FROM csi_item_instances csi
82 WHERE csi.instance_id = p_instance_id
83 AND csi.instance_number = p_instance_number;
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 IF (l_debug_level > 1) THEN
87 csi_gen_utility_pvt.put_line(' ERROR-CSI_CANT_GET_ITEM_INSTANCE: '
88 ||'No item found with instance id '||p_instance_id
89 ||' and instance number '||p_instance_number);
90 END IF;
91 FND_MESSAGE.SET_NAME('CSI','CSI_CANT_GET_ITEM_INSTANCE');
92 FND_MESSAGE.SET_TOKEN('ERROR','No item found with instance id '
93 ||p_instance_id||' and instance number '||p_instance_number);
94 FND_MSG_PUB.Add;
95 RAISE FND_API.G_EXC_ERROR;
96 WHEN OTHERS THEN
97 IF (l_debug_level > 1) THEN
98 csi_gen_utility_pvt.put_line(' ERROR-CSI_CANT_GET_ITEM_INSTANCE: '
99 ||SQLERRM);
100 END IF;
101 FND_MESSAGE.SET_NAME('CSI','CSI_CANT_GET_ITEM_INSTANCE');
102 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
103 FND_MSG_PUB.Add;
104 RAISE FND_API.G_EXC_ERROR;
105 END;
106 ELSE
107 l_instance_id := p_instance_id;
108 END IF;
109 ELSIF p_instance_number IS NOT NULL THEN
110 BEGIN
111 SELECT csi.instance_id
112 INTO l_instance_id
113 FROM csi_item_instances csi
114 WHERE csi.instance_number = p_instance_number;
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 IF (l_debug_level > 1) THEN
118 csi_gen_utility_pvt.put_line(' ERROR-CSI_CANT_GET_ITEM_INSTANCE: '
119 ||'No item found with instance number '||p_instance_number);
120 END IF;
121 FND_MESSAGE.SET_NAME('CSI','CSI_CANT_GET_ITEM_INSTANCE');
122 FND_MESSAGE.SET_TOKEN('ERROR','No item found with instance number '
123 ||p_instance_number);
124 FND_MSG_PUB.Add;
125 RAISE FND_API.G_EXC_ERROR;
126 WHEN OTHERS THEN
127 IF (l_debug_level > 1) THEN
128 csi_gen_utility_pvt.put_line(' ERROR-CSI_CANT_GET_ITEM_INSTANCE: '
129 ||SQLERRM);
130 END IF;
131 FND_MESSAGE.SET_NAME('CSI','CSI_CANT_GET_ITEM_INSTANCE');
132 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
133 FND_MSG_PUB.Add;
134 RAISE FND_API.G_EXC_ERROR;
135 END;
136 ELSE
137 IF (l_debug_level > 1) THEN
138 csi_gen_utility_pvt.put_line(' ERROR-CSI_CANT_GET_ITEM_INSTANCE: '
139 ||'Must specify at least instance id or instance number');
140 END IF;
141 FND_MESSAGE.SET_NAME('CSI','CSI_CANT_GET_ITEM_INSTANCE');
142 FND_MESSAGE.SET_TOKEN('ERROR','Must specify at least instance id or instance number');
143 FND_MSG_PUB.Add;
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146
147 IF l_instance_id IS NOT NULL THEN
148 x_item_instance_obj := CSI_ITEM_INSTANCE_OBJ(l_instance_id);
149 END IF;
150
151 -- Standard check of p_commit.
152 IF FND_API.To_Boolean( p_commit ) THEN
153 COMMIT WORK;
154 END IF;
155
156 -- Standard call to get message count and if count is get message info.
157 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
158 p_data => x_msg_data);
159 EXCEPTION
160 WHEN OTHERS THEN
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 ROLLBACK TO get_item_instance_obj;
163 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
164 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
165 l_api_name);
166 END IF;
167 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
168 p_data => x_msg_data);
169 END get_item_instance_obj;
170
171 END;
[Home] [Help]