[Home] [Help]
PACKAGE BODY: APPS.EAM_WL_UTIL_PKG
Source
1 PACKAGE BODY EAM_WL_UTIL_PKG as
2 /* $Header: EAMWLUTB.pls 120.1 2005/07/12 07:59:16 mkishore noship $ */
3
4
5 FUNCTION get_item_description( p_item_id NUMBER, p_organization_id NUMBER)
6 return VARCHAR2 IS
7 l_description MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE ;
8 CURSOR get_description IS
9 SELECT msi.description
10 FROM mtl_system_items msi, mtl_parameters mp
11 WHERE msi.inventory_item_id = p_item_id
12 AND msi.organization_id = mp.organization_id
13 AND mp.maint_organization_id = p_organization_id ;
14 BEGIN
15 OPEN get_description ;
16 FETCH get_description INTO l_description ;
17 CLOSE get_description;
18
19 RETURN l_description;
20 END get_item_description;
21
22 FUNCTION get_concatenated_segments( p_item_id NUMBER, p_organization_id NUMBER) RETURN VARCHAR2
23 IS
24 CURSOR get_value IS
25 SELECT msi.concatenated_segments
26 FROM mtl_system_items_kfv msi, mtl_parameters mp
27 WHERE msi.inventory_item_id = p_item_id
28 AND msi.organization_id = mp.organization_id
29 AND mp.maint_organization_id = p_organization_id ;
30
31 l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
32 BEGIN
33 OPEN get_value;
34 FETCH get_value INTO l_concatenated_segments;
35 CLOSE get_value;
36
37 RETURN l_concatenated_segments;
38 END get_concatenated_segments;
39
40 FUNCTION get_translatable_value(p_lookup_code NUMBER, p_lookup_type VARCHAR2) RETURN VARCHAR2
41 IS
42 CURSOR get_value IS
43 SELECT meaning
44 FROM mfg_lookups
45 WHERE lookup_code = p_lookup_code
46 AND lookup_type = p_lookup_type;
47
48 l_meaning mfg_lookups.meaning%TYPE;
49 BEGIN
50 OPEN get_value;
51 FETCH get_value INTO l_meaning;
52 CLOSE get_value;
53
54 RETURN l_meaning;
55 END get_translatable_value;
56
57 FUNCTION get_department_code(p_department_id NUMBER, p_organization_id NUMBER ) RETURN VARCHAR2
58 IS
59 CURSOR get_value IS
60 SELECT department_code
61 FROM bom_departments
62 WHERE organization_id = p_organization_id
63 AND department_id = p_department_id;
64
65 l_department_code bom_departments.department_code%TYPE;
66 BEGIN
67 OPEN get_value;
68 FETCH get_value INTO l_department_code;
69 CLOSE get_value;
70
71 RETURN l_department_code;
72 END get_department_code;
73
74 FUNCTION get_wip_entity_name( p_wip_entity_id NUMBER) RETURN VARCHAR2
75 IS
76 CURSOR get_value IS
77 SELECT wip_entity_name
78 FROM wip_entities
79 WHERE wip_entity_id = p_wip_entity_id;
80
81 l_description wip_entities.wip_entity_name%TYPE;
82 BEGIN
83 OPEN get_value;
84 FETCH get_value INTO l_description;
85 CLOSE get_value;
86
87 RETURN l_description;
88 END get_wip_entity_name;
89
90 FUNCTION get_wo_status( p_wip_entity_id NUMBER) RETURN VARCHAR2
91 IS
92 CURSOR get_value IS
93 SELECT status_type
94 FROM wip_discrete_jobs
95 WHERE wip_entity_id = p_wip_entity_id;
96
97 l_status_type wip_discrete_jobs.status_type%TYPE;
98 BEGIN
99 OPEN get_value;
100 FETCH get_value INTO l_status_type;
101 CLOSE get_value;
102
103 RETURN l_status_type;
104 END get_wo_status;
105
106 FUNCTION Is_Stock_Enable( p_inventory_item_id NUMBER, p_organization_id NUMBER) RETURN VARCHAR2
107 IS
108 CURSOR get_value IS
109 SELECT NVL(stock_enabled_flag,'N')
110 FROM mtl_system_items msi, mtl_parameters mp
111 WHERE msi.inventory_item_id = p_inventory_item_id
112 AND msi.organization_id = mp.organization_id
113 AND mp.maint_organization_id = p_organization_id ;
114
115 l_flag mtl_system_items.stock_enabled_flag%TYPE;
116 BEGIN
117 OPEN get_value;
118 FETCH get_value INTO l_flag;
119 CLOSE get_value;
120
121 RETURN l_flag;
122
123 END Is_Stock_Enable;
124
125
126
127 FUNCTION get_serial_description( p_serial_number VARCHAR2, p_item_id NUMBER, p_organization_id NUMBER) RETURN VARCHAR2
128 IS
129 CURSOR get_value IS
130 SELECT cii.instance_description
131 FROM csi_item_instances cii, mtl_parameters mp
132 WHERE cii.serial_number = p_serial_number
133 AND cii.inventory_item_id = p_item_id
134 AND cii.last_vld_organization_id = mp.organization_id
135 AND mp.maint_organization_id = p_organization_id;
136
137 l_description mtl_serial_numbers.descriptive_text%TYPE;
138 BEGIN
139 OPEN get_value;
140 FETCH get_value INTO l_description;
141 CLOSE get_value;
142
143 RETURN l_description;
144 END get_serial_description;
145
146 FUNCTION get_instance_count( p_operation_seq_num NUMBER,
147 p_wip_entity_id NUMBER,
148 p_organization_id NUMBER,
149 p_resource_type NUMBER) RETURN VARCHAR2
150 IS
151 CURSOR get_count IS
152 SELECT count(*)
153 FROM wip_op_resource_instances wori,
154 wip_operation_resources wor,
155 bom_resources br
156 WHERE wor.wip_entity_id = wori.wip_entity_id
157 AND wor.organization_id = wori.organization_id
158 AND wor.operation_seq_num = wori.operation_seq_num
159 AND wor.resource_seq_num = wori.resource_seq_num
160 AND br.resource_id = wor.resource_id
161 AND br.resource_type = p_resource_type -- (1=Man)/(2=Machine)
162 AND wori.operation_seq_num = p_operation_seq_num
163 AND wori.wip_entity_id = p_wip_entity_id
164 AND wori.organization_id = p_organization_id;
165
166 l_count NUMBER;
167 BEGIN
168 OPEN get_count;
169 FETCH get_count INTO l_count;
170 CLOSE get_count;
171
172 RETURN l_count;
173 END get_instance_count;
174
175 FUNCTION get_wip_job_sequence return NUMBER IS
176 l_job_sequence_number NUMBER ;
177 BEGIN
178 SELECT wip_job_number_s.nextval
179 INTO l_job_sequence_number
180 FROM DUAL;
181
182 RETURN l_job_sequence_number;
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 RETURN -1;
186 END get_wip_job_sequence;
187
188 FUNCTION isESignatureRequired( p_transaction_name IN VARCHAR2) return VARCHAR2
189 IS
190 l_return_status VARCHAR2(255);
191 l_msg_count NUMBER;
192 l_msg_data VARCHAR2(2000);
193 l_intputvar_values_tbl EDR_STANDARD_PUB.InputVar_Values_tbl_type;
194 BEGIN
195 IF nvl(fnd_profile.value('EDR_ERES_ENABLED'), 'N') = 'N' THEN
196 RETURN 'N';
197 END IF;
198
199 EDR_STANDARD_PUB.GET_AMERULE_VARIABLEVALUES (
200 p_api_version => 1.0,
201 p_init_msg_list => FND_API.G_FALSE,
202 x_return_status => l_return_status,
203 x_msg_count => l_msg_count,
204 x_msg_data => l_msg_data,
205 p_transaction_id => p_transaction_name,
206 p_ameRule_id => -1,
207 p_ameRule_name => NULL,
208 x_inputVar_values_tbl => l_intputvar_values_tbl );
209
210 IF l_return_status = 'S' THEN
211 FOR i IN l_intputvar_values_tbl.FIRST .. l_intputvar_values_tbl.LAST LOOP
212 IF l_intputvar_values_tbl(i).input_name = 'ESIG_REQUIRED' THEN
213 IF l_intputvar_values_tbl(i).input_value = 'Y' THEN
214 RETURN 'Y';
215 END IF;
216 END IF;
217 END LOOP;
218 END IF;
219 RETURN 'N';
220 END isESignatureRequired;
221
222 END EAM_WL_UTIL_PKG;