[Home] [Help]
PACKAGE BODY: APPS.OE_INV_IFACE_CONC
Source
1 PACKAGE BODY OE_INV_IFACE_CONC AS
2 /* $Header: OEXCIIFB.pls 120.5 2010/11/11 11:17:14 skurella ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_INV_IFACE_CONC';
7
8 Function Line_Eligible(p_line_id IN NUMBER)
9 RETURN BOOLEAN
10 IS
11 l_activity_status_code VARCHAR2(8);
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 --
15 BEGIN
16
17 -- Check for workflow status to be Inventory Interface Eligible
18 SELECT ACTIVITY_STATUS
19 INTO l_activity_status_code
20 FROM wf_item_activity_statuses wias, wf_process_activities wpa
21 WHERE wias.item_type = 'OEOL' AND
22 wias.item_key = to_char(p_line_id) AND
23 wias.process_activity = wpa.instance_id AND
24 wpa.activity_name = 'INVENTORY_INTERFACE_ELIGIBLE' AND
25 wias.activity_status = 'NOTIFIED';
26
27 RETURN TRUE;
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 IF l_debug_level > 0 THEN
32 oe_debug_pub.add( 'INV IFACE CONC: LINE DOES NOT HAVE NOTIFIED INV IFACE ELIGIBLE' , 5 ) ;
33 END IF;
34 RETURN FALSE;
35 WHEN OTHERS THEN
36 IF l_debug_level > 0 THEN
37 oe_debug_pub.add( 'INV IFACE CONC: OTHER EXCEPTION' , 5 ) ;
38 END IF;
39 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
40 RETURN FALSE;
41 END Line_Eligible;
42
43 /*-----------------------------------------------------------------
44 PROCEDURE : Request
45 DESCRIPTION: Inventory Interface Concurrent Request
46 -----------------------------------------------------------------*/
47
48 Procedure Request
49 (ERRBUF OUT NOCOPY VARCHAR2,
50
51 RETCODE OUT NOCOPY VARCHAR2,
52 /* Moac */
53 p_org_id IN NUMBER,
54 p_order_number_low IN NUMBER,
55 p_order_number_high IN NUMBER,
56 p_request_date_low IN VARCHAR2, -- Modified for Bug 10213020
57 p_request_date_high IN VARCHAR2, -- Modified for Bug 10213020
58 p_customer_po_number IN VARCHAR2,
59 p_ship_from_org_id IN VARCHAR2,
60 p_order_type IN VARCHAR2,
61 p_customer IN VARCHAR2,
62 p_item IN VARCHAR2
63 )IS
64
65 l_msg_count NUMBER;
66 l_msg_data VARCHAR2(2000) := NULL;
67 l_request_date_low DATE; -- Added for Bug 10213020
68 l_request_date_high DATE; -- Added for Bug 10213020
69 l_lin_id number; -- Added for Bug 10213020
70
71 j Integer;
72 -- variable for debugging.
73 l_file_val VARCHAR2(80);
74
75 CURSOR line_cur IS
76 SELECT H.org_id, H.header_id, L.line_id
77 FROM oe_order_headers H, oe_order_lines_all L
78 WHERE
79 H.org_id = NVL(p_org_id, H.org_id)
80 AND H.header_id = L.header_id
81 AND H.order_number >= NVL(p_order_number_low, H.order_number)
82 AND H.order_number <= NVL(p_order_number_high, H.order_number)
83 AND H.order_type_id = NVL(p_order_type, H.order_type_id)
84 AND nvl(H.sold_to_org_id, -1) = NVL(p_customer, nvl(H.sold_to_org_id, -1))
85 /* Begin Changes for Bug 10213020 */
86 -- AND nvl(H.request_date, sysdate) >= NVL(p_request_date_low, nvl(H.request_date, sysdate))
87 AND nvl(H.request_date, sysdate) >= NVL(l_request_date_low, nvl(H.request_date, sysdate))
88 -- AND nvl(H.request_date, sysdate) <= NVL(p_request_date_high, nvl(H.request_date, sysdate))
89 AND nvl(H.request_date, sysdate) <= NVL(l_request_date_high, nvl(H.request_date, sysdate))
90 /* End Changes for Bug 10213020 */
91 AND NVL(H.cust_po_number,-1) = NVL(p_customer_po_number, NVL(H.cust_po_number,-1))
92 AND H.open_flag = 'Y'
93 AND L.inventory_item_id = NVL(p_item, L.inventory_item_id)
94 AND NVL(L.ship_from_org_id, -1) = NVL(p_ship_from_org_id, NVL(L.ship_from_org_id, -1))
95 AND L.open_flag = 'Y'
96 ORDER BY H.org_id, H.header_id ;
97
98 /*
99 CURSOR line_cur(p_header_id IN NUMBER) IS
100 SELECT line_id, org_id
101 FROM oe_order_lines_all
102 WHERE header_id = p_header_id
103 AND inventory_item_id = NVL(p_item, inventory_item_id)
104 AND NVL(ship_from_org_id, -1) = NVL(p_ship_from_org_id, NVL(ship_from_org_id, -1))
105 AND open_flag = 'Y';
106 */
107
108 -- Moac : commented the below locking.
109 --FOR UPDATE NOWAIT;
110
111 CURSOR wf_item_inv IS -- /* Added for Bug 10213020 */
112 Select item_key
113 From wf_item_activity_statuses wias, wf_process_activities wpa
114 Where wias.item_type = 'OEOL'
115 And wias.process_activity = wpa.instance_id
116 And wpa.activity_name = 'INVENTORY_INTERFACE_ELIGIBLE'
117 And wias.activity_status = 'NOTIFIED';
118
119
120 --
121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
122 --
123 -- Moac
124 l_single_org BOOLEAN := FALSE;
125 l_old_org_id NUMBER := -99;
126
127 BEGIN
128
129 /*
130 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
131 OE_DEBUG_PUB.Initialize;
132 OE_DEBUG_PUB.Debug_Off;
133 OE_DEBUG_PUB.Debug_On;
134 oe_Debug_pub.setdebuglevel(5);
135 */
136 fnd_file.put_line(FND_FILE.LOG, 'Debug File: ' || l_file_val);
137 fnd_file.put_line(FND_FILE.LOG, 'Parameters:');
138
139 fnd_file.put_line(FND_FILE.LOG, 'p_org_id = '|| p_org_id);
140
141 fnd_file.put_line(FND_FILE.LOG, ' order_number_low = '||
142 p_order_number_low);
143 fnd_file.put_line(FND_FILE.LOG, ' order_number_high = '||
144 p_order_number_high);
145 fnd_file.put_line(FND_FILE.LOG, ' order_type = '||
146 p_order_type);
147 fnd_file.put_line(FND_FILE.LOG, ' sold_to_ord = '||
148 p_customer);
149 fnd_file.put_line(FND_FILE.LOG, ' request_date_low = '||
150 p_request_date_low);
151 fnd_file.put_line(FND_FILE.LOG, ' request_date_high = '||
152 p_request_date_high);
153 fnd_file.put_line(FND_FILE.LOG, ' cust_po_number = '||
154 p_customer_po_number);
155 fnd_file.put_line(FND_FILE.LOG, ' item = '||
156 p_item);
157 fnd_file.put_line(FND_FILE.LOG, ' warehouse = '||
158 p_ship_from_org_id);
159
160 l_request_date_low := fnd_date.canonical_to_date(p_request_date_low); -- Added for Bug 10213020
161 fnd_file.put_line(FND_FILE.LOG, ' l_request_date_low = '|| l_request_date_low); -- Added for Bug 10213020
162 l_request_date_high := fnd_date.canonical_to_date(p_request_date_high); -- Added for Bug 10213020
163 fnd_file.put_line(FND_FILE.LOG, ' l_request_date_high = '|| l_request_date_high); -- Added for Bug 10213020
164
165 -- Moac Start
166 IF MO_GLOBAL.get_access_mode = 'S' THEN
167 l_single_org := TRUE;
168 ELSIF p_org_id IS NOT NULL THEN
169 l_single_org := TRUE;
170 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
171 END IF;
172 -- Moac End
173
174 /* Begin Changes for Bug 10213020 */
175 -- If value is not passed through any of the parameters
176 IF p_order_number_low IS NULL AND
177 p_order_number_high IS NULL AND
178 p_request_date_low IS NULL AND
179 p_request_date_high IS NULL AND
180 p_customer_po_number IS NULL AND
181 p_ship_from_org_id IS NULL AND
182 p_order_type IS NULL AND
183 p_customer IS NULL AND
184 p_item IS NULL THEN
185
186 IF l_debug_level > 0 THEN
187 oe_debug_pub.add( 'INV IFACE CONC: - ' ) ;
188 END IF;
189 FOR i IN wf_item_inv LOOP
190
191 oe_debug_pub.add('##### Processing item key ' || i.item_key ||'##' , 5);
192
193 l_lin_id := TO_NUMBER(i.item_key) ;
194 IF l_debug_level > 0 THEN
195 oe_debug_pub.add( '##INV IFACE CONC: LINE_ID - ' || l_lin_id , 5 ) ;
196 END IF;
197
198 IF Line_Eligible(p_line_id => l_lin_id) THEN
199 BEGIN
200
201 IF l_debug_level > 0 THEN
202 oe_debug_pub.add( '##COMPLETING ACTIVITY FOR : ' || i.item_key , 5 ) ;
203 END IF;
204 wf_engine.CompleteActivityInternalName
205 ('OEOL',
206 i.item_key,
207 'INVENTORY_INTERFACE_ELIGIBLE',
208 'COMPLETE');
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 null;
212 WHEN OTHERS THEN
213 null;
214 END;
215 END IF;
216
217 END LOOP;
218
219 ELSE -- If some value is passed then derive based on the header_id
220
221 /* End Changes for Bug 10213020 */
222 SAVEPOINT lock_lines;
223
224 FOR j in line_cur LOOP
225 IF l_debug_level > 0 THEN
226 oe_debug_pub.add( 'INV IFACE CONC: LINE_ID - ' || TO_CHAR ( J.LINE_ID ) , 5 ) ;
227 END IF;
228 IF Line_Eligible(p_line_id => j.line_id) THEN
229 BEGIN
230
231 IF l_debug_level > 0 THEN
232 oe_debug_pub.add( 'COMPLETING ACTIVITY FOR : ' || J.LINE_ID , 5 ) ;
233 END IF;
234
235 -- MOAC Start. Set policy context if the OU changes on lines.
236 IF NOT l_single_org and j.org_id <> l_old_org_id then
237 l_old_org_id := j.org_id;
238 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => j.org_id);
239 END IF;
240 -- MOAC End.
241
242 wf_engine.CompleteActivityInternalName
243 ('OEOL',
244 to_char(j.line_id),
245 'INVENTORY_INTERFACE_ELIGIBLE',
246 'COMPLETE');
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 null;
250 WHEN OTHERS THEN
251 null;
252 END;
253 END IF;
254
255 END LOOP;
256 COMMIT;
257 END IF ; -- 'IF' for Bug 10213020
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 fnd_file.put_line(FND_FILE.LOG,
262 'Error executing Inventory Interface, Exception:G_EXC_ERROR');
263
264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
265 fnd_file.put_line(FND_FILE.LOG,
266 'Error executing Inventory Interface, Exception:G_EXC_UNEXPECTED_ERROR');
267 WHEN OTHERS THEN
268 fnd_file.put_line(FND_FILE.LOG,
269 'Error executing Inventory Interface');
270
271 END Request;
272
273 END OE_INV_IFACE_CONC;