1 PACKAGE BODY GMD_ICR01USR_XMLP_PKG AS
2 /* $Header: ICR01USRB.pls 120.0 2007/12/24 13:04:31 krreddy noship $ */
3 /* FUNCTION INDENT1FORMULA RETURN NUMBER IS
4 INDENTCF NUMBER(20) := 2;
5 BEGIN
6 INDENTCF := INDENTCF + 2;
7 RETURN (LPAD(' '
8 ,INDENTCF));
9 END INDENT1FORMULA;*/
10
11 FUNCTION LINE_NOCFFORMULA(LINE_TYPE IN NUMBER
12 ,ITEM_ID IN NUMBER
13 ,FORMULA_ID IN NUMBER) RETURN NUMBER IS
14 P_LINE_NO NUMBER := 0;
15 BEGIN
16 IF LINE_TYPE = 1 THEN
17 BEGIN
18 SELECT
19 LINE_NO
20 INTO P_LINE_NO
21 FROM
22 FM_MATL_DTL
23 WHERE INVENTORY_ITEM_ID = LINE_NOCFFORMULA.ITEM_ID
24 AND LINE_TYPE = 1
25 AND FORMULA_ID = LINE_NOCFFORMULA.FORMULA_ID;
26 EXCEPTION
27 WHEN TOO_MANY_ROWS THEN
28 P_LINE_NO := 0;
29 END;
30 ELSE
31 P_LINE_NO := 0;
32 END IF;
33 RETURN (P_LINE_NO);
34 END LINE_NOCFFORMULA;
35
36 FUNCTION LEVEL_NOCFFORMULA(LINE_NOCF IN NUMBER
37 ,LEVEL_NUM IN NUMBER) RETURN NUMBER IS
38 BEGIN
39 IF LINE_NOCF = 1 THEN
40 RETURN (0);
41 END IF;
42 IF LEVEL_NUM > NVL(P_LEVEL
43 ,0) THEN
44 P_LEVEL := LEVEL_NUM;
45 RETURN (1);
46 ELSE
47 P_LEVEL := LEVEL_NUM;
48 RETURN (0);
49 END IF;
50 -- RETURN NULL;
51 END LEVEL_NOCFFORMULA;
52
53 FUNCTION BATCH_QTYCFFORMULA(EXPLODE_SEQ IN VARCHAR2) RETURN NUMBER IS
54 BATCHQTY NUMBER;
55 BEGIN
56 SELECT
57 SCALE_QTY
58 INTO BATCHQTY
59 FROM
60 FM_IBOM_DTL
61 WHERE TOP_FORMULA_ID = P_FORMULA_ID
62 AND EXPLODE_SEQ = SUBSTR(BATCH_QTYCFFORMULA.EXPLODE_SEQ
63 ,1
64 ,LENGTH(BATCH_QTYCFFORMULA.EXPLODE_SEQ) - 3) || '001';
65 RETURN (BATCHQTY);
66 RETURN NULL;
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 RETURN (NULL);
70 END BATCH_QTYCFFORMULA;
71
72 FUNCTION FORM_QTYCFFORMULA(EXPLODE_SEQ IN VARCHAR2) RETURN NUMBER IS
73 FORMQTY NUMBER;
74 BEGIN
75 SELECT
76 FORMULA_QTY
77 INTO FORMQTY
78 FROM
79 FM_IBOM_DTL
80 WHERE TOP_FORMULA_ID = P_FORMULA_ID
81 AND EXPLODE_SEQ = SUBSTR(FORM_QTYCFFORMULA.EXPLODE_SEQ
82 ,1
83 ,LENGTH(FORM_QTYCFFORMULA.EXPLODE_SEQ) - 3) || '001';
84 RETURN (FORMQTY);
85 RETURN NULL;
86 EXCEPTION
87 WHEN NO_DATA_FOUND THEN
88 RETURN (NULL);
89 END FORM_QTYCFFORMULA;
90
91 FUNCTION STANDARD_QTYFORMULA(FORMULA_ID IN NUMBER
92 ,LEVEL_NUM IN NUMBER) RETURN NUMBER IS
93 STANDARDQTY NUMBER;
94 BEGIN
95 SELECT
96 DISTINCT
97 STD_QTY
98 INTO STANDARDQTY
99 FROM
100 FM_IBOM_DTL
101 WHERE LINE_TYPE = 1
102 AND TOP_FORMULA_ID = P_FORMULA_ID
103 AND FORMULA_ID = STANDARD_QTYFORMULA.FORMULA_ID
104 AND LEVEL_NUM = STANDARD_QTYFORMULA.LEVEL_NUM
105 AND FM_IBOM_DTL.INVENTORY_ITEM_ID = (
106 SELECT
107 FM_MATL_DTL.INVENTORY_ITEM_ID
108 FROM
109 FM_MATL_DTL
110 WHERE FORMULA_ID = STANDARD_QTYFORMULA.FORMULA_ID
111 AND LINE_TYPE = 1
112 AND LINE_NO = 1 )
113 AND LINE_NO = 1;
114 RETURN (STANDARDQTY);
115 RETURN NULL;
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 RETURN (NULL);
119 END STANDARD_QTYFORMULA;
120
121 FUNCTION UOMCFFORMULA(EXPLODE_SEQ IN VARCHAR2) RETURN VARCHAR2 IS
122 UOM1 VARCHAR2(4);
123 BEGIN
124 SELECT
125 I_QTY_UOM
126 INTO UOM1
127 FROM
128 FM_IBOM_DTL
129 WHERE TOP_FORMULA_ID = P_FORMULA_ID
130 AND EXPLODE_SEQ = SUBSTR(UOMCFFORMULA.EXPLODE_SEQ
131 ,1
132 ,LENGTH(UOMCFFORMULA.EXPLODE_SEQ) - 3) || '001';
133 RETURN (UOM1);
134 RETURN NULL;
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 RETURN (NULL);
138 END UOMCFFORMULA;
139
140 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
141 BEGIN
142 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
143 RETURN (TRUE);
144 END BEFOREREPORT;
145
146 PROCEDURE GMD_ICR01USR_XMLP_PKG_HEADER IS
147 BEGIN
148 NULL;
149 END GMD_ICR01USR_XMLP_PKG_HEADER;
150
151 FUNCTION AFTERREPORT RETURN BOOLEAN IS
152 BEGIN
153 RETURN (TRUE);
154 END AFTERREPORT;
155
156 FUNCTION CF_1FORMULA(STATUS IN VARCHAR2) RETURN CHAR IS
157 X VARCHAR2(80);
158 BEGIN
159 IF STATUS IS NOT NULL THEN
160 RETURN STATUS;
161 ELSE
162 X := APPS.FND_MESSAGE.GET_STRING('GMD'
163 ,'GMD_IBOM_EXPLODE_ALL_LEVELS');
164 RETURN X;
165 END IF;
166 END CF_1FORMULA;
167
168 FUNCTION M_HDR_GRPFRFORMATTRIGGER RETURN NUMBER
169 IS
170 P_COUNT NUMBER;
171 BEGIN
172 SELECT Count(1)
173 INTO P_Count
174 FROM FM_IBOM_HDR ;
175
176 RETURN(P_COUNT);
177 END;
178 END GMD_ICR01USR_XMLP_PKG;
179