[Home] [Help]
PACKAGE BODY: APPS.GMO_PREFERENCES_PVT
Source
1 PACKAGE BODY gmo_preferences_pvt AS
2 /* $Header: GMOVPRFB.pls 120.2 2011/02/24 12:29:38 srpuri ship $ */
3 G_ALL CONSTANT VARCHAR2(3) := 'ALL';
4 G_DISPENSE CONSTANT VARCHAR2(20) := 'DISPENSING';
5 G_REVERSE_DISPENSE CONSTANT VARCHAR2(20) := 'REVERSE_DISPENSE';
6 G_OWB CONSTANT VARCHAR2(3) := 'OWB';
7
8 PROCEDURE GET_APPLICABLE_PREFERENCE(P_RESPONSIBILITY_ID IN NUMBER,
9 P_USER_ID IN NUMBER,
10 P_ORGANIZATION_ID IN NUMBER,
11 P_MODULE_NAME IN VARCHAR2,
12 X_TIME_RANGE OUT NOCOPY VARCHAR2,
13 X_NO_OF_DAYS OUT NOCOPY NUMBER,
14 X_ROLLING_FLAG OUT NOCOPY VARCHAR2,
15 X_ENFORCE_CERTIFICATE_FLAG OUT NOCOPY VARCHAR2,
16 X_DISPENSE_AREA OUT NOCOPY VARCHAR2,
17 X_DISPENSE_ORGANIZATION OUT NOCOPY NUMBER,
18 X_DISPENSE_BOOTH OUT NOCOPY VARCHAR2,
19 X_DISPENSE_MODE OUT NOCOPY VARCHAR2,
20 X_PRINT_PALLET_LABEL_FLAG OUT NOCOPY VARCHAR2,
21 X_PRINT_MTL_LABEL_FLAG OUT NOCOPY VARCHAR2,
22 X_PRINT_DSP_LABEL_FLAG OUT NOCOPY VARCHAR2,
23 X_DEFAULT_DEVICE OUT NOCOPY NUMBER,
24 X_DEFAULT_SOURCE_DEVICE OUT NOCOPY NUMBER,
25 X_DEFAULT_TARGET_DEVICE OUT NOCOPY NUMBER,
26 X_DEFAULT_RESOURCE OUT NOCOPY VARCHAR2
27 ) IS
28
29 CURSOR C_GET_PREFERENCE_ID_FOR_SITE IS
30 SELECT *
31 FROM GMO_KIOSK_PREFERENCES
32 WHERE LEVEL_VALUE = 'Y'
33 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
34
35 CURSOR C_GET_PREFERENCE_ID_FOR_RESP IS
36 SELECT *
37 FROM GMO_KIOSK_PREFERENCES
38 WHERE LEVEL_CODE = 'RESPONSIBILITY'
39 AND ORGANIZATION_ID = P_ORGANIZATION_ID
40 AND LEVEL_VALUE = P_RESPONSIBILITY_ID;
41
42
43 CURSOR C_GET_PREFERENCE_ID_FOR_USER IS
44 SELECT *
45 FROM GMO_KIOSK_PREFERENCES
46 WHERE LEVEL_CODE = 'USER'
47 AND ORGANIZATION_ID = P_ORGANIZATION_ID
48 AND LEVEL_VALUE = P_USER_ID;
49
50
51 CURSOR C_GET_DISPENSE_PREFERENCES (P_PREFERENCE_ID NUMBER) IS
52 SELECT *
53 FROM GMO_KIOSK_DISP_PREFERENCES
54 WHERE PREFERENCES_ID = P_PREFERENCE_ID;
55
56 CURSOR C_GET_OWB_PREFERENCES (P_PREFERENCE_ID NUMBER) IS
57 SELECT *
58 FROM GMO_KIOSK_OWB_PREFERENCES
59 WHERE PREFERENCES_ID = P_PREFERENCE_ID;
60
61 CURSOR C_GET_REV_DISPENSE_PREFERENCES (P_PREFERENCE_ID NUMBER) IS
62 SELECT *
63 FROM GMO_KIOSK_REV_DISP_PREFERENCES
64 WHERE PREFERENCES_ID = P_PREFERENCE_ID;
65
66 l_preference_row GMO_KIOSK_PREFERENCES%ROWTYPE;
67 l_dispense_preferences GMO_KIOSK_DISP_PREFERENCES%ROWTYPE;
68 l_owb_preferences GMO_KIOSK_OWB_PREFERENCES%ROWTYPE;
69 l_reverse_dispense_pref GMO_KIOSK_REV_DISP_PREFERENCES%ROWTYPE;
70 l_preferences_id number;
71
72 BEGIN
73
74 /* Identify and select the preferences defined at the most restrictive levels
75 * first. Site -> Responsibility -> Organization -> User.
76 */
77
78 OPEN C_GET_PREFERENCE_ID_FOR_USER;
79 FETCH C_GET_PREFERENCE_ID_FOR_USER INTO l_preference_row;
80 CLOSE C_GET_PREFERENCE_ID_FOR_USER;
81
82 IF (l_preference_row.preferences_id is null) THEN
83 BEGIN
84 OPEN C_GET_PREFERENCE_ID_FOR_RESP;
85 FETCH C_GET_PREFERENCE_ID_FOR_RESP INTO l_preference_row;
86 CLOSE C_GET_PREFERENCE_ID_FOR_RESP;
87
88 IF ( l_preference_row.preferences_id is null ) THEN
89 BEGIN
90 OPEN C_GET_PREFERENCE_ID_FOR_SITE;
91 FETCH C_GET_PREFERENCE_ID_FOR_SITE INTO l_preference_row;
92 CLOSE C_GET_PREFERENCE_ID_FOR_SITE;
93
94 IF ( l_preference_row.preferences_id is null ) THEN
95 RETURN;
96 END IF; -- End of If site level
97 END;
98 END IF; -- End of if resp level
99 END;
100 END IF; -- End of IF user level
101
102 /* Now we have the valid preference master row, just query the child tables and populate
103 * the out parameters.*/
104
105 IF(P_MODULE_NAME = G_DISPENSE) THEN
106 BEGIN
107 OPEN C_GET_DISPENSE_PREFERENCES(l_preference_row.preferences_id);
108 FETCH C_GET_DISPENSE_PREFERENCES INTO l_dispense_preferences;
109 CLOSE C_GET_DISPENSE_PREFERENCES;
110
111 X_TIME_RANGE := l_dispense_preferences.TIME_RANGE;
112 X_NO_OF_DAYS := l_dispense_preferences.NO_OF_DAYS;
113 X_ROLLING_FLAG := l_dispense_preferences.ROLLING_FLAG;
114 IF(l_preference_row.ENFORCE_CERTIFICATE_FLAG = 'N' or l_preference_row.ENFORCE_CERTIFICATE_FLAG is null ) THEN
115 X_ENFORCE_CERTIFICATE_FLAG := 'N';
116 ELSE
117 X_ENFORCE_CERTIFICATE_FLAG := l_dispense_preferences.OPERATOR_CERTIFICATE_FLAG;
118 END IF;
119 X_DISPENSE_AREA := l_dispense_preferences.DISPENSE_AREA;
120 X_DISPENSE_ORGANIZATION := l_dispense_preferences.DISPENSE_ORGANIZATION;
121 X_DISPENSE_BOOTH := l_dispense_preferences.DISPENSE_BOOTH;
122 X_DISPENSE_MODE := l_dispense_preferences.DISPENSE_MODE;
123 X_PRINT_PALLET_LABEL_FLAG := l_dispense_preferences.PALLET_LABEL_FLAG;
124 X_PRINT_MTL_LABEL_FLAG := l_dispense_preferences.MATERIAL_LABEL_FLAG;
125 X_PRINT_DSP_LABEL_FLAG := l_dispense_preferences.DISPENSE_LABEL_FLAG;
126 X_DEFAULT_DEVICE := l_dispense_preferences.DEFAULT_DEVICE_ID;
127 X_DEFAULT_SOURCE_DEVICE := l_dispense_preferences.DEFAULT_SOURCE_DEVICE_ID;
128 X_DEFAULT_TARGET_DEVICE := l_dispense_preferences.DEFAULT_TARGET_DEVICE_ID;
129 END;
130 ELSIF (P_MODULE_NAME = G_REVERSE_DISPENSE) THEN
131 BEGIN
132 OPEN C_GET_REV_DISPENSE_PREFERENCES(l_preference_row.preferences_id);
133 FETCH C_GET_REV_DISPENSE_PREFERENCES INTO l_reverse_dispense_pref;
134 CLOSE C_GET_REV_DISPENSE_PREFERENCES;
135 X_TIME_RANGE := l_reverse_dispense_pref.TIME_RANGE;
136 X_NO_OF_DAYS := l_reverse_dispense_pref.NO_OF_DAYS;
137 X_ROLLING_FLAG := l_reverse_dispense_pref.ROLLING_FLAG;
138 IF(l_preference_row.ENFORCE_CERTIFICATE_FLAG = 'N' or l_preference_row.ENFORCE_CERTIFICATE_FLAG is null ) THEN
139 X_ENFORCE_CERTIFICATE_FLAG := 'N';
140 ELSE
141 X_ENFORCE_CERTIFICATE_FLAG := l_reverse_dispense_pref.OPERATOR_CERTIFICATE_FLAG;
142 END IF;
143 X_DISPENSE_AREA := l_reverse_dispense_pref.DISPENSE_AREA;
144 X_DISPENSE_ORGANIZATION := l_reverse_dispense_pref.DISPENSE_ORGANIZATION;
145 X_DISPENSE_BOOTH := l_reverse_dispense_pref.DISPENSE_BOOTH;
146 X_DISPENSE_MODE := l_reverse_dispense_pref.DISPENSE_MODE;
147 X_PRINT_MTL_LABEL_FLAG := l_reverse_dispense_pref.MATERIAL_LABEL_FLAG;
148 X_PRINT_DSP_LABEL_FLAG := l_reverse_dispense_pref.DISPENSE_LABEL_FLAG;
149 X_DEFAULT_DEVICE := l_reverse_dispense_pref.DEFAULT_DEVICE_ID;
150 X_DEFAULT_SOURCE_DEVICE := l_reverse_dispense_pref.DEFAULT_SOURCE_DEVICE_ID;
151 X_DEFAULT_TARGET_DEVICE := l_reverse_dispense_pref.DEFAULT_TARGET_DEVICE_ID;
152 END;
153 ELSE
154 BEGIN
155 OPEN C_GET_OWB_PREFERENCES(l_preference_row.preferences_id);
156 FETCH C_GET_OWB_PREFERENCES INTO l_owb_preferences;
157 CLOSE C_GET_OWB_PREFERENCES;
158 X_TIME_RANGE := l_owb_preferences.TIME_RANGE;
159 X_NO_OF_DAYS := l_owb_preferences.NO_OF_DAYS;
160 X_ROLLING_FLAG := l_owb_preferences.ROLLING_FLAG;
161 IF(l_preference_row.ENFORCE_CERTIFICATE_FLAG = 'N' or l_preference_row.ENFORCE_CERTIFICATE_FLAG is null ) THEN
162 X_ENFORCE_CERTIFICATE_FLAG := 'N';
163 ELSE
164 X_ENFORCE_CERTIFICATE_FLAG := l_owb_preferences.OPERATOR_CERTIFICATE_FLAG;
165 END IF;
166 X_DEFAULT_RESOURCE := l_owb_preferences.DEFAULT_RESOURCE;
167 END;
168 END IF;
169
170 END GET_APPLICABLE_PREFERENCE;
171
172
173 end gmo_preferences_pvt;