1 PACKAGE BODY INV_INVARPAR_XMLP_PKG AS
2 /* $Header: INVARPARB.pls 120.4 2011/03/02 11:17:51 sanjeevs ship $ */
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
134 BEGIN
131 --bug#11737196 changing the function argument
132 FUNCTION CF_COST_GROUPFORMULA(P_COST_GROUP_ID IN NUMBER) RETURN CHAR IS
133 M_COST_GROUP VARCHAR2(10) := NULL;
135 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_COST_GROUP_ID IS NOT NULL)) THEN
136 BEGIN
137 SELECT
138 COST_GROUP
139 INTO M_COST_GROUP
140 FROM
141 CST_COST_GROUPS
142 WHERE COST_GROUP_ID = P_COST_GROUP_ID;
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 M_COST_GROUP := 'ERROR';
146 END;
147 END IF;
148 RETURN (M_COST_GROUP);
149 END CF_COST_GROUPFORMULA;
150
151 --bug#11737196 changing the function argument
152 FUNCTION CF_OUTERMOST_LPNFORMULA(P_OUTERMOST_LPN_ID IN NUMBER) RETURN CHAR IS
153 M_OUTERMOST_LPN VARCHAR2(30) := NULL;
154 BEGIN
155 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_OUTERMOST_LPN_ID IS NOT NULL)) THEN
156 BEGIN
157 SELECT
158 LICENSE_PLATE_NUMBER
159 INTO M_OUTERMOST_LPN
160 FROM
161 WMS_LICENSE_PLATE_NUMBERS
162 WHERE LPN_ID = P_OUTERMOST_LPN_ID;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 M_OUTERMOST_LPN := 'ERROR';
166 END;
167 END IF;
168 RETURN (M_OUTERMOST_LPN);
169 END CF_OUTERMOST_LPNFORMULA;
170
171 --bug#11737196 changing the function argument
172 FUNCTION CF_PARENT_LPNFORMULA(P_PARENT_LPN_ID IN NUMBER) RETURN CHAR IS
173 M_PARENT_LPN VARCHAR2(30) := NULL;
174 BEGIN
175 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_PARENT_LPN_ID IS NOT NULL)) THEN
176 BEGIN
177 SELECT
178 LICENSE_PLATE_NUMBER
179 INTO M_PARENT_LPN
180 FROM
181 WMS_LICENSE_PLATE_NUMBERS
182 WHERE LPN_ID = P_PARENT_LPN_ID;
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 M_PARENT_LPN := 'ERROR';
186 END;
187 END IF;
188 RETURN (M_PARENT_LPN);
189 END CF_PARENT_LPNFORMULA;
190
191 FUNCTION CF_APPROVER_FORMULA(EMP_ID IN NUMBER) RETURN CHAR IS
192 V_EMP_NAME VARCHAR2(240) := NULL;
193 BEGIN
194 IF (EMP_ID IS NOT NULL) THEN
195 BEGIN
196 SELECT
197 FULL_NAME
198 INTO V_EMP_NAME
199 FROM
200 MTL_EMPLOYEES_VIEW
201 WHERE ORGANIZATION_ID = P_ORG_ID
202 AND EMPLOYEE_ID = EMP_ID;
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 V_EMP_NAME := 'ERROR';
206 END;
207 END IF;
208 RETURN (V_EMP_NAME);
209 END CF_APPROVER_FORMULA;
210
211 FUNCTION CF_APPROVERFORMULA(EMP_ID IN NUMBER) RETURN CHAR IS
212 V_EMP_NAME VARCHAR2(240) := NULL;
213 BEGIN
214 IF (EMP_ID IS NOT NULL) THEN
215 BEGIN
216 SELECT
217 FULL_NAME
218 INTO V_EMP_NAME
219 FROM
220 MTL_EMPLOYEES_VIEW
221 WHERE ORGANIZATION_ID = P_ORG_ID
222 AND EMPLOYEE_ID = EMP_ID;
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 V_EMP_NAME := 'ERROR';
226 END;
227 END IF;
228 RETURN (V_EMP_NAME);
229 END CF_APPROVERFORMULA;
230
231 END INV_INVARPAR_XMLP_PKG;
232
[Home] [Help]