[Home] [Help]
PACKAGE BODY: APPS.GMI_LOCT_INV_DB_PVT
Source
1 PACKAGE BODY GMI_LOCT_INV_DB_PVT AS
2 /* $Header: GMIVLOCB.pls 115.9 2003/03/27 20:31:22 adeshmuk ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVLOCB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private definitions For DML Actions |
13 | For IC_LOCT_INV |
14 | |
15 | HISTORY |
16 | 12-JAN-2000 H.Verdding |
17 | 27-DEC-2001 J. DiIorio Bug#2117575 11.5.1I |
18 | Changed trni logic to update status when diff stat = 2. A |
19 | standard move with this doc type may cause a status update. |
20 | Jalaj Srivastava Bug 2483644 |
21 | Removed code for bug 2117575 as it is already taken care |
22 | of in GMIVQTY.validate_inventory_posting. |
23 | 18-MAR-2003 James Bernard BUG#2847679 |
24 | Code is added to update ic_loct_inv.last_update_date with |
25 | SYSDATE and ic_loct_inv.last_updated_by the user who is currently |
26 | doing the adjust immediate. |
27 +=========================================================================+
28 API Name : GMI_LOCT_INV_DB_PVT
29 Type : Public
30 Function : This package contains private procedures used to create
31 IC_SUMM_INV transactions
32 Pre-reqs : N/A
33 Parameters: Per function
34
35 Current Vers : 1.0
36
37 Previous Vers : 1.0
38
39 Initial Vers : 1.0
40 Notes
41 */
42 /* Global variables */
43 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_LOCT_INV_DB_PVT';
44
45 FUNCTION UPDATE_IC_LOCT_INV
46 (
47 p_loct_inv IN IC_LOCT_INV%ROWTYPE,
48 p_status_updated IN NUMBER,
49 p_qty_updated IN NUMBER
50 )
51 RETURN BOOLEAN
52 IS
53
54 err_num NUMBER;
55 err_msg VARCHAR2(100);
56 bad_insert EXCEPTION;
57 bad_params EXCEPTION;
58
59 BEGIN
60
61 IF ( p_qty_updated =1) THEN
62 /* *************************************************
63 Jalaj Srivastava Bug 2483644
64 Below fix is not required here since, this
65 scenario is already taken care of in
66 GMIVQTY.validate_inventory_posting
67 ************************************************* */
68 /*================================================
69 27-DEC-2001 J. DiIorio Bug#2117575 11.5.1I |
70 ==============================================*/
71 /*================================================
72 21-MAR-2003 James Bernard Bug#2847679
73 Modified the update statement to update last_update_date with SYSDATE
74 and last_updated_by with the user_id if the current user doing adjust immediate.
75 ==============================================*/
76
77 UPDATE ic_loct_inv
78 SET
79 loct_onhand = loct_onhand + p_loct_inv.loct_onhand,
80 loct_onhand2 = loct_onhand2 + p_loct_inv.loct_onhand2,
81 last_update_date = SYSDATE,
82 last_updated_by = p_loct_inv.last_updated_by
83 WHERE
84 item_id = p_loct_inv.item_id and
85 lot_id = p_loct_inv.lot_id and
86 whse_code = p_loct_inv.whse_code and
87 location = p_loct_inv.location;
88
89 ELSIF (p_status_updated = 1) THEN
90
91 /*================================================
92 21-MAR-2003 James Bernard Bug#2847679
93 Modified the update statement to update last_update_date with SYSDATE
94 and last_updated_by with the user_id if the current user doing adjust immediate.
95 ==============================================*/
96
97 UPDATE ic_loct_inv
98 SET
99 lot_status = p_loct_inv.lot_status,
100 last_update_date = SYSDATE,
101 last_updated_by = p_loct_inv.last_updated_by
102 WHERE
103 item_id = p_loct_inv.item_id and
104 lot_id = p_loct_inv.lot_id and
105 whse_code = p_loct_inv.whse_code and
106 location = p_loct_inv.location;
107
108 ELSE
109 RAISE bad_params;
110 END IF;
111
112 IF SQL%ROWCOUNT = 0
113 THEN
114 /* There was nothing to update so we must insert a row */
115
116 IF INSERT_IC_LOCT_INV(p_loct_inv)
117 THEN
118 NULL;
119 ELSE
120 RAISE bad_insert;
121 END IF;
122 END IF;
123
124 RETURN TRUE;
125
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 RETURN FALSE;
129 WHEN OTHERS THEN
130 err_num :=SQLCODE;
131 err_msg :=SUBSTR(SQLERRM,1 ,100);
132 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
133 , 'update_ic_LOCT_inv'
134 );
135 RETURN FALSE;
136
137 END UPDATE_IC_LOCT_INV;
138
139 FUNCTION INSERT_IC_LOCT_INV
140 (
141 p_LOCT_inv IN IC_LOCT_INV%ROWTYPE
142 )
143 RETURN BOOLEAN
144 IS
145 err_num NUMBER;
146 err_msg VARCHAR2(100);
147
148 BEGIN
149
150 INSERT INTO IC_LOCT_INV
151 (
152 item_id,
153 whse_code,
154 lot_id,
155 location,
156 loct_onhand,
157 loct_onhand2,
158 lot_status,
159 qchold_res_code,
160 delete_mark,
161 text_code,
162 created_by,
163 creation_date,
164 last_update_date,
165 last_updated_by
166 )
167 VALUES
168 (
169 p_loct_inv.item_id,
170 p_loct_inv.whse_code,
171 p_loct_inv.lot_id,
172 p_loct_inv.location,
173 p_loct_inv.loct_onhand,
174 p_loct_inv.loct_onhand2,
175 p_loct_inv.lot_status,
176 p_loct_inv.qchold_res_code,
177 p_loct_inv.delete_mark,
178 p_loct_inv.text_code,
179 p_loct_inv.created_by,
180 p_loct_inv.creation_date,
181 p_LOCT_inv.last_update_date,
182 p_LOCT_inv.last_updated_by
183 );
184
185 RETURN TRUE;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 err_num :=SQLCODE;
190 err_msg :=SUBSTR(SQLERRM,1 ,100);
191 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
192 , 'insert_ic_LOCT_inv'
193 );
194 RETURN FALSE;
195
196 END INSERT_IC_LOCT_INV;
197
198 END GMI_LOCT_INV_DB_PVT;