1 PACKAGE BODY GMO_UTILITIES AS
2 /* $Header: GMOUTILB.pls 120.2 2005/11/09 02:34 bchopra noship $ */
3
4 PROCEDURE GET_WHO_COLUMNS
5 (
6 x_creation_date out nocopy date,
7 x_created_by out nocopy number,
8 x_last_update_date out nocopy date,
9 x_last_updated_by out nocopy number,
10 x_last_update_login out nocopy number
11 )
12 IS
13 BEGIN
14 x_creation_date := sysdate;
15 x_created_by := fnd_global.user_id();
16 x_last_update_date := sysdate;
17 x_last_updated_by := fnd_global.user_id();
18 x_last_update_login := fnd_global.login_id();
19 END GET_WHO_COLUMNS;
20
21
22
23 --This function would return the user display name for the user
24 function GET_USER_DISPLAY_NAME (P_USER_NAME IN VARCHAR2) RETURN VARCHAR2
25
26 IS
27
28 l_displayname varchar2(400);
29 l_emailaddress varchar2(400);
30 l_notification_preference varchar2(30);
31 l_language varchar2(30);
32 l_teritory varchar2(30);
33
34 begin
35 wf_directory.getroleinfo(
36 ROLE => P_USER_NAME,
37 DISPLAY_NAME => l_displayname,
38 EMAIL_ADDRESS => l_emailaddress,
39 NOTIFICATION_PREFERENCE => l_notification_preference,
40 LANGUAGE => l_language,
41 TERRITORY => l_teritory
42 );
43
44 return l_displayname;
45
46 END GET_USER_DISPLAY_NAME;
47
48
49 --This function would return the user display name for the user
50 function GET_USER_DISPLAY_NAME (P_USER_ID IN NUMBER) RETURN VARCHAR2
51
52 IS
53
54 cursor c_get_user_name is select user_name from fnd_user where user_id = P_USER_ID;
55 l_username varchar2(400);
56
57 begin
58 open c_get_user_name;
59 fetch c_get_user_name into l_username;
60 close c_get_user_name;
61
62
63
64 return GET_USER_DISPLAY_NAME(l_username);
65
66 END GET_USER_DISPLAY_NAME;
67
68 PROCEDURE GET_USER_DISPLAY_NAME (P_USER_ID IN NUMBER, P_USER_DISPLAY_NAME OUT nocopy VARCHAR2) as
69
70 BEGIN
71
72 P_USER_DISPLAY_NAME:=get_user_display_name(P_USER_ID);
73
74 END GET_USER_DISPLAY_NAME;
75
76 PROCEDURE GET_MFG_LOOKUP(P_LOOKUP_TYPE IN VARCHAR2,
77 P_LOOKUP_CODE IN VARCHAR2,
78 P_MEANING OUT NOCOPY VARCHAR2) IS
79
80 CURSOR GET_LKUP_MEANING IS SELECT MEANING FROM MFG_LOOKUPS WHERE LOOKUP_TYPE = P_LOOKUP_TYPE AND LOOKUP_CODE = P_LOOKUP_CODE;
81
82 BEGIN
83 OPEN GET_LKUP_MEANING;
84 FETCH GET_LKUP_MEANING INTO P_MEANING;
85 CLOSE GET_LKUP_MEANING;
86
87 END GET_MFG_LOOKUP;
88
89 function GET_LOOKUP_MEANING (P_LOOKUP_TYPE IN VARCHAR2, P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2
90 IS
91
92 CURSOR GET_LKUP_MEANING IS SELECT MEANING FROM FND_LOOKUPS WHERE LOOKUP_TYPE = P_LOOKUP_TYPE AND LOOKUP_CODE = P_LOOKUP_CODE;
93 l_meaning varchar2(300);
94
95 BEGIN
96 OPEN GET_LKUP_MEANING;
97 FETCH GET_LKUP_MEANING INTO l_MEANING;
98 CLOSE GET_LKUP_MEANING;
99 return l_meaning;
100
101 END GET_LOOKUP_MEANING;
102
103 PROCEDURE GET_LOOKUP
104 (
105 P_LOOKUP_TYPE IN VARCHAR2,
106 P_LOOKUP_CODE IN VARCHAR2,
107 X_MEANING OUT NOCOPY VARCHAR2
108 ) IS
109 begin
110 x_meaning := GET_LOOKUP_MEANING(P_LOOKUP_TYPE, P_LOOKUP_CODE);
111
112 end;
113
114 procedure get_organization (P_BATCH_ID IN NUMBER,
115 X_ORG_ID OUT NOCOPY NUMBER,
116 X_ORG_CODE OUT NOCOPY VARCHAR2,
117 X_ORG_NAME OUT NOCOPY VARCHAR2) IS
118
119
120 cursor get_org_details is
121 select organization_id
122 from gme_batch_header
123 where batch_id = p_batch_id;
124
125 begin
126
127 open get_org_details;
128 fetch get_org_details into X_org_id;
129 close get_org_details;
130 get_organization(P_ORG_ID => X_org_id,
131 X_ORG_CODE => X_ORG_CODE,
132 X_ORG_NAME => X_ORG_NAME);
133
134 end get_organization;
135
136 procedure get_organization (P_ORG_ID IN NUMBER,
137 X_ORG_CODE OUT NOCOPY VARCHAR2,
138 X_ORG_NAME OUT NOCOPY VARCHAR2) IS
139 l_org_code varchar2(3);
140 l_org_name varchar2(240);
141 cursor get_org_details IS
142 SELECT oav.organization_code,
143 oav.organization_name
144 FROM org_access_view oav
145 WHERE oav.organization_id = P_ORG_ID
146 AND oav.responsibility_id = fnd_global.resp_id
147 and oav.resp_application_id = fnd_global.resp_appl_id;
148
149 BEGIN
150 OPEN get_org_details;
151 FETCH get_org_details into X_ORG_CODE, X_ORG_NAME;
152 CLOSE get_org_details;
153 END get_organization;
154
155 END GMO_UTILITIES;
[Home] [Help]