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