[Home] [Help]
PACKAGE BODY: APPS.OE_DROP_SHIP_PVT
Source
1 PACKAGE BODY OE_DROP_SHIP_PVT as
2 /* $Header: OEXVDPVB.pls 115.1 99/07/16 08:16:48 porting shi $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_DROP_SHIP_PVT';
5
6 FUNCTION get_po_status
7 (
8 p_po_header_id IN NUMBER
9 ) return VARCHAR2
10 IS
11 l_status VARCHAR2(25);
12 BEGIN
13 l_status := po_headers_sv3.get_po_status(p_po_header_id);
14 return(l_status);
15 EXCEPTION
16 WHEN OTHERS THEN
17 return(NULL);
18 END get_po_status;
19
20 FUNCTION get_release_status
21 (
22 p_po_release_id IN NUMBER
23 ) return VARCHAR2
24 IS
25 l_status VARCHAR2(25);
26 BEGIN
27 l_status := po_releases_sv2.get_release_status(p_po_release_id);
28 return(l_status);
29 EXCEPTION
30 WHEN OTHERS THEN
31 return(NULL);
32 END get_release_status;
33
34 Function COMPARE_PO_SO ( p_so_location number,
35 p_po_location number,
36 p_so_unit_of_measure varchar2,
37 p_po_unit_of_measure varchar2,
38 p_so_schedule_date date,
39 p_po_schedule_date date,
40 p_so_ordered_qty number,
41 p_so_cancelled_qty number,
42 p_so_shipped_quantity number,
43 p_potableused varchar2,
44 p_headerid number,
45 p_lineid number) return varchar2 is
46 soqty number;
47 poqty number;
48 hold_exists varchar2(1);
49 po_hold_exists varchar2(1) := 'N';
50
51
52 begin
53
54
55 soqty := p_so_ordered_qty - p_so_cancelled_qty - p_so_shipped_quantity;
56
57 if p_potableused = 'po_line_locations' then
58 begin
59 select sum(nvl(poll.quantity,0) - nvl(poll.quantity_cancelled,0) -
60 nvl(poll.quantity_shipped,0))
61 into
62 poqty
63 from po_line_locations poll,
64 so_drop_ship_sources sodss
65 where sodss.line_id = p_lineid
66 and poll.line_location_id = sodss.line_location_id
67 and sodss.header_id = p_headerid
68 and nvl(poll.closed_code, '*') <> 'FINALLY CLOSED';
69
70
71 exception
72 when no_data_found then return null;
73 end;
74
75 begin
76 select user_hold_flag into po_hold_exists
77 from po_headers poh,
78 so_drop_ship_sources sodss
79 where
80 sodss.header_id = p_headerid
81 and sodss.line_id = p_lineid
82 and sodss.po_header_id = poh.po_header_id;
83 exception
84 when no_data_found then po_hold_exists := 'N';
85 end;
86
87
88 elsif p_potableused = 'po_requisition_lines' then
89 begin
90 select sum(nvl(prl.quantity,0) - nvl(prl.quantity_cancelled,0)
91 - nvl(prl.quantity_delivered,0))
92 into
93 poqty
94 from po_requisition_lines prl,
95 so_drop_ship_sources sodss
96 where sodss.line_id = p_lineid
97 and prl.requisition_line_id = sodss.requisition_line_id
98 and sodss.header_id = p_headerid
99 and nvl(prl.closed_code,'*') <> 'FINALLY CLOSED';
100
101 exception
102 when no_data_found then return null;
103 end;
104
105 else return null;
106
107 end if;
108
109
110
111
112
113 begin
114
115 select 'Y' into hold_exists
116 from so_order_holds sohlds
117 where ((sohlds.header_id = p_headerid)
118 or (sohlds.line_id = p_lineid))
119 and sohlds.hold_release_id is null;
120
121 exception
122 when no_data_found then hold_exists := 'N';
123 end;
124
125
126
127 if ((nvl(p_so_location, '*') <> nvl(p_po_location, '*')) or
128 (nvl(p_so_unit_of_measure, '*') <> nvl(p_po_unit_of_measure, '*')) or
129 (nvl(p_so_schedule_date, sysdate) <> nvl(p_po_schedule_date, sysdate)) or
130 (hold_exists = 'Y') or
131 (nvl(soqty, 0) <> nvl(poqty, 0))
132 or (po_hold_exists = 'Y') )
133 then return 'Y';
134
135 else return 'N';
136
137 end if;
138
139 end COMPARE_PO_SO;
140
141 Function get_hold_name(p_holdid in number) return varchar2 is
142 holdname varchar2(30) := '';
143 begin
144
145 if p_holdid is null then return '';
146 end if;
147
148 begin
149 select name into holdname
150 from so_holds
151 where hold_id = p_holdid;
152
153 exception
154 when no_data_found then return null;
155 end;
156
157 return holdname;
158
159 end get_hold_name;
160
161 PROCEDURE recover_schedule (P_API_Version In Number,
162 P_Return_Status Out Varchar2,
163 P_Msg_Count Out Number,
164 P_MSG_Data Out Varchar2,
165 p_line_id IN NUMBER)
166 AS
167 v_schedule_qty NUMBER := 0;
168 v_detail_id NUMBER := -1;
169
170 CURSOR details IS
171 SELECT line_detail_id
172 FROM so_line_details
173 WHERE line_id = p_line_id;
174
175 L_API_Name Constant Varchar2(30) := 'GET_DROP_SHIP_PVT';
176 L_API_Version Constant Number := 1.0;
177
178 BEGIN
179
180 IF Not FND_API.Compatible_API_Call (L_API_Version,
181 P_API_Version,
182 L_API_Name,
183 G_PKG_Name) Then
184 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
185 End If;
186
187 OPEN details;
188 FETCH details INTO v_detail_id;
189 CLOSE details;
190
191 Begin
192 SELECT ordered_quantity
193 INTO v_schedule_qty
194 FROM so_lines_all
195 WHERE line_id = p_line_id;
196
197 exception
198 when no_data_found then P_Return_Status := FND_API.G_RET_STS_ERROR;
199 Return;
200 End;
201
202 Begin
203
204 UPDATE so_line_details
205 SET quantity = v_schedule_qty,
206 revision = NULL,
207 lot_number = NULL,
208 subinventory = NULL,
209 demand_class_code = NULL,
210 schedule_status_code = NULL,
211 receipt_status_code = NULL
212 WHERE line_detail_id = v_detail_id;
213
214 Exception
215 When no_data_found then P_Return_Status := FND_API.G_RET_STS_ERROR;
216 Return;
217 End;
218
219 Begin
220
221 DELETE FROM so_line_details
222 WHERE line_id = p_line_id
223 AND line_detail_id <> v_detail_id;
224 Exception
225 When no_data_found then null;
226 End;
227
228 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
229
230
231 END recover_schedule;
232
233 END OE_DROP_SHIP_PVT;