[Home] [Help]
PACKAGE BODY: APPS.JG_ALLOCATE_JOURNALS_PKG
Source
1 PACKAGE BODY JG_ALLOCATE_JOURNALS_PKG AS
2 /* $Header: jgzztakb.pls 120.3 2004/02/20 08:53:56 fholst ship $ */
3
4 /* ---------------------------------------------------------------------
5 | PRIVATE PROCEDURE |
6 | prepare_journal_select |
7 | DESCRIPTION |
8 | Prepare the journal select dynamic SQL |
9 | CALLED BY |
10 | allocate |
11 --------------------------------------------------------------------- */
12 PROCEDURE prepare_journal_select IS
13 l_fiscal_journal_qry VARCHAR2(10000) := NULL;
14 BEGIN
15 JG_UTILITY_PKG.log('> JG_ALLOCATE_JOURNALS_PKG.prepare_journal_select');
16 --
17 -- prepare the query variable
18 --
19 l_fiscal_journal_qry := JG_ALLOCATE_JOURNALS_PKG.get_dynamic_select_string;
20 --
21 -- Prepare the main Dynamic SQL statement
22 --
23 JG_UTILITY_PKG.debug('Initialize dynamic cursor: start');
24 JG_UTILITY_PKG.debug(SUBSTR(l_fiscal_journal_qry,1,130));
25 JG_UTILITY_PKG.debug(SUBSTR(l_fiscal_journal_qry,
26 INSTR(l_fiscal_journal_qry,'FROM',1),
27 100));
28 JG_UTILITY_PKG.debug(SUBSTR(SUBSTR(l_fiscal_journal_qry,
29 INSTR(l_fiscal_journal_qry, 'WHERE', 1),
30 400),1,100));
31 JG_UTILITY_PKG.debug(SUBSTR(SUBSTR(l_fiscal_journal_qry,
32 INSTR(l_fiscal_journal_qry, 'WHERE', 1),
33 400),101,200));
34 JG_UTILITY_PKG.debug(SUBSTR(SUBSTR(l_fiscal_journal_qry,
35 INSTR(l_fiscal_journal_qry, 'WHERE', 1),
36 400),201,300));
37 JG_UTILITY_PKG.debug(SUBSTR(SUBSTR(l_fiscal_journal_qry,
38 INSTR(l_fiscal_journal_qry, 'WHERE', 1),
39 400),301,400));
40 JG_UTILITY_PKG.debug(SUBSTR(SUBSTR(l_fiscal_journal_qry,
41 INSTR(l_fiscal_journal_qry, 'WHERE', 1),
42 400),401,500));
43 JG_UTILITY_PKG.debug(SUBSTR(SUBSTR(l_fiscal_journal_qry,
44 INSTR(l_fiscal_journal_qry, 'WHERE', 1),
45 400),501,600));
46 --
47 -- Open the main cursor
48 --
49 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c := DBMS_SQL.OPEN_CURSOR;
50 DBMS_SQL.PARSE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, l_fiscal_journal_qry, DBMS_SQL.NATIVE);
51 --
52 -- Column Definitions listed in dynamic select clause by datatype
53 --
54 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,1,
55 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_batch_id);
56 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,2,
57 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_batch_name, 100);
58 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,3,
59 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_header_id);
60 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,4,
61 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_header_name, 100);
62 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,5,
63 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_code, 15);
64 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,6,
65 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_conversion_type, 30);
66 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,7,
67 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_conversion_date);
68 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,8,
69 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_conversion_rate);
70 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,9,
71 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.encumbrance_type_id);
72 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,10,
73 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.budget_version_id);
74 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,11,
75 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cost_center, 25);
76 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,12,
77 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_number, 25);
78 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,13,
79 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment1, 25);
80 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,14 ,
81 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment2, 25);
82 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,15 ,
83 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment3, 25);
84 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,16 ,
85 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment4, 25);
86 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,17 ,
87 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment5, 25);
88 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,18 ,
89 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment6, 25);
90 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,19 ,
91 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment7, 25);
92 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,20 ,
93 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment8, 25);
94 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,21 ,
95 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment9, 25);
96 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,22 ,
97 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment10, 25);
98 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,23 ,
99 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment11, 25);
100 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,24 ,
101 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment12, 25);
102 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,25 ,
103 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment13, 25);
104 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,26 ,
105 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment14, 25);
106 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,27 ,
107 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment15, 25);
108 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,28 ,
109 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment16, 25);
110 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,29 ,
111 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment17, 25);
112 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,30 ,
113 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment18, 25);
114 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,31 ,
115 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment19, 25);
116 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,32 ,
117 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment20, 25);
118 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,33 ,
119 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment21, 25);
120 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,34 ,
121 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment22, 25);
122 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,35 ,
123 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment23, 25);
124 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,36 ,
125 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment24, 25);
126 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,37 ,
127 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment25, 25);
128 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,38 ,
129 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment26, 25);
130 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,39 ,
131 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment27, 25);
132 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,40 ,
133 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment28, 25);
134 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,41 ,
135 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment29, 25);
136 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,42 ,
137 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment30, 25);
138 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,43 ,
139 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_line_num);
140 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,44 ,
141 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.accounted_cr);
142 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,45 ,
143 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.accounted_dr);
144 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,46 ,
145 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.entered_cr);
146 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,47 ,
147 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.entered_dr);
148 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,48 ,
149 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.stat_amount);
150 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,49 ,
151 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.subledger_doc_sequence_id);
152 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,50 ,
153 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.subledger_doc_sequence_value);
154 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,51 ,
155 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute1, 150);
156 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,52 ,
157 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute2, 150);
158 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,53 ,
159 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute3, 150);
160 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,54 ,
161 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute4, 150);
162 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,55 ,
163 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute5, 150);
164 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,56 ,
165 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute6, 150);
166 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,57 ,
167 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute7, 150);
168 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,58 ,
169 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute8, 150);
170 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,59 ,
171 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute9, 150);
172 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,60 ,
173 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute10, 150);
174 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,61 ,
175 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute11, 150);
176 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,62 ,
177 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute12, 150);
178 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,63 ,
179 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute13, 150);
180 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,64 ,
181 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute14, 150);
182 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,65 ,
183 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute15, 150);
184 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,66 ,
185 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute16, 150);
186 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,67 ,
187 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute17, 150);
188 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,68 ,
189 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute18, 150);
190 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,69 ,
191 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute19, 150);
192 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,70 ,
193 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute20, 150);
194 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,71 ,
195 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.context, 150);
196 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,72 ,
197 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.context2, 150);
198 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,73 ,
199 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.context3, 150);
200 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,74 ,
201 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.invoice_date);
202 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,75 ,
203 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.tax_code, 15);
204 DBMS_SQL.DEFINE_COLUMN( JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,76 ,
205 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.invoice_identifier, 20);
206 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,77,
207 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.invoice_amount);
208 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,78,
209 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.ussgl_transaction_code, 30);
210 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,79 ,
211 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.jgzz_recon_ref, 240);
212 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 80,
213 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.code_combination_id);
214 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,81 ,
215 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.row_id,50);
216 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,82,
217 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.effective_date);
218 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,83,
219 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.external_reference,80);
220 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,84,
221 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_doc_sequence_name,30);
222 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,85,
223 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_doc_sequence_value);
224 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,86,
225 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.alloc_row_id, 50);
226 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,87,
227 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.period_name, 15);
228 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,88,
229 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_id);
230 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,89,
231 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_range_id);
232 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,90,
233 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.offset_account, 25);
234 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,91,
235 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_low, 25);
236 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,92,
237 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_high, 25);
238 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,93,
239 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.partial_allocation, 1);
240 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,94,
241 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_description, 240);
242 DBMS_SQL.DEFINE_COLUMN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c,95,
243 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.rule_set_name, 100);
244 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.prepare_journal_select');
245 END prepare_journal_select;
246
247 /* ---------------------------------------------------------------------
248 | PRIVATE FUNCTOIN |
249 | get_cc_acc_range_ids |
250 | DESCRIPTION |
251 | Get Ids of the Cost Center Range and the Account Range for the |
252 | current fiscal journal line. |
253 | CALLED BY |
254 | JG_ALLOCATE_JOURNALS_PKG.allocate |
255 | RETURNS |
256 | TRUE if journal line STILL does not fall under either a cost |
257 | center range or an account range. |
258 | FALSE if we find a combination of cost center range and |
259 | account range that the line falls under. In this latter case, |
260 | we have found a line that falls under more than one cost |
261 | center range as they are allowed to overlap. The main dynamic |
262 | query will have found this/these other valid combinations and |
263 | the current invalid combination should be discarded without |
264 | displaying an error message. Remember we ONLY call this |
265 | function if we have an invalid journal line returned without |
266 | a cost center range id or an account range id. |
267 --------------------------------------------------------------------- */
268 FUNCTION get_cc_acc_range_ids RETURN BOOLEAN IS
269 CURSOR c_range_ids IS
270 SELECT ccr.cc_range_id,
271 acr.account_range_id,
272 acr.offset_account,
273 ccr.cc_range_low,
274 ccr.cc_range_high
275 FROM jg_zz_ta_account_ranges acr,
276 jg_zz_ta_cc_ranges ccr
277 WHERE NVL(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cost_center, ccr.cc_range_low)
278 BETWEEN ccr.cc_range_low AND ccr.cc_range_high
279 AND ccr.rule_set_id = JG_JOURNAL_ALLOCATIONS_PKG.G_rule_set_id
280 AND ccr.cc_range_id = acr.cc_range_id (+)
281 AND JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_number
282 BETWEEN acr.account_range_low (+) AND acr.account_range_high (+)
283 ORDER BY acr.offset_account;
284 BEGIN
285 JG_UTILITY_PKG.log( '> JG_ALLOCATE_JOURNALS_PKG.get_cc_acc_range_ids');
286 OPEN c_range_ids;
287 FETCH c_range_ids INTO JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_id,
288 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_range_id,
289 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.offset_account,
290 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_low,
291 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_high;
292 IF (JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.offset_account IS NOT NULL) THEN
293 CLOSE c_range_ids;
294 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.get_cc_acc_range_ids');
295 RETURN FALSE;
296 ELSE
297 CLOSE c_range_ids;
298 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.get_cc_acc_range_ids');
299 RETURN TRUE;
300 END IF;
301 END get_cc_acc_range_ids;
302
303
304 /* ---------------------------------------------------------------------
305 | PRIVATE PROCEDURE |
306 | Write_Report_Titles |
307 | DESCRIPTION |
308 | Write Report Title Lines to Output |
309 | CALLED BY |
310 | Allocate |
311 --------------------------------------------------------------------- */
312 PROCEDURE write_report_titles IS
313 l_output1 VARCHAR2(2000) := NULL;
314 l_output2 VARCHAR2(2000) := NULL;
315 l_output3 VARCHAR2(2000) := NULL;
316 l_output310 VARCHAR2(2000) := NULL;
317 l_output4 VARCHAR2(2000) := NULL;
318 l_output5 VARCHAR2(2000) := NULL;
319 l_output6 VARCHAR2(2000) := NULL;
320 BEGIN
321 JG_UTILITY_PKG.log( '> JG_ALLOCATE_JOURNALS_PKG.write_report_titles');
322 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
323 FND_MESSAGE.set_name(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_REPORT_TITLE1');
324 FND_MESSAGE.set_token('SOB_NAME', RPAD(JG_JOURNAL_ALLOCATIONS_PKG.G_set_of_books_name, 67));
325 -- Bug 876171: The following line is too long for a row of length 180 characters
326 -- FND_MESSAGE.set_token('DATE_TIME', LPAD(fnd_date.date_to_charDT(SYSDATE), 90));
327 FND_MESSAGE.set_token('DATE_TIME', LPAD(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'), 76));
328 l_output1 := FND_MESSAGE.get;
329 FND_MESSAGE.set_name(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_REPORT_TITLE2');
330 FND_MESSAGE.set_token('CONC_REQUEST_ID', TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_request_id));
331 l_output2 := FND_MESSAGE.get;
332 l_output3 := JG_JOURNAL_ALLOCATIONS_PKG.G_period_name;
333 l_output310 := JG_JOURNAL_ALLOCATIONS_PKG.G_currency_code;
334 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_ERR_HEADING2');
335 l_output4 := FND_MESSAGE.GET;
336 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_ERR_HEADING3');
337 l_output5 := FND_MESSAGE.GET;
338 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_ERR_HEADING1');
339 l_output6 := FND_MESSAGE.GET;
340 ELSE
341 FND_MESSAGE.set_name(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_U_REPORT_TITLE1');
342 FND_MESSAGE.set_token('DATE_TIME', fnd_date.date_to_charDT(SYSDATE));
343 l_output1 := FND_MESSAGE.get;
344 FND_MESSAGE.set_name(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_REPORT_TITLE2');
345 FND_MESSAGE.set_token('CONC_REQUEST_ID', TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_request_id));
346 l_output2 := FND_MESSAGE.get;
347 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_UNALLOC_HDING2');
348 l_output4 := FND_MESSAGE.GET;
349 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_UNALLOC_HDING3');
350 l_output5 := FND_MESSAGE.GET;
351 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_UNALLOC_HDING1');
352 l_output6 := FND_MESSAGE.GET;
353 END IF;
354 JG_UTILITY_PKG.out(l_output1);
355 JG_UTILITY_PKG.out(l_output2);
356 JG_UTILITY_PKG.out(l_output3);
357 JG_UTILITY_PKG.out(l_output310);
358 --FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
359 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
360 JG_UTILITY_PKG.out(l_output4);
361 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
362 JG_UTILITY_PKG.out(l_output5);
363 JG_UTILITY_PKG.out(l_output6);
364 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.write_report_titles');
365 END write_report_titles;
366
367 /* ---------------------------------------------------------------------
368 | PRIVATE PROCEDURE |
369 | Write_Report_Headings |
370 | DESCRIPTION |
371 | Write Report Headings to the Output File |
372 | CALLED BY |
373 | Create_Journal |
374 --------------------------------------------------------------------- */
375 PROCEDURE write_report_headings IS
376 l_line_output1 VARCHAR2(2000) := NULL;
377 l_line_output2 VARCHAR2(2000) := NULL;
378 l_line_output3 VARCHAR2(2000) := NULL;
379 BEGIN
380 JG_UTILITY_PKG.log('> JG_ALLOCATE_JOURNALS_PKG.write_report_headings');
381 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_HEADING1');
382 l_line_output1 := FND_MESSAGE.GET;
383 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_HEADING2');
384 l_line_output2 := FND_MESSAGE.GET;
385 FND_MESSAGE.SET_NAME (JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name ,'JG_ZZ_JGZZTAJA_HEADING3');
386 l_line_output3 := FND_MESSAGE.GET;
387 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 2);
388 JG_UTILITY_PKG.out(l_line_output1);
389 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
390 JG_UTILITY_PKG.out(l_line_output2);
391 JG_UTILITY_PKG.out(l_line_output3);
392 JG_UTILITY_PKG.log('< JG_ALLOCATE_JOURNALS_PKG.write_report_headings');
393 END Write_Report_Headings;
394
395 /* ---------------------------------------------------------------------
396 | PRIVATE PROCEDURE |
397 | write_allocated_lines_output |
398 | DESCRIPTION |
399 | Write Allocated line Details to the Output File |
400 | CALLED BY |
401 | Create_Journal_Allocations |
402 --------------------------------------------------------------------- */
403 PROCEDURE write_allocated_lines_output (lp_total_fiscal_accted_cr_amt IN NUMBER,
404 lp_total_fiscal_accted_dr_amt IN NUMBER) IS
405 l_acct_seg_string VARCHAR2(2000);
406 l_destn_acct_seg_string VARCHAR2(2000);
407 l_delimiter VARCHAR2(100);
408 l_acct_posn_start NUMBER;
409 l_acct_posn_end NUMBER;
410 l_previous_rec_id VARCHAR2(1000) := '#####################';
411 l_total_message VARCHAR2(100);
412 l_total_separator VARCHAR2(300) := NULL;
413 l_cc_range_separator VARCHAR2(200) := NULL;
414 l_range_offset_remark VARCHAR2(50) := NULL;
415 -- Bug 876171: Add the variable
416 l_no_data_message VARCHAR2(50);
417 BEGIN
418
419 JG_UTILITY_PKG.log('> JG_ALLOCATE_JOURNALS_PKG.write_allocated_lines_output');
420
421 --
422 -- For each allocated row
423 --
424 FOR i IN 1..JG_CREATE_JOURNALS_PKG.i LOOP
425
426 --
427 -- Retrieve the full accounting flexfield string based on original journal line
428 --
429 l_acct_seg_string := FND_FLEX_EXT.get_segs( JG_JOURNAL_ALLOCATIONS_PKG.G_GL_appln_short_name,
430 JG_JOURNAL_ALLOCATIONS_PKG.G_GL_acct_flex_code,
431 JG_JOURNAL_ALLOCATIONS_PKG.G_chart_of_accounts_id,
432 JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).code_combination_id);
433 --
434 -- Find out NOCOPY what the segment delimiter is
435 --
436 l_delimiter := FND_FLEX_EXT.get_delimiter(JG_JOURNAL_ALLOCATIONS_PKG.G_GL_appln_short_name,
437 JG_JOURNAL_ALLOCATIONS_PKG.G_GL_acct_flex_code,
438 JG_JOURNAL_ALLOCATIONS_PKG.G_chart_of_accounts_id);
439 l_destn_acct_seg_string := l_acct_seg_string;
440
441 --
442 -- Destination flexfield formulated differently if segment method
443 -- is zero-filled or for offset line grouped by account range
444 --
445 IF JG_JOURNAL_ALLOCATIONS_PKG.G_destn_segment_method = 'ZF' OR
446 JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_batch_name = 'OFFSET FOR ACCOUNT RANGE' THEN
447 --
448 -- For each segment
449 --
450 FOR j IN 1..JG_JOURNAL_ALLOCATIONS_PKG.G_num_of_segments LOOP
451 --
452 -- Don't zero-fill the natural account or balancing segment
453 --
454 IF j NOT IN (JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num, JG_JOURNAL_ALLOCATIONS_PKG.G_bal_segment_num) THEN
455 IF j = 1 THEN
456 l_acct_posn_start := 0;
457 ELSE
458 l_acct_posn_start := INSTR(l_destn_acct_seg_string ,l_delimiter ,1 ,j-1);
459 END IF;
460 IF j = JG_JOURNAL_ALLOCATIONS_PKG.G_num_of_segments THEN
461 l_acct_posn_end := LENGTH(l_destn_acct_seg_string) + 1;
462 ELSE
463 l_acct_posn_end := INSTR(l_destn_acct_seg_string ,l_delimiter ,1 ,j);
464 END IF;
465 l_destn_acct_seg_string := SUBSTR(l_destn_acct_seg_string, 1, l_acct_posn_start)||
466 JG_JOURNAL_ALLOCATIONS_PKG.G_zero_fill_arr(j).zero_string||
467 SUBSTR(l_destn_acct_seg_string, l_acct_posn_end);
468 END IF;
469 END LOOP; -- each segment
470 END IF; -- Zero Fill Method
471 --
472 -- For both the Journal Account and Zero-Filled method, we are
473 -- substituting the natural account for the destination account number
474 --
475 IF JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num = 1 THEN
476 l_acct_posn_start := 0;
477 ELSE
478 l_acct_posn_start := INSTR(l_destn_acct_seg_string ,l_delimiter ,1 ,JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num - 1);
479 END IF;
480 IF JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num = JG_JOURNAL_ALLOCATIONS_PKG.G_num_of_segments THEN
481 l_acct_posn_end := LENGTH(l_destn_acct_seg_string) + 1;
482 ELSE
483 l_acct_posn_end := INSTR(l_destn_acct_seg_string ,l_delimiter ,1 ,JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num);
484 END IF;
485 l_destn_acct_seg_string := SUBSTR(l_destn_acct_seg_string, 1, l_acct_posn_start)||
486 JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_account_number||
487 SUBSTR(l_destn_acct_seg_string, l_acct_posn_end);
488
489 -- If the next line is for a new cc range and i<>1, then insert the line separator
490 IF i <> 1 THEN
491 IF JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).cc_range_id <> JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i-1).cc_range_id THEN
492 FND_MESSAGE.set_name(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_CCRNGE_SEPRATOR');
493 l_cc_range_separator := FND_MESSAGE.get;
494 JG_UTILITY_PKG.out(RPAD(' ', 115)||l_cc_range_separator);
495 END IF;
496 END IF;
497
498 IF JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_batch_name = 'OFFSET FOR ACCOUNT RANGE' THEN
499 JG_UTILITY_PKG.out(RPAD(' ', 115)||RPAD(SUBSTR(NVL(l_destn_acct_seg_string, ' '), 1, 34), 34)||' '||
500 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_accted_dr,
501 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||
502 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_accted_cr,
503 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).remarks);
504 ELSIF l_previous_rec_id <> JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_batch_name||
505 JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_header_name||
506 TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_line_num) THEN
507 JG_UTILITY_PKG.out( RPAD(SUBSTR(NVL(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_batch_name, ' '), 1, 20), 20)||' '||
508 RPAD(SUBSTR(NVL(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_header_name, ' '), 1, 19), 19)||' '||
509 RPAD(SUBSTR(NVL(l_acct_seg_string, ' '), 1, 35), 35)||' '||
510 LPAD(SUBSTR(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_line_num), 1, 3), 3)||' '||
511 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).accounted_dr,
512 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||
513 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).accounted_cr,
514 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||
515 RPAD(SUBSTR(NVL(l_destn_acct_seg_string, ' '), 1, 34), 34)||' '||
516 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_accted_dr,
517 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||
518 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_accted_cr,
519 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).remarks);
520 ELSE
521 JG_UTILITY_PKG.out(RPAD(' ', 115)|| RPAD(SUBSTR(NVL(l_destn_acct_seg_string, ' '), 1, 34), 34)||' '||
522 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_accted_dr, JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||
523 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).destn_accted_cr, JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).remarks);
524 END IF;
525
526 l_previous_rec_id := JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_batch_name|| JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_header_name|| TO_CHAR(JG_CREATE_JOURNALS_PKG.alloc_lines_arr(i).je_line_num);
527 END LOOP; -- Each row
528
529 -- Output appopriate message if no rows allocated
530 IF JG_CREATE_JOURNALS_PKG.i = 0 THEN
531 -- Bug 876171: Add a new line before the no-data-found token
532 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
533 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_NO_DATA_FOUND');
534 -- Bug 876171: Replace the following line with new lines
535 -- JG_UTILITY_PKG.out(FND_MESSAGE.GET);
536 l_no_data_message := FND_MESSAGE.GET;
537 JG_UTILITY_PKG.out(RPAD(' ', 75)||RPAD(l_no_data_message,30)||RPAD(' ', 75));
538 ELSE
539 --
540 -- Print totals
541 --
542 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_TOTAL_SEPARATOR');
543 l_total_separator := FND_MESSAGE.GET;
544 JG_UTILITY_PKG.out(l_total_separator);
545 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_JGZZTAJA_TOTAL');
546 l_total_message := FND_MESSAGE.GET;
547 JG_UTILITY_PKG.out(RPAD(' ', 41)||RPAD(l_total_message, 40)||
548 LPAD(SUBSTR(NVL(TO_CHAR(lp_total_fiscal_accted_dr_amt,
549 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||' '||
550 LPAD(SUBSTR(NVL(TO_CHAR(lp_total_fiscal_accted_cr_amt,
551 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15)||
552 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.G_total_alloc_accted_dr_amt,
553 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 53)||' '||
554 LPAD(SUBSTR(NVL(TO_CHAR(JG_CREATE_JOURNALS_PKG.G_total_alloc_accted_cr_amt,
555 JG_JOURNAL_ALLOCATIONS_PKG.G_func_currency_format_mask), ' '), 1, 15), 15));
556 JG_UTILITY_PKG.log('< JG_ALLOCATE_JOURNALS_PKG.write_allocated_lines_output');
557 END IF;
558 END write_allocated_lines_output;
559
560
561 /* ---------------------------------------------------------------------
562 | PRIVATE FUNCTION |
563 | Get_Dynamic_Select_String |
564 | DESCRIPTION |
565 | Substitutes in variable strings into overall SELECT string |
566 | CALLED BY |
567 | Create_Journal_Allocations |
568 | RETURNS |
569 | SELECT string |
570 --------------------------------------------------------------------- */
571 FUNCTION get_dynamic_select_string RETURN VARCHAR2 IS
572 l_sob_where VARCHAR2(200) := NULL;
573 l_period_name_where VARCHAR2(200) := NULL;
574 l_currency_code_where VARCHAR2(200) := NULL;
575 l_bal_seg_where VARCHAR2(200) := NULL;
576 l_bal_type_where VARCHAR2(200) := NULL;
577 l_budenc_where VARCHAR2(200) := NULL;
578 l_allocate_where VARCHAR2(200) := NULL;
579 l_hint_clause VARCHAR2(200) := NULL;
580 l_cc_range_where VARCHAR2(200) := NULL;
581 l_acct_range_where VARCHAR2(200) := NULL;
582 l_inline_view_clause VARCHAR2(1000) := NULL;
583 l_non_view_columns VARCHAR2(500) := NULL;
584 l_rule_sets_clause VARCHAR2(200) := NULL;
585 l_rule_set_where VARCHAR2(200) := NULL;
586 l_account_type_where VARCHAR2(200) := NULL;
587 l_order_clause VARCHAR2(200) := NULL;
588 BEGIN
589 JG_UTILITY_PKG.log( '> JG_ALLOCATE_JOURNALS_PKG.get_dynamic_select_string');
590 --
591 -- Allocation
592 --
593 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
594 --
595 -- Set of Books ID
596 -- GC Ledger Architecture change:
597 l_sob_where := 'AND jlv.ledger_id = '||TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_set_of_books_id)||' ';
598 JG_UTILITY_PKG.debug('l_sob_where = ' || l_sob_where);
599 --
600 -- Period Name
601 --
602 IF JG_JOURNAL_ALLOCATIONS_PKG.G_period_name IS NOT NULL THEN
603 l_period_name_where := 'AND jlv.period_name = '''||JG_JOURNAL_ALLOCATIONS_PKG.G_period_name||''' ';
604 JG_UTILITY_PKG.debug('l_period_name_where = ' || l_period_name_where);
605 END IF;
606 --
607 -- Currency Code
608 --
609 IF JG_JOURNAL_ALLOCATIONS_PKG.G_currency_code IS NOT NULL THEN
610 l_currency_code_where := 'AND jlv.currency_code = '''||JG_JOURNAL_ALLOCATIONS_PKG.G_currency_code||''' ';
611 JG_UTILITY_PKG.debug('l_currency_code_where = ' ||l_currency_code_where);
612 END IF;
613 --
614 -- Balancing Segment Where
615 --
616 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_balance_segment_value IS NOT NULL) THEN
617 l_bal_seg_where := 'AND ' || JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_bal_segment_num).segment_col_name ||
618 ' = '''||JG_JOURNAL_ALLOCATIONS_PKG.G_balance_segment_value||'''';
619 JG_UTILITY_PKG.debug('l_bal_seg_where = ' ||l_bal_seg_where);
620 END IF;
621 --
622 -- Balance Type and budget version id OR encumbrance type id
623 --
624 l_bal_type_where := ' AND jlv.actual_flag = '''||JG_JOURNAL_ALLOCATIONS_PKG.G_balance_type||''' ';
625 JG_UTILITY_PKG.debug('l_bal_type_where = ' || l_bal_type_where);
626
627 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_balance_type = 'B') THEN
628 l_budenc_where := ' AND jlv.budget_version_id = '|| TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_balance_type_id)||' ';
629 JG_UTILITY_PKG.debug('l_budenc_where = ' || l_budenc_where);
630 ELSIF (JG_JOURNAL_ALLOCATIONS_PKG.G_balance_type = 'E') THEN
631 l_budenc_where := ' AND jlv.encumbrance_type_id = '||
632 TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_balance_type_id)||' ';
633 JG_UTILITY_PKG.debug('l_budenc_where = ' || l_budenc_where);
634 END IF;
635 l_allocate_where := ' AND jlv.status||'''' = ''P'' AND jlv.request_id IS NULL ';
636 JG_UTILITY_PKG.debug('l_allocate_where = '|| l_allocate_where);
637
638 l_inline_view_clause := '(SELECT ccr.cc_range_id '||
639 ', ccr.cc_range_low '||
640 ', ccr.cc_range_high '||
641 ', ccr.description '||
642 ', acr.account_range_id '||
643 ', acr.account_range_low '||
644 ', acr.account_range_high '||
645 ', acr.offset_account '||
646 'FROM jg_zz_ta_account_ranges acr '||
647 ', jg_zz_ta_cc_ranges ccr '||
648 'WHERE ccr.cc_range_id = acr.cc_range_id '||
649 'AND ccr.rule_set_id = '||TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_rule_set_id)||') ranges, ';
650
651 l_non_view_columns := ', ranges.cc_range_id '||
652 ', ranges.account_range_id '||
653 ', ranges.offset_account '||
654 ', ranges.cc_range_low '||
655 ', ranges.cc_range_high '||
656 ', rs.partial_allocation '||
657 ', ranges.description cc_range_description '||
658 ', rs.name rule_set_name ';
659
660 l_rule_sets_clause := ' jg_zz_ta_rule_sets rs, ';
661
662 l_rule_set_where := ' AND rs.rule_set_id = '||TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_rule_set_id)||' ' ;
663
664 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_cc_segment_num IS NOT NULL) THEN
665 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_cc_segment_num).segment_vset_fmt_type <> 'N') THEN
666 l_cc_range_where := 'jlv.'||JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_cc_segment_num).segment_col_name||
667 ' BETWEEN ranges.cc_range_low (+) AND ranges.cc_range_high (+) ';
668 ELSE
669 l_cc_range_where := 'jlv.'||JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_cc_segment_num).segment_col_name||
670 ' BETWEEN TO_NUMBER(ranges.cc_range_low) (+) AND TO_NUMBER(ranges.cc_range_high) (+) ';
671 END IF;
672 ELSE
673 l_cc_range_where := '1 = 1 ';
674 END IF;
675 JG_UTILITY_PKG.debug('l_cc_range_where = '|| l_cc_range_where);
676
677 IF JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num).segment_vset_fmt_type <> 'N' THEN
678 l_acct_range_where := ' AND jlv.'||JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num).segment_col_name||
679 ' BETWEEN ranges.account_range_low (+) AND ranges.account_range_high (+) ';
680 ELSE
681 l_acct_range_where := ' AND jlv.'||JG_JOURNAL_ALLOCATIONS_PKG.G_acct_flex_segmt_arr(JG_JOURNAL_ALLOCATIONS_PKG.G_acct_segment_num).segment_col_name||
682 ' BETWEEN TO_NUMBER(ranges.account_range_low) (+) AND TO_NUMBER(ranges.account_range_high) (+) ';
683 END IF;
684
685 l_account_type_where := ' AND NVL(rs.account_type, jlv.account_type) = jlv.account_type ';
686 l_order_clause := ' ORDER BY jlv.currency_code, ranges.cc_range_id, ranges.account_range_id';
687 --
688 -- Unallocate
689 --
690 ELSE
691 l_cc_range_where := '1 = 1 ';
692 l_non_view_columns := ', NULL '||
693 ', NULL '||
694 ', NULL '||
695 ', NULL '||
696 ', NULL '||
697 ', NULL '||
698 ', NULL '||
699 ', NULL ';
700 l_hint_clause := '/*+ INDEX(jlv JG_ZZ_TA_ALLOCATED_LINES_N1) */';
701 l_allocate_where := ' AND jlv.request_id = '|| TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id) || ' ';
702 JG_UTILITY_PKG.debug('l_allocate_where = '|| l_allocate_where);
703 END IF;
704 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.get_dynamic_select_string');
705 RETURN 'SELECT '||l_hint_clause||' jlv.je_batch_id '||
706 ', jlv.je_batch_name '||
707 ', jlv.je_header_id '||
708 ', jlv.je_header_name '||
709 ', jlv.currency_code '||
710 ', jlv.currency_conversion_type '||
711 ', jlv.currency_conversion_date '||
712 ', jlv.currency_conversion_rate '||
713 ', jlv.encumbrance_type_id '||
714 ', jlv.budget_version_id '||
715 JG_JOURNAL_ALLOCATIONS_PKG.G_cc_seg_num_string||
716 JG_JOURNAL_ALLOCATIONS_PKG.G_acc_seg_num_string||
717 ', jlv.segment1 '||
718 ', jlv.segment2 '||
719 ', jlv.segment3 '||
720 ', jlv.segment4 '||
721 ', jlv.segment5 '||
722 ', jlv.segment6 '||
723 ', jlv.segment7 '||
724 ', jlv.segment8 '||
725 ', jlv.segment9 '||
726 ', jlv.segment10 '||
727 ', jlv.segment11 '||
728 ', jlv.segment12 '||
729 ', jlv.segment13 '||
730 ', jlv.segment14 '||
731 ', jlv.segment15 '||
732 ', jlv.segment16 '||
733 ', jlv.segment17 '||
734 ', jlv.segment18 '||
735 ', jlv.segment19 '||
736 ', jlv.segment20 '||
737 ', jlv.segment21 '||
738 ', jlv.segment22 '||
739 ', jlv.segment23 '||
740 ', jlv.segment24 '||
741 ', jlv.segment25 '||
742 ', jlv.segment26 '||
743 ', jlv.segment27 '||
744 ', jlv.segment28 '||
745 ', jlv.segment29 '||
746 ', jlv.segment30 '||
747 ', jlv.je_line_num '||
748 ', jlv.accounted_cr '||
749 ', jlv.accounted_dr '||
750 ', jlv.entered_cr '||
751 ', jlv.entered_dr '||
752 ', jlv.stat_amount '||
753 ', jlv.subledger_doc_sequence_id '||
754 ', jlv.subledger_doc_sequence_value '||
755 ', jlv.attribute1 '||
756 ', jlv.attribute2 '||
757 ', jlv.attribute3 '||
758 ', jlv.attribute4 '||
759 ', jlv.attribute5 '||
760 ', jlv.attribute6 '||
761 ', jlv.attribute7 '||
762 ', jlv.attribute8 '||
763 ', jlv.attribute9 '||
764 ', jlv.attribute10 '||
765 ', jlv.attribute11 '||
766 ', jlv.attribute12 '||
767 ', jlv.attribute13 '||
768 ', jlv.attribute14 '||
769 ', jlv.attribute15 '||
770 ', jlv.attribute16 '||
771 ', jlv.attribute17 '||
772 ', jlv.attribute18 '||
773 ', jlv.attribute19 '||
774 ', jlv.attribute20 '||
775 ', jlv.context '||
776 ', jlv.context2 '||
777 ', jlv.context3 '||
778 ', jlv.invoice_date '||
779 ', jlv.tax_code '||
780 ', jlv.invoice_identifier '||
781 ', jlv.invoice_amount '||
782 ', jlv.ussgl_transaction_code '||
783 ', jlv.jgzz_recon_ref '||
784 ', jlv.code_combination_id '||
785 ', jlv.row_id '||
786 ', jlv.effective_date '||
787 ', jlv.external_reference '||
788 ', jlv.je_doc_sequence_name '||
789 ', jlv.je_doc_sequence_value '||
790 ', jlv.alloc_row_id '||
791 ', jlv.period_name '||
792 l_non_view_columns||
793 'FROM '||l_inline_view_clause||
794 l_rule_sets_clause||
795 ' jg_zz_ta_je_lines_v jlv '||
796 'WHERE '||l_cc_range_where ||
797 l_acct_range_where ||
798 l_rule_set_where ||
799 l_account_type_where ||
800 l_sob_where ||
801 l_period_name_where ||
802 l_currency_code_where ||
803 l_bal_seg_where ||
804 l_bal_type_where ||
805 l_budenc_where ||
806 l_allocate_where ||
807 l_order_clause;
808 END get_dynamic_select_string;
809
810 /* ---------------------------------------------------------------------
811 | PRIVATE FUNCTION |
812 | Record_Locked |
813 | DESCRIPTION |
814 | Determines whether current row returned by cursor is locked by |
815 | another process. |
816 | CALLED BY |
817 | JG_ALLOCATE_JOURNALS_PKG.allocate |
818 | RETURNS |
819 | TRUE if record locked, FALSE otherwise |
820 --------------------------------------------------------------------- */
821 FUNCTION record_locked RETURN BOOLEAN IS
822 l_header_id gl_je_lines.je_header_id%TYPE;
823 BEGIN
824 JG_UTILITY_PKG.log( '> JG_ALLOCATE_JOURNALS_PKG.record_locked');
825 JG_UTILITY_PKG.debug('rowid = '||CHARTOROWID(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.row_id));
826
827 SELECT je_header_id
828 INTO l_header_id
829 FROM gl_je_lines
830 WHERE rowid = CHARTOROWID(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.row_id)
831 FOR UPDATE OF je_header_id NOWAIT;
832
833 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.record_locked');
834 RETURN FALSE;
835 EXCEPTION
836 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
837 JG_UTILITY_PKG.debug( 'Record is Locked');
838 RETURN TRUE;
839 WHEN OTHERS THEN
840 JG_UTILITY_PKG.debug( 'OTHERS Failure '||TO_CHAR(SQLCODE)||': '||SQLERRM);
841 RAISE;
842 END record_locked;
843
844 /* ---------------------------------------------------------------------
845 | PRIVATE FUNCTION |
846 | Valid_Journal |
847 | DESCRIPTION |
848 | Validates Current Journal to check for row locking, line falls |
849 | within a cost center and account range, and valid allocation |
850 | percentage rule lines have been defined. |
851 | CALLED BY |
852 | JG_ALLOCATE_JOURNALS_PKG.allocate |
853 | RETURNS |
854 | TRUE if line valid, FALSE otherwise and error message code |
855 | returned in parameter |
856 --------------------------------------------------------------------- */
857 FUNCTION Valid_Journal(p_err_msg_code IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
858
859 BEGIN
860
861 IF (JG_ALLOCATE_JOURNALS_PKG.Record_Locked) THEN
862 JG_UTILITY_PKG.debug('Record Locked Processing');
863 p_err_msg_code := 'JG_ZZ_RECORD_LOCKED';
864 RETURN FALSE;
865 --
866 -- Allocation mode
867 --
868 ELSIF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
869 --
870 -- Check that cost_center exists within a defined cost center range
871 --
872 IF JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_id IS NULL THEN
873 --
874 -- Repopulate range ids in G_journal_qry_rec, because the cc_range_id
875 -- may have been null because the account_range_id was null (see dynamic select)
876 -- and we need to return a specific message as to why the journal line failed
877 --
878 IF (NOT JG_ALLOCATE_JOURNALS_PKG.get_cc_acc_range_ids) THEN
879 -- return false, but no error message to be displayed, just continue processing
880 p_err_msg_code := NULL;
881 RETURN FALSE;
882 END IF;
883 --
884 -- Recheck
885 --
886 IF JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_id IS NULL THEN
887 p_err_msg_code := 'JG_ZZ_MISSING_COST_CENTER_RULE';
888 RETURN FALSE;
889 END IF;
890 END IF; -- cc range id check
891 --
892 -- Check that account_number exists within a defined account number range
893 --
894 IF JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_range_id IS NULL THEN
895 p_err_msg_code := 'JG_ZZ_MISSING_ACC_NUMBER_RULE';
896 RETURN FALSE;
897 END IF; -- account range id check
898
899 END IF;
900
901 RETURN TRUE;
902
903 END Valid_Journal;
904
905 /* ------------------------------------------------------------------------------
906 | PUBLIC PROCEDURE |
907 | allocate |
908 | DESCRIPTION |
909 | Allocates the Journals |
910 | Pseudocode: |
911 | Write Report Titles to Output file |
912 | FOR each source journal line LOOP |
913 | Validate line: record not locked and line falls under a valid |
914 | combination of cost center range and account number range. |
915 | IF in allocation mode THEN |
916 | call JG_CREATE_JOURNAL_PKG.create_journal |
917 | Insert the journal line into the allocated lines table to show |
918 | that the line has been successfully allocated |
919 | ELSIF in unallocation mode THEN |
920 | Delete journal line from allocated lines table to show that the |
921 | line has been unallocated |
922 | END IF |
923 | END LOOP |
924 | IF in allocation mode THEN |
925 | IF last journal line processed had an offset account defined at the |
926 | account range level THEN |
927 | Add offset allocation line to array |
928 | Insert allocation line in GL_INTERFACE |
929 | END IF |
930 | Write Details of Allocated Lines to Output File from array |
931 | END IF |
932 | |
933 | CALLED BY |
934 | JG_JOURNAL_ALLOCATION_PKG.main |
935 --------------------------------------------------------------------------------*/
936 PROCEDURE allocate IS
937 l_dummy_int INTEGER;
938 l_total_fiscal_accted_cr_amt NUMBER := 0;
939 l_total_fiscal_accted_dr_amt NUMBER := 0;
940 x_rowid ROWID;
941 l_ext_precision NUMBER;
942 l_min_acct_unit NUMBER;
943 l_err_msg_code VARCHAR2(100) := NULL;
944 l_first_valid_row BOOLEAN := TRUE;
945 l_is_valid_row BOOLEAN;
946 -- Bug 876171: Add more variables for token printout
947 l_fail_ndf_flag NUMBER := 0;
948 l_unal_ndf_flag NUMBER := 0;
949 l_no_data_found_message VARCHAR2(50);
950 l_eof_message VARCHAR2(50);
951 BEGIN
952 JG_UTILITY_PKG.log( '> JG_ALLOCATE_JOURNALS_PKG.allocate');
953 --
954 -- Prepare the execution report Title and Error Headings;
955 -- for unallocation, the same column headings are used with different titles.
956 --
957 JG_ALLOCATE_JOURNALS_PKG.write_report_titles;
958 --
959 -- Prepare the main SQL statement, the dynamic parts
960 --
961 JG_ALLOCATE_JOURNALS_PKG.prepare_journal_select;
962
963 JG_UTILITY_PKG.debug( 'Execute Journal Select');
964
965 l_dummy_int := DBMS_SQL.EXECUTE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c);
966
967 JG_UTILITY_PKG.debug(TO_CHAR(SYSDATE, 'HH24:MI:SS'));
968 -- Loop for each row
969 WHILE DBMS_SQL.FETCH_ROWS(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c) > 0 LOOP
970 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 1 ,
971 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_batch_id);
972 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 2 ,
973 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_batch_name);
974 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 3 ,
975 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_header_id);
976 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 4 ,
977 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_header_name);
978 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 5 ,
979 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_code);
980 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 6 ,
981 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_conversion_type);
982 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 7,
983 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_conversion_date);
984 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 8,
985 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_conversion_rate);
986 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 9,
987 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.encumbrance_type_id);
988 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 10,
989 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.budget_version_id);
990 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 11,
991 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cost_center);
992 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 12,
993 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_number);
994 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 13,
995 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment1);
996 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 14,
997 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment2);
998 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 15,
999 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment3);
1000 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 16,
1001 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment4);
1002 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 17,
1003 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment5);
1004 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 18,
1005 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment6);
1006 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 19,
1007 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment7);
1008 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 20,
1009 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment8);
1010 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 21,
1011 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment9);
1012 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 22,
1013 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment10);
1014 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 23,
1015 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment11);
1016 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 24,
1017 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment12);
1018 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 25,
1019 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment13);
1020 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 26,
1021 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment14);
1022 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 27,
1023 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment15);
1024 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 28,
1025 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment16);
1026 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 29,
1027 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment17);
1028 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 30,
1029 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment18);
1030 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 31,
1031 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment19);
1032 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 32,
1033 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment20);
1034 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 33,
1035 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment21);
1036 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 34,
1037 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment22);
1038 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 35,
1039 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment23);
1040 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 36,
1041 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment24);
1042 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 37,
1043 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment25);
1044 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 38,
1045 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment26);
1046 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 39,
1047 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment27);
1048 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 40,
1049 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment28);
1050 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 41,
1051 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment29);
1052 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 42,
1053 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.segment30);
1054 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 43,
1055 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_line_num);
1056 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 44,
1057 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.accounted_cr);
1058 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 45,
1059 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.accounted_dr);
1060 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 46,
1061 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.entered_cr);
1062 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 47,
1063 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.entered_dr);
1064 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 48,
1065 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.stat_amount);
1066 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 49,
1067 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.subledger_doc_sequence_id);
1068 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 50,
1069 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.subledger_doc_sequence_value);
1070 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 51,
1071 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute1);
1072 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 52,
1073 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute2);
1074 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 53,
1075 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute3);
1076 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 54,
1077 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute4);
1078 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 55,
1079 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute5);
1080 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 56,
1081 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute6);
1082 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 57,
1083 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute7);
1084 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 58,
1085 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute8);
1086 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 59,
1087 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute9);
1088 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 60,
1089 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute10);
1090 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 61,
1091 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute11);
1092 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 62,
1093 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute12);
1094 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 63,
1095 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute13);
1096 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 64,
1097 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute14);
1098 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 65,
1099 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute15);
1100 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 66,
1101 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute16);
1102 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 67,
1103 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute17);
1104 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 68,
1105 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute18);
1106 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 69,
1107 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute19);
1108 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 70,
1109 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.attribute20);
1110 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 71,
1111 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.context);
1112 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 72,
1113 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.context2);
1114 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 73,
1115 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.context3);
1116 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 74,
1117 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.invoice_date);
1118 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 75,
1119 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.tax_code);
1120 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 76,
1121 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.invoice_identifier);
1122 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 77,
1123 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.invoice_amount);
1124 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 78,
1125 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.ussgl_transaction_code);
1126 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 79,
1127 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.jgzz_recon_ref);
1128 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 80,
1129 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.code_combination_id);
1130 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 81,
1131 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.row_id);
1132 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 82,
1133 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.effective_date);
1134 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 83,
1135 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.external_reference);
1136 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 84,
1137 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_doc_sequence_name);
1138 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 85,
1139 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_doc_sequence_value);
1140 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 86,
1141 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.alloc_row_id);
1142 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 87,
1143 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.period_name);
1144 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 88,
1145 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_id);
1146 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 89,
1147 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_range_id);
1148 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 90,
1149 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.offset_account);
1150 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 91,
1151 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_low);
1152 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 92,
1153 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_high);
1154 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 93,
1155 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.partial_allocation);
1156 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 94,
1157 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cc_range_description);
1158 DBMS_SQL.COLUMN_VALUE(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c, 95,
1159 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.rule_set_name);
1160 JG_UTILITY_PKG.debug('Dynamic cursor column assignments: finished');
1161 JG_UTILITY_PKG.debug('cost_center = '||JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.cost_center);
1162 JG_UTILITY_PKG.debug('cc_id = '||
1163 to_char(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.code_combination_id));
1164
1165 --
1166 -- Get Entered Currency Format Mask and the currency's precision for reporting and calculating any rounding errors
1167 --
1168 -- Bug 2638803, changed format mask length from 15 to 18
1169 JG_ALLOCATE_JOURNALS_PKG.G_currency_format_mask :=
1170 FND_CURRENCY.GET_FORMAT_MASK(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_code,18);
1171 JG_UTILITY_PKG.debug( 'curr format mask = '||JG_ALLOCATE_JOURNALS_PKG.G_currency_format_mask);
1172 FND_CURRENCY.GET_INFO(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_code,
1173 JG_ALLOCATE_JOURNALS_PKG.G_currency_precision,
1174 l_ext_precision,
1175 l_min_acct_unit);
1176 JG_UTILITY_PKG.debug( 'curr precision = '||JG_ALLOCATE_JOURNALS_PKG.G_currency_precision);
1177
1178
1179 -- ********************* VALIDATION ***************************************
1180 -- In the validation, the following things are checked:
1181 -- 1. Locking: Make sure that the Journal Entry Line is not locked
1182 -- 2. Cost Center Range: Make sure JE line falls into a cost center range
1183 -- 3. Account Range: Make sure JE line falls into an account number range
1184 l_is_valid_row := Valid_Journal(l_err_msg_code);
1185
1186 IF NOT l_is_valid_row AND l_err_msg_code IS NOT NULL THEN
1187
1188 JG_UTILITY_PKG.debug('Invalid Journal Line found');
1189 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_Error_Handling <> 'I') THEN
1190 JG_JOURNAL_ALLOCATIONS_PKG.G_retcode := '1';
1191 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name,l_err_msg_code);
1192 JG_CREATE_JOURNALS_PKG.write_error_to_output;
1193
1194 -- Bug 876171: Set up l_fail_ndf_flag for no-data-found token
1195 l_fail_ndf_flag :=1;
1196
1197 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_Error_Handling = 'E') THEN
1198 RAISE APP_EXCEPTION.application_exception;
1199 END IF;
1200 END IF;
1201
1202 -- N.B. there are some cases where we have an invalid row that we do not want to report as an
1203 -- error, instead it should just be skipped. Hence, we check it is a valid row below.
1204 ELSIF l_is_valid_row THEN
1205
1206 --
1207 -- Allocation Mode
1208 --
1209 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
1210
1211 --
1212 -- Initialize account_range_id and cc_range_id of the last journal record to the
1213 -- current record returned.
1214 -- Used in checking whether to create an offset account line first time round
1215 --
1216 IF l_first_valid_row THEN
1217 JG_UTILITY_PKG.debug('First Valid Row Initialization');
1218 JG_ALLOCATE_JOURNALS_PKG.G_last_journal_qry_rec.offset_account := NULL;
1219 JG_ALLOCATE_JOURNALS_PKG.G_last_journal_qry_rec.account_range_id :=
1220 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.account_range_id;
1221 JG_ALLOCATE_JOURNALS_PKG.G_last_journal_qry_rec.l_je_lines_v_rec.currency_code :=
1222 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.currency_code;
1223 JG_CREATE_JOURNALS_PKG.G_Batch_Name := TO_CHAR(JG_JOURNAL_ALLOCATIONS_PKG.G_Request_Id)||' '||
1224 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.rule_set_name||' '||
1225 JG_JOURNAL_ALLOCATIONS_PKG.G_period_name;
1226 l_first_valid_row := FALSE;
1227 END IF;
1228
1229 --
1230 -- After validating the ranges, we have all the necessary information to do the next step...
1231 -- Insert the allocations
1232 --
1233 JG_CREATE_JOURNALS_PKG.create_journal;
1234
1235 --
1236 -- Cumulative totals for report output (fiscal account)
1237 --
1238 l_total_fiscal_accted_cr_amt := l_total_fiscal_accted_cr_amt +
1239 NVL(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.accounted_cr, 0);
1240 l_total_fiscal_accted_dr_amt := l_total_fiscal_accted_dr_amt +
1241 NVL(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.accounted_dr, 0);
1242
1243 JG_UTILITY_PKG.debug('fiscal accted cr running total = '||to_char(l_total_fiscal_accted_cr_amt));
1244 JG_UTILITY_PKG.debug('fiscal accted dr running total = '||to_char(l_total_fiscal_accted_dr_amt));
1245
1246 --
1247 -- Insert line to show the journal has been allocated
1248 --
1249 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_validate_only = 'N') THEN
1250 JG_ZZ_TA_ALLOCATED_LINES_PKG.insert_row(
1251 x_rowid,
1252 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_batch_id,
1253 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_header_id,
1254 JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.je_line_num,
1255 SYSDATE,
1256 JG_JOURNAL_ALLOCATIONS_PKG.G_user_id,
1257 JG_JOURNAL_ALLOCATIONS_PKG.G_user_id,
1258 SYSDATE,
1259 JG_JOURNAL_ALLOCATIONS_PKG.G_login_id,
1260 JG_JOURNAL_ALLOCATIONS_PKG.G_request_id,
1261 JG_JOURNAL_ALLOCATIONS_PKG.G_progr_appl_id,
1262 JG_JOURNAL_ALLOCATIONS_PKG.G_conc_progr_id,
1263 SYSDATE);
1264 END IF;
1265 --
1266 -- Unallocation Mode
1267 --
1268 ELSE
1269 JG_UTILITY_PKG.log('> JG_ZZ_TA_ALLOCATED_LINES_PKG.delete_row');
1270 JG_ZZ_TA_ALLOCATED_LINES_PKG.delete_row(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec.l_je_lines_v_rec.alloc_row_id);
1271 JG_UTILITY_PKG.log('< JG_ZZ_TA_ALLOCATED_LINES_PKG.delete_row');
1272 --
1273 -- Write Unallocated Fiscal Journal Line to the Output File
1274 --
1275 JG_CREATE_JOURNALS_PKG.write_error_to_output;
1276
1277 -- Bug 876171: Set up l_unal_ndf_flag
1278 l_unal_ndf_flag := 1;
1279
1280 END IF; -- Allocation Mode check
1281 --
1282 -- Store this journal record as the last record fetched
1283 --
1284 JG_ALLOCATE_JOURNALS_PKG.G_last_journal_qry_rec := JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_rec;
1285 END IF; -- Valid Journal?
1286 END LOOP; -- Journals
1287
1288 JG_UTILITY_PKG.debug('End of Journal Lines Loop');
1289 JG_UTILITY_PKG.debug(TO_CHAR(SYSDATE, 'HH24:MI:SS'));
1290
1291 DBMS_SQL.CLOSE_CURSOR(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c);
1292
1293 -- Bug 876171: Add new lines to print no-data-found token
1294 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
1295 IF l_fail_ndf_flag = 0 THEN
1296 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
1297 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_NO_DATA_FOUND');
1298 l_no_data_found_message := FND_MESSAGE.GET;
1299 JG_UTILITY_PKG.out(RPAD(' ',75)||RPAD(l_no_data_found_message,30)||RPAD(' ',75));
1300 END IF;
1301 ELSE
1302 IF l_unal_ndf_flag = 0 THEN
1303 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
1304 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_NO_DATA_FOUND');
1305 l_no_data_found_message := FND_MESSAGE.GET;
1306 JG_UTILITY_PKG.out(RPAD(' ',45)||RPAD(l_no_data_found_message,30)||RPAD(' ',105));
1307 END IF;
1308 END IF;
1309
1310 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
1311 --
1312 -- If the last row returned had an offset account at the account range level, we need
1313 -- to create its corresponding offset line.
1314 --
1315 JG_UTILITY_PKG.debug('Last offset account was '||JG_ALLOCATE_JOURNALS_PKG.G_last_journal_qry_rec.offset_account);
1316 IF JG_ALLOCATE_JOURNALS_PKG.G_last_journal_qry_rec.offset_account IS NOT NULL THEN
1317 JG_CREATE_JOURNALS_PKG.Create_Offset_For_Acct_Range;
1318 END IF;
1319
1320 --
1321 -- For Allocation, after allocating, the allocated lines are printed to the report
1322 --
1323 JG_ALLOCATE_JOURNALS_PKG.Write_Report_Headings;
1324 JG_UTILITY_PKG.debug('Write Rep Headings: End');
1325 JG_ALLOCATE_JOURNALS_PKG.write_allocated_lines_output(l_total_fiscal_accted_cr_amt,
1326 l_total_fiscal_accted_dr_amt);
1327 END IF;
1328
1329 -- Bug 876171: Add the following output lines for end-of-report token
1330 IF (JG_JOURNAL_ALLOCATIONS_PKG.G_unalloc_request_id IS NULL) THEN
1331 IF JG_CREATE_JOURNALS_PKG.i > 0 OR l_fail_ndf_flag > 0 THEN
1332 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
1333 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_END_OF_REPORT');
1334 l_eof_message := FND_MESSAGE.GET;
1335 JG_UTILITY_PKG.out(RPAD(' ',75)||RPAD(l_eof_message,30)||RPAD(' ', 75));
1336 END IF;
1337 ELSE
1338 IF l_unal_ndf_flag > 0 THEN
1339 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
1340 FND_MESSAGE.SET_NAME(JG_JOURNAL_ALLOCATIONS_PKG.G_JG_appln_short_name, 'JG_ZZ_END_OF_REPORT');
1341 l_eof_message := FND_MESSAGE.GET;
1342 JG_UTILITY_PKG.out(RPAD(' ',45)||RPAD(l_eof_message,30)||RPAD(' ', 105));
1343 END IF;
1344 END IF;
1345
1346 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.allocate');
1347 EXCEPTION
1348 WHEN OTHERS THEN
1349 JG_UTILITY_PKG.log( '< JG_ALLOCATE_JOURNALS_PKG.allocate');
1350 IF (DBMS_SQL.IS_OPEN(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c)) THEN
1351 DBMS_SQL.CLOSE_CURSOR(JG_ALLOCATE_JOURNALS_PKG.G_journal_qry_c);
1352 END IF;
1353 RAISE;
1354 END allocate;
1355
1356 END JG_ALLOCATE_JOURNALS_PKG;