1 PACKAGE BODY IGC_LEDGER_UTILS AS
2 /* $Header: IGCLUTLB.pls 120.2.12000000.1 2007/10/25 09:20:10 mbremkum noship $ */
3 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCLUTLS.IGC_LEDGER_UTILS.';
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_LEDGER_UTILS';
5
6 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
8 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
10 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
12 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
13 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
14
15 FUNCTION is_bc_enabled
16 (p_ledger_id IN NUMBER,
17 p_doc_type IN varchar2) RETURN VARCHAR2;
18
19
20 FUNCTION is_bc_enabled
21 (p_ledger_id IN NUMBER,
22 p_doc_type IN varchar2) RETURN VARCHAR2 IS
23 l_full_path VARCHAR2(500) := g_path||'is_bc_enabled';
24
25 CURSOR c_bc_enabled IS
26 SELECT cc_bc_enable_flag
27 ,cbc_po_enable
28 FROM igc_cc_bc_enable
29 WHERE set_of_books_id = p_ledger_id;
30
31 l_bc_enable igc_cc_bc_enable.cc_bc_enable_flag%TYPE;
32 l_po_enable igc_cc_bc_enable.cbc_po_enable%TYPE;
33 BEGIN
34 l_full_path := g_path || 'Is_Cbc_Enabled';
35 OPEN c_bc_enabled;
36 FETCH c_bc_enabled INTO l_bc_enable, l_po_enable;
37 CLOSE c_bc_enabled;
38
39 IF (p_doc_type in ('CC','ANY') AND nvl(l_bc_enable,'N') = 'Y') THEN
40 RETURN FND_API.G_TRUE;
41 ELSIF (p_doc_type in ('PO','ANY') AND nvl(l_po_enable,'N') = 'Y') THEN
42 RETURN FND_API.G_TRUE;
43 END IF;
44
45 RETURN FND_API.G_FALSE;
46 EXCEPTION
47 WHEN OTHERS THEN
48 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
49 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
50 'Is_bc_enabled');
51 END IF;
52 IF ( g_unexp_level >= g_debug_level ) THEN
53 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
54 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
55 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
56 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
57 END IF;
58 RETURN FND_API.G_FALSE;
59 END;
60
61
62 -- To check Dual budgetary is enabled for Contract Commitment
63 FUNCTION is_cc_dual_bc_enabled
64 (p_ledger_id NUMBER) RETURN VARCHAR2 IS
65 BEGIN
66 RETURN is_bc_enabled(p_ledger_id, 'CC');
67 END;
68
69 -- To check Dual budgetary is enabled for Purchase Order
70 FUNCTION is_po_dual_bc_enabled
71 (p_ledger_id NUMBER) RETURN VARCHAR2 IS
72 BEGIN
73 RETURN is_bc_enabled(p_ledger_id, 'PO');
74 END;
75
76 -- To check Dual budgetary is enabled for either PO or CC
77 FUNCTION is_dual_bc_enabled
78 (p_ledger_id NUMBER
79 ) RETURN VARCHAR2
80 IS
81 BEGIN
82 RETURN is_bc_enabled(p_ledger_id, 'ANY');
83 END is_dual_bc_enabled;
84
85 -- To check Dual budgetary is enabled for either PO or CC
86 FUNCTION is_dual_bc_enabled
87 (p_ledger_id IN NUMBER
88 , p_ledger_category IN VARCHAR2) RETURN VARCHAR2 IS
89 l_primary_ledger_id GL_LEDGERS.LEDGER_ID%TYPE;
90 CURSOR c_primary_ledger IS
91 SELECT primary_ledger_id
92 FROM GL_LEDGER_RELATIONSHIPS
93 WHERE target_ledger_category_code = 'SECONDARY'
94 AND relationship_type_code <> 'NONE'
95 AND application_id = 101
96 AND target_ledger_id = p_ledger_id;
97 BEGIN
98 IF p_ledger_category = 'SECONDARY' THEN
99 OPEN c_primary_ledger;
100 FETCH c_primary_ledger INTO l_primary_ledger_id;
101 CLOSE c_primary_ledger;
102 ELSE
103 l_primary_ledger_id := p_ledger_id;
104 END IF;
105
106 IF l_primary_ledger_id is NULL THEN
107 RETURN FND_API.G_FALSE;
108 ELSE
109 RETURN is_bc_enabled(l_primary_ledger_id, 'ANY');
110 END IF;
111 END is_dual_bc_enabled;
112
113 -- Get Commitment Ledger Id for a Primary Ledger.
114 PROCEDURE get_cbc_ledger
115 (p_primary_ledger_id IN NUMBER,
116 p_cbc_ledger_id OUT NOCOPY NUMBER,
117 p_cbc_ledger_Name OUT NOCOPY VARCHAR2) IS
118
119 CURSOR c_cbc_ledger IS
120 SELECT SEC.LEDGER_ID,
121 SEC.LEDGER_NAME
122 FROM GL_SECONDARY_LEDGER_RSHIPS_V SEC
123 , GL_LEDGERS LED
124 WHERE LED.LEDGER_ID = SEC.LEDGER_ID
125 AND LED.COMMITMENT_BUDGET_FLAG = 'Y'
126 AND SEC.PRIMARY_LEDGER_ID = p_primary_ledger_id;
127
128 l_full_path VARCHAR2(500) := g_path||'get_cbc_ledger';
129 l_sec_ledger_id GL_LEDGERS.LEDGER_ID%TYPE;
130 l_sec_ledger_name GL_LEDGERS.NAME%TYPE;
131
132 BEGIN
133 OPEN c_cbc_ledger;
134 FETCH c_cbc_ledger INTO l_sec_ledger_id, l_sec_ledger_name;
135 CLOSE c_cbc_ledger;
136
137 p_cbc_ledger_id := l_sec_ledger_id;
138 p_cbc_ledger_name := l_sec_ledger_name;
139 EXCEPTION
140 WHEN OTHERS THEN
141 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
142 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
143 'get_cbc_ledger');
144 END IF;
145 IF ( g_unexp_level >= g_debug_level ) THEN
146 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
147 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
148 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
149 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
150 END IF;
151 END;
152
153 END IGC_LEDGER_UTILS;