1 PACKAGE BODY INV_INVIRDIS_XMLP_PKG AS
2 /* $Header: INVIRDISB.pls 120.1 2007/12/25 10:22:50 dwkrishn noship $ */
3 FUNCTION WHERE_STAT_EFF RETURN VARCHAR2 IS
4 BEGIN
5 IF P_STAT_EFF IS NOT NULL THEN
6 RETURN ('and to_date(to_char(mpis.EFFECTIVE_DATE,''DD-MON-RR''), ''DD-MON-RR'') >= ''' || TO_CHAR(P_STAT_EFF) || ''' ');
7 ELSE
8 RETURN (' ');
9 END IF;
10 RETURN NULL;
11 END WHERE_STAT_EFF;
12
13 FUNCTION WHERE_STATUS RETURN VARCHAR2 IS
14 BEGIN
15 IF P_STATUS IS NOT NULL THEN
16 RETURN ('and MPIS.STATUS_CODE = ''' || P_STATUS || ''' ');
17 ELSE
18 RETURN (' ');
19 END IF;
20 RETURN NULL;
21 END WHERE_STATUS;
22
23 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
24 BEGIN
25 BEGIN
26 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
27 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
28 EXCEPTION
29 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
30 /*SRW.MESSAGE(1
31 ,'Failed srwinit in before rpt trigger')*/NULL;
32 RAISE;
33 END;
34 BEGIN
35 IF P_BREAK_ID = 1 OR P_BREAK_ID = 2 THEN
36 NULL;
37 ELSE
38 P_CAT_FLEX := '''MC''';
39 END IF;
40 EXCEPTION
41 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
42 /*SRW.MESSAGE(2
43 ,'Failed Flexsql MCAT select in before rpt trig')*/NULL;
44 RAISE;
45 END;
46 BEGIN
47 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
48 NULL;
49 ELSE
50 NULL;
51 END IF;
52 EXCEPTION
53 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
54 /*SRW.MESSAGE(3
55 ,'Failed Flexsql MCAT Where in before rpt trig')*/NULL;
56 RAISE;
57 END;
58 BEGIN
59 NULL;
60 EXCEPTION
61 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
62 /*SRW.MESSAGE(6
63 ,'Failed Flexsql Item Select in before rpt trig')*/NULL;
64 RAISE;
65 END;
66 BEGIN
67 IF P_ITEM_LO IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
68 NULL;
69 ELSE
70 NULL;
71 END IF;
72 EXCEPTION
73 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
74 /*SRW.MESSAGE(7
75 ,'Failed Flexsql MSTK Where in before rpt trig')*/NULL;
76 RAISE;
77 END;
78 BEGIN
79 NULL;
80 EXCEPTION
81 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
82 /*SRW.MESSAGE(8
83 ,'Failed Flexsql Item Order by in before rpt trig')*/NULL;
84 RAISE;
85 END;
86 RETURN (TRUE);
87 END BEFOREREPORT;
88
89 FUNCTION AFTERREPORT RETURN BOOLEAN IS
90 BEGIN
91 BEGIN
92 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
93 EXCEPTION
94 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
95 /*SRW.MESSAGE(1
96 ,'SRWEXIT failed')*/NULL;
97 END;
98 RETURN (TRUE);
99 END AFTERREPORT;
100
101 FUNCTION C_CAT_FROMFORMULA RETURN VARCHAR2 IS
102 BEGIN
103 IF P_BREAK_ID = 1 OR (P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL) OR (P_CAT_SET_ID IS NOT NULL AND P_CAT_LO IS NULL AND P_CAT_HI IS NULL) THEN
104 RETURN (',mtl_item_categories mic, mtl_categories mc');
105 ELSE
106 RETURN ('/* Do not select mic or mc */');
107 END IF;
108 RETURN NULL;
109 END C_CAT_FROMFORMULA;
110
111 FUNCTION C_CAT_WHEREFORMULA RETURN VARCHAR2 IS
112 BEGIN
113 IF P_BREAK_ID = 1 OR (P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL) OR P_BREAK_ID = 2 THEN
114 RETURN ('and msi.inventory_item_id = mic.inventory_item_id
115 and mic.category_id = mc.category_id
116 and mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID) || '
117 and mic.organization_id = ' || TO_CHAR(P_ORG_ID));
118 ELSE
119 RETURN ('');
120 END IF;
121 RETURN NULL;
122 END C_CAT_WHEREFORMULA;
123
124 FUNCTION C_CAT_PADFORMULA(C_CAT_FIELD IN VARCHAR2
125 ,C_CAT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
126 BEGIN
127 /*SRW.REFERENCE(C_CAT_FIELD)*/NULL;
128 RETURN (C_CAT_PAD);
129 END C_CAT_PADFORMULA;
130
131 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
132 BEGIN
133 DECLARE
134 NAME VARCHAR2(30);
135 SET_ID NUMBER;
136 BEGIN
137 IF P_CAT_SET_ID IS NULL THEN
138 RETURN ('');
139 ELSE
140 SET_ID := P_CAT_SET_ID;
141 SELECT
142 CATEGORY_SET_NAME
143 INTO NAME
144 FROM
145 MTL_CATEGORY_SETS
146 WHERE CATEGORY_SET_ID = SET_ID;
147 RETURN (NAME);
148 END IF;
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 RETURN ('');
152 END;
153 RETURN NULL;
154 END C_CAT_SET_NAMEFORMULA;
155
156 FUNCTION C_ITEM_PADFORMULA(C_ITEM_FIELD IN VARCHAR2
157 ,C_ITEM_PAD IN VARCHAR2) RETURN VARCHAR2 IS
158 BEGIN
159 /*SRW.REFERENCE(C_ITEM_FIELD)*/NULL;
160 RETURN (C_ITEM_PAD);
161 END C_ITEM_PADFORMULA;
162
163 FUNCTION AFTERPFORM RETURN BOOLEAN IS
164 BEGIN
165 RETURN (TRUE);
166 END AFTERPFORM;
167
168 FUNCTION C_MORG_IDFORMULA RETURN VARCHAR2 IS
169 BEGIN
170 DECLARE
171 T_STATUS INTEGER;
172 T_MORG_ID INTEGER;
173 BEGIN
174 SELECT
175 CONTROL_LEVEL
176 INTO T_STATUS
177 FROM
178 MTL_ITEM_ATTRIBUTES
179 WHERE ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
180 IF (T_STATUS = 1) THEN
181 BEGIN
182 SELECT
183 MASTER_ORGANIZATION_ID
184 INTO T_MORG_ID
185 FROM
186 MTL_PARAMETERS P
187 WHERE P.ORGANIZATION_ID = P_ORG_ID;
188 RETURN (T_MORG_ID);
189 END;
190 ELSE
191 RETURN (P_ORG_ID);
192 END IF;
193 END;
194 RETURN NULL;
195 END C_MORG_IDFORMULA;
196
197 END INV_INVIRDIS_XMLP_PKG;
198
[Home] [Help]