1 PACKAGE BODY GMI_RIWIN_XMLP_PKG AS
2 /* $Header: RIWINB.pls 120.0 2007/12/24 13:29:09 nchinnam noship $ */
3 FUNCTION WHSE_QTYFORMULA(WHSCODE IN VARCHAR2
4 ,ITEM_ID IN NUMBER) RETURN NUMBER IS
5 WHSEQTY NUMBER;
6 BEGIN
7 SELECT
8 SUM(LOCT_ONHAND)
9 INTO WHSEQTY
10 FROM
11 IC_ITEM_MST ITM,
12 IC_LOCT_INV ILI
13 WHERE ITM.ITEM_ID = ILI.ITEM_ID
14 AND ILI.DELETE_MARK = 0
15 AND ITM.DELETE_MARK = 0
16 AND ILI.WHSE_CODE = WHSCODE
17 AND ITM.ITEM_ID = WHSE_QTYFORMULA.ITEM_ID;
18 RETURN (WHSEQTY);
19 END WHSE_QTYFORMULA;
20
21 FUNCTION TOTAL_QTYFORMULA(ITEM_ID IN NUMBER) RETURN NUMBER IS
22 TOTALQTY NUMBER;
23 BEGIN
24 SELECT
25 SUM(LOCT_ONHAND)
26 INTO TOTALQTY
27 FROM
28 IC_ITEM_MST ITM,
29 IC_LOCT_INV ILI
30 WHERE ITM.ITEM_ID = ILI.ITEM_ID
31 AND ILI.DELETE_MARK = 0
32 AND ITM.DELETE_MARK = 0
33 AND ITM.ITEM_ID = TOTAL_QTYFORMULA.ITEM_ID;
34 RETURN (TOTALQTY);
35 IF SQL%NOTFOUND THEN
36 RETURN (0.00);
37 END IF;
38 RETURN NULL;
39 END TOTAL_QTYFORMULA;
40
41 FUNCTION WHSE_QTY2FORMULA(WHSCODE IN VARCHAR2
42 ,ITEM_ID IN NUMBER) RETURN NUMBER IS
43 WHSEQTY2 NUMBER:=0;
44 BEGIN
45 SELECT
46 NVL(SUM(LOCT_ONHAND2),0)
47 INTO WHSEQTY2
48 FROM
49 IC_ITEM_MST ITM,
50 IC_LOCT_INV ILI
51 WHERE ITM.ITEM_ID = ILI.ITEM_ID
52 AND ILI.DELETE_MARK = 0
53 AND ITM.DELETE_MARK = 0
54 AND ILI.WHSE_CODE = WHSCODE
55 AND ITM.ITEM_ID = WHSE_QTY2FORMULA.ITEM_ID;
56 RETURN (WHSEQTY2);
57 END WHSE_QTY2FORMULA;
58
59 FUNCTION TOTAL_QTY2FORMULA(ITEM_ID IN NUMBER) RETURN NUMBER IS
60 TOTALQTY2 NUMBER:=0;
61 BEGIN
62 SELECT
63 NVL(SUM(LOCT_ONHAND2),0)
64 INTO TOTALQTY2
65 FROM
66 IC_ITEM_MST ITM,
67 IC_LOCT_INV ILI
68 WHERE ITM.ITEM_ID = ILI.ITEM_ID
69 AND ILI.DELETE_MARK = 0
70 AND ITM.DELETE_MARK = 0
71 AND ITM.ITEM_ID = TOTAL_QTY2FORMULA.ITEM_ID;
72 RETURN (TOTALQTY2);
73 IF SQL%NOTFOUND THEN
74 RETURN (0.00);
75 END IF;
76 RETURN NULL;
77 END TOTAL_QTY2FORMULA;
78
79 FUNCTION CF_RANGEFORMULA RETURN VARCHAR2 IS
80 RANGEV VARCHAR2(200);
81 BEGIN
82 SELECT
83 DECODE(FROM_ITEM
84 ,NULL
85 ,DECODE(TO_ITEM
86 ,NULL
87 ,'Item Range : All '
88 ,'Item Range : All - ' || TO_ITEM)
89 ,DECODE(TO_ITEM
90 ,NULL
91 ,'Item Range : ' || FROM_ITEM || ' - All '
92 ,'Item Range : ' || FROM_ITEM || ' - ' || TO_ITEM))
93 INTO RANGEV
94 FROM
95 DUAL;
96 RETURN (RANGEV);
97 END CF_RANGEFORMULA;
98
99 FUNCTION USERCFFORMULA RETURN VARCHAR2 IS
100 USERNAME VARCHAR2(100);
101 BEGIN
102 SELECT
103 USER_NAME
104 INTO USERNAME
105 FROM
106 FND_USER
107 WHERE USER_ID = GMI_RIWIN_XMLP_PKG.USER_ID;
108 RETURN (USERNAME);
109 END USERCFFORMULA;
110
111 FUNCTION FROM_ITEMCFFORMULA RETURN VARCHAR2 IS
112 BEGIN
113 IF FROM_ITEM IS NULL THEN
114 SELECT
115 'All'
116 INTO FROM_ITEMCP
117 FROM
118 DUAL;
119 ELSE
120 FROM_ITEMCP := FROM_ITEM;
121 END IF;
122 RETURN (FROM_ITEMCP);
123 END FROM_ITEMCFFORMULA;
124
125 FUNCTION TO_ITEMCFFORMULA RETURN VARCHAR2 IS
126 BEGIN
127 IF TO_ITEM IS NULL THEN
128 SELECT
129 'All'
130 INTO TO_ITEMCP
131 FROM
132 DUAL;
133 ELSE
134 TO_ITEMCP := TO_ITEM;
135 END IF;
136 RETURN (TO_ITEMCP);
137 END TO_ITEMCFFORMULA;
138
139 FUNCTION TO_WHSECFFORMULA RETURN VARCHAR2 IS
140 BEGIN
141 IF TO_WHSE IS NULL THEN
142 SELECT
143 'All'
144 INTO TO_WHSECP
145 FROM
146 DUAL;
147 ELSE
148 TO_WHSECP := TO_WHSE;
149 END IF;
150 RETURN (TO_WHSECP);
151 END TO_WHSECFFORMULA;
152
153 FUNCTION FROM_WHSECFFORMULA RETURN VARCHAR2 IS
154 BEGIN
155 IF FROM_WHSE IS NULL THEN
156 SELECT
157 'All'
158 INTO FROM_WHSECP
159 FROM
160 DUAL;
161 ELSE
162 FROM_WHSECP := FROM_WHSE;
163 END IF;
164 RETURN (FROM_WHSECP);
165 END FROM_WHSECFFORMULA;
166
167 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
168 BEGIN
169 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
170
171 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
172 RETURN (TRUE);
173 END BEFOREREPORT;
174
175 PROCEDURE HEADER IS
176 BEGIN
177 NULL;
178 END HEADER;
179
180 FUNCTION AFTERREPORT RETURN BOOLEAN IS
181 BEGIN
182 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
183 RETURN (TRUE);
184 END AFTERREPORT;
185
186 FUNCTION FROM_ITEMCP_P RETURN VARCHAR2 IS
187 BEGIN
188 RETURN FROM_ITEMCP;
189 END FROM_ITEMCP_P;
190
191 FUNCTION TO_ITEMCP_P RETURN VARCHAR2 IS
192 BEGIN
193 RETURN TO_ITEMCP;
194 END TO_ITEMCP_P;
195
196 FUNCTION FROM_WHSECP_P RETURN VARCHAR2 IS
197 BEGIN
198 RETURN FROM_WHSECP;
199 END FROM_WHSECP_P;
200
201 FUNCTION TO_WHSECP_P RETURN VARCHAR2 IS
202 BEGIN
203 RETURN TO_WHSECP;
204 END TO_WHSECP_P;
205
206 END GMI_RIWIN_XMLP_PKG;
207
[Home] [Help]