1 PACKAGE BODY wma_derive AS
2 /* $Header: wmacdrvb.pls 120.0 2005/05/25 07:40:34 appldev noship $ */
3
4 FUNCTION getTxnMode (orgID IN NUMBER) return NUMBER IS
5 txnMode NUMBER;
6 begin
7
8 select nvl(mobile_transaction_mode, wip_constants.background)
9 into txnMode
10 from wip_parameters
11 where organization_id = orgID;
12
13 return txnMode;
14 exception
15 when others then
16 return wip_constants.background;
17 end getTxnMode;
18
19 /**
20 * returns the next value in a database sequence
21 */
22 FUNCTION getNextVal (sequence IN VARCHAR2) return NUMBER is
23 nextVal NUMBER;
24
25 BEGIN
26 EXECUTE IMMEDIATE 'select ' || sequence || '.nextval from dual'
27 INTO nextVal;
28
29 return nextVal;
30 END getNextVal;
31
32
33 /**
34 * given an itemID, getItem populates the wma_common.Item structure
35 * with the item information.
36 */
37 FUNCTION getItem (
38 itemID IN NUMBER,
39 orgID IN NUMBER) return wma_common.Item
40 IS
41 item wma_common.Item;
42
43 cursor getItemInfo (itemID NUMBER, orgID NUMBER) IS
44 select msikfv.inventory_item_id,
45 msikfv.concatenated_segments,
46 msikfv.description,
47 msikfv.organization_id,
48 msikfv.primary_uom_code,
49 msikfv.lot_control_code,
50 msikfv.auto_lot_alpha_prefix,
51 msikfv.start_auto_lot_number,
52 msikfv.serial_number_control_code,
53 msikfv.auto_serial_alpha_prefix,
54 msikfv.start_auto_serial_number,
55 msikfv.location_control_code,
56 msikfv.revision_qty_control_code,
57 msikfv.restrict_locators_code,
58 msikfv.restrict_subinventories_code,
59 msikfv.shelf_life_code,
60 msikfv.shelf_life_days,
61 msikfv.inventory_asset_flag,
62 msikfv.allowed_units_lookup_code,
63 msikfv.mtl_transactions_enabled_flag,
64 null, -- projectID, assume locator is not available
65 null -- taskID, assume locator is not available
66 from mtl_system_items_kfv msikfv
67 where msikfv.inventory_item_id = itemID
68 and msikfv.organization_id = orgID;
69
70 BEGIN
71 open getItemInfo (itemID, orgID);
72 fetch getItemInfo into item;
73 if (getItemInfo%NOTFOUND) then
74 item.invItemID := null;
75 end if;
76 close getItemInfo;
77
78 return item;
79
80 END getItem;
81
82
83 /**
84 * given an itemID and a locatorID, getItem populates the
85 * wma_common.Item structure with the item information. Calling this
86 * version of getItem fills in the projectID and taskID fields of
87 * wma_common.Item
88 */
89 FUNCTION getItem (
90 itemID IN NUMBER,
91 orgID IN NUMBER,
92 locatorID IN NUMBER) return wma_common.Item
93 IS
94 item wma_common.Item;
95
96 cursor getItemInfo (orgID NUMBER, locatorID NUMBER) IS
97 select mil.project_id,
98 mil.task_id
99 from mtl_item_locations mil
100 where inventory_location_id = locatorID
101 and organization_id = orgID;
102
103 BEGIN
104 -- get most of the item information
105 item := getItem (itemID, orgID);
106
107 -- get the extra location information
108 open getItemInfo (orgID, locatorID);
109 fetch getItemInfo into item.projectID, item.taskID;
110 if (getItemInfo%NOTFOUND) then -- location information does not exist
111 item.projectID := null;
112 item.taskID := null;
113 end if;
114
115 return item;
116
117 END getItem;
118
119
120 /**
121 * getJob will fill out the structure wma_common.Job given the
122 * wipEntityID. Note that the given wipEntityID should be connected
123 * to a discrete job instead of a repetitive schedule.
124 *
125 * HISTORY:
126 * 30-DEC-2004 spondalu Bug 4093569: eAM-WMS Integration enhancements: Relaxed
127 * entity_type condition to include eAM jobs.
128 */
129 Function getJob(wipEntityID NUMBER) return wma_common.Job IS
130 jobInfo wma_common.Job;
131
132 cursor theJob (wipEntityID NUMBER) IS
133 select wdj.wip_entity_id,
134 wdj.organization_id,
135 wen.wip_entity_name,
136 wdj.job_type,
137 wdj.description,
138 wdj.primary_item_id,
139 wdj.status_type,
140 wdj.wip_supply_type,
141 wdj.line_id,
142 wl.line_code,
143 wdj.scheduled_start_date,
144 wdj.scheduled_completion_date,
145 wdj.start_quantity,
146 wdj.quantity_completed,
147 wdj.quantity_scrapped,
148 wdj.completion_subinventory,
149 wdj.completion_locator_id,
150 wdj.project_id,
151 wdj.task_id,
152 wdj.end_item_unit_number
153 from wip_discrete_jobs wdj,
154 wip_lines wl,
155 wip_entities wen
156 where wdj.wip_entity_id = wipEntityID
157 AND wen.wip_entity_id = wipEntityID
158 AND wen.entity_type in (WIP_CONSTANTS.DISCRETE,
159 WIP_CONSTANTS.EAM)
160 AND wl.line_id (+)= wdj.line_id
161 AND wl.organization_id (+) = wdj.organization_id;
162
163 BEGIN
164 open theJob(wipEntityID);
165 fetch theJob into jobInfo;
166 if (theJob%NOTFOUND) then
167 jobInfo.wipEntityID := null;
168 end if;
169 close theJob;
170
171 return jobInfo;
172
173 End getJob;
174
175 /**
176 * given an Environment partially filled (the ID's must be filled),
177 * derive the rest of the structure as possible depending on the
178 * information available to it.
179 */
180 PROCEDURE deriveEnvironment (
181 environment IN OUT NOCOPY wma_common.Environment)
182 IS
183 userName varchar2(100);
184 orgCode varchar2(4);
185
186 cursor getUser (userID NUMBER) IS
187 select user_name from fnd_user
188 where user_id = userID;
189
190 cursor getOrg (orgID NUMBER) IS
191 select organization_code from mtl_parameters
192 where organization_id = orgID;
193 BEGIN
194 -- check that all ID's are not missing
195 if ( environment.userID is null OR
196 environment.orgID is null ) then
197 raise FND_API.G_EXC_UNEXPECTED_ERROR;
198 end if;
199
200 -- derive the user name from userID
201 open getUser(environment.userID);
202 fetch getUser into userName;
203 close getUser;
204
205 -- derive the org code from orgID
206 open getOrg(environment.orgID);
207 fetch getOrg into orgCode;
208 close getOrg;
209
210 -- assign them
211 environment.userName := userName;
212 environment.orgCode := orgCode;
213
214 END deriveEnvironment;
215
216 END wma_derive;
[Home] [Help]