1 PACKAGE WSMPUTIL AS
2 /* $Header: WSMUTILS.pls 120.1.12010000.3 2008/10/22 10:35:57 ybabulal ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : wsmutilb.pls |
9 | Description : Contains the following procedures :
10 | FUNCTION CHECK_WSM_ORG
11 | PROCEDURE find_routing_start - overridden
12 | PROCEDURE find_routing_end - overridden
13 | FUNCTION GET_SCHEDULED_DATE - overridden
14 | FUNCTION GET_DEF_ACCT_CLASS_CODE
15 | PROCEDURE GET_DEF_COMPLETION_SUB_DTLS - overridden
16 | FUNCTION primary_loop_test
17 | PROCEDURE GET_DEFAULT_SUB_LOC
18 | PROCEDURE UPDATE_SUB_LOC
19 | FUNCTION CHECK_IF_ORG_IS_VALID
20 | PROCEDURE WRITE_TO_WIE
21 | PROCEDURE find_common_routing
22 | FUNCTION get_routing_start
23 | FUNCTION get_routing_end
24 | FUNCTION CHECK_COPROD_RELATION
25 | FUNCTION CHECK_COPROD_COMP_RELATION
26 | FUNCTION CHECK_COPROD_RELATION
27 | FUNCTION CHECK_100_PERCENT
28 | PROCEDURE AUTONOMOUS_TXN
29 | PROCEDURE OPERATION_IS_STANDARD_REPEATS - overridden
30 | PROCEDURE validate_non_std_references
31 | FUNCTION WSM_ESA_ENABLED
32 | FUNCTION WSM_CHANGE_ESA_FLAG
33 | FUNCTION network_with_disabled_op
34 | FUNCTION primary_path_is_effective_till
35 | FUNCTION effective_next_op_exists
36 | FUNCTION effective_next_op_exits
37 | FUNCTION wlt_if_costed
38 | PROCEDURE check_charges_exist
39 | FUNCTION replacement_op_seq_id
40 | FUNCTION check_po_move
41 | PROCEDURE validate_lbj_before_close
42 | PROCEDURE get_Kanban_rec_grp_info
43 | PROCEDURE get_max_kanban_asmbly_qty
44 | PROCEDURE return_att_quantity
45 | FUNCTION check_osp_operation
46 | FUNCTION CHECK_WLMTI - overridden
47 | FUNCTION CHECK_WMTI - overridden
48 | FUNCTION CHECK_WSMT - overridden
49 | FUNCTION CHECK_WMT
50 | FUNCTION CHECK_WSMTI
51 | FUNCTION JOBS_WITH_QTY_AT_FROM_OP - overridden
52 | FUNCTION CREATE_LBJ_COPY_RTG_PROFILE - overridden
53 | FUNCTION GET_INV_ACCT_PERIOD
54 | PROCEDURE AUTONOMOUS_WRITE_TO_WIE
55 | FUNCTION GET_JOB_BOM_SEQ_ID
56 | FUNCTION replacement_copy_op_seq_id
57 | FUNCTION get_internal_copy_type
58 | PROCEDURE lock_wdj
59 | |
60 | Revision |
61 | 04/24/00 Anirban Dey Initial Creation |
62 +==========================================================================*/
63
64
65 l_debug VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
66
67
68
69 FUNCTION CHECK_WSM_ORG (
70 p_organization_id IN NUMBER,
71 x_err_code OUT NOCOPY NUMBER,
72 x_err_msg OUT NOCOPY VARCHAR2
73 ) RETURN INTEGER;
74
75
76 PROCEDURE find_routing_start (
77 p_routing_sequence_id NUMBER,
78 start_op_seq_id OUT NOCOPY NUMBER,
79 x_err_code OUT NOCOPY NUMBER,
80 x_err_msg OUT NOCOPY VARCHAR2 );
81
82 -- BA: CZH.I_OED-1
83 PROCEDURE find_routing_start (
84 p_routing_sequence_id NUMBER,
85 p_routing_rev_date DATE,
86 start_op_seq_id OUT NOCOPY NUMBER,
87 x_err_code OUT NOCOPY NUMBER,
88 x_err_msg OUT NOCOPY VARCHAR2 );
89 -- EA: CZH.I_OED-1
90
91
92 PROCEDURE find_routing_end (
93 p_routing_sequence_id NUMBER,
94 end_op_seq_id OUT NOCOPY NUMBER,
95 x_err_code OUT NOCOPY NUMBER,
96 x_err_msg OUT NOCOPY VARCHAR2 );
97
98 -- BA: CZH.I_OED-1
99 PROCEDURE find_routing_end (
100 p_routing_sequence_id NUMBER,
101 p_routing_rev_date DATE,
102 end_op_seq_id OUT NOCOPY NUMBER,
103 x_err_code OUT NOCOPY NUMBER,
104 x_err_msg OUT NOCOPY VARCHAR2 );
105 -- EA: CZH.I_OED-1
106
107
108 --
109 -- This is an over-loaded function which calls the same function
110 -- with p_quantity parameter.
111 --
112 -- This is created to circumvent the dependency issues esp. with forms
113 --
114
115 FUNCTION GET_SCHEDULED_DATE
116 (
117 p_organization_id IN NUMBER,
118 p_primary_item_id IN NUMBER,
119 p_schedule_method IN VARCHAR2,
120 p_input_date IN DATE,
121 x_err_code OUT NOCOPY NUMBER,
122 x_err_msg OUT NOCOPY VARCHAR2
123 )
124 RETURN DATE;
125
126 --
127 -- Since this is an overloaded function, we shouldn't have
128 -- DEFAULT clause on p_quantity. Else, you'll get the following error
129 -- while calling this function.
130 -- PLS-00307: too many declarations of 'GET_SCHEDULED_DATE'
131 -- match this call
132 --
133
134 FUNCTION GET_SCHEDULED_DATE (
135 p_organization_id IN NUMBER,
136 p_primary_item_id IN NUMBER,
137 p_schedule_method IN VARCHAR2,
138 p_input_date IN DATE,
139 x_err_code OUT NOCOPY NUMBER,
140 x_err_msg OUT NOCOPY VARCHAR2,
141 p_quantity IN NUMBER --Fixed bug #2313574
142 ) RETURN DATE;
143
144
145
146 FUNCTION GET_DEF_ACCT_CLASS_CODE (
147 p_organization_id IN NUMBER,
148 p_inventory_item_id IN NUMBER,
149 p_subinventory_name IN VARCHAR2,
150 x_err_code OUT NOCOPY NUMBER,
151 x_err_msg OUT NOCOPY VARCHAR2
152 ) RETURN VARCHAR2;
153
154
155 PROCEDURE GET_DEF_COMPLETION_SUB_DTLS (
156 p_organization_id IN NUMBER,
157 p_routing_sequence_id IN NUMBER,
158 x_subinventory_code OUT NOCOPY VARCHAR2,
159 x_locator_id OUT NOCOPY NUMBER,
160 x_err_code OUT NOCOPY NUMBER,
161 x_err_msg OUT NOCOPY VARCHAR2
162 );
163
164 -- CZH: overloading function
165 PROCEDURE GET_DEF_COMPLETION_SUB_DTLS (
166 p_organization_id IN NUMBER,
167 p_routing_sequence_id IN NUMBER,
168 p_routing_revision_date IN DATE,
169 x_subinventory_code OUT NOCOPY VARCHAR2,
170 x_locator_id OUT NOCOPY NUMBER,
171 x_err_code OUT NOCOPY NUMBER,
172 x_err_msg OUT NOCOPY VARCHAR2
173 );
174
175 FUNCTION primary_loop_test(
176 p_routing_sequence_id NUMBER,
177 start_id NUMBER,
178 end_id NUMBER,
179 x_err_code OUT NOCOPY NUMBER,
180 x_err_msg OUT NOCOPY VARCHAR2
181 ) RETURN NUMBER;
182
183
184 PROCEDURE GET_DEFAULT_SUB_LOC (
185 p_org_id IN NUMBER ,
186 p_routing_sequence_id IN NUMBER,
187 p_end_id IN NUMBER,
188 x_completion_subinventory OUT NOCOPY VARCHAR2,
189 x_inventory_location_id OUT NOCOPY NUMBER,
190 x_err_code OUT NOCOPY NUMBER,
191 x_err_msg OUT NOCOPY VARCHAR2 );
192
193
194 PROCEDURE UPDATE_SUB_LOC (
195 p_routing_sequence_id IN NUMBER,
196 p_completion_subinventory IN VARCHAR2,
197 p_inventory_location_id IN NUMBER,
198 x_err_code OUT NOCOPY NUMBER,
199 x_err_msg OUT NOCOPY VARCHAR2 );
200
201 /*BA#1577747*/
202 /* Given a routing sequence id, this procedure will find the
203 ** root of the common routing sequence id associated with it.
204 */
205
206 PROCEDURE find_common_routing (
207 p_routing_sequence_id IN NUMBER,
208 p_common_routing_sequence_id OUT NOCOPY NUMBER,
209 x_err_code OUT NOCOPY NUMBER,
210 x_err_msg OUT NOCOPY VARCHAR2 );
211 /*EA#1577747*/
212
213 /*
214 ** Function to check if an Organization is eligible to be a
215 ** WSM Organization. The following checks are done;
216 ** 1. Org should be a standard costing org.
217 ** 2. Org should have the item lot number uniqueness set to NONE.
218 ** 3. Org should have WIP Parameters lotnumber default type set to
219 ** JobName.
220 */
221
222 FUNCTION CHECK_IF_ORG_IS_VALID (
223 p_organization_id IN NUMBER,
224 x_err_code OUT NOCOPY NUMBER,
225 x_err_msg OUT NOCOPY VARCHAR2
226 ) RETURN INTEGER;
227
228
229
230 PROCEDURE WRITE_TO_WIE (
231 p_header_id IN NUMBER,
232 p_message IN VARCHAR2,
233 p_request_id IN NUMBER,
234 p_program_id IN NUMBER,
235 p_program_application_id IN NUMBER,
236 p_message_type IN NUMBER,
237 x_err_code OUT NOCOPY NUMBER,
238 x_err_msg OUT NOCOPY VARCHAR2);
239
240
241 -- BA OSFM-APS integration.
242 -- Function submitted by Raghav Raghavacharya for OSFM-APS integration.
243 -- Added to this file by Sadiq
244
245 FUNCTION get_routing_end(
246 p_routing_sequence_id IN NUMBER
247 ) RETURN NUMBER;
248
249
250 FUNCTION get_routing_start(
251 p_routing_sequence_id IN NUMBER
252 ) RETURN NUMBER;
253 -- EA OSFM-APS integration.
254
255
256 -- Added on 12.29.2000 to fix Bug # 1418785.
257 -- This procedure checks if a co-product relationship
258 -- exists for a given bill sequence.
259 FUNCTION CHECK_COPROD_RELATION (
260 p_bom_bill_seq_id IN NUMBER,
261 x_err_code OUT NOCOPY NUMBER,
262 x_err_msg OUT NOCOPY VARCHAR2
263 ) RETURN BOOLEAN;
264
265
266 -- Function overloaded by BBK for BOM BUg#2046999
267 FUNCTION CHECK_COPROD_RELATION (
268 p_bom_bill_seq_id IN NUMBER
269 ) RETURN NUMBER;
270
271
272 -- Added for checking that the sum of planning percentages
273 -- for links originating from each node is exactly 100
274 FUNCTION CHECK_100_PERCENT (
275 p_routing_sequence_id IN NUMBER,
276 x_err_code OUT NOCOPY NUMBER,
277 x_err_msg OUT NOCOPY VARCHAR2
278 ) RETURN NUMBER;
279
280
281 PROCEDURE AUTONOMOUS_TXN (
282 p_user IN NUMBER,
283 p_login IN NUMBER,
284 p_header_id IN NUMBER,
285 p_message IN VARCHAR2,
286 p_request_id IN NUMBER,
287 p_program_id IN NUMBER,
288 p_program_application_id IN NUMBER,
289 p_message_type IN NUMBER,
290 p_txn_id IN NUMBER,
291 x_err_code OUT NOCOPY NUMBER,
292 x_err_msg OUT NOCOPY VARCHAR2);
293
294 -- BA: NSO-WLT
295 --
296 -- This is an over-loaded function which calls the same function
297 -- with p_routing_revision_date parameter.
298 --
299 -- This is created to circumvent the dependency issues esp. with forms
300 --
301 PROCEDURE OPERATION_IS_STANDARD_REPEATS (
302 p_routing_sequence_id IN NUMBER,
303 p_standard_operation_id IN NUMBER,
304 p_operation_code IN VARCHAR2,
305 p_organization_id IN NUMBER, --BBK
306 p_op_is_std_op OUT NOCOPY NUMBER,
307 p_op_repeated_times OUT NOCOPY NUMBER,
308 x_err_code OUT NOCOPY NUMBER,
309 x_err_msg OUT NOCOPY VARCHAR2);
310 -- BA: NSO-WLT
311
312
313 PROCEDURE OPERATION_IS_STANDARD_REPEATS (
314 p_routing_sequence_id IN NUMBER,
315 p_routing_revision_date IN DATE, -- ADD: CZH.I_OED-1
316 p_standard_operation_id IN NUMBER,
317 p_operation_code IN VARCHAR2,
318 p_organization_id IN NUMBER, --BBK
319 p_op_is_std_op OUT NOCOPY NUMBER,
320 p_op_repeated_times OUT NOCOPY NUMBER,
321 x_err_code OUT NOCOPY NUMBER,
322 x_err_msg OUT NOCOPY VARCHAR2);
323
324
325 -- abb H
326 procedure validate_non_std_references (
327 p_assembly_item_id IN NUMBER,
328 p_routing_reference_id IN NUMBER,
329 p_bom_reference_id IN NUMBER,
330 p_alt_routing_designator IN VARCHAR2,
331 p_alt_bom_designator IN VARCHAR2,
332 p_organization_id IN NUMBER,
333 p_start_date IN DATE,
334 p_end_date IN DATE,
335 p_start_quantity IN NUMBER,
336 p_mrp_net_quantity IN OUT NOCOPY NUMBER,
340 p_firm_planned_flag IN OUT NOCOPY NUMBER,
337 p_class_code IN VARCHAR2,
338 p_completion_subinventory IN VARCHAR2,
339 p_completion_locator_id IN NUMBER,
341 p_bom_revision IN OUT NOCOPY VARCHAR2,
342 p_bom_revision_date IN OUT NOCOPY DATE,
343 p_routing_revision IN OUT NOCOPY VARCHAR2,
344 p_routing_revision_date IN OUT NOCOPY DATE,
345 x_routing_seq_id OUT NOCOPY NUMBER,
346 x_bom_seq_id OUT NOCOPY NUMBER,
347 validation_level NUMBER,
348 x_error_code OUT NOCOPY NUMBER,
349 x_err_msg OUT NOCOPY VARCHAR2);
350
351
352 -- abb H
353 FUNCTION WSM_ESA_ENABLED (
354 p_wip_entity_id IN NUMBER DEFAULT NULL,
355 err_code OUT NOCOPY NUMBER,
356 err_msg OUT NOCOPY VARCHAR2,
357 p_org_id IN NUMBER DEFAULT NULL,
358 p_job_type IN NUMBER DEFAULT NULL
359 ) RETURN INTEGER;
360
361
362 -- abb H
363 FUNCTION WSM_CHANGE_ESA_FLAG (
364 p_org_id IN NUMBER,
365 err_code OUT NOCOPY NUMBER,
366 err_msg OUT NOCOPY VARCHAR2
367 ) RETURN INTEGER;
368
369
370 -- CZH.I_OED-1
371 -- return 0 if no disabled op is found in the routing
372 -- return 1 if disabled op's are found in the routing
373 FUNCTION network_with_disabled_op (
374 p_routing_sequence_id IN NUMBER,
375 p_routing_rev_date IN DATE,
376 x_err_code OUT NOCOPY NUMBER,
377 x_err_msg OUT NOCOPY VARCHAR2
378 ) RETURN INTEGER;
379
380
381 -- CZH.I_OED-1
382 -- return 0 if network dose not have effective primary path up to p_op_seq_num
383 -- return 1 if network has effective primary path up to p_op_seq_num
384 FUNCTION primary_path_is_effective_till (
385 p_routing_sequence_id IN NUMBER,
386 p_routing_rev_date IN DATE,
387 p_start_op_seq_id IN OUT NOCOPY NUMBER,
388 p_op_seq_num IN NUMBER,
389 x_err_code OUT NOCOPY NUMBER,
390 x_err_msg OUT NOCOPY VARCHAR2
391 ) RETURN INTEGER;
392
393 -- CZH.I_OED-1
394 -- return 0 if current operation does not have effective next operation
395 -- return 1 if current operation has effective next operation
396 -- return 2 if current operation is the last operation
397 FUNCTION effective_next_op_exists (
398 p_organization_id IN NUMBER,
399 p_wip_entity_id IN NUMBER,
400 p_wo_op_seq_num IN NUMBER,
401 p_end_op_seq_id IN NUMBER, -- ADD: CZH.I_9999
402 x_err_code OUT NOCOPY NUMBER,
403 x_err_msg OUT NOCOPY VARCHAR2
404 ) RETURN INTEGER;
405
406
407 --this is to make the UTIL compatible with 1158 + OED-1
408 --this function is called from Move Txn form/interface on OSFM 1158+OED-1 codeline
409 FUNCTION effective_next_op_exits (
410 p_organization_id IN NUMBER,
411 p_wip_entity_id IN NUMBER,
412 p_wo_op_seq_num IN NUMBER,
413 x_err_code OUT NOCOPY NUMBER,
414 x_err_msg OUT NOCOPY VARCHAR2
415 ) RETURN INTEGER;
416
417 --this is to make the UTIL compatible with 1158 and 1157
418 --added function is specific customer fix.
419 FUNCTION wlt_if_costed (
420 p_wip_entity_id in number)
421 RETURN NUMBER;
422
423
424 --VJ: Start additions for WLTEnh--
425 --Moved check_charges_exist from WSMPLTOP to here --
426 /*===========================================================================
427 PROCEDURE NAME: check_charges_exist
428 Description: Checks if charges exist for this job 'p_wip_entity_id' at the
429 operation 'p_op_seq_num' in the org 'p_organization_id'.
430
431 ===========================================================================*/
432 PROCEDURE check_charges_exist (
433 p_wip_entity_id IN NUMBER,
434 p_organization_id IN NUMBER,
435 p_op_seq_num IN NUMBER,
436 p_op_seq_id IN NUMBER,
437 p_charges_exist OUT NOCOPY NUMBER,
438 p_manually_added_comp OUT NOCOPY NUMBER,
439 p_issued_material OUT NOCOPY NUMBER,
440 p_manually_added_resource OUT NOCOPY NUMBER,
441 p_issued_resource OUT NOCOPY NUMBER,
442 x_error_code OUT NOCOPY NUMBER,
443 x_error_msg OUT NOCOPY VARCHAR2);
444
445
446 -- CZH.I_OED-2
447 -- return NULL if no effective replacement is found
448 Function replacement_op_seq_id (
449 p_op_seq_id NUMBER,
453 -- OSP
450 p_routing_rev_date DATE
451 ) RETURN INTEGER;
452
454
455 FUNCTION check_po_move (
456 p_sequence_id NUMBER,
457 p_sequence_id_type VARCHAR2,
458 p_routing_rev_date DATE,
459 x_err_code OUT NOCOPY NUMBER,
460 x_err_msg OUT NOCOPY VARCHAR2
461 ) RETURN BOOLEAN ;
462
463
464 --
465 -- Bugfix 2617330:
466 --
467 -- This new procedure will be used by WIP to determine if the lot based jobs
468 -- can be closed or not. The API will accept 2 parameters: group_id and orgn_id
469 -- Using these parameters, the API would identify all the lot based jobs in
470 -- the table WIP_DJ_CLOSE_TEMP and validate these records.
471 -- All jobs that fail in validation process would be printed and the value of
472 -- column STATUS_TYPE in wip_dj_close_temp would be updated to 99.
473 -- In the end, the status of these jobs in wip_discrete_jobs will be updated to 15 (Failed Close)
474 -- and records in wip_dj_close_temp with status 99 will be deleted.
475 --
476 -- x_err_code will be set to 0 if there are any unprocessed/uncosted txn.
477 -- Otherwise, x_err_code will have a value of 1.
478 --
479 PROCEDURE validate_lbj_before_close (
480 p_group_id in number,
481 p_organization_id in number,
482 x_err_code out nocopy number,
483 x_err_msg out nocopy varchar2,
484 x_return_status out nocopy varchar2 );
485
486
487 PROCEDURE get_Kanban_rec_grp_info (
488 p_organization_id IN number,
489 p_kanban_assembly_id IN number,
490 p_rtg_rev_date IN date,
491 p_bom_seq_id OUT NOCOPY number,
492 p_start_seq_num OUT NOCOPY number,
493 p_error_code OUT NOCOPY number,
494 p_error_msg OUT NOCOPY varchar2);
495
496
497 PROCEDURE get_max_kanban_asmbly_qty (
498 p_bill_seq_id IN number,
499 p_component_item_id IN number,
500 p_bom_revision_date IN date,
501 p_start_seq_num IN number,
502 p_available_qty IN number,
503 p_max_asmbly_qty OUT NOCOPY number,
504 p_error_code OUT NOCOPY number,
505 p_error_msg OUT NOCOPY varchar2);
506
507
508 PROCEDURE return_att_quantity(
509 p_org_id IN number,
510 p_item_id IN number,
511 p_rev IN varchar2,
512 p_lot_no IN varchar2,
513 p_subinv IN varchar2,
514 p_locator_id IN number,
515 p_qoh OUT NOCOPY number,
516 p_atr OUT NOCOPY number,
517 p_att OUT NOCOPY number,
518 p_err_code OUT NOCOPY number,
519 p_err_msg OUT NOCOPY varchar2 );
520
521
522 function check_osp_operation ( p_wip_entity_id IN NUMBER,
523 p_operation_seq_num IN OUT NOCOPY NUMBER,
524 p_organization_id IN NUMBER )
525
526 return boolean;
527
528 --BA 2731019
529 FUNCTION CHECK_COPROD_COMP_RELATION (
530 p_bom_bill_seq_id IN NUMBER,
531 p_component_seq_id IN NUMBER
532 )
533 RETURN NUMBER;
534 PRAGMA RESTRICT_REFERENCES(CHECK_COPROD_COMP_RELATION, WNDS);
535 --EA 2731019
536
537 -----------------------------------------------------
538 --bug fix:7387499 functions that are used in BOM_OPERATION_NETWORKS_V
539 -- to fetch operation code and department code
540 --of relatively effective operation at particular operation.
541 ---------------------------------------------------
542 FUNCTION EFFECTIVE_DATE(
543 p_oper_seq_num NUMBER,
544 p_routing_seq_id NUMBER,
545 p_operation_type NUMBER
546 ) RETURN DATE;
547
548 FUNCTION get_eff_stdop_id(
549 p_stdop_id NUMBER,
550 p_opseq_id NUMBER
551 ) RETURN NUMBER;
552
553 FUNCTION get_eff_dept_id(
554 p_dept_id NUMBER,
555 p_opseq_id NUMBER
556 ) RETURN NUMBER;
557 ------------------------------------------------------
558 --END bug fix:7387499
559 ---------------------------------------------------
560 ------------------------------------------------------------
561 -- FUNCTIONS THAT CHECK TXN and TXN INTERFACE TABLES
562 ------------------------------------------------------------
563
564 FUNCTION CHECK_WLMTI (
565 p_wip_entity_id IN NUMBER,
566 p_wip_entity_name IN VARCHAR2,
567 p_header_id IN NUMBER,
568 p_transaction_date IN DATE,
569 x_err_code OUT NOCOPY NUMBER,
573 --BA: 2804945, org_id messing, overloading function
570 x_err_msg OUT NOCOPY VARCHAR2
571 ) RETURN NUMBER;
572
574 FUNCTION CHECK_WLMTI (
575 p_wip_entity_id IN NUMBER,
576 p_wip_entity_name IN VARCHAR2,
577 p_header_id IN NUMBER,
578 p_transaction_date IN DATE,
579 x_err_code OUT NOCOPY NUMBER,
580 x_err_msg OUT NOCOPY VARCHAR2,
581 p_organization_id IN NUMBER
582 )
583 RETURN NUMBER;
584 --EA: 2804945
585
586 ------------------------------------------------------------
587
588 --Moved CHECK_WMTI from WSMPLOAD to here --
589 FUNCTION CHECK_WMTI (
590 p_wip_entity_id IN NUMBER,
591 p_wip_entity_name IN VARCHAR2,
592 p_transaction_date IN DATE,
593 x_err_code OUT NOCOPY NUMBER,
594 x_err_msg OUT NOCOPY VARCHAR2
595 )
596 RETURN NUMBER;
597
598 --BA: 2804945, org_id messing, overloading function
599 FUNCTION CHECK_WMTI (
600 p_wip_entity_id IN NUMBER,
601 p_wip_entity_name IN VARCHAR2,
602 p_transaction_date IN DATE,
603 x_err_code OUT NOCOPY NUMBER,
604 x_err_msg OUT NOCOPY VARCHAR2,
605 p_organization_id IN NUMBER
606 )
607 RETURN NUMBER;
608 --EA: 2804945
609
610 ------------------------------------------------------------
611
612 FUNCTION CHECK_WMT (
613 x_err_code OUT NOCOPY NUMBER,
614 x_err_msg OUT NOCOPY VARCHAR2,
615 p_wip_entity_id IN NUMBER,
616 p_wip_entity_name IN VARCHAR2,
617 p_organization_id IN NUMBER,
618 p_transaction_date IN DATE
619 )
620 RETURN NUMBER;
621
622 ------------------------------------------------------------
623
624 --Moved CHECK_WSMT from WSMPLOAD to here --
625 FUNCTION CHECK_WSMT (
626 p_wip_entity_id IN NUMBER,
627 p_wip_entity_name IN VARCHAR2,
628 p_transaction_id IN NUMBER,
629 p_transaction_date IN DATE,
630 x_err_code OUT NOCOPY NUMBER,
631 x_err_msg OUT NOCOPY VARCHAR2
632 )
633 RETURN NUMBER;
634
635 --BA: 2804945, org_id messing, overloading function
636 FUNCTION CHECK_WSMT (
637 p_wip_entity_id IN NUMBER,
638 p_wip_entity_name IN VARCHAR2,
639 p_transaction_id IN NUMBER,
640 p_transaction_date IN DATE,
641 x_err_code OUT NOCOPY NUMBER,
642 x_err_msg OUT NOCOPY VARCHAR2,
643 p_organization_id IN NUMBER
644 )
645 RETURN NUMBER;
646 --EA: 2804945
647
648 ------------------------------------------------------------
649
650 FUNCTION CHECK_WSMTI (
651 x_err_code OUT NOCOPY NUMBER,
652 x_err_msg OUT NOCOPY VARCHAR2,
653 p_wip_entity_id IN NUMBER,
654 p_wip_entity_name IN VARCHAR2,
655 p_organization_id IN NUMBER,
656 p_transaction_date IN DATE
657 )
658 RETURN NUMBER;
659
660 -- EA#2804945 - Organization_id missing for check for other txns.
661
662 --------------------------------------------------------------------
663 -- New Procedures/Functions added for DMF_PF.J or 11.5.10 ----------
664 --------------------------------------------------------------------
665 -- Import Network Routing Support through BOM Interface ----------
666 --------------------------------------------------------------------
667 -- Bug#/Project: FP.J Import Network Rtg - 3088690
668 -- New or Overloaded: New
669 -- Release : 11.5.10.
670 -- Backward Compatible: YES
671 -- Modified by: Bala Balakumar.
672 --------------------------------------------------------------------
673 FUNCTION JOBS_WITH_QTY_AT_FROM_OP(
674 x_err_code OUT NOCOPY NUMBER
675 , x_err_msg OUT NOCOPY varchar2
676 , p_operation_sequence_id IN NUMBER
677 ) RETURN BOOLEAN;
678
679 --------------------------------------------------------------------
680 -- Bug#/Project: FP.J Import Network Rtg - 3088690
681 -- New or Overloaded: New and Overloaded
682 -- Release : 11.5.10.
686 FUNCTION JOBS_WITH_QTY_AT_FROM_OP(
683 -- Backward Compatible: YES
684 -- Modified by: Bala Balakumar.
685 --------------------------------------------------------------------
687 x_err_code OUT NOCOPY NUMBER
688 , x_err_msg OUT NOCOPY varchar2
689 , p_routing_sequence_id IN NUMBER
690 , p_operation_seq_num IN NUMBER
691 ) RETURN BOOLEAN;
692 --------------------------------------------------------------------
693 -- Bug#/Project: FP.J - OSFM/APS P2 Integration.
694 -- New or Overloaded: New
695 -- Release : 11.5.10.
696 -- Backward Compatible: YES
697 -- Modified by: Bala Balakumar.
698 --------------------------------------------------------------------
699 FUNCTION CREATE_LBJ_COPY_RTG_PROFILE RETURN NUMBER;
700 --------------------------------------------------------------------
701 -- Bug#/Project: FP.J - OSFM/APS P2 Integration.
702 -- New or Overloaded: New and Overloaded
703 -- Release : 11.5.10.
704 -- Backward Compatible: YES
705 -- Modified by: Bala Balakumar.
706 --------------------------------------------------------------------
707 FUNCTION CREATE_LBJ_COPY_RTG_PROFILE(p_organization_id IN NUMBER) RETURN NUMBER;
708 --------------------------------------------------------------------
709
710 -- Bug#/Project: FP.J - Accounting Period consistent API
711 -- New or Overloaded: New
712 -- Release : 11.5.10.
713 -- Backward Compatible: YES
714 -- Modified by: Bala Balakumar.
715 -- RETURN value of 0 indicates the date is in a non-open period.
716 -- Exceptions should be handled by the calling programs.
717 --------------------------------------------------------------------
718 FUNCTION GET_INV_ACCT_PERIOD(
719 x_err_code OUT NOCOPY NUMBER
720 , x_err_msg OUT NOCOPY varchar2
721 , p_organization_id IN NUMBER
722 , p_date IN DATE) RETURN NUMBER;
723 --------------------------------------------------------------------
724
725
726 PROCEDURE AUTONOMOUS_WRITE_TO_WIE (
727 p_header_id IN NUMBER,
728 p_message IN VARCHAR2,
729 p_request_id IN NUMBER,
730 p_program_id IN NUMBER,
731 p_program_application_id IN NUMBER,
732 p_message_type IN NUMBER,
733 x_err_code OUT NOCOPY NUMBER,
734 x_err_msg OUT NOCOPY VARCHAR2);
735
736 ------------------------------------------------------
737 -- Added this constant for APS Integration Project for Patchset J.
738 -- This constant's value will decide Option A/C behavior at site/org level
739 -- If = 'Y', site level parameter will govern Option A/C behavior for instance
740 -- If = 'N', wsm_parameters.plan_code will govern Option A/C for that org :
741 -- if plan_code is NULL,
742 -- site level profile value will indicate behavior for that org
743 -- else,
744 -- plan_code value will indicate behavior for that org
745 -- For ST onwards and for customers, this should **ALWAYS** be 'Y'
746 -- For development/support purposes **ONLY**, this can be 'N'
747
748 REFER_SITE_LEVEL_PROFILE CONSTANT VARCHAR2(1) := 'Y';
749
750
751 -----------------------------------------------------
752 -- get bom_sequence_id a given wip_entity_id
753 FUNCTION GET_JOB_BOM_SEQ_ID(
754 p_wip_entity_id in number
755 ) RETURN NUMBER;
756
757
758 ------------------------------------------------------
759 -- Start : Added to fix bug 3452913 --
760 FUNCTION replacement_copy_op_seq_id (
761 p_job_op_seq_id NUMBER,
762 p_wip_entity_id NUMBER
763 ) RETURN INTEGER;
764 -- End : Added to fix bug 3452913 --
765
766 ------------------------------------------------------
767 -- BA bug 3512105
768 -- will return WLBJ.internal_copy_type, return -3 if not available
769 FUNCTION get_internal_copy_type (
770 p_wip_entity_id NUMBER
771 ) RETURN INTEGER;
772 -- EA bug 3512105
773
774 --bug 3754881
775 PROCEDURE lock_wdj(
776 x_err_code OUT NOCOPY NUMBER
777 , x_err_msg OUT NOCOPY VARCHAR2
778 , p_wip_entity_id IN NUMBER
779 , p_rollback_flag IN NUMBER);
780 --bug 3754881 end
781 --Bug 5182520:Added the following procedure to handle material status checks.
782 Function is_status_applicable(p_wms_installed IN VARCHAR2,
783 p_trx_status_enabled IN NUMBER,
784 p_trx_type_id IN NUMBER,
785 p_lot_status_enabled IN VARCHAR2,
786 p_serial_status_enabled IN VARCHAR2,
787 p_organization_id IN NUMBER,
788 p_inventory_item_id IN NUMBER,
789 p_sub_code IN VARCHAR2,
790 p_locator_id IN NUMBER,
791 p_lot_number IN VARCHAR2,
792 p_serial_number IN VARCHAR2,
793 x_error_msg OUT NOCOPY VARCHAR2)
794
795 return varchar2;
796
797 -- This Function is added to support Add operations/links in LBJ Interface.
798 FUNCTION validate_job_network(
799 p_wip_entity_id NUMBER,
800 x_err_code OUT NOCOPY NUMBER,
801 x_err_msg OUT NOCOPY VARCHAR2)
802 RETURN NUMBER;
803
804
805 END WSMPUTIL;