[Home] [Help]
PACKAGE BODY: APPS.ENG_CHANGE_POLICY_PKG
Source
1 PACKAGE BODY ENG_CHANGE_POLICY_PKG AS
2 /* $Header: ENGUCHPB.pls 120.1 2005/06/12 21:57:20 appldev $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_CHANGE_POLICY_PKG';
5
6 PROCEDURE GetChangePolicy
7 ( p_policy_object_name IN VARCHAR2
8 , p_policy_code IN VARCHAR2
9 , p_policy_pk1_value IN VARCHAR2
10 , p_policy_pk2_value IN VARCHAR2
11 , p_policy_pk3_value IN VARCHAR2
12 , p_policy_pk4_value IN VARCHAR2
13 , p_policy_pk5_value IN VARCHAR2
14 , p_attribute_object_name IN VARCHAR2
15 , p_attribute_code IN VARCHAR2
16 , p_attribute_value IN VARCHAR2
17 , x_policy_value OUT NOCOPY VARCHAR2
18 )
19 IS
20 BEGIN
21
22 SELECT pv.policy_char_value
23 INTO x_policy_value
24 from eng_change_rule_attributes_vl ra,
25 eng_change_rules r,
26 eng_change_policy_values pv,
27 eng_change_policies p
28 where
29 p.policy_object_name = p_policy_object_name
30 and p.policy_code= p_policy_code
31 and p.policy_object_pk1_value = p_policy_pk1_value
32 and p.policy_object_pk2_value = p_policy_pk2_value
33 and p.policy_object_pk3_value = p_policy_pk3_value
34 and ra.attribute_object_name = p_attribute_object_name
35 and ra.attribute_code = p_attribute_code
36 and nvl(r.attribute_number_value,'') = nvl(p_attribute_value,'')
37 and p.change_policy_id = pv.change_policy_id
38 and pv.change_rule_id = r.change_rule_id
39 and r.attribute_object_name = ra.attribute_object_name
40 and r.attribute_code = ra.attribute_code;
41 EXCEPTION
42 WHEN no_data_found THEN
43 x_policy_value := 'ALLOWED';
44
45 END GetChangePolicy ;
46
47 PROCEDURE GET_OPATTR_CHANGEPOLICY
48 ( p_api_version IN NUMBER
49 , x_return_status OUT NOCOPY VARCHAR2
50 , p_catalog_category_id IN VARCHAR2
51 , p_item_lifecycle_id IN VARCHAR2
52 , p_lifecycle_phase_id IN VARCHAR2
53 , p_attribute_grp_ids IN VARCHAR2
54 , x_policy_value OUT NOCOPY VARCHAR2
55 )
56 IS
57 l_dynamic_sql VARCHAR2(32767);
58 l_api_name CONSTANT VARCHAR2(30) := 'GET_OPATTR_CHANGEPOLICY';
59 l_api_version CONSTANT NUMBER := 1.0;
60 BEGIN
61 IF NOT FND_API.Compatible_API_Call( l_api_version,
62 p_api_version,
63 l_api_name,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 l_dynamic_sql := q'!
70 select decode (max(restrict_id), 30, 'NOT_ALLOWED',
71 20, 'CHANGE_ORDER_REQUIRED',
72 10, 'ALLOWED')
73 from
74 ( select decode( pv.policy_char_value, 'NOT_ALLOWED', 30,
75 'CHANGE_ORDER_REQUIRED', 20,
76 10) restrict_id
77 from eng_change_rule_attributes_vl ra,
78 eng_change_rules r,
79 eng_change_policy_values pv,
80 eng_change_policies p
81 where
82 p.policy_object_name = 'CATALOG_LIFECYCLE_PHASE'
83 and p.policy_code= 'CHANGE_POLICY'
84 and p.policy_object_pk1_value = !' || p_catalog_category_id || q'!
85 and p.policy_object_pk2_value = !' || p_item_lifecycle_id || q'!
86 and p.policy_object_pk3_value = !' || p_lifecycle_phase_id || q'!
87 and ra.attribute_object_name = 'EGO_CATALOG_GROUP'
88 and ra.attribute_code = 'ATTRIBUTE_GROUP'
89 and r.attribute_number_value in !'
90 || '('|| p_attribute_grp_ids|| ')' || q'!
91 and p.change_policy_id = pv.change_policy_id
92 and pv.change_rule_id = r.change_rule_id
93 and r.attribute_object_name = ra.attribute_object_name
94 and r.attribute_code = ra.attribute_code)!';
95
96 EXECUTE IMMEDIATE l_dynamic_sql
97 INTO x_policy_value;
98
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 EXCEPTION
102 WHEN no_data_found
103 THEN
104 x_policy_value := 'ALLOWED';
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106 WHEN OTHERS
107 THEN
108 x_policy_value := 'NOT_ALLOWED';
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110
111 END GET_OPATTR_CHANGEPOLICY;
112
113 END ENG_CHANGE_POLICY_PKG ;