1 PACKAGE BODY WMS_WMSSTCON_XMLP_PKG AS
2 /* $Header: WMSSTCONB.pls 120.3 2007/12/25 08:01:01 nchinnam noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 EXCEPTION
9 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
10 /*SRW.MESSAGE(10
11 ,'Before Report: Init')*/NULL;
12 END;
13 DECLARE
14 L_REPORT_NAME VARCHAR2(240);
15 BEGIN
16 SELECT
17 CP.USER_CONCURRENT_PROGRAM_NAME
18 INTO L_REPORT_NAME
19 FROM
20 FND_CONCURRENT_PROGRAMS_VL CP,
21 FND_CONCURRENT_REQUESTS CR
22 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
23 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
24 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
25 RP_REPORT_NAME := L_REPORT_NAME;
26 RP_REPORT_NAME := substr(RP_REPORT_NAME,1,instr(RP_REPORT_NAME,' (XML)'));
27 EXCEPTION
28 WHEN NO_DATA_FOUND THEN
29 RP_REPORT_NAME := 'Consolidation Report';
30 END;
31 BEGIN
32 NULL;
33 EXCEPTION
34 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
35 /*SRW.MESSAGE(20
36 ,'Failed in before report trigger:MSTK')*/NULL;
37 RAISE;
38 END;
39 BEGIN
40 NULL;
41 EXCEPTION
42 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
43 /*SRW.MESSAGE(30
44 ,'Failed flexsql loc select in before report trigger')*/NULL;
45 RAISE;
46 END;
47 BEGIN
48 NULL;
49 EXCEPTION
50 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
51 /*SRW.MESSAGE(200
52 ,'Failed in before report trigger:MSTK/order')*/NULL;
53 RAISE;
54 END;
55 BEGIN
56 NULL;
57 EXCEPTION
58 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
59 /*SRW.MESSAGE(300
60 ,'Failed in before report trigger:MTLL/order')*/NULL;
61 RAISE;
62 END;
63 BEGIN
64 P_WHERE_CLAUSE := ' 1 = 1 ';
65 IF P_TRIP_ID IS NOT NULL THEN
66 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and wt.trip_id = ' || P_TRIP_ID;
67 END IF;
68 IF P_DELIVERY_ID IS NOT NULL THEN
69 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and wnd.delivery_id = ' || P_DELIVERY_ID;
70 END IF;
71 IF P_SALES_ORDER_HEADER_ID IS NOT NULL THEN
72 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and oeh.header_id = ' || P_SALES_ORDER_HEADER_ID;
73 END IF;
74 IF P_CUSTOMER_ID IS NOT NULL THEN
75 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and oeh.sold_to_org_id = ' || P_CUSTOMER_ID;
76 END IF;
77 IF P_ORDER_TYPE_ID IS NOT NULL THEN
78 P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' and oeh.order_type_id = ' || P_ORDER_TYPE_ID;
79 END IF;
80 END;
81 BEGIN
82 IF P_ORG_ID IS NOT NULL THEN
83 SELECT
84 ORGANIZATION_CODE,
85 ORGANIZATION_NAME
86 INTO P_ORG_CODE,P_ORG_NAME
87 FROM
88 ORG_ORGANIZATION_DEFINITIONS
89 WHERE ORGANIZATION_ID = P_ORG_ID;
90 END IF;
91 IF P_TRIP_ID IS NOT NULL THEN
92 SELECT
93 NAME
94 INTO P_TRIP_NAME
95 FROM
96 WSH_TRIPS
97 WHERE TRIP_ID = P_TRIP_ID;
98 END IF;
99 IF P_DELIVERY_ID IS NOT NULL THEN
100 SELECT
101 NAME
102 INTO P_DELIVERY_NAME
103 FROM
104 WSH_NEW_DELIVERIES
105 WHERE DELIVERY_ID = P_DELIVERY_ID;
106 END IF;
107 IF P_CUSTOMER_ID IS NOT NULL THEN
108 SELECT
109 PARTY.PARTY_NAME,
110 PARTY.PARTY_NUMBER
111 INTO P_CUSTOMER_NAME,P_CUSTOMER_NUMBER
112 FROM
113 HZ_PARTIES PARTY,
114 HZ_CUST_ACCOUNTS CUST_ACCT
115 WHERE CUST_ACCT.CUST_ACCOUNT_ID = P_CUSTOMER_ID
116 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID;
117 END IF;
118 IF P_SALES_ORDER_HEADER_ID IS NOT NULL THEN
119 SELECT
120 ORDER_NUMBER
121 INTO P_SALES_ORDER
122 FROM
123 OE_ORDER_HEADERS_ALL
124 WHERE HEADER_ID = P_SALES_ORDER_HEADER_ID;
125 IF P_CUSTOMER_ID IS NULL THEN
126 SELECT
127 PARTY.PARTY_NAME,
128 PARTY.PARTY_NUMBER
129 INTO P_CUSTOMER_NAME,P_CUSTOMER_NUMBER
130 FROM
131 HZ_PARTIES PARTY,
132 HZ_CUST_ACCOUNTS CUST_ACCT,
133 OE_ORDER_HEADERS_ALL OEH
134 WHERE CUST_ACCT.CUST_ACCOUNT_ID = OEH.SOLD_TO_ORG_ID
135 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
136 AND OEH.HEADER_ID = P_SALES_ORDER_HEADER_ID;
137 END IF;
138 END IF;
139 END;
140 RETURN (TRUE);
141 END BEFOREREPORT;
142
143 FUNCTION AFTERREPORT RETURN BOOLEAN IS
144 BEGIN
145 BEGIN
146 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
147 EXCEPTION
148 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
149 /*SRW.MESSAGE(99
150 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
151 RETURN (FALSE);
152 END;
153 RETURN (TRUE);
154 END AFTERREPORT;
155
156 FUNCTION INMULTIPLESOFORMULA(LPN_ID IN NUMBER) RETURN CHAR IS
157 MCOUNT NUMBER := 1;
158 BEGIN
159 IF (P_MULTIPLE_ASSOCIATION_CHECK = 1) THEN
160 SELECT
161 count(SOURCE_HEADER_ID)
162 INTO MCOUNT
163 FROM
164 WSH_DELIVERY_DETAILS
165 WHERE DELIVERY_DETAIL_ID in (
166 SELECT
167 WDA.DELIVERY_DETAIL_ID
168 FROM
169 WSH_DELIVERY_DETAILS WDD,
170 WSH_DELIVERY_ASSIGNMENTS WDA
171 WHERE WDD.ORGANIZATION_ID = P_ORG_ID
172 AND WDD.LPN_ID = INMULTIPLESOFORMULA.LPN_ID
173 AND WDD.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID )
174 AND SOURCE_CODE = 'OE';
175 END IF;
176 IF MCOUNT > 1 THEN
177 RETURN P_MULTIPLE_YES;
178 ELSE
179 RETURN P_MULTIPLE_NO;
180 END IF;
181 END INMULTIPLESOFORMULA;
182
183 FUNCTION INMULTIPLEDELIVERYFORMULA(LPN_ID IN NUMBER) RETURN CHAR IS
184 MCOUNT NUMBER := 1;
185 BEGIN
186 IF (P_MULTIPLE_ASSOCIATION_CHECK = 1) THEN
187 SELECT
188 count(WDA.DELIVERY_ID)
189 INTO MCOUNT
190 FROM
191 WSH_DELIVERY_DETAILS WDD,
192 WSH_DELIVERY_ASSIGNMENTS WDA
193 WHERE WDD.ORGANIZATION_ID = P_ORG_ID
194 AND WDD.LPN_ID = INMULTIPLEDELIVERYFORMULA.LPN_ID
195 AND WDD.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID;
196 END IF;
197 IF MCOUNT > 1 THEN
198 RETURN P_MULTIPLE_YES;
199 ELSE
200 RETURN P_MULTIPLE_NO;
201 END IF;
202 END INMULTIPLEDELIVERYFORMULA;
203
204 FUNCTION INMULTIPLETRIPFORMULA(LPN_ID IN NUMBER) RETURN CHAR IS
205 MCOUNT NUMBER := 1;
206 BEGIN
207 IF (P_MULTIPLE_ASSOCIATION_CHECK = 1) THEN
208 SELECT
209 count(WTS.TRIP_ID)
210 INTO MCOUNT
211 FROM
212 WSH_DELIVERY_LEGS WDL,
213 WSH_TRIP_STOPS WTS
214 WHERE WDL.PICK_UP_STOP_ID = WTS.STOP_ID
215 AND WDL.DELIVERY_ID in (
216 SELECT
217 WDA.DELIVERY_ID
218 FROM
219 WSH_DELIVERY_DETAILS WDD,
220 WSH_DELIVERY_ASSIGNMENTS WDA
221 WHERE WDD.ORGANIZATION_ID = P_ORG_ID
222 AND WDD.LPN_ID = INMULTIPLETRIPFORMULA.LPN_ID
223 AND WDD.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID );
224 END IF;
225 IF MCOUNT > 1 THEN
226 RETURN P_MULTIPLE_YES;
227 ELSE
228 RETURN P_MULTIPLE_NO;
229 END IF;
230 END INMULTIPLETRIPFORMULA;
231
232 FUNCTION P_TRIP_NAME_P RETURN VARCHAR2 IS
233 BEGIN
234 RETURN P_TRIP_NAME;
235 END P_TRIP_NAME_P;
236
237 FUNCTION P_DELIVERY_NAME_P RETURN VARCHAR2 IS
238 BEGIN
239 RETURN P_DELIVERY_NAME;
240 END P_DELIVERY_NAME_P;
241
242 FUNCTION P_SALES_ORDER_P RETURN NUMBER IS
243 BEGIN
244 RETURN P_SALES_ORDER;
245 END P_SALES_ORDER_P;
246
247 FUNCTION P_CUSTOMER_NAME_P RETURN VARCHAR2 IS
248 BEGIN
249 RETURN P_CUSTOMER_NAME;
250 END P_CUSTOMER_NAME_P;
251
252 FUNCTION P_ORG_CODE_P RETURN VARCHAR2 IS
253 BEGIN
254 RETURN P_ORG_CODE;
255 END P_ORG_CODE_P;
256
257 FUNCTION P_ORG_NAME_P RETURN VARCHAR2 IS
258 BEGIN
259 RETURN P_ORG_NAME;
260 END P_ORG_NAME_P;
261
262 FUNCTION P_CUSTOMER_NUMBER_P RETURN VARCHAR2 IS
263 BEGIN
264 RETURN P_CUSTOMER_NUMBER;
265 END P_CUSTOMER_NUMBER_P;
266
267 END WMS_WMSSTCON_XMLP_PKG;
268
[Home] [Help]