[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_ATTR_RT_INSERT_PVT
Source
1 package body PV_PARTNER_ATTR_RT_INSERT_PVT as
2 /* $Header: pvxptaib.pls 120.1 2005/12/14 15:32:21 amaram noship $ */
3 g_pkg_name constant varchar2(100) := 'PV_PARTNER_ATTR_RT_INSERT_PVT';
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* private procedure declaration */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13 PROCEDURE Debug(
14 p_msg_string IN VARCHAR2,
15 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
16 );
17
18
19 PROCEDURE Set_Message(
20 p_msg_level IN NUMBER,
21 p_msg_name IN VARCHAR2,
22 p_token1 IN VARCHAR2 := NULL,
23 p_token1_value IN VARCHAR2 := NULL,
24 p_token2 IN VARCHAR2 := NULL,
25 p_token2_value IN VARCHAR2 := NULL,
26 p_token3 IN VARCHAR2 := NULL,
27 p_token3_value IN VARCHAR2 := NULL
28 );
29
30 PROCEDURE partner_attr_insert (
31 P_Api_Version_Number IN NUMBER,
32 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
33 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
34 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
35 p_partner_id IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 )
40 IS
41 l_api_name CONSTANT VARCHAR2(30) := 'partner_attr_insert';
42 l_api_version_number CONSTANT NUMBER := 1.0;
43
44
45 cnt NUMBER := 0;
46 l_attr_text VARCHAR2(100);
47 l_search_attr_id NUMBER;
48 l_chk_flag VARCHAR2(1);
49
50 BEGIN
51
52 -- Standard call to check for call compatibility.
53 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
54 p_api_version_number,
55 l_api_name,
56 G_PKG_NAME)
57 THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list )
63 THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66
67 -- Debug Message
68 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
69 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
70 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
71 fnd_msg_pub.Add;
72 END IF;
73
74 -- Initialize API return status to SUCCESS
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 --
78 EXECUTE IMMEDIATE
79 '
80 INSERT INTO pv_search_attr_values
81 SELECT pv_search_attr_values_s.nextval
82 , partner_id
83 , null
84 , attr_text
85 , SYSDATE
86 , FND_GLOBAL.user_id
87 , SYSDATE
88 , FND_GLOBAL.user_id
89 , 1.0
90 , FND_GLOBAL.user_id
91 , null
92 , attribute_id
93 , null
94 from (
95 SELECT 19 attribute_id, attr_code attr_text, pp.partner_id partner_id
96 FROM pv_partner_profiles pp,
97 pv_attribute_codes_vl pac
98 WHERE pp.partner_level=to_char(pac.attr_code_id)
99 AND pp.partner_id = :1
100 AND pac.enabled_flag = ''Y''
101 UNION all
102 SELECT distinct 4 attribute_id, hl.country attr_text, pvpp.partner_id partner_id
103 FROM hz_locations hl,
104 hz_party_sites hs,
105 pv_partner_profiles pvpp
106 WHERE hl.location_id = hs.location_id
107 AND hs.party_id = pvpp.partner_party_id
108 AND pvpp.partner_id = :1
109 AND (hs.status = ''A'' OR hs.status IS NULL)
110 UNION all
111 SELECT 11 attribute_id,
112 hzp.party_name attr_text,
113 pvpp.partner_id partner_id
114 FROM pv_partner_profiles pvpp, hz_parties hzp
115 WHERE partner_id=:1
116 AND hzp.party_id = pvpp.partner_party_id
117
118 UNION all
119 SELECT 3 attribute_id,pear.attr_value attr_text, pear.entity_id partner_id
120 FROM pv_enty_attr_values pear
121 WHERE pear.entity_id = :1
122 AND pear.latest_flag = ''Y''
123 AND attr_value is not null
124 AND attribute_id = 3 )' USING p_partner_id,p_partner_id,p_partner_id,p_partner_id ;
125
126
127 EXCEPTION
128 WHEN others THEN
129 Debug(SQLCODE || ': ' || SQLERRM);
130
131 END partner_attr_insert;
132
133 --=============================================================================+
134 --| Private Procedure |
135 --| |
136 --| Debug |
137 --| |
138 --| Parameters |
139 --| IN |
140 --| OUT |
141 --| |
142 --| |
143 --| NOTES: |
144 --| |
145 --| HISTORY |
146 --| |
147 --==============================================================================
148 PROCEDURE Debug(
149 p_msg_string IN VARCHAR2,
150 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE'
151 )
152 IS
153 BEGIN
154 FND_MESSAGE.Set_Name('PV', p_msg_type);
155 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
156 FND_MSG_PUB.Add;
157 END Debug;
158 -- =================================End of Debug================================
159
160
161 --=============================================================================+
162 --| Public Procedure |
163 --| |
164 --| Set_Message |
165 --| |
166 --| Parameters |
167 --| IN |
168 --| OUT |
169 --| |
170 --| |
171 --| NOTES: |
172 --| |
173 --| HISTORY |
174 --| |
175 --==============================================================================
176 PROCEDURE Set_Message(
177 p_msg_level IN NUMBER,
178 p_msg_name IN VARCHAR2,
179 p_token1 IN VARCHAR2 := NULL,
180 p_token1_value IN VARCHAR2 := NULL,
181 p_token2 IN VARCHAR2 := NULL,
182 p_token2_value IN VARCHAR2 := NULL,
183 p_token3 IN VARCHAR2 := NULL,
184 p_token3_value IN VARCHAR2 := NULL
185 )
186 IS
187 BEGIN
188 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level) THEN
189 FND_MESSAGE.Set_Name('PV', p_msg_name);
190 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
191
192 IF (p_token1 IS NOT NULL) THEN
193 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
194 END IF;
195
196 IF (p_token2 IS NOT NULL) THEN
197 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
198 END IF;
199
200 IF (p_token3 IS NOT NULL) THEN
201 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
202 END IF;
203
204 FND_MSG_PUB.Add;
205
206
207 END IF;
208 END Set_Message;
209 -- ==============================End of Set_Message==============================
210
211
212 END PV_PARTNER_ATTR_RT_INSERT_PVT;