[Home] [Help]
PACKAGE BODY: APPS.WSH_DOCUMENT_PUB
Source
1 PACKAGE BODY WSH_Document_PUB AS
2 -- $Header: WSHPPACB.pls 120.1 2006/01/18 13:47:47 parkhj noship $
3
4 --------------------
5 -- TYPE DECLARATIONS
6 --------------------
7
8 ------------
9 -- CONSTANTS
10 ------------
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_Document_PUB';
13
14 -------------------
15 -- PUBLIC VARIABLES
16 -------------------
17
18 ---------------------------
19 -- PROCEDURES AND FUNCTIONS
20 ---------------------------
21
22 ------------------------------------------------------------------------------
23 -- PROCEDURE : Get_Document PUBLIC
24 -- VERSION : 1.0
25 -- COMMENT : Returns as an out-param a record containing all attributes
26 -- of the the currently open document of the specified type
27 -- ( packing slip, bill of lading, etc.) for a OE order line.
28 --
29 -- PARAMETER LIST :
30 --
31 -- IN
32 --
33 -- p_api_version known API version
34 -- p_init_msg_list should API reset message stack (default: false)
35 -- p_commit should API do a commit (default: false)
36 -- p_validation_level extent of validation done in the API (not used)
37 -- p_order_line_id OE order line id for which doc info is needed
38 -- p_document_type type codes (PACK_TYPE, BOL, ASN, etc.)
39
40 -- OUT
41 --
42 -- x_msg_count number of messages in stack
43 -- x_msg_data message if there is only one message in stack
44 -- x_return_status API return status ('S', 'E', 'U')
45 -- x_document_rec record that contains all attributes of the doc
46 --
47 -- PRE-CONDITIONS : There should be only one open document of a specific
48 -- type for a delivery. If more than one open doc is
49 -- available the API returns the first available one.
50 -- POST-CONDITIONS : None
51 -- EXCEPTIONS : None
52 ------------------------------------------------------------------------------
53
54 PROCEDURE Get_Document
55 ( p_api_version IN NUMBER
56 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_false
57 , p_commit IN VARCHAR2 DEFAULT FND_API.g_false
58 , p_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full
59 , x_return_status OUT NOCOPY VARCHAR2
60 , x_msg_count OUT NOCOPY NUMBER
61 , x_msg_data OUT NOCOPY VARCHAR2
62 , p_order_line_id IN NUMBER
63 , p_document_type IN VARCHAR2
64 , x_document_rec OUT NOCOPY document_rectype
65 )
66 IS
67
68 -- bug 4891939, sql 15038246
69 -- Merge Join Cartesian between wsh_document_instances and
70 -- wsh_delivery_details due to decode in join condition.
71 -- document_csr is divided into two cursors document_csr_del
72 -- and document_csr_leg
73
74 CURSOR document_csr_del IS
75 SELECT
76 doc.document_instance_id
77 , doc.document_type
78 , doc.entity_name
79 , doc.entity_id
80 , doc.doc_sequence_category_id
81 , doc.sequence_number
82 , doc.status
83 , doc.final_print_date
84 /* Commented for Shipping Data Model Changes Bug#1918342
85 , doc.pod_flag
86 , doc.pod_by
87 , doc.pod_date
88 , doc.reason_of_transport
89 , doc.description
90 , doc.cod_amount
91 , doc.cod_currency_code
92 , doc.cod_remit_to
93 , doc.cod_charge_paid_by
94 , doc.problem_contact_reference
95 , doc.bill_freight_to
96 , doc.carried_by
97 , doc.port_of_loading
98 , doc.port_of_discharge
99 , doc.booking_office
100 , doc.booking_number
101 , doc.service_contract
102 , doc.shipper_export_ref
103 , doc.carrier_export_ref
104 , doc.bol_notify_party
105 , doc.supplier_code
106 , doc.aetc_number
107 , doc.shipper_signed_by
108 , doc.shipper_date
109 , doc.carrier_signed_by
110 , doc.carrier_date
111 , doc.bol_issue_office
112 , doc.bol_issued_by
113 , doc.bol_date_issued
114 , doc.shipper_hm_by
115 , doc.shipper_hm_date
116 , doc.carrier_hm_by
117 , doc.carrier_hm_date*/
118 , doc.created_by
119 , doc.creation_date
120 , doc.last_updated_by
121 , doc.last_update_date
122 , doc.last_update_login
123 , doc.program_application_id
124 , doc.program_id
125 , doc.program_update_date
126 , doc.request_id
127 , doc.attribute_category
128 , doc.attribute1
129 , doc.attribute2
130 , doc.attribute3
131 , doc.attribute4
132 , doc.attribute5
133 , doc.attribute6
134 , doc.attribute7
135 , doc.attribute8
136 , doc.attribute9
137 , doc.attribute10
138 , doc.attribute11
139 , doc.attribute12
140 , doc.attribute13
141 , doc.attribute14
142 , doc.attribute15
143 FROM
144 wsh_document_instances doc
145 , wsh_delivery_details det
146 , wsh_delivery_assignments_v del
147 WHERE det.source_line_id = p_order_line_id
148 AND det.delivery_detail_id = del.delivery_detail_id
149 and det.container_flag = 'N'
150 AND doc.entity_id = del.delivery_id
151 AND doc.entity_name = 'WSH_NEW_DELIVERIES'
152 AND nvl(det.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
153 AND doc.status = 'OPEN';
154
155 CURSOR document_csr_leg IS
156 SELECT
157 doc.document_instance_id
158 , doc.document_type
159 , doc.entity_name
160 , doc.entity_id
161 , doc.doc_sequence_category_id
162 , doc.sequence_number
163 , doc.status
164 , doc.final_print_date
165 /* Commented for Shipping Data Model Changes Bug#1918342
166 , doc.pod_flag
167 , doc.pod_by
168 , doc.pod_date
169 , doc.reason_of_transport
170 , doc.description
171 , doc.cod_amount
172 , doc.cod_currency_code
173 , doc.cod_remit_to
174 , doc.cod_charge_paid_by
175 , doc.problem_contact_reference
176 , doc.bill_freight_to
177 , doc.carried_by
178 , doc.port_of_loading
179 , doc.port_of_discharge
180 , doc.booking_office
181 , doc.booking_number
182 , doc.service_contract
183 , doc.shipper_export_ref
184 , doc.carrier_export_ref
185 , doc.bol_notify_party
186 , doc.supplier_code
187 , doc.aetc_number
188 , doc.shipper_signed_by
189 , doc.shipper_date
190 , doc.carrier_signed_by
191 , doc.carrier_date
192 , doc.bol_issue_office
193 , doc.bol_issued_by
194 , doc.bol_date_issued
195 , doc.shipper_hm_by
196 , doc.shipper_hm_date
197 , doc.carrier_hm_by
198 , doc.carrier_hm_date*/
199 , doc.created_by
200 , doc.creation_date
201 , doc.last_updated_by
202 , doc.last_update_date
203 , doc.last_update_login
204 , doc.program_application_id
205 , doc.program_id
206 , doc.program_update_date
207 , doc.request_id
208 , doc.attribute_category
209 , doc.attribute1
210 , doc.attribute2
211 , doc.attribute3
212 , doc.attribute4
213 , doc.attribute5
214 , doc.attribute6
215 , doc.attribute7
216 , doc.attribute8
217 , doc.attribute9
218 , doc.attribute10
219 , doc.attribute11
220 , doc.attribute12
221 , doc.attribute13
222 , doc.attribute14
223 , doc.attribute15
224 FROM
225 wsh_document_instances doc
226 , wsh_delivery_details det
227 , wsh_delivery_assignments_v del
228 , wsh_delivery_legs leg
229 WHERE det.source_line_id = p_order_line_id
230 AND det.delivery_detail_id = del.delivery_detail_id
231 and det.container_flag = 'N'
232 AND del.delivery_id = leg.delivery_id
233 AND doc.entity_id = leg.delivery_leg_id
234 AND doc.entity_name = 'WSH_DELIVERY_LEGS'
235 AND nvl(det.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics jckwok
236 AND doc.status = 'OPEN';
237
238 l_api_name CONSTANT VARCHAR2(30) := 'Get_Document';
239 l_api_version CONSTANT NUMBER := 1.0;
240
241 BEGIN
242 -- standard call to check for call compatibility
243 IF NOT FND_API.Compatible_API_Call ( l_api_version
244 , p_api_version
245 , l_api_name
246 , g_pkg_name ) THEN
247 RAISE FND_API.g_exc_unexpected_error;
248 END IF;
249
250 -- initialize message list if p_init_msg_list is set to TRUE
251 IF FND_API.to_boolean ( p_init_msg_list ) THEN
252 FND_MSG_PUB.initialize;
253 END IF;
254
255 -- initialize API return status to success
256 x_return_status := WSH_UTIL_CORE.g_ret_sts_success;
257
258 IF p_document_type = 'PACK_TYPE' THEN
259 OPEN document_csr_del;
260 FETCH document_csr_del INTO x_document_rec;
261 CLOSE document_csr_del;
262 ELSIF p_document_type = 'BOL' THEN
263 OPEN document_csr_leg;
264 FETCH document_csr_leg INTO x_document_rec;
265 CLOSE document_csr_leg;
266 END IF;
267
268 EXCEPTION
269 WHEN FND_API.g_exc_error THEN
270 x_return_status := WSH_UTIL_CORE.g_ret_sts_error;
271 FND_MSG_PUB.count_and_get ( p_count => x_msg_count
272 , p_data => x_msg_data );
273 IF document_csr_del%ISOPEN THEN
274 CLOSE document_csr_del;
275 END IF;
276 IF document_csr_leg%ISOPEN THEN
277 CLOSE document_csr_leg;
278 END IF;
279
280 WHEN FND_API.g_exc_unexpected_error THEN
281 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
282 FND_MSG_PUB.count_and_get ( p_count => x_msg_count
283 , p_data => x_msg_data );
284 IF document_csr_del%ISOPEN THEN
285 CLOSE document_csr_del;
286 END IF;
287 IF document_csr_leg%ISOPEN THEN
288 CLOSE document_csr_leg;
289 END IF;
290
291 WHEN others THEN
292 FND_MESSAGE.set_name('WSH', 'WSH_UNEXP_ERROR');
293 FND_MESSAGE.set_token('PACKAGE',g_pkg_name);
294 FND_MESSAGE.set_token('ORA_ERROR',to_char(sqlcode));
295 FND_MESSAGE.set_token('ORA_TEXT','Failure in performing action');
296 x_return_status := WSH_UTIL_CORE.g_ret_sts_unexp_error;
297 WSH_UTIL_CORE.add_message (x_return_status);
298 IF FND_MSG_PUB.check_msg_level THEN
299 FND_MSG_PUB.count_and_get ( p_count => x_msg_count
300 , p_data => x_msg_data );
301 END IF;
302
303 IF document_csr_del%ISOPEN THEN
304 CLOSE document_csr_del;
305 END IF;
306 IF document_csr_leg%ISOPEN THEN
307 CLOSE document_csr_leg;
308 END IF;
309 END Get_Document;
310
311 END WSH_Document_PUB;