1 PACKAGE BODY JL_ZZ_PO_LIBRARY_1_PKG AS
2 /* $Header: jlzzul1b.pls 120.3 2010/08/09 21:34:29 rahulkum 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
61 AND rownum = row_number;
58 FROM mtl_system_items mtl
59 WHERE mtl.organization_id = form_org_id
60 AND inventory_item_id = form_item_id
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 fnd_file.put_line( FND_FILE.LOG, 'Calling JL_ZZ_PO_LIBRARY_1_PKG.get_trx_reason1:Org_id: '||org_id );
139 SELECT global_attribute2, global_attribute1
140 INTO trx_reason, fcc
141 FROM mtl_system_items mtl
142 WHERE (mtl.organization_id = org_id OR (mtl.organization_id IN (SELECT ood.organization_id
143 FROM org_organization_definitions ood
144 WHERE ood.operating_unit = org_id
145 )
146 ))
147 AND mtl.inventory_item_id = item_id
148 AND rownum = row_number;
149 fnd_file.put_line( FND_FILE.LOG, 'In get_trx_reason1:Transaction Reason: '||trx_reason );
150 EXCEPTION
151 WHEN OTHERS THEN
152 Errcd := SQLCODE;
153 END get_trx_reason1;
154
155
156 PROCEDURE get_trx_reason2 (trx_reason IN OUT NOCOPY VARCHAR2,
157 row_number IN NUMBER,
158 Errcd IN OUT NOCOPY NUMBER) IS
159 BEGIN
160 Errcd := 0;
161 SELECT global_attribute1
162 INTO trx_reason
163 FROM po_system_parameters
164 WHERE rownum = row_number;
165 EXCEPTION
166 WHEN OTHERS THEN
167 Errcd := SQLCODE;
168 END get_trx_reason2;
169
170
171 PROCEDURE get_displayed_field (tran_code IN VARCHAR2,
172 disp_field IN OUT NOCOPY VARCHAR2,
173 row_number IN NUMBER,
174 Errcd IN OUT NOCOPY NUMBER) IS
175 BEGIN
176 Errcd := 0;
177 SELECT displayed_field
178 INTO disp_field
179 FROM po_lookup_codes
180 WHERE lookup_code = tran_code
181 AND rownum = row_number;
182 EXCEPTION
183 WHEN OTHERS THEN
184 Errcd := SQLCODE;
185 END get_displayed_field;
186
187
188 -----------------------------------------------------------------------
189 -- Added Bug: 7323242
190 -- PRIVATE PROCEDURE
191 -- get_trx_reason_def_rule
192 --
193 -- DESCRIPTION
194 -- This procedure gets transaction reason defaulting rule from PO Options
195 -- form to determine from which inventory org, local or master that
196 -- the value of transaction reason code should get from
197 --
201 p_error_code OUT NOCOPY NUMBER)
198 PROCEDURE get_trx_reason_def_rule(
199 p_org_id IN PO_REQUISITION_LINES.ORG_ID%TYPE,
200 p_trx_reason_def_rule OUT NOCOPY PO_SYSTEM_PARAMETERS.GLOBAL_ATTRIBUTE3%TYPE,
202 IS
203
204 CURSOR get_trx_reason_def_rule_mo_csr(
205 c_org_id PO_SYSTEM_PARAMETERS.ORG_ID%TYPE)
206 IS
207 SELECT global_attribute3
208 FROM PO_SYSTEM_PARAMETERS_ALL
209 WHERE org_id = c_org_id;
210
211 CURSOR get_trx_reason_def_rule_so_csr
212 IS
213 SELECT global_attribute3
214 FROM PO_SYSTEM_PARAMETERS_ALL;
215
216
217 BEGIN
218
219 p_error_code := 0;
220
221 IF p_org_id IS NULL THEN
222 -- single org
223 OPEN get_trx_reason_def_rule_so_csr;
224 FETCH get_trx_reason_def_rule_so_csr INTO
225 p_trx_reason_def_rule;
226 CLOSE get_trx_reason_def_rule_so_csr;
227 ELSE
228 OPEN get_trx_reason_def_rule_mo_csr(p_org_id);
229 FETCH get_trx_reason_def_rule_mo_csr INTO
230 p_trx_reason_def_rule;
231 CLOSE get_trx_reason_def_rule_mo_csr;
232 END IF;
233
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 p_error_code := SQLCODE;
238 IF get_trx_reason_def_rule_so_csr%ISOPEN THEN
239 CLOSE get_trx_reason_def_rule_so_csr;
240 END IF;
241 IF get_trx_reason_def_rule_mo_csr%ISOPEN THEN
242 CLOSE get_trx_reason_def_rule_mo_csr;
243 END IF;
244
245 END get_trx_reason_def_rule;
246
247
248 -----------------------------------------------------------------------
249 -- Added Bug: 7323242
250 -- PRIVATE PROCEDURE
251 -- get_trx_reason_from_po
252 --
253 -- DESCRIPTION
254 -- This procedure gets transaction reason code from po_system_parameters
255 --
256 PROCEDURE get_trx_reason_from_po(
257 p_org_id IN PO_SYSTEM_PARAMETERS.ORG_ID%TYPE,
258 p_trx_reason_code OUT NOCOPY MTL_SYSTEM_ITEMS.GLOBAL_ATTRIBUTE2%TYPE,
259 p_error_code OUT NOCOPY NUMBER)
260 IS
261
262 CURSOR get_trx_reason_code_so_csr
263 IS
264 SELECT global_attribute1
265 FROM PO_SYSTEM_PARAMETERS_ALL;
266
267 CURSOR get_trx_reason_code_mo_csr(
268 c_org_id PO_SYSTEM_PARAMETERS_ALL.ORG_ID%TYPE)
269 IS
270 SELECT global_attribute1
271 FROM PO_SYSTEM_PARAMETERS_ALL
272 WHERE org_id = c_org_id;
273
274 BEGIN
275
276 p_error_code := 0;
277
278 IF p_org_id IS NULL THEN
279 -- single org
280 OPEN get_trx_reason_code_so_csr;
281 FETCH get_trx_reason_code_so_csr INTO
282 p_trx_reason_code;
283 CLOSE get_trx_reason_code_so_csr;
284 ELSE
285 OPEN get_trx_reason_code_mo_csr(p_org_id);
286 FETCH get_trx_reason_code_mo_csr INTO
287 p_trx_reason_code;
288 CLOSE get_trx_reason_code_mo_csr;
289 END IF;
290
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 p_error_code := SQLCODE;
295 IF get_trx_reason_code_so_csr%ISOPEN THEN
296 CLOSE get_trx_reason_code_so_csr;
297 END IF;
298 IF get_trx_reason_code_mo_csr%ISOPEN THEN
299 CLOSE get_trx_reason_code_mo_csr;
300 END IF;
301
302 END get_trx_reason_from_po;
303
304
305 -----------------------------------------------------------------------
306 -- Added Bug: 7323242
307 -- PUBLIC PROCEDURE
308 -- get_trx_reason_cd_per_req_line
309 --
310 -- DESCRIPTION
311 -- This procedure is called from JL library, it gets the transaction
312 -- reason code from mtl_system_items based on a given item_id and the
313 -- organization that user specified from the Transaction Nature
314 -- Defaulting Rule GDF in PO Options form. If no item is provided or
315 -- Transaction Reason code is not available from mtl_system_items for
316 -- the specified Local/Master inventory organization, the
317 -- Transaction Reason code from PO Options form will be returned
318
319 PROCEDURE get_trx_reason_cd_per_req_line(
320 p_master_inv_org_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
321 , p_inventory_org_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
322 , p_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
323 , p_org_id IN PO_REQUISITION_LINES.ORG_ID%TYPE
324 , x_trx_reason_code OUT NOCOPY PO_REQUISITION_LINES.TRANSACTION_REASON_CODE%TYPE
325 , x_error_code OUT NOCOPY NUMBER)
326 IS
327
328 l_trx_reason_def_rule PO_SYSTEM_PARAMETERS.GLOBAL_ATTRIBUTE3%TYPE;
329 l_def_from_org_id NUMBER;
330 l_fcc VARCHAR2(15);
331 BEGIN
332
333 --
334 -- init return transaction reason code
335 --
336 x_trx_reason_code := NULL;
337 x_error_code := 0;
338
339 IF p_item_id IS NOT NULL THEN
340 --
341 -- get transaction reason code from mtl system items if
342 -- item is known.
343 -- determine which organization to use from po system parameters
344 --
345 get_trx_reason_def_rule(
346 p_org_id,
347 l_trx_reason_def_rule,
348 x_error_code);
349 IF x_error_code <> 0 THEN
350 RETURN;
351 END IF;
352
353 IF NVL(l_trx_reason_def_rule, 'MASTER INVENTORY ORGANIZATION') = 'INVENTORY ORGANIZATION' THEN
354 l_def_from_org_id := p_inventory_org_id;
355
356 get_trx_reason1(
357 l_def_from_org_id,
358 p_item_id,
359 x_trx_reason_code,
360 l_fcc,
361 1,
362 x_error_code);
363
364 IF x_error_code <> 0 THEN
365 RETURN;
366 END IF;
367 IF x_trx_reason_code IS NULL THEN
368 --
369 -- try to get trx reason code based on
370 -- master inventory org
371 --
372 l_def_from_org_id := p_master_inv_org_id;
373
374 get_trx_reason1(
375 l_def_from_org_id,
376 p_item_id,
377 x_trx_reason_code,
378 l_fcc,
379 1,
380 x_error_code);
381
382 IF (x_error_code <> 0 OR
383 x_trx_reason_code IS NOT NULL) THEN
384 --
385 -- return if error occurs or
386 -- trx reason code is found at Master org
387 --
388 RETURN;
389 END IF;
390 ELSE
391 --
392 -- found trx reason code based on local org
393 --
394 RETURN;
395 END IF;
396 ELSIF NVL(l_trx_reason_def_rule, 'MASTER INVENTORY ORGANIZATION') = 'MASTER INVENTORY ORGANIZATION' THEN
397 l_def_from_org_id := p_master_inv_org_id;
398 get_trx_reason1(
399 l_def_from_org_id,
400 p_item_id,
401 x_trx_reason_code,
402 l_fcc,
403 1,
404 x_error_code);
405 IF (x_error_code <> 0 OR
406 x_trx_reason_code IS NOT NULL) THEN
407 --
408 -- return if error occurs or
409 -- trx reason code is found
410 --
411 RETURN;
412 END IF;
413 END IF;
414 END IF; -- of p_item_id is NOT NULL
415 --
416 -- get here then 1 of the following is true
417 -- p_item_id is NULL or
418 -- p_item_id is not NULL but trx reason code is
419 -- not available for Local/Master org
420 -- need to get trx reason code from
421 -- PO system parameters
422 --
423
424 get_trx_reason_from_po(
425 p_org_id,
426 x_trx_reason_code,
427 x_error_code);
428
429 END get_trx_reason_cd_per_req_line;
430
431 END JL_ZZ_PO_LIBRARY_1_PKG;
[Home] [Help]