1 PACKAGE BODY OE_INC_ITEMS_FREEZE_CONC AS
2 /* $Header: OEXCFIIB.pls 120.5 2006/09/13 18:30:10 akurella noship $ */
3
4
5
6 /*-----------------------------------------------------+
7 | Name : Request |
8 | Parameters : IN p_order_num_low |
9 | p_order_num_high |
10 | p_inventory_item_id |
11 | p_schedule_date_low |
12 | p_schedule_date_high |
13 | p_num_of_days |
14 | p_ship_set_id |
15 | OUT ERRBUF |
16 | RETCODE |
17 | Description : This Procedure is called from |
18 | concurrent Program for progressing |
19 | all the lines that are eligible for |
20 | Freezing the included items |
21 | First select all the lines depending|
22 | upon the parameters and eligibility |
23 | and complete the activity. |
24 +-----------------------------------------------------*/
25
26
27 PROCEDURE Request
28 ( ERRBUF OUT NOCOPY VARCHAR2
29 ,RETCODE OUT NOCOPY VARCHAR2
30 ,p_org_id IN NUMBER
31 ,p_order_num_low IN NUMBER
32 ,p_order_num_high IN NUMBER
33 ,p_inventory_item_id IN NUMBER
34 ,p_schedule_date_low IN VARCHAR2
35 ,p_schedule_date_high IN VARCHAR2
36 ,p_num_of_days IN NUMBER
37 ,p_ship_set_id IN NUMBER
38 ) IS
39
40 l_line_id NUMBER;
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(2000) := NULL;
43 l_schedule_date_low DATE;
44 l_schedule_date_high DATE;
45 l_sql_stmt VARCHAR2(20900);
46 l_sqlCursor INTEGER;
47 l_dummy NUMBER;
48
49 -- Moac
50 l_single_org BOOLEAN := FALSE;
51 l_old_org_id NUMBER := -99;
52 l_org_id NUMBER;
53
54 BEGIN
55 --Initialze retcode #4220950
56 ERRBUF := '';
57 RETCODE := 0;
58
59 -- MOAC Start
60 IF MO_GLOBAL.Get_Access_Mode = 'S' THEN
61 l_single_org := TRUE;
62 ELSIF p_org_id IS NOT NULL THEN
63 l_single_org := TRUE;
64 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
65 END IF;
66
67 l_sql_stmt := 'SELECT L.line_id, L.org_id FROM oe_order_lines L' ||
68 ',oe_order_headers_all H ' ||
69 ',wf_item_activity_statuses WIAS' ||
70 ',wf_process_activities WPA' ||
71 ' WHERE l.open_flag = ''Y'' ' ||
72 ' AND WIAS.Process_Activity = WPA.instance_id' ||
73 ' AND WPA.activity_name = ''FREEZE_INCLUDED_ITEMS_ELIGIBLE''' ||
74 ' AND WIAS.item_type = ''OEOL''' ||
75 ' AND WPA.activity_item_type = ''OEOL''' ||
76 ' AND WIAS.item_key = l.line_id' ||
77 ' AND WIAS.activity_status = ''NOTIFIED''' ;
78
79
80 IF p_org_id is NOT NULL THEN
81 l_sql_stmt := l_sql_stmt || ' AND H.org_id = :bindvar_org_id';
82 END IF;
83 -- MOAC End
84
85 IF p_order_num_low is NOT NULL THEN
86 l_sql_stmt := l_sql_stmt || ' AND H.order_number >= :p1';
87 END IF;
88
89 IF p_order_num_high is NOT NULL THEN
90 l_sql_stmt := l_sql_stmt || ' AND H.order_number <= :p2';
91 END IF;
92
93 IF p_inventory_item_id is NOT NULL THEN
94 l_sql_stmt := l_sql_stmt || ' AND L.inventory_item_id = :p3';
95 END IF;
96
97 IF p_schedule_date_low is NOT NULL OR
98 p_num_of_days is NOT NULL THEN
99 l_sql_stmt := l_sql_stmt || ' AND L.schedule_ship_date >= :p4';
100 END IF;
101
102 IF p_schedule_date_high is NOT NULL OR
103 p_num_of_days is NOT NULL THEN
104 l_sql_stmt := l_sql_stmt || ' AND L.schedule_ship_date <= :p5';
105 END IF;
106
107 IF p_ship_set_id is NOT NULL THEN
108 l_sql_stmt := l_sql_stmt || ' AND L.ship_set_id = :p6';
109 END IF;
110
111 -- MOAC Start
112 If not l_single_org then
113 l_sql_stmt := l_sql_stmt || ' ORDER BY L.org_id, L.header_id';
114 else
115 l_sql_stmt := l_sql_stmt || ' ORDER BY L.header_id';
116 end if;
117 -- MOAC End
118
119 FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting Freeze Included Items Program..');
120
121 FND_FILE.PUT_LINE(FND_FILE.LOG,'Program Parameters');
122 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------');
123 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Num Low:'||p_order_num_low);
124 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Num High:'||p_order_num_high);
125 FND_FILE.PUT_LINE(FND_FILE.LOG,'Item:'||p_inventory_item_id);
126 FND_FILE.PUT_LINE(FND_FILE.LOG,'Schedule Date Low:'||p_schedule_date_low);
127 FND_FILE.PUT_LINE(FND_FILE.LOG,'Schedule Date High:'||p_schedule_date_high);
128 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ship Set:'||p_ship_set_id);
129 FND_FILE.PUT_LINE(FND_FILE.LOG,'Num of Days:'||p_num_of_days);
130
131 SELECT
132 FND_DATE.Canonical_To_Date(p_schedule_date_low),
133 FND_DATE.Canonical_To_Date(p_schedule_date_high)
134 INTO
135 l_schedule_date_low,
136 l_schedule_date_high
137 FROM DUAL;
138
139 l_sqlCursor := DBMS_SQL.Open_Cursor;
140
141 DBMS_SQL.PARSE(l_sqlCursor, l_sql_stmt, DBMS_SQL.NATIVE);
142
143 -- Moac Start
144 IF p_org_id IS NOT NULL THEN
145 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':bindvar_org_id',p_org_id);
146 END IF;
147 -- Moac End
148
149 IF p_order_num_low IS NOT NULL THEN
150 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p1',p_order_num_low);
151 END IF;
152
153 IF p_order_num_high IS NOT NULL THEN
154 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p2',p_order_num_high);
155 END IF;
156
157 IF p_inventory_item_id is NOT NULL THEN
158 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p3',p_inventory_item_id);
159 END IF;
160
161 IF p_schedule_date_low is NOT NULL THEN
162 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p4',l_schedule_date_low);
163 END IF;
164
165 IF p_schedule_date_high is NOT NULL THEN
166 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p5',l_schedule_date_high);
167 END IF;
168
169 IF p_ship_set_id is NOT NULL THEN
170 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p6',p_ship_set_id);
171 END IF;
172
173 IF (p_schedule_date_low is NULL AND
174 p_schedule_date_high is NULL) AND
175 p_num_of_days is NOT NULL THEN
176 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p4',sysdate);
177 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p5',sysdate+p_num_of_days);
178 END IF;
179
180 Oe_debug_pub.add('Sql Stmt: ' || l_sql_stmt,1);
181 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,1,l_line_id);
182 -- Moac
183 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,2,l_org_id);
184
185 l_dummy := DBMS_SQL.execute(l_sqlCursor);
186
187 Oe_debug_pub.add('After Executing the Cusrsor',1);
188 LOOP
189
190 IF DBMS_SQL.FETCH_ROWS(l_sqlCursor) = 0 THEN
191 EXIT;
192 END IF;
193
194 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,1,l_line_id);
195 -- Moac
196 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,2,l_org_id);
197
198 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Line:'||l_line_id);
199
200 -- MOAC Start
201 IF not l_single_org and l_org_id <> l_old_org_id THEN
202 l_old_org_id := l_org_id;
203 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
204 END IF;
205 -- MOAC End
206
207 Oe_debug_pub.add('Processing Line : ' || to_char(l_line_id),1);
208
209 WF_ENGINE.CompleteActivityInternalName(
210 itemtype => 'OEOL',
211 itemkey => to_char(l_line_id),
212 activity => 'FREEZE_INCLUDED_ITEMS_ELIGIBLE',
213 result => 'COMPLETED');
214
215 -- Write messages in to the log file
216
217 OE_MSG_PUB.Count_And_Get (
218 p_count => l_msg_count,
219 p_data => l_msg_data);
220
221 FOR I IN 1..l_msg_count
222 LOOP
223 l_msg_data := OE_MSG_PUB.Get(I,'F');
224 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
225 END LOOP;
226 END LOOP;
227
228 DBMS_SQL.CLOSE_CURSOR(l_sqlCursor);
229
230 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exiting Freeze Included Items Program:..');
231
232
233 EXCEPTION
234 WHEN FND_API.G_EXC_ERROR THEN
235 FND_FILE.PUT_LINE(FND_FILE.LOG,'Expected Error in '||
236 'Freeze Included Items Concurrent Program '||sqlerrm);
237
238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
239 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected Error in '||
240 'Freeze Included Items Concurrent Program '||sqlerrm);
241 END Request;
242
243 END OE_INC_ITEMS_FREEZE_CONC;
[Home] [Help]