1 PACKAGE BODY INV_INVTRDST_XMLP_PKG AS
2 /* $Header: INVTRDSTB.pls 120.2.12020000.2 2012/08/15 10:22:37 rgangara ship $ */
3 FUNCTION C_SOURCE_TYPE_NAMEFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 SOURCE_TYPE_ID NUMBER;
7 SOURCE_TYPE_NAME VARCHAR2(30);
8 BEGIN
9 SOURCE_TYPE_ID := P_SOURCE_TYPE_ID;
10 IF P_SOURCE_TYPE_ID IS NOT NULL THEN
11 SELECT
12 TRANSACTION_SOURCE_TYPE_NAME
13 INTO SOURCE_TYPE_NAME
14 FROM
15 MTL_TXN_SOURCE_TYPES
16 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
17 RETURN (SOURCE_TYPE_NAME);
18 ELSE
19 RETURN (' ');
20 END IF;
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 RETURN ('No Data');
24 END;
25 RETURN NULL;
26 END C_SOURCE_TYPE_NAMEFORMULA;
27 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
28 BEGIN
29 P_EXCHANGE_RATE := FND_NUMBER.CANONICAL_TO_NUMBER(P_EXCHANGE_RATE_CHAR);
30 IF (P_SOURCE_TYPE_ID IS NOT NULL) THEN
31 IF (P_SOURCE_TYPE_ID = 7 AND P_TXN_TYPE_ID = 54) THEN
32 P_SOURCE_TYPE := 'and (mta.transaction_source_type_id = 8
33 and mmt.transaction_action_id = 3)';
34 ELSE
35 P_SOURCE_TYPE := 'and mta.transaction_source_type_id = ' || P_SOURCE_TYPE_ID;
36 END IF;
37 END IF;
38 IF (P_TXN_TYPE_ID IS NOT NULL) THEN
39 P_TXN_TYPE := 'and mmt.transaction_type_id = ' || P_TXN_TYPE_ID;
40 END IF;
41 IF (P_GL_BATCH_ID IS NOT NULL) THEN
42 P_GL_BATCH := 'and mta.gl_batch_id = ' || P_GL_BATCH_ID;
43 END IF;
44 IF P_SORT_ID = 1 THEN
45 /*SRW.SET_MAXROW('Q_acct_item'
46 ,0)*/NULL;
47 /*SRW.SET_MAXROW('Q_item_acct'
48 ,0)*/NULL;
49 /*SRW.SET_MAXROW('Q_acct_subinv'
50 ,0)*/NULL;
51 /*SRW.SET_MAXROW('Q_subinv_acct'
52 ,0)*/NULL;
53 ELSIF P_SORT_ID = 2 THEN
54 /*SRW.SET_MAXROW('Q_item_acct'
55 ,0)*/NULL;
56 /*SRW.SET_MAXROW('Q_acct'
57 ,0)*/NULL;
58 /*SRW.SET_MAXROW('Q_acct_subinv'
59 ,0)*/NULL;
60 /*SRW.SET_MAXROW('Q_subinv_acct'
61 ,0)*/NULL;
62 ELSIF P_SORT_ID = 3 THEN
63 /*SRW.SET_MAXROW('Q_acct'
64 ,0)*/NULL;
65 /*SRW.SET_MAXROW('Q_acct_item'
66 ,0)*/NULL;
67 /*SRW.SET_MAXROW('Q_acct_subinv'
68 ,0)*/NULL;
69 /*SRW.SET_MAXROW('Q_subinv_acct'
70 ,0)*/NULL;
71 ELSIF P_SORT_ID = 4 THEN
72 /*SRW.SET_MAXROW('Q_acct'
73 ,0)*/NULL;
74 /*SRW.SET_MAXROW('Q_acct_item'
75 ,0)*/NULL;
76 /*SRW.SET_MAXROW('Q_item_acct'
77 ,0)*/NULL;
78 /*SRW.SET_MAXROW('Q_subinv_acct'
79 ,0)*/NULL;
80 ELSIF P_SORT_ID = 5 THEN
81 /*SRW.SET_MAXROW('Q_acct'
82 ,0)*/NULL;
83 /*SRW.SET_MAXROW('Q_acct_item'
84 ,0)*/NULL;
85 /*SRW.SET_MAXROW('Q_item_acct'
86 ,0)*/NULL;
87 /*SRW.SET_MAXROW('Q_acct_subinv'
88 ,0)*/NULL;
89 ELSE
90 NULL;
91 END IF;
92 BEGIN
93 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
94 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
95 EXCEPTION
96 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
97 /*SRW.MESSAGE(1
98 ,'Failed in SRWINIT')*/NULL;
99 RAISE;
100 END;
101 BEGIN
102 NULL;
103 EXCEPTION
104 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
105 /*SRW.MESSAGE(2
106 ,'Failed in MSTK/Select')*/NULL;
107 RAISE;
108 END;
109 BEGIN
110 NULL;
111 EXCEPTION
112 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
113 /*SRW.MESSAGE(3
114 ,'Failed in GL#/Select')*/NULL;
115 RAISE;
116 END;
117 BEGIN
118 IF P_ACCT_LO IS NOT NULL OR P_ACCT_HI IS NOT NULL THEN
119 NULL;
120 ELSE
121 NULL;
122 END IF;
123 EXCEPTION
124 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
125 /*SRW.MESSAGE(4
126 ,'Failed in GL#/Where')*/NULL;
127 RAISE;
128 END;
129 BEGIN
130 IF P_ITEM_LO IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
131 NULL;
132 ELSE
133 NULL;
134 END IF;
135 EXCEPTION
136 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
137 /*SRW.MESSAGE(5
138 ,'Failed in MSTK/Where')*/NULL;
139 RAISE;
140 END;
141 BEGIN
142 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
143 NULL;
144 ELSE
145 NULL;
146 END IF;
147 EXCEPTION
148 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
149 /*SRW.MESSAGE(6
150 ,'Failed in MCAT/Where')*/NULL;
151 RAISE;
152 END;
153 BEGIN
154 NULL;
155 EXCEPTION
156 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
157 /*SRW.MESSAGE(9
158 ,'Failed in MKTS/Sel')*/NULL;
159 RAISE;
160 END;
161 BEGIN
162 NULL;
163 EXCEPTION
164 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
165 /*SRW.MESSAGE(10
166 ,'Failed in MDSP/Sel')*/NULL;
167 RAISE;
168 END;
169 BEGIN
170 NULL;
171 EXCEPTION
172 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
173 /*SRW.MESSAGE(11
174 ,'Failed in GL#/Sel')*/NULL;
175 RAISE;
176 END;
177 IF P_SOURCE_TYPE_ID in (2,8,12) THEN
178 BEGIN
179 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
180 NULL;
181 ELSE
182 NULL;
183 END IF;
184 EXCEPTION
185 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
186 /*SRW.MESSAGE(1
187 ,'Failed in before report trigger:MKTS/where')*/NULL;
188 RAISE;
189 END;
190 ELSIF P_SOURCE_TYPE_ID = 6 THEN
191 BEGIN
192 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
193 NULL;
194 ELSE
195 NULL;
196 END IF;
197 EXCEPTION
198 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
199 /*SRW.MESSAGE(1
200 ,'Failed in before report trigger:MDSP/where')*/NULL;
201 RAISE;
202 END;
203 ELSIF P_SOURCE_TYPE_ID = 3 THEN
204 BEGIN
205 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
206 NULL;
207 ELSE
208 NULL;
209 END IF;
210 EXCEPTION
211 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
212 /*SRW.MESSAGE(1
213 ,'Failed in before report trigger:GL/where')*/NULL;
214 RAISE;
215 END;
216 ELSE
217 NULL;
218 END IF;
219 FORMAT_MASK := inv_common_xmlp_pkg.GET_PRECISION(P_QTY_PRECISION);
220 RETURN (TRUE);
221 RETURN (TRUE);
222 END BEFOREREPORT;
223 FUNCTION AFTERREPORT RETURN BOOLEAN IS
224 BEGIN
225 BEGIN
226 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
227 EXCEPTION
228 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
229 /*SRW.MESSAGE(1
230 ,'Failed in SRWEXIT')*/NULL;
231 END;
232 RETURN (TRUE);
233 RETURN (TRUE);
234 END AFTERREPORT;
235 FUNCTION C_ACCT_PAD0FORMULA(C_ACCT_PAD0 IN VARCHAR2) RETURN VARCHAR2 IS
236 BEGIN
237 RETURN (C_ACCT_PAD0);
238 END C_ACCT_PAD0FORMULA;
239 FUNCTION C_ACCT_PAD1FORMULA(C_ACCT_PAD1 IN VARCHAR2) RETURN VARCHAR2 IS
240 BEGIN
241 RETURN (C_ACCT_PAD1);
242 END C_ACCT_PAD1FORMULA;
243 FUNCTION C_ACCT_PAD2FORMULA(C_ACCT_PAD2 IN VARCHAR2) RETURN VARCHAR2 IS
244 BEGIN
245 RETURN (C_ACCT_PAD2);
246 END C_ACCT_PAD2FORMULA;
247 FUNCTION C_ACCT_PAD3FORMULA(C_ACCT_PAD3 IN VARCHAR2) RETURN VARCHAR2 IS
248 BEGIN
249 RETURN (C_ACCT_PAD3);
250 END C_ACCT_PAD3FORMULA;
251 FUNCTION C_ACCT_PAD4FORMULA(C_ACCT_PAD4 IN VARCHAR2) RETURN VARCHAR2 IS
252 BEGIN
253 RETURN (C_ACCT_PAD4);
254 END C_ACCT_PAD4FORMULA;
255 FUNCTION C_ITEM_PAD1FORMULA(C_ITEM_PAD1 IN VARCHAR2) RETURN VARCHAR2 IS
256 BEGIN
257 RETURN (C_ITEM_PAD1);
258 END C_ITEM_PAD1FORMULA;
259 FUNCTION C_ITEM_PAD2FORMULA(C_ITEM_PAD2 IN VARCHAR2) RETURN VARCHAR2 IS
260 BEGIN
261 RETURN (C_ITEM_PAD2);
262 END C_ITEM_PAD2FORMULA;
263 FUNCTION WHERE_SUBINV RETURN VARCHAR2 IS
264 HI VARCHAR2(10);
265 LO VARCHAR2(10);
266 COMMON_SQL VARCHAR2(2000);
267 BEGIN
268 HI := P_SUBINV_HI;
269 LO := P_SUBINV_LO;
270 COMMON_SQL := ' AND DECODE (mmt.transaction_action_id, 3,
271 DECODE ( mmt.organization_id, mta.organization_id, mmt.subinventory_code, mmt.transfer_subinventory) ,
272 2,
273 DECODE ( SIGN(mta.primary_quantity), - 1, mmt.subinventory_code, 1, mmt.transfer_subinventory, mmt.subinventory_code),
274 28,
275 decode(sign(mta.primary_quantity),-1,mmt.subinventory_code,1, mmt.transfer_subinventory,mmt.subinventory_code),
276 5,
277 mmt.SUBINVENTORY_CODE,
278 mmt.subinventory_code) ';
279 IF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NOT NULL THEN
280 RETURN (COMMON_SQL || ' BETWEEN ''' || LO || ''' AND ''' || HI || ''' ');
281 ELSIF P_SUBINV_LO IS NULL AND P_SUBINV_HI IS NOT NULL THEN
282 RETURN (COMMON_SQL || ' <= ''' || HI || ''' ');
283 ELSIF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NULL THEN
284 RETURN (COMMON_SQL || ' >= ''' || LO || ''' ');
285 ELSE
286 RETURN (' ');
287 END IF;
288 END WHERE_SUBINV;
289 FUNCTION WHERE_VALUE RETURN VARCHAR2 IS
290 BEGIN
291 DECLARE
292 HI VARCHAR2(16);
293 LO VARCHAR2(16);
294 BEGIN
295 HI := TO_CHAR(P_VALUE_HI);
296 LO := TO_CHAR(P_VALUE_LO);
297 IF P_VALUE_HI IS NOT NULL AND P_VALUE_LO IS NOT NULL THEN
298 RETURN ('and mta.base_transaction_value between ''' || LO || ''' and ''' || HI || '''');
299 ELSIF P_VALUE_HI IS NULL AND P_VALUE_LO IS NOT NULL THEN
300 RETURN ('and mta.base_transaction_value >= ''' || LO || ''' ');
301 ELSIF P_VALUE_HI IS NOT NULL AND P_VALUE_LO IS NULL THEN
302 RETURN ('and mta.base_transaction_value <= ''' || HI || '''');
303 ELSE
304 RETURN (' ');
305 END IF;
306 END;
307 RETURN ' ';
308 END WHERE_VALUE;
309 FUNCTION C_WHERE_REASONFORMULA RETURN VARCHAR2 IS
310 BEGIN
311 IF P_REASON_ID IS NOT NULL THEN
312 RETURN ('and mmt.reason_id = ' || TO_CHAR(P_REASON_ID));
313 ELSE
314 RETURN (' ');
315 END IF;
316 RETURN NULL;
317 END C_WHERE_REASONFORMULA;
318 FUNCTION C_FROM_CATFORMULA RETURN VARCHAR2 IS
319 BEGIN
320 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
321 RETURN ('mtl_item_categories mic, mtl_categories mc,');
322 ELSE
323 RETURN ('mtl_item_categories mic,');
324 END IF;
325 RETURN NULL;
326 END C_FROM_CATFORMULA;
327 FUNCTION C_WHERE_CATFORMULA RETURN VARCHAR2 IS
328 BEGIN
329 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
330 RETURN ('and mta.inventory_item_id = mic.inventory_item_id
331 and mic.category_id = mc.category_id
332 and mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID) || '
333 and mic.organization_id = ' || TO_CHAR(P_ORG_ID));
334 ELSE
335 RETURN ('and mta.inventory_item_id = mic.inventory_item_id
336 and mic.category_set_id+0 = ' || TO_CHAR(P_CAT_SET_ID) || '
337 and mic.organization_id = ' || TO_CHAR(P_ORG_ID));
338 END IF;
339 RETURN NULL;
340 END C_WHERE_CATFORMULA;
341 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
342 BEGIN
343 DECLARE
344 CAT_SET_ID NUMBER;
345 CAT_SET_NAME VARCHAR2(30);
346 BEGIN
347 IF P_CAT_SET_ID IS NULL THEN
348 RETURN (' ');
349 ELSE
350 CAT_SET_ID := P_CAT_SET_ID;
351 SELECT
352 CATEGORY_SET_NAME
353 INTO CAT_SET_NAME
354 FROM
355 MTL_CATEGORY_SETS
356 WHERE CATEGORY_SET_ID = CAT_SET_ID;
357 RETURN (CAT_SET_NAME);
358 END IF;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 RETURN (' ');
362 WHEN OTHERS THEN
363 RETURN ('Error');
364 END;
365 RETURN NULL;
366 END C_CAT_SET_NAMEFORMULA;
367 FUNCTION C_TXN_TYPE_NAMEFORMULA RETURN VARCHAR2 IS
368 BEGIN
369 DECLARE
370 TXN_TYPE_ID NUMBER;
371 TXN_TYPE_NAME VARCHAR2(30);
372 BEGIN
373 TXN_TYPE_ID := P_TXN_TYPE_ID;
374 IF TXN_TYPE_ID IS NOT NULL THEN
375 SELECT
376 TRANSACTION_TYPE_NAME
377 INTO TXN_TYPE_NAME
378 FROM
379 MTL_TRANSACTION_TYPES
380 WHERE TRANSACTION_TYPE_ID = TXN_TYPE_ID;
381 RETURN (TXN_TYPE_NAME);
382 ELSE
383 RETURN (' ');
384 END IF;
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 RETURN ('No Data');
388 END;
389 RETURN NULL;
390 END C_TXN_TYPE_NAMEFORMULA;
391 FUNCTION C_REASON_NAMEFORMULA RETURN VARCHAR2 IS
392 BEGIN
393 DECLARE
394 TXN_REASON_ID VARCHAR2(20);
395 REASON_NAME VARCHAR2(30);
396 BEGIN
397 TXN_REASON_ID := P_REASON_ID;
398 IF TXN_REASON_ID IS NOT NULL THEN
399 SELECT
400 REASON_NAME
401 INTO REASON_NAME
402 FROM
403 MTL_TRANSACTION_REASONS
404 WHERE REASON_ID = TXN_REASON_ID;
405 RETURN (REASON_NAME);
406 ELSE
407 RETURN (' ');
408 END IF;
409 EXCEPTION
410 WHEN NO_DATA_FOUND THEN
411 RETURN ('No Data');
412 END;
413 RETURN NULL;
414 END C_REASON_NAMEFORMULA;
415 FUNCTION C_TYPE_OPTIONFORMULA RETURN VARCHAR2 IS
416 BEGIN
417 IF P_TYPE_OPTION = 1 THEN
418 RETURN ('mtst.transaction_source_type_name');
419 ELSE
420 RETURN ('mtt.transaction_type_name');
421 END IF;
422 RETURN NULL;
423 END C_TYPE_OPTIONFORMULA;
424 FUNCTION C_FROM_TYPEFORMULA RETURN VARCHAR2 IS
425 BEGIN
426 IF P_TYPE_OPTION = 1 THEN
427 RETURN ('mtl_txn_source_types mtst,');
428 ELSE
429 RETURN ('mtl_transaction_types mtt,');
430 END IF;
431 RETURN NULL;
432 END C_FROM_TYPEFORMULA;
433 FUNCTION C_WHERE_TYPEFORMULA RETURN VARCHAR2 IS
434 BEGIN
435 IF P_TYPE_OPTION = 1 THEN
436 RETURN ('and mta.transaction_source_type_id
437 = mtst.transaction_source_type_id');
438 ELSE
439 RETURN ('and mmt.transaction_type_id = mtt.transaction_type_id');
440 END IF;
441 RETURN NULL;
442 END C_WHERE_TYPEFORMULA;
443 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
444 BEGIN
445 RETURN ('(' || R_CURRENCY_CODE || ')');
446 END C_CURRENCY_CODEFORMULA;
447 FUNCTION AFTERPFORM RETURN BOOLEAN IS
448 C_DATE_FORMAT varchar2(30);
449 BEGIN
450 C_DATE_FORMAT := 'DD-MON-YYYY';
451 P_DATE_LO_1 := TO_CHAR(TO_DATE(P_DATE_LO
452 ,'YYYY/MM/DD HH24:MI:SS')
453 ,'DD-MON-RRRR HH24:MI:SS');
454 P_DATE_HI_1 := TO_CHAR(TO_DATE(P_DATE_HI
455 ,'YYYY/MM/DD HH24:MI:SS')
456 ,'DD-MON-RRRR');
457 CP_DATE_LO := to_char(TO_DATE(P_DATE_LO,'YYYY/MM/DD HH24:MI:SS'),C_DATE_FORMAT);
458 CP_DATE_HI := to_char(TO_DATE(P_DATE_HI,'YYYY/MM/DD HH24:MI:SS'),C_DATE_FORMAT);
459 IF (P_DATE_HI_1 IS NOT NULL) THEN
460 P_DATE_HI_1 := TO_CHAR(TO_DATE(P_DATE_HI_1 || ' 23:59:59'
461 ,'DD-MON-RRRR HH24:MI:SS')
462 ,'DD-MON-RRRR HH24:MI:SS');
463 ELSE
464 P_DATE_HI_1 := TO_CHAR(TO_DATE(P_DATE_HI_1
465 ,'DD-MON-RRRR HH24:MI:SS')
466 ,'DD-MON-RRRR HH24:MI:SS');
467 END IF;
468 BEGIN
469 IF P_DATE_LO_1 IS NOT NULL AND P_DATE_HI_1 IS NOT NULL THEN
470 P_DATE_RANGE := 'and (mta.transaction_date) between ' || 'to_date(''' || P_DATE_LO_1 || ''',' || '''DD-MON-YYYY HH24:MI:SS''' || ')' || ' and ' || 'to_date(''' || P_DATE_HI_1 || ''',' || '''DD-MON-YYYY HH24:MI:SS''' || ')';
471 ELSIF P_DATE_LO_1 IS NOT NULL AND P_DATE_HI_1 IS NULL THEN
472 P_DATE_RANGE := 'and (mta.transaction_date) >= ' || 'to_date(''' || P_DATE_LO_1 || ''',' || '''DD-MON-YYYY HH24:MI:SS''' || ')';
473 ELSIF P_DATE_LO_1 IS NULL AND P_DATE_HI_1 IS NOT NULL THEN
474 P_DATE_RANGE := 'and (mta.transaction_date) <= ' || 'to_date(''' || P_DATE_HI_1 || ''',' || '''DD-MON-YYYY HH24:MI:SS''' || ')';
475 ELSE
476 P_DATE_RANGE := ' ';
477 END IF;
478 END;
479 RETURN (TRUE);
480 END AFTERPFORM;
481 FUNCTION C_DATE_WHEREFORMULA RETURN VARCHAR2 IS
482 BEGIN
483 IF P_DATE_LO_1 IS NOT NULL AND P_DATE_HI_1 IS NOT NULL THEN
484 RETURN ('and mta.transaction_date between ''' || P_DATE_LO_1 || ''' and
485 ''' || P_DATE_HI_1 || '''');
486 ELSIF P_DATE_LO_1 IS NOT NULL AND P_DATE_HI_1 IS NULL THEN
487 RETURN ('and mta.transaction_date >= ''' || P_DATE_LO_1 || '''');
488 ELSIF P_DATE_LO_1 IS NULL AND P_DATE_HI_1 IS NOT NULL THEN
489 RETURN ('and mta.transaction_date <= ''' || P_DATE_HI_1 || '''');
490 ELSE
491 RETURN (' ');
492 END IF;
493 RETURN NULL;
494 END C_DATE_WHEREFORMULA;
495 FUNCTION C_SOURCE_FROMFORMULA RETURN VARCHAR2 IS
496 BEGIN
497 BEGIN
498 IF P_SOURCE_TYPE_ID = 1 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
499 RETURN ('po_headers poh,');
500 END IF;
501 IF P_SOURCE_TYPE_ID = 4 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
502 RETURN ('mtl_txn_request_headers mtrh,');
503 END IF;
504 IF P_SOURCE_TYPE_ID = 5 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
505 RETURN ('wip_entities wipe,');
506 END IF;
507 IF P_SOURCE_TYPE_ID = 7 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
508 RETURN ('po_requisition_headers prh,');
509 END IF;
510 IF P_SOURCE_TYPE_ID = 9 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
511 RETURN ('mtl_cycle_count_headers cch,');
512 END IF;
513 IF P_SOURCE_TYPE_ID = 10 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
514 RETURN ('mtl_physical_inventories pi,');
515 END IF;
516 IF P_SOURCE_TYPE_ID = 11 AND (P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL) THEN
517 RETURN ('cst_cost_updates cst,');
518 END IF;
519 END;
520 RETURN ' ';
521 END C_SOURCE_FROMFORMULA;
522 FUNCTION C_ACCT_VALUE0_RFORMULA(C_ACCT_VALUE0 IN NUMBER
523 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
524 C_ACCT_VALUE0_R NUMBER;
525 BEGIN
526 C_ACCT_VALUE0_R := ROUND(C_ACCT_VALUE0
527 ,C_EXT_PREC);
528 RETURN C_ACCT_VALUE0_R;
529 END C_ACCT_VALUE0_RFORMULA;
530 FUNCTION VALUE_RFORMULA(VALUE IN NUMBER
531 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
532 VALUE_R NUMBER;
533 BEGIN
534 VALUE_R := ROUND(VALUE
535 ,C_EXT_PREC);
536 RETURN VALUE_R;
537 END VALUE_RFORMULA;
538 FUNCTION C_REPORT_VALUE_RFORMULA(C_REPORT_VALUE IN NUMBER
539 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
540 C_REPORT_VALUE_R NUMBER;
541 BEGIN
542 C_REPORT_VALUE_R := ROUND(C_REPORT_VALUE
543 ,C_EXT_PREC);
544 RETURN C_REPORT_VALUE_R;
545 END C_REPORT_VALUE_RFORMULA;
546 FUNCTION C_ACCT_VALUE1_RFORMULA(C_ACCT_VALUE1 IN NUMBER
547 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
548 C_ACCT_VALUE1_R NUMBER;
549 BEGIN
550 C_ACCT_VALUE1_R := ROUND(C_ACCT_VALUE1
551 ,C_EXT_PREC);
552 RETURN C_ACCT_VALUE1_R;
553 END C_ACCT_VALUE1_RFORMULA;
554 FUNCTION C_ACCT_VALUE3_RFORMULA(C_ACCT_VALUE3 IN NUMBER
555 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
556 C_ACCT_VALUE3_R NUMBER;
557 BEGIN
558 C_ACCT_VALUE3_R := ROUND(C_ACCT_VALUE3
559 ,C_EXT_PREC);
560 RETURN C_ACCT_VALUE3_R;
561 END C_ACCT_VALUE3_RFORMULA;
562 FUNCTION C_ITEM_VALUE2_RFORMULA(C_ITEM_VALUE2 IN NUMBER
563 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
564 C_ITEM_VALUE2_R NUMBER;
565 BEGIN
566 C_ITEM_VALUE2_R := ROUND(C_ITEM_VALUE2
567 ,C_EXT_PREC);
568 RETURN C_ITEM_VALUE2_R;
569 END C_ITEM_VALUE2_RFORMULA;
570 FUNCTION C_SUBINV_VALUE4_RFORMULA(C_SUBINV_VALUE4 IN NUMBER
571 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
572 C_SUBINV_VALUE4_R NUMBER;
573 BEGIN
574 C_SUBINV_VALUE4_R := ROUND(C_SUBINV_VALUE4
575 ,C_EXT_PREC);
576 RETURN C_SUBINV_VALUE4_R;
577 END C_SUBINV_VALUE4_RFORMULA;
578 FUNCTION C_REPORT_VALUE1_RFORMULA(C_REPORT_VALUE1 IN NUMBER
579 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
580 C_REPORT_VALUE1_R NUMBER;
581 BEGIN
582 C_REPORT_VALUE1_R := ROUND(C_REPORT_VALUE1
583 ,C_EXT_PREC);
584 RETURN C_REPORT_VALUE1_R;
585 END C_REPORT_VALUE1_RFORMULA;
586 FUNCTION C_REPORT_VALUE2_RFORMULA(C_REPORT_VALUE2 IN NUMBER
587 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
588 C_REPORT_VALUE2_R NUMBER;
589 BEGIN
590 C_REPORT_VALUE2_R := ROUND(C_REPORT_VALUE2
591 ,C_EXT_PREC);
592 RETURN C_REPORT_VALUE2_R;
593 END C_REPORT_VALUE2_RFORMULA;
594 FUNCTION C_REPORT_VALUE3_RFORMULA(C_REPORT_VALUE3 IN NUMBER
595 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
596 C_REPORT_VALUE3_R NUMBER;
597 BEGIN
598 C_REPORT_VALUE3_R := ROUND(C_REPORT_VALUE3
599 ,C_EXT_PREC);
600 RETURN C_REPORT_VALUE3_R;
601 END C_REPORT_VALUE3_RFORMULA;
602 FUNCTION C_REPORT_VALUE4_RFORMULA(C_REPORT_VALUE4 IN NUMBER
603 ,C_EXT_PREC IN NUMBER) RETURN NUMBER IS
604 C_REPORT_VALUE4_R NUMBER;
605 BEGIN
606 C_REPORT_VALUE4_R := ROUND(C_REPORT_VALUE4
607 ,C_EXT_PREC);
608 RETURN C_REPORT_VALUE4_R;
609 END C_REPORT_VALUE4_RFORMULA;
610 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
611 BEGIN
612 RETURN (TRUE);
613 END BEFOREPFORM;
614
615 /*bug 14112611 add function C_P_SOURCE_WHERE_FOMULA to get the sql string of P_SOURCE_WHERE*/
616 FUNCTION C_P_SOURCE_WHERE_FOMULA RETURN CHAR IS
617 BEGIN
618 DECLARE
619 l_CODE varchar2(10);
620 l_OPERATOR varchar2(20);
621 l_OPERAND1 varchar2(240);
622 l_OPERAND2 varchar2(240);
623 l_TABLEALIAS varchar2(20);
624 l_OUTPUT varchar2(20);
625 l_APPL_SHORT_NAME varchar2(20) := 'INV';
626 l_idFlexNum number := 101;
627 C_P_SOURCE_WHERE Varchar2(2000) := '';
628 l_NumOf_Bind_Vars_source Number :=0;
629 l_bind_variables_tab_source fnd_flex_xml_publisher_apis.bind_variables;
630 BEGIN
631
632 IF (P_SOURCE_TYPE_ID in (2,8,12)) THEN
633 l_CODE := 'MKTS';
634 ELSIF (P_SOURCE_TYPE_ID = 6) THEN
635 l_CODE := 'MDSP';
636 ELSIF (P_SOURCE_TYPE_ID = 3) THEN
637 l_APPL_SHORT_NAME := 'SQLGL';
638 l_CODE := 'GL';
639 ELSE
640 l_CODE := 'MKTS';
641 END IF;
642 l_TABLEALIAS := l_CODE;
643 IF (P_SOURCE_LO IS NOT NULL AND P_SOURCE_HI IS NOT NULL) THEN
644 l_OPERAND1 := P_SOURCE_LO;
645 l_OPERAND2 := P_SOURCE_HI;
646 l_OPERATOR := 'BETWEEN';
647 l_OUTPUT := 'P_source_where';
648
649 ELSIF (P_SOURCE_LO IS NOT NULL AND P_SOURCE_HI IS NULL) THEN
650 l_OPERAND1 := P_SOURCE_LO;
651 l_OPERAND2 := '';
652 l_OPERATOR := '>=';
653 l_OUTPUT := 'P_source_where';
654
655 ELSIF (P_SOURCE_LO IS NULL AND P_SOURCE_HI IS NOT NULL) THEN
656 l_OPERAND1 := '';
657 l_OPERAND2 := P_SOURCE_HI;
658 l_OPERATOR := '<=';
659 l_OUTPUT := 'P_source_where';
660
661 ELSE
662 l_OPERAND1 := NULL;
663 l_OPERAND2 := NULL;
664 l_OPERATOR := '=';
665 l_OUTPUT := 'P_source_where';
666 END IF;
667
668 FND_FLEX_XML_PUBLISHER_APIS.KFF_WHERE
669 (P_LEXICAL_NAME => l_OUTPUT
670 ,P_APPLICATION_SHORT_NAME => l_APPL_SHORT_NAME
671 ,P_ID_FLEX_CODE => l_CODE
672 ,P_ID_FLEX_NUM => l_idFlexNuM
673 ,P_CODE_COMBINATION_TABLE_ALIAS => l_TABLEALIAS
674 ,P_OPERATOR => l_OPERATOR
675 ,P_OPERAND1 => l_OPERAND1
676 ,P_OPERAND2 => l_OPERAND2
677 ,X_WHERE_EXPRESSION => C_P_SOURCE_WHERE
678 ,X_NUMOF_BIND_VARIABLES => l_NumOf_Bind_Vars_source
679 ,X_BIND_VARIABLES => l_bind_variables_tab_source
680 );
681 RETURN C_P_SOURCE_WHERE;
682 END;
683
684 END C_P_SOURCE_WHERE_FOMULA;
685
686 FUNCTION C_SOURCE_WHERE_SOFORMULA RETURN CHAR IS
687 C_P_SOURCE_WHERE Varchar2(2000) := '';
688 BEGIN
689
690 C_P_SOURCE_WHERE := C_P_SOURCE_WHERE_FOMULA();
691
692 IF P_SOURCE_TYPE_ID in (2,8,12) AND (P_SOURCE_LO IS NOT NULL OR P_SOURCE_HI IS NOT NULL) THEN
693 RETURN ('and mta.transaction_source_id = mkts.sales_order_id
694 and ' || C_P_SOURCE_WHERE);
695 ELSE
696 RETURN ' ';
697 END IF;
698 END C_SOURCE_WHERE_SOFORMULA;
699 FUNCTION C_SOURCE_WHERE_GLFORMULA RETURN CHAR IS
700 C_P_SOURCE_WHERE Varchar2(2000) := '';
701 BEGIN
702
703 C_P_SOURCE_WHERE := C_P_SOURCE_WHERE_FOMULA();
704
705 IF P_SOURCE_TYPE_ID = 3 AND (P_SOURCE_LO IS NOT NULL OR P_SOURCE_HI IS NOT NULL) THEN
706 RETURN ('and mta.transaction_source_id = glc.code_combination_id
707 and ' || C_P_SOURCE_WHERE);
708 ELSE
709 RETURN ' ';
710 END IF;
711 END C_SOURCE_WHERE_GLFORMULA;
712 FUNCTION C_SOURCE_WHERE_ALIASFORMULA RETURN CHAR IS
713 C_P_SOURCE_WHERE Varchar2(2000) := '';
714 BEGIN
715
716 C_P_SOURCE_WHERE := C_P_SOURCE_WHERE_FOMULA();
717
718 IF P_SOURCE_TYPE_ID = 6 AND (P_SOURCE_LO IS NOT NULL OR P_SOURCE_HI IS NOT NULL) THEN
719 RETURN ('and mta.transaction_source_id = mdsp.disposition_id
720 and ' || C_P_SOURCE_WHERE);
721 ELSE
722 RETURN ' ';
723 END IF;
724 END C_SOURCE_WHERE_ALIASFORMULA;
725 FUNCTION C_SOURCE_WHERE_NOFORMULA RETURN CHAR IS
726 BEGIN
727 BEGIN
728 IF P_SOURCE_TYPE_ID = 1 THEN
729 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
730 RETURN ('and mta.transaction_source_id = poh.po_header_id
731 and poh.segment1 between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
732 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
733 RETURN ('and mta.transaction_source_id = poh.po_header_id
734 and poh.segment1 >= ''' || P_SOURCE_LO || ''' ');
735 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
736 RETURN ('and mta.transaction_source_id = poh.po_header_id
737 and poh.segment1 <= ''' || P_SOURCE_HI || ''' ');
738 END IF;
739 END IF;
740 END;
741 BEGIN
742 IF P_SOURCE_TYPE_ID = 4 THEN
743 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
744 RETURN ('and mta.transaction_source_id = mtrh.header_id
745 and mtrh.request_number between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
746 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
747 RETURN ('and mta.transaction_source_id = mtrh.header_id
748 and mtrh.request_number >= ''' || P_SOURCE_LO || ''' ');
749 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
750 RETURN ('and mta.transaction_source_id = mtrh.header_id
751 and mtrh.request_number <= ''' || P_SOURCE_HI || ''' ');
752 END IF;
753 END IF;
754 END;
755 BEGIN
756 IF P_SOURCE_TYPE_ID = 5 THEN
757 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
758 RETURN ('and mta.transaction_source_id = wipe.wip_entity_id
759 and wipe.wip_entity_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
760 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
761 RETURN ('and mta.transaction_source_id = wipe.wip_entity_id
762 and wipe.wip_entity_name >= ''' || P_SOURCE_LO || ''' ');
763 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
764 RETURN ('and mta.transaction_source_id = wipe.wip_entity_id
765 and wipe.wip_entity_name <= ''' || P_SOURCE_HI || ''' ');
766 END IF;
767 END IF;
768 END;
769 BEGIN
770 IF P_SOURCE_TYPE_ID = 7 THEN
771 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
772 RETURN ('and mta.transaction_source_id = prh.requisition_header_id
773 and prh.segment1 between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
774 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
775 RETURN ('and mta.transaction_source_id = prh.requisition_header_id
776 and prh.segment1 >= ''' || P_SOURCE_LO || ''' ');
777 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
778 RETURN ('and mta.transaction_source_id = prh.requisition_header_id
779 and prh.segment1 <= ''' || P_SOURCE_HI || ''' ');
780 END IF;
781 END IF;
782 END;
783 BEGIN
784 IF P_SOURCE_TYPE_ID = 9 THEN
785 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
786 RETURN ('and mta.transaction_source_id = CCH.cycle_count_header_id
787 and CCH.cycle_count_header_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
788 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
789 RETURN ('and mta.transaction_source_id = CCH.cycle_count_header_id
790 and CCH.cycle_count_header_name >= ''' || P_SOURCE_LO || ''' ');
791 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
792 RETURN ('and mta.transaction_source_id = CCH.cycle_count_header_id
793 and CCH.cycle_count_header_name <= ''' || P_SOURCE_HI || ''' ');
794 END IF;
795 END IF;
796 END;
797 BEGIN
798 IF P_SOURCE_TYPE_ID = 10 THEN
799 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
800 RETURN ('and mta.transaction_source_id = PI.physical_inventory_id
801 and PI.physical_inventory_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
802 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
803 RETURN ('and mta.transaction_source_id = PI.physical_inventory_id
804 and PI.physical_inventory_name >= ''' || P_SOURCE_LO || ''' ');
805 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
806 RETURN ('and mta.transaction_source_id = PI.physical_inventory_id
807 and PI.physical_inventory_name <= ''' || P_SOURCE_HI || ''' ');
808 END IF;
809 END IF;
810 END;
811 BEGIN
812 IF P_SOURCE_TYPE_ID = 11 THEN
813 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
814 RETURN ('and mta.transaction_source_id = CST.cost_update_id
815 and CST.description between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
816 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
817 RETURN ('and mta.transaction_source_id = CST.cost_update_id
818 and CST.description between >= ''' || P_SOURCE_LO || ''' ');
819 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
820 RETURN ('and mta.transaction_source_id = CST.cost_update_id
821 and CST.description <= ''' || P_SOURCE_HI || ''' ');
822 END IF;
823 END IF;
824 END;
825 BEGIN
826 IF P_SOURCE_TYPE_ID >= 13 THEN
827 IF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
828 RETURN ('and mmt.transaction_source_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
829 ELSIF P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
830 RETURN ('and mmt.transaction_source_name >= ''' || P_SOURCE_LO || ''' ');
831 ELSIF P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
832 RETURN ('and mmt.transaction_source_name <= ''' || P_SOURCE_HI || ''' ');
833 END IF;
834 END IF;
835 END;
836 RETURN ' ';
837 END C_SOURCE_WHERE_NOFORMULA;
838 function C_sourceFormula(Source in varchar2, Type_id in number) return VARCHAR2 is
839 begin
840 declare
841 txn_source_id VARCHAR2(480);
842 txn_source VARCHAR2(480);
843 org_id number;
844 begin
845 txn_source_id := Source;
846 org_id := P_org_id;
847 if Type_id = 1 then select segment1 into txn_source
848 from po_headers_all
849 where po_header_id = to_number(txn_source_id);
850 return(txn_source);
851 elsif Type_id = 4 then --for Bug#3919355
852 select request_number into txn_source
853 from mtl_txn_request_headers
854 where header_id = to_number(txn_source_id);
855 return(txn_source);
856 elsif Type_id = 5 then
857 select wip_entity_name into txn_source
858 from wip_entities
859 where wip_entity_id = to_number(txn_source_id);
860 return(txn_source);
861 elsif Type_id = 7 then select segment1 into txn_source
862 from po_requisition_headers_all
863 where requisition_header_id =
864 txn_source_id;
865 return(txn_source);
866 elsif Type_id = 9 then
867 select CYCLE_COUNT_HEADER_NAME into txn_source
868 from mtl_cycle_count_headers
869 where cycle_count_header_id = to_number(txn_source_id)
870 and organization_id = org_id;
871 return(txn_source);
872 elsif Type_id = 10 then
873 select physical_inventory_name into txn_source
874 from mtl_physical_inventories
875 where physical_inventory_id = to_number(txn_source_id)
876 and organization_id = org_id;
877 return(txn_source);
878 elsif Type_id = 11 then
879 select description into txn_source
880 from cst_cost_updates
881 where cost_update_id = to_number(txn_source_id);
882 return(txn_source);
883 /*elsif Type_id in (2,8,12) then
884 BEGIN
885 SRW.USER_EXIT('FND FLEXIDVAL CODE="MKTS" NUM=":P_STRUCT_NUM"
886 APPL_SHORT_NAME="INV" DATA=":source"
887 VALUE=":C_source" DISPLAY="ALL"');
888 RETURN(C_source);
889 EXCEPTION when srw.user_exit_failure
890 then return('Flexidval Error');
891 END;
892 elsif Type_id = 3 then
893 BEGIN
894 SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":P_ACCT_STRUCT_NUM"
895 APPL_SHORT_NAME="SQLGL" DATA=":source"
896 VALUE=":C_source" DISPLAY="ALL"');
897 RETURN(C_source);
898 EXCEPTION when srw.user_exit_failure
899 then return('Flexidval Error');
900 END;
901 elsif Type_id = 6 then
902 BEGIN
903 SRW.USER_EXIT('FND FLEXIDVAL CODE="MDSP" NUM=":P_STRUCT_NUM"
904 APPL_SHORT_NAME="INV" DATA=":source"
905 VALUE=":C_source" DISPLAY="ALL"');
906 RETURN(C_source);
907 EXCEPTION when srw.user_exit_failure
908 then return('Flexidval Error');
909 END;*/
910 else
911 return(source);
912 end if;
913 exception
914 when NO_DATA_FOUND then return('Error: No Data');
915 end;
916 RETURN NULL; end;
917 function C_Source1Formula(Source1 in varchar2, Type_id1 in number) return VARCHAR2 is
918 begin
919 declare
920 txn_source_id VARCHAR2(480);
921 txn_source VARCHAR2(480);
922 org_id number;
923 begin
924 txn_source_id := Source1;
925 org_id := P_org_id;
926 if Type_id1 = 1 then select segment1 into txn_source
927 from po_headers_all
928 where po_header_id = to_number(txn_source_id);
929 return(txn_source);
930 elsif Type_id1 = 4 then --For Bug#3919355
931 select request_number into txn_source
932 from mtl_txn_request_headers
933 where header_id = to_number(txn_source_id);
934 return(txn_source);
935 elsif Type_id1 = 5 then
936 select wip_entity_name into txn_source
937 from wip_entities
938 where wip_entity_id = to_number(txn_source_id);
939 return(txn_source);
940 elsif Type_id1 = 7 then select segment1 into txn_source
941 from po_requisition_headers_all
942 where requisition_header_id =
943 txn_source_id;
944 return(txn_source);
945 elsif Type_id1 = 9 then
946 select CYCLE_COUNT_HEADER_NAME into txn_source
947 from mtl_cycle_count_headers
948 where cycle_count_header_id = to_number(txn_source_id)
949 and organization_id = org_id;
950 return(txn_source);
951 elsif Type_id1 = 10 then
952 select physical_inventory_name into txn_source
953 from mtl_physical_inventories
954 where physical_inventory_id = to_number(txn_source_id)
955 and organization_id = org_id;
956 return(txn_source);
957 elsif Type_id1 = 11 then
958 select description into txn_source
959 from cst_cost_updates
960 where cost_update_id = to_number(txn_source_id);
961 return(txn_source);
962 /*elsif Type_id1 in (2,8,12) then
963 BEGIN
964 SRW.USER_EXIT('FND FLEXIDVAL CODE="MKTS" NUM=":P_STRUCT_NUM"
965 APPL_SHORT_NAME="INV" DATA=":source1"
966 VALUE=":C_source1" DISPLAY="ALL"');
967 RETURN(C_source1);
968 EXCEPTION when srw.user_exit_failure
969 then return('Flexidval Error');
970 END;
971 elsif Type_id1 = 3 then
972 BEGIN
973 SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":P_ACCT_STRUCT_NUM"
974 APPL_SHORT_NAME="SQLGL" DATA=":source1"
975 VALUE=":C_source1" DISPLAY="ALL"');
976 RETURN(C_source1);
977 EXCEPTION when srw.user_exit_failure
978 then return('Flexidval Error');
979 END;
980 elsif Type_id1 = 6 then
981 BEGIN
982 SRW.USER_EXIT('FND FLEXIDVAL CODE="MDSP" NUM=":P_STRUCT_NUM"
983 APPL_SHORT_NAME="INV" DATA=":source1"
984 VALUE=":C_source1" DISPLAY="ALL"');
985 RETURN(C_source1);
986 EXCEPTION when srw.user_exit_failure
987 then return('Flexidval Error');
988 END;*/
989 else
990 return(source1);
991 end if;
992 exception
993 when NO_DATA_FOUND then return('');
994 end;
995 RETURN NULL; end;
996 function C_Source2Formula(Source2 in varchar2, Type_id2 in number) return VARCHAR2 is
997 begin
998 declare
999 txn_source_id VARCHAR2(480);
1000 txn_source VARCHAR2(480);
1001 org_id number;
1002 begin
1003 txn_source_id := Source2;
1004 org_id := P_org_id;
1005 if Type_id2 = 1 then select segment1 into txn_source
1006 from po_headers_all
1007 where po_header_id = to_number(txn_source_id);
1008 return(txn_source);
1009 elsif Type_id2 = 4 then --For Bug#3919355
1010 select request_number into txn_source
1011 from mtl_txn_request_headers
1012 where header_id = to_number(txn_source_id);
1013 return(txn_source);
1014 elsif Type_id2 = 5 then
1015 select wip_entity_name into txn_source
1016 from wip_entities
1017 where wip_entity_id = to_number(txn_source_id);
1018 return(txn_source);
1019 elsif Type_id2 = 7 then select segment1 into txn_source
1020 from po_requisition_headers_all
1021 where requisition_header_id =
1022 txn_source_id;
1023 return(txn_source);
1024 elsif Type_id2 = 9 then
1025 select CYCLE_COUNT_HEADER_NAME into txn_source
1026 from mtl_cycle_count_headers
1027 where cycle_count_header_id = to_number(txn_source_id)
1028 and organization_id = org_id;
1029 return(txn_source);
1030 elsif Type_id2 = 10 then
1031 select physical_inventory_name into txn_source
1032 from mtl_physical_inventories
1033 where physical_inventory_id = to_number(txn_source_id)
1034 and organization_id = org_id;
1035 return(txn_source);
1036 elsif Type_id2 = 11 then
1037 select description into txn_source
1038 from cst_cost_updates
1039 where cost_update_id = to_number(txn_source_id);
1040 return(txn_source);
1041 /*elsif Type_id2 in (2,8,12) then
1042 BEGIN
1043 SRW.USER_EXIT('FND FLEXIDVAL CODE="MKTS" NUM=":P_STRUCT_NUM"
1044 APPL_SHORT_NAME="INV" DATA=":source2"
1045 VALUE=":C_source2" DISPLAY="ALL"');
1046 RETURN(C_source2);
1047 EXCEPTION when srw.user_exit_failure
1048 then return('Flexidval Error');
1049 END;
1050 elsif Type_id2 = 3 then
1051 BEGIN
1052 SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":P_ACCT_STRUCT_NUM"
1053 APPL_SHORT_NAME="SQLGL" DATA=":source2"
1054 VALUE=":C_source2" DISPLAY="ALL"');
1055 RETURN(C_source2);
1056 EXCEPTION when srw.user_exit_failure
1057 then return('Flexidval Error');
1058 END;
1059 elsif Type_id2 = 6 then
1060 BEGIN
1061 SRW.USER_EXIT('FND FLEXIDVAL CODE="MDSP" NUM=":P_STRUCT_NUM"
1062 APPL_SHORT_NAME="INV" DATA=":source2"
1063 VALUE=":C_source2" DISPLAY="ALL"');
1064 RETURN(C_source2);
1065 EXCEPTION when srw.user_exit_failure
1066 then return('Flexidval Error');
1067 END;*/
1068 else
1069 return(source2);
1070 end if;
1071 exception
1072 when NO_DATA_FOUND then return('');
1073 end;
1074 RETURN NULL; end;
1075 function C_Source3Formula(Source3 in varchar2, Type_id3 in number) return VARCHAR2 is
1076 begin
1077 declare
1078 txn_source_id VARCHAR2(480);
1079 txn_source VARCHAR2(480);
1080 org_id number;
1081 begin
1082 txn_source_id := Source3;
1083 org_id := P_org_id;
1084 if Type_id3 = 1 then select segment1 into txn_source
1085 from po_headers_all
1086 where po_header_id = to_number(txn_source_id);
1087 return(txn_source);
1088 elsif Type_id3 = 4 then --For Bug#3919355
1089 select request_number into txn_source
1090 from mtl_txn_request_headers
1091 where header_id = to_number(txn_source_id);
1092 return(txn_source);
1093 elsif Type_id3 = 5 then
1094 select wip_entity_name into txn_source
1095 from wip_entities
1096 where wip_entity_id = to_number(txn_source_id);
1097 return(txn_source);
1098 elsif Type_id3 = 7 then select segment1 into txn_source
1099 from po_requisition_headers_all
1100 where requisition_header_id =
1101 txn_source_id;
1102 return(txn_source);
1103 elsif Type_id3 = 9 then
1104 select CYCLE_COUNT_HEADER_NAME into txn_source
1105 from mtl_cycle_count_headers
1106 where cycle_count_header_id = to_number(txn_source_id)
1107 and organization_id = org_id;
1108 return(txn_source);
1109 elsif Type_id3 = 10 then
1110 select physical_inventory_name into txn_source
1111 from mtl_physical_inventories
1112 where physical_inventory_id = to_number(txn_source_id)
1113 and organization_id = org_id;
1114 return(txn_source);
1115 elsif Type_id3 = 11 then
1116 select description into txn_source
1117 from cst_cost_updates
1118 where cost_update_id = to_number(txn_source_id);
1119 return(txn_source);
1120 /*elsif Type_id3 in (2,8,12) then
1121 BEGIN
1122 SRW.USER_EXIT('FND FLEXIDVAL CODE="MKTS" NUM=":P_STRUCT_NUM"
1123 APPL_SHORT_NAME="INV" DATA=":source3"
1124 VALUE=":C_source3" DISPLAY="ALL"');
1125 RETURN(C_source3);
1126 EXCEPTION when srw.user_exit_failure
1127 then return('Flexidval Error');
1128 END;
1129 elsif Type_id3 = 3 then
1130 BEGIN
1131 SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":P_ACCT_STRUCT_NUM"
1132 APPL_SHORT_NAME="SQLGL" DATA=":source3"
1133 VALUE=":C_source3" DISPLAY="ALL"');
1134 RETURN(C_source3);
1135 EXCEPTION when srw.user_exit_failure
1136 then return('Flexidval Error');
1137 END;
1138 elsif Type_id3 = 6 then
1139 BEGIN
1140 SRW.USER_EXIT('FND FLEXIDVAL CODE="MDSP" NUM=":P_STRUCT_NUM"
1141 APPL_SHORT_NAME="INV" DATA=":source3"
1142 VALUE=":C_source3" DISPLAY="ALL"');
1143 RETURN(C_source3);
1144 EXCEPTION when srw.user_exit_failure
1145 then return('Flexidval Error');
1146 END;*/
1147 else
1148 return(source3);
1149 end if;
1150 exception
1151 when NO_DATA_FOUND then return('');
1152 end;
1153 RETURN NULL; end;
1154 function C_Source4Formula(Source4 in varchar2, Type_id4 in number) return VARCHAR2 is
1155 begin
1156 declare
1157 txn_source_id VARCHAR2(480);
1158 txn_source VARCHAR2(480);
1159 org_id number;
1160 begin
1161 txn_source_id := Source4;
1162 org_id := P_org_id;
1163 if Type_id4 = 1 then select segment1 into txn_source
1164 from po_headers_all
1165 where po_header_id = to_number(txn_source_id);
1166 return(txn_source);
1167 elsif Type_id4 = 4 then --For Bug#3919355
1168 select request_number into txn_source
1169 from mtl_txn_request_headers
1170 where header_id = to_number(txn_source_id);
1171 return(txn_source);
1172 elsif Type_id4 = 5 then
1173 select wip_entity_name into txn_source
1174 from wip_entities
1175 where wip_entity_id = to_number(txn_source_id);
1176 return(txn_source);
1177 elsif Type_id4 = 7 then select segment1 into txn_source
1178 from po_requisition_headers_all
1179 where requisition_header_id =
1180 txn_source_id;
1181 return(txn_source);
1182 elsif Type_id4 = 9 then
1183 select CYCLE_COUNT_HEADER_NAME into txn_source
1184 from mtl_cycle_count_headers
1185 where cycle_count_header_id = to_number(txn_source_id)
1186 and organization_id = org_id;
1187 return(txn_source);
1188 elsif Type_id4 = 10 then
1189 select physical_inventory_name into txn_source
1190 from mtl_physical_inventories
1191 where physical_inventory_id = to_number(txn_source_id)
1192 and organization_id = org_id;
1193 return(txn_source);
1194 elsif Type_id4 = 11 then
1195 select description into txn_source
1196 from cst_cost_updates
1197 where cost_update_id = to_number(txn_source_id);
1198 return(txn_source);
1199 /*elsif Type_id4 in (2,8,12) then
1200 BEGIN
1201 SRW.USER_EXIT('FND FLEXIDVAL CODE="MKTS" NUM=":P_STRUCT_NUM"
1202 APPL_SHORT_NAME="INV" DATA=":source4"
1203 VALUE=":C_source4" DISPLAY="ALL"');
1204 RETURN(C_source4);
1205 EXCEPTION when srw.user_exit_failure
1206 then return('Flexidval Error');
1207 END;
1208 elsif Type_id4 = 3 then
1209 BEGIN
1210 SRW.USER_EXIT('FND FLEXIDVAL CODE="GL#" NUM=":P_ACCT_STRUCT_NUM"
1211 APPL_SHORT_NAME="SQLGL" DATA=":source4"
1212 VALUE=":C_source4" DISPLAY="ALL"');
1213 RETURN(C_source4);
1214 EXCEPTION when srw.user_exit_failure
1215 then return('Flexidval Error');
1216 END;
1217 elsif Type_id4 = 6 then
1218 BEGIN
1219 SRW.USER_EXIT('FND FLEXIDVAL CODE="MDSP" NUM=":P_STRUCT_NUM"
1220 APPL_SHORT_NAME="INV" DATA=":source4"
1221 VALUE=":C_source4" DISPLAY="ALL"');
1222 RETURN(C_source4);
1223 EXCEPTION when srw.user_exit_failure
1224 then return('Flexidval Error');
1225 END;*/
1226 else
1227 return(source4);
1228 end if;
1229 exception
1230 when NO_DATA_FOUND then return('');
1231 end;
1232 RETURN NULL; end;
1233 END INV_INVTRDST_XMLP_PKG;
1234
1235
[Home] [Help]