[Home] [Help]
PACKAGE BODY: APPS.JE_NO_VAT_RECON_PKG
Source
1 package body JE_NO_VAT_RECON_PKG as
2 /* $Header: jenovrcb.pls 120.3.12020000.2 2012/07/18 12:33:43 rshergil noship $ */
3
4 -- Some common string constants.
5
6 h_line varchar2(4);
7 h_offset varchar2(6);
8 h_ap varchar2(2);
9 h_ar varchar2(2);
10 h_gl varchar2(2);
11
12 procedure update_ar1
13 (p_bal_colname varchar2,
14 p_acc_colname varchar2
15 ) is
16 TYPE update_ar1_type IS REF CURSOR;
17 upd_ar1 update_ar1_type;
18 sql_stmt varchar2(2000);
19
20 h_bal varchar2(20);
21 h_acc varchar2(20);
22
23 h_jg_info_v1 varchar2(150);
24 h_jg_info_v2 varchar2(150);
25 h_jg_info_v3 varchar2(150);
26 h_jg_info_v4 varchar2(150);
27 h_jg_info_v5 varchar2(150);
28 h_jg_info_v6 varchar2(150);
29 h_jg_info_v7 varchar2(150);
30 h_jg_info_v8 varchar2(150);
31 h_jg_info_v9 varchar2(150);
32 h_jg_info_v10 varchar2(150);
33 h_jg_info_v11 varchar2(150);
34 h_jg_info_v12 varchar2(150);
35 h_jg_info_n1 number;
36 h_jg_info_n2 number;
37 h_jg_info_n3 number;
38 h_jg_info_n4 number;
39 h_jg_info_n5 number;
40 h_jg_info_n6 number;
41 h_jg_info_n7 number;
42 h_jg_info_n8 number;
43 h_jg_info_n9 number;
44 h_jg_info_n10 number;
45 h_jg_info_n11 number;
46 h_jg_info_n12 number;
47 h_jg_info_n13 number;
48 h_jg_info_n14 number;
49 h_jg_info_n15 number;
50 h_jg_info_d1 date;
51
52 h_trxgl_acct_amt number;
53
54 h_trx_id_prev number := -1;
55 h_trx_line_num_prev number := -1;
56
57 begin
58 h_line := 'LINE';
59 h_offset := 'OFFSET';
60 h_ap := 'AP';
61 h_ar := 'AR';
62 h_gl := 'GL';
63 sql_stmt := 'select '||p_bal_colname||', '||p_acc_colname||
64 ', jgzz.jg_info_v1'||
65 ', jgzz.jg_info_v2'||
66 ', jgzz.jg_info_v3'||
67 ', jgzz.jg_info_v4'||
68 ', jgzz.jg_info_v5'||
69 ', jgzz.jg_info_v6'||
70 ', jgzz.jg_info_v7'||
71 ', jgzz.jg_info_v8'||
72 ', jgzz.jg_info_v9'||
73 ', jgzz.jg_info_v10'||
74 ', jgzz.jg_info_v11'||
75 ', jgzz.jg_info_v12'||
76 ', jgzz.jg_info_n1'||
77 ', jgzz.jg_info_n2'||
78 ', jgzz.jg_info_n3'||
79 ', jgzz.jg_info_n4'||
80 ', jgzz.jg_info_n5'||
81 ', jgzz.jg_info_n6'||
82 ', jgzz.jg_info_n7'||
83 ', jgzz.jg_info_n8'||
84 ', jgzz.jg_info_n9'||
85 ', jgzz.jg_info_n10'||
86 ', jgzz.jg_info_n11'||
87 ', jgzz.jg_info_n12'||
88 ', jgzz.jg_info_n13'||
89 ', jgzz.jg_info_n14'||
90 ', jgzz.jg_info_n15'||
91 ', jgzz.jg_info_d1'||
92 ', ca.acctd_amount'||
93 ' from ra_customer_trx_lines_all ra,'||
94 'ra_cust_trx_line_gl_dist_all ca,'||
95 'jgzz_ar_tax_global_tmp jgzz,'||
96 'gl_code_combinations cc '||
97 'where ra.customer_trx_id=jgzz.jg_info_n1 '||
98 'and ra.line_number=jgzz.jg_info_n9 '||
99 'and ra.customer_trx_line_id=ca.customer_trx_line_id '||
100 'and ra.customer_trx_id=ca.customer_trx_id ' ||
101 'and ra.line_type= :line '||
102 'and cc.code_combination_id=ca.code_combination_id '||
103 'and jgzz.jg_info_v7 <> :offset '||
104 'and jgzz.jg_info_v2 = :ar';
105
106 OPEN upd_ar1 FOR sql_stmt USING h_line, h_offset, h_ar;
107 LOOP
108 FETCH upd_ar1 INTO h_bal, h_acc,
109 h_jg_info_v1,
110 h_jg_info_v2,
111 h_jg_info_v3,
112 h_jg_info_v4,
113 h_jg_info_v5,
114 h_jg_info_v6,
115 h_jg_info_v7,
116 h_jg_info_v8,
117 h_jg_info_v9,
118 h_jg_info_v10,
119 h_jg_info_v11,
120 h_jg_info_v12, -- gap
121 h_jg_info_n1,
122 h_jg_info_n2,
123 h_jg_info_n3,
124 h_jg_info_n4,
125 h_jg_info_n5,
126 h_jg_info_n6,
127 h_jg_info_n7,
128 h_jg_info_n8,
129 h_jg_info_n9,
130 h_jg_info_n10,
131 h_jg_info_n11,
132 h_jg_info_n12,
133 h_jg_info_n13,
134 h_jg_info_n14,
135 h_jg_info_n15, -- gap
136 h_jg_info_d1,
137 h_trxgl_acct_amt;
138 EXIT WHEN upd_ar1%NOTFOUND;
139
140 h_jg_info_n4 := h_trxgl_acct_amt;
141
142 if ((h_jg_info_n1 = h_trx_id_prev) and (h_jg_info_n9 = h_trx_line_num_prev)) then
143 h_jg_info_n7 := 0;
144 h_jg_info_n13 := 0;
145 end if;
146
147 insert into jgzz_ar_tax_global_tmp (
148 JG_INFO_V1,
149 JG_INFO_V2,
150 JG_INFO_V3,
151 JG_INFO_V4,
152 JG_INFO_V5,
153 JG_INFO_V6,
154 JG_INFO_V7,
155 JG_INFO_V8,
156 JG_INFO_V9,
157 JG_INFO_V10,
158 JG_INFO_V11,
159 JG_INFO_V12,
160 JG_INFO_V13,
161 JG_INFO_V14,
162 JG_INFO_V15,
163 JG_INFO_N1,
164 JG_INFO_N2,
165 JG_INFO_N3,
166 JG_INFO_N4,
167 JG_INFO_N5,
168 JG_INFO_N6,
169 JG_INFO_N7,
170 JG_INFO_N8,
171 JG_INFO_N9,
172 JG_INFO_N10,
173 JG_INFO_N11,
174 JG_INFO_N12,
175 JG_INFO_N13,
176 JG_INFO_N14,
177 JG_INFO_N15,
178 JG_INFO_D1,
179 JG_INFO_D2,
180 JG_INFO_D3,
181 JG_INFO_D4,
182 JG_INFO_D5,
183 JG_INFO_D6,
184 JG_INFO_D7,
185 JG_INFO_D8,
186 JG_INFO_D9,
187 JG_INFO_D10,
188 JG_INFO_D11,
189 JG_INFO_D12,
190 JG_INFO_D13,
191 JG_INFO_D14,
192 JG_INFO_D15 )
193 values (h_jg_info_v1,
194 h_jg_info_v2,
195 h_jg_info_v3,
196 h_jg_info_v4,
197 h_bal||'.'||h_acc, -- jg_info_v5, but no h_jg_info_v5
198 h_jg_info_v6,
199 h_jg_info_v7,
200 h_jg_info_v8,
201 h_jg_info_v9,
202 h_jg_info_v10,
203 h_jg_info_v11,
204 h_jg_info_v12,
205 NULL, -- jg_info_v13,
206 NULL, -- jg_info_v14,
207 NULL, -- jg_info_v15,
208 h_jg_info_n1,
209 h_jg_info_n2,
210 h_jg_info_n3,
211 h_jg_info_n4,
212 h_jg_info_n5,
213 h_jg_info_n6,
214 h_jg_info_n7,
215 h_jg_info_n8,
216 h_jg_info_n9,
217 h_jg_info_n10,
218 h_jg_info_n11,
219 h_jg_info_n12,
220 h_jg_info_n13,
221 h_jg_info_n14,
222 h_jg_info_n15,
223 h_jg_info_d1,
224 NULL, -- jg_info_d2,
225 NULL, -- jg_info_d3,
226 NULL, -- jg_info_d4,
227 NULL, -- jg_info_d5,
228 NULL, -- jg_info_d6,
229 NULL, -- jg_info_d7,
230 NULL, -- jg_info_d8,
231 NULL, -- jg_info_d9,
232 NULL, -- jg_info_d10,
233 NULL, -- jg_info_d11,
234 NULL, -- jg_info_d12,
235 NULL, -- jg_info_d13,
236 NULL, -- jg_info_d14,
237 to_date('2049/12/31','YYYY/MM/DD') -- jg_info_d15
238 );
239
240 h_trx_id_prev := h_jg_info_n1;
241 h_trx_line_num_prev := h_jg_info_n9;
242
243 END LOOP;
244 CLOSE upd_ar1;
245
246 delete from jgzz_ar_tax_global_tmp
247 where jg_info_v2 = 'AR'
248 and jg_info_v7 <> 'OFFSET'
249 and (jg_info_d15 is null
250 or jg_info_d15 <> to_date('2049/12/31','YYYY/MM/DD'));
251
252 end; -- Procedure update_ar1
253
254 end JE_NO_VAT_RECON_PKG;