[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_SELECTION_PKG
Source
1 PACKAGE BODY JG_ZZ_VAT_SELECTION_PKG AS
2 /* $Header: jgzzvspb.pls 120.42.12020000.4 2013/02/28 11:08:36 sakekuma ship $ */
3
4 -----------------------------------------
5 --Public Variable Declarations
6 -----------------------------------------
7
8 l_version_info VARCHAR2(90) := NULL;
9 C_LINES_PER_COMMIT CONSTANT NUMBER := 1000;
10
11 g_current_runtime_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
12 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
15 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16 g_level_error CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
17 g_level_exception CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
18 g_error_buffer VARCHAR2(100);
19 g_debug_flag VARCHAR2(1);
20 g_pkg_name CONSTANT VARCHAR2(30) := 'JG_ZZ_VAT_SELECTION_PKG';
21 g_module_name CONSTANT VARCHAR2(30) := 'JG.EMEA_VAT.SELECTION_PROCESS';
22
23
24 g_created_by number(15);
25 g_creation_date date;
26 g_last_updated_by number(15);
27 g_last_update_date date;
28 g_last_update_login number(15);
29 g_conc_request_id number(15);
30 g_prog_appl_id number(15);
31 g_conc_program_id number(15);
32 g_conc_login_id number(15);
33
34 g_rep_status_id_ap jg_zz_vat_rep_status.reporting_status_id%type;
35 g_rep_status_id_ar jg_zz_vat_rep_status.reporting_status_id%type;
36 g_rep_status_id_gl jg_zz_vat_rep_status.reporting_status_id%type;
37 g_selection_process_id jg_zz_vat_rep_status.selection_process_id%type;
38
39 -- Declare table type global variables
40 gt_vat_transaction_id JG_ZZ_VAT_SELECTION_PKG.vat_transaction_id_tbl;
41 gt_reporting_status_id JG_ZZ_VAT_SELECTION_PKG.reporting_status_id_tbl;
42 gt_rep_entity_id JG_ZZ_VAT_SELECTION_PKG.rep_entity_id_tbl;
43 gt_rep_context_entity_name JG_ZZ_VAT_SELECTION_PKG.rep_context_entity_name_tbl;
44 gt_rep_context_entity_loc_id JG_ZZ_VAT_SELECTION_PKG.rep_context_entity_loc_id_tbl;
45 gt_taxpayer_id JG_ZZ_VAT_SELECTION_PKG.taxpayer_id_tbl;
46 gt_org_information2 JG_ZZ_VAT_SELECTION_PKG.org_information2_tbl;
47 gt_legal_authority_name JG_ZZ_VAT_SELECTION_PKG.legal_authority_name_tbl;
48 gt_legal_auth_address_line2 JG_ZZ_VAT_SELECTION_PKG.legal_auth_address_line2_tbl;
49 gt_legal_auth_address_line3 JG_ZZ_VAT_SELECTION_PKG.legal_auth_address_line3_tbl;
50 gt_legal_auth_city JG_ZZ_VAT_SELECTION_PKG.legal_auth_city_tbl;
51 gt_legal_contact_party_name JG_ZZ_VAT_SELECTION_PKG.legal_contact_party_name_tbl;
52 gt_activity_code JG_ZZ_VAT_SELECTION_PKG.activity_code_tbl;
53 gt_ledger_id JG_ZZ_VAT_SELECTION_PKG.ledger_id_tbl;
54 gt_ledger_name JG_ZZ_VAT_SELECTION_PKG.ledger_name_tbl;
55 gt_chart_of_accounts_id JG_ZZ_VAT_SELECTION_PKG.chart_of_accounts_id_tbl;
56 gt_extract_source_ledger JG_ZZ_VAT_SELECTION_PKG.extract_source_ledger_tbl;
57 gt_establishment_id JG_ZZ_VAT_SELECTION_PKG.establishment_id_tbl;
58 gt_internal_organization_id JG_ZZ_VAT_SELECTION_PKG.internal_organization_id_tbl;
59 gt_application_id JG_ZZ_VAT_SELECTION_PKG.application_id_tbl;
60 gt_entity_code JG_ZZ_VAT_SELECTION_PKG.entity_code_tbl;
61 gt_event_class_code JG_ZZ_VAT_SELECTION_PKG.event_class_code_tbl;
62 gt_trx_id JG_ZZ_VAT_SELECTION_PKG.trx_id_tbl;
63 gt_trx_number JG_ZZ_VAT_SELECTION_PKG.trx_number_tbl;
64 gt_trx_description JG_ZZ_VAT_SELECTION_PKG.trx_description_tbl;
65 gt_trx_currency_code JG_ZZ_VAT_SELECTION_PKG.trx_currency_code_tbl;
66 gt_trx_type_id JG_ZZ_VAT_SELECTION_PKG.trx_type_id_tbl;
67 gt_trx_type_mng JG_ZZ_VAT_SELECTION_PKG.trx_type_mng_tbl;
68 gt_trx_line_id JG_ZZ_VAT_SELECTION_PKG.trx_line_id_tbl;
69 gt_trx_line_number JG_ZZ_VAT_SELECTION_PKG.trx_line_number_tbl;
70 gt_trx_line_description JG_ZZ_VAT_SELECTION_PKG.trx_line_description_tbl;
71 gt_trx_level_type JG_ZZ_VAT_SELECTION_PKG.trx_level_type_tbl;
72 gt_trx_line_type JG_ZZ_VAT_SELECTION_PKG.trx_line_type_tbl;
73 gt_trx_line_class JG_ZZ_VAT_SELECTION_PKG.trx_line_class_tbl;
74 gt_trx_class_mng JG_ZZ_VAT_SELECTION_PKG.trx_class_mng_tbl;
75 gt_trx_date JG_ZZ_VAT_SELECTION_PKG.trx_date_tbl;
76 gt_trx_due_date JG_ZZ_VAT_SELECTION_PKG.trx_due_date_tbl;
77 gt_trx_communicated_date JG_ZZ_VAT_SELECTION_PKG.trx_communicated_date_tbl;
78 gt_product_id JG_ZZ_VAT_SELECTION_PKG.product_id_tbl;
79 gt_functional_currency_code JG_ZZ_VAT_SELECTION_PKG.functional_currency_code_tbl;
80 gt_currency_conversion_type JG_ZZ_VAT_SELECTION_PKG.currency_conversion_type_tbl;
81 gt_currency_conversion_date JG_ZZ_VAT_SELECTION_PKG.currency_conversion_date_tbl;
82 gt_currency_conversion_rate JG_ZZ_VAT_SELECTION_PKG.currency_conversion_rate_tbl;
83 gt_territory_short_name JG_ZZ_VAT_SELECTION_PKG.territory_short_name_tbl;
84 gt_doc_seq_id JG_ZZ_VAT_SELECTION_PKG.doc_seq_id_tbl;
85 gt_doc_seq_name JG_ZZ_VAT_SELECTION_PKG.doc_seq_name_tbl;
86 gt_doc_seq_value JG_ZZ_VAT_SELECTION_PKG.doc_seq_value_tbl;
87 gt_trx_line_amt JG_ZZ_VAT_SELECTION_PKG.trx_line_amt_tbl;
88 gt_receipt_class_id JG_ZZ_VAT_SELECTION_PKG.receipt_class_id_tbl;
89 gt_applied_from_appl_id JG_ZZ_VAT_SELECTION_PKG.applied_from_appl_id_tbl;
90 gt_applied_from_entity_code JG_ZZ_VAT_SELECTION_PKG.applied_from_entity_code_tbl;
91 gt_applied_from_event_cls_cd JG_ZZ_VAT_SELECTION_PKG.applied_from_event_cls_cd_tbl;
92 gt_applied_from_trx_id JG_ZZ_VAT_SELECTION_PKG.applied_from_trx_id_tbl;
93 gt_applied_from_line_id JG_ZZ_VAT_SELECTION_PKG.applied_from_line_id_tbl;
94 gt_applied_from_trx_number JG_ZZ_VAT_SELECTION_PKG.applied_from_trx_number_tbl;
95 gt_adjusted_doc_appl_id JG_ZZ_VAT_SELECTION_PKG.adjusted_doc_appl_id_tbl;
96 gt_adjusted_doc_entity_code JG_ZZ_VAT_SELECTION_PKG.adjusted_doc_entity_code_tbl;
97 gt_adjusted_doc_event_cls_cd JG_ZZ_VAT_SELECTION_PKG.adjusted_doc_event_cls_cd_tbl;
98 gt_adjusted_doc_trx_id JG_ZZ_VAT_SELECTION_PKG.adjusted_doc_trx_id_tbl;
99 gt_adjusted_doc_number JG_ZZ_VAT_SELECTION_PKG.adjusted_doc_number_tbl;
100 gt_adjusted_doc_date JG_ZZ_VAT_SELECTION_PKG.adjusted_doc_date_tbl;
101 gt_applied_to_application_id JG_ZZ_VAT_SELECTION_PKG.applied_to_application_id_tbl;
102 gt_applied_to_entity_code JG_ZZ_VAT_SELECTION_PKG.applied_to_entity_code_tbl;
103 gt_applied_to_event_cls_code JG_ZZ_VAT_SELECTION_PKG.applied_to_event_cls_code_tbl;
104 gt_applied_to_trx_id JG_ZZ_VAT_SELECTION_PKG.applied_to_trx_id_tbl;
105 gt_applied_to_trx_line_id JG_ZZ_VAT_SELECTION_PKG.applied_to_trx_line_id_tbl;
106 gt_applied_to_trx_number JG_ZZ_VAT_SELECTION_PKG.applied_to_trx_number_tbl;
107 gt_ref_doc_application_id JG_ZZ_VAT_SELECTION_PKG.ref_doc_application_id_tbl;
108 gt_ref_doc_entity_code JG_ZZ_VAT_SELECTION_PKG.ref_doc_entity_code_tbl;
109 gt_ref_doc_event_class_code JG_ZZ_VAT_SELECTION_PKG.ref_doc_event_class_code_tbl;
110 gt_ref_doc_trx_id JG_ZZ_VAT_SELECTION_PKG.ref_doc_trx_id_tbl;
111 gt_ref_doc_line_id JG_ZZ_VAT_SELECTION_PKG.ref_doc_line_id_tbl;
112 gt_merchant_party_doc_num JG_ZZ_VAT_SELECTION_PKG.merchant_party_doc_num_tbl;
113 gt_merchant_party_name JG_ZZ_VAT_SELECTION_PKG.merchant_party_name_tbl;
114 gt_merchant_party_reference JG_ZZ_VAT_SELECTION_PKG.merchant_party_reference_tbl;
115 gt_merchant_party_tax_reg_num JG_ZZ_VAT_SELECTION_PKG.merchant_party_tax_reg_num_tbl;
116 gt_merchant_party_taxpayer_id JG_ZZ_VAT_SELECTION_PKG.merchant_party_taxpayer_id_tbl;
117 gt_start_expense_date JG_ZZ_VAT_SELECTION_PKG.start_expense_date_tbl;
118 gt_taxable_line_source_table JG_ZZ_VAT_SELECTION_PKG.taxable_line_source_table_tbl;
119 gt_tax_line_id JG_ZZ_VAT_SELECTION_PKG.tax_line_id_tbl;
120 gt_tax_line_number JG_ZZ_VAT_SELECTION_PKG.tax_line_number_tbl;
121 gt_tax_invoice_date JG_ZZ_VAT_SELECTION_PKG.tax_invoice_date_tbl;
122 gt_taxable_amt JG_ZZ_VAT_SELECTION_PKG.taxable_amt_tbl;
123 gt_taxable_amt_funcl_curr JG_ZZ_VAT_SELECTION_PKG.taxable_amt_funcl_curr_tbl;
124 gt_tax_amt JG_ZZ_VAT_SELECTION_PKG.tax_amt_tbl;
125 gt_tax_amt_funcl_curr JG_ZZ_VAT_SELECTION_PKG.tax_amt_funcl_curr_tbl;
126 gt_rec_tax_amt_tax_curr JG_ZZ_VAT_SELECTION_PKG.rec_tax_amt_tax_curr_tbl;
127 gt_nrec_tax_amt_tax_curr JG_ZZ_VAT_SELECTION_PKG.nrec_tax_amt_tax_curr_tbl;
128 gt_taxable_disc_amt JG_ZZ_VAT_SELECTION_PKG.taxable_disc_amt_tbl;
129 gt_taxable_disc_amt_fun_curr JG_ZZ_VAT_SELECTION_PKG.taxable_disc_amt_fun_curr_tbl;
130 gt_tax_disc_amt JG_ZZ_VAT_SELECTION_PKG.tax_disc_amt_tbl;
131 gt_tax_disc_amt_fun_curr JG_ZZ_VAT_SELECTION_PKG.tax_disc_amt_fun_curr_tbl;
132 gt_tax_rate_id JG_ZZ_VAT_SELECTION_PKG.tax_rate_id_tbl;
133 gt_tax_rate_code JG_ZZ_VAT_SELECTION_PKG.tax_rate_code_tbl;
134 gt_tax_rate JG_ZZ_VAT_SELECTION_PKG.tax_rate_tbl;
135 gt_tax_rate_code_name JG_ZZ_VAT_SELECTION_PKG.tax_rate_code_name_tbl;
136 gt_tax_rate_code_description JG_ZZ_VAT_SELECTION_PKG.tax_rate_code_description_tbl;
137 gt_tax_rate_vat_trx_type_code JG_ZZ_VAT_SELECTION_PKG.tax_rate_vat_trx_type_code_tbl;
138 gt_tax_rate_vat_trx_type_desc JG_ZZ_VAT_SELECTION_PKG.tax_rate_vat_trx_type_desc_tbl;
139 gt_tax_rate_vat_trx_type_mng JG_ZZ_VAT_SELECTION_PKG.tax_rate_vat_trx_type_mng_tbl;
140 gt_tax_rate_reg_type_code JG_ZZ_VAT_SELECTION_PKG.tax_rate_reg_type_code_tbl;
141 gt_tax_type_code JG_ZZ_VAT_SELECTION_PKG.tax_type_code_tbl;
142 gt_tax_type_mng JG_ZZ_VAT_SELECTION_PKG.tax_type_mng_tbl;
143 gt_tax_recovery_rate JG_ZZ_VAT_SELECTION_PKG.tax_recovery_rate_tbl;
144 gt_tax_regime_code JG_ZZ_VAT_SELECTION_PKG.tax_regime_code_tbl;
145 gt_tax JG_ZZ_VAT_SELECTION_PKG.tax_tbl;
146 gt_tax_jurisdiction_code JG_ZZ_VAT_SELECTION_PKG.tax_jurisdiction_code_tbl;
147 gt_tax_status_id JG_ZZ_VAT_SELECTION_PKG.tax_status_id_tbl;
148 gt_tax_status_code JG_ZZ_VAT_SELECTION_PKG.tax_status_code_tbl;
149 gt_tax_currency_code JG_ZZ_VAT_SELECTION_PKG.tax_currency_code_tbl;
150 gt_offset_tax_rate_code JG_ZZ_VAT_SELECTION_PKG.offset_tax_rate_code_tbl;
151 gt_billing_tp_name JG_ZZ_VAT_SELECTION_PKG.billing_tp_name_tbl;
152 gt_billing_tp_number JG_ZZ_VAT_SELECTION_PKG.billing_tp_number_tbl;
153 gt_billing_tp_tax_reg_num JG_ZZ_VAT_SELECTION_PKG.billing_tp_tax_reg_num_tbl;
154 gt_billing_tp_taxpayer_id JG_ZZ_VAT_SELECTION_PKG.billing_tp_taxpayer_id_tbl;
155 gt_billing_tp_party_number JG_ZZ_VAT_SELECTION_PKG.billing_tp_party_number_tbl;
156 gt_billing_tp_id JG_ZZ_VAT_SELECTION_PKG.billing_tp_id_tbl;
157 gt_billing_tp_tax_rep_flag JG_ZZ_VAT_SELECTION_PKG.billing_tp_tax_rep_flag_tbl;
158 gt_billing_tp_site_id JG_ZZ_VAT_SELECTION_PKG.billing_tp_site_id_tbl;
159 gt_billing_tp_address_id JG_ZZ_VAT_SELECTION_PKG.billing_tp_address_id_tbl;
160 gt_billing_tp_site_name JG_ZZ_VAT_SELECTION_PKG.billing_tp_site_name_tbl;
161 gt_billing_tp_site_tx_reg_num JG_ZZ_VAT_SELECTION_PKG.billing_tp_site_tx_reg_num_tbl;
162 gt_shipping_tp_name JG_ZZ_VAT_SELECTION_PKG.shipping_tp_name_tbl;
163 gt_shipping_tp_number JG_ZZ_VAT_SELECTION_PKG.shipping_tp_number_tbl;
164 gt_shipping_tp_tax_reg_num JG_ZZ_VAT_SELECTION_PKG.shipping_tp_tax_reg_num_tbl;
165 gt_shipping_tp_taxpayer_id JG_ZZ_VAT_SELECTION_PKG.shipping_tp_taxpayer_id_tbl;
166 gt_shipping_tp_id JG_ZZ_VAT_SELECTION_PKG.shipping_tp_id_tbl;
167 gt_shipping_tp_site_id JG_ZZ_VAT_SELECTION_PKG.shipping_tp_site_id_tbl;
168 gt_shipping_tp_address_id JG_ZZ_VAT_SELECTION_PKG.shipping_tp_address_id_tbl;
169 gt_shipping_tp_site_name JG_ZZ_VAT_SELECTION_PKG.shipping_tp_site_name_tbl;
170 gt_shipping_tp_site_tx_rg_num JG_ZZ_VAT_SELECTION_PKG.shipping_tp_site_tx_rg_num_tbl;
171 gt_bill_from_party_id JG_ZZ_VAT_SELECTION_PKG.bill_from_party_id_tbl; -- Bug 16386556
172 gt_bill_from_party_site_id JG_ZZ_VAT_SELECTION_PKG.bill_from_party_site_id_tbl; -- Bug 16386556
173 gt_banking_tp_name JG_ZZ_VAT_SELECTION_PKG.banking_tp_name_tbl;
174 gt_banking_tp_taxpayer_id JG_ZZ_VAT_SELECTION_PKG.banking_tp_taxpayer_id_tbl;
175 gt_bank_account_name JG_ZZ_VAT_SELECTION_PKG.bank_account_name_tbl;
176 gt_bank_account_num JG_ZZ_VAT_SELECTION_PKG.bank_account_num_tbl;
177 gt_bank_account_id JG_ZZ_VAT_SELECTION_PKG.bank_account_id_tbl;
178 gt_bank_branch_id JG_ZZ_VAT_SELECTION_PKG.bank_branch_id_tbl;
179 gt_legal_entity_tax_reg_num JG_ZZ_VAT_SELECTION_PKG.legal_entity_tax_reg_num_tbl;
180 gt_hq_estb_reg_number JG_ZZ_VAT_SELECTION_PKG.hq_estb_reg_number_tbl;
181 gt_tax_line_registration_num JG_ZZ_VAT_SELECTION_PKG.tax_line_registration_num_tbl;
182 gt_cancelled_date JG_ZZ_VAT_SELECTION_PKG.cancelled_date_tbl;
183 gt_cancel_flag JG_ZZ_VAT_SELECTION_PKG.cancel_flag_tbl;
184 gt_offset_flag JG_ZZ_VAT_SELECTION_PKG.offset_flag_tbl;
185 gt_posted_flag JG_ZZ_VAT_SELECTION_PKG.posted_flag_tbl;
186 gt_mrc_tax_line_flag JG_ZZ_VAT_SELECTION_PKG.mrc_tax_line_flag_tbl;
187 gt_reconciliation_flag JG_ZZ_VAT_SELECTION_PKG.reconciliation_flag_tbl;
188 gt_tax_recoverable_flag JG_ZZ_VAT_SELECTION_PKG.tax_recoverable_flag_tbl;
189 gt_reverse_flag JG_ZZ_VAT_SELECTION_PKG.reverse_flag_tbl;
190 gt_correction_flag JG_ZZ_VAT_SELECTION_PKG.correction_flag_tbl;
191 gt_ar_cash_receipt_rev_stat JG_ZZ_VAT_SELECTION_PKG.ar_cash_receipt_rev_stat_tbl;
192 gt_ar_cash_receipt_rev_date JG_ZZ_VAT_SELECTION_PKG.ar_cash_receipt_rev_date_tbl;
193 gt_payables_invoice_source JG_ZZ_VAT_SELECTION_PKG.payables_invoice_source_tbl;
194 gt_acctd_amount_dr JG_ZZ_VAT_SELECTION_PKG.acctd_amount_dr_tbl;
195 gt_acctd_amount_cr JG_ZZ_VAT_SELECTION_PKG.acctd_amount_cr_tbl;
196 gt_rec_application_status JG_ZZ_VAT_SELECTION_PKG.rec_application_status_tbl;
197 gt_vat_country_code JG_ZZ_VAT_SELECTION_PKG.vat_country_code_tbl;
198 gt_invoice_identifier JG_ZZ_VAT_SELECTION_PKG.invoice_identifier_tbl;
199 gt_account_class JG_ZZ_VAT_SELECTION_PKG.account_class_tbl;
200 gt_latest_rec_flag JG_ZZ_VAT_SELECTION_PKG.latest_rec_flag_tbl;
201 gt_jgzz_fiscal_code JG_ZZ_VAT_SELECTION_PKG.jgzz_fiscal_code_tbl;
202 gt_tax_reference JG_ZZ_VAT_SELECTION_PKG.tax_reference_tbl;
203 gt_pt_location JG_ZZ_VAT_SELECTION_PKG.pt_location_tbl;
204 gt_invoice_report_type JG_ZZ_VAT_SELECTION_PKG.invoice_report_type_tbl;
205 gt_es_correction_year JG_ZZ_VAT_SELECTION_PKG.es_correction_year_tbl;
206 gt_es_correction_period JG_ZZ_VAT_SELECTION_PKG.es_correction_period_tbl;
207 gt_triangulation JG_ZZ_VAT_SELECTION_PKG.triangulation_tbl;
208 gt_document_sub_type JG_ZZ_VAT_SELECTION_PKG.document_sub_type_tbl;
209 gt_assessable_value JG_ZZ_VAT_SELECTION_PKG.assessable_value_tbl;
210 gt_property_location JG_ZZ_VAT_SELECTION_PKG.property_location_tbl;
211 gt_chk_vat_amount_paid JG_ZZ_VAT_SELECTION_PKG.chk_vat_amount_paid_tbl;
212 gt_import_document_number JG_ZZ_VAT_SELECTION_PKG.import_document_number_tbl;
213 gt_import_document_date JG_ZZ_VAT_SELECTION_PKG.import_document_date_tbl;
214 gt_prl_no JG_ZZ_VAT_SELECTION_PKG.prl_no_tbl;
215 gt_property_rental JG_ZZ_VAT_SELECTION_PKG.property_rental_tbl;
216 gt_rates_reference JG_ZZ_VAT_SELECTION_PKG.rates_reference_tbl;
217 gt_stair_num JG_ZZ_VAT_SELECTION_PKG.stair_num_tbl;
218 gt_floor_num JG_ZZ_VAT_SELECTION_PKG.floor_num_tbl;
219 gt_door_num JG_ZZ_VAT_SELECTION_PKG.door_num_tbl;
220 gt_amount_applied JG_ZZ_VAT_SELECTION_PKG.amount_applied_tbl;
221 gt_actg_event_type_code JG_ZZ_VAT_SELECTION_PKG.actg_event_type_code_tbl;
222 gt_actg_event_type_mng JG_ZZ_VAT_SELECTION_PKG.actg_event_type_mng_tbl;
223 gt_actg_event_number JG_ZZ_VAT_SELECTION_PKG.actg_event_number_tbl;
224 gt_actg_event_status_flag JG_ZZ_VAT_SELECTION_PKG.actg_event_status_flag_tbl;
225 gt_actg_event_status_mng JG_ZZ_VAT_SELECTION_PKG.actg_event_status_mng_tbl;
226 gt_actg_category_code JG_ZZ_VAT_SELECTION_PKG.actg_category_code_tbl;
227 gt_actg_category_mng JG_ZZ_VAT_SELECTION_PKG.actg_category_mng_tbl;
228 gt_accounting_date JG_ZZ_VAT_SELECTION_PKG.accounting_date_tbl;
229 gt_gl_transfer_flag JG_ZZ_VAT_SELECTION_PKG.gl_transfer_flag_tbl;
230 gt_actg_line_num JG_ZZ_VAT_SELECTION_PKG.actg_line_num_tbl;
231 gt_actg_line_type_code JG_ZZ_VAT_SELECTION_PKG.actg_line_type_code_tbl;
232 gt_actg_line_type_mng JG_ZZ_VAT_SELECTION_PKG.actg_line_type_mng_tbl;
233 gt_actg_line_description JG_ZZ_VAT_SELECTION_PKG.actg_line_description_tbl;
234 gt_actg_stat_amt JG_ZZ_VAT_SELECTION_PKG.actg_stat_amt_tbl;
235 gt_actg_party_id JG_ZZ_VAT_SELECTION_PKG.actg_party_id_tbl;
236 gt_actg_party_site_id JG_ZZ_VAT_SELECTION_PKG.actg_party_site_id_tbl;
237 gt_actg_party_type JG_ZZ_VAT_SELECTION_PKG.actg_party_type_tbl;
238 gt_actg_event_id JG_ZZ_VAT_SELECTION_PKG.actg_event_id_tbl;
239 gt_actg_header_id JG_ZZ_VAT_SELECTION_PKG.actg_header_id_tbl;
240 gt_actg_line_id JG_ZZ_VAT_SELECTION_PKG.actg_line_id_tbl;
241 gt_actg_source_id JG_ZZ_VAT_SELECTION_PKG.actg_source_id_tbl;
242 gt_actg_source_table JG_ZZ_VAT_SELECTION_PKG.actg_source_table_tbl;
243 gt_actg_line_ccid JG_ZZ_VAT_SELECTION_PKG.actg_line_ccid_tbl;
244 gt_account_flexfield JG_ZZ_VAT_SELECTION_PKG.account_flexfield_tbl;
245 gt_account_description JG_ZZ_VAT_SELECTION_PKG.account_description_tbl;
246 gt_period_name JG_ZZ_VAT_SELECTION_PKG.period_name_tbl;
247 gt_trx_arap_balancing_seg JG_ZZ_VAT_SELECTION_PKG.trx_arap_balancing_seg_tbl;
248 gt_trx_arap_natural_account JG_ZZ_VAT_SELECTION_PKG.trx_arap_natural_account_tbl;
249 gt_trx_taxable_balancing_seg JG_ZZ_VAT_SELECTION_PKG.trx_taxable_balancing_seg_tbl;
250 gt_trx_taxable_natural_acct JG_ZZ_VAT_SELECTION_PKG.trx_taxable_natural_acct_tbl;
251 gt_trx_tax_balancing_seg JG_ZZ_VAT_SELECTION_PKG.trx_tax_balancing_seg_tbl;
252 gt_trx_tax_natural_account JG_ZZ_VAT_SELECTION_PKG.trx_tax_natural_account_tbl;
253 gt_created_by JG_ZZ_VAT_SELECTION_PKG.created_by_tbl;
254 gt_creation_date JG_ZZ_VAT_SELECTION_PKG.creation_date_tbl;
255 gt_last_updated_by JG_ZZ_VAT_SELECTION_PKG.last_updated_by_tbl;
256 gt_last_update_date JG_ZZ_VAT_SELECTION_PKG.last_update_date_tbl;
257 gt_last_update_login JG_ZZ_VAT_SELECTION_PKG.last_update_login_tbl;
258 gt_request_id JG_ZZ_VAT_SELECTION_PKG.request_id_tbl;
259 gt_program_application_id JG_ZZ_VAT_SELECTION_PKG.program_application_id_tbl;
260 gt_program_id JG_ZZ_VAT_SELECTION_PKG.program_id_tbl;
261 gt_program_login_id JG_ZZ_VAT_SELECTION_PKG.program_login_id_tbl;
262 gt_object_version_number JG_ZZ_VAT_SELECTION_PKG.object_version_number;
263 gt_gl_date JG_ZZ_VAT_SELECTION_PKG.gl_date_tbl;
264 gt_tax_origin JG_ZZ_VAT_SELECTION_PKG.tax_origin_tbl;
265 gt_trx_ctrl_actg_flexfield JG_ZZ_VAT_SELECTION_PKG.trx_control_actg_flexfield_tbl;
266 gt_reporting_code JG_ZZ_VAT_SELECTION_PKG.reporting_code_tbl;
267 gt_def_rec_settlement_opt_code JG_ZZ_VAT_SELECTION_PKG.def_rec_settlement_op_code_tbl;
268 gt_taxable_item_source_id JG_ZZ_VAT_SELECTION_PKG.taxable_item_source_id_tbl;
269
270 -- BUG 9765740 Spanish Modelo 349 ESL EPL Changes
271 gt_intra_eu_trx_type_code JG_ZZ_VAT_SELECTION_PKG.intra_eu_trx_type_code_tbl;
272
273 ----------------------------------------
274 --Private Methods Declaration
275 ----------------------------------------
276 --
277 PROCEDURE initialize(
278 P_GLOBAL_VARIABLES_REC OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
279 P_REPORTING_LEVEL IN JG_ZZ_VAT_REP_ENTITIES.entity_level_code%TYPE,
280 P_LEDGER IN JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE,
281 P_BSV IN JG_ZZ_VAT_REP_ENTITIES.balancing_segment_value%TYPE,
282 P_VAT_REPORTING_ENTITY_ID IN JG_ZZ_VAT_REP_ENTITIES.vat_reporting_entity_id%TYPE,
283 P_TAX_PERIOD IN JG_ZZ_VAT_REP_STATUS.tax_calendar_period%TYPE,
284 P_SOURCE IN JG_ZZ_VAT_REP_STATUS.source%TYPE,
285 P_ACCTD_UNACCTD IN VARCHAR2,
286 P_DEBUG_FLAG IN VARCHAR2,
287 P_ERRBUF IN OUT NOCOPY VARCHAR2,
288 P_RETCODE IN OUT NOCOPY VARCHAR2);
289
290 PROCEDURE get_period_date_range (
291 p_calendar_name IN GL_PERIODS.period_set_name%TYPE,
292 p_period IN GL_PERIODS.period_name%TYPE,
293 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
294 x_return_status OUT NOCOPY VARCHAR2);
295
296 PROCEDURE get_VAT_reporting_details (
297 p_vat_reporting_entity_id IN JG_ZZ_VAT_REP_ENTITIES.vat_reporting_entity_id%TYPE,
298 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
299 x_return_status OUT NOCOPY VARCHAR2);
300
301 PROCEDURE call_TRL (
302 P_GLOBAL_VARIABLES_REC IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
303 x_request_id IN NUMBER);
304
305 PROCEDURE fetch_tax_data (
306 P_GLOBAL_VARIABLES_REC IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
307 x_request_id IN NUMBER);
308
309 PROCEDURE insert_tax_data ;
310
311 PROCEDURE init_gt_variables;
312
313 PROCEDURE control_intersecting_domains(
314 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
315 x_request_id IN NUMBER,
316 x_intersect_domain_err OUT NOCOPY VARCHAR2);
317
318 PROCEDURE purge_report_finalrep (
319 xv_return_status OUT NOCOPY VARCHAR2,
320 xv_return_message OUT NOCOPY VARCHAR2 );
321
322 PROCEDURE log_file (
323 filename IN VARCHAR2,
324 text_to_write IN VARCHAR2 );
325
326 PROCEDURE tax_date_maintenance_program (
327 p_legal_entity_id IN JG_ZZ_VAT_REP_ENTITIES.legal_entity_id%TYPE,
328 p_ledger_id IN JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE,
329 p_end_date IN GL_PERIODS.END_DATE%TYPE,
330 p_source IN JG_ZZ_VAT_REP_STATUS.source%TYPE,
331 p_debug_flag IN VARCHAR2,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_errbuf OUT NOCOPY VARCHAR2 );
334
335 FUNCTION is_prev_period_open (
336 P_GLOBAL_VARIABLES_REC IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE)
337 RETURN BOOLEAN;
338
339
340 -----------------------------------------
341 --Public Methods
342 -----------------------------------------
343 --
344 /*===========================================================================+
345 | PROCEDURE |
346 | main() |
347 | |
348 | DESCRIPTION |
349 | This procedure calls |
350 | (1) JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities to validate and |
351 | determine whether to create ACCOUNTING reporting entities or not. |
352 | (2) JG_ZZ_VAT_REP_UTILITY.validate_process_initiation to validate and |
353 | determine whether to proceed with selection or not. |
354 | (2) Insert status information into jg_zz_vat_rep_status table. |
355 | (3) Call TRL engine to fetch AR, AP, GL tax information. |
356 | (4) Controls intersecting domains (transactions selected in current |
357 | run intersects with a previous selection run(s). |
358 | (5) Insert TRL info into JG Tax Trx table |
359 | (6) JG_ZZ_VAT_REP_UTILITY.post_process_updates to perform post |
360 | selection process processing. |
361 | |
362 | SCOPE - Public |
363 | |
364 | NOTES |
365 | |
366 | MODIFICATION HISTORY |
367 | Date Author Description |
368 | ============ ============== ================================= |
369 | 23-Jan-2006 RBASKER Initial Version. |
370 | 28-Mar-2006 RBASKER Incorporated changes for XBuild4. |
371 | 28-Apr-2006 RBASKER Bug: 5169118 - Fixed issues identified |
372 | during Unit Testing. |
373 | 04-Aug-2006 RJREDDY Bug: 5372731 - Added procedure call to |
374 | tax_date_maintenance_program |
375 | |
376 +===========================================================================*/
377
378 PROCEDURE main( errbuf OUT NOCOPY VARCHAR2,
379 retcode OUT NOCOPY NUMBER,
380 p_reporting_level IN JG_ZZ_VAT_REP_ENTITIES.entity_level_code%TYPE,
381 p_ledger IN JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE ,
382 p_chart_of_account IN NUMBER,
383 p_bsv IN JG_ZZ_VAT_REP_ENTITIES.balancing_segment_value%TYPE,
384 p_vat_reporting_entity_id IN JG_ZZ_VAT_REP_ENTITIES.vat_reporting_entity_id%TYPE,
385 p_period IN JG_ZZ_VAT_REP_STATUS.tax_calendar_period%TYPE,
386 p_source IN JG_ZZ_VAT_REP_STATUS.source%TYPE,
387 p_acct_unacctd IN VARCHAR2,
388 p_dummy IN VARCHAR2,
389 p_entity_identifier IN VARCHAR2,
390 p_debug_flag IN VARCHAR2)
391 IS
392 l_global_variables_rec GLOBAL_VARIABLES_REC_TYPE;
393 l_api_name CONSTANT VARCHAR2(30) := 'MAIN';
394 l_return_status VARCHAR2(1);
395 l_return_message VARCHAR2(1000);
396 l_intersect_domain_err VARCHAR2(1000);
397
398 l_selection_process_id JG_ZZ_VAT_REP_STATUS.selection_process_id%TYPE;
399 l_selection_status_flag JG_ZZ_VAT_REP_STATUS.selection_status_flag%TYPE;
400 l_mapping_rep_entity_id JG_ZZ_VAT_REP_STATUS.mapping_vat_rep_entity_id%TYPE;
401 l_country xle_firstparty_information_v.country%TYPE;
402 l_legal_entity_id JG_ZZ_VAT_REP_ENTITIES.legal_entity_id%TYPE;
403 l_ledger JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE;
404
405 -- Bug 6835573
406 l_last_reported_period JG_ZZ_VAT_REP_ENTITIES.last_reported_period%TYPE;
407 l_is_mgr_trx_exist NUMBER(15);
408
409
410
411 CURSOR c_is_mgr_trx_exist(pn_vat_rep_entity_id number) IS
412 SELECT JGTRD.trx_id
413 FROM jg_zz_vat_trx_details JGTRD,
414 jg_zz_vat_rep_status JGREPS,
415 zx_lines ZX
416 WHERE JGREPS.vat_reporting_entity_id = pn_vat_rep_entity_id
417 AND JGREPS.reporting_status_id = JGTRD.reporting_status_id
418 AND JGREPS.source = JGTRD.extract_source_ledger
419 AND ZX.trx_id = JGTRD.trx_id
420 AND JGTRD.created_by = 1
421 AND ZX.record_type_code = 'MIGRATED'
422 AND ZX.application_id = JGTRD.application_id
423 AND ZX.entity_code = JGTRD.entity_code
424 AND ZX.event_class_code = JGTRD.event_class_code
425 AND rownum=1;
426 -- Bug 6835573
427
428 l_is_upgrade_customer NUMBER := 0;
429 CURSOR c_is_upgrade_customer IS
430 SELECT 1
431 FROM zx_lines
432 WHERE record_type_code= 'MIGRATED'
433 AND rownum=1;
434
435 l_last_rep_period_start_date DATE;
436 l_last_rep_period_end_date DATE;
437
438 cursor last_reported_date_from_11i (pn_vat_rep_entity_id number) IS
439 select glp.start_date,glp.end_date,acct.last_reported_period
440 from jg_zz_vat_rep_entities legal
441 ,jg_zz_vat_rep_entities acct
442 ,gl_periods glp
443 where acct.entity_type_code = 'ACCOUNTING'
444 and acct.vat_reporting_entity_id = pn_vat_rep_entity_id
445 and acct.mapping_vat_rep_entity_id= legal.vat_reporting_entity_id
446 and glp.period_set_name = legal.tax_calendar_name
447 and glp.period_name = acct.last_reported_period;
448
449
450 BEGIN
451
452 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
454 G_PKG_NAME||': '||l_api_name||'()+');
455 END IF;
456
457 IF p_debug_flag = 'Y' THEN
458 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
459 END IF;
460
461 -- Initialize the parameters:
462 initialize(
463 p_global_variables_rec => l_global_variables_rec,
464 p_reporting_level => p_reporting_level,
465 p_ledger => p_ledger,
466 p_bsv => p_bsv,
467 p_vat_reporting_entity_id => p_vat_reporting_entity_id,
468 p_tax_period => p_period,
469 p_source => p_source,
470 p_acctd_unacctd => p_acct_unacctd,
471 p_debug_flag => p_debug_flag,
472 p_errbuf => errbuf,
473 p_retcode => retcode
474 );
475
476
477
478 l_mapping_rep_entity_id := l_global_variables_rec.vat_reporting_entity_id;
479
480 -- maintain_selection_entities:
481 /*-------------------------------------------------------------------+
482 | Call utility package to validate and determine whether to create |
483 | ACCOUNTING reporting entities or not. This procedure calls: |
484 | (1) validate_entity_attributes - Validates the parameters passed |
485 | based on the Reporting Level. |
486 | (2) get_accounting_entity - Checks if the passed accounting |
487 | entity exists in jg_zz_vat_rep_entities table. |
488 | (3) create_accounting_entity - create a record for the accounting|
489 | entity in the table if it does not exists and returns the vat |
490 | reporting entity identifier. |
491 +--------------------------------------------------------------------*/
492 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
494 'JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities'||'.BEGIN',
495 G_PKG_NAME||': '||'JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities'
496 ||'()+');
497 END IF;
498
499
500 JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities(
501 pv_entity_level_code => l_global_variables_rec.reporting_entity_level,
502 pn_vat_reporting_entity_id => l_global_variables_rec.vat_reporting_entity_id,
503 pn_ledger_id => l_global_variables_rec.ledger,
504 pv_balancing_segment_value => l_global_variables_rec.bsv,
505 xn_vat_reporting_entity_id => l_global_variables_rec.vat_reporting_entity_id,
506 xv_return_status => l_return_status,
507 xv_return_message => l_return_message
508 );
509
510 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
511 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
512 'JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities'||'.END',
513 G_PKG_NAME||': '||'JG_ZZ_VAT_REP_UTILITY.maintain_selection_entities'||'()-'
514 );
515 END IF;
516
517
518 -- Begin : Collect the information related to Last Reported Period in 11i. Bug 9381398
519
520 IF l_global_variables_rec.reporting_entity_level IN ('LEDGER', 'BSV') THEN
521
522
523 OPEN last_reported_date_from_11i(l_global_variables_rec.vat_reporting_entity_id);
524 FETCH last_reported_date_from_11i
525 INTO l_last_rep_period_start_date
526 ,l_last_rep_period_end_date
527 ,l_last_reported_period;
528 CLOSE last_reported_date_from_11i;
529
530 IF p_debug_flag = 'Y' THEN
531 fnd_file.put_line(fnd_file.log, 'l_last_rep_period_start_date :'||l_last_rep_period_start_date);
532 fnd_file.put_line(fnd_file.log, 'l_last_rep_period_end_date :'||l_last_rep_period_end_date);
533 fnd_file.put_line(fnd_file.log, 'l_last_reported_period :'||l_last_reported_period);
534 END IF;
535
536 l_global_variables_rec.LAST_REPORTED_PERIOD_FROM_11i := l_last_reported_period;
537 l_global_variables_rec.LAST_REP_PERIOD_START_DATE := l_last_rep_period_start_date;
538 l_global_variables_rec.LAST_REP_PERIOD_END_DATE := l_last_rep_period_end_date;
539
540 END IF;
541
542 -- End : Collect the information related to Last Reported Period in 11i. Bug 9381398
543
544 if l_return_status in ( FND_API.G_RET_STS_UNEXP_ERROR,
545 FND_API.G_RET_STS_ERROR) then
546 l_selection_status_flag := fnd_api.g_ret_sts_error;
547 errbuf := l_return_message;
548 retcode := 2;
549 RETURN;
550 end if;
551 COMMIT; --Bug:7759140 is for commiting the creation of accounting vat reporting entity.
552
553 -- Bug 6835573
554
555 OPEN c_is_upgrade_customer;
556 FETCH c_is_upgrade_customer INTO l_is_upgrade_customer;
557 CLOSE c_is_upgrade_customer;
558
559 IF l_is_upgrade_customer = 1 THEN
560 IF l_global_variables_rec.reporting_entity_level = 'LEDGER' or
561 l_global_variables_rec.reporting_entity_level = 'BSV' THEN
562
563 IF l_last_reported_period IS NULL THEN
564
565 OPEN c_is_mgr_trx_exist(l_global_variables_rec.vat_reporting_entity_id);
566 FETCH c_is_mgr_trx_exist INTO l_is_mgr_trx_exist;
567 CLOSE c_is_mgr_trx_exist;
568
569 IF l_is_mgr_trx_exist IS NULL THEN
570
571 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_PRE_REP_PROC_REQ');
572 errbuf := FND_MESSAGE.GET;
573 retcode := 2;
574
575 RETURN;
576
577 END IF;
578
579 END IF;
580 END IF;
581 END IF;
582 -- Bug 6835573
583
584 -- validate_process_initiation:
585 /*-------------------------------------------------------------------+
586 | Call utility package to validate and determine whether to proceed |
587 | with Selection Process or not. This procedure is responsible for: |
588 | (1) Check if Selection is already processed for given combination.|
589 | (2) Check if Final reporting has already happened, if yes then |
590 | re-selection is not allowed. |
591 | (3) Before re-selection, purge allocation data and errors table by|
592 | calling allocation purge api. |
593 | (4) Before re-selection, purge the tax data pertaining to the |
594 | previous run from jg_zz_vat_trx_details table by calling |
595 | jg_zz_vat_selection_pkg.purge_tax_data. |
596 | (5) Before re-selection, update the status information (nullify) |
597 | of the selection process for the given combination by calling |
598 | JG_ZZ_VAT_REP_UTILITY.pre_process_update procedure. |
599 | (6) If Selection is not yet initiated for the given combination |
600 | Validate if there is any gap in selection as per calendar |
601 | (7) Inserts status information of the Selection process into the |
602 | jg_zz_vat_rep_status table. |
603 +--------------------------------------------------------------------*/
604 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
605 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
606 'JG_ZZ_VAT_REP_UTILITY.validate_process_initiation'||'.BEGIN',
607 G_PKG_NAME||': '||
608 'JG_ZZ_VAT_REP_UTILITY.validate_process_initiation'||'()+');
609 END IF;
610
611 JG_ZZ_VAT_REP_UTILITY.validate_process_initiation(
612 pn_vat_reporting_entity_id => l_global_variables_rec.vat_reporting_entity_id,
613 pv_tax_calendar_period => l_global_variables_rec.tax_period,
614 pv_source => l_global_variables_rec.source,
615 pv_process_name => 'SELECTION',
616 xn_reporting_status_id_ap => g_rep_status_id_ap,
617 xn_reporting_status_id_ar => g_rep_status_id_ar,
618 xn_reporting_status_id_gl => g_rep_status_id_gl,
619 xv_return_status => l_return_status,
620 xv_return_message => l_return_message
621 );
622
623 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
624 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
625 'JG_ZZ_VAT_REP_UTILITY.validate_process_initiation'||'.END',
626 G_PKG_NAME||': ' ||
627 'JG_ZZ_VAT_REP_UTILITY.validate_process_initiation'||'()-');
628 END IF;
629
630 if l_return_status in ( FND_API.G_RET_STS_UNEXP_ERROR,
631 FND_API.G_RET_STS_ERROR) then
632 l_selection_status_flag := fnd_api.g_ret_sts_error;
633 errbuf := l_return_message;
634 retcode := 2;
635 RETURN;
636 end if;
637
638 -- Get Reporting configuration details
639 get_VAT_reporting_details (
640 p_vat_reporting_entity_id => l_mapping_rep_entity_id,
641 p_global_variables_rec => l_global_variables_rec,
642 x_return_status => l_return_status);
643
644 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
645 l_selection_status_flag := fnd_api.g_ret_sts_error;
646 errbuf := 'Unexpected error in get_VAT_reporting_details';
647 retcode := 2;
648 RETURN;
649 ELSE
650 -- Get Date Range
651 get_period_date_range (
652 p_calendar_name => l_global_variables_rec.tax_calendar_name,
653 p_period => l_global_variables_rec.tax_period,
654 p_global_variables_rec => l_global_variables_rec,
655 x_return_status => l_return_status);
656 END IF;
657
658 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 l_selection_status_flag := fnd_api.g_ret_sts_error;
660 errbuf := 'Unexpected error in get_period_date_range';
661 retcode := 2;
662 RETURN;
663 ELSE
664
665 -- use TRN/LE to determine the country for which SELECTION is executed.
666 SELECT xle.country
667 INTO l_country
668 FROM xle_firstparty_information_v xle
669 WHERE xle.legal_entity_id = l_global_variables_rec.legal_entity_id;
670
671
672 /*-----------------------------------------------------------------------------------------------------+
673 | For country Spain, the Driving date for Tax Reporting is a special case. Non Modelo and Modelo |
674 | reports are based on GL date and Transaction Date respectively. When we select the data for |
675 | 'Spain',we select the full data set for both modelo and non modelo reports and pass to the trl BOTH |
676 | the GL date and Trx/Inv date as filtering criteria/driving date. We request TRL with parameter |
677 | P_GL_OR_TRX_DATE_FILTER=> 'Y', to select all data that fall within GL date range and also UNION |
678 | all data within the TRX date range. |
679 +------------------------------------------------------------------------------------------------------*/
680
681 IF l_country ='ES' THEN --spain
682 l_global_variables_rec.gl_or_trx_date_filter := 'Y';
683 ELSE
684 l_global_variables_rec.gl_or_trx_date_filter := 'N';
685 END IF;
686
687 -- tax_date_maintenance_program:
688 /*-------------------------------------------------------------------+
689 | Call tax_date_maintenance_program for ECE countries (PL, HU, CZ) |
690 | to update the tax_invoice_date to payment clearing date for the |
691 | invoices which has not been finally reported. |
692 | - This routine is called at the same reporting level as TRL is |
693 | called ie LE or LEDGER level. |
694 +--------------------------------------------------------------------*/
695
696 IF (l_country = 'PL' or l_country = 'HU' or
697 l_country = 'CZ') THEN
698
699 -- Check the Reporting Entity Level.
700 IF l_global_variables_rec.REPORTING_ENTITY_LEVEL = 'LEDGER' or
701 l_global_variables_rec.REPORTING_ENTITY_LEVEL = 'BSV' THEN
702 l_ledger := l_global_variables_rec.legal_entity_id;
703 l_legal_entity_id := NULL;
704 ELSE
705 l_legal_entity_id := l_global_variables_rec.legal_entity_id;
706 l_ledger := NULL;
707 END IF;
708
709 IF g_debug_flag = 'Y' THEN
710 fnd_file.put_line(fnd_file.log,
711 'Following parameters are passed to Tax_Date_Maintenance_Program...' );
712 fnd_file.put_line(fnd_file.log,
713 'P_LEGAL_ENTITY_ID = '|| l_legal_entity_id);
714 fnd_file.put_line(fnd_file.log,
715 'P_LEDGER = '|| l_ledger);
716 fnd_file.put_line(fnd_file.log,
717 ' P_END_DATE = '|| l_global_variables_rec.tax_invoice_date_high );
718 fnd_file.put_line(fnd_file.log,
719 ' P_SOURCE = ' || l_global_variables_rec.source );
720 fnd_file.put_line(fnd_file.log,
721 ' P_DEBUG_FLAG = '|| p_debug_flag );
722 END IF;
723
724 tax_date_maintenance_program (
725 p_legal_entity_id => l_legal_entity_id,
726 p_ledger_id => l_ledger,
727 p_end_date => l_global_variables_rec.tax_invoice_date_high,
728 p_source => l_global_variables_rec.source,
729 p_debug_flag => p_debug_flag ,
730 x_return_status => l_return_status,
731 x_errbuf => l_return_message
732 );
733
734 if l_return_status in ( FND_API.G_RET_STS_UNEXP_ERROR,
735 FND_API.G_RET_STS_ERROR) then
736 l_selection_status_flag := fnd_api.g_ret_sts_error;
737 errbuf := l_return_message;
738 retcode := 2;
739 RETURN;
740 end if;
741 END IF;
742
743 -- Call TRL engine
744 call_TRL(l_global_variables_rec,
745 g_conc_request_id);
746 END IF;
747
748 IF l_global_variables_rec.retcode <> 2 THEN
749
750 -- Call control_intersecting_domains
751 control_intersecting_domains(
752 l_global_variables_rec,
753 g_conc_request_id,
754 l_intersect_domain_err);
755
756 IF l_global_variables_rec.retcode <> 2 THEN
757
758 -- Bulk fetch data from TRL interface table.
759 -- And bulk insert into JG_ZZ_VAT_TRX_DETAILS.
760 fetch_tax_data(l_global_variables_rec,
761 g_conc_request_id);
762 ELSE
763 l_selection_status_flag := fnd_api.g_ret_sts_error;
764 errbuf := l_intersect_domain_err;
765 retcode := 2;
766 RETURN;
767 END IF;
768 END IF;
769
770 IF l_global_variables_rec.retcode <> 2 THEN
771
772 -- For System Testing purpose will not purge data from TRL table when run
773 -- in debug mode.
774 IF nvl(p_debug_flag, 'N') = 'N' THEN
775 -- Call TRL's purge api, to purge data from TRL interface table.
776 ZX_EXTRACT_PKG.purge(g_conc_request_id);
777 END IF;
778
779 -- post_process_update
780 -- Call the utility API to update selection_process columns of
781 -- jg_zz_vat_rep_status table by passing proper values.
782 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
783 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
784 'JG_ZZ_VAT_REP_UTILITY.post_process_update'||'.BEGIN',
785 G_PKG_NAME||': '||
786 'JG_ZZ_VAT_REP_UTILITY.post_process_update'||'()+');
787 END IF;
788
789 l_selection_process_id := g_selection_process_id;
790 if l_selection_status_flag is null then
791 l_selection_status_flag := fnd_api.g_ret_sts_success;
792 end if;
793
794
795 JG_ZZ_VAT_REP_UTILITY.post_process_update(
796 pn_vat_reporting_entity_id => l_global_variables_rec.vat_reporting_entity_id,
797 pv_tax_calendar_period => l_global_variables_rec.tax_period,
798 pv_source => l_global_variables_rec.source,
799 pv_process_name => 'SELECTION',
800 pn_process_id => l_selection_process_id,
801 pv_process_flag => l_selection_status_flag ,
802 pv_enable_allocations_flag => NULL,
803 xv_return_status => l_return_status,
804 xv_return_message => l_return_message
805 );
806
807 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
808 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||
809 'JG_ZZ_VAT_REP_UTILITY.post_process_update'||'.END',
810 G_PKG_NAME||': ' ||'JG_ZZ_VAT_REP_UTILITY.post_process_update'||'()-');
811 END IF;
812
813 IF l_return_status <> fnd_api.g_ret_sts_success THEN
814 errbuf := l_return_message;
815 retcode := 2;
816 RETURN;
817 END IF;
818
819 ELSE
820 errbuf := l_global_variables_rec.errbuf;
821 retcode := l_global_variables_rec.retcode;
822 RETURN;
823 END IF;
824
825
826 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
827 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
828 ' RETURN_STATUS = ' || retcode);
829 END IF;
830
831 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
832 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
833 G_PKG_NAME||': ' ||l_api_name||'()-');
834 END IF;
835
836 IF p_debug_flag = 'Y' THEN
837 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
838 END IF;
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
843
844 IF p_debug_flag = 'Y' THEN
845 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
846 END IF;
847
848 FND_MSG_PUB.Add;
849 IF (g_level_unexpected >= g_current_runtime_level ) THEN
850 FND_LOG.STRING(g_level_unexpected,
851 G_MODULE_NAME||l_api_name,
852 g_error_buffer);
853 END IF;
854
855 RETCODE := l_global_variables_rec.retcode;
856
857 END main;
858
859 /*===========================================================================+
860 | PROCEDURE |
861 | purge_tax_data |
862 | |
863 | DESCRIPTION |
864 | This procedure deletes the records from JG_ZZ_VAT_TRX_DETAILS |
865 | for a given reporting_status_id |
866 | |
867 | SCOPE - Public |
868 | |
869 | NOTES |
870 | |
871 | MODIFICATION HISTORY |
872 | Date Author Description |
873 | ============ ============== ================================= |
874 | 23-Jan-2006 RBASKER Initial Version. |
875 | |
876 +===========================================================================*/
877
878 PROCEDURE PURGE_TAX_DATA (p_reporting_status_id in number,
879 x_return_status OUT NOCOPY VARCHAR2)
880 IS
881 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_TAX_DATA';
882 BEGIN
883 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
885 G_PKG_NAME||': '||l_api_name||'()+');
886 END IF;
887
888 IF g_debug_flag = 'Y' THEN
889 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
890 END IF;
891
892 /*Set the return status to Success */
893 x_return_status := FND_API.G_RET_STS_SUCCESS;
894
895 delete from JG_ZZ_VAT_TRX_DETAILS
896 where reporting_Status_id = p_reporting_status_id;
897
898 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
899 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
900 ' RETURN_STATUS = ' || x_return_status);
901 END IF;
902
903 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
904 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
905 G_PKG_NAME||': ' ||l_api_name||'()-');
906 END IF;
907
908 IF g_debug_flag = 'Y' THEN
909 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
910 END IF;
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
915 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
916
917 IF g_debug_flag = 'Y' THEN
918 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
919 END IF;
920
921 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
922 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
923 END IF;
924 RETURN;
925 END PURGE_TAX_DATA;
926
927
928 /*===========================================================================+
929 | FUNCTION |
930 | PURGE_TAX_DATA |
931 | |
932 | DESCRIPTION |
933 | This function deletes the records from JG_ZZ_VAT_TRX_DETAILS, |
934 | for a given reporting_status_id |
935 | |
936 | SCOPE - Public |
937 | |
938 | NOTES |
939 | |
940 | MODIFICATION HISTORY |
941 | Date Author Description |
942 | ============ ============== ================================= |
943 | 23-Jan-2006 RBASKER Initial Version. |
944 | |
945 +===========================================================================*/
946
947 FUNCTION PURGE_TAX_DATA(p_reporting_status_id in number) return number is
948 num_rows_deleted number := 0;
949 l_return_status NUMBER;
950 BEGIN
951
952
953 select count(*) into num_rows_deleted
954 from jg_zz_vat_trx_details
955 where reporting_status_id = p_reporting_status_id;
956
957 PURGE_TAX_DATA(p_reporting_status_id, l_return_status);
958
959 return(num_rows_deleted);
960 END;
961
962 ------------------------------------------------------------------------------
963 -- PRIVATE METHODS
964 ------------------------------------------------------------------------------
965 --
966 /*===========================================================================+
967 | PROCEDURE |
968 | INITIALIZE |
969 | |
970 | DESCRIPTION |
971 | This procedure initializes the parameters for procedure |
972 | JG_ZZ_VAT_SELECTION_PKG.Main, and writes the values of parameters |
973 | passed in debug file. |
974 | |
975 | Called from JG_ZZ_VAT_SELECTION_PKG.Main |
976 | |
977 | SCOPE - Private |
978 | |
979 | NOTES |
980 | |
981 | MODIFICATION HISTORY |
982 | Date Author Description |
983 | ============ ============== ================================= |
984 | 23-Jan-2006 RBASKER Initial Version. |
985 | 28-Mar-2006 RBASKER Incorporated changes for XBuild4. |
986 | |
987 +===========================================================================*/
988
989 PROCEDURE initialize(
990 P_GLOBAL_VARIABLES_REC OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
991 P_REPORTING_LEVEL IN JG_ZZ_VAT_REP_ENTITIES.entity_level_code%TYPE,
992 P_LEDGER IN JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE,
993 P_BSV IN JG_ZZ_VAT_REP_ENTITIES.balancing_segment_value%TYPE,
994 P_VAT_REPORTING_ENTITY_ID IN JG_ZZ_VAT_REP_ENTITIES.vat_reporting_entity_id%TYPE,
995 P_TAX_PERIOD IN JG_ZZ_VAT_REP_STATUS.tax_calendar_period%TYPE,
996 P_SOURCE IN JG_ZZ_VAT_REP_STATUS.source%TYPE,
997 P_ACCTD_UNACCTD IN VARCHAR2,
998 P_DEBUG_FLAG IN VARCHAR2,
999 P_ERRBUF IN OUT NOCOPY VARCHAR2,
1000 P_RETCODE IN OUT NOCOPY VARCHAR2)
1001 IS
1002 l_api_name CONSTANT VARCHAR2(30) := 'INITIALIZE';
1003
1004 BEGIN
1005 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1006 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
1007 G_PKG_NAME||': '||l_api_name||'()+');
1008 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1009 'P_REPORTING_LEVEL = ' || P_REPORTING_LEVEL);
1010 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1011 'P_LEDGER = ' || P_LEDGER);
1012 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1013 'P_BSV = '|| P_BSV);
1014 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1015 ' P_VAT_REPORTING_ENTITY_ID = ' || P_VAT_REPORTING_ENTITY_ID);
1016 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1017 ' P_TAX_PERIOD = ' || P_TAX_PERIOD);
1018 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1019 ' P_SOURCE = '|| P_SOURCE);
1020 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1021 ' P_ACCTD_UNACCTD = '|| P_ACCTD_UNACCTD);
1022 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1023 ' P_DEBUG_FLAG = '|| P_DEBUG_FLAG);
1024 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1025 'P_ERRBUF = '||P_ERRBUF);
1026 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1027 'P_RETCODE = '||P_RETCODE);
1028 END IF;
1029
1030 IF p_debug_flag = 'Y' THEN
1031 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
1032 END IF;
1033
1034 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'start ..');
1035 P_GLOBAL_VARIABLES_REC.REPORTING_ENTITY_LEVEL := P_REPORTING_LEVEL;
1036 P_GLOBAL_VARIABLES_REC.LEDGER := P_LEDGER;
1037 P_GLOBAL_VARIABLES_REC.BSV := P_BSV;
1038 P_GLOBAL_VARIABLES_REC.VAT_REPORTING_ENTITY_ID := P_VAT_REPORTING_ENTITY_ID;
1039 P_GLOBAL_VARIABLES_REC.TAX_PERIOD := P_TAX_PERIOD;
1040 P_GLOBAL_VARIABLES_REC.SOURCE := P_SOURCE;
1041 P_GLOBAL_VARIABLES_REC.ACCTD_UNACCTD := P_ACCTD_UNACCTD;
1042 P_GLOBAL_VARIABLES_REC.DEBUG_FLAG := P_DEBUG_FLAG;
1043 P_GLOBAL_VARIABLES_REC.ERRBUF := P_ERRBUF;
1044 P_GLOBAL_VARIABLES_REC.RETCODE := NVL(P_RETCODE,0);
1045 IF P_GLOBAL_VARIABLES_REC.REPORTING_ENTITY_LEVEL IN ('LEDGER', 'BSV') THEN
1046 P_GLOBAL_VARIABLES_REC.MAPPING_VAT_REP_ENTITY_ID := P_VAT_REPORTING_ENTITY_ID;
1047 END IF;
1048 g_debug_flag := P_DEBUG_FLAG;
1049
1050 -- Populate the WHO columns :
1051
1052 g_created_by := nvl(fnd_global.user_id,1); --nvl(fnd_profile.value('USER_ID'),1);
1053 g_creation_date := sysdate;
1054 g_last_updated_by := nvl(fnd_global.user_id,1); --nvl(fnd_profile.value('USER_ID'),1);
1055 g_last_update_date := sysdate;
1056 g_last_update_login := 1;
1057 g_conc_request_id := nvl(fnd_global.conc_request_id,1); --nvl(fnd_profile.value('CONC_REQUEST_ID'),1);
1058 g_prog_appl_id := nvl(fnd_global.prog_appl_id,1); --nvl(fnd_profile.value('PROG_APPL_ID'),1);
1059 g_conc_program_id := nvl(fnd_global.conc_program_id,1); --nvl(fnd_profile.value('CONC_PROGRAM_ID'),1);
1060 g_conc_login_id := nvl(fnd_global.conc_login_id,1); --nvl(fnd_profile.value('CONC_LOGIN_ID'),1);
1061
1062 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1063 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1064 G_PKG_NAME||': ' ||l_api_name||'()-');
1065 END IF;
1066
1067 IF p_debug_flag = 'Y' THEN
1068 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
1069 END IF;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1074 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1075 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name, g_error_buffer);
1076 END IF;
1077
1078 IF p_debug_flag = 'Y' THEN
1079 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1080 END IF;
1081 P_GLOBAL_VARIABLES_REC.RETCODE := 2;
1082
1083 END initialize;
1084
1085 /*===========================================================================+
1086 | PROCEDURE |
1087 | get_period_date_range |
1088 | |
1089 | DESCRIPTION |
1090 | This procedure gets the start and end date for a given period of the |
1091 | tax calendar. This serves as the tax reporting date range low and high.|
1092 | |
1093 | Called from JG_ZZ_VAT_SELECTION_PKG.main |
1094 | |
1095 | SCOPE - Private |
1096 | |
1097 | NOTES |
1098 | |
1099 | MODIFICATION HISTORY |
1100 | Date Author Description |
1101 | ============ ============== ================================= |
1102 | 23-Jan-2006 RBASKER Initial Version. |
1103 | |
1104 +===========================================================================*/
1105
1106 PROCEDURE get_period_date_range (
1107 p_calendar_name IN GL_PERIODS.period_set_name%TYPE,
1108 p_period IN GL_PERIODS.period_name%TYPE,
1109 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
1110 x_return_status OUT NOCOPY VARCHAR2
1111 ) IS
1112 l_api_name CONSTANT VARCHAR2(30) := 'GET_PERIOD_DATE_RANGE';
1113
1114 BEGIN
1115 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
1117 G_PKG_NAME||': '||l_api_name||'()+');
1118 END IF;
1119
1120 /*Set the return status to Success */
1121 x_return_status := FND_API.G_RET_STS_SUCCESS;
1122
1123 IF g_debug_flag = 'Y' THEN
1124 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
1125 END IF;
1126
1127
1128 SELECT start_date,
1129 end_date
1130 INTO p_global_variables_rec.tax_invoice_date_low,
1131 p_global_variables_rec.tax_invoice_date_high
1132 FROM GL_PERIODS
1133 WHERE period_set_name = p_calendar_name
1134 AND period_name = p_period;
1135
1136 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1137 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1138 'TAX INVOICE DATE LOW = ' ||
1139 to_char( p_global_variables_rec.tax_invoice_date_low) );
1140 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1141 'TAX INVOICE DATE HIGH = ' ||
1142 to_char( p_global_variables_rec.tax_invoice_date_high) );
1143 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1144 ' RETURN_STATUS = ' || x_return_status);
1145 END IF;
1146
1147 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1149 G_PKG_NAME||': ' ||l_api_name||'()-');
1150 END IF;
1151
1152 IF g_debug_flag = 'Y' THEN
1153 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
1154 END IF;
1155
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1159
1160 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1161
1162 IF g_debug_flag = 'Y' THEN
1163 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1164 END IF;
1165
1166 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1167 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1168 END IF;
1169 p_global_variables_rec.retcode := 2;
1170 RETURN;
1171 END get_period_date_range;
1172
1173 /*===========================================================================+
1174 | PROCEDURE |
1175 | get_VAT_reporting_details |
1176 | |
1177 | DESCRIPTION |
1178 | This procedure gets VAT configuration details like Tax Registration |
1179 | Number(TRN), Legal Entity, Tax calendar, Tax Regime for a given |
1180 | VAT_REPORTING_ENTITY_ID. |
1181 | |
1182 | Called from JG_ZZ_VAT_SELECTION_PKG.main |
1183 | |
1184 | SCOPE - Private |
1185 | |
1186 | NOTES |
1187 | |
1188 | MODIFICATION HISTORY |
1189 | Date Author Description |
1190 | ============ ============== ================================= |
1191 | 23-Jan-2006 RBASKER Initial Version. |
1192 | 28-Mar-2006 RBASKER Incorporated changes for XBuild4. |
1193 | 28-Apr-2006 RBASKER Bug: 5169118 - Fixed issues identified |
1194 | during Unit Testing. |
1195 | |
1196 +===========================================================================*/
1197
1198 PROCEDURE get_VAT_reporting_details (
1199 p_vat_reporting_entity_id IN JG_ZZ_VAT_REP_ENTITIES.vat_reporting_entity_id%TYPE,
1200 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
1201 x_return_status OUT NOCOPY VARCHAR2
1202 ) IS
1203 l_api_name CONSTANT VARCHAR2(30) := 'GET_VAT_REPORTING_DETAILS';
1204
1205 BEGIN
1206 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1207 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1208 END IF;
1209
1210 /*Set the return status to Success */
1211 x_return_status := FND_API.G_RET_STS_SUCCESS;
1212
1213 IF g_debug_flag = 'Y' THEN
1214 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
1215 END IF;
1216
1217 SELECT legal_entity_id,
1218 tax_regime_code,
1219 tax_registration_number,
1220 tax_calendar_name,
1221 driving_date_code
1222 INTO p_global_variables_rec.legal_entity_id,
1223 p_global_variables_rec.tax_regime_code,
1224 p_global_variables_rec.tax_registration_number,
1225 p_global_variables_rec.tax_calendar_name,
1226 p_global_variables_rec.driving_date_code
1227 FROM JG_ZZ_VAT_REP_ENTITIES
1228 WHERE nvl(mapping_vat_rep_entity_id,
1229 vat_reporting_entity_id) = p_vat_reporting_entity_id
1230 AND entity_type_code = 'LEGAL';
1231
1232 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1233 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1234 'LEGAL ENTITY ID = ' || p_global_variables_rec.legal_entity_id);
1235 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1236 'TAX REGIME CODE = ' || p_global_variables_rec.tax_regime_code);
1237 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1238 'TAX REG NUM = '|| p_global_variables_rec.tax_registration_number);
1239 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1240 'TAX CALENDAR NAME = '|| p_global_variables_rec.tax_calendar_name);
1241 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1242 'DRIVING_DATE_CODE = '|| p_global_variables_rec.driving_date_code);
1243 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
1244 ' RETURN_STATUS = ' || x_return_status);
1245 END IF;
1246
1247 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1248 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1249 G_PKG_NAME||': ' ||l_api_name||'()-');
1250 END IF;
1251
1252 IF g_debug_flag = 'Y' THEN
1253 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
1254 END IF;
1255
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1259
1260 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1261
1262 IF g_debug_flag = 'Y' THEN
1263 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1264 END IF;
1265
1266 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1267 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1268 END IF;
1269 p_global_variables_rec.retcode := 2;
1270 RETURN;
1271 END get_VAT_reporting_details;
1272
1273 /*===========================================================================+
1274 | FUNCTION |
1275 | is_prev_period_open |
1276 | DESCRIPTION |
1277 | This function returns TRUE if multiple periods are kept open ie final |
1278 | reporting process is not yet done for the prev period(s). |
1279 | SCOPE - Private |
1280 | |
1281 | NOTES |
1282 | |
1283 | MODIFICATION HISTORY |
1284 | Date Author Description |
1285 | ============ ============== ================================= |
1286 | 23-Jan-2006 RBASKER Initial Version. |
1287 | |
1288 +===========================================================================*/
1289 FUNCTION is_prev_period_open(
1290 P_GLOBAL_VARIABLES_REC IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE)
1291 RETURN BOOLEAN IS
1292
1293 l_api_name CONSTANT VARCHAR2(30) := 'IS_MULTIPLE_PERIODS_OPEN';
1294 l_last_processed_date DATE;
1295 l_count NUMBER := 0;
1296
1297 BEGIN
1298
1299 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1300 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1301 END IF;
1302
1303 IF g_debug_flag = 'Y' THEN
1304 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
1305 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_global_variables_rec.last_rep_period_start_date :'||p_global_variables_rec.last_rep_period_start_date);
1306 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_global_variables_rec.tax_invoice_date_low :'||p_global_variables_rec.tax_invoice_date_low);
1307 END IF;
1308
1309 -- BEGIN Code to check if the period is prior to the last reported period in 11i. --9381398
1310
1311 IF p_global_variables_rec.last_rep_period_start_date is not null THEN
1312 IF p_global_variables_rec.tax_invoice_date_low <= p_global_variables_rec.last_rep_period_start_date THEN
1313 RETURN TRUE;
1314 END IF;
1315 END IF;
1316 -- END Code to check if the period is prior the the last reported period in 11i. --9381398
1317
1318 -- BEGIN Code to check if this is a very first reporting period.
1319
1320 select count(*) INTO l_count
1321 from jg_zz_vat_rep_status
1322 where vat_reporting_entity_id = p_global_variables_rec.vat_reporting_entity_id
1323 and period_start_date < p_global_variables_rec.tax_invoice_date_low
1324 and ( period_start_date > p_global_variables_rec.last_rep_period_start_date
1325 or p_global_variables_rec.last_rep_period_start_date is null) --9381398
1326 and (source = p_global_variables_rec.SOURCE
1327 OR p_global_variables_rec.SOURCE = 'ALL');
1328
1329 IF l_count = 0 THEN
1330 RETURN FALSE;
1331 END IF;
1332
1333 -- END Code to check if this is a very first reporting period.
1334
1335 --Find out the last final reported period
1336 l_last_processed_date := JG_ZZ_VAT_REP_UTILITY.get_last_processed_date(
1337 pn_vat_reporting_entity_id => p_global_variables_rec.vat_reporting_entity_id,
1338 pv_source => p_global_variables_rec.source,
1339 pv_process_name => 'FINAL REPORTING'
1340 );
1341
1342 IF l_last_processed_date IS NOT NULL THEN
1343 IF l_last_processed_date + 1 = p_global_variables_rec.tax_invoice_date_low
1344 THEN
1345 RETURN FALSE;
1346 ELSE
1347 RETURN TRUE;
1348 END IF;
1349 ELSE /* There is no processing record, this is the first run */
1350 -- RETURN FALSE;
1351 RETURN TRUE;
1352 END IF;
1353
1354 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1355 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1356 G_PKG_NAME||': ' ||l_api_name||'()-');
1357 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1358 'Last Processed Date : ' || to_char(l_last_processed_date));
1359 END IF;
1360
1361 IF g_debug_flag = 'Y' THEN
1362 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
1363 END IF;
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1368
1369 IF g_debug_flag = 'Y' THEN
1370 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1371 END IF;
1372
1373 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1374 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1375 END IF;
1376 RETURN NULL;
1377 END is_prev_period_open;
1378
1379 /*===========================================================================+
1380 | PROCEDURE |
1381 | INIT_GT_VARIABLES |
1382 | DESCRIPTION |
1383 | This proceure initializes all global variables |
1384 | |
1385 | SCOPE - Private |
1386 | |
1387 | NOTES |
1388 | |
1389 | MODIFICATION HISTORY |
1390 | Date Author Description |
1391 | ============ ============== ================================= |
1392 | 23-Jan-2006 RBASKER Initial Version. |
1393 | 28-Apr-2006 RBASKER Bug: 5169118 - Fixed issues identified |
1394 | during Unit Testing. |
1395 | 18-Sep-2006 RBASKER Bug 5509788 - Changes made to initialize |
1396 | TAX_ORIGIN,TRX_CONTROL_ACCOUNT_FLEXFIELD, |
1397 | GL_DATE |
1398 +===========================================================================*/
1399 PROCEDURE init_gt_variables
1400 IS
1401
1402 l_api_name CONSTANT VARCHAR2(30) := 'INIT_GT_VARIABLES';
1403
1404 BEGIN
1405 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1406 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
1407 G_PKG_NAME||': '||l_api_name||'()+');
1408
1409 END IF;
1410
1411 IF g_debug_flag = 'Y' THEN
1412 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
1413 END IF;
1414
1415 gt_vat_transaction_id.delete;
1416 gt_reporting_status_id.delete;
1417 gt_rep_entity_id.delete;
1418 gt_rep_context_entity_name.delete;
1419 gt_rep_context_entity_loc_id.delete;
1420 gt_taxpayer_id.delete;
1421 gt_org_information2.delete;
1422 gt_legal_authority_name.delete;
1423 gt_legal_auth_address_line2.delete;
1424 gt_legal_auth_address_line3.delete;
1425 gt_legal_auth_city.delete;
1426 gt_legal_contact_party_name.delete;
1427 gt_activity_code.delete;
1428 gt_ledger_id.delete;
1429 gt_ledger_name.delete;
1430 gt_chart_of_accounts_id.delete;
1431 gt_extract_source_ledger.delete;
1432 gt_establishment_id.delete;
1433 gt_internal_organization_id.delete;
1434 gt_application_id.delete;
1435 gt_entity_code.delete;
1436 gt_event_class_code.delete;
1437 gt_trx_id.delete;
1438 gt_trx_number.delete;
1439 gt_trx_description.delete;
1440 gt_trx_currency_code.delete;
1441 gt_trx_type_id.delete;
1442 gt_trx_type_mng.delete;
1443 gt_trx_line_id.delete;
1444 gt_trx_line_number.delete;
1445 gt_trx_line_description.delete;
1446 gt_trx_level_type.delete;
1447 gt_trx_line_type.delete;
1448 gt_trx_line_class.delete;
1449 gt_trx_class_mng.delete;
1450 gt_trx_date.delete;
1451 gt_trx_due_date.delete;
1452 gt_trx_communicated_date.delete;
1453 gt_product_id.delete;
1454 gt_functional_currency_code.delete;
1455 gt_currency_conversion_type.delete;
1456 gt_currency_conversion_date.delete;
1457 gt_currency_conversion_rate.delete;
1458 gt_territory_short_name.delete;
1459 gt_doc_seq_id.delete;
1460 gt_doc_seq_name.delete;
1461 gt_doc_seq_value.delete;
1462 gt_trx_line_amt.delete;
1463 gt_receipt_class_id.delete;
1464 gt_applied_from_appl_id.delete;
1465 gt_applied_from_entity_code.delete;
1466 gt_applied_from_event_cls_cd.delete;
1467 gt_applied_from_trx_id.delete;
1468 gt_applied_from_line_id.delete;
1469 gt_applied_from_trx_number.delete;
1470 gt_adjusted_doc_appl_id.delete;
1471 gt_adjusted_doc_entity_code.delete;
1472 gt_adjusted_doc_event_cls_cd.delete;
1473 gt_adjusted_doc_trx_id.delete;
1474 gt_adjusted_doc_number.delete;
1475 gt_adjusted_doc_date.delete;
1476 gt_applied_to_application_id.delete;
1477 gt_applied_to_entity_code.delete;
1478 gt_applied_to_event_cls_code.delete;
1479 gt_applied_to_trx_id.delete;
1480 gt_applied_to_trx_line_id.delete;
1481 gt_applied_to_trx_number.delete;
1482 gt_ref_doc_application_id.delete;
1483 gt_ref_doc_entity_code.delete;
1484 gt_ref_doc_event_class_code.delete;
1485 gt_ref_doc_trx_id.delete;
1486 gt_ref_doc_line_id.delete;
1487 gt_merchant_party_doc_num.delete;
1488 gt_merchant_party_name.delete;
1489 gt_merchant_party_reference.delete;
1490 gt_merchant_party_tax_reg_num.delete;
1491 gt_merchant_party_taxpayer_id.delete;
1492 gt_start_expense_date.delete;
1493 gt_taxable_line_source_table.delete;
1494 gt_tax_line_id.delete;
1495 gt_tax_line_number.delete;
1496 gt_tax_invoice_date.delete;
1497 gt_taxable_amt.delete;
1498 gt_taxable_amt_funcl_curr.delete;
1499 gt_tax_amt.delete;
1500 gt_tax_amt_funcl_curr.delete;
1501 gt_rec_tax_amt_tax_curr.delete;
1502 gt_nrec_tax_amt_tax_curr.delete;
1503 gt_taxable_disc_amt.delete;
1504 gt_taxable_disc_amt_fun_curr.delete;
1505 gt_tax_disc_amt.delete;
1506 gt_tax_disc_amt_fun_curr.delete;
1507 gt_tax_rate_id.delete;
1508 gt_tax_rate_code.delete;
1509 gt_tax_rate.delete;
1510 gt_tax_rate_code_name.delete;
1511 gt_tax_rate_code_description.delete;
1512 gt_tax_rate_vat_trx_type_code.delete;
1513 gt_tax_rate_vat_trx_type_desc.delete;
1514 gt_tax_rate_vat_trx_type_mng.delete;
1515 gt_tax_rate_reg_type_code.delete;
1516 gt_tax_type_code.delete;
1517 gt_tax_type_mng.delete;
1518 gt_tax_recovery_rate.delete;
1519 gt_tax_regime_code.delete;
1520 gt_tax.delete;
1521 gt_tax_jurisdiction_code.delete;
1522 gt_tax_status_id.delete;
1523 gt_tax_status_code.delete;
1524 gt_tax_currency_code.delete;
1525 gt_offset_tax_rate_code.delete;
1526 gt_billing_tp_name.delete;
1527 gt_billing_tp_number.delete;
1528 gt_billing_tp_tax_reg_num.delete;
1529 gt_billing_tp_taxpayer_id.delete;
1530 gt_billing_tp_party_number.delete;
1531 gt_billing_tp_id.delete;
1532 gt_billing_tp_tax_rep_flag.delete;
1533 gt_billing_tp_site_id.delete;
1534 gt_billing_tp_address_id.delete;
1535 gt_billing_tp_site_name.delete;
1536 gt_billing_tp_site_tx_reg_num.delete;
1537 gt_shipping_tp_name.delete;
1538 gt_shipping_tp_number.delete;
1539 gt_shipping_tp_tax_reg_num.delete;
1540 gt_shipping_tp_taxpayer_id.delete;
1541 gt_shipping_tp_id.delete;
1542 gt_shipping_tp_site_id.delete;
1543 gt_shipping_tp_address_id.delete;
1544 gt_shipping_tp_site_name.delete;
1545 gt_shipping_tp_site_tx_rg_num.delete;
1546 gt_bill_from_party_id.delete; -- Bug 16386556
1547 gt_bill_from_party_site_id.delete; -- Bug 16386556
1548 gt_banking_tp_name.delete;
1549 gt_banking_tp_taxpayer_id.delete;
1550 gt_bank_account_name.delete;
1551 gt_bank_account_num.delete;
1552 gt_bank_account_id.delete;
1553 gt_bank_branch_id.delete;
1554 gt_legal_entity_tax_reg_num.delete;
1555 gt_hq_estb_reg_number.delete;
1556 gt_tax_line_registration_num.delete;
1557 gt_cancelled_date.delete;
1558 gt_cancel_flag.delete;
1559 gt_offset_flag.delete;
1560 gt_posted_flag.delete;
1561 gt_mrc_tax_line_flag.delete;
1562 gt_reconciliation_flag.delete;
1563 gt_tax_recoverable_flag.delete;
1564 gt_reverse_flag.delete;
1565 gt_correction_flag.delete;
1566 gt_ar_cash_receipt_rev_stat.delete;
1567 gt_ar_cash_receipt_rev_date.delete;
1568 gt_payables_invoice_source.delete;
1569 gt_acctd_amount_dr.delete;
1570 gt_acctd_amount_cr.delete;
1571 gt_rec_application_status.delete;
1572 gt_vat_country_code.delete;
1573 gt_invoice_identifier.delete;
1574 gt_account_class.delete;
1575 gt_latest_rec_flag.delete;
1576 gt_jgzz_fiscal_code.delete;
1577 gt_tax_reference.delete;
1578 gt_pt_location.delete;
1579 gt_invoice_report_type.delete;
1580 gt_es_correction_year.delete;
1581 gt_es_correction_period.delete;
1582 gt_triangulation.delete;
1583 gt_document_sub_type.delete;
1584 gt_assessable_value.delete;
1585 gt_property_location.delete;
1586 gt_chk_vat_amount_paid.delete;
1587 gt_import_document_number.delete;
1588 gt_import_document_date.delete;
1589 gt_prl_no.delete;
1590 gt_property_rental.delete;
1591 gt_rates_reference.delete;
1592 gt_stair_num.delete;
1593 gt_floor_num.delete;
1594 gt_door_num.delete;
1595 gt_amount_applied.delete;
1596 gt_actg_event_type_code.delete;
1597 gt_actg_event_type_mng.delete;
1598 gt_actg_event_number.delete;
1599 gt_actg_event_status_flag.delete;
1600 gt_actg_event_status_mng.delete;
1601 gt_actg_category_code.delete;
1602 gt_actg_category_mng.delete;
1603 gt_accounting_date.delete;
1604 gt_gl_transfer_flag.delete;
1605 gt_actg_line_num.delete;
1606 gt_actg_line_type_code.delete;
1607 gt_actg_line_type_mng.delete;
1608 gt_actg_line_description.delete;
1609 gt_actg_stat_amt.delete;
1610 gt_actg_party_id.delete;
1611 gt_actg_party_site_id.delete;
1612 gt_actg_party_type.delete;
1613 gt_actg_event_id.delete;
1614 gt_actg_header_id.delete;
1615 gt_actg_line_id.delete;
1616 gt_actg_source_id.delete;
1617 gt_actg_source_table.delete;
1618 gt_actg_line_ccid.delete;
1619 gt_account_flexfield.delete;
1620 gt_account_description.delete;
1621 gt_period_name.delete;
1622 gt_trx_arap_balancing_seg.delete;
1623 gt_trx_arap_natural_account.delete;
1624 gt_trx_taxable_balancing_seg.delete;
1625 gt_trx_taxable_natural_acct.delete;
1626 gt_trx_tax_balancing_seg.delete;
1627 gt_trx_tax_natural_account.delete;
1628 gt_created_by.delete;
1629 gt_creation_date.delete;
1630 gt_last_updated_by.delete;
1631 gt_last_update_date.delete;
1632 gt_last_update_login.delete;
1633 gt_request_id.delete;
1634 gt_program_application_id.delete;
1635 gt_program_id.delete;
1636 gt_program_login_id.delete;
1637 gt_object_version_number.delete;
1638 gt_gl_date.delete;
1639 gt_trx_ctrl_actg_flexfield.delete;
1640 gt_tax_origin.delete;
1641 gt_reporting_code.delete;
1642 gt_def_rec_settlement_opt_code.delete;
1643 gt_taxable_item_source_id.delete;
1644
1645 -- BUG 9765740 Spanish Modelo 349 ESL EPL Changes
1646 gt_intra_eu_trx_type_code.delete;
1647
1648
1649 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1650 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
1651 G_PKG_NAME||': ' ||l_api_name||'()-');
1652 END IF;
1653
1654 IF g_debug_flag = 'Y' THEN
1655 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
1656 END IF;
1657
1658 EXCEPTION
1659 WHEN OTHERS THEN
1660 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1661 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1662 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1663 END IF;
1664
1665 IF g_debug_flag = 'Y' THEN
1666 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
1667 END IF;
1668 -- l_GLOBAL_VARIABLES_REC.RETCODE := 2;
1669
1670 END init_gt_variables;
1671
1672 /*===========================================================================+
1673 | PROCEDURE |
1674 | fetch_tax_data |
1675 | DESCRIPTION |
1676 | This procedure fetches tax data from TRL interface tables using bulk |
1677 | collect and calls insert_tax_data to perform a bulk insert into |
1678 | jg_zz_vat_trx_details_table. |
1679 | SCOPE - Private |
1680 | |
1681 | NOTES |
1682 | |
1683 | MODIFICATION HISTORY |
1684 | Date Author Description |
1685 | ============ ============== ================================= |
1686 | 23-Jan-2006 RBASKER Initial Version. |
1687 | 28-Mar-2006 RBASKER Incorporated changes for XBuild4. |
1688 | 28-Apr-2006 RBASKER Bug: 5169118 - Fixed issues identified |
1689 | during Unit Testing. |
1690 | 28-May-2006 RBASKER Bug: 5182167 - Fixed issues identified |
1691 | during Unit Testing. Also, modified GDF |
1692 | column mappings to reflect TRL changes. |
1693 | 18-Sep-2006 RBASKER Bug 5509788 - Changes made to fetch GL_DATE|
1694 | TAX_ORIGIN,TRX_CONTROL_ACCOUNT_FLEXFIELD |
1695 | 22-Nov-2006 RBASKER Bug 5673426 - Corrected the mapping |
1696 | columns of ADJUSTED_DOC_XXXX columns in |
1697 | fetch_tax_date and insert_tax_data proc |
1698 | |
1699 +===========================================================================*/
1700
1701 PROCEDURE fetch_tax_data (
1702 P_GLOBAL_VARIABLES_REC IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
1703 x_request_id IN NUMBER)
1704 IS
1705
1706 TYPE trl_tax_data_curtype IS REF CURSOR;
1707 trl_tax_data_csr trl_tax_data_curtype;
1708 i BINARY_INTEGER;
1709 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_TAX_DATA';
1710 l_correction_yes CONSTANT VARCHAR2(1) := 'Y';
1711 l_correction_no CONSTANT VARCHAR2(1) := 'N';
1712 l_tax_invoice_date_low DATE := P_GLOBAL_VARIABLES_REC.tax_invoice_date_low;
1713 BEGIN
1714
1715 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1716 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
1717 G_PKG_NAME||': '||l_api_name||'()+');
1718 END IF;
1719
1720 IF g_debug_flag = 'Y' THEN
1721 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
1722 END IF;
1723
1724 OPEN trl_tax_data_csr FOR 'SELECT
1725 CON.REP_ENTITY_ID ,
1726 CON.REP_CONTEXT_ENTITY_NAME ,
1727 CON.REP_CONTEXT_ENTITY_LOCATION_ID ,
1728 CON.TAXPAYER_ID ,
1729 CON.ORG_INFORMATION2 ,
1730 CON.LEGAL_AUTHORITY_NAME ,
1731 CON.LEGAL_AUTH_ADDRESS_LINE2 ,
1732 CON.LEGAL_AUTH_ADDRESS_LINE3 ,
1733 CON.LEGAL_AUTH_CITY ,
1734 CON.LEGAL_CONTACT_PARTY_NAME ,
1735 CON.ACTIVITY_CODE ,
1736 DET.LEDGER_ID ,
1737 DET.LEDGER_NAME ,
1738 DET.CHART_OF_ACCOUNTS_ID ,
1739 DET.EXTRACT_SOURCE_LEDGER ,
1740 DET.ESTABLISHMENT_ID ,
1741 DET.INTERNAL_ORGANIZATION_ID ,
1742 DET.APPLICATION_ID ,
1743 DET.ENTITY_CODE ,
1744 DET.EVENT_CLASS_CODE ,
1745 DET.TRX_ID ,
1746 DET.TRX_NUMBER ,
1747 DET.TRX_DESCRIPTION ,
1748 DET.TRX_CURRENCY_CODE ,
1749 DET.TRX_TYPE_ID ,
1750 DET.TRX_TYPE_MNG ,
1751 DET.TRX_LINE_ID ,
1752 DET.TRX_LINE_NUMBER ,
1753 DET.TRX_LINE_DESCRIPTION ,
1754 DET.TRX_LEVEL_TYPE ,
1755 DET.TRX_LINE_TYPE ,
1756 DET.TRX_LINE_CLASS ,
1757 DET.TRX_CLASS_MNG ,
1758 DET.TRX_DATE ,
1759 DET.TRX_DUE_DATE ,
1760 DET.TRX_COMMUNICATED_DATE ,
1761 DET.PRODUCT_ID ,
1762 DET.FUNCTIONAL_CURRENCY_CODE ,
1763 DET.CURRENCY_CONVERSION_TYPE ,
1764 DET.CURRENCY_CONVERSION_DATE ,
1765 DET.CURRENCY_CONVERSION_RATE ,
1766 DET.TERRITORY_SHORT_NAME ,
1767 DET.DOC_SEQ_ID ,
1768 DET.DOC_SEQ_NAME ,
1769 DET.DOC_SEQ_VALUE ,
1770 DET.TRX_LINE_AMT ,
1771 DET.RECEIPT_CLASS_ID ,
1772 DET.APPLIED_FROM_APPLICATION_ID ,
1773 DET.APPLIED_FROM_ENTITY_CODE ,
1774 DET.APPLIED_FROM_EVENT_CLASS_CODE ,
1775 DET.APPLIED_FROM_TRX_ID ,
1776 DET.APPLIED_FROM_LINE_ID ,
1777 DET.APPLIED_FROM_TRX_NUMBER ,
1778 DET.APPLIED_TO_APPLICATION_ID ,
1779 DET.APPLIED_TO_ENTITY_CODE ,
1780 DET.APPLIED_TO_EVENT_CLASS_CODE ,
1781 DET.APPLIED_TO_TRX_ID ,
1782 DET.APPLIED_TO_TRX_LINE_ID ,
1783 DET.APPLIED_TO_TRX_NUMBER ,
1784 DET.ADJUSTED_DOC_APPLICATION_ID ,
1785 DET.ADJUSTED_DOC_ENTITY_CODE ,
1786 DET.ADJUSTED_DOC_EVENT_CLASS_CODE ,
1787 DET.ADJUSTED_DOC_TRX_ID ,
1788 DET.ADJUSTED_DOC_NUMBER ,
1789 DET.ADJUSTED_DOC_DATE ,
1790 DET.REF_DOC_APPLICATION_ID ,
1791 DET.REF_DOC_ENTITY_CODE ,
1792 DET.REF_DOC_EVENT_CLASS_CODE ,
1793 DET.REF_DOC_TRX_ID ,
1794 DET.REF_DOC_LINE_ID ,
1795 DET.MERCHANT_PARTY_DOCUMENT_NUMBER ,
1796 DET.MERCHANT_PARTY_NAME ,
1797 DET.MERCHANT_PARTY_REFERENCE ,
1798 DET.MERCHANT_PARTY_TAX_REG_NUMBER ,
1799 DET.MERCHANT_PARTY_TAXPAYER_ID ,
1800 DET.START_EXPENSE_DATE ,
1801 DET.TAXABLE_LINE_SOURCE_TABLE ,
1802 DET.TAX_LINE_ID ,
1803 DET.TAX_LINE_NUMBER ,
1804 DET.TAX_INVOICE_DATE ,
1805 DET.TAXABLE_AMT ,
1806 DET.TAXABLE_AMT_FUNCL_CURR ,
1807 DET.TAX_AMT ,
1808 DET.TAX_AMT_FUNCL_CURR ,
1809 DET.REC_TAX_AMT_TAX_CURR ,
1810 DET.NREC_TAX_AMT_TAX_CURR ,
1811 DET.TAXABLE_DISC_AMT ,
1812 DET.TAXABLE_DISC_AMT_FUNCL_CURR ,
1813 DET.TAX_DISC_AMT ,
1814 DET.TAX_DISC_AMT_FUNCL_CURR ,
1815 DET.TAX_RATE_ID ,
1816 DET.TAX_RATE_CODE ,
1817 DET.TAX_RATE ,
1818 DET.TAX_RATE_CODE_NAME ,
1819 DET.TAX_RATE_CODE_DESCRIPTION ,
1820 DET.TAX_RATE_VAT_TRX_TYPE_CODE ,
1821 DET.TAX_RATE_VAT_TRX_TYPE_DESC ,
1822 DET.TAX_RATE_CODE_VAT_TRX_TYPE_MNG ,
1823 DET.TAX_RATE_REGISTER_TYPE_CODE ,
1824 DET.TAX_TYPE_CODE ,
1825 DET.TAX_TYPE_MNG ,
1826 DET.TAX_RECOVERY_RATE ,
1827 DET.TAX_REGIME_CODE ,
1828 DET.TAX ,
1829 DET.TAX_JURISDICTION_CODE ,
1830 DET.TAX_STATUS_ID ,
1831 DET.TAX_STATUS_CODE ,
1832 DET.TAX_CURRENCY_CODE ,
1833 DET.OFFSET_TAX_RATE_CODE ,
1834 DET.BILLING_TP_NAME ,
1835 DET.BILLING_TP_NUMBER ,
1836 DET.BILLING_TP_TAX_REG_NUM ,
1837 DET.BILLING_TP_TAXPAYER_ID ,
1838 DET.BILLING_TP_PARTY_NUMBER ,
1839 DET.BILLING_TRADING_PARTNER_ID ,
1840 DET.BILLING_TP_TAX_REPORTING_FLAG ,
1841 DET.BILLING_TP_SITE_ID ,
1842 DET.BILLING_TP_ADDRESS_ID ,
1843 DET.BILLING_TP_SITE_NAME ,
1844 DET.BILLING_TP_SITE_TAX_REG_NUM ,
1845 DET.SHIPPING_TP_NAME ,
1846 DET.SHIPPING_TP_NUMBER ,
1847 DET.SHIPPING_TP_TAX_REG_NUM ,
1848 DET.SHIPPING_TP_TAXPAYER_ID ,
1849 DET.SHIPPING_TRADING_PARTNER_ID ,
1850 DET.SHIPPING_TP_SITE_ID ,
1851 DET.SHIPPING_TP_ADDRESS_ID ,
1852 DET.SHIPPING_TP_SITE_NAME ,
1853 DET.SHIPPING_TP_SITE_TAX_REG_NUM ,
1854 DET.BILL_FROM_PARTY_ID , -- Bug 16386556
1855 DET.BILL_FROM_PARTY_SITE_ID , -- Bug 16386556
1856 DET.BANKING_TP_NAME ,
1857 DET.BANKING_TP_TAXPAYER_ID ,
1858 DET.BANK_ACCOUNT_NAME ,
1859 DET.BANK_ACCOUNT_NUM ,
1860 DET.BANK_ACCOUNT_ID ,
1861 DET.BANK_BRANCH_ID ,
1862 DET.LEGAL_ENTITY_TAX_REG_NUMBER ,
1863 DET.HQ_ESTB_REG_NUMBER ,
1864 DET.TAX_LINE_REGISTRATION_NUMBER ,
1865 DET.CANCELLED_DATE ,
1866 DET.CANCEL_FLAG ,
1867 DET.OFFSET_FLAG ,
1868 DET.POSTED_FLAG ,
1869 DET.MRC_TAX_LINE_FLAG ,
1870 DET.RECONCILIATION_FLAG ,
1871 DET.TAX_RECOVERABLE_FLAG ,
1872 DET.REVERSE_FLAG ,
1873 DECODE (sign(:l_tax_invoice_date_low - DET.TAX_INVOICE_DATE)
1874 , -1,:correction_no
1875 , 0, :correction_no, :correction_yes) , /* Correction Flag */
1876 DET.AR_CASH_RECEIPT_REVERSE_STATUS ,
1877 DET.AR_CASH_RECEIPT_REVERSE_DATE ,
1878 EXT.ATTRIBUTE20 , /* Payables_Invoice_Source */
1879 DET.ACCTD_AMOUNT_DR ,
1880 DET.ACCTD_AMOUNT_CR ,
1881 DET.REC_APPLICATION_STATUS ,
1882 EXT.ATTRIBUTE10 , /* vat country code in fsp */
1883 DET.SUB_LEDGER_INVOICE_IDENTIFIER , /* gl_je_lines.Invoice_identifier*/
1884 DET.ACCOUNT_CLASS ,
1885 DET.LATEST_REC_FLAG ,
1886 NVL(DET.BILLING_TP_TAXPAYER_ID, DET.SHIPPING_TP_TAXPAYER_ID),/*jgzz_fiscal_code*/
1887 NVL(DET.BILLING_TP_TAX_REG_NUM , DET.SHIPPING_TP_TAX_REG_NUM),/*tax_reference */
1888 EXT.ATTRIBUTE1 , /* PT_LOCATION */
1889 EXT.ATTRIBUTE23 , /* MODELO TYPE */
1890 EXT.ATTRIBUTE11 , /* ES Correctio year */
1891 EXT.ATTRIBUTE12 , /* ES Correctio period */
1892 EXT.ATTRIBUTE13 , /* Triangulation */
1893 DET.DOCUMENT_SUB_TYPE ,
1894 DET.ASSESSABLE_VALUE ,
1895 EXT.ATTRIBUTE9 , /* Property location */
1896 EXT.ATTRIBUTE8 , /* Chk VAT Amount paid */
1897 EXT.ATTRIBUTE21 , /* Import document Number */
1898 fnd_Date.canonical_To_Date(EXT.ATTRIBUTE22) , /* Import document Date */
1899 --EXT.ATTRIBUTE14 , /* prl no */ {PRL NO is used for tax class}
1900 EXT.ATTRIBUTE24 , /* Tax Class */
1901 EXT.ATTRIBUTE15 , /*property_rental */
1902 EXT.ATTRIBUTE16 , /*rates_reference */
1903 EXT.ATTRIBUTE17 , /*stair_num*/
1904 EXT.ATTRIBUTE18 , /*floor_num*/
1905 EXT.ATTRIBUTE19 , /*door_num*/
1906 DET.AMOUNT_APPLIED ,
1907 ACT.ACTG_EVENT_TYPE_CODE ,
1908 ACT.ACTG_EVENT_TYPE_MNG ,
1909 ACT.ACTG_EVENT_NUMBER ,
1910 ACT.ACTG_EVENT_STATUS_FLAG ,
1911 ACT.ACTG_EVENT_STATUS_MNG ,
1912 ACT.ACTG_CATEGORY_CODE ,
1913 ACT.ACTG_CATEGORY_MNG ,
1914 ACT.ACCOUNTING_DATE ,
1915 ACT.GL_TRANSFER_FLAG ,
1916 ACT.ACTG_LINE_NUM ,
1917 ACT.ACTG_LINE_TYPE_CODE ,
1918 ACT.ACTG_LINE_TYPE_MNG ,
1919 ACT.ACTG_LINE_DESCRIPTION ,
1920 ACT.ACTG_STAT_AMT ,
1921 ACT.ACTG_PARTY_ID ,
1922 ACT.ACTG_PARTY_SITE_ID ,
1923 ACT.ACTG_PARTY_TYPE ,
1924 ACT.ACTG_EVENT_ID ,
1925 ACT.ACTG_HEADER_ID ,
1926 NULL ,
1927 --bug13390487 ACT.ACTG_SOURCE_ID ,
1928 DET.ACTG_SOURCE_ID , --bug13390487
1929 ACT.ACTG_SOURCE_TABLE ,
1930 ACT.ACTG_LINE_CCID ,
1931 ACT.ACCOUNT_FLEXFIELD ,
1932 ACT.ACCOUNT_DESCRIPTION ,
1933 ACT.PERIOD_NAME ,
1934 ACT.TRX_ARAP_BALANCING_SEGMENT ,
1935 ACT.TRX_ARAP_NATURAL_ACCOUNT ,
1936 ACT.TRX_TAXABLE_BALANCING_SEGMENT ,
1937 ACT.TRX_TAXABLE_NATURAL_ACCOUNT ,
1938 ACT.TRX_TAX_BALANCING_SEGMENT ,
1939 ACT.TRX_TAX_NATURAL_ACCOUNT ,
1940 DET.GL_DATE ,
1941 ACT.TRX_CONTROL_ACCOUNT_FLEXFIELD ,
1942 EXT.ATTRIBUTE25 /* tax_origin*/ ,
1943 EXT.ATTRIBUTE26 /* Reporting Code */,
1944 DET.DEF_REC_SETTLEMENT_OPTION_CODE /* Deferred Settlement Code*/,
1945 DET.TAXABLE_ITEM_SOURCE_ID /*TAXABLE_ITEM_SOURCE_ID */,
1946
1947 -- BUG 9765740 Spanish Modelo 349 ESL EPL Changes
1948 EXT.ATTRIBUTE27 --- temporarily adding same attribute as reporting_code
1949 FROM ZX_REP_CONTEXT_T CON,
1950 ZX_REP_TRX_DETAIL_T DET,
1951 ZX_REP_TRX_JX_EXT_T EXT,
1952 ZX_REP_ACTG_EXT_T ACT
1953 WHERE CON.REQUEST_ID = :request_id
1954 AND DET.REQUEST_ID = CON.REQUEST_ID
1955 AND DET.REP_CONTEXT_ID = CON.REP_CONTEXT_ID
1956 AND EXT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID
1957 AND ACT.DETAIL_TAX_LINE_ID(+) = DET.DETAIL_TAX_LINE_ID
1958 AND NOT EXISTS (SELECT 1
1959 FROM JG_ZZ_VAT_TRX_DETAILS JZVTD
1960 WHERE JZVTD.TRX_ID = DET.TRX_ID
1961 AND JZVTD.APPLICATION_ID = DET.APPLICATION_ID
1962 AND JZVTD.ENTITY_CODE = DET.ENTITY_CODE
1963 AND JZVTD.EVENT_CLASS_CODE = DET.EVENT_CLASS_CODE
1964 /************************************************************************
1965 Bug 7379550 Start: Added the below 2 conditions also so that
1966 VAT Selection Process is able to fetch data when 1 transaction
1967 has multiple Tax Regime Code
1968 *************************************************************************/
1969 AND JZVTD.TRX_LINE_ID = DET.TRX_LINE_ID
1970 AND NVL(JZVTD.TAX_LINE_ID,-1) = NVL(DET.TAX_LINE_ID,-1)
1971 --AND NVL(JZVTD.CANCEL_FLAG,-1) = NVL(DET.CANCEL_FLAG,-1))
1972 AND NVL(JZVTD.ACTG_SOURCE_ID,-1) = NVL(DET.ACTG_SOURCE_ID,-1))'
1973 /************************************************************************
1974 Bug 7379550 End
1975 *************************************************************************/
1976 USING l_tax_invoice_date_low, l_correction_no, l_correction_no,
1977 l_correction_yes, x_request_id;
1978
1979 init_gt_variables;
1980
1981 LOOP
1982 FETCH trl_tax_data_csr BULK COLLECT INTO
1983 gt_rep_entity_id,
1984 gt_rep_context_entity_name,
1985 gt_rep_context_entity_loc_id,
1986 gt_taxpayer_id,
1987 gt_org_information2,
1988 gt_legal_authority_name,
1989 gt_legal_auth_address_line2,
1990 gt_legal_auth_address_line3,
1991 gt_legal_auth_city,
1992 gt_legal_contact_party_name,
1993 gt_activity_code,
1994 gt_ledger_id,
1995 gt_ledger_name,
1996 gt_chart_of_accounts_id,
1997 gt_extract_source_ledger,
1998 gt_establishment_id,
1999 gt_internal_organization_id,
2000 gt_application_id,
2001 gt_entity_code,
2002 gt_event_class_code,
2003 gt_trx_id,
2004 gt_trx_number,
2005 gt_trx_description,
2006 gt_trx_currency_code,
2007 gt_trx_type_id,
2008 gt_trx_type_mng,
2009 gt_trx_line_id,
2010 gt_trx_line_number,
2011 gt_trx_line_description,
2012 gt_trx_level_type,
2013 gt_trx_line_type,
2014 gt_trx_line_class,
2015 gt_trx_class_mng,
2016 gt_trx_date,
2017 gt_trx_due_date,
2018 gt_trx_communicated_date,
2019 gt_product_id,
2020 gt_functional_currency_code,
2021 gt_currency_conversion_type,
2022 gt_currency_conversion_date,
2023 gt_currency_conversion_rate,
2024 gt_territory_short_name,
2025 gt_doc_seq_id,
2026 gt_doc_seq_name,
2027 gt_doc_seq_value,
2028 gt_trx_line_amt,
2029 gt_receipt_class_id,
2030 gt_applied_from_appl_id,
2031 gt_applied_from_entity_code,
2032 gt_applied_from_event_cls_cd,
2033 gt_applied_from_trx_id,
2034 gt_applied_from_line_id,
2035 gt_applied_from_trx_number,
2036 gt_applied_to_application_id,
2037 gt_applied_to_entity_code,
2038 gt_applied_to_event_cls_code,
2039 gt_applied_to_trx_id,
2040 gt_applied_to_trx_line_id,
2041 gt_applied_to_trx_number,
2042 gt_adjusted_doc_appl_id,
2043 gt_adjusted_doc_entity_code,
2044 gt_adjusted_doc_event_cls_cd,
2045 gt_adjusted_doc_trx_id,
2046 gt_adjusted_doc_number,
2047 gt_adjusted_doc_date,
2048 gt_ref_doc_application_id,
2049 gt_ref_doc_entity_code,
2050 gt_ref_doc_event_class_code,
2051 gt_ref_doc_trx_id,
2052 gt_ref_doc_line_id,
2053 gt_merchant_party_doc_num,
2054 gt_merchant_party_name,
2055 gt_merchant_party_reference,
2056 gt_merchant_party_tax_reg_num,
2057 gt_merchant_party_taxpayer_id,
2058 gt_start_expense_date,
2059 gt_taxable_line_source_table,
2060 gt_tax_line_id,
2061 gt_tax_line_number,
2062 gt_tax_invoice_date,
2063 gt_taxable_amt,
2064 gt_taxable_amt_funcl_curr,
2065 gt_tax_amt,
2066 gt_tax_amt_funcl_curr,
2067 gt_rec_tax_amt_tax_curr,
2068 gt_nrec_tax_amt_tax_curr,
2069 gt_taxable_disc_amt,
2070 gt_taxable_disc_amt_fun_curr,
2071 gt_tax_disc_amt,
2072 gt_tax_disc_amt_fun_curr,
2073 gt_tax_rate_id,
2074 gt_tax_rate_code,
2075 gt_tax_rate,
2076 gt_tax_rate_code_name,
2077 gt_tax_rate_code_description,
2078 gt_tax_rate_vat_trx_type_code,
2079 gt_tax_rate_vat_trx_type_desc,
2080 gt_tax_rate_vat_trx_type_mng,
2081 gt_tax_rate_reg_type_code,
2082 gt_tax_type_code,
2083 gt_tax_type_mng,
2084 gt_tax_recovery_rate,
2085 gt_tax_regime_code,
2086 gt_tax,
2087 gt_tax_jurisdiction_code,
2088 gt_tax_status_id,
2089 gt_tax_status_code,
2090 gt_tax_currency_code,
2091 gt_offset_tax_rate_code,
2092 gt_billing_tp_name,
2093 gt_billing_tp_number,
2094 gt_billing_tp_tax_reg_num,
2095 gt_billing_tp_taxpayer_id,
2096 gt_billing_tp_party_number,
2097 gt_billing_tp_id,
2098 gt_billing_tp_tax_rep_flag,
2099 gt_billing_tp_site_id,
2100 gt_billing_tp_address_id,
2101 gt_billing_tp_site_name,
2102 gt_billing_tp_site_tx_reg_num,
2103 gt_shipping_tp_name,
2104 gt_shipping_tp_number,
2105 gt_shipping_tp_tax_reg_num,
2106 gt_shipping_tp_taxpayer_id,
2107 gt_shipping_tp_id,
2108 gt_shipping_tp_site_id,
2109 gt_shipping_tp_address_id,
2110 gt_shipping_tp_site_name,
2111 gt_shipping_tp_site_tx_rg_num,
2112 gt_bill_from_party_id, -- Bug 16386556
2113 gt_bill_from_party_site_id, -- Bug 16386556
2114 gt_banking_tp_name,
2115 gt_banking_tp_taxpayer_id,
2116 gt_bank_account_name,
2117 gt_bank_account_num,
2118 gt_bank_account_id,
2119 gt_bank_branch_id,
2120 gt_legal_entity_tax_reg_num,
2121 gt_hq_estb_reg_number,
2122 gt_tax_line_registration_num,
2123 gt_cancelled_date,
2124 gt_cancel_flag,
2125 gt_offset_flag,
2126 gt_posted_flag,
2127 gt_mrc_tax_line_flag,
2128 gt_reconciliation_flag,
2129 gt_tax_recoverable_flag,
2130 gt_reverse_flag,
2131 gt_correction_flag,
2132 gt_ar_cash_receipt_rev_stat,
2133 gt_ar_cash_receipt_rev_date,
2134 gt_payables_invoice_source,
2135 gt_acctd_amount_dr,
2136 gt_acctd_amount_cr,
2137 gt_rec_application_status,
2138 gt_vat_country_code,
2139 gt_invoice_identifier,
2140 gt_account_class,
2141 gt_latest_rec_flag,
2142 gt_jgzz_fiscal_code,
2143 gt_tax_reference,
2144 gt_pt_location,
2145 gt_invoice_report_type,
2146 gt_es_correction_year,
2147 gt_es_correction_period,
2148 gt_triangulation,
2149 gt_document_sub_type,
2150 gt_assessable_value,
2151 gt_property_location,
2152 gt_chk_vat_amount_paid,
2153 gt_import_document_number,
2154 gt_import_document_date,
2155 gt_prl_no,
2156 gt_property_rental,
2157 gt_rates_reference,
2158 gt_stair_num,
2159 gt_floor_num,
2160 gt_door_num,
2161 gt_amount_applied,
2162 gt_actg_event_type_code,
2163 gt_actg_event_type_mng,
2164 gt_actg_event_number,
2165 gt_actg_event_status_flag,
2166 gt_actg_event_status_mng,
2167 gt_actg_category_code,
2168 gt_actg_category_mng,
2169 gt_accounting_date,
2170 gt_gl_transfer_flag,
2171 gt_actg_line_num,
2172 gt_actg_line_type_code,
2173 gt_actg_line_type_mng,
2174 gt_actg_line_description,
2175 gt_actg_stat_amt,
2176 gt_actg_party_id,
2177 gt_actg_party_site_id,
2178 gt_actg_party_type,
2179 gt_actg_event_id,
2180 gt_actg_header_id,
2181 gt_actg_line_id,
2182 gt_actg_source_id,
2183 gt_actg_source_table,
2184 gt_actg_line_ccid,
2185 gt_account_flexfield,
2186 gt_account_description,
2187 gt_period_name,
2188 gt_trx_arap_balancing_seg,
2189 gt_trx_arap_natural_account,
2190 gt_trx_taxable_balancing_seg,
2191 gt_trx_taxable_natural_acct,
2192 gt_trx_tax_balancing_seg,
2193 gt_trx_tax_natural_account,
2194 gt_gl_date,
2195 gt_trx_ctrl_actg_flexfield,
2196 gt_tax_origin,
2197 gt_reporting_code,
2198 gt_def_rec_settlement_opt_code,
2199 gt_taxable_item_source_id,
2200
2201 -- BUG 9765740 Spanish Modelo 349 ESL EPL Changes
2202 gt_intra_eu_trx_type_code
2203 LIMIT C_LINES_PER_COMMIT;
2204
2205 insert_tax_data;
2206 COMMIT;
2207 init_gt_variables;
2208 EXIT WHEN trl_tax_data_csr%NOTFOUND;
2209
2210 END LOOP;
2211 CLOSE trl_tax_data_csr;
2212 IF g_debug_flag = 'Y' THEN
2213 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
2214 END IF;
2215
2216 EXCEPTION
2217 WHEN OTHERS THEN
2218 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2219
2220 IF g_debug_flag = 'Y' THEN
2221 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2222 END IF;
2223
2224 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
2225 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
2226 END IF;
2227 p_global_variables_rec.retcode := 2;
2228 RETURN;
2229 END fetch_tax_data;
2230
2231 /*===========================================================================+
2232 | PROCEDURE |
2233 | insert_tax_data |
2234 | DESCRIPTION |
2235 | This procedure bulk inserts tax data into JG_ZZ_VAT_TRX_DETAILS table |
2236 | |
2237 | SCOPE - Private |
2238 | |
2239 | NOTES |
2240 | |
2241 | MODIFICATION HISTORY |
2242 | Date Author Description |
2243 | ============ ============== ================================= |
2244 | 23-Jan-2006 RBASKER Initial Version. |
2245 | 28-Apr-2006 RBASKER Bug: 5169118 - Fixed issues identified |
2246 | during Unit Testing. |
2247 | 18-Sep-2006 RBASKER Bug 5509788 Changes made to insert gl_date|
2248 | TAX_ORIGIN,TRX_CONTROL_ACCOUNT_FLEXFIELD |
2249 | 22-Nov-2006 RBASKER Bug 5673426 - Corrected the mapping |
2250 | columns of ADJUSTED_DOC_XXXX columns in |
2251 | fetch_tax_date and insert_tax_data proc |
2252 | |
2253 +===========================================================================*/
2254
2255 PROCEDURE insert_tax_data
2256 IS
2257 l_count NUMBER;
2258 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_TAX_DATA';
2259 BEGIN
2260
2261 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2262 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name ,
2263 G_PKG_NAME||': '||l_api_name||'(+)');
2264 END IF;
2265
2266 IF g_debug_flag = 'Y' THEN
2267 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
2268 END IF;
2269
2270 l_count := GT_TRX_ID.COUNT;
2271
2272
2273 IF (g_level_procedure >= g_current_runtime_level ) THEN
2274 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name ,
2275 ' Record Count = ' ||to_char(l_count));
2276 END IF;
2277
2278 if g_selection_process_id is null then
2279 select JG_ZZ_VAT_REP_STATUS_S1.NEXTVAL
2280 into g_selection_process_id from dual;
2281 end if;
2282
2283
2284 FORALL i IN 1 .. l_count
2285
2286 INSERT INTO JG_ZZ_VAT_TRX_DETAILS(
2287 VAT_TRANSACTION_ID ,
2288 REPORTING_STATUS_ID ,
2289 SELECTION_PROCESS_ID ,
2290 FINAL_REPORTING_ID ,
2291 REP_ENTITY_ID ,
2292 REP_CONTEXT_ENTITY_NAME ,
2293 REP_CONTEXT_ENTITY_LOCATION_ID ,
2294 TAXPAYER_ID ,
2295 ORG_INFORMATION2 ,
2296 LEGAL_AUTHORITY_NAME ,
2297 LEGAL_AUTH_ADDRESS_LINE2 ,
2298 LEGAL_AUTH_ADDRESS_LINE3 ,
2299 LEGAL_AUTH_CITY ,
2300 LEGAL_CONTACT_PARTY_NAME ,
2301 ACTIVITY_CODE ,
2302 LEDGER_ID ,
2303 LEDGER_NAME ,
2304 CHART_OF_ACCOUNTS_ID ,
2305 EXTRACT_SOURCE_LEDGER ,
2306 ESTABLISHMENT_ID ,
2307 INTERNAL_ORGANIZATION_ID ,
2308 APPLICATION_ID ,
2309 ENTITY_CODE ,
2310 EVENT_CLASS_CODE ,
2311 TRX_ID ,
2312 TRX_NUMBER ,
2313 TRX_DESCRIPTION ,
2314 TRX_CURRENCY_CODE ,
2315 TRX_TYPE_ID ,
2316 TRX_TYPE_MNG ,
2317 TRX_LINE_ID ,
2318 TRX_LINE_NUMBER ,
2319 TRX_LINE_DESCRIPTION ,
2320 TRX_LEVEL_TYPE ,
2321 TRX_LINE_TYPE ,
2322 TRX_LINE_CLASS ,
2323 TRX_CLASS_MNG ,
2324 TRX_DATE ,
2325 TRX_DUE_DATE ,
2326 TRX_COMMUNICATED_DATE ,
2327 PRODUCT_ID ,
2328 FUNCTIONAL_CURRENCY_CODE ,
2329 CURRENCY_CONVERSION_TYPE ,
2330 CURRENCY_CONVERSION_DATE ,
2331 CURRENCY_CONVERSION_RATE ,
2332 TERRITORY_SHORT_NAME ,
2333 DOC_SEQ_ID ,
2334 DOC_SEQ_NAME ,
2335 DOC_SEQ_VALUE ,
2336 TRX_LINE_AMT ,
2337 RECEIPT_CLASS_ID ,
2338 APPLIED_FROM_APPLICATION_ID ,
2339 APPLIED_FROM_ENTITY_CODE ,
2340 APPLIED_FROM_EVENT_CLASS_CODE ,
2341 APPLIED_FROM_TRX_ID ,
2342 APPLIED_FROM_LINE_ID ,
2343 APPLIED_FROM_TRX_NUMBER ,
2344 APPLIED_TO_APPLICATION_ID ,
2345 APPLIED_TO_ENTITY_CODE ,
2346 APPLIED_TO_EVENT_CLASS_CODE ,
2347 APPLIED_TO_TRX_ID ,
2348 APPLIED_TO_TRX_LINE_ID ,
2349 APPLIED_TO_TRX_NUMBER ,
2350 ADJUSTED_DOC_APPLICATION_ID ,
2351 ADJUSTED_DOC_ENTITY_CODE ,
2352 ADJUSTED_DOC_EVENT_CLASS_CODE ,
2353 ADJUSTED_DOC_TRX_ID ,
2354 ADJUSTED_DOC_NUMBER ,
2355 ADJUSTED_DOC_DATE ,
2356 REF_DOC_APPLICATION_ID ,
2357 REF_DOC_ENTITY_CODE ,
2358 REF_DOC_EVENT_CLASS_CODE ,
2359 REF_DOC_TRX_ID ,
2360 REF_DOC_LINE_ID ,
2361 MERCHANT_PARTY_DOCUMENT_NUMBER ,
2362 MERCHANT_PARTY_NAME ,
2363 MERCHANT_PARTY_REFERENCE ,
2364 MERCHANT_PARTY_TAX_REG_NUMBER ,
2365 MERCHANT_PARTY_TAXPAYER_ID ,
2366 START_EXPENSE_DATE ,
2367 TAXABLE_LINE_SOURCE_TABLE ,
2368 TAX_LINE_ID ,
2369 TAX_LINE_NUMBER ,
2370 TAX_INVOICE_DATE ,
2371 TAXABLE_AMT ,
2372 TAXABLE_AMT_FUNCL_CURR ,
2373 TAX_AMT ,
2374 TAX_AMT_FUNCL_CURR ,
2375 REC_TAX_AMT_TAX_CURR ,
2376 NREC_TAX_AMT_TAX_CURR ,
2377 TAXABLE_DISC_AMT ,
2378 TAXABLE_DISC_AMT_FUNCL_CURR ,
2379 TAX_DISC_AMT ,
2380 TAX_DISC_AMT_FUNCL_CURR ,
2381 TAX_RATE_ID ,
2382 TAX_RATE_CODE ,
2383 TAX_RATE ,
2384 TAX_RATE_CODE_NAME ,
2385 TAX_RATE_CODE_DESCRIPTION ,
2386 TAX_RATE_VAT_TRX_TYPE_CODE ,
2387 TAX_RATE_VAT_TRX_TYPE_DESC ,
2388 TAX_RATE_CODE_VAT_TRX_TYPE_MNG ,
2389 TAX_RATE_REGISTER_TYPE_CODE ,
2390 TAX_TYPE_CODE ,
2391 TAX_TYPE_MNG ,
2392 TAX_RECOVERY_RATE ,
2393 TAX_REGIME_CODE ,
2394 TAX ,
2395 TAX_JURISDICTION_CODE ,
2396 TAX_STATUS_ID ,
2397 TAX_STATUS_CODE ,
2398 TAX_CURRENCY_CODE ,
2399 OFFSET_TAX_RATE_CODE ,
2400 BILLING_TP_NAME ,
2401 BILLING_TP_NUMBER ,
2402 BILLING_TP_TAX_REG_NUM ,
2403 BILLING_TP_TAXPAYER_ID ,
2404 BILLING_TP_PARTY_NUMBER ,
2405 BILLING_TRADING_PARTNER_ID ,
2406 BILLING_TP_TAX_REPORTING_FLAG ,
2407 BILLING_TP_SITE_ID ,
2408 BILLING_TP_ADDRESS_ID ,
2409 BILLING_TP_SITE_NAME ,
2410 BILLING_TP_SITE_TAX_REG_NUM ,
2411 SHIPPING_TP_NAME ,
2412 SHIPPING_TP_NUMBER ,
2413 SHIPPING_TP_TAX_REG_NUM ,
2414 SHIPPING_TP_TAXPAYER_ID ,
2415 SHIPPING_TRADING_PARTNER_ID ,
2416 SHIPPING_TP_SITE_ID ,
2417 SHIPPING_TP_ADDRESS_ID ,
2418 SHIPPING_TP_SITE_NAME ,
2419 SHIPPING_TP_SITE_TAX_REG_NUM ,
2420 BILL_FROM_PARTY_ID , -- Bug 16386556
2421 BILL_FROM_PARTY_SITE_ID , -- Bug 16386556
2422 BANKING_TP_NAME ,
2423 BANKING_TP_TAXPAYER_ID ,
2424 BANK_ACCOUNT_NAME ,
2425 BANK_ACCOUNT_NUM ,
2426 BANK_ACCOUNT_ID ,
2427 BANK_BRANCH_ID ,
2428 LEGAL_ENTITY_TAX_REG_NUMBER ,
2429 HQ_ESTB_REG_NUMBER ,
2430 TAX_LINE_REGISTRATION_NUMBER ,
2431 CANCELLED_DATE ,
2432 CANCEL_FLAG ,
2433 OFFSET_FLAG ,
2434 POSTED_FLAG ,
2435 MRC_TAX_LINE_FLAG ,
2436 RECONCILIATION_FLAG ,
2437 TAX_RECOVERABLE_FLAG ,
2438 REVERSE_FLAG ,
2439 CORRECTION_FLAG ,
2440 AR_CASH_RECEIPT_REVERSE_STATUS ,
2441 AR_CASH_RECEIPT_REVERSE_DATE ,
2442 PAYABLES_INVOICE_SOURCE ,
2443 ACCTD_AMOUNT_DR ,
2444 ACCTD_AMOUNT_CR ,
2445 REC_APPLICATION_STATUS ,
2446 VAT_COUNTRY_CODE ,
2447 INVOICE_IDENTIFIER ,
2448 ACCOUNT_CLASS ,
2449 LATEST_REC_FLAG ,
2450 JGZZ_FISCAL_CODE ,
2451 TAX_REFERENCE ,
2452 PT_LOCATION ,
2453 INVOICE_REPORT_TYPE ,
2454 ES_CORRECTION_YEAR ,
2455 ES_CORRECTION_PERIOD ,
2456 TRIANGULATION ,
2457 DOCUMENT_SUB_TYPE ,
2458 ASSESSABLE_VALUE ,
2459 PROPERTY_LOCATION ,
2460 CHK_VAT_AMOUNT_PAID ,
2461 IMPORT_DOCUMENT_NUMBER ,
2462 IMPORT_DOCUMENT_DATE ,
2463 PRL_NO ,
2464 PROPERTY_RENTAL ,
2465 RATES_REFERENCE ,
2466 STAIR_NUM ,
2467 FLOOR_NUM ,
2468 DOOR_NUM ,
2469 AMOUNT_APPLIED ,
2470 ACTG_EVENT_TYPE_CODE ,
2471 ACTG_EVENT_TYPE_MNG ,
2472 ACTG_EVENT_NUMBER ,
2473 ACTG_EVENT_STATUS_FLAG ,
2474 ACTG_EVENT_STATUS_MNG ,
2475 ACTG_CATEGORY_CODE ,
2476 ACTG_CATEGORY_MNG ,
2477 ACCOUNTING_DATE ,
2478 GL_TRANSFER_FLAG ,
2479 ACTG_LINE_NUM ,
2480 ACTG_LINE_TYPE_CODE ,
2481 ACTG_LINE_TYPE_MNG ,
2482 ACTG_LINE_DESCRIPTION ,
2483 ACTG_STAT_AMT ,
2484 ACTG_PARTY_ID ,
2485 ACTG_PARTY_SITE_ID ,
2486 ACTG_PARTY_TYPE ,
2487 ACTG_EVENT_ID ,
2488 ACTG_HEADER_ID ,
2489 ACTG_LINE_ID ,
2490 ACTG_SOURCE_ID ,
2491 ACTG_SOURCE_TABLE ,
2492 ACTG_LINE_CCID ,
2493 ACCOUNT_FLEXFIELD ,
2494 ACCOUNT_DESCRIPTION ,
2495 PERIOD_NAME ,
2496 TRX_ARAP_BALANCING_SEGMENT ,
2497 TRX_ARAP_NATURAL_ACCOUNT ,
2498 TRX_TAXABLE_BALANCING_SEGMENT ,
2499 TRX_TAXABLE_NATURAL_ACCOUNT ,
2500 TRX_TAX_BALANCING_SEGMENT ,
2501 TRX_TAX_NATURAL_ACCOUNT ,
2502 CREATED_BY ,
2503 CREATION_DATE ,
2504 LAST_UPDATED_BY ,
2505 LAST_UPDATE_DATE ,
2506 LAST_UPDATE_LOGIN ,
2507 REQUEST_ID ,
2508 PROGRAM_APPLICATION_ID ,
2509 PROGRAM_ID ,
2510 PROGRAM_LOGIN_ID ,
2511 OBJECT_VERSION_NUMBER ,
2512 GL_DATE ,
2513 TRX_CONTROL_ACCOUNT_FLEXFIELD ,
2514 TAX_ORIGIN ,
2515 REPORTING_CODE ,
2516 DEF_REC_SETTLEMENT_OPTION_CODE ,
2517 TAXABLE_ITEM_SOURCE_ID ,
2518
2519 -- BUG 9765740 Spanish Modelo 349 ESL EPL Changes
2520 INTRA_EU_TRX_TYPE_CODE)
2521 VALUES (
2522 JG_ZZ_VAT_TRX_DETAILS_S.NEXTVAL,
2523 DECODE( gt_extract_source_ledger(i), 'AR', g_rep_status_id_ar,
2524 'AP', g_rep_status_id_ap,
2525 'GL', g_rep_status_id_gl),
2526 g_selection_process_id,
2527 NULL,
2528 gt_rep_entity_id(i),
2529 gt_rep_context_entity_name(i),
2530 gt_rep_context_entity_loc_id(i),
2531 gt_taxpayer_id(i),
2532 gt_org_information2(i),
2533 gt_legal_authority_name(i),
2534 gt_legal_auth_address_line2(i),
2535 gt_legal_auth_address_line3(i),
2536 gt_legal_auth_city(i),
2537 gt_legal_contact_party_name(i),
2538 gt_activity_code(i),
2539 gt_ledger_id(i),
2540 gt_ledger_name(i),
2541 gt_chart_of_accounts_id(i),
2542 gt_extract_source_ledger(i),
2543 gt_establishment_id(i),
2544 gt_internal_organization_id(i),
2545 gt_application_id(i),
2546 gt_entity_code(i),
2547 gt_event_class_code(i),
2548 gt_trx_id(i),
2549 gt_trx_number(i),
2550 gt_trx_description(i),
2551 gt_trx_currency_code(i),
2552 gt_trx_type_id(i),
2553 gt_trx_type_mng(i),
2554 gt_trx_line_id(i),
2555 gt_trx_line_number(i),
2556 gt_trx_line_description(i),
2557 gt_trx_level_type(i),
2558 gt_trx_line_type(i),
2559 gt_trx_line_class(i),
2560 gt_trx_class_mng(i),
2561 gt_trx_date(i),
2562 gt_trx_due_date(i),
2563 gt_trx_communicated_date(i),
2564 gt_product_id(i),
2565 gt_functional_currency_code(i),
2566 gt_currency_conversion_type(i),
2567 gt_currency_conversion_date(i),
2568 gt_currency_conversion_rate(i),
2569 gt_territory_short_name(i),
2570 gt_doc_seq_id(i),
2571 gt_doc_seq_name(i),
2572 gt_doc_seq_value(i),
2573 gt_trx_line_amt(i),
2574 gt_receipt_class_id(i),
2575 gt_applied_from_appl_id(i),
2576 gt_applied_from_entity_code(i),
2577 gt_applied_from_event_cls_cd(i),
2578 gt_applied_from_trx_id(i),
2579 gt_applied_from_line_id(i),
2580 gt_applied_from_trx_number(i),
2581 gt_applied_to_application_id(i),
2582 gt_applied_to_entity_code(i),
2583 gt_applied_to_event_cls_code(i),
2584 gt_applied_to_trx_id(i),
2585 gt_applied_to_trx_line_id(i),
2586 gt_applied_to_trx_number(i),
2587 gt_adjusted_doc_appl_id(i),
2588 gt_adjusted_doc_entity_code(i),
2589 gt_adjusted_doc_event_cls_cd(i),
2590 gt_adjusted_doc_trx_id(i),
2591 gt_adjusted_doc_number(i),
2592 gt_adjusted_doc_date(i),
2593 gt_ref_doc_application_id(i),
2594 gt_ref_doc_entity_code(i),
2595 gt_ref_doc_event_class_code(i),
2596 gt_ref_doc_trx_id(i),
2597 gt_ref_doc_line_id(i),
2598 gt_merchant_party_doc_num(i),
2599 gt_merchant_party_name(i),
2600 gt_merchant_party_reference(i),
2601 gt_merchant_party_tax_reg_num(i),
2602 gt_merchant_party_taxpayer_id(i),
2603 gt_start_expense_date(i),
2604 gt_taxable_line_source_table(i),
2605 gt_tax_line_id(i),
2606 gt_tax_line_number(i),
2607 gt_tax_invoice_date(i),
2608 gt_taxable_amt(i),
2609 gt_taxable_amt_funcl_curr(i),
2610 gt_tax_amt(i),
2611 gt_tax_amt_funcl_curr(i),
2612 gt_rec_tax_amt_tax_curr(i),
2613 gt_nrec_tax_amt_tax_curr(i),
2614 gt_taxable_disc_amt(i),
2615 gt_taxable_disc_amt_fun_curr(i),
2616 gt_tax_disc_amt(i),
2617 gt_tax_disc_amt_fun_curr(i),
2618 gt_tax_rate_id(i),
2619 gt_tax_rate_code(i),
2620 gt_tax_rate(i),
2621 gt_tax_rate_code_name(i),
2622 gt_tax_rate_code_description(i),
2623 gt_tax_rate_vat_trx_type_code(i),
2624 gt_tax_rate_vat_trx_type_desc(i),
2625 gt_tax_rate_vat_trx_type_mng(i),
2626 gt_tax_rate_reg_type_code(i),
2627 gt_tax_type_code(i),
2628 gt_tax_type_mng(i),
2629 gt_tax_recovery_rate(i),
2630 gt_tax_regime_code(i),
2631 gt_tax(i),
2632 gt_tax_jurisdiction_code(i),
2633 gt_tax_status_id(i),
2634 gt_tax_status_code(i),
2635 gt_tax_currency_code(i),
2636 gt_offset_tax_rate_code(i),
2637 gt_billing_tp_name(i),
2638 gt_billing_tp_number(i),
2639 gt_billing_tp_tax_reg_num(i),
2640 gt_billing_tp_taxpayer_id(i),
2641 gt_billing_tp_party_number(i),
2642 gt_billing_tp_id(i),
2643 gt_billing_tp_tax_rep_flag(i),
2644 gt_billing_tp_site_id(i),
2645 gt_billing_tp_address_id(i),
2646 gt_billing_tp_site_name(i),
2647 gt_billing_tp_site_tx_reg_num(i),
2648 gt_shipping_tp_name(i),
2649 gt_shipping_tp_number(i),
2650 gt_shipping_tp_tax_reg_num(i),
2651 gt_shipping_tp_taxpayer_id(i),
2652 gt_shipping_tp_id(i),
2653 gt_shipping_tp_site_id(i),
2654 gt_shipping_tp_address_id(i),
2655 gt_shipping_tp_site_name(i),
2656 gt_shipping_tp_site_tx_rg_num(i),
2657 gt_bill_from_party_id(i), -- Bug 16386556
2658 gt_bill_from_party_site_id(i), -- Bug 16386556
2659 gt_banking_tp_name(i),
2660 gt_banking_tp_taxpayer_id(i),
2661 gt_bank_account_name(i),
2662 gt_bank_account_num(i),
2663 gt_bank_account_id(i),
2664 gt_bank_branch_id(i),
2665 gt_legal_entity_tax_reg_num(i),
2666 gt_hq_estb_reg_number(i),
2667 gt_tax_line_registration_num(i),
2668 gt_cancelled_date(i),
2669 gt_cancel_flag(i),
2670 gt_offset_flag(i),
2671 gt_posted_flag(i),
2672 gt_mrc_tax_line_flag(i),
2673 gt_reconciliation_flag(i),
2674 gt_tax_recoverable_flag(i),
2675 gt_reverse_flag(i),
2676 gt_correction_flag(i),
2677 gt_ar_cash_receipt_rev_stat(i),
2678 gt_ar_cash_receipt_rev_date(i),
2679 gt_payables_invoice_source(i),
2680 gt_acctd_amount_dr(i),
2681 gt_acctd_amount_cr(i),
2682 gt_rec_application_status(i),
2683 gt_vat_country_code(i),
2684 gt_invoice_identifier(i),
2685 gt_account_class(i),
2686 gt_latest_rec_flag(i),
2687 gt_jgzz_fiscal_code(i),
2688 gt_tax_reference(i),
2689 gt_pt_location(i),
2690 gt_invoice_report_type(i),
2691 gt_es_correction_year(i),
2692 gt_es_correction_period(i),
2693 gt_triangulation(i),
2694 gt_document_sub_type(i),
2695 gt_assessable_value(i),
2696 gt_property_location(i),
2697 gt_chk_vat_amount_paid(i),
2698 gt_import_document_number(i),
2699 gt_import_document_date(i),
2700 gt_prl_no(i),
2701 gt_property_rental(i),
2702 gt_rates_reference(i),
2703 gt_stair_num(i),
2704 gt_floor_num(i),
2705 gt_door_num(i),
2706 gt_amount_applied(i),
2707 gt_actg_event_type_code(i),
2708 gt_actg_event_type_mng(i),
2709 gt_actg_event_number(i),
2710 gt_actg_event_status_flag(i),
2711 gt_actg_event_status_mng(i),
2712 gt_actg_category_code(i),
2713 gt_actg_category_mng(i),
2714 gt_accounting_date(i),
2715 gt_gl_transfer_flag(i),
2716 gt_actg_line_num(i),
2717 gt_actg_line_type_code(i),
2718 gt_actg_line_type_mng(i),
2719 gt_actg_line_description(i),
2720 gt_actg_stat_amt(i),
2721 gt_actg_party_id(i),
2722 gt_actg_party_site_id(i),
2723 gt_actg_party_type(i),
2724 gt_actg_event_id(i),
2725 gt_actg_header_id(i),
2726 gt_actg_line_id(i),
2727 gt_actg_source_id(i),
2728 gt_actg_source_table(i),
2729 gt_actg_line_ccid(i),
2730 gt_account_flexfield(i),
2731 gt_account_description(i),
2732 gt_period_name(i),
2733 gt_trx_arap_balancing_seg(i),
2734 gt_trx_arap_natural_account(i),
2735 gt_trx_taxable_balancing_seg(i),
2736 gt_trx_taxable_natural_acct(i),
2737 gt_trx_tax_balancing_seg(i),
2738 gt_trx_tax_natural_account(i),
2739 g_created_by ,
2740 g_creation_date ,
2741 g_last_updated_by,
2742 g_last_update_date,
2743 g_last_update_login,
2744 g_conc_request_id,
2745 g_prog_appl_id,
2746 g_conc_program_id,
2747 g_conc_login_id,
2748 1,
2749 gt_gl_date(i),
2750 gt_trx_ctrl_actg_flexfield(i),
2751 gt_tax_origin(i),
2752 DECODE(gt_offset_flag(i),'Y','OFFSET',
2753 DECODE(gt_def_rec_settlement_opt_code(i),'DEFERRED','DEFERRED',
2754 gt_reporting_code(i))),
2755 gt_def_rec_settlement_opt_code(i),
2756 gt_taxable_item_source_id(i),
2757
2758 -- BUG 9765740 Spanish Modelo 349 ESL EPL Changes
2759 gt_intra_eu_trx_type_code(i)
2760 );
2761
2762
2763 IF (g_level_procedure >= g_current_runtime_level ) THEN
2764 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name ,
2765 ' Record successfully inserted = ' ||to_char(l_count));
2766 END IF;
2767
2768 IF g_debug_flag = 'Y' THEN
2769 FND_FILE.PUT_LINE(FND_FILE.LOG,'Record successfully inserted = ' ||to_char(l_count));
2770 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
2771 END IF;
2772
2773 EXCEPTION
2774 WHEN OTHERS THEN
2775 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2776 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2777
2778 IF g_debug_flag = 'Y' THEN
2779 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
2780 END IF;
2781
2782 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
2783 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
2784 END IF;
2785 --p_global_variables_rec.retcode := 2;
2786 RETURN;
2787
2788 END insert_tax_data;
2789
2790 /*===========================================================================+
2791 | PROCEDURE |
2792 | CALL_TRL |
2793 | |
2794 | DESCRIPTION |
2795 | This procedure calls the TRL engine for the given set of parameters. |
2796 | Argument Name Value to be passed |
2797 | ---------------------- ------------------------ |
2798 | P_REPORTING_LEVEL LEDGER or LEGAL ENTITY |
2799 | P_REPORTING_CONTEXT LEDGER_ID or LEGAL_ENTITY_ID |
2800 | P_LEGAL_ENTITY_LEVEL NULL -- LE Post Upg Changes |
2801 | P_LEGAL_ENTITY_ID :P_LEGAL_ENTITY_ID |
2802 | P_SUMMARY_LEVEL TRANSACTION DISTRIBUTION |
2803 | P_REGISTER_TYPE ALL |
2804 | P_PRODUCT :P_SOURCE |
2805 | P_FIRST_PARTY_TAX_REG_NUM : P_TAX_REGISTRATION_NUM |
2806 | P_TRX_DATE_LOW :L_TRX_DATE_LOW (Optional) |
2807 | P_TRX_DATE_HIGH :L_TRX_DATE_HIGH |
2808 | P_GL_DATE_LOW :L_GL_DATE_LOW (Optional) |
2809 | P_GL_DATE_HIGH :L_GL_DATE_HIGH |
2810 | P_TAX_INVOICE_DATE_LOW :P_TAX_INVOICE_DATE_LOW (Optional) |
2811 | P_TAX_INVOICE_DATE_HIGH :P_TAX_INVOICE_DATE_HIGH |
2812 | P_EXTRACT_ACCTED_TAX_LINES NULL --xBuild3 changes |
2813 | P_ACCOUNTING_STATUS :P_ACCTD_UNACCTD |
2814 | P_INCLUDE_ACCOUNTING_SEGMENTS Y |
2815 | P_LEGAL_REPORTING_STATUS UNREPORTED ie 000000000000000 |
2816 | P_REPORT_NAME JGVAT (To identify JE report call) |
2817 | P_ERRBUF :P_ERRBUF |
2818 | P_RETCODE :P_RETCODE |
2819 | |
2820 | Summary level : TRANSACTION DISTRIBUTION to have most granular information|
2821 | The report extracts should handle further grouping (if needed) based on |
2822 | report specific requirements. |
2823 | Register Type : ALL. The extracts will filter based on TAX, INTERIM etc |
2824 | based on report specific requirements. |
2825 | Report Name : Based on this parameter TRL engine conditionally calls JE |
2826 | specific plug ins for additional processing like GDFs related. |
2827 | |
2828 | Called from JG_ZZ_VAT_SELECTION_PKG.Main |
2829 | |
2830 | SCOPE - Private |
2831 | |
2832 | NOTES |
2833 | |
2834 | MODIFICATION HISTORY |
2835 | Date Author Description |
2836 | ============ ============== ================================= |
2837 | 23-Jan-2006 RBASKER Initial Version. |
2838 | 28-Mar-2006 RBASKER Incorporated changes for XBuild4. |
2839 | 28-Apr-2006 RBASKER Bug: 5169118 - Fixed issues identified |
2840 | during Unit Testing. |
2841 | 01-Jun-2006 RBASKER Bug: 5236973 - Call_TRL parameter is |
2842 | is fixed to pass include_acc_Seg parameter|
2843 | |
2844 +===========================================================================*/
2845
2846 PROCEDURE call_TRL(
2847 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
2848 x_request_id IN NUMBER
2849 ) IS
2850
2851 l_api_name CONSTANT VARCHAR2(30) := 'CALL_TRL';
2852 l_reporting_level VARCHAR2(30);
2853 l_reporting_context VARCHAR2(30);
2854 l_legal_entity_id NUMBER;
2855 l_trn VARCHAR2(30);
2856 l_accounting_status VARCHAR2(30);
2857 l_tax_invoice_date_low DATE;
2858 l_include_acc_segments VARCHAR2(1);
2859 l_tax_invoice_date_high DATE;
2860 l_trx_date_low DATE;
2861 l_trx_date_high DATE;
2862 l_gl_date_low DATE;
2863 l_gl_date_high DATE;
2864 l_country xle_firstparty_information_v.country%TYPE;
2865
2866 --Bug6835573
2867 l_reported_status VARCHAR2(20) := 'UNREPORTED';
2868 l_last_start_date DATE;
2869 l_last_end_date DATE;
2870
2871 cursor last_report_date (pn_vat_rep_entity_id number) IS
2872 select glp.start_date
2873 ,glp.end_date
2874 from jg_zz_vat_rep_entities legal
2875 ,jg_zz_vat_rep_entities acct
2876 ,gl_periods glp
2877 where acct.entity_type_code='ACCOUNTING'
2878 and acct.vat_reporting_entity_id = pn_vat_rep_entity_id
2879 and acct.mapping_vat_rep_entity_id= legal.vat_reporting_entity_id
2880 and glp.period_set_name = legal.tax_calendar_name
2881 and glp.period_name = acct.last_reported_period;
2882 --Bug6835573
2883
2884 BEGIN
2885 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2886 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
2887 G_PKG_NAME||': '||l_api_name||'()+');
2888 END IF;
2889
2890 IF g_debug_flag = 'Y' THEN
2891 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
2892 END IF;
2893
2894 -- Check the Reporting Entity Level.
2895 IF p_global_variables_rec.REPORTING_ENTITY_LEVEL = 'LEDGER' or
2896 p_global_variables_rec.REPORTING_ENTITY_LEVEL = 'BSV' THEN
2897 -- Ledger or BSV
2898 l_reporting_level := '1000';
2899 l_reporting_context := p_global_variables_rec.LEDGER;
2900 l_legal_entity_id := p_global_variables_rec.LEGAL_ENTITY_ID; -- Company_Name
2901 l_trn := NULL;
2902
2903 --Bug6835573
2904 OPEN last_report_date(p_global_variables_rec.vat_reporting_entity_id);
2905 FETCH last_report_date INTO l_last_start_date,l_last_end_date;
2906 CLOSE last_report_date;
2907
2908 FND_FILE.put_line(FND_FILE.log,'Last_Reported_Start_Date '|| l_last_start_date);
2909 IF p_global_variables_rec.TAX_INVOICE_DATE_HIGH <= l_last_end_date THEN
2910 l_reported_status := NULL;
2911 END IF;
2912 --Bug6835573
2913
2914 ELSE
2915 -- LE and TRN
2916 l_reporting_level := '2000';
2917 l_reporting_context := p_global_variables_rec.LEGAL_ENTITY_ID;
2918 l_legal_entity_id := NULL; -- Company_Name
2919 l_trn := p_global_variables_rec.TAX_REGISTRATION_NUMBER;
2920 END IF;
2921
2922 IF p_global_variables_rec.ACCTD_UNACCTD = 'UNACCOUNTED' THEN
2923 l_accounting_status := p_global_variables_rec.ACCTD_UNACCTD ;
2924 ELSE
2925 l_accounting_status := p_global_variables_rec.ACCTD_UNACCTD ;
2926 l_include_acc_segments := 'Y';
2927 END IF;
2928
2929 /*=========================================================================+
2930 | Based on driving date for VAT Reporting Entity call TRL with different |
2931 | set date range parameters. |
2932 +==========================================================================*/
2933 IF substr(p_global_variables_rec.DRIVING_DATE_CODE,
2934 instr(p_global_variables_rec.DRIVING_DATE_CODE,'GL',1,1),2) = 'GL' THEN
2935 l_gl_date_low := p_global_variables_rec.TAX_INVOICE_DATE_LOW;
2936 l_gl_date_high := p_global_variables_rec.TAX_INVOICE_DATE_HIGH;
2937 END IF;
2938
2939 IF substr(p_global_variables_rec.DRIVING_DATE_CODE,
2940 instr(p_global_variables_rec.DRIVING_DATE_CODE,'TRX',1,1),3) = 'TRX' THEN
2941 l_trx_date_low := p_global_variables_rec.TAX_INVOICE_DATE_LOW;
2942 l_trx_date_high := p_global_variables_rec.TAX_INVOICE_DATE_HIGH;
2943 END IF;
2944
2945 IF substr(p_global_variables_rec.DRIVING_DATE_CODE,
2946 instr(p_global_variables_rec.DRIVING_DATE_CODE,'TID',1,1),3) = 'TID' THEN
2947 l_tax_invoice_date_low := p_global_variables_rec.TAX_INVOICE_DATE_LOW;
2948 l_tax_invoice_date_high := p_global_variables_rec.TAX_INVOICE_DATE_HIGH;
2949 END IF;
2950
2951
2952 /*=========================================================================+
2953 | Corrections Approach: Correction transactions will be considered only |
2954 | when no previous OPEN period exists. (OPEN => Prev period is not FINAL |
2955 | reported yet). |
2956 | Start date should be passed as null if corrected transactions need to be |
2957 | considered. Otherwise, TRL would always return unreported transactions in|
2958 | in the given date range. |
2959 +==========================================================================*/
2960 IF NOT is_prev_period_open (p_global_variables_rec) THEN
2961 -- Correction transactions not to be considered.
2962 -- l_tax_invoice_date_low := p_global_variables_rec.TAX_INVOICE_DATE_LOW;
2963 -- ELSE
2964 l_gl_date_low := NULL;
2965 l_trx_date_low := NULL;
2966 l_tax_invoice_date_low := NULL;
2967
2968 END IF;
2969
2970 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2971
2972 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2973 'P_REPORTING_LEVEL = ' || l_reporting_level );
2974 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2975 'P_REPORTING_CONTEXT = ' || l_reporting_context );
2976 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2977 'P_LEGAL_ENTITY_ID = '|| l_legal_entity_id);
2978 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2979 ' P_PRODUCT = ' || p_global_variables_rec.SOURCE );
2980 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2981 ' P_FIRST_PARTY_TAX_REG_NUM = ' || l_trn );
2982 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2983 ' P_FIRST_PARTY_TAX_REG_NUM = ' || l_trn );
2984 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2985 ' P_TRX_DATE_LOW = '|| l_trx_date_low );
2986 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2987 ' P_TRX_DATE_HIGH = '|| l_trx_date_high );
2988 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2989 ' P_GL_DATE_LOW = '|| l_gl_date_low );
2990 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2991 ' P_GL_DATE_HIGH = '|| l_gl_date_high );
2992 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2993 ' P_TAX_INVOICE_DATE_LOW = '|| l_tax_invoice_date_low );
2994 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2995 ' P_TAX_INVOICE_DATE_HIGH = '|| l_tax_invoice_date_high );
2996 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2997 ' P_ACCOUNTING_STATUS = '|| l_accounting_status );
2998 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
2999 ' P_INCLUDE_ACCOUNTING_SEGMENTS = '|| l_include_acc_segments );
3000 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3001 ' P_TAX_REGIME_CODE = '|| p_global_variables_rec.TAX_REGIME_CODE);
3002 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3003 ' P_REPORTED_STATUS = '|| l_reported_status); --Bug6835573
3004
3005
3006 END IF;
3007
3008 IF g_debug_flag = 'Y' THEN
3009
3010 fnd_file.put_line(fnd_file.log,
3011 'Following parameters are passed to TRL...' );
3012 fnd_file.put_line(fnd_file.log,
3013 'P_REPORTING_LEVEL = ' || l_reporting_level );
3014 fnd_file.put_line(fnd_file.log,
3015 'P_REPORTING_CONTEXT = ' || l_reporting_context );
3016 fnd_file.put_line(fnd_file.log,
3017 'P_LEGAL_ENTITY_ID = '|| l_legal_entity_id);
3018 fnd_file.put_line(fnd_file.log,
3019 ' P_PRODUCT = ' || p_global_variables_rec.SOURCE );
3020 fnd_file.put_line(fnd_file.log,
3021 ' P_FIRST_PARTY_TAX_REG_NUM = ' || l_trn );
3022 fnd_file.put_line(fnd_file.log,
3023 ' P_TRX_DATE_LOW = '|| l_trx_date_low );
3024 fnd_file.put_line(fnd_file.log,
3025 ' P_TRX_DATE_HIGH = '|| l_trx_date_high);
3026 fnd_file.put_line(fnd_file.log,
3027 ' P_GL_DATE_LOW = '|| l_gl_date_low );
3028 fnd_file.put_line(fnd_file.log,
3029 ' P_GL_DATE_HIGH = '|| l_gl_date_high);
3030 fnd_file.put_line(fnd_file.log,
3031 ' P_TAX_INVOICE_DATE_LOW = '|| l_tax_invoice_date_low );
3032 fnd_file.put_line(fnd_file.log,
3033 ' P_TAX_INVOICE_DATE_HIGH = '|| l_tax_invoice_date_high);
3034 fnd_file.put_line(fnd_file.log,
3035 ' P_ACCOUNTING_STATUS = '|| l_accounting_status );
3036 fnd_file.put_line(fnd_file.log,
3037 ' P_INCLUDE_ACCOUNTING_SEGMENTS = '|| l_include_acc_segments );
3038 fnd_file.put_line(fnd_file.log,
3039 ' P_TAX_REGIME_CODE = '|| p_global_variables_rec.TAX_REGIME_CODE);
3040 fnd_file.put_line(fnd_file.log,
3041 'P_GL_OR_TRX_DATE_FILTER = '||p_global_variables_rec.gl_or_trx_date_filter);
3042 fnd_file.put_line(fnd_file.log,
3043 'P_REPORTED_STATUS = '||l_reported_status);
3044
3045 END IF;
3046
3047
3048 zx_extract_pkg.populate_tax_data
3049 (
3050 P_REPORTING_LEVEL => l_reporting_level,
3051 P_REPORTING_CONTEXT => l_reporting_context,
3052 P_LEGAL_ENTITY_ID => l_legal_entity_id ,
3053 P_SUMMARY_LEVEL => 'TRANSACTION_DISTRIBUTION',
3054 P_REGISTER_TYPE => 'ALL',
3055 P_PRODUCT => p_global_variables_rec.SOURCE ,
3056 P_MATRIX_REPORT => 'N',
3057 P_INCLUDE_AP_STD_TRX_CLASS => 'Y',
3058 P_INCLUDE_AP_DM_TRX_CLASS => 'Y',
3059 P_INCLUDE_AP_CM_TRX_CLASS => 'Y',
3060 P_INCLUDE_AP_PREP_TRX_CLASS => 'Y',
3061 P_INCLUDE_AP_MIX_TRX_CLASS => 'Y',
3062 P_INCLUDE_AP_EXP_TRX_CLASS => 'Y',
3063 P_INCLUDE_AP_INT_TRX_CLASS => 'Y',
3064 P_INCLUDE_AR_INV_TRX_CLASS => 'Y',
3065 P_INCLUDE_AR_APPL_TRX_CLASS => 'Y',
3066 P_INCLUDE_AR_ADJ_TRX_CLASS => 'Y',
3067 P_INCLUDE_AR_MISC_TRX_CLASS => 'Y',
3068 P_INCLUDE_AR_BR_TRX_CLASS => 'Y',
3069 P_INCLUDE_GL_MANUAL_LINES => 'Y',
3070 P_FIRST_PARTY_TAX_REG_NUM => l_trn ,
3071 P_TRX_DATE_LOW => l_trx_date_low,
3072 P_TRX_DATE_HIGH => l_trx_date_high,
3073 P_GL_DATE_LOW => l_gl_date_low,
3074 P_GL_DATE_HIGH => l_gl_date_high,
3075 P_TAX_INVOICE_DATE_LOW => l_tax_invoice_date_low ,
3076 P_TAX_INVOICE_DATE_HIGH => l_tax_invoice_date_high ,
3077 P_TAX_REGIME_CODE => p_global_variables_rec.TAX_REGIME_CODE,
3078 P_POSTING_STATUS => NULL ,
3079 P_REPORTED_STATUS => l_reported_status, --Bug6835573
3080 P_ACCOUNTING_STATUS => l_accounting_status,
3081 P_INCLUDE_ACCOUNTING_SEGMENTS => l_include_acc_segments,
3082 P_REPORT_NAME => 'JGVAT' ,
3083 P_REQUEST_ID => x_request_id ,
3084 P_ERRBUF => p_global_variables_rec.ERRBUF,
3085 P_RETCODE => p_global_variables_rec.RETCODE,
3086 P_GL_OR_TRX_DATE_FILTER => p_global_variables_rec.gl_or_trx_date_filter);
3087
3088 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3089 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3090 ' RETURN_STATUS = ' || p_global_variables_rec.RETCODE);
3091 END IF;
3092
3093 -- Filtering based on Balancing Segment Value.
3094 IF p_global_variables_rec.REPORTING_ENTITY_LEVEL = 'BSV' THEN
3095
3096 DELETE from ZX_REP_TRX_JX_EXT_T EXT
3097 WHERE EXT.request_id = x_request_id
3098 AND NOT EXISTS
3099 (SELECT 1
3100 FROM ZX_REP_ACTG_EXT_T ACT,
3101 ZX_REP_TRX_DETAIL_T DET
3102 WHERE DET.request_id = x_request_id
3103 AND EXT.detail_tax_line_id = DET.detail_tax_line_id
3104 AND ACT.detail_tax_line_id = DET.detail_tax_line_id
3105 AND ACT.trx_arap_balancing_segment = p_global_variables_rec.BSV
3106 );
3107
3108 DELETE from ZX_REP_TRX_DETAIL_T DET
3109 WHERE DET.request_id = x_request_id
3110 AND NOT EXISTS
3111 (SELECT 1
3112 FROM ZX_REP_ACTG_EXT_T ACT
3113 WHERE DET.request_id = x_request_id
3114 AND ACT.detail_tax_line_id = DET.detail_tax_line_id
3115 AND ACT.trx_arap_balancing_segment = p_global_variables_rec.BSV
3116 );
3117
3118 DELETE from ZX_REP_ACTG_EXT_T ACT
3119 WHERE ACT.request_id = x_request_id
3120 AND ACT.trx_arap_balancing_segment <> p_global_variables_rec.BSV;
3121
3122 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3123 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3124 ' Records filtered based on bsv');
3125 END IF;
3126
3127
3128 END IF;
3129
3130 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3131 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
3132 G_PKG_NAME||': ' ||l_api_name||'()-');
3133 END IF;
3134
3135 IF g_debug_flag = 'Y' THEN
3136 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
3137 END IF;
3138
3139 EXCEPTION
3140 WHEN OTHERS THEN
3141 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3142 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3143
3144 IF g_debug_flag = 'Y' THEN
3145 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
3146 END IF;
3147
3148 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
3149 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
3150 END IF;
3151 RETURN;
3152 END call_TRL;
3153
3154
3155 /*===========================================================================+
3156 | PROCEDURE |
3157 | control_intersecting_domains |
3158 | DESCRIPTION |
3159 | This procedure checks whether the current selection run results in |
3160 | intersecting data with a previous selection run(s). If exists, the |
3161 | intersecting data is populated into the jg_zz_vat_trx_gt table. |
3162 | If the intersecting data is a transaction that has been final reported |
3163 | we error the Selection process. |
3164 | For records that have not been final reported - we allow the subsequent|
3165 | run to replace the data. |
3166 | |
3167 | SCOPE - Private |
3168 | |
3169 | NOTES |
3170 | |
3171 | MODIFICATION HISTORY |
3172 | Date Author Description |
3173 | ============ ============== ================================= |
3174 | 29-Mar-2006 RBASKER Initial Version. |
3175 | 28-Jun-2006 RBASKER For GL transactions internal_org_id will |
3176 | be NULL, so added NVL logic. |
3177 | 03-AUG-2006 RJREDDY Added x_intersect_domain_err parameter |
3178 | for holding the error message |
3179 | |
3180 +===========================================================================*/
3181 PROCEDURE control_intersecting_domains(
3182 p_global_variables_rec IN OUT NOCOPY JG_ZZ_VAT_SELECTION_PKG.GLOBAL_VARIABLES_REC_TYPE,
3183 x_request_id IN NUMBER,
3184 x_intersect_domain_err OUT NOCOPY VARCHAR2
3185 ) IS
3186
3187 l_api_name CONSTANT VARCHAR2(30) := 'CONTROL_INTERSECTING_DOMAINS';
3188 l_count NUMBER;
3189 l_return_status VARCHAR2(1);
3190 l_return_message VARCHAR2(2000);
3191
3192 BEGIN
3193 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3194 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3195 G_PKG_NAME||': '||l_api_name||'()+');
3196 END IF;
3197
3198 IF g_debug_flag = 'Y' THEN
3199 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
3200 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_global_variables_rec.last_rep_period_start_date :'||p_global_variables_rec.last_rep_period_start_date);
3201 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_global_variables_rec.tax_invoice_date_low :'||p_global_variables_rec.tax_invoice_date_low);
3202 END IF;
3203
3204 -- populate temp table with the intersecting data.
3205 IF p_global_variables_rec.tax_invoice_date_high > p_global_variables_rec.LAST_REP_PERIOD_END_DATE THEN
3206
3207 -- Period above than last reported period.
3208 INSERT INTO JG_ZZ_VAT_TRX_GT
3209 (JG_INFO_N2,
3210 JG_INFO_N3,
3211 JG_INFO_V2,
3212 JG_INFO_V3,
3213 JG_INFO_N4,
3214 JG_INFO_N5,
3215 JG_INFO_N6,
3216 JG_INFO_N1,
3217 JG_INFO_V4,
3218 JG_INFO_D1,
3219 JG_INFO_N7,
3220 JG_INFO_N9,
3221 JG_INFO_N8,
3222 JG_INFO_V1)
3223 SELECT jgvt.internal_organization_id,
3224 jgvt.application_id ,
3225 jgvt.entity_code,
3226 jgvt.event_class_code,
3227 jgvt.trx_id,
3228 jgvt.trx_line_id,
3229 jgvt.tax_line_id ,
3230 jgvt.vat_transaction_id,
3231 jgvt.trx_number,
3232 jgvt.tax_invoice_date,
3233 jgvt.reporting_status_id ,
3234 jgvt.final_reporting_id,
3235 jgrs.vat_reporting_entity_id,
3236 jgre.entity_identifier
3237 FROM JG_ZZ_VAT_TRX_DETAILS JGVT,
3238 JG_ZZ_VAT_REP_STATUS JGRS,
3239 JG_ZZ_VAT_REP_ENTITIES JGRE
3240 WHERE JGVT.reporting_status_id = JGRS.reporting_status_id
3241 AND JGRS.vat_reporting_entity_id = JGRE.vat_reporting_entity_id
3242 AND (JGRS.period_end_date > p_global_variables_rec.LAST_REP_PERIOD_END_DATE
3243 OR p_global_variables_rec.LAST_REP_PERIOD_END_DATE IS NULL)
3244 AND (nvl(JGVT.internal_organization_id,-99),
3245 JGVT.application_id ,
3246 JGVT.entity_code,
3247 JGVT.event_class_code,
3248 JGVT.trx_id,
3249 JGVT.trx_line_id,
3250 JGVT.tax_line_id) IN (SELECT /*+ leading(con) */ nvl(DET.internal_organization_id,99),
3251 DET.application_id ,
3252 DET.entity_code,
3253 DET.event_class_code,
3254 DET.trx_id,
3255 DET.trx_line_id,
3256 DET.tax_line_id
3257 FROM
3258 ZX_REP_CONTEXT_T CON,
3259 ZX_REP_TRX_DETAIL_T DET
3260 WHERE CON.request_id = x_request_id
3261 AND DET.request_id = CON.request_id
3262 AND DET.rep_context_id = CON.rep_context_id
3263 );
3264 ELSE
3265 -- Period before or equal to last reported period.
3266 INSERT INTO JG_ZZ_VAT_TRX_GT
3267 (JG_INFO_N2,
3268 JG_INFO_N3,
3269 JG_INFO_V2,
3270 JG_INFO_V3,
3271 JG_INFO_N4,
3272 JG_INFO_N5,
3273 JG_INFO_N6,
3274 JG_INFO_N1,
3275 JG_INFO_V4,
3276 JG_INFO_D1,
3277 JG_INFO_N7,
3278 JG_INFO_N9,
3279 JG_INFO_N8,
3280 JG_INFO_V1)
3281 SELECT jgvt.internal_organization_id,
3282 jgvt.application_id ,
3283 jgvt.entity_code,
3284 jgvt.event_class_code,
3285 jgvt.trx_id,
3286 jgvt.trx_line_id,
3287 jgvt.tax_line_id ,
3288 jgvt.vat_transaction_id,
3289 jgvt.trx_number,
3290 jgvt.tax_invoice_date,
3291 jgvt.reporting_status_id ,
3292 jgvt.final_reporting_id,
3293 jgrs.vat_reporting_entity_id,
3294 jgre.entity_identifier
3295 FROM JG_ZZ_VAT_TRX_DETAILS JGVT,
3296 JG_ZZ_VAT_REP_STATUS JGRS,
3297 JG_ZZ_VAT_REP_ENTITIES JGRE
3298 WHERE JGVT.reporting_status_id = JGRS.reporting_status_id
3299 AND JGRS.vat_reporting_entity_id = JGRE.vat_reporting_entity_id
3300 AND (nvl(JGVT.internal_organization_id,-99),
3301 JGVT.application_id ,
3302 JGVT.entity_code,
3303 JGVT.event_class_code,
3304 JGVT.trx_id,
3305 JGVT.trx_line_id,
3306 JGVT.tax_line_id) IN (SELECT /*+ leading(con) */ nvl(DET.internal_organization_id,99),
3307 DET.application_id ,
3308 DET.entity_code,
3309 DET.event_class_code,
3310 DET.trx_id,
3311 DET.trx_line_id,
3312 DET.tax_line_id
3313 FROM
3314 ZX_REP_CONTEXT_T CON,
3315 ZX_REP_TRX_DETAIL_T DET
3316 WHERE CON.request_id = x_request_id
3317 AND DET.request_id = CON.request_id
3318 AND DET.rep_context_id = CON.rep_context_id
3319 );
3320
3321 END IF;
3322
3323 SELECT count(*) INTO l_count FROM JG_ZZ_VAT_TRX_GT;
3324
3325 -- purge_report_finalrep
3326 IF l_count > 0 THEN
3327 purge_report_finalrep(
3328 xv_return_status => l_return_status,
3329 xv_return_message => l_return_message
3330 );
3331 END IF;
3332
3333 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3334 x_intersect_domain_err := l_return_message;
3335 p_global_variables_rec.retcode := 2;
3336 RETURN;
3337 END IF;
3338
3339 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3340 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3341 ' RETURN_STATUS = ' || p_global_variables_rec.RETCODE);
3342 END IF;
3343
3344 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3345 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END'
3346 ,G_PKG_NAME||': ' ||l_api_name||'()-');
3347 END IF;
3348
3349 IF g_debug_flag = 'Y' THEN
3350 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
3351 END IF;
3352
3353 EXCEPTION
3354 WHEN OTHERS THEN
3355 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3356 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3357
3358 IF g_debug_flag = 'Y' THEN
3359 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
3360 END IF;
3361
3362 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
3363 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
3364 END IF;
3365 p_global_variables_rec.retcode := 2;
3366 RETURN;
3367 END control_intersecting_domains;
3368
3369 /*===========================================================================+
3370 | PROCEDURE |
3371 | purge_report_finalrep() |
3372 | |
3373 | DESCRIPTION |
3374 | This procedure |
3375 | (1) Checks whether records are finally reported in GTT. |
3376 | (2) Fetches the transaction details which are finally reported. |
3377 | (3) If atleast one transaction is finally reported, then Print and |
3378 | and exit. |
3379 | (4) None of the transactions in GTT are finally reported => |
3380 | Delete from box_allocs, box_errors, trx_details |
3381 | |
3382 | SCOPE - Private |
3383 | |
3384 | NOTES |
3385 | |
3386 | MODIFICATION HISTORY |
3387 | Date Author Description |
3388 | ============ ============== ================================= |
3389 | 29-Mar-2006 RPOKKULA Initial Version. |
3390 | 28-Jun-2006 RBASKER Changed forall loop for bulk operation. |
3391 | 04-Aug-2006 RJREDDY Added code for getting the location for |
3392 | the log file, into lv_utl_location variable|
3393 | |
3394 +===========================================================================*/
3395
3396 PROCEDURE purge_report_finalrep (
3397 xv_return_status out nocopy varchar2,
3398 xv_return_message out nocopy varchar2
3399 )
3400 IS
3401
3402 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_REPORT_FINALREP';
3403 l_return_status VARCHAR2(1);
3404 l_return_message VARCHAR2(2000);
3405 l_msg_count NUMBER;
3406 l_msg VARCHAR2(2000);
3407
3408 /*
3409 || Fetches the number of records which are finally reported from GTT
3410 */
3411 CURSOR c_finalrep_exist IS
3412 SELECT count(1)
3413 FROM jg_zz_vat_trx_gt
3414 WHERE jg_info_n9 is not null ; /* final_reporting_id */
3415
3416 /*
3417 || Fetches the invoice details which are finally reported
3418 */
3419 CURSOR c_get_finalrep IS
3420 SELECT jg_info_v1 , /* reporting_identifier */
3421 jg_info_n1 , /* vat_transaction_id */
3422 jg_info_n2 , /* internal_organization_id */
3423 jg_info_n3 , /* application_id */
3424 jg_info_v2 , /* entity_code */
3425 jg_info_v3 , /* event_class_code */
3426 jg_info_n4 , /* trx_id */
3427 jg_info_n5 , /* trx_line_id */
3428 jg_info_n6 , /* tax_line_id */
3429 jg_info_v4 , /* trx_number */
3430 jg_info_d1 , /* tax_invoice_date */
3431 jg_info_n7 , /* reporting_status_id */
3432 jg_info_n8 , /* vat_reporting_entity_id */
3433 jg_info_n9 /* final_reporting_id */
3434 FROM jg_zz_vat_trx_gt
3435 WHERE jg_info_n9 is not null; /* final_reporting_id */
3436
3437
3438 CURSOR c_get_finalrep_trx_for_upd IS
3439 SELECT
3440 p.jg_info_n3 , /* application_id */
3441 p.jg_info_v2 , /* entity_code */
3442 p.jg_info_v3 , /* event_class_code */
3443 p.jg_info_n4 , /* trx_id */
3444 p.jg_info_n5 , /* trx_line_id */
3445 p.jg_info_n2 , /* internal_organization_id */
3446 p.jg_info_n6 /* tax_line_id */
3447 FROM jg_zz_vat_trx_gt p
3448 WHERE p.jg_info_n9 is not null /* final_reporting_id */
3449 AND p.jg_info_n6 not in ( select tax_line_id
3450 from zx_lines
3451 where cancel_flag = 'Y'
3452 and trx_id = p.jg_info_n4
3453 and tax_line_id = p.jg_info_n6 );
3454
3455 CURSOR c_vat_transaction_id IS
3456 SELECT jg_info_n1 /* vat_transaction_id */
3457 FROM jg_zz_vat_trx_gt
3458 WHERE jg_info_n9 is null; /* final_reporting_id */
3459
3460 CURSOR c_get_outfile IS
3461 select 'jg_zz_vat' || to_char(sysdate,'ddmmyyyy_hhmiss') from dual ;
3462
3463 TYPE IdTab IS TABLE OF jg_zz_vat_trx_gt.jg_info_n1%TYPE;
3464 IdList IdTab ;
3465
3466 l_count NUMBER DEFAULT 0 ;
3467 lv_filename VARCHAR2(30);
3468
3469 lv_utl_location VARCHAR2(1000) ;
3470 l_trx_count NUMBER;
3471
3472 ltn_application_id zx_extract_pkg.application_id_tbl;
3473 ltv_entity_code zx_extract_pkg.entity_code_tbl;
3474 ltv_event_class_code zx_extract_pkg.event_class_code_tbl;
3475 ltn_trx_id zx_extract_pkg.trx_id_tbl;
3476 ltn_trx_line_id zx_extract_pkg.trx_line_id_tbl;
3477 ltn_internal_organization_id zx_extract_pkg.internal_organization_id_tbl;
3478 ltn_tax_line_id zx_extract_pkg.tax_line_id_tbl;
3479
3480 BEGIN
3481
3482 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3483 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3484 G_PKG_NAME||': '||l_api_name||'()+');
3485 END IF;
3486
3487 IF g_debug_flag = 'Y' THEN
3488 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
3489 END IF;
3490
3491 OPEN c_finalrep_exist ;
3492 FETCH c_finalrep_exist INTO l_count ;
3493 CLOSE c_finalrep_exist ;
3494
3495 IF l_count > 0 then
3496 /* If atleast one transaction is finally reported, then Print and exit */
3497
3498
3499 OPEN c_get_outfile ;
3500 FETCH c_get_outfile into lv_filename ;
3501 CLOSE c_get_outfile ;
3502
3503 -- Get the Location for the Log file
3504 SELECT decode(substr(value,1,instr(value,',') -1),
3505 null ,value ,
3506 substr (value,1,instr(value,',') -1)) INTO lv_utl_location
3507 FROM v$parameter
3508 WHERE name = 'utl_file_dir';
3509
3510
3511 log_file (lv_filename , '============================================================================================================================================================================================================================');
3512 log_file(lv_filename,'REPORTING_IDENTIFIER VAT_TRANSACTION_ID ORGANIZATION_ID APPLICATION_ID ENTITY_CODE EVENT_CLASS_CODE TRX_ID TRX_LINE_ID TAX_LINE_ID TRX_NUMBER TAX_INVOICE_DATE REPORTING_STATUS_ID VAT_REP_ENTITY_ID FINAL_REPORTING_ID');
3513 log_file (lv_filename , '============================================================================================================================================================================================================================');
3514
3515 FOR c_rec in c_get_finalrep
3516 LOOP
3517
3518 log_file (lv_filename , lpad(c_rec.JG_INFO_V1,20) ||
3519 lpad(c_rec.JG_INFO_N1,19) ||
3520 lpad(c_rec.JG_INFO_N2,16) ||
3521 lpad(c_rec.JG_INFO_N3,15) ||
3522 lpad(c_rec.JG_INFO_V2,15) ||
3523 lpad(c_rec.JG_INFO_V3,17) ||
3524 lpad(c_rec.JG_INFO_N4,15) ||
3525 lpad(c_rec.JG_INFO_N5,14) ||
3526 lpad(c_rec.JG_INFO_N6,13) ||
3527 lpad(c_rec.JG_INFO_V4,11) ||
3528 lpad(c_rec.JG_INFO_D1,17) ||
3529 lpad(c_rec.JG_INFO_N7,20) ||
3530 lpad(c_rec.JG_INFO_N8,18) ||
3531 lpad(c_rec.JG_INFO_N9,19)
3532 );
3533
3534 END LOOP ;
3535
3536 log_file (lv_filename , '=============================================================================================================================================================================================================================');
3537
3538 l_msg := l_count || ' Transactions are Finally Reported' ;
3539
3540 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3541 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, l_msg);
3542 END IF;
3543
3544 FND_MESSAGE.SET_NAME('JG','JG_ZZ_VAT_TRX_OVERLAPPING');
3545 FND_MESSAGE.SET_TOKEN('LOG_FILE', lv_filename );
3546 FND_MESSAGE.SET_TOKEN('LOG_LOCATION', lv_utl_location );
3547 xv_return_message := FND_MESSAGE.GET;
3548 -- xv_return_status := fnd_api.g_ret_sts_error;
3549
3550 IF g_debug_flag = 'Y' THEN
3551 FND_FILE.PUT_LINE(FND_FILE.LOG,xv_return_message);
3552 END IF;
3553
3554 OPEN c_get_finalrep_trx_for_upd;
3555
3556 FETCH c_get_finalrep_trx_for_upd BULK COLLECT INTO
3557 ltn_application_id ,
3558 ltv_entity_code ,
3559 ltv_event_class_code ,
3560 ltn_trx_id ,
3561 ltn_trx_line_id ,
3562 ltn_internal_organization_id ,
3563 ltn_tax_line_id ;
3564
3565 CLOSE c_get_finalrep_trx_for_upd;
3566
3567 /* Call the eBtax API to update transactions in eBtax as finally reported */
3568 zx_extract_pkg.zx_upd_legal_reporting_status
3569 (
3570 p_api_version => jg_zz_vat_rep_final_reporting.gn_api_version ,
3571 p_init_msg_list => fnd_api.g_false ,
3572 p_commit => fnd_api.g_false ,
3573 p_validation_level => null ,
3574 p_application_id_tbl => ltn_application_id ,
3575 p_entity_code_tbl => ltv_entity_code ,
3576 p_event_class_code_tbl => ltv_event_class_code ,
3577 p_trx_id_tbl => ltn_trx_id ,
3578 p_trx_line_id_tbl => ltn_trx_line_id ,
3579 p_internal_organization_id_tbl => ltn_internal_organization_id ,
3580 p_tax_line_id_tbl => ltn_tax_line_id ,
3581 p_legal_reporting_status_val => jg_zz_vat_rep_final_reporting.gv_legal_reporting_status ,
3582 x_return_status => l_return_status ,
3583 x_msg_count => l_msg_count ,
3584 x_msg_data => l_return_message
3585 );
3586
3587 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3588 xv_return_status := l_return_status;
3589 xv_return_message := l_return_message;
3590 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Unexpected error occurred in the
3591 procedure zx_extract_pkg.zx_upd_legal_reporting_status :'||l_return_message);
3592 END IF;
3593
3594 END IF; --IF l_count > 0 then
3595
3596 /* None of the transactions in GTT are finally reported
3597 => Delete from box_allocs, box_errors, trx_details */
3598
3599 IF g_debug_flag = 'Y' THEN
3600 FND_FILE.PUT_LINE(FND_FILE.LOG, 'None of the transactions in GTT are finally reported');
3601 END IF;
3602
3603
3604 OPEN c_vat_transaction_id ;
3605 FETCH c_vat_transaction_id BULK COLLECT INTO IdList ;
3606 CLOSE c_vat_transaction_id ;
3607
3608 l_trx_count := IdList.COUNT;
3609
3610 IF l_trx_count > 0 THEN
3611
3612 FORALL i in 1..l_trx_count
3613 delete from jg_zz_vat_box_allocs
3614 where vat_transaction_id = IdList(i) ;
3615
3616 FORALL i in 1..l_trx_count
3617 delete from jg_zz_vat_box_errors
3618 where vat_transaction_id = IdList(i) ;
3619
3620 FORALL i in 1..l_trx_count
3621 delete from jg_zz_vat_trx_details
3622 where vat_transaction_id = IdList(i) ;
3623
3624 END IF ; --IF l_trx_count > 0 THEN
3625
3626 xv_return_status := fnd_api.g_ret_sts_success;
3627 xv_return_message := 'None of the transactions are finally reported';
3628 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3629 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name, xv_return_message);
3630 END IF;
3631
3632 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3633 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
3634 G_PKG_NAME||': ' || l_api_name||'()-');
3635 END IF;
3636
3637 IF g_debug_flag = 'Y' THEN
3638 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
3639 END IF;
3640
3641
3642 EXCEPTION
3643 WHEN OTHERS THEN
3644 xv_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3645 xv_return_message := 'Unexpected error occurred in the
3646 procedure PURGE_REPORT_FINALREP:' || SQLCODE || ' ' ||
3647 SUBSTR(SQLERRM,1,80) ;
3648 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
3649 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
3650 END IF;
3651 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3652
3653 IF g_debug_flag = 'Y' THEN
3654 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
3655 END IF;
3656
3657 END purge_report_finalrep ;
3658
3659 /*===========================================================================+
3660 | PROCEDURE |
3661 | log_file() |
3662 | |
3663 | DESCRIPTION |
3664 | This procedure produces a log file that contains the details of those |
3665 | transactions that are final reported in previous selection runs. |
3666 | |
3667 | SCOPE - Private |
3668 | |
3669 | NOTES |
3670 | |
3671 | MODIFICATION HISTORY |
3672 | Date Author Description |
3673 | ============ ============== ================================= |
3674 | 29-Mar-2006 RPOKKULA Initial Version. |
3675 | |
3676 +===========================================================================*/
3677
3678 PROCEDURE log_file (
3679 filename IN VARCHAR2,
3680 text_to_write IN VARCHAR2 )
3681 IS
3682
3683 l_api_name CONSTANT VARCHAR2(30) := 'LOG_FILE';
3684 lv_utl_location VARCHAR2(1000);
3685 v_myfilehandle UTL_FILE.FILE_TYPE;
3686
3687 BEGIN
3688
3689 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3690 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3691 G_PKG_NAME||': '||l_api_name||'()+');
3692 END IF;
3693
3694
3695 SELECT decode(substr (value,1,instr(value,',') -1) ,
3696 null ,value ,
3697 substr (value,1,instr(value,',') -1)) INTO lv_utl_location
3698 FROM v$parameter
3699 WHERE name = 'utl_file_dir';
3700
3701 v_myfilehandle := utl_file.fopen(lv_utl_location,filename,'A');
3702 utl_file.put_line(v_myfilehandle,text_to_write);
3703 utl_file.fclose(v_myfilehandle);
3704
3705 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3706 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
3707 G_PKG_NAME||': ' || l_api_name||'()-');
3708 END IF;
3709
3710 IF g_debug_flag = 'Y' THEN
3711 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
3712 END IF;
3713
3714
3715 EXCEPTION
3716 WHEN OTHERS THEN
3717 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
3718 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
3719 END IF;
3720 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3721
3722 IF g_debug_flag = 'Y' THEN
3723 FND_FILE.PUT_LINE(FND_FILE.LOG, g_error_buffer);
3724 END IF;
3725 RETURN;
3726 END log_file ;
3727
3728
3729 /*===========================================================================+
3730 | PROCEDURE |
3731 | tax_date_maintenance_program() |
3732 | |
3733 | DESCRIPTION |
3734 | This procedure is called for ECE countries, to update the |
3735 | tax_invoice_date to payment clearing date for the invoices which has |
3736 | not been printed on a final register. |
3737 | Note: In R11i, this routine was called with ECE VAT registers to |
3738 | update Tax Date (GDF - GA1 column). |
3739 | |
3740 | SCOPE - Private |
3741 | |
3742 | NOTES |
3743 | |
3744 | MODIFICATION HISTORY |
3745 | Date Author Description |
3746 | ============ ============== ================================= |
3747 | 31-Jul-2006 RJREDDY Initial Version. |
3748 | 14-Sep-2006 RBASKER Bug 5517700 - Added two more statuses |
3749 | to support unaccounted status |
3750 | |
3751 +===========================================================================*/
3752 PROCEDURE tax_date_maintenance_program
3753 ( p_legal_entity_id IN JG_ZZ_VAT_REP_ENTITIES.legal_entity_id%TYPE,
3754 p_ledger_id IN JG_ZZ_VAT_REP_ENTITIES.ledger_id%TYPE,
3755 p_end_date IN GL_PERIODS.end_date%TYPE,
3756 p_source IN JG_ZZ_VAT_REP_STATUS.source%TYPE,
3757 p_debug_flag IN VARCHAR2,
3758 x_return_status OUT NOCOPY VARCHAR2,
3759 x_errbuf OUT NOCOPY VARCHAR2
3760 )
3761 IS
3762 l_api_name CONSTANT VARCHAR2(30) := 'TAX_DATE_MAINTENANCE_PROGRAM';
3763 l_prim_acct_method VARCHAR2(15);
3764 l_num NUMBER := 0;
3765 l_ledger_id GL_LEDGER_LE_V.ledger_id%TYPE;
3766
3767 -- Cursor to fecth all the invoices (Prepayments, Small Business Supplier
3768 -- Invoices ) for which the tax date is not present and not
3769 -- finally reported
3770 CURSOR c_inv_null_tax_date
3771 IS
3772 SELECT inv.invoice_id,
3773 inv.invoice_num,
3774 inv.set_of_books_id,
3775 inv.tax_invoice_recording_date,
3776 inv.invoice_type_lookup_code,
3777 checks.cleared_date,
3778 pv.small_business_flag
3779 FROM ap_invoices_all inv
3780 ,ap_checks_all checks
3781 ,ap_invoice_payments_all pay
3782 ,po_vendors pv
3783 ,zx_lines_det_factors zdf
3784 WHERE inv.set_of_books_id = DECODE(p_legal_entity_id, NULL, p_ledger_id,
3785 inv.set_of_books_id)
3786 AND inv.legal_entity_id = DECODE(p_ledger_id, NULL, p_legal_entity_id,
3787 inv.legal_entity_id)
3788 AND pay.set_of_books_id = inv.set_of_books_id
3789 AND checks.legal_entity_id = inv.legal_entity_id
3790 AND inv.invoice_id = pay.invoice_id
3791 AND inv.vendor_id = pv.vendor_id
3792 AND zdf.application_id = 200
3793 AND zdf.trx_id = inv.invoice_id
3794 AND checks.check_id = pay.check_id
3795 AND trunc(checks.cleared_date) < trunc(p_end_date)
3796 AND checks.status_lookup_code IN ('CLEARED', 'RECONCILED',
3797 'CLEARED BUT UNACCOUNTED', 'RECONCILED UNACCOUNTED')
3798 AND NOT EXISTS ( SELECT 1
3799 FROM jg_zz_vat_trx_details vtd
3800 WHERE vtd.application_id = 200
3801 AND vtd.trx_id = inv.invoice_id
3802 AND vtd.entity_code = zdf.entity_code
3803 AND vtd.event_class_code = zdf.event_class_code
3804 AND vtd.final_reporting_id IS NOT NULL
3805 );
3806
3807 -- Get the primary Accounting method for payables
3808 CURSOR c_primary_acct_method (l_ledger_id gl_ledgers.ledger_id%TYPE) IS
3809 SELECT accounting_method_option
3810 FROM ap_system_parameters_all
3811 WHERE set_of_books_id = l_ledger_id;
3812
3813 -- Get the primary Accounting method for receivables
3814 CURSOR c_ar_acct_method (l_ledger_id gl_ledgers.ledger_id%TYPE) IS
3815 SELECT upper(accounting_method)
3816 FROM ar_system_parameters_all
3817 WHERE set_of_books_id = l_ledger_id;
3818
3819 -- Get the Receivables Invoices
3820 CURSOR c_cust_trx
3821 IS
3822 SELECT trx.customer_trx_id,
3823 MAX(rpt.apply_date) apply_date,
3824 zdf.tax_invoice_date,
3825 zdf.entity_code,
3826 zdf.event_class_code
3827 FROM ra_customer_trx_all trx,
3828 ar_receivable_applications_all rpt,
3829 zx_lines_det_factors zdf
3830 WHERE trx.customer_trx_id = rpt.applied_customer_trx_id
3831 AND zdf.application_id = 222
3832 AND zdf.trx_id = trx.customer_trx_id
3833 AND trx.set_of_books_id = DECODE(p_legal_entity_id, NULL,
3834 p_ledger_id,trx.set_of_books_id)
3835 AND trx.legal_entity_id = DECODE(p_ledger_id, NULL,
3836 p_legal_entity_id,trx.legal_entity_id)
3837 AND rpt.set_of_books_id = trx.set_of_books_id
3838 AND rpt.status = 'APP'
3839 AND trx.STATUS_TRX = 'CL'
3840 AND amount_applied >= 0
3841 AND trunc(rpt.apply_date) <= trunc(p_end_date)
3842 AND rpt.apply_date < zdf.tax_invoice_date
3843 AND NOT EXISTS (SELECT 1
3844 FROM jg_zz_vat_trx_details
3845 WHERE application_id = 222
3846 AND trx_id = trx.customer_trx_id
3847 AND entity_code = zdf.entity_code
3848 AND event_class_code = zdf.event_class_code
3849 AND final_reporting_id IS NOT NULL
3850 )
3851 GROUP BY trx.customer_trx_id,
3852 zdf.tax_invoice_date,
3853 zdf.entity_code,
3854 zdf.event_class_code;
3855
3856 BEGIN
3857
3858 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3859 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',
3860 G_PKG_NAME||': '||l_api_name||'()+');
3861 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3862 'P_LEGAL_ENTITY_ID = '|| p_legal_entity_id);
3863 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3864 'P_LEDGER_ID = '|| p_ledger_id);
3865 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3866 'P_END_DATE = '|| p_end_date);
3867 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3868 ' P_SOURCE = ' || p_source);
3869 END IF;
3870
3871 IF g_debug_flag = 'Y' THEN
3872 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': '||l_api_name||'()+');
3873 END IF;
3874
3875 x_return_status := FND_API.G_RET_STS_SUCCESS;
3876 x_errbuf := NULL;
3877
3878 IF p_legal_entity_id IS NOT NULL THEN
3879 SELECT ledger_id
3880 INTO l_ledger_id
3881 FROM gl_ledger_le_v
3882 WHERE legal_entity_id = p_legal_entity_id
3883 AND LEDGER_CATEGORY_CODE = 'PRIMARY';
3884 ELSE
3885 l_ledger_id := p_ledger_id;
3886 END IF;
3887
3888
3889 -- Check whether Tax Date Maintenance Program for AP needs to be executed
3890 IF (p_source = 'AP' OR p_source = 'ALL') THEN
3891
3892 /*===== Start of Payables Tax Date Maintenance Program ==============*/
3893 BEGIN
3894
3895 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3896 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3897 'At the Beginning of Payables Tax Date Maintenance Program');
3898 END IF;
3899
3900 OPEN c_primary_acct_method (l_ledger_id);
3901 FETCH c_primary_acct_method INTO l_prim_acct_method;
3902 CLOSE c_primary_acct_method;
3903
3904 FOR c_inv_null_tax_date_rec IN c_inv_null_tax_date
3905 LOOP
3906 -- Check the accouting method
3907 -- If accounting method is 'Cash' then
3908 -- update Tax Date to the payment clearing date
3909
3910 IF l_prim_acct_method = 'Cash'
3911 OR ((c_inv_null_tax_date_rec.invoice_type_lookup_code = 'PREPAYMENT')
3912 OR (c_inv_null_tax_date_rec.small_business_flag = 'Y')) THEN
3913
3914 IF c_inv_null_tax_date_rec.cleared_date < p_end_date THEN
3915 UPDATE ap_invoices_all
3916 SET tax_invoice_recording_date = c_inv_null_tax_date_rec.cleared_date
3917 WHERE invoice_id = c_inv_null_tax_date_rec.invoice_id
3918 AND set_of_books_id = c_inv_null_tax_date_rec.set_of_books_id;
3919
3920 UPDATE zx_lines_det_factors
3921 SET tax_invoice_date = c_inv_null_tax_date_rec.cleared_date
3922 WHERE application_id = 200
3923 AND trx_id = c_inv_null_tax_date_rec.invoice_id
3924 AND entity_code = 'AP_INVOICES'
3925 AND ledger_id = c_inv_null_tax_date_rec.set_of_books_id;
3926
3927 -- Print the Invoices for which the tax date is getting
3928 -- updated in the log file
3929 IF l_num = 0 AND ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3930 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3931 'The tax date has been changed to cleared date for the
3932 following invoices');
3933 l_num := 1;
3934 END IF;
3935
3936 IF l_num = 1 AND ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3937 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3938 'Invoice num :' || c_inv_null_tax_date_rec.invoice_num ||
3939 -- ' ' ||'Original Tax Date :'|| to_char(c_inv_null_tax_date_rec.tax_invoice_recording_date, 'DD-MON-YYYY')
3940 --||' '|| 'Changed Tax Date :' || to_char(c_inv_null_tax_date_rec.cleared_date, 'DD-MON-YYYY'));
3941 ' ' || 'Actual Tax Date :'|| c_inv_null_tax_date_rec.tax_invoice_recording_date
3942 ||' '|| 'Updated Tax Date :' || c_inv_null_tax_date_rec.cleared_date);
3943 END IF;
3944
3945 END IF;
3946
3947 END IF;
3948
3949 END LOOP;
3950 EXCEPTION
3951 WHEN OTHERS THEN
3952
3953 IF c_inv_null_tax_date%ISOPEN THEN
3954 CLOSE c_inv_null_tax_date ;
3955 END IF;
3956
3957 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
3958 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,
3959 'Unable to update the Tax_Invoice_Date in
3960 PAYABLES_MAINTENANCE PROGRAM' || ' error_msg = '
3961 || sqlcode || ':' || SUBSTR(SQLERRM, 1, 80));
3962 END IF;
3963 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3964 x_errbuf := sqlcode || ':' || SUBSTR(SQLERRM, 1, 80);
3965 return;
3966
3967 END; -- Payables_Maintenance_Prog
3968
3969 END IF;
3970
3971
3972 IF (p_source = 'AR' OR p_source = 'ALL') THEN
3973
3974 /*=========== Start of Receivables Tax Date Maintenance Program =========*/
3975 BEGIN
3976
3977 l_num := 0;
3978 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3979 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,
3980 'Begining of the Receivables Tax date manintenance program');
3981 l_num := 1;
3982 END IF;
3983
3984 OPEN c_ar_acct_method (l_ledger_id);
3985 FETCH c_ar_acct_method INTO l_prim_acct_method;
3986 CLOSE c_ar_acct_method;
3987
3988
3989 IF l_prim_acct_method = 'CASH' THEN
3990 FOR c_cust_trx_rec IN c_cust_trx
3991 LOOP
3992 -- Write the information into log file
3993 IF l_num = 1 AND (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
3994 FND_LOG.STRING ( G_LEVEL_EXCEPTION, G_MODULE_NAME || l_api_name,
3995 ' Trx Id ' || c_cust_trx_rec.customer_trx_id ||
3996 ' Actual tax date ' || c_cust_trx_rec.tax_invoice_date ||
3997 ' Updated tax date ' || c_cust_trx_rec.apply_date );
3998 END IF;
3999
4000 -- Check the accouting method
4001 -- If accounting method is 'Cash' then
4002 -- update Tax Date to the payment clearing date
4003 -- Update the zx_lies_det_factors table
4004 BEGIN
4005 UPDATE zx_lines_det_factors
4006 SET tax_invoice_date = c_cust_trx_rec.apply_date
4007 WHERE application_id = 222
4008 AND trx_id = c_cust_trx_rec.customer_trx_id
4009 AND entity_code = c_cust_trx_rec.entity_code
4010 AND event_class_code = c_cust_trx_rec.event_class_code;
4011 EXCEPTION
4012 WHEN OTHERS THEN
4013 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
4014 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,
4015 'Error in updating Tax_Invoice_Date ' || ' error_msg = '
4016 || sqlcode || ':' || SUBSTR(SQLERRM, 1, 80));
4017 END IF;
4018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4019 x_errbuf := sqlcode || ':' || SUBSTR(SQLERRM, 1, 80);
4020 END;
4021 END LOOP;
4022 END IF;
4023 IF l_num = 1 AND (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
4024 FND_LOG.STRING ( G_LEVEL_EXCEPTION, G_MODULE_NAME || l_api_name, 'End of RECEIVABLES_MAINTENANCE PROGRAM' );
4025 END IF;
4026 EXCEPTION
4027 WHEN OTHERS THEN
4028 IF c_cust_trx%ISOPEN THEN
4029 CLOSE c_cust_trx ;
4030 END IF;
4031 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
4032 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,
4033 'Unable to update the Tax_Invoice_Date in
4034 RECEIVABLES_MAINTENANCE PROGRAM' || ' error_msg = '
4035 || sqlcode || ':' || SUBSTR(SQLERRM, 1, 80));
4036 END IF;
4037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4038 x_errbuf := sqlcode || ':' || SUBSTR(SQLERRM, 1, 80);
4039 END; -- Receivables_maintenance_prog
4040
4041 END IF;
4042
4043 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4044 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',
4045 G_PKG_NAME||': ' ||l_api_name||'()-');
4046 END IF;
4047
4048 IF g_debug_flag = 'Y' THEN
4049 FND_FILE.PUT_LINE(FND_FILE.LOG, G_PKG_NAME||': ' ||l_api_name||'()-');
4050 END IF;
4051
4052 EXCEPTION
4053 WHEN OTHERS THEN
4054 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
4055 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,
4056 'Unable to update the Tax_Invoice_Date in TAX DATE MAINTENANCE PROGRAM'
4057 || ' error_msg = ' || sqlcode || ':' || SUBSTR(SQLERRM, 1, 80) );
4058 END IF;
4059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4060 x_errbuf := sqlcode || ':' || SUBSTR(SQLERRM, 1, 80);
4061 END tax_date_maintenance_program;
4062
4063
4064
4065 /*=========================================================================+
4066 | PACKAGE Constructor |
4067 | |
4068 | |
4069 | DESCRIPTION |
4070 | The constructor initializes the global variables and displays the |
4071 | version of the package in the debug file |
4072 | |
4073 | MODIFICATION HISTORY |
4074 | Date Author Description |
4075 | ============ ============== ================================= |
4076 | 23-Jan-2006 RBASKER Initial Version. |
4077 | |
4078 +=========================================================================*/
4079
4080 BEGIN
4081 -- g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4082
4083 select text into l_version_info from user_source
4084 where name = 'JG_ZZ_VAT_SELECTION_PKG'
4085 and text like '%Header:%'
4086 and type = 'PACKAGE BODY'
4087 and line < 10;
4088
4089 IF (g_level_procedure >= g_current_runtime_level ) THEN
4090 FND_LOG.STRING(g_level_procedure, g_module_name||' version info :',
4091 l_version_info);
4092 FND_LOG.STRING(g_level_procedure, g_module_name|| ' version info :',
4093 'g_current_runtime_level :'||to_char(g_current_runtime_level));
4094 FND_LOG.STRING(g_level_procedure, g_module_name|| ' version info :',
4095 'g_level_procedure :'||to_char(g_level_procedure));
4096 FND_LOG.STRING(g_level_procedure, g_module_name|| ' version info :',
4097 'g_level_procedure :'||to_char(g_level_statement));
4098 END IF;
4099 END JG_ZZ_VAT_SELECTION_PKG;