1 PACKAGE BODY MRP_OE AS
2 /* $Header: MRPPNOEB.pls 120.0 2005/05/27 18:07:44 appldev noship $ */
3
4 FUNCTION mrp_quantity(p_demand_id IN NUMBER) RETURN NUMBER IS
5
6 CURSOR GET_MRP_QUANTITY IS
7 SELECT NVL(new_schedule_quantity,0)
8 FROM mrp_sales_order_updates u
9 WHERE u.sales_order_id = p_demand_id
10 AND u.process_status = 5;
11
12 l_quantity NUMBER;
13
14 BEGIN
15
16 OPEN GET_MRP_QUANTITY;
17
18 FETCH GET_MRP_QUANTITY INTO l_quantity;
19
20 CLOSE GET_MRP_QUANTITY;
21
22 RETURN l_quantity;
23
24 EXCEPTION
25
26 WHEN NO_DATA_FOUND THEN
27
28 RETURN 0;
29
30 END mrp_quantity;
31
32 FUNCTION mrp_date(p_demand_id IN NUMBER) RETURN DATE IS
33
34 CURSOR GET_MRP_DATE IS
35 SELECT new_schedule_date
36 FROM mrp_sales_order_updates u
37 WHERE u.sales_order_id = p_demand_id
38 AND u.process_status = 5;
39
40 l_date DATE;
41
42 BEGIN
43
44 OPEN GET_MRP_DATE;
45
46 FETCH GET_MRP_DATE INTO l_date;
47
48 CLOSE GET_MRP_DATE;
49
50 RETURN l_date;
51
52 EXCEPTION
53
54 WHEN NO_DATA_FOUND THEN
55
56 RETURN NULL;
57
58 END mrp_date;
59
60 FUNCTION available_to_mrp(p_demand_id IN NUMBER) RETURN NUMBER IS
61
62 CURSOR AVAIL IS
63 SELECT DECODE(visible_demand_flag,'Y',1,2)
64 FROM oe_order_lines_all oe
65 WHERE line_id = p_demand_id;
66
67 l_available_to_mrp NUMBER := 1;
68
69 BEGIN
70
71 OPEN AVAIL;
72 FETCH AVAIL into l_available_to_mrp;
73 CLOSE AVAIL;
74 RETURN l_available_to_mrp;
75
76 EXCEPTION
77
78 WHEN NO_DATA_FOUND THEN
79
80 RETURN 1;
81
82 END available_to_mrp;
83
84 FUNCTION updated_flag(p_demand_id IN NUMBER) RETURN NUMBER IS
85
86 CURSOR GET_UPDATED_FLAG IS
87 SELECT '2'
88 FROM mtl_demand_om_view demand
89 WHERE ((EXISTS
90 (SELECT NULL
91 FROM mrp_sales_order_updates updates
92 WHERE updates.sales_order_id = demand.demand_id
93 AND updates.old_schedule_date = demand.requirement_date
94 AND updates.old_schedule_quantity = demand.primary_uom_quantity
95 AND updates.previous_customer_id = demand.customer_id
96 AND updates.previous_ship_id = demand.ship_to_site_use_id
97 AND updates.previous_bill_id = demand.bill_to_site_use_id
98 AND NVL(updates.current_demand_class, 'A') =
99 NVL(demand.demand_class, 'A')
100 AND updates.process_status <> IN_PROCESS))
101 OR demand.demand_source_type NOT IN
102 (MTL_SALES_ORDER, MTL_INT_SALES_ORDER)
103 OR demand.customer_id IS NULL
104 OR demand.ship_to_site_use_id IS NULL
105 OR demand.bill_to_site_use_id IS NULL)
106 AND demand.demand_id = p_demand_id
107 AND demand.parent_demand_id IS NULL;
108
109 l_updated_flag NUMBER := 1;
110
111 BEGIN
112 /*
113 OPEN GET_UPDATED_FLAG;
114
115 FETCH GET_UPDATED_FLAG into l_updated_flag;
116
117 CLOSE GET_UPDATED_FLAG;
118 */
119 RETURN l_updated_flag;
120
121 EXCEPTION
122
123 WHEN NO_DATA_FOUND THEN
124
125 RETURN 1;
126
127 END updated_flag;
128
129 FUNCTION available_to_atp(p_demand_id IN NUMBER) RETURN NUMBER IS
130
131 CURSOR AVAIL IS
132 SELECT DECODE(visible_demand_flag,'Y',1,2)
133 FROM oe_order_lines_all oe
134 WHERE line_id = p_demand_id;
135
136 l_available_to_atp NUMBER := 1;
137
138 BEGIN
139
140 OPEN AVAIL;
141 FETCH AVAIL into l_available_to_atp;
142 CLOSE AVAIL;
143 RETURN l_available_to_atp;
144
145 EXCEPTION
146
147 WHEN NO_DATA_FOUND THEN
148
149 RETURN 1;
150
151 END available_to_atp;
152
153 FUNCTION total_reserv_qty (p_demand_id IN NUMBER) RETURN NUMBER IS
154 total_reservation_quantity NUMBER := 0;
155 BEGIN
156 SELECT SUM(NVL(MD1.PRIMARY_UOM_QUANTITY,0))
157 INTO total_reservation_quantity
158 FROM mtl_demand MD1
159 WHERE
160 MD1.DEMAND_SOURCE_LINE = to_char(p_demand_id)
161 AND NVL(MD1.DEMAND_SOURCE_TYPE,2) IN (2,8,12)
162 AND NVL(MD1.RESERVATION_TYPE,2) IN (2,3);
163
164 RETURN total_reservation_quantity;
165 EXCEPTION
166
167 WHEN NO_DATA_FOUND THEN
168
169 RETURN 0;
170
171 END total_reserv_qty;
172
173 END MRP_OE;
[Home] [Help]