[Home] [Help]
PACKAGE BODY: APPS.INV_THIRD_PARTY_STOCK_UTIL
Source
1 PACKAGE BODY INV_THIRD_PARTY_STOCK_UTIL AS
2 -- $Header: INVUTPSB.pls 120.4 2008/02/20 18:19:34 athammin ship $
3 --+=======================================================================+
4 --| Copyright (c) 2002 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVUTPSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Consignment Utilities Package |
13 --| |
14 --| PROCEDURE LIST |
15 --| Get_Org_Id |
16 --| Set_OU_Context |
17 --| Get_Primary_UOM |
18 --| Get_UOM_Code |
19 --| Get_Location |
20 --| Get_Vendor_Info |
21 --| |
22 --| HISTORY |
23 --| 10/01/02 Prabha Seshadri Created |
24 --+========================================================================
25
26 --===================
27 -- GLOBALS
28 --===================
29
30 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_THIRD_PARTY_STOCK_UTIL';
31 g_user_id NUMBER := FND_PROFILE.value('USER_ID');
32 g_appl_id NUMBER;
33 g_pgm_appl_id NUMBER := FND_PROFILE.value('RESP_APPL_ID');
34
35 TYPE ctx_value_rec_type IS RECORD (org_id NUMBER, resp_id NUMBER);
36 TYPE ctx_tbl_type IS TABLE OF ctx_value_rec_type INDEX BY BINARY_INTEGER;
37 g_context_tbl ctx_tbl_type;
38
39
40 --===================
41 -- PROCEDURES AND FUNCTIONS
42 --===================
43
44 --========================================================================
45 -- FUNCTION : Get_Org_id PUBLIC
46 -- PARAMETERS: p_vendor_site_id Vendor Site
47 -- COMMENT : Return the OU assocociated with the inventory orgn
48 --========================================================================
49
50 FUNCTION Get_Org_id(p_vendor_site_id IN NUMBER)
51 RETURN NUMBER
52 IS
53 l_org_id NUMBER;
54 BEGIN
55 SELECT
56 NVL(org_id,-99)
57 INTO
58 l_org_id
59 FROM
60 po_vendor_sites_all
61 WHERE vendor_site_id = p_vendor_site_id;
62
63 RETURN l_org_id;
64
65 END Get_Org_id;
66
67 --========================================================================
68 -- PROCEDURE : Set_OU_Context PUBLIC
69 -- PARAMETERS : p_org_id Operating Unit
70 -- COMMENT : Set the OU context
71 --========================================================================
72
73 PROCEDURE Set_OU_Context
74 ( p_org_id IN NUMBER
75 )
76 IS
77 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
78
79
80 BEGIN
81
82 IF (l_debug = 1)
83 THEN
84 INV_LOG_UTIL.trace
85 ( '>> In Set_OU_Context(p_org_id): '||p_org_id
86 ,'INV_THIRD_PARTY_STOCK_UTIL' , 9
87 );
88 END IF;
89
90 /*
91 MO_GLOBAL.Init('PO');
92 MO_GLOBAL.set_policy_context('S',p_org_id);
93 */
94
95 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_org_id);
96 MO_GLOBAL.set_policy_context('S',p_org_id);
97
98 EXCEPTION
99
100 WHEN OTHERS THEN
101 -- set message on the stack and raise failure
102 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_SET_OU_CXT');
103 FND_MSG_PUB.Add;
104 RAISE;
105
106 END Set_OU_Context;
107
108
109 --========================================================================
110 -- FUNCTION : Get_Primary_UOM PUBLIC
111 -- PARAMETERS: p_inventory_item_id Item
112 -- p_organization_id Inventory Organization
113 -- COMMENT : This function returns the primary UOM of the item
114 --========================================================================
115
116 FUNCTION Get_Primary_UOM
117 ( p_inventory_item_id IN NUMBER
118 , p_organization_id IN NUMBER
119 )
120 RETURN VARCHAR2
121 IS
122 l_uom VARCHAR2(25);
123 BEGIN
124
125 SELECT
126 primary_unit_of_measure
127 INTO
128 l_uom
129 FROM
130 mtl_system_items
131 WHERE inventory_item_id = p_inventory_item_id
132 AND organization_id = p_organization_id;
133
134 RETURN l_uom;
135
136 END Get_Primary_UOM;
137
138 --========================================================================
139 -- FUNCTION : Get_UOM_Code PUBLIC
140 -- PARAMETERS: p_unit_of_measure Unit of Measure
141 -- p_vendor_name Vendor
142 -- p_vendor_site_code Site
143 -- COMMENT : This function returns the UOM code for the item
144 --========================================================================
145
146 FUNCTION Get_UOM_Code
147 ( p_unit_of_measure IN VARCHAR2
148 , p_vendor_name IN VARCHAR2
149 , p_vendor_site_code IN VARCHAR2
150 )
151 RETURN VARCHAR2
152 IS
153 l_uom_code VARCHAR2(25);
154 BEGIN
155
156 SELECT
157 uom_code
158 INTO
159 l_uom_code
160 FROM
161 mtl_units_of_measure
162 WHERE unit_of_measure = p_unit_of_measure;
163
164 RETURN l_uom_code;
165
166
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 FND_MESSAGE.Set_Name('INV', 'INV_CONS_SUP_NO_UOM_CODE');
170 FND_MESSAGE.Set_Token('SuppName',p_vendor_name);
171 FND_MESSAGE.Set_Token('SiteCode',p_vendor_site_code);
172 FND_MSG_PUB.Add;
173 RAISE FND_API.G_EXC_ERROR;
174
175 END Get_UOM_Code;
176
177
178 --========================================================================
179 -- FUNCTION : Get_Location PUBLIC
180 -- PARAMETERS: p_organization_id Inventory Organization
181 -- COMMENT : This procedure will return the Location of the Inventory Orgn
182 --========================================================================
183
184 FUNCTION Get_Location
185 ( p_organization_id IN NUMBER
186 )
187 RETURN NUMBER
188 IS
189 l_location_id NUMBER;
190 BEGIN
191
192 SELECT
193 location_id
194 INTO
195 l_location_id
196 FROM
197 hr_all_organization_units
198 WHERE organization_id = p_organization_id;
199
200 RETURN l_location_id;
201 EXCEPTION -- Bug 6828643 Changes Start
202 WHEN NO_DATA_FOUND THEN
203 SELECT haou.location_id
204 INTO l_location_id
205 FROM hr_all_organization_units haou,
206 hr_operating_units hou
207 WHERE haou.organization_id = hou.organization_id;
208
209 RETURN l_location_id; -- Bug 6828643 Changes End
210 END Get_Location;
211
212 --========================================================================
213 -- PROCEDURE : Get_Vendor_Info PUBLIC
214 -- PARAMETERS:
215 -- p_vendor_site_id Vendor Site Id
216 -- x_vendor_name Vendor Name
217 -- x_vendor_site_code Vendor Site Code
218 -- COMMENT : Returns vendor name and vendor site code
219 --========================================================================
220 PROCEDURE Get_Vendor_Info
221 ( p_vendor_site_id IN NUMBER
222 , x_vendor_name OUT NOCOPY VARCHAR2
223 , x_vendor_site_code OUT NOCOPY VARCHAR2
224 )
225 IS
226 BEGIN
227
228 -- Get the vendor site code and vendor name
229
230 SELECT
231 pov.vendor_name
232 , povs.vendor_site_code
233 INTO
234 x_vendor_name
235 , x_vendor_site_code
236 FROM
237 po_vendors pov
238 , po_vendor_sites_all povs
239 WHERE pov.vendor_id = povs.vendor_id
240 AND povs.vendor_site_id = p_vendor_site_id;
241
242
243 END Get_Vendor_Info;
244
245
246 END INV_THIRD_PARTY_STOCK_UTIL;