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