1 PACKAGE BODY QA_SOLUTION_DISPOSITION_PKG as
2 /* $Header: qasodisb.pls 120.1 2006/01/31 05:48:19 saugupta noship $ */
3
4 -- Package level constants
5
6 -- check every 900 seconds to find the request completed or not.
7
8 -- Bug 2696473. Reduced the time interval for checking the request completion
9 -- to 30 seconds.
10
11 g_check_request_time CONSTANT NUMBER := 30;
12
13 -- wait for 6000 seconds for the concurrent request to complete.
14 g_total_request_time CONSTANT NUMBER := 6000;
15
16 -- Bug 3684073. Modified the constants to VARCHAR2 below.
17 -- We are no longer using mfg_lookups to derive the lookup_code.
18 -- g_lookup_yes CONSTANT NUMBER := 1; -- 1 is lookup_code for 'YES' in mfg_lookups.
19 -- g_lookup_no CONSTANT NUMBER := 2; -- 2 is lookup_code for 'NO' in mfg_lookups.
20
21 g_lookup_yes CONSTANT VARCHAR2(3) := 'YES';
22 g_lookup_no CONSTANT VARCHAR2(3) := 'NO';
23
24 g_success CONSTANT VARCHAR2(10) := 'SUCCESS';
25 g_failed CONSTANT VARCHAR2(10) := 'FAILED';
26 g_warning CONSTANT VARCHAR2(10) := 'WARNING';
27 g_int_err CONSTANT VARCHAR2(10) := 'INT_ERROR';
28
29 -- Bug 2689276. Added the below variable.
30 g_pending CONSTANT VARCHAR2(10) := 'PENDING';
31
32 -------------------------------------------------------------------------------
33 -- Forward declaration of Local functions.
34 -------------------------------------------------------------------------------
35
36 FUNCTION get_mfg_lookups_value (p_meaning VARCHAR2,
37 p_lookup_type VARCHAR2) RETURN NUMBER;
38
39 FUNCTION get_organization_id (p_organization_code VARCHAR2) RETURN NUMBER;
40 FUNCTION get_plan_id(p_plan_name VARCHAR2) RETURN NUMBER;
41
42 -- anagarwa Fri Jul 2 16:30:00 PDT 2004
43 -- bug 3736593 action fired element cannot be validated.
44 -- following function looks for values in qa_plan_char_value_lookups and
45 -- if not found, it executes the sql validation string
46 TYPE LookupCur IS REF CURSOR;
47 FUNCTION get_short_code(p_plan_id NUMBER,
48 p_char_id NUMBER,
49 p_short_code IN VARCHAR2) RETURN VARCHAR2;
50
51 -------------------------------------------------------------------------------
52 -- Create a New Work Order API
53 -------------------------------------------------------------------------------
54 -- Start of Comments
55 -- API name REWORK_NEW_NONSTANDARD_JOB
56 -- Type Public
57 -- Function
58 --
59 -- Pre-reqs
60 --
61 -- Parameters
62 -- p_item => Item Name
63 -- p_job_class => Class of the Job to be created.This should be 'Rework'
64 -- p_job_name => Name of the Rework Job to be created
65 -- p_job_start => Start Date of the Job
66 -- p_job_end => End Date of the Job
67 -- p_bill_reference => Bill reference
68 -- p_bom_revision => Bom revision
69 -- p_routing_reference => Routing reference
70 -- p_routing_revision => Routing revision
71 -- p_quantity => Quantity
72 -- p_job_mrp_net_quantity => MRP Net Quantity
73 -- p_project_number => Project Number
74 -- p_task_number => Task Number
75 -- p_collection_id => Collection ID
76 -- p_occurrence => Occurrence
77 -- p_plan_name => Collection Plan Name
78 -- p_organization_code => Organization Code, from which transaction happens
79 -- p_launch_action => This takes two values(Yes/No)
80 -- Pass a value of 'Yes' to successfully create Rework Job
81 -- p_action_fired => This takes two values(Yes/No)
82 -- Pass a value of 'No' to successfully create Rework Job
83 --
84 -- Version Current version = 1.0
85 -- Initial version = 1.0
86 --
87 -- Notes
88 --
89 -- We are performing all the below activities here
90 --
91 -- 1. Get the different id values.
92 -- 2. Call REWORK_NEW_NONSTANDARD_JOB_INT() procedure for inserting into interface table
93 -- and spawn the WIP Mass Load Program (WICMLX).
94 -- 3. Wait for the Mass Load Program to get completed.
95 -- 4. Get the results and perform the handshaking. Call the procedure
96 -- WRITE_BACK() for performing the same.
97 --
98 -- End of Comments
99 --
100 -- Bug Fixes
101 --
102 -- Bug 2689276 : Call WRITE_BACK() to handshake the concurrent request id as
103 -- soon as the concurrnt request gets launched.
104 --
105 -- Bug 2656461 : Code added to support copying of attachments to the
106 -- WIP_DISCRETE_OPERATIONS entity, once the Action is successful.
107 --
108 -- Bug 2697388 : Changed the parameter p_bill_revision to p_bom_revision. Functionality
109 -- remains the same. kabalakr Tue Dec 17 22:48:31 PST 2002.
110 --
111 -- Bug 2720431 : Added one more parameter p_job_mrp_net_quantity to the API. This input
112 -- parameter carries the value for MRP Net Quantiy of the new rework Job.
113 --
114 --
115 --
116 --
117
118
119 PROCEDURE REWORK_NEW_NONSTANDARD_JOB(
120 p_item IN VARCHAR2,
121 p_job_class IN VARCHAR2,
122 p_job_name IN VARCHAR2,
123 p_job_start IN VARCHAR2,
124 p_job_end IN VARCHAR2,
125 p_bill_reference IN VARCHAR2,
126 p_bom_revision IN VARCHAR2,
127 p_routing_reference IN VARCHAR2,
128 p_routing_revision IN VARCHAR2,
129 p_quantity IN NUMBER,
130 p_job_mrp_net_quantity IN NUMBER,
131 p_project_number IN VARCHAR2,
132 p_task_number IN VARCHAR2,
133 p_collection_id IN NUMBER,
134 p_occurrence IN NUMBER,
135 p_organization_code IN VARCHAR2,
136 p_plan_name IN VARCHAR2,
137 p_launch_action IN VARCHAR2,
138 p_action_fired IN VARCHAR2) IS
139
140
141 l_request NUMBER;
142 l_group_id NUMBER;
143 l_plan_id NUMBER;
144 l_organization_id NUMBER;
145
146 -- Bug 3684073. These variables are no longer required.
147 -- l_launch_action NUMBER;
148 -- l_action_fired NUMBER;
149
150 l_err_msg VARCHAR2(2000) := NULL;
151
152 l_wait BOOLEAN;
153 l_phase VARCHAR2(2000);
154 l_status VARCHAR2(2000);
155 l_devphase VARCHAR2(2000);
156 l_devstatus VARCHAR2(2000);
157 l_message VARCHAR2(2000);
158
159 l_error VARCHAR2(1000);
160 l_job_id NUMBER := NULL;
161 l_result VARCHAR2(1800);
162 l_item_id NUMBER;
163
164 l_bill_id NUMBER;
165 l_routing_id NUMBER;
166
167 l_source_code VARCHAR2(30);
168
169 CURSOR group_cur IS
170 SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
171 FROM DUAL;
172
173 -- Bug 3019869. Changed the below cursor to wip_job cursor.
174 -- Records will not reside in WJSI if the MRP Debug mode
175 -- profile option is set to 'No'. kabalakr
176
177 /*
178 CURSOR job_cur IS
179 SELECT we.wip_entity_id
180 FROM WIP_ENTITIES we, WIP_JOB_SCHEDULE_INTERFACE wjsi
181 WHERE wjsi.process_status = 4
182 AND wjsi.group_id = l_group_id
183 AND we.wip_entity_id = wjsi.wip_entity_id;
184 */
185
186 -- Bug 3641781. Modified the sql to include primary_item_id.
187 -- This would avoid a full table scan on wip_discrete_jobs and
188 -- make the sql pick up the NON-UNIQUE index WIP_DISCRETE_JOBS_N1
189 -- (on primary_item_id). kabalakr
190
191 CURSOR wip_job(l_src_code VARCHAR2, l_pri_item_id NUMBER) IS
192 SELECT wip_entity_id
193 FROM wip_discrete_jobs
194 WHERE primary_item_id = l_pri_item_id
195 AND source_code = l_src_code;
196
197 BEGIN
198
199 -- Get the value entered in confirm_action Collection element.
200
201 -- Bug 3684073. We should not derive the lookup_code value from
202 -- mfg_lookups because the value passed to this api would be the
203 -- qa_plan_char_value_lookups.short_code, which is not a translated
204 -- column. The mfg_lookups view would have the lookup meaning in the
205 -- language used in the current session.
206 --
207 -- Commented the below piece of code and compared p_launch_action
208 -- and p_action_fired parameters below with the new constants to resolve
209 -- the value entered. kabalakr.
210
211 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
212 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
213
214 -- The Action Code should get executed only if
215 -- Launch_action is 'Yes' and Action_fired is 'No'
216
217 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
218 NULL;
219
220 ELSE
221 -- dont fire the action.
222 RETURN;
223 END IF;
224
225 -- Get the plan_id, group_id and org_id now.
226 OPEN group_cur;
227 FETCH group_cur INTO l_group_id;
228 CLOSE group_cur;
229
230 l_organization_id := get_organization_id(p_organization_code);
231 l_plan_id := get_plan_id(p_plan_name);
232
233 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
234
235 -- We may need to populate appropriate error message here before return.
236 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239 -- Update the Disposition Status to 'Pending'.
240 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
241
242 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
243 l_bill_id := qa_flex_util.get_item_id(l_organization_id, p_bill_reference);
244 l_routing_id := qa_flex_util.get_item_id(l_organization_id, p_routing_reference);
245
246
247 -- Call the rework_int(). It returns back the concurrent request
248 -- id of the WIP Mass Load Program that gets spawned.
249
250 -- Bug 2697388. Changed the argument p_bill_revision to p_bom_revision.
251
252 -- Bug 2720431. Added p_job_mrp_net_quantity. This parameter carries the value
253 -- for MRP Net Quantiy of the new rework Job. kabalakr.
254
255 l_request := REWORK_NEW_NONSTANDARD_JOB_INT(
256 l_item_id,
257 l_group_id,
258 p_job_class,
259 p_job_name,
260 qltdate.any_to_date(p_job_start),
261 qltdate.any_to_date(p_job_end),
262 l_bill_id,
263 p_bom_revision,
264 l_routing_id,
265 p_routing_revision,
266 p_quantity,
267 p_job_mrp_net_quantity,
268 p_project_number,
269 p_task_number,
270 l_organization_id);
271
272 IF (l_request = 0) THEN
273
274 -- Concurrent Request not launched
275 l_result := g_failed;
276 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
277
278 WRITE_BACK( p_plan_id => l_plan_id,
279 p_collection_id => p_collection_id,
280 p_occurrence => p_occurrence,
281 p_status => l_result,
282 p_message => l_err_msg);
283 RETURN;
284
285 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
286 -- write back the concurrent request id.
287
288 ELSE
289
290 WRITE_BACK( p_plan_id => l_plan_id,
291 p_collection_id => p_collection_id,
292 p_occurrence => p_occurrence,
293 p_status => g_pending,
294 p_request_id => l_request);
295
296 END IF;
297
298
299 -- If request gets launched, proceed.
300 -- But first, wait for the WIP Mass Load Program request to be completed.
301 -- We wait 100 minutes for the Mass Load to Complete. And we check in
302 -- every 15 Minutes
303
304 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
305 g_check_request_time,
306 g_total_request_time,
307 l_phase,
308 l_status,
309 l_devphase,
310 l_devstatus,
311 l_message);
312
313 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
314 IF (substr(l_devstatus,1,5) = 'ERROR') THEN
315
316 l_result := g_failed;
317
318 ELSIF (substr(l_devstatus,1,7) = 'WARNING') THEN
319
320 l_result := g_warning;
321
322
323 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
324
325 -- Bug 3019869. We had passed the source_code concatenated
326 -- with the group_id. Hence fetching the wip_entity_id from
327 -- wip_discrete_jobs table using the source_code. kabalakr
328
329 l_source_code := 'QA ACTION: REWORK'||to_char(l_group_id);
330
331 -- Bug 3641781. Pass the item_id also to the cursor sql. kabalakr.
332
333 OPEN wip_job(l_source_code, l_item_id);
334 FETCH wip_job INTO l_job_id;
335 CLOSE wip_job;
336
337 l_result := g_success;
338
339 -- Bug 2656461. Once the Action is successful, we also need to copy
340 -- the attachments to the new Job created.
341
342 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
343 X_from_entity_name => 'QA_RESULTS',
344 X_from_pk1_value => to_char(p_occurrence),
345 X_from_pk2_value => to_char(p_collection_id),
346 X_from_pk3_value => to_char(l_plan_id),
347 X_to_entity_name => 'WIP_DISCRETE_JOBS',
348 X_to_pk1_value => to_char(l_job_id),
349 X_to_pk2_value => to_char(l_organization_id));
350
351 ELSE
352 l_result := g_failed;
353
354 END IF;
355
356 -- Call for handshaking the outcome onto the Collection Plan.
357
358 WRITE_BACK(p_plan_id => l_plan_id,
359 p_collection_id => p_collection_id,
360 p_occurrence => p_occurrence,
361 p_status => l_result,
362 p_job_id => l_job_id,
363 p_wjsi_group_id => l_group_id,
364 p_request_id => l_request
365 );
366
367 END IF; -- if complete.
368
369 END REWORK_NEW_NONSTANDARD_JOB;
370
371
372 FUNCTION REWORK_NEW_NONSTANDARD_JOB_INT(
373 p_item_id NUMBER,
374 p_group_id NUMBER,
375 p_jclass VARCHAR2,
376 p_job_name VARCHAR2,
377 p_job_start DATE,
378 p_job_end DATE,
379 p_bill_id NUMBER,
380 p_bill_revision VARCHAR2,
381 p_routing_id NUMBER,
382 p_routing_revision VARCHAR2,
383 p_quantity NUMBER,
384 p_job_mrp_net_quantity NUMBER,
385 p_project_number VARCHAR2,
386 p_task_number VARCHAR2,
387 p_organization_id NUMBER)
388
389 RETURN NUMBER IS
390
391 PRAGMA AUTONOMOUS_TRANSACTION;
392
393 l_request_id NUMBER;
394 l_update_by NUMBER := fnd_global.user_id;
395 l_update_name VARCHAR2(100);
396
397 l_source_code VARCHAR2(30);
398
399 CURSOR update_cur IS
400 SELECT user_name
401 FROM fnd_user_view
402 WHERE user_id = l_update_by;
403
404
405 BEGIN
406
407 OPEN update_cur;
408 FETCH update_cur INTO l_update_name;
409 CLOSE update_cur;
410
411 -- Bug 3019869. The source_code will be concatenated with the
412 -- group_id in order to retrieve the wip_entity_id when writing
413 -- back to the results. kabalakr
414
415 l_source_code := 'QA ACTION: REWORK'||to_char(p_group_id);
416
417
418 -- Bug 2720431. Added p_job_mrp_net_quantity as NET_QUANTITY.
419
420 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
421 PRIMARY_ITEM_ID,
422 SOURCE_CODE,
423 LOAD_TYPE,
424 PROCESS_PHASE,
425 PROCESS_STATUS,
426 GROUP_ID,
427 LAST_UPDATE_DATE,
428 LAST_UPDATED_BY,
429 LAST_UPDATED_BY_NAME,
430 CREATION_DATE,
431 CREATED_BY_NAME,
432 CREATED_BY,
433 FIRST_UNIT_START_DATE,
434 LAST_UNIT_COMPLETION_DATE,
435 CLASS_CODE,
436 ORGANIZATION_ID,
437 START_QUANTITY,
438 NET_QUANTITY,
439 JOB_NAME,
440 PROJECT_NUMBER,
441 TASK_NUMBER,
442 BOM_REFERENCE_ID,
443 BOM_REVISION,
444 ROUTING_REFERENCE_ID,
445 ROUTING_REVISION
446 )
447 VALUES
448 (
449 p_item_id,
450 l_source_code,
451 4,
452 2,
453 1,
454 p_group_id,
455 SYSDATE,
456 l_update_by,
457 l_update_name,
458 SYSDATE,
459 l_update_name,
460 l_update_by,
461 p_job_start,
462 p_job_end,
463 p_jclass,
464 p_organization_id,
465 p_quantity,
466 p_job_mrp_net_quantity,
467 p_job_name,
468 nvl(p_project_number,NULL),
469 nvl(p_task_number,NULL),
470 p_bill_id,
471 p_bill_revision,
472 p_routing_id,
473 p_routing_revision
474 );
475
476 -- Call the WIP Mass Load Program in Background.
477 l_request_id := FND_REQUEST.SUBMIT_REQUEST('WIP', 'WICMLP',
478 NULL, NULL, FALSE,
479 TO_CHAR(p_group_id), /* grp id*/
480 TO_CHAR(WIP_CONSTANTS.FULL), /*validation lvl*/
481 TO_CHAR(WIP_CONSTANTS.YES)); /* print report */
482
483 -- Commit the insert
484 COMMIT;
485
486 RETURN l_request_id;
487
488 END REWORK_NEW_NONSTANDARD_JOB_INT;
489
490
491
492 -------------------------------------------------------------------------------
493 -- WIP Scrap Transactions API
494 -------------------------------------------------------------------------------
495 -- Start of Comments
496 -- API name WIP_SCRAP_WIP_MOVE
497 -- Type Public
498 -- Function
499 --
500 -- Pre-reqs
501 --
502 -- Parameters
503 -- p_item => Item Name
504 -- p_job_name => Job Name to do the WIP Scrap Transaction
505 -- p_scrap_alias => Scrap Account
506 -- p_from_op_seq => From Operation Sequence Number
507 -- p_from_intra_step => From Intraoperation Step
508 -- p_to_op_seq => To Operation Sequence Number
509 -- p_to_intra_step => To Intraoperation Step
510 -- p_from_op_code => From Operation Code
511 -- p_to_op_code => To Operation Code
512 -- p_reason_code => Reason Code
513 -- p_uom => Transacting UOM
514 -- p_quantity => Quantity
515 -- p_txn_date => WIP Scrap requires date in varchar2. This should be either Canonical
516 -- format ('YYYY/MM/DD') or in Real format ('DD-MON-YYYY')
517 -- p_collection_id => Collection ID
518 -- p_occurrence => Occurrence
519 -- p_plan_name => Collection Plan Name
520 -- p_organization_code => Organization Code, from which transaction happens
521 -- p_launch_action => This takes two values(Yes/No)
522 -- Pass a value of 'Yes' to successfully do WIP Scrap Transactions
523 -- p_action_fired => This takes two values(Yes/No)
524 -- Pass a value of 'No' to successfully do WIP Scrap Transactions
525 --
526 -- Version Current version = 1.0
527 -- Initial version = 1.0
528 --
529 -- Notes
530 --
531 -- We are performing all the below activities here
532 --
533 -- 1. Get the different id values.
534 -- 2. Call WIP_SCRAP_WIP_MOVE_INT() procedure for inserting into interface table
535 -- and spawn the WIP Move Transaction manager.
536 -- 3. Wait for the Move Manager and Worker Program to get completed.
537 -- 4. Get the results and perform the handshaking. Call the procedure
538 -- WRITE_BACK() for performing the same.
539 --
540 -- End of Comments
541 --
542 -- Bug Fixes
543 --
544 -- Bug 2689276 : Call WRITE_BACK() to handshake the concurrent request id as
545 -- soon as the concurrnt request gets launched.
546 --
547 -- Bug 2697388 : Changed the parameters p_fm_op_code and p_reason_name to p_from_op_code
548 -- and p_reason_code respectively. Functionality remains the same.
549 -- kabalakr Tue Dec 17 22:48:31 PST 2002.
550 --
551 --
552 --
553
554
555 PROCEDURE WIP_SCRAP_WIP_MOVE(
556 p_item IN VARCHAR2,
557 p_job_name IN VARCHAR2,
558 p_scrap_alias IN VARCHAR2,
559 p_from_op_seq IN NUMBER,
560 p_from_intra_step IN VARCHAR2,
561 p_to_op_seq IN NUMBER,
562 p_to_intra_step IN VARCHAR2,
563 p_from_op_code IN VARCHAR2,
564 p_to_op_code IN VARCHAR2,
565 p_reason_code IN VARCHAR2,
566 p_uom IN VARCHAR2,
567 p_quantity IN NUMBER,
568 p_txn_date IN VARCHAR2,
569 p_collection_id IN NUMBER,
570 p_occurrence IN NUMBER,
571 p_organization_code IN VARCHAR2,
572 p_plan_name IN VARCHAR2,
573 p_launch_action IN VARCHAR2,
574 p_action_fired IN VARCHAR2) IS
575
576
577 l_request NUMBER;
578 l_child_request NUMBER;
579 l_plan_id NUMBER;
580 l_organization_id NUMBER;
581 l_to_step NUMBER;
582 l_from_step NUMBER;
583 l_src_code VARCHAR2(30);
584
585 -- Bug 3684073. These variables are no longer required.
586 -- l_launch_action NUMBER;
587 -- l_action_fired NUMBER;
588
589 l_reason_id NUMBER;
590 l_group_id NUMBER;
591 l_transaction_id NUMBER;
592 l_txn_id NUMBER;
593 l_dist_account_id NUMBER;
594 l_item_id NUMBER;
595
596 l_wait BOOLEAN;
597 l_phase VARCHAR2(2000);
598 l_status VARCHAR2(2000);
599 l_devphase VARCHAR2(2000);
600 l_devstatus VARCHAR2(2000);
601 l_message VARCHAR2(2000);
602
603 l_result VARCHAR2(1800);
604 l_err_msg VARCHAR2(2000) := NULL;
605 l_err_col VARCHAR2(1000);
606 l_arg VARCHAR2(240);
607
608 -- Bug 2697724.suramasw.
609 l_txn_date DATE;
610
611 CURSOR txn_cur IS
612 SELECT wip_transactions_s.nextval
613 FROM DUAL;
614
615 CURSOR req_cur IS
616 SELECT request_id
617 FROM FND_CONC_REQ_SUMMARY_V
618 WHERE parent_request_id = l_request;
619
620 CURSOR grp_cur IS
621 SELECT group_id
622 FROM WIP_MOVE_TXN_INTERFACE
623 WHERE source_code = l_src_code;
624
625 CURSOR acc_cur IS
626 SELECT distribution_account
627 FROM mtl_generic_dispositions_kfv
628 WHERE concatenated_segments = p_scrap_alias
629 AND organization_id = l_organization_id;
630
631 CURSOR txns_cur IS
632 SELECT transaction_id
633 FROM wip_move_transactions
634 WHERE source_code = l_src_code;
635
636 CURSOR reason_cur IS
637 SELECT reason_id
638 FROM mtl_transaction_reasons_val_v
639 WHERE reason_name LIKE p_reason_code;
640
641 CURSOR arg_cur IS
642 SELECT argument_text
643 FROM FND_CONC_REQ_SUMMARY_V
644 WHERE request_id = l_child_request;
645
646 BEGIN
647
648 -- Get the value entered in confirm_action Collection element.
649
650 -- Bug 3684073. We should not derive the lookup_code value from
651 -- mfg_lookups because the value passed to this api would be the
652 -- qa_plan_char_value_lookups.short_code, which is not a translated
653 -- column. The mfg_lookups view would have the lookup meaning in the
654 -- language used in the current session.
655 --
656 -- Commented the below piece of code and compared p_launch_action
657 -- and p_action_fired parameters below with the new constants to resolve
658 -- the value entered. kabalakr.
659
660 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
661 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
662
663 -- The Action Code should get executed only if
664 -- Launch_action is 'Yes' and relaunch_flag is 'No'
665
666 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
667 NULL;
668 ELSE
669 -- dont fire the action
670 RETURN;
671 END IF;
672
673 l_organization_id := get_organization_id(p_organization_code);
674 l_plan_id := get_plan_id(p_plan_name);
675
676 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
677
678 -- We may need to populate appropriate error message here before return.
679 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
680 END IF;
681
682 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
683
684 -- Get transaction_id and lookup code for the Intraoperation step.
685
686 l_to_step := get_mfg_lookups_value(p_to_intra_step,'WIP_INTRAOPERATION_STEP');
687 l_from_step := get_mfg_lookups_value(p_from_intra_step,'WIP_INTRAOPERATION_STEP');
688
689 -- IF the To Intraoperation step is not 'SCRAP', do not execute the action.
690 -- Write back INT_ERROR and RETURN.
691
692 IF (l_to_step <> 5) THEN
693
694 l_result := g_int_err;
695 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_INVALID_OP_STEP');
696
697 WRITE_BACK( p_plan_id => l_plan_id,
698 p_collection_id => p_collection_id,
699 p_occurrence => p_occurrence,
700 p_status => l_result,
701 p_message => l_err_msg);
702 RETURN;
703 END IF;
704
705 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
706
707 OPEN txn_cur;
708 FETCH txn_cur INTO l_txn_id;
709 CLOSE txn_cur;
710
711 OPEN reason_cur;
712 FETCH reason_cur INTO l_reason_id;
713 CLOSE reason_cur;
714
715 OPEN acc_cur;
716 FETCH acc_cur INTO l_dist_account_id;
717 CLOSE acc_cur;
718
719
720 -- Added the following code to make the transaction date to have the time portion
721 -- so that WIP processor picks the transaction date.If the transaction date doesn't
722 -- have the time portion and the transaction happens on the job release date the
723 -- WIP Move transaction fails saying 'Transaction date precedes release date for
724 -- the job/schedule'.l_txn_date in the following code will be passed to the
725 -- WIP_SCRAP_WIP_MOVE_INT instead of the p_txn_date.
726 -- Bug 2697724.suramasw.Mon Jan 20 06:21:03 PST 2003.
727
728 IF (qltdate.canon_to_date(p_txn_date) - trunc(sysdate)) = 0 THEN
729 l_txn_date := sysdate;
730 ELSE
731 l_txn_date := qltdate.canon_to_date(p_txn_date);
732 END IF;
733
734
735 -- Call the wipscrap_int(). It spawns and returns back the concurrent request
736 -- id of the WIP Move Manager Program.
737
738 -- Bug 2697388. Changed the argument p_fm_op_code to p_from_op_code.
739
740 l_request := WIP_SCRAP_WIP_MOVE_INT(
741 l_item_id,
742 l_txn_id,
743 p_job_name,
744 l_dist_account_id,
745 p_from_op_seq,
746 l_from_step,
747 p_to_op_seq,
748 l_to_step,
749 nvl(p_from_op_code,NULL),
750 nvl(p_to_op_code,NULL),
751 nvl(l_reason_id,0),
752 p_uom,
753 p_quantity,
754 qltdate.any_to_date(l_txn_date),
755 p_organization_code,
756 p_collection_id);
757
758 IF (l_request = 0) THEN
759
760 -- Concurrent Request not launched
761 l_result := g_failed;
762 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
763
764 WRITE_BACK(p_plan_id => l_plan_id,
765 p_collection_id => p_collection_id,
766 p_occurrence => p_occurrence,
767 p_status => l_result,
768 p_message => l_err_msg);
769 RETURN;
770
771 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
772 -- write back the concurrent request id.
773
774 ELSE
775
776 WRITE_BACK( p_plan_id => l_plan_id,
777 p_collection_id => p_collection_id,
778 p_occurrence => p_occurrence,
779 p_status => g_pending,
780 p_request_id => l_request);
781
782 END IF;
783
784 -- If request gets launched, proceed.
785 -- But first, wait for the WIP Move Manager Program to be completed.
786 -- After that, wait for the WIP Move Worker to be completed.
787 -- We wait 100 minutes each for these requests to be Completed. And we
788 -- check in every 15 Minutes
789
790 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
791 g_check_request_time,
792 g_total_request_time,
793 l_phase,
794 l_status,
795 l_devphase,
796 l_devstatus,
797 l_message);
798
799 l_src_code := 'QA WIP SCRAP:'||to_char(l_txn_id);
800
801 OPEN grp_cur;
802 FETCH grp_cur INTO l_group_id;
803 CLOSE grp_cur;
804
805 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
806 -- If the Manager gets completed, we need to find the correct
807 -- worker that processes the record we have inserted.
808
809 FOR i IN req_cur LOOP
810 l_child_request := i.request_id;
811
812 -- Get the Argument text of the concurrent request so that we can
813 -- extract the group_id.
814
815 OPEN arg_cur;
816 FETCH arg_cur INTO l_arg;
817 IF (to_char(l_group_id) = substr(l_arg, 1, instr(l_arg, ',') - 1)) THEN
818 CLOSE arg_cur;
819 EXIT;
820 END IF;
821 CLOSE arg_cur;
822
823 END LOOP;
824
825 ELSE
826 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_child_request,
830 g_check_request_time,
831 g_total_request_time,
832 l_phase,
833 l_status,
834 l_devphase,
835 l_devstatus,
836 l_message);
837
838 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
839 IF (substr(l_devstatus,1,7) = 'WARNING') THEN
840
841 l_result := g_warning;
842 l_transaction_id := 0;
843
844 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
845
846 l_result := g_success;
847
848 OPEN txns_cur;
849 FETCH txns_cur INTO l_transaction_id;
850 CLOSE txns_cur;
851
852 l_group_id := 0;
853
854 ELSE
855 -- If error, or any other cases, give FAILURE.
856 l_result := g_failed;
857 l_transaction_id := 0;
858
859 END IF;
860
861 -- Call for handshaking the outcome onto the Collection Plan.
862 WRITE_BACK(p_plan_id => l_plan_id,
863 p_collection_id => p_collection_id,
864 p_occurrence => p_occurrence,
865 p_status => l_result,
866 p_wmti_group_id => l_group_id,
867 p_wmt_transaction_id => l_transaction_id,
868 p_request_id => l_child_request);
869
870 END IF; -- if complete.
871
872 END WIP_SCRAP_WIP_MOVE;
873
874
875
876
877 FUNCTION WIP_SCRAP_WIP_MOVE_INT(
878 p_item_id NUMBER,
879 p_txn_id NUMBER,
880 p_job_name VARCHAR2,
881 p_dist_account_id NUMBER,
882 p_from_op_seq NUMBER,
883 p_from_intra_step NUMBER,
884 p_to_op_seq NUMBER,
885 p_to_intra_step NUMBER,
886 p_fm_op_code VARCHAR2,
887 p_to_op_code VARCHAR2,
888 p_reason_id NUMBER,
889 p_uom VARCHAR2,
890 p_quantity NUMBER,
891 p_txn_date DATE,
892 p_organization_code VARCHAR2,
893 p_collection_id VARCHAR2)
894
895 RETURN NUMBER IS
896
897 PRAGMA AUTONOMOUS_TRANSACTION;
898
899 l_request_id NUMBER;
900 l_update_by NUMBER := fnd_global.user_id;
901 l_update_name VARCHAR2(100);
902
903 CURSOR update_cur IS
904 SELECT user_name
905 FROM fnd_user_view
906 WHERE user_id = l_update_by;
907
908 BEGIN
909
910 OPEN update_cur;
911 FETCH update_cur INTO l_update_name;
912 CLOSE update_cur;
913
914 -- Insert into Interface table.
915
916 -- Bug 2689305. Added NVL() to p_reason_id in the below sql.
917
918 INSERT INTO WIP_MOVE_TXN_INTERFACE(
919 PRIMARY_ITEM_ID,
920 TRANSACTION_ID,
921 LAST_UPDATE_DATE,
922 LAST_UPDATED_BY_NAME,
923 CREATION_DATE,
924 CREATED_BY_NAME,
925 ORGANIZATION_CODE,
926 WIP_ENTITY_NAME,
927 TRANSACTION_DATE,
928 SOURCE_CODE,
929 SCRAP_ACCOUNT_ID,
930 PROCESS_PHASE,
931 PROCESS_STATUS,
932 TRANSACTION_TYPE,
933 FM_OPERATION_SEQ_NUM,
934 FM_INTRAOPERATION_STEP_TYPE,
935 TO_OPERATION_SEQ_NUM,
936 TO_INTRAOPERATION_STEP_TYPE,
937 TRANSACTION_QUANTITY,
938 TRANSACTION_UOM,
939 FM_OPERATION_CODE,
940 TO_OPERATION_CODE,
941 QA_COLLECTION_ID,
942 REASON_ID
943 )
944 VALUES(
945 p_item_id,
946 p_txn_id,
947 sysdate,
948 l_update_name,
949 sysdate,
950 l_update_name,
951 p_organization_code,
952 p_job_name,
953 p_txn_date,
954 'QA WIP SCRAP:'||to_char(p_txn_id),
955 p_dist_account_id,
956 1,
957 1,
958 1,
959 p_from_op_seq,
960 p_from_intra_step,
961 p_to_op_seq,
962 p_to_intra_step,
963 p_quantity,
964 p_uom,
965 p_fm_op_code,
966 p_to_op_code,
967 p_collection_id,
968 NVL(p_reason_id, 0));
969
970 -- Call the WIP Move Transaction Manager Program in Background.
971 l_request_id := FND_REQUEST.SUBMIT_REQUEST( 'WIP', 'WICTMS');
972
973 -- Commit Now.
974 COMMIT;
975
976 RETURN l_request_id;
977
978 END WIP_SCRAP_WIP_MOVE_INT;
979
980
981 -------------------------------------------------------------------------------
982 -- INV Scrap - Account Alias Transactions API
983 -------------------------------------------------------------------------------
984 --
985 -- Start of Comments
986 --
987 -- API name INV_SCRAP_ACCOUNT_ALIAS
988 -- Type Public
989 -- Function
990 --
991 -- Pre-reqs
992 --
993 -- Parameters
994 -- p_item => Item Name
995 -- p_revision => Item Revision
996 -- p_subinventory => Subinventory Name
997 -- p_locator => Locator Name
998 -- p_lot_number => Lot Number
999 -- p_serial_number => Serial Number
1000 -- p_transaction_uom => Transacting UOM
1001 -- p_transaction_qty => Transaction Quantity
1002 -- p_transaction_date => INV Scrap requires date in varchar2. This should be
1003 -- either Canonical format ('YYYY/MM/DD') or in Real
1004 -- format ('DD-MON-YYYY')
1005 -- p_inv_acc_alias => INV Scrap Account Name
1006 -- p_collection_id => Collection ID
1007 -- p_occurrence => Occurrence
1008 -- p_plan_name => Collection Plan Name
1009 -- p_organization_code => Organization Code, from which transaction happens
1010 -- p_launch_action => This takes two values(Yes/No)
1011 -- Pass a value of 'Yes' to successfully do INV Scrap Transaction
1012 -- p_action_fired => This takes two values(Yes/No)
1013 -- Pass a value of 'No' to successfully do INV Scrap Transaction
1014 --
1015 -- Notes
1016 --
1017 -- We are performing all the below activities here
1018 --
1019 -- 1. Inserts into the MTL_TRANSACTIONS_INTERFACE
1020 -- 2. If applicable, inserts into MTL_TRANSACTION_LOTS_INTERFACE
1021 -- and MTL_SERIAL_NUMBERS_INTERFACE
1022 -- 3. Invokes the MTL_ONLINE_TRANSACTION_PUB.PROCESS_ONLINE proc to do the
1023 -- transaction processing.
1024 -- 4. Calls WRITE_BACK proc to write the result of the transaction processor
1025 -- against the Quality record.
1026 --
1027 -- End of Comments
1028 --
1029
1030 PROCEDURE INV_SCRAP_ACCOUNT_ALIAS(
1031 p_item IN VARCHAR2,
1032 p_revision IN VARCHAR2,
1033 p_subinventory IN VARCHAR2,
1034 p_locator IN VARCHAR2,
1035 p_lot_number IN VARCHAR2,
1036 p_serial_number IN VARCHAR2,
1037 p_transaction_uom IN VARCHAR2,
1038 p_transaction_qty IN NUMBER,
1039 p_transaction_date IN VARCHAR2,
1040 p_inv_acc_alias IN VARCHAR2,
1041 p_collection_id IN NUMBER,
1042 p_occurrence IN NUMBER,
1043 p_organization_code IN VARCHAR2,
1044 p_plan_name IN VARCHAR2,
1045 p_launch_action IN VARCHAR2,
1046 p_action_fired IN VARCHAR2) IS
1047
1048
1049 l_header_id NUMBER;
1050 l_plan_id NUMBER;
1051 l_organization_id NUMBER;
1052 l_transaction_date DATE;
1053 l_disposition_id NUMBER;
1054
1055 -- Meant for Inventory transaction processing
1056 -- Using process online which is similar to the trip stop/pick release functionality
1057
1058 l_outcome BOOLEAN := TRUE;
1059 l_error_code VARCHAR2(240) := NULL;
1060 l_error_explanation VARCHAR2(240) := NULL;
1061 l_time_out NUMBER := 1200;
1062 l_profile_time_out NUMBER;
1063 l_phase VARCHAR2(240);
1064 l_result VARCHAR2(240);
1065
1066 -- Bug 3684073. These variables are no longer required.
1067 -- l_launch_action NUMBER;
1068 -- l_action_fired NUMBER;
1069
1070 l_int_txn_id NUMBER;
1071 l_txn_id NUMBER;
1072 l_item_id NUMBER;
1073 l_locator_id NUMBER;
1074
1075
1076 CURSOR mmt_cur (set_id NUMBER) IS
1077 SELECT transaction_id
1078 FROM mtl_material_transactions
1079 WHERE transaction_set_id = set_id;
1080
1081 CURSOR mti_cur (header_id NUMBER) IS
1082 SELECT transaction_interface_id
1083 FROM mtl_transactions_interface
1084 WHERE transaction_header_id = header_id;
1085
1086 CURSOR disp IS
1087 SELECT disposition_id
1088 FROM mtl_generic_dispositions_kfv
1089 WHERE organization_id = l_organization_id
1090 AND concatenated_segments = p_inv_acc_alias;
1091
1092
1093 BEGIN
1094
1095 -- Get the value entered in confirm_action Collection element.
1096
1097 -- Bug 3684073. We should not derive the lookup_code value from
1098 -- mfg_lookups because the value passed to this api would be the
1099 -- qa_plan_char_value_lookups.short_code, which is not a translated
1100 -- column. The mfg_lookups view would have the lookup meaning in the
1101 -- language used in the current session.
1102 --
1103 -- Commented the below piece of code and compared p_launch_action
1104 -- and p_action_fired parameters below with the new constants to resolve
1105 -- the value entered. kabalakr.
1106
1107 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
1108 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
1109
1110 -- The Action Code should get executed only if
1111 -- launch_action is 'Yes' and action_fired is 'No'
1112
1113 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
1114 NULL;
1115 ELSE
1116 -- dont fire the action.
1117 RETURN;
1118 END IF;
1119
1120 -- Get the org_id, plan_id
1121 l_organization_id := get_organization_id (p_organization_code);
1122 l_plan_id := get_plan_id(p_plan_name);
1123
1124 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
1125
1126 -- We may need to populate appropriate error message here before return.
1127 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1128 END IF;
1129
1130 -- Update the Dispostion Status to 'Pending'.
1131 UPDATE_STATUS(l_plan_id, p_collection_id, p_occurrence);
1132
1133 IF p_transaction_date IS NULL THEN
1134 l_transaction_date := sysdate;
1135 ELSE
1136 l_transaction_date := qltdate.any_to_date(p_transaction_date);
1137 END IF;
1138
1139 -- Get the Inventory Item ID and Locator ID.
1140 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
1141 l_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_locator);
1142
1143 -- Get the Disposition ID.
1144 OPEN disp;
1145 FETCH disp INTO l_disposition_id;
1146 CLOSE disp;
1147
1148 -- Call the INV_SCRAP_ACCOUNT_ALIAS_INT() AT proc to Insert into interface table
1149 l_header_id := INV_SCRAP_ACCOUNT_ALIAS_INT(
1150 l_item_id,
1151 p_revision,
1152 p_subinventory,
1153 l_locator_id,
1154 p_lot_number,
1155 p_serial_number,
1156 p_transaction_uom,
1157 p_transaction_qty,
1158 l_transaction_date,
1159 l_disposition_id,
1160 p_collection_id,
1161 p_occurrence,
1162 l_organization_id);
1163
1164
1165 l_outcome := mtl_online_transaction_pub.process_online(l_header_id,
1166 l_time_out,
1167 l_error_code,
1168 l_error_explanation);
1169 IF (l_outcome <> TRUE) THEN
1170 l_result := g_failed;
1171
1172 OPEN mti_cur(l_header_id);
1173 FETCH mti_cur INTO l_int_txn_id;
1174 CLOSE mti_cur;
1175
1176 l_txn_id := 0;
1177
1178 ELSE
1179 l_result := g_success;
1180
1181 OPEN mmt_cur(l_header_id);
1182 FETCH mmt_cur INTO l_txn_id;
1183 CLOSE mmt_cur;
1184
1185 l_int_txn_id := 0;
1186
1187
1188 END IF;
1189
1190
1191 WRITE_BACK(p_plan_id => l_plan_id,
1192 p_collection_id => p_collection_id,
1193 p_occurrence => p_occurrence,
1194 p_status => l_result,
1195 p_mti_transaction_header_id => l_header_id,
1196 p_mti_transaction_interface_id => l_int_txn_id,
1197 p_mmt_transaction_id => l_txn_id);
1198
1199
1200 END INV_SCRAP_ACCOUNT_ALIAS;
1201
1202 FUNCTION INV_SCRAP_ACCOUNT_ALIAS_INT(
1203 p_item_id NUMBER,
1204 p_revision VARCHAR2,
1205 p_subinventory VARCHAR2,
1206 p_locator_id NUMBER,
1207 p_lot_number VARCHAR2,
1208 p_serial_number VARCHAR2,
1209 p_transaction_uom VARCHAR2,
1210 p_transaction_qty NUMBER,
1211 p_transaction_date DATE,
1212 p_disposition_id NUMBER,
1213 p_collection_id NUMBER,
1214 p_occurrence NUMBER,
1215 p_organization_id NUMBER)
1216 RETURN NUMBER IS
1217
1218 PRAGMA AUTONOMOUS_TRANSACTION;
1219
1220 l_header_id NUMBER;
1221 l_interface_id NUMBER;
1222 l_update_by NUMBER := fnd_global.user_id;
1223 l_lot_control_code NUMBER;
1224 l_serial_control_code NUMBER;
1225 l_transaction_qty NUMBER := -p_transaction_qty;
1226
1227 CURSOR item_cur IS
1228 SELECT lot_control_code,serial_number_control_code
1229 FROM mtl_system_items_b
1230 WHERE inventory_item_id = p_item_id
1231 AND organization_id = p_organization_id;
1232
1233
1234 BEGIN
1235
1236 OPEN item_cur;
1237 FETCH item_cur INTO l_lot_control_code,l_serial_control_code;
1238 CLOSE item_cur;
1239
1240 -- Insert into Interface table.
1241 INSERT INTO MTL_TRANSACTIONS_INTERFACE (
1242 TRANSACTION_HEADER_ID,
1243 TRANSACTION_INTERFACE_ID,
1244 SOURCE_CODE,
1245 SOURCE_HEADER_ID,
1246 SOURCE_LINE_ID,
1247 PROCESS_FLAG,
1248 TRANSACTION_MODE,
1249 LOCK_FLAG,
1250 LAST_UPDATE_DATE,
1251 LAST_UPDATED_BY,
1252 CREATION_DATE,
1253 CREATED_BY,
1254 INVENTORY_ITEM_ID,
1255 REVISION,
1256 ORGANIZATION_ID,
1257 TRANSACTION_QUANTITY,
1258 TRANSACTION_UOM,
1259 TRANSACTION_DATE,
1260 SUBINVENTORY_CODE,
1261 LOCATOR_ID,
1262 TRANSACTION_TYPE_ID,
1263 TRANSACTION_ACTION_ID,
1264 TRANSACTION_SOURCE_TYPE_ID,
1265 TRANSACTION_SOURCE_ID)
1266 VALUES(
1267 mtl_material_transactions_s.nextval,
1268 mtl_material_transactions_s.nextval,
1269 'QA ACTION: INVSCRAP',
1270 p_collection_id,
1271 p_occurrence,
1272 1,
1273 3,
1274 2,
1275 SYSDATE,
1276 l_update_by,
1277 SYSDATE,
1278 l_update_by,
1279 p_item_id,
1280 p_revision,
1281 p_organization_id,
1282 l_transaction_qty,
1283 p_transaction_uom,
1284 p_transaction_date,
1285 p_subinventory,
1286 p_locator_id,
1287 31,
1288 1,
1289 6,
1290 p_disposition_id)
1291
1292 RETURNING TRANSACTION_HEADER_ID INTO l_header_id;
1293
1294 IF l_serial_control_code IN (2,5) OR l_lot_control_code = 2 THEN
1295
1296 -- The item is serial control set to predefined/at receipt.
1297 -- (l_serial_control_code IN (2,5)).
1298 -- The item is lot controlled.(l_lot_control_code = 2).
1299
1300 -- One of Lot or Serial controls are existing for the item
1301 -- Need to insert in appropriate interface table against
1302 -- the transaction_header_id
1303
1304 l_interface_id := l_header_id;
1305 END IF;
1306
1307 IF l_lot_control_code = 2 THEN
1308
1309 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
1310 TRANSACTION_INTERFACE_ID,
1311 SERIAL_TRANSACTION_TEMP_ID,
1312 LOT_NUMBER,
1313 TRANSACTION_QUANTITY,
1314 LAST_UPDATE_DATE,
1315 LAST_UPDATED_BY,
1316 CREATION_DATE,
1317 CREATED_BY
1318 ) VALUES (
1319 l_interface_id,
1320 l_interface_id,
1321 p_lot_number,
1322 l_transaction_qty,
1323 SYSDATE,
1324 l_update_by,
1325 SYSDATE,
1326 l_update_by
1327 );
1328 END IF;
1329
1330 IF l_serial_control_code IN (2,5) THEN
1331 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
1332 TRANSACTION_INTERFACE_ID,
1333 FM_SERIAL_NUMBER,
1334 LAST_UPDATE_DATE,
1335 LAST_UPDATED_BY,
1336 CREATION_DATE,
1337 CREATED_BY
1338 ) VALUES (
1339 l_interface_id,
1340 p_serial_number,
1341 SYSDATE,
1342 l_update_by,
1343 SYSDATE,
1344 l_update_by
1345 );
1346 END IF;
1347
1348 COMMIT;
1349
1350 RETURN l_header_id;
1351
1352 END INV_SCRAP_ACCOUNT_ALIAS_INT;
1353
1354
1355
1356 -------------------------------------------------------------------------------
1357 -- PO Return To Vendor API
1358 -------------------------------------------------------------------------------
1359 -- Start of Comments
1360 -- API name PO_RETURN_TO_VENDOR
1361 -- Type Public
1362 -- Function
1363 --
1364 -- Pre-reqs
1365 --
1366 -- Parameters
1367 -- p_item => Item Name
1368 -- p_revision => Item Revision
1369 -- p_subinventory => Subinventory Name
1370 -- p_locator => Locator Name
1371 -- p_lot_number => Lot Number
1372 -- p_serial_number => Serial Number
1373 -- p_uom_code => Transacting UOM code
1374 -- p_quantity => Transaction Quantity(Return to Vendor Quantity)
1375 -- p_po_number => PO Number, against which item is return to vendor
1376 -- p_po_line_number => PO Line Number
1377 -- p_po_shipment_number => PO Shipment Number
1378 -- p_po_receipt_number => PO Receipt Number
1379 --
1380 -- p_transaction_date => Transaction Date in varchar2. This should be either Canonical
1381 -- format ('YYYY/MM/DD') or in Real format ('DD-MON-YYYY')
1382 -- p_collection_id => Collection ID
1383 -- p_occurrence => Occurrence
1384 -- p_plan_name => Collection Plan Name
1385 -- p_organization_code => Organization Code, from which transaction happens
1386 --
1387 -- p_launch_action => This takes two values(Yes/No)
1388 -- Pass a value of 'Yes' to successfully return to vendor
1389 -- p_action_fired => This takes two values(Yes/No)
1390 -- Pass a value of 'No' to successfully return to vendor
1391 --
1392 -- Version Current version = 1.0
1393 -- Initial version = 1.0
1394 --
1395 -- Notes
1396 --
1397 -- End of Comments
1398
1399 PROCEDURE PO_RETURN_TO_VENDOR(
1400 p_item IN VARCHAR2,
1401 p_revision IN VARCHAR2,
1402 p_subinventory IN VARCHAR2,
1403 p_locator IN VARCHAR2,
1404 p_lot_number IN VARCHAR2,
1405 p_serial_number IN VARCHAR2,
1406 p_uom_code IN VARCHAR2,
1407 p_quantity IN NUMBER,
1408 p_po_number IN VARCHAR2,
1409 p_po_line_number IN NUMBER,
1410 p_po_shipment_number IN NUMBER,
1411 p_po_receipt_number IN NUMBER,
1412 p_transaction_date IN VARCHAR2,
1413 p_collection_id IN NUMBER,
1414 p_occurrence IN NUMBER,
1415 p_plan_name IN VARCHAR2,
1416 p_organization_code IN VARCHAR2,
1417 p_launch_action IN VARCHAR2,
1418 p_action_fired IN VARCHAR2 ) IS
1419
1420
1421 BEGIN
1422
1423 null;
1424
1425 END PO_RETURN_TO_VENDOR;
1426
1427
1428 FUNCTION PO_RETURN_TO_VENDOR_INT(
1429 p_item_id IN NUMBER,
1430 p_revision IN VARCHAR2,
1431 p_subinventory IN VARCHAR2,
1432 p_locator_id IN NUMBER,
1433 p_lot_number IN VARCHAR2,
1434 p_serial_number IN VARCHAR2,
1435 p_uom_code IN VARCHAR2,
1436 p_quantity IN NUMBER,
1437 p_po_number IN VARCHAR2,
1438 p_po_line_number IN NUMBER,
1439 p_po_shipment_number IN NUMBER,
1440 p_po_receipt_number IN NUMBER,
1441 p_transaction_date IN DATE,
1442 p_collection_id IN NUMBER,
1443 p_occurrence IN NUMBER,
1444 p_plan_id IN NUMBER,
1445 p_organization_id IN NUMBER,
1446 p_interface_transaction_id IN NUMBER)
1447 RETURN NUMBER IS
1448
1449 BEGIN
1450
1451 RETURN NULL;
1452
1453 END PO_RETURN_TO_VENDOR_INT;
1454
1455
1456
1457 -------------------------------------------------------------------------------
1458 -- Inventory Create Move Order API
1459 -------------------------------------------------------------------------------
1460 -- Start of Comments
1461 -- API name INV_CREATE_MOVE_ORDER
1462 -- Type Public
1463 -- Function
1464 --
1465 -- Pre-reqs
1466 --
1467 -- Parameters
1468 -- p_item => Item Name
1469 -- p_revision => Item Revision
1470 -- p_from_subinventory => From Subinventory Name
1471 -- p_from_locator => From Locator Name
1472 -- p_lot_number => Lot Number
1473 -- p_serial_number => Serial Number
1474 -- p_uom_code => Transacting UOM code
1475 -- p_quantity => Quantity
1476 -- p_to_subinventory => From Subinventory Name
1477 -- p_to_locator => From Locator Name
1478 -- p_date_required => Moveorder Required date in varchar2. This should be either Canonical
1479 -- format ('YYYY/MM/DD') or in Real format ('DD-MON-YYYY')
1480 --
1481 -- p_project_number => Project Number
1482 -- p_task_number => Task Number
1483 --
1484 -- p_collection_id => Collection ID
1485 -- p_occurrence => Occurrence
1486 -- p_plan_name => Collection Plan Name
1487 -- p_organization_code => Organization Code, from which transaction happens
1488 --
1489 -- p_launch_action => This takes two values(Yes/No)
1490 -- Pass a value of 'Yes' to successfully create move order
1491 -- p_action_fired => This takes two values(Yes/No)
1492 -- Pass a value of 'No' to successfully create move order
1493 --
1494 -- Version Current version = 1.0
1495 -- Initial version = 1.0
1496 --
1497 -- Notes
1498 --
1499 -- Move order created by this actions will be of type 'Move Order Transfer'
1500 -- approval is Preapproved. And the columns
1501 -- mtl_txn_request_header.Request Number = mtl_txn_request_header.Header_id
1502 --
1503 -- End of Comments
1504
1505
1506
1507 PROCEDURE INV_CREATE_MOVE_ORDER (
1508 p_item IN VARCHAR2,
1509 p_revision IN VARCHAR2,
1510 p_from_subinventory IN VARCHAR2,
1511 p_from_locator IN VARCHAR2,
1512 p_lot_number IN VARCHAR2,
1513 p_serial_number IN VARCHAR2,
1514 p_uom_code IN VARCHAR2,
1515 p_quantity IN NUMBER,
1516 p_to_subinventory IN VARCHAR2,
1517 p_to_locator IN VARCHAR2,
1518 p_date_required IN VARCHAR2,
1519 p_project_number IN VARCHAR2,
1520 p_task_number IN VARCHAR2,
1521 p_collection_id IN NUMBER,
1522 p_occurrence IN NUMBER,
1523 p_plan_name IN VARCHAR2,
1524 p_organization_code IN VARCHAR2,
1525 p_launch_action IN VARCHAR2,
1526 p_action_fired IN VARCHAR2 ) IS
1527
1528 -- Bug 3684073. These variables are no longer required.
1529 -- l_launch_action NUMBER;
1530 -- l_action_fired NUMBER;
1531
1532 l_item_id NUMBER;
1533 l_from_locator_id NUMBER;
1534 l_to_locator_id NUMBER;
1535 l_project_id NUMBER;
1536 l_task_id NUMBER;
1537
1538 l_organization_id NUMBER;
1539 l_plan_id NUMBER;
1540 l_status VARCHAR2(2000);
1541 l_request_number VARCHAR2(30); -- holds move order request number
1542
1543 BEGIN
1544
1545 -- Get the value entered in confirm_action Collection element.
1546
1547 -- Bug 3684073. We should not derive the lookup_code value from
1548 -- mfg_lookups because the value passed to this api would be the
1549 -- qa_plan_char_value_lookups.short_code, which is not a translated
1550 -- column. The mfg_lookups view would have the lookup meaning in the
1551 -- language used in the current session.
1552 --
1553 -- Commented the below piece of code and compared p_launch_action
1554 -- and p_action_fired parameters below with the new constants to resolve
1555 -- the value entered. kabalakr.
1556
1557 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
1558 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
1559
1560 -- The Action Code should get executed only if
1561 -- Launch_action is 'Yes' and Action_fired is 'No'
1562
1563 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
1564 NULL;
1565
1566 ELSE
1567 -- dont fire the action.
1568 RETURN;
1569 END IF;
1570
1571 l_organization_id := Get_organization_id (p_organization_code);
1572 l_plan_id := Get_plan_id(p_plan_name);
1573
1574 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
1575
1576 -- We may need to populate appropriate error message here before return.
1577 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1578 END IF;
1579
1580 -- Update the Disposition Status to 'Pending'.
1581 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
1582
1583 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
1584 l_from_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_from_locator);
1585 l_to_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_to_locator);
1586
1587 l_project_id := qa_flex_util.get_project_id(p_project_number);
1588 l_task_id := qa_flex_util.get_task_id(l_project_id,p_task_number);
1589
1590
1591 l_status := INV_CREATE_MOVE_ORDER_INT (
1592 l_item_id ,
1593 p_revision ,
1594 p_from_subinventory ,
1595 l_from_locator_id,
1596 p_lot_number,
1597 p_serial_number,
1598 p_uom_code,
1599 p_quantity,
1600 p_to_subinventory,
1601 l_to_locator_id,
1602 qltdate.any_to_date (p_date_required),
1603 l_project_id,
1604 l_task_id,
1605 l_organization_id,
1606 l_request_number);
1607
1608 -- Call for handshaking the outcome onto the Collection Plan.
1609
1610 WRITE_BACK(p_plan_id => l_plan_id,
1611 p_collection_id => p_collection_id,
1612 p_occurrence => p_occurrence,
1613 p_status => l_status,
1614 p_move_order_number => l_request_number);
1615
1616 END INV_CREATE_MOVE_ORDER;
1617
1618
1619 FUNCTION INV_CREATE_MOVE_ORDER_INT (
1620 p_item_id IN NUMBER,
1621 p_revision IN VARCHAR2,
1622 p_from_subinventory IN VARCHAR2,
1623 p_from_locator_id IN NUMBER,
1624 p_lot_number IN VARCHAR2,
1625 p_serial_number IN VARCHAR2,
1626 p_uom_code IN VARCHAR2,
1627 p_quantity IN NUMBER,
1628 p_to_subinventory IN VARCHAR2,
1629 p_to_locator_id IN NUMBER,
1630 p_date_required IN DATE,
1631 p_project_id IN NUMBER,
1632 p_task_id IN NUMBER,
1633 p_organization_id IN NUMBER,
1634 x_request_number OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1635
1636
1637 l_lot_control_code NUMBER;
1638 l_serial_control_code NUMBER;
1639 l_revision_control_code NUMBER;
1640
1641 l_status VARCHAR2(2000);
1642 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1643 l_msg_count NUMBER;
1644 l_msg_data VARCHAR2(240);
1645 l_message VARCHAR2(2000);
1646
1647 -- Bug 2698365. GSCC warning fix. Commented the usage of
1648 -- FND_API.G_MISS_NUM. kabalakr.
1649 l_header_id NUMBER; -- := FND_API.G_MISS_NUM;
1650
1651 l_line_num NUMBER := 0;
1652 l_order_count NUMBER := 1;
1653 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1654
1655 l_request_number VARCHAR2(30);
1656
1657 l_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
1658 l_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type;
1659
1660 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1661 l_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;
1662
1663 CURSOR item_att_cur IS
1664 SELECT revision_qty_control_code,
1665 lot_control_code,serial_number_control_code
1666 FROM mtl_system_items_b
1667 WHERE inventory_item_id = p_item_id
1668 AND organization_id = p_organization_id;
1669
1670 BEGIN
1671
1672 -- Bug 2698365. GSCC warning fix. Moved the usage of FND_API.G_MISS_NUM
1673 -- to here. kabalakr.
1674 l_header_id := FND_API.G_MISS_NUM;
1675
1676 -- populate Header WHO columns
1677 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
1678 l_trohdr_rec.creation_date := sysdate;
1679 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
1680 l_trohdr_rec.last_update_date := sysdate;
1681 l_trohdr_rec.last_update_login := FND_GLOBAL.USER_ID;
1682
1683 l_trohdr_rec.from_subinventory_code := p_from_subinventory;
1684 l_trohdr_rec.to_subinventory_code := p_to_subinventory;
1685
1686 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1687 l_trohdr_rec.organization_id := p_organization_id;
1688 l_trohdr_rec.status_date := sysdate;
1689 l_trohdr_rec.date_required := p_date_required;
1690 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
1691 l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1692 l_trohdr_rec.db_flag := FND_API.G_TRUE;
1693 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1694
1695 l_line_num := l_line_num + 1;
1696
1697 -- populate Lines WHO columns.
1698
1699 l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
1700 l_trolin_tbl(l_order_count).creation_date := sysdate;
1701 l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
1702 l_trolin_tbl(l_order_count).last_update_date := sysdate;
1703 l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
1704
1705 l_trolin_tbl(l_order_count).date_required := p_date_required;
1706 l_trolin_tbl(l_order_count).status_date := sysdate;
1707
1708 l_trolin_tbl(l_order_count).inventory_item_id := p_item_id;
1709
1710
1711 OPEN item_att_cur;
1712 FETCH item_att_cur INTO
1713 l_revision_control_code,
1714 l_lot_control_code,l_serial_control_code;
1715
1716 IF l_revision_control_code = 2 THEN
1717 -- revision control item
1718 l_trolin_tbl(l_order_count).revision := p_revision;
1719 END IF;
1720
1721 IF l_lot_control_code = 2 THEN
1722 -- lot control item
1723 l_trolin_tbl(l_order_count).lot_number := p_lot_number;
1724 END IF;
1725
1726 IF l_serial_control_code in( 2,5) THEN
1727 -- serial control item
1728 l_trolin_tbl(l_order_count).serial_number_start := p_serial_number;
1729 l_trolin_tbl(l_order_count).serial_number_end := p_serial_number;
1730 END IF;
1731
1732 CLOSE item_att_cur;
1733
1734 l_trolin_tbl(l_order_count).quantity := p_quantity;
1735 l_trolin_tbl(l_order_count).uom_code := p_uom_code;
1736
1737 l_trolin_tbl(l_order_count).from_subinventory_code := p_from_subinventory;
1738 l_trolin_tbl(l_order_count).from_locator_id := p_from_locator_id;
1739 l_trolin_tbl(l_order_count).to_subinventory_code := p_to_subinventory;
1740 l_trolin_tbl(l_order_count).to_locator_id := p_to_locator_id;
1741
1742 l_trolin_tbl(l_order_count).organization_id := p_organization_id;
1743 l_trolin_tbl(l_order_count).project_id := p_project_id;
1744 l_trolin_tbl(l_order_count).task_id := p_task_id;
1745
1746
1747 l_trolin_tbl(l_order_count).header_id := l_trohdr_rec.header_id;
1748 l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
1749
1750 l_trolin_tbl(l_order_count).line_number := l_line_num;
1751 l_trolin_tbl(l_order_count).line_status := INV_GLOBALS.G_TO_STATUS_PREAPPROVED;
1752
1753 l_trolin_Tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1754
1755 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
1756 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
1757
1758
1759 Inv_Move_Order_Pub.Process_Move_order(
1760 p_api_version_number => 1.0,
1761 p_init_msg_list => FND_API.G_FALSE,
1762 p_return_values => FND_API.G_TRUE,
1763 p_commit => FND_API.G_FALSE,
1764 x_return_status => l_return_status,
1765 x_msg_count => l_msg_count,
1766 x_msg_data => l_msg_data,
1767 p_trohdr_rec => l_trohdr_rec,
1768 p_trohdr_val_rec => l_trohdr_val_rec,
1769 p_trolin_tbl => l_trolin_tbl,
1770 p_trolin_val_tbl => l_trolin_val_tbl,
1771 x_trohdr_rec => l_trohdr_rec,
1772 x_trohdr_val_rec => l_trohdr_val_rec,
1773 x_trolin_tbl => l_trolin_tbl,
1774 x_trolin_val_tbl => l_trolin_val_tbl
1775 );
1776
1777
1778 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1779 l_status := g_failed;
1780 x_request_number := NULL;
1781
1782 ELSE
1783 -- on success commit the txn.
1784 COMMIT;
1785
1786 l_status := g_success;
1787 x_request_number := l_trohdr_rec.request_number;
1788 END IF;
1789
1790 RETURN l_status;
1791
1792 END INV_CREATE_MOVE_ORDER_INT;
1793
1794
1795 -------------------------------------------------------------------------------
1796 -- WIP Component Return to Inventory
1797 -------------------------------------------------------------------------------
1798 --
1799 -- Start of Comments
1800 --
1801 -- API name WIP_COMP_RETURN
1802 -- Type Public
1803 -- Function
1804 --
1805 -- Pre-reqs
1806 --
1807 -- Parameters
1808 -- p_item => Item Name
1809 -- p_job_name => Job Name to do the WIP Component Issue Transaction
1810 -- p_revision => Item Revision
1811 -- p_subinventory => Subinventory Name
1812 -- p_locator => Locator Name
1813 -- p_lot_number => Lot Number
1814 -- p_serial_number => Serial Number
1815 -- p_transaction_uom => Transacting UOM
1816 -- p_transaction_qty => Transaction Quantity
1817 -- p_transaction_date => WIP Component Issue requires date in varchar2. This
1818 -- should be either Canonical format ('YYYY/MM/DD') or
1819 -- in Real format ('DD-MON-YYYY')
1820 -- p_op_seq_num => Operation Sequence Number
1821 -- p_reason_code => Reason Code
1822 -- p_collection_id => Collection ID
1823 -- p_occurrence => Occurrence
1824 -- p_plan_name => Collection Plan Name
1825 -- p_organization_code => Organization Code, from which transaction happens
1826 -- p_launch_action => This takes two values(Yes/No)
1827 -- Pass a value of 'Yes' to successfully perform
1828 -- WIP Component Issue Transaction
1829 -- p_action_fired => This takes two values(Yes/No)
1830 -- Pass a value of 'No' to successfully perform
1831 -- WIP Component Issue Transaction
1832 --
1833 -- Notes
1834 --
1835 -- We are performing all the below activities here
1836 --
1837 -- 1. Get the collection element values. Derive the other values.
1838 -- 2. Call WIP_MATERIAL_TXN_INT () function for inserting into interface table.
1839 -- 3. Call mtl_online_transaction_pub.process_online () procedure.
1840 -- 4. Call the WRITE_BACK () procedure for handshaking.
1841 --
1842 --
1843 -- End of Comments
1844 --
1845
1846 PROCEDURE WIP_COMP_RETURN(p_job_name IN VARCHAR2,
1847 p_item IN VARCHAR2,
1848 p_revision IN VARCHAR2,
1849 p_subinventory IN VARCHAR2,
1850 p_locator IN VARCHAR2,
1851 p_lot_number IN VARCHAR2,
1852 p_serial_number IN VARCHAR2,
1853 p_transaction_uom IN VARCHAR2,
1854 p_transaction_qty IN NUMBER,
1855 p_transaction_date IN VARCHAR2,
1856 p_op_seq_num IN NUMBER,
1857 p_reason_code IN VARCHAR2,
1858 p_collection_id IN NUMBER,
1859 p_occurrence IN NUMBER,
1860 p_organization_code IN VARCHAR2,
1861 p_plan_name IN VARCHAR2,
1862 p_launch_action IN VARCHAR2,
1863 p_action_fired IN VARCHAR2) IS
1864
1865
1866 l_plan_id NUMBER;
1867 l_organization_id NUMBER;
1868
1869 -- Bug 3684073. These variables are no longer required.
1870 -- l_launch_action NUMBER;
1871 -- l_action_fired NUMBER;
1872
1873 l_outcome BOOLEAN := TRUE;
1874 l_header_id NUMBER;
1875 l_result VARCHAR2(240);
1876 l_error_code VARCHAR2(240) := NULL;
1877 l_error_explanation VARCHAR2(240) := NULL;
1878 l_time_out NUMBER := 1200;
1879 l_int_txn_id NUMBER;
1880 l_txn_id NUMBER;
1881 l_transaction_date DATE;
1882 l_item_id NUMBER;
1883 l_locator_id NUMBER;
1884 l_reason_id NUMBER;
1885
1886 CURSOR mmt_cur (set_id NUMBER) IS
1887 SELECT transaction_id
1888 FROM mtl_material_transactions
1889 WHERE transaction_set_id = set_id;
1890
1891 CURSOR mti_cur (header_id NUMBER) IS
1892 SELECT transaction_interface_id
1893 FROM mtl_transactions_interface
1894 WHERE transaction_header_id = header_id;
1895
1896 CURSOR reason_cur IS
1897 SELECT reason_id
1898 FROM mtl_transaction_reasons_val_v
1899 WHERE reason_name LIKE p_reason_code;
1900
1901
1902 BEGIN
1903
1904 -- Get the value entered in confirm_action Collection element.
1905
1906 -- Bug 3684073. We should not derive the lookup_code value from
1907 -- mfg_lookups because the value passed to this api would be the
1908 -- qa_plan_char_value_lookups.short_code, which is not a translated
1909 -- column. The mfg_lookups view would have the lookup meaning in the
1910 -- language used in the current session.
1911 --
1912 -- Commented the below piece of code and compared p_launch_action
1913 -- and p_action_fired parameters below with the new constants to resolve
1914 -- the value entered. kabalakr.
1915
1916 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
1917 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
1918
1919 -- The Action Code should get executed only if
1920 -- launch_action is 'Yes' and action_fired is 'No'
1921
1922 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
1923 NULL;
1924 ELSE
1925 -- dont fire the action.
1926 RETURN;
1927 END IF;
1928
1929 -- Get the plan_id and org_id now.
1930
1931 l_organization_id := get_organization_id (p_organization_code);
1932 l_plan_id := get_plan_id(p_plan_name);
1933
1934 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
1935
1936 -- We may need to populate appropriate error message here before return.
1937 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1938 END IF;
1939
1940 IF p_transaction_date IS NULL THEN
1941 l_transaction_date := sysdate;
1942 ELSE
1943 l_transaction_date := qltdate.any_to_date(p_transaction_date);
1944 END IF;
1945
1946 -- Update the Disposition Status to 'Pending'.
1947 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
1948
1949 -- Get the Inventory Item ID and Locator ID and Reason ID.
1950 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
1951 l_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_locator);
1952
1953 OPEN reason_cur;
1954 FETCH reason_cur INTO l_reason_id;
1955 CLOSE reason_cur;
1956
1957
1958 l_header_id := WIP_MATERIAL_TXN_INT(
1959 p_job_name,
1960 l_item_id,
1961 p_revision,
1962 p_subinventory,
1963 l_locator_id,
1964 p_lot_number,
1965 p_serial_number,
1966 p_transaction_uom,
1967 p_transaction_qty,
1968 l_transaction_date,
1969 p_op_seq_num,
1970 l_reason_id,
1971 'QA ACTION:WIP COMP RETURN',
1972 43,
1973 27,
1974 p_collection_id,
1975 p_occurrence,
1976 l_organization_id);
1977
1978 l_outcome := MTL_ONLINE_TRANSACTION_PUB.PROCESS_ONLINE(l_header_id,
1979 l_time_out,
1980 l_error_code,
1981 l_error_explanation);
1982
1983
1984 IF (l_outcome <> TRUE) THEN
1985 l_result := g_failed;
1986
1987 OPEN mti_cur(l_header_id);
1988 FETCH mti_cur INTO l_int_txn_id;
1989 CLOSE mti_cur;
1990
1991 l_txn_id := 0;
1992
1993
1994 ELSE
1995 l_result := g_success;
1996
1997 OPEN mmt_cur(l_header_id);
1998 FETCH mmt_cur INTO l_txn_id;
1999 CLOSE mmt_cur;
2000
2001 l_int_txn_id := 0;
2002
2003
2004 END IF;
2005
2006
2007 -- call WRITE_BACK procedure.
2008
2009 WRITE_BACK(p_plan_id => l_plan_id,
2010 p_collection_id => p_collection_id,
2011 p_occurrence => p_occurrence,
2012 p_status => l_result,
2013 p_mti_transaction_header_id => l_header_id,
2014 p_mti_transaction_interface_id => l_int_txn_id,
2015 p_mmt_transaction_id => l_txn_id);
2016
2017 END WIP_COMP_RETURN;
2018
2019
2020 -------------------------------------------------------------------------------
2021 -- WIP Component Issue
2022 -------------------------------------------------------------------------------
2023 --
2024 -- Start of Comments
2025 --
2026 -- API name WIP_COMP_ISSUE
2027 -- Type Public
2028 -- Function
2029 --
2030 -- Pre-reqs
2031 --
2032 -- Parameters
2033 -- p_item => Item Name
2034 -- p_job_name => Job Name
2035 -- p_revision => Item Revision
2036 -- p_subinventory => Subinventory Name
2037 -- p_locator => Locator Name
2038 -- p_lot_number => Lot Number
2039 -- p_serial_number => Serial Number
2040 -- p_transaction_uom => Transacting UOM
2041 -- p_transaction_qty => Transaction Quantity
2042 -- p_transaction_date => WIP Component Issue requires date in varchar2. This
2043 -- should be either Canonical format ('YYYY/MM/DD') or
2044 -- in Real format ('DD-MON-YYYY')
2045 -- p_op_seq_num => Operation Sequence Number
2046 -- p_reason_code => Reason Code
2047 -- p_collection_id => Collection ID
2048 -- p_occurrence => Occurrence
2049 -- p_plan_name => Collection Plan Name
2050 -- p_organization_code => Organization Code, from which transaction happens
2051 -- p_launch_action => This takes two values(Yes/No)
2052 -- Pass a value of 'Yes' to successfully perform
2053 -- WIP Component Issue Transaction
2054 -- p_action_fired => This takes two values(Yes/No)
2055 -- Pass a value of 'No' to successfully perform
2056 -- WIP Component Issue Transaction
2057 --
2058 -- Notes
2059 --
2060 -- We are performing all the below activities here
2061 --
2062 -- 1. Get the collection element values. Derive the other values.
2063 -- 2. Call WIP_MATERIAL_TXN_INT () function for inserting into interface table.
2064 -- 3. Call mtl_online_transaction_pub.process_online () procedure.
2065 -- 4. Call the WRITE_BACK () procedure for handshaking.
2066 --
2067 --
2068 -- End of Comments
2069 --
2070
2071 PROCEDURE WIP_COMP_ISSUE (p_job_name IN VARCHAR2,
2072 p_item IN VARCHAR2,
2073 p_revision IN VARCHAR2,
2074 p_subinventory IN VARCHAR2,
2075 p_locator IN VARCHAR2,
2076 p_lot_number IN VARCHAR2,
2077 p_serial_number IN VARCHAR2,
2078 p_transaction_uom IN VARCHAR2,
2079 p_transaction_qty IN NUMBER,
2080 p_transaction_date IN VARCHAR2,
2081 p_op_seq_num IN NUMBER,
2082 p_reason_code IN VARCHAR2,
2083 p_collection_id IN NUMBER,
2084 p_occurrence IN NUMBER,
2085 p_organization_code IN VARCHAR2,
2086 p_plan_name IN VARCHAR2,
2087 p_launch_action IN VARCHAR2,
2088 p_action_fired IN VARCHAR2) IS
2089
2090
2091 l_plan_id NUMBER;
2092 l_organization_id NUMBER;
2093
2094 -- Bug 3684073. These variables are no longer required.
2095 -- l_launch_action NUMBER;
2096 -- l_action_fired NUMBER;
2097
2098 l_outcome BOOLEAN := TRUE;
2099 l_header_id NUMBER;
2100 l_result VARCHAR2(240);
2101 l_error_code VARCHAR2(240) := NULL;
2102 l_error_explanation VARCHAR2(240) := NULL;
2103 l_time_out NUMBER := 1200;
2104 l_int_txn_id NUMBER;
2105 l_txn_id NUMBER;
2106 l_transaction_date DATE;
2107 l_item_id NUMBER;
2108 l_locator_id NUMBER;
2109 l_reason_id NUMBER;
2110
2111
2112 CURSOR mmt_cur (set_id NUMBER) IS
2113 SELECT transaction_id
2114 FROM mtl_material_transactions
2115 WHERE transaction_set_id = set_id;
2116
2117 CURSOR mti_cur (header_id NUMBER) IS
2118 SELECT transaction_interface_id
2119 FROM mtl_transactions_interface
2120 WHERE transaction_header_id = header_id;
2121
2122 CURSOR reason_cur IS
2123 SELECT reason_id
2124 FROM mtl_transaction_reasons_val_v
2125 WHERE reason_name LIKE p_reason_code;
2126
2127 BEGIN
2128
2129 -- Get the value entered in confirm_action Collection element.
2130
2131 -- Bug 3684073. We should not derive the lookup_code value from
2132 -- mfg_lookups because the value passed to this api would be the
2133 -- qa_plan_char_value_lookups.short_code, which is not a translated
2134 -- column. The mfg_lookups view would have the lookup meaning in the
2135 -- language used in the current session.
2136 --
2137 -- Commented the below piece of code and compared p_launch_action
2138 -- and p_action_fired parameters below with the new constants to resolve
2139 -- the value entered. kabalakr.
2140
2141 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
2142 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
2143
2144
2145 -- The Action Code should get executed only if
2146 -- launch_action is 'Yes' and action_fired is 'No'
2147
2148 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
2149 NULL;
2150 ELSE
2151 -- dont fire the action.
2152 RETURN;
2153 END IF;
2154
2155 -- Get the plan_id and org_id now.
2156
2157 l_organization_id := get_organization_id (p_organization_code);
2158 l_plan_id := get_plan_id(p_plan_name);
2159
2160 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
2161
2162 -- We may need to populate appropriate error message here before return.
2163 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2164 END IF;
2165
2166 IF p_transaction_date IS NULL THEN
2167 l_transaction_date := sysdate;
2168 ELSE
2169 l_transaction_date := qltdate.any_to_date(p_transaction_date);
2170 END IF;
2171
2172 -- Update the Disposition Status to 'Pending'.
2173 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
2174
2175 -- Get the Inventory Item ID and Locator ID and Reason ID.
2176 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
2177 l_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_locator);
2178
2179 OPEN reason_cur;
2180 FETCH reason_cur INTO l_reason_id;
2181 CLOSE reason_cur;
2182
2183
2184 l_header_id := WIP_MATERIAL_TXN_INT(
2185 p_job_name,
2186 l_item_id,
2187 p_revision,
2188 p_subinventory,
2189 l_locator_id,
2190 p_lot_number,
2191 p_serial_number,
2192 p_transaction_uom,
2193 -p_transaction_qty,
2194 l_transaction_date,
2195 p_op_seq_num,
2196 l_reason_id,
2197 'QA ACTION:WIP COMP ISSUE',
2198 35,
2199 1,
2200 p_collection_id,
2201 p_occurrence,
2202 l_organization_id);
2203
2204
2205 l_outcome := MTL_ONLINE_TRANSACTION_PUB.PROCESS_ONLINE(l_header_id,
2206 l_time_out,
2207 l_error_code,
2208 l_error_explanation);
2209
2210
2211 IF (l_outcome <> TRUE) THEN
2212 l_result := g_failed;
2213
2214 OPEN mti_cur(l_header_id);
2215 FETCH mti_cur INTO l_int_txn_id;
2216 CLOSE mti_cur;
2217
2218 l_txn_id := 0;
2219
2220
2221 ELSE
2222 l_result := g_success;
2223
2224 OPEN mmt_cur(l_header_id);
2225 FETCH mmt_cur INTO l_txn_id;
2226 CLOSE mmt_cur;
2227
2228 l_int_txn_id := 0;
2229
2230
2231 END IF;
2232
2233 -- call WRITE_BACK procedure.
2234
2235
2236 WRITE_BACK(p_plan_id => l_plan_id,
2237 p_collection_id => p_collection_id,
2238 p_occurrence => p_occurrence,
2239 p_status => l_result,
2240 p_mti_transaction_header_id => l_header_id,
2241 p_mti_transaction_interface_id => l_int_txn_id,
2242 p_mmt_transaction_id => l_txn_id);
2243
2244
2245 END WIP_COMP_ISSUE;
2246
2247
2248
2249 FUNCTION WIP_MATERIAL_TXN_INT(
2250 p_job_name VARCHAR2,
2251 p_item_id NUMBER,
2252 p_revision VARCHAR2,
2253 p_subinventory VARCHAR2,
2254 p_locator_id NUMBER,
2255 p_lot_number VARCHAR2,
2256 p_serial_number VARCHAR2,
2257 p_transaction_uom VARCHAR2,
2258 p_transaction_qty NUMBER,
2259 p_transaction_date DATE,
2260 p_op_seq_num NUMBER,
2261 p_reason_id NUMBER,
2262 p_source_code VARCHAR2,
2263 p_txn_type_id NUMBER,
2264 p_txn_action_id NUMBER,
2265 p_collection_id NUMBER,
2266 p_occurrence NUMBER,
2267 p_organization_id NUMBER)
2268 RETURN NUMBER IS
2269
2270 PRAGMA AUTONOMOUS_TRANSACTION;
2271
2272 l_header_id NUMBER;
2273 l_entity_id NUMBER;
2274 l_entity_type VARCHAR2(30);
2275 l_interface_id NUMBER;
2276 l_lot_control_code NUMBER;
2277 l_serial_control_code NUMBER;
2278 l_update_by NUMBER := fnd_global.user_id;
2279
2280 CURSOR wip_cur IS
2281 SELECT wip_entity_id, entity_type
2282 FROM wip_entities
2283 WHERE wip_entity_name = p_job_name
2284 AND organization_id = p_organization_id;
2285
2286 CURSOR item_cur IS
2287 SELECT lot_control_code,serial_number_control_code
2288 FROM mtl_system_items_b
2289 WHERE inventory_item_id = p_item_id
2290 AND organization_id = p_organization_id;
2291
2292
2293 BEGIN
2294
2295 OPEN wip_cur;
2296 FETCH wip_cur INTO l_entity_id,l_entity_type;
2297 CLOSE wip_cur ;
2298
2299
2300 OPEN item_cur;
2301 FETCH item_cur INTO l_lot_control_code,l_serial_control_code;
2302 CLOSE item_cur;
2303
2304 INSERT INTO MTL_TRANSACTIONS_INTERFACE (
2305 TRANSACTION_HEADER_ID,
2306 TRANSACTION_INTERFACE_ID,
2307 SOURCE_CODE,
2308 SOURCE_HEADER_ID,
2309 SOURCE_LINE_ID,
2310 PROCESS_FLAG,
2311 TRANSACTION_MODE,
2312 LOCK_FLAG,
2313 LAST_UPDATE_DATE,
2314 LAST_UPDATED_BY,
2315 CREATION_DATE,
2316 CREATED_BY,
2317 INVENTORY_ITEM_ID,
2318 REVISION,
2319 ORGANIZATION_ID,
2320 TRANSACTION_QUANTITY,
2321 TRANSACTION_UOM,
2322 TRANSACTION_DATE,
2323 SUBINVENTORY_CODE,
2324 LOCATOR_ID,
2325 TRANSACTION_TYPE_ID,
2326 TRANSACTION_ACTION_ID,
2327 TRANSACTION_SOURCE_TYPE_ID,
2328 TRANSACTION_SOURCE_ID,
2329 WIP_ENTITY_TYPE,
2330 OPERATION_SEQ_NUM,
2331 REASON_ID)
2332 VALUES (
2333 mtl_material_transactions_s.nextval,
2334 mtl_material_transactions_s.nextval,
2335 p_source_code,
2336 p_collection_id,
2337 p_occurrence,
2338 1,
2339 3,
2340 2,
2341 SYSDATE,
2342 l_update_by,
2343 SYSDATE,
2344 l_update_by,
2345 p_item_id,
2346 p_revision,
2347 p_organization_id,
2348 p_transaction_qty,
2349 p_transaction_uom,
2350 p_transaction_date,
2351 p_subinventory,
2352 p_locator_id,
2353 p_txn_type_id,
2354 p_txn_action_id,
2355 5,
2356 l_entity_id,
2357 l_entity_type,
2358 p_op_seq_num,
2359 p_reason_id)
2360
2361 RETURNING TRANSACTION_HEADER_ID INTO l_header_id;
2362
2363
2364 IF l_serial_control_code IN (2,5) OR l_lot_control_code = 2 THEN
2365
2366 -- The item is serial control set to predefined/at receipt.
2367 -- (l_serial_control_code IN (2,5)).
2368 -- The item is lot controlled.(l_lot_control_code = 2).
2369
2370 -- One of Lot or Serial controls are existing for the item
2371 -- Need to insert in appropriate interface table against
2372 -- the transaction_header_id
2373
2374 l_interface_id := l_header_id;
2375 END IF;
2376
2377 IF l_lot_control_code = 2 THEN
2378
2379 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
2380 TRANSACTION_INTERFACE_ID,
2381 SERIAL_TRANSACTION_TEMP_ID,
2382 LOT_NUMBER,
2383 TRANSACTION_QUANTITY,
2384 LAST_UPDATE_DATE,
2385 LAST_UPDATED_BY,
2386 CREATION_DATE,
2387 CREATED_BY)
2388 VALUES (
2389 l_interface_id,
2390 l_interface_id,
2391 p_lot_number,
2392 p_transaction_qty,
2393 SYSDATE,
2394 l_update_by,
2395 SYSDATE,
2396 l_update_by);
2397
2398 END IF;
2399
2400
2401
2402 IF l_serial_control_code IN (2,5) THEN
2403
2404
2405 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
2406 TRANSACTION_INTERFACE_ID,
2407 FM_SERIAL_NUMBER,
2408 LAST_UPDATE_DATE,
2409 LAST_UPDATED_BY,
2410 CREATION_DATE,
2411 CREATED_BY)
2412 VALUES (
2413 l_interface_id,
2414 p_serial_number,
2415 SYSDATE,
2416 l_update_by,
2417 SYSDATE,
2418 l_update_by);
2419
2420 END IF;
2421
2422 -- Commit the insert.
2423 COMMIT;
2424
2425 RETURN l_header_id;
2426
2427 END WIP_MATERIAL_TXN_INT;
2428
2429 -------------------------------------------------------------------------------
2430 -- WIP Move Transactions API
2431 -------------------------------------------------------------------------------
2432 -- Start of Comments
2433 -- API name WIP_MOVE_TXN
2434 -- Type Public
2435 -- Function
2436 --
2437 -- Pre-reqs
2438 --
2439 -- Parameters
2440 -- p_item => Item Name
2441 -- p_job_name => Job Name to do the WIP Scrap Transaction
2442 -- p_from_op_seq => From Operation Sequence Number
2443 -- p_from_intra_step => From Intraoperation Step
2444 -- p_to_op_seq => To Operation Sequence Number
2445 -- p_to_intra_step => To Intraoperation Step
2446 -- p_fm_op_code => Operation Code
2447 -- p_to_op_code => To Operation Code
2448 -- p_reason_name => Reason Code
2449 -- p_uom => Transacting UOM
2450 -- p_quantity => Quantity
2451 -- p_txn_date => WIP Move requires date in varchar2. This should be either Canonical
2452 -- format ('YYYY/MM/DD') or in Real format ('DD-MON-YYYY')
2453 -- p_collection_id => Collection ID
2454 -- p_occurrence => Occurrence
2455 -- p_plan_name => Collection Plan Name
2456 -- p_organization_code => Organization Code, from which transaction happens
2457 -- p_launch_action => This takes two values(Yes/No)
2458 -- Pass a value of 'Yes' to successfully do WIP Scrap Transactions
2459 -- p_action_fired => This takes two values(Yes/No)
2460 -- Pass a value of 'No' to successfully do WIP Scrap Transactions
2461 --
2462 -- Version Current version = 1.0
2463 -- Initial version = 1.0
2464 --
2465 -- Notes
2466 --
2467 -- We are performing all the below activities here
2468 --
2469 -- 1. Get the different id values.
2470 -- 2. Call WIP_SCRAP_WIP_MOVE_INT() procedure for inserting into interface table
2471 -- and spawn the WIP Move Transaction manager.
2472 -- 3. Wait for the Move Manager and Worker Program to get completed.
2473 -- 4. Get the results and perform the handshaking. Call the procedure
2474 -- WRITE_BACK() for performing the same.
2475 --
2476 -- End of Comments
2477 --
2478 --
2479 -- Bug Fixes
2480 --
2481 -- Bug 2689276 : Call WRITE_BACK() to handshake the concurrent request id as
2482 -- soon as the concurrnt request gets launched.
2483 --
2484 --
2485
2486
2487 PROCEDURE WIP_MOVE_TXN(
2488 p_item IN VARCHAR2,
2489 p_job_name IN VARCHAR2,
2490 p_from_op_seq IN NUMBER,
2491 p_from_intra_step IN VARCHAR2,
2492 p_to_op_seq IN NUMBER,
2493 p_to_intra_step IN VARCHAR2,
2494 p_fm_op_code IN VARCHAR2,
2495 p_to_op_code IN VARCHAR2,
2496 p_reason_name IN VARCHAR2,
2497 p_uom IN VARCHAR2,
2498 p_quantity IN NUMBER,
2499 p_txn_date IN VARCHAR2,
2500 p_collection_id IN NUMBER,
2501 p_occurrence IN NUMBER,
2502 p_organization_code IN VARCHAR2,
2503 p_plan_name IN VARCHAR2,
2504 p_launch_action IN VARCHAR2,
2505 p_action_fired IN VARCHAR2) IS
2506
2507
2508 l_request NUMBER;
2509 l_child_request NUMBER;
2510 l_plan_id NUMBER;
2511 l_organization_id NUMBER;
2512 l_to_step NUMBER;
2513 l_from_step NUMBER;
2514 l_src_code VARCHAR2(30);
2515
2516 -- Bug 3684073. These variables are no longer required.
2517 -- l_launch_action NUMBER;
2518 -- l_action_fired NUMBER;
2519
2520 l_reason_id NUMBER;
2521 l_group_id NUMBER;
2522 l_transaction_id NUMBER;
2523 l_txn_id NUMBER;
2524 l_item_id NUMBER;
2525
2526 l_wait BOOLEAN;
2527 l_phase VARCHAR2(2000);
2528 l_status VARCHAR2(2000);
2529 l_devphase VARCHAR2(2000);
2530 l_devstatus VARCHAR2(2000);
2531 l_message VARCHAR2(2000);
2532
2533 l_result VARCHAR2(1800);
2534 l_err_msg VARCHAR2(2000) := NULL;
2535 l_err_col VARCHAR2(1000);
2536 l_arg VARCHAR2(240);
2537
2538 CURSOR txn_cur IS
2539 SELECT wip_transactions_s.nextval
2540 FROM DUAL;
2541
2542 CURSOR req_cur IS
2543 SELECT request_id
2544 FROM FND_CONC_REQ_SUMMARY_V
2545 WHERE parent_request_id = l_request;
2546
2547 CURSOR grp_cur IS
2548 SELECT group_id
2549 FROM WIP_MOVE_TXN_INTERFACE
2550 WHERE source_code = l_src_code;
2551
2552
2553 CURSOR txns_cur IS
2554 SELECT transaction_id
2555 FROM wip_move_transactions
2556 WHERE source_code = l_src_code;
2557
2558 CURSOR reason_cur IS
2559 SELECT reason_id
2560 FROM mtl_transaction_reasons_val_v
2561 WHERE reason_name LIKE p_reason_name;
2562
2563 CURSOR arg_cur IS
2564 SELECT argument_text
2565 FROM FND_CONC_REQ_SUMMARY_V
2566 WHERE request_id = l_child_request;
2567
2568 BEGIN
2569
2570 -- Get the value entered in confirm_action Collection element.
2571
2572 -- Bug 3684073. We should not derive the lookup_code value from
2573 -- mfg_lookups because the value passed to this api would be the
2574 -- qa_plan_char_value_lookups.short_code, which is not a translated
2575 -- column. The mfg_lookups view would have the lookup meaning in the
2576 -- language used in the current session.
2577 --
2578 -- Commented the below piece of code and compared p_launch_action
2579 -- and p_action_fired parameters below with the new constants to resolve
2580 -- the value entered. kabalakr.
2581
2582 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
2583 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
2584
2585 -- The Action Code should get executed only if
2586 -- Launch_action is 'Yes' and relaunch_flag is 'No'
2587
2588 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
2589 NULL;
2590 ELSE
2591 -- dont fire the action
2592 RETURN;
2593 END IF;
2594
2595 l_organization_id := get_organization_id(p_organization_code);
2596 l_plan_id := get_plan_id(p_plan_name);
2597
2598 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
2599
2600 -- We may need to populate appropriate error message here before return.
2601 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603
2604 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
2605
2606 -- Get transaction_id and lookup code for the Intraoperation step.
2607
2608 l_to_step := get_mfg_lookups_value(p_to_intra_step,'WIP_INTRAOPERATION_STEP');
2609 l_from_step := get_mfg_lookups_value(p_from_intra_step,'WIP_INTRAOPERATION_STEP');
2610
2611 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
2612
2613 OPEN txn_cur;
2614 FETCH txn_cur INTO l_txn_id;
2615 CLOSE txn_cur;
2616
2617 OPEN reason_cur;
2618 FETCH reason_cur INTO l_reason_id;
2619 CLOSE reason_cur;
2620
2621 --WIP move is similar to scrap, so call the same WIP_SCRAP_WIP_MOVE_INT
2622 -- Call the wipscrap_int(). It spawns and returns back the concurrent request
2623 -- id of the WIP Move Manager Program.
2624
2625 l_request := WIP_SCRAP_WIP_MOVE_INT(
2626 l_item_id,
2627 l_txn_id,
2628 p_job_name,
2629 null, --no scrap account
2630 p_from_op_seq,
2631 l_from_step,
2632 p_to_op_seq,
2633 l_to_step,
2634 nvl(p_fm_op_code,NULL),
2635 nvl(p_to_op_code,NULL),
2636 nvl(l_reason_id,0),
2637 p_uom,
2638 p_quantity,
2639 qltdate.any_to_date(p_txn_date),
2640 p_organization_code,
2641 p_collection_id);
2642
2643 IF (l_request = 0) THEN
2644
2645 -- Concurrent Request not launched
2646 l_result := g_failed;
2647 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
2648
2649 WRITE_BACK(p_plan_id => l_plan_id,
2650 p_collection_id => p_collection_id,
2651 p_occurrence => p_occurrence,
2652 p_status => l_result,
2653 p_message => l_err_msg);
2654 RETURN;
2655
2656 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
2657 -- write back the concurrent request id.
2658
2659 ELSE
2660
2661 WRITE_BACK( p_plan_id => l_plan_id,
2662 p_collection_id => p_collection_id,
2663 p_occurrence => p_occurrence,
2664 p_status => g_pending,
2665 p_request_id => l_request);
2666
2667 END IF;
2668
2669 -- If request gets launched, proceed.
2670 -- But first, wait for the WIP Move Manager Program to be completed.
2671 -- After that, wait for the WIP Move Worker to be completed.
2672 -- We wait 100 minutes each for these requests to be Completed. And we
2673 -- check in every 15 Minutes
2674
2675 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
2676 g_check_request_time,
2677 g_total_request_time,
2678 l_phase,
2679 l_status,
2680 l_devphase,
2681 l_devstatus,
2682 l_message);
2683
2684 l_src_code := 'QA WIP SCRAP:'||to_char(l_txn_id);
2685 --This QA WIP SCRAP is just a varchar that we use
2686 --Same Varchar is used for WIP move and WIP Scrap
2687 --This will not cause any problem
2688
2689 OPEN grp_cur;
2690 FETCH grp_cur INTO l_group_id;
2691 CLOSE grp_cur;
2692
2693 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
2694 -- If the Manager gets completed, we need to find the correct
2695 -- worker that processes the record we have inserted.
2696
2697 FOR i IN req_cur LOOP
2698 l_child_request := i.request_id;
2699
2700 -- Get the Argument text of the concurrent request so that we can
2701 -- extract the group_id.
2702
2703 OPEN arg_cur;
2704 FETCH arg_cur INTO l_arg;
2705 IF (to_char(l_group_id) = substr(l_arg, 1, instr(l_arg, ',') - 1)) THEN
2706 CLOSE arg_cur;
2707 EXIT;
2708 END IF;
2709 CLOSE arg_cur;
2710
2711 END LOOP;
2712
2713 ELSE
2714 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2715 END IF;
2716
2717 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_child_request,
2718 g_check_request_time,
2719 g_total_request_time,
2720 l_phase,
2721 l_status,
2722 l_devphase,
2723 l_devstatus,
2724 l_message);
2725
2726 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
2727 IF (substr(l_devstatus,1,7) = 'WARNING') THEN
2728
2729 l_result := g_warning;
2730 l_transaction_id := 0;
2731
2732 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
2733
2734 l_result := g_success;
2735
2736 OPEN txns_cur;
2737 FETCH txns_cur INTO l_transaction_id;
2738 CLOSE txns_cur;
2739
2740 l_group_id := 0;
2741
2742 ELSE
2743 -- If error, or any other cases, give FAILURE.
2744 l_result := g_failed;
2745 l_transaction_id := 0;
2746
2747 END IF;
2748
2749 -- Call for handshaking the outcome onto the Collection Plan.
2750 WRITE_BACK(p_plan_id => l_plan_id,
2751 p_collection_id => p_collection_id,
2752 p_occurrence => p_occurrence,
2753 p_status => l_result,
2754 p_wmti_group_id => l_group_id,
2755 p_wmt_transaction_id => l_transaction_id,
2756 p_request_id => l_child_request);
2757
2758 END IF; -- if complete.
2759
2760 END WIP_MOVE_TXN;
2761
2762
2763 -------------------------------------------------------------------------------
2764 -- Add New Rework Operation API
2765 -------------------------------------------------------------------------------
2766 -- Start of Comments
2767 -- API name REWORK_ADD_OPERATION
2768 -- Type Public
2769 -- Function
2770 --
2771 -- Pre-reqs
2772 --
2773 -- Parameters
2774 -- p_job_name => Name of the Rework Job to be created
2775 -- p_op_seq_num => Rework Operation Sequence Number
2776 -- p_operation_code => Rework Operation Code
2777 -- p_department_code => Department Code for NON Standard Operation.
2778 -- p_res_seq_num => Resource Sequence Number to add resources to NON
2779 -- Standard Operations.
2780 -- p_resource_code => Resource Code to add resources to NON Standard Operations.
2781 -- p_assigned_units => Assigned units for the resource.
2782 -- p_usage_rate => Usage rate of resources.
2783 -- p_start_date => First Unit Start date and First unit completion date.
2784 -- p_end_date => Last Unit Start date and Last unit completion date.
2785 -- p_collection_id => Collection ID
2786 -- p_occurrence => Occurrence
2787 -- p_plan_name => Collection Plan Name
2788 -- p_organization_code => Organization Code, from which transaction happens
2789 -- p_launch_action => This takes two values(Yes/No)
2790 -- Pass a value of 'Yes' to successfully create Rework Job
2791 -- p_action_fired => This takes two values(Yes/No)
2792 -- Pass a value of 'No' to successfully create Rework Job
2793 --
2794 -- Version Current version = 1.0
2795 -- Initial version = 1.0
2796 --
2797 -- Notes
2798 --
2799 -- We are performing all the below activities here
2800 --
2801 -- 1. Get the different id values.
2802 -- 2. Call REWORK_OP_ADD_OP_INT() procedure for inserting into interface table
2803 -- and spawn the WIP Mass Load Program (WICMLX) for adding Standard and NON
2804 -- Standard operations.
2805 -- 3. Wait for the Mass Load Program to get completed.
2806 -- 4. Get the results and perform the handshaking. Call the procedure
2807 -- WRITE_BACK() for performing the same.
2808 -- 5. Call REWORK_OP_ADD_RES_INT() procedure for inserting into interface table
2809 -- and spawn the WIP Mass Load Program (WICMLX) for automatic adding of resources
2810 -- for Standard Operations and adding specified resources for specified NON
2811 -- Standard operations.
2812 -- 6. Wait for the Mass Load Program to get completed.
2813 -- 7. Get the results and perform the handshaking. Call the procedure
2814 -- WRITE_BACK() for performing the same.
2815 --
2816 -- End of Comments
2817 --
2818 -- Bug Fixes
2819 --
2820 -- Bug 2689276 : Call WRITE_BACK() to handshake the concurrent request id as
2821 -- soon as the concurrnt request gets launched.
2822 --
2823 -- Bug 2656461 : Code added to support copying of attachments to the
2824 -- WIP_DISCRETE_OPERATIONS entity, once the Action is successful.
2825 --
2826 -- Bug 2714880 : Derive the STATUS_TYPE of the Job from WIP_DISCRETE_JOBS. This
2827 -- value is needed when importing Operations and resources in the
2828 -- internal functions REWORK_OP_ADD_OP_INT() and
2829 -- REWORK_OP_ADD_RES_INT().
2830 --
2831 --
2832
2833 PROCEDURE REWORK_ADD_OPERATION(
2834 p_job_name IN VARCHAR2,
2835 p_op_seq_num IN NUMBER,
2836 p_operation_code IN VARCHAR2,
2837 p_department_code IN VARCHAR2,
2838 p_res_seq_num IN NUMBER,
2839 p_resource_code IN VARCHAR2,
2840 p_assigned_units IN NUMBER,
2841 p_usage_rate IN NUMBER,
2842 p_start_date IN VARCHAR2,
2843 p_end_date IN VARCHAR2,
2844 p_collection_id IN NUMBER,
2845 p_occurrence IN NUMBER,
2846 p_organization_code IN VARCHAR2,
2847 p_plan_name IN VARCHAR2,
2848 p_launch_action IN VARCHAR2,
2849 p_action_fired IN VARCHAR2) IS
2850
2851
2852 l_request NUMBER;
2853 l_group_id NUMBER;
2854 l_plan_id NUMBER;
2855 l_organization_id NUMBER;
2856
2857 -- Bug 3684073. These variables are no longer required.
2858 -- l_launch_action NUMBER;
2859 -- l_action_fired NUMBER;
2860
2861 l_err_msg VARCHAR2(2000) := NULL;
2862
2863 l_wait BOOLEAN;
2864 l_phase VARCHAR2(2000);
2865 l_status VARCHAR2(2000);
2866 l_devphase VARCHAR2(2000);
2867 l_devstatus VARCHAR2(2000);
2868 l_message VARCHAR2(2000);
2869 l_result VARCHAR2(1800);
2870
2871 l_dup_op_seq NUMBER;
2872 l_dup_res_seq NUMBER;
2873
2874 l_wip_entity_id NUMBER;
2875 l_operation_id NUMBER;
2876 l_department_id NUMBER;
2877 l_resource_id NUMBER;
2878 l_op_type NUMBER := 2;
2879
2880 -- Bug 2714880.
2881 l_status_type NUMBER;
2882
2883
2884 CURSOR group_cur IS
2885 SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
2886 FROM DUAL;
2887
2888 CURSOR job_cur IS
2889 SELECT wip_entity_id
2890 FROM wip_entities
2891 WHERE wip_entity_name = p_job_name
2892 AND organization_id = l_organization_id;
2893
2894 CURSOR op_cur IS
2895 SELECT bsoav.standard_operation_id
2896 FROM bom_standard_operations_all_v bsoav, bom_departments_val_v bdvv
2897 WHERE bsoav.organization_id = l_organization_id
2898 AND bsoav.department_id = bdvv.department_id
2899 AND NVL (bsoav.operation_type, 1) = 1
2900 AND bsoav.operation_code = p_operation_code;
2901
2902 CURSOR op_seq_cur IS
2903 SELECT operation_seq_num
2904 FROM wip_operations
2905 WHERE wip_entity_id = l_wip_entity_id
2906 AND operation_seq_num = p_op_seq_num;
2907
2908 CURSOR dept_cur IS
2909 SELECT department_id
2910 FROM bom_departments
2911 WHERE organization_id = l_organization_id
2912 AND nvl(disable_date, sysdate + 2) > sysdate
2913 AND department_code = p_department_code;
2914
2915 CURSOR res_cur IS
2916 SELECT resource_id
2917 FROM bom_resources_val_v
2918 WHERE organization_id = l_organization_id
2919 AND resource_code = p_resource_code;
2920
2921 CURSOR res_seq_cur IS
2922 SELECT resource_seq_num
2923 FROM wip_operation_resources
2924 WHERE wip_entity_id = l_wip_entity_id
2925 AND operation_seq_num = p_op_seq_num
2926 AND resource_seq_num = p_res_seq_num;
2927
2928 -- Bug 2714880. Added the cursor below to fetch the status of the job.
2929
2930 CURSOR job_status IS
2931 SELECT status_type
2932 FROM wip_discrete_jobs
2933 WHERE wip_entity_id = l_wip_entity_id;
2934
2935
2936 BEGIN
2937
2938 -- Get the value entered in confirm_action Collection element.
2939
2940 -- Bug 3684073. We should not derive the lookup_code value from
2941 -- mfg_lookups because the value passed to this api would be the
2942 -- qa_plan_char_value_lookups.short_code, which is not a translated
2943 -- column. The mfg_lookups view would have the lookup meaning in the
2944 -- language used in the current session.
2945 --
2946 -- Commented the below piece of code and compared p_launch_action
2947 -- and p_action_fired parameters below with the new constants to resolve
2948 -- the value entered. kabalakr.
2949
2950 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
2951 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
2952
2953 -- The Action Code should get executed only if
2954 -- Launch_action is 'Yes' and Action_fired is 'No'
2955
2956 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
2957 NULL;
2958
2959 ELSE
2960 -- dont fire the action.
2961 RETURN;
2962 END IF;
2963
2964 -- Get the plan_id, group_id and org_id now. We need these values
2965 -- for Handshaking in this procedure.
2966
2967 l_organization_id := get_organization_id(p_organization_code);
2968 l_plan_id := get_plan_id(p_plan_name);
2969
2970
2971 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
2972
2973 -- We may need to populate appropriate error message here before return.
2974 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2975 END IF;
2976
2977 OPEN group_cur;
2978 FETCH group_cur INTO l_group_id;
2979 CLOSE group_cur;
2980
2981
2982 -- Update the Disposition Status to 'Pending'.
2983 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
2984
2985 -- Derive the wip_entity_id of the Job.
2986 OPEN job_cur;
2987 FETCH job_cur INTO l_wip_entity_id;
2988 CLOSE job_cur;
2989
2990 -- Bug 2714880. Fetch the status_type of the job. We pass this value to the
2991 -- internal functions REWORK_OP_ADD_OP_INT() REWORK_OP_ADD_RES_INT() that
2992 -- adds Operations and Resources respectively.
2993 OPEN job_status;
2994 FETCH job_status INTO l_status_type;
2995 CLOSE job_status;
2996
2997 -- Validate the Rework Operation Sequence Num. If Operation code is specified,
2998 -- its a standard Operation. If Operation Code is not mentioned, user is
2999 -- intending to add NON Standard Operation OR to add just resource to the
3000 -- existing Operation Sequence Number.
3001
3002 OPEN op_seq_cur;
3003 FETCH op_seq_cur INTO l_dup_op_seq;
3004 CLOSE op_seq_cur;
3005
3006
3007 -- If Operation Seq Num is already existing and the Operation code is
3008 -- specified, user is intending to add a standard Operation with Duplicate
3009 -- Operation Seq Num. So error out.
3010
3011 IF (l_dup_op_seq IS NOT NULL) AND (p_operation_code IS NOT NULL) THEN
3012 l_result := g_int_err;
3013 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_DUP_OP_SEQ_NUM');
3014
3015 WRITE_BACK( p_plan_id => l_plan_id,
3016 p_collection_id => p_collection_id,
3017 p_occurrence => p_occurrence,
3018 p_status => l_result,
3019 p_message => l_err_msg);
3020 RETURN;
3021 END IF;
3022
3023 -- Check whehter the Resource Seq Num given as input is not a duplicate one.
3024 -- We cannot accespt duplicate resource seq num for a operation Seq Num.
3025
3026 OPEN res_seq_cur;
3027 FETCH res_seq_cur INTO l_dup_res_seq;
3028 CLOSE res_seq_cur;
3029
3030 IF (l_dup_res_seq IS NOT NULL) AND (p_operation_code IS NULL) THEN
3031 l_result := g_int_err;
3032 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_DUP_RES_SEQ_NUM');
3033
3034 WRITE_BACK( p_plan_id => l_plan_id,
3035 p_collection_id => p_collection_id,
3036 p_occurrence => p_occurrence,
3037 p_status => l_result,
3038 p_message => l_err_msg);
3039 RETURN;
3040
3041 END IF;
3042
3043
3044 -- If Operation Seq Num specified is not a duplicate, we need to add this
3045 -- Operation onto the Job. If Operation Code is specified, then add the specified
3046 -- operation, otherwise just add the Operation Seq Num with Department details.
3047
3048 IF (p_operation_code IS NOT NULL) THEN
3049
3050 -- A value of 1 in l_op_type means, its standard operation.
3051 l_op_type := 1;
3052
3053 OPEN op_cur;
3054 FETCH op_cur INTO l_operation_id;
3055 CLOSE op_cur;
3056
3057 END IF;
3058
3059 IF (p_department_code IS NOT NULL) THEN
3060
3061 OPEN dept_cur;
3062 FETCH dept_cur INTO l_department_id;
3063 CLOSE dept_cur;
3064
3065 END IF;
3066
3067 IF ( l_dup_op_seq IS NULL) THEN
3068
3069 -- Call REWORK_OP_ADD_OP_INT procedure for Adding Operations.
3070 -- It returns the WIP Mass Load Concurrent Request Id launched for
3071 -- processing the Addition of Operation.
3072
3073 l_request := REWORK_OP_ADD_OP_INT(
3074 l_group_id,
3075 p_job_name,
3076 l_wip_entity_id,
3077 p_op_seq_num,
3078 l_operation_id,
3079 l_department_id,
3080 qltdate.any_to_date(p_start_date),
3081 qltdate.any_to_date(p_end_date),
3082 l_organization_id,
3083 l_status_type);
3084
3085
3086 IF (l_request = 0) THEN
3087
3088 -- Concurrent Request not launched
3089 l_result := g_failed;
3090 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
3091
3092 WRITE_BACK( p_plan_id => l_plan_id,
3093 p_collection_id => p_collection_id,
3094 p_occurrence => p_occurrence,
3095 p_status => l_result,
3096 p_message => l_err_msg);
3097
3098 RETURN;
3099
3100 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
3101 -- write back the concurrent request id.
3102
3103 ELSE
3104
3105 WRITE_BACK( p_plan_id => l_plan_id,
3106 p_collection_id => p_collection_id,
3107 p_occurrence => p_occurrence,
3108 p_status => g_pending,
3109 p_request_id => l_request);
3110
3111 END IF;
3112
3113 -- If request gets launched, proceed.
3114 -- But first, wait for the WIP Mass Load Program request to be completed.
3115 -- We wait 100 minutes for the Mass Load to Complete. And we check in
3116 -- every 15 Minutes
3117
3118 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
3119 g_check_request_time,
3120 g_total_request_time,
3121 l_phase,
3122 l_status,
3123 l_devphase,
3124 l_devstatus,
3125 l_message);
3126
3127 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
3128 IF (substr(l_devstatus,1,5) = 'ERROR') THEN
3129
3130 l_result := g_failed;
3131
3132 ELSIF (substr(l_devstatus,1,7) = 'WARNING') THEN
3133
3134 l_result := g_warning;
3135
3136 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
3137
3138 -- Bug 2656461. Commented the 'COMMIT' below. Its not required.
3139
3140 -- Issue a Commit.
3141 -- COMMIT;
3142
3143 l_result := g_success;
3144
3145 -- Bug 2656461. Once the Action is successful, we also need to copy
3146 -- the attachments to the new Operation added.
3147
3148 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
3149 X_from_entity_name => 'QA_RESULTS',
3150 X_from_pk1_value => to_char(p_occurrence),
3151 X_from_pk2_value => to_char(p_collection_id),
3152 X_from_pk3_value => to_char(l_plan_id),
3153 X_to_entity_name => 'WIP_DISCRETE_OPERATIONS',
3154 X_to_pk1_value => to_char(l_wip_entity_id),
3155 X_to_pk2_value => to_char(p_op_seq_num),
3156 X_to_pk3_value => to_char(l_organization_id));
3157
3158
3159 ELSE
3160 l_result := g_failed;
3161
3162 END IF;
3163
3164 -- Call for handshaking the outcome onto the Collection Plan.
3165
3166 WRITE_BACK(p_plan_id => l_plan_id,
3167 p_collection_id => p_collection_id,
3168 p_occurrence => p_occurrence,
3169 p_status => l_result,
3170 p_wjsi_group_id => l_group_id,
3171 p_request_id => l_request
3172 );
3173
3174 -- Return from the API if the WIP Mass Load errors out.
3175 IF (l_result <> g_success) THEN
3176 RETURN;
3177 END IF;
3178
3179 END IF; -- if complete.
3180
3181 END IF; -- l_dup_op_seq IS NULL.
3182
3183 -- Call REWORK_OP_ADD_RES_INT procedure for Adding Resources. It returns the
3184 -- WIP Mass Load Concurrent Request Id launched for processing the Addition
3185 -- of Resource. For Standard Operations. all the resources will be added
3186 -- together. For Non Standard Operation, only one resource will be added at
3187 -- a time.
3188 -- The parameter p_op_type decides internally whether the resources are being
3189 -- added for s Standard Operation (1) or NON standard operation (2).
3190
3191 -- Get the new group_id from the Sequence for Adding Resources.
3192 OPEN group_cur;
3193 FETCH group_cur INTO l_group_id;
3194 CLOSE group_cur;
3195
3196
3197 -- Get the id for Resource.
3198 OPEN res_cur;
3199 FETCH res_cur INTO l_resource_id;
3200 CLOSE res_cur;
3201
3202
3203 l_request := REWORK_OP_ADD_RES_INT(
3204 l_group_id,
3205 p_job_name,
3206 l_wip_entity_id,
3207 p_op_seq_num,
3208 l_operation_id,
3209 l_department_id,
3210 p_res_seq_num,
3211 l_resource_id,
3212 p_assigned_units,
3213 p_usage_rate,
3214 l_organization_id,
3215 l_op_type,
3216 l_status_type
3217 );
3218
3219
3220 IF (l_request = 0) THEN
3221
3222 -- Concurrent Request not launched
3223 l_result := g_failed;
3224 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
3225
3226 WRITE_BACK( p_plan_id => l_plan_id,
3227 p_collection_id => p_collection_id,
3228 p_occurrence => p_occurrence,
3229 p_status => l_result,
3230 p_message => l_err_msg);
3231
3232 RETURN;
3233
3234 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
3235 -- write back the concurrent request id.
3236
3237 ELSE
3238
3239 WRITE_BACK( p_plan_id => l_plan_id,
3240 p_collection_id => p_collection_id,
3241 p_occurrence => p_occurrence,
3242 p_status => g_pending,
3243 p_request_id => l_request);
3244
3245 END IF;
3246
3247 -- If request gets launched, proceed.
3248 -- But first, wait for the WIP Mass Load Program request to be completed.
3249 -- We wait 100 minutes for the Mass Load to Complete. And we check in
3250 -- every 15 Minutes
3251
3252 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
3253 g_check_request_time,
3254 g_total_request_time,
3255 l_phase,
3256 l_status,
3257 l_devphase,
3258 l_devstatus,
3259 l_message);
3260
3261 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
3262 IF (substr(l_devstatus,1,5) = 'ERROR') THEN
3263
3264 l_result := g_failed;
3265
3266 ELSIF (substr(l_devstatus,1,7) = 'WARNING') THEN
3267
3268 l_result := g_warning;
3269
3270 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
3271
3272 l_result := g_success;
3273
3274 ELSE
3275 l_result := g_failed;
3276
3277 END IF;
3278
3279 -- Call for handshaking the outcome onto the Collection Plan.
3280
3281 WRITE_BACK(p_plan_id => l_plan_id,
3282 p_collection_id => p_collection_id,
3283 p_occurrence => p_occurrence,
3284 p_status => l_result,
3285 p_wjsi_group_id => l_group_id,
3286 p_request_id => l_request
3287 );
3288
3289 END IF; -- if complete.
3290
3291 END REWORK_ADD_OPERATION;
3292
3293
3294 -- Procedure to Add Operations.
3295
3296 FUNCTION REWORK_OP_ADD_OP_INT(
3297 p_group_id NUMBER,
3298 p_job_name VARCHAR2,
3299 p_wip_entity_id NUMBER,
3300 p_op_seq_num NUMBER,
3301 p_operation_id NUMBER,
3302 p_department_id NUMBER,
3303 p_start_date DATE,
3304 p_end_date DATE,
3305 p_organization_id NUMBER,
3306 p_status_type NUMBER)
3307
3308 RETURN NUMBER IS
3309
3310 PRAGMA AUTONOMOUS_TRANSACTION;
3311
3312 l_request_id NUMBER;
3313 l_update_by NUMBER := fnd_global.user_id;
3314 l_update_name VARCHAR2(100);
3315
3316 l_header_id NUMBER;
3317
3318 l_department_id NUMBER;
3319 l_count_point_type NUMBER;
3320 l_backflush_flag NUMBER;
3321 l_min_txfr_qty NUMBER;
3322
3323
3324 CURSOR update_cur IS
3325 SELECT user_name
3326 FROM fnd_user_view
3327 WHERE user_id = l_update_by;
3328
3329 CURSOR op_det IS
3330 SELECT bdp.department_id, nvl(bso.count_point_type, 1) count_point_type,
3331 bso.backflush_flag, nvl(bso.minimum_transfer_quantity, 0) minimum_transfer_quantity
3332 FROM bom_departments bdp, bom_standard_operations bso
3333 WHERE bso.organization_id = p_organization_id
3334 AND bso.line_id is null
3335 AND nvl(bso.operation_type,1) = 1
3336 AND bdp.organization_id = p_organization_id
3337 AND bso.department_id = bdp.department_id
3338 AND nvl(bdp.disable_date, sysdate + 2) > sysdate
3339 AND bso.standard_operation_id = p_operation_id;
3340
3341
3342 BEGIN
3343
3344 OPEN update_cur;
3345 FETCH update_cur INTO l_update_name;
3346 CLOSE update_cur;
3347
3348
3349 IF (p_operation_id IS NOT NULL) THEN
3350 OPEN op_det;
3351 FETCH op_det INTO l_department_id, l_count_point_type, l_backflush_flag, l_min_txfr_qty;
3352 CLOSE op_det;
3353
3354 ELSE
3355 -- Get the default values.
3356 l_department_id := p_department_id;
3357 l_min_txfr_qty := 0;
3358 l_count_point_type := 1;
3359 l_backflush_flag := 2;
3360
3361 END IF;
3362
3363
3364 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
3365 (
3366 SOURCE_CODE,
3367 LAST_UPDATE_DATE,
3368 LAST_UPDATED_BY,
3369 LAST_UPDATED_BY_NAME,
3370 CREATION_DATE,
3371 CREATED_BY,
3372 CREATED_BY_NAME,
3373 GROUP_ID,
3374 ORGANIZATION_ID,
3375 LOAD_TYPE,
3376 STATUS_TYPE,
3377 JOB_NAME,
3378 WIP_ENTITY_ID,
3379 INTERFACE_ID,
3380 PROCESS_PHASE,
3381 PROCESS_STATUS,
3382 HEADER_ID
3383 )
3384 VALUES
3385 (
3386 'QA_ACTION: ADD_OP',
3387 sysdate,
3388 l_update_by,
3389 l_update_name,
3390 sysdate,
3391 l_update_by,
3392 l_update_name,
3393 p_group_id,
3394 p_organization_id,
3395 3,
3396 p_status_type,
3397 p_job_name,
3398 p_wip_entity_id,
3399 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3400 2,
3401 1,
3402 WIP_JOB_SCHEDULE_INTERFACE_S.nextval
3403 ) returning header_id into l_header_id;
3404
3405
3406 INSERT INTO WIP_JOB_DTLS_INTERFACE
3407 (
3408 INTERFACE_ID,
3409 GROUP_ID,
3410 WIP_ENTITY_ID,
3411 ORGANIZATION_ID,
3412 OPERATION_SEQ_NUM,
3413 DEPARTMENT_ID,
3414 LOAD_TYPE,
3415 SUBSTITUTION_TYPE,
3416 PROCESS_PHASE,
3417 PROCESS_STATUS,
3418 LAST_UPDATE_DATE,
3419 LAST_UPDATED_BY,
3420 CREATION_DATE,
3421 CREATED_BY,
3422 PARENT_HEADER_ID,
3423 STANDARD_OPERATION_ID,
3424 FIRST_UNIT_START_DATE,
3425 FIRST_UNIT_COMPLETION_DATE,
3426 LAST_UNIT_START_DATE,
3427 LAST_UNIT_COMPLETION_DATE,
3428 MINIMUM_TRANSFER_QUANTITY,
3429 BACKFLUSH_FLAG,
3430 COUNT_POINT_TYPE
3431 )
3432 VALUES
3433 (
3434 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3435 p_group_id,
3436 p_wip_entity_id,
3437 p_organization_id,
3438 p_op_seq_num,
3439 l_department_id,
3440 3, -- ( 3- Load Operation) LOAD_TYPE
3441 2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
3442 2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
3443 1, -- ( 1- wip_constants.pending) PROCESS_STATUS
3444 sysdate,
3445 l_update_by,
3446 sysdate,
3447 l_update_by,
3448 l_header_id,
3449 p_operation_id,
3450 p_start_date,
3451 p_start_date,
3452 p_end_date,
3453 p_end_date,
3454 l_min_txfr_qty,
3455 l_backflush_flag,
3456 l_count_point_type
3457 );
3458
3459
3460
3461 -- Call the WIP Mass Load Program in Background.
3462 l_request_id := FND_REQUEST.SUBMIT_REQUEST('WIP', 'WICMLP',
3463 NULL, NULL, FALSE,
3464 TO_CHAR(p_group_id), -- grp id
3465 TO_CHAR(WIP_CONSTANTS.FULL), -- validation lvl
3466 TO_CHAR(WIP_CONSTANTS.YES)); -- print report
3467
3468 -- Commit the insert
3469 COMMIT;
3470
3471 RETURN l_request_id;
3472
3473 END REWORK_OP_ADD_OP_INT;
3474
3475
3476 -- addres
3477
3478 FUNCTION REWORK_OP_ADD_RES_INT(
3479 p_group_id NUMBER,
3480 p_job_name VARCHAR2,
3481 p_wip_entity_id NUMBER,
3482 p_op_seq_num NUMBER,
3483 p_operation_id NUMBER,
3484 p_department_id NUMBER,
3485 p_res_seq_num NUMBER,
3486 p_resource_id NUMBER,
3487 p_assigned_units NUMBER,
3488 p_usage_rate NUMBER,
3489 p_organization_id NUMBER,
3490 p_op_type NUMBER,
3491 p_status_type NUMBER)
3492
3493 RETURN NUMBER IS
3494
3495 PRAGMA AUTONOMOUS_TRANSACTION;
3496
3497 l_request_id NUMBER;
3498 l_update_by NUMBER := fnd_global.user_id;
3499 l_update_name VARCHAR2(100);
3500
3501 l_header_id NUMBER;
3502
3503 l_res_seq_num NUMBER;
3504 l_resource_id NUMBER;
3505 l_activity_id NUMBER;
3506 l_assigned_units NUMBER;
3507 l_basis_type NUMBER;
3508 l_schedule_flag NUMBER;
3509 l_std_rate_flag NUMBER;
3510 l_usage_rate NUMBER;
3511 l_autocharge_type NUMBER;
3512 l_uom VARCHAR2(3);
3513
3514
3515 CURSOR update_cur IS
3516 SELECT user_name
3517 FROM fnd_user_view
3518 WHERE user_id = l_update_by;
3519
3520
3521 CURSOR res_det IS
3522 SELECT bsor.resource_seq_num, bsor.resource_id, bsor.activity_id,
3523 bsor.assigned_units, bsor.basis_type, bsor.schedule_flag,
3524 bsor.standard_rate_flag, bsor.usage_rate_or_amount,
3525 bsor.autocharge_type, br.unit_of_measure
3526 FROM bom_std_op_resources bsor, bom_resources br
3527 WHERE bsor.resource_id = br.resource_id
3528 AND bsor.standard_operation_id = p_operation_id;
3529
3530
3531 CURSOR m_res_det IS
3532 SELECT res.unit_of_measure, nvl(res.default_basis_type,1) basis_type,
3533 2 "scheduled_flag", res.default_activity_id,
3534 nvl(res.autocharge_type, 1) autocharge_type,
3535 nvl(res.standard_rate_flag,1) standard_rate_flag
3536 FROM bom_resources res, bom_department_resources bdr
3537 WHERE res.organization_id = p_organization_id
3538 AND nvl(res.disable_date, sysdate + 2) > sysdate
3539 AND res.resource_id = bdr.resource_id
3540 AND bdr.department_id = p_department_id
3541 AND res.resource_id = p_resource_id;
3542
3543 BEGIN
3544
3545
3546 OPEN update_cur;
3547 FETCH update_cur INTO l_update_name;
3548 CLOSE update_cur;
3549
3550 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
3551 (
3552 SOURCE_CODE,
3553 LAST_UPDATE_DATE,
3554 LAST_UPDATED_BY,
3555 LAST_UPDATED_BY_NAME,
3556 CREATION_DATE,
3557 CREATED_BY,
3558 CREATED_BY_NAME,
3559 GROUP_ID,
3560 ORGANIZATION_ID,
3561 LOAD_TYPE,
3562 STATUS_TYPE,
3563 JOB_NAME,
3564 WIP_ENTITY_ID,
3565 INTERFACE_ID,
3566 PROCESS_PHASE,
3567 PROCESS_STATUS,
3568 HEADER_ID
3569 )
3570 VALUES
3571 (
3572 'QA_ACTION: ADD_RES',
3573 sysdate,
3574 l_update_by,
3575 l_update_name,
3576 sysdate,
3577 l_update_by,
3578 l_update_name,
3579 p_group_id,
3580 p_organization_id,
3581 3,
3582 p_status_type,
3583 p_job_name,
3584 p_wip_entity_id,
3585 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3586 2,
3587 1,
3588 WIP_JOB_SCHEDULE_INTERFACE_S.nextval
3589 ) returning header_id into l_header_id;
3590
3591
3592 IF (p_op_type = 2) THEN
3593
3594 OPEN m_res_det;
3595 FETCH m_res_det INTO l_uom, l_basis_type, l_schedule_flag, l_activity_id,
3596 l_autocharge_type, l_std_rate_flag;
3597 CLOSE m_res_det;
3598
3599 INSERT INTO WIP_JOB_DTLS_INTERFACE
3600 (
3601 INTERFACE_ID,
3602 GROUP_ID,
3603 ORGANIZATION_ID,
3604 OPERATION_SEQ_NUM,
3605 LOAD_TYPE,
3606 SUBSTITUTION_TYPE,
3607 PROCESS_PHASE,
3608 PROCESS_STATUS,
3609 LAST_UPDATE_DATE,
3610 LAST_UPDATED_BY,
3611 CREATION_DATE,
3612 CREATED_BY,
3613 PARENT_HEADER_ID,
3614 ACTIVITY_ID,
3615 ASSIGNED_UNITS,
3616 AUTOCHARGE_TYPE,
3617 BASIS_TYPE,
3618 RESOURCE_ID_NEW,
3619 RESOURCE_SEQ_NUM,
3620 SCHEDULED_FLAG,
3621 STANDARD_RATE_FLAG,
3622 USAGE_RATE_OR_AMOUNT,
3623 UOM_CODE
3624 )
3625 VALUES
3626 (
3627 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3628 p_group_id,
3629 p_organization_id,
3630 p_op_seq_num,
3631 1, -- ( 1- Load Resource) LOAD_TYPE
3632 2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
3633 2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
3634 1, -- ( 1- wip_constants.pending) PROCESS_STATUS
3635 sysdate,
3636 l_update_by,
3637 sysdate,
3638 l_update_by,
3639 l_header_id,
3640 l_activity_id,
3641 p_assigned_units,
3642 l_autocharge_type,
3643 l_basis_type,
3644 p_resource_id,
3645 p_res_seq_num,
3646 l_schedule_flag,
3647 l_std_rate_flag,
3648 p_usage_rate,
3649 l_uom
3650 );
3651
3652
3653 ELSIF (p_op_type = 1) THEN
3654
3655 OPEN res_det;
3656 LOOP
3657 FETCH res_det INTO l_res_seq_num, l_resource_id, l_activity_id,
3658 l_assigned_units, l_basis_type, l_schedule_flag,
3659 l_std_rate_flag, l_usage_rate, l_autocharge_type, l_uom;
3660
3661 EXIT WHEN res_det%NOTFOUND;
3662
3663
3664 INSERT INTO WIP_JOB_DTLS_INTERFACE
3665 (
3666 INTERFACE_ID,
3667 GROUP_ID,
3668 ORGANIZATION_ID,
3669 OPERATION_SEQ_NUM,
3670 LOAD_TYPE,
3671 SUBSTITUTION_TYPE,
3672 PROCESS_PHASE,
3673 PROCESS_STATUS,
3674 LAST_UPDATE_DATE,
3675 LAST_UPDATED_BY,
3676 CREATION_DATE,
3677 CREATED_BY,
3678 PARENT_HEADER_ID,
3679 ACTIVITY_ID,
3680 ASSIGNED_UNITS,
3681 AUTOCHARGE_TYPE,
3682 BASIS_TYPE,
3683 RESOURCE_ID_NEW,
3684 RESOURCE_SEQ_NUM,
3685 SCHEDULED_FLAG,
3686 STANDARD_RATE_FLAG,
3687 USAGE_RATE_OR_AMOUNT,
3688 UOM_CODE
3689 )
3690 VALUES
3691 (
3692 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3693 p_group_id,
3694 p_organization_id,
3695 p_op_seq_num,
3696 1, -- ( 1- Load Resource) LOAD_TYPE
3697 2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
3698 2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
3699 1, -- ( 1- wip_constants.pending) PROCESS_STATUS
3700 sysdate,
3701 l_update_by,
3702 sysdate,
3703 l_update_by,
3704 l_header_id,
3705 l_activity_id,
3706 l_assigned_units,
3707 l_autocharge_type,
3708 l_basis_type,
3709 l_resource_id,
3710 l_res_seq_num,
3711 l_schedule_flag,
3712 l_std_rate_flag,
3713 l_usage_rate,
3714 l_uom
3715 );
3716
3717 END LOOP;
3718 CLOSE res_det;
3719
3720 END IF;
3721
3722 -- Call the WIP Mass Load Program in Background.
3723 l_request_id := FND_REQUEST.SUBMIT_REQUEST('WIP', 'WICMLP',
3724 NULL, NULL, FALSE,
3725 TO_CHAR(p_group_id), -- grp id
3726 TO_CHAR(WIP_CONSTANTS.FULL), -- validation lvl
3727 TO_CHAR(WIP_CONSTANTS.YES)); -- print report
3728
3729 -- Commit the insert
3730 COMMIT;
3731
3732 RETURN l_request_id;
3733
3734 END REWORK_OP_ADD_RES_INT;
3735
3736
3737
3738
3739 PROCEDURE UPDATE_STATUS(p_plan_id IN NUMBER,
3740 p_collection_id IN NUMBER,
3741 p_occurrence IN NUMBER) IS
3742
3743 PRAGMA AUTONOMOUS_TRANSACTION;
3744
3745 l_txnheader_id NUMBER;
3746
3747 BEGIN
3748
3749 -- Update the Dispostion_Status to 'Pending'.
3750
3751 UPDATE qa_results
3752 SET disposition_status = 'PENDING',
3753 txn_header_id = mtl_material_transactions_s.nextval
3754 WHERE collection_id = p_collection_id AND occurrence = p_occurrence
3755 RETURNING txn_header_id INTO l_txnheader_id;
3756
3757 -- Calling parent-child pkg to insert History child record for the plan.
3758 QA_PARENT_CHILD_PKG.insert_history_auto_rec(p_plan_id,l_txnheader_id, 1, 4);
3759
3760 -- Commit now.
3761 COMMIT;
3762
3763 END UPDATE_STATUS;
3764
3765
3766 PROCEDURE WRITE_BACK(p_plan_id IN NUMBER,
3767 p_collection_id IN NUMBER,
3768 p_occurrence IN NUMBER,
3769 p_status IN VARCHAR2,
3770 p_mti_transaction_header_id IN NUMBER,
3771 p_mti_transaction_interface_id IN NUMBER,
3772 p_mmt_transaction_id IN NUMBER,
3773 p_wmti_group_id IN NUMBER,
3774 p_wmt_transaction_id IN NUMBER,
3775 p_rti_interface_transaction_id IN NUMBER,
3776 p_job_id IN NUMBER,
3777 p_wjsi_group_id IN NUMBER,
3778 p_request_id IN NUMBER,
3779 p_message IN VARCHAR2,
3780 p_move_order_number IN VARCHAR2,
3781 p_eco_name IN VARCHAR2) IS
3782
3783 PRAGMA AUTONOMOUS_TRANSACTION;
3784
3785 l_action_fired_column VARCHAR2(30);
3786 l_disp_message_column VARCHAR2(30);
3787 l_move_order_column VARCHAR2(30);
3788
3789 l_sql_string VARCHAR2(10000);
3790 l_action_fired VARCHAR2(250);
3791
3792 -- Find out correct element char_id and assign to the below constant
3793
3794 l_move_order_char_id CONSTANT NUMBER := qa_ss_const.move_order_number;
3795 l_action_fired_char_id CONSTANT NUMBER := qa_ss_const.action_fired;
3796 l_disp_message_char_id CONSTANT NUMBER := qa_ss_const.disposition_message;
3797
3798 l_txnheader_id NUMBER;
3799
3800 -- Bug 2698365. The below cursor is no longer required.
3801 -- We are returning the sequence value from the update statement.
3802 -- kabalakr.
3803
3804 /*
3805 CURSOR txn_head_seq IS
3806 SELECT mtl_material_transactions_s.nextval
3807 FROM DUAL;
3808 */
3809
3810 -- We are not translating the Action_Fired value.
3811 -- Hence commenting the below cursor.
3812 -- Bug 2595276. kabalakr
3813
3814 /*
3815 CURSOR lookup_cur(l_lookup_code NUMBER) IS
3816 SELECT substr(ltrim(rtrim(meaning)),1,20)
3817 FROM mfg_lookups
3818 WHERE lookup_type = 'SYS_YES_NO'
3819 AND lookup_code = l_lookup_code ;
3820 */
3821
3822 BEGIN
3823
3824
3825 IF p_status = 'INT_ERROR' THEN
3826 -- When internal error occurs, we need to set the action_fired to 'No'
3827 -- so that user can refire the action form UQR
3828
3829 -- OPEN lookup_cur(g_lookup_no);
3830
3831 -- We are not translating the Action_Fired value. Commented the
3832 -- above line of code. Assigning the hardcoded string below.
3833 -- Bug 2595276. kabalakr.
3834
3835 -- anagarwa Fri Jul 2 16:30:00 PDT 2004
3836 -- bug 3736593 action fired element cannot be validated.
3837 -- following function looks for values in qa_plan_char_value_lookups and
3838 -- if not found, it executes the sql validation string
3839 --l_action_fired := 'No' ;
3840 l_action_fired := get_short_code(p_plan_id, l_action_fired_char_id, 'NO');
3841
3842 ELSE
3843 -- all other cases, set action_fired to 'Yes'
3844
3845 -- OPEN lookup_cur(g_lookup_yes);
3846
3847 -- We are not translating the Action_Fired value. Commented the
3848 -- above line of code. Assigning the hardcoded string below.
3849 -- Bug 2595276. kabalakr.
3850
3851 -- anagarwa Fri Jul 2 16:30:00 PDT 2004
3852 -- bug 3736593 action fired element cannot be validated.
3853 -- following function looks for values in qa_plan_char_value_lookups and
3854 -- if not found, it executes the sql validation string
3855 --l_action_fired := 'Yes' ;
3856 l_action_fired := get_short_code(p_plan_id, l_action_fired_char_id, 'YES');
3857
3858
3859 END IF;
3860
3861 -- We are not translating the Action_Fired value. Commenting the
3862 -- code below. Bug 2595276. kabalakr.
3863
3864 -- FETCH lookup_cur INTO l_action_fired;
3865 -- CLOSE lookup_cur;
3866
3867 -- Needs to get the result column names of the Seeded handshaking
3868 -- Collection elements.
3869
3870 l_action_fired_column := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, l_action_fired_char_id);
3871 l_disp_message_column := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, l_disp_message_char_id);
3872
3873 -- Bug 2698365. The cursor txn_head_seq is no longer required. Hence commenting
3874 -- the piece of code below. We are returning the value of txn_header_id from the
3875 -- update statement. kabalakr.
3876 /*
3877 OPEN txn_head_seq;
3878 FETCH txn_head_seq INTO l_txnheader_id;
3879 CLOSE txn_head_seq;
3880 */
3881
3882 -- Added the eco_name column to support 'Create ECO' corrective action.
3883
3884 -- Bug 2689305. Added NVL () for all the parameter variables used below
3885 -- which had a default value of NULL. kabalakr.
3886
3887 -- Bug 2698365. Added txn_header_id in the update statement below and
3888 -- returning its value to the variable l_txnheader_id. kabalakr.
3889
3890 UPDATE qa_results
3891 SET disposition_status = p_status,
3892 wip_rework_id = NVL(p_job_id, 0),
3893 wjsi_group_id = NVL(p_wjsi_group_id, 0),
3894 mti_transaction_header_id = NVL(p_mti_transaction_header_id, 0),
3895 mti_transaction_interface_id = NVL(p_mti_transaction_interface_id, 0),
3896 mmt_transaction_id = NVL(p_mmt_transaction_id, 0),
3897 wmti_group_id = NVL(p_wmti_group_id, 0),
3898 wmt_transaction_id = NVL(p_wmt_transaction_id, 0),
3899 rti_interface_transaction_id = NVL(p_rti_interface_transaction_id, 0),
3900 concurrent_request_id = NVL(p_request_id, 0),
3901 eco_name = p_eco_name,
3902 txn_header_id = mtl_material_transactions_s.nextval
3903 WHERE collection_id = p_collection_id
3904 AND occurrence = p_occurrence
3905 RETURNING txn_header_id INTO l_txnheader_id;
3906
3907
3908 -- Bug 2935558/2941809. Need to use bind variables instead of literal values when
3909 -- using EXECUTE IMMEDIATE. This is for the SQL Bind Compliance Project.
3910 -- kabalakr
3911
3912 IF p_move_order_number IS NOT NULL THEN
3913 l_move_order_column := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, l_move_order_char_id);
3914
3915 l_sql_string := 'UPDATE qa_results SET '||
3916 l_move_order_column ||' = :move_order_number, ' ||
3917 l_action_fired_column||' = :action_fired, '||
3918 l_disp_message_column||' = :message '||
3919 ' WHERE collection_id = :coll_id AND occurrence = :occ';
3920
3921 EXECUTE IMMEDIATE l_sql_string USING p_move_order_number, l_action_fired, p_message, p_collection_id, p_occurrence;
3922
3923 ELSE
3924
3925
3926 l_sql_string := 'UPDATE qa_results SET ' ||
3927 l_action_fired_column||' = :action_fired, '||
3928 l_disp_message_column||' = :message '||
3929 ' WHERE collection_id = :coll_id AND occurrence = :occ';
3930
3931 EXECUTE IMMEDIATE l_sql_string USING l_action_fired, p_message, p_collection_id, p_occurrence;
3932
3933 END IF;
3934
3935
3936 -- Calling parent-child pkg to insert History child record for the plan.
3937 -- action firing for child rec is taken care in the parent-child pkg.
3938 QA_PARENT_CHILD_PKG.insert_history_auto_rec(p_plan_id, l_txnheader_id, 1, 4);
3939
3940 -- We need to fire action for the parent record only.
3941 QA_PARENT_CHILD_PKG.enable_fire_for_txn_hdr_id(l_txnheader_id);
3942
3943 -- Commit Now.
3944 COMMIT;
3945
3946 END WRITE_BACK;
3947
3948 FUNCTION get_mfg_lookups_value (p_meaning VARCHAR2,
3949 p_lookup_type VARCHAR2)
3950 RETURN NUMBER IS
3951
3952 l_lookup_code VARCHAR2(2);
3953
3954 CURSOR meaning_cur IS
3955 SELECT lookup_code
3956 FROM mfg_lookups
3957 WHERE lookup_type = p_lookup_type
3958 AND upper(meaning) = upper(ltrim(rtrim(p_meaning)));
3959
3960 BEGIN
3961
3962 OPEN meaning_cur;
3963 FETCH meaning_cur INTO l_lookup_code;
3964 CLOSE meaning_cur;
3965
3966 RETURN l_lookup_code;
3967
3968 END get_mfg_lookups_value;
3969
3970 FUNCTION get_plan_id(p_plan_name VARCHAR2)
3971 RETURN NUMBER IS
3972
3973 l_plan_id NUMBER := -1;
3974
3975 CURSOR plan_cur IS
3976 SELECT plan_id
3977 FROM QA_PLANS
3978 WHERE name = p_plan_name;
3979
3980 BEGIN
3981 OPEN plan_cur;
3982 FETCH plan_cur INTO l_plan_id;
3983 CLOSE plan_cur;
3984
3985 RETURN l_plan_id;
3986
3987 EXCEPTION
3988 when NO_DATA_FOUND then
3989 RETURN -1;
3990 when OTHERS then
3991 RAISE;
3992
3993 END get_plan_id;
3994
3995
3996 FUNCTION get_organization_id (p_organization_code VARCHAR2)
3997 RETURN NUMBER IS
3998
3999 l_organization_id NUMBER := -1;
4000
4001 -- Bug 4958743. SQL Repository Fix SQL ID: 15008948
4002 CURSOR org_cur IS
4003 SELECT organization_id
4004 FROM mtl_parameters
4005 WHERE organization_code = upper(p_organization_code);
4006 /*
4007 SELECT organization_id
4008 FROM org_organization_definitions
4009 WHERE organization_code = p_organization_code;
4010 */
4011
4012 BEGIN
4013
4014 OPEN org_cur;
4015 FETCH org_cur INTO l_organization_id;
4016 CLOSE org_cur;
4017
4018 RETURN l_organization_id;
4019
4020 EXCEPTION
4021
4022 when NO_DATA_FOUND then
4023 RETURN -1;
4024 when OTHERS then
4025 RAISE;
4026
4027 END get_organization_id;
4028
4029 -- anagarwa Fri Jul 2 16:30:00 PDT 2004
4030 -- bug 3736593 action fired element cannot be validated.
4031 -- following function looks for values in qa_plan_char_value_lookups and
4032 -- if not found, it executes the sql validation string
4033 FUNCTION get_short_code(p_plan_id NUMBER,
4034 p_char_id NUMBER,
4035 p_short_code IN VARCHAR2)
4036 RETURN VARCHAR2 IS
4037
4038 l_return_value VARCHAR2(250);
4039 x_ref LookupCur;
4040 sql_string VARCHAR2(3000);
4041
4042 CURSOR c IS
4043 SELECT short_code
4044 FROM qa_plan_char_value_lookups
4045 WHERE plan_id = p_plan_id
4046 AND char_id = p_char_id
4047 AND upper(short_code) = upper(p_short_code);
4048
4049 BEGIN
4050
4051 IF qa_plan_element_api.values_exist(p_plan_id, p_char_id) THEN
4052
4053 OPEN c;
4054 FETCH c INTO l_return_value;
4055 CLOSE c;
4056
4057 ELSIF qa_plan_element_api.sql_validation_exists(p_char_id) THEN
4058
4059 sql_string := qa_plan_element_api.get_sql_validation_string(p_char_id);
4060
4061 --
4062 -- Bug 1474995. Adding filter to the user-defined SQL.
4063 --
4064 sql_string :=
4065 'select code
4066 from
4067 (select ''x'' code, ''x'' description
4068 from dual
4069 where 1 = 2
4070 union
4071 select * from
4072 ( '|| sql_string ||
4073 ' )) where upper(code) = upper(:1)';
4074
4075 OPEN x_ref FOR sql_string USING p_short_code;
4076 FETCH x_ref INTO l_return_value;
4077 CLOSE x_ref;
4078 ELSE
4079 l_return_value := p_short_code;
4080 END IF;
4081
4082 RETURN l_return_value;
4083 END;
4084
4085 END QA_SOLUTION_DISPOSITION_PKG;
4086
[Home] [Help]