[Home] [Help]
PACKAGE BODY: APPS.QP_DEALS_UTIL
Source
1 PACKAGE BODY QP_DEALS_UTIL as
2 /* $Header: QPXUDLSB.pls 120.0.12010000.2 2008/11/05 12:09:57 bhuchand ship $ */
3
4 procedure debug_log(p_origin number, text varchar2)
5 is
6 begin
7 if p_origin = 660 then
8 oe_debug_pub.add(text);
9 end if;
10 if p_origin = 697 then
11 aso_debug_pub.ADD (text);
12 end if;
13 end;
14
15 PROCEDURE CALL_DEALS_API(
16 p_origin in NUMBER,
17 p_header_id in NUMBER,
18 p_updatable_flag IN varchar2,
19 x_redirect_function out nocopy varchar2,
20 x_is_deal_compliant out nocopy varchar2,
21 x_rules_desc out nocopy varchar2,
22 x_return_status out nocopy varchar2,
23 x_msg_data out nocopy varchar2,
24 x_is_curr_inst_deal_inst out nocopy varchar2)
25
26 IS
27 l_package VARCHAR2(30) := 'QPR_PRICE_NEGOTIATION_PUB';
28 l_procedure VARCHAR2(30) := 'INITIATE_DEAL';
29 QPR_DEAL_WKB_URL varchar2(1000) := 'OA.jsp?page=/oracle/apps/qpr/planning/negotiation/webui/PNDetailsPG' || '&' ||
30 'OAHP=QPR_ANALYST_HOME' || '&' || 'OASF=QPR_DEAL_WORKBENCH' || '&' || 'OAPB=QPR_BRANDING_TEXT';
31 QPR_DEAL_NEGO_URL varchar2(1000) := 'OA.jsp?page=/oracle/apps/qpr/planning/negotiation/webui/PNWorkbenchPG' || '&' ||
32 'OAHP=QPR_ANALYST_HOME' || '&' || 'OASF=QPR_DEAL_NEGOTIATION' || '&' || 'OAPB=QPR_BRANDING_TEXT';
33 l_instance_id NUMBER;
34 l_db_link varchar2(240);
35 l_dynamicSqlString VARCHAR2(2000);
36 l_quote_origin NUMBER ;
37 l_usr_name varchar2(100);
38 l_resp_id number;
39 l_usr_id number;
40 l_appl_id number;
44 l_dummy number;
41 l_responsibility_name varchar2(100) := 'QPR_PRICING_ANALYST';
42 l_sql varchar2(1000);
43 l_pn_url varchar2(1000);
45 --
46 BEGIN
47 l_quote_origin := p_origin;
48
49 x_is_curr_inst_deal_inst := 'Y';
50
51 l_instance_id := FND_PROFILE.VALUE('QPR_CURRENT_INSTANCE') ;
52 l_db_link := FND_PROFILE.VALUE('QPR_PN_DBLINK') ;
53
54 --If Instance ID is NULL, QPR API will fail so DONT call.
55 if l_instance_id is NULL THEN
56 x_return_status :='E';
57 x_is_deal_compliant := 'N';
58 debug_log(p_origin, 'Profile: QPR:Instance Id of this Server is null');
59 x_msg_data := fnd_message.get_string('QP','QP_QPR_INSTID_NULL_ERROR');
60 return;
61 end if;
62
63 -- This check is only required when deal instance and quote instance
64 -- are different
65
66 if l_db_link is NOT NULL THEN
67 l_db_link := '@' || l_db_link;
68 debug_log(p_origin, 'DBLink:' || l_db_link);
69
70 l_pn_url := fnd_profile.value('QPR_PN_URL');
71
72 if l_pn_url is null then
73 x_return_status :='E';
74 x_is_deal_compliant := 'N';
75 debug_log(p_origin,'Profile QPR:Price Negotiation Web server is null');
76 x_msg_data := fnd_message.get_string('QP','QP_QPR_PN_URL_NULL_ERROR');
77 return;
78 end if;
79 begin
80 debug_log(p_origin, 'Testing dblink...');
81 l_sql := 'select 1 from dual' || l_db_link;
82 execute immediate l_sql into l_dummy;
83 debug_log(p_origin, 'Success!');
84 exception
85 when others then
86 x_return_status := 'E';
87 x_is_deal_compliant := 'N';
88 debug_log(p_origin, 'Error connecting to remote instance');
89 debug_log(p_origin, sqlerrm);
90 x_msg_data := fnd_message.get_string('QP','QP_QPR_DBLINK_ERROR');
91 return;
92 end;
93
94 x_is_curr_inst_deal_inst := 'N';
95 end if;
96
97 select user_name into l_usr_name
98 from fnd_user_view
99 where user_id = fnd_global.user_id;
100
101 debug_log(p_origin,
102 'Checking if user context is available in deal instance..');
103
104 l_sql := 'begin :1 := fnd_global.user_id' || l_db_link || '; end;';
105
106 execute immediate l_sql using out l_usr_id;
107
108 debug_log(p_origin, 'Deal user id: ' || l_usr_id);
109
110 if nvl(l_usr_id , -1) = -1 then
111 debug_log(p_origin, 'Setting user context in deal instance for user '|| l_usr_name);
112 begin
113 l_sql := 'select user_id from fnd_user_view' || l_db_link
114 || ' where user_name = :1' ;
115 execute immediate l_sql into l_usr_id using l_usr_name ;
116
117 l_sql := 'select application_id, responsibility_id from fnd_responsibility' || l_db_link;
118 l_sql := l_sql || ' where responsibility_key = :1';
119
120 execute immediate l_sql into l_appl_id, l_resp_id
121 using l_responsibility_name ;
122
123 l_sql := 'begin fnd_global.apps_initialize' || l_db_link ;
124 l_sql := l_sql || '(:usr, :resp, :appl_id);end; ' ;
125
126 execute immediate l_sql using in l_usr_id, l_resp_id, l_appl_id;
127 exception
128 when others then
129 x_return_status := 'E';
130 x_is_deal_compliant := 'N';
131 debug_log(p_origin, 'Error setting user context in remote instance');
132 debug_log(p_origin, sqlerrm);
133 x_msg_data := fnd_message.get_string('QP','QP_QPR_USR_CTXT_ERROR');
134 return;
135 end;
136 end if;
137
138 begin
139 debug_log(p_origin, 'Invoking deal creation method...');
140 l_dynamicSqlString := ' begin ';
141 l_dynamicSqlString := l_dynamicSqlString || l_package ||'.';
142 l_dynamicSqlString := l_dynamicSqlString || l_procedure || l_db_link ;
143 l_dynamicSqlString := l_dynamicSqlString || '( ';
144 l_dynamicSqlString := l_dynamicSqlString || ':source_id, ';
145 l_dynamicSqlString := l_dynamicSqlString || ':source_ref_id,';
146 l_dynamicSqlString := l_dynamicSqlString || ':instance_id, ';
147 l_dynamicSqlString := l_dynamicSqlString || ':updatable, ';
148 -- OUT Parameters
149 l_dynamicSqlString := l_dynamicSqlString || ':redirect_function, ';
150 l_dynamicSqlString := l_dynamicSqlString || ':p_is_deal_compliant, ';
151 l_dynamicSqlString := l_dynamicSqlString || ':p_rules_desc, ';
152 l_dynamicSqlString := l_dynamicSqlString || ':x_return_status , ';
153 l_dynamicSqlString := l_dynamicSqlString || ':x_mesg_data ); ';
154 l_dynamicSqlString := l_dynamicSqlString || ' end; ';
155
156 EXECUTE IMMEDIATE l_dynamicSqlString USING
157 IN l_quote_origin,
158 IN p_header_id,
162 OUT x_is_deal_compliant,
159 IN l_instance_id,
160 IN p_updatable_flag,
161 OUT x_redirect_function,
163 OUT x_rules_desc,
164 OUT x_return_status,
165 OUT x_msg_data;
166 if x_return_status = FND_API.G_RET_STS_SUCCESS then
167 -- if l_quote_origin = 697 then
168 -- commit;
169 -- end if;
170
171 -- If quote instance and deal instance are different then
172 -- to invoke deal page we return the entire url
173 -- else we send the function name.
174
175 if l_pn_url is not null and l_db_link is not null then
176 if substr(l_pn_url, -1, 1) <> '/' then
177 l_pn_url := l_pn_url || '/';
178 end if;
179 if x_redirect_function = 'QPR_DEAL_WORKBENCH' then
180 x_redirect_function := l_pn_url || QPR_DEAL_WKB_URL;
181 elsif x_redirect_function = 'QPR_DEAL_NEGOTIATION' then
182 x_redirect_function := l_pn_url || QPR_DEAL_NEGO_URL;
183 end if;
184 end if;
185 end if;
186 exception
187 when others then
188 x_return_status := 'E';
189 x_is_deal_compliant := 'N';
190 debug_log(p_origin, 'Error invoking /from deal creation method');
191 debug_log(p_origin, sqlerrm);
192 x_msg_data := fnd_message.get_string('QP','QP_QPR_DEAL_CREATE_ERROR');
193 return;
194 end;
195
196 EXCEPTION
197 when others then
198 x_return_status :='E';
199 x_is_deal_compliant := 'N';
200 debug_log(p_origin, 'Unexpected error');
201 debug_log(p_origin, sqlerrm);
202 END CALL_DEALS_API;
203
204 END QP_DEALS_UTIL;