1 PACKAGE BODY WMS_WMSSCHS1_XMLP_PKG AS
2 /* $Header: WMSSCHS1B.pls 120.1.12010000.2 2008/08/19 09:56:25 anviswan ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_REPORT_NAME VARCHAR2(10000);
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
9 EXCEPTION
10 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
11 /*SRW.MESSAGE(1
12 ,'Before Report: Init')*/NULL;
13 END;
14 BEGIN
15 SELECT
16 CP.USER_CONCURRENT_PROGRAM_NAME
17 INTO L_REPORT_NAME
18 FROM
19 FND_CONCURRENT_PROGRAMS_VL CP,
20 FND_CONCURRENT_REQUESTS CR
21 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
22 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
23 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
24 L_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
25 P_REPORT_NAME := L_REPORT_NAME;
26 EXCEPTION
27 WHEN NO_DATA_FOUND THEN
28 P_REPORT_NAME := 'Schedule Shipment Report';
29 END;
30 BEGIN
31 NULL;
32 EXCEPTION
33 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
34 /*SRW.MESSAGE(2
35 ,'Failed in before report trigger:MSTK')*/NULL;
36 END;
37 BEGIN
38 SELECT
39 REQUEST_DATE,
40 ACTUAL_START_DATE,
41 SAVE_OUTPUT_FLAG,
42 PRINTER
43 INTO P_REQUEST_DATE,P_ACTUAL_START_DATE,P_SAVE_OUTPUT_FLAG,P_PRINTER
44 FROM
45 FND_CONCURRENT_REQUESTS
46 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
47 RETURN (TRUE);
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 NULL;
51 END;
52 BEGIN
53 NULL;
54 EXCEPTION
55 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
56 /*SRW.MESSAGE(5
57 ,'Failed flexsql loc select in before report trigger')*/NULL;
58 RAISE;
59 END;
60 RETURN (TRUE);
61 END BEFOREREPORT;
62
63 FUNCTION AFTERPFORM RETURN BOOLEAN IS
64 L_VALUE_YES MFG_LOOKUPS.MEANING%TYPE;
65 L_VALUE_NO MFG_LOOKUPS.MEANING%TYPE;
66 L_MASTER_ORG_ID NUMBER;
67 BEGIN
68
69 P_PROJECT_1:=P_PROJECT;
70 P_TASK_1:=P_TASK;
71 /*SRW.MESSAGE(1
72 ,'org_id:' || P_ORG_ID)*/NULL;
73 SELECT
74 OPERATING_UNIT
75 INTO L_MASTER_ORG_ID
76 FROM
77 ORG_ORGANIZATION_DEFINITIONS
78 WHERE ORGANIZATION_ID = P_ORG_ID;
79 FND_CLIENT_INFO.SET_ORG_CONTEXT(L_MASTER_ORG_ID);
80 IF P_SCHEDULE_SHIPMENT_FROM_DATE IS NOT NULL AND P_SCHEDULE_SHIPMENT_TO_DATE IS NOT NULL THEN
81 LP_SHIPMENT_SCHEDULE_DATE := ' AND to_char(OE1.SCHEDULE_SHIP_DATE,''YYYY/MM/DD'') BETWEEN ''' || SUBSTR(P_SCHEDULE_SHIPMENT_FROM_DATE
82 ,1
83 ,10) || ''' AND ''' || SUBSTR(P_SCHEDULE_SHIPMENT_TO_DATE
84 ,1
85 ,10) || '''';
86 ELSIF P_SCHEDULE_SHIPMENT_FROM_DATE IS NOT NULL AND P_SCHEDULE_SHIPMENT_TO_DATE IS NULL THEN
87 LP_SHIPMENT_SCHEDULE_DATE := ' AND to_char(OE1.SCHEDULE_SHIP_DATE,''YYYY/MM/DD'') >= ''' || SUBSTR(P_SCHEDULE_SHIPMENT_FROM_DATE
88 ,1
89 ,10) || '''';
90 ELSIF P_SCHEDULE_SHIPMENT_FROM_DATE IS NULL AND P_SCHEDULE_SHIPMENT_TO_DATE IS NOT NULL THEN
91 LP_SHIPMENT_SCHEDULE_DATE := ' AND to_char(OE1.SCHEDULE_SHIP_DATE,''YYYY/MM/DD'') <= ''' || SUBSTR(P_SCHEDULE_SHIPMENT_TO_DATE
92 ,1
93 ,10) || '''';
94 END IF;
95 IF P_REQUEST_DATE_FROM IS NOT NULL AND P_REQUEST_DATE_TO IS NOT NULL THEN
96 LP_REQUEST_DATE := ' AND to_char(OE1.REQUEST_DATE,''YYYY/MM/DD'') BETWEEN ''' || SUBSTR(P_REQUEST_DATE_FROM
97 ,1
98 ,10) || ''' AND ''' || SUBSTR(P_REQUEST_DATE_TO
99 ,1
100 ,10) || '''';
101 ELSIF P_REQUEST_DATE_FROM IS NOT NULL AND P_REQUEST_DATE_TO IS NULL THEN
102 LP_REQUEST_DATE := ' AND to_char(OE1.REQUEST_DATE,''YYYY/MM/DD'') >= ''' || SUBSTR(P_REQUEST_DATE_FROM
103 ,1
104 ,10) || '''';
105 ELSIF P_REQUEST_DATE_FROM IS NULL AND P_REQUEST_DATE_TO IS NOT NULL THEN
106 LP_REQUEST_DATE := ' AND to_char(OE1.REQUEST_DATE,''YYYY/MM/DD'') <= ''' || SUBSTR(P_REQUEST_DATE_TO
107 ,1
108 ,10) || '''';
109 END IF;
110 IF P_DELIVERY_ID IS NOT NULL THEN
111 LP_DELIVERY_ID := ' AND WND.DELIVERY_ID = ' || P_DELIVERY_ID;
112 END IF;
113 IF P_TRIP_ID IS NOT NULL THEN
114 LP_TRIP_STOP := ' AND WT.TRIP_ID = ' || P_TRIP_ID;
115 END IF;
116 IF P_PROJECT_ID IS NOT NULL THEN
117 LP_PROJECT := ' AND WDD.PROJECT_ID =' || P_PROJECT_ID;
118 END IF;
119 IF P_TASK_ID IS NOT NULL THEN
120 LP_TASK := ' AND WDD.TASK_ID =' || P_TASK_ID;
121 END IF;
122 IF P_PRIOR_RES_FLAG = 1 THEN
123 LP_PRIOR_RES_FLAG := ' AND OE1.LINE_ID= MR.DEMAND_SOURCE_LINE_ID';
124 ELSE
125 LP_PRIOR_RES_FLAG := ' AND OE1.LINE_ID= MR.DEMAND_SOURCE_LINE_ID(+)';
126 END IF;
127 /*SRW.MESSAGE(1
128 ,'Order header' || P_ORDER_HEADER_ID)*/NULL;
129 IF P_ORDER_HEADER_ID IS NOT NULL THEN
130 LP_SO_HEADER := ' AND OE1.HEADER_ID =' || P_ORDER_HEADER_ID;
131 END IF;
132 /*SRW.MESSAGE(1
133 ,'Order header' || LP_SO_HEADER)*/NULL;
134 IF P_ORDER_LINE_ID IS NOT NULL THEN
135 LP_SO_LINE := ' AND OE1.LINE_ID =' || P_ORDER_LINE_ID;
136 END IF;
137 IF P_ORG_ID IS NOT NULL THEN
138 SELECT
139 ORGANIZATION_CODE,
140 ORGANIZATION_NAME
141 INTO P_ORG_CODE,P_ORG_NAME
142 FROM
143 ORG_ORGANIZATION_DEFINITIONS
144 WHERE ORGANIZATION_ID = P_ORG_ID;
145 END IF;
146 IF P_CUSTOMER_ID IS NOT NULL THEN
147 SELECT
148 PARTY.PARTY_NAME,
149 PARTY.PARTY_NUMBER
150 INTO P_CUSTOMER_NAME,P_CUSTOMER_NUMBER
151 FROM
152 HZ_PARTIES PARTY,
153 HZ_CUST_ACCOUNTS CUST_ACCT
154 WHERE CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER_ID
155 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
156 END IF;
157 SELECT
158 MEANING
159 INTO L_VALUE_NO
160 FROM
161 MFG_LOOKUPS
162 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
163 AND LOOKUP_CODE = 2;
164 SELECT
165 MEANING
166 INTO L_VALUE_YES
167 FROM
168 MFG_LOOKUPS
169 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
170 AND LOOKUP_CODE = 1;
171 IF P_PRINT_CUS_PROD_DET = 1 THEN
172 P_PRINT_CUS_DETAILS := L_VALUE_YES;
173 ELSE
174 P_PRINT_CUS_DETAILS := L_VALUE_NO;
175 END IF;
176 IF P_PRIOR_RES_FLAG = 1 THEN
177 P_RES_FLAG := L_VALUE_YES;
178 ELSE
179 P_RES_FLAG := L_VALUE_NO;
180 END IF;
181 IF P_ORDER_TYPE IS NOT NULL THEN
182 BEGIN
183 /*SRW.MESSAGE(1
184 ,'so_type' || P_ORDER_TYPE)*/NULL;
185 SELECT
186 NAME
187 INTO P_SO_TYPE
188 FROM
189 OE_TRANSACTION_TYPES
190 WHERE TRANSACTION_TYPE_ID = P_ORDER_TYPE;
191 /*SRW.MESSAGE(1
192 ,'SO_TYPE' || P_SO_TYPE)*/NULL;
193 EXCEPTION
194 WHEN OTHERS THEN
195 /*SRW.MESSAGE(1
196 ,'hi..no type found' || SQLERRM)*/NULL;
197 END;
198 END IF;
199 IF P_ORDER_HEADER_ID IS NOT NULL THEN
200 BEGIN
201 SELECT
202 ORDER_NUMBER
203 INTO P_ORDER_NUMBER
204 FROM
205 OE_ORDER_HEADERS_ALL
206 WHERE HEADER_ID = P_ORDER_HEADER_ID;
207 EXCEPTION
208 WHEN OTHERS THEN
209 /*SRW.MESSAGE(1
210 ,'ORDER NUMBER NOT FOUND')*/NULL;
211 END;
212 END IF;
213 IF P_ORDER_HEADER_ID IS NOT NULL AND P_ORDER_LINE_ID IS NOT NULL THEN
214 BEGIN
215 SELECT
216 LINE_NUMBER || '.' || SHIPMENT_NUMBER || '.' || DECODE(OPTION_NUMBER
217 ,NULL
218 ,' '
219 ,'.' || OPTION_NUMBER)
220 INTO P_LINE_NUMBER
221 FROM
222 OE_ORDER_LINES_ALL
223 WHERE HEADER_ID = P_ORDER_HEADER_ID
224 AND LINE_ID = P_ORDER_LINE_ID;
225 EXCEPTION
226 WHEN OTHERS THEN
227 /*SRW.MESSAGE(1
228 ,'hi..no line found' || SQLERRM)*/NULL;
229 END;
230 END IF;
231 RETURN (TRUE);
232 END AFTERPFORM;
233
234 FUNCTION AFTERREPORT RETURN BOOLEAN IS
235 BEGIN
236 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
237 RETURN TRUE;
238 END AFTERREPORT;
239
240 FUNCTION CF_LOC_FLEX_FIELDFORMULA RETURN CHAR IS
241 BEGIN
242 RETURN ' ';
243 END CF_LOC_FLEX_FIELDFORMULA;
244
245 FUNCTION CF_LPN_NUMBERFORMULA(LPN_ID_1 IN NUMBER) RETURN CHAR IS
246 BEGIN
247 IF LPN_ID_1 IS NOT NULL THEN
248 SELECT
249 LICENSE_PLATE_NUMBER
250 INTO P_LICENSE_PLATE_NUMBER
251 FROM
252 WMS_LICENSE_PLATE_NUMBERS
253 WHERE LPN_ID = LPN_ID_1;
254 ELSE
255 P_LICENSE_PLATE_NUMBER := '';
256 END IF;
257 RETURN (P_LICENSE_PLATE_NUMBER);
258 END CF_LPN_NUMBERFORMULA;
259
260 FUNCTION CF_OUTERMOST_LPNFORMULA(LPN_ID_1 IN NUMBER) RETURN CHAR IS
261 BEGIN
262 IF LPN_ID_1 IS NOT NULL THEN
263 SELECT
264 LICENSE_PLATE_NUMBER
265 INTO P_OUTERMOST_LPN
266 FROM
267 WMS_LICENSE_PLATE_NUMBERS
268 WHERE LPN_ID = (
269 SELECT
270 OUTERMOST_LPN_ID
271 FROM
272 WMS_LICENSE_PLATE_NUMBERS
273 WHERE LPN_ID = LPN_ID_1 );
274 ELSE
275 P_OUTERMOST_LPN := ' ';
276 END IF;
277 RETURN (P_OUTERMOST_LPN);
278 END CF_OUTERMOST_LPNFORMULA;
279
280 FUNCTION CF_PROJECTFORMULA(PROJECT_ID_1 IN NUMBER) RETURN CHAR IS
281 BEGIN
282 IF PROJECT_ID_1 IS NOT NULL THEN
283 SELECT
284 PROJECT_NAME
285 INTO P_PROJECT_1
286 FROM
287 PJM_PROJECTS_ORG_V
288 WHERE INVENTORY_ORGANIZATION_ID = P_ORG_ID
289 AND PROJECT_ID = PROJECT_ID_1;
290 ELSE
291 P_PROJECT_1 := ' ';
292 END IF;
293 RETURN (P_PROJECT_1);
294 END CF_PROJECTFORMULA;
295
296 FUNCTION CF_TASKFORMULA(TASK_ID_1 IN NUMBER
297 ,PROJECT_ID_1 IN NUMBER) RETURN CHAR IS
298 BEGIN
299 IF TASK_ID_1 IS NOT NULL THEN
300 SELECT
301 TASK_NAME
302 INTO P_TASK_1
303 FROM
304 PJM_TASKS_V
305 WHERE PROJECT_ID = PROJECT_ID_1
306 AND TASK_ID = TASK_ID_1;
307 ELSE
308 P_TASK_1 := '';
309 END IF;
310 RETURN (P_TASK_1);
311 END CF_TASKFORMULA;
312
313 FUNCTION CF_SHIP_TO_LOCATIONFORMULA(SHIP_TO_LOCATION_ID_1 IN NUMBER) RETURN CHAR IS
314 BEGIN
315 BEGIN
316 SELECT
317 LOCATION_CODE
318 INTO P_SHIP_TO_LOCATION
319 FROM
320 HR_LOCATIONS
321 WHERE LOCATION_ID = SHIP_TO_LOCATION_ID_1;
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 P_SHIP_TO_LOCATION := ' ';
325 END;
326 RETURN (P_SHIP_TO_LOCATION);
327 END CF_SHIP_TO_LOCATIONFORMULA;
328
329 FUNCTION CF_BARCODEFORMULA RETURN VARCHAR2 IS
330 L_BARCODE_TYPE VARCHAR2(30);
331 L_BARCODE_FONT_NAME VARCHAR2(30);
332 BARCODE_STRING VARCHAR2(30);
333 L_START_DIGIT VARCHAR2(3);
334 L_STOP_DIGIT VARCHAR2(3);
335 L_CARRIAGE_RETURN VARCHAR2(10);
336 L_CHECKSUMDATA VARCHAR2(100);
337 L_CHECKSUM NUMBER;
338 L_ADDITIONAL_CHECKSUM NUMBER;
339 BEGIN
340 RETURN ' ';
341 END CF_BARCODEFORMULA;
342
343 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
344 BEGIN
345 RETURN (TRUE);
346 END BEFOREPFORM;
347
348 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
349 BEGIN
350 RETURN (TRUE);
351 END BETWEENPAGE;
352
353 END WMS_WMSSCHS1_XMLP_PKG;
354
355
[Home] [Help]