[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_UTILS_PKG
Source
1 PACKAGE BODY jai_rcv_utils_pkg AS
2 /* $Header: jai_rcv_utils.plb 120.2 2006/05/26 11:58:32 lgopalsa ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_rcv_utils -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 13-Jun-2005 File Version: 116.3
15 Ramananda for bug#4428980. Removal of SQL LITERALs is done
16
17 06-Jul-2005 rallamse for bug# PADDR Elimination
18 1. Removed function query_locator from both package spec and body.
19 --------------------------------------------------------------------------------------*/
20
21
22 /*------------------------------------------------------------------------------------------------------------*/
23 FUNCTION get_orgn_type_flags (p_location_id number,
24 p_organization_id number,
25 p_subinventory varchar2)
26 RETURN VARCHAR2 IS
27 v_rg_location_id number;
28 v_subinv varchar2(3);
29 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_rcv_utils_pkg.get_orgn_type_flags';
30 BEGIN
31
32 /*----------------------------------------------------------------------------------------------------------------------
33 FILENAME: get_orgn_type_flags.sql
34 CHANGE HISTORY:
35
36 S.No Date Author and Details
37 -----------------------------------------------------------------------------------------------------------------------
38 1 30/07/04 Nagaraj.s for Bug 3693740 Version : 115.1
39 Previously the condition
40 v_subinv := NVL(v_subinv,'No Data');
41 and since the data v_subinv was having a width of
42 3 characters. Hence the call to this function went into
43 a Numeric or Value Error. Hence changed this to NN.
44
45 */
46 jai_rcv_utils_pkg.get_rg1_location (p_location_id,
47 p_organization_id,
48 p_subinventory,
49 v_rg_location_id);
50 For loc_rec IN (SELECT manufacturing,
51 trading
52 FROM JAI_CMN_INVENTORY_ORGS
53 WHERE organization_id = p_organization_id
54 AND location_id = v_rg_location_id)
55 LOOP
56 v_subinv := NVL(loc_rec.manufacturing, 'N')||NVL(loc_rec.trading, 'N');
57 END LOOP;
58
59 v_subinv := NVL(v_subinv, 'NN'); /* Changed as NN for Bug3693740 */
60
61 RETURN v_subinv;
62 EXCEPTION
63 WHEN OTHERS THEN
64 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
65 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
66 app_exception.raise_exception;
67 END get_orgn_type_flags;
68 /*------------------------------------------------------------------------------------------------------------*/
69
70 PROCEDURE get_div_range (p_vendor_id number,
71 p_vendor_site_id number,
72 p_range_no OUT NOCOPY varchar2,
73 p_division_no OUT NOCOPY varchar2) IS
74 BEGIN
75 For hr_rec IN (SELECT excise_duty_range range,
76 excise_duty_division div
77 FROM JAI_CMN_VENDOR_SITES
78 WHERE vendor_id = p_vendor_id
79 AND vendor_site_id = p_vendor_site_id)
80 LOOP
81 p_range_no := hr_rec.range;
82 p_division_no := hr_rec.div;
83 END LOOP;
84
85 END get_div_range;
86 /*--------------------------------------------------------------------------------------------------------*/
87 PROCEDURE get_func_curr (p_organization_id number,
88 p_func_currency OUT NOCOPY varchar2,
89 p_gl_set_of_books_id OUT NOCOPY number)
90 IS
91 v_set_of_books_id gl_sets_of_books.set_of_books_id % type;
92 v_func_currency gl_sets_of_books.currency_code % type;
93 v_currency_conversion_rate rcv_transactions.currency_conversion_type % type;
94
95 /* Bug 5243532. Added by Lakshmi Gopalsami
96 * Defined variable for implementing caching logic.
97 */
98 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
99 -- End for bug 5243532
100 BEGIN
101 IF p_organization_id IS NOT NULL
102 THEN
103 /* bug 5243532. Added by Lakshmi Gopalsami
104 * removed cursor org_rec and implemented caching logic.
105 */
106 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
107 (p_org_id => p_organization_id );
108 v_func_currency := l_func_curr_det.currency_code;
109 v_set_of_books_id := l_func_curr_det.ledger_id;
110
111 ELSE
112 FND_PROFILE.GET('GL_SET_OF_BKS_ID', v_set_of_books_id);
113 For org_rec IN (SELECT currency_code
114 FROM gl_sets_of_books
115 WHERE set_of_books_id = v_set_of_books_id)
116 LOOP
117 v_func_currency := org_rec.currency_code;
118 END LOOP;
119 END IF;
120 p_func_currency := v_func_currency;
121 p_gl_set_of_books_id := v_set_of_books_id;
122 END get_func_curr;
123 ----------------------------- Procedure For picking organization and item -----------------------
124 PROCEDURE get_organization (p_shipment_line_id number,
125 p_organization_id OUT NOCOPY number,
126 p_item_id OUT NOCOPY number) IS
127 BEGIN
128 For item_rec IN (SELECT to_organization_id,
129 item_id
130 FROM rcv_shipment_lines
131 WHERE shipment_line_id = p_shipment_line_id)
132 LOOP
133 p_organization_id := item_rec.to_organization_id;
134 p_item_id := item_rec.item_id;
135 END LOOP;
136 END get_organization;
137
138 ---------------------- Procedure For picking the location_id and bonded type --------------------
139 PROCEDURE get_rg1_location (p_location_id number,
140 p_organization_id number,
141 p_subinventory varchar2,
142 p_rg_location_id OUT NOCOPY number) IS
143 v_rg_location_id number;
144 v_organization_id number;
145 BEGIN
146 IF p_subinventory IS NOT NULL
147 THEN
148 get_location (p_location_id,
149 p_organization_id,
150 p_subinventory,
151 v_rg_location_id);
152 ELSE
153 IF p_location_id = 0
154 THEN
155 v_rg_location_id := 0;
156 ELSIF p_location_id IS NOT NULL
157 THEN
158 For hr_rec IN (SELECT inventory_organization_id org
159 FROM hr_locations
160 WHERE location_id = p_location_id)
161 LOOP
162 v_organization_id := NVL(hr_rec.org, 0);
163 END LOOP;
164 IF NVL(v_organization_id, 0) <> p_organization_id
165 THEN
166 v_rg_location_id := 0;
167 ELSE
168 v_rg_location_id := p_location_id;
169 END IF;
170 END IF;
171 END IF;
172 p_rg_location_id := NVL(v_rg_location_id, 0);
173 END get_rg1_location;
174
175 ---------------------- Procedure For picking the location_id and bonded type --------------------
176
177 PROCEDURE get_location (p_location_id number,
178 p_organization_id number,
179 p_subinventory varchar2,
180 p_rg_location_id OUT NOCOPY number) IS
181 v_rg_location_id number;
182 BEGIN
183
184 For location_rec IN (SELECT location_id
185 FROM JAI_INV_SUBINV_DTLS
186 WHERE organization_id = p_organization_id
187 AND sub_inventory_name = p_subinventory)
188 LOOP
189 v_rg_location_id := location_rec.location_id;
190 END LOOP;
191 p_rg_location_id := NVL(v_rg_location_id, 0);
192 END get_location;
193
194
195
196
197 END jai_rcv_utils_pkg;