1 PACKAGE BODY QA_SOLUTION_DISPOSITION_PKG as
2 /* $Header: qasodisb.pls 120.1.12010000.3 2010/04/26 17:23:12 ntungare ship $ */
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 -- bug 9652549 CLM changes
1400 --
1401 PROCEDURE PO_RETURN_TO_VENDOR(
1402 p_item IN VARCHAR2,
1403 p_revision IN VARCHAR2,
1404 p_subinventory IN VARCHAR2,
1405 p_locator IN VARCHAR2,
1406 p_lot_number IN VARCHAR2,
1407 p_serial_number IN VARCHAR2,
1408 p_uom_code IN VARCHAR2,
1409 p_quantity IN NUMBER,
1410 p_po_number IN VARCHAR2,
1411 p_po_line_number IN VARCHAR2,
1412 p_po_shipment_number IN NUMBER,
1413 p_po_receipt_number IN NUMBER,
1414 p_transaction_date IN VARCHAR2,
1415 p_collection_id IN NUMBER,
1416 p_occurrence IN NUMBER,
1417 p_plan_name IN VARCHAR2,
1418 p_organization_code IN VARCHAR2,
1419 p_launch_action IN VARCHAR2,
1420 p_action_fired IN VARCHAR2 ) IS
1421
1422
1423 BEGIN
1424
1425 null;
1426
1427 END PO_RETURN_TO_VENDOR;
1428
1429 --
1430 -- bug 9652549 CLM changes
1431 --
1432 FUNCTION PO_RETURN_TO_VENDOR_INT(
1433 p_item_id IN NUMBER,
1434 p_revision IN VARCHAR2,
1435 p_subinventory IN VARCHAR2,
1436 p_locator_id IN NUMBER,
1437 p_lot_number IN VARCHAR2,
1438 p_serial_number IN VARCHAR2,
1439 p_uom_code IN VARCHAR2,
1440 p_quantity IN NUMBER,
1441 p_po_number IN VARCHAR2,
1442 p_po_line_number IN VARCHAR2,
1443 p_po_shipment_number IN NUMBER,
1444 p_po_receipt_number IN NUMBER,
1445 p_transaction_date IN DATE,
1446 p_collection_id IN NUMBER,
1447 p_occurrence IN NUMBER,
1448 p_plan_id IN NUMBER,
1449 p_organization_id IN NUMBER,
1450 p_interface_transaction_id IN NUMBER)
1451 RETURN NUMBER IS
1452
1453 BEGIN
1454
1455 RETURN NULL;
1456
1457 END PO_RETURN_TO_VENDOR_INT;
1458
1459
1460
1461 -------------------------------------------------------------------------------
1462 -- Inventory Create Move Order API
1463 -------------------------------------------------------------------------------
1464 -- Start of Comments
1465 -- API name INV_CREATE_MOVE_ORDER
1466 -- Type Public
1467 -- Function
1468 --
1469 -- Pre-reqs
1470 --
1471 -- Parameters
1472 -- p_item => Item Name
1473 -- p_revision => Item Revision
1474 -- p_from_subinventory => From Subinventory Name
1475 -- p_from_locator => From Locator Name
1476 -- p_lot_number => Lot Number
1477 -- p_serial_number => Serial Number
1478 -- p_uom_code => Transacting UOM code
1479 -- p_quantity => Quantity
1480 -- p_to_subinventory => From Subinventory Name
1481 -- p_to_locator => From Locator Name
1482 -- p_date_required => Moveorder Required date in varchar2. This should be either Canonical
1483 -- format ('YYYY/MM/DD') or in Real format ('DD-MON-YYYY')
1484 --
1485 -- p_project_number => Project Number
1486 -- p_task_number => Task Number
1487 --
1488 -- p_collection_id => Collection ID
1489 -- p_occurrence => Occurrence
1490 -- p_plan_name => Collection Plan Name
1491 -- p_organization_code => Organization Code, from which transaction happens
1492 --
1493 -- p_launch_action => This takes two values(Yes/No)
1494 -- Pass a value of 'Yes' to successfully create move order
1495 -- p_action_fired => This takes two values(Yes/No)
1496 -- Pass a value of 'No' to successfully create move order
1497 --
1498 -- Version Current version = 1.0
1499 -- Initial version = 1.0
1500 --
1501 -- Notes
1502 --
1503 -- Move order created by this actions will be of type 'Move Order Transfer'
1504 -- approval is Preapproved. And the columns
1505 -- mtl_txn_request_header.Request Number = mtl_txn_request_header.Header_id
1506 --
1507 -- End of Comments
1508
1509
1510
1511 PROCEDURE INV_CREATE_MOVE_ORDER (
1512 p_item IN VARCHAR2,
1513 p_revision IN VARCHAR2,
1514 p_from_subinventory IN VARCHAR2,
1515 p_from_locator IN VARCHAR2,
1516 p_lot_number IN VARCHAR2,
1517 p_serial_number IN VARCHAR2,
1518 p_uom_code IN VARCHAR2,
1519 p_quantity IN NUMBER,
1520 p_to_subinventory IN VARCHAR2,
1521 p_to_locator IN VARCHAR2,
1522 p_date_required IN VARCHAR2,
1523 p_project_number IN VARCHAR2,
1524 p_task_number IN VARCHAR2,
1525 p_collection_id IN NUMBER,
1526 p_occurrence IN NUMBER,
1527 p_plan_name IN VARCHAR2,
1528 p_organization_code IN VARCHAR2,
1529 p_launch_action IN VARCHAR2,
1530 p_action_fired IN VARCHAR2 ) IS
1531
1532 -- Bug 3684073. These variables are no longer required.
1533 -- l_launch_action NUMBER;
1534 -- l_action_fired NUMBER;
1535
1536 l_item_id NUMBER;
1537 l_from_locator_id NUMBER;
1538 l_to_locator_id NUMBER;
1539 l_project_id NUMBER;
1540 l_task_id NUMBER;
1541
1542 l_organization_id NUMBER;
1543 l_plan_id NUMBER;
1544 l_status VARCHAR2(2000);
1545 l_request_number VARCHAR2(30); -- holds move order request number
1546
1547 BEGIN
1548
1549 -- Get the value entered in confirm_action Collection element.
1550
1551 -- Bug 3684073. We should not derive the lookup_code value from
1552 -- mfg_lookups because the value passed to this api would be the
1553 -- qa_plan_char_value_lookups.short_code, which is not a translated
1554 -- column. The mfg_lookups view would have the lookup meaning in the
1555 -- language used in the current session.
1556 --
1557 -- Commented the below piece of code and compared p_launch_action
1558 -- and p_action_fired parameters below with the new constants to resolve
1559 -- the value entered. kabalakr.
1560
1561 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
1562 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
1563
1564 -- The Action Code should get executed only if
1565 -- Launch_action is 'Yes' and Action_fired is 'No'
1566
1567 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
1568 NULL;
1569
1570 ELSE
1571 -- dont fire the action.
1572 RETURN;
1573 END IF;
1574
1575 l_organization_id := Get_organization_id (p_organization_code);
1576 l_plan_id := Get_plan_id(p_plan_name);
1577
1578 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
1579
1580 -- We may need to populate appropriate error message here before return.
1581 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1582 END IF;
1583
1584 -- Update the Disposition Status to 'Pending'.
1585 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
1586
1587 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
1588 l_from_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_from_locator);
1589 l_to_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_to_locator);
1590
1591 l_project_id := qa_flex_util.get_project_id(p_project_number);
1592 l_task_id := qa_flex_util.get_task_id(l_project_id,p_task_number);
1593
1594
1595 l_status := INV_CREATE_MOVE_ORDER_INT (
1596 l_item_id ,
1597 p_revision ,
1598 p_from_subinventory ,
1599 l_from_locator_id,
1600 p_lot_number,
1601 p_serial_number,
1602 p_uom_code,
1603 p_quantity,
1604 p_to_subinventory,
1605 l_to_locator_id,
1606 qltdate.any_to_date (p_date_required),
1607 l_project_id,
1608 l_task_id,
1609 l_organization_id,
1610 l_request_number);
1611
1612 -- Call for handshaking the outcome onto the Collection Plan.
1613
1614 WRITE_BACK(p_plan_id => l_plan_id,
1615 p_collection_id => p_collection_id,
1616 p_occurrence => p_occurrence,
1617 p_status => l_status,
1618 p_move_order_number => l_request_number);
1619
1620 END INV_CREATE_MOVE_ORDER;
1621
1622
1623 FUNCTION INV_CREATE_MOVE_ORDER_INT (
1624 p_item_id IN NUMBER,
1625 p_revision IN VARCHAR2,
1626 p_from_subinventory IN VARCHAR2,
1627 p_from_locator_id IN NUMBER,
1628 p_lot_number IN VARCHAR2,
1629 p_serial_number IN VARCHAR2,
1630 p_uom_code IN VARCHAR2,
1631 p_quantity IN NUMBER,
1632 p_to_subinventory IN VARCHAR2,
1633 p_to_locator_id IN NUMBER,
1634 p_date_required IN DATE,
1635 p_project_id IN NUMBER,
1636 p_task_id IN NUMBER,
1637 p_organization_id IN NUMBER,
1638 x_request_number OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1639
1640
1641 l_lot_control_code NUMBER;
1642 l_serial_control_code NUMBER;
1643 l_revision_control_code NUMBER;
1644
1645 l_status VARCHAR2(2000);
1646 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1647 l_msg_count NUMBER;
1648 l_msg_data VARCHAR2(240);
1649 l_message VARCHAR2(2000);
1650
1651 -- Bug 2698365. GSCC warning fix. Commented the usage of
1652 -- FND_API.G_MISS_NUM. kabalakr.
1653 l_header_id NUMBER; -- := FND_API.G_MISS_NUM;
1654
1655 l_line_num NUMBER := 0;
1656 l_order_count NUMBER := 1;
1657 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1658
1659 l_request_number VARCHAR2(30);
1660
1661 l_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
1662 l_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type;
1663
1664 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1665 l_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;
1666
1667 CURSOR item_att_cur IS
1668 SELECT revision_qty_control_code,
1669 lot_control_code,serial_number_control_code
1670 FROM mtl_system_items_b
1671 WHERE inventory_item_id = p_item_id
1672 AND organization_id = p_organization_id;
1673
1674 BEGIN
1675
1676 -- Bug 2698365. GSCC warning fix. Moved the usage of FND_API.G_MISS_NUM
1677 -- to here. kabalakr.
1678 l_header_id := FND_API.G_MISS_NUM;
1679
1680 -- populate Header WHO columns
1681 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
1682 l_trohdr_rec.creation_date := sysdate;
1683 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
1684 l_trohdr_rec.last_update_date := sysdate;
1685 l_trohdr_rec.last_update_login := FND_GLOBAL.USER_ID;
1686
1687 l_trohdr_rec.from_subinventory_code := p_from_subinventory;
1688 l_trohdr_rec.to_subinventory_code := p_to_subinventory;
1689
1690 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1691 l_trohdr_rec.organization_id := p_organization_id;
1692 l_trohdr_rec.status_date := sysdate;
1693 l_trohdr_rec.date_required := p_date_required;
1694 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
1695 l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1696 l_trohdr_rec.db_flag := FND_API.G_TRUE;
1697 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1698
1699 l_line_num := l_line_num + 1;
1700
1701 -- populate Lines WHO columns.
1702
1703 l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
1704 l_trolin_tbl(l_order_count).creation_date := sysdate;
1705 l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
1706 l_trolin_tbl(l_order_count).last_update_date := sysdate;
1707 l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
1708
1709 l_trolin_tbl(l_order_count).date_required := p_date_required;
1710 l_trolin_tbl(l_order_count).status_date := sysdate;
1711
1712 l_trolin_tbl(l_order_count).inventory_item_id := p_item_id;
1713
1714
1715 OPEN item_att_cur;
1716 FETCH item_att_cur INTO
1717 l_revision_control_code,
1718 l_lot_control_code,l_serial_control_code;
1719
1720 IF l_revision_control_code = 2 THEN
1721 -- revision control item
1722 l_trolin_tbl(l_order_count).revision := p_revision;
1723 END IF;
1724
1725 IF l_lot_control_code = 2 THEN
1726 -- lot control item
1727 l_trolin_tbl(l_order_count).lot_number := p_lot_number;
1728 END IF;
1729
1730 IF l_serial_control_code in( 2,5) THEN
1731 -- serial control item
1732 l_trolin_tbl(l_order_count).serial_number_start := p_serial_number;
1733 l_trolin_tbl(l_order_count).serial_number_end := p_serial_number;
1734 END IF;
1735
1736 CLOSE item_att_cur;
1737
1738 l_trolin_tbl(l_order_count).quantity := p_quantity;
1739 l_trolin_tbl(l_order_count).uom_code := p_uom_code;
1740
1741 l_trolin_tbl(l_order_count).from_subinventory_code := p_from_subinventory;
1742 l_trolin_tbl(l_order_count).from_locator_id := p_from_locator_id;
1743 l_trolin_tbl(l_order_count).to_subinventory_code := p_to_subinventory;
1744 l_trolin_tbl(l_order_count).to_locator_id := p_to_locator_id;
1745
1746 l_trolin_tbl(l_order_count).organization_id := p_organization_id;
1747 l_trolin_tbl(l_order_count).project_id := p_project_id;
1748 l_trolin_tbl(l_order_count).task_id := p_task_id;
1749
1750
1751 l_trolin_tbl(l_order_count).header_id := l_trohdr_rec.header_id;
1752 l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
1753
1754 l_trolin_tbl(l_order_count).line_number := l_line_num;
1755 l_trolin_tbl(l_order_count).line_status := INV_GLOBALS.G_TO_STATUS_PREAPPROVED;
1756
1757 l_trolin_Tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1758
1759 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
1760 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
1761
1762
1763 Inv_Move_Order_Pub.Process_Move_order(
1764 p_api_version_number => 1.0,
1765 p_init_msg_list => FND_API.G_FALSE,
1766 p_return_values => FND_API.G_TRUE,
1767 p_commit => FND_API.G_FALSE,
1768 x_return_status => l_return_status,
1769 x_msg_count => l_msg_count,
1770 x_msg_data => l_msg_data,
1771 p_trohdr_rec => l_trohdr_rec,
1772 p_trohdr_val_rec => l_trohdr_val_rec,
1773 p_trolin_tbl => l_trolin_tbl,
1774 p_trolin_val_tbl => l_trolin_val_tbl,
1775 x_trohdr_rec => l_trohdr_rec,
1776 x_trohdr_val_rec => l_trohdr_val_rec,
1777 x_trolin_tbl => l_trolin_tbl,
1778 x_trolin_val_tbl => l_trolin_val_tbl
1779 );
1780
1781
1782 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1783 l_status := g_failed;
1784 x_request_number := NULL;
1785
1786 ELSE
1787 -- on success commit the txn.
1788 COMMIT;
1789
1790 l_status := g_success;
1791 x_request_number := l_trohdr_rec.request_number;
1792 END IF;
1793
1794 RETURN l_status;
1795
1796 END INV_CREATE_MOVE_ORDER_INT;
1797
1798
1799 -------------------------------------------------------------------------------
1800 -- WIP Component Return to Inventory
1801 -------------------------------------------------------------------------------
1802 --
1803 -- Start of Comments
1804 --
1805 -- API name WIP_COMP_RETURN
1806 -- Type Public
1807 -- Function
1808 --
1809 -- Pre-reqs
1810 --
1811 -- Parameters
1812 -- p_item => Item Name
1813 -- p_job_name => Job Name to do the WIP Component Issue Transaction
1814 -- p_revision => Item Revision
1815 -- p_subinventory => Subinventory Name
1816 -- p_locator => Locator Name
1817 -- p_lot_number => Lot Number
1818 -- p_serial_number => Serial Number
1819 -- p_transaction_uom => Transacting UOM
1820 -- p_transaction_qty => Transaction Quantity
1821 -- p_transaction_date => WIP Component Issue requires date in varchar2. This
1822 -- should be either Canonical format ('YYYY/MM/DD') or
1823 -- in Real format ('DD-MON-YYYY')
1824 -- p_op_seq_num => Operation Sequence Number
1825 -- p_reason_code => Reason Code
1826 -- p_collection_id => Collection ID
1827 -- p_occurrence => Occurrence
1828 -- p_plan_name => Collection Plan Name
1829 -- p_organization_code => Organization Code, from which transaction happens
1830 -- p_launch_action => This takes two values(Yes/No)
1831 -- Pass a value of 'Yes' to successfully perform
1832 -- WIP Component Issue Transaction
1833 -- p_action_fired => This takes two values(Yes/No)
1834 -- Pass a value of 'No' to successfully perform
1835 -- WIP Component Issue Transaction
1836 --
1837 -- Notes
1838 --
1839 -- We are performing all the below activities here
1840 --
1841 -- 1. Get the collection element values. Derive the other values.
1842 -- 2. Call WIP_MATERIAL_TXN_INT () function for inserting into interface table.
1843 -- 3. Call mtl_online_transaction_pub.process_online () procedure.
1844 -- 4. Call the WRITE_BACK () procedure for handshaking.
1845 --
1846 --
1847 -- End of Comments
1848 --
1849
1850 PROCEDURE WIP_COMP_RETURN(p_job_name IN VARCHAR2,
1851 p_item IN VARCHAR2,
1852 p_revision IN VARCHAR2,
1853 p_subinventory IN VARCHAR2,
1854 p_locator IN VARCHAR2,
1855 p_lot_number IN VARCHAR2,
1856 p_serial_number IN VARCHAR2,
1857 p_transaction_uom IN VARCHAR2,
1858 p_transaction_qty IN NUMBER,
1859 p_transaction_date IN VARCHAR2,
1860 p_op_seq_num IN NUMBER,
1861 p_reason_code IN VARCHAR2,
1862 p_collection_id IN NUMBER,
1863 p_occurrence IN NUMBER,
1864 p_organization_code IN VARCHAR2,
1865 p_plan_name IN VARCHAR2,
1866 p_launch_action IN VARCHAR2,
1867 p_action_fired IN VARCHAR2) IS
1868
1869
1870 l_plan_id NUMBER;
1871 l_organization_id NUMBER;
1872
1873 -- Bug 3684073. These variables are no longer required.
1874 -- l_launch_action NUMBER;
1875 -- l_action_fired NUMBER;
1876
1877 l_outcome BOOLEAN := TRUE;
1878 l_header_id NUMBER;
1879 l_result VARCHAR2(240);
1880 l_error_code VARCHAR2(240) := NULL;
1881 l_error_explanation VARCHAR2(240) := NULL;
1882 l_time_out NUMBER := 1200;
1883 l_int_txn_id NUMBER;
1884 l_txn_id NUMBER;
1885 l_transaction_date DATE;
1886 l_item_id NUMBER;
1887 l_locator_id NUMBER;
1888 l_reason_id NUMBER;
1889
1890 CURSOR mmt_cur (set_id NUMBER) IS
1891 SELECT transaction_id
1892 FROM mtl_material_transactions
1893 WHERE transaction_set_id = set_id;
1894
1895 CURSOR mti_cur (header_id NUMBER) IS
1896 SELECT transaction_interface_id
1897 FROM mtl_transactions_interface
1898 WHERE transaction_header_id = header_id;
1899
1900 CURSOR reason_cur IS
1901 SELECT reason_id
1902 FROM mtl_transaction_reasons_val_v
1903 WHERE reason_name LIKE p_reason_code;
1904
1905
1906 BEGIN
1907
1908 -- Get the value entered in confirm_action Collection element.
1909
1910 -- Bug 3684073. We should not derive the lookup_code value from
1911 -- mfg_lookups because the value passed to this api would be the
1912 -- qa_plan_char_value_lookups.short_code, which is not a translated
1913 -- column. The mfg_lookups view would have the lookup meaning in the
1914 -- language used in the current session.
1915 --
1916 -- Commented the below piece of code and compared p_launch_action
1917 -- and p_action_fired parameters below with the new constants to resolve
1918 -- the value entered. kabalakr.
1919
1920 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
1921 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
1922
1923 -- The Action Code should get executed only if
1924 -- launch_action is 'Yes' and action_fired is 'No'
1925
1926 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
1927 NULL;
1928 ELSE
1929 -- dont fire the action.
1930 RETURN;
1931 END IF;
1932
1933 -- Get the plan_id and org_id now.
1934
1935 l_organization_id := get_organization_id (p_organization_code);
1936 l_plan_id := get_plan_id(p_plan_name);
1937
1938 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
1939
1940 -- We may need to populate appropriate error message here before return.
1941 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1942 END IF;
1943
1944 IF p_transaction_date IS NULL THEN
1945 l_transaction_date := sysdate;
1946 ELSE
1947 l_transaction_date := qltdate.any_to_date(p_transaction_date);
1948 END IF;
1949
1950 -- Update the Disposition Status to 'Pending'.
1951 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
1952
1953 -- Get the Inventory Item ID and Locator ID and Reason ID.
1954 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
1955 l_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_locator);
1956
1957 OPEN reason_cur;
1958 FETCH reason_cur INTO l_reason_id;
1959 CLOSE reason_cur;
1960
1961
1962 l_header_id := WIP_MATERIAL_TXN_INT(
1963 p_job_name,
1964 l_item_id,
1965 p_revision,
1966 p_subinventory,
1967 l_locator_id,
1968 p_lot_number,
1969 p_serial_number,
1970 p_transaction_uom,
1971 p_transaction_qty,
1972 l_transaction_date,
1973 p_op_seq_num,
1974 l_reason_id,
1975 'QA ACTION:WIP COMP RETURN',
1976 43,
1977 27,
1978 p_collection_id,
1979 p_occurrence,
1980 l_organization_id);
1981
1982 l_outcome := MTL_ONLINE_TRANSACTION_PUB.PROCESS_ONLINE(l_header_id,
1983 l_time_out,
1984 l_error_code,
1985 l_error_explanation);
1986
1987
1988 IF (l_outcome <> TRUE) THEN
1989 l_result := g_failed;
1990
1991 OPEN mti_cur(l_header_id);
1992 FETCH mti_cur INTO l_int_txn_id;
1993 CLOSE mti_cur;
1994
1995 l_txn_id := 0;
1996
1997
1998 ELSE
1999 l_result := g_success;
2000
2001 OPEN mmt_cur(l_header_id);
2002 FETCH mmt_cur INTO l_txn_id;
2003 CLOSE mmt_cur;
2004
2005 l_int_txn_id := 0;
2006
2007
2008 END IF;
2009
2010
2011 -- call WRITE_BACK procedure.
2012
2013 WRITE_BACK(p_plan_id => l_plan_id,
2014 p_collection_id => p_collection_id,
2015 p_occurrence => p_occurrence,
2016 p_status => l_result,
2017 p_mti_transaction_header_id => l_header_id,
2018 p_mti_transaction_interface_id => l_int_txn_id,
2019 p_mmt_transaction_id => l_txn_id);
2020
2021 END WIP_COMP_RETURN;
2022
2023
2024 -------------------------------------------------------------------------------
2025 -- WIP Component Issue
2026 -------------------------------------------------------------------------------
2027 --
2028 -- Start of Comments
2029 --
2030 -- API name WIP_COMP_ISSUE
2031 -- Type Public
2032 -- Function
2033 --
2034 -- Pre-reqs
2035 --
2036 -- Parameters
2037 -- p_item => Item Name
2038 -- p_job_name => Job Name
2039 -- p_revision => Item Revision
2040 -- p_subinventory => Subinventory Name
2041 -- p_locator => Locator Name
2042 -- p_lot_number => Lot Number
2043 -- p_serial_number => Serial Number
2044 -- p_transaction_uom => Transacting UOM
2045 -- p_transaction_qty => Transaction Quantity
2046 -- p_transaction_date => WIP Component Issue requires date in varchar2. This
2047 -- should be either Canonical format ('YYYY/MM/DD') or
2048 -- in Real format ('DD-MON-YYYY')
2049 -- p_op_seq_num => Operation Sequence Number
2050 -- p_reason_code => Reason Code
2051 -- p_collection_id => Collection ID
2052 -- p_occurrence => Occurrence
2053 -- p_plan_name => Collection Plan Name
2054 -- p_organization_code => Organization Code, from which transaction happens
2055 -- p_launch_action => This takes two values(Yes/No)
2056 -- Pass a value of 'Yes' to successfully perform
2057 -- WIP Component Issue Transaction
2058 -- p_action_fired => This takes two values(Yes/No)
2059 -- Pass a value of 'No' to successfully perform
2060 -- WIP Component Issue Transaction
2061 --
2062 -- Notes
2063 --
2064 -- We are performing all the below activities here
2065 --
2066 -- 1. Get the collection element values. Derive the other values.
2067 -- 2. Call WIP_MATERIAL_TXN_INT () function for inserting into interface table.
2068 -- 3. Call mtl_online_transaction_pub.process_online () procedure.
2069 -- 4. Call the WRITE_BACK () procedure for handshaking.
2070 --
2071 --
2072 -- End of Comments
2073 --
2074
2075 PROCEDURE WIP_COMP_ISSUE (p_job_name IN VARCHAR2,
2076 p_item IN VARCHAR2,
2077 p_revision IN VARCHAR2,
2078 p_subinventory IN VARCHAR2,
2079 p_locator IN VARCHAR2,
2080 p_lot_number IN VARCHAR2,
2081 p_serial_number IN VARCHAR2,
2082 p_transaction_uom IN VARCHAR2,
2083 p_transaction_qty IN NUMBER,
2084 p_transaction_date IN VARCHAR2,
2085 p_op_seq_num IN NUMBER,
2086 p_reason_code IN VARCHAR2,
2087 p_collection_id IN NUMBER,
2088 p_occurrence IN NUMBER,
2089 p_organization_code IN VARCHAR2,
2090 p_plan_name IN VARCHAR2,
2091 p_launch_action IN VARCHAR2,
2092 p_action_fired IN VARCHAR2) IS
2093
2094
2095 l_plan_id NUMBER;
2096 l_organization_id NUMBER;
2097
2098 -- Bug 3684073. These variables are no longer required.
2099 -- l_launch_action NUMBER;
2100 -- l_action_fired NUMBER;
2101
2102 l_outcome BOOLEAN := TRUE;
2103 l_header_id NUMBER;
2104 l_result VARCHAR2(240);
2105 l_error_code VARCHAR2(240) := NULL;
2106 l_error_explanation VARCHAR2(240) := NULL;
2107 l_time_out NUMBER := 1200;
2108 l_int_txn_id NUMBER;
2109 l_txn_id NUMBER;
2110 l_transaction_date DATE;
2111 l_item_id NUMBER;
2112 l_locator_id NUMBER;
2113 l_reason_id NUMBER;
2114
2115
2116 CURSOR mmt_cur (set_id NUMBER) IS
2117 SELECT transaction_id
2118 FROM mtl_material_transactions
2119 WHERE transaction_set_id = set_id;
2120
2121 CURSOR mti_cur (header_id NUMBER) IS
2122 SELECT transaction_interface_id
2123 FROM mtl_transactions_interface
2124 WHERE transaction_header_id = header_id;
2125
2126 CURSOR reason_cur IS
2127 SELECT reason_id
2128 FROM mtl_transaction_reasons_val_v
2129 WHERE reason_name LIKE p_reason_code;
2130
2131 BEGIN
2132
2133 -- Get the value entered in confirm_action Collection element.
2134
2135 -- Bug 3684073. We should not derive the lookup_code value from
2136 -- mfg_lookups because the value passed to this api would be the
2137 -- qa_plan_char_value_lookups.short_code, which is not a translated
2138 -- column. The mfg_lookups view would have the lookup meaning in the
2139 -- language used in the current session.
2140 --
2141 -- Commented the below piece of code and compared p_launch_action
2142 -- and p_action_fired parameters below with the new constants to resolve
2143 -- the value entered. kabalakr.
2144
2145 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
2146 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
2147
2148
2149 -- The Action Code should get executed only if
2150 -- launch_action is 'Yes' and action_fired is 'No'
2151
2152 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
2153 NULL;
2154 ELSE
2155 -- dont fire the action.
2156 RETURN;
2157 END IF;
2158
2159 -- Get the plan_id and org_id now.
2160
2161 l_organization_id := get_organization_id (p_organization_code);
2162 l_plan_id := get_plan_id(p_plan_name);
2163
2164 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
2165
2166 -- We may need to populate appropriate error message here before return.
2167 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2168 END IF;
2169
2170 IF p_transaction_date IS NULL THEN
2171 l_transaction_date := sysdate;
2172 ELSE
2173 l_transaction_date := qltdate.any_to_date(p_transaction_date);
2174 END IF;
2175
2176 -- Update the Disposition Status to 'Pending'.
2177 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
2178
2179 -- Get the Inventory Item ID and Locator ID and Reason ID.
2180 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
2181 l_locator_id := qa_flex_util.get_locator_id(l_organization_id, p_locator);
2182
2183 OPEN reason_cur;
2184 FETCH reason_cur INTO l_reason_id;
2185 CLOSE reason_cur;
2186
2187
2188 l_header_id := WIP_MATERIAL_TXN_INT(
2189 p_job_name,
2190 l_item_id,
2191 p_revision,
2192 p_subinventory,
2193 l_locator_id,
2194 p_lot_number,
2195 p_serial_number,
2196 p_transaction_uom,
2197 -p_transaction_qty,
2198 l_transaction_date,
2199 p_op_seq_num,
2200 l_reason_id,
2201 'QA ACTION:WIP COMP ISSUE',
2202 35,
2203 1,
2204 p_collection_id,
2205 p_occurrence,
2206 l_organization_id);
2207
2208
2209 l_outcome := MTL_ONLINE_TRANSACTION_PUB.PROCESS_ONLINE(l_header_id,
2210 l_time_out,
2211 l_error_code,
2212 l_error_explanation);
2213
2214
2215 IF (l_outcome <> TRUE) THEN
2216 l_result := g_failed;
2217
2218 OPEN mti_cur(l_header_id);
2219 FETCH mti_cur INTO l_int_txn_id;
2220 CLOSE mti_cur;
2221
2222 l_txn_id := 0;
2223
2224
2225 ELSE
2226 l_result := g_success;
2227
2228 OPEN mmt_cur(l_header_id);
2229 FETCH mmt_cur INTO l_txn_id;
2230 CLOSE mmt_cur;
2231
2232 l_int_txn_id := 0;
2233
2234
2235 END IF;
2236
2237 -- call WRITE_BACK procedure.
2238
2239
2240 WRITE_BACK(p_plan_id => l_plan_id,
2241 p_collection_id => p_collection_id,
2242 p_occurrence => p_occurrence,
2243 p_status => l_result,
2244 p_mti_transaction_header_id => l_header_id,
2245 p_mti_transaction_interface_id => l_int_txn_id,
2246 p_mmt_transaction_id => l_txn_id);
2247
2248
2249 END WIP_COMP_ISSUE;
2250
2251
2252
2253 FUNCTION WIP_MATERIAL_TXN_INT(
2254 p_job_name VARCHAR2,
2255 p_item_id NUMBER,
2256 p_revision VARCHAR2,
2257 p_subinventory VARCHAR2,
2258 p_locator_id NUMBER,
2259 p_lot_number VARCHAR2,
2260 p_serial_number VARCHAR2,
2261 p_transaction_uom VARCHAR2,
2262 p_transaction_qty NUMBER,
2263 p_transaction_date DATE,
2264 p_op_seq_num NUMBER,
2265 p_reason_id NUMBER,
2266 p_source_code VARCHAR2,
2267 p_txn_type_id NUMBER,
2268 p_txn_action_id NUMBER,
2269 p_collection_id NUMBER,
2270 p_occurrence NUMBER,
2271 p_organization_id NUMBER)
2272 RETURN NUMBER IS
2273
2274 PRAGMA AUTONOMOUS_TRANSACTION;
2275
2276 l_header_id NUMBER;
2277 l_entity_id NUMBER;
2278 l_entity_type VARCHAR2(30);
2279 l_interface_id NUMBER;
2280 l_lot_control_code NUMBER;
2281 l_serial_control_code NUMBER;
2282 l_update_by NUMBER := fnd_global.user_id;
2283
2284 CURSOR wip_cur IS
2285 SELECT wip_entity_id, entity_type
2286 FROM wip_entities
2287 WHERE wip_entity_name = p_job_name
2288 AND organization_id = p_organization_id;
2289
2290 CURSOR item_cur IS
2291 SELECT lot_control_code,serial_number_control_code
2292 FROM mtl_system_items_b
2293 WHERE inventory_item_id = p_item_id
2294 AND organization_id = p_organization_id;
2295
2296
2297 BEGIN
2298
2299 OPEN wip_cur;
2300 FETCH wip_cur INTO l_entity_id,l_entity_type;
2301 CLOSE wip_cur ;
2302
2303
2304 OPEN item_cur;
2305 FETCH item_cur INTO l_lot_control_code,l_serial_control_code;
2306 CLOSE item_cur;
2307
2308 INSERT INTO MTL_TRANSACTIONS_INTERFACE (
2309 TRANSACTION_HEADER_ID,
2310 TRANSACTION_INTERFACE_ID,
2311 SOURCE_CODE,
2312 SOURCE_HEADER_ID,
2313 SOURCE_LINE_ID,
2314 PROCESS_FLAG,
2315 TRANSACTION_MODE,
2316 LOCK_FLAG,
2317 LAST_UPDATE_DATE,
2318 LAST_UPDATED_BY,
2319 CREATION_DATE,
2320 CREATED_BY,
2321 INVENTORY_ITEM_ID,
2322 REVISION,
2323 ORGANIZATION_ID,
2324 TRANSACTION_QUANTITY,
2325 TRANSACTION_UOM,
2326 TRANSACTION_DATE,
2327 SUBINVENTORY_CODE,
2328 LOCATOR_ID,
2329 TRANSACTION_TYPE_ID,
2330 TRANSACTION_ACTION_ID,
2331 TRANSACTION_SOURCE_TYPE_ID,
2332 TRANSACTION_SOURCE_ID,
2333 WIP_ENTITY_TYPE,
2334 OPERATION_SEQ_NUM,
2335 REASON_ID)
2336 VALUES (
2337 mtl_material_transactions_s.nextval,
2338 mtl_material_transactions_s.nextval,
2339 p_source_code,
2340 p_collection_id,
2341 p_occurrence,
2342 1,
2343 3,
2344 2,
2345 SYSDATE,
2346 l_update_by,
2347 SYSDATE,
2348 l_update_by,
2349 p_item_id,
2350 p_revision,
2351 p_organization_id,
2352 p_transaction_qty,
2353 p_transaction_uom,
2354 p_transaction_date,
2355 p_subinventory,
2356 p_locator_id,
2357 p_txn_type_id,
2358 p_txn_action_id,
2359 5,
2360 l_entity_id,
2361 l_entity_type,
2362 p_op_seq_num,
2363 p_reason_id)
2364
2365 RETURNING TRANSACTION_HEADER_ID INTO l_header_id;
2366
2367
2368 IF l_serial_control_code IN (2,5) OR l_lot_control_code = 2 THEN
2369
2370 -- The item is serial control set to predefined/at receipt.
2371 -- (l_serial_control_code IN (2,5)).
2372 -- The item is lot controlled.(l_lot_control_code = 2).
2373
2374 -- One of Lot or Serial controls are existing for the item
2375 -- Need to insert in appropriate interface table against
2376 -- the transaction_header_id
2377
2378 l_interface_id := l_header_id;
2379 END IF;
2380
2381 IF l_lot_control_code = 2 THEN
2382
2383 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
2384 TRANSACTION_INTERFACE_ID,
2385 SERIAL_TRANSACTION_TEMP_ID,
2386 LOT_NUMBER,
2387 TRANSACTION_QUANTITY,
2388 LAST_UPDATE_DATE,
2389 LAST_UPDATED_BY,
2390 CREATION_DATE,
2391 CREATED_BY)
2392 VALUES (
2393 l_interface_id,
2394 l_interface_id,
2395 p_lot_number,
2396 p_transaction_qty,
2397 SYSDATE,
2398 l_update_by,
2399 SYSDATE,
2400 l_update_by);
2401
2402 END IF;
2403
2404
2405
2406 IF l_serial_control_code IN (2,5) THEN
2407
2408
2409 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
2410 TRANSACTION_INTERFACE_ID,
2411 FM_SERIAL_NUMBER,
2412 LAST_UPDATE_DATE,
2413 LAST_UPDATED_BY,
2414 CREATION_DATE,
2415 CREATED_BY)
2416 VALUES (
2417 l_interface_id,
2418 p_serial_number,
2419 SYSDATE,
2420 l_update_by,
2421 SYSDATE,
2422 l_update_by);
2423
2424 END IF;
2425
2426 -- Commit the insert.
2427 COMMIT;
2428
2429 RETURN l_header_id;
2430
2431 END WIP_MATERIAL_TXN_INT;
2432
2433 -------------------------------------------------------------------------------
2434 -- WIP Move Transactions API
2435 -------------------------------------------------------------------------------
2436 -- Start of Comments
2437 -- API name WIP_MOVE_TXN
2438 -- Type Public
2439 -- Function
2440 --
2441 -- Pre-reqs
2442 --
2443 -- Parameters
2444 -- p_item => Item Name
2445 -- p_job_name => Job Name to do the WIP Scrap Transaction
2446 -- p_from_op_seq => From Operation Sequence Number
2447 -- p_from_intra_step => From Intraoperation Step
2448 -- p_to_op_seq => To Operation Sequence Number
2449 -- p_to_intra_step => To Intraoperation Step
2450 -- p_fm_op_code => Operation Code
2451 -- p_to_op_code => To Operation Code
2452 -- p_reason_name => Reason Code
2453 -- p_uom => Transacting UOM
2454 -- p_quantity => Quantity
2455 -- p_txn_date => WIP Move requires date in varchar2. This should be either Canonical
2456 -- format ('YYYY/MM/DD') or in Real format ('DD-MON-YYYY')
2457 -- p_collection_id => Collection ID
2458 -- p_occurrence => Occurrence
2459 -- p_plan_name => Collection Plan Name
2460 -- p_organization_code => Organization Code, from which transaction happens
2461 -- p_launch_action => This takes two values(Yes/No)
2462 -- Pass a value of 'Yes' to successfully do WIP Scrap Transactions
2463 -- p_action_fired => This takes two values(Yes/No)
2464 -- Pass a value of 'No' to successfully do WIP Scrap Transactions
2465 --
2466 -- Version Current version = 1.0
2467 -- Initial version = 1.0
2468 --
2469 -- Notes
2470 --
2471 -- We are performing all the below activities here
2472 --
2473 -- 1. Get the different id values.
2474 -- 2. Call WIP_SCRAP_WIP_MOVE_INT() procedure for inserting into interface table
2475 -- and spawn the WIP Move Transaction manager.
2476 -- 3. Wait for the Move Manager and Worker Program to get completed.
2477 -- 4. Get the results and perform the handshaking. Call the procedure
2478 -- WRITE_BACK() for performing the same.
2479 --
2480 -- End of Comments
2481 --
2482 --
2483 -- Bug Fixes
2484 --
2485 -- Bug 2689276 : Call WRITE_BACK() to handshake the concurrent request id as
2486 -- soon as the concurrnt request gets launched.
2487 --
2488 --
2489
2490
2491 PROCEDURE WIP_MOVE_TXN(
2492 p_item IN VARCHAR2,
2493 p_job_name IN VARCHAR2,
2494 p_from_op_seq IN NUMBER,
2495 p_from_intra_step IN VARCHAR2,
2496 p_to_op_seq IN NUMBER,
2497 p_to_intra_step IN VARCHAR2,
2498 p_fm_op_code IN VARCHAR2,
2499 p_to_op_code IN VARCHAR2,
2500 p_reason_name IN VARCHAR2,
2501 p_uom IN VARCHAR2,
2502 p_quantity IN NUMBER,
2503 p_txn_date IN VARCHAR2,
2504 p_collection_id IN NUMBER,
2505 p_occurrence IN NUMBER,
2506 p_organization_code IN VARCHAR2,
2507 p_plan_name IN VARCHAR2,
2508 p_launch_action IN VARCHAR2,
2509 p_action_fired IN VARCHAR2) IS
2510
2511
2512 l_request NUMBER;
2513 l_child_request NUMBER;
2514 l_plan_id NUMBER;
2515 l_organization_id NUMBER;
2516 l_to_step NUMBER;
2517 l_from_step NUMBER;
2518 l_src_code VARCHAR2(30);
2519
2520 -- Bug 3684073. These variables are no longer required.
2521 -- l_launch_action NUMBER;
2522 -- l_action_fired NUMBER;
2523
2524 l_reason_id NUMBER;
2525 l_group_id NUMBER;
2526 l_transaction_id NUMBER;
2527 l_txn_id NUMBER;
2528 l_item_id NUMBER;
2529
2530 l_wait BOOLEAN;
2531 l_phase VARCHAR2(2000);
2532 l_status VARCHAR2(2000);
2533 l_devphase VARCHAR2(2000);
2534 l_devstatus VARCHAR2(2000);
2535 l_message VARCHAR2(2000);
2536
2537 l_result VARCHAR2(1800);
2538 l_err_msg VARCHAR2(2000) := NULL;
2539 l_err_col VARCHAR2(1000);
2540 l_arg VARCHAR2(240);
2541
2542 -- Bug 7395743 FP for bug 7394787.pdube.
2543 l_txn_date DATE;
2544
2545 CURSOR txn_cur IS
2546 SELECT wip_transactions_s.nextval
2547 FROM DUAL;
2548
2549 CURSOR req_cur IS
2550 SELECT request_id
2551 FROM FND_CONC_REQ_SUMMARY_V
2552 WHERE parent_request_id = l_request;
2553
2554 CURSOR grp_cur IS
2555 SELECT group_id
2556 FROM WIP_MOVE_TXN_INTERFACE
2557 WHERE source_code = l_src_code;
2558
2559
2560 CURSOR txns_cur IS
2561 SELECT transaction_id
2562 FROM wip_move_transactions
2563 WHERE source_code = l_src_code;
2564
2565 CURSOR reason_cur IS
2566 SELECT reason_id
2567 FROM mtl_transaction_reasons_val_v
2568 WHERE reason_name LIKE p_reason_name;
2569
2570 CURSOR arg_cur IS
2571 SELECT argument_text
2572 FROM FND_CONC_REQ_SUMMARY_V
2573 WHERE request_id = l_child_request;
2574
2575 BEGIN
2576
2577 -- Get the value entered in confirm_action Collection element.
2578
2579 -- Bug 3684073. We should not derive the lookup_code value from
2580 -- mfg_lookups because the value passed to this api would be the
2581 -- qa_plan_char_value_lookups.short_code, which is not a translated
2582 -- column. The mfg_lookups view would have the lookup meaning in the
2583 -- language used in the current session.
2584 --
2585 -- Commented the below piece of code and compared p_launch_action
2586 -- and p_action_fired parameters below with the new constants to resolve
2587 -- the value entered. kabalakr.
2588
2589 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
2590 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
2591
2592 -- The Action Code should get executed only if
2593 -- Launch_action is 'Yes' and relaunch_flag is 'No'
2594
2595 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
2596 NULL;
2597 ELSE
2598 -- dont fire the action
2599 RETURN;
2600 END IF;
2601
2602 l_organization_id := get_organization_id(p_organization_code);
2603 l_plan_id := get_plan_id(p_plan_name);
2604
2605 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
2606
2607 -- We may need to populate appropriate error message here before return.
2608 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2609 END IF;
2610
2611 l_item_id := qa_flex_util.get_item_id(l_organization_id, p_item);
2612
2613 -- Get transaction_id and lookup code for the Intraoperation step.
2614
2615 l_to_step := get_mfg_lookups_value(p_to_intra_step,'WIP_INTRAOPERATION_STEP');
2616 l_from_step := get_mfg_lookups_value(p_from_intra_step,'WIP_INTRAOPERATION_STEP');
2617
2618 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
2619
2620 OPEN txn_cur;
2621 FETCH txn_cur INTO l_txn_id;
2622 CLOSE txn_cur;
2623
2624 OPEN reason_cur;
2625 FETCH reason_cur INTO l_reason_id;
2626 CLOSE reason_cur;
2627
2628 --WIP move is similar to scrap, so call the same WIP_SCRAP_WIP_MOVE_INT
2629 -- Call the wipscrap_int(). It spawns and returns back the concurrent request
2630 -- id of the WIP Move Manager Program.
2631
2632 -- Bug 7395743 FP for bug 7394787.pdube Sun Dec 20 22:30:33 PST 2009
2633 -- The time part of the transaction date was truncating,
2634 -- making this code fix to handle it.
2635 IF (qltdate.canon_to_date(p_txn_date) - trunc(sysdate)) = 0 THEN
2636 l_txn_date := sysdate;
2637 ELSE
2638 l_txn_date := qltdate.canon_to_date(p_txn_date);
2639 END IF;
2640
2641 l_request := WIP_SCRAP_WIP_MOVE_INT(
2642 l_item_id,
2643 l_txn_id,
2644 p_job_name,
2645 null, --no scrap account
2646 p_from_op_seq,
2647 l_from_step,
2648 p_to_op_seq,
2649 l_to_step,
2650 nvl(p_fm_op_code,NULL),
2651 nvl(p_to_op_code,NULL),
2652 nvl(l_reason_id,0),
2653 p_uom,
2654 p_quantity,
2655 qltdate.any_to_date(l_txn_date), -- Bug 7395743
2656 p_organization_code,
2657 p_collection_id);
2658
2659 IF (l_request = 0) THEN
2660
2661 -- Concurrent Request not launched
2662 l_result := g_failed;
2663 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
2664
2665 WRITE_BACK(p_plan_id => l_plan_id,
2666 p_collection_id => p_collection_id,
2667 p_occurrence => p_occurrence,
2668 p_status => l_result,
2669 p_message => l_err_msg);
2670 RETURN;
2671
2672 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
2673 -- write back the concurrent request id.
2674
2675 ELSE
2676
2677 WRITE_BACK( p_plan_id => l_plan_id,
2678 p_collection_id => p_collection_id,
2679 p_occurrence => p_occurrence,
2680 p_status => g_pending,
2681 p_request_id => l_request);
2682
2683 END IF;
2684
2685 -- If request gets launched, proceed.
2686 -- But first, wait for the WIP Move Manager Program to be completed.
2687 -- After that, wait for the WIP Move Worker to be completed.
2688 -- We wait 100 minutes each for these requests to be Completed. And we
2689 -- check in every 15 Minutes
2690
2691 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
2692 g_check_request_time,
2693 g_total_request_time,
2694 l_phase,
2695 l_status,
2696 l_devphase,
2697 l_devstatus,
2698 l_message);
2699
2700 l_src_code := 'QA WIP SCRAP:'||to_char(l_txn_id);
2701 --This QA WIP SCRAP is just a varchar that we use
2702 --Same Varchar is used for WIP move and WIP Scrap
2703 --This will not cause any problem
2704
2705 OPEN grp_cur;
2706 FETCH grp_cur INTO l_group_id;
2707 CLOSE grp_cur;
2708
2709 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
2710 -- If the Manager gets completed, we need to find the correct
2711 -- worker that processes the record we have inserted.
2712
2713 FOR i IN req_cur LOOP
2714 l_child_request := i.request_id;
2715
2716 -- Get the Argument text of the concurrent request so that we can
2717 -- extract the group_id.
2718
2719 OPEN arg_cur;
2720 FETCH arg_cur INTO l_arg;
2721 IF (to_char(l_group_id) = substr(l_arg, 1, instr(l_arg, ',') - 1)) THEN
2722 CLOSE arg_cur;
2723 EXIT;
2724 END IF;
2725 CLOSE arg_cur;
2726
2727 END LOOP;
2728
2729 ELSE
2730 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2731 END IF;
2732
2733 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_child_request,
2734 g_check_request_time,
2735 g_total_request_time,
2736 l_phase,
2737 l_status,
2738 l_devphase,
2739 l_devstatus,
2740 l_message);
2741
2742 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
2743 IF (substr(l_devstatus,1,7) = 'WARNING') THEN
2744
2745 l_result := g_warning;
2746 l_transaction_id := 0;
2747
2748 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
2749
2750 l_result := g_success;
2751
2752 OPEN txns_cur;
2753 FETCH txns_cur INTO l_transaction_id;
2754 CLOSE txns_cur;
2755
2756 l_group_id := 0;
2757
2758 ELSE
2759 -- If error, or any other cases, give FAILURE.
2760 l_result := g_failed;
2761 l_transaction_id := 0;
2762
2763 END IF;
2764
2765 -- Call for handshaking the outcome onto the Collection Plan.
2766 WRITE_BACK(p_plan_id => l_plan_id,
2767 p_collection_id => p_collection_id,
2768 p_occurrence => p_occurrence,
2769 p_status => l_result,
2770 p_wmti_group_id => l_group_id,
2771 p_wmt_transaction_id => l_transaction_id,
2772 p_request_id => l_child_request);
2773
2774 END IF; -- if complete.
2775
2776 END WIP_MOVE_TXN;
2777
2778
2779 -------------------------------------------------------------------------------
2780 -- Add New Rework Operation API
2781 -------------------------------------------------------------------------------
2782 -- Start of Comments
2783 -- API name REWORK_ADD_OPERATION
2784 -- Type Public
2785 -- Function
2786 --
2787 -- Pre-reqs
2788 --
2789 -- Parameters
2790 -- p_job_name => Name of the Rework Job to be created
2791 -- p_op_seq_num => Rework Operation Sequence Number
2792 -- p_operation_code => Rework Operation Code
2793 -- p_department_code => Department Code for NON Standard Operation.
2794 -- p_res_seq_num => Resource Sequence Number to add resources to NON
2795 -- Standard Operations.
2796 -- p_resource_code => Resource Code to add resources to NON Standard Operations.
2797 -- p_assigned_units => Assigned units for the resource.
2798 -- p_usage_rate => Usage rate of resources.
2799 -- p_start_date => First Unit Start date and First unit completion date.
2800 -- p_end_date => Last Unit Start date and Last unit completion date.
2801 -- p_collection_id => Collection ID
2802 -- p_occurrence => Occurrence
2803 -- p_plan_name => Collection Plan Name
2804 -- p_organization_code => Organization Code, from which transaction happens
2805 -- p_launch_action => This takes two values(Yes/No)
2806 -- Pass a value of 'Yes' to successfully create Rework Job
2807 -- p_action_fired => This takes two values(Yes/No)
2808 -- Pass a value of 'No' to successfully create Rework Job
2809 --
2810 -- Version Current version = 1.0
2811 -- Initial version = 1.0
2812 --
2813 -- Notes
2814 --
2815 -- We are performing all the below activities here
2816 --
2817 -- 1. Get the different id values.
2818 -- 2. Call REWORK_OP_ADD_OP_INT() procedure for inserting into interface table
2819 -- and spawn the WIP Mass Load Program (WICMLX) for adding Standard and NON
2820 -- Standard operations.
2821 -- 3. Wait for the Mass Load Program to get completed.
2822 -- 4. Get the results and perform the handshaking. Call the procedure
2823 -- WRITE_BACK() for performing the same.
2824 -- 5. Call REWORK_OP_ADD_RES_INT() procedure for inserting into interface table
2825 -- and spawn the WIP Mass Load Program (WICMLX) for automatic adding of resources
2826 -- for Standard Operations and adding specified resources for specified NON
2827 -- Standard operations.
2828 -- 6. Wait for the Mass Load Program to get completed.
2829 -- 7. Get the results and perform the handshaking. Call the procedure
2830 -- WRITE_BACK() for performing the same.
2831 --
2832 -- End of Comments
2833 --
2834 -- Bug Fixes
2835 --
2836 -- Bug 2689276 : Call WRITE_BACK() to handshake the concurrent request id as
2837 -- soon as the concurrnt request gets launched.
2838 --
2839 -- Bug 2656461 : Code added to support copying of attachments to the
2840 -- WIP_DISCRETE_OPERATIONS entity, once the Action is successful.
2841 --
2842 -- Bug 2714880 : Derive the STATUS_TYPE of the Job from WIP_DISCRETE_JOBS. This
2843 -- value is needed when importing Operations and resources in the
2844 -- internal functions REWORK_OP_ADD_OP_INT() and
2845 -- REWORK_OP_ADD_RES_INT().
2846 --
2847 --
2848
2849 PROCEDURE REWORK_ADD_OPERATION(
2850 p_job_name IN VARCHAR2,
2851 p_op_seq_num IN NUMBER,
2852 p_operation_code IN VARCHAR2,
2853 p_department_code IN VARCHAR2,
2854 p_res_seq_num IN NUMBER,
2855 p_resource_code IN VARCHAR2,
2856 p_assigned_units IN NUMBER,
2857 p_usage_rate IN NUMBER,
2858 p_start_date IN VARCHAR2,
2859 p_end_date IN VARCHAR2,
2860 p_collection_id IN NUMBER,
2861 p_occurrence IN NUMBER,
2862 p_organization_code IN VARCHAR2,
2863 p_plan_name IN VARCHAR2,
2864 p_launch_action IN VARCHAR2,
2865 p_action_fired IN VARCHAR2) IS
2866
2867
2868 l_request NUMBER;
2869 l_group_id NUMBER;
2870 l_plan_id NUMBER;
2871 l_organization_id NUMBER;
2872
2873 -- Bug 3684073. These variables are no longer required.
2874 -- l_launch_action NUMBER;
2875 -- l_action_fired NUMBER;
2876
2877 l_err_msg VARCHAR2(2000) := NULL;
2878
2879 l_wait BOOLEAN;
2880 l_phase VARCHAR2(2000);
2881 l_status VARCHAR2(2000);
2882 l_devphase VARCHAR2(2000);
2883 l_devstatus VARCHAR2(2000);
2884 l_message VARCHAR2(2000);
2885 l_result VARCHAR2(1800);
2886
2887 l_dup_op_seq NUMBER;
2888 l_dup_res_seq NUMBER;
2889
2890 l_wip_entity_id NUMBER;
2891 l_operation_id NUMBER;
2892 l_department_id NUMBER;
2893 l_resource_id NUMBER;
2894 l_op_type NUMBER := 2;
2895
2896 -- Bug 2714880.
2897 l_status_type NUMBER;
2898
2899
2900 CURSOR group_cur IS
2901 SELECT WIP_JOB_SCHEDULE_INTERFACE_S.nextval
2902 FROM DUAL;
2903
2904 CURSOR job_cur IS
2905 SELECT wip_entity_id
2906 FROM wip_entities
2907 WHERE wip_entity_name = p_job_name
2908 AND organization_id = l_organization_id;
2909
2910 CURSOR op_cur IS
2911 SELECT bsoav.standard_operation_id
2912 FROM bom_standard_operations_all_v bsoav, bom_departments_val_v bdvv
2913 WHERE bsoav.organization_id = l_organization_id
2914 AND bsoav.department_id = bdvv.department_id
2915 AND NVL (bsoav.operation_type, 1) = 1
2916 AND bsoav.operation_code = p_operation_code;
2917
2918 CURSOR op_seq_cur IS
2919 SELECT operation_seq_num
2920 FROM wip_operations
2921 WHERE wip_entity_id = l_wip_entity_id
2922 AND operation_seq_num = p_op_seq_num;
2923
2924 CURSOR dept_cur IS
2925 SELECT department_id
2926 FROM bom_departments
2927 WHERE organization_id = l_organization_id
2928 AND nvl(disable_date, sysdate + 2) > sysdate
2929 AND department_code = p_department_code;
2930
2931 CURSOR res_cur IS
2932 SELECT resource_id
2933 FROM bom_resources_val_v
2934 WHERE organization_id = l_organization_id
2935 AND resource_code = p_resource_code;
2936
2937 CURSOR res_seq_cur IS
2938 SELECT resource_seq_num
2939 FROM wip_operation_resources
2940 WHERE wip_entity_id = l_wip_entity_id
2941 AND operation_seq_num = p_op_seq_num
2942 AND resource_seq_num = p_res_seq_num;
2943
2944 -- Bug 2714880. Added the cursor below to fetch the status of the job.
2945
2946 CURSOR job_status IS
2947 SELECT status_type
2948 FROM wip_discrete_jobs
2949 WHERE wip_entity_id = l_wip_entity_id;
2950
2951
2952 BEGIN
2953
2954 -- Get the value entered in confirm_action Collection element.
2955
2956 -- Bug 3684073. We should not derive the lookup_code value from
2957 -- mfg_lookups because the value passed to this api would be the
2958 -- qa_plan_char_value_lookups.short_code, which is not a translated
2959 -- column. The mfg_lookups view would have the lookup meaning in the
2960 -- language used in the current session.
2961 --
2962 -- Commented the below piece of code and compared p_launch_action
2963 -- and p_action_fired parameters below with the new constants to resolve
2964 -- the value entered. kabalakr.
2965
2966 -- l_launch_action := get_mfg_lookups_value(p_launch_action,'SYS_YES_NO');
2967 -- l_action_fired := get_mfg_lookups_value(p_action_fired,'SYS_YES_NO');
2968
2969 -- The Action Code should get executed only if
2970 -- Launch_action is 'Yes' and Action_fired is 'No'
2971
2972 IF (upper(p_launch_action) = g_lookup_yes AND upper(p_action_fired) = g_lookup_no) THEN
2973 NULL;
2974
2975 ELSE
2976 -- dont fire the action.
2977 RETURN;
2978 END IF;
2979
2980 -- Get the plan_id, group_id and org_id now. We need these values
2981 -- for Handshaking in this procedure.
2982
2983 l_organization_id := get_organization_id(p_organization_code);
2984 l_plan_id := get_plan_id(p_plan_name);
2985
2986
2987 IF (l_plan_id = -1 OR l_organization_id = -1) THEN
2988
2989 -- We may need to populate appropriate error message here before return.
2990 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2991 END IF;
2992
2993 OPEN group_cur;
2994 FETCH group_cur INTO l_group_id;
2995 CLOSE group_cur;
2996
2997
2998 -- Update the Disposition Status to 'Pending'.
2999 UPDATE_STATUS(l_plan_id,p_collection_id,p_occurrence);
3000
3001 -- Derive the wip_entity_id of the Job.
3002 OPEN job_cur;
3003 FETCH job_cur INTO l_wip_entity_id;
3004 CLOSE job_cur;
3005
3006 -- Bug 2714880. Fetch the status_type of the job. We pass this value to the
3007 -- internal functions REWORK_OP_ADD_OP_INT() REWORK_OP_ADD_RES_INT() that
3008 -- adds Operations and Resources respectively.
3009 OPEN job_status;
3010 FETCH job_status INTO l_status_type;
3011 CLOSE job_status;
3012
3013 -- Validate the Rework Operation Sequence Num. If Operation code is specified,
3014 -- its a standard Operation. If Operation Code is not mentioned, user is
3015 -- intending to add NON Standard Operation OR to add just resource to the
3016 -- existing Operation Sequence Number.
3017
3018 OPEN op_seq_cur;
3019 FETCH op_seq_cur INTO l_dup_op_seq;
3020 CLOSE op_seq_cur;
3021
3022
3023 -- If Operation Seq Num is already existing and the Operation code is
3024 -- specified, user is intending to add a standard Operation with Duplicate
3025 -- Operation Seq Num. So error out.
3026
3027 IF (l_dup_op_seq IS NOT NULL) AND (p_operation_code IS NOT NULL) THEN
3028 l_result := g_int_err;
3029 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_DUP_OP_SEQ_NUM');
3030
3031 WRITE_BACK( p_plan_id => l_plan_id,
3032 p_collection_id => p_collection_id,
3033 p_occurrence => p_occurrence,
3034 p_status => l_result,
3035 p_message => l_err_msg);
3036 RETURN;
3037 END IF;
3038
3039 -- Check whehter the Resource Seq Num given as input is not a duplicate one.
3040 -- We cannot accespt duplicate resource seq num for a operation Seq Num.
3041
3042 OPEN res_seq_cur;
3043 FETCH res_seq_cur INTO l_dup_res_seq;
3044 CLOSE res_seq_cur;
3045
3046 IF (l_dup_res_seq IS NOT NULL) AND (p_operation_code IS NULL) THEN
3047 l_result := g_int_err;
3048 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_DUP_RES_SEQ_NUM');
3049
3050 WRITE_BACK( p_plan_id => l_plan_id,
3051 p_collection_id => p_collection_id,
3052 p_occurrence => p_occurrence,
3053 p_status => l_result,
3054 p_message => l_err_msg);
3055 RETURN;
3056
3057 END IF;
3058
3059
3060 -- If Operation Seq Num specified is not a duplicate, we need to add this
3061 -- Operation onto the Job. If Operation Code is specified, then add the specified
3062 -- operation, otherwise just add the Operation Seq Num with Department details.
3063
3064 IF (p_operation_code IS NOT NULL) THEN
3065
3066 -- A value of 1 in l_op_type means, its standard operation.
3067 l_op_type := 1;
3068
3069 OPEN op_cur;
3070 FETCH op_cur INTO l_operation_id;
3071 CLOSE op_cur;
3072
3073 END IF;
3074
3075 IF (p_department_code IS NOT NULL) THEN
3076
3077 OPEN dept_cur;
3078 FETCH dept_cur INTO l_department_id;
3079 CLOSE dept_cur;
3080
3081 END IF;
3082
3083 IF ( l_dup_op_seq IS NULL) THEN
3084
3085 -- Call REWORK_OP_ADD_OP_INT procedure for Adding Operations.
3086 -- It returns the WIP Mass Load Concurrent Request Id launched for
3087 -- processing the Addition of Operation.
3088
3089 l_request := REWORK_OP_ADD_OP_INT(
3090 l_group_id,
3091 p_job_name,
3092 l_wip_entity_id,
3093 p_op_seq_num,
3094 l_operation_id,
3095 l_department_id,
3096 qltdate.any_to_date(p_start_date),
3097 qltdate.any_to_date(p_end_date),
3098 l_organization_id,
3099 l_status_type);
3100
3101
3102 IF (l_request = 0) THEN
3103
3104 -- Concurrent Request not launched
3105 l_result := g_failed;
3106 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
3107
3108 WRITE_BACK( p_plan_id => l_plan_id,
3109 p_collection_id => p_collection_id,
3110 p_occurrence => p_occurrence,
3111 p_status => l_result,
3112 p_message => l_err_msg);
3113
3114 RETURN;
3115
3116 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
3117 -- write back the concurrent request id.
3118
3119 ELSE
3120
3121 WRITE_BACK( p_plan_id => l_plan_id,
3122 p_collection_id => p_collection_id,
3123 p_occurrence => p_occurrence,
3124 p_status => g_pending,
3125 p_request_id => l_request);
3126
3127 END IF;
3128
3129 -- If request gets launched, proceed.
3130 -- But first, wait for the WIP Mass Load Program request to be completed.
3131 -- We wait 100 minutes for the Mass Load to Complete. And we check in
3132 -- every 15 Minutes
3133
3134 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
3135 g_check_request_time,
3136 g_total_request_time,
3137 l_phase,
3138 l_status,
3139 l_devphase,
3140 l_devstatus,
3141 l_message);
3142
3143 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
3144 IF (substr(l_devstatus,1,5) = 'ERROR') THEN
3145
3146 l_result := g_failed;
3147
3148 ELSIF (substr(l_devstatus,1,7) = 'WARNING') THEN
3149
3150 l_result := g_warning;
3151
3152 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
3153
3154 -- Bug 2656461. Commented the 'COMMIT' below. Its not required.
3155
3156 -- Issue a Commit.
3157 -- COMMIT;
3158
3159 l_result := g_success;
3160
3161 -- Bug 2656461. Once the Action is successful, we also need to copy
3162 -- the attachments to the new Operation added.
3163
3164 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
3165 X_from_entity_name => 'QA_RESULTS',
3166 X_from_pk1_value => to_char(p_occurrence),
3167 X_from_pk2_value => to_char(p_collection_id),
3168 X_from_pk3_value => to_char(l_plan_id),
3169 X_to_entity_name => 'WIP_DISCRETE_OPERATIONS',
3170 X_to_pk1_value => to_char(l_wip_entity_id),
3171 X_to_pk2_value => to_char(p_op_seq_num),
3172 X_to_pk3_value => to_char(l_organization_id));
3173
3174
3175 ELSE
3176 l_result := g_failed;
3177
3178 END IF;
3179
3180 -- Call for handshaking the outcome onto the Collection Plan.
3181
3182 WRITE_BACK(p_plan_id => l_plan_id,
3183 p_collection_id => p_collection_id,
3184 p_occurrence => p_occurrence,
3185 p_status => l_result,
3186 p_wjsi_group_id => l_group_id,
3187 p_request_id => l_request
3188 );
3189
3190 -- Return from the API if the WIP Mass Load errors out.
3191 IF (l_result <> g_success) THEN
3192 RETURN;
3193 END IF;
3194
3195 END IF; -- if complete.
3196
3197 END IF; -- l_dup_op_seq IS NULL.
3198
3199 -- Call REWORK_OP_ADD_RES_INT procedure for Adding Resources. It returns the
3200 -- WIP Mass Load Concurrent Request Id launched for processing the Addition
3201 -- of Resource. For Standard Operations. all the resources will be added
3202 -- together. For Non Standard Operation, only one resource will be added at
3203 -- a time.
3204 -- The parameter p_op_type decides internally whether the resources are being
3205 -- added for s Standard Operation (1) or NON standard operation (2).
3206
3207 -- Get the new group_id from the Sequence for Adding Resources.
3208 OPEN group_cur;
3209 FETCH group_cur INTO l_group_id;
3210 CLOSE group_cur;
3211
3212
3213 -- Get the id for Resource.
3214 OPEN res_cur;
3215 FETCH res_cur INTO l_resource_id;
3216 CLOSE res_cur;
3217
3218
3219 l_request := REWORK_OP_ADD_RES_INT(
3220 l_group_id,
3221 p_job_name,
3222 l_wip_entity_id,
3223 p_op_seq_num,
3224 l_operation_id,
3225 l_department_id,
3226 p_res_seq_num,
3227 l_resource_id,
3228 p_assigned_units,
3229 p_usage_rate,
3230 l_organization_id,
3231 l_op_type,
3232 l_status_type
3233 );
3234
3235
3236 IF (l_request = 0) THEN
3237
3238 -- Concurrent Request not launched
3239 l_result := g_failed;
3240 l_err_msg := fnd_message.get_string('QA', 'QA_SODISP_REQ_NOT_LAUNCHED');
3241
3242 WRITE_BACK( p_plan_id => l_plan_id,
3243 p_collection_id => p_collection_id,
3244 p_occurrence => p_occurrence,
3245 p_status => l_result,
3246 p_message => l_err_msg);
3247
3248 RETURN;
3249
3250 -- Bug 2689276. Added the ELSE condition below. If the request gets launched,
3251 -- write back the concurrent request id.
3252
3253 ELSE
3254
3255 WRITE_BACK( p_plan_id => l_plan_id,
3256 p_collection_id => p_collection_id,
3257 p_occurrence => p_occurrence,
3258 p_status => g_pending,
3259 p_request_id => l_request);
3260
3261 END IF;
3262
3263 -- If request gets launched, proceed.
3264 -- But first, wait for the WIP Mass Load Program request to be completed.
3265 -- We wait 100 minutes for the Mass Load to Complete. And we check in
3266 -- every 15 Minutes
3267
3268 l_wait := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request,
3269 g_check_request_time,
3270 g_total_request_time,
3271 l_phase,
3272 l_status,
3273 l_devphase,
3274 l_devstatus,
3275 l_message);
3276
3277 IF (substr(l_devphase,1,8) = 'COMPLETE') THEN
3278 IF (substr(l_devstatus,1,5) = 'ERROR') THEN
3279
3280 l_result := g_failed;
3281
3282 ELSIF (substr(l_devstatus,1,7) = 'WARNING') THEN
3283
3284 l_result := g_warning;
3285
3286 ELSIF (substr(l_devstatus,1,6) = 'NORMAL') THEN
3287
3288 l_result := g_success;
3289
3290 ELSE
3291 l_result := g_failed;
3292
3293 END IF;
3294
3295 -- Call for handshaking the outcome onto the Collection Plan.
3296
3297 WRITE_BACK(p_plan_id => l_plan_id,
3298 p_collection_id => p_collection_id,
3299 p_occurrence => p_occurrence,
3300 p_status => l_result,
3301 p_wjsi_group_id => l_group_id,
3302 p_request_id => l_request
3303 );
3304
3305 END IF; -- if complete.
3306
3307 END REWORK_ADD_OPERATION;
3308
3309
3310 -- Procedure to Add Operations.
3311
3312 FUNCTION REWORK_OP_ADD_OP_INT(
3313 p_group_id NUMBER,
3314 p_job_name VARCHAR2,
3315 p_wip_entity_id NUMBER,
3316 p_op_seq_num NUMBER,
3317 p_operation_id NUMBER,
3318 p_department_id NUMBER,
3319 p_start_date DATE,
3320 p_end_date DATE,
3321 p_organization_id NUMBER,
3322 p_status_type NUMBER)
3323
3324 RETURN NUMBER IS
3325
3326 PRAGMA AUTONOMOUS_TRANSACTION;
3327
3328 l_request_id NUMBER;
3329 l_update_by NUMBER := fnd_global.user_id;
3330 l_update_name VARCHAR2(100);
3331
3332 l_header_id NUMBER;
3333
3334 l_department_id NUMBER;
3335 l_count_point_type NUMBER;
3336 l_backflush_flag NUMBER;
3337 l_min_txfr_qty NUMBER;
3338
3339
3340 CURSOR update_cur IS
3341 SELECT user_name
3342 FROM fnd_user_view
3343 WHERE user_id = l_update_by;
3344
3345 CURSOR op_det IS
3346 SELECT bdp.department_id, nvl(bso.count_point_type, 1) count_point_type,
3347 bso.backflush_flag, nvl(bso.minimum_transfer_quantity, 0) minimum_transfer_quantity
3348 FROM bom_departments bdp, bom_standard_operations bso
3349 WHERE bso.organization_id = p_organization_id
3350 AND bso.line_id is null
3351 AND nvl(bso.operation_type,1) = 1
3352 AND bdp.organization_id = p_organization_id
3353 AND bso.department_id = bdp.department_id
3354 AND nvl(bdp.disable_date, sysdate + 2) > sysdate
3355 AND bso.standard_operation_id = p_operation_id;
3356
3357
3358 BEGIN
3359
3360 OPEN update_cur;
3361 FETCH update_cur INTO l_update_name;
3362 CLOSE update_cur;
3363
3364
3365 IF (p_operation_id IS NOT NULL) THEN
3366 OPEN op_det;
3367 FETCH op_det INTO l_department_id, l_count_point_type, l_backflush_flag, l_min_txfr_qty;
3368 CLOSE op_det;
3369
3370 ELSE
3371 -- Get the default values.
3372 l_department_id := p_department_id;
3373 l_min_txfr_qty := 0;
3374 l_count_point_type := 1;
3375 l_backflush_flag := 2;
3376
3377 END IF;
3378
3379
3380 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
3381 (
3382 SOURCE_CODE,
3383 LAST_UPDATE_DATE,
3384 LAST_UPDATED_BY,
3385 LAST_UPDATED_BY_NAME,
3386 CREATION_DATE,
3387 CREATED_BY,
3388 CREATED_BY_NAME,
3389 GROUP_ID,
3390 ORGANIZATION_ID,
3391 LOAD_TYPE,
3392 STATUS_TYPE,
3393 JOB_NAME,
3394 WIP_ENTITY_ID,
3395 INTERFACE_ID,
3396 PROCESS_PHASE,
3397 PROCESS_STATUS,
3398 HEADER_ID
3399 )
3400 VALUES
3401 (
3402 'QA_ACTION: ADD_OP',
3403 sysdate,
3404 l_update_by,
3405 l_update_name,
3406 sysdate,
3407 l_update_by,
3408 l_update_name,
3409 p_group_id,
3410 p_organization_id,
3411 3,
3412 p_status_type,
3413 p_job_name,
3414 p_wip_entity_id,
3415 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3416 2,
3417 1,
3418 WIP_JOB_SCHEDULE_INTERFACE_S.nextval
3419 ) returning header_id into l_header_id;
3420
3421
3422 INSERT INTO WIP_JOB_DTLS_INTERFACE
3423 (
3424 INTERFACE_ID,
3425 GROUP_ID,
3426 WIP_ENTITY_ID,
3427 ORGANIZATION_ID,
3428 OPERATION_SEQ_NUM,
3429 DEPARTMENT_ID,
3430 LOAD_TYPE,
3431 SUBSTITUTION_TYPE,
3432 PROCESS_PHASE,
3433 PROCESS_STATUS,
3434 LAST_UPDATE_DATE,
3435 LAST_UPDATED_BY,
3436 CREATION_DATE,
3437 CREATED_BY,
3438 PARENT_HEADER_ID,
3439 STANDARD_OPERATION_ID,
3440 FIRST_UNIT_START_DATE,
3441 FIRST_UNIT_COMPLETION_DATE,
3442 LAST_UNIT_START_DATE,
3443 LAST_UNIT_COMPLETION_DATE,
3444 MINIMUM_TRANSFER_QUANTITY,
3445 BACKFLUSH_FLAG,
3446 COUNT_POINT_TYPE
3447 )
3448 VALUES
3449 (
3450 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3451 p_group_id,
3452 p_wip_entity_id,
3453 p_organization_id,
3454 p_op_seq_num,
3455 l_department_id,
3456 3, -- ( 3- Load Operation) LOAD_TYPE
3457 2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
3458 2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
3459 1, -- ( 1- wip_constants.pending) PROCESS_STATUS
3460 sysdate,
3461 l_update_by,
3462 sysdate,
3463 l_update_by,
3464 l_header_id,
3465 p_operation_id,
3466 p_start_date,
3467 p_start_date,
3468 p_end_date,
3469 p_end_date,
3470 l_min_txfr_qty,
3471 l_backflush_flag,
3472 l_count_point_type
3473 );
3474
3475
3476
3477 -- Call the WIP Mass Load Program in Background.
3478 l_request_id := FND_REQUEST.SUBMIT_REQUEST('WIP', 'WICMLP',
3479 NULL, NULL, FALSE,
3480 TO_CHAR(p_group_id), -- grp id
3481 TO_CHAR(WIP_CONSTANTS.FULL), -- validation lvl
3482 TO_CHAR(WIP_CONSTANTS.YES)); -- print report
3483
3484 -- Commit the insert
3485 COMMIT;
3486
3487 RETURN l_request_id;
3488
3489 END REWORK_OP_ADD_OP_INT;
3490
3491
3492 -- addres
3493
3494 FUNCTION REWORK_OP_ADD_RES_INT(
3495 p_group_id NUMBER,
3496 p_job_name VARCHAR2,
3497 p_wip_entity_id NUMBER,
3498 p_op_seq_num NUMBER,
3499 p_operation_id NUMBER,
3500 p_department_id NUMBER,
3501 p_res_seq_num NUMBER,
3502 p_resource_id NUMBER,
3503 p_assigned_units NUMBER,
3504 p_usage_rate NUMBER,
3505 p_organization_id NUMBER,
3506 p_op_type NUMBER,
3507 p_status_type NUMBER)
3508
3509 RETURN NUMBER IS
3510
3511 PRAGMA AUTONOMOUS_TRANSACTION;
3512
3513 l_request_id NUMBER;
3514 l_update_by NUMBER := fnd_global.user_id;
3515 l_update_name VARCHAR2(100);
3516
3517 l_header_id NUMBER;
3518
3519 l_res_seq_num NUMBER;
3520 l_resource_id NUMBER;
3521 l_activity_id NUMBER;
3522 l_assigned_units NUMBER;
3523 l_basis_type NUMBER;
3524 l_schedule_flag NUMBER;
3525 l_std_rate_flag NUMBER;
3526 l_usage_rate NUMBER;
3527 l_autocharge_type NUMBER;
3528 l_uom VARCHAR2(3);
3529
3530
3531 CURSOR update_cur IS
3532 SELECT user_name
3533 FROM fnd_user_view
3534 WHERE user_id = l_update_by;
3535
3536
3537 CURSOR res_det IS
3538 SELECT bsor.resource_seq_num, bsor.resource_id, bsor.activity_id,
3539 bsor.assigned_units, bsor.basis_type, bsor.schedule_flag,
3540 bsor.standard_rate_flag, bsor.usage_rate_or_amount,
3541 bsor.autocharge_type, br.unit_of_measure
3542 FROM bom_std_op_resources bsor, bom_resources br
3543 WHERE bsor.resource_id = br.resource_id
3544 AND bsor.standard_operation_id = p_operation_id;
3545
3546
3547 CURSOR m_res_det IS
3548 SELECT res.unit_of_measure, nvl(res.default_basis_type,1) basis_type,
3549 2 "scheduled_flag", res.default_activity_id,
3550 nvl(res.autocharge_type, 1) autocharge_type,
3551 nvl(res.standard_rate_flag,1) standard_rate_flag
3552 FROM bom_resources res, bom_department_resources bdr
3553 WHERE res.organization_id = p_organization_id
3554 AND nvl(res.disable_date, sysdate + 2) > sysdate
3555 AND res.resource_id = bdr.resource_id
3556 AND bdr.department_id = p_department_id
3557 AND res.resource_id = p_resource_id;
3558
3559 BEGIN
3560
3561
3562 OPEN update_cur;
3563 FETCH update_cur INTO l_update_name;
3564 CLOSE update_cur;
3565
3566 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
3567 (
3568 SOURCE_CODE,
3569 LAST_UPDATE_DATE,
3570 LAST_UPDATED_BY,
3571 LAST_UPDATED_BY_NAME,
3572 CREATION_DATE,
3573 CREATED_BY,
3574 CREATED_BY_NAME,
3575 GROUP_ID,
3576 ORGANIZATION_ID,
3577 LOAD_TYPE,
3578 STATUS_TYPE,
3579 JOB_NAME,
3580 WIP_ENTITY_ID,
3581 INTERFACE_ID,
3582 PROCESS_PHASE,
3583 PROCESS_STATUS,
3584 HEADER_ID
3585 )
3586 VALUES
3587 (
3588 'QA_ACTION: ADD_RES',
3589 sysdate,
3590 l_update_by,
3591 l_update_name,
3592 sysdate,
3593 l_update_by,
3594 l_update_name,
3595 p_group_id,
3596 p_organization_id,
3597 3,
3598 p_status_type,
3599 p_job_name,
3600 p_wip_entity_id,
3601 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3602 2,
3603 1,
3604 WIP_JOB_SCHEDULE_INTERFACE_S.nextval
3605 ) returning header_id into l_header_id;
3606
3607
3608 IF (p_op_type = 2) THEN
3609
3610 OPEN m_res_det;
3611 FETCH m_res_det INTO l_uom, l_basis_type, l_schedule_flag, l_activity_id,
3612 l_autocharge_type, l_std_rate_flag;
3613 CLOSE m_res_det;
3614
3615 INSERT INTO WIP_JOB_DTLS_INTERFACE
3616 (
3617 INTERFACE_ID,
3618 GROUP_ID,
3619 ORGANIZATION_ID,
3620 OPERATION_SEQ_NUM,
3621 LOAD_TYPE,
3622 SUBSTITUTION_TYPE,
3623 PROCESS_PHASE,
3624 PROCESS_STATUS,
3625 LAST_UPDATE_DATE,
3626 LAST_UPDATED_BY,
3627 CREATION_DATE,
3628 CREATED_BY,
3629 PARENT_HEADER_ID,
3630 ACTIVITY_ID,
3631 ASSIGNED_UNITS,
3632 AUTOCHARGE_TYPE,
3633 BASIS_TYPE,
3634 RESOURCE_ID_NEW,
3635 RESOURCE_SEQ_NUM,
3636 SCHEDULED_FLAG,
3637 STANDARD_RATE_FLAG,
3638 USAGE_RATE_OR_AMOUNT,
3639 UOM_CODE
3640 )
3641 VALUES
3642 (
3643 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3644 p_group_id,
3645 p_organization_id,
3646 p_op_seq_num,
3647 1, -- ( 1- Load Resource) LOAD_TYPE
3648 2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
3649 2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
3650 1, -- ( 1- wip_constants.pending) PROCESS_STATUS
3651 sysdate,
3652 l_update_by,
3653 sysdate,
3654 l_update_by,
3655 l_header_id,
3656 l_activity_id,
3657 p_assigned_units,
3658 l_autocharge_type,
3659 l_basis_type,
3660 p_resource_id,
3661 p_res_seq_num,
3662 l_schedule_flag,
3663 l_std_rate_flag,
3664 p_usage_rate,
3665 l_uom
3666 );
3667
3668
3669 ELSIF (p_op_type = 1) THEN
3670
3671 OPEN res_det;
3672 LOOP
3673 FETCH res_det INTO l_res_seq_num, l_resource_id, l_activity_id,
3674 l_assigned_units, l_basis_type, l_schedule_flag,
3675 l_std_rate_flag, l_usage_rate, l_autocharge_type, l_uom;
3676
3677 EXIT WHEN res_det%NOTFOUND;
3678
3679
3680 INSERT INTO WIP_JOB_DTLS_INTERFACE
3681 (
3682 INTERFACE_ID,
3683 GROUP_ID,
3684 ORGANIZATION_ID,
3685 OPERATION_SEQ_NUM,
3686 LOAD_TYPE,
3687 SUBSTITUTION_TYPE,
3688 PROCESS_PHASE,
3689 PROCESS_STATUS,
3690 LAST_UPDATE_DATE,
3691 LAST_UPDATED_BY,
3692 CREATION_DATE,
3693 CREATED_BY,
3694 PARENT_HEADER_ID,
3695 ACTIVITY_ID,
3696 ASSIGNED_UNITS,
3697 AUTOCHARGE_TYPE,
3698 BASIS_TYPE,
3699 RESOURCE_ID_NEW,
3700 RESOURCE_SEQ_NUM,
3701 SCHEDULED_FLAG,
3702 STANDARD_RATE_FLAG,
3703 USAGE_RATE_OR_AMOUNT,
3704 UOM_CODE
3705 )
3706 VALUES
3707 (
3708 WIP_JOB_SCHEDULE_INTERFACE_S.nextval,
3709 p_group_id,
3710 p_organization_id,
3711 p_op_seq_num,
3712 1, -- ( 1- Load Resource) LOAD_TYPE
3713 2, -- ( 2- Add wip_job_details.wip_add) SUBSTITUTION_TYPE
3714 2, -- ( 2- wip_constants.ml_validation) PROCESS_PHASE
3715 1, -- ( 1- wip_constants.pending) PROCESS_STATUS
3716 sysdate,
3717 l_update_by,
3718 sysdate,
3719 l_update_by,
3720 l_header_id,
3721 l_activity_id,
3722 l_assigned_units,
3723 l_autocharge_type,
3724 l_basis_type,
3725 l_resource_id,
3726 l_res_seq_num,
3727 l_schedule_flag,
3728 l_std_rate_flag,
3729 l_usage_rate,
3730 l_uom
3731 );
3732
3733 END LOOP;
3734 CLOSE res_det;
3735
3736 END IF;
3737
3738 -- Call the WIP Mass Load Program in Background.
3739 l_request_id := FND_REQUEST.SUBMIT_REQUEST('WIP', 'WICMLP',
3740 NULL, NULL, FALSE,
3741 TO_CHAR(p_group_id), -- grp id
3742 TO_CHAR(WIP_CONSTANTS.FULL), -- validation lvl
3743 TO_CHAR(WIP_CONSTANTS.YES)); -- print report
3744
3745 -- Commit the insert
3746 COMMIT;
3747
3748 RETURN l_request_id;
3749
3750 END REWORK_OP_ADD_RES_INT;
3751
3752
3753
3754
3755 PROCEDURE UPDATE_STATUS(p_plan_id IN NUMBER,
3756 p_collection_id IN NUMBER,
3757 p_occurrence IN NUMBER) IS
3758
3759 PRAGMA AUTONOMOUS_TRANSACTION;
3760
3761 l_txnheader_id NUMBER;
3762
3763 BEGIN
3764
3765 -- Update the Dispostion_Status to 'Pending'.
3766
3767 UPDATE qa_results
3768 SET disposition_status = 'PENDING',
3769 txn_header_id = mtl_material_transactions_s.nextval
3770 WHERE collection_id = p_collection_id AND occurrence = p_occurrence
3771 RETURNING txn_header_id INTO l_txnheader_id;
3772
3773 -- Calling parent-child pkg to insert History child record for the plan.
3774 QA_PARENT_CHILD_PKG.insert_history_auto_rec(p_plan_id,l_txnheader_id, 1, 4);
3775
3776 -- Commit now.
3777 COMMIT;
3778
3779 END UPDATE_STATUS;
3780
3781
3782 PROCEDURE WRITE_BACK(p_plan_id IN NUMBER,
3783 p_collection_id IN NUMBER,
3784 p_occurrence IN NUMBER,
3785 p_status IN VARCHAR2,
3786 p_mti_transaction_header_id IN NUMBER,
3787 p_mti_transaction_interface_id IN NUMBER,
3788 p_mmt_transaction_id IN NUMBER,
3789 p_wmti_group_id IN NUMBER,
3790 p_wmt_transaction_id IN NUMBER,
3791 p_rti_interface_transaction_id IN NUMBER,
3792 p_job_id IN NUMBER,
3793 p_wjsi_group_id IN NUMBER,
3794 p_request_id IN NUMBER,
3795 p_message IN VARCHAR2,
3796 p_move_order_number IN VARCHAR2,
3797 p_eco_name IN VARCHAR2) IS
3798
3799 PRAGMA AUTONOMOUS_TRANSACTION;
3800
3801 l_action_fired_column VARCHAR2(30);
3802 l_disp_message_column VARCHAR2(30);
3803 l_move_order_column VARCHAR2(30);
3804
3805 l_sql_string VARCHAR2(10000);
3806 l_action_fired VARCHAR2(250);
3807
3808 -- Find out correct element char_id and assign to the below constant
3809
3810 l_move_order_char_id CONSTANT NUMBER := qa_ss_const.move_order_number;
3811 l_action_fired_char_id CONSTANT NUMBER := qa_ss_const.action_fired;
3812 l_disp_message_char_id CONSTANT NUMBER := qa_ss_const.disposition_message;
3813
3814 l_txnheader_id NUMBER;
3815
3816 -- Bug 2698365. The below cursor is no longer required.
3817 -- We are returning the sequence value from the update statement.
3818 -- kabalakr.
3819
3820 /*
3821 CURSOR txn_head_seq IS
3822 SELECT mtl_material_transactions_s.nextval
3823 FROM DUAL;
3824 */
3825
3826 -- We are not translating the Action_Fired value.
3827 -- Hence commenting the below cursor.
3828 -- Bug 2595276. kabalakr
3829
3830 /*
3831 CURSOR lookup_cur(l_lookup_code NUMBER) IS
3832 SELECT substr(ltrim(rtrim(meaning)),1,20)
3833 FROM mfg_lookups
3834 WHERE lookup_type = 'SYS_YES_NO'
3835 AND lookup_code = l_lookup_code ;
3836 */
3837
3838 BEGIN
3839
3840
3841 IF p_status = 'INT_ERROR' THEN
3842 -- When internal error occurs, we need to set the action_fired to 'No'
3843 -- so that user can refire the action form UQR
3844
3845 -- OPEN lookup_cur(g_lookup_no);
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 := 'No' ;
3856 l_action_fired := get_short_code(p_plan_id, l_action_fired_char_id, 'NO');
3857
3858 ELSE
3859 -- all other cases, set action_fired to 'Yes'
3860
3861 -- OPEN lookup_cur(g_lookup_yes);
3862
3863 -- We are not translating the Action_Fired value. Commented the
3864 -- above line of code. Assigning the hardcoded string below.
3865 -- Bug 2595276. kabalakr.
3866
3867 -- anagarwa Fri Jul 2 16:30:00 PDT 2004
3868 -- bug 3736593 action fired element cannot be validated.
3869 -- following function looks for values in qa_plan_char_value_lookups and
3870 -- if not found, it executes the sql validation string
3871 --l_action_fired := 'Yes' ;
3872 l_action_fired := get_short_code(p_plan_id, l_action_fired_char_id, 'YES');
3873
3874
3875 END IF;
3876
3877 -- We are not translating the Action_Fired value. Commenting the
3878 -- code below. Bug 2595276. kabalakr.
3879
3880 -- FETCH lookup_cur INTO l_action_fired;
3881 -- CLOSE lookup_cur;
3882
3883 -- Needs to get the result column names of the Seeded handshaking
3884 -- Collection elements.
3885
3886 l_action_fired_column := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, l_action_fired_char_id);
3887 l_disp_message_column := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, l_disp_message_char_id);
3888
3889 -- Bug 2698365. The cursor txn_head_seq is no longer required. Hence commenting
3890 -- the piece of code below. We are returning the value of txn_header_id from the
3891 -- update statement. kabalakr.
3892 /*
3893 OPEN txn_head_seq;
3894 FETCH txn_head_seq INTO l_txnheader_id;
3895 CLOSE txn_head_seq;
3896 */
3897
3898 -- Added the eco_name column to support 'Create ECO' corrective action.
3899
3900 -- Bug 2689305. Added NVL () for all the parameter variables used below
3901 -- which had a default value of NULL. kabalakr.
3902
3903 -- Bug 2698365. Added txn_header_id in the update statement below and
3904 -- returning its value to the variable l_txnheader_id. kabalakr.
3905
3906 UPDATE qa_results
3907 SET disposition_status = p_status,
3908 wip_rework_id = NVL(p_job_id, 0),
3909 wjsi_group_id = NVL(p_wjsi_group_id, 0),
3910 mti_transaction_header_id = NVL(p_mti_transaction_header_id, 0),
3911 mti_transaction_interface_id = NVL(p_mti_transaction_interface_id, 0),
3912 mmt_transaction_id = NVL(p_mmt_transaction_id, 0),
3913 wmti_group_id = NVL(p_wmti_group_id, 0),
3914 wmt_transaction_id = NVL(p_wmt_transaction_id, 0),
3915 rti_interface_transaction_id = NVL(p_rti_interface_transaction_id, 0),
3916 concurrent_request_id = NVL(p_request_id, 0),
3917 eco_name = p_eco_name,
3918 txn_header_id = mtl_material_transactions_s.nextval
3919 WHERE collection_id = p_collection_id
3920 AND occurrence = p_occurrence
3921 RETURNING txn_header_id INTO l_txnheader_id;
3922
3923
3924 -- Bug 2935558/2941809. Need to use bind variables instead of literal values when
3925 -- using EXECUTE IMMEDIATE. This is for the SQL Bind Compliance Project.
3926 -- kabalakr
3927
3928 IF p_move_order_number IS NOT NULL THEN
3929 l_move_order_column := QA_FLEX_UTIL.qpc_result_column_name(p_plan_id, l_move_order_char_id);
3930
3931 l_sql_string := 'UPDATE qa_results SET '||
3932 l_move_order_column ||' = :move_order_number, ' ||
3933 l_action_fired_column||' = :action_fired, '||
3934 l_disp_message_column||' = :message '||
3935 ' WHERE collection_id = :coll_id AND occurrence = :occ';
3936
3937 EXECUTE IMMEDIATE l_sql_string USING p_move_order_number, l_action_fired, p_message, p_collection_id, p_occurrence;
3938
3939 ELSE
3940
3941
3942 l_sql_string := 'UPDATE qa_results SET ' ||
3943 l_action_fired_column||' = :action_fired, '||
3944 l_disp_message_column||' = :message '||
3945 ' WHERE collection_id = :coll_id AND occurrence = :occ';
3946
3947 EXECUTE IMMEDIATE l_sql_string USING l_action_fired, p_message, p_collection_id, p_occurrence;
3948
3949 END IF;
3950
3951
3952 -- Calling parent-child pkg to insert History child record for the plan.
3953 -- action firing for child rec is taken care in the parent-child pkg.
3954 QA_PARENT_CHILD_PKG.insert_history_auto_rec(p_plan_id, l_txnheader_id, 1, 4);
3955
3956 -- We need to fire action for the parent record only.
3957 QA_PARENT_CHILD_PKG.enable_fire_for_txn_hdr_id(l_txnheader_id);
3958
3959 -- Commit Now.
3960 COMMIT;
3961
3962 END WRITE_BACK;
3963
3964 FUNCTION get_mfg_lookups_value (p_meaning VARCHAR2,
3965 p_lookup_type VARCHAR2)
3966 RETURN NUMBER IS
3967
3968 l_lookup_code VARCHAR2(2);
3969
3970 CURSOR meaning_cur IS
3971 SELECT lookup_code
3972 FROM mfg_lookups
3973 WHERE lookup_type = p_lookup_type
3974 AND upper(meaning) = upper(ltrim(rtrim(p_meaning)));
3975
3976 BEGIN
3977
3978 OPEN meaning_cur;
3979 FETCH meaning_cur INTO l_lookup_code;
3980 CLOSE meaning_cur;
3981
3982 RETURN l_lookup_code;
3983
3984 END get_mfg_lookups_value;
3985
3986 FUNCTION get_plan_id(p_plan_name VARCHAR2)
3987 RETURN NUMBER IS
3988
3989 l_plan_id NUMBER := -1;
3990
3991 CURSOR plan_cur IS
3992 SELECT plan_id
3993 FROM QA_PLANS
3994 WHERE name = p_plan_name;
3995
3996 BEGIN
3997 OPEN plan_cur;
3998 FETCH plan_cur INTO l_plan_id;
3999 CLOSE plan_cur;
4000
4001 RETURN l_plan_id;
4002
4003 EXCEPTION
4004 when NO_DATA_FOUND then
4005 RETURN -1;
4006 when OTHERS then
4007 RAISE;
4008
4009 END get_plan_id;
4010
4011
4012 FUNCTION get_organization_id (p_organization_code VARCHAR2)
4013 RETURN NUMBER IS
4014
4015 l_organization_id NUMBER := -1;
4016
4017 -- Bug 4958743. SQL Repository Fix SQL ID: 15008948
4018 CURSOR org_cur IS
4019 SELECT organization_id
4020 FROM mtl_parameters
4021 WHERE organization_code = upper(p_organization_code);
4022 /*
4023 SELECT organization_id
4024 FROM org_organization_definitions
4025 WHERE organization_code = p_organization_code;
4026 */
4027
4028 BEGIN
4029
4030 OPEN org_cur;
4031 FETCH org_cur INTO l_organization_id;
4032 CLOSE org_cur;
4033
4034 RETURN l_organization_id;
4035
4036 EXCEPTION
4037
4038 when NO_DATA_FOUND then
4039 RETURN -1;
4040 when OTHERS then
4041 RAISE;
4042
4043 END get_organization_id;
4044
4045 -- anagarwa Fri Jul 2 16:30:00 PDT 2004
4046 -- bug 3736593 action fired element cannot be validated.
4047 -- following function looks for values in qa_plan_char_value_lookups and
4048 -- if not found, it executes the sql validation string
4049 FUNCTION get_short_code(p_plan_id NUMBER,
4050 p_char_id NUMBER,
4051 p_short_code IN VARCHAR2)
4052 RETURN VARCHAR2 IS
4053
4054 l_return_value VARCHAR2(250);
4055 x_ref LookupCur;
4056 sql_string VARCHAR2(3000);
4057
4058 CURSOR c IS
4059 SELECT short_code
4060 FROM qa_plan_char_value_lookups
4061 WHERE plan_id = p_plan_id
4062 AND char_id = p_char_id
4063 AND upper(short_code) = upper(p_short_code);
4064
4065 BEGIN
4066
4067 IF qa_plan_element_api.values_exist(p_plan_id, p_char_id) THEN
4068
4069 OPEN c;
4070 FETCH c INTO l_return_value;
4071 CLOSE c;
4072
4073 ELSIF qa_plan_element_api.sql_validation_exists(p_char_id) THEN
4074
4075 sql_string := qa_plan_element_api.get_sql_validation_string(p_char_id);
4076
4077 --
4078 -- Bug 1474995. Adding filter to the user-defined SQL.
4079 --
4080 sql_string :=
4081 'select code
4082 from
4083 (select ''x'' code, ''x'' description
4084 from dual
4085 where 1 = 2
4086 union
4087 select * from
4088 ( '|| sql_string ||
4089 ' )) where upper(code) = upper(:1)';
4090
4091 OPEN x_ref FOR sql_string USING p_short_code;
4092 FETCH x_ref INTO l_return_value;
4093 CLOSE x_ref;
4094 ELSE
4095 l_return_value := p_short_code;
4096 END IF;
4097
4098 RETURN l_return_value;
4099 END;
4100
4101 END QA_SOLUTION_DISPOSITION_PKG;
4102
[Home] [Help]