[Home] [Help]
PACKAGE BODY: APPS.EAM_MATERIAL_REQUEST_PVT
Source
1 PACKAGE BODY EAM_MATERIAL_REQUEST_PVT AS
2 /* $Header: EAMWEMRB.pls 120.2 2007/12/12 02:28:02 mashah ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_material_request_pvt';
4
5 --author: dgupta
6 --This procedure allocates material by calling WIP
7 PROCEDURE allocate(
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2,
10 p_commit IN VARCHAR2,
11 p_validation_level IN NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_request_number OUT NOCOPY VARCHAR2,
16 p_wip_entity_type IN NUMBER,
17 p_organization_id IN NUMBER,
18 p_wip_entity_id IN NUMBER,
19 p_operation_seq_num IN NUMBER,
20 p_inventory_item_id IN NUMBER,
21 p_project_id IN NUMBER,
22 p_task_id IN NUMBER,
23 p_requested_quantity IN NUMBER,
24 p_source_subinventory IN VARCHAR2,
25 p_source_locator IN NUMBER,
26 p_lot_number IN VARCHAR2,
27 p_fm_serial IN VARCHAR2,
28 p_to_serial IN VARCHAR2
29 ) IS
30
31 l_api_name CONSTANT VARCHAR2(30) := 'allocate';
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_full_name CONSTANT VARCHAR2(100) := g_pkg_name || '.' || l_api_name;
34 l_module CONSTANT VARCHAR2(60) := 'eam.plsql.'||l_full_name;
35 l_msg_data VARCHAR2(2000);
36 l_msg VARCHAR2(2000);
37 l_return_status VARCHAR2(2000);
38 allocate_table wip_picking_pub.allocate_comp_tbl_t;
39 l_pickslip_conc_req_id NUMBER := 0;
40 l_current_log_level constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
41 l_uLog constant BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= l_current_log_level;
42 l_pLog constant BOOLEAN := l_uLog AND FND_LOG.LEVEL_PROCEDURE >= l_current_log_level;
43 l_sLog constant BOOLEAN := l_pLog AND FND_LOG.LEVEL_STATEMENT >= l_current_log_level;
44 l_partial_qty NUMBER := 0;
45 l_project_id number := p_project_id;
46 l_task_id number := p_task_id;
47
48 BEGIN
49 -- Standard Start of API savepoint
50 SAVEPOINT ALLOCATE_EAM;
51 -- Standard call to check for call compatibility.
52 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
53 l_api_name, G_PKG_NAME )
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57 -- Initialize message list if p_init_msg_list is set to TRUE.
58 IF FND_API.to_Boolean( p_init_msg_list ) THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61 -- Initialize API return status to success
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 -- API body
65 l_msg_data := NULL;
66 l_return_status := NULL;
67 x_msg_data := null;
68 x_msg_count := 0;
69
70 --check for invalid parameters
71 if (p_requested_quantity = 0) then
72 return;
73 end if;
74 if (p_requested_quantity < 0) then
75 x_return_status := FND_API.G_RET_STS_ERROR;
76 fnd_message.set_name('EAM','EAM_MR_NEGATIVE_REQ_QTY');
77 fnd_message.set_token('REQUESTED', to_char(p_requested_quantity) );
78 fnd_msg_pub.add;
79 FND_MSG_PUB.Count_And_Get(
80 p_count => x_msg_count,
81 p_data => x_msg_data);
82 return;
83 end if;
84
85 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
86 'Start of ' || l_full_name || '('
87 || 'p_commit='|| p_commit ||','
88 || 'p_wip_entity_type='|| p_wip_entity_type || ','
89 || 'p_organization_id='|| p_organization_id || ','
90 || 'p_wip_entity_id='|| p_wip_entity_id || ','
91 || 'p_operation_seq_num='|| p_operation_seq_num || ','
92 || 'p_inventory_item_id='|| p_inventory_item_id || ','
93 || 'p_project_id='|| p_project_id || ','
94 || 'p_task_id='|| p_task_id || ','
95 || 'p_requested_quantity='|| p_requested_quantity || ','
96 || 'p_source_subinventory='|| p_source_subinventory ||','
97 || 'p_source_locator='|| p_source_locator ||','
98 || 'p_lot_number='|| p_lot_number ||','
99 || 'p_fm_serial='|| p_fm_serial ||','
100 || 'p_to_serial='|| p_to_serial ||','
101 || ')');
102 end if;
103 -- if project or task info is missing, retrieve it, else carry on
104 if ((p_project_id is null) or (p_task_id is null)) then
105 select project_id, task_id into l_project_id, l_task_id
106 from wip_discrete_jobs
107 where wip_entity_id = p_wip_entity_id;
108 else
109 if (p_project_id = FND_API.G_MISS_NUM) then
110 l_project_id := null;
111 end if;
112 if (p_task_id = FND_API.G_MISS_NUM) then
113 l_task_id := null;
114 end if;
115 end if;
116 allocate_table(1).wip_entity_id := p_wip_entity_id;
117 allocate_table(1).use_pickset_flag := 'N'; --this flag only used by flow
118 allocate_table(1).project_id := l_project_id;
119 allocate_table(1).task_id := l_task_id;
120 allocate_table(1).operation_seq_num := p_operation_seq_num;
121 allocate_table(1).inventory_item_id := p_inventory_item_id;
122 allocate_table(1).requested_quantity := p_requested_quantity;
123 allocate_table(1).source_subinventory_code := p_source_subinventory;
124 allocate_table(1).source_locator_id := p_source_locator;
125 allocate_table(1).lot_number := p_lot_number;
126 allocate_table(1).start_serial := p_fm_serial;
127 allocate_table(1).end_serial := p_to_serial;
128
129 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
130 'Calling wip_picking_pub.allocate_comp');
131 end if;
132 wip_picking_pub.allocate_comp(p_alloc_comp_tbl => allocate_table,
133 p_cutoff_date => null,
134 p_wip_entity_type => wip_constants.eam,
135 p_organization_id => p_organization_id,
136 x_return_status => l_return_status,
137 x_msg_data => l_msg_data,
138 x_mo_req_number => x_request_number,
139 x_conc_req_id => l_pickslip_conc_req_id);
140 if (l_sLog) then FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
141 'After Calling wip_picking_pub.allocate_comp');
142 end if;
143 if (l_sLog) then
144 l_msg := 'WIP returned: Request Number:' || x_request_number || ', x_return_status:'
145 || l_return_status|| ', x_msg_data:' || l_msg_data ||
146 ', x_conc_req_id:' || l_pickslip_conc_req_id;
147 l_msg := REPLACE(l_msg, CHR(0), ' ');
148 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, l_msg);
149 end if;
150 x_return_status := l_return_status;
151 if (l_msg_data is not null) then
152 --needed since WIP does not put messages on stack while INV does.
153 FND_MSG_PUB.Count_And_Get(
154 p_count => x_msg_count,
155 p_data => x_msg_data);
156 if (x_msg_count = 0 ) then --means not on stack, add it.
157 fnd_message.set_name('EAM','EAM_GENERIC_ERROR');
158 fnd_message.set_token('EAM_ERROR', l_msg_data );
159 fnd_msg_pub.add;
160 end if;
161 end if;
162
163 if (l_return_status = 'P') then
164 select sum(quantity_detailed) into l_partial_qty
165 from mtl_txn_request_lines
166 where header_id = (select header_id from mtl_txn_request_headers
167 where request_number = x_request_number
168 and organization_id = p_organization_id);
169 if (l_partial_qty = 0) then
170 fnd_message.set_name('EAM','EAM_MR_NO_MATERIAL_AVAILABLE');
171 fnd_msg_pub.add;
172 else
173 fnd_message.set_name('EAM','EAM_MR_PARTIAL_ALLOCATION');
174 fnd_message.set_token('PARTIAL', to_char(l_partial_qty) );
175 fnd_message.set_token('REQUESTED', to_char(p_requested_quantity));
176 fnd_msg_pub.add;
177 end if;
178 end if;
179
180 if (x_return_status = FND_API.G_RET_STS_ERROR) then
181 if l_msg_data is null then --should not happen, just a safeguard
182 fnd_message.set_name('EAM','EAM_MR_ALLOCATION_FAILED');
183 fnd_msg_pub.add;
184 end if;
185 RAISE FND_API.G_EXC_ERROR;
186 end if;
187 -- End of API body.
188
189 -- Standard check of p_commit.
190 IF FND_API.To_Boolean( p_commit ) THEN
191 COMMIT WORK;
192 END IF;
193
194 -- Standard call to get message count and if count is 1, get message info.
195 FND_MSG_PUB.Count_And_Get(
196 p_count => x_msg_count,
197 p_data => x_msg_data);
198 if (l_pLog) then FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
199 'End of ' || l_full_name );
200 end if;
201 EXCEPTION
202 WHEN FND_API.G_EXC_ERROR THEN
203 ROLLBACK TO ALLOCATE_EAM;
204 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
205 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
206 'Exception Block 1 - Expected Error' );
207 END IF;
208 x_return_status := FND_API.G_RET_STS_ERROR ;
209 FND_MSG_PUB.Count_And_Get(
210 p_count => x_msg_count,
211 p_data => x_msg_data);
212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
213 ROLLBACK TO ALLOCATE_EAM;
214 IF ( l_uLog ) THEN
215 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module,
216 'Exception Block 2 - Unexpected error' );
217 END IF ;
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
219 FND_MSG_PUB.Count_And_Get(
220 p_count => x_msg_count,
221 p_data => x_msg_data);
222 WHEN OTHERS THEN
223 ROLLBACK TO ALLOCATE_EAM;
224 IF ( l_uLog ) THEN
225 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, l_module,
226 'Exception Block 3 - Others' );
227 END IF ;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
229 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
230 THEN
231 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
232 END IF;
233 FND_MSG_PUB.Count_And_Get(
234 p_count => x_msg_count,
235 p_data => x_msg_data);
236 END allocate;
237
238 END EAM_MATERIAL_REQUEST_PVT;