1 PACKAGE BODY QA_SOLUTION_CAR_PKG as
2 /* $Header: qasocorb.pls 120.0.12000000.2 2007/02/16 11:00:16 skolluku ship $ */
3
4 -- Package Private Variables.
5
6 -- Bug 3684073. Modified the constants to VARCHAR2 below.
7 -- We are no longer using mfg_lookups to derive the lookup_code.
8 -- g_lookup_yes CONSTANT NUMBER := 1; -- 1 is lookup_code for 'YES' in mfg_lookups.
9 -- g_lookup_no CONSTANT NUMBER := 2; -- 2 is lookup_code for 'NO' in mfg_lookups.
10
11 g_lookup_yes CONSTANT VARCHAR2(3) := 'YES';
12 g_lookup_no CONSTANT VARCHAR2(3) := 'NO';
13
14 g_success CONSTANT VARCHAR2(10) := 'SUCCESS';
15 g_failed CONSTANT VARCHAR2(10) := 'FAILED';
16 g_warning CONSTANT VARCHAR2(10) := 'WARNING';
17
18
19 -------------------------------------------------------------------------------
20 -- Forward declaration of Local functions.
21 -------------------------------------------------------------------------------
22
23 FUNCTION get_mfg_lookups_value (p_meaning VARCHAR2,
24 p_lookup_type VARCHAR2) RETURN NUMBER;
25
26 FUNCTION get_organization_id (p_organization_code VARCHAR2) RETURN NUMBER;
27 FUNCTION get_plan_id(p_plan_name VARCHAR2) RETURN NUMBER;
28
29
30
31 -------------------------------------------------------------------------------
32 -- Create a new ECO (Engineering Change Order)
33 -------------------------------------------------------------------------------
34 -- Start of Comments
35 -- API name ENG_CHANGE_ORDER
36 -- Type Public
37 -- Function
38 --
39 -- Pre-reqs
40 --
41 -- Parameters
42 -- p_change_notice => New ECO Name .
43 -- p_change_type => ECO Type.
44 -- p_description => Description of ECO.
45 -- p_approval_list => Name of the Approval List.
46 -- p_requestor => Requestor of ECO.
47 -- p_eco_department => ECO Department name.
48 -- p_reason_code => ECO Reason Code.
49 -- p_priority_code => ECO Priority Code.
50 -- p_collection_id => Collection Identifier
51 -- p_occurrence => Occurrence
52 -- p_plan_name => Collection Plan Name
53 -- p_organization_code => Organization Code, from which transaction happens
54 -- p_launch_action => This takes two values(Yes/No)
55 -- Pass a value of 'Yes' to successfully create an ECO.
56 -- p_action_fired => This takes two values(Yes/No)
57 -- Pass a value of 'No' to successfully create an ECO.
58 --
59 -- Version Current version = 1.0
60 -- Initial version = 1.0
61 --
62 -- Notes
63 --
64 -- We are performing all the below activities here
65 --
66 -- 1. Get the Collection element values.
67 -- 2. Call the ENG_CHANGE_ORDER_INT () to call the PROCESS_ECO () API.
68 -- 4. Get the results and perform the handshaking. Call the procedure
69 -- WRITE_BACK() for performing the same.
70 --
71 -- End of Comments
72 --
73 -- Bug Fixes
74 --
75 -- Bug 2714477 - Made changes in the way ECO_Rec_Type is populated. Status_type and
76 -- approval_status_type got changed to status_name and approval_status_
77 -- name respectively in 11.5.9 ENG Codeline. Also the requestor accepts
78 -- the full name of the user instead of employee number.
79 --
80 -- Bug 2731618 - ENG expects the requestor to be fnd_user name instead of the full_name
81 -- in 11.5.9 codeline. Made the changes in API to resolve this.
82 --
83 --
84 -------------------------------------------------------------------------------
85
86
87 PROCEDURE ENG_CHANGE_ORDER(
88 p_change_notice IN VARCHAR2,
89 p_change_type IN VARCHAR2,
90 p_description IN VARCHAR2,
91 p_approval_list IN VARCHAR2,
92 p_reason_code IN VARCHAR2,
93 p_requestor IN VARCHAR2,
94 p_eco_department IN VARCHAR2,
95 p_priority_code IN VARCHAR2,
96 p_collection_id IN NUMBER,
97 p_occurrence IN NUMBER,
98 p_organization_code IN VARCHAR2,
99 p_plan_name IN VARCHAR2,
100 p_launch_action IN VARCHAR2,
101 p_action_fired IN VARCHAR2) IS
102
103
104 -- Bug 3684073. These variables are no longer required.
105 -- l_launch_action NUMBER;
106 -- l_action_fired NUMBER;
107
108 -- Bug 2714477. Employee number is no longer required.
109 -- l_emp_num VARCHAR2(30);
110
111 l_organization_id NUMBER;
112 l_plan_id NUMBER;
113 l_result VARCHAR2(10);
114
115 -- Added for bug 2731618.
116 l_user_name VARCHAR2(100);
117
118 /* Bug 2714477. The below cursor is no more needed as we are passing the full
119 name of the requestor to the ECO API.
120
121 CURSOR emp_num IS
122 SELECT employee_num
123 FROM mtl_employees_current_view
124 WHERE full_name = p_requestor;
125 */
126
127 -- Bug 2731618. Added the below cursor to fetch the fnd_user name from
128 -- the full_name of the employee.
129
130 CURSOR emp_user IS
131 SELECT fu.user_name
132 FROM hr_employees_current_v hecv, fnd_user fu
133 WHERE hecv.employee_id = fu.employee_id
134 AND hecv.full_name = p_requestor;
135
136
137 BEGIN
138
139 -- Get the value entered in launch_action and Action_fired
140 -- Collection elements.
141
142 -- Bug 3684073. We should not derive the lookup_code value from
143 -- mfg_lookups because the value passed to this api would be the
144 -- qa_plan_char_value_lookups.short_code, which is not a translated
145 -- column. The mfg_lookups view would have the lookup meaning in the
146 -- language used in the current session.
147 --
148 -- Commented the below piece of code and compared p_launch_action
149 -- and p_action_fired parameters below with the new constants to resolve
150 -- the value entered. kabalakr.
151
152 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
153 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
154
155 -- The Action Code should get executed only if
156 -- Launch_action is 'Yes' and Action_fired is 'No'
157
158 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
159 NULL;
160
161 ELSE
162 -- dont fire the action.
163 RETURN;
164 END IF;
165
166 -- Get the plan_id, org_id now. We need it for handshaking.
167
168 l_organization_id := get_organization_id(p_organization_code);
169 l_plan_id := get_plan_id(p_plan_name);
170
171 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
172
173 -- We may need to populate appropriate error message here before return.
174 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 -- Update the Disposition Status to 'Pending'.
178
179 QA_SOLUTION_DISPOSITION_PKG.UPDATE_STATUS(l_plan_id, p_collection_id, p_occurrence);
180
181 -- Get the Employee_num of the Requestor only if its entered.
182
183
184 /* Bug 2714477. The below code is no more needed as we are passing the full
185 name of the requestor to the ECO API.
186
187 IF p_requestor IS NOT NULL THEN
188 OPEN emp_num;
189 FETCH emp_num INTO l_emp_num;
190 CLOSE emp_num;
191 END IF;
192 */
193
194 -- Bug 2731618. Added the below code to fetch the fnd_user name corresponding
195 -- to the requestor full_name onto l_user_name. If a corresponding fnd_user name
196 -- does not exist, assign NULL to l_user_name. kabalakr.
197
198 IF p_requestor IS NOT NULL THEN
199 OPEN emp_user;
200 FETCH emp_user INTO l_user_name;
201
202 IF (emp_user%NOTFOUND) THEN
203 l_user_name := NULL;
204 END IF;
205
206 CLOSE emp_user;
207
208 END IF;
209
210 -- Call the ENG_CHANGE_ORDER_INT(). This procedure creates a struct of ECO_REC_TYPE.
211 -- And will call the Process_ECO () API to process the ECO Header Information.
212
213 -- Bug 2717744. Remove the l_emp_num from the below function call. Instead, we are
214 -- passing the full name of the employee.
215
216 -- Bug 2731618. Removed p_requestor from the below function call. Instead, passed
217 -- l_user_name, which contains the fnd_user_name of the employee.
218
219 l_result := ENG_CHANGE_ORDER_INT(
220 p_change_notice,
221 p_change_type,
222 p_description,
223 p_approval_list,
224 p_reason_code,
225 l_user_name,
226 p_eco_department,
227 p_priority_code,
228 p_organization_code);
229
230
231 -- Call WRITE_BACK() for handshaking the outcome onto the Collection Plan.
232 -- If the ECO Creation is successful, we need to write back the new ECO
233 -- name onto the hardcoded element column ECO_NAME.
234
235 IF (l_result = g_success) THEN
236
237 QA_SOLUTION_DISPOSITION_PKG.WRITE_BACK(
238 p_plan_id => l_plan_id,
239 p_collection_id => p_collection_id,
240 p_occurrence => p_occurrence,
241 p_status => l_result,
242 p_eco_name => p_change_notice);
243
244 ELSE
245
246 QA_SOLUTION_DISPOSITION_PKG.WRITE_BACK(
247 p_plan_id => l_plan_id,
248 p_collection_id => p_collection_id,
249 p_occurrence => p_occurrence,
250 p_status => l_result);
251
252 END IF;
253
254
255 END ENG_CHANGE_ORDER;
256
257
258
259 FUNCTION ENG_CHANGE_ORDER_INT(
260 p_change_notice VARCHAR2,
261 p_change_type VARCHAR2,
262 p_description VARCHAR2,
263 p_approval_list VARCHAR2,
264 p_reason_code VARCHAR2,
265 p_requestor VARCHAR2,
266 p_eco_department VARCHAR2,
267 p_priority_code VARCHAR2,
268 p_org_code VARCHAR2)
269
270 RETURN VARCHAR2 IS
271
272 PRAGMA AUTONOMOUS_TRANSACTION;
273
274 l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
275 l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
276 l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
277 l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
278 l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
279 l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
280 l_rev_operation_tbl Bom_Rtg_Pub.Rev_Operation_Tbl_Type;
281 l_rev_op_resource_tbl Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type;
282 l_rev_sub_resource_tbl Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type;
283 l_return_status VARCHAR2(10);
284 l_msg_count NUMBER;
285 l_msg_data VARCHAR2(2000);
286 l_Error_Table Error_Handler.Error_Tbl_Type;
287 l_Message_text VARCHAR2(2000);
288
289 -- Bug 2714477. Added the below variables and cursor c1.
290
291 l_status_name VARCHAR2(80);
292 l_approval_status_name VARCHAR2(80);
293
294 CURSOR c1(type VARCHAR2) IS
295 SELECT meaning
296 FROM mfg_lookups
297 WHERE lookup_type = type
298 AND lookup_code = 1;
299
300 BEGIN
301
302 -- Bug 2714477. Get the Status name and Approval status name
303 -- from mfg_lookups.
304
305 OPEN c1('ECG_ECN_STATUS');
306 FETCH c1 INTO l_status_name;
307 CLOSE c1;
308
309 OPEN c1('ENG_ECN_APPROVAL_STATUS');
310 FETCH c1 INTO l_approval_status_name;
311 CLOSE c1;
312
313
314 -- Fill in the Eco_Rec_Type structure to pass to the API.
315
316 -- Bug 2714477. Status_type and approval_status_type changed to status_name
317 -- and approval_status_name respectively in 11.5.9 ENG codeline. Hence
318 -- these changes are made here too. kabalakr.
319
320 l_eco_rec.eco_name := p_change_notice;
321 l_eco_rec.organization_code := p_org_code;
322 l_eco_rec.change_type_code := p_change_type;
323 l_eco_rec.status_name := l_status_name; -- 1 for 'Open'.
324 l_eco_rec.approval_list_name := p_approval_list;
325 l_eco_rec.approval_status_name := l_approval_status_name; -- 1 for 'Not submitted for Approval'.
326 l_eco_rec.requestor := p_requestor;
327 l_eco_rec.priority_code := p_priority_code;
328 l_eco_rec.eco_department_name := p_eco_department;
329 l_eco_rec.reason_code := p_reason_code;
330 l_eco_rec.description := p_description;
331 l_eco_rec.transaction_type := 'CREATE';
332 --
333 -- Bug 5869696
334 -- The Eco_Rec_Type structure got modified in the PUBLIC API Eng_Eco_PUB
335 -- since a new parameter plm_or_erp_change is added to it.
336 -- Quality should pass the value 'ERP' to the API for this parameter
337 -- while posting the CAR.
338 -- skolluku Fri Feb 16 2007
339 --
340 l_eco_rec.plm_or_erp_change := 'ERP';
341
342 -- Calling the Process_ECO API.
343
344 Eng_Eco_PUB.Process_Eco
345 ( p_api_version_number => 1.0,
346 p_init_msg_list => FALSE,
347 x_return_status => l_return_status,
348 x_msg_count => l_msg_count,
349 p_bo_identifier => 'ECO',
350 p_ECO_rec => l_eco_rec,
351 x_ECO_rec => l_eco_rec,
352 x_eco_revision_tbl => l_eco_revision_tbl,
353 x_revised_item_tbl => l_revised_item_tbl,
354 x_rev_component_tbl => l_rev_component_tbl,
355 x_sub_component_tbl => l_sub_component_tbl,
356 x_ref_designator_tbl => l_ref_designator_tbl,
357 x_rev_operation_tbl => l_rev_operation_tbl,
358 x_rev_op_resource_tbl => l_rev_op_resource_tbl,
359 x_rev_sub_resource_tbl => l_rev_sub_resource_tbl
360 );
361
362 -- Assign the disposition statuses as return value.
363
364 IF (l_return_status = 'S') THEN
365 l_return_status := g_success;
366
367 ELSE
368 l_return_status := g_failed;
369 END IF ;
370
371 -- Commit before the return.
372 COMMIT;
373
374 RETURN l_return_status;
375
376
377 END ENG_CHANGE_ORDER_INT;
378
379
380
381 FUNCTION get_mfg_lookups_value (p_meaning VARCHAR2,
382 p_lookup_type VARCHAR2)
383 RETURN NUMBER IS
384
385 l_lookup_code VARCHAR2(2);
386
387 Cursor meaning_cur IS
388 SELECT lookup_code
389 FROM mfg_lookups
390 WHERE lookup_type = p_lookup_type
391 AND upper(meaning) = upper(ltrim(rtrim(p_meaning)));
392 BEGIN
393 OPEN meaning_cur;
394 FETCH meaning_cur INTO l_lookup_code;
395 CLOSE meaning_cur;
396
397 RETURN l_lookup_code;
398
399 END get_mfg_lookups_value;
400
401
402
403 FUNCTION get_plan_id(p_plan_name VARCHAR2)
404 RETURN NUMBER IS
405
406 l_plan_id NUMBER;
407
408 CURSOR plan_cur IS
409 SELECT plan_id
410 FROM qa_plans
411 WHERE name = p_plan_name;
412 BEGIN
413
414 OPEN plan_cur;
415 FETCH plan_cur INTO l_plan_id;
416 CLOSE plan_cur;
417
418 RETURN l_plan_id;
419
420 END get_plan_id;
421
422
423 FUNCTION get_organization_id (p_organization_code VARCHAR2)
424 RETURN NUMBER IS
425
426 l_org_id NUMBER;
427
428 CURSOR org_cur IS
429 SELECT organization_id
430 FROM mtl_parameters
431 WHERE organization_code = p_organization_code;
432
433 BEGIN
434
435 OPEN org_cur;
436 FETCH org_cur INTO l_org_id;
437 CLOSE org_cur;
438
439 RETURN l_org_id;
440
441 END Get_organization_id;
442
443
444 END QA_SOLUTION_CAR_PKG;
445
[Home] [Help]