[Home] [Help]
PACKAGE BODY: APPS.ZX_AP_POPULATE_PKG
Source
1 PACKAGE BODY ZX_AP_POPULATE_PKG AS
2 /* $Header: zxrippopulatpvtb.pls 120.85.12020000.8 2013/03/22 00:41:03 skorrapa ship $ */
3 --Populate variables
4
5 -- bug 13607557 removing unwanted structures
6 -- GT_TRX_LINE_CLASS ZX_EXTRACT_PKG.TRX_LINE_CLASS_TBL;
7 -- GT_TRX_CLASS_MNG ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
8 -- GT_TAX_RATE_CODE_REG_TYPE_MNG ZX_EXTRACT_PKG.TAX_RATE_CODE_REG_TYPE_MNG_TBL;
9 -- GT_TAX_RATE_REG_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_REG_TYPE_CODE_TBL;
10 -- GT_TAX_RATE_VAT_TRX_TYPE_DESC ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_DESC_TBL;
11 -- GT_TAX_RATE_VAT_TRX_TYPE_MNG ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_MNG_TBL;
12 -- GT_TAX_RATE_VAT_TRX_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_CODE_TBL;
13 -- GT_TRX_QUANTITY_UOM_MNG ZX_EXTRACT_PKG.TRX_QUANTITY_UOM_MNG_TBL;
14
15 GT_TAX_RECOVERABLE_FLAG ZX_EXTRACT_PKG.TAX_RECOVERABLE_FLAG_TBL;
16 GT_TAXABLE_DISC_AMT ZX_EXTRACT_PKG.TAXABLE_DISC_AMT_TBL;
17 GT_TAXABLE_DISC_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_DISC_AMT_FUN_CURR_TBL;
18 GT_TAX_DISC_AMT ZX_EXTRACT_PKG.TAX_DISC_AMT_TBL;
19 GT_TAX_DISC_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_DISC_AMT_FUN_CURR_TBL;
20 GT_BILLING_TP_NAME ZX_EXTRACT_PKG.BILLING_TP_NAME_TBL;
21 GT_BILLING_TP_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_NAME_ALT_TBL;
22 GT_BILLING_TP_SIC_CODE ZX_EXTRACT_PKG.BILLING_TP_SIC_CODE_TBL;
23 GT_BILLING_TP_CITY ZX_EXTRACT_PKG.BILLING_TP_CITY_TBL;
24 GT_BILLING_TP_COUNTY ZX_EXTRACT_PKG.BILLING_TP_COUNTY_TBL;
25 GT_BILLING_TP_STATE ZX_EXTRACT_PKG.BILLING_TP_STATE_TBL;
26 GT_BILLING_TP_PROVINCE ZX_EXTRACT_PKG.BILLING_TP_PROVINCE_TBL;
27 GT_BILLING_TP_ADDRESS1 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS1_TBL;
28 GT_BILLING_TP_ADDRESS2 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS2_TBL;
29 GT_BILLING_TP_ADDRESS3 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS3_TBL;
30 GT_BILLING_TP_ADDR_LINES_ALT ZX_EXTRACT_PKG.BILLING_TP_ADDR_LINES_ALT_TBL;
31 GT_BILLING_TP_COUNTRY ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
32 GT_BILLING_TP_POSTAL_CODE ZX_EXTRACT_PKG.BILLING_TP_POSTAL_CODE_TBL;
33 -- GT_SHIPPING_TP_CITY ZX_EXTRACT_PKG.SHIPPING_TP_CITY_TBL;
34 -- GT_SHIPPING_TP_COUNTY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTY_TBL;
35 -- GT_SHIPPING_TP_STATE ZX_EXTRACT_PKG.SHIPPING_TP_STATE_TBL;
36 -- GT_SHIPPING_TP_ADDRESS1 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS1_TBL;
37 -- GT_SHIPPING_TP_ADDRESS2 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS2_TBL;
38 -- GT_SHIPPING_TP_ADDRESS3 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS3_TBL;
39 -- GT_SHIPPING_TP_COUNTRY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTRY_TBL;
40 -- GT_SHIPPING_TP_POSTAL_CODE ZX_EXTRACT_PKG.SHIPPING_TP_POSTAL_CODE_TBL;
41 --GT_BILLING_TRADING_PARTNER_ID ZX_EXTRACT_PKG.BILLING_TRADING_PARTNER_ID_TBL;
42 --GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
43 -- GT_BILLING_TP_TAX_REP_FLAG ZX_EXTRACT_PKG.BILLING_TP_TAX_REP_FLAG_TBL;
44 -- GT_OFFICE_SITE_FLAG ZX_EXTRACT_PKG.OFFICE_SITE_FLAG_TBL;
45 -- GT_REGISTRATION_STATUS_CODE ZX_EXTRACT_PKG.REGISTRATION_STATUS_CODE_TBL;
46 GT_BILLING_TP_NUMBER ZX_EXTRACT_PKG.BILLING_TP_NUMBER_TBL;
47 GT_BILLING_TP_TAX_REG_NUM ZX_EXTRACT_PKG.BILLING_TP_TAX_REG_NUM_TBL;
48 GT_BILLING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.BILLING_TP_TAXPAYER_ID_TBL;
49 GT_BILLING_TP_SITE_NAME ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_ALT_TBL;
50 GT_BILLING_TP_SITE_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_ALT_TBL;
51 GT_BILLING_SITE_TAX_REG_NUM ZX_EXTRACT_PKG.BILLING_TP_SITE_TX_REG_NUM_TBL;
52
53 -- GT_SHIPPING_TP_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_NAME_ALT_TBL;
54 -- GT_SHIPPING_TP_SIC_CODE ZX_EXTRACT_PKG.BILLING_TP_SIC_CODE_TBL;
55 GT_GDF_PO_VENDOR_SITE_ATT17 ZX_EXTRACT_PKG.GDF_PO_VENDOR_SITE_ATT17_TBL;
56 GT_LEDGER_ID ZX_EXTRACT_PKG.LEDGER_ID_TBL;
57 GT_LEDGER_NAME ZX_EXTRACT_PKG.LEDGER_NAME_TBL;
58
59 --Global variables to fetch detail cursor
60 GT_DETAIL_TAX_LINE_ID ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
61 GT_APPLICATION_ID ZX_EXTRACT_PKG.APPLICATION_ID_TBL;
62 GT_ENTITY_CODE ZX_EXTRACT_PKG.ENTITY_CODE_TBL;
63 GT_EVENT_CLASS_CODE ZX_EXTRACT_PKG.EVENT_CLASS_CODE_TBL;
64 GT_TRX_LEVEL_TYPE ZX_EXTRACT_PKG.TRX_LEVEL_TYPE_TBL;
65 GT_INTERNAL_ORGANIZATION_ID ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
66 GT_TAX_DATE ZX_EXTRACT_PKG.TAX_DATE_TBL;
67 GT_TRX_ID ZX_EXTRACT_PKG.TRX_ID_TBL;
68 GT_TRX_LINE_ID ZX_EXTRACT_PKG.TRX_LINE_ID_TBL;
69 GT_TRX_LINE_DIST_ID ZX_EXTRACT_PKG.TAXABLE_ITEM_SOURCE_ID_TBL;
70 GT_TAX_LINE_ID ZX_EXTRACT_PKG.TAX_LINE_ID_TBL;
71 GT_TRX_LINE_TYPE ZX_EXTRACT_PKG.TRX_LINE_TYPE_TBL;
72 GT_SHIP_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_TAX_PROF_ID_TBL;
73 GT_SHIP_FROM_PTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_PTY_TAX_PROF_ID_TBL;
74 GT_BILL_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_TAX_PROF_ID_TBL;
75 GT_BILL_FROM_PTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_PTY_TAX_PROF_ID_TBL;
76 GT_SHIP_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_SITE_TAX_PROF_ID_TBL;
77 GT_BILL_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_SITE_TAX_PROF_ID_TBL;
78 GT_SHIP_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_SITE_TAX_PROF_ID_TBL;
79 GT_BILL_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_SITE_TAX_PROF_ID_TBL;
80 GT_BILL_FROM_PARTY_ID ZX_EXTRACT_PKG.BILL_FROM_PARTY_ID_TBL;
81 GT_BILL_FROM_PARTY_SITE_ID ZX_EXTRACT_PKG.BILL_FROM_PARTY_SITE_ID_TBL;
82 GT_SHIPPING_TP_ID ZX_EXTRACT_PKG.SHIPPING_TP_ID_TBL;
83 GT_BILLING_TRADING_PARTNER_ID ZX_EXTRACT_PKG.BILLING_TRADING_PARTNER_ID_TBL;
84 GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
85 GT_SHIPPING_TP_SITE_ID ZX_EXTRACT_PKG.SHIPPING_TP_SITE_ID_TBL;
86 GT_BILLING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
87 GT_SHIPPING_TP_ADDRESS_ID ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS_ID_TBL;
88 GT_HISTORICAL_FLAG ZX_EXTRACT_PKG.HISTORICAL_FLAG_TBL;
89 GT_POSTED_FLAG ZX_EXTRACT_PKG.POSTED_FLAG_TBL;
90 GT_TAX_RATE_ID ZX_EXTRACT_PKG.TAX_RATE_ID_TBL;
91 GT_TAX_REGIME_CODE ZX_EXTRACT_PKG.TAX_REGIME_CODE_TBL;
92 GT_TAX_STATUS_CODE ZX_EXTRACT_PKG.TAX_STATUS_CODE_TBL;
93 GT_TAX ZX_EXTRACT_PKG.TAX_TBL;
94
95 -- apai GT_REP_CONTEXT_ID ZX_EXTRACT_PKG.REP_CONTEXT_ID_TBL;
96 G_FUN_CURRENCY_CODE gl_ledgers.currency_code%TYPE;
97
98 GT_TAX_REG_NUM ZX_EXTRACT_PKG.HQ_ESTB_REG_NUMBER_TBL;
99 GT_HQ_ESTB_REG_NUMBER ZX_EXTRACT_PKG.HQ_ESTB_REG_NUMBER_TBL;
100
101
102 --Accounting global variables declaration --
103 GT_ACTG_EXT_LINE_ID ZX_EXTRACT_PKG.ACTG_EXT_LINE_ID_TBL;
104 GT_ACTG_EVENT_TYPE_CODE ZX_EXTRACT_PKG.ACTG_EVENT_TYPE_CODE_TBL;
105 GT_ACTG_EVENT_NUMBER ZX_EXTRACT_PKG.ACTG_EVENT_NUMBER_TBL;
106 GT_ACTG_EVENT_STATUS_FLAG ZX_EXTRACT_PKG.ACTG_EVENT_STATUS_FLAG_TBL;
107 GT_ACTG_CATEGORY_CODE ZX_EXTRACT_PKG.ACTG_CATEGORY_CODE_TBL;
108 GT_ACCOUNTING_DATE ZX_EXTRACT_PKG.ACCOUNTING_DATE_TBL;
109 GT_GL_TRANSFER_FLAG ZX_EXTRACT_PKG.GL_TRANSFER_FLAG_TBL;
110 GT_GL_TRANSFER_RUN_ID ZX_EXTRACT_PKG.GL_TRANSFER_RUN_ID_TBL;
111 GT_ACTG_HEADER_DESCRIPTION ZX_EXTRACT_PKG.ACTG_HEADER_DESCRIPTION_TBL;
112 GT_ACTG_LINE_NUM ZX_EXTRACT_PKG.ACTG_LINE_NUM_TBL;
113 GT_ACTG_LINE_TYPE_CODE ZX_EXTRACT_PKG.ACTG_LINE_TYPE_CODE_TBL;
114 GT_ACTG_LINE_DESCRIPTION ZX_EXTRACT_PKG.ACTG_LINE_DESCRIPTION_TBL;
115 GT_ACTG_STAT_AMT ZX_EXTRACT_PKG.ACTG_STAT_AMT_TBL;
116 GT_ACTG_ERROR_CODE ZX_EXTRACT_PKG.ACTG_ERROR_CODE_TBL;
117 GT_GL_TRANSFER_CODE ZX_EXTRACT_PKG.GL_TRANSFER_CODE_TBL;
118 GT_ACTG_DOC_SEQUENCE_ID ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_ID_TBL;
119 GT_ACTG_DOC_SEQUENCE_NAME ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_NAME_TBL;
120 GT_ACTG_DOC_SEQUENCE_VALUE ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_VALUE_TBL;
121 GT_ACTG_PARTY_ID ZX_EXTRACT_PKG.ACTG_PARTY_ID_TBL;
122 GT_ACTG_PARTY_SITE_ID ZX_EXTRACT_PKG.ACTG_PARTY_SITE_ID_TBL;
123 GT_ACTG_PARTY_TYPE ZX_EXTRACT_PKG.ACTG_PARTY_TYPE_TBL;
124 GT_ACTG_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
125 GT_ACTG_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
126 GT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
127 GT_ACTG_SOURCE_TABLE ZX_EXTRACT_PKG.ACTG_SOURCE_TABLE_TBL;
128 GT_ACTG_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
129 GT_PERIOD_NAME ZX_EXTRACT_PKG.PERIOD_NAME_TBL;
130
131 -- GT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
132 GT_AE_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
133 GT_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
134 GT_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
135 GT_TRX_ARAP_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_ARAP_BALANCING_SEG_TBL;
136 GT_TRX_ARAP_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_ARAP_NATURAL_ACCOUNT_TBL;
137 GT_TRX_TAXABLE_BAL_SEG ZX_EXTRACT_PKG.TRX_TAXABLE_BALANCING_SEG_TBL;
138 GT_TRX_TAXABLE_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAXABLE_NATURAL_ACCT_TBL;
139 GT_TRX_TAX_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_TAX_BALANCING_SEG_TBL;
140 GT_TRX_TAX_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAX_NATURAL_ACCOUNT_TBL;
141 GT_TAX_AMT ZX_EXTRACT_PKG.TAX_AMT_TBL;
142 GT_TAX_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
143 GT_TAXABLE_AMT ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
144 GT_TAXABLE_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL;
145
146 AGT_ACTG_EXT_LINE_ID ZX_EXTRACT_PKG.ACTG_EXT_LINE_ID_TBL;
147 AGT_DETAIL_TAX_LINE_ID ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
148 AGT_ACTG_EVENT_TYPE_CODE ZX_EXTRACT_PKG.ACTG_EVENT_TYPE_CODE_TBL;
149 AGT_ACTG_EVENT_NUMBER ZX_EXTRACT_PKG.ACTG_EVENT_NUMBER_TBL;
150 AGT_ACTG_EVENT_STATUS_FLAG ZX_EXTRACT_PKG.ACTG_EVENT_STATUS_FLAG_TBL;
151 AGT_ACTG_CATEGORY_CODE ZX_EXTRACT_PKG.ACTG_CATEGORY_CODE_TBL;
152 AGT_ACCOUNTING_DATE ZX_EXTRACT_PKG.ACCOUNTING_DATE_TBL;
153 AGT_GL_TRANSFER_FLAG ZX_EXTRACT_PKG.GL_TRANSFER_FLAG_TBL;
154 AGT_GL_TRANSFER_RUN_ID ZX_EXTRACT_PKG.GL_TRANSFER_RUN_ID_TBL;
155 AGT_ACTG_HEADER_DESCRIPTION ZX_EXTRACT_PKG.ACTG_HEADER_DESCRIPTION_TBL;
156 AGT_ACTG_LINE_NUM ZX_EXTRACT_PKG.ACTG_LINE_NUM_TBL;
157 AGT_ACTG_LINE_TYPE_CODE ZX_EXTRACT_PKG.ACTG_LINE_TYPE_CODE_TBL;
158 AGT_ACTG_LINE_DESCRIPTION ZX_EXTRACT_PKG.ACTG_LINE_DESCRIPTION_TBL;
159 AGT_ACTG_STAT_AMT ZX_EXTRACT_PKG.ACTG_STAT_AMT_TBL;
160 AGT_ACTG_ERROR_CODE ZX_EXTRACT_PKG.ACTG_ERROR_CODE_TBL;
161 AGT_GL_TRANSFER_CODE ZX_EXTRACT_PKG.GL_TRANSFER_CODE_TBL;
162 AGT_ACTG_DOC_SEQUENCE_ID ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_ID_TBL;
163 AGT_ACTG_DOC_SEQUENCE_NAME ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_NAME_TBL;
164 AGT_ACTG_DOC_SEQUENCE_VALUE ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_VALUE_TBL;
165 AGT_ACTG_PARTY_ID ZX_EXTRACT_PKG.ACTG_PARTY_ID_TBL;
166 AGT_ACTG_PARTY_SITE_ID ZX_EXTRACT_PKG.ACTG_PARTY_SITE_ID_TBL;
167 AGT_ACTG_PARTY_TYPE ZX_EXTRACT_PKG.ACTG_PARTY_TYPE_TBL;
168 AGT_ACTG_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
169 AGT_ACTG_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
170 AGT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
171 AGT_ACTG_SOURCE_TABLE ZX_EXTRACT_PKG.ACTG_SOURCE_TABLE_TBL;
172 AGT_ACTG_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
173 AGT_PERIOD_NAME ZX_EXTRACT_PKG.PERIOD_NAME_TBL;
174 GT_ACCOUNT_FLEXFIELD ZX_EXTRACT_PKG.ACCOUNT_FLEXFIELD_TBL;
175 GT_ACCOUNT_DESCRIPTION ZX_EXTRACT_PKG.ACCOUNT_DESCRIPTION_TBL;
176 GT_TRX_CONTROL_ACCFLEXFIELD ZX_EXTRACT_PKG.TRX_CONTROL_ACCT_FLEXFLD_TBL ; --Bug 5510907
177
178 TYPE TRX_TAXABLE_ACCOUNT_DESC_tbl IS TABLE OF
179 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_ACCOUNT_DESC%TYPE INDEX BY BINARY_INTEGER;
180
181 TYPE TRX_TAXABLE_BALSEG_DESC_tbl IS TABLE OF
182 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_BALSEG_DESC%TYPE INDEX BY BINARY_INTEGER;
183
184 TYPE TRX_TAXABLE_NATACCT_DESC_tbl IS TABLE OF
185 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_NATACCT_SEG_DESC%TYPE INDEX BY BINARY_INTEGER;
186
187 type IPV_PRIORITY_TBL is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
188
189 GT_TRX_TAXABLE_ACCOUNT_DESC TRX_TAXABLE_ACCOUNT_DESC_tbl ; --Bug 5650415
190 GT_TRX_TAXABLE_BALSEG_DESC TRX_TAXABLE_BALSEG_DESC_TBL ;
191 GT_TRX_TAXABLE_NATACCT_DESC TRX_TAXABLE_NATACCT_DESC_tbl ;
192 GT_IPV_PRIORITY IPV_PRIORITY_TBL;
193
194 -- GT_TAX_TYPE_MNG ZX_EXTRACT_PKG.TAX_TYPE_MNG_TBL;
195 -- gt_tax_type_code zx_extract_pkg.tax_type_code_tbl;
196
197 -- Accounting---
198
199 G_CREATED_BY NUMBER(15);
200 G_CREATION_DATE DATE;
201 G_LAST_UPDATED_BY NUMBER(15);
202 G_LAST_UPDATE_DATE DATE;
203 G_LAST_UPDATE_LOGIN NUMBER(15);
204 G_PROGRAM_APPLICATION_ID NUMBER;
205 G_PROGRAM_ID NUMBER;
206 G_PROGRAM_LOGIN_ID NUMBER;
207 g_chart_of_accounts_id NUMBER;
208
209 g_request_id NUMBER;
210
211 C_LINES_PER_COMMIT constant number := 5000;
212
213 G_REP_CONTEXT_ID NUMBER := 0;
214 g_retcode NUMBER := 0;
215 g_current_runtime_level NUMBER;
216 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
217 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
218 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
219 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
220 g_error_buffer VARCHAR2(100);
221
222 -- l_balancing_segment VARCHAR2(25);
223 -- l_accounting_segment VARCHAR2(25);
224 l_ledger_id NUMBER(15);
225 l_gl_name gl_ledgers.name%TYPE;
226
227 PROCEDURE extract_party_info(i IN BINARY_INTEGER,
228 P_REPORT_NAME IN VARCHAR2);
229
230
231 PROCEDURE get_accounting_info (
232 p_application_id IN NUMBER,
233 p_entity_code IN VARCHAR2,
234 p_event_class_code IN VARCHAR2,
235 p_trx_level_type IN VARCHAR2,
236 p_trx_id IN NUMBER,
237 p_trx_line_id IN NUMBER,
238 p_trx_line_dist_id IN NUMBER,
239 p_tax_line_id IN NUMBER,
240 p_event_id IN NUMBER,
241 p_ae_header_id IN NUMBER,
242 p_tax_dist_id IN NUMBER,
243 p_balancing_segment IN VARCHAR2,
244 p_accounting_segment IN VARCHAR2,
245 p_summary_level IN VARCHAR2,
246 p_include_discounts IN VARCHAR2,
247 p_tax_rec_flag IN VARCHAR2,
248 p_tax_regime_code IN VARCHAR2,
249 p_tax IN VARCHAR2,
250 p_tax_status_code IN VARCHAR2,
251 p_tax_rate_id IN NUMBER,
252 p_org_id IN NUMBER,
253 p_ledger_id IN NUMBER,
254 j IN BINARY_INTEGER);
255
256 PROCEDURE get_accounting_amounts (
257 p_application_id IN NUMBER,
258 p_entity_code IN VARCHAR2,
259 p_event_class_code IN VARCHAR2,
260 p_trx_level_type IN VARCHAR2,
261 p_trx_id IN NUMBER,
262 p_trx_line_id IN NUMBER,
263 p_tax_line_id IN NUMBER,
264 p_event_id IN NUMBER,
265 p_ae_header_id IN NUMBER,
266 p_tax_dist_id IN NUMBER,
267 p_summary_level IN VARCHAR2,
268 p_report_name IN VARCHAR2,
269 p_ledger_id IN NUMBER,
270 j IN binary_integer,
271 p_ae_line_num IN NUMBER,
272 p_trx_line_dist_id IN NUMBER);
273
274
275 PROCEDURE get_discount_info (
276 j IN BINARY_INTEGER,
277 p_trx_id IN NUMBER,
278 p_tax_line_id IN NUMBER,
279 p_summary_level IN VARCHAR2,
280 p_dist_id IN NUMBER,
281 p_trx_line_id IN NUMBER,
282 p_trx_line_dist_id IN NUMBER,
283 p_tax_rec_flag IN VARCHAR2,
284 p_tax_regime_code IN VARCHAR2,
285 p_tax IN VARCHAR2,
286 p_tax_status_code IN VARCHAR2,
287 p_tax_rate_id IN NUMBER,
288 p_ledger_id IN NUMBER,
289 p_disc_distribution_method IN VARCHAR2,
290 p_liability_post_lookup_code IN VARCHAR2
291 );
292
293 PROCEDURE bulk_populate_zx_dtl
294 (p_register_type IN VARCHAR2 -- P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE
295 );
296
297 PROCEDURE populate_tax_reg_num(
298 p_trl_global_variables_rec IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
299 p_org_id IN zx_lines.internal_organization_id%TYPE ,
300 p_tax_date IN zx_lines.tax_date%TYPE,
301 i IN BINARY_INTEGER);
302
303
304 PROCEDURE initialize_variables (
305 p_count IN NUMBER);
306
307 PROCEDURE update_zx_rep_detail_t(
308 p_count IN BINARY_INTEGER);
309
310 PROCEDURE insert_actg_info (
311 P_COUNT IN BINARY_INTEGER);
312
313 PROCEDURE update_additional_info(
314 p_trl_global_variables_rec IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE) IS
315
316 l_count number;
317 l_balancing_segment varchar2(30);
318 l_accounting_segment varchar2(30);
319 j number;
320
321 l_disc_inv_less_tax_flag_hdr VARCHAR2(1);
322 l_disc_dist_method_hdr VARCHAR2(30);
323 l_liability_post_lkp_code_hdr VARCHAR2(30);
324
325 CURSOR get_system_info_cur_hdr(c_org_id NUMBER) IS
326 SELECT discount_distribution_method,
327 disc_is_inv_less_tax_flag,
328 liability_post_lookup_code
329 FROM ap_system_parameters_all
330 WHERE org_id = c_org_id;
331
332 CURSOR rep_detail_cursor(c_request_id IN NUMBER
333 ,c_ledger_id IN NUMBER
334 ) IS
335 SELECT DETAIL_TAX_LINE_ID,
336 APPLICATION_ID,
337 ENTITY_CODE,
338 EVENT_CLASS_CODE,
339 TRX_LEVEL_TYPE,
340 INTERNAL_ORGANIZATION_ID,
341 tax_date,
342 -- TAX_RATE_VAT_TRX_TYPE_CODE,
343 HQ_ESTB_REG_NUMBER,
344 TRX_ID,
345 TRX_LINE_ID ,
346 TAXABLE_ITEM_SOURCE_ID,
347 TAX_LINE_ID ,
348 TRX_LINE_TYPE,
349 -- TRX_LINE_CLASS,
350 BILL_FROM_PARTY_TAX_PROF_ID,
351 BILL_FROM_SITE_TAX_PROF_ID,
352 SHIP_TO_SITE_TAX_PROF_ID,
353 SHIP_FROM_SITE_TAX_PROF_ID,
354 SHIP_TO_PARTY_TAX_PROF_ID,
355 SHIP_FROM_PARTY_TAX_PROF_ID,
356 -- zx_dtl.BILL_FROM_PARTY_ID,
357 -- zx_dtl.BILL_FROM_PARTY_SITE_ID,
358 SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
359 BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
360 SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
361 BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
362 SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
363 BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
364 HISTORICAL_FLAG,
365 posted_flag,
366 event_type_code, -- Accounting Columns
367 event_number,
368 event_status_code,
369 je_category_name,
370 accounting_date,
371 gl_transfer_status_flag,
372 description_header,
373 ae_line_num,
374 accounting_class_code,
375 description_line,
376 statistical_amount,
377 process_status_code,
378 gl_transfer_status_code,
379 doc_sequence_id,
380 doc_sequence_value,
381 party_id,
382 party_site_id,
383 party_type_code,
384 event_id,
385 ae_header_id,
386 code_combination_id,
387 period_name,
388 -- zx_dtl.trx_line_id
389 actg_source_id,
390 ledger_id,
391 tax_recoverable_flag,
392 taxable_amt , --Bug 5409170
393 0, --tax_amt, --Bug 5409170
394 taxable_amt_funcl_curr,
395 0, --tax_amt_funcl_curr ,
396 tax_regime_code,
397 tax,
398 tax_status_code,
399 tax_rate_id,
400 ipv_priority
401 -- tax_type_code
402 FROM ( SELECT /*+ leading(zx_dtl,xla_ent,xla_event,xla_head) full(zx_dtl) parallel(zx_dtl,4) */
403 zx_dtl.DETAIL_TAX_LINE_ID,
404 zx_dtl.APPLICATION_ID,
405 zx_dtl.ENTITY_CODE,
406 zx_dtl.EVENT_CLASS_CODE,
407 zx_dtl.TRX_LEVEL_TYPE,
408 zx_dtl.INTERNAL_ORGANIZATION_ID,
409 zx_dtl.tax_date,
410 -- ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
411 ZX_DTL.HQ_ESTB_REG_NUMBER,
412 zx_dtl.TRX_ID,
413 zx_dtl.TRX_LINE_ID ,
414 zx_dtl.TAXABLE_ITEM_SOURCE_ID,
415 zx_dtl.TAX_LINE_ID ,
416 zx_dtl.TRX_LINE_TYPE,
417 -- zx_dtl.TRX_LINE_CLASS,
418 zx_dtl.BILL_FROM_PARTY_TAX_PROF_ID,
419 zx_dtl.BILL_FROM_SITE_TAX_PROF_ID,
420 zx_dtl.SHIP_TO_SITE_TAX_PROF_ID,
421 zx_dtl.SHIP_FROM_SITE_TAX_PROF_ID,
422 zx_dtl.SHIP_TO_PARTY_TAX_PROF_ID,
423 zx_dtl.SHIP_FROM_PARTY_TAX_PROF_ID,
424 -- zx_dtl.BILL_FROM_PARTY_ID,
425 -- zx_dtl.BILL_FROM_PARTY_SITE_ID,
426 zx_dtl.SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
427 zx_dtl.BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
428 zx_dtl.SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
429 zx_dtl.BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
430 zx_dtl.SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
431 zx_dtl.BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
432 zx_dtl.HISTORICAL_FLAG,
433 zx_dtl.posted_flag,
434 xla_event.event_type_code, -- Accounting Columns
435 xla_event.event_number,
436 xla_event.event_status_code,
437 xla_head.je_category_name,
438 xla_head.accounting_date,
439 DECODE(xla_head.gl_transfer_status_code,'Y','Y','NT','Y','N') gl_transfer_status_flag,
440 xla_head.description description_header,
441 xla_line.ae_line_num,
442 xla_line.accounting_class_code,
443 xla_line.description description_line,
444 xla_line.statistical_amount,
445 xla_event.process_status_code,
446 xla_head.gl_transfer_status_code,
447 xla_head.doc_sequence_id,
448 xla_head.doc_sequence_value,
449 xla_line.party_id,
450 xla_line.party_site_id,
451 xla_line.party_type_code,
452 xla_event.event_id,
453 xla_head.ae_header_id,
454 xla_line.code_combination_id,
455 xla_head.period_name,
456 -- zx_dtl.trx_line_id
457 zx_dtl.actg_source_id,
458 zx_dtl.ledger_id,
459 zx_dtl.tax_recoverable_flag,
460 zx_dtl.taxable_amt , --Bug 5409170
461 0, --zx_dtl.tax_amt, --Bug 5409170
462 nvl(zx_dtl.taxable_amt_funcl_curr,zx_dtl.taxable_amt) taxable_amt_funcl_curr,
463 0, --nvl(zx_dtl.tax_amt_funcl_curr,zx_dtl.tax_amt) tax_amt_funcl_curr,
464 zx_dtl.tax_regime_code,
465 zx_dtl.tax,
466 zx_dtl.tax_status_code,
467 zx_dtl.tax_rate_id,
468 row_number() over ( partition by xla_dist.event_id,
469 xla_dist.ae_header_id,
470 xla_dist.ae_line_num,
471 xla_dist.source_distribution_type,
472 xla_dist.tax_line_ref_id,
473 xla_dist.tax_rec_nrec_dist_ref_id
474 order by xla_dist.event_id,
475 xla_dist.ae_header_id,
476 xla_dist.ae_line_num,
477 xla_dist.source_distribution_type,
478 xla_dist.tax_line_ref_id,
479 xla_dist.tax_rec_nrec_dist_ref_id
480 ) ipv_priority
481 -- zx_dtl.tax_type_code
482 FROM zx_rep_trx_detail_t zx_dtl,
483 xla_transaction_entities xla_ent,
484 xla_events xla_event,
485 xla_ae_headers xla_head,
486 xla_ae_lines xla_line,
487 xla_acct_class_assgns acs,
488 xla_assignment_defns_b asd,
489 xla_distribution_links xla_dist
490 WHERE zx_dtl.request_id = c_request_id
491 AND zx_dtl.extract_source_ledger = 'AP'
492 AND zx_dtl.posted_flag = 'A'
493 AND xla_ent.application_id = 200
494 AND nvl(xla_ent.source_id_int_1,-99) = zx_dtl.trx_id -- Accounting Joins
495 AND xla_ent.ledger_id = zx_dtl.ledger_id
496 AND xla_ent.entity_code = 'AP_INVOICES' -- Check this condition
497 AND xla_event.application_id = xla_ent.application_id
498 AND xla_event.entity_id = xla_ent.entity_id
499 AND xla_head.application_id = xla_event.application_id
500 AND xla_head.event_id = xla_event.event_id
501 AND xla_head.balance_type_code = 'A'
502 AND xla_head.ledger_id = c_ledger_id
503 AND xla_line.application_id = xla_head.application_id
504 AND xla_line.ae_header_id = xla_head.ae_header_id
505 AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
506 AND acs.accounting_class_code = xla_line.accounting_class_code
507 AND acs.program_owner_code = asd.program_owner_code
508 AND acs.program_code = asd.program_code
509 AND acs.assignment_owner_code = asd.assignment_owner_code
510 AND acs.assignment_code = asd.assignment_code
511 AND asd.enabled_flag = 'Y'
512 AND zx_dtl.tax_line_id = xla_dist.tax_line_ref_id
513 AND zx_dtl.actg_source_id = xla_dist.tax_rec_nrec_dist_ref_id
514 AND xla_head.ledger_id = xla_line.ledger_id
515 --AND xla_line.ae_header_id = xla_dist.ae_header_id
516 AND xla_head.ae_header_id = xla_dist.ae_header_id
517 AND xla_line.ae_line_num = xla_dist.ae_line_num
518 AND xla_line.application_id = xla_dist.application_id
519 AND ((substr(xla_head.event_type_code,1,10) <> 'PREPAYMENT')
520 OR
521 (substr(xla_head.event_type_code,1,10) = 'PREPAYMENT'
522 AND zx_dtl.trx_line_class IN ('PREPAY_APPLICATION', 'PREPAYMENT INVOICES'))
523 )
524 ) ipv
525 WHERE ipv.ipv_priority = 1
526 UNION ALL
527 SELECT /*+ FULL(zx_dtl) parallel(zx_dtl) */
528 zx_dtl.DETAIL_TAX_LINE_ID,
529 zx_dtl.APPLICATION_ID,
530 zx_dtl.ENTITY_CODE,
531 zx_dtl.EVENT_CLASS_CODE,
532 zx_dtl.TRX_LEVEL_TYPE,
533 zx_dtl.INTERNAL_ORGANIZATION_ID,
534 zx_dtl.tax_date,
535 -- ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
536 ZX_DTL.HQ_ESTB_REG_NUMBER,
537 zx_dtl.TRX_ID,
538 zx_dtl.TRX_LINE_ID ,
539 zx_dtl.TAXABLE_ITEM_SOURCE_ID,
540 zx_dtl.TAX_LINE_ID ,
541 zx_dtl.TRX_LINE_TYPE,
542 -- zx_dtl.TRX_LINE_CLASS,
543 zx_dtl.BILL_FROM_PARTY_TAX_PROF_ID,
544 zx_dtl.BILL_FROM_SITE_TAX_PROF_ID,
545 zx_dtl.SHIP_TO_SITE_TAX_PROF_ID,
546 zx_dtl.SHIP_FROM_SITE_TAX_PROF_ID,
547 zx_dtl.SHIP_TO_PARTY_TAX_PROF_ID,
548 zx_dtl.SHIP_FROM_PARTY_TAX_PROF_ID,
549 -- zx_dtl.BILL_FROM_PARTY_ID,
550 -- zx_dtl.BILL_FROM_PARTY_SITE_ID,
551 zx_dtl.SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
552 zx_dtl.BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
553 zx_dtl.SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
554 zx_dtl.BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
555 zx_dtl.SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
556 zx_dtl.BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
557 zx_dtl.HISTORICAL_FLAG,
558 zx_dtl.posted_flag,
559 TO_CHAR(NULL), --xla_event.event_type_code, -- Accounting Columns
560 TO_NUMBER(NULL), --xla_event.event_number,
561 TO_CHAR(NULL), --xla_event.event_status_code,
562 TO_CHAR(NULL), --xla_head.je_category_name,
563 TO_DATE(NULL), --xla_head.accounting_date,
564 TO_CHAR(NULL), --xla_head.gl_transfer_status_code,
565 TO_CHAR(NULL), --xla_head.description,
566 TO_NUMBER(NULL), --xla_line.ae_line_num,
567 TO_CHAR(NULL), --xla_line.accounting_class_code,
568 TO_CHAR(NULL), --xla_line.description,
569 TO_NUMBER(NULL), --xla_line.statistical_amount,
570 TO_CHAR(NULL), --xla_event.process_status_code,
571 TO_CHAR(NULL), --xla_head.gl_transfer_status_code,
572 TO_NUMBER(NULL), --xla_head.doc_sequence_id,
573 TO_NUMBER(NULL), --xla_head.doc_sequence_value,
574 TO_NUMBER(NULL), --xla_line.party_id,
575 TO_NUMBER(NULL), --xla_line.party_site_id,
576 TO_CHAR(NULL), --xla_line.party_type_code,
577 TO_NUMBER(NULL), --xla_event.event_id,
578 TO_NUMBER(NULL), --xla_head.ae_header_id,
579 TO_NUMBER(NULL), --xla_line.code_combination_id,
580 TO_CHAR(NULL), --xla_head.period_name,
581 TO_NUMBER(NULL), --zx_dtl.trx_line_id
582 zx_dtl.ledger_id,
583 zx_dtl.tax_recoverable_flag,
584 zx_dtl.TAXABLE_AMT , --Bug 5409170
585 0, --zx_dtl.tax_amt , --Bug 5409170
586 nvl(zx_dtl.taxable_amt_funcl_curr,zx_dtl.TAXABLE_AMT) ,--Bug 5405785
587 0, --nvl(zx_dtl.tax_amt_funcl_curr,zx_dtl.tax_amt), --Bug 5405785
588 zx_dtl.tax_regime_code,
589 zx_dtl.tax,
590 zx_dtl.tax_status_code,
591 zx_dtl.tax_rate_id,
592 to_number(NULL)
593 -- tax_type_code
594 FROM zx_rep_trx_detail_t zx_dtl
595 WHERE zx_dtl.request_id = c_request_id
596 AND zx_dtl.extract_source_ledger = 'AP'
597 AND ( (nvl(zx_dtl.posted_flag,'N') = 'N')
598 OR
599 (zx_dtl.posted_flag in ('A', 'Y') AND zx_dtl.tax_line_id IS NULL)
600 );
601
602 BEGIN
603
604 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
605 G_REQUEST_ID := P_TRL_GLOBAL_VARIABLES_REC.request_id;
606
607
608 IF (g_level_procedure >= g_current_runtime_level ) THEN
609 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info.BEGIN',
610 'update_additional_info(+)');
611 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
612 'Request ID : '||to_char(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID));
613 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
614 'Reporting Ledger : '||to_char(p_trl_global_variables_rec.reporting_ledger_id));
615 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
616 'Primary Ledger : '||to_char(p_trl_global_variables_rec.ledger_id));
617 END IF;
618
619 gl_mc_info.get_ledger_currency(NVL(p_trl_global_variables_rec.reporting_ledger_id,
620 p_trl_global_variables_rec.ledger_id), G_FUN_CURRENCY_CODE);
621
622 XLA_SECURITY_PKG.set_security_context(p_application_id => 602); --Bug 5393051
623 -- l_ledger_id := P_TRL_GLOBAL_VARIABLES_REC.ledger_id; --Bug 5393051
624
625 l_ledger_id := NVL(p_trl_global_variables_rec.reporting_ledger_id,
626 P_TRL_GLOBAL_VARIABLES_REC.ledger_id);
627
628
629 BEGIN
630 SELECT name
631 INTO l_gl_name
632 FROM gl_ledgers
633 WHERE ledger_id = l_ledger_id
634 AND rownum=1;
635 EXCEPTION
636 WHEN OTHERS THEN
637 l_gl_name := NULL;
638 END;
639
640
641 IF p_trl_global_variables_rec.reporting_ledger_id IS NOT NULL
642 OR p_trl_global_variables_rec.report_name = 'ZXXTATAT' THEN
643 UPDATE zx_rep_trx_detail_t
644 SET tax_amt = 0,
645 tax_amt_funcl_curr = 0
646 WHERE request_id = G_REQUEST_ID
647 AND extract_source_ledger = 'AP' ;
648 END IF;
649
650 -- bug 13607557 moved the code out of the loop
651 G_CHART_OF_ACCOUNTS_ID := p_trl_global_variables_rec.chart_of_accounts_id;
652 l_balancing_segment := fa_rx_flex_pkg.flex_sql(
653 p_application_id =>101,
654 p_id_flex_code => 'GL#',
655 p_id_flex_num => g_chart_of_accounts_id,
656 p_table_alias => '',
657 p_mode => 'SELECT',
658 p_qualifier => 'GL_BALANCING');
659 l_accounting_segment := fa_rx_flex_pkg.flex_sql(
660 p_application_id =>101,
661 p_id_flex_code => 'GL#',
662 p_id_flex_num => g_chart_of_accounts_id,
663 p_table_alias => '',
664 p_mode => 'SELECT',
665 p_qualifier => 'GL_ACCOUNT');
666
667 G_REP_CONTEXT_ID := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID,
668 P_TRL_GLOBAL_VARIABLES_REC.request_id);
669
670 OPEN rep_detail_cursor(p_trl_global_variables_rec.request_id,
671 NVL(p_trl_global_variables_rec.reporting_ledger_id,p_trl_global_variables_rec.ledger_id)
672 );
673 LOOP
674 FETCH rep_detail_cursor BULK COLLECT INTO
675 GT_DETAIL_TAX_LINE_ID,
676 GT_APPLICATION_ID,
677 GT_ENTITY_CODE,
678 GT_EVENT_CLASS_CODE,
679 GT_TRX_LEVEL_TYPE,
680 GT_INTERNAL_ORGANIZATION_ID,
681 GT_TAX_DATE,
682 -- GT_TAX_RATE_VAT_TRX_TYPE_CODE,
683 GT_HQ_ESTB_REG_NUMBER,
684 GT_TRX_ID,
685 GT_TRX_LINE_ID,
686 GT_TRX_LINE_DIST_ID,
687 GT_TAX_LINE_ID,
688 GT_TRX_LINE_TYPE,
689 -- GT_TRX_LINE_CLASS,
690 GT_BILL_FROM_PTY_TAX_PROF_ID,
691 GT_BILL_FROM_SITE_TAX_PROF_ID,
692 GT_SHIP_TO_SITE_TAX_PROF_ID,
693 GT_SHIP_FROM_SITE_TAX_PROF_ID,
694 GT_SHIP_TO_PARTY_TAX_PROF_ID,
695 GT_SHIP_FROM_PTY_TAX_PROF_ID,
696 -- GT_BILL_FROM_PARTY_ID,
697 -- GT_BILL_FROM_PARTY_SITE_ID,
698 GT_SHIPPING_TP_ADDRESS_ID,
699 GT_BILLING_TP_ADDRESS_ID,
700 GT_SHIPPING_TP_SITE_ID,
701 GT_BILLING_TP_SITE_ID,
702 GT_SHIPPING_TP_ID,
703 GT_BILLING_TRADING_PARTNER_ID,
704 GT_HISTORICAL_FLAG,
705 GT_POSTED_FLAG,
706 gt_actg_event_type_code,
707 gt_actg_event_number,
708 gt_actg_event_status_flag,
709 gt_actg_category_code,
710 gt_accounting_date,
711 gt_gl_transfer_flag,
712 -- gt_gl_transfer_run_id,
713 gt_actg_header_description,
714 gt_actg_line_num,
715 gt_actg_line_type_code,
716 gt_actg_line_description,
717 gt_actg_stat_amt,
718 gt_actg_error_code,
719 gt_gl_transfer_code,
720 gt_actg_doc_sequence_id,
721 -- gt_actg_doc_sequence_name,
722 gt_actg_doc_sequence_value,
723 gt_actg_party_id,
724 gt_actg_party_site_id,
725 gt_actg_party_type,
726 gt_actg_event_id,
727 gt_actg_header_id,
728 -- gt_actg_source_table,
729 gt_actg_line_ccid,
730 gt_period_name,
731 gt_actg_source_id,
732 gt_ledger_id,
733 gt_tax_recoverable_flag,
734 GT_TAXABLE_AMT, --Bug 5409170
735 GT_TAX_AMT, --Bug 5409170
736 GT_TAXABLE_AMT_FUNCL_CURR, --Bug 5405785
737 GT_TAX_AMT_FUNCL_CURR, --Bug 5405785
738 gt_tax_regime_code,
739 gt_tax,
740 gt_tax_status_code,
741 gt_tax_rate_id,
742 GT_IPV_PRIORITY
743 -- GT_TAX_TYPE_CODE
744 LIMIT C_LINES_PER_COMMIT;
745
746 l_count := GT_DETAIL_TAX_LINE_ID.count;
747 IF (g_level_procedure >= g_current_runtime_level ) THEN
748 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
749 'Rows fetched by rep_detail_cursor :'||to_char(l_count));
750 END IF;
751
752 j:=0;
753
754 IF l_count > 0 THEN
755 initialize_variables(l_count);
756
757 FOR i in 1..l_count
758 LOOP
759
760 IF (g_level_procedure >= g_current_runtime_level ) THEN
761 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
762 'Populate Cursor Line Number :'||to_char(i));
763 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
764 'GT_BILL_FROM_PTY_TAX_PROF_ID(i) :'||to_char(GT_BILL_FROM_PTY_TAX_PROF_ID(i)));
765 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
766 'GT_BILLING_TRADING_PARTNER_ID(i) :'||to_char(GT_BILLING_TRADING_PARTNER_ID(i)));
767 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
768 'include_accounting_segments :'||p_trl_global_variables_rec.include_accounting_segments);
769 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
770 'gt_posted_flag :'||gt_posted_flag(i));
771 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
772 'GT_TAXABLE_AMT(i) :'||to_char(GT_TAXABLE_AMT(i)));
773 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
774 'GT_TAX_AMT(i) :'||to_char(GT_TAX_AMT(i)));
775 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
776 'GT_IPV_PRIORITY(i) :'||to_char(GT_IPV_PRIORITY(i)));
777 END IF;
778
779 -- Intialize discount amount variables --
780 GT_TAXABLE_DISC_AMT(i) := NULL ;
781 GT_TAXABLE_DISC_AMT_FUNCL_CURR(i) := NULL;
782 GT_TAX_DISC_AMT(i) := NULL;
783 GT_TAX_DISC_AMT_FUNCL_CURR(i) := NULL;
784 -- End of Initialization ----
785
786 GT_LEDGER_NAME(i) := l_gl_name;
787 extract_party_info(i,p_trl_global_variables_rec.report_name);
788
789 -- This api populates first party registration number if the HQ_ESTB_REG_NUMBER is null
790 --
791 IF GT_HQ_ESTB_REG_NUMBER(i) IS NULL AND
792 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM IS NULL THEN
793 populate_tax_reg_num(
794 P_TRL_GLOBAL_VARIABLES_REC,
795 GT_INTERNAL_ORGANIZATION_ID(i),
796 GT_TAX_DATE(i),
797 i);
798 ELSE
799 GT_TAX_REG_NUM(i) := GT_HQ_ESTB_REG_NUMBER(i);
800 END IF;
801
802 --Bug 5438409 : Interchange the condition check , first accounted condition should be checked
803 --and then include_accounting_segments = 'Y' should bne checked ..
804 IF ( gt_posted_flag(i) IN ('A','Y')
805 AND
806 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' ) THEN
807 IF (g_level_procedure >= g_current_runtime_level ) THEN
808 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
809 ' Accounting API calls :');
810 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
811 ' include_accounting_segments :'||p_trl_global_variables_rec.include_accounting_segments);
812 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
813 ' gt_posted_flag :'||gt_posted_flag(i));
814 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
815 ' GT_actg_EVENT_ID :'||to_char(GT_actg_EVENT_ID(i)));
816 END IF;
817
818 IF p_trl_global_variables_rec.include_accounting_segments='Y' THEN --Bug 5438409
819 j:=j+1;
820 agt_detail_tax_line_id(j) := gt_detail_tax_line_id(i);
821 agt_actg_event_type_code(j) := gt_actg_event_type_code(i);
822 agt_actg_event_number(j) := gt_actg_event_number(i);
823 agt_actg_event_status_flag(j) := gt_actg_event_status_flag(i);
824 agt_actg_category_code(j) := gt_actg_category_code(i);
825 agt_accounting_date(j) := gt_accounting_date(i);
826 agt_gl_transfer_flag(j) := gt_gl_transfer_flag(i);
827 -- agt_gl_transfer_run_id(j) := gt_gl_transfer_run_id(i);
828 agt_actg_header_description(j) := gt_actg_header_description(i);
829 agt_actg_line_num(j) := gt_actg_line_num(i);
830 agt_actg_line_type_code(j) := gt_actg_line_type_code(i);
831 agt_actg_line_description(j) := gt_actg_line_description(i);
832 agt_actg_stat_amt(j) := gt_actg_stat_amt(i);
833 agt_actg_error_code(j) := gt_actg_error_code(i);
834 agt_gl_transfer_code(j) := gt_gl_transfer_code(i);
835 agt_actg_doc_sequence_id(j) := gt_actg_doc_sequence_id(i);
836 -- agt_actg_doc_sequence_name(j) := gt_actg_doc_sequence_name(i);
837 agt_actg_doc_sequence_value(j) := gt_actg_doc_sequence_value(i);
838 agt_actg_party_id(j) := gt_actg_party_id(i);
839 agt_actg_party_site_id(j) := gt_actg_party_site_id(i);
840 agt_actg_party_type(j) := gt_actg_party_type(i);
841 agt_actg_event_id(j) := gt_actg_event_id(i);
842 agt_actg_header_id(j) := gt_actg_header_id(i);
843 agt_actg_source_id(j) := gt_actg_source_id(i);
844 -- agt_actg_source_table(j) := gt_actg_source_table(i);
845 agt_actg_line_ccid(j) := gt_actg_line_ccid(i);
846 agt_period_name(j) := gt_period_name(i);
847
848 IF p_trl_global_variables_rec.report_name <> 'ZXXTATAT' THEN
849 get_accounting_info(
850 GT_APPLICATION_ID(i),
851 GT_ENTITY_CODE(i),
852 GT_EVENT_CLASS_CODE(i),
853 GT_TRX_LEVEL_TYPE(i),
854 GT_TRX_ID(i),
855 GT_TRX_LINE_ID(i),
856 GT_TRX_LINE_DIST_ID(i),
857 GT_TAX_LINE_ID(i),
858 GT_actg_EVENT_ID(i),
859 GT_actg_HEADER_ID(i),
860 GT_ACTG_SOURCE_ID(i),
861 l_balancing_segment,
862 l_accounting_segment,
863 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
864 P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_DISCOUNTS,
865 GT_TAX_RECOVERABLE_FLAG(i),
866 gt_tax_regime_code(i),
867 gt_tax(i),
868 gt_tax_status_code(i),
869 gt_tax_rate_id(i),
870 GT_INTERNAL_ORGANIZATION_ID(i),
871 NVL(p_trl_global_variables_rec.reporting_ledger_id,p_trl_global_variables_rec.ledger_id), --l_ledger_id,
872 j) ;
873 END IF;
874 END IF ; -- Include accounting segement check -- --Bug 5438409
875
876 IF p_trl_global_variables_rec.reporting_ledger_id IS NOT NULL
877 OR
878 p_trl_global_variables_rec.report_name = 'ZXXTATAT' THEN
879
880 get_accounting_amounts(
881 GT_APPLICATION_ID(i),
882 GT_ENTITY_CODE(i),
883 GT_EVENT_CLASS_CODE(i),
884 GT_TRX_LEVEL_TYPE(i),
885 GT_TRX_ID(i),
886 GT_TRX_LINE_ID(i),
887 GT_TAX_LINE_ID(i),
888 GT_actg_EVENT_ID(i),
889 GT_actg_HEADER_ID(i),
890 GT_ACTG_SOURCE_ID(i),
891 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
892 p_trl_global_variables_rec.report_name,
893 NVL(p_trl_global_variables_rec.reporting_ledger_id,p_trl_global_variables_rec.ledger_id), --l_ledger_id,
894 i,--Need to change this to j if inserting into accouting table
895 gt_actg_line_num(i),
896 GT_TRX_LINE_DIST_ID(i)) ;
897 END IF;
898
899 IF (g_level_statement >= g_current_runtime_level ) THEN
900 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',' i : '||to_Char(i)||
901 'Taxable Amt : '|| to_char(GT_TAXABLE_AMT(i)) ||'TAXABLE_AMT_FUNCL_CURR : '||GT_TAXABLE_AMT_FUNCL_CURR(i));
902 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',' i : '||to_Char(i)||
903 'Tax Amt : '|| to_char(GT_TAX_AMT(i)) ||'TAX_AMT_FUNCL_CURR : '||GT_TAX_AMT_FUNCL_CURR(i));
904 END IF;
905
906 ELSE -- Discounts API Call --
907
908 -- New code introduced for Header Level Discounts --
909
910 OPEN get_system_info_cur_hdr(GT_INTERNAL_ORGANIZATION_ID(i));
911 FETCH get_system_info_cur_hdr
912 INTO l_disc_dist_method_hdr,
913 l_disc_inv_less_tax_flag_hdr,
914 l_liability_post_lkp_code_hdr;
915 CLOSE get_system_info_cur_hdr;
916
917 IF NVL(l_disc_inv_less_tax_flag_hdr, 'N') = 'N' AND
918 NVL(l_disc_dist_method_hdr, 'SYSTEM') <> 'SYSTEM' THEN
919
920 IF P_TRL_GLOBAL_VARIABLES_REC.INCLUDE_DISCOUNTS = 'Y' THEN
921 get_discount_info(i,
922 GT_TRX_ID(i),
923 GT_TAX_LINE_ID(i),
924 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
925 GT_ACTG_SOURCE_ID(i),
926 GT_TRX_LINE_ID(i),
927 GT_TRX_LINE_DIST_ID(i),
928 GT_TAX_RECOVERABLE_FLAG(i),
929 gt_tax_regime_code(i),
930 gt_tax(i),
931 gt_tax_status_code(i),
932 gt_tax_rate_id(i),
933 NVL(p_trl_global_variables_rec.reporting_ledger_id,p_trl_global_variables_rec.ledger_id),
934 l_disc_dist_method_hdr,
935 l_liability_post_lkp_code_hdr);
936 END IF;
937 END IF;
938
939 END IF; -- Posted flag check --
940
941 END LOOP;
942 ELSE
943 EXIT;
944 END IF;
945
946 -- EXIT WHEN rep_detail_cursor%NOTFOUND
947 -- OR rep_detail_cursor%NOTFOUND IS NULL;
948
949 -- END LOOP;
950
951 update_zx_rep_detail_t(l_count);
952 IF p_trl_global_variables_rec.include_accounting_segments='Y' THEN
953 insert_actg_info(j);
954 END IF;
955
956 EXIT WHEN rep_detail_cursor%NOTFOUND
957 OR rep_detail_cursor%NOTFOUND IS NULL;
958
959 END LOOP;
960
961 bulk_populate_zx_dtl (p_register_type => P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE);
962
963 IF (g_level_procedure >= g_current_runtime_level ) THEN
964 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info.END',
965 'update_additional_info(-)');
966 END IF;
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
971 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
972 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
973 FND_MSG_PUB.Add;
974 IF (g_level_unexpected >= g_current_runtime_level ) THEN
975 FND_LOG.STRING(g_level_unexpected,
976 'ZX.TRL.ZX_AP_POPULATE_PKG.update_additional_info',
977 g_error_buffer);
978 END IF;
979
980 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := g_retcode;
981
982 END update_additional_info;
983
984
985 PROCEDURE extract_party_info( i IN BINARY_INTEGER,
986 P_REPORT_NAME IN VARCHAR2) IS
987
988
989 l_party_id zx_rep_trx_detail_t.bill_from_party_id%TYPE;
990 l_party_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE;
991 l_party_profile_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE;
992 l_site_profile_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE;
993 l_bill_ship_pty_id zx_rep_trx_detail_t.bill_from_party_id%TYPE;
994 l_bill_ship_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE;
995 l_tbl_index_party BINARY_INTEGER;
996 l_tbl_index_site BINARY_INTEGER;
997
998 -- If party_id is NULL and Historical flag 'N' then get the party ID from zx_party_tax_profile
999 --CURSOR ledger_cur (c_ledger_id ZX_REP_TRX_DETAIL_T.ledger_id%TYPE) IS
1000 -- SELECT name
1001 -- FROM gl_ledgers
1002 -- WHERE ledger_id = c_ledger_id
1003 -- AND rownum = 1;
1004
1005 -- CURSOR party_id_cur
1006 -- (c_bill_from_ptp_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE) IS
1007 -- SELECT party_id
1008 -- FROM zx_party_tax_profile
1009 -- WHERE PARTY_TAX_PROFILE_ID = c_bill_from_ptp_id
1010 -- AND party_type_code = 'THIRD_PARTY';
1011
1012 -- CURSOR party_site_id_cur
1013 -- (c_bill_from_stp_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE) IS
1014 -- SELECT party_id
1015 -- FROM zx_party_tax_profile
1016 -- WHERE PARTY_TAX_PROFILE_ID = c_bill_from_stp_id
1017 -- AND party_type_code = 'THIRD_PARTY_SITE';
1018
1019 -- If party_id is NOT NULL and Historical flag 'Y' then get the party tax profile ID from zx_party_tax_profile
1020 CURSOR party_reg_num_cur
1021 (c_bill_from_party_id zx_rep_trx_detail_t.bill_from_party_id%TYPE) IS
1022 SELECT rep_registration_number
1023 FROM zx_party_tax_profile
1024 WHERE party_id = c_bill_from_party_id
1025 AND party_type_code = 'THIRD_PARTY';
1026
1027 CURSOR party_site_reg_num_cur
1028 (c_bill_from_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE) IS
1029 SELECT rep_registration_number
1030 FROM zx_party_tax_profile
1031 WHERE party_id = c_bill_from_site_id
1032 AND party_type_code = 'THIRD_PARTY_SITE';
1033
1034 CURSOR party_cur
1035 (c_bill_from_party_id zx_rep_trx_detail_t.bill_from_party_id%TYPE) IS
1036 SELECT SEGMENT1,
1037 --VAT_REGISTRATION_NUM,
1038 DECODE(P_REPORT_NAME,'ZXCLPPLR',
1039 NUM_1099||'-'||GLOBAL_ATTRIBUTE12,
1040 NUM_1099||GLOBAL_ATTRIBUTE12),
1041 VENDOR_NAME,
1042 VENDOR_NAME_ALT,
1043 STANDARD_INDUSTRY_CLASS,
1044 PARTY_ID
1045 FROM ap_suppliers
1046 WHERE vendor_id = c_bill_from_party_id;
1047
1048 CURSOR party_site_cur
1049 (c_bill_from_site_id zx_rep_trx_detail_t.bill_from_party_site_id%TYPE) IS
1050 SELECT CITY,
1051 COUNTY,
1052 STATE,
1053 PROVINCE,
1054 ADDRESS_LINE1,
1055 ADDRESS_LINE2,
1056 ADDRESS_LINE3,
1057 ADDRESS_LINES_ALT,
1058 COUNTRY,
1059 ZIP,
1060 -- VENDOR_ID,
1061 -- VENDOR_SITE_ID,
1062 -- TAX_REPORTING_SITE_FLAG,
1063 GLOBAL_ATTRIBUTE17,
1064 VENDOR_SITE_CODE_ALT,
1065 VENDOR_SITE_CODE,
1066 -- VAT_REGISTRATION_NUM
1067 PARTY_SITE_ID
1068 FROM ap_supplier_sites_all
1069 WHERE vendor_site_id = c_bill_from_site_id;
1070
1071
1072 BEGIN
1073
1074 IF (g_level_procedure >= g_current_runtime_level ) THEN
1075 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info.BEGIN',
1076 'extract_party_info(+)'||to_char(i));
1077 END IF;
1078
1079 -- OPEN ledger_cur(GT_LEDGER_ID(i));
1080 -- FETCH ledger_cur into GT_LEDGER_NAME(i);
1081 -- CLOSE ledger_cur;
1082
1083 --IF NVL(gt_historical_flag(i),'N') = 'N' AND GT_BILL_FROM_PTY_TAX_PROF_ID(i) IS NOT NULL THEN
1084 -- IF GT_BILL_FROM_PTY_TAX_PROF_ID(i) IS NOT NULL THEN
1085 -- OPEN party_id_cur(GT_BILL_FROM_PTY_TAX_PROF_ID(i));
1086 -- FETCH party_id_cur INTO l_party_id;
1087 --
1088 -- OPEN party_site_id_cur(GT_BILL_FROM_SITE_TAX_PROF_ID(i));
1089 -- FETCH party_site_id_cur INTO l_party_site_id;
1090 -- ELSE
1091 -- l_bill_ship_pty_id := NVL(GT_SHIPPING_TP_ID(i),GT_BILLING_TRADING_PARTNER_ID(i));
1092 -- l_bill_ship_site_id := NVL(GT_SHIPPING_TP_ADDRESS_ID(i), GT_BILLING_TP_ADDRESS_ID(i));
1093 --
1094 -- OPEN party_profile_id_cur (l_bill_ship_pty_id);
1095 -- FETCH party_profile_id_cur into l_party_profile_id;
1096 --
1097 -- OPEN site_profile_id_cur(l_bill_ship_site_id);
1098 -- FETCH site_profile_id_cur INTO l_site_profile_id;
1099 --
1100 -- l_party_id := GT_BILL_FROM_PARTY_ID(i);
1101 -- l_party_site_id := GT_BILL_FROM_PARTY_SITE_ID(i);
1102
1103 -- END IF;
1104
1105 l_bill_ship_pty_id := NVL(GT_SHIPPING_TP_ID(i),GT_BILLING_TRADING_PARTNER_ID(i));
1106 l_bill_ship_site_id := NVL(GT_SHIPPING_TP_ADDRESS_ID(i), GT_BILLING_TP_ADDRESS_ID(i));
1107
1108 IF (g_level_procedure >= g_current_runtime_level ) THEN
1109 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1110 ' l_party_id :'||to_char(l_bill_ship_pty_id)||' '||to_char(l_bill_ship_site_id));
1111 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1112 ' GT_DETAIL_TAX_LINE_ID :'||to_char(l_party_id)||' '||to_char(GT_DETAIL_TAX_LINE_ID(i)));
1113 END IF;
1114
1115 IF (g_level_procedure >= g_current_runtime_level ) THEN
1116 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1117 ' l_party_id :'||to_char(l_bill_ship_pty_id)||' '||to_char(l_bill_ship_site_id));
1118 END IF;
1119
1120
1121 IF l_bill_ship_pty_id IS NOT NULL THEN
1122 --l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_bill_ship_pty_id), 1,8192);
1123 l_tbl_index_party := to_char(l_bill_ship_pty_id);
1124
1125 IF g_party_info_ap_tbl.EXISTS(l_tbl_index_party) THEN
1126
1127 GT_BILLING_TP_NUMBER(i) := g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NUMBER ;
1128 -- GT_BILLING_TP_TAX_REG_NUM(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM;
1129 GT_BILLING_TP_TAXPAYER_ID(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID;
1130 GT_BILLING_TP_NAME(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME;
1131 GT_BILLING_TP_NAME_ALT(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT;
1132 GT_BILLING_TP_SIC_CODE(i) :=g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE;
1133 GT_BILL_FROM_PARTY_ID(i) := g_party_info_ap_tbl(l_tbl_index_party).BILL_FROM_PARTY_ID;
1134
1135 ELSE
1136
1137 OPEN party_cur (l_bill_ship_pty_id);
1138 FETCH party_cur INTO
1139 GT_BILLING_TP_NUMBER(i),
1140 -- GT_BILLING_TP_TAX_REG_NUM(i),
1141 GT_BILLING_TP_TAXPAYER_ID(i),
1142 GT_BILLING_TP_NAME(i),
1143 GT_BILLING_TP_NAME_ALT(i),
1144 GT_BILLING_TP_SIC_CODE(i),
1145 GT_BILL_FROM_PARTY_ID(i);
1146 -- l_party_id;
1147
1148
1149 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
1150 --g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM := GT_BILLING_TP_TAX_REG_NUM(i);
1151 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID := GT_BILLING_TP_TAXPAYER_ID(i);
1152 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME := GT_BILLING_TP_NAME(i);
1153 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT := GT_BILLING_TP_NAME_ALT(i);
1154 g_party_info_ap_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE := GT_BILLING_TP_SIC_CODE(i);
1155 g_party_info_ap_tbl(l_tbl_index_party).BILL_FROM_PARTY_ID := GT_BILL_FROM_PARTY_ID(i);
1156
1157 IF (g_level_procedure >= g_current_runtime_level ) THEN
1158 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1159 ' GT_BILLING_TP_NUMBER(i) :'||GT_BILLING_TP_NUMBER(i));
1160 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1161 ' l_party_id : Name :'||to_char(l_party_id)||'-'||GT_BILLING_TP_NAME(i));
1162 END IF;
1163 END IF;
1164 IF (g_level_procedure >= g_current_runtime_level ) THEN
1165 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1166 ' GT_BILLING_TP_NUMBER(i) :'||GT_BILLING_TP_NUMBER(i));
1167 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1168 ' l_party_id : Name :'||to_char(l_bill_ship_pty_id)||'-'||GT_BILLING_TP_NAME(i));
1169 END IF;
1170 l_party_id := GT_BILL_FROM_PARTY_ID(i);
1171 OPEN party_reg_num_cur (l_party_id);
1172 FETCH party_reg_num_cur into GT_BILLING_TP_TAX_REG_NUM(i);
1173 END IF;
1174
1175 IF l_bill_ship_site_id IS NOT NULL THEN
1176 --l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_bill_ship_site_id), 1,8192);
1177 l_tbl_index_site := to_char(l_bill_ship_site_id);
1178
1179 IF g_party_site_tbl.EXISTS(l_tbl_index_site) THEN
1180
1181 GT_BILLING_TP_CITY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_CITY;
1182 -- GT_BILLING_TP_COUNTY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY;
1183 GT_BILLING_TP_COUNTY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY;
1184 GT_BILLING_TP_STATE(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_STATE;
1185 GT_BILLING_TP_PROVINCE(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_PROVINCE;
1186 GT_BILLING_TP_ADDRESS1(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1;
1187 GT_BILLING_TP_ADDRESS2(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2;
1188 GT_BILLING_TP_ADDRESS3(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3;
1189 GT_BILLING_TP_ADDR_LINES_ALT(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT;
1190 GT_BILLING_TP_COUNTRY(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTRY;
1191 GT_BILLING_TP_POSTAL_CODE(i) := g_party_site_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE;
1192 GT_GDF_PO_VENDOR_SITE_ATT17(i) := g_party_site_tbl(l_tbl_index_site).GDF_PO_VENDOR_SITE_ATT17;
1193 GT_BILLING_TP_SITE_NAME_ALT(i) :=g_party_site_tbl(l_tbl_index_site).BILLING_TP_SITE_NAME_ALT;
1194 GT_BILLING_TP_SITE_NAME(i) :=g_party_site_tbl(l_tbl_index_site).BILLING_TP_SITE_NAME;
1195 GT_BILL_FROM_PARTY_SITE_ID(i) := g_party_site_tbl(l_tbl_index_site).BILL_FROM_PARTY_SITE_ID;
1196 -- GT_BILLING_SITE_TAX_REG_NUM(i) := g_party_site_tbl(l_tbl_index_site).BILLING_SITE_TAX_REG_NUM;
1197
1198 ELSE
1199
1200 OPEN party_site_cur (l_bill_ship_site_id);
1201 FETCH party_site_cur INTO
1202 GT_BILLING_TP_CITY(i),
1203 GT_BILLING_TP_COUNTY(i),
1204 GT_BILLING_TP_STATE(i),
1205 GT_BILLING_TP_PROVINCE(i),
1206 GT_BILLING_TP_ADDRESS1(i),
1207 GT_BILLING_TP_ADDRESS2(i),
1208 GT_BILLING_TP_ADDRESS3(i),
1209 GT_BILLING_TP_ADDR_LINES_ALT(i),
1210 GT_BILLING_TP_COUNTRY(i),
1211 GT_BILLING_TP_POSTAL_CODE(i),
1212 GT_GDF_PO_VENDOR_SITE_ATT17(i),
1213 GT_BILLING_TP_SITE_NAME_ALT(i),
1214 GT_BILLING_TP_SITE_NAME(i),
1215 GT_BILL_FROM_PARTY_SITE_ID(i);
1216
1217 --l_party_site_id;
1218 -- GT_BILLING_SITE_TAX_REG_NUM(i);
1219
1220 g_party_site_tbl(l_tbl_index_site).BILLING_TP_CITY := GT_BILLING_TP_CITY(i);
1221 g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
1222 g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
1223 g_party_site_tbl(l_tbl_index_site).BILLING_TP_PROVINCE := GT_BILLING_TP_STATE(i);
1224 g_party_site_tbl(l_tbl_index_site).BILLING_TP_PROVINCE := GT_BILLING_TP_PROVINCE(i);
1225 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS1 := GT_BILLING_TP_ADDRESS1(i);
1226 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS2 := GT_BILLING_TP_ADDRESS2(i);
1227 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDRESS3 := GT_BILLING_TP_ADDRESS3(i);
1228 g_party_site_tbl(l_tbl_index_site).BILLING_TP_ADDR_LINES_ALT := GT_BILLING_TP_ADDR_LINES_ALT(i);
1229 g_party_site_tbl(l_tbl_index_site).BILLING_TP_COUNTRY := GT_BILLING_TP_COUNTRY(i);
1230 g_party_site_tbl(l_tbl_index_site).BILLING_TP_POSTAL_CODE := GT_BILLING_TP_POSTAL_CODE(i);
1231 g_party_site_tbl(l_tbl_index_site).GDF_PO_VENDOR_SITE_ATT17 := GT_GDF_PO_VENDOR_SITE_ATT17(i);
1232 g_party_site_tbl(l_tbl_index_site).BILLING_TP_SITE_NAME_ALT := GT_BILLING_TP_SITE_NAME_ALT(i);
1233 g_party_site_tbl(l_tbl_index_site).BILLING_TP_SITE_NAME := GT_BILLING_TP_SITE_NAME(i);
1234 g_party_site_tbl(l_tbl_index_site).BILL_FROM_PARTY_SITE_ID := GT_BILL_FROM_PARTY_SITE_ID(i);
1235 -- g_party_site_tbl(l_tbl_index_site).BILLING_SITE_TAX_REG_NUM := GT_BILLING_SITE_TAX_REG_NUM(i);
1236 END IF;
1237 IF (g_level_procedure >= g_current_runtime_level ) THEN
1238 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1239 ' l_party_site_id :'||to_char(l_party_site_id));
1240 END IF;
1241 l_party_site_id := GT_BILL_FROM_PARTY_SITE_ID(i);
1242 OPEN party_site_reg_num_cur(l_party_site_id);
1243 FETCH party_site_reg_num_cur INTO GT_BILLING_SITE_TAX_REG_NUM(i);
1244 END IF;
1245
1246 IF (g_level_procedure >= g_current_runtime_level ) THEN
1247 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info.END',
1248 'extract_party_info(-)');
1249 END IF;
1250
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1255 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1256 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1257 FND_MSG_PUB.Add;
1258 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1259 FND_LOG.STRING(g_level_unexpected,
1260 'ZX.TRL.ZX_AP_POPULATE_PKG.extract_party_info',
1261 g_error_buffer);
1262 END IF;
1263 g_retcode := 2;
1264 END extract_party_info;
1265
1266 -- Begin Accounting procedures --
1267
1268
1269 PROCEDURE get_accounting_info (
1270 P_APPLICATION_ID IN NUMBER,
1271 P_ENTITY_CODE IN VARCHAR2,
1272 P_EVENT_CLASS_CODE IN VARCHAR2,
1273 P_TRX_LEVEL_TYPE IN VARCHAR2,
1274 P_TRX_ID IN NUMBER,
1275 P_TRX_LINE_ID IN NUMBER,
1276 P_TRX_LINE_DIST_ID IN NUMBER,
1277 P_TAX_LINE_ID IN NUMBER,
1278 -- P_ENTITY_ID IN NUMBER,
1279 P_EVENT_ID IN NUMBER,
1280 P_AE_HEADER_ID IN NUMBER,
1281 P_TAX_DIST_ID IN NUMBER,
1282 P_BALANCING_SEGMENT IN VARCHAR2,
1283 P_ACCOUNTING_SEGMENT IN VARCHAR2,
1284 P_SUMMARY_LEVEL IN VARCHAR2,
1285 P_INCLUDE_DISCOUNTS IN VARCHAR2,
1286 P_TAX_REC_FLAG IN VARCHAR2,
1287 p_tax_regime_code IN VARCHAR2,
1288 p_tax IN VARCHAR2,
1289 p_tax_status_code IN VARCHAR2,
1290 p_tax_rate_id IN NUMBER,
1291 P_ORG_ID IN NUMBER,
1292 P_LEDGER_ID IN NUMBER,
1293 j IN BINARY_INTEGER) IS
1294
1295 CURSOR get_system_info_cur(c_org_id NUMBER) IS
1296 SELECT discount_distribution_method,
1297 disc_is_inv_less_tax_flag,
1298 liability_post_lookup_code
1299 FROM ap_system_parameters_all
1300 WHERE org_id = c_org_id;
1301
1302 CURSOR trx_ccid (c_application_id number,
1303 c_entity_code varchar2,
1304 c_event_class_code varchar2,
1305 c_trx_level_type varchar2,
1306 c_trx_id number,
1307 c_event_id number,
1308 c_ae_header_id number) IS
1309 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
1310 ael.code_combination_id
1311 FROM zx_rec_nrec_dist zx_dist,
1312 xla_distribution_links lnk,
1313 xla_ae_lines ael
1314 WHERE zx_dist.trx_id = c_trx_id
1315 AND zx_dist.APPLICATION_ID = c_application_id
1316 AND zx_dist.entity_code = c_entity_code
1317 AND zx_dist.event_class_Code = c_event_class_code
1318 AND zx_dist.trx_level_type = c_trx_level_type
1319 AND lnk.application_id = 200
1320 AND lnk.source_distribution_type = 'AP_INV_DIST'
1321 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
1322 AND lnk.event_id = c_event_id
1323 AND lnk.ae_header_id = c_ae_header_id
1324 AND ael.ae_header_id = lnk.ae_header_id
1325 AND ael.ae_line_num = lnk.ae_line_num
1326 AND ael.application_id = lnk.application_id
1327 AND rownum =1;
1328
1329 CURSOR trx_line_ccid (c_application_id number,
1330 c_entity_code varchar2,
1331 c_event_class_code varchar2,
1332 c_trx_level_type varchar2,
1333 c_trx_id NUMBER,
1334 c_trx_line_id NUMBER,
1335 c_event_id NUMBER,
1336 c_ae_header_id NUMBER) IS
1337 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
1338 ael.code_combination_id
1339 FROM zx_rec_nrec_dist zx_dist,
1340 xla_distribution_links lnk,
1341 xla_ae_lines ael
1342 WHERE zx_dist.trx_id = c_trx_id
1343 AND zx_dist.APPLICATION_ID = c_application_id
1344 AND zx_dist.entity_code = c_entity_code
1345 AND zx_dist.event_class_Code = c_event_class_code
1346 AND zx_dist.trx_level_type = c_trx_level_type
1347 AND zx_dist.trx_line_id = c_trx_line_id
1348 AND lnk.application_id = 200
1349 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1350 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
1351 AND lnk.event_id = c_event_id
1352 AND lnk.ae_header_id = c_ae_header_id
1353 AND ael.ae_header_id = lnk.ae_header_id
1354 AND ael.ae_line_num = lnk.ae_line_num
1355 AND ael.application_id = lnk.application_id
1356 AND rownum =1;
1357
1358 -- For transavtion distribution level code combination id select in the build SQL
1359 -- The following query can be removed ----
1360
1361 CURSOR trx_dist_ccid (c_application_id number,
1362 c_entity_code varchar2,
1363 c_event_class_code varchar2,
1364 c_trx_level_type varchar2,
1365 c_trx_id NUMBER,
1366 c_trx_line_id NUMBER,
1367 c_tax_line_dist_id NUMBER,
1368 c_event_id NUMBER,
1369 c_ae_header_id NUMBER) IS
1370 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
1371 ael.code_combination_id
1372 FROM zx_rec_nrec_dist zx_dist,
1373 xla_distribution_links lnk,
1374 xla_ae_lines ael
1375 WHERE zx_dist.trx_id = c_trx_id
1376 AND zx_dist.APPLICATION_ID = c_application_id
1377 AND zx_dist.entity_code = c_entity_code
1378 AND zx_dist.event_class_Code = c_event_class_code
1379 AND zx_dist.trx_level_type = c_trx_level_type
1380 AND zx_dist.trx_line_id = c_trx_line_id
1381 AND zx_dist.rec_nrec_tax_dist_id = c_tax_line_dist_id
1382 AND lnk.application_id = 200
1383 AND lnk.source_distribution_type = 'AP_INV_DIST'
1384 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
1385 AND lnk.event_id = c_event_id
1386 AND lnk.ae_header_id = c_ae_header_id
1387 AND ael.ae_header_id = lnk.ae_header_id
1388 AND ael.ae_line_num = lnk.ae_line_num
1389 AND ael.application_id = lnk.application_id
1390 AND ael.accounting_class_code <> 'LIABILITY'
1391 AND rownum =1;
1392
1393 CURSOR trx_dist_ccid_tax_event (c_application_id number,
1394 c_entity_code varchar2,
1395 c_event_class_code varchar2,
1396 c_trx_level_type varchar2,
1397 c_trx_id NUMBER,
1398 c_trx_line_id NUMBER,
1399 c_tax_line_dist_id NUMBER,
1400 c_event_id NUMBER,
1401 c_ae_header_id NUMBER,
1402 c_ledger_id NUMBER) IS
1403 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) index(lnk XLA_DISTRIBUTION_LINKS_N1) */
1404 ael.code_combination_id
1405 FROM zx_rec_nrec_dist zx_dist,
1406 xla_distribution_links lnk,
1407 xla_ae_lines ael
1408 WHERE zx_dist.trx_id = c_trx_id
1409 AND zx_dist.APPLICATION_ID = c_application_id
1410 AND zx_dist.entity_code = c_entity_code
1411 AND zx_dist.event_class_Code = c_event_class_code
1412 AND zx_dist.trx_level_type = c_trx_level_type
1413 AND zx_dist.trx_line_id = c_trx_line_id
1414 AND zx_dist.rec_nrec_tax_dist_id = c_tax_line_dist_id
1415 AND lnk.application_id = 200
1416 AND lnk.source_distribution_type = 'AP_INV_DIST'
1417 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
1418 AND ael.ae_header_id = lnk.ae_header_id
1419 AND ael.ae_line_num = lnk.ae_line_num
1420 AND ael.application_id = lnk.application_id
1421 AND ael.ledger_id = c_ledger_id
1422 AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY','PURCHASE_ORDER','QV')
1423 AND rownum =1;
1424
1425 CURSOR tax_ccid (c_application_id number,
1426 c_entity_code varchar2,
1427 c_event_class_code varchar2,
1428 c_trx_level_type varchar2,
1429 c_trx_id number,
1430 c_event_id number,
1431 c_ae_header_id number) IS
1432 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) */
1433 ael.code_combination_id
1434 FROM zx_rec_nrec_dist zx_dist,
1435 xla_distribution_links lnk,
1436 xla_ae_lines ael
1437 WHERE zx_dist.trx_id = c_trx_id
1438 AND zx_dist.APPLICATION_ID = c_application_id
1439 AND zx_dist.entity_code = c_entity_code
1440 AND zx_dist.event_class_Code = c_event_class_code
1441 AND zx_dist.trx_level_type = c_trx_level_type
1442 AND lnk.application_id = 200
1443 AND lnk.source_distribution_type = 'AP_INV_DIST'
1444 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
1445 AND lnk.event_id = c_event_id
1446 AND lnk.ae_header_id = c_ae_header_id
1447 AND ael.ae_header_id = lnk.ae_header_id
1448 AND ael.ae_line_num = lnk.ae_line_num
1449 AND ael.application_id = lnk.application_id
1450 AND rownum =1;
1451
1452
1453 CURSOR tax_line_ccid (c_application_id number,
1454 c_entity_code varchar2,
1455 c_event_class_code varchar2,
1456 c_trx_level_type varchar2,
1457 c_trx_id number,
1458 c_tax_line_id NUMBER,
1459 c_event_id number,
1460 c_ae_header_id number) IS
1461 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) */
1462 ael.code_combination_id
1463 FROM zx_rec_nrec_dist zx_dist,
1464 xla_distribution_links lnk,
1465 xla_ae_lines ael
1466 WHERE zx_dist.trx_id = c_trx_id
1467 AND zx_dist.APPLICATION_ID = c_application_id
1468 AND zx_dist.entity_code = c_entity_code
1469 AND zx_dist.event_class_Code = c_event_class_code
1470 AND zx_dist.trx_level_type = c_trx_level_type
1471 AND zx_dist.tax_line_id = c_tax_line_id
1472 AND lnk.application_id = 200
1473 AND lnk.source_distribution_type = 'AP_INVOICE_DISTRIBUTIONS'
1474 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
1475 AND lnk.event_id = c_event_id
1476 AND lnk.ae_header_id = c_ae_header_id
1477 AND ael.ae_header_id = lnk.ae_header_id
1478 AND ael.ae_line_num = lnk.ae_line_num
1479 AND ael.application_id = lnk.application_id
1480 AND rownum =1;
1481
1482 -- For transaction distribution level code combination id select in the build SQL
1483 -- The following query can be removed ----
1484
1485 CURSOR tax_dist_ccid (c_application_id number,
1486 c_entity_code varchar2,
1487 c_event_class_code varchar2,
1488 c_trx_level_type varchar2,
1489 c_trx_id NUMBER,
1490 c_tax_line_id NUMBER,
1491 c_tax_line_dist_id NUMBER,
1492 c_event_id number,
1493 c_ae_header_id number) IS
1494 SELECT /*+ FIRST_ROWS(1) leading(zx_dist,lnk) */
1495 ael.code_combination_id
1496 FROM zx_rec_nrec_dist zx_dist,
1497 xla_distribution_links lnk,
1498 xla_ae_lines ael
1499 WHERE zx_dist.trx_id = c_trx_id
1500 AND zx_dist.APPLICATION_ID = c_application_id
1501 AND zx_dist.entity_code = c_entity_code
1502 AND zx_dist.event_class_Code = c_event_class_code
1503 AND zx_dist.trx_level_type = c_trx_level_type
1504 AND zx_dist.tax_line_id = c_tax_line_id
1505 AND zx_dist.REC_NREC_TAX_DIST_ID = c_tax_line_dist_id
1506 AND lnk.application_id = 200
1507 AND lnk.source_distribution_type = 'AP_INV_DIST'
1508 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
1509 AND lnk.event_id = c_event_id
1510 AND lnk.ae_header_id = c_ae_header_id
1511 AND ael.ae_header_id = lnk.ae_header_id
1512 AND ael.ae_line_num = lnk.ae_line_num
1513 AND ael.application_id = lnk.application_id
1514 AND ael.accounting_class_code <> 'LIABILITY'
1515 AND rownum =1;
1516
1517
1518 l_disc_is_inv_less_tax_flag VARCHAR2(1);
1519 l_disc_distribution_method VARCHAR2(30);
1520 l_liability_post_lookup_code VARCHAR2(30);
1521
1522 L_BAL_SEG_VAL VARCHAR2(240);
1523 L_ACCT_SEG_VAL VARCHAR2(240);
1524 L_SQL_STATEMENT1 VARCHAR2(1000);
1525 -- L_SQL_STATEMENT2 VARCHAR2(1000);
1526 l_ccid number;
1527 l_tax_dist_ccid number;
1528 l_trx_dist_ccid number;
1529
1530 l_balancing_seg_val varchar2(100);
1531 l_natural_acct_val varchar2(100);
1532 l_tbl_index_ccid BINARY_INTEGER;
1533
1534 BEGIN
1535
1536 IF (g_level_procedure >= g_current_runtime_level ) THEN
1537 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info.BEGIN',
1538 'get_accounting_info(+)');
1539 END IF;
1540
1541 GT_TRX_ARAP_BALANCING_SEGMENT(j) := NULL;
1542 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := NULL;
1543 GT_TRX_TAXABLE_BAL_SEG(j) := NULL;
1544 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := NULL;
1545 GT_TRX_TAX_BALANCING_SEGMENT(j) := NULL;
1546 GT_TRX_TAX_NATURAL_ACCOUNT(j) := NULL;
1547 GT_ACCOUNT_FLEXFIELD(j) := NULL;
1548 GT_ACCOUNT_DESCRIPTION(j) := NULL;
1549
1550 GT_TRX_CONTROL_ACCFLEXFIELD(j) := NULL ;
1551 GT_TRX_TAXABLE_ACCOUNT_DESC(j) := NULL ;
1552 GT_TRX_TAXABLE_BALSEG_DESC(j) := NULL ;
1553 GT_TRX_TAXABLE_NATACCT_DESC(j) := NULL ;
1554
1555
1556 L_BAL_SEG_VAL := '';
1557 L_ACCT_SEG_VAL := '';
1558
1559 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||','||P_ACCOUNTING_SEGMENT ||
1560 ' FROM GL_CODE_COMBINATIONS '||
1561 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1562
1563 OPEN get_system_info_cur(p_org_id);
1564 FETCH get_system_info_cur
1565 INTO l_disc_distribution_method,
1566 l_disc_is_inv_less_tax_flag,
1567 l_liability_post_lookup_code;
1568 CLOSE get_system_info_cur;
1569
1570 IF (g_level_procedure >= g_current_runtime_level ) THEN
1571 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_info.BEGIN',
1572 'l_disc_distribution_method : '||l_disc_distribution_method);
1573 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_info.BEGIN',
1574 'l_disc_is_inv_less_tax_flag : '||l_disc_is_inv_less_tax_flag);
1575 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_POPULATE_PKG.get_accounting_info.BEGIN',
1576 'l_liability_post_lookup_code : '||l_liability_post_lookup_code);
1577 END IF;
1578
1579 IF NVL(l_disc_is_inv_less_tax_flag, 'N') = 'N' AND
1580 NVL(l_disc_distribution_method, 'SYSTEM') <> 'SYSTEM' THEN
1581
1582 IF P_INCLUDE_DISCOUNTS = 'Y' THEN
1583 get_discount_info(j,
1584 P_TRX_ID,
1585 P_TAX_LINE_ID,
1586 P_SUMMARY_LEVEL,
1587 P_TAX_DIST_ID,
1588 P_TRX_LINE_ID,
1589 P_TRX_LINE_DIST_ID,
1590 P_TAX_REC_FLAG,
1591 p_tax_regime_code,
1592 p_tax,
1593 p_tax_status_code,
1594 p_tax_rate_id,
1595 P_LEDGER_ID,
1596 l_disc_distribution_method,
1597 l_liability_post_lookup_code);
1598 END IF;
1599 END IF;
1600
1601
1602 IF (g_level_procedure >= g_current_runtime_level ) THEN
1603 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1604 'P_SUMMARY_LEVEL'||P_SUMMARY_LEVEL);
1605 END IF;
1606
1607 IF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
1608 OPEN trx_ccid (p_application_id,
1609 p_entity_code,
1610 p_event_class_code,
1611 p_trx_level_type,
1612 p_trx_id,
1613 p_event_id,
1614 p_ae_header_id);
1615 LOOP
1616 FETCH trx_ccid INTO l_ccid;
1617
1618 IF (g_level_procedure >= g_current_runtime_level ) THEN
1619 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1620 'TRANSACTION LEVEL : p_trx_id - p_event_id - p_ae_header_id'||to_char(p_trx_id)
1621 ||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)||'-'||to_char(l_ccid));
1622 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1623 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT1);
1624 END IF;
1625
1626
1627 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL, L_ACCT_SEG_VAL
1628 USING l_ccid;
1629
1630 IF (g_level_procedure >= g_current_runtime_level ) THEN
1631 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1632 'TRANSACTION LEVEL : p_trx_id - p_event_id - p_ae_header_id'||to_char(p_trx_id)
1633 ||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)||'-'||to_char(l_ccid));
1634 END IF;
1635
1636 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
1637 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
1638 ELSE
1639 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
1640 NULL;
1641 ELSE
1642 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
1643 ||','||L_BAL_SEG_VAL;
1644 END IF;
1645 END IF;
1646
1647
1648 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
1649 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1650 ELSE
1651 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1652 NULL;
1653 ELSE
1654 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
1655 ||','||L_ACCT_SEG_VAL;
1656 END IF;
1657 END IF;
1658
1659 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
1660 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
1661 EXIT WHEN trx_ccid%NOTFOUND;
1662 END LOOP;
1663
1664
1665 OPEN tax_ccid (p_application_id,
1666 p_entity_code,
1667 p_event_class_code,
1668 p_trx_level_type,
1669 p_trx_id,
1670 p_event_id,
1671 p_ae_header_id);
1672 LOOP
1673 FETCH tax_ccid INTO l_ccid;
1674 EXIT WHEN tax_ccid%NOTFOUND;
1675
1676 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL, L_ACCT_SEG_VAL
1677 USING l_ccid;
1678
1679
1680 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
1681 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
1682 ELSE
1683 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
1684 NULL;
1685 ELSE
1686 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
1687 ||','||L_BAL_SEG_VAL;
1688 END IF;
1689 END IF;
1690
1691
1692 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
1693 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1694 ELSE
1695 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1696 NULL;
1697 ELSE
1698 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
1699 ||','||L_ACCT_SEG_VAL;
1700 END IF;
1701 END IF;
1702
1703 END LOOP;
1704
1705 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1706 OPEN trx_line_ccid (p_application_id,
1707 p_entity_code,
1708 p_event_class_code,
1709 p_trx_level_type,
1710 p_trx_id,
1711 p_trx_line_id,
1712 p_event_id,
1713 p_ae_header_id);
1714 LOOP
1715 FETCH trx_line_ccid INTO l_ccid;
1716 EXIT WHEN trx_line_ccid%NOTFOUND;
1717
1718 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL, L_ACCT_SEG_VAL
1719 USING l_ccid;
1720
1721
1722 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
1723 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
1724 ELSE
1725 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
1726 NULL;
1727 ELSE
1728 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
1729 ||','||L_BAL_SEG_VAL;
1730 END IF;
1731 END IF;
1732
1733
1734 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
1735 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1736 ELSE
1737 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1738 NULL;
1739 ELSE
1740 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
1741 ||','||L_ACCT_SEG_VAL;
1742 END IF;
1743 END IF;
1744
1745 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
1746 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
1747 END LOOP;
1748
1749
1750 OPEN tax_line_ccid (p_application_id,
1751 p_entity_code,
1752 p_event_class_code,
1753 p_trx_level_type,
1754 p_trx_id,
1755 p_tax_line_id,
1756 p_event_id,
1757 p_ae_header_id);
1758 LOOP
1759 FETCH tax_line_ccid INTO l_ccid;
1760 EXIT WHEN tax_line_ccid%NOTFOUND;
1761
1762 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL, L_ACCT_SEG_VAL
1763 USING l_ccid;
1764
1765
1766 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
1767 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
1768 ELSE
1769 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
1770 NULL;
1771 ELSE
1772 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
1773 ||','||L_BAL_SEG_VAL;
1774 END IF;
1775 END IF;
1776
1777 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
1778 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1779 ELSE
1780 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1781 NULL;
1782 ELSE
1783 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
1784 ||','||L_ACCT_SEG_VAL;
1785 END IF;
1786 END IF;
1787
1788 END LOOP;
1789
1790 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1791 IF (g_level_procedure >= g_current_runtime_level ) THEN
1792 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1793 'TRANSACTION_DISTRIBUTION LEVEL');
1794 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1795 'trx_dist_ccid cursor :');
1796 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1797 'P_TRX_ID :'||to_char(P_TRX_ID));
1798 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1799 'P_TRX_LINE_ID :'||to_char(P_TRX_LINE_ID));
1800 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1801 'P_TAX_LINE_ID :'||to_char(P_TAX_LINE_ID));
1802 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1803 'P_EVENT_ID :'||to_char(P_EVENT_ID));
1804 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1805 'P_AE_HEADER_ID :'||to_char(P_AE_HEADER_ID));
1806 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1807 'P_TAX_DIST_ID :'||to_char(P_TAX_DIST_ID));
1808 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1809 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT1);
1810
1811 END IF;
1812 OPEN trx_dist_ccid (
1813 p_application_id,
1814 p_entity_code,
1815 p_event_class_code,
1816 p_trx_level_type,
1817 p_trx_id,
1818 p_trx_line_id,
1819 p_tax_dist_id,
1820 p_event_id,
1821 p_ae_header_id);
1822
1823 FETCH trx_dist_ccid INTO l_ccid;
1824
1825 IF trx_dist_ccid%NOTFOUND OR l_ccid IS NULL THEN
1826 IF (g_level_procedure >= g_current_runtime_level ) THEN
1827 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1828 'Cursor trx_dist_ccid Not Found, So open cursor trx_dist_ccid_tax_event');
1829 END IF;
1830
1831 OPEN trx_dist_ccid_tax_event (
1832 p_application_id,
1833 p_entity_code,
1834 p_event_class_code,
1835 p_trx_level_type,
1836 p_trx_id,
1837 p_trx_line_id,
1838 p_tax_dist_id,
1839 p_event_id,
1840 p_ae_header_id,
1841 p_ledger_id);
1842
1843 FETCH trx_dist_ccid_tax_event INTO l_ccid;
1844 CLOSE trx_dist_ccid_tax_event;
1845 END IF;
1846
1847 CLOSE trx_dist_ccid;
1848
1849 l_trx_dist_ccid := l_ccid ; --Bug 5510907
1850
1851 IF (g_level_procedure >= g_current_runtime_level ) THEN
1852 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1853 'l_trx_dist_ccid :'||l_trx_dist_ccid);
1854 END IF;
1855
1856 IF l_trx_dist_ccid IS NOT NULL THEN
1857 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL, L_ACCT_SEG_VAL
1858 USING l_ccid;
1859 END IF;
1860
1861 IF (g_level_procedure >= g_current_runtime_level ) THEN
1862 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1863 'L_BAL_SEG_VAL :'||L_BAL_SEG_VAL);
1864 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1865 'L_ACCT_SEG_VAL :'||L_ACCT_SEG_VAL);
1866 END IF;
1867 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
1868 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
1869 ELSE
1870 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
1871 NULL;
1872 ELSE
1873 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
1874 ||','||L_BAL_SEG_VAL;
1875 END IF;
1876 END IF;
1877
1878
1879 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
1880 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1881 ELSE
1882 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1883 NULL;
1884 ELSE
1885 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
1886 ||','||L_ACCT_SEG_VAL;
1887 END IF;
1888 END IF;
1889
1890 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
1891 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
1892 -- END LOOP;
1893
1894 IF (g_level_procedure >= g_current_runtime_level ) THEN
1895 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info.BEGIN',
1896 'tax_dist_ccid cursor :');
1897 END IF;
1898
1899 OPEN tax_dist_ccid (p_application_id,
1900 p_entity_code,
1901 p_event_class_code,
1902 p_trx_level_type,
1903 p_trx_id,
1904 p_tax_line_id,
1905 p_tax_dist_id,
1906 p_event_id,
1907 p_ae_header_id);
1908 LOOP
1909 FETCH tax_dist_ccid INTO l_ccid;
1910 IF (g_level_procedure >= g_current_runtime_level ) THEN
1911 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1912 'TRANSACTION_DISTRIBUTION LEVEL');
1913 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1914 'tax_dist_ccid cursor :');
1915 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1916 'P_TRX_ID :'||to_char(P_TRX_ID));
1917 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1918 'P_TaX_LINE_ID :'||to_char(P_TAX_LINE_ID));
1919 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1920 'P_TAX_dist_ID :'||to_char(P_TAX_DIST_ID));
1921 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1922 'P_EVENT_ID :'||to_char(P_EVENT_ID));
1923 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1924 'P_AE_HEADER_ID :'||to_char(P_AE_HEADER_ID));
1925 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1926 'l_ccid :'||to_char(l_ccid));
1927 END IF;
1928
1929 EXIT WHEN tax_dist_ccid%NOTFOUND;
1930
1931 l_tax_dist_ccid := l_ccid;
1932 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL, L_ACCT_SEG_VAL
1933 USING l_ccid;
1934
1935
1936 IF (g_level_procedure >= g_current_runtime_level ) THEN
1937 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1938 'L_BAL_SEG_VAL :'||L_BAL_SEG_VAL);
1939 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info',
1940 'L_ACCT_SEG_VAL :'||L_ACCT_SEG_VAL);
1941 END IF;
1942
1943 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
1944 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
1945 ELSE
1946 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
1947 NULL;
1948 ELSE
1949 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
1950 ||','||L_BAL_SEG_VAL;
1951 END IF;
1952 END IF;
1953
1954
1955 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
1956 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1957 ELSE
1958 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1959 NULL;
1960 ELSE
1961 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
1962 ||','||L_ACCT_SEG_VAL;
1963 END IF;
1964 END IF;
1965
1966 END LOOP;
1967
1968 -- populare accounting_flexfield and accounting_description column ---
1969 ----------------------------------------------------------------------
1970 IF l_tax_dist_ccid IS NOT NULL THEN
1971
1972 l_tbl_index_ccid := dbms_utility.get_hash_value('tax'||LPAD(TO_CHAR(l_tax_dist_ccid),15,'0'), 1,8192);
1973 IF ZX_EXTRACT_PKG.g_ccid_val_desc_tbl.EXISTS(l_tbl_index_ccid) AND
1974 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).CCID_KEY = 'tax'||TO_CHAR(l_tax_dist_ccid) THEN
1975
1976 GT_ACCOUNT_FLEXFIELD(j) := ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_VAL;
1977 GT_ACCOUNT_DESCRIPTION(j) := ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_DESC;
1978
1979 IF (g_level_procedure >= g_current_runtime_level ) THEN
1980 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
1981 'From Cache :: Hash = '||l_tbl_index_ccid);
1982 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
1983 'From Cache :: Account Flexfield = '||GT_ACCOUNT_FLEXFIELD(j));
1984 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
1985 'From Cache :: Account Description = '||GT_ACCOUNT_DESCRIPTION(j));
1986 END IF;
1987 ELSE
1988
1989 GT_ACCOUNT_FLEXFIELD(j) := FA_RX_FLEX_PKG.GET_VALUE(
1990 P_APPLICATION_ID => 101,
1991 P_ID_FLEX_CODE => 'GL#',
1992 P_ID_FLEX_NUM => g_chart_of_accounts_id,
1993 P_QUALIFIER => 'ALL',
1994 P_CCID => l_tax_dist_ccid);
1995
1996 GT_ACCOUNT_DESCRIPTION(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
1997 P_APPLICATION_ID => 101,
1998 P_ID_FLEX_CODE => 'GL#',
1999 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2000 P_QUALIFIER => 'ALL',
2001 P_DATA => GT_ACCOUNT_FLEXFIELD(j));
2002
2003 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).CCID_KEY := 'tax'||TO_CHAR(l_tax_dist_ccid);
2004 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_VAL := GT_ACCOUNT_FLEXFIELD(j);
2005 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_DESC := GT_ACCOUNT_DESCRIPTION(j);
2006 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).BALSEG_DESC := NULL;
2007 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).NATACCT_DESC := NULL;
2008 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).BALSEG_VAL := NULL;
2009 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).NATACCT_VAL := NULL;
2010
2011 IF (g_level_procedure >= g_current_runtime_level ) THEN
2012 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2013 'From FA_RX_FLEX_PKG :: Hash = '||l_tbl_index_ccid);
2014 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2015 'From FA_RX_FLEX_PKG :: Account Flexfield = '||GT_ACCOUNT_FLEXFIELD(j));
2016 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2017 'From FA_RX_FLEX_PKG :: Account Description = '||GT_ACCOUNT_DESCRIPTION(j));
2018 END IF;
2019 END IF; --Cache Exists Check
2020 END IF; --TAX CCID Null Check
2021
2022 IF l_trx_dist_ccid IS NOT NULL THEN
2023
2024 l_tbl_index_ccid := dbms_utility.get_hash_value('TRX'||LPAD(TO_CHAR(l_trx_dist_ccid),15,'0'), 1,8192);
2025 IF ZX_EXTRACT_PKG.g_ccid_val_desc_tbl.EXISTS(l_tbl_index_ccid) AND
2026 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).CCID_KEY = 'TRX'||TO_CHAR(l_trx_dist_ccid) THEN
2027
2028 GT_TRX_CONTROL_ACCFLEXFIELD(j) := ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_VAL;
2029 GT_TRX_TAXABLE_ACCOUNT_DESC(j) := ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_DESC;
2030 GT_TRX_TAXABLE_BALSEG_DESC(j) := ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).BALSEG_DESC;
2031 GT_TRX_TAXABLE_NATACCT_DESC(j) := ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).NATACCT_DESC;
2032
2033 IF (g_level_procedure >= g_current_runtime_level ) THEN
2034 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2035 'From Cache :: Hash = '||l_tbl_index_ccid);
2036 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2037 'From Cache :: Account Flexfield = '||GT_TRX_CONTROL_ACCFLEXFIELD(j));
2038 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2039 'From Cache :: Account Description = '||GT_TRX_TAXABLE_ACCOUNT_DESC(j));
2040 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2041 'From Cache :: Balancing Seg Description = '||GT_TRX_TAXABLE_BALSEG_DESC(j));
2042 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2043 'From Cache :: Natural Seg Description = '||GT_TRX_TAXABLE_NATACCT_DESC(j));
2044 END IF;
2045 ELSE
2046
2047 GT_TRX_CONTROL_ACCFLEXFIELD(j) := FA_RX_FLEX_PKG.GET_VALUE(
2048 P_APPLICATION_ID => 101,
2049 P_ID_FLEX_CODE => 'GL#',
2050 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2051 P_QUALIFIER => 'ALL',
2052 P_CCID => l_trx_dist_ccid);
2053
2054
2055 GT_TRX_TAXABLE_ACCOUNT_DESC(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2056 P_APPLICATION_ID => 101,
2057 P_ID_FLEX_CODE => 'GL#',
2058 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2059 P_QUALIFIER => 'ALL',
2060 P_DATA => GT_TRX_CONTROL_ACCFLEXFIELD(j));
2061
2062
2063 l_balancing_seg_val := FA_RX_FLEX_PKG.GET_VALUE(
2064 P_APPLICATION_ID => 101,
2065 P_ID_FLEX_CODE => 'GL#',
2066 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2067 P_QUALIFIER => 'GL_BALANCING',
2068 P_CCID => l_trx_dist_ccid);
2069
2070
2071 IF ( l_balancing_seg_val IS NOT NULL ) THEN
2072 GT_TRX_TAXABLE_BALSEG_DESC(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2073 P_APPLICATION_ID => 101,
2074 P_ID_FLEX_CODE => 'GL#',
2075 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2076 P_QUALIFIER => 'GL_BALANCING',
2077 P_DATA => l_balancing_seg_val);
2078
2079 END IF ;
2080
2081 --Populate the Natural Account desccription after fetching its value
2082 l_natural_acct_val := FA_RX_FLEX_PKG.GET_VALUE(
2083 P_APPLICATION_ID => 101,
2084 P_ID_FLEX_CODE => 'GL#',
2085 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2086 P_QUALIFIER => 'GL_ACCOUNT',
2087 P_CCID => l_trx_dist_ccid);
2088
2089 IF ( l_balancing_seg_val IS NOT NULL ) THEN
2090 GT_TRX_TAXABLE_NATACCT_DESC(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2091 P_APPLICATION_ID => 101,
2092 P_ID_FLEX_CODE => 'GL#',
2093 P_ID_FLEX_NUM => g_chart_of_accounts_id,
2094 P_QUALIFIER => 'GL_ACCOUNT',
2095 P_DATA => l_natural_acct_val);
2096 END IF ;
2097
2098 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).CCID_KEY := 'TRX'||TO_CHAR(l_trx_dist_ccid);
2099 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_VAL := GT_TRX_CONTROL_ACCFLEXFIELD(j);
2100 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).FLEXFIELD_DESC := GT_TRX_TAXABLE_ACCOUNT_DESC(j);
2101 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).BALSEG_DESC := GT_TRX_TAXABLE_BALSEG_DESC(j);
2102 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).NATACCT_DESC := GT_TRX_TAXABLE_NATACCT_DESC(j);
2103 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).BALSEG_VAL := NULL;
2104 ZX_EXTRACT_PKG.g_ccid_val_desc_tbl(l_tbl_index_ccid).NATACCT_VAL := NULL;
2105
2106 IF (g_level_procedure >= g_current_runtime_level ) THEN
2107 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2108 'From FA_RX_FLEX_PKG :: Hash = '||l_tbl_index_ccid);
2109 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2110 'From FA_RX_FLEX_PKG :: Account Description for Taxable Line CCID = '||GT_TRX_TAXABLE_ACCOUNT_DESC(j));
2111 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2112 'From FA_RX_FLEX_PKG :: Taxable Line Account Flexfield = '||GT_TRX_CONTROL_ACCFLEXFIELD(j));
2113 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2114 'From FA_RX_FLEX_PKG :: l_balancing_seg_val for TaxableLine = '||l_balancing_seg_val);
2115 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2116 'From FA_RX_FLEX_PKG :: Balacing Seg Description for Taxable Line CCID = '||GT_TRX_TAXABLE_BALSEG_DESC(j));
2117 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2118 'From FA_RX_FLEX_PKG :: l_natural_acct_val for TaxableLine = '||l_natural_acct_val);
2119 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2120 'From FA_RX_FLEX_PKG :: Natural Seg Description for Taxable Line CCID = '||GT_TRX_TAXABLE_NATACCT_DESC(j));
2121 END IF;
2122 END IF; -- Cache Exists Check
2123 END IF ; -- TRX CCID Null Check
2124
2125 ---- End of accounting flexfield population -----------------------
2126 END IF; -- Summary Level
2127
2128 IF (g_level_procedure >= g_current_runtime_level ) THEN
2129 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_info.END',
2130 'get_accounting_info(-)');
2131 END IF;
2132
2133 END get_accounting_info;
2134
2135
2136
2137 PROCEDURE get_accounting_amounts (
2138 P_APPLICATION_ID IN NUMBER,
2139 P_ENTITY_CODE IN VARCHAR2,
2140 P_EVENT_CLASS_CODE IN VARCHAR2,
2141 P_TRX_LEVEL_TYPE IN VARCHAR2,
2142 P_TRX_ID IN NUMBER,
2143 P_TRX_LINE_ID IN NUMBER,
2144 P_TAX_LINE_ID IN NUMBER,
2145 P_EVENT_ID IN NUMBER,
2146 P_AE_HEADER_ID IN NUMBER,
2147 P_TAX_DIST_ID IN NUMBER,
2148 P_SUMMARY_LEVEL IN VARCHAR2,
2149 P_REPORT_NAME IN VARCHAR2,
2150 P_LEDGER_ID IN NUMBER,
2151 j IN binary_integer,
2152 p_ae_line_num IN NUMBER,
2153 P_TRX_LINE_DIST_ID IN NUMBER) IS
2154 -- Transaction Header Level
2155
2156 CURSOR taxable_amount_hdr (c_application_id number,
2157 c_entity_code varchar2,
2158 c_event_class_code varchar2,
2159 c_trx_level_type varchar2,
2160 c_trx_id NUMBER,
2161 c_ae_header_id NUMBER,
2162 c_event_id NUMBER,
2163 c_ledger_id NUMBER) IS
2164 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2165 FROM zx_rec_nrec_dist zx_dist,
2166 xla_distribution_links lnk,
2167 xla_ae_headers aeh,
2168 xla_ae_lines ael
2169 WHERE zx_dist.trx_id = c_trx_id
2170 AND zx_dist.APPLICATION_ID = c_application_id
2171 AND zx_dist.entity_code = c_entity_code
2172 AND zx_dist.event_class_Code = c_event_class_code
2173 AND zx_dist.trx_level_type = c_trx_level_type
2174 AND lnk.application_id = 200
2175 AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
2176 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
2177 AND lnk.ae_header_id = c_ae_header_id
2178 AND lnk.event_id = c_event_id
2179 AND lnk.ae_line_num = ael.ae_line_num
2180 AND lnk.ae_header_id = ael.ae_header_id
2181 AND aeh.ae_header_id = ael.ae_header_id
2182 AND aeh.ledger_id = c_ledger_id
2183 AND aeh.ae_header_id = lnk.ae_header_id
2184 AND aeh.application_id = lnk.application_id
2185 AND ael.application_id = aeh.application_id;
2186
2187
2188 CURSOR tax_amount_hdr (c_application_id number,
2189 c_entity_code varchar2,
2190 c_event_class_code varchar2,
2191 c_trx_level_type varchar2,
2192 c_trx_id NUMBER,
2193 c_ae_header_id NUMBER,
2194 c_event_id NUMBER,
2195 c_ledger_id NUMBER) IS
2196 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2197 FROM zx_rec_nrec_dist zx_dist,
2198 xla_distribution_links lnk,
2199 xla_ae_headers aeh,
2200 xla_ae_lines ael
2201 WHERE zx_dist.trx_id = c_trx_id
2202 AND zx_dist.APPLICATION_ID = c_application_id
2203 AND zx_dist.entity_code = c_entity_code
2204 AND zx_dist.event_class_Code = c_event_class_code
2205 AND zx_dist.trx_level_type = c_trx_level_type
2206 AND lnk.application_id = 200
2207 AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
2208 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
2209 AND lnk.ae_header_id = c_ae_header_id
2210 AND lnk.event_id = c_event_id
2211 AND lnk.ae_line_num = ael.ae_line_num
2212 AND aeh.ae_header_id = ael.ae_header_id
2213 AND aeh.ledger_id = c_ledger_id
2214 AND aeh.ae_header_id = lnk.ae_header_id
2215 AND aeh.application_id = lnk.application_id
2216 AND ael.application_id = aeh.application_id;
2217
2218
2219 -- Transaction Line Level
2220
2221 CURSOR taxable_amount_line (c_application_id number,
2222 c_entity_code varchar2,
2223 c_event_class_code varchar2,
2224 c_trx_level_type varchar2,
2225 c_trx_id NUMBER,
2226 c_trx_line_id NUMBER,
2227 c_ae_header_id NUMBER,
2228 c_event_id NUMBER,
2229 c_ledger_id NUMBER) IS
2230 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2231 FROM zx_rec_nrec_dist zx_dist,
2232 xla_distribution_links lnk,
2233 xla_ae_headers aeh,
2234 xla_ae_lines ael
2235 WHERE zx_dist.trx_id = c_trx_id
2236 AND zx_dist.APPLICATION_ID = c_application_id
2237 AND zx_dist.entity_code = c_entity_code
2238 AND zx_dist.event_class_Code = c_event_class_code
2239 AND zx_dist.trx_level_type = c_trx_level_type
2240 AND zx_dist.trx_line_id = c_trx_line_id
2241 AND lnk.application_id = 200
2242 AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
2243 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
2244 AND lnk.ae_header_id = c_ae_header_id
2245 AND lnk.event_id = c_event_id
2246 AND lnk.ae_line_num = ael.ae_line_num
2247 AND aeh.ae_header_id = ael.ae_header_id
2248 AND aeh.ledger_id = c_ledger_id
2249 AND aeh.ae_header_id = lnk.ae_header_id
2250 AND aeh.application_id = lnk.application_id
2251 AND ael.application_id = aeh.application_id;
2252
2253
2254 CURSOR tax_amount_line (c_application_id number,
2255 c_entity_code varchar2,
2256 c_event_class_code varchar2,
2257 c_trx_level_type varchar2,
2258 c_trx_id NUMBER,
2259 c_tax_line_id NUMBER,
2260 c_ae_header_id NUMBER,
2261 c_event_id NUMBER,
2262 c_ledger_id NUMBER) IS
2263 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2264 FROM zx_rec_nrec_dist zx_dist,
2265 xla_distribution_links lnk,
2266 xla_ae_headers aeh,
2267 xla_ae_lines ael
2268 WHERE zx_dist.trx_id = c_trx_id
2269 AND zx_dist.APPLICATION_ID = c_application_id
2270 AND zx_dist.entity_code = c_entity_code
2271 AND zx_dist.event_class_Code = c_event_class_code
2272 AND zx_dist.trx_level_type = c_trx_level_type
2273 AND zx_dist.tax_line_id = c_tax_line_id
2274 AND lnk.application_id = 200
2275 AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
2276 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
2277 AND lnk.ae_header_id = c_ae_header_id
2278 AND lnk.event_id = c_event_id
2279 AND lnk.ae_line_num = ael.ae_line_num
2280 AND aeh.ae_header_id = ael.ae_header_id
2281 AND aeh.ledger_id = c_ledger_id
2282 AND aeh.ae_header_id = lnk.ae_header_id
2283 AND aeh.application_id = lnk.application_id
2284 AND ael.application_id = aeh.application_id;
2285
2286
2287 -- Transaction Distribution Level
2288
2289 CURSOR tax_amount_dist (c_tax_dist_id NUMBER,
2290 c_ae_header_id NUMBER,
2291 c_event_id NUMBER,
2292 c_ae_line_num NUMBER ) IS
2293 SELECT SUM( nvl(lnk.UNROUNDED_ENTERED_DR,0) - (nvl(lnk.UNROUNDED_ENTERED_CR,0) )) ,
2294 SUM( nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0) ))
2295 FROM zx_rec_nrec_dist zx_dist,
2296 xla_distribution_links lnk,
2297 xla_ae_lines ael
2298 WHERE zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
2299 AND lnk.application_id = 200
2300 AND lnk.source_distribution_type in ('AP_INV_DIST','AP_PREPAY')
2301 AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
2302 AND lnk.ae_header_id = c_ae_header_id
2303 AND lnk.event_id = c_event_id
2304 AND lnk.ae_line_num = c_ae_line_num
2305 and ael.application_id = lnk.application_id
2306 and ael.ae_header_id = lnk.ae_header_id
2307 and ael.ae_line_num = lnk.ae_line_num ;
2308
2309 CURSOR taxable_amount_dist (c_ae_header_id NUMBER,
2310 c_event_id NUMBER,
2311 c_tax_dist_id NUMBER ) IS
2312 SELECT SUM(decode(zx_dist.reverse_flag, 'Y',
2313 (abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) -(nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
2314 (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2315 (nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)))),
2316 SUM(decode(zx_dist.reverse_flag, 'Y',
2317 (abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))*
2318 (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2319 (nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))))
2320 FROM zx_rec_nrec_dist zx_dist,
2321 xla_distribution_links lnk,
2322 xla_ae_lines ael
2323 WHERE zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
2324 AND lnk.application_id = 200
2325 AND lnk.source_distribution_type = 'AP_INV_DIST'
2326 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
2327 AND lnk.ae_header_id = c_ae_header_id
2328 AND lnk.event_id = c_event_id
2329 AND ael.application_id = lnk.application_id
2330 AND ael.ae_header_id = lnk.ae_header_id
2331 AND ael.ae_line_num = lnk.ae_line_num
2332 AND ael.accounting_class_code NOT IN ('NRTAX','RTAX','LIABILITY','EXCHANGE_RATE_VARIANCE');
2333
2334 CURSOR taxable_amount_dist_ERV (c_ae_header_id NUMBER,
2335 c_event_id NUMBER,
2336 c_tax_dist_id NUMBER ) IS
2337 SELECT SUM( (nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0))),
2338 SUM( (nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))
2339 FROM zx_rec_nrec_dist zx_dist,
2340 xla_distribution_links lnk,
2341 xla_ae_lines ael
2342 WHERE zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
2343 AND lnk.application_id = 200
2344 AND lnk.source_distribution_type = 'AP_INV_DIST'
2345 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
2346 AND lnk.ae_header_id = c_ae_header_id
2347 AND lnk.event_id = c_event_id
2348 AND ael.application_id = lnk.application_id
2349 AND ael.ae_header_id = lnk.ae_header_id
2350 AND ael.ae_line_num = lnk.ae_line_num
2351 AND ael.accounting_class_code = 'EXCHANGE_RATE_VARIANCE';
2352
2353 CURSOR taxable_amount_dist2 (c_application_id number,
2354 c_entity_code varchar2,
2355 c_event_class_code varchar2,
2356 c_trx_level_type varchar2,
2357 c_trx_id NUMBER,
2358 c_trx_line_id NUMBER,
2359 c_ae_header_id NUMBER,
2360 c_event_id NUMBER,
2361 c_ledger_id NUMBER,
2362 c_tax_dist_id NUMBER
2363 ) IS
2364 SELECT SUM(decode(zx_dist.reverse_flag, 'Y',
2365 (abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) -(nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
2366 (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2367 (nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)))),
2368 SUM(decode(zx_dist.reverse_flag, 'Y',
2369 (abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))*
2370 (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2371 (nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))))
2372 -- SELECT SUM( abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) - (nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
2373 -- (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2374 -- SUM( abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))) *
2375 -- (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT))))
2376 FROM zx_rec_nrec_dist zx_dist,
2377 xla_distribution_links lnk,
2378 xla_ae_headers aeh,
2379 xla_ae_lines ael
2380 WHERE zx_dist.trx_id = c_trx_id
2381 AND zx_dist.APPLICATION_ID = c_application_id
2382 AND zx_dist.entity_code = c_entity_code
2383 AND zx_dist.event_class_Code = c_event_class_code
2384 AND zx_dist.trx_level_type = c_trx_level_type
2385 AND zx_dist.trx_line_id = c_trx_line_id
2386 -- AND zx_dist.trx_line_dist_id = c_trx_line_dist_id
2387 AND lnk.application_id = 200
2388 AND lnk.source_distribution_type = 'AP_INV_DIST' --Bug 5393051
2389 AND lnk.alloc_to_dist_id_num_1 = zx_dist.trx_line_dist_id
2390 AND lnk.alloc_to_dist_id_num_1<>lnk.source_distribution_id_num_1
2391 AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id --Bug 5393051
2392 AND lnk.ae_header_id = c_ae_header_id
2393 AND lnk.event_id = c_event_id
2394 AND lnk.ae_line_num = ael.ae_line_num
2395 AND aeh.ae_header_id = ael.ae_header_id
2396 AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY')
2397 AND aeh.ledger_id = c_ledger_id
2398 AND aeh.ae_header_id = lnk.ae_header_id
2399 AND aeh.application_id = lnk.application_id
2400 AND ael.application_id = aeh.application_id;
2401
2402 CURSOR taxable_amount_dist1
2403 (c_application_id number,
2404 c_entity_code varchar2,
2405 c_event_class_code varchar2,
2406 c_trx_level_type varchar2,
2407 c_trx_id NUMBER,
2408 c_trx_line_id NUMBER,
2409 c_ledger_id NUMBER,
2410 c_tax_dist_id NUMBER,
2411 c_header_id NUMBER
2412 ) IS
2413 SELECT SUM(decode(zx_dist.reverse_flag, 'Y',
2414 (abs(nvl(lnk.UNROUNDED_ENTERED_DR,0) -(nvl(lnk.UNROUNDED_ENTERED_CR,0))) *
2415 (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2416 (nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)))),
2417 SUM(decode(zx_dist.reverse_flag, 'Y',
2418 (abs(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - (nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)))*
2419 (decode(sign(zx_dist.TRX_LINE_DIST_AMT),0,1,sign(zx_dist.TRX_LINE_DIST_AMT)))),
2420 (nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))))
2421 FROM zx_rec_nrec_dist zx_dist,
2422 xla_ae_lines ael,
2423 xla_distribution_links lnk
2424 WHERE zx_dist.trx_id = c_trx_id
2425 AND zx_dist.APPLICATION_ID = c_application_id
2426 AND zx_dist.entity_code = c_entity_code
2427 AND zx_dist.event_class_Code = c_event_class_code
2428 AND zx_dist.trx_level_type = c_trx_level_type
2429 AND zx_dist.trx_line_id = c_trx_line_id
2430 AND lnk.application_id = 200
2431 AND lnk.source_distribution_type = 'AP_INV_DIST'
2432 AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
2433 -- AND( lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id OR
2434 -- lnk.alloc_to_dist_id_num_1 = zx_dist.trx_line_dist_id
2435 -- AND lnk.alloc_to_dist_id_num_1<>lnk.source_distribution_id_num_1 )
2436 AND lnk.ae_line_num = ael.ae_line_num
2437 AND lnk.ae_header_id = ael.ae_header_id
2438 AND lnk.ae_header_id = c_header_id
2439 AND ael.application_id = lnk.application_id
2440 AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
2441 AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY','PURCHASE_ORDER')
2442 AND ael.ledger_id = c_ledger_id
2443 AND ROWNUM = 1;
2444
2445 CURSOR taxable_amount_dist_no_tax
2446 ( c_trx_id NUMBER,
2447 c_trx_line_id NUMBER,
2448 c_ledger_id NUMBER,
2449 c_trx_line_dist_id NUMBER
2450 ) IS
2451 SELECT SUM(nvl(lnk.UNROUNDED_ENTERED_DR,0) - nvl(lnk.UNROUNDED_ENTERED_CR,0)),
2452 SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0) - nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))
2453 FROM ap_invoice_distributions_all ap_dist,
2454 xla_distribution_links lnk,
2455 xla_ae_headers aeh,
2456 xla_ae_lines ael
2457 WHERE ap_dist.invoice_id = c_trx_id
2458 AND ap_dist.invoice_line_number = c_trx_line_id
2459 AND ap_dist.invoice_distribution_id = c_trx_line_dist_id
2460 AND lnk.application_id = 200
2461 AND lnk.source_distribution_type = 'AP_INV_DIST'
2462 AND lnk.source_distribution_id_num_1 = ap_dist.invoice_distribution_id
2463 AND lnk.event_id = ap_dist.accounting_event_id
2464 AND lnk.ae_line_num = ael.ae_line_num
2465 AND aeh.ae_header_id = ael.ae_header_id
2466 AND ael.accounting_class_code not in ('NRTAX','RTAX','LIABILITY')
2467 AND aeh.ledger_id = c_ledger_id
2468 AND aeh.ae_header_id = lnk.ae_header_id
2469 AND aeh.application_id = lnk.application_id
2470 AND ael.application_id = aeh.application_id;
2471
2472
2473 l_erv_entered_amt number;
2474 l_erv_accounted_amt number;
2475
2476 BEGIN
2477
2478 IF (g_level_procedure >= g_current_runtime_level ) THEN
2479 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts.BEGIN',
2480 'get_accounting_amounts(+)');
2481 END IF;
2482
2483 --Bug 5393051 :
2484 IF (g_level_statement >= g_current_runtime_level ) THEN
2485 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2486 'p_summary_level : '||p_summary_level);
2487 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2488 'p_report_name : '||p_report_name);
2489 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2490 'j : '||to_char(j));
2491 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2492 'p_application_id : '||p_application_id);
2493 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2494 'P_ENTITY_CODE : '||P_ENTITY_CODE);
2495 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2496 'P_EVENT_CLASS_CODE : '||P_EVENT_CLASS_CODE);
2497 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2498 'P_TRX_LEVEL_TYPE : '||P_TRX_LEVEL_TYPE);
2499 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2500 'P_TRX_ID : '||P_TRX_ID);
2501 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2502 'P_TRX_LINE_ID : '||P_TRX_LINE_ID);
2503 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2504 'P_TAX_LINE_ID : '||P_TAX_LINE_ID);
2505 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2506 'P_EVENT_ID : '||P_EVENT_ID);
2507 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2508 'P_AE_HEADER_ID : '||P_AE_HEADER_ID);
2509 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2510 'P_TAX_DIST_ID : '||P_TAX_DIST_ID);
2511 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2512 'P_LEDGER_ID : '||P_LEDGER_ID);
2513 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2514 'p_ae_line_num : '||p_ae_line_num);
2515
2516 END IF;
2517
2518 IF p_summary_level = 'TRANSACTION' THEN
2519 OPEN taxable_amount_hdr(p_application_id,
2520 p_entity_code,
2521 p_event_class_code,
2522 p_trx_level_type,
2523 p_trx_id ,
2524 p_ae_header_id ,
2525 p_event_id,
2526 p_ledger_id);
2527 FETCH taxable_amount_hdr INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2528 -- EXIT WHEN taxable_amount_hdr%NOTFOUND;
2529 CLOSE taxable_amount_hdr;
2530
2531 OPEN tax_amount_hdr(p_application_id,
2532 p_entity_code,
2533 p_event_class_code,
2534 p_trx_level_type,
2535 p_trx_id ,
2536 p_ae_header_id ,
2537 p_event_id,
2538 p_ledger_id);
2539 FETCH tax_amount_hdr INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
2540 -- EXIT WHEN tax_amount_hdr%NOTFOUND;
2541 CLOSE tax_amount_hdr;
2542 ELSIF p_summary_level = 'TRANSACTION_LINE' THEN
2543 OPEN taxable_amount_line(p_application_id,
2544 p_entity_code,
2545 p_event_class_code,
2546 p_trx_level_type,
2547 p_trx_id ,
2548 p_trx_line_id,
2549 p_ae_header_id ,
2550 p_event_id,
2551 p_ledger_id);
2552 FETCH taxable_amount_line INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2553 -- EXIT WHEN taxable_amount_line%NOTFOUND;
2554 CLOSE taxable_amount_line;
2555
2556 OPEN tax_amount_line(p_application_id,
2557 p_entity_code,
2558 p_event_class_code,
2559 p_trx_level_type,
2560 p_trx_id ,
2561 p_trx_line_id,
2562 p_ae_header_id ,
2563 p_event_id,
2564 p_ledger_id);
2565 FETCH tax_amount_line INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
2566 -- EXIT WHEN tax_amount_line%NOTFOUND;
2567 CLOSE tax_amount_line;
2568
2569 ELSIF p_summary_level = 'TRANSACTION_DISTRIBUTION' THEN
2570 OPEN taxable_amount_dist(p_ae_header_id ,
2571 p_event_id,
2572 p_tax_dist_id --Bug 5393051
2573 );
2574
2575 FETCH taxable_amount_dist INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2576
2577 -- Open ERV cursor -- Bug#9410781
2578
2579 OPEN taxable_amount_dist_ERV(p_ae_header_id ,
2580 p_event_id,
2581 p_tax_dist_id --Bug 5393051
2582 );
2583
2584 FETCH taxable_amount_dist_ERV INTO l_erv_entered_amt,l_erv_accounted_amt;
2585
2586 GT_TAXABLE_AMT(j) := GT_TAXABLE_AMT(j) + NVL(l_erv_entered_amt,0) ;
2587 GT_TAXABLE_AMT_FUNCL_CURR(j) := GT_TAXABLE_AMT_FUNCL_CURR(j) + NVL(l_erv_accounted_amt,0) ;
2588
2589 IF taxable_amount_dist%NOTFOUND OR GT_TAXABLE_AMT(j) IS NULL OR GT_TAXABLE_AMT_FUNCL_CURR(j) IS NULL THEN
2590 IF (g_level_procedure >= g_current_runtime_level ) THEN
2591 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2592 'Cursor taxable_amount_dist Not Found, So open cursor taxable_amount_dist1');
2593 END IF;
2594 OPEN taxable_amount_dist1 (p_application_id,
2595 p_entity_code,
2596 p_event_class_code,
2597 p_trx_level_type,
2598 p_trx_id ,
2599 p_trx_line_id,
2600 p_ledger_id,
2601 p_tax_dist_id,
2602 p_ae_header_id);
2603 FETCH taxable_amount_dist1 INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2604
2605 IF taxable_amount_dist1%NOTFOUND OR GT_TAXABLE_AMT(j) IS NULL OR GT_TAXABLE_AMT_FUNCL_CURR(j) IS NULL THEN
2606 IF (g_level_procedure >= g_current_runtime_level ) THEN
2607 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2608 'Cursor taxable_amount_dist1 Not Found, So open cursor taxable_amount_dist2');
2609 END IF;
2610
2611 OPEN taxable_amount_dist2(p_application_id,
2612 p_entity_code,
2613 p_event_class_code,
2614 p_trx_level_type,
2615 p_trx_id ,
2616 p_trx_line_id,
2617 p_ae_header_id ,
2618 p_event_id,
2619 p_ledger_id,
2620 p_tax_dist_id --Bug 5393051
2621 );
2622
2623 FETCH taxable_amount_dist2 INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2624
2625 IF (p_report_name = 'ZXXTATAT' AND (taxable_amount_dist2%NOTFOUND
2626 OR GT_TAXABLE_AMT(j) IS NULL OR GT_TAXABLE_AMT_FUNCL_CURR(j) IS NULL )) THEN
2627 IF (g_level_procedure >= g_current_runtime_level ) THEN
2628 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',
2629 'Cursor taxable_amount_dist2 Not Found, So open cursor taxable_amount_dist_no_tax');
2630 END IF;
2631 OPEN taxable_amount_dist_no_tax(p_trx_id,
2632 p_trx_line_id,
2633 p_ledger_id,
2634 p_trx_line_dist_id);
2635 FETCH taxable_amount_dist_no_tax INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2636 CLOSE taxable_amount_dist_no_tax;
2637 END IF;
2638 CLOSE taxable_amount_dist2;
2639 END IF; -- Dist2 Cursor
2640 CLOSE taxable_amount_dist1;
2641 END IF; -- Dist1 cursor
2642
2643 CLOSE taxable_amount_dist;
2644 CLOSE taxable_amount_dist_ERV;
2645
2646 OPEN tax_amount_dist(p_tax_dist_id,
2647 p_ae_header_id ,
2648 p_event_id,
2649 p_ae_line_num);
2650 FETCH tax_amount_dist INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
2651 -- EXIT WHEN tax_amount_dist%NOTFOUND;
2652 CLOSE tax_amount_dist;
2653 END IF;
2654
2655 IF (g_level_statement >= g_current_runtime_level ) THEN
2656 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',' j : '||to_Char(j)||
2657 'Taxable Amt : '|| to_char(GT_TAXABLE_AMT(j)) ||'TAXABLE_AMT_FUNCL_CURR : '||
2658 GT_TAXABLE_AMT_FUNCL_CURR(j));
2659 FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts',' j : '||to_Char(j)||
2660 'Tax Amt : '|| to_char(GT_TAX_AMT(j)) ||'TAX_AMT_FUNCL_CURR : '||
2661 GT_TAX_AMT_FUNCL_CURR(j));
2662 END IF;
2663
2664 IF (g_level_procedure >= g_current_runtime_level ) THEN
2665 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_accounting_amounts.END',
2666 'get_accounting_amounts(-)');
2667 END IF;
2668
2669 END get_accounting_amounts;
2670
2671
2672 PROCEDURE get_discount_info
2673 ( j IN BINARY_INTEGER,
2674 P_TRX_ID IN NUMBER,
2675 P_TAX_LINE_ID IN NUMBER,
2676 P_SUMMARY_LEVEL IN VARCHAR2,
2677 P_DIST_ID IN NUMBER,
2678 P_TRX_LINE_ID IN NUMBER,
2679 P_TRX_LINE_DIST_ID IN NUMBER,
2680 P_TAX_REC_FLAG IN VARCHAR2,
2681 p_tax_regime_code IN VARCHAR2,
2682 p_tax IN VARCHAR2,
2683 p_tax_status_code IN VARCHAR2,
2684 p_tax_rate_id IN NUMBER,
2685 P_LEDGER_ID IN NUMBER,
2686 P_DISC_DISTRIBUTION_METHOD IN VARCHAR2,
2687 P_LIABILITY_POST_LOOKUP_CODE IN VARCHAR2
2688 )
2689 IS
2690
2691 -- nipatel - I find lots of issues with this query. The main query only restricts
2692 -- invoices based on distribution line number. We should have a condition based on
2693 -- invoice id in the main query. The join to ap_invoices is not necessary since
2694 -- we already have trx_id as input parameter which can be used to join to
2695 -- ap_invoice_distributions or ap_invoice_payments. Why do we need the subquery?
2696 -- it seems to be unncessary if we put the same conditions in the main query.
2697 -- Need to get this query reviewed by AP team
2698
2699 -- CURSOR taxable_hdr_csr IS
2700 -- SELECT sum(aphd.amount), -- discount amount (entered)
2701 -- sum(aphd.paid_base_amount) -- discount amount (accounted)
2702 -- FROM ap_invoice_distributions_all aid,
2703 -- ap_invoices_all ai,
2704 -- ap_invoice_payments_all aip,
2705 -- ap_payment_hist_dists aphd,
2706 -- ap_payment_history_all aph
2707 -- WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2708 -- AND aid.invoice_id = aip.invoice_id
2709 -- AND aid.distribution_line_number
2710 -- IN (SELECT distribution_line_number
2711 -- FROM ap_invoice_distributions_all
2712 -- WHERE invoice_id = p_trx_id
2713 -- AND line_type_lookup_code = 'ITEM')
2714 -- AND aip.invoice_payment_id = aphd.invoice_payment_id
2715 -- AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2716 -- AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2717 -- AND nvl(aph.historical_flag, 'N') = 'N'
2718 -- AND aph.check_id = aip.check_id
2719
2720 CURSOR taxable_hdr_csr IS
2721 SELECT sum(aphd.amount), -- discount amount (entered)
2722 sum(aphd.paid_base_amount) -- discount amount (accounted)
2723 FROM ap_invoice_distributions_all aid,
2724 ap_invoice_payments_all aip,
2725 ap_payment_hist_dists aphd,
2726 ap_payment_history_all aph,
2727 zx_rec_nrec_dist zx_dist
2728 WHERE aid.invoice_id = p_trx_id
2729 AND aid.invoice_id = aip.invoice_id
2730 AND zx_dist.recoverable_flag = p_tax_rec_flag
2731 AND aip.invoice_payment_id = aphd.invoice_payment_id
2732 --AND aid.line_type_lookup_code in ('REC_TAX', 'NONREC_TAX')
2733 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2734 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2735 AND nvl(aph.historical_flag, 'N') = 'N'
2736 AND aph.check_id = aip.check_id
2737 and aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
2738 and aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
2739 and zx_dist.trx_id = aid.invoice_id
2740 and ((zx_dist.trx_line_dist_id = aid.invoice_distribution_id) OR
2741 ((aid.line_type_lookup_code='IPV') and (zx_dist.trx_line_dist_id = aid.related_id)))
2742 and zx_dist.application_id = 200
2743 and zx_dist.tax_regime_code = p_tax_regime_code
2744 and zx_dist.tax = p_tax
2745 and zx_dist.tax_status_code = p_tax_status_code
2746 and zx_dist.tax_rate_id = p_tax_rate_id
2747 AND zx_dist.entity_code = 'AP_INVOICES'
2748 and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
2749 UNION ALL
2750
2751 -- nipatel - I find lots of issues with this query. The main query only restricts
2752 -- invoices based on distribution line number. We should have a condition based on
2753 -- invoice id in the main query. The join to ap_invoices is not necessary since
2754 -- we already have trx_id as input parameter which can be used to join to
2755 -- ap_invoice_distributions or ap_invoice_payments. Why do we need the subquery?
2756 -- it seems to be unncessary if we put the same conditions in the main query. Also
2757 -- there a re no indexes based on xal.Upg_Tax_Reference_ID2/3 whihc causes FTS
2758 -- on xla_ae_lines. Need to get this query reviewed by AP team and log a bug
2759 -- against XLA team for indexes.
2760
2761 SELECT /*+ leading (aid,aip) */
2762 xal.entered_dr - xal.entered_cr ,
2763 -- discount entered amount (replace this with new xla colum names)
2764 xal.accounted_dr -xal.entered_cr
2765 -- discount entered amount (replace this with new xla colum names)
2766 FROM ap_invoice_distributions_all aid,
2767 -- ap_invoices_all ai,
2768 ap_invoice_payments_all aip,
2769 ap_payment_history_all aph,
2770 xla_ae_lines xal
2771 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2772 AND aid.line_type_lookup_code = 'ITEM'
2773 AND aip.invoice_id = aid.invoice_id
2774 -- AND aid.distribution_line_number
2775 -- IN (SELECT distribution_line_number
2776 -- FROM ap_invoice_distributions_all
2777 -- WHERE invoice_id = p_trx_id
2778 -- AND line_type_lookup_code = 'ITEM')
2779 AND xal.application_id = 200
2780 AND xal.ledger_id = p_ledger_id
2781 AND xal.accounting_class_code = 'DISCOUNT'
2782 AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
2783 AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
2784 AND aph.check_id = aip.check_id
2785 AND nvl(aph.historical_flag, 'N') = 'Y';
2786
2787 -- nipatel - I find lots of issues with this query. The main query only restricts
2788 -- invoices based on distribution line number. We should have a condition based on
2789 -- invoice id in the main query. The join to ap_invoices is not necessary since
2790 -- we already have trx_id as input parameter which can be used to join to
2791 -- ap_invoice_distributions or ap_invoice_payments. Why do we need the subquery?
2792 -- it seems to be unncessary if we put the same conditions in the main query. Also
2793 -- there a re no indexes based on xal.Upg_Tax_Reference_ID2/3 whihc causes FTS
2794 -- on xla_ae_lines. Need to get this query reviewed by AP team and log a bug
2795 -- against XLA team for indexes.
2796
2797 -- CURSOR tax_hdr_csr IS
2798 -- SELECT sum(aphd.amount), -- discount amount (entered)
2799 -- sum(aphd.paid_base_amount) -- discount amount (accounted)
2800 -- FROM ap_invoice_distributions_all aid,
2801 -- ap_invoices_all ai,
2802 -- ap_invoice_payments_all aip,
2803 -- ap_payment_hist_dists aphd,
2804 -- ap_payment_history_all aph
2805 -- WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2806 -- AND aid.invoice_id = aip.invoice_id
2807 -- AND aid.distribution_line_number
2808 -- IN (SELECT distribution_line_number
2809 -- FROM ap_invoice_distributions_all
2810 -- WHERE invoice_id = p_trx_id
2811 -- AND line_type_lookup_code in ('REC_TAX', 'NONREC_TAX'))
2812 -- AND aip.invoice_payment_id = aphd.invoice_payment_id
2813 -- AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2814 -- AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2815 -- AND nvl(aph.historical_flag, 'N') = 'N'
2816 -- AND aph.check_id = aip.check_id
2817 -- New --
2818 -- SELECT sum(aphd.amount), -- discount amount (entered)
2819 -- sum(aphd.paid_base_amount) -- discount amount (accounted)
2820 -- FROM ap_invoice_distributions_all aid,
2821 -- ap_invoice_payments_all aip,
2822 -- ap_payment_hist_dists aphd,
2823 -- ap_payment_history_all aph
2824 -- WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2825 -- AND aid.invoice_id = aip.invoice_id
2826 -- AND aid.tax_recoverable_flag = p_tax_rec_flag
2827 -- AND aip.invoice_payment_id = aphd.invoice_payment_id
2828 -- AND aid.line_type_lookup_code in ('REC_TAX', 'NONREC_TAX')
2829 -- AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2830 -- AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2831 -- AND nvl(aph.historical_flag, 'N') = 'N'
2832 -- AND aph.check_id = aip.check_id
2833 -- and aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
2834 -- and aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
2835 --and aph.TRANSACTION_TYPE = 'PAYMENT CLEARING'
2836
2837 CURSOR tax_hdr_csr IS
2838 SELECT sum(aphd.amount), -- discount amount (entered)
2839 sum(aphd.paid_base_amount) -- discount amount (accounted)
2840 FROM ap_invoice_distributions_all aid,
2841 ap_invoice_payments_all aip,
2842 ap_payment_hist_dists aphd,
2843 ap_payment_history_all aph,
2844 zx_rec_nrec_dist zx_dist
2845 WHERE aid.invoice_id = p_trx_id
2846 AND aid.invoice_id = aip.invoice_id
2847 AND aid.tax_recoverable_flag = p_tax_rec_flag
2848 AND aip.invoice_payment_id = aphd.invoice_payment_id
2849 AND aid.line_type_lookup_code in ('REC_TAX', 'NONREC_TAX')
2850 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2851 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2852 AND nvl(aph.historical_flag, 'N') = 'N'
2853 AND aph.check_id = aip.check_id
2854 and aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
2855 and aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
2856 and zx_dist.trx_id = aid.invoice_id
2857 and zx_dist.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
2858 and zx_dist.recoverable_flag = aid.tax_recoverable_flag
2859 and zx_dist.application_id = 200
2860 and zx_dist.tax_regime_code = p_tax_regime_code
2861 and zx_dist.tax = p_tax
2862 and zx_dist.tax_status_code = p_tax_status_code
2863 and zx_dist.tax_rate_id = p_tax_rate_id
2864 AND zx_dist.entity_code = 'AP_INVOICES'
2865 and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
2866 UNION ALL
2867 SELECT /*+ leading(aid,aip) */
2868 xal.entered_dr - xal.entered_cr ,
2869 -- discount entered amount (replace this with new xla colum names)
2870 xal.accounted_dr -xal.entered_cr
2871 -- discount entered amount (replace this with new xla colum names)
2872 FROM ap_invoice_distributions_all aid,
2873 -- ap_invoices_all ai,
2874 ap_invoice_payments_all aip,
2875 ap_payment_history_all aph,
2876 xla_ae_lines xal
2877 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2878 AND aid.line_type_lookup_code = 'TAX'
2879 AND aip.invoice_id = aid.invoice_id
2880 -- AND aid.distribution_line_number
2881 -- IN (SELECT distribution_line_number
2882 -- FROM ap_invoice_distributions_all
2883 -- WHERE invoice_id = p_trx_id
2884 -- AND line_type_lookup_code = 'TAX')
2885 AND xal.application_id = 200
2886 AND xal.accounting_class_code = 'DISCOUNT'
2887 AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
2888 AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
2889 AND aph.check_id = aip.check_id
2890 AND nvl(aph.historical_flag, 'N') = 'Y';
2891
2892
2893 CURSOR taxable_line_csr IS
2894 SELECT sum(aphd.amount), -- discount amount (entered)
2895 sum(aphd.paid_base_amount) -- discount amount (accounted)
2896 FROM ap_invoice_distributions_all aid,
2897 -- ap_invoices_all ai,
2898 ap_invoice_payments_all aip,
2899 ap_payment_hist_dists aphd,
2900 ap_payment_history_all aph
2901 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2902 AND aid.invoice_id = aip.invoice_id
2903 AND aid.invoice_distribution_id = p_trx_line_id
2904 AND aip.invoice_payment_id = aphd.invoice_payment_id
2905 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2906 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2907 AND nvl(aph.historical_flag, 'N') = 'N'
2908 AND aph.check_id = aip.check_id
2909 UNION ALL
2910 SELECT /*+ leading (aid,aip) */
2911 xal.entered_dr - xal.entered_cr ,
2912 -- discount entered amount (replace this with new xla colum names)
2913 xal.accounted_dr -xal.entered_cr
2914 -- discount entered amount (replace this with new xla colum names)
2915 FROM ap_invoice_distributions_all aid,
2916 -- ap_invoices_all ai,
2917 ap_invoice_payments_all aip,
2918 ap_payment_history_all aph,
2919 xla_ae_lines xal
2920 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2921 AND aid.invoice_distribution_id = p_trx_line_id
2922 AND aip.invoice_id = aid.invoice_id
2923 AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
2924 AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
2925 AND xal.application_id = 200
2926 AND xal.accounting_class_code = 'DISCOUNT'
2927 AND xal.ledger_id = p_ledger_id
2928 AND aph.check_id = aip.check_id
2929 AND nvl(aph.historical_flag, 'N') = 'Y';
2930
2931
2932 CURSOR tax_line_csr IS
2933 SELECT sum(aphd.amount), -- discount amount (entered)
2934 sum(aphd.paid_base_amount) -- discount amount (accounted)
2935 FROM ap_invoice_distributions_all aid,
2936 -- ap_invoices_all ai,
2937 ap_invoice_payments_all aip,
2938 ap_payment_hist_dists aphd,
2939 ap_payment_history_all aph
2940 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2941 AND aid.invoice_id = aip.invoice_id
2942 AND aid.invoice_distribution_id = p_tax_line_id
2943 AND aip.invoice_payment_id = aphd.invoice_payment_id
2944 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2945 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2946 AND nvl(aph.historical_flag, 'N') = 'N'
2947 AND aph.check_id = aip.check_id
2948 UNION ALL
2949 SELECT /*+ leading (aid,aip) */
2950 xal.entered_dr - xal.entered_cr ,
2951 -- discount entered amount (replace this with new xla colum names)
2952 xal.accounted_dr -xal.entered_cr
2953 -- discount entered amount (replace this with new xla colum names)
2954 FROM ap_invoice_distributions_all aid,
2955 -- ap_invoices_all ai,
2956 ap_invoice_payments_all aip,
2957 ap_payment_history_all aph,
2958 xla_ae_lines xal
2959 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2960 AND aid.invoice_distribution_id = p_tax_line_id
2961 AND aip.invoice_id = aid.invoice_id
2962 AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
2963 AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
2964 AND xal.application_id = 200
2965 AND xal.ledger_id = p_ledger_id
2966 AND xal.accounting_class_code = 'DISCOUNT'
2967 AND aph.check_id = aip.check_id
2968 AND nvl(aph.historical_flag, 'N') = 'Y';
2969
2970
2971 CURSOR taxable_dist_csr IS
2972 SELECT sum(aphd.amount), -- discount amount (entered)
2973 sum(aphd.paid_base_amount) -- discount amount (accounted)
2974 FROM ap_invoice_distributions_all aid,
2975 -- ap_invoices_all ai,
2976 ap_invoice_payments_all aip,
2977 ap_payment_hist_dists aphd,
2978 ap_payment_history_all aph,
2979 zx_rec_nrec_dist zx_dist
2980 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
2981 AND aid.invoice_id = aip.invoice_id
2982 and ((zx_dist.trx_line_dist_id = aid.invoice_distribution_id) OR
2983 ((aid.line_type_lookup_code='IPV') and (zx_dist.trx_line_dist_id = aid.related_id)))
2984 AND zx_dist.rec_nrec_tax_dist_id = p_dist_id
2985 AND aip.invoice_payment_id = aphd.invoice_payment_id
2986 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
2987 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
2988 AND nvl(aph.historical_flag, 'N') = 'N'
2989 AND aph.check_id = aip.check_id
2990 and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
2991 UNION ALL
2992 SELECT SUM(NVL(xal.entered_dr,0) - NVL(xal.entered_cr,0)) ,
2993 -- discount entered amount (replace this with new xla colum names)
2994 SUM(NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0))
2995 -- discount entered amount (replace this with new xla colum names)
2996 FROM ap_invoice_distributions_all aid,
2997 -- ap_invoices_all ai,
2998 ap_invoice_payments_all aip,
2999 ap_payment_history_all aph,
3000 xla_ae_lines xal
3001 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
3002 AND aid.invoice_distribution_id = p_trx_line_dist_id
3003 AND aip.invoice_id = aid.invoice_id
3004 AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
3005 AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
3006 AND xal.application_id = 200
3007 AND xal.accounting_class_code = 'DISCOUNT'
3008 AND xal.ledger_id = p_ledger_id
3009 AND aph.check_id = aip.check_id
3010 AND nvl(aph.historical_flag, 'N') = 'Y';
3011
3012 CURSOR taxable_disc_sys_cur IS
3013 SELECT sum(aphd.amount),
3014 sum(aphd.paid_base_amount)
3015 FROM ap_invoice_distributions_all aid,
3016 ap_invoice_payments_all aip,
3017 ap_payment_hist_dists aphd,
3018 ap_payment_history_all aph
3019 WHERE aid.invoice_id = p_trx_id
3020 AND aid.invoice_id = aip.invoice_id
3021 AND aip.invoice_payment_id = aphd.invoice_payment_id
3022 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
3023 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
3024 AND nvl(aph.historical_flag, 'N') = 'N'
3025 AND aph.check_id = aip.check_id
3026 AND aid.line_type_lookup_code not in ('REC_TAX', 'NONREC_TAX');
3027
3028 CURSOR tax_dist_csr IS
3029 SELECT sum(aphd.amount), -- discount amount (entered)
3030 sum(aphd.paid_base_amount) -- discount amount (accounted)
3031 FROM ap_invoice_distributions_all aid,
3032 -- ap_invoices_all ai,
3033 ap_invoice_payments_all aip,
3034 ap_payment_hist_dists aphd,
3035 ap_payment_history_all aph
3036 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
3037 AND aid.invoice_id = aip.invoice_id
3038 AND aid.detail_tax_dist_id = p_dist_id
3039 AND aip.invoice_payment_id = aphd.invoice_payment_id
3040 AND aphd.PAY_DIST_LOOKUP_CODE = 'DISCOUNT'
3041 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
3042 AND nvl(aph.historical_flag, 'N') = 'N'
3043 AND aph.check_id = aip.check_id
3044 AND aphd.ACCOUNTING_EVENT_ID = aph.ACCOUNTING_EVENT_ID
3045 AND aphd.PAYMENT_HISTORY_ID = aph.PAYMENT_HISTORY_ID
3046 and aph.TRANSACTION_TYPE = 'PAYMENT CREATED'
3047 UNION ALL
3048 SELECT SUM(NVL(xal.entered_dr,0) - NVL(xal.entered_cr,0)) ,
3049 -- discount entered amount (replace this with new xla colum names)
3050 SUM(NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0))
3051 -- discount entered amount (replace this with new xla colum names)
3052 FROM ap_invoice_distributions_all aid,
3053 -- ap_invoices_all ai,
3054 ap_invoice_payments_all aip,
3055 ap_payment_history_all aph,
3056 xla_ae_lines xal
3057 WHERE aid.invoice_id = p_trx_id -- ai.invoice_id
3058 -- AND aid.invoice_distribution_id = p_tax_line_id
3059 AND aid.detail_tax_dist_id = p_dist_id
3060 AND aip.invoice_id = aid.invoice_id
3061 AND xal.Upg_Tax_Reference_ID3 = aip.invoice_payment_id
3062 AND xal.Upg_Tax_Reference_ID2 = aid.old_dist_line_number
3063 AND xal.application_id = 200
3064 AND xal.ledger_id = p_ledger_id
3065 AND xal.accounting_class_code = 'DISCOUNT'
3066 AND aph.check_id = aip.check_id
3067 AND nvl(aph.historical_flag, 'N') = 'Y';
3068
3069
3070
3071 l_tax_entered_disc_amt NUMBER;
3072 l_tax_acct_disc_amt NUMBER;
3073 -- l_tax1_entered_disc_amt NUMBER;
3074 -- l_tax1_accounted_disc_amt NUMBER;
3075 -- l_tax2_entered_disc_amt NUMBER;
3076 -- l_tax2_accounted_disc_amt:= 0;
3077 -- l_tax3_entered_disc_amt:= 0;
3078 -- l_tax3_accounted_disc_amt:= 0;
3079 -- l_tax4_entered_disc_amt:= 0;
3080 -- l_tax4_accounted_disc_amt:= 0;
3081 l_0_taxable_entered_disc_amt NUMBER;
3082 l_0_taxable_accounted_disc_amt NUMBER;
3083 l_taxable_entered_disc_amt NUMBER;
3084 l_taxable_acct_disc_amt NUMBER;
3085 i BINARY_INTEGER;
3086
3087 BEGIN
3088
3089 IF (g_level_procedure >= g_current_runtime_level ) THEN
3090 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info.BEGIN',
3091 'get_discount_info(+)');
3092 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info.BEGIN',
3093 'P_DISC_DISTRIBUTION_METHOD :'||P_DISC_DISTRIBUTION_METHOD);
3094 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info.BEGIN',
3095 'P_LIABILITY_POST_LOOKUP_CODE : '||P_LIABILITY_POST_LOOKUP_CODE);
3096 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info.BEGIN',
3097 'P_TRX_ID : '||to_char(P_TRX_ID));
3098 END IF;
3099 i := j;
3100 --P_INDEX_TO_GLOBAL_TABLES;
3101 --l_tax_type := P_SUB_ITF_REC.tax_code_type_code;
3102
3103 -- get discount tax amount;
3104
3105
3106 l_tax_entered_disc_amt:= 0;
3107 l_tax_acct_disc_amt:= 0;
3108 -- l_tax1_entered_disc_amt:= 0;
3109 -- l_tax1_accounted_disc_amt:= 0;
3110 -- l_tax2_entered_disc_amt:= 0;
3111 -- l_tax2_accounted_disc_amt:= 0;
3112 -- l_tax3_entered_disc_amt:= 0;
3113 -- l_tax3_accounted_disc_amt:= 0;
3114 -- l_tax4_entered_disc_amt:= 0;
3115 -- l_tax4_accounted_disc_amt:= 0;
3116 l_0_taxable_entered_disc_amt:= 0;
3117 l_0_taxable_accounted_disc_amt:= 0;
3118 l_taxable_entered_disc_amt:= 0;
3119 l_taxable_acct_disc_amt:= 0;
3120
3121
3122 IF P_DISC_DISTRIBUTION_METHOD = 'EXPENSE' OR
3123 P_LIABILITY_POST_LOOKUP_CODE IS NOT NULL THEN
3124 IF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
3125
3126
3127 OPEN taxable_dist_csr;
3128 --(p_trx_line_id);
3129 FETCH taxable_dist_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
3130
3131 IF taxable_dist_csr%NOTFOUND THEN
3132 -- Message
3133 NULL;
3134 END IF; -- tax_discount_cur
3135
3136 IF (g_level_procedure >= g_current_runtime_level ) THEN
3137 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3138 'l_taxable_entered_disc_amt : '||to_char(l_taxable_entered_disc_amt));
3139 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3140 'l_taxable_acct_disc_amt : '||to_char(l_taxable_acct_disc_amt));
3141 END IF;
3142
3143 CLOSE taxable_dist_csr;
3144
3145 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
3146
3147 OPEN taxable_line_csr;
3148 --(p_trx_line_id);
3149 FETCH taxable_line_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
3150
3151 IF taxable_line_csr%NOTFOUND THEN
3152 NULL;
3153 -- Message
3154 END IF; -- tax_discount_cur
3155
3156 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
3157
3158 OPEN taxable_hdr_csr;
3159 --(p_trx_line_id);
3160 FETCH taxable_hdr_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
3161
3162 IF (g_level_procedure >= g_current_runtime_level ) THEN
3163 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3164 'l_taxable_entered_disc_amt : '||to_char(l_taxable_entered_disc_amt));
3165 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3166 'l_taxable_acct_disc_amt : '||to_char(l_taxable_acct_disc_amt));
3167 END IF;
3168 IF taxable_hdr_csr%NOTFOUND THEN
3169 NULL;
3170 -- Message
3171 END IF; -- tax_discount_cur
3172 CLOSE taxable_hdr_csr;
3173
3174 END IF; --summary level
3175
3176
3177 -- GT_TAXABLE_ENT_DISC_AMT_TBL(j):= l_taxable_entered_disc_amt;
3178 -- G_TAXABLE_ACCT_DISC_AMT_TBL(j):= l_taxable_acct_disc_amt;
3179
3180 GT_TAXABLE_DISC_AMT(j) := l_taxable_entered_disc_amt;
3181 GT_TAXABLE_DISC_AMT_FUNCL_CURR(j) := l_taxable_acct_disc_amt;
3182
3183 ELSE -- P_DISC_DISTRIBUTION_METHOD = 'TAX' AND P_LIABILITY_POST_LOOKUP_CODE IS NULL
3184 IF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
3185 OPEN taxable_dist_csr;
3186 FETCH taxable_dist_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
3187 IF taxable_dist_csr%NOTFOUND THEN
3188 NULL;
3189 END IF;
3190
3191 IF (g_level_procedure >= g_current_runtime_level ) THEN
3192 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3193 'l_taxable_entered_disc_amt : '||to_char(l_taxable_entered_disc_amt));
3194 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3195 'l_taxable_acct_disc_amt : '||to_char(l_taxable_acct_disc_amt));
3196 END IF;
3197 CLOSE taxable_dist_csr;
3198 ELSE
3199 IF (g_level_procedure >= g_current_runtime_level ) THEN
3200 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info', ' Dist method TAX : ');
3201 END IF;
3202 OPEN taxable_hdr_csr;
3203 FETCH taxable_hdr_csr INTO l_taxable_entered_disc_amt, l_taxable_acct_disc_amt;
3204 IF taxable_hdr_csr%NOTFOUND THEN
3205 -- close taxable_disc_cur;
3206 -- Message
3207 NULL;
3208 END IF; -- taxable_disc_sys_cur
3209 close taxable_hdr_csr;
3210
3211 IF (g_level_procedure >= g_current_runtime_level ) THEN
3212 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3213 'l_taxable_entered_disc_amt : '||to_char(l_taxable_entered_disc_amt));
3214 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3215 'l_taxable_acct_disc_amt : '||to_char(l_taxable_acct_disc_amt));
3216 END IF;
3217 END IF;
3218 GT_TAXABLE_DISC_AMT(j) := l_taxable_entered_disc_amt;
3219 GT_TAXABLE_DISC_AMT_FUNCL_CURR(j) := l_taxable_acct_disc_amt;
3220
3221 END IF;
3222
3223 IF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
3224
3225 OPEN tax_dist_csr;
3226 --(p_trx_line_id);
3227 FETCH tax_dist_csr INTO l_tax_entered_disc_amt, l_tax_acct_disc_amt;
3228
3229 IF tax_dist_csr%NOTFOUND THEN
3230 -- Message
3231 NULL;
3232 END IF; -- tax_discount_cur
3233
3234 CLOSE tax_dist_csr;
3235
3236 IF (g_level_procedure >= g_current_runtime_level ) THEN
3237 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3238 'l_tax_entered_disc_amt : '||to_char(l_tax_entered_disc_amt));
3239 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3240 'l_tax_acct_disc_amt : '||to_char(l_tax_acct_disc_amt));
3241 END IF;
3242
3243 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
3244
3245 OPEN tax_line_csr;
3246 --(p_trx_line_id);
3247 FETCH tax_line_csr INTO l_tax_entered_disc_amt, l_tax_acct_disc_amt;
3248
3249 IF tax_line_csr%NOTFOUND THEN
3250 NULL; -- Message
3251 END IF; -- tax_discount_cur
3252
3253 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
3254
3255 OPEN tax_hdr_csr;
3256 --(p_trx_line_id);
3257 FETCH tax_hdr_csr INTO l_tax_entered_disc_amt, l_tax_acct_disc_amt;
3258
3259 IF tax_hdr_csr%NOTFOUND THEN
3260 NULL; -- Message
3261 END IF; -- tax_discount_cur
3262 IF (g_level_procedure >= g_current_runtime_level ) THEN
3263 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3264 'l_tax_entered_disc_amt : '||to_char(l_tax_entered_disc_amt));
3265 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3266 'l_tax_acct_disc_amt : '||to_char(l_tax_acct_disc_amt));
3267 END IF;
3268
3269 END IF; --summary level
3270
3271
3272 GT_TAX_DISC_AMT(j) := l_tax_entered_disc_amt;
3273 GT_TAX_DISC_AMT_FUNCL_CURR(j) := l_tax_acct_disc_amt;
3274
3275 EXCEPTION
3276
3277 WHEN NO_DATA_FOUND THEN
3278 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3279 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3280 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_discount_info- '|| g_error_buffer);
3281 FND_MSG_PUB.Add;
3282 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3283 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3284 g_error_buffer);
3285 END IF;
3286
3287 WHEN OTHERS THEN
3288 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3289 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3290 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','get_discount_info- '|| g_error_buffer);
3291 FND_MSG_PUB.Add;
3292 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3293 FND_LOG.STRING(g_level_unexpected,
3294 'ZX.TRL.ZX_AP_POPULATE_PKG.get_discount_info',
3295 g_error_buffer);
3296 END IF;
3297
3298 END get_discount_info;
3299
3300 -- End Accounting procedures --
3301
3302 PROCEDURE update_zx_rep_detail_t(
3303 P_COUNT IN BINARY_INTEGER)
3304 IS
3305
3306 BEGIN
3307
3308 IF (g_level_procedure >= g_current_runtime_level ) THEN
3309 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t.BEGIN',
3310 'update_zx_rep_detail_t(+)');
3311 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t',
3312 'Rows Update by update_zx_rep_detail_t :'||to_char(p_count));
3313 END IF;
3314
3315 FORALL i IN 1 .. p_count
3316 UPDATE /*+ INDEX (ZX_REP_TRX_DETAIL_T ZX_REP_TRX_DETAIL_T_U1)*/ ZX_REP_TRX_DETAIL_T
3317 SET REP_CONTEXT_ID = G_REP_CONTEXT_ID,
3318 BILLING_TP_NUMBER = GT_BILLING_TP_NUMBER(i),
3319 BILLING_TP_TAX_REG_NUM = GT_BILLING_TP_TAX_REG_NUM(i),
3320 BILLING_TP_TAXPAYER_ID = GT_BILLING_TP_TAXPAYER_ID(i),
3321 BILLING_TP_SITE_NAME_ALT = GT_BILLING_TP_SITE_NAME_ALT(i),
3322 BILLING_TP_SITE_NAME = GT_BILLING_TP_SITE_NAME(i),
3323 BILLING_TP_SITE_TAX_REG_NUM = GT_BILLING_SITE_TAX_REG_NUM(i),
3324 BILL_FROM_PARTY_SITE_ID = GT_BILL_FROM_PARTY_SITE_ID(i),
3325 HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(i),
3326 BILL_FROM_PARTY_ID = GT_BILL_FROM_PARTY_ID(i),
3327 BILLING_TP_NAME = GT_BILLING_TP_NAME(i),
3328 BILLING_TP_NAME_ALT = GT_BILLING_TP_NAME_ALT(i),
3329 BILLING_TP_SIC_CODE = GT_BILLING_TP_SIC_CODE(i),
3330 BILLING_TP_CITY = GT_BILLING_TP_CITY(i),
3331 BILLING_TP_COUNTY = GT_BILLING_TP_COUNTY(i),
3332 BILLING_TP_STATE = GT_BILLING_TP_STATE(i),
3333 BILLING_TP_PROVINCE = GT_BILLING_TP_PROVINCE(i),
3334 BILLING_TP_ADDRESS1 = GT_BILLING_TP_ADDRESS1(i),
3335 BILLING_TP_ADDRESS2 = GT_BILLING_TP_ADDRESS2(i),
3336 BILLING_TP_ADDRESS3 = GT_BILLING_TP_ADDRESS3(i),
3337 BILLING_TP_ADDRESS_LINES_ALT = GT_BILLING_TP_ADDR_LINES_ALT(i),
3338 BILLING_TP_COUNTRY = GT_BILLING_TP_COUNTRY(i),
3339 BILLING_TP_POSTAL_CODE = GT_BILLING_TP_POSTAL_CODE(i),
3340 GDF_PO_VENDOR_SITE_ATT17 = GT_GDF_PO_VENDOR_SITE_ATT17(i),
3341 FUNCTIONAL_CURRENCY_CODE = G_FUN_CURRENCY_CODE,
3342 LEDGER_NAME = GT_LEDGER_NAME(i),
3343 TAXABLE_DISC_AMT = GT_TAXABLE_DISC_AMT(i),
3344 TAXABLE_DISC_AMT_FUNCL_CURR = GT_TAXABLE_DISC_AMT_FUNCL_CURR(i),
3345 TAX_DISC_AMT = GT_TAX_DISC_AMT(i),
3346 TAX_DISC_AMT_FUNCL_CURR = GT_TAX_DISC_AMT_FUNCL_CURR(i),
3347 TAX_AMT = TAX_AMT + GT_TAX_AMT(i),
3348 TAX_AMT_FUNCL_CURR = TAX_AMT_FUNCL_CURR + GT_TAX_AMT_FUNCL_CURR(i),
3349 TAXABLE_AMT = GT_TAXABLE_AMT(i),
3350 TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i)
3351 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
3352
3353 IF (g_level_procedure >= g_current_runtime_level ) THEN
3354 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t.END',
3355 'update_zx_rep_detail_t(-)');
3356 END IF;
3357
3358 EXCEPTION
3359 WHEN OTHERS THEN
3360 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3361 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3362 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3363 FND_MSG_PUB.Add;
3364 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3365 FND_LOG.STRING(g_level_unexpected,
3366 'ZX.TRL.ZX_AP_POPULATE_PKG.update_zx_rep_detail_t',
3367 g_error_buffer);
3368 END IF;
3369 g_retcode := 2;
3370
3371 END update_zx_rep_detail_t;
3372
3373
3374
3375 /*===========================================================================+
3376 | PROCEDURE |
3377 | insert_actg_info |
3378 | DESCRIPTION |
3379 | This procedure inserts payables tax data into ZX_REP_TRX_DETAIL_T table|
3380 | |
3381 | SCOPE - Private |
3382 | |
3383 | NOTES |
3384 | |
3385 | MODIFICATION HISTORY |
3386 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
3387 | |
3388 +===========================================================================*/
3389
3390
3391 PROCEDURE insert_actg_info(
3392 P_COUNT IN BINARY_INTEGER)
3393 IS
3394 l_count NUMBER;
3395
3396 BEGIN
3397
3398 IF (g_level_procedure >= g_current_runtime_level ) THEN
3399 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info.BEGIN',
3400 'ZX_AP_ACTG_EXTRACT_PKG: insert_actg_info(+)');
3401 END IF;
3402
3403 l_count := P_COUNT;
3404
3405
3406 IF (g_level_procedure >= g_current_runtime_level ) THEN
3407 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info',
3408 ' Record Count = ' ||to_char(P_COUNT));
3409 END IF;
3410
3411
3412 FORALL i IN 1 .. l_count
3413 INSERT INTO ZX_REP_ACTG_EXT_T(
3414 actg_ext_line_id,
3415 detail_tax_line_id,
3416 actg_event_type_code,
3417 actg_event_number,
3418 actg_event_status_flag,
3419 actg_category_code,
3420 accounting_date,
3421 gl_transfer_flag,
3422 -- gl_transfer_run_id,
3423 actg_header_description,
3424 actg_line_num,
3425 actg_line_type_code,
3426 actg_line_description,
3427 actg_stat_amt,
3428 actg_error_code,
3429 gl_transfer_code,
3430 actg_doc_sequence_id,
3431 --actg_doc_sequence_name,
3432 actg_doc_sequence_value,
3433 actg_party_id,
3434 actg_party_site_id,
3435 actg_party_type,
3436 actg_event_id,
3437 actg_header_id,
3438 actg_source_id,
3439 --actg_source_table,
3440 actg_line_ccid,
3441 period_name,
3442 TRX_ARAP_BALANCING_SEGMENT,
3443 TRX_ARAP_NATURAL_ACCOUNT,
3444 TRX_TAXABLE_BALANCING_SEGMENT,
3445 TRX_TAXABLE_NATURAL_ACCOUNT,
3446 TRX_TAX_BALANCING_SEGMENT,
3447 TRX_TAX_NATURAL_ACCOUNT,
3448 ACCOUNT_FLEXFIELD,
3449 ACCOUNT_DESCRIPTION,
3450 created_by,
3451 creation_date,
3452 last_updated_by,
3453 last_update_date,
3454 last_update_login,
3455 program_application_id,
3456 program_id,
3457 program_login_id,
3458 request_id,
3459 TRX_CONTROL_ACCOUNT_FLEXFIELD,
3460 TRX_TAXABLE_ACCOUNT_DESC,--Bug 5650415
3461 TRX_TAXABLE_BALSEG_DESC,--Bug 5650415
3462 TRX_TAXABLE_NATACCT_SEG_DESC, --Bug 5650415
3463 TRX_TAXABLE_ACCOUNT
3464 )
3465 VALUES (zx_rep_actg_ext_t_s.nextval,
3466 agt_detail_tax_line_id(i),
3467 agt_actg_event_type_code(i),
3468 agt_actg_event_number(i),
3469 agt_actg_event_status_flag(i),
3470 agt_actg_category_code(i),
3471 agt_accounting_date(i),
3472 agt_gl_transfer_flag(i),
3473 -- agt_gl_transfer_run_id(i),
3474 agt_actg_header_description(i),
3475 agt_actg_line_num(i),
3476 agt_actg_line_type_code(i),
3477 agt_actg_line_description(i),
3478 agt_actg_stat_amt(i),
3479 agt_actg_error_code(i),
3480 agt_gl_transfer_code(i),
3481 agt_actg_doc_sequence_id(i),
3482 -- agt_actg_doc_sequence_name(i),
3483 agt_actg_doc_sequence_value(i),
3484 agt_actg_party_id(i),
3485 agt_actg_party_site_id(i),
3486 agt_actg_party_type(i),
3487 agt_actg_event_id(i),
3488 agt_actg_header_id(i),
3489 agt_actg_source_id(i),
3490 -- agt_actg_source_table(i),
3491 agt_actg_line_ccid(i),
3492 agt_period_name(i),
3493 GT_TRX_ARAP_BALANCING_SEGMENT(i),
3494 GT_TRX_ARAP_NATURAL_ACCOUNT(i),
3495 GT_TRX_TAXABLE_BAL_SEG(i),
3496 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),
3497 GT_TRX_TAX_BALANCING_SEGMENT(i),
3498 GT_TRX_TAX_NATURAL_ACCOUNT(i),
3499 GT_ACCOUNT_FLEXFIELD(i),
3500 GT_ACCOUNT_DESCRIPTION(i),
3501 g_created_by,
3502 g_creation_date,
3503 g_last_updated_by,
3504 g_last_update_date,
3505 g_last_update_login,
3506 g_program_application_id,
3507 g_program_id,
3508 g_program_login_id,
3509 g_request_id,
3510 GT_TRX_CONTROL_ACCFLEXFIELD(i),
3511 GT_TRX_TAXABLE_ACCOUNT_DESC(i),--Bug 5650415
3512 GT_TRX_TAXABLE_BALSEG_DESC(i),--Bug 5650415
3513 GT_TRX_TAXABLE_NATACCT_DESC(i), --Bug 5650415
3514 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i)
3515 );
3516
3517 IF (g_level_procedure >= g_current_runtime_level ) THEN
3518 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info',
3519 'Number of Tax Lines successfully inserted = '||TO_CHAR(l_count));
3520
3521 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info.END',
3522 'ZX_AP_ACTG_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
3523 END IF;
3524
3525 EXCEPTION
3526 WHEN OTHERS THEN
3527 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3528 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3529 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3530 FND_MSG_PUB.Add;
3531 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3532 FND_LOG.STRING(g_level_unexpected,
3533 'ZX.TRL.ZX_AP_ACTG_EXTRACT_PKG.insert_actg_info',
3534 g_error_buffer);
3535 END IF;
3536
3537 g_retcode := 2;
3538
3539 END insert_actg_info;
3540
3541
3542
3543 PROCEDURE initialize_variables (
3544 p_count IN NUMBER) IS
3545 i number;
3546
3547 BEGIN
3548
3549 IF (g_level_procedure >= g_current_runtime_level ) THEN
3550 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.initialize_variables.BEGIN',
3551 'initialize_variables(+)');
3552 END IF;
3553
3554 FOR i IN 1.. p_count LOOP
3555 GT_LEDGER_NAME(i) := NULL;
3556 GT_BILLING_TP_NUMBER(i) := NULL;
3557 GT_BILLING_TP_TAX_REG_NUM(i) := NULL;
3558 GT_BILLING_TP_TAXPAYER_ID(i) := NULL;
3559 GT_BILLING_TP_SITE_NAME_ALT(i) := NULL;
3560 GT_BILLING_TP_SITE_NAME(i) := NULL;
3561 GT_BILLING_SITE_TAX_REG_NUM(i) := NULL;
3562 GT_TAX_REG_NUM(i) := NULL;
3563 GT_BILLING_TP_NAME(i) := NULL;
3564 GT_BILLING_TP_NAME_ALT(i) := NULL;
3565 GT_BILLING_TP_SIC_CODE(i) := NULL;
3566 GT_BILLING_TP_CITY(i) := NULL;
3567 GT_BILLING_TP_COUNTY(i) := NULL;
3568 GT_BILLING_TP_STATE(i) := NULL;
3569 GT_BILLING_TP_PROVINCE(i) := NULL;
3570 GT_BILLING_TP_ADDRESS1(i) := NULL;
3571 GT_BILLING_TP_ADDRESS2(i) := NULL;
3572 GT_BILLING_TP_ADDRESS3(i) := NULL;
3573 GT_BILLING_TP_ADDR_LINES_ALT(i) := NULL;
3574 GT_BILLING_TP_COUNTRY(i) := NULL;
3575 GT_BILLING_TP_POSTAL_CODE(i) := NULL;
3576 GT_GDF_PO_VENDOR_SITE_ATT17(i) := NULL;
3577 -- GT_TRX_CLASS_MNG(i) := NULL;
3578 -- GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := NULL;
3579 -- GT_TAX_RATE_REG_TYPE_CODE(i) := NULL;
3580 -- GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := NULL;
3581 -- GT_TAX_RATE_VAT_TRX_TYPE_MNG(i) := NULL;
3582 gt_actg_ext_line_id(i) := NULL;
3583 GT_TRX_ARAP_BALANCING_SEGMENT(i) := NULL;
3584 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := NULL;
3585 GT_TRX_TAXABLE_BAL_SEG(i) := NULL;
3586 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := NULL;
3587 GT_TRX_TAX_BALANCING_SEGMENT(i) := NULL;
3588 GT_TRX_TAX_NATURAL_ACCOUNT(i) := NULL;
3589 GT_ACCOUNT_FLEXFIELD(i) := NULL;
3590 GT_ACCOUNT_DESCRIPTION(i) := NULL;
3591 GT_TRX_CONTROL_ACCFLEXFIELD(i) := NULL;
3592 GT_TRX_TAXABLE_ACCOUNT_DESC(i) := NULL;
3593 GT_TRX_TAXABLE_BALSEG_DESC(i) := NULL;
3594 GT_TRX_TAXABLE_NATACCT_DESC(i) := NULL;
3595 GT_BILL_FROM_PARTY_SITE_ID(i) := NULL;
3596 GT_BILL_FROM_PARTY_ID(i) := NULL;
3597
3598 END LOOP;
3599 -- Populate WHO columns --
3600
3601 g_created_by := fnd_global.user_id;
3602 g_creation_date := sysdate;
3603 g_last_updated_by := fnd_global.user_id;
3604 g_last_update_login := fnd_global.login_id;
3605 g_last_update_date := sysdate;
3606
3607 g_program_application_id := fnd_global.prog_appl_id;
3608 g_program_id := fnd_global.conc_program_id;
3609 g_program_login_id := fnd_global.conc_login_id;
3610
3611 -- GT_SHIPPING_TP_ADDRESS_ID.delete;
3612 -- GT_BILLING_TP_ADDRESS_ID.delete;
3613 -- GT_SHIPPING_TP_SITE_ID.delete;
3614 -- GT_BILLING_TP_SITE_ID.delete;
3615 -- GT_SHIPPING_TP_ID.delete;
3616 -- GT_BILLING_TRADING_PARTNER_ID.delete;
3617
3618 IF (g_level_procedure >= g_current_runtime_level ) THEN
3619 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.initialize_variables.END',
3620 'initialize_variables(-)');
3621 END IF;
3622
3623
3624 EXCEPTION
3625 WHEN OTHERS THEN
3626 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3627 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3628 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3629 FND_MSG_PUB.Add;
3630 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3631 FND_LOG.STRING(g_level_unexpected,
3632 'ZX.TRL.ZX_AP_POPULATE_PKG.initialize_variables',
3633 g_error_buffer);
3634 END IF;
3635 g_retcode := 2;
3636
3637 END initialize_variables ;
3638
3639 PROCEDURE populate_tax_reg_num(
3640 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
3641 P_ORG_ID IN zx_lines.internal_organization_id%TYPE ,
3642 P_TAX_DATE IN zx_lines.tax_date%TYPE,
3643 i BINARY_INTEGER) IS
3644
3645 CURSOR trn_ptp_id_cur (c_org_id zx_lines.internal_organization_id%TYPE,
3646 c_le_id NUMBER,
3647 c_tax_date zx_lines.tax_date%TYPE
3648 ) IS
3649 SELECT ptp.rep_registration_number
3650 FROM xle_tax_associations rel
3651 ,zx_party_tax_profile ptp
3652 ,xle_etb_profiles etb
3653 WHERE rel.legal_construct_id = etb.establishment_id
3654 AND etb.party_id = ptp.party_id
3655 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
3656 AND rel.entity_id = c_org_id
3657 AND rel.legal_parent_id = c_le_id
3658 --P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id
3659 AND rel.LEGAL_CONSTRUCT = 'ESTABLISHMENT'
3660 AND rel.entity_type = 'OPERATING_UNIT'
3661 AND rel.context = 'TAX_CALCULATION'
3662 AND c_tax_date between rel.effective_from and nvl(rel.effective_to,c_tax_date);
3663
3664 BEGIN
3665
3666 IF (g_level_procedure >= g_current_runtime_level ) THEN
3667 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.populate_tax_reg_num.BEGIN',
3668 'populate_tax_reg_num(+)');
3669 END IF;
3670
3671 OPEN trn_ptp_id_cur (p_org_id,
3672 P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id,
3673 p_tax_date);
3674 FETCH trn_ptp_id_cur into GT_TAX_REG_NUM(i);
3675 CLOSE trn_ptp_id_cur;
3676
3677 IF (g_level_procedure >= g_current_runtime_level ) THEN
3678 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.populate_tax_reg_num.END',
3679 'populate_tax_reg_num(-)');
3680 END IF;
3681 END populate_tax_reg_num;
3682
3683
3684 PROCEDURE lookup_desc_meaning(p_lookup_type IN VARCHAR2,
3685 P_LOOKUP_CODE IN VARCHAR2,
3686 p_meaning OUT NOCOPY VARCHAR2,
3687 p_description OUT NOCOPY VARCHAR2) IS
3688
3689 CURSOR lookup_cur (c_lookup_type VARCHAR2,
3690 c_lookup_code VARCHAR2) IS
3691 SELECT meaning, description
3692 FROM fnd_lookups
3693 WHERE lookup_type = c_lookup_type
3694 AND lookup_code = c_lookup_code;
3695
3696 l_tbl_index_lookup BINARY_INTEGER;
3697 BEGIN
3698
3699 IF (g_level_procedure >= g_current_runtime_level ) THEN
3700 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning.BEGIN',
3701 'lookup_desc_meaning(+)');
3702 END IF;
3703
3704 IF p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL THEN
3705 IF (g_level_procedure >= g_current_runtime_level ) THEN
3706 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning',
3707 'Lookup Type and Lookup code are not null '||p_lookup_type||'-'||P_LOOKUP_CODE);
3708 END IF;
3709
3710 l_tbl_index_lookup := dbms_utility.get_hash_value(p_lookup_type||p_lookup_code, 1,8192);
3711
3712 IF (g_level_procedure >= g_current_runtime_level ) THEN
3713 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning',
3714 'Meaning Alredy existed in the Cache');
3715 END IF;
3716
3717 IF g_lookup_info_tbl.EXISTS(l_tbl_index_lookup) THEN
3718 p_meaning := g_lookup_info_tbl(l_tbl_index_lookup).lookup_meaning;
3719 p_description := g_lookup_info_tbl(l_tbl_index_lookup).lookup_description;
3720 ELSE
3721 IF (g_level_procedure >= g_current_runtime_level ) THEN
3722 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning',
3723 'Before Open lookup_cur');
3724 END IF;
3725
3726 OPEN lookup_cur (p_lookup_type, p_lookup_code);
3727 FETCH lookup_cur
3728 INTO p_meaning,
3729 p_description;
3730
3731 IF (g_level_procedure >= g_current_runtime_level ) THEN
3732 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning',
3733 'p_meaning p_description'||p_meaning||' '||p_description);
3734 END IF;
3735
3736 g_lookup_info_tbl(l_tbl_index_lookup).lookup_meaning := p_meaning;
3737 g_lookup_info_tbl(l_tbl_index_lookup).lookup_description := p_description;
3738 END IF;
3739 END IF;
3740
3741 IF lookup_cur%ISOPEN THEN
3742 CLOSE lookup_cur;
3743 END IF;
3744 IF (g_level_procedure >= g_current_runtime_level ) THEN
3745 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning.END',
3746 'lookup_desc_meaning(-)');
3747 END IF;
3748
3749 EXCEPTION
3750 WHEN OTHERS THEN
3751 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3752 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3753 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3754 FND_MSG_PUB.Add;
3755 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3756 FND_LOG.STRING(g_level_unexpected,
3757 'ZX.TRL.ZX_AP_POPULATE_PKG.lookup_desc_meaning',
3758 g_error_buffer);
3759 END IF;
3760 g_retcode := 2;
3761
3762 END lookup_desc_meaning;
3763
3764 -----------------------------------------------------------------------
3765 -- bug 13607557
3766 -- new procedure replacing row by row processing earlier done
3767 -- in the procedure populate_meaning
3768 -----------------------------------------------------------------------
3769 PROCEDURE bulk_populate_zx_dtl
3770 (p_register_type IN VARCHAR2 -- P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE
3771 ) IS
3772
3773 l_meaning fnd_lookups.meaning%TYPE;
3774
3775 BEGIN
3776
3777 BEGIN
3778 SELECT meaning
3779 INTO l_meaning
3780 FROM fnd_lookups
3781 WHERE lookup_type = 'ZX_TRL_REGISTER_TYPE'
3782 AND lookup_code = p_register_type;
3783 EXCEPTION
3784 WHEN OTHERS THEN
3785 l_meaning := NULL;
3786 END;
3787
3788 UPDATE zx_rep_trx_detail_t zx_dtl
3789 SET tax_rate_code_reg_type_mng = l_meaning
3790 ,tax_rate_register_type_code =
3791 DECODE(tax_recoverable_flag,'Y','TAX'
3792 ,'NON-RECOVERABLE')
3793 ,(tax_rate_code_vat_trx_type_mng,tax_rate_vat_trx_type_desc) =
3794 (SELECT meaning, description
3795 FROM fnd_lookups
3796 WHERE lookup_type = 'ZX_JEBE_VAT_TRANS_TYPE'
3797 AND lookup_code = zx_dtl.tax_rate_vat_trx_type_code
3798 )
3799 ,trx_class_mng = CASE WHEN trx_line_class IS NULL THEN NULL
3800 ELSE
3801 (SELECT meaning
3802 FROM fnd_lookups
3803 WHERE lookup_type = 'ZX_TRL_TAXABLE_TRX_TYPE'
3804 AND lookup_code = zx_dtl.trx_line_class
3805 )
3806 END
3807 ,tax_type_mng = CASE WHEN tax_type_code IS NULL THEN NULL
3808 ELSE
3809 (SELECT meaning
3810 FROM fnd_lookups
3811 WHERE lookup_type = 'ZX_TAX_TYPE_CATEGORY'
3812 AND lookup_code = zx_dtl.tax_type_code
3813 )
3814 END
3815 WHERE zx_dtl.request_id = G_REQUEST_ID
3816 AND zx_dtl.extract_source_ledger = 'AP';
3817
3818 END bulk_populate_zx_dtl;
3819
3820 END ZX_AP_POPULATE_PKG;