1 PACKAGE BODY cz_atp_util AS
2 /* $Header: czatpub.pls 120.0 2005/05/25 05:06:46 appldev noship $ */
3 -- constants
4
5 PROCEDURE insert_atp_request (p_inventory_item_id IN NUMBER,
6 p_organization_id IN NUMBER,
7 p_quantity IN NUMBER,
8 p_atp_group_id IN OUT NOCOPY NUMBER,
9 p_return_status OUT NOCOPY VARCHAR2,
10 p_error_message OUT NOCOPY VARCHAR2,
11 p_sequence_number IN NUMBER,
12 p_atp_rule_id IN NUMBER DEFAULT NULL) IS
13
14 rec_mgav mtl_group_atps_view%rowtype;
15 p_process_flag NUMBER := 1;
16 atp_lead_time NUMBER := NULL;
17 demand_class VARCHAR2(30) := NULL;
18 atp_group_id_exc EXCEPTION;
19 no_atp_rule_exc EXCEPTION;
20 no_item_exc EXCEPTION;
21 no_calendar_org_exc EXCEPTION;
22
23 BEGIN
24
25 -- Initialize p_return_status
26 p_return_status := G_RET_STS_SUCCESS;
27
28 -- ATP group ID assignment
29 IF p_atp_group_id IS NULL THEN
30 select MTL_DEMAND_INTERFACE_S.NEXTVAL into p_atp_group_id
31 from dual;
32 END IF;
33
34 rec_mgav.ATP_GROUP_ID := p_atp_group_id;
35
36 if rec_mgav.ATP_GROUP_ID is null then
37 -- cannot proceed with null atp group id
38 raise atp_group_id_exc;
39 end if;
40
41 -- ATP rule ID selection
42 -- if passed in, use it. else find out from item if it has atp rule or else
43 -- takes it from organization default. cannot be null
44
45 -- NOTE: This block always needs to be run, because it selects
46 -- primary_uom_code as well as atp_rule_id
47 begin
48 select atp_rule_id, primary_uom_code
49 into
50 rec_mgav.atp_rule_id, rec_mgav.uom_code
51 from
52 mtl_system_items
53 where
54 inventory_item_id = p_inventory_item_id and
55 organization_id = p_organization_id;
56 exception
57 WHEN NO_DATA_FOUND THEN
58 raise no_item_exc;
59 end;
60
61 -- if ATP rule was provided, use instead
62 if p_atp_rule_id is not null then
63 rec_mgav.atp_rule_id := p_atp_rule_id;
64 end if;
65
66 -- check if ATP rule ID has been provided or selected from msi table.
67 -- if not, take it from mtl parameters by org definition.
68 begin
69 if rec_mgav.atp_rule_id is null then
70 begin
71 SELECT r.rule_id
72 INTO
73 rec_mgav.atp_rule_id
74 FROM
75 mtl_parameters p, mtl_atp_rules r
76 WHERE
77 p.default_atp_rule_id = r.rule_id
78 AND p.organization_id = p_organization_id;
79 exception
80 when NO_DATA_FOUND then
81 --No Atp Rule specified
82 raise no_atp_rule_exc;
83 end;
84 end if;
85 exception
86 when NO_DATA_FOUND then
87 -- item not found
88 raise no_item_exc;
89 end;
90 --dbms_output.put_line ('Atp Rule id : ' || to_char (rec_mgav.ATP_RULE_ID) );
91
92 -- retrieve calendar organization ID
93 begin
94 SELECT MTL.ORGANIZATION_ID
95 into rec_mgav.atp_calendar_organization_id
96 FROM HR_ORGANIZATION_UNITS HR, MTL_PARAMETERS MTL
97 WHERE HR.ORGANIZATION_ID = MTL.ORGANIZATION_ID
98 AND MTL.CALENDAR_CODE is not null
99 AND MTL.CALENDAR_EXCEPTION_SET_ID is not null
100 AND MTL.ORGANIZATION_ID = p_organization_id;
101 if rec_mgav.atp_calendar_organization_id is null then
102 raise no_calendar_org_exc;
103 end if;
104 exception
105 when NO_DATA_FOUND then
106 raise no_calendar_org_exc;
107 end;
108
109 --dbms_output.put_line ('Calendar org id: ' ||
110 -- to_char (rec_mgav.atp_calendar_organization_id) );
111
112 INSERT INTO MTL_GROUP_ATPS_VIEW
113 (ATP_GROUP_ID,
114 ORGANIZATION_ID,
115 INVENTORY_ITEM_ID,
116 LAST_UPDATE_DATE,
117 LAST_UPDATED_BY,
118 CREATION_DATE,
119 CREATED_BY,
120 LAST_UPDATE_LOGIN,
121 ATP_RULE_ID,
122 REQUEST_QUANTITY,
123 REQUEST_PRIMARY_UOM_QUANTITY,
124 REQUEST_DATE,
125 ATP_LEAD_TIME,
126 ATP_CALENDAR_ORGANIZATION_ID,
127 AVAILABLE_TO_ATP,
128 UOM_CODE,
129 DEMAND_CLASS,
130 N_COLUMN2
131 )
132 values
133 (
134 rec_mgav.ATP_GROUP_ID,
135 p_organization_id,
136 p_inventory_item_id,
137 sysdate,
138 -1,
139 sysdate,
140 -1,
141 -1,
142 rec_mgav.ATP_RULE_ID,
143 p_quantity,
144 p_quantity,
145 sysdate,
146 atp_lead_time,
147 rec_mgav.ATP_CALENDAR_ORGANIZATION_ID,
148 1,
149 rec_mgav.UOM_CODE,
150 demand_class,
151 p_sequence_number
152 );
153
154 -- Q: Should we do the commit, or have it done externally?
155 -- A: for now, keep it here, because we're not managing transactions
156 -- in "read only" mode
157 commit;
158
159 EXCEPTION
160 WHEN atp_group_id_exc THEN
161 p_atp_group_id := null;
162 p_error_message := 'ATP group ID could not be assigned';
163 p_return_status := G_RET_STS_ERROR;
164 WHEN no_atp_rule_exc THEN
165 p_atp_group_id := null;
166 p_error_message := 'ATP rule could not be determined';
167 p_return_status := G_RET_STS_ERROR;
168 WHEN no_item_exc THEN
169 -- Item not found
170 p_atp_group_id := null;
171 p_error_message := 'Invalid inventory item id';
172 p_return_status := G_RET_STS_ERROR;
173 WHEN no_calendar_org_exc THEN
174 p_atp_group_id := null;
175 p_error_message := 'Calendar org could not be determined';
176 p_return_status := G_RET_STS_ERROR;
177 WHEN OTHERS THEN
178 p_atp_group_id := null;
179 p_error_message := 'cz_atp_util.insert_atp_request: ' || SQLERRM;
180 p_return_status := G_RET_STS_UNEXP_ERROR;
181 END insert_atp_request;
182
183
184 PROCEDURE run_atp_check (p_return_status OUT NOCOPY VARCHAR2, p_error_message OUT NOCOPY VARCHAR2,
185 p_atp_group_id IN NUMBER, p_user_id IN NUMBER,
186 p_resp_id IN NUMBER, p_appl_id IN NUMBER,
187 p_timeout IN NUMBER) IS
188 no_atp_group_exc EXCEPTION;
189 retval NUMBER;
190 mgr_outcome varchar2 (30);
191 mgr_message varchar2 (240);
192 BEGIN
193
194 -- Initialize p_return_status
195 p_return_status := G_RET_STS_SUCCESS;
196
197 -- Initialize apps environment.
198 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
199
200 IF p_atp_group_id IS NULL THEN
201 raise no_atp_group_exc;
202 END IF;
203
204 retval := fnd_transaction.synchronous
205 (p_timeout,
206 mgr_outcome,
207 mgr_message,
208 'INV',
209 'INXATP',
210 -- 'INXATP GROUP_ID=343499 DETAIL_FLAG=0 MRP_STATUS=1'
211 -- 'INXATP GROUP_ID=343499 MRP_STATUS=1'
212 'INXATP GROUP_ID=' || to_char(p_atp_group_id) || ' MRP_STATUS=1'
213 );
214
215 if retval = 0 then
216 -- success
217 --dbms_output.put_line ('Success');
218 null;
219 elsif retval = 1 then
220 -- timeout
221 --dbms_output.put_line ('Timed out');
222 p_error_message := 'cz_atp_util.run_atp_check timed out';
223 elsif retval = 2 then
224 -- no manager
225 --dbms_output.put_line ('No manager');
226 p_error_message := 'cz_atp_util.run_atp_check failed: no manager';
227 elsif retval = 3 then
228 -- other
229 --dbms_output.put_line ('Other Error');
230 p_error_message := 'cz_atp_util failed: ' || mgr_outcome || ' '
231 || mgr_message;
232 end if;
233
234 EXCEPTION
235 WHEN no_atp_group_exc THEN
236 p_error_message := 'cz_atp_util.run_atp_check: ATP group ID is required for processing';
237 p_return_status := G_RET_STS_ERROR;
238 WHEN OTHERS THEN
239 p_error_message := 'cz_atp_util.run_atp_check: ' || SQLERRM;
240 p_return_status := G_RET_STS_UNEXP_ERROR;
241 END run_atp_check;
242
243
244 PROCEDURE get_atp_result (p_atp_group_id IN NUMBER, p_earliest_atp_date OUT NOCOPY DATE,
245 p_return_status OUT NOCOPY VARCHAR2, p_error_message OUT NOCOPY VARCHAR2,
246 p_sequence_number IN NUMBER) IS
247 error_code NUMBER;
248 ret_val number;
249 group_available_date DATE;
250 no_atp_group_exc EXCEPTION;
251 BEGIN
252
253 -- Initialize p_return_status
254 p_return_status := G_RET_STS_SUCCESS;
255
256 if p_atp_group_id is null then
257 raise no_atp_group_exc;
258 end if;
259
260 select
261 error_code,
262 group_available_date,
263 earliest_atp_date
264 into
265 error_code,
266 group_available_date,
267 p_earliest_atp_date
268 from
269 MTL_GROUP_ATPS_VIEW
270 where
271 ATP_GROUP_ID = p_atp_group_id and
272 n_column2 = p_sequence_number;
273
274 IF p_earliest_atp_date IS NULL THEN
275 IF error_code IS NOT NULL AND error_code <> 0 THEN
276 SELECT meaning INTO p_error_message FROM mfg_lookups
277 WHERE lookup_code = error_code
278 AND lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS';
279 p_return_status := G_RET_STS_ERROR;
280 ELSIF error_code IS NULL THEN
281 p_error_message := 'cz_atp_util.get_atp_result: MTL_DEMAND_INTERFACE row '
282 || 'not processed';
283 p_return_status := G_RET_STS_UNEXP_ERROR;
284 ELSE
285 p_error_message := 'cz_atp_util.get_atp_result: unknown '
286 || 'mtl_demand_interface error';
287 p_return_status := G_RET_STS_UNEXP_ERROR;
288 END IF;
289 END IF;
290
291 -- delete record
292 DELETE FROM mtl_group_atps_view WHERE atp_group_id = p_atp_group_id
293 AND n_column2 = p_sequence_number;
294
295 -- need to commit here
296 commit;
297
298 EXCEPTION
299 WHEN no_atp_group_exc THEN
300 p_error_message := 'cz_atp_util.get_atp_result: ATP group ID is required input';
301 p_return_status := G_RET_STS_ERROR;
302 WHEN OTHERS THEN
303 p_error_message := 'cz_atp_util.get_atp_result: ' || SQLERRM;
304 p_return_status := G_RET_STS_UNEXP_ERROR;
305 END get_atp_result;
306
307 END CZ_ATP_UTIL;
[Home] [Help]