[Home] [Help]
PACKAGE BODY: APPS.INV_GRADE_PKG
Source
1 PACKAGE BODY INV_GRADE_PKG AS
2 /* $Header: INVUPLGB.pls 120.1 2006/09/21 14:38:59 jsrivast noship $ */
3
4 PROCEDURE print_debug(p_err_msg VARCHAR2,
5 p_level NUMBER default 4)
6 IS
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 BEGIN
9 IF (l_debug = 1) THEN
10 inv_mobile_helper_functions.tracelog
11 (p_err_msg => p_err_msg,
12 p_module => 'INV_GRADE_UPDATE',
13 p_level => p_level);
14 END IF;
15 END print_debug;
16
17 PROCEDURE UPDATE_GRADE
18 ( p_organization_id IN NUMBER DEFAULT NULL
19 , p_update_method IN NUMBER
20 , p_inventory_item_id IN NUMBER
21 , p_from_grade_code IN VARCHAR2
22 , p_to_grade_code IN VARCHAR2
23 , p_reason_id IN NUMBER
24 , p_lot_number IN VARCHAR2
25 , x_Status OUT NOCOPY VARCHAR2
26 , x_Message OUT NOCOPY VARCHAR2
27 , p_update_from_mobile IN VARCHAR2 DEFAULT 'N'
28 , p_primary_quantity IN NUMBER
29 , p_secondary_quantity IN NUMBER
30 ) IS
31 -- BEGIN SCHANDRU INVERES
32 l_grade_update_id NUMBER := NULL;
33 g_eres_enabled varchar2(1):= NVL(fnd_profile.VALUE('EDR_ERES_ENABLED'), 'N');
34
35 -- END SCHANDRU INVERES
36 BEGIN
37
38 /* Initialize API return status to success */
39 x_Status := FND_API.G_RET_STS_SUCCESS;
40
41
42 print_debug(' In Grade Update Package - Before Updating record ') ;
43
44 -- BEGIN SCHANDRU INVERES
45 select mtl_lot_grade_history_s.nextval
46 into l_grade_update_id
47 from dual;
48 -- END SCHANDRU INVERES
49
50 UPDATE mtl_lot_numbers
51 SET grade_code = p_to_grade_code
52 WHERE lot_number = p_lot_number
53 AND organization_id = p_organization_id
54 AND inventory_item_id = p_inventory_item_id ;
55
56 --COMMIT ;-- SCHANDRU INVERES
57
58 print_debug(' In Grade Update Package - Before inserting record ') ;
59
60 /* Define Savepoint */
61
62 -- BEGIN SCHANDRU INVERES
63 if ( p_update_from_mobile = 'Y') then
64 SAVEPOINT Insert_GradeUpdate_PVT;
65 end if;
66 -- END SCHANDRU INVERES
67 INSERT INTO MTL_LOT_GRADE_HISTORY
68 (
69 GRADE_UPDATE_ID
70 , INVENTORY_ITEM_ID
71 , ORGANIZATION_ID
72 , LOT_NUMBER
73 , UPDATE_METHOD
74 , NEW_GRADE_CODE
75 , OLD_GRADE_CODE
76 , PRIMARY_QUANTITY
77 , SECONDARY_QUANTITY
78 , UPDATE_REASON_ID
79 , INITIAL_GRADE_FLAG
80 , FROM_MOBILE_APPS_FLAG
81 , GRADE_UPDATE_DATE
82 , ATTRIBUTE1
83 , ATTRIBUTE2
84 , ATTRIBUTE3
85 , ATTRIBUTE4
86 , ATTRIBUTE5
87 , ATTRIBUTE6
88 , ATTRIBUTE7
89 , ATTRIBUTE8
90 , ATTRIBUTE9
91 , ATTRIBUTE10
92 , ATTRIBUTE11
93 , ATTRIBUTE12
94 , ATTRIBUTE13
95 , ATTRIBUTE14
96 , ATTRIBUTE15
97 , ATTRIBUTE_CATEGORY
98 , CREATION_DATE
99 , CREATED_BY
100 , LAST_UPDATED_BY
101 , LAST_UPDATE_DATE
102 , LAST_UPDATE_LOGIN
103 ) VALUES
104 (
105 -- BEGIN SCHANDRU INVERES
106 -- MTL_LOT_GRADE_HISTORY_S.NEXTVAL
107 l_grade_update_id
108 -- END SCHADRU INVERES
109 , p_inventory_item_id
110 , p_organization_id
111 , p_lot_number
112 , p_update_method -- UPDATE_METHOD /* Jalaj Srivastava Bug 4998256 pass p_update_method instead of null */
113 , p_to_grade_code -- NEW_GRADE_CODE
114 , p_from_grade_code -- OLD_GRADE_CODE
115 , p_primary_quantity -- PRIMARY_QUANTITY
116 , p_secondary_quantity -- SECONDARY_QUANTITY
117 , p_reason_id -- UPDATE_REASON_ID
118 , 'N' -- INITIAL_GRADE_FLAG
119 , 'N' -- FROM_MOBILE_APPS_FLAG
120 , SYSDATE -- GRADE_UPDATE_DATE
121 , NULL -- ATTRIBUTE1
122 , NULL -- ATTRIBUTE2
123 , NULL -- ATTRIBUTE3
124 , NULL -- ATTRIBUTE4
125 , NULL -- ATTRIBUTE5
126 , NULL -- ATTRIBUTE6
127 , NULL -- ATTRIBUTE7
128 , NULL -- ATTRIBUTE8
129 , NULL -- ATTRIBUTE9
130 , NULL -- ATTRIBUTE10
131 , NULL -- ATTRIBUTE11
132 , NULL -- ATTRIBUTE12
133 , NULL -- ATTRIBUTE13
134 , NULL -- ATTRIBUTE14
135 , NULL -- ATTRIBUTE15
136 , NULL -- ATTRIBUTE_CATEGORY
137 , SYSDATE -- CREATION_DATE
138 , FND_GLOBAL.USER_ID -- CREATED_BY
139 , FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
140 , SYSDATE -- LAST_UPDATE_DATE
141 , FND_GLOBAL.LOGIN_ID -- LAST_UPDATE_LOGIN
142 ) ;
143
144 -- BEGIN SCHANDRU INVERES
145 IF g_eres_enabled <> 'N' THEN
146
147 Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id, grade_update_id) values ( NULL, l_grade_update_id);
148 END IF;
149 -- END SCHANDRU INVERES
150
151 print_debug(' In Grade Update Package - AFtrer inserting record ') ;
152
153 --COMMIT ;-- SCHANDRU INVERES
154
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 -- BEGIN SCHANDRU INVERES
158 if ( p_update_from_mobile = 'Y') then
159 ROLLBACK TO Insert_GradeUpdate_PVT;
160 end if;
161 -- END SCHANDRU INVERES
162 x_Status := FND_API.G_RET_STS_ERROR;
163 x_Message := SQLERRM ;
164 print_debug(' In Grade Update Package - Encountered exec error ') ;
165
166 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
167 -- BEGIN SCHANDRU INVERES
168 if ( p_update_from_mobile = 'Y') then
169 ROLLBACK TO Insert_GradeUpdate_PVT;
170 end if;
171 -- END SCHANDRU INVERES
172
173 x_Status := FND_API.G_RET_STS_ERROR;
174 x_Message := SQLERRM ;
175 print_debug(' In Grade Update Package - Encountered unexpected error ') ;
176 WHEN OTHERS THEN
177 -- BEGIN SCHANDRU INVERES
178 if ( p_update_from_mobile = 'Y') then
179 ROLLBACK TO Insert_GradeUpdate_PVT;
180 end if;
181 -- END SCHANDRU INVERES
182
183 x_Status := FND_API.G_RET_STS_UNEXP_ERROR;
184 X_Message := SQLERRM ;
185 print_debug(' In Grade Update Package - Encountered other error ') ;
186
187 END UPDATE_GRADE ;
188
189
190 END INV_GRADE_PKG ;