1 PACKAGE BODY GMO_DISPENSE_SETUP_PVT
2 -- $Header: GMOVDSSB.pls 120.3 2006/02/01 14:45:31 swasubra noship $
3 AS
4
5
6 --This function is used to obtain the display name of the item
7 --identified by the item ID.
8 FUNCTION GET_ITEM_DISPLAY_NAME(P_ITEM_ID NUMBER)
9 RETURN VARCHAR2
10
11 IS
12
13 --This cursor would fetch the item display name for the specified
14 --item ID.
15 CURSOR GET_ITEM_DISPLAY_NAME_CUR IS
16 SELECT
17 CONCATENATED_SEGMENTS
18 FROM
19 MTL_SYSTEM_ITEMS_VL
20 WHERE
21 INVENTORY_ITEM_ID = P_ITEM_ID;
22
23 --This variable would hold the item display name.
24 L_DISPLAY_NAME VARCHAR2(240);
25
26 BEGIN
27
28 --Open the cursor.
29 OPEN GET_ITEM_DISPLAY_NAME_CUR;
30
31 --Fetch the item display name into the local variable.
32 FETCH GET_ITEM_DISPLAY_NAME_CUR INTO L_DISPLAY_NAME;
33
34 --Close the cursor.
35 CLOSE GET_ITEM_DISPLAY_NAME_CUR;
36
37 --Return the item display name.
38 RETURN L_DISPLAY_NAME;
39
40 EXCEPTION
41 WHEN OTHERS THEN
42
43 IF GET_ITEM_DISPLAY_NAME_CUR%ISOPEN THEN
44 CLOSE GET_ITEM_DISPLAY_NAME_CUR;
45 END IF;
46 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
47 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
48 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
49 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_ITEM_DISPLAY_NAME');
50 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
51 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
52 'edr.plsql.GMO_DISPENSE_SETUP_PVT.GET_ITEM_DISPLAY_NAME',
53 FALSE
54 );
55 END IF;
56 --Diagnostics End
57
58 APP_EXCEPTION.RAISE_EXCEPTION;
59
60 END GET_ITEM_DISPLAY_NAME;
61
62
63 --This function is used to obtain the description of the item
64 --identified by the item ID.
65 FUNCTION GET_ITEM_DESCRIPTION(P_ITEM_ID NUMBER)
66 RETURN VARCHAR2
67
68 IS
69
70 --This cursor would fetch the description for the specified
71 --item ID.
72 CURSOR GET_ITEM_DESCRIPTION_CUR IS
73 SELECT
74 DESCRIPTION
75 FROM
76 MTL_SYSTEM_ITEMS_VL
77 WHERE
78 INVENTORY_ITEM_ID = P_ITEM_ID;
79
80 --This variable would hold the item description.
81 L_DESCRIPTION VARCHAR2(240);
82
83 BEGIN
84
85 --Open the cursor.
86 OPEN GET_ITEM_DESCRIPTION_CUR;
87
88 --Fetch the item description into the local variable.
89 FETCH GET_ITEM_DESCRIPTION_CUR INTO L_DESCRIPTION;
90
91 CLOSE GET_ITEM_DESCRIPTION_CUR;
92
93 --Return the item description.
94 RETURN L_DESCRIPTION;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98
99 IF GET_ITEM_DESCRIPTION_CUR%ISOPEN THEN
100 CLOSE GET_ITEM_DESCRIPTION_CUR;
101 END IF;
102 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
103 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
104 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
105 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_ITEM_DESCRIPTION');
106
107 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
108 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
109 'edr.plsql.GMO_DISPENSE_SETUP_PVT.GET_ITEM_DESCRIPTION',
110 FALSE
111 );
112 END IF;
113
114 APP_EXCEPTION.RAISE_EXCEPTION;
115
116 END GET_ITEM_DESCRIPTION;
117
118
119 --This procedure is used to create a definition context in Process Instructions
120 --for the specified entity name, entity key and instruction types. It would return
121 --a instruction process ID through its OUT parameter.
122 PROCEDURE CREATE_DEFN_CONTEXT
123 (
124 P_ENTITY_NAME IN VARCHAR2,
125 P_ENTITY_KEY IN VARCHAR2,
126 P_ENTITY_DISPLAYNAME IN VARCHAR2,
127 P_INSTRUCTION_TYPE IN FND_TABLE_OF_VARCHAR2_255,
128 P_MODE IN VARCHAR2,
129 P_CONTEXT_PARAMETER_NAMES IN FND_TABLE_OF_VARCHAR2_255,
130 P_CONTEXT_PARAMETER_VALUES IN FND_TABLE_OF_VARCHAR2_255,
131 P_CURR_INSTRUCTION_PROCESS_ID IN NUMBER DEFAULT NULL,
132 X_INSTRUCTION_PROCESS_ID OUT NOCOPY NUMBER
133 )
134
135 IS
136
137
138 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
139
140 L_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
141
142 L_ENTITY_DISPLAYNAME FND_TABLE_OF_VARCHAR2_255;
143
144
145 L_RETURN_STATUS VARCHAR2(10);
146 L_MSG_COUNT NUMBER;
147 L_MSG_DATA VARCHAR2(4000);
148
149
150 L_CONTEXT_PARAMETERS GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE;
151
152 i NUMBER;
153
154 CREATE_ERROR EXCEPTION;
155
156 BEGIN
157
158 --Initialize the the entity names array containing only one element.
159 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
160 L_ENTITY_NAME.EXTEND;
161
162 --Initialize the the entity keys array containing only one element.
163 L_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
164 L_ENTITY_KEY.EXTEND;
165
166 --Initialize the the entity display name array containing only one element.
167 L_ENTITY_DISPLAYNAME := FND_TABLE_OF_VARCHAR2_255();
168 L_ENTITY_DISPLAYNAME.EXTEND;
169
170 --Set the entity name, key and display name values.
171 L_ENTITY_NAME(1) := P_ENTITY_NAME;
172 L_ENTITY_KEY(1) := P_ENTITY_KEY;
173 L_ENTITY_DISPLAYNAME(1) := P_ENTITY_DISPLAYNAME;
174
175 --Set the context parameter values.
176 FOR i IN 1..P_CONTEXT_PARAMETER_NAMES.COUNT LOOP
177
178 L_CONTEXT_PARAMETERS(i).name := P_CONTEXT_PARAMETER_NAMES(i);
179 L_CONTEXT_PARAMETERS(i).value := P_CONTEXT_PARAMETER_VALUES(i);
180
181 END LOOP;
182
183 --Call the API to create a definition context.
184 GMO_INSTRUCTION_GRP.CREATE_DEFN_CONTEXT
185 (
186 P_API_VERSION => 1.0,
187 P_INIT_MSG_LIST => FND_API.G_FALSE,
188 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_NONE,
189 P_ENTITY_NAME => L_ENTITY_NAME,
190 P_ENTITY_KEY => L_ENTITY_KEY,
191 P_ENTITY_DISPLAYNAME => L_ENTITY_DISPLAYNAME,
192 P_INSTRUCTION_TYPE => P_INSTRUCTION_TYPE,
193 P_MODE => P_MODE,
194 P_CONTEXT_PARAMETERS => L_CONTEXT_PARAMETERS,
195 P_CURR_INSTR_PROCESS_ID => P_CURR_INSTRUCTION_PROCESS_ID,
196 X_INSTRUCTION_PROCESS_ID => X_INSTRUCTION_PROCESS_ID,
197 X_RETURN_STATUS => L_RETURN_STATUS,
198 X_MSG_COUNT => L_MSG_COUNT,
199 X_MSG_DATA => L_MSG_DATA
200 );
201
202 --If the return status is not success then raise an exception.
203 IF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
204 RAISE CREATE_ERROR;
205 END IF;
206
207 EXCEPTION
208 WHEN CREATE_ERROR THEN
209 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
210
211 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
212 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
213 'edr.plsql.GMO_DISPENSE_SETUP_PVT.CREATE_DEFN_CONTEXT',
214 FALSE
215 );
216 END IF;
217
218 APP_EXCEPTION.RAISE_EXCEPTION;
219
220 WHEN OTHERS THEN
221 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
222 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
223 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
224 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','CREATE_DEFN_CONTEXT');
225
226 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
227 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
228 'edr.plsql.GMO_DISPENSE_SETUP_PVT.CREATE_DEFN_CONTEXT',
229 FALSE
230 );
231 END IF;
232
233 APP_EXCEPTION.RAISE_EXCEPTION;
234
235 END CREATE_DEFN_CONTEXT;
236
237
238 --This procedure is used to update the context parameters associated with the specified process ID and entity parameters
239 PROCEDURE UPDATE_CONTEXT_PARAMS
240 (
241 P_INSTRUCTION_PROCESS_ID IN NUMBER,
242 P_ENTITY_NAME IN VARCHAR2,
243 P_ENTITY_KEY IN VARCHAR2,
244 P_ENTITY_DISPLAYNAME IN VARCHAR2,
245 P_INSTRUCTION_TYPE IN FND_TABLE_OF_VARCHAR2_255,
246 P_CONTEXT_PARAMETER_NAMES IN FND_TABLE_OF_VARCHAR2_255,
247 P_CONTEXT_PARAMETER_VALUES IN FND_TABLE_OF_VARCHAR2_255
248 )
249
250 IS
251
252 L_ENTITY_NAME FND_TABLE_OF_VARCHAR2_255;
253
254 L_ENTITY_KEY FND_TABLE_OF_VARCHAR2_255;
255
256 L_ENTITY_DISPLAYNAME FND_TABLE_OF_VARCHAR2_255;
257
258
259 L_RETURN_STATUS VARCHAR2(10);
260 L_MSG_COUNT NUMBER;
261 L_MSG_DATA VARCHAR2(4000);
262
263 L_INSTRUCTION_PROCESS_ID NUMBER;
264
265
266 L_CONTEXT_PARAMETERS GMO_DATATYPES_GRP.GMO_DEFINITION_PARAM_TBL_TYPE;
267
268 i NUMBER;
269
270 UPDATE_ERROR EXCEPTION;
271
272 BEGIN
273
274 --Initialize the the entity names array containing only one element.
275 L_ENTITY_NAME := FND_TABLE_OF_VARCHAR2_255();
276 L_ENTITY_NAME.EXTEND;
277
278 --Initialize the the entity keys array containing only one element.
279 L_ENTITY_KEY := FND_TABLE_OF_VARCHAR2_255();
280 L_ENTITY_KEY.EXTEND;
281
282 --Initialize the the entity display name array containing only one element.
283 L_ENTITY_DISPLAYNAME := FND_TABLE_OF_VARCHAR2_255();
284 L_ENTITY_DISPLAYNAME.EXTEND;
285
286 --Set the entity name, key and display name values.
287 L_ENTITY_NAME(1) := P_ENTITY_NAME;
288 L_ENTITY_KEY(1) := P_ENTITY_KEY;
289 L_ENTITY_DISPLAYNAME(1) := P_ENTITY_DISPLAYNAME;
290
291 --Set the context parameter values.
292 FOR i IN 1..P_CONTEXT_PARAMETER_NAMES.COUNT LOOP
293
294 L_CONTEXT_PARAMETERS(i).name := P_CONTEXT_PARAMETER_NAMES(i);
295 L_CONTEXT_PARAMETERS(i).value := P_CONTEXT_PARAMETER_VALUES(i);
296
297 END LOOP;
298
299 --Call the API to update the context parameters of the existing definition context.
300 GMO_INSTRUCTION_GRP.CREATE_DEFN_CONTEXT
301 (
302 P_API_VERSION => 1.0,
303 P_INIT_MSG_LIST => FND_API.G_FALSE,
304 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_NONE,
305 P_CURR_INSTR_PROCESS_ID => P_INSTRUCTION_PROCESS_ID,
306 P_ENTITY_NAME => L_ENTITY_NAME,
307 P_ENTITY_KEY => L_ENTITY_KEY,
308 P_ENTITY_DISPLAYNAME => L_ENTITY_DISPLAYNAME,
309 P_INSTRUCTION_TYPE => P_INSTRUCTION_TYPE,
310 P_MODE => GMO_CONSTANTS_GRP.G_INSTR_DEFN_MODE_UPDATE,
311 P_CONTEXT_PARAMETERS => L_CONTEXT_PARAMETERS,
312 X_INSTRUCTION_PROCESS_ID => L_INSTRUCTION_PROCESS_ID,
313 X_RETURN_STATUS => L_RETURN_STATUS,
314 X_MSG_COUNT => L_MSG_COUNT,
315 X_MSG_DATA => L_MSG_DATA
316 );
317
318 --If the return status is not success then raise an exception.
319 IF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
320 RAISE UPDATE_ERROR;
321 END IF;
322
323 EXCEPTION
324 WHEN UPDATE_ERROR THEN
325 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
326
327 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
329 'edr.plsql.GMO_DISPENSE_SETUP_PVT.UPDATE_CONTEXT_PARAMS',
330 FALSE
331 );
332 END IF;
333
334 APP_EXCEPTION.RAISE_EXCEPTION;
335
336 WHEN OTHERS THEN
337 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
338 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
339 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
340 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','CREATE_DEFN_CONTEXT');
341
342 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
343 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
344 'edr.plsql.GMO_DISPENSE_SETUP_PVT.UPDATE_CONTEXT_PARAMS',
345 FALSE
346 );
347 END IF;
348
349 APP_EXCEPTION.RAISE_EXCEPTION;
350
351 END UPDATE_CONTEXT_PARAMS;
352
353
354 --This procedure is used to construct transaction XML to be send to ERES for processing.
355 --In particular, it obtains the process instruction details (identified by the
356 --instruction process ID) in XML format and merges the same with the current XML parameter.
357 --This merged XML is returned as the output XML.
358 PROCEDURE GET_TRANSACTION_XML(P_INSTR_PROCESS_ID IN NUMBER,
359 P_CURRENT_XML IN CLOB,
360 X_OUTPUT_XML OUT NOCOPY CLOB)
361
362 IS
363
364 L_PROCESS_STATUS VARCHAR2(20);
365
366 L_RETURN_STATUS VARCHAR2(10);
367
368 L_MSG_COUNT NUMBER;
369
370 L_MSG_DATA VARCHAR2(4000);
371
372 L_INSTR_XML CLOB;
373
374 XML_ERROR EXCEPTION;
375
376 BEGIN
377
378 --Create a temporary CLOB that would hold the final transaction XML.
379 DBMS_LOB.CREATETEMPORARY(X_OUTPUT_XML, TRUE, DBMS_LOB.SESSION);
380
381 --Append the contents of the current XML.
382 DBMS_LOB.APPEND(X_OUTPUT_XML,P_CURRENT_XML);
383
384 --Call the PL/SQL API to fetch the instruction set details in XML format for the specified
385 --process ID.
386 GMO_INSTRUCTION_GRP.GET_INSTR_XML
387 (P_API_VERSION => 1.0,
388 P_INIT_MSG_LIST => FND_API.G_FALSE,
389 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_NONE,
390 P_INSTRUCTION_PROCESS_ID => P_INSTR_PROCESS_ID,
391 X_OUTPUT_XML => L_INSTR_XML,
392 X_RETURN_STATUS => L_RETURN_STATUS,
393 X_MSG_COUNT => L_MSG_COUNT,
394 X_MSG_DATA => L_MSG_DATA);
395
396 --If the return status is not success then raise an error.
397 IF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
398 RAISE XML_ERROR;
399 END IF;
400
401 --Append the XML of instruction set details to the final XML.
402 DBMS_LOB.APPEND(X_OUTPUT_XML,L_INSTR_XML);
403
404 --Append the XML Footer to the FINAL XML.
405 DBMS_LOB.WRITEAPPEND(X_OUTPUT_XML,length(EDR_CONSTANTS_GRP.G_ERECORD_XML_FOOTER),EDR_CONSTANTS_GRP.G_ERECORD_XML_FOOTER);
406
407 EXCEPTION
408 WHEN XML_ERROR THEN
409 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
410 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
411 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
412 'edr.plsql.GMO_DISPENSE_SETUP_PVT.GET_TRANSACTION_XML',
413 FALSE
414 );
415 END IF;
416
417 APP_EXCEPTION.RAISE_EXCEPTION;
418
419 WHEN OTHERS THEN
420 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
421 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
422 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
423 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_TRANSACTION_XML');
424
425 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
426 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
427 'edr.plsql.GMO_DISPENSE_SETUP_PVT.GET_TRANSACTION_XML',
428 FALSE
429 );
430 END IF;
431
432 APP_EXCEPTION.RAISE_EXCEPTION;
433
434
435
436 END GET_TRANSACTION_XML;
437
438
439 --This procedure used to send an acknowledgement back to process instructions to
440 --copy the details from the temp tables into the permanent tables for the specified
441 --instruction process ID and entity.
442 PROCEDURE SEND_INSTR_ACKN(P_INSTR_PROCESS_ID IN NUMBER,
443 P_ENTITY_NAME IN VARCHAR2,
444 P_SOURCE_ENTITY_KEY IN VARCHAR2,
445 P_TARGET_ENTITY_KEY IN VARCHAR2)
446 IS
447
448 L_MSG_DATA VARCHAR2(4000);
449 L_MSG_COUNT NUMBER;
450 L_RETURN_STATUS VARCHAR2(10);
451 L_INSTRUCTION_TYPE VARCHAR2(4000);
452 L_INSTRUCTION_SET_ID NUMBER;
453 SEND_ACKN_ERROR EXCEPTION;
454
455 CURSOR DISPENSE_INSTR_TYPES_CSR IS
456 SELECT LOOKUP_CODE
457 FROM FND_LOOKUPS
458 WHERE LOOKUP_TYPE = 'GMO_INSTR_' || P_ENTITY_NAME;
459
460 BEGIN
461
462
463 --If the source and target entity keys are different then call PI's SEND_DEFN_FROM_DEFN
464 --to ensure proper versioning.
465 IF P_SOURCE_ENTITY_KEY <> P_TARGET_ENTITY_KEY THEN
466
467 --Open the cursor.
468 OPEN DISPENSE_INSTR_TYPES_CSR;
469
470 LOOP
471 FETCH DISPENSE_INSTR_TYPES_CSR INTO L_INSTRUCTION_TYPE;
472 EXIT WHEN DISPENSE_INSTR_TYPES_CSR%NOTFOUND;
473
474 --Create a definition from definition for each instruction type.
475 GMO_INSTRUCTION_GRP.CREATE_DEFN_FROM_DEFN
476 (
477 P_API_VERSION => 1.0,
478 P_INIT_MSG_LIST => FND_API.G_TRUE,
479 P_COMMIT => FND_API.G_FALSE,
480 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_NONE,
481 P_SOURCE_ENTITY_NAME => P_ENTITY_NAME,
482 P_SOURCE_ENTITY_KEY => P_SOURCE_ENTITY_KEY,
483 P_TARGET_ENTITY_NAME => P_ENTITY_NAME,
484 P_TARGET_ENTITY_KEY => P_TARGET_ENTITY_KEY,
485 P_INSTRUCTION_TYPE => L_INSTRUCTION_TYPE,
486 X_RETURN_STATUS => L_RETURN_STATUS,
487 X_MSG_COUNT => L_MSG_COUNT,
488 X_MSG_DATA => L_MSG_DATA,
489 X_INSTRUCTION_SET_ID => L_INSTRUCTION_SET_ID
490 );
491
492 --If the return status is not success then raise an exception.
493 IF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
494 RAISE SEND_ACKN_ERROR;
495 END IF;
496 END LOOP;
497
498 CLOSE DISPENSE_INSTR_TYPES_CSR;
499
500 END IF;
501
502 --Call the PL/SQL API to send the definition acknowledgement.
503 GMO_INSTRUCTION_GRP.SEND_DEFN_ACKN
504 (
505 P_API_VERSION => 1.0,
506 P_INIT_MSG_LIST => FND_API.G_FALSE,
507 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_NONE,
508 P_INSTRUCTION_PROCESS_ID => P_INSTR_PROCESS_ID,
509 P_ENTITY_NAME => P_ENTITY_NAME,
510 P_SOURCE_ENTITY_KEY => P_SOURCE_ENTITY_KEY,
511 P_TARGET_ENTITY_KEY => P_TARGET_ENTITY_KEY,
512 X_RETURN_STATUS => L_RETURN_STATUS,
513 X_MSG_COUNT => L_MSG_COUNT,
514 X_MSG_DATA => L_MSG_DATA
515 );
516
517 --If the return status is not success then raise an exception.
518 IF L_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR OR L_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
519 RAISE SEND_ACKN_ERROR;
520 END IF;
521
522 EXCEPTION
523 WHEN SEND_ACKN_ERROR THEN
524
525 IF DISPENSE_INSTR_TYPES_CSR%ISOPEN THEN
526 CLOSE DISPENSE_INSTR_TYPES_CSR;
527 END IF;
528
529 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
530
531 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
532 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
533 'edr.plsql.GMO_DISPENSE_SETUP_PVT.SEND_INSTR_ACKN',
534 FALSE
535 );
536 END IF;
537
538 APP_EXCEPTION.RAISE_EXCEPTION;
539
540 WHEN OTHERS THEN
541
542 IF DISPENSE_INSTR_TYPES_CSR%ISOPEN THEN
543 CLOSE DISPENSE_INSTR_TYPES_CSR;
544 END IF;
545
546 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
547 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
548 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
549 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','SEND_INSTR_ACKN');
550
551 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
552 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
553 'edr.plsql.GMO_DISPENSE_SETUP_PVT.SEND_INSTR_ACKN',
554 FALSE
555 );
556 END IF;
557
558 APP_EXCEPTION.RAISE_EXCEPTION;
559 null;
560
561 END SEND_INSTR_ACKN;
562
563 --This procedure is used to obtain the dispense configuration for the specified
564 --item, organization and recipe.
565 PROCEDURE GET_DISPENSE_CONFIG
566 (
567 P_INVENTORY_ITEM_ID IN NUMBER,
568 P_ORGANIZATION_ID IN NUMBER,
569 P_RECIPE_ID IN NUMBER,
570 X_DISPENSE_CONFIG OUT NOCOPY GMO_DISPENSE_CONFIG%ROWTYPE,
571 X_INSTRUCTION_DEFINITION_KEY OUT NOCOPY VARCHAR2
572 )
573
574 IS
575
576 BEGIN
577
578 X_INSTRUCTION_DEFINITION_KEY := NULL;
579
580 --Obtain the dispense configuration for the case where both organization and recipe are specified.
581 BEGIN
582 SELECT * INTO X_DISPENSE_CONFIG
583 FROM GMO_DISPENSE_CONFIG
584 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
585 AND ORGANIZATION_ID = P_ORGANIZATION_ID
586 AND RECIPE_ID = P_RECIPE_ID
587 AND DISPENSE_REQUIRED_FLAG = 'Y'
588 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,START_DATE);
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN
592 X_DISPENSE_CONFIG := NULL;
593 END;
594
595
596 IF (X_DISPENSE_CONFIG.CONFIG_ID IS NOT NULL) THEN
597
598 --A valid dispense configuration has been found.
599 --Obtain the instruction defninition key and exit.
600 X_INSTRUCTION_DEFINITION_KEY := X_DISPENSE_CONFIG.CONFIG_ID;
601 RETURN;
602
603 END IF;
604
605 --Obtain the dispense configuration for the case where organization is specified, but recipe is null.
606 BEGIN
607 SELECT * INTO X_DISPENSE_CONFIG
608 FROM GMO_DISPENSE_CONFIG
609 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
610 AND ORGANIZATION_ID=P_ORGANIZATION_ID
611 AND DISPENSE_REQUIRED_FLAG = 'Y'
612 AND RECIPE_ID IS NULL
613 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,START_DATE);
614
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 X_DISPENSE_CONFIG := NULL;
618 END;
619
620 IF (X_DISPENSE_CONFIG.CONFIG_ID IS NOT NULL) THEN
621
622 --A valid dispense configuration has been found.
623 --Obtain the instruction defninition key and exit.
624 X_INSTRUCTION_DEFINITION_KEY := X_DISPENSE_CONFIG.CONFIG_ID;
625 RETURN;
626
627 END IF;
628
629 --Obtain the dispense configuration for the case where organization and recipe is null.
630 BEGIN
631 SELECT * INTO X_DISPENSE_CONFIG
632 FROM GMO_DISPENSE_CONFIG
633 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
634 AND DISPENSE_REQUIRED_FLAG = 'Y'
635 AND ORGANIZATION_ID IS NULL
636 AND RECIPE_ID IS NULL
637 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,START_DATE);
638
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641 X_DISPENSE_CONFIG := NULL;
642 END;
643
644 IF (X_DISPENSE_CONFIG.CONFIG_ID IS NOT NULL) THEN
645
646 --A valid dispense configuration has been found.
647 --Obtain the instruction defninition key and exit.
648 X_INSTRUCTION_DEFINITION_KEY := X_DISPENSE_CONFIG.CONFIG_ID;
649
650 END IF;
651
652 END GET_DISPENSE_CONFIG;
653
654
655 --This function is obtain the dispense UOM value for the specified item, organization and recipe.
656 FUNCTION GET_DISPENSE_UOM
657 (
658 P_INVENTORY_ITEM_ID IN NUMBER,
659 P_ORGANIZATION_ID IN NUMBER,
660 P_RECIPE_ID IN NUMBER
661 ) RETURN VARCHAR2
662
663 IS
664
665 --This variable would hold the dispense uom value.
666 L_RETURN_VALUE VARCHAR2(240);
667
668 --This variable would hold the dispense configuration details for the specified item, organization and recipe.
669 L_DISPENSE_CONFIG GMO_DISPENSE_CONFIG%ROWTYPE;
670
671 --This variable sould hold the instruction key.
672 L_INSTRUCTION_KEY VARCHAR2(240);
673 BEGIN
674
675 L_RETURN_VALUE := NULL;
676
677 --Obtain the dispense configuration details for the specified item, org and recipe.
678 GET_DISPENSE_CONFIG(P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
679 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
680 P_RECIPE_ID => P_RECIPE_ID,
681 X_DISPENSE_CONFIG => L_DISPENSE_CONFIG,
682 X_INSTRUCTION_DEFINITION_KEY => L_INSTRUCTION_KEY);
683
684 --If the dispense UOM value was found then set the same on the return value.
685 IF(L_DISPENSE_CONFIG.DISPENSE_UOM IS NOT NULL) THEN
686 L_RETURN_VALUE := L_DISPENSE_CONFIG.DISPENSE_UOM;
687 END IF;
688
689 --Return the dispense UOM value.
690 RETURN L_RETURN_VALUE;
691
692 END GET_DISPENSE_UOM;
693
694
695 --This function is used to verify if the specified UOM is convertible with the
696 --primary UOM value.
697 FUNCTION IS_CONV_WITH_PRIMARY_UOM
698 (
699 P_UOM VARCHAR2,
700 P_ITEM_ID NUMBER,
701 P_ORGANIZATION_ID NUMBER
702 ) RETURN VARCHAR2
703
704 IS
705
706 L_ITEM_UOM VARCHAR2(10);
707 L_RETURN_VALUE NUMBER;
708 BEGIN
709
710 SELECT PRIMARY_UOM_CODE INTO L_ITEM_UOM
711 FROM MTL_SYSTEM_ITEMS
712 WHERE INVENTORY_ITEM_ID = P_ITEM_ID
713 AND ORGANIZATION_ID = NVL(P_ORGANIZATION_ID, ORGANIZATION_ID)
714 AND ROWNUM =1;
715
716 IF P_ORGANIZATION_ID IS NOT NULL THEN
717
718 L_RETURN_VALUE := INV_CONVERT.INV_UM_CONVERT(P_ITEM_ID,
719 NULL,
720 P_ORGANIZATION_ID,
721 1,
722 1,
723 L_ITEM_UOM,
724 P_UOM,
725 NULL,
726 NULL);
727 ELSE
728 L_RETURN_VALUE := INV_CONVERT.INV_UM_CONVERT(P_ITEM_ID,
729 1,
730 1,
731 L_ITEM_UOM,
732 P_UOM,
733 NULL,
734 NULL);
735 END IF;
736
737 IF (L_RETURN_VALUE = -99999) THEN
738 RETURN FND_API.G_FALSE;
739 END IF;
740
741 RETURN FND_API.G_TRUE;
742
743 EXCEPTION WHEN OTHERS THEN
744 RETURN FND_API.G_FALSE;
745
746 END IS_CONV_WITH_PRIMARY_UOM;
747
748
749 --Yhis procedure is used check if dispense is required for the specified item,
750 --organization and recipe. If dispensing is required, it returns the corresponding
751 --dispense config ID that can be used to identify the dispense setup.
752 PROCEDURE IS_DISPENSE_ITEM
753 (
754 P_INVENTORY_ITEM_ID IN NUMBER,
755 P_ORGANIZATION_ID IN NUMBER,
756 P_RECIPE_ID IN NUMBER,
757 X_IS_DISPENSE_REQUIRED OUT NOCOPY VARCHAR2,
758 X_DISPENSE_CONFIG_ID OUT NOCOPY VARCHAR2
759 )
760
761 IS
762
763 BEGIN
764
765 --Obtain the value of the dispense required flag for the case where organization and recipe are specified.
766 BEGIN
767 SELECT DISPENSE_REQUIRED_FLAG,CONFIG_ID
768 INTO X_IS_DISPENSE_REQUIRED,X_DISPENSE_CONFIG_ID
769 FROM GMO_DISPENSE_CONFIG
770 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
771 AND ORGANIZATION_ID=P_ORGANIZATION_ID
772 AND RECIPE_ID=P_RECIPE_ID
773 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
774 EXCEPTION
775 WHEN NO_DATA_FOUND THEN
776 X_IS_DISPENSE_REQUIRED := 'N';
777 END;
778
779 IF (X_IS_DISPENSE_REQUIRED = 'Y' ) THEN
780
781 --If the flag is set to 'Y' then return.
782 RETURN;
783
784 END IF;
785
786 --Obtain the value of the dispense required flag for the case where organization is specified, but recipe is null.
787 BEGIN
788 SELECT DISPENSE_REQUIRED_FLAG,CONFIG_ID
789 INTO X_IS_DISPENSE_REQUIRED,X_DISPENSE_CONFIG_ID
790 FROM GMO_DISPENSE_CONFIG
791 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
792 AND ORGANIZATION_ID=P_ORGANIZATION_ID
793 AND RECIPE_ID IS NULL
794 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
795
796 EXCEPTION
797 WHEN NO_DATA_FOUND THEN
798 X_IS_DISPENSE_REQUIRED := 'N';
799 END;
800
801 IF X_IS_DISPENSE_REQUIRED = 'Y' THEN
802
803 --If the flag is set to 'Y' then return.
804 RETURN;
805 END IF;
806
807 --Obtain the value of the dispense required flag for the case where both organization and recipe are null.
808 BEGIN
809 SELECT DISPENSE_REQUIRED_FLAG,CONFIG_ID
810 INTO X_IS_DISPENSE_REQUIRED,X_DISPENSE_CONFIG_ID
811 FROM GMO_DISPENSE_CONFIG
812 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
813 AND ORGANIZATION_ID IS NULL
814 AND RECIPE_ID IS NULL
815 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE);
816
817 EXCEPTION
818 WHEN NO_DATA_FOUND THEN
819 X_IS_DISPENSE_REQUIRED := 'N';
820 END;
821
822 IF X_IS_DISPENSE_REQUIRED = 'N' THEN
823 X_DISPENSE_CONFIG_ID := NULL;
824 END IF;
825
826 END IS_DISPENSE_ITEM;
827
828
829 --This procedure is used to obtain the dispense configuration for the specified ENTITY_NAME and
830 --ENTITY_KEY from the instance tables.
831 PROCEDURE GET_DISPENSE_CONFIG_INST
832 (
833 P_ENTITY_NAME IN VARCHAR2,
834 P_ENTITY_KEY IN VARCHAR2,
835 X_DISPENSE_CONFIG OUT NOCOPY GMO_DISPENSE_CONFIG%ROWTYPE,
836 X_INSTRUCTION_DEFINITION_KEY OUT NOCOPY VARCHAR2
837 )
838 IS
839
840 L_DISPENSE_CONFIG_ID NUMBER;
841
842 BEGIN
843
844 X_INSTRUCTION_DEFINITION_KEY := NULL;
845
846 SELECT DISPENSE_CONFIG_ID INTO L_DISPENSE_CONFIG_ID
847 FROM GMO_DISPENSE_CONFIG_INST
848 WHERE ENTITY_NAME = P_ENTITY_NAME
849 AND ENTITY_KEY = P_ENTITY_KEY;
850
851 IF L_DISPENSE_CONFIG_ID IS NOT NULL THEN
852 SELECT * INTO X_DISPENSE_CONFIG FROM GMO_DISPENSE_CONFIG
853 WHERE CONFIG_ID = L_DISPENSE_CONFIG_ID;
854
855 IF (X_DISPENSE_CONFIG.CONFIG_ID IS NOT NULL) THEN
856
857 --A valid dispense configuration has been found.
858 --Obtain the instruction defninition key.
859 X_INSTRUCTION_DEFINITION_KEY := X_DISPENSE_CONFIG.CONFIG_ID;
860
861 END IF;
862 END IF;
863
864 EXCEPTION
865 WHEN NO_DATA_FOUND THEN
866 NULL;
870 --This procedure is used to instantiate the dispense setup identified by the specified
867 END GET_DISPENSE_CONFIG_INST;
868
869
871 --dispense config ID, entity name and entity key.
872 PROCEDURE INSTANTIATE_DISPENSE_SETUP
873 (P_DISPENSE_CONFIG_ID IN NUMBER,
874 P_ENTITY_NAME IN VARCHAR2,
875 P_ENTITY_KEY IN VARCHAR2,
876 P_INIT_MSG_LIST IN VARCHAR2,
877 P_AUTO_COMMIT IN VARCHAR2,
878 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
879 X_MSG_COUNT OUT NOCOPY NUMBER,
880 X_MSG_DATA OUT NOCOPY VARCHAR2)
881
882 IS
883
884 L_API_NAME CONSTANT VARCHAR2(40) := 'INSTANTIATE_DISPENSE_SETUP';
885
886
887 L_COUNT NUMBER;
888
889 GMO_NO_DISPENSE_CONFIG_ERR EXCEPTION;
890
891 BEGIN
892
893 --Initialize the message list if specified so.
894 IF FND_API.TO_BOOLEAN( P_INIT_MSG_LIST ) THEN
895
896 FND_MSG_PUB.INITIALIZE;
897
898 END IF;
899
900 IF P_AUTO_COMMIT = FND_API.G_TRUE THEN
901 INSTANTIATE_DISP_SETUP_AUTO
902 (P_DISPENSE_CONFIG_ID => P_DISPENSE_CONFIG_ID,
903 P_ENTITY_NAME => P_ENTITY_NAME,
904 P_ENTITY_KEY => P_ENTITY_KEY,
905 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
906 X_RETURN_STATUS => X_RETURN_STATUS,
907 X_MSG_COUNT => X_MSG_COUNT,
908 X_MSG_DATA => X_MSG_DATA);
909 ELSE
910 --Check if a dispense setup exists for the specified config ID.
911 SELECT COUNT(*) INTO L_COUNT
912 FROM GMO_DISPENSE_CONFIG
913 WHERE CONFIG_ID = P_DISPENSE_CONFIG_ID;
914
915 IF L_COUNT > 0 THEN
916 INSERT INTO GMO_DISPENSE_CONFIG_INST(INSTANCE_ID,
917 DISPENSE_CONFIG_ID,
918 ENTITY_NAME,
919 ENTITY_KEY,
920 CREATION_DATE,
921 CREATED_BY,
922 LAST_UPDATE_DATE,
923 LAST_UPDATED_BY,
924 LAST_UPDATE_LOGIN)
925
926 VALUES (GMO_DISPENSE_CONFIG_INST_S.NEXTVAL,
927 P_DISPENSE_CONFIG_ID,
928 P_ENTITY_NAME,
929 P_ENTITY_KEY,
930 SYSDATE,
931 FND_GLOBAL.USER_ID(),
932 SYSDATE,
933 FND_GLOBAL.LOGIN_ID(),
934 FND_GLOBAL.LOGIN_ID());
935 ELSE
936 RAISE GMO_NO_DISPENSE_CONFIG_ERR;
937 END IF;
938
939 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
940
941 END IF;
942
943 EXCEPTION
944 WHEN GMO_NO_DISPENSE_CONFIG_ERR THEN
945
946 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
947
948 FND_MESSAGE.SET_NAME('GMO','GMO_INVALID_DISPENSE_CONFIG_ID');
949 FND_MESSAGE.SET_TOKEN('CONFIG_ID',P_DISPENSE_CONFIG_ID);
950 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
951 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
952
953 FND_MSG_PUB.ADD;
954
955 FND_MSG_PUB.COUNT_AND_GET
956 (P_COUNT => X_MSG_COUNT,
957 P_DATA => X_MSG_DATA);
958
959 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
960 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
961 'gmo.plsql.GMO_DISPENSE_SETUP_PVT.INSTANTIATE_DISPENSE_SETUP',
962 FALSE);
963 END IF;
964
965 WHEN OTHERS THEN
966
967 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
968
969 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
970 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
971 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
972 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','INSTANTIATE_DISPENSE_SETUP');
973
974 FND_MSG_PUB.ADD;
975
976 IF FND_MSG_PUB.CHECK_MSG_LEVEL( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
977
978 FND_MSG_PUB.ADD_EXC_MSG (G_PKG_NAME,
979 L_API_NAME );
980
981 END IF;
982
983 FND_MSG_PUB.COUNT_AND_GET
984 (P_COUNT => X_MSG_COUNT,
985 P_DATA => X_MSG_DATA);
986
987 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
988 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
989 'gmo.plsql.GMO_DISPENSE_SETUP_PVT.INSTANTIATE_DISPENSE_SETUP',
990 FALSE);
991 END IF;
992
993 END INSTANTIATE_DISPENSE_SETUP;
994
995
996 --This procedure is used to instantiate the dispense setup identified by the specified
997 --dispense config ID, entity name and entity key. The transaction is committed autonomously.
998 PROCEDURE INSTANTIATE_DISP_SETUP_AUTO
999 (P_DISPENSE_CONFIG_ID IN NUMBER,
1000 P_ENTITY_NAME IN VARCHAR2,
1001 P_ENTITY_KEY IN VARCHAR2,
1002 P_INIT_MSG_LIST IN VARCHAR2,
1003 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1004 X_MSG_COUNT OUT NOCOPY NUMBER,
1005 X_MSG_DATA OUT NOCOPY VARCHAR2)
1006
1007 IS
1008
1009 L_API_NAME CONSTANT VARCHAR2(40) := 'INSTANTIATE_DISPENSE_SETUP';
1010
1011
1012 L_COUNT NUMBER;
1013
1014 GMO_NO_DISPENSE_CONFIG_ERR EXCEPTION;
1015
1016 PRAGMA AUTONOMOUS_TRANSACTION;
1017
1018 BEGIN
1019
1020 --Initialize the message list if specified so.
1021 IF FND_API.TO_BOOLEAN( P_INIT_MSG_LIST ) THEN
1022
1023 FND_MSG_PUB.INITIALIZE;
1024
1025 END IF;
1026
1027 --Check if a dispense setup exists for the specified config ID.
1028 SELECT COUNT(*) INTO L_COUNT
1029 FROM GMO_DISPENSE_CONFIG
1030 WHERE CONFIG_ID = P_DISPENSE_CONFIG_ID;
1031
1032 IF L_COUNT > 0 THEN
1033 INSERT INTO GMO_DISPENSE_CONFIG_INST(INSTANCE_ID,
1034 DISPENSE_CONFIG_ID,
1035 ENTITY_NAME,
1036 ENTITY_KEY,
1037 CREATION_DATE,
1038 CREATED_BY,
1039 LAST_UPDATE_DATE,
1040 LAST_UPDATED_BY,
1041 LAST_UPDATE_LOGIN)
1042
1043 VALUES (GMO_DISPENSE_CONFIG_INST_S.NEXTVAL,
1044 P_DISPENSE_CONFIG_ID,
1045 P_ENTITY_NAME,
1046 P_ENTITY_KEY,
1047 SYSDATE,
1048 FND_GLOBAL.USER_ID(),
1049 SYSDATE,
1050 FND_GLOBAL.LOGIN_ID(),
1051 FND_GLOBAL.LOGIN_ID());
1052 ELSE
1053 RAISE GMO_NO_DISPENSE_CONFIG_ERR;
1054 END IF;
1055
1056 COMMIT;
1057
1058 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1059
1060 EXCEPTION
1061 WHEN GMO_NO_DISPENSE_CONFIG_ERR THEN
1062 ROLLBACK;
1063
1064 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1065
1066 FND_MESSAGE.SET_NAME('GMO','GMO_INVALID_DISPENSE_CONFIG_ID');
1067 FND_MESSAGE.SET_TOKEN('CONFIG_ID',P_DISPENSE_CONFIG_ID);
1068 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1069 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',P_ENTITY_NAME);
1070
1071 FND_MSG_PUB.ADD;
1072
1073 FND_MSG_PUB.COUNT_AND_GET
1074 (P_COUNT => X_MSG_COUNT,
1075 P_DATA => X_MSG_DATA);
1076
1077 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1079 'gmo.plsql.GMO_DISPENSE_SETUP_PVT.INSTANTIATE_DISP_SETUP',
1080 FALSE);
1081 END IF;
1082
1083 WHEN OTHERS THEN
1084 ROLLBACK;
1085
1086 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
1087
1088 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1089 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
1090 FND_MESSAGE.SET_TOKEN('PKG_NAME','GMO_DISPENSE_SETUP_PVT');
1091 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','INSTANTIATE_DISP_SETUP');
1092
1093 FND_MSG_PUB.ADD;
1094
1095 IF FND_MSG_PUB.CHECK_MSG_LEVEL( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1096
1097 FND_MSG_PUB.ADD_EXC_MSG (G_PKG_NAME,
1098 L_API_NAME );
1099
1100 END IF;
1101
1102 FND_MSG_PUB.COUNT_AND_GET
1103 (P_COUNT => X_MSG_COUNT,
1104 P_DATA => X_MSG_DATA);
1105
1106 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1107 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1108 'gmo.plsql.GMO_DISPENSE_SETUP_PVT.INSTANTIATE_DISP_SETUP',
1109 FALSE);
1110 END IF;
1111
1112 END INSTANTIATE_DISP_SETUP_AUTO;
1113
1114 --This function return the dispense setup status based on the specified start and end date values.
1115 --The status value returned is one of possible lookup code values contained in the lookup type
1116 --GMO_DISP_SETUP_STATUS.
1117 FUNCTION GET_SETUP_STATUS(P_START_DATE DATE,
1118 P_END_DATE DATE)
1119
1120 RETURN VARCHAR2
1121
1122 IS
1123
1124 BEGIN
1125
1126 IF SYSDATE BETWEEN P_START_DATE AND NVL(P_END_DATE,SYSDATE) OR P_START_DATE > SYSDATE THEN
1127 RETURN 'N';
1128 ELSE
1129 RETURN 'P';
1130 END IF;
1131
1132 END GET_SETUP_STATUS;
1133
1134 --This function checks if the the difference between the specified dates is atleast
1135 --two seconds. Based on this condition it returns FND_API.G_TRUE or FND_API.G_FALSE.
1136 FUNCTION IS_DATE_DIFF_SUFFICIENT(P_FIRST_DATE DATE,
1137 P_SECOND_DATE DATE)
1138
1139 RETURN VARCHAR2
1140
1141 IS
1142
1143 L_DIFF NUMBER;
1144
1145 BEGIN
1146
1147 IF P_FIRST_DATE IS NOT NULL AND P_SECOND_DATE IS NOT NULL THEN
1148 L_DIFF := P_SECOND_DATE - P_FIRST_DATE;
1149
1150 IF L_DIFF > 1/86400 THEN
1151 RETURN FND_API.G_TRUE;
1152 ELSE
1153 RETURN FND_API.G_FALSE;
1154 END IF;
1155 ELSE
1156 RETURN FND_API.G_TRUE;
1157 END IF;
1158
1159 END IS_DATE_DIFF_SUFFICIENT;
1160
1161
1162 --This function subtracts the specified number of seconds from the date provided.
1163 FUNCTION SUBTRACT_SECONDS_FROM_DATE(P_DATE DATE,
1164 P_SECONDS NUMBER)
1165
1166 RETURN DATE
1167
1168 IS
1169
1170 L_DATE DATE;
1171
1172 BEGIN
1173
1174 IF P_DATE IS NOT NULL THEN
1175
1176 L_DATE := P_DATE;
1177
1178 L_DATE := L_DATE - P_SECONDS/86400;
1179 ELSE
1180 L_DATE := NULL;
1181 END IF;
1182
1183 RETURN L_DATE;
1184 END SUBTRACT_SECONDS_FROM_DATE;
1185
1186 --This function adds the specified number of seconds from the date provided.
1187 FUNCTION ADD_SECONDS_TO_DATE(P_DATE DATE,
1188 P_SECONDS NUMBER)
1189
1190 RETURN DATE
1191
1192 IS
1193
1194 L_DATE DATE;
1195
1196 BEGIN
1197
1198 IF P_DATE IS NOT NULL THEN
1199
1200 L_DATE := P_DATE;
1201
1202 L_DATE := L_DATE + P_SECONDS/86400;
1203 ELSE
1204 L_DATE := NULL;
1205 END IF;
1206
1207 RETURN L_DATE;
1208 END ADD_SECONDS_TO_DATE;
1209
1210
1211 END GMO_DISPENSE_SETUP_PVT;
[Home] [Help]