[Home] [Help]
PACKAGE BODY: APPS.JA_CN_GSSM_EXP_PKG
Source
1 PACKAGE BODY JA_CN_GSSM_EXP_PKG AS
2 --$Header: JACNGSEB.pls 120.1.12000000.1 2007/08/13 14:09:39 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNGSEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used for GSSM Export, for Enterprise and |
13 --| Public Sector in the CNAO Project. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE GSSM_Export PUBLIC |
17 --| |
18 --| HISTORY |
19 -- 05/17/2006 Andrew Liu Created |
20 --+======================================================================*/
21
22 l_module_prefix VARCHAR2(100) :='JA_CN_GSSM_EXP_PKG';
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 -- GSSM_Export PUBLIC
27 --
28 -- DESCRIPTION:
29 -- This procedure calls GSSM Export program to export GSSM for
30 -- Enterprise.
31 --
32 -- PARAMETERS:
33 -- Out: errbuf NOCOPY VARCHAR2
34 -- Out: retcode NOCOPY VARCHAR2
35 -- In: P_GSSM_TYPE VARCHAR2 Type of ENT/PUB
36 --
37 -- DESIGN REFERENCES:
38 -- None
39 --
40 -- CHANGE HISTORY:
41 -- 05/17/2006 Andrew Liu Created
42 --===========================================================================
43 PROCEDURE GSSM_Export( errbuf OUT NOCOPY VARCHAR2
44 ,retcode OUT NOCOPY VARCHAR2
45 ,P_GSSM_TYPE IN VARCHAR2
46 ) IS
47 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
48 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
49 l_proc_name VARCHAR2(100) :='GSSM_Export';
50
51 JA_CN_NO_DATA_FOUND exception;
52 l_msg_no_data_found varchar2(2000); --'*****No data found*****';
53
54 l_lookup varchar2(100);
55 l_line FND_FLEX_VALUES_TL.DESCRIPTION%TYPE;
56 l_row_count NUMBER; --count of rows
57
58 --Cursor to get all GSSM.
59 CURSOR c_gssm IS
60 SELECT DECODE(SUBSTR(B.LOOKUP_CODE,LENGTH(B.LOOKUP_CODE)-2,3)
61 , '999', '' --For the blank lines
62 ,B.DESCRIPTION
63 )
64 FROM FND_LOOKUP_VALUES B
65 WHERE B.LANGUAGE = userenv('LANG')
66 AND b.lookup_type = l_lookup --using variable l_lookup
67 ORDER BY B.LOOKUP_CODE
68 ;
69
70 /*SELECT T.DESCRIPTION line
71 FROM FND_FLEX_VALUES_TL T
72 ,FND_FLEX_VALUES B
73 WHERE B.FLEX_VALUE_ID = T.FLEX_VALUE_ID
74 AND T.LANGUAGE = userenv('LANG')
75 AND B.FLEX_VALUE_SET_ID=
76 (SELECT V.FLEX_VALUE_SET_ID
77 FROM FND_FLEX_VALUE_SETS V
78 WHERE V.FLEX_VALUE_SET_NAME LIKE l_lookup --using variable l_lookup
79 )
80 ORDER BY B.FLEX_VALUE
81 ;*/
82 BEGIN
83 --log for debug
84 IF (l_proc_level >= l_dbg_level)
85 THEN
86 FND_LOG.String( l_proc_level
87 ,l_module_prefix||'.'||l_proc_name||'.begin'
88 ,'Enter procedure'
89 );
90 FND_LOG.String( l_proc_level
91 ,l_module_prefix||'.'||l_proc_name||'.parameters'
92 ,'P_GSSM_TYPE '||P_GSSM_TYPE
93 );
94 END IF; --(l_proc_level >= l_dbg_level)
95
96 --Check Profile
97 IF NOT(JA_CN_UTILITY.Check_Profile)
98 THEN
99 retcode := 1;
100 errbuf := '';
101 RETURN;
102 END IF;
103
104 IF P_GSSM_TYPE = 'ENT'
105 THEN
106 l_lookup := 'JA_CN_GSSM_ENT';
107 ELSIF P_GSSM_TYPE = 'PUB'
108 THEN
109 l_lookup := 'JA_CN_GSSM_PUB';
110 END IF;
111
112 --Export all lines into the format predefined flat file
113 l_row_count := 0;
114 OPEN c_gssm;
115 LOOP
116 FETCH c_gssm INTO l_line;
117 EXIT WHEN c_gssm%NOTFOUND;
118 l_row_count := l_row_count+1;
119
120 FND_FILE.put_line( FND_FILE.output
121 ,l_line
122 );
123 END LOOP;
124 CLOSE c_gssm;
125
126 IF l_row_count = 0 --No data found
127 THEN
128 raise JA_CN_NO_DATA_FOUND;
129 END IF;
130
131 retcode := 0;
132 errbuf := '';
133 EXCEPTION
134 WHEN JA_CN_NO_DATA_FOUND THEN
135 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
136 ,NAME => 'JA_CN_NO_DATA_FOUND'
137 );
138 l_msg_no_data_found := FND_MESSAGE.Get;
139
140 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
141 IF (l_proc_level >= l_dbg_level)
142 THEN
143 FND_LOG.String( l_proc_level
144 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
145 ,l_msg_no_data_found);
146 END IF;
147 retcode := 1;
148 errbuf := l_msg_no_data_found;
149 WHEN OTHERS THEN
150 IF (l_proc_level >= l_dbg_level)
151 THEN
152 FND_LOG.String( l_proc_level
153 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
154 ,SQLCODE||':'||SQLERRM
155 );
156 END IF; --(l_proc_level >= l_dbg_level)
157 retcode := 2;
158 errbuf := SQLCODE||':'||SQLERRM;
159
160 END GSSM_Export;
161 BEGIN
162 -- Initialization
163 null;
164 END JA_CN_GSSM_EXP_PKG;