[Home] [Help]
PACKAGE BODY: APPS.IGI_IGIGCBGD_XMLP_PKG
Source
1 PACKAGE BODY IGI_IGIGCBGD_XMLP_PKG AS
2 /* $Header: IGIGCBGDB.pls 120.0.12010000.1 2008/07/29 08:58:13 appldev ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
7 IF LP_FROM_PERIOD IS NOT NULL THEN
8 SELECT
9 EFFECTIVE_PERIOD_NUM
10 INTO CP_EFFECTIVE_PERIOD_FROM
11 FROM
12 GL_PERIOD_STATUSES
13 WHERE APPLICATION_ID = 101
14 AND SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
15 AND PERIOD_NAME = LP_FROM_PERIOD;
16 ELSE
17 SELECT
18 MIN(EFFECTIVE_PERIOD_NUM)
19 INTO CP_EFFECTIVE_PERIOD_FROM
20 FROM
21 GL_PERIOD_STATUSES
22 WHERE APPLICATION_ID = 101
23 AND SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
24 END IF;
25 IF P_TO_PERIOD IS NOT NULL THEN
26 SELECT
27 EFFECTIVE_PERIOD_NUM
28 INTO CP_EFFECTIVE_PERIOD_TO
29 FROM
30 GL_PERIOD_STATUSES
31 WHERE APPLICATION_ID = 101
32 AND SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
33 AND PERIOD_NAME = P_TO_PERIOD;
34 ELSE
35 SELECT
36 MAX(EFFECTIVE_PERIOD_NUM)
37 INTO CP_EFFECTIVE_PERIOD_TO
38 FROM
39 GL_PERIOD_STATUSES
40 WHERE APPLICATION_ID = 101
41 AND SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
42 END IF;
43 RETURN (TRUE);
44 END BEFOREREPORT;
45
46 FUNCTION AFTERREPORT RETURN BOOLEAN IS
47 BEGIN
48 DELETE FROM IGI_CBR_GL_INTERFACE;
49 COMMIT;
50 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
51 RETURN (TRUE);
52 END AFTERREPORT;
53
54 FUNCTION AFTERPFORM RETURN BOOLEAN IS
55 BEGIN
56 LP_FROM_PERIOD := P_FROM_PERIOD;
57 IF P_FROM_PERIOD IS NULL THEN
58 SELECT
59 GP.PERIOD_NAME
60 INTO P_FROM_PERIOD
61 FROM
62 GL_PERIODS GP,
63 GL_SETS_OF_BOOKS GSOB
64 WHERE GSOB.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
65 AND GSOB.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
66 AND GP.PERIOD_TYPE = GSOB.ACCOUNTED_PERIOD_TYPE
67 AND GP.PERIOD_NUM = 1
68 AND GP.PERIOD_YEAR = (
69 SELECT
70 G.PERIOD_YEAR
71 FROM
72 GL_PERIODS G,
73 GL_SETS_OF_BOOKS GS
74 WHERE GS.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
75 AND GS.PERIOD_SET_NAME = G.PERIOD_SET_NAME
76 AND G.PERIOD_NAME = P_TO_PERIOD );
77 END IF;
78 SELECT
79 DISTINCT
80 GP.PERIOD_NUM,
81 GP.PERIOD_YEAR
82 INTO LP_FROM_PERIOD_NUM,LP_FROM_PERIOD_YEAR
83 FROM
84 GL_PERIODS GP,
85 GL_SETS_OF_BOOKS GS
86 WHERE GP.PERIOD_NAME = P_FROM_PERIOD
87 AND GP.PERIOD_SET_NAME = GS.PERIOD_SET_NAME
88 AND GS.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
89 SELECT
90 GP.PERIOD_NUM,
91 GP.PERIOD_YEAR
92 INTO LP_TO_PERIOD_NUM,LP_TO_PERIOD_YEAR
93 FROM
94 GL_PERIODS GP,
95 GL_SETS_OF_BOOKS GSOB
96 WHERE GP.PERIOD_SET_NAME = GSOB.PERIOD_SET_NAME
97 AND GSOB.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
98 AND GP.PERIOD_NAME = P_TO_PERIOD;
99 SELECT
100 NAME,
101 CURRENCY_CODE
102 INTO LP_LEDGER_NAME,LP_CURRENCY_CODE
103 FROM
104 GL_SETS_OF_BOOKS
105 WHERE SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID;
106 SELECT
107 NAME
108 INTO LP_CASH_SOB_NAME
109 FROM
110 GL_SETS_OF_BOOKS
111 WHERE SET_OF_BOOKS_ID = P_CASH_SOB_ID;
112 SELECT
113 count(*)
114 INTO LP_UNPOSTED_JOURNALS
115 FROM
116 GL_JE_HEADERS GJH,
117 GL_JE_BATCHES GJB
118 WHERE GJH.LEDGER_ID = P_CASH_SOB_ID
119 AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
120 AND GJB.STATUS <> 'P';
121 SELECT_INTO_TABLE;
122 COMMIT;
123 RETURN (TRUE);
124 END AFTERPFORM;
125
126 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
127 BEGIN
128 RETURN (TRUE);
129 END BEFOREPFORM;
130
131 FUNCTION CP_EFFECTIVE_PERIOD_FROM_P RETURN NUMBER IS
132 BEGIN
133 RETURN CP_EFFECTIVE_PERIOD_FROM;
134 END CP_EFFECTIVE_PERIOD_FROM_P;
135
136 FUNCTION CP_EFFECTIVE_PERIOD_TO_P RETURN NUMBER IS
137 BEGIN
138 RETURN CP_EFFECTIVE_PERIOD_TO;
139 END CP_EFFECTIVE_PERIOD_TO_P;
140
141 PROCEDURE SELECT_INTO_TABLE IS
142 L_SEGMENT VARCHAR2(25);
143 L_INSERT VARCHAR2(5000);
144 BEGIN
145 L_SEGMENT := P_ACCT_SEGMENT;
146 L_INSERT := ' INSERT INTO igi_cbr_gl_interface
147 ( BATCH_ID, LEDGER_ID, STATUS, -- bug 6315298
148 ACCOUNT,
149 DOC_SEQ_NUM,
150 NAME,
151 JE_SOURCE,
152 DESCRIPTION,
153 EFFECTIVE_DATE,
154 AMOUNT
155 )
156 SELECT
157 distinct gjb.je_batch_id, gjh.ledger_id, gjb.status, -- bug 6315298
158 gcc.' || L_SEGMENT || ' Account
159 , nvl(gjl.subledger_doc_sequence_value, gjh.doc_sequence_value) Document_Sequence_Number
160 , gjh.name Document_Sequence_Name
161 , gjh.je_source Journal_Source
162 , gjl.description Description
163 , gjl.effective_date Effective_Date
164 , (nvl(gjl.accounted_dr,0) - nvl(gjl.accounted_cr,0)) Amount
165 FROM gl_period_statuses gls
166 -- , igi_com_gl_batches_copy_v jgb -- bug 6315298
167 , gl_je_batches gjb , gl_lookups l -- bug 6315298
168 , gl_code_combinations gcc
169 , gl_je_headers gjh
170 , gl_je_lines gjl
171 WHERE gjb.je_batch_id = gjh.je_batch_id
172 and l.lookup_type = ''BATCH_TYPE'' -- bug 6315298
173 and l.lookup_code = gjb.actual_flag -- bug 6315298
174 AND gjh.je_header_id = gjl.je_header_id
175 AND gjh.actual_flag = ''A''
176 AND gjh.je_source like replace(''' || P_JE_SOURCE || ''', ''ALL'', ''%'')
177 AND gjh.je_source not in (''Payables'',''Receivables'',''Purchasing'')
178 AND gjh.je_source not in ( select arc_je_source_name
179 from igi_ar_system_options_all
180 where arc_je_source_name is not null)
181 AND gjl.period_name = gls.period_name
182 AND gjl.ledger_id = gls.set_of_books_id
183 AND gls.application_id = 101
184 AND gjl.ledger_id = ' || P_SET_OF_BOOKS_ID || ' -- replaced set_of_books_id to ledger_id for bug 6315298
185 AND gjl.code_combination_id = gcc.code_combination_id
186 AND gcc.' || L_SEGMENT || ' between ''' || P_FROM_ACCOUNT || ''' and ''' || P_TO_ACCOUNT || '''
187 AND to_number(gls.period_year||lpad(gls.period_num,2,0)) between to_number(' || LP_FROM_PERIOD_YEAR || '||(lpad(' || LP_FROM_PERIOD_NUM || ',2,0))) and to_number(' || LP_TO_PERIOD_YEAR || '||(lpad(' || LP_TO_PERIOD_NUM || ',2,0)))
188 AND gcc.account_type in (''A'', ''O'', ''L'')
189 and gjb.status = ''P'' -- bug 6315298
190 UNION ALL
191 SELECT
192 distinct gjb.je_batch_id, gjh.ledger_id, gjb.status, -- bug 6315298
193 gcc.' || L_SEGMENT || ' Account
194 , nvl(gjl.subledger_doc_sequence_value, gjh.doc_sequence_value) Document_Sequence_Number
195 , gjh.name Document_Sequence_Name
196 , gjh.je_source Journal_Source
197 , gjl.description Description
198 , gjl.effective_date Effective_Date
199 , (nvl(gjl.accounted_dr,0) - nvl(gjl.accounted_cr,0)) Amount
200 FROM gl_period_statuses gls
201 -- , igi_com_gl_batches_copy_v jgb -- bug 6315298
202 , gl_je_batches gjb, gl_lookups l -- bug 6315298
203 , gl_code_combinations gcc
204 , gl_je_headers gjh
205 , gl_je_lines gjl
206 WHERE gjb.je_batch_id = gjh.je_batch_id
207 and l.lookup_type = ''BATCH_TYPE'' -- bug 6315298
208 and l.lookup_code = gjb.actual_flag and gjb.status = ''P'' -- bug 6315298
209 AND gjh.je_header_id = gjl.je_header_id
210 AND gjh.actual_flag = ''A''
211 AND gjh.je_source like replace(''' || P_JE_SOURCE || ''', ''ALL'', ''%'')
212 AND gjh.je_source not in (''Payables'',''Receivables'',''Purchasing'')
213 AND gjh.je_source not in ( select arc_je_source_name
214 from igi_ar_system_options_all
215 where arc_je_source_name is not null)
216 AND gjl.period_name = gls.period_name
217 AND gjl.ledger_id = gls.set_of_books_id
218 AND gls.application_id = 101
219 AND gjl.ledger_id = ' || P_SET_OF_BOOKS_ID || ' -- bug 6315298
220 AND gjl.code_combination_id = gcc.code_combination_id
221 AND gcc.' || L_SEGMENT || ' between ''' || P_FROM_ACCOUNT || ''' and ''' || P_TO_ACCOUNT || '''
222 AND to_number(gls.period_year||lpad(gls.period_num,2,0)) between to_number(' || LP_FROM_PERIOD_YEAR || '||(lpad(' || LP_FROM_PERIOD_NUM || ',2,0))) and to_number(' || LP_TO_PERIOD_YEAR || '||(lpad(' || LP_TO_PERIOD_NUM || ',2,0)))
223 AND gls.period_year = ' || LP_TO_PERIOD_YEAR || '
224 AND gcc.account_type not in (''A'', ''O'', ''L'')';
225 EXECUTE IMMEDIATE
226 L_INSERT;
227 END SELECT_INTO_TABLE;
228
229 END IGI_IGIGCBGD_XMLP_PKG;