[Home] [Help]
PACKAGE BODY: APPS.WMS_SHIPPING_LOVS
Source
1 PACKAGE BODY WMS_SHIPPING_LOVS AS
2 /* $Header: WMSSHPLB.pls 120.3.12010000.5 2009/08/14 14:27:48 pbonthu ship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_SHIPPING_LOVS';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSSHPLB.pls 120.3.12010000.5 2009/08/14 14:27:48 pbonthu ship $';
7
8 PROCEDURE Get_LPN_Order_LOV(
9 x_order_lov OUT NOCOPY t_genref
10 , p_organization_id IN NUMBER
11 , p_parent_delivery_detail_id IN NUMBER
12 , p_order IN VARCHAR2
13 )IS
14
15 BEGIN
16 -- Bug4579790
17
18 -- bug 5515230 separeated query based on if p_order is null or not
19 -- to resolve performance issue when doing an open query (null p_order)
20 IF ( p_order IS NULL OR p_order = '%' ) THEN
21 OPEN x_order_lov FOR
22 SELECT DISTINCT
23 wdd.source_header_number
24 , wdd.source_header_id
25 , otl.name
26 , wdd.source_header_type_id
27 , party.party_name --c.customer_name
28 , party.party_id --c.customer_id
29 , party.party_number--c.customer_number
30 FROM wsh_delivery_details wdd
31 , hz_parties party --ra_customers c
32 , hz_cust_accounts cust_acct
33 , oe_transaction_types_tl otl
34 , wsh_delivery_assignments_v wda
35 WHERE wdd.customer_id = party.party_id
36 --c.customer_id
37 AND cust_acct.party_id = party.party_id
38 AND otl.language=userenv('LANG')
39 AND otl.transaction_type_id = wdd.source_header_type_id
40 AND wdd.organization_id = p_organization_id
41 AND wdd.source_code = 'OE'
42 AND wdd.date_scheduled is not null
43 --AND wdd.released_status in ('B','R','X')
44 AND wda.delivery_detail_id = wdd.delivery_detail_id
45 AND wda.parent_delivery_detail_id = p_parent_delivery_detail_id
46 ORDER BY 2,1;
47 ELSE
48 OPEN x_order_lov FOR
49 SELECT DISTINCT
50 wdd.source_header_number
51 , wdd.source_header_id
52 , otl.name
53 , wdd.source_header_type_id
54 , party.party_name --c.customer_name
55 , party.party_id --c.customer_id
56 , party.party_number--c.customer_number
57 FROM wsh_delivery_details wdd
58 , hz_parties party --ra_customers c
59 , hz_cust_accounts cust_acct
60 , oe_transaction_types_tl otl
61 , wsh_delivery_assignments_v wda
62 WHERE wdd.customer_id = party.party_id
63 --c.customer_id
64 AND cust_acct.party_id = party.party_id
65 AND otl.language=userenv('LANG')
66 AND wdd.source_header_number like (p_order)
67 AND otl.transaction_type_id = wdd.source_header_type_id
68 AND wdd.organization_id = p_organization_id
69 AND wdd.source_code = 'OE'
70 AND wdd.date_scheduled is not null
71 --AND wdd.released_status in ('B','R','X')
72 AND wda.delivery_detail_id = wdd.delivery_detail_id
73 AND wda.parent_delivery_detail_id = p_parent_delivery_detail_id
74 ORDER BY 2,1;
75 END IF;
76 END Get_LPN_Order_LOV;
77
78 PROCEDURE Get_LPN_Orderline_LOV(
79 x_orderline_lov OUT NOCOPY T_GENREF
80 , p_organization_id IN NUMBER
81 , p_source_header_id IN NUMBER
82 , p_parent_delivery_detail_id IN NUMBER
83 , p_order_line IN VARCHAR2
84 ) IS
85 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
86 BEGIN
87 OPEN x_orderline_lov FOR
88 SELECT DISTINCT
89 oel.line_id
90 , to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||
91 decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
92 decode(oel.component_number, null, null,decode(oel.option_number, null,'.',null)||
93 '.'||to_char(oel.component_number)) LINE_NUMBER
94 , oel.inventory_item_id
95 , oel.item_revision
96 , oel.PROJECT_ID
97 , oel.TASK_ID
98 , oel.END_ITEM_UNIT_NUMBER
99 , oel.SHIP_TOLERANCE_ABOVE
100 , oel.ship_tolerance_below
101 , oel.FLOW_STATUS_CODE
102 , oel.SHIPPING_INTERFACED_FLAG
103 , oel.REQUEST_DATE
104 , msik.serial_number_control_code
105 , msik.concatenated_segments
106 , 0
107 , ''
108 , 0
109 , 0
110 , 0
111 , 0
112 , ''
113 , ''
114 , ''
115 , ''
116 , 0
117 FROM oe_order_lines_all oel
118 , mtl_system_items_kfv msik
119 , wsh_delivery_details wdd
120 , wsh_delivery_assignments_v wda
121 WHERE oel.ship_from_org_id = p_organization_id
122 AND oel.header_id = p_source_header_id
123 AND oel.item_type_code in ('STANDARD','CONFIG','INCLUDED','OPTION')
124 AND wda.parent_delivery_detail_id = p_parent_delivery_detail_id
125 AND wdd.delivery_detail_id = wda.delivery_detail_id
126 AND oel.line_id = wdd.source_line_id
127 AND msik.inventory_item_id = oel.inventory_item_id
128 AND msik.organization_id = oel.ship_from_org_id
129 AND msik.mtl_transactions_enabled_flag <> 'N'
130 AND to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||
131 decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number))||
132 decode(oel.component_number, null, null,decode(oel.option_number, null, '.',null)||
133 '.'||to_char(oel.component_number)) like (p_order_line)
134 ORDER BY 1,2;
135 END Get_LPN_Orderline_LOV;
136
137 --Added for Case Picking Project start (8732301)
138
139
140
141 PROCEDURE Get_Manifest_Order_LOV( x_orderline_lov OUT NOCOPY T_GENREF ,
142 p_organization_id IN NUMBER ,
143 p_order_number IN VARCHAR2,
144 p_equipment_id IN NUMBER := NULL,
145 p_sign_on_emp_id IN NUMBER,
146 p_zone IN VARCHAR2 := NULL) IS
147 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
148 BEGIN
149
150 OPEN x_orderline_lov FOR
151 SELECT DISTINCT oeh.order_number
152 FROM oe_order_headers_all oeh ,
153 mtl_sales_orders mso ,
154 mtl_material_transactions_temp mmtt,
155 -- inlined wms_person_resource_utt_v
156 (SELECT utt_emp.standard_operation_id standard_operation_id,
157 utt_emp.person_id emp_id ,
158 utt_eqp.inventory_item_id eqp_id
159 FROM
160 (SELECT x_utt_res1.standard_operation_id standard_operation_id,
161 x_emp_r.person_id
162 FROM bom_std_op_resources x_utt_res1,
163 bom_resources r1 ,
164 bom_resource_employees x_emp_r
165 WHERE x_utt_res1.resource_id = r1.resource_id
166 AND r1.resource_type = 2
167 AND x_utt_res1.resource_id = x_emp_r.resource_id
168 ) utt_emp ,
169 (SELECT x_utt_res2.standard_operation_id standard_operation_id,
170 x_eqp_r.inventory_item_id
171 FROM bom_std_op_resources x_utt_res2,
172 bom_resources r2 ,
173 bom_resource_equipments x_eqp_r
174 WHERE x_utt_res2.resource_id = r2.resource_id
175 AND r2.resource_type = 1
176 AND x_utt_res2.resource_id = x_eqp_r.resource_id
177 ) utt_eqp
178 WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
179 ) v ,
180 mtl_secondary_inventories sub
181 WHERE mmtt.organization_id = p_organization_id
182 AND oeh.order_number LIKE (p_order_number)
183 AND mmtt.transaction_source_id = mso.sales_order_id
184 AND mso.segment1 = oeh.order_number
185 AND oeh.order_number NOT IN ( SELECT * FROM TABLE(WMS_PICKING_PKG.list_order_numbers) )
186 AND v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
187 AND mmtt.standard_operation_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
188 AND mmtt.subinventory_code = NVL(p_zone, mmtt.subinventory_code)
189 AND NVL(v.eqp_id, -999) = NVL(p_equipment_id, NVL(v.eqp_id, -999))
190 AND mmtt.subinventory_code = sub.secondary_inventory_name
191 AND mmtt.organization_id = sub.organization_id
192 AND (mmtt.wms_task_status <> 8 OR mmtt.wms_task_status IS NULL )
193 AND NOT EXISTS ( SELECT 1
194 FROM wms_dispatched_tasks wdt
195 WHERE mmtt.transaction_temp_id=wdt.transaction_temp_id
196 AND ( wdt.status > 3 OR v.emp_id <> wdt.person_id )
197 )
198 ORDER BY 1;
199
200 END Get_Manifest_Order_LOV;
201
202 PROCEDURE Get_Manifest_Pickslip_LOV( x_pickslip_lov OUT NOCOPY T_GENREF ,
203 p_organization_id IN NUMBER ,
204 p_pick_slip_number IN VARCHAR2 ,
205 p_equipment_id IN NUMBER := NULL,
206 p_sign_on_emp_id IN NUMBER,
207 p_zone IN VARCHAR2 := NULL ) IS
208 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
209 BEGIN
210
211 OPEN x_pickslip_lov FOR
212 SELECT DISTINCT mmtt.pick_slip_number
213 FROM mtl_material_transactions_temp mmtt,
214 -- inlined wms_person_resource_utt_v
215 (SELECT utt_emp.standard_operation_id standard_operation_id,
216 utt_emp.person_id emp_id ,
217 utt_eqp.inventory_item_id eqp_id
218 FROM
219 (SELECT x_utt_res1.standard_operation_id standard_operation_id,
220 x_emp_r.person_id
221 FROM bom_std_op_resources x_utt_res1,
222 bom_resources r1 ,
223 bom_resource_employees x_emp_r
224 WHERE x_utt_res1.resource_id = r1.resource_id
225 AND r1.resource_type = 2
226 AND x_utt_res1.resource_id = x_emp_r.resource_id
227 ) utt_emp ,
228 (SELECT x_utt_res2.standard_operation_id standard_operation_id,
229 x_eqp_r.inventory_item_id
230 FROM bom_std_op_resources x_utt_res2,
231 bom_resources r2 ,
232 bom_resource_equipments x_eqp_r
233 WHERE x_utt_res2.resource_id = r2.resource_id
234 AND r2.resource_type = 1
235 AND x_utt_res2.resource_id = x_eqp_r.resource_id
236 ) utt_eqp
237 WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
238 ) v ,
239 mtl_secondary_inventories sub
240 WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
241 AND mmtt.organization_id = p_organization_id
242 AND mmtt.standard_operation_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
243 AND mmtt.subinventory_code = NVL(p_zone, mmtt.subinventory_code)
244 AND NVL(v.eqp_id, -999) = NVL(p_equipment_id, NVL(v.eqp_id, -999))
245 AND mmtt.subinventory_code = sub.secondary_inventory_name
246 AND mmtt.organization_id = sub.organization_id
247 AND mmtt.parent_line_id IS NULL -- Added for bulk task
248 AND mmtt.pick_slip_number LIKE (p_pick_slip_number)
249 AND mmtt.pick_slip_number NOT IN ( SELECT * FROM TABLE(WMS_PICKING_PKG.list_pick_slip_numbers) )
250 AND (mmtt.wms_task_status <> 8 OR mmtt.wms_task_status IS NULL )
251 AND mmtt.transaction_action_id = 28
252 AND mmtt.transaction_source_type_id IN (2,8)
253 AND NOT EXISTS ( SELECT 1
254 FROM wms_dispatched_tasks wdt
255 WHERE mmtt.transaction_temp_id=wdt.transaction_temp_id
256 AND ( wdt.status > 3 OR v.emp_id <> wdt.person_id )
257 )
258 ORDER BY 1;
259 END Get_Manifest_Pickslip_LOV;
260
261
262 --Added for Case Picking Project end (8732301)
263
264
265
266 END WMS_SHIPPING_LOVS;