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