[Home] [Help]
PACKAGE BODY: APPS.MRP_EXCEPTION_SC
Source
1 PACKAGE BODY MRP_EXCEPTION_SC AS
2 /* $Header: MRPXSUMB.pls 120.2 2006/02/20 21:21:11 avjain noship $ */
3
4 ---------------------------------------------------------------
5 -- insert into mrp_form_query the selected exception counts
6 ---------------------------------------------------------------
7 FUNCTION group_by(
8 var_exception_id NUMBER,
9 planning_org NUMBER, planned_org NUMBER,
10 count_list VARCHAR2, count_list_mfq VARCHAR2,
11 compile_designator VARCHAR2, where_clause_segment VARCHAR2, all_orgs NUMBER DEFAULT NULL)
12 RETURN NUMBER IS
13
14 c integer;
15 n number :=1;
16 statement varchar2(20000);
17 exception_id NUMBER;
18 rows_processed integer;
19
20 BEGIN
21
22
23 statement :=
24 ' INSERT INTO mrp_form_query' ||
25 '(query_id, ' ||
26 'last_update_date, ' ||
27 'last_updated_by, ' ||
28 'last_update_login, ' ||
29 'creation_date, ' ||
30 'created_by, ' ||
31 'char1, ' ||
32 count_list_mfq ||',' || 'number5, number13) ' ||
33 ' SELECT /*+ CHOOSE */ '||
34 ':var_exception_id ' ||
35 ', TRUNC(SYSDATE),' ||
36 '-1, -1,' ||
37 'TRUNC(SYSDATE),' ||
38 '-1, ' ||
39 'compile_designator, ' ||
40 count_list ||', ' ||
41 'sum(exception_count), display ' ||
42 ' FROM mrp_item_exception_v ' ||
43 ' WHERE compile_designator = :compile_designator' ||
44 ' and organization_id= decode( :planning_org ,:planned_org,organization_id,:planned_org) '||
45 ' and organization_id= decode(:all_orgs, :n '||
46 ', organization_id,:planned_org )'||
47 where_clause_segment ||
48 'GROUP BY compile_designator, display, ' ||
49 count_list;
50
51 /*c := dbms_sql.open_cursor;
52 dbms_sql.parse(c, statement, dbms_sql.native);
53 rows_processed := dbms_sql.execute(c);
54 dbms_sql.close_cursor(c);
55 */
56 --bug 5022710
57
58 execute immediate statement using
59 var_exception_id,compile_designator,planning_org,
60 planned_org,planned_org,all_orgs,n,planned_org;
61
62 SELECT mrp_form_query_s.NEXTVAL
63 INTO exception_id
64 FROM dual;
65
66 statement :=
67 ' INSERT INTO mrp_form_query' ||
68 '(query_id, ' ||
69 'last_update_date, ' ||
70 'last_updated_by, ' ||
71 'last_update_login, ' ||
72 'creation_date, ' ||
73 'created_by, ' ||
74 'char1, ' ||
75 'number1, ' ||
76 'number2, ' ||
77 'number3, ' ||
78 'number4, ' ||
79 'number6, ' ||
80 'number7, ' ||
81 'number8, ' ||
82 'number9, ' ||
83 'number10, ' ||
84 'number11, ' ||
85 'number12, ' ||
86 'number13, ' ||
87 'char2, ' ||
88 'char9, ' ||
89 'char4, ' ||
90 'char8 ) ' ||
91 ' SELECT /*+ CHOOSE*/ DISTINCT ' ||
92 ' :exception_id,' ||
93 ' TRUNC(SYSDATE),' ||
94 '-1, -1,' ||
95 'TRUNC(SYSDATE),' ||
96 '-1, ' ||
97 'compile_designator, ' ||
98 'version, ' ||
99 'exception_type, ' ||
100 'inventory_item_id, ' ||
101 'organization_id, ' ||
102 'project_id, ' ||
103 'task_id, ' ||
104 'category_id, ' ||
105 'department_id, ' ||
106 'resource_id, ' ||
107 'line_id, ' ||
108 'resource_type, ' ||
109 'display, ' ||
110 'planner_code, ' ||
111 'buyer_name, ' ||
112 'planning_group, ' ||
113 'row_id ' ||
114 ' FROM mrp_item_exception_v ' ||
115 ' WHERE compile_designator = :compile_designator' ||
116 ' and organization_id= decode(:planning_org,:planned_org ' ||
117 ', organization_id,:planned_org) '||
118 ' and organization_id= decode( :all_orgs '||
119 ', :n,organization_id,:planned_org ) '||
120 ' and version is null ' ||
121 where_clause_segment;
122
123 /*c := dbms_sql.open_cursor;
124 dbms_sql.parse(c, statement, dbms_sql.native);
125 rows_processed := dbms_sql.execute(c);
126 dbms_sql.close_cursor(c);
127 */
128 --5022710
129 execute immediate statement using
130 exception_id,compile_designator,planning_org,planned_org,planned_org,
131 all_orgs,n,planned_org;
132
133 return(exception_id);
134
135 EXCEPTION
136 when no_data_found
137 then null;
138 when others then
139 raise_application_error(-20000,sqlerrm||':'||statement);
140 END;
141
142 ---------------------------------------------------------------------
143 -- to save the current exception summary into mrp_item_exceptions
144 ---------------------------------------------------------------------
145 FUNCTION save_as(org_id number, plan VARCHAR2) RETURN NUMBER IS
146 version_id NUMBER;
147 BEGIN
148
149 SELECT MAX(NVL(version,0))+1
150 INTO version_id
151 FROM mrp_item_exceptions
152 WHERE compile_designator= plan;
153
154 INSERT INTO mrp_item_exceptions
155 (exception_type, inventory_item_id, compile_designator,
156 organization_id, last_update_date, last_updated_by,
157 creation_date, created_by, last_update_login, display, request_id,
158 program_application_id, program_id,
159 program_update_date, updated, status, exception_count, project_id,
160 task_id, version, planning_group,
161 department_id, resource_id, line_id)
162 SELECT exception_type, inventory_item_id, compile_designator,
163 organization_id, last_update_date, last_updated_by,
164 creation_date, created_by, last_update_login, display, request_id,
165 program_application_id, program_id,
166 program_update_date, updated, status, exception_count, project_Id,
167 task_id, version_id, planning_group,
168 department_id, resource_id, line_id
169 FROM mrp_item_exceptions
170 WHERE version is null
171 AND compile_designator= plan;
172 commit;
173 Return version_id;
174 END save_as;
175
176 ---------------------------------------------------------------------
177 -- update mrp-item-exceptions and mrp_form_query tables
178 ---------------------------------------------------------------------
179 PROCEDURE update_row(p_exception_id number,
180 p_omit_list VARCHAR2,
181 p_row_id VARCHAR2,
182 p_last_update_login NUMBER,
183 p_last_updated_by NUMBER) IS
184 c integer;
185 statement varchar2(2000);
186 rows_processed integer;
187 BEGIN
188
189 -- update mrp_item_exceptions by the their rowid (stored as char8 in
190 -- mrp_form_query) by applying the criteria on the second insert
191 -- of mrp_form_query
192
193 statement :=
194 'update mrp_item_exceptions
195 set display = 2,
196 last_update_login = :last_update_login,
197 last_updated_by = :last_updated_by,
198 last_update_date = sysdate
199 where rowid in (
200 select chartorowid(char8)
201 from mrp_form_query
202 where ' ||p_omit_list||
203 ' and query_id = :exception_id )';
204
205 c := dbms_sql.open_cursor;
206 dbms_sql.parse(c, statement, dbms_sql.native);
207 dbms_sql.bind_variable(c,'last_update_login',p_last_update_login);
208 dbms_sql.bind_variable(c,'last_updated_by',p_last_updated_by);
209 dbms_sql.bind_variable(c,'exception_id',p_exception_id);
210 rows_processed := dbms_sql.execute(c);
211 dbms_sql.close_cursor(c);
212
213 Exception
214 When no_data_found Then
215 raise no_data_found;
216
217 END update_row;
218 ---------------------------------------------------------------------
219 -- to lock the row in mrp_item_exceptions
220 -- if the row is deleted, return 1. if the data is changed, return 2.
221 -- if the row is locked successfully, return 3.
222 ---------------------------------------------------------------------
223 FUNCTION lock_row(p_exception_id number, p_omit_list VARCHAR2)
224 RETURN NUMBER IS
225 c integer;
226 statement varchar2(20000);
227 rows_processed integer;
228 display_flag NUMBER;
229 counter NUMBER;
230 BEGIN
231
232 -- lock the row in mrp_item_exceptions by the their rowid (stored as char8 in
233 -- mrp_form_query) by applying the criteria on the second insert
234 -- of mrp_form_query
235
236 statement :=
237 'select display
238 from mrp_item_exceptions
239 where rowid in (
240 select chartorowid(char8)
241 from mrp_form_query
242 where ' ||p_omit_list||
243 ' and query_id = :exception_id )'||
244 ' for update of display nowait ';
245
246 c := dbms_sql.open_cursor;
247 dbms_sql.parse(c, statement, dbms_sql.native);
248 dbms_sql.define_column(c, 1, display_flag);
249 dbms_sql.bind_variable(c,'exception_id',p_exception_id);
250 rows_processed := dbms_sql.execute(c);
251 counter :=0;
252
253 Loop
254 IF (dbms_sql.fetch_rows(c) >0) THEN
255 dbms_sql.column_value(c, 1, display_flag);
256 IF display_flag <> 1 THEN
257 -- record already changed
258 return 2;
259 END IF;
260 counter :=counter+1;
261 ELSE
262 exit;
263 END IF;
264 END Loop;
265 dbms_sql.close_cursor(c);
266 IF counter = 0 THEN
267 -- no matching records , record already deleted
268 return 1;
269 ELSE
270 -- with matching records, and the display flag is not changed yet
271 return 3;
272 END IF;
273
274
275 EXCEPTION
276 WHEN others THEN
277 --can not lock the record, because the record is locked by other users already
278 IF dbms_sql.is_open(c) THEN
279 dbms_sql.close_cursor(c);
280 END IF;
281 raise_application_error(-20020,sqlerrm||':'||statement);
282
283 END lock_row;
284
285 FUNCTION item_number(p_org_id number, p_inventory_item_id NUMBER) RETURN VARCHAR2
286 IS
287 v_item_number mtl_item_flexfields.item_number%type;
288 BEGIN
289 IF p_inventory_item_id IS NOT NULL AND p_org_id IS NOT NULL THEN
290
291 SELECT item_number INTO v_item_number
292 FROM mtl_item_flexfields
293 WHERE organization_id = p_org_id
294 AND inventory_item_id = p_inventory_item_id;
295
296 RETURN(v_item_number);
297 ELSE
298 RETURN(NULL);
299 END IF;
300 EXCEPTION WHEN NO_DATA_FOUND THEN
301 RETURN(null);
302 END item_number;
303
304
305 FUNCTION supplier(arg_supplier_id IN NUMBER) return varchar2 IS
306 supplier_name varchar2(240);
307 BEGIN
308
309 if arg_supplier_id is null then
310 return null;
311 end if;
312 select vendor_name
316 vendor_id = arg_supplier_id;
313 into supplier_name
314 from po_vendors
315 where
317
318 return supplier_name;
319
320 END supplier;
321
322
323 FUNCTION supplier_site(arg_supplier_site_id IN NUMBER) return varchar2 IS
324 supplier_site varchar2(240);
325 BEGIN
326
327 if arg_supplier_site_id is null then
328 return null;
329 end if;
330 select vendor_site_code
331 into supplier_site
332 from po_vendor_sites_all
333 where
334 vendor_site_id = arg_supplier_site_id;
335
336 return supplier_site;
337
338 END supplier_site;
339
340
341 END;