[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRIVTW_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRIVTW_XMLP_PKG AS
2 /* $Header: CSTRIVTWB.pls 120.0 2007/12/24 10:04:39 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID:= FND_GLOBAL.CONC_REQUEST_ID;
6 LP_BATCH_ID:= P_BATCH_ID;
7 P_ORGANIZATION_ID1:= P_ORGANIZATION_ID;
8
9
10 DECLARE
11 L_PROCESS_ENABLED_FLAG MTL_PARAMETERS.PROCESS_ENABLED_FLAG%TYPE;
12 L_ORGANIZATION_CODE MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
13 BEGIN
14 P_DESCRIPTION_v:=P_DESCRIPTION;
15 P_WORK_ORDER_ID_v:=P_WORK_ORDER_ID;
16 P_ITEM_TYPE_v:=P_ITEM_TYPE;
17 P_ITEM_ID_v:=P_ITEM_ID;
18 P_CATEGORY_SET_ID_v:=P_CATEGORY_SET_ID;
19 P_CATEGORY_ID_v:=P_CATEGORY_ID;
20 P_PROJECT_ID_v:=P_PROJECT_ID;
21 P_ADJUSTMENT_ACCOUNT_v:=P_ADJUSTMENT_ACCOUNT;
22 P_CUTOFF_DATE_v:=P_CUTOFF_DATE;
23 SELECT
24 NVL(PROCESS_ENABLED_FLAG
25 ,'N'),
26 ORGANIZATION_CODE
27 INTO L_PROCESS_ENABLED_FLAG,L_ORGANIZATION_CODE
28 FROM
29 MTL_PARAMETERS
30 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID1;
31
32 IF NVL(L_PROCESS_ENABLED_FLAG
33 ,'N') = 'Y' THEN
34 FND_MESSAGE.SET_NAME('CST'
35 ,'CST_PROCESS_ORG_ERROR');
36 FND_MESSAGE.SET_TOKEN('ORGCODE'
37 ,L_ORGANIZATION_CODE);
38 /*SRW.MESSAGE('2001'
39 ,'BOM_CSTRIVTW_XMLP_PKG' || FND_MESSAGE.GET || ')')*/NULL;
40
41 RETURN (FALSE);
42
43 END IF;
44 END;
45 DECLARE
46 L_FCN_CURRENCY VARCHAR2(15);
47 BEGIN
48
49 SELECT
50 SOB.CURRENCY_CODE
51 INTO L_FCN_CURRENCY
52 FROM
53 GL_SETS_OF_BOOKS SOB,
54 ORG_ORGANIZATION_DEFINITIONS OOD
55 WHERE OOD.ORGANIZATION_ID = P_ORGANIZATION_ID1
56 AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID;
57 P_CURRENCY_CODE := L_FCN_CURRENCY;
58 SELECT
59 O.ORGANIZATION_NAME,
60 O.ORGANIZATION_CODE,
61 NVL(MINIMUM_ACCOUNTABLE_UNIT
62 ,POWER(10
63 ,NVL(-PRECISION
64 ,0)))
65 INTO CP_ORG_NAME,CP_ORG_CODE,ROUND_UNIT
66 FROM
67 ORG_ORGANIZATION_DEFINITIONS O,
68 FND_CURRENCIES FC
69 WHERE FC.CURRENCY_CODE = P_CURRENCY_CODE
70 AND O.ORGANIZATION_ID = P_ORGANIZATION_ID1;
71
72 END;
73 DECLARE
74 L_BATCH_ID NUMBER;
75 L_RETCODE NUMBER:=0;
76 L_USER_ID NUMBER;
77 L_LOGIN_ID NUMBER;
78 L_PROG_APPL_ID NUMBER;
79 L_PROG_ID NUMBER;
80 L_ERRBUF VARCHAR2(240);
81 PROC_ERROR EXCEPTION;
82 BEGIN
83
84
85 IF (P_CONC_REQUEST_ID IS NOT NULL) THEN
86
87 SELECT
88 NVL(REQUESTED_BY
89 ,-1),
90 NVL(CONC_LOGIN_ID
91 ,-1),
92 NVL(PROGRAM_APPLICATION_ID
93 ,-1),
94 NVL(CONCURRENT_PROGRAM_ID
95 ,-1)
96 INTO L_USER_ID,L_LOGIN_ID,L_PROG_APPL_ID,L_PROG_ID
97 FROM
98 FND_CONCURRENT_REQUESTS
99 WHERE REQUEST_ID = P_CONC_REQUEST_ID
100 AND ROWNUM = 1;
101
102 END IF;
103
104
105
106 IF (P_TRANSFER_IPV = 1) THEN
107 L_BATCH_ID := CSTPPIPV.TRF_INVOICE_TO_WIP(ERRBUF => L_ERRBUF
108 ,RETCODE => L_RETCODE
109 ,P_ORGANIZATION_ID => P_ORGANIZATION_ID1
110 ,P_DESCRIPTION => P_DESCRIPTION
111 ,P_WORK_ORDER_ID => P_WORK_ORDER_ID
112 ,P_ITEM_TYPE => P_ITEM_TYPE
113 ,P_ITEM_OPTION => P_ITEM_OPTION
114 ,P_SPECIFIC_ITEM_ID => P_ITEM_ID
115 ,P_CATEGORY_SET_ID => P_CATEGORY_SET_ID
116 ,P_CATEGORY_ID => P_CATEGORY_ID
117 ,P_PROJECT_ID => P_PROJECT_ID
118 ,P_ADJ_ACCOUNT => P_ADJUSTMENT_ACCOUNT
119 ,P_CUTOFF_DATE => P_CUTOFF_DATE
120 ,P_TRANSACTION_PROCESS_MODE => P_TRANSACTION_PROCESS_MODE
121 ,P_REQUEST_ID => P_CONC_REQUEST_ID
122 ,P_USER_ID => L_USER_ID
123 ,P_LOGIN_ID => L_LOGIN_ID
124 ,P_PROG_APPL_ID => L_PROG_APPL_ID
125 ,P_PROG_ID => L_PROG_ID);
126
127
128
129 IF L_RETCODE <> 0 THEN
130 RAISE PROC_ERROR;
131 END IF;
132 --P_BATCH_ID := L_BATCH_ID;
133 LP_BATCH_ID := L_BATCH_ID;
134 END IF;
135
136 IF (P_PRINT_REPORT = 2) THEN
137 /*SRW.SET_MAXROW('Q_IPV'
138 ,0)*/NULL;
139 ELSE
140 NULL;
141 IF (P_TRANSFER_IPV = 2) THEN
142 SELECT
143 DESCRIPTION,
144 WIP_ENTITY_ID,
145 ITEM_TYPE,
146 SPECIFIC_ITEM_ID,
147 CATEGORY_SET_ID,
148 CATEGORY_ID,
149 SPECIFIC_PROJECT_ID,
150 ADJUSTMENT_ACCOUNT,
151 TO_CHAR(CUTOFF_DATE
152 ,'YYYY/MM/DD HH24:MI:SS')
153 /*INTO P_DESCRIPTION,
154 P_WORK_ORDER_ID,
155 P_ITEM_TYPE,
156 P_ITEM_ID,
157 P_CATEGORY_SET_ID,
158 P_CATEGORY_ID,
159 P_PROJECT_ID,
160 P_ADJUSTMENT_ACCOUNT,
161 P_CUTOFF_DATE*/
162 INTO P_DESCRIPTION_v,
163 P_WORK_ORDER_ID_v,
164 P_ITEM_TYPE_v,
165 P_ITEM_ID_v,
166 P_CATEGORY_SET_ID_v,
167 P_CATEGORY_ID_v,
168 P_PROJECT_ID_v,
169 P_ADJUSTMENT_ACCOUNT_v,
170 P_CUTOFF_DATE_v
171 FROM
172 CST_AP_VARIANCE_BATCHES
173 WHERE BATCH_ID = LP_BATCH_ID;
174 IF (P_ITEM_ID_v IS NOT NULL) THEN
175 P_ITEM_OPTION_v := 2;
176 ELSE
177 IF (P_CATEGORY_ID_v IS NOT NULL) THEN
178 P_ITEM_OPTION_v := 5;
179 ELSE
180 P_ITEM_OPTION_v := 1;
181 END IF;
182 END IF;
183
184 END IF;
185 /*SRW.MESSAGE('999'
186 ,'Description: ' || P_DESCRIPTION)*/NULL;
187 IF (P_WORK_ORDER_ID_V IS NOT NULL) THEN
188 SELECT
189 WIP_ENTITY_NAME
190 INTO CP_WORK_ORDER
191 FROM
192 WIP_ENTITIES WE
193 WHERE WIP_ENTITY_ID = P_WORK_ORDER_ID_v;
194
195 END IF;
196 /*SRW.MESSAGE('999'
197 ,'Specific Work Order: ' || CP_WORK_ORDER)*/NULL;
198
199 IF (P_ITEM_TYPE_v IS NOT NULL) THEN
200 SELECT
201 MEANING
202 INTO CP_ITEM_TYPE
203 FROM
204 MFG_LOOKUPS
205 WHERE LOOKUP_TYPE = 'CST_IPV_WIP_ITEM_TYPE'
206 AND LOOKUP_CODE = P_ITEM_TYPE_v;
207
208 END IF;
209 /*SRW.MESSAGE('999'
210 ,'IPV Item Type: ' || CP_ITEM_TYPE)*/NULL;
211 IF (P_ITEM_OPTION_v IS NOT NULL) THEN
212 SELECT
213 MEANING
214 INTO CP_ITEM_RANGE
215 FROM
216 MFG_LOOKUPS
217 WHERE LOOKUP_TYPE = 'CST_ITEM_RANGE'
218 AND LOOKUP_CODE = P_ITEM_OPTION_V;
219 END IF;
220 /*SRW.MESSAGE('999'
221 ,'Item Range: ' || CP_ITEM_TYPE)*/NULL;
222 IF (P_ITEM_ID_v IS NOT NULL) THEN
223 SELECT
224 CONCATENATED_SEGMENTS
225 INTO CP_ITEM
226 FROM
227 MTL_SYSTEM_ITEMS_KFV
228 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID1
229 AND INVENTORY_ITEM_ID = P_ITEM_ID_v;
230 END IF;
231 /*SRW.MESSAGE('999'
232 ,'Specific Item: ' || CP_ITEM)*/NULL;
233 IF (P_CATEGORY_SET_ID_v IS NOT NULL) THEN
234 SELECT
235 CATEGORY_SET_NAME
236 INTO CP_CATEGORY_SET
237 FROM
238 MTL_CATEGORY_SETS
239 WHERE CATEGORY_SET_ID = P_CATEGORY_SET_ID_v;
240 END IF;
241 /*SRW.MESSAGE('999'
242 ,'Category Set: ' || CP_CATEGORY_SET)*/NULL;
243 IF (P_CATEGORY_ID_v IS NOT NULL) THEN
244 SELECT
245 CONCATENATED_SEGMENTS
246 INTO CP_CATEGORY
247 FROM
248 MTL_CATEGORIES_KFV
249 WHERE CATEGORY_ID = P_CATEGORY_ID_v;
250 END IF;
251 /*SRW.MESSAGE('999'
252 ,'Specific Category: ' || CP_CATEGORY)*/NULL;
253 IF (P_PROJECT_ID_v IS NOT NULL) THEN
254 SELECT
255 NAME
256 INTO CP_PROJECT
257 FROM
258 PA_PROJECTS_ALL
259 WHERE PROJECT_ID = P_PROJECT_ID_v;
260 END IF;
261 /*SRW.MESSAGE('999'
262 ,'Specific Project: ' || CP_PROJECT)*/NULL;
263 IF (P_ADJUSTMENT_ACCOUNT_v IS NOT NULL) THEN
264 SELECT
265 CONCATENATED_SEGMENTS
266 INTO CP_ADJUSTMENT_ACCOUNT
267 FROM
268 GL_CODE_COMBINATIONS_KFV
269 WHERE CODE_COMBINATION_ID = P_ADJUSTMENT_ACCOUNT_v;
270 END IF;
271 /*SRW.MESSAGE('999'
272 ,'Adjustment Account: ' || CP_ADJUSTMENT_ACCOUNT)*/NULL;
273 /*SRW.MESSAGE('999'
274 ,'Cutoff Date: ' || P_CUTOFF_DATE)*/NULL;
275 END IF;
276
277 EXCEPTION
278 WHEN PROC_ERROR THEN
279 /*SRW.MESSAGE('999'
280 ,'Shop Floor Invoice Variance Transfer Failed : ' || L_ERRBUF)*/NULL;
281
282 ROLLBACK;
283 RETURN (FALSE);
284 WHEN OTHERS THEN
285 /*SRW.MESSAGE('998'
286 ,'Shop Floor Invoice Variance Report Initialization Failed : ' || SQLERRM)*/NULL;
287
288 ROLLBACK;
289 RETURN (FALSE);
290 END;
291 BEGIN
292 IF P_VIEW_COST <> 1 THEN
293 FND_MESSAGE.SET_NAME('null'
294 ,'null');
295 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
296 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
297 END IF;
298 END;
299 BEGIN
300 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
301 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
302 EXCEPTION
303 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
304 /*SRW.MESSAGE(1
305 ,'Failed in SRWINIT')*/NULL;
306 RAISE;
307 END;
308 BEGIN
309 NULL;
310 EXCEPTION
311 WHEN OTHERS THEN
312 /*SRW.MESSAGE(999
313 ,'FND FLEXSQL(GL#) >X')*/NULL;
314 RAISE;
315 END;
316 BEGIN
317 NULL;
318 EXCEPTION
319 WHEN OTHERS THEN
320 /*SRW.MESSAGE(999
321 ,'FND FLEXSQL(MSTK) >X')*/NULL;
322 RAISE;
323 END;
324 RETURN (TRUE);
325 END BEFOREREPORT;
326
327 FUNCTION AFTERREPORT RETURN BOOLEAN IS
328 BEGIN
329 DECLARE
330 L_SHOPFLOOR NUMBER;
331 BEGIN
332 L_SHOPFLOOR := 0;
333 SELECT
334 NVL(ITEM_TYPE
335 ,0)
336 INTO L_SHOPFLOOR
337 FROM
338 CST_AP_VARIANCE_BATCHES
339 WHERE BATCH_ID = LP_BATCH_ID
340 AND ROWNUM = 1;
341 IF (P_TRANSACTION_PROCESS_MODE = 2 AND L_SHOPFLOOR <> 0) THEN
342 NULL;
343 DELETE FROM CST_AP_VARIANCE_LINES CAVL
344 WHERE BATCH_ID = LP_BATCH_ID;
345 DELETE FROM CST_AP_VARIANCE_HEADERS CAVH
346 WHERE BATCH_ID = LP_BATCH_ID;
347 DELETE FROM CST_AP_VARIANCE_BATCHES CAVB
348 WHERE BATCH_ID = LP_BATCH_ID;
349 END IF;
350 END;
351 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
352 RETURN (TRUE);
353 END AFTERREPORT;
354
355 FUNCTION ITEM_PSEGFORMULA(ITEM_SEGMENT IN VARCHAR2
356 ,ITEM_NAME IN VARCHAR2
357 ,ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
358 BEGIN
359 /*SRW.REFERENCE(ITEM_SEGMENT)*/NULL;
360 /*SRW.REFERENCE(ITEM_NAME)*/NULL;
361 RETURN (ITEM_PSEG);
362 END ITEM_PSEGFORMULA;
363
364 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
365 BEGIN
366 RETURN (TRUE);
367 END BETWEENPAGE;
368
369 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
370 BEGIN
371 RETURN (TRUE);
372 END BEFOREPFORM;
373
374 FUNCTION AFTERPFORM RETURN BOOLEAN IS
375 BEGIN
376 RETURN (TRUE);
377 END AFTERPFORM;
378
379 FUNCTION CP_ORG_NAME_P RETURN VARCHAR2 IS
380 BEGIN
381 RETURN CP_ORG_NAME;
382 END CP_ORG_NAME_P;
383
384 FUNCTION CP_ORG_CODE_P RETURN VARCHAR2 IS
385 BEGIN
386 RETURN CP_ORG_CODE;
387 END CP_ORG_CODE_P;
388
389 FUNCTION CP_WORK_ORDER_P RETURN VARCHAR2 IS
390 BEGIN
391 RETURN CP_WORK_ORDER;
392 END CP_WORK_ORDER_P;
393
394 FUNCTION CP_ITEM_TYPE_P RETURN VARCHAR2 IS
395 BEGIN
396 RETURN CP_ITEM_TYPE;
397 END CP_ITEM_TYPE_P;
398
399 FUNCTION CP_ITEM_RANGE_P RETURN VARCHAR2 IS
400 BEGIN
401 RETURN CP_ITEM_RANGE;
402 END CP_ITEM_RANGE_P;
403
404 FUNCTION CP_ITEM_P RETURN VARCHAR2 IS
405 BEGIN
406 RETURN CP_ITEM;
407 END CP_ITEM_P;
408
409 FUNCTION CP_CATEGORY_SET_P RETURN VARCHAR2 IS
410 BEGIN
411 RETURN CP_CATEGORY_SET;
412 END CP_CATEGORY_SET_P;
413
414 FUNCTION CP_CATEGORY_P RETURN VARCHAR2 IS
415 BEGIN
416 RETURN CP_CATEGORY;
417 END CP_CATEGORY_P;
418
419 FUNCTION CP_PROJECT_P RETURN VARCHAR2 IS
420 BEGIN
421 RETURN CP_PROJECT;
422 END CP_PROJECT_P;
423
424 FUNCTION CP_ADJUSTMENT_ACCOUNT_P RETURN VARCHAR2 IS
425 BEGIN
426 RETURN CP_ADJUSTMENT_ACCOUNT;
427 END CP_ADJUSTMENT_ACCOUNT_P;
428
429 END BOM_CSTRIVTW_XMLP_PKG;
430
431