[Home] [Help]
PACKAGE BODY: APPS.AHL_UTIL_PC_PKG
Source
1 package body AHL_UTIL_PC_PKG as
2 /* $Header: AHLUPCXB.pls 120.3.12000000.2 2007/08/17 14:37:23 sathapli ship $ */
3
4 FUNCTION get_fmp_pc_node
5 (
6 p_pc_node_id IN NUMBER:= NULL,
7 p_inventory_id IN NUMBER:= NULL
8 )
9 RETURN BOOLEAN
10 IS
11
12 CURSOR get_pc_asso
13 IS
14 SELECT 'X'
15 FROM ahl_pc_associations
16 WHERE pc_node_id IN (
17 SELECT pc_node_id
18 FROM ahl_pc_nodes_b
19 START WITH pc_node_id = p_pc_node_id
20 CONNECT BY parent_node_id = PRIOR pc_node_id )
21 AND unit_item_id = p_inventory_id;
22
23 l_dummy VARCHAR2(100);
24
25 BEGIN
26
27 OPEN get_pc_asso;
28 FETCH get_pc_asso INTO l_dummy;
29 IF get_pc_asso%FOUND THEN
30 CLOSE get_pc_asso;
31 RETURN TRUE;
32 ELSE
33 CLOSE get_pc_asso;
34 RETURN FALSE;
35 END IF;
36
37 END get_fmp_pc_node;
38
39
40 FUNCTION get_uc_node
41 (
42 p_pc_node_id IN NUMBER := NULL,
43 p_Item_Instance_ID IN NUMBER := NULL
44 )
45 RETURN BOOLEAN
46 IS
47
48 CURSOR get_uc_asso
49 IS
50 -- SAGARWAL::Bug# 5246104 SQL Id: 17237425
51 /*
52 SELECT 'X'
53 FROM ahl_pc_associations
54 WHERE pc_node_id IN (
55 SELECT pc_node_id
56 FROM ahl_pc_nodes_b
57 CONNECT BY parent_node_id = PRIOR pc_node_id
58 START WITH pc_node_id = p_pc_node_id)
59 AND unit_item_id IN (
60 SELECT Unit_config_header_ID
61 FROM ahl_unit_header_details_v
62 WHERE Csi_Item_Instance_ID = p_Item_Instance_ID)
63 AND association_type_flag='U';
64 */
65 SELECT 'X'
66 FROM ahl_pc_associations
67 WHERE pc_node_id IN (
68 SELECT pc_node_id
69 FROM ahl_pc_nodes_b
70 CONNECT BY parent_node_id = PRIOR pc_node_id
71 START WITH pc_node_id = p_pc_node_id)
72 AND unit_item_id IN (
73 SELECT Unit_config_header_ID
74 FROM ahl_unit_config_headers
75 WHERE Csi_Item_Instance_ID = p_Item_Instance_ID
76 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
77 AND association_type_flag='U';
78
79 CURSOR get_pc_asso
80 IS
81 -- SAGARWAL::Bug# 5246104 SQL Id: 17237441
82 /*
83 SELECT 'X'
84 FROM ahl_pc_associations
85 WHERE pc_node_id IN (
86 SELECT pc_node_id
87 FROM ahl_pc_nodes_b
88 CONNECT BY parent_node_id = PRIOR pc_node_id
89 START WITH pc_node_id = p_pc_node_id)
90 AND unit_item_id in (
91 SELECT inventory_item_id
92 FROM csi_instance_details_v
93 WHERE Instance_ID = p_Item_Instance_ID)
94 AND association_type_flag='P';
95 */
96 SELECT 'X'
97 FROM ahl_pc_associations
98 WHERE pc_node_id IN (
99 SELECT pc_node_id
100 FROM ahl_pc_nodes_b
101 CONNECT BY parent_node_id = PRIOR pc_node_id
102 START WITH pc_node_id = p_pc_node_id)
103 AND unit_item_id in (
104 SELECT inventory_item_id
105 FROM csi_item_instances
106 WHERE Instance_ID = p_Item_Instance_ID)
107 AND association_type_flag='P';
108
109 l_dummy VARCHAR2(100);
110
111 BEGIN
112
113 OPEN get_uc_asso;
114 FETCH get_uc_asso INTO l_dummy;
115 IF get_uc_asso%FOUND THEN
116 CLOSE get_uc_asso;
117 RETURN TRUE;
118 ELSE
119 CLOSE get_pc_asso;
120 OPEN get_pc_asso;
121 FETCH get_pc_asso INTO l_dummy;
122 IF get_pc_asso%FOUND THEN
123 CLOSE get_pc_asso;
124 RETURN TRUE;
125 ELSE
126 CLOSE get_pc_asso;
127 RETURN FALSE;
128 END IF;
129 END IF;
130
131 END get_uc_node;
132
133 FUNCTION is_pc_complete
134 (
135 p_pc_header_id IN NUMBER
136 )
137 RETURN NUMBER
138 IS
139
140 CURSOR get_pc_details
141 IS
142 select primary_flag, status, association_type_flag
143 from ahl_pc_headers_b
144 where pc_header_id = p_pc_header_id;
145
146 CURSOR check_unassigned_parts
147 IS
148 -- SATHAPLI::Bug# 5246104 SQL Id: 17237465
149 /*
150 SELECT DISTINCT
151 MTL.INVENTORY_ITEM_ID
152 FROM
153 MTL_SYSTEM_ITEMS_KFV MTL,
154 MTL_ITEM_STATUS STAT,
155 AHL_PC_HEADERS_VL HEADER
156 WHERE
157 STAT.INVENTORY_ITEM_STATUS_CODE = MTL.INVENTORY_ITEM_STATUS_CODE AND
158 MTL.ITEM_TYPE = HEADER.PRODUCT_TYPE_CODE AND
159 MTL.ORGANIZATION_ID = FND_PROFILE.VALUE('ORG_ID') AND
160 HEADER.PC_HEADER_ID = p_pc_header_id AND
161 MTL.INVENTORY_ITEM_STATUS_CODE NOT IN ('Obsolete','Inactive') AND
162 TRUNC(SYSDATE) BETWEEN NVL(TRUNC(MTL.START_DATE_ACTIVE), TRUNC(SYSDATE)) AND
163 NVL(TRUNC(MTL.END_DATE_ACTIVE), TRUNC(SYSDATE))
164 MINUS
165 SELECT DISTINCT
166 AHASS.UNIT_ITEM_ID
167 FROM
168 AHL_PC_ASSOCIATIONS_V AHASS,
169 AHL_PC_NODES_B NODE
170 WHERE
171 AHASS.ASSOCIATION_TYPE_FLAG = 'I' AND
172 AHASS.PC_NODE_ID = NODE.PC_NODE_ID AND
173 NODE.PC_HEADER_ID = p_pc_header_id;
174 */
175 SELECT DISTINCT
176 MTL.INVENTORY_ITEM_ID
177 FROM
178 MTL_SYSTEM_ITEMS_KFV MTL,
179 MTL_ITEM_STATUS STAT,
180 AHL_PC_HEADERS_B HEADER
181 WHERE
182 STAT.INVENTORY_ITEM_STATUS_CODE = MTL.INVENTORY_ITEM_STATUS_CODE AND
183 MTL.ITEM_TYPE = HEADER.PRODUCT_TYPE_CODE AND
184 -- SATHAPLI::Bug# 5576835, 17-Aug-2007
185 /*
186 MTL.ORGANIZATION_ID = FND_PROFILE.VALUE('ORG_ID') AND
187 */
188 MTL.ORGANIZATION_ID IN (SELECT DISTINCT MASTER_ORGANIZATION_ID FROM MTL_PARAMETERS) AND
189 HEADER.PC_HEADER_ID = p_pc_header_id AND
190 MTL.INVENTORY_ITEM_STATUS_CODE NOT IN ('Obsolete','Inactive') AND
191 TRUNC(SYSDATE) BETWEEN NVL(TRUNC(MTL.START_DATE_ACTIVE), TRUNC(SYSDATE)) AND
192 NVL(TRUNC(MTL.END_DATE_ACTIVE), TRUNC(SYSDATE))
193 MINUS
194 SELECT DISTINCT
195 AHS.UNIT_ITEM_ID
196 FROM
197 AHL_PC_ASSOCIATIONS AHS,
198 AHL_PC_NODES_B NODE
199 WHERE
200 AHS.ASSOCIATION_TYPE_FLAG = 'I' AND
201 AHS.PC_NODE_ID = NODE.PC_NODE_ID AND
202 NODE.PC_HEADER_ID = p_pc_header_id;
203
204 -- ACL - PC Changes :: Bug 4684690
205 -- Modified cursor check_unassigned_units to Include Quarantine and Deactivate Quarantine Status
206 CURSOR check_unassigned_units
207 IS
208 -- SATHAPLI::Bug# 5246104 SQL Id: 17237482
209 /*
210 SELECT DISTINCT
211 UNIT.UNIT_CONFIG_HEADER_ID
212 FROM
213 AHL_UNIT_CONFIG_HEADERS UNIT,
214 CSI_ITEM_INSTANCES CSI,
215 MTL_SYSTEM_ITEMS_KFV MTL,
216 AHL_PC_HEADERS_B HEADER
217 WHERE
218 UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND
219 CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND
220 CSI.LAST_VLD_ORGANIZATION_ID = MTL.ORGANIZATION_ID AND
221 MTL.ITEM_TYPE = HEADER.PRODUCT_TYPE_CODE AND
222 UNIT.UNIT_CONFIG_STATUS_CODE IN ('COMPLETE', 'INCOMPLETE','QUARANTINE','DEACTIVATE_QUARANTINE') AND
223 HEADER.PC_HEADER_ID = p_pc_header_id AND
224 TRUNC(SYSDATE) BETWEEN NVL(TRUNC(UNIT.ACTIVE_START_DATE), TRUNC(SYSDATE)) AND
225 NVL(TRUNC(UNIT.ACTIVE_END_DATE), TRUNC(SYSDATE))
226
227 -- SATHAPLI::Bug#5140968 fix --
228 AND TRUNC(SYSDATE) < NVL(TRUNC(CSI.ACTIVE_END_DATE), TRUNC(SYSDATE + 1))
229
230 MINUS
231 SELECT
232 AHASS.UNIT_ITEM_ID
233 FROM
234 AHL_PC_ASSOCIATIONS_V AHASS,
235 AHL_PC_NODES_B NODE
236 WHERE
237 AHASS.ASSOCIATION_TYPE_FLAG = 'U' AND
238 AHASS.PC_NODE_ID = NODE.PC_NODE_ID AND
239 NODE.PC_HEADER_ID = p_pc_header_id;
240 */
241 SELECT DISTINCT
242 UNIT.UNIT_CONFIG_HEADER_ID
243 FROM
244 AHL_UNIT_CONFIG_HEADERS UNIT,
245 CSI_ITEM_INSTANCES CSI,
246 MTL_SYSTEM_ITEMS_KFV MTL,
247 AHL_PC_HEADERS_B HEADER
248 WHERE
249 UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID AND
250 CSI.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND
251 CSI.LAST_VLD_ORGANIZATION_ID = MTL.ORGANIZATION_ID AND
252 MTL.ITEM_TYPE = HEADER.PRODUCT_TYPE_CODE AND
253 UNIT.UNIT_CONFIG_STATUS_CODE IN ('COMPLETE',
254 'INCOMPLETE','QUARANTINE','DEACTIVATE_QUARANTINE') AND
255 HEADER.PC_HEADER_ID = p_pc_header_id AND
256 TRUNC(SYSDATE) BETWEEN NVL(TRUNC(UNIT.ACTIVE_START_DATE), TRUNC(SYSDATE))
257 AND
258 NVL(TRUNC(UNIT.ACTIVE_END_DATE), TRUNC(SYSDATE))
259
260 -- SATHAPLI::Bug#5140968 fix --
261 AND TRUNC(SYSDATE) < NVL(TRUNC(CSI.ACTIVE_END_DATE), TRUNC(SYSDATE + 1))
262 MINUS
263 SELECT
264 AHS.UNIT_ITEM_ID
265 FROM
266 AHL_PC_ASSOCIATIONS AHS,
267 AHL_PC_NODES_B NODE
268 WHERE
269 AHS.ASSOCIATION_TYPE_FLAG = 'U' AND
270 AHS.PC_NODE_ID = NODE.PC_NODE_ID AND
271 NODE.PC_HEADER_ID = p_pc_header_id;
272
273
274 l_pc_status VARCHAR2(30) := 'DRAFT';
275 l_pc_primary VARCHAR2(1) := 'N';
276 l_pc_assos_type VARCHAR2(1) := null;
277 l_dummy_id NUMBER;
278
279 BEGIN
280
281 OPEN get_pc_details;
282 FETCH get_pc_details INTO l_pc_primary, l_pc_status, l_pc_assos_type;
283 CLOSE get_pc_details;
284
285 IF l_pc_primary = 'Y' --and l_pc_status = 'COMPLETE'
286 THEN
287 IF (l_pc_assos_type = 'U')
288 THEN
289 OPEN check_unassigned_units;
290 FETCH check_unassigned_units INTO l_dummy_id;
291 IF (check_unassigned_units%FOUND)
292 THEN
293 CLOSE check_unassigned_units;
294 RETURN -1;
295 ELSE
296 CLOSE check_unassigned_units;
297 RETURN 0;
298 END IF;
299 ELSIF (l_pc_assos_type = 'I')
300 THEN
301 OPEN check_unassigned_parts;
302 FETCH check_unassigned_parts INTO l_dummy_id;
303 IF (check_unassigned_parts%FOUND)
304 THEN
305 CLOSE check_unassigned_parts;
306 RETURN -1;
307 ELSE
308 CLOSE check_unassigned_parts;
309 RETURN 0;
310 END IF;
311 ELSE
312 RETURN 0;
313 END IF;
314 ELSE
315 RETURN 0;
316 END IF;
317
318 END is_pc_complete;
319
320 END ahl_util_pc_pkg;