[Home] [Help]
PACKAGE BODY: APPS.MTL_LOT_CONV_AUD_DET_PKG
Source
1 package body MTL_LOT_CONV_AUD_DET_PKG as
2 /* $Header: INVHLCDB.pls 120.0 2005/05/25 05:44:24 appldev noship $ */
3
4
5 PROCEDURE INSERT_ROW(
6 X_CONV_AUDIT_DETAIL_ID IN OUT NOCOPY NUMBER,
7 X_CONV_AUDIT_ID IN NUMBER,
8 X_REVISION IN VARCHAR2,
9 X_ORGANIZATION_ID IN NUMBER,
10 X_SUBINVENTORY_CODE IN VARCHAR2,
11 X_LPN_ID IN NUMBER,
12 X_LOCATOR_ID IN NUMBER,
13 X_OLD_PRIMARY_QTY IN NUMBER,
14 X_OLD_SECONDARY_QTY IN NUMBER,
15 X_NEW_PRIMARY_QTY IN NUMBER,
16 X_NEW_SECONDARY_QTY IN NUMBER,
17 X_TRANSACTION_PRIMARY_QTY IN NUMBER,
18 X_TRANSACTION_SECONDARY_QTY IN NUMBER,
19 X_TRANSACTION_UPDATE_FLAG IN VARCHAR2,
20 X_CREATED_BY IN NUMBER,
21 X_CREATION_DATE IN DATE,
22 X_LAST_UPDATED_BY IN NUMBER,
23 X_LAST_UPDATE_DATE IN DATE,
24 X_LAST_UPDATE_LOGIN IN NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2)
28
29 IS
30
31
32 CURSOR GET_AUD_DET_SEQ
33 IS
34 SELECT MTL_CONV_AUDIT_DETAIL_ID_S.NEXTVAL
35 FROM FND_DUAL;
36
37 l_aud_det_seq NUMBER;
38
39
40 BEGIN
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 IF (X_CONV_AUDIT_DETAIL_ID IS NULL) THEN
43 OPEN GET_AUD_DET_SEQ;
44 FETCH GET_AUD_DET_SEQ INTO l_aud_det_seq;
45 IF (SQL%NOTFOUND) then
46 CLOSE GET_AUD_DET_SEQ;
47 RAISE no_data_found;
48 END IF;
49 X_CONV_AUDIT_DETAIL_ID := l_aud_det_seq;
50 CLOSE GET_AUD_DET_SEQ;
51 END IF;
52
53 INSERT INTO MTL_LOT_CONV_AUDIT_DETAILS (
54 CONV_AUDIT_DETAIL_ID,
55 CONV_AUDIT_ID,
56 REVISION,
57 ORGANIZATION_ID,
58 SUBINVENTORY_CODE,
59 LPN_ID,
60 LOCATOR_ID,
61 OLD_PRIMARY_QTY,
62 OLD_SECONDARY_QTY,
63 NEW_PRIMARY_QTY,
64 NEW_SECONDARY_QTY,
65 TRANSACTION_PRIMARY_QTY,
66 TRANSACTION_SECONDARY_QTY,
67 TRANSACTION_UPDATE_FLAG,
68 CREATED_BY,
69 CREATION_DATE,
70 LAST_UPDATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATE_LOGIN
73 )
74 VALUES (
75 X_CONV_AUDIT_DETAIL_ID,
76 X_CONV_AUDIT_ID,
77 X_REVISION,
78 X_ORGANIZATION_ID,
79 X_SUBINVENTORY_CODE,
80 X_LPN_ID,
81 X_LOCATOR_ID,
82 X_OLD_PRIMARY_QTY,
83 X_OLD_SECONDARY_QTY,
84 X_NEW_PRIMARY_QTY,
85 X_NEW_SECONDARY_QTY,
86 X_TRANSACTION_PRIMARY_QTY,
87 X_TRANSACTION_SECONDARY_QTY,
88 X_TRANSACTION_UPDATE_FLAG,
89 X_CREATED_BY,
90 X_CREATION_DATE,
91 X_LAST_UPDATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATE_LOGIN
94 );
95
96
97
98 FND_MSG_PUB.Count_AND_GET
99 (p_count => x_msg_count, p_data => x_msg_data);
100
101 EXCEPTION
102
103 WHEN OTHERS THEN
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 IF (SQLCODE IS NOT NULL) THEN
106 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
107 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
108 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
109 FND_MSG_PUB.Add;
110 END IF;
111 FND_MSG_PUB.Count_AND_GET
112 (p_count => x_msg_count, p_data => x_msg_data);
113
114 END INSERT_ROW;
115
116
117 PROCEDURE UPDATE_ROW(
118 X_CONV_AUDIT_DETAIL_ID IN NUMBER,
119 X_CONV_AUDIT_ID IN NUMBER,
120 X_REVISION IN VARCHAR2,
121 X_ORGANIZATION_ID IN NUMBER,
122 X_SUBINVENTORY_CODE IN VARCHAR2,
123 X_LPN_ID IN NUMBER,
124 X_LOCATOR_ID IN NUMBER,
125 X_OLD_PRIMARY_QTY IN NUMBER,
126 X_OLD_SECONDARY_QTY IN NUMBER,
127 X_NEW_PRIMARY_QTY IN NUMBER,
128 X_NEW_SECONDARY_QTY IN NUMBER,
129 X_TRANSACTION_PRIMARY_QTY IN NUMBER,
130 X_TRANSACTION_SECONDARY_QTY IN NUMBER,
131 X_TRANSACTION_UPDATE_FLAG IN VARCHAR2,
132 X_LAST_UPDATED_BY IN NUMBER,
133 X_LAST_UPDATE_DATE IN DATE,
134 X_LAST_UPDATE_LOGIN IN NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2)
138
139
140 IS
141
142 BEGIN
143
144
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146 UPDATE MTL_LOT_CONV_AUDIT_DETAILS SET
147 CONV_AUDIT_ID = X_CONV_AUDIT_ID,
148 REVISION = X_REVISION,
149 ORGANIZATION_ID = X_ORGANIZATION_ID,
150 SUBINVENTORY_CODE = X_SUBINVENTORY_CODE,
151 LPN_ID = X_LPN_ID,
152 LOCATOR_ID = X_LOCATOR_ID,
153 OLD_PRIMARY_QTY = X_OLD_PRIMARY_QTY,
154 OLD_SECONDARY_QTY = X_OLD_SECONDARY_QTY,
155 NEW_PRIMARY_QTY = X_NEW_PRIMARY_QTY,
156 NEW_SECONDARY_QTY = X_NEW_SECONDARY_QTY,
157 TRANSACTION_PRIMARY_QTY = X_TRANSACTION_PRIMARY_QTY,
158 TRANSACTION_SECONDARY_QTY = X_TRANSACTION_SECONDARY_QTY,
159 TRANSACTION_UPDATE_FLAG = X_TRANSACTION_UPDATE_FLAG,
160 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
161 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
163 WHERE CONV_AUDIT_DETAIL_ID = X_CONV_AUDIT_DETAIL_ID;
164
165
166 FND_MSG_PUB.Count_AND_GET
167 (p_count => x_msg_count, p_data => x_msg_data);
168
169 EXCEPTION
170
171 WHEN OTHERS THEN
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 IF (SQLCODE IS NOT NULL) THEN
174 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
175 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
176 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
177 FND_MSG_PUB.Add;
178 END IF;
179 FND_MSG_PUB.Count_AND_GET
180 (p_count => x_msg_count, p_data => x_msg_data);
181
182
183 END UPDATE_ROW;
184
185
186
187 END MTL_LOT_CONV_AUD_DET_PKG;