[Home] [Help]
PACKAGE BODY: APPS.GMP_UTILITY
Source
1 PACKAGE BODY GMP_UTILITY as
2 /* $Header: GMPUTILB.pls 120.5 2010/09/16 11:34:06 rpatangy ship $ */
3
4 /*
5 REM+=========================================================================+
6 REM| PROCEDURE NAME |
7 REM| log_message |
8 REM| |
9 REM| TYPE |
10 REM| Private |
11 REM| USAGE |
12 REM| |
13 REM| DESCRIPTION |
14 REM| This is a general error reporting procedure which logs errors to a |
15 REM| file or to the screen (for debug) |
16 REM| |
17 REM| PARAMETERS |
18 REM| string IN VARCHAR2 |
19 REM| |
20 REM| HISTORY |
21 REM| 09/01/2010 Rajesh Patangya |
22 REM+=========================================================================+
23 */
24
25 PROCEDURE LOG_MESSAGE(pBUFF IN VARCHAR2) IS
26 BEGIN
27 IF fnd_global.conc_request_id > 0 THEN
31 END IF;
28 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
29 ELSE
30 null;
32
33 EXCEPTION
34 WHEN OTHERS THEN
35 RETURN;
36 END LOG_MESSAGE;
37
38 PROCEDURE generate_opm_acct
39 (
40 V_DESTINATION_TYPE IN VARCHAR2 ,
41 V_INV_ITEM_TYPE IN VARCHAR2 ,
42 V_SUBINV_TYPE IN VARCHAR2,
43 V_DEST_ORG_ID IN NUMBER ,
44 V_APPS_ITEM_ID IN NUMBER,
45 V_VENDOR_SITE_ID IN NUMBER,
46 V_CC_ID IN OUT NOCOPY NUMBER
47 ) IS
48
49 P_DESTINATION_TYPE VARCHAR2(20) := NULL ;
50 P_INV_ITEM_TYPE VARCHAR2(20) := NULL ;
51 P_SUBINV_TYPE VARCHAR2(20) := NULL ;
52 P_DEST_ORG_ID NUMBER := 0 ;
53 P_APPS_ITEM_ID NUMBER := 0 ;
54 P_VENDOR_SITE_ID NUMBER := 0 ;
55 P_CC_ID NUMBER := 0 ;
56
57 BEGIN
58 P_DESTINATION_TYPE := V_DESTINATION_TYPE ;
59 P_INV_ITEM_TYPE := V_INV_ITEM_TYPE ;
60 P_SUBINV_TYPE := V_SUBINV_TYPE ;
61 P_DEST_ORG_ID := V_DEST_ORG_ID ;
62 P_APPS_ITEM_ID := V_APPS_ITEM_ID ;
63 P_VENDOR_SITE_ID := V_VENDOR_SITE_ID ;
64
65 /* Actual call to routine to return the account Id's */
66
67 GML_ACCT_GENERATE.generate_opm_acct(P_DESTINATION_TYPE,
68 P_INV_ITEM_TYPE,
69 P_SUBINV_TYPE,
70 P_DEST_ORG_ID,
71 P_APPS_ITEM_ID,
72 P_VENDOR_SITE_ID,
73 P_CC_ID ) ;
74
75 V_CC_ID := P_CC_ID ;
76
77 END generate_opm_acct ;
78
79 PROCEDURE populate_eff
80 ( org_string IN VARCHAR2 ,
81 RC_RESULT OUT NOCOPY number
82 ) IS
83
84 sql_stmt VARCHAR2(32767) := NULL ;
85 l_return NUMBER ;
86 PRAGMA AUTONOMOUS_TRANSACTION;
87
88 BEGIN
89
90 l_return := 2;
91
92 -- B10117656 If no rows are present in gmf_form_eff, then aps_fmeff_id = NULL.
93
94 sql_stmt := 'INSERT INTO gmp_form_eff( aps_fmeff_id, fmeff_id, '
95 ||' organization_id, formula_id, routing_id, '
96 ||' creation_date, created_by, last_update_date, last_updated_by) '
97 ||' ( SELECT (rownum + nvl(gmp.aps_id,0)) aps_fmeff_id, '
98 ||' eff.recipe_validity_rule_id, eff.organization_id, '
99 ||' eff.formula_id, '
100 ||' eff.routing_id , sysdate , -2 , sysdate , -2 '
101 ||'FROM ( '
102 ||' SELECT ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
103 ||' grb.formula_id, ffe.organization_id, '
104 ||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
105 ||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
106 ||' mp.organization_code wcode , grb.routing_id, '
107 ||' frh.routing_no, frh.routing_vers, frh.routing_desc, '
108 ||' frh.routing_uom, frh.routing_qty, '
109 ||' DECODE(frh.routing_uom,msi.primary_uom_code ,1, '
110 ||' inv_convert.inv_um_convert '
111 ||' ( ffe.inventory_item_id, '
112 ||' NULL, '
113 ||' ffe.organization_id, '
114 ||' 5 , '
115 ||' 1, '
116 ||' msi.primary_uom_code , ' /* primary */
117 ||' frh.routing_uom , ' /* routing um */
118 ||' NULL , '
119 ||' NULL '
120 ||' ) '
121 ||' ) prd_fct, -1 prd_ind, '
122 ||' grb.recipe_id, grb.recipe_no, grb.recipe_version , '
123 ||' 0 rhdr_loc, '
124 ||' grb.calculate_step_quantity '
125 ||' FROM gmd_recipes_b grb, '
126 ||' gmd_recipe_validity_rules ffe, '
127 ||' fm_form_mst ffm, '
128 ||' fm_rout_hdr frh, '
129 ||' mtl_parameters mp, '
130 ||' mtl_system_items msi, '
131 ||' hr_organization_units hou, '
132 ||' gmd_status_b gs1,'
133 ||' gmd_status_b gs2, '
134 ||' gmd_status_b gs3, '
135 ||' gmd_status_b gs4 '
136 ||' WHERE grb.delete_mark = 0 '
137 ||' AND grb.recipe_id = ffe.recipe_id '
138 ||' AND grb.recipe_status = gs1.status_code '
139 ||' AND gs1.status_type IN (''700'' ,''900'' ,''400'' ) '
140 ||' AND gs1.delete_mark = 0 '
141 ||' AND ffe.delete_mark = 0 '
142 ||' AND ffe.validity_rule_status = gs2.status_code '
143 ||' AND gs2.status_type IN (''700'' ,''900'' ,''400'' ) '
144 ||' AND gs2.delete_mark = 0 '
145 ||' AND frh.delete_mark = 0 '
146 ||' AND ffm.delete_mark = 0 '
147 ||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
148 ||' AND hou.organization_id = mp.organization_id '
149 ||' AND frh.inactive_ind = 0 '
150 ||' AND ffm.inactive_ind = 0 '
151 ||' AND grb.routing_id IS NOT NULL '
152 ||' AND ffe.organization_id IS NOT NULL '
153 ||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
154 ||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
155 ||' AND ffe.organization_id = mp.organization_id '
156 ||' AND grb.formula_id = ffm.formula_id '
157 ||' AND ffm.formula_status = gs3.status_code '
158 ||' AND gs3.status_type IN (''700'' ,''900'' ,''400'' ) '
159 ||' AND gs3.delete_mark = 0 '
160 ||' AND grb.routing_id = frh.routing_id '
161 ||' AND frh.routing_status = gs4.status_code '
162 ||' AND gs4.status_type IN (''700'' ,''900'' ,''400'' ) '
163 ||' AND gs4.delete_mark = 0 '
164 ||' AND msi.organization_id = ffe.organization_id '
165 ||' AND msi.inventory_item_id = ffe.inventory_item_id '
166 ||' AND msi.recipe_enabled_flag = ''Y'' '
170 ||' FROM fm_matl_dtl '
167 ||' AND msi.process_execution_enabled_flag = ''Y'' '
168 ||' AND mp.process_enabled_flag = ''Y'' '
169 ||' AND EXISTS ( SELECT 1 '
171 ||' WHERE formula_id = grb.formula_id '
172 ||' AND line_type = 1 '
173 ||' AND inventory_item_id = msi.inventory_item_id '
174 ||' AND msi.organization_id = ffe.organization_id '
175 ||' AND inventory_item_id = ffe.inventory_item_id ) '
176 ||' UNION ALL '
177 ||' SELECT ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
178 ||' grb.formula_id, ffe.organization_id, '
179 ||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
180 ||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
181 ||' mp.organization_code wcode , to_number(null) , '
182 ||' NULL, to_number(null), NULL, '
183 ||' NULL, to_number(null), to_number(null) prd_fct, -1 prd_ind, '
184 ||' grb.recipe_id, grb.recipe_no, grb.recipe_version , '
185 ||' 0 rhdr_loc, '
186 ||' 0 calculate_step_quantity '
187 ||' FROM gmd_recipes_b grb, '
188 ||' gmd_recipe_validity_rules ffe, '
189 ||' fm_form_mst ffm, '
190 ||' mtl_parameters mp, '
191 ||' mtl_system_items msi, '
192 ||' hr_organization_units hou, '
193 ||' gmd_status_b gs1, '
194 ||' gmd_status_b gs2, '
195 ||' gmd_status_b gs3 '
196 ||' WHERE grb.delete_mark = 0 '
197 ||' AND grb.recipe_id = ffe.recipe_id '
198 ||' AND grb.recipe_status = gs1.status_code '
199 ||' AND gs1.status_type IN (''700'' ,''900'' ,''400'' ) '
200 ||' AND gs1.delete_mark = 0'
201 ||' AND ffe.delete_mark = 0 '
202 ||' AND ffe.validity_rule_status = gs2.status_code '
203 ||' AND gs2.status_type IN (''700'' ,''900'' ,''400'' ) '
204 ||' AND gs2.delete_mark = 0 '
205 ||' AND ffm.delete_mark = 0 '
206 ||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
207 ||' AND hou.organization_id = mp.organization_id '
208 ||' AND ffm.inactive_ind = 0 '
209 ||' AND grb.routing_id IS NULL '
210 ||' AND ffe.organization_id IS NOT NULL '
211 ||' AND ffe.organization_id = mp.organization_id '
212 ||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
213 ||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
214 ||' AND grb.formula_id = ffm.formula_id '
215 ||' AND ffm.formula_status = gs3.status_code '
216 ||' AND gs3.status_type IN (''700'' ,''900'' ,''400'' ) '
217 ||' AND gs3.delete_mark = 0 '
218 ||' AND msi.organization_id = ffe.organization_id '
219 ||' AND msi.inventory_item_id = ffe.inventory_item_id '
220 ||' AND msi.recipe_enabled_flag = ''Y'' '
221 ||' AND msi.process_execution_enabled_flag = ''Y'' '
222 ||' AND mp.process_enabled_flag = ''Y'' '
223 ||' AND EXISTS ( SELECT 1 '
224 ||' FROM fm_matl_dtl '
225 ||' WHERE formula_id = grb.formula_id '
226 ||' AND line_type = 1 '
227 ||' AND inventory_item_id = msi.inventory_item_id '
228 ||' AND msi.organization_id = ffe.organization_id '
229 ||' AND inventory_item_id = ffe.inventory_item_id ) '
230 ||' UNION ALL '
231 ||' SELECT ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
232 ||' grb.formula_id, msi.organization_id, '
233 ||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
234 ||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
235 ||' mp.organization_code wcode , grb.routing_id, '
236 ||' frh.routing_no, frh.routing_vers, frh.routing_desc, '
237 ||' frh.routing_uom, frh.routing_qty, ' /*B2870041*/
238 ||' DECODE(frh.routing_uom,msi.primary_uom_code ,1, '
239 ||' inv_convert.inv_um_convert '
240 ||' (ffe.inventory_item_id, '
241 ||' NULL, '
242 ||' msi.organization_id, '
243 ||' 5 , '
244 ||' 1, '
245 ||' msi.primary_uom_code , ' /* primary */
246 ||' frh.routing_uom , ' /* routing um */
247 ||' NULL , '
248 ||' NULL '
249 ||' ) '
250 ||' ) prd_fct, -1 prd_ind, '
251 ||' grb.recipe_id, grb.recipe_no, grb.recipe_version , '
252 ||' 0 rhdr_loc, '
253 ||' grb.calculate_step_quantity '
254 ||' FROM gmd_recipes_b grb, '
255 ||' gmd_recipe_validity_rules ffe, '
256 ||' fm_form_mst ffm, '
257 ||' fm_rout_hdr frh, '
258 ||' mtl_parameters mp, '
259 ||' mtl_system_items msi, '
260 ||' hr_organization_units hou, '
261 ||' gmd_status_b gs1, '
262 ||' gmd_status_b gs2, '
263 ||' gmd_status_b gs3, '
264 ||' gmd_status_b gs4 '
265 ||' WHERE grb.delete_mark = 0 '
266 ||' AND grb.recipe_id = ffe.recipe_id '
267 ||' AND grb.recipe_status = gs1.status_code '
268 ||' AND gs1.status_type IN (''700'' ,''900'' ,''400'' ) '
269 ||' AND gs1.delete_mark = 0 '
270 ||' AND ffe.delete_mark = 0 '
271 ||' AND ffe.validity_rule_status = gs2.status_code '
272 ||' AND gs2.status_type IN (''700'' ,''900'' ,''400'' ) '
273 ||' AND gs2.delete_mark = 0 '
274 ||' AND frh.delete_mark = 0 '
275 ||' AND ffm.delete_mark = 0 '
276 ||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
277 ||' AND hou.organization_id = mp.organization_id '
278 ||' AND frh.inactive_ind = 0 '
279 ||' AND ffm.inactive_ind = 0 '
280 ||' AND grb.routing_id IS NOT NULL '
281 ||' AND ffe.organization_id IS NULL '
282 ||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
283 ||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
284 ||' AND grb.formula_id = ffm.formula_id '
285 ||' AND ffm.formula_status = gs3.status_code '
289 ||' AND frh.routing_status = gs4.status_code '
286 ||' AND gs3.status_type IN (''700'' ,''900'' ,''400'' ) '
287 ||' AND gs3.delete_mark = 0 '
288 ||' AND grb.routing_id = frh.routing_id '
290 ||' AND gs4.status_type IN (''700'' ,''900'' ,''400'' ) '
291 ||' AND gs4.delete_mark = 0 '
292 ||' AND mp.organization_id = msi.organization_id '
293 ||' AND mp.process_enabled_flag = ''Y'' '
294 ||' AND msi.inventory_item_id = ffe.inventory_item_id '
295 ||' AND msi.recipe_enabled_flag = ''Y'' '
296 ||' AND msi.process_execution_enabled_flag = ''Y'' '
297 ||' AND EXISTS ( SELECT 1 '
298 ||' FROM fm_matl_dtl '
299 ||' WHERE formula_id = grb.formula_id '
300 ||' AND line_type = 1 '
301 ||' AND inventory_item_id = msi.inventory_item_id '
302 ||' AND msi.organization_id = nvl(ffe.organization_id,msi.organization_id) '
303 ||' AND inventory_item_id = ffe.inventory_item_id ) '
304 ||' UNION ALL '
305 ||' SELECT ffe.recipe_validity_rule_id, ffe.inventory_item_id, '
306 ||' grb.formula_id, msi.organization_id, '
307 ||' ffe.start_date, ffe.end_date, ffe.inv_min_qty, '
308 ||' ffe.inv_max_qty, ffe.preference, msi.primary_uom_code, '
309 ||' mp.organization_code wcode , to_number(null) , '
310 ||' NULL, to_number(null), NULL, '
311 ||' NULL, to_number(null), to_number(null) prd_fct, -1 prd_ind, '
312 ||' grb.recipe_id, grb.recipe_no, grb.recipe_version , '
313 ||' 0 rhdr_loc, '
314 ||' 0 calculate_step_quantity '
315 ||' FROM gmd_recipes_b grb, '
316 ||' gmd_recipe_validity_rules ffe, '
317 ||' mtl_parameters mp, '
318 ||' fm_form_mst ffm, '
319 ||' mtl_system_items msi, '
320 ||' hr_organization_units hou, '
321 ||' gmd_status_b gs1, '
322 ||' gmd_status_b gs2, '
323 ||' gmd_status_b gs3 '
324 ||' WHERE grb.delete_mark = 0 '
325 ||' AND grb.recipe_id = ffe.recipe_id '
326 ||' AND grb.recipe_status = gs1.status_code '
327 ||' AND gs1.status_type IN (''700'' ,''900'' ,''400'' ) '
328 ||' AND gs1.delete_mark = 0 '
329 ||' AND ffe.delete_mark = 0 '
330 ||' AND ffe.validity_rule_status = gs2.status_code '
331 ||' AND gs2.status_type IN (''700'' ,''900'' ,''400'' ) '
332 ||' AND gs2.delete_mark = 0 '
333 ||' AND ffm.delete_mark = 0 '
334 ||' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '
335 ||' AND hou.organization_id = mp.organization_id '
336 ||' AND ffm.inactive_ind = 0 '
337 ||' AND grb.routing_id IS NULL '
338 ||' AND ffe.organization_id IS NULL '
339 ||' AND ffe.recipe_use IN (''0'',''1'') ' /* B10075785 */
340 ||' AND nvl(ffe.end_date,(SYSDATE + 1)) > SYSDATE '
341 ||' AND grb.formula_id = ffm.formula_id '
342 ||' AND ffm.formula_status = gs3.status_code '
343 ||' AND gs3.status_type IN (''700'' ,''900'' ,''400'' ) '
344 ||' AND gs3.delete_mark = 0 '
345 ||' AND msi.organization_id = mp.organization_id '
346 ||' AND mp.process_enabled_flag = ''Y'' '
347 ||' AND msi.inventory_item_id = ffe.inventory_item_id '
348 ||' AND msi.recipe_enabled_flag = ''Y'' '
349 ||' AND msi.process_execution_enabled_flag = ''Y'' '
350 ||' AND EXISTS ( SELECT 1 '
351 ||' FROM fm_matl_dtl '
352 ||' WHERE formula_id = grb.formula_id '
353 ||' AND line_type = 1 '
354 ||' AND inventory_item_id = msi.inventory_item_id '
355 ||' AND msi.organization_id = nvl(ffe.organization_id,msi.organization_id) '
356 ||' AND inventory_item_id = ffe.inventory_item_id ) ) eff , '
357 ||' ( select max(aps_fmeff_id) aps_id from gmp_form_eff) gmp '
358 ||' WHERE NOT EXISTS ( SELECT 1 FROM gmp_form_eff gfe '
359 ||' WHERE organization_id is NOT NULL '
360 ||' AND eff.organization_id = gfe.organization_id '
361 ||' AND eff.recipe_validity_rule_id = gfe.fmeff_id ) ' ;
362
363 IF org_string IS NOT NULL THEN
364 sql_stmt := sql_stmt
365 ||' and eff.ORGANIZATION_ID '|| org_string ||' ) ';
366 ELSE
367 sql_stmt := sql_stmt ||' ) ' ;
368 END IF;
369
370 LOG_MESSAGE('Remote Org string '||' '||org_string);
371
372 EXECUTE IMMEDIATE SQL_STMT ;
373 COMMIT;
374
375 l_return := 1;
376 RC_RESULT := l_return ;
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 l_return := 1;
381 RC_RESULT := l_return ;
382
383 WHEN OTHERS THEN
384 l_return := 2;
385 RC_RESULT := l_return ;
386 END populate_eff ;
387
388 end gmp_utility;