1 PACKAGE BODY MRP_CL_FUNCTION AS
2 /* $Header: MRPCLHAB.pls 120.12.12010000.3 2008/08/25 10:48:56 sbyerram ship $ */
3 -- ===============================================================
4
5
6 G_BOM_GREATER_THAN_EQUAL_J NUMBER := 1;
7
8 G_BOM_LESS_THAN_J NUMBER := 0;
9
10 G_AHL_GREATER_THAN_EQUAL_J NUMBER := 1;
11
12 G_AHL_LESS_THAN_J NUMBER := 0;
13
14
15 G_WSH_GREATER_THAN_EQUAL_J NUMBER := 1;
16
17 G_WSH_LESS_THAN_J NUMBER := 0;
18
19 PROCEDURE APPS_INITIALIZE(
20 p_user_name IN VARCHAR2,
21 p_resp_name IN VARCHAR2,
22 p_application_name IN VARCHAR2 )
23 IS
24 lv_application_id NUMBER;
25
26 BEGIN
27
28 SELECT APPLICATION_ID
29 INTO lv_application_id
30 FROM FND_APPLICATION_VL
31 WHERE APPLICATION_NAME = p_application_name;
32
33 APPS_INITIALIZE (p_user_name,
34 p_resp_name,
35 p_application_name,
36 lv_application_id);
37
38 END APPS_INITIALIZE;
39
40 /* -- Added this procedure to accept application_id instead of application_name */
41
42 PROCEDURE APPS_INITIALIZE(
43 p_user_name IN VARCHAR2,
44 p_resp_name IN VARCHAR2,
45 p_application_name IN VARCHAR2,
46 p_application_id IN NUMBER )
47 IS
48 lv_user_id NUMBER;
49 lv_resp_id NUMBER;
50 lv_application_id NUMBER;
51 lv_log_msg VARCHAR2(500);
52
53 BEGIN
54
55 IF FND_GLOBAL.USER_ID = -1 THEN
56 /* if user_id = -1, it means this initialization process is needed */
57
58 BEGIN
59 SELECT USER_ID
60 INTO lv_user_id
61 FROM FND_USER
62 WHERE USER_NAME = p_user_name;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 raise_application_error (-20001, 'User not defined on Source instance');
66 END;
67
68 IF MRP_CL_FUNCTION.validateUser(lv_user_id,MSC_UTIL.TASK_COLL,lv_log_msg) THEN
69 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_COLL,
70 lv_user_id,
71 -1, --l_resp_id,
72 -1 --l_application_id
73 );
74 ELSE
75 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
76 raise_application_error (-20001, lv_log_msg);
77 END IF;
78
79 END IF;
80
81 END APPS_INITIALIZE;
82
83 FUNCTION Default_ABC_Assignment_Group ( p_org_id NUMBER)
84 RETURN NUMBER IS
85
86 CURSOR c1( ORG_ID NUMBER) IS
87 SELECT default_abc_assignment_group
88 FROM MRP_PARAMETERS
89 WHERE ORGANIZATION_ID = ORG_ID;
90
91 lv_res NUMBER:= NULL;
92
93 BEGIN
94
95 FOR c_rec IN c1(p_org_id) LOOP
96
97 lv_res:= c_rec.default_abc_assignment_group;
98
99 END LOOP;
100
101 RETURN lv_res;
102
103 END;
104
105 FUNCTION mrp_resource_cost(p_item_id in number,
106 p_org_id in number,
107 p_primary_cost_method in number)
108 RETURN NUMBER IS
109
110 CURSOR COST_C IS
111 SELECT NVL(cst.tl_resource,0)
112 + NVL(cst.tl_overhead,0)
113 + NVL(cst.tl_material_overhead,0)
114 + NVL(cst.tl_outside_processing,0)
115 FROM cst_item_costs cst,
116 cst_cost_types cct
117 WHERE cct.costing_method_type = p_primary_cost_method
118 AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
119 AND cst.cost_type_id = cct.cost_type_id
120 AND cst.inventory_item_id = p_item_id
121 AND cst.organization_id = p_org_id;
122
123 CURSOR COST_C2 IS
124 SELECT NVL(cst.tl_resource,0)
125 + NVL(cst.tl_overhead,0)
126 + NVL(cst.tl_material_overhead,0)
127 + NVL(cst.tl_outside_processing,0)
128 FROM cst_cost_types cct,
129 cst_item_costs cst
130 WHERE cst.cost_type_id = cct.default_cost_type_id
131 AND cct.costing_method_type = p_primary_cost_method
132 AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
133 AND cst.inventory_item_id = p_item_id
134 AND cst.organization_id = p_org_id;
135
136 l_cost NUMBER:= NULL;
137
138 BEGIN
139
140 OPEN COST_C;
141 FETCH COST_C into l_cost;
142 CLOSE COST_C;
143
144 IF l_cost IS NULL THEN
145
146 OPEN COST_C2;
147 FETCH COST_C2 into l_cost;
148 CLOSE COST_C2;
149
150 END IF;
151
152 RETURN(l_cost);
153
154 END mrp_resource_cost;
155
156
157 FUNCTION mrp_item_cost(p_item_id in number,
158 p_org_id in number,
159 p_primary_cost_method in number)
160 RETURN NUMBER IS
161
162 CURSOR COST_C IS
163 SELECT NVL(cst.item_cost,0)
164 FROM cst_item_costs cst,
165 cst_cost_types cct
166 WHERE cct.costing_method_type = p_primary_cost_method
167 AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
168 AND cst.cost_type_id = cct.cost_type_id
169 AND cst.inventory_item_id = p_item_id
170 AND cst.organization_id = p_org_id;
171
172 CURSOR COST_C2 IS
173 SELECT NVL(cst.item_cost,0)
174 FROM cst_cost_types cct,
175 cst_item_costs cst
176 WHERE cst.cost_type_id = cct.default_cost_type_id
177 AND cct.costing_method_type = p_primary_cost_method
178 AND cct.cost_type_id = DECODE(p_primary_cost_method,1,1,2,2,1)
179 AND cst.inventory_item_id = p_item_id
180 AND cst.organization_id = p_org_id;
181
182 l_cost NUMBER:= NULL;
183
184 BEGIN
185
186 OPEN COST_C;
187 FETCH COST_C into l_cost;
188 CLOSE COST_C;
189
190 IF l_cost IS NULL THEN
191
192 OPEN COST_C2;
193 FETCH COST_C2 into l_cost;
194 CLOSE COST_C2;
195
196 END IF;
197
198
199 RETURN(l_cost);
200
201 END mrp_item_cost;
202
203
204 FUNCTION mrp_item_list_price(arg_item_id in number,
205 arg_org_id in number,
206 arg_uom_code in varchar2,
207 arg_process_flag in varchar2,
208 arg_primary_cost_method in number)
209 RETURN NUMBER
210 IS
211
212 /* according to bug 1221049, we use the new cursor definition recommended by
213 QP team(product 495).
214
215 CURSOR PRICE_C IS
216 SELECT round(list_price,NVL(spl.rounding_factor,2))
217 from oe_price_list_lines sopl,
218 oe_price_lists spl
219 where spl.price_list_id = arg_price_list_id
220 and sopl.price_list_id = spl.price_list_id
221 and sopl.inventory_item_id = arg_item_id
222 and nvl(sopl.unit_code,' ') = nvl(arg_uom_code,' ')
223 and sysdate between nvl(sopl.start_date_active, sysdate-1)
224 and nvl(sopl.end_date_active, sysdate+1);
225 */
226
227 /* For the bug # 2070983, adding + 0 to qpl.list_header_id to avoid the range
228 * scan on the index QP_LIST_LINES_U1, instead it will now do a unique scan
229 * on the index QP_LIST_LINES_PK
230 */
231
232 /* For Bug 2113445, converting rounding factor to follow OM rounding convention
233 by multiplying it by -1
234 */
235 CURSOR PRICE_D ( p_item_id IN VARCHAR2,
236 p_uom_code IN VARCHAR2) IS
237 /*
238 select round(operand,-1*(nvl(qplh.rounding_factor,2)))
239 from qp_list_headers_b qplh,
240 qp_list_lines qpl,
241 qp_pricing_attributes qpa
242 where qplh.list_header_id = p_price_list_id
243 and qpl.list_header_id + 0 = qplh.list_header_id
244 and qpl.list_line_id = qpa.list_line_id
245 and qpa.product_attribute_context = 'ITEM'
246 and qpa.product_attribute = 'PRICING_ATTRIBUTE1'
247 and qpa.product_attr_value = p_item_id
248 and ( qpa.product_uom_code = p_uom_code
249 OR ( qpa.product_uom_code IS NULL
250 AND p_uom_code IS NULL))
251 and ( qpl.start_date_active <= sysdate
252 OR qpl.start_date_active IS NULL)
253 and ( qpl.end_date_active >= sysdate
254 OR qpl.end_date_active IS NULL);
255 */
256 /* For Bug 2230228, based on QP Teams suggestion*/
257 SELECT ROUND(QPL.OPERAND,-1 * (NVL(QPLH.ROUNDING_FACTOR,2)))
258 FROM
259 QP_PRICING_ATTRIBUTES QPA ,
260 QP_LIST_LINES QPL,
261 QP_LIST_HEADERS_B QPLH
262 WHERE QPA.PRICING_PHASE_ID = 1
263 AND QPA.QUALIFICATION_IND = 4
264 AND QPA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
265 AND QPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
266 AND QPA.PRODUCT_ATTR_VALUE = p_item_id
267 AND QPA.LIST_HEADER_ID = v_price_list_id
268 AND QPL.LIST_LINE_ID = QPA.LIST_LINE_ID
269 AND QPL.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
270 AND QPA.PRODUCT_UOM_CODE = p_uom_code
271 AND (QPL.START_DATE_ACTIVE <= SYSDATE OR QPL.START_DATE_ACTIVE IS NULL )
272 AND (QPL.END_DATE_ACTIVE >= SYSDATE OR QPL.END_DATE_ACTIVE IS NULL) ;
273
274 CURSOR PRICE_D_NULL_UOM( p_item_id IN VARCHAR2)
275 IS
276 SELECT ROUND(QPL.OPERAND,-1 * (NVL(QPLH.ROUNDING_FACTOR,2)))
277 FROM QP_PRICING_ATTRIBUTES QPA ,
278 QP_LIST_LINES QPL,
279 QP_LIST_HEADERS_B QPLH
280 WHERE QPA.PRICING_PHASE_ID = 1
281 AND QPA.QUALIFICATION_IND = 4
282 AND QPA.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
283 AND QPA.PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
284 AND QPA.PRODUCT_ATTR_VALUE = p_item_id
285 AND QPA.LIST_HEADER_ID = v_price_list_id
286 AND QPL.LIST_LINE_ID = QPA.LIST_LINE_ID
287 AND QPL.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
288 AND QPA.PRODUCT_UOM_CODE IS NULL
289 AND (QPL.START_DATE_ACTIVE <= SYSDATE OR QPL.START_DATE_ACTIVE IS NULL )
290 AND (QPL.END_DATE_ACTIVE >= SYSDATE OR QPL.END_DATE_ACTIVE IS NULL) ;
291
292 lv_price number:= NULL;
293 BEGIN
294
295 IF arg_uom_code IS NULL THEN
296 OPEN PRICE_D_NULL_UOM(arg_item_id);
297 FETCH PRICE_D_NULL_UOM into lv_price;
298 CLOSE PRICE_D_NULL_UOM;
299 ELSE
300 OPEN PRICE_D( arg_item_id, arg_uom_code);
301 FETCH PRICE_D into lv_price;
302 CLOSE PRICE_D;
303 END IF;
304
305 /* OPM Team - OPM INventoryConvergence Project
306 OPM will not have separate price lists
307 IF (lv_price IS NULL) AND (arg_process_flag = 'Y') THEN
308 lv_price:= gmp_aps_output_pkg.retrieve_price_list
309 (arg_item_id,
310 arg_org_id);
311 END IF;
312 */
313
314 RETURN lv_price;
315
316 EXCEPTION
317
318 WHEN OTHERS THEN RETURN NULL;
319
320 END mrp_item_list_price;
321
322
323 FUNCTION mrp_item_supp_price(p_item_id in number,
324 p_asl_id in number)
325 RETURN NUMBER IS
326
327 CURSOR c_po_line IS
328 SELECT DOCUMENT_HEADER_ID , DOCUMENT_LINE_ID
329 FROM po_asl_documents
330 WHERE asl_id = p_asl_id
331 AND using_organization_id = -1
332 AND DOCUMENT_TYPE_CODE = 'BLANKET'; --??
333
334 CURSOR c_unit_price(c_header_id in number,
335 c_line_id in number) IS
336 SELECT NVL(pll.unit_price,0) unit_price
337 FROM po_lines_all pll
338 WHERE po_line_id = c_line_id
339 and po_header_id = c_header_id
340 and item_id = p_item_id
341 and (pll.cancel_date is null OR
342 pll.cancel_date >= SYSDATE)
343 and (pll.closed_date is null OR
344 pll.closed_date >= SYSDATE)
345 and (pll.expiration_date is null OR
346 pll.expiration_date >= SYSDATE);
347
348 l_header_id number;
349 l_line_id number;
350 l_unit_price number;
351
352 BEGIN
353 l_unit_price := -1;
354 FOR c_rec in c_po_line
355 LOOP
356 BEGIN
357 For c_rec1 in c_unit_price(c_rec.DOCUMENT_HEADER_ID,
358 c_rec.DOCUMENT_LINE_ID)
359 LOOP
360 IF c_rec1.unit_price > l_unit_price then
361 l_unit_price := c_rec1.unit_price;
362 END IF;
363 END LOOP;
364 END;
365 END LOOP;
366 IF l_unit_price = -1 THEN
367 RETURN (NULL);
368 ELSE
369 RETURN(l_unit_price);
370 END IF;
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN RETURN NULL;
374 WHEN OTHERS THEN RETURN NULL;
375 END mrp_item_supp_price;
376
377
378 /* This is being added corresponding to bug : 2062398. */
379 FUNCTION mrp_rev_cum_yield(p_wip_entity_id IN NUMBER,
380 p_org_id IN NUMBER,
381 p_bill_seq_id IN NUMBER,
382 p_co_prod_supply IN NUMBER)
383 RETURN NUMBER IS
384 l_rev_cum number := 1;
385 v_split number;
386 qty_to_move number;
387 curr_op_seq_id number;
388 v_curr_op_seq_num number;
389
390 v_sql_stmt1 varchar2(1000);
391 v_sql_stmt2 varchar2(1000);
392
393 BEGIN
394
395 begin
396
397 v_sql_stmt1 := ' SELECT bos.Operation_Seq_Num, nvl(bos.reverse_cumulative_yield,1), wo.quantity_waiting_to_move '
398 ||' , nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) '
399 ||' FROM MRP_SN_OPR_SEQS bos, '
400 ||' MRP_SN_WOPRS wo '
401 ||' WHERE wo.organization_id = :p_org_id '
402 ||' AND wo.wip_entity_id = :p_wip_entity_id '
403 ||' AND (wo.quantity_in_queue <> 0 OR '
404 ||' wo.quantity_running <> 0 OR '
405 ||' wo.quantity_waiting_to_move <> 0) '
406 ||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id ';
407
408 EXECUTE IMMEDIATE v_sql_stmt1
409 INTO v_curr_op_seq_num, l_rev_cum, qty_to_move, curr_op_seq_id
410 USING p_org_id, p_wip_entity_id;
411
412 exception
413 when others then
414 l_rev_cum := l_rev_cum;
415 end;
416
417 /* for bug: 2488331 get the RCY of next primary operation from the network, if the job is moved to to_move stage or
418 it is jumped to another opern and is in to_move stage */
419
420 IF (qty_to_move <> 0) THEN
421
422 begin
423 v_sql_stmt2 := ' SELECT nvl(bos.reverse_cumulative_yield,1) '
424 ||' FROM MRP_SN_OPR_SEQS bos, '
425 ||' MRP_SN_OPR_NETWORKS bon '
426 ||' WHERE bon.from_op_seq_id = :curr_op_seq_id '
427 ||' AND bon.to_op_seq_id = bos.operation_sequence_id '
428 ||' AND bon.transition_type = 1 ';
429
430 EXECUTE IMMEDIATE v_sql_stmt2
431 INTO l_rev_cum
432 USING curr_op_seq_id;
433
434 exception
435 when others then
436 l_rev_cum := l_rev_cum;
437 end;
438
439 END IF;
440
441 /* if the co-product flag on the job is yes , the apply the co-product split */
442 IF (p_co_prod_supply = 1) THEN
443 begin
444 SELECT nvl(wsc.split,100)
445 INTO v_split
446 FROM wsm_Co_products wsc
447 WHERE wsc.bill_sequence_id is not null
448 AND wsc.split > 0
449 AND wsc.bill_Sequence_id = p_bill_seq_id;
450
451 v_split := v_split/100;
452 l_rev_cum := v_split*l_rev_cum;
453
454 exception
455 when others then
456 l_rev_cum := l_rev_cum;
457 end;
458
459 END IF;
460
461 RETURN(l_rev_cum);
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 RETURN 1;
466
467 END mrp_rev_cum_yield;
468
469
470 /*This is added to get the reverse cumulative yield for the current operation from the job details */
471 FUNCTION mrp_jd_rev_cum_yield(p_wip_entity_id IN NUMBER,
472 p_org_id IN NUMBER,
473 p_bill_seq_id IN NUMBER,
474 p_co_prod_supply IN NUMBER)
475 RETURN NUMBER IS
476 l_rev_cum number := 1;
477 lv_status number := 0;
478 v_split number;
479 qty_to_move number;
480 curr_op_seq_id number;
481 v_curr_op_seq_num number;
482
483 v_sql_stmt1 varchar2(1000);
484 v_sql_stmt2 varchar2(1000);
485
486 BEGIN
487
488 begin
489
490 v_sql_stmt1 := ' Select wdj.status_type, decode(wdj.status_type, 1, (select nvl(bos.reverse_cumulative_yield,1) from MRP_SN_LJ_OPRS bos where bos.wip_entity_id = wdj.wip_entity_id and upper(bos.Network_start_end) = ''S''),1) from '
491 ||' MRP_SN_DSCR_JOBS wdj '
492 ||' Where wdj.wip_entity_id = :p_wip_entity_id ';
493
494 EXECUTE IMMEDIATE v_sql_stmt1
495 INTO lv_status, l_rev_cum
496 USING p_wip_entity_id;
497 exception
498 when others then
499 l_rev_cum := l_rev_cum;
500 end;
501
502 if lv_status <> 1 then
503
504 begin
505
506 v_sql_stmt1 := ' SELECT bos.Operation_Seq_Num, nvl(bos.reverse_cumulative_yield,1), wo.quantity_waiting_to_move '
507 ||' , nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) '
508 ||' FROM MRP_SN_LJ_OPRS bos, '
509 ||' MRP_SN_WOPRS wo '
510 ||' WHERE wo.organization_id = :p_org_id '
511 ||' AND wo.wip_entity_id = :p_wip_entity_id '
512 ||' AND (wo.quantity_in_queue <> 0 OR '
513 ||' wo.quantity_running <> 0 OR '
514 ||' wo.quantity_waiting_to_move <> 0) '
515 ||' AND bos.wip_entity_id = wo.wip_entity_id '
516 ||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id ';
517
518 EXECUTE IMMEDIATE v_sql_stmt1
519 INTO v_curr_op_seq_num, l_rev_cum, qty_to_move, curr_op_seq_id
520 USING p_org_id, p_wip_entity_id;
521
522 exception
523 when others then
524 l_rev_cum := l_rev_cum;
525 end;
526
527 /* for bug: 2488331 get the RCY of next primary operation from the network, if the job is moved to to_move stage or
528 it is jumped to another opern and is in to_move stage */
529
530 IF (qty_to_move <> 0) THEN
531
532 begin
533 v_sql_stmt2 := ' SELECT nvl(bos.reverse_cumulative_yield,1) '
534 ||' FROM MRP_SN_LJ_OPRS bos, '
535 ||' MRP_SN_LJ_OPR_NWK bon '
536 ||' WHERE bon.from_op_seq_id = :curr_op_seq_id '
537 ||' AND bon.wip_entity_id = :p_wip_entity_id '
538 ||' AND bon.wip_entity_id = bos.wip_entity_id '
539 ||' AND bon.to_op_seq_id = bos.operation_sequence_id '
540 ||' AND bon.transition_type = 1 ';
541
542 EXECUTE IMMEDIATE v_sql_stmt2
543 INTO l_rev_cum
544 USING curr_op_seq_id, p_wip_entity_id;
545
546 exception
547 when others then
548 l_rev_cum := l_rev_cum;
549 end;
550
551 END IF;
552
553 END IF;
554
555 /* if the co-product flag on the job is yes , the apply the co-product split */
556 IF (p_co_prod_supply = 1) THEN
557 begin
558 SELECT nvl(wsc.split,100)
559 INTO v_split
560 FROM wsm_Co_products wsc
561 WHERE wsc.bill_sequence_id is not null
562 AND wsc.split > 0
563 AND wsc.bill_Sequence_id = p_bill_seq_id;
564
565 v_split := v_split/100;
566 l_rev_cum := v_split*l_rev_cum;
567
568 exception
569 when others then
570 l_rev_cum := l_rev_cum;
571 end;
572
573 END IF;
574
575 RETURN(l_rev_cum);
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 RETURN 1;
580
581 END mrp_jd_rev_cum_yield;
582
583
584 /* modified this func to consider the co-product supply for bug:2401445 */
585 FUNCTION mrp_rev_cum_yield_unreleased(p_wip_entity_id in number,
586 p_org_id in number,
587 p_bill_seq_id in number,
588 p_co_prod_supply in number)
589 RETURN NUMBER IS
590
591 v_op_seq_num number;
592 v_operation_sequence_id number;
593 x_err_msg varchar2(2000);
594 x_err_code number;
595 e_user_exception EXCEPTION;
596 p_common_routing_sequence_id NUMBER;
597 lv_rev_cum number;
598 v_routing_seq_id number;
599
600 v_split number;
601
602 BEGIN
603
604 lv_rev_cum := 1;
605 v_split := 0;
606
607 Begin
608
609 SELECT nvl(wdj.common_routing_sequence_id,wdj.routing_reference_id)
610 into v_routing_seq_id
611 FROM wip_discrete_jobs wdj
612 WHERE wdj.wip_entity_id = p_wip_entity_id
613 AND wdj.organization_id = p_org_id;
614
615 Exception
616 WHEN NO_DATA_FOUND THEN RETURN 1;
617 WHEN OTHERS THEN RETURN 1;
618
619 End;
620
621
622 WSMPUTIL.find_common_routing(
623 p_routing_sequence_id => v_routing_seq_id
624 , p_common_routing_sequence_id => p_common_routing_sequence_id
625 , x_err_code => x_err_code
626 , x_err_msg => x_err_msg
627 );
628
629 If x_err_code <> 0 Then
630 raise e_user_exception;
631 End If;
632
633 WSMPUTIL.find_routing_start( p_common_routing_sequence_id ,
634 v_operation_sequence_id ,
635 x_err_code,
636 x_err_msg);
637
638 Begin
639 select reverse_cumulative_yield
640 into lv_rev_cum
641 from bom_operation_sequences
642 where operation_sequence_id = v_operation_sequence_id;
643
644 Exception
645 WHEN NO_DATA_FOUND THEN RETURN 1;
646 WHEN OTHERS THEN RETURN 1;
647
648 End;
649
650 Begin
651 IF (p_co_prod_supply = 1) THEN
652 select wsc.split
653 into v_split
654 from wsm_Co_products wsc
655 where wsc.bill_sequence_id is not null
656 and wsc.split > 0
657 and wsc.bill_Sequence_id = p_bill_seq_id;
658
659 v_split := v_split/100;
660 lv_rev_cum := v_split*lv_rev_cum;
661
662 ELSE
663 v_split := 0;
664 lv_rev_cum := lv_rev_cum;
665 END IF;
666
667 Exception
668 WHEN OTHERS THEN
669 v_split := 0;
670 lv_rev_cum := lv_rev_cum;
671 END;
672
673 RETURN lv_rev_cum;
674
675 EXCEPTION
676 WHEN NO_DATA_FOUND THEN RETURN 1;
677 WHEN OTHERS THEN RETURN 1;
678 END mrp_rev_cum_yield_unreleased;
679
680
681 /*over-loaded the func so that the old ver of view mrp_ap_wip_jobs_v does
682 not get invalid during patch application
683 This func will not be called in the collections */
684 FUNCTION mrp_rev_cum_yield(p_wip_entity_id in number,
685 p_org_id in number)
686 RETURN NUMBER IS
687
688 CURSOR c_rev_cum IS
689 SELECT bos.reverse_cumulative_yield
690 FROM wip_operations wo,
691 bom_operation_sequences bos
692 WHERE wo.wip_entity_id = p_wip_entity_id
693 AND wo.organization_id = p_org_id
694 AND (wo.quantity_in_queue <> 0 or
695 wo.quantity_running <> 0 or
696 wo.quantity_waiting_to_move <> 0)
697 AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id;
698
699 l_rev_cum number;
700
701 BEGIN
702 l_rev_cum := 1;
703 FOR c_rec in c_rev_cum
704 LOOP
705 BEGIN
706 l_rev_cum := NVL(c_rec.reverse_cumulative_yield,1);
707 END;
708 END LOOP;
709 RETURN(l_rev_cum);
710
711 EXCEPTION
712 WHEN NO_DATA_FOUND THEN RETURN 1;
713 WHEN OTHERS THEN RETURN 1;
714 END mrp_rev_cum_yield;
715
716
717 /*over-loaded the func so that the old ver of view mrp_ap_wip_jobs_v does
718 not get invalid during patch application
719 This func will not be called in the collections */
720 FUNCTION mrp_rev_cum_yield_unreleased(p_wip_entity_id in number,
721 p_org_id in number)
722 RETURN NUMBER IS
723
724 v_op_seq_num number;
725 v_operation_sequence_id number;
726 x_err_msg varchar2(2000);
727 x_err_code number;
728 e_user_exception EXCEPTION;
729 p_common_routing_sequence_id NUMBER;
730 lv_rev_cum number;
731 v_routing_seq_id number;
732
733 BEGIN
734
735 lv_rev_cum := 1;
736
737 Begin
738
739 SELECT nvl(wdj.common_routing_sequence_id,wdj.routing_reference_id)
740 into v_routing_seq_id
741 FROM wip_discrete_jobs wdj
742 WHERE wdj.wip_entity_id = p_wip_entity_id
743 AND wdj.organization_id = p_org_id;
744 Exception
745
746 WHEN NO_DATA_FOUND THEN RETURN 1;
747 WHEN OTHERS THEN RETURN 1;
748
749
750 End;
751
752
753 WSMPUTIL.find_common_routing(
754 p_routing_sequence_id => v_routing_seq_id
755 , p_common_routing_sequence_id => p_common_routing_sequence_id
756 , x_err_code => x_err_code
757 , x_err_msg => x_err_msg
758 );
759
760 If x_err_code <> 0 Then
761 raise e_user_exception;
762 End If;
763
764 WSMPUTIL.find_routing_start( p_common_routing_sequence_id ,
765 v_operation_sequence_id ,
766 x_err_code,
767 x_err_msg);
768
769 Begin
770 select reverse_cumulative_yield
771 into lv_rev_cum
772 from bom_operation_sequences
773 where operation_sequence_id = v_operation_sequence_id;
774
775 Exception
776
777 WHEN NO_DATA_FOUND THEN RETURN 1;
778 WHEN OTHERS THEN RETURN 1;
779
780 End;
781
782
783 return lv_rev_cum;
784 EXCEPTION
785 WHEN NO_DATA_FOUND THEN RETURN 1;
786 WHEN OTHERS THEN RETURN 1;
787 END mrp_rev_cum_yield_unreleased;
788
789 FUNCTION mrp_day_uom_qty(p_uom_code in varchar2,
790 p_quantity in number)
791 RETURN NUMBER IS
792 day_uom boolean;
793 hour_uom boolean;
794 lv_day_qty number;
795 lv_uom_code varchar2(3);
796 lv_hr_uom_code varchar2(3);
797 lv_day_uom_code varchar2(3);
798 lv_hr_conversion_rate number;
799 lv_day_conversion_rate number;
800 lv_base_conversion_rate number;
801
802 CURSOR get_conversion(user_uom_code varchar2,prf_uom_code varchar2) IS
803 SELECT conversion_rate
804 FROM mtl_uom_conversions muc
805 WHERE muc.uom_code = user_uom_code
806 AND muc.inventory_item_id = 0
807 AND NVL(muc.disable_date, SYSDATE + 1) > SYSDATE
808 AND EXISTS (SELECT 1 from mtl_units_of_measure_tl b
809 WHERE b.uom_code = prf_uom_code
810 AND b.uom_class = muc.uom_class);
811
812 BEGIN
813 day_uom := true;
814 hour_uom := true;
815 lv_day_qty := p_quantity;
816 lv_uom_code := p_uom_code;
817 lv_hr_uom_code := fnd_profile.value('BOM:HOUR_UOM_CODE');
818 lv_day_uom_code := fnd_profile.value('MSC:DAY_UOM_CODE');
819
820 /* If the UOM entered in UI is same as MSC:Day UOM return the same value
821 No conversion is neeeded */
822 IF lv_day_uom_code IS NOT NULL THEN
823 IF (lv_day_uom_code = lv_uom_code) THEN
824 RETURN lv_day_qty;
825 END IF;
826 END IF;
827
828
829 IF lv_day_uom_code IS NULL THEN
830 day_uom := false;
831 /* IF MSC:Day UOM is not set then use Bom:Hour UOM to convert into hours
832 and then re-convert into days */
833 END IF;
834
835 IF day_uom THEN
836 /* IF MSC: Day UOM is set, get the conversion rate from user-entered UOM to base UOM */
837 OPEN get_conversion(lv_uom_code,lv_day_uom_code);
838 FETCH get_conversion INTO lv_base_conversion_rate;
839
840 IF get_conversion%NOTFOUND THEN
841 /* If no converision is defined between user defined uom and base UOM
842 do not use MSC: Day UOM, check if BOM:Hour UOM is defined */
843 day_uom := false;
844 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
845
846 ELSE
847 BEGIN
848 /* get the conversion rate between MSC: Day UOM and the base UOM*/
849 SELECT conversion_rate
850 INTO lv_day_conversion_rate
851 FROM mtl_uom_conversions muc
852 WHERE muc.uom_code = lv_day_uom_code
853 AND muc.inventory_item_id = 0
854 AND NVL(muc.disable_date, SYSDATE + 1) > SYSDATE;
855
856 /* get the number of days using above conversions */
857 lv_day_qty := ceil(lv_day_qty * lv_base_conversion_rate / lv_day_conversion_rate);
858
859 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
860
861 RETURN lv_day_qty;
862
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 day_uom := false;
866 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
867
868 WHEN OTHERS THEN
869 day_uom := false;
870 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
871
872 END;
873
874 END IF;
875
876 END IF; -- MSC:Day UOM is used for conversion
877
878 IF (day_uom = false) THEN
879 /* IF the MSC:Day UOM is not set and Bom:Hour UOM is set and USer UOM is same as Bom:Hour UOM ,
880 return the number divided by 24 to convert into days */
881 IF lv_hr_uom_code IS NOT NULL THEN
882 IF (lv_hr_uom_code = lv_uom_code) THEN
883 lv_day_qty := ceil( lv_day_qty / 24 );
884 RETURN lv_day_qty;
885 END IF;
886 ELSE hour_uom:= FALSE;
887 END IF;
888 IF hour_uom THEN
889 /* IF BOM: Hour UOM is set and for any exception day_uom is false, get the conversion rate
890 from user-entered UOM and base UOM */
891 OPEN get_conversion(lv_uom_code,lv_hr_uom_code);
892 FETCH get_conversion INTO lv_base_conversion_rate;
893
894 IF get_conversion%NOTFOUND THEN
895 /* IF conversion between BOM: Hour UOM and user UOM is not defined,
896 return number/24 to get the number of days */
897 lv_day_qty := ceil( lv_day_qty / 24);
898 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
899 RETURN lv_day_qty;
900 ELSE
901 BEGIN
902 /* Get conversion betwwen BOM: Hour UOM and base UOM */
903 SELECT nvl(conversion_rate,1/24)
904 INTO lv_hr_conversion_rate
905 FROM mtl_uom_conversions muc
906 WHERE muc.uom_code = lv_hr_uom_code
907 AND muc.inventory_item_id = 0
908 AND NVL(muc.disable_date, SYSDATE + 1) > SYSDATE;
909
910 /* get the number of days using conversions */
911 lv_day_qty := ceil(((lv_day_qty * lv_base_conversion_rate)/lv_hr_conversion_rate) / 24);
912
913 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
914
915 RETURN lv_day_qty;
916
917 EXCEPTION
918
919 WHEN NO_DATA_FOUND THEN
920 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
921 lv_day_qty := ceil( lv_day_qty / 24);
922 RETURN lv_day_qty;
923
924 WHEN OTHERS THEN
925 IF get_conversion%ISOPEN THEN CLOSE get_conversion; END IF;
926 lv_day_qty := ceil( lv_day_qty / 24);
927 RETURN lv_day_qty;
928 END;
929
930 END IF;
931 ELSE
932 lv_day_qty := ceil( lv_day_qty / 24);
933 RETURN lv_day_qty;
934
935 END IF; -- hour uom cond
936
937 END IF; -- Hour UOM is used for conversion
938
939 IF lv_day_qty = 0 THEN
940 lv_day_qty := 1 ;
941 END IF;
942
943 RETURN(lv_day_qty);
944
945 EXCEPTION
946 WHEN NO_DATA_FOUND THEN RETURN ceil(lv_day_qty/24);
947 WHEN OTHERS THEN RETURN ceil(lv_day_qty/24);
948
949 END mrp_day_uom_qty;
950 FUNCTION get_primary_quantity(p_org_id in number,
951 p_item_id in number,
952 p_primary_uom_code in varchar2)
953 RETURN NUMBER IS
954 lv_uom_code varchar2(3);
955 BEGIN
956 BEGIN
957
958 select uom_code
959 into lv_uom_code
960 from mtl_units_of_measure
961 where uom_class = v_yield_uom_class
962 and base_uom_flag = 'Y';
963 EXCEPTION
964 WHEN NO_DATA_FOUND THEN return -99999;
965
966 END;
967 return( inv_convert.inv_um_convert(
968 item_id => p_item_id,
969 precision => 9,
970 from_quantity => 1,
971 from_unit => p_primary_uom_code,
972 to_unit => lv_uom_code,
973 from_name => null,
974 to_name => null) );
975
976 exception
977 when others then return 1;
978
979 END get_primary_quantity;
980
981 FUNCTION GET_RESOURCE_OVERHEAD(res_id IN NUMBER, dept_id IN NUMBER,
982 org_id IN NUMBER, res_cost IN NUMBER)
983 return NUMBER
984 IS
985
986 v_overhead NUMBER := 0;
987 BASIS_RESOURCE_UNITS CONSTANT INTEGER := 3;
988 BASIS_RESOURCE_VALUE CONSTANT INTEGER := 4;
989
990 CST_FROZEN CONSTANT INTEGER := 1;
991
992 BEGIN
993
994 SELECT NVL(SUM(decode(basis_type,
995 BASIS_RESOURCE_VALUE, res_cost * NVL(rate_or_amount,0),
996 NVL(rate_or_amount,0)
997 )
998 ),0)
999 INTO v_overhead
1000 FROM cst_department_overheads
1001 WHERE organization_id = org_id
1002 AND department_id = dept_id
1003 AND cost_type_id = CST_FROZEN
1004 AND basis_type in (BASIS_RESOURCE_VALUE, BASIS_RESOURCE_UNITS)
1005 AND overhead_id IN (SELECT overhead_id
1006 FROM cst_resource_overheads res
1007 WHERE res.organization_id = org_id
1008 AND res.resource_id = res_id
1009 AND cost_type_id = CST_FROZEN);
1010
1011 RETURN v_overhead;
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 v_overhead := 0;
1016 RETURN v_overhead;
1017
1018 END GET_RESOURCE_OVERHEAD;
1019
1020 /* Check whether bom version is above I. If yes return true else return false.*/
1021 FUNCTION CHECK_BOM_VER
1022 return NUMBER
1023 IS
1024 lv_patch_level VARCHAR2(100);
1025 lv_bom_ver NUMBER;
1026 lv_family_pack VARCHAR2(10);
1027
1028 BEGIN
1029
1030 select nvl(fpi.patch_level, 'Not Available')
1031 into lv_patch_level
1032 from fnd_application_vl fav, fnd_product_installations fpi
1033 where fav.application_id = fpi.application_id and
1034 fpi.APPLICATION_ID in (702);
1035
1036 If lv_patch_level = 'Not Available' Then
1037 return G_BOM_LESS_THAN_J;
1038 End if;
1039
1040 lv_family_pack := SUBSTR(lv_patch_level,1,3);
1041
1042 IF lv_family_pack = '11i' Then
1043
1044 lv_bom_ver := ASCII( SUBSTR(lv_patch_level,-1,1) );
1045
1046 if lv_bom_ver > 73 Then
1047 return G_BOM_GREATER_THAN_EQUAL_J;
1048 else
1049 return G_BOM_LESS_THAN_J;
1050 End if;
1051
1052 ELSE
1053 return G_BOM_GREATER_THAN_EQUAL_J;
1054
1055 END IF;
1056
1057 EXCEPTION
1058 When Others Then return G_BOM_LESS_THAN_J;
1059
1060 END CHECK_BOM_VER;
1061
1062 FUNCTION CHECK_AHL_VER
1063 return NUMBER
1064 IS
1065 lv_retval BOOLEAN;
1066 lv_dummy1 VARCHAR2(32);
1067 lv_dummy2 VARCHAR2(32);
1068 l_applsys_schema_ahl VARCHAR2(32);
1069 lv_ahl_exists NUMBER;
1070
1071 BEGIN
1072
1073 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'AHL', lv_dummy1, lv_dummy2, l_applsys_schema_ahl);
1074
1075 SELECT count(1) into lv_ahl_exists FROM all_tab_columns
1076 WHERE (OWNER, TABLE_NAME, COLUMN_NAME) in ((l_applsys_schema_ahl, 'AHL_SCHEDULE_MATERIALS', 'COMPLETED_QUANTITY'));
1077
1078 IF lv_ahl_exists =1 THEN
1079 return G_AHL_GREATER_THAN_EQUAL_J;
1080 else
1081 return G_AHL_LESS_THAN_J;
1082 End if;
1083
1084
1085 EXCEPTION
1086 When Others Then return G_AHL_LESS_THAN_J;
1087
1088 END CHECK_AHL_VER;
1089
1090
1091 FUNCTION GET_CURRENT_OP_SEQ_NUM( p_org_id IN NUMBER
1092 , p_wip_entity_id IN NUMBER)
1093 RETURN NUMBER
1094 IS
1095
1096 l_rev_cum number := 1;
1097 v_split number;
1098 qty_to_move number;
1099 curr_op_seq_id number;
1100 v_curr_op_seq_num number;
1101
1102 v_sql_stmt1 varchar2(1000);
1103
1104 BEGIN
1105
1106 begin
1107
1108 v_sql_stmt1 := ' SELECT bos.Operation_Seq_Num, nvl(bos.reverse_cumulative_yield,1), wo.quantity_waiting_to_move '
1109 ||' , nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) '
1110 ||' FROM MRP_SN_OPR_SEQS bos, '
1111 ||' MRP_SN_WOPRS wo '
1112 ||' WHERE wo.organization_id = :p_org_id '
1113 ||' AND wo.wip_entity_id = :p_wip_entity_id '
1114 ||' AND (wo.quantity_in_queue <> 0 OR '
1115 ||' wo.quantity_running <> 0 OR '
1116 ||' wo.quantity_waiting_to_move <> 0) '
1117 ||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = bos.operation_sequence_id ';
1118
1119 EXECUTE IMMEDIATE v_sql_stmt1
1120 INTO v_curr_op_seq_num, l_rev_cum, qty_to_move, curr_op_seq_id
1121 USING p_org_id, p_wip_entity_id;
1122
1123 exception
1124 when others then
1125 return to_number(NULL);
1126 end;
1127
1128 RETURN v_curr_op_seq_num;
1129
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 v_curr_op_seq_num := to_number(NULL);
1133 RETURN v_curr_op_seq_num;
1134
1135 END GET_CURRENT_OP_SEQ_NUM;
1136
1137 /* To get the current routing operation sequence number from job details */
1138 FUNCTION GET_CURRENT_JD_OP_SEQ_NUM( p_org_id IN NUMBER
1139 , p_wip_entity_id IN NUMBER)
1140 RETURN NUMBER
1141 IS
1142
1143 v_curr_op_seq_num number := null;
1144
1145 v_sql_stmt1 varchar2(1000);
1146
1147 BEGIN
1148
1149 begin
1150
1151 v_sql_stmt1 := ' SELECT wo1.Wsm_Op_Seq_Num '
1152 ||' FROM MRP_SN_WOPRS wo, '
1153 ||' MRP_SN_WOPRS wo1 '
1154 ||' WHERE wo.organization_id = :p_org_id '
1155 ||' AND wo.wip_entity_id = :p_wip_entity_id '
1156 ||' AND (wo.quantity_in_queue <> 0 OR '
1157 ||' wo.quantity_running <> 0 OR '
1158 ||' wo.quantity_waiting_to_move <> 0) '
1159 ||' AND wo1.wip_entity_id = wo.wip_entity_id '
1160 ||' AND nvl(wo.operation_sequence_id,wo.previous_operation_seq_id) = wo1.operation_sequence_id'
1161 ||' AND rownum=1';
1162
1163 EXECUTE IMMEDIATE v_sql_stmt1
1164 INTO v_curr_op_seq_num
1165 USING p_org_id, p_wip_entity_id;
1166
1167 exception
1168 when others then
1169 return to_number(NULL);
1170 end;
1171
1172 RETURN v_curr_op_seq_num;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 v_curr_op_seq_num := to_number(NULL);
1177 RETURN v_curr_op_seq_num;
1178
1179 END GET_CURRENT_JD_OP_SEQ_NUM;
1180
1181 FUNCTION GET_CURRENT_JOB_OP_SEQ_NUM( p_org_id IN NUMBER
1182 , p_wip_entity_id IN NUMBER)
1183 RETURN NUMBER
1184 IS
1185
1186 v_curr_op_seq_num number := null;
1187 lv_status number := null;
1188
1189 v_sql_stmt varchar2(1000);
1190
1191 BEGIN
1192
1193 begin
1194
1195 v_sql_stmt := ' Select wdj.status_type from '
1196 ||' MRP_SN_DSCR_JOBS wdj '
1197 ||' Where wdj.wip_entity_id = :p_wip_entity_id ';
1198
1199 EXECUTE IMMEDIATE v_sql_stmt
1200 INTO lv_status
1201 USING p_wip_entity_id;
1202 exception
1203 when others then
1204 return to_number(NULL);
1205 end;
1206
1207 If lv_status = 1 Then
1208 begin
1209
1210 v_sql_stmt := ' SELECT wo.Operation_Seq_Num '
1211 ||' FROM MRP_SN_WOPRS wo '
1212 ||' WHERE wo.organization_id = :p_org_id '
1213 ||' AND wo.wip_entity_id = :p_wip_entity_id ';
1214
1215 EXECUTE IMMEDIATE v_sql_stmt
1216 INTO v_curr_op_seq_num
1217 USING p_org_id, p_wip_entity_id;
1218
1219 exception
1220 when others then
1221 return to_number(NULL);
1222 end;
1223
1224 Else
1225 begin
1226
1227 v_sql_stmt := ' SELECT wo.Operation_Seq_Num '
1228 ||' FROM MRP_SN_WOPRS wo '
1229 ||' WHERE wo.organization_id = :p_org_id '
1230 ||' AND wo.wip_entity_id = :p_wip_entity_id '
1231 ||' AND (wo.quantity_in_queue <> 0 OR '
1232 ||' wo.quantity_running <> 0 OR '
1233 ||' wo.quantity_waiting_to_move <> 0) ' ;
1234
1235 EXECUTE IMMEDIATE v_sql_stmt
1236 INTO v_curr_op_seq_num
1237 USING p_org_id, p_wip_entity_id;
1238
1239 exception
1240 when others then
1241 return to_number(NULL);
1242 end;
1243 END IF;
1244
1245 RETURN v_curr_op_seq_num;
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 v_curr_op_seq_num := to_number(NULL);
1250 RETURN v_curr_op_seq_num;
1251
1252 END GET_CURRENT_JOB_OP_SEQ_NUM;
1253
1254 FUNCTION GET_CURRENT_RTNG_OP_SEQ_NUM( p_org_id IN NUMBER
1255 , p_wip_entity_id IN NUMBER)
1256 RETURN NUMBER
1257 IS
1258
1259 v_curr_rtng_op_seq_num number := 50000;
1260 v_sql_stmt varchar2(1000);
1261
1262 BEGIN
1263
1264 v_sql_stmt := ' SELECT nvl(wo.wsm_op_seq_num,50000) '
1265 ||' FROM MRP_SN_WOPRS wo '
1266 ||' WHERE wo.organization_id = :p_org_id '
1267 ||' AND wo.wip_entity_id = :p_wip_entity_id '
1268 ||' AND (wo.quantity_in_queue <> 0 OR '
1269 ||' wo.quantity_running <> 0 OR '
1270 ||' wo.quantity_waiting_to_move <> 0) ' ;
1271
1272
1273 EXECUTE IMMEDIATE v_sql_stmt
1274 INTO v_curr_rtng_op_seq_num
1275 USING p_org_id, p_wip_entity_id;
1276
1277
1278 RETURN v_curr_rtng_op_seq_num;
1279
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 v_curr_rtng_op_seq_num := to_number(NULL);
1283 RETURN v_curr_rtng_op_seq_num;
1284
1285 END GET_CURRENT_RTNG_OP_SEQ_NUM;
1286
1287 FUNCTION GETWFUSER(ORIG_SYS_ID in varchar2)
1288 RETURN varchar2
1289 IS
1290 p_wf_name varchar2(320);
1291 p_wf_full_name varchar2(1000);
1292 BEGIN
1293 wf_directory.getusername('PER',orig_sys_id,p_wf_name,p_wf_full_name) ;
1294 RETURN substr(p_wf_name,1,100);
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 p_wf_name := null;
1298 RETURN substr(p_wf_name,1,100);
1299 END GETWFUSER;
1300
1301
1302 FUNCTION GET_ROUTING_SEQ_ID ( p_primary_item_id IN NUMBER,
1303 p_org_id IN NUMBER,
1304 p_alt_ROUTING_DESIG IN VARCHAR2,
1305 p_common_rout_seq_id IN NUMBER)
1306 RETURN NUMBER
1307 IS
1308
1309 v_temp_sql_stmt varchar2(1000);
1310 v_routing_seq_id number;
1311
1312 BEGIN
1313
1314 v_temp_sql_stmt := ' SELECT ROUTING_SEQUENCE_ID '
1315 ||' FROM MRP_SN_OPR_RTNS '
1316 ||' WHERE ASSEMBLY_ITEM_ID = :p_primary_item_id '
1317 ||' AND ORGANIZATION_ID = :p_org_id '
1318 ||' AND nvl(ALTERNATE_ROUTING_DESIGNATOR,''-1'') = :p_alt_ROUTING_DESIG '
1319 ||' AND COMMON_ROUTING_SEQUENCE_ID = :p_common_rout_seq_id ';
1320
1321 EXECUTE IMMEDIATE v_temp_sql_stmt
1322 INTO v_routing_seq_id
1323 USING p_primary_item_id ,p_org_id ,p_alt_ROUTING_DESIG ,p_common_rout_seq_id;
1324
1325 RETURN v_routing_seq_id;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 RETURN to_number(null);
1330
1331 END get_routing_seq_id;
1332
1333 FUNCTION GET_PO_ORIG_NEED_BY_DATE ( p_po_header_id IN NUMBER,
1334 p_po_line_id IN NUMBER,
1335 p_po_line_location_id IN NUMBER
1336 )
1337 RETURN DATE
1338 IS
1339 l_orig_need_by_date DATE;
1340 v_sql_stmt VARCHAR2(2000);
1341
1342 BEGIN
1343
1344 v_sql_stmt :=
1345 ' select need_by_date '
1346 ||' from ( '
1347 ||' SELECT revision_num, '
1348 ||' need_by_date, '
1349 ||' quantity, '
1350 ||' RANK() OVER (PARTITION BY LINE_LOCATION_ID, PO_HEADER_ID,PO_LINE_ID '
1351 ||' order by revision_num) as seqnumber '
1352 ||' FROM po_line_locations_archive_all '
1353 ||' where PO_HEADER_ID = :p_po_header_id '
1354 ||' and po_line_id = :p_po_line_id '
1355 ||' and line_location_id = :p_po_line_location_id '
1356 ||' ) '
1357 ||' where seqnumber = 1 ';
1358
1359 EXECUTE IMMEDIATE v_sql_stmt INTO l_orig_need_by_date
1360 USING p_po_header_id,
1361 p_po_line_id,
1362 p_po_line_location_id;
1363
1364 return l_orig_need_by_date;
1365
1366 EXCEPTION WHEN OTHERS THEN
1367 return null;
1368 END GET_PO_ORIG_NEED_BY_DATE;
1369
1370 FUNCTION GET_PO_ORIG_QUANTITY ( p_po_header_id IN NUMBER,
1371 p_po_line_id IN NUMBER,
1372 p_po_line_location_id IN NUMBER
1373 )
1374 RETURN NUMBER IS
1375 l_quantity NUMBER ;
1376 v_sql_stmt VARCHAR2(2000);
1377
1378 BEGIN
1379
1380 v_sql_stmt :=
1381 ' select quantity '
1382 ||' from ( '
1383 ||' SELECT revision_num, '
1384 ||' need_by_date, '
1385 ||' quantity, '
1386 ||' RANK() OVER (PARTITION BY LINE_LOCATION_ID, PO_HEADER_ID,PO_LINE_ID '
1387 ||' order by revision_num) as seqnumber '
1388 ||' FROM po_line_locations_archive_all '
1389 ||' where PO_HEADER_ID = :p_po_header_id '
1390 ||' and po_line_id = :p_po_line_id '
1391 ||' and line_location_id = :p_po_line_location_id '
1392 ||' ) '
1393 ||' where seqnumber = 1 ';
1394
1395 EXECUTE IMMEDIATE v_sql_stmt INTO l_quantity
1396 USING p_po_header_id,
1397 p_po_line_id,
1398 p_po_line_location_id;
1399
1400 return l_quantity;
1401
1402 EXCEPTION WHEN OTHERS THEN
1403 return to_number(NULL);
1404 END GET_PO_ORIG_QUANTITY;
1405
1406 FUNCTION get_userenv_lang
1407 RETURN varchar2
1408 IS
1409 BEGIN
1410 RETURN userenv('LANG');
1411
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 RETURN 'US';
1415 END get_userenv_lang;
1416
1417 FUNCTION GET_COST_TYPE_ID ( p_org_id IN NUMBER )
1418 RETURN NUMBER
1419 IS
1420
1421 CURSOR COST_C IS
1422 SELECT NVL(FND_PROFILE.VALUE('MSC_COST_TYPE'),
1423 DECODE(cost_org.primary_cost_method,1,1,2,cost_org.AVG_RATES_COST_TYPE_ID,1))
1424 FROM mtl_parameters org, mtl_parameters cost_org
1425 WHERE org.cost_organization_id = cost_org.organization_id
1426 and org.organization_id = p_org_id;
1427
1428 CURSOR COST_C2 IS
1429 SELECT NVL(FND_PROFILE.VALUE('MSC_COST_TYPE'),1)
1430 FROM dual;
1431
1432 l_cost_type_id NUMBER:= NULL;
1433
1434 BEGIN
1435
1436 OPEN COST_C;
1437 FETCH COST_C into l_cost_type_id;
1438 CLOSE COST_C;
1439
1440 IF l_cost_type_id IS NULL THEN
1441 OPEN COST_C2;
1442 FETCH COST_C2 into l_cost_type_id;
1443 CLOSE COST_C2;
1444 END IF;
1445
1446 RETURN(l_cost_type_id);
1447
1448 END GET_COST_TYPE_ID ;
1449
1450 FUNCTION MAP_REGION_TO_SITE (p_last_update_date in DATE)
1451 RETURN NUMBER
1452 IS
1453
1454 Cursor regions_update is
1455 select max(LAST_UPDATE_DATE)
1456 from WSH_REGIONS;
1457
1458 Cursor po_vendors_update(p_date DATE) is
1459 select vendor_site_id
1460 from PO_VENDOR_SITES_ALL
1461 where last_update_date >= p_date and creation_date < p_date;
1462
1463 Cursor all_vendor_sites is
1464 select vendor_site_id,country,state,city,zip
1465 from PO_VENDOR_SITES_ALL;
1466
1467 Cursor new_vendor_sites(p_date DATE) is
1468 select vendor_site_id,country,state,city,zip
1469 from PO_VENDOR_SITES_ALL
1470 where last_update_date >= p_date;
1471
1472 TYPE VendorSiteTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.VENDOR_SITE_ID%TYPE;
1473 TYPE CountryTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.COUNTRY%TYPE;
1474 TYPE StateTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.STATE%TYPE;
1475 TYPE CityTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.CITY%TYPE;
1476 TYPE ZipTblTyp IS TABLE OF PO_VENDOR_SITES_ALL.ZIP%TYPE;
1477 TYPE NumTblTyp IS TABLE OF NUMBER;
1478
1479 l_vendor_site_tab VendorSiteTblTyp ;
1480 l_vendor_site_tab1 VendorSiteTblTyp ;
1481 l_country_tab CountryTblTyp;
1482 l_state_tab StateTblTyp;
1483 l_city_tab CityTblTyp;
1484 l_postal_tab ZipTblTyp;
1485 l_region_id_tab NumTblTyp := NumTblTyp();
1486 l_region_type_tab NumTblTyp := NumTblTyp();
1487 l_zone_level_tab NumTblTyp := NumTblTyp();
1488
1489 l_regions WSH_REGIONS_SEARCH_PKG.region_table;
1490
1491 region_last_update DATE := NULL;
1492 l_status NUMBER;
1493 lv_sql_stmt VARCHAR2(8000);
1494 v_current_date DATE;
1495 v_current_user NUMBER;
1496
1497
1498
1499 BEGIN
1500
1501 SELECT SYSDATE,
1502 FND_GLOBAL.USER_ID
1503 INTO v_current_date,
1504 v_current_user
1505 FROM DUAL;
1506
1507 OPEN regions_update;
1508 FETCH regions_update into region_last_update;
1509 CLOSE regions_update;
1510
1511
1512 IF region_last_update is NULL THEN
1513 Return(1);
1514 NULL;
1515 END IF;
1516 -- If wsh_regions has undergone change after last Collection, all mappings are re-established.
1517 IF (p_last_update_date is NULL) OR (region_last_update >= p_last_update_date) THEN
1518 -- delete mrp_region_sites completely.
1519 DELETE FROM MRP_REGION_SITES;
1520
1521 -- Get all vendor sites for re mapping.
1522 OPEN all_vendor_sites;
1523 FETCH all_vendor_sites BULK COLLECT
1524 INTO l_vendor_site_tab,
1525 l_country_tab,
1526 l_state_tab,
1527 l_city_tab,
1528 l_postal_tab;
1529 CLOSE all_vendor_sites;
1530 ELSE
1531 OPEN po_vendors_update (p_last_update_date);
1532 FETCH po_vendors_update BULK COLLECT
1533 INTO l_vendor_site_tab1;
1534 CLOSE po_vendors_update;
1535 -- bug 5985580
1536
1537 FOR j IN 1..l_vendor_site_tab1.COUNT LOOP
1538 BEGIN
1539 DELETE FROM MRP_REGION_SITES
1540 where vendor_site_id =l_vendor_site_tab1(j);
1541 EXCEPTION WHEN OTHERS THEN
1542 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1543 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in DELETEING Region to Sites');
1544 NULL;
1545 END;
1546 END LOOP;
1547 --
1548 -- If wsh_regions has not undergone any change after the last Collection, map only new vendor sites.
1549 OPEN new_vendor_sites(p_last_update_date);
1550 FETCH new_vendor_sites BULK COLLECT
1551 INTO l_vendor_site_tab,
1552 l_country_tab,
1553 l_state_tab,
1554 l_city_tab,
1555 l_postal_tab;
1556 CLOSE new_vendor_sites;
1557 END IF;
1558
1559 FOR i IN 1..l_vendor_site_tab.COUNT LOOP
1560 BEGIN
1561 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(
1562 p_country => null, -- p_country
1563 p_country_region => null, -- p_country_region
1564 p_state => null, -- p_state
1565 p_city => l_city_tab(i), -- p_city
1566 p_postal_code_from => l_postal_tab(i), -- p_postal_code_from
1567 p_postal_code_to => l_postal_tab(i), -- p_postal_code_to
1568 p_country_code => l_country_tab(i), -- p_country_code
1569 p_country_region_code => null, -- p_country_region_code
1570 p_state_code => l_state_tab(i), -- p_state_code
1571 p_city_code => null, -- p_city_code
1572 p_lang_code => userenv('LANG'), -- p_lang_code
1573 p_location_id => null, -- p_location_id
1574 p_zone_flag => 'Y', -- p_zone_flag
1575 x_status => l_status,
1576 x_regions => l_regions);
1577
1578 EXCEPTION WHEN OTHERS THEN
1579 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1580 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Get All Region Matches for City '||l_city_tab(i)||' State ' ||l_state_tab(i)||' Country '||l_country_tab(i) );
1581
1582 NULL;
1583 END;
1584
1585 IF l_regions.count > 0 THEN
1586 l_region_id_tab.EXTEND(l_regions.count);
1587 l_region_type_tab.EXTEND(l_regions.count);
1588 l_zone_level_tab.EXTEND(l_regions.count);
1589
1590 FOR j IN 1..l_regions.COUNT LOOP
1591 l_region_id_tab(j) := l_regions(j).region_id;
1592 IF(l_regions(j).region_type = 10) THEN
1593 l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 1;
1594 ELSE
1595 l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 0;
1596 END IF;
1597 l_zone_level_tab(j) := l_regions(j).zone_level;
1598 END LOOP;
1599 FORALL k IN 1..l_regions.count
1600 INSERT INTO MRP_REGION_SITES(region_id,vendor_site_id, region_type, zone_level, last_update_date, last_updated_by, creation_date, created_by)
1601 VALUES (l_region_id_tab(k), l_vendor_site_tab(i),l_region_type_tab(k),l_zone_level_tab(k), v_current_date, v_current_user, v_current_date, v_current_user);
1602 END IF;
1603
1604 END LOOP;
1605 COMMIT;
1606 Return(1);
1607
1608
1609 EXCEPTION WHEN OTHERS THEN
1610 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1611 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Mapping Region to Sites');
1612 Return(0);
1613 END MAP_REGION_TO_SITE ;
1614
1615 FUNCTION get_ship_set_name(p_SHIP_SET_ID in number)
1616 RETURN VARCHAR2
1617 IS
1618 l_set_name VARCHAR2(30);
1619 BEGIN
1620
1621 IF (p_SHIP_SET_ID is not null) THEN
1622 select set_name into l_set_name
1623 from oe_sets
1624 where set_id=p_SHIP_SET_ID;
1625
1626 RETURN l_set_name;
1627
1628 ELSE
1629
1630 l_set_name := null;
1631 RETURN l_set_name;
1632
1633 END IF;
1634
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 l_set_name := null;
1638 RETURN l_set_name;
1639
1640 END get_ship_set_name;
1641
1642 FUNCTION get_arrival_set_name(p_ARRIVAL_SET_ID in number)
1643 RETURN VARCHAR2
1644 IS
1645 l_set_name VARCHAR2(30);
1646 BEGIN
1647
1648 IF (p_ARRIVAL_SET_ID is not null) THEN
1649 select set_name into l_set_name
1650 from oe_sets
1651 where set_id=p_ARRIVAL_SET_ID;
1652
1653 RETURN l_set_name;
1654
1655 ELSE
1656
1657 l_set_name := null;
1658 RETURN l_set_name;
1659
1660 END IF;
1661
1662 EXCEPTION
1663 WHEN OTHERS THEN
1664 l_set_name := null;
1665 RETURN l_set_name;
1666
1667 END get_arrival_set_name;
1668
1669 /* CMRO Collection Impact */
1670 FUNCTION GET_CMRO_CUSTOMER_ID
1671 RETURN NUMBER IS
1672 lv_customer_id NUMBER;
1673 BEGIN
1674
1675 /* Need to make sure that the AHL Customer Name is based of HZ Parties */
1676 /* I am settin other global variables in the function hoping and invoking
1677 * this function once for each row to avoid the hit 3 times?.
1678 * Should not we actually implement this in the Snap Shot?.
1679 */
1680
1681 Begin
1682 lv_customer_id := fnd_profile.value('AHL_APS_CUSTOMER_NAME');
1683 v_cmro_customer_id := lv_customer_id;
1684
1685 Exception
1686 When others then
1687 lv_customer_id := null;
1688 Return lv_customer_id ;
1689 End ;
1690
1691 RETURN lv_customer_id;
1692
1693 END GET_CMRO_CUSTOMER_ID;
1694
1695 FUNCTION GET_CMRO_SHIP_TO return NUMBER IS
1696 lv_customer_id NUMBER;
1697 lv_cmro_ship_to NUMBER;
1698 BEGIN
1699
1700 Begin
1701 lv_customer_id := fnd_profile.value('AHL_APS_CUSTOMER_NAME');
1702
1703 Exception
1704 When others then
1705 lv_customer_id := null;
1706 Return lv_customer_id ;
1707 End ;
1708
1709
1710 Begin
1711 SELECT
1712 SITE_USES_ALL.site_use_id SR_TP_SITE_ID
1713 INTO
1714 lv_cmro_ship_to
1715 FROM
1716 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
1717 HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
1718 HZ_CUST_ACCOUNTS CUST_ACCT,
1719 HR_ORGANIZATION_INFORMATION O,
1720 HR_ALL_ORGANIZATION_UNITS_TL OTL
1721 WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
1722 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
1723 AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
1724 AND OTL.LANGUAGE = userenv('LANG')
1725 AND SITE_USES_ALL.ORG_ID is NOT NULL
1726 AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
1727 AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
1728 AND CUST_ACCT.CUST_ACCOUNT_ID = lv_customer_id;
1729
1730 Exception
1731 When others then
1732 lv_cmro_ship_to := null ;
1733 End ;
1734
1735 RETURN lv_cmro_ship_to;
1736
1737
1738 END GET_CMRO_SHIP_TO ;
1739
1740
1741 FUNCTION GET_CMRO_BILL_TO return NUMBER IS
1742 lv_customer_id NUMBER;
1743 lv_cmro_bill_to NUMBER;
1744 BEGIN
1745
1746
1747 Begin
1748 lv_customer_id := fnd_profile.value('AHL_APS_CUSTOMER_NAME');
1749
1750 Exception
1751 When others then
1752 lv_customer_id := null;
1753 lv_cmro_bill_to := null;
1754 Return lv_cmro_bill_to ;
1755 End ;
1756
1757
1758 Begin
1759 SELECT
1760 SITE_USES_ALL.site_use_id SR_TP_SITE_ID
1761 INTO
1762 lv_cmro_bill_to
1763 FROM
1764 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
1765 HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
1766 HZ_CUST_ACCOUNTS CUST_ACCT,
1767 HR_ORGANIZATION_INFORMATION O,
1768 HR_ALL_ORGANIZATION_UNITS_TL OTL
1769 WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
1770 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
1771 AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
1772 AND OTL.LANGUAGE = userenv('LANG')
1773 AND SITE_USES_ALL.ORG_ID is NOT NULL
1774 AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
1775 AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
1776 AND CUST_ACCT.CUST_ACCOUNT_ID = lv_customer_id;
1777
1778 Exception
1779 When others then
1780 lv_cmro_bill_to := null ;
1781 End ;
1782
1783 RETURN lv_cmro_bill_to;
1784
1785 END GET_CMRO_BILL_TO ;
1786
1787
1788 /* Check whether wsh version is above I. If yes return true else return false.*/
1789 FUNCTION CHECK_WSH_VER
1790 return NUMBER
1791 IS
1792 lv_patch_level VARCHAR2(100);
1793 lv_wsh_ver NUMBER;
1794 lv_family_pack VARCHAR2(10);
1795
1796 BEGIN
1797
1798 select nvl(fpi.patch_level, 'Not Available')
1799 into lv_patch_level
1800 from fnd_application_vl fav, fnd_product_installations fpi
1801 where fav.application_id = fpi.application_id and
1802 fpi.APPLICATION_ID in (665);
1803
1804 If lv_patch_level = 'Not Available' Then
1805 return G_WSH_LESS_THAN_J;
1806 End if;
1807
1808 lv_family_pack := SUBSTR(lv_patch_level,1,3);
1809
1810 IF lv_family_pack = '11i' Then
1811
1812 lv_wsh_ver := ASCII( SUBSTR(lv_patch_level,-1,1) );
1813
1814 if lv_wsh_ver > 73 Then
1815 return G_WSH_GREATER_THAN_EQUAL_J;
1816 else
1817 return G_WSH_LESS_THAN_J;
1818 End if;
1819
1820 ELSE
1821 return G_WSH_GREATER_THAN_EQUAL_J;
1822
1823 END IF;
1824
1825 EXCEPTION
1826 When Others Then return G_WSH_LESS_THAN_J;
1827
1828 END CHECK_WSH_VER;
1829
1830 Procedure SUBMIT_CR
1831 ( p_user_name IN VARCHAR2,
1832 p_resp_name IN VARCHAR2,
1833 p_application_name IN VARCHAR2,
1834 p_application_id IN NUMBER,
1835 p_batch_id IN NUMBER,
1836 p_conc_req_short_name IN varchar2 ,
1837 p_conc_req_desc IN varchar2 ,
1838 p_owning_applshort_name IN varchar2,
1839 p_load_type IN NUMBER,
1840 p_request_id IN OUT NOCOPY Number) IS
1841
1842 l_request number;
1843 l_result BOOLEAN;
1844
1845 l_user_id NUMBER;
1846 lv_log_msg varchar2(500);
1847
1848 BEGIN
1849
1850 BEGIN
1851
1852 SELECT USER_ID
1853 INTO l_user_id
1854 FROM FND_USER
1855 WHERE USER_NAME = p_user_name;
1856
1857 EXCEPTION
1858 WHEN NO_DATA_FOUND THEN
1859 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Error in launching the concurrent request : NO_USER_DEFINED');
1860 raise_application_error (-20001, 'NO_USER_DEFINED');
1861 END;
1862
1863 IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_RELEASE,lv_log_msg) THEN
1864 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_RELEASE,
1865 l_user_id,
1866 -1, --l_resp_id,
1867 -1 --l_application_id
1868 );
1869 ELSE
1870 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
1871 raise_application_error (-20001, lv_log_msg);
1872 END IF;
1873
1874
1875 --=============================================
1876
1877 l_result := fnd_request.set_mode(TRUE);
1878
1879 IF p_load_type >0 THEN
1880 l_request := FND_REQUEST.SUBMIT_REQUEST
1881 (p_owning_applshort_name,
1882 p_conc_req_short_name,
1883 p_conc_req_desc,
1884 null,
1885 FALSE,
1886 p_batch_id);
1887 ELSE
1888 l_request := FND_REQUEST.SUBMIT_REQUEST
1889 (p_owning_applshort_name,
1890 p_conc_req_short_name,
1891 p_conc_req_desc,
1892 null,
1893 FALSE);
1894 END IF ;
1895
1896 IF nvl( l_request,0) = 0 THEN
1897 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Error in launching the concurrent request ');
1898 ELSE
1899 p_request_id := l_request;
1900 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Concurrent Request ID ' || p_request_id || ' has been submitted ');
1901 END IF;
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 RAISE;
1905
1906 END SUBMIT_CR;
1907
1908 FUNCTION validateUser (pUSERID IN NUMBER,
1909 pTASK IN NUMBER,
1910 pMESSAGE IN OUT NOCOPY varchar2)
1911 return BOOLEAN IS
1912 lv_result NUMBER := 0;
1913 BEGIN
1914 IF pTASK = MSC_UTIL.TASK_COLL THEN
1915
1916 select 1
1917 into lv_result
1918 from fnd_responsibility resp
1919 , FND_USER_RESP_GROUPS user_resp
1920 where resp.responsibility_id = user_resp.responsibility_id
1921 and resp.application_id = user_resp.responsibility_application_id
1922 and resp.responsibility_key = 'APS_COLLECTIONS'
1923 and user_resp.user_id = pUSERID
1924 and rownum =1 ;
1925
1926 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
1927
1928 select 1
1929 into lv_result
1930 from fnd_responsibility resp,
1931 FND_USER_RESP_GROUPS user_resp
1932 where resp.responsibility_id = user_resp.responsibility_id
1933 and resp.application_id = user_resp.responsibility_application_id
1934 and resp.responsibility_key = 'APS_RELEASE'
1935 and user_resp.user_id = pUSERID
1936 and rownum =1 ;
1937
1938 END IF;
1939
1940 IF lv_result = 1 THEN
1941 RETURN TRUE;
1942 ELSIF lv_result = 0 THEN
1943 RETURN FALSE;
1944 END IF;
1945
1946 EXCEPTION
1947 WHEN NO_DATA_FOUND THEN
1948 IF pTASK = MSC_UTIL.TASK_COLL THEN
1949 pMESSAGE := 'User not defnied or not assigned to the responsibility - ''APS COLLECTIONS''';
1950 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
1951 pMESSAGE := 'User not defnied or not assigned to the responsibility - ''APS RELEASE''';
1952 END IF;
1953 Return FALSE;
1954 WHEN OTHERS THEN
1955 pMESSAGE := 'Unable to validate user because of unexpected error';
1956 RAISE;
1957 END;
1958
1959
1960 PROCEDURE msc_Initialize(pTASK IN NUMBER,
1961 pUSERID IN NUMBER,
1962 pRESPID IN NUMBER,
1963 pAPPLID IN NUMBER) IS
1964
1965 lv_resp_id NUMBER;
1966 lv_application_id NUMBER;
1967 PRAGMA AUTONOMOUS_TRANSACTION;
1968 BEGIN
1969 IF pTASK = MSC_UTIL.TASK_COLL THEN
1970
1971 select resp.responsibility_id, resp.application_id
1972 into lv_resp_id, lv_application_id
1973 from fnd_responsibility resp,
1974 FND_USER_RESP_GROUPS user_resp
1975 where resp.responsibility_id = user_resp.responsibility_id
1976 and resp.application_id = user_resp.responsibility_application_id
1977 and resp.responsibility_key = 'APS_COLLECTIONS'
1978 and user_resp.user_id = pUSERID
1979 and rownum =1 ;
1980
1981 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
1982
1983 select resp.responsibility_id, resp.application_id
1984 into lv_resp_id, lv_application_id
1985 from fnd_responsibility resp,
1986 FND_USER_RESP_GROUPS user_resp
1987 where resp.responsibility_id = user_resp.responsibility_id
1988 and resp.application_id = user_resp.responsibility_application_id
1989 and resp.responsibility_key = 'APS_RELEASE'
1990 and user_resp.user_id = pUSERID
1991 and rownum =1 ;
1992
1993 ELSIF pTASK = MSC_UTIL.TASK_USER_DEFINED THEN
1994
1995 lv_resp_id := pRESPID;
1996 lv_application_id := pAPPLID;
1997
1998 END IF;
1999
2000 FND_GLOBAL.APPS_INITIALIZE( pUSERID,
2001 lv_resp_id,
2002 lv_application_id);
2003 commit;
2004 EXCEPTION
2005 WHEN NO_DATA_FOUND THEN
2006 IF pTASK = MSC_UTIL.TASK_COLL THEN
2007 raise_application_error (-20001, 'User not defnied or not assigned to the responsibility - ''APS COLLECTIONS''');
2008 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
2009 raise_application_error (-20001, 'User not defnied or not assigned to the responsibility - ''APS RELEASE''');
2010 ELSE
2011 raise_application_error (-20001, 'User not defnied or not assigned to the required responsibility');
2012 END IF;
2013 WHEN OTHERS THEN
2014 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Unable to initialize user because of unexpected error');
2015 RAISE;
2016 END;
2017
2018 END MRP_CL_FUNCTION;
[Home] [Help]