[Home] [Help]
PACKAGE BODY: APPS.PO_FTE_INTEGRATION_PVT
Source
1 PACKAGE BODY PO_FTE_INTEGRATION_PVT AS
2 /* $Header: POXVFTEB.pls 120.2 2005/10/20 00:58:12 kpsingh noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_FTE_INTEGRATION_PVT';
5 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
6 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8 -------------------------------------------------------------------------------
9 --Start of Comments
10 --Name: get_po_release_attributes
11 --Pre-reqs:
12 -- None.
13 --Modifies:
14 -- None.
15 --Locks:
16 -- None.
17 --Function:
18 -- Get attributes of Standard Purchase Order and Blanket Release for
19 -- Transportation delivery record.
20 --Parameters:
21 --IN:
22 --p_api_version
23 -- Specifies API version.
24 --p_line_location_id
25 -- Corresponding to po_line_location_id
26 --OUT:
27 --x_return_status
28 -- Indicates API return status as 'S', 'E' or 'U'.
29 --x_msg_count
30 -- Error messages number.
31 --x_msg_data
32 -- Error messages body.
33 --x_po_release_attributes
34 --Testing:
35 -- Call this API when only line_location_id exists.
36 --End of Comments
37 -------------------------------------------------------------------------------
38
39
40 PROCEDURE get_po_release_attributes(
41 p_api_version IN NUMBER,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2,
45 p_line_location_id IN NUMBER,
46 x_po_releases_attributes OUT NOCOPY PO_FTE_INTEGRATION_GRP.po_release_rec_type
47 )
48 IS
49 l_api_name CONSTANT VARCHAR2(100) := 'get_po_release_attributes';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_progress VARCHAR2(3) := '001';
52
53 BEGIN
54 IF (g_fnd_debug = 'Y') THEN
55 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
56 FND_LOG.string (
57 log_level => FND_LOG.LEVEL_STATEMENT,
58 module => c_log_head || '.'||l_api_name||'.' || l_progress,
59 message => 'Check API Call Compatibility');
60 END IF;
61 END IF;
62
63 -- Standard call to check for call compatibility.
64 IF NOT FND_API.Compatible_API_Call(
65 p_current_version_number => l_api_version,
66 p_caller_version_number => p_api_version,
67 p_api_name => l_api_name,
68 p_pkg_name => g_pkg_name) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 -- Initialize API return status to success
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 IF (g_fnd_debug = 'Y') THEN
76 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
77 FND_LOG.string (
78 log_level => FND_LOG.LEVEL_STATEMENT,
79 module => c_log_head || '.'||l_api_name||'.' || l_progress,
80 message => 'Query to get x_po_releases_attributes');
81 END IF;
82 END IF;
83
84 --SQL What: Querying data from Standard PO or Blanket Release based on
85 --SQL line_location_id
86 --SQL Where: MUOM1.unit_of_measure(+) = POL.secondary_unit_of_measure
87 --SQL To get secondary_uom_code
88 --SQL Why: Same as SQL What
89
90 SELECT POR.po_release_id,
91 POR.release_num,
92 POR.revision_num,
93 --bug 3633863: removed decode on release_id; always use header_id
94 POH.po_header_id,
95 POH.vendor_id,
96 POH.vendor_site_id,
97 POH.user_hold_flag,
98 POH.freight_terms_lookup_code,
99 POH.fob_lookup_code,
100 POH.segment1,
101 DECODE(POLL.po_release_id, NULL, 1, 2),
102 PDT.type_name,
103 POH.org_id,
104 POH.currency_code,
105 DECODE(POLL.po_release_id, NULL, POH.shipping_control,
106 POR.shipping_control),
107 POH.revision_num,
108 POL.po_line_id,
109 POL.item_id,
110 POL.item_description,
111 POL.hazard_class_id,
112 POL.item_revision,
113 POL.vendor_product_num,
114 POL.line_num,
115 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
116 POLL.country_of_origin_code,
117 POLL.ship_to_location_id,
118 POLL.qty_rcv_tolerance,
119 POLL.receive_close_tolerance,
120 POLL.quantity_shipped,
121 POLL.need_by_date,
122 POLL.promised_date,
123 POLL.ship_to_organization_id,
124 POLL.quantity,
125 MUOM.uom_code,
126 POLL.quantity_cancelled,
127 POLL.price_override,
128 POLL.preferred_grade,
129 POLL.secondary_quantity,
130 MUOM1.uom_code,
131 POLL.secondary_quantity_cancelled,
132 POLL.shipment_num,
133 POLL.days_early_receipt_allowed,
134 POLL.days_late_receipt_allowed,
135 POLL.drop_ship_flag,
136 POLL.qty_rcv_exception_code,
137 POLL.closed_flag,
138 POLL.closed_code,
139 POLL.cancel_flag,
140 POLL.receipt_days_exception_code,
141 POLL.enforce_ship_to_location_code,
142 POLL.sales_order_update_date,
143 FRT.party_id
144 INTO x_po_releases_attributes.source_blanket_reference_id,
145 x_po_releases_attributes.source_blanket_reference_num,
146 x_po_releases_attributes.release_revision,
147 x_po_releases_attributes.header_id,
148 x_po_releases_attributes.vendor_id,
149 x_po_releases_attributes.ship_from_site_id,
150 x_po_releases_attributes.hold_code,
151 x_po_releases_attributes.freight_terms_code,
152 x_po_releases_attributes.fob_point_code,
153 x_po_releases_attributes.source_header_number,
154 x_po_releases_attributes.source_header_type_id,
155 x_po_releases_attributes.source_header_type_name,
156 x_po_releases_attributes.org_id,
157 x_po_releases_attributes.currency_code,
158 x_po_releases_attributes.shipping_control,
159 x_po_releases_attributes.po_revision,
160 x_po_releases_attributes.line_id,
161 x_po_releases_attributes.inventory_item_id,
162 x_po_releases_attributes.item_description,
163 x_po_releases_attributes.hazard_class_id,
164 x_po_releases_attributes.revision,
165 x_po_releases_attributes.supplier_item_num,
166 x_po_releases_attributes.source_line_number,
167 x_po_releases_attributes.source_line_type_code,
168 x_po_releases_attributes.country_of_origin,
169 x_po_releases_attributes.ship_to_location_id,
170 x_po_releases_attributes.ship_tolerance_above,
171 x_po_releases_attributes.ship_tolerance_below,
172 x_po_releases_attributes.shipped_quantity,
173 x_po_releases_attributes.request_date,
174 x_po_releases_attributes.schedule_ship_date,
175 x_po_releases_attributes.organization_id,
176 x_po_releases_attributes.ordered_quantity,
177 x_po_releases_attributes.order_quantity_uom,
178 x_po_releases_attributes.cancelled_quantity,
179 x_po_releases_attributes.unit_list_price,
180 x_po_releases_attributes.preferred_grade,
181 x_po_releases_attributes.ordered_quantity2,
182 x_po_releases_attributes.ordered_quantity_uom2,
183 x_po_releases_attributes.cancelled_quantity2,
184 x_po_releases_attributes.po_shipment_line_number,
185 x_po_releases_attributes.days_early_receipt_allowed,
186 x_po_releases_attributes.days_late_receipt_allowed,
187 x_po_releases_attributes.drop_ship_flag,
188 x_po_releases_attributes.qty_rcv_exception_code,
189 x_po_releases_attributes.closed_flag,
190 x_po_releases_attributes.closed_code,
191 x_po_releases_attributes.cancelled_flag,
192 x_po_releases_attributes.receipt_days_exception_code,
193 x_po_releases_attributes.enforce_ship_to_location_code,
194 x_po_releases_attributes.shipping_details_updated_on,
195 x_po_releases_attributes.carrier_id
196 FROM PO_HEADERS_ALL POH,
197 PO_LINES_ALL POL,
198 PO_LINE_LOCATIONS_ALL POLL,
199 PO_RELEASES_ALL POR,
200 PO_LINE_TYPES_B PLT,
201 MTL_UNITS_OF_MEASURE MUOM,
202 MTL_UNITS_OF_MEASURE MUOM1,
203 ORG_FREIGHT_TL FRT,
204 PO_DOCUMENT_TYPES_ALL_TL PDT -- Bug #4635593
205 WHERE POLL.line_location_id = p_line_location_id
206 AND PDT.document_type_code =
207 DECODE(POLL.po_release_id, NULL, 'PO', 'PA')
208 AND PDT.document_subtype
209 = DECODE(POLL.po_release_id, NULL, POH.type_lookup_code,
210 POR.release_type)
211 AND POL.po_line_id = POLL.po_line_id
212 AND POH.po_header_id = POL.po_header_id
213 AND POR.po_release_id(+) = POLL.po_release_id
214 AND FRT.freight_code (+) = POH.ship_via_lookup_code
215 AND FRT.language (+) = USERENV('LANG')
216 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
217 = POLL.ship_to_organization_id
218 AND POL.line_type_id = PLT.line_type_id
219 AND MUOM.unit_of_measure = POL.unit_meas_lookup_code
220 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
221 AND POH.ORG_ID = PDT.ORG_ID -- Bug #4635593
222 AND PDT.LANGUAGE = USERENV('LANG'); -- Bug #4635593
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 IF (g_fnd_debug = 'Y') THEN
227 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
228 FND_LOG.string( log_level => FND_LOG.LEVEL_EXCEPTION,
229 module => c_log_head || '.'||l_api_name,
230 message => 'unexpected error');
231 END IF;
232 END IF;
233 FND_MSG_PUB.add_exc_msg ( G_PKG_NAME, l_api_name );
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 END get_po_release_attributes;
236
237 END PO_FTE_INTEGRATION_PVT;