1 PACKAGE BODY eam_material_validate_pvt AS
2 /* $Header: EAMVMSCB.pls 120.8.12010000.2 2008/10/06 09:33:18 smrsharm 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(2000);
99 l_where_clause VARCHAR2(2000);
100 l_rows NUMBER := 5000;
101 l_encoding VARCHAR2(2000);
102 BEGIN
103 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
104 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="'||l_encoding ||'"?>' );
105 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ROWSET>');
106 l_sql_stmt := ' SELECT wdj.wip_entity_id
107 FROM wip_discrete_jobs wdj, eam_work_order_details ewod, csi_item_instances cii
108 WHERE wdj.wip_entity_id = ewod.wip_entity_id
109 AND wdj.maintenance_object_id = cii.instance_id
110 AND wdj.maintenance_object_type = 3
111 AND wdj.organization_id = :p_org_id ';
112
113 IF p_work_order_from IS NOT NULL THEN
114 l_where_clause := l_where_clause || ' AND wdj.wip_entity_id >= '|| p_work_order_from ;
115 IF p_work_order_to IS NOT NULL THEN
116 l_where_clause := l_where_clause || ' AND wdj.wip_entity_id <= '|| p_work_order_to ;
117 ELSE
118 l_where_clause := ' AND wdj.wip_entity_id = '|| p_work_order_from ;
119 END IF;
120 END IF;
121
122
123 IF p_asset_number IS NOT NULL THEN
124 l_where_clause := l_where_clause || ' AND cii.instance_number = '|| '''' ||p_asset_number ||'''';
125 END IF;
126
127 IF p_owning_department IS NOT NULL THEN
128 l_where_clause := l_where_clause || ' AND wdj.owning_department = '|| p_owning_department ;
129 END IF;
130
131 IF p_scheduled_start_date_from IS NOT NULL THEN
132 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date >= fnd_date.canonical_to_date( '||' '' '||p_scheduled_start_date_from||' '' ) ';
133 END IF;
134
135 IF p_scheduled_start_date_to IS NOT NULL THEN
136 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date <= fnd_date.canonical_to_date('||' '' '||p_scheduled_start_date_to||' '' ) ';
137 END IF;
138
139 IF p_backlog_horizon IS NOT NULL THEN
140 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date >= (sysdate - '|| p_backlog_horizon || ') ' ;
141 END IF;
142
143 IF p_horizon IS NOT NULL THEN
144 l_where_clause := l_where_clause || ' AND wdj.scheduled_start_date <= (sysdate + '||p_horizon || ') ' ;
145 END IF;
146
147 IF p_status_type IS NOT NULL THEN
148 l_where_clause := l_where_clause || ' AND ewod.user_defined_status_id = ' || p_status_type ;
149 END IF;
150
151 IF p_project IS NOT NULL THEN
152 l_where_clause := l_where_clause || ' AND wdj.project_id = ' || p_project ;
153 END IF;
154
155 IF p_task IS NOT NULL THEN
156 l_where_clause := l_where_clause || ' AND wdj.task_id = ' || p_task ;
157 END IF;
158
159 IF p_assigned_department IS NOT NULL THEN
160 l_where_clause := l_where_clause || ' AND EXISTS (SELECT 1
161 FROM wip_operations wo
162 WHERE wo.wip_entity_id = wdj.wip_entity_id
163 AND wo.department_id = ' || p_assigned_department || ' ) ';
164 END IF;
165
166 l_sql_stmt := l_sql_stmt || l_where_clause;
167
168
169 OPEN get_wip_entity_id_csr FOR l_sql_stmt USING p_organization_id;
170 LOOP
171 FETCH get_wip_entity_id_csr BULK COLLECT INTO wip_entity_id_tbl LIMIT l_rows;
172
173 IF wip_entity_id_tbl.count > 0 THEN
174 FOR i IN wip_entity_id_tbl.first..wip_entity_id_tbl.last
175 LOOP
176 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
177 fnd_message.set_name('EAM','EAM_PROCESS_WORK_ORDER');
178 fnd_message.set_token('WORK_ORDER',get_wip_entity_name( wip_entity_id_tbl(i)),FALSE);
179 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
180 END IF;
181
182 eam_material_validate_pub.Check_Shortage
183 (p_api_version => l_api_version
184 , x_return_status => l_return_status
185 , x_msg_count => l_msg_count
186 , x_msg_data => l_msg_data
187 , p_commit => FND_API.G_TRUE
188 , p_wip_entity_id => wip_entity_id_tbl(i)
189 , x_shortage_exists => l_shortage_exists
190 , p_source_api => 'Concurrent'
191 );
192 IF l_return_status='E' THEN
193 CLOSE get_wip_entity_id_csr;
194 RAISE l_check_shortage_excep;
195 END IF;
196 END LOOP;
197 END IF;
198 IF l_return_status='E' THEN
199 CLOSE get_wip_entity_id_csr;
200 RAISE l_check_shortage_excep;
201 END IF;
202 EXIT WHEN get_wip_entity_id_csr%NOTFOUND;
203 END LOOP;
204 CLOSE get_wip_entity_id_csr;
205
206 retcode := 0;
207 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
208
209 EXCEPTION
210 WHEN l_check_shortage_excep THEN
211 FOR indexCount IN 1 ..l_msg_count
212 LOOP
213 errbuf := errbuf || FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
214 END LOOP;
215 retcode := 2;
216 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
217 WHEN OTHERS THEN
218 errbuf := SQLERRM;
219 retcode := 2;
220 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
221 END Material_Shortage_CP;
222
223 END eam_material_validate_pvt;
[Home] [Help]