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