[Home] [Help]
PACKAGE BODY: APPS.EAM_PN_EXTRACTION_PUB
Source
1 PACKAGE BODY EAM_PN_EXTRACTION_PUB AS
2 /* $Header: EAMPNXPB.pls 120.1 2006/02/09 20:43:11 hkarmach noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_PN_EXTRACTION_PUB';
4
5 Function pn_check_eam_asset(p_location_id in NUMBER) return BOOLEAN
6 IS
7 l_asset_exists varchar2(1) := 'N';
8 begin
9
10 select 'Y' into l_asset_exists
11 from dual
12 where exists
13 ( select * from csi_item_instances
14 where pn_location_id = p_location_id
15 and nvl(active_start_date, sysdate-1) < sysdate
16 and nvl(active_end_date, sysdate+1) > sysdate);
17
18 if (l_asset_exists = 'Y') then
19 return TRUE;
20 else
21 return FALSE;
22 end if;
23
24 end pn_check_eam_asset;
25
26 PROCEDURE pn_eam_export_mode(
27 P_API_VERSION IN NUMBER,
28 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
29 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
30 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31 P_PN_LOCATION_ID IN NUMBER,
32 P_PARENT_LOCATION_ID IN NUMBER,
33 P_ACTIVE_START_DATE IN DATE,
34 P_ACTIVE_END_DATE IN DATE,
35 X_INSERT OUT NOCOPY NUMBER,
36 X_INSERT_MODE OUT NOCOPY NUMBER,
37 X_INSERT_STATUS OUT NOCOPY NUMBER,
38 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
39 X_MSG_COUNT OUT NOCOPY NUMBER,
40 X_MSG_DATA OUT NOCOPY VARCHAR2)
41 IS
42
43 -- X_INSERT, if equals 1 then the row will be inserted in the interface tables.
44 -- X_INSERT_MODE => 0 - create a new row and 1 - Update the existing row
45 -- X_INSERT_STATUS, specifies the current status of Asset Number (3 Resides in stores )
46 -- X_RETURN_STATUS, X_MSG_COUNT OUT NOCOPY NUMBER, X_MSG_DATA OUT NOCOPY VARCHAR2
47 -- Standard API out parameter (for error handling).
48
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_api_name CONSTANT VARCHAR2(30) := 'PN_EAM_EXPORT_MODE';
51
52 l_stmt_num number := 0;
53 l_instance_id number;
54 l_gen_object_id number;
55 l_INVENTORY_ITEM_ID NUMBER;
56 l_SERIAL_NUMBER NUMBER;
57 l_ORGANIZATION_ID NUMBER;
58 l_pn_exists_in_eam varchar2(1) := 'N';
59 l_parent_exists_in_eam varchar2(1) := 'N';
60 l_hr_exists varchar2(1) := 'N';
61 l_pn_start_date date;
62 l_pn_end_date date;
63 l_start_date date;
64 l_end_date date;
65 l_parent_exists_in_mog number := 0;
66
67 BEGIN
68
69 -- Standard Start of API savepoint
70 l_stmt_num := 10;
71 SAVEPOINT pn_eam_export_mode_PUB;
72
73 l_stmt_num := 20;
74
75 -- Standard call to check for call compatibility.
76 IF NOT fnd_api.compatible_api_call(
77 l_api_version
78 ,p_api_version
79 ,l_api_name
80 ,g_pkg_name) THEN
81 RAISE fnd_api.g_exc_unexpected_error;
82 END IF;
83
84 l_stmt_num := 30;
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 IF fnd_api.to_boolean(p_init_msg_list) THEN
87 fnd_msg_pub.initialize;
88 END IF;
89
90 x_insert := 1;
91
92 l_gen_object_id := -1;
93
94 --Does PN exist in MSN?
95
96 begin
97 -- Bug # 3372982
98 SELECT cii.active_start_date, cii.active_end_date, msn.gen_object_id, cii.instance_id
99 INTO l_start_date, l_end_date, l_gen_object_id, l_instance_id
100 FROM mtl_serial_numbers msn, csi_item_instances cii
101 WHERE cii.pn_location_id = p_pn_location_id
102 and msn.current_organization_id = cii.last_vld_organization_id
103 and msn.inventory_item_id = cii.inventory_item_id
104 and msn.serial_number = cii.serial_number;
105
106 exception
107 when no_data_found then
108 l_gen_object_id := -1;
109 end;
110
111 if l_gen_object_id <> -1 then
112 x_insert := 1;
113 x_insert_mode := 1;
114 x_insert_status := 4;
115
116 begin
117 select 'Y' into l_parent_exists_in_eam from dual
118 where exists
119 (select * from csi_item_instances where
120 pn_location_id = p_parent_location_id);
121
122 exception
123 when no_data_found then
124 l_parent_exists_in_eam := 'N';
125 end;
126
127
128 if l_parent_exists_in_eam <> 'Y' then
129 x_insert := 1;
130 x_insert_mode := 1;
131 x_insert_status := 4;
132 else
133 begin
134 select 'Y' into l_hr_exists from dual
135 where exists
136 (select * from mtl_object_genealogy
137 where object_id = l_gen_object_id);
138 exception
139 when no_data_found then
140 l_hr_exists := 'N';
141 end;
142
143 if l_hr_exists <> 'Y' then
144 x_insert := 1;
145 x_insert_mode := 1;
146 x_insert_status := 4;
147 else
148 x_insert := 1;
149 x_insert_mode := 1;
150 x_insert_status := 4;
151
152 select pl.active_start_date, pl.active_end_date
153 into l_pn_start_date, l_pn_end_date
154 from pn_locations_all pl
155 where location_id = p_pn_location_id;
156
157 -- Bug # 3372982
158 SELECT COUNT(*)
159 INTO l_parent_exists_in_mog
160 FROM mtl_object_genealogy mog, mtl_serial_numbers msn
161 WHERE mog.object_id = l_gen_object_id
162 AND msn.gen_object_id = mog.parent_object_id
163 AND mog.genealogy_type = 5
164 AND mog.start_date_active = l_pn_start_date
165 AND ( mog.end_date_active = l_pn_end_date OR
166 (l_pn_end_date IS NULL and mog.end_date_active is NULL))
167 AND rownum = 1 ;
168
169 IF l_parent_exists_in_mog = 1 THEN
170 x_insert := 0;
171 END IF;
172 end if;
173 end if;
174 else
175 x_insert := 1;
176 x_insert_mode := 0;
177 x_insert_status := 4;
178 end if;
179
180
181 EXCEPTION
182 WHEN fnd_api.g_exc_error THEN
183
184 ROLLBACK TO pn_eam_export_mode_PUB;
185 x_return_status := fnd_api.g_ret_sts_error;
186 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
187
188 WHEN fnd_api.g_exc_unexpected_error THEN
189
190 ROLLBACK TO pn_eam_export_mode_PUB;
191 x_return_status := fnd_api.g_ret_sts_unexp_error;
192 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
193
194 WHEN OTHERS THEN
195
196 ROLLBACK TO pn_eam_export_mode_PUB;
197 x_return_status := fnd_api.g_ret_sts_unexp_error;
198
199 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
200 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
201 END IF;
202
203 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
204
205 END PN_EAM_EXPORT_MODE;
206
207
208 END EAM_PN_EXTRACTION_PUB;