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