1 PACKAGE BODY GL_LEDGER_UTILS_PKG AS
2 /* $Header: gluldgub.pls 120.4 2005/01/05 18:06:58 djogg noship $ */
3
4
5 --
6 -- PUBLIC FUNCTIONS
7 --
8 -- *********************************************************************
9
10 PROCEDURE Find_Ledger(X_Ledger_Short_Name VARCHAR2,
11 X_Ledger_Id OUT NOCOPY NUMBER,
12 X_Ledger_Currency OUT NOCOPY VARCHAR2,
13 X_Translated_Flag OUT NOCOPY VARCHAR2) IS
14 CURSOR c_ledger IS
15 SELECT ledger_id, decode(object_type_code, 'S', NULL, currency_code), 'N'
16 FROM GL_LEDGERS
17 WHERE short_name = X_Ledger_Short_Name;
18
19 CURSOR c_ledger_relationship IS
20 SELECT target_ledger_id, target_currency_code,
21 decode(relationship_type_code, 'BALANCE', 'Y', 'N')
22 FROM GL_LEDGER_RELATIONSHIPS
23 WHERE target_ledger_short_name = X_Ledger_Short_Name;
24 BEGIN
25 OPEN c_ledger;
26 FETCH c_ledger INTO X_Ledger_Id, X_Ledger_Currency, X_Translated_Flag;
27 IF (c_ledger%FOUND) THEN
28 CLOSE c_ledger;
29 RETURN;
30 END IF;
31 CLOSE c_ledger;
32
33 -- Or it could be a translated ALC.
34 OPEN c_ledger_relationship;
35 FETCH c_ledger_relationship INTO X_Ledger_Id, X_Ledger_Currency,
36 X_Translated_Flag;
37 CLOSE c_ledger_relationship;
38 END Find_Ledger;
39
40 -- *********************************************************************
41
42 PROCEDURE Find_Ledger_Short_Name(X_Ledger_Id NUMBER,
43 X_Ledger_Currency VARCHAR2,
44 X_Ledger_Short_Name OUT NOCOPY VARCHAR2,
45 X_Translated_Flag OUT NOCOPY VARCHAR2) IS
46 CURSOR c_ledger IS
47 SELECT short_name, 'N'
48 FROM GL_LEDGERS
49 WHERE ledger_id = X_Ledger_Id
50 AND ( object_type_code = 'S'
51 OR decode(X_Ledger_Currency, Null, 'Y', currency_code) =
52 nvl(X_Ledger_Currency, 'Y') );
53
54 CURSOR c_ledger_relationship IS
55 SELECT target_ledger_short_name,
56 decode(relationship_type_code, 'BALANCE', 'Y', 'N')
57 FROM GL_LEDGER_RELATIONSHIPS
58 WHERE target_ledger_id = X_Ledger_Id
59 AND target_currency_code = X_Ledger_Currency
60 AND application_id = 101;
61 BEGIN
62 OPEN c_ledger;
63 FETCH c_ledger INTO X_Ledger_Short_Name, X_Translated_Flag;
64 IF (c_ledger%FOUND) THEN
65 CLOSE c_ledger;
66 RETURN;
67 END IF;
68 CLOSE c_ledger;
69
70 -- Or it could be a translated ALC.
71 OPEN c_ledger_relationship;
72 FETCH c_ledger_relationship INTO X_Ledger_Short_Name, X_Translated_Flag;
73 CLOSE c_ledger_relationship;
74
75 END Find_Ledger_Short_Name;
76
77 -- *********************************************************************
78
79 FUNCTION Get_Ledger_Id_Of_Short_Name(X_Ledger_Short_Name VARCHAR2)
80 RETURN NUMBER
81 IS
82 ledger_id NUMBER;
83 ledger_curr VARCHAR2(15);
84 trans_flag VARCHAR2(1);
85 BEGIN
86 Find_Ledger(X_Ledger_Short_Name, ledger_id, ledger_curr, trans_flag);
87 RETURN ledger_id;
88 END Get_Ledger_Id_Of_Short_Name;
89
90 -- *********************************************************************
91
92 FUNCTION Get_Default_Ledger_Currency(X_Ledger_Short_Name VARCHAR2)
93 RETURN VARCHAR2
94 IS
95 CURSOR c_ledger IS
96 SELECT target_currency_code
97 FROM GL_LEDGER_RELATIONSHIPS
98 WHERE target_ledger_short_name = X_Ledger_Short_Name;
99
100 CURSOR c_ledger_set IS
101 SELECT currency_code
102 FROM GL_LEDGERS
103 WHERE ledger_id = (select default_ledger_id
104 from GL_ACCESS_SETS
105 where access_set_id =
106 (select implicit_access_set_id
107 from GL_LEDGERS
108 where short_name = X_Ledger_Short_Name));
109 default_currency VARCHAR2(30);
110 BEGIN
111 OPEN c_ledger;
112 FETCH c_ledger INTO default_currency;
113 IF (c_ledger%FOUND) THEN
114 CLOSE c_ledger;
115 RETURN default_currency;
116 END IF;
117 CLOSE c_ledger;
118
119 -- Or it could be a ledger set.
120 OPEN c_ledger_set;
121 FETCH c_ledger_set INTO default_currency;
122 CLOSE c_ledger_set;
123
124 RETURN default_currency;
125 END Get_Default_Ledger_Currency;
126
127 -- *********************************************************************
128
129 PROCEDURE Get_First_Ledger_Id_From_Set(
130 X_Ledger_Set_Id IN NUMBER,
131 X_Ledger_Currency IN VARCHAR2,
132 X_Ledger_Id OUT NOCOPY NUMBER,
133 X_Errbuf OUT NOCOPY varchar2)
134 IS
135 CURSOR LedgerID(p_ledger_set_id number, p_ledger_currency varchar2) IS
136 SELECT TARGET_LEDGER_ID
137 FROM GL_LEDGER_SET_ASSIGNMENTS ASG,
138 GL_LEDGER_RELATIONSHIPS LR
139 WHERE ASG.LEDGER_SET_ID = P_LEDGER_SET_ID
140 AND LR.TARGET_LEDGER_ID = ASG.LEDGER_ID
141 AND LR.SOURCE_LEDGER_ID = ASG.LEDGER_ID
142 AND LR.TARGET_CURRENCY_CODE = P_LEDGER_CURRENCY;
143 l_ledger_id number:= -1;
144 BEGIN
145 X_Ledger_Id := -1;
146
147 OPEN LedgerID(X_Ledger_Set_Id, X_Ledger_Currency);
148 LOOP
149 FETCH LedgerID INTO l_ledger_id;
150 EXIT WHEN LedgerID%NOTFOUND;
151 X_Ledger_Id := l_ledger_id;
152 IF X_Ledger_Id <> -1 THEN --found the first ledger ID in a ledger set
153 EXIT;
154 END IF;
155
156 END LOOP;
157 CLOSE LedgerID;
158
159 --the input ledger set id is actually a ledger id, not a ledger set id
160 IF (l_ledger_id = -1) OR (X_Ledger_Id = -1) THEN
161 X_Ledger_Id := X_Ledger_Set_Id;
162 END IF;
163
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 X_Errbuf := SQLERRM;
167 WHEN OTHERS THEN
168 X_Errbuf := SQLERRM;
169
170 END Get_First_Ledger_Id_From_Set;
171
172 -- *********************************************************************
173
174 END GL_LEDGER_UTILS_PKG;
[Home] [Help]