[Home] [Help]
PACKAGE BODY: APPS.ZX_AP_EXTRACT_PKG
Source
1 PACKAGE BODY ZX_AP_EXTRACT_PKG AS
2 /* $Header: zxripextractpvtb.pls 120.44.12010000.6 2009/01/14 16:17:27 bibeura ship $ */
3
4 PROCEDURE assign_global_parameters(
5 p_trl_global_variables_rec IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE);
6
7 PROCEDURE build_sql;
8
9 PROCEDURE execute_sql_stmt;
10
11 PROCEDURE filter_validated;
12
13 PROCEDURE fetch_tax_info(
14 p_statement IN VARCHAR2);
15
16 PROCEDURE init_gt_variables;
17
18 PROCEDURE insert_tax_info;
19
20 TYPE l_sql_statement_tabtype IS TABLE OF VARCHAR2(32600)
21 INDEX BY BINARY_INTEGER;
22 l_sql_statement_tbl l_sql_statement_tabtype;
23
24
25
26 -- Declare Global Variables
27
28 c_lines_per_insert CONSTANT NUMBER := 1000;
29 g_sql_statement VARCHAR2(32000);
30 --g_column_list_trx_dist_lvl VARCHAR2(32000);
31 --g_column_list_trx_line_lvl VARCHAR2(32000);
32 l_msg VARCHAR2(50);
33
34 -- Declare table type global variables
35
36 gt_detail_tax_line_id ZX_EXTRACT_PKG.detail_tax_line_id_tbl;
37 gt_application_id ZX_EXTRACT_PKG.application_id_tbl;
38 gt_event_class_code ZX_EXTRACT_PKG.event_class_code_tbl;
39 gt_line_class ZX_EXTRACT_PKG.trx_line_class_tbl;
40 GT_TRX_TYPE_DESCRIPTION ZX_EXTRACT_PKG.TRX_TYPE_DESCRIPTION_TBL;
41 gt_internal_organization_id ZX_EXTRACT_PKG.internal_organization_id_tbl;
42 gt_extract_rep_line_num ZX_EXTRACT_PKG.extract_report_line_number_tbl;
43 gt_ledger_id ZX_EXTRACT_PKG.ledger_id_tbl;
44 gt_doc_event_status ZX_EXTRACT_PKG.doc_event_status_tbl;
45 gt_application_doc_status ZX_EXTRACT_PKG.application_doc_status_tbl;
46 gt_doc_seq_id ZX_EXTRACT_PKG.doc_seq_id_tbl;
47 gt_doc_seq_name ZX_EXTRACT_PKG.doc_seq_name_tbl;
48 gt_doc_seq_value ZX_EXTRACT_PKG.doc_seq_value_tbl;
49 gt_establishment_id ZX_EXTRACT_PKG.establishment_id_tbl;
50 gt_batch_source_id ZX_EXTRACT_PKG.batch_source_id_tbl;
51 gt_currency_conversion_date ZX_EXTRACT_PKG.currency_conversion_date_tbl;
52 gt_currency_conversion_rate ZX_EXTRACT_PKG.currency_conversion_rate_tbl;
53 gt_currency_conversion_type ZX_EXTRACT_PKG.currency_conversion_type_tbl;
54 gt_minimum_accountable_unit ZX_EXTRACT_PKG.minimum_accountable_unit_tbl;
55 gt_precision ZX_EXTRACT_PKG.precision_tbl;
56 gt_trx_communicated_date ZX_EXTRACT_PKG.trx_communicated_date_tbl;
57 gt_trx_currency_code ZX_EXTRACT_PKG.trx_currency_code_tbl;
58 gt_trx_id ZX_EXTRACT_PKG.trx_id_tbl;
59 gt_trx_number ZX_EXTRACT_PKG.trx_number_tbl;
60 gt_trx_date ZX_EXTRACT_PKG.trx_date_tbl;
61 gt_trx_description ZX_EXTRACT_PKG.trx_description_tbl;
62 gt_trx_due_date ZX_EXTRACT_PKG.trx_due_date_tbl;
63 gt_trx_line_description ZX_EXTRACT_PKG.trx_line_description_tbl;
64 gt_trx_line_id ZX_EXTRACT_PKG.trx_line_id_tbl;
65 gt_taxable_item_source_id zx_extract_pkg.taxable_item_source_id_tbl;
66 gt_trx_line_number ZX_EXTRACT_PKG.trx_line_number_tbl;
67 gt_trx_line_quantity ZX_EXTRACT_PKG.trx_line_quantity_tbl;
68 gt_trx_line_amt ZX_EXTRACT_PKG.trx_line_amt_tbl;
69 gt_trx_line_type ZX_EXTRACT_PKG.trx_line_type_tbl;
70 gt_trx_shipping_date ZX_EXTRACT_PKG.trx_shipping_date_tbl;
71 gt_uom_code ZX_EXTRACT_PKG.uom_code_tbl;
72 gt_related_doc_date ZX_EXTRACT_PKG.related_doc_date_tbl;
73 gt_related_doc_entity_code ZX_EXTRACT_PKG.related_doc_entity_code_tbl;
74 gt_related_doc_event_cls_code ZX_EXTRACT_PKG.related_doc_event_cls_code_tbl;
75 gt_related_doc_number ZX_EXTRACT_PKG.related_doc_number_tbl;
76 gt_related_doc_trx_id ZX_EXTRACT_PKG.related_doc_trx_id_tbl;
77 gt_applied_from_appl_id ZX_EXTRACT_PKG.applied_from_appl_id_tbl;
78 gt_applied_from_entity_code ZX_EXTRACT_PKG.applied_from_entity_code_tbl;
79 gt_applied_from_event_cls_code ZX_EXTRACT_PKG.applied_from_event_cls_cd_tbl;
80 gt_applied_from_line_id ZX_EXTRACT_PKG.applied_from_line_id_tbl;
81 gt_applied_from_trx_id ZX_EXTRACT_PKG.applied_from_trx_id_tbl;
82 gt_applied_from_trx_number ZX_EXTRACT_PKG.applied_from_trx_number_tbl;
83 gt_applied_to_appl_id ZX_EXTRACT_PKG.applied_to_application_id_tbl;
84 gt_applied_to_entity_code ZX_EXTRACT_PKG.applied_to_entity_code_tbl;
85 gt_applied_to_event_cls_code ZX_EXTRACT_PKG.applied_to_event_cls_code_tbl;
86 gt_applied_to_trx_id ZX_EXTRACT_PKG.applied_to_trx_id_tbl;
87 gt_applied_to_trx_line_id ZX_EXTRACT_PKG.applied_to_trx_line_id_tbl;
88 gt_applied_to_trx_number ZX_EXTRACT_PKG.applied_to_trx_number_tbl;
89 gt_adjusted_doc_appl_id ZX_EXTRACT_PKG.adjusted_doc_appl_id_tbl;
90 gt_adjusted_doc_date ZX_EXTRACT_PKG.adjusted_doc_date_tbl;
91 gt_adjusted_doc_entity_code ZX_EXTRACT_PKG.adjusted_doc_entity_code_tbl;
92 gt_adjusted_doc_event_cls_code ZX_EXTRACT_PKG.adjusted_doc_event_cls_cd_tbl;
93 GT_ADJUSTED_DOC_NUMBER ZX_EXTRACT_PKG.ADJUSTED_DOC_NUMBER_TBL;
94 gt_country_of_supply ZX_EXTRACT_PKG.country_of_supply_tbl;
95 gt_default_taxation_country ZX_EXTRACT_PKG.default_taxation_country_tbl;
96 gt_merchant_party_doc_num ZX_EXTRACT_PKG.merchant_party_doc_num_tbl;
97 gt_merchant_party_name ZX_EXTRACT_PKG.merchant_party_name_tbl;
98 gt_merchant_party_reference ZX_EXTRACT_PKG.merchant_party_reference_tbl;
99 gt_merchant_party_tax_reg_num ZX_EXTRACT_PKG.merchant_party_tax_reg_num_tbl;
100 gt_merchant_party_taxpayer_id ZX_EXTRACT_PKG.merchant_party_taxpayer_id_tbl;
101 gt_ref_doc_application_id ZX_EXTRACT_PKG.ref_doc_application_id_tbl;
102 gt_ref_doc_entity_code ZX_EXTRACT_PKG.ref_doc_entity_code_tbl;
103 gt_ref_doc_event_cls_code ZX_EXTRACT_PKG.ref_doc_event_class_code_tbl;
104 gt_ref_doc_line_id ZX_EXTRACT_PKG.ref_doc_line_id_tbl;
105 gt_ref_doc_line_quantity ZX_EXTRACT_PKG.ref_doc_line_quantity_tbl;
106 gt_ref_doc_trx_id ZX_EXTRACT_PKG.ref_doc_trx_id_tbl;
107 gt_start_expense_date ZX_EXTRACT_PKG.start_expense_date_tbl;
108 gt_assessable_value ZX_EXTRACT_PKG.assessable_value_tbl;
109 gt_document_sub_type ZX_EXTRACT_PKG.document_sub_type_tbl;
110 gt_line_intended_use ZX_EXTRACT_PKG.line_intended_use_tbl;
111 gt_product_category ZX_EXTRACT_PKG.product_category_tbl;
112 gt_product_description ZX_EXTRACT_PKG.product_description_tbl;
113 gt_prod_fisc_classification ZX_EXTRACT_PKG.prod_fisc_classification_tbl;
114 gt_product_id ZX_EXTRACT_PKG.product_id_tbl;
115 gt_supplier_exchange_rate ZX_EXTRACT_PKG.supplier_exchange_rate_tbl;
116 gt_supplier_tax_invoice_date ZX_EXTRACT_PKG.supplier_tax_invoice_date_tbl;
117 gt_supplier_tax_invoice_num ZX_EXTRACT_PKG.supplier_tax_invoice_num_tbl;
118 gt_tax_invoice_date ZX_EXTRACT_PKG.tax_invoice_date_tbl;
119 gt_tax_invoice_number ZX_EXTRACT_PKG.tax_invoice_number_tbl;
120 gt_trx_business_category ZX_EXTRACT_PKG.trx_business_category_tbl;
121 gt_user_defined_fisc_class ZX_EXTRACT_PKG.user_defined_fisc_class_tbl;
122 gt_nrec_tax_amt_tax_curr ZX_EXTRACT_PKG.nrec_tax_amt_tax_curr_tbl;
123 gt_offset_tax_rate_code ZX_EXTRACT_PKG.offset_tax_rate_code_tbl;
124 gt_orig_rec_nrec_tax_amt ZX_EXTRACT_PKG.orig_rec_nrec_tax_amt_tbl;
125 gt_orig_tax_amt ZX_EXTRACT_PKG.orig_tax_amt_tbl;
126 gt_orig_tax_amt_tax_curr ZX_EXTRACT_PKG.orig_tax_amt_tax_curr_tbl;
127 gt_orig_taxable_amt ZX_EXTRACT_PKG.orig_taxable_amt_tbl;
128 gt_orig_taxable_amt_tax_curr ZX_EXTRACT_PKG.orig_taxable_amt_tax_curr_tbl;
129 gt_rec_tax_amt_tax_curr ZX_EXTRACT_PKG.rec_tax_amt_tax_curr_tbl;
130 gt_recovery_rate_code ZX_EXTRACT_PKG.recovery_rate_code_tbl;
131 gt_recovery_type_code ZX_EXTRACT_PKG.recovery_type_code_tbl;
132 gt_tax ZX_EXTRACT_PKG.tax_tbl;
133 gt_tax_amt ZX_EXTRACT_PKG.tax_amt_tbl;
134 gt_tax_amt_funcl_curr ZX_EXTRACT_PKG.tax_amt_funcl_curr_tbl;
135 gt_tax_amt_tax_curr ZX_EXTRACT_PKG.tax_amt_tax_curr_tbl;
136 gt_tax_apportionment_line_num ZX_EXTRACT_PKG.tax_apportionment_line_num_tbl;
137 gt_tax_currency_code ZX_EXTRACT_PKG.tax_currency_code_tbl;
138 gt_tax_date ZX_EXTRACT_PKG.tax_date_tbl;
139 gt_tax_determine_date ZX_EXTRACT_PKG.tax_determine_date_tbl;
140 gt_tax_jurisdiction_code ZX_EXTRACT_PKG.tax_jurisdiction_code_tbl;
141 gt_tax_line_id ZX_EXTRACT_PKG.tax_line_id_tbl;
142 gt_tax_line_number ZX_EXTRACT_PKG.tax_line_number_tbl;
143 gt_tax_line_user_attribute1 ZX_EXTRACT_PKG.tax_line_user_attribute1_tbl;
144 gt_tax_line_user_attribute10 ZX_EXTRACT_PKG.tax_line_user_attribute10_tbl;
145 gt_tax_line_user_attribute11 ZX_EXTRACT_PKG.tax_line_user_attribute11_tbl;
146 gt_tax_line_user_attribute12 ZX_EXTRACT_PKG.tax_line_user_attribute12_tbl;
147 gt_tax_line_user_attribute13 ZX_EXTRACT_PKG.tax_line_user_attribute13_tbl;
148 gt_tax_line_user_attribute14 ZX_EXTRACT_PKG.tax_line_user_attribute14_tbl;
149 gt_tax_line_user_attribute15 ZX_EXTRACT_PKG.tax_line_user_attribute15_tbl;
150 gt_tax_line_user_attribute2 ZX_EXTRACT_PKG.tax_line_user_attribute2_tbl;
151 gt_tax_line_user_attribute3 ZX_EXTRACT_PKG.tax_line_user_attribute3_tbl;
152 gt_tax_line_user_attribute4 ZX_EXTRACT_PKG.tax_line_user_attribute4_tbl;
153 gt_tax_line_user_attribute5 ZX_EXTRACT_PKG.tax_line_user_attribute5_tbl;
154 gt_tax_line_user_attribute6 ZX_EXTRACT_PKG.tax_line_user_attribute6_tbl;
155 gt_tax_line_user_attribute7 ZX_EXTRACT_PKG.tax_line_user_attribute7_tbl;
156 gt_tax_line_user_attribute8 ZX_EXTRACT_PKG.tax_line_user_attribute8_tbl;
157 gt_tax_line_user_attribute9 ZX_EXTRACT_PKG.tax_line_user_attribute9_tbl;
158 gt_tax_line_user_category ZX_EXTRACT_PKG.tax_line_user_category_tbl;
159 gt_tax_rate ZX_EXTRACT_PKG.tax_rate_tbl;
160 gt_tax_rate_code ZX_EXTRACT_PKG.tax_rate_code_tbl;
161 GT_TAX_RATE_CODE_NAME ZX_EXTRACT_PKG.TAX_RATE_CODE_NAME_TBL;
162 GT_TAX_RATE_VAT_TRX_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_CODE_TBL;
163 GT_TAX_TYPE_CODE ZX_EXTRACT_PKG.TAX_TYPE_CODE_TBL;
164 gt_tax_rate_id ZX_EXTRACT_PKG.tax_rate_id_tbl;
165 gt_tax_recovery_rate ZX_EXTRACT_PKG.tax_recovery_rate_tbl;
166 gt_tax_regime_code ZX_EXTRACT_PKG.tax_regime_code_tbl;
167 gt_tax_status_code ZX_EXTRACT_PKG.tax_status_code_tbl;
168 gt_tax_status_id ZX_EXTRACT_PKG.tax_status_id_tbl;
169 gt_taxable_amt ZX_EXTRACT_PKG.taxable_amt_tbl;
170 gt_taxable_amt_funcl_curr ZX_EXTRACT_PKG.taxable_amt_funcl_curr_tbl;
171 gt_billing_tp_name ZX_EXTRACT_PKG.billing_tp_name_tbl;
172 gt_billing_tp_number ZX_EXTRACT_PKG.billing_tp_number_tbl;
173 -- Party ids --
174 GT_SHIPPING_TP_ID ZX_EXTRACT_PKG.SHIPPING_TP_ID_TBL;
175 GT_BILLING_TRADING_PARTNER_ID ZX_EXTRACT_PKG.BILLING_TRADING_PARTNER_ID_TBL;
176 GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
177 GT_SHIPPING_TP_SITE_ID ZX_EXTRACT_PKG.SHIPPING_TP_SITE_ID_TBL;
178 GT_BILLING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
179 GT_SHIPPING_TP_ADDRESS_ID ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS_ID_TBL;
180
181 gt_bill_from_pty_tax_prof_id ZX_EXTRACT_PKG.bill_from_pty_tax_prof_id_tbl;
182 gt_bill_from_site_tax_prof_id ZX_EXTRACT_PKG.bill_from_site_tax_prof_id_tbl;
183 gt_billing_tp_taxpayer_id ZX_EXTRACT_PKG.billing_tp_taxpayer_id_tbl;
184 gt_ship_to_site_tax_prof_id ZX_EXTRACT_PKG.ship_to_site_tax_prof_id_tbl;
185 gt_ship_from_site_tax_prof_id ZX_EXTRACT_PKG.ship_from_site_tax_prof_id_tbl;
186 gt_ship_to_pty_tax_prof_id ZX_EXTRACT_PKG.ship_to_party_tax_prof_id_tbl;
187 gt_ship_from_pty_tax_prof_id ZX_EXTRACT_PKG.ship_from_pty_tax_prof_id_tbl;
188 gt_hq_estb_reg_number ZX_EXTRACT_PKG.hq_estb_reg_number_tbl;
189 gt_tax_line_registration_num ZX_EXTRACT_PKG.tax_line_registration_num_tbl;
190 gt_legal_entity_tax_reg_num ZX_EXTRACT_PKG.legal_entity_tax_reg_num_tbl;
191 gt_own_hq_pty_site_prof_id ZX_EXTRACT_PKG.own_hq_party_site_prof_id_tbl;
192 gt_own_hq_pty_tax_prof_id ZX_EXTRACT_PKG.own_hq_party_tax_prof_id_tbl;
193 gt_port_of_entry_code ZX_EXTRACT_PKG.port_of_entry_code_tbl;
194 gt_registration_party_type ZX_EXTRACT_PKG.registration_party_type_tbl;
195 gt_cancel_flag ZX_EXTRACT_PKG.cancel_flag_tbl;
196 gt_historical_flag ZX_EXTRACT_PKG.historical_flag_tbl;
197 gt_mrc_tax_line_flag ZX_EXTRACT_PKG.mrc_tax_line_flag_tbl;
198 gt_offset_flag ZX_EXTRACT_PKG.offset_flag_tbl;
199 gt_reporting_only_flag ZX_EXTRACT_PKG.reporting_only_flag_tbl;
200 gt_self_assessed_flag ZX_EXTRACT_PKG.self_assessed_flag_tbl;
201 gt_tax_amt_included_flag ZX_EXTRACT_PKG.tax_amt_included_flag_tbl;
202 gt_tax_only_flag ZX_EXTRACT_PKG.tax_only_flag_tbl;
203 gt_tax_recoverable_flag ZX_EXTRACT_PKG.tax_recoverable_flag_tbl;
204 gt_posted_flag ZX_EXTRACT_PKG.posted_flag_tbl;
205 gt_reverse_flag ZX_EXTRACT_PKG.reverse_flag_tbl;
206 gt_entity_code ZX_EXTRACT_PKG.entity_code_tbl;
207 gt_trx_level_type ZX_EXTRACT_PKG.TRX_LEVEL_TYPE_TBL; --Bug 5393051
208 gt_unit_price_tbl ZX_EXTRACT_PKG.UNIT_PRICE_TBL; -- Bug 5439099
209 gt_gl_date zx_extract_pkg.gl_date_tbl; --Bug 5523095
210 gt_tax_rate_code_description ZX_EXTRACT_PKG.tax_rate_code_description_tbl;
211 g_created_by number(15);
212 g_creation_date date;
213 g_last_updated_by number(15);
214 g_last_update_date date;
215 g_last_update_login number(15);
216 g_program_application_id number;
217 g_program_id number;
218 g_program_login_id number;
219
220
221 gt_actg_source_id ZX_EXTRACT_PKG.actg_source_id_tbl;
222
223 -- declare global variables to assign global parameters
224
225 g_reporting_level VARCHAR2(30);
226 g_reporting_context VARCHAR2(30);
227 -- apai g_legal_entity_level VARCHAR2(30);
228 g_legal_entity_id NUMBER;
229 g_summary_level VARCHAR2(30);
230 g_ledger_id NUMBER;
231 g_register_type VARCHAR2(30);
232 g_product VARCHAR2(30);
233 g_matrix_report VARCHAR2(30);
234 g_currency_code_low VARCHAR2(30);
235 g_currency_code_high VARCHAR2(30);
236 g_include_ap_std_trx_class VARCHAR2(1);
237 g_include_ap_dm_trx_class VARCHAR2(1);
238 g_include_ap_cm_trx_class VARCHAR2(1);
239 g_include_ap_prep_trx_class VARCHAR2(1);
240 g_include_ap_mix_trx_class VARCHAR2(1);
241 g_include_ap_exp_trx_class VARCHAR2(1);
242 g_include_ap_int_trx_class VARCHAR2(1);
243 g_include_ar_inv_trx_class VARCHAR2(1);
244 g_include_ar_appl_trx_class VARCHAR2(1);
245 g_include_ar_adj_trx_class VARCHAR2(1);
246 g_include_ar_misc_trx_class VARCHAR2(1);
247 g_include_ar_br_trx_class VARCHAR2(1);
248 g_include_gl_manual_lines VARCHAR2(30);
249 g_trx_number_low VARCHAR2(30);
250 g_trx_number_high VARCHAR2(30);
251 g_ar_trx_printing_status VARCHAR2(30);
252 g_ar_exemption_status VARCHAR2(30);
253 g_gl_date_low date;
254 g_gl_date_high date;
255 g_trx_date_low date;
256 g_trx_date_high date;
257 g_trx_date_low_ln date;
258 g_trx_date_high_ln date;
259 g_gl_period_name_low VARCHAR2(15);
260 g_gl_period_name_high VARCHAR2(15);
261 g_trx_date_period_name_low VARCHAR2(15);
262 g_trx_date_period_name_high VARCHAR2(15);
263 g_tax_jurisdiction_code VARCHAR2(30);
267 g_tax_status_code VARCHAR2(30);
264 g_first_party_tax_reg_num VARCHAR2(30);
265 g_tax_regime_code VARCHAR2(30);
266 g_tax VARCHAR2(30);
268 g_tax_rate_code_low VARCHAR2(30);
269 g_tax_rate_code_high VARCHAR2(30);
270 g_tax_type_code_low VARCHAR2(30);
271 g_tax_type_code_high VARCHAR2(30);
272 g_document_sub_type VARCHAR2(30);
273 g_trx_business_category VARCHAR2(30);
274 g_tax_invoice_date_low VARCHAR2(30);
275 g_tax_invoice_date_high VARCHAR2(30);
276 g_posting_status VARCHAR2(30);
277 g_extract_accted_tax_lines VARCHAR2(30);
278 g_include_accounting_segments VARCHAR2(1);
279 g_balancing_segment_low VARCHAR2(30);
280 g_balancing_segment_high VARCHAR2(30);
281 g_include_discounts VARCHAR2(1);
282 g_extract_starting_line_num NUMBER;
283 g_request_id NUMBER;
284 g_report_name VARCHAR2(30);
285 g_vat_transaction_type_code VARCHAR2(30);
286 g_include_fully_nr_tax_flag VARCHAR2(30);
287 g_municipal_tax_type_code_low VARCHAR2(30);
288 g_municipal_tax_type_code_high VARCHAR2(30);
289 g_prov_tax_type_code_low VARCHAR2(30);
290 g_prov_tax_type_code_high VARCHAR2(30);
291 g_excise_tax_type_code_low VARCHAR2(30);
292 g_excise_tax_type_code_high VARCHAR2(30);
293 g_non_taxable_tax_type_code VARCHAR2(30);
294 g_per_tax_type_code_low VARCHAR2(30);
295 g_per_tax_type_code_high VARCHAR2(30);
296 g_fed_per_tax_type_code_low VARCHAR2(30);
297 g_fed_per_tax_type_code_high VARCHAR2(30);
298 g_vat_tax_type_code VARCHAR2(30);
299 g_excise_tax VARCHAR2(30);
300 g_vat_additional_tax VARCHAR2(30);
301 g_vat_non_taxable_tax VARCHAR2(30);
302 g_vat_not_tax VARCHAR2(30);
303 g_vat_perception_tax VARCHAR2(30);
304 g_vat_tax VARCHAR2(30);
305 g_inc_self_wd_tax VARCHAR2(30);
306 g_excluding_trx_letter VARCHAR2(30);
307 g_trx_letter_low VARCHAR2(30);
308 g_trx_letter_high VARCHAR2(30);
309 g_include_referenced_source VARCHAR2(30);
310 g_party_name VARCHAR2(30);
311 g_batch_name VARCHAR2(30);
312 g_batch_date_low DATE;
313 g_batch_date_high DATE;
314 g_batch_source_id VARCHAR2(30);
315 g_adjusted_doc_from VARCHAR2(30);
316 g_adjusted_doc_to VARCHAR2(30);
317 g_standard_vat_tax_rate VARCHAR2(30);
318 g_municipal_tax VARCHAR2(30);
319 g_provincial_tax VARCHAR2(30);
320 g_tax_account_low VARCHAR2(30);
321 g_tax_account_high VARCHAR2(30);
322 g_exp_cert_date_from DATE;
323 g_exp_cert_date_to DATE;
324 g_exp_method VARCHAR2(30);
325 g_print_company_info VARCHAR2(30);
326 g_reprint VARCHAR2(1);
327 g_errbuf VARCHAR2(30);
328 g_retcode NUMBER := 2;
329
330 g_extract_line_num NUMBER :=1;
331 g_accounting_status VARCHAR2(30);
332 g_chart_of_accounts_id NUMBER;
333 g_gl_or_trx_date_filter varchar(1); --Bug 5347188
334 g_reported_status VARCHAR2(30);
335
336 -- Declare global varibles for FND log messages
337
338 g_current_runtime_level NUMBER;
339 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
340 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
341 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
342 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
343 g_error_buffer VARCHAR2(100);
344
345
346 /*===========================================================================+
347 | PROCEDURE |
348 | INSERT_TAX_DATA |
349 | |
350 | DESCRIPTION |
351 | This procedure takes the input parameters from ZX_EXTRACT_PKG |
352 | and builds a dynamic SQL statement clauses based on the parameters, |
353 | supplies them as output parameters. |
354 | |
355 | SCOPE - Public |
356 | |
357 | NOTES |
358 | |
359 | MODIFICATION HISTORY |
360 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
361 +===========================================================================*/
362
363
364 PROCEDURE insert_tax_data (
365 p_trl_global_variables_rec IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
366 )
367 IS
368
369 BEGIN
373 IF (g_level_procedure >= g_current_runtime_level ) THEN
370 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
371
372 g_retcode := p_trl_global_variables_rec.retcode;
374 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.INSERT_TAX_DATA.BEGIN',
375 'ZX_AP_EXTRACT_PKG: INSERT_TAX_DATA(+)');
376 END IF;
377
378 assign_global_parameters(
379 p_trl_global_variables_rec => P_TRL_GLOBAL_VARIABLES_REC);
380
381 IF g_retcode <> 2 THEN
382 build_sql;
383 END IF;
384
385 IF ( g_level_statement>= g_current_runtime_level ) THEN
386 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_EXTRACT_PKG',
387 ' g_ret_code after build_sql : '||g_retcode );
388 END IF;
389
390 IF g_retcode <> 2 THEN
391 execute_sql_stmt;
392 END IF;
393
394 IF ( g_level_statement>= g_current_runtime_level ) THEN
395 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_EXTRACT_PKG',
396 ' g_ret_code after execute_sql_stmt : '||g_retcode );
397 END IF;
398
399 IF g_retcode <> 2 THEN
400 filter_validated;
401 END IF;
402
403 IF ( g_level_statement>= g_current_runtime_level ) THEN
404 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_EXTRACT_PKG',
405 ' g_ret_code after filter_validated : '||g_retcode );
406 END IF;
407
408 p_trl_global_variables_rec.retcode := g_retcode;
409
410 IF (g_level_procedure >= g_current_runtime_level ) THEN
411 FND_LOG.STRING(g_level_procedure,
412 'ZX.TRL.ZX_AP_EXTRACT_PKG.INSERT_TAX_DATA.END',
413 'ZX_AP_EXTRACT_PKG: INSERT_TAX_DATA(-)');
414 END IF;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
419 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
420 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_tax_data- '|| g_error_buffer);
421 FND_MSG_PUB.Add;
422 IF (g_level_unexpected >= g_current_runtime_level ) THEN
423 FND_LOG.STRING(g_level_unexpected,
424 'ZX.TRL.ZX_AP_EXTRACT_PKG.insert_tax_data',
425 g_error_buffer);
426 END IF;
427 p_trl_global_variables_rec.retcode := 2;
428
429
430 END insert_tax_data;
431
432
433 /*===========================================================================+
434 | PROCEDURE |
435 | build_sql |
436 | |
437 | DESCRIPTION |
438 | This procedure builds dynamic SQL statement for AP tax data extract. |
439 | |
440 | SCOPE - Private |
441 | |
442 | NOTES |
443 | |
444 | MODIFICATION HISTORY |
445 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
446 +===========================================================================*/
447
448
449 PROCEDURE BUILD_SQL IS
450
451 L_REPORTING_CONTEXT_VAL VARCHAR2(1000);
452 L_SELECT_ACCOUNT_SEG VARCHAR2(500);
453 L_WHERE_REPORT_CONTEXT VARCHAR2(500);
454 L_WHERE_GL_DATE VARCHAR2(200);
455 L_WHERE_GL_TRX_DATE varchar2(1000);--Bug 5347188
456 --L_WHERE_GL_DATE_I VARCHAR2(200);
457 L_WHERE_TRX_DATE VARCHAR2(500);
458 L_WHERE_TAX_CODE VARCHAR2(200);
459 L_WHERE_CURRENCY_CODE VARCHAR2(200);
460 --L_WHERE_CURRENCY_CODE_DIST_I VARCHAR2(200);
461 --L_WHERE_CURRENCY_CODE_DIST_T VARCHAR2(200);
462 --L_WHERE_CURRENCY_CODE_I VARCHAR2(200);
463 --L_WHERE_CURRENCY_CODE_AX VARCHAR2(200);
464 --L_WHERE_CURRENCY_CODE_AXSUB VARCHAR2(200);
465 --L_WHERE_GBL_TAX_DATE VARCHAR2(200);
466 L_WHERE_TAX_CODE_VAT_TRX_TYPE VARCHAR2(200);
467 L_WHERE_TAX_CODE_TYPE VARCHAR2(200);
468 --L_WHERE_TP_NAME_AP VARCHAR2(200);
469 L_WHERE_LEDGER_ID VARCHAR2(500);
470 L_BALANCING_SEGMENT VARCHAR2(50);
471 L_ACCOUNTING_SEGMENT VARCHAR2(50);
472 --L_WHERE_GL_FLEX VARCHAR2(200);
473 L_WHERE_TRX_CLASS VARCHAR2(10000);
474 L_WHERE_INCLUDE_FLAG VARCHAR2(1);
475 --L_THIRD_PARTY_REPORTING_LEVEL VARCHAR2(25);
476 --L_WHERE_AP_DIST_ATT1_IS_NULL VARCHAR2(5000);
477 --L_INIT_PARAM VARCHAR2(4);
478 L_WHERE_REGISTER_TYPE VARCHAR2(3000);
479 L_WHERE_TRX_NUM VARCHAR2(1000);
480 --L_WHERE_ACCOUNT_SEG VARCHAR2(1000);
481 --L_TOTAL_LINES_SQL VARCHAR2(32000);
482 --L_TOTAL_LINES NUMBER;
483 --L_ACCOUNTED_LINES_SQL VARCHAR2(32000);
484 --L_ACCOUNTED_LINES NUMBER;
485 --L_TOTAL NUMBER;
486 --L_CURSOR NUMBER;
487 --L_RETVAL NUMBER;
491 --L_TRX_DATE_HIGH DATE;
488 --L_WHERE_TAX_CLASS VARCHAR2(500);
489 --L_WHERE_TAX_CLASS_GRP VARCHAR2(2000);
490 --L_TRX_DATE_LOW DATE;
492 --L_WHERE_ATT3_NULL VARCHAR2(500);
493 --L_MSG VARCHAR2(50);
494 --L_WHERE_POSTING_STATUS_DIST_T VARCHAR2(500);
495 --L_WHERE_POSTING_STATUS_DIST_I VARCHAR2(500);
496 --L_WHERE_SPECIAL_PL_HU_C VARCHAR2(500);
497
498 L_WHERE_TRX_LINE_CLASS VARCHAR2(500);
499 l_sql_statement VARCHAR2(32000);
500 --L_COLUMN_LIST_TRX_DIST_LVL VARCHAR2(32000);
501 --L_COLUMN_LIST_TRX_LINE_LVL VARCHAR2(32000);
502
503 L_ADD NUMBER;
504 L_WHERE_BATCH_DATE VARCHAR2(500);
505 L_WHERE_BATCH_NAME VARCHAR2(500);
506 L_WHERE_PARTY_NAME VARCHAR2(500);
507 --L_WHERE_TRADING_PARTNER_ID VARCHAR2(200);
508 --
509 -- MRC changes
510 --
511
512 L_WHERE_DOCUMENT_SUB_TYPE VARCHAR2(500);
513 L_WHERE_ADJUSTED_DOC_NUM VARCHAR2(500);
514 L_WHERE_TRX_BUSINESS_CATEGORY VARCHAR2(500);
515 L_WHERE_TAX_INVOICE_DATE VARCHAR2(500);
516 L_WHERE_TAX_REGIME_CODE VARCHAR2(500);
517 L_WHERE_TAX_JURISDICTION_CODE VARCHAR2(500);
518 L_WHERE_FIRST_PTY_TAX_REG_NUM VARCHAR2(500);
519 L_WHERE_TAX VARCHAR2(500);
520 L_WHERE_TAX_STATUS_CODE VARCHAR2(500);
521 L_WHERE_TAX_RATE_CODE VARCHAR2(500);
522 L_WHERE_TAX_TYPE_CODE VARCHAR2(500);
523 L_WHERE_VAT_TRANSACTION_TYPE VARCHAR2(500);
524 L_WHERE_LEGAL_ENTITY_ID VARCHAR2(500);
525
526 L_WHERE_ACCOUNTING_STATUS VARCHAR2(500);
527 L_WHERE_REPORTED_STATUS VARCHAR2(500);
528
529 -- Variables used to change predicate return by FND multi org API
530
531 l_fnd_mo_org_stg VARCHAR2(2000);
532 l_internal_org_stg VARCHAR2(2000);
533 l_string_len NUMBER;
534 l_org_len NUMBER;
535 l_get_org_id VARCHAR2(25);
536 l_equal_pos NUMBER;
537
538 BEGIN
539
540 IF (g_level_procedure >= g_current_runtime_level ) THEN
541 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL.BEGIN',
542 'ZX_AP_EXTRACT_PKG: BUILD_SQL(+)');
543 END IF;
544
545
546 -- ===========================================================+
547 -- Building Where Clauses:
548 -- L_WHERE_REPORTING_CONTEXT :
549 -- These where clauses will be used in the queries
550 -- to restrict the data from multi-org tables to the appropriate
551 -- reporting context .
552 -- ==============================================================+
553
554 L_REPORTING_CONTEXT_VAL := ' ' ||TO_CHAR(G_REPORTING_CONTEXT) || ' ' ;
555
556 FND_MO_REPORTING_API.INITIALIZE(G_REPORTING_LEVEL,G_REPORTING_CONTEXT,'AUTO');
557
558 l_fnd_mo_org_stg := FND_MO_REPORTING_API.GET_PREDICATE('ZX_DET',NULL,L_REPORTING_CONTEXT_VAL);
559 IF (g_level_procedure >= g_current_runtime_level ) THEN
560 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
561 'l_fnd_mo_org_stg = '||l_fnd_mo_org_stg);
562 END IF;
563
564 /* l_string_len := LENGTH(l_fnd_mo_org_stg);
565 l_equal_pos := instr(l_fnd_mo_org_stg,'=');
566 -- l_org_len := l_string_len - 19;
567 l_get_org_id := substr(l_fnd_mo_org_stg,l_equal_pos,l_string_len);
568 IF (g_level_procedure >= g_current_runtime_level ) THEN
569 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
570 'l_fnd_mo_org_stg = '||l_get_org_id||' Pos '||to_char(l_equal_pos)||' Len '||to_char(l_string_len));
571 END IF; */
572 l_internal_org_stg:= replace (l_fnd_mo_org_stg,'ORG_ID','INTERNAL_ORGANIZATION_ID');
573
574 IF (g_level_procedure >= g_current_runtime_level ) THEN
575 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
576 'l_internal_org_stg = '||l_internal_org_stg);
577 END IF;
578
579 --L_WHERE_REPORT_CONTEXT := 'AND ZX_DET.INTERNAL_ORGANIZATION_ID '||l_get_org_id;
580
581 IF G_REPORTING_LEVEL IN ('1000','3000') THEN
582 L_WHERE_REPORT_CONTEXT := l_internal_org_stg;
583 ELSE
584 L_WHERE_REPORT_CONTEXT := 'AND NULL IS NULL';
585 END IF;
586
587 IF (g_level_procedure >= g_current_runtime_level ) THEN
588 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
589 'L_WHERE_REPORT_CONTEXT = '||L_WHERE_REPORT_CONTEXT);
590 END IF;
591
592 -- New Parameter code
593 /* Commented Bug 5347188 :
594 IF g_trx_date_low IS NULL AND
595 g_trx_date_high IS NULL
596 THEN
597 L_WHERE_TRX_DATE := ' AND decode(:g_trx_date_low,NULL,NULL) IS NULL AND DECODE(:g_trx_date_high,NULL,NULL) IS NULL ';
598 ELSE
599 L_WHERE_TRX_DATE := ' AND ZX_DET.TRX_DATE BETWEEN :g_trx_date_low and :g_trx_date_high ' ;
600 END IF;*/
601
602 --Bug 5347188 : To take care of all 4 conditions as in case of trx_date
603 IF g_trx_date_low IS NOT NULL AND g_trx_date_high IS NOT NULL THEN
604 L_WHERE_TRX_DATE := ' (ZX_DET.TRX_DATE BETWEEN :g_trx_date_low AND :g_trx_date_high) AND (zx_line.TRX_DATE BETWEEN :g_trx_date_low_ln AND :g_trx_date_high_ln) ';
605 ELSIF g_trx_date_low IS NULL AND g_trx_date_high IS NULL THEN
609 ELSE
606 L_WHERE_TRX_DATE := ' (DECODE(:g_trx_date_low, NULL,NULL) IS NULL AND DECODE(:g_trx_date_high, NULL,NULL) IS NULL) AND (DECODE(:g_trx_date_low_ln, NULL,NULL) IS NULL AND DECODE(:g_trx_date_high_ln, NULL,NULL) IS NULL) ';
607 ELSIF g_trx_date_low IS NOT NULL AND g_trx_date_high IS NULL THEN
608 L_WHERE_TRX_DATE := ' (ZX_DET.TRX_DATE >= :g_trx_date_low AND DECODE(:g_trx_date_high, NULL,NULL) IS NULL) AND (ZX_LINE.TRX_DATE >= :g_trx_date_low_ln AND DECODE(:g_trx_date_high_ln, NULL,NULL) IS NULL) ';
610 L_WHERE_TRX_DATE := ' (DECODE(:g_trx_date_low, NULL,NULL) IS NULL AND ZX_DET.TRX_DATE <= :g_trx_date_high) AND (DECODE(:g_trx_date_low_ln, NULL,NULL) IS NULL AND ZX_LINE.TRX_DATE <= :g_trx_date_high_ln) ';
611 END IF;
612
613 /* +===================================================================================================+
614 -- The following where clauses will be built to select appropriate register type
615 -- Tax Register would show all the invoices that have partially recoverable Taxes and
616 -- Fully Recoverable Taxes.
617
618 -- If P_AP_INCLUDE_FULLY_NR_TAX_FLAG = 'Y' then the Tax Register would also include invoices which
619 -- have Fully Non-Recoverable Taxes.
620
621 -- Non-Recoverable Tax Register shows all the invoices that have partially recoverable Taxes and
622 -- Fully Non-Recoverable Taxes.
623 +===================================================================================================+
624 */
625 IF g_register_type = 'TAX' THEN
626 IF G_INCLUDE_FULLY_NR_TAX_FLAG = 'Y' THEN
627 L_WHERE_REGISTER_TYPE := ' ';
628 ELSE
629 L_WHERE_REGISTER_TYPE :=' AND EXISTS (SELECT ''Fully Recoverable''' ||
630 ' FROM zx_rec_nrec_dist zx_dist1 WHERE ' ||
631 ' zx_dist1.trx_id = zx_dist.trx_id ' ||
632 ' AND zx_dist1.rec_nrec_tax_dist_id = zx_dist.rec_nrec_tax_dist_id ' ||
633 ' AND zx_dist1.recoverable_flag = ''Y'' '||
634 ' AND zx_dist1.tax_rate_id = zx_dist.tax_rate_id )';
635
636
637 END IF;
638 END IF;
639
640 IF g_register_type = 'NON-RECOVERABLE' THEN
641 L_WHERE_REGISTER_TYPE :=' AND EXISTS (SELECT ''Fully Recoverable''' ||
642 ' FROM zx_rec_nrec_dist zx_dist1 WHERE ' ||
643 ' zx_dist1.trx_id = zx_dist.trx_id ' ||
644 ' AND zx_dist1.rec_nrec_tax_dist_id = zx_dist.rec_nrec_tax_dist_id ' ||
645 ' AND zx_dist1.recoverable_flag = ''N'' '||
646 ' AND zx_dist1.tax_rate_id = zx_dist.tax_rate_id )';
647 END IF;
648
649 IF g_register_type = 'ALL' THEN
650 L_WHERE_REGISTER_TYPE := ' ';
651 END IF;
652
653 /* Commented Bug 5347188 :
654 IF g_gl_date_low IS NOT NULL AND g_gl_date_high IS NOT NULL THEN
655 L_WHERE_GL_DATE := ' AND ZX_DIST.GL_DATE BETWEEN :g_gl_date_low AND :g_gl_date_high ';
656
657 ELSE
658 L_WHERE_GL_DATE := ' AND decode(:g_gl_date_low,NULL,NULL) IS NULL AND DECODE(:g_gl_date_high,NULL,NULL) IS NULL ';
659 END IF; */
660
661 --Bug 5347188 : To take care of all 4 conditions as in case of gl_date
662 IF g_gl_date_low IS NOT NULL AND g_gl_date_high IS NOT NULL THEN
663 L_WHERE_GL_DATE := ' ZX_DIST.GL_DATE BETWEEN :g_gl_date_low AND :g_gl_date_high ';
664 ELSIF g_gl_date_low IS NULL AND g_gl_date_high IS NULL THEN
665 L_WHERE_GL_DATE := ' DECODE(:g_gl_date_low, NULL,NULL) IS NULL AND DECODE(:g_gl_date_high, NULL,NULL) IS NULL ';
666 ELSIF g_gl_date_low IS NOT NULL AND g_gl_date_high IS NULL THEN
667 L_WHERE_GL_DATE := ' ZX_DIST.GL_DATE >= :g_gl_date_low AND DECODE(:g_gl_date_high, NULL,NULL) IS NULL ';
668 ELSE
669 L_WHERE_GL_DATE := ' DECODE(:g_gl_date_low, NULL,NULL) IS NULL AND ZX_DIST.GL_DATE <= :g_gl_date_high ';
670 END IF;
671
672 -- IF g_document_sub_type IS NOT NULL THEN
673 -- L_WHERE_DOCUMENT_SUB_TYPE := ' AND ZX_DET.DOCUMENT_SUB_TYPE = :g_document_sub_type ';
674 -- ELSE
675 -- L_WHERE_DOCUMENT_SUB_TYPE := ' AND DECODE(:g_document_sub_type,NULL,NULL) IS NULL ';
676 -- END IF;
677
678 -- Adjusted document predicate added for Taiwan
679
680 IF g_adjusted_doc_from IS NOT NULL AND g_adjusted_doc_to IS NOT NULL THEN
681 L_WHERE_ADJUSTED_DOC_NUM := ' AND ZX_LINE.ADJUSTED_DOC_NUMBER BETWEEN :g_adjusted_doc_from AND :g_adjusted_doc_to ';
682 ELSE
683 L_WHERE_ADJUSTED_DOC_NUM := ' AND DECODE(:g_adjusted_doc_from,NULL,NULL) IS NULL '||
684 ' AND DECODE(:g_adjusted_doc_to,NULL,NULL) IS NULL ';
685 END IF;
686
687 IF g_trx_business_category IS NOT NULL THEN
688 L_WHERE_TRX_BUSINESS_CATEGORY := ' AND ZX_DET.TRX_BUSINESS_CATEGORY = :G_TRX_BUSINESS_CATEGORY ';
689 ELSE
690 L_WHERE_TRX_BUSINESS_CATEGORY := ' AND DECODE(:G_TRX_BUSINESS_CATEGORY,NULL,NULL) IS NULL ';
691 END IF;
692
693 /*Bug Fix 5119565 */
694 IF g_tax_invoice_date_low IS NOT NULL AND g_tax_invoice_date_high IS NOT NULL THEN
695 L_WHERE_TAX_INVOICE_DATE := ' AND ZX_DET.TAX_INVOICE_DATE BETWEEN :G_TAX_INVOICE_DATE_LOW AND :G_TAX_INVOICE_DATE_HIGH ';
696 ELSIF g_tax_invoice_date_low IS NULL AND g_tax_invoice_date_high IS NULL THEN
697 L_WHERE_TAX_INVOICE_DATE := ' AND DECODE(:G_TAX_INVOICE_DATE_LOW, NULL,NULL) IS NULL AND DECODE(:G_TAX_INVOICE_DATE_HIGH, NULL,NULL) IS NULL ';
701 L_WHERE_TAX_INVOICE_DATE := ' AND DECODE(:G_TAX_INVOICE_DATE_LOW, NULL,NULL) IS NULL AND ZX_DET.TAX_INVOICE_DATE <= :G_TAX_INVOICE_DATE_HIGH ';
698 ELSIF G_TAX_INVOICE_DATE_LOW IS NOT NULL AND G_TAX_INVOICE_DATE_HIGH IS NULL THEN
699 L_WHERE_TAX_INVOICE_DATE := ' AND ZX_DET.TAX_INVOICE_DATE >= :G_TAX_INVOICE_DATE_LOW AND DECODE(:G_TAX_INVOICE_DATE_HIGH, NULL,NULL) IS NULL ';
700 ELSE
702 END IF;
703
704 --Bug 5347188 : Create and OR predicate for gl_date and trx_date
705 IF ( g_gl_or_trx_date_filter = 'Y' ) THEN
706 L_WHERE_GL_TRX_DATE := ' AND ( ( '||L_WHERE_TRX_DATE||' ) OR ('||L_WHERE_GL_DATE||')) ';
707 L_WHERE_TRX_DATE := ' ' ;
708 L_WHERE_GL_DATE := ' ' ;
709 ELSE
710 L_WHERE_TRX_DATE := ' AND '||L_WHERE_TRX_DATE;
711 L_WHERE_GL_DATE := ' AND '||L_WHERE_GL_DATE;
712 L_WHERE_GL_TRX_DATE := ' ';
713 END IF ;
714
715
716 IF g_first_party_tax_reg_num IS NOT NULL THEN
717 L_WHERE_FIRST_PTY_TAX_REG_NUM := ' AND zx_line.hq_estb_reg_number = :g_first_party_tax_reg_num ';
718 ELSE
719 L_WHERE_FIRST_PTY_TAX_REG_NUM := ' AND DECODE(:g_first_party_tax_reg_num,NULL,NULL) IS NULL ';
720 END IF;
721
722 IF g_tax_jurisdiction_code IS NOT NULL THEN
723 L_WHERE_TAX_JURISDICTION_CODE := ' AND ZX_LINE.TAX_JURISDICTION_CODE = :g_tax_jurisdiction_code ';
724 ELSE
725 L_WHERE_TAX_JURISDICTION_CODE := ' AND DECODE(:g_tax_jurisdiction_code,NULL,NULL) IS NULL ';
726 END IF;
727
728 IF g_tax_regime_code IS NOT NULL THEN
729 L_WHERE_TAX_REGIME_CODE := ' AND ZX_LINE.TAX_REGIME_CODE = :G_TAX_REGIME_CODE ';
730 ELSE
731 L_WHERE_TAX_REGIME_CODE := ' AND DECODE(:G_TAX_REGIME_CODE,NULL,NULL) IS NULL ';
732 END IF;
733
734 IF g_tax IS NOT NULL THEN
735 L_WHERE_TAX := ' AND ZX_LINE.TAX = :G_TAX ';
736 ELSE
737 L_WHERE_TAX := ' AND DECODE(:G_TAX,NULL,NULL) IS NULL ';
738 END IF;
739
740 IF g_tax_status_code IS NOT NULL THEN
741 L_WHERE_TAX_STATUS_CODE := ' AND ZX_LINE.TAX_STATUS_CODE = :G_TAX_STATUS_CODE ';
742 ELSE
743 L_WHERE_TAX_STATUS_CODE := ' AND DECODE(:G_TAX_STATUS_CODE,NULL,NULL) IS NULL ';
744 END IF;
745
746 IF g_tax_rate_code_low IS NOT NULL AND g_tax_rate_code_high IS NOT NULL THEN
747 L_WHERE_TAX_RATE_CODE := ' AND ZX_LINE.TAX_RATE_CODE BETWEEN :G_TAX_RATE_CODE_LOW AND :G_TAX_RATE_CODE_HIGH ';
748 ELSE
749 L_WHERE_TAX_RATE_CODE := ' AND DECODE(:G_TAX_RATE_CODE_LOW,NULL,NULL) IS NULL '||
750 ' AND DECODE(:G_TAX_RATE_CODE_HIGH,NULL,NULL) IS NULL ';
751 END IF;
752
753 IF g_tax_type_code_low IS NOT NULL AND g_tax_type_code_high IS NOT NULL THEN
754 L_WHERE_TAX_TYPE_CODE := ' AND ZX_TAX.TAX_TYPE_CODE BETWEEN :G_TAX_TYPE_CODE_LOW AND :G_TAX_TYPE_CODE_HIGH ';
755 ELSE
756 L_WHERE_TAX_TYPE_CODE := ' AND DECODE(:G_TAX_TYPE_CODE_LOW,NULL,NULL) IS NULL '||
757 ' AND DECODE(:G_TAX_TYPE_CODE_HIGH,NULL,NULL) IS NULL ';
758 END IF;
759
760 IF g_currency_code_low IS NOT NULL AND g_currency_code_high IS NOT NULL THEN
761 L_WHERE_CURRENCY_CODE := ' AND ZX_DET.TRX_CURRENCY_CODE BETWEEN :G_CURRENCY_CODE_LOW AND :G_CURRENCY_CODE_HIGH ';
762 ELSE
763 L_WHERE_CURRENCY_CODE := ' AND DECODE(:G_CURRENCY_CODE_LOW,NULL,NULL) IS NULL '||
764 ' AND DECODE(:G_CURRENCY_CODE_HIGH,NULL,NULL) IS NULL ';
765 END IF;
766
767 /*
768 IF G_POSTING_STATUS = 'POSTED' THEN
769 L_WHERE_POSTING_STATUS_DIST_T := ' AND (DIST_T.POSTED_FLAG = ''Y'') ';
770 L_WHERE_POSTING_STATUS_DIST_I := ' AND (DIST_I.POSTED_FLAG = ''Y'') ';
771 ELSIF G_POSTING_STATUS = 'UNPOSTED' THEN
772 L_WHERE_POSTING_STATUS_DIST_T := ' AND (DIST_T.POSTED_FLAG = ''N'') ';
773 L_WHERE_POSTING_STATUS_DIST_I := ' AND (DIST_I.POSTED_FLAG = ''N'') ';
774 ELSIF G_POSTING_STATUS = 'ALL' THEN
775 L_WHERE_POSTING_STATUS_DIST_T := ' AND DIST_T.POSTED_FLAG in (''Y'',''N'') ';
776 L_WHERE_POSTING_STATUS_DIST_I := ' AND DIST_I.POSTED_FLAG in ( ''Y'',''N'') ';
777 END IF;
778 */
779 IF g_trx_number_low IS NOT NULL AND g_trx_number_high IS NOT NULL THEN
780 L_WHERE_TRX_NUM := ' AND ZX_DET.TRX_NUMBER BETWEEN :G_TRX_NUMBER_LOW AND :G_TRX_NUMBER_HIGH ';
781 ELSE
782 L_WHERE_TRX_NUM := ' AND DECODE(:G_TRX_NUMBER_LOW,NULL,NULL) IS NULL AND DECODE(:G_TRX_NUMBER_HIGH,NULL,NULL) IS NULL ';
783 END IF;
784
785 IF ( g_include_ap_std_trx_class = 'Y' OR g_include_ap_dm_trx_class = 'Y' OR
786 g_include_ap_cm_trx_class = 'Y' OR g_include_ap_prep_trx_class = 'Y' OR
787 g_include_ap_mix_trx_class = 'Y' OR g_include_ap_exp_trx_class = 'Y' )
788
789 THEN
790
791 L_WHERE_TRX_CLASS := '';
792
793 IF g_include_ap_std_trx_class = 'Y' THEN
794 L_WHERE_TRX_CLASS := L_WHERE_TRX_CLASS || '''STANDARD INVOICES'' ,''PREPAY_APPLICATION'' ,' ;
795 END IF;
796
797 IF g_include_ap_dm_trx_class = 'Y' THEN
798 L_WHERE_TRX_CLASS := L_WHERE_TRX_CLASS || '''AP_DEBIT_MEMO'' ,';
799 END IF;
800
801 IF g_include_ap_cm_trx_class = 'Y' THEN
802 L_WHERE_TRX_CLASS := L_WHERE_TRX_CLASS || '''AP_CREDIT_MEMO'' ,';
803 END IF;
804
805 IF g_include_ap_prep_trx_class = 'Y' THEN
806 L_WHERE_TRX_CLASS := L_WHERE_TRX_CLASS || '''PREPAYMENT INVOICES'' ,';
807 END IF;
808
812
809 IF g_include_ap_mix_trx_class = 'Y' THEN
810 L_WHERE_TRX_CLASS := L_WHERE_TRX_CLASS || '''MIXED'' ,';
811 END IF;
813 IF g_include_ap_exp_trx_class = 'Y' THEN
814 L_WHERE_TRX_CLASS := L_WHERE_TRX_CLASS || '''EXPENSE REPORTS'' ,';
815 END IF;
816
817 L_WHERE_TRX_CLASS := ' AND ZX_DET.LINE_CLASS IN ( '|| RTRIM(L_WHERE_TRX_CLASS,' ,') || ')';
818
819 ELSE
820 L_WHERE_TRX_CLASS := 'AND 1 = 0 ';
821
822 END IF;
823
824 IF g_batch_date_low IS NOT NULL AND g_batch_date_high IS NOT NULL THEN
825 L_WHERE_BATCH_DATE := ' AND BAT.BATCH_DATE BETWEEN :G_BATCH_DATE_LOW AND :G_BATCH_DATE_HIGH ';
826 ELSE
827 L_WHERE_BATCH_DATE := ' AND DECODE(:G_BATCH_DATE_LOW,NULL,NULL) IS NULL AND DECODE(:G_BATCH_DATE_HIGH,NULL,NULL) IS NULL';
828 END IF;
829
830 IF g_batch_name IS NOT NULL THEN
831 L_WHERE_BATCH_NAME := ' AND BAT.BATCH_NAME = :G_BATCH_NAME';
832 ELSE
833 L_WHERE_BATCH_NAME := ' AND DECODE(:G_BATCH_NAME,NULL,NULL) IS NULL ';
834 END IF;
835
836 IF g_party_name IS NOT NULL THEN
837 L_WHERE_PARTY_NAME := ' AND NVL(ZX_DET.SHIP_THIRD_PTY_ACCT_ID, ZX_DET.BILL_THIRD_PTY_ACCT_ID) = to_number(:G_PARTY_NAME)';
838 ELSE
839 L_WHERE_PARTY_NAME := ' AND DECODE(:G_PARTY_NAME,NULL,NULL) IS NULL ';
840 END IF;
841
842
843 IF g_vat_transaction_type_code IS NOT NULL THEN
844 L_WHERE_VAT_TRANSACTION_TYPE := ' AND ZX_RATE.VAT_TRANSACTION_TYPE_CODE = :G_VAT_TRANSACTION_TYPE_CODE ';
845 ELSE
846 L_WHERE_VAT_TRANSACTION_TYPE := ' AND DECODE(:G_VAT_TRANSACTION_TYPE_CODE,NULL,NULL) IS NULL ';
847 END IF;
848
849
850 IF g_reporting_level = '2000' THEN
851 L_WHERE_LEGAL_ENTITY_ID := ' AND ZX_LINE.LEGAL_ENTITY_ID = :G_LEGAL_ENTITY_ID ';
852 ELSE
853 L_WHERE_LEGAL_ENTITY_ID := ' AND DECODE(:G_LEGAL_ENTITY_ID,NULL,NULL) IS NULL ';
854 END IF;
855
856 IF g_ledger_id IS NOT NULL THEN
857 L_WHERE_LEDGER_ID := ' AND ZX_LINE.LEDGER_ID = :G_LEDGER_ID ';
858 ELSE
859 L_WHERE_LEDGER_ID := ' AND DECODE(:G_LEDGER_ID,NULL,NULL) IS NULL ';
860 END IF;
861
862
863 L_WHERE_ACCOUNTING_STATUS := '';
864 IF g_accounting_status = 'ACCOUNTED' THEN
865 L_WHERE_ACCOUNTING_STATUS := ' AND zx_dist.POSTING_FLAG = ''A''';
866 ELSIF g_accounting_status = 'UNACCOUNTED' then
867 L_WHERE_ACCOUNTING_STATUS := ' AND nvl(zx_dist.POSTING_FLAG,''N'') = ''N''';
868 ELSIF g_accounting_status = 'BOTH' OR g_accounting_status IS NULL THEN
869 /* IF G_INCLUDE_ACCOUNTING_SEGMENTS = 'Y' THEN
870 l_balancing_segment := fa_rx_flex_pkg.flex_sql(
871 p_application_id =>101,
872 p_id_flex_code => 'GL#',
873 p_id_flex_num => g_chart_of_accounts_id,
874 p_table_alias => '',
875 p_mode => 'SELECT',
876 p_qualifier => 'GL_BALANCING');
877 l_accounting_segment := fa_rx_flex_pkg.flex_sql(
878 p_application_id =>101,
879 p_id_flex_code => 'GL#',
880 p_id_flex_num => g_chart_of_accounts_id,
881 p_table_alias => '',
882 p_mode => 'SELECT',
883 p_qualifier => 'GL_ACCOUNT');
884 END IF;
885 */
886 L_WHERE_ACCOUNTING_STATUS := ' AND NULL IS NULL ';
887 END IF;
888
889
890 IF G_REPORTED_STATUS IS NOT NULL THEN
891 -- L_WHERE_REPORTED_STATUS := ' AND ZX_LINES.LEGAL_REPORTING_STATUS = ''111111111111111''' ;
892 --ELSIF G_REPORTED_STATUS = 'N' THEN
893 L_WHERE_REPORTED_STATUS := ' AND ZX_LINE.LEGAL_REPORTING_STATUS = ''000000000000000''';
894 ELSE
895 L_WHERE_REPORTED_STATUS := '';
896 END IF;
897
898
899 /*
900 IF G_TRADING_PARTNER_ID IS NOT NULL THEN
901 L_WHERE_TRADING_PARTNER_ID := ' AND TRX_H.VENDOR_ID = :G_TRADING_PARTNER_ID ';
902 ELSE
903 L_WHERE_TRADING_PARTNER_ID := ' AND DECODE(:G_TRADING_PARTNER_ID,NULL,NULL) IS NULL ';
904 END IF;
905 */
906
907 IF g_summary_level = 'TRANSACTION' THEN
908
909 IF (g_level_procedure >= g_current_runtime_level ) THEN
910 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
911 'SQL-1 For Invoice, Credit Memo, Debit Memo: Header Level');
912 END IF;
913
914 l_sql_statement :=
915 'SELECT
916 zx_det.application_id,
917 zx_line.event_class_code ,
918 zx_det.internal_organization_id,
919 zx_det.doc_event_status,
920 zx_det.application_doc_status,
921 zx_det.line_class,
922 zx_det.doc_seq_id ,
923 zx_det.doc_seq_name ,
924 zx_det.doc_seq_value,
925 zx_det.establishment_id,
926 zx_det.batch_source_id,
927 zx_det.currency_conversion_date,
928 zx_det.currency_conversion_rate,
929 zx_det.currency_conversion_type,
930 zx_det.minimum_accountable_unit,
931 zx_det.precision,
932 zx_det.trx_communicated_date ,
933 zx_det.trx_currency_code,
934 zx_line.trx_id ,
935 zx_det.trx_number ,
936 zx_det.trx_date,
937 zx_det.trx_description,
938 zx_det.trx_type_description,
942 to_char(null),--zx_line.trx_line_id,
939 zx_rate.description,
940 zx_det.trx_due_date,
941 to_char(null),--zx_det.trx_line_description,
943 to_char(null),--zx_line.trx_line_number,
944 to_char(null),--zx_line.trx_line_quantity,
945 sum(zx_line.line_amt),
946 to_char(NULL), --zx_det.trx_line_type,
947 zx_det.trx_shipping_date,
948 zx_det.uom_code,
949 zx_det.related_doc_date,
950 zx_det.related_doc_entity_code,
951 zx_det.related_doc_event_class_code,
952 zx_det.related_doc_number,
953 zx_det.related_doc_trx_id,
954 zx_det.applied_from_application_id,
955 zx_line.applied_from_entity_code,
956 zx_line.applied_from_event_class_code,
957 zx_det.applied_from_line_id,
958 zx_line.applied_from_trx_id,
959 zx_line.applied_from_trx_number,
960 zx_det.applied_to_application_id,
961 zx_line.applied_to_entity_code,
962 zx_line.applied_to_event_class_code,
963 zx_line.applied_to_trx_id,
964 zx_det.applied_to_trx_line_id,
965 zx_det.applied_to_trx_number,
966 zx_det.adjusted_doc_application_id,
967 zx_det.adjusted_doc_date,
968 zx_det.adjusted_doc_entity_code,
969 zx_det.adjusted_doc_event_class_code,
970 ZX_DET.ADJUSTED_DOC_NUMBER,
971 --zx_det.country_of_supply,
972 zx_det.default_taxation_country,
973 TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_DOCUMENT_NUMBER,
974 TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_NAME,
975 TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_REFERENCE,
976 TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_TAX_REG_NUMBER,
977 TO_CHAR(NULL), --ZX_DET.MERCHANT_PARTY_TAXPAYER_ID,
978 zx_det.ref_doc_application_id,
979 zx_det.ref_doc_entity_code,
980 zx_det.ref_doc_event_class_code,
981 zx_det.ref_doc_line_id,
982 zx_det.ref_doc_line_quantity,
983 zx_det.ref_doc_trx_id,
984 zx_det.start_expense_date,
985 zx_det.assessable_value,
986 zx_det.document_sub_type,
987 zx_det.line_intended_use,
988 zx_det.product_category,
989 zx_det.product_description,
990 zx_det.product_fisc_classification,
991 zx_det.product_id,
992 zx_det.supplier_exchange_rate,
993 zx_det.supplier_tax_invoice_date,
994 zx_det.supplier_tax_invoice_number,
995 zx_det.tax_invoice_date,
996 zx_det.tax_invoice_number,
997 zx_det.trx_business_category,
998 zx_det.user_defined_fisc_class,
999 sum(zx_dist.rec_nrec_tax_amt_tax_curr),
1000 zx_line.OFFSET_TAX_RATE_CODE,
1001 sum(zx_dist.orig_rec_nrec_tax_amt),
1002 sum(zx_line.orig_tax_amt),
1003 sum(zx_line.orig_tax_amt_tax_curr) ,
1004 sum(zx_line.orig_taxable_amt),
1005 sum(zx_line.orig_taxable_amt_tax_curr),
1006 sum(zx_dist.orig_rec_nrec_tax_amt_tax_curr),
1007 TO_CHAR(NULL), --ZX_DIST.RECOVERY_RATE_CODE,
1008 TO_CHAR(NULL), --ZX_DIST.RECOVERY_TYPE_CODE,
1009 zx_line.tax,
1010 sum(zx_dist.rec_nrec_tax_amt),
1011 sum(zx_dist.rec_nrec_tax_amt_funcl_curr),
1012 sum(zx_line.tax_amt_tax_curr),
1013 zx_line.tax_apportionment_line_number,
1014 zx_line.tax_currency_code,
1015 zx_line.tax_date,
1016 zx_line.tax_determine_date,
1017 zx_line.tax_jurisdiction_code,
1018 TO_NUMBER(NULL), --ZX_LINE.TAX_LINE_ID ,
1019 TO_NUMBER(NULL), --ZX_LINE.TAX_LINE_NUMBER ,
1020 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE1 ,
1021 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE10,
1022 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE11,
1023 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE12,
1024 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE13,
1025 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE14,
1026 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE15,
1027 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE2,
1028 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE3,
1029 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE4,
1030 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE5,
1031 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE6,
1032 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE7,
1033 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE8,
1034 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE9,
1035 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_CATEGORY,
1036 zx_rate.percentage_rate,
1037 zx_line.tax_rate_code,
1038 zx_line.tax_rate_id,
1039 TO_NUMBER(NULL), --ZX_DIST.REC_NREC_RATE,
1040 zx_line.tax_regime_code,
1041 zx_line.tax_status_id,
1042 zx_line.tax_status_code,
1043 sum(zx_dist.taxable_amt),
1044 sum(zx_dist.taxable_amt_funcl_curr),
1045 -- zx_det.billing_trading_partner_name,
1046 -- zx_det.billing_trading_partner_number,
1047 zx_det.bill_from_party_tax_prof_id,
1048 zx_det.bill_from_site_tax_prof_id,
1049 -- zx_det.billing_tp_taxpayer_id,
1050 zx_det.ship_to_site_tax_prof_id,
1051 zx_det.ship_from_site_tax_prof_id,
1052 zx_det.ship_to_party_tax_prof_id,
1053 zx_det.ship_from_party_tax_prof_id,
1054 ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
1055 ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
1056 ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
1060 zx_line.hq_estb_reg_number,
1057 ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
1058 ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
1059 ZX_DET.BILL_THIRD_PTY_ACCT_ID,
1061 zx_line.tax_registration_number,
1062 zx_line.legal_entity_tax_reg_number,
1063 zx_det.own_hq_site_tax_prof_id,
1064 zx_det.own_hq_party_tax_prof_id,
1065 zx_det.port_of_entry_code,
1066 zx_line.registration_party_type,
1067 zx_line.cancel_flag,
1068 zx_line.historical_flag,
1069 zx_line.mrc_tax_line_flag,
1070 zx_line.offset_flag,
1071 zx_line.reporting_only_flag,
1072 zx_dist.self_assessed_flag,
1073 zx_line.tax_amt_included_flag,
1074 zx_line.tax_only_line_flag,
1075 zx_dist.recoverable_flag,
1076 zx_dist.posting_flag,
1077 zx_dist.reverse_flag,
1078 zx_det.trx_id,
1079 to_number(NULL),
1080 zx_det.entity_code ,
1081 zx_det.ledger_id,
1082 ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
1083 zx_tax.tax_type_code,
1084 ZX_RATE.TAX_RATE_NAME,
1085 zx_det.trx_level_type, -- Bug 5393051
1086 zx_det.unit_price , -- Bug 5439099
1087 zx_det.trx_line_gl_date --Bug 5523095
1088 FROM zx_lines zx_line,
1089 zx_lines_det_factors zx_det,
1090 zx_rec_nrec_dist zx_dist,
1091 zx_taxes_vl zx_tax,
1092 zx_rates_vl zx_rate
1093 WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
1094 AND zx_det.application_id = zx_line.application_id
1095 AND zx_det.application_id = 200
1096 AND zx_det.entity_code = zx_line.entity_code
1097 AND zx_det.event_class_code = zx_line.event_class_code
1098 AND zx_det.trx_id = zx_line.trx_id
1099 AND zx_det.trx_line_id = zx_line.trx_line_id
1100 -- AND zx_det.application_id = zx_dist.application_id
1101 -- AND zx_det.entity_code = zx_dist.entity_code
1102 -- AND zx_det.event_class_code = zx_dist.event_class_code
1103 -- AND zx_det.event_type_code = zx_dist.event_type_code
1104 -- AND zx_det.trx_id = zx_dist.trx_id
1105 AND zx_line.tax_line_id = zx_dist.tax_line_id
1106 AND zx_det.tax_reporting_flag = ''Y''
1107 AND zx_line.tax_id = zx_tax.tax_id
1108 AND zx_line.tax_rate_id = zx_rate.tax_rate_id '
1109 ||L_WHERE_GL_TRX_DATE||' '
1110 ||L_WHERE_TRX_DATE|| ' '
1111 ||L_WHERE_REGISTER_TYPE|| ' '
1112 ||L_WHERE_GL_DATE|| ' '
1113 ||L_WHERE_TRX_NUM|| ' '
1114 ||L_WHERE_VAT_TRANSACTION_TYPE|| ' '
1115 ||L_WHERE_TRX_BUSINESS_CATEGORY|| ' '
1116 ||L_WHERE_TAX_INVOICE_DATE|| ' '
1117 ||L_WHERE_TAX_JURISDICTION_CODE|| ' '
1118 ||L_WHERE_FIRST_PTY_TAX_REG_NUM|| ' '
1119 ||L_WHERE_TAX_REGIME_CODE|| ' '
1120 ||L_WHERE_TAX|| ' '
1121 ||L_WHERE_TAX_STATUS_CODE|| ' '
1122 ||L_WHERE_TAX_RATE_CODE|| ' '
1123 ||L_WHERE_TAX_TYPE_CODE|| ' '
1124 ||L_WHERE_CURRENCY_CODE|| ' '
1125 ||L_WHERE_PARTY_NAME|| ' '
1126 ||L_WHERE_TRX_CLASS|| ' '
1127 ||L_WHERE_LEGAL_ENTITY_ID|| ' '
1128 ||L_WHERE_LEDGER_ID|| ' '
1129 ||L_WHERE_REPORT_CONTEXT||' '
1130 ||L_WHERE_ACCOUNTING_STATUS||' '
1131 ||L_WHERE_REPORTED_STATUS||' '
1132 ||L_WHERE_ADJUSTED_DOC_NUM||' '
1133 || 'GROUP BY
1134 zx_det.application_id,
1135 zx_line.event_class_code ,
1136 zx_det.internal_organization_id,
1137 zx_det.doc_event_status,
1138 zx_det.application_doc_status,
1139 zx_det.line_class,
1140 zx_det.doc_seq_id,
1141 zx_det.doc_seq_name ,
1142 zx_det.doc_seq_value,
1143 zx_det.establishment_id,
1144 zx_det.batch_source_id,
1145 zx_det.currency_conversion_date,
1146 zx_det.currency_conversion_rate,
1147 zx_det.currency_conversion_type,
1148 zx_det.minimum_accountable_unit,
1149 zx_det.precision,
1150 zx_det.trx_communicated_date,
1151 zx_det.trx_currency_code,
1152 zx_line.trx_id,
1153 zx_det.trx_number,
1154 zx_det.trx_date,
1155 zx_det.trx_description,
1156 zx_det.trx_type_description,
1157 zx_rate.description,
1158 zx_det.trx_due_date,
1159 to_char(null),--zx_det.trx_line_description,
1160 to_char(null),--zx_line.trx_line_id,
1161 to_char(null),--zx_line.trx_line_number,
1162 to_char(null),--zx_line.trx_line_quantity,
1163 --zx_line.line_amt,
1164 --zx_det.trx_line_type,
1165 zx_det.trx_shipping_date,
1166 zx_det.uom_code,
1167 zx_det.related_doc_date,
1168 zx_det.related_doc_entity_code,
1169 zx_det.related_doc_event_class_code,
1170 zx_det.related_doc_number,
1171 zx_det.related_doc_trx_id,
1172 zx_det.applied_from_application_id,
1173 zx_line.applied_from_entity_code,
1174 zx_line.applied_from_event_class_code,
1175 zx_det.applied_from_line_id,
1176 zx_line.applied_from_trx_id,
1177 zx_line.applied_from_trx_number,
1178 zx_det.applied_to_application_id,
1179 zx_line.applied_to_entity_code,
1180 zx_line.applied_to_event_class_code,
1181 zx_line.applied_to_trx_id,
1182 zx_det.applied_to_trx_line_id,
1183 zx_det.applied_to_trx_number,
1184 zx_det.adjusted_doc_application_id,
1185 zx_det.adjusted_doc_date,
1186 zx_det.adjusted_doc_entity_code,
1187 zx_det.adjusted_doc_event_class_code,
1191 zx_det.ref_doc_application_id,
1188 ZX_DET.ADJUSTED_DOC_NUMBER,
1189 --zx_det.country_of_supply,
1190 zx_det.default_taxation_country,
1192 zx_det.ref_doc_entity_code,
1193 zx_det.ref_doc_event_class_code,
1194 zx_det.ref_doc_line_id,
1195 zx_det.ref_doc_line_quantity,
1196 zx_det.ref_doc_trx_id,
1197 zx_det.start_expense_date,
1198 zx_det.assessable_value,
1199 zx_det.document_sub_type,
1200 zx_det.line_intended_use,
1201 zx_det.product_category,
1202 zx_det.product_description,
1203 zx_det.product_fisc_classification,
1204 zx_det.product_id,
1205 zx_det.supplier_exchange_rate,
1206 zx_det.supplier_tax_invoice_date,
1207 zx_det.supplier_tax_invoice_number,
1208 zx_det.tax_invoice_date,
1209 zx_det.tax_invoice_number,
1210 zx_det.trx_business_category,
1211 zx_det.user_defined_fisc_class,
1212 --zx_dist.rec_nrec_tax_amt_tax_curr,
1213 zx_line.OFFSET_TAX_RATE_CODE,
1214 --zx_dist.orig_rec_nrec_tax_amt,
1215 --zx_line.orig_tax_amt,
1216 --zx_line.orig_tax_amt_tax_curr,
1217 --zx_line.orig_taxable_amt,
1218 --zx_line.orig_taxable_amt_tax_curr,
1219 --zx_dist.orig_rec_nrec_tax_amt_tax_curr,
1220 zx_line.tax,
1221 --zx_dist.rec_nrec_tax_amt,
1222 --zx_dist.rec_nrec_tax_amt_funcl_curr,
1223 --zx_line.tax_amt_tax_curr,
1224 zx_line.tax_apportionment_line_number,
1225 zx_line.tax_currency_code,
1226 zx_line.tax_date,
1227 zx_line.tax_determine_date,
1228 zx_line.tax_jurisdiction_code,
1229 zx_rate.percentage_rate,
1230 zx_line.tax_rate_code,
1231 zx_line.tax_rate_id,
1232 to_number(null), --zx_dist.rec_nrec_rate,
1233 zx_line.tax_regime_code,
1234 zx_line.tax_status_id,
1235 zx_line.tax_status_code,
1236 --zx_line.taxable_amt,
1237 --zx_line.taxable_amt_funcl_curr ,
1238 -- zx_det.billing_trading_partner_name,
1239 -- zx_det.billing_trading_partner_number,
1240 zx_det.bill_from_party_tax_prof_id,
1241 zx_det.bill_from_site_tax_prof_id,
1242 -- zx_det.billing_tp_taxpayer_id,
1243 zx_det.ship_to_site_tax_prof_id,
1244 zx_det.ship_from_site_tax_prof_id,
1245 zx_det.ship_to_party_tax_prof_id,
1246 zx_det.ship_from_party_tax_prof_id ,
1247 ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
1248 ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
1249 ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
1250 ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
1251 ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
1252 ZX_DET.BILL_THIRD_PTY_ACCT_ID,
1253 zx_line.hq_estb_reg_number,
1254 zx_line.tax_registration_number,
1255 zx_line.legal_entity_tax_reg_number,
1256 zx_det.own_hq_site_tax_prof_id,
1257 zx_det.own_hq_party_tax_prof_id,
1258 zx_det.port_of_entry_code,
1259 zx_line.registration_party_type,
1260 zx_line.cancel_flag,
1261 zx_line.historical_flag,
1262 zx_line.mrc_tax_line_flag,
1263 zx_line.offset_flag,
1264 zx_line.reporting_only_flag,
1265 zx_dist.self_assessed_flag,
1266 zx_line.tax_amt_included_flag,
1267 zx_line.tax_only_line_flag,
1268 zx_dist.recoverable_flag,
1269 zx_dist.posting_flag ,
1270 zx_dist.reverse_flag,
1271 zx_det.trx_id,
1272 zx_det.entity_code,
1273 zx_det.ledger_id,
1274 ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
1275 --ZX_RATE.RATE_TYPE_CODE,
1276 zx_tax.tax_type_code,
1277 ZX_RATE.TAX_RATE_NAME,
1278 zx_det.trx_level_type,
1279 zx_det.unit_price,
1280 zx_det.trx_line_gl_date';
1281
1282 g_sql_statement := l_sql_statement ;
1283
1284 ELSIF G_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1285
1286 IF (g_level_procedure >= g_current_runtime_level ) THEN
1287 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
1288 'SQL-2 For Invoice, Credit Memo, Debit Memo: Distribution Level');
1289 END IF;
1290
1291 l_sql_statement :=
1292 'SELECT
1293 zx_det.application_id,
1294 zx_line.event_class_code ,
1295 zx_det.internal_organization_id,
1296 zx_det.doc_event_status,
1297 zx_det.application_doc_status,
1298 zx_det.line_class,
1299 zx_det.doc_seq_id ,
1300 zx_det.doc_seq_name ,
1301 zx_det.doc_seq_value,
1302 zx_det.establishment_id,
1303 zx_det.batch_source_id,
1304 zx_det.currency_conversion_date,
1305 zx_det.currency_conversion_rate,
1306 zx_det.currency_conversion_type,
1307 zx_det.minimum_accountable_unit,
1308 zx_det.precision,
1309 zx_det.trx_communicated_date ,
1310 zx_det.trx_currency_code,
1311 zx_line.trx_id,
1312 zx_det.trx_number,
1313 zx_det.trx_date,
1314 zx_det.trx_description,
1315 zx_det.trx_type_description,
1316 zx_rate.description,
1317 zx_det.trx_due_date,
1318 zx_det.trx_line_description,
1319 zx_line.trx_line_id,
1320 zx_line.trx_line_number,
1324 zx_det.trx_shipping_date,
1321 zx_line.trx_line_quantity,
1322 zx_line.line_amt,
1323 zx_det.trx_line_type,
1325 zx_det.uom_code,
1326 zx_det.related_doc_date,
1327 zx_det.related_doc_entity_code,
1328 zx_det.related_doc_event_class_code,
1329 zx_det.related_doc_number,
1330 zx_det.related_doc_trx_id,
1331 zx_det.applied_from_application_id,
1332 zx_line.applied_from_entity_code,
1333 zx_line.applied_from_event_class_code,
1334 zx_det.applied_from_line_id,
1335 zx_line.applied_from_trx_id,
1336 zx_line.applied_from_trx_number,
1337 zx_det.applied_to_application_id,
1338 zx_line.applied_to_entity_code,
1339 zx_line.applied_to_event_class_code,
1340 zx_line.applied_to_trx_id,
1341 zx_det.applied_to_trx_line_id,
1342 zx_det.applied_to_trx_number,
1343 zx_det.adjusted_doc_application_id,
1344 zx_det.adjusted_doc_date,
1345 zx_det.adjusted_doc_entity_code,
1346 zx_det.adjusted_doc_event_class_code,
1347 ZX_DET.ADJUSTED_DOC_NUMBER,
1348 --zx_det.country_of_supply,
1349 zx_det.default_taxation_country,
1350 ZX_DET.MERCHANT_PARTY_DOCUMENT_NUMBER,
1351 ZX_DET.MERCHANT_PARTY_NAME,
1352 ZX_DET.MERCHANT_PARTY_REFERENCE,
1353 ZX_DET.MERCHANT_PARTY_TAX_REG_NUMBER,
1354 ZX_DET.MERCHANT_PARTY_TAXPAYER_ID,
1355 zx_det.ref_doc_application_id,
1356 zx_det.ref_doc_entity_code,
1357 zx_det.ref_doc_event_class_code,
1358 zx_det.ref_doc_line_id,
1359 zx_det.ref_doc_line_quantity,
1360 zx_det.ref_doc_trx_id,
1361 zx_det.start_expense_date,
1362 zx_det.assessable_value,
1363 zx_det.document_sub_type,
1364 zx_det.line_intended_use,
1365 zx_det.product_category,
1366 zx_det.product_description,
1367 zx_det.product_fisc_classification,
1368 zx_det.product_id,
1369 zx_det.supplier_exchange_rate,
1370 zx_det.supplier_tax_invoice_date,
1371 zx_det.supplier_tax_invoice_number,
1372 zx_det.tax_invoice_date,
1373 zx_det.tax_invoice_number,
1374 zx_det.trx_business_category,
1375 zx_det.user_defined_fisc_class,
1376 zx_dist.rec_nrec_tax_amt_tax_curr,
1377 zx_line.OFFSET_TAX_RATE_CODE,
1378 zx_dist.orig_rec_nrec_tax_amt,
1379 zx_line.orig_tax_amt,
1380 zx_line.orig_tax_amt_tax_curr ,
1381 zx_line.orig_taxable_amt,
1382 zx_line.orig_taxable_amt_tax_curr,
1383 zx_dist.orig_rec_nrec_tax_amt_tax_curr,
1384 zx_dist.recovery_rate_code,
1385 zx_dist.recovery_type_code,
1386 zx_line.tax,
1387 zx_dist.rec_nrec_tax_amt,
1388 zx_dist.rec_nrec_tax_amt_funcl_curr,
1389 zx_line.tax_amt_tax_curr,
1390 zx_line.tax_apportionment_line_number,
1391 zx_line.tax_currency_code,
1392 zx_line.tax_date,
1393 zx_line.tax_determine_date,
1394 zx_line.tax_jurisdiction_code,
1395 zx_line.tax_line_id,
1396 zx_line.tax_line_number ,
1397 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE1 ,
1398 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE10,
1399 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE11,
1400 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE12,
1401 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE13,
1402 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE14,
1403 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE15,
1404 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE2,
1405 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE3,
1406 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE4,
1407 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE5,
1408 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE6,
1409 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE7,
1410 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE8,
1411 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_ATTRIBUTE9,
1412 TO_CHAR(NULL), --ZX_LINE.TAX_LINE_USER_CATEGORY,
1413 zx_rate.percentage_rate,
1414 zx_line.tax_rate_code,
1415 zx_line.tax_rate_id,
1416 zx_dist.rec_nrec_rate,
1417 zx_line.tax_regime_code,
1418 zx_line.tax_status_id,
1419 zx_line.tax_status_code,
1420 zx_dist.taxable_amt, --zx_line.taxable_amt,
1421 zx_dist.taxable_amt_funcl_curr, --zx_line.taxable_amt_funcl_curr,
1422 -- zx_det.billing_trading_partner_name,
1423 -- zx_det.billing_trading_partner_number,
1424 zx_det.bill_from_party_tax_prof_id,
1425 zx_det.bill_from_site_tax_prof_id,
1426 -- zx_det.billing_tp_taxpayer_id,
1427 zx_det.ship_to_site_tax_prof_id,
1428 zx_det.ship_from_site_tax_prof_id,
1429 zx_det.ship_to_party_tax_prof_id ,
1430 zx_det.ship_from_party_tax_prof_id ,
1431 ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
1432 ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
1433 ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
1434 ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
1435 ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
1436 ZX_DET.BILL_THIRD_PTY_ACCT_ID,
1437 zx_line.hq_estb_reg_number,
1438 zx_line.tax_registration_number,
1439 zx_line.legal_entity_tax_reg_number,
1440 zx_det.own_hq_site_tax_prof_id,
1444 decode(zx_dist.REVERSED_TAX_DIST_ID,NULL,''N'',''Y''), -- zx_line.cancel_flag,
1441 zx_det.own_hq_party_tax_prof_id,
1442 zx_det.port_of_entry_code,
1443 zx_line.registration_party_type,
1445 zx_line.historical_flag,
1446 zx_line.mrc_tax_line_flag,
1447 zx_line.offset_flag,
1448 zx_line.reporting_only_flag,
1449 zx_dist.self_assessed_flag,
1450 zx_line.tax_amt_included_flag,
1451 zx_line.tax_only_line_flag,
1452 zx_dist.recoverable_flag,
1453 zx_dist.posting_flag,
1454 zx_dist.reverse_flag,
1455 zx_dist.rec_nrec_tax_dist_id,
1456 zx_dist.trx_line_dist_id,
1457 zx_det.entity_code,
1458 zx_det.ledger_id,
1459 ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
1460 zx_tax.tax_type_code,
1461 ZX_RATE.TAX_RATE_NAME,
1462 zx_det.trx_level_type,--Bug 5393051
1463 zx_det.unit_price , -- Bug 5439099
1464 nvl(zx_dist.gl_date,zx_det.trx_line_gl_date) --Bug 5523095
1465 FROM zx_lines zx_line,
1466 zx_lines_det_factors zx_det,
1467 zx_rec_nrec_dist zx_dist,
1468 zx_taxes_vl zx_tax,
1469 zx_rates_vl zx_rate
1470 WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
1471 AND zx_det.application_id = zx_line.application_id
1472 AND zx_det.application_id = 200
1473 AND zx_det.entity_code = zx_line.entity_code
1474 AND zx_det.event_class_code = zx_line.event_class_code
1475 AND zx_det.trx_id = zx_line.trx_id
1476 AND zx_det.trx_line_id = zx_line.trx_line_id --Bug 5443504
1477 -- AND zx_det.application_id = zx_dist.application_id
1478 -- AND zx_det.entity_code = zx_dist.entity_code
1479 -- AND zx_det.event_class_code = zx_dist.event_class_code
1480 -- AND zx_det.event_type_code = zx_dist.event_type_code
1481 -- AND zx_det.trx_id = zx_dist.trx_id
1482 AND zx_line.tax_line_id = zx_dist.tax_line_id
1483 AND zx_det.tax_reporting_flag = ''Y''
1484 AND zx_line.tax_id = zx_tax.tax_id
1485 AND zx_line.tax_rate_id = zx_rate.tax_rate_id'
1486 ||L_WHERE_GL_TRX_DATE||' '
1487 ||L_WHERE_TRX_DATE|| ' '
1488 ||L_WHERE_REGISTER_TYPE|| ' '
1489 ||L_WHERE_GL_DATE|| ' '
1490 ||L_WHERE_TRX_NUM|| ' '
1491 ||L_WHERE_VAT_TRANSACTION_TYPE|| ' '
1492 ||L_WHERE_TRX_BUSINESS_CATEGORY|| ' '
1493 ||L_WHERE_TAX_INVOICE_DATE|| ' '
1494 ||L_WHERE_TAX_JURISDICTION_CODE|| ' '
1495 ||L_WHERE_FIRST_PTY_TAX_REG_NUM|| ' '
1496 ||L_WHERE_TAX_REGIME_CODE|| ' '
1497 ||L_WHERE_TAX|| ' '
1498 ||L_WHERE_TAX_STATUS_CODE|| ' '
1499 ||L_WHERE_TAX_RATE_CODE|| ' '
1500 ||L_WHERE_TAX_TYPE_CODE|| ' '
1501 ||L_WHERE_CURRENCY_CODE|| ' '
1502 ||L_WHERE_PARTY_NAME|| ' '
1503 ||L_WHERE_TRX_CLASS|| ' '
1504 ||L_WHERE_LEGAL_ENTITY_ID|| ' '
1505 ||L_WHERE_LEDGER_ID|| ' '
1506 ||L_WHERE_ACCOUNTING_STATUS||' '
1507 ||L_WHERE_REPORTED_STATUS||' '
1508 ||L_WHERE_ADJUSTED_DOC_NUM||' ';
1509
1510 g_sql_statement := l_sql_statement ;
1511 END IF;
1512
1513 IF G_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1514
1515 IF (g_level_procedure >= g_current_runtime_level ) THEN
1516 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL',
1517 'SQL-3 For Invoice, Credit Memo, Debit Memo: Line Level');
1518 END IF;
1519
1520 l_sql_statement :=
1521 'SELECT
1522 zx_det.application_id,
1523 zx_line.event_class_code,
1524 zx_det.internal_organization_id,
1525 zx_det.doc_event_status,
1526 zx_det.application_doc_status,
1527 zx_det.line_class,
1528 zx_det.doc_seq_id,
1529 zx_det.doc_seq_name,
1530 zx_det.doc_seq_value,
1531 zx_det.establishment_id,
1532 zx_det.batch_source_id,
1533 zx_det.currency_conversion_date,
1534 zx_det.currency_conversion_rate,
1535 zx_det.currency_conversion_type,
1536 zx_det.minimum_accountable_unit,
1537 zx_det.precision,
1538 zx_det.trx_communicated_date,
1539 zx_det.trx_currency_code,
1540 zx_line.trx_id,
1541 zx_det.trx_number,
1542 zx_det.trx_date,
1543 zx_det.trx_description,
1544 zx_det.trx_type_description,
1545 zx_rate.description,
1546 zx_det.trx_due_date,
1547 zx_det.trx_line_description,
1548 zx_line.trx_line_id,
1549 zx_line.trx_line_number,
1550 zx_line.trx_line_quantity,
1551 sum(zx_line.line_amt),
1552 zx_det.trx_line_type,
1553 zx_det.trx_shipping_date,
1554 zx_det.uom_code,
1555 zx_det.related_doc_date,
1556 zx_det.related_doc_entity_code,
1557 zx_det.related_doc_event_class_code,
1558 zx_det.related_doc_number,
1559 zx_det.related_doc_trx_id,
1560 zx_det.applied_from_application_id,
1561 zx_line.applied_from_entity_code,
1562 zx_line.applied_from_event_class_code,
1563 zx_det.applied_from_line_id,
1564 zx_line.applied_from_trx_id,
1565 zx_line.applied_from_trx_number,
1566 zx_det.applied_to_application_id,
1567 zx_line.applied_to_entity_code,
1568 zx_line.applied_to_event_class_code,
1569 zx_line.applied_to_trx_id,
1573 zx_det.adjusted_doc_date,
1570 zx_det.applied_to_trx_line_id,
1571 zx_det.applied_to_trx_number,
1572 zx_det.adjusted_doc_application_id,
1574 zx_det.adjusted_doc_entity_code,
1575 zx_det.adjusted_doc_event_class_code,
1576 ZX_DET.ADJUSTED_DOC_NUMBER,
1577 --zx_det.country_of_supply,
1578 zx_det.default_taxation_country,
1579 zx_det.merchant_party_document_number,
1580 zx_det.merchant_party_name,
1581 zx_det.merchant_party_reference,
1582 zx_det.merchant_party_tax_reg_number,
1583 zx_det.merchant_party_taxpayer_id,
1584 zx_det.ref_doc_application_id,
1585 zx_det.ref_doc_entity_code,
1586 zx_det.ref_doc_event_class_code,
1587 zx_det.ref_doc_line_id,
1588 zx_det.ref_doc_line_quantity,
1589 zx_det.ref_doc_trx_id,
1590 zx_det.start_expense_date,
1591 zx_det.assessable_value,
1592 zx_det.document_sub_type,
1593 zx_det.line_intended_use,
1594 zx_det.product_category,
1595 zx_det.product_description,
1596 zx_det.product_fisc_classification,
1597 zx_det.product_id,
1598 zx_det.supplier_exchange_rate,
1599 zx_det.supplier_tax_invoice_date,
1600 zx_det.supplier_tax_invoice_number,
1601 zx_det.tax_invoice_date,
1602 zx_det.tax_invoice_number,
1603 zx_det.trx_business_category,
1604 zx_det.user_defined_fisc_class,
1605 sum(zx_dist.rec_nrec_tax_amt_tax_curr),
1606 zx_line.OFFSET_TAX_RATE_CODE,
1607 sum(zx_dist.orig_rec_nrec_tax_amt),
1608 sum(zx_line.orig_tax_amt),
1609 sum(zx_line.orig_tax_amt_tax_curr),
1610 sum(zx_line.orig_taxable_amt),
1611 sum(zx_line.orig_taxable_amt_tax_curr),
1612 sum(zx_dist.orig_rec_nrec_tax_amt_tax_curr),
1613 zx_dist.recovery_rate_code,
1614 zx_dist.recovery_type_code,
1615 zx_line.tax,
1616 sum(zx_dist.rec_nrec_tax_amt),
1617 sum(zx_dist.rec_nrec_tax_amt_funcl_curr),
1618 sum(zx_line.tax_amt_tax_curr),
1619 zx_line.tax_apportionment_line_number,
1620 zx_line.tax_currency_code,
1621 zx_line.tax_date,
1622 zx_line.tax_determine_date,
1623 zx_line.tax_jurisdiction_code,
1624 to_char(null),--zx_line.tax_line_id ,
1625 to_char(null),--zx_line.tax_line_number ,
1626 zx_line.attribute1 ,
1627 zx_line.attribute2 ,
1628 zx_line.attribute3 ,
1629 zx_line.attribute4 ,
1630 zx_line.attribute5 ,
1631 zx_line.attribute6 ,
1632 zx_line.attribute7 ,
1633 zx_line.attribute8 ,
1634 zx_line.attribute9 ,
1635 zx_line.attribute10,
1636 zx_line.attribute11,
1637 zx_line.attribute12,
1638 zx_line.attribute13,
1639 zx_line.attribute14,
1640 zx_line.attribute15,
1641 zx_line.attribute_category ,
1642 zx_rate.percentage_rate,
1643 zx_line.tax_rate_code,
1644 zx_line.tax_rate_id,
1645 zx_dist.rec_nrec_rate,
1646 zx_line.tax_regime_code,
1647 zx_line.tax_status_id,
1648 zx_line.tax_status_code,
1649 sum(zx_dist.taxable_amt),
1650 sum(zx_dist.taxable_amt_funcl_curr) ,
1651 -- zx_det.billing_trading_partner_name,
1652 -- zx_det.billing_trading_partner_number,
1653 zx_det.bill_from_party_tax_prof_id,
1654 zx_det.bill_from_site_tax_prof_id,
1655 -- zx_det.billing_tp_taxpayer_id,
1656 zx_det.ship_to_site_tax_prof_id,
1657 zx_det.ship_from_site_tax_prof_id,
1658 zx_det.ship_to_party_tax_prof_id ,
1659 zx_det.ship_from_party_tax_prof_id ,
1660 ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
1661 ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
1662 ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
1663 ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
1664 ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
1665 ZX_DET.BILL_THIRD_PTY_ACCT_ID,
1666 zx_line.hq_estb_reg_number ,
1667 zx_line.tax_registration_number,
1668 zx_line.legal_entity_tax_reg_number,
1669 zx_det.own_hq_site_tax_prof_id,
1670 zx_det.own_hq_party_tax_prof_id,
1671 zx_det.port_of_entry_code,
1672 zx_line.registration_party_type,
1673 zx_line.cancel_flag,
1674 zx_line.historical_flag,
1675 zx_line.mrc_tax_line_flag,
1676 zx_line.offset_flag,
1677 zx_line.reporting_only_flag,
1678 zx_dist.self_assessed_flag,
1679 zx_line.tax_amt_included_flag,
1680 zx_line.tax_only_line_flag,
1681 zx_dist.recoverable_flag,
1682 zx_dist.posting_flag,
1683 zx_dist.reverse_flag,
1684 zx_line.trx_line_id,
1685 to_number(NULL),
1686 zx_det.entity_code,
1687 zx_det.ledger_id,
1688 ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
1689 zx_tax.tax_type_code,
1690 ZX_RATE.TAX_RATE_NAME,
1691 zx_det.trx_level_type, --Bug 5393051
1692 zx_det.unit_price , -- Bug 5439099
1693 zx_det.trx_line_gl_date --Bug 5523095
1694 FROM zx_lines zx_line,
1695 zx_lines_det_factors zx_det,
1696 zx_rec_nrec_dist zx_dist,
1697 zx_taxes_vl zx_tax,
1698 zx_rates_vl zx_rate
1699 WHERE zx_det.internal_organization_id = zx_line.internal_organization_id
1703 AND zx_det.event_class_code = zx_line.event_class_code
1700 AND zx_det.application_id = zx_line.application_id
1701 AND zx_det.application_id = 200
1702 AND zx_det.entity_code = zx_line.entity_code
1704 AND zx_det.trx_id = zx_line.trx_id
1705 AND zx_det.trx_line_id = zx_line.trx_line_id --Bug 5443504
1706 -- AND zx_det.application_id = zx_dist.application_id
1707 -- AND zx_det.entity_code = zx_dist.entity_code
1708 -- AND zx_det.event_class_code = zx_dist.event_class_code
1709 -- AND zx_det.event_type_code = zx_dist.event_type_code
1710 -- AND zx_det.trx_id = zx_dist.trx_id
1711 AND zx_line.tax_line_id = zx_dist.tax_line_id
1712 AND zx_line.tax_id = zx_tax.tax_id
1713 AND zx_line.tax_rate_id = zx_rate.tax_rate_id
1714 AND zx_det.tax_reporting_flag = ''Y'' '
1715 ||L_WHERE_GL_TRX_DATE||' '
1716 ||L_WHERE_TRX_DATE|| ' '
1717 ||L_WHERE_REGISTER_TYPE|| ' '
1718 ||L_WHERE_GL_DATE|| ' '
1719 ||L_WHERE_TRX_NUM|| ' '
1720 ||L_WHERE_VAT_TRANSACTION_TYPE|| ' '
1721 ||L_WHERE_TRX_BUSINESS_CATEGORY|| ' '
1722 ||L_WHERE_TAX_INVOICE_DATE|| ' '
1723 ||L_WHERE_TAX_JURISDICTION_CODE|| ' '
1724 ||L_WHERE_FIRST_PTY_TAX_REG_NUM|| ' '
1725 ||L_WHERE_TAX_REGIME_CODE|| ' '
1726 ||L_WHERE_TAX|| ' '
1727 ||L_WHERE_TAX_STATUS_CODE|| ' '
1728 ||L_WHERE_TAX_RATE_CODE|| ' '
1729 ||L_WHERE_TAX_TYPE_CODE|| ' '
1730 ||L_WHERE_CURRENCY_CODE|| ' '
1731 ||L_WHERE_PARTY_NAME|| ' '
1732 ||L_WHERE_TRX_CLASS|| ' '
1733 ||L_WHERE_LEGAL_ENTITY_ID|| ' '
1734 ||L_WHERE_LEDGER_ID|| ' '
1735 ||L_WHERE_ACCOUNTING_STATUS||' '
1736 ||L_WHERE_REPORTED_STATUS||' '
1737 ||L_WHERE_ADJUSTED_DOC_NUM||' '
1738 ||'GROUP BY
1739 zx_det.application_id,
1740 zx_line.event_class_code ,
1741 zx_det.internal_organization_id,
1742 zx_det.doc_event_status,
1743 zx_det.application_doc_status,
1744 zx_det.line_class,
1745 zx_det.doc_seq_id ,
1746 zx_det.doc_seq_name ,
1747 zx_det.doc_seq_value,
1748 zx_det.establishment_id,
1749 zx_det.batch_source_id,
1750 zx_det.currency_conversion_date,
1751 zx_det.currency_conversion_rate,
1752 zx_det.currency_conversion_type,
1753 zx_det.minimum_accountable_unit,
1754 zx_det.precision,
1755 zx_det.trx_communicated_date ,
1756 zx_det.trx_currency_code,
1757 zx_line.trx_id ,
1758 zx_det.trx_number ,
1759 zx_det.trx_date,
1760 zx_det.trx_description,
1761 zx_det.trx_type_description,
1762 zx_rate.description,
1763 zx_det.trx_due_date,
1764 zx_det.trx_line_description,
1765 zx_line.trx_line_id,
1766 zx_line.trx_line_number,
1767 zx_line.trx_line_quantity,
1768 --zx_line.line_amt,
1769 zx_det.trx_line_type,
1770 zx_det.trx_shipping_date,
1771 zx_det.uom_code,
1772 zx_det.related_doc_date,
1773 zx_det.related_doc_entity_code,
1774 zx_det.related_doc_event_class_code,
1775 zx_det.related_doc_number,
1776 zx_det.related_doc_trx_id,
1777 zx_det.applied_from_application_id,
1778 zx_line.applied_from_entity_code,
1779 zx_line.applied_from_event_class_code,
1780 zx_det.applied_from_line_id,
1781 zx_line.applied_from_trx_id,
1782 zx_line.applied_from_trx_number,
1783 zx_det.applied_to_application_id,
1784 zx_line.applied_to_entity_code,
1785 zx_line.applied_to_event_class_code,
1786 zx_line.applied_to_trx_id,
1787 zx_det.applied_to_trx_line_id,
1788 zx_det.applied_to_trx_number,
1789 zx_det.adjusted_doc_application_id,
1790 zx_det.adjusted_doc_date,
1791 zx_det.adjusted_doc_entity_code,
1792 zx_det.adjusted_doc_event_class_code,
1793 ZX_DET.ADJUSTED_DOC_NUMBER,
1794 --zx_det.country_of_supply,
1795 zx_det.default_taxation_country,
1796 -- zx_det.default_taxation_country, --Bug 5374021
1797 zx_det.merchant_party_document_number,
1798 zx_det.merchant_party_name,
1799 zx_det.merchant_party_reference,
1800 zx_det.merchant_party_tax_reg_number,
1801 zx_det.merchant_party_taxpayer_id,
1802 zx_det.ref_doc_application_id,
1803 zx_det.ref_doc_entity_code,
1804 zx_det.ref_doc_event_class_code,
1805 zx_det.ref_doc_line_id,
1806 zx_det.ref_doc_line_quantity,
1807 zx_det.ref_doc_trx_id,
1808 zx_det.start_expense_date,
1809 zx_det.assessable_value,
1810 zx_det.document_sub_type,
1811 zx_det.line_intended_use,
1812 zx_det.product_category,
1813 zx_det.product_description,
1814 zx_det.product_fisc_classification,
1815 zx_det.product_id,
1816 zx_det.supplier_exchange_rate,
1817 zx_det.supplier_tax_invoice_date,
1818 zx_det.supplier_tax_invoice_number,
1819 zx_det.tax_invoice_date,
1820 zx_det.tax_invoice_number,
1821 zx_det.trx_business_category,
1822 zx_det.user_defined_fisc_class,
1823 --zx_dist.rec_nrec_tax_amt_tax_curr,
1824 zx_line.OFFSET_TAX_RATE_CODE,
1825 --zx_dist.orig_rec_nrec_tax_amt,
1826 --zx_line.orig_tax_amt,
1830 --zx_dist.orig_rec_nrec_tax_amt_tax_curr,
1827 --zx_line.orig_tax_amt_tax_curr ,
1828 --zx_line.orig_taxable_amt,
1829 --zx_line.orig_taxable_amt_tax_curr,
1831 zx_dist.recovery_rate_code,
1832 zx_dist.recovery_type_code,
1833 zx_line.tax,
1834 --zx_dist.rec_nrec_tax_amt,
1835 --zx_dist.rec_nrec_tax_amt_funcl_curr,
1836 --zx_line.tax_amt_tax_curr,
1837 zx_line.tax_apportionment_line_number,
1838 zx_line.tax_currency_code,
1839 zx_line.tax_date,
1840 zx_line.tax_determine_date,
1841 zx_line.tax_jurisdiction_code,
1842 to_char(null),--zx_line.tax_line_id ,
1843 to_char(null),--zx_line.tax_line_number ,
1844 zx_line.attribute1 ,
1845 zx_line.attribute2 ,
1846 zx_line.attribute3 ,
1847 zx_line.attribute4 ,
1848 zx_line.attribute5 ,
1849 zx_line.attribute6 ,
1850 zx_line.attribute7 ,
1851 zx_line.attribute8 ,
1852 zx_line.attribute9 ,
1853 zx_line.attribute10,
1854 zx_line.attribute11,
1855 zx_line.attribute12,
1856 zx_line.attribute13,
1857 zx_line.attribute14,
1858 zx_line.attribute15,
1859 zx_line.attribute_category ,
1860 zx_rate.percentage_rate,
1861 zx_line.tax_rate_code,
1862 zx_line.tax_rate_id,
1863 zx_dist.rec_nrec_rate,
1864 zx_line.tax_regime_code,
1865 zx_line.tax_status_id,
1866 zx_line.tax_status_code,
1867 --zx_line.taxable_amt,
1868 --zx_line.taxable_amt_funcl_curr ,
1869 -- zx_det.billing_trading_partner_name,
1870 -- zx_det.billing_trading_partner_number,
1871 zx_det.bill_from_party_tax_prof_id,
1872 zx_det.bill_from_site_tax_prof_id,
1873 -- zx_det.billing_tp_taxpayer_id,
1874 zx_det.ship_to_site_tax_prof_id,
1875 zx_det.ship_from_site_tax_prof_id,
1876 zx_det.ship_to_party_tax_prof_id,
1877 zx_det.ship_from_party_tax_prof_id,
1878 ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID, --Bug 5374021
1879 ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID, --Bug 5374021
1880 ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID, --Bug 5374021
1881 ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID, --Bug 5374021
1882 ZX_DET.SHIP_THIRD_PTY_ACCT_ID, --Bug 5374021
1883 ZX_DET.BILL_THIRD_PTY_ACCT_ID,--Bug 5374021
1884 zx_line.hq_estb_reg_number,
1885 zx_line.tax_registration_number,
1886 zx_line.legal_entity_tax_reg_number,
1887 zx_det.own_hq_site_tax_prof_id,
1888 zx_det.own_hq_party_tax_prof_id,
1889 zx_det.port_of_entry_code,
1890 zx_line.registration_party_type,
1891 zx_line.cancel_flag,
1892 zx_line.historical_flag,
1893 zx_line.mrc_tax_line_flag,
1894 zx_line.offset_flag,
1895 zx_line.reporting_only_flag,
1896 zx_dist.self_assessed_flag,
1897 zx_line.tax_amt_included_flag,
1898 zx_line.tax_only_line_flag,
1899 zx_dist.recoverable_flag,
1900 zx_dist.posting_flag,
1901 zx_dist.reverse_flag,
1902 zx_line.trx_line_id , --Bug 5374021
1903 zx_det.entity_code,
1904 zx_det.ledger_id,
1905 ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
1906 --ZX_RATE.RATE_TYPE_CODE,
1907 zx_tax.tax_type_code,
1908 ZX_RATE.TAX_RATE_NAME,
1909 zx_det.trx_level_type ,
1910 zx_det.unit_price,
1911 zx_det.trx_line_gl_date';
1912 --Bug 5523095
1913
1914 g_sql_statement := l_sql_statement ;
1915
1916 END IF; -- summary level
1917
1918 IF (g_level_procedure >= g_current_runtime_level ) THEN
1919 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.BUILD_SQL.BEGIN',
1920 'ZX_AP_EXTRACT_PKG: BUILD_SQL(-)');
1921 END IF;
1922
1923 EXCEPTION
1924 WHEN OTHERS THEN
1925 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1926 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1927 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','build_sql- '|| g_error_buffer);
1928 FND_MSG_PUB.Add;
1929 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1930 FND_LOG.STRING(g_level_unexpected,
1931 'ZX.TRL.ZX_AP_EXTRACT_PKG.build_sql',
1932 g_error_buffer);
1933 END IF;
1934 g_retcode := 2;
1935
1936 END build_sql;
1937
1938 /*===========================================================================+
1939 | PROCEDURE |
1940 | execute_sql_stmt |
1941 | |
1942 | DESCRIPTION |
1943 | This procedure executes the Dynamic SQL statement built by |
1944 | the procedure BUILD_SQL. |
1945 | |
1946 | Called from INSERT_TAX_DATA |
1947 | |
1948 | SCOPE - Private |
1949 | |
1953 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
1950 | NOTES |
1951 | |
1952 | MODIFICATION HISTORY |
1954 +===========================================================================*/
1955
1956 PROCEDURE execute_sql_stmt IS
1957
1958 l_sql1 VARCHAR2(3500);
1959 l_sql2 VARCHAR2(3500);
1960 l_sql3 VARCHAR2(3500);
1961 l_sql4 VARCHAR2(3500);
1962 l_sql5 VARCHAR2(3500);
1963 l_sql6 VARCHAR2(3500);
1964 l_sql7 VARCHAR2(3500);
1965 l_sql8 VARCHAR2(3500);
1966
1967 BEGIN
1968
1969 IF (g_level_procedure >= g_current_runtime_level ) THEN
1970 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.EXECUTE_SQL_STMT.BEGIN',
1971 'ZX_AP_EXTRACT_PKG: EXECUTE_SQL_STMT(+)');
1972 END IF;
1973
1974
1975 -- l_sql_statement_tbl(1) := G_COLUMN_LIST_TRX_HDR_LVL; --AP Transaction level
1976 -- l_sql_statement_tbl(2) := G_COLUMN_LIST_TRX_DIST_LVL; --AP Distribution level
1977 -- l_sql_statement_tbl(3) := G_COLUMN_LIST_TRX_LINE_LVL; --AP Line level
1978
1979 IF (g_level_procedure >= g_current_runtime_level ) THEN
1980 --Added as part of testing ( for showing all the bind vars values )
1981 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1982 ' Displaying all the Global Bind Variable Values used for the Dynamic SQL Stmt ' );
1983 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1984 'g_gl_date_low : '||g_gl_date_low);
1985 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1986 'g_gl_date_high : '||g_gl_date_high);
1987 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1988 'G_LEDGER_ID : '||G_LEDGER_ID);
1989 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1990 'g_trx_date_low : '||g_trx_date_low);
1991 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1992 'g_trx_date_high : '||g_trx_date_high);
1993 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1994 'g_trx_number_low : '||g_trx_number_low);
1995 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1996 'g_trx_number_high : '||g_trx_number_high);
1997 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
1998 'g_vat_transaction_type_code : '||g_vat_transaction_type_code);
1999 -- FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2000 -- 'g_document_sub_type : '||g_document_sub_type);
2001 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2002 'g_trx_business_category : '||g_trx_business_category);
2003 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2004 'g_tax_invoice_date_low : '||g_tax_invoice_date_low);
2005 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2006 'g_tax_invoice_date_high : '||g_tax_invoice_date_high);
2007 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2008 'g_tax_jurisdiction_code : '||g_tax_jurisdiction_code);
2009 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2010 'g_first_party_tax_reg_num : '||g_first_party_tax_reg_num);
2011 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2012 'g_tax_regime_code : '||g_tax_regime_code);
2013 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2014 'g_tax : '||g_tax);
2015 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2016 'g_tax_status_code : '||g_tax_status_code);
2017 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2018 'g_tax_rate_code_low : '||g_tax_rate_code_low);
2019 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2020 'g_tax_rate_code_high : '||g_tax_rate_code_high);
2021 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2022 'g_tax_type_code_low : '||g_tax_type_code_low);
2023 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2024 'g_tax_type_code_high : '||g_tax_type_code_high);
2025 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2026 'g_currency_code_low : '||g_currency_code_low);
2027 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2028 'g_currency_code_high : '||g_currency_code_high);
2029 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2030 'g_legal_entity_id : '||g_legal_entity_id);
2031 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL',
2032 'g_ledger_id : '||g_ledger_id);
2033
2034 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'Dynamic sql statement : '||g_summary_level);
2035 l_sql1 := substr(g_sql_statement,1,3000);
2039 l_sql3 := substr(g_sql_statement,6001,3000);
2036 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql1);
2037 l_sql2 := substr(g_sql_statement,3001,3000);
2038 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql2);
2040 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql3);
2041 l_sql4 := substr(g_sql_statement,9001,3000);
2042 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql4);
2043 l_sql5 := substr(g_sql_statement,12001,3000);
2044 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql5);
2045 l_sql6 := substr(g_sql_statement,15001,3000);
2046 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql6);
2047 l_sql7 := substr(g_sql_statement,18001,3000);
2048 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql7);
2049 l_sql8 := substr(g_sql_statement,21001,3000);
2050 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_AR_TRX_INFO SQL', 'g_sql_statement : '||l_sql8);
2051 END IF;
2052
2053
2054
2055 fetch_tax_info(g_sql_statement);
2056
2057 /* FOR i IN 1..l_sql_statement_tbl.COUNT LOOP
2058 IF l_sql_statement_tbl(i) IS NOT NULL THEN
2059 FETCH_TAX_INFO(l_sql_statement_tbl(i));
2060 END IF;
2061 COMMIT;
2062 END LOOP; */
2063
2064 IF (g_level_procedure >= g_current_runtime_level ) THEN
2065 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.execute_sql_stmt.END',
2066 'ZX_AP_EXTRACT_PKG: execute_sql_stmt(-)');
2067 END IF;
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2072 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2073 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','execute_sql_stmt- '|| g_error_buffer);
2074 FND_MSG_PUB.Add;
2075 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2076 FND_LOG.STRING(g_level_unexpected,
2077 'ZX.TRL.ZX_AP_EXTRACT_PKG.execute_sql_stmt',
2078 g_error_buffer);
2079 END IF;
2080 g_retcode := 2;
2081
2082 END execute_sql_stmt;
2083
2084 /*===========================================================================+
2085 | PROCEDURE |
2086 | fetch_tax_info |
2087 | DESCRIPTION |
2088 | This procedure executes dyanamic sql statement using bind variables |
2089 | |
2090 | SCOPE - Private |
2091 | |
2092 | NOTES |
2093 | |
2094 | MODIFICATION HISTORY |
2095 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
2096 | |
2097 +===========================================================================*/
2098 PROCEDURE fetch_tax_info (
2099 p_statement IN VARCHAR2)
2100 IS
2101
2102 TYPE zx_rep_detail_curtype IS REF CURSOR;
2103 zx_rep_detail_csr zx_rep_detail_curtype;
2104 i BINARY_INTEGER;
2105
2106 BEGIN
2107
2108 IF (g_level_procedure >= g_current_runtime_level ) THEN
2109 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info.BEGIN',
2110 'ZX_AP_EXTRACT_PKG: fetch_tax_info(+)');
2111 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info',
2112 'Open cursor');
2113 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info',
2114 'Gl Date Low/High'||to_char(g_gl_date_low)||' '
2115 ||to_char(g_gl_date_high));
2116 END IF;
2117
2118 OPEN zx_rep_detail_csr FOR p_statement
2119 USING g_trx_date_low,
2120 g_trx_date_high,
2121 g_trx_date_low_ln,
2122 g_trx_date_high_ln,
2123 -- g_register_type,
2124 g_gl_date_low,
2125 g_gl_date_high,
2126 g_trx_number_low,
2127 g_trx_number_high,
2128 g_vat_transaction_type_code,
2129 --g_document_sub_type,
2130 g_trx_business_category,
2131 g_tax_invoice_date_low ,
2132 g_tax_invoice_date_high,
2133 g_tax_jurisdiction_code,
2134 g_first_party_tax_reg_num,
2135 g_tax_regime_code,
2136 g_tax,
2137 g_tax_status_code,
2138 g_tax_rate_code_low,
2139 g_tax_rate_code_high,
2140 g_tax_type_code_low,
2141 g_tax_type_code_high,
2142 g_currency_code_low,
2146 g_party_name,
2143 g_currency_code_high,
2144 -- g_batch_date_low ,
2145 -- g_batch_date_high,
2147 --g_batch_name,
2148 g_legal_entity_id ,
2149 g_ledger_id,
2150 g_adjusted_doc_from,
2151 g_adjusted_doc_to;
2152 -- g_trading_partner_id;
2153
2154 IF (g_level_procedure >= g_current_runtime_level ) THEN
2155 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_TAX_INFO',
2156 'After USING Clause Call :');
2157 END IF;
2158
2159 i := 1;
2160
2161 init_gt_variables;
2162 g_created_by := fnd_global.user_id;
2163 g_creation_date := sysdate;
2164 g_last_updated_by := fnd_global.user_id;
2165 g_last_update_login := fnd_global.login_id;
2166 g_last_update_date := sysdate;
2167
2168 -- insert when fetch up to 1000 rows
2169
2170 LOOP
2171 FETCH zx_rep_detail_csr INTO
2172 gt_application_id(i),
2173 gt_event_class_code(i),
2174 gt_internal_organization_id(i),
2175 gt_doc_event_status(i),
2176 gt_application_doc_status(i),
2177 gt_line_class(i),
2178 gt_doc_seq_id(i),
2179 gt_doc_seq_name(i),
2180 gt_doc_seq_value(i),
2181 gt_establishment_id(i),
2182 gt_batch_source_id(i),
2183 gt_currency_conversion_date(i),
2184 gt_currency_conversion_rate(i),
2185 gt_currency_conversion_type(i),
2186 gt_minimum_accountable_unit(i),
2187 gt_precision(i),
2188 gt_trx_communicated_date(i),
2189 gt_trx_currency_code(i),
2190 gt_trx_id(i),
2191 gt_trx_number(i),
2192 gt_trx_date(i),
2193 gt_trx_description(i),
2194 gt_trx_type_description(i),
2195 gt_tax_rate_code_description(i),
2196 gt_trx_due_date(i),
2197 gt_trx_line_description(i),
2198 gt_trx_line_id(i),
2199 gt_trx_line_number(i),
2200 gt_trx_line_quantity(i),
2201 gt_trx_line_amt(i),
2202 gt_trx_line_type(i),
2203 gt_trx_shipping_date(i),
2204 gt_uom_code(i),
2205 gt_related_doc_date(i),
2206 gt_related_doc_entity_code(i),
2207 gt_related_doc_event_cls_code(i),
2208 gt_related_doc_number(i),
2209 gt_related_doc_trx_id(i),
2210 gt_applied_from_appl_id(i),
2211 gt_applied_from_entity_code(i),
2212 gt_applied_from_event_cls_code(i),
2213 gt_applied_from_line_id(i),
2214 gt_applied_from_trx_id(i),
2215 gt_applied_from_trx_number(i),
2216 gt_applied_to_appl_id(i),
2217 gt_applied_to_entity_code(i),
2218 gt_applied_to_event_cls_code(i),
2219 gt_applied_to_trx_id(i),
2220 gt_applied_to_trx_line_id(i),
2221 gt_applied_to_trx_number(i),
2222 gt_adjusted_doc_appl_id(i),
2223 gt_adjusted_doc_date(i),
2224 gt_adjusted_doc_entity_code(i),
2225 gt_adjusted_doc_event_cls_code(i),
2226 GT_ADJUSTED_DOC_NUMBER(i),
2227 -- gt_country_of_supply(i),
2228 gt_default_taxation_country(i),
2229 gt_merchant_party_doc_num(i),
2230 gt_merchant_party_name(i),
2231 gt_merchant_party_reference(i),
2232 gt_merchant_party_tax_reg_num(i),
2233 gt_merchant_party_taxpayer_id(i),
2234 gt_ref_doc_application_id(i),
2235 gt_ref_doc_entity_code(i),
2236 gt_ref_doc_event_cls_code(i),
2237 gt_ref_doc_line_id(i),
2238 gt_ref_doc_line_quantity(i),
2239 gt_ref_doc_trx_id(i),
2240 gt_start_expense_date(i),
2241 gt_assessable_value(i),
2242 gt_document_sub_type(i),
2243 gt_line_intended_use(i),
2244 gt_product_category(i),
2245 gt_product_description(i),
2246 gt_prod_fisc_classification(i),
2247 gt_product_id(i),
2248 gt_supplier_exchange_rate(i),
2249 gt_supplier_tax_invoice_date(i),
2250 gt_supplier_tax_invoice_num(i),
2251 gt_tax_invoice_date(i),
2252 gt_tax_invoice_number(i),
2253 gt_trx_business_category(i),
2254 gt_user_defined_fisc_class(i),
2255 gt_nrec_tax_amt_tax_curr(i),
2256 gt_offset_tax_rate_code(i),
2257 gt_orig_rec_nrec_tax_amt(i),
2258 gt_orig_tax_amt(i),
2259 gt_orig_tax_amt_tax_curr(i),
2260 gt_orig_taxable_amt(i),
2261 gt_orig_taxable_amt_tax_curr(i),
2262 gt_rec_tax_amt_tax_curr(i),
2263 gt_recovery_rate_code(i),
2264 gt_recovery_type_code(i),
2265 gt_tax(i),
2266 gt_tax_amt(i),
2267 gt_tax_amt_funcl_curr(i),
2268 gt_tax_amt_tax_curr(i),
2269 gt_tax_apportionment_line_num(i),
2270 gt_tax_currency_code(i),
2271 gt_tax_date(i),
2272 gt_tax_determine_date(i),
2273 gt_tax_jurisdiction_code(i),
2274 gt_tax_line_id(i),
2275 gt_tax_line_number(i),
2276 gt_tax_line_user_attribute1(i),
2277 gt_tax_line_user_attribute2(i),
2278 gt_tax_line_user_attribute3(i),
2279 gt_tax_line_user_attribute4(i),
2280 gt_tax_line_user_attribute5(i),
2281 gt_tax_line_user_attribute6(i),
2285 gt_tax_line_user_attribute10(i),
2282 gt_tax_line_user_attribute7(i),
2283 gt_tax_line_user_attribute8(i),
2284 gt_tax_line_user_attribute9(i),
2286 gt_tax_line_user_attribute11(i),
2287 gt_tax_line_user_attribute12(i),
2288 gt_tax_line_user_attribute13(i),
2289 gt_tax_line_user_attribute14(i),
2290 gt_tax_line_user_attribute15(i),
2291 gt_tax_line_user_category(i),
2292 gt_tax_rate(i),
2293 gt_tax_rate_code(i),
2294 gt_tax_rate_id(i),
2295 gt_tax_recovery_rate(i),
2296 gt_tax_regime_code(i),
2297 gt_tax_status_id(i),
2298 gt_tax_status_code(i),
2299 gt_taxable_amt(i),
2300 gt_taxable_amt_funcl_curr(i),
2301 -- gt_billing_tp_name(i),
2302 -- gt_billing_tp_number(i),
2303 gt_bill_from_pty_tax_prof_id(i),
2304 gt_bill_from_site_tax_prof_id(i),
2305 --gt_billing_tp_taxpayer_id(i),
2306 gt_ship_to_site_tax_prof_id(i),
2307 gt_ship_from_site_tax_prof_id(i),
2308 gt_ship_to_pty_tax_prof_id(i),
2309 gt_ship_from_pty_tax_prof_id(i),
2310 GT_SHIPPING_TP_ADDRESS_ID(i),
2311 GT_BILLING_TP_ADDRESS_ID(i),
2312 GT_SHIPPING_TP_SITE_ID(i),
2313 GT_BILLING_TP_SITE_ID(i),
2314 GT_SHIPPING_TP_ID(i),
2315 GT_BILLING_TRADING_PARTNER_ID(i),
2316 gt_hq_estb_reg_number(i),
2317 gt_tax_line_registration_num(i),
2318 gt_legal_entity_tax_reg_num(i),
2319 gt_own_hq_pty_site_prof_id(i),
2320 gt_own_hq_pty_tax_prof_id(i),
2321 gt_port_of_entry_code(i),
2322 gt_registration_party_type(i),
2323 gt_cancel_flag(i),
2324 gt_historical_flag(i),
2325 gt_mrc_tax_line_flag(i),
2326 gt_offset_flag(i),
2327 gt_reporting_only_flag(i),
2328 gt_self_assessed_flag(i),
2329 gt_tax_amt_included_flag(i),
2330 gt_tax_only_flag(i),
2331 gt_tax_recoverable_flag(i),
2332 gt_posted_flag(i),
2333 gt_reverse_flag(i),
2334 gt_actg_source_id(i),
2335 gt_taxable_item_source_id(i),
2336 gt_entity_code(i),
2337 gt_ledger_id(i),
2338 GT_TAX_RATE_VAT_TRX_TYPE_CODE(i),
2339 GT_TAX_TYPE_CODE(i),
2340 GT_TAX_RATE_CODE_NAME(i),
2341 gt_trx_level_type(i),--Bug 5393051
2342 gt_unit_price_tbl(i), -- Bug 5439099
2343 gt_gl_date(i); --Bug 5523095
2344
2345 /* IF (g_level_procedure >= g_current_runtime_level ) THEN
2346 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info',
2347 'Before insert Call :' ||to_char(GT_TRX_ID.count));
2348 END IF;
2349 */
2350 IF zx_rep_detail_csr%FOUND THEN
2351 IF (g_level_procedure >= g_current_runtime_level ) THEN
2352 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info',
2353 'zx_rep_detail_csr Found :' );
2354 END IF;
2355
2356 -- populate EXTRACT_REPORT_LINE_NUMBER
2357
2358 gt_extract_rep_line_num(i) := g_extract_line_num;
2359 g_extract_line_num := g_extract_line_num + 1;
2360
2361 IF (i >= c_lines_per_insert) THEN
2362 insert_tax_info;
2363 COMMIT;
2364
2365 IF (g_level_procedure >= g_current_runtime_level ) THEN
2366 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_TAX_INFO',
2367 'After insert_tax_info Call :' );
2368 END IF;
2369 i := 1;
2370 init_gt_variables;
2371 ELSE
2372 i := i + 1;
2373 END IF;
2374 ELSE
2375 IF (g_level_procedure >= g_current_runtime_level ) THEN
2376 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_TAX_INFO',
2377 'Before INSERT_TAX_INFO 2 Call :' );
2378 END IF;
2379
2380 -- total rows fetched less than 1000
2381 -- insert the rest of rows
2382
2383 insert_tax_info;
2384 COMMIT;
2385
2386 IF (g_level_procedure >= g_current_runtime_level ) THEN
2387 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FETCH_TAX_INFO',
2388 'After INSERT_TAX_INFO 2 :' );
2389 END IF;
2390
2391 CLOSE zx_rep_detail_csr;
2392 EXIT;
2393 END IF;
2394 END LOOP;
2395
2396 IF (g_level_procedure >= g_current_runtime_level ) THEN
2397 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info.END',
2398 'ZX_AP_EXTRACT_PKG: fetch_tax_info(-)');
2399 END IF;
2400
2401 EXCEPTION
2402 WHEN OTHERS THEN
2403 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2404 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
2405 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
2406 FND_MSG_PUB.Add;
2407 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2408 FND_LOG.STRING(g_level_unexpected,
2409 'ZX.TRL.ZX_AP_EXTRACT_PKG.fetch_tax_info',
2410 g_error_buffer);
2411 END IF;
2412
2416
2413 g_retcode := 2;
2414
2415 END fetch_tax_info;
2417 /*===========================================================================+
2418 | PROCEDURE |
2419 | INIT_GT_VARIABLES |
2420 | DESCRIPTION |
2421 | This proceure initializes all global variables |
2422 | |
2423 | SCOPE - Private |
2424 | |
2425 | NOTES |
2426 | |
2427 | MODIFICATION HISTORY |
2428 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
2429 | |
2430 +===========================================================================*/
2431
2432 PROCEDURE init_gt_variables
2433 IS
2434
2435 BEGIN
2436
2437 IF (g_level_procedure >= g_current_runtime_level ) THEN
2438 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.init_gt_variables.BEGIN',
2439 'ZX_AP_EXTRACT_PKG: init_gt_variables(+)');
2440 END IF;
2441
2442 gt_extract_rep_line_num.delete;
2443 gt_application_id.delete;
2444 gt_event_class_code.delete;
2445 gt_internal_organization_id.delete;
2446 gt_doc_event_status.delete;
2447 gt_application_doc_status.delete;
2448 gt_line_class.delete;
2449 gt_doc_seq_id.delete;
2450 gt_doc_seq_name.delete;
2451 gt_doc_seq_value.delete;
2452 gt_establishment_id.delete;
2453 gt_batch_source_id.delete;
2454 gt_currency_conversion_date.delete;
2455 gt_currency_conversion_rate.delete;
2456 gt_currency_conversion_type.delete;
2457 gt_minimum_accountable_unit.delete;
2458 gt_precision.delete;
2459 gt_trx_communicated_date.delete;
2460 gt_trx_currency_code.delete;
2461 gt_trx_id.delete;
2462 gt_trx_number.delete;
2463 gt_trx_date.delete;
2464 gt_trx_description.delete;
2465 gt_trx_type_description.delete;
2466 gt_trx_due_date.delete;
2467 gt_trx_line_description.delete;
2468 gt_trx_line_id.delete;
2469 gt_trx_line_number.delete;
2470 gt_trx_line_quantity.delete;
2471 gt_trx_line_amt.delete;
2472 gt_trx_line_type.delete;
2473 gt_trx_shipping_date.delete;
2474 gt_uom_code.delete;
2475 gt_related_doc_date.delete;
2476 gt_related_doc_entity_code.delete;
2477 gt_related_doc_event_cls_code.delete;
2478 gt_related_doc_number.delete;
2479 gt_related_doc_trx_id.delete;
2480 gt_applied_from_appl_id.delete;
2481 gt_applied_from_entity_code.delete;
2482 gt_applied_from_event_cls_code.delete;
2483 gt_applied_from_line_id.delete;
2484 gt_applied_from_trx_id.delete;
2485 gt_applied_from_trx_number.delete;
2486 gt_applied_to_appl_id.delete;
2487 gt_applied_to_entity_code.delete;
2488 gt_applied_to_event_cls_code.delete;
2489 gt_applied_to_trx_id.delete;
2490 gt_applied_to_trx_line_id.delete;
2491 gt_applied_to_trx_number.delete;
2492 gt_adjusted_doc_appl_id.delete;
2493 gt_adjusted_doc_date.delete;
2494 gt_adjusted_doc_entity_code.delete;
2495 gt_adjusted_doc_event_cls_code.delete;
2496 GT_ADJUSTED_DOC_NUMBER.delete;
2497 -- gt_country_of_supply.delete;
2498 gt_default_taxation_country.delete;
2499 gt_merchant_party_doc_num.delete;
2500 gt_merchant_party_name.delete;
2501 gt_merchant_party_reference.delete;
2502 gt_merchant_party_tax_reg_num.delete;
2503 gt_merchant_party_taxpayer_id.delete;
2504 gt_ref_doc_application_id.delete;
2505 gt_ref_doc_entity_code.delete;
2506 gt_ref_doc_event_cls_code.delete;
2507 gt_ref_doc_line_id.delete;
2508 gt_ref_doc_line_quantity.delete;
2509 gt_ref_doc_trx_id.delete;
2510 gt_start_expense_date.delete;
2511 gt_assessable_value.delete;
2512 gt_document_sub_type.delete;
2513 gt_line_intended_use.delete;
2514 gt_product_category.delete;
2515 gt_product_description.delete;
2516 gt_prod_fisc_classification.delete;
2517 gt_product_id.delete;
2518 gt_supplier_exchange_rate.delete;
2519 gt_supplier_tax_invoice_date.delete;
2520 gt_supplier_tax_invoice_num.delete;
2521 gt_tax_invoice_date.delete;
2522 gt_tax_invoice_number.delete;
2523 gt_trx_business_category.delete;
2524 gt_user_defined_fisc_class.delete;
2525 gt_nrec_tax_amt_tax_curr.delete;
2526 gt_offset_tax_rate_code.delete;
2527 gt_orig_rec_nrec_tax_amt.delete;
2528 gt_orig_tax_amt.delete;
2529 gt_orig_tax_amt_tax_curr.delete;
2530 gt_orig_taxable_amt.delete;
2531 gt_orig_taxable_amt_tax_curr.delete;
2532 gt_rec_tax_amt_tax_curr.delete;
2533 gt_recovery_rate_code.delete;
2534 gt_recovery_type_code.delete;
2538 gt_tax_amt_tax_curr.delete;
2535 gt_tax.delete;
2536 gt_tax_amt.delete;
2537 gt_tax_amt_funcl_curr.delete;
2539 gt_tax_apportionment_line_num.delete;
2540 gt_tax_currency_code.delete;
2541 gt_tax_date.delete;
2542 gt_tax_determine_date.delete;
2543 gt_tax_jurisdiction_code.delete;
2544 gt_tax_line_id.delete;
2545 gt_tax_line_number.delete;
2546 gt_tax_line_user_attribute1.delete;
2547 gt_tax_line_user_attribute2.delete;
2548 gt_tax_line_user_attribute3.delete;
2549 gt_tax_line_user_attribute4.delete;
2550 gt_tax_line_user_attribute5.delete;
2551 gt_tax_line_user_attribute6.delete;
2552 gt_tax_line_user_attribute7.delete;
2553 gt_tax_line_user_attribute8.delete;
2554 gt_tax_line_user_attribute9.delete;
2555 gt_tax_line_user_attribute10.delete;
2556 gt_tax_line_user_attribute11.delete;
2557 gt_tax_line_user_attribute12.delete;
2558 gt_tax_line_user_attribute13.delete;
2559 gt_tax_line_user_attribute14.delete;
2560 gt_tax_line_user_attribute15.delete;
2561 gt_tax_line_user_category.delete;
2562 gt_tax_rate.delete;
2563 gt_tax_rate_code.delete;
2564 gt_tax_rate_id.delete;
2565 gt_tax_recovery_rate.delete;
2566 gt_tax_regime_code.delete;
2567 gt_tax_status_id.delete;
2568 gt_tax_status_code.delete;
2569 gt_taxable_amt.delete;
2570 gt_taxable_amt_funcl_curr.delete;
2571 -- gt_billing_tp_name.delete;
2572 -- gt_billing_tp_number.delete;
2573 gt_bill_from_pty_tax_prof_id.delete;
2574 gt_bill_from_site_tax_prof_id.delete;
2575 -- gt_billing_tp_taxpayer_id.delete;
2576 gt_ship_to_site_tax_prof_id.delete;
2577 gt_ship_from_site_tax_prof_id.delete;
2578 gt_ship_to_pty_tax_prof_id.delete;
2579 gt_ship_from_pty_tax_prof_id.delete;
2580 GT_SHIPPING_TP_ADDRESS_ID.delete;
2581 GT_BILLING_TP_ADDRESS_ID.delete;
2582 GT_SHIPPING_TP_SITE_ID.delete;
2583 GT_BILLING_TP_SITE_ID.delete;
2584 GT_SHIPPING_TP_ID.delete;
2585 GT_BILLING_TRADING_PARTNER_ID.delete;
2586 gt_hq_estb_reg_number.delete;
2587 gt_tax_line_registration_num.delete;
2588 gt_legal_entity_tax_reg_num.delete;
2589 gt_own_hq_pty_site_prof_id.delete;
2590 gt_own_hq_pty_tax_prof_id.delete;
2591 gt_port_of_entry_code.delete;
2592 gt_registration_party_type.delete;
2593 gt_cancel_flag.delete;
2594 gt_historical_flag.delete;
2595 gt_mrc_tax_line_flag.delete;
2596 gt_offset_flag.delete;
2597 gt_reporting_only_flag.delete;
2598 gt_self_assessed_flag.delete;
2599 gt_tax_amt_included_flag.delete;
2600 gt_tax_only_flag.delete;
2601 gt_tax_recoverable_flag.delete;
2602 gt_posted_flag.delete;
2603 gt_reverse_flag.delete;
2604 gt_actg_source_id.delete;
2605 gt_entity_code.delete;
2606 gt_ledger_id.delete;
2607 GT_TAXABLE_ITEM_SOURCE_ID.DELETE;
2608 GT_TAX_RATE_VAT_TRX_TYPE_CODE.DELETE;
2609 GT_TAX_TYPE_CODE.DELETE;
2610 GT_TAX_RATE_CODE_NAME.DELETE;
2611 gt_tax_rate_code_description.delete;
2612
2613 IF (g_level_procedure >= g_current_runtime_level ) THEN
2614 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.init_gt_variables.END',
2615 'ZX_AP_EXTRACT_PKG: init_gt_variables(-)');
2616 END IF;
2617
2618 END init_gt_variables;
2619
2620
2621 /*===========================================================================+
2622 | PROCEDURE |
2623 | insert_tax_info |
2624 | DESCRIPTION |
2625 | This procedure inserts payables tax data into ZX_REP_TRX_DETAIL_T table|
2626 | |
2627 | SCOPE - Private |
2628 | |
2629 | NOTES |
2630 | |
2631 | MODIFICATION HISTORY |
2632 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
2633 | |
2634 +===========================================================================*/
2635 PROCEDURE insert_tax_info
2636 IS
2637 l_count NUMBER;
2638
2639 BEGIN
2640
2641 IF (g_level_procedure >= g_current_runtime_level ) THEN
2642 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.insert_tax_info.BEGIN',
2643 'ZX_AP_EXTRACT_PKG: insert_tax_info(+)');
2644 END IF;
2645
2646 l_count := GT_TRX_ID.COUNT;
2647
2648
2649 IF (g_level_procedure >= g_current_runtime_level ) THEN
2650 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.insert_tax_info',
2651 ' Record Count = ' ||to_char(GT_TRX_ID.COUNT));
2652 END IF;
2653
2654 FORALL i IN 1 .. l_count
2655 INSERT INTO ZX_REP_TRX_DETAIL_T(
2659 EVENT_CLASS_CODE,
2656 DETAIL_TAX_LINE_ID,
2657 APPLICATION_ID,
2658 EXTRACT_REPORT_LINE_NUMBER,
2660 INTERNAL_ORGANIZATION_ID,
2661 DOC_EVENT_STATUS,
2662 APPLICATION_DOC_STATUS,
2663 TRX_LINE_CLASS,
2664 DOC_SEQ_ID,
2665 DOC_SEQ_NAME,
2666 DOC_SEQ_VALUE,
2667 ESTABLISHMENT_ID,
2668 TRX_BATCH_SOURCE_ID,
2669 CURRENCY_CONVERSION_DATE,
2670 CURRENCY_CONVERSION_RATE,
2671 CURRENCY_CONVERSION_TYPE,
2672 MINIMUM_ACCOUNTABLE_UNIT,
2673 PRECISION,
2674 TRX_COMMUNICATED_DATE ,
2675 TRX_CURRENCY_CODE,
2676 TRX_ID,
2677 TRX_NUMBER,
2678 TRX_DATE,
2679 TRX_DESCRIPTION,
2680 TRX_TYPE_DESCRIPTION,
2681 TRX_TYPE_MNG,
2682 TAX_RATE_CODE_DESCRIPTION,
2683 TRX_DUE_DATE,
2684 TRX_LINE_DESCRIPTION,
2685 TRX_LINE_ID,
2686 TRX_LINE_NUMBER,
2687 TRX_LINE_QUANTITY,
2688 TRX_LINE_AMT,
2689 TRX_LINE_TYPE,
2690 TRX_SHIPPING_DATE,
2691 UOM_CODE,
2692 RELATED_DOC_DATE,
2693 RELATED_DOC_ENTITY_CODE,
2694 RELATED_DOC_EVENT_CLASS_CODE,
2695 RELATED_DOC_NUMBER,
2696 RELATED_DOC_TRX_ID,
2697 APPLIED_FROM_APPLICATION_ID,
2698 APPLIED_FROM_ENTITY_CODE,
2699 APPLIED_FROM_EVENT_CLASS_CODE,
2700 APPLIED_FROM_LINE_ID,
2701 APPLIED_FROM_TRX_ID,
2702 APPLIED_FROM_TRX_NUMBER,
2703 APPLIED_TO_APPLICATION_ID,
2704 APPLIED_TO_ENTITY_CODE,
2705 APPLIED_TO_EVENT_CLASS_CODE,
2706 APPLIED_TO_TRX_ID,
2707 APPLIED_TO_TRX_LINE_ID,
2708 APPLIED_TO_TRX_NUMBER ,
2709 ADJUSTED_DOC_APPLICATION_ID,
2710 ADJUSTED_DOC_DATE,
2711 ADJUSTED_DOC_ENTITY_CODE, --ok
2712 ADJUSTED_DOC_EVENT_CLASS_CODE,
2713 ADJUSTED_DOC_NUMBER ,
2714 -- COUNTRY_OF_SUPPLY,
2715 DEFAULT_TAXATION_COUNTRY,
2716 MERCHANT_PARTY_DOCUMENT_NUMBER,
2717 MERCHANT_PARTY_NAME,
2718 MERCHANT_PARTY_REFERENCE,
2719 MERCHANT_PARTY_TAX_REG_NUMBER,
2720 MERCHANT_PARTY_TAXPAYER_ID,
2721 REF_DOC_APPLICATION_ID,
2722 REF_DOC_ENTITY_CODE,
2723 REF_DOC_EVENT_CLASS_CODE,
2724 REF_DOC_LINE_ID,
2725 REF_DOC_LINE_QUANTITY,
2726 REF_DOC_TRX_ID, --ok
2727 START_EXPENSE_DATE,
2728 ASSESSABLE_VALUE,
2729 DOCUMENT_SUB_TYPE,
2730 LINE_INTENDED_USE,
2731 PRODUCT_CATEGORY,
2732 PRODUCT_DESCRIPTION,
2733 PRODUCT_FISC_CLASSIFICATION,
2734 PRODUCT_ID,
2735 SUPPLIER_EXCHANGE_RATE,
2736 SUPPLIER_TAX_INVOICE_DATE,
2737 SUPPLIER_TAX_INVOICE_NUMBER,
2738 TAX_INVOICE_DATE,
2739 TAX_INVOICE_NUMBER,
2740 TRX_BUSINESS_CATEGORY,
2741 USER_DEFINED_FISC_CLASS,
2742 NREC_TAX_AMT_TAX_CURR,
2743 OFFSET_TAX_RATE_CODE,
2744 ORIG_REC_NREC_TAX_AMT,
2745 ORIG_TAX_AMT,
2746 ORIG_TAX_AMT_TAX_CURR ,
2747 ORIG_TAXABLE_AMT,
2748 ORIG_TAXABLE_AMT_TAX_CURR,
2749 REC_TAX_AMT_TAX_CURR,
2750 RECOVERY_RATE_CODE,
2751 RECOVERY_TYPE_CODE, --ok
2752 TAX,
2753 TAX_AMT,
2754 TAX_AMT_FUNCL_CURR,
2755 TAX_AMT_TAX_CURR ,
2756 TAX_APPORTIONMENT_LINE_NUMBER,
2757 TAX_CURRENCY_CODE,
2758 TAX_DATE,
2759 TAX_DETERMINE_DATE,
2760 TAX_JURISDICTION_CODE,
2761 TAX_LINE_ID,
2762 TAX_LINE_NUMBER,
2763 TAX_LINE_USER_ATTRIBUTE1,
2764 TAX_LINE_USER_ATTRIBUTE2,
2765 TAX_LINE_USER_ATTRIBUTE3,
2766 TAX_LINE_USER_ATTRIBUTE4,
2767 TAX_LINE_USER_ATTRIBUTE5,
2768 TAX_LINE_USER_ATTRIBUTE6,
2769 TAX_LINE_USER_ATTRIBUTE7,
2770 TAX_LINE_USER_ATTRIBUTE8 ,
2771 TAX_LINE_USER_ATTRIBUTE9 ,
2772 TAX_LINE_USER_ATTRIBUTE10 ,
2773 TAX_LINE_USER_ATTRIBUTE11 ,
2774 TAX_LINE_USER_ATTRIBUTE12 ,
2775 TAX_LINE_USER_ATTRIBUTE13 ,
2776 TAX_LINE_USER_ATTRIBUTE14 ,
2777 TAX_LINE_USER_ATTRIBUTE15 ,
2778 TAX_LINE_USER_CATEGORY , --ok
2779 TAX_RATE,
2780 TAX_RATE_CODE,
2781 TAX_RATE_ID ,
2782 TAX_RECOVERY_RATE,
2783 TAX_REGIME_CODE,
2784 TAX_STATUS_ID,
2785 TAX_STATUS_CODE,
2786 TAXABLE_AMT,
2787 TAXABLE_AMT_FUNCL_CURR ,
2788 -- BILLING_TP_NAME,
2789 -- BILLING_TP_NUMBER,
2790 BILL_FROM_PARTY_TAX_PROF_ID,
2791 BILL_FROM_SITE_TAX_PROF_ID,
2792 --BILLING_TP_TAXPAYER_ID,
2793 SHIP_TO_SITE_TAX_PROF_ID ,
2794 SHIP_FROM_SITE_TAX_PROF_ID,
2795 SHIP_TO_PARTY_TAX_PROF_ID ,
2796 SHIP_FROM_PARTY_TAX_PROF_ID ,
2797 SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
2798 BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
2799 SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
2800 BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
2801 SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
2802 BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
2806 OWN_HQ_PARTY_SITE_PROF_ID,
2803 HQ_ESTB_REG_NUMBER ,
2804 TAX_LINE_REGISTRATION_NUMBER,
2805 LEGAL_ENTITY_TAX_REG_NUMBER,
2807 OWN_HQ_PARTY_TAX_PROF_ID,
2808 PORT_OF_ENTRY_CODE,
2809 REGISTRATION_PARTY_TYPE,
2810 CANCEL_FLAG,
2811 HISTORICAL_FLAG,
2812 MRC_TAX_LINE_FLAG,
2813 OFFSET_FLAG,
2814 REPORTING_ONLY_FLAG,
2815 SELF_ASSESSED_FLAG,
2816 TAX_AMT_INCLUDED_FLAG,
2817 TAX_ONLY_FLAG,
2818 TAX_RECOVERABLE_FLAG,
2819 CREATED_BY ,
2820 CREATION_DATE ,
2821 LAST_UPDATED_BY,
2822 LAST_UPDATE_DATE,
2823 LAST_UPDATE_LOGIN,
2824 REQUEST_ID,
2825 EXTRACT_SOURCE_LEDGER,
2826 POSTED_FLAG,
2827 REVERSE_FLAG,
2828 ACTG_SOURCE_ID,
2829 TAXABLE_ITEM_SOURCE_ID,
2830 ENTITY_CODE,
2831 LEDGER_ID,
2832 TAX_RATE_VAT_TRX_TYPE_CODE,
2833 TAX_TYPE_CODE,
2834 TAX_RATE_CODE_NAME,
2835 trx_level_type, --Bug 5393051
2836 unit_price,
2837 gl_date) --Bug 5523095
2838 VALUES(
2839 ZX_REP_TRX_DETAIL_T_S.NEXTVAL,
2840 gt_application_id(i),
2841 gt_extract_rep_line_num(i),
2842 gt_event_class_code(i),
2843 gt_internal_organization_id(i),
2844 gt_doc_event_status(i),
2845 gt_application_doc_status(i),
2846 gt_line_class(i),
2847 gt_doc_seq_id(i),
2848 gt_doc_seq_name(i),
2849 gt_doc_seq_value(i),
2850 gt_establishment_id(i),
2851 gt_batch_source_id(i),
2852 gt_currency_conversion_date(i),
2853 gt_currency_conversion_rate(i),
2854 gt_currency_conversion_type(i),
2855 gt_minimum_accountable_unit(i),
2856 gt_precision(i),
2857 gt_trx_communicated_date(i),
2858 gt_trx_currency_code(i),
2859 gt_trx_id(i),
2860 gt_trx_number(i),
2861 gt_trx_date(i),
2862 gt_trx_description(i),
2863 gt_trx_type_description(i),
2864 gt_trx_type_description(i),
2865 gt_tax_rate_code_description(i),
2866 gt_trx_due_date(i),
2867 gt_trx_line_description(i),
2868 gt_trx_line_id(i),
2869 gt_trx_line_number(i),
2870 gt_trx_line_quantity(i),
2871 gt_trx_line_amt(i),
2872 gt_trx_line_type(i),
2873 gt_trx_shipping_date(i),
2874 gt_uom_code(i),
2875 gt_related_doc_date(i),
2876 gt_related_doc_entity_code(i),
2877 gt_related_doc_event_cls_code(i),
2878 gt_related_doc_number(i),
2879 gt_related_doc_trx_id(i),
2880 gt_applied_from_appl_id(i),
2881 gt_applied_from_entity_code(i),
2882 gt_applied_from_event_cls_code(i),
2883 gt_applied_from_line_id(i),
2884 gt_applied_from_trx_id(i),
2885 gt_applied_from_trx_number(i),
2886 gt_applied_to_appl_id(i),
2887 gt_applied_to_entity_code(i),
2888 gt_applied_to_event_cls_code(i),
2889 gt_applied_to_trx_id(i),
2890 gt_applied_to_trx_line_id(i),
2891 gt_applied_to_trx_number(i),
2892 gt_adjusted_doc_appl_id(i),
2893 gt_adjusted_doc_date(i),
2894 gt_adjusted_doc_entity_code(i),
2895 gt_adjusted_doc_event_cls_code(i),
2896 GT_ADJUSTED_DOC_NUMBER(i),
2897 -- gt_country_of_supply(i),
2898 gt_default_taxation_country(i),
2899 gt_merchant_party_doc_num(i),
2900 gt_merchant_party_name(i),
2901 gt_merchant_party_reference(i),
2902 gt_merchant_party_tax_reg_num(i),
2903 gt_merchant_party_taxpayer_id(i),
2904 gt_ref_doc_application_id(i),
2905 gt_ref_doc_entity_code(i),
2906 gt_ref_doc_event_cls_code(i),
2907 gt_ref_doc_line_id(i),
2908 gt_ref_doc_line_quantity(i),
2909 gt_ref_doc_trx_id(i),
2910 gt_start_expense_date(i),
2911 gt_assessable_value(i),
2912 gt_document_sub_type(i),
2913 gt_line_intended_use(i),
2914 gt_product_category(i),
2915 gt_product_description(i),
2916 gt_prod_fisc_classification(i),
2917 gt_product_id(i),
2918 gt_supplier_exchange_rate(i),
2919 gt_supplier_tax_invoice_date(i),
2920 gt_supplier_tax_invoice_num(i),
2921 gt_tax_invoice_date(i),
2922 gt_tax_invoice_number(i),
2923 gt_trx_business_category(i),
2924 gt_user_defined_fisc_class(i),
2925 gt_nrec_tax_amt_tax_curr(i),
2926 gt_offset_tax_rate_code(i),
2927 gt_orig_rec_nrec_tax_amt(i),
2928 gt_orig_tax_amt(i),
2929 gt_orig_tax_amt_tax_curr(i),
2930 gt_orig_taxable_amt(i),
2931 gt_orig_taxable_amt_tax_curr(i),
2932 gt_rec_tax_amt_tax_curr(i),
2933 gt_recovery_rate_code(i),
2934 gt_recovery_type_code(i),
2935 gt_tax(i),
2936 gt_tax_amt(i),
2937 gt_tax_amt_funcl_curr(i),
2938 gt_tax_amt_tax_curr(i),
2939 gt_tax_apportionment_line_num(i),
2940 gt_tax_currency_code(i),
2941 gt_tax_date(i),
2942 gt_tax_determine_date(i),
2943 gt_tax_jurisdiction_code(i),
2944 gt_tax_line_id(i),
2945 gt_tax_line_number(i),
2946 gt_tax_line_user_attribute1(i),
2947 gt_tax_line_user_attribute2(i),
2951 gt_tax_line_user_attribute6(i),
2948 gt_tax_line_user_attribute3(i),
2949 gt_tax_line_user_attribute4(i),
2950 gt_tax_line_user_attribute5(i),
2952 gt_tax_line_user_attribute7(i),
2953 gt_tax_line_user_attribute8(i),
2954 gt_tax_line_user_attribute9(i),
2955 gt_tax_line_user_attribute10(i),
2956 gt_tax_line_user_attribute11(i),
2957 gt_tax_line_user_attribute12(i),
2958 gt_tax_line_user_attribute13(i),
2959 gt_tax_line_user_attribute14(i),
2960 gt_tax_line_user_attribute15(i),
2961 gt_tax_line_user_category(i),
2962 gt_tax_rate(i),
2963 gt_tax_rate_code(i),
2964 gt_tax_rate_id(i),
2965 gt_tax_recovery_rate(i),
2966 gt_tax_regime_code(i),
2967 gt_tax_status_id(i),
2968 gt_tax_status_code(i),
2969 gt_taxable_amt(i),
2970 gt_taxable_amt_funcl_curr(i),
2971 -- gt_billing_tp_name(i),
2972 -- gt_billing_tp_number(i),
2973 gt_bill_from_pty_tax_prof_id(i),
2974 gt_bill_from_site_tax_prof_id(i),
2975 -- gt_billing_tp_taxpayer_id(i),
2976 gt_ship_to_site_tax_prof_id(i) ,
2977 gt_ship_from_site_tax_prof_id(i),
2978 gt_ship_to_pty_tax_prof_id(i),
2979 gt_ship_from_pty_tax_prof_id(i),
2980 GT_SHIPPING_TP_ADDRESS_ID(i),
2981 GT_BILLING_TP_ADDRESS_ID(i),
2982 GT_SHIPPING_TP_SITE_ID(i),
2983 GT_BILLING_TP_SITE_ID(i),
2984 GT_SHIPPING_TP_ID(i),
2985 GT_BILLING_TRADING_PARTNER_ID(i),
2986 gt_hq_estb_reg_number(i),
2987 gt_tax_line_registration_num(i),
2988 gt_legal_entity_tax_reg_num(i),
2989 gt_own_hq_pty_site_prof_id(i),
2990 gt_own_hq_pty_tax_prof_id(i),
2991 gt_port_of_entry_code(i),
2992 gt_registration_party_type(i),
2993 gt_cancel_flag(i),
2994 gt_historical_flag(i),
2995 gt_mrc_tax_line_flag(i),
2996 gt_offset_flag(i),
2997 gt_reporting_only_flag(i),
2998 gt_self_assessed_flag(i),
2999 gt_tax_amt_included_flag(i),
3000 gt_tax_only_flag(i),
3001 gt_tax_recoverable_flag(i),
3002 g_created_by ,
3003 g_creation_date ,
3004 g_last_updated_by,
3005 g_last_update_date,
3006 g_last_update_login,
3007 g_request_id,
3008 'AP',
3009 gt_posted_flag(i),
3010 gt_reverse_flag(i),
3011 gt_actg_source_id(i),
3012 gt_taxable_item_source_id(i),
3013 gt_entity_code(i),
3014 gt_ledger_id(i),
3015 GT_TAX_RATE_VAT_TRX_TYPE_CODE(i),
3016 GT_TAX_TYPE_CODE(i),
3017 GT_TAX_RATE_CODE_NAME(i),
3018 gt_trx_level_type(i), --Bug 5393051
3019 gt_unit_price_tbl(i), -- Bug 5439099
3020 gt_gl_date(i) --Bug 5523095
3021 )
3022 RETURNING detail_tax_line_id bulk collect into GT_DETAIL_TAX_LINE_ID ;
3023
3024 IF (g_level_procedure >= g_current_runtime_level ) THEN
3025 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.insert_tax_info',
3026 'Number of Tax Lines successfully inserted = '||TO_CHAR(l_count));
3027
3028 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.INSERT_TAX_INFO.END',
3029 'ZX_AP_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
3030 END IF;
3031
3032 EXCEPTION
3033 WHEN OTHERS THEN
3034 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3035 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3036 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3037 FND_MSG_PUB.Add;
3038 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3039 FND_LOG.STRING(g_level_unexpected,
3040 'ZX.TRL.ZX_AP_EXTRACT_PKG.INSERT_TAX_INFO',
3041 g_error_buffer);
3042 END IF;
3043
3044 g_retcode := 2;
3045
3046 END insert_tax_info;
3047
3048
3049 /*===========================================================================+
3050 | FUNCTION |
3051 | assign_global_parameters |
3052 | |
3053 | DESCRIPTION |
3054 | Assign the global variable to the the output parameters. |
3055 | This procedure is used by AP procedures to get the global |
3056 | variable values from Main package. |
3057 | |
3058 | SCOPE - Public |
3059 | |
3060 | NOTES |
3061 | |
3062 | MODIFICATION HISTORY |
3063 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
3064 | |
3065 +===========================================================================*/
3066
3067 PROCEDURE assign_global_parameters (
3068 p_trl_global_variables_rec IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
3069 IS
3070
3071 BEGIN
3075 'ZX_AP_EXTRACT_PKG: assign_global_parametERS(+)');
3072
3073 IF (g_level_procedure >= g_current_runtime_level ) THEN
3074 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.assign_global_parameTERS.BEGIN',
3076 END IF;
3077
3078 g_reporting_level := p_trl_global_variables_rec.reporting_level;
3079 g_reporting_context := p_trl_global_variables_rec.reporting_context;
3080 -- apai g_legal_entity_level := p_trl_global_variables_rec.legal_entity_level;
3081 g_legal_entity_id := p_trl_global_variables_rec.legal_entity_id;
3082 g_summary_level := p_trl_global_variables_rec.summary_level;
3083 g_ledger_id := p_trl_global_variables_rec.ledger_id;
3084 g_register_type := p_trl_global_variables_rec.register_type;
3085 g_product := p_trl_global_variables_rec.product;
3086 g_matrix_report := p_trl_global_variables_rec.matrix_report;
3087 g_currency_code_low := p_trl_global_variables_rec.currency_code_low;
3088 g_currency_code_high := p_trl_global_variables_rec.currency_code_high;
3089 g_include_ap_std_trx_class := p_trl_global_variables_rec.include_ap_std_trx_class;
3090 g_include_ap_dm_trx_class := p_trl_global_variables_rec.include_ap_dm_trx_class;
3091 g_include_ap_cm_trx_class := p_trl_global_variables_rec.include_ap_cm_trx_class;
3092 g_include_ap_prep_trx_class := p_trl_global_variables_rec.include_ap_prep_trx_class;
3093 g_include_ap_mix_trx_class := p_trl_global_variables_rec.include_ap_mix_trx_class;
3094 g_include_ap_exp_trx_class := p_trl_global_variables_rec.include_ap_exp_trx_class;
3095 g_include_ap_int_trx_class := p_trl_global_variables_rec.include_ap_int_trx_class;
3096 g_trx_number_low := p_trl_global_variables_rec.trx_number_low;
3097 g_trx_number_high := p_trl_global_variables_rec.trx_number_high;
3098 g_ar_trx_printing_status := p_trl_global_variables_rec.ar_trx_printing_status;
3099 g_ar_exemption_status := p_trl_global_variables_rec.ar_exemption_status;
3100 g_gl_date_low := p_trl_global_variables_rec.gl_date_low;
3101 g_gl_date_high := p_trl_global_variables_rec.gl_date_high;
3102 g_trx_date_low := p_trl_global_variables_rec.trx_date_low;
3103 g_trx_date_high := p_trl_global_variables_rec.trx_date_high;
3104 g_trx_date_low_ln := p_trl_global_variables_rec.trx_date_low;
3105 g_trx_date_high_ln := p_trl_global_variables_rec.trx_date_high;
3106 g_gl_period_name_low := p_trl_global_variables_rec.gl_period_name_low;
3107 g_gl_period_name_high := p_trl_global_variables_rec.gl_period_name_high;
3108 g_trx_date_period_name_low := p_trl_global_variables_rec.trx_date_period_name_low;
3109 g_trx_date_period_name_high := p_trl_global_variables_rec.trx_date_period_name_high;
3110 g_tax_jurisdiction_code := p_trl_global_variables_rec.tax_jurisdiction_code;
3111 g_first_party_tax_reg_num := p_trl_global_variables_rec.first_party_tax_reg_num;
3112 g_tax_regime_code := p_trl_global_variables_rec.tax_regime_code;
3113 g_tax := p_trl_global_variables_rec.tax;
3114 g_tax_status_code := p_trl_global_variables_rec.tax_status_code;
3115 g_tax_rate_code_low := p_trl_global_variables_rec.tax_rate_code_low;
3116 g_tax_rate_code_high := p_trl_global_variables_rec.tax_rate_code_high;
3117 g_tax_type_code_low := p_trl_global_variables_rec.tax_type_code_low;
3118 g_tax_type_code_high := p_trl_global_variables_rec.tax_type_code_high;
3119 g_document_sub_type := p_trl_global_variables_rec.document_sub_type;
3120 g_trx_business_category := p_trl_global_variables_rec.trx_business_category;
3121 g_tax_invoice_date_low := p_trl_global_variables_rec.tax_invoice_date_low;
3122 g_tax_invoice_date_high := p_trl_global_variables_rec.tax_invoice_date_high;
3123 g_posting_status := p_trl_global_variables_rec.posting_status;
3124 g_extract_accted_tax_lines := p_trl_global_variables_rec.extract_accted_tax_lines;
3125 g_include_accounting_segments := p_trl_global_variables_rec.include_accounting_segments;
3126 g_balancing_segment_low := p_trl_global_variables_rec.balancing_segment_low;
3127 g_balancing_segment_high := p_trl_global_variables_rec.balancing_segment_high;
3128 g_include_discounts := p_trl_global_variables_rec.include_discounts;
3129 g_extract_starting_line_num := p_trl_global_variables_rec.extract_starting_line_num;
3130 g_request_id := p_trl_global_variables_rec.request_id;
3131 g_report_name := p_trl_global_variables_rec.report_name;
3132 g_vat_transaction_type_code := p_trl_global_variables_rec.vat_transaction_type_code;
3133 g_include_fully_nr_tax_flag := p_trl_global_variables_rec.include_fully_nr_tax_flag;
3134 g_municipal_tax_type_code_low := p_trl_global_variables_rec.municipal_tax_type_code_low;
3135 g_municipal_tax_type_code_high := p_trl_global_variables_rec.municipal_tax_type_code_high;
3136 g_prov_tax_type_code_low := p_trl_global_variables_rec.prov_tax_type_code_low;
3137 g_prov_tax_type_code_high := p_trl_global_variables_rec.prov_tax_type_code_high;
3138 g_excise_tax_type_code_low := p_trl_global_variables_rec.excise_tax_type_code_low;
3139 g_excise_tax_type_code_high := p_trl_global_variables_rec.excise_tax_type_code_high;
3140 g_non_taxable_tax_type_code := p_trl_global_variables_rec.non_taxable_tax_type_code;
3141 g_per_tax_type_code_low := p_trl_global_variables_rec.per_tax_type_code_low;
3142 g_per_tax_type_code_high := p_trl_global_variables_rec.per_tax_type_code_high;
3143 g_fed_per_tax_type_code_low := p_trl_global_variables_rec.fed_per_tax_type_code_low;
3144 g_fed_per_tax_type_code_high := p_trl_global_variables_rec.fed_per_tax_type_code_high;
3145 g_vat_tax_type_code := p_trl_global_variables_rec.vat_tax_type_code;
3146 g_excise_tax := p_trl_global_variables_rec.excise_tax;
3147 g_vat_additional_tax := p_trl_global_variables_rec.vat_additional_tax;
3148 g_vat_non_taxable_tax := p_trl_global_variables_rec.vat_non_taxable_tax;
3149 g_vat_not_tax := p_trl_global_variables_rec.vat_not_tax;
3153 g_excluding_trx_letter := p_trl_global_variables_rec.excluding_trx_letter;
3150 g_vat_perception_tax := p_trl_global_variables_rec.vat_perception_tax;
3151 g_vat_tax := p_trl_global_variables_rec.vat_tax;
3152 g_inc_self_wd_tax := p_trl_global_variables_rec.inc_self_wd_tax;
3154 g_trx_letter_low := p_trl_global_variables_rec.trx_letter_low;
3155 g_trx_letter_high := p_trl_global_variables_rec.trx_letter_high;
3156 g_include_referenced_source := p_trl_global_variables_rec.include_referenced_source;
3157 g_party_name := p_trl_global_variables_rec.party_name;
3158 g_adjusted_doc_from := p_trl_global_variables_rec.adjusted_doc_from;
3159 g_adjusted_doc_to := p_trl_global_variables_rec.adjusted_doc_to;
3160 g_batch_name := p_trl_global_variables_rec.batch_name;
3161 g_batch_date_low := p_trl_global_variables_rec.batch_date_low;
3162 g_batch_date_high := p_trl_global_variables_rec.batch_date_high;
3163 g_batch_source_id := p_trl_global_variables_rec.batch_source_id;
3164 g_adjusted_doc_from := p_trl_global_variables_rec.adjusted_doc_from;
3165 g_adjusted_doc_to := p_trl_global_variables_rec.adjusted_doc_to;
3166 g_standard_vat_tax_rate := p_trl_global_variables_rec.standard_vat_tax_rate;
3167 g_municipal_tax := p_trl_global_variables_rec.municipal_tax;
3168 g_provincial_tax := p_trl_global_variables_rec.provincial_tax;
3169 g_tax_account_low := p_trl_global_variables_rec.tax_account_low;
3170 g_tax_account_high := p_trl_global_variables_rec.tax_account_high;
3171 g_exp_cert_date_from := p_trl_global_variables_rec.exp_cert_date_from;
3172 g_exp_cert_date_to := p_trl_global_variables_rec.exp_cert_date_to;
3173 g_exp_method := p_trl_global_variables_rec.exp_method;
3174 g_print_company_info := p_trl_global_variables_rec.print_company_info;
3175 g_reprint := p_trl_global_variables_rec.reprint;
3176 g_errbuf := p_trl_global_variables_rec.errbuf;
3177 g_retcode := p_trl_global_variables_rec.retcode;
3178 g_accounting_status := p_trl_global_variables_rec.accounting_status;
3179 g_chart_of_accounts_id := p_trl_global_variables_rec.chart_of_accounts_id;
3180 g_reported_status := p_trl_global_variables_rec.reported_status;
3181 g_gl_or_trx_date_filter := p_trl_global_variables_rec.GL_OR_TRX_DATE_FILTER ; --Bug 5396444
3182
3183
3184
3185 IF (g_level_procedure >= g_current_runtime_level ) THEN
3186 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.assign_global_parameters.END',
3187 'ZX_AP_EXTRACT_PKG: assign_global_parameters(-)');
3188 END IF;
3189
3190 END assign_global_parameters;
3191
3192 /*===========================================================================+
3193 | PROCEDURE |
3194 | filter_validated |
3195 | |
3196 | DESCRIPTION |
3197 | This procedure deletes unwanted records from AP tax data extract. |
3198 | |
3199 | SCOPE - Private |
3200 | |
3201 | NOTES |
3202 | |
3203 | MODIFICATION HISTORY |
3204 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
3205 +===========================================================================*/
3206
3207
3208 PROCEDURE FILTER_VALIDATED IS
3209
3210 BEGIN
3211
3212 IF (g_level_procedure >= g_current_runtime_level ) THEN
3213 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FILTER_VALIDATED.BEGIN',
3214 'ZX_AP_EXTRACT_PKG: FILTER_VALIDATED(+)');
3215 END IF;
3216
3217 IF g_accounting_status <> 'ACCOUNTED' THEN
3218
3219 --This statment will delete invoices having below status:
3220 --1.Never Validated.
3221 --2.Un Validated.
3222 --3.Needs Revalidation and don't have any hold.
3223 --4.Needs Revalidation and have atleast one un-released accounting not allowed hold.
3224
3225 DELETE
3226 FROM ZX_REP_TRX_DETAIL_T DTL
3227 WHERE REQUEST_ID = g_request_id
3228 AND DTL.EXTRACT_SOURCE_LEDGER = 'AP'
3229 AND EXISTS
3230 (SELECT 1
3231 FROM AP_INVOICES_ALL AI
3232 WHERE AI.INVOICE_ID = DTL.TRX_ID
3233 AND DECODE(AP_INVOICES_PKG.GET_APPROVAL_STATUS(AI.INVOICE_ID,AI.INVOICE_AMOUNT,AI.PAYMENT_STATUS_FLAG,AI.INVOICE_TYPE_LOOKUP_CODE),
3234 'NEVER APPROVED','Y',
3235 'UNAPPROVED','Y',
3236 'NEEDS REAPPROVAL',DECODE((SELECT COUNT(*)
3237 FROM DUAL
3238 WHERE NOT EXISTS (SELECT 1
3239 FROM AP_HOLDS AH
3240 WHERE AI.INVOICE_ID = AH.INVOICE_ID)
3241 OR
3242 EXISTS
3243 (SELECT 1
3244 FROM AP_HOLDS AH1, AP_HOLD_CODES AHC
3245 WHERE AH1.RELEASE_LOOKUP_CODE IS NULL
3246 AND AI.INVOICE_ID = AH1.INVOICE_ID
3247 AND AH1.HOLD_LOOKUP_CODE = AHC.HOLD_LOOKUP_CODE
3248 AND NVL(AHC.POSTABLE_FLAG,'N') = 'N')),
3249 0,'N',
3250 'Y'),
3251 'N') = 'Y');
3252 END IF;
3253
3254 IF ( g_level_statement>= g_current_runtime_level ) THEN
3255 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_EXTRACT_PKG',
3256 ' Deleted Count : Filter_Validated : '||to_char(sql%ROWCOUNT) ); --Bug 5347188
3257 END IF;
3258
3259 IF (g_level_procedure >= g_current_runtime_level ) THEN
3260 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_EXTRACT_PKG.FILTER_VALIDATED.BEGIN',
3261 'ZX_AP_EXTRACT_PKG: FILTER_VALIDATED(-)');
3262 END IF;
3263
3264 EXCEPTION
3265 WHEN OTHERS THEN
3266 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3267 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3268 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','filter_validated- '|| g_error_buffer);
3269 FND_MSG_PUB.Add;
3270 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3271 FND_LOG.STRING(g_level_unexpected,
3272 'ZX.TRL.ZX_AP_EXTRACT_PKG.filter_validated',
3273 g_error_buffer);
3274 END IF;
3275 g_retcode := 2;
3276
3277 END filter_validated;
3278
3279 END ZX_AP_EXTRACT_PKG;