1 PACKAGE BODY INV_INVARPAR_XMLP_PKG AS
2 /* $Header: INVARPARB.pls 120.3 2008/01/08 06:21:53 dwkrishn 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(1
11 ,'Failed in before report trigger:SRWINIT')*/NULL;
12 RAISE;
13 END;
14 DECLARE
15 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
16 BEGIN
17 FND_PROFILE.PUT('MFG_ORGANIZATION_ID'
18 ,P_ORG_ID_CHAR);
19 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
20 EXCEPTION
21 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
22 /*SRW.MESSAGE(020
23 ,'Failed in before report trigger, setting org profile ')*/NULL;
24 RAISE;
25 END;
26 BEGIN
27 SELECT
28 MEANING
29 INTO P_REJ_OPT_TITLE
30 FROM
31 MFG_LOOKUPS
32 WHERE LOOKUP_TYPE = 'SYS_YES_NO'
33 AND LOOKUP_CODE = NVL(P_INCLUDE_REJ_ITEMS
34 ,1);
35 EXCEPTION
36 WHEN OTHERS THEN
37 NULL;
38 END;
39 IF NVL(P_INCLUDE_REJ_ITEMS
40 ,1) = 1 THEN
41 P_INCLUDE_REJ_ITEMS_OPT := ' ';
42 ELSE
43 P_INCLUDE_REJ_ITEMS_OPT := 'and nvl(mpa.approval_status,1) <> 2 ';
44 END IF;
45 BEGIN
46 NULL;
47 EXCEPTION
48 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
49 /*SRW.MESSAGE(1
50 ,'Failed in before report trigger:MSTK')*/NULL;
51 RAISE;
52 END;
53 BEGIN
54 NULL;
55 EXCEPTION
56 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
57 /*SRW.MESSAGE(1
58 ,'Failed in before report trigger:MSTK:ORDERBY')*/NULL;
59 RAISE;
60 END;
61 BEGIN
62 NULL;
63 EXCEPTION
64 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
65 /*SRW.MESSAGE(1
66 ,'Failed in before report trigger:MTLL')*/NULL;
67 RAISE;
68 END;
69 DECLARE
70 M_RETURN_STATUS VARCHAR2(1);
71 M_MSG_COUNT NUMBER;
72 M_MSG_DATA VARCHAR2(2000);
73 BEGIN
74 IF (WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS => M_RETURN_STATUS
75 ,X_MSG_COUNT => M_MSG_COUNT
76 ,X_MSG_DATA => M_MSG_DATA
77 ,P_ORGANIZATION_ID => P_ORG_ID)) THEN
78 P_WMS_INSTALLED := 'TRUE';
79 END IF;
80 END;
81 RETURN (TRUE);
82 END BEFOREREPORT;
83
84 FUNCTION AFTERREPORT RETURN BOOLEAN IS
85 BEGIN
86 BEGIN
87 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
88 EXCEPTION
89 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
90 /*SRW.MESSAGE(1
91 ,'SRWEXIT failed')*/NULL;
92 END;
93 RETURN (TRUE);
94 END AFTERREPORT;
95
96 FUNCTION C_PHYS_INV_NAMEFORMULA RETURN VARCHAR2 IS
97 BEGIN
98 DECLARE
99 NAME VARCHAR2(40);
100 BEGIN
101 SELECT
102 PHYSICAL_INVENTORY_NAME
103 INTO NAME
104 FROM
105 MTL_PHYSICAL_INVENTORIES
106 WHERE ORGANIZATION_ID = P_ORG_ID
107 AND PHYSICAL_INVENTORY_ID = P_PHYS_INV_ID;
108 RETURN (NAME);
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 RETURN (NULL);
112 END;
113 RETURN NULL;
114 END C_PHYS_INV_NAMEFORMULA;
115
116 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
117 BEGIN
118 RETURN ('(' || R_CURRENCY_CODE || ')');
119 END C_CURRENCY_CODEFORMULA;
120
121 FUNCTION C_SORTFORMULA RETURN VARCHAR2 IS
122 BEGIN
123 IF P_SORT_ID = 1 THEN
124 RETURN ('ASC');
125 ELSE
126 RETURN ('DESC');
127 END IF;
128 RETURN NULL;
129 END C_SORTFORMULA;
130
131 FUNCTION CF_COST_GROUPFORMULA(COST_GROUP_ID IN NUMBER) RETURN CHAR IS
132 M_COST_GROUP VARCHAR2(10) := NULL;
133 BEGIN
134 IF ((P_WMS_INSTALLED = 'TRUE') AND (COST_GROUP_ID IS NOT NULL)) THEN
135 BEGIN
136 SELECT
137 COST_GROUP
138 INTO M_COST_GROUP
139 FROM
140 CST_COST_GROUPS
141 WHERE COST_GROUP_ID = COST_GROUP_ID;
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 M_COST_GROUP := 'ERROR';
145 END;
146 END IF;
147 RETURN (M_COST_GROUP);
148 END CF_COST_GROUPFORMULA;
149
150 FUNCTION CF_OUTERMOST_LPNFORMULA(OUTERMOST_LPN_ID IN NUMBER) RETURN CHAR IS
151 M_OUTERMOST_LPN VARCHAR2(30) := NULL;
152 BEGIN
153 IF ((P_WMS_INSTALLED = 'TRUE') AND (OUTERMOST_LPN_ID IS NOT NULL)) THEN
154 BEGIN
155 SELECT
156 LICENSE_PLATE_NUMBER
157 INTO M_OUTERMOST_LPN
158 FROM
159 WMS_LICENSE_PLATE_NUMBERS
160 WHERE LPN_ID = OUTERMOST_LPN_ID;
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 M_OUTERMOST_LPN := 'ERROR';
164 END;
165 END IF;
166 RETURN (M_OUTERMOST_LPN);
167 END CF_OUTERMOST_LPNFORMULA;
168
169 FUNCTION CF_PARENT_LPNFORMULA(PARENT_LPN_ID IN NUMBER) RETURN CHAR IS
170 M_PARENT_LPN VARCHAR2(30) := NULL;
171 BEGIN
172 IF ((P_WMS_INSTALLED = 'TRUE') AND (PARENT_LPN_ID IS NOT NULL)) THEN
173 BEGIN
174 SELECT
175 LICENSE_PLATE_NUMBER
176 INTO M_PARENT_LPN
177 FROM
178 WMS_LICENSE_PLATE_NUMBERS
179 WHERE LPN_ID = PARENT_LPN_ID;
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 M_PARENT_LPN := 'ERROR';
183 END;
184 END IF;
185 RETURN (M_PARENT_LPN);
186 END CF_PARENT_LPNFORMULA;
187
188 FUNCTION CF_APPROVER_FORMULA(EMP_ID IN NUMBER) RETURN CHAR IS
189 V_EMP_NAME VARCHAR2(240) := NULL;
190 BEGIN
191 IF (EMP_ID IS NOT NULL) THEN
192 BEGIN
193 SELECT
194 FULL_NAME
195 INTO V_EMP_NAME
196 FROM
197 MTL_EMPLOYEES_VIEW
198 WHERE ORGANIZATION_ID = P_ORG_ID
199 AND EMPLOYEE_ID = EMP_ID;
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 V_EMP_NAME := 'ERROR';
203 END;
204 END IF;
205 RETURN (V_EMP_NAME);
206 END CF_APPROVER_FORMULA;
207
208 FUNCTION CF_APPROVERFORMULA(EMP_ID IN NUMBER) RETURN CHAR IS
209 V_EMP_NAME VARCHAR2(240) := NULL;
210 BEGIN
211 IF (EMP_ID IS NOT NULL) THEN
212 BEGIN
213 SELECT
214 FULL_NAME
215 INTO V_EMP_NAME
216 FROM
217 MTL_EMPLOYEES_VIEW
218 WHERE ORGANIZATION_ID = P_ORG_ID
219 AND EMPLOYEE_ID = EMP_ID;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 V_EMP_NAME := 'ERROR';
223 END;
224 END IF;
225 RETURN (V_EMP_NAME);
226 END CF_APPROVERFORMULA;
227
228 END INV_INVARPAR_XMLP_PKG;
229
[Home] [Help]