[Home] [Help]
PACKAGE BODY: APPS.GME_GMEMUSBV_XMLP_PKG
Source
1 PACKAGE BODY GME_GMEMUSBV_XMLP_PKG AS
2 /* $Header: GMEMUSBVB.pls 120.1 2007/12/28 11:22:38 nchinnam noship $ */
3 FUNCTION CF_ACCTG_COST(DITEMID IN NUMBER
4 ,ACTUAL_CMPLT_DATE IN DATE) RETURN NUMBER IS
5 BEGIN
6 DECLARE
7 V_COST_BASIS NUMBER(1);
8 V_COST NUMBER;
9 V_GL_COST_MTHD VARCHAR2(4);
10 V_CO_CODE VARCHAR2(4);
11 V_RET_VAL NUMBER;
12 V_COST_MTHD VARCHAR2(8) := NULL;
13 V_CMPNTCLS_ID NUMBER := NULL;
14 V_ANALYSIS_CODE VARCHAR2(8) := NULL;
15 V_RETREIVE_IND NUMBER := NULL;
16 V_COST_CMPNTCLS_ID NUMBER;
17 V_COST_ANALYSIS_CODE VARCHAR2(1) := NULL;
18 V_ACCTG_COST NUMBER;
19 V_STND VARCHAR2(4) := 'STND';
20 COST NUMBER;
21 V_NUM_ROWS NUMBER;
22 L_RETURN_STATUS VARCHAR2(4);
23 L_MSG_COUNT NUMBER;
24 L_MSG_DATA VARCHAR2(2000);
25 BEGIN
26 V_RET_VAL := GMF_CMCOMMON.GET_PROCESS_ITEM_COST(P_API_VERSION => 1.0
27 ,P_INIT_MSG_LIST => 'T'
28 ,P_ORGANIZATION_ID => P_ORG_ID
29 ,P_INVENTORY_ITEM_ID => DITEMID
30 ,P_TRANSACTION_DATE => TRUNC(ACTUAL_CMPLT_DATE)
31 ,P_DETAIL_FLAG => 1
32 ,P_COST_METHOD => V_COST_MTHD
33 ,P_COST_COMPONENT_CLASS_ID => V_COST_CMPNTCLS_ID
34 ,P_COST_ANALYSIS_CODE => V_COST_ANALYSIS_CODE
35 ,X_TOTAL_COST => V_ACCTG_COST
36 ,X_NO_OF_ROWS => V_NUM_ROWS
37 ,X_RETURN_STATUS => L_RETURN_STATUS
38 ,X_MSG_COUNT => L_MSG_COUNT
39 ,X_MSG_DATA => L_MSG_DATA);
40 IF V_RET_VAL = 1 THEN
41 V_COST := V_ACCTG_COST;
42 ELSE
43 V_COST := 0;
44 END IF;
45 CP_ITEM_COST := V_COST;
46 RETURN (V_COST);
47 EXCEPTION
48 WHEN OTHERS THEN
49 RETURN NULL;
50 END;
51 RETURN NULL;
52 END CF_ACCTG_COST;
53
54 FUNCTION CF_ACCTG_COST2FORMULA(TRANS_QTY IN NUMBER) RETURN NUMBER IS
55 COST NUMBER;
56 BEGIN
57 COST := TRANS_QTY * CP_ITEM_COST;
58 RETURN (COST);
59 END CF_ACCTG_COST2FORMULA;
60
61 FUNCTION CF_QUANTITY_VARIANCE(CF_ACTL_QTY_ITEMUM IN NUMBER
62 ,TRANS_QTY IN NUMBER) RETURN NUMBER IS
63 BEGIN
64 RETURN (nvl(CF_ACTL_QTY_ITEMUM, 0) - nvl(TRANS_QTY, 0));
65 END CF_QUANTITY_VARIANCE;
66
67 FUNCTION CF_VALUE_VARIANCE(BATCHUM IN VARCHAR2
68 ,ITEM_UM IN VARCHAR2
69 ,DITEMID IN NUMBER
70 ,CF_QUANTITY_VARIANCE IN NUMBER
71 ,CF_ACCTG_COST IN NUMBER) RETURN NUMBER IS
72 V_QTY_VAR NUMBER;
73 MESSAGE_IND NUMBER;
74 MESSAGE_TEXT VARCHAR2(2000);
75 BEGIN
76 IF (BATCHUM <> ITEM_UM) THEN
77 V_QTY_VAR := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => DITEMID
78 ,PRECISION => 5
79 ,FROM_QUANTITY => NVL(CF_QUANTITY_VARIANCE
80 ,0)
81 ,FROM_UNIT => BATCHUM
82 ,TO_UNIT => ITEM_UM
83 ,FROM_NAME => NULL
84 ,TO_NAME => NULL);
85 ELSE
86 V_QTY_VAR := NVL(CF_QUANTITY_VARIANCE
87 ,0);
88 END IF;
89 RETURN (CF_ACCTG_COST * V_QTY_VAR);
90 EXCEPTION
91 WHEN OTHERS THEN
92 GME_COMMON_PVT.COUNT_AND_GET(P_ENCODED => 'F'
93 ,X_DATA => MESSAGE_TEXT
94 ,X_COUNT => MESSAGE_IND);
95 /*SRW.MESSAGE(100
96 ,'Error in UOM Conversion ' || MESSAGE_TEXT)*/NULL;
97 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
98 END CF_VALUE_VARIANCE;
99
100 FUNCTION CF_VARIANCE_PCT(TRANS_QTY IN NUMBER
101 ,CF_QUANTITY_VARIANCE IN NUMBER) RETURN NUMBER IS
102 TEMPFIELD NUMBER;
103 BEGIN
104 IF ((TRANS_QTY = 0) OR TRANS_QTY IS NULL) THEN
105 IF CF_QUANTITY_VARIANCE = 0 THEN
106 TEMPFIELD := 0;
107 ELSE
108 TEMPFIELD := NULL;
109 END IF;
110 ELSE
111 TEMPFIELD := (CF_QUANTITY_VARIANCE / TRANS_QTY) * 100;
112 END IF;
113 RETURN (TEMPFIELD);
114 END CF_VARIANCE_PCT;
115
116 FUNCTION CF_BATCHRANGEFORMULA RETURN VARCHAR2 IS
117 BEGIN
118 /*SRW.REFERENCE(CP_BATCHRANGE)*/NULL;
119 IF FROMBATCH IS NOT NULL AND TOBATCH IS NOT NULL AND LPAD(FROMBATCH
120 ,32
121 ,'0') = LPAD(TOBATCH
122 ,32
123 ,'0') THEN
124 CP_BATCHRANGE := ' and Lpad(h.batch_no,32,''0'') = ' || '''' || LPAD(FROMBATCH
125 ,32
126 ,'0') || '''';
127 ELSIF FROMBATCH IS NOT NULL AND TOBATCH IS NOT NULL THEN
128 CP_BATCHRANGE := ' and Lpad(h.batch_no,32,''0'') between ' || '''' || LPAD(FROMBATCH
129 ,32
130 ,'0') || '''' || ' and ' || '''' || LPAD(TOBATCH
131 ,32
132 ,'0') || '''';
133 ELSIF FROMBATCH IS NULL AND TOBATCH IS NULL THEN
134 CP_BATCHRANGE := ' ';
135 ELSIF FROMBATCH IS NOT NULL AND TOBATCH IS NULL THEN
136 CP_BATCHRANGE := 'and Lpad(h.batch_no,32,''0'') >= ' || '''' || LPAD(FROMBATCH
137 ,32
138 ,'0') || '''';
139 ELSIF FROMBATCH IS NULL AND TOBATCH IS NOT NULL THEN
140 CP_BATCHRANGE := 'and Lpad(h.batch_no,32,''0'') <= ' || '''' || LPAD(TOBATCH
141 ,32
142 ,'0') || '''';
143 END IF;
144 RETURN NULL;
145 END CF_BATCHRANGEFORMULA;
146
147 FUNCTION CF_DATERANGEFORMULA RETURN VARCHAR2 IS
148 BEGIN
149 /*SRW.REFERENCE(CP_DATERANGE)*/NULL;
150 RETURN NULL;
151 END CF_DATERANGEFORMULA;
152
153 FUNCTION CF_ITEM_RANGE RETURN VARCHAR2 IS
154 BEGIN
155 /*SRW.REFERENCE(CP_ITEMRANGE)*/NULL;
156 IF FROMITEM IS NOT NULL AND TOITEM IS NOT NULL AND FROMITEM = TOITEM THEN
157 CP_ITEMRANGE := ' and im.concatenated_segments = ' || '''' || FROMITEM || '''';
158 ELSIF FROMITEM IS NOT NULL AND TOITEM IS NOT NULL THEN
159 CP_ITEMRANGE := ' and im.concatenated_segments between ' || '''' || FROMITEM || ''' and ' || '''' || TOITEM || '''';
160 ELSIF FROMITEM IS NULL AND TOITEM IS NULL THEN
161 CP_ITEMRANGE := ' ';
162 ELSIF FROMITEM IS NOT NULL AND TOITEM IS NULL THEN
163 CP_ITEMRANGE := 'and im.concatenated_segments >= ' || '''' || FROMITEM || '''';
164 ELSIF FROMITEM IS NULL AND TOITEM IS NOT NULL THEN
165 CP_ITEMRANGE := 'and im.concatenated_segments <= ' || '''' || TOITEM || '''';
166 END IF;
167 RETURN NULL;
168 END CF_ITEM_RANGE;
169
170 FUNCTION CF_WIPCODE RETURN VARCHAR2 IS
171 WHSECODE VARCHAR2(50);
172 BEGIN
173 WHSECODE := NULL;
174 RETURN (WHSECODE);
175 RETURN NULL;
176 EXCEPTION
177 WHEN OTHERS THEN
178 RETURN (NULL);
179 END CF_WIPCODE;
180
181 FUNCTION G_MAINGROUPFILTER RETURN BOOLEAN IS
182 BEGIN
183 RETURN (TRUE);
184 END G_MAINGROUPFILTER;
185
186 FUNCTION CF_ACTL_QTY_ITEMUMFORMULA(BATCHUM IN VARCHAR2
187 ,FORMUM IN VARCHAR2
188 ,AQTY IN NUMBER
189 ,DITEMID IN NUMBER) RETURN NUMBER IS
190 V_PLAN_QTY_ITEMUM NUMBER;
191 X_RET NUMBER(5);
192 BEGIN
193 IF (BATCHUM <> FORMUM OR NVL(AQTY
194 ,0) <> 0) THEN
195 V_PLAN_QTY_ITEMUM := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => DITEMID
196 ,PRECISION => 5
197 ,FROM_QUANTITY => NVL(AQTY
198 ,0)
199 ,FROM_UNIT => BATCHUM
200 ,TO_UNIT => FORMUM
201 ,FROM_NAME => NULL
202 ,TO_NAME => NULL);
203 RETURN V_PLAN_QTY_ITEMUM;
204 ELSE
205 RETURN NVL(AQTY
206 ,0);
207 END IF;
208 RETURN NULL;
209 END CF_ACTL_QTY_ITEMUMFORMULA;
210
211 FUNCTION CF_BATCH_SIZEFORMULA(RECIPE_VALIDITY_RULE_ID IN NUMBER
212 ,BATCH_ID IN NUMBER) RETURN NUMBER IS
213 PLANQTY NUMBER(10,2);
214 BEGIN
215 IF RECIPE_VALIDITY_RULE_ID IS NOT NULL THEN
216 SELECT
217 PLAN_QTY
218 INTO PLANQTY
219 FROM
220 GME_MATERIAL_DETAILS
221 WHERE BATCH_ID = CF_BATCH_SIZEFORMULA.BATCH_ID
222 AND INVENTORY_ITEM_ID = (
223 SELECT
224 INVENTORY_ITEM_ID
225 FROM
226 GMD_RECIPE_VALIDITY_RULES
227 WHERE RECIPE_VALIDITY_RULE_ID = CF_BATCH_SIZEFORMULA.RECIPE_VALIDITY_RULE_ID );
228 ELSE
229 SELECT
230 PLAN_QTY
231 INTO PLANQTY
232 FROM
233 GME_MATERIAL_DETAILS
234 WHERE BATCH_ID = CF_BATCH_SIZEFORMULA.BATCH_ID
235 AND LINE_TYPE = 1
236 AND LINE_NO = 1;
237 END IF;
238 RETURN (PLANQTY);
239 RETURN NULL;
240 EXCEPTION
241 WHEN OTHERS THEN
242 RETURN (0);
243 END CF_BATCH_SIZEFORMULA;
244
245 FUNCTION CF_ITEMUMFORMULA(RECIPE_VALIDITY_RULE_ID IN NUMBER
246 ,BATCH_ID IN NUMBER) RETURN VARCHAR2 IS
247 ITEMUM VARCHAR2(4);
248 BEGIN
249 IF RECIPE_VALIDITY_RULE_ID IS NOT NULL THEN
250 SELECT
251 DTL_UM
252 INTO ITEMUM
253 FROM
254 GME_MATERIAL_DETAILS
255 WHERE BATCH_ID = cf_itemumformula.BATCH_ID
256 AND INVENTORY_ITEM_ID = (
257 SELECT
258 INVENTORY_ITEM_ID
259 FROM
260 GMD_RECIPE_VALIDITY_RULES
261 WHERE RECIPE_VALIDITY_RULE_ID = cf_itemumformula.RECIPE_VALIDITY_RULE_ID );
262 ELSE
263 SELECT
264 DTL_UM
265 INTO ITEMUM
266 FROM
267 GME_MATERIAL_DETAILS
268 WHERE BATCH_ID = cf_itemumformula.BATCH_ID
269 AND LINE_TYPE = 1
270 AND LINE_NO = 1;
271 END IF;
272 RETURN (ITEMUM);
273 RETURN NULL;
274 EXCEPTION
275 WHEN OTHERS THEN
276 RETURN (NULL);
277 END CF_ITEMUMFORMULA;
278
279 FUNCTION TRANS_QTYFORMULA_005(FORMULALINE_ID IN NUMBER
280 ,RECIPE_VALIDITY_RULE_ID IN NUMBER
281 ,FORMULA_ID IN NUMBER
282 ,BATCH_ID IN NUMBER) RETURN NUMBER IS
283 X_ITEM_ID NUMBER(10);
284 P_SCALE_TAB GMD_COMMON_SCALE.SCALE_TAB;
285 X_SCALE_TAB GMD_COMMON_SCALE.SCALE_TAB;
286 X_RETURN_STATUS VARCHAR2(4);
287 errcode1 number;
288 errmsg1 varchar2(100);
289
290
291 CURSOR CUR_FORMULA_EXISTS IS
292 SELECT
293 COUNT(1)
294 FROM
295 FND_DUAL
296 WHERE EXISTS (
297 SELECT
298 FORMULALINE_ID
299 FROM
300 GME_SCALE_DETAIL
301 WHERE FORMULALINE_ID = trans_qtyformula_005.FORMULALINE_ID );
302 CURSOR CUR_MATL_QTY IS
303 SELECT
304 QTY
305 FROM
306 GME_SCALE_DETAIL
307 WHERE FORMULALINE_ID = trans_qtyformula_005.FORMULALINE_ID;
308 CURSOR CUR_PRIMARY_PROD IS
309 SELECT
310 INVENTORY_ITEM_ID
311 FROM
312 GMD_RECIPE_VALIDITY_RULES
313 WHERE RECIPE_VALIDITY_RULE_ID = trans_qtyformula_005.RECIPE_VALIDITY_RULE_ID;
314 CURSOR CUR_ITEM_UM(V_ITEM_ID IN NUMBER) IS
315 SELECT
316 PRIMARY_UOM_CODE
317 FROM
318 MTL_SYSTEM_ITEMS_B
319 WHERE ORGANIZATION_ID = P_ORG_ID
320 AND INVENTORY_ITEM_ID = V_ITEM_ID;
321 CURSOR CUR_FORM_DTL IS
322 SELECT
323 QTY,
324 DETAIL_UOM
325 FROM
326 FM_MATL_DTL
327 WHERE FORMULA_ID = trans_qtyformula_005.FORMULA_ID
328 AND ORGANIZATION_ID = P_ORG_ID
329 AND INVENTORY_ITEM_ID = X_ITEM_ID
330 AND LINE_TYPE = 1;
331 CURSOR CUR_BATCH_DTL IS
332 SELECT
333 PLAN_QTY,
334 DTL_UM
335 FROM
336 GME_MATERIAL_DETAILS
337 WHERE BATCH_ID = trans_qtyformula_005.BATCH_ID
338 AND INVENTORY_ITEM_ID = X_ITEM_ID
339 AND LINE_TYPE = 1;
340 CURSOR CUR_PLAN_QTY IS
341 SELECT
342 QTY
343 FROM
344 GME_SCALE_DETAIL
345 WHERE FORMULALINE_ID = trans_qtyformula_005.FORMULALINE_ID;
346 CURSOR CUR_SCRAP_FACTOR IS
347 SELECT
348 SCRAP_FACTOR
349 FROM
350 FM_MATL_DTL
351 WHERE FORMULALINE_ID = trans_qtyformula_005.FORMULALINE_ID
352 AND FORMULA_ID = trans_qtyformula_005.FORMULA_ID
353 AND LINE_TYPE = - 1;
354 CURSOR CUR_GET_INFO(PBATCH_ID IN NUMBER) IS
355 SELECT
356 A.RECIPE_ID,
357 B.ROUTING_ID,
358 B.FORMULA_ID,
359 B.PLAN_START_DATE
360 FROM
361 GME_BATCH_HEADER B,
362 GMD_RECIPE_VALIDITY_RULES A
363 WHERE B.BATCH_ID = PBATCH_ID
364 AND B.RECIPE_VALIDITY_RULE_ID = A.RECIPE_VALIDITY_RULE_ID;
365 CURSOR CUR_GET_RT_UOM(P_ROUTING_ID IN NUMBER) IS
366 SELECT
367 ITEM_UM
368 FROM
369 GMD_ROUTINGS_B
370 WHERE ROUTING_ID = P_ROUTING_ID;
371 CURSOR CUR_GET_RTCLASS(PROUTING_ID IN NUMBER) IS
372 SELECT
373 ROUTING_CLASS,
374 ITEM_UM
375 FROM
376 GMD_ROUTINGS_B
377 WHERE ROUTING_ID = PROUTING_ID;
378 CURSOR CUR_MTL_DETAILS IS
379 SELECT
380 *
381 FROM
382 GME_SCALE_DETAIL
383 WHERE LINE_TYPE in ( 1 , 2 );
384 CURSOR CUR_MTL_DTLS IS
385 SELECT
386 *
387 FROM
388 FM_MATL_DTL
389 WHERE FORMULA_ID = trans_qtyformula_005.FORMULA_ID
390 AND LINE_TYPE in ( 1 , 2 );
391 L_RECIPE_ID NUMBER;
392 L_ORGN_CODE VARCHAR2(4);
393 L_FORMULA_ID NUMBER;
394 L_ROUTING_ID NUMBER;
395 L_ROUTING_CLASS FM_ROUT_HDR.ROUTING_CLASS%TYPE;
396 L_ROUTING_UOM VARCHAR2(3);
397 L_TOTAL_OUTPUT_QTY_C NUMBER := 0;
398 L_TOTAL_OUTPUT_QTY_B NUMBER := 0;
399 L_TEMP_QTY NUMBER;
400 L_COUNT NUMBER;
401 L_PLAN_START_DATE DATE;
402 L_FORMULA_TBL GMDFMVAL_PUB.FORMULA_DETAIL_TBL;
403 X_TRANS_QTY NUMBER := 0;
404 X_COUNT NUMBER(5);
405 X_ITEM_UM VARCHAR2(4);
406 X_QTY NUMBER;
407 X_DTL_UM VARCHAR2(4);
408 X_CONV_QTY_FORM NUMBER;
409 X_CONV_QTY_BATCH NUMBER;
410 X_SCALE_FACTOR NUMBER;
411 X_SCRAP_FACTOR NUMBER;
412 X_RVAR NUMBER(5);
413 I NUMBER;
414 L_PROCESS_LOSS NUMBER;
415 L_NUMBER_OF_FORMULA_LINES NUMBER;
416 MESSAGE_TEXT VARCHAR2(200);
417 MESSAGE_IND NUMBER;
418 PRAGMA AUTONOMOUS_TRANSACTION;
419 BEGIN
420 IF FORMULALINE_ID > 0 THEN
421 OPEN CUR_FORMULA_EXISTS;
422 FETCH CUR_FORMULA_EXISTS
423 INTO X_COUNT;
424 CLOSE CUR_FORMULA_EXISTS;
425 OPEN CUR_GET_INFO(BATCH_ID);
426 FETCH CUR_GET_INFO
427 INTO L_RECIPE_ID,L_ROUTING_ID,L_FORMULA_ID,L_PLAN_START_DATE;
428 CLOSE CUR_GET_INFO;
429 OPEN CUR_GET_RT_UOM(L_ROUTING_ID);
430 FETCH CUR_GET_RT_UOM
431 INTO L_ROUTING_UOM;
432 CLOSE CUR_GET_RT_UOM;
433 OPEN CUR_PRIMARY_PROD;
434 FETCH CUR_PRIMARY_PROD
435 INTO X_ITEM_ID;
436 CLOSE CUR_PRIMARY_PROD;
437 OPEN CUR_ITEM_UM(X_ITEM_ID);
438 FETCH CUR_ITEM_UM
439 INTO X_ITEM_UM;
440 CLOSE CUR_ITEM_UM;
441 OPEN CUR_FORM_DTL;
442 FETCH CUR_FORM_DTL
443 INTO X_QTY,X_DTL_UM;
444 CLOSE CUR_FORM_DTL;
445 IF X_DTL_UM <> X_ITEM_UM THEN
446 X_CONV_QTY_FORM := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => X_ITEM_ID
447 ,PRECISION => 5
448 ,FROM_QUANTITY => X_QTY
449 ,FROM_UNIT => X_DTL_UM
450 ,TO_UNIT => X_ITEM_UM
451 ,FROM_NAME => NULL
452 ,TO_NAME => NULL);
453 ELSE
454 X_CONV_QTY_FORM := X_QTY;
455 END IF;
456 OPEN CUR_BATCH_DTL;
457 FETCH CUR_BATCH_DTL
458 INTO X_QTY,X_DTL_UM;
459 CLOSE CUR_BATCH_DTL;
460 IF X_DTL_UM <> X_ITEM_UM THEN
461 X_CONV_QTY_BATCH := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => X_ITEM_ID
462 ,PRECISION => 5
463 ,FROM_QUANTITY => X_QTY
464 ,FROM_UNIT => X_DTL_UM
465 ,TO_UNIT => X_ITEM_UM
466 ,FROM_NAME => NULL
467 ,TO_NAME => NULL);
468 ELSE
469 X_CONV_QTY_BATCH := X_QTY;
470 END IF;
471 IF X_CONV_QTY_FORM > 0 THEN
472 X_SCALE_FACTOR := X_CONV_QTY_BATCH / X_CONV_QTY_FORM;
473 ELSE
474 X_SCALE_FACTOR := 0;
475 END IF;
476 IF L_ROUTING_ID IS NOT NULL THEN
477 L_TOTAL_OUTPUT_QTY_C := 0;
478 FOR l_material_details IN CUR_MTL_DTLS LOOP
479 L_TEMP_QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => L_MATERIAL_DETAILS.ITEM_ID
480 ,PRECISION => 5
481 ,FROM_QUANTITY => L_MATERIAL_DETAILS.QTY
482 ,FROM_UNIT => L_MATERIAL_DETAILS.ITEM_UM
483 ,TO_UNIT => L_ROUTING_UOM
484 ,FROM_NAME => NULL
485 ,TO_NAME => NULL);
486 IF L_TEMP_QTY < 0 THEN
487 L_TEMP_QTY := 0;
488 ELSE
489 L_TOTAL_OUTPUT_QTY_C := L_TOTAL_OUTPUT_QTY_C + L_TEMP_QTY;
490 END IF;
491 END LOOP;
492 END IF;
493 GMDFMVAL_PUB.GET_SUBSTITUTE_ITEMS(PFORMULA_ID => L_FORMULA_ID
494 ,PDATE => L_PLAN_START_DATE
495 ,XFORMULADETAIL_TBL => L_FORMULA_TBL);
496 FOR i IN 1 .. L_FORMULA_TBL.COUNT LOOP
497 P_SCALE_TAB(I).LINE_NO := L_FORMULA_TBL(I).FORMULALINE_ID;
498 P_SCALE_TAB(I).LINE_TYPE := L_FORMULA_TBL(I).LINE_TYPE;
499 P_SCALE_TAB(I).INVENTORY_ITEM_ID := L_FORMULA_TBL(I).INVENTORY_ITEM_ID;
500 P_SCALE_TAB(I).QTY := NVL(L_FORMULA_TBL(I).QTY
501 ,0);
502 P_SCALE_TAB(I).DETAIL_UOM := L_FORMULA_TBL(I).DETAIL_UOM;
503 P_SCALE_TAB(I).SCALE_TYPE := L_FORMULA_TBL(I).SCALE_TYPE;
504 P_SCALE_TAB(I).SCALE_MULTIPLE := L_FORMULA_TBL(I).SCALE_MULTIPLE;
505 P_SCALE_TAB(I).SCALE_ROUNDING_VARIANCE := L_FORMULA_TBL(I).SCALE_ROUNDING_VARIANCE;
506 P_SCALE_TAB(I).ROUNDING_DIRECTION := L_FORMULA_TBL(I).ROUNDING_DIRECTION;
507 P_SCALE_TAB(I).CONTRIBUTE_YIELD_IND := L_FORMULA_TBL(I).CONTRIBUTE_YIELD_IND;
508 END LOOP;
509 GMD_COMMON_SCALE.SCALE(P_SCALE_TAB => P_SCALE_TAB
510 ,P_ORGN_ID => P_ORG_ID
511 ,P_SCALE_FACTOR => X_SCALE_FACTOR
512 ,P_PRIMARIES => 'OUTPUTS'
513 ,X_SCALE_TAB => X_SCALE_TAB
514 ,X_RETURN_STATUS => X_RETURN_STATUS);
515 IF L_ROUTING_ID IS NOT NULL THEN
516 L_TOTAL_OUTPUT_QTY_B := 0;
517 L_COUNT := X_SCALE_TAB.COUNT;
518 FOR i IN 1 .. L_COUNT LOOP
519 IF X_SCALE_TAB(I).LINE_TYPE in (1,2) THEN
520 L_TEMP_QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => X_SCALE_TAB(I).INVENTORY_ITEM_ID
521 ,PRECISION => 5
522 ,FROM_QUANTITY => X_SCALE_TAB(I).QTY
523 ,FROM_UNIT => X_SCALE_TAB(I).DETAIL_UOM
524 ,TO_UNIT => L_ROUTING_UOM
525 ,FROM_NAME => NULL
526 ,TO_NAME => NULL);
527 IF L_TEMP_QTY < 0 THEN
528 L_TEMP_QTY := 0;
529 ELSE
530 L_TOTAL_OUTPUT_QTY_B := L_TOTAL_OUTPUT_QTY_B + L_TEMP_QTY;
531 END IF;
532 END IF;
533 END LOOP;
534 L_PROCESS_LOSS := GME_COMMON_PVT.GET_PROCESS_LOSS(P_BATCH_ID => trans_qtyformula_005.BATCH_ID
535 ,P_VALIDITY_RULE_ID => trans_qtyformula_005.RECIPE_VALIDITY_RULE_ID
536 ,P_ORGANIZATION_ID => P_ORG_ID
537 ,P_TOTAL_OUTPUT_QTY_SCALED => L_TOTAL_OUTPUT_QTY_B
538 ,P_TOTAL_OUTPUT_QTY_PRE_SCALE => L_TOTAL_OUTPUT_QTY_C);
539 IF (L_PROCESS_LOSS IS NULL) THEN
540 GME_COMMON_PVT.COUNT_AND_GET(P_ENCODED => 'F'
541 ,X_DATA => MESSAGE_TEXT
542 ,X_COUNT => MESSAGE_IND);
543 /*SRW.MESSAGE(100
544 ,'Error in the get_process_loss ' || MESSAGE_TEXT)*/NULL;
545 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
546 END IF;
547 L_NUMBER_OF_FORMULA_LINES := X_SCALE_TAB.COUNT;
548 FOR l_row_count IN 1 .. L_NUMBER_OF_FORMULA_LINES LOOP
549 IF X_SCALE_TAB(L_ROW_COUNT).LINE_TYPE < 0 THEN
550 IF X_SCALE_TAB(L_ROW_COUNT).SCALE_TYPE = 1 THEN
551 X_SCALE_TAB(L_ROW_COUNT).QTY := X_SCALE_TAB(L_ROW_COUNT).QTY * 100 / (100 - L_PROCESS_LOSS);
552 END IF;
553 END IF;
554 END LOOP;
555 END IF;
556 IF X_RETURN_STATUS = 'S' THEN
557 FOR i IN 1 .. X_SCALE_TAB.COUNT LOOP
558
559 INSERT INTO GME_SCALE_DETAIL ( FORMULALINE_ID,LINE_TYPE,ITEM_ID,QTY,ITEM_UM,SCALE_TYPE,CONV_QTY)
560 VALUES (X_SCALE_TAB(I).LINE_NO
561 ,X_SCALE_TAB(I).LINE_TYPE
562 ,X_SCALE_TAB(I).INVENTORY_ITEM_ID
563 ,X_SCALE_TAB(I).QTY
564 ,X_SCALE_TAB(I).DETAIL_UOM
565 ,X_SCALE_TAB(I).SCALE_TYPE
566 ,X_SCALE_TAB(I).QTY);
567 END LOOP;
568 commit;
569 END IF;
570 OPEN CUR_PLAN_QTY;
571 FETCH CUR_PLAN_QTY
572 INTO X_TRANS_QTY;
573 CLOSE CUR_PLAN_QTY;
574 OPEN CUR_SCRAP_FACTOR;
575 FETCH CUR_SCRAP_FACTOR
576 INTO X_SCRAP_FACTOR;
577 CLOSE CUR_SCRAP_FACTOR;
578 IF X_SCRAP_FACTOR > 0 THEN
579 X_TRANS_QTY := X_TRANS_QTY * (1 + X_SCRAP_FACTOR);
580 END IF;
581 ELSE
582 RETURN (0);
583 END IF;
584 RETURN (X_TRANS_QTY);
585 EXCEPTION
586 WHEN OTHERS THEN
587
588 /*SRW.MESSAGE(100
589 ,'DB error in process loss ' || SQLERRM)*/NULL;
590
591 errcode1 := sqlcode;
592 errmsg1 := sqlerrm;
593
594 RETURN (0);
595 END TRANS_QTYFORMULA_005;
596
597 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
598 CO_CODE VARCHAR2(4);
599 BEGIN
600 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
601 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
602 DELETE FROM GME_SCALE_DETAIL;
603 FND_MSG_PUB.INITIALIZE;
604 RETURN (TRUE);
605 RETURN NULL;
606 EXCEPTION
607 WHEN OTHERS THEN
608 RETURN (TRUE);
609 END BEFOREREPORT;
610
611 FUNCTION CF_U_VALUE_VARIANCEFORMULA(CF_ACTL_QTY_ITEMUM IN NUMBER
612 ,TRANS_QTY IN NUMBER
613 ,CF_VALUE_VARIANCE IN NUMBER) RETURN NUMBER IS
614 BEGIN
615 IF (CF_ACTL_QTY_ITEMUM > 0) AND (TRANS_QTY > 0) THEN
616 RETURN (CF_VALUE_VARIANCE);
617 ELSE
618 RETURN (0);
619 END IF;
620 RETURN NULL;
621 END CF_U_VALUE_VARIANCEFORMULA;
622
623 FUNCTION CF_S_VALUE_VARIANCEFORMULA(CF_ACTL_QTY_ITEMUM IN NUMBER
624 ,TRANS_QTY IN NUMBER
625 ,CF_VALUE_VARIANCE IN NUMBER) RETURN NUMBER IS
626 BEGIN
627 IF (CF_ACTL_QTY_ITEMUM = 0) OR (TRANS_QTY = 0) THEN
628 RETURN (CF_VALUE_VARIANCE);
629 ELSE
630 RETURN (0);
631 END IF;
632 RETURN NULL;
633 END CF_S_VALUE_VARIANCEFORMULA;
634
635 FUNCTION CF_TOTAL_VARIANCEFORMULA(CS_TOT_U_VALUE_VARIANCE IN NUMBER
636 ,CS_TOT_S_VALUE_VARIANCE IN NUMBER) RETURN NUMBER IS
637 BEGIN
638 RETURN (CS_TOT_U_VALUE_VARIANCE + CS_TOT_S_VALUE_VARIANCE);
639 END CF_TOTAL_VARIANCEFORMULA;
640
641 FUNCTION AFTERREPORT RETURN BOOLEAN IS
642 BEGIN
643 DELETE FROM GME_SCALE_DETAIL;
644 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
645 RETURN (TRUE);
646 END AFTERREPORT;
647
648 FUNCTION CF_DELETE_TRANSFORMULA RETURN NUMBER IS
649 PRAGMA AUTONOMOUS_TRANSACTION;
650 BEGIN
651 DELETE FROM GME_SCALE_DETAIL;
652 commit;
653 RETURN (1);
654 END CF_DELETE_TRANSFORMULA;
655
656 PROCEDURE HEADER IS
657 BEGIN
658 NULL;
659 END HEADER;
660
661 FUNCTION CF_UOMFORMULA(FORMUM IN VARCHAR2
662 ,BATCHUM IN VARCHAR2) RETURN CHAR IS
663 BEGIN
664 IF FORMUM = ' ' THEN
665 RETURN BATCHUM;
666 ELSE
667 RETURN FORMUM;
668 END IF;
669 END CF_UOMFORMULA;
670
671 FUNCTION CF_CONTEXT_ORGFORMULA RETURN CHAR IS
672 CURSOR C_GET_ORG IS
673 SELECT
674 ORGANIZATION_CODE
675 FROM
676 MTL_PARAMETERS
677 WHERE ORGANIZATION_ID = P_ORG_ID;
678 L_ORG VARCHAR2(6);
679 BEGIN
680 OPEN C_GET_ORG;
681 FETCH C_GET_ORG
682 INTO L_ORG;
683 CLOSE C_GET_ORG;
684 L_ORG := '(' || L_ORG || ')';
685 RETURN L_ORG;
686 END CF_CONTEXT_ORGFORMULA;
687
688 FUNCTION CP_ITEM_COST_P RETURN NUMBER IS
689 BEGIN
690 RETURN CP_ITEM_COST;
691 END CP_ITEM_COST_P;
692
693 FUNCTION CP_BATCHRANGE_P RETURN VARCHAR2 IS
694 BEGIN
695 RETURN CP_BATCHRANGE;
696 END CP_BATCHRANGE_P;
697
698 FUNCTION CP_DATERANGE_P RETURN VARCHAR2 IS
699 BEGIN
700 RETURN CP_DATERANGE;
701 END CP_DATERANGE_P;
702
703 FUNCTION CP_ITEMRANGE_P RETURN VARCHAR2 IS
704 BEGIN
705 RETURN CP_ITEMRANGE;
706 END CP_ITEMRANGE_P;
707
708 END GME_GMEMUSBV_XMLP_PKG;
709
710