1 PACKAGE BODY OE_EXPORT_COMPLIANCE_CONC AS
2 /* $Header: OEXCITMB.pls 120.5.12010000.2 2009/01/12 10:36:13 sahvivek ship $ */
3
4
5
6 /*-----------------------------------------------------+
7 | Name : Screening_Eligible |
8 | Parameters : IN p_line_id |
9 | |
10 | Description : This Procedure returns whether the |
11 | line is eligible for Screening. |
12 | The line is eligible for screening |
13 | if it has line status code as |
14 | EXPORT COMPLIANCE ELIGIBLE |
15 | (The line had a data error) |
16 +-----------------------------------------------------*/
17
18 FUNCTION Screening_Eligible(
19 p_line_id NUMBER
20 ) RETURN BOOLEAN IS
21
22 l_activity_status VARCHAR2(20);
23
24 BEGIN
25
26 -- Get Work Flow status for Line Id
27
28 SELECT WIAS.Activity_Status
29 INTO l_activity_status
30 FROM wf_item_activity_statuses WIAS,
31 wf_process_activities WPA
32 WHERE WIAS.Process_Activity = WPA.instance_id
33 AND WPA.activity_name = 'EXPORT_COMPLIANCE_ELIGIBLE'
34 AND WIAS.item_type = 'OEOL'
35 AND WIAS.item_key = to_char(p_line_id)
36 AND WIAS.activity_status = 'NOTIFIED' ;
37
38 RETURN TRUE;
39
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 RETURN FALSE;
43 WHEN OTHERS THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45
46 END Screening_Eligible;
47
48
49
50
51
52 /*-----------------------------------------------------+
53 | Name : Screening |
54 | Parameters : IN p_order_num_low |
55 | p_order_num_high |
56 | p_customer_name |
57 | p_customer_po_num |
58 | p_order_type |
59 | p_warehouse |
60 | p_ship_to_location |
61 | p_inventory_item_id |
62 | p_schedule_date_low |
63 | p_schedule_date_high |
64 | p_ordered_date_low |
65 | p_ordered_date_high |
66 | OUT NOCOPY ERRBUF |
67 | RETCODE |
68 | Description : This Procedure is called from |
69 | concurrent Program for Performing |
70 | Export Compliance Screening |
71 +-----------------------------------------------------*/
72
73
74 PROCEDURE Screening (
75 ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2
76 ,RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2
77 /* Moac */
78 ,p_org_id IN NUMBER
79 ,p_order_num_low IN NUMBER
80 ,p_order_num_high IN NUMBER
81 ,p_customer IN NUMBER
82 ,p_customer_po_num IN VARCHAR2
83 ,p_order_type IN NUMBER
84 ,p_warehouse IN NUMBER
85 ,p_ship_to_location IN NUMBER
86 ,p_inventory_item_id IN NUMBER
87 ,p_schedule_date_low IN VARCHAR2
88 ,p_schedule_date_high IN VARCHAR2
89 ,p_ordered_date_low IN VARCHAR2
90 ,p_ordered_date_high IN VARCHAR2
91 ) IS
92 /* bug 4632747
93 CURSOR C_GET_LINES (
94 cp_order_num_low NUMBER
95 ,cp_order_num_high NUMBER
96 ,cp_customer NUMBER
97 ,cp_customer_po_num VARCHAR2
98 ,cp_order_type NUMBER
99 ,cp_warehouse NUMBER
100 ,cp_ship_to_location NUMBER
101 ,cp_inventory_item_id NUMBER
102 ,cp_schedule_date_low DATE
103 ,cp_schedule_date_high DATE
104 ,cp_ordered_date_low DATE
105 ,cp_ordered_date_high DATE
106 )
107 IS
108 SELECT -- MOAC_SQL_CHANGE
109 L.line_id , L.org_id
110 FROM oe_order_lines L
111 ,oe_order_headers_all H
112 ,mtl_system_items MSI
113 WHERE L.header_id = H.header_id
114 AND L.inventory_item_id = MSI.inventory_item_id
115 AND L.ship_from_org_id = MSI.organization_id
116 AND H.order_number >= NVL(cp_order_num_low,
117 H.order_number)
118 AND H.order_number <= NVL(cp_order_num_high,
119 H.order_number)
120 AND NVL(H.sold_to_org_id,-99) = NVL(cp_customer,
121 NVL(H.sold_to_org_id,-99))
122 AND NVL(H.cust_po_number,-99) = NVL(cp_customer_po_num,
123 NVL(H.cust_po_number,-99))
124 AND H.order_type_id = NVL(cp_order_type,
125 H.order_type_id)
126 AND NVL(L.ship_from_org_id,-99) = NVL(cp_warehouse,
127 NVL(L.ship_from_org_id,-99))
128 AND NVL(L.ship_to_org_id,-99) = NVL(cp_ship_to_location,
129 NVL(L.ship_to_org_id,-99))
130 AND L.inventory_item_id = NVL(cp_inventory_item_id,
131 L.inventory_item_id)
132 AND L.schedule_ship_date >= NVL(cp_schedule_date_low,
133 L.schedule_ship_date)
134 AND L.schedule_ship_date <= NVL(cp_schedule_date_high,
135 L.schedule_ship_date)
136 AND H.ordered_date >= NVL(cp_ordered_date_low,
137 H.ordered_date)
138 AND H.ordered_date <= NVL(cp_ordered_date_high,
139 H.ordered_date)
140 AND H.open_flag = 'Y' --for 3631462
141 AND L.open_flag = 'Y' --for 3631462
142 ORDER BY H.org_id, H.header_id;
143
144 commented for bug 4632747 */
145 -- added for bug 4632747
146 l_sql_stmt VARCHAR2(20900);
147 l_sqlCursor INTEGER;
148 l_dummy NUMBER;
149
150 l_line_id NUMBER;
151 l_msg_count NUMBER;
152 l_msg_data VARCHAR2(2000) := NULL;
153 l_schedule_date_low DATE;
154 l_schedule_date_high DATE;
155 l_ordered_date_low DATE;
156 l_ordered_date_high DATE;
157
158 -- MOAC
159 l_single_org BOOLEAN := FALSE;
160 l_old_org_id NUMBER := -99;
161 l_org_id NUMBER;
162
163 BEGIN
164 --Initialze retcode #4220950
165 ERRBUF := '';
166 RETCODE := 0;
167
168 FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting EC Screening Program..');
169
170
171 FND_FILE.PUT_LINE(FND_FILE.LOG,'Program Parameters');
172 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------');
173
174 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_org_id:'||p_org_id);
175 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Num Low:'||p_order_num_low);
176 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Num High:'||p_order_num_high);
177 FND_FILE.PUT_LINE(FND_FILE.LOG,'Customer Id:'||p_customer);
178 FND_FILE.PUT_LINE(FND_FILE.LOG,'Customer PO Num:'||p_customer_po_num);
179 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Type:'||p_order_type);
180 FND_FILE.PUT_LINE(FND_FILE.LOG,'Warehouse:'||p_warehouse);
181 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ship To Location:'||p_ship_to_location);
182 FND_FILE.PUT_LINE(FND_FILE.LOG,'Item:'||p_inventory_item_id);
183 FND_FILE.PUT_LINE(FND_FILE.LOG,'Schedule Date Low:'||p_schedule_date_low);
184 FND_FILE.PUT_LINE(FND_FILE.LOG,'Schedule Date High:'||p_schedule_date_high);
185 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ordered Date Low:'||p_ordered_date_low);
186 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ordered Date High:'||p_ordered_date_high);
187
188 SELECT
189 FND_DATE.Canonical_To_Date(p_schedule_date_low),
190 FND_DATE.Canonical_To_Date(p_schedule_date_high),
191 FND_DATE.Canonical_To_Date(p_ordered_date_low),
192 FND_DATE.Canonical_To_Date(p_ordered_date_high)
193 INTO
194 l_schedule_date_low,
195 l_schedule_date_high,
196 l_ordered_date_low,
197 l_ordered_date_high
198 FROM DUAL;
199
200 -- MOAC Start
201 IF MO_GLOBAL.get_access_mode = 'S' THEN
202 l_single_org := TRUE;
203 ELSIF p_org_id IS NOT NULL THEN
204 l_single_org := TRUE;
205 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
206 END IF;
207 -- MOAC End
208
209 -- The cursor is being built based on the parameters passed, bug 4632747
210 l_sql_stmt := 'SELECT L.line_id, L.org_id '||
211 'FROM oe_order_lines L, oe_order_headers_all H, '||
212 'wf_item_activity_statuses WIAS, wf_process_activities WPA ' ||
213 'WHERE L.header_id = H.header_id '||
214 'AND WIAS.item_key = to_char(L.line_id) '||
215 'AND WIAS.Process_Activity = WPA.instance_id '||
216 'AND WPA.activity_name = ''EXPORT_COMPLIANCE_ELIGIBLE'' '||
217 'AND WIAS.item_type = ''OEOL'' ' ||
218 'AND WIAS.activity_status = ''NOTIFIED'' '||
219 'AND H.open_flag = ''Y'' ' ||
220 'AND L.open_flag = ''Y'' ';
221
222 IF p_order_num_low is not null then
223 l_sql_stmt := l_sql_stmt || ' AND H.ORDER_NUMBER >= :p1 ';
224 END IF;
225 IF p_order_num_high is not null then
226 l_sql_stmt := l_sql_stmt || ' AND H.ORDER_NUMBER <= :p2 ';
227 END IF;
228 IF p_customer is not null then
229 l_sql_stmt := l_sql_stmt || ' AND H.SOLD_TO_ORG_ID = :p3 ';
230 END IF;
231 IF p_customer_po_num is not null then
232 l_sql_stmt := l_sql_stmt || ' AND H.cust_po_number = :p4 ' ;
233 END IF;
234 IF p_order_type is not null then
235 l_sql_stmt := l_sql_stmt || ' AND H.order_type_id = :p5 ';
236 END IF;
237 IF p_warehouse is not null then
238 l_sql_stmt := l_sql_stmt || ' AND L.ship_from_org_id = :p6 ';
239 END IF;
240 IF p_ship_to_location is not null then
241 l_sql_stmt := l_sql_stmt || ' AND L.ship_to_org_id = :p7 ';
242 END IF;
243 IF p_inventory_item_id is not null then
244 l_sql_stmt := l_sql_stmt || ' AND L.inventory_item_id = :p8';
245 END IF;
246 IF l_schedule_date_low is not null then
247 l_sql_stmt := l_sql_stmt || ' AND L.schedule_ship_date >= :p9 ' ;
248 END IF;
249 IF l_schedule_date_high is not null then
250 l_sql_stmt := l_sql_stmt || ' AND L.schedule_ship_date <= :p10 ' ;
251 END IF;
252 IF l_ordered_date_low is not null then
253 l_sql_stmt := l_sql_stmt || ' AND H.ordered_date >= :p11 ';
254 END IF;
255 IF l_ordered_date_high is not null then
256 l_sql_stmt := l_sql_stmt || ' AND H.ordered_date <= :p12';
257 END IF;
258 IF p_org_id is NOT NULL THEN
259 l_sql_stmt := l_sql_stmt || ' AND L.org_id = :p13';
260 END IF;
261
262 l_sql_stmt := l_sql_stmt || ' ORDER BY H.header_id';
263 FND_FILE.PUT_LINE(FND_FILE.LOG,'Sql built = '|| l_sql_stmt);
264
265 l_sqlCursor := DBMS_SQL.Open_Cursor;
266
267 DBMS_SQL.PARSE(l_sqlCursor, l_sql_stmt, DBMS_SQL.NATIVE);
268
269 IF p_order_num_low IS NOT NULL THEN
270 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p1',p_order_num_low);
271 END IF;
272 IF p_order_num_high IS NOT NULL THEN
273 dbms_sql.bind_variable(l_sqlCursor,':p2',p_order_num_high);
274 END IF;
275 IF p_customer IS NOT NULL THEN
276 dbms_sql.bind_variable(l_sqlCursor,':p3',p_customer);
277 END IF;
278 IF p_customer_po_num IS NOT NULL THEN
279 dbms_sql.bind_variable(l_sqlCursor,':p4',p_customer_po_num);
280 END IF;
281 IF p_order_type IS NOT NULL THEN
282 dbms_sql.bind_variable(l_sqlCursor,':p5',p_order_type);
283 END IF;
284 IF p_warehouse IS NOT NULL THEN
285 dbms_sql.bind_variable(l_sqlCursor,':p6',p_warehouse);
286 END IF;
287 IF p_ship_to_location IS NOT NULL THEN
288 dbms_sql.bind_variable(l_sqlCursor,':p7',p_ship_to_location);
289 END IF;
290 IF p_inventory_item_id IS NOT NULL THEN
291 dbms_sql.bind_variable(l_sqlCursor,':p8',p_inventory_item_id);
292 END IF;
293 IF l_schedule_date_low IS NOT NULL THEN
294 dbms_sql.bind_variable(l_sqlCursor,':p9',l_schedule_date_low);
295 END IF;
296 IF l_schedule_date_high IS NOT NULL THEN
297 dbms_sql.bind_variable(l_sqlCursor,':p10',l_schedule_date_high);
298 END IF;
299 IF l_ordered_date_low IS NOT NULL THEN
300 dbms_sql.bind_variable(l_sqlCursor,':p11',l_ordered_date_low);
301 END IF;
302 IF l_ordered_date_high IS NOT NULL THEN
303 dbms_sql.bind_variable(l_sqlCursor,':p12',l_ordered_date_high);
304 END IF;
305 IF p_org_id IS NOT NULL THEN
306 dbms_sql.bind_variable(l_sqlCursor,':p13',p_org_id);
307 END IF;
308
309 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,1,l_line_id);
310 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,2,l_org_id);
311 l_dummy := DBMS_SQL.execute(l_sqlCursor);
312
313 LOOP
314
315 IF DBMS_SQL.FETCH_ROWS(l_sqlCursor) = 0 THEN
316 EXIT;
317 END IF;
318
319 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,1,l_line_id);
320 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,2,l_org_id);
321
322 /*
323 FOR c_lines IN C_GET_LINES(
324 p_order_num_low,
325 p_order_num_high,
326 p_customer,
327 p_customer_po_num,
328 p_order_type,
329 p_warehouse,
330 p_ship_to_location,
331 p_inventory_item_id,
332 l_schedule_date_low,
333 l_schedule_date_high,
334 l_ordered_date_low,
335 l_ordered_date_high
336 )
337 LOOP
338 l_line_id := c_lines.Line_Id;
339
340 IF Screening_Eligible(p_line_id => l_line_id) THEN
341 */
342 FND_FILE.PUT_LINE(FND_FILE.LOG,'Line:'||l_line_id||
343 ' is Eligible for Screening');
344
345 -- MOAC Start
346 -- l_org_id := c_lines.org_id; commented for bug 4632747
347 IF NOT l_single_org and l_org_id <> l_old_org_id THEN
348 l_old_org_id := l_org_id;
349 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
350 END IF;
351 -- MOAC End
352
353 WF_ENGINE.CompleteActivityInternalName(
354 itemtype => 'OEOL',
355 itemkey => to_char(l_line_id),
356 activity => 'EXPORT_COMPLIANCE_ELIGIBLE',
357 result => 'COMPLETE'); -- Bug 7688120
358
359 -- Write messages in to the log file
360
361 OE_MSG_PUB.Count_And_Get (
362 p_count => l_msg_count,
363 p_data => l_msg_data);
364
365 FOR I IN 1..l_msg_count
366 LOOP
367 l_msg_data := OE_MSG_PUB.Get(I,'F');
368 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
369 END LOOP;
370 -- END IF;
371 END LOOP;
372
373 DBMS_SQL.CLOSE_CURSOR(l_sqlCursor); -- bug 4632747
374
375 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exiting EC Screening Program..');
376
377
378 EXCEPTION
379 WHEN FND_API.G_EXC_ERROR THEN
380 FND_FILE.PUT_LINE(FND_FILE.LOG,'Expected Error in '||
381 'Export Compliance Screening Concurrent Program '||sqlerrm);
382
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected Error in '||
385 'Export Compliance Screening Concurrent Program '||sqlerrm);
386 END Screening;
387
388 END OE_EXPORT_COMPLIANCE_CONC;
[Home] [Help]