1 PACKAGE BODY INV_INVRCWSR_XMLP_PKG AS
2 /* $Header: INVRCWSRB.pls 120.1 2007/12/25 10:48:52 dwkrishn noship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 BEGIN
5 SELECT
6 ORGANIZATION_CODE,
7 ORGANIZATION_NAME
8 INTO P_ORG_CODE,P_ORG_NAME
9 FROM
10 ORG_ORGANIZATION_DEFINITIONS
11 WHERE ORGANIZATION_ID = P_ORG_ID;
12 RETURN (TRUE);
13 END AFTERPFORM;
14
15 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
16 BEGIN
17 BEGIN
18 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
19 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
20 EXCEPTION
21 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
22 /*SRW.MESSAGE(1
23 ,'Before Report: Init')*/NULL;
24 END;
25 DECLARE
26 L_REPORT_NAME VARCHAR2(240);
27 BEGIN
28 SELECT
29 CP.USER_CONCURRENT_PROGRAM_NAME,
30 TO_CHAR(CR.REQUEST_DATE
31 ,'DD-MON-YYYY HH24:MI'),
32 TO_CHAR(CR.ACTUAL_START_DATE
33 ,'DD-MON-YYYY HH24:MI'),
34 CR.PRINTER
35 INTO L_REPORT_NAME,P_REQUEST_DATE,P_ACTUAL_START_DATE,P_PRINTER
36 FROM
37 FND_CONCURRENT_PROGRAMS_VL CP,
38 FND_CONCURRENT_REQUESTS CR
39 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
40 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
41 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
42 l_report_name := substr(l_report_name,1,instr(l_report_name,' (XML)'));
43 P_REPORT_NAME := L_REPORT_NAME;
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 P_REPORT_NAME := 'PAR Replenishment Count Worksheet';
47 P_REQUEST_DATE := '***';
48 P_ACTUAL_START_DATE := '***';
49 P_PRINTER := '***';
50 END;
51 BEGIN
52 SELECT
53 APPLICATION_NAME
54 INTO P_APPLICATION_NAME
55 FROM
56 FND_CONCURRENT_REQUESTS FCR,
57 FND_APPLICATION_VL FAV
58 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
59 AND FAV.APPLICATION_ID = FCR.PROGRAM_APPLICATION_ID;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 P_APPLICATION_NAME := '***';
63 END;
64 BEGIN
65 SELECT
66 RESPONSIBILITY_NAME
67 INTO P_RESPONSIBILITY_NAME
68 FROM
69 FND_CONCURRENT_REQUESTS FCR,
70 FND_RESPONSIBILITY_VL FRV
71 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
72 AND FRV.RESPONSIBILITY_ID = FCR.RESPONSIBILITY_ID;
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 P_RESPONSIBILITY_NAME := '***';
76 END;
77 BEGIN
78 NULL;
79 EXCEPTION
80 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
81 /*SRW.MESSAGE(1
82 ,'Before Report: Item Flex')*/NULL;
83 END;
84 SELECT
85 CHART_OF_ACCOUNTS_ID
86 INTO P_EXPENSEACCOUNT_FLEXNUM
87 FROM
88 ORG_ORGANIZATION_DEFINITIONS
89 WHERE ORGANIZATION_ID = P_ORG_ID;
90 BEGIN
91 NULL;
92 EXCEPTION
93 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
94 /*SRW.MESSAGE(1
95 ,'Before Report: Expense Account Flex')*/NULL;
96 END;
97 RETURN (TRUE);
98 END BEFOREREPORT;
99
100 FUNCTION AFTERREPORT RETURN BOOLEAN IS
101 BEGIN
102 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
103 RETURN (TRUE);
104 END AFTERREPORT;
105
106 FUNCTION CF_LOCATORFORMULA(LOCATOR_ID IN NUMBER
107 ,ORGANIZATION_ID IN NUMBER) RETURN VARCHAR2 IS
108 BEGIN
109 RETURN (INV_PROJECT.GET_LOCSEGS(LOCATOR_ID
110 ,ORGANIZATION_ID));
111 END CF_LOCATORFORMULA;
112
113 FUNCTION CF_PAR_LEVELFORMULA(LOCATOR_ID IN NUMBER
114 ,ITEM_ID IN NUMBER) RETURN NUMBER IS
115 L_PAR_LEVEL NUMBER;
116 BEGIN
117 SELECT
118 MAXIMUM_QUANTITY
119 INTO L_PAR_LEVEL
120 FROM
121 MTL_SECONDARY_LOCATORS MSL
122 WHERE MSL.ORGANIZATION_ID = P_ORG_ID
123 AND MSL.SECONDARY_LOCATOR = LOCATOR_ID
124 AND MSL.INVENTORY_ITEM_ID = ITEM_ID;
125 P_ITEM_LOCATOR_FOUND := 1;
126 RETURN (L_PAR_LEVEL);
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 P_ITEM_LOCATOR_FOUND := 2;
130 RETURN (NULL);
131 END CF_PAR_LEVELFORMULA;
132
133 FUNCTION CF_ERRORFORMULA(ERROR_CODE IN NUMBER
134 ,PROCESS_STATUS_CODE IN NUMBER
135 ,PLANNING_LEVEL IN NUMBER
136 ,QUANTITY_TRACKED IN NUMBER
137 ,COUNT_TYPE_CODE IN NUMBER
138 ,LOCATOR_ID IN NUMBER
139 ,CF_PAR_LEVEL IN NUMBER) RETURN CHAR IS
140 L_MEANING VARCHAR2(80);
141 L_ERROR_NUMBER NUMBER := NULL;
142 BEGIN
143 IF ERROR_CODE IS NOT NULL THEN
144 L_ERROR_NUMBER := ERROR_CODE;
145 ELSIF PROCESS_STATUS_CODE in (1,2,4) THEN
146 IF PLANNING_LEVEL = 2 THEN
147 L_ERROR_NUMBER := 19;
148 ELSIF (QUANTITY_TRACKED = 1 AND COUNT_TYPE_CODE = 1) THEN
149 L_ERROR_NUMBER := 13;
150 ELSIF LOCATOR_ID IS NULL THEN
151 L_ERROR_NUMBER := 19;
152 ELSIF COUNT_TYPE_CODE = 3 THEN
153 L_ERROR_NUMBER := 12;
154 ELSIF P_ITEM_LOCATOR_FOUND = 2 THEN
155 L_ERROR_NUMBER := 18;
156 ELSIF (COUNT_TYPE_CODE in (1,4) AND CF_PAR_LEVEL IS NULL) THEN
157 L_ERROR_NUMBER := 23;
158 END IF;
159 END IF;
160 IF L_ERROR_NUMBER IS NOT NULL THEN
161 SELECT
162 MFG.MEANING
163 INTO L_MEANING
164 FROM
165 MFG_LOOKUPS MFG
166 WHERE MFG.LOOKUP_TYPE = 'MTL_REPLENISHMENT_ERROR'
167 AND MFG.LOOKUP_CODE = L_ERROR_NUMBER;
168 END IF;
169 RETURN (L_MEANING);
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 RETURN ('***');
173 END CF_ERRORFORMULA;
174
175 FUNCTION CF_PROCESS_STATUSFORMULA(PROCESS_STATUS_CODE IN NUMBER) RETURN CHAR IS
176 BEGIN
177 IF PROCESS_STATUS_CODE IS NULL OR PROCESS_STATUS_CODE not in (1,2,3,4,5) THEN
178 RETURN ('***');
179 ELSIF PROCESS_STATUS_CODE = 1 THEN
180 RETURN (P_STATUS_HOLD);
181 ELSIF PROCESS_STATUS_CODE = 2 THEN
182 RETURN (P_STATUS_PENDING);
183 ELSIF PROCESS_STATUS_CODE = 3 THEN
184 RETURN (P_STATUS_PROCESSING);
185 ELSIF PROCESS_STATUS_CODE = 4 THEN
186 RETURN (P_STATUS_ERROR);
187 ELSIF PROCESS_STATUS_CODE = 5 THEN
188 RETURN (P_STATUS_COMPLETED);
189 END IF;
190 END CF_PROCESS_STATUSFORMULA;
191
192 FUNCTION CF_SOURCE_TYPEFORMULA(SOURCE_TYPE_CODE IN NUMBER) RETURN CHAR IS
193 BEGIN
194 IF SOURCE_TYPE_CODE IS NULL OR SOURCE_TYPE_CODE not in (1,2,3) THEN
195 RETURN ('');
196 ELSIF SOURCE_TYPE_CODE = 1 THEN
197 RETURN (P_SOURCE_TYPE_INV);
198 ELSIF SOURCE_TYPE_CODE = 2 THEN
199 RETURN (P_SOURCE_TYPE_SUPPLIER);
200 ELSIF SOURCE_TYPE_CODE = 3 THEN
201 RETURN (P_SOURCE_TYPE_SUB);
202 END IF;
203 END CF_SOURCE_TYPEFORMULA;
204
205 FUNCTION CF_COUNT_TYPEFORMULA(COUNT_TYPE_CODE IN NUMBER) RETURN CHAR IS
206 BEGIN
207 IF COUNT_TYPE_CODE IS NULL OR COUNT_TYPE_CODE not in (1,2,4) THEN
208 RETURN ('***');
209 ELSIF COUNT_TYPE_CODE = 1 THEN
210 RETURN (P_COUNT_TYPE_ONHAND);
211 ELSIF COUNT_TYPE_CODE = 2 THEN
212 RETURN (P_COUNT_TYPE_ORDER);
213 ELSIF COUNT_TYPE_CODE = 4 THEN
214 RETURN (P_COUNT_TYPE_PAR);
215 END IF;
216 END CF_COUNT_TYPEFORMULA;
217
218 FUNCTION CF_SOURCE_ORG_CODEFORMULA(SOURCE_ORG_ID IN NUMBER) RETURN CHAR IS
219 L_ORG_CODE VARCHAR2(3);
220 BEGIN
221 IF SOURCE_ORG_ID IS NULL THEN
222 RETURN ('');
223 ELSE
224 SELECT
225 ORGANIZATION_CODE
226 INTO L_ORG_CODE
227 FROM
228 ORG_ORGANIZATION_DEFINITIONS
229 WHERE ORGANIZATION_ID = SOURCE_ORG_ID;
230 RETURN (L_ORG_CODE);
231 END IF;
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 RETURN ('***');
235 END CF_SOURCE_ORG_CODEFORMULA;
236
237 FUNCTION IS_COUNT_PROCESSED(PROCESS_STATUS_CODE IN NUMBER) RETURN BOOLEAN IS
238 BEGIN
239 IF ((PROCESS_STATUS_CODE IS NOT NULL) AND (PROCESS_STATUS_CODE = 5)) THEN
240 RETURN (TRUE);
241 END IF;
242 RETURN (FALSE);
243 END IS_COUNT_PROCESSED;
244
245 FUNCTION SETUP_ERROR(CF_ERROR IN VARCHAR2) RETURN BOOLEAN IS
246 BEGIN
247 IF CF_ERROR IS NOT NULL THEN
248 RETURN (TRUE);
249 END IF;
250 RETURN (FALSE);
251 END SETUP_ERROR;
252
253 END INV_INVRCWSR_XMLP_PKG;
254
[Home] [Help]