1 PACKAGE BODY INV_INVIRSNO_XMLP_PKG AS
2 /* $Header: INVIRSNOB.pls 120.1 2007/12/25 10:33:59 dwkrishn noship $ */
3 FUNCTION WHERE_SERIAL_NUM RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 LO VARCHAR2(30);
7 HI VARCHAR2(30);
8 BEGIN
9 LO := P_SERIAL_LO;
10 HI := P_SERIAL_HI;
11 IF P_SERIAL_LO IS NULL AND P_SERIAL_HI IS NULL THEN
12 RETURN (' ');
13 ELSE
14 IF P_SERIAL_LO IS NOT NULL AND P_SERIAL_HI IS NULL THEN
15 RETURN (' and msn.serial_number >= ''' || LO || ''' ');
16 ELSE
17 IF P_SERIAL_LO IS NULL AND P_SERIAL_HI IS NOT NULL THEN
18 RETURN (' and msn.serial_number <= ''' || HI || ''' ');
19 ELSE
20 RETURN (' and msn.serial_number between ''' || LO || ''' and ''' || HI || ''' ');
21 END IF;
22 END IF;
23 END IF;
24 END;
25 RETURN NULL;
26 END WHERE_SERIAL_NUM;
27
28 FUNCTION WHERE_VENDOR RETURN VARCHAR2 IS
29 BEGIN
30 DECLARE
31 LO VARCHAR2(30);
32 HI VARCHAR2(30);
33 BEGIN
34 LO := P_VENDOR_LO;
35 HI := P_VENDOR_HI;
36 IF P_VENDOR_LO IS NULL AND P_VENDOR_HI IS NULL THEN
37 RETURN (' ');
38 ELSE
39 IF P_VENDOR_LO IS NOT NULL AND P_VENDOR_HI IS NULL THEN
40 RETURN (' and pov.vendor_name >= ''' || LO || ''' ');
41 ELSE
42 IF P_VENDOR_LO IS NULL AND P_VENDOR_HI IS NOT NULL THEN
43 RETURN (' and pov.vendor_name >= ''' || HI || ''' ');
44 ELSE
45 RETURN (' and pov.vendor_name between ''' || LO || ''' and ''' || HI || ''' ');
46 END IF;
47 END IF;
48 END IF;
49 END;
50 RETURN NULL;
51 END WHERE_VENDOR;
52
53 FUNCTION WHERE_VEND_SN RETURN VARCHAR2 IS
54 BEGIN
55 DECLARE
56 LO VARCHAR2(30);
57 HI VARCHAR2(30);
58 BEGIN
59 LO := P_VEND_SERIAL_LO;
60 HI := P_VEND_SERIAL_HI;
61 IF P_VEND_SERIAL_LO IS NULL AND P_VEND_SERIAL_HI IS NULL THEN
62 RETURN (' ');
63 ELSE
64 IF P_VEND_SERIAL_LO IS NOT NULL AND P_VEND_SERIAL_HI IS NULL THEN
65 RETURN (' and msn.vendor_serial_number >= ''' || LO || ''' ');
66 ELSE
67 IF P_VEND_SERIAL_LO IS NULL AND P_VEND_SERIAL_HI IS NOT NULL THEN
68 RETURN (' and msn.vendor_serial_number <= ''' || HI || ''' ');
69 ELSE
70 RETURN (' and msn.vendor_serial_number between ''' || LO || ''' and ''' || HI || ''' ');
71 END IF;
72 END IF;
73 END IF;
74 END;
75 RETURN NULL;
76 END WHERE_VEND_SN;
77
78 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
79 BEGIN
80 BEGIN
81 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
82 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
83 EXCEPTION
84 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
85 /*SRW.MESSAGE(0
86 ,'Failed srwinit, before report trigger')*/NULL;
87 RAISE;
88 END;
89 DECLARE
90 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG);
91 BEGIN
92 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
93 EXCEPTION
94 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
95 /*SRW.MESSAGE(020
96 ,'Failed in before report trigger, setting org profile ')*/NULL;
97 RAISE;
98 END;
99 BEGIN
100 NULL;
101 EXCEPTION
102 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
103 /*SRW.MESSAGE(2
104 ,'Failed flexsql loc select, before report trigger')*/NULL;
105 RAISE;
106 END;
107 BEGIN
108 NULL;
109 EXCEPTION
110 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
111 /*SRW.MESSAGE(4
112 ,'Failed flexsql item select, before report trigger')*/NULL;
113 RAISE;
114 END;
115 BEGIN
116 NULL;
117 EXCEPTION
118 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
119 /*SRW.MESSAGE(5
120 ,'Failed flexsql item order by, before report trigger')*/NULL;
121 RAISE;
122 END;
123 BEGIN
124 NULL;
125 EXCEPTION
126 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
127 /*SRW.MESSAGE(8
128 ,'Failed flexsql item where, before report trigger')*/NULL;
129 RAISE;
130 END;
131 BEGIN
132 NULL;
133 EXCEPTION
134 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
135 /*SRW.MESSAGE(12
136 ,'Failed flexsql MKTS select, before report trigger')*/NULL;
137 RAISE;
138 END;
139 BEGIN
140 NULL;
141 EXCEPTION
142 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
143 /*SRW.MESSAGE(16
144 ,'Failed flexsql MDSP select, before report trigger')*/NULL;
145 RAISE;
146 END;
147 BEGIN
148 NULL;
149 EXCEPTION
150 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
151 /*SRW.MESSAGE(20
152 ,'Failed flexsql GL# select, before report trigger')*/NULL;
153 RAISE;
154 END;
155 RETURN (TRUE);
156 END BEFOREREPORT;
157
158 FUNCTION C_UNIT_STATUS_WHEREFORMULA RETURN VARCHAR2 IS
159 BEGIN
160 BEGIN
161 IF P_UNIT_STATUS IS NULL THEN
162 return(' ');
163 ELSE
164 RETURN (' and msn.current_status = ' || TO_CHAR(P_UNIT_STATUS));
165 END IF;
166 END;
167 RETURN(' ');
168 END C_UNIT_STATUS_WHEREFORMULA;
169
170 FUNCTION C_SOURCE_TYPE_WHEREFORMULA RETURN VARCHAR2 IS
171 BEGIN
172 BEGIN
173 IF P_UNIT_STATUS IS NOT NULL THEN
174 IF P_SOURCE_TYPE IS NULL THEN
175 return(' ');
176 ELSE
177 RETURN (' and msn.last_txn_source_type_id = ' || TO_CHAR(P_SOURCE_TYPE));
178 END IF;
179 ELSE
180 return(' ');
181 END IF;
182 END;
183 RETURN (' ');
184 END C_SOURCE_TYPE_WHEREFORMULA;
185
186 FUNCTION AFTERPFORM RETURN BOOLEAN IS
187 BEGIN
188 RETURN (TRUE);
189 END AFTERPFORM;
190
191 FUNCTION AFTERREPORT RETURN BOOLEAN IS
192 BEGIN
193 BEGIN
194 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
195 EXCEPTION
196 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
197 /*SRW.MESSAGE(1
198 ,'SRWEXIT failed')*/NULL;
199 END;
200 RETURN (TRUE);
201 END AFTERREPORT;
202
203 END INV_INVIRSNO_XMLP_PKG;
204