[Home] [Help]
PACKAGE BODY: APPS.OE_INV_IFACE_CONC
Source
1 PACKAGE BODY OE_INV_IFACE_CONC AS
2 /* $Header: OEXCIIFB.pls 120.4 2005/08/29 10:04:06 pkannan noship $ */
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 DATE,
57 p_request_date_high IN DATE,
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
68 j Integer;
69 -- variable for debugging.
70 l_file_val VARCHAR2(80);
71
72 CURSOR line_cur IS
73 SELECT H.org_id, H.header_id, L.line_id
74 FROM oe_order_headers H, oe_order_lines_all L
75 WHERE
76 H.org_id = NVL(p_org_id, H.org_id)
77 AND H.header_id = L.header_id
78 AND H.order_number >= NVL(p_order_number_low, H.order_number)
79 AND H.order_number <= NVL(p_order_number_high, H.order_number)
80 AND H.order_type_id = NVL(p_order_type, H.order_type_id)
81 AND nvl(H.sold_to_org_id, -1) = NVL(p_customer, nvl(H.sold_to_org_id, -1))
82 AND nvl(H.request_date, sysdate) >= NVL(p_request_date_low, nvl(H.request_date, sysdate))
83 AND nvl(H.request_date, sysdate) <= NVL(p_request_date_high, nvl(H.request_date, sysdate))
84 AND NVL(H.cust_po_number,-1) = NVL(p_customer_po_number, NVL(H.cust_po_number,-1))
85 AND H.open_flag = 'Y'
86 AND L.inventory_item_id = NVL(p_item, L.inventory_item_id)
87 AND NVL(L.ship_from_org_id, -1) = NVL(p_ship_from_org_id, NVL(L.ship_from_org_id, -1))
88 AND L.open_flag = 'Y'
89 ORDER BY H.org_id, H.header_id ;
90
91 /*
92 CURSOR line_cur(p_header_id IN NUMBER) IS
93 SELECT line_id, org_id
94 FROM oe_order_lines_all
95 WHERE header_id = p_header_id
96 AND inventory_item_id = NVL(p_item, inventory_item_id)
97 AND NVL(ship_from_org_id, -1) = NVL(p_ship_from_org_id, NVL(ship_from_org_id, -1))
98 AND open_flag = 'Y';
99 */
100
101 -- Moac : commented the below locking.
102 --FOR UPDATE NOWAIT;
103
104 --
105 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
106 --
107 -- Moac
108 l_single_org BOOLEAN := FALSE;
109 l_old_org_id NUMBER := -99;
110
111 BEGIN
112
113 /*
114 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
115 OE_DEBUG_PUB.Initialize;
116 OE_DEBUG_PUB.Debug_Off;
117 OE_DEBUG_PUB.Debug_On;
118 oe_Debug_pub.setdebuglevel(5);
119 */
120 fnd_file.put_line(FND_FILE.LOG, 'Debug File: ' || l_file_val);
121 fnd_file.put_line(FND_FILE.LOG, 'Parameters:');
122
123 fnd_file.put_line(FND_FILE.LOG, 'p_org_id = '|| p_org_id);
124
125 fnd_file.put_line(FND_FILE.LOG, ' order_number_low = '||
126 p_order_number_low);
127 fnd_file.put_line(FND_FILE.LOG, ' order_number_high = '||
128 p_order_number_high);
129 fnd_file.put_line(FND_FILE.LOG, ' order_type = '||
130 p_order_type);
131 fnd_file.put_line(FND_FILE.LOG, ' sold_to_ord = '||
132 p_customer);
133 fnd_file.put_line(FND_FILE.LOG, ' request_date_low = '||
134 p_request_date_low);
135 fnd_file.put_line(FND_FILE.LOG, ' request_date_high = '||
136 p_request_date_high);
137 fnd_file.put_line(FND_FILE.LOG, ' cust_po_number = '||
138 p_customer_po_number);
139 fnd_file.put_line(FND_FILE.LOG, ' item = '||
140 p_item);
141 fnd_file.put_line(FND_FILE.LOG, ' warehouse = '||
142 p_ship_from_org_id);
143 -- Moac Start
144 IF MO_GLOBAL.get_access_mode = 'S' THEN
145 l_single_org := TRUE;
146 ELSIF p_org_id IS NOT NULL THEN
147 l_single_org := TRUE;
148 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
149 END IF;
150 -- Moac End
151
152
153 SAVEPOINT lock_lines;
154
155 FOR j in line_cur LOOP
156 IF l_debug_level > 0 THEN
157 oe_debug_pub.add( 'INV IFACE CONC: LINE_ID - ' || TO_CHAR ( J.LINE_ID ) , 5 ) ;
158 END IF;
159 IF Line_Eligible(p_line_id => j.line_id) THEN
160 BEGIN
161
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'COMPLETING ACTIVITY FOR : ' || J.LINE_ID , 5 ) ;
164 END IF;
165
166 -- MOAC Start. Set policy context if the OU changes on lines.
167 IF NOT l_single_org and j.org_id <> l_old_org_id then
168 l_old_org_id := j.org_id;
169 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => j.org_id);
170 END IF;
171 -- MOAC End.
172
173 wf_engine.CompleteActivityInternalName
174 ('OEOL',
175 to_char(j.line_id),
176 'INVENTORY_INTERFACE_ELIGIBLE',
177 'COMPLETE');
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 null;
181 WHEN OTHERS THEN
182 null;
183 END;
184 END IF;
185
186 END LOOP;
187 COMMIT;
188
189
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 fnd_file.put_line(FND_FILE.LOG,
193 'Error executing Inventory Interface, Exception:G_EXC_ERROR');
194
195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
196 fnd_file.put_line(FND_FILE.LOG,
197 'Error executing Inventory Interface, Exception:G_EXC_UNEXPECTED_ERROR');
198 WHEN OTHERS THEN
199 fnd_file.put_line(FND_FILE.LOG,
200 'Error executing Inventory Interface');
201
202 END Request;
203
204 END OE_INV_IFACE_CONC;