1 PACKAGE BODY ENG_ENGRCNDR_XMLP_PKG AS
2 /* $Header: ENGRCNDRB.pls 120.2.12010000.2 2010/02/01 22:31:30 vbrobbey ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 C_DATE_FORMAT varchar2(22);
5 BEGIN
6 C_DATE_FORMAT := 'DD-MON-YY';
7 CP_FROM_DATE := to_char(P_FROM_DATE,C_DATE_FORMAT);
8
9 CP_TO_DATE := to_char(P_TO_DATE,C_DATE_FORMAT);
10
11 DECLARE
12 STATUS VARCHAR2(80);
13 L_YES VARCHAR2(4);
14 L_NO VARCHAR2(4);
15 BEGIN
16 SELECT
17 SUBSTR(L1.MEANING
18 ,1
19 ,4),
20 SUBSTR(L2.MEANING
21 ,1
22 ,4)
23 INTO L_YES,L_NO
24 FROM
25 MFG_LOOKUPS L1,
26 MFG_LOOKUPS L2
27 WHERE L1.LOOKUP_TYPE = 'SYS_YES_NO'
28 AND L1.LOOKUP_CODE = 1
29 AND L2.LOOKUP_TYPE = 'SYS_YES_NO'
30 AND L2.LOOKUP_CODE = 2;
31 P_YES := L_YES;
32 P_NO := L_NO;
33 IF P_ADDITIONAL_DETAIL = 1 THEN
34 P_ADDITIONAL_CODE := P_YES;
35 ELSE
36 P_ADDITIONAL_CODE := P_NO;
37 END IF;
38 IF P_ORDER_ENTRY_DETAIL = 1 THEN
39 P_ORDER_ENTRY_CODE := P_YES;
40 ELSE
41 P_ORDER_ENTRY_CODE := P_NO;
42 END IF;
43 IF P_STATUS IS NOT NULL THEN
44 SELECT
45 MEANING
46 INTO STATUS
47 FROM
48 MFG_LOOKUPS
49 WHERE LOOKUP_CODE = P_STATUS
50 AND LOOKUP_TYPE = 'ECG_ECN_STATUS';
51 P_STATUS_CHAR := STATUS;
52 END IF;
53 END;
54 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
55 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
56 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" NUM=":P_RI_STRUCT_NUM"
57 APPL_SHORT_NAME="INV" OUTPUT=":P_RI_FLEXDATA"
58 MODE="SELECT" DISPLAY="ALL" TABLEALIAS="msi1"')*/NULL;
59 DECLARE
60 L_ORG_ID NUMBER;
61 L_INDEX NUMBER;
62 L_ORG_CODE_LIST INV_ORGHIERARCHY_PVT.ORGID_TBL_TYPE;
63 L_ORG_NAME VARCHAR2(60);
64 L_RESP_ID NUMBER;
65 L_RESP_APPL_ID NUMBER;
66 BEGIN
67 SELECT
68 BOM_LISTS_S.NEXTVAL
69 INTO P_SEQUENCE_ID
70 FROM
71 DUAL;
72 IF P_ALL_ORGS = 1 THEN
73 L_RESP_ID := FND_PROFILE.VALUE('RESP_ID');
74 L_RESP_APPL_ID := FND_PROFILE.VALUE('RESP_APPL_ID');
75 L_INDEX := 0;
76 FOR c_org IN (SELECT
77 ORGANIZATION_ID
78 FROM
79 MTL_PARAMETERS MP
80 WHERE MP.MASTER_ORGANIZATION_ID = (
81 SELECT
82 MP1.MASTER_ORGANIZATION_ID
83 FROM
84 MTL_PARAMETERS MP1
85 WHERE MP1.ORGANIZATION_ID = P_ORG_ID )
86 AND MP.ORGANIZATION_ID IN (
87 SELECT
88 ORGANIZATION_ID
89 FROM
90 ORG_ACCESS_VIEW
91 WHERE RESPONSIBILITY_ID = L_RESP_ID
92 AND RESP_APPLICATION_ID = L_RESP_APPL_ID )) LOOP
93 L_INDEX := L_INDEX + 1;
94 INSERT INTO BOM_LISTS
95 (SEQUENCE_ID
96 ,ORGANIZATION_ID
97 ,ALTERNATE_DESIGNATOR)
98 VALUES (P_SEQUENCE_ID
99 ,C_ORG.ORGANIZATION_ID
100 ,L_INDEX);
101 END LOOP;
102 DP_ALL_ORGS := 'Yes';
103 ELSE
104 IF (P_ORG_HIERARCHY IS NULL) THEN
105 INSERT INTO BOM_LISTS
106 (SEQUENCE_ID
107 ,ORGANIZATION_ID
108 ,ALTERNATE_DESIGNATOR)
109 VALUES (P_SEQUENCE_ID
110 ,P_ORG_ID
111 ,1);
112 ELSE
113 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(P_ORG_HIERARCHY
114 ,P_ORG_ID
115 ,L_ORG_CODE_LIST);
116 IF (L_ORG_CODE_LIST.COUNT > 0) THEN
117 FOR l_index IN 1 .. L_ORG_CODE_LIST.COUNT LOOP
118 INSERT INTO BOM_LISTS
119 (SEQUENCE_ID
120 ,ORGANIZATION_ID
121 ,ALTERNATE_DESIGNATOR)
122 VALUES (P_SEQUENCE_ID
123 ,L_ORG_CODE_LIST(L_INDEX)
124 ,L_INDEX);
125 END LOOP;
126 END IF;
127 END IF;
128 DP_ALL_ORGS := 'No';
129 END IF;
130 END;
131 RETURN (TRUE);
132 END BEFOREREPORT;
133
134 FUNCTION AFTERREPORT RETURN BOOLEAN IS
135 BEGIN
136 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
137 RETURN (TRUE);
138 END AFTERREPORT;
139
140 FUNCTION GET_REV(REVISED_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
141 REV VARCHAR2(3);
142 ORG_ID NUMBER := P_ORG_ID;
143 ITEM_ID NUMBER := REVISED_ITEM_ID;
144 EFF_DATE DATE := SYSDATE;
145 BEGIN
146 SELECT
147 REV.REVISION
148 INTO REV
149 FROM
150 MTL_ITEM_REVISIONS REV
151 WHERE REV.INVENTORY_ITEM_ID = ITEM_ID
152 AND REV.ORGANIZATION_ID = ORG_ID
153 AND REV.EFFECTIVITY_DATE = (
154 SELECT
155 MAX(EFFECTIVITY_DATE)
156 FROM
157 MTL_ITEM_REVISIONS
158 WHERE EFFECTIVITY_DATE <= EFF_DATE
159 AND ORGANIZATION_ID = ORG_ID
160 AND INVENTORY_ITEM_ID = ITEM_ID
161 AND IMPLEMENTATION_DATE is not null )
162 AND REV.IMPLEMENTATION_DATE is not null;
163 RETURN (REV);
164 RETURN NULL;
165 EXCEPTION
166 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
167 RETURN NULL;
168 END GET_REV;
169
170 FUNCTION LATEST_REV(REVISED_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
171 LATE_REV VARCHAR2(3);
172 ORG_ID NUMBER := P_ORG_ID;
173 ITEM_ID NUMBER := REVISED_ITEM_ID;
174 BEGIN
175 SELECT
176 REV.REVISION
177 INTO LATE_REV
178 FROM
179 MTL_ITEM_REVISIONS REV
180 WHERE REV.INVENTORY_ITEM_ID = ITEM_ID
181 AND REV.ORGANIZATION_ID = ORG_ID
182 AND REV.EFFECTIVITY_DATE = (
183 SELECT
184 MAX(EFFECTIVITY_DATE)
185 FROM
186 MTL_ITEM_REVISIONS
187 WHERE ORGANIZATION_ID = ORG_ID
188 AND INVENTORY_ITEM_ID = ITEM_ID );
189 RETURN (LATE_REV);
190 RETURN NULL;
191 EXCEPTION
192 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
193 RETURN NULL;
194 END LATEST_REV;
195
196 FUNCTION LIMIT_CHANGE_NOTICES RETURN CHARACTER IS
197 LIMIT_CHANGE_NOTICES VARCHAR2(80);
198 BEGIN
199 IF (P_FROM_NOTICE IS NOT NULL) THEN
200 IF (P_TO_NOTICE IS NOT NULL) THEN
201 LIMIT_CHANGE_NOTICES := ' AND EEC.CHANGE_NOTICE BETWEEN ''' || P_FROM_NOTICE || ''' AND ''' || P_TO_NOTICE || '''';
202 ELSE
203 LIMIT_CHANGE_NOTICES := ' AND EEC.CHANGE_NOTICE >= ''' || P_FROM_NOTICE || '''';
204 END IF;
205 ELSE
206 IF (P_TO_NOTICE IS NOT NULL) THEN
207 LIMIT_CHANGE_NOTICES := ' AND EEC.CHANGE_NOTICE <= ''' || P_TO_NOTICE || '''';
208 ELSE
209 LIMIT_CHANGE_NOTICES := ' ';
210 END IF;
211 END IF;
212 RETURN (LIMIT_CHANGE_NOTICES);
213 END LIMIT_CHANGE_NOTICES;
214
215 FUNCTION OPTIONAL_DISPFORMULA(OPTIONAL IN NUMBER) RETURN VARCHAR2 IS
216 BEGIN
217 IF OPTIONAL = 1 THEN
218 RETURN (P_YES);
219 ELSE
220 RETURN (P_NO);
221 END IF;
222 RETURN NULL;
223 END OPTIONAL_DISPFORMULA;
224
225 FUNCTION COSTED_DISPFORMULA(COSTED IN NUMBER) RETURN VARCHAR2 IS
226 BEGIN
227 IF COSTED = 1 THEN
228 RETURN (P_YES);
229 ELSE
230 RETURN (P_NO);
231 END IF;
232 RETURN NULL;
233 END COSTED_DISPFORMULA;
234
235 FUNCTION CHECK_ATP_DISPFORMULA(CHECK_ATP IN NUMBER) RETURN VARCHAR2 IS
236 BEGIN
237 IF CHECK_ATP = 1 THEN
238 RETURN (P_YES);
239 ELSE
240 RETURN (P_NO);
241 END IF;
242 RETURN NULL;
243 END CHECK_ATP_DISPFORMULA;
244
245 FUNCTION MUTUALLY_EXCLUSIVE_DISPFORMULA(MUTUALLY_EXCLUSIVE_OPTION IN NUMBER) RETURN VARCHAR2 IS
246 BEGIN
247 IF MUTUALLY_EXCLUSIVE_OPTION = 1 THEN
248 RETURN (P_YES);
249 ELSE
250 RETURN (P_NO);
251 END IF;
252 RETURN NULL;
253 END MUTUALLY_EXCLUSIVE_DISPFORMULA;
254
255 FUNCTION REQUIRED_TO_SHIP_DISPFORMULA(REQUIRED_TO_SHIP IN NUMBER) RETURN VARCHAR2 IS
256 BEGIN
257 IF REQUIRED_TO_SHIP = 1 THEN
258 RETURN (P_YES);
259 ELSE
260 RETURN (P_NO);
261 END IF;
262 RETURN NULL;
263 END REQUIRED_TO_SHIP_DISPFORMULA;
264
265 FUNCTION REQUIRED_FOR_REVENUE_DISPFORMU(REQUIRED_FOR_REVENUE IN NUMBER) RETURN VARCHAR2 IS
266 BEGIN
267 IF REQUIRED_FOR_REVENUE = 1 THEN
268 RETURN (P_YES);
269 ELSE
270 RETURN (P_NO);
271 END IF;
272 RETURN NULL;
273 END REQUIRED_FOR_REVENUE_DISPFORMU;
274
275 FUNCTION INCLUDE_ON_SHIP_DISPFORMULA(INCLUDE_ON_SHIPPING_DOCUM IN NUMBER) RETURN VARCHAR2 IS
276 BEGIN
277 IF INCLUDE_ON_SHIPPING_DOCUM = 1 THEN
278 RETURN (P_YES);
279 ELSE
280 RETURN (P_NO);
281 END IF;
282 RETURN NULL;
283 END INCLUDE_ON_SHIP_DISPFORMULA;
284
285 FUNCTION QUANTITY_RELATED_DISPFORMULA(QUANTITY_RELATED IN NUMBER) RETURN VARCHAR2 IS
286 BEGIN
287 IF QUANTITY_RELATED = 1 THEN
288 RETURN (P_YES);
289 ELSE
290 RETURN (P_NO);
291 END IF;
292 RETURN NULL;
293 END QUANTITY_RELATED_DISPFORMULA;
294
295 FUNCTION GET_NAME(EMPLOYEE_ID IN NUMBER
296 ,ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
297 EMP_NAME VARCHAR2(240);
298 L_ORGANIZATION_ID NUMBER := ORGANIZATION_ID; -- Added for bug 7381270
299 L_EMPLOYEE_ID NUMBER := EMPLOYEE_ID; -- Added for bug 7381270
300 BEGIN
301 IF L_EMPLOYEE_ID IS NULL THEN --Changed for bug 7381270
302 NULL;
303 ELSE
304 SELECT
305 FULL_NAME
306 INTO EMP_NAME
307 FROM
308 MTL_EMPLOYEES_VIEW
309 WHERE ORGANIZATION_ID = L_ORGANIZATION_ID --Changed for bug 7381270
310 AND EMPLOYEE_ID = L_EMPLOYEE_ID; --Changed for bug 7381270
311 END IF;
312 RETURN (EMP_NAME);
313 RETURN NULL;
314 EXCEPTION
315 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
316 RETURN NULL;
317 END GET_NAME;
318
319 FUNCTION GET_REQ_NAME(REQUESTOR_ID IN NUMBER) RETURN VARCHAR2 IS
320 REQ_NAME VARCHAR2(240);
321 BEGIN
322 IF REQUESTOR_ID IS NULL THEN
323 NULL;
324 ELSE
325 SELECT
326 EMP.FULL_NAME
327 INTO REQ_NAME
328 FROM
329 MTL_EMPLOYEES_CURRENT_VIEW EMP
330 WHERE EMP.ORGANIZATION_ID = P_ORG_ID
331 AND EMP.EMPLOYEE_ID = (
332 SELECT
333 HZ.PERSON_IDENTIFIER
334 FROM
335 HZ_PARTIES HZ
336 WHERE HZ.PARTY_ID = REQUESTOR_ID )
337 AND EMP.SYSTEM_PERSON_TYPE = 'EMP';
338 END IF;
339 RETURN (REQ_NAME);
340 RETURN NULL;
341 EXCEPTION
342 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
343 RETURN NULL;
344 END GET_REQ_NAME;
345
346 FUNCTION GET_ITEM_TYPE(ITEM_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
347 ITEM_TYPE_NAME VARCHAR2(80);
348 BEGIN
349 IF ITEM_TYPE IS NULL THEN
350 NULL;
351 ELSE
352 SELECT
353 MEANING
354 INTO ITEM_TYPE_NAME
355 FROM
356 FND_COMMON_LOOKUPS
357 WHERE LOOKUP_CODE = ITEM_TYPE
358 AND LOOKUP_TYPE = 'ITEM_TYPE';
359 END IF;
360 RETURN (ITEM_TYPE_NAME);
361 RETURN NULL;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 RETURN NULL;
365 END GET_ITEM_TYPE;
366
367 FUNCTION AFTERPFORM RETURN BOOLEAN IS
368 BEGIN
369 RETURN (TRUE);
370 END AFTERPFORM;
371
372 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
373 BEGIN
374 RETURN (TRUE);
375 END BETWEENPAGE;
376
377 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
378 BEGIN
379 RETURN (TRUE);
380 END BEFOREPFORM;
381
382 END ENG_ENGRCNDR_XMLP_PKG;
383
[Home] [Help]