1 PACKAGE BODY OE_PUR_CONC_REQUESTS AS
2 /* $Header: OEXCDSPB.pls 120.6.12010000.2 2008/11/26 00:08:03 shrgupta ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_PUR_CONC_REQUESTS';
7
8 /*-----------------------------------------------------------------
9 FUNCTION : Line_Eligible
10 DESCRIPTION: Check if the line is eligible for purchase release.
11 -----------------------------------------------------------------*/
12
13 Function Line_Eligible(p_line_id IN NUMBER)
14 RETURN BOOLEAN
15 IS
16 l_activity_status_code VARCHAR2(8);
17 BEGIN
18
19 -- Check for workflow status to be Purchase Release Eligible
20
21 SELECT wias.ACTIVITY_STATUS
22 INTO l_activity_status_code
23 FROM wf_item_activity_statuses wias, wf_process_activities wpa
24 WHERE wias.process_activity = wpa.instance_id
25 AND wpa.activity_name = 'PURCHASE RELEASE ELIGIBLE'
26 AND wias.item_type = 'OEOL'
27 AND wias.item_key = to_char(p_line_id)
28 AND wias.activity_status = 'NOTIFIED';
29
30 RETURN TRUE;
31
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 RETURN FALSE;
35 WHEN OTHERS THEN
36 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
37 END Line_Eligible;
38
39
40 /*-----------------------------------------------------------------
41 PROCEDURE : Request
42 DESCRIPTION: Purchase Release Concurrent Request
43 -----------------------------------------------------------------*/
44
45 Procedure Request
46 (ERRBUF OUT NOCOPY VARCHAR2,
47
48 RETCODE OUT NOCOPY VARCHAR2,
49 /* Moac */
50 p_org_id IN NUMBER,
51 p_order_number_low IN NUMBER,
52 p_order_number_high IN NUMBER,
53 p_request_date_low IN VARCHAR2,
54 p_request_date_high IN VARCHAR2,
55 p_customer_po_number IN VARCHAR2,
56 p_ship_to_location IN VARCHAR2,
57 p_order_type IN VARCHAR2,
58 p_customer IN VARCHAR2,
59 p_item IN VARCHAR2
60 )
61 IS
62 l_return_status VARCHAR2(1);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000) := NULL;
65
66 v_line_id NUMBER;
67 l_sql_stmt VARCHAR2(20900);
68 l_sqlCursor INTEGER;
69 l_dummy NUMBER;
70 --bug#5081428: introducing 1 local variable
71 l_count number := 1;
72 --Bug2295434 Introduced 2 Local Varibles given below.
73 l_request_date_low DATE;
74 l_request_date_high DATE;
75
76 -- MOAC
77 l_single_org BOOLEAN := FALSE;
78 l_old_org_id NUMBER := -99;
79 l_org_id NUMBER;
80 l_activity_status VARCHAR2(50);
81 l_activity_result VARCHAR2(50);
82
83 BEGIN
84 --Initialze retcode #4220950
85 ERRBUF := '';
86 RETCODE := 0;
87
88 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start of the program ');
89
90 -- Moac Start
91 IF MO_GLOBAL.get_access_mode = 'S' THEN
92 l_single_org := TRUE;
93 ELSIF p_org_id IS NOT NULL THEN
94 l_single_org := TRUE;
95 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
96 END IF;
97 -- Moac End
98
99 SELECT fnd_date.canonical_to_date(p_request_date_low),
100 fnd_date.canonical_to_date(p_request_date_high)
101 INTO l_request_date_low,
102 l_request_date_high
103 FROM DUAL;
104
105 --MOAC start
106 /* l_sql_stmt := ' SELECT SL.LINE_ID, SL.ORG_ID '||
107 ' FROM MTL_SYSTEM_ITEMS MSI, OE_ORDER_LINES SL, OE_ORDER_HEADERS_ALL SH '||
108 ' WHERE SL.HEADER_ID = SH.HEADER_ID '||
109 ' AND SL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID '||
110 ' AND SL.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID '||
111 ' AND SL.SOURCE_TYPE_CODE = ''EXTERNAL'''; */
112
113 l_sql_stmt := ' SELECT SL.LINE_ID, SL.ORG_ID '||
114 ' FROM OE_ORDER_LINES SL, OE_ORDER_HEADERS_ALL SH '||
115 ' WHERE SL.HEADER_ID = SH.HEADER_ID '||
116 ' AND SL.SOURCE_TYPE_CODE = ''EXTERNAL''';
117
118 IF p_org_id is NOT NULL THEN
119 l_sql_stmt := l_sql_stmt || ' AND SH.ORG_ID = :bindvar_org_id ' ;
120 END IF;
121 -- Moac End
122
123 IF p_order_number_low IS NOT NULL THEN
124 l_sql_stmt := l_sql_stmt || ' AND SH.ORDER_NUMBER >= :p1 ';
125 END IF;
126 IF p_order_number_high IS NOT NULL THEN
127 l_sql_stmt := l_sql_stmt || ' AND SH.ORDER_NUMBER <= :p2 ';
128 END IF;
129 IF p_ship_to_location IS NOT NULL THEN
130 l_sql_stmt := l_sql_stmt || ' AND SL.SHIP_TO_ORG_ID = :p3 ';
131 END IF;
132 IF p_order_type IS NOT NULL THEN
133 l_sql_stmt := l_sql_stmt || ' AND SH.ORDER_TYPE_ID = :p4 ';
134 END IF;
135 IF p_customer IS NOT NULL THEN
136 l_sql_stmt := l_sql_stmt || ' AND SH.SOLD_TO_ORG_ID = :p5 ';
137 END IF;
138 IF l_request_date_low IS NOT NULL THEN
139 l_sql_stmt := l_sql_stmt || ' AND SH.REQUEST_DATE >= :p6 ';
140 END IF;
141 IF l_request_date_high IS NOT NULL THEN
142 l_sql_stmt := l_sql_stmt || ' AND SH.REQUEST_DATE <= :p7 ';
143 END IF;
144
145 -- Moac Start : Commented the below code
146 --IF p_item IS NOT NULL THEN
147 -- l_sql_stmt := l_sql_stmt || ' AND MSI.SEGMENT1 = :p8 ';
148 --END IF;
149 IF p_item IS NOT NULL THEN
150 l_sql_stmt := l_sql_stmt || ' AND SL.INVENTORY_ITEM_ID = :p8 ';
151 END IF;
152 -- Moac End
153
154 -- shewgupt
155 IF p_customer_po_number IS NOT NULL THEN
156 l_sql_stmt := l_sql_stmt || ' AND SH.cust_po_number = :p9 ';
157 END IF;
158
159 --bug3241701
160 --added the open_flag condition to l_sql_stmt
161 l_sql_stmt := l_sql_stmt || ' AND SH.OPEN_FLAG = ''Y''' ; /* Moac */
162 --bug3241701 ends
163
164 --bug7583417
165 l_sql_stmt := l_sql_stmt || ' AND SL.OPEN_FLAG = ''Y''';
166 --bug7583417 ends
167
168 -- Moac Start
169 IF p_org_id IS NOT NULL THEN
170 l_sql_stmt := l_sql_stmt || ' ORDER BY SH.ORG_ID, SH.HEADER_ID ';
171 ELSE
172 l_sql_stmt := l_sql_stmt || ' ORDER BY SH.HEADER_ID ';
173 END IF;
174 -- Moac End
175
176 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Purchase Release program with new set Parameters:');
177 FND_FILE.PUT_LINE(FND_FILE.LOG, ' p_org_id = '|| p_org_id);
178 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Order Number High = '|| p_order_number_low);
179 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Order Number Low = '|| p_order_number_high);
180 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Ship To Org ID = '|| p_ship_to_location);
181 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Order Type ID = '|| p_order_type);
182 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Customer ID = '|| p_customer);
183 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Request Date High = '|| to_char(l_request_date_high,'DD-MON-YYYY'));
184 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Request Date Low = '|| to_char(l_request_date_low,'DD-MON-YYYY'));
185 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Item = '|| p_item);
186 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Cust PO Number = '|| p_customer_po_number);
187
188 l_sqlCursor := DBMS_SQL.Open_Cursor;
189
190 DBMS_SQL.PARSE(l_sqlCursor, l_sql_stmt, DBMS_SQL.NATIVE);
191
192 -- Moac Start
193 IF p_org_id IS NOT NULL THEN
194 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':bindvar_org_id',p_org_id);
195 END IF;
196 -- Moac End
197
198 IF p_order_number_low IS NOT NULL THEN
199 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p1',p_order_number_low);
200 END IF;
201 IF p_order_number_high IS NOT NULL THEN
202 dbms_sql.bind_variable(l_sqlCursor,':p2',p_order_number_high);
203 END IF;
204 IF p_ship_to_location IS NOT NULL THEN
205 dbms_sql.bind_variable(l_sqlCursor,':p3',p_ship_to_location);
206 END IF;
207 IF p_order_type IS NOT NULL THEN
208 dbms_sql.bind_variable(l_sqlCursor,':p4',p_order_type);
209 END IF;
210 IF p_customer IS NOT NULL THEN
211 dbms_sql.bind_variable(l_sqlCursor,':p5',p_customer);
212 END IF;
213 IF l_request_date_low IS NOT NULL THEN
214 dbms_sql.bind_variable(l_sqlCursor,':p6',l_request_date_low);
215 END IF;
216 IF l_request_date_high IS NOT NULL THEN
217 dbms_sql.bind_variable(l_sqlCursor,':p7',l_request_date_high);
218 END IF;
219
220 -- Moac Start.
221 IF p_item IS NOT NULL THEN
222 dbms_sql.bind_variable(l_sqlCursor,':p8',p_item);
223 END IF;
224
225 IF p_customer_po_number IS NOT NULL THEN
226 dbms_sql.bind_variable(l_sqlCursor,':p9',p_customer_po_number);
227 END IF;
228 -- Moac End.
229
230 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,1,v_line_id);
231 -- Moac
232 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,2,l_org_id);
233
234
235 l_dummy := DBMS_SQL.execute(l_sqlCursor);
236
237 LOOP
238
239 IF DBMS_SQL.FETCH_ROWS(l_sqlCursor) = 0 THEN
240 EXIT;
241 END IF;
242
243 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,1,v_line_id);
244 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,2,l_org_id);
245
246 IF Line_Eligible(p_line_id => v_line_id) THEN
247
248 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Processing Line ID => '||v_line_id);
249
250 -- Moac Start
251 IF NOT l_single_org and l_org_id <> l_old_org_id THEN
252 l_old_org_id := l_org_id;
253 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
254 END IF;
255 -- Moac End.
256
257 WF_ENGINE.CompleteActivityInternalName
258 (itemtype => 'OEOL',
259 itemkey => to_char(v_line_id),
260 activity => 'PURCHASE RELEASE ELIGIBLE',
261 result => 'COMPLETE');
262
263 -- #5873209, to set the concurrent program's completion status, check if
264 -- the order line was purchase release complete
265
266 BEGIN
267
268 SELECT wias.ACTIVITY_STATUS, wias.activity_result_code
269 INTO l_activity_status, l_activity_result
270 FROM wf_item_activity_statuses wias, wf_process_activities wpa
271 WHERE wias.process_activity = wpa.instance_id
272 AND wpa.activity_name = 'PUR_REL_THE_LINE'
273 AND wias.item_type = 'OEOL'
274 AND wias.item_key = to_char(v_line_id);
275
276 EXCEPTION WHEN OTHERS THEN
277 NULL;
278 END;
279
280 FND_FILE.PUT_LINE (FND_FILE.LOG,'activity result '||l_activity_result);
281
282 -- #5873209, if purchase release activity was not complete, then set the conc program status as warning
283 IF l_activity_result <> 'COMPLETE' THEN
284 errbuf := 'Could not complete the Purchase Release activity for Order Line ID '||v_line_id||', review the log for more details';
285 retcode := AD_CONC_UTILS_PKG.CONC_WARNING;
286 END IF;
287
288 /* Write Messages in the log file */
289
290 OE_MSG_PUB.Count_And_Get
291 ( p_count => l_msg_count
292 , p_data => l_msg_data
293 );
294
295 --bug#5081428:- printing only those mesgs which belongs to line_id
296 --under iteration. Earlier all the mesgs from 1st till last were
297 -- printed and causing log file to increase exponentially in size.
298 for I in l_count..l_msg_count loop
299 l_msg_data := OE_MSG_PUB.Get(I,'F');
300 fnd_file.put_line(FND_FILE.LOG, l_msg_data);
301 -- Write the message to the database?
302 end loop;
303 l_count := l_msg_count + 1;
304 --bug#5081428
305
306
307 END IF;
308
309 END LOOP;
310 DBMS_SQL.CLOSE_CURSOR(l_sqlCursor);
311
312
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 fnd_file.put_line(FND_FILE.LOG, 'Expected Error in Purchase Release Program'||sqlerrm);
316
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 fnd_file.put_line(FND_FILE.LOG, 'Unexpected Error in Purchase Release Program'||sqlerrm);
319 END Request;
320
321 END OE_PUR_CONC_REQUESTS;
[Home] [Help]