1 PACKAGE BODY QLTDACTB as
2 /* $Header: qltdactb.plb 120.13.12010000.3 2008/11/12 10:02:51 ntungare ship $ */
3 -- 2/8/95 - created
4 -- Kevin Wiggen
5
6
7 PROCEDURE INSERT_ACTION_LOG(X_PLAN_ID NUMBER,
8 X_COLLECTION_ID NUMBER,
9 X_CREATION_DATE DATE,
10 X_CHAR_ID NUMBER,
11 X_OPERATOR NUMBER,
12 X_LOW_VALUE VARCHAR2,
13 X_HIGH_VALUE VARCHAR2,
14 X_MESSAGE VARCHAR2,
15 X_RESULT VARCHAR2,
16 X_CONCURRENT NUMBER) IS
17
18
19 user_id NUMBER;
20 X_REQUEST_ID NUMBER;
21 X_PROGRAM_APPLICATION_ID NUMBER;
22 X_PROGRAM_ID NUMBER;
23 X_LAST_UPDATE_LOGIN NUMBER;
24
25 BEGIN
26
27 user_id := NVL(FND_PROFILE.VALUE('USER_ID'), 0);
28
29 if X_CONCURRENT = 1 then -- Online
30
31 X_REQUEST_ID := FND_PROFILE.VALUE('REQUEST_ID');
32 X_PROGRAM_APPLICATION_ID :=
33 FND_PROFILE.VALUE('CONC_PROGRAM_APPLICATION_ID');
34 X_PROGRAM_ID := FND_PROFILE.VALUE('CONC_PROGRAM_ID');
35 X_LAST_UPDATE_LOGIN := FND_PROFILE.VALUE('CONC_LOGIN_ID');
36
37 insert into qa_action_log (LOG_ID,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 CREATION_DATE,
41 CREATED_BY,
42 LAST_UPDATE_LOGIN,
43 REQUEST_ID,
44 PROGRAM_APPLICATION_ID,
45 PROGRAM_ID,
46 PROGRAM_UPDATE_DATE,
47 PLAN_ID,
48 COLLECTION_ID,
49 TRANSACTION_DATE,
50 CHAR_ID,
51 OPERATOR,
52 LOW_VALUE,
53 HIGH_VALUE,
54 ACTION_LOG_MESSAGE,
55 RESULT_VALUE)
56 values (qa_action_log_s.NEXTVAL,
57 SYSDATE,
58 user_id,
59 SYSDATE,
60 user_id,
61 X_LAST_UPDATE_LOGIN,
62 X_REQUEST_ID,
63 X_PROGRAM_APPLICATION_ID,
64 X_PROGRAM_ID,
65 SYSDATE,
66 X_PLAN_ID,
67 X_COLLECTION_ID,
68 X_CREATION_DATE,
69 X_CHAR_ID,
70 X_OPERATOR,
71 X_LOW_VALUE,
72 X_HIGH_VALUE,
73 X_MESSAGE,
74 X_RESULT);
75
76 else
77
78 insert into qa_action_log (LOG_ID,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 CREATION_DATE,
82 CREATED_BY,
83 PLAN_ID,
84 COLLECTION_ID,
85 TRANSACTION_DATE,
86 CHAR_ID,
87 OPERATOR,
88 LOW_VALUE,
89 HIGH_VALUE,
90 ACTION_LOG_MESSAGE,
91 RESULT_VALUE)
92 values (qa_action_log_s.NEXTVAL,
93 SYSDATE,
94 user_id,
95 SYSDATE,
96 user_id,
97 X_PLAN_ID,
98 X_COLLECTION_ID,
99 X_CREATION_DATE,
100 X_CHAR_ID,
101 X_OPERATOR,
102 X_LOW_VALUE,
103 X_HIGH_VALUE,
104 X_MESSAGE,
105 X_RESULT);
106 end if;
107
108 end INSERT_ACTION_LOG;
109
110
111 -- Added X_ARGUMENT in the signature of DO_ACTIONS.
112 -- X_ARGUMENT will get the value IMPORT from qltactwb.plb
113 -- if collection import is used.In other cases it will be NULL.
114 -- Bug 3273447. suramasw
115
116 FUNCTION DO_ACTIONS(X_TXN_HEADER_ID NUMBER,
117 X_CONCURRENT NUMBER , -- DEFAULT NULL
118 X_PO_TXN_PROCESSOR_MODE VARCHAR2 , -- DEFAULT NULL
119 X_GROUP_ID NUMBER , -- DEFAULT NULL
120 X_BACKGROUND BOOLEAN , -- DEFAULT NULL
121 X_DEBUG BOOLEAN , -- DEFAULT NULL
122 X_ACTION_TYPE VARCHAR2, -- DEFAULT NULL
123 X_PASSED_ID_NAME VARCHAR2 , -- DEFAULT NULL
124 P_OCCURRENCE NUMBER , -- DEFAULT NULL
125 P_PLAN_ID NUMBER, -- DEFAULT NULL
126 X_ARGUMENT VARCHAR2) -- DEFAULT NULL
127
128 RETURN BOOLEAN IS
129
130 TYPE numtable IS TABLE OF NUMBER
131 INDEX BY BINARY_INTEGER;
132 TYPE char30table IS TABLE OF VARCHAR2(30)
133 INDEX BY BINARY_INTEGER;
134 TYPE char150table IS TABLE OF VARCHAR2(150)
135 INDEX BY BINARY_INTEGER;
136
137 char_id_tab numtable;
138 datatype_tab numtable;
139 fk_type_tab numtable;
140 column_tab char30table;
141 operator_tab numtable;
142 low_value_tab char150table;
143 high_value_tab char150table;
144 action_id_tab numtable;
145 pcat_id_tab numtable;
146 pca_id_tab numtable;
147 seq_id_tab numtable;
148
149 --
150 -- See Bug 2624112
151 --
152 -- Modified the query for Global Specifications Enhancements
153 --
154 -- Added new table to FROM list and modified the WHERE clause
155 --
156 -- rkunchal
157 --
158
159 -- bug 3402856. rkaza. 01/27/2004.
160 -- Same problem as in bug 2767550. Making the same fix here.
161 -- Problem introduced by incorrect join conditions of global spec stuff.
162
163 CURSOR action_triggers(X_PLAN_ID NUMBER,
164 X_SPEC_ID NUMBER) is
165 SELECT qpc.char_id CHAR_ID,
166 qc.datatype TYPE,
167 qc.fk_lookup_type FK_LOOKUP_TYPE,
168 NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
169 qpcat.operator OPERATOR,
170 decode(X_SPEC_ID, 0,
171 decode(qpcat.low_value_lookup,
172 7,qc.lower_reasonable_limit,
173 6,qc.lower_spec_limit,
174 5,qc.lower_user_defined_limit,
175 4,qc.target_value,
176 3,qc.upper_user_defined_limit,
177 2,qc.upper_spec_limit,
178 1,qc.upper_reasonable_limit,
179 NULL,qpcat.low_value_other),
180 decode(qpcat.low_value_lookup,
181 7,qscqs.lower_reasonable_limit,
182 6,qscqs.lower_spec_limit,
183 5,qscqs.lower_user_defined_limit,
184 4,qscqs.target_value,
185 3,qscqs.upper_user_defined_limit,
186 2,qscqs.upper_spec_limit,
187 1,qscqs.upper_reasonable_limit,
188 NULL,qpcat.low_value_other)) LOW_VALUE,
189 decode(X_SPEC_ID, 0,
190 decode(qpcat.high_value_lookup,
191 7,qc.lower_reasonable_limit,
192 6,qc.lower_spec_limit,
193 5,qc.lower_user_defined_limit,
194 4,qc.target_value,
195 3,qc.upper_user_defined_limit,
196 2,qc.upper_spec_limit,
197 1,qc.upper_reasonable_limit,
198 NULL,qpcat.high_value_other),
199 decode(qpcat.high_value_lookup,
200 7,qscqs.lower_reasonable_limit,
201 6,qscqs.lower_spec_limit,
202 5,qscqs.lower_user_defined_limit,
203 4,qscqs.target_value,
204 3,qscqs.upper_user_defined_limit,
205 2,qscqs.upper_spec_limit,
206 1,qscqs.upper_reasonable_limit,
207 NULL,qpcat.high_value_other)) HIGH_VALUE,
208 qpca.action_id ACTION,
209 qpca.plan_char_action_id PCA_ID,
210 qpcat.trigger_sequence SEQ_ID,
211 qpcat.plan_char_action_trigger_id PCAT_ID,
212 nvl(qscqs.uom_code, qc.uom_code) SPEC_CHAR_UOM,
213 nvl(qpc.uom_code, qc.uom_code) PLAN_CHAR_UOM,
214 qpc.decimal_precision DECIMAL_PRECISION
215 FROM qa_chars qc,
216 qa_plan_chars qpc,
217 qa_plan_char_action_triggers qpcat,
218 qa_plan_char_actions qpca,
219 qa_actions qa,
220 (select
221 qsc.CHAR_ID,
222 qsc.ENABLED_FLAG,
223 qsc.TARGET_VALUE,
224 qsc.UPPER_SPEC_LIMIT,
225 qsc.LOWER_SPEC_LIMIT,
226 qsc.UPPER_REASONABLE_LIMIT,
227 qsc.LOWER_REASONABLE_LIMIT,
228 qsc.UPPER_USER_DEFINED_LIMIT,
229 qsc.LOWER_USER_DEFINED_LIMIT,
230 qsc.UOM_CODE
231 from
232 qa_spec_chars qsc,
233 qa_specs qs
234 where
235 qsc.spec_id = qs.common_spec_id and
236 qs.spec_id = X_SPEC_ID
237 ) qscqs
238 WHERE qpc.plan_id = X_PLAN_ID
239 and qpc.char_id = qc.char_id
240 and qc.char_id = qscqs.char_id (+)
241 and qpcat.plan_id (+) = X_PLAN_ID
242 and qpcat.char_id (+) = qpc.char_id
243 and qpca.plan_char_action_trigger_id (+) =
244 qpcat.plan_char_action_trigger_id
245 and qpca.action_id = qa.action_id
246 and (qa.online_flag = 2 or qa.action_id = 24)
247 and qa.enabled_flag = 1
248 ORDER BY qpc.prompt_sequence, qpcat.trigger_sequence,
249 qpcat.plan_char_action_trigger_id;
250
251 Cursor MY_MESSAGE(X_PCA_ID NUMBER) is
252 select MESSAGE, ASSIGN_TYPE from qa_plan_char_actions
253 where PLAN_CHAR_ACTION_ID = X_PCA_ID;
254
255 Cursor WORKFLOW_itemtype(X_PCA_ID VARCHAR2) is
256 select MESSAGE from qa_plan_char_actions
257 where PLAN_CHAR_ACTION_ID = X_PCA_ID;
258
259 Cursor WF_ITEMTYPE_SELECTOR(WORKFLOW_ITEMTYPE VARCHAR2) IS
260 select wf_selector
261 from wf_item_types
262 where name = WORKFLOW_ITEMTYPE;
263
264 Cursor WF_NUMBER_OF_PROCESSES(WORKFLOW_ITEMTYPE VARCHAR2) IS
265 select count (*)
266 from wf_runnable_processes_v
267 where item_type = WORKFLOW_ITEMTYPE;
268
269 Cursor MY_STATUS(X_PCA_ID NUMBER) is
270 select STATUS_CODE from qa_plan_char_actions
271 where PLAN_CHAR_ACTION_ID = X_PCA_ID;
272
273
274 -- Bug 5196076. SQL Repository Fix SQL ID 17898864.
275 -- Removed usage of inv_organization_info_v and replaced with
276 -- call to base table hr_organization_information
277 -- to improve performance.
278 Cursor MY_OP_UNIT ( ORG_ID NUMBER ) is
279 SELECT to_number(org_information3)
280 FROM hr_organization_information
281 WHERE organization_id = ORG_ID
282 AND org_information_context = 'Accounting Information';
283
284 /*
285 Bug 4958762: SQL Repository Fix SQL ID: 15008200
286 Cursor MY_OP_UNIT ( ORG_ID NUMBER ) is
287 SELECT
288 operating_unit
289 FROM inv_organization_info_v
290 WHERE organization_id = ORG_ID ;
291 */
292
293 /*
294 Select Operating_Unit from org_organization_definitions
295 Where Organization_id = ORG_ID ;
296 */
297 -- Bug 4958762: SQL Repository Fix SQL ID: 15008214
298 Cursor MY_STEP(X_PLAN_ID NUMBER) is
299 SELECT
300 result_column_name
301 FROM qa_plan_chars
302 WHERE plan_id = x_plan_id
303 AND char_id = 23;
304 /*
305 select RESULT_COLUMN_NAME from qa_plan_chars
306 where PLAN_ID = X_PLAN_ID
307 and CHAR_ID in (select char_id
308 from qa_chars
309 where developer_name =
310 'TO_INTRAOPERATION_STEP');
311 */
312
313 Cursor MY_STEP_LOOKUP(X_MEANING VARCHAR2) is
314 select lookup_code from mfg_lookups
315 where meaning = X_MEANING
316 and lookup_type = 'WIP_INTRAOPERATION_STEP';
317
318 Cursor MY_ASSIGNED_CHAR_ID(X_PCA_ID NUMBER, X_ACTION_ID NUMBER) is
319 select ASSIGNED_CHAR_ID
320 from QA_PLAN_CHAR_ACTIONS
321 where PLAN_CHAR_ACTION_ID = X_PCA_ID
322 and ACTION_ID = X_ACTION_ID;
323
324 -- Bug 4958762: SQL Repository Fix SQL ID: 15008263
325 CURSOR MY_RESULTS_COLUMN(X_PLAN_ID NUMBER) IS
326 SELECT
327 NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
328 qc.developer_name DEV_NAME
329 FROM qa_plan_chars qpc,
330 qa_chars qc
331 WHERE qpc.plan_id = X_PLAN_ID
332 AND qc.char_id = qpc.char_id
333 AND qc.char_context_flag <> 3
334 ORDER BY qc.char_id;
335
336 /*
337 SELECT NVL(qpcv.hardcoded_column,qpcv.result_column_name) Q_COLUMN,
338 qpcv.developer_name DEV_NAME
339 FROM qa_plan_chars_v qpcv
340 WHERE qpcv.plan_id = X_PLAN_ID
341 AND qpcv.char_context_flag <> 3
342 ORDER BY qpcv.char_id;
343 */
344
345 RC MY_RESULTS_COLUMN%ROWTYPE;
346
347 CURSOR MY_REASON_ID(X_REASON_CODE VARCHAR2) IS
348 SELECT REASON_ID FROM MTL_TRANSACTION_REASONS_VAL_V
349 WHERE REASON_NAME = X_REASON_CODE;
350
351 CURSOR MY_EMPLOYEE_ID(X_EMPLOYEE VARCHAR2) IS
352 SELECT EMPLOYEE_ID FROM HR_EMPLOYEES_CURRENT_V
353 WHERE FULL_NAME = X_EMPLOYEE;
354
355 --kaza
356
357 CURSOR Get_result_column_name(elem_id NUMBER, col_plan_id NUMBER) IS
358 select result_column_name
359 from qa_plan_chars
360 where char_id = elem_id and
361 plan_id = col_plan_id;
362
363
364 CURSOR Get_user_message(elem_id NUMBER,col_plan_id NUMBER, act_id NUMBER) IS
365 select qpca.message
366 from qa_plan_char_actions qpca, qa_plan_char_action_triggers qpcat
367 where qpcat.char_id = elem_id and
368 qpcat.plan_id = col_plan_id and
369 qpcat.plan_char_action_trigger_id = qpca.plan_char_action_trigger_id and
370 qpca.action_id = act_id;
371
372 CURSOR Get_priority_id(value VARCHAR2) IS
373 select lookup_code
374 from mfg_lookups
375 where lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY' and
376 meaning = value;
377
378
379 CURSOR Get_eam_firm_flag(org_id NUMBER) IS
380 select auto_firm_flag
381 from wip_eam_parameters
382 where organization_id = org_id ;
383
384
385 /*
386 CURSOR Get_operator_type(operator_number number) IS
387 select meaning
388 from mfg_lookups
389 where lookup_type = 'QA_OPERATOR' and
390 lookup_code = operator_number;
391 */
392
393 --kaza
394
395
396 -- CURSOR MY_PO_GROUP_ID IS
397 -- SELECT rcv_interface_groups_s.nextval
398 -- FROM dual;
399
400 -- used for action 18, 19 to
401 -- get status id for lot/serial statuses
402 CURSOR MY_STATUS_ID (X_PCA_ID NUMBER) IS
403 SELECT STATUS_ID FROM QA_PLAN_CHAR_ACTIONS
404 WHERE PLAN_CHAR_ACTION_ID = X_PCA_ID;
405
406
407 Y_SPEC_ID NUMBER;
408 Y_PLAN_ID NUMBER;
409 OLD_SPEC_ID NUMBER := -9999;
410 OLD_PLAN_ID NUMBER := -9999;
411 i BINARY_INTEGER := 0;
412 total_rows BINARY_INTEGER;
413 result VARCHAR2(150);
414 done BOOLEAN;
415 output NUMBER;
416 output2 NUMBER;
417 output3 NUMBER;
418 output4 NUMBER;
419 X_STATUS VARCHAR2(240);
420 RES_COL_NAME VARCHAR2(30);
421 INTEROP_STEP VARCHAR2(30);
422 X_message VARCHAR2(2500);
423 X_org_id NUMBER;
424 X_CREATION_DATE DATE;
425 X_COLLECTION_ID NUMBER;
426 X_OCCURRENCE NUMBER;
427 X_ERROR BOOLEAN := TRUE;
428 X_ASSIGNED_CHAR_ID NUMBER;
429 X_ASSIGN_TYPE VARCHAR(1);
430 --
431 -- Bug 5926308
432 -- Increasing the width of the variable
433 -- X_SQL_STATEMENT from 1500 to 2500
434 -- skoluku Mon Apr 9 23:23:05 PDT 2007
435 --
436 X_SQL_STATEMENT VARCHAR2(2500);
437 X_REASON_ID NUMBER;
438 X_REASON_CODE VARCHAR2(30);
439 X_EMPLOYEE_ID NUMBER;
440 X_EMPLOYEE VARCHAR2(30);
441 X_LOGIN NUMBER;
442 X_TRANSACTION_ID NUMBER;
443 X_TRANSACTION_DATE DATE;
444 X_TXN_TYPE VARCHAR2(30);
445 X_QUANTITY NUMBER;
446 X_UOM VARCHAR2(25);
447 X_QUALITY_CODE VARCHAR2(25);
448 X_VENDOR_LOT VARCHAR2(30);
449 X_COMMENTS VARCHAR2(240);
450 X_RETURN_STATUS VARCHAR2(5);
451 X_MSG_COUNT NUMBER;
452 X_MSG_DATA VARCHAR2(240);
453 X_LAST_UPDATED_BY NUMBER;
454 X_VENDOR_ID NUMBER ;
455 X_ITEM_ID NUMBER ;
456 X_OP_UNIT NUMBER ;
457 X_TRANSACTION_NUMBER NUMBER ;
458 X_WORKFLOW_ITEMTYPE VARCHAR2(2000);
459 X_WORKFLOW_ITEMTYPE_SELECTOR VARCHAR2(240);
460 X_WORKFLOW_NUMBER_OF_PROCESSES NUMBER;
461
462
463 --kaza
464
465 X_ASSET_GROUP_ID NUMBER;
466 X_ASSET_NUMBER VARCHAR2(100);
467 X_ASSET_INSTANCE_ID NUMBER; --dgupta: R12 EAM Integration. Bug 4345492
468 ELEMENT_ID number:= 172;
469 ELEMENT_NAME VARCHAR2(50);
470 Priority_id NUMBER;
471 Priority_soft_column varchar2(100);
472 Priority_value varchar2(30);
473 user_id NUMBER;
474 request_id NUMBER;
475 status_id NUMBER;
476 request_log VARCHAR2(1500);
477 operator_type VARCHAR2(50);
478 Priority_exists BOOLEAN;
479 l_resultout VARCHAR2(200);
480 l_error_message VARCHAR2(200);
481
482 l_group_id NUMBER;
483 l_work_order_rec WIP_EAMWORKORDER_PVT.work_order_interface_rec_type;
484 l_followup_activity_id NUMBER;
485
486 l_firm_flag VARCHAR2(1);
487
488 --kaza
489
490 result_value VARCHAR2(300);
491 retnum NUMBER;
492
493 po_api_failed EXCEPTION;
494 null_txn_id EXCEPTION;
495 column_missing EXCEPTION;
496 fail_po_insertion EXCEPTION;
497 wf_missing_selector EXCEPTION;
498 fail_setting_lot_status EXCEPTION;
499 fail_setting_serial_status EXCEPTION;
500
501 -- Added the below two exceptions for Bug 3225280. kabalakr.
502 fail_serial_insertion EXCEPTION;
503 fail_lot_insertion EXCEPTION;
504
505
506 x_progress NUMBER; -- for debug purposes
507 x_asl_return VARCHAR2(1);
508 update_column numtable; -- Added for update capabilities.
509 total_updates number := -1; -- Added for update capabilities.
510 txn_type varchar2(6); -- Added for update capabilities.
511 errmsg VARCHAR2(240);
512 errcode NUMBER;
513
514
515 -- OPM Conv R12 Tracking Bug 4345760
516 -- change variable size for lot num
517
518 X_LOT_NUMBER qa_results.lot_number%TYPE;
519
520 X_SERIAL_NUMBER VARCHAR2(30);
521 X_SUBINVENTORY VARCHAR2(10);
522 X_LOCATOR_ID NUMBER;
523 X_STATUS_ID NUMBER;
524
525 -- Added the below cursor and variables for RCV/WMS Merge.
526 -- Bug 3096256. kabalakr Mon Aug 18 03:18:19 PDT 2003.
527
528 CURSOR item_uom_cur (l_item_id NUMBER, l_org_id NUMBER) IS
529 SELECT primary_unit_of_measure
530 FROM mtl_system_items_b
531 WHERE inventory_item_id = l_item_id
532 AND organization_id = l_org_id;
533
534 X_LPN_ID NUMBER;
535 X_XFR_LPN_ID NUMBER;
536
537 l_primary_uom VARCHAR2(25);
538 l_primary_qty NUMBER;
539 l_int_txn_id NUMBER;
540 l_ser_txn_id NUMBER;
541
542 -- Added the below cursor and variables for Bug 3225280.
543 -- kabalakr Wed Oct 29 23:19:22 PST 2003.
544
545 CURSOR int_txn (grp_id NUMBER, txn_id NUMBER) IS
546 SELECT max(interface_transaction_id)
547 FROM rcv_transactions_interface
548 WHERE group_id = grp_id
549 AND parent_transaction_id = txn_id;
550
551 l_rti_int_txn_id NUMBER;
552
553 -- Bug 6781108
554 -- Added the following two variables to get the value
555 -- and pass to the RCV API
556 x_rti_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE :=NULL;
557 x_rti_loc_id NUMBER := NULL;
558 BEGIN
559
560 x_progress := 1;
561 --dbms_output.enable(1000000);
562
563 -- 12/28/98
564 -- since parameter x_txn_header_id contains txn_header_id
565 -- or collection id depending odn the place from where it is
566 -- called , we pass NULL to the INIT_CURSOR procedure for collection_id
567 -- if txn_header_id is passed, otherwise NULL is passed for txn_header_id
568 -- if x_txn_header_id contains collection id.
569 -- The reason we are doing so is because we want to maintain backward
570 -- compatibility and do not disrturb code for collection import etc.
571
572 -- For Bug1843356. Added the IF condition below.
573 -- kabalakr 22 feb 02
574
575 IF (P_OCCURRENCE IS NOT NULL) THEN
576 QLTNINRB.INIT_CURSOR(P_PLAN_ID, NULL, X_TXN_HEADER_ID, P_OCCURRENCE);
577 ELSIF nvl(X_PASSED_ID_NAME, 'TXN_HEADER_ID') = 'TXN_HEADER_ID' then
578 QLTNINRB.INIT_CURSOR(NULL, X_TXN_HEADER_ID, NULL, NULL);
579 ELSE
580 QLTNINRB.INIT_CURSOR(NULL, NULL, X_TXN_HEADER_ID, NULL) ;
581 END IF ;
582
583 WHILE QLTNINRB.NEXT_ROW LOOP
584
585 -- get collection id, creation date, spec id and plan id
586 X_COLLECTION_ID := to_number(QLTNINRB.NAME_IN('COLLECTION_ID'));
587 X_CREATION_DATE := fnd_date.chardt_to_date(QLTNINRB.NAME_IN('QA_CREATION_DATE'));
588 Y_SPEC_ID := to_number(QLTNINRB.NAME_IN('SPEC_ID'));
589 Y_PLAN_ID := to_number(QLTNINRB.NAME_IN('PLAN_ID'));
590
591 -- Bug 5111269.
592 -- In EAM Transactions we are not supporting Specifications at plan level.
593 -- We support specifications at element level only. Since specifications is
594 -- not supported at plan level we call Quality APIs from EAM with spec_id as -1
595 -- (even if one of the elements in the collection plan has specification limits
596 -- defined). When spec_id is -1 the existing code assigns NULL to spec_id(This
597 -- happens in qltssresb.plb). If we pass NULL then the actions based on the elements
598 -- would not fire because for specification at element level in qltdactb we check
599 -- whether spec_id is 0 and then fire the actions. If it is NULL then no action
600 -- would fire. So made Y_SPEC_ID to have the value as 0 if spec_id is NULL or <= 0.
601 -- ntungare Wed Mar 29 02:58:45 PST 2006.
602
603 If ((Y_SPEC_ID IS NULL) or (Y_SPEC_ID <= 0)) then
604 Y_SPEC_ID := 0;
605 End if;
606
607 -- debug message, beginning actions processing
608 IF X_DEBUG = TRUE THEN
609 X_message := 'ACTION PROCESS DBG, TXN ID: '||X_TXN_HEADER_ID;
610 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
611 X_CREATION_DATE,0,
612 0,' ',
613 ' ',X_message,
614 ' ',X_CONCURRENT);
615 END IF;
616
617 -- Added for update capabilities.
618 -- Get which columns got updated from history table for this particular
619 -- collection and occurence.
620
621
622 -- if statement is added to fix bug 902020
623 -- the following piece of code should not be executed
624 -- when transaction integration. It should be executed only for
625 -- collection import, when x_passed_id_name is default to
626 -- 'COLLECTION_ ID'
627 -- Jenny 6/13/99
628
629 -- Changed the IF condition so that the below code gets
630 -- executed only during collection import.When collection
631 -- import is used X_ARGUMENT will have the value as IMPORT.
632 -- Bug 3273447. suramasw
633
634 -- if nvl(X_PASSED_ID_NAME, 'TXN_HEADER_ID') <> 'TXN_HEADER_ID' THEN
635
636 if NVL(X_ARGUMENT,NULL) = 'IMPORT' THEN
637 BEGIN
638
639 I := 0;
640 X_OCCURRENCE := TO_NUMBER(QLTNINRB.NAME_IN('OCCURRENCE'));
641 FOR UPDATED_RES_COLUMNS IN
642 (SELECT CHAR_ID
643 FROM QA_RESULTS_UPDATE_HISTORY
644 WHERE TXN_HEADER_ID = X_TXN_HEADER_ID
645 AND OCCURRENCE = X_OCCURRENCE) LOOP
646 I := I + 1;
647 UPDATE_COLUMN(I) := UPDATED_RES_COLUMNS.CHAR_ID;
648 END LOOP;
649 TOTAL_UPDATES := I;
650
651 IF TOTAL_UPDATES = 0
652 THEN SELECT DECODE(INSERT_TYPE,2,'UPDATE','INSERT')
653 INTO TXN_TYPE
654 FROM QA_RESULTS_INTERFACE
655 WHERE TRANSACTION_INTERFACE_ID = X_TXN_HEADER_ID;
656
657 IF NVL(TXN_TYPE,'INSERT') = 'UPDATE'
658 THEN TOTAL_UPDATES := 0;
659 ELSE TOTAL_UPDATES := -1;
660 END IF;
661 END IF;
662
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 TOTAL_UPDATES := -1;
666 END;
667
668 end if;
669 i := 0;
670 IF ((Y_SPEC_ID <> OLD_SPEC_ID) OR (Y_PLAN_ID <> OLD_PLAN_ID)) AND
671 (TOTAL_UPDATES <> 0) THEN
672 BEGIN
673 FOR prec IN action_triggers(Y_PLAN_ID, Y_SPEC_ID) LOOP
674 i := i + 1;
675 char_id_tab(i) := prec.CHAR_ID;
676 datatype_tab(i) := prec.TYPE;
677 fk_type_tab(i) := prec.FK_LOOKUP_TYPE;
678 column_tab(i) := prec.Q_COLUMN;
679 operator_tab(i) := prec.OPERATOR;
680 low_value_tab(i) := prec.LOW_VALUE;
681 high_value_tab(i) := prec.HIGH_VALUE;
682 action_id_tab(i) := prec.ACTION;
683 pcat_id_tab(i) := prec.PCAT_ID;
684 pca_id_tab(i) := prec.PCA_ID;
685 seq_id_tab(i) := prec.SEQ_ID;
686
687 -- BUG 3303285
688 -- ksoh Wed Dec 31 12:20:09 PST 2003
689 -- if spec is used and spec element UOM and plan element UOM
690 -- are different, perform conversion
691 IF ((Y_SPEC_ID <> 0) AND
692 (prec.PLAN_CHAR_UOM <> prec.SPEC_CHAR_UOM)) THEN
693 IF (prec.LOW_VALUE IS NOT NULL) THEN
694 low_value_tab(i) := INV_CONVERT.INV_UM_CONVERT(null,
695 prec.DECIMAL_PRECISION,
696 prec.LOW_VALUE,
697 prec.SPEC_CHAR_UOM,
698 prec.PLAN_CHAR_UOM,
699 null,
700 null);
701 END IF;
702 IF (prec.HIGH_VALUE IS NOT NULL) THEN
703 high_value_tab(i) := INV_CONVERT.INV_UM_CONVERT(null,
704 prec.DECIMAL_PRECISION,
705 prec.HIGH_VALUE,
706 prec.SPEC_CHAR_UOM,
707 prec.PLAN_CHAR_UOM,
708 null,
709 null);
710 END IF;
711 IF ((low_value_tab(i) = -99999) OR (high_value_tab(i) = -99999)) THEN
712 fnd_message.set_name('QA', 'QA_INCONVERTIBLE_UOM');
713 fnd_message.set_token('ENTITY1', prec.SPEC_CHAR_UOM);
714 fnd_message.set_token('ENTITY2', prec.PLAN_CHAR_UOM);
715 fnd_msg_pub.add();
716 END IF;
717 END IF; -- (Y_SPEC_ID <> 0)...
718 END LOOP;
719 EXCEPTION
720 WHEN NO_DATA_FOUND then
721 i := 0;
722 WHEN OTHERS then
723 raise;
724 END;
725 total_rows := i;
726 END IF;
727
728 IF X_DEBUG = TRUE THEN
729 X_message := 'ACTION PROCESS DBG, PAST IMPORT LOGIC';
730 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
731 X_CREATION_DATE,0,
732 0,'',
733 '',X_message,
734 '',X_CONCURRENT);
735 END IF;
736
737 i := 1;
738 WHILE i <= total_rows LOOP
739 -- Logic added for foreign keyed elements (customer, supplier,
740 -- etc.) which need to be decoded from ID to meaning before the
741 -- comparison can be evaluated. Checking FK_LOOKUP_TYPE locally
742 -- saves us an unnecessary database hit.
743 IF fk_type_tab(i) in (0, 1, 3)
744 THEN result := QLTSMENB.LOOKUP(char_id_tab(i),
745 QLTNINRB.NAME_IN(column_tab(i)));
746 ELSE result := QLTNINRB.NAME_IN(column_tab(i));
747 END IF;
748 done := FALSE;
749
750 -- Added for update capabilities...
751 -- If the updated column matches the action trigger column,
752 -- then not_done will remain FALSE. Else, it will be set to TRUE
753 -- and the action code will not be executed. This is so that
754 -- only columns that got updated will fire actions.
755 IF total_updates > -1
756 THEN FOR j IN 1..(total_updates+1) LOOP
757 IF ( j = (total_updates+1) )THEN done := TRUE;
758 ELSIF (update_column(j) = char_id_tab(i)) THEN EXIT;
759 END IF;
760 END LOOP;
761 END IF;
762
763 -- see if the action rule evaluates to true, and if so,
764 -- fire appropriate actions
765 IF qltcompb.compare(result,operator_tab(i),low_value_tab(i),
766 high_value_tab(i), datatype_tab(i)) AND (not done) THEN
767
768 WHILE (not done) LOOP
769
770 -- online actions
771 IF action_id_tab(i) in (1, 2) and nvl(x_action_type, 'DEFERRED') = 'DEFFERED' THEN
772 null;
773
774 -- alerts (always a concurrent request)
775 ELSIF action_id_tab(i) in (10, 11, 12, 13) AND
776 nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
777 IF X_DEBUG = TRUE THEN
778 X_message := 'ACTION PROCESS DBG, FIRING ALERT';
779 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
780 X_CREATION_DATE,char_id_tab(i),
781 operator_tab(i),low_value_tab(i),
782 high_value_tab(i),X_message,
783 result,X_CONCURRENT);
784 END IF;
785 FIRE_ALERT(pca_id_tab(i));
786
787 -- placeholder for corrective action
788 ELSIF action_id_tab(i) = 14 THEN
789 null;
790
791 -- action log
792 ELSIF action_id_tab(i) = 15 AND
793 nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
794 IF X_DEBUG = TRUE THEN
795 X_message := 'ACTION PROCESS DBG, ACTION LOG INSERT';
796 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
797 X_CREATION_DATE,char_id_tab(i),
798 operator_tab(i),low_value_tab(i),
799 high_value_tab(i),X_message,
800 result,X_CONCURRENT);
801 END IF;
802
803 OPEN MY_MESSAGE(pca_id_tab(i));
804 FETCH MY_MESSAGE INTO X_MESSAGE, X_ASSIGN_TYPE;
805 CLOSE MY_MESSAGE;
806 INSERT_ACTION_LOG(Y_PLAN_ID,
807 X_COLLECTION_ID,
808 X_CREATION_DATE,
809 char_id_tab(i),
810 operator_tab(i),
811 low_value_tab(i),
812 high_value_tab(i),
813 X_message,
814 result,
815 X_CONCURRENT);
816
817 -- job or schedule on hold
818 ELSIF action_id_tab(i) = 16 AND nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
819 IF X_DEBUG = TRUE THEN
820 X_message := 'ACTION PROCESS DBG, PLACING JOB ON HOLD';
821 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
822 X_CREATION_DATE,char_id_tab(i),
823 operator_tab(i),low_value_tab(i),
824 high_value_tab(i),X_message,
825 result,X_CONCURRENT);
826 END IF;
827 output := to_number(QLTNINRB.NAME_IN('WIP_ENTITY_ID'));
828 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
829
830 -- call WIP api
831 BEGIN
832 WIP_CHANGE_STATUS.PUT_JOB_ON_HOLD(output, X_org_id);
833 EXCEPTION
834 WHEN OTHERS THEN
835 -- write exceptions to action log;
836 X_ERROR := FALSE;
837 X_message := FND_MESSAGE.GET;
838 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
839 X_CREATION_DATE,char_id_tab(i),
840 operator_tab(i),low_value_tab(i),
841 high_value_tab(i),X_message,
842 result,X_CONCURRENT);
843 END;
844
845
846 -- assign shop floor status to intraoperation step
847 ELSIF action_id_tab(i) = 17 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
848 IF X_DEBUG = TRUE THEN
849 X_message := 'ACTION PROCESS DBG, ASSIGNING SHOP FLOOR STATUS';
850 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
851 X_CREATION_DATE,char_id_tab(i),
852 operator_tab(i),low_value_tab(i),
853 high_value_tab(i),X_message,
854 result,X_CONCURRENT);
855 END IF;
856
857 OPEN MY_STATUS(pca_id_tab(i));
858 FETCH MY_STATUS INTO X_STATUS;
859 CLOSE MY_STATUS;
860
861 OPEN MY_STEP(Y_PLAN_ID);
862 FETCH MY_STEP INTO RES_COL_NAME;
863 CLOSE MY_STEP;
864
865 output := to_number(QLTNINRB.NAME_IN('WIP_ENTITY_ID'));
866 output2 := to_number(QLTNINRB.NAME_IN('LINE_ID'));
867 output3 := to_number(QLTNINRB.NAME_IN('TO_OP_SEQ_NUM'));
868 INTEROP_STEP := QLTNINRB.NAME_IN(RES_COL_NAME);
869
870 OPEN MY_STEP_LOOKUP(INTEROP_STEP);
871 FETCH MY_STEP_LOOKUP INTO output4;
872 CLOSE MY_STEP_LOOKUP;
873
874 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
875
876 -- call WIP api
877 BEGIN
878 WIP_SF_STATUS.ATTACH(output,X_org_id,output2,
879 output3, output4,X_STATUS);
880 EXCEPTION
881 WHEN OTHERS THEN
882 -- write exceptions to action log;
883 X_ERROR := FALSE;
884 X_message := FND_MESSAGE.GET;
885 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
886 X_CREATION_DATE,char_id_tab(i),
887 operator_tab(i),low_value_tab(i),
888 high_value_tab(i),X_message,
889 result,X_CONCURRENT);
890 END;
891
892 -- assign lot status
893 ELSIF action_id_tab(i) = 18 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
894
895 X_message := 'ACTION PROCESS DBG, ASSIGNING LOT STATUS';
896 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
897 X_CREATION_DATE,0,
898 0,'',
899 '',X_message,
900 '',X_CONCURRENT);
901 -- get lot status id
902 OPEN MY_STATUS_ID (pca_id_tab(i));
903 FETCH MY_STATUS_ID INTO X_STATUS_ID;
904 CLOSE MY_STATUS_ID;
905
906 -- get the org id, lot number and item id
907 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
908 X_lot_number := QLTNINRB.NAME_IN('LOT_NUMBER');
909 X_Item_id := to_number (QLTNINRB.Name_in('ITEM_ID' )) ;
910
911 -- call Inventory API
912 BEGIN
913 INV_MATERIAL_STATUS_GRP.UPDATE_STATUS(
914 p_api_version_number => 1.0,
915 x_return_status => x_return_status,
916 x_msg_count => x_msg_count,
917 x_msg_data => x_msg_data,
918 p_update_method => 4,
919 p_status_id => x_status_id,
920 p_organization_id => x_org_id,
921 p_inventory_item_id => x_item_id,
922 p_lot_number => x_lot_number,
923 p_object_type => 'O');
924
925 IF x_return_status <> fnd_api.g_ret_sts_success THEN
926 RAISE fail_setting_lot_status;
927 END IF;
928
929 EXCEPTION
930 WHEN fail_setting_lot_status THEN
931
932 FND_MESSAGE.SET_NAME('QA', 'QA_LOT_STATUS_ACTION_FAIL');
933 X_message := FND_MESSAGE.GET;
934 X_message := X_message ||X_msg_data;
935
936 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
937 X_CREATION_DATE,char_id_tab(i),
938 operator_tab(i),low_value_tab(i),
939 high_value_tab(i),X_message,
940 result,X_CONCURRENT);
941 WHEN OTHERS THEN
942 -- write exceptions to action log;
943 X_ERROR := FALSE;
944 X_message := FND_MESSAGE.GET;
945 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
946 X_CREATION_DATE,char_id_tab(i),
947 operator_tab(i),low_value_tab(i),
948 high_value_tab(i),X_message,
949 result,X_CONCURRENT);
950 END;
951
952 -- assign serial status
953 ELSIF action_id_tab(i) = 19 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
954 X_message := 'ACTION PROCESS DBG, ASSIGNING SERIAL STATUS';
955 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
956 X_CREATION_DATE,0,
957 0,'',
958 '',X_message,
959 '',X_CONCURRENT);
960
961 -- get serial status id
962 OPEN MY_STATUS_ID (pca_id_tab(i));
963 FETCH MY_STATUS_ID INTO X_STATUS_ID;
964 CLOSE MY_STATUS_ID;
965
966 -- get the org id, serial number and item id
967 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
968 X_serial_number := QLTNINRB.NAME_IN('SERIAL_NUMBER');
969 X_Item_id := to_number (QLTNINRB.Name_in('ITEM_ID' )) ;
970
971 -- call Inventory API
972 BEGIN
973 INV_MATERIAL_STATUS_GRP.UPDATE_STATUS(
974 p_api_version_number => 1.0,
975 x_return_status => x_return_status,
976 x_msg_count => x_msg_count,
977 x_msg_data => x_msg_data,
978 p_update_method => 4,
979 p_status_id => x_status_id,
980 p_organization_id => x_org_id,
981 p_inventory_item_id => x_item_id,
982 p_serial_number => x_serial_number,
983 p_object_type => 'S');
984
985 IF x_return_status <> fnd_api.g_ret_sts_success THEN
986 RAISE fail_setting_serial_status;
987 END IF;
988
989 EXCEPTION
990 WHEN fail_setting_serial_status THEN
991
992 FND_MESSAGE.SET_NAME('QA', 'QA_SERIAL_STATUS_ACTION_FAIL');
993 X_message := FND_MESSAGE.GET;
994 X_message := X_message || X_msg_data;
995
996 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
997 X_CREATION_DATE,char_id_tab(i),
998 operator_tab(i),low_value_tab(i),
999 high_value_tab(i),X_message,
1000 result,X_CONCURRENT);
1001 WHEN OTHERS THEN
1002 -- write exceptions to action log;
1003 X_ERROR := FALSE;
1004 X_message := FND_MESSAGE.GET;
1005 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1006 X_CREATION_DATE,char_id_tab(i),
1007 operator_tab(i),low_value_tab(i),
1008 high_value_tab(i),X_message,
1009 result,X_CONCURRENT);
1010 END;
1011
1012
1013 -- placeholder for assigning item status
1014 ELSIF action_id_tab(i) = 20 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED'THEN
1015 null;
1016
1017 -- place the supplier on hold
1018 ELSIF action_id_tab(i) = 21 and nvl(x_action_type, 'DEFERRED')= 'DEFERRED'THEN
1019 X_message := 'ACTION PROCESS DBG, PLACING SUPPLIER ON HOLD';
1020 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1021 X_CREATION_DATE,0,
1022 0,'',
1023 '',X_message,
1024 '',X_CONCURRENT);
1025 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
1026 X_Vendor_id := to_number(QLTNINRB.NAME_IN('VENDOR_ID'));
1027
1028 -- get the operating unit
1029 OPEN MY_OP_UNIT(X_ORG_ID);
1030 FETCH MY_OP_UNIT INTO X_OP_UNIT;
1031 CLOSE MY_OP_UNIT ;
1032
1033 BEGIN
1034 -- we need an API to update vendor status from AP team.
1035 -- for now we just update the table directly
1036 Update PO_VENDOR_SITES
1037 SET HOLD_ALL_PAYMENTS_FLAG = 'Y'
1038 WHERE VENDOR_ID = x_vendor_id
1039 AND ORG_ID = NVL ( X_OP_UNIT, ORG_ID);
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 -- write exceptions to action log;
1043 X_ERROR := FALSE;
1044 X_message := FND_MESSAGE.GET;
1045 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1046 X_CREATION_DATE,char_id_tab(i),
1047 operator_tab(i),low_value_tab(i),
1048 high_value_tab(i),X_message,
1049 result,X_CONCURRENT);
1050 END;
1051
1052 -- place the po or release on hold
1053 ELSIF action_id_tab(i) = 22 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
1054 -- call PO API
1055 X_message := 'ACTION PROCESS DBG, PLACING PO OR RELEASE ON HOLD';
1056 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1057 X_CREATION_DATE,0,
1058 0,'',
1059 '',X_message,
1060 '',X_CONCURRENT);
1061 BEGIN
1062 retnum := PO_DOCUMENT_ACTIONS_SV.PO_HOLD_DOCUMENT(
1063 to_number(QLTNINRB.NAME_IN('PO_HEADER_ID')),
1064 to_number(QLTNINRB.NAME_IN('PO_RELEASE_ID')),
1065 x_msg_data);
1066 IF retnum <> 0 THEN
1067 RAISE po_api_failed;
1068 END IF;
1069 EXCEPTION
1070 WHEN po_api_failed THEN
1071 -- write exceptions to action log;
1072 X_ERROR := FALSE;
1073 X_message := FND_MESSAGE.GET;
1074 X_message := X_message || X_msg_data;
1075 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1076 X_CREATION_DATE,char_id_tab(i),
1077 operator_tab(i),low_value_tab(i),
1078 high_value_tab(i),X_message,
1079 result,X_CONCURRENT);
1080 END;
1081
1082 -- WIP production line on hold
1083 ELSIF action_id_tab(i) = 23 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED'THEN
1084 X_message := 'ACTION PROCESS DBG, PLACING PRODUCTION LINE ON HOLD';
1085 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1086 X_CREATION_DATE,0,
1087 0,'',
1088 '',X_message,
1089 '',X_CONCURRENT);
1090 output := to_number(QLTNINRB.NAME_IN('WIP_ENTITY_ID'));
1091 output2 := to_number(QLTNINRB.NAME_IN('LINE_ID'));
1092 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
1093
1094 -- AG: QWB: wip_entity_id is needed to put the schedule on hold.
1095 IF output IS NULL THEN
1096 BEGIN
1097 SELECT DISTINCT wrs.wip_entity_id INTO output
1098 FROM wip_repetitive_schedules wrs,
1099 wip_entities we
1100 WHERE wrs.organization_id = x_org_id
1101 AND wrs.line_id = output2
1102 AND we.wip_entity_id = wrs.wip_entity_id
1103 AND we.organization_id = wrs.organization_id
1104 AND we.primary_item_id = to_number(QLTNINRB.NAME_IN('ITEM_ID'));
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 NULL;
1108 END;
1109 END IF;
1110 -- AG: QWB: end
1111
1112 -- call WIP api
1113 BEGIN
1114 WIP_CHANGE_STATUS.PUT_LINE_ON_HOLD(output, output2, X_org_id);
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 -- write exceptions to action log;
1118 X_ERROR := FALSE;
1119 X_message := FND_MESSAGE.GET;
1120 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1121 X_CREATION_DATE,char_id_tab(i),
1122 operator_tab(i),low_value_tab(i),
1123 high_value_tab(i),X_message,
1124 result,X_CONCURRENT);
1125 END;
1126
1127 -- assign a value
1128
1129 -- For Bug 1843356. Added the OR condition below.
1130
1131 ELSIF action_id_tab(i) = 24 AND (nvl(x_action_type, 'DEFERRED') = 'DEFERRED' OR
1132 x_action_type = 'BACKGROUND_ASSIGN_VALUE') THEN
1133 -- if action wasn't executed in form, execute here
1134 IF nvl(X_BACKGROUND, TRUE) THEN
1135 X_message := 'ACTION PROCESS DBG, ASSIGNING A VALUE';
1136 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1137 X_CREATION_DATE,0,
1138 0,'',
1139 '',X_message,
1140 '',X_CONCURRENT);
1141 X_OCCURRENCE := to_number(QLTNINRB.NAME_IN('OCCURRENCE'));
1142
1143 OPEN MY_ASSIGNED_CHAR_ID(pca_id_tab(i), action_id_tab(i));
1144 FETCH MY_ASSIGNED_CHAR_ID INTO X_ASSIGNED_CHAR_ID;
1145 CLOSE MY_ASSIGNED_CHAR_ID;
1146
1147 OPEN MY_MESSAGE(pca_id_tab(i));
1148 FETCH MY_MESSAGE
1149 INTO X_MESSAGE, X_ASSIGN_TYPE;
1150 CLOSE MY_MESSAGE;
1151
1152 -- See Bug 956708
1153 -- bso
1154 x_message := rtrim(x_message, ' ;/
1155 ');
1156
1157
1158 IF X_ASSIGN_TYPE = 'F' THEN
1159 X_MESSAGE := 'SELECT ' || X_MESSAGE || ' FROM dual';
1160 END IF;
1161
1162 BEGIN
1163 DO_ASSIGNMENT(pca_id_tab(i),X_MESSAGE,
1164 X_ASSIGNED_CHAR_ID,X_COLLECTION_ID,
1165 X_OCCURRENCE,Y_PLAN_ID,
1166 X_SQL_STATEMENT);
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 -- write exceptions to action log;
1171 X_ERROR := FALSE;
1172 X_message := FND_MESSAGE.GET;
1173 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1174 X_CREATION_DATE,char_id_tab(i),
1175 operator_tab(i),low_value_tab(i),
1176 high_value_tab(i),X_message,
1177 result,X_CONCURRENT);
1178 END;
1179 END IF; -- X_BACKGROUND
1180
1181 -- accept, reject rcv shipment
1182 ELSIF action_id_tab(i) in (25, 26) AND
1183 x_action_type = 'IMMEDIATE' THEN
1184 x_progress := 2;
1185 X_TRANSACTION_NUMBER :=
1186 to_number(QLTNINRB.NAME_IN('TRANSACTION_NUMBER'));
1187
1188 -- only allow action if from inspection transaction
1189
1190 --
1191 -- and when this collection is neither skip lot inspection
1192 -- nor sampling inspection
1193 -- jezheng
1194 -- Wed Aug 22 18:29:07 PDT 2001
1195 --
1196 IF X_TRANSACTION_NUMBER = 21 AND
1197 QA_INSPECTION_PKG.IS_REGULAR_INSP(X_COLLECTION_ID) = fnd_api.g_true
1198 THEN
1199 BEGIN
1200
1201 -- 25 is accept and 26 is reject
1202
1203
1204 IF action_id_tab(i)= 25 THEN
1205 X_TXN_TYPE := 'ACCEPT';
1206 X_message := 'ACTION PROCESS DBG, ACCEPTING THE SHIPMENT';
1207 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1208 X_CREATION_DATE,0,
1209 0,'',
1210 '',X_message,
1211 '',X_CONCURRENT);
1212 ELSIF action_id_tab(i)= 26 THEN
1213 X_TXN_TYPE := 'REJECT';
1214 X_message := 'ACTION PROCESS DBG, REJECTING THE SHIPMENT';
1215 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1216 X_CREATION_DATE,0,
1217 0,'',
1218 '',X_message,
1219 '',X_CONCURRENT);
1220 END IF;
1221
1222 X_TRANSACTION_ID := QLTNINRB.NAME_IN('TRANSACTION_ID');
1223
1224 x_progress := 3;
1225 IF X_TRANSACTION_ID IS NULL THEN
1226 x_progress := 4;
1227 RAISE null_txn_id;
1228 END IF;
1229
1230 x_progress := 5;
1231 X_LAST_UPDATED_BY := QLTNINRB.NAME_IN('LAST_UPDATED_BY');
1232 X_LOGIN := QLTNINRB.NAME_IN('LAST_UPDATE_LOGIN');
1233 OPEN MY_RESULTS_COLUMN(Y_PLAN_ID);
1234
1235 -- loop through collection elements, extracting
1236 -- the ones required for the action
1237 LOOP
1238 FETCH MY_RESULTS_COLUMN INTO RC;
1239 EXIT WHEN MY_RESULTS_COLUMN%NOTFOUND;
1240
1241 result_value := QLTNINRB.NAME_IN(RC.Q_COLUMN);
1242 IF RC.DEV_NAME = 'TRANSACTION_DATE' THEN
1243 X_TRANSACTION_DATE := qltdate.any_to_date(result_value);
1244 ELSIF RC.DEV_NAME = 'QUANTITY' THEN
1245 X_QUANTITY := result_value;
1246 ELSIF RC.DEV_NAME = 'QUALITY_CODE' THEN
1247 X_QUALITY_CODE := result_value;
1248 ELSIF RC.DEV_NAME = 'COMMENTS' THEN
1249 X_COMMENTS := result_value;
1250 --
1251 -- bug 6266404
1252 -- Modified the condition to use the actual
1253 -- context element SUPPLIER_LOT instead of
1254 -- INSP_SUPPLIER_LOT.
1255 -- skolluku Mon Jul 23 02:44:58 PDT 2007
1256 --
1257 --ELSIF RC.DEV_NAME = 'INSP_SUPPLIER_LOT' THEN
1258 ELSIF RC.DEV_NAME = 'SUPPLIER_LOT' THEN
1259 X_VENDOR_LOT := result_value;
1260 ELSIF RC.DEV_NAME = 'UOM_NAME' THEN
1261 X_UOM := result_value;
1262 ELSIF RC.DEV_NAME = 'INSP_REASON_CODE' THEN
1263 X_REASON_CODE := result_value;
1264 ELSIF RC.DEV_NAME = 'EMPLOYEE' THEN
1265 X_EMPLOYEE := result_value;
1266
1267 -- Bug 3096256.
1268 -- Added the below statements for RCV/WMS Merge.
1269 -- Fetch the LPN, Lot and serial info entered in the results
1270 -- record.
1271 --
1272 -- For Transfer LPN, user can enter a new LPN name which
1273 -- will be generated in Quality Module. This generation has
1274 -- already been done in QLTRES during Insert_Row. Hence, just
1275 -- fetch the id entered in the result for transfer LPN.
1276 -- kabalakr Mon Aug 18 03:18:19 PDT 2003.
1277 --
1278
1279 -- Bug 3225280. Change the developer_name to ITEM instead of
1280 -- ITEM_ID. kabalakr Wed Oct 29 23:19:22 PST 2003.
1281
1282 ELSIF RC.DEV_NAME = 'ITEM' THEN
1283 X_ITEM_ID := to_number(result_value);
1284 ELSIF RC.DEV_NAME = 'LICENSE_PLATE_NUMBER' THEN
1285 X_LPN_ID := to_number(result_value);
1286 ELSIF RC.DEV_NAME = 'XFR_LICENSE_PLATE_NUMBER' THEN
1287 X_XFR_LPN_ID := to_number(result_value);
1288 ELSIF RC.DEV_NAME = 'LOT_NUMBER' THEN
1289 X_LOT_NUMBER := result_value;
1290 ELSIF RC.DEV_NAME = 'SERIAL_NUMBER' THEN
1291 X_SERIAL_NUMBER := result_value;
1292
1293 END IF;
1294 END LOOP;
1295
1296 CLOSE MY_RESULTS_COLUMN;
1297
1298 -- Fetching the value of org_id. For RCV/WMS project.
1299 -- Bug 3096256. kabalakr Mon Aug 18 03:18:19 PDT 2003.
1300
1301 X_ORG_ID := TO_NUMBER(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
1302
1303
1304 IF X_TRANSACTION_DATE IS NULL OR
1305 X_QUANTITY IS NULL OR
1306 X_UOM IS NULL THEN
1307 -- the not null columns are null, raise error
1308 RAISE column_missing;
1309 END IF;
1310
1311 IF X_REASON_CODE IS NOT NULL THEN
1312 OPEN MY_REASON_ID(X_REASON_CODE);
1313 FETCH MY_REASON_ID INTO X_REASON_ID;
1314 CLOSE MY_REASON_ID;
1315 ELSE
1316 X_REASON_ID := NULL;
1317 END IF;
1318
1319 IF X_EMPLOYEE IS NOT NULL THEN
1320 OPEN MY_EMPLOYEE_ID(X_EMPLOYEE);
1321 FETCH MY_EMPLOYEE_ID INTO X_EMPLOYEE_ID;
1322 CLOSE MY_EMPLOYEE_ID;
1323 ELSE
1324 X_EMPLOYEE_ID := 1;
1325 END IF;
1326
1327 -- If X_LPN_ID is not null, it denotes that we are performing
1328 -- LPN Inspection. Hence the below algorithm is followed.
1329 --
1330 -- 1. If transfer LPN is not entered in the results record,
1331 -- default the LPN_ID as transfer_LPN_ID.
1332 --
1333 -- 2. If transfer LPN is entered in the results record but
1334 -- the transfer LPN_ID is NULL, call the inventory/WMS API
1335 -- inv_rcv_integration_apis.validate_lpn to validate and
1336 -- generate the new LPN. This API returns the LPN_ID for the
1337 -- new transfer LPN.
1338 --
1339 -- This step has been done in QLTRES during Insert_row beacuse
1340 -- the value of Transfer LPN is not stored in QA_RESULTS and
1341 -- hence cannot be retrieved.
1342 --
1343 -- 3. If transfer_LPN and transfer_LPN_ID exist is the results
1344 -- record, pass it directly to the RCV Inspection API.
1345 --
1346 -- Bug 3096256. kabalakr Mon Aug 18 03:18:19 PDT 2003.
1347 --
1348
1349 IF X_LPN_ID IS NOT NULL THEN
1350
1351 IF (X_XFR_LPN_ID IS NULL) THEN
1352 X_XFR_LPN_ID := X_LPN_ID;
1353 -- Bug 6781108
1354 -- Calling this Procedure to get subinv_code and loc_id
1355 -- in order to insert into RTI table
1356 -- pdube Wed Feb 6 04:53:32 PST 2008
1357 DEFAULT_LPN_SUB_LOC_INFO(X_LPN_ID,
1358 X_XFR_LPN_ID,
1359 x_rti_sub_code,
1360 x_rti_loc_id);
1361
1362 END IF;
1363
1364 END IF; -- If x_lpn_id is not null
1365
1366
1367 x_progress := 6;
1368
1369 -- Modified the RCV Inspection API for RCV/WMS Project.
1370 -- Parameters P_LPN_ID and P_TRANSFER_LPN_ID are added for
1371 -- enabling LPN Inspections through Desktop. Bug 3096256.
1372 --
1373 -- The api version is changed from 1.0 to 1.1.
1374 -- kabalakr Mon Aug 18 03:18:19 PDT 2003.
1375 --
1376 -- Bug 6781108
1377 -- Passing two variables to four parameters p_sub, p_loc_id,
1378 -- p_from_subinv and p_from_loc_id as new API
1379 -- for receiving needed these parameters
1380 -- pdube Wed Feb 6 23:22:10 PST 2008
1381 RCV_INSPECTION_GRP.INSERT_INSPECTION(
1382 p_api_version => 1.1,
1383 p_init_msg_list => NULL,
1384 p_commit => 'F',
1385 p_validation_level => NULL,
1386 p_created_by => X_LAST_UPDATED_BY,
1387 p_last_updated_by => X_LAST_UPDATED_BY,
1388 p_last_update_login => X_LOGIN,
1389 p_employee_id => X_EMPLOYEE_ID,
1390 p_group_id => X_GROUP_ID,
1391 p_transaction_id => X_TRANSACTION_ID,
1392 p_transaction_type => X_TXN_TYPE,
1393 p_processing_mode => X_PO_TXN_PROCESSOR_MODE,
1394 p_quantity => X_QUANTITY,
1395 p_uom => X_UOM,
1396 p_quality_code => X_QUALITY_CODE,
1397 p_transaction_date => X_TRANSACTION_DATE,
1398 p_comments => X_COMMENTS,
1399 p_reason_id => X_REASON_ID,
1400 p_vendor_lot => X_VENDOR_LOT,
1401 p_lpn_id => X_LPN_ID,
1402 p_transfer_lpn_id => X_XFR_LPN_ID,
1403 p_qa_collection_id => X_COLLECTION_ID,
1404 p_return_status => X_RETURN_STATUS,
1405 p_msg_count => X_MSG_COUNT,
1406 p_msg_data => X_MSG_DATA,
1407 p_subinventory => X_RTI_SUB_CODE,
1408 p_locator_id => X_RTI_LOC_ID,
1409 p_from_subinventory => X_RTI_SUB_CODE,
1410 p_from_locator_id => X_RTI_LOC_ID);
1411
1412
1413 x_progress := 7;
1414 IF X_RETURN_STATUS <> 'S' THEN
1415
1416
1417 x_progress := 8;
1418 RAISE fail_po_insertion;
1419 END IF;
1420
1421 -- Bug 6781108
1422 -- Commenting the following fix for 3225280 and 3270283
1423 -- as already handled above through the INSERT_INSPECTION API
1424 -- pdube Wed Feb 6 04:53:32 PST 2008
1425 /*-- Bug 3225280. Moved the Lot and serial insertion code after RCV
1426 -- insert_inspection API because, we want the interface_transaction_id
1427 -- of the ACCEPT and REJECT transactions to be passed to the WMS APIs
1428 -- as product_transaction_id.
1429 --
1430 -- For this, first we need to find the interface_transaction_id of the
1431 -- inspection record inserted by RCV API. The logic here is to fetch the
1432 -- max(interface_transaction_id) from rti for the parent_transaction_id
1433 -- and group_id combination. Since we are implementing this just after
1434 -- RCV API call, it will fetch the interface_transaction_id of the
1435 -- inspection record just inserted.
1436 -- kabalakr. Wed Oct 29 23:19:22 PST 2003.
1437 --
1438
1439 OPEN int_txn(X_GROUP_ID, X_TRANSACTION_ID);
1440 FETCH int_txn INTO l_rti_int_txn_id;
1441 CLOSE int_txn;
1442
1443
1444
1445 -- Bug 3270283. For LPN inspections, we need to default the receiving
1446 -- subinventory and Locator for the transfer LPN, if its a newly
1447 -- created one OR, it has a LPN context other than 'Resides in receiving'.
1448 -- The new procedure DEFAULT_LPN_SUB_LOC_INFO() takes care of this
1449 -- defaulting logic entirely. Hence just call this procedure if its
1450 -- a LPN inspection. kabalakr Mon Mar 8 08:01:35 PST 2004.
1451
1452 IF X_LPN_ID IS NOT NULL THEN
1453
1454 DEFAULT_LPN_SUB_LOC_INFO(X_LPN_ID,
1455 X_XFR_LPN_ID,
1456 l_rti_int_txn_id);
1457
1458 END IF; -- If x_lpn_id is not null*/
1459 -- End bug 6781108
1460
1461
1462 -- Bug 3096256. Changes for RCV/WMS Merge.
1463 -- Lot and Serial Inpsections are enabled in Quality from 11.5.10.
1464 -- For this, we need to call the APIs provided by WMS to insert the
1465 -- lot and serial information onto mtl_transaction_lots_interface
1466 -- (MTLI) and mtl_serial_numbers_interface (MSNI). This data would
1467 -- be used by th PO API which performs the inpsection.
1468 --
1469 -- NOTE :
1470 -- For Lot and Serial controlled items, the output variable
1471 -- x_serial_transaction_temp_id of INSERT_MLTI API needs to be passed
1472 -- as the p_transaction_interface_id of INSERT_MSNI API.
1473 -- kabalakr Mon Aug 18 03:18:19 PDT 2003.
1474
1475
1476 IF X_LOT_NUMBER IS NOT NULL THEN
1477
1478 OPEN item_uom_cur(X_ITEM_ID, X_ORG_ID);
1479 FETCH item_uom_cur INTO l_primary_uom;
1480 CLOSE item_uom_cur;
1481
1482 IF (l_primary_uom = X_UOM) THEN
1483 l_primary_qty := X_QUANTITY;
1484
1485 ELSE
1486 l_primary_qty := inv_convert.inv_um_convert
1487 (X_ITEM_ID,
1488 NULL,
1489 X_QUANTITY,
1490 X_UOM,
1491 l_primary_uom,
1492 NULL,
1493 NULL);
1494
1495 END IF;
1496
1497 l_int_txn_id := NULL;
1498
1499 -- Now, call the Inventory/WMS API for Lot Insertion.
1500 -- Passing NULL value to p_transaction_interface_id to allow the
1501 -- API to generate one. Bug 3096256.
1502
1503 -- Bug 3225280. Changed the value passed as p_product_transaction_id
1504 -- to l_rti_int_txn_id, derived above.
1505
1506 INV_RCV_INTEGRATION_APIS.INSERT_MTLI
1507 (p_api_version => 1.0,
1508 p_init_msg_lst => NULL,
1509 x_return_status => X_RETURN_STATUS,
1510 x_msg_count => X_MSG_COUNT,
1511 x_msg_data => X_MSG_DATA,
1512 p_transaction_interface_id => l_int_txn_id,
1513 p_transaction_quantity => X_QUANTITY,
1514 p_primary_quantity => l_primary_qty,
1515 p_organization_id => X_ORG_ID,
1516 p_inventory_item_id => X_ITEM_ID,
1517 p_lot_number => X_LOT_NUMBER,
1518 p_expiration_date => NULL,
1519 p_status_id => NULL,
1520 x_serial_transaction_temp_id => l_ser_txn_id,
1521 p_product_code => 'RCV',
1522 p_product_transaction_id => l_rti_int_txn_id);
1523
1524
1525 IF X_RETURN_STATUS <> 'S' THEN
1526 RAISE fail_lot_insertion;
1527 END IF;
1528
1529 END IF;
1530
1531
1532 IF X_SERIAL_NUMBER IS NOT NULL THEN
1533
1534 IF X_LOT_NUMBER IS NOT NULL THEN
1535 l_int_txn_id := l_ser_txn_id;
1536
1537 ELSE
1538 l_int_txn_id := NULL;
1539
1540 END IF;
1541
1542 -- Now, call the Inventory/WMS API for Serial Insertion.
1543 -- Passing NULL value to p_transaction_interface_id to allow the
1544 -- API to generate one. Bug 3096256.
1545
1546 -- Bug 3225280. Changed the value passed as p_product_transaction_id
1547 -- to l_rti_int_txn_id, derived above.
1548
1549 INV_RCV_INTEGRATION_APIS.INSERT_MSNI
1550 (p_api_version => 1.0,
1551 p_init_msg_lst => NULL,
1552 x_return_status => X_RETURN_STATUS,
1553 x_msg_count => X_MSG_COUNT,
1554 x_msg_data => X_MSG_DATA,
1555 p_transaction_interface_id => l_int_txn_id,
1556 p_fm_serial_number => X_SERIAL_NUMBER,
1557 p_to_serial_number => X_SERIAL_NUMBER,
1558 p_organization_id => X_ORG_ID,
1559 p_inventory_item_id => X_ITEM_ID,
1560 p_status_id => NULL,
1561 p_product_code => 'RCV',
1562 p_product_transaction_id => l_rti_int_txn_id);
1563
1564
1565 IF X_RETURN_STATUS <> 'S' THEN
1566 RAISE fail_serial_insertion;
1567 END IF;
1568
1569 END IF;
1570
1571 EXCEPTION
1572
1573 WHEN null_txn_id THEN
1574 --dbms_output.put_line('x progress is '||to_char(x_progress));
1575 X_ERROR := FALSE;
1576 FND_MESSAGE.SET_NAME('QA', 'QA_PO_INSP_ACTION_NULL_TXN_ID');
1577 X_message := FND_MESSAGE.GET;
1578 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1579 X_CREATION_DATE,char_id_tab(i),
1580 operator_tab(i),low_value_tab(i),
1581 high_value_tab(i),X_message,
1582 result,X_CONCURRENT);
1583
1584 WHEN column_missing THEN
1585 --dbms_output.put_line('x progress is '||to_char(x_progress));
1586 X_ERROR := FALSE;
1587 FND_MESSAGE.SET_NAME('QA', 'QA_PO_INSP_ACTION_COL_MISSING');
1588 X_message := FND_MESSAGE.GET;
1589 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1590 X_CREATION_DATE,char_id_tab(i),
1591 operator_tab(i),low_value_tab(i),
1592 high_value_tab(i),X_message,
1593 result,X_CONCURRENT);
1594
1595 WHEN fail_po_insertion THEN
1596 --dbms_output.put_line('x progress is '||to_char(x_progress));
1597 X_ERROR := FALSE;
1598 FND_MESSAGE.SET_NAME('QA', 'QA_PO_INSP_ACTION_FAIL');
1599 X_message := FND_MESSAGE.GET;
1600 X_message := X_message || X_msg_data;
1601 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1602 X_CREATION_DATE,char_id_tab(i),
1603 operator_tab(i),low_value_tab(i),
1604 high_value_tab(i),X_message,
1605 result,X_CONCURRENT);
1606
1607 WHEN fail_serial_insertion THEN
1608 X_ERROR := FALSE;
1609 FND_MESSAGE.SET_NAME('QA', 'QA_WMS_SER_INSERT_FAIL');
1610 X_message := FND_MESSAGE.GET;
1611 X_message := X_message || X_msg_data;
1612 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1613 X_CREATION_DATE,char_id_tab(i),
1614 operator_tab(i),low_value_tab(i),
1615 high_value_tab(i),X_message,
1616 result,X_CONCURRENT);
1617
1618 WHEN fail_lot_insertion THEN
1619 X_ERROR := FALSE;
1620 FND_MESSAGE.SET_NAME('QA', 'QA_WMS_LOT_INSERT_FAIL');
1621 X_message := FND_MESSAGE.GET;
1622 X_message := X_message || X_msg_data;
1623 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1624 X_CREATION_DATE,char_id_tab(i),
1625 operator_tab(i),low_value_tab(i),
1626 high_value_tab(i),X_message,
1627 result,X_CONCURRENT);
1628
1629
1630 WHEN OTHERS THEN
1631 --dbms_output.put_line('x progress is '||to_char(x_progress));
1632 -- write exceptions to action log;
1633 X_ERROR := FALSE;
1634 X_message := FND_MESSAGE.GET;
1635 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1636 X_CREATION_DATE,char_id_tab(i),
1637 operator_tab(i),low_value_tab(i),
1638 high_value_tab(i),X_message,
1639 result,X_CONCURRENT);
1640 END; -- insert inspection
1641
1642 END IF; -- inspection transaction
1643
1644 -- Launch a Workflow
1645 ELSIF action_id_tab(i) = 28 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
1646
1647 BEGIN
1648 OPEN workflow_itemtype(pca_id_tab(i));
1649 FETCH workflow_itemtype INTO X_WORKFLOW_ITEMTYPE;
1650 CLOSE workflow_itemtype;
1651
1652 OPEN wf_itemtype_selector(X_WORKFLOW_ITEMTYPE);
1653 FETCH wf_itemtype_selector
1654 INTO X_WORKFLOW_ITEMTYPE_SELECTOR;
1655 CLOSE wf_itemtype_selector;
1656
1657 OPEN wf_number_of_processes (X_WORKFLOW_ITEMTYPE);
1658 FETCH wf_number_of_processes
1659 INTO X_WORKFLOW_NUMBER_OF_PROCESSES;
1660 CLOSE wf_number_of_processes;
1661
1662 -- Raise an exception only if number of processes is not
1663 -- equal to 1 (more than one) and there is no selector
1664 -- function defined. Please refer to bug # 1330038
1665 --
1666 -- ORASHID
1667
1668 IF (X_WORKFLOW_NUMBER_OF_PROCESSES > 1)
1669 AND X_WORKFLOW_ITEMTYPE_SELECTOR IS NULL THEN
1670 raise wf_missing_selector;
1671 ELSE
1672 launch_workflow(pca_id_tab(i),
1673 X_WORKFLOW_ITEMTYPE, Y_PLAN_ID,
1674 X_WORKFLOW_NUMBER_OF_PROCESSES);
1675 END IF;
1676
1677 EXCEPTION
1678 WHEN WF_MISSING_SELECTOR THEN
1679 X_ERROR := FALSE;
1680 X_message := 'Found no SELECTOR for ' || X_WORKFLOW_ITEMTYPE;
1681 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1682 X_CREATION_DATE,char_id_tab(i),
1683 operator_tab(i),low_value_tab(i),
1684 high_value_tab(i),X_message,
1685 result,X_CONCURRENT);
1686 WHEN OTHERS THEN
1687 -- write exceptions to action log;
1688 X_ERROR := FALSE;
1689 errmsg := substr(SQLERRM,1,240);
1690 errcode :=SQLCODE;
1691 X_message := 'QA ' || to_char(errcode) || ':' || errmsg;
1692 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1693 X_CREATION_DATE,char_id_tab(i),
1694 operator_tab(i),low_value_tab(i),
1695 high_value_tab(i),X_message,
1696 result,X_CONCURRENT);
1697 END;
1698
1699
1700
1701
1702 --Create work request added by kaza
1703 ELSIF action_id_tab(i) = 29 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
1704 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
1705 X_OCCURRENCE := to_number(QLTNINRB.NAME_IN('OCCURRENCE'));
1706 --dgupta: Start R12 EAM Integration. Bug 4345492
1707 X_ASSET_INSTANCE_ID := QLTNINRB.NAME_IN('ASSET_INSTANCE_ID');
1708 if (X_ASSET_INSTANCE_ID is null) then
1709 X_ASSET_GROUP_ID := to_number(QLTNINRB.NAME_IN('ASSET_GROUP_ID'));
1710 X_ASSET_NUMBER := QLTNINRB.NAME_IN('ASSET_NUMBER');
1711 end if;
1712 --dgupta: End R12 EAM Integration. Bug 4345492
1713
1714 -- check to see if priority element exists in the plan
1715
1716 Priority_exists := qa_plan_element_api.element_in_plan(Y_PLAN_ID, ELEMENT_ID);
1717
1718
1719 if (Priority_exists = TRUE) then
1720 -- get the soft column name mapped to the 'priority' element.
1721 OPEN Get_result_column_name (ELEMENT_ID, Y_PLAN_ID);
1722 FETCH Get_result_column_name INTO Priority_soft_column;
1723 CLOSE Get_result_column_name;
1724
1725 Priority_value := QLTNINRB.NAME_IN(Priority_soft_column);
1726
1727 if (Priority_value is not null) then
1728 OPEN Get_priority_id (Priority_value);
1729 FETCH Get_priority_id INTO Priority_id;
1730 CLOSE Get_priority_id;
1731 else
1732 Priority_id := 1;
1733 end if;
1734
1735 else
1736 Priority_id := 1;
1737
1738 end if;
1739
1740 -- get user id
1741 user_id := NVL(FND_PROFILE.VALUE('USER_ID'), 0);
1742
1743 -- build the message for request_log by concatenating fnd_message and user input text message.
1744
1745 -- Commented the usage of the cursor get_user_message and added code to use the cursor
1746 -- my_message, as the cursor get_user_message was showing up the message of the first
1747 -- trigger action of an element only in the EAM work requests even though the second or
1748 -- third or any other trigger condition is satisfied for the element.Refer bug for more
1749 -- details.
1750 -- Bug 3416961.suramasw.
1751
1752 /*
1753 OPEN Get_user_message(char_id_tab(i), Y_PLAN_ID, action_id_tab(i));
1754 FETCH Get_user_message INTO x_message;
1755 CLOSE Get_user_message;
1756 */
1757
1758 OPEN MY_MESSAGE(pca_id_tab(i));
1759 FETCH MY_MESSAGE INTO X_MESSAGE, X_ASSIGN_TYPE;
1760 CLOSE MY_MESSAGE;
1761
1762 request_log := fnd_message.get_string('QA', 'QA_WORK_REQUEST_LOG');
1763 request_log := request_log || ' ' || x_message;
1764
1765
1766
1767 -- call the EAM api
1768
1769 BEGIN
1770
1771 WIP_EAM_WORKREQUEST_PVT.create_and_approve(
1772 p_api_version => 1.0,
1773 p_init_msg_list => fnd_api.g_false,
1774 p_commit => fnd_api.g_true,
1775 p_validation_level => fnd_api.g_valid_level_full,
1776 p_org_id => X_org_id,
1777 p_asset_group_id => X_ASSET_GROUP_ID,
1778 p_asset_number => X_ASSET_NUMBER,
1779 p_maintenance_object_id => X_ASSET_INSTANCE_ID, --dgupta: R12 EAM Integration. Bug 4345492
1780 p_priority_id => Priority_id,
1781 p_request_by_date => sysdate,
1782 p_request_log => request_log,
1783 p_owning_dept_id => null, -- Owning_dept_id,
1784 p_user_id => user_id,
1785 p_work_request_type_id => null,
1786 p_asset_location => null,
1787 p_expected_resolution_date => null,
1788 p_work_request_created_by => 2,
1789 x_work_request_id => request_id,
1790 x_resultout => l_resultout,
1791 x_error_message => l_error_message,
1792 x_return_status => x_return_status,
1793 x_msg_count => x_msg_count,
1794 x_msg_data => x_msg_data
1795 ) ;
1796
1797
1798
1799
1800 EXCEPTION
1801 WHEN OTHERS THEN
1802 -- write exceptions to action log;
1803 X_ERROR := FALSE;
1804 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1805 X_CREATION_DATE,char_id_tab(i),
1806 operator_tab(i),low_value_tab(i),
1807 high_value_tab(i),X_message,
1808 result,X_CONCURRENT);
1809 END;
1810
1811
1812
1813 --Create work order added by rkaza
1814
1815 -- EAM rebuild tracking bug 3133312. 09/22/2003.
1816 -- Modified the action to pass in rebuild item information to the
1817 -- work order API, if the asset group entered is a rebuild item.
1818 -- Also passing followup activity now as primary item, instead of
1819 -- asset activity. Asset activity is the activity of the parent
1820 -- work order. Followup activity will be the activity of the new
1821 -- work order being created.
1822
1823
1824 ELSIF action_id_tab(i) = 30 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
1825
1826 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
1827 --dgupta: Start R12 EAM Integration. Bug 4345492
1828 X_ASSET_INSTANCE_ID := QLTNINRB.NAME_IN('ASSET_INSTANCE_ID');
1829
1830 if (X_ASSET_INSTANCE_ID is not null) then
1831 l_work_order_rec.maintenance_object_id := X_ASSET_INSTANCE_ID;
1832 l_work_order_rec.maintenance_object_type := 3;
1833 l_work_order_rec.asset_group_id := to_number(QLTNINRB.NAME_IN('ASSET_GROUP_ID'));
1834 l_work_order_rec.asset_number := QLTNINRB.NAME_IN('ASSET_NUMBER');
1835 else
1836 X_ASSET_GROUP_ID := to_number(QLTNINRB.NAME_IN('ASSET_GROUP_ID'));
1837 X_ASSET_NUMBER := QLTNINRB.NAME_IN('ASSET_NUMBER');
1838 if (X_ASSET_GROUP_ID is not null and X_ASSET_NUMBER is null) then
1839 l_work_order_rec.maintenance_object_id := X_ASSET_GROUP_ID;
1840 l_work_order_rec.rebuild_item_id :=X_ASSET_GROUP_ID;
1841 l_work_order_rec.maintenance_object_type := 2;
1842 elsif (X_ASSET_GROUP_ID is not null) then
1843 l_work_order_rec.maintenance_object_id :=
1844 qa_plan_element_api.get_asset_instance_id(X_ASSET_GROUP_ID, X_ASSET_NUMBER);
1845 l_work_order_rec.maintenance_object_type := 3;
1846 l_work_order_rec.asset_group_id := to_number(QLTNINRB.NAME_IN('ASSET_GROUP_ID'));
1847 l_work_order_rec.asset_number := QLTNINRB.NAME_IN('ASSET_NUMBER');
1848
1849 end if;
1850 end if;
1851
1852 l_followup_activity_id := QLTNINRB.NAME_IN('FOLLOWUP_ACTIVITY_ID');
1853 --dgupta: End R12 EAM Integration. Bug 4345492
1854 l_work_order_rec.primary_item_id := l_followup_activity_id;
1855
1856 l_work_order_rec.last_update_date := fnd_date.chardt_to_date(QLTNINRB.NAME_IN('QA_CREATION_DATE'));
1857 l_work_order_rec.last_updated_by := QLTNINRB.NAME_IN('LAST_UPDATED_BY');
1858 l_work_order_rec.creation_date := x_creation_date;
1859 l_work_order_rec.created_by := QLTNINRB.NAME_IN('CREATED_BY');
1860 l_work_order_rec.last_update_login := QLTNINRB.NAME_IN('LAST_UPDATE_LOGIN');
1861
1862 l_work_order_rec.organization_id := X_org_id;
1863 l_work_order_rec.load_type := 7;
1864 l_work_order_rec.wip_supply_type := 7;
1865
1866 -- l_work_order_rec.firm_planned_flag := 1;
1867
1868 OPEN Get_eam_firm_flag(X_org_id);
1869 FETCH Get_eam_firm_flag INTO l_firm_flag;
1870 CLOSE Get_eam_firm_flag;
1871
1872 if l_firm_flag = 'Y' then
1873 l_work_order_rec.firm_planned_flag := 1;
1874 else
1875 l_work_order_rec.firm_planned_flag := 2;
1876 end if;
1877
1878 l_work_order_rec.process_phase := 2;
1879 l_work_order_rec.process_status := 1;
1880 l_work_order_rec.scheduling_method := 2;
1881 l_work_order_rec.net_quantity := 1;
1882 l_work_order_rec.status_type := 3;
1883 l_work_order_rec.first_unit_start_date := sysdate;
1884 l_work_order_rec.last_unit_start_date := sysdate;
1885
1886 -- call the EAM api
1887 BEGIN
1888
1889 WIP_EAMWORKORDER_PVT.Create_EAM_Work_Order
1890 ( p_api_version => 1.0,
1891 p_init_msg_list => FND_API.G_FALSE,
1892 p_commit => FND_API.G_FALSE,
1893 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1894 x_return_status => X_RETURN_STATUS,
1895 x_msg_count => X_MSG_COUNT,
1896 x_msg_data => X_MSG_DATA,
1897 p_work_order_rec => l_work_order_rec,
1898 x_group_id => l_group_id,
1899 x_request_id => request_id
1900 );
1901
1902
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 -- write exceptions to action log;
1906 X_ERROR := FALSE;
1907 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1908 X_CREATION_DATE,char_id_tab(i),
1909 operator_tab(i),low_value_tab(i),
1910 high_value_tab(i),X_message,
1911 result,X_CONCURRENT);
1912 END;
1913
1914
1915
1916 -- assign ASL status
1917 ELSIF action_id_tab(i) = 27 and nvl(x_action_type, 'DEFERRED') = 'DEFERRED' THEN
1918 X_message := 'ACTION PROCESS DBG, ASSIGNING ASL STATUS';
1919 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1920 X_CREATION_DATE,0,
1921 0,'',
1922 '',X_message,
1923 '',X_CONCURRENT);
1924 -- get ASL status
1925 OPEN MY_STATUS(pca_id_tab(i));
1926 FETCH MY_STATUS INTO X_STATUS;
1927 CLOSE MY_STATUS ;
1928 -- get the vendor , item and org id
1929 X_org_id := to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID'));
1930 X_Vendor_id := to_number(QLTNINRB.NAME_IN('VENDOR_ID'));
1931 X_Item_id := to_number (QLTNINRB.Name_in('ITEM_ID' )) ;
1932
1933 -- call PO API
1934 BEGIN
1935 PO_ASL_SV.Update_Vendor_Status (x_org_id, x_vendor_id,
1936 X_Status, NULL, x_item_id,'N', NULL, x_asl_return);
1937 EXCEPTION
1938 WHEN OTHERS THEN
1939 -- write exceptions to action log;
1940 X_ERROR := FALSE;
1941 X_message := FND_MESSAGE.GET;
1942 INSERT_ACTION_LOG(Y_PLAN_ID,X_COLLECTION_ID,
1943 X_CREATION_DATE,char_id_tab(i),
1944 operator_tab(i),low_value_tab(i),
1945 high_value_tab(i),X_message,
1946 result,X_CONCURRENT);
1947 END;
1948
1949 END IF; -- switch on action_id
1950
1951 i := i + 1;
1952 IF i > total_rows THEN
1953 done := TRUE;
1954 ELSIF pcat_id_tab(i) <> pcat_id_tab(i-1) THEN
1955 done := TRUE;
1956 END IF;
1957
1958 END LOOP; --a given action rule is true: loop to perform all actions
1959
1960 IF i <= total_rows THEN
1961 done := FALSE;
1962 END IF;
1963
1964 -- process all action rules with the same sequence id
1965 IF (not done) THEN
1966 IF (seq_id_tab(i) <> seq_id_tab(i-1) AND
1967 char_id_tab(i) = char_id_tab(i-1)) THEN
1968 WHILE (not done) LOOP
1969 IF char_id_tab(i) = char_id_tab(i-1) THEN
1970 i := i + 1;
1971 IF i > total_rows THEN
1972 done := TRUE;
1973 END IF;
1974 ELSE
1975 done := TRUE;
1976 END IF;
1977 END LOOP;
1978 END IF; -- current seq num not the same as the last seq num
1979 END IF; -- not done
1980
1981 ELSE
1982 i := i + 1;
1983 END IF; -- IF qltcompb.compare
1984
1985 END LOOP;
1986
1987 OLD_SPEC_ID := Y_SPEC_ID;
1988 OLD_PLAN_ID := Y_PLAN_ID;
1989
1990 END LOOP; -- i <= total_rows
1991
1992 QLTNINRB.CLOSE_CURSOR;
1993 RETURN(X_ERROR);
1994
1995 EXCEPTION
1996
1997 When Others THEN
1998 --dbms_output.put_line('x progress is '||to_char(x_progress));
1999 QLTNINRB.CLOSE_CURSOR;
2000 raise;
2001 END DO_ACTIONS;
2002
2003
2004 PROCEDURE launch_workflow(X_PCA_ID NUMBER,
2005 X_WF_ITEMTYPE VARCHAR2,
2006 X_PLAN_ID NUMBER,
2007 X_WORKFLOW_PROCESSES VARCHAR2) IS
2008
2009 -- Bug 2671638. Added 'datatype' in the select clause
2010 -- rponnusa Wed Nov 20 04:33:03 PST 2002
2011
2012 CURSOR OUTPUTS IS
2013 SELECT NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
2014 qpcao.token_name TOKEN_NAME,
2015 qc.fk_lookup_type LOOKUP_TYPE,
2016 qc.char_id CHAR_ID,
2017 qc.datatype DATATYPE
2018 from qa_chars qc,
2019 qa_plan_chars qpc,
2020 qa_plan_char_action_outputs qpcao,
2021 qa_plan_char_actions qpca,
2022 qa_plan_char_action_triggers qpcat
2023 where qc.char_id = qpcao.char_id
2024 and qpc.char_id = qpcao.char_id
2025 and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
2026 and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
2027 and qpc.plan_id = qpcat.plan_id
2028 and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
2029 and qc.char_context_flag <> 3
2030 UNION SELECT qc.hardcoded_column Q_COLUMN,
2031 qpcao.token_name TOKEN_NAME,
2032 qc.fk_lookup_type LOOKUP_TYPE,
2033 qc.char_id CHAR_ID,
2034 qc.datatype DATATYPE
2035 from qa_chars qc,
2036 qa_plan_char_action_outputs qpcao,
2037 qa_plan_char_actions qpca,
2038 qa_plan_char_action_triggers qpcat
2039 where qc.char_id = qpcao.char_id
2040 and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
2041 and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
2042 and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
2043 and qc.char_context_flag = 3;
2044
2045 CURSOR wf_item_attributes_cursor IS
2046 SELECT name
2047 FROM wf_item_attributes wia
2048 WHERE wia.item_type = X_WF_ITEMTYPE;
2049
2050 -- Bug 4958762: SQL Repository Fix SQL ID: 15008550
2051 CURSOR token_column_datatype (t_char_id number) IS
2052 SELECT
2053 qc.datatype
2054 FROM qa_plan_chars qpc,
2055 qa_chars qc
2056 WHERE qc.char_id = t_char_id
2057 AND qpc.char_id = qc.char_id
2058 AND qpc.plan_id = X_PLAN_ID;
2059 /*
2060 SELECT qpcv.datatype
2061 FROM qa_plan_chars_v qpcv
2062 WHERE qpcv.char_id = t_char_id
2063 AND qpcv.plan_id = X_PLAN_ID;
2064 */
2065
2066 Cursor l_WorkFlowItemKey IS
2067 select qa_action_workflow_s.nextval
2068 from dual;
2069
2070 TYPE wf_attributes_table IS TABLE OF WF_ITEM_ATTRIBUTES.NAME%TYPE
2071 INDEX BY BINARY_INTEGER;
2072
2073 Cursor wf_number_of_processes IS
2074 select process_name
2075 from wf_runnable_processes_v
2076 where item_type = X_WF_ITEMTYPE;
2077
2078 wf_itemattributes WF_ATTRIBUTES_TABLE;
2079 i BINARY_INTEGER := 1;
2080 token_datatype NUMBER;
2081 token_value VARCHAR2(2500);
2082 l_wf_itemkey VARCHAR2(240);
2083 l_wf_process_name VARCHAR2(30) DEFAULT NULL;
2084 l_pca_id_exists BOOLEAN := FALSE;
2085
2086 BEGIN
2087
2088 FOR attr_rec IN wf_item_attributes_cursor LOOP
2089 wf_itemattributes(i) := attr_rec.name;
2090 i := i + 1;
2091 END LOOP;
2092
2093 OPEN l_WorkflowItemKey;
2094 FETCH l_WorkflowItemKey INTO l_wf_itemkey;
2095 CLOSE l_WorkflowItemKey;
2096
2097 l_wf_itemkey := 'QAACTION' || l_wf_itemkey;
2098
2099 -- We will be here in two scenarios
2100 --
2101 -- 1. Number of processes is more than one and there is a selector function
2102 -- in this scenario, we will simply put a NULL in the process_name
2103 -- variable.
2104 --
2105 -- 2. Number of processes is equal to one. In this case, we will compute
2106 -- the process name and populate the process_name variable with it.
2107 --
2108 -- Please refer to bug # 1330038
2109 --
2110 -- ORASHID
2111
2112
2113 IF (x_workflow_processes = 1) THEN
2114 OPEN wf_number_of_processes;
2115 FETCH wf_number_of_processes INTO l_wf_process_name;
2116 CLOSE wf_number_of_processes;
2117 END IF;
2118
2119 WF_ENGINE.CreateProcess(
2120 itemtype => X_WF_ITEMTYPE,
2121 itemkey => l_wf_itemkey,
2122 process => l_wf_process_name);
2123
2124 FOR prec IN outputs LOOP
2125
2126 FOR i IN wf_itemattributes.FIRST..wf_itemattributes.LAST LOOP
2127
2128 -- Bug 2671638. Find out pca_id attribute available in workflow or not
2129
2130 IF wf_itemattributes(i) = 'PCA_ID' THEN
2131 l_pca_id_exists := TRUE;
2132 END IF;
2133
2134 IF prec.token_name = wf_itemattributes(i) THEN
2135
2136 -- Bug 2671638. comment out token_column_datatype cursor since it is not needed.
2137 /*
2138 OPEN token_column_datatype(prec.char_id);
2139 FETCH token_column_datatype INTO token_datatype;
2140 CLOSE token_column_datatype;
2141 */
2142
2143 token_datatype := prec.datatype;
2144
2145 token_value := QLTNINRB.NAME_IN(prec.Q_COLUMN);
2146
2147 -- Convert all normalized element ID's into its values.
2148 -- ex. Item_id,locator_id converted to item_name, locator respectively
2149 -- For performance reason, dont call qltsmenb for char_id 2,39
2150
2151 IF (prec.LOOKUP_TYPE in( 0,1,3) AND prec.char_id NOT IN (2, 39)) THEN
2152 token_value := QLTSMENB.LOOKUP(prec.CHAR_ID, token_value);
2153 END IF;
2154
2155 -- Bug 2671638. Extending support to Sequence and Longcomment datatypes.
2156 IF token_datatype IN (1,4,5) THEN
2157
2158 WF_ENGINE.SetItemAttrText(
2159 itemtype => X_WF_ITEMTYPE,
2160 itemkey => l_wf_itemkey,
2161 aname => prec.token_name,
2162 avalue => token_value);
2163
2164 ELSIF token_datatype = 2 THEN
2165
2166 WF_ENGINE.SetItemAttrNumber(
2167 itemtype => X_WF_ITEMTYPE,
2168 itemkey => l_wf_itemkey,
2169 aname => prec.token_name,
2170 avalue => fnd_number.canonical_to_number(token_value));
2171
2172 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
2173 -- Added datetime data type
2174 ELSIF token_datatype IN (3,6)THEN
2175
2176 WF_ENGINE.SetItemAttrDate(
2177 itemtype => X_WF_ITEMTYPE,
2178 itemkey => l_wf_itemkey,
2179 aname => prec.token_name,
2180 avalue => fnd_date.canonical_to_date(token_value));
2181 END IF;
2182
2183 exit;
2184 END IF;
2185 END LOOP;
2186
2187 END LOOP;
2188
2189 -- Bug 2671638. Added following IF. rponnusa Wed Nov 20 04:33:03 PST 2002
2190
2191 IF l_pca_id_exists THEN
2192
2193 WF_ENGINE.SetItemAttrNumber(
2194 itemtype => X_WF_ITEMTYPE,
2195 itemkey => l_wf_itemkey,
2196 aname => 'PCA_ID',
2197 avalue => fnd_number.canonical_to_number(X_PCA_ID));
2198 END IF;
2199
2200 WF_ENGINE.StartProcess(
2201 itemtype => X_WF_ITEMTYPE,
2202 itemkey => l_wf_itemkey);
2203
2204
2205 END launch_workflow;
2206
2207
2208
2209 PROCEDURE FIRE_ALERT(X_PCA_ID NUMBER) IS
2210
2211 TYPE chartable IS TABLE OF VARCHAR2(240)
2212 INDEX BY BINARY_INTEGER;
2213
2214
2215 X_OUTPUTS chartable;
2216 i BINARY_INTEGER := 0;
2217 total_rows BINARY_INTEGER := 96;
2218 X_ACTION_SET_NAME VARCHAR2(50);
2219 X_REQUEST_ID NUMBER;
2220 ACTUAL_OUTPUT VARCHAR2(2100);
2221
2222
2223 CURSOR OUTPUTS IS
2224 SELECT NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
2225 qpcao.token_name TOKEN_NAME,
2226 qc.fk_lookup_type LOOKUP_TYPE,
2227 qc.char_id CHAR_ID
2228 from qa_chars qc,
2229 qa_plan_chars qpc,
2230 qa_plan_char_action_outputs qpcao,
2231 qa_plan_char_actions qpca,
2232 qa_plan_char_action_triggers qpcat
2233 where qc.char_id = qpcao.char_id
2234 and qpc.char_id = qpcao.char_id
2235 and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
2236 and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
2237 and qpc.plan_id = qpcat.plan_id
2238 and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
2239 and qc.char_context_flag <> 3
2240 UNION SELECT qc.hardcoded_column Q_COLUMN,
2241 qpcao.token_name TOKEN_NAME,
2242 qc.fk_lookup_type LOOKUP_TYPE,
2243 qc.char_id CHAR_ID
2244 from qa_chars qc,
2245 qa_plan_char_action_outputs qpcao,
2246 qa_plan_char_actions qpca,
2247 qa_plan_char_action_triggers qpcat
2248 where qc.char_id = qpcao.char_id
2249 and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
2250 and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
2251 and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
2252 and qc.char_context_flag = 3;
2253
2254 CURSOR ACTION_SET_NAME IS
2255 SELECT aas.NAME
2256 FROM QA_PLAN_CHAR_ACTIONS qpca,
2257 ALR_ACTION_SETS aas
2258 WHERE qpca.PLAN_CHAR_ACTION_ID = X_PCA_ID
2259 AND qpca.ALR_ACTION_SET_ID = aas.ACTION_SET_ID
2260 AND aas.APPLICATION_ID = 250;
2261
2262 -- Kashyap. For Bug2408728.
2263 l_count NUMBER;
2264 l_size NUMBER;
2265 SUFFIXSTRING VARCHAR2(10) := '_QAKM_Z';
2266
2267 -- suramasw.Bug 2921276.Wed Jul 30 05:15:40 PDT 2003
2268 l_occurrence NUMBER;
2269
2270 -- suramasw.Bug 3162828.
2271
2272 CURSOR CPLAN(l_pca_id number) IS
2273 SELECT plan_id FROM qa_plan_char_action_triggers
2274 WHERE plan_char_action_trigger_id = (select PLAN_CHAR_ACTION_TRIGGER_ID
2275 FROM QA_PLAN_CHAR_ACTIONS WHERE PLAN_CHAR_ACTION_ID = l_pca_id);
2276
2277 CURSOR RESULT_COLUMN(l_plan_id number, l_pca_id number) IS
2278 SELECT result_column_name FROM qa_plan_chars
2279 WHERE plan_id = l_plan_id AND char_id = ( SELECT char_id
2280 FROM qa_plan_char_action_triggers WHERE
2281 plan_char_action_trigger_id = (SELECT plan_char_action_trigger_id
2282 FROM qa_plan_char_actions WHERE plan_char_action_id = l_pca_id));
2283
2284 l_result_column_name VARCHAR2(100);
2285 l_result_column_value VARCHAR2(2000);
2286 l_plan_id NUMBER;
2287 l_sql_string VARCHAR2(1000);
2288
2289 BEGIN
2290
2291 -- First we get the Action_set_name
2292 open ACTION_SET_NAME;
2293 FETCH ACTION_SET_NAME INTO X_ACTION_SET_NAME;
2294 close ACTION_SET_NAME;
2295
2296 -- suramasw.Bug 2921276.Wed Jul 30 05:15:40 PDT 2003
2297 l_occurrence := TO_NUMBER(QLTNINRB.NAME_IN('OCCURRENCE'));
2298
2299 -- Next we get any outputs to the alert
2300 FOR prec in OUTPUTS LOOP
2301 i := i + 1;
2302 ACTUAL_OUTPUT := QLTNINRB.NAME_IN(prec.Q_COLUMN);
2303 IF (prec.LOOKUP_TYPE = 0) or (prec.LOOKUP_TYPE = 1) or
2304 (prec.LOOKUP_TYPE = 3) THEN
2305 ACTUAL_OUTPUT := QLTSMENB.LOOKUP(prec.CHAR_ID, ACTUAL_OUTPUT);
2306 end IF;
2307
2308 /*kashyap, rkaza 06/14/2002. as part of fix for Bug 2408728 */
2309 IF (qa_chars_api.datatype(prec.char_id) = 4) THEN
2310
2311 -- Bug 2640953. If the ACTUAL_OUTPUT is NULL, set the l_count to 0.
2312 -- Added the NVL() below. kabalakr.
2313
2314 l_count := NVL(ROUND(LENGTH(ACTUAL_OUTPUT)/200) + 1, 0);
2315 l_size := 0;
2316
2317 FOR x IN 1..l_count LOOP
2318 X_OUTPUTS(i) := prec.TOKEN_NAME || SUFFIXSTRING || to_char(x)||'=' ||
2319 substr(ACTUAL_OUTPUT,l_size, 200);
2320
2321 l_size := l_size + 200 ;
2322 i := i + 1;
2323 END LOOP;
2324
2325 FOR y IN (l_count+1)..10 LOOP
2326 X_OUTPUTS(i) := prec.TOKEN_NAME || SUFFIXSTRING || to_char(y)||'=' ||'';
2327 i := i + 1;
2328 END LOOP;
2329
2330 i := i - 1;
2331 ELSE
2332 X_OUTPUTS(i) := prec.TOKEN_NAME || '=' || ACTUAL_OUTPUT;
2333 END IF;
2334
2335 END LOOP;
2336
2337 -- suramasw.Bug 2921276.Wed Jul 30 05:15:40 PDT 2003
2338 -- QA_PLAN_CHAR_VALUE and QA_OCCURRENCE will be passed as parameter
2339 -- to ALECDC. QA_PLAN_CHAR_VALUE will hold the value of the token
2340 -- and QA_OCCURRENCE will hold the occurrence.From now only when
2341 -- the collection element which has the action associated with it
2342 -- is updated in UQR the action will fire for that collection element.
2343
2344 -- i := i + 1;
2345
2346 /*
2347 Added the following piece of code and commented the code added as
2348 a part Bug 2921276. X_OUTPUTS(i+1) had been assigned with wrong
2349 value(ACTUAL_OUTPUT) as part of the fix done for 2921276.
2350
2351 Functionality after the fix
2352 --------------------------------
2353 Identify the collection element value(l_result_column_value) and
2354 occurrence(l_occurrence) for every update. The values will be passed
2355 to alerts which takes the combination of l_result_column_value and
2356 l_occurrence and fires the action if that particular combination is
2357 not already available. But before this some mandatory steps are needed
2358 in Oracle Alerts which is specified in the ARU readme.
2359
2360 Bug 3162828.suramasw.
2361 */
2362
2363 OPEN CPLAN(x_pca_id);
2364 fetch CPLAN into l_plan_id;
2365 close CPLAN;
2366
2367 OPEN RESULT_COLUMN(l_plan_id,x_pca_id);
2368 FETCH RESULT_COLUMN INTO l_result_column_name;
2369 CLOSE RESULT_COLUMN;
2370
2371 l_sql_string := 'SELECT '|| l_result_column_name ||' FROM QA_RESULTS '||
2372 'WHERE PLAN_ID = :l_plan_id AND OCCURRENCE = :l_occurrence';
2373
2374 EXECUTE IMMEDIATE l_sql_string into l_result_column_value
2375 USING l_plan_id,l_occurrence;
2376
2377 X_OUTPUTS(i+1) := 'QA_PLAN_CHAR_VALUE='|| l_result_column_value;
2378 X_OUTPUTS(i+2) := 'QA_OCCURRENCE='|| l_occurrence;
2379
2380 /*
2381 X_OUTPUTS(i+1) := 'QA_PLAN_CHAR_VALUE='|| ACTUAL_OUTPUT;
2382 X_OUTPUTS(i+2) := 'QA_OCCURRENCE='|| l_occurrence;
2383 */
2384
2385 i := i + 3;
2386
2387 -- End of inclusions for Bug 2921276.
2388
2389 -- Now we add a chr(0) to signIFy a end of outputs
2390 IF i <= total_rows THEN
2391 X_OUTPUTS(i) := chr(0);
2392 i := i + 1;
2393 end IF;
2394
2395 -- Now we add nulls to the rest of the X_OUTPUTS
2396 WHILE i <= total_rows LOOP
2397 X_OUTPUTS(i) := null;
2398 i := i + 1;
2399 end LOOP;
2400
2401 -- Bug 4210833.suramasw.
2402 -- Included arguments X_OUTPUTS(41) to X_OUTPUTS(50) in the following call to
2403 -- ALECDC. Since those 10 arguments were missed, if the call from Quality to
2404 -- Alert actions exceeds 40 arguments then the 'Check Periodic Alert' concurrent
2405 -- request fails with the error reported in the bug.
2406
2407 -- now we call the alert API
2408 X_REQUEST_ID := fnd_request.submit_request('ALR','ALECDC', null,
2409 null, FALSE, '250', '10177',
2410 'A',X_ACTION_SET_NAME,
2411 X_OUTPUTS(1), X_OUTPUTS(2),
2412 X_OUTPUTS(3), X_OUTPUTS(4),
2413 X_OUTPUTS(5), X_OUTPUTS(6),
2414 X_OUTPUTS(7), X_OUTPUTS(8),
2415 X_OUTPUTS(9), X_OUTPUTS(10),
2416 X_OUTPUTS(11), X_OUTPUTS(12),
2417 X_OUTPUTS(13), X_OUTPUTS(14),
2418 X_OUTPUTS(15), X_OUTPUTS(16),
2419 X_OUTPUTS(17), X_OUTPUTS(18),
2420 X_OUTPUTS(19), X_OUTPUTS(20),
2421 X_OUTPUTS(21), X_OUTPUTS(22),
2422 X_OUTPUTS(23), X_OUTPUTS(24),
2423 X_OUTPUTS(25), X_OUTPUTS(26),
2424 X_OUTPUTS(27), X_OUTPUTS(28),
2425 X_OUTPUTS(29), X_OUTPUTS(30),
2426 X_OUTPUTS(31), X_OUTPUTS(32),
2427 X_OUTPUTS(33), X_OUTPUTS(34),
2428 X_OUTPUTS(35), X_OUTPUTS(36),
2429 X_OUTPUTS(37), X_OUTPUTS(38),
2430 X_OUTPUTS(39), X_OUTPUTS(40),
2431 X_OUTPUTS(41), X_OUTPUTS(42),
2432 X_OUTPUTS(43), X_OUTPUTS(44),
2433 X_OUTPUTS(45), X_OUTPUTS(46),
2434 X_OUTPUTS(47), X_OUTPUTS(48),
2435 X_OUTPUTS(49), X_OUTPUTS(50),
2436 X_OUTPUTS(51), X_OUTPUTS(52),
2437 X_OUTPUTS(53), X_OUTPUTS(54),
2438 X_OUTPUTS(55), X_OUTPUTS(56),
2439 X_OUTPUTS(57), X_OUTPUTS(58),
2440 X_OUTPUTS(59), X_OUTPUTS(60),
2441 X_OUTPUTS(61), X_OUTPUTS(62),
2442 X_OUTPUTS(63), X_OUTPUTS(64),
2443 X_OUTPUTS(65), X_OUTPUTS(66),
2444 X_OUTPUTS(67), X_OUTPUTS(68),
2445 X_OUTPUTS(69), X_OUTPUTS(70),
2446 X_OUTPUTS(71), X_OUTPUTS(72),
2447 X_OUTPUTS(73), X_OUTPUTS(74),
2448 X_OUTPUTS(75), X_OUTPUTS(76),
2449 X_OUTPUTS(77), X_OUTPUTS(78),
2450 X_OUTPUTS(79), X_OUTPUTS(80),
2451 X_OUTPUTS(81), X_OUTPUTS(82),
2452 X_OUTPUTS(83), X_OUTPUTS(84),
2453 X_OUTPUTS(85), X_OUTPUTS(86),
2454 X_OUTPUTS(87), X_OUTPUTS(88),
2455 X_OUTPUTS(89), X_OUTPUTS(90),
2456 X_OUTPUTS(91), X_OUTPUTS(92),
2457 X_OUTPUTS(93), X_OUTPUTS(94),
2458 X_OUTPUTS(95), X_OUTPUTS(96));
2459
2460 END FIRE_ALERT;
2461
2462 --
2463 -- Bug 4751249
2464 -- Added a new procedure that will derive the normalized value
2465 -- based on the assigned column and the denormalized value passed
2466 -- ntungare Sat Nov 26 00:20:36 PST 2005
2467 --
2468 PROCEDURE GET_DERIVED_VALUE(assigned_col VARCHAR2,
2469 denorm_val IN OUT NOCOPY VARCHAR2) AS
2470 BEGIN
2471 --
2472 -- bug 7552682
2473 -- Added the COMP_ITEM_ID
2474 -- ntungare
2475 --
2476 -- IF assigned_col = 'ITEM_ID' THEN
2477 IF assigned_col IN ( 'ITEM_ID', 'COMP_ITEM_ID') THEN
2478 denorm_val := to_char(QA_FLEX_UTIL.get_item_id(to_number(QLTNINRB.NAME_IN('ORGANIZATION_ID')),denorm_val));
2479 ELSIF assigned_col = 'VENDOR_ID' THEN
2480 denorm_val := qa_plan_element_api.get_supplier_id(denorm_val);
2481 END IF;
2482 END;
2483
2484 PROCEDURE DO_ASSIGNMENT(X_PCA_ID NUMBER,
2485 X_MESSAGE VARCHAR2,
2486 X_ASSIGNED_CHAR_ID NUMBER,
2487 X_COLLECTION_ID NUMBER,
2488 X_OCCURRENCE NUMBER,
2489 X_PLAN_ID NUMBER,
2490 X_SQL_STATEMENT OUT NOCOPY VARCHAR2) IS
2491
2492 CURSOR OUTPUTS IS
2493 SELECT NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
2494 qpcao.token_name TOKEN_NAME,
2495 qc.fk_lookup_type LOOKUP_TYPE,
2496 qc.char_id CHAR_ID
2497 from qa_chars qc,
2498 qa_plan_chars qpc,
2499 qa_plan_char_actions qpca,
2500 qa_plan_char_action_triggers qpcat,
2501 qa_plan_char_action_outputs qpcao
2502 where qc.char_id = qpcao.char_id
2503 and qpc.char_id = qpcao.char_id
2504 and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
2505 and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
2506 and qpc.plan_id = qpcat.plan_id
2507 and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
2508 and qc.char_context_flag <> 3
2509 UNION SELECT qc.hardcoded_column Q_COLUMN,
2510 qpcao.token_name TOKEN_NAME,
2511 qc.fk_lookup_type LOOKUP_TYPE,
2512 qc.char_id CHAR_ID
2513 from qa_chars qc,
2514 qa_plan_char_actions qpca,
2515 qa_plan_char_action_triggers qpcat,
2516 qa_plan_char_action_outputs qpcao
2517 where qc.char_id = qpcao.char_id
2518 and qpcao.PLAN_CHAR_ACTION_ID = qpca.PLAN_CHAR_ACTION_ID
2519 and qpcat.PLAN_CHAR_ACTION_TRIGGER_ID = qpca.PLAN_CHAR_ACTION_TRIGGER_ID
2520 and qpcao.PLAN_CHAR_ACTION_ID = X_PCA_ID
2521 and qc.char_context_flag = 3;
2522
2523 -- Bug 4958762: SQL Repository Fix SQL ID: 15008686
2524 CURSOR ASSIGNED_COLUMN (t_char_id number) IS
2525 SELECT
2526 NVL(qc.hardcoded_column,qpc.result_column_name) Q_COLUMN,
2527 qc.datatype,
2528 DECODE(qpc.decimal_precision, NULL, qc.decimal_precision, qpc.decimal_precision)
2529 FROM qa_plan_chars qpc,
2530 qa_chars qc
2531 WHERE qc.char_id = qpc.char_id
2532 AND qc.char_id = t_char_id
2533 AND qpc.plan_id = X_PLAN_ID;
2534 /*
2535 SELECT NVL(qpcv.hardcoded_column,qpcv.result_column_name) Q_COLUMN,
2536 qpcv.datatype, qpcv.decimal_precision
2537 FROM qa_plan_chars_v qpcv
2538 WHERE qpcv.char_id = t_char_id
2539 AND qpcv.plan_id = X_PLAN_ID;
2540 */
2541
2542 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2543 -- Increased the width of sql_stmt, final_stmt and update_stmt to 2500 from
2544 -- 2000. If the one of the target elements is a comment datatype and has a
2545 -- value of approx 2000 characters then these variables would not be able to
2546 -- hold the whole string and would raise an exception. So increased the width.
2547 sql_stmt VARCHAR2(2500) := X_MESSAGE;
2548 final_stmt VARCHAR2(2500) := '';
2549 update_stmt VARCHAR2(2500);
2550 len NUMBER;
2551 i NUMBER := 1;
2552 j NUMBER;
2553 k NUMBER := 1; --enumerated bind variable counter
2554 curr_char VARCHAR2(30);
2555 token_name VARCHAR2(30);
2556 token_char_id NUMBER;
2557
2558 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2559 -- Increased the column width of token_column_value from 150 to 2000.
2560 -- If the value of token_column_value which is going to be copied to
2561 -- the target element is more than 150 characters then ORA-06502
2562 -- would be raised. To prevent that column width has been increased.
2563 token_column_value VARCHAR2(2000);
2564
2565 bind_var_name VARCHAR2(150);
2566 y_column VARCHAR2(30);
2567
2568 y_datatype NUMBER;
2569 y_deciprec NUMBER;
2570 c1 NUMBER;
2571 ignore NUMBER;
2572
2573 --
2574 -- Bug 2976810
2575 -- to get rid of a sql bind compliance exemption, the token values used in
2576 -- an assign a value action are now accumulated in an array and passed in as
2577 -- bind variables instead of as literals
2578 -- ilawler Tue May 27 13:34:49 2003
2579 --
2580 TYPE tokenValTab IS TABLE OF token_column_value%TYPE INDEX BY BINARY_INTEGER;
2581 token_vals tokenValTab;
2582
2583 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2584 -- Increased the column width of return_value_char from 1500 to
2585 -- 2000 for the same reason mentioned above for token_column_value.
2586 return_value_char VARCHAR2(2000);
2587
2588 return_value_num NUMBER;
2589 return_value_date DATE;
2590 find_token BOOLEAN := FALSE;
2591
2592 BEGIN
2593
2594 len := length (sql_stmt);
2595
2596 WHILE i <= len LOOP
2597 curr_char := substr(sql_stmt, i, 1);
2598
2599 IF curr_char <> '&' THEN
2600 final_stmt := final_stmt || curr_char;
2601 i := i + 1;
2602
2603 ELSE -- we're at an ampersand
2604 i := i + 1; -- skip over ampersand
2605 token_name := '';
2606 curr_char := substr(sql_stmt, i, 1);
2607
2608 WHILE curr_char between '0' and '9'
2609 or curr_char between 'A' and 'Z'
2610 or curr_char between 'a' and 'z'
2611 LOOP
2612 token_name := token_name || curr_char;
2613 i := i + 1;
2614 curr_char := substr(sql_stmt, i, 1);
2615 END LOOP;
2616
2617 find_token := FALSE;
2618 FOR prec in OUTPUTS LOOP
2619 EXIT WHEN find_token;
2620 IF UPPER(token_name) = UPPER(prec.token_name) THEN
2621 token_char_id := prec.char_id;
2622 token_column_value := QLTNINRB.NAME_IN(prec.Q_COLUMN);
2623 IF (prec.LOOKUP_TYPE = 0) or (prec.LOOKUP_TYPE = 1) or
2624 (prec.LOOKUP_TYPE = 3) THEN
2625 token_column_value := QLTSMENB.LOOKUP(prec.CHAR_ID,
2626 token_column_value);
2627 END IF;
2628 find_token := TRUE;
2629 END IF;
2630 END LOOP;
2631
2632 IF NOT find_token THEN -- it doesn't find match in the output
2633 --token_column_value := '&'||token_name; -- just don't substitude
2634 APP_EXCEPTION.RAISE_EXCEPTION;
2635 END IF;
2636
2637 OPEN ASSIGNED_COLUMN (token_char_id);
2638 FETCH ASSIGNED_COLUMN
2639 INTO y_column, y_datatype, y_deciprec;
2640 CLOSE ASSIGNED_COLUMN;
2641
2642 bind_var_name := ':' || k;
2643
2644 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2645 -- Included comment datatype(y_datatype=4) in the following IF loop.
2646 -- Before this fix if we try to assign a value to a comment datatype
2647 -- element the action would fire but the value would not be copied
2648 -- to the target element (or) would error out in Collection Import.
2649 if y_datatype in (1,4) then
2650 bind_var_name := bind_var_name;
2651 elsif y_datatype = 2 then
2652 bind_var_name:= 'nvl(qltdate.canon_to_number(' || bind_var_name || '), 0)';
2653 elsif y_datatype = 3 then
2654 bind_var_name := 'qltdate.any_to_date(' || bind_var_name || ')';
2655
2656 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
2657 elsif y_datatype = 6 then
2658 -- Bug 3211247. rponnusa Tue Oct 28 23:19:18 PST 2003
2659 /*
2660 IF y_column LIKE 'CHARACTER%' THEN
2661 bind_var_name := 'qltdate.canon_to_date(' || bind_var_name || ')';
2662 ELSE
2663 bind_var_name := bind_var_name;
2664 END IF;
2665 */
2666 bind_var_name := 'qltdate.any_to_datetime(' || bind_var_name || ')';
2667 end if;
2668
2669 --add the token value to the token value array and append the bind variable string to the statement
2670 token_vals(k) := token_column_value;
2671 final_stmt := final_stmt || bind_var_name ;
2672 X_SQL_STATEMENT := final_stmt;
2673
2674 k := k + 1;
2675
2676 END IF; -- curr_char <> '&'
2677 END LOOP; -- while i < len
2678
2679 -- parse the sql_statement, and get the final value
2680 -- assign value to the corresponding column
2681 -- update the table with the assigned value
2682 BEGIN
2683
2684 OPEN ASSIGNED_COLUMN (X_ASSIGNED_CHAR_ID);
2685 FETCH ASSIGNED_COLUMN
2686 INTO y_column, y_datatype, y_deciprec;
2687 CLOSE ASSIGNED_COLUMN;
2688
2689 c1 := dbms_sql.open_cursor;
2690 dbms_sql.parse(c1, final_stmt, dbms_sql.native);
2691
2692 --go through the token_vals array and do the bindings
2693 k := token_vals.FIRST;
2694 WHILE (k IS NOT NULL) LOOP
2695 dbms_sql.bind_variable(c1, ':' || to_char(k), token_vals(k));
2696 k := token_vals.NEXT(k);
2697 END LOOP;
2698
2699 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2700 -- Included comment datatype(y_datatype=4) in the following IF loop.
2701 -- Before this fix if we try to assign a value to a comment datatype
2702 -- element the action would fire but the value would not be copied to
2703 -- the target element in Collection Import.
2704 -- Also increased the width of return_value_char from 1500 to 2000.
2705 IF y_datatype in (1,4) THEN
2706 dbms_sql.define_column(c1, 1, return_value_char, 2000);
2707 ELSIF y_datatype = 2 THEN
2708 dbms_sql.define_column(c1, 1, return_value_num);
2709
2710 ELSIF y_datatype = 3 THEN
2711 -- Bug 3213920. rponnusa Tue Oct 28 23:19:18 PST 2003
2712 -- define the column as date type
2713 dbms_sql.define_column(c1, 1, return_value_date);
2714
2715 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
2716
2717 ELSIF y_datatype = 6 THEN -- datetime
2718 dbms_sql.define_column(c1, 1, return_value_date);
2719 -- Bug 3211247. rponnusa Tue Oct 28 23:19:18 PST 2003
2720 -- convert to canon mask for softcoded elements.
2721 IF y_column LIKE 'CHARACTER%' THEN
2722 bind_var_name := 'qltdate.date_to_canon_dt(' || bind_var_name || ')';
2723 END IF;
2724
2725 END IF;
2726
2727 ignore := dbms_sql.execute(c1);
2728
2729 --
2730 -- Bug 2976810
2731 -- although this piece of SQL was not in the original plan, update it to use EXECUTE IMMEDIATE
2732 -- with bind variables instead of piecing the SQL together with string concats.
2733 -- Note: the column name is still hardcoded because you can't bind schema object names
2734 -- ilawler Tue May 27 13:34:49 2003
2735 --
2736 update_stmt := 'UPDATE qa_results SET ' || y_column || ' = ' ;
2737
2738 --set the default bind variable name
2739 bind_var_name := ':CHAR_VALUE';
2740
2741 IF dbms_sql.fetch_rows(c1)>0 THEN
2742
2743 --get the column's value into return_value_char|num and add a properly wrapped bind variable for it
2744
2745 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2746 -- Included y_datatype=4 also in the following loop for the same
2747 -- reason mentioned few lines above.
2748 IF y_datatype in (1,4) THEN
2749
2750 dbms_sql.column_value(c1, 1, return_value_char);
2751
2752 --
2753 -- Bug 4751249
2754 -- Made a call to the new procedure added that will get the
2755 -- normalized value for the assigned column using the
2756 -- denormalized value passed
2757 -- ntungare Sat Nov 26 00:19:07 PST 2005
2758 --
2759 GET_DERIVED_VALUE(y_column, return_value_char);
2760
2761 ELSIF y_datatype = 2 THEN
2762
2763 dbms_sql.column_value(c1, 1, return_value_num);
2764 return_value_num := round(return_value_num, nvl(y_deciprec, 0));
2765
2766 IF y_column LIKE 'CHARACTER%' THEN
2767 bind_var_name := 'qltdate.number_to_canon(' || bind_var_name || ')';
2768 END IF;
2769
2770 ELSIF y_datatype = 3 THEN
2771
2772 -- Bug 3213920. rponnusa Tue Oct 28 23:19:18 PST 2003
2773 -- fetch the col value as Date type
2774
2775 dbms_sql.column_value(c1, 1, return_value_date);
2776
2777 IF y_column LIKE 'CHARACTER%' THEN
2778 bind_var_name := 'qltdate.date_to_canon(' || bind_var_name || ')';
2779 END IF;
2780
2781 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
2782 ELSIF y_datatype = 6 THEN -- datetime
2783
2784 dbms_sql.column_value(c1, 1, return_value_date);
2785
2786 IF y_column LIKE 'CHARACTER%' THEN
2787 bind_var_name := 'qltdate.date_to_canon_dt(' || bind_var_name || ')';
2788 END IF;
2789
2790 END IF; -- IF y_datatype
2791 ELSE
2792 --
2793 -- Bug 1431126. If the user sql statement does not return
2794 -- any value, the action processor will fail. It is nicer
2795 -- to treat that as assigning a NULL. ("51" Bug 1432918)
2796 --
2797 -- also modify the target datatype so the execute immediate knows to use return_value_char
2798 y_datatype := 1;
2799 return_value_char := NULL;
2800
2801 END IF; -- IF fetch_rows
2802
2803 dbms_sql.close_cursor(c1);
2804
2805 --add the bind variable name and additional where clause params to the update statement
2806 update_stmt := update_stmt || bind_var_name || ' WHERE plan_id = :PLAN_ID AND '||
2807 'collection_id = :COLL_ID AND '||
2808 'occurrence = :OCCURRENCE';
2809
2810 --modify the bind variable values if the source is a number
2811 IF y_datatype = 2 THEN
2812 EXECUTE IMMEDIATE update_stmt USING return_value_num, X_PLAN_ID, X_COLLECTION_ID, X_OCCURRENCE;
2813
2814 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
2815 -- Added datetime data type
2816
2817 -- Bug 3213920. rponnusa Tue Oct 28 23:19:18 PST 2003
2818 -- Added date type
2819 ELSIF y_datatype IN (3, 6) THEN
2820 EXECUTE IMMEDIATE update_stmt USING
2821 return_value_date,
2822 X_PLAN_ID,
2823 X_COLLECTION_ID,
2824 X_OCCURRENCE;
2825
2826 ELSE
2827 EXECUTE IMMEDIATE update_stmt USING return_value_char, X_PLAN_ID, X_COLLECTION_ID, X_OCCURRENCE;
2828 END IF;
2829
2830 --make sure we modified a single row, otherwise raise exception
2831 IF SQL%ROWCOUNT <> 1 THEN
2832 APP_EXCEPTION.RAISE_EXCEPTION;
2833 END IF;
2834 --
2835 -- Bug 7491253. 12.1.1 FP for Bug 6599571
2836 -- Added this if-else ladder to update the record in session
2837 -- in order to fire the cascaded actions.
2838 -- skolluku
2839 IF y_datatype IN (1,4) AND (return_value_char IS NOT NULL) THEN
2840 qltninrb.set_value(y_column,return_value_char);
2841 END IF;
2842 IF y_datatype=2 and return_value_num is not null then
2843 qltninrb.set_value(y_column,to_char(return_value_num));
2844 END IF;
2845 IF y_datatype = 6 and return_value_date is not null then
2846 IF y_column LIKE 'CHARACTER%' THEN
2847 qltninrb.set_value(y_column,qltdate.date_to_canon_dt(return_value_date));
2848 ELSE
2849 qltninrb.set_value(y_column,to_char(return_value_date));
2850 END IF;
2851 END IF;
2852 IF y_datatype = 3 AND return_value_date IS NOT NULL THEN
2853 IF y_column LIKE 'CHARACTER%' THEN
2854 qltninrb.set_value(y_column,qltdate.date_to_canon(return_value_date));
2855 ELSE
2856 qltninrb.set_value(y_column,to_char(return_value_date));
2857 END IF;
2858 END IF;
2859 -- End of bug 7491253
2860 END;
2861
2862 END DO_ASSIGNMENT;
2863
2864
2865 -- Bug 3270283. This procedure takes care of defaulting the receiving subinventory
2866 -- and locator values to the transfer LPN from the parent LPN. The inputs to this
2867 -- procedure are -
2868 -- X_LPN_ID : Parent LPN_ID
2869 -- X_XFR_LPN_ID : Transfer LPN_ID
2870 -- X_TRANSACTION_ID : Interface Transaction id of the Inspection record inserted
2871 -- into RTI by RCV API.
2872 --
2873 -- The logic followed is as follows -
2874 -- 1. Fetch the LPN context, Sub and Loc info for the Transfer LPN.
2875 -- 2. If the LPN context is 'Resides in Receiving', then keep the Sub and Loc info.
2876 -- 3. If not 'Resides in Receiving', then fetch the Sub and Loc info of the parent LPN.
2877 -- 4. Update RTI for the interface_transaction_id (X_TRANSACTION_ID), with the
2878 -- values for Sub and Loc.
2879 --
2880 -- kabalakr Mon Mar 8 08:01:35 PST 2004.
2881 --
2882 -- Bug 6781108
2883 -- Deleted the Transaction_id and Converted the two variables
2884 -- l_rti_sub_code and l_rti_loc_id as out parameters
2885 -- pdube Wed Feb 6 04:53:32 PST 2008
2886 PROCEDURE DEFAULT_LPN_SUB_LOC_INFO(X_LPN_ID NUMBER,
2887 X_XFR_LPN_ID NUMBER,
2888 -- X_TRANSACTION_ID NUMBER
2889 l_rti_sub_code OUT NOCOPY mtl_secondary_inventories.secondary_inventory_name%TYPE,
2890 l_rti_loc_id OUT NOCOPY NUMBER) IS
2891
2892 l_lpn_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
2893 l_lpn_loc_id NUMBER;
2894 l_xfer_lpn_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
2895 l_xfer_lpn_loc_id NUMBER;
2896 l_xfer_lpn_ctxt NUMBER;
2897 -- Bug 6781108
2898 -- Commenting the following two variables
2899 -- l_rti_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE;
2900 -- l_rti_loc_id NUMBER;
2901
2902 BEGIN
2903
2904 IF (x_lpn_id IS NOT NULL) THEN
2905
2906 BEGIN
2907 SELECT lpn_context
2908 , subinventory_code
2909 , locator_id
2910 INTO l_xfer_lpn_ctxt
2911 , l_xfer_lpn_sub
2912 , l_xfer_lpn_loc_id
2913 FROM wms_license_plate_numbers
2914 WHERE lpn_id = x_xfr_lpn_id;
2915
2916
2917 -- If Transfer LPN resides in Receiving, then keep the
2918 -- values for Receiving Sub and Loc. Else, derive it from
2919 -- the parent LPN.
2920
2921 IF (NVL(l_xfer_lpn_ctxt, 5) = 3) THEN
2922 l_rti_sub_code := l_xfer_lpn_sub;
2923 l_rti_loc_id := l_xfer_lpn_loc_id;
2924
2925 ELSE
2926
2927 -- Transfer LPN has been generated newly, so we need to default the RTI
2928 -- with the sub/locator of the parent LPN
2929
2930 BEGIN
2931 SELECT subinventory_code
2932 , locator_id
2933 INTO l_lpn_sub
2934 , l_lpn_loc_id
2935 FROM wms_license_plate_numbers
2936 WHERE lpn_id = x_lpn_id;
2937
2938 l_rti_sub_code := l_lpn_sub;
2939 l_rti_loc_id := l_lpn_loc_id;
2940
2941 EXCEPTION
2942 WHEN OTHERS THEN
2943 l_rti_sub_code := NULL;
2944 l_rti_loc_id := NULL;
2945 END;
2946 END IF; --END IF check xfer lpn context
2947
2948 EXCEPTION
2949 WHEN OTHERS THEN
2950 l_rti_sub_code := NULL;
2951 l_rti_loc_id := NULL;
2952 END;
2953
2954
2955 -- Now, we have derived the Subinventory and Locator info for the Transfer
2956 -- LPN. We need to update the RTI record with these values.
2957
2958 -- Bug 6781108
2959 -- Commenting the following update statement as
2960 -- the receiving API is handling this scenario.
2961 -- pdube Wed Feb 6 04:51:22 PST 2008
2962 -- UPDATE RCV_TRANSACTIONS_INTERFACE
2963 -- SET subinventory = l_rti_sub_code,
2964 -- locator_id = l_rti_loc_id
2965 -- WHERE interface_transaction_id = X_TRANSACTION_ID;
2966
2967
2968 END IF;
2969
2970
2971 END DEFAULT_LPN_SUB_LOC_INFO;
2972
2973 -- 12.1 QWB Usability Improvements
2974 -- Function to replace tokens defined in an
2975 -- action message
2976 FUNCTION replace_tokens(p_plan_char_action_id IN NUMBER,
2977 p_message_str IN VARCHAR2,
2978 p_assign_type IN VARCHAr2,
2979 P_assigned_elem_type IN NUMBER)
2980 RETURN VARCHAR2 AS
2981 Type token_rec IS record(token_name VARCHAR2(100),
2982 char_name VARCHAR2(100));
2983 Type token_rec_tab_typ IS TABLE OF token_rec INDEX BY binary_integer;
2984 token_rec_tab token_rec_tab_typ;
2985
2986 l_message_str VARCHAR2(4000);
2987 BEGIN
2988 -- fetching the token names and the elements
2989 -- they are mapped to
2990 --
2991 -- bug 6904497
2992 -- Ordering the tokens in the decreasing
2993 -- order of their length before replacement
2994 -- to ensure that the longest tokens get replaced
2995 -- first.
2996 -- ntungare
2997 --
2998 SELECT TRIM(token_name) tokenName,
2999 'CHARID' || char_id bulk collect
3000 INTO token_rec_tab
3001 FROM qa_plan_char_action_outputs
3002 WHERE plan_char_action_id = p_plan_char_action_id
3003 order by length(tokenName) desc ;
3004
3005 l_message_str := p_message_str;
3006 FOR cntr IN 1 .. token_rec_tab.COUNT
3007 LOOP
3008 -- Performing a case Insensitive replacement
3009 -- of the tokens with the field names
3010 --
3011 SELECT REGEXP_REPLACE(l_message_str,
3012 '&' || token_rec_tab(cntr).token_name,
3013 '&' || token_rec_tab(cntr).char_name || ';',
3014 1,
3015 0,
3016 'i')
3017 INTO l_message_str
3018 FROM dual;
3019 END LOOP;
3020 RETURN l_message_str;
3021 END replace_tokens;
3022
3023 -- 12.1 QWB Usability Improvements
3024 -- Function to compute the lower limit for
3025 -- an acion trigger.
3026 FUNCTION low_val(p_plan_id in NUMBER,
3027 p_spec_id in NUMBER,
3028 p_char_id in number,
3029 p_char_type in number,
3030 p_lowval_lookup in NUMBER,
3031 p_highval_lookup in NUMBER,
3032 p_char_uom in VARCHAR2,
3033 p_plan_uom in VARCHAR2,
3034 p_precision in NUMBER)
3035 RETURN VARCHAR2 AS
3036 low_val NUMBER;
3037 high_val NUMBER;
3038 BEGIN
3039 -- Processing for Numeric elements
3040 IF (p_char_type =2) THEN
3041 -- Fetching the low values using the limit of the spec selected
3042 -- or the specs defined on the collection element.
3043 -- If the spec_id is not null which means that a user defined
3044 -- spec has been selected then the API performs the UOM conversions
3045 -- as well
3046 qa_plan_element_api.get_low_high_values(
3047 p_plan_id, p_spec_id, p_char_id,
3048 p_lowval_lookup, p_highval_lookup,
3049 low_val, high_val);
3050
3051 -- If the low and high values are based on the spec limits defined
3052 -- on the collection element level and the element is not of the same
3053 -- UOM as that of the collection plan then the UOM conversion needs to
3054 -- be done.
3055 IF (p_char_uom <> p_plan_uom and
3056 P_spec_id = 0) THEN
3057 low_val := qa_plan_element_api.perform_uom_conversion(
3058 p_source_val => low_val ,
3059 p_precision => p_precision,
3060 p_source_UOM => p_char_uom,
3061 p_target_UOM => p_plan_uom);
3062
3063 END IF;
3064 ELSE
3065 -- If the low value is not numeric then it is returned as is
3066 -- need to check what format to return for dates.
3067 low_val := p_lowval_lookup;
3068 END IF;
3069
3070 RETURN TO_CHAR(low_val);
3071 END low_val;
3072
3073 -- 12.1 QWB Usability Improvements
3074 -- Function to compute the upper limit for
3075 -- an acion trigger.
3076
3077 FUNCTION high_val(p_plan_id in NUMBER,
3078 p_spec_id in NUMBER,
3079 p_char_id in number,
3080 p_char_type in number,
3081 p_lowval_lookup in NUMBER,
3082 p_highval_lookup in NUMBER,
3083 p_char_uom in VARCHAR2,
3084 p_plan_uom in VARCHAR2,
3085 p_precision in NUMBER)
3086 RETURN VARCHAR2 AS
3087 low_val NUMBER;
3088 high_val NUMBER;
3089 BEGIN
3090 -- If the p_highval_lookup is NULL then no processing
3091 -- is needed
3092 IF p_highval_lookup IS NOT NULL THEN
3093 IF (p_char_type =2) THEN
3094
3095 -- Fetching the high values using the limits of the spec selected
3096 -- or the specs defined on the collection element.
3097 -- If the spec_id is not null which means that a user defined
3098 -- spec has been selected then the API performs the UOM conversions
3099 -- as well
3100 qa_plan_element_api.get_low_high_values(
3101 p_plan_id, p_spec_id, p_char_id,
3102 p_lowval_lookup, p_highval_lookup,
3103 low_val, high_val);
3104
3105 -- If the low and high values are based on the spec limits defined
3106 -- on the collection element level and the element is not of the same
3107 -- UOM as that of the collection plan then the UOM conversion needs to
3108 -- be done.
3109 IF (p_char_uom <> p_plan_uom and
3110 P_spec_id = 0) THEN
3111 high_val := qa_plan_element_api.perform_uom_conversion(
3112 p_source_val => high_val ,
3113 p_precision => p_precision,
3114 p_source_UOM => p_char_uom,
3115 p_target_UOM => p_plan_uom);
3116 END IF;
3117 ELSE
3118 -- If the low value is not numeric then it is returned as is
3119 -- need to check what format to return for dates.
3120 high_val := p_highval_lookup;
3121 END IF;
3122
3123 RETURN TO_CHAR(high_val);
3124 ELSE
3125 RETURN NULL;
3126 END IF;
3127 END high_val;
3128
3129
3130
3131 END QLTDACTB;
3132
[Home] [Help]