1 PACKAGE BODY po_req_template_sv2 AS
2 /* $Header: POXRQT2B.pls 115.9 2003/10/21 20:47:18 nipagarw ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: get_req_line_info
7
8 ===========================================================================*/
9
10 PROCEDURE get_req_line_info (
11 x_rowid IN VARCHAR2,
12 x_inv_org_id IN NUMBER,
13 x_item_id IN OUT NOCOPY NUMBER,
14 x_item_revision IN OUT NOCOPY VARCHAR2,
15 x_item_description IN OUT NOCOPY VARCHAR2,
16 x_category_id IN OUT NOCOPY NUMBER,
17 x_unit_meas_lookup_code IN OUT NOCOPY VARCHAR2,
18 x_unit_price IN OUT NOCOPY NUMBER,
19 x_vendor_id IN OUT NOCOPY NUMBER,
20 x_vendor_site_id IN OUT NOCOPY NUMBER,
21 x_vendor_contact_id IN OUT NOCOPY NUMBER,
22 x_vendor_product_code IN OUT NOCOPY VARCHAR2,
23 x_suggested_buyer_id IN OUT NOCOPY NUMBER,
24 x_source_type_code IN OUT NOCOPY VARCHAR2,
25 x_source_organization_id IN OUT NOCOPY NUMBER,
26 x_source_subinventory IN OUT NOCOPY VARCHAR2,
27 x_line_type_id IN OUT NOCOPY NUMBER,
28 x_rfq_required_flag IN OUT NOCOPY VARCHAR2,
29 x_vendor_source_context IN OUT NOCOPY VARCHAR2,
30 x_org_id IN OUT NOCOPY NUMBER,
31 x_line_type IN OUT NOCOPY VARCHAR2,
32 x_order_type_lookup_code IN OUT NOCOPY VARCHAR2,
33 x_source_type IN OUT NOCOPY VARCHAR2,
34 x_suggested_buyer IN OUT NOCOPY VARCHAR2,
35 x_vendor_name IN OUT NOCOPY VARCHAR2,
36 x_vendor_contact IN OUT NOCOPY VARCHAR2,
37 x_vendor_site IN OUT NOCOPY VARCHAR2,
38 x_source_organization_name IN OUT NOCOPY VARCHAR2,
39 x_amount IN OUT NOCOPY NUMBER, -- <SERVICES FPJ>
40 x_negotiated_by_preparer_flag IN OUT NOCOPY VARCHAR2 --<DBI FPJ>
41 )
42 IS
43 BEGIN
44
45 -- <SERVICES FPJ>
46 -- Added the column amount to retrieve its value for
47 -- Fixed Price Services lines.
48 SELECT porl.item_id,
49 porl.item_revision,
50 porl.item_description,
51 porl.category_id,
52 porl.unit_meas_lookup_code,
53 porl.unit_price,
54 porl.vendor_id,
55 porl.vendor_site_id,
56 porl.vendor_contact_id,
57 porl.suggested_vendor_product_code,
58 porl.suggested_buyer_id,
59 nvl(porl.source_type_code, 'VENDOR'),
60 porl.source_organization_id,
61 porl.source_subinventory,
62 porl.line_type_id,
63 porl.rfq_required_flag,
64 porl.vendor_source_context,
65 porl.org_id,
66 plt.line_type,
67 plt.order_type_lookup_code,
68 plc.displayed_field,
69 po_inq_sv.get_person_name(porl.suggested_buyer_id),
70 nvl(v.vendor_name,porl.suggested_vendor_name),
71 decode (vc.last_name, NULL, porl.suggested_vendor_contact, vc.last_name||', '||vc.first_name),
72 nvl(vs.vendor_site_code,porl.suggested_vendor_location),
73 ood.organization_name,
74 porl.amount,
75 porl.negotiated_by_preparer_flag
76 INTO x_item_id,
77 x_item_revision,
78 x_item_description,
79 x_category_id,
80 x_unit_meas_lookup_code,
81 x_unit_price,
82 x_vendor_id,
83 x_vendor_site_id,
84 x_vendor_contact_id,
85 x_vendor_product_code,
86 x_suggested_buyer_id,
87 x_source_type_code,
88 x_source_organization_id,
89 x_source_subinventory,
90 x_line_type_id,
91 x_rfq_required_flag,
92 x_vendor_source_context,
93 x_org_id, -- debug
94 x_line_type,
95 x_order_type_lookup_code,
96 x_source_type,
97 x_suggested_buyer,
98 x_vendor_name,
99 x_vendor_contact,
100 x_vendor_site,
101 x_source_organization_name,
102 x_amount,
103 x_negotiated_by_preparer_flag
104 FROM po_lookup_codes plc,
105 org_organization_definitions ood,
106 po_line_types plt,
107 po_vendor_contacts vc,
108 po_vendor_sites vs,
109 po_vendors v,
110 mtl_system_items msi,
111 po_requisition_lines porl
112 WHERE porl.rowid = x_rowid
113 AND nvl(porl.cancel_flag,'N') = 'N'
114 AND nvl(porl.closed_code,'OPEN') <> 'FINALLY CLOSED'
115 AND porl.item_id = msi.inventory_item_id (+)
116 AND msi.organization_id (+) = x_inv_org_id
117 AND nvl(msi.outside_operation_flag,'N') = 'N'
118 AND plt.line_type_id = porl.line_type_id
119 AND plc.lookup_type (+) = 'REQUISITION SOURCE TYPE'
120 AND plc.lookup_code (+) = porl.source_type_code
121 AND v.vendor_id (+) = porl.vendor_id
122 AND vc.vendor_contact_id (+) = porl.vendor_contact_id
123 AND vs.vendor_site_id (+) = porl.vendor_site_id
124 AND ood.organization_id(+) = porl.source_organization_id;
125
126
127
128 EXCEPTION
129 WHEN OTHERS THEN RAISE;
130 END;
131
132 /*===========================================================================
133
134 PROCEDURE NAME: duplicate_express_name
135
136 ===========================================================================*/
137
138 FUNCTION duplicate_express_name (x_express_name VARCHAR2)
139 RETURN BOOLEAN
140 IS
141 dummy_char VARCHAR2(1) := '';
142 BEGIN
143
144 IF x_express_name IS NOT NULL THEN
145
146 BEGIN
147
148 SELECT 'Y'
149 INTO dummy_char
150 FROM po_reqexpress_headers porh
151 WHERE porh.express_name = x_express_name;
152
153 return(TRUE);
154
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 return(false);
158 WHEN OTHERS THEN
159 return(false);
160 END;
161
162 ELSE
163
164 return (FALSE);
165
166 END IF;
167
168 EXCEPTION
169 WHEN OTHERS THEN RAISE;
170 END;
171
172 /*===========================================================================
173
174 PROCEDURE NAME: duplicate_sequence_number ( iali bug 489705 )
175
176 ===========================================================================*/
177
178 FUNCTION duplicate_sequence_number (X_express_name IN VARCHAR2,
179 X_sequence_num IN NUMBER,
180 X_rowid IN VARCHAR2)
181 RETURN BOOLEAN
182 IS
183 dummy NUMBER;
184 BEGIN
185 IF (X_sequence_num IS NOT NULL) THEN
186 BEGIN
187
188 SELECT 1
189 INTO dummy
190 FROM DUAL
191 WHERE not exists (SELECT 'this line num exists already'
192 FROM PO_REQEXPRESS_LINES
193 WHERE EXPRESS_NAME = X_express_name
194 AND SEQUENCE_NUM = X_sequence_num
195 AND (rowid <> X_rowid OR X_rowid is NULL));
196 return (FALSE);
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 return (TRUE);
200 WHEN OTHERS THEN
201 return (TRUE);
202 END;
203
204 ELSE
205 return (TRUE);
206
207 END IF;
208
209 EXCEPTION
210 WHEN OTHERS THEN RAISE;
211
212 END; -- duplicate_sequence_number
213
214 /*===========================================================================
215
216 PROCEDURE NAME: inventory_item_cost
217
218 ===========================================================================*/
219
220 FUNCTION inventory_item_cost (x_inventory_item_id VARCHAR2,
221 x_organization_id VARCHAR2)
222 RETURN NUMBER
223 IS
224 x_unit_price NUMBER := 0;
225 BEGIN
226
227 IF (x_organization_id IS NOT NULL AND
228 x_inventory_item_id IS NOT NULL) THEN
229
230 BEGIN
231
232 SELECT item_cost
233 INTO x_unit_price
234 FROM cst_item_costs_for_gl_view
235 WHERE inventory_item_id = x_inventory_item_id
236 AND organization_id = x_organization_id;
237
238 return(x_unit_price);
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 return(0);
243 WHEN OTHERS THEN
244 return(0);
245 END;
246
247 ELSE
248 return (0);
249 END IF;
250
251 EXCEPTION
252 WHEN OTHERS THEN RAISE;
253 END;
254
255 -- debug: This procedure is not needed.
256
257
258 -- Bug 1006562
259 -- Fetches the primary unit of measure for an item.
260 /*===========================================================================
261
262 PROCEDURE NAME: primary_unit_of_measure
263
264 ============================================================================*/
265
266 FUNCTION primary_unit_of_measure (x_inventory_item_id IN VARCHAR2,
267 x_organization_id IN VARCHAR2)
268 RETURN VARCHAR2
269 IS
270 x_primary_unit_of_measure VARCHAR2(100) := NULL;
271 BEGIN
272
273 IF (x_organization_id IS NOT NULL AND
274 x_inventory_item_id IS NOT NULL) THEN
275
276 BEGIN
277
278 SELECT primary_unit_of_measure
279 INTO x_primary_unit_of_measure
280 FROM mtl_system_items
281 WHERE inventory_item_id = x_inventory_item_id
282 AND organization_id = x_organization_id;
283
284 return(x_primary_unit_of_measure);
285
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288 return(NULL);
289 WHEN OTHERS THEN
290 return(NULL);
291 END;
292
293 ELSE
294 return(NULL);
295 END IF;
296
297 EXCEPTION
298 WHEN OTHERS THEN RAISE;
299
300 END primary_unit_of_measure;
301
302 /*===========================================================================
303
304 PROCEDURE NAME: get_order_type
305
306 ===========================================================================*/
307
308 PROCEDURE get_order_type (x_line_type_id IN NUMBER,
309 x_order_type_lookup_code IN OUT NOCOPY VARCHAR2)
310 IS
311 BEGIN
312
313 IF (x_line_type_id IS NOT NULL) THEN
314
315 BEGIN
316
317 SELECT order_type_lookup_code
318 INTO x_order_type_lookup_code
319 FROM po_line_types
320 WHERE line_type_id = x_line_type_id;
321
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 x_order_type_lookup_code := '';
325 WHEN OTHERS THEN
326 raise;
327 END;
328 END IF;
329
330 EXCEPTION
331 WHEN OTHERS THEN RAISE;
332 END;
333
334 END PO_REQ_TEMPLATE_SV2;
[Home] [Help]