1 PACKAGE BODY eam_material_validate_pvt AS
2 /* $Header: EAMVMSCB.pls 120.12 2010/11/13 01:50:57 mashah ship $ */
3
4
5 /***************************************************************************
6 --
7 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
8 -- All rights reserved.
9 --
10 -- FILENAME
11 --
12 -- EAMVMSCB.pls
13 --
14 -- DESCRIPTION
15 --
16 -- Body of package EAM_MATERIAL_VALIDATE_PVT
17 --
18 -- NOTES
19 --
20 -- HISTORY
21 --
22 -- 02-FEB-2005 Girish Rajan Initial Creation
23 ***************************************************************************/
24
25 /*******************************************************************
26 * Procedure : get_wip_entity_name
27 * Returns : wip_entity_name
28 * Parameters IN : Wip Entity Id
29 * Purpose : Local function to get the wip_entity_name corresponding to
30 * the wip_entity_id
31 *********************************************************************/
32
33 FUNCTION get_wip_entity_name(p_wip_entity_id NUMBER)
34 RETURN VARCHAR2
35 IS
36 CURSOR wip_entity_name_csr(p_wip_entity_id NUMBER) IS
37 SELECT wip_entity_name
38 FROM wip_entities
39 WHERE wip_entity_id = p_wip_entity_id;
40
41 l_wip_entity_name wip_entities.wip_entity_name%TYPE;
42 BEGIN
43 OPEN wip_entity_name_csr(p_wip_entity_id);
44 FETCH wip_entity_name_csr INTO l_wip_entity_name;
45 CLOSE wip_entity_name_csr;
46
47 RETURN l_wip_entity_name;
48 END get_wip_entity_name;
49
50
51 /*******************************************************************
52 * Procedure : Material_Shortage_CP
53 * Returns : None
54 * Parameters IN : Owning Department, Assigned Department, Asset_number,
55 * : Scheduled Start Date_from, Scheduled Start Date To,
56 * : Work Order From, Work Order To, Status Type, Horizon, Backlog Horizon,
57 * : Organization Id, Project, Task
58 * Parameters OUT NOCOPY: errbuf to show the erro fired by concurremt program
59 * retcode = 2 if error
60 * Purpose : For any given work order, this wrapper API will
61 * determine whether there is material shortage
62 * or not and then update that field at the work order
63 * level. API will return whether shortage
64 * exists in p_shortage_exists parameter.
65 *********************************************************************/
66
67 PROCEDURE Material_Shortage_CP
68 ( errbuf OUT NOCOPY VARCHAR2
69 , retcode OUT NOCOPY VARCHAR2
70 , p_owning_department IN VARCHAR2
71 , p_assigned_department IN NUMBER
72 , p_asset_number IN VARCHAR2
73 , p_scheduled_start_date_from IN VARCHAR2
74 , p_scheduled_start_date_to IN VARCHAR2
75 , p_work_order_from IN VARCHAR2
76 , p_work_order_to IN VARCHAR2
77 , p_status_type IN NUMBER
78 , p_horizon IN NUMBER
79 , p_backlog_horizon IN NUMBER
80 , p_organization_id IN NUMBER
81 , p_project IN VARCHAR2
82 , p_task IN VARCHAR2
83 )
84 IS
85 TYPE WipIdCurType IS REF CURSOR;
86
87 get_wip_entity_id_csr WipIdCurType;
88 TYPE wip_entity_id_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
89 wip_entity_id_tbl wip_entity_id_type;
90
91
92 l_api_version CONSTANT NUMBER:=1;
93 l_shortage_exists VARCHAR2(1);
94 l_return_status VARCHAR2(1);
95 l_msg_count NUMBER;
96 l_msg_data VARCHAR2(2000);
97 l_check_shortage_excep EXCEPTION;
98 l_sql_stmt VARCHAR2(4000);
99 l_where_clause VARCHAR2(4000);
100 l_rows NUMBER := 5000;
101 l_instance_id NUMBER;
102 l_encoding VARCHAR2(2000);
103 BEGIN
104 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
105 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="'||l_encoding ||'"?>' );
106 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ROWSET>');
107 l_sql_stmt := ' SELECT wdj.wip_entity_id
108 FROM wip_discrete_jobs wdj, eam_work_order_details ewod, wip_entities we, csi_item_instances cii
109 WHERE wdj.wip_entity_id = ewod.wip_entity_id
110 AND wdj.wip_entity_id=we.wip_entity_id
111 AND wdj.maintenance_object_id = cii.instance_id
112 AND wdj.maintenance_object_type = 3
113 AND wdj.organization_id = :p_org_id ';
114
115 IF p_work_order_from IS NOT NULL THEN
116 l_where_clause := l_where_clause || ' AND we.wip_entity_name >= '|| '''' || p_work_order_from ||'''';
117 IF p_work_order_to IS NOT NULL THEN
118 l_where_clause := l_where_clause || ' AND we.wip_entity_name <= '|| ''''|| p_work_order_to ||'''' ;
119 ELSE
120 l_where_clause := ' AND we.wip_entity_name = '|| p_work_order_from ;
121 END IF;
122 END IF;
123
124
125 IF p_asset_number IS NOT NULL THEN
126
127 BEGIN
128 --added bug 10192902
129 SELECT instance_id
130 INTO l_instance_id
131 FROM csi_item_instances
132 WHERE
133 instance_number=p_asset_number;
134
135 EXCEPTION WHEN NO_DATA_FOUND THEN
136 l_instance_id:=-1;
137 END;
138
139
140 l_where_clause := l_where_clause || ' AND cii.instance_id = '|| l_instance_id ;
141 END IF;
142
143 IF p_owning_department IS NOT NULL THEN
144 l_where_clause := l_where_clause || ' AND wdj.owning_department = '|| p_owning_department ;
145 END IF;
146
147 IF p_scheduled_start_date_from IS NOT NULL THEN
148 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date >= fnd_date.canonical_to_date( '||' '' '||p_scheduled_start_date_from||' '' ) ';
149 END IF;
150
151 IF p_scheduled_start_date_to IS NOT NULL THEN
152 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date <= fnd_date.canonical_to_date('||' '' '||p_scheduled_start_date_to||' '' ) ';
153 END IF;
154
155 IF p_backlog_horizon IS NOT NULL THEN
156 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date >= (sysdate - '|| p_backlog_horizon || ') ' ;
157 END IF;
158
159 IF p_horizon IS NOT NULL THEN
160 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date <= (sysdate + '||p_horizon || ') ' ;
161 END IF;
162
163 IF p_status_type IS NOT NULL THEN
164 l_where_clause := l_where_clause || ' AND ewod.user_defined_status_id = ' || p_status_type ;
165 END IF;
166
167 IF p_project IS NOT NULL THEN
168 l_where_clause := l_where_clause || ' AND wdj.project_id = ' || p_project ;
169 END IF;
170
171 IF p_task IS NOT NULL THEN
172 l_where_clause := l_where_clause || ' AND wdj.task_id = ' || p_task ;
173 END IF;
174
175 IF p_assigned_department IS NOT NULL THEN
176 l_where_clause := l_where_clause || ' AND EXISTS (SELECT 1
177 FROM wip_operations wo
178 WHERE wo.wip_entity_id = wdj.wip_entity_id
179 AND wo.department_id = ' || p_assigned_department || ' ) ';
180 END IF;
181
182 l_sql_stmt := l_sql_stmt || l_where_clause;
183
184
185 OPEN get_wip_entity_id_csr FOR l_sql_stmt USING p_organization_id;
186 LOOP
187 FETCH get_wip_entity_id_csr BULK COLLECT INTO wip_entity_id_tbl LIMIT l_rows;
188
189 IF wip_entity_id_tbl.count > 0 THEN
190 FOR i IN wip_entity_id_tbl.first..wip_entity_id_tbl.last
191 LOOP
192 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
193 fnd_message.set_name('EAM','EAM_PROCESS_WORK_ORDER');
194 fnd_message.set_token('WORK_ORDER',get_wip_entity_name( wip_entity_id_tbl(i)),FALSE);
195 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
196 END IF;
197
198 eam_material_validate_pub.Check_Shortage
199 (p_api_version => l_api_version
200 , x_return_status => l_return_status
201 , x_msg_count => l_msg_count
202 , x_msg_data => l_msg_data
203 , p_commit => FND_API.G_TRUE
204 , p_wip_entity_id => wip_entity_id_tbl(i)
205 , x_shortage_exists => l_shortage_exists
206 , p_source_api => 'Concurrent'
207 );
208 IF l_return_status='E' THEN
209 CLOSE get_wip_entity_id_csr;
210 RAISE l_check_shortage_excep;
211 END IF;
212 END LOOP;
213 END IF;
214 IF l_return_status='E' THEN
215 CLOSE get_wip_entity_id_csr;
216 RAISE l_check_shortage_excep;
217 END IF;
218 EXIT WHEN get_wip_entity_id_csr%NOTFOUND;
219 END LOOP;
220 CLOSE get_wip_entity_id_csr;
221
222 retcode := 0;
223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
224
225 EXCEPTION
226 WHEN l_check_shortage_excep THEN
227 FOR indexCount IN 1 ..l_msg_count
228 LOOP
229 errbuf := errbuf || FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
230 END LOOP;
231 retcode := 2;
232 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
233 WHEN OTHERS THEN
234 errbuf := SQLERRM;
235 retcode := 2;
236 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
237 END Material_Shortage_CP;
238
239 END eam_material_validate_pvt;
[Home] [Help]