1 PACKAGE BODY ENG_ENGRCNDR_XMLP_PKG AS
2 /* $Header: ENGRCNDRB.pls 120.2 2008/04/08 08:34:06 dwkrishn noship $ */
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 BEGIN
299 IF EMPLOYEE_ID IS NULL THEN
300 NULL;
301 ELSE
302 SELECT
303 FULL_NAME
304 INTO EMP_NAME
305 FROM
306 MTL_EMPLOYEES_VIEW
307 WHERE ORGANIZATION_ID = ORGANIZATION_ID
308 AND EMPLOYEE_ID = EMPLOYEE_ID;
309 END IF;
310 RETURN (EMP_NAME);
311 RETURN NULL;
312 EXCEPTION
313 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
314 RETURN NULL;
315 END GET_NAME;
316
317 FUNCTION GET_REQ_NAME(REQUESTOR_ID IN NUMBER) RETURN VARCHAR2 IS
318 REQ_NAME VARCHAR2(240);
319 BEGIN
320 IF REQUESTOR_ID IS NULL THEN
321 NULL;
322 ELSE
323 SELECT
324 EMP.FULL_NAME
325 INTO REQ_NAME
326 FROM
327 MTL_EMPLOYEES_CURRENT_VIEW EMP
328 WHERE EMP.ORGANIZATION_ID = P_ORG_ID
329 AND EMP.EMPLOYEE_ID = (
330 SELECT
331 HZ.PERSON_IDENTIFIER
332 FROM
333 HZ_PARTIES HZ
334 WHERE HZ.PARTY_ID = REQUESTOR_ID )
335 AND EMP.SYSTEM_PERSON_TYPE = 'EMP';
336 END IF;
337 RETURN (REQ_NAME);
338 RETURN NULL;
339 EXCEPTION
340 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
341 RETURN NULL;
342 END GET_REQ_NAME;
343
344 FUNCTION GET_ITEM_TYPE(ITEM_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
345 ITEM_TYPE_NAME VARCHAR2(80);
346 BEGIN
347 IF ITEM_TYPE IS NULL THEN
348 NULL;
349 ELSE
350 SELECT
351 MEANING
352 INTO ITEM_TYPE_NAME
353 FROM
354 FND_COMMON_LOOKUPS
355 WHERE LOOKUP_CODE = ITEM_TYPE
356 AND LOOKUP_TYPE = 'ITEM_TYPE';
357 END IF;
358 RETURN (ITEM_TYPE_NAME);
359 RETURN NULL;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 RETURN NULL;
363 END GET_ITEM_TYPE;
364
365 FUNCTION AFTERPFORM RETURN BOOLEAN IS
366 BEGIN
367 RETURN (TRUE);
368 END AFTERPFORM;
369
370 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
371 BEGIN
372 RETURN (TRUE);
373 END BETWEENPAGE;
374
375 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
376 BEGIN
377 RETURN (TRUE);
378 END BEFOREPFORM;
379
380 END ENG_ENGRCNDR_XMLP_PKG;
381