1 PACKAGE BODY GMI_PIR01_XMLP_PKG AS
2 /* $Header: PIR01B.pls 120.1 2007/12/27 11:29:13 nchinnam noship $ */
3 FUNCTION ORDCFFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 IF SORT_BY = '2' THEN
6 IF ORDER1 = '1' THEN
7 RETURN ('1 ASC,12 ASC ,r.whse_code asc');
8 ELSE
9 RETURN ('1 ASC,12 ASC ,r.whse_code desc');
10 END IF;
11 ELSIF SORT_BY = '1' THEN
12 IF ORDER1 = '1' THEN
13 RETURN ('R.abc_code asc');
14 ELSE
15 RETURN ('R.abc_code desc');
16 END IF;
17 END IF;
18 RETURN 'r.whse_code';
19 END ORDCFFORMULA;
20 FUNCTION RANGECFFORMULA RETURN VARCHAR2 IS
21 X_CLAUSE VARCHAR2(150);
22 X_AND VARCHAR2(5);
23 BEGIN
24 IF (FROM_WHSE IS NOT NULL AND TO_WHSE IS NOT NULL) THEN
25 X_CLAUSE := 'r.whse_code>=''' || FROM_WHSE || ''' and r.whse_code<= ''' || TO_WHSE || '''';
26 ELSIF (FROM_WHSE IS NOT NULL AND TO_WHSE IS NULL) THEN
27 X_CLAUSE := 'r.whse_code>=''' || FROM_WHSE || '''';
28 END IF;
29 IF X_CLAUSE IS NOT NULL THEN
30 X_AND := ' AND ';
31 END IF;
32 IF (FROM_RANK IS NOT NULL AND TO_RANK IS NOT NULL) THEN
33 X_CLAUSE := X_CLAUSE || X_AND || 'abc_code>=''' || FROM_RANK || ''' and abc_code<= ''' || TO_RANK || ''' and r.whse_code>=''' || RFROM_WHSE || ''' and r.whse_code<= ''' || RTO_WHSE || '''';
34 ELSIF (FROM_RANK IS NOT NULL AND TO_RANK IS NULL) THEN
35 X_CLAUSE := X_CLAUSE || X_AND || 'abc_code >=''' || FROM_RANK || ''' and r.whse_code>=''' || RFROM_WHSE || ''' ';
36 END IF;
37 IF X_CLAUSE is null then
38 X_CLAUSE:='r.whse_code>=''0''';
39 end if;
40 RETURN X_CLAUSE;
41 END RANGECFFORMULA;
42 FUNCTION AFTERPFORM RETURN BOOLEAN IS
43 BEGIN
44 IF FROM_WHSE > TO_WHSE THEN
45 /*SRW.MESSAGE(100
46 ,GGM_MESSAGE.GET('IC_FROM_LTE_THRU'))*/NULL;
47 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
48 END IF;
49 IF FROM_RANK > TO_RANK THEN
50 /*SRW.MESSAGE(100
51 ,GGM_MESSAGE.GET('IC_FROM_LTE_THRU'))*/NULL;
52 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
53 END IF;
54 IF FROM_WHSE IS NULL THEN
55 IF TO_WHSE IS NOT NULL THEN
56 /*SRW.MESSAGE(100
57 ,GGM_MESSAGE.GET('IC_FRM_REQD_FOR_THRU'))*/NULL;
58 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
59 END IF;
60 END IF;
61 IF FROM_RANK IS NULL THEN
62 IF TO_RANK IS NOT NULL THEN
63 /*SRW.MESSAGE(100
64 ,GGM_MESSAGE.GET('IC_FRM_REQD_FOR_THRU'))*/NULL;
65 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
66 END IF;
67 END IF;
68 RFROM_WHSE := SUBSTR(FROM_RANK
69 ,INSTR(FROM_RANK
70 ,':') + 1);
71 RTO_WHSE := SUBSTR(TO_RANK
72 ,INSTR(TO_RANK
73 ,':') + 1);
74 FROM_RANK := SUBSTR(FROM_RANK
75 ,1
76 ,INSTR(FROM_RANK
77 ,':') - 1);
78 TO_RANK := SUBSTR(TO_RANK
79 ,1
80 ,INSTR(TO_RANK
81 ,':') - 1);
82 RETURN (TRUE);
83 END AFTERPFORM;
84 FUNCTION FROM_TOCFFORMULA RETURN VARCHAR2 IS
85 RANGEV VARCHAR2(200);
86 BEGIN
87 IF SORT_BY = '2' THEN
88 SELECT
89 DECODE(FROM_WHSE
90 ,NULL
91 ,DECODE(TO_WHSE
92 ,NULL
93 ,' Range All '
94 ,' Range From: All Range To: ' || TO_WHSE)
95 ,DECODE(TO_WHSE
96 ,NULL
97 ,' Range From ' || FROM_WHSE || ' Range To All '
98 ,' Range From ' || FROM_WHSE || ' Range To ' || TO_WHSE))
99 INTO RANGEV
100 FROM
101 DUAL;
102 ELSIF SORT_BY = '1' THEN
103 SELECT
104 DECODE(FROM_RANK
105 ,NULL
106 ,DECODE(TO_RANK
107 ,NULL
108 ,' Range All '
109 ,' Range From: All Range To: ' || TO_RANK)
110 ,DECODE(TO_RANK
111 ,NULL
112 ,' Range From ' || FROM_RANK || ' Range To All '
113 ,' Range From ' || FROM_RANK || ' Range To ' || TO_RANK))
114 INTO RANGEV
115 FROM
116 DUAL;
117 END IF;
118 RETURN (RANGEV);
119 END FROM_TOCFFORMULA;
120 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
121 BEGIN
122 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
123 select argument2 into order1 from FND_CONCURRENT_REQUESTS
124 where request_id =P_CONC_REQUEST_ID;
125 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
126 P_ROWS := 0;
127 RETURN (TRUE);
128 END BEFOREREPORT;
129 FUNCTION ORDERCFFORMULA RETURN VARCHAR2 IS
130 ORDER2 VARCHAR2(17);
131 BEGIN
132 IF ORDER1 = '1' THEN
133 SELECT
134 MEANING
135 INTO ORDER2 FROM
136 FND_LOOKUP_VALUES_VL
137 WHERE LOOKUP_CODE = '1'
138 AND LOOKUP_TYPE = 'PI_ORDER';
139 ELSIF ORDER1 = '2' THEN
140 SELECT
141 MEANING
142 INTO ORDER2
143 FROM
144 FND_LOOKUP_VALUES_VL
145 WHERE LOOKUP_CODE = '2'
146 AND LOOKUP_TYPE = 'PI_ORDER';
147 END IF;
148 RETURN (ORDER2);
149 END ORDERCFFORMULA;
150 FUNCTION SORTRETCFFORMULA RETURN VARCHAR2 IS
151 SORT1 VARCHAR2(17);
152 BEGIN
153 IF SORT_BY = '1' THEN
154 SELECT
155 MEANING
156 INTO SORT1
157 FROM
158 FND_LOOKUP_VALUES_VL
159 WHERE LOOKUP_CODE = '1'
160 AND LOOKUP_TYPE = 'PI_PIR01_SORT';
161 ELSIF SORT_BY = '2' THEN
162 SELECT
163 MEANING
164 INTO SORT1
165 FROM
166 FND_LOOKUP_VALUES_VL
167 WHERE LOOKUP_CODE = '2'
168 AND LOOKUP_TYPE = 'PI_PIR01_SORT';
169 END IF;
170 RETURN (SORT1);
171 END SORTRETCFFORMULA;
172 FUNCTION SELECTCFFORMULA RETURN VARCHAR2 IS
173 SELECT1 VARCHAR2(17);
174 BEGIN
175 IF SELECT_CRITERIA = '1' THEN
176 SELECT
177 MEANING
178 INTO SELECT1
179 FROM
180 FND_LOOKUP_VALUES_VL
181 WHERE LOOKUP_CODE = '1'
182 AND LOOKUP_TYPE = 'PI_SELECTCRITERIA';
183 ELSIF SELECT_CRITERIA = '2' THEN
184 SELECT
185 MEANING
186 INTO SELECT1
187 FROM
188 FND_LOOKUP_VALUES_VL
189 WHERE LOOKUP_CODE = '2'
190 AND LOOKUP_TYPE = 'PI_SELECTCRITERIA';
191 ELSIF SELECT_CRITERIA = '3' THEN
192 SELECT
193 MEANING
194 INTO SELECT1
195 FROM
196 FND_LOOKUP_VALUES_VL
197 WHERE LOOKUP_CODE = '3'
198 AND LOOKUP_TYPE = 'PI_SELECTCRITERIA';
199 END IF;
200 RETURN (SELECT1);
201 END SELECTCFFORMULA;
202 PROCEDURE GMI_PIR01_XMLP_PKG_HEADER IS
203 BEGIN
204 NULL;
205 END GMI_PIR01_XMLP_PKG_HEADER;
206 FUNCTION AFTERREPORT RETURN BOOLEAN IS
207 BEGIN
208 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
209 RETURN (TRUE);
210 END AFTERREPORT;
211 END GMI_PIR01_XMLP_PKG;
212