1 PACKAGE BODY ONT_OEXOHHSA_XMLP_PKG AS
2 /* $Header: OEXOHHSAB.pls 120.1 2007/12/25 07:29:20 npannamp noship $ */
3 FUNCTION SOB_NAMEFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 SOB_NAME VARCHAR2(30);
7 BEGIN
8 SELECT
9 NAME
10 INTO SOB_NAME
11 FROM
12 GL_SETS_OF_BOOKS
13 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
14 RETURN (SOB_NAME);
15 END;
16 RETURN NULL;
17 END SOB_NAMEFORMULA;
18
19 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
20 BEGIN
21 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
22 --ADDED AS FIX
23 P_ACTIVITY_DATE_LO_V:=TO_CHAR(P_ACTIVITY_DATE_LO,'DD-MON-YY');
24 P_ACTIVITY_DATE_HI_V:=TO_CHAR(P_ACTIVITY_DATE_HI,'DD-MON-YY');
25 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
26 RETURN (TRUE);
27 EXCEPTION
28 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
29 BEGIN
30 /*SRW.MESSAGE(1
31 ,'FAILED IN BEFORE REPORT TRIGGER')*/NULL;
32 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
33 RETURN (FALSE);
34 END;
35 BEGIN
36 --P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
37 P_ORGANIZATION_ID_V := MO_GLOBAL.GET_CURRENT_ORG_ID;
38 END;
39 END BEFOREREPORT;
40
41 FUNCTION AFTERREPORT RETURN BOOLEAN IS
42 BEGIN
43 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
44 RETURN (TRUE);
45 EXCEPTION
46 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
47 /*SRW.MESSAGE(1
48 ,'FAILED IN AFTER REPORT TRIGGER')*/NULL;
49 RETURN (FALSE);
50 END AFTERREPORT;
51
52 FUNCTION C_ACTIVITY_MEANINGFORMULA RETURN VARCHAR2 IS
53 BEGIN
54 DECLARE
55 ACTIVITY_MEANING VARCHAR2(80);
56 BEGIN
57 SELECT
58 MEANING
59 INTO ACTIVITY_MEANING
60 FROM
61 OE_LOOKUPS
62 WHERE LOOKUP_TYPE = 'AUTHORIZED_ACTION'
63 AND LOOKUP_CODE = P_ACTIVITY;
64 RETURN (ACTIVITY_MEANING);
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 RETURN ('');
68 END;
69 RETURN NULL;
70 END C_ACTIVITY_MEANINGFORMULA;
71
72 FUNCTION C_HOLD_TYPE_WHERE RETURN VARCHAR2 IS
73 BEGIN
74 IF P_HOLD_TYPE_LO IS NOT NULL AND P_HOLD_TYPE_HI IS NOT NULL THEN
75 RETURN ('and ht.lookup_code between ''' || P_HOLD_TYPE_LO || '''
76 and ''' || P_HOLD_TYPE_HI || ''' ');
77 ELSE
78 IF P_HOLD_TYPE_LO IS NULL AND P_HOLD_TYPE_HI IS NOT NULL THEN
79 RETURN ('and ht.lookup_code <= ''' || P_HOLD_TYPE_HI || ''' ');
80 ELSE
81 IF P_HOLD_TYPE_LO IS NOT NULL AND P_HOLD_TYPE_HI IS NULL THEN
82 RETURN ('and ht.lookup_code >= ''' || P_HOLD_TYPE_LO || ''' ');
83 ELSE
84 RETURN (NULL);
85 END IF;
86 END IF;
87 END IF;
88 RETURN NULL;
89 END C_HOLD_TYPE_WHERE;
90
91 FUNCTION C_HOLD_NAME_WHERE RETURN VARCHAR2 IS
92 BEGIN
93 IF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
94 RETURN ('and h.name between ''' || P_HOLD_NAME_LO || ''' and
95 ''' || P_HOLD_NAME_HI || ''' ');
96 ELSE
97 IF P_HOLD_NAME_LO IS NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
98 RETURN ('and h.name <= ''' || P_HOLD_NAME_HI || ''' ');
99 ELSE
100 IF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NULL THEN
101 RETURN ('and h.name >= ''' || P_HOLD_NAME_LO || ''' ');
102 ELSE
103 RETURN (NULL);
104 END IF;
105 END IF;
106 END IF;
107 RETURN NULL;
108 END C_HOLD_NAME_WHERE;
109
110 FUNCTION C_ENTITY_VALUE(OBJECT_TYPE_CODE IN VARCHAR2
111 ,OBJECT_ID IN NUMBER) RETURN VARCHAR2 IS
112 L_ENTITY_VALUE VARCHAR2(500);
113 BEGIN
114 IF OBJECT_TYPE_CODE = 'O' THEN
115 SELECT
116 ORDER_NUMBER
117 INTO L_ENTITY_VALUE
118 FROM
119 OE_ORDER_HEADERS
120 WHERE HEADER_ID = OBJECT_ID;
121 ELSE
122 IF OBJECT_TYPE_CODE = 'C' THEN
123 SELECT
124 SUBSTRB(PARTY.PARTY_NAME
125 ,1
126 ,50)
127 INTO L_ENTITY_VALUE
128 FROM
129 HZ_PARTIES PARTY,
130 HZ_CUST_ACCOUNTS CUST_ACCT
131 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
132 AND CUST_ACCT.CUST_ACCOUNT_ID = OBJECT_ID;
133 ELSE
134 IF OBJECT_TYPE_CODE = 'I' THEN
135 SELECT
136 DESCRIPTION
137 INTO L_ENTITY_VALUE
138 FROM
139 MTL_SYSTEM_ITEMS_VL
140 WHERE INVENTORY_ITEM_ID = OBJECT_ID
141 AND NVL(ORGANIZATION_ID
142 ,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
143 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
144 ,0);
145 ELSE
146 IF OBJECT_TYPE_CODE = 'S' THEN
147 SELECT
148 SUBSTR((LOC.ADDRESS1 || ', ' || LOC.CITY || ' ' || LOC.STATE)
149 ,1
150 ,30)
151 INTO L_ENTITY_VALUE
152 FROM
153 HZ_CUST_SITE_USES_ALL SU,
154 HZ_PARTY_SITES PARTY_SITE,
155 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
156 HZ_LOCATIONS LOC,
157 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
158 WHERE SU.SITE_USE_ID = OBJECT_ID
159 AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
160 AND SU.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID
161 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
162 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
163 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
164 AND NVL(ACCT_SITE.ORG_ID
165 ,-99) = NVL(LOC_ASSIGN.ORG_ID
166 ,-99);
167 ELSE
168 L_ENTITY_VALUE := OBJECT_ID;
169 END IF;
170 END IF;
171 END IF;
172 END IF;
173 RETURN (L_ENTITY_VALUE);
174 RETURN NULL;
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 RETURN ('Can not retrieve Value');
178 END C_ENTITY_VALUE;
179
180 FUNCTION AFTERPFORM RETURN BOOLEAN IS
181 BEGIN
182 BEGIN
183 IF P_ACTIVITY_DATE_LO IS NOT NULL AND P_ACTIVITY_DATE_HI IS NOT NULL THEN
184 IF P_ACTIVITY = 'REMOVE' THEN
185 LP_ACTIVITY_DATE_WHERE := 'and trunc(hr1.creation_date) between trunc(:P_activity_date_lo) and trunc(:P_activity_date_hi)';
186 ELSE
187 IF P_ACTIVITY = 'APPLY' THEN
188 LP_ACTIVITY_DATE_WHERE := 'and trunc(hs.creation_date) between trunc(:P_activity_date_lo) and trunc(:P_activity_date_hi)';
189 ELSE
190 LP_ACTIVITY_DATE_WHERE := 'and (trunc(hs.creation_date) between trunc(:P_activity_date_lo) and trunc(:P_activity_date_hi) OR trunc(hr1.creation_date) between trunc(:P_activity_date_lo) and trunc(:P_activity_date_hi)) ';
191 END IF;
192 END IF;
193 ELSE
194 IF P_ACTIVITY_DATE_LO IS NULL AND P_ACTIVITY_DATE_HI IS NOT NULL THEN
195 IF P_ACTIVITY = 'REMOVE' THEN
196 LP_ACTIVITY_DATE_WHERE := 'and trunc(hr1.creation_date) <= trunc(:P_activity_date_hi)';
197 ELSE
198 IF P_ACTIVITY = 'APPLY' THEN
199 LP_ACTIVITY_DATE_WHERE := 'and trunc(hs.creation_date) <= trunc(:P_activity_date_hi)';
200 ELSE
201 LP_ACTIVITY_DATE_WHERE := 'and (trunc(hs.creation_date) <= trunc(:P_activity_date_hi) OR trunc(hr1.creation_date) <= trunc(:P_activity_date_hi)) ';
202 END IF;
203 END IF;
204 ELSE
205 IF P_ACTIVITY_DATE_LO IS NOT NULL AND P_ACTIVITY_DATE_HI IS NULL THEN
206 IF P_ACTIVITY = 'REMOVE' THEN
207 LP_ACTIVITY_DATE_WHERE := 'and trunc(hr1.creation_date) >= trunc(:P_activity_date_lo)';
208 ELSE
209 IF P_ACTIVITY = 'APPLY' THEN
210 LP_ACTIVITY_DATE_WHERE := 'and trunc(hs.creation_date) >= trunc(:P_activity_date_lo)';
211 ELSE
212 LP_ACTIVITY_DATE_WHERE := 'and (trunc(hs.creation_date) >= trunc(:P_activity_date_lo) OR trunc(hr1.creation_date) >= trunc(:P_activity_date_lo)) ';
213 END IF;
214 END IF;
215 END IF;
216 END IF;
217 END IF;
218 IF P_ACTIVITY = 'REMOVE' THEN
219 LP_ACTIVITY_TYPE_WHERE := 'and hs.released_flag = ''Y''';
220 END IF;
221 IF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
222 LP_HOLD_WHERE := 'and h.name between :P_hold_name_lo and :P_hold_name_hi';
223 ELSIF P_HOLD_NAME_LO IS NULL AND P_HOLD_NAME_HI IS NOT NULL THEN
224 LP_HOLD_WHERE := 'and h.name <= :P_hold_name_hi';
225 ELSIF P_HOLD_NAME_LO IS NOT NULL AND P_HOLD_NAME_HI IS NULL THEN
226 LP_HOLD_WHERE := 'and h.name >= :P_hold_name_lo';
227 ELSE
228 --LP_HOLD_WHERE := NULL;
229 LP_HOLD_WHERE := ' ';
230 END IF;
231 BEGIN
232 IF (P_HOLD_TYPE_LO IS NOT NULL AND P_HOLD_TYPE_HI IS NOT NULL) THEN
233 LP_HOLD_TYPE_WHERE := 'and ht.lookup_code between :P_hold_type_lo and :P_hold_type_hi ';
234 ELSE
235 IF (P_HOLD_TYPE_LO IS NULL AND P_HOLD_TYPE_HI IS NOT NULL) THEN
236 LP_HOLD_TYPE_WHERE := 'and ht.lookup_code <= :P_hold_type_hi ';
237 ELSE
238 IF (P_HOLD_TYPE_LO IS NOT NULL AND P_HOLD_TYPE_HI IS NULL) THEN
239 LP_HOLD_TYPE_WHERE := 'and ht.lookup_code >= :P_hold_type_lo ';
240 ELSE
241 --LP_HOLD_TYPE_WHERE := NULL;
242 LP_HOLD_TYPE_WHERE := ' ';
243 END IF;
244 END IF;
245 END IF;
246 END;
247 END;
248 RETURN (TRUE);
249 END AFTERPFORM;
250
251 FUNCTION C_ENTITY_VALUE2(OBJECT_TYPE_CODE2 IN VARCHAR2
252 ,OBJECT_ID2 IN NUMBER) RETURN VARCHAR2 IS
253 L_ENTITY_VALUE VARCHAR2(500);
254 BEGIN
255 IF OBJECT_TYPE_CODE2 = 'O' THEN
256 SELECT
257 ORDER_NUMBER
258 INTO L_ENTITY_VALUE
259 FROM
260 OE_ORDER_HEADERS
261 WHERE HEADER_ID = OBJECT_ID2;
262 ELSE
263 IF OBJECT_TYPE_CODE2 = 'C' THEN
264 SELECT
265 SUBSTRB(PARTY.PARTY_NAME
266 ,1
267 ,50)
268 INTO L_ENTITY_VALUE
269 FROM
270 HZ_PARTIES PARTY,
271 HZ_CUST_ACCOUNTS CUST_ACCT
272 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
273 AND CUST_ACCT.CUST_ACCOUNT_ID = OBJECT_ID2;
274 ELSE
275 IF OBJECT_TYPE_CODE2 = 'I' THEN
276 SELECT
277 DESCRIPTION
278 INTO L_ENTITY_VALUE
279 FROM
280 MTL_SYSTEM_ITEMS_VL
281 WHERE INVENTORY_ITEM_ID = OBJECT_ID2
282 AND NVL(ORGANIZATION_ID
283 ,0) = NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
284 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
285 ,0);
286 ELSE
287 IF OBJECT_TYPE_CODE2 = 'S' THEN
288 SELECT
289 SUBSTR((LOC.ADDRESS1 || ', ' || LOC.CITY || ' ' || LOC.STATE)
290 ,1
291 ,30)
292 INTO L_ENTITY_VALUE
293 FROM
294 HZ_CUST_SITE_USES_ALL SU,
295 HZ_PARTY_SITES PARTY_SITE,
296 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
297 HZ_LOCATIONS LOC,
298 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
299 WHERE SU.SITE_USE_ID = OBJECT_ID2
300 AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
301 AND SU.ORG_ID = MO_GLOBAL.GET_CURRENT_ORG_ID
302 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
303 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
304 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
305 AND NVL(ACCT_SITE.ORG_ID
306 ,-99) = NVL(LOC_ASSIGN.ORG_ID
307 ,-99);
308 ELSE
309 L_ENTITY_VALUE := OBJECT_ID2;
310 END IF;
311 END IF;
312 END IF;
313 END IF;
314 RETURN (L_ENTITY_VALUE);
315 RETURN NULL;
316 EXCEPTION
317 WHEN NO_DATA_FOUND THEN
318 RETURN ('Can not retrieve Value');
319 END C_ENTITY_VALUE2;
320
321 END ONT_OEXOHHSA_XMLP_PKG;
322
323
[Home] [Help]