1 PACKAGE BODY MRP_GET_PROJECT AS
2 /* $Header: MRPGPRJB.pls 120.2 2005/08/18 03:30:40 gmalhotr noship $*/
3 FUNCTION project (arg_project_id IN NUMBER)
4 return varchar2 IS
5 var_project_num VARCHAR2(30);
6 cursor C1 is
7 select segment1
8 from pa_projects_all
9 where project_id = arg_project_id
10 union
11 select project_number
12 from mrp_seiban_numbers
13 where project_id = arg_project_id;
14
15 BEGIN
16
17 IF arg_project_id is null THEN
18 return null;
19 END IF;
20
21 OPEN C1;
22 LOOP
23 FETCH C1 INTO var_project_num;
24 EXIT;
25 END LOOP;
26
27 return var_project_num;
28 END project;
29
30 FUNCTION task (arg_task_id IN NUMBER)
31 return varchar2 IS
32 var_task_num varchar2(25);
33 BEGIN
34 IF arg_task_id is null THEN
35 return null;
36 END IF;
37
38 select task_number
39 into var_task_num
40 from pa_tasks
41 where task_id = arg_task_id;
42
43 return var_task_num;
44 END task;
45
46 FUNCTION planning_group (arg_project_id IN NUMBER)
47 return varchar2 IS
48 var_plng_grp varchar2(30);
49 BEGIN
50
51 IF arg_project_id is null THEN
52 return null;
53 END IF;
54
55 select DISTINCT planning_group
56 into var_plng_grp
57 from mrp_project_parameters
58 where project_id = arg_project_id;
59
60 return var_plng_grp;
61 END planning_group;
62
63
64 FUNCTION lookup_fnd (arg_lookup_type IN varchar2, arg_lookup_code IN varchar2)
65 return fnd_lookups.meaning%type IS
66 meaning_text fnd_lookups.meaning%type;
67 CURSOR c1 is
68 select meaning
69 from fnd_lookups
70 where lookup_type = arg_lookup_type and lookup_code = arg_lookup_code;
71 BEGIN
72 IF arg_lookup_code is null or arg_lookup_code is null THEN
73 return null;
74 END IF;
75 OPEN c1;
76 FETCH c1 into meaning_text;
77 CLOSE c1;
78 return meaning_text;
79
80 EXCEPTION WHEN OTHERS THEN
81 IF c1%ISOPEN THEN
82 CLOSE c1;
83 END IF;
84 return null;
85 END lookup_fnd;
86
87 FUNCTION lookup_meaning(arg_lookup_type IN varchar2,
88 arg_lookup_code IN NUMBER) return varchar2 IS
89 meaning_text varchar2(80);
90 BEGIN
91 select lv.meaning
92 into meaning_text
93 from mfg_lookups lv
94 where lv.lookup_type = arg_lookup_type
95 and lv.lookup_code = arg_lookup_code;
96
97 return meaning_text;
98
99 EXCEPTION when no_data_found THEN
100 return null;
101 END lookup_meaning;
102
103 FUNCTION org_code(arg_org_id IN NUMBER) return varchar2 IS
104 org_code varchar2(3);
105 BEGIN
106
107 if arg_org_id is null then
108 return null;
109 end if;
110 select organization_code
111 into org_code
112 from mtl_parameters
113 where organization_id = arg_org_id;
114
115 return org_code;
116
117 EXCEPTION when no_data_found THEN
118 return null;
119 END org_code;
120
121 FUNCTION item_name(arg_org_id IN NUMBER,
122 arg_item_id IN NUMBER) return varchar2 IS
123 item_text varchar2(40);
124 BEGIN
125
126 if arg_org_id is null or
127 arg_item_id is null then
128 return null;
129 end if;
130 select concatenated_segments
131 into item_text
132 from mtl_system_items_kfv
133 where organization_id = arg_org_id
134 and inventory_item_id = arg_item_id;
135
136 return item_text;
137
138 EXCEPTION when no_data_found THEN
139 return null;
140 END item_name;
141
142 FUNCTION item_desc(arg_org_id IN NUMBER,
143 arg_item_id IN NUMBER) return varchar2 IS
144 item_desc varchar2(240);
145 BEGIN
146
147 if arg_org_id is null or
148 arg_item_id is null then
149 return null;
150 end if;
151 select description
152 into item_desc
153 from mtl_system_items_kfv
154 where organization_id = arg_org_id
155 and inventory_item_id = arg_item_id;
156
157 return item_desc;
158
159 EXCEPTION when no_data_found THEN
160 return null;
161 END item_desc;
162
163 FUNCTION category_desc(arg_cat_set_id IN NUMBER,
164 arg_cat_id IN NUMBER) return varchar2 IS
165 category_desc varchar2(240);
166 BEGIN
167
168 if arg_cat_set_id is null or
169 arg_cat_id is null then
170 return null;
171 end if;
172 select mcvl.description
173 into category_desc
174 from mtl_categories_vl mcvl,
175 mtl_category_sets mcs
176 where mcs.category_set_id = arg_cat_set_id
177 and mcs.structure_id = mcvl.structure_id
178 and mcvl.category_id = arg_cat_id;
179
180
181 return category_desc;
182
183 EXCEPTION when no_data_found THEN
184 return null;
185 END category_desc;
186
187 FUNCTION category_name(arg_cat_id IN NUMBER) return varchar2 IS
188 category_name varchar2(122);
189 BEGIN
190
191 if arg_cat_id is null then
192 return null;
193 end if;
194 select concatenated_segments
195 into category_name
196 from mtl_categories_kfv
197 where category_id = arg_cat_id;
198
199 return category_name;
200
201 EXCEPTION when no_data_found THEN
202 return null;
203 END category_name;
204
205 FUNCTION customer_name(arg_cust_id IN NUMBER) return varchar2 IS
206 cust_name varchar2(255);
207 BEGIN
208
209 if arg_cust_id is null then
210 return null;
211 end if;
212 select P.party_name
213 into cust_name
214 from HZ_CUST_ACCOUNTS A,
215 HZ_PARTIES P
216 where A.cust_account_id = arg_cust_id
217 AND A.PARTY_ID = P.PARTY_ID ;
218
219 return cust_name;
220
221 EXCEPTION when no_data_found THEN
222 return null;
223 END customer_name;
224
225 FUNCTION ship_to_address(arg_site_id IN NUMBER) return varchar2 IS
226 address_name varchar2(240);
227 BEGIN
228
229 if arg_site_id is null then
230 return null;
231 end if;
232 select A.address1
233 into address_name
234 from hz_locations A,
235 HZ_PARTY_SITES PS1,
236 HZ_CUST_ACCT_SITES_ALL AS1,
237 hz_cust_site_uses_all SU
238 where SU.site_use_id = arg_site_id
239 AND SU.CUST_ACCT_SITE_ID = AS1.CUST_ACCT_SITE_ID
240 AND AS1.PARTY_SITE_ID = PS1.PARTY_SITE_ID
241 AND PS1.LOCATION_ID = A.LOCATION_ID ;
242 return address_name;
243
244 EXCEPTION when no_data_found THEN
245 return null;
246 END ship_to_address;
247
248 FUNCTION vendor_site_code(arg_org_id IN NUMBER,
249 arg_vendor_site_id IN NUMBER) return varchar2 IS
250 v_vendor_site_code po_vendor_sites_all.vendor_site_code%type;
251 BEGIN
252
253 IF arg_vendor_site_id IS NULL THEN
254 RETURN(NULL);
255 END IF;
256
257 SELECT vendor_site_code INTO v_vendor_site_code
258 FROM po_vendor_sites_all site,
259 org_organization_definitions ood
260 WHERE site.vendor_site_id = arg_vendor_site_id
261 AND nvl(site.org_id,nvl(ood.operating_unit,-1)) =
262 nvl(ood.operating_unit,-1)
263 AND ood.organization_id = arg_org_id;
264
265
266 RETURN(v_vendor_site_code);
267
268 EXCEPTION when no_data_found THEN
269 return NULL;
270 END vendor_site_code;
271
272 END;
[Home] [Help]