[Home] [Help]
PACKAGE BODY: APPS.INV_OPSM_LOT_BE_PKG
Source
1 PACKAGE BODY INV_OPSM_LOT_BE_PKG AS
2 /* $Header: INVOPSMLBEB.pls 120.1 2010/09/27 20:27:38 skaradib noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='INV_OPSM_LOT_BE_PKG';
4 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5 /*------------------------------------
6 --PROCEDURE print_debug will be used to log the messages
7 ------------------------------------*/
8 PROCEDURE PRINT_DEBUG ( P_MESSAGE VARCHAR2) IS
9 BEGIN
10 IF g_debug IS NULL THEN
11 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
12 END IF;
13 IF (g_debug = 1) THEN
14 inv_log_util.trace(p_message, 'INV_OPSM_LOT_BE_PKG', 9);
15 END IF;
16 END PRINT_DEBUG;
17 PROCEDURE RAISE_EVENT(P_GEN_OBJECT_ID IN NUMBER
18 ,P_EVENT IN VARCHAR2
19 ,P_PARAMETERS IN WF_PARAMETER_LIST_T
20 ,P_SEND_DATE IN DATE DEFAULT SYSDATE
21 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
22 L_RAISE_EVENT BOOLEAN;
23 L_WF_PROCESS_EXISTS VARCHAR2(1);
24 L_RETURN_STATUS VARCHAR2(1);
25 L_NO_ENABLED_SUB NUMBER;
26 L_MODULE_NAME CONSTANT VARCHAR2(100) := 'INV.PLSQL.'||G_PKG_NAME|| '.' ||'RAISE_EVENT';
27 L_DEBUG_ON BOOLEAN;
28 BEGIN
29 IF (g_debug = 1) THEN
30 L_DEBUG_ON := TRUE;
31 END IF;
32 IF L_DEBUG_ON THEN
33 print_debug(L_MODULE_NAME||','||'P_GEN_OBJECT_ID' ||','||P_GEN_OBJECT_ID);
34 END IF;
35 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
36 L_RAISE_EVENT := FALSE;
37 SELECT COUNT(*)
38 INTO L_NO_ENABLED_SUB
39 FROM WF_EVENTS A
40 ,WF_EVENT_SUBSCRIPTIONS B
41 WHERE A.GUID = B.EVENT_FILTER_GUID
42 AND A.NAME = P_EVENT
43 AND B.JAVA_RULE_FUNC ='oracle.apps.inv.opsm.lot.util.InvOpsmLotRule'
44 AND B.STATUS = 'ENABLED'
45 AND B.SOURCE_TYPE = 'LOCAL'
46 AND B.SYSTEM_GUID = HEXTORAW(WF_CORE.TRANSLATE('WF_SYSTEM_GUID')) ;
47 IF L_NO_ENABLED_SUB > 0 THEN
48 L_RAISE_EVENT := TRUE;
49 END IF;
50 IF (L_RAISE_EVENT) THEN
51 WF_EVENT.RAISE(
52 P_EVENT_NAME => P_EVENT
53 ,P_EVENT_KEY => P_GEN_OBJECT_ID
54 ,P_PARAMETERS => P_PARAMETERS
55 ,P_SEND_DATE => P_SEND_DATE);
56 END IF;
57 EXCEPTION
58 WHEN OTHERS THEN
59 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR ;
60 IF L_DEBUG_ON THEN
61 print_debug(L_MODULE_NAME||','||'UNEXPECTED ERROR HAS OCCURED. ORACLE ERROR MESSAGE IS '||SQLERRM);
62 END IF;
63 END RAISE_EVENT;
64 -------------
65 PROCEDURE PROCESS_LOT
66 ( p_inventory_item_id in number
67 ,p_concatenated_segments in varchar2
68 ,p_item_description in varchar2
69 ,p_primary_uom_code in varchar2
70 ,p_lot_control_code in number
71 ,p_serial_number_control_code in number
72 ,p_opsm_integrated_flag in number
73 ,p_organization_id in number
74 ,p_organization_code in varchar2
75 ,p_age in number
76 ,p_best_by_date in date
77 ,p_change_date in date
78 ,p_expiration_date in date default null
79 ,p_grade_code in varchar2 default null
80 ,p_length in number default null
81 ,p_length_uom in varchar2 default null
82 ,p_lot_description in varchar2 default null
83 ,p_lot_number in varchar2
84 ,p_maturity_date in date default null
85 ,p_origination_date in date default null
86 ,p_place_of_origin in varchar2 default null
87 ,p_recycled_content in number default null
88 ,p_retest_date in date default null
89 ,p_thickness in number default null
90 ,p_thickness_uom in varchar2 default null
91 ,p_volume in number default null
92 ,p_volume_uom in varchar2 default null
93 ,p_width in number default null
94 ,p_width_uom in varchar2 default null
95 ,p_expirationactioncode in varchar2 default null
96 ,p_expirationactiondate in date default null
97 ,p_parent_lot_number in varchar2 default null
98 ,p_reservable_type in number
99 ,p_origination_type in number
100 ,p_gen_object_id in number
101 ) IS
102 PRAGMA AUTONOMOUS_TRANSACTION;
103 L_PARAMETERS WF_PARAMETER_LIST_T;
104 L_EVENT VARCHAR2(240) := 'oracle.apps.inv.opsm.syncLot';
105 L_DEBUG_ON BOOLEAN;
106 L_BE_RS VARCHAR2(1);
107 L_MODULE_NAME CONSTANT VARCHAR2(100) := 'INV.PLSQL.'|| G_PKG_NAME ||'.'||'PROCESS_LOT';
108 BEGIN
109 IF (p_inventory_item_id is not null and p_organization_code is not null and p_lot_number is not null) THEN
110 IF (g_debug = 1) THEN
111 L_DEBUG_ON := TRUE;
112 END IF;
113 IF L_DEBUG_ON THEN
114 print_debug(L_MODULE_NAME||','||'ADDING PARAMETERS TO WF_PARAMETER_LIST');
115 END IF;
116 --ITEM
117 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ITEM_ID',
118 P_VALUE => P_INVENTORY_ITEM_ID,
119 P_PARAMETERLIST => L_PARAMETERS);
120 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ITEM',
121 P_VALUE => replace(P_CONCATENATED_SEGMENTS,'^^^^','&'),
122 P_PARAMETERLIST => L_PARAMETERS);
123 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ITEM_DESCRIPTION',
124 P_VALUE => replace(P_ITEM_DESCRIPTION,'^^^^','&'),
125 P_PARAMETERLIST => L_PARAMETERS);
126 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'PRIMARY_UOM_CODE',
127 P_VALUE => P_PRIMARY_UOM_CODE,
128 P_PARAMETERLIST => L_PARAMETERS);
129 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'LOT_CONTROL_CODE',
130 P_VALUE => P_LOT_CONTROL_CODE,
131 P_PARAMETERLIST => L_PARAMETERS);
132 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'SERIAL_CONTROL_CODE',
133 P_VALUE => P_SERIAL_NUMBER_CONTROL_CODE,
134 P_PARAMETERLIST => L_PARAMETERS);
135 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'OPSM_INTEGRATED_FLAG',
136 P_VALUE => P_OPSM_INTEGRATED_FLAG,
137 P_PARAMETERLIST => L_PARAMETERS);
138 --ITEM
139 --org
140 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ORGANIZATION_ID',
141 P_VALUE => P_ORGANIZATION_ID,
142 P_PARAMETERLIST => L_PARAMETERS);
143 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ORGANIZATION_CODE',
144 P_VALUE => P_ORGANIZATION_CODE,
145 P_PARAMETERLIST => L_PARAMETERS);
146 --org
147 --lot
148 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'AGE',
149 P_VALUE => P_AGE,
150 P_PARAMETERLIST => L_PARAMETERS);
151 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'BEST_BY_DATE',
152 P_VALUE => to_char(P_BEST_BY_DATE,'YYYY-MM-DD HH24:MI:SS'),
153 P_PARAMETERLIST => L_PARAMETERS);
154 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'CHANGE_DATE',
155 P_VALUE => to_char(P_CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS'),
156 P_PARAMETERLIST => L_PARAMETERS);
157 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'EXPIRATION_DATE',
158 P_VALUE => to_char(P_EXPIRATION_DATE,'YYYY-MM-DD HH24:MI:SS'),
159 P_PARAMETERLIST => L_PARAMETERS);
160 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'GRADE_CODE',
161 P_VALUE => replace(P_GRADE_CODE,'^^^^','&'),
162 P_PARAMETERLIST => L_PARAMETERS);
163 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'LENGTH',
164 P_VALUE => P_LENGTH,
165 P_PARAMETERLIST => L_PARAMETERS);
166 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'LENGTH_UOM',
167 P_VALUE => P_LENGTH_UOM,
168 P_PARAMETERLIST => L_PARAMETERS);
169 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'LOT_DESCRIPTION',
170 P_VALUE => replace(P_LOT_DESCRIPTION,'^^^^','&'),
171 P_PARAMETERLIST => L_PARAMETERS);
172 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'LOT_NUMBER',
173 P_VALUE => replace(P_LOT_NUMBER,'^^^^','&'),
174 P_PARAMETERLIST => L_PARAMETERS);
175 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'MATURITY_DATE',
176 P_VALUE => to_char(P_MATURITY_DATE,'YYYY-MM-DD HH24:MI:SS'),
177 P_PARAMETERLIST => L_PARAMETERS);
178 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ORIGINATION_DATE',
179 P_VALUE => to_char(P_ORIGINATION_DATE,'YYYY-MM-DD HH24:MI:SS'),
180 P_PARAMETERLIST => L_PARAMETERS);
181 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'PLACE_OF_ORIGIN',
182 P_VALUE => replace(P_PLACE_OF_ORIGIN,'^^^^','&'),
183 P_PARAMETERLIST => L_PARAMETERS);
184 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'RECYCLED_CONTENT',
185 P_VALUE => P_RECYCLED_CONTENT,
186 P_PARAMETERLIST => L_PARAMETERS);
187 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'RETEST_DATE',
188 P_VALUE => to_char(P_RETEST_DATE,'YYYY-MM-DD HH24:MI:SS'),
189 P_PARAMETERLIST => L_PARAMETERS);
190 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'THICKNESS',
191 P_VALUE => P_THICKNESS,
192 P_PARAMETERLIST => L_PARAMETERS);
193 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'THICKNESS_UOM',
194 P_VALUE => P_THICKNESS_UOM,
195 P_PARAMETERLIST => L_PARAMETERS);
196 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'VOLUME',
197 P_VALUE => P_VOLUME,
198 P_PARAMETERLIST => L_PARAMETERS);
199 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'VOLUME_UOM',
200 P_VALUE => P_VOLUME_UOM,
201 P_PARAMETERLIST => L_PARAMETERS);
202 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'WIDTH',
203 P_VALUE => P_WIDTH,
204 P_PARAMETERLIST => L_PARAMETERS);
205 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'WIDTH_UOM',
206 P_VALUE => P_WIDTH_UOM,
207 P_PARAMETERLIST => L_PARAMETERS);
208 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'EXPIRATIONACTIONCODE',
209 P_VALUE => replace(P_EXPIRATIONACTIONCODE,'^^^^','&'),
210 P_PARAMETERLIST => L_PARAMETERS);
211 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'EXPIRATIONACTIONDATE',
212 P_VALUE => to_char(P_EXPIRATIONACTIONDATE,'YYYY-MM-DD HH24:MI:SS'),
213 P_PARAMETERLIST => L_PARAMETERS);
214 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'PARENT_LOT_NUMBER',
215 P_VALUE => replace(P_PARENT_LOT_NUMBER,'^^^^','&'),
216 P_PARAMETERLIST => L_PARAMETERS);
217 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'RESERVABLE_TYPE',
218 P_VALUE => P_RESERVABLE_TYPE,
219 P_PARAMETERLIST => L_PARAMETERS);
220 WF_EVENT.ADDPARAMETERTOLIST (P_NAME => 'ORIGINATION_TYPE',
221 P_VALUE => P_ORIGINATION_TYPE,
222 P_PARAMETERLIST => L_PARAMETERS);
223 --lot
224 IF L_DEBUG_ON THEN
225 print_debug(L_MODULE_NAME||','||'CALLING PROGRAM UNIT RAISE_EVENT');
226 END IF;
227 RAISE_EVENT( P_GEN_OBJECT_ID => P_GEN_OBJECT_ID
228 ,P_EVENT => L_EVENT
229 ,P_PARAMETERS => L_PARAMETERS
230 ,X_RETURN_STATUS => L_BE_RS
231 );
232 COMMIT;
233 IF L_DEBUG_ON THEN
234 print_debug(L_MODULE_NAME||','||'RETURN STATUS AFTER CALLING RAISE_EVENT => '||','||L_BE_RS);
235 END IF;
236 END IF;
237 EXCEPTION
238 WHEN OTHERS THEN
239 IF L_DEBUG_ON THEN
240 print_debug(L_MODULE_NAME||','||'UNEXPECTED ERROR HAS OCCURED. ORACLE ERROR MESSAGE IS '||SQLERRM);
241 END IF;
242 END PROCESS_LOT;
243 -------------
244 END INV_OPSM_LOT_BE_PKG;
245