1 PACKAGE BODY JL_ZZ_PO_LIBRARY_1_PKG AS
2 /* $Header: jlzzul1b.pls 120.1.12010000.2 2008/08/25 06:44:19 vspuli ship $ */
3
4 PROCEDURE get_fcc_code (fcc_code_type IN VARCHAR2,
5 tran_nat_type IN VARCHAR2,
6 so_org_id IN VARCHAR2,
7 inv_item_id IN NUMBER,
8 fcc_code IN OUT NOCOPY VARCHAR2,
9 tran_nat IN OUT NOCOPY VARCHAR2,
10 row_number IN NUMBER,
11 Errcd IN OUT NOCOPY NUMBER) IS
12 BEGIN
13 Errcd := 0;
14 SELECT fcc.meaning,
15 tn.meaning
16 INTO fcc_code,
17 tran_nat
18 FROM mtl_system_items mtl, fnd_lookups fcc, fnd_lookups tn
19 WHERE fcc.lookup_code = SUBSTR (mtl.global_attribute1, 1, 25)
20 AND fcc.lookup_type = fcc_code_type
21 AND tn.lookup_code = SUBSTR (mtl.global_attribute2, 1, 25)
22 AND tn.lookup_type = tran_nat_type
23 AND mtl.organization_id = so_org_id
24 AND mtl.inventory_item_id = inv_item_id
25 AND rownum = row_number;
26 EXCEPTION
27 WHEN OTHERS THEN
28 Errcd := SQLCODE;
29 END get_fcc_code;
30
31
32 PROCEDURE get_total_tax (po_header_id IN NUMBER,
33 total_tax IN OUT NOCOPY VARCHAR2,
34 row_number IN NUMBER,
35 Errcd IN OUT NOCOPY NUMBER) IS
36 BEGIN
37 Errcd := 0;
38 SELECT SUM (global_attribute6)
39 INTO total_tax
40 FROM po_line_locations_all
41 WHERE po_header_id = po_header_id
42 AND rownum = row_number;
43 EXCEPTION
44 WHEN OTHERS THEN
45 Errcd := SQLCODE;
46 END get_total_tax;
47
48
49 PROCEDURE get_fc_code (form_org_id IN NUMBER,
50 form_item_id IN NUMBER,
51 fc_code IN OUT NOCOPY VARCHAR2,
52 row_number IN NUMBER,
53 Errcd IN OUT NOCOPY NUMBER) IS
54 BEGIN
55 Errcd := 0;
56 SELECT global_attribute1
57 INTO fc_code
58 FROM mtl_system_items mtl
59 WHERE mtl.organization_id = form_org_id
60 AND inventory_item_id = form_item_id
61 AND rownum = row_number;
62 EXCEPTION
63 WHEN OTHERS THEN
64 Errcd := SQLCODE;
65 END get_fc_code;
66
67
68 PROCEDURE get_global_attributes (form_line_loca_id IN NUMBER,
69 ga1 IN OUT NOCOPY VARCHAR2,
70 ga2 IN OUT NOCOPY VARCHAR2,
71 ga3 IN OUT NOCOPY VARCHAR2,
72 ga4 IN OUT NOCOPY VARCHAR2,
73 ga5 IN OUT NOCOPY VARCHAR2,
74 ga6 IN OUT NOCOPY VARCHAR2,
75 row_number IN NUMBER,
76 Errcd IN OUT NOCOPY NUMBER) IS
77 BEGIN
78 Errcd := 0;
79 SELECT global_attribute1, global_attribute2,
80 global_attribute3, global_attribute4,
81 global_attribute5, global_attribute6
82 INTO ga1, ga2, ga3, ga4, ga5, ga6
83 FROM po_line_locations_all
84 WHERE line_location_id = form_line_loca_id
85 AND rownum = row_number;
86 EXCEPTION
87 WHEN OTHERS THEN
88 Errcd := SQLCODE;
89 END get_global_attributes;
90
91
92 PROCEDURE get_total_tax_for_release (po_header_id2 IN NUMBER,
93 po_release_id2 IN NUMBER,
94 total_tax IN OUT NOCOPY VARCHAR2,
95 row_number IN NUMBER,
96 Errcd IN OUT NOCOPY NUMBER) IS
97 BEGIN
98 Errcd := 0;
99 SELECT SUM (global_attribute6)
100 INTO total_tax
101 FROM po_line_locations_all
102 WHERE po_header_id = po_header_id2
103 AND po_release_id = po_release_id2
104 AND rownum = row_number;
105 EXCEPTION
106 WHEN OTHERS THEN
107 Errcd := SQLCODE;
108 END get_total_tax_for_release;
109
110
111 PROCEDURE get_context_name3 (global_description IN OUT NOCOPY VARCHAR2,
112 row_number IN NUMBER,
113 Errcd IN OUT NOCOPY NUMBER) IS
114 BEGIN
115 Errcd := 0;
116 SELECT SUBSTR (description, 1, 30)
117 INTO global_description
118 FROM fnd_descr_flex_contexts_vl
119 WHERE application_id = 7003
120 AND descriptive_flexfield_name = 'JG_PO_SYSTEM_PARAMETERS'
121 AND descriptive_flex_context_code = 'JL.BR.POXSTDPO.PO_OPTIONS'
122 AND enabled_flag = 'Y'
123 AND rownum = row_number;
124 EXCEPTION
125 WHEN OTHERS THEN
126 Errcd := SQLCODE;
127 END get_context_name3;
128
129
130 PROCEDURE get_trx_reason1 (org_id IN NUMBER,
131 item_id IN NUMBER,
132 trx_reason IN OUT NOCOPY VARCHAR2,
133 fcc IN OUT NOCOPY VARCHAR2,
134 row_number IN NUMBER,
135 Errcd IN OUT NOCOPY NUMBER) IS
136 BEGIN
137 Errcd := 0;
138 SELECT global_attribute2, global_attribute1
139 INTO trx_reason, fcc
140 FROM mtl_system_items mtl
141 WHERE mtl.organization_id = org_id
142 AND mtl.inventory_item_id = item_id
143 AND rownum = row_number;
144 EXCEPTION
145 WHEN OTHERS THEN
146 Errcd := SQLCODE;
147 END get_trx_reason1;
148
149
150 PROCEDURE get_trx_reason2 (trx_reason IN OUT NOCOPY VARCHAR2,
151 row_number IN NUMBER,
152 Errcd IN OUT NOCOPY NUMBER) IS
153 BEGIN
154 Errcd := 0;
155 SELECT global_attribute1
156 INTO trx_reason
157 FROM po_system_parameters
158 WHERE rownum = row_number;
159 EXCEPTION
160 WHEN OTHERS THEN
161 Errcd := SQLCODE;
162 END get_trx_reason2;
163
164
165 PROCEDURE get_displayed_field (tran_code IN VARCHAR2,
166 disp_field IN OUT NOCOPY VARCHAR2,
167 row_number IN NUMBER,
168 Errcd IN OUT NOCOPY NUMBER) IS
169 BEGIN
170 Errcd := 0;
171 SELECT displayed_field
172 INTO disp_field
173 FROM po_lookup_codes
174 WHERE lookup_code = tran_code
175 AND rownum = row_number;
176 EXCEPTION
177 WHEN OTHERS THEN
178 Errcd := SQLCODE;
179 END get_displayed_field;
180
181
182 -----------------------------------------------------------------------
183 -- Added Bug: 7323242
184 -- PRIVATE PROCEDURE
185 -- get_trx_reason_def_rule
186 --
187 -- DESCRIPTION
188 -- This procedure gets transaction reason defaulting rule from PO Options
189 -- form to determine from which inventory org, local or master that
190 -- the value of transaction reason code should get from
191 --
192 PROCEDURE get_trx_reason_def_rule(
193 p_org_id IN PO_REQUISITION_LINES.ORG_ID%TYPE,
194 p_trx_reason_def_rule OUT NOCOPY PO_SYSTEM_PARAMETERS.GLOBAL_ATTRIBUTE3%TYPE,
195 p_error_code OUT NOCOPY NUMBER)
196 IS
197
198 CURSOR get_trx_reason_def_rule_mo_csr(
199 c_org_id PO_SYSTEM_PARAMETERS.ORG_ID%TYPE)
200 IS
201 SELECT global_attribute3
202 FROM PO_SYSTEM_PARAMETERS_ALL
203 WHERE org_id = c_org_id;
204
205 CURSOR get_trx_reason_def_rule_so_csr
206 IS
207 SELECT global_attribute3
208 FROM PO_SYSTEM_PARAMETERS_ALL;
209
210
211 BEGIN
212
213 p_error_code := 0;
214
215 IF p_org_id IS NULL THEN
216 -- single org
217 OPEN get_trx_reason_def_rule_so_csr;
218 FETCH get_trx_reason_def_rule_so_csr INTO
219 p_trx_reason_def_rule;
220 CLOSE get_trx_reason_def_rule_so_csr;
221 ELSE
222 OPEN get_trx_reason_def_rule_mo_csr(p_org_id);
223 FETCH get_trx_reason_def_rule_mo_csr INTO
224 p_trx_reason_def_rule;
225 CLOSE get_trx_reason_def_rule_mo_csr;
226 END IF;
227
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 p_error_code := SQLCODE;
232 IF get_trx_reason_def_rule_so_csr%ISOPEN THEN
233 CLOSE get_trx_reason_def_rule_so_csr;
234 END IF;
235 IF get_trx_reason_def_rule_mo_csr%ISOPEN THEN
236 CLOSE get_trx_reason_def_rule_mo_csr;
237 END IF;
238
239 END get_trx_reason_def_rule;
240
241
242 -----------------------------------------------------------------------
243 -- Added Bug: 7323242
244 -- PRIVATE PROCEDURE
245 -- get_trx_reason_from_po
246 --
247 -- DESCRIPTION
248 -- This procedure gets transaction reason code from po_system_parameters
249 --
250 PROCEDURE get_trx_reason_from_po(
251 p_org_id IN PO_SYSTEM_PARAMETERS.ORG_ID%TYPE,
252 p_trx_reason_code OUT NOCOPY MTL_SYSTEM_ITEMS.GLOBAL_ATTRIBUTE2%TYPE,
253 p_error_code OUT NOCOPY NUMBER)
254 IS
255
256 CURSOR get_trx_reason_code_so_csr
257 IS
258 SELECT global_attribute1
259 FROM PO_SYSTEM_PARAMETERS_ALL;
260
261 CURSOR get_trx_reason_code_mo_csr(
262 c_org_id PO_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE)
263 IS
264 SELECT global_attribute1
265 FROM PO_SYSTEM_PARAMETERS_ALL
266 WHERE org_id = c_org_id;
267
268 BEGIN
269
270 p_error_code := 0;
271
272 IF p_org_id IS NULL THEN
273 -- single org
274 OPEN get_trx_reason_code_so_csr;
275 FETCH get_trx_reason_code_so_csr INTO
276 p_trx_reason_code;
277 CLOSE get_trx_reason_code_so_csr;
278 ELSE
279 OPEN get_trx_reason_code_mo_csr(p_org_id);
280 FETCH get_trx_reason_code_mo_csr INTO
281 p_trx_reason_code;
282 CLOSE get_trx_reason_code_mo_csr;
283 END IF;
284
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 p_error_code := SQLCODE;
289 IF get_trx_reason_code_so_csr%ISOPEN THEN
290 CLOSE get_trx_reason_code_so_csr;
291 END IF;
292 IF get_trx_reason_code_mo_csr%ISOPEN THEN
293 CLOSE get_trx_reason_code_mo_csr;
294 END IF;
295
296 END get_trx_reason_from_po;
297
298
299 -----------------------------------------------------------------------
300 -- Added Bug: 7323242
301 -- PUBLIC PROCEDURE
302 -- get_trx_reason_cd_per_req_line
303 --
304 -- DESCRIPTION
305 -- This procedure is called from JL library, it gets the transaction
306 -- reason code from mtl_system_items based on a given item_id and the
307 -- organization that user specified from the Transaction Nature
308 -- Defaulting Rule GDF in PO Options form. If no item is provided or
309 -- Transaction Reason code is not available from mtl_system_items for
310 -- the specified Local/Master inventory organization, the
311 -- Transaction Reason code from PO Options form will be returned
312
313 PROCEDURE get_trx_reason_cd_per_req_line(
314 p_master_inv_org_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
315 , p_inventory_org_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
316 , p_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
317 , p_org_id IN PO_REQUISITION_LINES.ORG_ID%TYPE
318 , x_trx_reason_code OUT NOCOPY PO_REQUISITION_LINES.TRANSACTION_REASON_CODE%TYPE
319 , x_error_code OUT NOCOPY NUMBER)
320 IS
321
322 l_trx_reason_def_rule PO_SYSTEM_PARAMETERS.GLOBAL_ATTRIBUTE3%TYPE;
323 l_def_from_org_id NUMBER;
324 l_fcc VARCHAR2(15);
325 BEGIN
326
327 --
328 -- init return transaction reason code
329 --
330 x_trx_reason_code := NULL;
331 x_error_code := 0;
332
333 IF p_item_id IS NOT NULL THEN
334 --
335 -- get transaction reason code from mtl system items if
336 -- item is known.
337 -- determine which organization to use from po system parameters
338 --
339 get_trx_reason_def_rule(
340 p_org_id,
341 l_trx_reason_def_rule,
342 x_error_code);
343 IF x_error_code <> 0 THEN
344 RETURN;
345 END IF;
346
347 IF NVL(l_trx_reason_def_rule, 'MASTER INVENTORY ORGANIZATION') = 'INVENTORY ORGANIZATION' THEN
348 l_def_from_org_id := p_inventory_org_id;
349
350 get_trx_reason1(
351 l_def_from_org_id,
352 p_item_id,
353 x_trx_reason_code,
354 l_fcc,
355 1,
356 x_error_code);
357
358 IF x_error_code <> 0 THEN
359 RETURN;
360 END IF;
361 IF x_trx_reason_code IS NULL THEN
362 --
363 -- try to get trx reason code based on
364 -- master inventory org
365 --
366 l_def_from_org_id := p_master_inv_org_id;
367
368 get_trx_reason1(
369 l_def_from_org_id,
370 p_item_id,
371 x_trx_reason_code,
372 l_fcc,
373 1,
374 x_error_code);
375
376 IF (x_error_code <> 0 OR
377 x_trx_reason_code IS NOT NULL) THEN
378 --
379 -- return if error occurs or
380 -- trx reason code is found at Master org
381 --
382 RETURN;
383 END IF;
384 ELSE
385 --
386 -- found trx reason code based on local org
387 --
388 RETURN;
389 END IF;
390 ELSIF NVL(l_trx_reason_def_rule, 'MASTER INVENTORY ORGANIZATION') = 'MASTER INVENTORY ORGANIZATION' THEN
391 l_def_from_org_id := p_master_inv_org_id;
392 get_trx_reason1(
393 l_def_from_org_id,
394 p_item_id,
395 x_trx_reason_code,
396 l_fcc,
397 1,
398 x_error_code);
399 IF (x_error_code <> 0 OR
400 x_trx_reason_code IS NOT NULL) THEN
401 --
402 -- return if error occurs or
403 -- trx reason code is found
404 --
405 RETURN;
406 END IF;
407 END IF;
408 END IF; -- of p_item_id is NOT NULL
409 --
410 -- get here then 1 of the following is true
411 -- p_item_id is NULL or
412 -- p_item_id is not NULL but trx reason code is
413 -- not available for Local/Master org
414 -- need to get trx reason code from
415 -- PO system parameters
416 --
417
418 get_trx_reason_from_po(
419 p_org_id,
420 x_trx_reason_code,
421 x_error_code);
422
423 END get_trx_reason_cd_per_req_line;
424
425 END JL_ZZ_PO_LIBRARY_1_PKG;
[Home] [Help]