[Home] [Help]
PACKAGE BODY: APPS.GMI_SUMM_INV_DB_PVT
Source
1 PACKAGE BODY GMI_SUMM_INV_DB_PVT AS
2 /* $Header: GMIVSUMB.pls 115.8 2002/04/10 09:14:43 pkm ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVSUMB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private definitions For DML Actions |
13 | For IC_SUMM_INV |
14 | |
18 | Bug 1662876
15 | HISTORY |
16 | 12-JAN-2000 H.Verdding |
17 | 07-MAR-2001 Jalaj Srivastava
19 | 08-JAN-2002 Joe DiIorio 11.5.1I BUG#2043337
20 | Make all qty 2 fields get added with zero when null.
21 +=========================================================================+
22 API Name : GMI_SUMM_INV_DB_PVT
23 Type : Public
24 Function : This package contains private procedures used to create
25 IC_SUMM_INV transactions
26 Pre-reqs : N/A
27 Parameters: Per function
28
29 Current Vers : 1.0
30
31 Previous Vers : 1.0
32
33 Initial Vers : 1.0
34 Notes
35
36
37 Body end of comments
38 */
39 /* Global variables */
40 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_SUMM_INV_DB_PVT';
41 /* Api start of comments */
42
43 FUNCTION UPDATE_IC_SUMM_INV
44 (
45 p_summ_inv IN IC_SUMM_INV%ROWTYPE
46 )
47 RETURN BOOLEAN
48 IS
49
50 err_num NUMBER;
51 err_msg VARCHAR2(100);
52
53 BEGIN
54
55 IF ( p_summ_inv.qc_grade is NULL) THEN
56
57 --Jalaj Srivastava Bug 1662876
58 --nvl is required with qty2 since for pending txns qty2 is null
59 UPDATE IC_SUMM_INV
60 SET
61 onhand_qty = onhand_qty + p_summ_inv.onhand_qty,
62 onhand_qty2 = onhand_qty2 + NVL(p_summ_inv.onhand_qty2,0),
63 onhand_prod_qty = onhand_prod_qty + p_summ_inv.onhand_prod_qty,
64 onhand_prod_qty2 = NVL(onhand_prod_qty2,0) +
65 NVL(p_summ_inv.onhand_prod_qty2,0),
66 onhand_order_qty = onhand_order_qty + p_summ_inv.onhand_order_qty,
67 onhand_order_qty2 = NVL(onhand_order_qty2,0) +
68 NVL(p_summ_inv.onhand_order_qty2,0),
69 onhand_ship_qty = onhand_ship_qty + p_summ_inv.onhand_ship_qty,
70 onhand_ship_qty2 = NVL(onhand_ship_qty2,0) +
71 NVL(p_summ_inv.onhand_ship_qty2,0),
72 onpurch_qty = onpurch_qty + p_summ_inv.onpurch_qty,
73 onpurch_qty2 = NVL(onpurch_qty2,0) +
74 NVL(p_summ_inv.onpurch_qty2,0),
75 onprod_qty = onprod_qty + p_summ_inv.onprod_qty,
76 onprod_qty2 = NVL(onprod_qty2,0) +
77 NVL(p_summ_inv.onprod_qty2,0),
78 committedsales_qty = committedsales_qty + p_summ_inv.committedsales_qty,
79 committedsales_qty2 = NVL(committedsales_qty2,0) +
80 NVL(p_summ_inv.committedsales_qty2,0),
81 committedprod_qty = committedprod_qty + p_summ_inv.committedprod_qty,
82 committedprod_qty2 = NVL(committedprod_qty2,0) +
83 NVL(p_summ_inv.committedprod_qty2,0),
84 intransit_qty = intransit_qty + p_summ_inv.intransit_qty,
85 intransit_qty2 = NVL(intransit_qty2,0) +
86 NVL(p_summ_inv.intransit_qty2,0),
87 last_updated_by = p_summ_inv.last_updated_by,
88 created_by = p_summ_inv.created_by,
89 last_update_date = p_summ_inv.last_update_date,
90 creation_date = p_summ_inv.creation_date
91 WHERE item_id = p_summ_inv.item_id
92 AND whse_code = p_summ_inv.whse_code
93 AND qc_grade IS NULL;
94
95 ELSE
96
97 UPDATE IC_SUMM_INV
98 SET
99 onhand_qty = onhand_qty + p_summ_inv.onhand_qty,
100 onhand_qty2 = onhand_qty2 + NVL(p_summ_inv.onhand_qty2,0),
101 onhand_prod_qty = onhand_prod_qty + p_summ_inv.onhand_prod_qty,
102 onhand_prod_qty2 = NVL(onhand_prod_qty2,0) +
103 NVL(p_summ_inv.onhand_prod_qty2,0),
104 onhand_order_qty = onhand_order_qty + p_summ_inv.onhand_order_qty,
105 onhand_order_qty2 = NVL(onhand_order_qty2,0) +
106 NVL(p_summ_inv.onhand_order_qty2,0),
107 onhand_ship_qty = onhand_ship_qty + p_summ_inv.onhand_ship_qty,
108 onhand_ship_qty2 = NVL(onhand_ship_qty2,0) +
109 NVL(p_summ_inv.onhand_ship_qty2,0),
110 onpurch_qty = onpurch_qty + p_summ_inv.onpurch_qty,
111 onpurch_qty2 = NVL(onpurch_qty2,0) +
112 NVL(p_summ_inv.onpurch_qty2,0),
113 onprod_qty = onprod_qty + p_summ_inv.onprod_qty,
114 onprod_qty2 = NVL(onprod_qty2,0) +
115 NVL(p_summ_inv.onprod_qty2,0),
116 committedsales_qty = committedsales_qty + p_summ_inv.committedsales_qty,
117 committedsales_qty2 = NVL(committedsales_qty2,0) +
118 NVL(p_summ_inv.committedsales_qty2,0),
119 committedprod_qty = committedprod_qty + p_summ_inv.committedprod_qty,
120 committedprod_qty2 = NVL(committedprod_qty2,0) +
121 NVL(p_summ_inv.committedprod_qty2,0),
122 intransit_qty = intransit_qty + p_summ_inv.intransit_qty,
123 intransit_qty2 = NVL(intransit_qty2,0) +
124 NVL(p_summ_inv.intransit_qty2,0),
125 last_updated_by = p_summ_inv.last_updated_by,
126 created_by = p_summ_inv.created_by,
127 last_update_date = p_summ_inv.last_update_date,
128 creation_date = p_summ_inv.creation_date
129 WHERE item_id = p_summ_inv.item_id
130 AND whse_code = p_summ_inv.whse_code
131 AND qc_grade = p_summ_inv.qc_grade;
132
133 END IF;
134
135 IF SQL%ROWCOUNT =0 THEN
136 RETURN FALSE;
137 END IF;
138
139 RETURN TRUE;
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 RETURN FALSE;
144 WHEN OTHERS THEN
145 /* err_num :=SQLCODE; */
146 /* err_msg :=SUBSTR(SQLERRM,1 ,100);*/
147 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
148 , 'update_ic_summ_inv'
149 );
150 RETURN FALSE;
151
152 END UPDATE_IC_SUMM_INV;
153
154 FUNCTION INSERT_IC_SUMM_INV
155 (
156 p_summ_inv IN IC_SUMM_INV%ROWTYPE
157 )
158 RETURN BOOLEAN
159 IS
160 err_num NUMBER;
161 err_msg VARCHAR2(100);
162 l_summ_inv_id NUMBER;
163
164 BEGIN
165
166 /* GET Sequence Number For SUMM_INV_ID*/
167
168 SELECT GEM5_SUMM_INV_ID_S.nextval
169 INTO l_summ_inv_id
170 FROM dual;
171
172
173 INSERT INTO IC_SUMM_INV
174 (
175 summ_inv_id,
176 item_id,
177 whse_code,
178 qc_grade,
179 onhand_qty,
180 onhand_qty2,
181 onhand_prod_qty,
182 onhand_prod_qty2,
183 onhand_order_qty,
184 onhand_order_qty2,
185 onhand_ship_qty,
186 onhand_ship_qty2,
187 onpurch_qty,
188 onpurch_qty2,
189 onprod_qty,
190 onprod_qty2,
191 committedsales_qty,
192 committedsales_qty2,
193 committedprod_qty,
194 committedprod_qty2,
195 intransit_qty,
196 intransit_qty2,
197 last_updated_by,
198 created_by,
199 last_update_date,
200 creation_date
201 )
202 VALUES
203 (
204 l_summ_inv_id,
205 p_summ_inv.item_id,
206 p_summ_inv.whse_code,
207 p_summ_inv.qc_grade,
208 p_summ_inv.onhand_qty,
209 nvl(p_summ_inv.onhand_qty2,0),
210 p_summ_inv.onhand_prod_qty,
211 nvl(p_summ_inv.onhand_prod_qty2,0),
212 p_summ_inv.onhand_order_qty,
213 nvl(p_summ_inv.onhand_order_qty2,0),
214 p_summ_inv.onhand_ship_qty,
215 nvl(p_summ_inv.onhand_ship_qty2,0),
216 p_summ_inv.onpurch_qty,
217 nvl(p_summ_inv.onpurch_qty2,0),
218 p_summ_inv.onprod_qty,
219 nvl(p_summ_inv.onprod_qty2,0),
220 p_summ_inv.committedsales_qty,
221 nvl(p_summ_inv.committedsales_qty2,0),
222 p_summ_inv.committedprod_qty,
223 nvl(p_summ_inv.committedprod_qty2,0),
224 p_summ_inv.intransit_qty,
225 nvl(p_summ_inv.intransit_qty2,0),
226 p_summ_inv.last_updated_by,
227 p_summ_inv.created_by,
228 p_summ_inv.last_update_date,
229 p_summ_inv.creation_date
230 );
231
232 RETURN TRUE;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 /* err_num :=SQLCODE;*/
237 /* err_msg :=SUBSTR(SQLERRM,1 ,100);*/
238 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
239 , 'insert_ic_summ_inv'
240 );
241 RETURN FALSE;
242
243
244 END INSERT_IC_SUMM_INV;
245
246 FUNCTION GET_LOT_ATTRIBUTES
247 (
248 p_lot_status IN VARCHAR2,
249 x_lots_sts OUT IC_LOTS_STS%ROWTYPE
250 )
251 RETURN BOOLEAN
252 IS
253 /* err_num NUMBER;*/
254 /* err_msg VARCHAR2(100);*/
255
256 CURSOR Get_Lot_status(v_lot_status IN VARCHAR2)
257 IS
258 SELECT *
259 FROM IC_LOTS_STS
260 WHERE UPPER(lot_status) = UPPER(v_lot_status)
261 AND DELETE_MARK <> 1;
262
263
264 BEGIN
265
266 OPEN get_lot_status(p_lot_status);
267 FETCH get_lot_status into x_lots_sts;
268
269 IF (get_lot_status%NOTFOUND) THEN
270 RAISE NO_DATA_FOUND;
271 END IF;
272
273
274 RETURN TRUE;
275
276 EXCEPTION
277
278 WHEN NO_DATA_FOUND THEN
279 RETURN FALSE;
280
281 WHEN OTHERS THEN
282 /* err_num :=SQLCODE;*/
283 /* err_msg :=SUBSTR(SQLERRM,1 ,100);*/
284 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
285 , 'insert_ic_summ_inv'
286 );
287 RETURN FALSE;
288
289 END GET_LOT_ATTRIBUTES;
290
291
292 END GMI_SUMM_INV_DB_PVT;