[Home] [Help]
PACKAGE BODY: APPS.OKE_FLOWDOWN_UTILS
Source
1 PACKAGE BODY OKE_FLOWDOWN_UTILS AS
2 /* $Header: OKEFWDUB.pls 120.4 2006/02/07 17:02:40 ifilimon noship $ */
3 --
4 -- Name : Get_Flowdown_URL
5 -- Pre-reqs : FND_GLOBAL.INITIALIZE
6 -- Function : This function returns the URL for the flowdown viewer
7 --
8 --
9 -- Parameters :
10 -- IN : None
11 -- OUT : None
12 --
13 -- Returns : VARCHAR2
14 --
15
16 FUNCTION Flowdown_URL
17 ( X_Business_Area IN VARCHAR2
18 , X_Object_Name IN VARCHAR2
19 , X_PK1 IN VARCHAR2
20 , X_PK2 IN VARCHAR2
21 ) RETURN VARCHAR2 IS
22
23 CURSOR C_FUNC ( C_Function_Name VARCHAR2 ) IS
24 SELECT function_id
25 FROM fnd_form_functions
26 WHERE function_name=C_Function_Name;
27
28 CURSOR C_WIP ( C_Org_ID NUMBER , C_Job_ID NUMBER ) IS
29 SELECT project_id , task_id
30 FROM wip_discrete_jobs
31 WHERE organization_id = C_Org_ID
32 AND wip_entity_id = C_Job_ID;
33
34 CURSOR C_PO_Line ( C_Line_ID NUMBER ) IS
35 SELECT distinct L.oke_contract_header_id , D.oke_contract_line_id
36 FROM po_distributions_all D , po_lines_all L
37 WHERE D.po_line_id = C_Line_ID
38 AND L.po_line_id = D.po_line_id;
39
40 CURSOR C_PO_Dist ( C_Dist_ID NUMBER ) IS
41 SELECT L.oke_contract_header_id , D.oke_contract_line_id , D.project_id , D.task_id
42 FROM po_distributions_all D , po_lines_all L
43 WHERE D.po_distribution_id = C_Dist_ID
44 AND L.po_line_id = D.po_line_id;
45
46 CURSOR C_Req_Line ( C_Line_ID NUMBER ) IS
47 SELECT distinct L.oke_contract_header_id , D.oke_contract_line_id
48 FROM po_req_distributions_all D , po_requisition_lines_all L
49 WHERE D.requisition_line_id = C_Line_ID
50 AND L.requisition_line_id = D.requisition_line_id;
51
52 CURSOR C_Req_Dist ( C_Dist_ID NUMBER ) IS
53 SELECT L.oke_contract_header_id , D.oke_contract_line_id , D.project_id , D.task_id
54 FROM po_req_distributions_all D , po_requisition_lines_all L
55 WHERE D.distribution_id = C_Dist_ID
56 AND L.requisition_line_id = D.requisition_line_id;
57
58 CURSOR C_Shipping ( C_Delivery_detail_ID NUMBER ) IS
59 SELECT d.k_header_id ,D.k_line_id , d.project_id , d.task_id
60 FROM wsh_delivery_details W , oke_k_deliverables_b D
61 WHERE W.delivery_detail_id = C_Delivery_detail_ID
62 AND W.source_code ='OKE'
63 AND D.deliverable_id = W.source_line_id;
64
65 user_id NUMBER;
66 resp_id NUMBER;
67 resp_appl_id NUMBER;
68 sec_grp_id NUMBER;
69 session_id NUMBER;
70 actual_target VARCHAR2(2000);
71 apps_web_agent_url VARCHAR2(2000);
72 encrypted_ids VARCHAR2(512);
73 encrypted_params VARCHAR2(10000);
74 encryption_string VARCHAR2(500);
75 other_params VARCHAR2(1000);
76
77 L_Function_ID NUMBER;
78 L_K_Hdr_ID NUMBER;
79 L_K_Line_ID NUMBER;
80 L_Proj_ID NUMBER;
81 L_Task_ID NUMBER;
82 URL_Text VARCHAR2(2000);
83
84 BEGIN
85
86 /* func_id, userid, resp_id, and func_id will indicate which func we want */
87
88 user_id := to_number(fnd_profile.value('USER_ID'));
89 resp_id := to_number(fnd_profile.value('RESP_ID'));
90 resp_appl_id := to_number(fnd_profile.value('RESP_APPL_ID'));
91 sec_grp_id := to_number(fnd_profile.value('SECURITY_GROUP_ID'));
92
93
94 -- /* Get profile plsql agent with one trailing slash */
95 -- do not need plsql_agent bug#4865201
96 -- apps_web_agent_url := fnd_web_config.plsql_agent(help_mode=>'APPS');
97
98 -- if (apps_web_agent_url is null) then
99 -- FND_MESSAGE.RETRIEVE;
100 -- FND_MESSAGE.ERROR;
101 -- RETURN ;
102 -- end if;
103
104 /* prepare other_params. */
105
106 --
107 -- PO is passing a business area of PO. Need to transform that to
108 -- PROCUREMENT. Long term we need to ask PO to fix the call
109 --
110 IF ( X_Business_Area = 'PO' ) THEN
111 other_params := 'p_ba=PROCUREMENT';
112 ELSE
113 other_params := 'p_ba=' || X_Business_Area;
114 END IF;
115
116 --
117 -- Initializing output
118 --
119 L_K_Hdr_ID := NULL;
120 L_K_Line_ID := NULL;
121 L_Proj_ID := NULL;
122 L_Task_ID := NULL;
123
124 --
125 -- Retrieveing contract / project reference from desired objects
126 --
127 IF ( X_Object_Name = 'WIP_DISCRETE_JOBS' ) THEN
128
129 OPEN C_WIP ( X_PK1 , X_PK2 );
130 FETCH C_WIP INTO L_Proj_ID , L_Task_ID;
131 CLOSE C_WIP;
132
133 ELSIF ( X_Object_Name = 'PO_LINES' ) THEN
134
135 L_K_Line_ID := NULL;
136 FOR c IN C_PO_Line ( X_PK1 ) LOOP
137 IF L_K_Line_ID IS NOT NULL AND L_K_Line_ID <> c.oke_contract_line_id THEN
138 L_K_Line_ID := NULL;
139 EXIT;
140 END IF;
141 L_K_Hdr_ID := c.oke_contract_header_id;
142 L_K_Line_ID := c.oke_contract_line_id;
143 END LOOP;
144
145 ELSIF ( X_Object_Name = 'PO_DISTRIBUTIONS' ) THEN
146
147 OPEN C_PO_Dist ( X_PK1 );
148 FETCH C_PO_Dist INTO L_K_Hdr_ID , L_K_Line_ID , L_Proj_ID , L_Task_ID;
149 CLOSE C_PO_Dist;
150
151 ELSIF ( X_Object_Name = 'PO_REQUISITION_LINES' ) THEN
152
153 L_K_Line_ID := NULL;
154 FOR c IN C_Req_Line ( X_PK1 ) LOOP
155 IF L_K_Line_ID IS NOT NULL AND L_K_Line_ID <> c.oke_contract_line_id THEN
156 L_K_Line_ID := NULL;
157 EXIT;
158 END IF;
159 L_K_Hdr_ID := c.oke_contract_header_id;
160 L_K_Line_ID := c.oke_contract_line_id;
161 END LOOP;
162
163 ELSIF ( X_Object_Name = 'PO_REQ_DISTRIBUTIONS' ) THEN
164
165 OPEN C_Req_Dist ( X_PK1 );
166 FETCH C_Req_Dist INTO L_K_Hdr_ID , L_K_Line_ID , L_Proj_ID , L_Task_ID ;
167 CLOSE C_Req_Dist;
168
169 ELSIF ( X_Object_Name = 'WSH_DELIVERY_DETAILS' ) THEN
170
171 OPEN C_Shipping ( X_PK1 );
172 FETCH C_Shipping INTO L_K_Hdr_ID , L_K_Line_ID , L_Proj_ID , L_Task_ID ;
173 CLOSE C_Shipping;
174
175 ELSIF ( X_Object_Name = 'OKE_K_HEADERS' ) THEN
176
177 L_K_Hdr_ID := X_PK1;
178
179 END IF;
180
181 IF ( L_K_Hdr_ID IS NULL ) THEN
182 --
183 -- Contract reference not found; base flowdown on project reference
184 --
185 OPEN C_FUNC ( 'OKEFLDVS' );
186 FETCH C_FUNC INTO L_Function_ID;
187 CLOSE C_FUNC;
188
189 other_params := other_params ||
190 '&p_project_id=' || L_Proj_ID ||
191 '&p_task_id=' || L_Task_ID;
192
193 ELSE
194 --
195 -- Contract reference found; base flowdown on contract reference
196 --
197 OPEN C_FUNC ( 'OKEFLDVH' );
198 FETCH C_FUNC INTO L_Function_ID;
199 CLOSE C_FUNC;
200
201 other_params := other_params ||
202 '&p_k_header_id=' || L_K_Hdr_ID ||
203 '&p_k_line_id=' || L_K_Line_ID ||
204 '&p_project_id=' || L_Proj_ID ||
205 '&p_task_id=' || L_Task_ID ;
206
207 END IF;
208
209 --
210 -- Add parameter to enable Bread Crumbs
211 --
212 other_params := other_params || '&addBreadCrumb=Y';
213
214 IF ( X_Business_Area <> 'APPROVAL' ) THEN
215 other_params := other_params || '&CallFromForm=''Y''';
216 END IF;
217
218 /* plsql_agent bug#4865201
219 --
220 -- Prepare and encrypt the parameters
221 --
222 encryption_string := to_char(user_id)||'*'
223 ||to_char(resp_appl_id)||'*'
224 ||to_char(resp_id)||'*'
225 ||to_char(sec_grp_id)||'*'
226 ||to_char(L_Function_id)||'**]';
227
228 encrypted_ids := icx_call.encrypt(encryption_string);
229
230 encrypted_params := icx_call.encrypt(other_params);
231
232 --
233 -- Construct the URL
234 --
235 URL_Text:=apps_web_agent_url
236 ||'OracleApps.LF?F='
237 ||encrypted_ids
238 ||'&P='
239 ||encrypted_params;
240 */
241 URL_Text:= fnd_run_function.get_run_function_url(
242 L_Function_id, resp_appl_id, resp_id, sec_grp_id, other_params
243 );
244
245 RETURN ( URL_Text );
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 RETURN ( apps_web_agent_url || 'oraclemypage.home' );
250 END Flowdown_URL;
251
252
253 PROCEDURE INSERT_ROW
254 ( P_BUSINESS_AREA_CODE IN VARCHAR2
255 , P_FLOWDOWN_TYPE IN VARCHAR2
256 , P_FLOWDOWN_CODE IN VARCHAR2
257 , P_ATTRIBUTE_GROUP_TYPE IN VARCHAR2
258 ) IS
259 L_CREATION_DATE DATE;
260 L_CREATED_BY NUMBER;
261 L_LAST_UPDATE_LOGIN NUMBER;
262
263 BEGIN
264
265 L_CREATION_DATE := SYSDATE;
266 L_CREATED_BY := FND_GLOBAL.USER_ID;
267 L_LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
268
269 INSERT INTO OKE_FLOWDOWNS
270 ( BUSINESS_AREA_CODE
271 , FLOWDOWN_TYPE
272 , FLOWDOWN_CODE
273 , ATTRIBUTE_GROUP_TYPE
274 , CREATION_DATE
275 , CREATED_BY
276 , LAST_UPDATE_DATE
277 , LAST_UPDATED_BY
278 , LAST_UPDATE_LOGIN
279 ) VALUES
280 ( P_BUSINESS_AREA_CODE
281 , P_FLOWDOWN_TYPE
282 , P_FLOWDOWN_CODE
283 , Decode(P_FLOWDOWN_TYPE,'ATTRIBUTE',P_ATTRIBUTE_GROUP_TYPE,'NONE')
284 , L_CREATION_DATE
285 , L_CREATED_BY
286 , L_CREATION_DATE
287 , L_CREATED_BY
288 , L_LAST_UPDATE_LOGIN
289 );
290
291 END INSERT_ROW;
292
293 PROCEDURE DELETE_ROW
294 ( P_BUSINESS_AREA_CODE IN VARCHAR2
295 , P_FLOWDOWN_TYPE IN VARCHAR2
296 , P_FLOWDOWN_CODE IN VARCHAR2
297 , P_ATTRIBUTE_GROUP_TYPE IN VARCHAR2
298 ) IS
299
300 BEGIN
301
302 DELETE FROM OKE_FLOWDOWNS
303 WHERE BUSINESS_AREA_CODE = P_BUSINESS_AREA_CODE
304 AND FLOWDOWN_TYPE = P_FLOWDOWN_TYPE
305 AND ( P_FLOWDOWN_CODE IS NULL
306 OR FLOWDOWN_CODE = P_FLOWDOWN_CODE
307 AND ATTRIBUTE_GROUP_TYPE =
308 Decode( P_FLOWDOWN_TYPE, 'ATTRIBUTE', P_ATTRIBUTE_GROUP_TYPE, 'NONE') );
309
310 END DELETE_ROW;
311
312
313 END OKE_FLOWDOWN_UTILS;