[Home] [Help]
PACKAGE BODY: APPS.ENG_DOM_UTIL_PUB
Source
1 PACKAGE BODY ENG_DOM_UTIL_PUB AS
2 /* $Header: ENGPDUTB.pls 120.1 2006/04/27 03:23:21 prgopala noship $ */
3
4 G_SUCCESS CONSTANT NUMBER := 0;
5 G_WARNING CONSTANT NUMBER := 1;
6 G_ERROR CONSTANT NUMBER := 2;
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_DOM_UTIL_PUB';
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'ENG';
10 G_PKG_NAME_TOKEN CONSTANT VARCHAR2(8) := 'PKG_NAME';
11 G_API_NAME_TOKEN CONSTANT VARCHAR2(8) := 'API_NAME';
12 G_SQL_ERR_MSG_TOKEN CONSTANT VARCHAR2(11) := 'SQL_ERR_MSG';
13 G_PLSQL_ERR CONSTANT VARCHAR2(17) := 'EGO_PLSQL_ERR';
14
15 PROCEDURE code_debug (p_msg IN VARCHAR2) IS
16 BEGIN
17 --sri_debug (' ENGPDUTB - ENG_DOM_UTIL_PUB '||p_msg);
18 RETURN;
19 EXCEPTION
20 WHEN OTHERS THEN
21 NULL;
22 END;
23
24
25 FUNCTION check_floating_attachments (
26 p_inventory_item_id IN NUMBER
27 ,p_revision_id IN NUMBER
28 ,p_organization_id IN NUMBER
29 ,p_lifecycle_id IN NUMBER
30 ,p_new_phase_id IN NUMBER
31 ) RETURN VARCHAR2 IS
32 l_catalog_id NUMBER;
33 l_lifecycle_id NUMBER;
34 l_current_phase_id NUMBER;
35 l_catalog_group_ids VARCHAR2(150);
36 l_att_cat_ids VARCHAR2(150);
37 l_row_count NUMBER;
38 attach_query VARCHAR2(2000);
39 pk_value NUMBER;
40 cat_index INTEGER;
41 l_flag BOOLEAN;
42 t_parent_cat_id NUMBER;
43
44
45 CURSOR policy_cursor (
46 cp_pol_obj_name IN VARCHAR2,
47 cp_pol_code IN VARCHAR2,
48 cp_pol_pk1 IN NUMBER,
49 cp_pol_pk2 IN NUMBER,
50 cp_pol_pk3 IN NUMBER,
51 cp_attr_obj_name IN VARCHAR2,
52 cp_attr_code IN VARCHAR2) IS
53 SELECT r.attribute_code,
54 ra.attribute_column_type,
55 r.attribute_number_value,
56 r.attribute_char_value,
57 p.policy_column_type,
58 pv.policy_char_value,
59 pv.policy_number_value,
60 ra.attribute_object_name
61 from
62 eng_change_rule_attributes_vl ra,
63 eng_change_rules r,
64 eng_change_policy_values pv,
65 eng_change_policies p
66 where
67 p.policy_object_name = cp_pol_obj_name
68 and p.policy_code= cp_pol_code
69 and p.policy_object_pk1_value = cp_pol_pk1
70 and p.policy_object_pk2_value = cp_pol_pk2
71 and p.policy_object_pk3_value = cp_pol_pk3
72 and ra.attribute_object_name = cp_attr_obj_name
73 and ra.attribute_code = cp_attr_code
74 and p.change_policy_id = pv.change_policy_id
75 and pv.change_rule_id = r.change_rule_id
76 and r.attribute_object_name = ra.attribute_object_name
77 and r.attribute_code = ra.attribute_code
78 and r.attribute_number_value IS NOT NULL;
79
80 CURSOR c_get_assoc_category_id (cp_catalog_category_id IN NUMBER
81 ,cp_lifecycle_id IN NUMBER
82 ) IS
83 SELECT ic.item_catalog_group_id
84 FROM MTL_ITEM_CATALOG_GROUPS_B ic
85 WHERE EXISTS (
86 SELECT olc.object_classification_code CatalogId
87 FROM ego_obj_type_lifecycles olc, fnd_objects o
88 WHERE o.obj_name = 'EGO_ITEM'
89 AND olc.object_id = o.object_id
90 AND olc.lifecycle_id = cp_lifecycle_id
91 AND olc.object_classification_code = item_catalog_group_id
92 )
93 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
94 START WITH item_catalog_group_id = cp_catalog_category_id;
95
96 BEGIN
97 code_debug( 'ENG_DOM_UTIL ' ) ;
98
99
100
101 IF p_revision_id IS NULL OR p_revision_id = -1 THEN
102 SELECT lifecycle_id,
103 current_phase_id,
104 item_catalog_group_id
105 INTO
106 l_lifecycle_id,
107 l_current_phase_id,
108 l_catalog_id
109 FROM MTL_SYSTEM_ITEMS
110 WHERE inventory_item_id = p_inventory_item_id
111 AND organization_id = p_organization_id;
112 ELSE
113 SELECT REV.lifecycle_id,
114 REV.current_phase_id,
115 IT.item_catalog_group_id
116 INTO
117 l_lifecycle_id,
118 l_current_phase_id,
119 l_catalog_id
120 FROM MTL_ITEM_REVISIONS REV, MTL_SYSTEM_ITEMS IT
121 WHERE
122 REV.inventory_item_id = IT.inventory_item_id
123 AND REV.inventory_item_id = p_inventory_item_id
124 AND REV.organization_id = p_organization_id
125 AND REV.revision_id = p_revision_id;
126 END IF;
127
128 code_debug('vals ' || p_inventory_item_id || ' ' || p_revision_id || ' ' || p_lifecycle_id || ' ' || p_new_phase_id || ' ' || l_catalog_id );
129 IF p_lifecycle_id IS NOT NULL THEN
130 l_lifecycle_id := p_lifecycle_id;
131 END IF;
132
133 --Check which catalog category actually has the lifecycle associated with it
134 OPEN c_get_assoc_category_id (cp_lifecycle_id => l_lifecycle_id
135 ,cp_catalog_category_id => l_catalog_id
136 );
137 FETCH c_get_assoc_category_id INTO t_parent_cat_id;
138 CLOSE c_get_assoc_category_id;
139
140
141 code_debug('CATALOG_ID' || t_parent_cat_id );
142
143
144 l_att_cat_ids := '';
145 code_debug ('vals ' || pk_value || ' ' || l_lifecycle_id || ' ' || p_new_phase_id);
146 FOR pol_rec1 IN policy_cursor (
147 cp_pol_obj_name => 'CATALOG_LIFECYCLE_PHASE',
148 cp_pol_code => 'CHANGE_POLICY',
149 cp_pol_pk1 => t_parent_cat_id,
150 cp_pol_pk2 => l_lifecycle_id,
151 cp_pol_pk3 => p_new_phase_id,
152 cp_attr_obj_name => 'EGO_CATALOG_GROUP',
153 cp_attr_code => 'ATTACHMENT'
154 ) LOOP
155 IF pol_rec1.ATTRIBUTE_NUMBER_VALUE IS NOT NULL THEN
156 IF pol_rec1.POLICY_CHAR_VALUE = 'CHANGE_ORDER_REQUIRED' THEN
157 l_att_cat_ids := l_att_cat_ids || pol_rec1.ATTRIBUTE_NUMBER_VALUE || ',';
158 END IF;
159 END IF;
160 END LOOP;
161
162 IF (LENGTH(l_att_cat_ids) > 0) THEN
163 l_att_cat_ids := SUBSTR(l_att_cat_ids, 1, LENGTH(l_att_cat_ids) - LENGTH(','));
164 END IF;
165
166 code_debug( 'ATT CATS ' || l_att_cat_ids );
167
168 IF l_att_cat_ids IS NULL THEN
169 RETURN 'N';
170 END IF;
171
172 attach_query := 'select count(*) from (SELECT a.ATTACHED_DOCUMENT_ID,
173 a.DOCUMENT_ID,
174 a.ENTITY_NAME,
175 a.PK1_VALUE,
176 a.PK2_VALUE,
177 a.PK3_VALUE,
178 a.PK4_VALUE,
179 a.PK5_VALUE,
180 nvl(a.CATEGORY_ID, d.CATEGORY_ID) AS CATEGORY_ID,
181 d.DOCUMENT_ID AS DOCUMENT_ID1,
182 dt.DESCRIPTION,
183 dt.FILE_NAME FILE_NAME,
184 dt.MEDIA_ID,
185 d.DM_DOCUMENT_ID,
186 d.DM_VERSION_NUMBER,
187 pr.PROTOCOL
188 FROM FND_ATTACHED_DOCUMENTS a,
189 FND_DOCUMENTS d,
190 FND_DOCUMENTS_TL dt,
191 FND_DOCUMENT_CATEGORIES_TL ct,
192 FND_DM_NODES n,
193 FND_LOOKUP_VALUES_VL lkp,
194 DOM_FOLDER_ATTACHMENTS df,
195 FND_DM_NODES dn,
196 FND_DM_PRODUCTS pr
197 WHERE a.DOCUMENT_ID = d.DOCUMENT_ID and
198 d.DOCUMENT_ID = dt.DOCUMENT_ID and
199 dt.LANGUAGE = USERENV(''LANG'') and
200 ct.CATEGORY_ID = nvl(a.CATEGORY_ID, d.CATEGORY_ID) and
201 ct.LANGUAGE = USERENV(''LANG'') and
202 d.DM_NODE = n.NODE_ID (+)
203 and lkp.LOOKUP_TYPE(+) = ''FND_DM_ATTACHED_DOC_STATUS''
204 and lkp.LOOKUP_CODE(+) = nvl(a.STATUS,''UNAPPROVED'')
205 and df.attachment_id(+)=a.attached_document_id
206 and d.dm_node = dn.node_id
207 and dn.PRODUCT_ID = pr.product_id)';
208
209 IF p_revision_id IS NULL THEN
210 attach_query := attach_query || ' where entity_name = ''MTL_SYSTEM_ITEMS''';
211 ELSE
212 attach_query := attach_query || ' where entity_name = ''MTL_ITEM_REVISIONS''';
213 END IF;
214
215 attach_query := attach_query || ' and pk1_value = :1 and pk2_value = :2 and dm_document_id = 0 and protocol = ''WEBSERVICES'' and category_id in ('|| l_att_cat_ids || ')';
216
217 code_debug (attach_query);
218
219 EXECUTE IMMEDIATE attach_query INTO l_row_count using p_organization_id, p_inventory_item_id;
220
221 code_debug ('Row count ' || l_row_count);
222
223 IF l_row_count = 0 THEN
224 RETURN 'N';
225 ELSE
226 RETURN 'Y';
227 END IF;
228 RETURN 'N';
229 EXCEPTION
230 WHEN OTHERS THEN
231 RETURN 'N';
232
233 END check_floating_attachments;
234
235
236 END ENG_DOM_UTIL_PUB;