1 PACKAGE BODY GMI_CMXFER02_XMLP_PKG AS
2 /* $Header: CMXFER02B.pls 120.2 2007/12/27 15:48:06 nchinnam noship $ */
3 FUNCTION RELEASEDATECFFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 IF P_FROM_RELEASE_DATE IS NOT NULL AND P_TO_RELEASE_DATE IS NOT NULL THEN
6 RELEASEDATECP := ' and trunc(xf.actual_release_date) between ''' || P_FROM_RELEASE_DATE || '''
7 and ''' || P_TO_RELEASE_DATE || '''';
8 ELSIF P_FROM_RELEASE_DATE IS NULL AND P_TO_RELEASE_DATE IS NULL THEN
9 RELEASEDATECP := ' ';
10 ELSIF P_FROM_RELEASE_DATE IS NOT NULL AND P_TO_RELEASE_DATE IS NULL THEN
11 RELEASEDATECP := ' and trunc(xf.actual_release_date) >= ''' || P_FROM_RELEASE_DATE || '''';
12 ELSIF P_FROM_RELEASE_DATE IS NOT NULL AND P_TO_RELEASE_DATE IS NOT NULL THEN
13 RELEASEDATECP := ' and trunc(xf.actual_release_date) <= ''' || P_TO_RELEASE_DATE || '''';
14 END IF;
15 RETURN NULL;
16 END RELEASEDATECFFORMULA;
17
18 FUNCTION SOURCEWAREHOUSECFFORMULA RETURN VARCHAR2 IS
19 BEGIN
20 IF P_FROM_SOURCE_WAREHOUSE IS NOT NULL AND P_TO_SOURCE_WAREHOUSE IS NOT NULL THEN
21 SOURCEWAREHOUSECP := ' and xf.from_warehouse between ''' || P_FROM_SOURCE_WAREHOUSE || '''
22 and ''' || P_TO_SOURCE_WAREHOUSE || '''';
23 ELSIF P_FROM_SOURCE_WAREHOUSE IS NULL AND P_TO_SOURCE_WAREHOUSE IS NULL THEN
24 SOURCEWAREHOUSECP := ' ';
25 CP_FROM_SOURCE_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
26 CP_TO_SOURCE_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
27 ELSIF P_FROM_SOURCE_WAREHOUSE IS NOT NULL AND P_TO_SOURCE_WAREHOUSE IS NULL THEN
28 SOURCEWAREHOUSECP := ' and xf.from_warehouse >= ''' || P_FROM_SOURCE_WAREHOUSE || '''';
29 CP_TO_SOURCE_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
30 ELSIF P_FROM_SOURCE_WAREHOUSE IS NOT NULL AND P_TO_SOURCE_WAREHOUSE IS NOT NULL THEN
31 SOURCEWAREHOUSECP := ' and xf.from_warehouse <= ''' || P_TO_SOURCE_WAREHOUSE || '''';
32 CP_FROM_SOURCE_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
33 END IF;
34 RETURN NULL;
35 END SOURCEWAREHOUSECFFORMULA;
36
37 FUNCTION DESTINATIONWAREHOUSECFFORMULA RETURN VARCHAR2 IS
38 BEGIN
39 IF P_FROM_DESTINATION_WAREHOUSE IS NOT NULL AND P_TO_DESTINATION_WAREHOUSE IS NOT NULL THEN
40 DESTINATIONWAREHOUSECP := ' and xf.to_warehouse between ''' || P_FROM_DESTINATION_WAREHOUSE || '''
41 and ''' || P_TO_DESTINATION_WAREHOUSE || '''';
42 ELSIF P_FROM_DESTINATION_WAREHOUSE IS NULL AND P_TO_DESTINATION_WAREHOUSE IS NULL THEN
43 DESTINATIONWAREHOUSECP := ' ';
44 CP_FROM_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
45 CP_TO_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
46 ELSIF P_FROM_DESTINATION_WAREHOUSE IS NOT NULL AND P_TO_DESTINATION_WAREHOUSE IS NULL THEN
47 DESTINATIONWAREHOUSECP := ' and xf.to_warehouse >= ''' || P_FROM_DESTINATION_WAREHOUSE || '''';
48 CP_TO_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
49 ELSIF P_FROM_DESTINATION_WAREHOUSE IS NOT NULL AND P_TO_DESTINATION_WAREHOUSE IS NOT NULL THEN
50 DESTINATIONWAREHOUSECP := ' and xf.to_warehouse <= ''' || P_TO_DESTINATION_WAREHOUSE || '''';
51 CP_FROM_DESTINATION_WAREHOUSE := FND_PROFILE.VALUE('SY$ALL');
52 END IF;
53 RETURN NULL;
54 END DESTINATIONWAREHOUSECFFORMULA;
55
56 FUNCTION TREANSFERSTATUSCFFORMULA RETURN VARCHAR2 IS
57 BEGIN
58 IF P_FROM_TRANSFER_STATUS IS NOT NULL AND P_TO_TRANSFER_STATUS IS NOT NULL THEN
59 TRANSFERSTATUSCP := ' and xf.transfer_status between ''' || P_FROM_TRANSFER_STATUS || '''
60 and ''' || P_TO_TRANSFER_STATUS || '''';
61 ELSIF P_FROM_TRANSFER_STATUS IS NULL AND P_TO_TRANSFER_STATUS IS NULL THEN
62 TRANSFERSTATUSCP := ' ';
63 CP_FROM_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
64 CP_TO_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
65 ELSIF P_FROM_TRANSFER_STATUS IS NOT NULL AND P_TO_TRANSFER_STATUS IS NULL THEN
66 TRANSFERSTATUSCP := ' and xf.transfer_status >= ''' || P_FROM_TRANSFER_STATUS || '''';
67 CP_TO_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
68 ELSIF P_FROM_TRANSFER_STATUS IS NOT NULL AND P_TO_TRANSFER_STATUS IS NOT NULL THEN
69 TRANSFERSTATUSCP := ' and xf.transfer_status <= ''' || P_TO_TRANSFER_STATUS || '''';
70 CP_FROM_TRANSFER_STATUS := FND_PROFILE.VALUE('SY$ALL');
71 END IF;
72 RETURN NULL;
73 END TREANSFERSTATUSCFFORMULA;
74
75 FUNCTION SCHEDULEDSHIPDATECFFORMULA RETURN VARCHAR2 IS
76 BEGIN
77 IF P_FROM_SCHEDULED_RELEASE_DATE IS NOT NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NOT NULL THEN
78 SCHEDULEDSHIPDATECP := ' and trunc(xf.scheduled_release_date) between ''' || P_FROM_SCHEDULED_RELEASE_DATE || '''
79 and ''' || P_TO_SCHEDULED_RELEASE_DATE || '''';
80 ELSIF P_FROM_SCHEDULED_RELEASE_DATE IS NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NULL THEN
81 SCHEDULEDSHIPDATECP := ' ';
82 ELSIF P_FROM_SCHEDULED_RELEASE_DATE IS NOT NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NULL THEN
83 SCHEDULEDSHIPDATECP := ' and trunc(xf.scheduled_release_date) >= ''' || P_FROM_SCHEDULED_RELEASE_DATE || '''';
84 ELSIF P_FROM_SCHEDULED_RELEASE_DATE IS NOT NULL AND P_TO_SCHEDULED_RELEASE_DATE IS NOT NULL THEN
85 SCHEDULEDSHIPDATECP := ' and trunc(xf.scheduled_release_date) <= ''' || P_TO_SCHEDULED_RELEASE_DATE || '''';
86 END IF;
87 RETURN NULL;
88 END SCHEDULEDSHIPDATECFFORMULA;
89
90 FUNCTION CANCELDATECFFORMULA RETURN VARCHAR2 IS
91 BEGIN
92 IF P_FROM_CANCEL_DATE IS NOT NULL AND P_TO_CANCEL_DATE IS NOT NULL THEN
93 CANCELDATECP := ' and trunc(xf.cancel_date) between ''' || P_FROM_CANCEL_DATE || '''
94 and ''' || P_TO_CANCEL_DATE || '''';
95 ELSIF P_FROM_CANCEL_DATE IS NULL AND P_TO_CANCEL_DATE IS NULL THEN
96 CANCELDATECP := ' ';
97 ELSIF P_FROM_CANCEL_DATE IS NOT NULL AND P_TO_CANCEL_DATE IS NULL THEN
98 CANCELDATECP := ' and trunc(xf.cancel_date) >= ''' || P_FROM_CANCEL_DATE || '''';
99 ELSIF P_FROM_CANCEL_DATE IS NOT NULL AND P_TO_CANCEL_DATE IS NOT NULL THEN
100 CANCELDATECP := ' and trunc(xf.cancel_date) <= ''' || P_TO_CANCEL_DATE || '''';
101 END IF;
102 RETURN NULL;
103 END CANCELDATECFFORMULA;
104
105 FUNCTION ACTUALRECEIVEDATECFFORMULA RETURN VARCHAR2 IS
106 BEGIN
107 IF P_FROM_ACTUAL_RECEIVE_DATE IS NOT NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NOT NULL THEN
108 ACTUALRECEIVEDATECP := ' and trunc(xf.actual_receive_date) between ''' || P_FROM_ACTUAL_RECEIVE_DATE || '''
109 and ''' || P_TO_ACTUAL_RECEIVE_DATE || '''';
110 ELSIF P_FROM_ACTUAL_RECEIVE_DATE IS NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NULL THEN
111 ACTUALRECEIVEDATECP := ' ';
112 ELSIF P_FROM_ACTUAL_RECEIVE_DATE IS NOT NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NULL THEN
113 ACTUALRECEIVEDATECP := ' and trunc(xf.actual_receive_date) >= ''' || P_FROM_ACTUAL_RECEIVE_DATE || '''';
114 ELSIF P_FROM_ACTUAL_RECEIVE_DATE IS NOT NULL AND P_TO_ACTUAL_RECEIVE_DATE IS NOT NULL THEN
115 ACTUALRECEIVEDATECP := ' and trunc(xf.actual_receive_date) <= ''' || P_TO_ACTUAL_RECEIVE_DATE || '''';
116 END IF;
117 RETURN NULL;
118 END ACTUALRECEIVEDATECFFORMULA;
119
120 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
121 BEGIN
122 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
123 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
124 V_USER_ID := FND_PROFILE.VALUE('USER_ID');
125 SETUP_REPORT;
126 lV_REPORT_TITLE:=substr(V_REPORT_TITLE,1,instr(V_REPORT_TITLE,' (XML)'));
127 RETURN (TRUE);
128 END BEFOREREPORT;
129
130 PROCEDURE SETUP_REPORT IS
131 V_DEF_SET_OF_BOOKS_ID NUMBER := 1;
132 V_DEF_REPORT_TITLE VARCHAR2(240) := 'Inventory Transfer Report by Batch';
133 V_SET_OF_BOOKS_ID NUMBER;
134 BEGIN
135 V_REPORT_NUMBER := 'CMXFER02';
136 V_SUBTITLE := '';
137 V_SET_OF_BOOKS_ID := NVL(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
138 ,V_DEF_SET_OF_BOOKS_ID);
139 SELECT
140 MIN(NAME)
141 INTO V_SET_OF_BOOKS_NAME
142 FROM
143 GL_SETS_OF_BOOKS
144 WHERE SET_OF_BOOKS_ID = V_SET_OF_BOOKS_ID;
145 SELECT
146 NVL(MIN(FCP.USER_CONCURRENT_PROGRAM_NAME)
147 ,V_DEF_REPORT_TITLE)
148 INTO V_REPORT_TITLE
149 FROM
150 FND_CONCURRENT_PROGRAMS_VL FCP,
151 FND_CONCURRENT_REQUESTS FCR
152 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
153 AND FCP.APPLICATION_ID = FCR.PROGRAM_APPLICATION_ID
154 AND FCP.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID;
155 END SETUP_REPORT;
156
157 FUNCTION BATCHNOCFFORMULA RETURN VARCHAR2 IS
158 BEGIN
159 IF P_FROM_BATCH_NUM IS NOT NULL AND P_TO_BATCH_NUM IS NOT NULL THEN
160 BATCHNOCP := ' and xf.transfer_batch between ''' || P_FROM_BATCH_NUM || '''
161 and ''' || P_TO_BATCH_NUM || '''';
162 ELSIF P_FROM_BATCH_NUM IS NULL AND P_TO_BATCH_NUM IS NULL THEN
163 BATCHNOCP := ' ';
164 ELSIF P_FROM_BATCH_NUM IS NOT NULL AND P_TO_BATCH_NUM IS NULL THEN
165 BATCHNOCP := ' and xf.transfer_batch >= ''' || P_FROM_BATCH_NUM || '''';
166 ELSIF P_FROM_BATCH_NUM IS NOT NULL AND P_TO_BATCH_NUM IS NOT NULL THEN
167 BATCHNOCP := ' and xf.transfer_batch <= ''' || P_TO_BATCH_NUM || '''';
168 END IF;
169 RETURN NULL;
170 END BATCHNOCFFORMULA;
171
172 FUNCTION RELEASE_CFFORMULA(STAT IN VARCHAR2) RETURN VARCHAR2 IS
173 BEGIN
174 IF STAT in (1,4) THEN
175 RETURN ('(Scheduled)');
176 ELSE
177 RETURN ('(Actual)');
178 END IF;
179 RETURN NULL;
180 END RELEASE_CFFORMULA;
181
182 FUNCTION RECEIVE_CFFORMULA(STAT IN VARCHAR2) RETURN VARCHAR2 IS
183 BEGIN
184 IF STAT in (1,2,4) THEN
185 RETURN ('(Scheduled)');
186 ELSIF STAT in (3) THEN
187 RETURN ('(Actual)');
188 ELSE
189 RETURN ('(Cancel)');
190 END IF;
191 RETURN NULL;
192 END RECEIVE_CFFORMULA;
193
194 PROCEDURE HEADER IS
195 BEGIN
196 NULL;
197 END HEADER;
198
199 FUNCTION AFTERREPORT RETURN BOOLEAN IS
200 BEGIN
201 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
202 RETURN (TRUE);
203 END AFTERREPORT;
204
205 FUNCTION RELEASEDATECP_P RETURN VARCHAR2 IS
206 BEGIN
207 RETURN RELEASEDATECP;
208 END RELEASEDATECP_P;
209
210 FUNCTION SOURCEWAREHOUSECP_P RETURN VARCHAR2 IS
211 BEGIN
212 RETURN SOURCEWAREHOUSECP;
213 END SOURCEWAREHOUSECP_P;
214
215 FUNCTION DESTINATIONWAREHOUSECP_P RETURN VARCHAR2 IS
216 BEGIN
217 RETURN DESTINATIONWAREHOUSECP;
218 END DESTINATIONWAREHOUSECP_P;
219
220 FUNCTION TRANSFERSTATUSCP_P RETURN VARCHAR2 IS
221 BEGIN
222 RETURN TRANSFERSTATUSCP;
223 END TRANSFERSTATUSCP_P;
224
225 FUNCTION SCHEDULEDSHIPDATECP_P RETURN VARCHAR2 IS
226 BEGIN
227 RETURN SCHEDULEDSHIPDATECP;
228 END SCHEDULEDSHIPDATECP_P;
229
230 FUNCTION CANCELDATECP_P RETURN VARCHAR2 IS
231 BEGIN
232 RETURN CANCELDATECP;
233 END CANCELDATECP_P;
234
235 FUNCTION ACTUALRECEIVEDATECP_P RETURN VARCHAR2 IS
236 BEGIN
237 RETURN ACTUALRECEIVEDATECP;
238 END ACTUALRECEIVEDATECP_P;
239
240 FUNCTION BATCHNOCP_P RETURN VARCHAR2 IS
241 BEGIN
242 RETURN BATCHNOCP;
243 END BATCHNOCP_P;
244
245 END GMI_CMXFER02_XMLP_PKG;
246
[Home] [Help]