[Home] [Help]
PACKAGE BODY: APPS.WMS_SHIPPING_LOVS
Source
1 PACKAGE BODY WMS_SHIPPING_LOVS AS
2 /* $Header: WMSSHPLB.pls 120.3 2006/09/20 20:27:10 jsheu noship $ */
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 2006/09/20 20:27:10 jsheu noship $';
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 END WMS_SHIPPING_LOVS;