1 PACKAGE BODY MRP_CL_FUNCTION AS
2 /* $Header: MRPCLHAB.pls 120.34 2012/03/22 11:53:59 swundapa 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 PVS.vendor_site_id,PVS.country,PVS.state,PVS.city,PVS.zip,nvl(HL.language,userenv('LANG')) lang
1465 from AP_SUPPLIER_SITES_ALL PVS ,HZ_LOCATIONS HL
1466 WHERE PVS.LOCATION_ID = HL.LOCATION_ID(+);
1467
1468 Cursor new_vendor_sites(p_date DATE) is
1469 select PVS.vendor_site_id,PVS.country,PVS.state,PVS.city,PVS.zip,nvl(HL.language,userenv('LANG')) lang
1470 from AP_SUPPLIER_SITES_ALL PVS ,HZ_LOCATIONS HL
1471 where PVS.last_update_date >= p_date
1472 AND PVS.LOCATION_ID = HL.LOCATION_ID(+);
1473
1474 TYPE VendorSiteTblTyp IS TABLE OF AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID%TYPE;
1475 TYPE CountryTblTyp IS TABLE OF AP_SUPPLIER_SITES_ALL.COUNTRY%TYPE;
1476 TYPE StateTblTyp IS TABLE OF AP_SUPPLIER_SITES_ALL.STATE%TYPE;
1477 TYPE CityTblTyp IS TABLE OF AP_SUPPLIER_SITES_ALL.CITY%TYPE;
1478 TYPE ZipTblTyp IS TABLE OF AP_SUPPLIER_SITES_ALL.ZIP%TYPE;
1479 TYPE langTblTyp IS TABLE OF HZ_LOCATIONS.LANGUAGE%TYPE;
1480 TYPE NumTblTyp IS TABLE OF NUMBER;
1481
1482 l_vendor_site_tab VendorSiteTblTyp ;
1483 l_vendor_site_tab1 VendorSiteTblTyp ;
1484 l_country_tab CountryTblTyp;
1485 l_state_tab StateTblTyp;
1486 l_city_tab CityTblTyp;
1487 l_postal_tab ZipTblTyp;
1488 l_lang_tab langTblTyp;
1489 l_region_id_tab NumTblTyp := NumTblTyp();
1490 l_region_type_tab NumTblTyp := NumTblTyp();
1491 l_zone_level_tab NumTblTyp := NumTblTyp();
1492
1493 l_regions WSH_REGIONS_SEARCH_PKG.region_table;
1494
1495 region_last_update DATE := NULL;
1496 l_status NUMBER;
1497 lv_sql_stmt VARCHAR2(8000);
1498 v_current_date DATE;
1499 v_current_user NUMBER;
1500
1501
1502
1503 BEGIN
1504
1505 SELECT SYSDATE,
1506 FND_GLOBAL.USER_ID
1507 INTO v_current_date,
1508 v_current_user
1509 FROM DUAL;
1510
1511 OPEN regions_update;
1512 FETCH regions_update into region_last_update;
1513 CLOSE regions_update;
1514
1515
1516 IF region_last_update is NULL THEN
1517 Return(1);
1518 NULL;
1519 END IF;
1520 -- If wsh_regions has undergone change after last Collection, all mappings are re-established.
1521 IF (p_last_update_date is NULL) OR (region_last_update >= p_last_update_date) THEN
1522 -- delete mrp_region_sites completely.
1523 DELETE FROM MRP_REGION_SITES;
1524
1525 -- Get all vendor sites for re mapping.
1526 OPEN all_vendor_sites;
1527 FETCH all_vendor_sites BULK COLLECT
1528 INTO l_vendor_site_tab,
1529 l_country_tab,
1530 l_state_tab,
1531 l_city_tab,
1532 l_postal_tab,
1533 l_lang_tab;
1534 CLOSE all_vendor_sites;
1535 ELSE
1536 OPEN po_vendors_update (p_last_update_date);
1537 FETCH po_vendors_update BULK COLLECT
1538 INTO l_vendor_site_tab1;
1539 CLOSE po_vendors_update;
1540 -- bug 5985580
1541
1542 FOR j IN 1..l_vendor_site_tab1.COUNT LOOP
1543 BEGIN
1544 DELETE FROM MRP_REGION_SITES
1545 where vendor_site_id =l_vendor_site_tab1(j);
1546 EXCEPTION WHEN OTHERS THEN
1547 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1548 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in DELETEING Region to Sites');
1549 NULL;
1550 END;
1551 END LOOP;
1552 --
1553 -- If wsh_regions has not undergone any change after the last Collection, map only new vendor sites.
1554 OPEN new_vendor_sites(p_last_update_date);
1555 FETCH new_vendor_sites BULK COLLECT
1556 INTO l_vendor_site_tab,
1557 l_country_tab,
1558 l_state_tab,
1559 l_city_tab,
1560 l_postal_tab,
1561 l_lang_tab;
1562 CLOSE new_vendor_sites;
1563 END IF;
1564
1565 FOR i IN 1..l_vendor_site_tab.COUNT LOOP
1566 BEGIN
1567 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Vendor id passed to shippin API: '||l_vendor_site_tab(i) );
1568 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('State code passed to shippin API: '||l_state_tab(i) );
1569 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Language code passed to shippin API: '||l_lang_tab(i) );
1570 WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches(
1571 p_country => null, -- p_country
1572 p_country_region => null, -- p_country_region
1573 p_state => l_state_tab(i), -- p_state
1574 p_city => l_city_tab(i), -- p_city
1575 p_postal_code_from => l_postal_tab(i), -- p_postal_code_from
1576 p_postal_code_to => l_postal_tab(i), -- p_postal_code_to
1577 p_country_code => l_country_tab(i), -- p_country_code
1578 p_country_region_code => null, -- p_country_region_code
1579 p_state_code => null, -- p_state_code
1580 p_city_code => null, -- p_city_code
1581 p_lang_code => l_lang_tab(i), -- p_lang_code
1582 p_location_id => null, -- p_location_id
1583 p_zone_flag => 'Y', -- p_zone_flag
1584 x_status => l_status,
1585 x_regions => l_regions);
1586
1587 EXCEPTION WHEN OTHERS THEN
1588 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1589 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) );
1590
1591 NULL;
1592 END;
1593
1594 IF l_regions.count > 0 THEN
1595 l_region_id_tab.EXTEND(l_regions.count);
1596 l_region_type_tab.EXTEND(l_regions.count);
1597 l_zone_level_tab.EXTEND(l_regions.count);
1598
1599 FOR j IN 1..l_regions.COUNT LOOP
1600 l_region_id_tab(j) := l_regions(j).region_id;
1601 IF(l_regions(j).region_type = 10) THEN
1602 l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 1;
1603 ELSE
1604 l_region_type_tab(j) := (10 * (10 - l_regions(j).zone_level))+ 0;
1605 END IF;
1606 l_zone_level_tab(j) := l_regions(j).zone_level;
1607 END LOOP;
1608 FORALL k IN 1..l_regions.count
1609 INSERT INTO MRP_REGION_SITES(region_id,vendor_site_id, region_type, zone_level, last_update_date, last_updated_by, creation_date, created_by)
1610 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);
1611 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Val inserted into MRP_REGION_SITES for vendor_site_id: '|| l_vendor_site_tab(i) );
1612
1613 END IF;
1614
1615 END LOOP;
1616 COMMIT;
1617 Return(1);
1618
1619
1620 EXCEPTION WHEN OTHERS THEN
1621 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1622 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Mapping Region to Sites');
1623 Return(0);
1624 END MAP_REGION_TO_SITE ;
1625
1626 FUNCTION POPULATE_WO_SUB_COMP
1627 RETURN NUMBER
1628 IS
1629
1630
1631 TYPE CurTyp IS REF CURSOR;
1632
1633 ahl_wo_all CurTyp;
1634
1635 lv_sql_stmt VARCHAR2(500);
1636
1637 TYPE NumTblTyp IS TABLE OF NUMBER;
1638 l_wip_entity_id_tab1 NumTblTyp := NumTblTyp();
1639 l_wip_entity_id_tab NumTblTyp := NumTblTyp();
1640 wo_last_update DATE := NULL;
1641 L_ALT_ITEM_TBL AHL_MATERIALS_GRP.Alt_Item_Tbl_Type;
1642
1643 l_status VARCHAR2(10);
1644
1645 l_msg_count NUMBER;
1646 l_msg_data VARCHAR2 (5000);
1647 v_current_date DATE;
1648 v_current_user NUMBER;
1649 lv_trunc_stmt VARCHAR2(100);
1650 lv_mrp_schema VARCHAR2(30);
1651
1652
1653 BEGIN
1654
1655 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('In POPULATE_WO_SUB_COMP ');
1656
1657 SELECT SYSDATE,
1658 FND_GLOBAL.USER_ID
1659 INTO v_current_date,
1660 v_current_user
1661 FROM DUAL;
1662
1663 lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1664
1665 lv_trunc_stmt := ' TRUNCATE TABLE ' || lv_mrp_schema ||
1666 '.MRP_WO_SUB_COMP ';
1667 EXECUTE IMMEDIATE lv_trunc_stmt;
1668
1669 lv_sql_stmt :=
1670 'select WIP_ENTITY_ID, ORGANIZATION_ID ' ||
1671 ' from AHL_WORKORDERS_SN ' ||
1672 ' where ITEM_ALTERNATES_EXIST = ''Y''';
1673
1674 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Cursor lv_sql_stmt : '|| lv_sql_stmt );
1675
1676 -- Get all workorders for mapping.
1677
1678 OPEN ahl_wo_all FOR lv_sql_stmt ;
1679
1680 FETCH ahl_wo_all BULK COLLECT
1681 INTO l_wip_entity_id_tab, l_wip_entity_id_tab1;
1682 CLOSE ahl_wo_all;
1683
1684
1685 FOR i IN 1..l_wip_entity_id_tab.COUNT LOOP
1686 BEGIN
1687 L_ALT_ITEM_TBL.DELETE; -- need to delete the table L_ALT_ITEM_TBL
1688
1689 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Wip Entity ID Passed : '|| l_wip_entity_id_tab(i) );
1690 AHL_MATERIALS_GRP.GET_WO_ALTERNATES
1691 (P_API_VERSION => 1.0,
1692 p_init_msg_list => FND_API.G_FALSE,
1693 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1694 x_return_status => l_status,
1695 x_msg_count =>l_msg_count,
1696 x_msg_data => l_msg_data,
1697 P_WIP_ENTITY_ID => l_wip_entity_id_tab(i),
1698 X_ALT_ITEM_TBL => L_ALT_ITEM_TBL);
1699
1700 EXCEPTION WHEN OTHERS THEN
1701 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1702 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Get WO for WIP
1703 entity id '||l_wip_entity_id_tab(i));
1704
1705 NULL;
1706 END;
1707
1708 IF( L_ALT_ITEM_TBL.Count > 0 ) THEN
1709 FORALL k IN 1..L_ALT_ITEM_TBL.count
1710 INSERT INTO MRP_WO_SUB_COMP
1711 (WIP_ENTITY_ID,
1712 ORGANIZATION_ID,
1713 PRIMARY_COMPONENT_ID,
1714 ALTERNATE_COMPONENT_ID,
1715 OP_SEQ_NUM,
1716 RATIO,
1717 RANK,
1718 last_update_date,
1719 last_updated_by,
1720 creation_date,
1721 created_by)
1722 VALUES
1723 (l_wip_entity_id_tab(i),
1724 l_wip_entity_id_tab1(i),
1725 L_ALT_ITEM_TBL(k).PRIMARY_ITEM_ID,
1726 L_ALT_ITEM_TBL(k).ALTERNATE_ITEM_ID,
1727 L_ALT_ITEM_TBL(k).OPERATION_SEQUENCE,
1728 1,
1729 L_ALT_ITEM_TBL(k).PRIORITY,
1730 v_current_date,
1731 v_current_user,
1732 v_current_date,
1733 v_current_user);
1734 END IF;
1735
1736
1737 END LOOP;
1738 COMMIT;
1739 Return(1);
1740
1741
1742 EXCEPTION WHEN OTHERS THEN
1743 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG(SQLERRM);
1744 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('There was an error in Populating MRP_WO_SUB_COMP');
1745 Return(0);
1746 END POPULATE_WO_SUB_COMP ;
1747
1748 FUNCTION get_ship_set_name(p_SHIP_SET_ID in number)
1749 RETURN VARCHAR2
1750 IS
1751 l_set_name VARCHAR2(30);
1752 BEGIN
1753
1754 IF (p_SHIP_SET_ID is not null) THEN
1755 select set_name into l_set_name
1756 from oe_sets
1757 where set_id=p_SHIP_SET_ID;
1758
1759 RETURN l_set_name;
1760
1761 ELSE
1762
1763 l_set_name := null;
1764 RETURN l_set_name;
1765
1766 END IF;
1767
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 l_set_name := null;
1771 RETURN l_set_name;
1772
1773 END get_ship_set_name;
1774
1775 FUNCTION get_arrival_set_name(p_ARRIVAL_SET_ID in number)
1776 RETURN VARCHAR2
1777 IS
1778 l_set_name VARCHAR2(30);
1779 BEGIN
1780
1781 IF (p_ARRIVAL_SET_ID is not null) THEN
1782 select set_name into l_set_name
1783 from oe_sets
1784 where set_id=p_ARRIVAL_SET_ID;
1785
1786 RETURN l_set_name;
1787
1788 ELSE
1789
1790 l_set_name := null;
1791 RETURN l_set_name;
1792
1793 END IF;
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 l_set_name := null;
1798 RETURN l_set_name;
1799
1800 END get_arrival_set_name;
1801
1802 /* CMRO Collection Impact */
1803 FUNCTION GET_CMRO_CUSTOMER_ID
1804 RETURN NUMBER IS
1805 lv_customer_id NUMBER;
1806 BEGIN
1807
1808 /* Need to make sure that the AHL Customer Name is based of HZ Parties */
1809 /* I am settin other global variables in the function hoping and invoking
1810 * this function once for each row to avoid the hit 3 times?.
1811 * Should not we actually implement this in the Snap Shot?.
1812 */
1813
1814 Begin
1815 lv_customer_id := fnd_profile.value('AHL_APS_CUSTOMER_NAME');
1816 v_cmro_customer_id := lv_customer_id;
1817
1818 Exception
1819 When others then
1820 lv_customer_id := null;
1821 Return lv_customer_id ;
1822 End ;
1823
1824 RETURN lv_customer_id;
1825
1826 END GET_CMRO_CUSTOMER_ID;
1827
1828 FUNCTION GET_CMRO_SHIP_TO return NUMBER IS
1829 lv_customer_id NUMBER;
1830 lv_cmro_ship_to NUMBER;
1831 BEGIN
1832
1833 Begin
1834 lv_customer_id := fnd_profile.value('AHL_APS_CUSTOMER_NAME');
1835
1836 Exception
1837 When others then
1838 lv_customer_id := null;
1839 Return lv_customer_id ;
1840 End ;
1841
1842
1843 Begin
1844 SELECT
1845 SITE_USES_ALL.site_use_id SR_TP_SITE_ID
1846 INTO
1847 lv_cmro_ship_to
1848 FROM
1849 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
1850 HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
1851 HZ_CUST_ACCOUNTS CUST_ACCT,
1852 HR_ORGANIZATION_INFORMATION O,
1853 HR_ALL_ORGANIZATION_UNITS_TL OTL
1854 WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
1855 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
1856 AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
1857 AND OTL.LANGUAGE = userenv('LANG')
1858 AND SITE_USES_ALL.ORG_ID is NOT NULL
1859 AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
1860 AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
1861 AND CUST_ACCT.CUST_ACCOUNT_ID = lv_customer_id;
1862
1863 Exception
1864 When others then
1865 lv_cmro_ship_to := null ;
1866 End ;
1867
1868 RETURN lv_cmro_ship_to;
1869
1870
1871 END GET_CMRO_SHIP_TO ;
1872
1873
1874 FUNCTION GET_CMRO_BILL_TO return NUMBER IS
1875 lv_customer_id NUMBER;
1876 lv_cmro_bill_to NUMBER;
1877 BEGIN
1878
1879
1880 Begin
1881 lv_customer_id := fnd_profile.value('AHL_APS_CUSTOMER_NAME');
1882
1883 Exception
1884 When others then
1885 lv_customer_id := null;
1886 lv_cmro_bill_to := null;
1887 Return lv_cmro_bill_to ;
1888 End ;
1889
1890
1891 Begin
1892 SELECT
1893 SITE_USES_ALL.site_use_id SR_TP_SITE_ID
1894 INTO
1895 lv_cmro_bill_to
1896 FROM
1897 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
1898 HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
1899 HZ_CUST_ACCOUNTS CUST_ACCT,
1900 HR_ORGANIZATION_INFORMATION O,
1901 HR_ALL_ORGANIZATION_UNITS_TL OTL
1902 WHERE OTL.ORGANIZATION_ID = SITE_USES_ALL.ORG_ID
1903 AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
1904 AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
1905 AND OTL.LANGUAGE = userenv('LANG')
1906 AND SITE_USES_ALL.ORG_ID is NOT NULL
1907 AND SITE_USES_ALL.CUST_ACCT_SITE_ID=ACCT_SITE.CUST_ACCT_SITE_ID
1908 AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
1909 AND CUST_ACCT.CUST_ACCOUNT_ID = lv_customer_id;
1910
1911 Exception
1912 When others then
1913 lv_cmro_bill_to := null ;
1914 End ;
1915
1916 RETURN lv_cmro_bill_to;
1917
1918 END GET_CMRO_BILL_TO ;
1919
1920
1921 /* Check whether wsh version is above I. If yes return true else return false.*/
1922 FUNCTION CHECK_WSH_VER
1923 return NUMBER
1924 IS
1925 lv_patch_level VARCHAR2(100);
1926 lv_wsh_ver NUMBER;
1927 lv_family_pack VARCHAR2(10);
1928
1929 BEGIN
1930
1931 select nvl(fpi.patch_level, 'Not Available')
1932 into lv_patch_level
1933 from fnd_application_vl fav, fnd_product_installations fpi
1934 where fav.application_id = fpi.application_id and
1935 fpi.APPLICATION_ID in (665);
1936
1937 If lv_patch_level = 'Not Available' Then
1938 return G_WSH_LESS_THAN_J;
1939 End if;
1940
1941 lv_family_pack := SUBSTR(lv_patch_level,1,3);
1942
1943 IF lv_family_pack = '11i' Then
1944
1945 lv_wsh_ver := ASCII( SUBSTR(lv_patch_level,-1,1) );
1946
1947 if lv_wsh_ver > 73 Then
1948 return G_WSH_GREATER_THAN_EQUAL_J;
1949 else
1950 return G_WSH_LESS_THAN_J;
1951 End if;
1952
1953 ELSE
1954 return G_WSH_GREATER_THAN_EQUAL_J;
1955
1956 END IF;
1957
1958 EXCEPTION
1959 When Others Then return G_WSH_LESS_THAN_J;
1960
1961 END CHECK_WSH_VER;
1962
1963 Procedure SUBMIT_CR
1964 ( p_user_name IN VARCHAR2,
1965 p_resp_name IN VARCHAR2,
1966 p_application_name IN VARCHAR2,
1967 p_application_id IN NUMBER,
1968 p_batch_id IN NUMBER,
1969 p_conc_req_short_name IN varchar2 ,
1970 p_conc_req_desc IN varchar2 ,
1971 p_owning_applshort_name IN varchar2,
1972 p_load_type IN NUMBER,
1973 p_request_id IN OUT NOCOPY Number) IS
1974
1975 l_request number;
1976 l_result BOOLEAN;
1977
1978 l_user_id NUMBER;
1979 lv_log_msg varchar2(500);
1980
1981 BEGIN
1982
1983 BEGIN
1984
1985 SELECT USER_ID
1986 INTO l_user_id
1987 FROM FND_USER
1988 WHERE USER_NAME = p_user_name;
1989
1990 EXCEPTION
1991 WHEN NO_DATA_FOUND THEN
1992 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Error in launching the concurrent request : NO_USER_DEFINED');
1993 raise_application_error (-20001, 'NO_USER_DEFINED');
1994 END;
1995
1996 IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_RELEASE,lv_log_msg) THEN
1997 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_RELEASE,
1998 l_user_id,
1999 -1, --l_resp_id,
2000 -1 --l_application_id
2001 );
2002 ELSE
2003 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
2004 raise_application_error (-20001, lv_log_msg);
2005 END IF;
2006
2007
2008 --=============================================
2009
2010 l_result := fnd_request.set_mode(TRUE);
2011
2012 IF p_load_type >0 THEN
2013 l_request := FND_REQUEST.SUBMIT_REQUEST
2014 (p_owning_applshort_name,
2015 p_conc_req_short_name,
2016 p_conc_req_desc,
2017 null,
2018 FALSE,
2019 p_batch_id);
2020 ELSE
2021 l_request := FND_REQUEST.SUBMIT_REQUEST
2022 (p_owning_applshort_name,
2023 p_conc_req_short_name,
2024 p_conc_req_desc,
2025 null,
2026 FALSE);
2027 END IF ;
2028
2029 IF nvl( l_request,0) = 0 THEN
2030 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Error in launching the concurrent request ');
2031 ELSE
2032 p_request_id := l_request;
2033 MRP_CL_REFRESH_SNAPSHOT.LOG_DEBUG('Concurrent Request ID ' || p_request_id || ' has been submitted ');
2034 END IF;
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 RAISE;
2038
2039 END SUBMIT_CR;
2040
2041 FUNCTION validateUser (pUSERID IN NUMBER,
2042 pTASK IN NUMBER,
2043 pMESSAGE IN OUT NOCOPY varchar2)
2044 return BOOLEAN IS
2045 lv_result NUMBER := 0;
2046 BEGIN
2047 IF pTASK = MSC_UTIL.TASK_COLL THEN
2048
2049 select 1
2050 into lv_result
2051 from fnd_responsibility resp
2052 , FND_USER_RESP_GROUPS user_resp
2053 where resp.responsibility_id = user_resp.responsibility_id
2054 and resp.application_id = user_resp.responsibility_application_id
2055 and resp.responsibility_key = 'APS_COLLECTIONS'
2056 and user_resp.user_id = pUSERID
2057 and rownum =1 ;
2058
2059 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
2060
2061 select 1
2062 into lv_result
2063 from fnd_responsibility resp,
2064 FND_USER_RESP_GROUPS user_resp
2065 where resp.responsibility_id = user_resp.responsibility_id
2066 and resp.application_id = user_resp.responsibility_application_id
2067 and resp.responsibility_key = 'APS_RELEASE'
2068 and user_resp.user_id = pUSERID
2069 and rownum =1 ;
2070
2071 END IF;
2072
2073 IF lv_result = 1 THEN
2074 RETURN TRUE;
2075 ELSIF lv_result = 0 THEN
2076 RETURN FALSE;
2077 END IF;
2078
2079 EXCEPTION
2080 WHEN NO_DATA_FOUND THEN
2081 IF pTASK = MSC_UTIL.TASK_COLL THEN
2082 pMESSAGE := 'User not defnied or not assigned to the responsibility - ''APS COLLECTIONS''';
2083 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
2084 pMESSAGE := 'User not defnied or not assigned to the responsibility - ''APS RELEASE''';
2085 END IF;
2086 Return FALSE;
2087 WHEN OTHERS THEN
2088 pMESSAGE := 'Unable to validate user because of unexpected error';
2089 RAISE;
2090 END;
2091
2092 Function IB_CONTRACTS
2093 (pREFRESH_TYPE in VARCHAR2,
2094 pSTART_DATE in DATE,
2095 pWINDOW IN NUMBER )
2096
2097 Return NUMBER
2098 Is
2099 lv_refresh_type VARCHAR2(1);
2100 --pwindow NUMBER;
2101 lv_request_id_ibuc NUMBER ;
2102 lv_purge_flag VARCHAR2(1);
2103 lv_start_date DATE;
2104 lv_sql_stmt varchar2(500);
2105
2106 BEGIN
2107
2108 If (pREFRESH_TYPE = 'C' OR pREFRESH_TYPE = 'P') then
2109 --pwindow := NVL(fnd_profile.value('MSC_COLL_TIME_WINDOW_IBUC_HISTORY') ,0);
2110 lv_start_date := TRUNC(SYSTIMESTAMP-(pWINDOW*30+5));
2111 lv_purge_flag := 'Y';
2112 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Value '|| pwindow );
2113 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Start Date of the History window calculated based on profile '||lv_start_date);
2114
2115 ELSIF pREFRESH_TYPE = 'I' THEN
2116 lv_start_date := TRUNC(pSTART_DATE);
2117 lv_purge_flag := 'N';
2118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Start Date of the History window for net change '||pSTART_DATE);
2119 END IF;
2120
2121
2122 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refresh type : '
2123 ||pREFRESH_TYPE||' Start date for CP - ' ||lv_start_date );
2124
2125 lv_request_id_ibuc := FND_REQUEST.SUBMIT_REQUEST(
2126 'MSC',
2127 'MSCAGGRIBUC',
2128 NULL,
2129 NULL,
2130 FALSE,
2131 lv_start_date,--to_char(lv_start_date,'DD-MON-RRRR'),
2132 lv_purge_flag
2133 );
2134 COMMIT;
2135 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '
2136 || lv_request_id_IBUC ||' Calculates the Install Base under Contracts
2137 for a given date range');
2138
2139
2140 RETURN lv_request_id_ibuc ;
2141
2142 END IB_CONTRACTS;
2143
2144 PROCEDURE GET_ZONE (ERRBUF OUT NOCOPY VARCHAR2,
2145 RETCODE OUT NOCOPY NUMBER,
2146 p_LOCATION_ID IN NUMBER,
2147 p_ZONE OUT NOCOPY VARCHAR2,
2148 p_REGION_ID OUT NOCOPY NUMBER)
2149
2150 IS
2151 lv_zone VARCHAR2(60);
2152 lv_region_id NUMBER;
2153 BEGIN
2154
2155 select zone,parent_region_id
2156 into p_zone,p_region_id
2157 from (
2158 select wrl.region_id, z.parent_region_id ,a.zone, wrl.location_id,l.location_id,
2159 wrl.region_type
2160 from
2161 wsh_region_locations wrl,
2162 wsh_zone_regions z,
2163 wsh_regions_v a ,
2164 HZ_PARTY_SITES l
2165 where wrl.region_id = z.region_id
2166 and z.parent_region_id = a.region_id
2167 and a.region_type = 10
2168 and wrl.location_id = l.location_id
2169 and l.party_site_id = p_location_id
2170 order by l.location_id,wrl.region_type desc, a.zone_level asc)
2171 where rownum =1 ;
2172
2173 exception
2174 when no_data_found then
2175 lv_zone := null;
2176 lv_region_id := null;
2177
2178 when others then
2179 ERRBUF := SQLERRM;
2180 RETCODE := 1;
2181
2182 END GET_ZONE;
2183
2184
2185 PROCEDURE POPULATE_IB_CONTRACTS(
2186 ERRBUF OUT NOCOPY VARCHAR2,
2187 RETCODE OUT NOCOPY NUMBER,
2188 pSTART_DATE IN DATE,
2189 pPURGE_FLAG IN VARCHAR2)
2190 IS
2191
2192
2193 /*--------------------------------get item instances ------------------*/
2194 cursor c10(orig_st_date DATE) is
2195 SELECT
2196 mib.Instance_id ITEM_INSTANCE_ID,
2197 mib.instance_number,
2198 mib.inventory_item_id ,
2199 mib.quantity QUANTITY,
2200 mib.install_date INSTALL_DATE,
2201 mib.active_end_date EXPIRATION_DATE,
2202 nvl(mib.install_location_id,mib.location_id) loc_id,
2203 nvl(mib.install_location_type_code,mib.location_type_code) LOC_TYPE_CODE
2204 FROM
2205 CSI_ITEM_INSTANCES mib
2206 WHERE
2207 mib.accounting_class_code ='CUST_PROD'
2208 and mib.instance_usage_code = 'OUT_OF_ENTERPRISE'
2209 and mib.install_date is not null
2210 and( mib.active_end_date is null or mib.active_end_date >=orig_st_date)
2211 and mib.owner_party_source_table = 'HZ_PARTIES'
2212 and nvl(mib.install_location_type_code,mib.location_type_code) = 'HZ_PARTY_SITES';
2213
2214
2215 lv_purge_flag VARCHAR2(1);
2216 x_return_status VARCHAR2(10);
2217 x_msg_count NUMBER(20);
2218 x_msg_data VARCHAR2(100);
2219 x_output_contracts CSI_JAVA_INTERFACE_PKG.csi_output_tbl_ib;
2220 orig_st_date DATE ;
2221 orig_end_date DATE ;
2222 v_curr_date DATE;
2223 i NUMBER;
2224 lv_sql_stmt varchar2(1000);
2225 lv_zone VARCHAR2(60);
2226 lv_region_id NUMBER;
2227 lv_errbuf varchar2(100);
2228 lv_retcode number;
2229 lv_period varchar2(30) ;
2230 lv_duration number;
2231 lv_grace_stmt VARCHAR2(1000);
2232 lv_grace_date date;
2233 prf_end_date date;
2234 lv_sql_stmt3 varchar2(3000) := null;
2235 TYPE numtbltype is TABLE OF NUMBER ;
2236 lb_inv_item_id numtbltype;
2237 lb_item_instance_id numtbltype;
2238 lb_location_id numtbltype;
2239 prev_lb_inv_item_id number ;
2240
2241 BEGIN
2242
2243 ERRBUF := MSC_UTIL.G_SUCCESS;
2244 orig_st_date := trunc(pSTART_DATE);
2245 lv_purge_flag := pPURGE_FLAG;
2246
2247 BEGIN
2248 prf_end_date := nvl(fnd_profile.value('MSC_FIXED_DATE_IBUC_HISTORY'),0) ;
2249 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Profile value for Fixed Date '||prf_end_date);
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Please enter the value of profile ' ||
2253 'MSC: Fixed Date for Install Base under Contracts History (DD-MON-YYYY) ' ||
2254 ' in the correct date format DD-MON-YYYY ' );
2255 END;
2256
2257 orig_end_date := trunc(nvl(prf_end_date,systimestamp));
2258
2259 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Start date of the Time Window '||orig_st_date);
2260 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'End date of the Time Window '||orig_end_date);
2261
2262 /* determine the list of contracts for each of the above item instance----- */
2263
2264 lv_sql_stmt := 'Truncate table ' || MSC_UTIL.G_MSC_SCHEMA || '.MSC_IB_CONTRACTS_TEMP';
2265 EXECUTE IMMEDIATE lv_sql_stmt;
2266 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Truncated table MSC_IB_CONTRACTS_TEMP');
2267
2268 for t in c10(orig_st_date)
2269 loop
2270 CSI_JAVA_INTERFACE_PKG.getContracts(t.item_instance_id,x_return_status,x_msg_count,x_msg_data,x_output_contracts);
2271
2272 If x_output_contracts.count >0 then
2273
2274 lv_zone := null;
2275 lv_region_id := null;
2276 GET_ZONE(lv_errbuf,lv_retcode,t.loc_id, lv_zone, lv_region_id);
2277
2278 for i in x_output_contracts.FIRST..x_output_contracts.LAST LOOP
2279
2280 /*Logic to calculate the grace period*/
2281
2282 IF x_output_contracts(i).date_terminated IS NULL AND x_output_contracts(i).service_end_date <= orig_end_date THEN
2283
2284 lv_grace_stmt := 'select GRACE_PERIOD,GRACE_DURATION from oks_k_headers_b where chr_id = ' || x_output_contracts(i).contract_id;
2285 EXECUTE IMMEDIATE lv_grace_stmt into lv_period,lv_duration ;
2286
2287
2288 IF lv_duration IS NOT NULL THEN
2289 lv_grace_date := okc_time_util_pub.get_enddate(x_output_contracts(i).service_end_date,lv_period,lv_duration)+1;
2290 x_output_contracts(i).service_end_date := lv_grace_date;
2291 END IF ;
2292
2293 END IF ;
2294 /* end Logic to calculate the grace period*/
2295
2296 IF ( (x_output_contracts(i).service_start_date <= orig_end_date)
2297 and ((nvl(x_output_contracts(i).date_terminated,x_output_contracts(i).service_end_date)) >= orig_st_date)
2298 AND x_output_contracts(i).STS_CODE <> 'ENTERED' ) THEN
2299
2300 insert into MSC_IB_CONTRACTS_TEMP(
2301 item_instance_id,
2302 inventory_item_id ,
2303 install_date,
2304 expiration_date,
2305 quantity,
2306 zone,
2307 region_id,
2308 location_id,
2309 contract_id,
2310 contract_number,
2311 contract_num_modifier,
2312 sts_code,
2313 service_line_id,
2314 service_name,
2315 service_start_date,
2316 service_end_date,
2317 coterm_date ,
2318 last_update_date,
2319 last_updated_by,
2320 creation_date,
2321 created_by
2322 )
2323 values (
2324 t.item_instance_id,
2325 t.inventory_item_id,
2326 trunc(t.install_date),
2327 t.expiration_date,
2328 t.quantity,
2329 lv_zone,
2330 lv_region_id,
2331 t.loc_id,
2332 x_output_contracts(i).contract_id,
2333 x_output_contracts(i).contract_number,
2334 x_output_contracts(i).contract_number_modifier,
2335 x_output_contracts(i).sts_code,
2336 x_output_contracts(i).service_line_id,
2337 SUBSTRB(x_output_contracts(i).service_name,1,240),
2338 trunc(x_output_contracts(i).service_start_date),
2339 x_output_contracts(i).service_end_date,
2340 x_output_contracts(i).date_terminated,
2341 systimestamp,
2342 -1,
2343 systimestamp,
2344 -1);
2345 END IF;
2346
2347
2348 END LOOP;/* Inner loop - for each contract*/
2349 commit;
2350 end if ;
2351
2352 END LOOP; /*Outer loop - Item instances*/
2353 /* ----------*/
2354
2355 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Completed inserting Data into MSC_IB_CONTRACTS_TEMP');
2356
2357 /* ADD the function based index*/
2358
2359 begin
2360 lv_sql_stmt := 'CREATE INDEX ' || MSC_UTIL.G_MSC_SCHEMA ||'.MSC_IB_CONTRACTS_TEMP_N1 ' ||
2361 'ON ' || MSC_UTIL.G_MSC_SCHEMA || '.MSC_IB_CONTRACTS_TEMP( INSTALL_DATE,
2362 SERVICE_START_DATE, NVL(COTERM_DATE,SERVICE_END_DATE), EXPIRATION_DATE )
2363 STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 )
2364 TABLESPACE APPS_TS_TX_DATA ';
2365
2366 EXECUTE IMMEDIATE lv_sql_stmt;
2367 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Index MSC_IB_CONTRACTS_TEMP_N1 on table MSC_IB_CONTRACTS_TEMP created ');
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2371 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2372 /* IF SQLCODE IN (-01418) THEN RAISE; END IF; */
2373 END;
2374 BEGIN
2375 /*Aggregate the quantity based on the item, date, quantity */
2376
2377 if lv_purge_flag = 'Y' then
2378 lv_sql_stmt := 'Truncate table '|| MSC_UTIL.G_MSC_SCHEMA || '.MSC_SR_ZN_AGGR_IBUC';
2379 EXECUTE IMMEDIATE lv_sql_stmt;
2380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Truncated table MSC_SR_ZN_AGGR_IBUC');
2381
2382 elsif lv_purge_flag = 'N' THEN
2383 /* In case of net-change coll, we are deleting the data of the start date
2384 alone, to avoid unique constraint, as data would have been collected in the
2385 Complete Refresh on the same day. */
2386 Delete from msc_sr_zn_aggr_ibuc where sample_date = orig_st_date;
2387 end if ;
2388
2389
2390 v_curr_date := orig_st_date;
2391 LOOP --for each day
2392 insert into msc_sr_zn_aggr_ibuc
2393 (sr_inventory_item_id,
2394 sample_date,
2395 zone,
2396 region_id,
2397 quantity,
2398 last_update_date,
2399 last_updated_by,
2400 creation_date,
2401 created_by)
2402 select
2403 inventory_item_id,
2404 v_curr_date,
2405 zone,
2406 region_id,
2407 sum(qty),
2408 systimestamp,
2409 -1,
2410 systimestamp,
2411 -1
2412 from ( select distinct item_instance_id, inventory_item_id, quantity qty,zone,region_id
2413 from msc_ib_contracts_temp mict
2414 where mict.install_date <= v_curr_date
2415 and nvl(mict.coterm_date,mict.service_end_date) >= v_curr_date
2416 and mict.service_start_date <=v_curr_date
2417 and nvl(mict.expiration_date,(SYSTIMESTAMP + 365000)) >= v_curr_date
2418 )
2419 group by inventory_item_id,zone,region_id;
2420
2421 v_curr_date := v_curr_date + 1;
2422
2423 IF v_curr_date > orig_end_date THEN
2424 exit;
2425 END IF;
2426 END LOOP;
2427
2428
2429 COMMIT;
2430
2431 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Completed inserting data into MSC_SR_ZN_AGGR_IBUC');
2432
2433 BEGIN
2434 lv_sql_stmt := 'DROP INDEX ' || MSC_UTIL.G_MSC_SCHEMA || '.MSC_IB_CONTRACTS_TEMP_N1';
2435 EXECUTE IMMEDIATE lv_sql_stmt;
2436 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Index MSC_IB_CONTRACTS_TEMP_N1 on table MSC_IB_CONTRACTS_TEMP Dropped ');
2437 EXCEPTION
2438 WHEN OTHERS THEN
2439 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2440 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2441 /*
2442 IF SQLCODE IN (-01418) THEN
2443 RAISE;
2444 END IF;
2445 */
2446 END;
2447
2448 BEGIN
2449 prev_lb_inv_item_id := -1;
2450 lv_sql_stmt3 :=
2451 'select inventory_item_id,item_instance_id,location_id from '
2452 || MSC_UTIL.G_MSC_SCHEMA ||'.MSC_IB_CONTRACTS_TEMP'
2453 || ' where zone is null group by inventory_item_id,item_instance_id,location_id order by inventory_item_id ' ;
2454
2455 EXECUTE IMMEDIATE lv_sql_stmt3 BULK COLLECT into lb_inv_item_id,lb_item_instance_id,lb_location_id ;
2456
2457 IF lb_inv_item_id.count >0 THEN
2458 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2459 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Following Item Instances do not have Location to Zone Mapping ');
2460
2461 for j in lb_inv_item_id.FIRST .. lb_inv_item_id.LAST
2462 Loop
2463 IF prev_lb_inv_item_id <> lb_inv_item_id(j) THEN
2464 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Inventory Item Id '||lb_inv_item_id(j));
2465 ELSE
2466 prev_lb_inv_item_id := lb_inv_item_id(j);
2467 END IF;
2468
2469 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Item Instance Id -- ' || lb_item_instance_id(j)
2470 || ' Location Id -- ' || lb_location_id(j) );
2471 END LOOP;
2472
2473 ELSE
2474 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2475 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Item Instances have Location to Zone Mapping ');
2476 END IF ;
2477
2478 /*IF lb_inv_item_id.COUNT >0 THEN
2479 RETCODE := MSC_UTIL.G_WARNING;
2480 END IF;*/
2481
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2485 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2486 END;
2487
2488 EXCEPTION
2489 WHEN OTHERS THEN
2490 ERRBUF := SQLERRM;
2491 END;
2492
2493 COMMIT;
2494
2495 End POPULATE_IB_CONTRACTS;
2496
2497 FUNCTION DOC_TEXT_LONG2CHAR ( pMEDIA_ID IN NUMBER)
2498 return varchar2
2499 IS
2500 lbuffer varchar2(32767);
2501 BEGIN
2502 select LONG_TEXT
2503 into lbuffer
2504 from FND_DOCUMENTS_LONG_TEXT
2505 where MEDIA_ID=pmedia_id;
2506
2507 return lbuffer;
2508 EXCEPTION
2509 WHEN OTHERS THEN RETURN null;
2510
2511 END DOC_TEXT_LONG2CHAR;
2512
2513 FUNCTION DOC_TEXT_LONG2CLOB (pMEDIA_ID IN NUMBER)
2514 return CLOB
2515 IS
2516 lcharbuffer varchar2(32767);
2517 lclob CLOB;
2518 BEGIN
2519 lcharbuffer := DOC_TEXT_LONG2CHAR(pMEDIA_ID);
2520 DBMS_LOB.CREATETEMPORARY(lclob, FALSE, 10);
2521 IF (lcharbuffer is null) THEN
2522 lclob := EMPTY_CLOB();
2523 ELSE
2524 lclob := lcharbuffer;
2525 END IF;
2526 return lclob;
2527
2528 EXCEPTION
2529 WHEN OTHERS THEN RETURN EMPTY_CLOB();
2530
2531 END DOC_TEXT_LONG2CLOB;
2532
2533 PROCEDURE msc_Initialize(pTASK IN NUMBER,
2534 pUSERID IN NUMBER,
2535 pRESPID IN NUMBER,
2536 pAPPLID IN NUMBER) IS
2537
2538 lv_resp_id NUMBER;
2539 lv_application_id NUMBER;
2540 PRAGMA AUTONOMOUS_TRANSACTION;
2541 BEGIN
2542 IF pTASK = MSC_UTIL.TASK_COLL THEN
2543
2544 select resp.responsibility_id, resp.application_id
2545 into lv_resp_id, lv_application_id
2546 from fnd_responsibility resp,
2547 FND_USER_RESP_GROUPS user_resp
2548 where resp.responsibility_id = user_resp.responsibility_id
2549 and resp.application_id = user_resp.responsibility_application_id
2550 and resp.responsibility_key = 'APS_COLLECTIONS'
2551 and user_resp.user_id = pUSERID
2552 and rownum =1 ;
2553
2554 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
2555
2556 select resp.responsibility_id, resp.application_id
2557 into lv_resp_id, lv_application_id
2558 from fnd_responsibility resp,
2559 FND_USER_RESP_GROUPS user_resp
2560 where resp.responsibility_id = user_resp.responsibility_id
2561 and resp.application_id = user_resp.responsibility_application_id
2562 and resp.responsibility_key = 'APS_RELEASE'
2563 and user_resp.user_id = pUSERID
2564 and rownum =1 ;
2565
2566 ELSIF pTASK = MSC_UTIL.TASK_USER_DEFINED THEN
2567
2568 lv_resp_id := pRESPID;
2569 lv_application_id := pAPPLID;
2570
2571 END IF;
2572
2573 FND_GLOBAL.APPS_INITIALIZE( pUSERID,
2574 lv_resp_id,
2575 lv_application_id);
2576 commit;
2577 EXCEPTION
2578 WHEN NO_DATA_FOUND THEN
2579 IF pTASK = MSC_UTIL.TASK_COLL THEN
2580 raise_application_error (-20001, 'User not defnied or not assigned to the responsibility - ''APS COLLECTIONS''');
2581 ELSIF pTASK = MSC_UTIL.TASK_RELEASE THEN
2582 raise_application_error (-20001, 'User not defnied or not assigned to the responsibility - ''APS RELEASE''');
2583 ELSE
2584 raise_application_error (-20001, 'User not defnied or not assigned to the required responsibility');
2585 END IF;
2586 WHEN OTHERS THEN
2587 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Unable to initialize user because of unexpected error');
2588 RAISE;
2589 END;
2590
2591
2592 /*Commented the function LONG_TEXT w.r.t bug 13628509
2593 /*****Adddition of function(LONG_TEXT) for bug 12359111 by ngaddamp starts here
2594
2595 FUNCTION LONG_TEXT RETURN NUMBER
2596 IS
2597
2598 lv_sql_stmt VARCHAR2(500);
2599 v_sql_stmt VARCHAR2(5000);
2600
2601 BEGIN
2602 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Inside Function Long_Text. ');
2603
2604 lv_sql_stmt := 'Truncate table ' || MSC_UTIL.G_MSC_SCHEMA || '.MSC_ST_LONG_TEXT';
2605 EXECUTE IMMEDIATE lv_sql_stmt;
2606
2607 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Truncated table MSC_ST_LONG_TEXT ');
2608
2609 v_sql_stmt:=
2610 ' INSERT INTO MSC_ST_LONG_TEXT '
2611 ||'( MEDIA_ID,'
2612 ||' LONG_TEXT,'
2613 ||' SR_INSTANCE_ID,'
2614 ||' REFRESH_ID,'
2615 ||' LAST_UPDATE_DATE,'
2616 ||' LAST_UPDATED_BY,'
2617 ||' CREATION_DATE,'
2618 ||' CREATED_BY,'
2619 ||' LAST_UPDATE_LOGIN)'
2620 ||' SELECT'
2621 ||' MLT.MEDIA_ID,'
2622 ||' TO_LOB(MLT.LONG_TEXT),'
2623 ||' -999,'
2624 ||' -99,'
2625 ||' MLT.LAST_UPDATE_DATE,'
2626 ||' MLT.LAST_UPDATED_BY,'
2627 ||' MLT.CREATION_DATE,'
2628 ||' MLT.CREATED_BY,'
2629 ||' MLT.LAST_UPDATE_LOGIN'
2630 ||' FROM MSC_AP_LONG_TEXT_V MLT';
2631
2632 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'sql statement getting executed '||v_sql_stmt);
2633 EXECUTE IMMEDIATE v_sql_stmt;
2634 COMMIT;
2635
2636 Return 1;
2637 EXCEPTION WHEN OTHERS THEN
2638 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '========================================');
2639 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2640 RETURN 0;
2641
2642 END LONG_TEXT ;
2643
2644 /*****Addition of function(LONG_TEXT) for bug 12359111 by ngaddamp ends here
2645 Commented code w.r.t bug 13628509 ends here */
2646
2647 ------------------------------------------
2648 -- FUNCTION to get cmro WO operation codes--
2649 ------------------------------------------
2650 FUNCTION GET_CMRO_OP_CODE(p_wip_entity_id NUMBER,p_op_seq_num NUMBER ) return VARCHAR2 IS
2651 p_op_code VARCHAR2(200);
2652 BEGIN
2653 select op.concatenated_segments
2654 into p_op_code
2655 from ahl_operations_b_kfv op, ahl_workorders awo, ahl_workorder_operations awop
2656 where awo.workorder_id = awop.workorder_id
2657 and awop.operation_id = op.operation_id
2658 and awo.wip_entity_id = p_wip_entity_id
2659 and awop.operation_sequence_num = p_op_seq_num;
2660
2661 return p_op_code;
2662
2663 EXCEPTION WHEN OTHERS THEN
2664 return null;
2665 END GET_CMRO_OP_CODE;
2666
2667 ----------------------------------------------------------------------
2668 -- FUNCTION to get cmro/eam operation actual start and actual end date--
2669 -- This function will return the actual start date concatenated with
2670 -- actual end date with a separator '#' in between them.
2671 ----------------------------------------------------------------------
2672 FUNCTION GET_WO_ACTUAL_DATES(p_wip_entity_id NUMBER,p_op_seq_num NUMBER,p_maint_obj_src NUMBER )return VARCHAR2 IS
2673 p_actual_dates VARCHAR2(100);
2674 BEGIN
2675
2676 IF p_maint_obj_src = 1 THEN
2677
2678 select to_char(actual_start_date,'DD-MON-YYYY')||'#'||to_char(actual_end_date,'DD-MON-YYYY')
2679 into p_actual_dates
2680 from ( select actual_start_date,actual_end_date
2681 from eam_op_completion_txns
2682 where wip_entity_id = p_wip_entity_id
2683 and operation_seq_num = p_op_seq_num
2684 and transaction_type=1
2685 order by transaction_id desc) where rownum=1;
2686
2687 ELSIF p_maint_obj_src = 2 THEN
2688 select to_char(awop.actual_start_date,'DD-MON-YYYY')||'#'||to_char(awop.actual_end_date,'DD-MON-YYYY')
2689 into p_actual_dates
2690 from ahl_workorders aw, ahl_workorder_operations awop
2691 where aw.workorder_id = awop.workorder_id
2692 and aw.wip_entity_id = p_wip_entity_id
2693 and awop.operation_sequence_num = p_op_seq_num;
2694
2695 END IF;
2696 return p_actual_dates;
2697 EXCEPTION WHEN OTHERS THEN
2698 return null;
2699 END GET_WO_ACTUAL_DATES;
2700
2701 END MRP_CL_FUNCTION;
[Home] [Help]